Pricing Data Bulk Loader

This chapter covers the following topics:

• Overview of Pricing Data Bulk Loader
• Populating the Interface Table
• QP: Bulk Import of Price List Program

Overview of Pricing Data Bulk Loader

The Pricing Data Bulk Loader application programming interface (API), which is available in Basic and Advanced Pricing, enables you to complete the following tasks:

• Import new price lists.

• Update and delete price list data.

This Pricing Data Bulk Loader API consists of a set of interface tables and a concurrent program. All validations and defaulting is done before the data is inserted or updated. The Pricing Data Bulk Loader can be used as an alternative to the price list user interface (UI) and Price List Business Object API for importing large volumes of price list data such as from a legacy system.

The following price lists are not imported by the Pricing Data Bulk Loader:

• Price lists that are specific to pricing agreements (List Type Code of AGR)

• Price lists that are created from sales agreements (List Source Code of BSO)

• Price lists that are created from service contracts (List Source Code OKS)

Profile Options That Are Used in Pricing Data Bulk Loader

The following profile options are used with the Pricing Data Bulk Loader API:

• QP: Pricing Transaction Entity: When the Pricing Data Bulk Loader API is called, the price list data is picked up from the interface table when the Pricing Transaction Entity attribute (column) is either:

  • NULL

  • 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 the interface table when the Source System Code attribute (column) is either:

  • NULL

  • Same as the profile value

• QP: Batch Size for Bulk Import: This profile value determines the number of records that are loaded into the memory for processing. For improved performance, set an appropriate value for this profile based on your hardware configuration. If the profile value is set too high, then the system may "suspend." The default value is 1000.

  Note: For a Basic Pricing installation, only the Basic Pricing data is imported into the system.

Related Topics

Examples of the Interface Table Setup

Populating the Interface Table

As a first step, the Pricing Data Bulk Loader imports data from the following interface tables:

• QP_INTERFACE_LIST_HEADERS: This table captures the Price List header data.

  Important: START_DATE_ACTIVE, END_DATE_ACTIVE: When populating the interface tables ensure that the following format is used for the start and end dates: YYYY/MM/DD (for example, 2006/06/24):

  Data type	Format
  Date	YYYY/MM/DD
  DateTime	YYYY/MM/DD HH:MM:SS

• QP_INTERFACE_LIST_LINES: Price list line data is captured in this interface table.

• QP_INTERFACE_QUALIFIERS: This table contains the header qualifiers that are associated with the price lists to be imported.

• QP_INTERFACE_PRICING_ATTRIBS: The product and pricing attributes data is captured in this table. When updating the product value for a price list line, you can use the product_attr_value column and the Product_attr_val_Disp column in the qp_interface_pricing_attribs table.

Attributes in the Interface Tables

The following key attributes in the interface tables control the import of the price list data:

• ORIG_SYS_HEADER_REF: This attribute is used in all four interface tables. In the QP_ INTERFACE_LIST_HEADERS table, the value in this attribute uniquely identifies a price list header and refers to the primary key of the price list header record in the external/legacy system.

  For the other interface tables, this attribute value determines the association of the respective entity with the price list header. This attribute is stored in the price list core tables as a map between the price list data and the data in the interface tables. It is used with Update and Delete actions for interface table records. Therefore, during Insert, the uniqueness of this attribute in the price lists data is checked.

• ORIG_SYS_LINE_REF: This attribute is used in the tables: QP_INTERFACE_LIST_LINES and QP_ INTERFACE_PRICING_ATTRIBS. Similar to the ORIG_SYS_HEADER_REF, this field uniquely identifies a price list line. For updating and deleting a price list line, this attribute value identifies the price list line. Also the uniqueness of this field is checked during insert.

• ORG_SYS_QUALIFIER_REF: This attribute is used in the table: QP_INTERFACE_QUALIFIERS. Similar to the ORIG_SYS_HEADER_REF, this field uniquely identifies a price list qualifier. For updating and deleting a qualifier, this attribute value is used to identify the qualifier. Also the uniqueness of this field is checked during insert.

• ORG_SYS_PRICING_ATTR_REF: This attribute is used in the table: QP_INTERFACE_PRICING_ATTRIBS. Similar to the ORIG_SYS_HEADER_REF, this field uniquely identifies a product/pricing attribute.

  When you are updating and deleting, this attribute value identifies the product or pricing attribute. Also, the uniqueness of this field is validated during insert.

• PROCESS_STATUS_FLAG: This attribute is used in all four interface tables, and indicates the status of a record during the import process. The API processes the records only when the value of the attribute is P. The records with errors display a value of NULL in this field. The successfully processed records will have the value I in this field, but all the successful records are deleted at the end of the process, so no records will exist with this attribute value I.

• INTERFACE_ACTION_CODE: This attribute is used in all four interface tables. The attribute indicates the action to be performed on the record:

  • INSERT: Indicates that the entity needs to be inserted into the price list.

  • UPDATE: Indicates that the entity needs to be updated.

  • DELETE: Indicates that the entity needs to be deleted.

Updating or Deleting Price Lists using the Bulk Loader

When you update or delete price lists, all original system reference columns must be populated on the interface tables to identify the record being updated. For example, to delete or update header records, specify orig_sys_header_ref, and for line records, specify orig_sys_header_ref and orig_sys_line_ref.

For internal price lists or pricing APIs

For internal price lists created from pricing user interfaces or from pricing APIs where original system reference columns were not entered by the user, the system defaults these columns. For you to populate the original system reference columns for data created prior to the R12 release, you must run the concurrent program QP: Upgrade Pricing data for Bulk Loader. For data to be updated or deleted on these internal price lists, these defaulted values must be populated in original system reference columns on the interface table:

The Orig_sys_header_ref column on all entities (list header, list lines, pricing attributes, qualifiers) defaults to list_header_id prefixed by INT (for Internal).

Important: The prefix INT is only for the upgraded price list headers from 11.5.10 and earlier releases. For price lists created in 12.0 and later releases, the prefix INT is not added. For example, if the header_id is 12345, the orig_sys_header_ref for all entities for that price list will default to INT12345.

Other original system reference columns on list line, pricing attribute, and qualifier entities, default to the primary key ID value. As an example, orig_sys_line_ref defaults to the list_line_id. If there are duplicate orig_sys_header_ref, the prefix "INT-D-" is added to the list_header_id.

Here are some examples of how the naming conventions are applied:

Price List Header Name	List_header_id	Orig_sys_hdr_ref (After Naming Convention Applied)
PL_11510	12345	INT12345
PL_120	67890	67890
PL_120	67890	INT-D-67890 Note: If the Orig_sys_hdr_ref = 67890 already exists for another price list, then the prefix "INT-D-" is added.

Notes:

• If you are using the bulk loader operation to update a price break child line and to delete the price break simultaneously (a usage not recommended), then both the price break and the price break child line are deleted. Similarly, you cannot update the price break child line and delete the pricing attribute because a price break child line must have a pricing attribute. The correct usage is to delete (using DELETE action) both the line and the pricing attribute so that no error message appears.

• For the table qp_interface_list_headers, do not use the INSERT and UPDATE operations for the same price list at the same time (the bulk loader cannot create and update the price list header information in the same run). For example, the following setup shows the INSERT and UPDATE operations being used at the same time:

  INTERFACE_ACTION_CODE	HEADER_ID	ORIG_SYS_HEADER_REF
  INSERT	__	123
  UPDATE	__	123
  UPDATE	__	123

  Line 1: Create a new price list with orig_sys_header_ref of 123.

  Line 2: Update a price list with orig_sys_header_ref of 123.

  Line 3: Same as line 2.

To update selected columns:

For updating selected columns, you need to enter only updated column values on the interface tables. For example, to update only the operand column for a price list line, specify the original system reference columns to identify the record and new value for the operand column on the list lines interface table.

To update a column to a null value populate that column in the interface table with one of the following constants:

• QP_BULK_LOADER_PUB.Get_Null_Date for column of datatype Date

• QP_BULK_LOADER_PUB.Get_Null_Char for column of datatype Varchar2

• QP_BULK_LOADER_PUB.Get_Null_Number for column of datatype Number

Note: To do validations, the Pricing Data Bulk Loader populates all the empty columns on the interface tables (set to current values in the database) with the current data in the database. In the previous example, all columns other than the operand column will be updated by the bulk loader. If errors occur, you might see all columns populated after the bulk loader is run even though the user only populated the operand column.

If you are creating, updating or deleting a child record, the parent record (and grandparent record if applicable) must be present in the interface table. Even if the parent (and grandparent) record does not need any processing (insert, update, or delete) it should still be present with process_status_flag = I and original system columns populated. If the header record does not exist, child entity records will not be processed by the bulk loader.

Table Names and Upload Action Validations

Table Name	Select	Insert	Update	Delete
QP_INTERFACE_LIST_HEADER	X	NA	X	X
QP_INTERFACE_LIST_LINES	X	NA	X	X
QP_INTERFACE_QUALIFIERS	X	NA	X	X
QP_INTERFACE_PRICING_ATTRIBS	X	NA	X	X
QP_INTERFACE_ERRORS	NA	X	NA	NA
QP_LIST_HEADERS_B	NA	X	X	X
QP_LIST_HEADERS_TL	NA	X	X	X
QP_LIST_LINES	NA	X	X	X
QP_QUALIFIERS	NA	X	X	X
QP_PRICING_ATTRIBUTES	NA	X	X	X
QP_RLTD_MODIFIERS	NA	X	X	X

Note: NA = Not applicable

Examples of the Interface Table Setup

The following sample scripts show how to set up the interface tables for the Pricing Data Bulk Loader feature. The scripts are located in the $QP_TOP/patch/115/sql directory:

• QPBLKEX1.sql: Script to insert price list header and price list line.

• QPBLKEX2.sql: Script to insert price list header and price list line and pricing attributes.

• QPBLKEX3.sql: Script to insert price list header and price break line.

• QPBLKEX4.sql: Script to update price list header with qualifiers.

• QPBLKEX5.sql: Script to attach secondary price lists to the primary price list.

QP: Bulk Import of Price List Program

The Pricing Data Bulk Loader API is implemented as the concurrent program QP: Bulk Import of Price List. Using the concurrent program, you define the pricing data to be imported (bulk loaded), then run the concurrent program to import the price list data from interface tables into the Oracle Advanced Pricing tables. To improve processing efficiency, you can schedule the concurrent program to run at optimal times, such as when no active users are on the system.

The QP: Bulk Import of Price List program enables you to complete the following tasks:

• Do bulk validations and bulk loading.

• Provide all validation error messages in the same run for records that were not uploaded rather than returning errors one after another.

• Provide the ability to multi-thread the interface loader similar to Order Import with the ability to multi-thread within the same price list.

You can set the profile option QP: Batch size for Bulk Upload (QP_BATCH_SIZE_FOR_BULK_UPLOAD) to restrict the number of records that are read to the PL/SQL tables. This helps to improve the bulk import performance. The default value for the profile is 1,000, but you can change this value to suit your hardware configuration. For more information, see the Advanced Pricing Implementation Guide, Profile Options.

Scenarios for Using the QP: Bulk Import of Price List Program

Your business practices may require that you upload price list data on a regular basis, such as in the following scenarios:

• When a new price list must be created by uploading a price change file.

• When new products lines are created, new offerings to new channels, acquisitions of product lines or companies, new product rollout, and when new price lists are created.

• When you are implementing a new sales territory, to bulk load existing price lists or to upload pricing from regions that may have their pricing on spreadsheets.

Processes performed by the QP: Bulk Import of Price List Program

The QP: Bulk Import of Price List program completes the following processes:

• Creates and updates price list headers, lines, pricing attributes, qualifiers, and breaks using the interface tables.

• Deletes price list lines, pricing attributes, qualifiers, and breaks using the interface tables.

• (Optionally) Uses the Process Parent parameter in the concurrent program to load list lines even if the pricing attribute validation fails. If the Option is NO, and a pricing attribute fails, both parent and child lines are not uploaded.

• Stores the interface program ID in the main pricing tables so that you know which batch ID updated the records.

  This program updates only the IDs that are derived from Value To ID conversions back to the interface tables. If a record generates errors, the record remains in the interface tables where you can view the IDs.

• Marks a record as being processed so that multiple processes do not pick up the same record.

• Automatically deletes the successfully uploaded rows from the interface tables.

• Processes a specific set rather than the entire eligible data.

Note: For more information on populating the interface tables for bulk loader, see Oracle Advanced Pricing Implementation Guide.

Considerations for Running the QP: Bulk Import of Price List Program

Review the following considerations before running the QP: Bulk Import of Price List program:

• Source System/Pricing Transaction Entity (PTE): The concurrent program retrieves records only when the source system code and the PTE code match with the same profile values or null.

• Price lists that are attached to the following entities are not retrieved for bulk loader processing:

  • Sales Agreements (list source code of BSO)

  • Service Contracts (list source code of OKS)

  • Pricing Agreements (list type code of AGR)

• Importance of Log and Output file: The request output contains the following information:

  • Number of records processed

  • Number of successful records

  • Error messages (if applicable)

  The concurrent request log file provides the processing details. The log file also lists debug messages if Debug On parameter was set to Yes. If you enter an entity name that is not unique for the eligible records, none are selected for processing.

• Child lines, parents, and grandparent records must be populated to insert, update, and delete records: To insert, update, or delete records, ensure that the corresponding parent and grandparent records (if applicable) are also in the interface table so they are eligible for retrieval by the concurrent program.

• Errors before resubmit: For records with errors in the interface tables, set the request_id and process status flag columns to NULL so that these records are selected in the next run. When the Entity Name is specified as a Report parameter, you do not need to reset these values.

• Data for error messages in a QP interface table: The table QP_INTERFACE_ERRORS is populated with the processing error messages that are then written to the output file of the concurrent request. Occasionally, you must purge this table of this data.

Report Submission

In the Submit Request window, Name field, select QP: Bulk Import of Price List.

Report Parameters

QP: Bulk Import of Price List Parameters

• Entity: Indicates which pricing entity is to be processed. The default value is PRL (price list).

• Entity Name: This is an optional parameter. Enter the name of the price list to be processed. If this field is left blank, the system processes all entities of the selected entity type that are available for processing in the interface tables.

  If you enter a price list name, that price list is considered for import even if it failed in a previous import and regardless of the other flags (for example, even if PROCESS_ STATUS_FLAG not equal to P, or PROCESS_FLAG not equal to Y or REQUEST_ ID not equal to NULL in the QP_INTERFACE_LIST_HEADER record).

• Process ID: This value corresponds to the process_id in the interface tables. The Process ID groups data in the interface table. You can select criteria to specify which records to process.

  If this field is left blank, all available records for processing are considered subject to the restrictions entered in the Entity Name field.

• Process Type: This field corresponds to the process type that is listed in the interface tables such as XML. If specified, the bulk loader processes only the records with PROCESS_TYPE column matching the value entered here.

• Process Parent: Select Yes (the default) or No to determine if the loader should process a price list line when validation for a child pricing attribute fails. Valid values are Yes (the default) and No.

• No of Threads: Enter a number that indicates the total number of child processes or threads to achieve multithreading for list lines and their child records. The default value is 1.

  Multithreading occurs when the child concurrent processes are initiated after a user submits the Process Parent request. The parent process first processes the list header records and then the qualifiers records before spawning child processes. The child processes upload price list lines and attributes.

  Note: Multithreading can impact the length of processing time that is required; therefore, you should not exceed the number of Central Processing Units (CPUs) that are available when running the program. Ideally, you should schedule this process for a slow processing period or when few users are on the system.

• Turn Debug On: Select Yes or No (default):

  • Select No to improve loading performance; however error messages are not sent to the Debug log.

  • Select Yes to have all error messages sent to the Debug Log.

    The Debug On parameter controls only the log files. However, error messages, number of records processed, and other related information is written to the output file, regardless of the value set for Debug On.

• Enable Duplicate Line Check: The bulk loader can upload thousands of lines into the pricing (QP) lines table. If Enable Duplicate Line is set to Yes, then the QP: Bulk Import of Price List checks for duplicates lines and displays an error message if duplicate lines are found. If it is set to No, then the duplicate check is not done. The default value is Yes.