PO ALL RECEIPTS Query

SELECT rcv_shipment_lines.shipment_line_id, po_vendors.vendor_name,

       DECODE (rcv_shipment_headers.vendor_site_id,

               NULL, NULL,

               po_vendor_sites_all.vendor_site_code

              ) vendor_site,

       po_vendor_sites_all.address_line1, po_vendor_sites_all.address_line2,

       po_vendor_sites_all.address_line3, po_vendor_sites_all.city,

       po_vendor_sites_all.state, po_vendor_sites_all.country,

       DECODE (rcv_shipment_lines.po_header_id,

               NULL, 'Unordered',

               'Ordered'

              ) AS receipt_type,

       rcv_shipment_headers.receipt_num,

       rcv_shipment_headers.creation_date AS receipt_date,

       rcv_shipment_lines.ship_to_location_id,

          hr_locations.location_code

       || '-'

       || hr_locations.description AS ship_to_location,

       rcv_shipment_headers.ship_to_org_id,

       hr_all_organization_units_tl.NAME AS ORGANIZATION,

       rcv_shipment_headers.employee_id, per_all_people_f.full_name,

       rcv_shipment_headers.packing_slip, rcv_shipment_headers.shipped_date,

       rcv_shipment_headers.freight_carrier_code,

       rcv_shipment_headers.freight_terms, rcv_shipment_lines.category_id,

          mtl_categories_v.segment1

       || '.'

       || mtl_categories_v.segment2 AS category_subcat,

       rcv_shipment_lines.quantity_shipped,

       rcv_shipment_lines.quantity_received,

       rcv_shipment_lines.unit_of_measure,

       rcv_shipment_lines.item_description, po_headers.segment1 AS po_num,

       po_headers.creation_date AS po_date, rcv_shipment_lines.po_line_id,

       rcv_shipment_lines.po_distribution_id,

       rcv_shipment_lines.requisition_line_id,

       rcv_shipment_lines.deliver_to_person_id,

       disc_rcv_delivery.delivery_date,

       rcv_shipment_headers.comments AS header_note,

       disc_po_distribution_dsc.gl_name_seg1,

       disc_po_distribution_dsc.gl_name_seg2,

       disc_po_distribution_dsc.gl_name_seg3,

       disc_po_distribution_dsc.gl_name_seg4,

       disc_po_distribution_dsc.gl_name_seg5,

       disc_po_distribution_dsc.gl_name_seg6,

       disc_po_distribution_dsc.quantity_ordered,

       disc_po_distribution_dsc.quantity_billed,

       disc_po_distribution_dsc.quantity_delivered, po_lines_all.unit_price,

       po_headers.currency_code, po_line_locations_all.promised_date,

       DECODE (rcv_shipment_lines.routing_header_id,

               2, 'Yes',

               'No'

              ) AS inspection_required,

       disc_rcv_acceptance.inspection_status_code,

       disc_rcv_return.return_date,

       disc_rcv_return.transaction_type AS return_transaction_code

  FROM rcv_shipment_headers rcv_shipment_headers,

       rcv_shipment_lines rcv_shipment_lines,

       disc_rcv_delivery disc_rcv_delivery,

       disc_rcv_return disc_rcv_return,

       po_vendors po_vendors,

       po_vendor_sites_all po_vendor_sites_all,

       hr_locations hr_locations,

       hr_all_organization_units_tl hr_all_organization_units_tl,

       per_all_people_f per_all_people_f,

       po_headers_all po_headers,

       mtl_categories_v mtl_categories_v,

       disc_po_distribution_dsc disc_po_distribution_dsc,

       po_lines_all po_lines_all,

       po_line_locations_all po_line_locations_all,

       disc_rcv_acceptance

 WHERE rcv_shipment_headers.shipment_header_id =

                                         rcv_shipment_lines.shipment_header_id

   AND rcv_shipment_headers.vendor_id = po_vendors.vendor_id

   AND po_vendors.vendor_id = po_vendor_sites_all.vendor_id

   AND NVL (rcv_shipment_headers.vendor_site_id,

            (SELECT MAX (s.vendor_site_id)

               FROM po_vendor_sites_all s

              WHERE s.vendor_id = po_vendors.vendor_id)

           ) = po_vendor_sites_all.vendor_site_id

   AND rcv_shipment_lines.ship_to_location_id = hr_locations.location_id

   AND rcv_shipment_headers.ship_to_org_id =

                                  hr_all_organization_units_tl.organization_id

   AND rcv_shipment_headers.employee_id = per_all_people_f.person_id(+)

   AND rcv_shipment_lines.po_header_id = po_headers.po_header_id(+)

   AND rcv_shipment_lines.po_distribution_id = disc_po_distribution_dsc.po_distribution_id(+)

   AND rcv_shipment_lines.shipment_header_id = disc_rcv_delivery.shipment_header_id(+)

   AND rcv_shipment_lines.shipment_line_id = disc_rcv_delivery.shipment_line_id(+)

   AND rcv_shipment_lines.po_line_id = po_lines_all.po_line_id(+)

   AND rcv_shipment_lines.po_line_location_id = po_line_locations_all.line_location_id(+)

   AND mtl_categories_v.category_id = rcv_shipment_lines.category_id

   AND rcv_shipment_lines.shipment_line_id = disc_rcv_acceptance.shipment_line_id(+)

   AND rcv_shipment_lines.shipment_line_id = disc_rcv_return.shipment_line_id(+)

   AND DECODE (rcv_shipment_headers.employee_id,

               NULL, SYSDATE,

               rcv_shipment_headers.creation_date

              ) BETWEEN DECODE (rcv_shipment_headers.employee_id,

                                NULL, SYSDATE,

                                per_all_people_f.effective_start_date

                               )

                    AND DECODE (rcv_shipment_headers.employee_id,

                                NULL, SYSDATE,

                                per_all_people_f.effective_end_date

                               )