APPS.HR_FR_MMO SQL Statements

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

Line: 21

SELECT ff.formula_id
FROM   ff_formulas_f ff
WHERE  ff.business_group_id = p_business_group_id and
       ff.formula_name = p_formula_name and
       (p_session_date BETWEEN ff.effective_start_date and ff.effective_end_date);

Line: 30

SELECT ff.formula_id
FROM   ff_formulas_f ff
WHERE  ff.legislation_code = p_legislation_code and
       ff.formula_name = p_formula_name and
       (p_session_date BETWEEN ff.effective_start_date and ff.effective_end_date);

Line: 82

   select max(a.effective_end_date) + 1
   into l_date
   from per_all_assignments_f       a,
        per_assignment_status_types s
   where a.person_id = p_person_id
     AND a.primary_flag = 'Y'
     AND a.effective_end_date < p_end_date
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND (s.per_system_status not in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
         OR a.establishment_id  <> p_establishment_id);

Line: 100

   select max(a.effective_start_date)
   into l_date
   from per_all_assignments_f       a,
        per_assignment_status_types s
   where a.person_id = p_person_id
     AND a.primary_flag = 'Y'
     AND a.effective_start_date < p_end_date
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND (s.per_system_status in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
     AND    a.establishment_id  = p_establishment_id)
and not exists
   (select null
   from per_all_assignments_f       a2,
        per_assignment_status_types s2
   where a2.person_id = p_person_id
     AND a2.primary_flag = 'Y'
     AND a2.effective_end_date = a.effective_start_date-1
     AND a2.assignment_status_type_id = s2.assignment_status_type_id
     AND (s2.per_system_status in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
     AND    a2.establishment_id  = p_establishment_id));

Line: 150

   select min(a.effective_start_date) - 1
   into l_date
   from per_all_assignments_f          a,
        per_assignment_status_types    s
   where  a.person_id = p_person_id
     AND a.primary_flag = 'Y'
     AND a.effective_start_date > p_start_date
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND (s.per_system_status not in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
         OR a.establishment_id  <> p_establishment_id);

Line: 168

   select min(a.effective_end_date)
   into l_date
   from per_all_assignments_f          a,
        per_assignment_status_types    s
   where  a.person_id = p_person_id
     AND a.primary_flag = 'Y'
     AND a.effective_end_date > p_start_date
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND a.assignment_status_type_id = s.assignment_status_type_id
     AND (s.per_system_status in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
     AND a.establishment_id  = p_establishment_id)
and not exists
   (select null
   from per_all_assignments_f       a2,
        per_assignment_status_types s2
   where a2.person_id = p_person_id
     AND a2.primary_flag = 'Y'
     AND a2.effective_start_date = a.effective_end_date+1
     AND a2.assignment_status_type_id = s2.assignment_status_type_id
     AND (s2.per_system_status in
           ('ACTIVE_ASSIGN',decode(p_include_suspended,'Y','SUSP_ASSIGN','ACTIVE_ASSIGN'))
     AND    a2.establishment_id  = p_establishment_id));

Line: 227

   select formula_id,effective_start_date
   into l_formula_id,l_effective_start_date
   from ff_formulas_f
   where formula_id = p_formula_id;