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