A. QP: Bulk Import of Price List
Pricing Data Bulk Loader Example Scripts
1. QPBLKEX1.sql
2. QPBLKEX2.sql
3. QPBLKEX3.sql
4. QPBLKEX4.sql
5. QPBLKEX5.sql
B. Price List Setup API. (QP_PRICE_LIST_PUB.Process_Price_List)
Examples For the Price List Setup API
1. QPPLXMP1.sql
2. QPPLXMP2.sql
3. QPPLXMP3.sql
4. QPPLXMP4.sql in $QP_TOP/patch/115/sql directory:
5.QPPLXMP5.sql
Information in this document applies to any platform.
EXECUTABLE:QPXVBLK - Pricelist Bulk Loader
GOAL
How does one load over 200 customer price lists?
Is there any customer sales price list API which we can use?
SOLUTION
Oracle Pricing provides the ability to allow for the import of large volumes of Price List data into the Pricing Tables. The following two methods, A) QP: Bulk Import of Price List or B) Pricing API(QP_PRICE_LIST_PUB.Process_Price_List) may be used to populate the price list.
It is recommended to use the QP: Bulk Import of Price List as it provides best performance.
A. QP: Bulk Import of Price List
This is the recommended approach providing best performance when loading price lists.
This feature is available for both Basic and Advanced Pricing and is provided within Oracle Applications 11.5.10 and Oracle Applications Release 12.
Users first load the interface tables using any method of choice including PLSQL, XML, Excel spreadsheets then run concurrent program QP: Bulk Import of Price Lists to load the price list information. This method is more user-friendly and provides best performance.
Pricing Data Bulk Loader Example Scripts
The following example scripts are found under directory $QP_TOP/patch/115/sql.
1. QPBLKEX1.sql
Script populates interface tables to insert price list header and price list line.
2. QPBLKEX2.sql
Script populates interface tables to insert price list header and price list line and pricing attributes.
3. QPBLKEX3.sql
Script populates interface tables to insert price list header and price break line.
4. QPBLKEX4.sql
Script populates interface tables to update price list header with qualifiers.
5. QPBLKEX5.sql
Script populates interface tables to attach secondary price lists to the primary price list
*Note: For Oracle Applications 11.5.10, one may need to apply Patch 4900462 to obtain the scripts.
Additional Pricing Data Bulk Loader Resources
Note 435475.1 QP: Bulk Import of Price List - Master List - A Good Place To Start for Oracle Applications 11.5.10 and Higher
Note 435473.1 QP: Bulk Import of Price List - White Paper
Note 435467.1 QP: Bulk Import of Price List - Troubleshooting Guide
Note 432877.1 QP: Bulk Import of Price List - Current Issues For Oracle Applications 11.5.10
*Be sure to review this note as some patches may need to be applied before testing your scripts.
Note 1182973.1 QP: Bulk Import of Price List - Current Issues For Oracle Applications R12
*Be sure to review this note as some patches may need to be applied before testing your scripts.
Oracle Advanced Pricing
Implementation Manual
Release 12
Part No. B31440-01
Chapter 6 - Pricing Data Bulk Loader
Oracle Advanced Pricing Implementation Manual
Release 11i
Part No. B14385-01
Chapter 6 - Pricing Data Bulk Loader
B. Price List Setup API. (QP_PRICE_LIST_PUB.Process_Price_List)
The Price List Setup package consists of entities to set up price lists.
The Price List Setup package QP_Price_List_PUB.Process_Price_List contains the
following public record type and table of records entities:
■ Process_Price_List: QP_Price_List_PUB.Process_Price_List:.Takes two record types and six table types as input parameters. Use this API to insert, update, and delete price lists and to set up a price list for a given p_PRICE_LIST_rec record structure.
You can:
■ Set up multiple price list lines by giving multiple price list line definitions in the p_ PRICE_LIST_LINE_tbl table structure.
■ Attach multiple qualifiers at the price list header level by giving multiple qualifiers in the p_QUALIFIERS_tbl table structure.
■ Attach multiple pricing attributes to price list lines by giving the pricing attributes in the p_PRICING_ATTR_tbl table structure.
■ Price_List_Rec_Type: Corresponds to the columns in the price list header tables QP_ LIST_HEADERS_B and QP_LIST_HEADERS_TL.
■ Price_List_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list header table QP_LIST_HEADERS_B, for example, Currency.
■ Price_List_Line_Rec_Type: Corresponds to columns in the price list line table and related modifiers tables QP_LIST_LINES and QP_RLTD_MODIFIERS.
■ Price_List_Line_Tbl_Type: Table of Price_List_Line_Rec_Type.
■ Price_List_Line_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list line table QP_LIST_LINES, for example, Price_By_Formula.
■ Price_List_Line_Val_Tbl_Type: Table of Price_List_Line_Val_Rec_Type.
■ Qualifiers_Rec_Type: Corresponds to the columns in the qualifier table QP_QUALIFIERS.
■ Qualifiers_Tbl_Type: Table of Qualifiers_Rec_Type.
■ Qualifiers_Val_Rec_Type: Made up of attributes that store the meaning of id or code columns in the qualifiers table QP_QUALIFIERS, for example, Qualifier_Rule.
■ Qualifiers_Val_Tbl_Type: Table of Qualifiers_Val_Rec_Type.
■ Pricing_Attr_Rec_Type: Corresponds to the columns in the pricing attributes table QP_ PRICING_ATTRIBUTES.
■ Pricing_Attr_Tbl_Type: Table of Pricing_Attr_Rec_Type.
■ Pricing_Attr_Val_Rec_Type: Attributes that store the meaning of id or code columns in the pricing attributes table QP_PRICING_ATTRIBUTES, for example, Accumulate.
■ Pricing_Attr_Val_Tbl_Type: Table of Pricing_Attr_Val_Rec_Type.
Examples For the Price List Setup API
The following example scripts are found under directory $QP_TOP/patch/115/sql.
1. QPPLXMP1.sql
Sample script which inserts a Price List with 3 price list lines, and the product information for each of the lines(Product Information is stored in pricing attributes table in product attribute columns). This sample price list does not have any qualifiers or price breaks or non product-information type of pricing attributes. This script must be modified by the user such that the qpr_pricing_attr_tbl(J).product_attr_value columns (for J = 1 to 3) are populated with 3 different valid inventory_item_id from the instance where this script is run.
2. QPPLXMP2.sql
Sample script which inserts a Price List with 3 price list lines, and the product information for each of the lines(Product Information is stored in pricing attributes table in product attribute columns), 1 secondary price list, 2 price list qualifiers(Only header level qualifiers supported for pricelists) and 2 non-product pricing attributes per price list line. This sample price list does not have any price breaks. This script must be modified by the user such that the qpr_pricing_attr_tbl(J).product_attr_value
columns are populated with valid inventory_item_id's from the instance where this script is run. Also the qpr_qualifiers_tbl(I).list_header_id must be populated with a valid list_header_id of a price list.
3. QPPLXMP3.sql
Sample script which inserts a Price List with 1 price list line and the product information for this line (Product Information is stored in pricing attributes table in product attribute columns), 1 non-product pricing attribute for the price list line and 1 price list qualifier(Only header level qualifiers supported for pricelists). The qualifier and pricing attribute record passes the display value of the qualifier attribute value and pricing attribute value columns respectively instead of the hidden id. Simultaneously, the qualifier_attribute and pricing_attribute segment name must be passed instead of the pricing attribute itself. This is done using the corresponding val_tbl structure for qualifiers and pricing attributes.
4. QPPLXMP4.sql in $QP_TOP/patch/115/sql directory:
Sample script which inserts a Price List with 1 price list line of type PBH price break header) and the product information for this line(Product Information is stored in pricing attributes table in product attribute columns), a regular pricing attribute(non product) and a Price Break Child Line (To create a price break child line it is necessary to create a combination of a list line
and a pricing attribute where the pricing attribute can only have the Volume pricing context and Item Quantity Pricing Attribute).
5.QPPLXMP5.sql
Sample script to which updates the list price on a Price List Line , This script must be modified by the user such that the qpr_list_line_tbl(K).list_line_id column is populated with a valid list_line_id from the instance where this script is run.
Additional Process Price List Resources:
Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide Release 12
Part No. E18000-01
Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide Release 11i
Part No. B14446-01
Oracle® Advanced Pricing User's Guide Release 12
Part No. B31581-02
Oracle Advanced Pricing User’s Guide Release 11i
Part No. B13966-01
Note 445132.1 QPXRQSRC: Unable To Create Price List Lines For Purchased Items Using an
API QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
Note 444795.1 ORACLE ERROR 4030 IN FDPSTP When Importing In 3000 Line Price List Via API
Note 375610.1 Unable To Load PRIMARY_UOM_FLAG using the sample code QPPLXMP1.sql
Using API QP_PRICE_LIST_PUB.Process_Price_List
Note 362667.1 Sample API to Delete Price List Lines from Price List
Still Have Questions? To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Advanced Pricing Community.
REFERENCES
NOTE:412220.1 - HOW TO IMPORT PRICE LIST LINE WHEN ORIG_SYS_HEADER_REF I S NULLON THE PRICE LIST HEADER
NOTE:432877.1 - QP: Bulk Import of Price List - Current Issues For Oracle Applications 11.5.10
NOTE:375610.1 - Unable To Load PRIMARY_UOM_FLAG using the sample code QPPLXMP1.sql Using API QP_PRICE_LIST_PUB.Process_Price_List
NOTE:435475.1 - QPXVBLK: QP: Bulk Import of Price List - Master List - A Good Place To Start for Oracle Applications 11.5.10 and Higher Bulk Load API
NOTE:1182973.1 - QP: Bulk Import of Price List - Current Issues For Oracle Applications R12
NOTE:435467.1 - QPXVBLK: QP: Bulk Import of Price List - Troubleshooting - Including Sample Scripts
NOTE:435473.1 - Whitepaper for QPXVBLK: Pricing Data Bulk Loader API Including QP: Bulk Import of Price List Implementation Details with Sample Scripts
NOTE:362667.1 - Sample API to Delete Price List Lines from Price List
NOTE:445132.1 - QPXRQSRC: Unable To Create Price List Lines For Purchased Items Using an API QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
NOTE:444795.1 - ORACLE ERROR 4030 IN FDPSTP When Importing In 30000 Line Price List Using QP_PRICE_LIST_PUB.Process_Price_List API
Source : support.oracle.com
No comments:
Post a Comment