QPXVBLK Duplicate Line Error While Running QP: Bulk Import Of Price List Program With Pricing Attributes (Doc ID 467495.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Advanced Pricing - Version 11.5.10 to 11.5.10 [Release 11.5]
Information in this document applies to any platform.
EXECUTABLE:QPXVBLK - Pricelist Bulk Loader
Checked for relevance on 4-APR-2014


Symptoms

-- Problem Statement:
On 11.5.10.2 in Production:
When attempting to run QP: Bulk Insert of Price list
the following error occurs:

ERROR
715016/PRL/QP_INTERFACE_LIST_LINES/TOS CDN (APRIL 2004) TEST8/125886 One or more lines are duplicates of each other. Please change the effectivity dates or the product or the pricing attributes to make these lines unique.

715016/PRL/QP_INTERFACE_LIST_LINES/TOS CDN (APRIL 2004) TEST8/125894 One or more lines are duplicates of each other. Please change the effectivity dates or the product or the pricing attributes to make these lines unique.

The attributes created for each child are as following:

LINE ITEM Pricing Attribute Pricing Attribute Value
------- ------ ------------------ -----------------------
125886 PM PANEL PRICING_ATTRIBUTE1 36
125886 PM PANEL PRICING_ATTRIBUTE2 36

125894 PM PANEL PRICING_ATTRIBUTE1 51
125894 PM PANEL PRICING_ATTRIBUTE2 36

If one tries to load the lines in separate batches (inserting data for one line, running the Bulk loader then inserting data for the other line, running the Bulk loader ) both lines are created.

If one changes line: 125894 PRICING_ATTRIBUTE2 to have a value of 37 (for example) both lines are created.

It seems that the program sees the the value (36) for PRICING_ATTRIBUTE2 for both lines as a duplicate, even though they are from distinct lines.

OR
One could receive just the following errors:

ERROR
-----------------------
DUP_LINE_CHECK Unexpected Exception ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
Unexpected error


-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
1. Populate QP Interface tables with multiple pricing attribute per price list line.
2. Responsibility: Oracle Pricing Manager
3. Navigate to: Reports
Run QP: Bulk Insert of Price Lists

-- Business Impact:
The issue has the following business impact:
Due to this issue, users cannot import lines with different pricing attributes during one run of this concurrent process.

Changes

Scripts used to populate the QP Interface tables.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

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
(
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'PRL',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'NOV14 (TOS CDN APR 2004) Sample Bulk Loaded Price List',
'CAD',
'Y',
5,
sysdate,
NULL,
-2,
'US',
'US',
'INSERT',
'Y',
'P'
);

/* Insert Price list line 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
(
'NOV 14_LINE1',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'PLL',
sysdate,
NULL,
'UNIT_PRICE',
150,
'Y',
200,
'INSERT',
'Y',
'P'
);

/* Insert Price list line 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
(
'NOV 14_LINE2',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'PLL',
sysdate,
NULL,
'UNIT_PRICE',
310,
'Y',
230,
'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_VALUE,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG) VALUES
(
'NOV 14_LINE1_ATTR1',
'NOV 14_LINE1',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
‘55018’,
'Ea',
'INSERT',
'Y',
'P'
);

/* Insert Pricing 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,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM,
PRICING_ATTR_VALUE_TO,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'NOV 14_ LINE1_ATTR2',
'NOV 14_LINE1',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
'ks-fg',   --This is the actual item name
'PM',
'WIDTH',
'36',
'36',
'BETWEEN',
'Y',
'INSERT',
'Ea',
'P'
);

Insert into qp_interface_pricing_attribs ( orig_sys_pricing_attr_ref,
orig_sys_line_ref ,orig_sys_header_ref ,pricing_attribute_context,
PRICING_ATTR_CODE, PRICING_ATTR_VALUE_FROM , COMPARISON_OPERATOR_CODE,
process_flag ,interface_action_code,
process_id, product_attribute_context, product_attr_code
,product_attr_val_disp,product_uom_code, process_type, process_status_flag)

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,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM,
PRICING_ATTR_VALUE_TO,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'NOV 14_ LINE1_ATTR3',
'NOV 14_LINE1',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
'ks-fg', --This is the actual item name
'PM',
'HEIGHT',
'36',
'36',
'BETWEEN',
'Y',
'INSERT',
'Ea',
'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_VALUE,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG) VALUES
(
'NOV 14_LINE2_ATTR1',
'NOV 14_LINE2',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
‘55018’,
'Ea',
'INSERT',
'Y',
'P'
);

/* Insert Pricing 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,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM,
PRICING_ATTR_VALUE_TO,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'NOV 14_ LINE2_ATTR2',
'NOV 14_LINE2',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
'ks-fg', --This is the actual item name
'PM',
'WIDTH',
'51',
'51',
'BETWEEN',
'Y',
'INSERT',
'Ea',
'P'
);

Insert into qp_interface_pricing_attribs ( orig_sys_pricing_attr_ref,
orig_sys_line_ref ,orig_sys_header_ref ,pricing_attribute_context,
PRICING_ATTR_CODE, PRICING_ATTR_VALUE_FROM , COMPARISON_OPERATOR_CODE,
process_flag ,interface_action_code,
process_id, product_attribute_context, product_attr_code
,product_attr_val_disp,product_uom_code, process_type, process_status_flag)

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,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM,
PRICING_ATTR_VALUE_TO,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'NOV 14_ LINE2_ATTR3',
'NOV 14_LINE2',
‘TOS CDN (APRIL 2004) TEST NOV 14’,
'ITEM',
'INVENTORY_ITEM_ID',
'ks-fg', --This is the actual item name
actual item name
'PM',
'HEIGHT',
'36',
'36',
'BETWEEN',
'Y',
'INSERT',
'Ea',
'P'
);

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms