VIEW: APPS.CSP_MOVEORDERS_V

Source

View Text - Preformatted

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 all 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) , 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 mtl_material_transactions_temp mmtt, 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 mmtt.transaction_source_type_id in (4,13) and mmtt.transaction_source_id = mtrh.header_id and mmtt.trx_source_line_id = mtrl.line_id and 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 cpil.transaction_temp_id = mmtt.transaction_temp_id and mtrl.line_status <> 6 and not exists (select line_id from csp_packlist_lines cpal where cpal.line_id = cpil.line_id) and cpih.picklist_status < 3 union all 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) , cpil.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) and cpih.picklist_status = 3 union all 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 union all 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, mtrh.creation_date , to_number(null), to_number(null), null waybill, null carrier, null 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, mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, csp_sec_inventories csi, org_organization_definitions ood where mtrh.header_id = mtrl.header_id and mtrh.organization_id=ood.organization_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 mtrh.move_order_type = 2 and not exists (select header_id from csp_moveorder_headers cmh where cmh.header_id = mtrh.header_id)

View Text - HTML Formatted

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 ALL 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)
, 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 MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, 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 MMTT.TRANSACTION_SOURCE_TYPE_ID IN (4
, 13)
AND MMTT.TRANSACTION_SOURCE_ID = MTRH.HEADER_ID
AND MMTT.TRX_SOURCE_LINE_ID = MTRL.LINE_ID
AND 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 CPIL.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
AND MTRL.LINE_STATUS <> 6
AND NOT EXISTS (SELECT LINE_ID
FROM CSP_PACKLIST_LINES CPAL
WHERE CPAL.LINE_ID = CPIL.LINE_ID)
AND CPIH.PICKLIST_STATUS < 3 UNION ALL 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)
, CPIL.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)
AND CPIH.PICKLIST_STATUS = 3 UNION ALL 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 UNION ALL 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
, MTRH.CREATION_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL WAYBILL
, NULL CARRIER
, NULL 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
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TXN_REQUEST_LINES MTRL
, CSP_SEC_INVENTORIES CSI
, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE MTRH.HEADER_ID = MTRL.HEADER_ID
AND MTRH.ORGANIZATION_ID=OOD.ORGANIZATION_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 MTRH.MOVE_ORDER_TYPE = 2
AND NOT EXISTS (SELECT HEADER_ID
FROM CSP_MOVEORDER_HEADERS CMH
WHERE CMH.HEADER_ID = MTRH.HEADER_ID)