Here is the query to to get you the vendor fiscal classification code in Oracle Fusion.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Vendor Fiscal Classification Query
SELECT supp.SEGMENT1 SUPPLIER_NUMBER,
hz.PARTY_NAME PARTY_NAME,
( SELECT Reg.REGISTRATION_NUMBER
FROM hz_party_sites site
, zx_party_tax_profile TaxProf
, zx_registrations Reg
WHERE site.party_id = hz.party_id
AND site.IDENTIFYING_ADDRESS_FLAG = ‘Y’
AND site.status = ‘A’
AND NVL(site.start_date_active,SYSDATE -1)< SYSDATE AND NVL(site.End_date_active,SYSDATE +1)> SYSDATE
AND TaxProf.party_id= site.party_Site_Id
AND Reg.party_tax_profile_id = TaxProf.party_tax_profile_id
AND rownum = 1 — Just as a precaution..
) Reg_Num,
( SELECT Reg.TAX_REGIME_CODE
FROM hz_party_sites site
, zx_party_tax_profile TaxProf
, zx_registrations Reg
WHERE site.party_id = hz.party_id
AND site.IDENTIFYING_ADDRESS_FLAG = ‘Y’
AND site.status = ‘A’
AND NVL(site.start_date_active,SYSDATE -1)< SYSDATE AND NVL(site.End_date_active,SYSDATE +1)> SYSDATE
AND TaxProf.party_id= site.party_Site_Id
AND Reg.party_tax_profile_id = TaxProf.party_tax_profile_id
AND rownum = 1 — Just as a precaution..
) Tax_Reg_Code,
(SELECT HzCodeAssignmentEO.CLASS_CODE
FROM HZ_CODE_ASSIGNMENTS HzCodeAssignmentEO,
zx_fc_types_b zft,
zx_party_tax_profile ptp
WHERE HzCodeAssignmentEO.CLASS_CATEGORY = zft.OWNER_ID_CHAR
AND OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
AND CLASSIFICATION_TYPE_CATEG_CODE = ‘PARTY_FISCAL_CLASS’
AND PTP.party_tax_profile_id = HzCodeAssignmentEO.OWNER_TABLE_ID
AND ptp.party_id = hps.party_site_id
AND rownum = 1) TAX_CLASS_CODE,
NVL(TO_CHAR((SELECT HzCodeAssignmentEO.start_date_active
FROM HZ_CODE_ASSIGNMENTS HzCodeAssignmentEO,
zx_fc_types_b zft,
zx_party_tax_profile ptp
WHERE HzCodeAssignmentEO.CLASS_CATEGORY = zft.OWNER_ID_CHAR
AND OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
AND CLASSIFICATION_TYPE_CATEG_CODE = ‘PARTY_FISCAL_CLASS’
AND PTP.party_tax_profile_id = HzCodeAssignmentEO.OWNER_TABLE_ID
AND ptp.party_id = hps.party_site_id
AND rownum = 1),’MM/DD/YYYY’),”) TAX_CLASS_START_DT,
NVL(DECODE(TO_CHAR((SELECT HzCodeAssignmentEO.end_date_active
FROM HZ_CODE_ASSIGNMENTS HzCodeAssignmentEO,
zx_fc_types_b zft,
zx_party_tax_profile ptp
WHERE HzCodeAssignmentEO.CLASS_CATEGORY = zft.OWNER_ID_CHAR
AND OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
AND CLASSIFICATION_TYPE_CATEG_CODE = ‘PARTY_FISCAL_CLASS’
AND PTP.party_tax_profile_id = HzCodeAssignmentEO.OWNER_TABLE_ID
AND ptp.party_id = hps.party_site_id
AND rownum = 1),’MM/DD/YYYY’),’12/31/4712′,”
, TO_CHAR((SELECT HzCodeAssignmentEO.end_date_active
FROM HZ_CODE_ASSIGNMENTS HzCodeAssignmentEO,
zx_fc_types_b zft,
zx_party_tax_profile ptp
WHERE HzCodeAssignmentEO.CLASS_CATEGORY = zft.OWNER_ID_CHAR
AND OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
AND CLASSIFICATION_TYPE_CATEG_CODE = ‘PARTY_FISCAL_CLASS’
AND PTP.party_tax_profile_id = HzCodeAssignmentEO.OWNER_TABLE_ID
AND ptp.party_id = hps.party_site_id
AND rownum = 1),’MM/DD/YYYY’)),”) TAX_CLASS_END_DT,
hps.PARTY_SITE_NAME ADDRESS_NAME
FROM hz_parties hz,
poz_suppliers supp,
hz_party_sites hps
WHERE hz.party_id=supp.party_id
AND hz.party_id=hps.party_id
AND hz.party_name = NVL(:Supplier_Name, hz.party_name)
AND supp.segment1 = NVL(:Supplier_Number, supp.segment1)
Order by hz.PARTY_NAME
Related Posts:
Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS
Supplier Site Query ( SQL) – General in Fusion
Vendor Site – Invoicing & Payments Query
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
