Function to return HSN/SAC code for an item

CREATE OR REPLACE FUNCTION ITEM_HSN_CD (
P_ITEM_ID NUMBER
)
RETURN VARCHAR2
IS
L_REP_CODE VARCHAR2(40);

BEGIN
SELECT JRA.REPORTING_CODE
INTO L_REP_CODE
FROM JAI_REPORTING_ASSOCIATIONS_V JRA
WHERE JRA.ENTITY_CODE = ‘ITEM’
AND REPORTING_TYPE_CODE IN (‘GST_HSN_CODE’ ,’GST_SAC_CODE_ITEM’)
AND ( ENTITY_ID =
(SELECT MIN(JR.TEMPLATE_HDR_ID)
FROM JAI_ITEM_TEMPL_HDR JR
WHERE JR.ENTITY_TYPE_CODE = ‘ITEM’
AND JR.ENTITY_ID = P_ITEM_ID
AND JR.ITEM_CLASSIFICATION = ‘OTIN’
)
OR ENTITY_ID IN
(SELECT JRT.TEMPLATE_HDR_ID
FROM JAI_ITEM_TEMPL_HDR JRT
WHERE JRT.ENTITY_TYPE_CODE = ‘ITEM_TEMPL’
AND JRT.INVENTORY_ITEM_ID = P_ITEM_ID
AND JRT.ITEM_CLASSIFICATION = ‘OTIN’
)
OR ENTITY_ID IN
(SELECT JT.TEMPLATE_HDR_ID
FROM JAI_ITEM_TEMPL_HDR JT
WHERE JT.ENTITY_TYPE_CODE = ‘ITEM_TEMPL_ASGN’
AND JT.ENTITY_ID = P_ITEM_ID
AND JT.INVENTORY_ITEM_ID = P_ITEM_ID
AND JT.ITEM_CLASSIFICATION = ‘OTIN’
));
RETURN L_REP_CODE;
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END ;