Oracle Apps Technical Consultant: how to create a receipt and apply the receipt in AR by using AR_RECEIPT_API

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);
--ln_doc_seq_value number(30);
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;
-- l_org_id:=fnd_profile.value(l_org_id);
FOR cur_create_stg IN recipt_create_stg
LOOP
-- xxfin_cust_trx_prc_apply(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,cur_create_stg.TRX_NUMBER,'Step0',l_msg_data);
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--'329'--rec_ar_dt.org_id
AND lookup_type = 'XXIMS_APPLICATION_INITIATION12';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_msg := 'select for XXIMS_APPLICATION_INITIATION12 failed. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
WHEN TOO_MANY_ROWS THEN
l_error_msg := 'select for XXIMS_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 (-1, 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_trx.org_id);--'329'
*/
-- 1) Set the applications context
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);
-- fnd_global.apps_initialize(1011902, 50559, 222,0);
-----------------------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;
--fnd_file.put_line (fnd_file.LOG, p_error_msg);
UPDATE xxfin_cust_trx
SET ERRBUF =lv_error_msg
WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number
-- AND TRX_NUMBER =cur_create_stg.trx_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 TRX_NUMBER =cur_create_stg.trx_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:=15002;
elsif lv_receipt_method LIKE '%Cheque%' THEN
ln_receipt_method_id:=15003;
elsif lv_receipt_method LIKE '%PDC%' THEN
ln_receipt_method_id:=15003;
--elsif lv_receipt_method like '%Intercompany Bank Transfer acc%' then --COMMENTED 17-OCT-16
-- ln_receipt_method_id:=5005; --COMMENTED 17-OCT-16
elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
ln_receipt_method_id:=5005;
ELSE
ln_receipt_method_id:=cur_create_stg.RECEIPT_METHOD_ID;
END IF;
END;
/*
begin
select xxfin_doc_sequence.nextval into ln_doc_seq_value from dual;
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,--'AED',
p_amount => cur_create_stg.amount, --'450',
p_receipt_number => cur_create_stg.RECEIPT_NUMBER, --'T-149',
p_receipt_date => TRUNC(SYSDATE), --'11-JUN-2016',
p_gl_date => cur_create_stg.GL_DATE, --'11-JUN-2016',
p_customer_number => cur_create_stg.account_number, --'1231',
p_receipt_method_id => ln_receipt_method_id, --'11001',
p_comments => cur_create_stg.comments, P_ORG_ID => cur_create_stg.org_id, --'329',
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';
-- xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
COMMIT;
IF l_return_status='S'
--and l_cash_receipt_id IS NOT NULL
-- xxfin_cust_trx_prc(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,l_status,l_msg_data);
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);
--UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
-- WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
-- AND rec_status='Y';
-- xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
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;
--exception--
l_status:='Receipt Applied';
--UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
-- WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
-- AND rec_status='Y';
--xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
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);
-- xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
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';
--UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
-- WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
-- AND rec_status='Y';
-- xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status); commented today
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);
-- UPDATE xxfin_cust_trx SET status=l_status,CASH_RECEIPT_ID=l_cash_receipt_id
-- WHERE receipt_number=cur_create_stg.RECEIPT_NUMBER
-- AND rec_status='Y';
-- xxfin_cust_trx_prc(l_cash_receipt_id,cur_create_stg.RECEIPT_NUMBER,l_status);
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;
-- Have to call miscellaneous receipt creation api here
-- xxfin_cus_form_pkg.xxfin_misc_receipt_submit_proc(ip_receipt_num);
DECLARE --added 06-oct-16 12:02 pm
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;
-- l_org_id:=fnd_profile.value(l_org_id);
FOR cur_apply_stg IN recipt_apply_stg
LOOP
BEGIN
/*mo_global.init(gc_application_short_name);
mo_global.set_policy_context('S',ip_org_id);
fnd_global.apps_initialize (lr_login_variables.user_id, lr_login_variables.resp_id, lr_login_variables.resp_appl_id );*/

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--l_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 ----'553533',
, p_customer_trx_id => l_customer_trx_id , p_amount_applied => cur_apply_stg.AMOUNT_APPLIED --'450',--recpt_rec.amount,
, p_org_id => ip_org_id --cur_apply_stg.org_id--'329'--l_org_id
, p_apply_date => cur_apply_stg.apply_date --added 12-09-16.
,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--if l_return_status='E' then
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);--calling the procedure to update the applied_amount and un applied amount in custom table.
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;
/

Oracle Apps Technical Consultant

Monday, 24 April 2017

how to create a receipt and apply the receipt in AR by using AR_RECEIPT_API_PUB API ?

No comments:

Post a Comment

Query to find request set and its responsibility

Report Abuse