BI Publisher: How to format a specific column conditionally.

When I was going through a tech forum, this question got my attention.  

"How to format(bold, color,underline etc) specific column conditionally.

Say you have XML data, based on certain flag value, data should be displayed in a special format.

See the actual question here.

How to Underline a column with a specific width

Here is the answer..

I created this test scenario at my end and it works for me.

Consider following data as my XML data file..

=====XML data begins. =====

1

abc

12,000.00

Y

2

def

13,000.00

N

3

ghi

14,000.00

N

=====XML data ends. =====

And the way I worked is as follows..

Following statement is the key in this solution

--

--

1. Create two IF conditions in your template.

- one Y 

- another one for N

2. Inside your IF under_line= Y region, use this font underline statement

3. Inside your IF under_line= N region, use simple amount display field. i.e

For your other requirements..

a. Underline must be of 13 characters length.. do PADding with spaces in your data source itself. So when it come to XML file, your amount column will comes with full 13 characters. 

ex: I did LPAD to my data. LPAD(amount,13,' ' )

b. For amount formats(ex: $12,345.60), try this in XML publisher template, else workout this also as a part of your XML data source(i.e Oracle Reports, or any..).    

Complete code can be downloaded here..  

In the same way you can do lot of other font formats like

etc..

Happy surfing.

Complex Service PO

Question: How to customize Complex Service PO in R12.

Answer:
Let's try to discuss about this report in our random walk through.

Below is the query to listdown all the "complex service POs' in the system. Of course you need to set org profile for this query by "begin mo_global.set_policy_context('S',) ; end ; "
==
select * from po_headers ph, po_doc_style_lines_tl psl
where psl.style_id = ph.style_id
and psl.document_subtype(+) = ph.type_lookup_code
and psl.language(+) = userenv('LANG')
and psl.display_name = 'Complex Service Purchase Order'
==

For many reasons Standard template(provided by Oracle) "PO_STANDARD_XSLFO" alone is not sufficient for your requirement.
Example:
- company logo need to be added.
- layout need to be changed
- hide/show additional columns/information
- show registration number of your company. etc

For minor customizations, standard template can be modified and used. But remember standard template is XSL. So it requires some specialized skill to work on XSL.

I had huge amount customizations and all these customizations can not done using standard template. So I had to design and use my own template.
You can visit my template here.

How to design custom template ?

If you decided to proceed with your own template, then the total process(design, define, attach template) is no different with any other BI Publsiher reports.

To design RTF(or any other) template, you need to have XML file.
Getting XML file/data for this customization is not an easy task.
Oracle posted few notes on this report, but none of them discussed how to get XML datafile.

"PO Output for Communication" - is a Java concurrent program.
When you create complex purchase order, PO workflow will trigger "PO Output for Communication" program. This program generates XML file and apply this XML file to template and generate required PDF as output. 

In the process described above, system will not save XML file anywhere.

To get XML data file, you need to follow the steps mentioned below.
- Goto PO Responsibility.
- Goto SRS(Conc. program submission) Window
- Enter program "PO Output for Communication"  
- Parameters
- PO Number ->
- Test ->  Debug
- Template Name -> Standard Purchase Order Stylesheet.

- Submit.
After a while, this program will be completed in error. Don't worry about why it errored out. Just go through the logfile and find XML file in there.
Along with the XML data, there will be lot of other information, which you don't need.

Save the log file in your machine and remove complete text which is outside XML file. That means the text outside of XML tags and . Save this edited file as new file with .XML as extension.
Do following changes to your XML file now.
 - Add "xml version="1.0" encoding="UTF-8" ?>" as a first line.
- remove complete text under the tag , as this is not correctly formatted.

Now your XML data file is ready and you can proceed for creating custom template.

Once done with the template design,  here are the steps to register and use custom template.
 Log in as "XML Publisher Administrator"
 - create XML Template for the data definition: Standard Purchase Order Data Source.
- Note: Select Application as “Purchasing’.

- Nav: Purchase Order Super User
- Setup/Purchasing/Document types/
- Locate “Purchase Order Standard” and then Set the Document Type Layout to your new custom template.

Now you are ready test your customizations.
- Create complex service Purchase orde r.
- View PDF. You should be able to see your customizations now.






How to change/modify Data Definition ?

So far so good..
We have seen how to customize RTF template to produce desired output as per your requirements.

How about having additional information, which is not even available in your XML data file.?
Or in other words, how do you change/customize data definition itself, so that your new XML file come up with additional information you want.

Well, there is no direct way to change data definition as concurrent program is of JAVA type.
So you cannot customize this JAVA program(at least, I couldn't ).

In my case, I had to show "Project Code" for each PO distribution, if the procurement is project related.
Another requirement was - Legal Entity Name

Now - my requirements are clear ..show
- Project Code
- Regd Number and Regd. Legal Entity Name on the header portion of the report.
 Since I could not alter the data definition( PO Output for Communication), I did following.

Some how I figured out following views are being used by "PO Output for Communication"  to produce XML data file
- PO_HEADERS_XML
- PO_LINES_XML
- PO_LINE_LOCATIONS_XML.
- PO_DISTRIBUTION_XML .. etc

Altered "PO_DISTRIBUTION_XML" view to include project information.
eg:
--
CREATE OR REPLACE FORCE VIEW APPS.PO_DISTRIBUTION_XML
(
   AMOUNT_ORDERED,


   PA.SEGMENT1 PROJECT_CODE
     FROM   PER_ALL_PEOPLE_F PAP,
            PO_DISTRIBUTIONS_ALL PD,
   PA_PROJECTS_ALL PA,

    WHERE   GCD.CODE_COMBINATION_ID(+) = PD.CODE_COMBINATION_ID

            AND PAP.PERSON_ID(+) = PD.DELIVER_TO_PERSON_ID

   AND PD.PROJECT_ID = PA.PROJECT_ID(+)

            AND SYSDATE BETWEEN PAP.EFFECTIVE_START_DATE(+)

                            AND  PAP.EFFECTIVE_END_DATE(+);

---

Now my new XML datafile comes with Project code, which can be displayed in my output/pdf.

PS: To see  your changes to the view, it is better to create new PO and generate XML for that PO.

This is because, system caches XML data and reuses that.. thats the reason it is better to create brand new POs and test it.

Following few screenshots are to view your PDF  for PO from SRS window.
This way, you don't need to regenerate your PO or revise your PO.
Without increasing revision numbers, you can see all your PO updates in your PDF.
 
 
 
 
 
 
 
 

PS: I am updating this post, as and when I get some free time.
So information could be assorted and zig-zag.

Return statement in PL/SQL Procedure.

Question: Can we use RETURN Statement in PL/SQL Procedure.?
Ans: Yes, we can !! ;)

Honestly, as far as I remember, I've never used return statements in my PL/SQL Procedures.
How about you ??

Of coarse, in PL/SQL functions RETURN statement is a mandatory thing. But what about procedures ??

Lets see how and where we can use of return statement in a procedure.
- You have procedure, that contains few lines of code.
- After validating certain condition, you don't need to proceed further. You want control to be returned to calling procedure/main program.

** Couple of approaches here **

One way of doing it is, handling USER defined EXCEPTIONs.
- you can check how to use  USER defined Exceptions. I am not covering it here. ;)
I used this approach many times until now. Big advantage of this way is, you can trap the ERROR code in your calling program.

Another way is having RETURN statement in your sub-routine/child program.
For example:
== sub-routine ==
procedure CHILD_PROC
-- declarations
begin
-- pl/sql statements
if CONDITION = TRUE then
RETURN ;
else
-- do something
end if ;
end CHILD_PROC ;

=== calling program ==
procedure MAIN_PROC
-- declarations
begin
-- pl/sql statements
-- call to child proc
CHILD_PROC();
-- few more statements
end MAIN_PROC ;

Here is some sample code.

-- child procedure.

create or replace procedure child_proc(p_boolean BOOLEAN) as
begin
dbms_output.put_line('in child_proc before return call');
if p_boolean then
return ;
end if ;
dbms_output.put_line('in child_proc after return');
end;
/

-- main procedure

create or replace procedure main_proc(p_boolean boolean) as
begin
dbms_output.put_line('before child_proc call');
child_proc(p_boolean) ;
dbms_output.put_line('after child_proc call');
end;
/

-- execution and  result check --

[1]

begin main_proc(TRUE); end ;

--output --

before child_proc call

in child_proc before return call

after child_proc call

Statement processed.

0.00 seconds

[2]

begin main_proc(FALSE); end ;

--output --

before child_proc call

in child_proc before return call

in child_proc after return

after child_proc call

Statement processed.

0.00 seconds

HAPPY NEW YEAR - 2010

Hi Friends.. HAPPY NEW YEAR - 2010 .!!