Adding new fields to the R12 Payment Funds Disbursement XML file

BY_FD_EXTRACT_EXT_PUB is a standard PL/SQL package that is used to extend (i.e. add additional tags to) the XML file generated during a R12 Oracle Payments ‘Payment Process Request’:

This XML file is then used as the data source for the XML Publisher cheque or electronic file presentation layout.

To understand how to use IBY_FD_EXTRACT_EXT_PUB, we have to understand the structure of the XML file created by the Payments process request.

There are 4 main levels to the file. These are:

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

Level 2: 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.

Level 3: Document Payable:
Details the documents (i.e. invoices) being paid. There can be multiple Document Payable tags per Outbound Payment

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

Additional XML tags can be added at each of these 4 levels by coding different PL/SQL functions in IBY_FD_EXTRACT_EXT_PUB.

The following table lists the functions you need to modify to add additional tags to each level of the XML file:

XML File Level	IBY_FD_EXTRACT_EXT_PUB Function To Modify	Example of Parameter Usage
OutboundPayment Instruction	Get_Ins_Ext_Agg (p_payment_instruction_id IN NUMBER)	SELECT * FROM iby_pay_instructions_all WHERE payment_instruction_id = P_payment_instruction_id;
OutboundPayment	Get_Pmt_Ext_Agg (p_payment_id IN NUMBER)	SELECT * FROM iby_payments_all ipa WHERE ipa.payment_id = p_payment_id;
DocumentPayable	Get_Doc_Ext_Agg (p_document_payable_id IN NUMBER)	SELECT * FROM iby_docs_payable_all dp WHERE dp.document_payable_id = P_document_payable_id;
DocumentPayable Line	Get_Docline_Ext_Agg (p_document_payable_id IN NUMBER, P_line_number IN NUMBER)
PaymentProcess Profile	Get_Ppr_Ext_Agg (p_payment_service_request_id IN NUMBER)	SELECT * FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;

As shown in the example below, it is best practice to group all of your custom tags within a parent tag called 'Extend'

EXAMPLE:

The following is an example of adding an 'Extend' tag and some additional invoice data to the ‘DocumentPayable’ level of the XML file:

--
-- This API is called once per document payable.
-- Implementor should construct the extract extension elements
-- at the document level as a SQLX XML Aggregate
-- and return the aggregate.
--
FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)
RETURN XMLTYPE
IS
v_att1 ap_invoices_all.attribute1%TYPE;
v_att2 ap_invoices_all.attribute2%TYPE;
v_att3 ap_invoices_all.attribute3%TYPE;
v_detail_line VARCHAR2(255);
v_result XMLTYPE;
BEGIN

SELECT ai.attribute1,ai.attribute2,ai.attribute3
INTO v_att1, v_att2, v_att3
FROM iby_docs_payable_all dp,ap_invoices_all ai
WHERE dp.calling_app_doc_unique_ref2 = ai.invoice_id
AND dp.document_payable_id = p_document_payable_id;

v_detail_line := xxcsd_build_inv_line(p_document_payable_id);

--now build the XML string
SELECT XMLConcat(XMLElement("Extend",
XMLElement("XXCSD_INVOICE_DFF1", v_att1),
XMLElement("XXCSD_INVOICE_DFF2", v_att2),
XMLElement("XXCSD_INVOICE_DFF3", v_att3),
XMLElement("XXCSD_INV_DETAIL", v_detail_line) ) )
INTO v_result
FROM dual;

RETURN v_result;

END Get_Doc_Ext_Agg;

As you can see, I’ve added 4 new tags ‘XXCSD_INVOICE_DFF1’, ‘XXCSD_INVOICE_DFF2’, ‘XXCSD_INVOICE_DFF3’ and ‘XXCSD_INV_DETAIL’ to the XML File:

These new XML tags can now be referenced in my XML Publisher presentation template and added to my cheque or electronic payment file layout.