APPS.HR_CA_PERSON_TERM_LEG_HOOK SQL Statements

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

Line: 13

                  hr_periods_of_service_bk1.update_pds_details_b for CA
                  legislation. It is used to correctly End Date the Tax
                  Records (Federal / Provincial) as per the
                  Final Process Date entered by the user.

    Change List:
    ------------
     Name          Date        Version    Bug     Text
    ------------- ----------- --------- ------- ------------------------------
    sneelapa      24-APR-2008    115.0           Initial version.

*/

procedure update_tax_rules(p_period_of_service_id in number,
                           p_final_process_date in date) is

/* Fetching Old Final Process Date and Person ID From Period of Service */

cursor c_get_person_oldfpd(p_period_of_service_id in number) is
select distinct pds.person_id,
                pds.final_process_date
from per_periods_of_service pds
where pds.period_of_service_id = p_period_of_service_id ;

Line: 40

select distinct assignment_id
from    per_assignments_f paf_o,
        per_assignment_status_types past
where  paf_o.person_id = p_person_id
and    paf_o.assignment_status_type_id = past.assignment_status_type_id
and    past.per_system_status = 'TERM_ASSIGN'
and  ((past.business_group_id is null
                and past.legislation_code is null)
                OR (past.business_group_id is null
                    and past.legislation_code = 'CA')
                OR (past.legislation_code is null
                    and exists
                        (select 'x'
                         from  per_assignments_f paf_a
                         where paf_a.assignment_id = paf_o.assignment_id
                         and   paf_a.business_group_id = past.business_group_id)
                    )
               )
and    paf_o.effective_end_date = (select max(paf_i.effective_end_date)
                                                 from per_assignments_f paf_i
                                                 where paf_i.assignment_id = paf_o.assignment_id) ;

Line: 67

select pueo.emp_fed_tax_inf_id,
       pueo.effective_start_date,
       pueo.effective_end_date
from pay_ca_emp_fed_tax_info_f pueo
where pueo.assignment_id = p_assignment_id
and   pueo.effective_start_date = (select max(puei.effective_start_date)
                              from pay_ca_emp_fed_tax_info_f puei
                              where puei.assignment_id = pueo.assignment_id
                              and   puei.effective_start_date <= p_final_process_date) ;

Line: 83

select pueo.emp_province_tax_inf_id,
       pueo.effective_start_date,
       pueo.effective_end_date
from pay_ca_emp_prov_tax_info_f pueo
where pueo.assignment_id = p_assignment_id
and   pueo.effective_start_date = (select max(puei.effective_start_date)
                              from pay_ca_emp_prov_tax_info_f puei
                              where puei.assignment_id = pueo.assignment_id
                              and   puei.province_code = pueo.province_code
                              and   puei.effective_start_date <= p_final_process_date) ;

Line: 98

SELECT NULL
FROM   pay_payroll_actions ppa
      ,pay_assignment_actions paa
      ,per_assignments_f asg
      ,per_periods_of_service pds
WHERE pds.period_of_service_id = p_period_of_service_id
AND   pds.person_id = asg.person_id
AND   asg.period_of_service_id = pds.period_of_service_id
AND   asg.assignment_id = paa.assignment_id
AND   paa.payroll_action_id = ppa.payroll_action_id
AND   ppa.action_type NOT IN ('X','BEE')
AND   ppa.effective_date > p_new_final_process_date ;

Line: 124

hr_utility.trace('Entering HR_CA_PERSON_TERM_LEG_HOOK.update_tax_rules');

Line: 169

            update pay_ca_emp_fed_tax_info_f
            set effective_end_date = l_final_process_date
            where emp_fed_tax_inf_id = l_emp_fed_tax_inf_id
            and  effective_start_date = l_eff_st_dt
            and  effective_end_date = l_eff_end_dt ;

Line: 185

          update pay_ca_emp_prov_tax_info_f
          set effective_end_date = l_final_process_date
          where emp_province_tax_inf_id = l_emp_prov_tax_inf_id
          and  effective_start_date = l_eff_st_dt
          and  effective_end_date = l_eff_end_dt ;

Line: 204

hr_utility.trace('Leaving HR_CA_PERSON_TERM_LEG_HOOK.update_tax_rules');

Line: 205

end update_tax_rules ;