Friday, April 12, 2013
In R12, we have E-Business Tax module to maintain the Tax
Details. Every TAX_RATE_CODE is attached to a TAX_REGIME and in-turn a
TAX_REGIME is tagged to one or more Operating Units. Below query is used to
list down the TAX_RATE_CODES attached to a Operating Unit.
SELECT hou.organization_id,
hou.set_of_books_id,
hou.default_legal_context_id,
hou.short_code,
hou.NAME,
zru.first_pty_org_id
party_tax_profile_id,
zru.tax_regime_id,
zru.tax_regime_code,
zxr.tax,
zxr.tax_status_code,
zxr.tax_rate_code,
zxr.tax_jurisdiction_code,
zxr.rate_type_code,
zxr.recovery_type_code,
zxr.percentage_rate,
zxr.tax_rate_id,
zxr.effective_from,
zxr.effective_to,
zxr.active_flag,
zxr.attribute3,
zxr.offset_tax,
zxr.offset_status_code,
zxr.offset_tax_rate_code
FROM zx_party_tax_profile ptp,
zx_subscription_details zsd,
hr_operating_units hou,
zx_regimes_usages zru,
zx_rates_vl zxr
WHERE zxr.tax_regime_code = zru.tax_regime_code
AND ptp.party_type_code = 'OU'
AND ptp.party_id = hou.organization_id
AND zru.first_pty_org_id = ptp.party_tax_profile_id
AND zru.first_pty_org_id = zsd.first_pty_org_id
AND zsd.tax_regime_code = ZRU.TAX_REGIME_CODE
AND zsd.parent_first_pty_org_id =
-99
AND SYSDATE BETWEEN zsd.effective_from AND NVL(zsd.effective_to,SYSDATE);
Below query is to list down the tax account based on
Operating Unit,
SELECT hou.organization_id,
hou.NAME,
tax_account_ccid,
zxr.tax,
zxr.tax_status_code,
zxr.tax_regime_code,
zxr.tax_rate_code,
zxr.tax_jurisdiction_code,
zxr.rate_type_code,
zxr.percentage_rate,
zxr.tax_rate_id,
zxr.effective_from,
zxr.effective_to,
zxr.active_flag
FROM zx_rates_vl zxr,
zx_accounts b,
hr_operating_units hou
WHERE b.internal_organization_id =
hou.organization_id
AND b.tax_account_entity_code = 'RATES'
AND b.tax_account_entity_id =
zxr.tax_rate_id
AND zxr.active_flag = 'Y'
AND SYSDATE BETWEEN zxr.effective_from AND nvl(zxr.effective_to, SYSDATE);
Friday, April 12, 2013 by Team search · 4
Assign Receipt method to Customer via API in Oracle Apps R12 (hz_payment_method_pub.create_payment_method)
In this post, we have given a sample script to assign a
payment method to Customer account in R12.
Test instance:
R12
Script:
SET SERVEROUTPUT ON;
DECLARE
vl_payment_method_name VARCHAR2(50) := 'AP/AR Netting';
vl_cust_orig_sys_reference VARCHAR2(50) := '17312';
vl_site_use_orig_sys_reference VARCHAR2(50) := '34914';
vl_pay_method_rec hz_payment_method_pub.payment_method_rec_type;
vl_method_id_num NUMBER;
vl_acc_id_num NUMBER;
vl_party_id_num NUMBER;
vl_site_id_num NUMBER;
vg_status_txt VARCHAR2 (1);
vg_msg_cnt_num NUMBER;
vg_msg_data_txt VARCHAR2 (2000);
vl_cust_receipt_method_id NUMBER;
lv_cust_rec_met_id NUMBER;
BEGIN
--
Apps initialize
fnd_global.APPS_INITIALIZE (1119, 50937, 222);
mo_global.init('AR');
BEGIN
SELECT receipt_method_id
INTO vl_method_id_num
FROM ar_receipt_methods
WHERE NAME = vl_payment_method_name
AND SYSDATE BETWEEN
(nvl(start_date,SYSDATE - 1))
AND (nvl(end_date,SYSDATE + 1));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error deriving Receipt Method ID - ' || SQLERRM);
END;
BEGIN
SELECT hca.cust_account_id,
hca.party_id
INTO vl_acc_id_num,
vl_party_id_num
FROM hz_cust_accounts hca
WHERE
hca.orig_system_reference = TRIM(vl_cust_orig_sys_reference);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error deriving Cust Account ID - ' || SQLERRM);
END;
BEGIN
SELECT hcsua.site_use_id
INTO vl_site_id_num
FROM hz_cust_site_uses_all hcsua
WHERE
hcsua.orig_system_reference = vl_site_use_orig_sys_reference
AND hcsua.site_use_code IN ('BILL_TO', 'SHIP_TO');
dbms_output.put_line('vl_site_id_num :'||vl_site_id_num);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error deriving Site Use ID - ' || SQLERRM);
END;
vl_pay_method_rec.cust_account_id
:= vl_acc_id_num;
vl_pay_method_rec.receipt_method_id := vl_method_id_num;
vl_pay_method_rec.primary_flag
:= 'Y';
vl_pay_method_rec.site_use_id
:= vl_site_id_num;
vl_pay_method_rec.start_date
:= SYSDATE;
vl_pay_method_rec.end_date := NULL;
hz_payment_method_pub.create_payment_method
(p_init_msg_list =>
fnd_api.g_false,
p_payment_method_rec => vl_pay_method_rec,
x_cust_receipt_method_id => vl_cust_receipt_method_id,
x_return_status =>
vg_status_txt,
x_msg_count => vg_msg_cnt_num,
x_msg_data => vg_msg_data_txt);
dbms_output.put_line('PAYMENT METHOD ');
dbms_output.put_line('return_status=' ||
substr(vg_status_txt,1,255));
dbms_output.put_line('count=' ||
to_char(vg_msg_cnt_num));
dbms_output.put_line('Msg_data = ' ||
substr(vg_msg_data_txt,1,255));
IF (nvl(vg_status_txt,'X') !=
fnd_api.g_ret_sts_success)
THEN
IF vg_msg_cnt_num >= 1
THEN
dbms_output.put_line('Create Payment Methods : ' || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255));
END IF;
ELSE
BEGIN
SELECT cust_receipt_method_id
INTO lv_cust_rec_met_id
FROM ra_cust_receipt_methods
WHERE
customer_id = vl_acc_id_num
AND site_use_id = vl_site_id_num
AND receipt_method_id =
vl_method_id_num;
dbms_output.put_line('Sucessfully Created the Payment Methods
(cust_receipt_method_id) :'||lv_cust_rec_met_id);
END;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unknown error during call to Create Payment Methods : ' || SQLERRM);
END;
/
I hope it helps you!!! If so, please provide your valuable comments and
share it. Thank You!!
by Team search · 6
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.