EAR 9.2: Payment Loader Abends At Step AR_PAYLOAD.ECREMIT.LOADCU11 Due To Unique Constrain At Table PS_PD_IDCUST_TAO4 When Processing Multiple Excel Deposits (Doc ID 2507349.1)

Last updated on MARCH 31, 2021

Applies to:

Symptoms

ISSUE:

It has been detected that the Payment Loader (AR_PAYLOAD Application Engine Program) abends in an Error Message due to duplicate constrain violation at Table PS_PD_IDCUST_TAO4 when processing multiple Excel Deposit/Payment transactions after PeopleSoft Enterprise FSCM 9.2 Image #28 has been applied, as well as the two additional Bug solutions:

     - <Bug 28187949> - Customer ID & BU not populated with AR Excel Upload     (Fix is delivered within FSCM 9.2 Image #29)

     - <Bug 27735639> - P1 Core: AR Payload Abended at step AR_PAYLOAD.PRCS_DTL.UPD_CUST     (Fix is delivered within FSCM 9.2 Image #29)

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 5 different Regular Deposits, with 1 Payment transaction each
    3.- Launch the interface from the ExcelUploadforDeposits.xlsm spreadsheet into the _EXCEL tables in the application
    4.- Launch the Excel Edit Request AE Program (AR_EXCL_EDIT), and ensure it is Successful
    5.- Confirm that all the transaction data is stored in the _EC Tables
    6.- Run the  Payment Loader (AR_PAYLOAD Application Engine Program) with the option of Excel flagged
    7.- 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 Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

    " -- 18:14:28.763 ...(AR_PAYLOAD.ECREMIT.LOADCU11) (SQL)
      INSERT INTO PS_PD_IDCUST_TAO4 (PROCESS_INSTANCE, LB_BANK_TRANS_ID, LOCKBOX_ID, LOCKBOX_BATCH_ID, PAYMENT_SEQ_NUM, EIP_CTL_ID, ID_SEQ_NUM, BNK_ID_NBR, BANK_ACCOUNT_NUM, RECON_CYCLE_NBR, RECORD_SEQ_NUMBER, DEPOSIT_BU, DEPOSIT_ID, BUSINESS_UNIT, CUST_ID, NAME1, CITY, POSTAL, REMIT_FROM_SETID, REMIT_FROM_CUST_ID, CORPORATE_SETID, CORPORATE_CUST_ID, MICR_ID, DB_NUMBER, LOCKBOX_RECGRP_NUM, DFI_ID_QUAL, DFI_ID_NUM, EDI_TRACE_NBR, DATA_SOURCE, MATCH, PAYMENT_AMT, ACCOUNTING_DT, PAYMENT_ID, MICR_SETID, DUPLICATE_CNT, AR_REMIT_STATUS, NEW_SEQ_NUM, MATCH_FLG) SELECT DISTINCT CUST.PROCESS_INSTANCE , CUST.LB_BANK_TRANS_ID ,' ' , CUST.LOCKBOX_BATCH_ID , PAY.PAYMENT_SEQ_NUM , CUST.EIP_CTL_ID , CUST.ID_SEQ_NUM , CUST.BNK_ID_NBR , CUST.BANK_ACCOUNT_NUM , 0 , 0 , PAY.DEPOSIT_BU , PAY.DEPOSIT_ID , CUST.BUSINESS_UNIT , cust.cust_id , CUST.NAME1 , CUST.CITY , CUST.POSTAL , CUST.REMIT_FROM_SETID , CUST.REMIT_FROM_CUST_ID , CUST.CORPORATE_SETID , CUST.CORPORATE_CUST_ID , CUST.MICR_ID , CUST.DB_NUMBER , CUST.LOCKBOX_RECGRP_NUM , CUST.DFI_ID_QUAL , CUST.DFI_ID_NUM , CUST.EDI_TRACE_NBR , CUST.DATA_SOURCE , CUST.MATCH , PAY.PAYMENT_AMT , PAY.ACCOUNTING_DT , PAY.PAYMENT_ID , ' ' , 0 , AR_REMIT_STATUS , 0 , 'E' FROM PS_PD_PAYMNT_TAO4 PAY , PS_AR_IDCUST_EC CUST WHERE CUST.MATCH='Y' AND PAY.DATA_SOURCE = 'XEL' AND PAY.LB_BANK_TRANS_ID=CUST.LB_BANK_TRANS_ID AND PAY.LOCKBOX_BATCH_ID=CUST.LOCKBOX_BATCH_ID AND PAY.PAYMENT_SEQ_NUM=CUST.PAYMENT_SEQ_NUM AND PAY.PROCESS_INSTANCE =
CUST.PROCESS_INSTANCE AND PAY.PROCESS_INSTANCE = 174295
      /
      -- Row(s) affected: 0
      -- 18:14:28.826 Process 174295 ABENDED at Step AR_PAYLOAD.ECREMIT.LOADCU11 (Action SQL) -- RC = 805
      ROLLBACK
      /
      -- 18:14:28.838 SQL Error: ORA-00001: unique constraint (EMDBO.PS_PD_IDCUST_TAO4) violated "

ACTUAL RESULT:

The Regular Deposits, and its related Payment transactions, are not properly being generated in the system, and as such, outstanding Customer invoices cannot be marked as paid.

EXPECTED BEHAVIOR:

The delivered Payment Loader (AR_PAYLOAD Application Engine Program) should be able to successfully process all Regular Deposits and Payment transactions loaded into the EC Tables should they come from an ExcelUploadforDeposits.xlsm interface.

Changes

Cause

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