[Home] [Help]
317: if ln_person_id is not null then
318:
319: lv_sql_string :=
320: 'select distinct asg.person_id person_id
321: from per_assignments_f asg
322: where person_id = ' || ln_person_id ||
323: ' and :payroll_action_id > 0';
324:
325: hr_utility.trace('Range for person_id not null');
328:
329: lv_sql_string :=
330: 'select distinct paf.person_id
331: from hr_assignment_set_amendments asgset,
332: per_assignments_f paf
333: where assignment_set_id = ' || ln_asg_set || '
334: and asgset.assignment_id = paf.assignment_id
335: and asgset.include_or_exclude = ''I''
336: and :payroll_action_id > 0
343: lv_sql_string :=
344: 'select distinct paf.person_id
345: from pay_payroll_actions ppa_arch,
346: pay_assignment_actions paa_arch,
347: per_assignments_f paf,
348: pay_payroll_actions ppa
349: where paa_arch.assignment_id = paf.assignment_id
350: and ppa.payroll_action_id = :payroll_action_id
351: and ppa_arch.business_group_id = ppa.business_group_id
362: lv_sql_string :=
363: 'select distinct paf.person_id
364: from pay_payroll_actions ppa_arch,
365: pay_assignment_actions paa_arch,
366: per_assignments_f paf,
367: pay_payroll_actions ppa
368: where paa_arch.assignment_id = paf.assignment_id
369: and ppa.payroll_action_id = :payroll_action_id
370: and ppa_arch.business_group_id = ppa.business_group_id
442: ,cp_asg_set in number
443: ,cp_effective_date in date) is
444: select distinct paf.person_id
445: from hr_assignment_set_amendments asgset,
446: per_assignments_f paf,
447: pay_payroll_actions ppa_arch,
448: pay_assignment_actions paa_arch
449: where asgset.assignment_set_id = cp_asg_set
450: and asgset.include_or_exclude = 'I'
469: ,cp_asg_set in number
470: ,cp_effective_date in date) is
471: select distinct paf.person_id
472: from hr_assignment_set_amendments asgset,
473: per_assignments_f paf,
474: pay_payroll_actions ppa_arch,
475: pay_assignment_actions paa_arch,
476: pay_population_ranges ppr
477: where asgset.assignment_set_id = cp_asg_set
581: ppa.legislative_parameters),
582: paf.effective_end_date
583: from pay_payroll_actions ppa,
584: pay_assignment_actions paa,
585: per_assignments_f paf
586: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
587: and paa.assignment_id = paf.assignment_id
588: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
589: ppa.legislative_parameters) =
594: and ppa.effective_date = cp_effective_date
595: and ppa.business_group_id = cp_business_group_id
596: and (ppa.report_type = 'CAEOY_RL1_AMEND_PP'or ppa.report_type = 'PYRL1PRAMEND') /*Added for Bug#5046006*/
597: and paf.effective_end_date = (select max(paf.effective_end_date)
598: from per_assignments_f paf
599: where paf.assignment_id = paa.assignment_id
600: and paf.effective_start_date <= cp_effective_date
601: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
602: AND not exists
640: and ppa.payroll_action_id = paa.payroll_action_id
641: and ppa.effective_date = cp_effective_date
642: and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
643: and exists (select 1
644: from per_assignments_f paf
645: where paf.assignment_id = paa.assignment_id
646: and paf.effective_start_date <= cp_effective_date
647: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
648: group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,
661: ppa.legislative_parameters),
662: paf.effective_end_date
663: from pay_payroll_actions ppa,
664: pay_assignment_actions paa,
665: per_assignments_f paf
666: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
667: and paa.assignment_id = paf.assignment_id
668: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
669: ppa.legislative_parameters) =
674: and ppa.payroll_action_id = paa.payroll_action_id
675: and ppa.effective_date = cp_effective_date
676: and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
677: and paf.effective_end_date = (select max(paf.effective_end_date)
678: from per_assignments_f paf
679: where paf.assignment_id = paa.assignment_id
680: and paf.effective_start_date <= cp_effective_date
681: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
682: and exists
723: ppa.legislative_parameters),
724: paf.effective_end_date
725: from pay_payroll_actions ppa,
726: pay_assignment_actions paa,
727: per_assignments_f paf,
728: per_periods_of_service pds
729: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
730: and paa.assignment_id = paf.assignment_id
731: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
737: and ppa.effective_date = cp_effective_date
738: and ppa.business_group_id = cp_business_group_id
739: and (ppa.report_type = 'CAEOY_RL1_AMEND_PP'or ppa.report_type = 'PYRL1PRAMEND') /*Added for Bug#5046006*/
740: and paf.effective_end_date = (select max(paf.effective_end_date)
741: from per_assignments_f paf
742: where paf.assignment_id = paa.assignment_id
743: and paf.effective_start_date <= cp_effective_date
744: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
745: AND not exists
776: ppa.legislative_parameters),
777: paf.effective_end_date
778: from pay_payroll_actions ppa,
779: pay_assignment_actions paa,
780: per_assignments_f paf,
781: per_periods_of_service pds
782: where (paa.serial_number = to_char(cp_person_id) or paf.person_id = cp_person_id)
783: and paa.assignment_id = paf.assignment_id
784: and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
790: and ppa.payroll_action_id = paa.payroll_action_id
791: and ppa.effective_date = cp_effective_date
792: and ppa.report_type = 'CAEOY_RL1_AMEND_PP'
793: and paf.effective_end_date = (select max(paf.effective_end_date)
794: from per_assignments_f paf
795: where paf.assignment_id = paa.assignment_id
796: and paf.effective_start_date <= cp_effective_date
797: and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
798: and exists