title: Query based on several conditions for status_type
body: <p>I want to fetch the latest ACTIVE STATUS TYPE WITH ACTION_CODE HIRE OR IF THE STATUS_TYPE HAS CHANGED FROM INACTIVE TO ACTIVE,
  then the query should fetch the first record just after the INACTIVE status.</p>
  
  <p>for example, shown below </p>
  
  <ul>
  <li>for employee_number 1, the query should fetch the <code>ACTION_CODE HIRE</code> with status_type <code>ACTIVE</code> i..e if there is no change in the status_type of the employee_number, then the first row. </li>
  <li>For employee_number 2, the query should return <code>ACTION_CODE ASG_CHANGE</code> AND <code>START_DATE 07-AUG-2019</code> with status_type <code>ACTIVE</code> i.e. first effective_start_date after the "INACTIVE" STATUS_TYPE. </li>
  <li>For employee_number 3, the query should return <code>ACTION_CODE GLB_TRANSFER</code> and <code>start_date 07-AUG-2019</code> with <code>status_type ACTIVE</code></li>
  <li>For employee_number 4, the query should not return anything as the latest record is <code>INACTIVE</code>.</li>
  </ul>
  
  <p>asg_table :</p>
  
  <pre><code>employee_number          start_Date        END_DATE                action_code            status_type  
  1                       01-JAN-2019        02-JUL-2019               HIRE                   ACTIVE  
  1                       03-JUL-2019        06-AUG-2019               ASG_CHANGE             ACTIVE
  1                       07-AUG-2019        31-DEC-4712               ASG_CHANGE             ACTIVE
  
  
  employee_number          start_Date        END_DATE                action_code            status_type  
  2                       01-JAN-2019        02-JUL-2019               HIRE                   ACTIVE  
  2                       03-JUN-2019        05-JUL-2019               TERM                   INACTIVE
  2                       07-AUG-2019        15-AUG-2019               ASG_CHANGE             ACTIVE
  2                       16-AUG-2019        31-DEC-4712               ASG_CHANGE             ACTIVE
  
  
  
  
  employee_number          start_Date        END_DATE                action_code            status_type  
  3                       01-JAN-2019        02-JUL-2019               HIRE                   ACTIVE  
  3                       03-JUN-2019        05-JUL-2019               GLB_TRANSFER          INACTIVE
  3                       07-AUG-2019        15-AUG-2019               GLB_TRANSFER             ACTIVE
  3                       16-AUG-2019        31-DEC-4712               ASG_CHANGE             ACTIVE
  
  
  employee_number          start_Date        END_DATE                action_code            status_type  
  4                       01-JAN-2019        02-JUL-2019               HIRE                   ACTIVE  
  4                       03-JUL-2019        06-AUG-2019               ASG_CHANGE             ACTIVE
  4                       07-AUG-2019        31-DEC-4712               TERM                   INACTIVE
  </code></pre>
  
  <p>What approach can I follow to include all the conditions in one query.</p>