Vendor Fiscal Classification Query in Oracle Fusion

Here is the query to to get you the vendor fiscal classification code in Oracle Fusion.

Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.

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..