Sample files and instructions > P:\M I S Department\CARM\Loading PV PO\137219 - INCOMING Modified.XLSX
File should contain unique BRANDS. If multi-brand save each brand to individual files.
1. Generate query to find SKU (you might need it later)
Formula:X2 should be copied til the end of file
(X1) : SELECT * FROM BILSI.dbo.B3DB_PRODVEND WHERE COMPANY_ID = 'A' AND REFERENCE_NUMBER = '@1' AND ADDED_IN_CANDATA = 1
(X2..onwards) : =SUBSTITUTE($X$1,"@1",G2)
=SUBSTITUTE($X$1,"@1",G3)
|
2. QUANTITY |
If Column J (Unit of measure is not by PCE) then Column V should calculate as follows: (copy formula til end of file)
=IF(OR(LEFT(J2,1) = "C",LEFT(J2,1) = "D",LEFT(J2,1) = "E",LEFT(J2,1) = "F"),K2*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,"C",""),"D","1"),"E","2"),"F","3"),K2)
Example:
Column J is C44 ==> (0)44 ==> 44 x qty
D22 ==> (1)22 ==> 122 x qty
E34 ==> (2)34 ==> 234 x qty
F55 ==> (3)55 ==> 355 x qty
NMB ==> 1 ===> 1 x qty (NMB is not in the formula as this is just x 1 which is the actual number on QTY column)
3. Product Number (set to column Q)
If length is 5 = no change on formula
=RIGHT(TRIM(H2),5)
If length is 4 = formula should only get 4 chars otherwise it might include control characters
=RIGHT(TRIM(H2),4)
4. Save file and load in LINK : Customer Service \ Provision Tools \ Incoming
HERMES
BHI
5. If any of the product sku is invalid, go back to the excel file. Get the value in X (generated from process#1) for the line that has issue and perform in SQL
Get the value from PRODUCT_NUMBER and plug it into Column Q.
Repeat process#4
6. Click Create Provision PO
7. Give Customs team the PO# generated from process#6