VIEW: APPS.WIP_EAM_WORK_ORDERS_V

Source

View Text - Preformatted

SELECT WDJ.ROWID, WDJ.WIP_ENTITY_ID as wip_entity_id, WE.WIP_ENTITY_NAME, WE.ROWID, WE.ENTITY_TYPE, WDJ.ORGANIZATION_ID, WDJ.LAST_UPDATE_DATE, WDJ.LAST_UPDATED_BY, WDJ.CREATION_DATE, WDJ.CREATED_BY, WDJ.LAST_UPDATE_LOGIN, WDJ.DESCRIPTION, WDJ.PRIMARY_ITEM_ID, nvl(wdj.asset_group_id, nvl(wdj1.asset_group_id, wdj1.rebuild_item_id)) as ASSET_GROUP_ID, decode(WDJ.ASSET_group_id, null, decode(wdj.parent_wip_entity_id, null, null, EAM_COMMON_UTILITIES_PVT.get_parent_asset(wdj.parent_wip_entity_id,wdj.organization_id)), cii.instance_number) AS ASSET_NUMBER, cii.instance_description, WDJ.CLASS_CODE, WDJ.STATUS_TYPE, WDJ.PM_SCHEDULE_ID, WDJ.MANUAL_REBUILD_FLAG, WDJ.WORK_ORDER_TYPE, LU2.MEANING WORK_ORDER_TYPE_DISP, WDJ.MATERIAL_ACCOUNT, WDJ.MATERIAL_OVERHEAD_ACCOUNT, WDJ.RESOURCE_ACCOUNT, WDJ.OUTSIDE_PROCESSING_ACCOUNT, WDJ.MATERIAL_VARIANCE_ACCOUNT, WDJ.RESOURCE_VARIANCE_ACCOUNT, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT, WDJ.STD_COST_ADJUSTMENT_ACCOUNT, WDJ.OVERHEAD_ACCOUNT, WDJ.OVERHEAD_VARIANCE_ACCOUNT, WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE, WDJ.DATE_RELEASED, WDJ.DATE_COMPLETED, WDJ.DATE_CLOSED, WDJ.OWNING_DEPARTMENT, BD.DEPARTMENT_CODE, WDJ.ACTIVITY_TYPE, LU3.MEANING ACTIVITY_TYPE_DISP, WDJ.ACTIVITY_CAUSE, LU4.MEANING ACTIVITY_CAUSE_DISP, WDJ.PRIORITY, LU6.MEANING PRIORITY_DISP, WDJ.REQUESTED_START_DATE, WDJ.DUE_DATE, WDJ.ESTIMATION_STATUS, WDJ.NOTIFICATION_REQUIRED, WDJ.SHUTDOWN_TYPE, LU5.MEANING, WDJ.TAGOUT_REQUIRED, WDJ.PLAN_MAINTENANCE, WDJ.FIRM_PLANNED_FLAG, WDJ.SCHEDULE_GROUP_ID, WSG.SCHEDULE_GROUP_NAME, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME, WDJ.PROJECT_ID, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME, WDJ.TASK_ID, WDJ.PARENT_WIP_ENTITY_ID, WE1.WIP_ENTITY_NAME, WDJ.REBUILD_ITEM_ID, decode(wdj.rebuild_item_id, null, null, cii.instance_number), WDJ.BOM_REFERENCE_ID, WDJ.ROUTING_REFERENCE_ID, WDJ.COMMON_BOM_SEQUENCE_ID, WDJ.COMMON_ROUTING_SEQUENCE_ID, WDJ.ALTERNATE_BOM_DESIGNATOR, WDJ.BOM_REVISION, WDJ.BOM_REVISION_DATE, WDJ.ALTERNATE_ROUTING_DESIGNATOR, WDJ.ROUTING_REVISION, WDJ.ROUTING_REVISION_DATE, WDJ.COMPLETION_SUBINVENTORY, WDJ.COMPLETION_LOCATOR_ID, WDJ.LOT_NUMBER, WDJ.DEMAND_CLASS, WDJ.ATTRIBUTE_CATEGORY, WDJ.ATTRIBUTE1, WDJ.ATTRIBUTE2, WDJ.ATTRIBUTE3, WDJ.ATTRIBUTE4, WDJ.ATTRIBUTE5, WDJ.ATTRIBUTE6, WDJ.ATTRIBUTE7, WDJ.ATTRIBUTE8, WDJ.ATTRIBUTE9, WDJ.ATTRIBUTE10, WDJ.ATTRIBUTE11, WDJ.ATTRIBUTE12, WDJ.ATTRIBUTE13, WDJ.ATTRIBUTE14, WDJ.ATTRIBUTE15, nvl(cii.instance_id, -1) AS INSTANCE_ID, nvl(cii.instance_number, '') AS INSTANCE_NUMBER, WDJ.MAINTENANCE_OBJECT_SOURCE, WDJ.MAINTENANCE_OBJECT_TYPE, LU7.MEANING AS SOURCE, WDJ.MAINTENANCE_OBJECT_ID, NULL AS SERVICE_REQUEST_ID, NULL AS SERVICE_REQUEST, WDJ.MATERIAL_ISSUE_BY_MO, WDJ.ACTIVITY_SOURCE, LU8.MEANING AS ACTIVITY_SOURCE_MEANING, NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID, WDJ.EAM_LINEAR_LOCATION_ID, ewodv.pending_flag, ewodv.user_defined_status_id, ewodv.work_order_status, ewodv.work_order_status_pending, ewodv.material_shortage_check_date, ewodv.material_shortage_flag, ewodv.material_shortage_disp, ewodv.workflow_type, ewodv.warranty_claim_status, ewodv.cycle_id, ewodv.seq_id, ewodv.ds_scheduled_flag, ewodv.estimate_id, ewodv.estimate_number, cii.serial_number,mp.maint_organization_id FROM WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE, WIP_ENTITIES WE1, csi_item_instances cii, WIP_SCHEDULE_GROUPS WSG, MFG_LOOKUPS LU2, MFG_LOOKUPS LU3, MFG_LOOKUPS LU4, MFG_LOOKUPS LU5, MFG_LOOKUPS LU6, BOM_DEPARTMENTS BD, MFG_LOOKUPS LU7, wip_discrete_jobs wdj1, MFG_LOOKUPS LU8, eam_work_order_details_v ewodv,mtl_parameters mp WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID and we.organization_id = wdj.organization_id AND WE.ENTITY_TYPE IN (6, 7) AND wdj.maintenance_object_type = 3 and wdj.maintenance_object_id = cii.instance_id AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE' AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE' AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE' AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE' AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE' AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE' and wdj.parent_wip_entity_id = wdj1.wip_entity_id(+) and wdj1.organization_id(+) = wdj.organization_id and wdj.wip_entity_id = ewodv.wip_entity_id and wdj.organization_id = ewodv.organization_id and mp.organization_id = cii.last_vld_organization_id union all SELECT WDJ.ROWID, WDJ.WIP_ENTITY_ID as wip_entity_id, WE.WIP_ENTITY_NAME, WE.ROWID, WE.ENTITY_TYPE, WDJ.ORGANIZATION_ID, WDJ.LAST_UPDATE_DATE, WDJ.LAST_UPDATED_BY, WDJ.CREATION_DATE, WDJ.CREATED_BY, WDJ.LAST_UPDATE_LOGIN, WDJ.DESCRIPTION, WDJ.PRIMARY_ITEM_ID, nvl(wdj1.asset_group_id,wdj1.rebuild_item_id) as ASSET_GROUP_ID, decode(wdj.parent_wip_entity_id, null, null, EAM_COMMON_UTILITIES_PVT.get_parent_asset(wdj.parent_wip_entity_id,wdj.organization_id)) AS ASSET_NUMBER, null, WDJ.CLASS_CODE, WDJ.STATUS_TYPE, WDJ.PM_SCHEDULE_ID, WDJ.MANUAL_REBUILD_FLAG, WDJ.WORK_ORDER_TYPE, LU2.MEANING WORK_ORDER_TYPE_DISP, WDJ.MATERIAL_ACCOUNT, WDJ.MATERIAL_OVERHEAD_ACCOUNT, WDJ.RESOURCE_ACCOUNT, WDJ.OUTSIDE_PROCESSING_ACCOUNT, WDJ.MATERIAL_VARIANCE_ACCOUNT, WDJ.RESOURCE_VARIANCE_ACCOUNT, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT, WDJ.STD_COST_ADJUSTMENT_ACCOUNT, WDJ.OVERHEAD_ACCOUNT, WDJ.OVERHEAD_VARIANCE_ACCOUNT, WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE, WDJ.DATE_RELEASED, WDJ.DATE_COMPLETED, WDJ.DATE_CLOSED, WDJ.OWNING_DEPARTMENT, BD.DEPARTMENT_CODE, WDJ.ACTIVITY_TYPE, LU3.MEANING ACTIVITY_TYPE_DISP, WDJ.ACTIVITY_CAUSE, LU4.MEANING ACTIVITY_CAUSE_DISP, WDJ.PRIORITY, LU6.MEANING PRIORITY_DISP, WDJ.REQUESTED_START_DATE, WDJ.DUE_DATE, WDJ.ESTIMATION_STATUS, WDJ.NOTIFICATION_REQUIRED, WDJ.SHUTDOWN_TYPE, LU5.MEANING, WDJ.TAGOUT_REQUIRED, WDJ.PLAN_MAINTENANCE, WDJ.FIRM_PLANNED_FLAG, WDJ.SCHEDULE_GROUP_ID, WSG.SCHEDULE_GROUP_NAME, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME, WDJ.PROJECT_ID, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME, WDJ.TASK_ID, WDJ.PARENT_WIP_ENTITY_ID, WE1.WIP_ENTITY_NAME, WDJ.REBUILD_ITEM_ID, null, WDJ.BOM_REFERENCE_ID, WDJ.ROUTING_REFERENCE_ID, WDJ.COMMON_BOM_SEQUENCE_ID, WDJ.COMMON_ROUTING_SEQUENCE_ID, WDJ.ALTERNATE_BOM_DESIGNATOR, WDJ.BOM_REVISION, WDJ.BOM_REVISION_DATE, WDJ.ALTERNATE_ROUTING_DESIGNATOR, WDJ.ROUTING_REVISION, WDJ.ROUTING_REVISION_DATE, WDJ.COMPLETION_SUBINVENTORY, WDJ.COMPLETION_LOCATOR_ID, WDJ.LOT_NUMBER, WDJ.DEMAND_CLASS, WDJ.ATTRIBUTE_CATEGORY, WDJ.ATTRIBUTE1, WDJ.ATTRIBUTE2, WDJ.ATTRIBUTE3, WDJ.ATTRIBUTE4, WDJ.ATTRIBUTE5, WDJ.ATTRIBUTE6, WDJ.ATTRIBUTE7, WDJ.ATTRIBUTE8, WDJ.ATTRIBUTE9, WDJ.ATTRIBUTE10, WDJ.ATTRIBUTE11, WDJ.ATTRIBUTE12, WDJ.ATTRIBUTE13, WDJ.ATTRIBUTE14, WDJ.ATTRIBUTE15, null AS INSTANCE_ID, null AS INSTANCE_NUMBER, WDJ.MAINTENANCE_OBJECT_SOURCE, WDJ.MAINTENANCE_OBJECT_TYPE, LU7.MEANING AS SOURCE, WDJ.MAINTENANCE_OBJECT_ID, NULL AS SERVICE_REQUEST_ID, NULL AS SERVICE_REQUEST, WDJ.MATERIAL_ISSUE_BY_MO, WDJ.ACTIVITY_SOURCE, LU8.MEANING AS ACTIVITY_SOURCE_MEANING, NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID, WDJ.EAM_LINEAR_LOCATION_ID, ewodv.pending_flag, ewodv.user_defined_status_id, ewodv.work_order_status, ewodv.work_order_status_pending, ewodv.material_shortage_check_date, ewodv.material_shortage_flag, ewodv.material_shortage_disp, ewodv.workflow_type, ewodv.warranty_claim_status, ewodv.cycle_id, ewodv.seq_id, ewodv.ds_scheduled_flag, ewodv.estimate_id, ewodv.estimate_number, null ,wdj.organization_id FROM WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE, WIP_ENTITIES WE1, WIP_SCHEDULE_GROUPS WSG, MFG_LOOKUPS LU2, MFG_LOOKUPS LU3, MFG_LOOKUPS LU4, MFG_LOOKUPS LU5, MFG_LOOKUPS LU6, BOM_DEPARTMENTS BD, MFG_LOOKUPS LU7, wip_discrete_jobs wdj1, MFG_LOOKUPS LU8 , eam_work_order_details_v ewodv WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID and we.organization_id = wdj.organization_id AND WE.ENTITY_TYPE IN (6, 7) AND wdj.maintenance_object_type = 2 AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE' AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE' AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE' AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE' AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE' AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE' and wdj.parent_wip_entity_id = wdj1.wip_entity_id(+) and wdj1.organization_id(+) = wdj.organization_id and wdj.wip_entity_id = ewodv.wip_entity_id and wdj.organization_id = ewodv.organization_id

View Text - HTML Formatted

SELECT WDJ.ROWID
, WDJ.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ROWID
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY
, WDJ.CREATION_DATE
, WDJ.CREATED_BY
, WDJ.LAST_UPDATE_LOGIN
, WDJ.DESCRIPTION
, WDJ.PRIMARY_ITEM_ID
, NVL(WDJ.ASSET_GROUP_ID
, NVL(WDJ1.ASSET_GROUP_ID
, WDJ1.REBUILD_ITEM_ID)) AS ASSET_GROUP_ID
, DECODE(WDJ.ASSET_GROUP_ID
, NULL
, DECODE(WDJ.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(WDJ.PARENT_WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID))
, CII.INSTANCE_NUMBER) AS ASSET_NUMBER
, CII.INSTANCE_DESCRIPTION
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, WDJ.WORK_ORDER_TYPE
, LU2.MEANING WORK_ORDER_TYPE_DISP
, WDJ.MATERIAL_ACCOUNT
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WDJ.RESOURCE_ACCOUNT
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WDJ.MATERIAL_VARIANCE_ACCOUNT
, WDJ.RESOURCE_VARIANCE_ACCOUNT
, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT
, WDJ.STD_COST_ADJUSTMENT_ACCOUNT
, WDJ.OVERHEAD_ACCOUNT
, WDJ.OVERHEAD_VARIANCE_ACCOUNT
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, WDJ.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, WDJ.ACTIVITY_TYPE
, LU3.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, LU4.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, LU6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, WDJ.NOTIFICATION_REQUIRED
, WDJ.SHUTDOWN_TYPE
, LU5.MEANING
, WDJ.TAGOUT_REQUIRED
, WDJ.PLAN_MAINTENANCE
, WDJ.FIRM_PLANNED_FLAG
, WDJ.SCHEDULE_GROUP_ID
, WSG.SCHEDULE_GROUP_NAME
, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME
, WDJ.PROJECT_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME
, WDJ.TASK_ID
, WDJ.PARENT_WIP_ENTITY_ID
, WE1.WIP_ENTITY_NAME
, WDJ.REBUILD_ITEM_ID
, DECODE(WDJ.REBUILD_ITEM_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, WDJ.BOM_REFERENCE_ID
, WDJ.ROUTING_REFERENCE_ID
, WDJ.COMMON_BOM_SEQUENCE_ID
, WDJ.COMMON_ROUTING_SEQUENCE_ID
, WDJ.ALTERNATE_BOM_DESIGNATOR
, WDJ.BOM_REVISION
, WDJ.BOM_REVISION_DATE
, WDJ.ALTERNATE_ROUTING_DESIGNATOR
, WDJ.ROUTING_REVISION
, WDJ.ROUTING_REVISION_DATE
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, WDJ.LOT_NUMBER
, WDJ.DEMAND_CLASS
, WDJ.ATTRIBUTE_CATEGORY
, WDJ.ATTRIBUTE1
, WDJ.ATTRIBUTE2
, WDJ.ATTRIBUTE3
, WDJ.ATTRIBUTE4
, WDJ.ATTRIBUTE5
, WDJ.ATTRIBUTE6
, WDJ.ATTRIBUTE7
, WDJ.ATTRIBUTE8
, WDJ.ATTRIBUTE9
, WDJ.ATTRIBUTE10
, WDJ.ATTRIBUTE11
, WDJ.ATTRIBUTE12
, WDJ.ATTRIBUTE13
, WDJ.ATTRIBUTE14
, WDJ.ATTRIBUTE15
, NVL(CII.INSTANCE_ID
, -1) AS INSTANCE_ID
, NVL(CII.INSTANCE_NUMBER
, '') AS INSTANCE_NUMBER
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, LU7.MEANING AS SOURCE
, WDJ.MAINTENANCE_OBJECT_ID
, NULL AS SERVICE_REQUEST_ID
, NULL AS SERVICE_REQUEST
, WDJ.MATERIAL_ISSUE_BY_MO
, WDJ.ACTIVITY_SOURCE
, LU8.MEANING AS ACTIVITY_SOURCE_MEANING
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID
, WDJ.EAM_LINEAR_LOCATION_ID
, EWODV.PENDING_FLAG
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS
, EWODV.WORK_ORDER_STATUS_PENDING
, EWODV.MATERIAL_SHORTAGE_CHECK_DATE
, EWODV.MATERIAL_SHORTAGE_FLAG
, EWODV.MATERIAL_SHORTAGE_DISP
, EWODV.WORKFLOW_TYPE
, EWODV.WARRANTY_CLAIM_STATUS
, EWODV.CYCLE_ID
, EWODV.SEQ_ID
, EWODV.DS_SCHEDULED_FLAG
, EWODV.ESTIMATE_ID
, EWODV.ESTIMATE_NUMBER
, CII.SERIAL_NUMBER
, MP.MAINT_ORGANIZATION_ID
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_ENTITIES WE1
, CSI_ITEM_INSTANCES CII
, WIP_SCHEDULE_GROUPS WSG
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
, MFG_LOOKUPS LU6
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU7
, WIP_DISCRETE_JOBS WDJ1
, MFG_LOOKUPS LU8
, EAM_WORK_ORDER_DETAILS_V EWODV
, MTL_PARAMETERS MP
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.MAINTENANCE_OBJECT_TYPE = 3
AND WDJ.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE'
AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE
AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE
AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE'
AND WDJ.PARENT_WIP_ENTITY_ID = WDJ1.WIP_ENTITY_ID(+)
AND WDJ1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = EWODV.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = EWODV.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID UNION ALL SELECT WDJ.ROWID
, WDJ.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ROWID
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY
, WDJ.CREATION_DATE
, WDJ.CREATED_BY
, WDJ.LAST_UPDATE_LOGIN
, WDJ.DESCRIPTION
, WDJ.PRIMARY_ITEM_ID
, NVL(WDJ1.ASSET_GROUP_ID
, WDJ1.REBUILD_ITEM_ID) AS ASSET_GROUP_ID
, DECODE(WDJ.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(WDJ.PARENT_WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID)) AS ASSET_NUMBER
, NULL
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, WDJ.WORK_ORDER_TYPE
, LU2.MEANING WORK_ORDER_TYPE_DISP
, WDJ.MATERIAL_ACCOUNT
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WDJ.RESOURCE_ACCOUNT
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WDJ.MATERIAL_VARIANCE_ACCOUNT
, WDJ.RESOURCE_VARIANCE_ACCOUNT
, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT
, WDJ.STD_COST_ADJUSTMENT_ACCOUNT
, WDJ.OVERHEAD_ACCOUNT
, WDJ.OVERHEAD_VARIANCE_ACCOUNT
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, WDJ.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, WDJ.ACTIVITY_TYPE
, LU3.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, LU4.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, LU6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, WDJ.NOTIFICATION_REQUIRED
, WDJ.SHUTDOWN_TYPE
, LU5.MEANING
, WDJ.TAGOUT_REQUIRED
, WDJ.PLAN_MAINTENANCE
, WDJ.FIRM_PLANNED_FLAG
, WDJ.SCHEDULE_GROUP_ID
, WSG.SCHEDULE_GROUP_NAME
, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME
, WDJ.PROJECT_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME
, WDJ.TASK_ID
, WDJ.PARENT_WIP_ENTITY_ID
, WE1.WIP_ENTITY_NAME
, WDJ.REBUILD_ITEM_ID
, NULL
, WDJ.BOM_REFERENCE_ID
, WDJ.ROUTING_REFERENCE_ID
, WDJ.COMMON_BOM_SEQUENCE_ID
, WDJ.COMMON_ROUTING_SEQUENCE_ID
, WDJ.ALTERNATE_BOM_DESIGNATOR
, WDJ.BOM_REVISION
, WDJ.BOM_REVISION_DATE
, WDJ.ALTERNATE_ROUTING_DESIGNATOR
, WDJ.ROUTING_REVISION
, WDJ.ROUTING_REVISION_DATE
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, WDJ.LOT_NUMBER
, WDJ.DEMAND_CLASS
, WDJ.ATTRIBUTE_CATEGORY
, WDJ.ATTRIBUTE1
, WDJ.ATTRIBUTE2
, WDJ.ATTRIBUTE3
, WDJ.ATTRIBUTE4
, WDJ.ATTRIBUTE5
, WDJ.ATTRIBUTE6
, WDJ.ATTRIBUTE7
, WDJ.ATTRIBUTE8
, WDJ.ATTRIBUTE9
, WDJ.ATTRIBUTE10
, WDJ.ATTRIBUTE11
, WDJ.ATTRIBUTE12
, WDJ.ATTRIBUTE13
, WDJ.ATTRIBUTE14
, WDJ.ATTRIBUTE15
, NULL AS INSTANCE_ID
, NULL AS INSTANCE_NUMBER
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, LU7.MEANING AS SOURCE
, WDJ.MAINTENANCE_OBJECT_ID
, NULL AS SERVICE_REQUEST_ID
, NULL AS SERVICE_REQUEST
, WDJ.MATERIAL_ISSUE_BY_MO
, WDJ.ACTIVITY_SOURCE
, LU8.MEANING AS ACTIVITY_SOURCE_MEANING
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID
, WDJ.EAM_LINEAR_LOCATION_ID
, EWODV.PENDING_FLAG
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS
, EWODV.WORK_ORDER_STATUS_PENDING
, EWODV.MATERIAL_SHORTAGE_CHECK_DATE
, EWODV.MATERIAL_SHORTAGE_FLAG
, EWODV.MATERIAL_SHORTAGE_DISP
, EWODV.WORKFLOW_TYPE
, EWODV.WARRANTY_CLAIM_STATUS
, EWODV.CYCLE_ID
, EWODV.SEQ_ID
, EWODV.DS_SCHEDULED_FLAG
, EWODV.ESTIMATE_ID
, EWODV.ESTIMATE_NUMBER
, NULL
, WDJ.ORGANIZATION_ID
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_ENTITIES WE1
, WIP_SCHEDULE_GROUPS WSG
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
, MFG_LOOKUPS LU6
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU7
, WIP_DISCRETE_JOBS WDJ1
, MFG_LOOKUPS LU8
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.MAINTENANCE_OBJECT_TYPE = 2
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE'
AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE
AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE
AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE'
AND WDJ.PARENT_WIP_ENTITY_ID = WDJ1.WIP_ENTITY_ID(+)
AND WDJ1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = EWODV.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = EWODV.ORGANIZATION_ID