Monday, 22 September 2014

Cursor showing first group by then having then order by clause (Order Clause)

CURSOR xx_docimg_receipts_in (P_PO_Line_ID IN VARCHAR2) IS
      SELECT rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id,
                            SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                       --  + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) qty_not_billed
      FROM rcv_transactions rt,  rcv_shipment_headers rh, rcv_transactions rt1,
                 ( SELECT  xx.receipt_number,  sum(nvl((xx.amount/(pll.quantity*pll.unit_price)) * pll.quantity,0)) quantity_applied ----Added By Junaid Khan
                        --SUM(NVL(xx.quantity_invoiced,0)) quantity_applied -- Commented By Junaid Khan
                      FROM xx_ap_inv_lines_interface xx,
                     po_lines_all pll
                     WHERE xx.receipt_number IS NOT NULL
                     and xx.po_line_id = pll.po_line_id
                     GROUP BY xx.receipt_number ) xx
      WHERE rt.shipment_header_id = rh.shipment_header_id AND
                        rh.receipt_num = xx.receipt_number(+) AND
                        rt.parent_transaction_id = rt1.transaction_id(+)  AND
                        rt.transaction_type IN ('RECEIVE','RETURN TO VENDOR', 'CORRECT', 'REJECT') AND
                        rt.po_line_id = P_PO_Line_ID
      GROUP BY  rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id
         HAVING  SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                        -- + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) <> 0
      ORDER BY rh.creation_date;