APPS.PER_IN_PERQUISITE_PKG SQL Statements

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

Line: 24

    SELECT pet.element_information1
          ,pet.element_type_id
	  ,pee.assignment_id
      FROM pay_element_types_f pet
          ,pay_element_entries_f pee
     WHERE pet.element_type_id =pee.element_type_id
       AND pee.element_entry_id =p_element_entry_id
       AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
       AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;

Line: 40

    SELECT  peev.screen_entry_value,
            peev.element_entry_value_id
      FROM  pay_element_entry_values_f peev
           ,pay_input_values_f piv
     WHERE  peev.element_entry_id = p_element_entry_id
       AND  piv.name = p_input_name
       AND  peev.input_value_id = piv.input_value_id
       AND  piv.element_Type_id = p_element_type_id
       AND  p_effective_date BETWEEN peev.effective_start_date
                                 AND peev.effective_end_date
       AND  p_effective_Date BETWEEN piv.effective_start_date
                                 AND piv.effective_end_date;

Line: 56

    SELECT global_value
      from ff_globals_f ffg
     WHERE ffg.global_name = l_global_name
       AND p_effective_date BETWEEN ffg.effective_start_date AND ffg.effective_end_date;

Line: 65

   SELECT piv.input_value_id
     FROM pay_input_values_f piv
    WHERE piv.element_type_id = p_element_type_id
      AND piv.NAME = p_input_name
      AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

Line: 82

  SELECT pee.element_entry_id
        ,pee.effective_start_date
    FROM pay_element_entries_f pee
        ,pay_element_entry_values_f peev1
	,pay_element_entry_values_f peev2
	,pay_element_entry_values_f peev3
    WHERE pee.assignment_id = p_assignment_id
      AND pee.element_type_id =	p_element_type_id
      and pee.element_entry_id =peev1.element_entry_id
      AND peev1.input_value_id = p_input_value_id
      and peev1.screen_entry_value ='CAR'
      and pee.element_entry_id =peev2.element_entry_id
      AND peev2.input_value_id = p_input_value_id_start
      and pee.element_entry_id =peev3.element_entry_id
      AND peev3.input_value_id = p_input_value_id_end
      AND fnd_date.canonical_to_date(peev2.screen_entry_value) <= nvl(l_benefit_end_date,to_date('31-12-4712','DD-MM-YYYY'))
      AND nvl(fnd_date.canonical_to_date(peev3.screen_entry_value),to_date('31-12-4712','DD-MM-YYYY'))   >= l_benefit_start_date
      AND pee.element_entry_id <> p_element_entry_id   -- Bugfix 4049484
      AND p_effective_date BETWEEN pee.effective_start_date   and pee.effective_end_date
      AND p_effective_date BETWEEN peev1.effective_start_date and peev1.effective_end_date
      AND p_effective_date BETWEEN peev2.effective_start_date and peev2.effective_end_date
      AND p_effective_date BETWEEN peev3.effective_start_date and peev3.effective_end_date;

Line: 110

   SELECT peev.screen_entry_value
     FROM pay_element_entry_values_f peev
    WHERE peev.element_entry_id = p_el_entry_id
      AND peev.input_value_id = p_inp_value_id
      AND p_eff_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date;

Line: 118

      SELECT pee.effective_start_date
            ,pet.element_type_id
            ,pee.assignment_id
        FROM pay_element_entries_f pee
            ,pay_element_types_f   pet
            ,pay_element_links_f   links
       WHERE pee.element_entry_id  = p_element_entry_id
         AND pet.element_type_id   = links.element_type_id
         AND links.element_link_id = pee.element_link_id
         AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
         AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;

Line: 212

        UPDATE pay_element_entry_values_f
	   SET screen_entry_value = to_char(p_effective_start_date,'YYYY/MM/DD HH24:MI:SS')
         WHERE element_entry_value_id =l_entry_value_id
	   AND effective_start_date =p_effective_start_date;

Line: 270

        SELECT pee.element_entry_id,pet.element_type_id
          FROM pay_element_entries_f pee,pay_element_types_f pet
         WHERE pee.assignment_id = p_assignment_id
          AND pee.element_type_id = pet.element_type_id
          AND pet.element_information1 = p_element_name
         GROUP BY pee.element_entry_id,pet.element_type_id
        HAVING MIN(pee.effective_start_date) >= p_tax_year_start;

Line: 527

        /* Update the Taxable Flag to 'Y' when limit is exceeded */
        FOR j IN 0 .. P_CNT - 1 LOOP

          pay_in_utils.set_location(g_debug,
                                    'Changing the following entries : ' ||
                                    l_element_entry_id(j),
                                    70);

Line: 534

          UPDATE pay_element_entry_values_f peev
             SET peev.screen_entry_value = 'Y'
           WHERE peev.element_entry_id = l_element_entry_id(j)
             AND peev.input_value_id = l_tax_input_value_id(j)
             AND p_effective_date BETWEEN peev.effective_start_date and
                 peev.effective_end_date
             AND nvl(peev.screen_entry_value, 'N') = 'N';

Line: 544

          /* Update the Taxable Flag to 'N' when the user accidentally enters incorrect values previously */
          pay_in_utils.set_location(g_debug,
                                    'Changing the following entries : ' ||
                                    l_element_entry_id(j),
                                    80);

Line: 549

          UPDATE pay_element_entry_values_f peev
             SET peev.screen_entry_value = 'N'
           WHERE peev.element_entry_id = l_element_entry_id(j)
             AND peev.input_value_id = l_tax_input_value_id(j)
             AND p_effective_date BETWEEN peev.effective_start_date and
                 peev.effective_end_date
             AND nvl(peev.screen_entry_value, 'Y') = 'Y';

Line: 564

      /* delete the PL/SQL table */
      l_element_entry_id.delete;

Line: 762

        l_element_entry_value.delete;

Line: 815

  IF  l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;

Line: 816

  IF  l_input_value_id.COUNT > 0 THEN l_input_value_id.delete; END IF;

Line: 817

  IF l_eff_start_date.COUNT > 0 THEN  l_eff_start_date.delete; END IF;

Line: 818

  IF l_element_entry_value.COUNT > 0 THEN l_element_entry_value.delete;  END IF;

Line: 854

  SELECT hrl.lookup_code
        ,hrl.meaning
    FROM hr_lookups hrl
   WHERE hrl.lookup_type ='IN_LTC_BLOCK'
     AND to_number(to_char(p_date,'YYYY')) BETWEEN
        to_number(SUBSTR(HRL.LOOKUP_CODE,1,4)) AND  to_number(SUBSTR(HRL.LOOKUP_CODE,8,4)) ;

Line: 866

  SELECT sum(nvl(ppm.pem_information8,0))
    FROM per_previous_employers ppm,
         per_all_assignments_f paa
   WHERE paa.assignment_id = p_assignment_id
     AND p_effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
     AND paa.person_id =ppm.person_id
     AND ppm.end_date BETWEEN p_start_date and p_end_date;

Line: 883

   SELECT count(*)
     FROM pay_element_entries_f pee
         ,pay_element_entry_values_f peev1
         ,pay_element_entry_values_f peev2
    WHERE pee.assignment_id = p_assignment_id
      AND pee.element_type_id = p_element_Type_id
      AND peev1.input_value_id = p_value_input
      AND peev1.element_entry_id =peev2.element_entry_id
      AND peev2.input_value_id = p_blk_input
      AND peev2.screen_entry_value = p_prev_block
      AND nvl(peev1.screen_entry_value,'N')='N'
      AND peev1.element_entry_id =pee.element_entry_id
      AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
      AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
      AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;

Line: 908

  SELECT count(*)
    FROM pay_element_entries_f pee
        ,pay_element_entry_values_f peev1
        ,pay_element_entry_values_f peev2
   WHERE pee.assignment_id = p_assignment_id
     AND pee.element_type_id = p_element_Type_id
     AND peev1.input_value_id = p_value_input
     AND peev1.element_entry_id =peev2.element_entry_id
     AND peev2.input_value_id = p_blk_input
     AND peev2.screen_entry_value = p_curr_block
     AND peev1.screen_entry_value='Y'
     AND peev1.element_entry_id =pee.element_entry_id
     AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
     AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
     AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;