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 ?

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;
/

How to create a miscellaneous receipt by using AR_RECEIPT_API_PUB API in AR ?

create or replace PROCEDURE xxfin_create_misc_prc(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
ip_receipt_num IN VARCHAR2)
-- op_return_status2 OUT VARCHAR2)
AS
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
p_usr_currency_code VARCHAR2(200);
p_currency_code VARCHAR2(200);
p_usr_exchange_rate_type VARCHAR2(200);
p_exchange_rate_type VARCHAR2(200);
p_exchange_rate NUMBER;
p_exchange_rate_date DATE;
p_amount NUMBER;
p_org_id NUMBER;
p_receipt_number VARCHAR2(200);
p_receipt_date DATE;
p_gl_date DATE;
p_receivables_trx_id NUMBER;
p_activity VARCHAR2(200) DEFAULT NULL;
p_misc_payment_source VARCHAR2(200):='Created by AR RECEIPT API PUB';
p_tax_code VARCHAR2(200);
p_vat_tax_id VARCHAR2(200);
p_tax_rate NUMBER;
p_tax_amount NUMBER DEFAULT NULL;
p_deposit_date DATE;
p_reference_type VARCHAR2(200);
p_reference_num VARCHAR2(200);
p_reference_id NUMBER;
p_remittance_bank_account_id NUMBER;
p_remittance_bank_account_num VARCHAR2(200);
p_remittance_bank_account_name VARCHAR2(200);
p_receipt_method_id NUMBER;
p_receipt_method_name VARCHAR2(200);
p_doc_sequence_value NUMBER;
p_ussgl_transaction_code VARCHAR2(200);
p_anticipated_clearing_date DATE;
p_attribute_record AR_RECEIPT_API_PUB.attribute_rec_type;
p_global_attribute_record AR_RECEIPT_API_PUB.global_attribute_rec_type;
p_comments VARCHAR2(200);
p_misc_receipt_id NUMBER;
p_called_from VARCHAR2(200);
gc_user_name VARCHAR2 (100);
gc_responsibility_name VARCHAR2 (100);
gc_application_short_name VARCHAR2 (100);
gc_org_id NUMBER;
l_error_msg VARCHAR2 (2000);
l_receipt_id NUMBER;
l_bank_ref_number VARCHAR2(100);
lv_receipt_method VARCHAR2(150);
ln_receipt_method_id NUMBER(15);
lv_receipt_num VARCHAR2(30);
ln_user_id NUMBER:=fnd_global.user_id;
l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
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;
CURSOR misc_receipts
IS
SELECT SUM(xct.amount_applied) amount ,
xcr.comments ,
xcr.ATTRIBUTE_CATEGORY ,
xcr.ATTRIBUTE1 ,
xcr.ATTRIBUTE2 ,
xcr.ATTRIBUTE3 ,
xcr.ATTRIBUTE4 ,
xcr.attribute5 ,
xcr.receipt_date ,
xcr.gl_date ,
xct.invoice_currency_code ,
XCR.RECEIPT_METHOD_ID ,
XCR.RECEIPT_METHOD ,
xcr.receipt_number
FROM xxfin_custom_receipt xcr ,
xxfin_cust_trx xct
WHERE xcr.receipt_number=xct.receipt_number
AND xcr.receipt_number=ip_receipt_num
GROUP BY xcr.comments,
xcr.ATTRIBUTE_CATEGORY,
xcr.ATTRIBUTE1,
xcr.ATTRIBUTE2,
xcr.ATTRIBUTE3,
xcr.ATTRIBUTE4,
xcr.attribute5,
xcr.receipt_date,
xcr.gl_date,
XCT.INVOICE_CURRENCY_CODE,
XCR.RECEIPT_METHOD_ID,
XCR.RECEIPT_METHOD,
xcr.receipt_number;
BEGIN
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 = 367
AND lookup_type = 'XXIMS_APPLICATION_INIT';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_msg := 'select for XXIMS_APPLICATION_INIT failed. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
WHEN TOO_MANY_ROWS THEN
l_error_msg := 'select for XXIMS_APPLICATION_INIT failed due to too many rows. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
WHEN OTHERS THEN
l_error_msg := 'select for XXIMS_APPLICATION_INIT failed due to other reasons ';
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;
BEGIN
FOR rec_misc IN misc_receipts
LOOP
----------------------------changin receipt method internally------------------------------------------
BEGIN
lv_receipt_num :=rec_misc.receipt_number;
lv_receipt_method:=rec_misc.receipt_method;
IF lv_receipt_method LIKE '%Cash%' THEN
ln_receipt_method_id:=15062;
elsif lv_receipt_method LIKE '%Cheque%' THEN
ln_receipt_method_id:=15063;
elsif lv_receipt_method LIKE '%PDC%' THEN
ln_receipt_method_id:=15063;
elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
ln_receipt_method_id:=50065;
ELSE
ln_receipt_method_id:=rec_misc.receipt_method_id;
END IF;
END;
---------------------------------------end receipt method changing internally---------------------------
fnd_global.apps_initialize ( lr_login_variables.user_id -- -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', 367);
fnd_file.put_line(fnd_file.log,'Application Code :' ||gc_application_short_name);

--p_receipt_number := rec_misc.receipt_number;
p_receipt_date := SYSDATE;
p_gl_date := rec_misc.gl_date ;
p_misc_receipt_id := NULL;
l_attribute_rec.attribute_category:=rec_misc.attribute_category;
l_attribute_rec.attribute2 :=rec_misc.attribute1;
l_attribute_rec.attribute3 :=rec_misc.attribute2;
l_attribute_rec.attribute4 :=rec_misc.attribute4;
l_attribute_rec.attribute5 :=rec_misc.attribute3;
l_attribute_rec.attribute8 :=NVL(rec_misc.attribute5,'000000');
SELECT receivables_trx_id
INTO p_receivables_trx_id
FROM AR_RECEIVABLES_TRX_ALL
WHERE name IN
(SELECT meaning
FROM ar_lookups
WHERE lookup_type = 'XXIMD_MISC_RECEIPT_ACTIVITY'
) ;

AR_RECEIPT_API_PUB.create_misc ( 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 => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_usr_currency_code => p_usr_currency_code, p_currency_code => rec_misc.invoice_currency_code, p_usr_exchange_rate_type => p_usr_exchange_rate_type , p_exchange_rate_type => p_exchange_rate_type , p_exchange_rate => p_exchange_rate , p_exchange_rate_date => p_exchange_rate_date , p_amount => rec_misc.amount, p_receipt_number => lv_receipt_num, p_receipt_date => TRUNC(SYSDATE), p_gl_date => rec_misc.gl_date , p_receivables_trx_id => p_receivables_trx_id, p_activity => p_activity, p_misc_payment_source => p_misc_payment_source, p_tax_code => p_tax_code , p_vat_tax_id => p_vat_tax_id , p_tax_rate => p_tax_rate , p_tax_amount => p_tax_amount , p_deposit_date => TRUNC(SYSDATE) , p_reference_type => p_reference_type , p_reference_num => p_reference_num , p_reference_id => p_reference_id , p_remittance_bank_account_id
=> p_remittance_bank_account_id , p_remittance_bank_account_num => p_remittance_bank_account_num , p_remittance_bank_account_name => p_remittance_bank_account_name , p_receipt_method_id => ln_receipt_method_id , p_receipt_method_name => lv_receipt_method , p_doc_sequence_value => p_doc_sequence_value , p_ussgl_transaction_code => p_ussgl_transaction_code , p_anticipated_clearing_date => p_anticipated_clearing_date,
p_comments => rec_misc.comments,
p_attribute_record => l_attribute_rec, p_misc_receipt_id => p_misc_receipt_id, p_called_from => p_called_from, P_Org_Id => 367
);
IF (x_return_status = 'S') THEN
COMMIT;
fnd_file.put_line(fnd_file.log,'SUCCESS');

ELSE
ROLLBACK;

fnd_file.put_line(fnd_file.log,'ERROR');
fnd_file.put_line(fnd_file.log,'Return Status = '|| SUBSTR (x_return_status,1,255)||','||x_msg_data);
fnd_file.put_line(fnd_file.log,APPS.FND_MSG_PUB.Get ( p_msg_index => APPS.FND_MSG_PUB.G_LAST, p_encoded => APPS.FND_API.G_FALSE));
IF x_msg_count >=0 THEN
FOR I IN 1..10
LOOP
fnd_file.put_line(fnd_file.log,I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END IF;
END LOOP;
fnd_file.put_line(fnd_file.log,'After end loop');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Exception :'||sqlerrm);
END;
COMMIT;
END xxfin_create_misc_prc;
/

How to Update Customer Profile by using API

CREATE OR REPLACE procedure APPS.UPDATE_CUST_COLL_PROF(ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2)
AS
l_customer_profile_rec_type hz_customer_profile_v2pub.customer_profile_rec_type;
l_object_version_number NUMBER;
l_return_status VARCHAR2 (10);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_customer_number NUMBER;
l_customer_name VARCHAR2(100);
l_collector_name VARCHAR2(100);
l_CUST_ACCOUNT_PROFILE_ID NUMBER;
l_process VARCHAR2(3) :='N';
l_cust_account_id VARCHAR2(100);
l_collector_id VARCHAR2(100);
CURSOR cur_coll
IS
SELECT xct.*,rowid row_id
FROM temp_custgrcol_tab xct
WHERE 1=1;

BEGIN

fnd_global.apps_initialize (user_id => 1318,
resp_id => 50559,
resp_appl_id => 222
);
mo_global.set_policy_context ('M','');

for rec_coll in cur_coll
loop
-- UPDATE_CUST_PROFILE.collector_name
--START VALIDATION FOR THE REQUIRED COLUMNS
-- when u r doing the validation, assign the value for l_return_status like ('S' FOR SUCCESS, 'E' FOR ERROR).
--END VALIDATION FOR THE REQUIRED COLUMNS
begin
select hcp.CUST_ACCOUNT_PROFILE_ID,hca.CUST_ACCOUNT_ID
into l_CUST_ACCOUNT_PROFILE_ID , l_cust_account_id
from hz_customer_profiles hcp,
hz_cust_accounts hca
where hcp.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
and hca.account_number =rec_coll.customer_number
and hcp.site_use_id is null;
dbms_output.put_line('Id is:'||l_CUST_ACCOUNT_PROFILE_ID);
exception
when no_data_found then
l_process :='Y';
when others then
l_process :='Y';
end;

/* Collector ID Receiving...*/

begin
select collector_id
into l_collector_id
from ar_collectors where 1=1
and name = (select collector_name FROM temp_custgrcol_tab xct
WHERE 1=1
and xct.CUSTOMER_NUMBER=rec_coll.customer_number);
exception
when others then
dbms_output.put_line('Id is not received for '||rec_coll.customer_number);
end;
/* Initializing the Mandatory API parameters */
l_customer_profile_rec_type.cust_account_profile_id := l_CUST_ACCOUNT_PROFILE_ID;
l_customer_profile_rec_type.cust_account_id := l_cust_account_id;
l_customer_profile_rec_type.collector_id :=l_collector_id;

SELECT object_version_number
INTO l_object_version_number
from hz_customer_profiles
WHERE cust_account_profile_id = l_cust_account_profile_id;
fnd_file.put_line(fnd_file.log,'Calling the API hz_customer_profile_v2pub.update_customer_profile');
hz_customer_profile_v2pub.update_customer_profile
(
p_init_msg_list => fnd_api.g_true,
p_customer_profile_rec => l_customer_profile_rec_type,
p_object_version_number => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
fnd_file.put_line(fnd_file.log, 'Updation of Customer Profile is Successful '||l_cust_account_profile_id);
fnd_file.put_line(fnd_file.log, 'Output information ....');
fnd_file.put_line(fnd_file.log, 'Object Version Number = '||l_object_version_number );

fnd_file.put_line(fnd_file.log, 'Updation of Customer Profile is Successful '||l_cust_account_profile_id);
fnd_file.put_line(fnd_file.log, 'Output information ....');
fnd_file.put_line(fnd_file.log, 'Object Version Number = '||l_object_version_number );

update temp_custgrcol_tab
set STATUS_FLAG = 'S'
where rowid = rec_coll.row_id;

ELSE
fnd_file.put_line ( fnd_file.log, 'Updation of Customer Profile got failed:'
|| l_msg_data
);
fnd_file.put_line(fnd_file.log, 'Updation of Customer Profile got failed:'
|| l_msg_data
);
ROLLBACK;
update temp_custgrcol_tab
set STATUS_FLAG = 'E'
where rowid = rec_coll.row_id;

END IF;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,SQLCODE||','||SQLERRM);
fnd_file.put_line (fnd_file.log,'Completion of API');
END UPDATE_CUST_COLL_PROF;
/

Friday, 3 June 2016

what are the tables effected by employee creation ?

Tables effected by employee creation is

PER_ALL_PEOPLE_F

PER_PEOPLE_F
PER_ALL_ASSIGNMENTS_F

HR_EMPLOYEES

PER_PERIODS_OF_SERVICES

Sunday, 15 May 2016

How to find out which user hook package and procedure have to use for the requirement ?

We have to find out that what user hook package and procedure we have to use to satisfy the requirement.

for that we have to know on which table we have to do modifications, after knowing that
we can run below query to find out the user hook package and procedure.

Here for example I am taking per_pay_proposals table.

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

How to create a user hook in Oracle HRMS ?

step 1:
=======

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

step2:
======

create or replace package XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id in number,
p_assignment_id in number,
p_business_group_id in number,
p_change_date in date,
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_date_to    in date,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number in number,
p_multiple_components in varchar2,
p_approved in varchar2,
p_inv_next_sal_date_warning in boolean,
p_proposed_salary_warning    in boolean,
p_approved_warning in boolean,
p_payroll_warning    in boolean);
end XX_SEH_PKG_HK_SAL;

create or replace package body XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id in number,
p_assignment_id in number,
p_business_group_id in number,
p_change_date in date,
p_comments in varchar2,
p_next_sal_review_date in date,
p_proposal_reason in varchar2,
p_proposed_salary_n in number,
p_forced_ranking in number,
p_date_to    in date,
p_performance_review_id in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_object_version_number in number,
p_multiple_components in varchar2,
p_approved in varchar2,
p_inv_next_sal_date_warning in boolean,
p_proposed_salary_warning    in boolean,
p_approved_warning in boolean,
p_payroll_warning    in boolean)
is
nl_pay_proposal_id number(20);
nl_assignment_id number(20);
lv_pay_proposal_id number(10);
lv_count number(1);
begin

select count(pps.PAY_PROPOSAL_ID)
into lv_count
from per_pay_proposals pps
where pps.PAY_PROPOSAL_ID=p_pay_proposal_id;

if lv_count=1 then
update per_pay_proposals pps1 set pps1.PROPOSED_SALARY= ROUND(pps1.PROPOSED_SALARY,2)
where pps1.PAY_PROPOSAL_ID=p_pay_proposal_id;
commit;
else
fnd_file.put_line(fnd_file.log,'Invalid Pay Proposal Id');
end if;
end XX_HK_SAL;
end XX_SEH_PKG_HK_SAL;
/

select * from HR_API_HOOK_CALLS;

step 3:
=======

DECLARE
L_API_HOOK_ID NUMBER:= 3035;
L_API_HOOK_CALL_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_SEQUENCE NUMBER;

BEGIN

SELECT HR_API_HOOKS_S.NEXTVAL
INTO L_SEQUENCE
FROM DUAL;

HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL

(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => TO_DATE('01-JAN-1950','DD-MON-YYYY'),
P_API_HOOK_ID =>L_API_HOOK_ID,
P_API_HOOK_CALL_TYPE => 'PP',
P_SEQUENCE => L_SEQUENCE,
P_ENABLED_FLAG => 'Y',
P_CALL_PACKAGE => 'XX_SS_PKG_HK_SAL',
P_CALL_PROCEDURE => 'XX_HK_SAL',
P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID,
P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.PUT_LINE('L_API_HOOK_CALL_ID '|| L_API_HOOK_CALL_ID);
END;
/

select * from HR_API_HOOK_CALLS where call_package='XX_SS_PKG_HK_SAL';

select * from HR_API_HOOK_CALLS where call_procedure='XX_HK_SAL';

step 4:
=======

declare
l_api_module_id number := 1416; --Value 1416 is derived from Step 1 above using following query
begin
hr_api_user_hooks_utility.create_hooks_one_module(l_api_module_id);
dbms_output.put_line('Success');
exception when others then
dbms_output.put_line('Exception : '||SQLERRM);
end;
/

How to delete a user hook in Oracle apps HRMS?

Before going to delete a user hook we have to find out hook call id and object version number

-> We can find out those values by using the below the query.

SELECT api_hook_call_id,object_version_number
FROM HR_API_HOOK_CALLS
WHERE call_package = 'XX_SS_PKG_HK_SAL'
AND call_procedure = UPPER('XX_HK_SAL');

After finding the api hook call id and OVN

Run the following program

BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1539,
p_object_version_number =>5
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
commit;
END;
/