APPS.PAY_IE_MEDICAL_ADJUST SQL Statements

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

Line: 19

  SELECT element_type_id
  FROM   pay_element_types_f
  WHERE  element_name = 'Setup BIK Medical Insurance'
--  AND    nvl(business_group_id, p_bg_id) = p_bg_id
  AND    legislation_code = 'IE'
  AND    p_eff_date BETWEEN effective_start_date AND effective_end_date;

Line: 29

  SELECT input_value_id
  FROM   pay_input_values_f
  WHERE  element_type_id = p_element_type_id
  AND    name = p_name
  --AND    nvl(business_group_id, p_bg_id) = p_bg_id
  AND    legislation_code = 'IE'
  AND    p_eff_date BETWEEN effective_start_date AND effective_end_date;

Line: 40

   SELECT links.element_link_id
      FROM   pay_element_links_f links, per_all_assignments_f assign
      WHERE  links.element_type_id = p_element_type_id
      AND    links.business_group_id=p_bg_id
      AND    assign.assignment_id=p_asg_id
      AND   ((    links.payroll_id is not null
              and links.payroll_id = assign.payroll_id)
      OR     (    links.link_to_all_payrolls_flag='Y'
              and assign.payroll_id is not null)
      OR     (    links.payroll_id is null
              and links.link_to_all_payrolls_flag='N')
      OR     links.job_id=assign.job_id
      OR     links.position_id=assign.position_id
      OR     links.people_group_id=assign.people_group_id
      OR     links.organization_id=assign.organization_id
      OR     links.grade_id=assign.grade_id
      OR     links.location_id=assign.location_id
      OR     links.pay_basis_id=assign.pay_basis_id
      OR     links.employment_category=assign.employment_category)
      AND    p_eff_date BETWEEN links.effective_start_date
                              AND     links.effective_end_date;

Line: 123

	update pay_element_entries_f pee
	set    pee.creator_type          = 'B',
	       pee.target_entry_id          = NULL
	where  pee.element_entry_id      = l_element_entry_id
	and    p_eff_date between pee.effective_start_date and pee.effective_end_date;

Line: 188

select distinct element_type_id
from pay_element_types_f
where element_name = 'IE BIK Other Reportable Item Details'
and legislation_code = 'IE';

Line: 196

select distinct input_value_id
from pay_input_values_f
where name = p_input_val_name
and legislation_code = 'IE'
and element_type_id = p_ele_type_id;

Line: 208

select paa.assignment_id, sum(fnd_number.canonical_to_number(prrv1.result_value)) result_value  --paa.assignment_action_id, paa.payroll_action_id
from pay_assignment_actions paa,
     pay_payroll_actions ppa,
     pay_run_results prr,
     pay_run_result_values prrv,
     pay_run_result_values prrv1
where paa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = nvl(p_assig_id, paa.assignment_id)
and paa.assignment_action_id = prr.assignment_action_id
and prr.element_type_id = p_ele_type_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = p_input_val_id_med
and prrv.result_value = p_report_item_type
and prr.run_result_id = prrv1.run_result_id
and prrv1.input_value_id = p_input_val_id_tax
and ppa.effective_date between  to_date('01/01/2007','DD/MM/RRRR') and l_effective_date
and ppa.action_type in ('R','Q','B','V')
group by paa.assignment_id
having sum(fnd_number.canonical_to_number(prrv1.result_value)) <> 0
order by paa.assignment_id;

Line: 234

select result_value from pay_run_result_values where
input_value_id = p_input_val_id
and run_result_id in (select run_result_id from pay_run_results
                        where assignment_action_id = p_assg_act_id
                        and element_type_id = p_ele_type_id) ;

Line: 242

   select distinct ppf.full_name  ,
          paaf.assignment_number
   from per_all_people_f ppf,
   per_all_assignments_f paaf
   where paaf.assignment_id = p_assg_id
   and paaf.person_id = ppf.person_id
   and l_effective_date between ppf.effective_start_date and ppf.effective_end_date;

Line: 252

SELECT 1
FROM   pay_patch_status
WHERE  patch_number     = 6506755
AND    patch_name       = 'IE_MED_BAL_ADJ'
AND    phase            = 'C'
AND    legislation_code = 'IE';

Line: 274

	l_Med_pl_table.delete;

Line: 450

		INSERT INTO pay_patch_status(id
		,patch_number
		,patch_name
		,phase
		,applied_date
		,legislation_code)
		SELECT pay_patch_status_s.nextval
		,6506755
		,'IE_MED_BAL_ADJ'
		,'C'
		,sysdate
		,'IE'
		FROM  dual;