[Home] [Help]
18: in range_cursor to improve performance and
19: modified the query of action_creation.
20: 26-JUL-2001 ndorai 115.3 Modified the action_creation cursor and
21: range_cursor.
22: 30-JUL-2001 ndorai 115.4 Replaced per_assignments_f with
23: per_all_assignments_f in sort_action_cursor.
24: 26-SEP-2001 ndorai 115.5 Fixed the sort_action string so that the
25: variable value will be substituted properly.
26: 24-DEC-2001 meshah 115.8 Changed hr_locations to hr_locations_all.
40: As the action is already created the sort_cursor
41: should go to the base table(Bug 3131302).
42: 09-SEP-2004 rsethupa 115.18 Modified cursors in the action_creation
43: procedure to fetch only from
44: secure view per_assignments_f.
45: 14-MAR-2005 sackumar 115.19 Bug 4222032
46: Change in the Range Cursor removing redundant
47: use of bind Variable (:pactid)
48: 25-MAY-2005 ahanda 115.20 Bug 4378773
164: where act.payroll_action_id = c_eoy_payroll_action_id
165: and act.tax_unit_id = c_tax_unit_id
166: and to_number(act.serial_number) between stperson and endperson
167: and exists ( select 1
168: from per_assignments_f paf,
169: hr_assignment_set_amendments hasa
170: where hasa.assignment_set_id = c_assign_set
171: and hasa.assignment_id = paf.assignment_id
172: and upper(hasa.include_or_exclude) = 'I'
174: and paf.person_id = to_number(act.serial_number)
175: );
176:
177: /* when assignment_set is not selected */
178: -- #3871087 Included join with per_assignments_f
179: CURSOR c_actions_without_asg_set
180: (
181: pactid number,
182: stperson number,
192: act.tax_unit_id,
193: to_number(act.serial_number)
194: -- need to select person id to check for assignment set
195: from pay_assignment_actions act,
196: per_assignments_f paf
197: where act.payroll_action_id = c_eoy_payroll_action_id
198: and act.tax_unit_id = c_tax_unit_id
199: and paf.assignment_id = act.assignment_id
200: and paf.effective_start_date =
198: and act.tax_unit_id = c_tax_unit_id
199: and paf.assignment_id = act.assignment_id
200: and paf.effective_start_date =
201: (select max(paf2.effective_start_date)
202: from per_assignments_f paf2
203: where paf2.assignment_id = paf.assignment_id
204: and paf2.effective_start_date <=
205: c_effective_date )
206: AND paf.effective_end_date >= c_start_date
355: act.tax_unit_id,
356: to_number(act.serial_number)
357: -- need to select person id to check for assignment set
358: from pay_assignment_actions act,
359: per_assignments_f paf
360: where act.payroll_action_id = ' || l_eoy_payroll_action_id || '
361: and act.tax_unit_id = ' || l_tax_unit_id || '
362: and paf.assignment_id = act.assignment_id
363: and paf.effective_start_date =
361: and act.tax_unit_id = ' || l_tax_unit_id || '
362: and paf.assignment_id = act.assignment_id
363: and paf.effective_start_date =
364: (select max(paf2.effective_start_date)
365: from per_assignments_f paf2
366: where paf2.assignment_id = paf.assignment_id
367: and paf2.effective_start_date <= ''' ||
368: l_effective_date || ''' )
369: AND paf.effective_end_date >= ''' || l_start_date || '''
591:
592: l_dt date;
593: l_year number ;
594: l_gre_id pay_assignment_actions.tax_unit_id%type;
595: l_org_id per_assignments_f.organization_id%type;
596: l_loc_id per_assignments_f.location_id%type;
597: l_per_id per_assignments_f.person_id%type;
598: l_ssn per_people_f.national_identifier%type;
599: l_state_code pay_us_states.state_code%type;
592: l_dt date;
593: l_year number ;
594: l_gre_id pay_assignment_actions.tax_unit_id%type;
595: l_org_id per_assignments_f.organization_id%type;
596: l_loc_id per_assignments_f.location_id%type;
597: l_per_id per_assignments_f.person_id%type;
598: l_ssn per_people_f.national_identifier%type;
599: l_state_code pay_us_states.state_code%type;
600: l_sort1 varchar2(60);
593: l_year number ;
594: l_gre_id pay_assignment_actions.tax_unit_id%type;
595: l_org_id per_assignments_f.organization_id%type;
596: l_loc_id per_assignments_f.location_id%type;
597: l_per_id per_assignments_f.person_id%type;
598: l_ssn per_people_f.national_identifier%type;
599: l_state_code pay_us_states.state_code%type;
600: l_sort1 varchar2(60);
601: l_sort2 varchar2(60);