EAR 9.2: AR Update (AE Program AR_UPDATE) Abends At Step AR_POSTING.ED_IPAC.INS_DST With Unique Constrain On Record PS_RP_PNDDST_TAO4 (Doc ID 2287815.1)

Last updated on JULY 18, 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:

When attempting to run AR Update (AE Program AR_UPDATE) for a Pending Item Group containing multiple Items for Customers set up for IPAC, the AR_POST1 AE Program fails with a unique constrain violation Error Message when adding the transaction data into Table PS_RP_PNDDST_TAO.

A similar issue was already reported in FSCM 9.1 <Bug 14481866> (AR: Unique Constrain Error At AR_POSTING.ED_IPAC.INS_DST When Posting IPAC Invoice). This was fixed in FSCM 9.1 FIN Bundle #25, and addressed before FSCM 9.2 release date. However, in this new scenario, the difference is that the same Group to be posted has at least 2 Items for Customer A, and 2 Items for Customer B, both IPAC configured.

REPLICATION STEPS:

     1.- Setup for IPAC configuration for 2 Customers
     2.- Enter 2 Bills, one for each Customer ID, and containing 2 Lines each.
     3.- Place both Bills in Ready status
     4.- Launch the Finalization and Print process against the 2 transactions
     5.- Continue by running the Load AR program
     6.- Confirm that the process has reached Receivables, where a new External Pending Item Group is created, with a total of 4 Items (2 for each IPAC Customer)
     7.- Place the Group into a Post Action of Batch Standard
     8.- Launch AR Update to post Pending Item Groups
     9.- AR Update abends in Error Message

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

     " -- 20:35:57.282 .......(AR_POSTING.ED_IPAC.INS_DST) (SQL)
       INSERT INTO PS_RP_PNDDST_TAO4 (PROCESS_INSTANCE, GROUP_BU, GROUP_ID, BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, GROUP_SEQ_NUM, LEDGER_GROUP, LEDGER, DST_SEQ_NUM, ITEM_SEQ_NUM, PRIMARY_LEDGER, BUSINESS_UNIT_GL, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, BUSINESS_UNIT_PC, PROJECT_ID, 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, APPL_JRNL_ID, 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, VAT_APPLICABILITY, VAT_TXN_TYPE_CD, TAX_CD_VAT, VAT_DST_ACCT_TYPE, ENTRY_EVENT, ENTRY_TYPE, ENTRY_REASON, IU_ANCHOR_FLG, IU_SYS_TRAN_CD, IU_TRAN_CD, GL_ACCTG_DT, TRANSACTION_TYPE, ENTRY_USE_ID, EE_PROC_STATUS, OPEN_ITEM_KEY, FED_DISTRIB_STATUS) SELECT 121935, G.GROUP_BU, G.GROUP_ID, I.BUSINESS_UNIT, I.CUST_ID, I.ITEM, I.ITEM_LINE, I.GROUP_SEQ_NUM, D.LEDGER_GROUP, D.LEDGER, D.DST_SEQ_NUM, 0, D.PRIMARY_LEDGER, D.BUSINESS_UNIT_GL, D.ACCOUNT, D.ALTACCT, D.DEPTID, D.OPERATING_UNIT, D.PRODUCT, D.FUND_CODE, D.CLASS_FLD, D.PROGRAM_CODE, D.BUDGET_REF, D.AFFILIATE, D.AFFILIATE_INTRA1, D.AFFILIATE_INTRA2, D.CHARTFIELD1, D.CHARTFIELD2, D.CHARTFIELD3, D.BUSINESS_UNIT_PC, D.PROJECT_ID, D.ACTIVITY_ID, D.RESOURCE_TYPE, D.RESOURCE_CATEGORY, D.RESOURCE_SUB_CAT, D.ANALYSIS_TYPE, D.BUDGET_DT, D.STATISTICS_CODE, D.MONETARY_AMOUNT, D.STATISTIC_AMOUNT, D.CF_EDIT_STATUS, D.JRNL_LN_REF, D.LINE_DESCR, D.SYSTEM_DEFINED, ' ', I.ACCOUNTING_DT, D.FOREIGN_CURRENCY, D.FOREIGN_AMOUNT, I.RT_TYPE, I.RATE_MULT, I.RATE_DIV, I.CURRENCY_CD, D.GL_DISTRIB_STATUS, I.DOC_TYPE, I.DOC_SEQ_NBR, I.DOC_SEQ_DATE, I.DOC_SEQ_STATUS, D.MOVEMENT_FLAG, I.USER1, I.USER2, I.USER3, I.USER4, I.USER5, D.VAT_APPLICABILITY, D.VAT_TXN_TYPE_CD, D.TAX_CD_VAT, I.VAT_DST_ACCT_TYPE, I.ENTRY_EVENT, I.ENTRY_TYPE, I.ENTRY_REASON, D.IU_ANCHOR_FLG, D.IU_SYS_TRAN_CD, D.IU_TRAN_CD, NULL, ' ', I.ENTRY_USE_ID, ' ', D.OPEN_ITEM_KEY, 'N' FROM PS_RP_GROUP_TAO4 G, PS_PENDING_ITEM I , PS_PENDING_DST D WHERE G.PROCESS_INSTANCE=121935 AND G.GROUP_BU = 'US001' AND G.GROUP_ID = '445' AND G.GROUP_BU = I.GROUP_BU AND G.GROUP_ID = I.GROUP_ID AND I.GROUP_BU = D.GROUP_BU AND I.GROUP_ID = D.GROUP_ID AND I.BUSINESS_UNIT = D.BUSINESS_UNIT AND I.CUST_ID = D.CUST_ID AND I.ITEM = D.ITEM AND I.ITEM_LINE = D.ITEM_LINE
       /
       -- Row(s) affected: 0
       -- 20:35:57.436 Process 121935 ABENDED at Step AR_POSTING.ED_IPAC.INS_DST (Action SQL) -- RC = 805
       ROLLBACK
       /
       -- 20:35:57.464 SQL Error: ORA-00001: unique constraint (EMDBO.PS_RP_PNDDST_TAO4) violated "

ACTUAL RESULT:

The AR Update AE Program (AR_UPDATE) is abending, hence, the needed Accounting Entries for the posting of an External Pending Item Group are not being generated.

EXPECTED BEHAVIOR:

The AR Update process should be able to handle a single Group containing multiple Invoices for different IPAC enabled Customers, and generate the needed posting Accounting Entries.

 

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