UPLOAD LDT Files using shell script

Requirement is to migrate the configuration files (LDTs) across environments.

 

Resolution - FNDLOAD is Oracle provided utility to achieve this by passing certain inputs. The required inputs are

1)       DB credentials

2)       Respective LCT name

This is very commonly and frequently used utility in EBD development.

 

Through this post we are sharing a shell script which can exclude LCT name parameter knowledge for LDT migration.

 

# !/bin/sh

 

echo "Enter APPS password : "

stty -echo

read v_apps_schema

stty echo

 

for file in `ls *.ldt`

do

v_ext=`echo | cut -d "." -f2`

v_lct=`grep "patch/115/import/" $file | cut -d "@" -f2`

echo "File Name    :$file" 

echo "Control File :$v_lct"

FNDLOAD apps/$v_apps_schema 0 Y UPLOAD @$v_lct $file

if [ $? != 0 ];then

echo "ERROR - Failed while uploading $file" 

else

echo "Uploaded $file successfully " 

fi

done

 

• Copy the above script and save this as Sandeep.sh
• $Dos2unix Sandeep.sh
• Place the source LDT files and Sandeep.sh
• $sh Sandeep.sh

 

How to get JDBC Connection details

In EBS JDBC connection are required while migrating XML files to other environments. XML files are source for XML publisher reports and OAF personalization.

Like FNDLOAD Oracle has provided JPXimporter and XDOloder java utilities to inport XML data into standard database tables. Java utilities require JDBC connection in order to execute and hence below are 2 methods to get JDBC connection.

 

 

Method 1 > Using Database

Parameters	Oralce Functions
Host Name/IP Address	UTL_INADDR.get_host_address
SID	sys_context('userenv','instance_name')
Port	Hard Code

 

 

 

Method 2 > Using context files

Server has context file to store all context details. One can connect to server and extract the jdbc connection string using unix command.

 

lv_contxtfile=`echo $CONTEXT_FILE`

echo "CONTEXT_FILE  : $lv_contxtfile"

lv_jdbc=`grep '</jdbc_url>' $lv_contxtfile | cut -d "@" -f2 | cut -d "<" -f1`

echo "JDBC COnnection String : $lv_jdbc"

 

o    Echo $CONTEXT_FILE will list the context file name with full path

o    Grep will search string “</jdbc_url>” in context file and returned line will be cut from literal “@”. –f2 will return the right side portion from “@”.

o     Which will be cut further with literal “<” and -f1 will return the left side portion now.

o The final output should be complete JDBC_STRING, which can be used.

 

 

** Thanks to my colleagues Srini & Manish for above info **

 
 

SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility

 

SELECT

 a.application_short_name,

  a.data_source_code,

  b.lob_code,

  b.lob_type,

  b.file_name,

  b.last_update_date

FROM

   XDO_DS_DEFINITIONS_VL a,

   XDO_LOBS b

WHERE a.data_source_code    =b.lob_code

AND a.application_short_name=’XXXXX’

AND a.data_source_code      =:ConCurrentProgram or Data Defintion Code;

;

 
 

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.