Oracle EAM: WO Along With PO Details Query

Some users have a requirement to have the data for WO and PO together.

1. WO Information.
2. WO Operation
3. Project
4. Resource
5. GL Account
6. Asset
7. PTW Information
8. PO - If the WO is having PO.


Here is the query for that.

/* Formatted on 22/11/2015 11:59:30 (QP5 v5.126.903.23003) */
SELECT   a.ORGANIZATION_ID,
         a.WIP_ENTITY_ID,
         b.OPERATION_SEQ_NUM,
         b.DESCRIPTION OP_DESC,
         b.FIRST_UNIT_START_DATE OP_SCHEDULE_START_DATE,
         b.LAST_UNIT_COMPLETION_DATE OP_SCHEDULE_END_DATE,
         (b.LAST_UNIT_COMPLETION_DATE - b.FIRST_UNIT_START_DATE) * 24
            WO_OP_Duration,
         f.ACTUAL_START_DATE OP_ACTUAL_START_DATE,
         f.ACTUAL_END_DATE OP_ACTUAL_END_DATE,
         (a.SCHEDULED_COMPLETION_DATE - a.SCHEDULED_START_DATE) * 24
            WO_Duration,
         a.PROJECT_ID,
         a.TASK_ID,
         a.PM_SCHEDULE_ID,
         a.STATUS_TYPE WO_STATUS_ID,
         h.USER_DEFINED_STATUS_ID WO_USER_DEFINED_STATUS_ID,
         a.CLASS_CODE WIP_Account,
         a.MATERIAL_ACCOUNT MATERIAL_ACCOUNT_ID,
         a.NOTIFICATION_REQUIRED Scaffolding_Required_Flag,
         a.TAGOUT_REQUIRED Insulation_Required_Flag,
         b.ATTRIBUTE2 Permit_Number,
         b.ATTRIBUTE3 Permit_Type,
         b.ATTRIBUTE4 PTW_Status,
         b.ATTRIBUTE6 PTW_DATE,
         c.RESOURCE_ID,
         c.USAGE_RATE_OR_AMOUNT Quantity_Required,
         g.EMPLOYEE_NUMBER,
         g.FULL_NAME EMPLOYEE_NAME,
         PO.PR_NUMBER,
         PO.PR_LINE,
         PO.PR_REQUEST_DATE,
         PO.REQUESTER_NUMNER,
         PO.REQUESTER_NAME,
         PO.PR_STATUS,
         PO.PO_NUMBER,
         PO.PO_LINE,
         PO.RELEASE_NUM,
         PO.PO_CREATION_DATE,
         PO.PO_STATUS,
         PO.ITEM_ID,
         PO.QUANTITY_REQUESTED,
         PO.UNIT_PRICE,
         PO.QUANTITY_ONHAND,
         PO.QUANTITY_RECEIVED,
         PO.DELIVERY_TO,
         PO.DELIVERY_DATE,
         PO.TOTAL_COST,
         a.CREATION_DATE WO_CREATION_DATE,
         b.CREATED_BY OP_CREATED_BY,
         b.CREATION_DATE OP_CREATION_DATE,
         b.DEPARTMENT_ID OP_DEPARTMENT_ID,
         a.OWNING_DEPARTMENT WO_OWNING_DEPARTMENT,
         cii.INSTANCE_ID ASSET_ID,
         wr.WORK_REQUEST_ID WORK_REQUEST_ID,
         PO.PO_RELEASE_STATUS
  FROM   WIP_DISCRETE_JOBS a,
         WIP_OPERATIONS b,
         WIP_OPERATION_RESOURCES c,
         WIP_OP_RESOURCE_INSTANCES d,
         BOM_RESOURCE_EMPLOYEES e,
         CSI_ITEM_INSTANCES CII,
         WIP_EAM_WORK_REQUESTS WR,
         (SELECT   person_id, full_name, EMPLOYEE_NUMBER
            FROM   PER_ALL_PEOPLE_F
           WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                       AND  EFFECTIVE_END_DATE) g,
         eam_work_order_details h,
         (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,
         (SELECT   porl.wip_entity_id,
                   porl.wip_operation_seq_num OPERATION_SEQ_NUM,
                   porh.segment1 PR_NUMBER,
                   porl.line_num PR_LINE,
                   porh.creation_date PR_Request_date,
                   g.EMPLOYEE_NUMBER Requester_Numner,
                   g.FULL_NAME Requester_NAME,
                   porh.authorization_status PR_STATUS,
                   poh.segment1 PO_NUMBER,
                   pol.line_num PO_LINE,
                   por.RELEASE_NUM,
                   por.authorization_status PO_RELEASE_STATUS,
                   --                   (SELECT   RELEASE_NUM
                   --                      FROM   po_releases_all
                   --                     WHERE   po_release_id = poll.po_release_id)
                   --                      release_num,
                   poh.CREATION_DATE PO_CREATION_DATE,
                   poh.authorization_status PO_STATUS,
                   pol.item_id,
                   NVL (pol.quantity, poll.quantity) Quantity_requested,
                   pol.unit_price,
                   (SELECT   SUM (mohq.TRANSACTION_QUANTITY)
                      FROM   MTL_ONHAND_QUANTITIES mohq
                     WHERE   pol.item_id = mohq.inventory_item_id
                             AND poll.SHIP_TO_ORGANIZATION_ID =
                                   mohq.ORGANIZATION_ID)
                      QUANTITY_ONHAND,
                   poll.QUANTITY_RECEIVED,
                   --poll.SHIP_TO_LOCATION_ID,
                   (SELECT   DESCRIPTION
                      FROM   hr_locations
                     WHERE   LOCATION_ID = poll.SHIP_TO_LOCATION_ID)
                      Delivery_To,
                   rsh.creation_date Delivery_Date,
                   (NVL (pol.unit_price, 0) * NVL (poll.QUANTITY_RECEIVED, 0))
                      TOTAL_COST
            FROM   po_requisition_headers_all porh,
                   PO_REQUISITION_LINES_ALL porl,
                   po_req_distributions_all pord,
                   po_distributions_all pod,
                   po_lines_all pol,
                   po_headers_all poh,
                   po_line_locations_all poll,
                   rcv_shipment_lines rsl,
                   rcv_shipment_headers rsh,
                   po_releases_all por,
                   (SELECT   person_id, full_name, EMPLOYEE_NUMBER
                      FROM   PER_ALL_PEOPLE_F
                     WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                                 AND  EFFECTIVE_END_DATE) g
           WHERE   porh.requisition_header_id = porl.requisition_header_id(+)
                   AND porl.requisition_line_id = pord.requisition_line_id(+)
                   AND pord.distribution_id = pod.req_distribution_id(+)
                   AND pod.po_header_id = pol.po_header_id(+)
                   AND pod.po_line_id = pol.po_line_id(+)
                   AND pol.po_header_id = poh.po_header_id(+)
                   AND pod.po_header_id = poll.po_header_id(+)
                   AND pod.po_line_id = poll.po_line_id(+)
                   AND pod.line_location_id = poll.line_location_id(+)
                   AND pod.po_header_id = rsl.po_header_id(+)
                   AND pod.po_line_id = rsl.po_line_id(+)
                   AND pod.line_location_id = rsl.po_line_location_id(+)
                   AND rsl.shipment_header_id = rsh.shipment_header_id(+)
                   AND porl.to_person_id = g.person_id(+)
                   AND poll.po_release_id = por.po_release_id(+)
                   AND porl.wip_entity_id IS NOT NULL
          UNION ALL
          SELECT   a.WIP_ENTITY_ID,
                   a.OPERATION_SEQ_NUM,
                   c.REQUEST_NUMBER PR_MO_NUMBER,
                   b.line_number LINE_NUMBER,
                   c.CREATION_DATE REQUEST_DATE,
                   -- c.CREATED_BY,
                   g.EMPLOYEE_NUMBER,
                   g.FULL_NAME EMPLOYEE_NAME,
                   (SELECT   MEANING
                      FROM   FND_LOOKUP_VALUES_VL
                     WHERE   LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
                             AND LOOKUP_CODE = c.HEADER_STATUS)
                      STATUS,
                   NULL PO_NUMBER,
                   NULL PO_LINE,
                   NULL RELEASE_NUM,
                   NULL PO_RELEASE_STATUS,
                   NULL PO_CREATION_DATE,
                   NULL PO_STATUS,
                   b.inventory_item_id ITEM_ID,
                   b.quantity QUANTITY_REQUESTED,
                   d.ITEM_COST UNIT_PRICE,
                   (SELECT   SUM (mohq.TRANSACTION_QUANTITY)
                      FROM   MTL_ONHAND_QUANTITIES mohq
                     WHERE   b.INVENTORY_ITEM_ID = mohq.inventory_item_id
                             AND b.ORGANIZATION_ID = mohq.ORGANIZATION_ID)
                      QUANTITY_ONHAND,
                   b.QUANTITY_DELIVERED QUANTITY_RECEIVED,
                   NULL DELIVERY_TO,
                   NULL DELIVERY_DATE,
                   NVL ( (b.quantity * d.ITEM_COST), 0) TOTAL_COST
            FROM   WIP_REQUIREMENT_OPERATIONS a,
                   mtl_txn_request_lines b,
                   mtl_txn_request_headers c,
                   cst_item_costs d,
                   (SELECT   person_id, full_name, EMPLOYEE_NUMBER
                      FROM   PER_ALL_PEOPLE_F
                     WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                                 AND  EFFECTIVE_END_DATE) g
           WHERE       a.WIP_ENTITY_ID = b.TXN_SOURCE_ID(+)
                   AND a.inventory_item_id = b.inventory_item_id(+)
                   AND a.operation_seq_num = b.TXN_SOURCE_LINE_ID(+)
                   AND b.header_id = c.header_id
                   AND b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID(+)
                   AND b.ORGANIZATION_ID = d.ORGANIZATION_ID(+)
                   AND c.CREATED_BY = g.person_id(+)) PO
 WHERE       a.WIP_ENTITY_ID = b.WIP_ENTITY_ID(+)
         AND 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 a.WIP_ENTITY_ID = h.WIP_ENTITY_ID(+)
         AND e.PERSON_ID = g.PERSON_ID(+)
         AND b.WIP_ENTITY_ID = po.WIP_ENTITY_ID(+)
         AND b.OPERATION_SEQ_NUM = po.OPERATION_SEQ_NUM(+)
         AND a.MAINTENANCE_OBJECT_ID = cii.INSTANCE_ID(+)
         AND a.WIP_ENTITY_ID = wr.wip_entity_id(+)
-- AND b.DEPARTMENT_ID=63234
--AND a.WIP_ENTITY_ID=63234