EAR 9.2: AR Update Abends At Step AR_PGG_SERV.CHK_RND.CHK_DST With A Unique Constrain 805 On Table PS_PG_PENDST_TAO4 After Performance Fix From Defect # 24311353 Is Applied (FSCM 9.2 Image #21) (Doc ID 2239659.1)

Last updated on MAY 23, 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:

The AR Update abends with an Error Message when the below criteria are being met under a same run:

     a.- The fix from <Bug 24311353>, (delivered within PeopleSoft Enterprise FSCM 9.2 Image #21), has been applied to the affected environment
     b.- At the Receivables Installation Options level, the 'No Mult Pending Item Selection' flag is un-selected
     c.- Multiple Maintenance Worksheets, referring to at least one same Item ID, are set in Batch Standard Post Action

REPLICATION STEPS:

     1.- At the Receivables Installation Options level, un-select the 'No Mult Pending Item Selection' flag
     2.- Create a new Debit Item on Business Unit US001, Customer ID 1001, and an Amount of 200 USD
     3.- Create two new Credit Items on Business Unit US001, Customer ID 1001, and Amounts of -100 USD in both cases
     4.- Run AR Update for Pending Items alone
     5.- Create a new Maintenance Worksheet, and balance the Debit Item against one of the Credit Items for 100 USD (This should create a second sequence for the Debit Item for the remaining 100 USD)
     6.- Set the new Maintenance Worksheet as Batch Standard Post Action
     7.- Create a second Maintenance Worksheet, and balance the remaining 100 USD of the Debit Item against other Credit Item
     8.- Set this second Maintenance Worksheet as Batch Standard Post Action as well
     9.- Run AR Update for Maintenance Worksheets alone
     10.- AR Update abends with an 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:

     " -- 18:45:44.311 ........(AR_PGG_SERV.CHK_RND.CHK_DST) (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, 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, 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 108113, 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, ' ', 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, 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, IU_ANCHOR_FLG, IU_SYS_TRAN_CD, IU_TRAN_CD, OPEN_ITEM_KEY, 0 FROM PS_PENDING_DST I WHERE I.GROUP_BU = 'US001' AND I.BUSINESS_UNIT = 'US001' AND I.GROUP_ID != '356' AND I.CUST_ID = '1001' AND I.ITEM = 'DEBIT0002' AND I.ITEM_LINE = 0 AND I.LEDGER = 'LOCAL' AND I.LEDGER_GROUP = 'RECORDING' AND
I.SYSTEM_DEFINED = 'A'
       /
       -- Row(s) affected: 0
       -- 18:45:44.416 Process 108113 ABENDED at Step AR_PGG_SERV.CHK_RND.CHK_DST (Action SQL) -- RC = 805
       ROLLBACK
       /
       -- 18:45:44.435 SQL Error: ORA-00001: unique constraint (EMDBO.PS_PG_PENDST_TAO4) violated "

ACTUAL RESULT:

AR Update abends in an Error Message, corrupts the involved transactions, and prevents Users from further processing the affected Maintenance Worksheets, and post their Accounting Entries.

EXPECTED BEHAVIOR:

The AR Update should be able to handle multiple Maintenance Worksheets listing the same Item ID, and properly post the needed 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