Adding new fields to the R12 Payment Funds Disbursement XML file

Standard PL/SQL package to use to extend or add additional tags to the XML file generated during a R12 Oracle Payments ‘Payment Process Request’ is IBY_FD_EXTRACT_EXT_PUB.

• You cannot customize the package specification, but package body contains stubbed functions that you can customize.
• We use this XML file as the data source for the XML Publisher cheque or wire or electronic file presentation layout.

We need to understand the structure of the XML file created by the Payments process request to use IBY_FD_EXTRACT_EXT_PUB package.

We can customize file in following levels.

Level1:

Outbound Payment Instruction: This is the top level of the XML File and there is one Outbound Payment Instruction per Payment process request.

Function: Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)

Query:

SELECT *
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;

Level2:

Outbound Payment: This is the Payment Level i.e. an individual cheque or BACS payment amount to a supplier. There can be multiple Outbound Payments per Outbound Payment Instruction.

Function: Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)

Query:

SELECT *
FROM iby_payments_all ipa
WHERE ipa.payment_id = p_payment_id;

Level3:

Document Payable: Details the documents like invoices being paid. There can be multiple Document Payable tags per Outbound Payment.

Function: Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)

Query:

SELECT *
FROM iby_docs_payable_all dp
WHERE dp.document_payable_id = P_document_payable_id;

Level4:

Document Payable Line: This level details the invoice line. There can be multiple Document Payable Line tags per Document Payable.

Function: Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)

Level5:

Payment Process Profile: This level allows XML element to be introduced at document payable level and run once for each payment process request.

Function: Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER)

Query:

SELECT *
FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;

We can add/extend additional XML tags can be added at each of these 4 levels by coding/modifying functions in IBY_FD_EXTRACT_EXT_PUB.

Example for adding/extend custom tags to R12 Payment Funds Disbursement XML file