How (with Example Scripts) to Efficiently Load or Update Hundreds of Price Lists (Doc ID 395262.1)

  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
 

Oracle object types: using constructors

As PL/SQL developer you probably have worked with object types before and maybe you also know that you can add methods (i.e. functions and procedure which operate on the object type) to your type, but did not use them.
So why should you use them or not?

First of all, it is not mandatory to use methods when working with object types in the Oracle database, but it can be handy. Especially constructors can make working with object types in PL/SQL code even easier. A constructor is a special kind of method and is a function which returns a new instance of the object and sets up the initial values of the object type. When defining an object type (CREATE OR REPLACE TYPE {object type name} AS OBJECT), a default constructor is always implicitly defined. You use that constructor to create an new instance of the object type by providing all values. But what if you do not want to provide all values or even no values at all?

In that case you need to create a constructor explictly.
In the next example we create a new object type with a constructor with no arguments:

DROP  TYPE xxp2p.xxpo_pri_matrix_gl_codes_obj FORCE;

CREATE OR REPLACE TYPE xxp2p.xxpo_pri_matrix_gl_codes_obj AS OBJECT
                  (vndr_prilist_id NUMBER,
                   po_pri_matrix_gl_code_id NUMBER,
                   gl_code VARCHAR2 (240),
                   gl_code_desc         VARCHAR2(240),
                   attribute_category VARCHAR2 (240),
                   p_mode VARCHAR2 (1),
                   CONSTRUCTOR FUNCTION xxpo_pri_matrix_gl_codes_obj
                      RETURN SELF AS RESULT);

CREATE OR REPLACE TYPE BODY xxpo_pri_matrix_gl_codes_obj
IS
   CONSTRUCTOR FUNCTION xxpo_pri_matrix_gl_codes_obj
      RETURN SELF AS RESULT
   IS
   BEGIN
      RETURN;
   END;
END;

TYPE xxpo_pri_matrix_gl_codes_tobj as TABLE of xxpo_pri_matrix_gl_codes_obj;

Declaration:

lpo_pri_matrix_gl_codes_obj xxpo_pri_matrix_gl_codes_obj := new xxpo_pri_matrix_gl_codes_obj();

In case of table objects

lpo_pri_matrix_gl_codes_tobj xxpo_pri_matrix_gl_codes_tobj := new xxpo_pri_matrix_gl_codes_tobj();
lpo_pri_matrix_gl_codes_tobj.extend;
lpo_pri_matrix_gl_codes_tobj(lpo_pri_matrix_gl_codes_tobj.count) := new lpo_pri_matrix_gl_codes_obj();
Then only we can assign values.
lpo_pri_matrix_gl_codes_tobj(lpo_pri_matrix_gl_codes_tobj.count).vndr_prilist_id:=1;

Use Of Pricing API: QP_PRICE_LIST_PUB.Process_Price_List

Using QP_PRICE_LIST_PUB.Process_Price_List api,allows to import of large volumes of Price List data into QP tables

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.

Usage :

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.

To Create a New Price List:

DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');

BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');

FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := qp_list_headers_b_s.nextval;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_create;
v_price_list_rec.name := 'Example1';
v_price_list_rec.description := 'Creating sample price list';
v_price_list_rec.currency_code :='USD';
--Line Record Values
v_price_list_line_tbl(1).list_header_id := qp_list_headers_b_s.currval;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';

--Attribute Record Values

v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209955';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;

dbms_output.put_line('Calling API to Enter Item Into Price List');

QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);

COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
DBMS_OUTPUT.PUT_LINE(v_msg_count);
DBMS_OUTPUT.PUT_LINE(v_msg_data);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;



To update the existing price list with a new price list line:

DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('USER_ID');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');

BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');

FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := 230566;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
--Line Record Values
v_price_list_line_tbl(1).list_header_id := 230566;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';

--Attribute Record Values

v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209957';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;

dbms_output.put_line('Calling API to Enter Item Into Price List');

QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);

COMMIT;
IF (v_return_Status ='S') THEN
DBMS_OUTPUT.PUT_LINE('API Executed Successfully');
DBMS_OUTPUT.PUT_LINE(v_return_status);
ELSE
IF v_msg_count > 0 THEN
FOR l_index in 1..v_msg_count LOOP
dbms_output.put_line(l_index || '.' || SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('API Not Executed Successfully ');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR='||sqlerrm);
END;

Reference Links :
http://jyotioraapps.blogspot.com/2009/08/use-of-pricing-api-qppricelistpubproces.html
http://erpschools.com/scripts/

Demobld.sql, to create sample emp, dept tables

--
-- Copyright (c) Oracle Corporation 1988, 1999.  All Rights Reserved.
--
--  NAME
--    demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema.  It should be STARTed by each user wishing to
--   access the tables.  To remove the tables use the demodrop.sql
--   script.
--
--  USAGE
--       SQL> START demobld.sql
--
--

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

Oracle sql rows to columns transpose

Use any of the following SQL to transpose rows into a single column

SQL 1
SELECT substr(XMLAGG (XMLELEMENT ("T", e.job||',')).EXTRACT ('//text()') ,1,length(XMLAGG (XMLELEMENT ("T", e.job||',')).EXTRACT ('//text()'))-1)
  FROM emp e

More documentation on XMLAGG  function :

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm
SQL 2

SELECT LISTAGG(e.job, ', ') WITHIN GROUP (ORDER BY 1)
  FROM emp e

More documentation on LISTAGG function :  http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions089.htm#SQLRF51487