Saturday, 11 May 2013

Query to retrieve vendor against the total receiving qty


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