[Home] [Help]
PACKAGE BODY: APPS.GHR_HISTORY_API
Source
1 Package Body GHR_HISTORY_API as
2 /* $Header: ghpahapi.pkb 120.0.12010000.2 2008/10/17 06:20:36 vmididho ship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- |--------------------------< <Ghr_History_API> >--------------------------|
7 -- ----------------------------------------------------------------------------
8 --
9 -- Package Global variables ---
10
11 g_session_var g_session_var_type;
12 g_pre_update pa_history_type;
13 g_operation_info table_operation_info_type;
14
15
16 -- End Package global variables
17
18 -- ---------------------------------------------------------------------------
19 -- |--------------------------< set_g_session_var >---------------------------|
20 -- ---------------------------------------------------------------------------
21 -- {Start Of Comments}
22 --
23 -- Description:
24 -- This procedure sets the global session variable g_session_var to
25 -- the values passed.
26 --
27 -- Pre-Requisities:
28 -- None.
29 --
30 -- In Parameters:
31 -- In Paramaters correspond to the like named columns in the g_session_var global
32 -- variable. See the definition of the global for details.
33 --
34 -- Post Success:
35 -- All passed paramaters will have been set in g_session_var.
36 --
37 -- Post Failure:
38 -- No failure conditions.
39 --
40 -- Developer Implementation Notes:
41 -- None
42 --
43 -- Access Status:
44 -- Internal Development Use Only.
45 --
46 -- {End Of Comments}
47 -- ---------------------------------------------------------------------------
48
49 Procedure set_g_session_var( p_pa_request_id in number default null,
50 p_noa_id in number default null,
51 p_altered_pa_request_id in number default null,
52 p_noa_id_correct in number default null,
53 p_person_id in number default null,
54 p_assignment_id in number default null,
55 p_date_effective in date default null)
56 is
57 l_proc varchar2(30):='set_g_session_var';
58 begin
59 hr_utility.set_location('Entering:'|| l_proc, 5);
60 g_session_var.pa_request_id := nvl(p_pa_request_id, g_session_var.pa_request_id);
61 g_session_var.noa_id := nvl(p_noa_id,g_session_var.noa_id);
62 g_session_var.altered_pa_request_id := nvl(p_altered_pa_request_id, g_session_var.altered_pa_request_id);
63 g_session_var.noa_id_correct := nvl(p_noa_id_correct, g_session_var.noa_id_correct);
64 g_session_var.person_id := nvl(p_person_id, g_session_var.person_id);
65 g_session_var.assignment_id := nvl(p_assignment_id, g_session_var.assignment_id);
66 g_session_var.date_effective := nvl(p_date_effective, g_session_var.date_effective);
67 hr_utility.set_location(' Leaving:'||l_proc, 10);
68 end set_g_session_var;
69
70 -- ---------------------------------------------------------------------------
71 -- |--------------------------< get_g_session_var >---------------------------|
72 -- ---------------------------------------------------------------------------
73 -- {Start Of Comments}
74 --
75 -- Description:
76 -- This procedure gets the global session variable g_session_var into
77 -- the passed paramaters.
78 --
79 -- Pre-Requisities:
80 -- None.
81 --
82 -- In Parameters:
83 -- In Paramaters correspond to the like named columns in the g_session_var global
84 -- variable. See the definition of the global for details.
85 --
86 -- Post Success:
87 -- All passed parameters will contain the corresponding values from g_session_var.
88 --
89 -- Post Failure:
90 -- No failure conditions.
91 --
92 -- Developer Implementation Notes:
93 -- None
94 --
95 -- Access Status:
96 -- Internal Development Use Only.
97 --
98 -- {End Of Comments}
99 -- ---------------------------------------------------------------------------
100
101 Procedure get_g_session_var( p_pa_request_id out nocopy number,
102 p_noa_id out nocopy number,
103 p_altered_pa_request_id out nocopy number,
104 p_noa_id_correct out nocopy number,
105 p_person_id out nocopy number,
106 p_assignment_id out nocopy number,
107 p_date_effective out nocopy date) is
108
109 l_proc varchar2(30):='get_g_session_var';
110 begin
111 hr_utility.set_location('Entering:'|| l_proc, 5);
112 p_pa_request_id := g_session_var.pa_request_id;
113 p_noa_id := g_session_var.noa_id;
114 p_altered_pa_request_id := g_session_var.altered_pa_request_id;
115 p_noa_id_correct := g_session_var.noa_id_correct;
116 p_person_id := g_session_var.person_id;
117 p_assignment_id := g_session_var.assignment_id;
118 p_date_effective := g_session_var.date_effective;
119 hr_utility.set_location(' Leaving:'||l_proc, 10);
120 exception when others then
121 --
122 -- Reset IN OUT parameters and set OUT parameters
123 --
124 p_pa_request_id := null;
125 p_noa_id := null;
126 p_altered_pa_request_id := null;
127 p_noa_id_correct := null;
128 p_person_id := null;
129 p_assignment_id := null;
130 p_date_effective := null;
131 raise;
132 end get_g_session_var;
133
134 -- ---------------------------------------------------------------------------
135 -- |--------------------------< set_g_session_var >---------------------------|
136 -- ---------------------------------------------------------------------------
137 -- {Start Of Comments}
138 --
139 -- Description:
140 -- This procedure sets the global session variable g_session_var to
141 -- the values passed.
142 --
143 -- Pre-Requisities:
144 -- None.
145 --
146 -- In Parameters:
147 -- p_session_var -> g_session_var_type contains values the g_session_var
148 -- should be set to.
149 --
150 -- Post Success:
151 -- g_session_var will have been set to corresponding values received in p_session_var.
152 --
153 -- Post Failure:
154 -- No failure conditions.
155 --
156 -- Developer Implementation Notes:
157 -- None
158 --
159 -- Access Status:
160 -- Internal Development Use Only.
161 --
162 -- {End Of Comments}
163 -- ---------------------------------------------------------------------------
164
165 Procedure set_g_session_var( p_session_var in g_session_var_type) is
166 l_proc varchar2(30):='set_g_session_var 2';
167 begin
168 hr_utility.set_location('Entering:'|| l_proc, 5);
169 g_session_var.pa_request_id := nvl( p_session_var.pa_request_id, g_session_var.pa_request_id);
170 g_session_var.noa_id := nvl(p_session_var.noa_id, g_session_var.noa_id);
171 g_session_var.altered_pa_request_id := nvl(p_session_var.altered_pa_request_id, g_session_var.altered_pa_request_id);
172 g_session_var.noa_id_correct := nvl(p_session_var.noa_id_correct, g_session_var.noa_id_correct);
173 g_session_var.person_id := p_session_var.person_id;
174 g_session_var.assignment_id := p_session_var.assignment_id;
175 g_session_var.program_name := p_session_var.program_name;
176 g_session_var.fire_trigger := p_session_var.fire_trigger;
177 g_session_var.date_effective := nvl(p_session_var.date_effective, g_session_var.date_effective);
178 g_session_var.pa_history_id := p_session_var.pa_history_id;
179 hr_utility.set_location(' Leaving:'||l_proc, 10);
180 end set_g_session_var;
181
182 -- ---------------------------------------------------------------------------
183 -- |--------------------------< get_g_session_var >---------------------------|
184 -- ---------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
187 -- Description:
188 -- This procedure gets the global session variable g_session_var into
189 -- the passed p_session_var.
190 --
191 -- Pre-Requisities:
192 -- None.
193 --
194 -- In Parameters:
195 -- p_session_var -> g_session_var_type that will be populated with
196 -- from g_session_var.
197 --
198 -- Post Success:
199 -- p_session_var will contain the corresponding values from g_session_var.
200 --
201 -- Post Failure:
202 -- No failure conditions.
203 --
204 -- Developer Implementation Notes:
205 -- None
206 --
207 -- Access Status:
208 -- Internal Development Use Only.
209 --
210 -- {End Of Comments}
211 -- ---------------------------------------------------------------------------
212
213 Procedure get_g_session_var( p_session_var out nocopy g_session_var_type) is
214 l_proc varchar2(30):='get_g_session_var 2';
215 begin
216 hr_utility.set_location('Entering:'|| l_proc, 5);
217 p_session_var.pa_request_id := g_session_var.pa_request_id;
218 p_session_var.noa_id := g_session_var.noa_id;
219 p_session_var.altered_pa_request_id := g_session_var.altered_pa_request_id;
220 p_session_var.noa_id_correct := g_session_var.noa_id_correct;
221 p_session_var.person_id := g_session_var.person_id;
222 p_session_var.assignment_id := g_session_var.assignment_id;
223 p_session_var.program_name := g_session_var.program_name;
224 p_session_var.fire_trigger := g_session_var.fire_trigger;
225 p_session_var.date_effective := g_session_var.date_effective;
226 p_session_var.pa_history_id := g_session_var.pa_history_id;
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 exception when others then
229 --
230 -- Reset IN OUT parameters and set OUT parameters
231 --
232 p_session_var := null;
233 raise;
234 end get_g_session_var;
235
236 -- ---------------------------------------------------------------------------
237 -- |--------------------------< set_operation_info >--------------------------|
238 -- ---------------------------------------------------------------------------
239 -- {Start Of Comments}
240 --
241 -- Description:
242 -- This procedure tracks the fact that a given database row was touched in
243 -- this session. This is meant to be called from database triggers for the
244 -- tables that OGHR is concerned with tracking.
245 -- If the row has not already been touched, we add it to the global session var
246 -- g_operation_info rg. If it has already been touched, it is not necessary to add
247 -- as it should already have been added.
248 -- Inserts are handled differently than updates as we are also tracking the pre-values
249 -- for the row in the case of update.
250 -- Note that this information is being stored in global session variables so that
251 -- it can subsequently be stored in GHR_PA_HISTORY when this action is complete (see
252 -- procedure post_update_process).
253 --
254 -- Pre-Requisities:
255 -- None.
256 --
257 -- In Parameters:
258 -- p_program_name -> program from which we are calling (either 'core' or 'sf50').
259 -- p_date_Effective -> effective date of the action.
260 -- p_table_name -> name of the table that this row is from.
261 -- p_table_pk_id -> primary key of the row.
262 -- p_operation -> dml operation being performed on the row (insert, update, delete).
263 -- p_old_record_data -> row data prior to this dml operation.
264 -- p_row_id -> rowid of this row.
265 --
266 -- Post Success:
267 -- Historical data about the dml operation being performed on this row will have been
268 -- noted in the global session vars.
269 --
270 -- Post Failure:
271 -- The program name passed is not supported.
272 --
273 -- Developer Implementation Notes:
274 -- None
275 --
276 -- Access Status:
277 -- Internal Development Use Only.
278 --
279 -- {End Of Comments}
280 -- ---------------------------------------------------------------------------
281 Procedure set_operation_info(
282 p_program_name in varchar2 ,
283 p_date_Effective in date ,
284 p_table_name in ghr_pa_history.table_name%type ,
285 p_table_pk_id in ghr_pa_history.information1%type ,
286 p_operation in varchar2 ,
287 p_old_record_data in ghr_pa_history%rowtype ,
288 p_row_id in rowid
289 ) is
290
291 l_proc varchar2(30):='set_operation_info';
292 indx binary_integer;
293 l_row_touched boolean;
294 Begin
295
296 hr_utility.set_location('Entering:'|| l_proc, 5);
297 hr_utility.set_location(l_proc || '. program_name :' || p_program_name, 6);
298 hr_utility.set_location(l_proc || '. operation :' || p_operation, 7);
299 if lower(p_program_name) = 'core' or
300 lower(p_program_name) = 'sf50' then
301 if lower(p_operation) = 'insert' then
302 -- Check if this record was previously updated/created within this session
303 -- so it should already be in the rg. then don't add row since it is already in rg.
304 l_row_touched := row_already_touched(p_row_id);
305 if l_row_touched then
306 hr_utility.set_location(l_proc, 15);
307 null;
308 else
309 hr_utility.set_location(l_proc, 20);
310 -- track the fact that this row was touched in this session.
311 indx := add_row_operation_info_rg(
312 p_table_name => upper(p_table_name),
313 p_table_pk_id => p_table_pk_id,
314 p_operation => p_operation,
315 p_row_id => p_row_id);
316 end if;
317 elsif lower(p_operation) = 'update' then
318 -- Check if this record was previously updated/created within this session
319 -- so it should be in the rg. then don't update pre. and pre-update.
320 l_row_touched := row_already_touched(p_row_id);
321 if l_row_touched then
322 hr_utility.set_location(l_proc || 'row touched', 25);
323 null;
324 else
325 hr_utility.set_location(l_proc || 'row not touched', 30);
326 -- track the fact that this row was touched in this session.
327 indx := add_row_operation_info_rg(
328 p_table_name => p_table_name,
329 p_table_pk_id => p_table_pk_id,
330 p_operation => p_operation,
331 p_row_id => p_row_id);
332 -- since this is an update, save the pre values for this row
333 add_row_pre_update_record_rg(p_old_record_data, indx);
334 end if;
335 else /* delete */
336 hr_utility.set_location(l_proc, 35);
337 null;
338 end if;
339 else /* If called from any other process */
340 -- To be worked out.
341 -- It may need to be handled the way we'll handle core
342 -- Until then, throw an error since this program name is not yet supported.
343 hr_utility.set_location('ERROR: Program is unsupported. Program name : ' || p_program_name || l_proc, 40);
344 hr_utility.set_message(8301,'GHR_38494_UNKNOWN_PROGRAM_NAME');
345 hr_utility.set_message_token('PROGRAM_NAME',p_program_name);
346 hr_utility.raise_error;
347 end if;
348 hr_utility.set_location('Leaving:'|| l_proc, 45);
349 End set_operation_info;
350
351 -- ---------------------------------------------------------------------------
352 -- |--------------------------< add_row_operation_info_rg >-------------------|
353 -- ---------------------------------------------------------------------------
354 -- {Start Of Comments}
355 --
356 -- Description:
357 -- Adds a row to the global session variable g_operation_info. Returns the
358 -- incremented index for the next row in the record group.
359 --
360 -- Pre-Requisities:
361 -- None.
362 --
363 -- In Parameters:
364 -- p_table_name -> name of the table that this row is from.
365 -- p_table_pk_id -> primary key of the row.
366 -- p_operation -> dml operation being performed on the row (insert, update, delete).
367 -- p_row_id -> rowid of this row.
368 --
369 -- Post Success:
370 -- Row will have been added to g_operation_info.
371 --
372 -- Post Failure:
373 -- No failure conditions.
374 --
375 -- Developer Implementation Notes:
376 -- None
377 --
378 -- Access Status:
379 -- Internal Development Use Only.
380 --
381 -- {End Of Comments}
382 -- ---------------------------------------------------------------------------
383 Function add_row_operation_info_rg ( p_table_name in ghr_pa_history.table_name%type,
384 p_table_pk_id in ghr_pa_history.information1%type,
385 p_operation in varchar2,
386 p_row_id in rowid) return binary_integer is
387
388 indx binary_integer;
389 l_proc varchar2(30):='add_row_operation_info_rg';
390 BEGIN
391 hr_utility.set_location('Entering:'|| l_proc, 5);
392 indx := g_operation_info.COUNT+1;
393 g_operation_info(indx).table_name := p_table_name;
394 g_operation_info(indx).table_pk_id := p_table_pk_id;
395 g_operation_info(indx).operation := p_operation;
396 g_operation_info(indx).row_id := p_row_id;
397 hr_utility.set_location('Leaving:'|| l_proc, 10);
398 return indx;
399 END;
400
401 -- ---------------------------------------------------------------------------
402 -- |--------------------------< add_row_pre_update_record_rg >----------------|
403 -- ---------------------------------------------------------------------------
404 -- {Start Of Comments}
405 --
406 -- Description:
407 -- Adds a row to the global session variable g_pre_update_record_rg.
408 --
409 -- Pre-Requisities:
410 -- None.
411 --
412 -- In Parameters:
413 -- p_pre_update_rg -> record to be added.
414 -- p_ind -> index of this row (index is the same as
415 -- the index for the corresponding row in
416 -- g_operation_info).
417 --
418 -- Post Success:
419 -- Row will have been added to g_pre_update.
420 --
421 -- Post Failure:
422 -- No failure conditions.
423 --
424 -- Developer Implementation Notes:
425 -- None
426 --
427 -- Access Status:
428 -- Internal Development Use Only.
429 --
430 -- {End Of Comments}
431 -- ---------------------------------------------------------------------------
432 Procedure add_row_pre_update_record_rg ( p_pre_update_rg in ghr_pa_history%rowtype,
433 p_ind in binary_integer) is
434 l_proc varchar2(30) := 'add_row_pre_update_record_rg';
435 l_hist_data ghr_pa_history%rowtype;
436 begin
437 hr_utility.set_location('Entering:'|| l_proc, 5);
438 -- local variable is used to temporarily hold the data because the
439 -- assignment did not work for some unknown reason (a bug in PL/SQL??)
440 -- when doing the assignment with p_pre_update directly.
441 l_hist_data := p_pre_update_rg;
442 g_pre_update(p_ind) := l_hist_data;
443 hr_utility.set_location('Leaving:'|| l_proc, 10);
444 End add_row_pre_update_record_rg ;
445
446
447 -- ---------------------------------------------------------------------------
448 -- |--------------------------< display_g_session_var >-----------------------|
449 -- ---------------------------------------------------------------------------
450 -- {Start Of Comments}
451 --
452 -- Description:
453 -- Displays the g_session_var information using hr_utility.set_location calls.
454 -- (trace must be on to see this information). This procedure is meant to be
455 -- used for debugging only.
456 --
457 -- Pre-Requisities:
458 -- None.
459 --
460 -- In Parameters:
461 -- None.
462 --
463 -- Post Success:
464 -- g_session_var data will have been displayed.
465 --
466 -- Post Failure:
467 -- No failure conditions.
468 --
469 -- Developer Implementation Notes:
470 -- None
471 --
472 -- Access Status:
473 -- Internal Development Use Only.
474 --
475 -- {End Of Comments}
476 -- ---------------------------------------------------------------------------
477 Procedure display_g_session_var is
478 l_ind binary_integer;
479 l_proc varchar2(30):='display_g_session_var';
480 begin
481 hr_utility.set_location('Entering:' || l_proc, 5);
482 hr_utility.set_location('pa_request_id=' ||g_session_var.pa_request_id, 6);
483 hr_utility.set_location('NOA_ID='||g_session_var.noa_id, 7);
484 hr_utility.set_location('altered_pa_request_id=' || g_session_var.altered_pa_request_id, 8);
485 hr_utility.set_location('NOA_ID_CORRECT=' || g_session_var.noa_id_correct, 9);
486 hr_utility.set_location('PERSON_ID=' || g_session_var.person_id, 10 );
487 hr_utility.set_location('ASSIGNMENT_ID=' || g_session_var.assignment_id, 11);
488
489 for l_ind in 1 .. g_pre_update.count LOOP
490 null;
491 end loop;
492
493 hr_utility.set_location('Leaving:' || l_proc, 10);
494 exception
495 when others then
496 raise;
497 end display_g_session_var;
498
499 -- ---------------------------------------------------------------------------
500 -- |--------------------------< row_already_touched >-----------------------|
501 -- ---------------------------------------------------------------------------
502 -- {Start Of Comments}
503 --
504 -- Description:
505 -- Returns true if row is already in g_operation_info. If it already is in
506 -- this record group, then this session has already touched this particular
507 -- row.
508 --
509 -- Pre-Requisities:
510 -- None.
511 --
512 -- In Parameters:
513 -- p_row_id -> rowid of the row being considered.
514 --
515 -- Post Success:
516 -- Returns TRUE if row has already been put in g_operation_info, FALSE
517 -- otherwise.
518 --
519 -- Post Failure:
520 -- No failure conditions.
521 --
522 -- Developer Implementation Notes:
523 -- None
524 --
525 -- Access Status:
526 -- Internal Development Use Only.
527 --
528 -- {End Of Comments}
529 -- ---------------------------------------------------------------------------
530 Function row_already_touched(p_row_id in rowid) return boolean is
531 ind binary_integer;
532 l_proc varchar2(30) := 'row_already_touched' ;
533 Begin
534 hr_utility.set_location('Entering:'|| l_proc, 5);
535 if g_operation_info.COUNT > 0 then
536 ind := g_operation_info.FIRST;
537 hr_utility.set_location(l_proc, 10);
538 LOOP
539 if g_operation_info(ind).row_id = p_row_id then
540 return TRUE ;
541 END IF;
542 Exit when ind = g_operation_info.LAST;
543 ind := g_operation_info.NEXT(ind);
544 END LOOP;
545 hr_utility.set_location('Leaving:'|| l_proc, 15);
546 return FALSE;
547 END IF;
548 return FALSE;
549 End;
550
551 -- ---------------------------------------------------------------------------
552 -- |--------------------------< reinit_g_session_var >------------------------|
553 -- ---------------------------------------------------------------------------
554 -- {Start Of Comments}
555 --
556 -- Description:
557 -- set g_session_var back to default values.
558 --
559 -- Pre-Requisities:
560 -- None.
561 --
562 -- In Parameters:
563 -- None.
564 --
565 -- Post Success:
566 -- g_session_var will contain default settings.
567 --
568 -- Post Failure:
569 -- No failure conditions.
570 --
571 -- Developer Implementation Notes:
572 -- None
573 --
574 -- Access Status:
575 -- Internal Development Use Only.
576 --
577 -- {End Of Comments}
578 -- ---------------------------------------------------------------------------
579 Procedure reinit_g_session_var is
580 l_proc varchar2(30):='reinit_g_session_var';
581 begin
582 hr_utility.set_location('Entering:'|| l_proc, 5);
583 g_session_var.pa_request_id := null;
584 g_session_var.noa_id := null;
585 g_session_var.altered_pa_request_id := null;
586 g_session_var.noa_id_correct := null;
587 g_session_var.person_id := null;
588 g_session_var.assignment_id := null;
589 g_session_var.program_name := null;
590 g_session_var.fire_trigger := null;
591 g_session_var.date_effective := null;
592 g_session_var.pa_history_id := null;
593 g_pre_update.delete;
594 g_operation_info.delete;
595
596 hr_utility.set_location('Leaving:' || l_proc, 10);
597
598 end reinit_g_session_var;
599
600 Procedure fetch_history_info(
601 p_table_name in varchar2 default null,
602 p_table_pk_id in varchar2 default null,
603 p_row_id in rowid default null,
604 p_person_id in number default null,
605 p_date_effective in date default null,
606 p_altered_pa_request_id in number default null,
607 p_noa_id_corrected in number default null,
608 p_pa_history_id in number default null,
609 p_hist_data in out nocopy ghr_pa_history%rowtype,
610 p_result_code out nocopy varchar2) is
611
612 l_people_data per_all_people_f%rowtype;
613 l_asgei_data per_assignment_extra_info%rowtype;
614 l_asgn_data per_all_assignments_f%rowtype;
615 l_peopleei_data per_people_extra_info%rowtype;
616 l_element_entry_value_data pay_element_entry_values_f%rowtype;
617 l_element_entry_data pay_element_entries_f%rowtype;
618 l_posnei_data per_position_extra_info%rowtype;
619 l_peranalyses_data per_person_analyses%rowtype;
620 l_address_data per_addresses%rowtype;
621 l_position_data hr_all_positions_f%rowtype;
622 l_hist_data ghr_pa_history%rowtype;
623 l_proc varchar2(30) := 'fetch_history_info';
624
625 begin
626 hr_utility.set_location('Entering:'|| l_proc, 5);
627 --
628 -- Remember IN OUT parameter IN values
629 l_hist_data := p_hist_data;
630
631 if ( lower(p_table_name) = lower(ghr_history_api.g_peop_table) ) then
632 hr_utility.set_location(l_proc, 10);
633 ghr_history_fetch.fetch_people(p_person_id => p_person_id,
634 p_date_effective => p_date_effective,
635 p_altered_pa_request_id => p_altered_pa_request_id,
636 p_noa_id_corrected => p_noa_id_corrected,
637 p_rowid => p_row_id,
638 p_pa_history_id => p_pa_history_id,
639 p_people_data => l_people_data,
640 p_result_code => p_result_code );
641
642 ghr_history_conv_rg.conv_people_rg_to_hist_rg( p_people_data => l_people_data,
643 p_history_data => p_hist_data );
644 elsif ( lower(p_table_name) = lower(ghr_history_api.g_asgnei_table) ) then
645 hr_utility.set_location(l_proc, 15);
646 ghr_history_fetch.fetch_asgei(p_assignment_extra_info_id => p_table_pk_id,
647 p_date_effective => p_date_effective,
648 p_altered_pa_request_id => p_altered_pa_request_id,
649 p_noa_id_corrected => p_noa_id_corrected,
650 p_rowid => p_row_id,
651 p_pa_history_id => p_pa_history_id,
652 p_asgei_data => l_asgei_data,
653 p_result_code => p_result_code );
654 ghr_history_conv_rg.conv_asgnei_rg_to_hist_rg(
655 p_asgnei_data => l_asgei_data,
656 p_history_data => p_hist_data );
657
658 elsif ( lower(p_table_name) = lower(ghr_history_api.g_asgn_table) ) then
659 hr_utility.set_location(l_proc, 25);
660 ghr_history_fetch.fetch_assignment (
661 p_assignment_id => p_table_pk_id,
662 p_date_effective => p_date_effective,
663 p_altered_pa_request_id => p_altered_pa_request_id,
664 p_noa_id_corrected => p_noa_id_corrected,
665 p_rowid => p_row_id,
666 p_pa_history_id => p_pa_history_id,
667 p_assignment_data => l_asgn_data,
668 p_result_code => p_result_code
669 );
670 ghr_history_conv_rg.conv_asgn_rg_to_hist_rg(
671 p_assignment_data => l_asgn_data,
672 p_history_data => p_hist_data );
673
674 elsif ( lower(p_table_name) = lower(ghr_history_api.g_peopei_table) ) then
675 hr_utility.set_location(l_proc, 35);
676 ghr_history_fetch.fetch_peopleei(
677 p_person_extra_info_id => p_table_pk_id,
678 p_date_effective => p_date_effective,
679 p_altered_pa_request_id => p_altered_pa_request_id,
680 p_noa_id_corrected => p_noa_id_corrected,
681 p_rowid => p_row_id,
682 p_pa_history_id => p_pa_history_id,
683 p_peopleei_data => l_peopleei_data,
684 p_result_code => p_result_code);
685
686 ghr_history_conv_rg.conv_peopleei_rg_to_hist_rg(
687 p_people_ei_data => l_peopleei_data,
688 p_history_data => p_hist_data );
689
690 elsif ( lower(p_table_name) = lower(ghr_history_api.g_eleevl_table) ) then
691 hr_utility.set_location(l_proc, 35);
692 ghr_history_fetch.fetch_element_entry_value(
693 p_element_entry_value_id => p_table_pk_id,
694 p_date_effective => p_date_effective,
695 p_altered_pa_request_id => p_altered_pa_request_id,
696 p_noa_id_corrected => p_noa_id_corrected,
697 p_rowid => p_row_id,
698 p_pa_history_id => p_pa_history_id,
699 p_element_entry_data => l_element_entry_value_data,
700 p_result_code => p_result_code);
701
702 ghr_history_conv_rg.conv_element_entval_rg_to_hist(
703 p_element_entval_data => l_element_entry_value_data,
704 p_history_data => p_hist_data );
705
706 elsif ( lower(p_table_name) = lower(ghr_history_api.g_eleent_table) ) then
707 hr_utility.set_location(l_proc, 35);
708 ghr_history_fetch.fetch_element_entries(
709 p_element_entry_id => p_table_pk_id,
710 p_date_effective => p_date_effective,
711 p_altered_pa_request_id => p_altered_pa_request_id,
712 p_noa_id_corrected => p_noa_id_corrected,
713 p_rowid => p_row_id,
714 p_pa_history_id => p_pa_history_id,
715 p_element_entry_data => l_element_entry_data,
716 p_result_code => p_result_code);
717
718 ghr_history_conv_rg.conv_element_entry_rg_to_hist(
719 p_element_entries_data => l_element_entry_data,
720 p_history_data => p_hist_data );
721
722 elsif ( lower(p_table_name) = lower(ghr_history_api.g_posnei_table) ) then
723 hr_utility.set_location(l_proc, 35);
724 ghr_history_fetch.fetch_positionei(
725 p_position_extra_info_id => p_table_pk_id,
726 p_date_effective => p_date_effective,
727 p_altered_pa_request_id => p_altered_pa_request_id,
728 p_noa_id_corrected => p_noa_id_corrected,
729 p_rowid => p_row_id,
730 p_pa_history_id => p_pa_history_id,
731 p_posei_data => l_posnei_data,
732 p_result_code => p_result_code);
733
734 ghr_history_conv_rg.conv_positionei_rg_to_hist_rg(
735 p_position_ei_data => l_posnei_data,
736 p_history_data => p_hist_data );
737
738 elsif ( lower(p_table_name) = lower(ghr_history_api.g_perana_table) ) then
739 hr_utility.set_location(l_proc, 40);
740 ghr_history_fetch.fetch_person_analyses (
741 p_person_analysis_id => p_table_pk_id,
742 p_date_effective => p_date_effective,
743 p_altered_pa_request_id => p_altered_pa_request_id,
744 p_noa_id_corrected => p_noa_id_corrected,
745 p_rowid => p_row_id,
746 p_pa_history_id => p_pa_history_id,
747 p_peranalyses_data => l_peranalyses_data,
748 p_result_code => p_result_code);
749
750 ghr_history_conv_rg.conv_peranalyses_rg_to_hist_rg(
751 p_peranalyses_data => l_peranalyses_data,
752 p_history_data => p_hist_data);
753 elsif ( lower(p_table_name) = lower(ghr_history_api.g_addres_table) ) then
754 hr_utility.set_location(l_proc, 40);
755 ghr_history_fetch.fetch_address(
756 p_address_id => p_table_pk_id,
757 p_date_effective => p_date_effective,
758 p_altered_pa_request_id => p_altered_pa_request_id,
759 p_noa_id_corrected => p_noa_id_corrected,
760 p_rowid => p_row_id,
761 p_pa_history_id => p_pa_history_id,
762 p_address_data => l_address_data,
763 p_result_code => p_result_code);
764
765 ghr_history_conv_rg.conv_addresses_rg_to_hist_rg(
766 p_addresses_data => l_address_data,
767 p_history_data => p_hist_data);
768 elsif ( lower(p_table_name) = lower(ghr_history_api.g_posn_table) ) then
769 hr_utility.set_location(l_proc, 40);
770 ghr_history_fetch.fetch_position(
771 p_position_id => p_table_pk_id,
772 p_date_effective => p_date_effective,
773 p_altered_pa_request_id => p_altered_pa_request_id,
774 p_noa_id_corrected => p_noa_id_corrected,
775 p_rowid => p_row_id,
776 p_pa_history_id => p_pa_history_id,
777 p_position_data => l_position_data,
778 p_result_code => p_result_code);
779
780 ghr_history_conv_rg.conv_position_rg_to_hist_rg(
781 p_position_data => l_position_data,
782 p_history_data => p_hist_data);
783
784 else
785 hr_utility.set_location('ERROR: Table is unsupported. Table name : ' || p_table_name || l_proc, 45);
786 hr_utility.set_message(8301,'GHR_38495_UNKNOWN_TABLE_NAME');
787 hr_utility.set_message_token('TABLE_NAME',p_table_name);
788 hr_utility.raise_error;
789 end if;
790
791 hr_utility.set_location(' Leaving:'||l_proc, 20);
792 exception when others then
793 --
794 -- Reset IN OUT parameters and set OUT parameters
795 --
796 p_hist_data := l_hist_data;
797 p_result_code := null;
798 raise;
799
800 end fetch_history_info;
801
802 -- ---------------------------------------------------------------------------
803 -- |--------------------------< post_update_process >-------------------------|
804 -- ---------------------------------------------------------------------------
805 -- {Start Of Comments}
806 --
807 -- Description:
808 -- Takes information from session global variables and inserts appropriate tracking
809 -- information into ghr_pa_history table.
810 --
811 -- Pre-Requisities:
812 -- None.
813 --
814 -- In Parameters:
815 -- None.
816 --
817 -- Post Success:
818 -- ghr_pa_history will have been populated with all history information for the
819 -- database rows that were changed in this session.
820 --
821 -- Post Failure:
822 -- An exception will be generated if there were no records found in history for an
823 -- element_entry_value.
824 --
825 -- Developer Implementation Notes:
826 -- None
827 --
828 -- Access Status:
829 -- Internal Development Use Only.
830 --
831 -- {End Of Comments}
832 -- ---------------------------------------------------------------------------
833 PROCEDURE post_update_process IS
834 indx binary_integer:=1;
835 l_return_status varchar2(10);
836 l_pre_mode varchar2(10):='PRE';
837 l_post_mode varchar2(10):='POST';
838 l_hist_data ghr_pa_history%rowtype;
839 l_eleevl_hist_data ghr_pa_history%rowtype;
840 l_eleevl_data pay_element_entry_values_f%rowtype;
841 l_pre_record ghr_pa_history%rowtype;
842 l_post_data ghr_pa_history%rowtype;
843 l_session_var g_session_var_type;
844 pa_history_id ghr_pa_history.pa_history_id%type;
845 l_interv_on_table Boolean;
846 l_interv_on_eff_date Boolean;
847 l_rec_avl Boolean;
848 l_pre_effective_end_date date;
849 l_dummy_hist_data ghr_pa_history%rowtype := null;
850 l_hist_data_as_of_date ghr_pa_history%rowtype;
851 l_dml_operation ghr_pa_history.dml_operation%type;
852 l_pre_values_flag ghr_pa_history.pre_values_flag%type;
853 l_pa_history_id number;
854 l_proc varchar2(30) := 'post_update_process';
855
856 -- initializes l_hist_data with session variable information.
857 PROCEDURE init_record_data(l_session_var in g_session_var_type,
858 l_hist_data in out nocopy ghr_pa_history%rowtype,
859 indx in binary_integer) IS
860 l_dummy VARCHAR2(1);
861 l_i_hist_data ghr_pa_history%rowtype;
862 cursor c_noa_sep_family is
863 select 'X' from ghr_nature_of_actions
864 where code in ('300','301','302','303','304','312','317',
865 '330','350','351','353','355','356','357','385')
866 and nature_of_action_id = l_session_var.noa_id;
867 BEGIN
868 --
869 -- Remember IN OUT parameter IN values
870 l_i_hist_data := l_hist_data;
871
872 l_hist_data.process_date := sysdate;
873 l_hist_data.effective_date := l_session_var.date_effective;
874 l_hist_data.table_name := g_operation_info(indx).table_name;
875 l_hist_data.pa_request_id := l_session_var.pa_request_id;
876 l_hist_data.altered_pa_request_id := l_session_var.altered_pa_request_id;
877 l_hist_data.nature_of_action_id := l_session_var.noa_id;
878 l_hist_data.person_id := l_session_var.person_id;
879 l_hist_data.assignment_id := l_session_var.assignment_id;
880
881 -- Bug# 1240717. In case of Separation and NTE dates for assignment extra info
882 -- the effective date should be 1 day more than the effective date of RPA.
883 IF l_hist_data.table_name = 'PER_ASSIGNMENT_EXTRA_INFO' AND
884 l_hist_data.information5 = 'GHR_US_ASG_NTE_DATES'
885 THEN
886 OPEN c_noa_sep_family;
887 FETCH c_noa_sep_family INTO l_dummy;
888 IF c_noa_sep_family%FOUND THEN
889 l_hist_data.effective_date := l_session_var.date_effective + 1;
890 END IF;
891 CLOSE c_noa_sep_family;
892 END IF;
893 EXCEPTION WHEN OTHERS then
894 --
895 -- Reset IN OUT parameters and set OUT parameters
896 --
897 l_hist_data := l_i_hist_data;
898 raise;
899
900 END;
901
902 -- populates p_session_var with correct person_id and assignment_id if they
903 -- have not already been populated.
904 procedure get_asgn_peop (p_session_var in out nocopy g_session_var_type) is
905 cursor c1 (c_position_id number) is
906 select
907 assignment_id ,
908 person_id
909 from per_all_assignments_f
910 where position_id = c_position_id
911 and assignment_type <> 'B';
912
913 cursor c2 (c_assignment_id number) is
914 select
915 person_id
916 from per_all_assignments_f
917 where assignment_id = c_assignment_id
918 and assignment_type <> 'B';
919
920 cursor c3 (c_element_entry_id number) is
921 select
922 assignment_id
923 from pay_element_entries_f
924 where element_entry_id = c_element_entry_id;
925
926 l_session_var g_session_var_type;
927 l_proc varchar2(30):='get_asgn_peop';
928
929 Begin
930 hr_utility.set_location( 'Entering : ' || l_proc, 10);
931 --
932 -- Remember IN OUT parameter IN values
933 l_session_var := p_session_var;
934
935 if p_session_var.assignment_id is null and
936 p_session_var.position_id is not null then
937 hr_utility.set_location( l_proc, 20);
938 open c1 (p_session_var.position_id);
939 fetch c1 into
940 p_session_var.assignment_id,
941 p_session_var.person_id;
942 if c1%notfound then
943 hr_utility.set_location( l_proc, 30);
944 end if;
945 close c1;
946 end if;
947
948 if p_session_var.assignment_id is null and
949 p_session_var.element_entry_id is not null then
950 hr_utility.set_location( l_proc, 40);
951 open c3( p_session_var.element_entry_id);
952 Fetch c3 into
953 p_session_var.assignment_id;
954 if c3%notfound then
955 hr_utility.set_location( l_proc, 50);
956 end if;
957 close c3;
958 end if;
959
960 if p_session_var.person_id is null and
961 p_session_var.assignment_id is not null then
962 hr_utility.set_location( l_proc, 60);
963 open c2(p_session_var.assignment_id);
964 fetch c2 into
965 p_session_var.person_id;
966 if c2%notfound then
967 hr_utility.set_location( l_proc, 20);
968 end if;
969 close c2;
970
971 end if;
972 exception when others then
973 --
974 -- Reset IN OUT parameters and set OUT parameters
975 --
976 p_session_var := l_session_var;
977 raise;
978 End;
979
980 BEGIN
981 hr_utility.set_location('Entering:'|| l_proc, 5);
982 /* set fire_trigger to N so that none of the cascading will cause triggers to be fired.
983 must be turned back on when cascading is complete. */
984 get_g_session_var (l_session_var);
985 l_session_var.fire_trigger := 'N';
986 -- set session variables (person_id, assignment_id) if value is null
987 get_asgn_peop( l_session_var);
988 set_g_session_var (l_session_var);
989 hr_utility.set_location(l_proc, 10);
990
991 -- The following loop will loop thru all database rows that were touched in this session (everything in
992 -- g_operation_info rg). For every row, the corresponding history information will be inserted into
993 -- ghr_pa_history.
994 WHILE indx <= g_operation_info.COUNT
995 LOOP
996 hr_utility.set_location(l_proc || 'in post process' || to_char(indx) || g_operation_info(indx).operation, 10);
997 l_hist_data := l_dummy_hist_data;
998 l_pre_record := l_dummy_hist_data;
999 l_post_data := l_dummy_hist_data;
1000
1001 -- if operation is insert then call fetch_history_info passing l_session_var.date_effective - 1
1002 -- otherwise use l_session_var.dete_effective.
1003 if g_operation_info(indx).operation = 'insert' then
1004 l_pre_effective_end_date := l_session_var.date_effective - 1;
1005 elsif g_operation_info(indx).operation = 'update' then
1006 l_pre_effective_end_date := l_session_var.date_effective;
1007 else
1008 l_pre_effective_end_date := l_session_var.date_effective;
1009 end if;
1010
1011 -- if this is a datetrack table, DML operation was an update, and it is not the correction SF52 then
1012 -- we may use Pre-update from global session
1013 if (lower(g_operation_info(indx).table_name) in (lower(ghr_history_api.g_eleevl_table),
1014 lower(ghr_history_api.g_asgn_table),lower(ghr_history_api.g_peop_table),
1015 lower(ghr_history_api.g_posn_table),
1016 lower(ghr_history_api.g_eleent_table)) AND l_session_var.noa_id_correct is NULL AND
1017 lower(g_operation_info(indx).operation) = 'update') then
1018 hr_utility.set_location(l_proc || 'using pre_update', 9158);
1019 l_pre_record := g_pre_update(indx);
1020 -- Pre-record found
1021 l_return_Status := NULL;
1022 else
1023 hr_utility.set_location(l_proc || 'calling fetch_history_info', 9157);
1024 -- Fetch Pre-record
1025 fetch_history_info(
1026 p_table_name => g_operation_info(indx).table_name,
1027 p_hist_data => l_hist_data,
1028 p_table_pk_id => g_operation_info(indx).table_pk_id,
1029 p_person_id => l_session_var.person_id,
1030 p_date_effective => l_pre_effective_end_date,
1031 p_altered_pa_request_id => l_session_var.altered_pa_request_id,
1032 p_noa_id_corrected => l_session_var.noa_id_correct,
1033 p_result_code => l_return_status);
1034
1035 l_pre_record := l_hist_data;
1036
1037 end if;
1038 hr_utility.set_location('l_pre_record.pa_history_id: ' || l_pre_record.pa_history_id || l_proc,2009);
1039 hr_utility.set_location('l_pre_record.information1: ' || l_pre_record.information1 || l_proc,2010);
1040 hr_utility.set_location('l_pre_record.person_id: ' || l_pre_record.person_id || l_proc,2011);
1041 hr_utility.set_location('l_pre_record.effective_date: ' || l_pre_record.effective_date || l_proc,2012);
1042 hr_utility.set_location('l_pre_record.information9: ' || l_pre_record.information9 || l_proc,2019);
1043 hr_utility.set_location('l_pre_record.information10: ' || l_pre_record.information10 || l_proc,2020);
1044 hr_utility.set_location('l_pre_record.information11: ' || l_pre_record.information11 || l_proc,2021);
1045 hr_utility.set_location('l_pre_record.information12: ' || l_pre_record.information12 || l_proc,2022);
1046 hr_utility.set_location('l_pre_record.information13: ' || l_pre_record.information13 || l_proc,2023);
1047 hr_utility.set_location('l_pre_record.information14: ' || l_pre_record.information14 || l_proc,2024);
1048
1049 IF g_operation_info(indx).operation = 'insert' THEN
1050 IF l_return_status IS NULL THEN
1051 hr_utility.set_location(l_proc || 'Insert: history found.', 15);
1052 -- inserting with pre-values
1053 l_pre_values_flag := 'Y';
1054 l_dml_operation := ghr_history_api.g_ins_operation;
1055 ELSE
1056 hr_utility.set_location(l_proc || 'Insert: history NOT found.', 20);
1057 -- inserting with no pre-values
1058 l_dml_operation := ghr_history_api.g_ins_operation;
1059 l_pre_values_flag := 'N';
1060 END IF;
1061 ELSIF g_operation_info(indx).operation = 'update' THEN
1062 -- set pre-record rg
1063 hr_utility.set_location(l_proc || 'Update.', 25);
1064 IF l_return_status IS NULL THEN
1065 hr_utility.set_location(l_proc || 'Update: history found.', 15);
1066 -- set Pre_record_rg and other flags
1067 -- updating with pre-values
1068 l_pre_values_flag := 'Y';
1069 l_dml_operation := ghr_history_api.g_upd_operation;
1070 ELSE
1071 hr_utility.set_location(l_proc || 'Update: history NOT found.', 20);
1072 -- updating with no pre-values
1073 l_dml_operation := ghr_history_api.g_upd_operation;
1074 l_pre_values_flag := 'N';
1075 END IF;
1076 END IF;
1077
1078
1079 hr_utility.set_location(l_proc || 'Fetch history info for post-update.', 30);
1080 -- get post_update using rowid.
1081 -- Need only pass rowid, other parms should be changed to be nullable.
1082 fetch_history_info(
1083 p_table_name => g_operation_info(indx).table_name,
1084 p_hist_data => l_post_data,
1085 p_row_id => g_operation_info(indx).row_id,
1086 p_result_code => l_return_status);
1087
1088 -- BUG # 5195518 added the below validation as if no record exists with the
1089 -- row id then no need of inserting into PA History.
1090 l_rec_avl := TRUE;
1091 If g_operation_info(indx).table_name in (ghr_history_api.g_eleevl_table,ghr_history_api.g_eleent_table )
1092 and l_return_status is not null then
1093 l_rec_avl := FALSE ;
1094 End if;
1095
1096 If l_rec_avl then
1097
1098 l_post_data.pre_values_flag := l_pre_values_flag;
1099 l_post_data.dml_operation := l_dml_operation;
1100
1101 -- set post-update-rg
1102 init_record_data(l_session_var, l_post_data, indx);
1103 hr_utility.set_location(l_proc || 'Add post-record record', 35);
1104 hr_utility.set_location(l_proc || l_post_data.effective_date,36);
1105 hr_utility.set_location(l_proc || l_return_status,37);
1106
1107 hr_utility.set_location(l_proc || 'Insert in pa history: effective_date = .' || to_char(l_post_data.effective_date), 40);
1108 hr_utility.set_location(l_proc || 'Insert in pa history: Process_date = .' || to_char(l_post_data.Process_date), 45);
1109 hr_utility.set_location(l_proc || 'Insert in pa history: table_name = .' || l_post_data.table_name, 50);
1110 hr_utility.set_location(l_proc || 'Insert in pa history: pa_request_id = .' || to_char(l_post_data.pa_request_id), 60);
1111 hr_utility.set_location(l_proc || 'Insert in pa history: nature_of_action_id = .' || to_char(l_post_data.nature_of_action_id), 65);
1112 hr_utility.set_location(l_proc || 'Insert in pa history: dml_operation = .' || l_post_data.dml_operation, 75);
1113
1114 ghr_pah_ins.ins(
1115 p_pa_history_id => pa_history_id ,
1116 p_pa_request_id => l_post_data.pa_request_id ,
1117 p_process_date => l_post_data.process_date ,
1118 p_nature_of_action_id => l_post_data.nature_of_action_id ,
1119 p_effective_date => l_post_data.effective_date ,
1120 p_altered_pa_request_id => l_post_data.altered_pa_request_id ,
1121 p_person_id => l_post_data.person_id ,
1122 p_assignment_id => l_post_data.assignment_id ,
1123 p_dml_operation => l_post_data.dml_operation ,
1124 p_table_name => upper(l_post_data.table_name) ,
1125 p_pre_values_flag => l_post_data.pre_values_flag ,
1126 p_information1 => l_post_data.information1 ,
1127 p_information2 => l_post_data.information2 ,
1128 p_information3 => l_post_data.information3 ,
1129 p_information4 => l_post_data.information4 ,
1130 p_information5 => l_post_data.information5 ,
1131 p_information6 => l_post_data.information6 ,
1132 p_information7 => l_post_data.information7 ,
1133 p_information8 => l_post_data.information8 ,
1134 p_information9 => l_post_data.information9 ,
1135 p_information10 => l_post_data.information10 ,
1136 p_information11 => l_post_data.information11 ,
1137 p_information12 => l_post_data.information12 ,
1138 p_information13 => l_post_data.information13 ,
1139 p_information14 => l_post_data.information14 ,
1140 p_information15 => l_post_data.information15 ,
1141 p_information16 => l_post_data.information16 ,
1142 p_information17 => l_post_data.information17 ,
1143 p_information18 => l_post_data.information18 ,
1144 p_information19 => l_post_data.information19 ,
1145 p_information20 => l_post_data.information20 ,
1146 p_information21 => l_post_data.information21 ,
1147 p_information22 => l_post_data.information22 ,
1148 p_information23 => l_post_data.information23 ,
1149 p_information24 => l_post_data.information24 ,
1150 p_information25 => l_post_data.information25 ,
1151 p_information26 => l_post_data.information26 ,
1152 p_information27 => l_post_data.information27 ,
1153 p_information28 => l_post_data.information28 ,
1154 p_information29 => l_post_data.information29 ,
1155 p_information30 => l_post_data.information30 ,
1156 p_information31 => l_post_data.information31 ,
1157 p_information32 => l_post_data.information32 ,
1158 p_information33 => l_post_data.information33 ,
1159 p_information34 => l_post_data.information34 ,
1160 p_information35 => l_post_data.information35 ,
1161 p_information36 => l_post_data.information36 ,
1162 p_information37 => l_post_data.information37 ,
1163 p_information38 => l_post_data.information38 ,
1164 p_information39 => l_post_data.information39 ,
1165 p_information47 => l_post_data.information47 ,
1166 p_information48 => l_post_data.information48 ,
1167 p_information49 => l_post_data.information49 ,
1168 p_information40 => l_post_data.information40 ,
1169 p_information41 => l_post_data.information41 ,
1170 p_information42 => l_post_data.information42 ,
1171 p_information43 => l_post_data.information43 ,
1172 p_information44 => l_post_data.information44 ,
1173 p_information45 => l_post_data.information45 ,
1174 p_information46 => l_post_data.information46 ,
1175 p_information50 => l_post_data.information50 ,
1176 p_information51 => l_post_data.information51 ,
1177 p_information52 => l_post_data.information52 ,
1178 p_information53 => l_post_data.information53 ,
1179 p_information54 => l_post_data.information54 ,
1180 p_information55 => l_post_data.information55 ,
1181 p_information56 => l_post_data.information56 ,
1182 p_information57 => l_post_data.information57 ,
1183 p_information58 => l_post_data.information58 ,
1184 p_information59 => l_post_data.information59 ,
1185 p_information60 => l_post_data.information60 ,
1186 p_information61 => l_post_data.information61 ,
1187 p_information62 => l_post_data.information62 ,
1188 p_information63 => l_post_data.information63 ,
1189 p_information64 => l_post_data.information64 ,
1190 p_information65 => l_post_data.information65 ,
1191 p_information66 => l_post_data.information66 ,
1192 p_information67 => l_post_data.information67 ,
1193 p_information68 => l_post_data.information68 ,
1194 p_information69 => l_post_data.information69 ,
1195 p_information70 => l_post_data.information70 ,
1196 p_information71 => l_post_data.information71 ,
1197 p_information72 => l_post_data.information72 ,
1198 p_information73 => l_post_data.information73 ,
1199 p_information74 => l_post_data.information74 ,
1200 p_information75 => l_post_data.information75 ,
1201 p_information76 => l_post_data.information76 ,
1202 p_information77 => l_post_data.information77 ,
1203 p_information78 => l_post_data.information78 ,
1204 p_information79 => l_post_data.information79 ,
1205 p_information80 => l_post_data.information80 ,
1206 p_information81 => l_post_data.information81 ,
1207 p_information82 => l_post_data.information82 ,
1208 p_information83 => l_post_data.information83 ,
1209 p_information84 => l_post_data.information84 ,
1210 p_information85 => l_post_data.information85 ,
1211 p_information86 => l_post_data.information86 ,
1212 p_information87 => l_post_data.information87 ,
1213 p_information88 => l_post_data.information88 ,
1214 p_information89 => l_post_data.information89 ,
1215 p_information90 => l_post_data.information90 ,
1216 p_information91 => l_post_data.information91 ,
1217 p_information92 => l_post_data.information92 ,
1218 p_information93 => l_post_data.information93 ,
1219 p_information94 => l_post_data.information94 ,
1220 p_information95 => l_post_data.information95 ,
1221 p_information96 => l_post_data.information96 ,
1222 p_information97 => l_post_data.information97 ,
1223 p_information98 => l_post_data.information98 ,
1224 p_information99 => l_post_data.information99 ,
1225 p_information100 => l_post_data.information100 ,
1226 p_information101 => l_post_data.information101 ,
1227 p_information102 => l_post_data.information102 ,
1228 p_information103 => l_post_data.information103 ,
1229 p_information104 => l_post_data.information104 ,
1230 p_information105 => l_post_data.information105 ,
1231 p_information106 => l_post_data.information106 ,
1232 p_information107 => l_post_data.information107 ,
1233 p_information108 => l_post_data.information108 ,
1234 p_information109 => l_post_data.information109 ,
1235 p_information110 => l_post_data.information110 ,
1236 p_information111 => l_post_data.information111 ,
1237 p_information112 => l_post_data.information112 ,
1238 p_information113 => l_post_data.information113 ,
1239 p_information114 => l_post_data.information114 ,
1240 p_information115 => l_post_data.information115 ,
1241 p_information116 => l_post_data.information116 ,
1242 p_information117 => l_post_data.information117 ,
1243 p_information118 => l_post_data.information118 ,
1244 p_information119 => l_post_data.information119 ,
1245 p_information120 => l_post_data.information120 ,
1246 p_information121 => l_post_data.information121 ,
1247 p_information122 => l_post_data.information122 ,
1248 p_information123 => l_post_data.information123 ,
1249 p_information124 => l_post_data.information124 ,
1250 p_information125 => l_post_data.information125 ,
1251 p_information126 => l_post_data.information126 ,
1252 p_information127 => l_post_data.information127 ,
1253 p_information128 => l_post_data.information128 ,
1254 p_information129 => l_post_data.information129 ,
1255 p_information130 => l_post_data.information130 ,
1256 p_information131 => l_post_data.information131 ,
1257 p_information132 => l_post_data.information132 ,
1258 p_information133 => l_post_data.information133 ,
1259 p_information134 => l_post_data.information134 ,
1260 p_information135 => l_post_data.information135 ,
1261 p_information136 => l_post_data.information136 ,
1262 p_information137 => l_post_data.information137 ,
1263 p_information138 => l_post_data.information138 ,
1264 p_information139 => l_post_data.information139 ,
1265 p_information140 => l_post_data.information140 ,
1266 p_information141 => l_post_data.information141 ,
1267 p_information142 => l_post_data.information142 ,
1268 p_information143 => l_post_data.information143 ,
1269 p_information144 => l_post_data.information144 ,
1270 p_information145 => l_post_data.information145 ,
1271 p_information146 => l_post_data.information146 ,
1272 p_information147 => l_post_data.information147 ,
1273 p_information148 => l_post_data.information148 ,
1274 p_information149 => l_post_data.information149 ,
1275 p_information150 => l_post_data.information150 ,
1276 p_information151 => l_post_data.information151 ,
1277 p_information152 => l_post_data.information152 ,
1278 p_information153 => l_post_data.information153 ,
1279 p_information154 => l_post_data.information154 ,
1280 p_information155 => l_post_data.information155 ,
1281 p_information156 => l_post_data.information156 ,
1282 p_information157 => l_post_data.information157 ,
1283 p_information158 => l_post_data.information158 ,
1284 p_information159 => l_post_data.information159 ,
1285 p_information160 => l_post_data.information160 ,
1286 p_information161 => l_post_data.information161 ,
1287 p_information162 => l_post_data.information162 ,
1288 p_information163 => l_post_data.information163 ,
1289 p_information164 => l_post_data.information164 ,
1290 p_information165 => l_post_data.information165 ,
1291 p_information166 => l_post_data.information166 ,
1292 p_information167 => l_post_data.information167 ,
1293 p_information168 => l_post_data.information168 ,
1294 p_information169 => l_post_data.information169 ,
1295 p_information170 => l_post_data.information170 ,
1296 p_information171 => l_post_data.information171 ,
1297 p_information172 => l_post_data.information172 ,
1298 p_information173 => l_post_data.information173 ,
1299 p_information174 => l_post_data.information174 ,
1300 p_information175 => l_post_data.information175 ,
1301 p_information176 => l_post_data.information176 ,
1302 p_information177 => l_post_data.information177 ,
1303 p_information178 => l_post_data.information178 ,
1304 p_information179 => l_post_data.information179 ,
1305 p_information180 => l_post_data.information180 ,
1306 p_information181 => l_post_data.information181 ,
1307 p_information182 => l_post_data.information182 ,
1308 p_information183 => l_post_data.information183 ,
1309 p_information184 => l_post_data.information184 ,
1310 p_information185 => l_post_data.information185 ,
1311 p_information186 => l_post_data.information186 ,
1312 p_information187 => l_post_data.information187 ,
1313 p_information188 => l_post_data.information188 ,
1314 p_information189 => l_post_data.information189 ,
1315 p_information190 => l_post_data.information190 ,
1316 p_information191 => l_post_data.information191 ,
1317 p_information192 => l_post_data.information192 ,
1318 p_information193 => l_post_data.information193 ,
1319 p_information194 => l_post_data.information194 ,
1320 p_information195 => l_post_data.information195 ,
1321 p_information196 => l_post_data.information196 ,
1322 p_information197 => l_post_data.information197 ,
1323 p_information198 => l_post_data.information198 ,
1324 p_information199 => l_post_data.information199 ,
1325 p_information200 => l_post_data.information200
1326 );
1327 -- we stored pa_history id into local variable, put it into the record now.
1328 l_post_data.pa_history_id := pa_history_id;
1329 if lower(g_operation_info(indx).table_name) not in
1330 (lower(ghr_history_api.g_eleevl_table),
1331 lower(ghr_history_api.g_eleent_table),
1332 lower(ghr_history_api.g_perana_table)) then
1333 -- this is the normal case for most of the tables we are tracking.
1334
1335 hr_utility.set_location(l_proc || ' g_do_not_cascade ' || ghr_ses_conv_pkg.g_do_not_cascade, 76);
1336
1337 if nvl(ghr_ses_conv_pkg.g_do_not_cascade,'N') <> 'Y' then
1338 ghr_history_cascade.Cascade_History_Data(
1339 p_table_name => upper(g_operation_info(indx).table_name),
1340 p_person_id => l_session_var.person_id,
1341 p_pre_record => l_pre_record,
1342 p_post_record => l_post_data,
1343 p_cascade_type => 'retroactive',
1344 p_interv_on_table => l_interv_on_table,
1345 p_interv_on_eff_date => l_interv_on_eff_date,
1346 p_hist_data_as_of_date => l_hist_data_as_of_date
1347 );
1348 end if;
1349 elsif (lower(g_operation_info(indx).table_name) = lower(ghr_history_api.g_eleevl_table)) then
1350 -- PAY_ELEMENT_ENTRY_VALUES_F is a special case and needs to be handled differently.
1351 -- Cascade need not be performed for PAY_ELEMENT_ENTRY_VALUES_F,
1352 -- as it has only one column value so a record will be created only
1353 -- if the value changes thur sf52 or any other process which need not be
1354 -- cascaded. But if more than one sf52s were applied on the same date then
1355 -- we need to update the row with the most recent value.
1356
1357 -- retrieve the currently effective eleevl row from history and re-apply it to the
1358 -- core table if necessary.
1359
1360 -- get the row from ghr_pa_history for this element_entry_value given the effective date
1361 -- of this action.
1362 ghr_history_fetch.get_date_eff_eleevl(
1363 p_element_entry_value_id => to_number(l_post_data.information1) ,
1364 p_date_effective => l_post_data.effective_date ,
1365 p_element_entry_data => l_eleevl_data ,
1366 p_result_code => l_return_status ,
1367 p_pa_history_id => l_pa_history_id);
1368 if (l_return_status is not null) then
1369 -- this should never happen, but just in case
1370 hr_utility.set_location('ERROR: no date effective row found in history' || l_proc, 38);
1371 hr_utility.set_message(8301,'GHR_38360_NO_RECFND_FOR_DATE');
1372 hr_utility.raise_error;
1373 end if;
1374 ghr_history_conv_rg.conv_element_entval_rg_to_hist(
1375 p_element_entval_data => l_eleevl_data,
1376 p_history_data => l_eleevl_hist_data );
1377 -- check if the element_entry_value from history is different than what we just changed in the core table.
1378 -- if it is, then there were intervening records on the same date. So, re-apply the date-effective row from history.
1379 if (l_post_data.information6 <> l_eleevl_hist_data.information6) then
1380 -- there were intervening records on the same date. So, re-apply the date-effective row from history.
1381 ghr_corr_canc_sf52.update_eleentval( p_hist_pre => l_eleevl_hist_data);
1382 end if;
1383 else
1384 -- PAY_ELEMENT_ENTRIES_F, PER_PERSON_ANALYSES (ghr_history_api.g_eleent_table and g_perana_table)
1385 -- need not be cascaded or updated with
1386 -- most recent values because they will only ever be inserted by non-correction sf52's. They
1387 -- will never be updated by non-correction sf52's. Correction's that update these tables
1388 -- are already handled correctly.
1389 null;
1390 end if;
1391
1392 if l_interv_on_table then
1393 -- if changes were cascaded in the history table then we may have to cascade
1394 -- changes in database tables
1395 hr_utility.set_location(l_proc || ' g_do_not_cascade ' || ghr_ses_conv_pkg.g_do_not_cascade, 78);
1396
1397 if nvl(ghr_ses_conv_pkg.g_do_not_cascade,'N') <> 'Y' then
1398 IF NVL(l_post_data.information5,'N') <> 'GHR_US_RETAINED_GRADE' then -- Bug 2715828/3021003
1399 -- NO need to cascade for Retained Grade.
1400 ghr_history_cascade.Cascade_appl_table_Data (
1401 p_table_name => upper(g_operation_info(indx).table_name),
1402 p_person_id => l_session_var.person_id,
1403 p_pre_record => l_pre_record,
1404 p_post_record => l_post_data,
1405 p_cascade_type => 'retroactive',
1406 p_interv_on_table => l_interv_on_table,
1407 p_interv_on_eff_date => l_interv_on_eff_date,
1408 p_hist_data_as_of_date => l_hist_data_as_of_date
1409 );
1410 END IF; -- IF NVL(l_post_data.information5,'N') <> 'GHR_US_RE....
1411 end if;
1412 end if;
1413 end if;
1414
1415 indx := indx + 1;
1416 END LOOP;
1417 hr_utility.set_location(l_proc, 15);
1418
1419 /* set fire_trigger to Y. Cascading is complete. All triggers should be set to fire again. */
1420 get_g_session_var (l_session_var);
1421 l_session_var.fire_trigger := 'Y';
1422 set_g_session_var (l_session_var);
1423 END post_update_process;
1424
1425 Procedure Post_forms_commit_process( p_eff_date in date) is
1426 l_session_var g_session_var_type;
1427 Begin
1428
1429 -- Set ghr_api.g_api_dml to TRUE to bypass Position and Person EI validation
1430 -- trigger to be fired. This must be set only before post_update_process and
1431 -- not in the form so that validation trigger gets fired for the first time
1432 -- but not at the time of cascacade.
1433 ghr_api.g_api_dml := TRUE;
1434
1435 Post_update_process;
1436 reinit_g_session_var;
1437
1438 ghr_api.g_api_dml := FALSE;
1439 new_form_instance_process;
1440
1441 end post_forms_commit_process;
1442
1443 Procedure New_form_instance_process is
1444 l_session_var g_session_var_type;
1445 Begin
1446
1447 reinit_g_session_var;
1448 l_session_var.program_name := 'core';
1449 l_session_var.fire_trigger := 'Y';
1450 set_g_session_var(l_session_var);
1451
1452
1453 end new_form_instance_process;
1454
1455 -- ---------------------------------------------------------------------------
1456 -- |--------------------------< get_session_date >----------------------------|
1457 -- ---------------------------------------------------------------------------
1458 -- {Start Of Comments}
1459 --
1460 -- Description:
1461 -- Gets the effective date for the current database session.
1462 --
1463 -- Pre-Requisities:
1464 -- None.
1465 --
1466 -- In Parameters:
1467 --
1468 -- Post Success:
1469 -- p_sess_date will contain the effective_date for the current session.
1470 --
1471 -- Post Failure:
1472 -- Throws exception if effective_date not found.
1473 --
1474 -- Developer Implementation Notes:
1475 -- None
1476 --
1477 -- Access Status:
1478 -- Internal Development Use Only.
1479 --
1480 -- {End Of Comments}
1481 -- ---------------------------------------------------------------------------
1482 Procedure get_session_date ( p_sess_date out nocopy date) is
1483 l_proc varchar2(30);
1484 cursor c_getsessdate is
1485 select trunc(effective_date)
1486 from fnd_sessions
1487 where session_id = (select userenv('sessionid')
1488 from dual);
1489 Begin
1490 open c_getsessdate;
1491 fetch c_getsessdate into p_sess_date;
1492 if c_getsessdate%notfound then
1493 close c_getsessdate;
1494 hr_utility.set_message(8301, 'GHR_SESSION_DATE_NOT_FND');
1495 hr_utility.raise_error;
1496 end if;
1497 close c_getsessdate ;
1498 End get_session_date ;
1499
1500 end GHR_HISTORY_API;