How to customize the standard Format Payments program while upgrading to Release 12 form 11i(11.5.10.2 or earlier).

How to customize the standard Format Payments program while upgrading to Release 12 form 11i

Payment Processing in Release 11i

Payment Processing in Release 12

Multiple Applications leverage ONE payment processing solution

Oracle Payments:

Oracle Payments is a central engine to disburse and capture payments. 

•For electronic payments, it connects Oracle EBS products like Receivables and Payables to financial institutions like banks and credit card processors.

•Better working capital management by providing real-time visibility to cash inflows and outflows

•Provides a full audit trail and control to a single point of payment processing

•Efficiently Generate Payments for Multiple Organizations, Currencies and Regions

• Lower Costs associated with Transmission Protocol/ Formats

• Interfaces EBS Products with Financial Institutions like Banks and Credit Card Processors

Payments Bank Account Information:

Funds Disbursement:

In R12 Funds Disbursement Moved from Payables to new Payment module, the Payments module has replaced the previous Payables functionality and has re implemented all the templates using XML Publisher.

But from the technical and work-load perspective, the upgrade to Payments needs to re-implement any customizations to your payment instructions and you should know XML Publisher

Creation & validation of payments

Aggregation of payments into files

Format & transmission of files

Selection & approval of invoices remains in

Payables

R12  payment format steps

R12 Oracle Payment Processing Request (PPR) in AP 

PAYMENT PROCESSING REQUEST FUNCTIONALITY-

In 11i we used Payment batches to pay for multiple invoices same time. In R12, PPR is the replacement of Payment batches. R12 PPR process enables payment Administrator to select multiple invoices for payment by selection criteria and he can pause the invoice selection and payment build process. During the invoice selection review, payment manager can review the invoice selected; if the invoices were validated or approved and hence did not get included in the payment process request. He can add or remove the invoices in the Payment process and also can check the cash requirements for the full payment. Payment manager can also dismiss the individual documents or payments if necessary, and restart the payment build process.

Steps in Pay run Process-

Managing a Pay run involves 3 main processes

• Selection of the invoices for payment
• Grouping the invoices into payments
• Building the payment instruction files to either print checks or send instructions to bank.

 There are four steps in the processing of PPR:-

• Document selection – Handled by Payables(AP)
• Build Payments – Handled by Payments(IBY)
• Format Payments – Handled by Payments(IBY)
• Confirm Payments – Handled by Payables(AP)

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.

Adding new fields to the R12 Payment 

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.

Level 3: Document Payable:
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:

ML File Level	IBY_FD_EXTRACT_EXT_PUB Function To Modify	Example of Parameter Usage
OutboundPaymentInstruction	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;
DocumentPayableLine	Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)
PaymentProcessProfile	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;

Sample Code  where function had been extended Get_Pmt_Ext_Agg


FUNCTION Get_Pmt_Ext_Agg(
                          p_payment_id IN NUMBER)
  RETURN XMLTYPE

  L_INS_EXT_AGG XMLTYPE;
  l_inv_string          XMLTYPE         := NULL;
  l_output_str          VARCHAR (3000);
     
 CURSOR pay_docs (
                   p_pmnt_id                           NUMBER
                    )
      IS
         SELECT   sno
                , doc_ref_num
                , payment_id
                , payment_date
                , payment_amount
                , discount_taken
                , invoice_id
                , invoice_num
                , description
                , po_number
             FROM XYZ_TABLE

      lrec_pay_docs   pay_docs%ROWTYPE;            
 
BEGIN

    BEGIN

      FOR lrec IN pay_docs (p_payment_id)
      LOOP
            l_output_str               := rtrim(RPAD (lrec.doc_ref_num, 21)
                                          || LPAD (' ', 1)
                                          || Lpad (To_Char (Lrec.Invoice_Num, 'DD-MON-YY'), 9) || ' '
                                          || RPAD (NVL (lrec.description, ' '), 20) || LPAD (' ', 1)
                                          || RPAD (NVL (lrec.po_number, ' '), 13)|| LPAD (' ', 1)
                                          || LPAD (TRIM (TO_CHAR (lrec.payment_amount, '9,999,999,990.00'))
                                                 , 13
                                                  ));

         IF pay_docs%rowcount = 1
         THEN
            SELECT XMLELEMENT ("ChpPmtDtl", XMLELEMENT ("InvDocDtls", l_output_str))
              INTO l_inv_string
              FROM DUAL;
         ELSE
 
            SELECT XMLCONCAT (l_inv_string
                            , XMLELEMENT ("ChpPmtDtl", XMLELEMENT ("InvDocDtls", l_output_str))
                             )
              INTO l_inv_string
              FROM DUAL;
         END IF;
     
      END LOOP;

      SELECT XMLCONCAT (l_inv_string,l_ins_ext_agg)
        INTO l_ins_ext_agg
        FROM DUAL;    
     END;

  RETURN L_INS_EXT_AGG;
END Get_Pmt_Ext_Agg;

Query to retrieve the XML tags fro the above payment 
SELECT  *  FROM iby_trxn_documents WHERE 1 = 1   AND payment_instruction_id = '133591'
/

Testing e Text  Templates.

You can test eText templates using the ‘Template Viewer’ included with the BIP desktop toolkit.

From the Windows Start menu, click on : All Programs, Oracle BI Publisher Desktop, Template Viewer:


Once the Template Viewer is open:
select your working directory i.e. the folder containing your eText template and preview XML data

Change the output format to eText :

and then click on the button ‘Start Processing’ to view the generated text file :

Will be adding more details very soon ..