XML Publisher Report with Data Template as Data Source and Known Issues

No need to use developer 6i/10g report tool or pl/sql stored procedure to create an XML report in this case.

Please refer another post to see XML Publisher Report with PLSQL as Data Source and Known Issues

Here we will create a Data Template and include our SQL query into it.
We will be using seeded executable (XDODTEXE) to meet our requirement.

Step-by-step instruction to create a XML report using Data Template.

1> Prepare your query, based on your business requirement.
2> Define a Data Template XML File. (Please refer the sample Data Template attached below)
   > It is very important to create the Data Template in correct format.
      Lets go through the different sections of it.
     >> <dataTemplate name>: Name of your Data Template, Ex: NOBANK_DD
     >> <parameters>: Add separate parameters for each input(conditions/bind variables) used in the query.
     >> <dataQuery>: Your above created query with proper bind variables.
     >> <dataStructure>: It is the XML output data structure.  
3> Create a Data Definition (Ex. short code: XXSAMXML) in Test application.
   Navigation: XML Publisher Administrator -> Home -> Data Definitions --> Create Data Definition
   > Attach the above created Data Template XML file to it as a Data Template.
3> Register the packaged procedure in Test application (Concurrent program short code : XXSAMXML).
   > Short code of Data Definition and Concurrent Program must be same.
   > Executable: XDODTEXE
   > Output Format: XML
   > Define parameter for each parameters in the Data Template.
     >> The concurrent program parameter token should match with the Data Template parameter name.
4> Add the concurrent program to the respective request group.
5> Run the report and save the output as sample.xml
6> Create an rtf template (sample.rtf) on MS Word based on sample.xml (using BI Publisher Desktop)
7> Define a Template in Test application.
   Navigation: XML Publisher Administrator -> Home -> Templates --> Create Template
   > Refer the same Data Definition created above (short code: XXSAMXML).
   > Attach the above created rtf template (Sample.rtf) to this Template.
   > Choose 'Default Output Type' as required.
8> Run the Cuncurrent program again to get the desired output.

Known Issues:

1> While defining Data Definition

Error:
The uploaded file is invalid. the file should be in xml-data-template format

Solution : Double check your data definition xml file, format must be correct.

2> Concurrent Program Error

Error:
-- Log file of concurrent request
======================================
XDO Data Engine Version No: 5.6.3
Resp: 20419
Org ID : null
Request ID: 2250535
All Parameters: p_due_date=
Data Template Code: XXSAMXML
Data Template Application Short Name: XXX
Debug Flag: N
{p_due_date=}
Calling XDO Data Engine...
java.lang.NullPointerException
    at oracle.apps.xdo.oa.util.DataTemplate.getDataTemplate(DataTemplate.java:379)
    at oracle.apps.xdo.oa.util.DataTemplate.<init>(DataTemplate.java:226)
    at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:283)
    at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

Solution :  DataTemplate code must be in Uppercase and same as Concurrent program Short Code.

Sample Data Template

Completing Oracle Report(developer 6i/10g) to Warning/Error

Very old one but still can be very useful for many.

1> Store the required result in a placeholder based on your business requirement.

2> In 'after report' trigger call fnd_concurrent.set_completion_status(<status>,<your message text>);

status can be NORMAL, WARNING or ERROR, it is not case sensitive.

Example:

1> palceholder :p_status_chk

2>
function afterreport return boolean is
v_status_flag boolean;
begin
   srw.user_exit ('FND SRWEXIT');
   if :p_status_chk = 'ERROR' then
      v_status_flag := fnd_concurrent.set_completion_status('ERROR','Please review log file for details.');
      commit;
      --srw.message(1002,'Setting Completion status to Error');
   elsif :p_status_chk = 'WARNING' then
      v_status_flag := fnd_concurrent.set_completion_status('WARNING','Please review log file for details.');
      commit;
      --srw.message(1003,'Setting Completion status to Warning');     
   end if;
return (v_status_flag);
end;

---

Order By Case

ORDER BY CASE:



Requirement:

There are four types of codes ‘CN’, ‘CNA’, ‘CX’ and ‘CNX’ which are there in a table. We want to process in following order CN, CNA, CX and then CNX. Order By Code will result in CN, CAN, CNX and then CX which is not required. Hence we will use ORDER BY CASE.



Data Set:

Solution:

SQL Fun : Connect By Level

Yesterday evening while watching Chris Gayle’s blistering knock against Mumbai Indians, my friend Sarkar came up with a requirement. He asked me to write a single SQL query which will return first day and last day of the month starting from today’s date till next 100 months.

Requirement: 

1-Apr-2013 30-Apr-2013

1-May-2013 31-May-2013

1-Jun-2013 30-Jun-2013

1-Jul-2013 31-Jul-2013

-------- ---------

-------- ---------

100th Record 100th Record

    Solution:



SELECT LEVEL,
TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, LEVEL - 1), 'MM'),
'DD-MON-RRRR'
),
TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, LEVEL - 1)), 'DD-MON-RRRR')
FROM DUAL
CONNECT BY LEVEL <= 100;



We can get Last Day of month using LAST_DAY (SYSDATE) and First Day using TRUNC (SYSDATE, 'MM'). To repeat 100 times we can use CONNECT BY LEVEL





Result:

He is happy now  J