Adding new fields to the R12 Payment Funds Disbursement XML file

In 11i, we used Payment Batches to pay multiple invoices same time. In R12, PPR is the replacement for 11i Payment Batches. Release 12 payment setup enables a 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 selected invoices, the invoices that met the criteria but were either not validated or were not approved and hence did not get included in the payment process request. He can adjust the invoice selection by adding or removing the invoices and can also review the cash requirements. While reviewing the payments, payment manager can dismiss individual documents or payments if necessary, and restart the payment build process.

Frequently Used Terms..
Oracle Payments
Oracle Payments is an e-Business Suite module Payables will leverage to group invoices into payments, create instructions, and print or communicate with the bank. Payment Manager(OA page) is the function you can access it from Payables respondibilty.
Navigation Path: Payables->Payments:Entry->Payment Manager

Pay Run
A business action to select multiple invoices on a regular basis to be processed for payment. This may also be referred to as creating and processing payment batches and, in this release, managing a payment process request through completion

Payment Process Request
The payment process request is the selection of invoices into a group for payment processing.

Payment Instruction
Information compiled from one or more payment process requests that is formatted and either transmitted to a financial institution for payment or used in-house to print check documents..

Template
Templates provide a way to store section criteria, payment attributes, and processing rules that can be reused for single pay runs or scheduled pay runs.

Payment Manger Page
There are five tabs under payment manger.

1.Home
The Home tab on Payment Manager Dashboard presents the useful information for a Payment Manager to:

• Monitor the progress of the recent pay run processes
• Highlight any payment processes that require attention and automatically prompt to take appropriate actions.
• Shortcuts and Tabs for initiating, reviewing and adjusting proposed funds disbursements

2.Templates
Using Payment Manager dashboard, a Payment Manager can perform all the tasks associated with pay run process. In the Template tab he can click the “Create” button to create new templates. He can also query a template and then use it to submit or schedule the payment process requests and run cash requirements before a pay run.

3.Payment Process Requests(PPR)
Payment Process Requests tab can be used to submit a single payment process request or schedule the repeating payment process requests. The pending action on the payment process request can be performed using “Start Action” icon and the payment request can be cancelled using “Cancel” icon. Clicking on the Payment Process request name, payment manager can drill down to the details.

Process Automation tab in PPR
The pay run process itself provides for processing steps that you can pause for review based on your needs. In Process Automation tab, the payment manager can specify up front whether the pay run process should pause for review or if the payment process will be fully automated. Of course, if issues arise during processing that require user input, the process will pause regardless of these options.

Processing options in Process Automation tab

• Maximize Credits: If Maximize Credits checkbox is enabled then during invoice selection, if there is any credit for a payee, after interest and payment withholding calculations the system will group all scheduled payments for the payee site together to be paid on one payment, and if the sum is negative, the system will reduce the credit amount so the sum is zero.
• Stop Process for Review After Scheduled Payment Selection
• Calculate Payment Withholding and Interest During Scheduled Payment Selection
• Stop Process for Review After Creation of Proposed Payments
• Create Payment Instructions option

If the user wants immediate payment instructions creation, the user can set this option to start the payment instruction program immediately when the payment process request has a Completed status. This option has an additional function: It ensures that payments from this payment process request will not be combined with payments from other payment process requests when the system builds the payment instructions.
Or,
the user can set the option to wait until the Payment Instruction Program is submitted, typically, in this case an enterprise would schedule the Payment Instruction Program to run periodically. An enterprise would choose this option to take all built payments from multiple payment process requests and build fewer payment instructions.

4.Payment Instructions
Payment Manager can use the Payment Instructions tab to review the status of the payment instructions and if required, can perform any subsequent actions. He can also drill down into the details of the payment instruction and can void all the payments in the instruction.

5.Payments
Payment Manager can use the Payments tab to review the status of the payments created by his payment process requests. He can also can drill down into the details of the payments to stop or void the payments.

Steps in Pay Run Process
Managing a Pay Run involves 3 main processes:
1)Selection of the invoices for payment
2)Grouping the invoices into payments
3)Building the payment instruction files to either print checks or send instructions to the bank.

Follow red mark numbers in the picture to get the sequence of process steps in Pay Run Process

1. Invoice Selection
After user submits PPR, the Payment Process request completes with the status “Invoices Pending Review” if it has been configured to pause after the invoice selection. Clicking on “Start Action” icon navigates the user to the “Selected Scheduled Payments” page.

On the “Selected Scheduled Payments” page, Payment Manager can review the total count of selected scheduled payments. Amount remaining , discounts, payment amount, and interest due can also be reviewed for each currency in the payment process request.

The page also lists all the invoices along with their details. Payment Manager can add or remove the scheduled payments or modify the Discounts and payment amounts.

Clicking on the “View Unselected” takes the Payment Manager to a “Unselected Scheduled Payments” page that gives the following information:
Counts for invoices that were never validated and that failed validations
Counts for invoices that require approval and where approval is rejected
Counts of invoices on Scheduled Payment Hold and Supplier Site hold
Counts where Payee total is zero or less and where Discount rate is too low
Count of Unselected Payment Schedules, Total Amount, and Discount per currency
List of Invoices with invoice information and reason for not getting selected

Payment Manager can add more Scheduled Payments by clicking on the “Add Scheduled Payments”, and choosing the search criteria for the documents payables from the list of values.

Once the Payment Manager is done reviewing the payment process request, he can click on the “Submit” button to initiate the Payment creation process. This action also generates the Scheduled Payment Selection
Report again.

The Payment Process will complete with the status “Information Required – Pending Action” if certain information required for the payment creation was missing on scheduled payments. Clicking on “Start Action” icon navigates the user to the “Complete Document Assignments” page.

2.Grouping into Payments
The Payment Process request completes with the status “Pending Proposed Payment Review” if it has been configured to pause after the creation of proposed payments. The payment process request also displays the count for documents that were rejected during payment creation. Clicking on “Start Action” icon navigates the user to the “Review Proposed Payments” page.

In the Review Proposed Payments page, payment manager can review the payment information for the selected scheduled payments.

After reviewing, payment manager can then specify the action “Run Payment Process” to submit the Payment build process. After this action, the payment process request has the status of “Assembled Payments”.

Payment Manager can drill down to view payment details by clicking on the Payment Process request link. He can view the number of payments, documents, and Total Payment Amount per currency. Individual payments are also listed along with more information. By selecting the radio button of a payment, payment manager can view the scheduled payments that got included in that payment.

Clicking on “Rejected and Removed Items”, Payment manager can navigate to see the details for scheduled payments that got rejected/removed.

Rejected and Removed Items page lists the rejected document payables, and clicking on the reference number link you can view the details of the document and the reason it got rejected.

3.Building Payment Instructions
For creating Printed payment instructions, Payment Manager can specify the criteria for selecting payments and printing information. The criteria can include the Payment Process profile, Currency, Internal Bank Account, Payment Document, Payment Process Request, etc.

To Find Column

select
AA.TABLE_TYPE,
AA.TABLE_NAME,
AA.COLUMN_NAME,
--aa.FLEXFIELD_NAME,
decode(AA.FLEXFIELD_NAME, null, AA.DESCRIPTION, bb.form_left_prompt) as DESCRIPTION,
AA.COLUMN_SEQUENCE,
AA.COLUMN_TYPE,
AA.WIDTH,
AA.NULL_ALLOWED_FLAG,
AA.DEFAULT_VALUE
from

(select B.TABLE_TYPE,
B.TABLE_NAME,
A.COLUMN_NAME,
A.COLUMN_SEQUENCE,
A.COLUMN_TYPE,
A.WIDTH,
A.NULL_ALLOWED_FLAG,
A.DESCRIPTION,
A.FLEXFIELD_NAME,
a.flexfield_usage_code,
a.flexfield_application_id,
a.flex_value_set_application_id,
a.flex_value_set_id,
A.DEFAULT_VALUE
from
FND_COLUMNS A,
Fnd_Tables B
WHERE
A.TABLE_ID = B.TABLE_ID
AND B.TABLE_NAME = 'OE_ORDER_LINES_ALL') aa,
------------------
(select c.DESCRIPTIVE_FLEXFIELD_NAME,
c.application_column_name,
c.form_left_prompt,
-- c.descriptive_flex_context_code,
c.form_above_prompt
from Fnd_Descr_Flex_Col_Usage_Tl c, FND_DESCR_FLEX_CONTEXTS_TL d
WHERE c.DESCRIPTIVE_FLEXFIELD_NAME = d.DESCRIPTIVE_FLEXFIELD_NAME
AND c.DESCRIPTIVE_FLEX_CONTEXT_CODE =
d.DESCRIPTIVE_FLEX_CONTEXT_CODE
and c.application_id=d.application_id
AND c.LANGUAGE = d.LANGUAGE
AND c.LANGUAGE = 'JA'
-- and substr(c.descriptive_flex_context_code,4,2)='AR'
) bb

where
aa.column_name = bb.application_column_name(+)
and aa.FLEXFIELD_NAME = bb.descriptive_flexfield_name(+)
order by
AA.COLUMN_SEQUENCE

Adding new fields to the R12 Payment Funds Disbursement XML file

BY_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 Instruction
This 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:

XML File Level	IBY_FD_EXTRACT_EXT_PUB Function To Modify	Example of Parameter Usage
OutboundPayment Instruction	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;
DocumentPayable Line	Get_Docline_Ext_Agg (p_document_payable_id IN NUMBER, P_line_number IN NUMBER)
PaymentProcess Profile	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;

As shown in the example below, it is best practice to group all of your custom tags within a parent tag called 'Extend'

EXAMPLE:

The following is an example of adding an 'Extend' tag and some additional invoice data to the ‘DocumentPayable’ level of the XML file:

--
-- This API is called once per document payable.
-- Implementor 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_detail_line VARCHAR2(255);
v_result XMLTYPE;
BEGIN

SELECT ai.attribute1,ai.attribute2,ai.attribute3
INTO v_att1, 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;

v_detail_line := xxcsd_build_inv_line(p_document_payable_id);

--now build the XML string
SELECT XMLConcat(XMLElement("Extend",
XMLElement("XXCSD_INVOICE_DFF1", v_att1),
XMLElement("XXCSD_INVOICE_DFF2", v_att2),
XMLElement("XXCSD_INVOICE_DFF3", v_att3),
XMLElement("XXCSD_INV_DETAIL", v_detail_line) ) )
INTO v_result
FROM dual;

RETURN v_result;

END Get_Doc_Ext_Agg;

As you can see, I’ve added 4 new tags ‘XXCSD_INVOICE_DFF1’, ‘XXCSD_INVOICE_DFF2’, ‘XXCSD_INVOICE_DFF3’ and ‘XXCSD_INV_DETAIL’ to the XML File:

These new XML tags can now be referenced in my XML Publisher presentation template and added to my cheque or electronic payment file layout.

Oracle R12 NACHA Customizations

Starting in release 12, Oracle has changed the way payment formats are applied
against the data set. Out with Reports, and in with BI Publisher/XML.

Recently, we were faced with customizing the NACHA US file format to meet the
receiving bank's specs. In this post, we will walk you through the steps to quickly
make this change.

Before we begin, it is important to verify if your payment version has been
patched up to deal with a known issue around the IBY IDENTITY file. This file
works in conjunction with your Nacha BI Publisher template, and when it comes
to the seeded NACHA template, this file is saved properly in the base XDO tables.

Follow this link to Patching IBY IDENTITY FOR R12 to see if you need to mannualy
apply a small script to your instance. It is a linux command that makes the file
available for your custom template upload. When you upload a custom template,
Oracle saves a record in the XDO_LOBS table along with one extra row for this
IBY IDENTITY file, and without it you will receive the error "Error: an error
occurred during formatting. Please verify the template is valid."

Now, on with the NACHA change. For this example we will assume you will use
tags already supplied by the datasource "IBY_FD_INSTRUCTION_1_0." If you
need to add custom tags, please refer to the public package
"IBY_FD_EXTRACT_EXT_PUB" where Oracle allows you to add on to the xml tree.

Step 1: Download the NACHA format RFT file that best suits your need. In the
example below, we chose the CCDP format. You do this in the XML Administrator
responsibility. Just drill down to where you can see the "download" link. Pick
either the one with a territory or without, it doesn't matter.

Step 2: Rename this on your hard drive using your custom prefix ("XX") on the
front.

Step 3: Open the RTF and modify according to your requirements. This uses a
different format than what you may be accustomed to with BI Publisher. This is
the eText Outbound style and almost looks like a document about Nacha, rather
than true code. But it is a nice method Oracle introduced to allow for readable
code to a functional person, as well as an easy way to change a fixed format file.

Step 4: Upload the new file under a new template that you setup using the same
datasource as the standard Nacha.

Step 5: Create a new Payment Format that uses this template

Step 6: Create a new Payment Profile that uses the new Format.

Step 7: Create a payment run using the new format.
Below is an example of how to make specific changes in the template.
Example Requirements

1. Change the name being paid from using the supplier name to the tax reporting
name first if it exists, otherwise stick with the vendor name.

2. Remove the debit records

3. Change the Addenda count in record "8" to reflect that we removed one of the
two record "6" because of the debit.

Payee :
To change the payee it is important to note that Oracle supplies in the XML file
two valuable names that could go on the payment record. Within the tree, under
the parent tag of "Payee", you will see a "Name" tag as well as "AlternateName."
Oracle seems to determine the AlternateName from the tax reporting name field
on the supplier record. This can come in handy when you vendor name has other
information in it that does not reflect their bank account name. However, most
companies keep the tax reporting name in sync with their 1099 company or
personal name as it was probably used when opening a bank account. In position
55, see the syntax for adding the AlternateName tag as the first in line.


Remove Debit Records :
Some banks do not like this extra record. You need to be careful about what you
delete in this file, being cautious not to remove specific table heading rows that
are needed.

See the image below for what it looks like after removing part of the "6" record
which seems to be embedded in the "7".


Notice how the "7" record must still be within the loop of OutboundPayment. The
debit headers were removed.

Change Addenda Count in Record "8"

In position 5, the Addenda Count field needs the total number of transactions ("6"
records) plus one. you can see in the original formula,

InstructionTotals/PaymentCount*2 + 1

That is multiplies the payment count times two. This is because of the debit and
credits. To keep it documented in the code, keep the multiplier and change to:

InstructionTotals/PaymentCount*1 + 1
Summary :
Once you upload, this takes effect immediately, so try to pay another invoice and
see the output.