OEXOEORD: QPXPRDPL: Cannot Add Item To Price List as it Errors With ORA-00001: unique constraint (QP.QP_LIST_LINES_U1) violated in Package QP_Price_List_line_Util Procedures Insert_Row (Doc ID 466320.1)

Last updated on OCTOBER 03, 2016

Applies to:

Oracle Advanced Pricing - Version 11.5.9 to 12.2.4 [Release 11.5 to 12.2]
Information in this document applies to any platform.
FORM:QPXPRDPL.FMB - Define Price Lists

Checked for relevance on 26-APR-2013


Symptoms

-- Problem Statement:

When attempting to add an item to a price list line on a price list
the following error occurs upon saving:

ERROR
ORA-00001: unique constraint (QP.QP_LIST_LINES_U1) violated in Package QP_Price_List_line_Util Procedure Insert_Row

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
1. Responsibility: Order Management Super User
2. Navigate to: Pricing>Price List Setup.
3. Query Price List
4. Insert new line, enter Item information, prices, and effective dates, press save.
Aforementioned error message appears

-- Business Impact:
The issue has the following business impact:
Due to this issue, users cannot add items to the price list.

Changes

Steps taken to analyze issue:

--Verify how index is setup using SQL >

select index_name, column_name,column_position
from all_ind_columns
where table_name = 'QP_LIST_LINES'
order by index_name,column_position ;

 

 

--Check the highest value stored in table QP_LIST_LINES for list_line_id;

select max(list_line_id) from qp_list_lines;



--Check the sequence used to populate the list_line_id to determine its next value to assign. Make sure the value is greater than the highest value stored
In table QP_LIST_LINES as returned from previous step.

select qp_list_lines_s.nextval from dual;



--This script will provide values populated for the various fields.
   One will be prompted to provide the list_header_id and list_line_id.

Select QUALIFICATION_IND
||','||ACCRUAL_QTY
||','||ACCRUAL_UOM_CODE
||','||ARITHMETIC_OPERATOR
||','||ATTRIBUTE1
||','||ATTRIBUTE10
||','||ATTRIBUTE11
||','||ATTRIBUTE12
||','||ATTRIBUTE13
||','||ATTRIBUTE14
||','||ATTRIBUTE15
||','||ATTRIBUTE2
||','||ATTRIBUTE3
||','||ATTRIBUTE4
||','||ATTRIBUTE5
||','||ATTRIBUTE6
||','||ATTRIBUTE7
||','||ATTRIBUTE8
||','||ATTRIBUTE9
||','||AUTOMATIC_FLAG
||','||BASE_QTY
||','||BASE_UOM_CODE
||','||COMMENTS
||','||CONTEXT
||','||CREATED_BY
||','||CREATION_DATE
||','||EFFECTIVE_PERIOD_UOM
||','||END_DATE_ACTIVE
||','||ESTIM_ACCRUAL_RATE
||','||GENERATE_USING_FORMULA_ID
||','||INVENTORY_ITEM_ID
||','||LAST_UPDATED_BY
||','||LAST_UPDATE_DATE
||','||LAST_UPDATE_LOGIN
||','||LIST_HEADER_ID
||','||LIST_LINE_ID
||','||LIST_LINE_NO
||','||LIST_LINE_TYPE_CODE
||','||LIST_PRICE
||','||PRODUCT_PRECEDENCE
||','||MODIFIER_LEVEL_CODE
||','||NUMBER_EFFECTIVE_PERIODS
||','||OPERAND
||','||ORGANIZATION_ID
||','||OVERRIDE_FLAG
||','||PERCENT_PRICE
||','||PRICE_BREAK_TYPE_CODE
||','||PRICE_BY_FORMULA_ID
||','||PRIMARY_UOM_FLAG
||','||PRINT_ON_INVOICE_FLAG
||','||PROGRAM_APPLICATION_ID
||','||PROGRAM_ID
||','||PROGRAM_UPDATE_DATE
||','||REBATE_TRANSACTION_TYPE_CODE
||','||RELATED_ITEM_ID
||','||RELATIONSHIP_TYPE_ID
||','||REPRICE_FLAG
||','||REQUEST_ID
||','||REVISION
||','||REVISION_DATE
||','||REVISION_REASON_CODE
||','||START_DATE_ACTIVE
||','||SUBSTITUTION_ATTRIBUTE
||','||SUBSTITUTION_CONTEXT
||','||SUBSTITUTION_VALUE
||','||PRICING_GROUP_SEQUENCE
||','||PRICING_PHASE_ID
||','||INCOMPATIBILITY_GRP_CODE
||','||RECURRING_VALUE
From QP_LIST_LINES
Where list_header_id = &list_header_id
And list_line_id = &list_line_id;

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