VIEW: APPS.CSP_PARTS_REQUIREMENTS_V

Source

View Text - Preformatted

SELECT jtv.source_object_name , jtv.task_number , jttv.name , jtsv.name , csf_util_pvt.get_object_name(jtv.owner_type_code,jtv.owner_id) , csf_util_pvt.get_object_name(NVL(jta.resource_type_code,crh.resource_type),NVL(jta.resource_id,crh.resource_id)) , NULL , NULL , crl.source_organization_id , crl.source_subinventory , crh.requirement_header_id , crh.destination_organization_Id , crh.destination_subinventory , crh.need_by_date , msibk.padded_concatenated_segments , msibk.inventory_item_id , msibk.description , NULL , NULL , NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, jtf_tasks_vl jtv, jtf_task_assignments jta, jtf_task_types_vl jttv, jtf_task_statuses_vl jtsv, mtl_system_items_b_kfv msibk, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crl.requirement_header_id = crh.requirement_header_id AND jtv.task_id = crh.task_id AND jta.task_assignment_id(+) = crh.task_assignment_id AND jttv.task_type_id = jtv.task_type_id AND jtsv.task_status_id = jtv.task_status_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND crh.task_id IS NOT NULL AND NOT EXISTS (SELECT 'x' FROM csp_req_line_details WHERE requirement_line_id = crl.requirement_line_id ) UNION ALL SELECT NULL , NULL, NULL, NULL, NULL , csf_util_pvt.get_object_name(crh.resource_type,crh.resource_id) , NULL , NULL , crl.source_organization_id , crl.source_subinventory , crh.requirement_header_id , crh.destination_organization_Id , crh.destination_subinventory , crh.need_by_date , msibk.padded_concatenated_segments , msibk.inventory_item_id , msibk.description , NULL , NULL , NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, mtl_system_items_b_kfv msibk, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crl.requirement_header_id = crh.requirement_header_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND crh.task_id IS NULL AND NOT EXISTS (SELECT 'x' FROM csp_req_line_details WHERE requirement_line_id = crl.requirement_line_id ) UNION ALL SELECT jtv.source_object_name, jtv.task_number, jttv.name, jtsv.name, csf_util_pvt.get_object_name(jtv.owner_type_code,jtv.owner_id), csf_util_pvt.get_object_name(NVL(jta.resource_type_code,crh.resource_type),NVL(jta.resource_id,crh.resource_id)), TO_CHAR(crld.source_id), 'RESERVED', mr.organization_id, mr.subinventory_code, crh.requirement_header_id, crh.destination_organization_Id, crh.destination_subinventory, crh.need_by_date , msibk.padded_concatenated_segments , msibk.inventory_item_id , msibk.description , NULL , NULL , NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, jtf_tasks_vl jtv, jtf_task_assignments jta, jtf_task_types_vl jttv, jtf_task_statuses_vl jtsv, mtl_system_items_b_kfv msibk, mtl_reservations mr, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND jtv.task_id = crh.task_id AND jta.task_assignment_id(+) = crh.task_assignment_id AND jttv.task_type_id = jtv.task_type_id AND jtsv.task_status_id = jtv.task_status_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND mr.reservation_id = crld.source_id AND crh.task_id IS NOT NULL AND crld.source_type = 'RES' UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, csf_util_pvt.get_object_name(crh.resource_type,crh.resource_id), TO_CHAR(crld.source_id), 'RESERVED', mr.organization_id, mr.subinventory_code, crh.requirement_header_id, crh.destination_organization_Id, crh.destination_subinventory, crh.need_by_date , msibk.padded_concatenated_segments , msibk.inventory_item_id , msibk.description , NULL , NULL , NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, mtl_system_items_b_kfv msibk, mtl_reservations mr, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND mr.reservation_id = crld.source_id AND crh.task_id IS NULL AND crld.source_type = 'RES' UNION ALL SELECT jtv.source_object_name , jtv.task_number, jttv.name, jtsv.name, csf_util_pvt.get_object_name(jtv.owner_type_code,jtv.owner_id), csf_util_pvt.get_object_name(NVL(jta.resource_type_code,crh.resource_type),NVL(jta.resource_id,crh.resource_id)), TO_CHAR(ooha.order_number) , csp_pick_utils.get_line_status_meaning(oola.line_id,oola.booked_flag,oola.flow_status_code), oola.ship_from_org_id, oola.subinventory , crh.requirement_header_id , crh.destination_organization_Id, crh.destination_subinventory, crh.need_by_date, msibk.padded_concatenated_segments, msibk.inventory_item_id, msibk.description, oola.schedule_arrival_date, NULL, NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, jtf_tasks_vl jtv, jtf_task_assignments jta, jtf_task_types_vl jttv, jtf_task_statuses_vl jtsv, mtl_system_items_b_kfv msibk, oe_order_lines_all oola, oe_order_headers_all ooha, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND jtv.task_id = crh.task_id AND jta.task_assignment_id(+) = crh.task_assignment_id AND jttv.task_type_id = jtv.task_type_id AND jtsv.task_status_id = jtv.task_status_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND oola.line_id = crld.source_id AND ooha.header_id = oola.header_id AND crh.task_id IS NOT NULL AND crld.source_type = 'IO' UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, csf_util_pvt.get_object_name(crh.resource_type,crh.resource_id), TO_CHAR(ooha.order_number) , csp_pick_utils.get_line_status_meaning(oola.line_id,oola.booked_flag,oola.flow_status_code), oola.ship_from_org_id, oola.subinventory , crh.requirement_header_id , crh.destination_organization_Id, crh.destination_subinventory, crh.need_by_date, msibk.padded_concatenated_segments, msibk.inventory_item_id, msibk.description, oola.schedule_arrival_date, NULL, NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, mtl_system_items_b_kfv msibk, oe_order_lines_all oola, oe_order_headers_all ooha, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND oola.line_id = crld.source_id AND ooha.header_id = oola.header_id AND crh.task_id IS NULL AND crld.source_type = 'IO' UNION ALL SELECT jtv.source_object_name , jtv.task_number, jttv.name, jtsv.name, csf_util_pvt.get_object_name(jtv.owner_type_code,jtv.owner_id), csf_util_pvt.get_object_name(NVL(jta.resource_type_code,crh.resource_type),NVL(jta.resource_id,crh.resource_id)), mtrh.request_number , ml.meaning, mtrh.organization_id, mtrl.from_subinventory_code , crh.requirement_header_id , mtrh.organization_id, mtrl.to_subinventory_code, crh.need_by_date, msibk.padded_concatenated_segments, msibk.inventory_item_id, msibk.description, NULL, NULL, NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, jtf_tasks_vl jtv, jtf_task_assignments jta, jtf_task_types_vl jttv, jtf_task_statuses_vl jtsv, mtl_system_items_b_kfv msibk, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mfg_lookups ml, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND jtv.task_id = crh.task_id AND jta.task_assignment_id(+) = crh.task_assignment_id AND jttv.task_type_id = jtv.task_type_id AND jtsv.task_status_id = jtv.task_status_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND mtrl.line_id = crld.source_id AND mtrh.header_id = mtrl.header_id AND crh.task_id IS NOT NULL AND crld.source_type = 'MO' AND ml.lookup_type = 'MTL_TXN_REQUEST_STATUS' AND ml.lookup_code = mtrh.header_status UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, csf_util_pvt.get_object_name(crh.resource_type,crh.resource_id), mtrh.request_number , ml.meaning, mtrh.organization_id, mtrl.from_subinventory_code , crh.requirement_header_id , mtrh.organization_id, mtrl.to_subinventory_code, crh.need_by_date, msibk.padded_concatenated_segments, msibk.inventory_item_id, msibk.description, NULL, NULL, NULL FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, mtl_system_items_b_kfv msibk, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mfg_lookups ml, (SELECT fnd_profile.value('CS_INV_VALIDATION_ORG') inv_organization_id FROM dual ) a WHERE crld.requirement_line_id = crl.requirement_line_id AND crl.requirement_header_id = crh.requirement_header_id AND msibk.organization_id = a.inv_organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND mtrl.line_id = crld.source_id AND mtrh.header_id = mtrl.header_id AND crh.task_id IS NULL AND crld.source_type = 'MO' AND ml.lookup_type = 'MTL_TXN_REQUEST_STATUS' AND ml.lookup_code = mtrh.header_status

View Text - HTML Formatted

SELECT JTV.SOURCE_OBJECT_NAME
, JTV.TASK_NUMBER
, JTTV.NAME
, JTSV.NAME
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, CSF_UTIL_PVT.GET_OBJECT_NAME(NVL(JTA.RESOURCE_TYPE_CODE
, CRH.RESOURCE_TYPE)
, NVL(JTA.RESOURCE_ID
, CRH.RESOURCE_ID))
, NULL
, NULL
, CRL.SOURCE_ORGANIZATION_ID
, CRL.SOURCE_SUBINVENTORY
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_TASK_TYPES_VL JTTV
, JTF_TASK_STATUSES_VL JTSV
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND JTV.TASK_ID = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTTV.TASK_TYPE_ID = JTV.TASK_TYPE_ID
AND JTSV.TASK_STATUS_ID = JTV.TASK_STATUS_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND CRH.TASK_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM CSP_REQ_LINE_DETAILS
WHERE REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID ) UNION ALL SELECT NULL
, NULL
, NULL
, NULL
, NULL
, CSF_UTIL_PVT.GET_OBJECT_NAME(CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID)
, NULL
, NULL
, CRL.SOURCE_ORGANIZATION_ID
, CRL.SOURCE_SUBINVENTORY
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND CRH.TASK_ID IS NULL
AND NOT EXISTS (SELECT 'X'
FROM CSP_REQ_LINE_DETAILS
WHERE REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID ) UNION ALL SELECT JTV.SOURCE_OBJECT_NAME
, JTV.TASK_NUMBER
, JTTV.NAME
, JTSV.NAME
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, CSF_UTIL_PVT.GET_OBJECT_NAME(NVL(JTA.RESOURCE_TYPE_CODE
, CRH.RESOURCE_TYPE)
, NVL(JTA.RESOURCE_ID
, CRH.RESOURCE_ID))
, TO_CHAR(CRLD.SOURCE_ID)
, 'RESERVED'
, MR.ORGANIZATION_ID
, MR.SUBINVENTORY_CODE
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_TASK_TYPES_VL JTTV
, JTF_TASK_STATUSES_VL JTSV
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_RESERVATIONS MR
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND JTV.TASK_ID = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTTV.TASK_TYPE_ID = JTV.TASK_TYPE_ID
AND JTSV.TASK_STATUS_ID = JTV.TASK_STATUS_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MR.RESERVATION_ID = CRLD.SOURCE_ID
AND CRH.TASK_ID IS NOT NULL
AND CRLD.SOURCE_TYPE = 'RES' UNION ALL SELECT NULL
, NULL
, NULL
, NULL
, NULL
, CSF_UTIL_PVT.GET_OBJECT_NAME(CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID)
, TO_CHAR(CRLD.SOURCE_ID)
, 'RESERVED'
, MR.ORGANIZATION_ID
, MR.SUBINVENTORY_CODE
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_RESERVATIONS MR
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MR.RESERVATION_ID = CRLD.SOURCE_ID
AND CRH.TASK_ID IS NULL
AND CRLD.SOURCE_TYPE = 'RES' UNION ALL SELECT JTV.SOURCE_OBJECT_NAME
, JTV.TASK_NUMBER
, JTTV.NAME
, JTSV.NAME
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, CSF_UTIL_PVT.GET_OBJECT_NAME(NVL(JTA.RESOURCE_TYPE_CODE
, CRH.RESOURCE_TYPE)
, NVL(JTA.RESOURCE_ID
, CRH.RESOURCE_ID))
, TO_CHAR(OOHA.ORDER_NUMBER)
, CSP_PICK_UTILS.GET_LINE_STATUS_MEANING(OOLA.LINE_ID
, OOLA.BOOKED_FLAG
, OOLA.FLOW_STATUS_CODE)
, OOLA.SHIP_FROM_ORG_ID
, OOLA.SUBINVENTORY
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, OOLA.SCHEDULE_ARRIVAL_DATE
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_TASK_TYPES_VL JTTV
, JTF_TASK_STATUSES_VL JTSV
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, OE_ORDER_LINES_ALL OOLA
, OE_ORDER_HEADERS_ALL OOHA
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND JTV.TASK_ID = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTTV.TASK_TYPE_ID = JTV.TASK_TYPE_ID
AND JTSV.TASK_STATUS_ID = JTV.TASK_STATUS_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND OOLA.LINE_ID = CRLD.SOURCE_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND CRH.TASK_ID IS NOT NULL
AND CRLD.SOURCE_TYPE = 'IO' UNION ALL SELECT NULL
, NULL
, NULL
, NULL
, NULL
, CSF_UTIL_PVT.GET_OBJECT_NAME(CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID)
, TO_CHAR(OOHA.ORDER_NUMBER)
, CSP_PICK_UTILS.GET_LINE_STATUS_MEANING(OOLA.LINE_ID
, OOLA.BOOKED_FLAG
, OOLA.FLOW_STATUS_CODE)
, OOLA.SHIP_FROM_ORG_ID
, OOLA.SUBINVENTORY
, CRH.REQUIREMENT_HEADER_ID
, CRH.DESTINATION_ORGANIZATION_ID
, CRH.DESTINATION_SUBINVENTORY
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, OOLA.SCHEDULE_ARRIVAL_DATE
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, OE_ORDER_LINES_ALL OOLA
, OE_ORDER_HEADERS_ALL OOHA
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND OOLA.LINE_ID = CRLD.SOURCE_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND CRH.TASK_ID IS NULL
AND CRLD.SOURCE_TYPE = 'IO' UNION ALL SELECT JTV.SOURCE_OBJECT_NAME
, JTV.TASK_NUMBER
, JTTV.NAME
, JTSV.NAME
, CSF_UTIL_PVT.GET_OBJECT_NAME(JTV.OWNER_TYPE_CODE
, JTV.OWNER_ID)
, CSF_UTIL_PVT.GET_OBJECT_NAME(NVL(JTA.RESOURCE_TYPE_CODE
, CRH.RESOURCE_TYPE)
, NVL(JTA.RESOURCE_ID
, CRH.RESOURCE_ID))
, MTRH.REQUEST_NUMBER
, ML.MEANING
, MTRH.ORGANIZATION_ID
, MTRL.FROM_SUBINVENTORY_CODE
, CRH.REQUIREMENT_HEADER_ID
, MTRH.ORGANIZATION_ID
, MTRL.TO_SUBINVENTORY_CODE
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_TASK_TYPES_VL JTTV
, JTF_TASK_STATUSES_VL JTSV
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_TXN_REQUEST_LINES MTRL
, MTL_TXN_REQUEST_HEADERS MTRH
, MFG_LOOKUPS ML
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND JTV.TASK_ID = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTTV.TASK_TYPE_ID = JTV.TASK_TYPE_ID
AND JTSV.TASK_STATUS_ID = JTV.TASK_STATUS_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MTRL.LINE_ID = CRLD.SOURCE_ID
AND MTRH.HEADER_ID = MTRL.HEADER_ID
AND CRH.TASK_ID IS NOT NULL
AND CRLD.SOURCE_TYPE = 'MO'
AND ML.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND ML.LOOKUP_CODE = MTRH.HEADER_STATUS UNION ALL SELECT NULL
, NULL
, NULL
, NULL
, NULL
, CSF_UTIL_PVT.GET_OBJECT_NAME(CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID)
, MTRH.REQUEST_NUMBER
, ML.MEANING
, MTRH.ORGANIZATION_ID
, MTRL.FROM_SUBINVENTORY_CODE
, CRH.REQUIREMENT_HEADER_ID
, MTRH.ORGANIZATION_ID
, MTRL.TO_SUBINVENTORY_CODE
, CRH.NEED_BY_DATE
, MSIBK.PADDED_CONCATENATED_SEGMENTS
, MSIBK.INVENTORY_ITEM_ID
, MSIBK.DESCRIPTION
, NULL
, NULL
, NULL
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_TXN_REQUEST_LINES MTRL
, MTL_TXN_REQUEST_HEADERS MTRH
, MFG_LOOKUPS ML
, (SELECT FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG') INV_ORGANIZATION_ID
FROM DUAL ) A
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND MSIBK.ORGANIZATION_ID = A.INV_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MTRL.LINE_ID = CRLD.SOURCE_ID
AND MTRH.HEADER_ID = MTRL.HEADER_ID
AND CRH.TASK_ID IS NULL
AND CRLD.SOURCE_TYPE = 'MO'
AND ML.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND ML.LOOKUP_CODE = MTRH.HEADER_STATUS