View: INVFV_INVENTORY_SUPPLIES

Product:	INV - Inventory
Description:	- Retrofitted
Implementation/DBA Data:	APPS.INVFV_INVENTORY_SUPPLIES

View Text

SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, NULL
, MTS.PO_RELEASE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_LOCATION_ID
, TO_NUMBER(NULL)
, MTS.PO_DISTRIBUTION_ID
, TO_NUMBER(NULL)
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, PRH.SEGMENT1
, MTS.REQ_LINE_ID
, PRL.LINE_NUM
, MTS.SHIPMENT_HEADER_ID
, NULL
, MTS.SHIPMENT_LINE_ID
, TO_NUMBER (NULL)
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'REQ'
AND PRL.REQUISITION_LINE_ID = MTS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID = MTS.REQ_HEADER_ID
AND HOU1.ORGANIZATION_ID (+)= MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+)= MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+)= MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+)= MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+)= MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+)= MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+)= MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+)= MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+)= MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+)= MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL UNION ALL /* THIS FOR PO */ SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, POH.SEGMENT1
, MTS.PO_RELEASE_ID
, POR.RELEASE_NUM
, MTS.PO_LINE_ID
, POL.LINE_NUM
, MTS.PO_LINE_LOCATION_ID
, PLL.SHIPMENT_NUM
, MTS.PO_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, NULL
, MTS.REQ_LINE_ID
, TO_NUMBER(NULL)
, MTS.SHIPMENT_HEADER_ID
, NULL
, MTS.SHIPMENT_LINE_ID
, TO_NUMBER (NULL)
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'PO'
AND POH.PO_HEADER_ID = MTS.PO_HEADER_ID
AND POL.PO_LINE_ID = MTS.PO_LINE_ID
AND POD.PO_DISTRIBUTION_ID (+) = MTS.PO_DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID (+) = MTS.PO_LINE_LOCATION_ID
AND POR.PO_RELEASE_ID (+) = MTS.PO_RELEASE_ID
AND HOU1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+) = MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL /* RECEVING IS MADE FORM TWO SELECTS*/ /* THIS IS FOR RECEIVING
AND THERE ARE NO DISTRIBUTIONS */ UNION ALL SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, NULL
, MTS.PO_RELEASE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_LOCATION_ID
, TO_NUMBER(NULL)
, MTS.PO_DISTRIBUTION_ID
, TO_NUMBER(NULL)
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, PRH.SEGMENT1
, MTS.REQ_LINE_ID
, PRL.LINE_NUM
, MTS.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, MTS.SHIPMENT_LINE_ID
, RSL.LINE_NUM
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MTS.PO_DISTRIBUTION_ID IS NULL
AND RCT.TRANSACTION_ID = MTS.RCV_TRANSACTION_ID
AND RSL.SHIPMENT_LINE_ID = MTS.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = MTS.SHIPMENT_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = MTS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = MTS.REQ_HEADER_ID
AND HOU1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+) = MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL /* THIS IS FOR RECEIVING
AND DISTRIBUTIONS EXISTS */ UNION ALL SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, POH.SEGMENT1
, MTS.PO_RELEASE_ID
, POR.RELEASE_NUM
, MTS.PO_LINE_ID
, POL.LINE_NUM
, MTS.PO_LINE_LOCATION_ID
, PLL.SHIPMENT_NUM
, MTS.PO_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, PRH.SEGMENT1
, MTS.REQ_LINE_ID
, PRL.LINE_NUM
, MTS.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, MTS.SHIPMENT_LINE_ID
, RSL.LINE_NUM
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MTS.PO_DISTRIBUTION_ID IS NOT NULL
AND RCT.TRANSACTION_ID = MTS.RCV_TRANSACTION_ID
AND RSL.SHIPMENT_LINE_ID = MTS.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = MTS.SHIPMENT_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = MTS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = MTS.REQ_HEADER_ID
AND POR.PO_RELEASE_ID (+) = MTS.PO_RELEASE_ID
AND POH.PO_HEADER_ID = MTS.PO_HEADER_ID
AND POL.PO_LINE_ID = MTS.PO_LINE_ID
AND POD.PO_DISTRIBUTION_ID = MTS.PO_DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID (+) = MTS.PO_LINE_LOCATION_ID
AND HOU1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+) = MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL UNION ALL /*-- THIS IS FOR SHIPMENT */ SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, NULL
, MTS.PO_RELEASE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_ID
, TO_NUMBER(NULL)
, MTS.PO_LINE_LOCATION_ID
, TO_NUMBER(NULL)
, MTS.PO_DISTRIBUTION_ID
, TO_NUMBER(NULL)
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, PRH.SEGMENT1
, MTS.REQ_LINE_ID
, PRL.LINE_NUM
, MTS.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, MTS.SHIPMENT_LINE_ID
, RSL.LINE_NUM
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MTS.PO_DISTRIBUTION_ID IS NULL
AND RSL.SHIPMENT_LINE_ID = MTS.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = MTS.SHIPMENT_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = MTS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = MTS.REQ_HEADER_ID
AND HOU1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+) = MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL UNION ALL SELECT MTS.SUPPLY_TYPE_CODE
, MTS.SUPPLY_SOURCE_ID
, MTS.PO_HEADER_ID
, POH.SEGMENT1
, MTS.PO_RELEASE_ID
, POR.RELEASE_NUM
, MTS.PO_LINE_ID
, POL.LINE_NUM
, MTS.PO_LINE_LOCATION_ID
, PLL.SHIPMENT_NUM
, MTS.PO_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, MTS.RCV_TRANSACTION_ID
, MTS.REQ_HEADER_ID
, PRH.SEGMENT1
, MTS.REQ_LINE_ID
, PRL.LINE_NUM
, MTS.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, MTS.SHIPMENT_LINE_ID
, RSL.LINE_NUM
, MTS.ITEM_ID
, '_KF:INV:MSTK:MSI'
, MTS.ITEM_REVISION
, MTS.QUANTITY
, MTS.UNIT_OF_MEASURE
, MTS.TO_ORG_PRIMARY_QUANTITY
, MTS.TO_ORG_PRIMARY_UOM
, MTS.RECEIPT_DATE
, MTS.NEED_BY_DATE
, MTS.EXPECTED_DELIVERY_DATE
, '_LA:MTS.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:DESTINATION TYPE:DESCRIPTION'
, MTS.LOCATION_ID
, '_KF:INV:MTLL:MIL'
, MTS.FROM_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME
, MTS.FROM_SUBINVENTORY
, MTS.TO_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE
, HOU2.NAME
, MTS.TO_SUBINVENTORY
, MTS.INTRANSIT_OWNING_ORG_ID
, MP3.ORGANIZATION_CODE
, HOU3.NAME
, MTS.CREATED_BY
, MTS.CREATION_DATE
, MTS.LAST_UPDATED_BY
, MTS.LAST_UPDATE_DATE
FROM HR_ALL_ORGANIZATION_UNITS HOU1
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_ALL_ORGANIZATION_UNITS HOU3
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MTL_ITEM_LOCATIONS MIL
, MTL_SYSTEM_ITEMS MSI
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, MTL_SUPPLY MTS
WHERE MTS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MTS.PO_DISTRIBUTION_ID IS NOT NULL
AND RSL.SHIPMENT_LINE_ID = MTS.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = MTS.SHIPMENT_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = MTS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = MTS.REQ_HEADER_ID
AND POR.PO_RELEASE_ID (+)= MTS.PO_RELEASE_ID
AND POH.PO_HEADER_ID = MTS.PO_HEADER_ID
AND POL.PO_LINE_ID = MTS.PO_LINE_ID
AND POD.PO_DISTRIBUTION_ID = MTS.PO_DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID (+) = MTS.PO_LINE_LOCATION_ID
AND HOU1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MP1.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID (+) = MTS.TO_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID (+) = MTS.INTRANSIT_OWNING_ORG_ID
AND MSI.INVENTORY_ITEM_ID (+) = MTS.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = MTS.LOCATION_ID
AND MIL.ORGANIZATION_ID (+) = MTS.FROM_ORGANIZATION_ID
AND '_SEC:MTS.FROM_ORGANIZATION_ID' IS NOT NULL
AND '_SEC:MTS.TO_ORGANIZATION_ID' IS NOT NULL WITH READ ONLY

Columns