Query to Find HSN Code

Query to Find HSN Code

SELECT REPORTING_CODE
FROM jai_reporting_codes jrc, jai_reporting_types jrt
WHERE jrc.reporting_type_id = jrt.reporting_type_id
AND jrt.reporting_type_code = ‘GST_HSN_CODE’
AND TRUNC (SYSDATE) BETWEEN jrt.effective_from
AND NVL (jrt.effective_to,
TRUNC (SYSDATE + 1)
)
AND TRUNC (SYSDATE) BETWEEN jrc.effective_from
AND NVL (jrc.effective_to,
TRUNC (SYSDATE + 1)
)
AND jrc.reporting_code =
(SELECT reporting_code
FROM jai_reporting_associations jra, jai_regimes jr
WHERE 1 = 1
AND jra.entity_id IN (
SELECT b.template_hdr_id
FROM jai_item_templ_hdr a,
jai_item_templ_hdr b,
mtl_system_items c,
org_organization_definitions d
WHERE a.entity_id = b.template_hdr_id
AND a.inventory_item_id(+) =
c.inventory_item_id
AND c.inventory_item_id = i.inventory_item_id
AND a.organization_id = c.organization_id
AND a.organization_id = d.organization_id
AND a.organization_id = i.warehouse_id)
AND jra.entity_code = ‘ITEM’
AND jra.reporting_type_name =
‘HSN Code Type for GST’
AND jra.regime_id = jr.regime_id
AND TRUNC (SYSDATE) BETWEEN jra.effective_from
AND NVL (jra.effective_to,
TRUNC (SYSDATE)
));