[Home] [Help]
301: if ln_person_id is not null then
302:
303: lv_sql_string :=
304: 'select distinct asg.person_id person_id
305: from per_assignments_f asg
306: where person_id = ' || ln_person_id ||
307: ' and :payroll_action_id > 0';
308:
309: hr_utility.trace('Range for person_id not null');
312:
313: lv_sql_string :=
314: 'select distinct paf.person_id
315: from hr_assignment_set_amendments asgset,
316: per_assignments_f paf
317: where assignment_set_id = ' || ln_asg_set || '
318: and asgset.assignment_id = paf.assignment_id
319: and asgset.include_or_exclude = ''I''
320: and :payroll_action_id > 0
327: lv_sql_string :=
328: 'select distinct paf.person_id
329: from pay_payroll_actions ppa_arch,
330: pay_assignment_actions paa_arch,
331: per_assignments_f paf,
332: pay_payroll_actions ppa
333: where paa_arch.assignment_id = paf.assignment_id
334: and ppa.payroll_action_id = :payroll_action_id
335: and ppa_arch.business_group_id = ppa.business_group_id
346: lv_sql_string :=
347: 'select distinct paf.person_id
348: from pay_payroll_actions ppa_arch,
349: pay_assignment_actions paa_arch,
350: per_assignments_f paf,
351: pay_payroll_actions ppa
352: where paa_arch.assignment_id = paf.assignment_id
353: and ppa.payroll_action_id = :payroll_action_id
354: and ppa_arch.business_group_id = ppa.business_group_id
425: ,cp_asg_set in number
426: ,cp_effective_date in date) is
427: select distinct paf.person_id
428: from hr_assignment_set_amendments asgset,
429: per_assignments_f paf,
430: pay_payroll_actions ppa_arch,
431: pay_assignment_actions paa_arch
432: where asgset.assignment_set_id = cp_asg_set
433: and asgset.include_or_exclude = 'I'
452: ,cp_asg_set in number
453: ,cp_effective_date in date) is
454: select distinct paf.person_id
455: from hr_assignment_set_amendments asgset,
456: per_assignments_f paf,
457: pay_payroll_actions ppa_arch,
458: pay_assignment_actions paa_arch,
459: pay_population_ranges ppr
460: where asgset.assignment_set_id = cp_asg_set
561: ppa.legislative_parameters),
562: paf.effective_end_date
563: from pay_payroll_actions ppa,
564: pay_assignment_actions paa,
565: per_assignments_f paf
566: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
567: and paa.assignment_id = paf.assignment_id
568: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
569: ppa.legislative_parameters) =
574: and ppa.effective_date = cp_effective_date
575: and ppa.business_group_id = cp_business_group_id
576: and (ppa.report_type = 'CAEOY_RL2_AMEND_PP'or ppa.report_type = 'PYRL2PRAMEND') /*Added for Bug#5046010*/
577: and paf.effective_end_date = (select max(paf.effective_end_date)
578: from per_assignments_f paf
579: where paf.assignment_id = paa.assignment_id
580: and paf.effective_start_date <= cp_effective_date
581: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
582: AND not exists
620: and ppa.payroll_action_id = paa.payroll_action_id
621: and ppa.effective_date = cp_effective_date
622: and ppa.report_type = 'CAEOY_RL2_AMEND_PP'
623: and exists (select 1
624: from per_assignments_f paf
625: where paf.assignment_id = paa.assignment_id
626: and paf.effective_start_date <= cp_effective_date
627: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
628: group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
641: ppa.legislative_parameters),
642: paf.effective_end_date
643: from pay_payroll_actions ppa,
644: pay_assignment_actions paa,
645: per_assignments_f paf
646: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
647: and paa.assignment_id = paf.assignment_id
648: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
649: ppa.legislative_parameters) =
654: and ppa.payroll_action_id = paa.payroll_action_id
655: and ppa.effective_date = cp_effective_date
656: and ppa.report_type = 'CAEOY_RL2_AMEND_PP'
657: and paf.effective_end_date = (select max(paf.effective_end_date)
658: from per_assignments_f paf
659: where paf.assignment_id = paa.assignment_id
660: and paf.effective_start_date <= cp_effective_date
661: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
662: and exists
704: ppa.legislative_parameters),
705: paf.effective_end_date
706: from pay_payroll_actions ppa,
707: pay_assignment_actions paa,
708: per_assignments_f paf,
709: per_periods_of_service pds
710: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
711: and paa.assignment_id = paf.assignment_id
712: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
718: and ppa.effective_date = cp_effective_date
719: and ppa.business_group_id = cp_business_group_id
720: and (ppa.report_type = 'CAEOY_RL2_AMEND_PP'or ppa.report_type = 'PYRL2PRAMEND') /*Added for Bug#5046010*/
721: and paf.effective_end_date = (select max(paf.effective_end_date)
722: from per_assignments_f paf
723: where paf.assignment_id = paa.assignment_id
724: and paf.effective_start_date <= cp_effective_date
725: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
726: AND not exists
756: ppa.legislative_parameters),
757: paf.effective_end_date
758: from pay_payroll_actions ppa,
759: pay_assignment_actions paa,
760: per_assignments_f paf,
761: per_periods_of_service pds
762: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
763: and paa.assignment_id = paf.assignment_id
764: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
770: and ppa.payroll_action_id = paa.payroll_action_id
771: and ppa.effective_date = cp_effective_date
772: and ppa.report_type = 'CAEOY_RL2_AMEND_PP'
773: and paf.effective_end_date = (select max(paf.effective_end_date)
774: from per_assignments_f paf
775: where paf.assignment_id = paa.assignment_id
776: and paf.effective_start_date <= cp_effective_date
777: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
778: and exists