Reviewed and Updated : Oct 21, 2026



Sample files and instructions > P:\M I S Department\CARM\Loading PV PO\BHI-158784-Modified.xlsx (as of Sept 23, 2026)

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 for column X2 and should be copied up to 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)  **Column G or PRODUCT_NUMBER 

                            =SUBSTITUTE($X$1,"@1",G3)       **Column G or PRODUCT_NUMBER 


2. QUANTITY

     Column K or Unit of Measure; Column J or CCI Quantity (copy formula below to column R)

      =IF(OR(LEFT(K2,1) = "C",LEFT(K2,1) = "D",LEFT(K2,1) = "E",LEFT(K2,1) = "F"),J2*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,"C",""),"D","1"),"E","2"),"F","3"),J2)


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 / KGM ==> 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)  **Column H or Product Description


     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