Oracle EBS + Query to Find List of XML Publisher Reports

 SELECT fe.executable_name,'Data Template (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'K'

AND fe.executable_name = 'XDODTEXE'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'PLSQL (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'I'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'RDF (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'P'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%');

Regards,

Amar Alam

Oracle XML Publisher + How to display the numbers start with zero in Excel?

If our value is 01234 but in excel it shows 1234 because it converts it into number.

I have item number 006454.123. it is a varchar2 column. when I load it in xml and in output excel it is showing 6454.123

so to display as it is i used below code in form field.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">  <?ITEM_NUMBER?></fo:bidi-override>

Your's

Amar ALam

Oracle apps + How to display Sequence number in xml publisher?

Place the below code in a form field and place that form field after loop.

<?position()?>

It will display the numbers start from one.

Regards,

Amar

Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding in Oracle Reports

Usually we are facing the issue, while generating XML Tags, the issue is Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding.  This is Caused because of the version of Oracle Reports or Language we need to set in NLS_LANG.

For a Time Being we can go for a below step, we need to change the XML Prolog Value in Oracle Reports Builder.

 XML Prolog Value: xml version="1.0" encoding="iso-8859-1"

This will help in the short term but in my experience it will come back and bite you. you might get lucky but remember what you have done if you hit another problem with your XML in the future.

You are now treating a single report - Oracle support can help you address all of them at once :).

Thanks
Amar Alam.

How To Sort Date Columns in Oracle XML Publisher

Some Times We are Unable To Sort Date Columns in Oracle XML Publisher, To Overcome This Issue Please follow below link.

http://oraclemaniac.com/2012/02/04/sort-date-field-in-xml-publisher-template/

Thanks
Amar Alam

Formatting Date in Oracle XML(BI) Publisher

We can format dates in BI Publishes in the following three different ways,

1. Formatting Date using Microsoft Word’s native date format mask.
2. Formatting Date using Oracle’s format-date function.
3. Formatting Date using Oracle’s abstract date format masks.
4. Format with Calendar.

Lets me now explain little details about the above three ways, and below is the XML I am going to use in the below examples,

1. Formatting Date using Microsoft Word’s native date format mask?
In the BI Publisher Properties window, select type as Date and select the format from the drop down box.

Here is the output sample of all the three types of date’s we had in the XML. Format I selected is
DD-MM-YY.

2. Formatting Date using Oracle’s format-date function.
format-date() is an inbuilt function in BI Publisher.
Syntax:

In the above syntax MASK is an optional parameter. We will see more details about MASK later in this post.

To use this format-date function, under the BI Publisher Properties window -> keep type as Regular Text -> click on Advanced tab –> call format-date function with the tag name. (For time being we will not pass the MASK parameter.)

Here is the output sample of all the three types of date’s we had in the XML.

3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to MASK’

?> function. - See more at: http://flexfields.blogspot.ae/2011/01/bi-publisher-formatting-date.html#sthash.LrUuM9zp.dpuf3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to

Function.

Out of box we have the following MASK available for various format of dates,
Mask Constant                                               Output
SHORT                                                          1/26/11
MEDIUM                                                      Jan 26, 2011
LONG                                                           Wednesday, January 1, 2011
SHORT_TIME                                              1/26/11 8.10PM
MEDIUM_TIME                                          Jan 26, 2011 8.10PM
LONG_TIME                                                Wednesday, January 1, 2011 8.10PM
SHORT_TIME_TZ                                       1/26/11 8.10PM GMT
MEDIUM_TIME_TZ                                   Jan 26, 2011 8.10PM GMT
LONG_TIME_TZ                                        Wednesday, January 1, 2011 8.10PM GMT


The default MASK constant is MEDIUM.

In order to user these abstract’s you need to have your XML date data in canonical format as,
YYYY-MM-DDThh:mm:ss(+/-)HH:MM
(For example check out DATE_UTC tag value in the above XML.)

Here,
YYYY is the year
MM is the month
DD is the date
T is the separator between the date and time component
hh is the hour in 24-hour format
mm is the minutes
ss is the seconds
(+/-)HH:MM is the time zone offset from Universal Time (UTC) or Greenwich Mean Time

Here is the output for our XML data using the MEDIUM_TIME mask,

Now if you notice the output that we got earlier while using format-date with the default parameter, column for date_utc alone is showing Jan 27 were as the date we have in the XML is 26.

This is because of the time zone offset –08:00.

Where this timezone offset is nothing but, the time zone difference of the date that we passed in the XML with respect to GMT. So BI Publisher will automatically understand the timezone of the XML data, and print/convert it as per the BI Publisher Local Server time.

How to get Time zone offset of the data?
Ok now we know its important to pass the time zone offset in the XML data to use the MASK. Lets see how to get them in the XML data,

First Option:
If your data source is a data definition file, then no need to worry, the standard BI Publisher data definition engine by default will give the data in the canonical format with time zone offset. So you are good. No need of any extra coding.

If you do not want the template to change the date as per the time zone then, user the TO_CHAR function to print without the time zone offset. So that template engine will not modify the date as per the server time zone.

Second Option:
Lets say your data source is an RDF file. In that case use TO_CHAR function in your SQL query and bring the data in required canonical format.

Third Option:
Lets say, you do not have control over the data source. You have only access to the template and know what will be the time zone of the data that is going in come in the XML.

4. Format with Calendar:
Now that we had seen different types of formatting dates, let finally see how to use the above formats with various calendars.

Here is the supported calendar types,
- GREGORIAN
- ARABIC_HIJRAH
- ENGLISH_HIJRAH
- JAPANESE_IMPERIAL
- THAI_BUDDHA
- ROC_OFFICIAL (Taiwan)


I think, I had covered all the date formatting techniques in BI Publisher. If you feel I had missed anything or would like to explain some specific example, feel free to put on the comments.



Ur's
AmarAlam

Hide a Field in Oracle XML Publisher

1). If You Want to Hide a Field:

2). If you want to Hide The Data Based on One Field :

3). To Hide a Column We Can Use IF And END IF.


Ur's
AmarAlam

Deleting a Template From Back End

DECLARE  
   l_templateCode    varchar2 (100) := 'XX_AR_CUST_AUDIT_REPORT'; -- Template Code  
BEGIN
   FOR r IN (SELECT t1.application_short_name template_app_name,
                    t1.data_source_code,
                    t1.application_short_name def_app_name
               FROM xdo_templates_b t1
              WHERE t1.template_code = l_templateCode)
   LOOP
    
      xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

      DELETE FROM xdo_lobs
            WHERE lob_code = l_templateCode
                  AND application_short_name = r.template_app_name
                  AND lob_type IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM xdo_config_values
            WHERE application_short_name = r.template_app_name
                  AND template_code = l_templateCode
                  AND data_source_code = r.data_source_code
                  AND config_level = 50;

  END LOOP;
END;


Ur's
AmarAlam

Generating XML Tags Using SQL Query

We have a standard oracle package to generate the XML Tags.
We just need to pass the SQL statement as parameter to the package.

Below is an example, which prints Todays date as XML Tag

SELECT   DBMS_XMLGEN.getxml ('select sysdate today_date from dual') xml
  FROM   DUAL;

*****************************************************************
There is another way to get the same output using some other functions.

Let me start off with a simple query to print TODAY_DATE tag only

SELECT   XMLELEMENT ("TODAY_DATE", SYSDATE) FROM DUAL;

Below is the output for the above statement

2011-11-21

Now, Let me change the date format as DD-MON-YY

SELECT   XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
FROM DUAL;

Below is the output for the above statement

21-NOV-11

Now, i will modify the query to get same output as we got using standard package

SELECT   ''
         || XMLELEMENT (
               "ROWSET",
               XMLELEMENT (
                  "ROW",
                  XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
               )
            )
  FROM   DUAL;

Below is the output for the above statement, which is same as what we got using standard package




21-NOV-11

*****************************************************************************


Ur's
AmarAlam

data template in xml publisher

The Data Template Definition:

The data template is an XML document that consists of four basic sections:
1. Define Parameters
2. Define triggers
3. Define Data Query
4. Define Data Structure
This structure is shown in the following graphic:

To build XML report the following steps are involved.
Step 1: Designthe Data Template XML File.
Step 2:Create the Data Definition in XML Publisher and associate it with Data Template
Step 3:Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report.

Step 2: Create the Data Definition in XML Publisher and associate it with Data Template.

Step 3: Define a Concurrent Program to generate the Data XML File.

The short name in the above concurrent program is the code what you give while defining data definition.

Then run the above created concurrent program and get the xml output.

Save the output.

Step 4: Define the RTF Template using the Generated Data XML.
Go to word document and design your rtf Template.
Step 5: Registering the Template with XML Publisher.

Step 6: Execute the concurrent program to generate the PDF report.

Ur's
AmarAlam

Developing XML Publisher Report - using Data Source as PL/SQL Stored Procedure

Background:

Developing sample XML Publisher Report with Executable Method as 'PL/SQL Stored Procedure'

Prerequisite for the below Example:
1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
     VALUES (569, 'Oracle Cost Management');
3. Issue Commit

1. Create a Package Spec & Body with a single Procedure
Spec:

CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END MY_PACKAGE;
/
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := 'SELECT product_code, product_name
         FROM demo_products
       WHERE product_code = ' || p_product_id;

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      -- set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
      DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');

      -- now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length - l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
   END REPORT;
END MY_PACKAGE;
/

2. Define Executable
Navigation: Application Developer > Concurrent > Executable

Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: MY_PACKAGE.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step

- Output Format should be XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group.

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

6. Run the Concurrent Program to see the output


Ur's
AmarAlam

Benefits of Oracle XML Publisher

» Document Management Solution well integrated with Oracle Applications.
» A solution which authors, manages and delivers the Business documents.
» Meets the Business Requirements like corporate identity.
» Removes the Complexity
» Reduces Maintenance Cost
» Reduces Total Cost
» One Template -> Multiple Outputs -> PDF,HTML,EXCEL,RTF
» Business Consultants can use familiar desktop tools like Word, PDF, etc.
» Extract one time ->-> Publish multiple times

Query to get details of XML Report

SELECT fndcptl.USER_CONCURRENT_PROGRAM_NAME UserConcurrentProgram,
       fndcp.CONCURRENT_PROGRAM_NAME ConcurrentProgramShortName,
       fndex.EXECUTION_FILE_NAME ExecutionFileName,
       datadefinitiontl.DATA_SOURCE_NAME DataDefinitionName,
       datadefinition.DATA_SOURCE_CODE DataDefinitionCode,
       templatestl.TEMPLATE_NAME TemplateName,
       templates.TEMPLATE_CODE TemplateCode,
       lobs.FILE_NAME FileName
  FROM fnd_executables fndex,
       fnd_concurrent_programs fndcp,
       fnd_concurrent_programs_tl fndcptl,
       xdo_ds_definitions_b datadefinition,
       xdo_ds_definitions_tl datadefinitiontl,
       xdo_templates_b templates,
       xdo_templates_tl templatestl,
       xdo_lobs lobs
 WHERE     fndex.EXECUTABLE_ID = fndcp.EXECUTABLE_ID
       AND fndcp.CONCURRENT_PROGRAM_ID = fndcptl.CONCURRENT_PROGRAM_ID
       AND fndcp.CONCURRENT_PROGRAM_NAME = datadefinition.DATA_SOURCE_CODE
       AND datadefinition.DATA_SOURCE_CODE =
              datadefinitiontl.DATA_SOURCE_CODE
       AND datadefinitiontl.DATA_SOURCE_CODE = templates.DATA_SOURCE_CODE
       AND templates.TEMPLATE_CODE = templatestl.TEMPLATE_CODE
       AND templatestl.TEMPLATE_CODE = lobs.LOB_CODE
       AND lobs.LOB_TYPE = 'TEMPLATE_SOURCE'
       AND fndex.EXECUTABLE_NAME = 'XXAJ_SALES_ORDER_REPORT'


Ur's
AmarAlam

Some Useful XML Tags

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

SELECT DBMS_XMLGEN.getxml(
'SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND    order_number = &p_order_number')
FROM DUAL



Ur's
AmarAlam

Leading and Trailing zeroes truncated for excel reports

Leading and Trailing zeroes truncated for excel reports Microsoft Excel is too smart and it identifies whether the value in the cell is a Text or number and applies formatting accordingly. This sometimes becomes an issue for us when we are trying to generate an excel report. For example item number 0003463262360 has all the numbers and starts with zero, this when printed in excel report displays it as 3463262360. Hence all the leading zeroes are truncated. Same issue happens when we have decimal and trailing zeroes.

FO formatting options can be used to get away with this problem. Below is the syntax for same.

Ur's
AmarAlam

Setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .


Ur's
AmarAlam

How to Generate XML Tag from PL/SQL

CREATE OR REPLACE PACKAGE AJ_XML AS

PROCEDURE put_xml_tags(i_tag_type_flag IN VARCHAR2
                      ,i_tag_name      IN VARCHAR2 DEFAULT NULL
                      );

PROCEDURE put_xml_data ( i_xml_tags IN VARCHAR2
                       , i_output  IN VARCHAR2
                       )
PROCEDURE XX_XML (i_employee_number IN VARCHAR2);
           
END;

CREATE OR REPLACE PACKAGE AJ_XML AS

PROCEDURE put_xml_tags(i_tag_type_flag IN VARCHAR2
                      ,i_tag_name      IN VARCHAR2 DEFAULT NULL
                      )
IS
--
  l_strng VARCHAR2(32376);
--
BEGIN
--
  CASE
  WHEN i_tag_type_flag = 'S' THEN
--
      l_strng := '';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
      l_strng := '';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
      l_strng := ''|| USERENV('LANG') ||'';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
      l_strng := ''|| TO_CHAR(SYSDATE,'DD-MON-RRRR') ||'';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
    WHEN i_tag_type_flag = 'E' THEN
--
        l_strng := '';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
    WHEN i_tag_type_flag = 'G' THEN
--
      l_strng := '<'||i_tag_name||'>';
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
  END CASE;
--
EXCEPTION
  WHEN OTHERS THEN
    FND_FILE.PUT_LINE(FND_FILE.LOG,'PERC1428-put_xml_tags Function Error '||SQLERRM);
--
END put_xml_tags;
--
PROCEDURE put_xml_data ( i_xml_tags IN VARCHAR2
                       , i_output  IN VARCHAR2
                       )
IS
  i_occ       NUMBER(2):=1;
  l_xml_tag   VARCHAR2(32767);
  l_output    VARCHAR2(32767) ;
  l_strng     VARCHAR2(32767);
 BEGIN
--
    WHILE (INSTR(i_xml_tags,'#',1,i_occ)!=0) LOOP
      BEGIN
        SELECT  SUBSTR(i_xml_tags,DECODE(i_occ,1,1,INSTR(i_xml_tags,'#',1,i_occ-1)+1),INSTR(i_xml_tags,'#',1,i_occ)-DECODE(i_occ,1,1,INSTR(i_xml_tags,'#',1,i_occ-1)+1))
        INTO l_xml_tag
        FROM dual;
    EXCEPTION
      WHEN OTHERS THEN
          l_xml_tag :='END';
    END;
--
    BEGIN
       SELECT  SUBSTR(i_output,DECODE(i_occ,1,1,INSTR(i_output,'#',1,i_occ-1)+1),INSTR(i_output,'#',1,i_occ)-DECODE(i_occ,1,1,INSTR(i_output,'#',1,i_occ-1)+1))
       INTO l_output
       FROM dual;
    EXCEPTION
       WHEN OTHERS THEN
         l_output :='';
    END;
--
      l_strng:= ('<'||l_xml_tag||'>'||''||'');
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
      i_occ := i_occ+1;
--
   END LOOP;
   --
 EXCEPTION
   WHEN OTHERS THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,'PERC1428- put_xml_data Function Error '||SQLERRM);
END put_xml_data;

PROCEDURE XX_XML( o_errbuf             OUT VARCHAR2 -- Error Message
                , o_retcode            OUT VARCHAR2 -- Error Code
                , i_employee_number    IN  VARCHAR2
AS
 l_xml_tags VARCHAR2(32376);
 l_xml_data VARCHAR2(32376);
 CURSOR c_emp_info ( i_employee_number  IN NUMBER
                   )
 IS
 Select first_name, last_name, full_name, employee_number from per_all_people_f where employee_number=i_employee_number;

BEGIN
    put_xml_tags('S');  
    put_xml_tags('G','G_EMP_INFO');
    BEGIN
      FOR r_emp_info IN c_emp_info(i_employee_number)
      LOOP
         l_xml_tags := 'LAST_NAME#FIRST_NAME#FULL_NAME#EMPLOYEE_NUMBER#';
         l_xml_data := r_emp_info.last_name                     || '#' ||
                       r_emp_info.first_name                    || '#' ||
                       r_emp_info.full_name                     || '#' ||
               r_emp_info.employee_number               || '#' ;
         put_xml_data(l_xml_tags,l_xml_data);
       END LOOP;
    put_xml_tags('G','/G_EMP_INFO');
    put_xml_tags('E');
EXCEPTION
  WHEN OTHERS THEN
       FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured IN PERC1435' || SQLERRM);
END XX_XML;

END AJ_XML;
/


Ur's
AmarAlam

How to Develop XML Publisher Report (XMLP) or BI Publisher Reports

hey Guys Now adays BI Publisher is in Boom technology formally it know as XMLP means XML Publisher.
We can develop this by using following steps:

1)Develop the Report(.rdf) Only DataModel

2)Save the report and Compoile and Generate the data in .xml
           Format File=>Generate to File => XML

3)Open the Ms-Word document Design the Layout include the follwing Tool Bar options called Form and Templete builder

4)Define the Table and Specify the Titles and Labels whatever we would like to print

5)Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax : Here G_USER_ID is Group name from .rdf Data Model Note: It means Indirectley Repeating Frame starting

6)Define the Fields which we would like to display Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax ?
Note: Define all the fields like this

7)We have to close the Repeating Frame (For each Loop) Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax

8)Save the document in .rtf(Rich Text File format)

9)Load the .xml file data and generate the Output in pdf Format Data=>Load XMl Data=>Load the .xml File=>Once Data is Loaded succesfully

10)Generate the Preview as per this Preview=>PDF

Registering in Apps

1)Develop the .rdf file and .rtf file

2)Register both .rdf and .rtf file


.rdf File Registration

1)Move the .rdf file into CUS_TOP\11.5.0\Reports\US

2)Create Execuiteable

Concurrent Program Output = XML

Request Group

Responsibility

User

SRS

3)Copy the Concurrent Program Short name .rtf File


RTF Registration:

1)Goto the XML Publisher Administrator Responsibility

2) Creation of Data Defination
Select Data Definations form Enter Data Defination

Name = Enter Any Name

Code = Concurrent Program Short Name

Application = Any application

startdate = Current Date

3)Copy the DataDefination name we have to add this to the Templete

4)Creation of Template form

Open the Templete Form

Enter Templete Name : Any Name

DataDefination : enter data defination whatever we have created

Code : Concurrent Program Short Name

Type : rtf

application : any Application

File : Upload the .rtf file

Laguage : English

Territory : United States

5)Select Apply button

After register the .rtf file submit concurrent Program from SRS window system will automatically pick the .rtf file and generates the Output.

If we want to customze the .rtf file we have to download by using templete , Customize thenupload the .rtf templeteme, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal



Ur's
AmarAlam

Steps to create a XML Publisher Report

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.
2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.
3] Set the user parameter as p_conc_request_id.
4] Add the default values to the Before Report and After Report triggers(not mandatory)
5] Ftp the Report to the Cust_Top/Report/Us.
6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.
7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.
8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.
9] Design the template in Ms Word(Using the .Rtf file).
10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.
11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.
12] Now go to the Responsibility and run the request.


Ur's
AmarAlam