Search from 700 + Posts

Dec 10, 2012

Query to get Resource and Operations Details associated with Work Order (in Oracle eAM)

Below is simple Query to return Resource and Operations Details associated with Work Order ( in Oracle eAM)
It has 2 parameters

  1. Work order ID
  2. Organization ID


SELECT wo.operation_seq_num ,
          wo.description,
          br.resource_code,
          NVL(wori.serial_number,ppf.full_name) Resource_details  ,
        TO_CHAR(wo.first_unit_start_date,'DD-MON-YYYY') first_unit_start_date,
        TO_CHAR(wo.last_unit_completion_date,'DD-MON-YYYY') last_unit_completion_date,
        NVL(wo.operation_completed,'N') operation_complete
    FROM wip_operations wo,
      wip_operation_resources wor,
      bom_resources br,
      wip_op_resource_instances wori,
      bom_resource_employees bre,
      PER_ALL_PEOPLE_F ppf
    WHERE wo.wip_entity_id             =wor.wip_entity_id(+)
    AND wo.operation_seq_num         =wor.operation_seq_num(+)
    AND wor.resource_id              =br.resource_id(+)

    AND wo.wip_entity_id             =wori.wip_entity_id(+)
    AND wo.operation_seq_num         =wori.operation_seq_num(+)
    AND wori.instance_id             = bre.instance_id(+)

    AND bre.person_id                = ppf.person_id(+)


    AND wo.wip_entity_id             = p_wip_entity_id

    AND wo.organization_id           = p_org_id
    Order By operation_seq_num ;



No comments:

Post a Comment