View: EAM_PM_SCHEDULINGS_V

Product:	EAM - Enterprise Asset Management
Description:	View is used to access individual Preventive Maintenance schedule.
Implementation/DBA Data:	APPS.EAM_PM_SCHEDULINGS_V

View Text

SELECT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_NUMBER "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, PM.MAINTENANCE_OBJECT_ID
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, CII.INSTANCE_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, CII.INSTANCE_DESCRIPTION
, CII.SERIAL_NUMBER
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, NVL(CII.NETWORK_ASSET_FLAG
, 'N')
, FU.USER_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME
, PM.GENERATE_NEXT_WORK_ORDER
FROM EAM_PM_SCHEDULINGS PM
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_PM_SET_NAMES EPSN
, MTL_PARAMETERS MP
, EAM_WO_STATUSES_V EWSV
, PER_PEOPLE_F PER
, FND_USER FU
WHERE NVL(PM.TMPL_FLAG
, 'N') = 'N'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND NVL(CII.MAINTAINABLE_FLAG
, 'Y') = 'Y'
AND PM.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND EWSV.STATUS_ID = PM.GENERATE_WO_STATUS
AND PM.MAINTENANCE_OBJECT_TYPE = 3
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND NVL(EAA.TMPL_FLAG
, 'N') = 'N') UNION ALL SELECT DISTINCT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, PM.MAINTENANCE_OBJECT_ID
, NULL "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, NULL
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, NULL
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, NULL
, NULL
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, 'N'
, FU.USER_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME
, PM.GENERATE_NEXT_WORK_ORDER
FROM EAM_PM_SCHEDULINGS PM
, EAM_PM_SET_NAMES EPSN
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_WO_STATUSES_V EWSV
, MTL_PARAMETERS MP
, PER_PEOPLE_F PER
, FND_USER FU
WHERE PM.TMPL_FLAG = 'Y'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND PM.MAINTENANCE_OBJECT_ID = MSIK.INVENTORY_ITEM_ID
AND EWSV.STATUS_ID=PM.GENERATE_WO_STATUS
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PM.MAINTENANCE_OBJECT_TYPE = 2
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND EAA.TMPL_FLAG = 'Y') UNION ALL SELECT DISTINCT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, PM.MAINTENANCE_OBJECT_ID
, NULL "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, NULL
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, NULL
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, NULL
, NULL
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, 'N'
, FU.USER_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME
, PM.GENERATE_NEXT_WORK_ORDER
FROM EAM_PM_SCHEDULINGS PM
, EAM_PM_SET_NAMES EPSN
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_WO_STATUSES_V EWSV
, MTL_PARAMETERS MP
, PER_PEOPLE_F PER
, FND_USER FU
WHERE NVL(PM.TMPL_FLAG
, 'N') = 'N'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND PM.MAINTENANCE_OBJECT_ID =MSIK.INVENTORY_ITEM_ID
AND MSIK.EAM_ITEM_TYPE = 3
AND MSIK.SERIAL_NUMBER_CONTROL_CODE = 1
AND EWSV.STATUS_ID=PM.GENERATE_WO_STATUS
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PM.MAINTENANCE_OBJECT_TYPE = 2
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND NVL(EAA.TMPL_FLAG
, 'N') = 'N')

Columns