How to Use HOST as executable in Oracle apps / Send Mail to Business via mailx utility

How to send mail in oracle : Using Mailx utility : Using HOST as executable

UNIX is the most widely used Operating system for Oracle Apps Implementation. Shell script is default programming language for UNIX. Simply we can say, UNIX host based concurrent program is actually a shell script. Unix host based concurrent program executes all command written in shell script and returns control back to the concurrent manager with the exit code.

Exit code in Unix concurrent program:

§  = 0 – program completed successfully

§  > 0 – program completed in error

Create a UNIX concurrent program which emails a file from a location or concurrent program output to a user using mailx utility Concurrent program parameters,

From
To
CC
BCC
Subject
File Location

Table of Contents

·         Pre-Requisite

·         Step1- Create shell script(.prog)

·         Step2- FTP this file to CUSTOM_TOP/bin path

·         Step3: create a soft link to FNDSPESR

·         Step4: register executable

·         Step 5: register concurrent program

Pre-requisite

You should have a working knowledge of Oracle Apps and Basic knowledge of Unix command.

Software/Hardware environment

§  Oracle apps 11i or R12

§  Putty

§  Winscp

Steps To Register Unix Shell Script As A Concurrent Program

Step 1 – Create shell script (. prog)

Let’s create an executable file first. It is a text file with .prog extension. As per Oracle Apps Developer Guide, the extension should be .prog.

In Unix based concurrent program, first 5 parameters from $0 to $4 are reserved and used by Oracle to pass below information to the shell script. User-defined parameters start from $5 onwards.

Note: This is applicable when there exists a link to fndscpr file.

 

— Standard Parameter—

Below parameters from $0 to $4 are reserved to hold information.

$0: Shell script to be executed
$1: Oracle user/password
$2: Applications user_id
$3: Application user_name
$4: Concurrent program request_id

— User-defined parameters—-

$5 Onwards are the custom parameters.

$5: From
$6: To
$7: CC
$8: BCC
$9: Subject
$10: File Path

Note: some Unix operating system you need to use curly brackets when parameter numbers are two digits {$10} or use Unix SHIFT command to shift parameter to $9 position. Check file below for to understand shift command.

# Name:- xxhost_conc_demo.prog

# Description:- Demo script to send email

# parameters

#  From

#  To

#  CC

#  BCC

#  Subject

#  File Name

echo "print reserved parameter"

echo "0 - Shell Script " $0

echo "1 - Oracle user/password" $1

echo "2 - Applications user id" $2

echo "3 - Application user name" $3

echo "4 - Concurrent program request id" $4

echo

echo "print custom parameter"

echo "5 - From" $5

FROM=$5

echo "6 - To" $6

TO=$6

echo "7 - CC" $7

CC=$7

echo "8 - BCC" $8

BCC=$8

echo "9 - Subject" $9

SUB=$9

shift                     # this shift 10th parameter to 9th position

echo "10 -File Name" $9   # referring 10th parameter as 9th

FILE=$9

# send email using mailx command

echo "Testing Unix Host Based concurernt Program" | mailx -s $SUB -c $CC -b $BCC -r $FROM -a $FILE $TO

Step 2 – FTP this file to $XXCUSTOM_TOP/bin path

FTP this script using WinSCP or FILEZILLA to respective $XXCUSTOM_TOP/bin. Host program executable file always put under /bin directory. Make sure encoding of the script should be in Unix format else you will get an error while running the script 

Login to Unix Server using putty and change file permission to 755 using below command,

chmod 755 xxhost_conc_demo.prog

Above step is required to avoid file permission error, else you will get FND-CP-ESP: Child: exec:: Permission denied error. It happens because Oracle Unix user cannot execute the script.

FND-CP-ESP: Child: exec:: Permission denied
/atech/app/xxcust/12.0.0/bin/xxhost_conc_demo
Program exited with status 1

Step 3 – Create a soft link to FNDCPESR

All parameters to shell script are passed as a single concatenated string. You need to either parse and split these parameters using SED, CUT commands or create a soft link to FNDCPER program. This is a standard utility by the oracle which parses parameter passed to the Unix program from the concurrent program and properly segregates them as $0,$1 .. $n.

ln -s $FND_TOP/bin/fndcpesr  xxhost_conc_demo

Do not include .prog extension.

Step 4 – Register executable

Register executable for the program as shown in below image. Navigation Application Developer–> Define–> Executable.

Execution Method: Host

Execution File Name: xxhost_conc_demo ( name of file without .prog extension)

Step 5 – Register Concurrent Program

Navigate and register a concurrent program. Select executable name defined in the previous step.

Sample Log File

Below if log file which will help you to understand the how parameters are passed.

+—————————————————————————+

Current system time is 25-OCT-2017 03:51:03

+—————————————————————————+

print reserved parameter
0 – Shell Script /atech/app/xxcust/12.0.0/bin/xxhost_conc_demo
1 – Oracle user/password APPS/apps
2 – Applications user id 45878
3 – Application user name SHOKEEN
4 – Concurrent program request id 90315090

print custom parameter
5 – From vivek@mydemoserver
6 – To shokeen@gmail.com
7 – CC shokeen@gmail.com
8 – BCC ashokeen@gmail.com
9 – Subject Testing
10 -File Name /home/shokeen/sample_file

+—————————————————————————+
No completion options were requested.