API Script to create Receipts in oracle receivables

Hi Team,

This API script is useful in oracle receivables.

Thi is useful to create oracle receipts in oracle for the customers that are paid for their invoices in oracle

This API works fine for all the data

CREATE OR REPLACE PACKAGE APPS.XX_AR_RECEIPT_PKG AUTHID CURRENT_USER IS

PROCEDURE CREATE_RECEIPT ( p_receipt_method IN VARCHAR2
,p_account_number IN VARCHAR2
,p_receipt_number IN VARCHAR2
,p_receipt_date IN VARCHAR2
,p_gl_date IN VARCHAR2
,p_currency IN VARCHAR2
,p_receipt_amount IN VARCHAR2
,p_maturity_date IN VARCHAR2
,p_receipt_type IN VARCHAR2
,p_trans_number IN VARCHAR2
,p_customer_name IN VARCHAR2
,p_customer_number IN VARCHAR2
,p_customer_location IN VARCHAR2
,p_invoice_number IN VARCHAR2
,p_installment IN VARCHAR2
,p_apply_date IN VARCHAR2
,p_amount_applied IN VARCHAR2
,p_discount IN VARCHAR2
,p_context_value IN VARCHAR2
,p_main_amount_received IN VARCHAR2
,p_base_interest_calculation IN VARCHAR2
,p_calculated_interest IN VARCHAR2
,p_received_interest IN VARCHAR2
,p_interest_difference_action IN VARCHAR2
,p_write_off_reason IN VARCHAR2);
END XX_AR_RECEIPT_PKG;
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_AR_RECEIPT_PKG IS
—
— +===================================================================+
— | |
— +===================================================================+
— | |
— | Name : XX_AR_RECEIPT_PKG.pkb |
— | |
— | Description: AR-INT-017 – Manual receipt Batch |
— | |
— | |
— | Purpose : AR-INT-017 – Package to import and apply receipts. |

—

—
—
ln_customer_id NUMBER;
ln_org_id NUMBER;
ln_resp_id NUMBER;
ln_user_id NUMBER;
ln_site_use_id NUMBER;
ln_cr_id NUMBER;
ln_resp_app_id NUMBER;
ln_customer_trx_id NUMBER;
ln_remittance_bank_account_id NUMBER;
ln_cash_receipt_id NUMBER;
—
lc_msg_data VARCHAR2(1000);
lc_msg_data_out VARCHAR2(1000);
lc_return_status VARCHAR2(1000);
lc_msg_count VARCHAR2(1000);
lc_sqlerrm VARCHAR2(20000);
lc_location VARCHAR2(1000);
lc_log_trace VARCHAR2(1000);
—
global_attribute_rec_type ar_receipt_api_pub.global_attribute_rec_type;
—
BEGIN
—
ln_org_id := FND_PROFILE.VALUE(‘ORG_ID’);
ln_resp_id := FND_PROFILE.VALUE(‘RESP_ID’);
ln_user_id := FND_PROFILE.value(‘USER_ID’);
—
BEGIN
SELECT responsibility_application_id
INTO ln_resp_app_id
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = ln_user_id
AND RESPONSIBILITY_ID = ln_resp_id;
EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select ln_resp_app_id ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
mo_global.init(‘AR’);
mo_global.set_policy_context(‘S’,ln_org_id);
Fnd_Global.Apps_Initialize(User_Id => ln_user_id , Resp_Id => ln_resp_id, Resp_Appl_Id => ln_resp_app_id);
—
BEGIN
—
SELECT rcta.customer_trx_id
INTO ln_customer_trx_id
FROM ra_customer_trx_all rcta
,hz_cust_accounts_all hcaa
WHERE rcta.trx_number = p_invoice_number
AND ( hcaa.account_number = p_customer_number
OR hcaa.account_name = p_customer_name)
AND rcta.bill_to_customer_id = hcaa.cust_account_id;
EXCEPTION
WHEN OTHERS THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select ln_customer_trx_id ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
BEGIN
—
UPDATE ar_payment_schedules_all
SET global_attribute9 = ‘MANUAL_RECEIPT’
,global_attribute11 = ‘N’
,global_attribute20 = NULL
,selected_for_receipt_batch_id = NULL
WHERE customer_trx_id = ln_customer_trx_id;
EXCEPTION
WHEN OTHERS THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error updating ar_payment_schedules_all ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
BEGIN
SELECT customer_id
INTO ln_customer_id
FROM AR_CUSTOMERS
WHERE customer_number = p_customer_number;
EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select ln_customer_id ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
IF p_customer_location IS NULL THEN
—
BEGIN
SELECT site_use_id
,location
INTO ln_site_use_id
,lc_location
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id IN (SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = ln_customer_id)
AND site_use_code = ‘BILL_TO’
AND primary_flag = ‘Y’
AND org_id = ln_org_id;
EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select site_use_id/LOCATION ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
ELSIF p_customer_location IS NOT NULL THEN
—
BEGIN
SELECT site_use_id
INTO ln_site_use_id
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id IN (SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = ln_customer_id)
AND site_use_code = ‘BILL_TO’
AND LOCATION = p_customer_location
AND org_id = ln_org_id;
—
lc_location := p_customer_location;
—
EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select site_use_id/LOCATION ‘ || ‘ – ‘ || lc_sqlerrm);
END;
—
END IF;
—
BEGIN
—
SELECT (CASE armaa.primary_flag
WHEN ‘Y’ THEN NULL
WHEN ‘N’ THEN cbaua.bank_acct_use_id
END) remittance_bank_account_id
INTO ln_remittance_bank_account_id
FROM ar_receipt_method_accounts_all armaa
,ce_bank_acct_uses_all cbaua
,ar_receipt_methods arm
,ce_bank_accounts cba
WHERE armaa.remit_bank_acct_use_id = cbaua.bank_acct_use_id
AND arm.receipt_method_id = armaa.receipt_method_id
AND cbaua.bank_account_id = cba.bank_account_id
AND cba.bank_account_num = p_account_number
AND arm.name = p_receipt_method;
—
EXCEPTION
WHEN OTHERS THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in select bank_acct_use_id ‘ || ‘ – ‘ || lc_sqlerrm);

END;
—
BEGIN
lc_log_trace := ‘global_attribute_category’;
global_attribute_rec_type.global_attribute_category := p_context_value;
—
lc_log_trace := ‘global_attribute1’;
global_attribute_rec_type.global_attribute1 := p_main_amount_received;
—
lc_log_trace := ‘global_attribute2’;
global_attribute_rec_type.global_attribute2 := p_base_interest_calculation;
—
lc_log_trace := ‘global_attribute3’;
global_attribute_rec_type.global_attribute3 := p_calculated_interest;
—
lc_log_trace := ‘global_attribute4’;
global_attribute_rec_type.global_attribute4 := p_received_interest;
—
lc_log_trace := ‘global_attribute5’;
global_attribute_rec_type.global_attribute5 := p_interest_difference_action;
—
lc_log_trace := ‘global_attribute6’;
global_attribute_rec_type.global_attribute6 := p_write_off_reason;
EXCEPTION
WHEN OTHERS THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error in global_attribute_rec_type ‘ || ‘ – ‘ || lc_log_trace || ‘ – ‘ || lc_sqlerrm);
END;
—
BEGIN
—
/*SELECT cash_receipt_id
INTO ln_cash_receipt_id
FROM ar_cash_receipts_all
WHERE receipt_number = p_receipt_number
AND org_id = ln_org_id;*/
—
SELECT acra.cash_receipt_id
INTO ln_cash_receipt_id
FROM ar_cash_receipts_all acra
,ar_receipt_method_accounts_all armaa
,ce_bank_acct_uses_all cbaua
,ar_receipt_methods arm
,ce_bank_accounts cba
WHERE acra.receipt_number = p_receipt_number
AND acra.receipt_method_id = armaa.receipt_method_id
AND armaa.remit_bank_acct_use_id = cbaua.bank_acct_use_id
AND arm.receipt_method_id = armaa.receipt_method_id
AND cbaua.bank_account_id = cba.bank_account_id
AND cba.bank_account_num = p_account_number
AND arm.name = p_receipt_method
AND acra.org_id = ln_org_id
AND acra.amount = p_receipt_amount
AND acra.currency_code = p_currency;
— End Change Guilherme Marques – IT Convergence – 11/25/2015
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_cash_receipt_id := NULL;

WHEN OTHERS THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error checking if receipt already exists ‘ || lc_sqlerrm);
END;
—
IF ln_cash_receipt_id IS NULL THEN
—
BEGIN
— api para criar receipt and apply
ar_receipt_api_pub.Create_and_apply ( — Standard API Parameters
p_api_version => 1.0 — IN
,p_init_msg_list => fnd_api.g_true — IN
,p_commit => fnd_api.g_false — IN
,p_validation_level => fnd_api.g_valid_level_full — IN
— Create Receipt
,p_receipt_number => p_receipt_number — IN
,p_amount => p_receipt_amount — IN
,p_receipt_date => p_receipt_date — IN
,p_gl_date => p_gl_date — IN
,p_receipt_method_name => p_receipt_method — IN
–,p_customer_id => ln_customer_id — IN
–,p_customer_site_use_id => ln_site_use_id — IN
–,p_location => lc_location — IN
,p_org_id => ln_org_id — IN
,p_currency_code => p_currency — IN
,p_maturity_date => p_maturity_date — IN
,p_remittance_bank_account_id => ln_remittance_bank_account_id — IN
— Apply Receipt
,p_customer_trx_id => ln_customer_trx_id — IN
,p_installment => p_installment — IN
,p_amount_applied => p_amount_applied — IN
,p_apply_date => p_apply_date — IN
,p_discount => p_discount — IN
,app_global_attribute_rec => global_attribute_rec_type — IN
— OUT Standard API Parameters
,x_return_status => lc_return_status — OUT
,x_msg_count => lc_msg_count — OUT
,x_msg_data => lc_msg_data — OUT
,p_cr_id => ln_cr_id — OUT
);

EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error calling API ar_receipt_api_pub.Create_and_apply ‘|| ‘ – ‘ || lc_sqlerrm);
END;
—
IF lc_return_status <> ‘S’ THEN
IF lc_msg_count > 0 THEN
fnd_msg_pub.get (p_msg_index => 1, p_encoded => ‘F’, p_data => lc_msg_data, p_msg_index_out => lc_msg_data_out);
lc_sqlerrm := lc_msg_data;
END IF;
—
raise_application_error (-20001, ‘CREATE_APPLY (‘ || lc_msg_count || ‘) ‘ || lc_sqlerrm );
—
END IF;
—
—
ELSIF ln_cash_receipt_id IS NOT NULL THEN — already exists
—
—
BEGIN

ar_receipt_api_pub.APPLY ( p_api_version => 1.0 — IN
,p_init_msg_list => fnd_api.g_true — IN
,p_commit => fnd_api.g_false — IN
,p_validation_level => fnd_api.g_valid_level_full — IN
,p_cash_receipt_id => ln_cash_receipt_id — IN
,p_customer_trx_id => ln_customer_trx_id — IN
,p_installment => p_installment — IN
,p_amount_applied => p_amount_applied — IN
,p_apply_date => p_apply_date — IN
,p_discount => p_discount — IN
,p_global_attribute_rec => global_attribute_rec_type — IN
,p_org_id => ln_org_id — IN
,x_return_status => lc_return_status — OUT
,x_msg_count => lc_msg_count — OUT
,x_msg_data => lc_msg_data — OUT
);
EXCEPTION
WHEN others THEN
lc_sqlerrm := sqlerrm;
raise_application_error (-20001,’Error calling API ar_receipt_api_pub.APPLY ‘|| ‘ – ‘ || lc_sqlerrm);
END;
—
IF lc_return_status <> ‘S’ THEN
IF lc_msg_count > 0 THEN
fnd_msg_pub.get (p_msg_index => 1, p_encoded => ‘F’, p_data => lc_msg_data, p_msg_index_out => lc_msg_data_out);
lc_sqlerrm := lc_msg_data;
END IF;
—
raise_application_error (-20001, ‘APPLY (‘ || lc_msg_count || ‘) ‘ || lc_sqlerrm );
—
END IF;
—
END IF;
—
END;
END XX_AR_RECEIPT_PKG;
/

UdayKumar Koneti