Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments.
If there is a requirement to change the BI Publisher to add additional columns to the template, we need to add the columns in BI Publisher, and for the values we may need to modify the function get_pmt_ext_agg which will help us to get the customized columns.
FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at payment level and run once for each payment in the instruction.
This is a sample example where we had included contact name and contact phone to BI Publisher for each payment.
The below piece of code had helped to add Custom Columns to the Wire template while payment process in EBS.
FUNCTION get_pmt_ext_agg (p_payment_id IN NUMBER)
RETURN XMLTYPE
IS
l_xml_res XMLTYPE;
l_sua_group VARCHAR2 (150);
l_expire_date DATE;
l_contact_emails VARCHAR2 (500);
lc_contact_name VARCHAR2 (100);
lc_contact_phone NUMBER;
BEGIN
–V1.0
BEGIN
SELECT ieba.contact_name, ieba.contact_phone
INTO lc_contact_name, lc_contact_phone
FROM iby_payments_all ip, iby_ext_bank_accounts ieba, ce_banks_v ceb
WHERE 1 = 1
AND ip.payment_id = p_payment_id
AND ip.external_bank_account_id = ieba.ext_bank_account_id(+)
AND ceb.bank_party_id = ieba.bank_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Unable to fetch the XML data for payments’
|| SQLERRM
);
END;
SELECT XMLCONCAT (XMLELEMENT (“Extend”,
XMLELEMENT (“ContactName”, lc_contact_name),
XMLELEMENT (“ContactPhone”, lc_contact_phone)
)
)
INTO l_xml_res
FROM DUAL;
RETURN l_xml_res;
END get_pmt_ext_agg;
This Function get_pmt_ext_agg will work once for each payment ID.
We need to add the above columns to BI Publisher Template.
