EAM 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. Organization is mandatory. Additional parameters may be added as required.

Copyright 2020 Senfor International
All rights reserved. Permission to use this code is granted provided the original author is acknowledged. No warranties, express or otherwise are included in this permission. |

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

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.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 = :P_Organization
order by 1,7

Parameter Name

SQL text

Validation

Organization

ow.organization_id = :P_Organization

LOV Oracle

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(+)
)