EAM Work Order Details Extract for multiple activity assignments

Description
Columns: Equipement, Equipement Designation, Zone, Activité, Designation Activité, Date De Début Programmé, Ordre De Travail, Ot Status, Temps Prévu, Cause D'Activité ...
Author: Ion Yilmaz 15.jun 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 15.jun 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 0 15 June 2020 Modified version of EAM Work Order Details Extract to ensure that multiple activity assignments are correctly output.
License: Creative Commons CC with Attribution
   more
select distinct 
          ecwo.asset_number "Equipement"
        , ecwo.asset_number_description "Equipement designation"
        , NVL(ecwo.area, ' ') "Zone"
        , NVL(ecwo.asset_activity, 'NA') "Activité"
        , NVL(ecwo.asset_activity_description, ' ') "Designation activité"
        , ecwo.scheduled_start_date "Date de début programmé"
        , ecwo.wip_entity_name "Ordre de travail"
        , ecwo.system_status_disp "OT status"
        , NVL(to_char(msib.cumulative_total_lead_time*24, '999.99'), ' ') "Temps prévu"
        , NVL(ecwo.activity_cause_disp, ' ') "Cause d'activité"
        , NVL(meaa.attribute2, ' ') "Activité à risque"
        , ecwo.class_code "Classe en-cours"
        , ecwo.owning_department_code "Département"
        , ecwo.activity_type "Type d'activité"
        , NVL(ecwo.priority_disp, ' ') "Priorité de travail"
        , ecwo.shutdown_type_disp "Arrét"
from  apps.eam_cfr_work_order_v ecwo
     ,apps.mtl_eam_asset_activities_v meaa
     ,apps.mtl_system_items_b msib
     ,dual
where 1=1 and 2=2
and     ecwo.asset_activity_id = meaa.asset_activity_id
and     ecwo.organization_id = meaa.organization_id
and     NVL(meaa.end_date_active,sysdate + 1) > sysdate
and     ecwo.asset_activity_id = msib.inventory_item_id
order by 4, 3
Parameter Name SQL text Validation
Organization
ecwo.organization_id=(select organization_id from apps.mtl_parameters where organization_code = :P_ORGANIZATION)
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
Order Status
ecwo.user_defined_status_disp = :P_ORDER_STATUS
LOV