EAR 9.2: AR Update Abends At Step AR_PGG_SERV.VAT_DST1.INTERIM2 With Unique Constrain Violation On Record PS_PG_PENDST_TAO4 For VAT Item With Hundreds Of Distribution Lines

(Doc ID 2246944.1)

Last updated on MARCH 22, 2017

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

ISSUE:

AR Update Application Engine Program (AR_UPDATE) abends with a Unique Constrain Violation Error Message at Step AR_PGG_SERV.VAT_DST1.INTERIM2 on delivered Record PS_PG_PENDST_TAO4. This is the case when VAT functionality has been enabled into the affected Business Unit where the involved Item belongs to, as well as the requirement to have a large number of Accounting Distribution Lines on that single Item, (should be a minimum of 300 rows, though could be as high as 1200).

Note that previous AE Program/Section/Step AR_PGG_SERV.ARLNPREP.AR_DST already performs an SQL Insert into Table PS_PG_PENDST_TAO4. In this Record, there is Field DST_SEQ_NUM, which will contain the Distribution Line Sequence Number, in the above scenario, would contain a value of up to 1200 for instance, if so many rows exist.

Later on, the offending AE Program/Section/Step AR_PGG_SERV.VAT_DST1.INTERIM2 will perform a second SQL Insert into Table PS_PG_PENDST_TAO4, this time though with partial data coming from Temporary Record PS_PG_VATSEQ_TAO4. This Record is being populated by AE Program/Section/Step AR_PGG_SERV.VATDPREP.LOAD, which has the below codeline:

     %InsertSelect(DISTINCT, PG_VATSEQ_TAO, PG_PENVAT_TAO V, DST_SEQ_NUM = 300)
     FROM %Table(PG_PENVAT_TAO) V
     WHERE V.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

Note how in the above codeline, the Record Field PS_PG_VATSEQ_TAO4.DST_SEQ_NUM is getting a hardcoded value of 300, and this row will have the VAT Payment information. When this row, with a DST_SEQ_NUM of 300 is tried to be inserted by AE Program/Section/Step AR_PGG_SERV.VAT_DST1.INTERIM2 into main PS_PG_PENDST_TAO4 Record, the system detects that there is already one row with DST_SEQ_NUM of 300, due to the unusual high volume of Distribution Accounting Lines, and as such, it abends with a unique constrain violation Error Message.

REPLICATION STEPS:

     1.- Create a Recurring Invoice in Billing module, so that each month it generates a new transaction in a VAT enabled Business Unit with 1200+ rows for Distribution Lines
     2.- Finalize, and interface this Invoice from Billing into the Accounts Receivable module
     3.- Launch AR Update to post the new Pending Item Group into the proper Customer's Account
     4.- Confirm that Record PS_ITEM_DST contains the data of this one Invoice with over 1200+ Distribution Accounting Lines after Group By the below criteria:
BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, ACCOUNT, ALTACCT, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, DEPTID, PROJECT_ID, BUSINESS_UNIT_PC, ACTIVITY_ID, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, ANALYSIS_TYPE, STATISTICS_CODE, I.BAL_AMT HAVING SUM(FOREIGN_AMOUNT) <> 0
     5.- Proceed by creating a new Regular Deposit
     6.- Create a new Payment Worksheet for the Deposit just created, and include the Invoice interfaced from Billing
     7.- Balance the Payment Worksheet, and place it into Post Action of Batch Standard
     8.- Run AR Update once again, to post this transaction
     9.- The AR_UPDATE AE Program abends with a Unique Constrain Violation on Record PS_PG_PENDST_TAO4

ERROR MESSAGE:

     " -- 17:07:50.489 .........(AR_PGG_SERV.VAT_DST1.INTERIM2) (SQL)
       INSERT INTO PS_PG_PENDST_TAO4 ( PROCESS_INSTANCE, GROUP_BU, GROUP_ID, BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, GROUP_SEQ_NUM, DST_SEQ_NUM, LEDGER_GROUP, LEDGER, PRIMARY_LEDGER, BUSINESS_UNIT_GL, BUSINESS_UNIT_TO, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, CHARTFIELD2, CHARTFIELD1, CHARTFIELD3, PROJECT_ID, PRODUCT, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA2, AFFILIATE_INTRA1, CLASS_FLD, FUND_CODE, PROGRAM_CODE, BUSINESS_UNIT_PC, ACTIVITY_ID, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, ANALYSIS_TYPE, BUDGET_DT, STATISTICS_CODE, MONETARY_AMOUNT, STATISTIC_AMOUNT, CF_EDIT_STATUS, JRNL_LN_REF, LINE_DESCR, SYSTEM_DEFINED, ACCOUNTING_DT, FOREIGN_CURRENCY, FOREIGN_AMOUNT, RT_TYPE, RATE_MULT, RATE_DIV, CURRENCY_CD, GL_DISTRIB_STATUS, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, DOC_SEQ_STATUS, MOVEMENT_FLAG, USER1, USER2, USER3, USER4, USER5, DST_GL, ALLOW_DST, VAT_APPLICABILITY, VAT_TXN_TYPE_CD, TAX_CD_VAT, VAT_DST_ACCT_TYPE, ENTRY_EVENT, IU_ANCHOR_FLG, IU_SYS_TRAN_CD, IU_TRAN_CD, OPEN_ITEM_KEY, SEQ_NUM4 ) SELECT DISTINCT R.PROCESS_INSTANCE , I.GROUP_BU , I.GROUP_ID , I.BUSINESS_UNIT , I.CUST_ID , I.ITEM , I.ITEM_LINE , I.GROUP_SEQ_NUM , S.DST_SEQ_NUM , ' ' , ' ' , 'Y' , A.BUSINESS_UNIT_GL , ' ' , D.ACCOUNT , D.ALTACCT , L.DEPTID, L.OPERATING_UNIT, L.CHARTFIELD2, L.CHARTFIELD1, L.CHARTFIELD3, L.PROJECT_ID, L.PRODUCT, L.BUDGET_REF, D.AFFILIATE, D.AFFILIATE_INTRA2, D.AFFILIATE_INTRA1, D.CLASS_FLD, D.FUND_CODE, D.PROGRAM_CODE , L.BUSINESS_UNIT_PC, L.ACTIVITY_ID, L.RESOURCE_TYPE, L.RESOURCE_CATEGORY, L.RESOURCE_SUB_CAT, L.ANALYSIS_TYPE , I.ACCOUNTING_DT , ' ' , V.SUM_VAT_AMT_I , 0 , 'N' , ' ' , ' ' , 'W' , I.ACCOUNTING_DT , I.ENTRY_CURRENCY , V.SUM_VAT_AMT_I , I.RT_TYPE , I.RATE_MULT , I.RATE_DIV , I.CURRENCY_CD , 'N' , I.DOC_TYPE , I.DOC_SEQ_NBR , I.DOC_SEQ_DATE , I.DOC_SEQ_STATUS , 'N' , ' ' , ' ' , ' ' , ' ' , ' ' , A.DST_GL , R.ALLOW_DST , V.VAT_APPLICABILITY , V.VAT_TXN_TYPE_CD , V.TAX_CD_VAT , D.VAT_DST_ACCT_TYPE , I.ENTRY_EVENT , 'N' , 'ARIT' , 'GENERAL' , ' ' , 0 FROM PS_PG_PENVAT_TAO4 V , PS_VAT_ACCT_TMP24 D , PS_PG_VATSEQ_TAO4 S , PS_PG_AUTENT_TAO4 A , PS_PG_RQST_TAO4 R , PS_PG_PNDTMP_TAO4 I , PS_PG_DST_AR_TAO4 L , PS_BUS_UNIT_TBL_GL B WHERE A.PROCESS_INSTANCE = 7677405 AND A.PROCESS_INSTANCE = S.PROCESS_INSTANCE AND A.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND A.PROCESS_INSTANCE = V.PROCESS_INSTANCE AND A.PROCESS_INSTANCE = R.PROCESS_INSTANCE AND A.PROCESS_INSTANCE = D.PROCESS_INSTANCE AND A.PROCESS_INSTANCE = L.PROCESS_INSTANCE AND R.PGG_GROUP_TYPE NOT IN ('B', 'D') AND I.VAT_DCLRTN_POINT = 'P' AND 'X' = 'X' AND 'X' = 'X' AND R.GROUP_BU = I.GROUP_BU AND R.GROUP_ID = I.GROUP_ID AND V.GROUP_BU = I.GROUP_BU AND V.GROUP_ID = I.GROUP_ID AND V.BUSINESS_UNIT = I.BUSINESS_UNIT AND V.CUST_ID = I.CUST_ID AND V.ITEM = I.ITEM AND V.ITEM_LINE = I.ITEM_LINE AND V.GROUP_SEQ_NUM = I.GROUP_SEQ_NUM AND V.VAT_AMT_I <> 0 AND S.VAT_APPLICABILITY = V.VAT_APPLICABILITY AND S.VAT_TXN_TYPE_CD = V.VAT_TXN_TYPE_CD AND S.TAX_CD_VAT = V.TAX_CD_VAT AND S.VAT_DST_ACCT_TYPE = V.VAT_DST_ACCT_TYPE AND D.TAX_CD = V.TAX_CD_VAT AND D.VAT_TXN_TYPE_CD = V.VAT_TXN_TYPE_CD AND D.VAT_DST_ACCT_TYPE = 'VOI' AND I.VAT_ADVPAY_FLG <> 'Y' AND A.BUSINESS_UNIT_GL = D.BUSINESS_UNIT_GL AND A.BUSINESS_UNIT = I.BUSINESS_UNIT AND A.ACCOUNTING_DT = I.ACCOUNTING_DT AND A.ENTRY_USE_ID = I.ENTRY_USE_ID AND I.ENTRY_USE_ID NOT IN ('WS-02', 'WS-03') AND I.VAT_ADVPAY_FLG <> 'Y' AND A.BUSINESS_UNIT_GL = B.BUSINESS_UNIT AND L.GROUP_BU = I.GROUP_BU AND L.GROUP_ID = I.GROUP_ID AND L.BUSINESS_UNIT = I.BUSINESS_UNIT AND L.CUST_ID = I.CUST_ID AND L.ITEM = I.ITEM AND L.ITEM_LINE = I.ITEM_LINE AND L.GROUP_SEQ_NUM = I.GROUP_SEQ_NUM AND V.VAT_SEQ_NUM = ( SELECT MIN(V2.VAT_SEQ_NUM) FROM PS_PG_PENVAT_TAO4 V2 WHERE V2.PROCESS_INSTANCE = V.PROCESS_INSTANCE AND V2.GROUP_BU = V. GROUP_BU AND V2.GROUP_ID = V. GROUP_ID AND V2.BUSINESS_UNIT = V. BUSINESS_UNIT AND V2.CUST_ID = V.CUST_ID AND V2.ITEM = V.ITEM AND V2.ITEM_LINE = V.ITEM_LINE AND V2.GROUP_SEQ_NUM = V.GROUP_SEQ_NUM AND V2.VAT_APPLICABILITY = V.VAT_APPLICABILITY AND V2.VAT_TXN_TYPE_CD = V.VAT_TXN_TYPE_CD AND V2.TAX_CD_VAT = V.TAX_CD_VAT AND V2.VAT_DST_ACCT_TYPE = V.VAT_DST_ACCT_TYPE) AND L.DST_SEQ_NUM = ( SELECT MIN(L2.DST_SEQ_NUM) FROM PS_PG_DST_AR_TAO4 L2 WHERE L.PROCESS_INSTANCE = L2.PROCESS_INSTANCE AND L.GROUP_BU = L2.GROUP_BU AND L.GROUP_ID = L2.GROUP_ID AND L.BUSINESS_UNIT = L2.BUSINESS_UNIT AND L.CUST_ID = L2.CUST_ID AND L.ITEM = L2.ITEM AND L.ITEM_LINE = L2.ITEM_LINE AND L.GROUP_SEQ_NUM = L2.GROUP_SEQ_NUM)
       /
       -- Row(s) affected: 0
       -- 17:07:50.634 Process 7677405 ABENDED at Step AR_PGG_SERV.VAT_DST1.INTERIM2 (Action SQL) -- RC = 805
       ROLLBACK
       /
       -- 17:07:50.655 SQL Error: ORA-00001: unique constraint (SYSADM.PS_PG_PENDST_TAO4) violated "

ACTUAL RESULT:

The AR Update is abending, and prevents Payment Worksheet transaction from being posted.

EXPECTED BEHAVIOR:

The hardcoded value of 300 for Field DST_SEQ_NUM on AE Program/Section/Step AR_PGG_SERV.VATDPREP.LOAD should be modified to accommodate the possible scenario where an Item could have more than 300 Distribution Accounting Lines.

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