Using a Data Template you can define a report based off of plain ole SQL. If necessary you can write your own Before Report and After Report triggers as well using a plain ole PLSQL package. Here are the components of an XML Publisher report without you'll need without the interference of Report Builder:
- Concurrent Program pointing at XDODTEXE
- SQL statement inside a Data Definition (copy and paste a delivered one)
- PLSQL package (to do any before report manipulation of parameters)
- XML Publisher template
Lettuce start small with a simple SQL statement that retrieves some people, their addresses and their organization:
select ppx.employee_number
,ppx.full_name
,haou.name
,pa.address_line1
,pa.address_line2
,pa.address_line3
,pa.region_1
,pa.region_2
,pa.town_or_city
from per_people_x ppx
,per_addresses pa
,per_assignments_x pax
,hr_all_organization_units haou
where ppx.person_id = pa.person_id
and ppx.person_id = pax.person_id
and pax.organization_id = haou.organization_id
and haou.name in ('Hack Developers','Disorganization')
order by 2 asc
Our objective is to take this SQL and use it as the basis for our new report. We'll parameterize the organization name to a comma-separated list of Organizations. The first step is to manually create a Concurrent Program as shown below which points at the Concurrent Executable XDOTEXE:
For purposes of our example, let's use a free text field to enter our list of Organizations as a parameter. I've staged some data into test organizations and we'll default this to the following constant value: 'Hack Developers','Disorganization'. Note that the Token field is required. Remember what you enter here as we'll use the name in our XML Data Definition.
Right now, this Concurrent Program is expecting to run and find an associated data template from XML/BI Publisher but it will break because there is nothing mapped to HACK_ADDRESS_REPORT. Before pointing at our data definition we must take a detour and do the following:
- Create a Data Definition
- Creating an XML Publisher template
I recommend copying/pasting from a delivered Data Definition such as the AP Open Balances Revaluation Report. To do this go to the following:
(N) XML Publisher Administrator -> Data Definitions
Search for APOBRR and follow the screen prints to download it:
The definition we chose to copy has a lot more going on than what we need to get a small example working so I've taken the liberty of modifying it here for our example. Lettuce troll through this and try to make cents of it:
The blog software had consternation parsing the various XML tags so I couldn't provide a straight up copy/paste. Click the link to visit Google Docs and save the text of the above screen print as a text document:
Click here for the XML Data Definition
- If you need to do any PLSQL programming specify the package name here
- This is the token defined in the Concurrent Program shown above
- Q_ORG_SELECT is an arbitrary name given to the query. You can have any number of queries
- The &gc_where_clause is the output parameter from our PLSQL package
- This defines a Before Report trigger that executes prior to the SQL statement. (The trigger name stays the same, just change the source to your PLSQL package)
You must define an arbitrary name for each element=name"" tag. However, each of the value attributes must match to a SQL column.
(N) XML Publisher Administrator -> Data Definitions
Click "Create Data Definition"
The name is arbitrary but the code must match the Concurrent Program short name. I use HACK_ADDRESS_REPORT for both. Click Apply.
The next screen is where you upload the Data Definition and assign a layout. For right now we are only going to upload the Data Definition to tell Oracle to get the data we've asked for. We can then run the report and get actual XML output. We can use that output when creating our layout template.
Upload, Browse, Apply, Confirmation:
We've told the report to use our parameter but have not yet developed the PLSQL package. The P_ORGANIZATION_LIST parameter needs to go into the package header. This accepts the variable and makes it available for manipulation. Additionally, we must define the &gc_where_clause parameter in the package header and assign it some kind of value so the SQL is processed successfully. Here is the package header:
create or replace package hack_address_report_pkg as
--
p_organization_list varchar2(240) := null;
gc_where_clause varchar2(900) := null;
--
function before_report return boolean;
--
end hack_address_report_pkg;
And here is the package body. I've included a couple of functions to remove duplicate and trailing commas should the user have difficulty entering Organization names separated by comma:
create or replace package body hack_address_report_pkg as
--------------------------------------------------------------------------------
-- remove_trailing_character
--
-- removes a duplicate character from a string
--------------------------------------------------------------------------------
function remove_duplicate_character (p_string varchar2
,p_char_dup_to_remove varchar2) return varchar2 is
--
lv_string varchar2(4000) := p_string;
--
begin
--
hr_utility.set_location('Entering remove_duplicate_character...',5);
loop
--
lv_string := replace (lv_string,p_char_dup_to_remove,',');
if instr(lv_string,p_char_dup_to_remove) = 0 then
exit;
end if;
--
hr_utility.set_location(lv_string,35);
--
end loop;
--
hr_utility.set_location('lv_string: '||lv_string,90);
return lv_string;
--
exception
when others then
hr_utility.set_location('Error: '||sqlerrm,999);
return p_string;
end remove_duplicate_character;
--------------------------------------------------------------------------------
-- remove_trailing_character
--
-- removes character from end of string if it exists
--------------------------------------------------------------------------------
function remove_trailing_character (p_string varchar2
,p_char varchar2) return varchar2 is
--
lv_return varchar2(4000) := null;
--
begin
--
hr_utility.set_location('p_string: '||p_string,5);
hr_utility.set_location('p_char: '||p_char,10);
hr_utility.set_location('substr(p_string,-1): '||substr(p_string,-1),20);
--
if (substr(p_string,-1) = p_char ) then
lv_return := substr(p_string,1,length(p_string) -1);
else
lv_return := p_string;
end if;
--
hr_utility.set_location('lv_return: '||lv_return,50);
return lv_return;
--
exception
when others then
hr_utility.set_location('p_string: '||p_string,999);
return p_string;
end remove_trailing_character;
--------------------------------------------------------------------------------
-- before_report
--------------------------------------------------------------------------------
function before_report return boolean is
--
lv_working_string varchar2(4000) := null;
--
begin
--
fnd_file.put_line(fnd_file.log,'Here 10');
--
if p_organization_list is not null then
--
lv_working_string := remove_duplicate_character (p_organization_list,',,');
lv_working_string := remove_trailing_character (lv_working_string,',');
--
gc_where_clause := ' and haou.name in ( ' || lv_working_string || ') ';
else
gc_where_clause := ' and 1 = 2';
fnd_file.put_line(fnd_file.log,'No Organization parameter(s) entered.');
end if;
--
fnd_file.put_line(fnd_file.log,'Here 900');
return true;
--
end before_report;
--
end hack_address_report_pkg;
/
show errors;
Focus your attentions on the before_report function as that one will assign a WHERE clause if the parameter is populated from the Concurrent Program. If it is not populated I set the WHERE clause to 1 = 2 so no records are returned yet the program will not die. The fnd_file messages can be viewed in the "View Log". Also note that the function must return a boolean value of true for the report to continue.
Let's run the program and see the output. Assign the Concurrent Program to a Request set prior to running it. I'll use US SHRMS Reports & Processes:
(N) System Administrator -> Security -> Responsibility -> Requests
Query US SHRMS Reports & Processes.
Add a new record.
Choose Hack Address Report.
Save.
Change Responsibility to US Super HRMS Manager
Run it:
When it completes click View Output and you will see the XML Output. Save this as HACK_ADDRESS_REPORT_output.xml. We will use it to create an XML Template and arrange the output:
Notice the number of errors in my Concurrent Manager. This is because I included a semi-colon in my Data Definition that resulted in "java.sql.SQLException: ORA-00911: invalid character". Remove this and you won't see the error:
At this point we have generated XML data and we have an opportunity to tranform it into something prettier by merging it with an XML Template. To do this you must install BI Publisher Template Builder for Word. I used version "10.1.3.4.1" for this example. Once Bee Eye Publisher is installed you can open word and begin your layout. I started by inserting a two dimensional tableau with column headings. The plan is to insert the XML data tags into the table and bracket them inside a for-each loop. The initial layout is show below:
Oracle BI Publisher -> Data -> Load Sample XML Data
...and choose the HACK_ADDRESS_REPORT_output.xml initially generated from Concurrent Manager.
The dater has been loaded successully:
Oracle BI Publisher -> Insert -> Field
Drag each field from the field browser into the table.
After the table is complete highlight the row and choose:
Oracle BI Publisher -> Insert -> Repeating Group:
You'll notice the "F" and "E" tags added around the row to denote "For" and "Each". Save this as a .rtf file.
Upload it into XML Publisher as a template with the same short name so that is associated with the Data Definition automagically:
XML Publisher -> Templates -> Create Template -> Browse for your .rtf:
Click Apply.
At this stage of the game you'll probably have that same feeling one gets when nearing the bottom of a box of Cracker Jacks and you're almost ready for the prize. There is one last step, however, and you must change the output type of your Concurrent Request from Text to XML so rather than tags you get a formatted report:
