IBY_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 InstructionThis 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.
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
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.
--
-- 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
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;
--
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 1=1
INTO v_att1, v_att2, v_att3
FROM iby_docs_payable_all dp,ap_invoices_all ai
WHERE 1=1
AND dp.calling_app_doc_unique_ref2 = ai.invoice_id
AND dp.document_payable_id = p_document_payable_id;
AND dp.document_payable_id = p_document_payable_id;
v_detail_line := xxcsd_build_inv_line(p_document_payable_id);
--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;
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 shown above; 4 new tags added are - ‘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 XML Publisher e-Text template and added to cheque or electronic payment file layout.
