Generating a Single Invoice for Transactions with multiple installments
When using Oracle Cloud ERP’s Print Receivables Transactions program, the seeded functionality prints each installment of an invoice separately. For example, if a transaction has multiple installments scheduled, the program will generate and email a distinct invoice for each payment schedule ID, resulting in multiple documents per invoice.
Problem with Seeded Functionality
Transaction 1009123198 has 2 installments.
Default behaviour: Two separate invoice outputs are printed and emailed.
This can be inefficient and confusing for customers and internal processes when a single consolidated invoice document is required.
Goal
To customise the invoice printing so that all installments for a given invoice appear in a single invoice document.
Customisation Steps
01.
Understanding the Data Model Customization
The key to this customization lies in modifying the Transactions Print Data Model used by the Print Receivables Transactions program.
The data model by default uses the customer_trx_id concatenated with the payment_schedule_id as a key used for bursting, which is why installments are printed separately.
trx.customer_trx_id || '_' || ps.payment_schedule_id
02.
Adjust the Header Data Set
We will adjust the query to only select the first installment.
AND NVL(ps.terms_sequence_number,1) = 1
This condition ensures that only the first installment is used as the key for generating the invoice, preventing multiple invoices per transaction.
03.
Adjust Amount Calculations
Simply restricting rows to the first installment is not enough. The amounts on the invoice—such as line_amount, tax_amount, freight_amount, and total_amount need to be recalculated to reflect the sum of all installments, not just the first.
From
TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.amount_due_original, 'N',
(SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = trx.customer_trx_id
) ) + NVL(ps.amount_adjusted,TO_NUMBER(0)) ,TO_NUMBER(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) AS total_amount
To
TO_CHAR(NVL( DECODE(types.accounting_affect_flag,
'Y',
(SELECT SUM(arps.amount_due_original)
FROM ar_payment_schedules_all arps
WHERE arps.customer_trx_id = ps.customer_trx_id
AND arps.org_id = trx.org_id),
'N',
(SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = trx.customer_trx_id
) ) + NVL((SELECT SUM(arps.amount_adjusted)
FROM ar_payment_schedules_all arps
WHERE arps.customer_trx_id = ps.customer_trx_id
AND arps.org_id = trx.org_id),TO_NUMBER(0)) ,TO_NUMBER(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) AS total_amount
This ensures that the invoice displays the total amount due across all installments.
04.
Test the changes to the data model
- Run the ESS Job Print Receivables Transactions for a transaction with multiple installments.
- Verify that only one invoice output is generated.
- Check that the amount fields correctly reflect the total of all installments combined.
05.
Adjust the Invoice Template (if necessary):
Ensure the invoice layout can accommodate data showing all installments.
Optionally, add a breakdown of installments in the template if needed, this will require the creation of an additional Data Set for Installment lines.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
