AR Open Receipt Apply to other Receipt by ar_receipt_api_pub.Apply_Open_Receipt API Example
=======================================================================
DECLARE
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
v_r2r_return_status VARCHAR2 (1);
v_r2r_msg_count NUMBER;
v_r2r_msg_data VARCHAR2 (240);
lv_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
lv_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
lv_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
lv_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
lv_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
cursor c_open_rcpts (cp_cus VARCHAR2) IS
SELECT DISTINCT aou.NAME operating_unit, hp.party_name customer_name,
hca.account_number customer_number, arca.receipt_number,
arca.cash_receipt_id,
(g.amount_due_remaining * NVL (g.exchange_rate, 1)
) amount_due_remaining,
arca.org_id
/*,
crh_first_posted.gl_date gl_date, crh_first_posted.gl_date trx_date, arca.status,
arca.TYPE receipt_type, arca.currency_code, arca.receipt_number,
arca.receipt_date, hp.party_name received_from,
hca.account_number customer_no, arca.amount entered_amount,
crh_current.acctd_amount functional_amount,
-1 * (crh_current.amount) total_net_amount, arm.NAME payment_method,
apbb.bank_name bank_account_name,
apbaa.bank_account_num bank_account_num, arca.comments description*/
FROM hz_parties hp,
hz_cust_accounts_all hca,
ar_cash_receipts_all arca,
ar_receipt_methods arm,
ar_receipt_classes arc,
ap_bank_accounts_all apbaa,
ap_bank_branches apbb,
ar_cash_receipt_history_all crh_first_posted,
ar_cash_receipt_history_all crh_current,
hr_all_organization_units aou,
ar_payment_schedules_all g
-- ,hz_party_sites hps,
-- hz_cust_site_uses_all hcsua,
-- hz_cust_acct_sites_all hcas
WHERE 1 = 1
AND arca.org_id = aou.organization_id(+)
-- AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
-- AND hps.party_site_id = hcas.party_site_id
-- and arca.CUSTOMER_SITE_USE_ID=hcsua.BILL_TO_SITE_USE_ID
AND arca.cash_receipt_id = 9882804 --9882805--9882804
AND NVL (arca.status, 'ZZ') <> 'APP' --Open Only
-- AND hca.ACCOUNT_NUMBER=cp_cus
AND arca.pay_from_customer = hca.cust_account_id(+)
AND hp.party_id(+) = hca.party_id
AND arca.remittance_bank_account_id = apbaa.bank_account_id(+)
AND apbb.bank_branch_id(+) = apbaa.bank_branch_id
AND arca.receipt_method_id = arm.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
-- AND crh_first_posted.batch_id = bat.batch_id(+)
-- AND crh_first_posted.org_id = bat.org_id(+)
-- AND bat.TYPE(+) = 'MANUAL'
AND crh_first_posted.cash_receipt_id(+) = arca.cash_receipt_id
AND crh_first_posted.org_id(+) = arca.org_id
AND crh_first_posted.first_posted_record_flag(+) = 'Y'
-- AND crh_first_posted.batch_id = bat_br.batch_id(+)
-- AND crh_first_posted.org_id = bat_br.org_id(+)
AND crh_current.cash_receipt_id = arca.cash_receipt_id
AND crh_current.org_id = arca.org_id
AND crh_current.current_record_flag =
NVL ('Y', arca.receipt_number)
-- and crh_first_posted.gl_date between to_date('01-JAN-2019') and to_date('31-DEC-2019')
--and to_char(crh_first_posted.gl_date,'YYYY')='2019'
AND arca.org_id = 103 ;--MTC
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
BEGIN
fnd_global.apps_initialize (0,
52219,--MTC Receivables Administrator
222,--Receivables
103
);
mo_global.set_policy_context ('S', 103);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
--For yz in c_open_rcpts () loop
--RECEIPT-A :=>To which the open RECEIPT have to apply
ar_receipt_api_pub.apply_open_receipt
(
-- Standard API parameters.
p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_true,
p_validation_level => apps.fnd_api.g_valid_level_full,--100,
x_return_status => v_r2r_return_status,
x_msg_count => v_r2r_msg_count,
x_msg_data => v_r2r_msg_data,
-- Receipt application parameters.
p_cash_receipt_id => 9882805,--To which the open RECEIPT have to apply
p_receipt_number => 'RECEIPT-A', --To which the open RECEIPT have to apply
p_applied_payment_schedule_id => NULL,
p_open_cash_receipt_id => 9882804,--Non Zero Amount Cursor Rcpt
p_open_receipt_number => 'RECEIPT-OPEN' ,--Non Zero Amount Cursor Rcpt
p_open_rec_app_id => null,--NONE,
p_amount_applied => -20000,--Non Zero Amount Cursor Rcpt Amount
p_apply_date => TRUNC (SYSDATE),
p_apply_gl_date => TRUNC (SYSDATE),
p_ussgl_transaction_code => null,--NONE,
p_attribute_rec => NULL,
-- ******* Global Flexfield parameters *******
p_global_attribute_rec => null,--NONE,
p_comments => 'Reciept to reciept Application by API',
p_org_id =>103,
x_application_ref_num =>lv_application_ref_num,
x_receivable_application_id =>lv_receivable_application_id,
x_applied_rec_app_id =>lv_applied_rec_app_id,
x_acctd_amount_applied_from =>lv_acctd_amount_applied_from,
x_acctd_amount_applied_to =>lv_acctd_amount_applied_to
);
Commit;
-- end loop;
dbms_output.put_line('v_r2r_msg_data :-'||v_r2r_msg_data);
dbms_output.put_line('x_application_ref_num :-'||lv_application_ref_num);
dbms_output.put_line('x_receivable_application_id :-'||lv_receivable_application_id);
dbms_output.put_line('x_applied_rec_app_id :-'||lv_applied_rec_app_id);
dbms_output.put_line('x_acctd_amount_applied_from :-'||lv_acctd_amount_applied_from);
dbms_output.put_line('x_acctd_amount_applied_to :-'||lv_acctd_amount_applied_to);
dbms_output.put_line('API Completed Successfully :-'||SQLERRM);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
-- commit;
exception when others then
dbms_output.put_line('Main Exception :-'||SQLERRM);
END;
=======================================================================
DECLARE
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
v_r2r_return_status VARCHAR2 (1);
v_r2r_msg_count NUMBER;
v_r2r_msg_data VARCHAR2 (240);
lv_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
lv_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
lv_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
lv_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
lv_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
cursor c_open_rcpts (cp_cus VARCHAR2) IS
SELECT DISTINCT aou.NAME operating_unit, hp.party_name customer_name,
hca.account_number customer_number, arca.receipt_number,
arca.cash_receipt_id,
(g.amount_due_remaining * NVL (g.exchange_rate, 1)
) amount_due_remaining,
arca.org_id
/*,
crh_first_posted.gl_date gl_date, crh_first_posted.gl_date trx_date, arca.status,
arca.TYPE receipt_type, arca.currency_code, arca.receipt_number,
arca.receipt_date, hp.party_name received_from,
hca.account_number customer_no, arca.amount entered_amount,
crh_current.acctd_amount functional_amount,
-1 * (crh_current.amount) total_net_amount, arm.NAME payment_method,
apbb.bank_name bank_account_name,
apbaa.bank_account_num bank_account_num, arca.comments description*/
FROM hz_parties hp,
hz_cust_accounts_all hca,
ar_cash_receipts_all arca,
ar_receipt_methods arm,
ar_receipt_classes arc,
ap_bank_accounts_all apbaa,
ap_bank_branches apbb,
ar_cash_receipt_history_all crh_first_posted,
ar_cash_receipt_history_all crh_current,
hr_all_organization_units aou,
ar_payment_schedules_all g
-- ,hz_party_sites hps,
-- hz_cust_site_uses_all hcsua,
-- hz_cust_acct_sites_all hcas
WHERE 1 = 1
AND arca.org_id = aou.organization_id(+)
-- AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
-- AND hps.party_site_id = hcas.party_site_id
-- and arca.CUSTOMER_SITE_USE_ID=hcsua.BILL_TO_SITE_USE_ID
AND arca.cash_receipt_id = 9882804 --9882805--9882804
AND NVL (arca.status, 'ZZ') <> 'APP' --Open Only
-- AND hca.ACCOUNT_NUMBER=cp_cus
AND arca.pay_from_customer = hca.cust_account_id(+)
AND hp.party_id(+) = hca.party_id
AND arca.remittance_bank_account_id = apbaa.bank_account_id(+)
AND apbb.bank_branch_id(+) = apbaa.bank_branch_id
AND arca.receipt_method_id = arm.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
-- AND crh_first_posted.batch_id = bat.batch_id(+)
-- AND crh_first_posted.org_id = bat.org_id(+)
-- AND bat.TYPE(+) = 'MANUAL'
AND crh_first_posted.cash_receipt_id(+) = arca.cash_receipt_id
AND crh_first_posted.org_id(+) = arca.org_id
AND crh_first_posted.first_posted_record_flag(+) = 'Y'
-- AND crh_first_posted.batch_id = bat_br.batch_id(+)
-- AND crh_first_posted.org_id = bat_br.org_id(+)
AND crh_current.cash_receipt_id = arca.cash_receipt_id
AND crh_current.org_id = arca.org_id
AND crh_current.current_record_flag =
NVL ('Y', arca.receipt_number)
-- and crh_first_posted.gl_date between to_date('01-JAN-2019') and to_date('31-DEC-2019')
--and to_char(crh_first_posted.gl_date,'YYYY')='2019'
AND arca.org_id = 103 ;--MTC
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
BEGIN
fnd_global.apps_initialize (0,
52219,--MTC Receivables Administrator
222,--Receivables
103
);
mo_global.set_policy_context ('S', 103);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
--For yz in c_open_rcpts () loop
--RECEIPT-A :=>To which the open RECEIPT have to apply
ar_receipt_api_pub.apply_open_receipt
(
-- Standard API parameters.
p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_true,
p_validation_level => apps.fnd_api.g_valid_level_full,--100,
x_return_status => v_r2r_return_status,
x_msg_count => v_r2r_msg_count,
x_msg_data => v_r2r_msg_data,
-- Receipt application parameters.
p_cash_receipt_id => 9882805,--To which the open RECEIPT have to apply
p_receipt_number => 'RECEIPT-A', --To which the open RECEIPT have to apply
p_applied_payment_schedule_id => NULL,
p_open_cash_receipt_id => 9882804,--Non Zero Amount Cursor Rcpt
p_open_receipt_number => 'RECEIPT-OPEN' ,--Non Zero Amount Cursor Rcpt
p_open_rec_app_id => null,--NONE,
p_amount_applied => -20000,--Non Zero Amount Cursor Rcpt Amount
p_apply_date => TRUNC (SYSDATE),
p_apply_gl_date => TRUNC (SYSDATE),
p_ussgl_transaction_code => null,--NONE,
p_attribute_rec => NULL,
-- ******* Global Flexfield parameters *******
p_global_attribute_rec => null,--NONE,
p_comments => 'Reciept to reciept Application by API',
p_org_id =>103,
x_application_ref_num =>lv_application_ref_num,
x_receivable_application_id =>lv_receivable_application_id,
x_applied_rec_app_id =>lv_applied_rec_app_id,
x_acctd_amount_applied_from =>lv_acctd_amount_applied_from,
x_acctd_amount_applied_to =>lv_acctd_amount_applied_to
);
Commit;
-- end loop;
dbms_output.put_line('v_r2r_msg_data :-'||v_r2r_msg_data);
dbms_output.put_line('x_application_ref_num :-'||lv_application_ref_num);
dbms_output.put_line('x_receivable_application_id :-'||lv_receivable_application_id);
dbms_output.put_line('x_applied_rec_app_id :-'||lv_applied_rec_app_id);
dbms_output.put_line('x_acctd_amount_applied_from :-'||lv_acctd_amount_applied_from);
dbms_output.put_line('x_acctd_amount_applied_to :-'||lv_acctd_amount_applied_to);
dbms_output.put_line('API Completed Successfully :-'||SQLERRM);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
-- commit;
exception when others then
dbms_output.put_line('Main Exception :-'||SQLERRM);
END;
No comments:
Post a Comment