[Home] [Help]
10: p_noa_id in number,
11: p_information1 in number,
12: p_table_name in varchar2,
13: p_result out nocopy boolean,
14: p_hist_data out nocopy ghr_pa_history%rowtype);
15:
16: Procedure get_min_hist_id(
17: p_pa_request_id in number,
18: p_noa_id in number,
25: p_information1 in number default null,
26: p_date_effective in date default null,
27: p_altered_pa_request_id in number default null,
28: p_noa_id_corrected in number default null,
29: p_hist_data out nocopy ghr_pa_history%rowtype,
30: p_result_code out nocopy varchar2 );
31:
32: Procedure get_hist_rec_ason_max_date(
33: p_information1 in varchar2,
32: Procedure get_hist_rec_ason_max_date(
33: p_information1 in varchar2,
34: p_max_date_effective in date,
35: p_table_name in varchar2,
36: p_hist_data out nocopy ghr_pa_history%rowtype,
37: p_result out nocopy Boolean);
38:
39: Procedure get_hist_rec_ason_date(
40: p_information1 in varchar2,
40: p_information1 in varchar2,
41: p_date_effective in date,
42: p_table_name in varchar2,
43: p_pa_history_id in number,
44: p_hist_data out nocopy ghr_pa_history%rowtype,
45: p_result out nocopy Boolean);
46:
47: Procedure filter_best_candidate_record(
48: p_hist_data in ghr_pa_history%rowtype,
44: p_hist_data out nocopy ghr_pa_history%rowtype,
45: p_result out nocopy Boolean);
46:
47: Procedure filter_best_candidate_record(
48: p_hist_data in ghr_pa_history%rowtype,
49: p_pa_req_id_skip in number default NULL,
50: p_noa_id_skip in number default NULL,
51: p_save_noa_id in out nocopy number,
52: p_save_found in out nocopy boolean,
51: p_save_noa_id in out nocopy number,
52: p_save_found in out nocopy boolean,
53: p_save_history_id in out nocopy number,
54: p_save_pa_request_id in out nocopy number,
55: p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype);
56:
57: Procedure fetch_for_histid (
58: p_table_name in varchar2,
59: p_information1 in number default null,
58: p_table_name in varchar2,
59: p_information1 in number default null,
60: p_date_effective in date default null,
61: p_pa_history_id in number default null,
62: p_hist_data out nocopy ghr_pa_history%rowtype,
63: p_result_code out nocopy varchar2 );
64:
65:
66: Procedure fetch_for_date_eff(
66: Procedure fetch_for_date_eff(
67: p_table_name in varchar2,
68: p_information1 in number default null,
69: p_date_effective in date default null,
70: p_hist_data out nocopy ghr_pa_history%rowtype,
71: p_result_code out nocopy varchar2 );
72:
73:
74: Procedure Fetch_hist_data(
77: p_date_effective in date default null,
78: p_altered_pa_request_id in number default null,
79: p_noa_id_corrected in number default null,
80: p_pa_history_id in number default null,
81: p_hist_data out nocopy ghr_pa_history%rowtype,
82: p_result_code out nocopy varchar2 );
83:
84: --
85: -- ----------------------------------------------------------------------------
95: Procedure get_hist_rec_ason_max_date(
96: p_information1 in varchar2,
97: p_max_date_effective in date,
98: p_table_name in varchar2,
99: p_hist_data out nocopy ghr_pa_history%rowtype,
100: p_result out nocopy Boolean) is
101:
102:
103: l_proc varchar2(30):='get_hist_rec_ason_max_date';
104:
105: l_history_id number;
106: l_root_pa_request_id number;
107: l_noa_id number;
108: l_save_pa_history_data ghr_pa_history%rowtype;
109: l_hist_rec ghr_pa_history%rowtype;
110:
111: l_found boolean:=FALSE;
112:
105: l_history_id number;
106: l_root_pa_request_id number;
107: l_noa_id number;
108: l_save_pa_history_data ghr_pa_history%rowtype;
109: l_hist_rec ghr_pa_history%rowtype;
110:
111: l_found boolean:=FALSE;
112:
113: l_root_hist_id_broken number;
117: cursor ghr_hist_ndt_canc3 (cp_information1 in varchar2,
118: cp_max_date_effective in date,
119: cp_table_name in varchar2) is
120: select *
121: from ghr_pa_history hist_1
122: where ( altered_pa_request_id is null OR
123: not exists (select 'exists'
124: from ghr_pa_history hist_2
125: where hist_1.altered_pa_request_id = hist_2.pa_request_id
120: select *
121: from ghr_pa_history hist_1
122: where ( altered_pa_request_id is null OR
123: not exists (select 'exists'
124: from ghr_pa_history hist_2
125: where hist_1.altered_pa_request_id = hist_2.pa_request_id
126: and hist_1.information1 = hist_2.information1
127: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
128: and hist_1.table_name = hist_2.table_name)
198: p_information1 in varchar2,
199: p_date_effective in date,
200: p_table_name in varchar2,
201: p_pa_history_id in number,
202: p_hist_data out nocopy ghr_pa_history%rowtype,
203: p_result out nocopy Boolean) is
204:
205:
206: l_proc varchar2(30):='get_hist_rec_ason_date';
208: l_root_pa_history_id number;
209: l_history_id number;
210: l_root_pa_request_id number;
211: l_noa_id number;
212: l_save_pa_history_data ghr_pa_history%rowtype;
213: l_hist_rec ghr_pa_history%rowtype;
214:
215: l_found boolean:=FALSE;
216:
209: l_history_id number;
210: l_root_pa_request_id number;
211: l_noa_id number;
212: l_save_pa_history_data ghr_pa_history%rowtype;
213: l_hist_rec ghr_pa_history%rowtype;
214:
215: l_found boolean:=FALSE;
216:
217: l_root_hist_id_broken number;
230: cp_date_effective in date,
231: cp_pa_history_id in number,
232: cp_table_name in varchar2) is
233: select *
234: from ghr_pa_history hist_1
235: where
236: -- root request or broken chain)
237: ( altered_pa_request_id is null OR
238: not exists (select 'exists'
235: where
236: -- root request or broken chain)
237: ( altered_pa_request_id is null OR
238: not exists (select 'exists'
239: from ghr_pa_history hist_2
240: where hist_1.altered_pa_request_id = hist_2.pa_request_id
241: and hist_1.information1 = hist_2.information1
242: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
243: and hist_1.table_name = hist_2.table_name)
244: )
245: -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
246: and (cp_root_pa_history_id >=
247: (select min(pa_history_id)
248: from ghr_pa_history
249: where pa_request_id =
250: (select min(pa_request_id)
251: from ghr_pa_requests
252: connect by pa_request_id = prior altered_pa_request_id
250: (select min(pa_request_id)
251: from ghr_pa_requests
252: connect by pa_request_id = prior altered_pa_request_id
253: start with pa_request_id = (select pa_request_id
254: from ghr_pa_history
255: where pa_history_id = hist_1.pa_history_id))
256: and nature_of_action_id = hist_1.nature_of_action_id)
257: -- or fetch record created by core form change with lower history_id
258: OR
273: cp_noa_id in number) is
274: select min(pa_history_id),
275: min(pa_request_id),
276: min(nature_of_action_id)
277: from ghr_pa_history
278: where pa_request_id =
279: (select min(pa_request_id)
280: from ghr_pa_requests
281: connect by pa_request_id = prior altered_pa_request_id
285: cursor get_req_and_noa (cp_pa_history_id in number) is
286: select
287: pa_request_id,
288: nature_of_action_id
289: from ghr_pa_history
290: where pa_history_id = cp_pa_history_id;
291:
292: Begin
293:
399: -- This procedure is called by get_hist_rec_ason_max_date and get_hist_rec_ason_date
400: -- it decides if p_hist_data is the best candidate record so far.
401:
402: Procedure filter_best_candidate_record(
403: p_hist_data in ghr_pa_history%rowtype,
404: p_pa_req_id_skip in number default NULL,
405: p_noa_id_skip in number default NULL,
406: p_save_noa_id in out nocopy number,
407: p_save_found in out nocopy boolean,
406: p_save_noa_id in out nocopy number,
407: p_save_found in out nocopy boolean,
408: p_save_history_id in out nocopy number,
409: p_save_pa_request_id in out nocopy number,
410: p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype) is
411:
412:
413: -- this cursor fetches min(pahistory_id) for cp_pa_request_id and cp_nature_of_Action_id
414: cursor c_get_min_hist (cp_pa_request_id in number,
414: cursor c_get_min_hist (cp_pa_request_id in number,
415: cp_nature_of_Action_id in number) is
416: select
417: min(pa_history_id)
418: from ghr_pa_history
419: where
420: pa_request_id = cp_pa_request_id
421: and nature_of_action_id = nvl(cp_nature_of_action_id, nature_of_action_id);
422:
433: --- if the pa_request_id is not null check for correction if any ---
434:
435: cursor cur_pa_corr is
436: select pa_history_id
437: from ghr_pa_history
438: where effective_date = p_hist_data.effective_date
439: and pa_history_id > p_save_history_id
440: and information1 = p_hist_data.information1
441: and table_name = p_hist_data.table_name
551: p_table_name in varchar2,
552: p_information1 in number default null,
553: p_date_effective in date default null,
554: p_pa_history_id in number default null,
555: p_hist_data out nocopy ghr_pa_history%rowtype,
556: p_result_code out nocopy varchar2 ) is
557:
558: l_proc varchar2(30):='Fetch_for_histid';
559: l_max_date_effective date;
557:
558: l_proc varchar2(30):='Fetch_for_histid';
559: l_max_date_effective date;
560: l_date_effective date;
561: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
562: l_hist_data ghr_pa_history%rowtype;
563: l_found boolean:=FALSE;
564:
565: l_result_code varchar2(30);
558: l_proc varchar2(30):='Fetch_for_histid';
559: l_max_date_effective date;
560: l_date_effective date;
561: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
562: l_hist_data ghr_pa_history%rowtype;
563: l_found boolean:=FALSE;
564:
565: l_result_code varchar2(30);
566:
574: cp_date_effective in date,
575: cp_pa_history_id in number,
576: cp_table_name in varchar2) is
577: select *
578: from ghr_pa_history hist_1
579: where
580: -- root request or broken chain)
581: ( altered_pa_request_id is null OR
582: not exists (select 'exists'
579: where
580: -- root request or broken chain)
581: ( altered_pa_request_id is null OR
582: not exists (select 'exists'
583: from ghr_pa_history hist_2
584: where hist_1.altered_pa_request_id = hist_2.pa_request_id
585: and hist_1.information1 = hist_2.information1
586: and hist_1.nature_of_action_id = hist_2.nature_of_action_id)
587: )
587: )
588: -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
589: and (cp_root_pa_history_id >=
590: (select min(pa_history_id)
591: from ghr_pa_history
592: where pa_request_id =
593: (select min(pa_request_id)
594: from ghr_pa_requests
595: connect by pa_request_id = prior altered_pa_request_id
593: (select min(pa_request_id)
594: from ghr_pa_requests
595: connect by pa_request_id = prior altered_pa_request_id
596: start with pa_request_id = (select pa_request_id
597: from ghr_pa_history
598: where pa_history_id = hist_1.pa_history_id))
599: and nature_of_action_id = hist_1.nature_of_action_id)
600: -- or fetch record created by core form change with lower history_id
601: OR
612: cursor ghr_hist_ndt_canc2 (cp_information1 in varchar2,
613: cp_date_effective in date,
614: cp_table_name in varchar2) is
615: select max(effective_date)
616: from ghr_pa_history hist_1
617: where
618: -- root request or broken chain)
619: ( altered_pa_request_id is null OR
620: not exists (select 'exists'
617: where
618: -- root request or broken chain)
619: ( altered_pa_request_id is null OR
620: not exists (select 'exists'
621: from ghr_pa_history hist_2
622: where hist_1.altered_pa_request_id = hist_2.pa_request_id
623: and hist_1.information1 = hist_2.information1
624: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
625: and hist_1.table_name = hist_2.table_name)
635: cp_date_effective in date,
636: cp_table_name in varchar2,
637: cp_pa_history_id in number) is
638: select max(effective_date)
639: from ghr_pa_history hist_1
640: where
641: -- root request or broken chain)
642: ( altered_pa_request_id is null OR
643: not exists (select 'exists'
640: where
641: -- root request or broken chain)
642: ( altered_pa_request_id is null OR
643: not exists (select 'exists'
644: from ghr_pa_history hist_2
645: where hist_1.altered_pa_request_id = hist_2.pa_request_id
646: and hist_1.information1 = hist_2.information1
647: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
648: and hist_1.table_name = hist_2.table_name)
759: Procedure fetch_for_date_eff(
760: p_table_name in varchar2,
761: p_information1 in number default null,
762: p_date_effective in date default null,
763: p_hist_data out nocopy ghr_pa_history%rowtype,
764: p_result_code out nocopy varchar2 ) is
765:
766: l_proc varchar2(30):='gen_fet1';
767: l_max_date_effective date;
764: p_result_code out nocopy varchar2 ) is
765:
766: l_proc varchar2(30):='gen_fet1';
767: l_max_date_effective date;
768: l_hist_data ghr_pa_history%rowtype;
769:
770: l_found boolean:=FALSE;
771:
772: -- This cursor finds the date of the record which was last created as of cp_date_Effective.
773: cursor ghr_hist_post (cp_information1 in varchar2,
774: cp_date_effective in date,
775: cp_table_name in varchar2) is
776: select max(effective_date)
777: from ghr_pa_history hist_1
778: where ( altered_pa_request_id is null OR
779: not exists (select 'exists'
780: from ghr_pa_history hist_2
781: where hist_1.altered_pa_request_id = hist_2.pa_request_id
776: select max(effective_date)
777: from ghr_pa_history hist_1
778: where ( altered_pa_request_id is null OR
779: not exists (select 'exists'
780: from ghr_pa_history hist_2
781: where hist_1.altered_pa_request_id = hist_2.pa_request_id
782: and hist_1.information1 = hist_2.information1
783: and hist_1.nature_of_action_id = hist_2.nature_of_action_id
784: and hist_1.table_name = hist_2.table_name)
841: p_noa_id in number,
842: p_information1 in number,
843: p_table_name in varchar2,
844: p_result out nocopy boolean,
845: p_hist_data out nocopy ghr_pa_history%rowtype) is
846:
847: -- This cursor traverses in the correction tree and finds the last node in the chain
848: -- other than with history_id = cp_pa_history_id.
849: -- Bug 3278827 Added +0 to nature_of_action_id to use GHR_PA_HISTORY_N1 Index.
845: p_hist_data out nocopy ghr_pa_history%rowtype) is
846:
847: -- This cursor traverses in the correction tree and finds the last node in the chain
848: -- other than with history_id = cp_pa_history_id.
849: -- Bug 3278827 Added +0 to nature_of_action_id to use GHR_PA_HISTORY_N1 Index.
850: cursor ghr_hist_ndt_traverse_corrs(cp_pa_history_id in number default hr_api.g_number,
851: cp_pa_request_id in number,
852: cp_noa_id in number,
853: cp_information1 in varchar2,
852: cp_noa_id in number,
853: cp_information1 in varchar2,
854: cp_table_name in varchar2) is
855: select *
856: from ghr_pa_history
857: where information1 = cp_information1
858: and pa_history_id <> nvl(cp_pa_history_id, 0)
859: and table_name = cp_table_name
860: and pa_request_id in
864: connect by prior pa_request_id = altered_pa_request_id)
865: and nature_of_action_id + 0 = cp_noa_id
866: order by pa_history_id desc;
867:
868: l_hist_data ghr_pA_history%rowtype;
869: l_proc varchar2(30):='Traverse';
870: Begin
871: -- traverse.
872: hr_utility.set_location (' Entering : ' || l_proc, 10);
894: p_information1 in number default null,
895: p_date_effective in date default null,
896: p_altered_pa_request_id in number default null,
897: p_noa_id_corrected in number default null,
898: p_hist_data out nocopy ghr_pa_history%rowtype,
899: p_result_code out nocopy varchar2 ) is
900:
901: cursor ghr_hist_corr(
902: cp_information1 in varchar2,
903: cp_altered_pa_request_id in number,
904: cp_noa_id_corrected in number,
905: cp_table_name in varchar2) is
906: select *
907: from ghr_pa_history
908: where information1 = cp_information1
909: and nature_of_action_id = cp_noa_id_corrected
910: and pa_request_id = cp_altered_pa_request_id
911: and table_name = cp_table_name;
957: p_date_effective in date default null,
958: p_altered_pa_request_id in number default null,
959: p_noa_id_corrected in number default null,
960: p_pa_history_id in number default null,
961: p_hist_data out nocopy ghr_pa_history%rowtype,
962: p_result_code out nocopy varchar2 ) is
963:
964: l_proc varchar2(30):='Fetch_hist_data';
965: Begin
1009: cursor get_hist_id
1010: (cp_pa_request_id in number,
1011: cp_noa_id in number) is
1012: select pa_history_id
1013: from ghr_pa_history
1014: where pa_request_id = cp_pa_request_id
1015: and nature_of_action_id = cp_noa_id;
1016:
1017: l_proc varchar2(30):='get_min_hist_id';
1039: p_pa_history_id out nocopy number,
1040: p_result out nocopy boolean) is
1041: cursor get_hist_id is
1042: select pa_history_id
1043: from ghr_pa_history
1044: where pa_request_id = p_pa_request_id
1045: and nature_of_action_id = p_noa_id;
1046: l_proc varchar2(30):='get_hist_id';
1047: Begin
1058:
1059: */
1060:
1061: --
1062: -- Procedure fetch_people fetches the last record from per_people_f or ghr_pa_history
1063: -- which was created between effective start date and effective end date
1064: --
1065: Procedure fetch_people (
1066: p_person_id in number default null,
1071: p_pa_history_id in number default null,
1072: p_people_data out nocopy per_all_people_f%rowtype,
1073: p_result_code out nocopy varchar2 ) is
1074: l_result_code varchar2(100);
1075: l_hist_data ghr_pa_history%rowtype;
1076: l_proc varchar2(30):='fetch_people';
1077: l_people_data per_all_people_f%rowtype;
1078:
1079: cursor per_people_f_cursor is
1171: where assignment_extra_info_id = p_assignment_extra_info_id;
1172:
1173: l_result_code varchar2(30);
1174: l_asgei_data per_assignment_extra_info%rowtype;
1175: l_hist_data ghr_pa_history%rowtype;
1176: l_proc varchar2(30):='fetch_asgei';
1177:
1178: Begin
1179: hr_utility.set_location('Entering:'|| l_proc, 5);
1239: where position_extra_info_id = p_position_extra_info_id;
1240:
1241: l_result_code varchar2(30);
1242: l_posei_data per_position_extra_info%rowtype;
1243: l_hist_data ghr_pa_history%rowtype;
1244: l_proc varchar2(30):='fetch_positionei';
1245:
1246: Begin
1247: hr_utility.set_location('Entering:'|| l_proc, 5);
1301: p_pa_history_id in number default null,
1302: p_assignment_data out nocopy per_all_assignments_f%rowtype,
1303: p_result_code out nocopy varchar2 ) is
1304: l_result_code varchar2(100);
1305: l_hist_data ghr_pa_history%rowtype;
1306: l_proc varchar2(30):='fetch_assignment';
1307: l_assignment_data per_all_assignments_f%rowtype;
1308: cursor per_assignment_f_cursor is
1309: select *
1389: p_pa_history_id in number default null,
1390: p_element_entry_data out nocopy pay_element_entries_f%rowtype,
1391: p_result_code out nocopy varchar2 ) is
1392: l_result_code varchar2(100);
1393: l_hist_data ghr_pa_history%rowtype;
1394: l_proc varchar2(30) := 'fetch_element_entries';
1395: l_element_entry_data pay_element_entries_f%rowtype;
1396: cursor pay_e_entry_f_cursor is
1397: select *
1477: p_get_ovn_flag in varchar2 default 'N',
1478: p_peopleei_data in out nocopy per_people_extra_info%rowtype,
1479: p_result_code out nocopy varchar2 ) is
1480: l_result_code varchar2(100);
1481: l_hist_data ghr_pa_history%rowtype;
1482: l_proc varchar2(30) := 'fetch_peopleei';
1483: l_peopleei_data per_people_extra_info%rowtype;
1484: cursor per_peopleei_rowid_cursor is
1485: select *
1558: )
1559: is
1560: l_proc varchar2(72) := 'Fetch_Asgei (2)';
1561: l_asg_ei_data ghr_assignment_extra_info_h_v%rowtype;
1562: l_pa_history_id ghr_pa_history.pa_history_id%type;
1563: l_max_effective_date date;
1564: l_session ghr_history_api.g_session_var_type;
1565: l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type;
1566: l_result varchar2(20);
1597: )
1598: is
1599: l_proc varchar2(72) := 'Fetch_peoplei (2)';
1600: l_per_ei_data ghr_people_extra_info_h_v%rowtype;
1601: l_pa_history_id ghr_pa_history.pa_history_id%type;
1602: l_max_effective_date date;
1603: l_session ghr_history_api.g_session_var_type;
1604: l_extra_info_id per_people_extra_info.person_extra_info_id%type;
1605: l_result varchar2(20);
1636: )
1637: is
1638: l_proc varchar2(72) := 'Fetch_positionei (2)';
1639: l_pos_ei_data ghr_position_extra_info_h_v%rowtype;
1640: l_pa_history_id ghr_pa_history.pa_history_id%type;
1641: l_max_effective_date date;
1642: l_session ghr_history_api.g_session_var_type;
1643: l_extra_info_id per_position_extra_info.position_extra_info_id%type;
1644: l_result varchar2(20);
1673: End if;
1674: hr_utility.set_location('Leaving ' ||l_proc,20);
1675: End fetch_positionei;
1676: -- The following procedure gets the date_effective row from the
1677: -- ghr_pa_history for the element_entry_values table.
1678: --VSM (What about this procedure) ??
1679: Procedure get_date_eff_eleevl(p_element_entry_value_id in number,
1680: p_date_effective in date,
1681: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1680: p_date_effective in date,
1681: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1682: p_result_code out nocopy varchar2,
1683: p_pa_history_id out nocopy number) IS
1684: l_hist_data ghr_pa_history%rowtype;
1685: -- l_result Boolean;
1686: l_proc varchar2(30) := 'get_date_eff_eleevl';
1687: l_element_entval_data pay_element_entry_values_f%rowtype;
1688: BEGIN
1710: p_rowid in rowid default null,
1711: p_pa_history_id in number default null,
1712: p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1713: p_result_code out nocopy varchar2 ) is
1714: l_hist_data ghr_pa_history%rowtype;
1715: l_result_code varchar2(100);
1716: l_proc varchar2(30) := 'fetch_element_entry_values';
1717: l_element_entval_data pay_element_entry_values_f%rowtype;
1718: cursor pay_e_entry_value_f_cursor is
2229: p_pa_history_id in number default null,
2230: p_address_data out nocopy per_addresses%rowtype,
2231: p_result_code out nocopy varchar2 ) is
2232: l_result_code varchar2(100);
2233: l_hist_data ghr_pa_history%rowtype;
2234: l_proc varchar2(30):='fetch_address';
2235: l_address_data per_addresses%rowtype;
2236:
2237: cursor per_address_rowid_cursor is
2283: when OTHERS then
2284: raise;
2285: End fetch_address ;
2286: --
2287: -- Procedure fetch_person_analyses fetches the last record from per_person_analyses or ghr_pa_history
2288: -- which was created between effective start date and effective end date
2289: --
2290: Procedure fetch_person_analyses (
2291: p_person_analysis_id in number default null,
2297: p_peranalyses_data out nocopy per_person_analyses%rowtype,
2298: p_result_code out nocopy varchar2 ) is
2299: l_peranalyses_data per_person_analyses%rowtype;
2300: l_proc varchar2(30) := 'fetch_person_analyses';
2301: l_hist_data ghr_pa_history%rowtype;
2302: l_result_code varchar2(100);
2303: cursor per_peranalyses_rowid_cursor is
2304: select *
2305: from per_person_analyses
2541: effective_end_date;
2542:
2543: l_result_code varchar2(30);
2544: l_position_data hr_all_positions_f%rowtype;
2545: l_hist_data ghr_pa_history%rowtype;
2546: l_proc varchar2(30):='fetch_positionei';
2547:
2548: Begin
2549: hr_utility.set_location('Entering:'|| l_proc, 5);
2928: FND_DATE.CANONICAL_TO_DATE(gan.information9) date_from,
2929: FND_DATE.CANONICAL_TO_DATE(gan.information10) date_to ,
2930: pa_request_id,
2931: TO_NUMBER(gan.INFORMATION6) analysis_Criteria_id
2932: from ghr_pa_history gan , -- ghr_person_analyses_h_v gan,
2933: per_person_analyses per
2934: where gan.table_name = 'PER_PERSON_ANALYSES'
2935: and TO_NUMBER(gan.information7) = p_person_id -- information7 holds person_id
2936: and per.person_id = TO_NUMBER(gan.information7)
3085: cursor get_root_hist_id(
3086: cp_pa_req_id in number,
3087: cp_noa_id in number) is
3088: select min(pa_history_id)
3089: from ghr_pa_history
3090: where pa_request_id =
3091: (select min(pa_request_id)
3092: from ghr_pa_requests
3093: connect by pa_request_id = prior altered_pa_request_id
3152: cursor get_root_hist_id(
3153: cp_pa_req_id in number,
3154: cp_noa_id in number) is
3155: select min(pa_history_id)
3156: from ghr_pa_history
3157: where pa_request_id =
3158: (select min(pa_request_id)
3159: from ghr_pa_requests
3160: connect by pa_request_id = prior altered_pa_request_id