select vendor_name,sum(receive_qty) from (SELECT NVL (
NVL (
(SELECT vendor_name
FROM rcv_shipment_headers rt, po_vendors pv
WHERE shipment_header_id = rsh.shipment_header_id
AND rt.vendor_id = pv.vendor_id
AND ROWNUM = 1),
(SELECT ood.name
FROM rcv_shipment_lines rt, hr_operating_units ood
WHERE shipment_header_id = rsh.shipment_header_id
AND rt.from_organization_id = ood.organization_id
AND ROWNUM = 1)
),
(SELECT customer_name
FROM ar_customers ar, rcv_transactions rt
WHERE rt.customer_id = ar.customer_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ROWNUM = 1)
)
vendor_name, quantity receive_qty
FROM rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rt.organization_id = 71
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
and trunc(transaction_date) between '01-JAN-2012' and '31-JAN-2012'
and transaction_type ='RECEIVE')
group by vendor_name order by 1
No comments:
Post a Comment