[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
135: where act.payroll_action_id = c_eoy_payroll_action_id
136: and act.tax_unit_id = c_tax_unit_id
137: and to_number(act.serial_number) between stperson and endperson
138: and exists ( select 1
139: from per_assignments_f paf,
140: hr_assignment_set_amendments hasa
141: where hasa.assignment_set_id = c_assign_set
142: and hasa.assignment_id = paf.assignment_id
143: and upper(hasa.include_or_exclude) = 'I'
145: and paf.person_id = to_number(act.serial_number)
146: );
147:
148: /* when assignment_set is not selected */
149: -- #3871087 Included join with per_assignments_f
150: CURSOR c_actions_without_asg_set
151: (
152: pactid number,
153: stperson number,
163: act.tax_unit_id,
164: to_number(act.serial_number)
165: -- need to select person id to check for assignment set
166: from pay_assignment_actions act,
167: per_assignments_f paf
168: where act.payroll_action_id = c_eoy_payroll_action_id
169: and act.tax_unit_id = c_tax_unit_id
170: and paf.assignment_id = act.assignment_id
171: and paf.effective_start_date =
169: and act.tax_unit_id = c_tax_unit_id
170: and paf.assignment_id = act.assignment_id
171: and paf.effective_start_date =
172: (select max(paf2.effective_start_date)
173: from per_assignments_f paf2
174: where paf2.assignment_id = paf.assignment_id
175: and paf2.effective_start_date <=
176: c_effective_date )
177: AND paf.effective_end_date >= c_start_date
322: act.tax_unit_id,
323: to_number(act.serial_number)
324: -- need to select person id to check for assignment set
325: from pay_assignment_actions act,
326: per_assignments_f paf
327: where act.payroll_action_id = ' || l_eoy_payroll_action_id || '
328: and act.tax_unit_id = ' || l_tax_unit_id || '
329: and paf.assignment_id = act.assignment_id
330: and paf.effective_start_date =
328: and act.tax_unit_id = ' || l_tax_unit_id || '
329: and paf.assignment_id = act.assignment_id
330: and paf.effective_start_date =
331: (select max(paf2.effective_start_date)
332: from per_assignments_f paf2
333: where paf2.assignment_id = paf.assignment_id
334: and paf2.effective_start_date <= ''' ||
335: l_effective_date || ''' )
336: AND paf.effective_end_date >= ''' || l_start_date || '''
553:
554: l_dt date;
555: l_year number ;
556: l_gre_id pay_assignment_actions.tax_unit_id%type;
557: l_org_id per_assignments_f.organization_id%type;
558: l_loc_id per_assignments_f.location_id%type;
559: l_per_id per_assignments_f.person_id%type;
560: l_ssn per_people_f.national_identifier%type;
561: l_state_code pay_us_states.state_code%type;
554: l_dt date;
555: l_year number ;
556: l_gre_id pay_assignment_actions.tax_unit_id%type;
557: l_org_id per_assignments_f.organization_id%type;
558: l_loc_id per_assignments_f.location_id%type;
559: l_per_id per_assignments_f.person_id%type;
560: l_ssn per_people_f.national_identifier%type;
561: l_state_code pay_us_states.state_code%type;
562: l_sort1 varchar2(60);
555: l_year number ;
556: l_gre_id pay_assignment_actions.tax_unit_id%type;
557: l_org_id per_assignments_f.organization_id%type;
558: l_loc_id per_assignments_f.location_id%type;
559: l_per_id per_assignments_f.person_id%type;
560: l_ssn per_people_f.national_identifier%type;
561: l_state_code pay_us_states.state_code%type;
562: l_sort1 varchar2(60);
563: l_sort2 varchar2(60);