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;
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;