Oracle Apps AP Check Printing issues in R12

R12 Check Printing – Part I

For my last client, I was brought in to deal with some issues with the recent implementation that other developers were unable to resolve. The main issue was check printing. My initial thought was that I’d done this many times, in Oracle Reports, with Evergreen and Optio, and although it was now done in XML Publisher, it shouldn’t be too difficult. I did not realize at the time that Payables was now integrated with Payments (formerly iPayments in 11i), and AP, one of the oldest and stablest of modules, had significant issues.

I’ll be detailing the process in a series of posts, as it was quite eye-opening, to say the least. We are at last at a working, reasonably-performing solution, but it has taken some time to get there.

One of the more difficult challenges is that XML Publisher, while a great tool for certain purposes, really has issues with documents that require precise placement of text. With some tweaks, I was able to get everything within tolerances acceptable to the bank, but it was quite difficult.

The other major issue was overflow checks. To put it bluntly, this does not work in 12.0.3 and below without some patching, and our final solution required 12.0.6, with a patch for performance.

Getting a Valid XML Extract -Part 2

One of the most basic steps in creating an XML Publisher report and debugging it, is to look at the XML being transformed and formatted by XML Publisher. For the AP checks, this proved an extraordinarily difficult process, until our 12.0.6 upgrade.

There are some steps to follow in Note 457411.1 on metalink for getting an extract of the data. However, when running this, I got an error, a small piece of which is below:
Error: an error occurred during formatting. Please verify the template is valid.
oracle.apps.iby.exception.PSException: Internal Error to Payment Server: No corresponding LOB data found

This issue could be resolved with this command (entering each line with “-” at the beginning and “\” at the end):

java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD appspwd \
-JDBC_CONNECTION aaa-au.au.yyyy.com:XXXX:VIS \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME IBY \
-LOB_CODE IBY_IDENTITY \
-XDO_FILE_TYPE XSL-XML \
-LANGUAGE en \
-TERRITORY 00

Unfortunately, the CLASSPATH (as configured) did not include all the correct classes. After downloading jar files one by one, I finally found the correct CLASSPATH to add. (This may have been a configuration issue, the APPLMGR account should have the CLASSPATH).

Once the XDO Loader fix was done, we got this error:

No extract has been previously created. Create extract.
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "APPS.IBY_EXTRACTGEN_PVT", line 138
ORA-06512: at line 1

This proved to be a dead end. In the meantime, I had been able to get a semi-workable template as a temporary workaround, and began working on more pressing issues for a time. Oracle was never able to fix the above issue, and I ended up getting an extract from a 12.0.6 upgrade test instance.

This was quite frustrating, as a lot of the data I desired to add (CR Memo, DR Memo, etc.)

Overflow Checks-Part 3

A basic check is not a challenge in almost any tool. One of the real challenges we had was in handling “overflow” checks. These are payments of more invoices than will fit on a single remittance stub. The practice has always been to have the overflow checks be voided, and the final page be an actual negotiable check. This has worked easily in Oracle Apps from the earliest releases I worked with (10.3 character mode).

As mentioned in Part II, we could not get a good XML Data Extract, and the seeded sample was very simple, not including Credit Memos, Debit Memos, or most significantly, overflow checks. In addition, the system did not seem to be providing any overflow at all – checks came out with any number of invoices on them. This necessitated some very advanced XSL programming to force pagination. (I’ll discuss some of the XML Publisher development for the final output in a later post.)

After installing patches 7131163 and 7253633 (while on 12.0.3), we had most of the output issues resolved, but overflow checks still did not work. So, in the meantime, we were limiting the checks to 12 invoices.

Finally, we had a 12.0.6 upgrade test instance, and I went into it, successfully created an XML extract for a large check run, and could see what Debit Memo, Credit Memo, and most importantly, overflow data looked like in the XML. I tweaked my design, took out the forced pagination (as it was clear that the overflow checks would be on a separate check number now). I tried this in desktop preview, and it worked like a charm.

I uploaded this to our test instance, and no luck – the pagination was bizzare, and seemingly inexplicable – worse than before.

As I still could not get an XML extract from our 12.0.3 instance, I suspected the data was different somehow, but I was not sure what the difference was.

Finally, I did a detailed search on applysys.ad_bugs for IBY patches in the 12.0.6 and 12.0.3 instance, and checked the README notes. Nothing was really clear, but on a hunch, I downloaded patch 6765314 and read the source code. The description only said:

DATE AND PAYEE INFO NOT POPULATED ON OVERFLOW CHECKS

but when I read the source code, I noted they made a significant change. Although the overflow check numbers were recorded in the system, the report writing PLSQL package that created the XML file had apparently been recording them all as the check number of the final, negotiable check. Since the template sorted by check number, and did page breaks on the check number, all the related payments were on one document. This is why we were getting such strange results. By putting in patch 6765314, the overflow checks were numbered properly, the same as the preprinted stock.

We put this patch in, and overflow checks worked fine, and I was able to add CR/DR Memo data, and make a few other tweaks, and we finally had checks working as desired. One thing I did note is that negotiable checks now print first, with the overflow checks following, which is the opposite of the way it worked in 11i and prior releases. (With the March, 2009 Critical Patch Update, which includes patch 788766.1, this will change back to printing the overflow checks first, with the negotiable check at the end.)

This worked fine until we upgraded to 12.0.6, when checks would not print at all…but that’s a future post.

XML Publisher Issues-Part 3

This last section on AP Check Printing in R12 is about the XML Publisher issues. These issues were greatly complicated by:

• Not being able to get a good XML data file from the system as documented in Part II
• Overflow check printing not working as discussed in Part III
• General instability in Oracle Payments, especially prior to 12.0.4

Coming onto the project, the client had a very inadequate check template in place, and the prior developers had given up and rolled off as they could not make it work. Checks were misaligned, and overflow checks did not work, and were not numbered correctly. In hindsight, some of this was not the developers fault – the system was not handling overflow checks correctly.

After patching up in 12.0.3, the system would show overflow checks, but the template, maddeningly, would not handle them. As it turned out, this was because they were all numbered the same as the negotiable check in the XML output, as noted in Part III. However, being unable to see the XML output, I could not see that.

There were 3 significant development issues for a good check template that were not handled by Oracle’s seeded sample template:

1. The number of invoices would vary, depending on how many were being paid – this affects the alignment down
2. We specified a limited number of invoices per check – 12, as configured in the apps, and more required an overflow check
3. Getting the check portion of the output to print in the same place, regardless of what was above

My interim solution was to copy Darshan Bhavsar’s solution on Anil Passi’s blog. (Incidentally, this blog has moved to http://apps2fusion.com, and it is an excellent resource for Oracle Apps developers). Briefly, this solution creates a variable for the number of invoices, has an inner loop and countdown function (with a recursive subtemplate to fill the blank lines needed). Darshan came up with a great solution to the problem of padding with blank lines. In a regular computer language you could use a for loop to easily add blank lines, but XSL is not a procedural language, so this is a challenge.

Darshan’s solution also enabled us to handle pagination as well. Even though the overflow and negotiable checks were showing as the same check number in the XML (unknown to us at the time), we were forcing them to page break (the template sorts by check number) and getting something out that was acceptable to the bank.

The other issue was getting the check output to print at the bottom, and to have the overflow checks say “***VOID****” on the signature and other areas, with the negotiable check having the check amount, the amount in words, and a blank line for the signature. Word does not really seem to offer an option to align objects with the bottom of the page in a way that worked in an RTF-based template. I found an interesting solution at the Functional DBA Blog, which necessitated copying down all of the XML Publisher classes from the server, creating a JDeveloper program to generate the XSL-FO output, and modifying that. (For those who are not that technical, the RTF template is transformed into XSL – Formatted Output before the final output is generated). The XSL-FO output is incredibly verbose, and I did not find this a workable solution in our case, but it would probably be necessary had we done the MICR encoding, rather than using pre-printed checks, as MICR encoding must be done to very precise tolerances. For our check portion, this was just too much text to force into a footer, and maintaining the XSL-FO directly (it can be uploaded, rather than an RTF template in XML Publisher) would be too complex for the client to maintain.

Rather than trying to create a huge footer, my solution was to use another subtemplate with a “last-page-first” tag:
<?start@last-page-first:body?><?end body?>
This makes the check subtemplate print on the last page, even if there is only one page.

This worked great, until additional patching broke it.

Finally, we patched to a level where I could get the XML output (from a 12.0.6 test instance), and found a patch to make the overflow check output have the actual overflow check document number, and was able to remove the forced pagination, and the last-page-first solution, and use:
<?when:PaymentStatus/Code='VOID_BY_OVERFLOW'?>
for the overflow checks and
<?otherwise:?>
for the negotiable check. This is very much like the seeded template, and finally works smoothly and efficiently.

A little tweaking for alignment, and we were home free. One issue, still unresolved is that there is sometimes a blank line inserted at the top of the invoice list, but I was able to get the alignment within tolerances acceptable to the bank.

As a wrap-up, if you are doing AP checks for R12, I highly recommend being at 12.0.6, with patch 7388882 before beginning development. Had all this been in place, development would have been a lot faster, and avoided a lot of the issues above.