APPS.PAY_ZA_UPDATE_PKG SQL Statements

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

Line: 18

    SELECT
        COUNT(tysp_id) INTO l_result
    FROM
        pay_za_tys_processes
    WHERE
        payroll_id = p_payroll
        AND tax_year = p_tax_year;

Line: 32

END payroll_updateble;

Line: 48

    Select
        ptp.prd_information1 into l_tax_year
    From
        per_time_periods ptp
    Where
        ptp.payroll_id = p_payroll
    And (sysdate - 365) between ptp.start_date and ptp.end_date;

Line: 78

    SELECT MAX(ptp.end_date) + 1 INTO l_tax_year_end
    FROM
        per_time_periods ptp
    WHERE
        ptp.payroll_id = p_payroll
    AND ptp.prd_information1 = SUBSTR(p_tax_year,-4);

Line: 110

        SELECT
            peev.screen_entry_value INTO l_result
                FROM
                pay_element_entries_f pee,
                pay_element_entry_values_f peev,
                pay_input_values_f piv,
                pay_element_types_f pet,
                pay_element_links_f pel,
                per_assignments_f pa
                WHERE
                pee.element_entry_id = peev.element_entry_id
            AND piv.input_value_id = peev.input_value_id
            AND piv.name = p_record.name
            AND pee.element_entry_id = p_record.element_entry_id
            AND piv.input_value_id = p_record.input_value_id
            AND piv.input_value_id = peev.input_value_id
            AND pet.element_name = 'ZA_Tax'
            AND pel.element_type_id = pet.element_type_id
            AND pee.element_link_id = pel.element_link_id
            AND pa.assignment_id = pee.assignment_id
            AND (p_record.p_effective_date + 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
            AND (p_record.p_effective_date + 1)BETWEEN pee.effective_start_date AND pee.effective_end_date
            AND (p_record.p_effective_date + 1)BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND (p_record.p_effective_date + 1)BETWEEN pet.effective_start_date AND pet.effective_end_date
            AND (p_record.p_effective_date + 1)BETWEEN pel.effective_start_date AND pel.effective_end_date
            AND (p_record.p_effective_date + 1)BETWEEN pa.effective_start_date AND pa.effective_end_date;

Line: 148

        SELECT
            COUNT(piv.input_value_id) INTO l_count
        FROM
            pay_input_values_f piv,
            pay_element_types_f pet,
            pay_element_links_f pel,
            pay_element_entries_f pee,
            pay_element_entry_values_f peev
        WHERE
            pee.element_entry_id = peev.element_entry_id
        AND pee.element_entry_id = p_record.element_entry_id
        AND piv.input_value_id = peev.input_value_id
        AND pet.element_type_id = piv.element_type_id
        AND pet.element_name = 'ZA_Tax'
        AND pel.element_type_id = pet.element_type_id
        AND pee.element_link_id = pel.element_link_id
        AND (p_record.p_effective_date + 2) < peev.effective_start_date
        AND (p_record.p_effective_date + 2) < pee.effective_start_date
        AND (p_record.p_effective_date + 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
        AND (p_record.p_effective_date + 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
        AND (p_record.p_effective_date + 1) BETWEEN pel.effective_start_date AND pel.effective_end_date;

Line: 177

        SELECT
            peev.screen_entry_value INTO l_result
                FROM
                pay_element_entries_f pee,
                pay_element_entry_values_f peev,
                pay_input_values_f piv,
                pay_element_types_f pet,
                pay_element_links_f pel,
                per_assignments_f pa
                WHERE
                pee.element_entry_id = peev.element_entry_id
            AND piv.input_value_id = peev.input_value_id
            AND piv.name = p_record.name
            AND pee.element_entry_id = p_record.element_entry_id
            AND piv.input_value_id = p_record.input_value_id
            AND piv.input_value_id = peev.input_value_id
            AND pet.element_name = 'ZA_Tax'
            AND pel.element_type_id = pet.element_type_id
            AND pee.element_link_id = pel.element_link_id
            AND pa.assignment_id = pee.assignment_id
            AND (p_record.p_effective_date - 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
            AND (p_record.p_effective_date - 1) BETWEEN pee.effective_start_date AND pee.effective_end_date
            AND (p_record.p_effective_date - 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND (p_record.p_effective_date - 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
            AND (p_record.p_effective_date - 1) BETWEEN pel.effective_start_date AND pel.effective_end_date
            AND (p_record.p_effective_date - 1) BETWEEN pa.effective_start_date AND pa.effective_end_date;

Line: 210

    ELSIF p_validation_mode = 'DELETE_NEXT_CHANGE' THEN
         /* Bug 5956650
         to check if there exists an element_entry row (which we intend to rollback)
         after the current row*/
         select ee.effective_start_date,
                ee.effective_end_date
         into   l_current_eff_start_date,
                l_current_eff_end_date
         from   pay_element_entries_f ee,
            pay_element_links_f el,
            pay_element_types_f et,
            pay_element_classifications ec
         where  ee.element_entry_id = p_record.element_entry_id
           and  el.element_link_id = ee.element_link_id
           and  et.element_type_id = el.element_type_id
           and  ec.classification_id = et.classification_id
           and  p_record.p_effective_date between ee.effective_start_date
                               and ee.effective_end_date
           and  p_record.p_effective_date between el.effective_start_date
                               and el.effective_end_date
           and  p_record.p_effective_date between et.effective_start_date
                               and et.effective_end_date;

Line: 233

         select count(ee.effective_end_date)
         into   l_count
         from   pay_element_entries_f ee
         where  ee.element_entry_id = p_record.element_entry_id
           and  ee.effective_start_date > l_current_eff_end_date;

Line: 248

/* This procedure updates the record that is passed in as a parameer. */

PROCEDURE update_this_record
          (
           p_one_record  Pay_Za_Tax_Year_Start_Pkg.c_entry_details%ROWTYPE,
           p_new_value   VARCHAR2,
           p_update_mode VARCHAR2
          )
AS
BEGIN
    hr_utility.set_location('Update_this_record ',10);

Line: 259

    hr_entry_api.update_element_entry
                 (
                  p_dt_update_mode   => p_update_mode,
                  p_session_date     => p_one_record.p_effective_date + 1,
                  p_element_entry_id => p_one_record.element_entry_id,
                  p_input_value_id1  => p_one_record.input_value_id,
                  p_entry_value1     => p_new_value
                 );

Line: 268

END update_this_record;

Line: 270

/* This procedure updates the PAY_ZA_TYS_PROCESSES table to ensure that
   the process will not be run for the same payroll and the same tax year.*/

PROCEDURE update_tysp_table
          (
           p_payroll  NUMBER,
           p_tax_year NUMBER
          )
AS
BEGIN
    INSERT INTO pay_za_tys_processes (
      TYSP_ID
    , PAYROLL_ID
    , CONSOLIDATION_SET_ID
    , TAX_YEAR
    )
    VALUES
    (
     pay_za_tys_processes_s.NEXTVAL,
     p_payroll,
     0,
     p_tax_year
    );

Line: 294

END update_tysp_table;

Line: 296

/* This procedure deletes the PAY_ZA_TYS_PROCESSES table to indicate that the
   TYSP process was rolled back for this specific payroll.  In other words if
   the user wants to run the process for this payroll, he will be allowed to. */

PROCEDURE delete_tysp_table
          (
           p_payroll  NUMBER,
           p_tax_year NUMBER
          )
AS
BEGIN

    DELETE FROM pay_za_tys_processes
    WHERE  payroll_id = p_payroll
    AND    tax_year = p_tax_year;

Line: 312

END delete_tysp_table;

Line: 326

    SELECT
        COUNT(tysp_id) INTO l_result
    FROM
        pay_za_tys_processes
    WHERE
        payroll_id = p_payroll
    AND tax_year = p_tax_year;

Line: 354

    SELECT
        peev.screen_entry_value INTO l_result
    FROM
                pay_element_entries_f pee,
                pay_element_entry_values_f peev,
                pay_input_values_f piv,
                pay_element_types_f pet,
                pay_element_links_f pel,
                per_assignments_f pa
    WHERE
                pee.element_entry_id = peev.element_entry_id
        AND     piv.input_value_id = peev.input_value_id
        AND     piv.name = p_record.name
        AND     pee.element_entry_id = p_record.element_entry_id
        AND     piv.input_value_id = p_record.input_value_id
        AND     piv.input_value_id = peev.input_value_id
        AND     pet.element_name = 'ZA_Tax'
        AND     pel.element_type_id = pet.element_type_id
        AND     pee.element_link_id = pel.element_link_id
        AND     pa.assignment_id = pee.assignment_id
        AND     (p_record.p_effective_date - 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
        AND     (p_record.p_effective_date - 1) BETWEEN pee.effective_start_date AND pee.effective_end_date
        AND     (p_record.p_effective_date - 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
        AND     (p_record.p_effective_date - 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
        AND     (p_record.p_effective_date - 1) BETWEEN pel.effective_start_date AND pel.effective_end_date
        AND     (p_record.p_effective_date - 1) BETWEEN pa.effective_start_date AND pa.effective_end_date;

Line: 401

           update_this_record
             (
              p_record,
              l_original_value,
              'CORRECTION'
             );

Line: 409

           before calling hr_entry_valid.delete_element_entry check
           if there exists another element_entry row (Next change which we intend to delete)
           after the current row
           */
           IF entry_valid(p_record,'DELETE_NEXT_CHANGE') THEN
              hr_utility.set_location('Delete Next Change  ',20);

Line: 415

              hr_entry_api.delete_element_entry
                        (
                         p_dt_delete_mode   => 'DELETE_NEXT_CHANGE',
                         p_session_date     => p_record.p_effective_date - 1,
                         p_element_entry_id => p_record.element_entry_id
               );