Query to find Unaccounted Receipts with Acct Nature is Average Costing

Unaccounted Receipts with Acct Nature is Average Costing


SELECT DISTINCT jrh.receipt_num, jrh.creation_date, jair.shipment_line_id,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id = jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE') excise,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                 jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_EDUCATION_CESS')
                                                        excise_education_cess,
                (SELECT SUM (tax_amount)
                   FROM jai_rcv_line_taxes
                  WHERE shipment_header_id =
                                    jrh.shipment_header_id
                    AND shipment_line_id = jair.shipment_line_id
                    AND UPPER (tax_type) = 'EXCISE_SH_EDU_CESS')
                                                           excise_sh_edu_cess
           FROM jai_rcv_headers jrh,
                jai_rcv_lines jair,
                jai_rcv_line_taxes jtax
          WHERE jrh.online_claim_flag = 'Y'
            AND jair.shipment_header_id = jrh.shipment_header_id
            AND jair.online_claim_flag = 'Y'
            AND jtax.shipment_header_id = jrh.shipment_header_id
            AND jtax.shipment_line_id = jair.shipment_line_id
            AND jrh.organization_id = :P_ORGN_ID
            AND TRUNC (jrh.creation_date) BETWEEN :p_from_date AND :p_to_date
            --AND jrh.receipt_num = '961664'          -- IN ('961033', '961664')
            AND EXISTS (
                   SELECT '1'
                     FROM jai_rcv_journal_entries
                    WHERE shipment_line_id = jtax.shipment_line_id
                      AND acct_nature = 'Average Costing'
                      AND organization_code =:P_ORGN_CODE
                      AND transaction_type = 'DELIVER'
                      AND TRUNC (transaction_date) BETWEEN :p_from_date
                                                       AND :p_to_date
                      AND jrh.receipt_num NOT IN (
                             SELECT jrj.receipt_num
                               FROM jai_rcv_journal_entries jrj
                              WHERE acct_nature ='CENVAT'
                                AND jrj.organization_code = :P_ORGN_CODE 
                                AND transaction_type = 'RECEIVE'
                                -- AND jrj.receipt_num IN (961033, 961664)
                                AND TRUNC (transaction_date)
                                       BETWEEN :p_from_date
                                           AND :p_to_date))
            AND UPPER (jtax.tax_type) IN
                    ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS')