EAM Senfor Intl Preventative Maintenance Forecast Planning

Description

This program extracts the latest preventative maintenance forecast from the EAM Outstanding Work Orders view for planning purposes. These include details of maintenance orders both in process and completed with order start and end dates.

The report can be run by any combination of organization, department, scheduled start dates, asset group and asset; see below. Additional parameters may be added as required.

Version Modified on Modified by   Description
===== ======== == ====== ========
1.0     04 Feb 2020 Ion Yılmaz     Initial Release

Copyright 2020 Senfor International Yönetim Danışmanlık Hizmetleri Ltd. Şti.

Original Author: İon Yılmaz ([email protected]) Senfor International, www.senfor-intl.eu ...    more

Copy

select distinct
ow.organization_id,
        (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.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
        ,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"
        ,DECODE(ow.SCHEDULED_START_DATE, NULL,'           ',ow.SCHEDULED_START_DATE) "Scheduled Start"
        ,DECODE(ow.SCHEDULED_COMPLETION_DATE, NULL, ow.WORK_ORDER_COMPLETION_DATE,ow.SCHEDULED_COMPLETION_DATE) "Scheduled Completion"
        ,NVL(ow.WIP_ENTITY_NAME, ' ') "Work Order"
        ,OW.DESCRIPTION "Description"
        ,OW.WO_STATUS_DISP "Order Status"
        ,OW.PM_NAME "PM Schedule"
        ,DECODE(ow.WORK_ORDER_START_DATE, NULL, '           ', ow.WORK_ORDER_START_DATE) "Order Start Date"
        ,DECODE(ow.WORK_ORDER_COMPLETION_DATE, NULL, '           ', ow.WORK_ORDER_COMPLETION_DATE) "Order Completion Date"
        ,NVL(ow.OWNING_DEPARTMENT_CODE, ' ') "Owning Department"
        ,OW.PRIORITY_MEANING Priority
        ,NVL(ow.SHUTDOWN_TYPE_MEANING, ' ') "Shutdown"
        ,NVL(ow.WORK_ORDER_TYPE_MEANING, ' ') "Order 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 1=1
--and ow.organization_id in (:P_Organization)
order by 1,2,8

Parameter Name

SQL text

Validation

Organization

ow.organization_id = :Organization

LOV

Department

ow.owning_department_code = :DEPARTMENT

LOV

Scheduled start from

ow.scheduled_start_date >= :P_SCHED_START_FROM

Date

Scheduled start to

ow.scheduled_start_date <= :P_SCHED_START_TO

Date

Asset Group

ow.maintenance_object_type=3 and
 ow.maintenance_object_id in (
select
cii0.instance_id 
from 
mtl_system_items_b_kfv msibk0, 
csi_item_instances cii0 
where 
msibk0.concatenated_segments = :asset_group and
 msibk0.organization_id = cii0.last_vld_organization_id and 
msibk0.inventory_item_id = cii0.inventory_item_id(+)
)