Overview: The Price Code Upload allows you to upload price code information from an external system to create, update, or delete records in the Price Code, Price Code Customer, and Price Code Details tables.
In this chapter:
• Price Code Upload Errors Report
For more information: See Working with Price Codes (WPCD) and Assigning Price Codes (APCD).
The setup required to use the price code upload includes:
• Price Code Upload Table (PriceCdUpload)
• PCUPLD Price Code Upload Periodic Function
Create a Price Code text file for the price code information you wish to create, update, or delete. The file should be named PRICECDUPLOAD.txt.
Note:
• The Request Type identifies the type of upload record.
• You must create records in this file in the correct order; for example, you must process a Price Code upload record before you can upload a Price Code Customer upload record and Price Code Detail upload record for that price code. The system processes the records in ascending Seq # order.
• Decimals in this file are explicit and not implied; for example, pass 19.99 and not 1999.
• To leave any field in the upload file blank, pass a space in an alphanumeric field and a 0 in a numeric field so that the file can be processed without errors. Leaving a field with no space or 0 is interpreted as null in the database and causes errors.
See the Price Code Upload Process for more information on using the data in the text file.
Sample data: You can use the sample data below to create a price code upload file.
Sample PCO Price Code Upload record type:
7|1|PCO|U|1150416|1234567|PRICE CODE UPLOAD|1|1|5.00|0.00|0.00|0.00|0.00|0.00|ITEM|Y|1150401|1150501|||||0||||
Sample PCC Price Code Customer Upload record type:
7|2|PCC|U|1150416|1234567||0|0|.00|.00|.00|.00|.00|.00|||0|0|||||55||||
Sample PCD Price Code Detail Upload record type:
7|3|PCD|U|1150416|1234567||0|0|.00|.00|.00|.00|.00|.00|||0|0|SKU|RED||SOURCE7|0||||
Price Code Upload Table (PriceCdUpload)
This table contains upload records for the Price Code, Price Code Customer, and Price Code Detail tables. The Request Type identifies the type of upload record.
Field |
Description |
Required for all Record Types. A code for the company associated with the Price Code Upload record. The system processes only records associated with the company from where the Price Code Upload Process was submitted. Company codes are defined in and validated against the Company table. Price Code: Updates Company in the Price Code table. Price Code Customer: Updates Company in the Price Code Customer table. Price Code Detail: Updates Company in the Price Code Details table. Numeric, 3 positions. |
|
Required for all Record Types. A unique number assigned to each Price Code Upload record. The system processes the records in the Price Code Upload table in ascending sequence number order. Numeric, 9 positions. |
|
Required for all Record Types. The type of Price Code Upload record. Valid values: • PCO = Price Code upload record; this record creates, updates, or deletes a record in the Price Code table. • PCC = Price Code Customer upload record; this record creates or updates a record in the Price Code Customer table. • PCD = Price Code Detail upload record; this record creates or updates a record in the Price Code Details table. Alphanumeric, 3 positions. |
|
Required for all Record Types. Indicates whether the Price Code Upload record creates, updates, or deletes a record. Valid values: • U = Valid for all Record Types. Create or update a record. The system determines whether the Price Code Upload record creates or updates an existing record by comparing the information in the Price Code Upload record with the existing records in the Order Management System table. See Price Code Upload Process for more information on how the system determines if the Price Code Upload record creates a new record or updates an existing record. • D = Valid only for Record Type PCO Price Code. Delete a Price Code record and its associated Price Code Customer records and Price Code Detail records. The system uses the information in the Price Code Upload record to determine which records to delete. Alphanumeric, 1 position. |
|
Required for all Record Types. The date the Price Code Upload record was created. Numeric, 7 positions (CYYMMDD format). |
|
Required for all Record Types. A code to identify the price code to upload. Price codes are defined in and validated against the Price Code table. Price Code: Updates PCO Price Code in the Price Code table. Price Code Customer: Updates Price Code Code in the Price Code Customer table. Price Code Detail: Updates PCO Price Code in the Price Code Details table. Numeric, 7 positions. |
|
Required for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. A description of the price code. Updates PCO Description in the Price Code table. Alphanumeric, 30 positions. |
|
Required for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The order in which the system evaluates whether a price code applies to an order. Regular Price Code Pricing: If an order qualifies for two or more price codes, the system uses the price code with the lowest sequence number. If two price codes have the same sequence number, the system uses the price code with the lowest price code number. Customer Price Group Price Code Pricing: If an order qualifies for two or more price codes, the system uses the price code that provides the greatest discount to the order. If two price codes provide the same discount, the system uses the price code with the lowest sequence number. If two price codes have the same sequence number, the system uses the price code with the lowest price code number. Updates PCO Sequence in the Price Code table. Numeric, 7 positions. |
|
Required for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The total quantity the customer must order of all qualifying items to get the price break. Updates PCO Qty Required in the Price Code table. Numeric, 7 positions. |
|
Required for Record Type PCO Price Code if a PCO Dollar Discount, PCO Special Price, or Group Price is not defined. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The percentage of the regular item price to subtract for each qualifying item associated with the price code. If the Tax Included in Price (E70) system control value is selected, the system applies the discount against the regular tax-inclusive price to calculate the tax-inclusive price code price. Updates PCO Percent Discount in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Required for Record Type PCO Price Code if a PCO Percent Discount, PCO Special Price, or Group Price is not defined. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The dollar amount to subtract from the regular price of each qualifying item associated with the price code. If the Tax Included in Price (E70) system control value is selected, the system applies the discount against the regular tax-inclusive price to calculate the tax-inclusive price code price. Updates PCO Dollar Discount in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Required for Record Type PCO Price Code if a PCO Percent Discount, PCO Dollar Discount, or Group Price is not defined. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The special price to charge the customer for all qualifying items associated with the price code. Updates PCO Special Price in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Required for Record Type PCO Price Code if a PCO Percent Discount, PCO Dollar Discount, or Group Price is not defined and the Tax Included in Price (E70) system control value is selected. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The special price to charge the customer for all qualifying items associated with the price code on orders subject to VAT. Used only if the Tax Included in Price (E70) system control value is selected. Note: If an order is subject to VAT, but the item is subject to a price code with a only a special price and not a Tax-inclusive price, the system uses the special price for the price code. Updates PCO Tax Special Price in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Required for Record Type PCO Price Code if a PCO Percent Discount, PCO Dollar Discount, or PCO Special Price is not defined. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The group price to charge the customer for all qualifying items associated with the price code. Updates PCO Group Price in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Required for Record Type PCO Price Code if a PCO Percent Discount, PCO Dollar Discount, or PCO Special Price is not defined and the Tax Included in Price (E70) system control value is selected. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The group price to charge the customer for all qualifying items associated with the price code on orders subject to VAT. Used only if the Tax Included in Price (E70) system control value is selected. Note: If an order is subject to VAT, but the item is subject to a price code with a only a group price and not a Tax-inclusive price, the system uses the group price for the price code. Updates PCO Tax Special Price in the Price Code table. Numeric, 7 positions with a 2-place decimal. |
|
Optional for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The method the system uses to determine whether the customer ordered the quantity required to receive the group price. Valid values: • Blank = Distinct By setting not defined. • ITEM = The quantity required is by item. • SKU = The quantity required is by SKU. • CATEGORY = The quantity required is by item category. Updates PCO Distinct By in the Price Code table. Alphanumeric, 10 positions. |
|
Required for Record Type PCO Price Code if a Distinct By setting or Group Price is defined. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. Defines whether the order qualifies for a discount multiple times if the correct number of qualifying items/SKUs is added to the order. • Blank = Multiples setting not defined. • Y = Allow multiples. Must be Y if the Distinct By field is populated. • N = Do not allow multiples. Updates PCO Allow Multiples in the Price Code table. Alphanumeric, 1 position. |
|
Optional for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The beginning date when the price code discount becomes effective. Updates PCO Start Date in the Price Code table. Numeric, 7 positions (CYYMMDD format). |
|
Optional for Record Type PCO Price Code. Not used by Record Type PCC Price Code Customer or PCD Price Code Detail. The end date when the pice code discount becomes effective. The end date cannot be earlier than the start date or earlier than the current date. Updates PCO End Date in the Price Code table. Numeric, 7 positions (CYYMMDD format). |
|
Required for Record Type PCD Price Code Detail. Not used by Record Type PCO Price Code or PCC Price Code Customer. A code for the item to assign to the price code. Item codes are defined in and validated against the Item table. Updates ITM Number in the Price Code Details table. Alphanumeric, 12 positions. |
|
Required for Record Type PCD Price Code Detail if the item contains SKUs and you want to specify which SKUs qualify for the price code; leave blank if all SKUs for the item qualify for the price code. Not used by Record Type PCO Price Code or PCC Price Code Customer. A code for the SKU to assign to the price code. SKU codes are defined in and validated against the SKU table. Updates SKU Code in the Price Code Details table. Alphanumeric, 14 positions. |
|
Required for Record Type PCD Price Code Detail if a SRC Source Code is not defined. Not used by Record Type PCO Price Code or PCC Price Code Customer. A catalog, advertisement, or other means you use to present merchandise to your customers. The price code defined for an item/offer combination applies to orders whose header-level source code points to the offer. Offers are defined in and validated against the Offer table. See Working with Offers (WOFR). Updates Offer Number in the Price Code Details table. Alphanumeric, 3 positions. |
|
Required for Record Type PCD Price Code Detail if a Offer Number is not defined. Not used by Record Type PCO Price Code or PCC Price Code Customer. A code you use to group a segment of your customers. The price code defined for this item/source code combination applies to orders with this source code on the order header. Source codes are defined in and validated against the Source Code table. See Working with Source Codes (WSRC). Updates SRC Source Code in the Price Code Details table. Alphanumeric, 9 positions. |
|
Required for Record Type PCC Price Code Customer if a Customer Price Group is not defined. Not used by Record Type PCO Price Code or PCD Price Code Detail. The sold to customer number that qualifies for the price code. Sold to customer numbers are defined in and validated against the Customer Sold To table. Updates Customer # in the Price Code Customer table. Numeric, 9 positions. |
|
Required for Record Type PCC Price Code Customer if a Customer is not defined. Not used by Record Type PCO Price Code or PCD Price Code Detail. The code for the customer price group that qualifies for the price code discount. Customer price group codes are defined in and validated against the Customer Price Group table. Updates Customer Price Group in the Price Code Customer table. Alphanumeric, 4 positions. |
|
Updated by the system during the Price Code Upload Process. The reason why the Price Code Upload record has been placed in an error status. See Price Code Upload Errors. Alphanumeric, 20 positions. |
|
Updated by the system during the Price Code Upload Process. The status of the Price Code Upload record. Valid values: • Blank = The Price Code Upload record has not yet been processed. • P = The Price Code Upload record has processed successfully. The system removes these records at the end of the Price Code Upload Process. • E = The Price Code Upload record contains errors. See Price Code Upload Errors. Alphanumeric, 1 position. |
PCUPLD Price Code Upload Periodic Function
Use the PCUPLD periodic function to update the Price Code table after you have completed the File for Upload.
Function: |
PCUPLD |
Description: |
PRICE CODE UPLOAD |
Program name: |
PFPRCCODUP |
Company |
The Company flag must be selected. The Price Code Upload process creates, updates, and deletes records in the Price Code, Price Code Customer, and Price Code Details tables based on the records in the Price Code Upload Table (PriceCdUpload) using the company you entered when you submitted the PCUPLD periodic function. |
3. The PCUPLD periodic function is delivered with the system; use the Working with Periodic Functions (WPER) menu option to review it.
4. Use the Working with Periodic Processes (WPPR) menu option to assign the UPPRCCD and PCUPLD periodic function to a periodic process.
5. Once you have created the periodic process, you can use the Execute Periodic Process screen (located in the Working with Periodic Processes (WPPR) or Executing Periodic Processes (EPRO)) to define a schedule for the job. See Defining the Job Schedule.
For more information: See Scheduling Jobs for more information on How to Schedule a Job.
Use the following steps to upload Price Code records from an external system.
1. |
Create a text file named PRICECDUPLOAD.txt containing the price code data. See File for Upload for more information. |
|
Update Price Code Upload Table (PriceCdUpload) with price code information from the text file: • Use the File Storage API to create a record of the PRICECDUPLOAD file in the FILE_STORAGE table, and then run the UPPRCCD Upload Price Code File (Program name PFR0134, Parameter PRICECDUPLOAD) periodic function, or • Place the file in the CWDIRECTCP_UPLOAD_ DIRECTORY if the file storage API is not enabled, and then run the UPPRCCD Upload Price Code File (Program name PFR0134, Parameter PRICECDUPLOAD) periodic function, or • Use the Work with File Uploads (WUPL) menu option to upload the file. |
2. |
Submit the Price Code Upload process: • Execute the PCUPLD Price Code Upload Periodic Function. The system submits the process for the specified company. • Select Upload on the Work with Price Codes Screen. The system submits the process for the company you are currently in. The Record Type identifies the type of upload record: • PCO = Price Code upload record. • PCC = Price Code Customer upload record. • PCD = Price Code Detail upload record. |
3. |
The system updates the Processed field for each record in the Price Code Upload table to blank to include all records in the upload process. |
4. |
The system processes the records in the Price Code Upload table in Seq # order. |
5. |
The system validates each record in the Price Code Upload Table (PriceCdUpload). |
|
PCO Price Code Upload Record Validation The required fields for Record Type PCO Price Code are: • Company: Validated against the Company table. • Seq # • Record Type: Must be PCO. • PCO Price Code: Validated against the Price Code table. • Request Type: U = Create or update. D = Delete. - If the Request type is U and the price code does not exist in the Price Code table for the specified company, the system creates a new record in the Price Code table. - If the Request type is U and the price code exists in the Price Code table for the specified company, the system overlays the existing record in the Price Code table with the values that are passed in the upload. - If the Request type is D and the price code exists in the Price Code table for the specified company, the system deletes the existing record in the Price Code table and the associated records in the Price Code Customer and Price Code Details tables. - If the Request type is D and the price code does not exist in the Price Code table for the specified company, the system places the upload record in an error status. • Enter only one of the following: • PCO Special Price; also PCO Tax Special Price if the Tax Included in Price (E70) system control value is selected. • Group Price; also PCO Tax Group Price if the Tax Included in Price (E70) system control value is selected. Optionally, you can define: • Start Date and End Date: The end date cannot be earlier than the start date. • Distinct By: Valid values are ITEM, SKU, and CATEGORY. • Multiples: Valid values are Y and N; must be Y if a Distinct By setting or Group Price is defined. |
|
PCC Price Code Customer Upload Record Validation The required fields for Record Type PCC Price Code Customer are: • Company: Validated against the Company table. • Seq # • Record Type: Must be PCC. • PCO Price Code: Validated against the Price Code table. • Request Type: U = Create or update. - If the Request type is U and the price code does not exist in the Price Code Customer table for the specified company and customer or customer price group, the system creates a new record in the Price Code Customer table. - If the Request type is U and the price code exists in the Price Code Customer table for the specified company and customer or customer price group, the system overlays the existing record in the Price Code Customer table with the values that are passed in the upload. • Enter one of the following: - Customer: Validated against the Customer Sold To table. - Customer Price Group: Validated against the Customer Price Group table. |
|
PCD Price Code Detail Upload Record Validation The required fields for Record Type PCD Price Code Detail are: • Company: Validated against the Company table. • Seq # • Record Type: Must be PCD. • PCO Price Code: Validated against the Price Code table. • Request Type: U = Create or update. - If the Request type is U and the price code does not exist in the Price Code Details table for the specified company and item/SKU/offer or item/SKU/source code combination, the system creates a new record in the Price Code Details table. - If the Request type is U and the price code exists in the Price Code Details table for the specified company and item/SKU/offer or item/SKU/source code combination, the system overlays the existing record in the Price Code Details table with the values that are passed in the upload. • ITM Number: Validated against the Item table. • Enter one of the following: - Offer Number: Validated against the Offer table. - SRC Source Code: Validated against the Source Code table. Optionally, you can define: • SKU Code: Validated against the SKU table. Must be a valid SKU for the specified ITM Number. - If you define a SKU code, the price code applies only to the SKUs specified for the item in the Price Code Details table. - If you do not define a SKU Code, the price code applies to all SKUs of the item. |
6. |
If the record in the Price Code Upload table does not pass validation, the system: • Retains the record in the Price Code Upload Table (PriceCdUpload) and updates the Processed field to E Error. • Prints the record on the Price Code Upload Errors Report. A record in error remains in the Price Code Upload table until you fix or delete it. The system validates the record again the next time you submit the Price Code Upload Process. |
7. |
If the record in the Price Code Upload table passes validation, the system: • Creates, updates, or deletes the appropriate record in the Order Management System database, based on the Record Type and Request Type. • Deletes the record from the Price Code Upload Table (PriceCdUpload). • Prints the record on the Price Code Upload Errors Report. You can review or maintain: • Price Code records on the Work with Price Codes Screen in the Working with Price Codes (WPCD) menu option. • Price Code Customer records on the Work with Qualifying Customer/Price Groups Screen in the Working with Price Codes (WPCD) menu option. • Price Code Detail records using the Assigning Price Codes (APCD) menu option. |
The system includes Price Code Upload records that are in error on the Price Code Upload Errors Report.
Error |
Reason |
General Errors The following errors can occur for any Record Type. |
|
Invalid Company |
The Company is missing, invalid, or inactive. |
Invalid Seq# |
The Seq # is missing. |
Record Type not found |
The Record Type is missing or invalid. Valid values: • PCO = Price Code upload record; this record creates, updates, or deletes a record in the Price Code table. • PCC = Price Code Customer upload record; this record creates or updates a record in the Price Code Customer table. • PCD = Price Code Detail upload record; this record creates or updates a record in the Price Code Details table. |
Invalid Request Type |
The Request Type is missing or invalid. Valid values: • U = Valid for all Record Types. Create or update a record. The system determines whether the Price Code Upload record creates or updates an existing record by comparing the information in the Price Code Upload record with the existing records in the Order Management System table. • D = Valid only for Record Type PCO Price Code. Delete a Price Code record and its associated Price Code Customer records and Price Code Detail records. The system uses the information in the Price Code Upload record to determine which records to delete. |
Invalid Record Date |
The Record Date is missing or invalid. Must be in CYYMMDD format. |
Invalid Price Code |
The PCO Price Code is missing or invalid. |
Price Code Table Errors The following errors can occur if the Record Type for the record in the Price Code Upload table is PCO Price Code. |
|
Invalid Seq # |
The PCO Sequence is missing or invalid. |
Invalid Qty Req’d |
The PCO Qty Required is missing or invalid. |
Discount Missing |
You must enter a PCO Percent Discount, PCO Dollar Discount, PCO Special Price, or Group Price. |
Discount Conflict |
You can define only one type of discount: PCO Percent Discount, PCO Dollar Discount, PCO Special Price, or Group Price. |
Invalid Distinct by |
The Distinct By field is invalid. Valid values: • Blank = Distinct By setting not defined. • ITEM = The quantity required is by item. • SKU = The quantity required is by SKU. • CATEGORY = The quantity required is by item category. |
Invalid Multiples |
The Multiples field is invalid or the Distinct By field is populated and the Multiples field is not set to Y. Valid values: • Blank = Multiples setting not defined. • Y = Allow multiples. • N = Do not allow multiples. |
Invalid Start Date |
The Start Date is invalid. |
Invalid End Date |
The End Date is invalid. |
Invalid Field Populated |
Fields not related to the PCO Record Type are populated. |
Price Code Customer Table Errors The following errors can occur if the Record Type for the record in the Price Code Upload table is PCC Price Code Customer. |
|
Invalid Customer |
The Customer is invalid. |
Cust\CPG Conflict |
You must define a Customer or Customer Price Group for a PCC Price Code Customer upload record, but not both. |
Invalid Cust Prc Grp |
The Customer Price Group is invalid. |
Invalid PCC Record |
Fields not related to the PCC Record Type are populated. |
Price Code Detail Table Errors The following errors can occur if the Record Type for the record in the Price Code Upload table is PCD Price Code Detail. |
|
Invalid Item |
The ITM Number is missing or invalid. |
Invalid SKU |
The SKU Code is invalid. |
Invalid Offer |
The Offer Number is invalid. |
Offer\Src Conflict |
You must define an Offer Number or SRC Source Code for a PCD Price Code Detail upload record, but not both. |
Invalid Source |
The SRC Source Code is invalid. |
Invalid PCD Record |
Fields not related to the PCD Record Type are populated. |
| Assigning Price Codes (APCD) | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Price Code Upload Errors Report |
WPCDb OMS 17.1 September 2018 OTN