--CREATING PACKAGE SPECIFICATION TO CREATE MULTIPLE RECEIPTS WITH SAME RECEIPT NUMBER FOR DIFFERENT ORGANIZATIONS.
CREATE OR REPLACE PACKAGE xxfin_cus_form_pkg
IS
PROCEDURE xxfin_cus_form_rec_prc(
errbuff OUT VARCHAR2 ,
retcode OUT NUMBER ,
ip_receipt_num VARCHAR2 ,
ip_cus_num VARCHAR2);
PROCEDURE xxfin_cus_form_rec_apply(
ip_cash_receipt_id IN NUMBER ,
ip_cus_num IN NUMBER ,
ip_receipt_num IN VARCHAR2 ,
ip_org_id IN NUMBER ,
op_return_status1 OUT VARCHAR2 );
END xxfin_cus_form_pkg;
/
--CREATING PACKAGE BODY TO CREATE MULTIPLE RECEIPTS WITH SAME RECEIPT NUMBER FOR DIFFERENT ORGANIZATIONS.
CREATE OR REPLACE PACKAGE body xxfin_cus_form_pkg
IS
--CREATING PROCEDURE TO CREATE MULTIPLE RECEIPT WITH SAME RECEIPT NUMBER FOR DIFFERENT ORG'S.
PROCEDURE xxfin_cus_form_rec_prc(
errbuff OUT VARCHAR2 ,
retcode OUT NUMBER ,
ip_receipt_num VARCHAR2 ,
ip_cus_num VARCHAR2 )
IS
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
lv_trx_number VARCHAR2(200);
l_msg_data VARCHAR2(240);
l_cash_receipt_id NUMBER;
p_count NUMBER := 0;
l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
l_customer_trx_id NUMBER;
gc_user_name VARCHAR2 (100);
gc_responsibility_name VARCHAR2 (100);
gc_application_short_name VARCHAR2 (100);
gc_org_id NUMBER;
l_error_msg VARCHAR2 (500);
l_org_id NUMBER;
l_return_status1_apply VARCHAR2(240);
l_status VARCHAR2(30);
lv_cr_id ar_cash_receipts_all.cash_receipt_id%TYPE;
ln_user_id NUMBER;
lv_receipt_method VARCHAR2(150);
ln_receipt_method_id NUMBER(15);
CURSOR recipt_create_stg
IS
SELECT SUM(xt.AMOUNT_APPLIED) amount,
xt.INVOICE_CURRENCY_CODE,
xr.RECEIPT_NUMBER,
xr.GL_DATE,
xt.account_number,
xr.receipt_method,
xr.RECEIPT_METHOD_ID,
xr.comments,
xr.ATTRIBUTE_CATEGORY,
xr.ATTRIBUTE1,
xr.ATTRIBUTE2,
xr.ATTRIBUTE3,
xr.ATTRIBUTE4,
xr.attribute5,
xt.org_id
FROM xxfin_custom_receipt xr,
xxfin_cust_trx xt
WHERE xr.receipt_number=xt.receipt_number
AND xt.rec_status ='Y'
AND xt.receipt_number =ip_receipt_num
AND xt.customer_num =ip_cus_num
GROUP BY xt.org_id,
xt.INVOICE_CURRENCY_CODE,
xr.RECEIPT_NUMBER,
xr.GL_DATE,
xt.account_number,
xr.RECEIPT_METHOD_ID,
xr.ATTRIBUTE_CATEGORY,
xr.ATTRIBUTE1,
xr.ATTRIBUTE2,
xr.ATTRIBUTE3,
xr.ATTRIBUTE4,
xr.ATTRIBUTE5,
xr.comments,
xr.receipt_method;
CURSOR cu_login_variables
IS
SELECT fu.user_id user_id,
frv.responsibility_id resp_id,
fav.application_id resp_appl_id
FROM fnd_application_vl fav,
fnd_responsibility_vl frv,
fnd_user fu
WHERE fu.user_id = ln_user_id
AND frv.responsibility_name = gc_responsibility_name
AND fav.application_short_name = gc_application_short_name;
lr_login_variables cu_login_variables%ROWTYPE;
lv_receipt_number VARCHAR2(200);
BEGIN
ln_user_id:=fnd_global.user_id;
DELETE FROM xxfin_cust_trx WHERE rec_status='N';
COMMIT;
FOR cur_create_stg IN recipt_create_stg
LOOP
BEGIN
SELECT lookup_code,
meaning,
description,
tag
INTO gc_org_id,
gc_user_name,
gc_responsibility_name,
gc_application_short_name
FROM fnd_lookup_values_vl
WHERE enabled_flag = 'Y'
AND lookup_code = cur_create_stg.org_id
AND lookup_type = 'XX_APPLICATION_INITIATION12';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_msg := 'select for XX_APPLICATION_INITIATION12 failed. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
WHEN TOO_MANY_ROWS THEN
l_error_msg := 'select for XX_APPLICATION_INITIATION12 failed due to too many rows. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
END;
OPEN cu_login_variables;
FETCH cu_login_variables INTO lr_login_variables;
CLOSE cu_login_variables;
fnd_global.apps_initialize (lr_login_variables.user_id
,lr_login_variables.resp_id
,lr_login_variables.resp_appl_id );
mo_global.init(gc_application_short_name);
mo_global.set_policy_context('S',cur_create_stg.org_id);
-----------------------validating for duplicate receipt.--------------------------------
DECLARE
lc_receipt_count NUMBER(3);
lv_error_msg VARCHAR2(500);
BEGIN
SELECT COUNT(receipt_number)
INTO lc_receipt_count
FROM ar_cash_receipts_all
WHERE receipt_number = cur_create_stg.receipt_number
AND org_id = cur_create_stg.org_id;
IF lc_receipt_count >0 THEN
lv_error_msg := 'Error: Receipt Number ' || cur_create_stg.receipt_number || ' already in the System for '||cur_create_stg.org_id;
UPDATE xxfin_cust_trx
SET ERRBUF =lv_error_msg
WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number
AND ORG_ID =cur_create_stg.org_id;
DBMS_OUTPUT.put_line (lv_error_msg);
ELSE
NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE xxfin_cust_trx
SET ERRBUF ='Receipt Values not found in custom receipt table'
WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number
AND ORG_ID =cur_create_stg.org_id;
END;
------------------------end of duplicate receipt validation.--------------------------------
BEGIN
lv_receipt_number := cur_create_stg.RECEIPT_NUMBER;
----------------------------------changing the receipt method internally---------------
BEGIN
lv_receipt_method:=cur_create_stg.receipt_method;
IF lv_receipt_method LIKE '%Cash%' THEN
ln_receipt_method_id:=1502;
elsif lv_receipt_method LIKE '%Cheque%' THEN
ln_receipt_method_id:=1503;
elsif lv_receipt_method LIKE '%PDC%' THEN
ln_receipt_method_id:=1503;
elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
ln_receipt_method_id:=505;
ELSE
ln_receipt_method_id:=cur_create_stg.RECEIPT_METHOD_ID;
END IF;
END;
----------------------------------------------------------------------------------------
l_cash_receipt_id := NULL;
l_attribute_rec.attribute_category:=cur_create_stg.attribute_category;
l_attribute_rec.attribute2 :=cur_create_stg.attribute1;
l_attribute_rec.attribute3 :=cur_create_stg.attribute2;
l_attribute_rec.attribute4 :=cur_create_stg.attribute4;
l_attribute_rec.attribute5 :=cur_create_stg.attribute3;
l_attribute_rec.attribute8 :=cur_create_stg.attribute5;
-- 2) Call the API
AR_RECEIPT_API_PUB.CREATE_CASH ( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_currency_code => cur_create_stg.INVOICE_CURRENCY_CODE
, p_amount => cur_create_stg.amount
, p_receipt_number => cur_create_stg.RECEIPT_NUMBER
, p_receipt_date => TRUNC(SYSDATE)
, p_gl_date => cur_create_stg.GL_DATE
, p_customer_number => cur_create_stg.account_number
, p_receipt_method_id => ln_receipt_method_id
, p_comments => cur_create_stg.comments
, P_ORG_ID => cur_create_stg.org_id
, p_attribute_rec => l_attribute_rec
, p_cr_id => l_cash_receipt_id );
COMMIT;
END;
-- 3) Review the API output
dbms_output.put_line('Status ' || l_return_status);
dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );
dbms_output.put_line('Message count ' || l_msg_count);
l_status:='Receipt Not Created';
COMMIT;
IF l_return_status='S'
THEN
BEGIN
l_status:='Receipt Created';
xxfin_cust_trx_prc(l_cash_receipt_id
,cur_create_stg.RECEIPT_NUMBER
,l_status,l_msg_data
,cur_create_stg.org_id
,ip_cus_num);
COMMIT;
dbms_output.put_line('Message Apply: '|| cur_create_stg.org_id );
xxfin_cus_form_pkg.xxfin_cus_form_rec_apply (ip_cash_receipt_id => l_cash_receipt_id
, ip_cus_num => ip_cus_num
, ip_receipt_num => cur_create_stg.RECEIPT_NUMBER
, ip_org_id => cur_create_stg.org_id
, op_return_status1 =>l_return_status1_apply );
dbms_output.put_line('Message Apply2: '|| l_return_status1_apply );
END;
l_status:='Receipt Applied';
COMMIT;
ELSIF l_return_status='E' THEN
l_status :='Receipt UnApplied Error';
xxfin_cust_trx_prc(l_cash_receipt_id
,cur_create_stg.RECEIPT_NUMBER
,l_status
,l_msg_data
,cur_create_stg.org_id
,ip_cus_num);
COMMIT;
END IF;
IF l_msg_count = 1 THEN
dbms_output.put_line('l_msg_data '||l_msg_data|| cur_create_stg.org_id|| 'Org_id');
l_status :='Receipt UnApplied EE';
xxfin_cust_trx_prc(l_cash_receipt_id
,cur_create_stg.RECEIPT_NUMBER
,l_status
,l_msg_data
, cur_create_stg.org_id
,ip_cus_num);
COMMIT;
elsif l_msg_count > 1 THEN
LOOP
p_count := p_count + 1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
l_status :='Receipt UnApplied EEE';
xxfin_cust_trx_prc(l_cash_receipt_id
,cur_create_stg.RECEIPT_NUMBER
,l_status
,l_msg_data
,cur_create_stg.org_id,ip_cus_num);
COMMIT;
IF l_msg_data IS NULL THEN
EXIT;
END IF;
dbms_output.put_line('Message ' || p_count ||'. '||l_msg_data);
END LOOP;
END IF;
COMMIT;
END LOOP;
COMMIT;
DECLARE
lv_err_msg VARCHAR2(2000);
lv_ret_code VARCHAR2(2000);
lv_receipt_number VARCHAR2(30);
BEGIN
lv_receipt_number:=ip_receipt_num;
xxfin_create_misc_rec_prc(lv_err_msg,lv_ret_code,lv_receipt_number);
dbms_output.put_line('receipt number is '||lv_receipt_number);
dbms_output.put_line('error message is '||lv_err_msg);
dbms_output.put_line('error code is '||lv_ret_code);
END;
EXCEPTION
WHEN OTHERS THEN
xxfin_cust_trx_prc_apply(l_cash_receipt_id,lv_receipt_number,lv_TRX_NUMBER,'Step2'||SQLERRM,l_msg_data,ip_cus_num);
END xxfin_cus_form_rec_prc;
--END OF PROCEDURE XXFIN_CUS_FORM_REC_PRC.
--CREATING A PROCEDURE TO APPLY THE RECEIPTS, WHICH IS CREATED BY THE XXFIN_CUS_FORM_REC_PRC PROCEDURE.
PROCEDURE xxfin_cus_form_rec_apply(
ip_cash_receipt_id IN NUMBER ,
ip_cus_num IN NUMBER ,
ip_receipt_num IN VARCHAR2 ,
ip_org_id IN NUMBER ,
op_return_status1 OUT VARCHAR2 )
IS
l_error_msg VARCHAR2 (500);
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_cash_receipt_id NUMBER;
p_count NUMBER := 0;
L_ATTRIBUTE_REC VARCHAR2(150);
l_customer_trx_id NUMBER;
gc_user_name VARCHAR2 (100);
gc_responsibility_name VARCHAR2 (100);
gc_application_short_name VARCHAR2 (100);
gc_org_id NUMBER;
l_status VARCHAR2(30);
lv_error_message VARCHAR2(2000);
ln_user_id NUMBER;
CURSOR recipt_apply_stg
IS
SELECT xt.AMOUNT_APPLIED,
xr.RECEIPT_NUMBER,
xt.org_id,
xt.TRX_NUMBER,
xt.apply_date
FROM xxfin_custom_receipt xr,
xxfin_cust_trx xt
WHERE xr.receipt_number=xt.receipt_number
AND xt.rec_status ='Y'
AND xt.receipt_number =ip_receipt_num
AND xt.customer_num =ip_cus_num
AND xt.org_id =ip_org_id;
CURSOR cu_login_variables
IS
SELECT fu.user_id user_id,
frv.responsibility_id resp_id,
fav.application_id resp_appl_id
FROM fnd_application_vl fav,
fnd_responsibility_vl frv,
fnd_user fu
WHERE fu.user_id = ln_user_id
AND frv.responsibility_name = gc_responsibility_name
AND fav.application_short_name = gc_application_short_name;
lr_login_variables cu_login_variables%ROWTYPE;
lv_trx_number NUMBER;
BEGIN
ln_user_id:=fnd_global.user_id;
FOR cur_apply_stg IN recipt_apply_stg
LOOP
BEGIN
BEGIN
ar_receipt_api_pub.Apply ( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_cash_receipt_id => ip_cash_receipt_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_trx_number => cur_apply_stg.trx_number
, p_customer_trx_id => l_customer_trx_id
, p_amount_applied => cur_apply_stg.AMOUNT_APPLIED
, p_org_id => ip_org_id
, p_apply_date => cur_apply_stg.apply_date
, p_show_closed_invoices => 'Y' );
COMMIT;
END ;
IF l_return_status='S' THEN
l_status :='Receipt Applied';
xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);
ELSE
l_status:='Receipt UnApplied';
xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);
END IF;
END;
END LOOP;
xxfin_applied_amount(ip_receipt_num);
EXCEPTION
WHEN OTHERS THEN
lv_error_message := sqlerrm;
UPDATE xxfin_cust_trx
SET errbuf =lv_error_message,
status = 'Step6',
CASH_RECEIPT_ID =l_cash_receipt_id
WHERE receipt_number=ip_receipt_num
AND rec_status ='Y';
COMMIT;
END xxfin_cus_form_rec_apply;
--END OF CREATING XXFIN_CUS_FORM_REC_APPLY.
END xxfin_cus_form_pkg;
/
--END OF PACKAGE BODY.
