No, there are no documented APIs are available for this requirement, or that is what claimed by Oracle community MOSC support on different discussions. So what happens when the country where you live and work decides to implement Tax (example, Middle East countries) and you were using Oracle applications(EBS) over a decade already without Tax setup? Are you going to open up each and every other customer/supplier account and setup the Tax profile? We are using Multi-organization structure and we have 8 companies under the same business group. We’ve 13,000 unique parties in our database and most of them are linked to all these organizations! It didn’t look good for us, I am sure it doesn’t look good for anyone else either!
Then I came across a poorly constructed sample for an API “zx_registrations_pkg.insert_row” that is “not well documented” by Oracle. This means, Oracle doesn’t categorize this API as public & they will not support the customer if something goes wrong while using this API. Hence, make sure that you are going to use an API that could turn stuffs into a nightmare.
Let’s see how to use this API now. Some technical details for better understanding where to look for some mandatory elements. Every time a new site created for customer or supplier, the TAX profile table (ZX_PARTY_TAX_PROFILE) is inserted with a new row, bearing the PARTY_ID column populated with newly created Site’s id (PARTY_SITE_ID) value. Given example below, I am setting up Tax profiles for customers and suppliers that were created for a new organization bearing organization id “285”
For the purpose, I created a new view comprise of both customer and supplier sites details. Please judge me because I am using “A” and “B” for aliases ;)
CREATE VIEW XXKCC_VAT_TABLE_V AS SELECT
hcas.org_id, ac.customer_id party_id, ac.customer_number party_number, ac.customer_name party_name,ac.customer_type party_type, hcas.party_site_id, 'CUSTOMER' as party_classification
FROM
hz_cust_acct_sites_all hcas
inner join ar_customers ac on ac.customer_id= hcas.cust_account_id
where
1=1
and hcas.status='A'
AND ac.status ='A'
AND NVL(ac.customer_type,'R') ='R'--Account type is empty for maximum bahrain customers
AND hcas.org_id=285
UNION ALL
select b.org_id, b.vendor_id party_id, A.segment1 party_number, a.vendor_name,NULL party_type, b.party_site_id, 'SUPPLIER' as party_classification
from AP_SUPPLIERS A
inner join AP_SUPPLIER_SITES_ALL B ON B.VENDOR_ID = A.VENDOR_ID
where B.ORG_ID = 285
/
I am using two different staging tables with VAT/TRN numbers & aliased as “B” in the following PL/SQL blocks. I executed the same script twice, once for customer and other time for supplier. Please note, I have hard coded many parameters, including TAX_AUTHORITY_ID. Make sure that you change such information to suit your environment. Although, we applied this solution to PRODUCTION, I would recommend everyone to practice caution, make backups.
DECLARE
CURSOR CUSTOMER_RECORD IS
SELECT B.VAT_TRN_NUMBER vat_registration_num,'VAT' REGISTRATION_TYPE_CODE, 'UP' ROUNDING_RULE_CODE,'TAX BH JURISDICTION' TAX_JURISDICTION_CODE,
'REGISTERED' REGISTRATION_STATUS_CODE, 'TAX BAHRAIN' TAX, 'TAX REGIME BAHRAIN' TAX_REGIME_CODE,5379228 TAX_AUTHORITY_ID,'N' SELF_ASSESS_FLAG,'IMPLICIT' REGISTRATION_SOURCE_CODE,
'N' INCLUSIVE_TAX_FLAG,'USER_DEFINED' RECORD_TYPE_CODE,
A.* FROM XXKCC_VAT_TABLE_V A
INNER JOIN XX_BAH_CUSTOMER_T B ON B.ERP_CUSTOMER_ID = A.PARTY_ID
WHERE
1=1
AND A.PARTY_CLASSIFICATION='CUSTOMER'
AND B.VAT_TRN_NUMBER IS NOT NULL;
--AND A.PARTY_SITE_ID=3702712;
g_return_status varchar2(1);
l_party_tax_profile_id NUMBER;
BEGIN
for i in CUSTOMER_RECORD loop
BEGIN
Select PARTY_TAX_PROFILE_ID into l_party_tax_profile_id from
ZX_PARTY_TAX_PROFILE
where party_id = i.party_site_id;
EXCEPTION
when no_data_found then
l_party_tax_profile_id:=NULL;
END;
IF l_party_tax_profile_id IS NOT NULL THEN
zx_registrations_pkg.insert_row(
p_request_id => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_rounding_rule_code => i.ROUNDING_RULE_CODE
,p_validation_rule => NULL
,p_tax_jurisdiction_code => i.TAX_JURISDICTION_CODE
,p_self_assess_flag => i.SELF_ASSESS_FLAG
,p_registration_status_code => i.REGISTRATION_STATUS_CODE
,p_registration_source_code => i.REGISTRATION_SOURCE_CODE
,p_registration_reason_code => NULL
,p_tax => i.TAX
,p_tax_regime_code => i.TAX_REGIME_CODE
,p_inclusive_tax_flag => i.INCLUSIVE_TAX_FLAG
,p_effective_from => SYSDATE
,p_effective_to => NULL
,p_rep_party_tax_name => NULL
,p_default_registration_flag => NULL
,p_bank_account_num => NULL
,p_record_type_code => i.RECORD_TYPE_CODE
,p_legal_location_id => NULL
,p_tax_authority_id => i.TAX_AUTHORITY_ID
,p_rep_tax_authority_id => NULL
,p_coll_tax_authority_id => NULL
,p_registration_type_code => i.REGISTRATION_TYPE_CODE
,p_registration_number => i.vat_registration_num
,p_party_tax_profile_id => l_party_tax_profile_id
,p_legal_registration_id => NULL
,p_bank_id => NULL
,p_bank_branch_id => NULL
,p_account_site_id => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute_category => NULL
,p_program_login_id => NULL
,p_account_id => NULL
,p_tax_classification_code => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,x_return_status => g_return_status
);
END IF;
DBMS_OUTPUT.PUT_LINE(i.PARTY_NUMBER||', '||i.party_name||' Process status : '||g_return_status);
--"S" for success, any other means trouble ;)
END loop;
--COMMIT;
END;
/That’s all folks. Have questions? Use the comments section to let me know.