Pending Transaction Quantity Done in Sona

SELECT rsh.receipt_num,

            poh.segment1 po_num, 

            rr.routing_name,                     

              NVL (q1.receives, 0) "RECEIVED_QUANTITY",

           (NVL (q2.accepts, 0) + NVL (q3.rejects, 0)) "INSPECTED QTY",

               NVL (q2.accepts, 0) accepts, NVL (q3.rejects, 0) rejects,

               NVL (q4.deliver, 0) "DELIVERED_QUANTITY",

               (NVL (q1.receives, 0)-(NVL (q2.accepts, 0) + NVL (q3.rejects, 0)))

                                                                                                      "PENDING FOR INSPECTION",

               (NVL (q1.receives, 0) - NVL (q4.deliver, 0)) "PENDING FOR DELIVERY"

FROM    po_headers_all poh,

            rcv_shipment_lines rsl,

            rcv_shipment_headers rsh,

            rcv_transactions rt,

            rcv_routing_headers  rr,

           (SELECT transaction_type, shipment_header_id, SUM (quantity) receives

              FROM rcv_transactions

            WHERE transaction_type = 'RECEIVE'

            AND organization_id=247        

                GROUP BY transaction_type,shipment_header_id) q1,

             (SELECT transaction_type, shipment_header_id, SUM (quantity) accepts

             FROM rcv_transactions

           WHERE NVL (transaction_type, 0) = 'ACCEPT'

           AND organization_id=247

              GROUP BY transaction_type,shipment_header_id) q2,

            (SELECT transaction_type, shipment_header_id, NVL (SUM (quantity), 0) rejects

          FROM rcv_transactions

          WHERE NVL (transaction_type, 0) = 'REJECT'

             AND organization_id=247

          GROUP BY transaction_type,shipment_header_id) q3,

          (SELECT shipment_header_id, transaction_type,nvl(SUM(quantity),0) deliver

           FROM rcv_transactions

          WHERE nvl(transaction_type, 0) = 'DELIVER'

           AND organization_id=247

          GROUP BY transaction_type, shipment_header_id) q4         

WHERE 1=1

 AND  rsl.po_header_id(+) = poh.po_header_id

 AND  rsh.shipment_header_id = rsl.shipment_header_id

 AND  rsl.shipment_line_id = rt.shipment_line_id(+)

 AND  q1.shipment_header_id(+) = rt.shipment_header_id

 AND  q2.shipment_header_id(+) = rt.shipment_header_id

 AND  q3.shipment_header_id(+) = rt.shipment_header_id

 AND  q4.shipment_header_id(+) = rt.shipment_header_id

 AND  rr.routing_header_id=rt.routing_header_id

 AND  rr.routing_header_id in(1,2)

 AND (NVL (q1.receives, 0) - NVL (q4.deliver, 0)) > 0

 AND  rsh.receipt_num=1173

 AND  rsh.creation_date BETWEEN NVL(:FROM_RECEIPT_DATE,rsh.creation_date) 

                                    AND NVL(:TO_RECEIPT_DATE,rsh.creation_date)                   

 AND  poh.org_id=246

GROUP BY poh.segment1,

         rsh.receipt_num,

         rr.routing_name,

         q1.receives,

         q2.accepts,

         q3.rejects,

         q4.deliver

order by 1