Receiving Shipments Generated for a Return Order (RMA).sql

SELECT
	RAH.SHIPMENT_HEADER_ID
  , RAH.LSP_FLAG
  , RAH.RA_DOCUMENT_NUMBER      RMA_NUM
  , RAH.RA_DOO_SOURCE_SYSTEM_ID SOURCE_SYSTEM_ID
  , RAH.RECEIPT_ADVICE_NUMBER
  , RAH.RMA_BU_ID
  , RAH.CURRENCY_CODE
  , RAH.CUSTOMER_ID
  , RAL.RECEIPT_ADVICE_LINE_NUMBER
  , EGP.ITEM_NUMBER
  , RAL.ITEM_ID                 AS ITEM_ID
  , RAL.ITEM_DESCRIPTION        AS ITEM_DESCRIPTION
  , NVL(RAL.CONSIGNED_FLAG,'N') AS CONSIGNED_ITEMS
  , RAL.ITEM_REVISION           AS ITEM_REVISION
  , RAL.CATEGORY_ID             AS CATEGORY_ID
  , IOP.ORGANIZATION_CODE       AS ORG_CODE
  , RAL.TO_ORGANIZATION_ID      AS ORG_ID
  , RAL.SHIP_FROM_LOCATION_ID
  , RAL.RA_QUANTITY_EXPECTED
  , RAL.QUANTITY_RECEIVED
  , RAL.QUANTITY_DELIVERED
  , RAL.UOM_CODE                AS UOM_CODE
  , RAL.DESTINATION_TYPE_CODE   AS DESTINATION_TYPE_CODE
  , RAL.DELIVER_TO_LOCATION_ID  AS DELIVER_TO_LOCATION_ID
  , RAL.RA_DOCUMENT_LINE_NUMBER AS DOCUMENT_LINE_NUMBER
  , RAL.RA_EXPECTED_RECEIPT_DATE
  , RAL.ROUTING_HEADER_ID  AS ROUTING_HEADER_ID
  , RAL.SECONDARY_UOM_CODE AS SECONDARY_UOM_CODE
  , RAH.CUSTOMER_SITE_ID   AS CUSTOMER_SITE_ID
  , RAL.SHIPMENT_HEADER_ID AS RECEIPT_ADVICE_HEADER_ID
  , RAL.SHIPMENT_LINE_ID   AS RECEIPT_ADVICE_LINE_ID
  , RAH.RA_DOCUMENT_CODE   AS RA_DOCUMENT_CODE
  , RAL.*
FROM
	FUSION.RCV_SHIPMENT_HEADERS RAH
  , FUSION.RCV_SHIPMENT_LINES   RAL
  , FUSION.INV_ORG_PARAMETERS   IOP
  , FUSION.EGP_SYSTEM_ITEMS_B   EGP
WHERE
	RAL.SOURCE_DOCUMENT_CODE    = 'RMA'
	AND RAH.SHIPMENT_HEADER_ID  = RAL.SHIPMENT_HEADER_ID
	AND RAH.RECEIPT_SOURCE_CODE = 'CUSTOMER'
	AND RAH.RA_DOCUMENT_CODE    = 'RMA'
	AND RAL.TO_ORGANIZATION_ID  = IOP.ORGANIZATION_ID
	AND RAL.TO_ORGANIZATION_ID  = EGP.ORGANIZATION_ID
	AND RAL.ITEM_ID             = EGP.INVENTORY_ITEM_ID
	AND NVL(RAH.LSP_FLAG, 'N') <> 'Y'
	AND RAH.RA_DOCUMENT_NUMBER  = '&SOURCE_ORDER_NUMBER' -- RMA number
ORDER BY
	RAL.SHIPMENT_LINE_ID