Oracle EAM: WO Operation Details Query

Here is the oracle query to get the EAM work order operation level details.
This query will get you below details in WO Operation level.

1. Work Order Details.
2. WO Operation Details.
3. Asset Details- for which WO has been raised.
4. Project details-  if WO is related to project.
5. Resource details - To whom this WO operation is assigned.
6. PTW Details - Permit information

Here is the query.


/* Formatted on 07/12/2015 15:26:07 (QP5 v5.126.903.23003) */
SELECT   b.WIP_ENTITY_ID,
         b.OPERATION_SEQ_NUM OPERATION_SEQ_NUM,
         b.DESCRIPTION OP_DESC,
         --c.RESOURCE_SEQ_NUM,
         b.FIRST_UNIT_START_DATE OP_SCHEDULE_START_DATE,
         b.LAST_UNIT_COMPLETION_DATE Op_SCHEDULE_END_DATE,
         f.ACTUAL_START_DATE OP_ACTUAL_START_DATE,
         f.ACTUAL_END_DATE OP_ACTUAL_END_DATE,
         b.OPERATION_COMPLETED OPERATION_COMPLETED_FLG,
         c.RESOURCE_ID,
         NVL (i.PROJECT_ID, 0) AS PROJECT_ID,
         NVL (j.INSTANCE_ID, 0) AS ASSET_NUMBER_ID,
         NVL (i.OWNING_DEPARTMENT, 0) AS WO_OWNING_DEPARTMENT_ID,
         NVL (i.STATUS_TYPE, 0) AS WO_STATUS_ID,
         g.employee_number,
         g.full_name EMPLOYEE_NAME,
         d.START_DATE INS_START_DATE,
         d.COMPLETION_DATE INS_COMPLETION_DATE,
         (d.COMPLETION_DATE - d.START_DATE) * 24 Duration,
         b.ATTRIBUTE2 PTW_Number,
         b.ATTRIBUTE3 PTW_Type,
         b.ATTRIBUTE4 PTW_Status,
         TO_DATE (b.ATTRIBUTE6, 'yyyy/mm/dd hh24:mi:ss') PTW_DATE,
         h.USER_DEFINED_STATUS_ID WO_USER_DEFINED_STATUS_ID,
         b.ORGANIZATION_ID,
         b.creation_date WO_OP_CREATION_DATE,
         TO_DATE (b.ATTRIBUTE5, 'yyyy/mm/dd hh24:mi:ss') PTW_STATUS_DATE -- New Column,
  FROM   WIP_OPERATIONS b,
         WIP_OPERATION_RESOURCES c,
         WIP_OP_RESOURCE_INSTANCES d,
         BOM_RESOURCE_EMPLOYEES e,
         eam_work_order_details h,
         WIP_DISCRETE_JOBS I,
         CSI_ITEM_INSTANCES J,
         (SELECT   person_id, full_name, EMPLOYEE_NUMBER
            FROM   PER_ALL_PEOPLE_F
           WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                       AND  EFFECTIVE_END_DATE) g,
         (SELECT   ACTUAL_START_DATE,
                   ACTUAL_END_DATE,
                   WIP_ENTITY_ID,
                   OPERATION_SEQ_NUM,
                   LAST_UPDATE_DATE,
                   TRANSACTION_TYPE,
                   CREATION_DATE
            FROM   EAM_OP_COMPLETION_TXNS
           WHERE   (TRANSACTION_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM) IN
                         (  SELECT   MAX (TRANSACTION_ID) MAX_TRANSACTION,
                                     WIP_ENTITY_ID,
                                     OPERATION_SEQ_NUM
                              FROM   EAM_OP_COMPLETION_TXNS
                             WHERE   EAM_OP_COMPLETION_TXNS.TRANSACTION_TYPE =
                                        1
                          GROUP BY   WIP_ENTITY_ID, OPERATION_SEQ_NUM)) f
 WHERE       b.WIP_ENTITY_ID = c.WIP_ENTITY_ID(+)
         AND b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM(+)
         AND c.WIP_ENTITY_ID = d.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = d.OPERATION_SEQ_NUM(+)
         AND c.RESOURCE_SEQ_NUM = d.RESOURCE_SEQ_NUM(+)
         AND d.instance_id = e.instance_id(+)
         AND c.WIP_ENTITY_ID = f.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = f.OPERATION_SEQ_NUM(+)
         AND e.PERSON_ID = g.PERSON_ID(+)
         AND b.wip_entity_id = i.wip_entity_id
         AND b.WIP_ENTITY_ID = h.WIP_ENTITY_ID(+)
         AND I.ASSET_NUMBER = j.SERIAL_NUMBER(+)
       --AND b.WIP_ENTITY_ID     = 26006

Enjoy and Stay Well !! :)