Showing posts with label XML Publisher. Show all posts
Showing posts with label XML Publisher. Show all posts

Friday, September 23, 2022

Query to get XML Template, Data source and template file details in Oracle Apps R12

 









Test Instance: R12.2.5


Query:


SELECT   xtv.application_short_name,
         xtv.template_code,
         xtv.ds_app_short_name,
         xtv.data_source_code,
         xtv.template_type_code, 
         xtv.template_name,
         xtv.description,	
         fu.user_name created_by,		 
         (SELECT   application_name
            FROM   fnd_application_vl
           WHERE   application_short_name =
                      xtv.application_short_name)
            application_name,
         (SELECT   meaning
            FROM   fnd_lookups
           WHERE   lookup_type = 'XDO_TEMPLATE_TYPE'
                   AND lookup_code = xtv.template_type_code)
            template_type,
         (SELECT   data_source_name
            FROM   xdo_ds_definitions_vl
           WHERE   data_source_code = xtv.data_source_code
                   AND application_short_name =
                         xtv.ds_app_short_name)
            data_source_name,
         (SELECT   file_name
            FROM   xdo_lobs
           WHERE   ( (    lob_type = 'TEMPLATE'
                      AND xdo_file_type != 'RTF'
                      AND xdo_file_type = xtv.template_type_code
                      AND xdo_lobs.LANGUAGE = xtv.default_language
                      AND xdo_lobs.territory =
                            xtv.default_territory)
                    OR (lob_type = 'TEMPLATE_SOURCE'
                        AND xdo_file_type IN ('RTF', 'RTF - ETEXT')
                        AND xdo_lobs.LANGUAGE =
                              xtv.default_language
                        AND xdo_lobs.territory =
                              xtv.default_territory)
                    OR (    xdo_file_type = 'RTF'
                        AND lob_type = 'TEMPLATE_SOURCE'
                        AND LANGUAGE = xtv.mls_language
                        AND territory = xtv.mls_territory
                        AND EXISTS
                              (SELECT   mls.lob_code
                                 FROM   xdo_lobs mls
                                WHERE   mls.lob_type = 'MLS_TEMPLATE'
                                        AND mls.lob_code =
                                              xtv.template_code
                                        AND mls.application_short_name =
                                              xtv.application_short_name
                                        AND mls.LANGUAGE =
                                              xtv.default_language
                                        AND mls.territory =
                                              xtv.default_territory)
                        AND NOT EXISTS
                              (SELECT   LOCAL.lob_code
                                 FROM   xdo_lobs LOCAL
                                WHERE   LOCAL.lob_type = 'TEMPLATE_SOURCE'
                                        AND LOCAL.lob_code =
                                              xtv.template_code
                                        AND LOCAL.application_short_name =
                                              xtv.application_short_name
                                        AND LOCAL.LANGUAGE =
                                              xtv.default_language
                                        AND LOCAL.territory =
                                              xtv.default_territory)))
                   AND lob_code = xtv.template_code
                   AND xdo_lobs.application_short_name =
                         xtv.application_short_name)
            default_template_file,
         (SELECT   file_name
            FROM   xdo_lobs
           WHERE   lob_type = 'TEMPLATE_SOURCE'
                   AND lob_code = xtv.template_code
                   AND xdo_lobs.application_short_name =
                         xtv.application_short_name
                   AND xdo_lobs.LANGUAGE = xtv.mls_language
                   AND xdo_lobs.territory = xtv.mls_territory)
            mls_template_file,
         (SELECT   NAME
            FROM   fnd_iso_languages_vl
           WHERE   iso_language_2 = xtv.default_language)
            default_file_lang,
         DECODE (xtv.default_territory,
                 '00',
                 ftv.territory_short_name)
            default_file_terr,
         xtv.default_language,
         xtv.default_territory,
         xtv.template_status,
         xtv.use_alias_table,
         xtv.start_date,
         xtv.end_date,
         xtv.created_by,
         xtv.creation_date,
         xtv.last_updated_by,
         xtv.last_update_date,
         xtv.last_update_login,
         xtv.object_version_number,
         xtv.dependency_flag,
         xtv.mls_language,
         xtv.mls_territory,
         xtv.default_output_type			
  FROM   xdo_templates_vl xtv,
         fnd_application_vl fndapplicationvl,
         fnd_territories_vl ftv,
         fnd_user fu
 WHERE   fndapplicationvl.application_short_name =
            xtv.application_short_name
   AND ftv.territory_code(+) =  xtv.default_territory
   AND xtv.created_by = fu.user_id		 
         --AND template_name = 'Template Name'

Sample Output:



Friday, September 23, 2022 by Team search · 0

Tuesday, May 26, 2015

BI Publisher API Example – Creating EXCEL Report from XML/XSL-FO/RTF in Oracle Apps?













Oracle has shared us a set of BI Publisher Java Classes for performing various BI Publisher function like converting RTF to XSL-FO, generating PDF output from XSL-FO and XML etc.
In this post, we will generate an EXCEL report output from XML file and RTF Template using the standard Oracle BI Publisher API’s (Java based).

STEP1: Prepare and Convert RTF template to XSL-FO file

Oracle has provided a Java Class to generate PDF output based on XSL-FO file and XML input. Hence, first step is to generate the XSL-FO file based on RTF template. Details are available in another article,
http://www.shareoracleapps.com/2015/05/how-to-convert-rtf-to-xsl-fo-using-bi-publisher-api-in-oracle-apps.html
Now, we should be having a XSL-FO file now.

STEP2: Prepare the Java wrapper Class for calling Standard class FOProcessor


import java.io.InputStream;
import java.io.OutputStream;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.fo.util.FOUtility;
import java.io.*;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import oracle.apps.xdo.batch.DocumentProcessor;

public class convertxmltoexcel {

  public static void main(String[] args)
  {
    try
    {    
      FOProcessor processor = new FOProcessor();
      processor.setData(args[0]); //input XML
      processor.setTemplate(args[1]);  //input XSL template
      processor.setOutput(args[2]); // output Excel File
     
          // Set output format (for EXCEL generation)
      processor.setOutputFormat(FOProcessor.FORMAT_EXCEL);
      // Start processing
   
      processor.generate();
    }
    catch (Exception e)
    {
       e.printStackTrace();
       System.exit(1);
    }

    System.exit(0);
   
  }
}


STEP3: Compile the Java wrapper


javac -classpath $CLASSPATH convertxmltoexcel.java

Step 4: Copy the Wrapper Class to JAVA_TOP


cp convertxmltoexcel.class $JAVA_TOP

STEP5: Verify the Sample RTF template, XSL-FO file and XML File

Input XML:
clip_image001[4]
Actual RTF:
clip_image003[4]
Generated XSL-FO based on Step 1:
clip_image005[4]

Step6: Place the XSL-FO and Input XML file in Desired Directory and Call the Wrapper Java Class


java -classpath $CLASSPATH convertxmltoexcel ./INPUTDATAXML.xml ./RTFTEMPLATEEX.xsl ./RTFTEMPLATEEX.xls
clip_image007[4]

Output Excel Report:
clip_image008[4]
Now the Command given in the step6 can be called from HOST based concurrent program in Oracle Apps.

Happy Shairng !! Enjoy Learning!!

Tuesday, May 26, 2015 by Team search · 1

BI Publisher API Example – Creating PDF Report from XML/RTF?











Oracle has shared us a set of BI Publisher Java Classes for performing various BI Publisher function like converting RTF to XSL-FO, generating PDF output from XSL-FO and XML etc.
In this post, we will generate a PDF output from XML file using the standard Oracle BI Publisher API’s (Java based).

STEP1: Prepare and Convert RTF template to XSL-FO file

Oracle have provided a Java Class to generate PDF output based on XSL-FO file and XML input. Hence, first step is to generate the XSL-FO file based on RTF template. Details are available in another article,
http://www.shareoracleapps.com/2015/05/how-to-convert-rtf-to-xsl-fo-using-bi-publisher-api-in-oracle-apps.html
Now, we should be having a XSL-FO file.

STEP2: Prepare the Java wrapper Class for calling Standard class FOProcessor


import java.io.InputStream;
import java.io.OutputStream;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.fo.util.FOUtility;
import java.io.*;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import oracle.apps.xdo.batch.DocumentProcessor;

public class convertxmltopdf {

  public static void main(String[] args)
  {
    try
    {    
      FOProcessor processor = new FOProcessor();
      processor.setData(args[0]); //input XML
      processor.setTemplate(args[1]);  //input XSL template
      processor.setOutput(args[2]); // output PDF File
     
          // Set output format (for PDF generation)
      processor.setOutputFormat(FOProcessor.FORMAT_PDF);
      // Start processing
   
      processor.generate();
    }
    catch (Exception e)
    {
       e.printStackTrace();
       System.exit(1);
    }

    System.exit(0);
   
  }
}

STEP3: Compile the Java wrapper

javac -classpath $CLASSPATH convertxmltopdf.java

Step 4: Copy the Wrapper Class to JAVA_TOP

cp convertxmltopdf.class $JAVA_TOP

STEP5: Verify the Sample RTF template, XSL-FO file and XML File

Input XML:
clip_image001[6]
Actual RTF:
clip_image003[6]
Generated XSL-FO based on Step 1:
clip_image005[6]

Step6: Place the XSL-FO and Input XML file in Desired Directory and Call the Wrapper Java Class

java -classpath $CLASSPATH convertxmltopdf ./INPUTDATAXML.xml ./RTFTEMPLATEEX.xsl ./RTFTEMPLATEEX.pdf
clip_image007[6]

Output PDF:
clip_image009[6]


by Team search · 1

Subscribe to: Posts (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search