[Home] [Help]
11: p_noa_id in number,
12: p_information1 in number,
13: p_table_name in varchar2,
14: p_result out nocopy boolean,
15: p_hist_data out nocopy ghr_pa_history%rowtype);
16:
17: Procedure get_min_hist_id(
18: p_pa_request_id in number,
19: p_noa_id in number,
26: p_information1 in number default null,
27: p_date_effective in date default null,
28: p_altered_pa_request_id in number default null,
29: p_noa_id_corrected in number default null,
30: p_hist_data out nocopy ghr_pa_history%rowtype,
31: p_result_code out nocopy varchar2 );
32:
33: Procedure get_hist_rec_ason_max_date(
34: p_information1 in varchar2,
33: Procedure get_hist_rec_ason_max_date(
34: p_information1 in varchar2,
35: p_max_date_effective in date,
36: p_table_name in varchar2,
37: p_hist_data out nocopy ghr_pa_history%rowtype,
38: p_result out nocopy Boolean);
39:
40: Procedure get_hist_rec_ason_date(
41: p_information1 in varchar2,
41: p_information1 in varchar2,
42: p_date_effective in date,
43: p_table_name in varchar2,
44: p_pa_history_id in number,
45: p_hist_data out nocopy ghr_pa_history%rowtype,
46: p_result out nocopy Boolean);
47:
48: Procedure filter_best_candidate_record(
49: p_hist_data in ghr_pa_history%rowtype,
45: p_hist_data out nocopy ghr_pa_history%rowtype,
46: p_result out nocopy Boolean);
47:
48: Procedure filter_best_candidate_record(
49: p_hist_data in ghr_pa_history%rowtype,
50: p_pa_req_id_skip in number default NULL,
51: p_noa_id_skip in number default NULL,
52: p_save_noa_id in out nocopy number,
53: p_save_found in out nocopy boolean,
52: p_save_noa_id in out nocopy number,
53: p_save_found in out nocopy boolean,
54: p_save_history_id in out nocopy number,
55: p_save_pa_request_id in out nocopy number,
56: p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype);
57:
58: Procedure fetch_for_histid (
59: p_table_name in varchar2,
60: p_information1 in number default null,
59: p_table_name in varchar2,
60: p_information1 in number default null,
61: p_date_effective in date default null,
62: p_pa_history_id in number default null,
63: p_hist_data out nocopy ghr_pa_history%rowtype,
64: p_result_code out nocopy varchar2 );
65:
66:
67: Procedure fetch_for_date_eff(
67: Procedure fetch_for_date_eff(
68: p_table_name in varchar2,
69: p_information1 in number default null,
70: p_date_effective in date default null,
71: p_hist_data out nocopy ghr_pa_history%rowtype,
72: p_result_code out nocopy varchar2 );
73:
74:
75: Procedure Fetch_hist_data(
78: p_date_effective in date default null,
79: p_altered_pa_request_id in number default null,
80: p_noa_id_corrected in number default null,
81: p_pa_history_id in number default null,
82: p_hist_data out nocopy ghr_pa_history%rowtype,
83: p_result_code out nocopy varchar2 );
84:
85: --
86: -- ----------------------------------------------------------------------------
96: Procedure get_hist_rec_ason_max_date(
97: p_information1 in varchar2,
98: p_max_date_effective in date,
99: p_table_name in varchar2,
100: p_hist_data out nocopy ghr_pa_history%rowtype,
101: p_result out nocopy Boolean) is
102:
103:
104: l_proc varchar2(30):='get_hist_rec_ason_max_date';
105:
106: l_history_id number;
107: l_root_pa_request_id number;
108: l_noa_id number;
109: l_save_pa_history_data ghr_pa_history%rowtype;
110: l_hist_rec ghr_pa_history%rowtype;
111:
112: l_found boolean:=FALSE;
113:
106: l_history_id number;
107: l_root_pa_request_id number;
108: l_noa_id number;
109: l_save_pa_history_data ghr_pa_history%rowtype;
110: l_hist_rec ghr_pa_history%rowtype;
111:
112: l_found boolean:=FALSE;
113:
114: l_root_hist_id_broken number;
118: cursor ghr_hist_ndt_canc3 (cp_information1 in varchar2,
119: cp_max_date_effective in date,
120: cp_table_name in varchar2) is
121: select *
122: from ghr_pa_history hist_1
123: where ( altered_pa_request_id is null OR
124: not exists (select 'exists'
125: from ghr_pa_history hist_2
126: where hist_1.altered_pa_request_id = hist_2.pa_request_id
121: select *
122: from ghr_pa_history hist_1
123: where ( altered_pa_request_id is null OR
124: not exists (select 'exists'
125: from ghr_pa_history hist_2
126: where hist_1.altered_pa_request_id = hist_2.pa_request_id
127: and hist_1.information1 = hist_2.information1
128: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
129: and hist_1.table_name = hist_2.table_name)
199: p_information1 in varchar2,
200: p_date_effective in date,
201: p_table_name in varchar2,
202: p_pa_history_id in number,
203: p_hist_data out nocopy ghr_pa_history%rowtype,
204: p_result out nocopy Boolean) is
205:
206:
207: l_proc varchar2(30):='get_hist_rec_ason_date';
209: l_root_pa_history_id number;
210: l_history_id number;
211: l_root_pa_request_id number;
212: l_noa_id number;
213: l_save_pa_history_data ghr_pa_history%rowtype;
214: l_hist_rec ghr_pa_history%rowtype;
215:
216: l_found boolean:=FALSE;
217:
210: l_history_id number;
211: l_root_pa_request_id number;
212: l_noa_id number;
213: l_save_pa_history_data ghr_pa_history%rowtype;
214: l_hist_rec ghr_pa_history%rowtype;
215:
216: l_found boolean:=FALSE;
217:
218: l_root_hist_id_broken number;
231: cp_date_effective in date,
232: cp_pa_history_id in number,
233: cp_table_name in varchar2) is
234: select *
235: from ghr_pa_history hist_1
236: where
237: -- root request or broken chain)
238: ( altered_pa_request_id is null OR
239: not exists (select 'exists'
236: where
237: -- root request or broken chain)
238: ( altered_pa_request_id is null OR
239: not exists (select 'exists'
240: from ghr_pa_history hist_2
241: where hist_1.altered_pa_request_id = hist_2.pa_request_id
242: and hist_1.information1 = hist_2.information1
243: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
244: and hist_1.table_name = hist_2.table_name)
245: )
246: -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
247: and (cp_root_pa_history_id >=
248: (select min(pa_history_id)
249: from ghr_pa_history
250: where pa_request_id =
251: (select min(pa_request_id)
252: from ghr_pa_requests
253: connect by pa_request_id = prior altered_pa_request_id
251: (select min(pa_request_id)
252: from ghr_pa_requests
253: connect by pa_request_id = prior altered_pa_request_id
254: start with pa_request_id = (select pa_request_id
255: from ghr_pa_history
256: where pa_history_id = hist_1.pa_history_id))
257: and nature_of_action_id = hist_1.nature_of_action_id)
258: -- or fetch record created by core form change with lower history_id
259: OR
274: cp_noa_id in number) is
275: select min(pa_history_id),
276: min(pa_request_id),
277: min(nature_of_action_id)
278: from ghr_pa_history
279: where pa_request_id =
280: (select min(pa_request_id)
281: from ghr_pa_requests
282: connect by pa_request_id = prior altered_pa_request_id
286: cursor get_req_and_noa (cp_pa_history_id in number) is
287: select
288: pa_request_id,
289: nature_of_action_id
290: from ghr_pa_history
291: where pa_history_id = cp_pa_history_id;
292:
293: Begin
294:
400: -- This procedure is called by get_hist_rec_ason_max_date and get_hist_rec_ason_date
401: -- it decides if p_hist_data is the best candidate record so far.
402:
403: Procedure filter_best_candidate_record(
404: p_hist_data in ghr_pa_history%rowtype,
405: p_pa_req_id_skip in number default NULL,
406: p_noa_id_skip in number default NULL,
407: p_save_noa_id in out nocopy number,
408: p_save_found in out nocopy boolean,
407: p_save_noa_id in out nocopy number,
408: p_save_found in out nocopy boolean,
409: p_save_history_id in out nocopy number,
410: p_save_pa_request_id in out nocopy number,
411: p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype) is
412:
413:
414: -- this cursor fetches min(pahistory_id) for cp_pa_request_id and cp_nature_of_Action_id
415: cursor c_get_min_hist (cp_pa_request_id in number,
415: cursor c_get_min_hist (cp_pa_request_id in number,
416: cp_nature_of_Action_id in number) is
417: select
418: min(pa_history_id)
419: from ghr_pa_history
420: where
421: pa_request_id = cp_pa_request_id
422: and nature_of_action_id = nvl(cp_nature_of_action_id, nature_of_action_id);
423:
434: --- if the pa_request_id is not null check for correction if any ---
435:
436: cursor cur_pa_corr is
437: select pa_history_id
438: from ghr_pa_history
439: where effective_date = p_hist_data.effective_date
440: and pa_history_id > p_save_history_id
441: and information1 = p_hist_data.information1
442: and table_name = p_hist_data.table_name
560: p_table_name in varchar2,
561: p_information1 in number default null,
562: p_date_effective in date default null,
563: p_pa_history_id in number default null,
564: p_hist_data out nocopy ghr_pa_history%rowtype,
565: p_result_code out nocopy varchar2 ) is
566:
567: l_proc varchar2(30):='Fetch_for_histid';
568: l_max_date_effective date;
566:
567: l_proc varchar2(30):='Fetch_for_histid';
568: l_max_date_effective date;
569: l_date_effective date;
570: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
571: l_hist_data ghr_pa_history%rowtype;
572: l_found boolean:=FALSE;
573:
574: l_result_code varchar2(30);
567: l_proc varchar2(30):='Fetch_for_histid';
568: l_max_date_effective date;
569: l_date_effective date;
570: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
571: l_hist_data ghr_pa_history%rowtype;
572: l_found boolean:=FALSE;
573:
574: l_result_code varchar2(30);
575:
583: cp_date_effective in date,
584: cp_pa_history_id in number,
585: cp_table_name in varchar2) is
586: select *
587: from ghr_pa_history hist_1
588: where
589: -- root request or broken chain)
590: ( altered_pa_request_id is null OR
591: not exists (select 'exists'
588: where
589: -- root request or broken chain)
590: ( altered_pa_request_id is null OR
591: not exists (select 'exists'
592: from ghr_pa_history hist_2
593: where hist_1.altered_pa_request_id = hist_2.pa_request_id
594: and hist_1.information1 = hist_2.information1
595: and hist_1.nature_of_action_id = hist_2.nature_of_action_id)
596: )
596: )
597: -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
598: and (cp_root_pa_history_id >=
599: (select min(pa_history_id)
600: from ghr_pa_history
601: where pa_request_id =
602: (select min(pa_request_id)
603: from ghr_pa_requests
604: connect by pa_request_id = prior altered_pa_request_id
602: (select min(pa_request_id)
603: from ghr_pa_requests
604: connect by pa_request_id = prior altered_pa_request_id
605: start with pa_request_id = (select pa_request_id
606: from ghr_pa_history
607: where pa_history_id = hist_1.pa_history_id))
608: and nature_of_action_id = hist_1.nature_of_action_id)
609: -- or fetch record created by core form change with lower history_id
610: OR
621: cursor ghr_hist_ndt_canc2 (cp_information1 in varchar2,
622: cp_date_effective in date,
623: cp_table_name in varchar2) is
624: select max(effective_date)
625: from ghr_pa_history hist_1
626: where
627: -- root request or broken chain)
628: ( altered_pa_request_id is null OR
629: not exists (select 'exists'
626: where
627: -- root request or broken chain)
628: ( altered_pa_request_id is null OR
629: not exists (select 'exists'
630: from ghr_pa_history hist_2
631: where hist_1.altered_pa_request_id = hist_2.pa_request_id
632: and hist_1.information1 = hist_2.information1
633: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
634: and hist_1.table_name = hist_2.table_name)
644: cp_date_effective in date,
645: cp_table_name in varchar2,
646: cp_pa_history_id in number) is
647: select max(effective_date)
648: from ghr_pa_history hist_1
649: where
650: -- root request or broken chain)
651: ( altered_pa_request_id is null OR
652: not exists (select 'exists'
649: where
650: -- root request or broken chain)
651: ( altered_pa_request_id is null OR
652: not exists (select 'exists'
653: from ghr_pa_history hist_2
654: where hist_1.altered_pa_request_id = hist_2.pa_request_id
655: and hist_1.information1 = hist_2.information1
656: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
657: and hist_1.table_name = hist_2.table_name)
768: Procedure fetch_for_date_eff(
769: p_table_name in varchar2,
770: p_information1 in number default null,
771: p_date_effective in date default null,
772: p_hist_data out nocopy ghr_pa_history%rowtype,
773: p_result_code out nocopy varchar2 ) is
774:
775: l_proc varchar2(30):='gen_fet1';
776: l_max_date_effective date;
773: p_result_code out nocopy varchar2 ) is
774:
775: l_proc varchar2(30):='gen_fet1';
776: l_max_date_effective date;
777: l_hist_data ghr_pa_history%rowtype;
778:
779: l_found boolean:=FALSE;
780:
781: -- This cursor finds the date of the record which was last created as of cp_date_Effective.
782: cursor ghr_hist_post (cp_information1 in varchar2,
783: cp_date_effective in date,
784: cp_table_name in varchar2) is
785: select max(effective_date)
786: from ghr_pa_history hist_1
787: where ( altered_pa_request_id is null OR
788: not exists (select 'exists'
789: from ghr_pa_history hist_2
790: where hist_1.altered_pa_request_id = hist_2.pa_request_id
785: select max(effective_date)
786: from ghr_pa_history hist_1
787: where ( altered_pa_request_id is null OR
788: not exists (select 'exists'
789: from ghr_pa_history hist_2
790: where hist_1.altered_pa_request_id = hist_2.pa_request_id
791: and hist_1.information1 = hist_2.information1
792: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
793: and hist_1.table_name = hist_2.table_name)
850: p_noa_id in number,
851: p_information1 in number,
852: p_table_name in varchar2,
853: p_result out nocopy boolean,
854: p_hist_data out nocopy ghr_pa_history%rowtype) is
855:
856: -- This cursor traverses in the correction tree and finds the last node in the chain
857: -- other than with history_id = cp_pa_history_id.
858: -- Bug 3278827 Added +0 to nature_of_action_id to use GHR_PA_HISTORY_N1 Index.
854: p_hist_data out nocopy ghr_pa_history%rowtype) is
855:
856: -- This cursor traverses in the correction tree and finds the last node in the chain
857: -- other than with history_id = cp_pa_history_id.
858: -- Bug 3278827 Added +0 to nature_of_action_id to use GHR_PA_HISTORY_N1 Index.
859: cursor ghr_hist_ndt_traverse_corrs(cp_pa_history_id in number default hr_api.g_number,
860: cp_pa_request_id in number,
861: cp_noa_id in number,
862: cp_information1 in varchar2,
861: cp_noa_id in number,
862: cp_information1 in varchar2,
863: cp_table_name in varchar2) is
864: select *
865: from ghr_pa_history
866: where information1 = cp_information1
867: and pa_history_id <> nvl(cp_pa_history_id, 0)
868: and table_name = cp_table_name
869: and pa_request_id in
873: connect by prior pa_request_id = altered_pa_request_id)
874: and nature_of_action_id + 0 = cp_noa_id
875: order by pa_history_id desc;
876:
877: l_hist_data ghr_pA_history%rowtype;
878: l_proc varchar2(30):='Traverse';
879: Begin
880: -- traverse.
881: hr_utility.set_location (' Entering : ' || l_proc, 10);
903: p_information1 in number default null,
904: p_date_effective in date default null,
905: p_altered_pa_request_id in number default null,
906: p_noa_id_corrected in number default null,
907: p_hist_data out nocopy ghr_pa_history%rowtype,
908: p_result_code out nocopy varchar2 ) is
909:
910: cursor ghr_hist_corr(
911: cp_information1 in varchar2,
911: cp_information1 in varchar2,
912: cp_altered_pa_request_id in number,
913: cp_noa_id_corrected in number,
914: cp_table_name in varchar2) is
915: SELECT /*+ index(GHR_PA_HISTORY GHR_PA_HISTORY_N1) */ *
916: from ghr_pa_history
917: where information1 = cp_information1
918: and nature_of_action_id = cp_noa_id_corrected
919: and pa_request_id = cp_altered_pa_request_id
912: cp_altered_pa_request_id in number,
913: cp_noa_id_corrected in number,
914: cp_table_name in varchar2) is
915: SELECT /*+ index(GHR_PA_HISTORY GHR_PA_HISTORY_N1) */ *
916: from ghr_pa_history
917: where information1 = cp_information1
918: and nature_of_action_id = cp_noa_id_corrected
919: and pa_request_id = cp_altered_pa_request_id
920: and table_name = cp_table_name;
923: cursor ghr_get_hist_max_date (cp_information1 in varchar2,
924: cp_date_effective in date,
925: cp_table_name in varchar2) is
926: select max(effective_date)
927: from ghr_pa_history hist_1
928: where ( altered_pa_request_id is null OR
929: not exists (select 'exists'
930: from ghr_pa_history hist_2
931: where hist_1.altered_pa_request_id = hist_2.pa_request_id
926: select max(effective_date)
927: from ghr_pa_history hist_1
928: where ( altered_pa_request_id is null OR
929: not exists (select 'exists'
930: from ghr_pa_history hist_2
931: where hist_1.altered_pa_request_id = hist_2.pa_request_id
932: and hist_1.information1 = hist_2.information1
933: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
934: and hist_1.table_name = hist_2.table_name)
1025: p_date_effective in date default null,
1026: p_altered_pa_request_id in number default null,
1027: p_noa_id_corrected in number default null,
1028: p_pa_history_id in number default null,
1029: p_hist_data out nocopy ghr_pa_history%rowtype,
1030: p_result_code out nocopy varchar2 ) is
1031:
1032: l_proc varchar2(30):='Fetch_hist_data';
1033: Begin
1077: cursor get_hist_id
1078: (cp_pa_request_id in number,
1079: cp_noa_id in number) is
1080: select pa_history_id
1081: from ghr_pa_history
1082: where pa_request_id = cp_pa_request_id
1083: and nature_of_action_id = cp_noa_id;
1084:
1085: l_proc varchar2(30):='get_min_hist_id';
1107: p_pa_history_id out nocopy number,
1108: p_result out nocopy boolean) is
1109: cursor get_hist_id is
1110: select pa_history_id
1111: from ghr_pa_history
1112: where pa_request_id = p_pa_request_id
1113: and nature_of_action_id = p_noa_id;
1114: l_proc varchar2(30):='get_hist_id';
1115: Begin
1126:
1127: */
1128:
1129: --
1130: -- Procedure fetch_people fetches the last record from per_people_f or ghr_pa_history
1131: -- which was created between effective start date and effective end date
1132: --
1133: Procedure fetch_people (
1134: p_person_id in number default null,
1139: p_pa_history_id in number default null,
1140: p_people_data out nocopy per_all_people_f%rowtype,
1141: p_result_code out nocopy varchar2 ) is
1142: l_result_code varchar2(100);
1143: l_hist_data ghr_pa_history%rowtype;
1144: l_proc varchar2(30):='fetch_people';
1145: l_people_data per_all_people_f%rowtype;
1146:
1147: cursor per_people_f_cursor is
1239: where assignment_extra_info_id = p_assignment_extra_info_id;
1240:
1241: l_result_code varchar2(30);
1242: l_asgei_data per_assignment_extra_info%rowtype;
1243: l_hist_data ghr_pa_history%rowtype;
1244: l_proc varchar2(30):='fetch_asgei';
1245:
1246: Begin
1247: hr_utility.set_location('Entering:'|| l_proc, 5);
1307: where position_extra_info_id = p_position_extra_info_id;
1308:
1309: l_result_code varchar2(30);
1310: l_posei_data per_position_extra_info%rowtype;
1311: l_hist_data ghr_pa_history%rowtype;
1312: l_proc varchar2(30):='fetch_positionei';
1313:
1314: Begin
1315: hr_utility.set_location('Entering:'|| l_proc, 5);
1371: p_pa_history_id in number default null,
1372: p_assignment_data out nocopy per_all_assignments_f%rowtype,
1373: p_result_code out nocopy varchar2 ) is
1374: l_result_code varchar2(100);
1375: l_hist_data ghr_pa_history%rowtype;
1376: l_proc varchar2(30):='fetch_assignment';
1377: l_assignment_data per_all_assignments_f%rowtype;
1378: cursor per_assignment_f_cursor is
1379: select *
1459: p_pa_history_id in number default null,
1460: p_element_entry_data out nocopy pay_element_entries_f%rowtype,
1461: p_result_code out nocopy varchar2 ) is
1462: l_result_code varchar2(100);
1463: l_hist_data ghr_pa_history%rowtype;
1464: l_proc varchar2(30) := 'fetch_element_entries';
1465: l_element_entry_data pay_element_entries_f%rowtype;
1466: cursor pay_e_entry_f_cursor is
1467: select *
1547: p_get_ovn_flag in varchar2 default 'N',
1548: p_peopleei_data in out nocopy per_people_extra_info%rowtype,
1549: p_result_code out nocopy varchar2 ) is
1550: l_result_code varchar2(100);
1551: l_hist_data ghr_pa_history%rowtype;
1552: l_proc varchar2(30) := 'fetch_peopleei';
1553: l_peopleei_data per_people_extra_info%rowtype;
1554: cursor per_peopleei_rowid_cursor is
1555: select *
1628: )
1629: is
1630: l_proc varchar2(72) := 'Fetch_Asgei (2)';
1631: l_asg_ei_data ghr_assignment_extra_info_h_v%rowtype;
1632: l_pa_history_id ghr_pa_history.pa_history_id%type;
1633: l_max_effective_date date;
1634: l_session ghr_history_api.g_session_var_type;
1635: l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type;
1636: l_result varchar2(20);
1650: where fnd_date.canonical_to_date(aei_in.aei_information1)<= p_date_effective
1651: AND aei_in.information_type=aei.information_type
1652: and aei_in.assignment_id = aei.assignment_id
1653: AND aei_in.assignment_extra_info_id IN(
1654: select to_number(information1) from ghr_pa_history gph
1655: where gph.assignment_id = p_assignment_id
1656: and gph.information5= p_information_type
1657: and gph.pa_request_id is not null));
1658: --End Bug 11680558
1692: )
1693: is
1694: l_proc varchar2(72) := 'Fetch_peoplei (2)';
1695: l_per_ei_data ghr_people_extra_info_h_v%rowtype;
1696: l_pa_history_id ghr_pa_history.pa_history_id%type;
1697: l_max_effective_date date;
1698: l_session ghr_history_api.g_session_var_type;
1699: l_extra_info_id per_people_extra_info.person_extra_info_id%type;
1700: l_result varchar2(20);
1731: )
1732: is
1733: l_proc varchar2(72) := 'Fetch_positionei (2)';
1734: l_pos_ei_data ghr_position_extra_info_h_v%rowtype;
1735: l_pa_history_id ghr_pa_history.pa_history_id%type;
1736: l_max_effective_date date;
1737: l_session ghr_history_api.g_session_var_type;
1738: l_extra_info_id per_position_extra_info.position_extra_info_id%type;
1739: l_result varchar2(20);
1773: g_info_type := NULL;
1774: hr_utility.set_location('Leaving ' ||l_proc,20);
1775: End fetch_positionei;
1776: -- The following procedure gets the date_effective row from the
1777: -- ghr_pa_history for the element_entry_values table.
1778: --VSM (What about this procedure) ??
1779: Procedure get_date_eff_eleevl(p_element_entry_value_id in number,
1780: p_date_effective in date,
1781: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1780: p_date_effective in date,
1781: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1782: p_result_code out nocopy varchar2,
1783: p_pa_history_id out nocopy number) IS
1784: l_hist_data ghr_pa_history%rowtype;
1785: -- l_result Boolean;
1786: l_proc varchar2(30) := 'get_date_eff_eleevl';
1787: l_element_entval_data pay_element_entry_values_f%rowtype;
1788: BEGIN
1810: p_rowid in rowid default null,
1811: p_pa_history_id in number default null,
1812: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1813: p_result_code out nocopy varchar2 ) is
1814: l_hist_data ghr_pa_history%rowtype;
1815: l_result_code varchar2(100);
1816: l_proc varchar2(30) := 'fetch_element_entry_values';
1817: l_element_entval_data pay_element_entry_values_f%rowtype;
1818: cursor pay_e_entry_value_f_cursor is
2329: p_pa_history_id in number default null,
2330: p_address_data out nocopy per_addresses%rowtype,
2331: p_result_code out nocopy varchar2 ) is
2332: l_result_code varchar2(100);
2333: l_hist_data ghr_pa_history%rowtype;
2334: l_proc varchar2(30):='fetch_address';
2335: l_address_data per_addresses%rowtype;
2336:
2337: cursor per_address_rowid_cursor is
2383: when OTHERS then
2384: raise;
2385: End fetch_address ;
2386: --
2387: -- Procedure fetch_person_analyses fetches the last record from per_person_analyses or ghr_pa_history
2388: -- which was created between effective start date and effective end date
2389: --
2390: Procedure fetch_person_analyses (
2391: p_person_analysis_id in number default null,
2397: p_peranalyses_data out nocopy per_person_analyses%rowtype,
2398: p_result_code out nocopy varchar2 ) is
2399: l_peranalyses_data per_person_analyses%rowtype;
2400: l_proc varchar2(30) := 'fetch_person_analyses';
2401: l_hist_data ghr_pa_history%rowtype;
2402: l_result_code varchar2(100);
2403: cursor per_peranalyses_rowid_cursor is
2404: select *
2405: from per_person_analyses
2641: effective_end_date;
2642:
2643: l_result_code varchar2(30);
2644: l_position_data hr_all_positions_f%rowtype;
2645: l_hist_data ghr_pa_history%rowtype;
2646: l_proc varchar2(30):='fetch_positionei';
2647:
2648: Begin
2649: hr_utility.set_location('Entering:'|| l_proc, 5);
3028: FND_DATE.CANONICAL_TO_DATE(gan.information9) date_from,
3029: FND_DATE.CANONICAL_TO_DATE(gan.information10) date_to ,
3030: pa_request_id,
3031: TO_NUMBER(gan.INFORMATION6) analysis_Criteria_id
3032: from ghr_pa_history gan , -- ghr_person_analyses_h_v gan,
3033: per_person_analyses per
3034: where gan.table_name = 'PER_PERSON_ANALYSES'
3035: and TO_NUMBER(gan.information7) = p_person_id -- information7 holds person_id
3036: and per.person_id = TO_NUMBER(gan.information7)
3185: cursor get_root_hist_id(
3186: cp_pa_req_id in number,
3187: cp_noa_id in number) is
3188: select min(pa_history_id)
3189: from ghr_pa_history
3190: where pa_request_id =
3191: (select min(pa_request_id)
3192: from ghr_pa_requests
3193: connect by pa_request_id = prior altered_pa_request_id
3252: cursor get_root_hist_id(
3253: cp_pa_req_id in number,
3254: cp_noa_id in number) is
3255: select min(pa_history_id)
3256: from ghr_pa_history
3257: where pa_request_id =
3258: (select min(pa_request_id)
3259: from ghr_pa_requests
3260: connect by pa_request_id = prior altered_pa_request_id