Wednesday, August 3, 2011

How to Customize R12 Payments Output (checks, ACH, positive pay, seperate remittance advice)

In R12 the formatting of payments (Checks, ACH, wires) and the output of related documents (positive pay, Separate Remittance Advice) is done by a Java concurrent programs that generate XML output which is then formatted by BI Publisher.

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.
The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively.

These files are located in the $IBY_TOP/patch/115/sql directory. The package allows custom elements to be created at five levels within the payment XML extract. You cannot customize the package specification, but the package body contains stubbed functions that you can customize.

The five functions are as follows:
Instruction -
FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at instruction level and run only once for the instruction.

Payment
- 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.

Document Payable -
FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

Document Payable Line -FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

Payment Process Request -
FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable level and run once for each payment process request.

Here are two exampleswhere I utilized this extension:
1) Last Day of Period for Positive Pay file: The Positive Pay File only provides the tree with for each payment that it captures. In order to add data to the positive pay file XML you must use the function IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg.

2) Count of ACH Transmissions per day: This count was needed once per payment instruction so I utilized IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg to insert the new xml element at the bottom of (the root of the XML for payment batches).
This funciton also appears in the XML stream for separate remittance advice, checks, etc. Here is a screen shot of how we coded the Get_Ins_Ext_Agg:





No comments:

Post a Comment

Best Blogger TipsGet Flower Effect