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)