价目表操作

Pricing Data Bulk Loader APIAn Oracle White PaperApr 2009Pricing Data Bulk Loader APIOVERVIEWThis document gives an overview of the functionality provided through the Pricing Data Bulk Loader API. This functionality is available for Basic Pricing and Advanced Pricing. For a Basic Pricing installation, only the Basic Pricing data is imported into the system. The Pricing Data Bulk Loader API consists of a set of interface tables and a concurrent program. All validations and defaulting are performed before the data is inserted, deleted or updated. Records that are successfully loaded are automatically purged from the interface table(s).Purpose of introducing this APIThe Pricing Data Bulk Loader can be used as an alternative to the price list user interface (UI) and Price List Business Object API (QP_PRICE_LIST_PUB.Process_Price_List)for importing large volumes of price list data such as from a legacy system. This functionality wascreated to improve performance for this task. For details on the setup for advanced pricing, please refer to advanced pricing users guide, My Oracle Support Note 91798.1 - Order Management Product Suite Documentation.Your business practices may require that you upload price list data on a regular basis such as those described in the following scenarios:•When a new price list needs to be created by uploading a price change file.•When new product lines are created, new offerings to new channels, acquisitions of product lines or companies, newproduct rollout, and new price list creations.•When implementing a new sales territory, to bulk load existing price lists or to upload pricing from regions that may have theirpricing on spreadsheets.Important implementation Notes:How Entities Are UtilizedTable Name Select Insert Update Delete QP_INTERFACE_LIST_HEADERS X - X XQP_INTERFACE_LIST_LINES X - X XQP_INTERFACE_QUALIFIERS X - X XQP_INTERFACE_PRICING_ATTRIBS X - X XQP_INTERFACE_ERRORS - X - -QP_LIST_HEADERS_B - X X XQP_LIST_HEADERS_TL - X X XQP_LIST_LINES - X X XQP_QUALIFIERS - X X XQP_PRICING_ATTRIBUTES - X X XQP_RLTD_MODIFIERS - X X XInterface Tables•QP_INTERFACE_LIST_HEADERS•QP_INTERFACE_LIST_LINES•QP_INTERFACE_QUALIFIERS•QP_INTERFACE_PRICING_ATTRIBS•QP_INTERFACE_ERRORSSample Scripts Are ProvidedPatch #4900462 provides example scripts for Price List Bulk Loader feature introduced in 11.5.10 under $QP_TOP/patch/115/sql directory.•QPBLKEX1.sql - Script populates interface tables to insert price list header and price list line.•QPBLKEX2.sql - Script populates interface tables to insert price list header and price list line and pricing attributes.•QPBLKEX3.sql - Script populates interface tables to insert price list header and price break line.•QPBLKEX4.sql - Script populates interface tables to update price list header with qualifiers.•QPBLKEX5.sql - Script populates interface tables to attach secondary price lists to the primary price list.Date FormatsPopulate QP_INTERFACE_LIST_HEADERS.START_DATE_ACTIVE orEND_DATE_ACTIVE with format 'YYYY/MM/DD', (for example, 2006/06/24).See Note 416121.1 - How to set the start_date_active and end_date_active columnsin QP_INTERFACE_LIST_HEADERS. Type of start_date_active andend_date_active columns is VARCHAR2 in QP_INTERFACE_LIST_HEADERSand DATE in other tables.Pricing Data Bulk Loader API ImplementationAn overview of the tasks involved in the implementation for the Pricing Data Bulk Loader API is depicted in the following flowchartPurge the Interface tablesIf QP interface data is no longer needed, be sure to delete any outstanding records; especially for the orig_sys_header_ref that will be populated forthe next run of this API.Sample SQL that may be used for purging specific data:Delete from qp_interface_list_headers where orig_sys_header_ref='XYZ'; Delete from qp_interface_list_headers where orig_sys_header_ref='XYZ'; Delete from qp_interface_list_lines where orig_sys_header_ref='XYZ'; Delete from qp_interface_pricing_attribs where orig_sys_header_ref='XYZ'; Delete from qp_interface_errors where orig_sys_header_ref='XYZ'; Commit;Populate the Interface TablesThe pricing data bulk loader imports data from the following interface tables:•QP_INTERFACE_LIST_HEADERS: This table captures the Price List header data.•QP_INTERFACE_LIST_LINES: Price list line data is captured in this interface table.•QP_INTERFACE_QUALIFIERS: This table contains the header qualifiers associated with the price lists to be imported.•QP_INTERFACE_PRICING_ATTRIBS: The product and pricing attributes data is captured in this table.Gathering QP Interface Table StatisticsThis step is required to improve performance.1. Responsibility: System Administrator2. Navigate to: Concurrent > Request3. Submit a New Request4. Select request 'Gather Table Statistics'Ensure 'Estimate Percentage' parameter entered is 10 or greaterRun this request for the QP interface tables as listed above.Gather Statistics for QP and INV SchemasIf data has recently been loaded or entered into QP(Advanced Pricing) or INV (Inventory) or if it has been a while since statistics were gathered for these schemas, please gather fresh statistics for both schemas. See Note 419728.1 – How to Gather Statistics on Oracle Applications 11i and above.Profile Options used in Pricing Data Bulk LoaderThe following profile options should be set:•QP: Pricing Transaction Entity: When the Pricing Data Bulk Loader API is called, the price list data is picked up from theinterface table when the Pricing Transaction Entity attribute(column) is either:NULL or same as the profile value•QP: Source System Code: When the Pricing Data Bulk Loader API is called, the price list data is picked up from theinterface table when the Source System Code attribute(column) is either;NULL or same as the profile value•QP: Batch Size for Bulk Import: This profile valuedetermines the number of records loaded into the memory forprocessing. For improved performance, set an appropriatevalue for this profile based on your hardware configuration. Ifthe profile value is set too high, then the system may "hang."The default value is 1000.Run the Pricing Data Bulk Loader API1. Responsibility: Oracle Pricing Manager2. Navigate to: Reports3. Select Name = QP:Bulk Import of Price List4. Enter desired parameters•Entity: Indicates which pricing entity is to be processed. This defaults as PRL (price list). Currently only price lists(PRL) may be uploaded.•Entity Name: Enter the name of the price list to be processed. When the name is specified the price list is considered for import regardless of thevalues. If left blank, all entities of the selected Entity type available forprocessing in the interface tables will be processed: for example,PROCESS_STATUS_FLAG = 'P', PROCESS_FLAG = 'Y' andREQUEST_ID = NULL are considered for the import.•Process ID: This value corresponds to the process_id in the interface tables. The Process ID groups data in the interface table. You can selectcriteria to specify which records should be processed. If this field is leftblank, all available records for processing will be considered subject to therestrictions entered in Entity Name.•Process Type: This corresponds to the process type listed in the interface tables such as XML.•Process Parent: Select Yes (the default) or No to define if the loader should process a parent record if a child record has an error. This is applicable onlyto the List Line and Pricing Attributes parent-child relation.•No of Threads: Enter a number that indicates the total number of child processes or threads to achieve multi-threading for list lines and their childrecords. The default value is 1. Note:Multi-threading can impact the lengthof processing time required; therefore, it is recommended that you do notexceed the number of Central Processing Units (CPUs) available whenrunning the program. Ideally, schedule the concurrent request for a slowprocessing period or when there are few users on the system.•Turn Debug On: Valid values are Yes or No. If Yes, detailed debug messages are written to the concurrent program log file. As debugmessages add to the performance overhead, specifying No results inquicker completion of the concurrent request.Note: The field PROCESS_STATUS_CODE is updated to ‘E’ for records thatfail the validation logic of value to id conversionExample Case :Price List Name – Test001Description – Test001 Price ListCurrency – USDMulti-Currency Conversion – Corporate Pricelist Conversion list.Start date active – 01-Jan-2009End date active – 05-May-2009Price List Lines Details:Product Context – ItemProduct Attribute – Item NumberProduct Value –AS54888Value – 100Precedence – 220Start Date – JAN-05-2009Populate interface tables as follows.INSERT INTO qp_interface_list_headers(orig_sys_header_ref,list_type_code, NAME, description, active_flag, currency_code,currency_header, rounding_factor, source_lang, LANGUAGE,start_date_active, end_date_active,interface_action_code, process_flag, process_status_flag)VALUES('H_EXM123', 'PRL', 'Test001’, 'Test001 Price List','Y', 'USD', 'Corporate Pricelist Conversion list', -2,'US', 'US', '2009/01/01', '2009/05/05','INSERT', 'Y', 'P');INSERT INTO qp_interface_list_lines(orig_sys_line_ref,orig_sys_header_ref, list_line_type_code,start_date_active,arithmetic_operator, operand, primary_uom_flag,product_precedence, interface_action_code,process_flag, process_status_flag)VALUES('L_EXM123', 'H_EXM123','PLL', To_Date('JAN-05-2009', 'MON-DD-YYYY'),'UNIT_PRICE', 100, 'Y', 220,'INSERT', 'Y', 'P');INSERT INTO qp_interface_pricing_attribs(orig_sys_pricing_attr_ref,orig_sys_line_ref, orig_sys_header_ref,product_attribute_context, product_attr_code, product_attr_val_disp,product_uom_code,interface_action_code, process_flag, process_status_flag)VALUES('A_EXM123','L_EXM123', 'H_EXM123','ITEM', 'INVENTORY_ITEM_ID', 'AS54888','Ea', 'INSERT', 'Y', 'P');Populate the Interface tables as follows to updatethe list price to 200 and UOM to DZ of the above price list.INSERT INTO qp_interface_list_headers(orig_sys_header_ref, NAME, list_type_code, interface_action_code,process_flag, process_status_flag)VALUES('H_EXM123', ‘Test001’, 'PRL', 'UPDATE', 'Y', 'P');INSERT INTO qp_interface_list_lines(orig_sys_line_ref,orig_sys_header_ref, list_line_type_code,start_date_active,arithmetic_operator, operand,interface_action_code, process_flag, process_status_flag)VALUES('L_EXM123', 'H_EXM123','PLL', To_Date('JAN-05-2009', 'MON-DD-YYYY'),'UNIT_PRICE', 200,'UPDATE', 'Y', 'P');INSERT INTO qp_interface_pricing_attribs(orig_sys_pricing_attr_ref,orig_sys_line_ref, orig_sys_header_ref,product_attribute_context, product_attr_code, product_attr_val,product_uom_code,interface_action_code, process_flag, process_status_flag)VALUES('A_EXM123', 'L_EXM123', 'H_EXM123','ITEM', 'INVENTORY_ITEM_ID', 'AS54888','DZ','UPDATE', 'Y', 'P');Note the format of start_date_active and end_date_active and process_status_flag = P and process_flag = Y.OTHER BUSINESS SCENARIOSSeveral other common business scenarios are provided below to assistyou in better understanding the setups required.Scenario 1: Script to Insert ALL_ITEMSREM /**************************************************************************REM * The columns INTERFACE_ACTION_CODE, PROCESS_FLAG andPROCESS_STATUS_FLAGREM are mandatory and should be populated in all the interface tables with appropriate values.REM * Errors in the process are inserted into QP_INTERFACE_ERRORS table.REM * Please read Oracle Electronic Technical Reference Manual (eTRM) to understand REM interface tables, columns and interface tables relationship.REM * Please read Oracle Advanced Pricing Implementation Guide(Metalink Note: 91798.1) REM for details on QP: Bulk Import of Price List concurrent program and parameters. REM***************************************************************************/-- Script to Insert ALL_ITEMS/* Insert Price list header details into QP_INTERFACE_LIST_HEADERS table */INSERT INTO QP_INTERFACE_LIST_HEADERS(ORIG_SYS_HEADER_REF,LIST_TYPE_CODE,NAME,DESCRIPTION,CURRENCY_CODE,ACTIVE_FLAG,CURRENCY_HEADER_ID,START_DATE_ACTIVE,END_DATE_ACTIVE,ROUNDING_FACTOR,SOURCE_LANG,LANGUAGE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_HEADER_SMITHA_SB13', /* Unique identification of the price list in the external applicationfrom where the price list is imported. */'PRL', /* List Type Code. PRL for standard price list */'Sample_BLK_PL_10', /* Price List Name */'Sample Bulk Loaded Price List',/* Description */'USD', /* Currency Code. The currency of the price list.*/'Y', /* Active Flag */30, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name. */TO_CHAR(sysdate,'YYYY/MM/DD'), /* Start Date Active */NULL, /* End Date Active */-1, /* Rounding Factor */'US', /* Source Language */'US', /* Language */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */INSERT INTO QP_INTERFACE_LIST_LINES (ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,LIST_LINE_TYPE_CODE,START_DATE_ACTIVE,END_DATE_ACTIVE,ARITHMETIC_OPERATOR,OPERAND,PRIMARY_UOM_FLAG,PRODUCT_PRECEDENCE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_LINE_10', /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of theequivalent of the price list line table of the external system from where the price data is loaded.*/'SAMPLE_HEADER_10', /* orig_sys_header_ref */'PLL', /* List Line Type Code. PLL for price list line. */sysdate, /* Start date active */NULL, /* End date active */'UNIT_PRICE', /* Arithmetic operator */150, /* operand */'Y', /* Primary UOM Flag */230, /* Product Precedence */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */ INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (ORIG_SYS_PRICING_ATTR_REF,ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTR_CODE,PRODUCT_ATTR_VAL_DISP,--product_attr_value,PRODUCT_UOM_CODE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_PATTR_10', /* The combination of ORIG_SYS_HEADER_REF,ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REFis the primary key of the equivalent of the pricing attribute table of the externalsystem from where the price data is loaded.*/'SAMPLE_LINE_10', /* orig_sys_line_ref */'SAMPLE_HEADER_10', /* orig_sys_header_ref */'ITEM', /* Product Attribute Context */'ALL_ITEMS', /* Product Attribute Code */'ALL', /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with inventory item id of the item.*/--1, /*for category id MISC.MISC*/'Ea', /* Product UOM */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);COMMIT;EXIT;Scenario 2: Script to Insert Item NumberREM /**************************************************************************REM * The columns INTERFACE_ACTION_CODE, PROCESS_FLAG andPROCESS_STATUS_FLAGREM are mandatory and should be populated in all the interface tables with appropriate values.REM * Errors in the process are inserted into QP_INTERFACE_ERRORS table.REM * Please read Oracle Electronic Technical Reference Manual (eTRM) to understand REM interface tables, columns and interface tables relationship.REM * Please read Oracle Advanced Pricing Implementation Guide(Metalink Note: 91798.1) REM for details on QP: Bulk Import of Price List concurrent program and parameters. REM***************************************************************************/-- script to insert Item number/* Insert Price list header details into QP_INTERFACE_LIST_HEADERS table */INSERT INTO QP_INTERFACE_LIST_HEADERS(ORIG_SYS_HEADER_REF,LIST_TYPE_CODE,NAME,DESCRIPTION,CURRENCY_CODE,ACTIVE_FLAG,CURRENCY_HEADER_ID,START_DATE_ACTIVE,END_DATE_ACTIVE,ROUNDING_FACTOR,SOURCE_LANG,LANGUAGE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_HEADER_10', /* Unique identification of the price list in the external applicationfrom where the price list is imported. */'PRL', /* List Type Code. PRL for standard price list */'Sample_BLK_PL_10', /* Price List Name */'Sample Bulk Loaded Price List',/* Description */'USD', /* Currency Code. The currency of the price list.*/'Y', /* Active Flag */30, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name. */TO_CHAR(sysdate,'YYYY/MM/DD'), /* Start Date Active */NULL, /* End Date Active */-1, /* Rounding Factor */'US', /* Source Language */'US', /* Language */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */INSERT INTO QP_INTERFACE_LIST_LINES (ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,LIST_LINE_TYPE_CODE,START_DATE_ACTIVE,END_DATE_ACTIVE,ARITHMETIC_OPERATOR,OPERAND,PRIMARY_UOM_FLAG,PRODUCT_PRECEDENCE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_LINE_10', /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of theequivalent of the price list line table of the external system from wherethe price data is loaded.*/'SAMPLE_HEADER_10', /* orig_sys_header_ref */'PLL', /* List Line Type Code. PLL for price list line. */sysdate, /* Start date active */NULL, /* End date active */'UNIT_PRICE', /* Arithmetic operator */150, /* operand */'Y', /* Primary UOM Flag */230, /* Product Precedence */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (ORIG_SYS_PRICING_ATTR_REF,ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTR_CODE,PRODUCT_ATTR_VAL_DISP,PRODUCT_UOM_CODE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_PATTR_10', /* The combination of ORIG_SYS_HEADER_REF,ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REFis the primary key of the equivalent of the pricing attribute table of the externalsystem from where the price data is loaded.*/'SAMPLE_LINE_10', /* orig_sys_line_ref */'SAMPLE_HEADER_10', /* orig_sys_header_ref */'ITEM', /* Product Attribute Context */'INVENTORY_ITEM_ID', /* Product Attribute Code */'AS54888', /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with inventory item id of the item.*/'Ea', /* Product UOM */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);COMMIT;EXIT;Scenario 3: Script to Insert Item CategoryREM /**************************************************************************REM * The columns INTERFACE_ACTION_CODE, PROCESS_FLAG andPROCESS_STATUS_FLAGREM are mandatory and should be populated in all the interface tables with appropriate values.REM * Errors in the process are inserted into QP_INTERFACE_ERRORS table.REM * Please read Oracle Electronic Technical Reference Manual (eTRM) to understand REM interface tables, columns and interface tables relationship.REM * Please read Oracle Advanced Pricing Implementation Guide(Metalink Note: 91798.1) REM for details on QP: Bulk Import of Price List concurrent program and parameters. REM***************************************************************************/-- script to insert item category/* Insert Price list header details into QP_INTERFACE_LIST_HEADERS table */INSERT INTO QP_INTERFACE_LIST_HEADERS(ORIG_SYS_HEADER_REF,LIST_TYPE_CODE,NAME,DESCRIPTION,CURRENCY_CODE,ACTIVE_FLAG,CURRENCY_HEADER_ID,START_DATE_ACTIVE,END_DATE_ACTIVE,ROUNDING_FACTOR,SOURCE_LANG,LANGUAGE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_HEADER_12', /* Unique identification of the price list in the external applicationfrom where the price list is imported. */'PRL', /* List Type Code. PRL for standard price list */'Sample_BLK_PL_12', /* Price List Name */'Sample Bulk Loaded Price List',/* Description */'USD', /* Currency Code. The currency of the price list.*/'Y', /* Active Flag */30, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name. */TO_CHAR(sysdate,'YYYY/MM/DD'), /* Start Date Active */NULL, /* End Date Active */-1, /* Rounding Factor */'US', /* Source Language */'US', /* Language */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */INSERT INTO QP_INTERFACE_LIST_LINES (ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,LIST_LINE_TYPE_CODE,START_DATE_ACTIVE,END_DATE_ACTIVE,ARITHMETIC_OPERATOR,OPERAND,PRIMARY_UOM_FLAG,PRODUCT_PRECEDENCE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_LINE_12', /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of theequivalent of the price list line table of the external system from wherethe price data is loaded.*/'SAMPLE_HEADER_12', /* orig_sys_header_ref */'PLL', /* List Line Type Code. PLL for price list line. */sysdate, /* Start date active */NULL, /* End date active */'UNIT_PRICE', /* Arithmetic operator */150, /* operand */'Y', /* Primary UOM Flag */230, /* Product Precedence */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE. */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (ORIG_SYS_PRICING_ATTR_REF,ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF,PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTR_CODE,--PRODUCT_ATTR_VAL_DISP,product_attr_value,PRODUCT_UOM_CODE,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG) VALUES('SAMPLE_PATTR_12', /* The combination of ORIG_SYS_HEADER_REF,ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REFis the primary key of the equivalent of the pricing attribute table of the externalsystem from where the price data is loaded.*/'SAMPLE_LINE_12', /* orig_sys_line_ref */'SAMPLE_HEADER_12', /* orig_sys_header_ref */'ITEM', /* Product Attribute Context */'ITEM_CATEGORY', /* Product Attribute Code */--'MISC.MISC', /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with inventory item id of the item.*/1, /*for category id MISC.MISC*/'Ea', /* Product UOM */'INSERT', /* Interface Action Code. The possible values areINSERT/UPDATE/DELETE */'Y', /* Process flag for transaction */'P' /* Process Status Flag for transaction */);COMMIT;EXIT;Scenario 4: Script to Update Header/Line orAttribute DetailsREM /**************************************************************************REM Please Note:REM * The columns INTERFACE_ACTION_CODE, PROCESS_FLAG andPROCESS_STATUS_FLAGREM are mandatory and should be populated in all the interface tables with appropriate values.REM * Errors in the process are inserted into QP_INTERFACE_ERRORS table.REM * Please read Oracle Electronic Technical Reference Manual (eTRM) to understandREM interface tables, columns and interface tables relationship.REM * Please read Oracle Advanced Pricing Implementation Guide(Metalink Note: 91798.1) REM for details on QP: Bulk Import of Price List concurrent program and parameters.REM***************************************************************************/--Script to update Header/Line or Attribute details/* Insert Price list header details into QP_INTERFACE_LIST_HEADERS table */INSERT INTO QP_INTERFACE_LIST_HEADERS(LIST_HEADER_ID,LIST_TYPE_CODE,INTERFACE_ACTION_CODE,PROCESS_STATUS_FLAG,process_flag,ORIG_SYS_HEADER_REF) VALUES(234415,'PRL' /* List Type Code. PRL for standard price list */,'UPDATE' /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */,'P' /* Process Status Flag for transaction */,'Y','SAMPLE_HEADER_10');/* Insert Price list line(PLL) details into QP_INTERFACE_LIST_LINES table. */ INSERT INTO QP_INTERFACE_LIST_LINES (LIST_LINE_ID,LIST_HEADER_ID,LIST_LINE_TYPE_CODE,INTERFACE_ACTION_CODE,PROCESS_STATUS_FLAG,OPERAND,process_flag,ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF) VALUES(288720,234415,'PLL','UPDATE' /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */,'P' /* Process Status Flag for transaction */,800,'Y','SAMPLE_LINE_10','SAMPLE_HEADER_10');/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */ INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (PRICING_ATTRIBUTE_ID,LIST_LINE_ID,INTERFACE_ACTION_CODE,PROCESS_FLAG,PROCESS_STATUS_FLAG,product_uom_code,ORIG_SYS_PRICING_ATTR_REF,ORIG_SYS_LINE_REF,ORIG_SYS_HEADER_REF) VALUES(289676,288720,'UPDATE' /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE */,'Y' /* Process flag for transaction */,'P' /* Process Status Flag for transaction */,'DZ','SAMPLE_PATTR_10','SAMPLE_LINE_10','SAMPLE_HEADER_10');COMMIT;EXIT;。