Supplier Tax Details Report in Oracle Fusion Procurement

--Supplier Tax Details Report in Oracle Fusion Procurement
----------------------------------------------------------
SELECT DISTINCT 'Supplier'                         AS partytype,
                sup.segment1                       AS partynumber,
                hp.party_name                      AS partyname,
                -- Fixed: Using TCA Party Name
                txp.process_for_applicability_flag AS "Allow tax applicability",
                'Header'                           AS rounding_level,
                'Nearest'                          AS rounding_rule,
                txp.tax_classification_code        AS "TAX CLASSIFICATION",
                zr.inclusive_tax_flag              AS taxinclusive,
                txp.allow_offset_tax_flag          AS "Allow offset taxes",
                NULL                               AS "Country code",
                NULL                               AS "tax registration type",
                NULL                               AS "Registration number"
FROM   poz_suppliers sup,
       hz_parties hp,-- Added: TCA Parties table
       zx_party_tax_profile txp,
       zx_registrations zr
WHERE  1 = 1
       -- Join Fusion Supplier to TCA Parties
       AND sup.party_id = hp.party_id
       -- Join TCA Party ID to Tax Profile Party ID
       AND sup.party_id = txp.party_id
       AND txp.party_tax_profile_id = zr.party_tax_profile_id
       -- Date Track/Active Validation
/*AND NVL(zr.effective_to, SYSDATE + 1) > SYSDATE
AND TRUNC(sup.last_update_date) <= TO_DATE('2025-10-20', 'YYYY-MM-DD') */
       -- Filter out Employee and Intercompany supplier types using Fusion Lookups
       AND Nvl((SELECT flv.meaning
                FROM   fnd_lookup_values_vl flv
                WHERE  flv.lookup_code = sup.vendor_type_lookup_code
                       AND flv.lookup_type = 'VENDOR TYPE'
                       AND flv.enabled_flag = 'Y'
                       /*AND TRUNC(SYSDATE) BETWEEN NVL(flv.start_date_active, TRUNC(SYSDATE)) AND NVL(flv.end_date_active, TRUNC(SYSDATE)) */
                       AND rownum = 1), sup.vendor_type_lookup_code) NOT IN (
           'Employee', 'Intercompany' )