Oracle HRMS : Unix shell scripting

Host Program

In Oracle APPS, we can execute Unix shell script by running Concurrent Program.

Basically the Unix file has a ".sh" Extension and we directly execute this Unix script in Putty,
but here in Oracle apps the Unix file has ".prog" Extension and we execute this Unix script as Concurrent program. We call this Concurrent Program as HOST based Program

Basic Steps to Create Host Concurrent Program
 1. Create a "XX_TEST_HOST_PROG.prog" File
 2. Move the File to Server in any Custom TOP "bin" Folder (Tools Winscp / Filezilla)
 3. Execute the Below commands sequentially
     3.1 Putty> cd $XXCUSTOM_TOP/bin
         -- Here the Custom TOP is the File Location TOP 
     3.2 Putty> dos2unix XX_TEST_HOST_PROG.prog
3.3 chmod 755 XX_TEST_HOST_PROG.prog
3.4 Putty> ln -s $FND_TOP/bin/fndcpesr XX_TEST_HOST_PROG
          -- Here the File name with out .prog Extension
          -- We are creating the Softlink to our File
3.5 Add continue to create concurrent program Executable and Program but the Executable type is "HOST"

Note: The symbolic link should be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.

#=======================================================================
# Program	: XX_TEST_HOST_PROG.prog 
# Description   : Host program example to print $0 to $4 parameters
# Author    	: Saurabh Shrivastava
#=======================================================================
#
#Parameters 0 to 4 are default parameters and values are 
#passed from Concurrent manager
#
echo "0 Shell script name along with Path 	: " ${0}
echo "1 Oracle Apps User Name and Password 	: " ${1}
echo "2 Application user_id from FND_USER 	: " ${2}
echo "3 Application user_name from FND_USER     : " ${3}
echo "4 Concurrent Program Request ID	        : " ${4}
#
#Parameters from 5 are concurrent program parameter
#
echo " "
echo " "
echo "5 Concurrent Program Parameter 1 	    : " ${5}
 
#End of script

Note: It is always suggestible to access parameters in this format ${0} rather than $0. The reason behind this is, the shell script wont recognize double-digit parameter when accessed in this format $10, so we need to use curly brackets  as shown ${10}

Why should we create host program executable file with .prog extension?

We can even a create the executable file with .sh extension but the concurrent manager will not pass any default parameters. Concurrent manager will pass default parameters only to the host program executable with .prog extension. Below are 5 default parameters of a host program, whose values are passed by concurrent manager.

Why we need to create the symbolic link to fndcpesr ?

fndcpesr parses the arguments passed to the shell script. So if you don’t create the symbolic link to fndcpesr while registering the program, parameter values will not be parsed and passed automatically to the variables $0, $1, $2 etc. You will have to parse the individual parameters yourself.

fndcpesr is a standard utility available in $FND_TOP directory. It is mainly used by the application to parse the above four arguments to the shell scripts.

How to set return codes (error and warning) for host concurrent program ?

If the script traps an error, use the UNIX exit command ‘exit 1’ to return failure (status code 1) to the Concurrent Manager running the program. Of course, code sections after the exit command will not be executed.

1 2 3 4 5 6 7 8 9 10 11 12 13

#!/bin/sh echo 'Printing parameters....' echo '0:'$0 echo '1:'$1 echo '2:'$2 echo '3:'$3 echo '4:'$4 echo '5:'$5   exit 1   echo 'FCPLOGIN:'$FCP_LOGIN echo 'Finished printing parameters.' The concurrent program will complete with status ‘Error’ and the log file will contain the following: Printing parameters…. 0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog 1:APPS/APPS 2:1001530 3:EBUSINESS 4:2721405 5:ABCDEF /u01/oracle/visappl/cs/11.5.0/bin/myscr Program exited with status 1 There are no defined exit commands to return a warning status. However, it can be done by using the FND_CONCURRENT.SET_COMPLETION_STATUS API  to set the completion status of the request to ‘Warning’. Gareth Roberts deserves a big ‘Thank You’ for posting this on Oracle Forums. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 #!/bin/sh echo 'Printing parameters....' echo '0:'$0 echo '1:'$1 echo '2:'$2 echo '3:'$3 echo '4:'$4 echo '5:'$5   MYSTATUS=`sqlplus -s $1 <<! SET HEADING FEEDBACK OFF PAGESIZE 0 declare l_result boolean; l_session_id number; begin fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1); l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.'); commit; end; / exit; !`   echo 'FCPLOGIN:'$FCP_LOGIN echo 'Finished printing parameters.' This solution makes use of a SQL script to initialize a session with the request_id of the concurrent program using FND_GLOBAL.INITIALIZE and then sets the completion status. Upon execution, the concurrent program ends with a ‘Warning’ status and generates the following output: Printing parameters…. 0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog 1:APPS/APPS 2:1001530 3:EBUSINESS 4:2721408 5:ABCDEF FCPLOGIN:APPS/APPS Finished printing parameters/ How to hide password in the log file ? One important thing to notice is that echoing the parameters $1 and $FCP_LOGIN leads to the Oracle user/password being written to the log file. This can be prevented by using the options ENCRYPT and SECURE while defining the concurrent program. ENCRYPT signals the Concurrent Manager to pass the Oracle password in the environment variable FCP_LOGIN. The Concurrent Manager leaves the password in the argument $1 blank. To prevent the password from being passed, enter SECURE in the Execution Options field. With this change, Concurrent Manager does not pass the password to the program. For this example specifying SECURE in the concurrent program options: and then running the concurrent program does not set the completion status to ‘Warning’ since the Oracle user/password is not passed and the SQL script cannot run. This can be observed from the contents of the log file. Printing parameters…. 0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog 1:APPS/ 2:1001530 3:EBUSINESS 4:2721412 5:ABCDEF FCPLOGIN: Finished printing parameters. If we set the options field in the concurrent program to ENCRYPT then the Oracle user/password will be passed only to $FCP_LOGIN and not to $1. We can change the SQL  script to use $FCP_LOGIN instead of $1 and execute the concurrent program. It will now complete with a ‘Warning’ status since the Oracle user/password was passed to the script through $FCP_LOGIN. This can be verified from the contents of the log file: Printing parameters…. 0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog 1:APPS/ 2:1001530 3:EBUSINESS 4:2721409 5:ABCDEF FCPLOGIN:APPS/APPS Finished printing parameter. SAMPLE HOST FILE OR SHELL SCRIPT FOR SQL LOADER: Create XX_JOB_HOST_PROG.prog with following content and deploy it in oracle server. DATAFILE=$5 DIR_NAME=$6 LOG_BAD_PATH=$7 CTRLFILE=$8 LOG_BAD_PATH_LOG=$9 LOG_BAD_PATH_BAD=${10} LOG_BAD_PATH_DSC=${11} APPLTOP=$DIR_NAME/bin FILE_NAME=${LOG_BAD_PATH}/${DATAFILE}.csv LOGFILE=${LOG_BAD_PATH_LOG}/${CTRLFILE}.log BADFILE=${LOG_BAD_PATH_BAD}/${DATAFILE}.bad DISCARDFILE=${LOG_BAD_PATH_DSC}/${DATAFILE}.dsc echo "****************************************************************************" echo "Parameters for SQL Loader Process " echo " " echo "Control File:" $APPLTOP/$CTRLFILE echo "Data File:" $FILE_NAME echo "Log File:" $LOGFILE echo "Bad File:" $BADFILE echo "Discard File:" $DISCARDFILE echo "****************************************************************************" echo "Executing the SQL Loader Process.." sqlldr userid=apps/apps@ebs12 control=${APPLTOP}/${CTRLFILE}.ctl data=${FILE_NAME} log=${LOGFILE} bad=${BADFILE} discard=${DISCARDFILE} errors=100000 echo "****************************************************************************" echo "Process Complete - Exiting" exit 0