Oracle EBS PL/SQL Unix Concepts

Monday, January 25, 2016

Payment details query in R12 EBS

SELECT ipa.payee_name supplier_name,
ipa.payee_supplier_number supplier_number,
ipa.payee_supplier_site_name supplier_site,
hou.name ou_name,
ipa.payment_process_request_name ppr_name,
aisc.creation_date creation_date,
ipa.payment_status payment_status,
ipa.payment_amount payment_amount_batch,
ipa.payment_currency_code payment_currency_code_batch,
ipa.payment_method_code payment_method,
ipa.payment_reference_number payment_refernce_number,
ipa.payment_profile_sys_name payment_process_profile,
ipa.int_bank_account_name int_bank_account_name,
ipa.int_bank_account_number int_bank_account_number,
apt.template_name template_name,
idpa.calling_app_doc_ref_number invoice_number,
idpa.payment_amount payment_amount,
idpa.payment_currency_code payment_currency,
ipa.payment_date payment_date,
ipa.ext_bank_name supplier_bank_name,
ipa.ext_bank_number supplier_bank_number,
ipa.ext_eft_swift_code eft_number,
ipa.ext_bank_branch_name bank_branch_name,
ipa.ext_branch_number bank_branch_number,
ipa.ext_bank_account_name bank_account_name,
ipa.ext_bank_account_number bank_acount_number,
ipa.ext_bank_account_type account_type,
ipa.external_bank_account_id,
isppb.OUTBOUND_PMT_FILE_DIRECTORY, isppb.OUTBOUND_PMT_FILE_EXTENSION,
isppb.OUTBOUND_PMT_FILE_PREFIX,isppb.POSITIVE_PAY_FILE_DIRECTORY,isppb.POSITIVE_PAY_FILE_PREFIX
FROM iby_payments_all ipa,
hr_operating_units hou,
ap_inv_selection_criteria_all aisc,
ap_payment_templates apt,
iby_docs_payable_all idpa,
IBY_SYS_PMT_PROFILES_b isppb,
IBY_SYS_PMT_PROFILES_tl ispp
WHERE 1 = 1
AND ipa.org_id = hou.organization_id
AND ipa.payment_process_request_name = aisc.checkrun_name
AND aisc.template_id = apt.template_id(+)
AND ipa.payment_id = idpa.payment_id
AND ipa.Payment_status IN ('FORMATTED', 'ISSUED')
--AND ipa.paper_document_number IS NULL
AND ipa.process_type = 'STANDARD'
AND TRUNC(ipa.creation_date) >= TO_DATE('18/12/2015', 'DD/MM/YYYY')
AND ispp.language='US'
AND isppb.SYSTEM_PROFILE_CODE=ispp.SYSTEM_PROFILE_CODE
and ipa.payment_profile_sys_name=ispp.SYSTEM_PROFILE_NAME
ORDER BY aisc.creation_date

Tuesday, June 4, 2013

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