Example for customization R12 Payment Funds Disbursement XML file and adding/extend custom tags.

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;

 

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

 Here, i want to add alternate supplier name in the tag Extend/AltSupName. So modified the Get_Pmt_Ext_Agg function in IBY_FD_EXTRACT_EXT_PUB package as below.

FUNCTION Get_Pmt_Ext_Agg (p_payment_id IN NUMBER)    RETURN XMLTYPE IS    v_supplier_name       ap_suppliers.vendor_name%TYPE;    v_alt_supplier_name   XMLTYPE; BEGIN    BEGIN       SELECT NVL (HP.ORGANIZATION_NAME_PHONETIC, HP.PARTY_NAME)         INTO V_SUPPLIER_NAME         FROM iby_payments_all IPA, HZ_PARTIES HP        WHERE IPA.INV_PAYEE_PARTY_ID = HP.PARTY_ID              AND ipa.payment_id = p_payment_id;    EXCEPTION       WHEN OTHERS       THEN          v_supplier_name := NULL;    END;    --build the XML string    SELECT XMLCONCAT (              XMLELEMENT ("Extend",                          XMLELEMENT ("AltSupName", v_supplier_name)))      INTO v_alt_supplier_name      FROM DUAL;    RETURN v_alt_supplier_name; --RETURN NULL; END Get_Pmt_Ext_Agg;

Give custom tag in document like as below: