eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HR_DISCOVERER SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29


  SELECT a.organization_id,
         a.job_id,
         a.position_id,
         a.grade_id,
         a.location_id,
         b.date_start,
         SYSDATE
    FROM per_periods_of_service b,
         per_assignments_f a
   WHERE (TRUNC(SYSDATE)
         BETWEEN TRUNC(a.effective_start_date)
         AND     TRUNC(a.effective_end_date))
     AND a.assignment_id        = p_assignment_id
     AND a.period_of_service_id = b.period_of_service_id
     AND ROWNUM                 = 1;

Line: 48


  SELECT a.organization_id,
         a.job_id,
         a.position_id,
         a.grade_id,
         a.location_id,
         b.date_start,
         b.actual_termination_date
    FROM per_periods_of_service      b,
         per_assignment_status_types c,
         per_assignments_f           a
   WHERE (TRUNC(SYSDATE)                 > TRUNC(a.effective_end_date))
         AND a.assignment_id             = p_assignment_id
         AND a.period_of_service_id      = b.period_of_service_id
         AND a.assignment_status_type_id = c.assignment_status_type_id
         AND c.per_system_status         = 'ACTIVE_ASSIGN'
         AND a.effective_end_date        = b.actual_termination_date
         AND TRUNC(b.final_process_date)
           = TRUNC(b.actual_termination_date)
         AND ROWNUM                      = 1;

Line: 73


  SELECT a.organization_id,
         a.job_id,
         a.position_id,
         a.grade_id,
         a.location_id,
         b.date_start,
         b.actual_termination_date
    INTO l_organization_id,
         l_job_id,
         l_position_id,
         l_grade_id,
         l_location_id,
         l_service_date_start,
         l_actual_termination_date
    FROM per_periods_of_service      b,
         per_assignments_f           a,
         per_assignment_status_types c
   WHERE a.assignment_id             = p_assignment_id
     AND a.period_of_service_id      = b.period_of_service_id
     AND a.assignment_status_type_id = c.assignment_status_type_id
     AND ROWNUM                      = 1
     AND ((TRUNC(SYSDATE)             >= TRUNC(a.effective_start_date)
       AND c.per_system_status         = 'TERM_ASSIGN')
       OR (TRUNC(SYSDATE)              > TRUNC(a.effective_end_date)
       AND b.final_process_date        = b.actual_termination_date
       AND c.per_system_status         = 'ACTIVE_ASSIGN')
       OR (TRUNC(SYSDATE)
         BETWEEN TRUNC(a.effective_start_date)
             AND TRUNC(a.effective_end_date))
       AND c.per_system_status        not in ('ACTIVE_ASSIGN','TERM_ASSIGN'));

Line: 144


   SELECT MIN(ass.effective_start_date)
   INTO   l_date_start
   FROM   per_assignments_f     ass
   WHERE  ass.organization_id = l_organization_id
   AND    ass.assignment_id   = p_assignment_id
   AND    ass.assignment_type = 'E'
   AND    NOT EXISTS
    (
    SELECT null
    FROM   per_assignments_f ass1
    WHERE  ass1.assignment_id        = ass.assignment_id
    AND    NVL(ass1.organization_id,9.9)+0  = NVL(ass.organization_id,9.9)+0
    AND    ass1.effective_start_date =
           (
            SELECT MAX(ass2.effective_start_date)
            FROM   per_assignments_f ass2
            WHERE  ass2.assignment_id        = ass1.assignment_id
            AND    ass2.effective_start_date < ass.effective_start_date
           )
    AND    ass1.assignment_type = 'E'
    )
   AND    ass.business_group_id+0=
          NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id);  /* changed for bug 3294224 */

Line: 170


   SELECT MAX(cass.effective_end_date+1)
   INTO  l_change_date
   FROM  per_assignments_f cass
   WHERE cass.assignment_id         = p_assignment_id
   AND   cass.organization_id      <> l_organization_id
   AND   cass.effective_start_date  > l_date_start
   AND   cass.effective_end_date   <  l_actual_termination_date;

Line: 184


   SELECT MIN(ass.effective_start_date)
   INTO   l_date_start
   FROM   per_assignments_f     ass
   WHERE  ass.job_id          = l_job_id
   AND    ass.assignment_id   = p_assignment_id
   AND    ass.assignment_type = 'E'
   AND NOT EXISTS
    (
    SELECT null
    FROM   per_assignments_f ass1
    WHERE  ass1.assignment_id      = ass.assignment_id
    AND    NVL(ass1.job_id,9.9)+0  = NVL(ass.job_id,9.9)+0
    AND    ass1.effective_start_date =
           (
            SELECT MAX(ass2.effective_start_date)
            FROM   per_assignments_f ass2
            WHERE  ass2.assignment_id        = ass1.assignment_id
            AND    ass2.effective_start_date < ass.effective_start_date
           )
    AND    ass1.assignment_type  = 'E'
    )
   AND  ass.business_group_id+0=
     NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id);        /* changed for bug 3294224 */

Line: 210


   SELECT MAX(cass.effective_end_date+1)
   INTO  l_change_date
   FROM  per_assignments_f cass
   WHERE cass.assignment_id         = p_assignment_id
   AND   cass.job_id               <> l_job_id
   AND   cass.effective_start_date  > l_date_start
   AND   cass.effective_end_date   <  l_actual_termination_date;

Line: 224


   SELECT MIN(ass.effective_start_date)
   INTO   l_date_start
   FROM   per_assignments_f         ass
   WHERE  ass.position_id     = l_position_id
   AND    ass.assignment_id   = p_assignment_id
   AND    ass.assignment_type = 'E'
   AND NOT EXISTS
    (
    SELECT null
    FROM   per_assignments_f       ass1
    WHERE  ass1.assignment_id      = ass.assignment_id
    AND    NVL(ass1.position_id,9.9)+0 =
           NVL(ass.position_id,9.9)+0
    AND    ass1.effective_start_date =
           (
            SELECT MAX(ass2.effective_start_date)
            FROM   per_assignments_f ass2
            WHERE  ass2.assignment_id        = ass1.assignment_id
            AND    ass2.effective_start_date < ass.effective_start_date
           )
    AND    ass1.assignment_type = 'E'
    )
   AND  ass.business_group_id+0=
     NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id);  /* changed for bug 3294224 */

Line: 251


   SELECT MAX(cass.effective_end_date+1)
   INTO  l_change_date
   FROM  per_assignments_f cass
   WHERE cass.assignment_id        = p_assignment_id
   AND   cass.position_id         <> l_position_id
   AND   cass.effective_start_date > l_date_start
   AND   cass.effective_end_date  <  l_actual_termination_date;

Line: 265


   SELECT MIN(ass.effective_start_date)
   INTO   l_date_start
   FROM   per_assignments_f     ass
   WHERE  ass.grade_id        = l_grade_id
   AND    ass.assignment_id   = p_assignment_id
   AND    ass.assignment_type = 'E'
   AND NOT EXISTS
    (
    SELECT null
    FROM   per_assignments_f ass1
    WHERE  ass1.assignment_id        = ass.assignment_id
    AND    NVL(ass1.grade_id,9.9)+0  = NVL(ass.grade_id,9.9)+0
    AND    ass1.effective_start_date =
           (
            SELECT MAX(ass2.effective_start_date)
            FROM   per_assignments_f ass2
            WHERE  ass2.assignment_id        = ass1.assignment_id
            AND    ass2.effective_start_date < ass.effective_start_date
           )
    AND    ass1.assignment_type = 'E'
    )
    AND  ass.business_group_id+0=
     NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id);  /* changed for bug 3294224 */

Line: 291


   SELECT MAX(cass.effective_end_date+1)
   INTO  l_change_date
   FROM  per_assignments_f cass
   WHERE cass.assignment_id        = p_assignment_id
   AND   cass.grade_id            <> l_grade_id
   AND   cass.effective_start_date > l_date_start
   AND   cass.effective_end_date  <  l_actual_termination_date;

Line: 305


   SELECT MIN(ass.effective_start_date)
   INTO   l_date_start
   FROM   per_assignments_f     ass
   WHERE  ass.location_id     = l_location_id
   AND    ass.assignment_id   = p_assignment_id
   AND    ass.assignment_type = 'E'
   AND NOT EXISTS
    (
    SELECT null
    FROM   per_assignments_f ass1
    WHERE  ass1.assignment_id        = ass.assignment_id
    AND    NVL(ass1.location_id,9.9)+0  =
           NVL(ass.location_id,9.9)+0
    AND    ass1.effective_start_date =
           (
            SELECT MAX(ass2.effective_start_date)
            FROM   per_assignments_f ass2
            WHERE  ass2.assignment_id        = ass1.assignment_id
            AND    ass2.effective_start_date < ass.effective_start_date
           )
    AND    ass1.assignment_type = 'E'
    )
   AND  ass.business_group_id+0=
     NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id);      /* changed for bug 3294224 */

Line: 332


   SELECT MAX(cass.effective_end_date+1)
   INTO  l_change_date
   FROM  per_assignments_f cass
   WHERE cass.assignment_id        = p_assignment_id
   AND   cass.location_id         <> l_location_id
   AND   cass.effective_start_date > l_date_start
   AND   cass.effective_end_date  <  l_actual_termination_date;