EAR 9.2: Payment Interface Loader Process Abends at Step AR_PAYLOAD.ECREMIT.LOADCU11 when All Data Sources Selected but Only Upload from Excel Exist (Doc ID 2759192.1)

Last updated on JUNE 20, 2024

Applies to:

Symptoms

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 only Excel Deposit/Payment transactions exist, but the Run Control ID has all options of sources selected. This has been replicated even after the below Fixes have been implemented:

     - <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)
     - <Bug 29168833> - AR: PAYMENT LOADER ABENDS AT STEP AR_PAYLOAD.ECREMIT.LOADCU11 ON PD_IDCUST_TAO4     (Fix is delivered within FSCM 9.2 Image #31)

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.- Query the EC Tables, and make sure there are no transactions left for processing, (Records PS_AR_LOCKBOX_EC, PS_AR_DEPOSIT_EC, PS_AR_PAYMENT_EC, PS_AR_IDCUST_EC, PS_AR_IDITEM_EC should be completely empty)
    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 all Data Sources selected (Lockbox, EDI820, Cash Drawer Receipts, EDI CREEXT, Bank Statement, Upload from Excel, Accounts Payable Payments)
    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-18 16:37:19.037 ...(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_ID=CUST.LOCKBOX_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 = 123456
      /
      -- Row(s) affected: 0
      -- 2020-11-18 16:37:19.159 Process 123456 ABENDED at Step AR_PAYLOAD.ECREMIT.LOADCU11 (Action SQL) -- RC = 805
      ROLLBACK
      /
      -- 2020-11-18 16:37:19.207 SQL Error: ORA-00001: unique constraint (SYSADM.PS_PD_IDCUST_TAO4) violated "

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

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 any Data Source interface without such an Abend.

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.