BI Publisher: Totals got ya down

Alright, if you have ever converted a report6i to a data template in bi publisher using the api's or by hand you will know one thing, removal of the integration of summary columns and formula columns or vice versa in lower groups is a total "beach."

I said it, "beach." There is a sneaky workaround, not pleasant but doable. When your report gets converted you might see something like this:

Functional flow in reports6i:
[First Group (CF_TOTAL_AMOUNT combines summaries columns)]-->[First Group has summary columns (:cs_detail_amount, :cs_detail_state, :cs_detail_federal)]

SQL Example:
select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from ap_invoices
where blah, blah

The basic probably we have here is that cs fields are in the same group, but when the query runs the summed amounts haven't taken place, nor will they, a classic paradox. You will get nothing for the cf_total_amount. Quite the conundrum....or is it.

I guess, it;s time to pull this calculated formula out and give up. The business your working for will totally understand that there are limitations to technology. Alright, back to reality, let's make this puppy work.

Solution remove the functions and create something like this for a sqlQuery and new group :

select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from dual

Organize your data template to do the summing below all of the other current group(s). Follow up with the new group with the calculated formula's select from dual with the summary columns. Here's the logic breakdown:

Order of execution:
1. [First Group (get all the fields that will be summed)]
2. [Summary Elements (summary columns here)]
3. [Second Group (select calculated formula(s) from dual with summary column values)]

Why:
Fields that are going to be summed must be higher than the summary columns. It's simple, we select the calculated formula's from dual with the summary bind variables.

This approach should work for those of you that are aspiring new bip developers that are converting reports6i to data templates. You will find that people believe or not will have functions calling summary columns in the same group. This example should help with other "minor" limitations with bip with summary columns and calculated formulas.

BI Publisher: BIP in the OA Framework part I

Have you ever wondered how the PO report pops up without a submitting a concurrent request? Pretty sweet huh! Well your going to know how do it after you reading this blog.

When this type of functionality came out in 11.5.10 myself and others wondered how is this possible, how can you generate a document and display it in "real-time." After digging into in 2005, I figured out that it was using the OA Framework. The problem for me at the time though was that I didn't have a clue about the OA Framework. I was literally baffeled and frustrated (see screaming baby above). I kept searching for the poViewDocumentPG and for the life of me I couldn't freakin' find it.

Well it's a two years later and I finally spent some time into figuring it out. A couple of key factors was reading the documentation, plus, I've started doing some OA Framework development. When we look at bare-bones how a pdf can be displayed on a screen in using the OA Framework it's really straightforward. Throughout this document I'm going to refer to this type of development as the "Real-Time" model.

How the Oracle PO Works

Step 1. find the form function PO_VIEW_DOCUMENT,

HTML CALL: OA.jsp?page=/oracle/apps/po/communicate/webui/poViewDocumentPG

Step 2. Create an OA framework page, very simple. This is ridicuosly easily. You need to have a controller and application module. Note, the application module doesn't have to have anything it but it must exist.

FYI: For our example, the PO_VIEW_DOCUMENT function has a page called poViewDocumentPG, however when we go out to the code tree (/oracle/apps/po/communicate/webui/) it doesn't exist. This because the xml files are loaded in the database. We can use this utility to download it:

begin

JDR_UTILS.printDocument('/oracle/apps/po/communicate/webui/poViewDocumentPG');

end;

We can see it's a bare bones page with one region and zero fields.

Step 3: write the logic in the controller class to get the document and return it. Note: this is a weird way to create an OAF page, usually you call the Application Module to generate the data and set the where clauses for the view objects in the AM, which bind to the OAF page. However the controller in this example generates the data/document and use HttpServletResponse to create the page, the application module doesn't really do anything. The application module could if you wanted it to, the blanket sales agreement is an example where the AM generates the xml.

sample code snippet:

public class poViewDocumentCO extends oracle.apps.fnd.framework.webui.OAControllerImpl
{
public void processRequest(oracle.apps.fnd.framework.webui.OAPageContext oapagecontext, oracle.apps.fnd.framework.webui.beans.OAWebBean oawebbean)

int j = java.lang.Integer.parseInt(oapagecontext.getParameter("DocumentId"));
....
....
....
....
httpservletresponse.setContentType("application/pdf");
httpservletresponse.setContentLength(abyte0.length);
httpservletresponse.setHeader("Content-Disposition", "attachment;Filename = " + pogeneratedocument.getfileName());
httpservletresponse.getOutputStream().write(abyte0, 0, abyte0.length);
httpservletresponse.getOutputStream().flush();
httpservletresponse.getOutputStream().close();
return;

Step 4: Call the function from your form to create basically a pop up and generate the document. Exluded in step 3's code above is the snippet to grab all the parameters that are appended together below from the function call in the form. I left just one so you would get the point.

fnd_function.execute( function_name => 'PO_VIEW_DOCUMENT',
other_params=>'DocumentId='l_document_id'
&RevisionNum=':HEADERS_FOLDER.REVISION_NUM'
&LanguageCode='userenv('LANG''
&DocumentType='l_document_type'
&DocumentSubtype='l_document_subtype'
&OrgId=' :po_startup_values.org_id'
&AuthorizationStatus=':headers_folder.authorization_status'
&UserSecurity='userSecurity '
&StoreFlag=N'
&ViewOrCommunicate=View');

DONE DEAL

Yes that's it, there really is no OA Framework code to write for the application module or controller. Note, as a best practice if you decide to do this development, your controller class should call another class to generate the final output, the purpose of the controller in this case is purely to push the document upto the browser. This is a better practice because it allows the developer to create new and additional constructors for one class that generates the document, versus copying and pasting the logic in each implementation (OAF, JavaConcurrentProgram, etc).

Things to Keep in Mind

If your still doing report development using reports6i you really need to stop that. While reports6i is a great tool, it has one big downside (besides being dated and replaced with BIP) and that is in-order to generate xml file, you have to submit a concurrent request (there are workarounds for this, none that are pleasant). This doesn't work in the "real-time" model.

In the above example the PO will actually generate the xml using pl/sql package. While this was acceptable in the past, you really shouldn't be doing this either.

Data templates should be used for generating the xml. Besides ease of use and it being less intense than using pure pl/sql, in this model it works because it can be run in "real-time."

There are a lot of possibilities with form personalization's to plug reports in line with standard oracle forms. Simply create a menu and call a built-in function which submits a function call to the OA Framework page. This allows the developer to have even more flexibility in customizing the application and reporting, while saving users time for keying and submitting concurrent requests.

A caveat here is don't do the real-time model with slow reports. Slow reports should be concurrent requests so they can be managed and maintained more effectively. From a high-level it doesn't make any sense at all to have a slow running report in real-time, it's a distraction for end-users and it may make it harder for them to manage there report(s), hence concurrent manager.

Part II: The next article will have a zip of an OA Framework project that will push a pdf up on the screen. Booyah!

Part III: Understand BIP Integration points