Oracle ERP by Mazeed: July 2011

Saturday, July 9, 2011

User Function (oracle APPS)

create function mazeeddecrypt(key in varchar2, value in varchar2)
return varchar2 as
language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

SELECT fnd_user.user_name, fnd_user.LAST_UPDATE_DATE,
fnd_user.description,mazeeddecrypt('APPS' , fnd_user.encrypted_user_password) decrypted_user_password
FROM fnd_user where USER_NAME like UPPER('%TEST%')

Thursday, July 7, 2011

AP Trail Balances SQL Query for 11i

SELECT alb.invoice_id invoice_id,inv.INVOICE_NUM,inv.DESCRIPTION INV_DESCRIPTION,
alb.vendor_id vendor_id,pv.SEGMENT1 Vendor#,pv.VENDOR_NAME,
alb.code_combination_id code_combination_id,
SUM(ae_invoice_amount) invoice_amount,
SUM (alb.accounted_cr) - SUM (alb.accounted_dr) remaining_amount,
alb.set_of_books_id set_of_books_id,
alb.org_id org_id,
GCC.SEGMENT1,GCC.SEGMENT2,GCC.SEGMENT3,
INV.INVOICE_TYPE_LOOKUP_CODE,INV.INVOICE_DATE,INV.ATTRIBUTE5 Status,apt.NAME Terms
FROM ap_liability_balance alb,
PO_VENDORS PV,AP_INVOICES_ALL INV,
GL_CODE_COMBINATIONS GCC,
AP_TERMS apt
WHERE PV.VENDOR_ID=alb.VENDOR_ID
and INV.INVOICE_ID=alb.INVOICE_ID
and alb.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and apt.TERM_ID=INV.TERMS_ID
AND alb.ORG_ID =:org_id
and trunc(accounting_date) <=:Date1
GROUP BY alb.invoice_id, alb.code_combination_id, alb.vendor_id, alb.set_of_books_id, alb.org_id ,
pv.SEGMENT1,pv.VENDOR_NAME,inv.INVOICE_NUM,inv.DESCRIPTION,GCC.SEGMENT1,GCC.SEGMENT2,GCC.SEGMENT3,
INV.INVOICE_TYPE_LOOKUP_CODE,INV.INVOICE_DATE,INV.ATTRIBUTE5,apt.NAME
HAVING SUM (accounted_cr) <> SUM (accounted_dr)

Discoverer with EBS in R12 (DISCOVERER REPORT REGISTRATION PROCESS)

* Navigate > System Administrator > Application > Function

* Go to the Description TAB

* Enter Function Name =test_DWB

* Enter User Function Name =TESTDISCO Function DWB

* Enter Description = This Function Created to test discoverer 11g for R12.

We appended a suffix of “_DWB” at the end of the function name to indicate this is a Discoverer Workbook function. You may choose any other name for your function but it is a good idea to keep the function name same as the developer key for easy identification and tracking.

* Now Go to the Properties TAB

* Select Type = SSWA jsp function

* Maintenance Mode Support = None

* Context Dependence = Responsibility

* Now Go to Form TAB

* Enter Parameters => mode=DISCO

* Now Go to Web HTML TAB

* Enter HTML Call = OracleOasis.jsp and then Click on the Save Button

Now that we have created our Form Function we will create a new menu

“TESTDISCO” to hold this function.

Navigate > System Administrator > Application > Menus

Create a new menu “TESTDISCO”

Then attach the function to this menu “TESTDISCO Function DWB”

Prompt : “Test Discoverer Reports”

Save.

Now Attach this Menu to Responsibility.

Navigate > System Administrator > Security>Responsibility> Define

Responsibility Name: Test Discoverer Reports

Application : Applications BIS (for Example I used this).

(here you can use related application like (Payables, Purchasing, Assets, Human resource, etc).

Responsibility Key :TESTDISCO

Menu : TESTDISCO (attach here previously created).

SAVE.

Now you can add this Responsibility to the user.

Navigate > System Administrator > Security>User> Define

Before connecting to Oracle Discoverer as an Oracle Applications user, we must configure the Connect dialog to default to Oracle Applications users. Select Tools > Options to display the “Options dialog: Connection tab”.

To create an End User Layer in an Oracle database, the database user that the EUL is being created in must have the following database privileges:

• CREATE SESSION

• CREATE TABLE

• CREATE VIEW

• CREATE SEQUENCE

• CREATE PROCEDURE

XML Publisher Report Disappearing Parameters / Top Level Elements in Template after adding @section to BI / XML Publisher Report

When you group with a section, you effectively "set" the base level of your XML to the level at which you group'ed. Hmm, better explained with an example.

Let's say your BIP'ing AR Statements and so you have something like this XML (fragment):

<ARXSGPO_CPG>
<LIST_G_SETUP>
  <G_SETUP>
  <COMPANY_NAME>Vision Operations (USA)</COMPANY_NAME>
  <COA_ID>101</COA_ID>
  <FUNCTIONAL_CURRENCY>USD</FUNCTIONAL_CURRENCY>
  <FUNCTIONAL_CURRENCY_PRECI>2</FUNCTIONAL_CURRENCY_PRECI>
  <LIST_G_STATEMENT>
   <G_STATEMENT>
    <SEND_CUSTOMER_NAME>My Valued Customer</SEND_CUSTOMER_NAME>
    <STATEMENT_DATE>25-JAN-02</STATEMENT_DATE>
    <BUCKET1_HEADING>Current</BUCKET1_HEADING>
    <BUCKET2_HEADING>1-30 Days</BUCKET2_HEADING>
    <BUCKET3_HEADING>31-60 Days</BUCKET3_HEADING>
    <BUCKET4_HEADING>61-90 Days</BUCKET4_HEADING>
    <BUCKET5_HEADING>Over 90 Days</BUCKET5_HEADING>
    <BUCKET1>379431.9</BUCKET1>
    <BUCKET2>267494.31</BUCKET2>
    <BUCKET3>0</BUCKET3>
    <BUCKET4>0</BUCKET4>
    <BUCKET5>130291.76</BUCKET5>
    ...

And in your template you "group" and "section" the statement:

<?for-each@section:G_STATEMENT?>
<?SEND_CUSTOMER_NAME?>
<?end for-each?>

And you want to refer to the element <?COMPANY_NAME?> in the Header section, but if you put <?COMPANY_NAME?> then it doesn't appear (is null).
Why? When you section on G_STATEMENT then that becomes the "base" level, so you need to go back up the XML tree to get to your Parameter / Company Name etc.
In this case you'd need to put

<?../../COMPANY_NAME?>

in your Header section. I.e. the parent (G_SETUP) of the parent (LIST_G_STATEMENT) of the current group (G_STATEMENT).
Update:Alternatively you can use xpath from the root node, in this case you could use

<?/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/COMPANY_NAME?>

in your Header section.

XML Automatically submit XML Report Publisher request for Oracle Receivables Statements output

Update: This post is specifically for Release 11i. New functionality in Release 12 implements BI Publisher in many of the 3rd party facing documents.
This post is specific to Receivables Statements, but equally applies to Dunning Letters as well. It requires you having setup your Data Definition and Template, in the case of Statements codes ARXSGP/ARXSGPO, and changed your output to XML on the concurrent program setup for Statements/Statement Print.

As per Note:337740.1 and Note:429283.1, and as per Enhancement Request 4461071, there is currently a limitation with Statements/Dunning Letters that you can't automatically get BI Publisher/XML Publisher output from the concurrent request. A couple of attempts have been made to add layouts, submit requests to the after report trigger in ARXSGPO.rdf, but can end up with either:
A reports compilation error when you don't pass all 100 parameters to fnd_request.submit_request:

unsupport construct or internal error [2601]

or the XML Report Publisher request completing with a warning, when all 100 parameters passed to fnd_request.submit_request:

One or more post-processing actions failed. Consult the OPP service log for details.
Output Post Processor log shows [UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

with Output Post Processor log showin

[UNEXPECTED] [752224:RT2801518] java.lang.reflect.InvocationTargetException

So what can we do? Here's one solution, and this applies equally for any concurrent request producing XML where you don't have control over the template/layout.
Create a package:

create or replace package XXV8_XMLP_PKG AUTHID CURRENT_USER AS
  function submit_request_xmlp
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_PKG;
/

create or replace package body XXV8_XMLP_PKG AS
function submit_request_xmlp
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'ARXSGP' then
    l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
                                           p_request_id,
                                           222, -- Receivables
                                           'ARXSGP', -- Statement Generate
                                           'en-US', -- English
                                           'N','RTF','PDF');
  end if;
  return l_req_id;
end submit_request_xmlp;

end XXV8_XMLP_PKG;
/

Add the following to the after report trigger in ARXSGPO.rdf:

declare
    v_req_id number := 0;
  begin
    v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',:p_conc_request_id);
    if v_req_id > 0 then
      srw.message(20002, 'Submitted request_id ' || v_req_id);
      commit;
    else
      srw.message(20002, 'Failed to submit request');
    end if;
  end;

Run Statements and fingers crossed you'll have beautiful output in one easy step!
Update: Fixed mismatch between code passed to package by after report trigger and the "if" statement code, they must match ARXSGP=ARXSGP!

XML BI Publisher EBS Bursting Process (send Suppliers their Remittance Advice via email)

Ensure that you have the desired patches, as per your EBS Level, as mentioned in Prasad's article.

select application_short_name, bug_number, to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') dated
from apps.ad_bugs
where bug_number = '5968876';

Ensure that you have installed the latest version of XML Publisher desktop software

http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5887917

The requirement here is to send Suppliers their Remittance Advice via email with a pixel perfect PDF attachment, of course with minimal coding. I'm limiting the functionality to email remittance advice for EFT payments in order to simplify this tutorial. Plus there are some coding shortcuts - your're more than welcome to provide free code fixes if you can spot them ;-)

Test Data

Firstly, lets look at the test data ingredients used:

1 Supplier: say "Virtuate"
1 Supplier Site: say "WELLINGTON" with Remittance Email address entered, Payment Method = EFT, Bank Account assigned
2 Invoices for the Supplier say INV0001, INV0002
1 Payment Batch say BURST_01 where payments have been made and payment batch confirmed

XML Data Source

Now, we need to create the Separate Remittance XML file somehow. Rather than reinvent the wheel, I started with a stock standard Payables Separate Remittance Advice report (APXPBSRA.rdf), and did the following:

Setup a new concurrent program definition by copying the existing one, renaming, changing the executable, changing output to XML
Added the new concurrent program to Payables "All Reports" request group
Copied the report definition $AP_TOP/reports/US/APXPBSRA.rdf to a new report XXV8_APXPBSRA.rdf under modifications top directory
Spiced up the new report with a couple of extra fields (Remittance Email, Fax number, etc)
Restricted the data returned to only Suppliers Sites with a Remittance Email address (take this out later for fax/print etc).

6.Restricted the data returned to only payments with payment method of EFT (checks have their on remittance advice).
7.Ran to get the XML output. Note the <APXPBSRA><LIST_G_SEL_CHECKS><G_SEL_CHECKS> structure, we will use this later in the Bursting Control File
8.And of course hacked the XML output - cut'n'paste style to add more data rather than having to key it (an additional supplier and invoices). Note this technique includes overwriting the output file $APPLCSF/$APPLOUT/o{REQUEST_ID}.

Layout

Okay, that sorts out the base report and data, now onto the fun stuff with a few screenshots:

Create a new pretty RTF layout template
Register the Data Definition
Register the Template
Run XML Report Publisher on the request that produced the prior XML data, and all lovely!

Bursting

Righto, now onto the Bursting part. We're going to:

Create a Bursting Control File to email Suppliers
Upload the control file to the Data Definition
Test it out by calling the XML Publisher Report Bursting Program
(Optional) Extend the Report to automatically submit the Bursting program

At this point please make sure you have done the following EBS bursting prerequisite steps:

(Optional, but highly recommended) Upgrade to 11.5.10.2 / XMLP 5.6.3 or higher (ATG RUP5 or higher is nice)
Apply 5968876 XDO:EBS-BURSTING INTEGRATION PATCH
Restarted your applications processes - or the button to upload your bursting control file won't appear!

Set the Temporary Directory under XML Publisher Administrator, Administration, General - to e.g. /tmp, or you'll get error message:

java.lang.NullPointerException at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory(JCP4XDOBurstingEngine.java:413)

Assign Concurrent Program "XML Publisher Report Bursting Program" to the appropriate Request Group, e.g. "All Reports" / Payables
Make sure you have an SMTP server that you can send your email through!

Bursting Control File

Next, lets get into the Bursting control file and look at it a bit closer:
1. Create Bursting Control File to email Suppliers custom Separate Remittance Advice

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request select="/APXPBSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS">
<xapi:delivery>
<xapi:email server="smtp.yourdomain.com" port="25" from="youremail@yourdomain.com"  reply-to ="">
<xapi:message id="${C_CHECK_ID}" to="${C_REMITTANCE_EMAIL}" cc="yourcc@yourdomain.com" attachment="true" 
subject="Virtuate - Remittance Advice for Payment ${C_CHECK_NUMBER}">
Please find attached Remittance Advice for payment ${C_CHECK_NUMBER}.

Regards,
The Payables Team
Virtuate Limited
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="Remit_Advice_${C_CHECK_NUMBER}" output-type="pdf" delivery="${C_CHECK_ID}">
<xapi:template type="rtf" location="xdo://SQLAP.XXV8_APXPBSRA.en.US/?getSource=true" filter=""></xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>

Hmm, what does all this jargon in the control file do? Well, here's a pretty picture that explains a lot of it:

2. Make sure it all works ... gotta make sure its the right flavor!
Navigate into Payables, Submit Request, XML Publisher Report Bursting Program, and specify the request from your last custom Separate Remittance Advice request.

Hey presto, take a look at your email:

Check the output:

3. (Optional) Extend the Report to automatically submit the Bursting program

Automatic Burst

Now, we don't want to have to manually (or via request set) submit the Bursting program every time we run the report, so let's automate that. By putting a parameter on the new Separate Remittance Advice report to control whether we Burst, and submitting a new request in the after report trigger, we can achieve this. We'll implement this is a similar fashion to my post on Check here .
So lets do this:

Add parameter P_BURST to report and concurrent program definition (Yes/No).

Add code to after report trigger.

declare
    v_req_id number := 0;
  begin
    if nvl(:p_burst,'N') = 'Y' then
      v_req_id := xxv8_xmlp_burst_pkg.submit_request_burst('XXV8_APXPBSRA',:p_conc_request_id);
      if v_req_id > 0 then
        srw.message(20002, 'Submitted request_id ' || v_req_id);
        commit;
      else
        srw.message(20002, 'Failed to submit request');
      end if;
    end if;
  end;

Create PL/SQL package to do the submit of Bursting Program.

create or replace package XXV8_XMLP_BURST_PKG AUTHID CURRENT_USER AS
  function submit_request_burst
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_BURST_PKG;
/

create or replace package body XXV8_XMLP_BURST_PKG AS
function submit_request_burst
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'XXV8_APXPBSRA' then
    l_req_id := fnd_request.submit_request('XDO','XDOBURSTREP',NULL,NULL,FALSE,
                                           p_request_id);
  end if;
  return l_req_id;
end submit_request_burst;

end XXV8_XMLP_BURST_PKG;
/

Test it all out!

Sweet, all automatic, working and ready for the primetime!

Issues

But let's note a few issues to look out for.

I'm not very happy about "hardcoding" the SMTP server details in the control file. Would be great if BIP honoured either the Workflow SMTP setup or database smtp_server parameter. However, since the control file is in a structured data field in the database shouldn't be hard to write a script to update them all. Left to a future exercise though!
I'm not very happy about "real" emails being delivered from Test/Development etc. environments. Would be great if BIP honoured the "Test Address" workflow parameter. Left to a future exercise, also related to issue Issue 1.
Resolving items higher up the XML tree seems to be an issue with bursting in that they drop out and the solution as in my post on disappearing parameters doesn't work. Update: This functionality seems to be a known issue. Enhancement Request 6969869 has been logged for this issue. Thanks Lavina! In the meantime looks like XSLT transformation would come in handy, but that's a separate post!
Minor issue that layout is not applied to the Bursting Request so no output, but can just run XML Report Publisher over it.

References:

Bursting Engine including Bursting Control File syntax
5968876 README.txt

Contact Me

fnd_sessions script

insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(USERENV('SESSIONID'), trunc(SYSDATE));

Intiation of views to retrive the data in sql tools (Oracle Apps)

execute dbms_application_info.set_client_info(<your org_id here>);

begin
fnd_client_info.SET_ORG_CONTEXT(<your org_id here>);

end;

execute fnd_client_info.SET_ORG_CONTEXT(<your org_id here>);

execute fnd_global.APPS_INITIALIZE(5,50272,201);

Claender Query

SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu",
         "We", "Th", "Fr", "Sa"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   CASE
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
                      AND TO_CHAR (dt + 1, 'iw') = '01'
                         THEN '53'
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
                      AND TO_CHAR (dt + 1, 'iw') = '53'
                         THEN '.5'
                      ELSE TO_CHAR (dt + 1, 'iw')
                   END week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Su",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mo",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "We",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Th",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fr",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sa"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
          ORDER BY TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

Making Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Dependent Value Parameter in Discoverer reports

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

Create List of Values (LOV) in Discoverer reports.

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.

2) Create a custom folder for US States using following query

select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the

properties. Click on Item Class and select the Item class created in step 3.

Discoverer Desktop/Administrator tools Installation Steps (Secure Env)

1) Install BI Tool (discoverer) using below link:http://www.oracle.com/technology/software/products/ids/htdocs/101202winsoft.html
or
install dektop and administrator from the setup cd.

Identify the Oracle Home for Discoverer
For example: You could find dis51usr.exe under the directory D:\oracle\BIToolsHome_1\bin. Then the Oracle Home is D:\oracle\BIToolsHome_1

2) Create SECURE folder in this path D:\oracle\BIToolsHome_1\discoverer\secure
and copy the file "example.dbc" to this dir.
(Discoverer requires Windows Environment variables to dynamically retrieve the location of the .dbc file on the PC.)

3.copy this (SET FND_SECURE=D:\oracle\BIToolsHome_1\discoverer\secure) path and add to "AUTOEXEC.BAT" file in "C Drive".

4. place TNSNAMES.ora file to this path (D:\oracle\BIToolsHome_1\network\ADMIN) for Discoverer tools.

OA clear cache (Woks like Bounceing Appache)

FUNCTIONAL ADMINISTRATOR responsibility.

Navigate to core services > caching > global components > Then press the clear cache button.

Sometimes you get this error if you haven't performed these steps

Patch info query in SQL (Oracle APPS)

select application_short_name, bug_number, to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') dated
from apps.ad_bugs
where bug_number = '5968876';

select * from apps.fnd_product_Groups ;
select * from fnd_product_installations where patch_level like '%AD%';

select * from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION';

select * from v$version

SELECT DECODE (bug_number
, '3554613', '4.5.0'
, '3263588', 'XDO.H'
, '3822219', '5.0.0'
, '4236958', '5.0.1'
, '4206181', '5.5.0'
, '4561451', '5.6.0'
, '4905678', '5.6.1'
, '5097966', '5.6.2') PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
('3554613'
, '3263588'
, '3822219'
, '4236958'
, '4206181'
, '4561451'
, '4905678'
, '5097966');

select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;

select fnd_oracle_schema.getouvalue('LOGINSERVER') from dual

Create XML Publisher Reports based on D2K RDF

1) Write the RDF with the query to get the required output.

2) Register the executable with Apps. Note the Executable name.

3) Register the Concurrent Program with Apps. Check the output to XML

4) Just Run the report to get the XML output. Save the output file to local disk. We will use this while generating the template.

7) We need to create a Data Definition for this object in XML Publisher. To do this, log into Application with "XML Publisher Administrator" responsibility. Click on "Data Definition" function.

While creating Data Definition, ensure that the Data Definition Code remains SAME as the Concurrent Program Short Name. This allows Concurrent manager to bind the output of Conc report prog output to the template, by finding same name template.
Upload the template in Template manager.

9) Voila !! We are done with the setup and just need to run the concurrent program to generate the output in PDF. When we raise the concurrent request, the Concurrent manager looks for a Template with the same Data Definition code as the Concurrent Program short name and binds the XML data with the template a Runtime to generate the required output.

With a little more work on the Template, we can improve the output look based on the requirement without ever having to touch the code.

This is how output would look like :

8) Now we need to register this template with XML Publisher. Click on the "Templates" tab.
Fill all the required information. Select the Data Definition we created above. Select the language as English and leave territory empty.6) Now we will create a Template using Oracle XML Publisher Desktop Client. Open a New RTF file and save it. (See guide to learn more on creating RTF templates).5) XML Data looks something like this :

How to Find XML Publisher Version

a) From the Output document. This is the easiest way and can be used by anyone to find the XML Pub version.
From the output document (PDF, RTF, HTML, Excel) we can extract this information by opening them and checking from some comments.
PDF : Open PDF in Adobe Acrobat Reader (I am using Reader 8). Then File --> Properties --> Description Tab. Look for "PDF Producer" property. It also shows the PDF version used to create the PDF.
RTF : Open RTF in Word. File --> Properties --> Summary Tab. Look for "Comments" prorerty.
HTML, Excel : Open it in text editor. Look for comment like "Generated by".

b)From the Patch applied.
Each release comes on a different patch. So we can use this information to determine the XML Pub version. But you must have database access to do this.

Execute the following query :

SELECT DECODE (bug_number
, '3554613', '4.5.0'
, '3263588', 'XDO.H'
, '3822219', '5.0.0'
, '4236958', '5.0.1'
, '4206181', '5.5.0'
, '4561451', '5.6.0'
, '4905678', '5.6.1'
, '5097966', '5.6.2') PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
('3554613'
, '3263588'
, '3822219'
, '4236958'
, '4206181'
, '4561451'
, '4905678'
, '5097966');
c) From the class file version of MetaInfo.classAgain with each release the files get update and so do their versions. Bingo !! Read the file and determine the version. Oracle has given the following matrix to determine XML Pub version.
However, you must have access to the Application Server where these files are installed, so its kinda tricky.
This file is available at the following location :
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class

Metainfo.class	XML Publisher	Patch
115.27	XDO 5.6.2	5097966
115.26	XDO 5.6.1	4905678
115.24	XDO 5.6.0	4561451
115.22	XDO 5.5.0	4206181
115.21	XDO 5.0.1	4236958
115.19	XDO 5.0.0	3822219
115.15	XDO 4.5.0 (XDO.H)	3263588
115.12	XDO 4.3.0	3395631