View: CSP_MOVEORDERS_V

Product:	CSP - Spares Management
Description:	The view gives the status of a moveorder.
Implementation/DBA Data:	APPS.CSP_MOVEORDERS_V

View Text

SELECT MTRH.HEADER_ID
, MTRH.REQUEST_NUMBER
, MTRH.ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME
, MTRH.DATE_REQUIRED
, MTRL.LINE_ID
, MTRL.INVENTORY_ITEM_ID
, MTRL.FROM_SUBINVENTORY_CODE
, MTRL.FROM_LOCATOR_ID
, MTRL.TO_SUBINVENTORY_CODE
, MTRL.TO_LOCATOR_ID
, MTRL.REVISION
, MTRL.QUANTITY
, TO_CHAR(MTRL.LINE_STATUS)
, ML.MEANING
, MTRL.STATUS_DATE
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CML.CREATION_DATE
, CML.INCIDENT_ID
, CML.TASK_ID
, NULL WAYBILL
, CMH.CARRIER
, CMH.SHIPMENT_METHOD
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MFG_LOOKUPS ML
, MTL_SYSTEM_ITEMS_VL MSIBK
, CSP_MOVEORDER_LINES CML
, CSP_MOVEORDER_HEADERS CMH
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TXN_REQUEST_LINES MTRL
, CSP_SEC_INVENTORIES CSI
, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE MTRL.LINE_ID = CML.LINE_ID AND MTRH.HEADER_ID = MTRL.HEADER_ID AND MTRH.ORGANIZATION_ID=OOD.ORGANIZATION_ID AND CMH.HEADER_ID = MTRH.HEADER_ID AND MSIBK.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID AND MSIBK.ORGANIZATION_ID = MTRL.ORGANIZATION_ID AND ML.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS' AND ML.LOOKUP_CODE = MTRL.LINE_STATUS AND CSI.ORGANIZATION_ID = MTRL.ORGANIZATION_ID AND CSI.SECONDARY_INVENTORY_NAME = MTRL.TO_SUBINVENTORY_CODE
AND (MTRL.LINE_STATUS = 6 OR NOT EXISTS (SELECT LINE_ID
FROM CSP_PICKLIST_LINES CPIL
WHERE CPIL.LINE_ID = MTRL.LINE_ID)) UNION SELECT MTRH.HEADER_ID
, MTRH.REQUEST_NUMBER
, MTRH.ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME
, MTRH.DATE_REQUIRED
, MTRL.LINE_ID
, MTRL.INVENTORY_ITEM_ID
, MTRL.FROM_SUBINVENTORY_CODE
, MTRL.FROM_LOCATOR_ID
, MTRL.TO_SUBINVENTORY_CODE
, MTRL.TO_LOCATOR_ID
, MTRL.REVISION
, MTRL.QUANTITY
, CPIH.PICKLIST_STATUS
, FL.MEANING
, NVL(CPIH.DATE_CREATED
, NVL(CPIH.DATE_CONFIRMED
, MTRL.STATUS_DATE))
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CML.CREATION_DATE
, CML.INCIDENT_ID
, CML.TASK_ID
, NULL WAYBILL
, CMH.CARRIER
, CMH.SHIPMENT_METHOD
, CPIH.PICKLIST_HEADER_ID
, CPIH.PICKLIST_NUMBER
, NVL(CPIH.DATE_CONFIRMED
, CPIH.DATE_CREATED)
, QUANTITY_PICKED
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM FND_LOOKUPS FL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_MOVEORDER_LINES CML
, CSP_MOVEORDER_HEADERS CMH
, CSP_PICKLIST_HEADERS CPIH
, CSP_PICKLIST_LINES CPIL
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TXN_REQUEST_LINES MTRL
, CSP_SEC_INVENTORIES CSI
, ORG_ORGANIZATION_DEFINITIONS OOD WHERE MTRL.LINE_ID = CML.LINE_ID AND MTRH.HEADER_ID = MTRL.HEADER_ID AND MTRH.ORGANIZATION_ID=OOD.ORGANIZATION_ID AND CMH.HEADER_ID = MTRH.HEADER_ID
AND MTRL.LINE_ID = CPIL.LINE_ID
AND CPIH.PICKLIST_HEADER_ID = CPIL.PICKLIST_HEADER_ID
AND MSIBK.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'CSP_PICKLIST_STATUS'
AND FL.LOOKUP_CODE = CPIH.PICKLIST_STATUS AND CSI.ORGANIZATION_ID = MTRL.ORGANIZATION_ID AND CSI.SECONDARY_INVENTORY_NAME = MTRL.TO_SUBINVENTORY_CODE
AND MTRL.LINE_STATUS <> 6
AND NOT EXISTS (SELECT LINE_ID
FROM CSP_PACKLIST_LINES CPAL
WHERE CPAL.LINE_ID = CPIL.LINE_ID) UNION SELECT MTRH.HEADER_ID
, MTRH.REQUEST_NUMBER
, MTRH.ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME
, MTRH.DATE_REQUIRED
, MTRL.LINE_ID
, MTRL.INVENTORY_ITEM_ID
, MTRL.FROM_SUBINVENTORY_CODE
, MTRL.FROM_LOCATOR_ID
, MTRL.TO_SUBINVENTORY_CODE
, MTRL.TO_LOCATOR_ID
, MTRL.REVISION
, MTRL.QUANTITY
, CPAL.PACKLIST_LINE_STATUS
, FL.MEANING
, NVL(CPAH.DATE_RECEIVED
, NVL(CPAH.DATE_SHIPPED
, NVL(CPAH.DATE_PACKED
, CPAH.DATE_CREATED)))
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CML.CREATION_DATE
, CML.INCIDENT_ID
, CML.TASK_ID
, CPAH.WAYBILL
, CPAH.CARRIER
, CPAH.SHIPMENT_METHOD
, CPIH.PICKLIST_HEADER_ID
, CPIH.PICKLIST_NUMBER
, NVL(CPIH.DATE_CONFIRMED
, CPIH.DATE_CREATED)
, QUANTITY_PICKED
, CPAH.PACKLIST_HEADER_ID
, CPAH.PACKLIST_NUMBER
, CPAH.DATE_PACKED
, CPAH.DATE_SHIPPED
, CPAH.DATE_RECEIVED
, CPAL.QUANTITY_PACKED
, CPAL.QUANTITY_SHIPPED
, CPAL.QUANTITY_RECEIVED
FROM FND_LOOKUPS FL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_MOVEORDER_LINES CML
, CSP_MOVEORDER_HEADERS CMH
, CSP_PICKLIST_HEADERS CPIH
, CSP_PICKLIST_LINES CPIL
, CSP_PACKLIST_HEADERS CPAH
, CSP_PACKLIST_LINES CPAL
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TXN_REQUEST_LINES MTRL
, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE MTRL.LINE_ID = CML.LINE_ID
AND MTRH.HEADER_ID = MTRL.HEADER_ID AND MTRH.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND CMH.HEADER_ID = MTRH.HEADER_ID
AND MTRL.LINE_ID = CPIL.LINE_ID
AND CPIH.PICKLIST_HEADER_ID = CPIL.PICKLIST_HEADER_ID
AND CPIL.PICKLIST_LINE_ID = CPAL.PICKLIST_LINE_ID
AND CPAH.PACKLIST_HEADER_ID = CPAL.PACKLIST_HEADER_ID
AND MSIBK.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'CSP_PACKLIST_STATUS'
AND FL.LOOKUP_CODE = CPAL.PACKLIST_LINE_STATUS
AND MTRL.LINE_STATUS <> 6

Columns