Oracle EBS XML Publisher Templates- Ancestor-or-Self Tags

Recently I found myself up against my old friend. The Oracle E-Business Suite Output Post Processor (OPP). Years ago I tackled many issues with the performance and reliability of OPP. So much so that I took the time to combat it by learning as much as possible about it. Later I presented at Collaborate about it. Additionally, I wrote an article for OATUG Insight Magazine on the same topic.

Help! My Report is Slow

A developer reached out to me because an XML report was running slow. It was new and would run for 10+ hours without completing. This was in our test environment. The developer had two of these reports running. First, I checked what the sessions were doing.

Using v$session, I find both sessions. They are waiting on OPP to finish post-processing.

This tells me it’s already completed the execution of the SQL statement and created the XML file. Next, I check the size of the output file for the request. I see that it’s not very large, a few megabytes. Sometimes if the file is really large, OPP could have problems processing it. Then I remembered one optimization tip related to ancestor-or-self tags.

Ancestor-or-Self Tags

The ancestor-or-self tags are embedded in the XML Publisher template file. There is a known performance issue with having these in templates. By the way, template files have an extension of *.rtf. For example, mytemplate.rtf.

What I do to check for ancestor-or-self tags is execute a grep command. I do this on the template file on the Linux application server. This allows me to quickly see if a template has these tags. Here is what that would like:

$ grep -i ancestor mytemplate.rtf

,xdoxslt:ifelse((WIP_SUPPLY_TYPE=4),xdoxslt:ifelse((ancestor-or-self::*/P_INCLUDE_BULK=1),true(),(QTY_ISSUED!=0)),xdoxslt:ifelse((WIP_SUPPLY_TYPE=5),xdoxslt:ifelse((ancestor-or-self::*/P_INCLUDE_VENDOR=1),true(),(QTY_ISSUED!=0)),true()))))='true'?>}

<?if:not(ancestor-or-self::*/COUNT_MASTER_SUM = 0)?>End of Report<?end if?>}{\*\ffstattext <?if:(ancestor-or-self::*/COUNT_MASTER_SUM = 0)?>No Data Found<?end if?>}}}}}{\fldrslt {\rtlch\fcs1 \ab\af1\afs18 \ltrch\fcs0

Solution

The solution to this performance issue was simple. I provide the information on the tags to the developer. The developer went back into the template and removed all the ancestor-or-self tags. As a result, the report runs in about 5 seconds now. 🙂

Oracle Seeded Templates

This problem doesn’t only happen to custom templates. Oracle seeded templates are notorious for the same problem. For example, once we ran into an issue with the seeded Receiving Transactions Register (XML) Report. It would run for 12+ hours. Sure enough there was a known issue with this report. Oracle MOS document, Receiving Transactions Register (XML) Report Performance Issue (Doc ID 2070306.1). We apply the patch that Oracle provided and the report ran in 30 seconds.

In the event a seeded template is taking a long time to execute. First, confirm that it’s spending all of it’s time on OPP Post Processing (FND_CP_OPP_REQ.postprocess). If it is, then perform the grep command on the template file as demonstrated above. If you find ancestor tags, then you can do two things. First, search for the template name on My Oracle Support. Second if nothing is found with a search, open an SR with Oracle

Summary

In summary, both custom and Oracle seeded RTF templates can be afflicted by the ancestor-or-self tags. This post demonstrates how you can check for that. The fix is to simply remove them. Additionally, the following document can provide tips on further XML Publisher including RTF template optimizations.

• Troubleshooting Known XML Publisher and E-Business Suite (EBS) Integration Issues (Doc ID 1410160.1)