AR Statement Print report is used to send statements to customers on a regular basis to notify the outstanding balance that the customer needs to pay. This is a standard report but it requires several setup steps to be used by any organization.

 

 

Step 1: Set Profile Option

Responsibility: System Administrator

Navigation: Profile > System

Query for Profile Option, Concurrent: Report Access Level

Step 2: Setup the Remit-To address for Print Statement Report

Navigation: Setup > Print > Remit To Addresses

This form contains all the address of the different operating units which will send the statements to the customers.

Remit To address setup

Step 3: Check whether a “Statement” Site is setup for customers

Responsibility: AR responsibility

Navigation: Customers > Standard

Query for a customer. Find customer, address, then open Site Uses

Check for Statement Location. This is the site for which Statement Print report can be run for this customer. Multiple sites can be enabled for a single customer.

 

Customer Site statement setup

Step 4: Enable a site to receive statements

Query for a customer and go to the site. Open the Payment Methods tab. Ensure that “Send Statements” box is checked. Else this customer site will not appear on the Statement form and hence no statement can be run for this site.

 

Customer Site

Go to the Payment Methods tab

Ensure “Send Statement” is checked

Customer Site “Send Statement” checked

Step 5: Setup statement cycle

Navigation: Print > Statement Cycles

The Statement Print Report can only run for the Statement Dates setup in this form

Statement Cycle

 

The concurrent program default output is TEXT. If the business requirement is to generate the output in PDF or any other format then we need to change the output format so that XML Publisher can publish the output in the desired format. We also need to add a template for this report. The steps are given below:

Responsibility: System Administrator

Navigation: Concurrent > Program > Define

Query for Short Name, ARXSGP.

Change output format of Statement Generation Program to XML

Statement Generation Program

On the same form, query for Short Name: ARXSGPO

Change output format of Print Statements to XML

Now for the XML Publisher setup steps

Responsibility: XML Publisher Administrator

Navigation: Data Definition

Create a new data definition named “ARXSGP”

Data Definition creation

Name: ARXSGP

Code: ARXSGP

Application: Receivables

Description: Statement Generation Print

 

Create a new data template named “ARXSGP” and upload the RTF template

Name: ARXSGP

Code: ARXSGP

Application: Receivables

Data Definition: ARXSGP

Type: RTF

Template: CLE_F_ARCUSBALSL.rtf

Language: English

The template can be any template you build. The template that we have used is the template that Oracle provides in Metalink as a sample template for this report to Oracle’s customers who are setting up the output to be published through XML Publisher

Template creation

Upload the template and click Apply button

Saved Template

 

 

 

 

 

Draft Statement option

According to Oracle, AR Statement Print report should be executed for some customers in Draft Mode, i.e. Option = Draft , on the Print Statement form. In this mode the report acts as a preview for this customer.

Print Statements option

After viewing the draft statements the Statement Print report should be fired for all customers simultaneously. This is done by selecting Option= Print Statement and Customer Name=blank. A Statement Date has to be selected. Once the report is generated for all customers, the Statement Date cannot be used for printing statements any more as Oracle will flag this date, i.e. on Statement Cycle form Date Printed field will be populated for the corresponding Statement Date (See Step 5 of Setup steps above)

Reprint Statements option

Once the Statement Date has been flagged this date will not appear when Option, Print Statements, is selected. If the need arises for running a Statement for a customer for this date then the option, Reprint Statements, has to be used.

 

Generate a statement

Responsibility: Any AR responsibility

Navigation: Print Documents > Statements

Print Statement form

Check the output of the Statement Generation program which will be in text or XML format from the Concurrent Program definition form.

There are 2 requests executed for each run of the report. The first one is Statement Generation Program and the second Print Statements is. The Statement output is given by the second request only.

Once the Submit button is pressed on this form the field, Request Id, will be populated by the first request id.

 

Publish the output in XML Publisher

If the output of the Statement Generation program is set to XML it means that the output has to be published by XML Publisher. Unfortunately the output is not automatically sent to XML Publisher.

It is interesting that both request names are “Statement Generation Program”. For some unknown reason Oracle decided to rename theStatement Print program request to Statement Generation Program after the request is completed. Until the Statement Print program completes it shows its own name in the Find Requests form.

We need to invoke XML Publisher to generate the output in the required format. Run the XML Report Publisher concurrent program by passing the details of the concurrent request of the Statement Generation Program so that the output is generated in PDF.

Run XML Report Publisher

The drop down for Report Request will contain all the requests. Select the program “Statement Generation Program” and the request that was executed for this particular statement.

Enter the parameters as shown.

Report Request: Statement Generation Program

Template Application: Receivables

Template: Statement Print Report

Template Locale: en

Output Format: PDF

Once the concurrent request is fired, the request name becomes “Statement Generation Program (XML Report Publisher)”. Click on Output for the request to see the output in PDF

Statement for a customer

Do note that the Supplier Address is coming from the Remit To address setup in Step Steps, Step 2, above. Let us look at the report in a little closely,

Statement for a customer

Tables

The Statement Print Report shows all open transactions since the last Statement Date. That is, when the statement date on the Statement Cycle form (Setup Step 5 above) for which Date Printed is populated.

 

if you like to check whether certain statements will be printed or not, you can run the following query,

SELECT rcta.trx_number, apsa.actual_date_closed, apsa.gl_date_closed, apsa.actual_date_closed- apsa.gl_date_closed

FROM ar_payment_schedules_all apsa, ra_customer_trx_all rcta

WHERE apsa.customer_trx_id = rcta.customer_trx_id AND rcta.trx_number IN (’1900504266′,’1900504350′, ’1900504846′) — Transaction numbers Eg. Invoice num, Receipt Num,

 

The field, actual_date_closed, will determine whether the report will pick up the transaction for the statement date for which the Statement Print report is executed.

 

When the Statement Print report is executed, the first concurrent request, Statement Generation Program (Shown in customizing output format above). This program populates a standard table, AR_STATEMENT_LINE_CLUSTERS. The 2nd concurrent request, Statement Print Report pulls data from this table and generates the output.

 

Automate executing XML Report Publisher program

Users might not like the idea of executing XML Report Publisher program after firing the Statement Generation program. We can automate this part so that users will not need to run the XML Report Publisher program.

In this case we can add a small customization into Statement Generation Program (ARXSGPO) . We can add the following code in the After Report trigger.

function AfterReport returnbooleanis

   l_req_id       NUMBER;

begin

 

   BEGIN

      l_req_id :=

         fnd_request.submit_request

                              (application      =>‘XDO’,

                               program          =>‘XDOREPPB’,

                               start_time       =>SYSDATE,

                               sub_request      =>FALSE,

                               argument1        =>:p_conc_request_id,

                               argument2        =>222,

                               argument3        =>‘ARXSGP’,

                               argument4        =>NULL,

                               argument5        =>‘N’,

                               argument6        =>‘RTF’,

                               argument7        =>‘PDF’,

                               argument8        =>NULL,

                               argument9        =>NULL,

                               argument10       =>NULL,

                               argument11       =>NULL,

                               argument12       =>NULL,

                               argument13       =>NULL,

                               argument14       =>NULL,

                               argument15       =>NULL,

                               argument16       =>NULL,

                               argument17       =>NULL,

                               argument18       =>NULL,

                               argument19       =>NULL,

                               argument20       =>NULL,

                               argument21       =>NULL,

                               argument22       =>NULL,

                               argument23       =>NULL,

                               argument24       =>NULL,

                               argument25       =>NULL,

                               argument26       =>NULL,

                               argument27       =>NULL,

                               argument28       =>NULL,

                               argument29       =>NULL,

                               argument30       =>NULL,

                               argument31       =>NULL,

                               argument32       =>NULL,

                               argument33       =>NULL,

                               argument34       =>NULL,

                               argument35       =>NULL,

                               argument36       =>NULL,

                               argument37       =>NULL,

                               argument38       =>NULL,

                               argument39       =>NULL,

                               argument40       =>NULL,

                               argument41       =>NULL,

                               argument42       =>NULL,

                               argument43       =>NULL,

                               argument44       =>NULL,

                               argument45       =>NULL,

                               argument46       =>NULL,

                               argument47       =>NULL,

                               argument48       =>NULL,

                               argument49       =>NULL,

                               argument50       =>NULL,

                               argument51       =>NULL,

                               argument52       =>NULL,

                               argument53       =>NULL,

                               argument54       =>NULL,

                               argument55       =>NULL,

                               argument56       =>NULL,

                               argument57       =>NULL,

                               argument58       =>NULL,

                               argument59       =>NULL,

                               argument60       =>NULL,

                               argument61       =>NULL,

                               argument62       =>NULL,

                               argument63       =>NULL,

                               argument64       =>NULL,

                               argument65       =>NULL,

                               argument66       =>NULL,

                               argument67       =>NULL,

                               argument68       =>NULL,

                               argument69       =>NULL,

                               argument70       =>NULL,

                               argument71       =>NULL,

                               argument72       =>NULL,

                               argument73       =>NULL,

                               argument74       =>NULL,

                               argument75       =>NULL,

                               argument76       =>NULL,

                               argument77       =>NULL,

                               argument78       =>NULL,

                               argument79       =>NULL,

                               argument80       =>NULL,

                               argument81       =>NULL,

                               argument82       =>NULL,

                               argument83       =>NULL,

                               argument84       =>NULL,

                               argument85       =>NULL,

                               argument86       =>NULL,

                               argument87       =>NULL,

                               argument88       =>NULL,

                               argument89       =>NULL,

                               argument90       =>NULL,

                               argument91       =>NULL,

                               argument92       =>NULL,

                               argument93       =>NULL,

                               argument94       =>NULL,

                               argument95       =>NULL,

                               argument96       =>NULL,

                               argument97       =>NULL,

                               argument98       =>NULL,

                               argument99       =>NULL,

                               argument100      =>NULL

                              );

      COMMIT;

   EXCEPTION

      WHENOTHERS

      THEN

         srw.MESSAGE (103,‘In When Others ‘ || SQLERRM);

   END;

 

SRW.USER_EXIT(‘FND SRWEXIT’);  return(TRUE);

end;

Do keep in mind that a patch can overwrite this report, i.e. ARXSGPO.rdf. If this happens then this change has to be put in place once again.