Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, February 25, 2012

Customizing Standard Check Print Report in Oracle R12

Over View of Check Print Report
Check Print Report (Format Payment Instructions) Program used display the check details which we issue to the suppliers and the invoice details of the suppliers.

This program will be called when we create invoices for the suppliers and when make the payments against these invoices.

The standard check print report displays the check information at the top and the invoice details just below the Check information.

Along this program Standard remittance advice program also called. It displays the invoice details for a particular Check, when we have given limited number of invoices to be displayed in the check print report.

This Learning deals how to customize these two report layouts and how to derive the additional data in R12.
How to run the Standard Program
To run this report we need to have invoices created for suppliers. Then submit the request to generate checks and as well as to display the check information.
Create Invoices:
Navigation: Payables Manager-->Invoice-->Entry-->Invoice Batches

Create one invoice batch and click on invoices.



Enter the required information as below. And click on distributions


And enter the required information as below. Save the record and validate it by clicking on actions.





Once it is got validated, submit the request to generate checks to make payments.
Navigate to: Payables Manager-->Payments-->Entry-->Payments Manager



Search for the Payment process request template which we have for Payment method ‘Check’. if it doesn’t exist create one click on Submit Single Request.



Then you can check the status of the program from SRS Window. This program builds the payments. Once the program builds the payments you can start making payments. This will be visible under pending actions.



Then select the list of suppliers and invoices created for them to whom we want to make payments. And click on go. The system submits the standard program Format payment instruction as well as separate Remittance Advice programs. You can check the status and output from SRS window.



It first formats the both templates Check format and remittance advice and populates the base table IBY_TRXN_DOCUMENTS.

We can have a look at the xml tags generated for this data in the column document of the table IBY_TRXN_DOCUMENTS.
How the program works?
While submitting the request we need to select the appropriate Payment process profile and Payment document. As we aware that we have template format assigned to this Payment process profile. So when we submit the request the program creates the xml tags for the data, it generates and displays it on the template which is assigned to the format template.
Common customizations to the check Print report
We normally get the requirement to customize the standard check print report to,
1. Derive/extract the data which is not achieved from standard functionality.
2. To display the data on layout as per the business requirement

Changes required to the code:

1. If we require to fetch the additional information of the supplier to whom we are issuing the check.
To derive the additional data we do not require to customize the existing rather we need to add the code to the seeded pl/sql package which has provided by oracle

Changes required to the Layout:

1. Many a times, business needs to display the check information with different formats, like displaying the logo and displaying the additional details of payee.
2. Controlling the stub (invoice details) when the number of these invoices exceeds certain limits. For example displaying the entire stub details in separate program when the total number of invoices exceeds certain limit.

To display the data on customized layout we need to additional setup.
Customizing the code
As it is a java concurrent program, it calls the seeded pl/sql package to generate the xml tags for the data that we are going to display. In addition it also calls one more package IBY_FD_EXTRACT_EXT_PUB which we use to add the code to get the additional data in the form of xml tags.

As we are aware that we have the data for check print report at various levels (Master and child levels), we have the seeded code IBY_FD_EXTRACT_EXT_PUB to derive the data at all these levels.

1. Assume, we need to find the payee site alternate name for a particular payment

As, it is for a particular payment we need to add the code to the function which has the input payment id as a parameter.

Get the code for the package IBY_FD_EXTRACT_EXT_PUB and add the code to the function Get_Pmt_Ext_Agg which accepts the payment id as a parameter.

Refer the below code to derive the Payee site alternate name.

CREATE OR REPLACE PACKAGE BODY IBY_FD_EXTRACT_EXT_PUB AS
FUNCTION Get_Pmt_Ext_Agg (p_payment_id IN NUMBER)
RETURN XMLTYPE
IS
l_alt_name varchar2(100);
BEGIN
SELECT XMLConcat (
XMLElement ("Extend",
XMLElement ("Name", 'PAYEE_SITE_ALT_NAME'),
XMLElement ("Value", payee_site_alternate_name)))
INTO l_alt_name
From IBY_PAYMENTS_all where payment_id = p_payment_id;
RETURN l_alt_name;
END Get_Pmt_Ext_Agg;
Save the code and compile it, it creates the xml tags Name and Value under the group Extend. And this gets created at payment id level.
Customizing the Layout
We need to customize the existing layout as per the business requirement. But you can’t replace the existing template (rtf) with the customized rtf.

To display the data in customized rtf we need to do few setups.

Even after doing so, we can have the existing functionality (displaying the data in seeded template) remains same; if we want we can display the data in seeded template at any time.
Required Setup
Assume the requirement where we need to customize both Check print as well as Remittance advice. Let’s do the setup to display the data on customized layouts.

As part of the setup we need to

1. Develop/Customize the template as per the business requirement.
2. Create template definition under existing data definition and attach the created template.
3. Create payment format for the template.
4. Create payment document for bank Account.
5. Create payment process profile.

1. Develop/Customize the template as per the business requirement.
If our requirement is to just add few other fields in the layout we can download the existing rtf and can customize it.

As we know that Standard Check Format template is going to be called when the standard report Format payment instructions got submitted, download the rtf which is assigned to the Standard Check Format template.

Do the same for Remittance advice too.

Or we wan develop the rtf from the scratch if we required to do lot of customization.

2. Create a template definition under existing data definition and attach the created template
Navigation: XML Publisher Administrator-->Templates-->Create Template

We need to create custom template under existing data definition Oracle Payments Funds Disbursement Payment Instruction Extract 1.0

Make sure we have created template under payments Application, as we are going to use it in the same application.

In this case we have created XX Check Format template.

Do the same for remittance advice too.
3. Create payment format for the template
Create payment format for the created template XX Check format in the above steps.

Navigation:Payables Manager-->Setup-->Payments-->Payment Administrator-->Formats-->Formats-->Go to task

And create it with the following data.

For check Format:
For Remittance: Select Disbursement Remittance Advice and click on create.
4. Create payment document for bank Account
We need to create Payment document which controls the check number with which number the number has to start.

We need to find which bank account the business uses.

Navigation:Payables Manager-->Setup-->Payment-->Bank Accounts

Query for Bank Account Name and then click on Manage Payment Documents.

And click on create. Create the document with the following details

5. Create payment process profile
Create payment process profile and assign payment document to it.

Navigation:Payments Manager-->Setup-->Payment-->Payment Administrator-->Codes-->Payment Process Profiles-->Go to Task-->Create
Under Usage Rules Tab
Under Payment Instruction Format Tab
Under reporting tab
We have done with the setup.

Now create invoices for suppliers and make the payments while doing the payment selects the custom profile and custom payment document which we have created earlier. By doing so it picks the custom template, format which assigned to this profile and displays the data on the custom layout

3 comments:

Sree said...

Excellent Post. Thanks a Lot.

Anonymous said...

Nice Post

Jemmi said...

Awesome documentation

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect