1 PACKAGE BODY pay_interpreter_pkg AS
2 /* $Header: pyinterp.pkb 120.29.12010000.4 2008/10/03 06:56:30 salogana ship $ */
3 --
4 -- Global Utils
5 g_pkg VARCHAR2(30) := 'pay_interpreter_pkg';
6 g_traces BOOLEAN := hr_utility.debug_enabled; --See if hr_utility.traces should be output
7 g_dbg BOOLEAN := FALSE; --Used for diagnosing issues by dev, more outputs
8
9 -- Global caches
10 g_business_group_id NUMBER; -- business_group_id cache
11 g_leg_code per_business_groups_perf.legislation_code%TYPE;
12 g_bus_grp_id per_business_groups_perf.business_group_id%TYPE;
13 g_key_date_cache t_key_date_cache; -- store of min dates for ins records
14 TYPE t_upd_cache is
15 table of varchar2(240) INDEX BY BINARY_INTEGER;
16 g_upd_cache t_upd_cache; -- store of min dates for ins records
17 --
18 TYPE t_number is
19 table of number INDEX BY BINARY_INTEGER;
20 --
21 g_grade_list t_number;
22 g_grd_assignment_id number;
23 --
24 type t_valact_rec is record
25 (
26 assignment_id pay_assignment_actions.assignment_id%type,
27 proc_not_exist_date date,
28 proc_exist_date date
29 );
30 --
31 g_valact_rec t_valact_rec;
32 --
33 /* Globals for Time definitions */
34 g_time_definition_id number := -1;
35 g_assignment_action_id number := -1;
36 g_process_time_def boolean;
37 g_tim_def_prc_name varchar2(70);
38 g_proc_set boolean;
39 --
40 G_DISCO_NONE number := 0;
41 G_DISCO_STANDARD number := 1;
42 G_DISCO_DF number := 2;
43 --
44 /****************************************************************************
45 Name : initialise_global
46 Purpose : This initialises the global structre and sets the default
47 values.
48 ****************************************************************************/
49 procedure initialise_global(p_global_env IN OUT NOCOPY t_global_env_rec)
50 is
51 begin
52 --
53 p_global_env.datetrack_ee_tab_use := FALSE;
54 p_global_env.validate_run_actions := FALSE;
55 --
56 end initialise_global;
57 --
58 /****************************************************************************
59 Name : add_datetrack_event_to_entry
60 Purpose : Store a record of entry and datetracked event combinations
61 This procedure uses the datetracked event id to hash into
62 a link list of entries.
63 ****************************************************************************/
64 procedure add_datetrack_event_to_entry
65 (p_datetracked_evt_id in number,
66 p_element_entry_id in number,
67 p_global_env in out nocopy t_global_env_rec)
68 is
69 l_curr_ptr number;
70 begin
71 --
72 l_curr_ptr := glo_datetrack_ee_tab.count + 1;
73
74 glo_datetrack_ee_tab(l_curr_ptr).datetracked_evt_id :=
75 p_datetracked_evt_id;
76 glo_datetrack_ee_tab(l_curr_ptr).element_entry_id :=
77 p_element_entry_id;
78 --
79 /* Put the new entry at the head of the chain
80 */
81 if (glo_datetrack_ee_hash_tab.exists(p_datetracked_evt_id)) then
82 glo_datetrack_ee_tab(l_curr_ptr).next_ptr :=
83 glo_datetrack_ee_hash_tab(p_datetracked_evt_id);
84 glo_datetrack_ee_hash_tab(p_datetracked_evt_id) := l_curr_ptr;
85 else
86 glo_datetrack_ee_tab(l_curr_ptr).next_ptr := null;
87 glo_datetrack_ee_hash_tab(p_datetracked_evt_id) := l_curr_ptr;
88 end if;
89 --
90 end add_datetrack_event_to_entry;
91 --
92 /****************************************************************************
93 Name : clear_dt_event_for_entry
94 Purpose :
95 This clears the cache relating datetracked events to
96 element entries.
97 ****************************************************************************/
98 procedure clear_dt_event_for_entry
99 ( p_global_env in out nocopy t_global_env_rec)
100 is
101 begin
102 --
103 glo_datetrack_ee_tab.delete;
104 glo_datetrack_ee_hash_tab.delete;
105 --
106 end clear_dt_event_for_entry;
107 --
108 /****************************************************************************
109 Name : time_period_internal
110 Purpose : calculates the start and end dates of the proration period.
111 Arguments :
112 IN : p_assignment_action_id
113 p_proration_group_id
114 OUT : p_business_group_id
115 p_start_date
116 p_end_date
117 Notes : Private
118 ****************************************************************************/
119 PROCEDURE time_period_internal(p_assignment_action_id IN NUMBER ,
120 p_proration_group_id IN NUMBER ,
121 p_business_group_id OUT NOCOPY NUMBER ,
122 p_start_date OUT NOCOPY DATE ,
123 p_end_date OUT NOCOPY DATE ) AS
124
125 l_year NUMBER ;
126 l_time_def_id NUMBER ;
127 l_proration_type VARCHAR2(10);
128 l_legislation_code VARCHAR2(40);
129 l_esd DATE ;
130 l_eed DATE ;
131 --
132 -- The following cursor selects the time periods from pay_payroll_actions table.
133
134 CURSOR c_time_period IS
135 SELECT ptp.start_date start_date,
136 ptp.end_date end_date
137 FROM pay_assignment_actions paa,
138 pay_payroll_actions ppa,
139 per_time_periods ptp
140 WHERE paa.assignment_action_id = p_assignment_action_id
141 AND paa.payroll_action_id = ppa.payroll_action_id
142 AND nvl(ppa.date_earned,ppa.effective_date) between ptp.START_DATE and ptp.END_DATE
143 AND ppa.payroll_id = ptp.payroll_id;
144
145 -- Bug 3080689, get periods even if check_date is not same as pay period end date
146 -- AND ptp.time_period_id = ppa.time_period_id; --obsoleted clause
147
148 -- The following cursor selects the start date and month of the financial year.
149
150 CURSOR c_financial_year(p_legislation_code IN VARCHAR) IS
151 SELECT to_date(rule_mode||'/'||l_year, 'DD/MM/YYYY') start_date
152 FROM pay_legislation_rules
153 WHERE legislation_code = p_legislation_code
154 AND rule_type = 'L' ;
155
156 -- The following cursor selects the Proration Type.
157 -- Valid values from proration_period_type are C,F,P,PPA
158
159 CURSOR c_event_group IS
160 SELECT proration_type, time_definition_id
161 FROM pay_event_groups
162 WHERE event_group_id = p_proration_group_id;
163
164 -- The following cursor selects the Legislation code.
165
166 CURSOR c_legislation_code IS
167 SELECT pbg.legislation_code legislation_code,
168 pbg.business_group_id business_group_id
169 FROM pay_assignment_actions paa,
170 pay_payroll_actions ppa,
171 per_business_groups_perf pbg
172 WHERE paa.assignment_action_id = p_assignment_action_id
173 AND paa.payroll_action_id = ppa.payroll_action_id
174 AND ppa.business_group_id = pbg.business_group_id ;
175 --
176 BEGIN
177 -- Finding the time period we are interested in
178 FOR ctp IN c_time_period
179 LOOP --{
180 p_start_date := ctp.start_date;
181 p_end_date := ctp.end_date ;
182 END LOOP; --}
183 --
184 if (g_traces) then
185 hr_utility.trace('Dates are ' || TO_CHAR(p_start_date) || ' ' || TO_CHAR(p_end_date));
186 end if;
187 -- Selects the Proration Type
188 FOR ceg IN c_event_group
189 LOOP
190 l_proration_type := ceg.proration_type;
191 l_time_def_id := ceg.time_definition_id;
192 END LOOP;
193
194 if (g_traces) then
195 hr_utility.trace('Proration Type is ' || l_proration_type);
196 end if;
197 -- Selects the Legislation Code.
198 FOR clc IN c_legislation_code
199 LOOP
200 l_legislation_code := clc.legislation_code;
201 p_business_group_id := clc.business_group_id;
202 END LOOP;
203 --
204 if (g_traces) then
205 hr_utility.trace('Legislation Code ' || l_legislation_code );
206 hr_utility.trace('Business Group Id ' || p_business_group_id);
207 end if;
208
209 -- The following code converts the start date to the appropriate date
210 -- depending upon the Proration Type
211 IF( l_time_def_id is not null) THEN
212 --
213 p_start_date := pay_core_dates.get_time_definition_date( l_time_def_id,
214 p_end_date,
215 p_business_group_id);
216 ELSE
217 IF (l_proration_type = 'P') THEN -- P = Payroll Period
218 -- Do nothing. l_start_date and l_end_date already contain the dates
219 -- we are interested in.
220 NULL;
221 --
222 ELSIF (l_proration_type = 'C') THEN
223 -- C = Calendar Year --BUG 3657955, corrected Y to C
224 p_start_date := TO_DATE('01/01' || TO_CHAR(p_start_date, 'YYYY'), 'DD/MM/YYYY');
225 -- The above instruction gives the 01-JAN-YYYY as the start_date
226 ELSIF (l_proration_type = 'F') THEN -- F = Financial Year
227 l_year := TO_CHAR(p_end_date, 'YYYY');
228 --
229 if (g_traces) then
230 hr_utility.trace('Legislation Code is ' || l_legislation_code);
231 end if;
232 --
233 FOR cfy IN c_financial_year(l_legislation_code)
234 LOOP
235 p_start_date := cfy.start_date;
236 END LOOP;
237 --
238 if (g_traces) then
239 hr_utility.trace('p_start_date ' || TO_CHAR(p_start_date));
240 end if;
241 --
242 IF (p_end_date < p_start_date) THEN
243 p_start_date := TO_DATE(TO_CHAR(p_start_date, 'DD/MM/') ||
244 TO_CHAR(TO_NUMBER(TO_CHAR(p_start_date,'YYYY')) -1)
245 ,'DD/MM/YYYY');
246 -- This condition covers the case where l_end_date = '31-MAY-2000' and
247 -- l_start_date = '01-JUL-2000'.Obviously we should convert l_start_date to
248 -- 01-JUL-1999
249 END IF;
250 -- We assume that the data in the field rule_mode will be in canonical_form
251 ELSIF (l_proration_type = 'PPA') THEN/* Past Period Adjustment */
252 p_start_date := p_start_date-1;
253 END IF;
254 END IF;
255 END time_period_internal;
256
257
258
259 /****************************************************************************
260 Name : prorate_start_date
261 Purpose : This function returns the start date of a proration period.
262 Arguments :
263 IN : p_assignment_action_id
264 p_proration_group_id
265 OUT : p_start_date
266 Notes : Public
267 ****************************************************************************/
268 FUNCTION prorate_start_date(p_assignment_action_id IN NUMBER ,
269 p_proration_group_id IN NUMBER
270 ) RETURN DATE IS
271 l_start_date DATE ;
272 l_end_date DATE ;
273 l_business_group_id NUMBER ;
274 BEGIN
275 time_period_internal(p_assignment_action_id => p_assignment_action_id ,
276 p_proration_group_id => p_proration_group_id ,
277 p_business_group_id => l_business_group_id ,
278 p_start_date => l_start_date ,
279 p_end_date => l_end_date );
280
281 RETURN l_start_date;
282 END prorate_start_date;
283
284
285
286 /****************************************************************************
287 Name : time_period
288 Purpose : The procedure returns 3 tables. This procedure is called by
289 the interpreter.
290 Arguments :
291 IN : p_assignment_action_id
292 p_proration_group_id
293 p_start_date
294 p_end_date
295 OUT : p_business_group_id
296 p_start_date
297 p_end_date
298 Notes : Private
299 ****************************************************************************/
300 PROCEDURE time_period(p_assignment_action_id IN NUMBER ,
301 p_proration_group_id IN NUMBER ,
302 p_element_entry_id IN NUMBER ,
303 p_business_group_id OUT NOCOPY NUMBER ,
304 p_start_date OUT NOCOPY DATE ,
305 p_end_date OUT NOCOPY DATE ) AS
306
307 l_year NUMBER ;
308 l_proration_type VARCHAR2(10);
309 l_legislation_code VARCHAR2(40);
310 l_esd DATE ;
311 l_eed DATE ;
312
313 -- The following cursor selects the start and end date of the element entry id.
314 CURSOR c_element_start_end IS
315 SELECT MIN(effective_start_date) esd,
316 MAX(effective_end_date) eed
317 FROM pay_element_entries_f
318 WHERE element_entry_id = p_element_entry_id;
319 --
320 BEGIN
321 --
322 time_period_internal(p_assignment_action_id,
323 p_proration_group_id,
324 p_business_group_id,
325 p_start_date,
326 p_end_date);
327 --
328 /** The following code ensures that we are interested in the time frame
329 in which an element entry Id was valid.
330
331 Lets say the time frame selected by using earlier instructions is
332
333 15-JAN-1990 31-OCT-1990
334 |-----------------------------------------------------------|
335
336 Shown below is the life time of the element entry id passed as an input.
337 |--------------------------------|
338 13-MAR-1990 15-AUG-1990
339
340 We should select the time as 13-MAR-1990 and 15-AUG-1990.
341
342 On the parallel lines if the life time of element entry id is
343 |----------------------------------------------------------|
344 13-MAR-1990 30-NOV-1990
345 Then we should select
346 13-MAR-1990 and 31-OCT-1990 as the time frame.
347
348 Similarly if the life time of element entry id is
349 |----------------------------------------------------------|
350 01-JAN-1990 30-SEP-1990
351 Then we should select
352 15-JAN-1990 and 30-SEP-1990 as the time frame.
353
354 **/
355 --
356 FOR ces IN c_element_start_end
357 LOOP
358 l_esd := ces.esd;
359 l_eed := ces.eed;
360 END LOOP;
361 --
362 IF (NVL(l_esd, p_start_date) > p_start_date) THEN
363 p_start_date := l_esd;
364 END IF;
365 --
366 IF (NVL(l_eed, p_end_date) < p_end_date) THEN
367 p_end_date := l_eed;
368 END IF;
369 /**
370 The following test case has been written to make sure that if the start
371 date is 01-JAN-YYYY, and the proration event occurs on 01-JAN-YYYY. We
372 do not want to report this proration event. Therefore we advance the date
373 by 1. For end date this criterion is not true.
374 ***/
375 --
376 if (g_traces) then
377 hr_utility.trace('Dates are ' || TO_CHAR(p_start_date) || ' ' || TO_CHAR(p_end_date));
378 end if;
379 --
380 END time_period;
381
382 /****************************************************************************
383 Name : time_fn
384 Purpose : The function return the start date.
385 Arguments :
386 IN : p_assignment_action_id
387 p_proration_group_id
388 p_element_entry_id
389 Notes : Public
390 ****************************************************************************/
391 FUNCTION time_fn(p_assignment_action_id IN NUMBER ,
392 p_proration_group_id IN NUMBER ,
393 p_element_entry_id IN NUMBER ) RETURN DATE IS
394 l_start_date DATE ;
395 l_end_date DATE ;
396 l_business_group_id NUMBER ;
397 BEGIN
398 /***
399 Finding the time period we are interested in. Procedure time_period
400 selects the appropriate time periods. This procedure also finds out
401 the business group id.
402 ***/
403 time_period(p_assignment_action_id => p_assignment_action_id ,
404 p_proration_group_id => p_proration_group_id ,
405 p_element_entry_id => p_element_entry_id ,
406 p_business_group_id => l_business_group_id ,
407 p_start_date => l_start_date ,
408 p_end_date => l_end_date );
409
410 RETURN l_start_date;
411 END time_fn;
412
413
414 /****************************************************************************
415 Name : unique_sort
416 Purpose : This procedure sorts the dates and then generate the listing
417 of unique dates.
418 Arguments :
419 IN OUT : p_proration_dates_temp
420 IN p_proration_dates
421 OUT : p_proration_type
422 Notes : PRIVATE
423 ****************************************************************************/
424
425
426 PROCEDURE unique_sort(p_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
427 p_proration_dates IN OUT NOCOPY t_proration_dates_table_type ,
428 p_change_type_temp IN OUT NOCOPY t_proration_type_table_type,
429 p_change_type IN OUT NOCOPY t_proration_type_table_type,
430 p_proration_type_temp IN OUT NOCOPY t_proration_type_table_type,
431 p_proration_type OUT NOCOPY t_proration_type_table_type ,
432 p_internal_mode IN OUT NOCOPY varchar2)
433 AS
434 l_table_count NUMBER := 0;
435 l_counter NUMBER := 0;
436 l_sort_i NUMBER := 0;
437 l_sort_j NUMBER := 0;
438 l_var NUMBER := 0;
439 l_unique NUMBER := 0;
440 l_temp_date DATE ;
441 l_temp_type VARCHAR2(40);
442 l_temp_pro_type VARCHAR2(40);
443 BEGIN
444 l_table_count := p_proration_dates_temp.COUNT;
445 /*
446 FOR l_sort_i IN 1..l_table_count LOOP
447 hr_utility.trace('Unique Sort : Date = '||p_proration_dates_temp(l_sort_i));
448 hr_utility.trace(' Style = '||p_proration_type_temp(l_sort_i));
449 END LOOP;
450 */
451
452 FOR l_sort_i IN 1..l_table_count
453 LOOP
454 l_temp_date := p_proration_dates_temp(l_sort_i);
455 l_temp_type := p_change_type_temp(l_sort_i);
456 l_temp_pro_type := p_proration_type_temp(l_sort_i);
457 l_var := l_sort_i ;
458 FOR l_sort_j IN l_sort_i..l_table_count
459 LOOP
460 IF (p_proration_dates_temp(l_sort_j) <
461 l_temp_date) THEN
462 l_temp_date := p_proration_dates_temp(l_sort_j) ;
463 l_temp_type := p_change_type_temp(l_sort_j) ;
464 l_temp_pro_type := p_proration_type_temp(l_sort_j);
465 l_var := l_sort_j;
466 END IF;
467 END LOOP;
468 p_proration_dates_temp(l_var) := p_proration_dates_temp(l_sort_i);
469 p_proration_dates_temp(l_sort_i) := l_temp_date;
470 p_change_type_temp(l_var) := p_change_type_temp(l_sort_i);
471 p_change_type_temp(l_sort_i) := l_temp_type;
472 p_proration_type_temp(l_var) := p_proration_type_temp(l_sort_i);
473 p_proration_type_temp(l_sort_i) := l_temp_pro_type;
474 END LOOP;
475
476 --hr_utility.trace('Sorting finished');
477
478 IF (l_table_count >= 1) THEN
479 l_temp_date := p_proration_dates_temp(1);
480 p_proration_dates(1) := p_proration_dates_temp(1);
481 l_temp_type := p_change_type_temp(1);
482 p_change_type(1) := p_change_type_temp(1);
483 l_temp_pro_type := p_proration_type_temp(1);
484 p_proration_type(1) := p_proration_type_temp(1);
485 END IF;
486
487 l_counter := 1;
488
489 --hr_utility.trace('Finding Unique Dates');
490
491 FOR l_unique IN 1..l_table_count
492 LOOP
493 --hr_utility.trace('Date = '||p_proration_dates_temp(l_unique));
494 --hr_utility.trace('Style = '||p_proration_type_temp(l_unique));
495 /* Proration Uniqueness is different to others */
496 if (p_internal_mode = 'PRORATION') then
497 IF (l_temp_date <> p_proration_dates_temp(l_unique)) then
498 l_counter := l_counter + 1 ;
499 p_proration_dates(l_counter) :=
500 p_proration_dates_temp(l_unique);
501 p_change_type(l_counter) :=
502 p_change_type_temp(l_unique);
503 p_proration_type(l_counter) :=
504 p_proration_type_temp(l_unique);
505 l_temp_date := p_proration_dates_temp(l_unique);
506 l_temp_type := p_change_type_temp(l_unique);
507 l_temp_pro_type := p_proration_type_temp(l_unique);
508 ELSE
509 if (p_proration_type_temp(l_unique) = 'R') then
510 p_proration_type(l_counter) :=
511 p_proration_type_temp(l_unique);
512 end if;
513 END IF;
514 else
515 IF (l_temp_date <> p_proration_dates_temp(l_unique) OR
516 l_temp_type <> p_change_type_temp(l_unique)) THEN
517 l_counter := l_counter + 1 ;
518 p_proration_dates(l_counter) :=
519 p_proration_dates_temp(l_unique);
520 p_change_type(l_counter) :=
521 p_change_type_temp(l_unique);
522 --p_proration_type(l_counter) := 'E' ;
523 l_temp_date := p_proration_dates_temp(l_unique);
524 l_temp_type := p_change_type_temp(l_unique);
525 END IF;
526 end if;
527 END LOOP;
528 END unique_sort;
529
530 PROCEDURE event_group_info
531 (
532 p_assignment_action_id IN NUMBER DEFAULT NULL ,
533 p_element_entry_id IN NUMBER DEFAULT NULL ,
534 p_event_group_id OUT NOCOPY NUMBER,
535 p_assignment_id OUT NOCOPY NUMBER,
536 p_business_group_id OUT NOCOPY NUMBER,
537 p_start_date OUT NOCOPY DATE,
538 p_end_date OUT NOCOPY DATE
539 ) AS
540
541 l_date_earned DATE ;
542
543 BEGIN
544
545 -- The following statement selects the date earned.
546 -- Date Earned is used while determining the Proration Group id.
547
548 SELECT ppa.date_earned
549 INTO l_date_earned
550 FROM pay_assignment_actions paa,
551 pay_payroll_actions ppa
552 WHERE paa.assignment_action_id = p_assignment_action_id
553 AND paa.payroll_action_id = ppa.payroll_action_id ;
554
555 -- The following statement selects the Proration Group Id
556
557 SELECT DISTINCT pee.assignment_id ,
558 pet.proration_group_id
559 into p_assignment_id,p_event_group_id
560 FROM pay_element_entries_f pee,
561 pay_element_types_f pet
562 WHERE pee.element_entry_id = p_element_entry_id
563 AND pee.element_type_id = pet.element_type_id
564 AND pee.effective_start_date <= l_date_earned
565 AND pee.effective_end_date >= time_fn(p_assignment_action_id,
566 pet.proration_group_id ,
567 p_element_entry_id )
568 AND l_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date;
569
570
571 -- Finding the time period we are interested in. Procedure time_period
572 -- selects the appropriate time periods. This procedure also finds out
573 -- the business group id.
574
575 time_period(p_assignment_action_id => p_assignment_action_id ,
576 p_proration_group_id => p_event_group_id ,
577 p_element_entry_id => p_element_entry_id ,
578 p_business_group_id => p_business_group_id ,
579 p_start_date => p_start_date ,
580 p_end_date => p_end_date );
581
582 if (g_traces) then
583 hr_utility.trace('Date Earned ' || TO_CHAR(l_date_earned,'DD-MON-YYYY'));
584 end if;
585
586 END;
587
588 procedure event_group_tables
589 (
590 p_event_group_id IN NUMBER,
591 p_distinct_tab IN OUT NOCOPY t_distinct_table
592 ) AS
593
594 -- The following cursor selects the distinct table_names associated with a
595 -- proration_group_id.
596
597 CURSOR c_distinct_table(p_proration_group_id IN NUMBER) IS
598 SELECT DISTINCT pdt.dated_table_id table_id ,
599 pdt.table_name table_name ,
600 nvl(pdt.dyn_trigger_type,'T') dyt_type ,
601 pdt.start_date_name start_date_name ,
602 pdt.end_date_name end_date_name ,
603 pdt.surrogate_key_name surrogate_key_name,
604 pde.datetracked_event_id datetracked_event_id,
605 pde.column_name column_name ,
606 pde.update_type update_type ,
607 pde.proration_style proration_type,
608 pdt.owner owner
609 FROM pay_datetracked_events pde,
610 pay_dated_tables pdt
611 WHERE pde.event_group_id = p_proration_group_id
612 AND pdt.dated_table_id = pde.dated_table_id
613 order by pdt.dated_table_id,pde.update_type; --ordering vital bug 3598389
614
615 l_tab_counter NUMBER ;
616 l_tab_ori_counter NUMBER ;
617
618
619 BEGIN
620
621 -- The following cursor selects the distinct/Unique table Ids.
622 -- POTENTIAL CACHING CANDIDATE
623 -- Caching in a PL/SQL table on proration_group_id
624
625 if (p_event_group_id is not null) then
626 IF (t_proration_group_tab.EXISTS(p_event_group_id) = FALSE) THEN
627
628 l_tab_counter := p_distinct_tab.COUNT + 1;
629 l_tab_ori_counter := l_tab_counter ;
630
631 t_proration_group_tab(p_event_group_id).range_start := 0;
632 t_proration_group_tab(p_event_group_id).range_end := 0 ;
633
634 if (g_traces) then
635 hr_utility.trace('Miss in Cache');
636 end if;
637 FOR cdt IN c_distinct_table(p_event_group_id)
638 LOOP
639 if (g_dbg) then
640 hr_utility.trace('Store Event in Cache: '||l_tab_counter);
641 end if;
642 p_distinct_tab(l_tab_counter).table_id := cdt.table_id ;
643 p_distinct_tab(l_tab_counter).table_name := cdt.table_name ;
644 p_distinct_tab(l_tab_counter).owner := cdt.owner ;
645 p_distinct_tab(l_tab_counter).dyt_type := cdt.dyt_type ;
646 p_distinct_tab(l_tab_counter).surrogate_key_name := cdt.surrogate_key_name;
647 p_distinct_tab(l_tab_counter).start_date_name := cdt.start_date_name ;
648 p_distinct_tab(l_tab_counter).end_date_name := cdt.end_date_name ;
649 p_distinct_tab(l_tab_counter).datetracked_event_id := cdt.datetracked_event_id ;
650 p_distinct_tab(l_tab_counter).update_type := cdt.update_type ;
651 p_distinct_tab(l_tab_counter).column_name := cdt.column_name ;
652 p_distinct_tab(l_tab_counter).proration_type := cdt.proration_type ;
653
654 t_proration_group_tab(p_event_group_id).range_start := l_tab_ori_counter;
655 t_proration_group_tab(p_event_group_id).range_end := l_tab_counter ;
656
657 l_tab_counter := l_tab_counter + 1;
658 END LOOP;
659 END IF;
660 end if;
661 END;
662
663 procedure event_group_tables
664 (
665 p_event_group_id IN NUMBER
666 ) AS
667 BEGIN
668 event_group_tables(p_event_group_id, t_distinct_tab);
669 END;
670
671
672 PROCEDURE event_group_table_inserted
673 (
674 p_date_counter IN OUT NOCOPY NUMBER,
675 p_assignment_id IN NUMBER,
676 p_effective_date IN date,
677 p_surrogate_key IN NUMBER,
678 p_business_group_id IN NUMBER,
679 p_dated_table_id IN NUMBER,
680 p_start_date_name IN VARCHAR2,
681 p_end_date_name IN VARCHAR2,
682 l_proration_type IN VARCHAR2,
683 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
684 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
685 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
686 t_detailed_output in OUT NOCOPY t_detailed_output_table_type
687 ) AS
688
689 insert_row number;
690 upd_end_date number;
691 upd_start_date number;
692
693 BEGIN
694
695
696
697 SELECT count(*)
698 into insert_row
699 FROM pay_process_events ppe,
700 pay_event_updates peu
701 WHERE ppe.assignment_id=p_assignment_id
702 AND ppe.surrogate_key=p_surrogate_key
703 AND ppe.business_group_id=p_business_group_id
704 AND ppe.event_update_id=peu.event_update_id
705 AND peu.event_type='I'
706 AND ppe.effective_date=p_effective_date
707 AND peu.dated_table_id=p_dated_table_id;
708
709 SELECT count(*)
710 INTo upd_end_date
711 FROM pay_process_events ppe,
712 pay_event_updates peu
713 WHERE ppe.assignment_id=p_assignment_id
714 AND ppe.surrogate_key=p_surrogate_key
715 AND ppe.business_group_id=p_business_group_id
716 AND ppe.event_update_id=peu.event_update_id
717 AND peu.event_type='U'
718 AND peu.column_name=p_end_date_name
719 AND ppe.calculation_date+1=p_effective_date
720 AND peu.dated_table_id=p_dated_table_id;
721
722 SELECT count(*)
723 INTo upd_start_date
724 FROM pay_process_events ppe,
725 pay_event_updates peu
726 WHERE ppe.assignment_id=p_assignment_id
727 AND ppe.surrogate_key=p_surrogate_key
728 AND ppe.business_group_id=p_business_group_id
729 AND ppe.event_update_id=peu.event_update_id
730 AND peu.event_type='U'
731 AND peu.column_name=p_start_date_name
732 AND ppe.calculation_date=p_effective_date
733 AND peu.dated_table_id=p_dated_table_id;
734
735
736 if (upd_start_date+upd_end_date <> insert_row)
737 then
738 t_proration_dates_temp(p_date_counter):= p_effective_date;
739 t_proration_change_type(p_date_counter):= 'I';
740 t_proration_type(p_date_counter):= l_proration_type;
741 t_detailed_output(p_date_counter).dated_table_id := p_dated_table_id;
742 t_detailed_output(p_date_counter).datetracked_event := 'I';
743 t_detailed_output(p_date_counter).surrogate_key := p_surrogate_key;
744 t_detailed_output(p_date_counter).effective_date := p_effective_date;
745 t_detailed_output(p_date_counter).proration_type := l_proration_type;
746 p_date_counter := p_date_counter + 1;
747 end if;
748
749 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
750
751
752 END;
753
754
755
756
757 PROCEDURE create_statement
758 (
759 p_proration_group_id IN NUMBER,
760 p_table_id IN NUMBER,
761 p_table_name IN VARCHAR2,
762 p_surrogate_key_name IN VARCHAR2,
763 p_surrogate_key IN NUMBER,
764 p_start_date_name IN VARCHAR2,
765 p_end_date_name IN VARCHAR2,
766 p_statement OUT NOCOPY VARCHAR2,
767 p_global_env IN OUT NOCOPY t_global_env_rec,
768 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
769 p_dynamic_counter OUT NOCOPY NUMBER
770 ) AS
771
772
773 l_loop_flag BOOLEAN ;
774 l_column_string VARCHAR2(2000) ;
775
776
777 BEGIN
778
779 -- The following cursor selects distinct columns for the table id.The logic
780 -- then creates a string of all the column names.
781
782 l_column_string := NULL;
783 l_loop_flag := FALSE;
784 t_dynamic_sql.DELETE;
785 p_dynamic_counter := 0;
786
787 -- POTENTIAL CACHING CANDIDATE
788 -- Code below is a potential caching candidate. We can cahche on
789 -- l_proration_group_id or table_id.
790 FOR k in p_global_env.monitor_start_ptr..p_global_env.monitor_end_ptr loop
791 -- if this event is on the table were checking and its an U
792 IF (glo_monitored_events(k).table_id = p_table_id
793 and glo_monitored_events(k).update_type = 'U'
794 and glo_monitored_events(k).column_name is not null) THEN
795
796 p_dynamic_counter := p_dynamic_counter + 1;
797
798 t_dynamic_sql(p_dynamic_counter).column_name := glo_monitored_events(k).column_name;
799 t_dynamic_sql(p_dynamic_counter).date_tracked_id := glo_monitored_events(k).datetracked_event_id;
800 t_dynamic_sql(p_dynamic_counter).proration_style := glo_monitored_events(k).proration_type;
801
802 if (l_loop_flag = TRUE) then
803 l_column_string := l_column_string || ',' || glo_monitored_events(k).column_name;
804 else
805 l_column_string := glo_monitored_events(k).column_name;
806 l_loop_flag := TRUE;
807 end if;
808 END IF;
809 END LOOP;
810
811
812 p_statement := 'SELECT ' || l_column_string ||
813 ' FROM ' ||
814 p_table_name ||
815 ' WHERE ' || p_surrogate_key_name || ' = :p_surrogate_key ' ||
816 ' AND :col1 BETWEEN ' ||
817 p_start_date_name || ' AND ' ||
818 p_end_date_name;
819 if (g_dbg) then
820 hr_utility.trace('-Dynamic SQL: ' || p_statement);
821 end if;
822
823 END;
824
825
826 PROCEDURE execute_statement
827 (
828 p_statement IN VARCHAR2,
829 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
830 p_surrogate_key IN NUMBER,
831 p_effective_date IN DATE,
832 p_start_date_name IN VARCHAR2,
833 p_end_date_name IN VARCHAR2,
834 p_dynamic_counter IN NUMBER,
835 p_updated_column_name IN VARCHAR2,
836 p_final_effective_date OUT NOCOPY DATE
837 ) AS
838
839
840 l_dummy NUMBER ;
841 l_new_sql_fetch NUMBER ;
842 l_old_sql_fetch NUMBER ;
843 l_counter NUMBER ;
844 l_cursor_id INTEGER ;
845
846 BEGIN
847
848 -- The following code creates the dynamic SQL for the column names selected
849 -- above.
850 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
851 hr_utility.trace(p_statement);
852 DBMS_SQL.PARSE(l_cursor_id , p_statement , DBMS_SQL.V7);
853 --
854 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_surrogate_key', p_surrogate_key);
855 IF (p_updated_column_name = p_start_date_name) THEN
856 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date - 1);
857 if (g_dbg) then
858 hr_utility.trace('Effective Start Date changed');
859 hr_utility.trace('date = '||(p_effective_date - 1));
860 hr_utility.trace('key = '||p_surrogate_key);
861 end if;
862 ELSIF (p_updated_column_name = p_end_date_name) THEN
863 --
864 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date);
865 if (g_dbg) then
866 hr_utility.trace('date = '||p_effective_date);
867 hr_utility.trace('key = '||p_surrogate_key);
868 end if;
869 --
870 END IF;
871
872 FOR l_counter IN 1..p_dynamic_counter
873 LOOP
874 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, l_counter, t_dynamic_sql(l_counter).old_value, 100);
875 END LOOP;
876
877 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
878
879 LOOP
880 -- This loop will always return a single row or no row
881 l_old_sql_fetch := 0;
882 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
883 EXIT;
884 END IF;
885
886 -- The following loop executes for all the columns
887 FOR l_counter IN 1..p_dynamic_counter
888 LOOP
889 l_old_sql_fetch := 1;
890 -- l_old_sql_fetch variable will become = 1 whenever the
891 -- LOOP is executed. Whenever l_old_sql_fetch becomes = 1,
892 -- It means that a row is fetched from the cursor. This
893 -- variable will be used to decide whether a Delete event occured.
894 DBMS_SQL.COLUMN_VALUE(l_cursor_id, l_counter, t_dynamic_sql(l_counter).old_value);
895 END LOOP;
896 END LOOP;
897
898 IF (p_updated_column_name = p_start_date_name) THEN
899 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date);
900 p_final_effective_date := p_effective_date;
901 if (g_dbg) then
902 hr_utility.trace('Effective Start Date changed');
903 hr_utility.trace('date = '||p_effective_date);
904 hr_utility.trace('key = '||p_surrogate_key);
905 end if;
906 ELSIF (p_updated_column_name = p_end_date_name) THEN
907 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date + 1);
908 p_final_effective_date := p_effective_date + 1;
909 if (g_dbg) then
910 hr_utility.trace('Effective End Date changed');
911 hr_utility.trace('date = '||(p_effective_date + 1));
912 hr_utility.trace('key = '||p_surrogate_key);
913 end if;
914 END IF;
915
916 -- The following loop executes for all the columns
917
918 FOR l_counter IN 1..p_dynamic_counter
919 LOOP
920 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, l_counter,
921 t_dynamic_sql(l_counter).new_value, 100);
922 END LOOP;
923 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
924
925 if (g_dbg) then
926 hr_utility.trace('Second statement executed ');
927 end if;
928
929 LOOP
930
931 -- This loop will always return a single row or no row
932
933 l_new_sql_fetch := 0;
934
935 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
936 EXIT;
937 END IF;
938
939 FOR l_counter IN 1..p_dynamic_counter
940 LOOP
941 l_new_sql_fetch := 1;
942 DBMS_SQL.COLUMN_VALUE(l_cursor_id, l_counter,t_dynamic_sql(l_counter).new_value);
943 if (g_traces) then
944 hr_utility.trace('old = '||t_dynamic_sql(l_counter).old_value||' new = '||t_dynamic_sql(l_counter).new_value);
945 end if;
946
947 END LOOP;
948 END LOOP;
949
950 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
951 END;
952
953 PROCEDURE add_event_procedure
954 (
955 p_table_id IN NUMBER,
956 p_business_group_id in NUMBER,
957 p_column_name IN VARCHAR2,
958 p_global_env IN OUT NOCOPY t_global_env_rec
959 )
960 IS
961 --
962 -- The following cursor selects the third party PL/SQL procedures names
963 -- from pay_event_procedure
964 CURSOR c_event_proc(p_table_id IN NUMBER ,
965 p_column_name IN VARCHAR2 ,
966 p_bg_id IN NUMBER) IS
967 SELECT pep.procedure_name
968 FROM pay_event_procedures pep,
969 per_business_groups_perf pbg
970 WHERE pep.dated_table_id = p_table_id
971 AND UPPER(pep.column_name) = UPPER(p_column_name)
972 AND nvl(pep.procedure_type, 'E') = 'E'
973 AND pbg.business_group_id = p_bg_id
974 AND ( ( pep.business_group_id = pbg.business_group_id
975 and pep.legislation_code is null)
976 or ( pep.legislation_code = pbg.legislation_code
977 and pep.business_group_id is null)
978 or ( pep.legislation_code is null
979 and pep.business_group_id is null)
980 )
981 ORDER BY NVL(pep.business_group_id, -100) asc,
982 NVL(pep.legislation_code, ' ') asc;
983 --
984 /*
985 Order by clause will ensure that the rows that are selected in the following
986 order i.e. Global, Legislation, and Client specific.
987
988 <Null> <Null> in business_group_id, and legislation_code resp = GLOBAL
989 <Null> XXX in business_group_id, and legislation_code resp = LEGISLATION
990 XXX <Null> in business_group_id, and legislation_code resp = CLIENT specific.
991
992 The typical Data in the table will be
993
994 Procedure_Name Business Group Id Legislation_code
995 -------------- ----------------- ----------------
996 Global <NULL> <NULL>
997 Legislation <NULL> US
998 Client 100 <NULL>
999
1000 We want to sort this in the order of Global, Legislation, and then Client.
1001
1002 The NVLs will generate the output as
1003
1004 Procedure_Name Business Group Id Legislation_code
1005 -------------- ----------------- ----------------
1006 Global -100 ' '
1007 Legislation -100 US
1008 Client 100 ' '
1009
1010 If we order by the abouve output Business Group Id, Legislation_code
1011 We will get the output as
1012
1013 Procedure_Name Business Group Id Legislation_code
1014 -------------- ----------------- ----------------
1015 Global -100 ' '
1016 Legislation -100 US
1017 Client 100 ' '
1018 */
1019 --
1020 new_idx number;
1021 proc_found boolean;
1022 evt_ptr number;
1023 --
1024 BEGIN
1025 --
1026 new_idx := glo_table_columns.count + 1;
1027 glo_table_columns(new_idx).column_name := p_column_name;
1028 glo_table_columns(new_idx).evt_proc_start_ptr := null;
1029 glo_table_columns(new_idx).evt_proc_end_ptr := null;
1030 glo_table_columns(new_idx).next_ptr :=
1031 glo_column_hash_tab(p_table_id);
1032 glo_column_hash_tab(p_table_id) := new_idx;
1033 --
1034 proc_found := FALSE;
1035 for evtrec in c_event_proc(p_table_id,
1036 p_column_name,
1037 p_business_group_id) loop
1038 --
1039 evt_ptr := glo_event_procedures.count +1;
1040 --
1041 if (proc_found = FALSE) then
1042 glo_table_columns(new_idx).evt_proc_start_ptr := evt_ptr;
1043 proc_found := TRUE;
1044 end if;
1045 --
1046 glo_event_procedures(evt_ptr).procedure_name :=
1047 evtrec.procedure_name;
1048 --
1049 end loop;
1050 --
1051 if (proc_found = TRUE) then
1052 glo_table_columns(new_idx).evt_proc_end_ptr := evt_ptr;
1053 end if;
1054 --
1055 END add_event_procedure;
1056
1057 PROCEDURE load_event_procedure
1058 (
1059 p_table_id IN NUMBER,
1060 p_business_group_id in NUMBER,
1061 p_column_name IN VARCHAR2,
1062 p_table_column_idx OUT NOCOPY NUMBER,
1063 p_global_env IN OUT NOCOPY t_global_env_rec
1064 )
1065 IS
1066 --
1067 proc_found boolean;
1068 curr_idx number;
1069 --
1070 BEGIN
1071 --
1072 if (glo_column_hash_tab.exists(p_table_id)) then
1073 --
1074 proc_found := FALSE;
1075 curr_idx := glo_column_hash_tab(p_table_id);
1076 while (curr_idx is not null and proc_found <> TRUE) loop
1077 --
1078 if (glo_table_columns(curr_idx).column_name =
1079 p_column_name) then
1080 --
1081 proc_found := TRUE;
1082 p_table_column_idx := curr_idx;
1083 --
1084 else
1085 --
1086 curr_idx := glo_table_columns(curr_idx).next_ptr;
1087 --
1088 end if;
1089 end loop;
1090 --
1091 if (proc_found = FALSE) then
1092 --
1093 add_event_procedure (p_table_id => p_table_id,
1094 p_business_group_id => p_business_group_id,
1095 p_column_name => p_column_name,
1096 p_global_env => p_global_env);
1097 p_table_column_idx := glo_column_hash_tab(p_table_id);
1098 --
1099 end if;
1100
1101 --
1102 else
1103 --
1104 glo_column_hash_tab(p_table_id) := null;
1105 add_event_procedure (p_table_id => p_table_id,
1106 p_business_group_id => p_business_group_id,
1107 p_column_name => p_column_name,
1108 p_global_env => p_global_env);
1109 p_table_column_idx := glo_column_hash_tab(p_table_id);
1110 --
1111 end if;
1112 --
1113 END load_event_procedure;
1114
1115 PROCEDURE event_group_procedure
1116 (
1117 p_table_id IN NUMBER,
1118 p_element_entry_id IN NUMBER,
1119 p_assignment_action_id IN NUMBER,
1120 p_business_group_id in NUMBER,
1121 p_surrogate_key IN NUMBER,
1122 p_column_name IN VARCHAR2,
1123 p_old_value IN VARCHAR2,
1124 p_new_value IN VARCHAR2,
1125 p_output_result IN OUT NOCOPY VARCHAR2,
1126 p_final_effective_date IN DATE default null,
1127 p_global_env IN OUT NOCOPY t_global_env_rec
1128 ) AS
1129
1130 l_proc_string VARCHAR2(400) ;
1131 l_cursor_id INTEGER ;
1132 l_dummy NUMBER ;
1133 l_proc_name VARCHAR2(40) ;
1134 curr_idx NUMBER;
1135
1136
1137
1138 BEGIN
1139 p_output_result := 'TRUE';
1140 --
1141 load_event_procedure
1142 (
1143 p_table_id => p_table_id,
1144 p_business_group_id => p_business_group_id,
1145 p_column_name => p_column_name,
1146 p_table_column_idx => curr_idx,
1147 p_global_env => p_global_env
1148 );
1149 --
1150 if (glo_table_columns(curr_idx).evt_proc_start_ptr is not null)
1151 then
1152 FOR evt_idx IN glo_table_columns(curr_idx).evt_proc_start_ptr..
1153 glo_table_columns(curr_idx).evt_proc_end_ptr
1154 LOOP
1155 l_proc_name := glo_event_procedures(evt_idx).procedure_name;
1156 -- Execute Procedure name
1157
1158 if (g_traces) then
1159 hr_utility.trace('Procedure Name ' || l_proc_name);
1160 end if;
1161
1162 l_proc_string := 'BEGIN ' || l_proc_name || '(' ||
1163 'p_surrogate_key => :col1,' ||
1164 'p_element_entry_id => :col2,' ||
1165 'p_assignment_action_id => :col3,' ||
1166 'p_column_name => :col4,' ||
1167 'p_old_value => :col5,' ||
1168 'p_new_value => :col6,' ||
1169 'p_output_result => :col7,' ||
1170 'p_date => :col8' ||
1171 '); END;';
1172
1173 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1174
1175 if (g_dbg) then
1176 hr_utility.trace('Parameters');
1177 hr_utility.trace('p_surrogate_key = '||p_surrogate_key);
1178 hr_utility.trace('p_element_entry_id = '||p_element_entry_id);
1179 hr_utility.trace('p_assignment_action_id = '||p_assignment_action_id);
1180 hr_utility.trace('p_column_name = '||p_column_name);
1181 hr_utility.trace('p_old_value = '||p_old_value);
1182 hr_utility.trace('p_new_value = '||p_new_value);
1183 end if;
1184 DBMS_SQL.PARSE(l_cursor_id, l_proc_string, DBMS_SQL.V7);
1185
1186 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col1',p_surrogate_key);
1187
1188 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col2', p_element_entry_id);
1189 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col3', p_assignment_action_id);
1190 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col4', p_column_name);
1191 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col5', p_old_value);
1192 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col6', p_new_value);
1193 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col7', p_output_result, 40);
1194 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':col8', p_final_effective_date);
1195 if (g_dbg) then
1196 hr_utility.trace('All Variables Bound');
1197 end if;
1198
1199 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1200
1201 if (g_dbg) then
1202 hr_utility.trace('Procedure Executed');
1203 end if;
1204
1205 DBMS_SQL.VARIABLE_VALUE(l_cursor_id, ':col7', p_output_result);
1206 --hr_utility.trace('Got Results');
1207
1208 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1209
1210 --hr_utility.trace('Closed cursor');
1211
1212 IF (p_output_result = 'FALSE') THEN
1213 -- False means no proration event occured
1214 hr_utility.trace('Not a valid event');
1215 EXIT;
1216 END IF;
1217 END LOOP;
1218 end if;
1219 END;
1220
1221 /* ----------------------------------------------------------
1222 Add an identified event to our store of identified events
1223 ---------------------------------------------------------- */
1224 PROCEDURE add_found_event
1225 (
1226 p_effective_date IN DATE,
1227 p_creation_date IN DATE DEFAULT NULL,
1228 p_update_type IN VARCHAR2,
1229 p_change_mode IN VARCHAR2,
1230 p_proration_type IN VARCHAR2,
1231 p_datetracked_event IN VARCHAR2,
1232 p_column_name IN VARCHAR2 default 'none',
1233 p_old_val IN VARCHAR2 default null,
1234 p_new_val IN VARCHAR2 default null,
1235 p_change_values IN VARCHAR2 default null,
1236 p_element_entry_id IN NUMBER default null,
1237 p_surrogate_key IN VARCHAR2,
1238 p_dated_table_id IN NUMBER,
1239 p_date_counter IN OUT NOCOPY number,
1240 p_global_env IN OUT NOCOPY t_global_env_rec,
1241 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1242 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1243 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1244 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type
1245 ) AS
1246
1247 l_proc VARCHAR2(80) := 'add_found_event';
1248
1249 BEGIN
1250 --p_update_type, eg I.E,
1251 --p_change_mode, eg DATE_EARNED,
1252 --proration_type,
1253 t_proration_dates_temp(p_date_counter):= p_effective_date;
1254
1255 t_proration_change_type(p_date_counter):= p_update_type;
1256
1257 t_proration_type(p_date_counter):= p_proration_type;
1258
1259 t_detailed_output(p_date_counter).dated_table_id := p_dated_table_id;
1260 t_detailed_output(p_date_counter).datetracked_event :=p_datetracked_event;
1261 t_detailed_output(p_date_counter).surrogate_key := p_surrogate_key;
1262 t_detailed_output(p_date_counter).effective_date := p_effective_date;
1263 t_detailed_output(p_date_counter).creation_date := p_creation_date;
1264 t_detailed_output(p_date_counter).update_type := p_update_type;
1265 t_detailed_output(p_date_counter).proration_type := p_proration_type;
1266 t_detailed_output(p_date_counter).change_mode := p_change_mode;
1267 t_detailed_output(p_date_counter).column_name := p_column_name;
1268 t_detailed_output(p_date_counter).old_value := p_old_val;
1269 t_detailed_output(p_date_counter).new_value := p_new_val;
1270 t_detailed_output(p_date_counter).change_values
1271 := nvl(p_change_values,p_old_val||' -> '||p_new_val);
1272 --
1273 -- If this is for a specific element entry, the maintain the
1274 -- entry hash cache
1275 --
1276 if (p_element_entry_id is not null) then
1277 if (glo_ee_hash_table.exists(p_element_entry_id)) then
1278 t_detailed_output(p_date_counter).next_ee :=
1279 glo_ee_hash_table(p_element_entry_id);
1280 glo_ee_hash_table(p_element_entry_id) := p_date_counter;
1281 else
1282 t_detailed_output(p_date_counter).next_ee := null;
1283 glo_ee_hash_table(p_element_entry_id) := p_date_counter;
1284 end if;
1285 t_detailed_output(p_date_counter).element_entry_id := p_element_entry_id;
1286 else
1287 t_detailed_output(p_date_counter).element_entry_id := null;
1288 t_detailed_output(p_date_counter).next_ee := null;
1289 end if;
1290
1291
1292 if (g_traces) then
1293 hr_utility.trace('>> FOUND EVENT: '||p_datetracked_event||', desc '||t_detailed_output(p_date_counter).change_values);
1294 end if;
1295
1296 p_date_counter := p_date_counter + 1;
1297 if (g_dbg) then
1298 hr_utility.trace(' For base record :' || p_surrogate_key );
1299 hr_utility.trace(' On Table :' || p_dated_table_id );
1300 hr_utility.trace(' Event Type :' || p_update_type );
1301 hr_utility.trace('>> adding at pos p_date_counter: '||p_date_counter);
1302 end if;
1303
1304 END add_found_event;
1305
1306 --
1307 -- Name : validate_affected_actions
1308 -- Description
1309 -- This procedure is used by RetroNotification and CC to
1310 -- ensure that assignment actions have been affected by the
1311 -- change
1312 --
1313 procedure validate_affected_actions(p_assignment_id in number,
1314 p_effective_date in date,
1315 p_valid out nocopy boolean)
1316 is
1317 l_dummy varchar2(5);
1318 begin
1319 --
1320 if (p_assignment_id <> g_valact_rec.assignment_id) then
1321 g_valact_rec.proc_not_exist_date := to_date('4712/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
1322 g_valact_rec.proc_exist_date := to_date('1900/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
1323 g_valact_rec.assignment_id := p_assignment_id;
1324 end if;
1325 --
1326 --
1327 -- This code is trying to find out if we already know
1328 -- whether processes exist for the date supplied
1329 --
1330 if (p_effective_date >= g_valact_rec.proc_not_exist_date) then
1331 p_valid := FALSE;
1332 elsif (p_effective_date <= g_valact_rec.proc_exist_date) then
1333 p_valid := TRUE;
1334 else
1335 --
1336 -- This date hasn't already been calculated, we
1337 -- need to find out if processes exist
1338 --
1339 begin
1340 --
1341 select ''
1342 into l_dummy
1343 from dual
1344 where exists (select ''
1345 from pay_payroll_actions ppa,
1346 pay_assignment_actions paa
1347 where paa.assignment_id = p_assignment_id
1348 and ppa.payroll_action_id = paa.payroll_action_id
1349 and ppa.action_type in ('R', 'Q', 'B', 'V')
1350 and (ppa.effective_date >= p_effective_date
1351 or ppa.date_earned >= p_effective_date)
1352 );
1353 --
1354 p_valid := TRUE;
1355 g_valact_rec.proc_exist_date := p_effective_date;
1356 --
1357 exception
1358 when no_data_found then
1359 p_valid := FALSE;
1360 g_valact_rec.proc_not_exist_date := p_effective_date;
1361 end;
1362 end if;
1363 --
1364 end validate_affected_actions;
1365
1366 procedure perform_qualifications
1367 (
1368 p_table_id IN NUMBER,
1369 p_final_effective_date IN DATE,
1370 p_creation_date IN DATE DEFAULT NULL,
1371 p_start_date IN DATE,
1372 p_end_date IN DATE,
1373 p_element_entry_id IN NUMBER,
1374 p_assignment_action_id IN NUMBER,
1375 p_business_group_id IN NUMBER,
1376 p_assignment_id IN NUMBER,
1377 p_process_mode in varchar2,
1378 p_update_type in varchar2,
1379 p_change_mode in varchar2,
1380 p_change_values in varchar2,
1381 p_surrogate_key IN NUMBER,
1382 p_date_counter IN OUT NOCOPY NUMBER,
1383 p_global_env IN OUT NOCOPY t_global_env_rec,
1384 p_datetracked_id IN NUMBER,
1385 p_column_name IN VARCHAR2,
1386 p_old_value IN VARCHAR2,
1387 p_new_value IN VARCHAR2,
1388 p_proration_style IN VARCHAR2,
1389 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1390 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1391 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1392 t_detailed_output in OUT NOCOPY t_detailed_output_table_type,
1393 p_run_event_proc in out nocopy boolean,
1394 p_event_proc_res in out nocopy varchar2
1395 )
1396 is
1397 l_output_result VARCHAR2(40) ;
1398 l_valid VARCHAR2(10);
1399 l_type VARCHAR2(10);
1400
1401 begin
1402 --
1403 l_output_result := 'TRUE';
1404 --
1405 -- Before we do anything do we need to check if there are any payroll runs
1406 -- out there for this effective_date
1407 --
1408 if (p_global_env.validate_run_actions) then
1409 --
1410 declare
1411 l_valid boolean;
1412 begin
1413 validate_affected_actions(p_assignment_id => p_assignment_id,
1414 p_effective_date => p_final_effective_date,
1415 p_valid => l_valid);
1416 if (l_valid = FALSE) then
1417 l_output_result := 'FALSE';
1418 end if;
1419 end ;
1420 --
1421 end if;
1422 --
1423 -- The above condition checks whether or not the values of the two SELECTS
1424 -- were same.
1425 --
1426 if (l_output_result = 'TRUE') then
1427 --
1428 -- Now perform the generic data comparison.
1429 begin
1430 pay_interpreter_pkg.generic_data_validation
1431 (p_table_id ,
1432 p_datetracked_id,
1433 p_old_value,
1434 p_new_value,
1435 p_final_effective_date,
1436 p_surrogate_key,
1437 p_element_entry_id,
1438 p_assignment_id,
1439 l_valid,
1440 l_type,
1441 p_global_env);
1442 exception
1443 -- This is possible if the hire date is chagned.
1444 when no_data_found then
1445 l_output_result := 'FALSE';
1446 when others then
1447 raise;
1448 end;
1449 --
1450 if (l_valid = 'N') then
1451 l_output_result := 'FALSE';
1452 end if;
1453 end if;
1454
1455 -- Now check the external procedure calls
1456
1457 if (l_output_result = 'TRUE')
1458 then
1459 if (p_run_event_proc = TRUE) then
1460 event_group_procedure(p_table_id,
1461 p_element_entry_id,
1462 p_assignment_action_id,
1463 p_business_group_id,
1464 p_surrogate_key,
1465 p_column_name,
1466 p_old_value,
1467 p_new_value,
1468 l_output_result,
1469 p_final_effective_date,
1470 p_global_env);
1471 --
1472 p_run_event_proc := FALSE;
1473 p_event_proc_res := l_output_result;
1474 --
1475 else
1476 l_output_result := p_event_proc_res;
1477 end if;
1478 end if;
1479
1480 IF(l_output_result = 'TRUE') THEN
1481
1482 -- t_proration_dates_temp is a temporary table. This stores all the dates
1483 -- irrespective of the fact the dates are unique or not
1484
1485 IF ( p_process_mode = 'ENTRY_EFFECTIVE_DATE'
1486 and p_final_effective_date > p_start_date
1487 and p_final_effective_date <= p_end_date)
1488 or
1489 (p_process_mode <> 'ENTRY_EFFECTIVE_DATE')
1490 THEN
1491
1492 add_found_event (
1493 p_effective_date => p_final_effective_date,
1494 p_creation_date => p_creation_date,
1495 p_update_type => p_update_type,
1496 p_change_mode => p_change_mode,
1497 p_proration_type => p_proration_style,
1498 p_datetracked_event => p_datetracked_id,
1499 -- possible future enhancement request
1500 p_column_name => p_column_name,
1501 p_old_val => p_old_value,
1502 p_new_val => p_new_value,
1503 p_element_entry_id => p_element_entry_id,
1504 p_surrogate_key => p_surrogate_key,
1505 p_change_values => p_change_values,
1506 p_dated_table_id => p_table_id,
1507 p_global_env => p_global_env,
1508 p_date_counter => p_date_counter,
1509 t_proration_dates_temp => t_proration_dates_temp,
1510 t_proration_change_type => t_proration_change_type,
1511 t_proration_type => t_proration_type,
1512 t_detailed_output => t_detailed_output
1513 );
1514
1515 END IF;
1516 END IF;
1517 end;
1518
1519
1520
1521
1522
1523 PROCEDURE compare_values
1524 (
1525 p_table_id IN NUMBER,
1526 p_table_name IN VARCHAR2,
1527 p_final_effective_date IN DATE,
1528 p_creation_date IN DATE DEFAULT NULL,
1529 p_start_date IN DATE,
1530 p_end_date IN DATE,
1531 p_dynamic_counter IN NUMBER,
1532 p_element_entry_id IN NUMBER,
1533 p_assignment_action_id IN NUMBER,
1534 p_business_group_id IN NUMBER,
1535 p_assignment_id IN NUMBER,
1536 p_process_mode in varchar2, --eg ENTRY_CREATION_DATE
1537 p_change_mode in varchar2, --eg DATE_PROCESSED
1538 p_surrogate_key IN NUMBER,
1539 p_date_counter IN OUT NOCOPY NUMBER,
1540 p_global_env IN OUT NOCOPY t_global_env_rec,
1541 t_dynamic_sql IN t_dynamic_sql_tab,
1542 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1543 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1544 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1545 l_proration_type IN VARCHAR2,
1546 t_detailed_output in OUT NOCOPY t_detailed_output_table_type
1547 ) AS
1548
1549 l_counter NUMBER ;
1550 process_event boolean;
1551 curr_ptr number;
1552 run_event_proc boolean;
1553 event_proc_res VARCHAR2(40);
1554
1555 BEGIN
1556
1557 run_event_proc := TRUE;
1558 event_proc_res := 'TRUE';
1559
1560 -- In the following loop the old and new values of the columns are compared
1561 FOR l_counter IN 1..p_dynamic_counter
1562 LOOP
1563 IF (NVL(t_dynamic_sql(l_counter).old_value,'-9999') <>
1564 NVL(t_dynamic_sql(l_counter).new_value,'-9999'))
1565 THEN
1566 if (g_dbg) then
1567 hr_utility.trace('Value of the column has changed');
1568 end if;
1569 --
1570 -- We could be saving the results in one for 2 modes.
1571 -- First mode is that the events are being generated for a
1572 -- single element entry.
1573 -- Second mode is that a list of element entries have been
1574 -- suppled cross referencing the datetracked events for which
1575 -- we are looking.
1576 --
1577 if (p_global_env.datetrack_ee_tab_use = FALSE) then
1578 --
1579 run_event_proc := TRUE;
1580 event_proc_res := 'TRUE';
1581 --
1582 perform_qualifications
1583 (
1584 p_table_id => p_table_id,
1585 p_final_effective_date => p_final_effective_date,
1586 p_creation_date => p_creation_date,
1587 p_start_date => p_start_date,
1588 p_end_date => p_end_date,
1589 p_element_entry_id => p_element_entry_id,
1590 p_assignment_action_id => p_assignment_action_id,
1591 p_business_group_id => p_business_group_id,
1592 p_assignment_id => p_assignment_id,
1593 p_process_mode => p_process_mode,
1594 p_update_type => 'U',
1595 p_change_mode => p_change_mode,
1596 p_change_values => null,
1597 p_surrogate_key => p_surrogate_key,
1598 p_date_counter => p_date_counter,
1599 p_global_env => p_global_env,
1600 p_datetracked_id => t_dynamic_sql(l_counter).date_tracked_id,
1601 p_column_name => t_dynamic_sql(l_counter).column_name,
1602 p_old_value => t_dynamic_sql(l_counter).old_value,
1603 p_new_value => t_dynamic_sql(l_counter).new_value,
1604 p_proration_style => t_dynamic_sql(l_counter).proration_style,
1605 t_proration_dates_temp => t_proration_dates_temp,
1606 t_proration_change_type => t_proration_change_type,
1607 t_proration_type => t_proration_type,
1608 t_detailed_output => t_detailed_output,
1609 p_run_event_proc => run_event_proc,
1610 p_event_proc_res => event_proc_res
1611 );
1612 --
1613 else
1614 --
1615 if (glo_datetrack_ee_hash_tab.exists(
1616 t_dynamic_sql(l_counter).date_tracked_id))
1617 then
1618 --
1619 run_event_proc := TRUE;
1620 event_proc_res := 'TRUE';
1621 --
1622 curr_ptr :=
1623 glo_datetrack_ee_hash_tab(
1624 t_dynamic_sql(l_counter).date_tracked_id);
1625 --
1626 while (curr_ptr is not null) loop
1627 --
1628 -- Need to decide if the event is relevent to the current entry
1629 --
1630 process_event := FALSE;
1631 if (p_table_name = 'PAY_ELEMENT_ENTRIES_F') then
1632 --
1633 if (glo_datetrack_ee_tab(curr_ptr).element_entry_id
1634 = p_surrogate_key) then
1635 process_event := TRUE;
1636 run_event_proc := TRUE;
1637 event_proc_res := 'TRUE';
1638 end if;
1639 --
1640 elsif (p_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F') then
1641 --
1642 declare
1643 l_dummy varchar2(2);
1644 l_ee_id pay_element_entries_f.element_entry_id%type;
1645 begin
1646 l_ee_id :=
1647 glo_datetrack_ee_tab(curr_ptr).element_entry_id;
1648 select ''
1649 into l_dummy
1650 from dual
1651 where exists (select ''
1652 from pay_element_entry_values_f
1653 where element_entry_id = l_ee_id
1654 and element_entry_value_id =
1655 p_surrogate_key
1656 );
1657 process_event := TRUE;
1658 run_event_proc := TRUE;
1659 event_proc_res := 'TRUE';
1660 exception
1661 when no_data_found then
1662 process_event := FALSE;
1663 end;
1664 --
1665 else
1666 process_event := TRUE;
1667 end if;
1668
1669 --
1670 if (process_event = TRUE) then
1671 --
1672 hr_utility.trace(' >= Found a valid event, valid for our ee, now check qualifiers');
1673 perform_qualifications
1674 (
1675 p_table_id => p_table_id,
1676 p_final_effective_date => p_final_effective_date,
1677 p_creation_date => p_creation_date,
1678 p_start_date => p_start_date,
1679 p_end_date => p_end_date,
1680 p_element_entry_id =>
1681 glo_datetrack_ee_tab(curr_ptr).element_entry_id,
1682 p_assignment_action_id => p_assignment_action_id,
1683 p_business_group_id => p_business_group_id,
1684 p_assignment_id => p_assignment_id,
1685 p_process_mode => p_process_mode,
1686 p_update_type => 'U',
1687 p_change_mode => p_change_mode,
1688 p_change_values => null,
1689 p_surrogate_key => p_surrogate_key,
1690 p_date_counter => p_date_counter,
1691 p_global_env => p_global_env,
1692 p_datetracked_id => t_dynamic_sql(l_counter).date_tracked_id,
1693 p_column_name => t_dynamic_sql(l_counter).column_name,
1694 p_old_value => t_dynamic_sql(l_counter).old_value,
1695 p_new_value => t_dynamic_sql(l_counter).new_value,
1696 p_proration_style => t_dynamic_sql(l_counter).proration_style,
1697 t_proration_dates_temp => t_proration_dates_temp,
1698 t_proration_change_type => t_proration_change_type,
1699 t_proration_type => t_proration_type,
1700 t_detailed_output => t_detailed_output,
1701 p_run_event_proc => run_event_proc,
1702 p_event_proc_res => event_proc_res
1703 );
1704 --
1705 end if;
1706 --
1707 curr_ptr := glo_datetrack_ee_tab(curr_ptr).next_ptr;
1708 --
1709 end loop;
1710 end if;
1711 end if;
1712 --
1713 END IF;
1714 END LOOP;
1715
1716 END;
1717
1718
1719
1720 PROCEDURE event_group_table_correction
1721 (
1722 p_end_date_name IN VARCHAR2,
1723 p_start_date_name IN VARCHAR2,
1724 p_updated_column_name IN VARCHAR2,
1725 p_table_id in NUMBER,
1726 p_surrogate_key in NUMBER,
1727 p_change_values in varchar2,
1728 p_effective_date IN DATE,
1729 p_date_counter IN OUT NOCOPY number,
1730 store_correction IN OUT NOCOPY NUMBER,
1731 is_correction IN OUT NOCOPY NUMBER,
1732 l_proration_type IN VARCHAR2,
1733 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1734 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1735 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1736 t_detailed_output in OUT NOCOPY t_detailed_output_table_type
1737 ) AS
1738
1739 BEGIN
1740
1741
1742 is_correction:=0;
1743
1744 if (p_start_date_name <> p_updated_column_name AND
1745 p_end_date_name <> p_updated_column_name)
1746 THEN
1747 is_correction:=1;
1748 END IF;
1749 IF (store_correction = 0 AND is_correction=1)
1750 THEN
1751 t_proration_dates_temp(p_date_counter):= p_effective_date;
1752 t_proration_change_type(p_date_counter):= 'C';
1753 t_proration_type(p_date_counter):= l_proration_type;
1754 t_detailed_output(p_date_counter).dated_table_id := p_table_id;
1755 t_detailed_output(p_date_counter).datetracked_event := 'C';
1756 t_detailed_output(p_date_counter).surrogate_key := p_surrogate_key;
1757 t_detailed_output(p_date_counter).column_name := p_updated_column_name;
1758 t_detailed_output(p_date_counter).change_values := p_change_values;
1759 p_date_counter := p_date_counter + 1;
1760 END IF;
1761 END;
1762
1763 PROCEDURE event_group_table_deleted
1764 (
1765 p_table_name IN VARCHAR2,
1766 p_table_id IN NUMBER,
1767 p_surrogate_key_name IN VARCHAR2,
1768 p_surrogate_key IN NUMBER,
1769 p_end_date_name IN VARCHAR2,
1770 p_effective_date IN DATE,
1771 p_updated_column_name IN VARCHAR2,
1772 p_date_counter IN OUT NOCOPY number,
1773 store_delete IN OUT NOCOPY NUMBER,
1774 is_delete IN OUT NOCOPY NUMBER,
1775 l_proration_type IN VARCHAR2,
1776 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1777 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1778 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1779 t_detailed_output in OUT NOCOPY t_detailed_output_table_type
1780 ) AS
1781
1782 l_statement VARCHAR2(1000) ;
1783 l_result NUMBER ;
1784 l_date date;
1785
1786
1787 BEGIN
1788 is_delete:=0;
1789 l_result:=0;
1790 IF (p_updated_column_name=p_end_date_name)
1791 THEN
1792
1793 l_statement := 'SELECT 1 FROM dual WHERE EXISTS (select 1 from '
1794 || p_table_name || ' where ' ||
1795 p_surrogate_key_name ||' = :p_surrogate_key '||
1796 ' and ' || p_end_date_name ||' > :col1)';
1797 if (g_traces) then
1798 hr_utility.trace('-Dynamic SQL ' || l_statement);
1799 end if;
1800
1801 execute immediate l_statement into l_result using p_surrogate_key, p_effective_date;
1802 return;
1803
1804 END IF;
1805 EXCEPTION
1806 when NO_DATA_FOUND then
1807 is_delete:=1;
1808 IF (store_delete = 0)
1809 THEN
1810 t_proration_dates_temp(p_date_counter):= p_effective_date;
1811 t_proration_change_type(p_date_counter):= 'E';
1812 t_proration_type(p_date_counter):= l_proration_type;
1813 t_detailed_output(p_date_counter).dated_table_id := p_table_id;
1814 t_detailed_output(p_date_counter).datetracked_event := 'E';
1815 t_detailed_output(p_date_counter).surrogate_key := p_surrogate_key;
1816 p_date_counter := p_date_counter + 1;
1817 END IF;
1818 END;
1819
1820 PROCEDURE event_group_table_updated
1821 (
1822 p_element_entry_id IN NUMBER DEFAULT NULL ,
1823 p_assignment_action_id IN NUMBER,
1824 p_business_group_id IN NUMBER,
1825 p_assignment_id IN NUMBER,
1826 p_process_mode IN VARCHAR2,
1827 p_change_mode IN VARCHAR2,
1828 p_proration_group_id IN NUMBER,
1829 p_table_id IN NUMBER,
1830 p_table_name IN VARCHAR2,
1831 p_surrogate_key_name IN VARCHAR2,
1832 p_surrogate_key IN NUMBER,
1833 p_start_date_name IN VARCHAR2,
1834 p_end_date_name IN VARCHAR2,
1835 p_effective_date IN DATE,
1836 p_creation_date IN DATE DEFAULT NULL,
1837 p_start_date IN DATE,
1838 p_end_date IN DATE,
1839 p_updated_column_name IN VARCHAR2,
1840 p_date_counter IN OUT NOCOPY number,
1841 p_global_env IN OUT NOCOPY t_global_env_rec,
1842 l_proration_type IN VARCHAR2,
1843 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
1844 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
1845 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
1846 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
1847 t_detailed_output in OUT NOCOPY t_detailed_output_table_type
1848 ) AS
1849
1850 l_old_sql_fetch NUMBER ;
1851 l_statement VARCHAR2(2000) ;
1852 l_dynamic_counter NUMBER ;
1853 l_final_effective_date DATE ;
1854
1855 BEGIN
1856
1857
1858 create_statement(p_proration_group_id,
1859 p_table_id,
1860 p_table_name,
1861 p_surrogate_key_name,
1862 p_surrogate_key,
1863 p_start_date_name,
1864 p_end_date_name,
1865 l_statement,
1866 p_global_env,
1867 t_dynamic_sql,
1868 l_dynamic_counter);
1869
1870 execute_statement(l_statement,
1871 t_dynamic_sql,
1872 p_surrogate_key,
1873 p_effective_date,
1874 p_start_date_name,
1875 p_end_date_name,
1876 l_dynamic_counter,
1877 p_updated_column_name,
1878 l_final_effective_date);
1879 --
1880 compare_values(p_table_id,
1881 p_table_name,
1882 l_final_effective_date,
1883 p_creation_date,
1884 p_start_date,
1885 p_end_date,
1886 l_dynamic_counter,
1887 p_element_entry_id,
1888 p_assignment_action_id,
1889 p_business_group_id,
1890 p_assignment_id,
1891 p_process_mode,
1892 p_change_mode,
1893 p_surrogate_key,
1894 p_date_counter,
1895 p_global_env,
1896 t_dynamic_sql,
1897 t_proration_dates_temp,
1898 t_proration_change_type,
1899 t_proration_type,
1900 l_proration_type,
1901 t_detailed_output );
1902 --
1903 END;
1904
1905
1906 procedure get_prorated_dates
1907 (
1908 p_element_entry_id IN NUMBER DEFAULT NULL ,
1909 p_assignment_action_id IN NUMBER DEFAULT NULL ,
1910 p_time_definition_id IN NUMBER DEFAULT NULL ,
1911 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
1912 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
1913 t_proration_type OUT NOCOPY t_proration_type_table_type
1914 )
1915 is
1916 --
1917 --
1918 t_proration_change_type t_proration_type_table_type;
1919 --
1920 t_proration_dates_temp t_proration_dates_table_type ;
1921 t_proration_change_type_temp t_proration_type_table_type;
1922 t_proration_type_temp t_proration_type_table_type;
1923 l_global_env t_global_env_rec;
1924 l_internal_mode varchar2(30) := 'PRORATION';
1925 --
1926 function process_time_def(p_assignment_action_id in number,
1927 p_time_definition_id in number)
1928 return boolean
1929 is
1930 l_recalc boolean;
1931 begin
1932 --
1933 l_recalc := FALSE;
1934 if (p_time_definition_id <> g_time_definition_id) then
1935 begin
1936 select procedure_name
1937 into g_tim_def_prc_name
1938 from pay_event_procedures
1939 where time_definition_id = p_time_definition_id
1940 and nvl(procedure_type, 'E') = 'T';
1941 --
1942 l_recalc := TRUE;
1943 g_proc_set := TRUE;
1944 --
1945 exception
1946 when no_data_found then
1947 g_process_time_def := TRUE;
1948 g_assignment_action_id := p_assignment_action_id;
1949 g_time_definition_id := p_time_definition_id;
1950 g_proc_set := FALSE;
1951 g_process_time_def := TRUE;
1952 end;
1953 end if;
1954 --
1955 if (p_assignment_action_id <> g_assignment_action_id) then
1956 l_recalc := TRUE;
1957 end if;
1958 --
1959 if (l_recalc = TRUE and g_proc_set = TRUE) then
1960 --
1961 declare
1962 l_cursor_id INTEGER;
1963 l_dummy NUMBER;
1964 l_res number;
1965 l_proc_string VARCHAR2(400);
1966 begin
1967
1968 if (g_traces) then
1969 hr_utility.trace('Procedure Name ' || g_tim_def_prc_name);
1970 end if;
1971
1972 l_proc_string := 'BEGIN :res := ' || g_tim_def_prc_name || '(' ||
1973 'p_assignment_action_id => :aa' ||
1974 '); END;';
1975
1976 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1977
1978 if (g_dbg) then
1979 hr_utility.trace('Parameters');
1980 end if;
1981
1982 DBMS_SQL.PARSE(l_cursor_id, l_proc_string, DBMS_SQL.V7);
1983
1984 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':res', l_res);
1985 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':aa',p_assignment_action_id);
1986
1987 if (g_dbg) then
1988 hr_utility.trace('All Variables Bound');
1989 end if;
1990
1991 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1992
1993 if (g_dbg) then
1994 hr_utility.trace('Procedure Executed');
1995 end if;
1996
1997 DBMS_SQL.VARIABLE_VALUE(l_cursor_id, ':res', l_res);
1998 --
1999 g_process_time_def := TRUE;
2000 if (l_res = 0) then
2001 g_process_time_def := FALSE;
2002 end if;
2003 g_assignment_action_id := p_assignment_action_id;
2004 g_time_definition_id := p_time_definition_id;
2005
2006 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
2007 end;
2008 end if;
2009 --
2010 return g_process_time_def;
2011 --
2012 end process_time_def;
2013 --
2014 procedure get_time_periods(p_assignment_action_id in number,
2015 p_time_definition_id in number,
2016 p_element_entry_id in number,
2017 t_detailed_output in out nocopy t_detailed_output_table_type ,
2018 t_proration_dates_temp in out nocopy t_proration_dates_table_type,
2019 t_proration_change_type_temp in out nocopy t_proration_type_table_type,
2020 t_proration_type_temp in out nocopy t_proration_type_table_type
2021 )
2022 is
2023 --
2024 cursor find_start_dates (p_asg_act in number,
2025 p_time_def in number
2026 )
2027 is
2028 select ptp_td.start_date
2029 from per_time_periods ptp_td,
2030 pay_assignment_actions paa,
2031 pay_payroll_actions ppa,
2032 per_time_periods ptp_ppa
2033 where ptp_td.time_definition_id = p_time_def
2034 and paa.assignment_action_id = p_asg_act
2035 and paa.payroll_action_id = ppa.payroll_action_id
2036 and ppa.payroll_id = ptp_ppa.payroll_id
2037 and ppa.date_earned between ptp_ppa.start_date
2038 and ptp_ppa.end_date
2039 and ptp_td.start_date > ptp_ppa.start_date
2040 and ptp_td.start_date <= ptp_ppa.end_date;
2041 --
2042 l_date_counter number;
2043 l_ee_min_date date;
2044 l_ee_max_date date;
2045 --
2046 begin
2047 --
2048 select min(effective_start_date),
2049 max(effective_end_date)
2050 into l_ee_min_date,
2051 l_ee_max_date
2052 from pay_element_entries_f
2053 where element_entry_id = p_element_entry_id;
2054 --
2055 hr_utility.trace('td id '|| p_time_definition_id );
2056 hr_utility.trace('asg act id '|| p_assignment_action_id );
2057 l_date_counter := t_proration_dates_temp.count + 1;
2058 --
2059 for datrec in find_start_dates(p_assignment_action_id,
2060 p_time_definition_id) loop
2061 --
2062 hr_utility.trace('Allocation Date '||datrec.start_date);
2063 --
2064 if (datrec.start_date > l_ee_min_date
2065 and datrec.start_date <= l_ee_max_date) then
2066 --
2067 add_found_event
2068 (
2069 p_effective_date => datrec.start_date,
2070 p_creation_date => null,
2071 p_update_type => null,
2072 p_change_mode => null,
2073 p_proration_type => 'E',
2074 p_datetracked_event => null,
2075 p_surrogate_key => null,
2076 p_dated_table_id => null,
2077 p_date_counter => l_date_counter,
2078 p_global_env => l_global_env,
2079 t_proration_dates_temp => t_proration_dates_temp,
2080 t_proration_change_type => t_proration_change_type_temp,
2081 t_proration_type => t_proration_type_temp,
2082 t_detailed_output => t_detailed_output
2083 );
2084 --
2085 end if;
2086 --
2087 end loop;
2088 --
2089 end get_time_periods;
2090 --
2091 begin
2092 --
2093 -- Clear out the caches
2094 --
2095 t_proration_dates.delete;
2096 t_proration_dates_temp.delete;
2097 t_proration_change_type.delete;
2098 t_proration_change_type_temp.delete;
2099 t_proration_type_temp.delete;
2100 t_proration_type.delete;
2101 --
2102 -- First generate the proration events
2103 --
2104 entry_affected(
2105 p_element_entry_id,
2106 p_assignment_action_id,
2107 NULL,
2108 NULL,
2109 NULL,
2110 NULL,
2111 l_internal_mode,
2112 hr_api.g_sot,
2113 hr_api.g_eot,
2114 sysdate,
2115 'N',
2116 null,
2117 t_detailed_output,
2118 t_proration_dates_temp,
2119 t_proration_change_type_temp,
2120 t_proration_type_temp);
2121 --
2122 -- Now generate the allocation events if needed
2123 --
2124 if (p_time_definition_id is not null) then
2125 --
2126 if (process_time_def(p_assignment_action_id,
2127 p_time_definition_id) = TRUE) then
2128 get_time_periods(p_assignment_action_id,
2129 p_time_definition_id,
2130 p_element_entry_id,
2131 t_detailed_output,
2132 t_proration_dates_temp,
2133 t_proration_change_type_temp,
2134 t_proration_type_temp
2135 );
2136 end if;
2137 --
2138 end if;
2139 --
2140 -- Finally create a sorted unique list
2141 --
2142 unique_sort(p_proration_dates_temp => t_proration_dates_temp ,
2143 p_proration_dates => t_proration_dates ,
2144 p_change_type_temp => t_proration_change_type_temp,
2145 p_proration_type_temp => t_proration_type_temp,
2146 p_change_type => t_proration_change_type,
2147 p_proration_type => t_proration_type,
2148 p_internal_mode => l_internal_mode);
2149 --
2150 end get_prorated_dates;
2151
2152 /****************************************************************************
2153 Name : entry_affected
2154 Purpose : The procedure returns 3 tables. This procedure is called by
2155 the Payroll.
2156 Arguments :
2157 IN : p_element_entry_id
2158 p_assignment_action_id
2159 OUT : t_detailed_output
2160 t_proration_dates
2161 t_proration_type
2162 Notes : PUBLIC
2163 ****************************************************************************/
2164
2165 -- Main Entry Point, 5 params, called from orig PRORATION code
2166 --
2167 PROCEDURE entry_affected
2168 (
2169 p_element_entry_id IN NUMBER DEFAULT NULL ,
2170 p_assignment_action_id IN NUMBER DEFAULT NULL ,
2171 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
2172 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
2173 t_proration_type OUT NOCOPY t_proration_type_table_type
2174 ) AS
2175
2176 t_proration_change_type t_proration_type_table_type;
2177
2178 BEGIN
2179
2180 -- Call main overloaded entry_affected, 15 params
2181 -- Created as part of ADV_RETRONOT enhancement.
2182 -- Allows calling in different historic modes
2183 --
2184 entry_affected(
2185 p_element_entry_id,
2186 p_assignment_action_id,
2187 NULL,
2188 NULL,
2189 NULL,
2190 NULL,
2191 'PRORATION',
2192 hr_api.g_sot,
2193 hr_api.g_eot,
2194 sysdate,
2195 'Y',
2196 null,
2197 t_detailed_output,
2198 t_proration_dates,
2199 t_proration_change_type,
2200 t_proration_type);
2201 END entry_affected; --5params
2202
2203 -- Main Entry Point, 7 params, called from orig RETRONOT code
2204 --
2205 PROCEDURE entry_affected
2206 (
2207 p_element_entry_id IN NUMBER DEFAULT NULL ,
2208 p_assignment_id IN NUMBER DEFAULT NULL ,
2209 p_mode IN VARCHAR2 DEFAULT NULL ,
2210 p_process IN VARCHAR2 DEFAULT NULL ,
2211 p_event_group_id IN NUMBER DEFAULT NULL ,
2212 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
2213 t_proration_change_type OUT NOCOPY t_proration_type_table_type
2214 ) AS
2215
2216 t_proration_type t_proration_type_table_type;
2217 t_detailed_output t_detailed_output_table_type;
2218 BEGIN
2219
2220 -- Call main overloaded entry_affected, 15 params
2221 -- Created as part of ADV_RETRONOT enhancement.
2222 -- Allows calling in different historic modes
2223 --
2224
2225 entry_affected (
2226 p_element_entry_id,
2227 NULL,
2228 p_assignment_id,
2229 p_mode,
2230 p_process,
2231 p_event_group_id,
2232 'ENTRY_RETROSTATUS',
2233 hr_api.g_sot,
2234 hr_api.g_eot,
2235 sysdate,
2236 'Y',
2237 null,
2238 t_detailed_output,
2239 t_proration_dates,
2240 t_proration_change_type,
2241 t_proration_type);
2242 END entry_affected; --7params
2243
2244 -- Main Entry Point, 11 params, called from somewhere
2245 --
2246 PROCEDURE entry_affected
2247
2248 (
2249 p_element_entry_id IN NUMBER DEFAULT NULL ,
2250 p_assignment_action_id IN NUMBER DEFAULT NULL ,
2251 p_assignment_id IN NUMBER DEFAULT NULL ,
2252 p_mode IN VARCHAR2 DEFAULT NULL ,
2253 p_process IN VARCHAR2 DEFAULT NULL ,
2254 p_event_group_id IN NUMBER DEFAULT NULL ,
2255 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE' ,
2256 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
2257 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
2258 t_proration_change_type OUT NOCOPY t_proration_type_table_type,
2259 t_proration_type OUT NOCOPY t_proration_type_table_type
2260 ) AS
2261 BEGIN
2262
2263 -- Call main overloaded entry_affected, 15 params
2264 -- Created as part of ADV_RETRONOT enhancement.
2265 -- Allows calling in different historic modes
2266 --
2267 entry_affected (
2268 p_element_entry_id,
2269 NULL,
2270 p_assignment_id,
2271 p_mode,
2272 p_process,
2273 p_event_group_id,
2274 'ENTRY_RETROSTATUS',
2275 hr_api.g_sot,
2276 hr_api.g_eot,
2277 sysdate,
2278 'Y',
2279 null,
2280 t_detailed_output,
2281 t_proration_dates,
2282 t_proration_change_type,
2283 t_proration_type);
2284 END entry_affected; --11params
2285
2286
2287 /****************************************************************************
2288 Name : asg_action_affected
2289 Purpose : The procedure is used in Continous Calc.
2290 Arguments :
2291 IN : p_assignment_action_id
2292 OUT : VARCHAR2 ('YES','NO')
2293 Notes : PUBLIC
2294 ****************************************************************************/
2295 PROCEDURE asg_action_affected(p_assignment_action_id IN NUMBER) AS
2296
2297 -- The following cursor selects the rows from pay_process_events where
2298 -- Change_type in DATE_PROCESSED, DATE_EARNED, GRE, PAYMENT, COST_CENTRE
2299 -- and status = 'U'
2300 CURSOR c_pay_process_events(p_assignment_id IN NUMBER) IS
2301 SELECT process_event_id ,
2302 event_update_id ,
2303 change_type ,
2304 assignment_id ,
2305 surrogate_key ,
2306 effective_date
2307 FROM pay_process_events
2308 WHERE assignment_id = p_assignment_id
2309 AND change_type IN ('DATE_PROCESSED',
2310 'DATE_EARNED' ,
2311 'PAYMENT' ,
2312 'GRE' ,
2313 'COST_CENTRE' )
2314 AND status = 'U';
2315
2316 -- The following cursor selects the assignment Id for a given assignment_action_id.
2317 CURSOR c_ass_act_id IS
2318 SELECT assignment_id
2319 FROM pay_assignment_actions
2320 WHERE assignment_action_id = p_assignment_action_id;
2321
2322 CURSOR c_mixed(p_event_update_id IN NUMBER) IS
2323 SELECT a.dated_table_id table_id ,
2324 a.column_name column_name ,
2325 a.change_type change_type ,
2326 a.event_type event_type ,
2327 b.table_name table_name ,
2328 b.surrogate_key_name surrogate_key_name ,
2329 b.start_date_name start_date_name ,
2330 b.end_date_name end_date_name
2331 FROM pay_event_updates a ,
2332 pay_dated_tables b
2333 WHERE a.dated_table_id = b.dated_table_id;
2334
2335 l_process_event_id pay_process_events.process_event_id%TYPE ;
2336 l_change_type1 pay_process_events.change_type%TYPE ;
2337 l_assignment_id pay_process_events.assignment_id%TYPE ;
2338 l_surrogate_key pay_process_events.surrogate_key%TYPE ;
2339 l_effective_date pay_process_events.effective_date%TYPE ;
2340
2341 l_event_update_id pay_event_updates.event_update_id%TYPE ;
2342 l_table_id pay_event_updates.dated_table_id%TYPE ;
2343 l_column_name pay_event_updates.column_name%TYPE ;
2344 l_change_type2 pay_event_updates.change_type%TYPE ;
2345 l_event_type pay_event_updates.event_type%TYPE ;
2346 l_table_name pay_dated_tables.table_name%TYPE ;
2347 l_surrogate_key_name pay_dated_tables.surrogate_key_name%TYPE ;
2348 l_start_date_name pay_dated_tables.start_date_name%TYPE ;
2349 l_end_date_name pay_dated_tables.end_date_name%TYPE ;
2350 BEGIN
2351 FOR caa IN c_ass_act_id
2352 LOOP
2353 l_assignment_id := caa.assignment_id;
2354 END LOOP;
2355
2356 FOR cppe IN c_pay_process_events(l_assignment_id)
2357 LOOP
2358 l_process_event_id := cppe.process_event_id ;
2359 l_event_update_id := cppe.event_update_id ;
2360 l_change_type1 := cppe.change_type ;
2361 l_assignment_id := cppe.assignment_id ;
2362 l_surrogate_key := cppe.surrogate_key ;
2363 l_effective_date := cppe.effective_date ;
2364
2365 FOR cm IN c_mixed(l_event_update_id)
2366 LOOP
2367 l_table_id := cm.table_id ;
2368 l_column_name := cm.column_name ;
2369 l_change_type2 := cm.change_type ;
2370 l_event_type := cm.event_type ;
2371 l_table_name := cm.table_name ;
2372 END LOOP;
2373 END LOOP;
2374 END asg_action_affected;
2375
2376
2377
2378 /****************************************************************************
2379 Name : asg_action_event
2380 Purpose : The procedure is used in Continous Calc.
2381 Arguments :
2382 IN : p_assignment_action_id
2383 : An array of process_event_id
2384 OUT : VARCHAR2 ('YES','NO')
2385 Notes : PUBLIC
2386 ****************************************************************************/
2387 PROCEDURE asg_action_event(p_assignment_action_id IN NUMBER ,
2388 p_process_event_tab IN t_process_event_table,
2389 p_affected OUT NOCOPY VARCHAR2 ) AS
2390
2391 CURSOR c_event_updates(p_event_update_id IN NUMBER ,
2392 p_change_type IN VARCHAR2 ) IS
2393 SELECT a.dated_table_id table_id ,
2394 a.column_name column_name ,
2395 a.event_type event_type
2396 FROM pay_event_updates a
2397 WHERE a.event_update_id = p_event_update_id
2398 AND a.change_type = p_change_type ;
2399
2400 CURSOR c_event_tables(p_table_id IN NUMBER ,
2401 p_change_type IN VARCHAR2 ,
2402 p_start_col IN VARCHAR2 ,
2403 p_end_col IN VARCHAR2) IS
2404 SELECT a.column_name column_name ,
2405 a.event_type event_type
2406 FROM pay_event_updates a,
2407 pay_dated_tables b
2408 WHERE a.dated_table_id = b.dated_table_id
2409 AND a.dated_table_id = p_table_id
2410 AND a.change_type = p_change_type
2411 AND a.column_name NOT IN (p_start_col, p_end_col)
2412 AND a.event_type = 'U'
2413 AND a.column_name IS NOT NULL ;
2414
2415 CURSOR c_process_events(p_process_event_id IN NUMBER) IS
2416 SELECT change_type ,
2417 event_update_id ,
2418 effective_date
2419 FROM pay_process_events
2420 WHERE process_event_id = p_process_event_id ;
2421
2422 CURSOR c_pay_tables(p_table_id IN NUMBER) IS
2423 SELECT table_name ,
2424 surrogate_key_name ,
2425 start_date_name ,
2426 end_date_name
2427 FROM pay_dated_tables
2428 WHERE dated_table_id = p_table_id;
2429
2430 l_counter NUMBER ;
2431 l_tab_count NUMBER ;
2432 l_process_event_id NUMBER ;
2433 l_event_update_id NUMBER ;
2434 l_table_id NUMBER ;
2435 l_dynamic_counter NUMBER ;
2436
2437 l_change_type VARCHAR2(40);
2438 l_event_type VARCHAR2(40);
2439 l_return_flag VARCHAR2(40);
2440 l_table_name VARCHAR2(40) ;
2441 l_surrogate_key_name VARCHAR2(40);
2442 l_start_date_name VARCHAR2(40) ;
2443 l_end_date_name VARCHAR2(40) ;
2444 l_column_name VARCHAR2(40);
2445 l_column_string VARCHAR2(2000);
2446
2447 l_loop_flag BOOLEAN;
2448
2449 l_effective_date DATE ;
2450
2451 t_dynamic_sql t_dynamic_sql_tab;
2452
2453 BEGIN
2454 p_affected := 'NO';
2455
2456 FOR l_counter IN 1..l_tab_count
2457 LOOP --{
2458 l_process_event_id := p_process_event_tab(l_counter).process_event_id;
2459
2460 l_change_type := NULL ;
2461 l_event_update_id := NULL ;
2462 l_table_id := NULL ;
2463 l_column_name := NULL ;
2464 l_event_type := NULL ;
2465 l_table_name := NULL ;
2466 l_surrogate_key_name := NULL ;
2467 l_start_date_name := NULL ;
2468 l_end_date_name := NULL ;
2469
2470 FOR cpes IN c_process_events(l_process_event_id)
2471 LOOP
2472 l_change_type := cpes.change_type ;
2473 l_event_update_id := cpes.event_update_id;
2474 l_effective_date := cpes.effective_date ;
2475 FOR ceu IN c_event_updates(l_event_update_id,
2476 l_change_type )
2477 LOOP
2478 l_table_id := ceu.table_id ;
2479 l_column_name := ceu.column_name;
2480 l_event_type := ceu.event_type ;
2481 FOR cpt IN c_pay_tables(l_table_id)
2482 LOOP
2483 l_table_name := cpt.table_name ;
2484 l_surrogate_key_name := cpt.surrogate_key_name ;
2485 l_start_date_name := cpt.start_date_name ;
2486 l_end_date_name := cpt.end_date_name ;
2487 END LOOP;
2488 END LOOP;
2489 END LOOP;
2490 IF(l_column_name IS NOT NULL AND
2491 l_column_name NOT IN (l_start_date_name, l_end_date_name) AND
2492 l_event_type = 'U') THEN
2493 p_affected := 'YES';
2494 EXIT;
2495 ELSIF(l_column_name IS NOT NULL
2496 AND l_column_name IN (l_start_date_name, l_end_date_name)
2497 AND l_event_type = 'U') THEN
2498
2499 l_column_string := NULL;
2500 l_loop_flag := FALSE;
2501
2502 IF (t_dynamic_sql.EXISTS(1)) THEN
2503 -- The code ensures that in the next cycle of loop for multiple tables, the
2504 -- dynamic_sql table gets intialized
2505 t_dynamic_sql.DELETE;
2506 END IF;
2507
2508 l_dynamic_counter := 0;
2509
2510 FOR cet IN c_event_tables(l_table_id ,
2511 l_change_type ,
2512 l_start_date_name ,
2513 l_end_date_name )
2514 LOOP
2515 l_column_name := cet.column_name ;
2516 l_event_type := cet.event_type ;
2517 l_dynamic_counter := l_dynamic_counter + 1;
2518 t_dynamic_sql(l_dynamic_counter).column_name := cet.column_name;
2519 IF (l_loop_flag = TRUE) THEN
2520 l_column_string := l_column_string || ',' || cet.column_name;
2521 ELSE
2522 l_column_string := cet.column_name;
2523 END IF;
2524 l_loop_flag := TRUE;
2525 END LOOP;
2526 -- Build_SQL_dynamically;
2527 -- See the difference;
2528 -- IF (difference) THEN
2529 -- Execute III party proc.
2530 -- IF return TRUE then
2531 -- EXIT with yes;
2532 -- END If; */
2533 END IF;
2534 END LOOP;
2535
2536 END asg_action_event;
2537 --
2538 procedure compare_event_values (p_old_value in varchar2,
2539 p_new_value in varchar2,
2540 p_from_value in varchar2,
2541 p_to_value in varchar2,
2542 p_valid_event in varchar2,
2543 p_prorate_type in varchar2,
2544 p_qualifier_valid in OUT NOCOPY boolean,
2545 p_qual_pro_type in OUT NOCOPY varchar2
2546 )
2547 is
2548 begin
2549 if (g_traces) then
2550 hr_utility.trace(' +Compare value change details...');
2551 hr_utility.trace(' |Compare '||nvl(p_old_value, '<NULL>'));
2552 hr_utility.trace(' | with '||nvl(p_from_value, '<NULL>'));
2553 hr_utility.trace(' |Compare '||nvl(p_new_value, '<NULL>'));
2554 hr_utility.trace(' | with '||nvl(p_to_value, '<NULL>'));
2555 end if;
2556
2557 -- Bug 2681385
2558 -- Dont do further comparisons if old and new are the same
2559 IF p_old_value = p_new_value Then
2560 if (g_traces) then
2561 hr_utility.trace(' + Does NOT pass comparison');
2562 end if;
2563 p_qualifier_valid := FALSE ;
2564 ELSE
2565
2566
2567 if (nvl(p_old_value, '<NULL>') = p_from_value or
2568 p_from_value = '<ANY_VALUE>') and
2569 (nvl(p_new_value, '<NULL>') = p_to_value or
2570 p_to_value = '<ANY_VALUE>') then
2571 if (p_to_value = '<ANY_VALUE>'
2572 and p_from_value = '<ANY_VALUE>'
2573 and nvl(p_old_value, '<NULL>') = nvl(p_new_value, '<NULL>')
2574 ) then
2575 if (g_traces) then
2576 hr_utility.trace('NULL path');
2577 end if;
2578 null;
2579 else
2580 if (p_valid_event = 'Y') then
2581 if (g_traces) then
2582 hr_utility.trace(' + PASS comparison, event is thus TRUE');
2583 end if;
2584 p_qualifier_valid := TRUE ;
2585 if p_qual_pro_type <> 'R' then
2586 p_qual_pro_type := p_prorate_type;
2587 end if ;
2588 else
2589 if (g_traces) then
2590 hr_utility.trace(' + PASS comparison, event is thus FALSE');
2591 end if;
2592 p_qualifier_valid := TRUE ; -- fixed in 3939168
2593 end if ;
2594 end if;
2595 else
2596 if (g_traces) then
2597 hr_utility.trace(' + FAILED comparison');
2598 end if;
2599 end if ;
2600
2601 END IF;
2602 end compare_event_values;
2603 --
2604 procedure run_qualification_code(p_qual_definition in varchar2,
2605 p_comparison_column in varchar2,
2606 p_qual_where_cl in varchar2,
2607 p_qualifying_value in varchar2,
2608 p_key in varchar2,
2609 p_date in date,
2610 p_qualified OUT NOCOPY boolean,
2611 p_old_col_value OUT NOCOPY varchar2,
2612 p_new_col_value OUT NOCOPY varchar2)
2613 --
2614 is
2615 l_statem varchar2(4000);
2616 l_qual_value varchar2(300);
2617 l_column_value varchar2(2000);
2618 --
2619 begin
2620 p_qualified := FALSE;
2621 p_old_col_value := null;
2622 p_new_col_value := null;
2623 --
2624 -- Build Qualifiction statement
2625 l_statem := 'select '|| p_qual_definition;
2626 if p_comparison_column is not null then
2627 l_statem := l_statem||', '||p_comparison_column;
2628 end if;
2629 l_statem := l_statem||' from '||p_qual_where_cl;
2630 g_effective_date := p_date;
2631 g_object_key := p_key;
2632 --
2633
2634 -- Run the select statement
2635 if p_comparison_column is not null then
2636 execute immediate l_statem into l_qual_value, l_column_value;
2637 else
2638 execute immediate l_statem into l_qual_value;
2639 end if;
2640 --
2641 -- Perform the qualifications
2642 if (g_dbg) then
2643 hr_utility.trace('++Testing value_change qualifier value.');
2644 hr_utility.trace(' +Qualifier cursor = evc qual value ? '||l_qual_value||' = '||p_qualifying_value);
2645 end if;
2646 if l_qual_value = p_qualifying_value then
2647 p_qualified := TRUE;
2648 if p_comparison_column is not null then
2649 p_new_col_value := l_column_value;
2650 g_effective_date := p_date -1;
2651 execute immediate l_statem into l_qual_value, l_column_value;
2652 p_old_col_value := l_column_value;
2653 end if;
2654 end if;
2655 end;
2656 --
2657 procedure full_qualification_code(p_qual_definition in varchar2,
2658 p_comparison_column in varchar2,
2659 p_qual_where_cl in varchar2,
2660 p_qualifying_value in varchar2,
2661 p_key in varchar2,
2662 p_date in date,
2663 p_old_col_value in varchar2,
2664 p_new_col_value in varchar2,
2665 p_multi_chk_code in varchar2,
2666 p_from_value in varchar2,
2667 p_to_value in varchar2,
2668 p_valid_event in varchar2,
2669 p_prorate_type in varchar2,
2670 p_qualifier_valid in OUT NOCOPY boolean,
2671 p_qual_pro_type in OUT NOCOPY varchar2)
2672 is
2673 l_statem varchar2(4000);
2674 TYPE MultCurTyp IS REF CURSOR; -- define weak REF CURSOR type
2675 mult_crs MultCurTyp;
2676 l_key varchar2(200);
2677 l_qualified boolean;
2678 l_old_value varchar2(2000);
2679 l_new_value varchar2(2000);
2680 begin
2681 --
2682 -- If we have multi checking code then we need
2683 -- to run the comparison for each of the sub keys.
2684 if(p_multi_chk_code is not null) then
2685 l_statem := p_multi_chk_code;
2686 g_effective_date := p_date;
2687 g_object_key := p_key;
2688 open mult_crs for l_statem;
2689 loop
2690 fetch mult_crs into l_key;
2691 exit when mult_crs%NOTFOUND;
2692 g_parent_key := p_key;
2693 run_qualification_code(p_qual_definition,
2694 p_comparison_column,
2695 p_qual_where_cl,
2696 p_qualifying_value,
2697 l_key,
2698 p_date,
2699 l_qualified,
2700 l_old_value,
2701 l_new_value);
2702 if l_qualified then
2703 compare_event_values(l_old_value,
2704 l_new_value,
2705 p_from_value,
2706 p_to_value,
2707 p_valid_event,
2708 p_prorate_type,
2709 p_qualifier_valid,
2710 p_qual_pro_type);
2711 end if;
2712 end loop;
2713 close mult_crs;
2714 else
2715 --
2716 -- Non multi checking comparison
2717 run_qualification_code(p_qual_definition,
2718 p_comparison_column,
2719 p_qual_where_cl,
2720 p_qualifying_value,
2721 p_key,
2722 p_date,
2723 l_qualified,
2724 l_old_value, -- For non multi checking code, we always compare values passed in.
2725 l_new_value) ;
2726 if l_qualified then
2727 compare_event_values(p_old_col_value,
2728 p_new_col_value,
2729 p_from_value,
2730 p_to_value,
2731 p_valid_event,
2732 p_prorate_type,
2733 p_qualifier_valid,
2734 p_qual_pro_type);
2735 end if;
2736 end if;
2737 end ;
2738 --
2739 procedure run_asg_ee_qualification(p_asg_id in number,
2740 p_ee_id in number,
2741 p_date in date,
2742 p_key in varchar2,
2743 p_asg_sql in varchar2,
2744 p_ee_sql in varchar2,
2745 p_asg_ee_valid in OUT NOCOPY boolean)
2746 is
2747 l_asg_ee_valid varchar2(1);
2748 l_asg_ee_valid_con boolean;
2749 begin
2750 --
2751 -- Setup the variables used in the dynamic sql
2752 g_effective_date := p_date;
2753 g_object_key := p_key;
2754 g_asg_id := p_asg_id;
2755 g_ee_id := p_ee_id;
2756 --
2757 l_asg_ee_valid_con := p_asg_ee_valid;
2758 -- Either perform the entry validation or the
2759 -- assignment validation
2760 if (p_ee_id is null) then
2761 if (p_asg_sql is not null) then
2762 --
2763 execute immediate p_asg_sql into l_asg_ee_valid;
2764 --
2765 end if;
2766 else
2767 if (p_ee_sql is not null) then
2768 --
2769 execute immediate p_ee_sql into l_asg_ee_valid;
2770 --
2771 end if;
2772 end if;
2773 --
2774 -- Set the output up
2775 if l_asg_ee_valid = 'Y' then
2776 l_asg_ee_valid_con := TRUE;
2777 else
2778 l_asg_ee_valid_con := FALSE;
2779 end if;
2780
2781 p_asg_ee_valid := l_asg_ee_valid_con;
2782 g_asg_id := null;
2783 g_ee_id := null;
2784 --
2785 end;
2786 --
2787 procedure load_event_qualifiers(p_datetracked_event_id in number,
2788 p_global_env IN OUT NOCOPY t_global_env_rec
2789 )
2790 is
2791 --
2792 cursor get_qual (p_datetracked_id in number,
2793 p_valid_events in varchar2)
2794 is
2795 select peqv.from_value,
2796 peqv.to_value,
2797 peqv.valid_event,
2798 peqv.proration_style,
2799 peqv.qualifier_value,
2800 peq.qualifier_definition,
2801 peq.comparison_column,
2802 peq.qualifier_where_clause,
2803 peq.multi_event_sql
2804 from pay_event_value_changes_f peqv,
2805 pay_event_qualifiers_f peq
2806 where peqv.datetracked_event_id = p_datetracked_id
2807 and peqv.valid_event = p_valid_events
2808 and peq.event_qualifier_id = peqv.event_qualifier_id;
2809 --
2810 default_val_event varchar2(30);
2811 default_pro_type varchar2(30);
2812 default_asg_qual varchar2(2000);
2813 default_ee_qual varchar2(2000);
2814 needed_events varchar2(30);
2815 qual_found boolean;
2816 qual_idx number;
2817 --
2818 begin
2819 --
2820 if (glo_event_qualifiers.exists(p_datetracked_event_id) = FALSE) then
2821 --
2822 begin
2823 --
2824 default_val_event := null;
2825 default_pro_type := null;
2826 default_asg_qual := null;
2827 default_ee_qual := null;
2828 --
2829 -- Get the default settings
2830 select peqv.valid_event,
2831 peqv.proration_style,
2832 peq.assignment_qualification,
2833 peq.entry_qualification
2834 into default_val_event,
2835 default_pro_type,
2836 default_asg_qual,
2837 default_ee_qual
2838 from pay_event_value_changes_f peqv,
2839 pay_event_qualifiers_f peq
2840 where peqv.datetracked_event_id = p_datetracked_event_id
2841 and peqv.default_event = 'Y'
2842 and peq.event_qualifier_id = peqv.event_qualifier_id;
2843 --
2844 exception
2845 when no_data_found then
2846 default_val_event := 'Y';
2847 default_pro_type := 'E';
2848 end;
2849 --
2850 glo_event_qualifiers(p_datetracked_event_id).valid_event:=
2851 default_val_event;
2852 glo_event_qualifiers(p_datetracked_event_id).proration_style:=
2853 default_pro_type;
2854 glo_event_qualifiers(p_datetracked_event_id).assignment_qualification:=
2855 default_asg_qual;
2856 glo_event_qualifiers(p_datetracked_event_id).entry_qualification :=
2857 default_ee_qual;
2858 glo_event_qualifiers(p_datetracked_event_id).start_qual_ptr := null;
2859 glo_event_qualifiers(p_datetracked_event_id).end_qual_ptr := null;
2860 --
2861 -- Now we have the default go get the exceptions
2862 --
2863 if (default_val_event = 'Y') then
2864 needed_events := 'N';
2865 else
2866 needed_events := 'Y';
2867 end if;
2868 --
2869 qual_found := FALSE;
2870 for qualrec in get_qual(p_datetracked_event_id, needed_events) loop
2871 --
2872 qual_idx := glo_child_event_qualifiers.count + 1;
2873 if (qual_found = FALSE) then
2874 --
2875 qual_found := TRUE;
2876 glo_event_qualifiers(p_datetracked_event_id).start_qual_ptr:=
2877 qual_idx;
2878 --
2879 end if;
2880 --
2881 glo_child_event_qualifiers(qual_idx).from_value :=
2882 qualrec.from_value;
2883 glo_child_event_qualifiers(qual_idx).to_value :=
2884 qualrec.to_value;
2885 glo_child_event_qualifiers(qual_idx).valid_event :=
2886 qualrec.valid_event;
2887 glo_child_event_qualifiers(qual_idx).proration_style :=
2888 qualrec.proration_style;
2889 glo_child_event_qualifiers(qual_idx).qualifier_value :=
2890 qualrec.qualifier_value;
2891 glo_child_event_qualifiers(qual_idx).qualifier_definition :=
2892 qualrec.qualifier_definition;
2893 glo_child_event_qualifiers(qual_idx).comparison_column :=
2894 qualrec.comparison_column;
2895 glo_child_event_qualifiers(qual_idx).qualifier_where_clause :=
2896 qualrec.qualifier_where_clause;
2897 glo_child_event_qualifiers(qual_idx).multi_event_sql :=
2898 qualrec.multi_event_sql;
2899 --
2900 end loop;
2901 --
2902 if (qual_found = TRUE) then
2903 glo_event_qualifiers(p_datetracked_event_id).end_qual_ptr :=
2904 qual_idx;
2905 end if;
2906 --
2907 end if;
2908 --
2909 end load_event_qualifiers;
2910 --
2911 procedure generic_data_validation(p_dated_table_id in number,
2912 p_datetracked_event_id in number,
2913 p_old_value in varchar2,
2914 p_new_value in varchar2,
2915 p_date in date,
2916 p_key in varchar2,
2917 p_ee_id in number,
2918 p_asg_id in number,
2919 p_valid OUT NOCOPY varchar2,
2920 p_type OUT NOCOPY varchar2,
2921 p_global_env IN OUT NOCOPY t_global_env_rec)
2922 is
2923 --
2924 l_overall_type varchar2(10);
2925 found_rows boolean;
2926 default_val_event varchar2(30);
2927 default_pro_type varchar2(30);
2928 default_asg_qual varchar2(2000);
2929 default_ee_qual varchar2(2000);
2930 l_asg_ee_valid boolean;
2931 needed_events varchar2(30);
2932 qualifier_passes boolean;
2933 qual_proration_type varchar2(30);
2934 --
2935 begin
2936 l_overall_type := 'E';
2937 found_rows := FALSE;
2938 l_asg_ee_valid := TRUE;
2939 --
2940 load_event_qualifiers(p_datetracked_event_id => p_datetracked_event_id,
2941 p_global_env => p_global_env
2942 );
2943 --
2944 default_pro_type :=
2945 glo_event_qualifiers(p_datetracked_event_id)
2946 .proration_style;
2947 default_val_event :=
2948 glo_event_qualifiers(p_datetracked_event_id)
2949 .valid_event;
2950 default_asg_qual :=
2951 glo_event_qualifiers(p_datetracked_event_id)
2952 .assignment_qualification;
2953 default_ee_qual :=
2954 glo_event_qualifiers(p_datetracked_event_id)
2955 .entry_qualification;
2956 --
2957 if ( default_asg_qual is not null
2958 or default_ee_qual is not null) then
2959 run_asg_ee_qualification(p_asg_id,
2960 p_ee_id,
2961 p_date,
2962 p_key,
2963 default_asg_qual,
2964 default_ee_qual,
2965 l_asg_ee_valid);
2966 end if;
2967 --
2968 if (g_dbg) then
2969 hr_utility.trace('Default valid entry '||default_val_event);
2970 hr_utility.trace('Default proration type '||default_pro_type);
2971 end if;
2972 --
2973 -- Only process if the event is valid for the assignment
2974 if (l_asg_ee_valid) then
2975 -- What types of comparisons do we need that will over rule the default.
2976 if (default_val_event = 'Y') then
2977 needed_events := 'N';
2978 else
2979 needed_events := 'Y';
2980 end if;
2981 --
2982 if (glo_event_qualifiers(p_datetracked_event_id).start_qual_ptr
2983 is not null)
2984 then
2985 for curr_idx in
2986 glo_event_qualifiers(p_datetracked_event_id).start_qual_ptr
2987 ..glo_event_qualifiers(p_datetracked_event_id).end_qual_ptr
2988 loop
2989 if(glo_child_event_qualifiers(curr_idx).qualifier_value
2990 is not null)
2991 then
2992 full_qualification_code(
2993 glo_child_event_qualifiers(curr_idx).qualifier_definition,
2994 glo_child_event_qualifiers(curr_idx).comparison_column,
2995 glo_child_event_qualifiers(curr_idx).qualifier_where_clause,
2996 glo_child_event_qualifiers(curr_idx).qualifier_value,
2997 p_key,
2998 p_date,
2999 p_old_value,
3000 p_new_value,
3001 glo_child_event_qualifiers(curr_idx).multi_event_sql,
3002 glo_child_event_qualifiers(curr_idx).from_value,
3003 glo_child_event_qualifiers(curr_idx).to_value,
3004 glo_child_event_qualifiers(curr_idx).valid_event,
3005 glo_child_event_qualifiers(curr_idx).proration_style,
3006 qualifier_passes,
3007 qual_proration_type);
3008 else
3009 compare_event_values (
3010 p_old_value,
3011 p_new_value,
3012 glo_child_event_qualifiers(curr_idx).from_value,
3013 glo_child_event_qualifiers(curr_idx).to_value,
3014 glo_child_event_qualifiers(curr_idx).valid_event,
3015 glo_child_event_qualifiers(curr_idx).proration_style,
3016 qualifier_passes,
3017 qual_proration_type
3018 );
3019 end if;
3020 --
3021
3022 -- record if we passed comparisons
3023 if (qualifier_passes = TRUE) then
3024 found_rows := TRUE;
3025 if (l_overall_type <> 'R') then
3026 l_overall_type := qual_proration_type;
3027 end if;
3028 end if;
3029 --
3030 end loop; -- Get next event value change qualifier row
3031 end if;
3032 --
3033 -- Now set up the return variables.
3034 if (found_rows = TRUE) then
3035 if (default_val_event = 'Y') then
3036 p_valid := 'N';
3037 p_type := 'E';
3038 else
3039 p_valid := 'Y';
3040 p_type := l_overall_type;
3041 end if;
3042 else
3043 p_valid := default_val_event;
3044 p_type := default_pro_type;
3045 end if;
3046 else
3047 p_valid := 'N';
3048 end if;
3049 --
3050
3051 hr_utility.trace(' >= Generic data validation, Event qualification Result: '||p_valid);
3052 end;
3053 --
3054 function get_object_key return varchar2
3055 is
3056 begin
3057 return g_object_key;
3058 end get_object_key;
3059 function get_effective_date return date
3060 is
3061 begin
3062 return g_effective_date;
3063 end get_effective_date;
3064 function get_parent_key return varchar2
3065 is
3066 begin
3067 return g_parent_key;
3068 end get_parent_key;
3069 function get_assignment_id return number
3070 is
3071 begin
3072 return g_asg_id;
3073 end get_assignment_id;
3074 function get_element_entry_id return number
3075 is
3076 begin
3077 return g_ee_id;
3078 end get_element_entry_id;
3079 --
3080
3081 /* ----------------------------------------------------------
3082 Get master mode that will tell us which version of main
3083 driving query to use.
3084 ---------------------------------------------------------- */
3085 FUNCTION get_master_process_mode
3086 (
3087 p_process_mode IN VARCHAR2
3088 ) return VARCHAR2 IS
3089
3090 l_master_process_mode VARCHAR2(30);
3091
3092 l_proc varchar2(80) := g_pkg||'.get_master_process_mode';
3093 BEGIN
3094
3095 -- >>> BUG 3329824- Performance issues, so massive restructure
3096 -- >>> There are 5 processing modes of executing interpreter, process_modes...
3097 -- > ENTRY_CREATION_DATE, ASG_CREATION,ENTRY_RETROSTATUS,
3098 -- > ENTRY_EFECTIVE_DATE , PRORATION
3099
3100 -- Additional glossary...
3101 -- p_process eg vals of ppe.retroactive_status -now obsoleted
3102 -- i.e. U nprocessed, P rocessing, C ompleted
3103 -- p_mode eg 'DATE_EARNED', 'DATE_PROCESSED', stored against event-update
3104
3105 -- When we start looking for candidate rows in ppe, we restrict on 3 main
3106 -- areas, process and mode, entry_creation_date and entry_effective_date
3107 -- Whereas previously bind variables were set to make these restrictions
3108 -- this was not performant, and thus now we can split in to two subsets of
3109 -- restriction and then use two different driving cursors. As we dont then
3110 -- bind in massive unused date ranges, the CBO can do its job much better.
3111 -- So based on the logic below, we use the new p_master_process_mode to split
3112 -- in to the two possible queries.
3113
3114 -- Binding restrictions \ Process Modes
3115 -- ENTRY_EFFECTIVE_DATE ASG_CREATION PRORATION
3116 -- ENTRY_RETROSTATUS ENTRY_CREATION_DATE
3117 -- process : X O X X X
3118 -- mode : X O X O X
3119 -- eff date : O X X X O
3120 -- creation date : X X O O X
3121
3122 if ( p_process_mode = 'ENTRY_EFFECTIVE_DATE' or
3123 p_process_mode = 'PRORATION' or
3124 p_process_mode = 'ENTRY_RETROSTATUS' ) then
3125 -- care about process, mode and effective date
3126 l_master_process_mode := 'EFF';
3127
3128 elsif (p_process_mode = 'ASG_CREATION' or
3129 p_process_mode = 'ENTRY_CREATION_DATE') then
3130 -- care about mode and creation date
3131 l_master_process_mode := 'CRE';
3132 else
3133 -- SHOULDNT HAVE NON-EXPLICIT CASES but robust
3134 l_master_process_mode := 'CRE';
3135 end if;
3136
3137 -- So in summary, our main driving cursors will be duplicated
3138 -- => we dont pass in blank date ranges where possible
3139 -- i) master mode EFF will be tuned to be performant for
3140 -- EFFECTIVE DATE: eg use PPE_N5: assignment_id, effective_date
3141 -- ii)master mode CRE, tuned to be performant for
3142 -- CREATION DATE: eg use PPE_N3: assignment_id, creation_date
3143 RETURN l_master_process_mode;
3144 end get_master_process_mode;
3145
3146
3147 /*Bug 7409433 -- Added parameter p_penserv_mode */
3148 procedure save_disco_details
3149 (
3150 p_effective_date IN DATE,
3151 p_creation_date IN DATE DEFAULT NULL,
3152 p_update_type IN VARCHAR2,
3153 p_change_mode IN VARCHAR2,
3154 p_process_mode IN VARCHAR2,
3155 p_proration_type IN VARCHAR2,
3156 p_datetracked_event IN VARCHAR2,
3157 p_column_name IN VARCHAR2 default 'none',
3158 p_old_val IN VARCHAR2 default null,
3159 p_new_val IN VARCHAR2 default null,
3160 p_change_values IN VARCHAR2 default null,
3161 p_element_entry_id IN NUMBER default null,
3162 p_surrogate_key IN VARCHAR2,
3163 p_dated_table_id IN NUMBER,
3164 p_table_name IN VARCHAR2,
3165 p_disco IN NUMBER,
3166 p_start_date IN DATE,
3167 p_end_date IN DATE,
3168 p_assignment_action_id IN NUMBER,
3169 p_business_group_id IN NUMBER,
3170 p_assignment_id IN NUMBER,
3171 p_penserv_mode IN VARCHAR2 default 'N',
3172 p_date_counter IN OUT NOCOPY number,
3173 p_global_env IN OUT NOCOPY t_global_env_rec,
3174 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
3175 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
3176 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
3177 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type
3178 )
3179 is
3180 save_event boolean;
3181 curr_ptr number;
3182
3183 run_event_proc boolean;
3184 event_proc_res VARCHAR2(40);
3185 l_update_type varchar2(10);
3186 l_element_entry_id number;
3187
3188 cursor get_update_type IS
3189 select update_type
3190 from pay_Datetracked_events
3191 where datetracked_event_id = p_datetracked_event;
3192
3193 begin
3194 --
3195 run_event_proc := TRUE;
3196 event_proc_res := 'TRUE';
3197 --
3198 -- We could be saving the results in one for 2 modes.
3199 -- First mode is that the events are being generated for a
3200 -- single element entry.
3201 -- Second mode is that a list of element entries have been
3202 -- suppled cross referencing the datetracked events for which
3203 -- we are looking.
3204 --
3205 if (p_global_env.datetrack_ee_tab_use = FALSE) then
3206 /*
3207 if (p_disco = G_DISCO_STANDARD) then
3208 --
3209 add_found_event (
3210 p_effective_date => p_effective_date,
3211 p_creation_date => p_creation_date,
3212 p_update_type => p_update_type,
3213 p_change_mode => p_change_mode,
3214 p_proration_type => p_proration_type,
3215 p_datetracked_event => p_datetracked_event,
3216 p_column_name => p_column_name,
3217 p_change_values => p_change_values,
3218 p_element_entry_id => p_element_entry_id,
3219 p_surrogate_key => p_surrogate_key,
3220 p_dated_table_id => p_dated_table_id,
3221 p_date_counter => p_date_counter,
3222 p_global_env => p_global_env,
3223 t_proration_dates_temp => t_proration_dates_temp,
3224 t_proration_change_type => t_proration_change_type,
3225 t_proration_type => t_proration_type,
3226 t_detailed_output => t_detailed_output
3227 );
3228 --
3229 elsif (p_disco = G_DISCO_DF) then
3230 add_found_event (
3231 p_effective_date => p_effective_date,
3232 p_creation_date => p_creation_date,
3233 p_update_type => p_update_type,
3234 p_change_mode => p_change_mode,
3235 p_proration_type => p_proration_type,
3236 p_datetracked_event => p_datetracked_event,
3237 p_element_entry_id => p_element_entry_id,
3238 p_surrogate_key => p_surrogate_key,
3239 p_dated_table_id => p_dated_table_id,
3240 p_date_counter => p_date_counter,
3241 p_global_env => p_global_env,
3242 t_proration_dates_temp => t_proration_dates_temp,
3243 t_proration_change_type => t_proration_change_type,
3244 t_proration_type => t_proration_type,
3245 t_detailed_output => t_detailed_output
3246 );
3247 --
3248 else
3249 pay_core_utils.assert_condition(
3250 'pay_interpreter_pkg.save_disco_details:1',
3251 1 = 2);
3252 end if;
3253 */
3254
3255 /* If its element entries don't run the procedure
3256 validation, assume it's qualified
3257 */
3258 if ( p_table_name = 'PAY_ELEMENT_ENTRIES_F'
3259 or p_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F') then
3260 run_event_proc := FALSE;
3261 event_proc_res := 'TRUE';
3262
3263 end if;
3264 --
3265 perform_qualifications
3266 (
3267 p_table_id => p_dated_table_id,
3268 p_final_effective_date => p_effective_date,
3269 p_creation_date => p_creation_date,
3270 p_start_date => p_start_date,
3271 p_end_date => p_end_date,
3272 p_element_entry_id => p_element_entry_id,
3273 p_assignment_action_id => p_assignment_action_id,
3274 p_business_group_id => p_business_group_id,
3275 p_assignment_id => p_assignment_id,
3276 p_process_mode => p_process_mode,
3277 p_update_type => p_update_type,
3278 p_change_mode => p_change_mode,
3279 p_change_values => p_change_values,
3280 p_surrogate_key => p_surrogate_key,
3281 p_date_counter => p_date_counter,
3282 p_global_env => p_global_env,
3283 p_datetracked_id => p_datetracked_event,
3284 p_column_name => p_column_name,
3285 p_old_value => p_old_val,
3286 p_new_value => p_new_val,
3287 p_proration_style => p_proration_type,
3288 t_proration_dates_temp => t_proration_dates_temp,
3289 t_proration_change_type => t_proration_change_type,
3290 t_proration_type => t_proration_type,
3291 t_detailed_output => t_detailed_output,
3292 p_run_event_proc => run_event_proc,
3293 p_event_proc_res => event_proc_res
3294 );
3295 else
3296 --
3297 if (g_dbg) then
3298 hr_utility.trace('Candidate has passed tests, final test as in ee list mode');
3299 end if;
3300 if (glo_datetrack_ee_hash_tab.exists(p_datetracked_event)) then
3301 --
3302 curr_ptr := glo_datetrack_ee_hash_tab(p_datetracked_event);
3303
3304 open get_update_type; -- 7190857
3305 fetch get_update_type into l_update_type;
3306 close get_update_type;
3307
3308 while (curr_ptr is not null) loop
3309 --
3310 l_element_entry_id := glo_datetrack_ee_tab(curr_ptr).element_entry_id;
3311
3312 -- Need to decide if the event is relevent to the current entry
3313 --
3314 save_event := FALSE;
3315 if (p_table_name = 'PAY_ELEMENT_ENTRIES_F') then
3316 --
3317 if(l_element_entry_id = p_surrogate_key) then
3318 save_event := TRUE;
3319 end if;
3320 --
3321 elsif (p_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F') then
3322 --
3323 declare
3324 l_dummy varchar2(2);
3325 l_ee_id pay_element_entries_f.element_entry_id%type;
3326 begin
3327 l_ee_id :=
3328 l_element_entry_id;
3329 select ''
3330 into l_dummy
3331 from dual
3332 where exists (select ''
3333 from pay_element_entry_values_f
3334 where element_entry_id = l_ee_id
3335 and element_entry_value_id = p_surrogate_key
3336 );
3337 save_event := TRUE;
3338 exception
3339 when no_data_found then
3340 save_event := FALSE;
3341 end;
3342 --
3343 else
3344 save_event := TRUE;
3345 end if;
3346
3347 -- 7190857. Move the increment of the pointer above. If no match is found between p_surrogate_key and element entry ids
3348 -- after all the iterations, switch save_event to TRUE for PURGE update_type.
3349
3350 curr_ptr := glo_datetrack_ee_tab(curr_ptr).next_ptr;
3351
3352 if( curr_ptr IS NULL AND
3353 save_event = FALSE and
3354 p_table_name = 'PAY_ELEMENT_ENTRIES_F' and
3355 l_update_type = 'P' and
3356 p_penserv_mode = 'N' ) THEN /*Bug 7409433 Added condition p_penserv_mode ='N' */
3357
3358 save_event := TRUE;
3359
3360 END if;
3361
3362 --
3363 if (save_event = TRUE) then
3364 /*
3365 if (p_disco = G_DISCO_STANDARD) then
3366 --
3367 add_found_event (
3368 p_effective_date => p_effective_date,
3369 p_creation_date => p_creation_date,
3370 p_update_type => p_update_type,
3371 p_change_mode => p_change_mode,
3372 p_proration_type => p_proration_type,
3373 p_datetracked_event => p_datetracked_event,
3374 p_column_name => p_column_name,
3375 p_change_values => p_change_values,
3376 p_element_entry_id =>
3377 l_element_entry_id,
3378 p_surrogate_key => p_surrogate_key,
3379 p_dated_table_id => p_dated_table_id,
3380 p_date_counter => p_date_counter,
3381 p_global_env => p_global_env,
3382 t_proration_dates_temp => t_proration_dates_temp,
3383 t_proration_change_type => t_proration_change_type,
3384 t_proration_type => t_proration_type,
3385 t_detailed_output => t_detailed_output
3386 );
3387 --
3388 elsif (p_disco = G_DISCO_DF) then
3389 add_found_event (
3390 p_effective_date => p_effective_date,
3391 p_creation_date => p_creation_date,
3392 p_update_type => p_update_type,
3393 p_change_mode => p_change_mode,
3394 p_proration_type => p_proration_type,
3395 p_datetracked_event => p_datetracked_event,
3396 p_element_entry_id =>
3397 l_element_entry_id,
3398 p_surrogate_key => p_surrogate_key,
3399 p_dated_table_id => p_dated_table_id,
3400 p_date_counter => p_date_counter,
3401 p_global_env => p_global_env,
3402 t_proration_dates_temp => t_proration_dates_temp,
3403 t_proration_change_type => t_proration_change_type,
3404 t_proration_type => t_proration_type,
3405 t_detailed_output => t_detailed_output
3406 );
3407 --
3408 else
3409 pay_core_utils.assert_condition(
3410 'pay_interpreter_pkg.save_disco_details:1',
3411 1 = 2);
3412 end if;
3413 */
3414
3415 /* If its element entries don't run the procedure
3416 validation, assume it's qualified
3417 */
3418 if ( p_table_name = 'PAY_ELEMENT_ENTRIES_F'
3419 or p_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F') then
3420 run_event_proc := FALSE;
3421 event_proc_res := 'TRUE';
3422 end if;
3423 perform_qualifications
3424 (
3425 p_table_id => p_dated_table_id,
3426 p_final_effective_date => p_effective_date,
3427 p_creation_date => p_creation_date,
3428 p_start_date => p_start_date,
3429 p_end_date => p_end_date,
3430 p_element_entry_id =>
3431 l_element_entry_id,
3432 p_assignment_action_id => p_assignment_action_id,
3433 p_business_group_id => p_business_group_id,
3434 p_assignment_id => p_assignment_id,
3435 p_process_mode => p_process_mode,
3436 p_update_type => p_update_type,
3437 p_change_mode => p_change_mode,
3438 p_change_values => p_change_values,
3439 p_surrogate_key => p_surrogate_key,
3440 p_date_counter => p_date_counter,
3441 p_global_env => p_global_env,
3442 p_datetracked_id => p_datetracked_event,
3443 p_column_name => p_column_name,
3444 p_old_value => p_old_val,
3445 p_new_value => p_new_val,
3446 p_proration_style => p_proration_type,
3447 t_proration_dates_temp => t_proration_dates_temp,
3448 t_proration_change_type => t_proration_change_type,
3449 t_proration_type => t_proration_type,
3450 t_detailed_output => t_detailed_output,
3451 p_run_event_proc => run_event_proc,
3452 p_event_proc_res => event_proc_res
3453 );
3454 end if;
3455 --
3456 end loop;
3457 --
3458 end if;
3459 --
3460 end if;
3461 --
3462 end save_disco_details;
3463 --
3464
3465
3466 /* ----------------------------------------------------------
3467 Procedure: extra_tests_dbt_df
3468 High Level Summary:
3469 We can do a single query to identify all the df events for a given
3470 table.
3471 So do this for this process_event, then mark in cache so no other events
3472 on the same table are performed as weve already recorded them.
3473
3474 --
3475 Detail Logic:
3476 /* ----------------------------------------------------------
3477
3478 ---------------------------------------------------------- */
3479
3480 PROCEDURE extra_tests_dbt_df
3481 (
3482 p_element_entry_id IN pay_element_entries.element_entry_id%type,
3483 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type,
3484 p_business_group_id IN per_business_groups.business_group_id%type,
3485 p_assignment_id IN per_all_assignments_f.assignment_id%type,
3486 p_mode IN VARCHAR2,
3487 p_process IN VARCHAR2,
3488 p_process_mode IN VARCHAR2,
3489 p_event_group_id IN pay_event_groups.event_group_id%type,
3490 p_start_date IN date,
3491 p_end_date IN date,
3492 p_penserv_mode IN VARCHAR2 default 'N', /*Bug 7409433 */
3493 p_date_counter IN OUT NOCOPY number,
3494 p_global_env IN OUT NOCOPY t_global_env_rec,
3495 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
3496
3497 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
3498 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
3499 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
3500 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
3501
3502 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
3503 p_dtevent_rec IN t_distinct_table_rec ,
3504 p_disco IN OUT NOCOPY number
3505 ) IS
3506
3507 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dbt_df';
3508 l_effective_date date;
3509 l_count number;
3510
3511 BEGIN
3512 --
3513 -- If the process event is a delete and is the earliest
3514 -- delete for the object. Also if there is an insert
3515 -- then it must be a Delete Next or Delete Future.
3516 --
3517 if (p_pro_evt_rec.event_type = 'D') then
3518 --
3519 -- Get the min date of the Delete rows for this
3520 -- Date Track transaction
3521 select min(effective_date)
3522 into l_effective_date
3523 from pay_process_events
3524 where surrogate_key = p_pro_evt_rec.surrogate_key
3525 and event_update_id = p_pro_evt_rec.event_update_id
3526 and creation_date = p_pro_evt_rec.creation_date;
3527 --
3528 -- If the min date matches the current process event then we may
3529 -- have a Delete Next.
3530 if (l_effective_date = p_pro_evt_rec.effective_date) then
3531 --
3532 select count(*)
3533 into l_count
3534 from pay_dated_tables pdt,
3535 pay_event_updates peu,
3536 pay_process_events ppe
3537 where pdt.table_name = p_pro_evt_rec.table_name
3538 and pdt.dated_table_id = peu.dated_table_id
3539 and peu.event_type = 'I'
3540 and peu.change_type = p_pro_evt_rec.change_mode
3541 and peu.event_update_id = ppe.event_update_id
3542 and ppe.surrogate_key = p_pro_evt_rec.surrogate_key
3543 and ppe.creation_date = p_pro_evt_rec.creation_date;
3544 --
3545 -- If the count is not 0 then there was an insert at the
3546 -- same time as a delete, hence we must assume that this
3547 -- is a Delete Next
3548 --
3549 if (l_count <> 0) then
3550 --
3551 save_disco_details (
3552 p_effective_date => p_pro_evt_rec.effective_date,
3553 p_creation_date => p_pro_evt_rec.creation_date,
3554 p_update_type => 'DF',
3555 p_change_mode => p_pro_evt_rec.change_mode,
3556 p_process_mode => p_process_mode,
3557 p_proration_type => p_dtevent_rec.proration_type,
3558 p_datetracked_event => p_dtevent_rec.datetracked_event_id,
3559 p_element_entry_id => p_element_entry_id,
3560 p_surrogate_key => p_pro_evt_rec.surrogate_key,
3561 p_dated_table_id => p_dtevent_rec.table_id,
3562 p_table_name => p_dtevent_rec.table_name,
3563 p_disco => p_disco,
3564 p_start_date => p_start_date,
3565 p_end_date => p_end_date,
3566 p_assignment_action_id => p_assignment_action_id,
3567 p_business_group_id => p_business_group_id,
3568 p_assignment_id => p_assignment_id,
3569 p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
3570 p_date_counter => p_date_counter,
3571 p_global_env => p_global_env,
3572 t_proration_dates_temp => t_proration_dates_temp,
3573 t_proration_change_type => t_proration_change_type,
3574 t_proration_type => t_proration_type,
3575 t_detailed_output => t_detailed_output
3576 );
3577 --
3578 end if;
3579 --
3580 end if;
3581 --
3582 -- If we ahave an Update to the effective end date, such that the
3583 -- calculation date is earlier than the effective date then
3584 -- it must be a Delete next change.
3585 --
3586 elsif (p_pro_evt_rec.event_type = 'U') then
3587 --
3588 if (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
3589 and p_pro_evt_rec.effective_date > p_pro_evt_rec.calculation_date)
3590 then
3591 --
3592 save_disco_details (
3593 p_effective_date => p_pro_evt_rec.calculation_date,
3594 p_creation_date => p_pro_evt_rec.creation_date,
3595 p_update_type => 'DF',
3596 p_change_mode => p_pro_evt_rec.change_mode,
3597 p_process_mode => p_process_mode,
3598 p_proration_type => p_dtevent_rec.proration_type,
3599 p_datetracked_event => p_dtevent_rec.datetracked_event_id,
3600 p_element_entry_id => p_element_entry_id,
3601 p_surrogate_key => p_pro_evt_rec.surrogate_key,
3602 p_dated_table_id => p_dtevent_rec.table_id,
3603 p_table_name => p_dtevent_rec.table_name,
3604 p_disco => p_disco,
3605 p_start_date => p_start_date,
3606 p_end_date => p_end_date,
3607 p_assignment_action_id => p_assignment_action_id,
3608 p_business_group_id => p_business_group_id,
3609 p_assignment_id => p_assignment_id,
3610 p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
3611 p_date_counter => p_date_counter,
3612 p_global_env => p_global_env,
3613 t_proration_dates_temp => t_proration_dates_temp,
3614 t_proration_change_type => t_proration_change_type,
3615 t_proration_type => t_proration_type,
3616 t_detailed_output => t_detailed_output
3617 );
3618 --
3619 end if;
3620 --
3621 end if;
3622 --
3623 if (g_dbg) then
3624 hr_utility.set_location(l_proc, 900);
3625 end if;
3626
3627 END extra_tests_dbt_df;
3628
3629
3630 /* ----------------------------------------------------------
3631 Procedure: extra_tests_dbt_u_e
3632 High Level Summary:
3633 A complex one. Need to differentiate between all of the following
3634 --
3635 Detail Logic:
3636 /* ----------------------------------------------------------
3637 Look through PPE for an update or end-date
3638 --
3639 -- all dbt_df now in extra_tests_dbt_df
3640
3641 Driving Query gets candidate rows...
3642 ...that may be indicative of one of the six situations.
3643 API-U,API-E,API-DF and , DT-U,DT-E
3644 The first two are indicated by an update to the end-date column with
3645 eff_date = calc_date.
3646 The third is indicated by an update to the end-date column with
3647 eff_date > calc_date
3648 The fourth is definitively indicated by any alteration to the
3649 start-date column.
3650 The last two situations are recorded elsewhere
3651
3652 Further Tests involve...
3653 ...differentiating between API-U and API-E by checking the base table
3654 for future dated records. If no future rows exist then must be an E.
3655 (NB. At this point we note that if an E occurs; and then it is undone
3656 and a future row inserted; this test will fail and an API-U will be
3657 recorded instead of the actual original API-E. It has been decided
3658 this is an acceptable behaviour.)
3659
3660 API-DF and DT-U require no more tests.
3661 DT-E and DT-DF cannot be distinguished from each other. Proposed
3662 behaviour is to flag this occurrence as a DT-DF.
3663
3664 Further advanced checking occurs against each update candidate to make
3665 sure we are interested in this type and values of the updates.
3666
3667 ---------------------------------------------------------- */
3668
3669 PROCEDURE extra_tests_dbt_u_e
3670 (
3671 p_element_entry_id IN pay_element_entries.element_entry_id%type,
3672 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type,
3673 p_business_group_id IN per_business_groups.business_group_id%type,
3674 p_assignment_id IN per_all_assignments_f.assignment_id%type,
3675 p_process_mode IN VARCHAR2,
3676 p_event_group_id IN pay_event_groups.event_group_id%type,
3677 p_start_date IN date,
3678 p_end_date IN date,
3679 p_penserv_mode IN VARCHAR2 DEFAULT 'N',
3680 p_date_counter IN OUT NOCOPY number,
3681 p_global_env IN OUT NOCOPY t_global_env_rec,
3682 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
3683
3684 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
3685 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
3686 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
3687 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
3688
3689 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
3690 p_dtevent_rec IN t_distinct_table_rec ,
3691 p_disco IN OUT NOCOPY NUMBER
3692 ) IS
3693
3694 l_search varchar2(30) := p_dtevent_rec.update_type;
3695 l_statement varchar2(800);
3696 l_dummy number := null;
3697 l_date_dummy date := hr_api.g_eot;
3698
3699 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dbt_u_e';
3700
3701 BEGIN
3702
3703 if (g_dbg) then
3704 hr_utility.set_location(l_proc, 10);
3705 end if;
3706
3707 /* Only interested in the event if it is a change to the
3708 effective date columns
3709 */
3710 if (p_pro_evt_rec.updated_column_name = p_dtevent_rec.start_date_name
3711 or p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
3712 ) then
3713 --
3714 -- Look for an Update (via Forms => DT-U
3715 --
3716 IF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.start_date_name
3717 and l_search = 'U') THEN
3718 --defo got DT-U
3719 --Allow existing involved code to further test and add to our list
3720 if (g_dbg) then
3721 hr_utility.set_location(l_proc, 25);
3722 end if;
3723 event_group_table_updated(p_element_entry_id,
3724 p_assignment_action_id,
3725 p_business_group_id,
3726 p_assignment_id,
3727 p_process_mode,
3728 p_pro_evt_rec.change_mode,
3729 p_event_group_id,
3730 p_dtevent_rec.table_id,
3731 p_dtevent_rec.table_name,
3732 p_dtevent_rec.surrogate_key_name,
3733 p_pro_evt_rec.surrogate_key,
3734 p_dtevent_rec.start_date_name,
3735 p_dtevent_rec.end_date_name,
3736 p_pro_evt_rec.effective_date,
3737 p_pro_evt_rec.creation_date,
3738 p_start_date,
3739 p_end_date,
3740 p_pro_evt_rec.updated_column_name,
3741 p_date_counter,
3742 p_global_env,
3743 p_dtevent_rec.proration_type,
3744 t_dynamic_sql,
3745 t_proration_dates_temp,
3746 t_proration_change_type,
3747 t_proration_type,
3748 t_detailed_output );
3749
3750 -- all dbt_df now in extra_tests_dbt_df
3751 -- ELSIF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
3752 -- and p_pro_evt_rec.effective_date > p_pro_evt_rec.calculation_date
3753 -- and l_search = 'DF') THEN
3754 -- --Add found API-DF to store
3755 -- if (g_dbg) then
3756 -- hr_utility.set_location(l_proc, 35);
3757 -- end if;
3758 -- p_disco := G_DISCO_DF;
3759 --
3760 ELSE
3761 -- >>> PHASE 4: Differentiate between remaining API-U,API-E,DT-E,DT-DF
3762 --
3763 --
3764 if (g_dbg) then
3765 hr_utility.set_location(l_proc, 45);
3766 end if;
3767
3768
3769 l_statement :=
3770 'select max('||p_dtevent_rec.end_date_name||')'||
3771 ' from '|| p_dtevent_rec.table_name||
3772 ' where '|| p_dtevent_rec.surrogate_key_name ||' = :1 '||
3773 ' and '|| p_dtevent_rec.end_date_name || '>= :2'||
3774 ' group by '|| p_dtevent_rec.surrogate_key_name;
3775
3776 if (g_dbg) then
3777 hr_utility.trace('- Dynamic SQL: '||l_statement);
3778 end if;
3779 begin
3780 execute immediate l_statement
3781 into l_date_dummy
3782 using p_pro_evt_rec.surrogate_key, --:1
3783 p_pro_evt_rec.effective_date; --:2
3784
3785 exception
3786 when no_data_found then
3787 --
3788 l_dummy := 0; -- No data, weve had a purge
3789 -- process as UPDATE
3790 end;
3791
3792
3793 /* Added for bug 6595505
3794 For Datetracked tables, after datetrack update, l_date_dummy is greater
3795 than the effective date. But for PER_ADDRESSES, l_date_dummy will be equal
3796 to effective date even for an UPDATE as it is not datetracked and the new
3797 record will have a different primary key.
3798 */
3799
3800 if ( p_dtevent_rec.table_name = 'PER_ADDRESSES' and
3801 l_date_dummy = p_pro_evt_rec.effective_date AND
3802 p_penserv_mode <> 'A' ) then -- bug 7211447
3803 --
3804 l_dummy := 2;
3805 --
3806 elsif (l_date_dummy = p_pro_evt_rec.effective_date) THEN -- If latest is our date then no future rows,
3807 -- End date has occurred
3808 l_dummy := 1; --Eff date is max, process as END DATE
3809 --
3810 elsif (l_date_dummy > p_pro_evt_rec.effective_date ) then
3811 --
3812 l_dummy := 2; -- Eff date is less max,later rows exist
3813 -- process as UPDATE
3814 end if;
3815
3816
3817 /*
3818 --Check the base table to see if any future dated records exist,
3819 l_statement :=
3820 'SELECT count(*) FROM '||p_dtevent_rec.table_name||
3821 ' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 '||
3822 ' AND '|| p_dtevent_rec.end_date_name || ' >= :2 ';
3823 --
3824 if (g_dbg) then
3825 hr_utility.trace('-Dynamic SQL: '||l_statement);
3826 end if;
3827 --
3828 execute immediate l_statement
3829 into l_dummy
3830 using p_pro_evt_rec.surrogate_key, --:1
3831 p_pro_evt_rec.effective_date; --:2
3832
3833 --The subset of rows for DT-E, may actually be one of four
3834 -- DT-I + DT-P, API-I + API-P, DT-DF or DT-E
3835 -- If a purge has occurred then cursor will find no rows, I.e.
3836
3837 */
3838 -- From our dummy system
3839 --l_dummy = 0 => purge occurred
3840 --l_dummy = 1 => end_date occurred
3841 --l_dummy = 2 => possible update occurred
3842
3843 IF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
3844 and l_dummy > 1
3845 and l_search = 'U') THEN
3846 --
3847 if (g_dbg) then
3848 hr_utility.set_location(l_proc, 55);
3849 end if;
3850 --
3851 --Allow existing involved code to further test and add API-U
3852 event_group_table_updated(p_element_entry_id,
3853 p_assignment_action_id,
3854 p_business_group_id,
3855 p_assignment_id,
3856 p_process_mode,
3857 p_pro_evt_rec.change_mode,
3858 p_event_group_id,
3859 p_dtevent_rec.table_id,
3860 p_dtevent_rec.table_name,
3861 p_dtevent_rec.surrogate_key_name,
3862 p_pro_evt_rec.surrogate_key,
3863 p_dtevent_rec.start_date_name,
3864 p_dtevent_rec.end_date_name,
3865 p_pro_evt_rec.effective_date,
3866 p_pro_evt_rec.creation_date,
3867 p_start_date,
3868 p_end_date,
3869 p_pro_evt_rec.updated_column_name,
3870 p_date_counter,
3871 p_global_env,
3872 p_dtevent_rec.proration_type,
3873 t_dynamic_sql,
3874 t_proration_dates_temp,
3875 t_proration_change_type,
3876 t_proration_type,
3877 t_detailed_output );
3878
3879 ELSIF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
3880 and l_dummy = 1
3881 and l_search = 'E') THEN
3882 --
3883 if (g_dbg) then
3884 hr_utility.set_location(l_proc, 65);
3885 end if;
3886 --
3887 --Add found API-E to store
3888 p_disco := G_DISCO_STANDARD;
3889
3890 -- all dbt_df now in extra_tests_dbt_df
3891 -- ELSIF (p_pro_evt_rec.updated_column_name is null
3892 --
3893 -- and l_dummy = 1
3894 -- and l_search = 'DF') THEN
3895 -- if (g_dbg) then
3896 -- hr_utility.set_location(l_proc, 75);
3897 -- end if;
3898 -- We have got either a DT-E or DT-F
3899 -- NB. As it is impossible to identify exactly which one
3900 -- it is proposed that it is simply classified as DT-DF
3901 -- Add found DF event to our store
3902 -- p_disco := G_DISCO_STANDARD;
3903
3904
3905 END IF;
3906
3907 END IF; --end of main IF
3908 end if; -- IS it effective_start_date or effective_end_date.
3909
3910 if (g_dbg) then
3911 hr_utility.set_location(l_proc, 900);
3912 end if;
3913 END extra_tests_dbt_u_e;
3914
3915
3916 /* ----------------------------------------------------------
3917 Procedure: extra_tests_dyt_pkg_e
3918 High Level Summary:
3919 Look at candidate process event to see if it is an end date
3920 --
3921 Detail Logic:
3922 1) Check it is flagged as a Datetrack delete = End dated
3923 2) Check this end date still applies
3924
3925 ---------------------------------------------------------- */
3926 PROCEDURE extra_tests_dyt_pkg_e
3927 (
3928 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
3929 p_dtevent_rec IN t_distinct_table_rec ,
3930 p_disco IN OUT NOCOPY number
3931 ) IS
3932 l_statement varchar2(800);
3933 l_dummy number := null;
3934
3935 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dyt_pkg_e';
3936
3937 BEGIN
3938 if (g_dbg) then
3939 hr_utility.set_location(l_proc, 10);
3940 end if;
3941 if (p_pro_evt_rec.event_type = hr_api.g_delete) then
3942
3943 -- >>> PHASE 2: Check e is still relevant
3944 --
3945 hr_utility.set_location(l_proc, 20);
3946 -- If end date is still relevant, then base key has no rows
3947 -- with end dates later than this end date.
3948
3949 --Check the base table to see if any future dated records exist,
3950 l_statement :=
3951 'SELECT count(*) FROM '||p_dtevent_rec.table_name||
3952 ' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 '||
3953 ' AND '|| p_dtevent_rec.end_date_name ||' >= :2';
3954 if (g_dbg) then
3955 hr_utility.trace(l_statement);
3956 end if;
3957 execute immediate l_statement
3958 into l_dummy
3959 using p_pro_evt_rec.surrogate_key, --:1
3960 p_pro_evt_rec.effective_date ; --:2
3961
3962 IF ( l_dummy = 1) then
3963 --Add found event to our store
3964 p_disco := G_DISCO_STANDARD;
3965 end if;
3966
3967 end if; --If not delete type then dont do anything
3968
3969 if (g_dbg) then
3970 hr_utility.set_location(l_proc, 900);
3971 end if;
3972 END extra_tests_dyt_pkg_e;
3973
3974 /* ----------------------------------------------------------
3975 Procedure: get_dbt_i_p_cache
3976 High Level Summary:
3977 Build up a global cache of ppe data for quick reference
3978 Essentially getting min and max dates
3979 Used by extra_tests_dbt_p and extra_tests_i
3980 --
3981 Detail Logic:
3982 1) Check the event update is the right type, a deletion
3983 2) Check this is the latest created ppe event for this
3984 event_update/surrogate_key combination
3985 3) Check this deletion is part of a purge, no rows in base table
3986 also check we havent got this event yet under the guise of another
3987 ppe row, 'cos creation date may be identical for several
3988
3989 ---------------------------------------------------------- */
3990 PROCEDURE get_dbt_i_p_cache
3991 (
3992 p_surrogate_key IN pay_process_events.surrogate_key%type ,
3993 p_event_update_id IN pay_process_events.event_update_id%type ,
3994 p_assignment_id IN per_all_assignments.assignment_id%type,
3995 p_change_mode IN pay_event_updates.change_type%type,
3996 p_cache_number IN OUT NOCOPY NUMBER
3997 ) IS
3998
3999 CURSOR csr_get_cache_asgid (
4000 cp_base_record_id in number,
4001 cp_event_update_id in number,
4002 cp_mode in varchar2,
4003 cp_assignment_id in number) is
4004 SELECT min(creation_date),
4005 max(creation_date)
4006 FROM PAY_PROCESS_EVENTS
4007 WHERE event_update_id = cp_event_update_id
4008 AND surrogate_key = cp_base_record_id
4009 AND assignment_id is not null
4010 AND assignment_id = cp_assignment_id
4011 AND change_type = nvl(cp_mode,change_type);
4012
4013 CURSOR csr_get_cache_noasg (
4014 cp_base_record_id in number,
4015 cp_event_update_id in number,
4016 cp_mode in varchar2) is
4017 SELECT min(creation_date),
4018 max(creation_date)
4019 FROM PAY_PROCESS_EVENTS
4020 WHERE event_update_id = cp_event_update_id
4021 AND surrogate_key = cp_base_record_id
4022 AND assignment_id is null
4023 AND change_type = nvl(cp_mode,change_type);
4024
4025 l_proc VARCHAR2(80) := g_pkg||'.get_dbt_i_p_cache';
4026
4027 l_statement varchar2(800);
4028 l_dummy number := null;
4029
4030 l_key varchar2(240);
4031 l_min_date date;
4032 l_max_date date;
4033 l_got_flag varchar2(15);
4034
4035 l_pos number := 0;
4036
4037 BEGIN
4038 if (g_dbg) then
4039 hr_utility.set_location(l_proc, 10);
4040 end if;
4041
4042 l_key := p_event_update_id||'_'
4043 ||p_surrogate_key||'_'
4044 ||p_change_mode ;
4045
4046 for j in 1..g_key_date_cache.count() loop
4047 if (g_key_date_cache(j).key = l_key) then
4048 l_pos := j;
4049 if (g_traces) then
4050 hr_utility.trace('Found key in cache, pos '||l_pos);
4051 end if;
4052 l_min_date := g_key_date_cache(j).min_date;
4053 l_max_date := g_key_date_cache(j).max_date;
4054 l_got_flag := g_key_date_cache(j).got_flag;
4055 exit;
4056 end if;
4057 end loop;
4058
4059 --if no date obtained then get it now
4060 if (l_max_date is null) then
4061 if (g_traces) then
4062 hr_utility.trace('Not in cache, get dates now, key '||l_key);
4063 end if;
4064 if p_assignment_id is not null then
4065 open csr_get_cache_asgid(
4066 p_surrogate_key,
4067 p_event_update_id,
4068 p_change_mode,
4069 p_assignment_id);
4070 fetch csr_get_cache_asgid into l_min_date,l_max_date;
4071 close csr_get_cache_asgid;
4072 else
4073 open csr_get_cache_noasg(
4074 p_surrogate_key,
4075 p_event_update_id,
4076 p_change_mode);
4077 fetch csr_get_cache_noasg into l_min_date,l_max_date;
4078 close csr_get_cache_noasg;
4079 end if;
4080 l_got_flag := 'N';
4081 --store result in cache
4082 l_pos := g_key_date_cache.count()+1;
4083 g_key_date_cache(l_pos).key := l_key;
4084 g_key_date_cache(l_pos).min_date := l_min_date;
4085 g_key_date_cache(l_pos).max_date := l_max_date;
4086 g_key_date_cache(l_pos).got_flag := l_got_flag;
4087
4088 end if;
4089 if (g_traces) then
4090 hr_utility.trace('Cache utilised key = '||l_key||', pos = '||l_pos);
4091 hr_utility.trace('min date = '||
4092 to_char(g_key_date_cache(l_pos).min_date,'DD-MON-RR HH24:MI:SS'));
4093 hr_utility.trace('max date = '||
4094 to_char(g_key_date_cache(l_pos).max_date,'DD-MON-RR HH24:MI:SS'));
4095 end if;
4096 p_cache_number :=l_pos;
4097
4098 if (g_dbg) then
4099 hr_utility.set_location(l_proc, 900);
4100 end if;
4101 end get_dbt_i_p_cache;
4102
4103
4104 /* ----------------------------------------------------------
4105 Procedure: extra_tests_i --Both dbt and dyt_pkg
4106 High Level Summary:
4107 Look at candidate process event to see if it is an insert
4108 --
4109 Detail Logic:
4110 1) Check this candidate is indicative of an Insert event update
4111 2) Check this candidate is the absolute min creation date for this
4112 event update, surrogate key combination
4113 as clearly later inserts will not be a result of a true insert.
4114 3) No earlier dated row exist in base table, and one today
4115 eg sanity check point 2 and also DO NOT RETURN A TRUE INSERT
4116 IF THE DATA HAS BEEN PURGED (this is designed behaviour) against
4117 the concept of a total audit trail.
4118
4119 ---------------------------------------------------------- */
4120 PROCEDURE extra_tests_i
4121 (
4122 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
4123 p_dtevent_rec IN t_distinct_table_rec ,
4124 p_disco IN OUT NOCOPY number
4125 ) IS
4126
4127 l_statement varchar2(800);
4128 l_dummy number := null;
4129 l_pos number;
4130
4131 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_i';
4132
4133 BEGIN
4134 if (g_dbg) then
4135 hr_utility.set_location(l_proc, 10);
4136 end if;
4137
4138 -- 1 >>> Check the found process event is an Insert
4139 --Quick short-circuit opportunity
4140 -- Removed here as done prior in calling code
4141 --if (p_pro_evt_rec.event_type <> 'I'
4142 -- AND p_pro_evt_rec.event_type <> hr_api.g_insert) then
4143 -- p_disco := G_DISCO_NONE;
4144 -- RETURN;
4145 --end if;
4146
4147
4148 -- 2 >>> We are looking for true inserts, this MUST be the earliest
4149 -- ins record for this surrogate key and event update_id, if its not
4150 -- then return straight away
4151
4152 -- if weve got a min date stored in our cache, then use that for the
4153 -- comparison, ow go and get it now
4154
4155 get_dbt_i_p_cache
4156 (
4157 p_surrogate_key => p_pro_evt_rec.surrogate_key,
4158 p_event_update_id => p_pro_evt_rec.event_update_id,
4159 p_assignment_id => p_pro_evt_rec.assignment_id,
4160 p_change_mode => p_pro_evt_rec.change_mode,
4161 p_cache_number => l_pos
4162 ) ;
4163
4164
4165 if (g_traces) then
4166 hr_utility.trace('Compare date = '||to_char(g_key_date_cache(l_pos).min_date,'DD-MON-RR HH24:MI:SS')||
4167 ' - '||
4168 to_char(p_pro_evt_rec.creation_date,'DD-MON-RR HH24:MI:SS'));
4169 hr_utility.trace('Compare N flag to '||g_key_date_cache(l_pos).got_flag);
4170 end if;
4171
4172 --MAIN COMPARE
4173 if (p_pro_evt_rec.creation_date = g_key_date_cache(l_pos).min_date
4174 and g_key_date_cache(l_pos).got_flag = 'N' ) then
4175
4176 -- 3 >>> Now check base table to see if we had any rows for this surrogate key
4177 -- on the previous day (if weve got here we know weve only got the
4178 -- earliest created ppe row, but sanity check and test for any future purge
4179 -- (Non-dated dated tables we know cant have straight away)
4180 if (p_dtevent_rec.start_date_name is null) then
4181 --g_key_date_cache(l_pos).got_flag := 'Y' ;
4182 p_disco := G_DISCO_STANDARD;
4183 else
4184
4185 l_statement :=
4186 'SELECT count(*) FROM '||p_dtevent_rec.table_name||
4187 ' WHERE ' || p_dtevent_rec.surrogate_key_name || ' = :1 '||
4188 ' AND '|| p_dtevent_rec.start_date_name || ' <= :2 ';
4189
4190 if (g_dbg) then
4191 hr_utility.trace('>>> Dynamic SQL: '||l_statement);
4192 end if;
4193 execute immediate l_statement
4194 into l_dummy
4195 using p_pro_evt_rec.surrogate_key, --:1
4196 p_pro_evt_rec.effective_date; --:2
4197 --
4198 IF (l_dummy = 1) THEN
4199 --g_key_date_cache(l_pos).got_flag := 'Y' ;
4200 p_disco := G_DISCO_STANDARD;
4201 end if;
4202 end if;
4203 END IF; --end if else due to non-dated support
4204 if (g_dbg) then
4205 hr_utility.set_location(l_proc, 900);
4206 end if;
4207 END extra_tests_i;
4208
4209 /* ----------------------------------------------------------
4210 Procedure: extra_tests_dyt_pkg_u
4211 High Level Summary:
4212 Look at candidate process event to see if it is an update
4213 --
4214 Detail Logic:
4215 1) Check the event update is the right type
4216 AND peu.event_type = hr_api.g_update
4217 2) Check the update is one we're interested
4218
4219 ---------------------------------------------------------- */
4220 PROCEDURE extra_tests_dyt_pkg_u
4221 (
4222 p_element_entry_id IN pay_element_entries.element_entry_id%type,
4223 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type,
4224 p_business_group_id IN per_business_groups.business_group_id%type,
4225 p_assignment_id IN per_all_assignments_f.assignment_id%type,
4226 p_process_mode IN VARCHAR2,
4227 p_event_group_id IN pay_event_groups.event_group_id%type,
4228 p_start_date IN date,
4229 p_end_date IN date,
4230 p_date_counter IN OUT NOCOPY number,
4231 p_global_env IN OUT NOCOPY t_global_env_rec,
4232 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
4233
4234 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
4235 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
4236 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
4237 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
4238
4239 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
4240 p_dtevent_rec IN t_distinct_table_rec ,
4241 p_disco IN OUT NOCOPY number
4242 ) IS
4243
4244 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dyt_pkg_u';
4245
4246 BEGIN
4247 if (g_dbg) then
4248 hr_utility.set_location(l_proc, 10);
4249 end if;
4250
4251 if ( p_pro_evt_rec.event_type = hr_api.g_update
4252 or p_pro_evt_rec.event_type = hr_api.g_update_override
4253 or p_pro_evt_rec.event_type = hr_api.g_update_change_insert
4254 ) then
4255
4256 event_group_table_updated(p_element_entry_id,
4257 p_assignment_action_id,
4258 p_business_group_id,
4259 p_assignment_id,
4260 p_process_mode,
4261 p_pro_evt_rec.change_mode,
4262 p_event_group_id,
4263 p_dtevent_rec.table_id,
4264 p_dtevent_rec.table_name,
4265 p_dtevent_rec.surrogate_key_name,
4266 p_pro_evt_rec.surrogate_key,
4267 p_dtevent_rec.start_date_name,
4268 p_dtevent_rec.end_date_name,
4269 p_pro_evt_rec.effective_date,
4270 p_pro_evt_rec.creation_date,
4271 p_start_date,
4272 p_end_date,
4273 p_pro_evt_rec.updated_column_name,
4274 p_date_counter,
4275 p_global_env,
4276 p_dtevent_rec.proration_type,
4277 t_dynamic_sql,
4278 t_proration_dates_temp,
4279 t_proration_change_type,
4280 t_proration_type,
4281 t_detailed_output );
4282 --NB This is the only instance in extra_tests where we do not return a found event
4283 -- existing code has already added the event so dont explicitly flag it for addition here
4284 end if;
4285
4286 if (g_dbg) then
4287 hr_utility.set_location(l_proc, 900);
4288 end if;
4289 END extra_tests_dyt_pkg_u;
4290
4291 /* ----------------------------------------------------------
4292 Procedure: extra_tests_dbt_p
4293 High Level Summary:
4294 Look at candidate process event to see if it is a correction
4295 Similar to extra_tests_i
4296 --
4297 Detail Logic:
4298 1) Check the event update is the right type, a deletion
4299 2) Check this is the latest created ppe event for this
4300 event_update/surrogate_key combination
4301 3) Check this deletion is part of a purge, no rows in base table
4302 also check we havent got this event yet under the guise of another
4303 ppe row, 'cos creation date may be identical for several
4304
4305 ---------------------------------------------------------- */
4306 PROCEDURE extra_tests_dbt_p
4307 (
4308 p_pro_evt_rec IN t_mst_process_event_rec ,
4309 p_dtevent_rec IN t_distinct_table_rec ,
4310 p_disco IN OUT NOCOPY number
4311 ) IS
4312
4313
4314 l_statement varchar2(800);
4315 l_dummy number := null;
4316 l_pos number := 0;
4317
4318 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dbt_p';
4319
4320 BEGIN
4321 if (g_dbg) then
4322 hr_utility.set_location(l_proc, 10);
4323 end if;
4324
4325 -- 1 >>> Check the found process event is an Delete
4326 -- Quick short-circuit opportunity
4327 -- Removed as done prior in calling code
4328 -- if (p_pro_evt_rec.event_type <> 'D') then
4329 -- p_disco := G_DISCO_NONE;
4330 -- RETURN;
4331 -- end if;
4332
4333 -- 2 >>> We are looking for total purges, this MUST be the last
4334 -- del record for this surrogate key and event update_id, if its not
4335 -- then return straight away
4336 get_dbt_i_p_cache
4337 (
4338 p_surrogate_key => p_pro_evt_rec.surrogate_key,
4339 p_event_update_id => p_pro_evt_rec.event_update_id,
4340 p_assignment_id => p_pro_evt_rec.assignment_id,
4341 p_change_mode => p_pro_evt_rec.change_mode,
4342 p_cache_number => l_pos
4343 ) ;
4344
4345
4346 if (g_traces) then
4347 hr_utility.trace('Compare date = '||to_char(g_key_date_cache(l_pos).min_date,'DD-MON-RR HH24:MI:SS')||
4348 ' - '||
4349 to_char(p_pro_evt_rec.creation_date,'DD-MON-RR HH24:MI:SS'));
4350 hr_utility.trace('Compare N flag to '||g_key_date_cache(l_pos).got_flag);
4351 end if;
4352
4353 -- MAIN TEST
4354 if (p_pro_evt_rec.creation_date = g_key_date_cache(l_pos).max_date
4355 and g_key_date_cache(l_pos).got_flag = 'N') then
4356
4357 -- 3 >>> Now check base table
4358 if (g_dbg) then
4359 hr_utility.set_location(l_proc, 30);
4360 end if;
4361
4362 --Now check base table to see if we have any rows for this surrogate key
4363 -- just need one row
4364 l_statement :=
4365 'SELECT count(*) FROM '||p_dtevent_rec.table_name||
4366 ' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 ';
4367
4368 if (g_dbg) then
4369 hr_utility.trace('-Dynamic SQL: '||l_statement);
4370 end if;
4371 execute immediate l_statement
4372 into l_dummy
4373 using p_pro_evt_rec.surrogate_key; --:1
4374
4375 IF (l_dummy = 0 ) THEN
4376 --Got no rows, so delete is part of PURGE
4377 p_disco := G_DISCO_STANDARD;
4378 --g_key_date_cache(l_pos).got_flag := 'Y'; --make sure we dont get this again
4379
4380 END IF;
4381
4382 end if;
4383
4384
4385 if (g_dbg) then
4386 hr_utility.set_location(l_proc, 900);
4387 end if;
4388 END extra_tests_dbt_p;
4389
4390
4391 /* ----------------------------------------------------------
4392 Procedure: extra_tests_dyt_pkg_df
4393 High Level Summary:
4394 As dynamic trigger package, its immediately obvious when a delete future
4395 (FUTURE_CHANGE or DELETE_NEXT_CHANGE has occurred)
4396 But functional requirement to check this is still valid,
4397 eg dont return if a new row has now been introduced after
4398 --
4399 Detail Logic:
4400 Easy to identify 'FUTURE_CHANGE','DELETE_NEXT_CHANGE'
4401 ...just want to check all future deletes are still applicable, eg no-one
4402 has reintroduced some information for the surrogate key.
4403
4404 ---------------------------------------------------------- */
4405 PROCEDURE extra_tests_dyt_pkg_df
4406 (
4407 p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
4408 p_dtevent_rec IN t_distinct_table_rec ,
4409 p_disco IN OUT NOCOPY number
4410 ) IS
4411 l_statement varchar2(800);
4412 l_dummy number := null;
4413
4414 l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dyt_pkg_df';
4415
4416 BEGIN
4417 if (g_dbg) then
4418 hr_utility.set_location(l_proc, 10);
4419 end if;
4420
4421 -- >>> PHASE 4: Check no future rows
4422 --
4423
4424 IF ( p_pro_evt_rec.event_type = hr_api.g_future_change or
4425 p_pro_evt_rec.event_type = hr_api.g_delete_next_change ) THEN
4426
4427 --Check the base table to see if any future dated records exist,
4428 -- Eg we know we have a FUTURE_CHANGE or a DELETE_NEXT_CHANGE
4429 -- but check it still applies, not been overriden
4430 l_statement :=
4431 'SELECT count(*) FROM '||p_dtevent_rec.table_name||
4432 ' WHERE ' || p_dtevent_rec.surrogate_key_name || ' = :1 ' ||
4433 ' AND '|| p_dtevent_rec.end_date_name || ' >= :2 ';
4434
4435 if (g_dbg) then
4436 hr_utility.trace(l_statement);
4437 end if;
4438 execute immediate l_statement
4439 into l_dummy
4440 using p_pro_evt_rec.surrogate_key, --:1
4441 p_pro_evt_rec.effective_date; --:2
4442
4443 IF (l_dummy >= 1) then
4444 p_disco := G_DISCO_STANDARD;
4445 END IF;
4446
4447 END IF;
4448 if (g_dbg) then
4449 hr_utility.set_location(l_proc, 900);
4450 end if;
4451 END extra_tests_dyt_pkg_df;
4452 --
4453 procedure analyse_disco_process_events
4454 (
4455 p_element_entry_id IN NUMBER ,
4456 p_assignment_id IN NUMBER ,
4457 p_assignment_action_id IN NUMBER ,
4458 p_business_group_id IN NUMBER ,
4459 p_start_date IN DATE ,
4460 p_end_date IN DATE,
4461 p_mode IN VARCHAR2,
4462 p_process IN VARCHAR2,
4463 p_process_mode IN VARCHAR2,
4464 p_range_start IN NUMBER,
4465 p_range_end IN NUMBER,
4466 p_mst_pe_rec IN t_mst_process_event_rec,
4467 p_event_group_id IN NUMBER,
4468 p_distinct_tab IN t_distinct_table,
4469 p_penserv_mode IN VARCHAR2 DEFAULT 'N',
4470 p_date_counter IN OUT NOCOPY NUMBER,
4471 p_global_env IN OUT NOCOPY t_global_env_rec,
4472 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
4473 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
4474 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
4475 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
4476 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type
4477 )
4478 IS
4479 l_look_for_rec t_distinct_table_rec;
4480 l_previous_look_for_rec t_distinct_table_rec; --bug 3598389
4481 l_search varchar2(30);
4482 disco number;
4483 l_all_upd_events_recorded boolean := FALSE; --tested before doing extra upd checks below
4484 BEGIN
4485 --
4486 if (g_traces) then
4487 hr_utility.trace('>> New master candidate PE: '||
4488 p_mst_pe_rec.process_event_id||
4489 ' , indicative of update_id: '||
4490 p_mst_pe_rec.event_update_id||
4491 ' key: '||
4492 p_mst_pe_rec.surrogate_key );
4493 end if;
4494 if (g_dbg) then
4495 hr_utility.trace(' Look within Event group '||
4496 p_event_group_id||
4497 ' with: '||
4498 to_char( to_number(nvl(p_range_end,0))
4499 - to_number(nvl(p_range_start,0)) + 1 )||
4500 ' events');
4501 end if;
4502 --
4503 -- >>> PHASE 3:
4504 --
4505 -- Loop through the table of datetracked events that the user
4506 -- has expressed an interest in. Perform a test on our master
4507 -- candidate row to see if it matches the desired event
4508 --
4509 disco := G_DISCO_NONE;
4510 --
4511 FOR l_tab_loop_counter IN p_range_start..p_range_end
4512 LOOP
4513 -- Look for next event in Event Group
4514 l_look_for_rec := p_distinct_tab(l_tab_loop_counter);
4515 l_search := l_look_for_rec.update_type;
4516
4517 if (g_dbg) then
4518 hr_utility.trace(' + Searching... event '||
4519 l_look_for_rec.datetracked_event_id||
4520 ' an: '||
4521 l_search||
4522 ' on '||
4523 l_look_for_rec.table_name||
4524 '.'||
4525 l_look_for_rec.column_name);
4526 end if;
4527
4528 -- As we are interpreting events in PPE, we must decide the
4529 -- kind of patterns to recognise in this table. These patterns
4530 -- depend on whether the rows were inserted (event captured) by
4531 -- the DYnamic Trigger PacKaGe methodology (eg from api->rhi->hook)
4532 -- or from dyt's as db trigs. In other words, if DBMS_TRIGGERS
4533 -- then we need to check for patterns that represent both API-row
4534 -- level updates and DT library row-level updates, If the table
4535 -- is DYT_PKG mode, then we need to check for patterns representing
4536 -- API-hook updates
4537 --
4538 -- First make sure the process is on the correct table
4539 -- (or our one exception of element entries and element_entry_values_f)
4540 -- if not then dont do any more processing, get next p_mst_pe_rec
4541 --
4542 IF (p_mst_pe_rec.table_name = l_look_for_rec.table_name
4543 or ( p_mst_pe_rec.table_name = 'PAY_ELEMENT_ENTRIES_F'
4544 and l_look_for_rec.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F') )
4545 then
4546
4547
4548 ------------ START fix for BUG 3598389 --------------------------
4549 -- As the events have been ordered by table_name,type
4550 -- we know if the event before this was same table, also "update"
4551 -- then we would have already recorded our current datetracked_event previously
4552 -- thus set flag so no duplicate processing will occur.
4553
4554 l_all_upd_events_recorded := FALSE;
4555 -- Only relevant to Update datetracked_events and not the first one
4556 -- (as first has no prior record)
4557 if (l_tab_loop_counter > p_range_start and l_search = 'U') then
4558 l_previous_look_for_rec := p_distinct_tab(l_tab_loop_counter - 1);
4559 if ( l_previous_look_for_rec.update_type = 'U'
4560 and l_look_for_rec.table_name = l_previous_look_for_rec.table_name) then
4561 l_all_upd_events_recorded := TRUE;
4562 else
4563 l_all_upd_events_recorded := FALSE;
4564 end if;
4565
4566 end if;
4567 ------------ End fix for BUG 3598389 --------------------------
4568
4569 -- a chance we're interested, so do extra work
4570 if (g_dbg) then
4571 hr_utility.trace(
4572 ' Found a candidate PE on same table as our DE: '||
4573 'so call extra logic');
4574 end if;
4575
4576 -- The if /elsif conditions have changed for bg 3488104
4577 -- Now call the code that identifies whether this process_event
4578 -- is indeed indicative of the event we are looking for. This
4579 -- comparing code is dependent on whether the pe_rec was created
4580 -- as part of a dbt dyn trig or a dyt_pkg dyn trigger.
4581 --
4582 -- Dynamic Triggers as dbt_trig are event_type 'I, 'U', 'D'
4583 -- Dynamic Triggers as dyt_pkg are all other event_types
4584 --
4585 IF ( l_search = 'I' and p_mst_pe_rec.event_type = 'I') then
4586 extra_tests_i(p_mst_pe_rec,l_look_for_rec,disco);
4587 ELSIF (l_search = 'I' and
4588 p_mst_pe_rec.event_type = hr_api.g_insert) then
4589 extra_tests_i(p_mst_pe_rec,l_look_for_rec,disco);
4590 --
4591 ELSIF (l_search = 'U' and p_mst_pe_rec.event_type = 'U'
4592 and l_all_upd_events_recorded <> TRUE) then
4593 extra_tests_dbt_u_e(
4594 p_element_entry_id => p_element_entry_id,
4595 p_assignment_action_id => p_assignment_action_id,
4596 p_business_group_id => p_business_group_id,
4597 p_assignment_id => p_assignment_id,
4598 p_process_mode => p_process_mode,
4599 p_event_group_id => null,
4600 p_start_date => p_start_date,
4601 p_end_date => p_end_date,
4602 p_penserv_mode => p_penserv_mode,
4603 p_date_counter => p_date_counter, --in/out
4604 p_global_env => p_global_env,
4605 t_dynamic_sql => t_dynamic_sql, --in/out
4606 t_proration_dates_temp => t_proration_dates_temp, --in/out
4607 t_proration_change_type => t_proration_change_type, --in/out
4608 t_proration_type => t_proration_type, --in/out
4609 t_detailed_output => t_detailed_output, --in/out
4610 p_pro_evt_rec => p_mst_pe_rec , --record from
4611 -- master query
4612 p_dtevent_rec => l_look_for_rec ,
4613 p_disco => disco --in/out
4614 );
4615
4616
4617 ELSIF (l_search = 'U' and
4618 p_mst_pe_rec.event_type not in ('I','U','D')
4619 and l_all_upd_events_recorded <> TRUE) then
4620 extra_tests_dyt_pkg_u(
4621 p_element_entry_id => p_element_entry_id,
4622 p_assignment_action_id => p_assignment_action_id,
4623 p_business_group_id => p_business_group_id,
4624 p_assignment_id => p_assignment_id,
4625 p_process_mode => p_process_mode,
4626 p_event_group_id => null,
4627 p_start_date => p_start_date,
4628 p_end_date => p_end_date,
4629 p_date_counter => p_date_counter, --in/out
4630 p_global_env => p_global_env,
4631 t_dynamic_sql => t_dynamic_sql, --in/out
4632 t_proration_dates_temp => t_proration_dates_temp, --in/out
4633 t_proration_change_type => t_proration_change_type, --in/out
4634 t_proration_type => t_proration_type, --in/out
4635 t_detailed_output => t_detailed_output, --in/out
4636 p_pro_evt_rec => p_mst_pe_rec , --record from
4637 -- master query
4638 p_dtevent_rec => l_look_for_rec ,
4639 p_disco => disco --in/out
4640 );
4641
4642 ELSIF (l_search = 'C' and p_mst_pe_rec.event_type = 'U') then
4643 -- Simple test for Database Trigger styled Correction
4644 if (p_mst_pe_rec.updated_column_name = l_look_for_rec.column_name)
4645 then
4646 disco := G_DISCO_STANDARD;
4647 end if;
4648
4649 ELSIF (l_search = 'C' and
4650 p_mst_pe_rec.event_type not in ('I','U','D')) then
4651 --
4652 -- Simple test for Dynamic Package styled Correction
4653 if (p_mst_pe_rec.updated_column_name = l_look_for_rec.column_name
4654 and p_mst_pe_rec.event_type = hr_api.g_correction) then
4655 disco := G_DISCO_STANDARD;
4656 end if;
4657
4658 ELSIF (l_search = 'P' and p_mst_pe_rec.event_type = 'D') then
4659 extra_tests_dbt_p(p_mst_pe_rec,l_look_for_rec,disco);
4660 ELSIF (l_search = 'P' and
4661 p_mst_pe_rec.event_type = hr_api.g_zap ) then
4662 -- Simple test already performed for Dynamic Package styled Purge
4663 disco := G_DISCO_STANDARD;
4664
4665 ELSIF (l_search = 'E' and
4666 p_mst_pe_rec.event_type in ('I','U','D')) then
4667 extra_tests_dbt_u_e(
4668 p_element_entry_id => p_element_entry_id,
4669 p_assignment_action_id => p_assignment_action_id,
4670 p_business_group_id => p_business_group_id,
4671 p_assignment_id => p_assignment_id,
4672 p_process_mode => p_process_mode,
4673 p_event_group_id => null,
4674 p_start_date => p_start_date,
4675 p_end_date => p_end_date,
4676 p_penserv_mode => p_penserv_mode,
4677 p_date_counter => p_date_counter, --in/out
4678 p_global_env => p_global_env,
4679 t_dynamic_sql => t_dynamic_sql, --in/out
4680 t_proration_dates_temp => t_proration_dates_temp, --in/out
4681 t_proration_change_type => t_proration_change_type, --in/out
4682 t_proration_type => t_proration_type, --in/out
4683 t_detailed_output => t_detailed_output, --in/out
4684 p_pro_evt_rec => p_mst_pe_rec , --record from
4685 -- master query
4686 p_dtevent_rec => l_look_for_rec ,
4687 p_disco => disco --in/out
4688 );
4689
4690 ELSIF (l_search = 'E' and
4691 p_mst_pe_rec.event_type not in ('I','U','D')) then
4692 extra_tests_dyt_pkg_e(p_mst_pe_rec,l_look_for_rec,disco);
4693
4694 ELSIF (l_search = 'DF'
4695 and p_mst_pe_rec.event_type in ('I','U','D')
4696 ) then
4697
4698 disco := G_DISCO_DF; -- This test calls save directly so set here...
4699 extra_tests_dbt_df(
4700 p_element_entry_id => p_element_entry_id,
4701 p_assignment_action_id => p_assignment_action_id,
4702 p_business_group_id => p_business_group_id,
4703 p_assignment_id => p_assignment_id,
4704 p_mode => p_mode,
4705 p_process => p_process,
4706 p_process_mode => p_process_mode,
4707 p_event_group_id => p_event_group_id,
4708 p_start_date => p_start_date,
4709 p_end_date => p_end_date,
4710 p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
4711 p_date_counter => p_date_counter, --in/out
4712 p_global_env => p_global_env,
4713 t_dynamic_sql => t_dynamic_sql, --in/out
4714 t_proration_dates_temp => t_proration_dates_temp, --in/out
4715 t_proration_change_type => t_proration_change_type, --in/out
4716 t_proration_type => t_proration_type, --in/out
4717 t_detailed_output => t_detailed_output, --in/out
4718 p_pro_evt_rec => p_mst_pe_rec , -- record from
4719 -- master query
4720 p_dtevent_rec => l_look_for_rec ,
4721 p_disco => disco --in/out
4722 );
4723
4724 disco := G_DISCO_NONE; -- ... and unset here
4725 ELSIF (l_search = 'DF' and
4726 p_mst_pe_rec.event_type not in ('I','U','D')) then
4727 extra_tests_dyt_pkg_df(p_mst_pe_rec,l_look_for_rec,disco);
4728
4729 END IF;
4730
4731
4732 If (disco <> G_DISCO_NONE) then
4733 --Add found event to store
4734
4735 save_disco_details (
4736 p_effective_date => p_mst_pe_rec.effective_date,
4737 p_creation_date => p_mst_pe_rec.creation_date,
4738 p_update_type => l_search,
4739 p_change_mode => p_mst_pe_rec.change_mode,
4740 p_process_mode => p_process_mode,
4741 p_proration_type => l_look_for_rec.proration_type,
4742 p_datetracked_event => l_look_for_rec.datetracked_event_id,
4743 p_column_name => p_mst_pe_rec.updated_column_name,
4744 p_change_values => p_mst_pe_rec.change_values,
4745 p_element_entry_id => p_element_entry_id,
4746 p_surrogate_key => p_mst_pe_rec.surrogate_key,
4747 p_dated_table_id => l_look_for_rec.table_id,
4748 p_table_name => l_look_for_rec.table_name,
4749 p_disco => disco,
4750 p_start_date => p_start_date,
4751 p_end_date => p_end_date,
4752 p_assignment_action_id => p_assignment_action_id,
4753 p_business_group_id => p_business_group_id,
4754 p_assignment_id => p_assignment_id,
4755 p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
4756 p_date_counter => p_date_counter,
4757 p_global_env => p_global_env,
4758 t_proration_dates_temp => t_proration_dates_temp,
4759 t_proration_change_type => t_proration_change_type,
4760 t_proration_type => t_proration_type,
4761 t_detailed_output => t_detailed_output
4762 );
4763 disco := G_DISCO_NONE;
4764
4765 end if;
4766
4767 END IF; --PE not on same table as DE, so get next DE
4768
4769 END LOOP; -- Get next datetracked event in cache and compare
4770 -- this event with that
4771 --
4772 end analyse_disco_process_events;
4773 --
4774 --
4775 -- Name: valid_group_event_for_asg
4776 -- Description : This function is used by the group level
4777 -- cursors. It tries to reduce the group
4778 -- level work needed by performing
4779 -- simple group level restrictions
4780 --
4781 function valid_group_event_for_asg(p_table_name in varchar2,
4782 p_assignment_id in number,
4783 p_surrogate_key in varchar2)
4784 return varchar2
4785 is
4786 --
4787 cursor validate_grade(p_assignment_id in number
4788 )
4789 is
4790 select /*+ USE_NL(pgr paf)*/
4791 pgr.grade_rule_id
4792 from pay_grade_rules_f pgr,
4793 per_all_assignments_f paf
4794 where paf.assignment_id = p_assignment_id
4795 and paf.grade_id = pgr.grade_or_spinal_point_id
4796 union all
4797 select /*+ ORDERED USE_NL(pgr psp psps pspp)*/
4798 pgr.grade_rule_id
4799 from per_spinal_point_placements_f pspp,
4800 per_spinal_point_steps_f psps,
4801 per_spinal_points psp,
4802 pay_grade_rules_f pgr
4803 where psp.spinal_point_id = pgr.grade_or_spinal_point_id
4804 and psp.spinal_point_id = psps.spinal_point_id
4805 and p_assignment_id = pspp.assignment_id
4806 and pspp.step_id = psps.step_id;
4807 --
4808 cursor validate_rate_by_criteria(p_assignment_id in number,
4809 p_surrogate_key in number)
4810 is
4811 select '' chk
4812 from dual
4813 where exists (
4814 select '' chk
4815 from pay_element_entries_f pee
4816 ,pqh_criteria_rate_elements pcre
4817 ,pqh_rate_matrix_rates_f prmr
4818 where pee.assignment_id=p_assignment_id
4819 and pcre.element_type_id=pee.element_type_id
4820 and pcre.criteria_rate_defn_id=prmr.criteria_rate_defn_id
4821 and prmr.rate_matrix_rate_id=p_surrogate_key)
4822 or exists (
4823 select '' chk
4824 from pay_element_entries_f pee
4825 ,pqh_criteria_rate_elements pcre
4826 ,pqh_criteria_rate_factors pcrf
4827 ,pqh_rate_matrix_rates_f prmr
4828 where pee.assignment_id=p_assignment_id
4829 and pcre.element_type_id=pee.element_type_id
4830 and pcre.criteria_rate_defn_id = pcrf.criteria_rate_defn_id
4831 and pcrf.parent_criteria_rate_defn_id = prmr.criteria_rate_defn_id
4832 and prmr.rate_matrix_rate_id = p_surrogate_key);
4833
4834
4835 l_valid_event varchar2(5);
4836 begin
4837 --
4838 l_valid_event := 'Y';
4839 --
4840 if (p_table_name = 'PAY_GRADE_RULES_F') then
4841 --
4842 if (g_grd_assignment_id <> p_assignment_id) then
4843 --
4844 g_grade_list.delete();
4845 g_grd_assignment_id := p_assignment_id;
4846 --
4847 for grrec in validate_grade(p_assignment_id) loop
4848 g_grade_list(grrec.grade_rule_id) := grrec.grade_rule_id;
4849 end loop;
4850 --
4851 end if;
4852 --
4853 l_valid_event := 'N';
4854 --
4855 if (g_grade_list.exists(p_surrogate_key)) then
4856 --
4857 l_valid_event := 'Y';
4858 --
4859 end if;
4860 --
4861 elsif (p_table_name = 'PQH_RATE_MATRIX_RATES_F') then
4862
4863 l_valid_event := 'N';
4864
4865 for grrec in validate_rate_by_criteria(p_assignment_id,p_surrogate_key) loop
4866 --
4867 l_valid_event := 'Y';
4868 --
4869 end loop;
4870 --
4871 elsif (p_table_name = 'FF_GLOBALS_F') then
4872 --
4873 l_valid_event := pay_group_event_pkg.ff_global_check(p_assignment_id,p_surrogate_key);
4874 --
4875 elsif (p_table_name = 'PAY_USER_COLUMN_INSTANCES_F') then
4876 --
4877 l_valid_event := pay_group_event_pkg.pay_user_table_check(p_assignment_id,p_surrogate_key);
4878 --
4879 end if;
4880 --
4881 return l_valid_event;
4882 --
4883 end valid_group_event_for_asg;
4884 --
4885 --
4886 ----------------------------
4887
4888 -- P_d(atetracked)ev(ents) info, eg stuff to look for
4889
4890 -- This procedure called for each table within the event group
4891 PROCEDURE record_all_disco_events
4892 (
4893 p_event_group_id IN NUMBER ,
4894
4895 p_element_entry_id IN NUMBER ,
4896 p_assignment_id IN NUMBER ,
4897 p_assignment_action_id IN NUMBER ,
4898 p_business_group_id IN NUMBER ,
4899 p_start_date IN DATE ,
4900 p_end_date IN DATE,
4901 p_process IN VARCHAR2,
4902 p_mode IN VARCHAR2,
4903 p_process_mode IN VARCHAR2,
4904 p_global_env IN OUT NOCOPY t_global_env_rec,
4905 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
4906 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
4907 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
4908 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
4909 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
4910 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
4911 ) AS
4912 --
4913 -- Setup the types
4914 --
4915 type t_column_name is table of pay_event_updates.column_name%type
4916 index by binary_integer;
4917 type t_event_type is table of pay_event_updates.event_type%type
4918 index by binary_integer;
4919 type t_event_update_id is table of pay_event_updates.event_update_id%type
4920 index by binary_integer;
4921 type t_effective_date is table of pay_process_events.effective_date%type
4922 index by binary_integer;
4923 type t_assignment_id is table of pay_process_events.assignment_id%type
4924 index by binary_integer;
4925 type t_surrogate_key is table of pay_process_events.surrogate_key%type
4926 index by binary_integer;
4927 type t_process_event_id is table of pay_process_events.process_event_id%type
4928 index by binary_integer;
4929 type t_description is table of pay_process_events.description%type
4930 index by binary_integer;
4931 type t_calculation_date is table of pay_process_events.calculation_date%type
4932 index by binary_integer;
4933 type t_creation_date is table of pay_process_events.creation_date%type
4934 index by binary_integer;
4935 type t_change_type is table of pay_process_events.change_type%type
4936 index by binary_integer;
4937 type t_table_name is table of pay_dated_tables.table_name%type
4938 index by binary_integer;
4939 --
4940 l_column_name t_column_name;
4941 l_event_type t_event_type;
4942 l_event_update_id t_event_update_id;
4943 l_effective_date t_effective_date;
4944 l_assignment_id t_assignment_id;
4945 l_surrogate_key t_surrogate_key;
4946 l_process_event_id t_process_event_id;
4947 l_description t_description;
4948 l_calculation_date t_calculation_date;
4949 l_creation_date t_creation_date;
4950 l_change_type t_change_type;
4951 l_table_name t_table_name;
4952 --
4953 -- NB Following statement has been tuned for performance purposes
4954 CURSOR csr_all_process_events_cre (
4955 cp_bulk_processing IN VARCHAR,
4956 cp_cstart_date IN DATE ,
4957 cp_cend_date IN DATE ) IS
4958
4959 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PPE) */
4960 peu.column_name updated_column_name ,
4961 peu.event_type event_type ,
4962 peu.event_update_id event_update_id ,
4963 ppe.effective_date effective_date ,
4964 ppe.assignment_id assignment_id ,
4965 ppe.surrogate_key surrogate_key ,
4966 ppe.process_event_id process_event_id,
4967 ppe.description change_values,
4968 ppe.calculation_date calculation_date,
4969 ppe.creation_date creation_date,
4970 ppe.change_type change_mode,
4971 pdt.table_name table_name
4972 FROM
4973 pay_dated_tables pdt ,
4974 pay_process_events ppe ,
4975 pay_event_updates peu
4976 WHERE
4977 peu.event_update_id = ppe.event_update_id + 0
4978 AND peu.dated_table_id = pdt.dated_table_id
4979 AND pdt.dated_table_id IN
4980 ( select distinct pde2.dated_table_id table_id
4981 from pay_datetracked_events pde2
4982 where pde2.event_group_id = p_event_group_id
4983 and cp_bulk_processing = 'N'
4984 union all
4985 select distinct pdt2.dated_table_id
4986 from pay_dated_tables pdt2
4987 where cp_bulk_processing = 'Y'
4988 )
4989 AND ppe.assignment_id is not null
4990 AND ppe.assignment_id = p_assignment_id
4991 AND ppe.business_group_id = p_business_group_id
4992 AND ( peu.business_group_id = ppe.business_group_id
4993 or peu.legislation_code = g_leg_code
4994 or ( peu.business_group_id is null
4995 and peu.legislation_code is null) )
4996 AND ppe.change_type = nvl(p_mode,ppe.change_type)
4997 AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
4998 AND ( (cp_bulk_processing = 'Y')
4999 or ( cp_bulk_processing = 'N'
5000 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5001 or
5002 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5003 and ppe.surrogate_key=p_element_entry_id )
5004 )
5005 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5006 or
5007 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5008 and exists
5009 ( select null
5010 from pay_element_entry_values_f
5011 where element_entry_id = p_element_entry_id
5012 and element_entry_value_id =
5013 ppe.surrogate_key ) )
5014 )
5015 )
5016 )
5017 UNION ALL
5018 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PDT) */
5019 peu.column_name updated_column_name ,
5020 peu.event_type event_type ,
5021 peu.event_update_id event_update_id ,
5022 ppe.effective_date effective_date ,
5023 ppe.assignment_id assignment_id ,
5024 ppe.surrogate_key surrogate_key ,
5025 ppe.process_event_id process_event_id,
5026 ppe.description change_values,
5027 ppe.calculation_date calculation_date,
5028 ppe.creation_date creation_date,
5029 ppe.change_type change_mode,
5030 pdt.table_name table_name
5031 FROM
5032 pay_dated_tables pdt ,
5033 pay_event_updates peu ,
5034 pay_process_events ppe
5035 WHERE
5036 peu.event_update_id = ppe.event_update_id + 0
5037 AND peu.dated_table_id = pdt.dated_table_id
5038 AND pdt.dated_table_id IN
5039 ( select distinct pde2.dated_table_id table_id
5040 from pay_datetracked_events pde2
5041 where pde2.event_group_id = p_event_group_id
5042 and cp_bulk_processing = 'N'
5043 union all
5044 select distinct pdt2.dated_table_id
5045 from pay_dated_tables pdt2
5046 where cp_bulk_processing = 'Y'
5047 )
5048 AND ppe.assignment_id is null
5049 AND ppe.business_group_id = p_business_group_id
5050 AND ( peu.business_group_id = ppe.business_group_id
5051 or peu.legislation_code = g_leg_code
5052 or ( peu.business_group_id is null
5053 and peu.legislation_code is null) )
5054 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5055 AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
5056 AND ( (cp_bulk_processing = 'Y')
5057 or ( cp_bulk_processing = 'N'
5058 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5059 or
5060 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5061 and ppe.surrogate_key=p_element_entry_id )
5062 )
5063 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5064 or
5065 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5066 and exists
5067 ( select null
5068 from pay_element_entry_values_f
5069 where element_entry_id = p_element_entry_id
5070 and element_entry_value_id =
5071 ppe.surrogate_key ) )
5072 )
5073 )
5074 )
5075 AND pay_interpreter_pkg.valid_group_event_for_asg
5076 (pdt.table_name,
5077 p_assignment_id,
5078 ppe.surrogate_key) = 'Y'
5079 ORDER BY 11, 6, 5, 4;
5080
5081
5082 -- NB Following statement has been tuned for performance purposes
5083 CURSOR csr_all_process_events_eff(
5084 cp_bulk_processing IN VARCHAR2,
5085 cp_estart_date IN DATE ,
5086 cp_eend_date IN DATE ) IS
5087
5088 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PPE) */
5089 peu.column_name updated_column_name ,
5090 peu.event_type event_type ,
5091 peu.event_update_id event_update_id ,
5092 ppe.effective_date effective_date ,
5093 ppe.assignment_id assignment_id ,
5094 ppe.surrogate_key surrogate_key ,
5095 ppe.process_event_id process_event_id,
5096 ppe.description change_values,
5097 ppe.calculation_date calculation_date,
5098 ppe.creation_date creation_date,
5099 ppe.change_type change_mode,
5100 pdt.table_name table_name
5101 FROM
5102 pay_dated_tables pdt ,
5103 pay_process_events ppe ,
5104 pay_event_updates peu
5105 WHERE
5106 peu.event_update_id = ppe.event_update_id + 0
5107 AND peu.dated_table_id = pdt.dated_table_id
5108 AND pdt.dated_table_id IN
5109 ( select distinct pde2.dated_table_id table_id
5110 from pay_datetracked_events pde2
5111 where pde2.event_group_id = p_event_group_id
5112 and cp_bulk_processing = 'N'
5113 union all
5114 select distinct pdt2.dated_table_id
5115 from pay_dated_tables pdt2
5116 where cp_bulk_processing = 'Y'
5117 )
5118 AND ppe.assignment_id is not null
5119 AND ppe.assignment_id = p_assignment_id
5120 AND ppe.business_group_id = p_business_group_id
5121 AND ( peu.business_group_id = ppe.business_group_id
5122 or peu.legislation_code = g_leg_code
5123 or ( peu.business_group_id is null
5124 and peu.legislation_code is null) )
5125 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5126 or ppe.retroactive_status is null)
5127 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5128 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5129 AND ( (cp_bulk_processing = 'Y')
5130 or ( cp_bulk_processing = 'N'
5131 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5132 or
5133 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5134 and ppe.surrogate_key=p_element_entry_id )
5135 )
5136 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5137 or
5138 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5139 and exists
5140 ( select null
5141 from pay_element_entry_values_f
5142 where element_entry_id = p_element_entry_id
5143 and element_entry_value_id =
5144 ppe.surrogate_key ) )
5145 )
5146 )
5147 )
5148 UNION ALL
5149 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PDT) */
5150 peu.column_name updated_column_name ,
5151 peu.event_type event_type ,
5152 peu.event_update_id event_update_id ,
5153 ppe.effective_date effective_date ,
5154 ppe.assignment_id assignment_id ,
5155 ppe.surrogate_key surrogate_key ,
5156 ppe.process_event_id process_event_id,
5157 ppe.description change_values,
5158 ppe.calculation_date calculation_date,
5159 ppe.creation_date creation_date,
5160 ppe.change_type change_mode,
5161 pdt.table_name table_name
5162 FROM
5163 pay_dated_tables pdt ,
5164 pay_event_updates peu ,
5165 pay_process_events ppe
5166 WHERE
5167 peu.event_update_id = ppe.event_update_id + 0
5168 AND peu.dated_table_id = pdt.dated_table_id
5169 AND pdt.dated_table_id IN
5170 ( select distinct pde2.dated_table_id table_id
5171 from pay_datetracked_events pde2
5172 where pde2.event_group_id = p_event_group_id
5173 and cp_bulk_processing = 'N'
5174 union all
5175 select distinct pdt2.dated_table_id
5176 from pay_dated_tables pdt2
5177 where cp_bulk_processing = 'Y'
5178 )
5179 AND ppe.assignment_id is null
5180 AND ppe.business_group_id = p_business_group_id
5181 AND ( peu.business_group_id = ppe.business_group_id
5182 or peu.legislation_code = g_leg_code
5183 or ( peu.business_group_id is null
5184 and peu.legislation_code is null) )
5185 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5186 or ppe.retroactive_status is null)
5187 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5188 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5189 AND ( (cp_bulk_processing = 'Y')
5190 or ( cp_bulk_processing = 'N'
5191 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5192 or
5193 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5194 and ppe.surrogate_key=p_element_entry_id )
5195 )
5196 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5197 or
5198 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5199 and exists
5200 ( select null
5201 from pay_element_entry_values_f
5202 where element_entry_id = p_element_entry_id
5203 and element_entry_value_id =
5204 ppe.surrogate_key ) )
5205 )
5206 )
5207 )
5208 AND pay_interpreter_pkg.valid_group_event_for_asg
5209 (pdt.table_name,
5210 p_assignment_id,
5211 ppe.surrogate_key) = 'Y'
5212 ORDER BY 11, 6, 5, 4;
5213 --ORDER BY pdt.table_name, ppe.surrogate_key, ppe.assignment_id, ppe.effective_date;
5214
5215 -- The above cursor is modified for penserver extract
5216 CURSOR csr_all_process_events_eff_pen(
5217 cp_bulk_processing IN VARCHAR2,
5218 cp_estart_date IN DATE ,
5219 cp_eend_date IN DATE ) IS
5220
5221 SELECT
5222 peu.column_name updated_column_name ,
5223 peu.event_type event_type ,
5224 peu.event_update_id event_update_id ,
5225 ppe.effective_date effective_date ,
5226 ppe.assignment_id assignment_id ,
5227 ppe.surrogate_key surrogate_key ,
5228 ppe.process_event_id process_event_id,
5229 ppe.description change_values,
5230 ppe.calculation_date calculation_date,
5231 ppe.creation_date creation_date,
5232 ppe.change_type change_mode,
5233 pdt.table_name table_name
5234 FROM
5235 pay_dated_tables pdt ,
5236 pay_process_events ppe ,
5237 pay_event_updates peu
5238 WHERE
5239 peu.event_update_id = ppe.event_update_id + 0
5240 AND peu.dated_table_id = pdt.dated_table_id
5241 AND pdt.dated_table_id IN
5242 ( select distinct pde2.dated_table_id table_id
5243 from pay_datetracked_events pde2
5244 where pde2.event_group_id = p_event_group_id
5245 and cp_bulk_processing = 'N'
5246 union all
5247 select distinct pdt2.dated_table_id
5248 from pay_dated_tables pdt2
5249 where cp_bulk_processing = 'Y'
5250 )
5251 AND ppe.assignment_id is not null
5252 AND ppe.assignment_id = p_assignment_id
5253 AND ppe.business_group_id = p_business_group_id
5254 AND ( peu.business_group_id = ppe.business_group_id
5255 or peu.legislation_code = g_leg_code
5256 or ( peu.business_group_id is null
5257 and peu.legislation_code is null) )
5258 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5259 or ppe.retroactive_status is null)
5260 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5261 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5262 AND ( (cp_bulk_processing = 'Y')
5263 or ( cp_bulk_processing = 'N'
5264 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5265 or
5266 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5267 and ppe.surrogate_key=p_element_entry_id )
5268 )
5269 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5270 or
5271 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5272 and exists
5273 ( select null
5274 from pay_element_entry_values_f
5275 where element_entry_id = p_element_entry_id
5276 and element_entry_value_id =
5277 ppe.surrogate_key ) )
5278 )
5279 )
5280 )
5281 AND pay_interpreter_pkg.valid_group_event_for_asg
5282 (pdt.table_name,
5283 p_assignment_id,
5284 ppe.surrogate_key) = 'Y'
5285 ORDER BY 11, 6, 5, 4;
5286 --ORDER BY pdt.table_name, ppe.surrogate_key, ppe.assignment_id, ppe.effective_date;
5287
5288 l_mst_pm VARCHAR2(30);
5289 l_proc VARCHAR2(80) := g_pkg||'.record_all_disco_events';
5290
5291
5292 --New holders
5293 l_date_counter NUMBER;
5294
5295 l_bulk_processing varchar2(5);
5296
5297 l_mst_pe_rec t_mst_process_event_rec;
5298
5299 --bug 7443747:Start
5300 -- New cursor for extracts that need to track just REPORTS type of events
5301 CURSOR csr_all_proces_eve_eff_pen_rep(
5302 cp_bulk_processing IN VARCHAR2,
5303 cp_estart_date IN DATE ,
5304 cp_eend_date IN DATE )
5305 IS
5306 SELECT
5307 peu.column_name updated_column_name ,
5308 peu.event_type event_type ,
5309 peu.event_update_id event_update_id ,
5310 ppe.effective_date effective_date ,
5311 ppe.assignment_id assignment_id ,
5312 ppe.surrogate_key surrogate_key ,
5313 ppe.process_event_id process_event_id,
5314 ppe.description change_values,
5315 ppe.calculation_date calculation_date,
5316 ppe.creation_date creation_date,
5317 ppe.change_type change_mode,
5318 pdt.table_name table_name
5319 FROM
5320 pay_dated_tables pdt ,
5321 pay_process_events ppe ,
5322 pay_event_updates peu
5323 WHERE
5324 peu.event_update_id = ppe.event_update_id + 0
5325 AND peu.dated_table_id = pdt.dated_table_id
5326 --Added new condition to capture REPORTS events only
5327 AND ppe.change_type = 'REPORTS'
5328 AND pdt.dated_table_id IN
5329 ( select distinct pde2.dated_table_id table_id
5330 from pay_datetracked_events pde2
5331 where pde2.event_group_id = p_event_group_id
5332 and cp_bulk_processing = 'N'
5333 union all
5334 select distinct pdt2.dated_table_id
5335 from pay_dated_tables pdt2
5336 where cp_bulk_processing = 'Y'
5337 )
5338 AND ppe.assignment_id is not null
5339 AND ppe.assignment_id = p_assignment_id
5340 AND ppe.business_group_id = p_business_group_id
5341 AND ( peu.business_group_id = ppe.business_group_id
5342 or peu.legislation_code = g_leg_code
5343 or ( peu.business_group_id is null
5344 and peu.legislation_code is null) )
5345 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5346 or ppe.retroactive_status is null)
5347 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5348 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5349 AND ( (cp_bulk_processing = 'Y')
5350 or ( cp_bulk_processing = 'N'
5351 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5352 or
5353 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5354 and ppe.surrogate_key=p_element_entry_id )
5355 )
5356 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5357 or
5358 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5359 and exists
5360 ( select null
5361 from pay_element_entry_values_f
5362 where element_entry_id = p_element_entry_id
5363 and element_entry_value_id =
5364 ppe.surrogate_key ) )
5365 )
5366 )
5367 )
5368 AND pay_interpreter_pkg.valid_group_event_for_asg
5369 (pdt.table_name,
5370 p_assignment_id,
5371 ppe.surrogate_key) = 'Y'
5372 --Modified order by to include actual date
5373 ORDER BY 11, 5, 4, 10 desc;
5374
5375
5376 CURSOR csr_get_pen_reports_exts(c_ext_dfn_id IN NUMBER)
5377 IS
5378 SELECT 'x'
5379 FROM BEN_EXT_DFN
5380 WHERE ext_dfn_id = c_ext_dfn_id
5381 AND name in ('PQP GB PenServer Periodic Changes Interface - Allowance History',
5382 'PQP GB PenServer Periodic Changes Interface - Bonus History')
5383 AND legislation_code ='GB';
5384
5385 l_pen_ext_exists VARCHAR2(5);
5386 --bug 7443747:Stop
5387
5388 BEGIN
5389 hr_utility.set_location(l_proc, 10);
5390 l_date_counter := 1;
5391
5392 -- As part of looking for a dbt_i and dbt_p we create a cache
5393 -- This is a new call to the Interpreter so we destroy this cache
5394 g_key_date_cache.delete;
5395 g_upd_cache.delete; --bug 3598389
5396
5397 -- set up bus grp and leg code cache
5398 if (g_bus_grp_id is null or
5399 (g_bus_grp_id is not null and
5400 g_bus_grp_id <> p_business_group_id)) then
5401 select legislation_code
5402 into g_leg_code
5403 from per_business_groups_perf
5404 where business_group_id = p_business_group_id;
5405 --
5406 g_bus_grp_id := p_business_group_id;
5407 end if;
5408
5409 l_bulk_processing := 'N';
5410 if(p_global_env.datetrack_ee_tab_use) then
5411 l_bulk_processing := 'Y';
5412 end if;
5413
5414 -- >>> PHASE 1: Split copy of code in to two cursors based on
5415 -- master process mode to avoid performance issues.
5416 l_mst_pm := get_master_process_mode(p_process_mode);
5417
5418 -- NB. More notes in get_master_process_mode, but summary
5419 -- CRE = tune for mode, creation date
5420 -- EFF = tune for process, mode and effective date
5421 if(g_traces) then
5422 hr_utility.trace('MASTER PROCESS MODE '||l_mst_pm);
5423 hr_utility.trace('p_penserv_mode '||p_penserv_mode);
5424 end if;
5425 -- >>> PHASE 2: Get candidate rows from PPE
5426 --
5427 IF (l_mst_pm = 'CRE') THEN
5428 hr_utility.set_location(l_proc,20);
5429 --
5430 l_column_name.delete;
5431 l_event_type.delete;
5432 l_event_update_id.delete;
5433 l_effective_date.delete;
5434 l_assignment_id.delete;
5435 l_surrogate_key.delete;
5436 l_process_event_id.delete;
5437 l_description.delete;
5438 l_calculation_date.delete;
5439 l_creation_date.delete;
5440 l_change_type.delete;
5441 l_table_name.delete;
5442 --
5443 open csr_all_process_events_cre(
5444 l_bulk_processing,
5445 p_start_date ,
5446 p_end_date
5447 );
5448 --
5449 fetch csr_all_process_events_cre bulk collect into
5450 l_column_name,
5451 l_event_type,
5452 l_event_update_id,
5453 l_effective_date,
5454 l_assignment_id,
5455 l_surrogate_key,
5456 l_process_event_id,
5457 l_description,
5458 l_calculation_date,
5459 l_creation_date,
5460 l_change_type,
5461 l_table_name;
5462 --
5463 for i in 1..l_process_event_id.count loop
5464 --
5465 l_mst_pe_rec.updated_column_name := l_column_name(i);
5466 l_mst_pe_rec.event_type := l_event_type(i);
5467 l_mst_pe_rec.event_update_id := l_event_update_id(i);
5468 l_mst_pe_rec.effective_date := l_effective_date(i);
5469 l_mst_pe_rec.assignment_id := l_assignment_id(i);
5470 l_mst_pe_rec.surrogate_key := l_surrogate_key(i);
5471 l_mst_pe_rec.process_event_id := l_process_event_id(i);
5472 l_mst_pe_rec.change_values := l_description(i);
5473 l_mst_pe_rec.calculation_date := l_calculation_date(i);
5474 l_mst_pe_rec.creation_date := l_creation_date(i);
5475 l_mst_pe_rec.change_mode := l_change_type(i);
5476 l_mst_pe_rec.table_name := l_table_name(i);
5477 --
5478 analyse_disco_process_events
5479 (
5480 p_element_entry_id => p_element_entry_id,
5481 p_assignment_id => p_assignment_id,
5482 p_assignment_action_id => p_assignment_action_id,
5483 p_business_group_id => p_business_group_id,
5484 p_start_date => p_start_date,
5485 p_end_date => p_end_date,
5486 p_mode => p_mode,
5487 p_process => p_process,
5488 p_process_mode => p_process_mode,
5489 p_range_start => p_global_env.monitor_start_ptr,
5490 p_range_end => p_global_env.monitor_end_ptr,
5491 p_mst_pe_rec => l_mst_pe_rec,
5492 p_event_group_id => p_event_group_id,
5493 p_distinct_tab => glo_monitored_events,
5494 p_penserv_mode => p_penserv_mode,
5495 p_date_counter => l_date_counter,
5496 p_global_env => p_global_env,
5497 t_dynamic_sql => t_dynamic_sql,
5498 t_proration_dates_temp => t_proration_dates_temp,
5499 t_proration_change_type => t_proration_change_type,
5500 t_proration_type => t_proration_type,
5501 t_detailed_output => t_detailed_output
5502 );
5503
5504 end loop; --Get next process event to do comparisons on
5505 close csr_all_process_events_cre;
5506
5507
5508 ELSIF (l_mst_pm = 'EFF') THEN
5509 --
5510 hr_utility.set_location(l_proc,320);
5511 --
5512 l_column_name.delete;
5513 l_event_type.delete;
5514 l_event_update_id.delete;
5515 l_effective_date.delete;
5516 l_assignment_id.delete;
5517 l_surrogate_key.delete;
5518 l_process_event_id.delete;
5519 l_description.delete;
5520 l_calculation_date.delete;
5521 l_creation_date.delete;
5522 l_change_type.delete;
5523 l_table_name.delete;
5524 --
5525 if (p_penserv_mode = 'N') then
5526 open csr_all_process_events_eff(
5527 l_bulk_processing,
5528 p_start_date ,
5529 p_end_date
5530 );
5531 --
5532 fetch csr_all_process_events_eff bulk collect into
5533 l_column_name,
5534 l_event_type,
5535 l_event_update_id,
5536 l_effective_date,
5537 l_assignment_id,
5538 l_surrogate_key,
5539 l_process_event_id,
5540 l_description,
5541 l_calculation_date,
5542 l_creation_date,
5543 l_change_type,
5544 l_table_name;
5545 --
5546 else
5547 --bug 7443747:Start
5548 hr_utility.trace('ben_ext_thread.g_ext_dfn_id '||ben_ext_thread.g_ext_dfn_id);
5549 hr_utility.trace('g_pen_collect_reports '||g_pen_collect_reports);
5550
5551 IF g_pen_collect_reports is null
5552 THEN
5553 OPEN csr_get_pen_reports_exts(ben_ext_thread.g_ext_dfn_id);
5554 FETCH csr_get_pen_reports_exts INTO l_pen_ext_exists;
5555
5556 IF csr_get_pen_reports_exts%found
5557 THEN
5558 g_pen_collect_reports := 'Y';
5559 ELSE
5560 g_pen_collect_reports := 'N';
5561 END IF;
5562
5563 CLOSE csr_get_pen_reports_exts;
5564
5565 END IF;
5566
5567 IF g_pen_collect_reports = 'Y'
5568 THEN
5569 open csr_all_proces_eve_eff_pen_rep(
5570 l_bulk_processing,
5571 p_start_date ,
5572 p_end_date
5573 );
5574 --
5575 fetch csr_all_proces_eve_eff_pen_rep bulk collect into
5576 l_column_name,
5577 l_event_type,
5578 l_event_update_id,
5579 l_effective_date,
5580 l_assignment_id,
5581 l_surrogate_key,
5582 l_process_event_id,
5583 l_description,
5584 l_calculation_date,
5585 l_creation_date,
5586 l_change_type,
5587 l_table_name;
5588
5589 ELSE
5590 --bug 7443747:Stop
5591 open csr_all_process_events_eff_pen(
5592 l_bulk_processing,
5593 p_start_date ,
5594 p_end_date
5595 );
5596 --
5597 fetch csr_all_process_events_eff_pen bulk collect into
5598 l_column_name,
5599 l_event_type,
5600 l_event_update_id,
5601 l_effective_date,
5602 l_assignment_id,
5603 l_surrogate_key,
5604 l_process_event_id,
5605 l_description,
5606 l_calculation_date,
5607 l_creation_date,
5608 l_change_type,
5609 l_table_name;
5610
5611 --bug 7443747:Start
5612 END IF;
5613 --bug 7443747:Stop
5614
5615 end if;
5616
5617 for i in 1..l_process_event_id.count loop
5618 --
5619 l_mst_pe_rec.updated_column_name := l_column_name(i);
5620 l_mst_pe_rec.event_type := l_event_type(i);
5621 l_mst_pe_rec.event_update_id := l_event_update_id(i);
5622 l_mst_pe_rec.effective_date := l_effective_date(i);
5623 l_mst_pe_rec.assignment_id := l_assignment_id(i);
5624 l_mst_pe_rec.surrogate_key := l_surrogate_key(i);
5625 l_mst_pe_rec.process_event_id := l_process_event_id(i);
5626 l_mst_pe_rec.change_values := l_description(i);
5627 l_mst_pe_rec.calculation_date := l_calculation_date(i);
5628 l_mst_pe_rec.creation_date := l_creation_date(i);
5629 l_mst_pe_rec.change_mode := l_change_type(i);
5630 l_mst_pe_rec.table_name := l_table_name(i);
5631 --
5632 analyse_disco_process_events
5633 (
5634 p_element_entry_id => p_element_entry_id,
5635 p_assignment_id => p_assignment_id,
5636 p_assignment_action_id => p_assignment_action_id,
5637 p_business_group_id => p_business_group_id,
5638 p_start_date => p_start_date,
5639 p_end_date => p_end_date,
5640 p_mode => p_mode,
5641 p_process => p_process,
5642 p_process_mode => p_process_mode,
5643 p_range_start => p_global_env.monitor_start_ptr,
5644 p_range_end => p_global_env.monitor_end_ptr,
5645 p_mst_pe_rec => l_mst_pe_rec,
5646 p_event_group_id => p_event_group_id,
5647 p_distinct_tab => glo_monitored_events,
5648 p_penserv_mode => p_penserv_mode,
5649 p_date_counter => l_date_counter,
5650 p_global_env => p_global_env,
5651 t_dynamic_sql => t_dynamic_sql,
5652 t_proration_dates_temp => t_proration_dates_temp,
5653 t_proration_change_type => t_proration_change_type,
5654 t_proration_type => t_proration_type,
5655 t_detailed_output => t_detailed_output
5656 );
5657 --
5658 end loop; --Get next process event to do comparisons on
5659 if (p_penserv_mode = 'N') then
5660 close csr_all_process_events_eff;
5661 else
5662 --bug 7443747:Start
5663 IF g_pen_collect_reports = 'Y'
5664 THEN
5665 close csr_all_proces_eve_eff_pen_rep;
5666 ELSE
5667 --bug 7443747:Stop
5668 close csr_all_process_events_eff_pen;
5669 --bug 7443747:Start
5670 END IF;
5671 --bug 7443747:Stop
5672 end if;
5673
5674 END IF; --END MAIN IF CRE OR EFF
5675
5676
5677 hr_utility.set_location(l_proc, 900);
5678 END record_all_disco_events;
5679
5680
5681
5682 --------------------------
5683 --------------------------
5684
5685 /* ----------------------------------------------------------
5686 Main Entrance procedure, get info of datetracked event we're looking for
5687 then call procedure for this type
5688 I - Ins event
5689 U - Update event
5690 E - End date event
5691 P - Purge event
5692 C - Correction event
5693 DF - Delete Future (Equals both Delete Next and Future Changes)
5694 ---------------------------------------------------------- */
5695 PROCEDURE event_group_tables_affected
5696 (
5697 p_element_entry_id IN NUMBER DEFAULT NULL,
5698 p_assignment_action_id IN NUMBER,
5699 p_event_group_id IN NUMBER,
5700 p_assignment_id IN NUMBER,
5701 p_business_group_id IN NUMBER,
5702 p_start_date IN DATE,
5703 p_end_date IN DATE,
5704 p_mode IN VARCHAR2,
5705 p_process IN VARCHAR2,
5706 p_process_mode IN VARCHAR2,
5707 p_global_env IN OUT NOCOPY t_global_env_rec,
5708 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
5709 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
5710 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
5711 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
5712 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
5713 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
5714 ) AS
5715 --Misc helper/counters
5716 l_date_counter NUMBER ;
5717 l_range_start NUMBER;
5718 l_range_end NUMBER;
5719 l_dyt_type VARCHAR2(15); --Holds wot type of dyt table has
5720
5721 l_proc VARCHAR2(80) := 'event_group_tables_affected';
5722
5723
5724 BEGIN
5725 hr_utility.set_location(l_proc, 10);
5726 if (g_traces) then
5727 hr_utility.trace('Event group ID '||p_event_group_id||
5728 ' number of events in t_proration_group_tab: '||t_proration_group_tab.COUNT);
5729 end if;
5730
5731
5732 --For each Table that features in the clients event group we need
5733 --to gather all the potential events from ppe, then compare each potential
5734 --to the actual reqd events and add them to our store of Happened events
5735 --all this code is within record_disco_events
5736
5737 -- EG
5738 -- PROCESS EVENTS
5739 -- pay_process_events are all events that have occurred
5740 -- DATETRACKED EVENTS
5741 -- All events the user has declared they are interested in,
5742 -- ie child of event group
5743 -- DISCO(vered) EVENTS
5744 -- Matched PROCESS EVENTS with DATETRACKED EVENTS, eg this list is
5745 -- the whole point of interpreter
5746
5747 -- Call this for each table in event group
5748 record_all_disco_events
5749 (
5750 p_event_group_id => p_event_group_id,
5751
5752 p_element_entry_id => p_element_entry_id,
5753 p_assignment_id => p_assignment_id,
5754 p_assignment_action_id => p_assignment_action_id,
5755 p_business_group_id => p_business_group_id,
5756 p_start_date => p_start_date,
5757 p_end_date => p_end_date,
5758 p_process => p_process,
5759 p_mode => p_mode,
5760 p_process_mode => p_process_mode,
5761 p_global_env => p_global_env,
5762 t_dynamic_sql => t_dynamic_sql,
5763 t_proration_dates_temp => t_proration_dates_temp,
5764 t_proration_change_type => t_proration_change_type,
5765 t_proration_type => t_proration_type,
5766 t_detailed_output => t_detailed_output,
5767 p_penserv_mode => p_penserv_mode
5768 );
5769
5770 hr_utility.set_location(l_proc, 900);
5771 END event_group_tables_affected;
5772
5773
5774 PROCEDURE event_group_tables_affected
5775 (
5776 p_element_entry_id IN NUMBER DEFAULT NULL ,
5777 p_assignment_action_id IN NUMBER,
5778 p_event_group_id IN NUMBER,
5779 p_assignment_id IN NUMBER,
5780 p_business_group_id IN NUMBER,
5781 p_start_date IN DATE,
5782 p_end_date IN DATE,
5783 p_mode IN VARCHAR2,
5784 p_process IN VARCHAR2,
5785 p_process_mode IN VARCHAR2,
5786 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
5787 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
5788 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
5789 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
5790 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
5791 ) is
5792 t_proration_type t_proration_type_table_type;
5793 l_global_env t_global_env_rec;
5794 begin
5795 initialise_global(l_global_env);
5796 --
5797 glo_monitored_events := t_distinct_tab;
5798 l_global_env.monitor_start_ptr :=
5799 t_proration_group_tab(p_event_group_id).range_start;
5800 l_global_env.monitor_end_ptr :=
5801 t_proration_group_tab(p_event_group_id).range_end;
5802 --
5803
5804 event_group_tables_affected
5805 (
5806 p_element_entry_id => p_element_entry_id,
5807 p_assignment_action_id => p_assignment_action_id,
5808 p_event_group_id => p_event_group_id,
5809 p_assignment_id => p_assignment_id,
5810 p_business_group_id => p_business_group_id,
5811 p_start_date => p_start_date,
5812 p_end_date => p_end_date,
5813 p_mode => p_mode,
5814 p_process => p_process,
5815 p_process_mode => p_process_mode,
5816 p_global_env => l_global_env,
5817 t_dynamic_sql => t_dynamic_sql,
5818 t_proration_dates_temp => t_proration_dates_temp,
5819 t_proration_change_type => t_proration_change_type,
5820 t_proration_type => t_proration_type,
5821 t_detailed_output => t_detailed_output,
5822 p_penserv_mode => p_penserv_mode
5823 );
5824
5825 end;
5826
5827 /*
5828 NAME
5829 validate_entry_parameters
5830
5831 DESCRIPTION
5832 Validate all the parameters supplied to entry_affected
5833 */
5834 procedure validate_entry_parameters ( p_assignment_action_id IN NUMBER,
5835 p_assignment_id IN NUMBER,
5836 p_mode IN VARCHAR2,
5837 p_process IN VARCHAR2,
5838 p_event_group_id IN NUMBER,
5839 p_process_mode IN VARCHAR2,
5840 p_start_date IN DATE,
5841 p_end_date IN DATE,
5842 p_outprocess_mode OUT NOCOPY VARCHAR2
5843 )
5844 is
5845 begin
5846 --
5847 -- Ensure we have either an assignment or and action.
5848 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:1',
5849 ( p_assignment_action_id is not null
5850 or p_assignment_id is not null));
5851 --
5852 -- Ensure the mode is correct
5853 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:2',
5854 (p_mode in ('COST_CENTRE',
5855 'DATE_EARNED',
5856 'DATE_PROCESSED',
5857 'PAYMENT',
5858 'REPORTS')
5859 or p_mode is null));
5860 --
5861 -- Ensure the status is correct
5862 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:3',
5863 (p_process in ('U',
5864 'P',
5865 'C')
5866 or p_process is null));
5867 --
5868 -- Ensure the processing mode is correct
5869 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:4',
5870 (p_process_mode in ('ENTRY_EFFECTIVE_DATE',
5871 'ENTRY_RETROSTATUS',
5872 'ENTRY_CREATION_DATE',
5873 'PRORATION')
5874 ));
5875 --
5876 if (p_process_mode = 'PRORATION') then
5877 --
5878 p_outprocess_mode := 'ENTRY_EFFECTIVE_DATE';
5879 --
5880 else
5881 --
5882 p_outprocess_mode := p_process_mode;
5883 --
5884 end if;
5885 --
5886 end validate_entry_parameters;
5887
5888 --This is called directly by ADV_RETRONOT and CONT CALC
5889 --also called by historic overloaded entry points
5890 --This is an exact copy of the logic in the original entry_affected
5891 --procedure, except having the three additional date parameters, thus existing code
5892 --will just call the original which in turn calls this overloaded definition
5893 PROCEDURE entry_affected
5894 (
5895 p_element_entry_id IN NUMBER DEFAULT NULL ,
5896 p_assignment_action_id IN NUMBER DEFAULT NULL ,
5897 p_assignment_id IN NUMBER DEFAULT NULL ,
5898 p_mode IN VARCHAR2 DEFAULT NULL ,
5899 p_process IN VARCHAR2 DEFAULT NULL ,
5900 p_event_group_id IN NUMBER DEFAULT NULL ,
5901 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE' ,
5902 p_start_date IN DATE DEFAULT hr_api.g_sot, --events created since this date
5903 p_end_date IN DATE DEFAULT hr_api.g_eot, --events created until this date
5904 p_process_date IN DATE DEFAULT SYSDATE, -- This date, drives for getting
5905 -- a dflt event grop id if one is not passed
5906 p_unique_sort IN VARCHAR2, --default 'Y', --quicker if N
5907 p_business_group_id IN NUMBER, --default null, in case someones wrapper needs it
5908 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
5909 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
5910 t_proration_change_type OUT NOCOPY t_proration_type_table_type,
5911 t_proration_type OUT NOCOPY t_proration_type_table_type,
5912 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
5913 ) AS
5914
5915 cursor csr_dflt_grps (cp_ee_id in number, cp_report_date in date ) IS
5916 select distinct(et.recalc_event_group_id) recalc_event_group_id
5917 from
5918 pay_element_entries_f ee
5919 ,pay_element_links_f el
5920 ,pay_element_types_f et
5921 where ee.element_entry_id = nvl(cp_ee_id,-1)
5922 and ee.element_link_id = el.element_link_id
5923 and el.element_type_id = et.element_type_id
5924 and cp_report_date between et.effective_start_date
5925 and et.effective_end_date;
5926
5927 -- Local variables declaration.
5928
5929 l_assignment_id per_all_assignments_f.assignment_id%TYPE ;
5930 l_event_group_id NUMBER ;
5931 l_business_group_id NUMBER ;
5932 l_start_date DATE := p_start_date ;
5933 l_end_date DATE := p_end_date ;
5934 t_dynamic_sql t_dynamic_sql_tab ;
5935 t_proration_dates_temp t_proration_dates_table_type ;
5936 t_proration_change_type_temp t_proration_type_table_type;
5937 t_proration_type_temp t_proration_type_table_type;
5938 l_internal_mode varchar2(30);
5939 l_process_mode varchar2(30);
5940 l_global_env t_global_env_rec;
5941
5942 l_proc VARCHAR2(80) := 'entry_affected';
5943
5944 BEGIN
5945 g_traces := hr_utility.debug_enabled ;
5946
5947 if (g_traces) then
5948 hr_utility.trace('+------ ENTERED INTERPRETER ----+');
5949 hr_utility.trace('| Assignment Id ' || p_assignment_id);
5950 hr_utility.trace('| Assignment Act Id ' || p_assignment_action_id);
5951 hr_utility.trace('| Element Entry Id ' || TO_CHAR(p_element_entry_id));
5952 hr_utility.trace('| Event Group Id ' || p_event_group_id);
5953 end if;
5954
5955 if (g_dbg) then
5956 hr_utility.trace('| P_mode ' || p_mode);
5957 hr_utility.trace('| P_process ' || p_process);
5958 hr_utility.trace('| P_process_mode ' || p_process_mode);
5959 hr_utility.trace('+-------------------------------+ ');
5960 end if;
5961 --
5962 -- were either passed in and ee_id,event_group_id ,assignment_id ,date, mode
5963 -- or a ee_id and assign_act_id.
5964 -- if its the second then we need to calc the assginemt_id, and time periods
5965 -- ENTRY_CREATION_DATE is a mode where dates are past in,
5966 -- Continuous Calculation is an example of this process mode
5967 validate_entry_parameters (p_assignment_action_id => p_assignment_action_id,
5968 p_assignment_id => p_assignment_id,
5969 p_mode => p_mode,
5970 p_process => p_process,
5971 p_event_group_id => p_event_group_id,
5972 p_process_mode => p_process_mode,
5973 p_start_date => p_start_date,
5974 p_end_date => p_end_date,
5975 p_outprocess_mode => l_process_mode
5976 );
5977 --
5978
5979 -- Empty all results, this stops accidental
5980 -- results in calling code and massive memory overheads
5981 -- Please remember NOT to pass in partial tables of results
5982 t_dynamic_sql.delete;
5983 t_proration_dates_temp.delete;
5984 t_proration_change_type_temp.delete;
5985 t_proration_type_temp.delete;
5986
5987
5988 IF p_process_mode = 'PRORATION' THEN
5989 event_group_info(p_assignment_action_id,
5990 p_element_entry_id,
5991 l_event_group_id,
5992 l_assignment_id,
5993 l_business_group_id,
5994 l_start_date,
5995 l_end_date);
5996 l_internal_mode := 'PRORATION';
5997 ELSE
5998
5999 -- if no business group id is passed then get from cache,
6000 -- if not in cache either then hit the db for it and store in cache
6001 if (p_business_group_id is null) then
6002 if (g_business_group_id is null) then
6003 --cache empty so get now
6004 select max(business_group_id)
6005 into l_business_group_id
6006 from per_all_assignments_f
6007 where assignment_id = p_assignment_id;
6008
6009 -- There is one exceptional circumstance where bg_id is null
6010 -- specifically, a purge of an asg from per_all_assignments_f
6011 -- if we care that its been purged then weve caught the event so
6012 -- workaround is to get the bg from ppe
6013 --
6014 if ( l_business_group_id is null) then
6015 select max(business_group_id)
6016 into l_business_group_id
6017 from pay_process_events
6018 where assignment_id = p_assignment_id;
6019 end if;
6020
6021
6022 if (g_dbg) then
6023 hr_utility.trace('BG ID was null, now ' ||l_business_group_id);
6024 end if;
6025
6026 -- set cache value
6027 g_business_group_id := l_business_group_id;
6028 else
6029 -- Use the cached value
6030 l_business_group_id := g_business_group_id;
6031 end if;
6032 else
6033 --use the parameter version and set global
6034 l_business_group_id := p_business_group_id;
6035 g_business_group_id := l_business_group_id;
6036
6037 end if;
6038
6039 l_assignment_id:=p_assignment_id;
6040 l_event_group_id:=p_event_group_id;
6041 l_internal_mode := 'RECALCULATION';
6042 END IF;
6043
6044 hr_utility.set_location(l_proc, 20);
6045 -- If event group id has not been passed then check to see if there is
6046 -- a default event group for this element type
6047 --
6048 if (l_event_group_id is null) then
6049 --
6050 -- If we are Prorating then there isn't a Proration group, hence
6051 -- just leave the procedure
6052 --
6053 if (l_internal_mode = 'PRORATION') then
6054 --
6055 return;
6056 --
6057 else
6058 for dflt_ev_grp in csr_dflt_grps(p_element_entry_id,p_process_date) loop
6059 --just one row, but fetch neatly
6060 l_event_group_id := dflt_ev_grp.recalc_event_group_id;
6061 end loop;
6062 if (g_traces) then
6063 hr_utility.trace(' Event Group ID from element type dflt: '||l_event_group_id);
6064 end if;
6065
6066 -- if we still have no event group just bug out
6067 if (l_event_group_id is null) then
6068 if (g_traces) then
6069 hr_utility.trace('>>> No event group => return null from interpreter');
6070 end if;
6071 return;
6072 end if;
6073 end if;
6074
6075
6076 end if;
6077
6078 event_group_tables(l_event_group_id);
6079 --
6080 -- Setup the global structure
6081 --
6082 initialise_global(l_global_env);
6083 glo_monitored_events := t_distinct_tab;
6084 l_global_env.monitor_start_ptr :=
6085 t_proration_group_tab(l_event_group_id).range_start;
6086 l_global_env.monitor_end_ptr :=
6087 t_proration_group_tab(l_event_group_id).range_end;
6088 --
6089 hr_utility.set_location(l_proc, 30);
6090
6091 event_group_tables_affected( p_element_entry_id,
6092 p_assignment_action_id,
6093 l_event_group_id,
6094 l_assignment_id,
6095 l_business_group_id,
6096 l_start_date,
6097 l_end_date,
6098 p_mode,
6099 p_process,
6100 l_process_mode,
6101 l_global_env,
6102 t_dynamic_sql,
6103 t_proration_dates_temp,
6104 t_proration_change_type_temp,
6105 t_proration_type_temp,
6106 t_detailed_output,
6107 p_penserv_mode);
6108
6109 hr_utility.set_location(l_proc, 40);
6110
6111 --Only perform the sort if calling procedure has requested it
6112 --NB detailed output results table is never sorted
6113 if (p_unique_sort = 'Y') then
6114 -- This procedure sorts the dates and then generate the listing of unique dates.
6115 unique_sort(p_proration_dates_temp => t_proration_dates_temp ,
6116 p_proration_dates => t_proration_dates ,
6117 p_change_type_temp => t_proration_change_type_temp,
6118 p_proration_type_temp => t_proration_type_temp,
6119 p_change_type => t_proration_change_type,
6120 p_proration_type => t_proration_type,
6121 p_internal_mode => l_internal_mode );
6122
6123 elsif (l_internal_mode = 'PRORATION') then
6124 --
6125 t_proration_type := t_proration_type_temp;
6126 t_proration_dates := t_proration_dates_temp;
6127 t_proration_change_type := t_proration_change_type_temp;
6128 --
6129 end if;
6130
6131 hr_utility.set_location(l_proc, 900);
6132 END entry_affected;
6133
6134 PROCEDURE get_subset_given_new_evg
6135 (
6136 p_filter_event_group_id IN NUMBER ,
6137 p_complete_detail_tab IN t_detailed_output_table_type ,
6138 p_subset_detail_tab IN OUT NOCOPY t_detailed_output_table_type
6139 ) AS
6140
6141 CURSOR csr_reqd_events is
6142 SELECT DISTINCT pdt.dated_table_id table_id ,
6143 pdt.table_name table_name ,
6144 pde.column_name column_name ,
6145 pde.update_type update_type
6146 FROM pay_datetracked_events pde,
6147 pay_dated_tables pdt
6148 WHERE pde.event_group_id = p_filter_event_group_id
6149 AND pdt.dated_table_id = pde.dated_table_id
6150 order by pdt.dated_table_id;
6151
6152
6153 l_proc VARCHAR2(80) := 'get_subset_given_new_evg';
6154
6155 k number := 1;
6156 BEGIN
6157 g_traces := hr_utility.debug_enabled ;
6158
6159 if (g_traces) then
6160 hr_utility.set_location(l_proc,10);
6161 hr_utility.trace('| Filter full results using new Event Group Id: ' ||
6162 p_filter_event_group_id);
6163 end if;
6164
6165 -- For each given event, look for the event in our new event group
6166 -- Match the table, column and update type from the complete details tab
6167
6168 -- Loop through the required events
6169 for reqd_event_rec in csr_reqd_events loop
6170
6171 if (g_dbg) then
6172 hr_utility.trace('Looking for '||reqd_event_rec.update_type||' on '||
6173 reqd_event_rec.table_name||'.'||reqd_event_rec.column_name);
6174 end if;
6175
6176 -- Loop through all the full table events
6177 for j in 1..p_complete_detail_tab.count loop
6178
6179 --First check type and table match
6180 if (reqd_event_rec.table_id = p_complete_detail_tab(j).dated_table_id
6181 and reqd_event_rec.update_type = p_complete_detail_tab(j).update_type)
6182 then
6183
6184 -- Second, Check that if we care, the column is also the same
6185 if ( reqd_event_rec.update_type not in ('U','C') )
6186 then
6187 --dont care about col - Found a match, add this event to results
6188 p_subset_detail_tab(k) := p_complete_detail_tab(j);
6189 k := k + 1;
6190 elsif
6191 (nvl(reqd_event_rec.column_name,'X') = nvl(p_complete_detail_tab(j).column_name,'X') )
6192 then
6193 --do care about col - Found a match, add this event to results
6194 p_subset_detail_tab(k) := p_complete_detail_tab(j);
6195 k := k + 1;
6196 end if;
6197
6198 end if;
6199
6200 end loop; --Get next event in full detail table
6201
6202 end loop; --Get next reqd event from the filter event group
6203
6204 if (g_traces) then
6205 hr_utility.trace('| p_complete_details_tab contained: '
6206 ||p_complete_detail_tab.count() );
6207 hr_utility.trace('| p_subset_detail_tab contains: '
6208 ||p_subset_detail_tab.count() );
6209 hr_utility.set_location(l_proc,900);
6210 end if;
6211 END get_subset_given_new_evg;
6212
6213
6214 PROCEDURE entries_affected
6215 (
6216 p_assignment_id IN NUMBER DEFAULT NULL ,
6217 p_mode IN VARCHAR2 DEFAULT NULL ,
6218 p_start_date IN DATE DEFAULT hr_api.g_sot,
6219 p_end_date IN DATE DEFAULT hr_api.g_eot,
6220 p_business_group_id IN NUMBER,
6221 p_global_env IN OUT NOCOPY t_global_env_rec,
6222 t_detailed_output OUT NOCOPY t_detailed_output_table_type,
6223 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE',
6224 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
6225 ) AS
6226
6227 -- Local variables declaration.
6228
6229 l_assignment_id per_all_assignments_f.assignment_id%TYPE ;
6230 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE ;
6231 t_dynamic_sql t_dynamic_sql_tab ;
6232 t_proration_dates_temp t_proration_dates_table_type ;
6233 t_proration_change_type_temp t_proration_type_table_type;
6234 t_proration_type_temp t_proration_type_table_type;
6235 l_internal_mode varchar2(30);
6236 l_process_mode varchar2(30);
6237 l_processout_mode varchar2(30);
6238
6239 l_proc VARCHAR2(80) := 'entries_affected';
6240
6241 BEGIN
6242 g_traces := hr_utility.debug_enabled ;
6243
6244 if (g_traces) then
6245 hr_utility.trace('+------ ENTERED INTERPRETER ----+');
6246 hr_utility.trace('| Assignment Id ' || p_assignment_id);
6247 end if;
6248
6249 if (g_dbg) then
6250 hr_utility.trace('| P_mode ' || p_mode);
6251 hr_utility.trace('+-------------------------------+ ');
6252 end if;
6253 --
6254 -- Setup the global structure
6255 --
6256 l_process_mode := p_process_mode;
6257 --
6258 validate_entry_parameters (p_assignment_action_id => null,
6259 p_assignment_id => p_assignment_id,
6260 p_mode => p_mode,
6261 p_process => null,
6262 p_event_group_id => null,
6263 p_process_mode => l_process_mode,
6264 p_start_date => p_start_date,
6265 p_end_date => p_end_date,
6266 p_outprocess_mode => l_processout_mode
6267 );
6268 --
6269 select assignment_action_id
6270 into l_assignment_action_id
6271 from pay_assignment_actions
6272 where assignment_id = p_assignment_id
6273 and rownum = 1;
6274 --
6275 -- Empty all results, this stops accidental
6276 -- results in calling code and massive memory overheads
6277 -- Please remember NOT to pass in partial tables of results
6278 t_dynamic_sql.delete;
6279 t_detailed_output.delete;
6280 t_proration_dates_temp.delete;
6281 t_proration_change_type_temp.delete;
6282 t_proration_type_temp.delete;
6283
6284
6285 l_assignment_id:=p_assignment_id;
6286 l_internal_mode := 'RECALCULATION';
6287
6288 hr_utility.set_location(l_proc, 20);
6289
6290 event_group_tables_affected( null,
6291 l_assignment_action_id,
6292 null,
6293 l_assignment_id,
6294 p_business_group_id,
6295 p_start_date,
6296 p_end_date,
6297 p_mode,
6298 null,
6299 l_processout_mode,
6300 p_global_env,
6301 t_dynamic_sql,
6302 t_proration_dates_temp,
6303 t_proration_change_type_temp,
6304 t_proration_type_temp,
6305 t_detailed_output,
6306 p_penserv_mode);
6307
6308 hr_utility.set_location(l_proc, 40);
6309
6310 hr_utility.set_location(l_proc, 900);
6311 END entries_affected;
6312
6313
6314 begin
6315 g_valact_rec.assignment_id := '-1';
6316 g_grd_assignment_id := -1;
6317 END PAY_INTERPRETER_PKG;