EAR 9.2: Automatic Maintenance Abending At Step AR_AUTOMNT.WKSHEETS.PEND_ITM (SQL) RC = 805 (108,524) (Doc ID 2276049.1)

Last updated on JUNE 12, 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:

Running the Automatic Maintenance AE Program (AR_AUTOMNT) with 2 Debit Items, with outstanding balances of positive, and negative amounts (due to prior underpayment, and overpayment transaction), it abends at Step AR_AUTOMNT.WKSHEETS.PEND_ITM with a unique constrain violation at Table PS_PENDING_ITEM. This happens even though the Tolerances defined for Write-Off are being met for the positive amount debit item, and its expectation is to be written-off.

REPLICATION STEPS:

     1.- Create a Pending Group with 2 Debit Items for 1000 USD each.
     2.- Run AR Update to post both Items into the Customer's Account.
     3.- Create a new Deposit Group, with two Payments: One for 980 USD, and another one for 1020 USD.
     4.- Generate a Payment Worksheet for the first Payment, and match the 980 USD to one of the Debit Items of 1000 USD, leaving a remanent of 20 USD outstanding. Place the Payment Worksheet into Post Action Batch Standard.
     5.- Generate a second Payment Worksheet for the other Payment, and match the 1020 USD to remaining Debit Item of 1000 USD. Being an overpayment, there will be a negative remanent of -20 USD outstanding. Place the Payment Worksheet into Post Action Batch Standard.
     6.- Run AR Update to post both Payment Worksheets
     7.- Now the two Debit Items have Item Balances of 20 USD, and -20 USD respectively.
     8.- Run Automatic Maintenance, which should write off the debit amount of 20 USD, because it is under the defined Tolerances of 50 USD, but not the credit item.
     9.- AR Automatic Maintenance AE Program (AR_AUTOMNT) abends in No Success 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:

     " -- 15:32:26.983 ..(AR_AUTOMNT.WKSHEETS.PEND_GRP) (Log Message)
       -- 15:32:26.984 ..(AR_AUTOMNT.WKSHEETS.PEND_ITM) (SQL)
INSERT INTO PS_PENDING_ITEM (GROUP_BU, GROUP_ID, BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, GROUP_SEQ_NUM, ENTRY_TYPE, ENTRY_REASON, ENTRY_AMT, ENTRY_EVENT, ACCOUNTING_DT, ASOF_DT, POST_DT, DUE_DT, CR_ANALYST, SALES_PERSON, COLLECTOR, DISPUTE_STATUS, DISPUTE_DT, DISPUTE_AMOUNT, PO_REF, PO_LINE, DOCUMENT, DOCUMENT_LINE, POSTED_FLAG, PYMNT_TERMS_CD, DISC_AMT, DISC_AMT1, DISC_DT, DISC_DT1, DISC_DAYS, DUE_DAYS, ALLOW_DISC, DST_ID_AR, DUN_DT, ST_DT, FC_DT, FC_AMT, OC_ADMIN_DT, OC_ADMIN_AMT, OC_PNLTY_DT, OC_PNLTY_AMT, OVERDUE_CHG_LINE, AR_ERROR_CD, ENTRY_USE_ID, DST_BAL_ITEM, COLLECTION_STATUS, COLLECTION_DT, BILL_OF_LADING, SUBCUST_QUAL1, SUBCUST_QUAL2, ENTRY_CURRENCY, RT_TYPE, RATE_MULT, RATE_DIV, PAYMENT_AMT, PAYMENT_CURRENCY, ENTRY_AMT_BASE, CURRENCY_CD, REAL_GAIN_LOSS, DST_ID_REAL, ITEM_ACCTG_DT, VAT_ENTITY, VAT_CALC_GROSS_NET, VAT_EXCPTN_TYPE, VAT_EXCPTN_CERTIF, VAT_DCLRTN_POINT, VAT_RECALC_FLG, VAT_BASIS_AMT, VAT_BASIS_AMT_BASE, VAT_AMT, VAT_AMT_BASE, COUNTRY_VAT_BILLFR, COUNTRY_VAT_BILLTO, COUNTRY_SHIP_TO, COUNTRY_SHIP_FROM, VAT_DISTRIB_FLG, VAT_RGSTRN_BUYER, VAT_DST_ACCT_TYPE, VAT_ROUND_RULE, ORDER_NO, CONTRACT_NUM, BUSINESS_UNIT_BI, BUSINESS_UNIT_OM, ADDRESS_SEQ_NUM, LETTER_CD, CONSOL_BUS_UNIT, CONSOL_INVOICE, PAYMENT_METHOD, DRAFT_BU, DRAFT_ID, DRAFT_APPROVAL, DRAFT_DOC, DRAFT_CURRENCY, DRAFT_FORMAT, DRAFT_AMT, BANK_SETID, BANK_CD, BANK_ACCT_KEY, DD_BU, DD_ID, DD_STATUS, DD_PROFILE_ID, DD_CURRENCY, DD_AMT, DD_AMT_BASE, DRAFT_TYPE, DRAFT_AMT_BASE, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, DOC_SEQ_STATUS, PROCESS_INSTANCE, USER_AMT1, USER_AMT2, USER_AMT3, USER_AMT4, USER_AMT5, USER_AMT6, USER_AMT7, USER_AMT8, USER_DT1, USER_DT2, USER_DT3, USER_DT4, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, DOUBTFUL, DRAFT_BUSN_EVENT, DRAFT_SUB_EVENT, SALES_PERSON2, REGION_CD, REVALUE_FLAG, PACKSLIP_NO, SBI_NUM, PC_DISTRIB_STATUS, VAT_ADVPAY_FLG, LC_ID, AR_SEQ_NUM, DRAFT_POST_ACTION, VAT_TRANS_AMT, RECEIVABLE_TYPE, ENTITY_CODE, CONTROL_AMT, CONTROL_AMT_BASE, ANCHOR_BU, ITM_PAY_AMT_BASE, PPRC_PROMO_CD, CLAIM_NO, CONTROL_CURRENCY, CONTROL_CURR_BASE, CONTROL_RATE_MULT, CONTROL_RATE_DIV, CONTROL_RT_TYPE, AR_SPECIALIST, BROKER_ID, CARRIER_ID, CLAIM_DT, CLASS_OF_TRADE, DEDUCTION_STATUS, DEDUCTION_DT, DIVISION, DT_INVOICED, INV_PROD_FAM_CD, INVOICE, INVOICE_BU, MAJOR_CLASS, MEMO_STATUS_CD, MERCH_TYPE, PROOF_OF_DELIVERY, SHIP_FROM_BU, SHIP_TO_ADDR_NUM, SHIP_TO_CUST_ID, SOLD_TO_ADDR_NUM, SOLD_TO_CUST_ID, SUB_GROUP_ID, VAT_TREATMENT_GRP, PHYSICAL_NATURE, COUNTRY_LOC_BUYER, STATE_LOC_BUYER, COUNTRY_LOC_SELLER, STATE_LOC_SELLER, VAT_SVC_SUPPLY_FLG, VAT_SERVICE_TYPE, COUNTRY_VAT_PERFRM, STATE_VAT_PERFRM, COUNTRY_VAT_SUPPLY, STATE_VAT_SUPPLY, STATE_SHIP_FROM, STATE_SHIP_TO, VAT_RPT_CNTRY_SRC, STATE_VAT_DEFAULT, DISC_TAKEN, GL_LVL, ITEM_LINE_ORIG, TOT_TAX_AMT, TOT_TAX_AMT_BSE, TOT_EXD_AMT, TOT_EXD_AMT_BSE, TOT_STX_AMT, TOT_STX_AMT_BSE, AG_REF_NBR, AR_IU_REQ, CLAIM_SETID, INVOICE_DT, BUSINESS_UNIT_CA, CONTRACT_LINE_NUM, SUBROG_CASE_NUM, SP_ID, REMIT_ADDR_SEQ_NUM, ENTERED_DTTM, OPRID, LAST_UPDATE_DTTM, OPRID_LAST_UPDT, AR_INBOUND_IPAC) SELECT G.GROUP_BU, G.GROUP_ID, I.BUSINESS_UNIT, I.CUST_ID, I.ITEM, I.ITEM_LINE, I.WS_SEQ, I.ENTRY_TYPE, I.ENTRY_REASON, I.ITEM_AMT, I.ENTRY_EVENT, I.ACCOUNTING_DT, I.ASOF_DT, G.POST_DT, I.DUE_DT, ' ', I.SALES_PERSON, ' ', I.DISPUTE_STATUS, NULL, 0, I.PO_REF, 0, I.DOCUMENT, I.DOCUMENT_LINE, 'N', I.PYMNT_TERMS_CD, 0, 0, I.DISC_DT, NULL, 0, 0, 'N', I.DST_ID_AR, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 'N', ' ', I.ENTRY_USE_ID, 'O', I.COLLECTION_STATUS, NULL, I.BILL_OF_LADING, I.SUBCUST_QUAL1, I.SUBCUST_QUAL2, I.BAL_CURRENCY, I.RT_TYPE, I.RATE_MULT_ORIG, I.RATE_DIV_ORIG, G.PAYMENT_AMT, G.PAYMENT_CURRENCY, I.ITEM_AMT_BASE, I.CURRENCY_CD, I.REAL_GAIN_LOSS, I.DST_ID_REAL, I.ITEM_ACCTG_DT, I.VAT_ENTITY, I.VAT_CALC_GROSS_NET, I.VAT_EXCPTN_TYPE, I.VAT_EXCPTN_CERTIF, I.VAT_DCLRTN_POINT, I.VAT_RECALC_FLG, 0, 0, 0, 0, I.COUNTRY_VAT_BILLFR, I.COUNTRY_VAT_BILLTO, I.COUNTRY_SHIP_TO, I.COUNTRY_SHIP_FROM, 'U', I.VAT_RGSTRN_BUYER, I.VAT_DST_ACCT_TYPE, I.VAT_ROUND_RULE, I.ORDER_NO, I.CONTRACT_NUM, ' ', ' ', 0, ' ', I.CONSOL_BUS_UNIT, I.CONSOL_INVOICE, I.PAYMENT_METHOD, I.DRAFT_BU, I.DRAFT_ID, I.DRAFT_APPROVAL, I.DRAFT_DOC, I.DRAFT_CURRENCY, I.DRAFT_FORMAT, 0, I.BANK_SETID, I.BANK_CD, I.BANK_ACCT_KEY, I.DD_BU, I.DD_ID, I.DD_STATUS, I.DD_PROFILE_ID, I.DD_CURRENCY, I.DD_AMT, I.DD_AMT_BASE, I.DRAFT_TYPE, I.DRAFT_AMT_BASE, C.DOC_TYPE, C.DOC_SEQ_NBR, C.DOC_SEQ_DATE, C.DOC_SEQ_STATUS, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', I.DOUBTFUL, I.DRAFT_BUSN_EVENT, I.DRAFT_SUB_EVENT, I.SALES_PERSON2, I.REGION_CD, I.REVALUE_FLAG, I.PACKSLIP_NO, I.SBI_NUM, 'N', I.VAT_ADVPAY_FLG, I.LC_ID, 0, ' ', 0, ' ', ' ', I.CONTROL_AMT, I.CONTROL_AMT_BASE, C.ANCHOR_BU, 0, I.PPRC_PROMO_CD, I.CLAIM_NO, I.CONTROL_CURRENCY, I.CONTROL_CURR_BASE, I.CONTROL_RATE_MULT, I.CONTROL_RATE_DIV, I.CONTROL_RT_TYPE, I.AR_SPECIALIST, I.BROKER_ID, I.CARRIER_ID, I.CLAIM_DT, I.CLASS_OF_TRADE, I.DEDUCTION_STATUS, I.DEDUCTION_DT, I.DIVISION, I.DT_INVOICED, I.INV_PROD_FAM_CD, I.INVOICE, I.INVOICE_BU, I.MAJOR_CLASS, I.MEMO_STATUS_CD, I.MERCH_TYPE, I.PROOF_OF_DELIVERY, I.SHIP_FROM_BU, I.SHIP_TO_ADDR_NUM, I.SHIP_TO_CUST_ID, I.SOLD_TO_ADDR_NUM, I.SOLD_TO_CUST_ID, I.SUB_GROUP_ID, I.VAT_TREATMENT_GRP, I.PHYSICAL_NATURE, I.COUNTRY_LOC_BUYER, I.STATE_LOC_BUYER, I.COUNTRY_LOC_SELLER, I.STATE_LOC_SELLER, I.VAT_SVC_SUPPLY_FLG, I.VAT_SERVICE_TYPE, I.COUNTRY_VAT_PERFRM, I.STATE_VAT_PERFRM, I.COUNTRY_VAT_SUPPLY, I.STATE_VAT_SUPPLY, I.STATE_SHIP_FROM, I.STATE_SHIP_TO, I.VAT_RPT_CNTRY_SRC, I.STATE_VAT_DEFAULT, 0, ' ', 0, 0, 0, 0, 0, 0, 0, I.AG_REF_NBR, ' ', ' ', I.INVOICE_DT, I.BUSINESS_UNIT_CA, I.CONTRACT_LINE_NUM, ' ', 0, 0, CAST(SYSTIMESTAMP AS TIMESTAMP), 'JREINKE', CAST(SYSTIMESTAMP AS TIMESTAMP), 'JREINKE', 'N' FROM PS_MT_WSCNTL_TAO4 C, PS_MT_WSITEM_TAO4 I, PS_GROUP_CONTROL G WHERE C.PROCESS_INSTANCE = 54733 AND
C.WORKSHEET_FLG <> 'Y' AND I.PROCESS_INSTANCE = C.PROCESS_INSTANCE AND I.WS_BU = C.WS_BU AND I.GROUP_ID_SEQ_NUM = C.GROUP_ID_SEQ_NUM AND G.GROUP_BU = C.WS_BU AND G.GROUP_ID = C.WS_ID
       /
       -- Row(s) affected: 0
       -- 15:32:27.071 Process 54733 ABENDED at Step AR_AUTOMNT.WKSHEETS.PEND_ITM (Action SQL) -- RC = 805
       ROLLBACK
       /
       -- 15:32:27.082 SQL Error: ORA-00001: unique constraint (SYSADM.PS_PENDING_ITEM) violated "

ACTUAL RESULT:

Automatic Maintenance AE Program (AR_AUTOMNT) abends in No Success, and as such, it is not completing the matching, and write-off tasks of the affected transactions. Furthermore, no Accounting Entries are being generated either.

EXPECTED BEHAVIOR:

No matter what type of transactions are being picked up by the Automatic Maintenance AE Program (AR_AUTOMNT), it should be able to handle them, match or write off the needed transactions, and generate their 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