EAM Work Order Details

Description
Columns: Asset Number, Asset Description, Area, Activity, Activity Description, Scheduled Start Date, Work Order, Work Order Status, Estimated Time, Activity Cause ...
Author: Ion Yilmaz 13.mai 2019
This report extracts eAM work order details from the eAM Work Order View.
The particularity of this report is that it will also search for any valid attributes from the eAM asset activities view. For example, in this case the asset activity attribute2 is used to identify if the asset activity is an ''At Risk'' activity. This attribute is also extracted in thi ... 
Author: Ion Yilmaz 13.mai 2019
This report extracts eAM work order details from the eAM Work Order View.
The particularity of this report is that it will also search for any valid attributes from the eAM asset activities view. For example, in this case the asset activity attribute2 is used to identify if the asset activity is an ''At Risk'' activity. This attribute is also extracted in this report.
The cumulative total lead time, if present, of the activities is also output.
The primary usage of this report is in the tracking and planning of eAM maintenance work orders.
Revison 1 13.mai 2019 logic change to allow work orders without activities assigned to be included.
Revision 2 6.juin2020 Added disable date control for ''At Risk'' activities
License: Creative Commons CC with Attribution
   more
select distinct
          ecwo.asset_number
        , ecwo.asset_number_description asset_description
        , ecwo.area
        , ecwo.asset_activity activity
        , ecwo.asset_activity_description activity_description
        , ecwo.scheduled_start_date
        , ecwo.wip_entity_name work_order
        , ecwo.system_status_disp work_order_status
        , (select msib.cumulative_total_lead_time*24 from mtl_system_items_b msib
          where ecwo.asset_activity_id = msib.inventory_item_id
          and     ecwo.organization_id = msib.organization_id) estimated_time
        , ecwo.activity_cause_disp activity_cause 
        , (select meaa.attribute2
               from mtl_eam_asset_activities_v meaa
               where ecwo.asset_activity_id = meaa.asset_activity_id
               and ecwo.organization_id = meaa.organization_id
               and nvl(end_date_active,sysdate + 1) > sysdate
               and meaa.attribute2 is not null
               and rownum=1) activity_at_risk      
        , ecwo.class_code
        , ecwo.owning_department_code department
        , ecwo.activity_type
        , ecwo.priority_disp priority
        , ecwo.shutdown_type_disp shutdown_type
from      eam_cfr_work_order_v ecwo
where   1=1
order by 4, 6
Parameter Name SQL text Validation
Organization
ecwo.organization_id=:P_organization_id
LOV Oracle
Scheduled start from
ecwo.scheduled_start_date>:P_SCHED_START_FROM
Date
Scheduled start to
ecwo.scheduled_start_date<:P_SCHED_START_TO
Date