View: WIP_EAM_WO_SRV_ASSOC_V

Product:	WIP - Work in Process
Description:
Implementation/DBA Data:	APPS.WIP_EAM_WO_SRV_ASSOC_V

View Text

SELECT WE.WIP_ENTITY_NAME WORK_ORDER_NAME
, EWODV.WORK_ORDER_STATUS AS STATUS
, BD.DEPARTMENT_CODE
, MSIBKFV1.CONCATENATED_SEGMENTS ASSET_GROUP
, CII.INSTANCE_NUMBER ASSET_NUMBER
, MSIBKFV2.CONCATENATED_SEGMENTS ACTIVITY
, MP.ORGANIZATION_CODE
, MLU1.MEANING PRIORITY
, DECODE(WDJ.FIRM_PLANNED_FLAG
, 1
, 'Y'
, 2
, 'N'
, 'N') FIRM_FLAG
, WDJ.DESCRIPTION
, EWOSA.SERVICE_REQUEST_ID
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, TRUNC((WDJ.SCHEDULED_COMPLETION_DATE-WDJ.SCHEDULED_START_DATE)*24
, 2) DURATION
, DECODE(WDJ.STATUS_TYPE
, 17
, TO_DATE(NULL)
, 1
, TO_DATE(NULL)
, WDJ.SCHEDULED_START_DATE) ACTUAL_START_DATE
, WDJ.DATE_COMPLETED ACTUAL_END_DATE
, WDJ.DATE_COMPLETED
, WDJ.OWNING_DEPARTMENT DEPARTMENT_ID
, WDJ.WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID
, WDJ.PRIORITY PRIORITY_ID
, EWODV.USER_DEFINED_STATUS_ID STATUS_ID
, NVL(WDJ.ASSET_GROUP_ID
, REBUILD_ITEM_ID) INVENTORY_ITEM_ID
, WDJ.PRIMARY_ITEM_ID ACTIVITY_ID
, WDJ.DATE_CLOSED
, MLU3.MEANING ASSET_TYPE
, WDJ.DATE_RELEASED
FROM (SELECT *
FROM EAM_WO_SERVICE_ASSOCIATION
WHERE ENABLE_FLAG IS NULL OR ENABLE_FLAG = 'Y') EWOSA
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, BOM_DEPARTMENTS BD
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MFG_LOOKUPS MLU1
, EAM_WORK_ORDER_DETAILS_V EWODV
, MFG_LOOKUPS MLU3
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV2
, CSI_ITEM_INSTANCES CII
WHERE MSIBKFV1.EAM_ITEM_TYPE=MLU3.LOOKUP_CODE(+)
AND MLU3.LOOKUP_TYPE (+) ='MTL_EAM_ASSET_TYPE'
AND WDJ.WIP_ENTITY_ID=EWOSA.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND NVL(WDJ.ASSET_GROUP_ID
, REBUILD_ITEM_ID)=MSIBKFV1.INVENTORY_ITEM_ID
AND WDJ.ORGANIZATION_ID= MP1.MAINT_ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = MSIBKFV1.ORGANIZATION_ID
AND WDJ.OWNING_DEPARTMENT=BD.DEPARTMENT_ID(+)
AND WDJ.ORGANIZATION_ID=BD.ORGANIZATION_ID(+)
AND MP.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WDJ.PRIORITY=MLU1.LOOKUP_CODE(+)
AND MLU1.LOOKUP_TYPE(+)='WIP_EAM_ACTIVITY_PRIORITY'
AND MLU1.ENABLED_FLAG(+)='Y'
AND MSIBKFV2.INVENTORY_ITEM_ID(+)=WDJ.PRIMARY_ITEM_ID
AND MSIBKFV2.ORGANIZATION_ID(+)=WDJ.ORGANIZATION_ID
AND WDJ.MAINTENANCE_OBJECT_ID= CII.INSTANCE_ID
AND WDJ.MAINTENANCE_OBJECT_TYPE = 3
AND EWODV.ORGANIZATION_ID = WDJ.ORGANIZATION_ID UNION ALL SELECT WE.WIP_ENTITY_NAME WORK_ORDER_NAME
, EWODV.WORK_ORDER_STATUS AS STATUS
, BD.DEPARTMENT_CODE
, MSIBKFV1.CONCATENATED_SEGMENTS ASSET_GROUP
, NULL ASSET_NUMBER
, MSIBKFV2.CONCATENATED_SEGMENTS ACTIVITY
, MP.ORGANIZATION_CODE
, MLU1.MEANING PRIORITY
, DECODE(WDJ.FIRM_PLANNED_FLAG
, 1
, 'Y'
, 2
, 'N'
, 'N') FIRM_FLAG
, WDJ.DESCRIPTION
, EWOSA.SERVICE_REQUEST_ID
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, TRUNC((WDJ.SCHEDULED_COMPLETION_DATE-WDJ.SCHEDULED_START_DATE)*24
, 2) DURATION
, DECODE(WDJ.STATUS_TYPE
, 17
, TO_DATE(NULL)
, 1
, TO_DATE(NULL)
, WDJ.SCHEDULED_START_DATE) ACTUAL_START_DATE
, WDJ.DATE_COMPLETED ACTUAL_END_DATE
, WDJ.DATE_COMPLETED
, WDJ.OWNING_DEPARTMENT DEPARTMENT_ID
, WDJ.WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID
, WDJ.PRIORITY PRIORITY_ID
, EWODV.USER_DEFINED_STATUS_ID STATUS_ID
, REBUILD_ITEM_ID INVENTORY_ITEM_ID
, WDJ.PRIMARY_ITEM_ID ACTIVITY_ID
, WDJ.DATE_CLOSED
, MLU3.MEANING ASSET_TYPE
, WDJ.DATE_RELEASED
FROM (SELECT *
FROM EAM_WO_SERVICE_ASSOCIATION
WHERE ENABLE_FLAG IS NULL OR ENABLE_FLAG = 'Y') EWOSA
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, BOM_DEPARTMENTS BD
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MFG_LOOKUPS MLU1
, EAM_WORK_ORDER_DETAILS_V EWODV
, MFG_LOOKUPS MLU3
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV2
WHERE MSIBKFV1.EAM_ITEM_TYPE=MLU3.LOOKUP_CODE(+)
AND MLU3.LOOKUP_TYPE (+) ='MTL_EAM_ASSET_TYPE'
AND WDJ.WIP_ENTITY_ID=EWOSA.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND NVL(WDJ.ASSET_GROUP_ID
, REBUILD_ITEM_ID)=MSIBKFV1.INVENTORY_ITEM_ID
AND WDJ.ORGANIZATION_ID= MP1.MAINT_ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = MSIBKFV1.ORGANIZATION_ID
AND WDJ.OWNING_DEPARTMENT=BD.DEPARTMENT_ID(+)
AND WDJ.ORGANIZATION_ID=BD.ORGANIZATION_ID(+)
AND MP.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WDJ.PRIORITY=MLU1.LOOKUP_CODE(+)
AND MLU1.LOOKUP_TYPE(+)='WIP_EAM_ACTIVITY_PRIORITY'
AND MLU1.ENABLED_FLAG(+)='Y'
AND MSIBKFV2.INVENTORY_ITEM_ID(+)=WDJ.PRIMARY_ITEM_ID
AND MSIBKFV2.ORGANIZATION_ID(+)=WDJ.ORGANIZATION_ID
AND WDJ.MAINTENANCE_OBJECT_TYPE = 2
AND EWODV.ORGANIZATION_ID = WDJ.ORGANIZATION_ID

Columns