CENVAT CLAIM - INDIA TAX PART

SELECT   ROWNUM, bb.receipt_num, bb.dat, bb.vendor_name,
         bb.excise_duty_reg_no, bb.vendor_type, bb.cenvat_amount, bb.se,
         bb.CV, bb.ae, bb.ec, bb.sc, bb.organization_id, bb.description,
         bb.unit_of_measure, bb.item_code, bb.excise_invoice_no,
         bb.excise_invoice_date, bb.quantity_shipped, bb.inventory_item_id,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.cenvat_amount
            WHEN bb.vendor_type IS NULL
               THEN bb.cenvat_amount
            ELSE 0
         END mfg_amt_cen,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.cenvat_amount
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.cenvat_amount
            ELSE 0
         END dea_amt_cen,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.CV
            WHEN bb.vendor_type IS NULL
               THEN bb.CV
            ELSE 0
         END mfg_amt_cvd,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.CV
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.CV
            ELSE 0
         END dea_amt_cvd,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.ec
            WHEN bb.vendor_type IS NULL
               THEN bb.ec
            ELSE 0
         END mfg_amt_ec,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.ec
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.ec
            ELSE 0
         END dea_amt_ec,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.sc
            WHEN bb.vendor_type IS NULL
               THEN bb.sc
            ELSE 0
         END mfg_amt_sc,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.sc
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.sc
            ELSE 0
         END dea_amt_sc,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type IS NULL AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            ELSE 0
         END nos_m,
         CASE
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type IS NULL AND bb.unit_of_measure = 'KGM'
               THEN bb.quantity_shipped
            ELSE 0
         END kgs_m,
         CASE
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'PKT' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END pcs_m,
         CASE
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'MTR' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END mtr_m,
         CASE
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Litre' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END ltr_m,
         CASE                                                  --DEALER
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            ELSE 0
         END nos_d,
         CASE
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END kgs_d,
         CASE
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END pcs_d,
         CASE
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SHE'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SHE'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END mtr_d,
         CASE
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END ltr_d
    FROM (SELECT DISTINCT b.receipt_num, b.dat, b.vendor_name,
                          b.excise_duty_reg_no, b.vendor_type,
                          b.cenvat_amount, SUM (b.sed) se, SUM (b.cvd) CV,
                          SUM (b.aed) ae, SUM (b.edu_cess) ec,
                          SUM (b.she_cess) sc, b.organization_id,
                          b.description, b.unit_of_measure, b.item_code,
                          b.inventory_item_id, b.excise_invoice_no,
                          b.excise_invoice_date, b.quantity_shipped
                     FROM (SELECT DISTINCT rsh.receipt_num,
                                           jcr2a.transaction_date dat,
                                           ap.vendor_name,
                                           jcvs.excise_duty_reg_no,
                                           NVL (jcvs.vendor_type,
                                                NULL
                                               ) vendor_type,
                                           jrcc.cenvat_amount,
                                           rsl.quantity_shipped, NULL sed,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                      rsl.shipment_header_id
                                               AND tax_type LIKE 'CVD%') cvd,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                      rsl.shipment_header_id
                                               AND tax_type = 'ADDITIONAL_CVD')
                                                                          aed,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                        rsl.shipment_header_id
                                               AND tax_type =
                                                       'EXCISE_EDUCATION_CESS')
                                                                     edu_cess,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                        rsl.shipment_header_id
                                               AND tax_type =
                                                          'EXCISE_SH_EDU_CESS')
                                                                     she_cess,
                                           jrl.organization_id,
                                           msib.description,
                                           rsl.unit_of_measure,
                                           msib.segment1 item_code,
                                           jrl.excise_invoice_no,
                                           jrl.excise_invoice_date,
                                           msib.inventory_item_id
                                      FROM rcv_shipment_headers rsh,
                                           rcv_shipment_lines rsl,
                                           jai_rcv_lines jrl,
                                           jai_rcv_headers jrh,
                                           ap_suppliers ap,
                                           jai_cmn_vendor_sites jcvs,
                                           po_lines_all pol,
                                           jai_rcv_cenvat_claims jrcc,
                                           jai_rcv_line_taxes jrlt,
                                           mtl_system_items_b msib,
                                           jai_cmn_rg_23ac_ii_trxs jcr2a
                                     WHERE rsh.shipment_header_id =
                                                        rsl.shipment_header_id
                                       AND jcr2a.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND TO_CHAR (jcr2a.transaction_date) =
                                                  TO_CHAR (jrcc.creation_date)
                                       AND jcr2a.organization_id =
                                                           jrl.organization_id
                                       AND jrl.excise_invoice_no =
                                                       jcr2a.excise_invoice_no
                                       AND jrl.claim_modvat_flag = 'Y'
                                       AND jrh.shipment_header_id =
                                                        rsh.shipment_header_id
                                       AND jrl.shipment_header_id =
                                                        jrh.shipment_header_id
                                       AND jrl.shipment_line_id =
                                                          rsl.shipment_line_id
                                       AND ap.vendor_id = rsh.vendor_id
                                       AND jcvs.vendor_id = ap.vendor_id
                                       AND jcvs.vendor_site_id = 0
                                       AND pol.po_header_id = rsl.po_header_id
                                       AND jrcc.shipment_line_id =
                                                          rsl.shipment_line_id
                                       AND jrlt.shipment_header_id =
                                                        rsh.shipment_header_id
                                       AND msib.inventory_item_id =
                                                                   rsl.item_id
                                       AND jrl.organization_id =
                                                          msib.organization_id
                                       AND jrl.organization_id =
                                              NVL (:p_org,
                                                   jrl.organization_id)
                                       AND TRUNC (jcr2a.transaction_date)
                                              BETWEEN NVL
                                                        (TO_DATE
                                                                (:p_start_date),
                                                         TRUNC
                                                            (jcr2a.transaction_date
                                                            )
                                                        )
                                                  AND NVL
                                                         (TO_DATE (:p_end_date),
                                                          TRUNC (SYSDATE)
                                                         )) b
                 GROUP BY b.receipt_num,
                          b.dat,
                          b.vendor_name,
                          b.excise_duty_reg_no,
                          b.vendor_type,
                          b.cenvat_amount,
                          b.sed,
                          b.cvd,
                          b.aed,
                          b.edu_cess,
                          b.she_cess,
                          b.organization_id,
                          b.description,
                          b.unit_of_measure,
                          b.item_code,                
                          b.excise_invoice_no,
                          b.excise_invoice_date,
                          b.quantity_shipped,
                          b.inventory_item_id
                 ORDER BY b.receipt_num) bb
ORDER BY ROWNUM