Sunday, July 31, 2011

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:

Function To Modify
Example of Parameter Usage
(p_payment_instruction_id IN NUMBER)
SELECT * FROM iby_pay_instructions_all
WHERE 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 IN NUMBER,
P_line_number IN NUMBER)

(p_payment_service_request_id IN NUMBER)
SELECT * FROM iby_pay_service_requests
WHERE 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'


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)
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;

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.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect