|
SELECT a.business_unit , a.po_id , a.line_nbr , a.sched_nbr , SUM(b.qty_sh_recvd) FROM PS_PO_LINE_SHIP a , ps_recv_ln_ship b WHERE a.business_unit = b.business_unit_po AND a.po_id = b.po_id AND a.line_nbr = b.line_nbr AND a.sched_nbr = b.sched_nbr AND b.recv_ship_status <> 'X' GROUP BY a.business_unit, a.po_id, a.line_nbr , a.sched_nbr UNION SELECT a.business_unit , a.po_id , a.line_nbr , a.sched_nbr , 0 FROM PS_PO_LINE_SHIP a WHERE NOT EXISTS ( SELECT 'x' FROM ps_recv_ln_ship b WHERE b.business_unit_po = a.business_unit AND b.po_id = a.po_id AND b.line_nbr = a.line_nbr AND b.sched_nbr = a.sched_nbr AND b.recv_ship_status <> 'X')
|