[Home] [Help]
49: query with high cost in 11.5.8.
50:
51: 16-AUG-2002 vpandya 115.11 Changed c_all_pres for perfoemance 11.5.8,
52: getting Quebec Busi.number from
53: pay_payroll_actions table instead of
54: archiver table.
55: Changed c_all_asg_in_asgset for 11.5.8 perf.
56: added table hr_all_organization_units table
57: to avoid cartesian join.
124: 10-FEB-2006 ssouresr 115.34 Removed references to hr_soft_coding_keyflex
125:
126: 24-FEB-2009 sneelapa 115.35 Bug 7572889, Modified the CURSORS
127: c_all_asg, c_single_asg and c_all_asg_in_asgset
128: to fetch the data from "pay_payroll_actions"
129: and "pay_assignment_actions" tables.
130: 05-Sep-2011 rgottipa 115.36 Bug 10399514, introduced new cursores
131: c_all_asg_range, c_single_asg_range and
132: c_all_asg_in_asgset_range. These will be
171: end;
172:
173: procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
174: l_payroll_id number;
175: leg_param pay_payroll_actions.legislative_parameters%type;
176:
177: begin
178: --hr_utility.trace_on('Y','RL1');
179: hr_utility.trace('begining of range_cursor 1 ');
178: --hr_utility.trace_on('Y','RL1');
179: hr_utility.trace('begining of range_cursor 1 ');
180: select legislative_parameters
181: into leg_param
182: from pay_payroll_actions ppa
183: where ppa.payroll_action_id = pactid;
184:
185:
186: /* pay reg code */
191: ff_database_items fdi1,
192: ff_database_items fdi2,
193: ff_archive_items fai3,
194: ff_database_items fdi3,
195: pay_payroll_actions ppa,
196: pay_assignment_actions paa
197: where ppa.payroll_action_id = :payroll_action_id
198: and fai1.user_entity_id = fdi1.user_entity_id
199: and fdi1.user_name = ''CAEOY_TAXATION_YEAR''
239: l_pre_organization_id varchar2(17);
240: l_rl1reg_pre_org_id varchar2(17);
241: l_effective_date date;
242: l_report_type varchar2(80);
243: l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
244: l_person_id number;
245: l_prev_person_id number;
246: l_prev_pre_organization_id varchar2(17);
247:
252: lv_per_id varchar2(30);
253:
254: -- Variables declared for bug 10399514
255: l_person_on boolean ;
256: l_report_cat pay_payroll_actions.report_category%type;
257: l_state pay_payroll_actions.report_qualifier%type;
258: l_report_format pay_report_format_mappings_f.report_format%type;
259: -- Variables declared for bug 10399514
260:
253:
254: -- Variables declared for bug 10399514
255: l_person_on boolean ;
256: l_report_cat pay_payroll_actions.report_category%type;
257: l_state pay_payroll_actions.report_qualifier%type;
258: l_report_format pay_report_format_mappings_f.report_format%type;
259: -- Variables declared for bug 10399514
260:
261: l_print_term varchar2(1);
261: l_print_term varchar2(1);
262: l_effective_end_date date;
263:
264: /* For performance: getting all pre organization ids from
265: legislative parameter of pay_payroll_actions for RL1 archiver
266: for the given year within same business group.
267: */
268: cursor c_all_pres is
269: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
267: */
268: cursor c_all_pres is
269: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
270: ppa.legislative_parameters )
271: from pay_payroll_actions ppa
272: where ppa.report_type = 'RL1'
273: and ppa.report_qualifier = 'CAEOYRL1'
274: and ppa.report_category = 'CAEOYRL1'
275: and ppa.effective_date = l_year_end
281: cp_assignment_id in number,
282: cp_pre_org_id in varchar2) is
283: select paa.assignment_action_id,
284: ppa.payroll_action_id
285: from pay_payroll_actions ppa,
286: pay_assignment_actions paa
287: where ppa.report_type = 'RL1'
288: and ppa.report_qualifier = 'CAEOYRL1'
289: and ppa.report_category = 'CAEOYRL1'
308: , l_year_start date
309: , l_year_end date ) is
310: select distinct to_number(paa.serial_number),
311: paa.assignment_id assignment_id
312: from pay_payroll_actions ppa,
313: pay_assignment_actions paa
314: where ppa.report_type = 'RL1'
315: and ppa.report_qualifier = 'CAEOYRL1'
316: and ppa.report_category = 'CAEOYRL1'
328: , l_year_start date
329: , l_year_end date ) is
330: select distinct to_number(paa.serial_number),
331: paa.assignment_id assignment_id
332: from pay_payroll_actions ppa,
333: pay_assignment_actions paa,
334: pay_population_ranges ppr
335: where ppa.report_type = 'RL1'
336: and ppa.report_qualifier = 'CAEOYRL1'
351: ,l_year_end date
352: ,l_per_id varchar2) is
353: select distinct to_number(paa.serial_number),
354: paa.assignment_id assignment_id
355: from pay_payroll_actions ppa,
356: pay_assignment_actions paa
357: where ppa.report_type = 'RL1'
358: and ppa.report_qualifier = 'CAEOYRL1'
359: and ppa.report_category = 'CAEOYRL1'
372: ,l_year_end date
373: ,l_per_id varchar2) is
374: select distinct to_number(paa.serial_number),
375: paa.assignment_id assignment_id
376: from pay_payroll_actions ppa,
377: pay_assignment_actions paa,
378: pay_population_ranges ppr
379: where ppa.report_type = 'RL1'
380: and ppa.report_qualifier = 'CAEOYRL1'
397: ,l_year_start date
398: ,l_year_end date ) is
399: select distinct to_number(paa.serial_number),
400: paa.assignment_id assignment_id
401: from pay_payroll_actions ppa,
402: pay_assignment_actions paa
403: where ppa.report_type = 'RL1'
404: and ppa.report_qualifier = 'CAEOYRL1'
405: and ppa.report_category = 'CAEOYRL1'
425: ,l_year_start date
426: ,l_year_end date ) is
427: select distinct to_number(paa.serial_number),
428: paa.assignment_id assignment_id
429: from pay_payroll_actions ppa,
430: pay_assignment_actions paa,
431: pay_population_ranges ppr
432: where ppa.report_type = 'RL1'
433: and ppa.report_qualifier = 'CAEOYRL1'
498: l_report_cat,
499: -- Added for bug 10399514
500: l_bus_group_id,
501: l_legislative_parameters
502: from pay_payroll_actions
503: where payroll_action_id = pactid;
504:
505: hr_utility.trace('begining of action creation 2 '||
506: to_char(l_bus_group_id));
945: hr_locations_all loc,
946: per_all_people_f ppf,
947: per_all_assignments_f paf,
948: pay_assignment_actions paa1,
949: pay_payroll_actions ppa1
950: where ppa1.payroll_action_id = :pactid
951: and paa1.payroll_action_id = ppa1.payroll_action_id
952: and paa1.assignment_id = paf.assignment_id
953: and paf.assignment_type = ''E''
994: parameter_list varchar2) return varchar2
995: is
996: start_ptr number;
997: end_ptr number;
998: token_val pay_payroll_actions.legislative_parameters%type;
999: par_value pay_payroll_actions.legislative_parameters%type;
1000: begin
1001:
1002: token_val := name||'=';
995: is
996: start_ptr number;
997: end_ptr number;
998: token_val pay_payroll_actions.legislative_parameters%type;
999: par_value pay_payroll_actions.legislative_parameters%type;
1000: begin
1001:
1002: token_val := name||'=';
1003: