Pages

Recently Viewed

Saturday, March 16, 2013

Generic UNIX script to import OAF personalizations

Below is the piece of UNIX which will import OAF personalization to system.  This is a common script and will work for all CO, VO extension or other OAF personalization.
 
 
# ***********************************************************************************************************
#  Import OAF Personalizations
# ***********************************************************************************************************
echo "# ******************************************************************************" >> $v_log
echo "# Import OAF Personalizations" >> $v_log
echo "# ******************************************************************************" >> $v_log
echo ""
echo "Immporting OAF Personalizations...."
 
########### Import CO Extension & Other Personalizations ################################
for file in `find unzipped_folder | grep PG.xml`
do
                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`
                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`
                                v_MDS_RN=`echo $JAVA_TOP/$lv`
                                v_rootPackage=`echo $lv | sed s/" unzipped_folder "/"@"/g | cut -d "@" -f2`
                                                               
                                echo "File Name : $v_file_name" >>$v_log
                echo "lv :: $lv" >>$v_log
                                echo "MDS_RN : $v_MDS_RN" >>$v_log
                                echo "Root Package : $v_rootPackage" >>$v_log
     
                                 java oracle.jrad.tools.xml.importer.XMLImporter ${v_MDS_RN}${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" -rootdir ${v_MDS_RN} -rootPackage $v_rootPackage >>$v_log
 
                                if [ $? != 0 ];then
                                                echo "ERROR - Failed while importing $file"   >>$v_log
                                                echo "ERROR - Failed while importing $file"  
                                else
                                                echo "Import $file successfully "  >>$v_log
                                fi
                                echo " " >> $v_log
done
 
 
########### Import VO Extension################################
for file in `find xxxxx | grep .jpx`
do
                                v_file_name=`echo $file | rev | cut -d "/" -f1 | rev`
                                lv=`echo $file | sed s/$v_file_name/"@"/g | cut -d "@" -f1`
                                v_MDS_RN=`echo $JAVA_TOP/$lv`
                               
                                                               
                                echo "File Name : $v_file_name" >>$v_log
                                echo "lv :: $lv" >>$v_log
                                echo "MDS_RN : $v_MDS_RN" >>$v_log
                                      
                                 java oracle.jrad.tools.xml.importer.JPXImporter ${v_MDS_RN}/${v_file_name} -username apps -password $v_apps_schema -dbconnection "$lv_jdbc" >>$v_log
 
                                if [ $? != 0 ];then
                                                echo "ERROR - Failed while importing $file"   >>$v_log
                                                echo "ERROR - Failed while importing $file"  
                                else
                                                echo "Import $file successfully "  >>$v_log
                                fi
                                echo " " >> $v_log
done
fi
# ******************************************************************************
 
 
 
Prerequisite is to place all java,class & XML files at correct folder path. For example if we are extending the agreement CO.  File structures should be –
 
xxxx.oracle.apps.po.documents.agreement.webui
*.class
*.java
xxxx.oracle.apps.po.documents.agreement.webui.customization.site/responsibility.0/responsibility_id
*.xml
 

 

 

Sunday, March 3, 2013

FNDLOAD+

FNDLOAD+ is extended (customized) standard FNDLOAD utility to upload LDT files.
 
How to use -
  • Login to server
  • Go to any directory (/staging/Sandeep)
  • Place all your LDT files
  • Call FNDLOAD+
 
User Inputs -
 FNDLOAD+ will ask for Apps DB password
 
Syntax-
[Unix server sandeep] $ FNDLOAD+
 
What is  +  in this?
  • Not required to use complete FNDLOAD syntax
  • Not required to know the LCT file name for upload
  • Single run is required to upload all LDT files available in current dir
 
 
How to get FNDLOAD+ available in my environments –
  • Check the $PATH variable       (echo $PATH)
  • Go to any of the defined dir  (cd ….)
  • Create file Sandeep.sh
  • Create FNDLOAD+ (cp Sandeep.sh FNDLOAD+)                                                              
***** Only to UPLOAD ldt files*************


 
 

Saturday, March 2, 2013

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;
;

 

 

Saturday, February 16, 2013

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
 

Saturday, December 1, 2012

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
 
 
 
 

Thursday, November 29, 2012

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.
 
 
 
 

Sunday, November 25, 2012

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.