Customer Merge Program Does Not Transfer PAYMENT_TRXN_EXTENSION_ID on the Invoice
Last updated on OCTOBER 10, 2017
Applies to:Oracle Receivables - Version 12.1.3 and later
Oracle Trading Community - Version 12.1.3 and later
Information in this document applies to any platform.
The Customer / Site Merge program does not update the PAYMENT_TRXN_EXTENSION_ID on the invoice.
The Bill To Site Reference is (automatically) updated on the invoice, due to the merge, and the invoice is still open.
The program should look up the Direct Debit (DD) Bank Account Assignment Details for this site.
If this does not match the existing one, the PAYMENT_TRXN_EXTENSION_ID P should be replaced by the one referring to the DD Bank Account Assignment on the invoice's new Bill To Site (Bill To Site A).
If there is no active DD Bank Account Assignment on the new Bill To Site, the PAYMENT_TRXN_EXTENSION_ID on the invoice should be removed.
As the Direct Debit invoice still refers to the “before merge” assignment id after the merge, meaning to the end dated assignment with the old site_use_id, the DD invoice is not included in the DD receipt batch
For SEPA Direct Debit purposes, we need to end date the assignment of the DD bank account on the to be merged customer/site before the merge.
How to reproduce:
1. Make an invoice for a customer with DD assignment on the sites.
2. Execute the following query
SELECT DISTINCT PAYMENT_TRXN_EXTENSION_ID
, RCT.BILL_TO_CUSTOMER_ID TRX_CUST_ACCOUNT_ID
, IEPA.CUST_ACCOUNT_ID ASSIGN_CUST_ACCOUNT_ID
, RCT.BILL_TO_SITE_USE_ID TRX_SITE_USE_ID
, IEPA.ACCT_SITE_USE_ID ASSIGN_SITE_USE_ID
FROM IBY_EXT_BANK_ACCOUNTS IEBA
, IBY_PMT_INSTR_USES_ALL IPIUA
, IBY_EXTERNAL_PAYERS_ALL IEPA
, IBY_FNDCPT_TX_EXTENSIONS IFTE
, RA_CUSTOMER_TRX_ALL RCT
WHERE 1 = 1
AND IEBA.EXT_BANK_ACCOUNT_ID = IPIUA.INSTRUMENT_ID
AND IPIUA.EXT_PMT_PARTY_ID = IEPA.EXT_PAYER_ID
AND IFTE.INSTR_ASSIGNMENT_ID= IPIUA.INSTRUMENT_PAYMENT_USE_ID
AND RCT.PAYMENT_TRXN_EXTENSION_ID = IFTE.TRXN_EXTENSION_ID
AND IEBA.BANK_ID = 1136960 -- = DIRECT DEBIT BANK
AND RCT.TRX_NUMBER= :C_TRX_NUMBER;
3. Once the invoice is completed, go to the customer and the site on which you booked the invoice. End date the DD bank account assignment and the receipt method assignment.
4. Go to the account merge form and execute a site merge for the customer of site x into site y. Select the option to delete after merge.
5. After the merge, execute the query from step 2 again
You can notice that the PAYMENT_TRXN_EXTENSION_ID is still the same as before the merge.
This is only relevant for open invoice.
This should not be done for closed invoices.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms