|
<< Click to Display Table of Contents >> Navigation: RHOP User Interface (UI) > Member Purchases > Load Purchases > Universal File Format |
Universal file format for Unipco RHOP
The preferred file format is JSON. If unable to provide JSON then provide the CSV format. Field names cannot be modified or file will be rejected.
JSON file format (preferred)
Example of a JSON file:
{
“number_records”: 5,
“total_sales”: 2345.00
“product_sales_records”:
[
{
"customer_no": "1234",
"customer_name": "TEST CUSTOMER",
"gtin": "12345678901234",
"product_no": "123456",
"product_description_gs1": "MILK",
“product_description_distributor”: ”MILK”,
"pack_size": "4/4L",
“product_weight_unit”: “lbs”
"total_net_weight ": "1.55",
"total_gross_weight ": "1.65",
"total_catch_weight": 1.5,
"quantity_shipped": 1.5,
"split_case": true,
"net_sales": 42.00,
"special_order": true,
"supplier_name": "SUPPLIER NAME",
"supplier_product_no": "123456",
"manufacturer_name": "MANUFACTURER NAME",
"manufacturer_product_no": "123456",
"product_brand": "PRODUCT BRAND",
"product_french_description": "DESCRIPTION EN FRANÇAIS",
"invoice_no": "12345",
"product_price_per_pack_size": 28.00,
"manufacturer_cost": 35.00,
“freight_cost”: 5.00
"rebatable": true,
"ship_date": "2021-08-16",
"rebate_amount": 1.26,
"rebate_percent": 3.00
},
...
]
}
Fields definitions:
•“number_records” (mandatory): This is how many records that are supposed to be in the JSON file. This will allow the application to validate that the file has all the records that it is supposed to have. (Not applicable for the CSV file format)
•“total_sales” (mandatory): This is the total of sales that the sum of the column “net_sales”, in the “sales_records” should equal to. (Not applicable for the CSV file format)
•“product_sales_records” (mandatory): This is the list of the product sales. This field can contain one or more product sales records and should only appear once in the JSON file. (Not applicable for the CSV file format)
•“customer_no” (mandatory): This is the distributor/supplier customer/account number for the member.
•“customer_name” (mandatory): This is the distributor/supplier customer name that represents the member.
•“gtin” (mandatory): This is either a GTIN (GS1 compliant), a SCC or a UPC code. If no code exists for the product, then it can be a blank value, but the field will need to be part of the record.
•“product_no” (mandatory): This is the product number used by the distributor or direct supplier.
•“product_description_gs1” (mandatory): This is the product description that was registered with GS1
•“product_description_distributor” (mandatory): This is the product description used by the distributor or direct supplier.
•“pack_size” (mandatory): Represents how the product is packaged (examples: 4/4L, 6X500g)
•“product_weight_unit” (conditional): One of the following values: “lbs” or “kg”. This field is optional, unless at least one of the following fields is included: “total_net_weight”, “total_gross_weight” and/or “total_catch_weight”.
•“total_net_weight” (optional): This is the total net weight (without packaging) of the product sales. If this field is included, then the “product_weight_unit” field has to be included, as to indicate if the weight is in ”lbs” or “kg”.
•“total_gross_weight” (optional): This is the total gross weight of the product sales. If this field is included, then the “product_weight_unit” field has to be included, as to indicate if the weight is in ”lbs” or “kg”.
•“total_catch_weight” (optional): This is the total catch weight of the product sales. If this field is included, then the “product_weight_unit” field has to be included, as to indicate if the weight is in ”lbs” or “kg”.
•“quantity_shipped” (mandatory): This the number of “pack_size” units that were shipped. The quantity can include decimals to indicate splits.
•“split_case” (mandatory): One of the following values: true or false. If the value is true, then the “quantity_shipped” should represent the actual quantity in decimal (i.e.: if a ] has a pack size of “4/4L” and only one 4L was shipped, then “split_case” will be true and the “quantity_shipped” will be 0.25)
•“net_sales” (mandatory): Total pre-tax sales amount for the product sales.
•“special_order” (optional): One of the following values: true or false. Include this field, if the product is a special order.
•“supplier_name” (mandatory): Name of the product’s supplier/vendor.
•“supplier_product_no” (optional): If the supplier product number is the same as the “product_no” field value, then this field can be omitted.
•“manufacturer_name” (optional): If the manufacturer name is the same as the supplier name, then this field can be omitted.
•“manufacturer_product_no” (optional): If the manufacturer product number is the same as the “supplier_product_no” field value, then this field can be omitted.
•“product_brand” (optional): This is the product’s brandname.
•“product_french_description” (optional): This is the French version of the “product_description_distributor” field.
•“invoice_no” (optional): This is the invoice number related to the product sales.
•“product_price_per_pack_size” (optional): This is the price for the product based on the “pack_size” field.
•“total_manufacturer_cost” (optional): This is the total manufacturer cost for the product sales.
•“total_freight_cost” (optional): This is the total freight cost for the product sales.
•“rebatable” (mandatory): One of the following values: true or false. This is to indicate which product sales is rebatable.
• “ship_date” (mandatory): The product sales ship date. It should be formatted as follows: YYYY-MM-DD (YYYY=4 digit year, MM=2 digit month, DD=2 digit day of the month) (i.e.: 2021-10-01).
•“rebate_amount” (optional): This is the rebate amount calculated by the distributor or the direct supplier. It can be omitted if it’s not available.
•“rebate_percent” (optional): This is the percentage used to calculate the “rebate_amount” by the distributor or the direct supplier. It can be omitted if it’s not available.
CSV file format
If the file will be sent from the distributor/supplier in a CSV file format, the following columns will have to be in the file and in the same order as listed below. The definition of the columns is the same as described as in the JSON file format fields definitions, with the exception that all the columns are mandatory, even if they would be blank.
CSV columns:
-CUSTOMER_NO
-CUSTOMER_NAME
-GTIN
-PRODUCT_NO
-PRODUCT_DESCRIPTION_GS1
-PRODUCT_DESCRIPTION_DISTRIBUTOR
-PACK_SIZE
-PRODUCT_WEIGHT_UNIT
-TOTAL_NET_WEIGHT
-TOTAL_GROSS_WEIGHT
-TOTAL_CATCH_WEIGHT
-QUANTITY_SHIPPED
-SPLIT_CASE
-NET_SALES
-SPECIAL_ORDER
-SUPPLIER_NAME
-SUPPLIER_PRODUCT_NO
-MANUFACTURER_NAME
-MANUFACTURER_PRODUCT_NO
-PRODUCT_BRAND
-PRODUCT_FRENCH_DESCRIPTION
-INVOICE_NO
-PRODUCT_PRICE_PER_PACK_SIZE
-MANUFACTURER_COST
-FREIGHT_COST
-REBATABLE
-SHIP_DATE
-REBATE_AMOUNT
-REBATE_PERCENT