1 PACKAGE BODY hri_opl_wmv AS
2 /* $Header: hriowmv.pkb 120.0 2005/05/29 06:56:21 appldev noship $ */
3
4 /* Simple table types */
5 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
6 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8
9 /* PL/SQL table of tables representing database table */
10 g_fte_value_tab g_number_tab_type;
11 g_head_value_tab g_number_tab_type;
12 g_start_date_tab g_date_tab_type;
13 g_asg_id_tab g_number_tab_type;
14 g_bgr_id_tab g_number_tab_type;
15 g_psn_id_tab g_number_tab_type;
16 g_ast_id_tab g_number_tab_type;
17 g_per_sys_stat_tab g_varchar2_tab_type;
18 g_pay_sys_stat_tab g_varchar2_tab_type;
19 g_pos_id_tab g_number_tab_type;
20 g_primary_flag_tab g_varchar2_tab_type;
21 g_last_chng_tab g_date_tab_type;
22 g_final_proc_tab g_date_tab_type;
23
24 /* Null tables - to use in initialization */
25 g_null_number_tab g_number_tab_type;
26 g_null_varchar2_tab g_varchar2_tab_type;
27 g_null_date_tab g_date_tab_type;
28
29 /* Global variables representing parameters passed */
30 g_collect_from_date DATE;
31 g_collect_to_date DATE;
32 g_collect_fte VARCHAR2(5);
33 g_collect_head VARCHAR2(5);
34 g_full_refresh VARCHAR2(5);
35
36 /* Global end of time date */
37 g_end_of_time DATE := hr_general.end_of_time;
38
39 /* Stores number of rows inserted into the PL/SQL global table structure */
40 g_rows_inserted PLS_INTEGER;
41
42 /******************************************************************************/
43 /* Inserts row into concurrent program log */
44 /******************************************************************************/
45 PROCEDURE output(p_text VARCHAR2) IS
46
47 BEGIN
48
49 /* Write to the concurrent request log */
50 fnd_file.put_line(fnd_file.log, p_text);
51
52 END output;
53
54 /******************************************************************************/
55 /* Initializes global table */
56 /******************************************************************************/
57 PROCEDURE init_global_table IS
58
59 BEGIN
60
61 /* Assign the corresponding null table to each of the global tables */
62 g_fte_value_tab := g_null_number_tab;
63 g_head_value_tab := g_null_number_tab;
64 g_start_date_tab := g_null_date_tab;
65 g_asg_id_tab := g_null_number_tab;
66 g_bgr_id_tab := g_null_number_tab;
67 g_psn_id_tab := g_null_number_tab;
68 g_ast_id_tab := g_null_number_tab;
69 g_per_sys_stat_tab := g_null_varchar2_tab;
70 g_pay_sys_stat_tab := g_null_varchar2_tab;
71 g_pos_id_tab := g_null_number_tab;
72 g_primary_flag_tab := g_null_varchar2_tab;
73 g_last_chng_tab := g_null_date_tab;
74
75 END init_global_table;
76
77 /******************************************************************************/
78 /* Sets up global list of parameters */
79 /******************************************************************************/
80 PROCEDURE get_parameters( p_payroll_action_id IN NUMBER ) IS
81
82 BEGIN
83
84 /* If parameters haven't already been set, then set them */
85 IF (g_collect_from_date IS NULL) THEN
86
87 SELECT
88 ppa.start_date
89 ,ppa.effective_date
90 ,SUBSTR(ppa.legislative_parameters,1,1)
91 ,SUBSTR(ppa.legislative_parameters,3,1)
92 ,SUBSTR(ppa.legislative_parameters,5,1)
93 INTO
94 g_collect_from_date
95 ,g_collect_to_date
96 ,g_full_refresh
97 ,g_collect_fte
98 ,g_collect_head
99 FROM pay_payroll_actions ppa
100 WHERE payroll_action_id = p_payroll_action_id;
101
102 END IF;
103
104 END get_parameters;
105
106 /******************************************************************************/
107 /* Truncates the HRI_MB_WMV table if a full refresh has been selected */
108 /* Checks that the seeded budget measurement type formulae are compiled */
109 /* Returns list of people to be processed */
110 /******************************************************************************/
111 PROCEDURE range_cursor( pactid IN NUMBER,
112 sqlstr OUT NOCOPY VARCHAR2) IS
113
114 l_sql_stmt VARCHAR2(500);
115 l_dummy1 VARCHAR2(2000);
116 l_dummy2 VARCHAR2(2000);
117 l_schema VARCHAR2(400);
118
119 BEGIN
120
121 /* Record the process start */
122 hri_bpl_conc_log.record_process_start('HRI_MB_WMV');
123
124 /* Set up the parameters */
125 get_parameters( p_payroll_action_id => pactid );
126
127 /* Feedback parameters selected */
128 output('Parameters selected:');
129 output(' Full Refresh: ' || g_full_refresh);
130 output(' Collect HEAD: ' || g_collect_head);
131 output(' Collect FTE: ' || g_collect_fte);
132
133 /* Raise a ff compile error if either of the seeded ffs to be used are not */
134 /* compiled */
135 IF (g_collect_fte = 'Y') THEN
136 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_FTE' );
137 END IF;
138
139 IF (g_collect_head = 'Y') THEN
140 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_HEAD' );
141 END IF;
142
143 /************************/
144 /* FULL REFRESH SECTION */
145 /************************/
146 /* Truncate the table if a full refresh is selected */
147 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
148
149 /* If it's a full refresh */
150 IF (g_full_refresh = 'Y') THEN
151
152 /* Truncate the table */
153 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
154 EXECUTE IMMEDIATE(l_sql_stmt);
155
156 /* Select all people with employee assignments in the collection range */
157 sqlstr :=
158 'SELECT DISTINCT
159 asg.person_id
160 FROM
161 per_all_assignments_f asg
162 ,pay_payroll_actions ppa
163 WHERE ppa.payroll_action_id = :payroll_action_id
164 AND asg.assignment_type = ''E''
165 AND (ppa.start_date
166 BETWEEN asg.effective_start_date AND asg.effective_end_date
167 OR asg.effective_start_date
168 BETWEEN ppa.start_date AND ppa.effective_date)
169 ORDER BY asg.person_id';
170
171 ELSE
172
173 /*******************************/
174 /* INCREMENTAL REFRESH SECTION */
175 /*******************************/
176 /* Select all people with changes to employee assignments or ABVs in the */
177 /* collection range */
178 sqlstr :=
179 'SELECT DISTINCT
180 asg.person_id
181 FROM
182 per_all_assignments_f asg
183 ,pay_payroll_actions ppa
184 WHERE ppa.payroll_action_id = :payroll_action_id
185 AND asg.assignment_type = ''E''
186 AND (asg.effective_start_date
187 BETWEEN ppa.start_date AND ppa.effective_date
188 OR asg.effective_end_date
189 BETWEEN ppa.start_date AND ppa.effective_date
190 OR EXISTS (SELECT null FROM per_assignment_budget_values_f abv
191 WHERE abv.assignment_id = asg.assignment_id
192 AND (abv.effective_start_date
193 BETWEEN ppa.start_date AND ppa.effective_date
194 OR abv.effective_end_date
195 BETWEEN ppa.start_date AND ppa.effective_date)))
196 ORDER BY asg.person_id';
197
198 END IF;
199
200 END IF;
201
202 END range_cursor;
203
204 /******************************************************************************/
205 /* Returns list of people to be processed */
206 /******************************************************************************/
207 PROCEDURE action_creation( pactid IN NUMBER,
208 stperson IN NUMBER,
209 endperson IN NUMBER,
210 chunk IN NUMBER) IS
211
212 /* Pick out assignments for people in range for incremental refresh */
213 /* all employee assignments which either have been updated or had an ABV */
214 /* updated within the collection range */
215 CURSOR incr_action_csr IS
216 SELECT
217 pay_assignment_actions_s.nextval next_seq
218 ,assignment_id assignment_id
219 FROM
220 (SELECT DISTINCT
221 asg.assignment_id assignment_id
222 FROM
223 per_all_assignments_f asg
224 WHERE asg.assignment_type = 'E'
225 AND asg.person_id BETWEEN stperson AND endperson
226 AND (asg.effective_start_date
227 BETWEEN g_collect_from_date AND g_collect_to_date
228 OR asg.effective_end_date
229 BETWEEN g_collect_from_date AND g_collect_to_date
230 OR EXISTS (SELECT null FROM per_assignment_budget_values_f abv
231 WHERE abv.assignment_id = asg.assignment_id
232 AND (abv.effective_start_date
233 BETWEEN g_collect_from_date AND g_collect_to_date
234 OR abv.effective_end_date
235 BETWEEN g_collect_from_date AND g_collect_to_date))));
236
237 BEGIN
238
239 get_parameters( p_payroll_action_id => pactid );
240
241 /************************/
242 /* FULL REFRESH SECTION */
243 /************************/
244 IF (g_full_refresh = 'Y') THEN
245
246 INSERT INTO pay_assignment_actions
247 (assignment_action_id,
248 assignment_id,
249 payroll_action_id,
250 action_status,
251 chunk_number,
252 action_sequence,
253 pre_payment_id,
254 object_version_number,
255 tax_unit_id,
256 source_action_id)
257 SELECT
258 pay_assignment_actions_s.nextval
259 ,assignment_id
260 ,pactid
261 ,'U'
262 ,chunk
263 ,pay_assignment_actions_s.nextval
264 ,to_number(null)
265 ,1
266 ,to_number(null)
267 ,to_number(null)
268 FROM
269 /* Pick out assignments for people in range for full refresh */
270 /* all employee assignments which exist at any point in the collection range */
271 (SELECT DISTINCT
272 asg.assignment_id assignment_id
273 FROM
274 per_all_assignments_f asg
275 WHERE asg.assignment_type = 'E'
276 AND asg.person_id BETWEEN stperson AND endperson
277 AND (g_collect_from_date
278 BETWEEN asg.effective_start_date AND asg.effective_end_date
279 OR asg.effective_start_date
280 BETWEEN g_collect_from_date AND g_collect_to_date));
281
282 ELSE
283
284 /*******************************/
285 /* INCREMENTAL REFRESH SECTION */
286 /*******************************/
287 /* Loop through cursor and insert actions one at a time */
288 FOR asg_rec IN incr_action_csr LOOP
289
290 hr_nonrun_asact.insact
291 (lockingactid => asg_rec.next_seq
292 ,assignid => asg_rec.assignment_id
293 ,pactid => pactid
294 ,chunk => chunk
295 ,greid => null);
296
297 END LOOP;
298
299 END IF;
300
301 END action_creation;
302
303 /******************************************************************************/
304 /* Initialization - sets up global parameters */
305 /******************************************************************************/
306 PROCEDURE init_code( p_payroll_action_id IN NUMBER) IS
307
308 l_test VARCHAR2(20);
309
310 BEGIN
311
312 get_parameters( p_payroll_action_id => p_payroll_action_id );
313
314 END init_code;
315
316 /******************************************************************************/
317 /* Inserts row into database table */
318 /******************************************************************************/
319 PROCEDURE insert_row( p_fte_value IN NUMBER,
320 p_head_value IN NUMBER,
321 p_effective_start_date IN DATE,
322 p_effective_end_date IN DATE,
323 p_assignment_id IN NUMBER,
324 p_person_id IN NUMBER,
325 p_business_group_id IN NUMBER,
326 p_asg_stat_type_id IN NUMBER,
327 p_per_sys_status IN VARCHAR2,
328 p_pay_sys_status IN VARCHAR2,
329 p_period_of_service_id IN NUMBER,
330 p_primary_flag IN VARCHAR2,
331 p_last_change_date IN VARCHAR2) IS
332
333 BEGIN
334
335 /* Inserts row */
336 INSERT INTO hri_mb_wmv
337 (primary_asg_indicator
338 ,asg_indicator
339 ,fte
340 ,head
341 ,effective_start_date
342 ,effective_end_date
343 ,assignment_id
344 ,person_id
345 ,business_group_id
346 ,assignment_status_type_id
347 ,per_system_status_code
348 ,pay_system_status_code
349 ,period_of_service_id
350 ,primary_flag
351 ,last_change_date)
352 VALUES
353 (DECODE(p_primary_flag,'Y',1,0)
354 ,1
355 ,p_fte_value
356 ,p_head_value
357 ,p_effective_start_date
358 ,p_effective_end_date
359 ,p_assignment_id
360 ,p_person_id
361 ,p_business_group_id
362 ,p_asg_stat_type_id
363 ,p_per_sys_status
364 ,p_pay_sys_status
365 ,p_period_of_service_id
366 ,p_primary_flag
367 ,p_last_change_date);
368
369 END insert_row;
370
371 /******************************************************************************/
372 /* Inserts stored rows into empty table - FULL REFRESH ONLY */
373 /******************************************************************************/
374 PROCEDURE insert_stored_rows IS
375
376 l_index PLS_INTEGER;
377 l_last_fte NUMBER;
378 l_last_head NUMBER;
379 l_end_date DATE;
380
381 BEGIN
382
383 /* Procedure only called if global table is populated */
384 l_index := g_start_date_tab.first;
385
386 /* Loop until there are no more rows left */
387 WHILE l_index IS NOT NULL LOOP
388
389 /* Get the next end date if it exists */
390 IF (g_start_date_tab.next(l_index) IS NOT NULL) THEN
391 l_end_date := g_start_date_tab(g_start_date_tab.next(l_index)) - 1;
392 /* otherwise use the final process date if that exists */
393 ELSIF (g_final_proc_tab(l_index) IS NOT NULL) THEN
394 l_end_date := g_final_proc_tab(l_index);
395 /* otherwise go with the end of time */
396 ELSE
397 l_end_date := g_end_of_time;
398 END IF;
399
400 /* If the FTE value has changed, store the change */
401 IF (g_fte_value_tab(l_index) IS NOT NULL) THEN
402 /* If an error has occurred running fast formula, set to null */
403 IF (g_fte_value_tab(l_index) = -999) THEN
404 l_last_fte := to_number(null);
405 ELSE
406 l_last_fte := g_fte_value_tab(l_index);
407 END IF;
408 END IF;
409
410 /* If the HEAD value has changed, store the change */
411 IF (g_head_value_tab(l_index) IS NOT NULL) THEN
412 /* If an error has occurred running fast formula, set to null */
413 IF (g_head_value_tab(l_index) = -999) THEN
414 l_last_head := to_number(null);
415 ELSE
416 l_last_head := g_head_value_tab(l_index);
417 END IF;
418 END IF;
419
420 /* Call procedure to insert the row */
421 insert_row
422 (p_fte_value => l_last_fte
423 ,p_head_value => l_last_head
424 ,p_effective_start_date => g_start_date_tab(l_index)
425 ,p_effective_end_date => l_end_date
426 ,p_assignment_id => g_asg_id_tab(l_index)
427 ,p_person_id => g_psn_id_tab(l_index)
428 ,p_business_group_id => g_bgr_id_tab(l_index)
429 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
430 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
431 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
432 ,p_period_of_service_id => g_pos_id_tab(l_index)
433 ,p_primary_flag => g_primary_flag_tab(l_index)
434 ,p_last_change_date => g_last_chng_tab(l_index));
435
436 /* Get the next index */
437 l_index := g_start_date_tab.next(l_index);
438
439 END LOOP;
440
441 END insert_stored_rows;
442
443 /******************************************************************************/
444 /* Inserts into or updates table with stored rows - INCREMENTAL REFRESH ONLY */
445 /******************************************************************************/
446 PROCEDURE process_stored_rows IS
447
448 /******************************************************************************/
449 /* The complexity here is due to the incremental updating of the abv table. */
450 /* If the first run populated fte values only, and the next run populates */
451 /* headcount only, there is no guarantee that the dates or periods on the */
452 /* table match the ones stored for inserting. So this procedure stores all */
453 /* the logic which marries up periods in the table with the stored periods to */
454 /* insert. */
455 /* */
456 /* For example, if the following already exists in the table for an */
457 /* assignment: */
458 /* */
459 /* TIME ======> */
460 /* */
461 /* FTE: |--- 1 ---|--- 0.6 ---|--- 0.3 ---| */
462 /* */
463 /* and the stored rows are for the following incremental headcount changes: */
464 /* */
465 /* HEAD: |----- 1 -----|---- 0 ----|-- 1 --| */
466 /* */
467 /* then the resulting data in the table after this process has run should be: */
468 /* */
469 /* FTE: | 1 |0.6| 0.6 |0.3| 0.3 | */
470 /* HEAD: | 1 | 1 | 0 | 0 | 1 | */
471 /* */
472 /******************************************************************************/
473
474 /* Cursor pulls out existing rows from the table each of which overlaps with */
475 /* the period for which the collection has taken place */
476 /* Note that the global table structure is populated in reverse chronological */
477 /* order */
478 CURSOR existing_rows_csr(v_assignment_id NUMBER,
479 v_start_date DATE,
480 v_end_date DATE) IS
481 SELECT
482 wmv.effective_start_date
483 ,wmv.effective_end_date
484 ,wmv.fte
485 ,wmv.head
486 FROM hri_mb_wmv wmv
487 WHERE wmv.assignment_id = v_assignment_id
488 AND (v_start_date BETWEEN wmv.effective_start_date AND wmv.effective_end_date
489 OR wmv.effective_start_date BETWEEN v_start_date AND v_end_date);
490 /* DO NOT CHANGE ORDER BY - PROCESSING IS DONE IN DATE ORDER (IMPLICIT) */
491
492 /* Variables to hold information from the cursor */
493 l_existing_start_date DATE;
494 l_existing_end_date DATE;
495 l_existing_fte NUMBER;
496 l_existing_head NUMBER;
497
498 /* Loop control variables */
499 l_next_insert_start DATE;
500 l_infinite_loop_catch DATE;
501 l_index PLS_INTEGER;
502
503 /* Variables to store calculations */
504 l_new_fte NUMBER;
505 l_new_head NUMBER;
506 l_end_date DATE;
507
508 BEGIN
509
510 /**********************************************************************/
511 /* The following should hold true for the main loop */
512 /* */
513 /* 1) Current existing row overlaps with current row to insert */
514 /* \- from cursor -/ \- from cache -/ */
515 /* */
516 /* 2) The next insert start date is within the date range of the */
517 /* current row to insert */
518 /* */
519 /* The following is enforced to prevent the WHILE loop never ending */
520 /* */
521 /* a) l_next_insert_start is strictly increasing */
522 /* */
523 /**********************************************************************/
524
525 /* Open the cursor with the end date of the range to insert. This is the */
526 /* final process date, if one exists, otherwise the end of time date */
527 IF (g_final_proc_tab(g_final_proc_tab.last) IS NULL) THEN
528 OPEN existing_rows_csr(g_asg_id_tab(g_start_date_tab.first)
529 ,g_start_date_tab(g_start_date_tab.first)
530 ,g_end_of_time);
531 ELSE
532 OPEN existing_rows_csr(g_asg_id_tab(g_start_date_tab.first)
533 ,g_start_date_tab(g_start_date_tab.first)
534 ,g_final_proc_tab(g_final_proc_tab.last));
535 END IF;
536
537 /* Initialize first existing row - overlaps with first row to insert by */
538 /* definition of cursor */
539 FETCH existing_rows_csr INTO l_existing_start_date,
540 l_existing_end_date,
541 l_existing_fte,
542 l_existing_head;
543
544 /* Initialize the index to the first record */
545 l_index := g_start_date_tab.first;
546
547 /* Set the next insert start date */
548 l_next_insert_start := g_start_date_tab(l_index);
549
550 -- EXISTING: - - - - - - - - - - -
551 -- TO INSERT: |-------------| - - - - -
552 -- PROCESSED TO: *
553
554 /* Loop through rows to insert */
555 WHILE l_index IS NOT NULL LOOP
556
557 /* Get the next end date if it exists */
558 IF (g_start_date_tab.next(l_index) IS NOT NULL) THEN
559 l_end_date := g_start_date_tab(g_start_date_tab.next(l_index)) - 1;
560 /* otherwise use the final process date if that exists */
561 ELSIF (g_final_proc_tab(l_index) IS NOT NULL) THEN
562 l_end_date := g_final_proc_tab(l_index);
563 /* otherwise go with the end of time */
564 ELSE
565 l_end_date := g_end_of_time;
566 END IF;
567
568 WHILE (l_next_insert_start <= l_end_date) LOOP
569
570 l_infinite_loop_catch := l_next_insert_start;
571
572 /* If the FTE value has changed, store the change */
573 IF (g_fte_value_tab(l_index) IS NOT NULL) THEN
574 /* If a fast formula error has occurred, set to null */
575 IF (g_fte_value_tab(l_index) = -999) THEN
576 l_new_fte := to_number(null);
577 ELSE
578 l_new_fte := g_fte_value_tab(l_index);
579 END IF;
580 /* Otherwise, use an existing value if there is one */
581 ELSIF (l_existing_fte IS NOT NULL) THEN
582 l_new_fte := l_existing_fte;
583 /* If there is no existing value, use the last value of l_new_fte */
584 END IF;
585
586 /* If the HEAD value has changed, store the change */
587 IF (g_head_value_tab(l_index) IS NOT NULL) THEN
588 /* If a fast formula error has occurred, set to null */
589 IF (g_head_value_tab(l_index) = -999) THEN
590 l_new_head := to_number(null);
591 ELSE
592 l_new_head := g_head_value_tab(l_index);
593 END IF;
594 /* Otherwise, use an existing value if there is one */
595 ELSIF (l_existing_head IS NOT NULL) THEN
596 l_new_head := l_existing_head;
597 /* If there is no existing value, use the last value of l_new_head */
598 END IF;
599
600 /*********************/
601 /* Main Body of Loop */
602 /*********************/
603 IF (l_existing_start_date < g_start_date_tab(l_index)) THEN
604
605 -- EXISTING: |------------- - - - - -
606 -- TO INSERT: |-------------| - - - - -
607 -- PROCESSED TO: |------*
608
609 /* End date existing row */
610 UPDATE hri_mb_wmv
611 SET effective_end_date = g_start_date_tab(l_index) - 1
612 WHERE assignment_id = g_asg_id_tab(l_index)
613 AND effective_start_date = l_existing_start_date;
614
615 -- EXISTING: |------------- - - - - -
616 -- TO INSERT: |-------------| - - - - -
617 -- PROCESSED TO: |------|*
618
619 /* Insert new row up to the end of the existing row */
620 insert_row
621 (p_fte_value => l_new_fte
622 ,p_head_value => l_new_head
623 ,p_effective_start_date => g_start_date_tab(l_index)
624 ,p_effective_end_date => LEAST(l_existing_end_date,l_end_date)
625 ,p_assignment_id => g_asg_id_tab(l_index)
626 ,p_person_id => g_psn_id_tab(l_index)
627 ,p_business_group_id => g_bgr_id_tab(l_index)
628 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
629 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
630 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
631 ,p_period_of_service_id => g_pos_id_tab(l_index)
632 ,p_primary_flag => g_primary_flag_tab(l_index)
633 ,p_last_change_date => g_last_chng_tab(l_index));
634
635 -- EXISTING: |------------- - - - - -
636 -- TO INSERT: |-------------| - - - - -
637 -- PROCESSED TO: |------|-----* - - - *
638 -- Processed to the earlier end date
639
640 /* Update the loop variable */
641 l_next_insert_start := LEAST(l_existing_end_date,l_end_date) + 1;
642
643 /* If the EXISTING row had the earlier end date, get the next one */
644 IF (l_next_insert_start > l_existing_end_date) THEN
645
646 /* Get the next existing row */
647 FETCH existing_rows_csr INTO l_existing_start_date,
648 l_existing_end_date,
649 l_existing_fte,
650 l_existing_head;
651
652 ELSE
653
654 /* Move the existing start date to the start of the last row */
655 /* inserted */
656 l_existing_start_date := g_start_date_tab(l_index);
657
658 END IF;
659
660 ELSIF (l_existing_start_date = g_start_date_tab(l_index)) THEN
661
662 IF (l_existing_end_date <= l_end_date) THEN
663
664 -- EXISTING: |------| - - - - -
665 -- TO INSERT: |-------------| - - - - -
666 -- PROCESSED TO: *
667
668 /* Update existing row */
669 UPDATE hri_mb_wmv
670 SET fte = l_new_fte,
671 head = l_new_head,
672 assignment_status_type_id = g_ast_id_tab(l_index),
673 per_system_status_code = g_per_sys_stat_tab(l_index),
674 pay_system_status_code = g_pay_sys_stat_tab(l_index),
675 primary_flag = g_primary_flag_tab(l_index),
676 last_change_date = g_last_chng_tab(l_index)
677 WHERE assignment_id = g_asg_id_tab(l_index)
678 AND effective_start_date = l_existing_start_date;
679
680 -- EXISTING: |------| - - - - -
681 -- TO INSERT: |-------------| - - - - -
682 -- PROCESSED TO: |------*
683
684 /* Update the loop variable */
685 l_next_insert_start := l_existing_end_date + 1;
686
687 /* Get the next existing row */
688 FETCH existing_rows_csr INTO l_existing_start_date,
689 l_existing_end_date,
690 l_existing_fte,
691 l_existing_head;
692
693 ELSIF (l_existing_end_date > l_end_date) THEN
694
695 -- EXISTING: |--------------------| - - - - -
696 -- TO INSERT: |-------------| - - - - -
697 -- PROCESSED TO: *
698
699 /* Start date existing row */
700 UPDATE hri_mb_wmv
701 SET effective_start_date = l_end_date + 1
702 WHERE assignment_id = g_asg_id_tab(l_index)
703 AND effective_start_date = l_existing_start_date;
704
705 /* Update l_existing_start_date */
706 l_existing_start_date := l_end_date + 1;
707
708 -- EXISTING: |------| - - - - -
709 -- TO INSERT: |-------------| - - - - -
710 -- PROCESSED TO: *
711
712 /* Insert new row up to the beginning of the updated existing row */
713 insert_row
714 (p_fte_value => l_new_fte
715 ,p_head_value => l_new_head
716 ,p_effective_start_date => g_start_date_tab(l_index)
717 ,p_effective_end_date => l_end_date
718 ,p_assignment_id => g_asg_id_tab(l_index)
719 ,p_person_id => g_psn_id_tab(l_index)
720 ,p_business_group_id => g_bgr_id_tab(l_index)
721 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
722 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
723 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
724 ,p_period_of_service_id => g_pos_id_tab(l_index)
725 ,p_primary_flag => g_primary_flag_tab(l_index)
726 ,p_last_change_date => g_last_chng_tab(l_index));
727
728 -- EXISTING: |------| - - - - -
729 -- TO INSERT: |-------------| - - - - -
730 -- PROCESSED TO: |-------------*
731
732 /* Update the loop variable */
733 l_next_insert_start := l_end_date + 1;
734
735 END IF;
736
737 ELSIF (l_existing_start_date > g_start_date_tab(l_index)) THEN
738
739 /* Will only happen on the first record if collection period is earlier */
740 /* than previous collection periods... */
741 IF (l_existing_start_date > l_next_insert_start) THEN
742
743 -- EXISTING: |-------| - - - - -
744 -- TO INSERT: |-------------| - - - - -
745 -- PROCESSED TO: *
746
747 /* Insert part of new row before existing row */
748 insert_row
749 (p_fte_value => l_new_fte
750 ,p_head_value => l_new_head
751 ,p_effective_start_date => l_next_insert_start
752 ,p_effective_end_date => l_existing_start_date - 1
753 ,p_assignment_id => g_asg_id_tab(l_index)
754 ,p_person_id => g_psn_id_tab(l_index)
755 ,p_business_group_id => g_bgr_id_tab(l_index)
756 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
757 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
758 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
759 ,p_period_of_service_id => g_pos_id_tab(l_index)
760 ,p_primary_flag => g_primary_flag_tab(l_index)
761 ,p_last_change_date => g_last_chng_tab(l_index));
762
763 -- EXISTING: |-------| - - - - -
764 -- TO INSERT: |-------------| - - - - -
765 -- PROCESSED TO: |--*
766
767 END IF;
768
769 IF (l_existing_end_date <= l_end_date) THEN
770
771 -- EXISTING: |-------| - - - - -
772 -- TO INSERT: |-------------| - - - - -
773 -- PROCESSED TO: |--*
774
775 /* Update existing row with latest information */
776 UPDATE hri_mb_wmv
777 SET fte = l_new_fte,
778 head = l_new_head,
779 assignment_status_type_id = g_ast_id_tab(l_index),
780 per_system_status_code = g_per_sys_stat_tab(l_index),
781 pay_system_status_code = g_pay_sys_stat_tab(l_index),
782 primary_flag = g_primary_flag_tab(l_index),
783 last_change_date = g_last_chng_tab(l_index)
784 WHERE assignment_id = g_asg_id_tab(l_index)
785 AND effective_start_date = l_existing_start_date;
786
787 -- EXISTING: |-------| - - - - -
788 -- TO INSERT: |-------------| - - - - -
789 -- PROCESSED TO: |--|-------*
790
791 /* Update the loop variable */
792 l_next_insert_start := l_existing_end_date + 1;
793
794 /* Get the next existing row */
795 FETCH existing_rows_csr INTO l_existing_start_date,
796 l_existing_end_date,
797 l_existing_fte,
798 l_existing_head;
799
800 ELSIF (l_existing_end_date > l_end_date) THEN
801
802 -- EXISTING: |-------------| - - - - -
803 -- TO INSERT: |-------------| - - - - -
804 -- PROCESSED TO: |------*
805
806 /* Start date existing row */
807 UPDATE hri_mb_wmv
808 SET effective_start_date = l_end_date + 1
809 WHERE assignment_id = g_asg_id_tab(l_index)
810 AND effective_start_date = l_existing_start_date;
811
812 -- EXISTING: |------| - - - - -
813 -- TO INSERT: |-------------| - - - - -
814 -- PROCESSED TO: |------*
815
816 /* Insert new row up to the beginning of the updated existing row */
817 insert_row
818 (p_fte_value => l_new_fte
819 ,p_head_value => l_new_head
820 ,p_effective_start_date => l_existing_start_date
821 ,p_effective_end_date => l_end_date
822 ,p_assignment_id => g_asg_id_tab(l_index)
823 ,p_person_id => g_psn_id_tab(l_index)
824 ,p_business_group_id => g_bgr_id_tab(l_index)
825 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
826 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
827 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
828 ,p_period_of_service_id => g_pos_id_tab(l_index)
829 ,p_primary_flag => g_primary_flag_tab(l_index)
830 ,p_last_change_date => g_last_chng_tab(l_index));
831
832 /* Update l_existing_start_date */
833 l_existing_start_date := l_end_date + 1;
834
835 -- EXISTING: |------| - - - - -
836 -- TO INSERT: |-------------| - - - - -
837 -- PROCESSED TO: |------|------*
838
839 /* Update the loop variable */
840 l_next_insert_start := l_end_date + 1;
841
842 END IF;
843
844 ELSIF (l_existing_start_date IS NULL) THEN
845
846 /* No overlap - insert row */
847 insert_row
848 (p_fte_value => l_new_fte
849 ,p_head_value => l_new_head
850 ,p_effective_start_date => g_start_date_tab(l_index)
851 ,p_effective_end_date => l_end_date
852 ,p_assignment_id => g_asg_id_tab(l_index)
853 ,p_person_id => g_psn_id_tab(l_index)
854 ,p_business_group_id => g_bgr_id_tab(l_index)
855 ,p_asg_stat_type_id => g_ast_id_tab(l_index)
856 ,p_per_sys_status => g_per_sys_stat_tab(l_index)
857 ,p_pay_sys_status => g_pay_sys_stat_tab(l_index)
858 ,p_period_of_service_id => g_pos_id_tab(l_index)
859 ,p_primary_flag => g_primary_flag_tab(l_index)
860 ,p_last_change_date => g_last_chng_tab(l_index));
861
862 l_next_insert_start := l_end_date + 1;
863
864 END IF;
865
866 /* Trap any infinite loops which may occur because of dodgy data or */
867 /* mistakes in the code - this should never actually happen... */
868 IF (l_next_insert_start = l_infinite_loop_catch) THEN
869
870 /* Put a note in the log */
871 output('Trapped for ' || to_char(g_asg_id_tab(l_index)) || ' on ' ||
872 to_char(l_infinite_loop_catch,'DD-MM-YYYY'));
873
874 /* This effectively exits the loops */
875 l_next_insert_start := to_date(null);
876
877 END IF;
878
879 END LOOP;
880
881 /* Move to next stored row */
882 l_index := g_start_date_tab.next(l_index);
883
884 END LOOP;
885
886 /* Close cursor */
887 CLOSE existing_rows_csr;
888
889 /* Tidy up obsolete rows if a termination has occurred */
890 IF (g_full_refresh = 'N' AND
891 g_final_proc_tab(g_final_proc_tab.last) IS NOT NULL) THEN
892
893 /* For some reason two statements are needed here */
894 l_index := g_asg_id_tab.last;
895
896 DELETE FROM hri_mb_wmv
897 WHERE assignment_id = g_asg_id_tab(l_index)
898 AND effective_start_date > g_final_proc_tab(l_index);
899
900 END IF;
901
902 EXCEPTION
903 WHEN OTHERS THEN
904
905 /* Close the cursor if an exception occurs */
906 CLOSE existing_rows_csr;
907
908 raise;
909
910 END process_stored_rows;
911
912 /******************************************************************************/
913 /* Calcualates potential ABV changes for an assignment and measurement type */
914 /******************************************************************************/
915 PROCEDURE process_assignment(p_assignment_id IN NUMBER,
916 p_bmt_code IN VARCHAR2) IS
917
918 CURSOR ptntl_abv_changes_csr IS
919 /* All assignment budget values active during the collect period */
920 SELECT
921 abv.value abv_value
922 ,GREATEST(asg.effective_start_date,
923 abv.effective_start_date,
924 g_collect_from_date) effective_start_date
925 ,asg.assignment_id assignment_id
926 ,asg.business_group_id business_group_id
927 ,asg.person_id person_id
928 ,asg.assignment_status_type_id asg_status_type_id
929 ,ast.per_system_status per_system_status
930 ,ast.pay_system_status pay_system_status
931 ,asg.period_of_service_id period_of_service_id
932 ,asg.primary_flag primary_flag
933 ,GREATEST(abv.last_update_date, asg.last_update_date)
934 last_change_date
935 ,pos.final_process_date final_process_date
936 FROM
937 per_assignment_budget_values_f abv
938 ,per_all_assignments_f asg
939 ,per_assignment_status_types ast
940 ,per_periods_of_service pos
941 WHERE abv.assignment_id = asg.assignment_id
942 AND asg.assignment_id = p_assignment_id
943 AND asg.period_of_service_id = pos.period_of_service_id
944 AND ast.assignment_status_type_id = asg.assignment_status_type_id
945 AND abv.unit = p_bmt_code
946 AND asg.assignment_type = 'E'
947 /* ABV Date Joins - all post hire ABV changes within the collection period */
948 /* Restrict to ABVs at hire or later */
949 AND (abv.effective_start_date >= pos.date_start
950 OR pos.date_start
951 BETWEEN abv.effective_start_date AND abv.effective_end_date)
952 /* Only ABVs starting in collection period */
953 AND (GREATEST(abv.effective_start_date, pos.date_start)
954 BETWEEN g_collect_from_date AND g_collect_to_date
955 /* or finishing in collection period (incremental refresh only) */
956 OR (pos.final_process_date
957 BETWEEN g_collect_from_date AND g_collect_to_date
958 AND pos.final_process_date
959 BETWEEN abv.effective_start_date AND abv.effective_end_date
960 AND g_full_refresh = 'N')
961 /* or active at the start of the collection period (full refresh only) */
962 OR (g_collect_from_date
963 BETWEEN abv.effective_start_date AND abv.effective_end_date
964 AND g_full_refresh = 'Y'))
965 /* Assignment Date Join - Pin by ABV, hire or period start */
966 AND GREATEST(abv.effective_start_date, pos.date_start, g_collect_from_date)
967 BETWEEN asg.effective_start_date AND asg.effective_end_date
968 UNION ALL
969 /* All ended assignment budget values with still active assignments */
970 /* that are not picked up in the next union (i.e. do not coincide with */
971 /* an assignment change */
972 SELECT
973 to_number(null) abv_value
974 ,abv.effective_end_date + 1 effective_start_date
975 ,asg.assignment_id assignment_id
976 ,asg.business_group_id business_group_id
977 ,asg.person_id person_id
978 ,asg.assignment_status_type_id asg_status_type_id
979 ,ast.per_system_status per_system_status
980 ,ast.pay_system_status pay_system_status
981 ,asg.period_of_service_id period_of_service_id
982 ,asg.primary_flag primary_flag
983 ,GREATEST(abv.last_update_date, asg.last_update_date)
984 last_change_date
985 ,pos.final_process_date final_process_date
986 FROM
987 per_assignment_budget_values_f abv
988 ,per_all_assignments_f asg
989 ,per_assignment_status_types ast
990 ,per_periods_of_service pos
991 WHERE abv.assignment_id = asg.assignment_id
992 AND asg.assignment_id = p_assignment_id
993 AND pos.period_of_service_id = asg.period_of_service_id
994 AND asg.assignment_type = 'E'
995 AND ast.assignment_status_type_id = asg.assignment_status_type_id
996 AND abv.unit = p_bmt_code
997 AND abv.effective_end_date + 1
998 BETWEEN g_collect_from_date AND g_collect_to_date
999 AND asg.effective_start_date < abv.effective_end_date + 1
1000 AND abv.effective_end_date + 1 <= asg.effective_end_date
1001 AND NOT EXISTS
1002 (SELECT null
1003 FROM per_assignment_budget_values_f abv_next
1004 WHERE abv_next.assignment_id = abv.assignment_id
1005 AND abv_next.unit = abv.unit
1006 AND abv_next.effective_start_date = abv.effective_end_date + 1)
1007 UNION ALL
1008 /* All assignment changes without an abv in the table */
1009 /* If full refresh is selected then active assignments at the start */
1010 /* of the collect period are also picked up */
1011 SELECT
1012 to_number(null) abv_value
1013 ,GREATEST(asg.effective_start_date, g_collect_from_date)
1014 effective_start_date
1015 ,asg.assignment_id assignment_id
1016 ,asg.business_group_id business_group_id
1017 ,asg.person_id person_id
1018 ,asg.assignment_status_type_id asg_status_type_id
1019 ,ast.per_system_status per_system_status
1020 ,ast.pay_system_status pay_system_status
1021 ,asg.period_of_service_id period_of_service_id
1022 ,asg.primary_flag primary_flag
1023 ,asg.last_update_date last_change_date
1024 ,pos.final_process_date final_process_date
1025 FROM
1026 per_all_assignments_f asg
1027 ,per_assignment_status_types ast
1028 ,per_periods_of_service pos
1029 WHERE asg.assignment_id = p_assignment_id
1030 AND pos.period_of_service_id = asg.period_of_service_id
1031 AND ast.assignment_status_type_id = asg.assignment_status_type_id
1032 AND asg.assignment_type = 'E'
1033 AND (asg.effective_start_date
1034 BETWEEN g_collect_from_date AND g_collect_to_date
1035 OR (g_collect_from_date
1036 BETWEEN asg.effective_start_date AND asg.effective_end_date
1037 AND g_full_refresh = 'Y'))
1038 AND NOT EXISTS
1039 (SELECT null
1040 FROM per_assignment_budget_values_f abv
1041 WHERE abv.assignment_id = asg.assignment_id
1042 AND abv.unit = p_bmt_code
1043 AND GREATEST(asg.effective_start_date, g_collect_from_date)
1044 BETWEEN abv.effective_start_date AND abv.effective_end_date)
1045 UNION ALL
1046 /* Bug 2649221 - All assignment status and primary flag changes with an ABV */
1047 SELECT
1048 abv.value abv_value
1049 ,next_asg.effective_start_date effective_start_date
1050 ,next_asg.assignment_id assignment_id
1051 ,next_asg.business_group_id business_group_id
1052 ,next_asg.person_id person_id
1053 ,next_asg.assignment_status_type_id asg_status_type_id
1054 ,ast.per_system_status per_system_status
1055 ,ast.pay_system_status pay_system_status
1056 ,next_asg.period_of_service_id period_of_service_id
1057 ,next_asg.primary_flag primary_flag
1058 ,GREATEST(abv.last_update_date, next_asg.last_update_date)
1059 last_change_date
1060 ,pos.final_process_date final_process_date
1061 FROM
1062 per_assignment_budget_values_f abv
1063 ,per_all_assignments_f asg
1064 ,per_all_assignments_f next_asg
1065 ,per_assignment_status_types ast
1066 ,per_periods_of_service pos
1067 WHERE abv.assignment_id = asg.assignment_id
1068 AND asg.assignment_id = p_assignment_id
1069 AND next_asg.assignment_id = asg.assignment_id
1070 AND next_asg.effective_start_date = asg.effective_end_date + 1
1071 /* Primary flag or assignment status change */
1072 AND (NVL(next_asg.primary_flag,'N') <> NVL(asg.primary_flag,'N')
1073 OR next_asg.assignment_status_type_id <> asg.assignment_status_type_id)
1074 AND next_asg.period_of_service_id = pos.period_of_service_id
1075 AND ast.assignment_status_type_id = next_asg.assignment_status_type_id
1076 AND abv.unit = p_bmt_code
1077 AND next_asg.assignment_type = 'E'
1078 AND next_asg.effective_start_date
1079 BETWEEN abv.effective_start_date AND abv.effective_end_date
1080 AND next_asg.effective_start_date
1081 BETWEEN g_collect_from_date AND g_collect_to_date
1082 ORDER BY 2 ASC;
1083 /* DO NOT CHANGE THE ORDER BY - MUST PROCESS IN DATE ORDER */
1084
1085 l_index PLS_INTEGER; -- index for package global table
1086 l_abv_value NUMBER;
1087
1088 l_last_fte NUMBER;
1089 l_last_head NUMBER;
1090 l_last_ast_id NUMBER;
1091 l_last_prm_flag VARCHAR2(30);
1092
1093 BEGIN
1094
1095 FOR abv_change_rec IN ptntl_abv_changes_csr LOOP
1096
1097 /* If no values have changed, skip the insert */
1098 IF ((p_bmt_code = 'FTE' AND
1099 abv_change_rec.abv_value = l_last_fte AND
1100 abv_change_rec.primary_flag = l_last_prm_flag AND
1101 abv_change_rec.asg_status_type_id = l_last_ast_id)
1102 OR
1103 (p_bmt_code = 'HEAD' AND
1104 abv_change_rec.abv_value = l_last_head AND
1105 abv_change_rec.primary_flag = l_last_prm_flag AND
1106 abv_change_rec.asg_status_type_id = l_last_ast_id)
1107 ) THEN
1108
1109 /* Easier to write the above condition this way round! */
1110 null;
1111
1112 ELSE
1113
1114 /* Get index of new row to insert */
1115 /* Cursor guarantees that: {cursor start date >= g_collect_from date} */
1116 l_index := abv_change_rec.effective_start_date - g_collect_from_date;
1117
1118 /* Get the ABV value if it is not in the ABV table */
1119 IF (abv_change_rec.abv_value IS NULL) THEN
1120 BEGIN
1121 l_abv_value := hri_bpl_abv.calc_abv
1122 (p_assignment_id => abv_change_rec.assignment_id
1123 ,p_business_group_id => abv_change_rec.business_group_id
1124 ,p_budget_type => p_bmt_code
1125 ,p_effective_date => abv_change_rec.effective_start_date
1126 ,p_primary_flag => NVL(abv_change_rec.primary_flag,'N')
1127 ,p_run_formula => 'Y');
1128 EXCEPTION WHEN OTHERS THEN
1129 l_abv_value := -999; -- error in running fast formula
1130 END;
1131 ELSE
1132 l_abv_value := abv_change_rec.abv_value;
1133 END IF;
1134
1135 /* If a row already exists for a date, skip and just update the ABV */
1136 IF (NOT g_start_date_tab.EXISTS(l_index)) THEN
1137
1138 /* Store row indexed by start date */
1139 g_start_date_tab(l_index) := abv_change_rec.effective_start_date;
1140 g_asg_id_tab(l_index) := abv_change_rec.assignment_id;
1141 g_bgr_id_tab(l_index) := abv_change_rec.business_group_id;
1142 g_psn_id_tab(l_index) := abv_change_rec.person_id;
1143 g_ast_id_tab(l_index) := abv_change_rec.asg_status_type_id;
1144 g_per_sys_stat_tab(l_index) := abv_change_rec.per_system_status;
1145 g_pay_sys_stat_tab(l_index) := abv_change_rec.pay_system_status;
1146 g_pos_id_tab(l_index) := abv_change_rec.period_of_service_id;
1147 g_primary_flag_tab(l_index) := abv_change_rec.primary_flag;
1148 g_last_chng_tab(l_index) := abv_change_rec.last_change_date;
1149 g_final_proc_tab(l_index) := abv_change_rec.final_process_date;
1150 l_last_ast_id := abv_change_rec.asg_status_type_id;
1151 l_last_prm_flag := abv_change_rec.primary_flag;
1152
1153 /* Sort out the ABV values */
1154 IF (p_bmt_code = 'HEAD') THEN
1155 g_fte_value_tab(l_index) := to_number(null);
1156 g_head_value_tab(l_index) := l_abv_value;
1157 l_last_head := l_abv_value;
1158 ELSIF (p_bmt_code = 'FTE') THEN
1159 g_fte_value_tab(l_index) := l_abv_value;
1160 g_head_value_tab(l_index) := to_number(null);
1161 l_last_fte := l_abv_value;
1162 END IF;
1163
1164 ELSE
1165
1166 /* Just update the single column corresponding to the ABV */
1167 IF (p_bmt_code = 'HEAD') THEN
1168 g_head_value_tab(l_index) := l_abv_value;
1169 l_last_head := l_abv_value;
1170 ELSIF (p_bmt_code = 'FTE') THEN
1171 g_fte_value_tab(l_index) := l_abv_value;
1172 l_last_fte := l_abv_value;
1173 END IF;
1174
1175 END IF;
1176
1177 /* Increment stored rows counter */
1178 g_rows_inserted := g_rows_inserted + 1;
1179
1180 END IF;
1181
1182 END LOOP;
1183
1184 END process_assignment;
1185
1186 /******************************************************************************/
1187 /* Processes actions and inserts data into summary table */
1188 /* This procedure is executed for every assignment in a chunk */
1189 /******************************************************************************/
1190 PROCEDURE archive_code( p_assactid IN NUMBER,
1191 p_effective_date IN DATE) IS
1192
1193 /* Cursor to get the assignment_id for the assignment action */
1194 CURSOR asg_action_csr IS
1195 SELECT
1196 paa.assignment_id
1197 FROM pay_assignment_actions paa
1198 WHERE paa.assignment_action_id = p_assactid;
1199
1200 /* Holds assignment from the cursor */
1201 l_assignment_id NUMBER;
1202
1203 BEGIN
1204
1205 /* Initialize global variables */
1206 g_rows_inserted := 0;
1207 init_global_table;
1208
1209 OPEN asg_action_csr;
1210 FETCH asg_action_csr INTO l_assignment_id;
1211 CLOSE asg_action_csr;
1212
1213 /* Process for FTE changes if the collect_fte parameter is set */
1214 IF (g_collect_fte = 'Y') THEN
1215 process_assignment
1216 (p_assignment_id => l_assignment_id
1217 ,p_bmt_code => 'FTE');
1218 END IF;
1219
1220 /* Process for HEAD changes if the collect_fte parameter is set */
1221 IF (g_collect_head = 'Y') THEN
1222 process_assignment
1223 (p_assignment_id => l_assignment_id
1224 ,p_bmt_code => 'HEAD');
1225 END IF;
1226
1227 /* Insert stored rows only if there are any stored */
1228 IF (g_full_refresh = 'Y' AND g_rows_inserted > 0) THEN
1229 insert_stored_rows;
1230 ELSIF (g_full_refresh = 'N' AND g_rows_inserted > 0) THEN
1231 process_stored_rows;
1232 END IF;
1233
1234 END archive_code;
1235
1236 /******************************************************************************/
1237 /* Runs at process end to clean up payroll actions and log conc process run */
1238 /******************************************************************************/
1239 PROCEDURE deinit_code(p_payroll_action_id IN NUMBER) IS
1240 --
1241 l_dummy1 VARCHAR2(2000);
1242 l_dummy2 VARCHAR2(2000);
1243 l_schema VARCHAR2(400);
1244 --
1245 CURSOR wmv_pact_id_csr IS
1246 SELECT payroll_action_id
1247 FROM pay_payroll_actions
1248 WHERE report_qualifier = 'HRI_MB_WMV'
1249 AND report_type = 'HISTORIC_SUMMARY'
1250 AND action_type = 'X';
1251 --
1252 BEGIN
1253 --
1254 -- Bug 2911335 - Collect stats for full refresh
1255 --
1256 IF (g_full_refresh = 'Y') THEN
1257 --
1258 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1259 --
1260 output('Full Refresh selected - gathering stats');
1261 fnd_stats.gather_table_stats(l_schema,'HRI_MB_WMV');
1262 --
1263 END IF;
1264 --
1265 END IF;
1266 --
1267 -- Bug 2823028 - Clean up payroll actions
1268 -- 4200282 Purge all historic payroll action records for HRI_MB_WMV
1269 -- process. This was done by hrizxwmv.sql which slows down patch application
1270 -- therefore it has been moved to
1271 --
1272 FOR wmv_pact_id_rec IN wmv_pact_id_csr LOOP
1273 --
1274 pay_archive.remove_report_actions(wmv_pact_id_rec.payroll_action_id);
1275 --
1276 END LOOP;
1277 --
1278 hri_bpl_conc_log.log_process_end(
1279 p_status => TRUE,
1280 p_period_from => TRUNC(g_collect_from_date),
1281 p_period_to => TRUNC(g_collect_to_date),
1282 p_attribute1 => g_collect_fte,
1283 p_attribute2 => g_collect_head,
1284 p_attribute3 => g_full_refresh);
1285 --
1286 END deinit_code;
1287
1288
1289 /******************************************************************************/
1290 /* Debugging procedure to run for a single business group */
1291 /******************************************************************************/
1292 PROCEDURE run_for_bg(p_business_group_id IN NUMBER,
1293 p_full_refresh IN VARCHAR2,
1294 p_collect_fte IN VARCHAR2,
1295 p_collect_head IN VARCHAR2,
1296 p_collect_from IN VARCHAR2,
1297 p_collect_to IN VARCHAR2) IS
1298
1299 CURSOR asg_csr IS
1300 SELECT DISTINCT
1301 asg.assignment_id
1302 FROM
1303 per_all_assignments_f asg
1304 WHERE (asg.business_group_id = p_business_group_id
1305 OR p_business_group_id IS NULL)
1306 AND asg.assignment_type = 'E'
1307 AND (g_collect_from_date
1308 BETWEEN asg.effective_start_date AND asg.effective_end_date
1309 OR asg.effective_start_date
1310 BETWEEN g_collect_from_date AND g_collect_to_date);
1311
1312 l_dummy1 VARCHAR2(2000);
1313 l_dummy2 VARCHAR2(2000);
1314 l_sql_stmt VARCHAR2(2000);
1315 l_schema VARCHAR2(30);
1316
1317 BEGIN
1318
1319 g_collect_from_date := to_date(p_collect_from,'DD-MM-YYYY');
1320 g_collect_to_date := to_date(p_collect_to,'DD-MM-YYYY');
1321 g_full_refresh := p_full_refresh;
1322 g_collect_fte := p_collect_fte;
1323 g_collect_head := p_collect_head;
1324
1325 /* Raise a ff compile error if either of the seeded ffs to be used are not */
1326 /* compiled */
1327 IF (g_collect_head = 'Y') THEN
1328 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_FTE' );
1329 END IF;
1330
1331 IF (g_collect_head = 'Y') THEN
1332 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_HEAD' );
1333 END IF;
1334
1335 /* Truncate the table if a full refresh is selected */
1336 IF (g_full_refresh = 'Y') THEN
1337
1338 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1339
1340 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
1341 EXECUTE IMMEDIATE(l_sql_stmt);
1342
1343 output('Full Refresh selected - truncated existing data');
1344
1345 END IF;
1346
1347 END IF;
1348
1349 FOR asg_rec IN asg_csr LOOP
1350
1351 /* Initialise globals */
1352 init_global_table;
1353 g_rows_inserted := 0;
1354
1355 IF (g_collect_fte = 'Y') THEN
1356 process_assignment
1357 (p_assignment_id => asg_rec.assignment_id
1358 ,p_bmt_code => 'FTE');
1359 END IF;
1360
1361 IF (g_collect_head = 'Y') THEN
1362 process_assignment
1363 (p_assignment_id => asg_rec.assignment_id
1364 ,p_bmt_code => 'HEAD');
1365 END IF;
1366
1367 IF (g_full_refresh = 'Y' AND g_rows_inserted > 0) THEN
1368 insert_stored_rows;
1369 ELSIF (g_full_refresh = 'N' AND g_rows_inserted > 0) THEN
1370 process_stored_rows;
1371 END IF;
1372
1373 END LOOP;
1374
1375
1376 END run_for_bg;
1377 --
1378 -- ----------------------------------------------------------------------------
1379 -- shared_hrms_dflt_prcss
1380 -- This process will be launched by shared_hrms_dflt_prcss (OVERLOADED).
1381 -- ============================================================================
1382 -- This procedure contains the code required to populate hri_mb_wmv in shared
1383 -- HR.
1384 --
1385 PROCEDURE shared_hrms_dflt_prcss
1386 IS
1387 --
1388 l_dummy1 VARCHAR2(2000);
1389 l_dummy2 VARCHAR2(2000);
1390 l_schema VARCHAR2(400);
1391 l_sql_stmt VARCHAR2(500);
1392 --
1393 BEGIN
1394 --
1395 output('Entering the default collection process,'||
1396 ' called when foundation HR is detected.');
1397 --
1398 -- Record the process start
1399 --
1400 hri_bpl_conc_log.record_process_start('HRI_MB_WMV');
1401 --
1402 -- Truncate the table
1403 --
1404 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1405 --
1406 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WMV';
1407 EXECUTE IMMEDIATE(l_sql_stmt);
1408 --
1409 END IF;
1410 --
1411 g_end_of_time := hr_general.end_of_time;
1412 --
1413 -- Inserts row
1414 --
1415 INSERT /*+ APPEND */ INTO hri_mb_wmv
1416 (primary_asg_indicator
1417 ,asg_indicator
1418 ,fte
1419 ,head
1420 ,effective_start_date
1421 ,effective_end_date
1422 ,assignment_id
1423 ,person_id
1424 ,business_group_id
1425 ,assignment_status_type_id
1426 ,per_system_status_code
1427 ,pay_system_status_code
1428 ,period_of_service_id
1429 ,primary_flag
1430 ,last_change_date)
1431 SELECT
1432 DECODE(asg.primary_flag,'Y',1,0) primary_flag_indicator
1433 ,1 asg_indicator
1434 ,1 fte_value
1435 ,1 head_value
1436 ,GREATEST(asg.effective_start_date
1437 ,trunc(SYSDATE))
1438 effective_start_date
1439 ,nvl(pos.final_process_date , g_end_of_time) effective_end_date
1440 ,asg.assignment_id assignment_id
1441 ,asg.person_id person_id
1442 ,asg.business_group_id business_group_id
1443 ,asg.assignment_status_type_id asg_status_type_id
1444 ,ast.per_system_status per_system_status
1445 ,ast.pay_system_status pay_system_status
1446 ,asg.period_of_service_id period_of_service_id
1447 ,asg.primary_flag primary_flag
1448 ,asg.last_update_date last_change_date
1449 FROM
1450 per_all_assignments_f asg
1451 ,per_assignment_status_types ast
1452 ,per_periods_of_service pos
1453 WHERE pos.period_of_service_id = asg.period_of_service_id
1454 AND ast.assignment_status_type_id = asg.assignment_status_type_id
1455 AND asg.assignment_type = 'E'
1456 AND trunc(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date;
1457 --
1458 -- Gather Statistics
1459 --
1460 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1461 --
1462 fnd_stats.gather_table_stats(l_schema,'HRI_MB_WMV');
1463 --
1464 END IF;
1465 --
1466 -- Insert process execution stats
1467 --
1468 hri_bpl_conc_log.log_process_end(
1469 p_status => TRUE,
1470 p_period_from => TRUNC(SYSDATE),
1471 p_period_to => TRUNC(SYSDATE),
1472 p_attribute1 => g_collect_fte,
1473 p_attribute2 => g_collect_head,
1474 p_attribute3 => g_full_refresh);
1475 --
1476 END shared_hrms_dflt_prcss;
1477 --
1478 -- ----------------------------------------------------------------------------
1479 -- shared_hrms_dflt_prcss (OVERLOADED)
1480 -- Default process executed when PYUGEN is not available.
1481 -- ============================================================================
1482 -- This process will be launched by the package HRI_BPL_PYUGEN_WRAPPER
1483 -- whenever it detects PYUGEN is not installed.
1484 --
1485 -- The parameters of this function are standard for all default processes
1486 -- called where PYUGEN does not exist. This particular package IGNORES THEM
1487 --
1488 PROCEDURE shared_hrms_dflt_prcss
1489 (
1490 errbuf OUT NOCOPY VARCHAR2
1491 ,retcode OUT NOCOPY NUMBER
1492 ,p_collect_from_date IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
1493 ,p_collect_to_date IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
1494 ,p_full_refresh IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
1495 ,p_attribute1 IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
1496 ,p_attribute2 IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
1497 )
1498 IS
1499 --
1500 BEGIN
1501 --
1502 -- Do not pass throuh IN parameters, as they are not used.
1503 --
1504 shared_hrms_dflt_prcss;
1505 --
1506 EXCEPTION
1507 WHEN OTHERS
1508 THEN
1509 --
1510 errbuf := SQLERRM;
1511 retcode := SQLCODE;
1512 RAISE;
1513 --
1514 --
1515 END shared_hrms_dflt_prcss;
1516 --
1517 END hri_opl_wmv;
[Home] [Help]