1 PACKAGE BODY pay_interpreter_pkg AS
2 /* $Header: pyinterp.pkb 120.45.12020000.5 2012/07/06 11:53:14 vmaripal 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 :=
80 */
77 p_element_entry_id;
78 --
79 /* Put the new entry at the head of the chain
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
162 WHERE event_group_id = p_proration_group_id;
159 CURSOR c_event_group IS
160 SELECT proration_type, time_definition_id
161 FROM pay_event_groups
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;
184 if (g_traces) then
181 p_end_date := ctp.end_date ;
182 END LOOP; --}
183 --
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);
468 p_proration_dates_temp(l_var) := p_proration_dates_temp(l_sort_i);
465 l_var := l_sort_j;
466 END IF;
467 END LOOP;
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
501 p_change_type(l_counter) :=
498 l_counter := l_counter + 1 ;
499 p_proration_dates(l_counter) :=
500 p_proration_dates_temp(l_unique);
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 ,
2391 CURSOR c_event_updates(p_event_update_id IN NUMBER ,
2388 p_process_event_tab IN t_process_event_table,
2389 p_affected OUT NOCOPY VARCHAR2 ) AS
2390
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;
2526 -- Build_SQL_dynamically;
2523 END IF;
2524 l_loop_flag := TRUE;
2525 END LOOP;
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 :=
2951 glo_event_qualifiers(p_datetracked_event_id)
2948 glo_event_qualifiers(p_datetracked_event_id)
2949 .valid_event;
2950 default_asg_qual :=
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 nvl(save_event,FALSE) = FALSE and -- bug 8298970. save_event is null as it is not initialized
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
3477
3474 --
3475 Detail Logic:
3476 /* ----------------------------------------------------------
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',
3599 p_datetracked_event => p_dtevent_rec.datetracked_event_id,
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,
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 /* Bug 13784208 : Handling the event DELETE_NEXT_CHANGE */
4729 ELSIF (l_search='D' and p_mst_pe_rec.event_type='DELETE_NEXT_CHANGE') THEN
4730 disco := G_DISCO_STANDARD;
4731 END IF;
4732
4733
4734 If (disco <> G_DISCO_NONE) then
4735 --Add found event to store
4736
4737 save_disco_details (
4738 p_effective_date => p_mst_pe_rec.effective_date,
4739 p_creation_date => p_mst_pe_rec.creation_date,
4740 p_update_type => l_search,
4741 p_change_mode => p_mst_pe_rec.change_mode,
4742 p_process_mode => p_process_mode,
4743 p_proration_type => l_look_for_rec.proration_type,
4744 p_datetracked_event => l_look_for_rec.datetracked_event_id,
4745 p_column_name => p_mst_pe_rec.updated_column_name,
4746 p_change_values => p_mst_pe_rec.change_values,
4747 p_element_entry_id => p_element_entry_id,
4748 p_surrogate_key => p_mst_pe_rec.surrogate_key,
4749 p_dated_table_id => l_look_for_rec.table_id,
4750 p_table_name => l_look_for_rec.table_name,
4751 p_disco => disco,
4752 p_start_date => p_start_date,
4753 p_end_date => p_end_date,
4754 p_assignment_action_id => p_assignment_action_id,
4755 p_business_group_id => p_business_group_id,
4756 p_assignment_id => p_assignment_id,
4757 p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
4758 p_date_counter => p_date_counter,
4759 p_global_env => p_global_env,
4760 t_proration_dates_temp => t_proration_dates_temp,
4761 t_proration_change_type => t_proration_change_type,
4762 t_proration_type => t_proration_type,
4763 t_detailed_output => t_detailed_output
4764 );
4765 disco := G_DISCO_NONE;
4766
4767 end if;
4768
4769 END IF; --PE not on same table as DE, so get next DE
4770
4771 END LOOP; -- Get next datetracked event in cache and compare
4772 -- this event with that
4773 --
4774 end analyse_disco_process_events;
4775 --
4776 --
4777 -- Name: valid_group_event_for_asg
4778 -- Description : This function is used by the group level
4779 -- cursors. It tries to reduce the group
4780 -- level work needed by performing
4781 -- simple group level restrictions
4782 --
4783 function valid_group_event_for_asg(p_table_name in varchar2,
4784 p_assignment_id in number,
4785 p_surrogate_key in varchar2)
4786 return varchar2
4787 is
4788 --
4789 cursor validate_grade(p_assignment_id in number
4790 )
4791 is
4792 select /*+ USE_NL(pgr paf)*/
4793 pgr.grade_rule_id
4794 from pay_grade_rules_f pgr,
4795 per_all_assignments_f paf
4796 where paf.assignment_id = p_assignment_id
4797 and paf.grade_id = pgr.grade_or_spinal_point_id
4798 union all
4799 select /*+ ORDERED USE_NL(pgr psp psps pspp)*/
4800 pgr.grade_rule_id
4801 from per_spinal_point_placements_f pspp,
4802 per_spinal_point_steps_f psps,
4803 per_spinal_points psp,
4804 pay_grade_rules_f pgr
4805 where psp.spinal_point_id = pgr.grade_or_spinal_point_id
4806 and psp.spinal_point_id = psps.spinal_point_id
4807 and p_assignment_id = pspp.assignment_id
4808 and pspp.step_id = psps.step_id;
4809 --
4810 cursor validate_rate_by_criteria(p_assignment_id in number,
4811 p_surrogate_key in number)
4812 is
4813 select '' chk
4814 from dual
4815 where exists (
4816 select '' chk
4817 from pay_element_entries_f pee
4818 ,pqh_criteria_rate_elements pcre
4819 ,pqh_rate_matrix_rates_f prmr
4820 where pee.assignment_id=p_assignment_id
4821 and pcre.element_type_id=pee.element_type_id
4822 and pcre.criteria_rate_defn_id=prmr.criteria_rate_defn_id
4823 and prmr.rate_matrix_rate_id=p_surrogate_key)
4824 or exists (
4825 select '' chk
4826 from pay_element_entries_f pee
4827 ,pqh_criteria_rate_elements pcre
4828 ,pqh_criteria_rate_factors pcrf
4829 ,pqh_rate_matrix_rates_f prmr
4830 where pee.assignment_id=p_assignment_id
4831 and pcre.element_type_id=pee.element_type_id
4832 and pcre.criteria_rate_defn_id = pcrf.criteria_rate_defn_id
4833 and pcrf.parent_criteria_rate_defn_id = prmr.criteria_rate_defn_id
4834 and prmr.rate_matrix_rate_id = p_surrogate_key);
4835
4836 --
4837 /*Added the following cursor for bug 13029951*/
4838 cursor validate_input_value_id(p_assignment_id in number,
4839 p_surrogate_key in number)
4840 is
4841 SELECT '' chk
4842 FROM dual
4843 WHERE EXISTS
4844 (
4845 SELECT '' chk
4846 FROM pay_input_values_f piv
4847 ,pay_element_links_f pelf
4848 ,pay_element_entries_f peef
4849 WHERE piv.input_value_id = p_surrogate_key
4850 AND pelf.element_type_id = piv.element_type_id
4851 AND peef.element_link_id = pelf.element_link_id
4852 AND peef.assignment_id = p_assignment_id
4853 );
4854 --
4855 /*Added the following cursor for bug 13384584*/
4856 cursor val_entl_line_elig(p_assignment_id in number,
4857 p_surrogate_key in number)
4858 is
4859 SELECT '' chk
4860 FROM dual
4861 WHERE EXISTS
4862 (
4863 SELECT '' chk
4864 FROM per_cagr_entitlement_lines_f cagr_lines
4865 ,per_cagr_entitlements cagr_ent
4866 ,per_all_assignments_f paf
4867 WHERE cagr_lines.cagr_entitlement_line_id = p_surrogate_key
4868 AND cagr_ent.cagr_entitlement_id = cagr_lines.cagr_entitlement_id
4869 AND paf.collective_agreement_id = cagr_ent.collective_agreement_id
4870 AND paf.assignment_id = p_assignment_id
4871 );
4872
4873
4874 l_valid_event varchar2(5);
4875 begin
4876 --
4877 l_valid_event := 'Y';
4878 --
4879 if (p_table_name = 'PAY_GRADE_RULES_F') then
4880 --
4881 if (g_grd_assignment_id <> p_assignment_id) then
4882 --
4883 g_grade_list.delete();
4884 g_grd_assignment_id := p_assignment_id;
4885 --
4886 for grrec in validate_grade(p_assignment_id) loop
4887 g_grade_list(grrec.grade_rule_id) := grrec.grade_rule_id;
4888 end loop;
4889 --
4890 end if;
4891 --
4892 l_valid_event := 'N';
4893 --
4894 if (g_grade_list.exists(p_surrogate_key)) then
4895 --
4896 l_valid_event := 'Y';
4897 --
4898 end if;
4899 --
4900 elsif (p_table_name = 'PQH_RATE_MATRIX_RATES_F') then
4901
4902 l_valid_event := 'N';
4903
4904 for grrec in validate_rate_by_criteria(p_assignment_id,p_surrogate_key) loop
4905 --
4906 l_valid_event := 'Y';
4907 --
4908 end loop;
4909 --
4910 /*Added the following code for bug 13029951*/
4911 elsif (p_table_name = 'PAY_INPUT_VALUES_F') then
4912
4913 hr_utility.trace('table name is :PAY_INPUT_VALUES_F' );
4914 hr_utility.trace('Assignment id is '||p_assignment_id );
4915 hr_utility.trace('Surrogate Key is '||p_surrogate_key );
4916
4917 l_valid_event := 'N';
4918
4919 for grrec in validate_input_value_id(p_assignment_id,p_surrogate_key) loop
4920 --
4921 l_valid_event := 'Y';
4922 hr_utility.trace('The input_values event is valid for this assignment');
4923 --
4924 end loop;
4925 --
4926 /*Added the following code for bug 13384584*/
4927 elsif (p_table_name = 'PER_CAGR_ENTITLEMENT_LINES_F') then
4928
4929 hr_utility.trace('table name is PER_CAGR_ENTITLEMENT_LINES_F' );
4930 hr_utility.trace('Assignment id is '||p_assignment_id );
4931 hr_utility.trace('Surrogate Key is '||p_surrogate_key );
4932
4933 l_valid_event := 'N';
4934
4935 for grrec in val_entl_line_elig(p_assignment_id,p_surrogate_key) loop
4936 --
4937 hr_utility.trace('The Entitlement lines event is valid for this assignment');
4938 l_valid_event := 'Y';
4939 --
4940 end loop;
4941 --
4942 elsif (p_table_name = 'FF_GLOBALS_F') then
4943
4944 hr_utility.trace('table name is FF_GLOBALS_F ' );
4945 hr_utility.trace('Assignment id is '||p_assignment_id );
4946 hr_utility.trace('Surrogate Key is '||p_surrogate_key );
4947 --
4948 l_valid_event := pay_group_event_pkg.ff_global_check(p_assignment_id,p_surrogate_key);
4949 --
4950 elsif (p_table_name = 'PAY_USER_COLUMN_INSTANCES_F') then
4951 --
4952 l_valid_event := pay_group_event_pkg.pay_user_table_check(p_assignment_id,p_surrogate_key);
4953 --
4954 end if;
4955 --
4956 return l_valid_event;
4957 --
4958 end valid_group_event_for_asg;
4959 --
4960 --
4961 ----------------------------
4962
4963 -- ----------------------------------------------------------------------------
4964 -- |----------------------------< get_penserver_date >-------------------------|
4965 -- Description : For each assignment fetch the least effective date
4966 -- ----------------------------------------------------------------------------
4967
4968 FUNCTION get_penserver_date
4969 (p_assignment_id IN NUMBER
4970 ,p_business_group_id IN NUMBER
4971 ,p_lapp_date IN DATE
4972 ,p_end_date IN DATE
4973 ) RETURN date
4974 IS
4975
4976 l_penserver_date date;
4977
4978 -- This cursor will fetch the minimum efective on which a penserver event has
4979 -- occured for the employee having the creation_date in the current period.
4980 cursor csr_pen_eff_date
4981 is select min(ppe.effective_date)
4982 from pay_process_events ppe
4983 where trunc(ppe.creation_date) between p_lapp_date and p_end_date
4984 and ppe.assignment_id = p_assignment_id
4985 and ppe.business_group_id = p_business_group_id
4986 and ppe.effective_date >= ben_ext_thread.g_effective_start_date
4987 and exists (select pde.event_group_id
4988 from pay_datetracked_events pde,
4989 pay_event_updates peu
4990 where pde.event_group_id in (select becv.val_1
4991 from ben_ext_crit_val becv,
4992 ben_ext_crit_typ bect,
4993 ben_ext_dfn bed
4994 where becv.ext_crit_typ_id = bect.ext_crit_typ_id
4995 and bect.ext_crit_prfl_id = bed.ext_crit_prfl_id
4996 and bed.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
4997 and bect.crit_typ_cd = 'CPE')
4998 and ppe.event_update_id = peu.event_update_id
4999 and peu.dated_table_id = pde.dated_table_id);
5000
5001
5002 BEGIN
5003
5004 open csr_pen_eff_date;
5005 fetch csr_pen_eff_date into l_penserver_date;
5006 if l_penserver_date is null
5007 then
5008 l_penserver_date := p_lapp_date;
5009 end if;
5010 close csr_pen_eff_date;
5011
5012
5013 if l_penserver_date > p_lapp_date
5014 then
5015 l_penserver_date := p_lapp_date;
5016 end if;
5017
5018 l_penserver_date := l_penserver_date - 1;
5019
5020
5021 hr_utility.trace('p_lapp_date :' ||p_lapp_date);
5022 hr_utility.trace('l_penserver_date :' ||l_penserver_date);
5023
5024 RETURN l_penserver_date;
5025
5026 END get_penserver_date;
5027
5028 -- P_d(atetracked)ev(ents) info, eg stuff to look for
5029
5030 -- This procedure called for each table within the event group
5031 PROCEDURE record_all_disco_events
5032 (
5033 p_event_group_id IN NUMBER ,
5034
5035 p_element_entry_id IN NUMBER ,
5036 p_assignment_id IN NUMBER ,
5037 p_assignment_action_id IN NUMBER ,
5038 p_business_group_id IN NUMBER ,
5039 p_start_date IN DATE ,
5040 p_end_date IN DATE,
5041 p_process IN VARCHAR2,
5042 p_mode IN VARCHAR2,
5043 p_process_mode IN VARCHAR2,
5044 p_global_env IN OUT NOCOPY t_global_env_rec,
5045 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
5046 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
5047 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
5048 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
5049 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
5050 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
5051 ) AS
5052 --
5053 -- Setup the types
5054 --
5055 type t_column_name is table of pay_event_updates.column_name%type
5056 index by binary_integer;
5057 type t_event_type is table of pay_event_updates.event_type%type
5058 index by binary_integer;
5059 type t_event_update_id is table of pay_event_updates.event_update_id%type
5060 index by binary_integer;
5061 type t_effective_date is table of pay_process_events.effective_date%type
5062 index by binary_integer;
5063 type t_assignment_id is table of pay_process_events.assignment_id%type
5064 index by binary_integer;
5065 type t_surrogate_key is table of pay_process_events.surrogate_key%type
5066 index by binary_integer;
5067 type t_process_event_id is table of pay_process_events.process_event_id%type
5068 index by binary_integer;
5069 type t_description is table of pay_process_events.description%type
5070 index by binary_integer;
5071 type t_calculation_date is table of pay_process_events.calculation_date%type
5072 index by binary_integer;
5073 type t_creation_date is table of pay_process_events.creation_date%type
5074 index by binary_integer;
5075 type t_change_type is table of pay_process_events.change_type%type
5076 index by binary_integer;
5077 type t_table_name is table of pay_dated_tables.table_name%type
5078 index by binary_integer;
5079 --
5080 l_column_name t_column_name;
5081 l_event_type t_event_type;
5082 l_event_update_id t_event_update_id;
5083 l_effective_date t_effective_date;
5084 l_assignment_id t_assignment_id;
5085 l_surrogate_key t_surrogate_key;
5086 l_process_event_id t_process_event_id;
5087 l_description t_description;
5088 l_calculation_date t_calculation_date;
5089 l_creation_date t_creation_date;
5090 l_change_type t_change_type;
5091 l_table_name t_table_name;
5092 --
5093 -- NB Following statement has been tuned for performance purposes
5094 /*Modified the following cursor for bug 13735584
5095 The Second part of the query where group level events are picked
5096 added an extra clause to pick up the events which has the value
5097 of business group id populated as null in pay process events table */
5098 CURSOR csr_all_process_events_cre (
5099 cp_bulk_processing IN VARCHAR,
5100 cp_cstart_date IN DATE ,
5101 cp_cend_date IN DATE ) IS
5102
5103 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PPE) */
5104 peu.column_name updated_column_name ,
5105 peu.event_type event_type ,
5106 peu.event_update_id event_update_id ,
5107 ppe.effective_date effective_date ,
5108 ppe.assignment_id assignment_id ,
5109 ppe.surrogate_key surrogate_key ,
5110 ppe.process_event_id process_event_id,
5111 ppe.description change_values,
5112 ppe.calculation_date calculation_date,
5113 ppe.creation_date creation_date,
5114 ppe.change_type change_mode,
5115 pdt.table_name table_name
5116 FROM
5117 pay_dated_tables pdt ,
5118 pay_process_events ppe ,
5119 pay_event_updates peu
5120 WHERE
5121 peu.event_update_id = ppe.event_update_id + 0
5122 AND peu.dated_table_id = pdt.dated_table_id
5123 AND pdt.dated_table_id IN
5124 ( select distinct pde2.dated_table_id table_id
5125 from pay_datetracked_events pde2
5126 where pde2.event_group_id = p_event_group_id
5127 and cp_bulk_processing = 'N'
5128 union all
5129 select distinct pdt2.dated_table_id
5130 from pay_dated_tables pdt2
5131 where cp_bulk_processing = 'Y'
5132 )
5133 AND ppe.assignment_id is not null
5134 AND ppe.assignment_id = p_assignment_id
5135 AND ppe.business_group_id = p_business_group_id
5136 AND ( peu.business_group_id = ppe.business_group_id
5137 or peu.legislation_code = g_leg_code
5138 or ( peu.business_group_id is null
5139 and peu.legislation_code is null) )
5140 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5141 AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
5142 AND ( (cp_bulk_processing = 'Y')
5143 or ( cp_bulk_processing = 'N'
5144 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5145 or
5146 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5147 and ppe.surrogate_key=p_element_entry_id )
5148 )
5149 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5150 or
5151 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5152 and exists
5153 ( select null
5154 from pay_element_entry_values_f
5155 where element_entry_id = p_element_entry_id
5156 and element_entry_value_id =
5157 ppe.surrogate_key ) )
5158 )
5159 )
5160 )
5161 UNION ALL
5162 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PDT) */
5163 peu.column_name updated_column_name ,
5164 peu.event_type event_type ,
5165 peu.event_update_id event_update_id ,
5166 ppe.effective_date effective_date ,
5167 ppe.assignment_id assignment_id ,
5168 ppe.surrogate_key surrogate_key ,
5169 ppe.process_event_id process_event_id,
5170 ppe.description change_values,
5171 ppe.calculation_date calculation_date,
5172 ppe.creation_date creation_date,
5173 ppe.change_type change_mode,
5174 pdt.table_name table_name
5175 FROM
5176 pay_dated_tables pdt ,
5177 pay_event_updates peu ,
5178 pay_process_events ppe
5179 WHERE
5180 peu.event_update_id = ppe.event_update_id + 0
5181 AND peu.dated_table_id = pdt.dated_table_id
5182 AND pdt.dated_table_id IN
5183 ( select distinct pde2.dated_table_id table_id
5184 from pay_datetracked_events pde2
5185 where pde2.event_group_id = p_event_group_id
5186 and cp_bulk_processing = 'N'
5187 union all
5188 select distinct pdt2.dated_table_id
5189 from pay_dated_tables pdt2
5190 where cp_bulk_processing = 'Y'
5191 )
5192 AND ppe.assignment_id is null
5193 AND (ppe.business_group_id = p_business_group_id
5194 or ppe.business_group_id is null)
5195 AND ( peu.business_group_id = ppe.business_group_id
5196 or peu.legislation_code = g_leg_code
5197 or ( peu.business_group_id is null
5198 and peu.legislation_code is null) )
5199 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5200 AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
5201 AND ( (cp_bulk_processing = 'Y')
5202 or ( cp_bulk_processing = 'N'
5203 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5204 or
5205 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5206 and ppe.surrogate_key=p_element_entry_id )
5207 )
5208 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5209 or
5210 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5211 and exists
5212 ( select null
5213 from pay_element_entry_values_f
5214 where element_entry_id = p_element_entry_id
5215 and element_entry_value_id =
5216 ppe.surrogate_key ) )
5217 )
5218 )
5219 )
5220 AND pay_interpreter_pkg.valid_group_event_for_asg
5221 (pdt.table_name,
5222 p_assignment_id,
5223 ppe.surrogate_key) = 'Y'
5224 ORDER BY 11, 6, 5, 4;
5225
5226
5227 -- NB Following statement has been tuned for performance purposes
5228 CURSOR csr_all_process_events_eff(
5229 cp_bulk_processing IN VARCHAR2,
5230 cp_estart_date IN DATE ,
5231 cp_eend_date IN DATE ) IS
5232
5233 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PPE) */
5234 peu.column_name updated_column_name ,
5235 peu.event_type event_type ,
5236 peu.event_update_id event_update_id ,
5237 ppe.effective_date effective_date ,
5238 ppe.assignment_id assignment_id ,
5239 ppe.surrogate_key surrogate_key ,
5240 ppe.process_event_id process_event_id,
5241 ppe.description change_values,
5242 ppe.calculation_date calculation_date,
5243 ppe.creation_date creation_date,
5244 ppe.change_type change_mode,
5245 pdt.table_name table_name
5246 FROM
5247 pay_dated_tables pdt ,
5248 pay_process_events ppe ,
5249 pay_event_updates peu
5250 WHERE
5251 peu.event_update_id = ppe.event_update_id + 0
5252 AND peu.dated_table_id = pdt.dated_table_id
5253 AND pdt.dated_table_id IN
5254 ( select distinct pde2.dated_table_id table_id
5255 from pay_datetracked_events pde2
5256 where pde2.event_group_id = p_event_group_id
5257 and cp_bulk_processing = 'N'
5258 union all
5259 select distinct pdt2.dated_table_id
5260 from pay_dated_tables pdt2
5261 where cp_bulk_processing = 'Y'
5262 )
5263 AND ppe.assignment_id is not null
5264 AND ppe.assignment_id = p_assignment_id
5265 AND ppe.business_group_id = p_business_group_id
5266 AND ( peu.business_group_id = ppe.business_group_id
5267 or peu.legislation_code = g_leg_code
5268 or ( peu.business_group_id is null
5269 and peu.legislation_code is null) )
5270 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5271 or ppe.retroactive_status is null)
5272 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5273 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5274 AND ( (cp_bulk_processing = 'Y')
5275 or ( cp_bulk_processing = 'N'
5276 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5277 or
5278 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5279 and ppe.surrogate_key=p_element_entry_id )
5280 )
5281 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5282 or
5283 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5284 and exists
5285 ( select null
5286 from pay_element_entry_values_f
5287 where element_entry_id = p_element_entry_id
5288 and element_entry_value_id =
5289 ppe.surrogate_key ) )
5290 )
5291 )
5292 )
5293 UNION ALL
5294 SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PDT) */
5295 peu.column_name updated_column_name ,
5296 peu.event_type event_type ,
5297 peu.event_update_id event_update_id ,
5298 ppe.effective_date effective_date ,
5299 ppe.assignment_id assignment_id ,
5300 ppe.surrogate_key surrogate_key ,
5301 ppe.process_event_id process_event_id,
5302 ppe.description change_values,
5303 ppe.calculation_date calculation_date,
5304 ppe.creation_date creation_date,
5305 ppe.change_type change_mode,
5306 pdt.table_name table_name
5307 FROM
5308 pay_dated_tables pdt ,
5309 pay_event_updates peu ,
5310 pay_process_events ppe
5311 WHERE
5312 peu.event_update_id = ppe.event_update_id + 0
5313 AND peu.dated_table_id = pdt.dated_table_id
5314 AND pdt.dated_table_id IN
5315 ( select distinct pde2.dated_table_id table_id
5316 from pay_datetracked_events pde2
5317 where pde2.event_group_id = p_event_group_id
5318 and cp_bulk_processing = 'N'
5319 union all
5320 select distinct pdt2.dated_table_id
5321 from pay_dated_tables pdt2
5322 where cp_bulk_processing = 'Y'
5323 )
5324 AND ppe.assignment_id is null
5325 AND ppe.business_group_id = p_business_group_id
5326 AND ( peu.business_group_id = ppe.business_group_id
5327 or peu.legislation_code = g_leg_code
5328 or ( peu.business_group_id is null
5329 and peu.legislation_code is null) )
5330 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5331 or ppe.retroactive_status is null)
5332 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5333 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5334 AND ( (cp_bulk_processing = 'Y')
5335 or ( cp_bulk_processing = 'N'
5336 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5337 or
5338 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5339 and ppe.surrogate_key=p_element_entry_id )
5340 )
5341 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5342 or
5343 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5344 and exists
5345 ( select null
5346 from pay_element_entry_values_f
5347 where element_entry_id = p_element_entry_id
5348 and element_entry_value_id =
5349 ppe.surrogate_key ) )
5350 )
5351 )
5352 )
5353 AND pay_interpreter_pkg.valid_group_event_for_asg
5354 (pdt.table_name,
5355 p_assignment_id,
5356 ppe.surrogate_key) = 'Y'
5357 ORDER BY 11, 6, 5, 4, 2 ASC;
5358 --ORDER BY pdt.table_name, ppe.surrogate_key, ppe.assignment_id, ppe.effective_date;
5359
5360 -- The above cursor is modified for penserver extract
5361 CURSOR csr_all_process_events_eff_pen(
5362 cp_bulk_processing IN VARCHAR2,
5366 SELECT
5363 cp_estart_date IN DATE ,
5364 cp_eend_date IN DATE ) IS
5365
5367 peu.column_name updated_column_name ,
5368 peu.event_type event_type ,
5369 peu.event_update_id event_update_id ,
5370 ppe.effective_date effective_date ,
5371 ppe.assignment_id assignment_id ,
5372 ppe.surrogate_key surrogate_key ,
5373 ppe.process_event_id process_event_id,
5374 ppe.description change_values,
5375 ppe.calculation_date calculation_date,
5376 ppe.creation_date creation_date,
5377 ppe.change_type change_mode,
5378 pdt.table_name table_name
5379 FROM
5380 pay_dated_tables pdt ,
5381 pay_process_events ppe ,
5382 pay_event_updates peu
5383 WHERE
5384 peu.event_update_id = ppe.event_update_id + 0
5385 AND peu.dated_table_id = pdt.dated_table_id
5386 AND pdt.dated_table_id IN
5387 ( select distinct pde2.dated_table_id table_id
5388 from pay_datetracked_events pde2
5389 where pde2.event_group_id = p_event_group_id
5390 and cp_bulk_processing = 'N'
5391 union all
5392 select distinct pdt2.dated_table_id
5393 from pay_dated_tables pdt2
5394 where cp_bulk_processing = 'Y'
5395 )
5396 AND ppe.assignment_id is not null
5397 AND ppe.assignment_id = p_assignment_id
5398 AND ppe.business_group_id = p_business_group_id
5399 AND ( peu.business_group_id = ppe.business_group_id
5400 or peu.legislation_code = g_leg_code
5401 or ( peu.business_group_id is null
5402 and peu.legislation_code is null) )
5403 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5404 or ppe.retroactive_status is null)
5405 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5406 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5407 AND ( (cp_bulk_processing = 'Y')
5408 or ( cp_bulk_processing = 'N'
5409 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5410 or
5411 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5412 and ppe.surrogate_key=p_element_entry_id )
5413 )
5414 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5415 or
5416 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5417 and exists
5418 ( select null
5419 from pay_element_entry_values_f
5420 where element_entry_id = p_element_entry_id
5421 and element_entry_value_id =
5422 ppe.surrogate_key ) )
5423 )
5424 )
5425 )
5426 AND pay_interpreter_pkg.valid_group_event_for_asg
5427 (pdt.table_name,
5428 p_assignment_id,
5429 ppe.surrogate_key) = 'Y'
5430 --ORDER BY 11, 6, 5, 4;
5431 ORDER BY 11, 5, 4, 10 desc;
5432 -- for bug 9327541
5433 --ORDER BY pdt.table_name, ppe.surrogate_key, ppe.assignment_id, ppe.effective_date;
5434
5435 l_mst_pm VARCHAR2(30);
5436 l_proc VARCHAR2(80) := g_pkg||'.record_all_disco_events';
5437
5438
5439 --New holders
5440 l_date_counter NUMBER;
5441
5442 l_bulk_processing varchar2(5);
5443
5444 l_mst_pe_rec t_mst_process_event_rec;
5445
5446 --bug 7443747:Start
5447 -- New cursor for extracts that need to track just REPORTS type of events
5448 CURSOR csr_all_proces_eve_eff_pen_rep(
5449 cp_bulk_processing IN VARCHAR2,
5450 cp_estart_date IN DATE ,
5451 cp_eend_date IN DATE )
5452 IS
5453 SELECT
5454 peu.column_name updated_column_name ,
5455 peu.event_type event_type ,
5456 peu.event_update_id event_update_id ,
5457 ppe.effective_date effective_date ,
5458 ppe.assignment_id assignment_id ,
5459 ppe.surrogate_key surrogate_key ,
5460 ppe.process_event_id process_event_id,
5461 ppe.description change_values,
5462 ppe.calculation_date calculation_date,
5463 ppe.creation_date creation_date,
5464 ppe.change_type change_mode,
5465 pdt.table_name table_name
5466 FROM
5467 pay_dated_tables pdt ,
5468 pay_process_events ppe ,
5469 pay_event_updates peu
5470 WHERE
5471 peu.event_update_id = ppe.event_update_id + 0
5472 AND peu.dated_table_id = pdt.dated_table_id
5473 --Added new condition to capture REPORTS events only
5474 AND ppe.change_type = 'REPORTS'
5475 AND pdt.dated_table_id IN
5476 ( select distinct pde2.dated_table_id table_id
5477 from pay_datetracked_events pde2
5481 select distinct pdt2.dated_table_id
5478 where pde2.event_group_id = p_event_group_id
5479 and cp_bulk_processing = 'N'
5480 union all
5482 from pay_dated_tables pdt2
5483 where cp_bulk_processing = 'Y'
5484 )
5485 AND ppe.assignment_id is not null
5486 AND ppe.assignment_id = p_assignment_id
5487 AND ppe.business_group_id = p_business_group_id
5488 AND ( peu.business_group_id = ppe.business_group_id
5489 or peu.legislation_code = g_leg_code
5490 or ( peu.business_group_id is null
5491 and peu.legislation_code is null) )
5492 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5493 or ppe.retroactive_status is null)
5494 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5495 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5496 AND ( (cp_bulk_processing = 'Y')
5497 or ( cp_bulk_processing = 'N'
5498 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5499 or
5500 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5501 and ppe.surrogate_key=p_element_entry_id )
5502 )
5503 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5504 or
5505 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5506 and exists
5507 ( select null
5508 from pay_element_entry_values_f
5509 where element_entry_id = p_element_entry_id
5510 and element_entry_value_id =
5511 ppe.surrogate_key ) )
5512 )
5513 )
5514 )
5515 AND pay_interpreter_pkg.valid_group_event_for_asg
5516 (pdt.table_name,
5517 p_assignment_id,
5518 ppe.surrogate_key) = 'Y'
5519 --Modified order by to include actual date
5520 ORDER BY 11, 5, 4, 10 desc;
5521
5522 -- Create a seperate cursor for bonus extract alone
5523 -- Bug 10092118
5524 CURSOR csr_bon_proces_eve_eff_pen_rep(
5525 cp_bulk_processing IN VARCHAR2,
5526 cp_estart_date IN DATE ,
5527 cp_eend_date IN DATE,
5528 cp_last_approved_date IN DATE)
5529 IS
5530 SELECT
5531 peu.column_name updated_column_name ,
5532 peu.event_type event_type ,
5533 peu.event_update_id event_update_id ,
5534 ppe.effective_date effective_date ,
5535 ppe.assignment_id assignment_id ,
5536 ppe.surrogate_key surrogate_key ,
5537 ppe.process_event_id process_event_id,
5538 ppe.description change_values,
5539 ppe.calculation_date calculation_date,
5540 ppe.creation_date creation_date,
5541 ppe.change_type change_mode,
5542 pdt.table_name table_name
5543 FROM
5544 pay_dated_tables pdt ,
5545 pay_process_events ppe ,
5546 pay_event_updates peu
5547 WHERE
5548 peu.event_update_id = ppe.event_update_id + 0
5549 AND peu.dated_table_id = pdt.dated_table_id
5550 --Added new condition to capture REPORTS events only
5551 AND ppe.change_type = 'REPORTS'
5552 AND pdt.dated_table_id IN
5553 ( select distinct pde2.dated_table_id table_id
5554 from pay_datetracked_events pde2
5555 where pde2.event_group_id = p_event_group_id
5556 and cp_bulk_processing = 'N'
5557 union all
5558 select distinct pdt2.dated_table_id
5559 from pay_dated_tables pdt2
5560 where cp_bulk_processing = 'Y'
5561 )
5562 AND ppe.assignment_id is not null
5563 AND ppe.assignment_id = p_assignment_id
5564 AND ppe.business_group_id = p_business_group_id
5565 AND ( peu.business_group_id = ppe.business_group_id
5566 or peu.legislation_code = g_leg_code
5567 or ( peu.business_group_id is null
5568 and peu.legislation_code is null) )
5569 AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
5570 or ppe.retroactive_status is null)
5571 AND ppe.change_type = nvl(p_mode,ppe.change_type)
5572 AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
5573 /* Bug fix 12363861 Start
5574 AND ( ppe.effective_date > cp_last_approved_date or ppe.creation_date > cp_last_approved_date) -- Bug 10092118
5575 */
5576 AND ( ppe.effective_date > nvl(cp_last_approved_date, hr_api.g_sot) or ppe.creation_date > nvl(cp_last_approved_date, hr_api.g_sot)) -- Bug 10092118
5577 -- Bug fix 12363861 End
5578 AND ( (cp_bulk_processing = 'Y')
5579 or ( cp_bulk_processing = 'N'
5580 AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
5581 or
5582 ( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
5583 and ppe.surrogate_key=p_element_entry_id )
5584 )
5585 AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
5586 or
5587 ( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
5588 and exists
5589 ( select null
5590 from pay_element_entry_values_f
5591 where element_entry_id = p_element_entry_id
5592 and element_entry_value_id =
5593 ppe.surrogate_key ) )
5594 )
5595 )
5596 )
5597 AND pay_interpreter_pkg.valid_group_event_for_asg
5598 (pdt.table_name,
5599 p_assignment_id,
5600 ppe.surrogate_key) = 'Y'
5601 --Modified order by to include actual date
5602 ORDER BY 11, 5, 4, 10 desc;
5603
5604 CURSOR csr_get_pen_reports_exts(c_ext_dfn_id IN NUMBER)
5605 IS
5606 --SELECT 'x'
5607 SELECT nvl(decode(name,'PQP GB PenServer Periodic Changes Interface - Allowance History','A',
5608 'PQP GB PenServer Periodic Changes Interface - Bonus History','B',
5609 'N'),'N')
5610 FROM BEN_EXT_DFN
5611 WHERE ext_dfn_id = c_ext_dfn_id
5612 AND name in ('PQP GB PenServer Periodic Changes Interface - Allowance History',
5613 'PQP GB PenServer Periodic Changes Interface - Bonus History')
5614 AND legislation_code ='GB';
5615
5616 l_pen_ext_exists VARCHAR2(5);
5617 --bug 7443747:Stop
5618
5619 -- -- BUG 7525608 :start For Penserver
5620 -- Cursor to fetch the last approved date for each extract
5621
5622 CURSOR csr_pen_get_lapp_date
5623 IS
5624 SELECT least(run_strt_dt,eff_dt) app_date
5625 FROM ben_ext_rslt
5626 WHERE ext_dfn_id = ben_ext_thread.g_ext_dfn_id
5627 AND business_group_id = p_business_group_id
5628 AND ext_stat_cd = 'A'
5629 ORDER BY app_date DESC;
5630
5631 BEGIN
5632 hr_utility.set_location(l_proc, 10);
5633
5634 if (g_dbg) then
5635 hr_utility.trace('The Business Group id is '||p_business_group_id);
5636 hr_utility.trace('The p_assignment_id '||p_assignment_id);
5637 hr_utility.trace('The p_event_group_id '||p_event_group_id);
5638 hr_utility.trace('The g_leg_code is '||g_leg_code);
5639 hr_utility.trace('The p_mode is '||p_mode);
5640 hr_utility.trace('The p_element_entry_id is '||p_element_entry_id);
5641 hr_utility.trace('The p_start_date is '||to_char(p_start_date,'DD-MON-YYYY HH24:MI:SS'));
5642 hr_utility.trace('The p_end_date is '||to_char(p_end_date,'DD-MON-YYYY HH24:MI:SS'));
5643 end if;
5644
5645 l_date_counter := 1;
5646
5647 -- As part of looking for a dbt_i and dbt_p we create a cache
5648 -- This is a new call to the Interpreter so we destroy this cache
5649 g_key_date_cache.delete;
5650 g_upd_cache.delete; --bug 3598389
5651
5652 -- set up bus grp and leg code cache
5653 if (g_bus_grp_id is null or
5654 (g_bus_grp_id is not null and
5655 g_bus_grp_id <> p_business_group_id)) then
5656 select legislation_code
5657 into g_leg_code
5658 from per_business_groups_perf
5659 where business_group_id = p_business_group_id;
5660 --
5661 g_bus_grp_id := p_business_group_id;
5662 end if;
5663
5664 l_bulk_processing := 'N';
5665 if(p_global_env.datetrack_ee_tab_use) then
5666 l_bulk_processing := 'Y';
5667 end if;
5668
5669 -- >>> PHASE 1: Split copy of code in to two cursors based on
5670 -- master process mode to avoid performance issues.
5671 l_mst_pm := get_master_process_mode(p_process_mode);
5672
5673 -- NB. More notes in get_master_process_mode, but summary
5674 -- CRE = tune for mode, creation date
5675 -- EFF = tune for process, mode and effective date
5676 if(g_traces) then
5677 hr_utility.trace('The value of l_bulk_processing is '||l_bulk_processing);
5678 hr_utility.trace('MASTER PROCESS MODE '||l_mst_pm);
5679 hr_utility.trace('p_penserv_mode '||p_penserv_mode);
5680 end if;
5681 -- >>> PHASE 2: Get candidate rows from PPE
5682 --
5683 IF (l_mst_pm = 'CRE') THEN
5684 hr_utility.set_location(l_proc,20);
5685 --
5686 l_column_name.delete;
5687 l_event_type.delete;
5688 l_event_update_id.delete;
5689 l_effective_date.delete;
5690 l_assignment_id.delete;
5691 l_surrogate_key.delete;
5692 l_process_event_id.delete;
5693 l_description.delete;
5694 l_calculation_date.delete;
5695 l_creation_date.delete;
5696 l_change_type.delete;
5697 l_table_name.delete;
5698 --
5699 open csr_all_process_events_cre(
5700 l_bulk_processing,
5701 p_start_date ,
5702 p_end_date
5703 );
5704 --
5705 fetch csr_all_process_events_cre bulk collect into
5706 l_column_name,
5707 l_event_type,
5708 l_event_update_id,
5709 l_effective_date,
5710 l_assignment_id,
5711 l_surrogate_key,
5712 l_process_event_id,
5713 l_description,
5714 l_calculation_date,
5715 l_creation_date,
5716 l_change_type,
5717 l_table_name;
5718 --
5719 for i in 1..l_process_event_id.count loop
5720 --
5721 l_mst_pe_rec.updated_column_name := l_column_name(i);
5722 l_mst_pe_rec.event_type := l_event_type(i);
5723 l_mst_pe_rec.event_update_id := l_event_update_id(i);
5724 l_mst_pe_rec.effective_date := l_effective_date(i);
5728 l_mst_pe_rec.change_values := l_description(i);
5725 l_mst_pe_rec.assignment_id := l_assignment_id(i);
5726 l_mst_pe_rec.surrogate_key := l_surrogate_key(i);
5727 l_mst_pe_rec.process_event_id := l_process_event_id(i);
5729 l_mst_pe_rec.calculation_date := l_calculation_date(i);
5730 l_mst_pe_rec.creation_date := l_creation_date(i);
5731 l_mst_pe_rec.change_mode := l_change_type(i);
5732 l_mst_pe_rec.table_name := l_table_name(i);
5733 --
5734 analyse_disco_process_events
5735 (
5736 p_element_entry_id => p_element_entry_id,
5737 p_assignment_id => p_assignment_id,
5738 p_assignment_action_id => p_assignment_action_id,
5739 p_business_group_id => p_business_group_id,
5740 p_start_date => p_start_date,
5741 p_end_date => p_end_date,
5742 p_mode => p_mode,
5743 p_process => p_process,
5744 p_process_mode => p_process_mode,
5745 p_range_start => p_global_env.monitor_start_ptr,
5746 p_range_end => p_global_env.monitor_end_ptr,
5747 p_mst_pe_rec => l_mst_pe_rec,
5748 p_event_group_id => p_event_group_id,
5749 p_distinct_tab => glo_monitored_events,
5750 p_penserv_mode => p_penserv_mode,
5751 p_date_counter => l_date_counter,
5752 p_global_env => p_global_env,
5753 t_dynamic_sql => t_dynamic_sql,
5754 t_proration_dates_temp => t_proration_dates_temp,
5755 t_proration_change_type => t_proration_change_type,
5756 t_proration_type => t_proration_type,
5757 t_detailed_output => t_detailed_output
5758 );
5759
5760 end loop; --Get next process event to do comparisons on
5761 close csr_all_process_events_cre;
5762
5763
5764 ELSIF (l_mst_pm = 'EFF') THEN
5765 --
5766 hr_utility.set_location(l_proc,320);
5767 --
5768 l_column_name.delete;
5769 l_event_type.delete;
5770 l_event_update_id.delete;
5771 l_effective_date.delete;
5772 l_assignment_id.delete;
5773 l_surrogate_key.delete;
5774 l_process_event_id.delete;
5775 l_description.delete;
5776 l_calculation_date.delete;
5777 l_creation_date.delete;
5778 l_change_type.delete;
5779 l_table_name.delete;
5780 --
5781 if (p_penserv_mode = 'N') then
5782 open csr_all_process_events_eff(
5783 l_bulk_processing,
5784 p_start_date ,
5785 p_end_date
5786 );
5787 --
5788 fetch csr_all_process_events_eff bulk collect into
5789 l_column_name,
5790 l_event_type,
5791 l_event_update_id,
5792 l_effective_date,
5793 l_assignment_id,
5794 l_surrogate_key,
5795 l_process_event_id,
5796 l_description,
5797 l_calculation_date,
5798 l_creation_date,
5799 l_change_type,
5800 l_table_name;
5801 --
5802 else
5803
5804 -- BUG 7525608 :start
5805 -- Fetch the actual p_start_date for each assignment instead of the
5806 -- start date passed by penserver program
5807
5808 IF g_pen_lapp_date is null
5809 THEN
5810 OPEN csr_pen_get_lapp_date;
5811 FETCH csr_pen_get_lapp_date INTO g_pen_lapp_date;
5812 CLOSE csr_pen_get_lapp_date;
5813 hr_utility.trace('-------g_pen_lapp_date----------');
5814 hr_utility.trace('g_pen_lapp_date :'||to_char(g_pen_lapp_date,'DD-MON-YYYY HH24:MI:SS'));
5815 END IF;
5816
5817 -- Get the Actual Start Date for Each assignment
5818 IF g_pen_lapp_date is not null
5819 THEN
5820 IF p_assignment_id <> g_pen_prev_ass_id
5821 THEN
5822 g_pen_from_date := get_penserver_date(p_assignment_id,p_business_group_id,trunc(g_pen_lapp_date),p_end_date); -- For bug 8359083
5823 g_pen_prev_ass_id := p_assignment_id;
5824 hr_utility.trace('-------p_assignment_id----------');
5825 hr_utility.trace('p_assignment_id :'||p_assignment_id);
5826 END IF;
5827 ELSE
5828 g_pen_from_date := p_start_date;
5829 END IF;
5830 hr_utility.trace('-------g_pen_prev_ass_id----------');
5831 hr_utility.trace('g_pen_prev_ass_id :'||g_pen_prev_ass_id);
5832 hr_utility.trace('g_pen_from_date :'||g_pen_from_date);
5833
5834 -- BUG 7525608 :End
5835
5836 --bug 7443747:Start
5837 hr_utility.trace('ben_ext_thread.g_ext_dfn_id '||ben_ext_thread.g_ext_dfn_id);
5838 hr_utility.trace('g_pen_collect_reports '||g_pen_collect_reports);
5839
5840 IF g_pen_collect_reports is null
5841 THEN
5842 OPEN csr_get_pen_reports_exts(ben_ext_thread.g_ext_dfn_id);
5843 FETCH csr_get_pen_reports_exts INTO g_pen_collect_reports;
5844
5845 IF csr_get_pen_reports_exts%notfound
5846 THEN
5847 g_pen_collect_reports := 'N';
5848 END IF;
5849
5850 CLOSE csr_get_pen_reports_exts;
5851
5852 END IF;
5853 hr_utility.trace('g_pen_collect_reports '||g_pen_collect_reports);
5854 IF g_pen_collect_reports = 'A'
5855 THEN
5856 open csr_all_proces_eve_eff_pen_rep(
5857 l_bulk_processing,
5858 g_pen_from_date , -- Replaced the date p_start_date for bug 7525608
5859 p_end_date
5860 );
5861 --
5862 fetch csr_all_proces_eve_eff_pen_rep bulk collect into
5863 l_column_name,
5864 l_event_type,
5865 l_event_update_id,
5866 l_effective_date,
5867 l_assignment_id,
5868 l_surrogate_key,
5869 l_process_event_id,
5870 l_description,
5871 l_calculation_date,
5872 l_creation_date,
5873 l_change_type,
5874 l_table_name;
5875
5876 -- call bonus cursor if the extract is bonus
5877 -- Bug 10092118
5878 ELSIF g_pen_collect_reports = 'B'
5879 THEN
5880 open csr_bon_proces_eve_eff_pen_rep(
5881 l_bulk_processing,
5882 g_pen_from_date , -- Replaced the date p_start_date for bug 7525608
5883 p_end_date,
5884 g_pen_lapp_date
5885 );
5886 --
5887 fetch csr_bon_proces_eve_eff_pen_rep bulk collect into
5888 l_column_name,
5889 l_event_type,
5890 l_event_update_id,
5891 l_effective_date,
5892 l_assignment_id,
5893 l_surrogate_key,
5894 l_process_event_id,
5895 l_description,
5896 l_calculation_date,
5897 l_creation_date,
5898 l_change_type,
5899 l_table_name;
5900 ELSE
5901 --bug 7443747:Stop
5902 open csr_all_process_events_eff_pen(
5903 l_bulk_processing,
5904 g_pen_from_date , -- Replaced the date p_start_date for bug 7525608
5905 p_end_date
5906 );
5907 --
5908 fetch csr_all_process_events_eff_pen bulk collect into
5909 l_column_name,
5910 l_event_type,
5911 l_event_update_id,
5912 l_effective_date,
5913 l_assignment_id,
5914 l_surrogate_key,
5915 l_process_event_id,
5916 l_description,
5917 l_calculation_date,
5918 l_creation_date,
5919 l_change_type,
5920 l_table_name;
5921
5922 --bug 7443747:Start
5923 END IF;
5924 --bug 7443747:Stop
5925
5926 end if;
5927
5928 for i in 1..l_process_event_id.count loop
5929 --
5930 l_mst_pe_rec.updated_column_name := l_column_name(i);
5931 l_mst_pe_rec.event_type := l_event_type(i);
5932 l_mst_pe_rec.event_update_id := l_event_update_id(i);
5933 l_mst_pe_rec.effective_date := l_effective_date(i);
5934 l_mst_pe_rec.assignment_id := l_assignment_id(i);
5935 l_mst_pe_rec.surrogate_key := l_surrogate_key(i);
5936 l_mst_pe_rec.process_event_id := l_process_event_id(i);
5937 l_mst_pe_rec.change_values := l_description(i);
5938 l_mst_pe_rec.calculation_date := l_calculation_date(i);
5939 l_mst_pe_rec.creation_date := l_creation_date(i);
5940 l_mst_pe_rec.change_mode := l_change_type(i);
5941 l_mst_pe_rec.table_name := l_table_name(i);
5942 --
5943
5944 IF (p_penserv_mode = 'N')
5945 THEN
5946
5947 analyse_disco_process_events
5948 (
5949 p_element_entry_id => p_element_entry_id,
5950 p_assignment_id => p_assignment_id,
5951 p_assignment_action_id => p_assignment_action_id,
5952 p_business_group_id => p_business_group_id,
5953 p_start_date => p_start_date,
5954 p_end_date => p_end_date,
5955 p_mode => p_mode,
5956 p_process => p_process,
5957 p_process_mode => p_process_mode,
5958 p_range_start => p_global_env.monitor_start_ptr,
5959 p_range_end => p_global_env.monitor_end_ptr,
5960 p_mst_pe_rec => l_mst_pe_rec,
5961 p_event_group_id => p_event_group_id,
5962 p_distinct_tab => glo_monitored_events,
5963 p_penserv_mode => p_penserv_mode,
5964 p_date_counter => l_date_counter,
5965 p_global_env => p_global_env,
5966 t_dynamic_sql => t_dynamic_sql,
5967 t_proration_dates_temp => t_proration_dates_temp,
5968 t_proration_change_type => t_proration_change_type,
5969 t_proration_type => t_proration_type,
5970 t_detailed_output => t_detailed_output
5971 );
5972
5973 ELSE
5974
5975 analyse_disco_process_events
5976 (
5977 p_element_entry_id => p_element_entry_id,
5978 p_assignment_id => p_assignment_id,
5979 p_assignment_action_id => p_assignment_action_id,
5980 p_business_group_id => p_business_group_id,
5981 p_start_date => g_pen_from_date , -- Replaced the date p_start_date for bug 7525608
5982 p_end_date => p_end_date,
5983 p_mode => p_mode,
5984 p_process => p_process,
5985 p_process_mode => p_process_mode,
5986 p_range_start => p_global_env.monitor_start_ptr,
5987 p_range_end => p_global_env.monitor_end_ptr,
5988 p_mst_pe_rec => l_mst_pe_rec,
5989 p_event_group_id => p_event_group_id,
5990 p_distinct_tab => glo_monitored_events,
5991 p_penserv_mode => p_penserv_mode,
5992 p_date_counter => l_date_counter,
5993 p_global_env => p_global_env,
5994 t_dynamic_sql => t_dynamic_sql,
5995 t_proration_dates_temp => t_proration_dates_temp,
5996 t_proration_change_type => t_proration_change_type,
5997 t_proration_type => t_proration_type,
5998 t_detailed_output => t_detailed_output
5999 );
6000
6001 END IF;
6002
6003 --
6004 end loop; --Get next process event to do comparisons on
6005 if (p_penserv_mode = 'N') then
6006 close csr_all_process_events_eff;
6007 else
6008 --bug 7443747:Start
6009 IF g_pen_collect_reports = 'A'
6010 THEN
6011 close csr_all_proces_eve_eff_pen_rep;
6012 ELSIF g_pen_collect_reports = 'B'
6013 THEN
6014 close csr_bon_proces_eve_eff_pen_rep; -- Bug 10092118
6015 ELSE
6016 --bug 7443747:Stop
6017 close csr_all_process_events_eff_pen;
6018 --bug 7443747:Start
6019 END IF;
6020 --bug 7443747:Stop
6021 end if;
6022
6023 END IF; --END MAIN IF CRE OR EFF
6024
6025
6026 hr_utility.set_location(l_proc, 900);
6027 END record_all_disco_events;
6028
6029
6030
6031 --------------------------
6032 --------------------------
6033
6034 /* ----------------------------------------------------------
6035 Main Entrance procedure, get info of datetracked event we're looking for
6036 then call procedure for this type
6037 I - Ins event
6038 U - Update event
6039 E - End date event
6040 P - Purge event
6041 C - Correction event
6042 DF - Delete Future (Equals both Delete Next and Future Changes)
6043 ---------------------------------------------------------- */
6044 PROCEDURE event_group_tables_affected
6045 (
6046 p_element_entry_id IN NUMBER DEFAULT NULL,
6047 p_assignment_action_id IN NUMBER,
6048 p_event_group_id IN NUMBER,
6049 p_assignment_id IN NUMBER,
6050 p_business_group_id IN NUMBER,
6051 p_start_date IN DATE,
6052 p_end_date IN DATE,
6053 p_mode IN VARCHAR2,
6054 p_process IN VARCHAR2,
6055 p_process_mode IN VARCHAR2,
6056 p_global_env IN OUT NOCOPY t_global_env_rec,
6057 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
6058 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
6059 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
6060 t_proration_type IN OUT NOCOPY t_proration_type_table_type,
6061 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
6062 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
6063 ) AS
6064 --Misc helper/counters
6065 l_date_counter NUMBER ;
6066 l_range_start NUMBER;
6067 l_range_end NUMBER;
6068 l_dyt_type VARCHAR2(15); --Holds wot type of dyt table has
6069
6070 l_proc VARCHAR2(80) := 'event_group_tables_affected';
6071
6072
6073 BEGIN
6074 hr_utility.set_location(l_proc, 10);
6075 if (g_traces) then
6076 hr_utility.trace('Event group ID '||p_event_group_id||
6077 ' number of events in t_proration_group_tab: '||t_proration_group_tab.COUNT);
6078 end if;
6079
6080
6081 --For each Table that features in the clients event group we need
6082 --to gather all the potential events from ppe, then compare each potential
6083 --to the actual reqd events and add them to our store of Happened events
6084 --all this code is within record_disco_events
6085
6086 -- EG
6087 -- PROCESS EVENTS
6088 -- pay_process_events are all events that have occurred
6089 -- DATETRACKED EVENTS
6090 -- All events the user has declared they are interested in,
6091 -- ie child of event group
6092 -- DISCO(vered) EVENTS
6093 -- Matched PROCESS EVENTS with DATETRACKED EVENTS, eg this list is
6094 -- the whole point of interpreter
6095
6096 -- Call this for each table in event group
6097 record_all_disco_events
6098 (
6099 p_event_group_id => p_event_group_id,
6100
6101 p_element_entry_id => p_element_entry_id,
6102 p_assignment_id => p_assignment_id,
6103 p_assignment_action_id => p_assignment_action_id,
6104 p_business_group_id => p_business_group_id,
6105 p_start_date => p_start_date,
6106 p_end_date => p_end_date,
6107 p_process => p_process,
6108 p_mode => p_mode,
6109 p_process_mode => p_process_mode,
6110 p_global_env => p_global_env,
6111 t_dynamic_sql => t_dynamic_sql,
6112 t_proration_dates_temp => t_proration_dates_temp,
6113 t_proration_change_type => t_proration_change_type,
6114 t_proration_type => t_proration_type,
6115 t_detailed_output => t_detailed_output,
6116 p_penserv_mode => p_penserv_mode
6117 );
6118
6119 hr_utility.set_location(l_proc, 900);
6120 END event_group_tables_affected;
6121
6122
6123 PROCEDURE event_group_tables_affected
6124 (
6125 p_element_entry_id IN NUMBER DEFAULT NULL ,
6126 p_assignment_action_id IN NUMBER,
6127 p_event_group_id IN NUMBER,
6128 p_assignment_id IN NUMBER,
6129 p_business_group_id IN NUMBER,
6130 p_start_date IN DATE,
6131 p_end_date IN DATE,
6132 p_mode IN VARCHAR2,
6133 p_process IN VARCHAR2,
6134 p_process_mode IN VARCHAR2,
6135 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
6136 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
6137 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
6138 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
6139 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
6140 ) is
6141 t_proration_type t_proration_type_table_type;
6142 l_global_env t_global_env_rec;
6143 begin
6144 initialise_global(l_global_env);
6145 --
6146 glo_monitored_events := t_distinct_tab;
6147 l_global_env.monitor_start_ptr :=
6148 t_proration_group_tab(p_event_group_id).range_start;
6149 l_global_env.monitor_end_ptr :=
6150 t_proration_group_tab(p_event_group_id).range_end;
6151 --
6152
6153 event_group_tables_affected
6154 (
6155 p_element_entry_id => p_element_entry_id,
6156 p_assignment_action_id => p_assignment_action_id,
6157 p_event_group_id => p_event_group_id,
6158 p_assignment_id => p_assignment_id,
6159 p_business_group_id => p_business_group_id,
6160 p_start_date => p_start_date,
6161 p_end_date => p_end_date,
6162 p_mode => p_mode,
6163 p_process => p_process,
6164 p_process_mode => p_process_mode,
6165 p_global_env => l_global_env,
6166 t_dynamic_sql => t_dynamic_sql,
6167 t_proration_dates_temp => t_proration_dates_temp,
6168 t_proration_change_type => t_proration_change_type,
6169 t_proration_type => t_proration_type,
6170 t_detailed_output => t_detailed_output,
6171 p_penserv_mode => p_penserv_mode
6172 );
6173
6174 end;
6175
6176 /*
6177 NAME
6178 validate_entry_parameters
6179
6180 DESCRIPTION
6181 Validate all the parameters supplied to entry_affected
6182 */
6183 procedure validate_entry_parameters ( p_assignment_action_id IN NUMBER,
6184 p_assignment_id IN NUMBER,
6185 p_mode IN VARCHAR2,
6186 p_process IN VARCHAR2,
6187 p_event_group_id IN NUMBER,
6188 p_process_mode IN VARCHAR2,
6189 p_start_date IN DATE,
6190 p_end_date IN DATE,
6191 p_outprocess_mode OUT NOCOPY VARCHAR2
6192 )
6193 is
6194 begin
6195 --
6196 -- Ensure we have either an assignment or and action.
6197 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:1',
6198 ( p_assignment_action_id is not null
6199 or p_assignment_id is not null));
6200 --
6201 -- Ensure the mode is correct
6202 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:2',
6203 (p_mode in ('COST_CENTRE',
6204 'DATE_EARNED',
6205 'DATE_PROCESSED',
6206 'PAYMENT',
6207 'REPORTS')
6208 or p_mode is null));
6209 --
6210 -- Ensure the status is correct
6211 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:3',
6212 (p_process in ('U',
6213 'P',
6214 'C')
6215 or p_process is null));
6216 --
6217 -- Ensure the processing mode is correct
6218 pay_core_utils.assert_condition('pay_interpreter_pkg.validate_entry_parameters:4',
6219 (p_process_mode in ('ENTRY_EFFECTIVE_DATE',
6220 'ENTRY_RETROSTATUS',
6221 'ENTRY_CREATION_DATE',
6222 'PRORATION')
6223 ));
6224 --
6225 if (p_process_mode = 'PRORATION') then
6226 --
6227 p_outprocess_mode := 'ENTRY_EFFECTIVE_DATE';
6228 --
6229 else
6230 --
6231 p_outprocess_mode := p_process_mode;
6232 --
6233 end if;
6234 --
6235 end validate_entry_parameters;
6236
6237 --This is called directly by ADV_RETRONOT and CONT CALC
6238 --also called by historic overloaded entry points
6239 --This is an exact copy of the logic in the original entry_affected
6240 --procedure, except having the three additional date parameters, thus existing code
6241 --will just call the original which in turn calls this overloaded definition
6242 PROCEDURE entry_affected
6243 (
6244 p_element_entry_id IN NUMBER DEFAULT NULL ,
6245 p_assignment_action_id IN NUMBER DEFAULT NULL ,
6246 p_assignment_id IN NUMBER DEFAULT NULL ,
6250 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE' ,
6247 p_mode IN VARCHAR2 DEFAULT NULL ,
6248 p_process IN VARCHAR2 DEFAULT NULL ,
6249 p_event_group_id IN NUMBER DEFAULT NULL ,
6251 p_start_date IN DATE DEFAULT hr_api.g_sot, --events created since this date
6252 p_end_date IN DATE DEFAULT hr_api.g_eot, --events created until this date
6253 p_process_date IN DATE DEFAULT SYSDATE, -- This date, drives for getting
6254 -- a dflt event grop id if one is not passed
6255 p_unique_sort IN VARCHAR2, --default 'Y', --quicker if N
6256 p_business_group_id IN NUMBER, --default null, in case someones wrapper needs it
6257 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
6258 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
6259 t_proration_change_type OUT NOCOPY t_proration_type_table_type,
6260 t_proration_type OUT NOCOPY t_proration_type_table_type,
6261 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
6262 ) AS
6263
6264 cursor csr_dflt_grps (cp_ee_id in number, cp_report_date in date ) IS
6265 select distinct(et.recalc_event_group_id) recalc_event_group_id
6266 from
6267 pay_element_entries_f ee
6268 ,pay_element_links_f el
6269 ,pay_element_types_f et
6270 where ee.element_entry_id = nvl(cp_ee_id,-1)
6271 and ee.element_link_id = el.element_link_id
6272 and el.element_type_id = et.element_type_id
6273 and cp_report_date between et.effective_start_date
6274 and et.effective_end_date;
6275
6276 -- Local variables declaration.
6277
6278 l_assignment_id per_all_assignments_f.assignment_id%TYPE ;
6279 l_event_group_id NUMBER ;
6280 l_business_group_id NUMBER ;
6281 l_start_date DATE := p_start_date ;
6282 l_end_date DATE := p_end_date ;
6283 t_dynamic_sql t_dynamic_sql_tab ;
6284 t_proration_dates_temp t_proration_dates_table_type ;
6285 t_proration_change_type_temp t_proration_type_table_type;
6286 t_proration_type_temp t_proration_type_table_type;
6287 l_internal_mode varchar2(30);
6288 l_process_mode varchar2(30);
6289 l_global_env t_global_env_rec;
6290
6291 l_proc VARCHAR2(80) := 'entry_affected';
6292
6293 BEGIN
6294 g_traces := hr_utility.debug_enabled ;
6295
6296 if (g_traces) then
6297 hr_utility.trace('+------ ENTERED INTERPRETER ----+');
6298 hr_utility.trace('| Assignment Id ' || p_assignment_id);
6299 hr_utility.trace('| Assignment Act Id ' || p_assignment_action_id);
6300 hr_utility.trace('| Element Entry Id ' || TO_CHAR(p_element_entry_id));
6301 hr_utility.trace('| Event Group Id ' || p_event_group_id);
6302 end if;
6303
6304 if (g_dbg) then
6305 hr_utility.trace('| P_mode ' || p_mode);
6306 hr_utility.trace('| P_process ' || p_process);
6307 hr_utility.trace('| P_process_mode ' || p_process_mode);
6308 hr_utility.trace('+-------------------------------+ ');
6309 end if;
6310 --
6311 -- were either passed in and ee_id,event_group_id ,assignment_id ,date, mode
6312 -- or a ee_id and assign_act_id.
6313 -- if its the second then we need to calc the assginemt_id, and time periods
6314 -- ENTRY_CREATION_DATE is a mode where dates are past in,
6315 -- Continuous Calculation is an example of this process mode
6316 validate_entry_parameters (p_assignment_action_id => p_assignment_action_id,
6317 p_assignment_id => p_assignment_id,
6318 p_mode => p_mode,
6319 p_process => p_process,
6320 p_event_group_id => p_event_group_id,
6321 p_process_mode => p_process_mode,
6322 p_start_date => p_start_date,
6323 p_end_date => p_end_date,
6324 p_outprocess_mode => l_process_mode
6325 );
6326 --
6327
6328 -- Empty all results, this stops accidental
6329 -- results in calling code and massive memory overheads
6330 -- Please remember NOT to pass in partial tables of results
6331 t_dynamic_sql.delete;
6332 t_proration_dates_temp.delete;
6333 t_proration_change_type_temp.delete;
6334 t_proration_type_temp.delete;
6335
6336
6337 IF p_process_mode = 'PRORATION' THEN
6338 event_group_info(p_assignment_action_id,
6339 p_element_entry_id,
6340 l_event_group_id,
6341 l_assignment_id,
6342 l_business_group_id,
6343 l_start_date,
6344 l_end_date);
6345 l_internal_mode := 'PRORATION';
6346 ELSE
6347
6348 -- if no business group id is passed then get from cache,
6349 -- if not in cache either then hit the db for it and store in cache
6350 if (p_business_group_id is null) then
6351 if (g_business_group_id is null) then
6352 --cache empty so get now
6353 select max(business_group_id)
6354 into l_business_group_id
6355 from per_all_assignments_f
6356 where assignment_id = p_assignment_id;
6357
6358 -- There is one exceptional circumstance where bg_id is null
6359 -- specifically, a purge of an asg from per_all_assignments_f
6360 -- if we care that its been purged then weve caught the event so
6361 -- workaround is to get the bg from ppe
6362 --
6363 if ( l_business_group_id is null) then
6364 select max(business_group_id)
6365 into l_business_group_id
6366 from pay_process_events
6367 where assignment_id = p_assignment_id;
6368 end if;
6369
6370
6371 if (g_dbg) then
6372 hr_utility.trace('BG ID was null, now ' ||l_business_group_id);
6373 end if;
6374
6375 -- set cache value
6376 g_business_group_id := l_business_group_id;
6377 else
6378 -- Use the cached value
6379 l_business_group_id := g_business_group_id;
6380 end if;
6381 else
6382 --use the parameter version and set global
6383 l_business_group_id := p_business_group_id;
6384 g_business_group_id := l_business_group_id;
6385
6386 end if;
6387
6388 l_assignment_id:=p_assignment_id;
6389 l_event_group_id:=p_event_group_id;
6390 l_internal_mode := 'RECALCULATION';
6391 END IF;
6392
6393 hr_utility.set_location(l_proc, 20);
6394 -- If event group id has not been passed then check to see if there is
6395 -- a default event group for this element type
6396 --
6397 if (l_event_group_id is null) then
6398 --
6399 -- If we are Prorating then there isn't a Proration group, hence
6400 -- just leave the procedure
6401 --
6402 if (l_internal_mode = 'PRORATION') then
6403 --
6404 return;
6405 --
6406 else
6407 for dflt_ev_grp in csr_dflt_grps(p_element_entry_id,p_process_date) loop
6408 --just one row, but fetch neatly
6409 l_event_group_id := dflt_ev_grp.recalc_event_group_id;
6410 end loop;
6411 if (g_traces) then
6412 hr_utility.trace(' Event Group ID from element type dflt: '||l_event_group_id);
6413 end if;
6414
6415 -- if we still have no event group just bug out
6416 if (l_event_group_id is null) then
6417 if (g_traces) then
6418 hr_utility.trace('>>> No event group => return null from interpreter');
6419 end if;
6420 return;
6421 end if;
6422 end if;
6423
6424
6425 end if;
6426
6427 event_group_tables(l_event_group_id);
6428 --
6429 -- Setup the global structure
6430 --
6431 initialise_global(l_global_env);
6432 glo_monitored_events := t_distinct_tab;
6433 l_global_env.monitor_start_ptr :=
6434 t_proration_group_tab(l_event_group_id).range_start;
6435 l_global_env.monitor_end_ptr :=
6436 t_proration_group_tab(l_event_group_id).range_end;
6437 --
6438 hr_utility.set_location(l_proc, 30);
6439
6440 event_group_tables_affected( p_element_entry_id,
6441 p_assignment_action_id,
6442 l_event_group_id,
6443 l_assignment_id,
6444 l_business_group_id,
6445 l_start_date,
6446 l_end_date,
6447 p_mode,
6448 p_process,
6449 l_process_mode,
6450 l_global_env,
6451 t_dynamic_sql,
6452 t_proration_dates_temp,
6453 t_proration_change_type_temp,
6454 t_proration_type_temp,
6455 t_detailed_output,
6456 p_penserv_mode);
6457
6458 hr_utility.set_location(l_proc, 40);
6459
6460 --Only perform the sort if calling procedure has requested it
6461 --NB detailed output results table is never sorted
6462 if (p_unique_sort = 'Y') then
6463 -- This procedure sorts the dates and then generate the listing of unique dates.
6464 unique_sort(p_proration_dates_temp => t_proration_dates_temp ,
6465 p_proration_dates => t_proration_dates ,
6466 p_change_type_temp => t_proration_change_type_temp,
6467 p_proration_type_temp => t_proration_type_temp,
6468 p_change_type => t_proration_change_type,
6469 p_proration_type => t_proration_type,
6470 p_internal_mode => l_internal_mode );
6471
6472 elsif (l_internal_mode = 'PRORATION') then
6473 --
6474 t_proration_type := t_proration_type_temp;
6475 t_proration_dates := t_proration_dates_temp;
6476 t_proration_change_type := t_proration_change_type_temp;
6477 --
6478 end if;
6479
6480 hr_utility.set_location(l_proc, 900);
6481 END entry_affected;
6482
6483 PROCEDURE get_subset_given_new_evg
6484 (
6485 p_filter_event_group_id IN NUMBER ,
6486 p_complete_detail_tab IN t_detailed_output_table_type ,
6487 p_subset_detail_tab IN OUT NOCOPY t_detailed_output_table_type
6488 ) AS
6489
6490 CURSOR csr_reqd_events is
6491 SELECT DISTINCT pdt.dated_table_id table_id ,
6492 pdt.table_name table_name ,
6493 pde.column_name column_name ,
6494 pde.update_type update_type
6495 FROM pay_datetracked_events pde,
6496 pay_dated_tables pdt
6497 WHERE pde.event_group_id = p_filter_event_group_id
6498 AND pdt.dated_table_id = pde.dated_table_id
6499 order by pdt.dated_table_id;
6500
6501
6502 l_proc VARCHAR2(80) := 'get_subset_given_new_evg';
6503
6504 k number := 1;
6505 BEGIN
6506 g_traces := hr_utility.debug_enabled ;
6507
6508 if (g_traces) then
6509 hr_utility.set_location(l_proc,10);
6510 hr_utility.trace('| Filter full results using new Event Group Id: ' ||
6511 p_filter_event_group_id);
6512 end if;
6513
6514 -- For each given event, look for the event in our new event group
6515 -- Match the table, column and update type from the complete details tab
6516
6517 -- Loop through the required events
6518 for reqd_event_rec in csr_reqd_events loop
6519
6520 if (g_dbg) then
6521 hr_utility.trace('Looking for '||reqd_event_rec.update_type||' on '||
6522 reqd_event_rec.table_name||'.'||reqd_event_rec.column_name);
6523 end if;
6524
6525 -- Loop through all the full table events
6526 for j in 1..p_complete_detail_tab.count loop
6527
6528 --First check type and table match
6529 if (reqd_event_rec.table_id = p_complete_detail_tab(j).dated_table_id
6530 and reqd_event_rec.update_type = p_complete_detail_tab(j).update_type)
6531 then
6532
6533 -- Second, Check that if we care, the column is also the same
6534 if ( reqd_event_rec.update_type not in ('U','C') )
6535 then
6536 --dont care about col - Found a match, add this event to results
6537 p_subset_detail_tab(k) := p_complete_detail_tab(j);
6538 k := k + 1;
6539 elsif
6540 (nvl(reqd_event_rec.column_name,'X') = nvl(p_complete_detail_tab(j).column_name,'X') )
6541 then
6542 --do care about col - Found a match, add this event to results
6543 p_subset_detail_tab(k) := p_complete_detail_tab(j);
6544 k := k + 1;
6545 end if;
6546
6547 end if;
6548
6549 end loop; --Get next event in full detail table
6550
6551 end loop; --Get next reqd event from the filter event group
6552
6553 if (g_traces) then
6554 hr_utility.trace('| p_complete_details_tab contained: '
6555 ||p_complete_detail_tab.count() );
6556 hr_utility.trace('| p_subset_detail_tab contains: '
6557 ||p_subset_detail_tab.count() );
6558 hr_utility.set_location(l_proc,900);
6559 end if;
6560 END get_subset_given_new_evg;
6561
6562
6563 PROCEDURE entries_affected
6564 (
6565 p_assignment_id IN NUMBER DEFAULT NULL ,
6566 p_mode IN VARCHAR2 DEFAULT NULL ,
6567 p_start_date IN DATE DEFAULT hr_api.g_sot,
6568 p_end_date IN DATE DEFAULT hr_api.g_eot,
6569 p_business_group_id IN NUMBER,
6570 p_global_env IN OUT NOCOPY t_global_env_rec,
6571 t_detailed_output OUT NOCOPY t_detailed_output_table_type,
6572 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE',
6573 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
6574 ) AS
6575
6576 -- Local variables declaration.
6577
6578 l_assignment_id per_all_assignments_f.assignment_id%TYPE ;
6579 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE ;
6580 t_dynamic_sql t_dynamic_sql_tab ;
6581 t_proration_dates_temp t_proration_dates_table_type ;
6582 t_proration_change_type_temp t_proration_type_table_type;
6583 t_proration_type_temp t_proration_type_table_type;
6584 l_internal_mode varchar2(30);
6585 l_process_mode varchar2(30);
6586 l_processout_mode varchar2(30);
6587
6588 l_proc VARCHAR2(80) := 'entries_affected';
6589
6590 BEGIN
6591 g_traces := hr_utility.debug_enabled ;
6592
6593 if (g_traces) then
6594 hr_utility.trace('+------ ENTERED INTERPRETER ----+');
6595 hr_utility.trace('| Assignment Id ' || p_assignment_id);
6596 end if;
6597
6598 if (g_dbg) then
6599 hr_utility.trace('| P_mode ' || p_mode);
6600 hr_utility.trace('+-------------------------------+ ');
6601 end if;
6602 --
6603 -- Setup the global structure
6604 --
6605 l_process_mode := p_process_mode;
6606 --
6607 validate_entry_parameters (p_assignment_action_id => null,
6608 p_assignment_id => p_assignment_id,
6609 p_mode => p_mode,
6610 p_process => null,
6611 p_event_group_id => null,
6612 p_process_mode => l_process_mode,
6613 p_start_date => p_start_date,
6614 p_end_date => p_end_date,
6615 p_outprocess_mode => l_processout_mode
6616 );
6617 --
6618 -- For Penserver bug 7829985
6619 IF p_penserv_mode = 'N'
6620 THEN
6621
6622 select assignment_action_id
6623 into l_assignment_action_id
6624 from pay_assignment_actions
6625 where assignment_id = p_assignment_id
6626 and rownum = 1;
6627
6628 END IF;
6629 --
6630
6631
6632 -- Empty all results, this stops accidental
6633 -- results in calling code and massive memory overheads
6634 -- Please remember NOT to pass in partial tables of results
6635 t_dynamic_sql.delete;
6636 t_detailed_output.delete;
6637 t_proration_dates_temp.delete;
6638 t_proration_change_type_temp.delete;
6639 t_proration_type_temp.delete;
6640
6641
6642 l_assignment_id:=p_assignment_id;
6643 l_internal_mode := 'RECALCULATION';
6644
6645 hr_utility.set_location(l_proc, 20);
6646
6647 event_group_tables_affected( null,
6648 l_assignment_action_id,
6649 null,
6650 l_assignment_id,
6651 p_business_group_id,
6652 p_start_date,
6653 p_end_date,
6654 p_mode,
6655 null,
6656 l_processout_mode,
6657 p_global_env,
6658 t_dynamic_sql,
6659 t_proration_dates_temp,
6660 t_proration_change_type_temp,
6661 t_proration_type_temp,
6662 t_detailed_output,
6663 p_penserv_mode);
6664
6665 hr_utility.set_location(l_proc, 40);
6666
6667 hr_utility.set_location(l_proc, 900);
6668 END entries_affected;
6669
6670
6671 begin
6672 g_valact_rec.assignment_id := '-1';
6673 g_grd_assignment_id := -1;
6674 END PAY_INTERPRETER_PKG;
[Home] [Help]