Receving Value Report

Customized Query to get same data as of Standard Report "Receiving Value Report".


SELECT   (SELECT organization_code
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_code,
         (SELECT organization_name
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_name, pla.item_id,
         pla.item_description,
         pla.category_id,
         rsl.shipment_line_id,
         (SELECT segment1 || '-' || segment2
            FROM mtl_system_items
           WHERE inventory_item_id = pla.item_id
             AND organization_id = rsh.ship_to_org_id) item,
         (SELECT segment1 || '.' || segment2 || '.' || segment3
            FROM mtl_categories
           WHERE category_id = pla.category_id) category_desc,
         pla.unit_meas_lookup_code,
         pla.unit_price * nvl(rcv.CURRENCY_CONVERSION_RATE,1) unit_price,
     --    pla.quantity po_qty,
         (pla.unit_price *  nvl(rcv.CURRENCY_CONVERSION_RATE,1)* rcv.quantity) line_value,
         rsh.receipt_num,
         rsh.creation_date receipt_date,
         rsh.shipment_num,
         rsh.packing_slip,
         rsh.ship_to_org_id,
         rsl.source_document_code,
         pha.segment1 po_num,
         pla.line_num,
         rcv.quantity rec_qty
    FROM rcv_transactions rcv,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pla,
         po_headers_all pha,
         (select distinct ship_to_organization_id,pll1.po_header_id, pll1.po_line_id from po_line_locations_all pll1) pll
      --   po_line_locations_all pll
   WHERE rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR')
                                       AND trunc(rcv.transaction_date) <= :p_date)
     AND rsh.shipment_header_id = rcv.shipment_header_id
     AND rsh.shipment_header_id = rsl.shipment_header_id
     AND rsl.shipment_line_id = rcv.shipment_line_id
     AND pla.po_header_id = pha.po_header_id
     AND pha.po_header_id = rcv.po_header_id
     AND pla.po_line_id = rcv.po_line_id
     AND pll.po_line_id=pla.po_line_id
     AND pll.po_header_id=pha.po_header_id
     AND pll.ship_to_organization_id=rsh.ship_to_org_id
     AND pla.item_id=rsl.item_id
       AND rcv.transaction_type IN ('RECEIVE')
     AND rsl.source_document_code = 'PO'
  --    AND rsh.ship_to_org_id in (726)
    AND trunc(rcv.transaction_date) <= :p_date
--and rsh.receipt_num=:rec
--and rsh.ship_to_org_id=122
--and rsh.receipt_num=:p_rec
     AND pha.org_id = :p_org_id
UNION ALL
SELECT   (SELECT organization_code
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_code,
         (SELECT organization_name
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_name, pla.item_id,
         pla.item_description,
         pla.category_id,
         rsl.shipment_line_id,
         (SELECT segment1 || '-' || segment2
            FROM mtl_system_items
           WHERE inventory_item_id = pla.item_id
             AND organization_id = rsh.ship_to_org_id) item,
         (SELECT segment1 || '.' || segment2 || '.' || segment3
            FROM mtl_categories
           WHERE category_id = pla.category_id) category_desc,
         pla.unit_meas_lookup_code,
         pla.unit_price * nvl(rcv.CURRENCY_CONVERSION_RATE,1) unit_price,
     --    pla.quantity po_qty,
         (pla.unit_price * nvl(rcv.CURRENCY_CONVERSION_RATE,1) * rcv.quantity) line_value,
         rsh.receipt_num,
         rsh.creation_date receipt_date,
         rsh.shipment_num,
         rsh.packing_slip,
         rsh.ship_to_org_id,
         rsl.source_document_code,
         pha.segment1 po_num,
         pla.line_num,
         rcv.quantity rec_qty
    FROM rcv_transactions rcv,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pla,
         po_headers_all pha,
          (select distinct ship_to_organization_id,pll1.po_header_id, pll1.po_line_id from po_line_locations_all pll1) pll
      --   po_line_locations_all pll
   WHERE rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('DELIVER','RETURN TO VENDOR')
                                       AND trunc(rcv.transaction_date) <= :p_date)
     and rsh.shipment_header_id = rcv.shipment_header_id
     AND rsh.shipment_header_id = rsl.shipment_header_id
     AND rsl.shipment_line_id = rcv.shipment_line_id
     AND pla.po_header_id = pha.po_header_id
     AND pha.po_header_id = rcv.po_header_id
     AND pla.po_line_id = rcv.po_line_id
     AND pll.po_line_id=pla.po_line_id
     AND pll.po_header_id=pha.po_header_id
     AND pll.ship_to_organization_id=rsh.ship_to_org_id
     AND pla.item_id=rsl.item_id
    AND rcv.transaction_type IN ('ACCEPT')
     AND rsl.source_document_code = 'PO'
    AND trunc(rcv.transaction_date) <= :p_date
--and rsh.receipt_num=:rec
--and rsh.ship_to_org_id=122
-- AND rsh.ship_to_org_id in (726)
--and rsh.receipt_num=:p_rec
     AND pha.org_id = :p_org_id      
union all
SELECT   (SELECT organization_code
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_code,
         (SELECT organization_name
            FROM org_organization_definitions
           WHERE organization_id = rsh.ship_to_org_id) org_name, pla.item_id,
         pla.item_description,
         pla.category_id,
         rsl.shipment_line_id,
         (SELECT segment1 || '-' || segment2
            FROM mtl_system_items
           WHERE inventory_item_id = pla.item_id
             AND organization_id = rsh.ship_to_org_id) item,
         (SELECT segment1 || '.' || segment2 || '.' || segment3
            FROM mtl_categories
           WHERE category_id = pla.category_id) category_desc,
         pla.unit_meas_lookup_code,
         pla.unit_price * nvl(rcv.CURRENCY_CONVERSION_RATE,1) unit_price,
     --    pla.quantity po_qty,
         (pla.unit_price *  nvl(rcv.CURRENCY_CONVERSION_RATE,1)* rcv.quantity) line_value,
         rsh.receipt_num,
         rsh.creation_date receipt_date,
         rsh.shipment_num,
         rsh.packing_slip,
         rsh.ship_to_org_id,
         rsl.source_document_code,
         pha.segment1 po_num,
         pla.line_num,
         rcv.quantity rec_qty
    FROM rcv_transactions rcv,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         po_lines_all pla,
         po_headers_all pha,
         (select distinct ship_to_organization_id,pll1.po_header_id, pll1.po_line_id from po_line_locations_all pll1) pll
      --   po_line_locations_all pll
   WHERE rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('RETURN TO VENDOR')
                                  AND trunc(rcv.transaction_date) <= :p_date)
     AND rsh.shipment_header_id = rcv.shipment_header_id
     AND rsh.shipment_header_id = rsl.shipment_header_id
     AND rsl.shipment_line_id = rcv.shipment_line_id
     AND pla.po_header_id = pha.po_header_id
     AND pha.po_header_id = rcv.po_header_id
     AND pla.po_line_id = rcv.po_line_id
     AND pll.po_line_id=pla.po_line_id
     AND pll.po_header_id=pha.po_header_id
     AND pll.ship_to_organization_id=rsh.ship_to_org_id
     AND pla.item_id=rsl.item_id
       AND rcv.transaction_type IN ('REJECT')
     AND rsl.source_document_code = 'PO'
  --    AND rsh.ship_to_org_id in (726)
    AND trunc(rcv.transaction_date) <= :p_date
--and rsh.receipt_num=:rec
--and rsh.ship_to_org_id=122
--and rsh.receipt_num=:p_rec
     AND pha.org_id = :p_org_id
ORDER BY 1,7,13 --,rsh.shipment_num