Import data Template/RTF/Bursting control files

Problem is that any custom XML publisher report requires dependency on application access in PROD (XML Publisher Administrator (R)). All supporting files for XML report are being uploaded through application.

 

Requirement is to have any import method for XML publisher supporting files i.e.

Data Template file (XML file)

Bursting Control File (XML file)

Template (RTF file)

 

Resolution

XDOLoader is the standard OA utility to suffice this requirement. This is similar to unix based FNDLOAD utility and should be run from server with all parameters.  

 

Syntex

java oracle.apps.xdo.oa.util.XDOLoader  

MODE                                                                 UPLOAD/DOWNLOAD

–DB_USERNAME                                             apps user name

–DB_PASSOWRD                                             apps password

–JDBC_CONNECTION                                     jdbc connection string

–LOB_TYPE                                                       TEMPLATE_SOURCE/   
                                                                              DATA_TEMPLATE/
                                                                              BURSTING_FILE             

–APPS_SHORT_NAME                                     xxxxx

–LOB_CODE                                                       Data definition Code

–LANGUAGE                                                      en

–XDO_FILE_TYPE                                             RTF/XML-DATA-TEMPLATE
                                                                              /XML-BURSTING-FILE

[–CUSTOM_MODE]                                            FORCE

[–LOG_FILE]                                                       log file name

[-DEBUG]                                                             true/false

 

 

RTF File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXXXX  -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE RTF -FILE_NAME 123.rtf -CUSTOM_MODE FORCE

 

 

 

Data Template File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME 123.xml -CUSTOM_MODE FORCE

 

 

Bursting File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -JDBC_CONNECTION  "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))"  -LOB_TYPE BURSTING_FILE -APPS_SHORT_NAME XXXXX  -LOB_CODE  DATA_DEFINITION_CODE -LANGUAGE en  -XDO_FILE_TYPE XML-BURSTING-FILE -FILE_NAME 123b.xml -CUSTOM_MODE FORCE

 

**Highlighted are user defined inputs

 

Concurrent Program Status

/* Query to check program status and completion time*/


SELECT fcr.REQUEST_ID,
  FU.USER_NAME,
  f.responsibility_name,
  fcr.USER_CONCURRENT_PROGRAM_NAME,
  DECODE(fcr.PHASE_CODE,'C','Completed','R','Running','E','Error','P','Pending','Other') PHASE,
  DECODE(fcr.STATUS_CODE,'C','Normal','E','Error','G','Warning','X','Terminated','Q','Sacheduled',fcr.STATUS_CODE)STATUS ,
  TO_CHAR(fcr.ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') STARTED_AT,
  TO_CHAR(fcr.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ENDS_ON,
  ROUND((NVL(fcr.ACTUAL_COMPLETION_DATE,SYSDATE)-fcr.ACTUAL_START_DATE)*24*60) "Duration (mins)",
  fcr.argument_text,fo.file_type,fo.file_size,fo.file_name,LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME,OUTFILE_NODE_NAME
FROM FND_CONCURRENT_REQUESTS fcr1,
  FND_CONC_REQ_SUMMARY_V FCR,
  FND_USER FU,
  fnd_conc_req_outputs fo,
  fnd_responsibility_vl f
WHERE FCR.REQUESTED_BY=FU.USER_ID
AND fcr.request_id = fo.concurrent_request_id(+)
and f.responsibility_id =  fcr.responsibility_id
and fcr1.REQUEST_ID = fcr.REQUEST_ID
AND USER_CONCURRENT_PROGRAM_NAME like ('%Programname%')
--and user_name like '%SANDEEP%'
ORDER BY NVL(fcr.ACTUAL_START_DATE,SYSDATE-1000) DESC;

Send Email using Task Scheduler

Requirement – Task scheduler to send any file as attachment.

 

Prerequisite – Should have SMTP details for any available email service

 

Resolution – Task scheduler has send email option, which is not helpful and people face many issues.

sendEmail is one utility, which can be used to fulfill the need. Download this executable file and save at system.

1.        Create a task in scheduler
2.        Select action- Run a program
3.      Give the sendEmail.exe in file name

1.  Start in (optional)=path of sendEmail.exe in your system
2.  Add arguments (Optional) – provide mailing details as mentioned below -

-f sandeep19rm@gmail.com -u Test Email -m Here is the Email Option! -a sandeep.txt -t sandeep@oalearning.com -cc learning@oalearning.com -s smtp.gmail.com:587 -xu username@gmail.com -xp password -o tls=yes

 

Options :

-f	From Email Address
-u	Mail Subject
-m	Mail Body
-a	Attachment file name
-t	To Email Address
-cc	CC Email Address
-bcc	BCC Email Address
-s	SMTP details (server:port)
-xu	Username
-xp	Password
-o	Mailing options

 

 
 

 

Windows Task Scheduler

This article is nowhere related with EBS, but will be useful for any computer users.

 

Requirement – Automated process to be executed on schedule basis in windows.

Wanted to ensure if there exist any specific file in system, I should be alerted with details.

 

Solution – Window provides task scheduler to achieve same. Task scheduler is same as oracle triggers with GUI.

 

How to find - Login to windows >> Start>> Task Scheduler

 

Create Task – Click on create Task action  

 

 

General Tab – Set the task name and description and click on Triggers

 

Triggers – This tab is to set the execution time. It can be set to run on schedule or on demand. Click on New button or edit existing schedule.

Provide scheduling detail and move to Actions

Actions- This is to perform different activity. It can run a program, send email or display messages. Click new button to set a new action.

Action = start a program   Program Name = Path of executable file.

We can give the file name only and place path in Start in (optional) block.

 

Conditions – To set various conditions when task should run and when not.

 

Settings – This is to define different instance scenarios.

 

Press OK and it will pop up the windows login password. Task will run at defined schedule.

 

 
 

 

DOA assignment using Data Loader

Data loader is a tool, used for data entry during EBS setups. This can be leveraged to do any manual data entry in various applications.

This post is to have data loader script for assigning DOA (delegation of Authority) to oracle positions in procurement.

 

 

First DOA Assignments	Update DOA Value	Add additional DOA value
\{TAB}	\{TAB}	\{TAB}
POSITION	POSITION	POSITION
\{TAB}	\{TAB}	\{TAB}
Purchase Requisitions	\{F11}	\{F7}
\{TAB}	Purchase Requisitions	Purchase Requisitions
10M	\^{F11}	\{TAB}
\{TAB}	\{TAB}	200K
\{TAB}	\{TAB}	\{TAB}
\^s	\{TAB}	\{TAB}
\{F4}	SYSDATE	\^s
\{ENTER}	\{TAB}	\{F4}
	\^s	\{ENTER}
	\{F4}
	\{ENTER}

 

First DOA Assignment - Position does not have any existing active/inactive assignment

Update DOA Value - Given sample is to end date existing assignment. Incase there is a change in value,give new value at second TAB.

Add additional DOA Value - assignment for a position having already some assignments. 

Multiple Queries in Data Templete

Problem – Oracle report builder provides flexibility to create multiple queries and join those with common attribute. How does data template method help us to use multiple queries?

Solution – Data template also provides same flexibilities to list down queries in multiple sections and connect them as parent child relationship.

There are two approached available to join multiple queries in data template.

•        Bind variable
•        Data link method

Bind Variable most efficient and faster option. Identify the common column in parent query and use it as bind variable in child queries.

<dataQuery>

<sqlstatement name="Q1_USER">

<![CDATA[

SELECT user_id BIND_USER_ID ,user_name From fnd_user

]]>

</sqlstatement>

<sqlstatement name="Q2_RESP">

<![CDATA[

SELECT responsibility_id  From fnd_user_resp_groups WHERE user_id=: BIND_USER_ID

]]>

</sqlstatement>

</dataQuery>

Data Link is another way to establish the relationship between multiple queries

<link name="USER_RESP_LINK" parentQuery="Q1_USER" parentColumn="user_id" childQuery="Q2_RESP" childColumn="user_id" condition="Q1_USER.user_id= Q2_RESP.user_id" />

XML Output

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

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1020</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

<G_USER>

  <USERID>200</USERID>

  <USERNAME>Deepak</USERNAME>

            <LIST_G_RESP>

            <G_RESP>

            <RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >               

            <RESPONSIBILITY_ID>1030</ RESPONSIBILITY_ID >               

</G_RESP>

<LIST_G_RESP>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >

Insert Table in XML Layout

Requirement – Insert a Table in xml layout. Inserting table in MS word and placing place holder creates some alignment issues.

Options – Instead of creating table in MS word manually, we can use BI publisher desktop utility to insert table in RTF file.

1.       Generate the XML Tags
2.       Open the MS Word to create RTF layout
3.       Go to Add on ( Menu) >> Data >> Load XML Data
4.       Browse the xml file and load
5.       Go to Add on ( Menu) >> Insert >> Table/Form>>Select the group and drag to Template section and press Drop All Nodes                                 
6.       Select the Group and pick Table property (Other options are form and free form).Select and delete any unwanted field and press OK.                      

Table will be created with all column and respective place holders.

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.