APPS.PQH_LENGTH_OF_SERVICE_PKG SQL Statements

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

Line: 17

   SELECT   effective_date
   INTO     l_date
   FROM     fnd_sessions
   WHERE    session_id = USERENV('sessionid');

Line: 50

  SELECT org_information1,      -- normal start time
         org_information2,      -- normal end time
         NVL(org_information3,0),  -- normal hours
         org_information4   -- frequency
  FROM   hr_organization_information
  WHERE  organization_id = p_bg_id
  AND    org_information_context = 'Work Day Information';

Line: 148

  SELECT   per.per_information15
  FROM     per_all_people_f per
  WHERE    per.person_id = p_person_id
  AND      p_determination_date between per.effective_start_date and per.effective_end_date;

Line: 154

 SELECT hr_api.return_legislation_code(per.business_group_id)
   FROM per_all_people_f per
  WHERE per.person_id = p_person_id
    AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;

Line: 160

 SELECT employee_category
   FROM per_all_assignments_f
  WHERE person_id = p_person_id
    AND p_determination_date between effective_start_date and effective_end_date;

Line: 227

      SELECT NVL(aat.absence_category,'*') ABSENCE_CATEGORY,
             NVL(paa.absence_days,0) ABSENCE_DAYS,
             NVL(paa.absence_hours,0) ABSENCE_HOURS,
             paa.date_start,
             paa.date_end
      FROM   per_absence_attendances paa,
             per_absence_attendance_types aat
      WHERE  paa.business_group_id = p_bg_id
      AND    paa.person_id  =  p_person_id
      AND   ( paa.date_start BETWEEN p_start_date AND  p_end_date
      OR      paa.date_end   BETWEEN p_start_date AND  p_end_date)
      AND    paa.absence_attendance_type_id = aat.absence_attendance_type_id;

Line: 242

      SELECT NVL(entitlement_value,0)
      FROM   pqh_situations
      WHERE  business_group_id = p_bg_id
      AND    situation_type = 'ABSENCE'
      AND    length_of_service = p_los_type
      AND    situation = p_abs_catg
      AND    employee_type = p_emp_type
      AND    g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
      AND    entitlement_flag = 'Y';

Line: 310

   SELECT  NVL(asg.employment_category,'$#') EMPLOYMENT_CATEGORY,
           asg.effective_start_date,
           asg.effective_end_date,
           NVL(asg.normal_hours,0) NORMAL_HOURS,
           asg.frequency
   FROM    per_all_assignments_f asg
   WHERE   asg.assignment_id = p_assignment_id
   AND    (p_end_date BETWEEN asg.effective_start_date  AND asg.effective_end_date
   OR     p_start_date BETWEEN  asg.effective_start_date AND asg.effective_end_date);

Line: 323

      SELECT NVL(worktime_proportional,'N'),
             NVL(entitlement_value,0)
      FROM   pqh_situations
      WHERE  business_group_id = p_bg_id
      AND    situation_type = 'PARTTIME'
      AND    length_of_service = p_los_type
--      AND    NVL(situation,'PT') = l_emp_catg
      AND    employee_type = l_emp_type
      AND    entitlement_flag = 'Y'
      AND    g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);

Line: 444

      SELECT situation, NVL(entitlement_value, 0)entitlement_value
      FROM   pqh_situations
      WHERE  business_group_id = p_bg_id
      AND    situation_type = 'EMPLOYMENT'
      AND    length_of_service = p_los_type
      AND    employee_type = l_emp_type
      AND    entitlement_flag = 'Y'
      AND    trunc(sysdate) between effective_start_date and NVL(effective_end_date,g_end_of_time);

Line: 454

    select pem.person_id, pem.business_group_id, pem.all_assignments, nvl(pjo.pjo_information2, 'XX') emp_type, pju.pju_information2 corps_definition_id,
            pju.pju_information3 grade_id, pju.pju_information4 step_id, pju.pju_information5 position_id,
                nvl(pjo.start_date, trunc(sysdate)) pjo_start_date,
                    nvl(pjo.end_date, trunc(sysdate)) pjo_end_date,
                    nvl(pju.start_date, trunc(sysdate)) pju_start_date,
                    nvl(pju.end_date, trunc(sysdate)) pju_end_date
        from per_previous_employers pem, per_previous_jobs pjo, per_previous_job_usages pju
    where
        pem.previous_employer_id = pjo.previous_employer_id(+)
        and
        pjo.previous_job_id = pju.previous_job_id(+)
        and
        pem.person_id = p_person_id
        and
        pjo.previous_job_id = p_previous_job_id;

Line: 471

select to_number(hsck.segment7) corps_id
from hr_soft_coding_keyflex hsck,
     fnd_id_flex_structures fifs,
     per_all_assignments_f paf,
     per_all_people_f ppf
where hsck.id_flex_num = fifs.id_flex_num
and fifs.id_flex_structure_code = 'FR_STATUTORY_INFO.'
and fifs.application_id = 800
and fifs.id_flex_code = 'SCL'
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and paf.person_id = ppf.person_id
and paf.primary_flag = 'Y'
and sysdate between paf.effective_start_date and paf.effective_end_date
and ppf.person_id = l_person_id;

Line: 487

select grade_id from
per_all_assignments_f
where person_id = l_person_id
and
primary_flag = 'Y'
and
sysdate between effective_start_date and effective_end_date;

Line: 496

select special_ceiling_step_id from
per_all_assignments_f
where person_id = l_person_id
and
primary_flag = 'Y'
and
sysdate between effective_start_date and effective_end_date;

Line: 505

select position_id from
per_all_assignments_f
where person_id = l_person_id
and
primary_flag = 'Y'
and
sysdate between effective_start_date and effective_end_date;

Line: 598

     SELECT  NVL(fnd_number.canonical_to_number(peit.pei_information4),0)
     FROM    per_people_extra_info peit
     WHERE   peit.person_id = p_person_id
     AND     peit.information_type = 'FR_PQH_ADDL_SENIORITY_INFO'
     AND     peit.pei_information1 = p_los_type
     AND     p_effective_date between fnd_date.canonical_to_date(peit.pei_information2)
     AND     NVL(fnd_date.canonical_to_date(peit.pei_information3),g_end_of_time);

Line: 637

      SELECT NVL(entitlement_value,0)
      FROM   pqh_situations
      WHERE  business_group_id = p_bg_id
      AND    situation_type = 'MILITARY'
      AND    length_of_service = p_los_type
      AND    employee_type = l_emp_type
      AND    entitlement_flag = 'Y'
      AND    g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);

Line: 647

       SELECT NVL(pei_information7,0) LENGTH_OF_SERVICE
       FROM   per_people_extra_info
       WHERE  person_id = p_person_id
       AND    information_type = 'FR_PQH_BONIFICATION_DETAILS'
       AND    (NVL(fnd_date.canonical_to_date(pei_information3),g_end_of_time) BETWEEN p_start_date AND p_end_date
       OR      fnd_date.canonical_to_date(pei_information2) BETWEEN p_start_date AND p_end_date  );

Line: 690

SELECT Min(effective_start_date)
FROM   per_all_assignments_f
WHERE  person_id = p_person_id
AND    assignment_id = p_assignment_id;

Line: 698

   SELECT date_start
   FROM   per_periods_of_service
   WHERE  person_id = p_person_id
   AND    business_group_id = p_bg_id
   AND    p_determination_date BETWEEN date_start AND NVL(actual_termination_date,g_end_of_time);

Line: 787

   SELECT     asg.assignment_id,
              asg.grade_id
   FROM       per_all_assignments_f asg
   WHERE      asg.person_id = p_person_id
   AND        (p_assignment_id IS NOT NULL OR asg.primary_flag ='Y')
   AND        (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
   AND         p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date;

Line: 801

   SELECT     asg.effective_start_date,
              asg.effective_end_date
   FROM       per_all_assignments_f asg
   WHERE      asg.assignment_id = l_assignment_id
   AND        asg.grade_id = l_grade_id
   AND        asg.effective_start_date <= p_determination_date
   ORDER BY   asg.effective_start_date, asg.effective_end_date;

Line: 902

   SELECT     asg.assignment_id,
              asg.position_id
   FROM       per_all_assignments_f asg
   WHERE      asg.person_id = p_person_id
   AND        (p_assignment_id IS NOT NULL OR asg.primary_flag ='Y')
   AND        (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
   AND         p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date;

Line: 913

   SELECT     asg.effective_start_date,
              asg.effective_end_date
   FROM       per_all_assignments_f asg
   WHERE      asg.assignment_id = l_assignment_id
   AND        asg.position_id = l_position_id
   AND        asg.effective_start_date <= p_determination_date
   ORDER BY   asg.effective_start_date, asg.effective_end_date;

Line: 1015

         SELECT asg.assignment_id, grade_ladder_pgm_id
           FROM per_all_assignments_f asg
          WHERE asg.person_id = p_person_id
            AND (p_assignment_id IS NOT NULL OR asg.primary_flag = 'Y')
            AND (p_assignment_id IS NULL
                 OR asg.assignment_id = p_assignment_id
                )
            AND p_determination_date BETWEEN asg.effective_start_date
                                         AND asg.effective_end_date;

Line: 1031

         SELECT asg.effective_start_date, asg.effective_end_date
           FROM per_all_assignments_f asg
          WHERE asg.assignment_id = l_assignment_id
            AND asg.effective_start_date <= p_determination_date
            AND asg.grade_ladder_pgm_id = l_corps_id;

Line: 1126

    SELECT spp.assignment_id,
           spp.step_id,
           spp.effective_start_date,
           spp.effective_end_date
    FROM   per_spinal_point_placements_f spp, per_all_assignments_f asg
    WHERE  asg.person_id = p_person_id
    AND    (p_assignment_id IS NOT NULL OR asg.primary_flag = 'Y')
    AND    (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
    AND    p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date
    AND    spp.assignment_id = asg.assignment_id
    AND    p_determination_date BETWEEN spp.effective_start_date and spp.effective_end_date;

Line: 1292

   SELECT   '1'
   FROM     per_all_people_f
   WHERE    person_id = p_person_id
   AND      l_determination_date BETWEEN effective_start_date AND effective_end_date;

Line: 1298

   SELECT   person_id
   FROM     per_all_assignments_f
   WHERE    assignment_id = p_assignment_id
   AND      l_determination_date BETWEEN effective_start_date AND effective_end_date;

Line: 1303

   SELECT   assignment_id
   FROM     per_all_assignments_f
   WHERE    person_id = p_person_id
   AND      primary_flag = 'Y'
   AND      l_determination_date BETWEEN effective_start_date AND effective_end_date;

Line: 1484

      SELECT '1'
      FROM   pqh_situations
      WHERE  business_group_id = p_bg_id
      AND    situation_type = 'MILITARY'
      AND    length_of_service = p_los_type
      AND    employee_type = l_emp_type
      AND    entitlement_flag = 'Y'
      AND    p_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
      AND    p_end_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);

Line: 1495

       SELECT nvl((p_end_date - p_start_date),0) los
       FROM   per_people_extra_info
       WHERE  person_id = p_person_id
       AND    information_type = 'DE_MILITARY_SERVICE'
       AND    p_start_date = fnd_date.canonical_to_date(pei_information1)
       AND    p_end_date = fnd_date.canonical_to_date(pei_information2);

Line: 1559

SELECT date_of_birth
  FROM per_all_people_f
 WHERE person_id = p_person_id
   AND effective_start_date <= p_assg_end_date
   AND effective_end_date >= p_assg_start_date;

Line: 1566

SELECT '1'
  FROM per_assignment_extra_info
 WHERE assignment_id = p_assignment_id
   AND nvl(aei_information1,'x') = p_los_type
   AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 1688

SELECT date_of_birth, nvl(per_information10,'N')
  FROM per_all_people_f
 WHERE person_id = p_person_id
   AND effective_start_date < p_assg_end_date
   AND effective_end_date > p_assg_start_date;

Line: 1695

SELECT '1'
  FROM per_assignment_extra_info
 WHERE assignment_id = p_assignment_id
   AND nvl(aei_information1,'x') = p_los_type
   AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 1702

 SELECT nvl(entitlement_value,0)
   FROM pqh_situations
  WHERE business_group_id = p_bg_id
    AND situation_type = 'PERSON'
    AND length_of_service = p_los_type
    AND situation = 'BEFORE_18'
    AND employee_type = l_emp_type
    AND p_assg_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
    AND p_assg_end_date  BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
    AND entitlement_flag = 'Y';

Line: 1810

SELECT employee_category, position_id
  FROM per_all_assignments_f
 WHERE person_id = p_person_id
   AND assignment_id = p_assignment_id
   AND effective_start_date = p_assg_start_date
   AND effective_end_date = decode(p_assg_end_date,trunc(sysdate),effective_end_date,p_assg_end_date);

Line: 1818

SELECT date_of_birth
  FROM per_all_people_f
 WHERE person_id = p_person_id
   AND effective_start_date <= p_assg_end_date
   AND effective_end_date >= p_assg_start_date;

Line: 1825

SELECT wkvr.tariff_contract_code
  FROM hr_all_positions_f pos, pqh_de_wrkplc_vldtn_vers wkvr
 WHERE pos.position_id = p_position_id
   AND pos.information1='WP'
   AND to_char(wkvr.wrkplc_vldtn_ver_id) = decode(pos.information6,'A',pos.information5,pos.information9)
   AND p_assg_end_date between pos.effective_start_date and pos.effective_end_date
   AND pos.business_group_id = wkvr.business_group_id;

Line: 1834

SELECT '1'
  FROM per_assignment_extra_info
 WHERE assignment_id = p_assignment_id
   AND nvl(aei_information1,'x') = p_los_type
   AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 1983

SELECT grade_id
  FROM per_all_assignments_f asg
 WHERE asg.person_id = p_person_id
   AND trunc(sysdate) between effective_start_date and effective_end_date;

Line: 2017

SELECT asg.grade_id
  FROM per_all_assignments_f asg
 WHERE asg.person_id = p_person_id
   AND asg.assignment_id = p_assignment_id
   AND asg.effective_start_date = p_assg_start_date
   AND asg.effective_end_date = decode(p_assg_end_date,trunc(sysdate),asg.effective_end_date,p_assg_end_date);

Line: 2025

SELECT grade_id
  FROM per_all_assignments_f asg
 WHERE asg.person_id = p_person_id
   AND trunc(sysdate) between effective_start_date and effective_end_date;

Line: 2031

SELECT '1'
  FROM per_assignment_extra_info
 WHERE assignment_id = p_assignment_id
   AND nvl(aei_information1,'x') = p_los_type
   AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 2118

SELECT  spp.step_id
  FROM  per_spinal_point_placements_f spp, per_all_assignments_f asg
 WHERE  asg.person_id = p_person_id
   AND  spp.assignment_id = asg.assignment_id
   AND  trunc(sysdate) BETWEEN spp.effective_start_date and spp.effective_end_date;

Line: 2148

    SELECT  spp.step_id,
            spp.effective_start_date,
            spp.effective_end_date
      FROM  per_spinal_point_placements_f spp, per_all_assignments_f asg
     WHERE  asg.person_id = p_person_id
       AND  asg.assignment_id = p_assignment_id
       AND  spp.assignment_id = asg.assignment_id
       AND  p_assg_end_date BETWEEN spp.effective_start_date and spp.effective_end_date;

Line: 2158

    SELECT '1'
      FROM per_assignment_extra_info
     WHERE assignment_id = p_assignment_id
       AND nvl(aei_information1,'x') = p_los_type
       AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 2253

  SELECT nvl(pjo_information4,'N')
    FROM per_previous_jobs
   WHERE previous_job_id = p_prev_job_id;

Line: 2281

SELECT '1'
  FROM per_assignment_extra_info
 WHERE assignment_id = p_assignment_id
   AND nvl(aei_information1,'x') = p_los_type
   AND information_type = 'DE_PQH_ASSG_LOS_INFO';

Line: 2376

 SELECT  '1'
   FROM  per_all_people_f
  WHERE  person_id = p_person_id
    AND  (p_assg_start_date BETWEEN effective_start_date AND effective_end_date
         or  l_end_date BETWEEN effective_start_date AND effective_end_date);

Line: 2383

 SELECT person_id, primary_flag
   FROM per_all_assignments_f
  WHERE assignment_id = p_assignment_id
    AND (p_assg_start_date BETWEEN effective_start_date AND effective_end_date
        or  l_end_date BETWEEN effective_start_date AND effective_end_date);

Line: 2390

 SELECT (end_date - start_date) los_days, previous_employer_id, pjo_information2, pjo_information3
   FROM per_previous_jobs
  WHERE previous_job_id = p_prev_job_id
    AND start_date = p_assg_start_date
    AND end_date   = l_end_date;

Line: 2397

SELECT nvl(employer_type,'x')
  FROM per_previous_employers
 WHERE previous_employer_id = l_prev_empl_id;

Line: 2402

SELECT date_of_birth
  FROM per_all_people_f
 WHERE person_id = p_person_id
   AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;

Line: 2408

 SELECT NVL(entitlement_value,0)
   FROM pqh_situations
  WHERE business_group_id = p_bg_id
    AND situation_type = 'EMPLOYMENT'
    AND length_of_service = p_los_type
    AND situation = decode(l_employer_type,'CM','C',l_employer_type)
    AND employee_type = l_emp_type
    AND p_assg_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
    AND l_end_date  BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
    AND entitlement_flag = 'Y';

Line: 2420

SELECT nvl(employment_category,'x')
  FROM per_previous_jobs
 WHERE previous_job_id = p_prev_job_id;

Line: 2656

SELECT nvl(pei.pei_information4,'0')
  FROM per_people_extra_info pei
 WHERE pei.person_id = p_person_id
   AND pei.information_type ='DE_PQH_POE_INFO'
   AND pei.pei_information1 = p_los_type
   AND p_effective_date BETWEEN fnd_date.canonical_to_date(pei.pei_information2)
       AND NVL(fnd_date.canonical_to_date(pei.pei_information3),g_end_of_time);

Line: 2680

select segment7  from hr_soft_coding_keyflex where id_flex_num in
(select id_flex_num from fnd_id_flex_structures fifs
where id_flex_structure_code = 'FR_STATUTORY_INFO.'
and fifs.application_id = 800 and fifs.id_flex_code = 'SCL')
and soft_coding_keyflex_id in
(select soft_coding_keyflex_id from per_all_assignments_f where
 assignment_id = p_assignment_id
 and business_group_id = p_bg_id);

Line: 2696

	select name into l_corps_name from pqh_corps_definitions
	where corps_definition_id = l_corps_id;

Line: 2708

	select name into l_corps_name from pqh_corps_definitions
	where corps_definition_id = p_corps_id;

Line: 2719

select name into l_grade_name from per_grades_vl where
grade_id = p_grade_id;