XX Unaccounted Taxes For GRN'S

/* added by Ram on 07-May-2010 to consider only the unaccounted_tax_lines_sum (including the zero values) for the receipt and not the total_receipt_tax_amount*/
SELECT  B.RECEIPT_NUM
        , SUM(JRLT.TAX_AMOUNT) TAX
        , JRT.TRANSACTION_DATE
FROM    JAI_RCV_LINE_TAXES JRLT
        , JAI_RCV_TRANSACTIONS JRT
        , (select a.receipt_num
                , a.shipment_header_id
                , a.shipment_line_id
         from   (select distinct rsh.receipt_num
                        , rsh.shipment_header_id
                        , rsl.shipment_line_id
                        , rt.transaction_date
                 from   rcv_shipment_lines rsl
                        ,rcv_transactions rt
                        ,rcv_shipment_headers rsh
                 where  exists (SELECT 'X'
                                  FROM JAI_RCV_LINE_TAXES JRLT
                                 WHERE RSL.SHIPMENT_LINE_ID=JRLT.SHIPMENT_LINE_ID)
                 and    rsl.shipment_line_id=rt.shipment_line_id
                 and    rsl.shipment_header_id=rsh.shipment_header_id
                 and    trunc(rt.transaction_date) between trunc(:p_from_date) and trunc(:p_to_date)
                 and    rt.organization_id=nvl(:p_org_id,rt.organization_id)
                 and    rt.transaction_type='RECEIVE') a
        where   not exists (select 'X'
                              from jai_rcv_journal_entries j
                             where a.shipment_line_id=j.shipment_line_id)
        ) b
WHERE   JRLT.SHIPMENT_LINE_ID=B.SHIPMENT_LINE_ID
AND     JRT.TRANSACTION_ID=JRLT.TRANSACTION_ID
GROUP BY B.RECEIPT_NUM, JRT.TRANSACTION_DATE
----HAVING  SUM(JRLT.TAX_AMOUNT)>0


/*  sateesh's previous query
SELECT B.RECEIPT_NUM,SUM(jrlt.TAX_AMOUNT) Tax,jrt.transaction_date
FROM jai_rcv_line_taxes jrlt,jai_rcv_transactions jrt,
(SELECT A.RECEIPT_NUM,A.SHIPMENT_HEADER_ID FROM
(select distinct rsh.receipt_num,RSH.SHIPMENT_HEADER_ID,RSL.SHIPMENT_LINE_ID,RT.TRANSACTION_DATE
from rcv_shipment_lines rsl
        ,rcv_transactions rt
        ,rcv_shipment_headers rsh
where exists     (select 'X'
                 from jai_rcv_line_taxes jrlt
                 where rsl.shipment_line_id=jrlt.shipment_line_id)
and rsl.shipment_line_id=rt.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
and trunc(rt.transaction_date) between trunc(:p_from_date) and trunc(:p_to_date)
and rt.organization_id=:p_org_id
and rt.transaction_type='RECEIVE') A
WHERE NOT EXISTS (SELECT 'X' FROM JAI_RCV_JOURNAL_ENTRIES J
                    WHERE A.SHIPMENT_LINE_ID=J.SHIPMENT_LINE_ID)) B
where jrlt.shipment_header_id=b.shipment_header_id
and jrt.transaction_id=jrlt.transaction_id
GROUP BY B.RECEIPT_NUM,jrt.transaction_date
having SUM(jrlt.TAX_AMOUNT)>0