EAR 9.2: Create Direct Debits Process Abends at Step AR_DIRDEBIT.INS_DD.INS_CNTL With Unique Constrain Violation on PS_DD_CONTROL Due to Reusing DD_ID Value (Doc ID 2958993.1)

Last updated on JULY 18, 2023

Applies to:

Symptoms

The Create Direct Debits process (AR_DIRDEB AE Program) is abending at Step AR_DIRDEBIT.INS_DD.INS_CNTL with a duplicate constrain violation on Record PS_DD_CONTROL. The problem takes place when the Direct Debit ID value last generated by the system reaches or surpasses the 100 figure, as the codeline is looking at the maximum value of the Direct Debit ID field (DD_ID), with the formula of Max(DD_ID), which would then consider the value of 99 as a higher one, and would then make sure this is what gets updated as Last Number Issued at the Auto Numbering set up.

As such, the next time the process is run, and new Direct Debit ID values are appointed to the latest transactions, the system will generate the same Direct Debit ID 100 one again, causing a potential duplicate that cannot be handled.

REPLICATION STEPS:

    1.- Log into the FSCM Online Applications as a Power User or System Administrator
    2.- Define a Bank Account for Collection Method of Direct Debit (Navigation Path: Banking > Bank Accounts > External Accounts)
    3.- Set up a Direct Debit Profile ID with the format of 'One Item per Direct Debit', and pointing to the newly defined Bank Account (Navigation Path: Set Up Financials/Supply Chain > Product Related > Receivables > Payments > Direct Debit Profile)
    4.- Manually configure the MICR Information containing Bank Account details for a given Customer that will use Direct Debit (Navigation Path: Customers > Customer Information > MICR Information)
    5.- At the Customer level, enable Payment Method of Direct Debit, define the MICR information account created, and link it to the Direct Debit Profile ID set up (Navigation Path: Customers > Customer Information > General Information > Bill To Options tab)
    6.- Define the Direct Debit ID Auto Numbering with a Last Number Issued value of 98 (Navigation Path: Set Up Financials/Supply Chain > Common Definitions > Codes and Auto Numbering > Auto Numbering)
    7.- Log into the FSCM Online Applications as a Receivables User
    8.- Create a new Pending Item Group for the Customer ID at hand, with a couple of Items, and set the Post Action as Batch Standard (Navigation Path: Accounts Receivable > Pending Items > Online Items > Online Item Group Entry)
    9.- Launch the AR Update process with the option of Pending Items selected (Navigation Path: Accounts Receivable > Receivables Update > Request Receivables Update)
    10.- Run the Create Direct Debits process for the Business Unit at hand, and the Direct Debit Profile ID previously created (Navigation Path: Accounts Receivable > Direct Debits > Administer Direct Debits > Create Direct Debits)
    11.- Confirm that the process has generated one new Direct Debit Worksheet, with two new Direct Debit IDs 99, and 100 (Navigation Path: Accounts Receivable > Direct Debits > Administer Direct Debits > Update Direct Debits)
    12.- PROBLEM: Check the configuration of the Direct Debit ID Auto Numbering, and confirm that the Last Number Issued is 99, instead of the expected 100 (Navigation Path: Set Up Financials/Supply Chain > Common Definitions > Codes and Auto Numbering > Auto Numbering)
    13.- Create a second Pending Item Group for the Customer ID at hand, with a new couple of Items, and set the Post Action as Batch Standard (Navigation Path: Accounts Receivable > Pending Items > Online Items > Online Item Group Entry)
    14.- Launch the AR Update process with the option of Pending Items selected (Navigation Path: Accounts Receivable > Receivables Update > Request Receivables Update)
    15.- Run the Create Direct Debits process once again (Navigation Path: Accounts Receivable > Direct Debits > Administer Direct Debits > Create Direct Debits)
    16.- PROBLEM: The Create Direct Debits process (AR_DIRDEBIT AE Program) goes to No Success

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:

Cause

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