Installing XML Publisher Desktop Client (used in MS WORD)

To create templates we need to in Desktop Client provided by Oracle. This tool is used for building and testing of layout templates.
This installer consists of a plugin to MS Word for the building of RTF templates and the Template Viewer, for testing and debugging all templates.

Use the following URL to download the client :
http://www.oracle.com/technology/products/xml-publisher/xmlpclient.html

Once download completes, unzip the file and just run the Setup program, which will guide you through the steps in installation.

Once the installation is complete, run MS Word.

Converting RDF Reports to XM Publisher

The following command executes “rwconverter.exe” utility, taking the source report,
XXTEST.rdf and converting it to an RDF-XML format that can be consumed by the
XML Publisher conversion utility.

Note the dtype must be specified as "xmlfile".\BIN>rwconverter batch=yes source= c:\XXTEST.rdf
dest= c:\XXTEST.xml dtype=xmlfile overwrite=yes


This would create an RDF-XML file, with all the PL/SQL logic and presentation logic.

Step 2.1 Data Model Migration :
XML Publisher 5.6.3 provides a Java API Package "oracle.apps.xdo.rdfparser". This package has "DataTemplateGenerator" class that creates a DataTemplate from the XML report file. If report has triggers this will generate '.pks' and '.pkb' file for that trigger which needs to be compiled into the Database

Example
javaw.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator c:\XXTEST.xml

Output Files:
PL/SQL Package : XXTESTS.pls
PL/SQL Body : XXTESTB.pls
DataTemplate : XXTEST_template.xml

Here is a sample XML output file. Notice the "defaultPackage", "dataTrigger" etc that contain reference to PL/SQL API.


<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXTEST" defaultPackage="XXTEST" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_CONC_REQ_ID" dataType = "number"></parameter>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_Main">
<![CDATA[
SELECT user_name, user_id
FROM xxuser
]]>
</sqlStatement></dataQuery>
<dataTrigger name="beforeReportTrigger" source="XXTEST.beforereport"/>
<dataStructure>
<group name="G_1" dataType="varchar2" source="Q_Main">
<element name="user_name" dataType="varchar2" value="SEGMENT_VALUE"/>
<element name="user_id" dataType="number" value="VALUE_SET"/>
</group>
</group>
<element name="CP_segment_name" dataType="varchar2" value="XXTEST.CP_segment_name_p"/>
<element name="CF_RUN_DATE" dataType="USDate" value="XXTEST.cf_run_dateformula()"/>
<element name="CF_NAME" dataType="varchar2" value="XXTEST.cf_nameformula()"/>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XXTEST.afterreport()"/>
</dataTemplate>


Step 2.2 Data Model Migration :
XML Publisher 5.6.3 API Package "oracle.apps.xdo.rdfparser" has "RTFTemplateGenerator" class to migrate the Oracle Reports layout to an XML Publisher RTF template. Because there is no support for PL/SQL in an RTF Template, the generator writes all Oracle Report format trigger code to the log file. This must later be implemented as XSL code

Example
javaw.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator c:\XXTEST.xml

Output Files:
RTF Template : C:\XXTEST.rtf
Log File : C:\XXTEST.log

That’s it we are done with the conversion.
Now that we have converted the RDF into XML publisher report, we simply need to use the Data Template and RTF to register with the XML Publisher.

Step 3 : Register a new Data Definition with XML Publisher Responsibility, with XML file generated as part of Step 2.2 as Data Template. Register the RTF template for the DD.
Step 4 : Create a Concurrent Report Program with Report output as XML and executable as "XDODTEXE". XDODTEXE is XML Publisher Data Template Executable Engine, which is a Java Concurrent Program.

: Convert the output format of Standard report to XML, create a new RTF template and register a Data Definition and Template with XML Publisher.

Approach 2 : Convert the RDF report into XML Report and use this XML as a Data Template. Create a new XML Publisher report Data Definition with this XML as Data Template and create a new Template.

The problem with Approach 2 is that Oracle provides no tool to convert Reports 6i into XML Report and any other supporting APIs to handle associated PL/SQL logic embedded into RDF.

Enter Reports 9i (or higher) and XML Publisher 5.6.3. These two combinations of Oracle products provide tools to first convert RDF report into XML report and associated XML Publisher API to handle PL/SQL logic.

This is how it works.
-----------------------------
Oracle Reports contains both Data Model (logic) and Layout (presentation) in a single file. In XML Publisher they are stored separately. Therefore migration involves a 2 step process.

Step 1 RDF to XML format report : Oracle Reports 9i (or higher) comes with utility "rwconverter.exe". Use either Reports Designer or "rwconverter.exe" command line to convert RDF into XML format report.

From the Designer :

Multilevel XML To Multiple Table

Recently I was working on an implementation project and was faced with situation where client wanted some inbound data populate standard tables. Fair enough, but the problem was that the data provided was in XML format and they wanted a PL/SQL solution for it.
After some research I managed to find out lot of APIs provided by Oracle (and there are plenty of them). Oracle 9i has DBMS_XMLSave
while Oracle 10g has DBMS_XMLStore .
DBMS_XMLStore is the preferred package as it is written in C and linked into the Oracle kernel. It also uses the SAX parser. As a result DBMS_XMLStore has better performance characteristics.

Both of these APIs rest over XMLtype object. XMLType is a system-defined opaque type for handling XML data and has predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically.

Now, most samples that I found on internet handled inserting into a single table at a time (will show that later as well). However, what I needed was
1) Read XML file from a file known location.
2) Read the structure into a master-detail (parent-child) kind of relation.

Based on my learnings, here is what it looks like :


-- In -- Flat Multi Level XML
-- Out -- Two Tables

----------------------------------
TABLES
----------------------------------
CREATE TABLE emp_t (
EMP_ID NUMBER,
NAME VARCHAR2(10) )
/
CREATE TABLE emp_details (
EMP_ID NUMBER,
DESCRIPTION VARCHAR2(100),
B_DATE DATE)
/
----------------------------------
XML
----------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<EMP_ROWS>
<EMP>
<EMP_ID>1</EMP_ID>
<NAME>A</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION1</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION2</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION3</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTIONDESCRIPTION4</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
<EMP>
<EMP_ID>2</EMP_ID>
<NAME>B</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION5</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION6</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION7</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION8</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
</EMP_ROWS>


----------------------------------
Sample Code
----------------------------------

DECLARE

v_xml_clob CLOB;
-- Oracle directory object and filename
-- Read the XML file into BFILE

v_xml_file BFILE := BFILENAME('USR_TMP', 'emp_data_levels.xml');

-- offsets refer to start of files
v_dst_offset number := 1;
v_src_offset number := 1;

v_lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning number;

v_xml XMLtype;

v_rows NUMBER;

BEGIN
-- Open xml file on OS
dbms_lob.open(v_xml_file,dbms_lob.lob_readonly);

-- necessary for correct handling of LOB locator
dbms_lob.createtemporary(v_xml_clob, true, dbms_lob.session);

-- xml file to clob
dbms_lob.loadCLOBfromfile(
dest_lob => v_xml_clob
, src_bfile => v_xml_file
, amount => dbms_lob.getlength(v_xml_file)
, dest_offset => v_dst_offset
, src_offset => v_src_offset
, bfile_csid => dbms_lob.default_csid
, lang_context => v_lang_ctx
, warning => v_warning
);

-- close xml file
dbms_lob.close(v_xml_file);

-- clob to XMLtype
v_xml := XMLtype(v_xml_clob);

------------------------------------
-- XML Processing Begins
------------------------------------
INSERT
WHEN tab = 'emp_t'
THEN
INTO emp_t
VALUES (id, name)
WHEN tab = 'emp_details'
THEN
INTO emp_details
VALUES (id, b_date, description)
WITH T AS (select XMLtype(v_xml_clob) xml from dual)
SELECT 'emp_t' tab,
to_number(extractvalue (t2.column_value, 'EMP/EMP_ID')) id,
extractvalue (t2.column_value, 'EMP/NAME') name,
null b_date,
null description
FROM t t, TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP'))) t2
UNION ALL
SELECT 'emp_details',
to_number(extractvalue (t2.column_value, 'LINE/EMP_ID')) emp_id,
null,
extractvalue (t2.column_value, 'LINE/DESCRIPTION') description,
to_date(extractvalue (t2.column_value, 'LINE/B_DATE'),'DD-MON-YYYY') b_date
FROM t t,
TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP/LINE_ROWS/LINE'))) t2;

-- commit ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ( 'Exception' || SQLERRM );
END;


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

Code is mostly self explanatory. Read the physical file, create a temporary CLOB, load CLOB from file and then crete a new XMLType. Using XMLType and XPath notation, read the XML and insert into the required columns.
That's it !!!

not a valid responsibility for the current user. Please contact your System Administrator.

1) Logon with System Administrator responsibility
2) Navigate to Profile > System, click on User and enter the user name
3) Search for profile 'Applications Start Page'. Delete the value set at user level and save
4) Try then

SQL Query to find concurrent program responsibilities menus that are attached

SELECT RESPONSIBILITY_NAME RESP,FM.MENU_NAME,PROG.USER_CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_VL RESP,FND_REQUEST_GROUP_UNITS RGU, FND_CONCURRENT_PROGRAMS_VL PROG,
FND_MENUS FM
WHERE RESP.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID
AND FM.MENU_ID = RESP.MENU_ID
AND RGU.REQUEST_UNIT_ID = PROG.CONCURRENT_PROGRAM_ID
AND RGU.UNIT_APPLICATION_ID = PROG.APPLICATION_ID
AND PROG.USER_CONCURRENT_PROGRAM_NAME LIKE 'Concc Program Name'
ORDER BY RESP

Display Numbers in Figures useing AP Module AP_Convert_number Function

In AP Module there is a builtin Function (ap_amount_utilities_pkg.ap_convert_number )to convert number value into Figures.

Example :-

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL  -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL -- For Lower Case Letters

SQL Query to Display Numbers in Figures

Example 1:-

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'FFSP') AS Value_in_words  FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'Ffsp') AS Value_in_words  FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'ffsp') AS Value_in_words  FROM DUAL -- For Lower Case Letters

Example 2:-

select to_char(to_date(1122187,'J'),'JSP') Value_in_words from dual   -- For upper-case letters

select to_char(to_date(1122187,'J'),'Jsp') Value_in_words from dual  -- For mixed-case letters

select to_char(to_date(1122187,'J'),'jsp') Value_in_words from dual  -- For lower-case letters