[Home] [Help]
2: /* $Header: ghcascad.pkb 120.4.12020000.2 2012/12/14 04:10:53 vmididho 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);
300: For rowno in 7..101 loop -- Bug 1161542 changed 4..101 to 7..101
301: l_true_false(rowno) := TRUE;
302: End loop;
303:
304: --BUG # 14625196 Added as lastname need to be updated in information6 of GHR_PA_HISTORY
305: IF p_table_name = 'PER_PEOPLE_F' THEN
306: l_true_false(6) := TRUE;
307: END IF;
308:
751: -- Procedure Cascade_Appl_table_data calls individual table cascade procedures
752: Procedure Cascade_Appl_table_data (
753: p_table_name in varchar2,
754: p_person_id in varchar2,
755: p_pre_record in ghr_pa_history%rowtype,
756: p_post_record in ghr_pa_history%rowtype,
757: p_cascade_type in varchar2,
758: p_interv_on_table in Boolean,
759: p_interv_on_eff_date in Boolean,
752: Procedure Cascade_Appl_table_data (
753: p_table_name in varchar2,
754: p_person_id in varchar2,
755: p_pre_record in ghr_pa_history%rowtype,
756: p_post_record in ghr_pa_history%rowtype,
757: p_cascade_type in varchar2,
758: p_interv_on_table in Boolean,
759: p_interv_on_eff_date in Boolean,
760: p_hist_data_as_of_date in ghr_pa_history%rowtype
756: p_post_record in ghr_pa_history%rowtype,
757: p_cascade_type in varchar2,
758: p_interv_on_table in Boolean,
759: p_interv_on_eff_date in Boolean,
760: p_hist_data_as_of_date in ghr_pa_history%rowtype
761:
762: ) is
763:
764: l_proc varchar2(30):='Cascade_appl_table_data';
876: --
877: -- {End Of Comments}
878: -- ---------------------------------------------------------------------------
879: Procedure Cascade_People (
880: p_pre_record in ghr_pa_history%rowtype,
881: p_post_record in ghr_pa_history%rowtype,
882: p_cascade_type in varchar2,
883: p_interv_on_eff_date in Boolean,
884: p_hist_data_as_of_date in ghr_pa_history%rowtype
877: -- {End Of Comments}
878: -- ---------------------------------------------------------------------------
879: Procedure Cascade_People (
880: p_pre_record in ghr_pa_history%rowtype,
881: p_post_record in ghr_pa_history%rowtype,
882: p_cascade_type in varchar2,
883: p_interv_on_eff_date in Boolean,
884: p_hist_data_as_of_date in ghr_pa_history%rowtype
885: ) is
880: p_pre_record in ghr_pa_history%rowtype,
881: p_post_record in ghr_pa_history%rowtype,
882: p_cascade_type in varchar2,
883: p_interv_on_eff_date in Boolean,
884: p_hist_data_as_of_date in ghr_pa_history%rowtype
885: ) is
886:
887: l_true_false ghr_history_cascade.condition_rg_type;
888: l_people_data per_all_people_f%rowtype;
885: ) is
886:
887: l_true_false ghr_history_cascade.condition_rg_type;
888: l_people_data per_all_people_f%rowtype;
889: l_hist_peop_data ghr_pa_history%rowtype;
890: l_stop Boolean;
891: l_proc varchar2(30):='Cascade_People';
892:
893: -- this cursor is meant to retrieve all following records in per_people_f table.
1020: -- {End Of Comments}
1021: -- ---------------------------------------------------------------------------
1022:
1023: Procedure Cascade_asgn (
1024: p_pre_record in ghr_pa_history%rowtype,
1025: p_post_record in ghr_pa_history%rowtype,
1026: p_cascade_type in varchar2,
1027: p_interv_on_eff_date in Boolean,
1028: p_hist_data_as_of_date in ghr_pa_history%rowtype
1021: -- ---------------------------------------------------------------------------
1022:
1023: Procedure Cascade_asgn (
1024: p_pre_record in ghr_pa_history%rowtype,
1025: p_post_record in ghr_pa_history%rowtype,
1026: p_cascade_type in varchar2,
1027: p_interv_on_eff_date in Boolean,
1028: p_hist_data_as_of_date in ghr_pa_history%rowtype
1029: ) is
1024: p_pre_record in ghr_pa_history%rowtype,
1025: p_post_record in ghr_pa_history%rowtype,
1026: p_cascade_type in varchar2,
1027: p_interv_on_eff_date in Boolean,
1028: p_hist_data_as_of_date in ghr_pa_history%rowtype
1029: ) is
1030:
1031: l_true_false ghr_history_cascade.condition_rg_type;
1032: l_asgn_data per_all_assignments_f%rowtype;
1029: ) is
1030:
1031: l_true_false ghr_history_cascade.condition_rg_type;
1032: l_asgn_data per_all_assignments_f%rowtype;
1033: l_hist_asgn_data ghr_pa_history%rowtype;
1034: l_stop Boolean;
1035: l_proc varchar2(30):='Cascade_asgn';
1036:
1037: -- this cursor is meant to retrieve all following records in per_assignments_f table.
1164: --
1165: -- {End Of Comments}
1166: -- ---------------------------------------------------------------------------
1167: Procedure Cascade_peopleei (
1168: p_post_record in ghr_pa_history%rowtype
1169: ) is
1170:
1171: l_hist_peopleei_data ghr_pa_history%rowtype;
1172: l_peopleei_data per_people_extra_info%rowtype;
1167: Procedure Cascade_peopleei (
1168: p_post_record in ghr_pa_history%rowtype
1169: ) is
1170:
1171: l_hist_peopleei_data ghr_pa_history%rowtype;
1172: l_peopleei_data per_people_extra_info%rowtype;
1173: l_result_code varchar2(30);
1174: l_proc varchar2(30):='Cascade_peopleei';
1175:
1240: -- {End Of Comments}
1241: -- ---------------------------------------------------------------------------
1242:
1243: Procedure Cascade_asgnei (
1244: p_post_record in ghr_pa_history%rowtype
1245: ) is
1246:
1247: l_hist_asgnei_data ghr_pa_history%rowtype;
1248: l_asgnei_data per_assignment_extra_info%rowtype;
1243: Procedure Cascade_asgnei (
1244: p_post_record in ghr_pa_history%rowtype
1245: ) is
1246:
1247: l_hist_asgnei_data ghr_pa_history%rowtype;
1248: l_asgnei_data per_assignment_extra_info%rowtype;
1249: l_result_code varchar2(30);
1250: l_proc varchar2(30):='Cascade_asgnei';
1251:
1313: --
1314: -- {End Of Comments}
1315: -- ---------------------------------------------------------------------------
1316: Procedure Cascade_posnei (
1317: p_post_record in ghr_pa_history%rowtype) is
1318:
1319: l_hist_posnei_data ghr_pa_history%rowtype;
1320: l_posnei_data per_position_extra_info%rowtype;
1321: l_result_code varchar2(30);
1315: -- ---------------------------------------------------------------------------
1316: Procedure Cascade_posnei (
1317: p_post_record in ghr_pa_history%rowtype) is
1318:
1319: l_hist_posnei_data ghr_pa_history%rowtype;
1320: l_posnei_data per_position_extra_info%rowtype;
1321: l_result_code varchar2(30);
1322: l_proc varchar2(30):='Cascade_posnei';
1323:
1403: --
1404: -- {End Of Comments}
1405: -- ---------------------------------------------------------------------------
1406: Procedure Cascade_posn (
1407: p_post_record in ghr_pa_history%rowtype) is
1408:
1409: l_hist_posn_data ghr_pa_history%rowtype;
1410: l_posn_data hr_all_positions_f%rowtype;
1411: l_result_code varchar2(30);
1405: -- ---------------------------------------------------------------------------
1406: Procedure Cascade_posn (
1407: p_post_record in ghr_pa_history%rowtype) is
1408:
1409: l_hist_posn_data ghr_pa_history%rowtype;
1410: l_posn_data hr_all_positions_f%rowtype;
1411: l_result_code varchar2(30);
1412: l_proc varchar2(30):='Cascade_posn';
1413:
1482: -- {End Of Comments}
1483: -- ---------------------------------------------------------------------------
1484:
1485: Procedure Cascade_posn (
1486: p_pre_record in ghr_pa_history%rowtype,
1487: p_post_record in ghr_pa_history%rowtype,
1488: p_cascade_type in varchar2,
1489: p_interv_on_eff_date in Boolean,
1490: p_hist_data_as_of_date in ghr_pa_history%rowtype
1483: -- ---------------------------------------------------------------------------
1484:
1485: Procedure Cascade_posn (
1486: p_pre_record in ghr_pa_history%rowtype,
1487: p_post_record in ghr_pa_history%rowtype,
1488: p_cascade_type in varchar2,
1489: p_interv_on_eff_date in Boolean,
1490: p_hist_data_as_of_date in ghr_pa_history%rowtype
1491: ) is
1486: p_pre_record in ghr_pa_history%rowtype,
1487: p_post_record in ghr_pa_history%rowtype,
1488: p_cascade_type in varchar2,
1489: p_interv_on_eff_date in Boolean,
1490: p_hist_data_as_of_date in ghr_pa_history%rowtype
1491: ) is
1492:
1493: l_true_false ghr_history_cascade.condition_rg_type;
1494: l_posn_data hr_all_positions_f%rowtype;
1491: ) is
1492:
1493: l_true_false ghr_history_cascade.condition_rg_type;
1494: l_posn_data hr_all_positions_f%rowtype;
1495: l_hist_posn_data ghr_pa_history%rowtype;
1496: l_stop Boolean;
1497: l_proc varchar2(30):='Cascade_posn';
1498:
1499: -- this cursor is meant to retrieve all following records in per_assignments_f table.
1626: --
1627: -- {End Of Comments}
1628: -- ---------------------------------------------------------------------------
1629: Procedure Cascade_addresses (
1630: p_post_record in ghr_pa_history%rowtype) is
1631:
1632: l_hist_addresses_data ghr_pa_history%rowtype;
1633: l_addresses_data per_addresses%rowtype;
1634: l_result_code varchar2(30);
1628: -- ---------------------------------------------------------------------------
1629: Procedure Cascade_addresses (
1630: p_post_record in ghr_pa_history%rowtype) is
1631:
1632: l_hist_addresses_data ghr_pa_history%rowtype;
1633: l_addresses_data per_addresses%rowtype;
1634: l_result_code varchar2(30);
1635: l_proc varchar2(30):='Cascade_addresses';
1636:
1697: --
1698: -- {End Of Comments}
1699: -- ---------------------------------------------------------------------------
1700: Procedure Cascade_perana (
1701: p_post_record in ghr_pa_history%rowtype) is
1702:
1703: l_hist_perana_data ghr_pa_history%rowtype;
1704: l_perana_data per_person_analyses%rowtype;
1705: l_result_code varchar2(30);
1699: -- ---------------------------------------------------------------------------
1700: Procedure Cascade_perana (
1701: p_post_record in ghr_pa_history%rowtype) is
1702:
1703: l_hist_perana_data ghr_pa_history%rowtype;
1704: l_perana_data per_person_analyses%rowtype;
1705: l_result_code varchar2(30);
1706: l_proc varchar2(30):='Cascade_perana';
1707:
2967: -- Internal Development Use Only.
2968: --
2969: -- {End Of Comments}
2970: -- ---------------------------------------------------------------------------
2971: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2972: p_post_record in ghr_pa_history%rowtype,
2973: p_apply_record in out nocopy ghr_pa_history%rowtype,
2974: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2975: is
2968: --
2969: -- {End Of Comments}
2970: -- ---------------------------------------------------------------------------
2971: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2972: p_post_record in ghr_pa_history%rowtype,
2973: p_apply_record in out nocopy ghr_pa_history%rowtype,
2974: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2975: is
2976:
2969: -- {End Of Comments}
2970: -- ---------------------------------------------------------------------------
2971: Procedure cascade_change(p_pre_record in ghr_pa_history%rowtype,
2972: p_post_record in ghr_pa_history%rowtype,
2973: p_apply_record in out nocopy ghr_pa_history%rowtype,
2974: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2975: is
2976:
2977: l_apply_record ghr_pa_history%rowtype;
2973: p_apply_record in out nocopy ghr_pa_history%rowtype,
2974: p_true_false in out nocopy ghr_history_cascade.condition_rg_type)
2975: is
2976:
2977: l_apply_record ghr_pa_history%rowtype;
2978: l_true_false ghr_history_cascade.condition_rg_type;
2979: l_proc varchar2(72) := 'cascade_change';
2980:
2981: Begin
3007:
3008: hr_utility.set_location(l_proc,20);
3009:
3010: --BUG # 14625196 Added as lastname need to be updated and last name is stored
3011: -- in information6 of GHR_PA_HISTORY. with out this last name not getting
3012: -- updated in PER_ALL_PEOPLE_F
3013:
3014: IF UPPER(p_post_record.table_name) = ghr_history_api.g_peop_table THEN
3015: cascade_field_value
3961: -- Internal Development Use Only.
3962: --
3963: -- {End Of Comments}
3964: -- ---------------------------------------------------------------------------
3965: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3966: p_post_field in ghr_pa_history.information1%type,
3967: p_apply_field in out nocopy ghr_pa_history.information1%type,
3968: p_result in out nocopy boolean )
3969: is
3962: --
3963: -- {End Of Comments}
3964: -- ---------------------------------------------------------------------------
3965: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3966: p_post_field in ghr_pa_history.information1%type,
3967: p_apply_field in out nocopy ghr_pa_history.information1%type,
3968: p_result in out nocopy boolean )
3969: is
3970:
3963: -- {End Of Comments}
3964: -- ---------------------------------------------------------------------------
3965: Procedure cascade_field_value( p_pre_field in ghr_pa_history.information1%type,
3966: p_post_field in ghr_pa_history.information1%type,
3967: p_apply_field in out nocopy ghr_pa_history.information1%type,
3968: p_result in out nocopy boolean )
3969: is
3970:
3971: l_apply_field ghr_pa_history.information1%type;
3967: p_apply_field in out nocopy ghr_pa_history.information1%type,
3968: p_result in out nocopy boolean )
3969: is
3970:
3971: l_apply_field ghr_pa_history.information1%type;
3972: l_result boolean;
3973: l_proc varchar2(72) := 'cascade_field_value';
3974: begin
3975:
4101: --
4102: -- {End Of Comments}
4103: -- ---------------------------------------------------------------------------
4104:
4105: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4106: p_apply_record in out nocopy ghr_pa_history%rowtype )
4107: is
4108:
4109: l_apply_record ghr_pa_history%rowtype;
4102: -- {End Of Comments}
4103: -- ---------------------------------------------------------------------------
4104:
4105: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4106: p_apply_record in out nocopy ghr_pa_history%rowtype )
4107: is
4108:
4109: l_apply_record ghr_pa_history%rowtype;
4110: l_proc varchar2(72) := 'cascade_dependencies';
4105: Procedure cascade_dependencies( p_record in ghr_pa_history%rowtype,
4106: p_apply_record in out nocopy ghr_pa_history%rowtype )
4107: is
4108:
4109: l_apply_record ghr_pa_history%rowtype;
4110: l_proc varchar2(72) := 'cascade_dependencies';
4111: begin
4112:
4113: -- Since information102 thru' 125 are always dependent on information101,
4170: -- procedure succeeded. Otherwise,
4171: -- contains error message.
4172: --
4173: -- Post Success:
4174: -- Row will have been fetched from ghr_pa_history.
4175: --
4176: -- Post Failure:
4177: -- p_result_code will contain failure message.
4178: --
4187: Procedure Fetch_most_recent_record(
4188: p_table_name in varchar2,
4189: p_table_pk_id in varchar2,
4190: p_person_id in number,
4191: p_history_data in out nocopy ghr_pa_history%rowtype,
4192: p_result_code in out nocopy varchar2
4193: ) is
4194:
4195: l_history_data ghr_pa_history%rowtype;
4191: p_history_data in out nocopy ghr_pa_history%rowtype,
4192: p_result_code in out nocopy varchar2
4193: ) is
4194:
4195: l_history_data ghr_pa_history%rowtype;
4196: l_result_code varchar2(200);
4197: l_date_effective date:=sysdate;
4198: l_proc varchar2(30):='fetch_most_recent_record';
4199: