[Home] [Help]
214: -- Declare cursors and local variables
215: --
216: l_perdetails_set PerDetails;
217: l_ptupetspt_set Var2Datatype;
218: l_asg_dets per_all_assignments_f%ROWTYPE;
219: l_plstab_count binary_integer;
220: --
221: l_v2dummy varchar2(1);
222: --
394: Is
395: select asg.assignment_id,
396: asg.object_version_number,
397: asg.effective_start_date
398: from per_all_assignments_f asg
399: where asg.person_id = c_person_id
400: and asg.assignment_type = 'B';
401: --
402: cursor c_getbenasgdets
404: ,c_eff_date in date
405: )
406: Is
407: select null
408: from per_all_assignments_f asg
409: where asg.person_id = c_person_id
410: and asg.assignment_type = 'B'
411: and c_eff_date
412: between asg.effective_start_date and asg.effective_end_date;
434: ,c_eff_date in date
435: )
436: Is
437: select *
438: from per_all_assignments_f asg
439: where asg.person_id = c_person_id
440: and asg.assignment_type = 'E'
441: and asg.primary_flag = 'Y'
442: and c_eff_date
447: ,c_eff_date in date
448: )
449: Is
450: select *
451: from per_all_assignments_f asg
452: where asg.person_id = c_person_id
453: and asg.primary_flag = 'Y'
454: and c_eff_date
455: between asg.effective_start_date and asg.effective_end_date;
461: )
462: Is
463: select asg.object_version_number,
464: asg.effective_start_date
465: from per_all_assignments_f asg
466: where asg.assignment_id = c_assignment_id
467: and c_eff_date
468: between asg.effective_start_date and asg.effective_end_date;
469: --
472: ,c_ovn in number
473: )
474: Is
475: select null
476: from per_all_assignments_f asg
477: where asg.assignment_id = c_assignment_id
478: and asg.object_version_number = c_ovn;
479: --
480: cursor c_getaeidets
1911: --
1912: procedure check_bnft_asgn
1913: (p_person_id in number
1914: ,p_effective_date in date
1915: ,p_asg_dets in per_all_assignments_f%rowtype
1916: ,p_exists out nocopy boolean
1917: --RCHASE BENASG bug fix Start
1918: ,p_emp_person_id in number default null
1919: --RCHASE End
1926: --
1927: cursor c_getbenasgdets
1928: Is
1929: select asg.*
1930: from per_all_assignments_f asg
1931: where asg.person_id = p_person_id
1932: and asg.assignment_type = 'B'
1933: and p_effective_date
1934: between asg.effective_start_date and asg.effective_end_date;
1933: and p_effective_date
1934: between asg.effective_start_date and asg.effective_end_date;
1935:
1936: l_asg_rec c_getbenasgdets%rowtype;
1937: l_object_version_number per_all_assignments_f.object_version_number%type;
1938: l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1939: l_age varchar2(100);
1940: l_adj_serv_date date;
1941: l_orig_hire_date date;
1934: between asg.effective_start_date and asg.effective_end_date;
1935:
1936: l_asg_rec c_getbenasgdets%rowtype;
1937: l_object_version_number per_all_assignments_f.object_version_number%type;
1938: l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1939: l_age varchar2(100);
1940: l_adj_serv_date date;
1941: l_orig_hire_date date;
1942: l_salary varchar2(100);
1984: -- Check for valid date track mode.
1985: --
1986: dt_api.find_dt_upd_modes
1987: (p_effective_date => p_effective_date,
1988: p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
1989: p_base_key_column => 'assignment_id',
1990: p_base_key_value => l_asg_rec.assignment_id,
1991: p_correction => l_correction,
1992: p_update => l_update,
2205: ,c_eff_date in date
2206: )
2207: Is
2208: select pyp.proposed_salary_n
2209: from per_all_assignments_f asg,
2210: per_pay_proposals pyp
2211: where asg.assignment_id = pyp.assignment_id
2212: and c_eff_date
2213: between asg.effective_start_date and asg.effective_end_date