2: /* $Header: ghcascad.pkb 115.31 2004/05/27 10:11:41 ajose ship $ */
3: --
4: -- forward declare all local procedures/functions
5: Procedure Cascade_People (
6: p_pre_record in ghr_pa_history%rowtype,
7: p_post_record in ghr_pa_history%rowtype,
8: p_cascade_type in varchar2,
9: p_interv_on_eff_date in Boolean,
10: p_hist_data_as_of_date in ghr_pa_history%rowtype
3: --
4: -- forward declare all local procedures/functions
5: Procedure Cascade_People (
6: p_pre_record in ghr_pa_history%rowtype,
7: p_post_record in ghr_pa_history%rowtype,
8: p_cascade_type in varchar2,
9: p_interv_on_eff_date in Boolean,
10: p_hist_data_as_of_date in ghr_pa_history%rowtype
11: );
6: p_pre_record in ghr_pa_history%rowtype,
7: p_post_record in ghr_pa_history%rowtype,
8: p_cascade_type in varchar2,
9: p_interv_on_eff_date in Boolean,
10: p_hist_data_as_of_date in ghr_pa_history%rowtype
11: );
12:
13: Procedure Cascade_asgn (
14: p_pre_record in ghr_pa_history%rowtype,
10: p_hist_data_as_of_date in ghr_pa_history%rowtype
11: );
12:
13: Procedure Cascade_asgn (
14: p_pre_record in ghr_pa_history%rowtype,
15: p_post_record in ghr_pa_history%rowtype,
16: p_cascade_type in varchar2,
17: p_interv_on_eff_date in Boolean,
18: p_hist_data_as_of_date in ghr_pa_history%rowtype
11: );
12:
13: Procedure Cascade_asgn (
14: p_pre_record in ghr_pa_history%rowtype,
15: p_post_record in ghr_pa_history%rowtype,
16: p_cascade_type in varchar2,
17: p_interv_on_eff_date in Boolean,
18: p_hist_data_as_of_date in ghr_pa_history%rowtype
19: );
14: p_pre_record in ghr_pa_history%rowtype,
15: p_post_record in ghr_pa_history%rowtype,
16: p_cascade_type in varchar2,
17: p_interv_on_eff_date in Boolean,
18: p_hist_data_as_of_date in ghr_pa_history%rowtype
19: );
20:
21: Procedure Cascade_peopleei (
22: p_post_record in ghr_pa_history%rowtype
18: p_hist_data_as_of_date in ghr_pa_history%rowtype
19: );
20:
21: Procedure Cascade_peopleei (
22: p_post_record in ghr_pa_history%rowtype
23: );
24:
25: Procedure Cascade_asgnei (
26: p_post_record in ghr_pa_history%rowtype);
22: p_post_record in ghr_pa_history%rowtype
23: );
24:
25: Procedure Cascade_asgnei (
26: p_post_record in ghr_pa_history%rowtype);
27:
28: Procedure Cascade_posnei (
29: p_post_record in ghr_pa_history%rowtype
30: );
25: Procedure Cascade_asgnei (
26: p_post_record in ghr_pa_history%rowtype);
27:
28: Procedure Cascade_posnei (
29: p_post_record in ghr_pa_history%rowtype
30: );
31:
32: /*
33: Procedure Cascade_posn (
30: );
31:
32: /*
33: Procedure Cascade_posn (
34: p_post_record in ghr_pa_history%rowtype
35: );
36: */
37:
38: Procedure Cascade_posn (
35: );
36: */
37:
38: Procedure Cascade_posn (
39: p_pre_record in ghr_pa_history%rowtype,
40: p_post_record in ghr_pa_history%rowtype,
41: p_cascade_type in varchar2,
42: p_interv_on_eff_date in Boolean,
43: p_hist_data_as_of_date in ghr_pa_history%rowtype
36: */
37:
38: Procedure Cascade_posn (
39: p_pre_record in ghr_pa_history%rowtype,
40: p_post_record in ghr_pa_history%rowtype,
41: p_cascade_type in varchar2,
42: p_interv_on_eff_date in Boolean,
43: p_hist_data_as_of_date in ghr_pa_history%rowtype
44: );
39: p_pre_record in ghr_pa_history%rowtype,
40: p_post_record in ghr_pa_history%rowtype,
41: p_cascade_type in varchar2,
42: p_interv_on_eff_date in Boolean,
43: p_hist_data_as_of_date in ghr_pa_history%rowtype
44: );
45:
46: Procedure Cascade_perana (
47: p_post_record in ghr_pa_history%rowtype
43: p_hist_data_as_of_date in ghr_pa_history%rowtype
44: );
45:
46: Procedure Cascade_perana (
47: p_post_record in ghr_pa_history%rowtype
48: );
49:
50: Procedure Cascade_addresses (
51: p_post_record in ghr_pa_history%rowtype );
47: p_post_record in ghr_pa_history%rowtype
48: );
49:
50: Procedure Cascade_addresses (
51: p_post_record in ghr_pa_history%rowtype );
52:
53: Procedure correct_people_row (
54: p_people_data in out nocopy per_all_people_f%rowtype);
55:
68:
69: Procedure Correct_asgnei_row (
70: p_asgnei_data in out nocopy per_assignment_extra_info%rowtype);
71:
72: Procedure cascade_change( p_pre_record in ghr_pa_history%rowtype,
73: p_post_record in ghr_pa_history%rowtype,
74: p_apply_record in out nocopy ghr_pa_history%rowtype,
75: p_true_false in out nocopy ghr_history_cascade.condition_rg_type
76: );
69: Procedure Correct_asgnei_row (
70: p_asgnei_data in out nocopy per_assignment_extra_info%rowtype);
71:
72: Procedure cascade_change( p_pre_record in ghr_pa_history%rowtype,
73: p_post_record in ghr_pa_history%rowtype,
74: p_apply_record in out nocopy ghr_pa_history%rowtype,
75: p_true_false in out nocopy ghr_history_cascade.condition_rg_type
76: );
77:
70: p_asgnei_data in out nocopy per_assignment_extra_info%rowtype);
71:
72: Procedure cascade_change( p_pre_record in ghr_pa_history%rowtype,
73: p_post_record in ghr_pa_history%rowtype,
74: p_apply_record in out nocopy ghr_pa_history%rowtype,
75: p_true_false in out nocopy ghr_history_cascade.condition_rg_type
76: );
77:
78: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
74: p_apply_record in out nocopy ghr_pa_history%rowtype,
75: p_true_false in out nocopy ghr_history_cascade.condition_rg_type
76: );
77:
78: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
79: p_post_field in ghr_pa_history.information1%type,
80: p_apply_field in out nocopy ghr_pa_history.information1%type,
81: p_result in out nocopy boolean
82: );
75: p_true_false in out nocopy ghr_history_cascade.condition_rg_type
76: );
77:
78: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
79: p_post_field in ghr_pa_history.information1%type,
80: p_apply_field in out nocopy ghr_pa_history.information1%type,
81: p_result in out nocopy boolean
82: );
83:
76: );
77:
78: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
79: p_post_field in ghr_pa_history.information1%type,
80: p_apply_field in out nocopy ghr_pa_history.information1%type,
81: p_result in out nocopy boolean
82: );
83:
84:
85: Function Stop_cascade( p_true_false ghr_history_cascade.condition_rg_type
86: ) Return boolean;
87:
88:
89: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
90: p_apply_record in out nocopy ghr_pa_history%rowtype
91: );
92:
93: Procedure Fetch_most_recent_record(
86: ) Return boolean;
87:
88:
89: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
90: p_apply_record in out nocopy ghr_pa_history%rowtype
91: );
92:
93: Procedure Fetch_most_recent_record(
94: p_table_name in varchar2,
93: Procedure Fetch_most_recent_record(
94: p_table_name in varchar2,
95: p_table_pk_id in varchar2,
96: p_person_id in number,
97: p_history_data in out nocopy ghr_pa_history%rowtype,
98: p_result_code in out nocopy varchar2
99: );
100:
101: FUNCTION cascade_pa_req_field(p_refresh_field in out nocopy varchar2,
122: -- ---------------------------------------------------------------------------
123: -- {Start Of Comments}
124: --
125: -- Description:
126: -- This procedure cascades data changes in ghr_pa_history table. If changes
127: -- have been made to a table and there were changes following it, this procedure
128: -- will correctly 'cascade' those changes to all following records.
129: --
130: -- Pre-Requisities:
157: -- ---------------------------------------------------------------------------
158:
159: Procedure Cascade_History_data ( p_table_name in varchar2,
160: p_person_id in varchar2,
161: p_pre_record in ghr_pa_history%rowtype,
162: p_post_record in ghr_pa_history%rowtype,
163: p_cascade_type in varchar2,
164: p_interv_on_table out nocopy boolean,
165: p_interv_on_eff_date out nocopy boolean,
158:
159: Procedure Cascade_History_data ( p_table_name in varchar2,
160: p_person_id in varchar2,
161: p_pre_record in ghr_pa_history%rowtype,
162: p_post_record in ghr_pa_history%rowtype,
163: p_cascade_type in varchar2,
164: p_interv_on_table out nocopy boolean,
165: p_interv_on_eff_date out nocopy boolean,
166: p_hist_data_as_of_date out nocopy ghr_pa_history%rowtype )
162: p_post_record in ghr_pa_history%rowtype,
163: p_cascade_type in varchar2,
164: p_interv_on_table out nocopy boolean,
165: p_interv_on_eff_date out nocopy boolean,
166: p_hist_data_as_of_date out nocopy ghr_pa_history%rowtype )
167: is
168:
169: l_session_var ghr_history_api.g_session_var_type;
170: l_proc varchar2(72) := 'Cascade_History_data';
167: is
168:
169: l_session_var ghr_history_api.g_session_var_type;
170: l_proc varchar2(72) := 'Cascade_History_data';
171: l_record_data ghr_pa_history%rowtype;
172: l_true_false ghr_history_cascade.condition_rg_type;
173: l_stop boolean;
174: l_num number := 1;
175: l_history_id number;
172: l_true_false ghr_history_cascade.condition_rg_type;
173: l_stop boolean;
174: l_num number := 1;
175: l_history_id number;
176: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
177: l_root_pa_request_id ghr_pa_history.pa_request_id%type;
178: l_cascade_history ghr_pa_history%rowtype;
179:
180:
173: l_stop boolean;
174: l_num number := 1;
175: l_history_id number;
176: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
177: l_root_pa_request_id ghr_pa_history.pa_request_id%type;
178: l_cascade_history ghr_pa_history%rowtype;
179:
180:
181: -- To cascade changes in the history table, we need to fetch the history_ids in the exact order
174: l_num number := 1;
175: l_history_id number;
176: l_root_pa_history_id ghr_pa_history.pa_history_id%type;
177: l_root_pa_request_id ghr_pa_history.pa_request_id%type;
178: l_cascade_history ghr_pa_history%rowtype;
179:
180:
181: -- To cascade changes in the history table, we need to fetch the history_ids in the exact order
182: -- in which changes have to be applied. For eg.
199: -- 3
200: -- 4 followed by its correction 7
201: -- To do this we first fetch all the history_ids which have null altered_pa_request_id
202: -- Then run a nested fetch to select all child records of the already fetched history_records one after the other.
203: -- cursor to fetch those history_ids from ghr_pa_history, which will be used as the root
204:
205: -- Cursor to select all root history_ids
206:
207: Cursor c_history(c_table_name varchar2,
210: c_effective_date date,
211: c_root_hist_id number)
212: is
213: select PAH.*
214: from ghr_pa_history pah
215: where
216: (table_name = c_table_name and information1 = c_information1 and nvl(person_id, -1) = nvl(c_person_id,-1)) and
217: ((effective_date = c_effective_date and
218: (( c_root_hist_id < pah.pa_history_id and pah.pa_request_id is null) or
216: (table_name = c_table_name and information1 = c_information1 and nvl(person_id, -1) = nvl(c_person_id,-1)) and
217: ((effective_date = c_effective_date and
218: (( c_root_hist_id < pah.pa_history_id and pah.pa_request_id is null) or
219: (pah.pa_request_id is not null and pah.altered_pa_request_id is null and c_root_hist_id <
220: (select min(pa_history_id) from ghr_pa_history where pa_request_id = pah.pa_request_id
221: and nature_of_action_id = pah.nature_of_action_id)) or
222: (altered_pa_request_id is not null and c_root_hist_id <
223: (select min(pa_history_id) from ghr_pa_history pah2
224: where pa_request_id = (select min(pa_request_id) from ghr_pa_requests
219: (pah.pa_request_id is not null and pah.altered_pa_request_id is null and c_root_hist_id <
220: (select min(pa_history_id) from ghr_pa_history where pa_request_id = pah.pa_request_id
221: and nature_of_action_id = pah.nature_of_action_id)) or
222: (altered_pa_request_id is not null and c_root_hist_id <
223: (select min(pa_history_id) from ghr_pa_history pah2
224: where pa_request_id = (select min(pa_request_id) from ghr_pa_requests
225: connect by pa_request_id = prior altered_pa_request_id
226: start with pa_request_id = pah.pa_request_id) and
227: nature_of_action_id = pah.nature_of_action_id and
225: connect by pa_request_id = prior altered_pa_request_id
226: start with pa_request_id = pah.pa_request_id) and
227: nature_of_action_id = pah.nature_of_action_id and
228: not exists (select 'exists'
229: from ghr_pa_history pah3
230: where pah3.pa_request_id = pah.altered_pa_request_id and
231: table_name = c_table_name and
232: information1 = c_information1
233: and pah3.nature_of_action_id = pah.nature_of_action_id)
238: ((pah.pa_request_id is null) or
239: (pah.pa_request_id is not null and pah.altered_pa_request_id is null) or
240: (pah.altered_pa_request_id is not null and not exists
241: (select 'exists'
242: from ghr_pa_history pah4
243: where pah4.pa_request_id = pah.altered_pa_request_id
244: and table_name = c_table_name
245: and information1 = c_information1
246: and pah4.nature_of_action_id = pah.nature_of_action_id)))))
255: c_noa_id number,
256: c_pa_request_id number)
257: is
258: Select pah.*
259: from ghr_pa_history pah
260: where table_name = c_table_name
261: and information1 = c_information1
262: -- and person_id = c_person_id
263: -- see comments in the cursor c_history
271: -- This cursor will retrieve the real root history id of a correction chain.
272: -- i.e. - The root NOA independent of any particular table or row change.
273: cursor get_root_hist_id(cp_pa_history_id in number) is
274: select min(pa_history_id)
275: from ghr_pa_history
276: where pa_request_id =
277: (select min(pa_request_id)
278: from ghr_pa_requests
279: connect by pa_request_id = prior altered_pa_request_id
277: (select min(pa_request_id)
278: from ghr_pa_requests
279: connect by pa_request_id = prior altered_pa_request_id
280: start with pa_request_id = (select pa_request_id
281: from ghr_pa_history
282: where pa_history_id = cp_pa_history_id))
283: AND nature_of_action_id = (select nature_of_action_id
284: from ghr_pa_history
285: where pa_history_id = cp_pa_history_id);
280: start with pa_request_id = (select pa_request_id
281: from ghr_pa_history
282: where pa_history_id = cp_pa_history_id))
283: AND nature_of_action_id = (select nature_of_action_id
284: from ghr_pa_history
285: where pa_history_id = cp_pa_history_id);
286:
287: begin
288: hr_utility.set_location('Entering '|| l_proc,5);
745: -- Procedure Cascade_Appl_table_data calls individual table cascade procedures
746: Procedure Cascade_Appl_table_data (
747: p_table_name in varchar2,
748: p_person_id in varchar2,
749: p_pre_record in ghr_pa_history%rowtype,
750: p_post_record in ghr_pa_history%rowtype,
751: p_cascade_type in varchar2,
752: p_interv_on_table in Boolean,
753: p_interv_on_eff_date in Boolean,
746: Procedure Cascade_Appl_table_data (
747: p_table_name in varchar2,
748: p_person_id in varchar2,
749: p_pre_record in ghr_pa_history%rowtype,
750: p_post_record in ghr_pa_history%rowtype,
751: p_cascade_type in varchar2,
752: p_interv_on_table in Boolean,
753: p_interv_on_eff_date in Boolean,
754: p_hist_data_as_of_date in ghr_pa_history%rowtype
750: p_post_record in ghr_pa_history%rowtype,
751: p_cascade_type in varchar2,
752: p_interv_on_table in Boolean,
753: p_interv_on_eff_date in Boolean,
754: p_hist_data_as_of_date in ghr_pa_history%rowtype
755:
756: ) is
757:
758: l_proc varchar2(30):='Cascade_appl_table_data';
870: --
871: -- {End Of Comments}
872: -- ---------------------------------------------------------------------------
873: Procedure Cascade_People (
874: p_pre_record in ghr_pa_history%rowtype,
875: p_post_record in ghr_pa_history%rowtype,
876: p_cascade_type in varchar2,
877: p_interv_on_eff_date in Boolean,
878: p_hist_data_as_of_date in ghr_pa_history%rowtype
871: -- {End Of Comments}
872: -- ---------------------------------------------------------------------------
873: Procedure Cascade_People (
874: p_pre_record in ghr_pa_history%rowtype,
875: p_post_record in ghr_pa_history%rowtype,
876: p_cascade_type in varchar2,
877: p_interv_on_eff_date in Boolean,
878: p_hist_data_as_of_date in ghr_pa_history%rowtype
879: ) is
874: p_pre_record in ghr_pa_history%rowtype,
875: p_post_record in ghr_pa_history%rowtype,
876: p_cascade_type in varchar2,
877: p_interv_on_eff_date in Boolean,
878: p_hist_data_as_of_date in ghr_pa_history%rowtype
879: ) is
880:
881: l_true_false ghr_history_cascade.condition_rg_type;
882: l_people_data per_all_people_f%rowtype;
879: ) is
880:
881: l_true_false ghr_history_cascade.condition_rg_type;
882: l_people_data per_all_people_f%rowtype;
883: l_hist_peop_data ghr_pa_history%rowtype;
884: l_stop Boolean;
885: l_proc varchar2(30):='Cascade_People';
886:
887: -- this cursor is meant to retrieve all following records in per_people_f table.
1014: -- {End Of Comments}
1015: -- ---------------------------------------------------------------------------
1016:
1017: Procedure Cascade_asgn (
1018: p_pre_record in ghr_pa_history%rowtype,
1019: p_post_record in ghr_pa_history%rowtype,
1020: p_cascade_type in varchar2,
1021: p_interv_on_eff_date in Boolean,
1022: p_hist_data_as_of_date in ghr_pa_history%rowtype
1015: -- ---------------------------------------------------------------------------
1016:
1017: Procedure Cascade_asgn (
1018: p_pre_record in ghr_pa_history%rowtype,
1019: p_post_record in ghr_pa_history%rowtype,
1020: p_cascade_type in varchar2,
1021: p_interv_on_eff_date in Boolean,
1022: p_hist_data_as_of_date in ghr_pa_history%rowtype
1023: ) is
1018: p_pre_record in ghr_pa_history%rowtype,
1019: p_post_record in ghr_pa_history%rowtype,
1020: p_cascade_type in varchar2,
1021: p_interv_on_eff_date in Boolean,
1022: p_hist_data_as_of_date in ghr_pa_history%rowtype
1023: ) is
1024:
1025: l_true_false ghr_history_cascade.condition_rg_type;
1026: l_asgn_data per_all_assignments_f%rowtype;
1023: ) is
1024:
1025: l_true_false ghr_history_cascade.condition_rg_type;
1026: l_asgn_data per_all_assignments_f%rowtype;
1027: l_hist_asgn_data ghr_pa_history%rowtype;
1028: l_stop Boolean;
1029: l_proc varchar2(30):='Cascade_asgn';
1030:
1031: -- this cursor is meant to retrieve all following records in per_assignments_f table.
1158: --
1159: -- {End Of Comments}
1160: -- ---------------------------------------------------------------------------
1161: Procedure Cascade_peopleei (
1162: p_post_record in ghr_pa_history%rowtype
1163: ) is
1164:
1165: l_hist_peopleei_data ghr_pa_history%rowtype;
1166: l_peopleei_data per_people_extra_info%rowtype;
1161: Procedure Cascade_peopleei (
1162: p_post_record in ghr_pa_history%rowtype
1163: ) is
1164:
1165: l_hist_peopleei_data ghr_pa_history%rowtype;
1166: l_peopleei_data per_people_extra_info%rowtype;
1167: l_result_code varchar2(30);
1168: l_proc varchar2(30):='Cascade_peopleei';
1169:
1234: -- {End Of Comments}
1235: -- ---------------------------------------------------------------------------
1236:
1237: Procedure Cascade_asgnei (
1238: p_post_record in ghr_pa_history%rowtype
1239: ) is
1240:
1241: l_hist_asgnei_data ghr_pa_history%rowtype;
1242: l_asgnei_data per_assignment_extra_info%rowtype;
1237: Procedure Cascade_asgnei (
1238: p_post_record in ghr_pa_history%rowtype
1239: ) is
1240:
1241: l_hist_asgnei_data ghr_pa_history%rowtype;
1242: l_asgnei_data per_assignment_extra_info%rowtype;
1243: l_result_code varchar2(30);
1244: l_proc varchar2(30):='Cascade_asgnei';
1245:
1307: --
1308: -- {End Of Comments}
1309: -- ---------------------------------------------------------------------------
1310: Procedure Cascade_posnei (
1311: p_post_record in ghr_pa_history%rowtype) is
1312:
1313: l_hist_posnei_data ghr_pa_history%rowtype;
1314: l_posnei_data per_position_extra_info%rowtype;
1315: l_result_code varchar2(30);
1309: -- ---------------------------------------------------------------------------
1310: Procedure Cascade_posnei (
1311: p_post_record in ghr_pa_history%rowtype) is
1312:
1313: l_hist_posnei_data ghr_pa_history%rowtype;
1314: l_posnei_data per_position_extra_info%rowtype;
1315: l_result_code varchar2(30);
1316: l_proc varchar2(30):='Cascade_posnei';
1317:
1379: --
1380: -- {End Of Comments}
1381: -- ---------------------------------------------------------------------------
1382: Procedure Cascade_posn (
1383: p_post_record in ghr_pa_history%rowtype) is
1384:
1385: l_hist_posn_data ghr_pa_history%rowtype;
1386: l_posn_data hr_all_positions_f%rowtype;
1387: l_result_code varchar2(30);
1381: -- ---------------------------------------------------------------------------
1382: Procedure Cascade_posn (
1383: p_post_record in ghr_pa_history%rowtype) is
1384:
1385: l_hist_posn_data ghr_pa_history%rowtype;
1386: l_posn_data hr_all_positions_f%rowtype;
1387: l_result_code varchar2(30);
1388: l_proc varchar2(30):='Cascade_posn';
1389:
1459: -- {End Of Comments}
1460: -- ---------------------------------------------------------------------------
1461:
1462: Procedure Cascade_posn (
1463: p_pre_record in ghr_pa_history%rowtype,
1464: p_post_record in ghr_pa_history%rowtype,
1465: p_cascade_type in varchar2,
1466: p_interv_on_eff_date in Boolean,
1467: p_hist_data_as_of_date in ghr_pa_history%rowtype
1460: -- ---------------------------------------------------------------------------
1461:
1462: Procedure Cascade_posn (
1463: p_pre_record in ghr_pa_history%rowtype,
1464: p_post_record in ghr_pa_history%rowtype,
1465: p_cascade_type in varchar2,
1466: p_interv_on_eff_date in Boolean,
1467: p_hist_data_as_of_date in ghr_pa_history%rowtype
1468: ) is
1463: p_pre_record in ghr_pa_history%rowtype,
1464: p_post_record in ghr_pa_history%rowtype,
1465: p_cascade_type in varchar2,
1466: p_interv_on_eff_date in Boolean,
1467: p_hist_data_as_of_date in ghr_pa_history%rowtype
1468: ) is
1469:
1470: l_true_false ghr_history_cascade.condition_rg_type;
1471: l_posn_data hr_all_positions_f%rowtype;
1468: ) is
1469:
1470: l_true_false ghr_history_cascade.condition_rg_type;
1471: l_posn_data hr_all_positions_f%rowtype;
1472: l_hist_posn_data ghr_pa_history%rowtype;
1473: l_stop Boolean;
1474: l_proc varchar2(30):='Cascade_posn';
1475:
1476: -- this cursor is meant to retrieve all following records in per_assignments_f table.
1603: --
1604: -- {End Of Comments}
1605: -- ---------------------------------------------------------------------------
1606: Procedure Cascade_addresses (
1607: p_post_record in ghr_pa_history%rowtype) is
1608:
1609: l_hist_addresses_data ghr_pa_history%rowtype;
1610: l_addresses_data per_addresses%rowtype;
1611: l_result_code varchar2(30);
1605: -- ---------------------------------------------------------------------------
1606: Procedure Cascade_addresses (
1607: p_post_record in ghr_pa_history%rowtype) is
1608:
1609: l_hist_addresses_data ghr_pa_history%rowtype;
1610: l_addresses_data per_addresses%rowtype;
1611: l_result_code varchar2(30);
1612: l_proc varchar2(30):='Cascade_addresses';
1613:
1674: --
1675: -- {End Of Comments}
1676: -- ---------------------------------------------------------------------------
1677: Procedure Cascade_perana (
1678: p_post_record in ghr_pa_history%rowtype) is
1679:
1680: l_hist_perana_data ghr_pa_history%rowtype;
1681: l_perana_data per_person_analyses%rowtype;
1682: l_result_code varchar2(30);
1676: -- ---------------------------------------------------------------------------
1677: Procedure Cascade_perana (
1678: p_post_record in ghr_pa_history%rowtype) is
1679:
1680: l_hist_perana_data ghr_pa_history%rowtype;
1681: l_perana_data per_person_analyses%rowtype;
1682: l_result_code varchar2(30);
1683: l_proc varchar2(30):='Cascade_perana';
1684:
2941: -- Internal Development Use Only.
2942: --
2943: -- {End Of Comments}
2944: -- ---------------------------------------------------------------------------
2945: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2946: p_post_record in ghr_pa_history%rowtype,
2947: p_apply_record in out nocopy ghr_pa_history%rowtype,
2948: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2949: is
2942: --
2943: -- {End Of Comments}
2944: -- ---------------------------------------------------------------------------
2945: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2946: p_post_record in ghr_pa_history%rowtype,
2947: p_apply_record in out nocopy ghr_pa_history%rowtype,
2948: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2949: is
2950:
2943: -- {End Of Comments}
2944: -- ---------------------------------------------------------------------------
2945: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2946: p_post_record in ghr_pa_history%rowtype,
2947: p_apply_record in out nocopy ghr_pa_history%rowtype,
2948: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2949: is
2950:
2951: l_apply_record ghr_pa_history%rowtype;
2947: p_apply_record in out nocopy ghr_pa_history%rowtype,
2948: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2949: is
2950:
2951: l_apply_record ghr_pa_history%rowtype;
2952: l_true_false ghr_history_cascade.condition_rg_type;
2953: l_proc varchar2(72) := 'cascade_change';
2954:
2955: Begin
3928: -- Internal Development Use Only.
3929: --
3930: -- {End Of Comments}
3931: -- ---------------------------------------------------------------------------
3932: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3933: p_post_field in ghr_pa_history.information1%type,
3934: p_apply_field in out nocopy ghr_pa_history.information1%type,
3935: p_result in out nocopy boolean )
3936: is
3929: --
3930: -- {End Of Comments}
3931: -- ---------------------------------------------------------------------------
3932: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3933: p_post_field in ghr_pa_history.information1%type,
3934: p_apply_field in out nocopy ghr_pa_history.information1%type,
3935: p_result in out nocopy boolean )
3936: is
3937:
3930: -- {End Of Comments}
3931: -- ---------------------------------------------------------------------------
3932: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3933: p_post_field in ghr_pa_history.information1%type,
3934: p_apply_field in out nocopy ghr_pa_history.information1%type,
3935: p_result in out nocopy boolean )
3936: is
3937:
3938: l_apply_field ghr_pa_history.information1%type;
3934: p_apply_field in out nocopy ghr_pa_history.information1%type,
3935: p_result in out nocopy boolean )
3936: is
3937:
3938: l_apply_field ghr_pa_history.information1%type;
3939: l_result boolean;
3940: l_proc varchar2(72) := 'cascade_field_value';
3941: begin
3942:
4068: --
4069: -- {End Of Comments}
4070: -- ---------------------------------------------------------------------------
4071:
4072: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4073: p_apply_record in out nocopy ghr_pa_history%rowtype )
4074: is
4075:
4076: l_apply_record ghr_pa_history%rowtype;
4069: -- {End Of Comments}
4070: -- ---------------------------------------------------------------------------
4071:
4072: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4073: p_apply_record in out nocopy ghr_pa_history%rowtype )
4074: is
4075:
4076: l_apply_record ghr_pa_history%rowtype;
4077: l_proc varchar2(72) := 'cascade_dependencies';
4072: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4073: p_apply_record in out nocopy ghr_pa_history%rowtype )
4074: is
4075:
4076: l_apply_record ghr_pa_history%rowtype;
4077: l_proc varchar2(72) := 'cascade_dependencies';
4078: begin
4079:
4080: -- Since information102 thru' 125 are always dependent on information101,
4137: -- procedure succeeded. Otherwise,
4138: -- contains error message.
4139: --
4140: -- Post Success:
4141: -- Row will have been fetched from ghr_pa_history.
4142: --
4143: -- Post Failure:
4144: -- p_result_code will contain failure message.
4145: --
4154: Procedure Fetch_most_recent_record(
4155: p_table_name in varchar2,
4156: p_table_pk_id in varchar2,
4157: p_person_id in number,
4158: p_history_data in out nocopy ghr_pa_history%rowtype,
4159: p_result_code in out nocopy varchar2
4160: ) is
4161:
4162: l_history_data ghr_pa_history%rowtype;
4158: p_history_data in out nocopy ghr_pa_history%rowtype,
4159: p_result_code in out nocopy varchar2
4160: ) is
4161:
4162: l_history_data ghr_pa_history%rowtype;
4163: l_result_code varchar2(200);
4164: l_date_effective date:=sysdate;
4165: l_proc varchar2(30):='fetch_most_recent_record';
4166: