XML Report Using Source as Data Template

Problem – Generate XML source for BI publisher report.

Solution – There is several options available to generate XML tags, intend as source for BI/XML publisher reports in EBS. One among those is use of data template.

Data Template is an xml file having predefined set of tags. Create a data template file and attach file at (XML Publisher Administrator (R) >> Data Definition >> Add Files >> Data Template)

Header	<?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1"   defaultPackage=”T1” version="1.0" >
Data Trigger	<dataTrigger name="beforeParameter" source="T1.beforeParameterFunction"/>
Parameter Section	<Parameters>             <parameter name="p_param1" datatype="number"/>       </Parameters>
Data Trigger	<dataTrigger name="afterParameter" source="T1.afterParameterFunction"/> <dataTrigger name="beforeReport" source="T1.beforeReportFunction"/>
Query Section : We can use multiple query here	<dataQuery>             <sqlstatement name="Q_EMP">                 <![CDATA[SELECT empname From EMP WHERE empid=: p_param1]]>             </sqlstatement>      </dataQuery>
Data Structure : XML tag generation.	<dataStructure>                      <group name="G_EMP" source="Q_EMP">                 <element name="EMPNAME" value="empname"/>            </group>     </dataStructure>
Data Trigger	<dataTrigger name="aftreReport" source="T1. aftreReportFunction "/>

  

</dataTemplate>

Sample Data Template File

<?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1" defaultPackage="T1"   version="1.0" >      

<Parameters>

<parameter name="p_user_id" datatype="number"/>

</Parameters>

<dataQuery>

<sqlstatement name="Q_USER">

<![CDATA[

SELECT user_id,

                User_name,

       FROM FND_USER

       where

        user_id=:p_user_id

   ]]>

</sqlstatement>

</dataQuery>

<dataStructure>

<group name="G_USER" source="Q_USER">

                <element name="USERID" value="user_id"/>

<element name="USERNAME" value="user_name"/>

  </group>

</dataStructure>

</dataTemplate>

Sample generated XML -

<?xml version="1.0" encoding="UTF-8" ?>

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >

XML Publisher - Different Executables & Data Source available

This post is to list out different executable and corresponding data source available for XML publisher report.

System Administrator Responsibility -

Concurrent >> Program >> Executable >> Create Executable file

                XML_LEARNING  (Oracle Report Builder)

                OA_LEARNING.XML_LEARNING (PL/SQL Stored Procedure)

                XDODTEXE (Java Concurrent Porgram)     <*** Standard program provided by Oracle>

Concurrent >> Program >> Define >> Create Program

                Executable Name – should be as above

                Output Format - XML

XML Publisher Responsibility -

Data Definition >> Create New data definition (code should be CP name)

                Data Template File = Attached if data source is XML file and CP is Java concurrent program

                Bursting File = Attach XML file, if bursting is required

Template >> Create a new template and attach the layout (RTF) file

                                                                               

 Table: Different Executable and Data source for BI Report

 

Excel Output Without XML publisher

Requirement– There are limited formats available to view Concurrent Program output. XML is one format which is not available. 

                    

Available Options – XML publisher is one frequently used solution, which has capability to display output in PDF, EXCEL and RTF formats. EXCEL file generated using xml processing is having more size compare to actuals.

                                    

Solution – This post is to discuss another way to generate output file in EXCEL format without using XML publisher. A small setup change is required.

1. Create a concurrent program having PL/SQL as executable
2. Select the output type as any available option (ex - PCI)                              
3. Print the output using FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Output Message’);
4. Change the Mime type(view option) as EXCEL for PCL file format
5. Run the concurrent program
6. Select Tool>>Copy option to see the output

Script to Update the Mime Type

Limitation – To view multi column format, use a separator during output print. Once output is opened in excel use excel options to display multi column view by using used separator.  

Update MIME type

There are few file formats available in oracle EBS to support concurrent program output.

Each file format is having standard Mime Type associated and being stored in FND_MIME_TYPES_TL Table, which can be updated to any other mime type using below script –

UPDATE fnd_mime_types_tl

SET    mime_type = 'application/vnd.ms-excel'

,      description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,      last_updated_by = 0

,      last_update_date = sysdate

WHERE  file_format_code = 'PCL'

AND   mime_type = 'application/vnd.hp-PCL';

COMMIT;

Emailing Concurrent Program Output

Requirement –  It is very common that business request to share concurrent program output file on every run.

 

 

Resolution – There are multiple options available to notify the output via email. Some of those are as below -

• - PL/SQL program unit to send the output file
• - Unix commands to send the output file
• - Email bursting concepts, if it is XML publisher report

 

Each program requires, additional effort/custom programs to achieve the emailing feature. In this post we shall learn the options already available in system. Which need small configuration change and CP will send the o/p as email.

 

 

 

Steps :-

1)      Configuration Change – 2 profile needs to be set.

·        FND: SMTP Host   >> This to store the smtp server host details

·        FND: SMTP Host  >> This to store the smtp server port details

 

 

2)     While submitting the CP, click the Delivery Opts (B)

 

 

3)     Go to Email tab

 

 

4)     Provide the To/From email address and Subject line, then press OK (B)

5)     Submit/Schedule the program

Email bursting should be better approach, if we are working with XML publisher report.

Program will send the output file to specified email address. Program log file can be referenced in case any issue occurred.

Profile to Enable Examine Option

Requirement -  Examine option in oracle core forms is not opening

On Oracle forms getting below error, while trying to explore examine options

Resolution -  Set the profile “Utilities:Diagnostics” to YES

This profile will enable examine option J

Check the examine option again

UNIX commands.....

1) zip and unzip : zip <file_name>.zip <file_required_to_zip>
                         $_Test>zip test.zip ls *.*   It will zip all files available at location and create test.zip file

2) Softt link Creation :  ln -s $FND_TOP/bin/fndcsper <filename>

3) Execute the enviornment script : . ./*.env

Set Enviornment Variable in Unix Server

Purpose - To set enviornment variable on unix server (for example Product_TOP and custom_TOP ).

Enviornment Variable - is the shell  script needs to run and assign the corrosponding path to variables.

How to Set -
 >> Login to unix server
 >>  locate  *.env
 >>  execute the *.env script

Default Location : /oracle/ebs/apps/apps_st/appl

File Name Format : $DBserviceName_$InstanceName.env ( * File name can be anything)
Command -
To set the enviornment variable = . ./XXXXX.env 
To validate the enviornment variable = echo $XXX_TOP

Desktop Integrator - A webADI Introduction

Introduction :

For single records transaction Oracle suggest to use the application and
provides open interface and public APIs for bulk transaction.

webADI is another strong feature provided by Oracle for bulk operations,
which allows user to provide input in MS application (Word or Excel..etc).

Why WebADI ? :

It requires technical expertise for both interface and API usages.

For interface user should know to insert all transactional data into interface table and API needs transactional data to be imported in any portable format to server.

WebADI does not have such technical dependency as it is available to user as function and Launch desktop integrator (Excel) to feed transactional data.

This provides reporting option also.

How It works :

        For this one interface package required to do  initial validations.

Post it we can use any of options -   

1) Either insert all data into Interface Tables and run the Import
    Program Manually

2) Call API inside the interface program and do transactions directly  

Components  :  

1) Integrator  - Basic definition and includes user name, internal
                       name and application name

2) Interface   -  Most Important section , which directs data to flow (can
                       be either of Table or Procedure/Function type)

3) Content  -    This is required if webADI is being used for reporting
                       purpose also

4)  Upload    -   Controls the upload options

5)  Importer –  Definition of import options

6)  Layout  -     Attributes arrangement on excel

    

Responsibility - Desktop Integration Manager

Enable/Disable Concurrent Program Parameters

Requirement - Business need is to have multiple parameters but end user will be allowed to use one at a time. selection of any parameter, should disable others.  

Solution - Below is one possible option which provides the same behavior in other way.

This requires one additional parameter as Selection/Choice which will decide the enabled parameter.

There will be as many dummy (non displayed) parameters as required parameters in program. actual parameters will be dependent on respective dummy parameters.

Dummy parameters will be dependent on selection parameters and hold values based on selection.

Example - User requires 2 parameters supplier number and name to be available on one report and only one should be selected at time. So the requirement was to make other disable. dependent parameters has limitations and it cannot flow bottom to top. we can achieve this by introducing one selection parameter, which can hold 2 values -

1)      Name – Supplier Name

2)      Number – Supplier Number

Initially both Supplier Number and Name range parameter will be disabled.

 

Selection = Name , will enable supplier Name parameter range.

Selection = Number , will enable supplier Number parameter range.

Implementation – Create 3 dummy parameter (1 displayable and 2 non displayable).

           

Parameter	Value Set	Enable	Display
Test1	VS0	Y	Y
Test2	VS1	Y	N
Test3	VS2	Y	N

Value Set - VS0, will hold below values

Parameter Test2 and Test3 :

 

Default Value  select decode(:$FLEX$.VS0,'NUMBER','Y',NULL) from dual

Test3 will have similar definition.

Value Set =  VS2

Default Value  select decode(:$FLEX$.VS0,'NAME','Y',NULL) from dual

Now include the below condition for Number and Supplier Value set definition –

Supplier Number Value set  -   :$FLEX$.VS1='Y'

Supplier Name Value set     -   :$FLEX$.VS2='Y'