pin_bill_accts / PCM_OP_BILL_MAKE_BILL Errors With Unique Constraint After Upgrade To 7.3

(Doc ID 550191.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 to 7.3.0.0.0 [Release 7.3.0]
Information in this document applies to any platform.
***Checked for relevance on 27-Dec-2011***


Symptoms

After a user upgraded to BRM 7.3 (upgrade path: 6.2_SP4 -> 7.0 -> 7.3), when running billing
(pin_bill_accts, or call PCM_OP_BILL_MAKE_BILL) on upgraded accounts, the following error is logged in dm_oracle.pinlog:

ORACLE error: do_proc_execute : PINStmtExecute: code 1, op 0
=ORA-00001: unique constraint (PIN2.I_ITEM_NO__ID) violated
ORA-06512: at "PIN2.BILLING_UPDATES", line 102.


The queries to the DB returns the following data:

SQL> select header_num , header_str , name from data_t where poid_type = '/data/sequence';

HEADER_NUM HEADER_STR NAME
---------- ------------------------------ ------------------------------
102358 :-:FI1 PIN_SEQUENCE_TYPE_PACKAGE_ID
59108 :,:T1 PIN_SEQUENCE_TYPE_TRANS_ID
906537 ::B PIN_SEQUENCE_TYPE_BILL_NO
643122 :-:P1 PIN_SEQUENCE_TYPE_PAYMENT
0 :-:D1 PIN_SEQUENCE_TYPE_DISPUTE
38195 :-:A1 PIN_SEQUENCE_TYPE_ADJUSTMENT
0 :-:S1 PIN_SEQUENCE_TYPE_SETTLEMENT
2864 :-:W1 PIN_SEQUENCE_TYPE_WRITEOFF
0 :-:C1 PIN_SEQUENCE_TYPE_ACCOUNT
12578 :-:R1 PIN_SEQUENCE_TYPE_REFUND
0 :-:FI1 PIN_SEQUENCE_TYPE_FIAS_ID
463292 :-:PTD1 PTD_SEQUENCE_ID
624120 :-:PTD2 PTD_SYNC_SEQUENCE_ID
10003 :-:PTD3 PTD_EQUIPMAINT_CONTRACT_ID
22690 :-:PTD2 PTD_ERATE_SEQUENCE_ID
589695 :-:PTD1 PTD_SEQUENCE_ID
25086 :-:PTD2 PTD_ERATE_SEQUENCE_ID
1387472 :-:PTD2 PTD_SYNC_SEQUENCE_ID
10003 :-:PTD3 PTD_EQUIPMAINT_CONTRACT_ID


However, no actual data has the violation (the queries below returns the data <= the HEADER_NUM in DATA_T).

SQL> select max((to_number(substr(item_no , instr(item_no , '-')+1 )))) from item_t where item_no like 'P1%';

MAX((TO_NUMBER(SUBSTR(ITEM_NO,INSTR(ITEM_NO,'-')+1))))
------------------------------------------------------
643122

SQL> select max((to_number(substr(item_no , instr(item_no , '-')+1 )))) from item_t where item_no like 'A1%';

MAX((TO_NUMBER(SUBSTR(ITEM_NO,INSTR(ITEM_NO,'-')+1))))
------------------------------------------------------
38195

SQL> select max((to_number(substr(item_no , instr(item_no , '-')+1 )))) from item_t where item_no like 'W1%';

MAX((TO_NUMBER(SUBSTR(ITEM_NO,INSTR(ITEM_NO,'-')+1))))
------------------------------------------------------
2864

SQL> select max((to_number(substr(item_no , instr(item_no , '-')+1 )))) from item_t where item_no like 'R1%';

MAX((TO_NUMBER(SUBSTR(ITEM_NO,INSTR(ITEM_NO,'-')+1))))
------------------------------------------------------
12578


SQL> select max((to_number(substr(item_no , instr(item_no , 'B')+1 , instr(item_no , ',') -
instr(item_no , 'B')-1)))) from item_t where item_no like 'B%'

MAX((TO_NUMBER(SUBSTR(ITEM_NO,INSTR(ITEM_NO,'B')+1,INSTR(ITEM_NO,',')-INSTR(ITEM_NO,'B')-1))))
----------------------------------------------------------------------------------------------
906537

What's the reason for the above "unique constraint (PIN2.I_ITEM_NO__ID) violated" error?

.

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