VIEW: APPS.WIP_EAM_WORK_ORDER_DTLS_V

Source

View Text - Preformatted

SELECT job.wip_entity_id, entity.wip_entity_name, job.organization_id, decode(msi.eam_item_type, 1, cii.instance_number, null), cii.instance_description, job.asset_group_id, decode(msi.eam_item_type, 1, msi.concatenated_segments, null), job.primary_item_id, decode(msi.eam_item_type, 1, msi2.concatenated_segments, null), cii.category_id, mck.concatenated_segments, eomd.area_id, loc.location_codes, eomd.owning_department_id, bd2.department_code, job.status_type, statuslookup.meaning, job.work_order_type, job.description, job.priority, job.scheduled_start_date, job.scheduled_completion_date, job.activity_type, job.activity_cause, job.activity_source, job.owning_department, bd.department_code, job.class_code, job.requested_start_date, job.notification_required, job.shutdown_type, job.tagout_required, job.plan_maintenance, job.parent_wip_entity_id, entity2.wip_entity_name, job.rebuild_item_id, decode(msi.eam_item_type, 3, cii.instance_number, null), decode(job.parent_wip_entity_id, null, decode(job.asset_group_id, null, null, msi.concatenated_segments), ( SELECT DISTINCT msi3.concatenated_segments FROM mtl_system_items_kfv msi3, mtl_parameters mp WHERE msi3.organization_id = mp.organization_id AND msi3.inventory_item_id = nvl(parent.asset_group_id, parent.rebuild_item_id) AND mp.maint_organization_id = parent.organization_id ) ), decode(job.parent_wip_entity_id, null, decode(job.asset_group_id, null, null, cii.instance_number), EAM_COMMON_UTILITIES_PVT.get_parent_asset(job.parent_wip_entity_id,job.organization_id)), decode(msi.eam_item_type, 3, msi2.concatenated_segments, null), job.manual_rebuild_flag, job.last_update_date, decode(msi.eam_item_type, 3, msi.concatenated_segments, null), job.project_id, job.task_id, job.material_issue_by_mo, job.eam_linear_location_id, job.maintenance_object_type, job.maintenance_object_id, cii.serial_number, cii.supplier_warranty_exp_date, cii.network_asset_flag FROM wip_discrete_jobs job, wip_entities entity, wip_entities entity2, mfg_lookups statuslookup, mtl_system_items_kfv msi, csi_item_instances cii, mtl_eam_locations loc, mtl_system_items_kfv msi2, mtl_categories_kfv mck, bom_departments bd, bom_departments bd2, eam_org_maint_defaults eomd, wip_discrete_jobs parent WHERE job.wip_entity_id = entity.wip_entity_id AND entity.entity_type = decode(job.status_type,12,7,6) AND statuslookup.lookup_code = job.status_type AND statuslookup.lookup_type = upper('wip_job_status') AND entity2.wip_entity_id (+) = job.parent_wip_entity_id AND parent.wip_entity_id (+) = job.parent_wip_entity_id AND parent.organization_id (+) = job.organization_id AND job.maintenance_object_type = 3 AND job.maintenance_object_id = cii.instance_id AND msi.inventory_item_id = cii.inventory_item_id AND msi.organization_id = cii.last_vld_organization_id AND eomd.object_id = cii.instance_id AND eomd.organization_id = job.organization_id AND eomd.object_type = 50 AND loc.location_id (+) = eomd.area_id AND mck.category_id (+) = cii.category_id AND msi2.inventory_item_id (+) = job.primary_item_id AND msi2.organization_id (+) = job.organization_id AND bd.department_id (+) = job.owning_department AND bd.organization_id (+) = job.organization_id AND bd2.department_id (+) = eomd.owning_department_id UNION ALL SELECT job.wip_entity_id, entity.wip_entity_name, job.organization_id, decode(msi.eam_item_type, 1, cii.instance_number, null), cii.instance_description, job.asset_group_id, decode(msi.eam_item_type, 1, msi.concatenated_segments, null), job.primary_item_id, decode(msi.eam_item_type, 1, msi2.concatenated_segments, null), cii.category_id, mck.concatenated_segments, to_number(null), to_char(null), to_number(null), to_char(null), job.status_type, statuslookup.meaning, job.work_order_type, job.description, job.priority, job.scheduled_start_date, job.scheduled_completion_date, job.activity_type, job.activity_cause, job.activity_source, job.owning_department, bd.department_code, job.class_code, job.requested_start_date, job.notification_required, job.shutdown_type, job.tagout_required, job.plan_maintenance, job.parent_wip_entity_id, entity2.wip_entity_name, job.rebuild_item_id, decode(msi.eam_item_type, 3, cii.instance_number, null), decode(job.parent_wip_entity_id, null, decode(job.asset_group_id, null, null, msi.concatenated_segments), ( SELECT DISTINCT msi3.concatenated_segments FROM mtl_system_items_kfv msi3, mtl_parameters mp WHERE msi3.organization_id = mp.organization_id AND msi3.inventory_item_id = nvl(parent.asset_group_id, parent.rebuild_item_id) AND mp.maint_organization_id = parent.organization_id ) ), decode(job.parent_wip_entity_id, null, decode(job.asset_group_id, null, null, cii.instance_number), EAM_COMMON_UTILITIES_PVT.get_parent_asset(job.parent_wip_entity_id,job.organization_id)), decode(msi.eam_item_type, 3, msi2.concatenated_segments, null), job.manual_rebuild_flag, job.last_update_date, decode(msi.eam_item_type, 3, msi.concatenated_segments, null), job.project_id, job.task_id, job.material_issue_by_mo, job.eam_linear_location_id, job.maintenance_object_type, job.maintenance_object_id, cii.serial_number, cii.supplier_warranty_exp_date, cii.network_asset_flag FROM wip_discrete_jobs job, wip_entities entity, wip_entities entity2, mfg_lookups statuslookup, mtl_system_items_kfv msi, csi_item_instances cii, mtl_system_items_kfv msi2, mtl_categories_kfv mck, bom_departments bd, wip_discrete_jobs parent WHERE job.wip_entity_id = entity.wip_entity_id AND entity.entity_type = decode(job.status_type,12,7,6) AND statuslookup.lookup_code = job.status_type AND statuslookup.lookup_type = upper('wip_job_status') AND entity2.wip_entity_id (+) = job.parent_wip_entity_id AND parent.wip_entity_id (+) = job.parent_wip_entity_id AND parent.organization_id (+) = job.organization_id AND job.maintenance_object_type = 3 AND job.maintenance_object_id = cii.instance_id AND msi.inventory_item_id = cii.inventory_item_id AND msi.organization_id = cii.last_vld_organization_id AND mck.category_id (+) = cii.category_id AND msi2.inventory_item_id (+) = job.primary_item_id AND msi2.organization_id (+) = job.organization_id AND bd.department_id (+) = job.owning_department AND bd.organization_id (+) = job.organization_id AND NOT EXISTS (SELECT 1 FROM eam_org_maint_defaults eomd WHERE eomd.object_id = cii.instance_id AND eomd.organization_id = job.organization_id AND eomd.object_type = 50) UNION ALL SELECT job.wip_entity_id, entity.wip_entity_name, job.organization_id, null, ( SELECT DISTINCT msi.description FROM mtl_system_items_kfv msi, mtl_parameters mp WHERE msi.organization_id = mp.organization_id AND msi.inventory_item_id = job.maintenance_object_id AND mp.maint_organization_id = job.organization_id ) , null, null, job.primary_item_id, null, null, null, null, null, null, null, job.status_type, statuslookup.meaning, job.work_order_type, job.description, job.priority, job.scheduled_start_date, job.scheduled_completion_date, job.activity_type, job.activity_cause, job.activity_source, job.owning_department, bd.department_code, job.class_code, job.requested_start_date, job.notification_required, job.shutdown_type, job.tagout_required, job.plan_maintenance, job.parent_wip_entity_id, entity2.wip_entity_name, job.rebuild_item_id, null, decode (job.parent_wip_entity_id, null, null, ( SELECT DISTINCT msi3.concatenated_segments FROM mtl_system_items_kfv msi3, mtl_parameters mp WHERE msi3.organization_id = mp.organization_id AND msi3.inventory_item_id = nvl(parent.asset_group_id, parent.rebuild_item_id) AND mp.maint_organization_id = parent.organization_id ) ), decode(job.parent_wip_entity_id, null, null, EAM_COMMON_UTILITIES_PVT.get_parent_asset(job.parent_wip_entity_id,job.organization_id)), msi2.concatenated_segments, job.manual_rebuild_flag, job.last_update_date, ( SELECT DISTINCT msi.concatenated_segments FROM mtl_system_items_kfv msi, mtl_parameters mp WHERE msi.organization_id = mp.organization_id AND msi.inventory_item_id = job.maintenance_object_id AND mp.maint_organization_id = job.organization_id ) , job.project_id, job.task_id, job.material_issue_by_mo, job.eam_linear_location_id, job.maintenance_object_type, job.maintenance_object_id, TO_CHAR(NULL), TO_DATE(NULL), TO_CHAR(NULL) FROM wip_discrete_jobs job, wip_entities entity, wip_entities entity2, mfg_lookups statuslookup, mtl_system_items_kfv msi2, bom_departments bd , wip_discrete_jobs parent WHERE job.wip_entity_id = entity.wip_entity_id AND entity.entity_type = decode(job.status_type,12,7,6) AND statuslookup.lookup_code = job.status_type AND statuslookup.lookup_type = upper('wip_job_status') AND entity2.wip_entity_id (+) = job.parent_wip_entity_id AND job.maintenance_object_type = 2 AND parent.wip_entity_id (+) = job.parent_wip_entity_id AND parent.organization_id (+) = job.organization_id AND msi2.inventory_item_id (+) = job.primary_item_id AND msi2.organization_id (+) = job.organization_id AND bd.department_id (+) = job.owning_department AND bd.organization_id (+) = job.organization_id

View Text - HTML Formatted

SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, CII.INSTANCE_NUMBER
, NULL)
, CII.INSTANCE_DESCRIPTION
, JOB.ASSET_GROUP_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PRIMARY_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, CII.CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS
, EOMD.AREA_ID
, LOC.LOCATION_CODES
, EOMD.OWNING_DEPARTMENT_ID
, BD2.DEPARTMENT_CODE
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, CII.INSTANCE_NUMBER
, NULL)
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, MSI.CONCATENATED_SEGMENTS)
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, CII.SERIAL_NUMBER
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.NETWORK_ASSET_FLAG
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_EAM_LOCATIONS LOC
, MTL_SYSTEM_ITEMS_KFV MSI2
, MTL_CATEGORIES_KFV MCK
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENTS BD2
, EAM_ORG_MAINT_DEFAULTS EOMD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50
AND LOC.LOCATION_ID (+) = EOMD.AREA_ID
AND MCK.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD2.DEPARTMENT_ID (+) = EOMD.OWNING_DEPARTMENT_ID UNION ALL SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, CII.INSTANCE_NUMBER
, NULL)
, CII.INSTANCE_DESCRIPTION
, JOB.ASSET_GROUP_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PRIMARY_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, CII.CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, CII.INSTANCE_NUMBER
, NULL)
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, MSI.CONCATENATED_SEGMENTS)
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, CII.SERIAL_NUMBER
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.NETWORK_ASSET_FLAG
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_KFV MSI2
, MTL_CATEGORIES_KFV MCK
, BOM_DEPARTMENTS BD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCK.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50) UNION ALL SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, NULL
, ( SELECT DISTINCT MSI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = JOB.MAINTENANCE_OBJECT_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID )
, NULL
, NULL
, JOB.PRIMARY_ITEM_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, NULL
, DECODE (JOB.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, MSI2.CONCATENATED_SEGMENTS
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, ( SELECT DISTINCT MSI.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = JOB.MAINTENANCE_OBJECT_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID )
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI2
, BOM_DEPARTMENTS BD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 2
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID