--CREATING A PROCEDURE TO CREATE MISCELLANEOUS RECEIPT FOR 3400 org
CREATE OR REPLACE PROCEDURE xxfin_create_misc_rec_prc(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
ip_receipt_num IN 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 New Custom Receipt Screen';
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 xct.status='Receipt Applied'
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 = 3400
AND lookup_type = 'XX_APPLICATION_INITIATION';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_error_msg := 'select for XX_APPLICATION_INITIATION failed. ';
fnd_file.put_line (fnd_file.LOG, l_error_msg);
RAISE;
WHEN TOO_MANY_ROWS THEN
l_error_msg := 'select for XX_APPLICATION_INITIATION 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 XX_APPLICATION_INITIATION 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:=102;
elsif lv_receipt_method LIKE '%Cheque%' THEN
ln_receipt_method_id:=103;
elsif lv_receipt_method LIKE '%PDC%' THEN
ln_receipt_method_id:=103;
elsif lv_receipt_method LIKE '%Bank Transfer%' THEN
ln_receipt_method_id:=105;
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
, lr_login_variables.resp_id
, lr_login_variables.resp_appl_id );
mo_global.init (gc_application_short_name);
mo_global.set_policy_context ('S', 3400);
fnd_file.put_line(fnd_file.log,'Application Code :' ||gc_application_short_name);
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 => 3400 );
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_rec_prc;
/
--END OF CREATING xxfin_create_misc_rec_prc.
No comments:
Post a Comment
Text Message