How to use Oracle v$ views in Shell Script

It is difficult to use v$ views in Shell script to fetch any Oracle Database internal data, due to the special character $ associated with the views.But it can be achieved by using an backspace.
Below example shows how the v$ views can be used to fetch the open cursors limit and current max cursors reached in the Oracle session.

#Variable declaration
V_FILE_NAME=”Open_Cursor_Check.txt”
V_SES_TAB=”v\$sesstat”
V_STAT_TAB=”v\$statname”
V_PARAM_TAB=”v\$parameter”
V_SEARCH_TXT1=”error”
V_USERID=$FNDNAM
V_LOGIN=$GWYUID
V_DATABASE=$USID
V_PASSWORD=$1

echo “———Open Cursor Limit Check———————-” >> ${V_FILE_NAME}

V_Q_OUTPUT=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}

V_HIGH_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $1}’)
echo ‘Max cursors opened so far : ‘$V_HIGH_OC >> ${V_FILE_NAME}

V_MAX_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $2}’)
echo ‘Max cursors allowed to be opened : ‘$V_MAX_OC >> ${V_FILE_NAME}

if [ $V_HIGH_OC == $V_MAX_OC ]
then
echo “Error : SQL Open Cursors maximum limit is reached” >> ${V_FILE_NAME}
else
echo “SQL Open Cursors has not reached max limit” >> ${V_FILE_NAME}
fi
echo >> ${V_FILE_NAME}

#EoF

Advertisements

Shell Script to Scan files for a text

The below shell script will be useful to scan a file for a specific text and write the content to a text/log file.The usecase here is to scan the Oracle EBS Concurrent Log file for Workflow Listener for any errors and extract the error lines, write them in a log file and mail them to the appropriate team to take any action.The script is ideal to be executed in Oracle EBS environment Unix Servers ,as some variables values are fetched from then environment.

V_CUR_DT_TIME=`date +”%Y%m%d%H%M%S”`
V_FILE_NAME_P=”Test_CP_Log_”
V_FILE_NAME=”$V_FILE_NAME_P$V_CUR_DT_TIME.txt”

echo “Workflow Listener Monitor Log File ” > ${V_FILE_NAME}
echo “Instance Name : ${USID}” >> ${V_FILE_NAME}
echo “Scanning Script Run Time : $(date)” >> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}

#Variable declaration
V_SEARCH_TXT1=”error”
V_USERID=$FNDNAM
V_LOGIN=$GWYUID
V_DATABASE=$USID
V_PASSWORD=$1
V_EMAIL_ID=$2

#SQL Query to fetch current hour of the script run
V_CUR_DT=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}

# SQL Query to fetch the Workflow Listener Log file path and name
V_LOG_FILE=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}

#Get line number in the file where the Current Hour appears for first time
V_ERR_LINE=$(awk -v x=”$V_CUR_DT” ‘$0~x {print NR;exit}’ ${V_LOG_FILE})

if [ -z “${V_ERR_LINE}” ]
then
echo “No log entries found to scan for errors. ” >> ${V_FILE_NAME}
else
V_ERR_CNT=$(tail -n +${V_ERR_LINE} ${V_LOG_FILE} | grep -ic ${V_SEARCH_TXT1})
fi

#Check if search text exists and write in a log file
if [ “${V_ERR_CNT}” -eq 0 ] || [ -z “${V_ERR_CNT}” ]
then
echo “No errors found in Workflow Business Event Listener log file” >> ${V_FILE_NAME}
else
echo >> ${V_FILE_NAME}
echo “Extract of errors and exceptions logged in Workflow Listener Log file” >> ${V_FILE_NAME}
echo >> ${V_FILE_NAME}
tail -n +${V_ERR_LINE} ${V_LOG_FILE} | grep -i ${V_SEARCH_TXT1} >> ${V_FILE_NAME}
fi
echo >> ${V_FILE_NAME}
echo “——————–End of File——————————–” >> ${V_FILE_NAME}

#Email the log file as attachment to recepients
(echo “Please find attached Workflow Business Event Listener error monitor log file for today’s run.Ignore this mail if the issues have been already fixed. – IT Support”;uuencode “${V_FILE_NAME}” “${V_FILE_NAME}”) | mailx -s “Workflow Listener Monitor Log : ${V_CUR_DT}” ${V_EMAIL_ID}

#EoF

List of Customer TCA APIs

If you are planning to do Customer Conversion using API in Oracle E-Business Suite R12, then you will be using the below mentioned brief list Trading Community Architecture(TCA), APIs.

Object Name Requirement
hz_party_v2pub                   – To create or update Customer Party details
fnd_msg_pub                       – To retrieve debug/error messages
hz_cust_account_v2pub      – To create or update Customer Account details
hz_customer_profile_v2pub – To create or update Customer Profile details
hz_location_v2pub               – To create or update Customer Location details
hz_party_site_v2pub            – To create or update Customer Party Site details
hz_cust_account_site_v2pub – To create or update Customer Account Site details
hz_contact_point_v2pub        – To create or update Customer Contact points
iby_ext_bankacct_pub           – To create or update Customer External Bank
iby_fndcpt_common_pub       – To assist create or update Bank & Bank Account creation
iby_fndcpt_setup_pub            – To link Customer Party with Bank Account
hz_extensibility_pub               – To creates or update Customer Party profile extension or UDA
hz_relationship_v2pub           – To create or update Customer Party Relationship

Multiparty link to single bank account in Oracle Apps

It’s a well known fact that in Oracle Apps’s AR module,we can link one Bank A/c with one Customer Account,.In R12 we can link more than one Bank A/c with a Customer.We can do this set up either from front end in Customer OAF Webpage or using APIs.This post will help you understand, how we can achieve this using Oracle IBY module’s APIs.

Before Linking :

Customer 1 linked to Bank 1:

Image

Customer 2 linked to Bank 2 :

Image

Bank Parties:

Image

Bank Accounts:

Image

Bank Account Owners:

Image

Pay Instruments :

Image

After 1st Bank Acct link to 2nd Customer Acct:

Image

Image

Image

Image

 

Code :

The first part is to link the 2nd Customer as a joint account owner of 1st Customer’s primary bank account and then link the bank with 2nd Customer.

 

Step 1 : Create Joint Owner
DECLARE

ln_party_id INTEGER :=null;
ln_acct_id INTEGER :=null;
x_joint_acct_id INTEGER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER(5);
x_msg_data VARCHAR2(3000);
x_response iby_fndcpt_common_pub.result_rec_type;
ln_count INTEGER;

BEGIN
mo_global.init(‘AR’);
mo_global.set_policy_context(‘S’, 100);
fnd_global.apps_initialize (19654,222,20678,0);
/*
Party ID Party Name
377688 Test Customer – 1
377694 Test Customer – 2

Bank Acct ID Bank Acct No
19131 111222333444
19132 555666777888
*/
ln_party_id:=377694;
ln_acct_id:=19131;

IBY_EXT_BANKACCT_PUB.add_joint_account_owner(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_bank_account_id => ln_acct_id,
p_acct_owner_party_id => ln_party_id,
x_joint_acct_owner_id => x_joint_acct_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
dbms_output.put_line(‘x_return_status = ‘ || x_return_status);
dbms_output.put_line(‘x_msg_count = ‘ || x_msg_count);
dbms_output.put_line(‘x_joint_acct_id = ‘ || x_joint_acct_id);
dbms_output.put_line(‘x_response.Result_Code = ‘ || x_response.result_code);
dbms_output.put_line(‘x_response.Result_Category = ‘|| x_response.result_category );
dbms_output.put_line(‘x_response.Result_Message = ‘|| x_response.result_message);

IF x_msg_count = 1 then
dbms_output.put_line(‘message count ‘||x_msg_count );
ELSIF x_msg_count > 1 THEN
LOOP
ln_count := ln_count + 1;
x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);

IF x_msg_data IS NULL THEN
EXIT;
END IF;

END LOOP;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error..’||SQLERRM);
END;
==========================================================================
Step 2 : Assign the account to Customer
DECLARE

ln_party_id number :=null;
ln_cust_account_id number :=null;
ln_acct_id number :=null;
x_assign_id number;
x_return_status VARCHAR2(2000);
x_msg_count number(5);
x_msg_data VARCHAR2(3000);
x_response iby_fndcpt_common_pub.result_rec_type;
lv_payer_context_rec iby_fndcpt_common_pub.PayerContext_rec_type;
lv_assignment_attribs iby_fndcpt_setup_pub.PmtInstrAssignment_rec_type;
ln_count NUMBER;

BEGIN
mo_global.init(‘AR’);
mo_global.set_policy_context(‘S’, 82);
fnd_global.apps_initialize (19654,222,20678,0);

/*
Party ID Party Name
377688 Test Customer – 1
377694 Test Customer – 2

Bank Acct ID Bank Acct No
19131 111222333444
19132 555666777888

*/

ln_party_id:=377694;
ln_cust_account_id:=68055;
ln_acct_id:=19131;

lv_payer_context_rec.payment_function := ‘CUSTOMER_PAYMENT’;
lv_payer_context_rec.party_id := ln_party_id;
lv_payer_context_rec.cust_account_id := ln_cust_account_id;
lv_assignment_attribs.instrument.instrument_type :=’BANKACCOUNT’;
lv_assignment_attribs.instrument.instrument_id := ln_acct_id;
lv_assignment_attribs.start_date := TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’);

SELECT NVL(max(ipiua.order_of_preference),0)+1
INTO lv_assignment_attribs.priority
FROM apps.iby_pmt_instr_uses_all ipiua,
apps.iby_external_payers_all iepa
WHERE iepa.ext_payer_id = ipiua.ext_pmt_party_id
AND iepa.party_id = ln_party_id
AND iepa.cust_account_id = ln_cust_account_id;

— map account to customer
IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => lv_payer_context_rec,
p_assignment_attribs => lv_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response
);

dbms_output.put_line(‘x_return_status = ‘ || x_return_status);
dbms_output.put_line(‘x_msg_count = ‘ || x_msg_count);
dbms_output.put_line(‘x_assign_id = ‘ || x_assign_id);
dbms_output.put_line(‘x_response.Result_Code = ‘ || x_response.result_code);
dbms_output.put_line(‘x_response.Result_Category = ‘|| x_response.result_category );
dbms_output.put_line(‘x_response.Result_Message = ‘|| x_response.result_message);

IF x_msg_count = 1 then
dbms_output.put_line(‘message count ‘||x_msg_count );
ELSIF x_msg_count > 1 THEN
LOOP
ln_count := ln_count + 1;
x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);

IF x_msg_data IS NULL THEN
EXIT;
END IF;

END LOOP;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error..’||SQLERRM);
END;

Default multi record data block using Custom.pll in Oracle Apps

In standard Oracle forms of Oracle Apps , we can assign default values in a single/multiple columns of a data block using forms personalization. But we cannot use personalization to default  different values in multiple record data blocks.

The requirement is to default different tax in India Localized PO’s goods receipt module and the code below explains how we can achieve this by Custom.pll.

As a best practice, it is always good to write the custom logic in a separate custom pll file and call the procedure in the specific event in Custom.pll.This helps in preserving our code,when oracle patches over writes Custom.pll file.

Code:

PACKAGE XX_PO_IL IS
Procedure event(event_name VARCHAR2);
END;

PACKAGE BODY XX_PO_IL IS
PROCEDURE EVENT(event_name VARCHAR2) IS
CURSOR c_il_tax(p_orgid VARCHAR2,p_cat_name VARCHAR2,p_boe_flg VARCHAR2) is
SELECT t.tax_id tax_id, tax_name tax_name,
            decode(nvl(vat_flag,’N’),’Y’,’VAT’,tax_type) tax_type_1,
            tax_rate, nvl(tax_amount,0) tax_amount, uom_code,
            modifiable_flag, currency_flag, mod_cr_percentage,
           vendor_flag, duty_drawback_percentage, tax_type,
           vendor_id, vendor_site_id, adhoc_flag ,vat_flag ,
           precedence_1, precedence_2, precedence_3,
           precedence_4, precedence_5, precedence_6,
           precedence_7, precedence_8, precedence_9,precedence_10
FROM apps.ja_in_tax_codes t,
           apps.ja_in_tax_category_lines cl
WHERE org_id = p_orgid
AND (end_date >= trunc(sysdate) or end_date is null)
AND start_date <= trunc(sysdate)
AND cl.tax_id=t.tax_id
AND EXISTS (SELECT 1 from ja_in_tax_categories c
WHERE cl.tax_category_id = c.tax_category_id
AND upper((c.tax_category_name))= p_cat_name)
AND ((p_boe_flg = ‘N’) or (p_boe_flg = ‘Y’ AND UPPER(tax_type) NOT IN
(‘CVD’, ‘ADDITIONAL_CVD’,’CVD_EDUCATION_CESS’, ‘CUSTOMS’, ‘CUSTOMS_EDUCATION_CESS’,’CVD_SH_EDU_CESS’,’CUSTOMS_SH_EDU_CESS’)))
ORDER BY cl.line_no;

form_name varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
func_name varchar2(30) := FND_FUNCTION.Current_Form_Function;
l_shiplineid varchar2(30) DEFAULT NULL;
l_fromorgid varchar2(30) DEFAULT NULL;
l_vendorid varchar2(30) DEFAULT NULL;
l_vendor varchar2(100)DEFAULT NULL;
l_org_id varchar2(30) DEFAULT NULL;
l_boe_flg varchar2(1) DEFAULT NULL;
l_trantype varchar2(30) DEFAULT NULL;
l_tax_curr varchar2(30) DEFAULT NULL;
l_rec_curr varchar2(30) DEFAULT NULL;
l_para_curr varchar2(30) DEFAULT NULL;
l_poh_id varchar2(30) DEFAULT NULL;
l_cat_name varchar2(30):=’FORM PERSONALIZATION’;
l_seqno number :=0;

BEGIN

IF (form_name = ‘JAINPORE’ and func_name=’JAINPORE_FUN’ and block_name = ‘JA_IN_TAX’ and event_name =’WHEN-NEW-BLOCK-INSTANCE’ and NAME_IN(‘SYSTEM.BLOCK_STATUS’)=’NEW’) THEN

l_shiplineid := NAME_IN (‘RECEIPT_LINES.SHIPMENT_LINE_ID’);
l_fromorgid := NAME_IN (‘RECEIPT_LINES.FROM_ORGANIZATION_ID’);
l_vendorid := NAME_IN (‘RECEIPT_LINES.VENDOR_ID’);
l_vendor := NAME_IN (‘RECEIPT_LINES.SUPPLIER’);
l_boe_flg := NVL(NAME_IN (‘RECEIPT_LINES.BOE_APPLIED_FLAG’),’N’);
l_trantype := NAME_IN (‘RECEIPT_LINES.TRANSACTION_TYPE’);
l_rec_curr := NAME_IN (‘RECEIPT_LINES.CURRENCY’);
l_poh_id := NAME_IN (‘RECEIPT_LINES.HEADER_ID_PO’);

IF XX_FORM_PERSONALIZATION_PKG.JAINPORE_VAL_MUL_TAX(l_shiplineid,l_fromorgid,l_vendorid) = ‘Y’ THEN — Custom validation

l_org_id := NAME_IN(‘PARAMETER.P_ORG_ID’);
l_para_curr := NAME_IN(‘PARAMETER.FUNC_CURR’);

BEGIN
IF l_trantype <> ‘UNORDERED’ THEN
l_tax_curr := l_rec_curr;
ELSE
BEGIN
SELECT currency_code
INTO l_tax_curr
FROM po_headers_all
WHERE po_header_id = l_poh_id;
EXCEPTION
WHEN OTHERS THEN
l_tax_curr:= NULL;
END;
END IF;
END;

FIRST_RECORD;
FOR i IN c_il_tax(l_org_id,l_cat_name,l_boe_flg) LOOP
l_seqno := NAME_IN(‘SYSTEM.TRIGGER_RECORD’);
COPY( TO_CHAR(l_seqno),’JA_IN_TAX.TAX_LINE_NO’);
COPY( TO_CHAR(i.TAX_ID),’JA_IN_TAX.TAX_ID’);
COPY( i.TAX_NAME,’JA_IN_TAX.TAX_NAME’);
COPY( i.TAX_TYPE_1,’JA_IN_TAX.TAX_TYPE_1′);
COPY( TO_CHAR(i.TAX_RATE),’JA_IN_TAX.TAX_RATE’);
COPY( TO_CHAR(i.TAX_AMOUNT),’JA_IN_TAX.TAX_AMOUNT’);
COPY( i.UOM_CODE,’JA_IN_TAX.UOM’);
COPY( NVL(l_tax_curr,l_para_curr),’JA_IN_TAX.CURRENCY’);
COPY( i.MODIFIABLE_FLAG,’JA_IN_TAX.MODIFIABLE_FLAG’);
COPY( i.CURRENCY_FLAG,’JA_IN_TAX.CURRENCY_FLAG’);
COPY( TO_CHAR(i.MOD_CR_PERCENTAGE),’JA_IN_TAX.MOD_CR_PERCENTAGE’);
COPY( i.VENDOR_FLAG,’JA_IN_TAX.VENDOR_FLAG’);
COPY( i.DUTY_DRAWBACK_PERCENTAGE,’JA_IN_TAX.DUTY_DRAWBACK_PERCENTAGE’);
COPY( i.TAX_TYPE,’JA_IN_TAX.TAX_TYPE’);

IF upper(i.tax_type) NOT IN
(‘CVD’,’CVD_EDUCATION_CESS’,’CUSTOMS’,’CUSTOMS_EDUCATION_CESS’,
‘TDS’,’CUSTOMS_SH_EDU_CESS’,’CVD_SH_EDU_CESS’) AND i.vendor_id IS NULL
THEN
COPY(NVL(l_vendorid,l_poh_id),’JA_IN_TAX.VENDOR_ID’);
COPY(l_vendor,’JA_IN_TAX.JA_IN_TAX.VENDOR_NAME’);
ELSIF
upper(i.tax_type) IN
(‘CVD’,’CVD_EDUCATION_CESS’,’CUSTOMS’,’CUSTOMS_EDUCATION_CESS’,’CUSTOMS_SH_EDU_CESS’,’CVD_SH_EDU_CESS’)
THEN
COPY(NULL,’JA_IN_TAX.VENDOR_ID’);
COPY(NULL,’JA_IN_TAX.JA_IN_TAX.VENDOR_NAME’);
END IF;

COPY( TO_CHAR(i.VENDOR_SITE_ID),’JA_IN_TAX.VENDOR_SITE_ID’);
COPY( i.ADHOC_FLAG,’JA_IN_TAX.ADHOC_FLAG’);
COPY( i.VAT_FLAG,’JA_IN_TAX.VAT_FLAG’);
COPY( TO_CHAR(i.PRECEDENCE_1),’JA_IN_TAX.PRECEDENCE_1′);
COPY( TO_CHAR(i.PRECEDENCE_2),’JA_IN_TAX.PRECEDENCE_2′);
COPY( TO_CHAR(i.PRECEDENCE_3),’JA_IN_TAX.PRECEDENCE_3′);
COPY( TO_CHAR(i.PRECEDENCE_4),’JA_IN_TAX.PRECEDENCE_4′);
COPY( TO_CHAR(i.PRECEDENCE_5),’JA_IN_TAX.PRECEDENCE_5′);
COPY( TO_CHAR(i.PRECEDENCE_6),’JA_IN_TAX.PRECEDENCE_6′);
COPY( TO_CHAR(i.PRECEDENCE_7),’JA_IN_TAX.PRECEDENCE_7′);
COPY( TO_CHAR(i.PRECEDENCE_8),’JA_IN_TAX.PRECEDENCE_8′);
COPY( TO_CHAR(i.PRECEDENCE_9),’JA_IN_TAX.PRECEDENCE_9′);
COPY( TO_CHAR(i.PRECEDENCE_10),’JA_IN_TAX.PRECEDENCE_10′);

NEXT_RECORD;
END LOOP;
FIRST_RECORD;
ELSE
NULL;
END IF;

ELSE
NULL;

END IF;
END EVENT;
END XX_PO_IL;

——————————————————-

Now , all we have to do is just call XX_PO_IL.EVENT(‘WHEN-NEW-BLOCK-INSTANCE’) in Custom.pll file.We can achieve similar requirements by Custom.pll, but care should be taken that all the correct required values be defaulted and the existing data flow in the form is not disturbed.It will need quite a good number of testing before going live.

Load multiple data files using SQL * Loader & Shell Script

If you happen to work in developing Interfaces & Conversions in Oracle Apps , you will often get requirements to load data files in .dat,.csv,.txt..etc formats.The most preferred option to load such files into database is using SQL * Loader.

Loading single file is easy but it becomes problematic to read data from multiple files or implementing an on-going interface where multiple files have to be read and loaded in database tables daily.In this post I will explain we can use shellscript to process multiple files in one go.

Code:

echo “******** Start of Script *******”

echo “Parameters received”
echo “Time : ” `date`
echo “Oracle UserID/Password : ” $1 — Do not forget to remove this line after testing
echo “Apps UserID : ” $2
echo “Apps UserName : ” $3
echo “Conc Request ID : ” $4
echo “5th parameter : ” $5
echo “6th parameter : ” $6
echo “7th parameter : ” $7
echo “8th parameter : ” $8

— Incoming parameters from Apps Concurrent Programs —
v_db_login=${1}
v_data_dir=${5}
v_ctl_dir=${6}
v_mail_list=${7}
v_from_mailid=${8}

— Removing old .bad and .log files —
rm -f ${v_data_dir}/log/*.log
rm -f ${v_data_dir}/bad/*.bad

— Replacing blank space in new data file’s name with ‘_’ —
for file in ${v_data_dir}/data/*.*;
do
 v_new_file=`echo “${file}”|sed “s/ /_/g”`
 mv -f “${file}” “${v_new_file}”
done

— Process each data file available in Data file location —
for file in ${v_data_dir}/data/*.*;
do
 v_data_file=` basename “$file”`;
 echo “File Name  ” “${v_data_file}”;

  case “${file}” in
  *.csv)
   v_file_name=` basename “${file}” .csv`;;
         *.txt)
   v_file_name=` basename “${file}” .txt`;;
         *.xls)
   v_file_name=` basename “${file}” .xls`;;
  esac

 echo “File name with no extn ” “${v_file_name}”

 sqlldr userid=${v_db_login} control=${v_ctl_dir}/TEST_LOADER.ctl data=${v_data_dir}/data/${v_data_file} log=${v_data_dir}/log/${v_file_name}.log bad=${v_data_dir}/bad/${v_file_name}.bad rows=100 skip=1
 
  — Updates the file name of the data being loaded in staging table —
  echo “Updating file name in staging table”
  v_ret_id=`sqlplus -s ${v_db_login} << eof
     UPDATE apps.test_data_stg
     SET file_name=TO_CHAR(‘${v_data_file}’)
     WHERE status = ‘N’
     AND bankfile_name IS NULL;
     COMMIT;
  exit;
  eof`
  echo “File name updated records: “${v_ret_id}

  — Mail the data loader log file to the mail receipients —
 if [ -f ${v_data_dir}/log/${v_file_name}.log ]
        then
  /usr/bin/uuencode ${v_data_dir}/log/${v_file_name}.log ${v_data_dir}/log/${v_file_name}.log | /usr/bin/mailx -s “Data Loader log file : ” -r ${v_from_mailid} ${v_mail_list}
  echo ${v_file_name}.log mailed to ${v_mail_list}
        fi

  — Check for bad file of data load and mail it to receipients —
 if [ -f ${v_data_dir}/bad/${v_file_name}.bad ]
        then

  echo “Deleting records for bad file from staging table”
  v_eret_id=`sqlplus -s ${v_db_login} << eof
     DELETE FROM apps.test_data_stg
     WHERE file_name=TO_CHAR(‘${v_data_file}’)
     AND status = ‘N’;
     COMMIT;
  exit;
  eof`
  echo “Number of records deleted for bad file : “${v_eret_id}

         /usr/bin/uuencode ${v_data_dir}/bad/${v_file_name}.bad ${v_data_dir}/bad/${v_file_name}.bad | /usr/bin/mailx -s “Data Loader bad file” -r ${v_from_mailid} ${v_mail_list}
  echo ${v_file_name}.bad mailed to ${v_mail_list}
       fi
 
 — Rename the processed data file and move to archive folder —
 v_date=”_”`date +”%F-%H%M%S”`
        mv -f ${v_data_dir}/data/${v_data_file} ${v_data_dir}/archive/${v_data_file}${v_date}
done

echo “******** End of Script *******”

The above script can read as many files as available,load the data into database tables,update the file name of the data being loaded, clean up the table if there are any bad records while loading for a specific file and also mail the log & bad files.You can customize the code further as per your need.

Zoom to Oracle Web Center from Oracle EBS

Requirement : Navigate from Oracle EBS forms to Oracle Web Center ,to view images/pdfs of documents like Invoices, Receipts etc..

Pre-Requisite : Create document searches in Oracle Web Center and access to Web Center page and searches is enabled for your login. Ideally,Oracle Web Center Technical team will handle these activities.

Solution:

Run the below mentioned script to configure the require settings to enable Zoom button in Oracle Forms and Navigate to Oracle Web Center on clicking the zoom button.

SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE

v_ipm_server AXF.AXF_CONFIGS.SOLUTIONENDPOINT%TYPE:=’imaging.oracle.com’; — Oracle WebCenter IPM Server Name
v_ipm_port AXF.AXF_CONFIGS.SOLUTIONENDPOINT%TYPE:= 16000; — Oracle WebCenter IPM Server’s Port Number
v_sol_namespace AXF.AXF_COMMANDS.SOLUTIONNAMESPACE%TYPE:=’Collections’; — Solution namespace in IPM Server ex.Receivables,Collections
v_cmd_namespace AXF.AXF_COMMANDS.COMMANDNAMESPACE%TYPE:=’SearchIPM’; — Command namespace
v_formId AXF.AXF_CONFIGS.FORMID%TYPE;
v_eventId AXF.AXF_COMMANDS.EVENTID%TYPE;

BEGIN

— Insert form details into Webcenter Configuration table —

SELECT axf.axf_configs_seq.nextval
INTO v_formId
FROM dual;

INSERT INTO axf.axf_configs

(formid,
formfunction,
solutionendpoint,
entityname,
logenabled,
datablockname,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login)
VALUES (v_formId,
‘IEXRCALL’,
http://&#8217;||v_ipm_server||’:’||v_ipm_port||’/axf-ws/AxfSolutionMediatorService’ ,
‘HZ_PARTIES’,
‘YES’,
‘IEXCUOVW_HEADER’,
0,
SYSDATE,
SYSDATE,
0,
0);

SELECT axf.axf_commands_seq.nextval
INTO v_eventId
FROM dual;

— Insert form details into Webcenter Commands table —

INSERT INTO axf.axf_commands(eventid,

formid,
eventname,
displaymenu,
commandnamespace,
requiresconversation,
sortby,
solutionnamespace,
menutype,
special,
responsibility,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login)

VALUES

(v_eventId,
v_formId,
‘ZOOM’,
‘Search Docs by Customer Number’,
v_cmd_namespace,
‘NO’,
1,
v_sol_namespace,
‘ZOOM’,
NULL,
NULL,
0,
SYSDATE,
SYSDATE,
0,
0);

— Insert form details into Webcenter Command Parameters table —

INSERT INTO axf.axf_command_parameters

(parameterid,
eventid,
parametername,
datasourcename,
datablockname,
fieldname,
constantvalue,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login)

VALUES (axf.axf_command_parameters_seq.nextval,

v_eventId,
‘SearchName’,
‘CONSTANT’,
NULL,
NULL,
‘Search By Party Name Customer Statements’,
0,
SYSDATE,
SYSDATE,
0,
0);

INSERT INTO axf.axf_command_parameters

(parameterid,
eventid,
parametername,
datasourcename,
datablockname,
fieldname,
constantvalue,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login)

VALUES (axf.axf_command_parameters_seq.nextval,

v_eventId,
‘Party Name’,
‘DATA’,
‘IEXCUOVW_HEADER’,
‘ORGANIZATION_NAME’,
NULL,
0,
SYSDATE,
SYSDATE,
0,
0);

COMMIT;

dbms_output.put_line(‘Webcenter zoom configuration details creation successfull’);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in webcenter zoom insert script:’||SQLERRM);
ROLLBACK;
END;

Once the script is run you can navigate to the Oracle Form for which the configurations have been setup.In this example i have used IEXRCALL (Collections form in Advanced Collections Module).

On navigation you can see the Zoom button in tool bar is disabled.

Image

On placing the cursor at Party Name,the Zoom button in toolbar is enabled.

Image

On clicking the Zoom button Search option LOV pops up.

Image

On selecting the search name,Oracle WebCenter login page opens up.On logging in the search based on the parameter is displayed.In this example the search was based on Party Name.

Image

On Clicking the PDF icon adjacent to party name,the PDF document related to the Party opens up.

Imagera