Senfor eAM Preventative Maintenance Planning - CC BY İon Yılmaz

Description

Columns: Activity, Action, Pm Type, Area, Asset, Group, Scheduled Start Date, Scheduled Completion Date, Ot, Description ...

This report will extract all eAM preventative maintenance work orders that are pending release from the last forecast run.

Copy

select distinct
        (select msib.segment1 
            from inv.mtl_system_items_b msib
            where msib.inventory_item_id = ow.asset_activity_id
            and msib.organization_id = ow.organization_id) "Activity",
--ow.asset_activity_id,
        ow.WORK_ORDER_PM_ACTION_MEANING Action,
        ow.WORK_ORDER_PM_TYPE_MEANING "PM Type",
        (select mean.area 
            from apps.mtl_eam_asset_numbers_all_v mean
            where ow.asset_number = mean.instance_number) Area,
--       ow3.area,
        ow.ASSET_NUMBER Asset,
        (select mean.concatenated_segments 
            from apps.mtl_eam_asset_numbers_all_v mean
            where ow.asset_number = mean.instance_number) "Group",       
 --       ow3.concatenated_segments,
        ow.SCHEDULED_START_DATE,
        ow.SCHEDULED_COMPLETION_DATE,
        ow.WIP_ENTITY_NAME OT,
        ow.DESCRIPTION,
        ow.WO_STATUS_DISP "OT Status",
        ow.PM_NAME "PM Schedule",
        ow.WORK_ORDER_START_DATE "OT Start Date",
        ow.WORK_ORDER_COMPLETION_DATE "OT Completion Date",
        ow.OWNING_DEPARTMENT_CODE "Owning Department",
        ow.PRIORITY_MEANING Priority,
        ow.SHUTDOWN_TYPE_MEANING "Shutdown",
        ow.WORK_ORDER_TYPE_MEANING "OT Type",
        ow.ACTIVITY_TYPE_MEANING "Activity Type"
from apps.eam_outstanding_work_orders_v ow
left join apps.eam_outstanding_work_orders_v ow1 
on (ow.asset_activity_id = ow1.asset_activity_id
and ow.forecast_id < ow1.forecast_id
and OW.SCHEDULED_START_DATE = OW1.SCHEDULED_START_DATE)
where ow.organization_id = (select organization_id 
                                from apps.mtl_parameters 
                                where organization_code = 'EM1')
--and OW.SCHEDULED_START_DATE >= '29-DEC-18' 
--and OW.SCHEDULED_START_DATE <= '29-JAN-19' 
--and ow.OWNING_DEPARTMENT_CODE >= :Department_from
--and ow.OWNING_DEPARTMENT_CODE <= :Department_to
--and ow.status_type = 1
--and ow.asset_number = '191-M14388'
order by 1, ow.SCHEDULED_START_DATE