APPS.PQP_GB_OSP_FUNCTIONS SQL Statements

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

Line: 186

  g_absences_taken_to_date.DELETE;     --pqp_absval_pkg.t_entitlements

Line: 197

  g_omp_absences_taken_to_date.DELETE;  --pqp_absval_pkg.t_entitlements

Line: 232

  g_ssp_input_values.DELETE;             --t_input_value_ids;

Line: 233

  g_ssp_element_links.DELETE;            --t_element_links;

Line: 236

  g_ssp_retro_input_values.DELETE;       --t_input_value_ids;

Line: 237

  g_ssp_retro_element_links.DELETE;      --t_element_links;

Line: 243

  g_smp_input_values.DELETE;             --t_input_value_ids;

Line: 244

  g_smp_element_links.DELETE;            --t_element_links;

Line: 247

  g_smp_retro_input_values.DELETE;       --t_input_value_ids;

Line: 248

  g_smp_retro_element_links.DELETE;      --t_element_links;

Line: 2057

          SELECT fnd_date.date_to_canonical(
                   ssp_smp_pkg.qualifying_week(fnd_date.canonical_to_date(l_due_date))
                 )
          INTO   l_val
          FROM   DUAL;

Line: 2088

          SELECT fnd_date.date_to_canonical(
                   ssp_smp_pkg.expected_week_of_confinement(fnd_date.canonical_to_date(l_due_date))
                 )
          INTO   l_val
          FROM   DUAL;

Line: 2155

    SELECT pei_information1  override_start_date_txt
          ,pei_information2  override_end_date_txt
          ,pei_information11 band1
          ,pei_information12 band2
          ,pei_information13 band3
          ,pei_information14 band4
    FROM   per_all_assignments_f asg
          ,per_people_extra_info pei
    WHERE  asg.assignment_id = p_assignment_id -- index primary key
      AND  p_effective_date
             BETWEEN asg.effective_start_date
                 AND asg.effective_end_date
      AND  pei.person_id = asg.person_id -- index PER_PEOPLE_EXTRA_INFO_N50
      AND  pei.information_type = 'PQP_GB_GAP_ENTITLEMENT_INFO'
      AND  pei.pei_information3 = p_pl_id_txt;

Line: 2374

    SELECT eei_information9 UOM -- "Absence Entitlement Days Type" segment
    FROM   pay_element_type_extra_info
    WHERE  information_type = 'PQP_GB_OMP_ABSENCE_PLAN_INFO' -- OMP col indexed
      AND  eei_information11 = -- "Absence Entitlement Parameters" segment
            fnd_number.number_to_canonical(p_entitlement_UDT_id);

Line: 2384

    SELECT eei_information8 UOM -- "Absence Days" segment
    FROM   pay_element_type_extra_info
    WHERE  information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' -- OSP col indexed
      AND  eei_information9 = -- "Absence Entitlement Sick Leave" segment
            fnd_number.number_to_canonical(p_entitlement_UDT_id);

Line: 3635

      SELECT (c_end_date - c_start_date + 1) cnt
      FROM   DUAL;

Line: 5950

      SELECT pln.pl_typ_id
            ,eei.eei_information3 cal_type
            ,eei.eei_information4 cal_duration
            ,eei.eei_information5 cal_uom
            ,eei.eei_information6 start_date
            ,eei.eei_information26 overlap_rul
-- Added for CS
            ,eei.eei_information20 dualrolling_dur
            ,eei.eei_information21 dualrolling_uom
            ,eei.eei_information17 default_work_pattern
 -- Added for LG/PT
            ,eei.eei_information22 track_part_timers
            ,eei.eei_information8 entitlement_uom
            ,eei.eei_information23 absence_schedule_wp
            ,eei.eei_information24 plan_types_to_extend_period
      FROM   pay_element_type_extra_info eei
            ,ben_pl_f pln
      WHERE  eei.element_type_id = p_element_type_id
        --AND  eei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' -- is indexed
        AND  UPPER(eei.eei_information19) = 'ABSENCE INFO'
        AND  pln.pl_id = fnd_number.canonical_to_number(eei.eei_information1)
        AND  p_effective_date BETWEEN pln.effective_start_date
                                  AND pln.effective_end_date;

Line: 6235

      SELECT eei.element_type_id
      FROM   pay_element_type_extra_info eei
      WHERE  eei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
      -- needs to be extended for omp
         AND eei.eei_information1 = fnd_number.number_to_canonical(p_pl_id)
         AND UPPER(eei.eei_information19) = 'ABSENCE INFO';

Line: 6632

      SELECT fnd_number.canonical_to_number(security_group_id)
                                                            security_group_id
      FROM   per_business_groups
      WHERE  business_group_id = p_business_group_id;

Line: 6645

      SELECT 'X'
      FROM   fnd_lookup_values_vl
      WHERE  lookup_type = p_lookup_type
      AND    lookup_code = p_lookup_code
      AND    security_group_id = c_security_group_id
      AND    view_application_id = c_view_application_id
      AND    enabled_flag = 'Y'
      AND    p_effective_date BETWEEN NVL(start_date_active, p_effective_date)
                                  AND NVL(end_date_active, p_effective_date);

Line: 6872

    SELECT pln.pl_typ_id
    FROM   pay_element_type_extra_info eei
          ,ben_pl_f pln
    WHERE  UPPER(eei.eei_information19) = 'ABSENCE INFO'
      AND  eei.element_type_id = p_element_type_id
      --AND  eei.information_type = 'PQP_GB_OMP_ABSENCE_PLAN_INFO'
      AND  pln.pl_id =
             fnd_number.canonical_to_number(eei.eei_information1)
      AND  p_effective_date BETWEEN pln.effective_start_date
                                AND pln.effective_end_date;

Line: 7542

  SELECT gap.gap_absence_plan_id id
        ,gap.last_gap_daily_absence_date
  FROM   pqp_gap_absence_plans gap
  WHERE  gap.absence_attendance_id = p_absence_attendance_id
    AND  gap.pl_id = p_pl_id;

Line: 7555

  SELECT MIN(gda.absence_date) abs_date
  FROM   pqp_gap_daily_absences gda
  WHERE  gda.gap_absence_plan_id = p_gap_absence_plan_id
    AND  ( ( p_level_of_entitlement IS NOT NULL
            AND
             gda.level_of_entitlement = p_level_of_entitlement
           )
          OR
           ( p_level_of_pay IS NOT NULL
            AND
             gda.level_of_pay = p_level_of_pay
           )
          OR
           ( p_level_of_entitlement IS NULL
            AND
             p_level_of_pay IS NULL
           )
         );

Line: 7580

  SELECT MAX(gda.absence_date) abs_date
  FROM   pqp_gap_daily_absences gda
  WHERE  gda.gap_absence_plan_id = p_gap_absence_plan_id
    AND  ( ( p_level_of_entitlement IS NOT NULL
            AND
             gda.level_of_entitlement = p_level_of_entitlement
           )
          OR
           ( p_level_of_pay IS NOT NULL
            AND
             gda.level_of_pay = p_level_of_pay
           )
         );

Line: 7962

  SELECT  pei_information1 override_start_date_txt
         ,pei_information2 override_end_date_txt
         ,pei_information3 pl_id_txt
  FROM    per_people_extra_info pei
  WHERE   pei.person_id = p_person_id
    AND   pei.information_type = p_information_type
    AND   pei.pei_information3 = p_pei_information3 -- fetch rows of same plan
    AND   pei.person_extra_info_id <> p_person_extra_info_id;

Line: 8333

  SELECT withhold_from
        ,NVL(withhold_to,withhold_from) withhold_to
  FROM   ssp_stoppages
  WHERE  absence_attendance_id = p_piw_id --index confirmed -- need not cache
    AND  override_stoppage = 'N';

Line: 10431

SELECT NVL(SUM(gda.duration),0) days
FROM pqp_gap_absence_plans gap
,pqp_gap_daily_absences gda
WHERE gap.assignment_id = p_assignment_id
AND gap.pl_id = p_plan_id
AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
-- commented out due to perf changes
-- AND EXISTS ( SELECT 1
-- FROM pay_element_type_extra_info eei
-- WHERE eei.eei_information30 IN ( 'Sickness','Maternity')
-- AND eei.eei_information1 = gap.pl_id
-- )
AND gda.level_of_pay = p_level_of_payment
AND gda.absence_date
BETWEEN p_range_start_date
AND p_range_end_date

;

Line: 10451

SELECT DISTINCT eei.eei_information1
FROM pay_element_type_extra_info eei
WHERE eei.information_type IN -- is indexed
('PQP_GB_OMP_ABSENCE_PLAN_INFO'
,'PQP_GB_OSP_ABSENCE_PLAN_INFO'
)
AND UPPER(eei_information30) IN
('SICKNESS'
,'MATERNITY'
);

Line: 10470

SELECT MAX(ABSENCE_DATE) NB_END_DT, MIN(ABSENCE_DATE) NB_START_DT
FROM pqp_gap_absence_plans gap
,pqp_gap_daily_absences gda
WHERE gap.assignment_id = p_assignment_id
AND gap.pl_id = p_plan_id
AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
AND gda.level_of_pay = p_level_of_payment
AND gda.absence_date
BETWEEN p_range_start_date
AND p_range_end_date
group by gda.gap_absence_plan_id;

Line: 10906

     SELECT  atd.abs_information4  start_date_txt --   minimum pay start day
            ,atd.abs_information5  end_date_txt   --  minimum pay end day
            ,atd.abs_information6  min_pay    --  minimum pay value
      FROM   per_absence_attendances atd, per_all_assignments_f asg
      WHERE  atd.abs_information_category = 'GB_PQP_OSP_OMP_PART_DAYS'
      AND    atd.absence_attendance_id = p_absence_id
      AND    atd.person_id = asg.person_id
      AND    asg.assignment_id = p_assignment_id
      AND    atd.business_group_id = p_business_group_id;

Line: 11218

  SELECT abs.person_id
  FROM   per_absence_attendances abs
  WHERE  abs.absence_attendance_id = p_absence_attendance_id
  AND    abs.business_group_id     = p_business_group_id ;

Line: 11224

  SELECT asg.person_id
  FROM   per_all_assignments_f asg
  WHERE  asg.assignment_id = p_assignment_id
  AND    rownum < 2 ;

Line: 11321

    select abs.person_id, type.absence_category
      from per_absence_attendances abs
          ,per_absence_attendance_types type
     where abs.absence_attendance_type_id = type.absence_attendance_type_id
       and abs.absence_attendance_id = p_absence_attendance_id
       and abs.business_group_id     = p_business_group_id ;

Line: 12121

select 1 from SSP_SMP_ENTRIES_V a1 , per_absence_attendances b1
where a1.person_id = b1.person_id and a1.person_id = l_person_id
and c_date between date_from and least(nvl(date_end, date_to), date_to)
union
(select 1 from SSP_SSP_ENTRIES_V where     person_id = l_person_id
and c_date between date_from and date_to
MINUS
SELECT 1 FROM ssp_stoppages_v x, per_absence_attendances y
WHERE X.absence_attendance_id = y.absence_attendance_id AND y.person_id = l_person_id
AND c_date BETWEEN withhold_from AND withhold_to
-- added for Bug 7304886
-- The Work-Off days falling in between Waiting days should NOT be
-- counted under paid days, even the date fall in SPP Paid Week.
MINUS
select 1 from
   (SELECT min(withhold_from) min_withhold_from
        , max(withhold_to) max_withhold_to
        , X.absence_attendance_id
    FROM ssp_stoppages_v x, per_absence_attendances y
    WHERE X.absence_attendance_id = y.absence_attendance_id
      AND y.person_id = l_person_id
      AND x.reason = 'Waiting day'
    GROUP BY X.absence_attendance_id
    )
  where c_date between min_withhold_from and max_withhold_to
)
-- addition for Bug 7304886 ends
union
select 1 from SSP_SAP_ENTRIES_V a2 , per_absence_attendances b2
where a2.person_id = b2.person_id and a2.person_id = l_person_id
and c_date between date_from and least(nvl(date_end, date_to), date_to )
union
select 1 from SSP_SPPA_ENTRIES_V a3 , per_absence_attendances b3
where a3.person_id = b3.person_id and a3.person_id = l_person_id
and c_date between date_from and least(nvl(date_end, date_to), date_to );

Line: 12158

SELECT person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id;