Search 800 + 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 ,
          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 ;

1 comment:

  1. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training