Introduction:
This report will display the HSN summary details.
SELECT ‘HSN Summary’ inv_type
,hsn_data.supp_gstin s_gstin
,hsn_data.period inv_period
,hsn_data.item_nm item_name
,hsn_data.hsn_sac hsn_sac_code
,hsn_data.uom_code trx_uom_code
,SUM(hsn_data.trx_line_qty) trx_line_quantity
,SUM(hsn_data.taxable_amt) taxable_amount
,hsn_data.SGST_RT SGST_RATE
,SUM(hsn_data.SGST_AMT) SGST_AMOUNT
,hsn_data.CGST_RT CGST_RATE
,SUM(hsn_data.CGST_AMT) CGST_AMOUNT
,hsn_data.IGST_RT IGST_RATE
,SUM(hsn_data.IGST_AMT) IGST_AMOUNT
,hsn_data.CESS_RT CESS_RATE
,SUM(hsn_data.CESS_AMT) CESS_AMOUNT
,SUM(hsn_data.total_tax_amt) total_tax_amount
,SUM(hsn_data.gross_amt) gross_amount
,hsn_data.self_assessed self_assessed_flag
FROM
(SELECT jtl.first_party_primary_reg_num supp_gstin
,TO_CHAR(TRUNC(jtl.trx_date),’MMYYYY’) period
,jrc.reporting_code_description item_nm
,jrc.reporting_code hsn_sac
,jdf.trx_uom_code uom_code
,jdf.trx_line_id line_id
,DECODE(jdf.trx_line_quantity,0,rct_line_agg.rct_qty,jdf.trx_line_quantity ) trx_line_qty
–,jdf.unit_price
,XXXX_GBL_JAI_GST_EXTRACT_PKG.get_taxable_amt(jdf.trx_id,jdf.det_factor_id) taxable_amt
,SUM (DECODE (jrav.REPORTING_CODE,’SGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) SGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’SGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) SGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’CGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) CGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’CGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) CGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’IGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) IGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’IGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) IGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’CESS’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) CESS_RT
,SUM (DECODE (jrav.REPORTING_CODE,’CESS’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) CESS_AMT
,SUM(jtl.rounded_tax_amt_fun_curr) total_tax_amt
,XXXX_GBL_JAI_GST_EXTRACT_PKG.get_taxable_amt(jdf.trx_id,jdf.det_factor_id) + SUM(jtl.rounded_tax_amt_fun_curr) gross_amt
,jtl.self_assessed_flag self_assessed
FROM jai_tax_lines jtl
,jai_tax_det_factors jdf
–,jai_rgm_recovery_lines jrrl
,jai_reporting_associations_v jrav
,jai_reporting_codes_v jrc
,ra_customer_trx_all rcta
, (SELECT rctla.customer_trx_id, SUM(rctla.quantity_invoiced) rct_qty
FROM ra_customer_trx_lines_all rctla
GROUP BY rctla.customer_trx_id) rct_line_agg
WHERE jtl.FIRST_PARTY_PRIMARY_REG_NUM = P_REGN_NUMBER
–AND NVL (jtl.SELF_ASSESSED_FLAG, ‘N’) = ‘N’ — not a reverse charge
AND jdf.det_factor_id = jtl.det_factor_id
AND TO_CHAR(TRUNC(jdf.trx_date),’MONYYYY’) = P_PERIOD
–AND jrrl.tax_line_id = jtl.tax_line_id
–AND jrrl.document_id = jtl.trx_id
–AND jrrl.STATUS = ‘CONFIRMED’
–AND NVL (jrrl.LIABILITY_AMOUNT, 0) > 0
AND rcta.customer_trx_id = jtl.trx_id
AND rct_line_agg.customer_trx_id = rcta.customer_trx_id
AND rcta.complete_flag = ‘Y’
AND NVL(jtl.rounded_tax_amt_fun_curr,0) <> 0
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND jrc.reporting_code_id = NVL(hsn_code_id,sac_code_id)
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
–AND jtl.tax_rate_code != ‘ZERO-TAX-EXEMPT’ — Defect 2167
AND jtl.tax_rate_code not in (‘ZERO-TAX-EXEMPT’,’REC ZERO-TAX-EXEMPT’,’OEC ZERO-TAX-EXEMPT’) — Defect 2167
— AND rcta.trx_number=’1703013376′
AND jtl.application_id = 222
GROUP BY jtl.first_party_primary_reg_num
,TO_CHAR(TRUNC(jtl.trx_date),’MMYYYY’)
,jrc.reporting_code_description
,jrc.reporting_code
,jdf.trx_uom_code
,jdf.trx_line_id
,jdf.trx_line_quantity
,rct_line_agg.rct_qty
–,jdf.unit_price
,jtl.tax_rate_percentage
,jtl.actual_tax_rate
,jdf.trx_id
,jdf.det_factor_id
,jtl.self_assessed_flag
) hsn_data
GROUP BY hsn_data.supp_gstin
,hsn_data.period
,hsn_data.item_nm
,hsn_data.hsn_sac
,hsn_data.uom_code
,hsn_data.SGST_RT
,hsn_data.CGST_RT
,hsn_data.IGST_RT
,hsn_data.CESS_RT
,hsn_data.self_assessed
— ORDER BY item_name
UNION
SELECT ‘HSN Summary’ inv_type
,hsn_data.supp_gstin s_gstin
,hsn_data.period inv_period
,hsn_data.item_nm item_name
,hsn_data.hsn_sac hsn_sac_code
,hsn_data.uom_code trx_uom_code
,SUM(hsn_data.trx_line_qty) trx_line_quantity
,SUM(hsn_data.taxable_amt) taxable_amount
,hsn_data.SGST_RT SGST_RATE
,SUM(hsn_data.SGST_AMT) SGST_AMOUNT
,hsn_data.CGST_RT CGST_RATE
,SUM(hsn_data.CGST_AMT) CGST_AMOUNT
,hsn_data.IGST_RT IGST_RATE
,SUM(hsn_data.IGST_AMT) IGST_AMOUNT
,hsn_data.CESS_RT CESS_RATE
,SUM(hsn_data.CESS_AMT) CESS_AMOUNT
,SUM(hsn_data.total_tax_amt) total_tax_amount
,SUM(hsn_data.gross_amt) gross_amount
,hsn_data.self_assessed self_assessed_flag
FROM
(SELECT jtl.first_party_primary_reg_num supp_gstin
,TO_CHAR(TRUNC(jtl.trx_date),’MMYYYY’) period
,jrc.reporting_code_description item_nm
,jrc.reporting_code hsn_sac
,jdf.trx_uom_code uom_code
,jdf.trx_line_id line_id
,DECODE(jdf.trx_line_quantity,0,rct_line_agg.rct_qty,jdf.trx_line_quantity ) trx_line_qty
–,jdf.unit_price
,XXXX_GBL_JAI_GST_EXTRACT_PKG.get_taxable_amt(jdf.trx_id,jdf.det_factor_id) taxable_amt
,SUM (DECODE (jrav.REPORTING_CODE,’SGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) SGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’SGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) SGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’CGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) CGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’CGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) CGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’IGST’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) IGST_RT
,SUM (DECODE (jrav.REPORTING_CODE,’IGST’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) IGST_AMT
,SUM (DECODE (jrav.REPORTING_CODE,’CESS’, NVL (jtl.tax_rate_percentage,jtl.actual_tax_rate))) CESS_RT
,SUM (DECODE (jrav.REPORTING_CODE,’CESS’, (jtl.ROUNDED_TAX_AMT_FUN_CURR))) CESS_AMT
,SUM(jtl.rounded_tax_amt_fun_curr) total_tax_amt
,XXXX_GBL_JAI_GST_EXTRACT_PKG.get_taxable_amt(jdf.trx_id,jdf.det_factor_id) + SUM(jtl.rounded_tax_amt_fun_curr) gross_amt
,jtl.self_assessed_flag self_assessed
FROM jai_tax_lines jtl
,jai_tax_det_factors jdf
–,jai_rgm_recovery_lines jrrl
,jai_reporting_associations_v jrav
,jai_reporting_codes_v jrc
,ra_customer_trx_all rcta
, (SELECT rctla.customer_trx_id, SUM(rctla.quantity_invoiced) rct_qty
FROM ra_customer_trx_lines_all rctla
GROUP BY rctla.customer_trx_id) rct_line_agg
WHERE jtl.FIRST_PARTY_PRIMARY_REG_NUM = P_REGN_NUMBER
–AND NVL (jtl.SELF_ASSESSED_FLAG, ‘N’) = ‘N’ — not a reverse charge
AND jdf.det_factor_id = jtl.det_factor_id
AND TO_CHAR(TRUNC(jdf.trx_date),’MONYYYY’) = P_PERIOD
–AND jrrl.tax_line_id = jtl.tax_line_id
–AND jrrl.document_id = jtl.trx_id
–AND jrrl.STATUS = ‘CONFIRMED’
–AND NVL (jrrl.LIABILITY_AMOUNT, 0) > 0
AND rcta.customer_trx_id = jtl.trx_id
AND rct_line_agg.customer_trx_id = rcta.customer_trx_id
AND rcta.complete_flag = ‘Y’
— AND NVL(jtl.rounded_tax_amt_fun_curr,0) <> 0
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND jrc.reporting_code_id = NVL(hsn_code_id,sac_code_id)
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
–AND jtl.tax_rate_code != ‘ZERO-TAX-EXEMPT’ — Defect 2167
AND jtl.tax_rate_code in (‘ZERO-TAX-EXEMPT’,’REC ZERO-TAX-EXEMPT’,’OEC ZERO-TAX-EXEMPT’) — Defect 2167
— AND rcta.trx_number=’1703013376′
AND jtl.application_id = 222
GROUP BY jtl.first_party_primary_reg_num
,TO_CHAR(TRUNC(jtl.trx_date),’MMYYYY’)
,jrc.reporting_code_description
,jrc.reporting_code
,jdf.trx_uom_code
,jdf.trx_line_id
,jdf.trx_line_quantity
,rct_line_agg.rct_qty
–,jdf.unit_price
,jtl.tax_rate_percentage
,jtl.actual_tax_rate
,jdf.trx_id
,jdf.det_factor_id
,jtl.self_assessed_flag
) hsn_data
GROUP BY hsn_data.supp_gstin
,hsn_data.period
,hsn_data.item_nm
,hsn_data.hsn_sac
,hsn_data.uom_code
,hsn_data.SGST_RT
,hsn_data.CGST_RT
,hsn_data.IGST_RT
,hsn_data.CESS_RT
,hsn_data.self_assessed
ORDER BY item_name
Summary:
This report is used to identified hsn summary details.
