SFTP Process & SUDO command in APPS Shell Code

sftp process :

Create batch file which is executed in remote machine

Batch name : batch_file contents are

        lcd .
        $lv_pre_option
        $lv_ftp_cmd ${lv_file_name}.${timestamp_val}.gpg

3.Execute the batch

         sftp batch_file “remoteHostUser@remotHostName”

sudo command :

                     In Oracle Apps some of the special software(gpg encryption,decryption,sftp process) installed in specific user. So if you want to use those software we need to add software installed user as sudo user.

 sudo  -u “username” –H “barchName”

barchName – this batch is executed in other system. which holds some of the command to be executed in sudo user

WITHOUT USING PASSWORD WE CAN RUN THE UNIX COMMAND IN OTHER USER SYSTEM.

PDF Related UNIX Commands

Removing particular days before created files here the day is consider as 30

find "$from_path/" -maxdepth 1 -name "$FILENAME_PREFIX*" -mtime +30 -exec rm -rf {} \;

Merging multiple pdf into single pdf

gs -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=finalcombinedpdf.pdf -dBATCH <file1.pdf> <file2.pdf>

Decrypt the password protected pdf file

pdftk "FILENAME" input_pw "DOCPASSWORD" output "NewDoc.pdf"

Print the pdf in printer

acroread -toPostScript -shrink -size a4 -landscape |lp -d "PRINTERNAME" "NewDoc.pdf“

Sending EMAIL & MAIL Attachment from Shell Script/Unix Command

Sending mail without attachment

echo "Success Mail content" | mailx -s "Test mail" tgkq36@gmail.com

Sending mail with single attachment

uuencode <file.pdf>  <file.pdf> | mailx –s “Test Mail” tgkq36@gmail.com  <  body_content_file

Sending mail with multiple  attachment

uuencode <file.pdf > <sample1.pdf>    > data
uuencode <file2.pdf>  <sample2.pdf >  >> data
echo “Success “ >> data

Change the sender/from mail address

mailx –s “Test Mail” tgkq36@gmail.com -- -r “sendearmailaddress” ~c “ccaddres” < data

Mutt options :

mutt -s "Test mail" -a <NewDoc.pdf>  tgkq36@gmail.com < body_content_file

b address  (bcc)
c address (cc)
Note : multiple mail address separated by comma

SPOOL Process in Shell Script

spoolFunc()
{
        sqlplus -s /nolog <<!!|grep -v 'Connected'
        connect ${FCP_LOGIN};
        set colsep "|"
        set term off
        set feedback off
        set HEADING off
        set pagesize 0
        set verify off
        SET echo off
        SPOOL $MSIAP_TOP/bin/success.csv;
        SELECT 'ROLLNO|'||'NAME|'||'MARKS' from dual;
        select * from student;
        Spool OFF;
        Exit;
/!!
 }
spoolFunc

TELNET Process in Shell Script

 export FSS_DEST='host_name'
 export FTP_USER='username'
 export FTP_PWD='password‘
 (  echo open $FSS_DEST
    sleep 5
    echo $FTP_USER
    sleep 3
    echo $FTP_PWD
    sleep 3
    echo 'cd $directory_name‘
    echo "chmod 777 filename“
    echo 'some more output, etc.'
    echo 'exit'
) | telnet > /dev/null

FTP Process in Shell Script

 echo -e "user $user_id $password
      ascii
      prompt
      $source_dir
      lcd ${DATA_PATH}
      $ftp_command $process_file
      bye“ > ftpfile
  ftp -n "$remote_server" < ftpfile 2>&1 1> $FTPLOG
  if (grep "^Login incorrect." $FTPLOG)
     then
        echo "ERROR LOGIN INCORRECT"
  fi
  if (grep "^Not connected." $FTPLOG)
     then
        echo "ERROR FTP CONNECT"
  fi
  if (grep "No such file" $FTPLOG)
     then
        echo "ERROR FTP NO SUCH FILE"
  fi
  if (grep "access denied" $FTPLOG )
  then
        echo "ERROR FTP ACCESS DENIED"
  fi
  if (grep "^Please login" $FTPLOG )
  then
        echo "ERROR FTP LOGIN"
  fi

SQL Loader program in Shell Script

1. Check the control file is present
2. Check the data file present
3. Check the data file size is zero or not
4. Put dos2unix <filename> is used to remove newline character and control character , this is optional
5. Load the data into custom table using sql loader

Syntax :
sqlldr “db_user/db_passwd” control="controlfile path".ctl log="logfilepath".log bad="badfile path".bad  data="data file path“
Example :

sqlldr ${FCP_LOGIN} control="controlfile.ctl" log="logfile.log" bad="badfile.bad"  data="datafile“

Note : bad file is created only data file having invalid data content

Call sql files in Shell Script

Need to check the parameter (if parameter is passing to sql file)

Syntax

 sqlplus ${FCP_LOGIN} @’SQLFILE_NAME’ p1 p2

Check the return code status

sql file must end with exit statement

Example :

sqlplus ${FCP_LOGIN} @/sql/sample.sql

If [ “$?” = 0 ] ; then
   echo –e “sql statement is successfully executed”
fi

Get the values from Value Set in Shell Script in R12

getPathInfo()
{
                sqlplus -s /nolog <<!!|grep -v 'Connected'
                connect ${FCP_LOGIN};
                SET HEADING OFF;
                SET FEEDBACK OFF;
                SET LINESIZE 3000;
                SET SERVEROUTPUT ON SIZE 1000000;
                BEGIN
                DECLARE
                         value1                VARCHAR2(250) := '';
                         value2                VARCHAR2(250) := '';
                         BEGIN
                             BEGIN
                                   SELECT '10','20'
                                   INTO    value1 ,value2
                                   FROM dual;
                             EXCEPTION
                                  WHEN NO_DATA_FOUND  THEN
                                        DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
                                  WHEN OTHERS THEN
                                        DBMS_OUTPUT.PUT_LINE('ERROR');
                             END;

                              DBMS_OUTPUT.PUT_LINE(value1||'#'||value2);
                     END;
                END;
/
!!
}
value=`getPathInfo`
x=`echo "$value"|cut -d# -f1`
y=`echo "$value"|cut -d# -f2`
echo "x : $x“
echo "y : $y“ 

Output :

x : 10
y : 20

Note : change the select query to fetch the valueset values

Validate Directories and Files- Check file size is zero or not

crontab - Schedule the process in Unix

• Cron job are used to schedule commands to be executed periodically. 
• can setup set up commands or scripts, which will repeatedly run at a set time. 
• The cron service (daemon) runs in the background and constantly checks the /etc/crontab file, 
• To edit your crontab file, type the following command at the UNIX / Linux shell prompt:  $ crontab -e

Example :

     1 2 3 4 5 /root/email_notifications.sh  argument1 argument2

Where

• 1 Minutes (0-59)
• 2 Hours (0-23)
• 3 Day of Month (1-31)
• 4 Month (1-12)
• 5 Day of Week (0-7) Sunday=0

Run the invoice program in working hours only

00 09-18 * * 1-5 invoice_sh "Customer_Number"

Run the program concurrent_fail_list_sh in every 10 mins

*/10 * * * * concurrent_fail_list_sh

Run the program customer_info_sh for every mins

* * * * *  customer_info_sh

Special Key words

Keyword	Equivalent
@yearly	0 0 1 1 *
@daily	0 0 * * *
@hourly	0 * * * *
@reboot	Run at startup.

Run the program customer_info_sh yearly once

@yearly  customer_info_sh

Run the program customer_info_sh daily once

@daily  customer_info_sh

Run the program customer_info_sh hourly once

@hourly  customer_info_sh

Run the program customer_info_sh after system boot up

@reboot  customer_info_sh

List the scheduled jobs for particular user

$crontab -u username -l

Add the new job in crontab use the below commands

$crontab -e

Create SQL*Loader Concurrent Program in R12

SQL*Loader Usage:
                                   SQL LOADER is an Oracle utility used to load data into table given a data file which has the records that need to be loaded. SQL*Loader takes  data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log file, bad file or reject file, discard file.


Log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. This information can be used to resume the load where it left off.

 Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.

Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

Structure of the data file:
1.Data file can be in fixed record format
2.Variable record format.

 Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

INV100AMOUNT3000ADDRCHENNAI
INV102AMOUNT5000ADDRCHENNAI

Control file example :



LOAD DATA
INFILE '$FILE_NAME'
APPEND
INTO TABLE student
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( STUDENT_ID                POSITION(1:6)   INTEGER,
  STUDENT_NAME        POSITION(7:15)  CHAR,
  ADDRESS                      POSITION(15:20) CHAR,
  MARKS                          POSITION(20:40) INTEGER
 )


Variable record format : Data file having the delimiter based values.

Create Table : student


CREATE TABLE student(student_id   NUMBER,
                     student_name VARCHAR2(50),
                     address      VARCHAR2(100),
                     marks        NUMBER
                     )

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications R12

Step 1 : Create the Executable name SQLLDR_TEST_PROGRAM with Executable file Name as sqlldr_demo_prog

Step 2 : Register the concurrent program Demo SQL*Loader Concurrent Program

Step 3 : Adding the parameter in the concurrent program

Step 4:  Add the concurrent program in to the request group & Add that request group into the Responsibility.

Create the control file sqlldr_demo_prog.ctl under $CUSTOM_TOP/bin directory

LOAD DATA

INFILE '$FILE_NAME'

APPEND

INTO TABLE student

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

( STUDENT_ID                    DECIMAL EXTERNAL,

  STUDENT_NAME            CHAR,

  ADDRESS                          CHAR,

  MARKS                              DECIMAL EXTERNAL

 )

$FILE_NAME --> We can hard code the file name with full directory path else we can pass the file name with file location as parameter. 

Note : We can put it OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

Place the data file sqlldr_data_file.dat under under /u06/app/applmgr/dvoa044/oc/1.0.0/bin directory

1000|Chidambaram|Chennai|98

1001|Ram|Bangalore|67

1002|Kamal|Chennai|89

1003|Preethi|Hyderabad|90

Note : can use any directory for data file path.

Step 5: Submit the concurrent program Demo SQL*Loader Concurrent Program

from Added responsibility

Program should be completed in normal and out put parameter values are displayed in log file.

Log Information :

Control File:   /u06/app/applmgr/dvoa044/oc/1.0.0/bin/sqlldr_demo_prog.ctl

Data File:      /u06/app/applmgr/dvoa044/oc/1.0.0/bin/sqlldr_data_file.dat

  Bad File:     /u06/app/applmgr/dvoa044/out/o55553058.out

  Discard File: /u06/app/applmgr/dvoa044/out/o55553058.out 

 (Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 65536 bytes

Continuation:    none specified

Path used:      Conventional

Table STUDENT, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

STUDENT_ID                          FIRST     *   |       CHARACTER            

STUDENT_NAME                  NEXT     *   |       CHARACTER            

ADDRESS                                NEXT     *   |       CHARACTER            

MARKS                                   NEXT     *   |       CHARACTER            

Table STUDENT:

  4 Rows successfully loaded.

  0 Rows not loaded due to data errors.

  0 Rows not loaded because all WHEN clauses were failed.

  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  65016 bytes(63 rows)

Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:         0

Total logical records read:              4

Total logical records rejected:        0

Total logical records discarded:      0

Run began on Mon May 13 01:51:57 2013

Run ended on Mon May 13 01:51:58 2013

Elapsed time was:     00:00:00.81

CPU time was:         00:00:00.02    

Data's are successfully inserted into back-end table student

sqlldr used in unix code

The below code can be used in unix shell script.

sqlldr ${FCP_LOGIN} control=$CUSTOM_PATH/bin/sqlldr_demo_prog.ctl log=sqlldr_data_file.log bad=sqlldr_data_file.bad  data=sqlldr_data_file.dat 

${FCP_LOGIN}  --> This is default credentials for APPS ,It provides Oracle Database User Name/Password@DataBase name, 

we can hardcode those values like USERID=apps/apps_passwd@apps_database

If the data file having invalid content then only bad file will be created otherwise bad file wont created.

Create Host/Shell Script Concurrent Program in R12

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications R12

Step 1 : Create the executable name HOST_TEST_PROGRAM and Executable File Name demo_sh

Step 2 : Register the concurrent program Demo Shell Script Concurrent Program

Options : encrypt it will avoid displaying apps password in unix command prompt
wont get the password using unix command ps -ef | grep "apps"

Step 3 : Adding two parameter in the concurrent program
             Parameter1  Parameter2

Step 4:  Add the concurrent program in to the request group & Add that request group into the Responsibility.

Step 5:

1.Create a shell script ( say demo.prog)  in appropriate $CUSTOM_TOP/bin directory
Script name : demo.prog

  #! /bin/ksh

  param1="$5"

  param2="$6"

  echo "Parameter1 : $param1"

  echo "Parameter2 : $param2"

2.Change Permissions to 755 for the demo.prog file. ($chmod 755 $CUSTOM_TOP/bin/demo.prog)

3.Create soft Link to the shell Script ( ln -s $FND_TOP/bin/fndcpesr   $CUSTOM_TOP/bin/demo_sh) to create demo_sh file.

4. First 5 params are reserved for Application, user defined params starts from 5th param

                      ORA_USER_PASS=$1

                     USERID=$2

                     USERNAME=$3

                     REQUESTID=$4

5. fndcpesr is a standard utility available in $FND_TOP directory. Its mainly used by the application to parse arguments for shell scripts.

Step 6:

Submit the concurrent program Demo Shell Script Concurrent Program

from Added responsibility

Program should be completed in normal and out put parameter values are displayed in log file.

Note : The above method should allow spaces in the user defined parameters