[Home] [Help]
13: p_information_type in varchar2,
14: p_effective_date in date
15: ) return number is
16: l_proc varchar2(72) ;
17: l_history_id ghr_pa_history.pa_history_id%type;
18:
19:
20: -- This cursor gets the latest date on which the given Extra information Type record
21: -- is inserted or updated on or before the given date
24: -- In c_history_id join on PER_PEOPLE_EXTRA_INFO is required to skip the history records
25: -- on the extra information records that are deleted.
26: cursor c_history_id is
27: select max(gph1.pa_history_id) pa_history_id
28: from ghr_pa_history gph1,
29: PER_PEOPLE_EXTRA_INFO pei1
30: where gph1.table_name = 'PER_PEOPLE_EXTRA_INFO'
31: and pei1.person_id = p_person_id
32: and pei1.information_type = p_information_type
31: and pei1.person_id = p_person_id
32: and pei1.information_type = p_information_type
33: and gph1.information1 = to_char(pei1.person_extra_info_id)
34: and gph1.effective_date = ( select max(gph2.effective_date)maxdate
35: from ghr_pa_history gph2
36: where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO'
37: and gph2.effective_date <= p_effective_date
38: and gph2.information1 = to_char(pei1.person_extra_info_id)) ;
39:
517: p_information_type in varchar2,
518: p_effective_date in date
519: ) return number is
520: l_proc varchar2(72) ;
521: l_history_id ghr_pa_history.pa_history_id%type;
522:
523: begin
524: l_proc := g_package||'get_history_id';
525: if (p_assignment_type = 'E' or p_assignment_type = 'C') then
534: p_information_type in varchar2,
535: p_effective_date in date) return number is
536:
537: l_proc varchar2(72) ;
538: l_history_id ghr_pa_history.pa_history_id%type;
539:
540:
541: -- This cursor gets the latest date on which the given Extra information Type record
542: -- is inserted or updated on or before the given date
545: -- In c_history_id join on PER_ASSIGNMENT_EXTRA_INFO is required to skip the history records
546: -- on the extra information records that are deleted.
547: cursor c_history_id is
548: select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
549: from ghr_pa_history gph1,
550: PER_ASSIGNMENT_EXTRA_INFO pei1
551: where gph1.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
552: and pei1.assignment_id = p_asg_id
553: and pei1.information_type = p_information_type
552: and pei1.assignment_id = p_asg_id
553: and pei1.information_type = p_information_type
554: and gph1.information1 = to_char(pei1.assignment_extra_info_id)
555: and gph1.effective_date = ( select max(gph2.effective_date)maxdate
556: from ghr_pa_history gph2
557: where gph2.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
558: and gph2.effective_date <= p_effective_date
559: and gph2.information1 = to_char(pei1.assignment_extra_info_id)) ;
560:
588: p_effective_date in date
589: ) return number is
590:
591: l_proc varchar2(72) ;
592: l_history_id ghr_pa_history.pa_history_id%type;
593:
594:
595: -- This cursor gets the latest date on which the given Extra information Type record
596: -- is inserted or updated on or before the given date
599: -- In c_history_id join on PER_POSITION_EXTRA_INFO is required to skip the history records
600: -- on the extra information records that are deleted.
601: cursor c_history_id is
602: select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
603: from ghr_pa_history gph1,
604: PER_POSITION_EXTRA_INFO pei1
605: where gph1.table_name = 'PER_POSITION_EXTRA_INFO'
606: and pei1.position_id = p_position_id
607: and pei1.information_type = p_information_type
606: and pei1.position_id = p_position_id
607: and pei1.information_type = p_information_type
608: and gph1.information1 = to_char(pei1.position_extra_info_id)
609: and gph1.effective_date = ( select max(gph2.effective_date)maxdate
610: from ghr_pa_history gph2
611: where gph2.table_name = 'PER_POSITION_EXTRA_INFO'
612: and gph2.effective_date <= p_effective_date
613: and gph2.information1 = to_char(pei1.position_extra_info_id)) ;
614: