[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:
492: p_information_type in varchar2,
493: p_effective_date in date
494: ) return number is
495: l_proc varchar2(72) ;
496: l_history_id ghr_pa_history.pa_history_id%type;
497:
498: begin
499: l_proc := g_package||'get_history_id';
500: if (p_assignment_type = 'E' or p_assignment_type = 'C') then
509: p_information_type in varchar2,
510: p_effective_date in date) return number is
511:
512: l_proc varchar2(72) ;
513: l_history_id ghr_pa_history.pa_history_id%type;
514:
515:
516: -- This cursor gets the latest date on which the given Extra information Type record
517: -- is inserted or updated on or before the given date
520: -- In c_history_id join on PER_ASSIGNMENT_EXTRA_INFO is required to skip the history records
521: -- on the extra information records that are deleted.
522: cursor c_history_id is
523: select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
524: from ghr_pa_history gph1,
525: PER_ASSIGNMENT_EXTRA_INFO pei1
526: where gph1.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
527: and pei1.assignment_id = p_asg_id
528: and pei1.information_type = p_information_type
527: and pei1.assignment_id = p_asg_id
528: and pei1.information_type = p_information_type
529: and gph1.information1 = to_char(pei1.assignment_extra_info_id)
530: and gph1.effective_date = ( select max(gph2.effective_date)maxdate
531: from ghr_pa_history gph2
532: where gph2.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
533: and gph2.effective_date <= p_effective_date
534: and gph2.information1 = to_char(pei1.assignment_extra_info_id)) ;
535:
563: p_effective_date in date
564: ) return number is
565:
566: l_proc varchar2(72) ;
567: l_history_id ghr_pa_history.pa_history_id%type;
568:
569:
570: -- This cursor gets the latest date on which the given Extra information Type record
571: -- is inserted or updated on or before the given date
574: -- In c_history_id join on PER_POSITION_EXTRA_INFO is required to skip the history records
575: -- on the extra information records that are deleted.
576: cursor c_history_id is
577: select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
578: from ghr_pa_history gph1,
579: PER_POSITION_EXTRA_INFO pei1
580: where gph1.table_name = 'PER_POSITION_EXTRA_INFO'
581: and pei1.position_id = p_position_id
582: and pei1.information_type = p_information_type
581: and pei1.position_id = p_position_id
582: and pei1.information_type = p_information_type
583: and gph1.information1 = to_char(pei1.position_extra_info_id)
584: and gph1.effective_date = ( select max(gph2.effective_date)maxdate
585: from ghr_pa_history gph2
586: where gph2.table_name = 'PER_POSITION_EXTRA_INFO'
587: and gph2.effective_date <= p_effective_date
588: and gph2.information1 = to_char(pei1.position_extra_info_id)) ;
589: