My Oracle Support Banner

EAR 9.2: AR25001 SQR Program Abends With Unique Constrain On PS_AR_IDCUST_EC Due To Prior AR_PAYLOAD Run Not Purging Already Processed Data (Doc ID 2447450.1)

Last updated on JANUARY 02, 2021

Applies to:

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

Symptoms

ISSUE:

After applying the solution from <Bug 27735639> (P1 CORE: AR PAYLOAD ABENDED AT STEP AR_PAYLOAD.PRCS_DTL.UPD_CUST (SQL)), AR_PAYLOAD is no longer abending, however, it is not clearing all needed rows from Table PS_AR_IDCUST_EC as it should, and as such, the next time the AR25001 SQR Process is being run with a new Lockbox file, the program abends with a duplicate insert values in Record PS_AR_IDCUST_EC.

The Key Fields from Record PS_AR_IDCUST_EC are:

    - EIP_CTL_ID
    - LB_BANK_TRANS_ID
    - LOCKBOX_ID
    - LOCKBOX_BATCH_ID
    - PAYMENT_SEQ_NUM
    - ID_SEQ_NUM

Out of those, three could easily be having same values: LB_BANK_TRANS_ID identifies the Bank, LOCKBOX_ID contains the Lockbox file number from the Bank, and LOCKBOX_BATCH_ID identifies the Batch of the Lockbox. One single Batch could be split into different files by the Bank.

REPLICATION STEPS:

    1.- Upgrade the FSCM 9.2 Environment up to Image #28, and apply the solution from Defect # 27735639
    2.- Receive a Lockbox file containing Payment information from a Customer, detailing the amounts to be allocated on several specific Items
    3.- Launch the Retrieve Lockbox Files SQR Process (AR25001), to interface the Lockbox electronic payment file into the Deposit EC Staging tables
    4.- Run the Payment Interface AE Program (AR_PAYLOAD)
    5.- Confirm that the process completes to success
    6.- Check Table PS_AR_IDCUST_EC, and confirm that it is still storing rows for this Lockbox file
    7.- Receive a second Lockbox file from the Bank
    8.- Launch once again the Retrieve Lockbox Files SQR Process (AR25001) for this second file, to interface the Lockbox electronic payment file into the Deposit EC Staging tables
    9.- Notice that the AR25001 SQR Process has abended in 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:

    " batch amt from file: 000000000100000
      batch amt after edit: 1000.000000
      c:\bld\g\e920\ora\e920gx5u\sqr\ar25001.sqr:  - SQL Statement = AR25001.SQR,Insert-ID-Cust
      SQL Status =     1, SQL Error  = ORA-00001: unique constraint (EMDBO.PS_AR_IDCUST_EC) violated
      Error on line 71:
         (SQR 3301) Program stopped by user request.
      SQR for PeopleSoft: Program Aborting. "

ACTUAL RESULT:

The Retrieve Lockbox Files SQR Process (AR25001) abends, and as such, no new Lockbox files are able to be interfaced into the main EC tables to generate new Regular Deposits, and their Payment transactions. This delays the payment application against outstanding invoices.

EXPECTED BEHAVIOR:

The delivered Payment Interface AE Program (AR_PAYLOAD) should purge from all EC tables those transactions that have been successfully processed and that have properly generated their respective Regular Deposits, and related Payments. 

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
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.