[Home] [Help]
420: -- Declare cursors and local variables
421: --
422: l_perdetails_set PerDetails;
423: l_ptupetspt_set Var2Datatype;
424: l_asg_dets per_all_assignments_f%ROWTYPE;
425: l_plstab_count binary_integer;
426: --
427: l_v2dummy varchar2(1);
428: --
600: Is
601: select asg.assignment_id,
602: asg.object_version_number,
603: asg.effective_start_date
604: from per_all_assignments_f asg
605: where asg.person_id = c_person_id
606: and asg.assignment_type = 'B';
607: --
608: cursor c_getbenasgdets
610: ,c_eff_date in date
611: )
612: Is
613: select null
614: from per_all_assignments_f asg
615: where asg.person_id = c_person_id
616: and asg.assignment_type = 'B'
617: and c_eff_date
618: between asg.effective_start_date and asg.effective_end_date;
640: ,c_eff_date in date
641: )
642: Is
643: select *
644: from per_all_assignments_f asg
645: where asg.person_id = c_person_id
646: and asg.assignment_type = 'E'
647: and asg.primary_flag = 'Y'
648: and c_eff_date
653: ,c_eff_date in date
654: )
655: Is
656: select *
657: from per_all_assignments_f asg
658: where asg.person_id = c_person_id
659: and asg.primary_flag = 'Y'
660: and c_eff_date
661: between asg.effective_start_date and asg.effective_end_date;
667: )
668: Is
669: select asg.object_version_number,
670: asg.effective_start_date
671: from per_all_assignments_f asg
672: where asg.assignment_id = c_assignment_id
673: and c_eff_date
674: between asg.effective_start_date and asg.effective_end_date;
675: --
678: ,c_ovn in number
679: )
680: Is
681: select null
682: from per_all_assignments_f asg
683: where asg.assignment_id = c_assignment_id
684: and asg.object_version_number = c_ovn;
685: --
686: cursor c_getaeidets
1971: --
1972: procedure check_bnft_asgn
1973: (p_person_id in number
1974: ,p_effective_date in date
1975: ,p_asg_dets in per_all_assignments_f%rowtype
1976: ,p_exists out nocopy boolean
1977: --RCHASE BENASG bug fix Start
1978: ,p_emp_person_id in number default null
1979: --RCHASE End
1986: --
1987: cursor c_getbenasgdets
1988: Is
1989: select asg.*
1990: from per_all_assignments_f asg
1991: where asg.person_id = p_person_id
1992: and asg.assignment_type = 'B'
1993: and p_effective_date
1994: between asg.effective_start_date and asg.effective_end_date;
1993: and p_effective_date
1994: between asg.effective_start_date and asg.effective_end_date;
1995:
1996: l_asg_rec c_getbenasgdets%rowtype;
1997: l_object_version_number per_all_assignments_f.object_version_number%type;
1998: l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1999: l_age varchar2(100);
2000: l_adj_serv_date date;
2001: l_orig_hire_date date;
1994: between asg.effective_start_date and asg.effective_end_date;
1995:
1996: l_asg_rec c_getbenasgdets%rowtype;
1997: l_object_version_number per_all_assignments_f.object_version_number%type;
1998: l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1999: l_age varchar2(100);
2000: l_adj_serv_date date;
2001: l_orig_hire_date date;
2002: l_salary varchar2(100);
2096: -- Check for valid date track mode.
2097: --
2098: dt_api.find_dt_upd_modes
2099: (p_effective_date => p_effective_date,
2100: p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
2101: p_base_key_column => 'assignment_id',
2102: p_base_key_value => l_asg_rec.assignment_id,
2103: p_correction => l_correction,
2104: p_update => l_update,
2317: ,c_eff_date in date
2318: )
2319: Is
2320: select pyp.proposed_salary_n
2321: from per_all_assignments_f asg,
2322: per_pay_proposals pyp
2323: where asg.assignment_id = pyp.assignment_id
2324: and c_eff_date
2325: between asg.effective_start_date and asg.effective_end_date