--Item Master with HSN
SELECT msi.segment1 item_code,msi.description,
primary_unit_of_measure uom,
msi.auto_lot_alpha_prefix,msi.start_auto_lot_number,
lot.lot_number,item_type,msi.inventory_item_status_code status_code,
mic.category_concat_segs Item_Classification
,mic.SEGMENT1,mic.SEGMENT3,mic.SEGMENT4
,(SELECT reporting_code
FROM apps.jai_item_templ_hdr jith,
apps.jai_reporting_associations jra,
apps.jai_regimes jr
WHERE jith.template_hdr_id = jra.entity_id
AND jra.reporting_usage = 'LR'--Legal Reporting
AND jra.effective_to IS NULL
AND jr.regime_id = jra.regime_id
AND jr.regime_code = 'GST' ---Depen on setup
AND jith.organization_id = msi.organization_id
AND jith.inventory_item_id = msi.inventory_item_id
GROUP BY reporting_code) HSN_CODE
,(SELECT reporting_code_description
FROM apps.jai_item_templ_hdr jith,
apps.jai_reporting_associations jra
WHERE jith.template_hdr_id = jra.entity_id
AND jra.reporting_usage = 'TD' --Tax Determination
AND jra.effective_to IS NULL
AND jith.organization_id = msi.organization_id
AND jith.inventory_item_id = msi.inventory_item_id
GROUP BY reporting_code_description) TAX_TYPE
FROM mtl_system_items_b msi
,mtl_lot_numbers lot
,mtl_item_categories_v mic
WHERE msi.organization_id=122 ---Master Org
AND (NVL (msi.enabled_flag, 'N') = NVL ('N', 'N') OR NVL ('N', 'N') = 'N')
AND msi.inventory_item_id = lot.inventory_item_id(+)
AND msi.organization_id = lot.organization_id(+)
AND msi.inventory_item_id = mic.inventory_item_id(+)
AND msi.organization_id = mic.organization_id(+)
Order by 1