My Oracle Support Banner

EAR 9.2: Payment Interface Loader Process Abends at Step AR_PAYLOAD.IDITEM.IDITEMDP when Multiple Active Effective Date Rows Exist at Automatic Entry Type for System Function WS-01 (Doc ID 2759515.1)

Last updated on MARCH 10, 2021

Applies to:

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

Symptoms


The delivered Payment Interface process, (AR_PAYLOAD AE Program), is abending in No Success, with a duplicate insert violation constrain Error Message into Record PS_PAYMENT_ID_ITEM, at Step AR_PAYLOAD.IDITEM.IDITEMDP.

This has been identified if a Regular Deposit and Payment have been interfaced into PeopleSoft Staging Tables, and the Automatic Entry Type configuration for System Function WS-01 contains multiple Active Effective Dated rows.

REPLICATION STEPS:

    1.- Configure the ExcelUploadforDeposits.xlsm spreadsheet to properly interface with the FSCM 9.2 Application
    2.- Enter manually in the spreadsheet the needed transaction data to create one single Regular Deposit, with 1 Payment transaction linked to a Customer ID and Item ID
    3.- Launch the interface from the ExcelUploadforDeposits.xlsm spreadsheet into the EXCEL tables in the application (Records PS_DEPOSIT_EXCEL, PS_PAYMENT_EXCEL, PS_ID_CUST_EXCEL, and PS_ID_ITEM_EXCEL)
    4.- Ensure that multiple Active Effective Date rows exist in the configuration of the Automation Entry Type for System Function WS-01
    5.- Launch the Excel Edit Request AE Program (AR_EXCL_EDIT), and ensure it is Successful
    6.- Confirm that all the transaction data is stored in the EC Tables
    7.- Run the  Payment Loader (AR_PAYLOAD Application Engine Program) with the Data Source 'Upload from Excel' selected
    8.- The AR_PAYLOAD Application Engine Program abends in Error

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

ERROR MESSAGE:

    " -- 2020-11-19 16:43:22.856 ...(AR_PAYLOAD.IDITEM.IDITEMDP) (SQL)
      INSERT INTO PS_PAYMENT_ID_ITEM (DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, ID_SEQ_NUM, PO_REF, ITEM, ITEM_LINE, DOCUMENT, PAY_AMT, ITEM_AMT, ITEM_DT, BUSINESS_UNIT, CUST_ID, SUBCUST_QUAL1, SUBCUST_QUAL2, BAL_AMT, DISC_ACTION, DISC_STATUS, DISC_TAKEN, DISC_AVAIL, ADJUSTMENT_AMT, ADJUSTMENT_REASON, ENTRY_TYPE, ENTRY_REASON, ENTRY_USE_ID, DST_ID_AR, LOCKBOX_RECGRP_NUM, REF_QUALIFIER_CODE, USER_AMT1, USER_AMT2, REF_VALUE, REF_VALUE_TO, PAY_AMT_BASE, DISC_BASE, REAL_GAIN_LOSS, BAL_CURRENCY, CURRENCY_CD, DST_ID_REAL, RT_TYPE, RATE_MULT, RATE_DIV, RATE_MULT_NEW, RATE_DIV_NEW, DESCR, PROCESS_INSTANCE, WO_AMT, WO_ENTRY_TYPE, WO_ENTRY_REASON, WO_ENTRY_USE_ID, ITEM_STATUS, ENTRY_EVENT, SP_ID, AR_TRAN_TYPE, ITEM_MSG_CD) SELECT ITM.DEPOSIT_BU , ITM.DEPOSIT_ID , ITM.PAYMENT_SEQ_NUM , SEQ.NEW_SEQ_NUM , ' ' , ITM.ITEM , ITM.ITEM_LINE , ' ' , ITM.PAY_AMT , ITM.ITEM_AMT , ITM.ITEM_DT , ITM.BUSINESS_UNIT , ITM.CUST_ID , ' ' , ' ' , 0 , ITM.DISC_ACTION , 'N' , ITM.DISC_TAKEN , ITM.DISC_TAKEN , ITM.ADJUSTMENT_AMT , ITM.ADJUSTMENT_REASON , ENTRY.ENTRY_TYPE , ' ' , ITM.ENTRY_USE_ID , ' ' , ITM.LOCKBOX_RECGRP_NUM , ITM.REF_QUALIFIER_CODE , 0 , 0 , ITM.REF_VALUE , ' ' , 0 , 0 , 0 , PAY.PAYMENT_CURRENCY , PAY.PAYMENT_CURRENCY , ' ' , ' ' , 1 , 1 , 1 , 1 , ' ' , 0 , 0 , ' ' , ' ' , ' ' , ' ' , ITM.ENTRY_EVENT , 0 , ' ' , ' ' FROM PS_PD_ITMSEQ_TAO6 SEQ , PS_PD_IDITEM_TAO6 ITM , PS_AUTO_ENTRY_TBL ENTRY , PS_PD_PAYMNT_TAO6 PAY WHERE SEQ.PROCESS_INSTANCE = 123456 AND SEQ.PROCESS_INSTANCE = ITM.PROCESS_INSTANCE AND SEQ.LB_BANK_TRANS_ID = ITM.LB_BANK_TRANS_ID AND SEQ.LOCKBOX_ID = ITM.LOCKBOX_ID AND SEQ.LOCKBOX_BATCH_ID = ITM.LOCKBOX_BATCH_ID AND SEQ.PAYMENT_SEQ_NUM = ITM.PAYMENT_SEQ_NUM AND SEQ.EIP_CTL_ID = ITM.EIP_CTL_ID AND SEQ.ID_SEQ_NUM = ITM.ID_SEQ_NUM AND SEQ.BNK_ID_NBR = ITM.BNK_ID_NBR AND SEQ.BANK_ACCOUNT_NUM = ITM.BANK_ACCOUNT_NUM AND SEQ.RECON_CYCLE_NBR = ITM.RECON_CYCLE_NBR AND SEQ.RECORD_SEQ_NUMBER = ITM.RECORD_SEQ_NUMBER AND SEQ.DEPOSIT_BU = ITM.DEPOSIT_BU AND SEQ.DEPOSIT_ID = ITM.DEPOSIT_ID AND PAY.PROCESS_INSTANCE = ITM.PROCESS_INSTANCE AND PAY.DEPOSIT_BU = ITM.DEPOSIT_BU AND PAY.DEPOSIT_ID = ITM.DEPOSIT_ID AND PAY.PAYMENT_SEQ_NUM = ITM.PAYMENT_SEQ_NUM AND PAY.REF_LEVEL = 'D' AND ITM.DUPLICATE_CNT= 0 AND ITM.PROCESS_INSTANCE=123456 AND ITM.ADJUSTMENT_REASON = ' ' AND ITM.MATCH_FLG = 'E' AND ITM.ENTRY_USE_ID=ENTRY.ENTRY_USE_ID AND ENTRY.SETID=( SELECT DISTINCT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE=ITM.ENTRY_USE_BU AND RECNAME='AUTO_ENTRY_TBL')
      /
      -- Row(s) affected: 0
      -- 2020-11-19 16:43:22.935 Process 123456 ABENDED at Step AR_PAYLOAD.IDITEM.IDITEMDP (Action SQL) -- RC = 805
      ROLLBACK
      /
      -- 2020-11-19 16:43:22.985 SQL Error: ORA-00001: unique constraint (EMDBO.PS_PAYMENT_ID_ITEM) violated  "

The AR_PAYLOAD AE Program abends, and as such, all the Regular Deposit, Payment, Item, and Customer references information are not being updated into the main Receivables Records.

The delivered Payment Interface process (AR_PAYLOAD AE Program), should not abend in such manner, regardless of how many Active Effective Date rows exist for Automatic Entry Type configuration on System Function WS-01. The process should be able to only take the most effective dated row.

NOTE: In the images/screenshots/examples mentioned and/or the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance).  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.