JA India GSTR-3B Return

            SELECT SUM(line_amt) taxable_value,
              SUM(tax_dtls.cgst) cgst,
              SUM(tax_dtls.sgst) sgst,
              SUM(tax_dtls.igst) igst,
              SUM(tax_dtls.cess) cess
            FROM jai_tax_det_factors det_fact,
              (SELECT DISTINCT tax.det_factor_id,
                SUM ( DECODE (tax_assc.REPORTING_CODE, 'IGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) igst,
                SUM ( DECODE (tax_assc.REPORTING_CODE, 'CGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) cgst,
                SUM ( DECODE (tax_assc.REPORTING_CODE, 'SGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) sgst,
                SUM ( DECODE (tax_assc.REPORTING_CODE, 'CESS', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) cess
              FROM jai_tax_det_factors tax,
                jai_tax_lines_v tax_lines,
                jai_reporting_associations_v tax_assc,
                jai_rgm_recovery_lines tax_rec
              WHERE to_char(trunc(tax.tax_invoice_date),'MONYYYY') like :P_PERIOD
              AND tax.det_factor_id                     = tax_lines.det_factor_id
              AND tax_assc.ENTITY_CODE                  = 'TAX_TYPE'
              AND tax_assc.REPORTING_TYPE_CODE          = 'TAX_TYPES_CLASSIFICATION'
              AND tax_lines.first_party_primary_reg_num = :P_REGN_NUMBER
                --AND tax_lines.tax_regime_code = 'GST'
              AND tax_rec.tax_line_id        = tax_lines.tax_line_id
              AND tax_lines.tax_type_id      = tax_assc.entity_id
              AND tax_lines.actual_tax_rate <> 0
              AND tax_lines.actual_tax_rate IS NOT NULL
              AND tax_lines.exemption_type  IS NULL
              AND tax.ship_to_country        = 'IN'
              AND tax_rec.liability_amount  <> 0
              AND tax_rec.status             = 'CONFIRMED'
              AND NOT EXISTS
                (SELECT 1
                FROM JAI_PARTY_REGS jpr,
                  JAI_REPORTING_ASSOCIATIONS_V jrav_party
                WHERE UPPER (jrav_party.reporting_code) IN (UPPER ('Deemed Exports EOU'), UPPER ('SEZ'))
                AND jrav_party.ENTITY_CODE               = 'THIRD_PARTY'
                AND jrav_party.REPORTING_TYPE_CODE       = 'THIRD_PARTY_CLASSIFICATION'
                AND jpr.PARTY_REG_ID                     = jrav_party.entity_id
                AND jpr.party_id                         = tax.party_id
                )
              GROUP BY tax.det_factor_id
              ) tax_dtls
            WHERE det_fact.det_factor_id=tax_dtls.det_factor_id

SELECT SUM(line_amt) taxable_value, SUM(tax_dtls.cgst) cgst, SUM(tax_dtls.sgst) sgst, SUM(tax_dtls.igst) igst, SUM(tax_dtls.cess) cess FROM jai_tax_det_factors det_fact, (SELECT DISTINCT tax.det_factor_id, SUM ( DECODE (tax_assc.REPORTING_CODE, 'IGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) igst, SUM ( DECODE (tax_assc.REPORTING_CODE, 'CGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) cgst, SUM ( DECODE (tax_assc.REPORTING_CODE, 'SGST', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) sgst, SUM ( DECODE (tax_assc.REPORTING_CODE, 'CESS', (tax_lines.ROUNDED_TAX_AMT_FUN_CURR))) cess FROM jai_tax_det_factors tax, jai_tax_lines_v tax_lines, jai_reporting_associations_v tax_assc, jai_rgm_recovery_lines tax_rec WHERE to_char(trunc(tax.tax_invoice_date),'MONYYYY') like :P_PERIOD AND tax.det_factor_id = tax_lines.det_factor_id AND tax_assc.ENTITY_CODE = 'TAX_TYPE' AND tax_assc.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION' AND tax_lines.first_party_primary_reg_num = :P_REGN_NUMBER --AND tax_lines.tax_regime_code = 'GST' AND tax_rec.tax_line_id = tax_lines.tax_line_id AND tax_lines.tax_type_id = tax_assc.entity_id AND tax_lines.actual_tax_rate <> 0 AND tax_lines.actual_tax_rate IS NOT NULL AND tax_lines.exemption_type IS NULL AND tax.ship_to_country = 'IN' AND tax_rec.liability_amount <> 0 AND tax_rec.status = 'CONFIRMED' AND NOT EXISTS (SELECT 1 FROM JAI_PARTY_REGS jpr, JAI_REPORTING_ASSOCIATIONS_V jrav_party WHERE UPPER (jrav_party.reporting_code) IN (UPPER ('Deemed Exports EOU'), UPPER ('SEZ')) AND jrav_party.ENTITY_CODE = 'THIRD_PARTY' AND jrav_party.REPORTING_TYPE_CODE = 'THIRD_PARTY_CLASSIFICATION' AND jpr.PARTY_REG_ID = jrav_party.entity_id AND jpr.party_id = tax.party_id ) GROUP BY tax.det_factor_id ) tax_dtls WHERE det_fact.det_factor_id=tax_dtls.det_factor_id