In a previous post, I spoke of an organization that needed to create a custom BI Publisher template to accommodate their Canadian bank’s proprietary file format for EFT files in R12. What I didn’t mention was that some of the required data was stored in descriptive flexfields within Oracle. Descriptive flexfields are not included in the Oracle Payments Funds Disbursement Payment Instruction Extract data which is the standard data definition for AP funds disbursement.
As is the case with many organizations, they were concerned that they would have to modify the standard programs to accommodate their bank’s requirements. Their minds were put at ease when they learned that Oracle provided a standard API which allows organizations to add additional fields (i.e. XML Tags) to the XML extract. This standard PL/SQL package was the IBY_FD_EXTRACT_EXT_PUB.
The standard XML file that is generated by the Payment Instruction Extract is organized into 4 hierarchical groups:
- Payment Instruction – The payment instruction group encompasses the entire file.
- Payment – The payment group represents a payment to a specific supplier.
- Document Payable – The document payable group represents the individual invoices that are being paid to the supplier within the payment.
- Document Payable Line – The document payable line group represents the lines within the invoice being paid to the supplier.
Here are the specific functions within the IBY_FD_EXTRACT_EXT_PUB API which can be used to add additional fields to the XML output of each group:
| Group | Function |
| Payment Instruction | get_ins_ext_agg(p_payment_instruction_id IN NUMBER) |
| Payment | get_pmt_ext_agg(p_payment_id IN NUMBER) |
| Document Payable | get_doc_ext_agg(p_document_payable_id IN NUMBER) |
| Document Payable Line | get_docline_ext_agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) |
Here is an example of adding descriptive flexfields to the Document Payable group (i.e. from the Invoice):
-- This API is called once per document payable.
-- Implementer 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_result xmltype;
BEGIN
SELECT ai.attribute1, ai.attribute2, ai.attribute3
INTO v_att3, 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;
SELECT xmlconcat(xmlelement("InvoiceHeader",
xmlelement("InvAttribute1", v_att1),
xmlelement("InvAttribute2", v_att2),
xmlelement("InvAttribute3", v_att3)))
INTO v_result
FROM dual;
RETURN v_result;
END get_doc_ext_agg;
By modifying the IBU_FD_EXTRACT_EXT_PUB API, the organization was able to add the required data into the XML file which is the data source for their BI Publisher template. They were then able to include the additional fields in their BI Publisher template and accommodate their bank’s proprietary EFT file format.
