Customer Statements with BI Publisher

Introduction

Everybody who has worked with Oracle Receivables has had to deal with customer statements. Customer statements show customer related transactions such as invoices, credit memos, payments, etc. Customer statements are printed with a predefined cycle. There is a special form which allows you to submit generation of a customer statement.

The form submits a standard process ARXSGP written in Pro-C.  This process gathers information about customer receivables activities for the reporting period throughout the Oracle eBusiness Suite, inserts the information into temporary tables and submits the report ARXSGPO to actually produce the output. The result of this operation is an ASCII file.

Needless to say, you cannot present ASCII output to the customer. Now that laser printers print as fast as a small printing press, there is no excuse available to use old fashioned preprinted forms; customers want personalized graphics, personalized messages, and HiFi quality of the output.

Basic BI Publisher

Rewriting the customer statements report from scratch is not a good idea.  The standard process navigates through all of the customer related transactions and neatly places the results in the temporary tables.  The process is very fast and reliable.  You are better off staying with the standard customer statement process and enhancing it using available means.

Following recommendations from Oracle, we decided to use Oracle BI Publisher to produce a presentable output.

In order to use BI Publisher, we changed the output type for the ARXSGPO report to XML, associated a new data source with the report, and built a nice looking template with corporate colors, tables, footnotes, and disclaimers.  After the customer statement generation completed, we manually submitted an ‘XML Report Publisher’ program to publish the report with BI Publisher in a PDF format.

Support for Consolidated Invoices

During testing we found that the standard customer statements report prints out individual receivables transactions rather than consolidated invoices. It transpired that the customer invoice process is customized so that it prints a consolidated invoice as one receivables transaction without any reference to individual transactions which it consolidated. It means that transaction numbers on our customer statement meant nothing to the customer since the customer never saw them on any paperwork.

The next task at hand is to adjust the customer statement report so that it does not print individual transactions if they are consolidated.

To handle consolidated invoices we enhanced the ARXSGPO report to show the consolidated invoice number for each transaction if such a number exists. This gave us the necessary information in the output file.  Unfortunately, we had to summarize the receivables transactions by consolidated invoice to produce the consolidated balance.  Such operation was not seen possible or desirable to perform within BI Publisher template. The help came from another XML related technology. We introduced an XSL transformation to convert the XML document generated by ARXSGPO report into an XML document with more suitable structure. You use BI Publisher template of type XSL-XML to perform such conversion. Within the XSL transformation we consolidated invoices, excluded transactions with zero balance, etc. The transformed XML document was now very easy to format into the desired PDF format.

To automate the process we had to introduce a chain of command design pattern. This pattern is very much similar to request sets. We discussed this pattern in previous posts.

Volume Testing

We tested the above discussed solution to print one customer statement at a time. Developer picked up several customers to make sure most possible data combinations are explored. Once happy, we gave the solution to the users.

After several casual tests with individual customer statement, the users got comfortable with the process and decided to test the month closing procedure which included printing customer statements for all their customers. Surely enough, customer statement report produced an XML document so big that it could not be loaded into memory for processing.

It is widely known that XML document consumes significant amount of memory. We hoped that 16GB RAM machine will be able to cope with it. Tough luck!

The new task is to reduce the size of the XML document.

Another concern was the printer itself. We calculated that it would take 24 hours of continuous printing and five paper reloads to print customer statements for all customers at the end of the month. We could hardly find any volunteer to watch the printer around the clock and resolve hardware problems should they occur four o’clock in the morning.

There were several possible workarounds. We could submit customer statements by alphabetical letters. It meant 26 submissions each month. Prospect of introduction of other languages shelved this approach. Alternatively, we could put customers into different statement cycles. This sounded better, but this would not represent the way the business is conducted. In short, technical difficulties should not affect the way the business processes are represented in ERP. So, we looked for a purely technical solution.

We observed that the customer statement XML document is a sequence of independent statement messages. So, we wrote a process which grouped the statements into batches, each batch with a manageable number of statements. The process read the file with the XML document and sliced in into batches, 300 statements per batch. As the result of the process, we produced around 60 batches for testing.

It not only allowed generating customer statements for all customers in one go, it also allowed printing customer statements to several printers. Since our monthly customer statement printing process produced multiple files, we sent the files to an array of five to six printers leveling the workload.  It reduced the total printing time for customer statements to four hours without any human intervention, a.k.a. loading paper into emptied paper trays, replacing overheated cartridges etc.

Summary

BI Publisher can be used not only to produce HiFi output, but also as an ETL tool. Combination of these two can help resolve complex tasks of XML manipulation and output management.