Query for GRN Report (AVANTHA)

Requirement Sample Excel:
-------------------------

NEW FINAL QUERY:
------------------------

SELECT  ROWNUM sl_no, a.segment1 po_number,b.item_id
, a.creation_date po_date,
       g.vendor_name, b.quantity po_quantity, e.receipt_num grn_no,
       f.quantity_received receipt_quantity, f.amount receipt_value,
       b.amount po_value,
       (f.quantity_received - f.quantity_shipped) pending_qty,
       c.need_by_date delivery_schedule, e.creation_date receipt_date,
       j.invoice_amount invoice_value, j.invoice_num invoice_number,
       j.invoice_date, f.attribute2 lr_no, f.attribute3 lr_date
FROM   po_headers_all a,
       po_lines_all b
       ,
       po_line_locations_all c
       ,
       po_distributions_all d
       ,
       rcv_shipment_headers e,
       rcv_shipment_lines f,
      rcv_transactions rt,
          ap_suppliers g,
       hr_all_organization_units_tl h,
       ap_invoice_lines_all i,
       ap_invoices_all j     
 WHERE a.po_header_id = b.po_header_id
   AND b.po_line_id = c.po_line_id
   AND c.line_location_id = d.line_location_id
   AND f.po_header_id = a.po_header_id
   AND e.shipment_header_id = f.shipment_header_id
   AND rt.shipment_line_id = f.shipment_line_id
   AND rt.po_header_id = a.po_header_id
   and rt.PO_LINE_ID = b.po_line_id
   AND g.vendor_id = a.vendor_id
   AND a.org_id = h.organization_id
   AND  b.item_id IN (SELECT distinct inventory_item_id
                         FROM mtl_system_items_b
                         WHERE segment1 BETWEEN NVL (:from_material_code, segment1)
                         AND NVL (:to_material_code, segment1)
                         and organization_id=nvl(:organization_id,organization_id))
   AND d.po_distribution_id = i.po_distribution_id
   AND i.invoice_id = j.invoice_id
   and trunc(e.CREATION_DATE) between nvl(:from_receipt_date,e.CREATION_DATE) and nvl(:to_receipt_date,e.CREATION_DATE)
   AND g.VENDOR_NAME = nvl(:vendor_name,g.VENDOR_NAME)
   --AND a.segment1 = '30076'
   AND rt.destination_type_code = 'RECEIVING'
   AND i.line_type_lookup_code = 'ITEM' ;




Query For This Excel Requirement:
---------------------------------

SELECT ROWNUM sl_no, a.segment1 po_number,b.item_id
, a.creation_date po_date,
g.vendor_name, b.quantity po_quantity, e.receipt_num grn_no,
f.quantity_received receipt_quantity, f.amount receipt_value,
b.amount po_value,
(f.quantity_received - f.quantity_shipped) pending_qty,
c.need_by_date delivery_schedule, e.creation_date receipt_date,
j.invoice_amount invoice_value, j.invoice_num invoice_number,
j.invoice_date, f.attribute2 lr_no, f.attribute3 lr_date
FROM po_headers_all a,
po_lines_all b
,
po_line_locations_all c
,
po_distributions_all d
,
rcv_shipment_headers e,
rcv_shipment_lines f,
rcv_transactions rt,
ap_suppliers g,
hr_all_organization_units_tl h,
ap_invoice_lines_all i,
ap_invoices_all j
WHERE a.po_header_id = b.po_header_id
AND b.po_line_id = c.po_line_id
AND c.line_location_id = d.line_location_id
AND f.po_header_id = a.po_header_id
AND e.shipment_header_id = f.shipment_header_id
AND rt.shipment_line_id = f.shipment_line_id
AND rt.po_header_id = a.po_header_id
and rt.PO_LINE_ID = b.po_line_id
AND g.vendor_id = a.vendor_id
AND a.org_id = h.organization_id
AND b.item_id IN (SELECT distinct inventory_item_id
FROM mtl_system_items_b
WHERE segment1 BETWEEN NVL (:from_item_code, segment1)
AND NVL (:to_item_code, segment1)
and organization_id=86)
AND d.po_distribution_id = i.po_distribution_id
AND i.invoice_id = j.invoice_id
and trunc(e.CREATION_DATE) between nvl(:from_receipt_date,e.CREATION_DATE) and nvl(:to_receipt_date,e.CREATION_DATE)
AND g.VENDOR_NAME = nvl(:vendor_name,g.VENDOR_NAME)
--AND a.segment1 = '30076'
AND rt.destination_type_code = 'RECEIVING'
AND i.line_type_lookup_code = 'ITEM'



Output For This Report:
------------------------