[Home] [Help]
38: report is Register or Paper.
39: 01-SEP-2004 SSouresr 115.4 Made changes to the package to
40: support multi assignments
41: and also removed unnecessary
42: joins to per_assignments_f
43: 27-SEP-2004 SSouresr 115.5 Corrected action_creation for
44: scenario with assignment set but
45: no GRE
46: 22-NOV-2004 SSouresr 115.6 Replaced tables with views and also
203: if ln_person_id is not null then
204:
205: lv_sql_string :=
206: 'select distinct asg.person_id person_id
207: from per_assignments_f asg
208: where person_id = ' || ln_person_id ||
209: ' and :payroll_action_id > 0';
210:
211: hr_utility.trace('Range for person_id not null');
214:
215: lv_sql_string :=
216: 'select distinct paf.person_id
217: from hr_assignment_set_amendments asgset,
218: per_assignments_f paf
219: where assignment_set_id = ' || ln_asg_set || '
220: and asgset.assignment_id = paf.assignment_id
221: and asgset.include_or_exclude = ''I''
222: and :payroll_action_id > 0
229: lv_sql_string :=
230: 'select distinct paf.person_id
231: from pay_payroll_actions ppa_arch,
232: pay_assignment_actions paa_arch,
233: per_assignments_f paf,
234: pay_payroll_actions ppa
235: where paa_arch.assignment_id = paf.assignment_id
236: and ppa.payroll_action_id = :payroll_action_id
237: and ppa_arch.business_group_id = ppa.business_group_id
247: lv_sql_string :=
248: 'select distinct paf.person_id
249: from pay_payroll_actions ppa_arch,
250: pay_assignment_actions paa_arch,
251: per_assignments_f paf,
252: pay_payroll_actions ppa
253: where paa_arch.assignment_id = paf.assignment_id
254: and ppa.payroll_action_id = :payroll_action_id
255: and ppa_arch.business_group_id = ppa.business_group_id
329: ,cp_asg_set in number
330: ,cp_effective_date in date) is
331: select distinct paf.person_id
332: from hr_assignment_set_amendments asgset,
333: per_assignments_f paf,
334: pay_payroll_actions ppa,
335: pay_assignment_actions paa
336: where asgset.assignment_set_id = cp_asg_set
337: and asgset.include_or_exclude = 'I'
413: and not exists (select 1
414: from pay_action_interlocks
415: where locked_action_id = paa.assignment_action_id)
416: and exists (select 1
417: from per_assignments_f paf
418: where paf.assignment_id = paa.assignment_id
419: and paf.effective_start_date <= cp_effective_date
420: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
421: order by paa.assignment_action_id desc;
436: and ppa.payroll_action_id = paa.payroll_action_id
437: and ppa.effective_date = cp_effective_date
438: and ppa.report_type = 'CAEOY_T4_AMEND_PP'
439: and exists (select 1
440: from per_assignments_f paf
441: where paf.assignment_id = paa.assignment_id
442: and paf.effective_start_date <= cp_effective_date
443: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
444: order by paa.assignment_action_id desc;
871:
872: l_dt date;
873: l_year number ;
874: l_gre_id pay_assignment_actions.tax_unit_id%type;
875: l_per_id per_assignments_f.person_id%type;
876: l_sort1 varchar2(60);
877: l_sort2 varchar2(60);
878: l_sort3 varchar2(60);
879: l_year_start date;