Oracle Applications: Invoice Transaction Reprint Program Using Shell

# Parameters.

#----- Standard Parameters: -----#

p_apps_passwd=$1

p_user_id=$2

p_username=$3

p_request_id=$4

p_order_by=$5 #----- Added as per FSIT-22651 -----#

p_business_model=$6

p_include_partners=$7

p_operating_unit=$8

p_country_code=$9

p_hotel_id=${10}

p_receipt_method=${11}

p_trx_class=${12}

p_trx_date_from=${13}

p_trx_date_to=${14}

p_currency_code=${15}

echo "p_user_id = $p_user_id"

echo "p_username = $p_username"

echo "p_request_id = $p_request_id"

echo "p_order_by = $p_order_by" #----- Added as per FSIT-22651 -----#

echo "p_business_model = $p_business_model"

echo "p_include_partners = $p_include_partners"

echo "p_operating_unit = $p_operating_unit"

echo "p_country_code = $p_country_code"

echo "p_hotel_id = $p_hotel_id"

echo "p_receipt_method = $p_receipt_method"

echo "p_trx_class = $p_trx_class"

echo "p_trx_date_from = $p_trx_date_from"

echo "p_trx_date_to = $p_trx_date_to"

echo "p_currency_code = $p_currency_code"

v_pdf_file_list=`echo ""`

counter=`echo "1"`

echo $counter

v_printed=`echo "N"`

v_error=`echo "N"`

for i in `sqlplus -s $p_apps_passwd <<!

SET LINES 255

SET PAGES 20

SET FEEDBACK OFF

SET HEADING OFF

COLUMN file_name FORMAT a255

SELECT rcta.trx_number || '~' ||

XXEXPD_AR_INVOICE_REP_PKG.get_file_path_group

(

hca.cust_account_id,

rcta.trx_number,

rcta.trx_date,

hou.short_code

)

FROM ra_customer_trx_all rcta,

hz_cust_accounts hca,

hr_operating_units hou,

ra_cust_trx_types_all rctt,

hz_cust_site_uses_all hcsu,

hz_cust_acct_sites_all hcas,

hz_party_sites hps,

hz_locations hl

WHERE rcta.org_id = hou.organization_id

AND rcta.bill_to_customer_id = hca.cust_account_id

AND rctt.cust_trx_type_id = rcta.cust_trx_type_id

AND rcta.printing_pending = 'N'

AND rcta.bill_to_site_use_id = hcsu.site_use_id (+)

AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id (+)

AND hcas.party_site_id = hps.party_site_id (+)

AND hps.location_id = hl.location_id (+)

AND NVL(rcta.attribute_category, 'NO BUSINESS MODEL') = NVL('$p_business_model', NVL(rcta.attribute_category, 'NO BUSINESS MODEL'))

AND hou.short_code = '$p_operating_unit'

AND NVL(hca.attribute1, 'NO HOTEL ID') = nvl('$p_hotel_id', NVL(hca.attribute1, 'NO HOTEL ID'))

AND NVL(rcta.receipt_method_id, -99) = nvl('$p_receipt_method', NVL(rcta.receipt_method_id, -99))

AND rctt.type = DECODE('$p_trx_class', 'ALL', rctt.type, '$p_trx_class')

AND TRUNC(rcta.trx_date) BETWEEN TRUNC(TO_DATE('$p_trx_date_from', 'YYYY/MM/DD HH24:MI:SS')) AND TRUNC(TO_DATE('$p_trx_date_to', 'YYYY/MM/DD HH24:MI:SS'))

AND rcta.invoice_currency_code = nvl('$p_currency_code', rcta.invoice_currency_code)

AND NVL(hl.country, 'NO COUNTRY') = NVL('$p_country_code', NVL(hl.country, 'NO COUNTRY'))

AND DECODE(NVL('$p_include_partners','ALL'),'ALL','Y',NVL('$p_include_partners','ALL')) = DECODE(NVL('$p_include_partners','ALL'),'ALL','Y',DECODE(xxexpd_ar_inv_reprint_pkg.is_group_cust(rcta.bill_to_customer_id)

,'Y','GROUP'

,'N','INDEPENDENT'))--added as part of FSIT-54217

ORDER BY DECODE('$p_order_by','Transaction Number',rcta.trx_number) --Added as per FSIT-22651;

EXIT;

v_trx_number=`echo $i | awk -F "~" '{print $1}'`

v_pdf_file=`echo $i | awk -F "~" '{print $2}'`

#echo "Transaction# : $v_trx_number"

#echo "PDF File : $v_pdf_file"

if [ ! -f $v_pdf_file ]

then

echo "Transaction# $v_trx_number was not printed because the PDF file does not exist"

v_error=`echo "Y"`

if [ -f $v_pdf_file ]

then

v_pdf_file_list=`echo $v_pdf_file_list $v_pdf_file`

v_printed=`echo "Y"`

done

#FSIT-85680

v_aws_copy=`sqlplus -s $p_apps_passwd <<!

declare

cursor c1 is (SELECT rcta.trx_number invoice_number,

XXEXPD_AR_INVOICE_REP_PKG.get_file_path_group

(

hca.cust_account_id,

rcta.trx_number,

rcta.trx_date,

hou.short_code

) file_path

FROM ra_customer_trx_all rcta,

hz_cust_accounts hca,

hr_operating_units hou,

ra_cust_trx_types_all rctt,

hz_cust_site_uses_all hcsu,

hz_cust_acct_sites_all hcas,

hz_party_sites hps,

hz_locations hl

WHERE rcta.org_id = hou.organization_id

AND rcta.bill_to_customer_id = hca.cust_account_id

AND rctt.cust_trx_type_id = rcta.cust_trx_type_id

AND rcta.printing_pending = 'N'

AND rcta.bill_to_site_use_id = hcsu.site_use_id (+)

AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id (+)

AND hcas.party_site_id = hps.party_site_id (+)

AND hps.location_id = hl.location_id (+)

AND NVL(rcta.attribute_category, 'NO BUSINESS MODEL') = NVL('$p_business_model', NVL(rcta.attribute_category, 'NO BUSINESS MODEL'))

AND hou.short_code = '$p_operating_unit'

AND NVL(hca.attribute1, 'NO HOTEL ID') = nvl('$p_hotel_id', NVL(hca.attribute1, 'NO HOTEL ID'))

AND NVL(rcta.receipt_method_id, -99) = nvl('$p_receipt_method', NVL(rcta.receipt_method_id, -99))

AND rctt.type = DECODE('$p_trx_class', 'ALL', rctt.type, '$p_trx_class')

AND TRUNC(rcta.trx_date) BETWEEN TRUNC(TO_DATE('$p_trx_date_from', 'YYYY/MM/DD HH24:MI:SS')) AND TRUNC(TO_DATE('$p_trx_date_to', 'YYYY/MM/DD HH24:MI:SS'))

AND rcta.invoice_currency_code = nvl('$p_currency_code', rcta.invoice_currency_code)

AND NVL(hl.country, 'NO COUNTRY') = NVL('$p_country_code', NVL(hl.country, 'NO COUNTRY'))

,'Y','GROUP'

,'N','INDEPENDENT')));

l_copy_status VARCHAR2(1000);

l_aws_path VARCHAR2(2000);

l_bucket_name VARCHAR2(200);

l_aws_profile VARCHAR2(100);

l_encrypt_status VARCHAR2(100);

begin

for i in c1 loop

BEGIN

l_encrypt_status := NULL;

l_aws_path := NULL;

l_bucket_name := NULL;

l_aws_profile := NULL;

l_copy_status := NULL;

BEGIN

SELECT xflv.value9,xflv.value8,xflv.value11

INTO l_aws_path,l_bucket_name,l_aws_profile

FROM xxexpd_fnd_lkp_t xfl

,xxexpd_fnd_lkp_values_t xflv

WHERE xfl.lkp_def_id = xflv.lkp_def_id

AND xfl.lkp_def_name = 'XXEXPD_CFD_AWS_DELIVERY'

AND EXISTS ( SELECT 1

FROM ar_receipt_methods a,

ra_customer_trx_all b

WHERE a.receipt_method_id = b.receipt_method_id

AND b.trx_number = i.invoice_number

AND printed_name = xflv.value5);

EXCEPTION

WHEN OTHERS THEN

l_aws_path := NULL;

END;

IF l_aws_path IS NOT NULL THEN

BEGIN

l_encrypt_status := xxexpd_file_utility_pkg.encrypt_file ('E.990',i.file_path);

dbms_output.put_line('l_status '||l_encrypt_status);

END;

BEGIN

SELECT apps.xxexpd_aws_s3_utility_pkg.upload_object (l_aws_profile

,l_bucket_name||'/'||l_aws_path

,i.file_path||'.pgp'

,NULL) INTO l_copy_status

FROM DUAL;

dbms_output.put_line('Status '||l_copy_status);

EXCEPTION

WHEN OTHERS THEN

l_copy_status := NULL;

END;

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

end loop;

END;

EXIT;

echo "Total No of Files Exported" $counter

echo "pdf file list $v_pdf_file_list"

#----- Changed for FSIT-54217-----#

v_file_name=$p_operating_unit"_"$p_trx_class"_"$(date +"%Y%m%d%H%M%S")

echo "PDF file name: '$v_file_name'"

#----- Changed XXEXPD_TOP to XXEXPD_TOP_NE for FSIT-35954-----#

java oracle.apps.xdo.common.pdf.util.PDFDocMerger -tmp /usr/tmp $v_pdf_file_list $XXEXPD_TOP_NE/data/outbound/R.434/$v_file_name.pdf

v_pdf_file_name=`echo $XXEXPD_TOP_NE/data/outbound/R.434/$v_file_name.pdf`

v_host_name=`hostname`

v_dummy=`sqlplus -s $p_apps_passwd <<!

INSERT INTO FND_CONC_PP_ACTIONS

(

concurrent_request_id

,action_type

,status_s_flag

,status_w_flag

,status_f_flag

,last_update_date

,last_updated_by

,creation_date

,created_by

,last_update_login

,arguments

,completed

,number_of_copies

,sequence

,ops_instance

)

VALUES

(

$p_request_id

,'Y'

,'N'

,SYSDATE

,$p_user_id

,SYSDATE

,$p_user_id

,'noprint'

,'Y'

,-1

);

INSERT INTO FND_CONC_PP_ACTIONS

(

concurrent_request_id

,action_type

,status_s_flag

,status_w_flag

,status_f_flag

,last_update_date

,last_updated_by

,creation_date

,created_by

,last_update_login

,completed

,sequence

,ops_instance

,argument1

,argument2

,argument3

,argument4

,argument5

)

VALUES

(

$p_request_id

,'Y'

,SYSDATE

,$p_user_id

,SYSDATE

,$p_user_id

,'Y'

,-1

,'XXEXPD'

,'XXEXPD_AR_REPRINT_TRANSACTIONS'

,'en'

,'US'

,'PDF'

);

INSERT INTO FND_CONC_REQ_OUTPUTS

(

concurrent_request_id

,output_id

,file_type

,file_name

,file_node_name

,file_size

,action_type

,file_creation_date

)

VALUES

(

$p_request_id

,fnd_conc_req_outputs_s.nextval

,'PDF'

,'$v_pdf_file_name'

,'$v_host_name'

,15000

,SYSDATE

);

COMMIT;

EXIT;

echo $v_dummy

if [ $? != 0 ]; then

echo "Unable to merge PDF file"

MYOWNSTATUS=`sqlplus -s $p_apps_passwd <<!

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,$p_request_id, null,null,null,null,null,null,-1);

l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');

commit;

end;

exit;

exit

if [ "$v_printed" = "N" ]; then

echo "No Transactions found for processing for the parameters submitted."

MYOWNSTATUS=`sqlplus -s $p_apps_passwd <<!

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,$p_request_id, null,null,null,null,null,null,-1);

l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');

commit;

end;

exit;

exit

if [ "$v_error" = "Y" ]; then

MYOWNSTATUS=`sqlplus -s $p_apps_passwd <<!

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,$p_request_id, null,null,null,null,null,null,-1);

l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');

commit;

end;

exit;

exit

Oracle Applications

Pages

Wednesday, April 8, 2020

Invoice Transaction Reprint Program Using Shell

No comments:

Post a Comment