VIEW: APPS.INVFV_INVENTORY_SUPPLIES

Source

View Text - Preformatted

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

View Text - HTML Formatted

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