1 PACKAGE BODY HRI_OPL_ASGN_EVENTS AS
2 /* $Header: hrioaevt.pkb 120.21 2007/01/12 09:10:04 jtitmas noship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 -- Process flow
6 -- ============
7 --
8 -- BEFORE MULTI-THREADING
9 -- ----------------------
10 -- PRE_PROCESS
11 -- - Shared HR mode
12 -- - Truncates table
13 -- - Repopulates table with data for current period of work
14 -- as of system date
15 --
16 -- - Other modes
17 -- - returns list of all assignments to process. These are
18 -- split into chunks by multi-threading master process
19 -- - disables WHO trigger
20 -- - checks seeded fast formulas are compiled
21 -- - Full Refresh
22 -- - stores index definitions and drops indexes (full refresh)
23 -- - truncates table
24 -- - Incremental Refresh
25 -- - updates event queue with any period of work band change events
26 --
27 -- MULTI-THREADING
28 -- ---------------
29 -- 1) PROCESS_RANGE
30 -- - Gets a range of objects (assignments) to process
31 -- - Calls COLLECT for each one
32 -- - bulk inserts any remaining rows
33 --
34 -- 2) COLLECT
35 -- - Entry point for processing each assignment
36 -- - Calls below procedures to process the assignment
37 -- - Checks whether enough rows are stored up to bulk insert
38 --
39 -- 4) IDENTIFY_ASSIGNMENT_CHANGES
40 -- - creates an assignment change history array for a given assignment
41 -- - inserts a record in the combined event list array for each change
42 --
43 -- 5) IDENTIFY_ABV_CHANGES
44 -- - inserts a record in the combined event list array for each ABV change
45 -- for Headcount or FTE
46 --
47 -- 6) FILL_GAPS_IN_ABV_HISTORY
48 -- - closes the gap where there is no data for an assignment in
49 -- PER_ASSIGNMENT_BUDGET_VALUES_F. This is achieved by using fast formula
50 -- at every point where theres an assignment change to calculate the value
51 --
52 -- 7) IDENTIFY_SALARY_CHANGES
53 -- - creates a list of salary changes
54 -- - inserts a record in the combined event list PLSQL table for each change
55 --
56 -- 8) IDENTIFY_PERF_RATING_CHANGES
57 -- - creates a list of performance rating changes
58 -- - inserts a record in the combined event list PLSQL table for each change
59 --
60 -- 9) IDENTIFY_POW_BAND_CHANGES
61 -- - creates a list of period of work changes
62 -- - inserts a record in the combined event list PLSQL table for each change
63 --
64 -- 10) SET_PREVIOUS_VALUES
65 -- - Full Refresh
66 -- - set up a default record for values before the refresh start date
67 -- - Incremental Refresh
68 -- - sets the previous values of various columns as they exists one day
69 -- before the incremental refresh
70 --
71 -- 11) MERGE_AND_INSERT_DATA
72 -- - sets the indicators
73 -- - merges the data in the master table into a PL/SQL table ready to insert
74 -- into the main database table HRI_MB_ASGN_EVENTS
75 --
76 -- 12) UPDATE_END_RECORD (Incremental only)
77 -- - During incremental proceesing it end dates the assignment
78 -- records for the assignment that ovelap the earliest event date
79 --
80 -- 3) DELETE_RECORDS (Incremental only)
81 -- - During Incremental processing deletes all the records from the table
82 -- HRI_MB_ASGN_EVENTS that start on or after the refresh start date for
83 -- each assignment in the range
84 --
85 -- 3) BULK_INSERT
86 -- - Bulk inserts stored rows once per range
87 --
88 -- AFTER MULTI-THREADING
89 -- ----------------------
90 -- POST_PROCESS
91 -- - Logs process end (success/failure)
92 --
93 -- - Enables WHO trigger
94 --
95 -- - Purges event queue
96 --
97 -- - Full Refresh
98 -- - Recreates indexes that were dropped in PRE_PROCESS
99 -- - Gathers stats
100 --
101 --
102 -- Event Merging
103 -- =============
104 -- All events processed occur on or after the global collection start date
105 -- (collect from date). So each event date can be converted to a positive
106 -- number by calculating the number of days between the event date and the
107 -- collection start date. This number is then used to index the master table
108 -- by date so that events which occur on the same date are merged.
109 --
110 -- -----------------------------------------------------------------------------
111 --
112 -- MAIN PL/SQL TABLE FOR BULK INSERT
113 TYPE g_asgn_events_tab_type IS TABLE OF hri_mb_asgn_events_ct%ROWTYPE
114 INDEX BY BINARY_INTEGER;
115 --
116 -- Number table type with varchar2 indexing.
117 --
118 TYPE g_index_by_varchar2_num_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
119 --
120 -- Type for service dates containing hire date, termination date, secondary
121 -- assignment start date, secondary assignment end date, primary assignment
122 -- start date and primary assignment end date.
123 --
124 TYPE g_asg_date_type IS RECORD
125 (hire_date DATE
126 ,termination_date DATE
127 ,post_hire_asgn_start_date DATE
128 ,pre_sprtn_asgn_end_date DATE
129 ,start_date_active DATE
130 ,end_date_active DATE
131 ,pow_start_date_adj DATE);
132 --
133 -- Type for merging the assignment, ABV ,salary, performance and period of work events.
134 --
135 TYPE g_master_record IS RECORD
136 (asg_index PLS_INTEGER
137 ,sal_index PLS_INTEGER
138 ,perf_index PLS_INTEGER
139 ,fte NUMBER
140 ,headcount NUMBER
141 ,primary_flag VARCHAR2(30)
142 ,rtrspctv_strt_ind PLS_INTEGER
143 ,asg_evt_ind PLS_INTEGER
144 ,sal_evt_ind PLS_INTEGER
145 ,perf_evt_ind PLS_INTEGER
146 ,fte_record_ind PLS_INTEGER
147 ,hdc_record_ind PLS_INTEGER
148 ,pow_evt_ind PLS_INTEGER
149 ,pow_band_sk_fk PLS_INTEGER
150 ,pow_extn_strt_dt DATE
151 ,prsntyp_evt_ind PLS_INTEGER
152 );
153 --
154 -- Type for ABV, ptu and period of work records containing info on event date before the
155 -- event occurs.
156 --
157 TYPE g_placeholder_rec IS RECORD
158 (fte NUMBER
159 ,fte_prv NUMBER
160 ,headcount NUMBER
161 ,headcount_prv NUMBER
162 ,pow_band_sk_fk NUMBER
163 ,pow_band_sk_fk_prv NUMBER
164 ,pow_extn_strt_dt DATE);
165 --
166 -- Type for various indexes used for storing the current indexes of the master
167 -- record table( while looping) as well as storing the previous indexes for
168 -- assignment, salary records and the index for next date.
169 --
170 TYPE g_index_record IS RECORD
171 (asg_index PLS_INTEGER
172 ,asg_index_prev PLS_INTEGER
173 ,sal_index PLS_INTEGER
174 ,sal_index_prev PLS_INTEGER
175 ,perf_index PLS_INTEGER
176 ,perf_index_prev PLS_INTEGER
177 ,date_index PLS_INTEGER
178 ,next_date_index PLS_INTEGER);
179 --
180 -- Type for various indicators, which gets set in the procedure set_indicators.
181 --
182 TYPE g_indicator_record IS RECORD
183 (asg_rtrspctv_strt_event_ind PLS_INTEGER
184 ,assignment_change_ind PLS_INTEGER
185 ,salary_change_ind PLS_INTEGER
186 --
187 -- Performance Indicators
188 --
189 ,perf_change_ind PLS_INTEGER
190 ,perf_band_change_ind PLS_INTEGER
191 --
192 -- POW Indicators
193 --
194 ,pow_band_change_ind PLS_INTEGER
195 ,headcount_gain_ind PLS_INTEGER
196 ,headcount_loss_ind PLS_INTEGER
197 ,fte_gain_ind PLS_INTEGER
198 ,fte_loss_ind PLS_INTEGER
199 ,contingent_ind PLS_INTEGER
200 ,employee_ind PLS_INTEGER
201 ,grade_change_ind PLS_INTEGER
202 ,job_change_ind PLS_INTEGER
203 ,position_change_ind PLS_INTEGER
204 ,location_change_ind PLS_INTEGER
205 ,organization_change_ind PLS_INTEGER
206 ,supervisor_change_ind PLS_INTEGER
207 ,worker_hire_ind PLS_INTEGER
208 ,post_hire_asgn_start_ind PLS_INTEGER
209 ,pre_sprtn_asgn_end_ind PLS_INTEGER
210 ,term_voluntary_ind PLS_INTEGER
211 ,term_involuntary_ind PLS_INTEGER
212 ,worker_term_ind PLS_INTEGER
213 ,start_asg_sspnsn_ind PLS_INTEGER
214 ,end_asg_sspnsn_ind PLS_INTEGER
215 ,promotion_ind PLS_INTEGER
216 --
217 -- Person Type Summarization Indicators
218 --
219 ,summarization_rqd_ind PLS_INTEGER
220 ,summarization_rqd_chng_ind PLS_INTEGER
221 ,summarization_rqd_chng_nxt_ind PLS_INTEGER
222 );
223 --
224 -- Type for storing previous records during incremental refresh
225 --
226 TYPE g_prv_record IS RECORD
227 (grade_prv_id NUMBER
228 ,job_prv_id NUMBER
229 ,location_prv_id NUMBER
230 ,organization_prv_id NUMBER
231 ,supervisor_prv_id NUMBER
232 ,position_prv_id NUMBER
233 ,primary_flag_prv VARCHAR2(30)
234 ,fte_prv NUMBER
235 ,headcount_prv NUMBER
236 ,anl_slry_prv NUMBER
237 ,anl_slry_currency_prv VARCHAR2(30)
238 --
239 -- Performance Records
240 --
241 ,perf_nrmlsd_rating_prv NUMBER
242 ,perf_band_prv NUMBER
243 ,fte_end_date_prv DATE
244 ,hdc_end_date_prv DATE
245 --
246 -- Period of work Record
247 --
248 ,pow_band_sk_fk_prv NUMBER
249 ,summarization_rqd_ind_prv NUMBER
250 ,row_id ROWID
251 );
252 --
253 -- Type for storing the different next indicator columns for updating the
254 -- table HRI_MB_ASGN_EVENTS_CT during incremental refresh
255 --
256 TYPE g_nxt_ind_record IS RECORD
257 (worker_term_nxt_ind PLS_INTEGER
258 ,term_voluntary_nxt_ind PLS_INTEGER
259 ,term_involuntary_nxt_ind PLS_INTEGER
260 ,supervisor_change_nxt_ind PLS_INTEGER
261 ,pre_sprtn_asgn_end_nxt_ind PLS_INTEGER
262 ,separation_category_nxt VARCHAR2(30)
263 ,summarization_rqd_chng_nxt_ind PLS_INTEGER
264 );
265 --
266 -- Table type for merging the assignment, ABV and salary events. This is
267 -- loaded in the procedures identify_assignment_changes, identify_abv_changes,
268 -- fill_gaps_in_abv_history, identify_salary_changes and merged in the
269 -- procedure merge_and_insert_data.
270 --
271 TYPE g_master_tab_type IS TABLE OF g_master_record INDEX BY BINARY_INTEGER;
272 --
273 -- Global variables representing parameters
274 --
275 g_refresh_start_date DATE;
276 g_refresh_end_date DATE;
277 g_collect_fte VARCHAR2(5);
278 g_collect_hdc VARCHAR2(5);
279 g_full_refresh VARCHAR2(5);
280 g_assignment_id NUMBER;
281 --
282 -- Global end of time date initialization from the package hr_general
283 --
284 g_end_of_time DATE;
285 --
286 -- Global DBI collection start date initialization
287 --
288 g_dbi_collection_start_date DATE;
289 --
290 -- Global to the value of the Adjusted Service Date calculation profile
291 --
292 g_adj_svc_profile VARCHAR2(30);
293 --
294 -- Bug 4105868: Global to store msg_sub_group
295 --
296 g_msg_sub_group VARCHAR2(400);
297 --
298 -- Global flag which determines the existence of materialized view logs
299 --
300 g_mv_log_exists_flag VARCHAR2(1);
301 g_drop_mv_log VARCHAR2(30);
302 --
303 -- Global Variable for checking if performance rating is to be collected
304 --
305 g_collect_perf_rating VARCHAR2(30);
306 g_collect_prsn_typ VARCHAR2(30);
307 --
308 -- Global variable for storing the manner in which the appraisals are stored
309 --
310 g_perf_query VARCHAR2(10000);
311 --
312 -- Global HRI Multithreading Array
313 --
314 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
315 --
316 -- Global warning indicator
317 --
318 g_raise_warning VARCHAR2(1);
319 --
320 -- Stores the value to be stored in the performance band columns for not rated records
321 --
322 g_perf_not_rated_id NUMBER;
323 --
324 g_rtn VARCHAR2(200);
325 --
326 -- Global Variable which is set if the person type has been classigied as a CWK in
327 -- the ptu dimension includes. It is used to detemine if the extension period is to
328 -- be calculated for the asg
329 --
330 g_cwk_asg BOOLEAN;
331 --
332 -- Globals for DBI
333 --
334 g_implement_dbi VARCHAR2(30);
335 --
336 -- Globals for OBIEE
337 --
338 g_implement_obiee VARCHAR2(30);
339 g_implement_obiee_orgh VARCHAR2(30);
340 g_implement_obiee_mgrh VARCHAR2(30);
341 --
342 -- Exceptions
343 --
344 no_assignment_record_found EXCEPTION;
345 --
346 -- Forward Declaration of procedures
347 --
348 PROCEDURE process_range(
349 p_object_range_id IN NUMBER
350 ,p_start_object_id IN NUMBER
351 ,p_end_object_id IN NUMBER ) ;
352 --
353 -- -----------------------------------------------------------------------------
354 -- Inserts row into concurrent program log
355 -- -----------------------------------------------------------------------------
356 --
357 PROCEDURE output(p_text VARCHAR2) IS
358 --
359 BEGIN
360 --
361 -- Bug 4105868: Collection Diagnostics
362 --
363 HRI_BPL_CONC_LOG.output(p_text);
364 --
365 END output;
366 --
367 -- -----------------------------------------------------------------------------
368 -- Inserts row into concurrent program log if debugging is enabled
369 -- -----------------------------------------------------------------------------
370 --
371 PROCEDURE dbg(p_text VARCHAR2) IS
372 --
373 BEGIN
374 --
375 -- Bug 4105868: Collection Diagnostics
376 --
377 HRI_BPL_CONC_LOG.dbg(p_text);
378 --
379 END dbg;
380 --
381 --
382 -- ----------------------------------------------------------------------------
383 -- Adds change records to workforce events queue
384 -- ----------------------------------------------------------------------------
385 --
386 PROCEDURE populate_wrkfc_evt_eq IS
387
388 BEGIN
389
390 -- Only insert event queue records if OBIEE is implemented
391 IF g_implement_obiee = 'Y' THEN
392
393 INSERT INTO hri_eq_wrkfc_evt
394 (assignment_id
395 ,erlst_evnt_effective_date)
396 SELECT
397 assignment_id
398 ,erlst_evnt_effective_date
399 FROM
400 hri_eq_asgn_evnts;
401
402 INSERT INTO hri_eq_wrkfc_mnth
403 (assignment_id
404 ,erlst_evnt_effective_date)
405 SELECT
406 assignment_id
407 ,erlst_evnt_effective_date
408 FROM
409 hri_eq_asgn_evnts;
410
411 END IF;
412
413 END populate_wrkfc_evt_eq;
414 --
415 --
416 -- ----------------------------------------------------------------------------
417 -- Adds change records to workforce events by organization hierarchy queue
418 -- ----------------------------------------------------------------------------
419 --
420 PROCEDURE populate_wrkfc_evt_orgh_eq IS
421
422 BEGIN
423
424 -- Only insert event queue records if OBIEE is implemented
425 IF g_implement_obiee_orgh = 'Y' THEN
426
427 INSERT INTO hri_eq_wrkfc_evt_orgh
428 (organization_id
429 ,erlst_evnt_effective_date)
430 -- Previous organization chains
431 SELECT /*+ ORDERED */
432 orgh.orgh_sup_organztn_fk
433 ,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
434 FROM
435 hri_eq_asgn_evnts eq
436 ,hri_mb_asgn_events_ct evt
437 ,hri_cs_orgh_ct orgh
438 WHERE eq.assignment_id = evt.assignment_id
439 AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
440 AND evt.organization_id = orgh.orgh_organztn_fk
441 UNION ALL
442 -- New organization chains
443 SELECT /*+ ORDERED */
444 orgh.orgh_sup_organztn_fk
445 ,GREATEST(eq.erlst_evnt_effective_date, asg.effective_start_date)
446 FROM
447 hri_eq_asgn_evnts eq
448 ,per_all_assignments_f asg
449 ,hri_cs_orgh_ct orgh
450 WHERE eq.assignment_id = asg.assignment_id
451 AND eq.erlst_evnt_effective_date <= asg.effective_end_date
452 AND asg.organization_id = orgh.orgh_organztn_fk;
453
454 END IF;
455
456 END populate_wrkfc_evt_orgh_eq;
457 --
458 --
459 -- ----------------------------------------------------------------------------
460 -- Adds change records to workforce events by manager hierarchy queue
461 -- ----------------------------------------------------------------------------
462 --
463 PROCEDURE populate_wrkfc_evt_mgrh_eq IS
464
465 BEGIN
466
467 -- Only insert event queue records if OBIEE is implemented
468 IF g_implement_obiee_mgrh = 'Y' THEN
469
470 INSERT INTO hri_eq_wrkfc_evt_mgrh
471 (sup_person_id
472 ,erlst_evnt_effective_date
473 ,source_code)
474 -- Previous manager chains
475 SELECT /*+ ORDERED */
476 suph.sup_person_id
477 ,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
478 ,'ASG_EVENT_PREV'
479 FROM
480 hri_eq_asgn_evnts eq
481 ,hri_mb_asgn_events_ct evt
482 ,hri_cs_suph suph
483 WHERE eq.assignment_id = evt.assignment_id
484 AND evt.supervisor_id = suph.sub_person_id
485 AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
486 AND eq.erlst_evnt_effective_date - 1 <= suph.effective_end_date
487 UNION ALL
488 -- New manager chains
489 SELECT /*+ ORDERED */
490 suph.sup_person_id
491 ,GREATEST(eq.erlst_evnt_effective_date, asg.effective_start_date)
492 ,'ASG_EVENT_CURR'
493 FROM
494 hri_eq_asgn_evnts eq
495 ,per_all_assignments_f asg
496 ,hri_cs_suph suph
497 WHERE eq.assignment_id = asg.assignment_id
498 AND eq.erlst_evnt_effective_date <= asg.effective_end_date
499 AND asg.supervisor_id = suph.sub_person_id
500 AND eq.erlst_evnt_effective_date <= suph.effective_end_date;
501
502 END IF;
503
504 END populate_wrkfc_evt_mgrh_eq;
505 --
506 -- ----------------------------------------------------------------------------
507 -- POPULATE_ASG_DELTA_EQ (4259598 Incremental Changes)
508 -- This procedure inserts all records from the assignment event queue into the
509 -- assignment event delta queue, which is used to incrementally refresh
510 -- the assignment delta table
511 -- ----------------------------------------------------------------------------
512 --
513 PROCEDURE populate_asg_delta_eq IS
514 --
515 BEGIN
516 --
517 -- 4259598 Incremental Changes
518 -- Populate the assignment event delta queue using which the assignment delta
519 -- table can be refrshed incrementally. It should be noted that any point in
520 -- time there should only be one record for an assingment in the event queue
521 -- which contains the earliest event date for the assignment. Therefore, if
522 -- a record exists for the asg then update the record otherwise, insert a
523 -- new record for the assignment
524 --
525 -- Only do if DBI is implemented
526 --
527 IF g_implement_dbi = 'Y' THEN
528
529 MERGE INTO hri_eq_asg_sup_wrfc delta_eq
530 USING (SELECT assignment_id,
531 erlst_evnt_effective_date,
532 'ASG_EVENT' source_type
533 FROM hri_eq_asgn_evnts) asg_eq
534 ON ( delta_eq.source_type = 'ASG_EVENT'
535 AND asg_eq.assignment_id = delta_eq.source_id)
536 WHEN MATCHED THEN
537 UPDATE SET delta_eq.erlst_evnt_effective_date =
538 least(delta_eq.erlst_evnt_effective_date,asg_eq.erlst_evnt_effective_date)
539 WHEN NOT MATCHED THEN
540 INSERT (delta_eq.source_type,
541 delta_eq.source_id,
542 delta_eq.erlst_evnt_effective_date
543 )
544 VALUES (asg_eq.source_type,
545 asg_eq.assignment_id,
546 asg_eq.erlst_evnt_effective_date);
547 --
548 COMMIT;
549 --
550 END IF;
551 --
552 END populate_asg_delta_eq;
553 --
554 -- ----------------------------------------------------------------------------
555 -- PROCEDURE insert_pow_change_events inserts period of work band changes in
556 -- the assignment events queue. If a record for the assignment id already
557 -- exists in the event queue, it updates if the POW band change occurs at an
558 -- earlier date. This procedure collects the period of work changes for
559 -- employees and contingent workers.
560 -- ----------------------------------------------------------------------------
561 --
562 PROCEDURE insert_pow_change_events
563 IS
564 --
565 -- Cursor to fetch the Period of work band change records
566 -- for employees and contingent workers
567 -- 4086548 changed the SQL for performance reasons
568 -- Drive the query off asg events fact table. This also prevents the process
569 -- from creating stray events for terminated person's and unrequired assignments.
570 --
571 CURSOR c_pow_changes IS
572 SELECT DISTINCT asgn.assignment_id,
573 add_months(pow_start_date_adj, band_range_high) first_event
574 FROM hri_mb_Asgn_events_ct asgn,
575 hri_cs_pow_band_ct powb
576 WHERE asgn.pow_band_sk_fk = powb.pow_band_sk_pk
577 AND powb.band_range_high is not null
578 AND asgn.worker_term_ind = 0
579 AND g_refresh_start_date <= asgn.effective_change_end_date
580 AND asgn.pow_start_date_adj BETWEEN add_months(g_refresh_start_date,-powb.band_range_high) AND
581 add_months(SYSDATE,-powb.band_range_high);
582 --
583 -- PLSQL tables for storing the assignment id and the date when a POW band
584 -- change event occurs
585 --
586 l_pow_event_asg_id g_number_tab_type;
587 l_pow_event_date g_date_tab_type;
588 --
589 l_upd_asg_id NUMBER;
590 l_upd_asg_date DATE;
591 --
592 dml_errors EXCEPTION;
593 PRAGMA exception_init(dml_errors, -24381);
594 --
595 BEGIN
596 --
597 dbg('Fetching pow band change records after previous refresh ' ||
598 g_refresh_start_date || ' till ' || sysdate);
599 --
600 -- Open the cursor to fetch the period of work band changes
601 --
602 OPEN c_pow_changes;
603 FETCH c_pow_changes
604 BULK COLLECT INTO
605 l_pow_event_asg_id,
606 l_pow_event_date;
607 --
608 CLOSE c_pow_changes;
609 --
610 IF l_pow_event_date.count = 0 THEN
611 --
612 dbg('no incremental pow events created' );
613 RETURN;
614 --
615 END IF;
616 --
617 -- Loop though all the POW events and insert them in assignment events queue
618 -- Those assignments which are already present in the queue will cause an exception to be
619 -- raised. These assignments are handled in the exception section where they are updated
620 -- if the event date in the PLSQL table is before the event date in the evetns queue
621 --
622 dbg('g_refresh_start_date = '||g_refresh_start_date);
623 dbg('fetched '||l_pow_event_date.count||' records for writing to events queue');
624 --
625 FORALL l_loop IN l_pow_event_asg_id.FIRST..l_pow_event_asg_id.LAST SAVE EXCEPTIONS
626 --
627 INSERT /*+ APPEND */ INTO hri_eq_asgn_evnts(
628 assignment_id,
629 erlst_evnt_effective_date
630 )
631 VALUES(
632 l_pow_event_asg_id(l_loop),
633 l_pow_event_date(l_loop)
634 );
635 --
636 -- Commit the transaction
637 --
638 COMMIT;
639 --
640 dbg('done inserting into events queue');
641 EXCEPTION
642 --
643 -- The case when the insertion was not possible because the assignment id was already present
644 -- in the events queue.
645 --
646 WHEN dml_errors THEN
647 --
648 dbg('Updating some assignments');
649 --
650 -- Loop through all the assignments that could not ben inserted and caused
651 -- the exception to be raised. They are updated if the event date in the PLSQL
652 -- table is before the event date in the evetns queue
653 --
654 FOR l_loop IN 1..sql%bulk_exceptions.count LOOP
655 --
656 l_upd_asg_id := l_pow_event_asg_id(sql%bulk_exceptions(l_loop).error_index);
657 l_upd_asg_date := l_pow_event_date(sql%bulk_exceptions(l_loop).error_index);
658 --
659 UPDATE hri_eq_asgn_evnts
660 SET erlst_evnt_effective_date = least(erlst_evnt_effective_date,l_upd_asg_date)
661 WHERE assignment_id = l_upd_asg_id;
662 --
663 END LOOP;
664 --
665 COMMIT;
666 --
667 WHEN OTHERS THEN
668 --
669 dbg(SQLERRM);
670 --
671 -- Bug 4105868: Collection Diagnostic Call
672 --
673 g_msg_sub_group := NVL(g_msg_sub_group, 'INSERT_POW_CHANGE_EVENTS');
674 --
675 RAISE;
676 --
677 END insert_pow_change_events;
678 --
679 --
680 -- Procedure to update the summarization related indicators for person
681 -- hiding purpose when the fast formula HRI_MAP_ASG_SUMMARIZATION exists
682 --
683 PROCEDURE check_update_smrztn_rqrmnt
684 (p_effective_start_date IN DATE,
685 p_indicator_rec IN OUT NOCOPY g_indicator_record,
686 p_summarization_ind_prev IN NUMBER
687 ) IS
688 --
689 l_ff_exists NUMBER;
690 l_summarization_rqd VARCHAR2(1);
691 l_summarization_ind NUMBER;
692 --
693 BEGIN
694 --
695 -- Call fast formula HRI_MAP_ASG_SUMMARIZATION to get the summarization
696 -- indicator
697 --
698 l_summarization_rqd := hri_bpl_asg_summarization.is_summarization_rqd(g_assignment_id,
699 p_effective_start_date);
700 --
701 IF (l_summarization_rqd = 'N') THEN
702 --
703 l_summarization_ind := 0;
704 --
705 ELSE
706 --
707 l_summarization_ind := 1;
708 --
709 END IF;
710 --
711 --
712 -- Determine the summarization change indicator
713 --
714 IF p_indicator_rec.worker_term_ind <> 1 THEN
715 --
716 p_indicator_rec.summarization_rqd_ind := l_summarization_ind;
717 --
718 IF NVL(l_summarization_ind,-1) <>
719 NVL(p_summarization_ind_prev,-1) AND
720 p_indicator_rec.asg_rtrspctv_strt_event_ind <> 1 AND
721 p_indicator_rec.worker_hire_ind <> 1
722 THEN
723 --
724 p_indicator_rec.summarization_rqd_chng_ind := 1;
725 --
726 END IF;
727 --
728 ELSE
729 --
730 -- In case of termination set the summarization_rqd_ind to the previous
731 -- value so that the record is included in delta collection
732 --
733 p_indicator_rec.summarization_rqd_ind := p_summarization_ind_prev;
734 --
735 END IF;
736 --
737 END check_update_smrztn_rqrmnt;
738 --
739 --------------------------------------------------------------------------------
740 -- Bulk insert the rows stored in the master pl/sql table
741 --------------------------------------------------------------------------------
742 PROCEDURE bulk_insert_rows
743 (p_asgn_events_tab IN g_asgn_events_tab_type) IS
744
745 --
746 -- Row count
747 --
748 l_row_count PLS_INTEGER;
749 --
750 -- Primary Key
751 --
752 l_tab_assignment_id g_number_tab_type;
753 l_tab_change_date g_date_tab_type;
754 l_tab_change_end_date g_date_tab_type;
755 l_tab_pow_start_date_adj g_date_tab_type;
756 --
757 --Id Keys
758 --
759 l_tab_person_id g_number_tab_type;
760 --
761 -- Assignment related FK ID's which are present in the
762 -- assignment records after the event
763 --
764 l_tab_bus_grp_id g_number_tab_type;
765 l_tab_grade_id g_number_tab_type;
766 l_tab_job_id g_number_tab_type;
767 l_tab_location_id g_number_tab_type;
768 l_tab_organization_id g_number_tab_type;
769 l_tab_supervisor_id g_number_tab_type;
770 l_tab_position_id g_number_tab_type;
771 l_tab_primary_flag g_varchar2_tab_type;
772 l_tab_asg_type_code g_varchar2_tab_type;
773 --
774 -- Assignment releated FK ID's existing prior to the event
775 --
776 l_tab_grade_prv_id g_number_tab_type;
777 l_tab_job_prv_id g_number_tab_type;
778 l_tab_location_prv_id g_number_tab_type;
779 l_tab_organization_prv_id g_number_tab_type;
780 l_tab_supervisor_prv_id g_number_tab_type;
781 l_tab_position_prv_id g_number_tab_type;
782 l_tab_primary_flag_prv g_varchar2_tab_type;
783 --
784 -- Other assignment related values
785 --
786 l_tab_change_reason_code g_varchar2_tab_type;
787 l_tab_leaving_reason_code g_varchar2_tab_type;
788 l_tab_pow_days_on_event_date g_number_tab_type;
789 l_tab_pow_months_on_event_date g_number_tab_type;
790 l_tab_days_since_last_prmtn g_number_tab_type;
791 l_tab_months_since_last_prmtn g_number_tab_type;
792 --
793 -- Headcount related Measures and information for an assignment
794 --
795 l_tab_fte g_number_tab_type;
796 l_tab_fte_prv g_number_tab_type;
797 l_tab_headcount g_number_tab_type;
798 l_tab_headcount_prv g_number_tab_type;
799 --
800 -- Salary related Measures and information for a person
801 --
802 l_tab_anl_slry g_number_tab_type;
803 l_tab_anl_slry_prv g_number_tab_type;
804 l_tab_anl_slry_currency g_varchar2_tab_type;
805 l_tab_anl_slry_currency_prv g_varchar2_tab_type;
806 l_tab_pay_proposal_id g_number_tab_type;
807 --
808 -- Separation Category related measure for a person
809 --
810 l_tab_separation_category g_varchar2_tab_type;
811 l_tab_separation_category_nxt g_varchar2_tab_type;
812 --
813 -- Person Type Usage related measures
814 --
815 l_tab_prsntyp_sk_fk g_number_tab_type;
816 l_tab_summarization_rqd_ind g_number_tab_type;
817 l_tab_sum_rqd_chng_ind g_number_tab_type;
818 l_tab_sum_rqd_chng_nxt_ind g_number_tab_type;
819 --
820 -- Performance related measures and information for a person
821 --
822 l_tab_perf_nrmlsd_rating g_number_tab_type;
823 l_tab_perf_nrmlsd_rating_prv g_number_tab_type;
824 l_tab_perf_review_id g_number_tab_type;
825 l_tab_perf_review_type_cd g_varchar2_tab_type;
826 l_tab_performance_rating_cd g_varchar2_tab_type;
827 l_tab_perf_change_ind g_number_tab_type;
828 l_tab_perf_band g_number_tab_type;
829 l_tab_perf_band_prv g_number_tab_type;
830 l_tab_perf_band_change_ind g_number_tab_type;
831 --
832 -- Peiord of Work related measures and information for a person
833 --
834 l_tab_pow_start_date g_date_tab_type;
835 l_tab_pow_band_sk_fk g_number_tab_type;
836 l_tab_pow_band_prv_sk_fk g_number_tab_type;
837 l_tab_pow_extn_strt_dt g_date_tab_type;
838 --
839 l_tab_pow_change_ind g_number_tab_type;
840 l_tab_pow_band_change_ind g_number_tab_type;
841 --
842 -- Various Indicators
843 --
844 l_tab_asg_rtr_strt_event_ind g_number_tab_type;
845 l_tab_assignment_change_ind g_number_tab_type;
846 l_tab_salary_change_ind g_number_tab_type;
847 l_tab_headcount_gain_ind g_number_tab_type;
848 l_tab_headcount_loss_ind g_number_tab_type;
849 l_tab_fte_gain_ind g_number_tab_type;
850 l_tab_fte_loss_ind g_number_tab_type;
851 l_tab_contingent_ind g_number_tab_type;
852 l_tab_employee_ind g_number_tab_type;
853 l_tab_grade_change_ind g_number_tab_type;
854 l_tab_job_change_ind g_number_tab_type;
855 l_tab_position_change_ind g_number_tab_type;
856 l_tab_location_change_ind g_number_tab_type;
857 l_tab_organization_change_ind g_number_tab_type;
858 l_tab_supervisor_change_ind g_number_tab_type;
859 l_tab_worker_hire_ind g_number_tab_type;
860 l_tab_post_hire_asgn_start_ind g_number_tab_type;
861 l_tab_pre_sprtn_asgn_end_ind g_number_tab_type;
862 l_tab_term_voluntary_ind g_number_tab_type;
863 l_tab_term_involuntary_ind g_number_tab_type;
864 l_tab_worker_term_ind g_number_tab_type;
865 l_tab_start_asg_sspnsn_ind g_number_tab_type;
866 l_tab_end_asg_sspnsn_ind g_number_tab_type;
867 l_tab_worker_term_nxt_ind g_number_tab_type;
868 l_tab_term_voluntary_nxt_ind g_number_tab_type;
869 l_tab_term_involuntary_nxt_ind g_number_tab_type;
870 l_tab_sup_change_nxt_ind g_number_tab_type;
871 l_tab_pre_sep_asgn_end_nxt_ind g_number_tab_type;
872 l_tab_promotion_ind g_number_tab_type;
873 --
874 -- Variable to store the WHO information
875 --
876 l_user_id NUMBER;
877 l_current_time DATE;
878 --
879 BEGIN
880 --
881 -- Set row count
882 --
883 l_user_id := fnd_global.user_id;
884 l_current_time := SYSDATE;
885 --
886 IF (p_asgn_events_tab.EXISTS(1)) THEN
887 l_row_count := p_asgn_events_tab.LAST;
888 ELSE
889 l_row_count := 0;
890 END IF;
891 --
892 -- Transfer rows from record to PL/SQL table for bulk insert
893 --
894 FOR i IN 1..l_row_count LOOP
895 l_tab_assignment_id(i) := p_asgn_events_tab(i).assignment_id;
896 l_tab_change_date(i) := p_asgn_events_tab(i).effective_change_date;
897 l_tab_change_end_date(i) := p_asgn_events_tab(i).effective_change_end_date;
898 l_tab_person_id(i) := p_asgn_events_tab(i).person_id;
899 l_tab_bus_grp_id(i) := p_asgn_events_tab(i).business_group_id;
900 l_tab_grade_id(i) := p_asgn_events_tab(i).grade_id;
901 l_tab_job_id(i) := p_asgn_events_tab(i).job_id;
902 l_tab_location_id(i) := p_asgn_events_tab(i).location_id;
903 l_tab_organization_id(i) := p_asgn_events_tab(i).organization_id;
904 l_tab_supervisor_id(i) := p_asgn_events_tab(i).supervisor_id;
905 l_tab_position_id(i) := p_asgn_events_tab(i).position_id;
906 l_tab_primary_flag(i) := p_asgn_events_tab(i).primary_flag;
907 l_tab_asg_type_code(i) := p_asgn_events_tab(i).asg_type_code;
908 l_tab_pow_start_date_adj(i) := p_asgn_events_tab(i).pow_start_date_adj;
909 l_tab_grade_prv_id(i) := p_asgn_events_tab(i).grade_prv_id;
910 l_tab_job_prv_id(i) := p_asgn_events_tab(i).job_prv_id;
911 l_tab_location_prv_id(i) := p_asgn_events_tab(i).location_prv_id;
912 l_tab_organization_prv_id(i) := p_asgn_events_tab(i).organization_prv_id;
913 l_tab_supervisor_prv_id(i) := p_asgn_events_tab(i).supervisor_prv_id;
914 l_tab_position_prv_id(i) := p_asgn_events_tab(i).position_prv_id;
915 l_tab_primary_flag_prv(i) := p_asgn_events_tab(i).primary_flag_prv;
916 l_tab_change_reason_code(i) := p_asgn_events_tab(i).change_reason_code;
917 l_tab_leaving_reason_code(i) := p_asgn_events_tab(i).leaving_reason_code;
918 l_tab_pow_days_on_event_date(i) := p_asgn_events_tab(i).pow_days_on_event_date;
919 l_tab_pow_months_on_event_date(i) := p_asgn_events_tab(i).pow_months_on_event_date;
920 l_tab_days_since_last_prmtn(i) := p_asgn_events_tab(i).days_since_last_prmtn;
921 l_tab_months_since_last_prmtn(i) := p_asgn_events_tab(i).months_since_last_prmtn;
922 l_tab_fte(i) := p_asgn_events_tab(i).fte;
923 l_tab_fte_prv(i) := p_asgn_events_tab(i).fte_prv;
924 l_tab_headcount(i) := p_asgn_events_tab(i).headcount;
925 l_tab_headcount_prv(i) := p_asgn_events_tab(i).headcount_prv;
926 l_tab_anl_slry(i) := p_asgn_events_tab(i).anl_slry;
927 l_tab_anl_slry_prv(i) := p_asgn_events_tab(i).anl_slry_prv;
928 l_tab_anl_slry_currency(i) := p_asgn_events_tab(i).anl_slry_currency;
929 l_tab_anl_slry_currency_prv(i) := p_asgn_events_tab(i).anl_slry_currency_prv;
930 l_tab_pay_proposal_id(i) := p_asgn_events_tab(i).pay_proposal_id;
931 l_tab_separation_category(i) := p_asgn_events_tab(i).separation_category;
932 l_tab_separation_category_nxt(i) := p_asgn_events_tab(i).separation_category_nxt;
933 --
934 -- Person Type Related
935 --
936 l_tab_prsntyp_sk_fk(i) := p_asgn_events_tab(i).prsntyp_sk_fk;
937 l_tab_summarization_rqd_ind(i) := p_asgn_events_tab(i).summarization_rqd_ind;
938 l_tab_sum_rqd_chng_ind(i) := p_asgn_events_tab(i).summarization_rqd_chng_ind;
939 l_tab_sum_rqd_chng_nxt_ind(i) := p_asgn_events_tab(i).summarization_rqd_chng_nxt_ind;
940 --
941 -- Performance Related
942 --
943 l_tab_perf_nrmlsd_rating(i) := p_asgn_events_tab(i).perf_nrmlsd_rating;
944 l_tab_perf_nrmlsd_rating_prv(i) := p_asgn_events_tab(i).perf_nrmlsd_rating_prv;
945 l_tab_perf_review_id(i) := p_asgn_events_tab(i).performance_review_id;
946 l_tab_perf_review_type_cd(i) := p_asgn_events_tab(i).perf_review_type_cd;
947 l_tab_performance_rating_cd(i) := p_asgn_events_tab(i).performance_rating_cd;
948 l_tab_perf_change_ind(i) := p_asgn_events_tab(i).perf_rating_change_ind;
949 l_tab_perf_band(i) := p_asgn_events_tab(i).perf_band;
950 l_tab_perf_band_prv(i) := p_asgn_events_tab(i).perf_band_prv;
951 l_tab_perf_band_change_ind(i) := p_asgn_events_tab(i).perf_band_change_ind;
952 l_tab_pow_start_date(i) := p_asgn_events_tab(i).pow_start_date;
953 l_tab_pow_band_sk_fk(i) := p_asgn_events_tab(i).pow_band_sk_fk;
954 l_tab_pow_band_prv_sk_fk(i) := p_asgn_events_tab(i).pow_band_prv_sk_fk;
955 l_tab_pow_extn_strt_dt(i) := p_asgn_events_tab(i).pow_extn_strt_dt;
956 l_tab_pow_change_ind(i) := p_asgn_events_tab(i).pow_band_change_ind;
957 l_tab_pow_band_change_ind(i) := p_asgn_events_tab(i).pow_band_change_ind;
958 l_tab_asg_rtr_strt_event_ind(i) := p_asgn_events_tab(i).asg_rtrspctv_strt_event_ind;
959 l_tab_assignment_change_ind(i) := p_asgn_events_tab(i).assignment_change_ind;
960 l_tab_salary_change_ind(i) := p_asgn_events_tab(i).salary_change_ind;
961 l_tab_headcount_gain_ind(i) := p_asgn_events_tab(i).headcount_gain_ind;
962 l_tab_headcount_loss_ind(i) := p_asgn_events_tab(i).headcount_loss_ind;
963 l_tab_fte_gain_ind(i) := p_asgn_events_tab(i).fte_gain_ind;
964 l_tab_fte_loss_ind(i) := p_asgn_events_tab(i).fte_loss_ind;
965 l_tab_contingent_ind(i) := p_asgn_events_tab(i).contingent_ind;
966 l_tab_employee_ind(i) := p_asgn_events_tab(i).employee_ind;
967 l_tab_grade_change_ind(i) := p_asgn_events_tab(i).grade_change_ind;
968 l_tab_job_change_ind(i) := p_asgn_events_tab(i).job_change_ind;
969 l_tab_position_change_ind(i) := p_asgn_events_tab(i).position_change_ind;
970 l_tab_location_change_ind(i) := p_asgn_events_tab(i).location_change_ind;
971 l_tab_organization_change_ind(i) := p_asgn_events_tab(i).organization_change_ind;
972 l_tab_supervisor_change_ind(i) := p_asgn_events_tab(i).supervisor_change_ind;
973 l_tab_worker_hire_ind(i) := p_asgn_events_tab(i).worker_hire_ind;
974 l_tab_post_hire_asgn_start_ind(i) := p_asgn_events_tab(i).post_hire_asgn_start_ind;
975 l_tab_pre_sprtn_asgn_end_ind(i) := p_asgn_events_tab(i).pre_sprtn_asgn_end_ind;
976 l_tab_term_voluntary_ind(i) := p_asgn_events_tab(i).term_voluntary_ind;
977 l_tab_term_involuntary_ind(i) := p_asgn_events_tab(i).term_involuntary_ind;
978 l_tab_worker_term_ind(i) := p_asgn_events_tab(i).worker_term_ind;
979 l_tab_start_asg_sspnsn_ind(i) := p_asgn_events_tab(i).start_asg_sspnsn_ind;
980 l_tab_end_asg_sspnsn_ind(i) := p_asgn_events_tab(i).end_asg_sspnsn_ind;
981 l_tab_worker_term_nxt_ind(i) := p_asgn_events_tab(i).worker_term_nxt_ind;
982 l_tab_term_voluntary_nxt_ind(i) := p_asgn_events_tab(i).term_voluntary_nxt_ind;
983 l_tab_term_involuntary_nxt_ind(i) := p_asgn_events_tab(i).term_involuntary_nxt_ind;
984 l_tab_sup_change_nxt_ind(i) := p_asgn_events_tab(i).supervisor_change_nxt_ind;
985 l_tab_pre_sep_asgn_end_nxt_ind(i) := p_asgn_events_tab(i).pre_sprtn_asgn_end_nxt_ind;
986 l_tab_promotion_ind(i) := p_asgn_events_tab(i).promotion_ind;
987
988 END LOOP;
989 --
990 -- ------------------------------------------------------------------
991 -- Starting bulk insert of all assignment events identified
992 --
993 dbg('Inserting data into table');
994 --
995 FORALL i IN 1..l_row_count
996 INSERT INTO HRI_MB_ASGN_EVENTS_CT (
997 --
998 -- Unique key generated for the events fact
999 --
1000 event_id
1001 --
1002 -- Effective Dates
1003 --
1004 ,effective_change_date
1005 ,effective_change_end_date
1006 --
1007 -- Id Keys
1008 --
1009 ,assignment_id
1010 ,person_id
1011 --
1012 -- Assignment related FK ID's which are present in the
1013 -- assignment records after the event
1014 --
1015 ,business_group_id
1016 ,grade_id
1017 ,job_id
1018 ,location_id
1019 ,organization_id
1020 ,supervisor_id
1021 ,position_id
1022 ,primary_flag
1023 ,asg_type_code
1024 ,pow_start_date_adj
1025 --
1026 -- Period of work related changes
1027 --
1028 ,pow_start_date
1029 --
1030 -- Assignment releated FK ID's existing prior to the event
1031 --
1032 ,grade_prv_id
1033 ,job_prv_id
1034 ,location_prv_id
1035 ,organization_prv_id
1036 ,supervisor_prv_id
1037 ,position_prv_id
1038 ,primary_flag_prv
1039 --
1040 -- Other assignment related values
1041 --
1042 ,change_reason_code
1043 ,leaving_reason_code
1044 --
1045 -- Separation Category related information for a person
1046 --
1047 ,separation_category
1048 ,separation_category_nxt
1049 ,pow_days_on_event_date
1050 ,pow_months_on_event_date
1051 ,days_since_last_prmtn
1052 ,months_since_last_prmtn
1053 --
1054 -- Headcount related Measures and information for an assignment
1055 --
1056 ,fte
1057 ,fte_prv
1058 ,headcount
1059 ,headcount_prv
1060 --
1061 -- Salary related Measures and information for a person
1062 --
1063 ,anl_slry
1064 ,anl_slry_prv
1065 ,anl_slry_currency
1066 ,anl_slry_currency_prv
1067 ,pay_proposal_id
1068 --
1069 -- Performance Related measures and information for a person
1070 --
1071 ,perf_nrmlsd_rating
1072 ,perf_nrmlsd_rating_prv
1073 ,perf_band
1074 ,perf_band_prv
1075 ,performance_review_id
1076 ,perf_review_type_cd
1077 ,performance_rating_cd
1078 --
1079 -- Period of work related measure and information for a person
1080 --
1081 ,pow_band_sk_fk
1082 ,pow_band_prv_sk_fk
1083 ,pow_extn_strt_dt
1084 --
1085 -- Person type usage related measures
1086 --
1087 ,prsntyp_sk_fk
1088 ,summarization_rqd_ind
1089 ,summarization_rqd_chng_ind
1090 ,summarization_rqd_chng_nxt_ind
1091 --
1092 --
1093 -- Indicators
1094 --
1095 ,asg_rtrspctv_strt_event_ind
1096 ,assignment_change_ind
1097 ,salary_change_ind
1098 --
1099 -- Performance related indicators
1100 --
1101 ,perf_rating_change_ind
1102 ,perf_band_change_ind
1103 --
1104 -- Period of work related indicators
1105 --
1106 ,pow_band_change_ind
1107 --
1108 -- Various Indicators
1109 --
1110 ,headcount_gain_ind
1111 ,headcount_loss_ind
1112 ,fte_gain_ind
1113 ,fte_loss_ind
1114 ,contingent_ind
1115 ,employee_ind
1116 ,grade_change_ind
1117 ,job_change_ind
1118 ,position_change_ind
1119 ,location_change_ind
1120 ,organization_change_ind
1121 ,supervisor_change_ind
1122 ,worker_hire_ind
1123 ,post_hire_asgn_start_ind
1124 ,pre_sprtn_asgn_end_ind
1125 ,term_voluntary_ind
1126 ,term_involuntary_ind
1127 ,worker_term_ind
1128 ,start_asg_sspnsn_ind
1129 ,end_asg_sspnsn_ind
1130 ,worker_term_nxt_ind
1131 ,term_voluntary_nxt_ind
1132 ,term_involuntary_nxt_ind
1133 ,supervisor_change_nxt_ind
1134 ,pre_sprtn_asgn_end_nxt_ind
1135 ,promotion_ind
1136 ,last_update_date
1137 ,last_update_login
1138 ,last_updated_by
1139 ,created_by
1140 ,creation_date)
1141 VALUES
1142 --
1143 -- Unique key generated for the events fact
1144 --
1145 (hri_mb_asgn_events_ct_s.nextval
1146 --
1147 -- Effective Dates
1148 --
1149 ,l_tab_change_date(i)
1150 ,l_tab_change_end_date(i)
1151 --
1152 -- Id Keys
1153 --
1154 ,l_tab_assignment_id(i)
1155 ,l_tab_person_id(i)
1156 --
1157 -- Assignment related FK ID's which are present in the
1158 -- assignment records after the event
1159 --
1160 ,l_tab_bus_grp_id(i)
1161 ,l_tab_grade_id(i)
1162 ,l_tab_job_id(i)
1163 ,l_tab_location_id(i)
1164 ,l_tab_organization_id(i)
1165 ,l_tab_supervisor_id(i)
1166 ,l_tab_position_id(i)
1167 ,l_tab_primary_flag(i)
1168 ,l_tab_asg_type_code(i)
1169 ,l_tab_pow_start_date_adj(i)
1170 --
1171 -- Period of work start date
1172 --
1173 ,l_tab_pow_start_date(i)
1174 --
1175 -- Assignment releated FK ID's existing prior to the event
1176 --
1177 ,l_tab_grade_prv_id(i)
1178 ,l_tab_job_prv_id(i)
1179 ,l_tab_location_prv_id(i)
1180 ,l_tab_organization_prv_id(i)
1181 ,l_tab_supervisor_prv_id(i)
1182 ,l_tab_position_prv_id(i)
1183 ,l_tab_primary_flag_prv(i)
1184 --
1185 -- Other assignment related values
1186 --
1187 ,l_tab_change_reason_code(i)
1188 ,l_tab_leaving_reason_code(i)
1189 --
1190 -- Separation Category related information
1191 --
1192 ,l_tab_separation_category(i)
1193 ,l_tab_separation_category_nxt(i)
1194 ,l_tab_pow_days_on_event_date(i)
1195 ,l_tab_pow_months_on_event_date(i)
1196 ,l_tab_days_since_last_prmtn(i)
1197 ,l_tab_months_since_last_prmtn(i)
1198 --
1199 -- Headcount related Measures and information for an assignment
1200 --
1201 ,l_tab_fte(i)
1202 ,l_tab_fte_prv(i)
1203 ,l_tab_headcount(i)
1204 ,l_tab_headcount_prv(i)
1205 --
1206 -- Salary related Measures and information for a person
1207 --
1208 ,l_tab_anl_slry(i)
1209 ,l_tab_anl_slry_prv(i)
1210 ,l_tab_anl_slry_currency(i)
1211 ,l_tab_anl_slry_currency_prv(i)
1212 ,l_tab_pay_proposal_id(i)
1213 --
1214 -- Performance rating related measures
1215 --
1216 ,l_tab_perf_nrmlsd_rating(i)
1217 ,l_tab_perf_nrmlsd_rating_prv(i)
1218 ,l_tab_perf_band(i)
1219 ,l_tab_perf_band_prv(i)
1220 ,l_tab_perf_review_id(i)
1221 ,l_tab_perf_review_type_cd(i)
1222 ,l_tab_performance_rating_cd(i)
1223 --
1224 -- Period of work related measures
1225 --
1226 ,l_tab_pow_band_sk_fk(i)
1227 ,l_tab_pow_band_prv_sk_fk(i)
1228 ,l_tab_pow_extn_strt_dt(i)
1229 --
1230 -- Person type related measures
1231 --
1232 ,l_tab_prsntyp_sk_fk(i)
1233 ,l_tab_summarization_rqd_ind(i)
1234 ,l_tab_sum_rqd_chng_ind(i)
1235 ,l_tab_sum_rqd_chng_nxt_ind(i)
1236 --
1237 -- Various Indicators
1238 --
1239 ,l_tab_asg_rtr_strt_event_ind(i)
1240 ,l_tab_assignment_change_ind(i)
1241 ,l_tab_salary_change_ind(i)
1242 --
1243 -- Performance Rating related indicators
1244 --
1245 ,l_tab_perf_change_ind(i)
1246 ,l_tab_perf_band_change_ind(i)
1247 --
1248 -- Period of work related indicators
1249 --
1250 ,l_tab_pow_band_change_ind(i)
1251 --
1252 -- Various Indicators
1253 --
1254 ,l_tab_headcount_gain_ind(i)
1255 ,l_tab_headcount_loss_ind(i)
1256 ,l_tab_fte_gain_ind(i)
1257 ,l_tab_fte_loss_ind(i)
1258 ,l_tab_contingent_ind(i)
1259 ,l_tab_employee_ind(i)
1260 ,l_tab_grade_change_ind(i)
1261 ,l_tab_job_change_ind(i)
1262 ,l_tab_position_change_ind(i)
1263 ,l_tab_location_change_ind(i)
1264 ,l_tab_organization_change_ind(i)
1265 ,l_tab_supervisor_change_ind(i)
1266 ,l_tab_worker_hire_ind(i)
1267 ,l_tab_post_hire_asgn_start_ind(i)
1268 ,l_tab_pre_sprtn_asgn_end_ind(i)
1269 ,l_tab_term_voluntary_ind(i)
1270 ,l_tab_term_involuntary_ind(i)
1271 ,l_tab_worker_term_ind(i)
1272 ,l_tab_start_asg_sspnsn_ind(i)
1273 ,l_tab_end_asg_sspnsn_ind(i)
1274 ,l_tab_worker_term_nxt_ind(i)
1275 ,l_tab_term_voluntary_nxt_ind(i)
1276 ,l_tab_term_involuntary_nxt_ind(i)
1277 ,l_tab_sup_change_nxt_ind(i)
1278 ,l_tab_pre_sep_asgn_end_nxt_ind(i)
1279 ,l_tab_promotion_ind(i)
1280 ,l_current_time
1281 ,l_user_id
1282 ,l_user_id
1283 ,l_user_id
1284 ,l_current_time);
1285 --
1286 -- End of bulk insert of all assignment changes.
1287 -- ------------------------------------------------------------------
1288 dbg('Done insert ok');
1289 --
1290 END bulk_insert_rows;
1291
1292 --
1293 -- -----------------------------------------------------------------------------
1294 -- 5A Delete Records
1295 -- This Procedure deletes all records for the chunk of assignments that are
1296 -- on or later than the earliest change date for each assignment
1297 -- ----------------------------------------------------------------------------
1298 --
1299 PROCEDURE delete_records
1300 (p_start_assignment_id IN NUMBER,
1301 p_end_assignment_id IN NUMBER) IS
1302 --
1303 BEGIN
1304 --
1305 --
1306 dbg('Entering delete_records');
1307 --
1308 -- Delete all assingment event records for the events that have occurred on or
1309 -- after the refresh date.
1310 --
1311 DELETE FROM hri_mb_asgn_events_ct evt
1312 WHERE evt.rowid IN
1313 (SELECT evt2.rowid
1314 FROM hri_eq_asgn_evnts eq
1315 , hri_mb_asgn_events_ct evt2
1316 WHERE eq.assignment_id = evt2.assignment_id
1317 AND evt2.effective_change_date >= eq.erlst_evnt_effective_date
1318 AND eq.assignment_id BETWEEN p_start_assignment_id AND p_end_assignment_id);
1319 --
1320 dbg('Deleted records occuring on or after '||g_refresh_start_date);
1321 --
1322 --
1323 END delete_records;
1324 --
1325 -- -----------------------------------------------------------------------------
1326 -- 5B Identify Assignment Changes
1327 -- This Procedure creates an assignment change history array for a given
1328 -- assignment. It also inserts a record in the combined event list array
1329 -- for each change.
1330 -- ----------------------------------------------------------------------------
1331 --
1332 PROCEDURE identify_assignment_changes(
1333 p_date_master_tab OUT NOCOPY g_master_tab_type,
1334 p_asg_change_tab OUT NOCOPY g_asg_change_tab_type,
1335 p_asg_dates OUT NOCOPY g_asg_date_type) IS
1336 --
1337 -- Cursor to get the assignment details of the assignment_id for assignment
1338 -- type 'Employee' and 'Contingent'. The hiring date, termination date and
1339 -- leaving reason for 'Employee' assignments collected from the table
1340 -- per_periods_of_service while the same details for the 'Contingent'
1341 -- assignments are collected from the table per_periods_of_placement.
1342 --
1343 CURSOR asg_csr IS
1344 SELECT
1345 GREATEST(ptu.effective_start_date, asg.effective_start_date) effective_start_date
1346 ,least(ptu.effective_end_date, asg.effective_end_date) effective_end_date
1347 ,NVL(pos.date_start,pop.date_start) hire_date
1348 ,NVL(pos.actual_termination_date,pop.actual_termination_date)
1349 termination_date
1350 ,asg.assignment_id assignment_id
1351 ,asg.person_id person_id
1352 ,asg.business_group_id business_group_id
1353 ,NVL(asg.organization_id,-1) organization_id
1354 ,NVL(asg.location_id,-1) location_id
1355 ,NVL(asg.job_id,-1) job_id
1356 ,NVL(asg.grade_id,-1) grade_id
1357 ,NVL(asg.position_id,-1) position_id
1358 ,NVL(asg.supervisor_id,-1) supervisor_id
1359 ,NVL(asg.primary_flag,'N') primary_flag
1360 ,asg.assignment_type assignment_type
1361 ,NVL(NVL(pos.leaving_reason,pop.termination_reason),'NA_EDW')
1362 separation_reason_code
1363 ,NVL(asg.change_reason,'NA_EDW') assignment_reason_code
1364 ,ast.per_system_status assignment_status_code
1365 ,NVL(asg.payroll_id,-1) payroll_id
1366 ,NVL(asg.pay_basis_id,-1) pay_basis_id
1367 ,pos.adjusted_svc_date pow_start_date_adj
1368 ,hpt.prsntyp_sk_pk prsntyp_sk_fk
1369 ,nvl(decode(hpt.include_flag_code,'Y',1,0),1) summarization_rqd_ind
1370 ,hpt.wkth_wktyp_code wkth_wktyp_code
1371 FROM per_all_assignments_f asg
1372 ,per_assignment_status_types ast
1373 ,per_periods_of_service pos
1374 ,per_periods_of_placement pop
1375 ,per_person_type_usages_f ptu
1376 ,hri_cs_prsntyp_ct hpt
1377 WHERE asg.assignment_id = g_assignment_id
1378 AND ast.assignment_status_type_id = asg.assignment_status_type_id
1379 AND pos.period_of_service_id(+) = asg.period_of_service_id
1380 AND pop.person_id(+) = asg.person_id
1381 AND pop.date_start(+) = asg.period_of_placement_date_start
1382 AND ast.per_system_status <> 'TERM_ASSIGN'
1383 AND asg.assignment_type IN ('E','C')
1384 --
1385 -- Need assignment details on refresh date - 1 otherwise it would be
1386 -- difficult to tell whether an assignment that starts on refresh start
1387 -- date was an assignment start or an assignment change
1388 --
1389 AND (asg.effective_start_date >= (g_refresh_start_date - 1)
1390 OR (g_refresh_start_date - 1) BETWEEN asg.effective_start_date
1391 AND asg.effective_end_date)
1392 AND (asg.effective_start_date between ptu.effective_start_date and ptu.effective_end_date OR
1393 ptu.effective_start_date between asg.effective_start_date and asg.effective_end_date)
1394 AND ptu.person_id = asg.person_id
1395 AND hpt.person_type_id = ptu.person_type_id
1396 AND hpt.employment_category_code = nvl(asg.employment_category,'NA_EDW')
1397 AND hpt.primary_flag_code = nvl(asg.primary_flag,'NA_EDW')
1398 AND hpt.assignment_type_code = asg.assignment_type
1399 ORDER BY 1;
1400 --
1401 -- ---------------------------------------------------------------------------
1402 -- Local Package Variables - reset every time procedure is called
1403 --
1404 -- Local PL/SQL tables to fetch cursor
1405 -- Note in 9i bulk fetch directly into table of records is supported
1406 --
1407 l_asg_change_date g_date_tab_type;
1408 l_asg_change_end_date g_date_tab_type;
1409 l_asg_hire_date g_date_tab_type;
1410 l_asg_termination_date g_date_tab_type;
1411 l_pow_start_date_adj g_date_tab_type;
1412 l_asg_assignment_id g_number_tab_type;
1413 l_asg_person_id g_number_tab_type;
1414 l_asg_business_group_id g_number_tab_type;
1415 l_asg_organization_id g_number_tab_type;
1416 l_asg_location_id g_number_tab_type;
1417 l_asg_job_id g_number_tab_type;
1418 l_asg_grade_id g_number_tab_type;
1419 l_asg_position_id g_number_tab_type;
1420 l_asg_supervisor_id g_number_tab_type;
1421 l_asg_primary_flag g_varchar2_tab_type;
1422 l_asg_type g_varchar2_tab_type;
1423 l_asg_leaving_reason_code g_varchar2_tab_type;
1424 l_asg_change_reason_code g_varchar2_tab_type;
1425 l_asg_status_code g_varchar2_tab_type;
1426 l_payroll_id g_number_tab_type;
1427 l_pay_basis_id g_number_tab_type;
1428 l_prsntyp_sk_fk g_number_tab_type;
1429 l_summarization_rqd_ind g_number_tab_type;
1430 l_wkth_wktyp_code g_varchar2_tab_type;
1431 --
1432 -- Variable to collect the total number of records in the assignment cursor
1433 --
1434 l_asg_no_records PLS_INTEGER;
1435 --
1436 -- Index variables used to load the master record table for assignment changes
1437 --
1438 l_asg_index PLS_INTEGER;
1439 l_date_index PLS_INTEGER;
1440 --
1441 -- Indicator variable which determmines if an assignment started before the DBI
1442 -- collection start date
1443 --
1444 l_rtrspctv_strt_ind PLS_INTEGER := 0;
1445 --
1446 -- Indicator variable which determines if an assignment event has occured on
1447 -- the relevant date
1448 --
1449 l_asg_evt_ind PLS_INTEGER := 1;
1450 --
1451 BEGIN
1452 --
1453 dbg('Entering identify_assignment_changes');
1454 --
1455 -- Bulk load cursor into PLSQL table
1456 --
1457 OPEN asg_csr;
1458 FETCH asg_csr BULK COLLECT INTO
1459 l_asg_change_date
1460 ,l_asg_change_end_date
1461 ,l_asg_hire_date
1462 ,l_asg_termination_date
1463 ,l_asg_assignment_id
1464 ,l_asg_person_id
1465 ,l_asg_business_group_id
1466 ,l_asg_organization_id
1467 ,l_asg_location_id
1468 ,l_asg_job_id
1469 ,l_asg_grade_id
1470 ,l_asg_position_id
1471 ,l_asg_supervisor_id
1472 ,l_asg_primary_flag
1473 ,l_asg_type
1474 ,l_asg_leaving_reason_code
1475 ,l_asg_change_reason_code
1476 ,l_asg_status_code
1477 ,l_payroll_id
1478 ,l_pay_basis_id
1479 ,l_pow_start_date_adj
1480 ,l_prsntyp_sk_fk
1481 ,l_summarization_rqd_ind
1482 ,l_wkth_wktyp_code;
1483 --
1484 l_asg_no_records := asg_csr%ROWCOUNT;
1485 --
1486 CLOSE asg_csr;
1487 --
1488 -- Bail out if no assignment rows are returned
1489 --
1490 IF (l_asg_no_records = 0
1491 OR l_asg_no_records IS NULL) THEN
1492 --
1493 dbg('No records founds therefore exiting');
1494 --
1495 RAISE NO_ASSIGNMENT_RECORD_FOUND;
1496 --
1497 ELSE
1498 -- Translate to table of records
1499 FOR i IN 1..l_asg_no_records LOOP
1500 p_asg_change_tab(i).change_date := l_asg_change_date(i);
1501 p_asg_change_tab(i).change_end_date := l_asg_change_end_date(i);
1502 p_asg_change_tab(i).hire_date := l_asg_hire_date(i);
1503 p_asg_change_tab(i).termination_date := l_asg_termination_date(i);
1504 p_asg_change_tab(i).assignment_id := l_asg_assignment_id(i);
1505 p_asg_change_tab(i).person_id := l_asg_person_id(i);
1506 p_asg_change_tab(i).business_group_id := l_asg_business_group_id(i);
1507 p_asg_change_tab(i).organization_id := l_asg_organization_id(i);
1508 p_asg_change_tab(i).location_id := l_asg_location_id(i);
1509 p_asg_change_tab(i).job_id := l_asg_job_id(i);
1510 p_asg_change_tab(i).grade_id := l_asg_grade_id(i);
1511 p_asg_change_tab(i).position_id := l_asg_position_id(i);
1512 p_asg_change_tab(i).supervisor_id := l_asg_supervisor_id(i);
1513 p_asg_change_tab(i).primary_flag := l_asg_primary_flag(i);
1514 p_asg_change_tab(i).type := l_asg_type(i);
1515 p_asg_change_tab(i).leaving_reason_code := l_asg_leaving_reason_code(i);
1516 p_asg_change_tab(i).change_reason_code := l_asg_change_reason_code(i);
1517 p_asg_change_tab(i).status_code := l_asg_status_code(i);
1518 p_asg_change_tab(i).payroll_id := l_payroll_id(i);
1519 p_asg_change_tab(i).pay_basis_id := l_pay_basis_id(i);
1520 p_asg_change_tab(i).pow_start_date_adj := l_pow_start_date_adj(i);
1521 --
1522 -- Person Type Change
1523 --
1524 p_asg_change_tab(i).prsntyp_sk_fk := l_prsntyp_sk_fk(i);
1525 p_asg_change_tab(i).summarization_rqd_ind := l_summarization_rqd_ind(i);
1526 p_asg_change_tab(i).wkth_wktyp_code := l_wkth_wktyp_code(i);
1527 END LOOP;
1528 END IF;
1529 --
1530 -- Insert termination record if assignment is ended or separated
1531 -- and set assignment end date if it is the former
1532 --
1533 IF (p_asg_change_tab(l_asg_no_records).change_end_date < g_end_of_time) THEN
1534 --
1535 dbg('Inserting a termination record');
1536 --
1537 -- Increment counter to index the termination record
1538 --
1539 l_asg_no_records := l_asg_no_records + 1;
1540 --
1541 -- Add termination record for assignment end or separation
1542 --
1543 p_asg_change_tab(l_asg_no_records).change_date :=
1544 p_asg_change_tab(l_asg_no_records - 1).change_end_date + 1;
1545 p_asg_change_tab(l_asg_no_records).change_end_date := g_end_of_time;
1546 p_asg_change_tab(l_asg_no_records).hire_date :=
1547 p_asg_change_tab(l_asg_no_records - 1).hire_date;
1548 p_asg_change_tab(l_asg_no_records).termination_date :=
1549 p_asg_change_tab(l_asg_no_records - 1).termination_date;
1550 p_asg_change_tab(l_asg_no_records).assignment_id :=
1551 p_asg_change_tab(l_asg_no_records - 1).assignment_id;
1552 p_asg_change_tab(l_asg_no_records).person_id :=
1553 p_asg_change_tab(l_asg_no_records - 1).person_id;
1554 p_asg_change_tab(l_asg_no_records).business_group_id :=
1555 p_asg_change_tab(l_asg_no_records - 1).business_group_id;
1556 p_asg_change_tab(l_asg_no_records).leaving_reason_code :=
1557 p_asg_change_tab(l_asg_no_records - 1).leaving_reason_code;
1558 p_asg_change_tab(l_asg_no_records).change_reason_code :=
1559 p_asg_change_tab(l_asg_no_records - 1).change_reason_code;
1560 p_asg_change_tab(l_asg_no_records).primary_flag :=
1561 p_asg_change_tab(l_asg_no_records - 1).primary_flag;
1562 p_asg_change_tab(l_asg_no_records).type :=
1563 p_asg_change_tab(l_asg_no_records - 1).type;
1564 --
1565 p_asg_change_tab(l_asg_no_records).organization_id := -1;
1566 p_asg_change_tab(l_asg_no_records).location_id := -1;
1567 p_asg_change_tab(l_asg_no_records).job_id := -1;
1568 p_asg_change_tab(l_asg_no_records).grade_id := -1;
1569 p_asg_change_tab(l_asg_no_records).position_id := -1;
1570 p_asg_change_tab(l_asg_no_records).supervisor_id := -1;
1571 p_asg_change_tab(l_asg_no_records).status_code := 'NA_EDW';
1572 --
1573 -- If the assignment is ended and not separated then set the assignment end date
1574 -- (secondary assignment end date)
1575 --
1576 IF ((p_asg_change_tab(l_asg_no_records - 1).change_end_date <
1577 p_asg_change_tab(l_asg_no_records - 1).termination_date)
1578 OR (p_asg_change_tab(l_asg_no_records - 1).termination_date IS NULL)) THEN
1579 --
1580 p_asg_dates.pre_sprtn_asgn_end_date :=
1581 p_asg_change_tab(l_asg_no_records - 1).change_end_date;
1582 --
1583 END IF;
1584 --
1585 -- Set refresh range for salary and ABV to active assignment date range
1586 -- In case a person has an EMP_APL assignment, the person can have a
1587 -- primary assignment starting after the hire_date and also he can have
1588 -- a assignment budget value. In such a case if the start_date_active is
1589 -- not set to the assignment_start_date the abv and asg records in the date
1590 -- master table may go out of sync.
1591 --
1592 IF (p_asg_change_tab(1).change_date >=
1593 NVL(p_asg_change_tab(1).hire_date, p_asg_change_tab(1).change_date)) THEN
1594 --
1595 p_asg_dates.start_date_active :=
1596 GREATEST(p_asg_change_tab(1).change_date, g_refresh_start_date);
1597 --
1598 ELSE
1599 --
1600 p_asg_dates.start_date_active :=
1601 GREATEST(p_asg_change_tab(1).hire_date, g_refresh_start_date);
1602 END IF;
1603 --
1604 p_asg_dates.end_date_active :=
1605 p_asg_change_tab(l_asg_no_records - 1).change_end_date;
1606 --
1607 --
1608 -- If the assignment has not ended or seperated
1609 --
1610 ELSE
1611 --
1612 -- Set refresh range for salary and ABV events
1613 --
1614 IF (p_asg_change_tab(1).change_date >=
1615 NVL(p_asg_change_tab(1).hire_date, p_asg_change_tab(1).change_date)) THEN
1616 --
1617 p_asg_dates.start_date_active :=
1618 GREATEST(p_asg_change_tab(1).change_date, g_refresh_start_date);
1619 --
1620 ELSE
1621 --
1622 p_asg_dates.start_date_active :=
1623 GREATEST(p_asg_change_tab(1).hire_date, g_refresh_start_date);
1624 --
1625 END IF;
1626 --
1627 p_asg_dates.end_date_active := g_end_of_time;
1628 --
1629 END IF;
1630 --
1631 -- Set assignment table pointer at second record (see comment in asg_csr)
1632 -- if the first record ends before the refresh period
1633 --
1634 IF (p_asg_change_tab(1).change_end_date < g_refresh_start_date) THEN
1635 --
1636 l_asg_index := 2;
1637 --
1638 --
1639 -- If the first record does not end before the refresh period
1640 --
1641 ELSE
1642 --
1643 -- Point at first record
1644 --
1645 l_asg_index := 1;
1646 --
1647 -- If the first record starts after the hire date then it is an assignment
1648 -- start (new secondary assignment)
1649 --
1650 IF (p_asg_change_tab(1).change_date > p_asg_change_tab(l_asg_index).hire_date
1651 AND (p_asg_change_tab(1).change_date >= g_refresh_start_date)) THEN
1652 --
1653 -- Assign the secondary assignment start date
1654 --
1655 p_asg_dates.post_hire_asgn_start_date :=
1656 p_asg_change_tab(l_asg_index).change_date;
1657 --
1658 END IF;
1659 --
1660 END IF;
1661 --
1662 -- If the record starts before the dbi collection start date
1663 --
1664 IF (p_asg_change_tab(1).change_date < g_dbi_collection_start_date) THEN
1665 --
1666 -- Assign the retrospective start event indicator to 1.
1667 --
1668 l_rtrspctv_strt_ind := 1;
1669 --
1670 END IF;
1671 --
1672 -- If there is no assignment event on refresh start
1673 --
1674 IF (p_asg_change_tab(l_asg_index).change_date < g_refresh_start_date) THEN
1675 --
1676 -- Assign the assignment start date for the record as refresh start date
1677 --
1678 p_asg_change_tab(l_asg_index).change_date := g_refresh_start_date;
1679 --
1680 -- Set the assignment event indicator to 0
1681 --
1682 l_asg_evt_ind := 0;
1683 --
1684 END IF;
1685 --
1686 -- Store hire and termination dates
1687 --
1688 p_asg_dates.hire_date := p_asg_change_tab(1).hire_date;
1689 p_asg_dates.termination_date := p_asg_change_tab(1).termination_date;
1690 --
1691 -- The period of service is calculated based on the profile option
1692 -- HRI:Period of Service / Placement Date Start Source
1693 -- The asg delta table calculates the pow based on pow_start_date_adj
1694 -- Based on the value of the profile the columns should be populated
1695 -- as either hire date or least of hire date and adjusted service date
1696 --
1697 dbg('g_adj_svc_profile='||g_adj_svc_profile);
1698 IF g_adj_svc_profile = 'ADJSTD_SVC_DT' THEN
1699 --
1700 p_asg_dates.pow_start_date_adj := LEAST(NVL(p_asg_change_tab(1).pow_start_date_adj,
1701 p_asg_dates.hire_date),
1702 p_asg_dates.hire_date);
1703 --
1704 ELSE
1705 --
1706 p_asg_dates.pow_start_date_adj := p_asg_dates.hire_date;
1707 --
1708 END IF;
1709 --
1710 dbg('p_asg_dates.pow_start_date_adj='||p_asg_dates.pow_start_date_adj);
1711 --
1712 -- Transpose assignment records to master records PLSQL table
1713 -- Start of the loop
1714 --
1715 FOR i IN l_asg_index..l_asg_no_records LOOP
1716 --
1717 -- 3900275 The assignment table contains a lot of records which are not
1718 -- relevant in DBI. Collect only those records which contains changes that
1719 -- impacts DBI. Ignore all other records
1720 --
1721 IF (l_asg_index = i OR
1722 (p_asg_change_tab(i).organization_id <> p_asg_change_tab(i-1).organization_id OR
1723 p_asg_change_tab(i).location_id <> p_asg_change_tab(i-1).location_id OR
1724 p_asg_change_tab(i).job_id <> p_asg_change_tab(i-1).job_id OR
1725 p_asg_change_tab(i).grade_id <> p_asg_change_tab(i-1).grade_id OR
1726 p_asg_change_tab(i).position_id <> p_asg_change_tab(i-1).position_id OR
1727 p_asg_change_tab(i).supervisor_id <> p_asg_change_tab(i-1).supervisor_id OR
1728 p_asg_change_tab(i).primary_flag <> p_asg_change_tab(i-1).primary_flag OR
1729 p_asg_change_tab(i).type <> p_asg_change_tab(i-1).type OR
1730 p_asg_change_tab(i).leaving_reason_code <> p_asg_change_tab(i-1).leaving_reason_code OR
1731 p_asg_change_tab(i).change_reason_code <> p_asg_change_tab(i-1).change_reason_code OR
1732 p_asg_change_tab(i).status_code <> p_asg_change_tab(i-1).status_code OR
1733 p_asg_change_tab(i).payroll_id <> p_asg_change_tab(i-1).payroll_id OR
1734 p_asg_change_tab(i).pay_basis_id <> p_asg_change_tab(i-1).pay_basis_id OR
1735 p_asg_change_tab(i).prsntyp_sk_fk <> p_asg_change_tab(i-1).prsntyp_sk_fk
1736 )
1737 ) THEN
1738 --
1739 -- Calculate date index value as the difference between the assignment
1740 -- start date and refresh start date. Being the difference between two
1741 -- dates, this will be an integer
1742 --
1743 l_date_index := p_asg_change_tab(i).change_date - g_refresh_start_date;
1744 --
1745 -- Assignment of the assignment index value
1746 --
1747 p_date_master_tab(l_date_index).asg_index := i;
1748 --
1749 -- Assignment of primary flag value for use in ABV calculations (FF bypass)
1750 --
1751 p_date_master_tab(l_date_index).primary_flag := p_asg_change_tab(i).primary_flag;
1752 --
1753 -- Store a null ABV value for FTE and Headcounts for time being. These will
1754 -- be updated in procedures identify_abv_changes and fill_gaps_in_abv_history.
1755 --
1756 p_date_master_tab(l_date_index).fte := to_number(null);
1757 p_date_master_tab(l_date_index).headcount := to_number(null);
1758 --
1759 -- Set the retrospective start indicator in the master table based on the
1760 -- value of the indicator variable l_rtrspctv_strt_ind.
1761 --
1762 IF (l_asg_evt_ind = 0) THEN
1763 --
1764 p_date_master_tab(l_date_index).rtrspctv_strt_ind := l_rtrspctv_strt_ind;
1765 --
1766 END IF;
1767 --
1768 -- After first record this indicator will be 0
1769 --
1770 l_rtrspctv_strt_ind := 0;
1771 --
1772 -- Set the assignment event indicator in the master table based on the
1773 -- value of the indicator varable l_asg_evt_ind
1774 --
1775 p_date_master_tab(l_date_index).asg_evt_ind := l_asg_evt_ind;
1776 --
1777 -- After first record all records are assignment events
1778 --
1779 l_asg_evt_ind := 1;
1780 --
1781 -- In case if the person type dimension code of the the record is CWK
1782 -- then in order to determine the extension period, person's projected end date
1783 -- should be also be stored. Store it in a global variable which is used in
1784 -- identify_pow_band_changes procedure
1785 --
1786 IF p_asg_change_tab(i).wkth_wktyp_code = 'CWK' THEN
1787 --
1788 g_cwk_asg := TRUE;
1789 --
1790 END IF;
1791 --
1792 END IF;
1793 --
1794 END LOOP;
1795 --
1796 -- End of the loop for transposing the assignment record to master record
1797 -- PL/SQL table
1798 --
1799 dbg('Exiting identify_assignment_changes');
1800 --
1801 --
1802 -- When an exception is raised, the cursor is closed and the exception is passed
1803 -- out of this block and it is handled in the collect procedure where an entry
1804 -- of this is made in the concurrent log
1805 --
1806 EXCEPTION
1807 --
1808 WHEN NO_ASSIGNMENT_RECORD_FOUND THEN
1809 --
1810 -- Bug 4105868: Collection Diagnostic Call
1811 --
1812 g_msg_sub_group := NVL(g_msg_sub_group, 'IDENTIFY_ASSIGNMENT_CHANGES');
1813 --
1814 -- Raise the error and so that it is handled in the process_range procedure
1815 --
1816 RAISE NO_ASSIGNMENT_RECORD_FOUND;
1817 --
1818 WHEN OTHERS THEN
1819 --
1820 dbg('Error encountered in identify_assignment_changes');
1821 dbg(SQLERRM);
1822 --
1823 IF asg_csr%ISOPEN THEN
1824 --
1825 CLOSE asg_csr;
1826 --
1827 END IF;
1828 --
1829 g_msg_sub_group := NVL(g_msg_sub_group, 'IDENTIFY_ASSIGNMENT_CHANGES');
1830 --
1831 RAISE;
1832 --
1833 --
1834 END identify_assignment_changes;
1835 --
1836 -- ----------------------------------------------------------------------------
1837 -- 5C Identify ABV Changes
1838 -- Inserts a record in the combined event list array for each ABV change
1839 -- for Headcount or FTE.
1840 -- ----------------------------------------------------------------------------
1841 --
1842 PROCEDURE identify_abv_changes(
1843 p_asg_dates IN g_asg_date_type,
1844 p_date_master_tab IN OUT NOCOPY g_master_tab_type,
1845 p_prv_rec IN OUT NOCOPY g_prv_record) IS
1846 --
1847 -- Cursor for assignment budget value changes
1848 --
1849 CURSOR abv_csr
1850 IS
1851 SELECT
1852 abv.value value
1853 ,abv.unit unit
1854 ,abv.effective_start_date abv_start_date
1855 ,GREATEST(abv.effective_start_date, p_asg_dates.start_date_active)
1856 effective_start_date
1857 ,LEAST(abv.effective_end_date, p_asg_dates.end_date_active)
1858 effective_end_date
1859 ,DECODE(SIGN(p_asg_dates.start_date_active - abv.effective_start_date),1,0,1)
1860 abv_evt_ind
1861 FROM per_assignment_budget_values_f abv
1862 WHERE abv.assignment_id = g_assignment_id
1863 AND abv.unit IN ('HEAD','FTE')
1864 --
1865 -- Only ABVs in collection period needs to be selected
1866 --
1867 AND (abv.effective_start_date BETWEEN p_asg_dates.start_date_active AND p_asg_dates.end_date_active
1868 OR p_asg_dates.start_date_active BETWEEN abv.effective_start_date AND abv.effective_end_date)
1869 ORDER BY abv.unit, abv.effective_start_date;
1870 --
1871 -- Index variables for indexing the master records PL/SQL table
1872 --
1873 l_start_date_index PLS_INTEGER;
1874 l_end_date_index PLS_INTEGER;
1875 --
1876 --
1877 BEGIN
1878 --
1879 dbg('Entering identify_abv_changes');
1880 --
1881 -- Load up the assignment budget value records for the assignment
1882 --
1883 -- =============================================================================
1884 -- Start of loop for ABV cursor
1885 -- Loop through the ABV cursor records and add in ABV change events
1886 -- clashes with assignment events will be overwritten by the ABV events
1887 --
1888 FOR abv_rec IN abv_csr LOOP
1889 --
1890 -- Calculate index value as the difference between the ABV event
1891 -- start date and refresh start date. Being the difference between two
1892 -- dates, this will be an integer
1893 --
1894 l_start_date_index := abv_rec.effective_start_date - g_refresh_start_date;
1895 --
1896 -- If the ABV values for the record ends at end of time, do not process them
1897 --
1898 IF (abv_rec.effective_end_date < g_end_of_time) THEN
1899 --
1900 l_end_date_index := abv_rec.effective_end_date + 1 - g_refresh_start_date;
1901 --
1902 ELSE
1903 --
1904 l_end_date_index := to_number(null);
1905 --
1906 END IF;
1907 --
1908 -- Split out on measurement type and store in master date-indexed PLSQL table
1909 --
1910 IF abv_rec.unit = 'FTE'
1911 AND g_collect_fte = 'Y' THEN
1912 --
1913 -- If there is a FTE change event on DBI collection start date then do
1914 -- not set the retrospective start indicator.
1915 --
1916 IF abv_rec.abv_start_date = g_dbi_collection_start_date THEN
1917 --
1918 p_date_master_tab(l_start_date_index).rtrspctv_strt_ind := 0;
1919 --
1920 END IF;
1921 --
1922 -- Store new ABV value at start
1923 --
1924 p_date_master_tab(l_start_date_index).fte := abv_rec.value;
1925 --
1926 -- If an abv event has occurred then only set the fte record indicator to
1927 -- 1, else set the fte record indicator to 0
1928 --
1929 IF abv_rec.abv_evt_ind = 1 THEN
1930 --
1931 p_date_master_tab(l_start_date_index).fte_record_ind := 1;
1932 --
1933 ELSE
1934 --
1935 p_date_master_tab(l_start_date_index).fte_record_ind := 0;
1936 --
1937 END IF;
1938 --
1939 -- Blank out ABV value at end (this may be overwritten by next ABV start)
1940 --
1941 IF (l_end_date_index IS NOT NULL) THEN
1942 --
1943 p_date_master_tab(l_end_date_index).fte := to_number(null);
1944 --
1945 -- If an abv event has occurred then only set the fte record indicator
1946 -- to 1, else set the fte_record_indicator to 0
1947 --
1948 IF abv_rec.abv_evt_ind = 1 THEN
1949 --
1950 p_date_master_tab(l_end_date_index).fte_record_ind := 1;
1951 --
1952 ELSE
1953 --
1954 p_date_master_tab(l_end_date_index).fte_record_ind := 0;
1955 --
1956 END IF;
1957 --
1958 END IF;
1959 --
1960 ELSIF abv_rec.unit = 'HEAD' AND
1961 g_collect_hdc = 'Y'
1962 THEN
1963 --
1964 -- If there is a headcount change event on DBI collection start date then
1965 -- do not set the retrospective start indicator.
1966 --
1967 IF abv_rec.abv_start_date = g_dbi_collection_start_date THEN
1968 --
1969 p_date_master_tab(l_start_date_index).rtrspctv_strt_ind := 0;
1970 --
1971 END IF;
1972 --
1973 -- Store new ABV value at start
1974 --
1975 p_date_master_tab(l_start_date_index).headcount := abv_rec.value;
1976 --
1977 -- If an abv event has occurred then only set the headcount record indicator
1978 -- to 1, else set the headcount record indicator to 0
1979 --
1980 IF abv_rec.abv_evt_ind = 1 THEN
1981 --
1982 p_date_master_tab(l_start_date_index).hdc_record_ind := 1;
1983 --
1984 ELSE
1985 --
1986 p_date_master_tab(l_start_date_index).hdc_record_ind := 0;
1987 --
1988 END IF;
1989 --
1990 -- Blank out ABV value at end (this may be overwritten by next ABV start)
1991 --
1992 IF (l_end_date_index IS NOT NULL) THEN
1993 --
1994 p_date_master_tab(l_end_date_index).headcount := to_number(null);
1995 --
1996 -- If an abv event has occurred then only set the headcount record indicator
1997 -- to 1, else set the headcount record indicator to 0
1998 --
1999 IF abv_rec.abv_evt_ind = 1 THEN
2000 --
2001 p_date_master_tab(l_end_date_index).hdc_record_ind := 1;
2002 --
2003 ELSE
2004 --
2005 p_date_master_tab(l_end_date_index).hdc_record_ind := 0;
2006 --
2007 END IF;
2008 --
2009 END IF;
2010 --
2011 END IF;
2012 --
2013 END LOOP;
2014 --
2015 --
2016 -- End of loop for ABV cursor
2017 -- =============================================================================
2018 --
2019 --
2020 -- When an exception is raised, the cursor is closed and the exception is passed
2021 -- out of this block and it is handled in the collect procedure where an entry
2022 -- of this is made in the concurrent log
2023 --
2024 dbg('Exiting identify_abv_changes');
2025 --
2026 EXCEPTION
2027 --
2028 WHEN OTHERS THEN
2029 --
2030 dbg('Error encountered in identify_abv_changes');
2031 dbg(SQLERRM);
2032 --
2033 IF abv_csr%ISOPEN THEN
2034 --
2035 CLOSE abv_csr;
2036 --
2037 END IF;
2038 --
2039 -- Bug 4105868: Collection Diagnostic Call
2040 --
2041 g_msg_sub_group := NVL(g_msg_sub_group, 'IDENTIFY_ABV_CHANGES');
2042 --
2043 RAISE;
2044 --
2045 --
2046 END identify_abv_changes;
2047 --
2048 -- -----------------------------------------------------------------------------
2049 -- 5D Fill gaps in ABV history
2050 -- Where there is no data for an assignment in PER_ASSIGNMENT_BUDGET_VALUES_F
2051 -- close the gap. This is achieved by using fast formula at every point
2052 -- where there is an assignment change to calculate the value.
2053 -- ----------------------------------------------------------------------------
2054 --
2055 PROCEDURE fill_gaps_in_abv_history(
2056 p_date_master_tab IN OUT NOCOPY g_master_tab_type,
2057 p_business_group_id IN NUMBER,
2058 p_asg_dates IN g_asg_date_type) IS
2059 --
2060 -- Index variables
2061 --
2062 l_date_index PLS_INTEGER;
2063 --
2064 -- ABV values from ABV table
2065 --
2066 l_fte_active NUMBER;
2067 l_headcount_active NUMBER;
2068 --
2069 -- Assignment Primary Flag value
2070 --
2071 l_primary_flag VARCHAR2(30);
2072 --
2073 BEGIN
2074 --
2075 dbg('Entering fill_gaps_in_abv_history');
2076 --
2077 --
2078 -- Calculate any unknown Assignment Budget Values
2079 --
2080 -- ----------------------------------------------------------------------------
2081 -- At this point the date-indexed master PL/SQL table might look like:
2082 --
2083 -- Date Index ABV Value Indiacators Meaning
2084 -- ============ ========= ===================== ==========================
2085 -- 1) 01-Jan-00 (null) asg_evt_ind not null Asg started with no ABV
2086 -- 2) 01-Feb-00 (null) asg_evt_ind not null Asg change with no ABV
2087 -- 3) 01-Mar-00 1 fte_record_ind set to 1 ABV value started 01-Mar-00
2088 -- 4) 01-Apr-00 (null) asg_evt_ind not null Asg change with ABV
2089 -- 5) 01-May-00 (null) fte_record_ind set to 1 ABV value ended 30-Apr-00
2090 -- 6) 01-Jun-00 (null) asg_evt_ind not null Asg change with no ABV
2091 -- 7) 01-Jul-00 2 fte_record_ind set to 1 ABV value started 01-Jul-00
2092 -- 8) 01-Aug-00 3 fte_record_ind set to 1 ABV value updated 01-Aug-00
2093 -- 9) 01-Sep-00 (null) Not required Asg terminated 31-Aug-2000
2094 --
2095 -- The Fast Formula for the ABV should be run to calculate the ABV whenever
2096 -- there is no ABV value in the table. In the above example, this should be
2097 -- done:
2098 --
2099 -- 1) Yes - assignment started with no ABV because source is still Assignment
2100 -- 2) Yes - assignment changed with no ABV because source is still Assignment
2101 -- 4) No - ABV value from 3) is still active
2102 -- 5) Yes - ABV value from 3) ends here and needs recalculating on 01-May-00
2103 -- 6) Yes - still no ABV value
2104 -- 9) No - ABV value will be unchanged on termination
2105 --
2106 -- So the fast formula will be called 4 times in the above example. Each time
2107 -- it is called there is no ABV value so the "run_formula" flag is set to 'Y'
2108 -- so that the calc_abv formula does not recheck the table. The primary flag
2109 -- value is also passed in to allow the TEMPLATE_HEAD bypass performance
2110 -- enhancement.
2111 --
2112 -- -----------------------------------------------------------------------------
2113 --
2114 --
2115 -- Local variable Initialization
2116 --
2117 l_date_index := p_date_master_tab.FIRST;
2118 l_fte_active := TO_NUMBER(NULL);
2119 l_headcount_active := TO_NUMBER(NULL);
2120 l_primary_flag := NULL;
2121 --
2122 -- Start looping through master PL/SQL table in date order
2123 --
2124 WHILE (l_date_index IS NOT NULL) LOOP
2125 --
2126 -- Keep track of primary flag value every time it changes
2127 -- This is used in calling calc_abv to bypass TEMPLATE_HEAD
2128 --
2129 IF (p_date_master_tab(l_date_index).primary_flag IS NOT NULL) THEN
2130 --
2131 l_primary_flag := p_date_master_tab(l_date_index).primary_flag;
2132 --
2133 END IF;
2134 --
2135 -- --------------------------------------------------------------------------
2136 -- Start calculating FTE values
2137 --
2138 -- Need to calculate an FTE value using fast formula if
2139 -- active FTE record ends or assignment changes whilst
2140 -- there is no active FTE record
2141 --
2142 -- If the record is the termination record then force an
2143 -- ABV value of 0
2144 --
2145 IF (l_date_index = (p_asg_dates.end_date_active - g_refresh_start_date) + 1) THEN
2146 --
2147 p_date_master_tab(l_date_index).fte := 0;
2148 --
2149 --
2150 -- If there is an assignment event on this date and there exists no fte
2151 -- record on this date
2152 --
2153 ELSIF (((l_fte_active IS NULL AND
2154 p_date_master_tab(l_date_index).asg_evt_ind IS NOT NULL AND
2155 p_date_master_tab(l_date_index).fte_record_ind IS NULL)
2156 --
2157 -- Or the FTE value is ended
2158 --
2159 OR
2160 (p_date_master_tab(l_date_index).fte IS NULL AND
2161 p_date_master_tab(l_date_index).fte_record_ind IS NOT NULL))
2162 --
2163 -- And FTE collection is required
2164 --
2165 AND (g_collect_fte = 'Y')) THEN
2166 --
2167 -- No active FTE value
2168 --
2169 l_fte_active := to_number(null);
2170 --
2171 -- Calculate the FTE value through Fast Formula
2172 --
2173 p_date_master_tab(l_date_index).fte := hri_bpl_abv.calc_abv(
2174 p_assignment_id => g_assignment_id
2175 ,p_business_group_id => p_business_group_id
2176 ,p_budget_type => 'FTE'
2177 ,p_effective_date => (g_refresh_start_date + l_date_index)
2178 ,p_primary_flag => l_primary_flag
2179 ,p_run_formula => 'Y');
2180 --
2181 -- Otherwise there is a new active FTE value
2182 --
2183 ELSIF (p_date_master_tab(l_date_index).fte IS NOT NULL) THEN
2184 --
2185 -- Store the new active FTE value
2186 --
2187 l_fte_active := p_date_master_tab(l_date_index).fte;
2188 --
2189 END IF;
2190 --
2191 -- End calculating FTE values
2192 -- -------------------------------------------------------------------------
2193 --
2194 -- -------------------------------------------------------------------------
2195 -- Start calculating headcounts values
2196 --
2197 -- Need to calculate an HEAD value using fast formula if
2198 -- active HEAD record ends or assignment changes whilst
2199 -- there is no active HEAD record
2200 --
2201 -- If the record is the termination record then force an
2202 -- ABV value of 0
2203 --
2204 IF (l_date_index = (p_asg_dates.end_date_active - g_refresh_start_date) + 1) THEN
2205 --
2206 p_date_master_tab(l_date_index).headcount := 0;
2207 --
2208 --
2209 -- If there is an assignment event on this date and there exists no headcount
2210 -- record on this date
2211 --
2212 ELSIF ( ( (l_headcount_active IS NULL AND
2213 p_date_master_tab(l_date_index).asg_evt_ind IS NOT NULL AND
2214 p_date_master_tab(l_date_index).hdc_record_ind IS NULL
2215 )
2216 --
2217 -- Or the headcount value is ended
2218 --
2219 OR
2220 (p_date_master_tab(l_date_index).headcount IS NULL AND
2221 p_date_master_tab(l_date_index).hdc_record_ind IS NOT NULL
2222 )
2223 )
2224 --
2225 -- And collection of headcount value is required
2226 --
2227 AND
2228 (g_collect_hdc = 'Y')
2229 ) THEN
2230 --
2231 -- No active HEAD value
2232 --
2233 l_headcount_active := to_number(null);
2234 --
2235 -- Calculate the headcount value through the formula
2236 --
2237 p_date_master_tab(l_date_index).headcount := hri_bpl_abv.calc_abv(
2238 p_assignment_id => g_assignment_id
2239 ,p_business_group_id => p_business_group_id
2240 ,p_budget_type => 'HEAD'
2241 ,p_effective_date => (g_refresh_start_date + l_date_index)
2242 ,p_primary_flag => l_primary_flag
2243 ,p_run_formula => 'Y');
2244 --
2245 -- Otherwise there is a new active HEAD value
2246 --
2247 ELSIF (p_date_master_tab(l_date_index).headcount IS NOT NULL) THEN
2248 --
2249 -- Store the new active HEAD value
2250 --
2251 l_headcount_active := p_date_master_tab(l_date_index).headcount;
2252 --
2253 END IF;
2254 --
2255 -- End calculating headcount values
2256 -- -------------------------------------------------------------------------
2257 --
2258 -- Increment the date index
2259 --
2260 l_date_index := p_date_master_tab.NEXT(l_date_index);
2261 --
2262 END LOOP;
2263 --
2264 -- End looping through master PL/SQL table in date order
2265 --
2266 dbg('Exiting fill_gaps_in_abv_history');
2267 --
2268 --
2269 END fill_gaps_in_abv_history;
2270 --
2271 -- -----------------------------------------------------------------------------
2272 -- 5E Identify Salary Changes
2273 -- Creates a list of salary changes, and inserts a record in the combined
2274 -- event list PLSQL table for each change
2275 -- -----------------------------------------------------------------------------
2276 --
2277 PROCEDURE identify_salary_changes(
2278 p_asg_dates IN g_asg_date_type,
2279 p_date_master_tab IN OUT NOCOPY g_master_tab_type,
2280 p_sal_change_tab OUT NOCOPY g_sal_change_tab_type) IS
2281 --
2282 -- Cursor for salary changes
2283 --
2284 CURSOR sal_csr IS
2285 SELECT
2286 CASE WHEN ppb.pay_annualization_factor IS NULL
2287 AND ppb.pay_basis = 'PERIOD' THEN
2288 --
2289 -- When the salary basis is PERIOD, the annualization can be
2290 -- null in such a case the the annualization factor is
2291 -- equal to the payroll frequency or the numer of paroll in a
2292 -- year. The function returns the payroll frequency
2293 --
2294 pro.proposed_salary_n *
2295 hri_bpl_sal.get_perd_annualization_factor
2296 (asg.assignment_id, pro.change_date)
2297 ELSE
2298 pro.proposed_salary_n * ppb.pay_annualization_factor
2299 END salary
2300 --
2301 -- Time
2302 --
2303 ,pro.change_date change_date
2304 --
2305 -- Dimensions
2306 --
2307 ,NVL(pro.pay_proposal_id,-1) pay_proposal_id
2308 ,NVL(pet.input_currency_code, 'NA_EDW') currency_code
2309 FROM
2310 per_all_assignments_f asg
2311 ,per_pay_bases ppb
2312 ,per_pay_proposals pro
2313 ,pay_input_values_f piv
2314 ,pay_element_types_f pet
2315 WHERE pro.approved = 'Y'
2316 AND asg.assignment_id = g_assignment_id
2317 AND asg.assignment_id = pro.assignment_id
2318 AND asg.pay_basis_id = ppb.pay_basis_id
2319 AND ppb.input_value_id = piv.input_value_id
2320 AND piv.element_type_id = pet.element_type_id
2321 AND pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2322 AND pro.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2323 AND pro.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2324 --
2325 -- Only Salary changes before assignment end
2326 --
2327 AND pro.change_date <= p_asg_dates.end_date_active
2328 ORDER BY pro.change_date;
2329 --
2330 -- Local Package Variables - reset every time procedure is called
2331 --
2332 -- Tables for bulk fetch
2333 -- Note in 9i bulk fetch directly into table of records is supported
2334 --
2335 l_sal_effective_start_date g_date_tab_type;
2336 l_sal_effective_end_date g_date_tab_type;
2337 l_sal_anl_slry g_number_tab_type;
2338 l_sal_pay_proposal_id g_number_tab_type;
2339 l_sal_anl_slry_currency g_varchar2_tab_type;
2340 --
2341 -- Variable to collect the total number of record fetched by the salary cursor
2342 --
2343 l_sal_no_records PLS_INTEGER;
2344 --
2345 -- Index variables
2346 --
2347 l_sal_index PLS_INTEGER;
2348 l_date_index PLS_INTEGER;
2349 --
2350 -- Indicator variable which determines if a salary event has occured on the
2351 -- relevant date
2352 --
2353 l_sal_evt_ind PLS_INTEGER := 1;
2354 --
2355 --
2356 BEGIN
2357 --
2358 dbg('Inside identify_salary_changes');
2359 --
2360 -- Load up the salary records for the assignment
2361 -- Bulk load cursor into PLSQL table
2362 --
2363 dbg('Opening the salary cursor');
2364 OPEN sal_csr;
2365 FETCH sal_csr
2366 BULK COLLECT INTO
2367 l_sal_anl_slry
2368 ,l_sal_effective_start_date
2369 ,l_sal_pay_proposal_id
2370 ,l_sal_anl_slry_currency;
2371 --
2372 l_sal_no_records := sal_csr%ROWCOUNT;
2373 --
2374 CLOSE sal_csr;
2375 --
2376 IF (l_sal_no_records > 0) THEN
2377 --
2378 -- Set the effective end date of the salary record to that of end of time
2379 -- for the last record
2380 --
2381 p_sal_change_tab(l_sal_no_records).effective_end_date := g_end_of_time;
2382 --
2383 l_sal_index := 1;
2384 --
2385 FOR i IN 1..l_sal_no_records LOOP
2386 --
2387 -- Transfer data to output table of records
2388 --
2389 p_sal_change_tab(i).anl_slry := l_sal_anl_slry(i);
2390 p_sal_change_tab(i).effective_start_date := l_sal_effective_start_date(i);
2391 p_sal_change_tab(i).pay_proposal_id := l_sal_pay_proposal_id(i);
2392 p_sal_change_tab(i).anl_slry_currency := l_sal_anl_slry_currency(i);
2393 --
2394 -- Set the effective end date for salary records
2395 --
2396 IF i < l_sal_no_records THEN
2397 --
2398 p_sal_change_tab(i).effective_end_date := l_sal_effective_start_date(i+1) - 1;
2399 --
2400 END IF;
2401 --
2402 -- The salary cursor will get all salary records for the person,
2403 -- we are only interested in ones that are valid on/after the refresh_date
2404 --
2405 IF p_sal_change_tab(i).effective_end_date < p_asg_dates.start_date_active THEN
2406 --
2407 -- The record starts before the start_date_active
2408 --
2409 l_sal_index := i + 1;
2410 --
2411 ELSE
2412 IF (l_sal_index = i) THEN
2413 --
2414 -- This should happen only for the first record after the dbi collection
2415 -- date
2416 --
2417 IF (p_sal_change_tab(l_sal_index).effective_start_date <
2418 p_asg_dates.start_date_active) THEN
2419 --
2420 p_sal_change_tab(l_sal_index).effective_start_date :=
2421 p_asg_dates.start_date_active;
2422 --
2423 -- If the first record starts on the day of the assignment start date then
2424 -- set the indicator for salary
2425 --
2426 l_sal_evt_ind := 0;
2427 --
2428 ELSIF (p_sal_change_tab(l_sal_index).effective_start_date =
2429 g_dbi_collection_start_date) THEN
2430 --
2431 -- salary change was done on dbi collection date, so unset the retrospective
2432 -- indicator
2433 --
2434 p_date_master_tab(p_sal_change_tab(l_sal_index).effective_start_date -
2435 g_refresh_start_date).rtrspctv_strt_ind := 0;
2436 --
2437 END IF;
2438 --
2439 END IF;
2440 --
2441 -- Calculate date index value as the difference between the effective
2442 -- start date and refresh start date. Being the difference between two
2443 -- dates, this will be an integer
2444 --
2445 l_date_index := p_sal_change_tab(i).effective_start_date - g_refresh_start_date;
2446 --
2447 -- Assign the salary index in date-indexed PLSQL table
2448 --
2449 p_date_master_tab(l_date_index).sal_index := i;
2450 --
2451 -- Set the salary event indicator in the master table based on the
2452 -- value of the indicator varable l_sal_evt_ind
2453 --
2454 p_date_master_tab(l_date_index).sal_evt_ind := l_sal_evt_ind;
2455 --
2456 -- After first record this indicator will be 1
2457 --
2458 l_sal_evt_ind := 1;
2459 --
2460 END IF;
2461 --
2462 END LOOP;
2463 --
2464 -- End of the loop for transposing salary records to master date-indexed
2465 -- PLSQL table
2466 -- -----------------------------------------------------------------------
2467 --
2468 END IF;
2469 --
2470 dbg('Exiting identify_salary_changes');
2471 --
2472 --
2473 -- When an exception is raised, the cursor is closed and the exception is passed
2474 -- out of this block and it is handled in the collect procedure where an entry
2475 -- of this is made in the concurrent log
2476 --
2477 EXCEPTION
2478 --
2479 WHEN OTHERS THEN
2480 --
2481 dbg('Error encountered in identify_salary_changes');
2482 dbg(SQLERRM);
2483 --
2484 IF sal_csr%ISOPEN THEN
2485 --
2486 CLOSE sal_csr;
2487 --
2488 END IF;
2489 --
2490 -- Bug 4105868: Collection Diagnostic Call
2491 --
2492 g_msg_sub_group := NVL(g_msg_sub_group, 'IDENTIFY_SALARY_CHANGES');
2493 --
2494 RAISE;
2495 --
2496 --
2497 END identify_salary_changes;
2498 --
2499 -- -----------------------------------------------------------------------------
2500 -- 5F Identify Performance Rating Changes
2501 -- Creates a list of performance rating changes, and inserts a record in the
2502 -- combined event list PLSQL table for each change
2503 -- ----------------------------------------------------------------------------
2504 --
2505 PROCEDURE identify_perf_rating_changes(
2506 p_asg_dates IN g_asg_date_type,
2507 p_person_id IN NUMBER,
2508 p_business_group_id IN NUMBER,
2509 p_date_master_tab IN OUT NOCOPY g_master_tab_type,
2510 p_perf_change_tab OUT NOCOPY g_perf_change_tab_type)
2511 IS
2512 --
2513 -- Local Package Variables - reset every time procedure is called
2514 --
2515 -- Tables for cursor fetch
2516 --
2517 l_perf_effective_start_date g_date_tab_type;
2518 l_perf_effective_end_date g_date_tab_type;
2519 l_last_update_date g_date_tab_type;
2520 l_perf_nrmlsd_rating g_number_tab_type;
2521 l_perf_band g_number_tab_type;
2522 l_perf_review_id g_number_tab_type;
2523 l_perf_review_type_cd g_varchar2_tab_type;
2524 l_perf_rating_cd g_varchar2_tab_type;
2525 --
2526 -- Variable to store ranks for reviews done on the same date
2527 --
2528 l_same_day_rank g_number_tab_type;
2529 --
2530 -- Variable to collect the total number of record fetched by the rating cursor
2531 --
2532 l_perf_no_records PLS_INTEGER;
2533 --
2534 -- Index variables
2535 --
2536 l_rating_index PLS_INTEGER;
2537 l_date_index PLS_INTEGER;
2538 l_dummy PLS_INTEGER;
2539 --
2540 -- Indicator variable which determines if a rating event has occured on the
2541 -- relevant date
2542 --
2543 l_perf_evt_ind PLS_INTEGER := 1;
2544 --
2545 -- PLSQL table to store the appraisal template name
2546 --
2547 l_app_temp_name g_varchar2_240_tab_type;
2548 l_normalized_rating NUMBER;
2549 l_appraisal_ff_id NUMBER;
2550 --
2551 -- Reference Cursor type to be used to fetch the performance sql
2552 --
2553 TYPE ref_cursor_type IS REF CURSOR;
2554 --
2555 -- Reference cursor to be used to fetch performance records
2556 --
2557 perf_csr ref_cursor_type;
2558 --
2559 --
2560 BEGIN
2561 --
2562 dbg('Inside identify_perf_rating_changes');
2563 --
2564 -- Do not collected performance rating information in case g_collect_perf_rating
2565 -- is set to N.
2566 --
2567 IF g_collect_perf_rating = 'N' THEN
2568 --
2569 RETURN;
2570 --
2571 END IF;
2572 --
2573 -- Load up the performance rating records for the assignment
2574 --
2575 -- Bulk load cursor into PLSQL table
2576 --
2577 dbg('p_asg_dates.end_date_active ='||p_asg_dates.end_date_active);
2578 dbg('Opening the performance cursor');
2579 --
2580 OPEN perf_csr
2581 FOR g_perf_query
2582 USING p_business_group_id,
2583 p_person_id,
2584 p_business_group_id, -- perf select
2585 p_person_id, -- perf where
2586 p_asg_dates.end_date_active, -- perf where
2587 p_asg_dates.hire_date, -- perf where
2588 p_business_group_id, -- app select
2589 p_person_id, -- app where
2590 p_asg_dates.end_date_active, -- app where
2591 p_asg_dates.hire_date, -- app where
2592 g_assignment_id;
2593 --
2594 FETCH perf_csr
2595 BULK COLLECT INTO
2596 l_perf_rating_cd
2597 ,l_perf_effective_start_date
2598 ,l_last_update_date
2599 ,l_perf_effective_end_date
2600 ,l_perf_review_id
2601 ,l_perf_review_type_cd
2602 ,l_app_temp_name
2603 ,l_perf_nrmlsd_rating
2604 ,l_perf_band
2605 ,l_same_day_rank;
2606 --
2607 -- Store the total number of records fetched by the cursor
2608 --
2609 l_perf_no_records := perf_csr%ROWCOUNT;
2610 --
2611 CLOSE perf_csr;
2612 --
2613 dbg('Number of records fetched(l_perf_no_records) ='||l_perf_no_records);
2614 --
2615 IF (l_perf_no_records > 0) THEN
2616 --
2617 l_rating_index := 1;
2618 --
2619 FOR i IN 1..l_perf_no_records LOOP
2620 --
2621 -- 4259647, 4300665 In case there are multiple appraisals on the same date, the
2622 -- last updated record should be used for collection. A warning should
2623 -- be displayed in this case, so that users are aware of the issue
2624 -- The query ranks the records based on the last update date of the record
2625 -- In case the rank for the record is > 1 then display a warning
2626 --
2627 IF l_same_day_rank(i) = 1 THEN
2628 --
2629 -- Transfer the data to the output table of records
2630 --
2631 p_perf_change_tab(i).rating_cd := l_perf_rating_cd(i);
2632 p_perf_change_tab(i).effective_start_date := l_perf_effective_start_date(i);
2633 p_perf_change_tab(i).effective_end_date := l_perf_effective_end_date(i);
2634 p_perf_change_tab(i).review_id := l_perf_review_id(i);
2635 p_perf_change_tab(i).review_type_cd := l_perf_review_type_cd(i);
2636 p_perf_change_tab(i).nrmlsd_rating := l_perf_nrmlsd_rating(i);
2637 p_perf_change_tab(i).band := l_perf_band(i);
2638 --
2639 -- The rating cursor will get all performance rating records for the person,
2640 -- we are only interested in ones that are valid on/after the refresh_date
2641 --
2642 IF p_perf_change_tab(i).effective_end_date < p_asg_dates.start_date_active THEN
2643 --
2644 -- The record starts before the start_date_active
2645 --
2646 l_rating_index := i + 1;
2647 --
2648 dbg('review date before the event date, skipping collection');
2649 --
2650 ELSE
2651 --
2652 dbg('writing review detials in the arrays');
2653 --
2654 IF (l_rating_index = i) THEN
2655 --
2656 -- This should happen only for the first record after the dbi collection
2657 -- date
2658 --
2659 IF (p_perf_change_tab(l_rating_index).effective_start_date <
2660 p_asg_dates.start_date_active)
2661 THEN
2662 --
2663 p_perf_change_tab(l_rating_index).effective_start_date :=
2664 p_asg_dates.start_date_active;
2665 --
2666 -- If the first record starts on the day of the assignment start date then
2667 -- set the indicator for performance
2668 --
2669 l_perf_evt_ind := 0;
2670 --
2671 END IF;
2672 --
2673 END IF;
2674 --
2675 dbg('Normalized performance rating '||p_perf_change_tab(i).nrmlsd_rating);
2676 --
2677 -- Calculate date index value as the difference between the effective
2678 -- start date and refresh start date. Being the difference between two
2679 -- dates, this will be an integer
2680 --
2681 l_date_index := p_perf_change_tab(i).effective_start_date - g_refresh_start_date;
2682 --
2683 -- Assign the perf index in date-indexed PLSQL table
2684 --
2685 p_date_master_tab(l_date_index).perf_index := i;
2686 --
2687 -- Set the perf event indicator in the master table based on the
2688 -- value of the indicator varable l_perf_evt_ind
2689 --
2690 p_date_master_tab(l_date_index).perf_evt_ind := l_perf_evt_ind;
2691 --
2692 -- After first record this indicator will be 1
2693 --
2694 l_perf_evt_ind := 1;
2695 --
2696 END IF;
2697 --
2698 ELSIF l_perf_effective_end_date(i) >= p_asg_dates.start_date_active AND
2699 l_same_day_rank(i) > 1
2700 THEN
2701 --
2702 -- The rank of the record is not one, so there are multiple appraisals existing
2703 -- for the person on the same date
2704 --
2705 output('WARNING! Multiple performance ratings exists for the person on the '||
2706 'same date. The rating given for the last updated record will '||
2707 'be considered for collection.');
2708 --
2709 END IF;
2710 --
2711 END LOOP;
2712 --
2713 -- End of the loop for transposing performance records to master date-indexed
2714 -- PLSQL table
2715 -- -----------------------------------------------------------------------
2716 --
2717 END IF;
2718 --
2719 dbg('Exiting identify_perf_rating_changes');
2720 --
2721 --
2722 -- When an exception is raised, the cursor is closed and the exception is passed
2723 -- out of this block and it is handled in the collect procedure where an entry
2724 -- of this is made in the concurrent log
2725 --
2726 EXCEPTION
2727 --
2728 WHEN OTHERS THEN
2729 --
2730 IF perf_csr%ISOPEN THEN
2731 --
2732 CLOSE perf_csr;
2733 --
2734 END IF;
2735 --
2736 dbg('Exception raised in identify_perf_rating_changes');
2737 dbg(sqlerrm);
2738 --
2739 -- Bug 4105868: Collection Diagnostic Call
2740 --
2741 g_msg_sub_group := NVL(g_msg_sub_group, 'IDENTIFY_PERF_RATING_CHANGES');
2742 --
2743 RAISE;
2744 --
2745 END identify_perf_rating_changes;
2746 --
2747 -- ----------------------------------------------------------------------------
2748 -- 5G Identify POW Band Changes
2749 -- Inserts a record in the combined event list array for each POW Band change
2750 -- The procedure also implements the logic for period of extension
2751 -- ----------------------------------------------------------------------------
2752 --
2753 PROCEDURE identify_pow_band_changes(
2754 p_asg_dates IN g_asg_date_type,
2755 p_person_id IN NUMBER,
2756 p_date_master_tab IN OUT NOCOPY g_master_tab_type,
2757 p_assignment_type IN VARCHAR2)
2758 IS
2759 --
2760 l_total_bands NUMBER := 5;
2761 l_pow_band_start_date DATE;
2762 l_pow_band_end_date DATE;
2763 l_prjctd_end_dt DATE;
2764 l_pow_extn_strt_dt DATE;
2765 l_date_index NUMBER;
2766 l_pow_band_high_val NUMBER;
2767 l_pow_band_sk_fk NUMBER;
2768 --
2769 CURSOR c_prjctd_end_dt IS
2770 SELECT min(asg.projected_assignment_end)
2771 FROM per_all_assignments_f asg
2772 WHERE asg.person_id = p_person_id
2773 AND asg.primary_flag = 'Y'
2774 --
2775 -- 4469175 incase if rehire of placement, the extension date should be
2776 -- derived from asg records in the same term. placement start date
2777 -- is the fk to placement table
2778 --
2779 AND asg.period_of_placement_date_start = p_asg_dates.hire_date;
2780 --
2781 BEGIN
2782 --
2783 dbg('Inside identify_pow_band_changes ');
2784 --
2785 -- Generate POW band change events in the date master tab array
2786 --
2787 -- Loop through the 5 POW bands and capture the band change date event dates
2788 -- in the master array till the ealrier of sysdate or the termination date of the person
2789 --
2790 dbg('p_asg_dates.start_date_active ='||p_asg_dates.start_date_active);
2791 dbg('p_asg_dates.termination_date ='||p_asg_dates.termination_date);
2792 dbg('p_asg_dates.pow_start_date_adj='||p_asg_dates.pow_start_date_adj);
2793 --
2794 -- Projected End Date
2795 --
2796 IF g_cwk_asg THEN
2797 --
2798 OPEN c_prjctd_end_dt;
2799 FETCH c_prjctd_end_dt into l_prjctd_end_dt;
2800 CLOSE c_prjctd_end_dt;
2801 --
2802 g_cwk_asg := FALSE;
2803 --
2804 END IF;
2805 --
2806 FOR l_loop_count in 1 .. l_total_bands LOOP
2807 --
2808 -- Determine the start date of the band
2809 --
2810 -- The start date of the first band is set to hire date
2811 --
2812 IF l_pow_band_start_date is null THEN
2813 --
2814 l_pow_band_start_date := p_asg_dates.pow_start_date_adj;
2815 --
2816 --
2817 -- The start date of subsequent band is set ot the end date+1 of thr previous band
2818 --
2819 ELSE
2820 --
2821 l_pow_band_start_date := l_pow_band_end_date + 1;
2822 --
2823 END IF;
2824 --
2825 -- Get end range of the band
2826 --
2827 BEGIN
2828 l_pow_band_high_val := hri_bpl_period_of_work.get_pow_band_high_val
2829 (p_band_number => l_loop_count,
2830 p_assignment_type => p_assignment_type);
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833 g_msg_sub_group := NVL(g_msg_sub_group, 'GET_POW_BAND_HIGH_VAL');
2834 RAISE;
2835 END;
2836 --
2837 dbg('l_pow_band_high_val:'||l_pow_band_high_val);
2838 --
2839 -- Find the end date of the band
2840 --
2841 l_pow_band_end_date := add_months(p_asg_dates.pow_start_date_adj,l_pow_band_high_val)-1;
2842 --
2843 dbg('pow_start_dt='||to_char(l_pow_band_start_date,'DD-MON-RRRR') || ' '||
2844 'pow_end='||to_char(l_pow_band_end_date,'DD-MON-RRRR'));
2845 --
2846 -- Do not create any events if the end date is null or end of time
2847 --
2848 IF p_asg_dates.start_date_active <= nvl(l_pow_band_end_date,hr_general.end_of_time) AND
2849 l_pow_band_start_date <= nvl(p_asg_dates.termination_date,hr_general.end_of_time)
2850 THEN
2851 --
2852 l_date_index := greatest(l_pow_band_start_date,p_asg_dates.start_date_active) - g_refresh_start_date;
2853 --
2854 -- The master date always starts with index 1. Incase the date_index is 0 then
2855 -- array should be initialized from 1 and not 0
2856 --
2857 IF l_date_index = 0 THEN
2858 --
2859 l_date_index := p_date_master_tab.first;
2860 --
2861 END IF;
2862 --
2863 -- Determine the low band
2864 --
2865 l_pow_band_sk_fk := hri_bpl_period_of_work.get_pow_band_sk_fk
2866 (p_band_number => l_loop_count
2867 ,p_assignment_type => p_assignment_type);
2868 --
2869 dbg('inserting pow band info in master index at index '||l_date_index);
2870 --
2871 -- Set the pow band change event indicator in the master table based on the
2872 -- value of the indicator varable l_perf_evt_ind
2873 --
2874 p_date_master_tab(l_date_index).pow_evt_ind := 1;
2875 p_date_master_tab(l_date_index).pow_band_sk_fk := l_pow_band_sk_fk;
2876 p_date_master_tab(l_date_index).pow_extn_strt_dt := l_pow_extn_strt_dt;
2877 --
2878 dbg('asg value at the index val='||p_date_master_tab(l_date_index).asg_index);
2879 --
2880 END IF;
2881 --
2882 -- PERIOD OF EXTENSION START DATE
2883 --
2884 -- A record should be created on the extension start date. The condition will only
2885 -- be true once, at which point a new record is created and the variable
2886 -- l_pow_extn_strt_dt is also populated. All subsequent records will be populated
2887 -- by referencing the l_pow_extn_strt_dt variable
2888 --
2889 IF l_prjctd_end_dt < g_refresh_start_date THEN
2890 --
2891 l_pow_extn_strt_dt := l_prjctd_end_dt + 1;
2892 p_date_master_tab(nvl(l_date_index,0)).pow_extn_strt_dt := l_pow_extn_strt_dt;
2893 --
2894 ELSIF l_prjctd_end_dt + 1 BETWEEN l_pow_band_start_date AND
2895 nvl(l_pow_band_end_date,hr_general.end_of_time)
2896 THEN
2897 --
2898 l_pow_extn_strt_dt := l_prjctd_end_dt + 1;
2899 --
2900 dbg('inside pow extn='||l_pow_extn_strt_dt);
2901 --
2902 l_date_index := l_pow_extn_strt_dt - g_refresh_start_date;
2903 p_date_master_tab(l_date_index).pow_evt_ind := 1;
2904 p_date_master_tab(l_date_index).pow_band_sk_fk := l_pow_band_sk_fk;
2905 p_date_master_tab(l_date_index).pow_extn_strt_dt := l_pow_extn_strt_dt;
2906 --
2907 ELSE
2908 --
2909 dbg('extn start='||l_prjctd_end_dt||' pow band start='||l_pow_band_start_date ||' end = '|| l_pow_band_end_date);
2910 --
2911 END IF;
2912 --
2913 -- Exit when the band end date is greater than the termination date or sysdate
2914 --
2915 IF l_pow_band_end_date is null OR
2916 l_pow_band_end_date > p_asg_dates.termination_date OR
2917 l_pow_band_end_date + 1 > SYSDATE
2918 THEN
2919 --
2920 EXIT;
2921 --
2922 END IF;
2923 --
2924 END LOOP;
2925 --
2926 dbg('Exiting identify_pow_band_changes ');
2927 --
2928 END identify_pow_band_changes;
2929 --
2930 -- ----------------------------------------------------------------------------
2931 -- Set Previous Values
2932 -- This procedure finds the previous values of columns such as grade, abv,
2933 -- salary etc. on a day before the incremental changes are being made to the
2934 -- assignment events
2935 -- ----------------------------------------------------------------------------
2936 --
2937 PROCEDURE set_previous_values(p_prv_rec IN OUT NOCOPY g_prv_record
2938 ,p_business_group_id IN NUMBER
2939 ,p_date_master_tab IN g_master_tab_type
2940 ,p_hire_date DATE) IS
2941 --
2942 -- Cursor to hold the values existing one day before the refresh start date
2943 --
2944 CURSOR prv_val_csr IS
2945 SELECT
2946 grade_id
2947 ,job_id
2948 ,location_id
2949 ,organization_id
2950 ,supervisor_id
2951 ,position_id
2952 ,primary_flag
2953 ,fte
2954 ,headcount
2955 ,anl_slry
2956 ,anl_slry_currency
2957 --
2958 -- Performance Values
2959 --
2960 ,perf_nrmlsd_rating
2961 ,perf_band
2962 ,to_date(null)
2963 ,to_date(null)
2964 --
2965 -- Period of Work Value
2966 --
2967 ,pow_band_sk_fk
2968 ,summarization_rqd_ind
2969 ,ROWID
2970 FROM HRI_MB_ASGN_EVENTS_CT
2971 WHERE assignment_id = g_assignment_id
2972 AND (g_refresh_start_date - 1) BETWEEN effective_change_date AND effective_change_end_date;
2973 --
2974 l_effective_start_date DATE;
2975 l_date_index NUMBER;
2976 --
2977 --
2978 BEGIN
2979 --
2980 dbg('Entering set_previous_values');
2981 --
2982 -- Set up a default record for previous assignment for values before the
2983 -- refresh start date
2984 --
2985 p_prv_rec.organization_prv_id := -1;
2986 p_prv_rec.location_prv_id := -1;
2987 p_prv_rec.job_prv_id := -1;
2988 p_prv_rec.grade_prv_id := -1;
2989 p_prv_rec.position_prv_id := -1;
2990 p_prv_rec.supervisor_prv_id := -1;
2991 p_prv_rec.primary_flag_prv := 'NA_EDW';
2992 --
2993 -- Set up a default no-salary record for values before the refresh start date
2994 --
2995 p_prv_rec.anl_slry_prv := TO_NUMBER(NULL);
2996 p_prv_rec.anl_slry_currency_prv := 'NA_EDW';
2997 --
2998 -- Set up a default no-performance record for values before the refresh start date
2999 --
3000 p_prv_rec.perf_nrmlsd_rating_prv := TO_NUMBER(NULL);
3001 p_prv_rec.perf_band_prv := TO_NUMBER(NULL);
3002 --
3003 -- Set up a default no-period of work record for values before the refresh start date
3004 --
3005 p_prv_rec.pow_band_sk_fk_prv := TO_NUMBER(NULL);
3006 --
3007 -- Set up a default ABV record for values before the refresh start date
3008 --
3009 p_prv_rec.fte_prv := 0;
3010 p_prv_rec.headcount_prv := 0;
3011 --
3012 l_date_index := p_date_master_tab.FIRST;
3013 l_effective_start_date := g_refresh_start_date + l_date_index;
3014 --
3015 -- Calculate the value of FTE before the DBI collection start date in cases where there
3016 -- is a FTE change event on DBI collection start date
3017 --
3018 IF g_collect_fte = 'Y' AND
3019 l_effective_start_date = g_dbi_collection_start_date
3020 THEN
3021 --
3022 IF (l_effective_start_date > p_hire_date) THEN
3023 --
3024 IF (p_date_master_tab(l_date_index).fte_record_ind = 1) THEN
3025 --
3026 -- FTE Changed on DBI Collection date, recalculate the pervious value
3027 --
3028 p_prv_rec.fte_prv := hri_bpl_abv.calc_abv(
3029 p_assignment_id => g_assignment_id
3030 ,p_business_group_id => p_business_group_id
3031 ,p_budget_type => 'FTE'
3032 ,p_effective_date => g_dbi_collection_start_date - 1
3033 ,p_primary_flag => null
3034 ,p_run_formula => null);
3035 --
3036 ELSE
3037 --
3038 -- The FTE Change was made before DBI Collection date, set the
3039 -- previous value as the current value
3040 --
3041 p_prv_rec.fte_prv := p_date_master_tab(l_date_index).fte;
3042 --
3043 END IF;
3044 --
3045 ELSIF (l_effective_start_date = p_hire_date) THEN
3046 --
3047 -- On hire date the prv value should be set to 0
3048 --
3049 p_prv_rec.fte_prv := 0;
3050 --
3051 END IF;
3052 --
3053 END IF;
3054 --
3055 -- Calculate the value of Headcount before the DBI collection start date on cases where
3056 -- there is a Headcount change event on DBI collection start date
3057 --
3058 IF g_collect_hdc = 'Y' AND
3059 l_effective_start_date = g_dbi_collection_start_date
3060 THEN
3061 --
3062 IF (l_effective_start_date > p_hire_date) THEN
3063 --
3064 IF (p_date_master_tab(l_date_index).hdc_record_ind = 1) THEN
3065 --
3066 -- HDC Changed on DBI Collection date, recalculate the pervious value
3067 --
3068 p_prv_rec.headcount_prv := hri_bpl_abv.calc_abv(
3069 p_assignment_id => g_assignment_id
3070 ,p_business_group_id => p_business_group_id
3071 ,p_budget_type => 'HEAD'
3072 ,p_effective_date => g_dbi_collection_start_date - 1
3073 ,p_primary_flag => null
3074 ,p_run_formula => null);
3075 ELSE
3076 --
3077 -- The HDC Change was made before DBI Collection date, set the
3078 -- previous value as the current value
3079 --
3080 p_prv_rec.headcount_prv := p_date_master_tab(l_date_index).headcount;
3081 --
3082 END IF;
3083 --
3084 ELSIF (l_effective_start_date = p_hire_date) THEN
3085 --
3086 -- On hire date the prv value should be set to 0
3087 --
3088 p_prv_rec.headcount_prv := 0;
3089 --
3090 END IF;
3091 --
3092 END IF;
3093 --
3094 -- If incremental refresh
3095 --
3096 IF g_full_refresh = 'N' THEN
3097 --
3098 -- Open the cursor and fetch the value of the columns into the record variable
3099 --
3100 OPEN prv_val_csr;
3101 FETCH prv_val_csr INTO p_prv_rec;
3102 CLOSE prv_val_csr;
3103 --
3104 END IF;
3105 --
3106 dbg('Exiting set_previous_values');
3107 --
3108 EXCEPTION
3109 --
3110 WHEN OTHERS THEN
3111 --
3112 -- If the cursot is open then close it
3113 --
3114 IF prv_val_csr%ISOPEN THEN
3115 --
3116 CLOSE prv_val_csr;
3117 --
3118 END IF;
3119 --
3120 dbg('Error encountered in set_previous_values');
3121 dbg(SQLERRM);
3122 --
3123 -- Bug 4105868: Collection Diagnostic Call
3124 --
3125 g_msg_sub_group := NVL(g_msg_sub_group, 'SET_PREVIOUS_VALUES');
3126 --
3127 RAISE;
3128 --
3129 --
3130 END set_previous_values;
3131 --
3132 -- -----------------------------------------------------------------------------
3133 -- This procedure sets the value for various indicators based on the the values
3134 -- various arrays passed. It returns an array p_indicator_rec which contains
3135 -- the value of various indicators on every event date
3136 -- -----------------------------------------------------------------------------
3137 --
3138 PROCEDURE set_indicators(
3139 p_asg_dates IN g_asg_date_type,
3140 p_asg_change_tab IN g_asg_change_tab_type,
3141 p_sal_change_tab IN g_sal_change_tab_type,
3142 p_perf_change_tab IN g_perf_change_tab_type,
3143 p_date_master_tab IN g_master_tab_type,
3144 p_index_rec IN g_index_record,
3145 p_placeholder_rec IN g_placeholder_rec,
3146 p_indicator_rec OUT NOCOPY g_indicator_record,
3147 p_effective_start_date IN DATE,
3148 p_effective_end_date IN DATE)
3149 IS
3150 --
3151 --
3152 l_ret_val BOOLEAN;
3153 --
3154 --
3155 BEGIN
3156 --
3157 dbg('Entering set_indicators');
3158 --
3159 -- ---------------------------------------------------------------------------
3160 -- Start of initializing of indicators
3161 --
3162 p_indicator_rec.asg_rtrspctv_strt_event_ind := 0;
3163 p_indicator_rec.assignment_change_ind := 0;
3164 p_indicator_rec.salary_change_ind := 0;
3165 --
3166 -- Setting the performance rating indicators
3167 --
3168 p_indicator_rec.perf_change_ind := 0;
3169 p_indicator_rec.perf_band_change_ind := 0;
3170 --
3171 -- Setting the Period of Work band inidcators
3172 --
3173 p_indicator_rec.pow_band_change_ind := 0;
3174 --
3175 p_indicator_rec.headcount_gain_ind := 0;
3176 p_indicator_rec.headcount_loss_ind := 0;
3177 p_indicator_rec.fte_gain_ind := 0;
3178 p_indicator_rec.fte_loss_ind := 0;
3179 p_indicator_rec.contingent_ind := 0;
3180 p_indicator_rec.employee_ind := 0;
3181 p_indicator_rec.grade_change_ind := 0;
3182 p_indicator_rec.job_change_ind := 0;
3183 p_indicator_rec.position_change_ind := 0;
3184 p_indicator_rec.location_change_ind := 0;
3185 p_indicator_rec.organization_change_ind := 0;
3186 p_indicator_rec.supervisor_change_ind := 0;
3187 p_indicator_rec.worker_hire_ind := 0;
3188 p_indicator_rec.post_hire_asgn_start_ind := 0;
3189 p_indicator_rec.pre_sprtn_asgn_end_ind := 0;
3190 p_indicator_rec.term_voluntary_ind := 0;
3191 p_indicator_rec.term_involuntary_ind := 0;
3192 p_indicator_rec.worker_term_ind := 0;
3193 p_indicator_rec.start_asg_sspnsn_ind := 0;
3194 p_indicator_rec.end_asg_sspnsn_ind := 0;
3195 p_indicator_rec.promotion_ind := 0;
3196 --
3197 -- Person Type Indicators
3198 --
3199 p_indicator_rec.summarization_rqd_ind := 0;
3200 p_indicator_rec.summarization_rqd_chng_ind := 0;
3201 p_indicator_rec.summarization_rqd_chng_nxt_ind := 0;
3202 --
3203 -- If fte collection is not required then initialize the fte gain indicator
3204 -- and fte loss indicator variables to null
3205 --
3206 IF g_collect_fte = 'N' THEN
3207 --
3208 p_indicator_rec.fte_gain_ind := NULL;
3209 p_indicator_rec.fte_loss_ind := NULL;
3210 --
3211 END IF;
3212 --
3213 -- If headcount collection is not required then initialize the headcount gain
3214 -- indicator and headcount loss indicator variables to null
3215 --
3216 IF g_collect_hdc = 'N' THEN
3217 --
3218 p_indicator_rec.headcount_gain_ind := NULL;
3219 p_indicator_rec.headcount_loss_ind := NULL;
3220 --
3221 END IF;
3222 --
3223 -- End of initializing of indicators
3224 -- ---------------------------------------------------------------------------
3225 --
3226 -- Set the retrospective start event indicator. This is set to 1 if the
3227 -- assignment start date is before the DBI collection start date
3228 --
3229 IF ((
3230 p_date_master_tab(p_index_rec.date_index).rtrspctv_strt_ind = 1
3231 )
3232 AND
3233 (p_effective_start_date = g_dbi_collection_start_date
3234 )
3235 ) THEN
3236 --
3237 p_indicator_rec.asg_rtrspctv_strt_event_ind := 1;
3238 --
3239 END IF;
3240 --
3241 -- Set the contingent indicator to 1 if the assignment type is 'C'
3242 -- Set the employee indicator to 1 if the assignment type is 'E'
3243 --
3244 IF (p_asg_change_tab(p_index_rec.asg_index).type = 'C') THEN
3245 --
3246 p_indicator_rec.contingent_ind := 1;
3247 --
3248 ELSIF (p_asg_change_tab(p_index_rec.asg_index).type = 'E') THEN
3249 --
3250 p_indicator_rec.employee_ind := 1;
3251 --
3252 END IF;
3253 --
3254 -- Set the worker hire indicator to 1 when the effective start date of the
3255 -- assignment record is the same as hire date
3256 --
3257 IF (p_effective_start_date = p_asg_dates.hire_date) THEN
3258 --
3259 p_indicator_rec.worker_hire_ind := 1;
3260 --
3261 --
3262 -- Set the secondary assignment start indicator as 1 when the effective start
3263 -- date of the assignment record is same as the secondary assignment start date
3264 --
3265 ELSIF (p_effective_start_date = p_asg_dates.post_hire_asgn_start_date) THEN
3266 --
3267 p_indicator_rec.post_hire_asgn_start_ind := 1;
3268 --
3269 --
3270 -- Set the worker termination indicator as 1 when the effective start date of
3271 -- the assignment record is one day after the termination date
3272 --
3273 ELSIF (p_effective_start_date = p_asg_dates.termination_date + 1) THEN
3274 --
3275 p_indicator_rec.worker_term_ind := 1;
3276 --
3277 -- Set the voluntary and involuntary temination indicators
3278 -- The termination is voluntary if the assignment type is not 'E'
3279 -- or if the employee separation category is 'SEP_VOL'
3280 --
3281 IF (p_asg_change_tab(p_index_rec.asg_index_prev).type <> 'E' OR
3282 hri_bpl_termination.get_separation_category
3283 (p_asg_change_tab(p_index_rec.asg_index).leaving_reason_code) = 'SEP_VOL')
3284 THEN
3285 --
3286 p_indicator_rec.term_voluntary_ind := 1;
3287 --
3288 ELSE
3289 --
3290 p_indicator_rec.term_involuntary_ind := 1;
3291 --
3292 END IF;
3293 --
3294 -- Set the secondary assignment end indicator to 1 when the effective start
3295 -- date of the assignment record is same as the next date of secondary
3296 -- assignment end date
3297 --
3298 ELSIF (p_effective_start_date = p_asg_dates.pre_sprtn_asgn_end_date + 1) THEN
3299 --
3300 p_indicator_rec.pre_sprtn_asgn_end_ind := 1;
3301 --
3302 --
3303 -- Set all other indicators only if the the effective start date of the
3304 -- assignment record is after the refresh start date or a change event
3305 -- has occured on the refresh start date
3306 --
3307 ELSIF ( (p_effective_start_date > g_refresh_start_date)
3308 OR
3309 (p_date_master_tab(p_index_rec.date_index).asg_evt_ind = 1)
3310 OR
3311 (p_date_master_tab(p_index_rec.date_index).sal_evt_ind = 1)
3312 OR
3313 (p_date_master_tab(p_index_rec.date_index).fte_record_ind = 1)
3314 OR
3315 (p_date_master_tab(p_index_rec.date_index).hdc_record_ind = 1)
3316 ) THEN
3317 --
3318 -- Set the assignment related indicators only if the the effective start date
3319 -- of the assignment record is after the refresh start date or an assignment
3320 -- related change has occured on the refresh date
3321 --
3322 IF p_date_master_tab(p_index_rec.date_index).asg_evt_ind = 1 THEN
3323 --
3324 -- Set the assignment change indicator whenever an assignment change occurs
3325 -- ( Assignment event indicator set to 1)
3326 --
3327 p_indicator_rec.assignment_change_ind := 1;
3328 --
3329 IF ( p_asg_change_tab(p_index_rec.asg_index).status_code <>
3330 p_asg_change_tab(p_index_rec.asg_index_prev).status_code) THEN
3331 --
3332 -- Set the start assignment suspension indicator if the status code of
3333 -- the currect assignment record is active assignment and the status
3334 -- code of the previous assignment record is suspended assignment.
3335 --
3336 IF ((p_asg_change_tab(p_index_rec.asg_index).status_code = 'ACTIVE_ASSIGN' AND
3337 p_asg_change_tab(p_index_rec.asg_index_prev).status_code = 'SUSP_ASSIGN'
3338 )
3339 OR
3340 (p_asg_change_tab(p_index_rec.asg_index).status_code = 'ACTIVE_CWK' AND
3341 p_asg_change_tab(p_index_rec.asg_index_prev).status_code = 'SUSP_CWK_ASG'
3342 )
3343 ) THEN
3344 --
3345 p_indicator_rec.end_asg_sspnsn_ind := 1;
3346 --
3347 --
3348 -- Set the end assignment suspension indicator if the status code of
3349 -- the currect assignment record is suspended assignment and the status
3350 -- code of the previous assignment record is active assignment.
3351 --
3352 ELSIF ((p_asg_change_tab(p_index_rec.asg_index).status_code = 'SUSP_ASSIGN' AND
3353 p_asg_change_tab(p_index_rec.asg_index_prev).status_code = 'ACTIVE_ASSIGN'
3354 )
3355 OR
3356 (p_asg_change_tab(p_index_rec.asg_index).status_code = 'SUSP_CWK_ASG' AND
3357 p_asg_change_tab(p_index_rec.asg_index_prev).status_code = 'ACTIVE_CWK'
3358 )
3359 ) THEN
3360 --
3361 p_indicator_rec.start_asg_sspnsn_ind := 1;
3362 --
3363 END IF;
3364 --
3365 END IF;
3366 --
3367 -- Set the job change indicator to 1 if the job id of the current
3368 -- assignment record is not the same as the job id of the previous
3369 -- assignment record
3370 --
3371 IF ( p_asg_change_tab(p_index_rec.asg_index).job_id <>
3372 p_asg_change_tab(p_index_rec.asg_index_prev).job_id) THEN
3373 --
3374 p_indicator_rec.job_change_ind := 1;
3375 --
3376 END IF;
3377 --
3378 -- Set the location change indicator to 1 if the location id of the current
3379 -- assignment record is not the same as the location id of the previous
3380 -- assignment record
3381 --
3382 IF (p_asg_change_tab(p_index_rec.asg_index).location_id <>
3383 p_asg_change_tab(p_index_rec.asg_index_prev).location_id) THEN
3384 --
3385 p_indicator_rec.location_change_ind := 1;
3386 --
3387 END IF;
3388 --
3389 -- Set the supervisor change indicator to 1 if the supervisor id of the
3390 -- current assignment record is not the same as the supervisor id of the
3391 -- previous assignment record
3392 --
3393 IF (p_asg_change_tab(p_index_rec.asg_index).supervisor_id <>
3394 p_asg_change_tab(p_index_rec.asg_index_prev).supervisor_id) THEN
3395 --
3396 p_indicator_rec.supervisor_change_ind := 1;
3397 --
3398 -- For a secondary assignment supervisor change event,
3399 -- process manager hierarchy transfer
3400 --
3401 IF (p_asg_change_tab(p_index_rec.asg_index).primary_flag = 'N' OR
3402 p_asg_change_tab(p_index_rec.asg_index_prev).primary_flag = 'N') THEN
3403
3404 hri_opl_wrkfc_trnsfr_events.process_mgrh_transfer
3405 (p_manager_from_id => p_asg_change_tab(p_index_rec.asg_index_prev).supervisor_id
3406 ,p_manager_to_id => p_asg_change_tab(p_index_rec.asg_index).supervisor_id
3407 ,p_transfer_psn_id => p_asg_change_tab(p_index_rec.asg_index).person_id
3408 ,p_transfer_asg_id => p_asg_change_tab(p_index_rec.asg_index).assignment_id
3409 ,p_transfer_wty_fk => p_asg_change_tab(p_index_rec.asg_index).wkth_wktyp_code
3410 ,p_transfer_date => p_asg_change_tab(p_index_rec.asg_index).change_date
3411 ,p_transfer_hdc => p_date_master_tab(p_index_rec.date_index).headcount
3412 ,p_transfer_fte => p_date_master_tab(p_index_rec.date_index).fte);
3413
3414 END IF;
3415 --
3416 END IF;
3417 --
3418 -- Set the grade change indicator to 1 if the grade id of the current
3419 -- assignment record is not the same as the grade id of the previous
3420 -- assignment record
3421 --
3422 IF (p_asg_change_tab(p_index_rec.asg_index).grade_id <>
3423 p_asg_change_tab(p_index_rec.asg_index_prev).grade_id) THEN
3424 --
3425 p_indicator_rec.grade_change_ind := 1;
3426 --
3427 END IF;
3428 --
3429 -- Set the position change indicator to 1 if the position id of the current
3430 -- assignment record is not the same as the position id of the previous
3431 -- assignment record
3432 --
3433 IF (p_asg_change_tab(p_index_rec.asg_index).position_id <>
3434 p_asg_change_tab(p_index_rec.asg_index_prev).position_id) THEN
3435 --
3436 p_indicator_rec.position_change_ind := 1;
3437 --
3438 END IF;
3439 --
3440 -- Set the organization change indicator to 1 if the organization id of the
3441 -- current assignment record is not the same as the organization id of the
3442 -- previous assignment record
3443 --
3444 IF (p_asg_change_tab(p_index_rec.asg_index).organization_id <>
3445 p_asg_change_tab(p_index_rec.asg_index_prev).organization_id) THEN
3446 --
3447 p_indicator_rec.organization_change_ind := 1;
3448 --
3449 -- For an organization change event, process org hierarchy transfer
3450 --
3451 hri_opl_wrkfc_trnsfr_events.process_orgh_transfer
3452 (p_organization_from_id => p_asg_change_tab(p_index_rec.asg_index_prev).organization_id
3453 ,p_organization_to_id => p_asg_change_tab(p_index_rec.asg_index).organization_id
3454 ,p_transfer_psn_id => p_asg_change_tab(p_index_rec.asg_index).person_id
3455 ,p_transfer_asg_id => p_asg_change_tab(p_index_rec.asg_index).assignment_id
3456 ,p_transfer_wty_fk => p_asg_change_tab(p_index_rec.asg_index).wkth_wktyp_code
3457 ,p_transfer_date => p_asg_change_tab(p_index_rec.asg_index).change_date
3458 ,p_transfer_hdc => p_date_master_tab(p_index_rec.date_index).headcount
3459 ,p_transfer_fte => p_date_master_tab(p_index_rec.date_index).fte);
3460 --
3461 END IF;
3462 --
3463 END IF;
3464 --
3465 -- Set the salary change indicator only if the the effective start date
3466 -- of the record is after the refresh start date or a salary related change
3467 -- has occured on the refresh date
3468 --
3469 IF p_date_master_tab(p_index_rec.date_index).sal_evt_ind = 1 THEN
3470 --
3471 -- Set the salary change indicator to 1 if the salary of the current record
3472 -- is not the same as the salary of the previous record
3473 --
3474 IF (NVL(p_sal_change_tab(p_index_rec.sal_index).anl_slry, -1) <>
3475 NVL(p_sal_change_tab(p_index_rec.sal_index_prev).anl_slry, -1)) THEN
3476 --
3477 p_indicator_rec.salary_change_ind := 1;
3478 --
3479 END IF;
3480 --
3481 END IF;
3482 --
3483 -- Set the fte indicators only if a fte change has occured
3484 --
3485 -- Set the fte gain indicator to 1 if the fte of the current record is
3486 -- greater than the fte of previous record and the asg record is not
3487 -- retrospective
3488 --
3489 IF p_indicator_rec.asg_rtrspctv_strt_event_ind <> 1 THEN
3490 --
3491 IF (p_placeholder_rec.fte > p_placeholder_rec.fte_prv) THEN
3492 --
3493 p_indicator_rec.fte_gain_ind := 1;
3494 --
3495 --
3496 -- Set the fte loss indicator to 1 if the fte of the current record is less
3497 -- than the fte of previous record
3498 --
3499 ELSIF (p_placeholder_rec.fte < p_placeholder_rec.fte_prv) THEN
3500 --
3501 p_indicator_rec.fte_loss_ind := 1;
3502 --
3503 END IF;
3504 --
3505 END IF;
3506 --
3507 -- Set the headcount gain indicator to 1 if the headcount of the current
3508 -- record is greater than the headcount of previous record and the asg
3509 -- record is not retrospective
3510 --
3511 IF p_indicator_rec.asg_rtrspctv_strt_event_ind <> 1 THEN
3512 --
3513 IF (p_placeholder_rec.headcount > p_placeholder_rec.headcount_prv) THEN
3514 --
3515 p_indicator_rec.headcount_gain_ind := 1;
3516 --
3517 --
3518 -- Set the headcount loss indicator to 1 if the headcount of the current
3519 -- record is less than the headcount of previous record
3520 --
3521 ELSIF (p_placeholder_rec.headcount < p_placeholder_rec.headcount_prv) THEN
3522 --
3523 p_indicator_rec.headcount_loss_ind := 1;
3524 --
3525 END IF;
3526 --
3527 END IF;
3528 --
3529 -- If a promotion check is required, test for it
3530 --
3531 IF p_indicator_rec.employee_ind = 1 THEN
3532 --
3533 p_indicator_rec.promotion_ind :=
3534 hri_bpl_wrkfc_evt.get_promotion_ind
3535 (p_assignment_id => p_asg_change_tab(p_index_rec.asg_index).assignment_id,
3536 p_business_group_id => p_asg_change_tab(p_index_rec.asg_index).business_group_id,
3537 p_effective_date => p_asg_change_tab(p_index_rec.asg_index).change_date,
3538 p_new_job_id => p_asg_change_tab(p_index_rec.asg_index).job_id,
3539 p_new_pos_id => p_asg_change_tab(p_index_rec.asg_index).position_id,
3540 p_new_grd_id => p_asg_change_tab(p_index_rec.asg_index).grade_id,
3541 p_old_job_id => p_asg_change_tab(p_index_rec.asg_index_prev).job_id,
3542 p_old_pos_id => p_asg_change_tab(p_index_rec.asg_index_prev).position_id,
3543 p_old_grd_id => p_asg_change_tab(p_index_rec.asg_index_prev).grade_id);
3544 --
3545 END IF;
3546 --
3547 END IF;
3548 --
3549 --
3550 -- Determine the person type change indicator
3551 --
3552 IF p_indicator_rec.worker_term_ind <> 1 THEN
3553 --
3554 p_indicator_rec.summarization_rqd_ind := p_asg_change_tab(p_index_rec.asg_index).summarization_rqd_ind;
3555 --
3556 IF NVL(p_asg_change_tab(p_index_rec.asg_index).summarization_rqd_ind,-1) <>
3557 NVL(p_asg_change_tab(p_index_rec.asg_index_prev).summarization_rqd_ind,-1) AND
3558 p_indicator_rec.asg_rtrspctv_strt_event_ind <> 1 AND
3559 p_indicator_rec.worker_hire_ind <> 1
3560 THEN
3561 --
3562 p_indicator_rec.summarization_rqd_chng_ind := 1;
3563 --
3564 END IF;
3565 --
3566 ELSE
3567 --
3568 -- In case of termination set the summarization_rqd_ind to the previous
3569 -- value so that the record is included in delta collection
3570 --
3571 p_indicator_rec.summarization_rqd_ind := p_asg_change_tab(p_index_rec.asg_index_prev).summarization_rqd_ind;
3572 --
3573 END IF;
3574 --
3575 -- Determine if there is a POW band change
3576 --
3577 IF NVL(p_placeholder_rec.pow_band_sk_fk,-1) <> NVL(p_placeholder_rec.pow_band_sk_fk_prv,-1) THEN
3578 --
3579 p_indicator_rec.pow_band_change_ind := 1;
3580 --
3581 ELSE
3582 --
3583 p_indicator_rec.pow_band_change_ind := 0;
3584 --
3585 END IF;
3586 --
3587 -- Set the performance rating change indicator
3588 --
3589 IF (NVL(p_perf_change_tab(p_index_rec.perf_index).nrmlsd_rating, -1) <>
3590 NVL(p_perf_change_tab(p_index_rec.perf_index_prev).nrmlsd_rating, -1)) THEN
3591 --
3592 p_indicator_rec.perf_change_ind := 1;
3593 --
3594 END IF;
3595 --
3596 -- Set the performance band change indicator
3597 --
3598 IF (NVL(p_perf_change_tab(p_index_rec.perf_index).band, -1) <>
3599 NVL(p_perf_change_tab(p_index_rec.perf_index_prev).band, -1))
3600 THEN
3601 --
3602 p_indicator_rec.perf_band_change_ind := 1;
3603 --
3604 END IF;
3605 --
3606 dbg('Exiting set_indicators');
3607 END set_indicators;
3608 --
3609 -- -----------------------------------------------------------------------------
3610 -- 5G. MERGE_AND_INSERT_DATA
3611 -- Set the indicators, merge the data in the master table and insert into
3612 -- the table HRI_MB_ASGN_EVENTS
3613 -- -----------------------------------------------------------------------------
3614 --
3615 PROCEDURE merge_and_insert_data(
3616 p_date_master_tab IN g_master_tab_type,
3617 p_asg_change_tab IN g_asg_change_tab_type,
3618 p_sal_change_tab IN g_sal_change_tab_type,
3619 p_perf_change_tab IN g_perf_change_tab_type,
3620 p_asg_dates IN g_asg_date_type,
3621 p_prv_rec IN g_prv_record,
3622 p_nxt_ind_rec OUT NOCOPY g_nxt_ind_record,
3623 p_asgn_events_tab IN OUT NOCOPY g_asgn_events_tab_type) IS
3624 --
3625 -- -----------------------------------------------------------------------------
3626 -- Start of Local Package Variable eclaration
3627 --
3628 -- Reset every time procedure is called
3629 --
3630 --
3631 -- Date track period dates
3632 --
3633 l_effective_start_date DATE;
3634 l_effective_end_date DATE;
3635 --
3636 -- Passed to the set_indicators procedure in order to collect the
3637 -- indicator values from the procedure
3638 --
3639 l_indicator_rec g_indicator_record;
3640 --
3641 -- Used to hold the previous and curerent ABV records
3642 --
3643 l_placeholder_rec g_placeholder_rec;
3644 --
3645 -- Holds various indexes and their previous/next values and is passed to the
3646 -- set_indicator procedure to set the indicator
3647 --
3648 l_index_rec g_index_record;
3649 --
3650 -- Holds the next indicator columns for updating of HRI_MB_ASGN_EVENTS_CT
3651 -- table during incremental refresh
3652 --
3653 l_nxt_ind_rec g_nxt_ind_record;
3654 --
3655 -- Number of rows in PL/SQL table for insert
3656 --
3657 l_row_count PLS_INTEGER;
3658 l_first_row_index PLS_INTEGER;
3659 --
3660 -- ID of fast formula HRI_MAP_ASG_SUMMARIZATION
3661 --
3662 l_asg_sumrzn_ff_id NUMBER;
3663 --
3664 -- Variable to hold the previous value of summarization indicator if the fast
3665 -- formula HRI_MAP_ASG_SUMMARIZATION exists
3666 --
3667 l_summarization_ind_prev PLS_INTEGER;
3668 --
3669 -- Holds date of last promotion
3670 --
3671 l_last_promotion_date DATE;
3672 --
3673 BEGIN
3674 --
3675 dbg('Inside merge_and_insert_data');
3676 --
3677 -- ---------------------------------------------------------------------------
3678 -- Insert results into tables
3679 --
3680 -- Initialise variables
3681 --
3682 l_index_rec.date_index := p_date_master_tab.FIRST;
3683 --
3684 IF (p_asgn_events_tab.EXISTS(1)) THEN
3685 l_row_count := p_asgn_events_tab.LAST;
3686 ELSE
3687 l_row_count := 0;
3688 END IF;
3689 l_first_row_index := l_row_count + 1;
3690 --
3691 -- If assignment has no salary then initialise salary record
3692 -- to null record
3693 --
3694 l_index_rec.sal_index := 0;
3695 l_index_rec.sal_index_prev := 0;
3696 --
3697 -- Set the performance indexes
3698 --
3699 l_index_rec.perf_index := 0;
3700 l_index_rec.perf_index_prev := 0;
3701
3702 l_placeholder_rec.fte_prv := p_prv_rec.fte_prv;
3703 l_placeholder_rec.headcount_prv := p_prv_rec.headcount_prv;
3704 --
3705 -- Set the previous period of work band
3706 --
3707 l_placeholder_rec.pow_band_sk_fk_prv := p_prv_rec.pow_band_sk_fk_prv;
3708 --
3709 -- Call to find out if the fast formula HRI_MAP_ASG_SUMMARIZATION exists
3710 --
3711 l_asg_sumrzn_ff_id := hri_bpl_asg_summarization.ff_exists_and_compiled (p_business_group_id => 0
3712 ,p_date => trunc(SYSDATE)
3713 ,p_ff_name => 'HRI_MAP_ASG_SUMMARIZATION'
3714 );
3715 --
3716 -- ---------------------------------------------------------------------------
3717 -- Start Looping through all changes in the date index table
3718 --
3719 dbg('Before start of the loop');
3720 WHILE (l_index_rec.date_index IS NOT NULL) LOOP
3721 --
3722 -- Finds the index for the next record in the date index table
3723 --
3724 l_index_rec.next_date_index := p_date_master_tab.NEXT(l_index_rec.date_index);
3725 --
3726 -- Set up start date variable
3727 -- Since the date index is calculated as
3728 -- (effective start date - g_refresh_start_date)
3729 --
3730 l_effective_start_date := g_refresh_start_date + l_index_rec.date_index;
3731 --
3732 -- For the last record in the date index table set up end date variable
3733 --
3734 IF (l_index_rec.next_date_index IS NULL) THEN
3735 --
3736 -- Assign effective end date to end of time value
3737 --
3738 l_effective_end_date := g_end_of_time;
3739 --
3740 --
3741 -- If the record in the date index table is not the last record
3742 --
3743 ELSE
3744 --
3745 -- Assign effective end date to one day before the effective start date of
3746 -- the next record
3747 --
3748 l_effective_end_date := g_refresh_start_date + l_index_rec.next_date_index - 1;
3749 --
3750 END IF;
3751 --
3752 -- Store any changes to asg_index
3753 -- Store the previous value of the assignment index into another variable
3754 -- which will be required for comparison purpose during setting up of
3755 -- indicators in set_indicator procedure
3756 --
3757 IF (p_date_master_tab(l_index_rec.date_index).asg_index IS NOT NULL) THEN
3758 --
3759 l_index_rec.asg_index := p_date_master_tab(l_index_rec.date_index).asg_index;
3760 l_index_rec.asg_index_prev := l_index_rec.asg_index - 1;
3761 --
3762 END IF;
3763 --
3764 -- Store any changes to sal_index
3765 -- Store the previous value of the salary index into another variable
3766 -- which will be required for comparison purpose during setting up of
3767 -- indicators in set_indicator procedure
3768 --
3769 IF (p_date_master_tab(l_index_rec.date_index).sal_index IS NOT NULL) THEN
3770 --
3771 l_index_rec.sal_index := p_date_master_tab(l_index_rec.date_index).sal_index;
3772 --
3773 -- For incremental refresh the previous salary values are populated
3774 -- in 0th node of the salary arrays. Therefore for the first
3775 -- iteration of the loop, get the previous salary values from the 0th
3776 -- node in the salary arrays
3777 --
3778 IF l_index_rec.date_index = p_date_master_tab.FIRST THEN
3779 --
3780 IF (g_full_refresh = 'N' AND g_refresh_start_date > g_dbi_collection_start_date) THEN
3781 --
3782 l_index_rec.sal_index_prev := 0;
3783 --
3784 -- If the refresh start on the DBI collection start date and there is a salary
3785 -- event on the same day, then set the previous salary value to the value of
3786 -- salary that existed before the DBI collection start date. Else, set the
3787 -- value of the previous salary to the value of salary as it exists on
3788 -- DBI collection start date
3789 --
3790 ELSIF (g_refresh_start_date = g_dbi_collection_start_date) THEN
3791 --
3792 IF (p_date_master_tab(l_index_rec.date_index).sal_evt_ind = 1) THEN
3793 --
3794 l_index_rec.sal_index_prev := l_index_rec.sal_index - 1;
3795 --
3796 ELSE
3797 --
3798 l_index_rec.sal_index_prev := l_index_rec.sal_index;
3799 --
3800 END IF;
3801 --
3802 END IF;
3803 --
3804 --
3805 -- From the second iteration onwards assign the index for previous salary by
3806 -- subtracting 1 from the current salary index
3807 --
3808 ELSE
3809 --
3810 l_index_rec.sal_index_prev := l_index_rec.sal_index - 1;
3811 --
3812 END IF;
3813 --
3814 END IF;
3815 --
3816 -- Store any changes to FTE value
3817 -- Only store when fte collection is required
3818 --
3819 IF p_date_master_tab(l_index_rec.date_index).fte IS NOT NULL AND
3820 g_collect_fte = 'Y'
3821 THEN
3822 --
3823 l_placeholder_rec.fte := p_date_master_tab(l_index_rec.date_index).fte;
3824 --
3825 END IF;
3826 --
3827 -- Store any changes to HEAD value
3828 -- Only store when headcount collection is required
3829 --
3830 IF p_date_master_tab(l_index_rec.date_index).headcount IS NOT NULL AND
3831 g_collect_hdc = 'Y'
3832 THEN
3833 --
3834 l_placeholder_rec.headcount := p_date_master_tab(l_index_rec.date_index).headcount;
3835 --
3836 END IF;
3837 --
3838 -- Store any changes to period of work band
3839 --
3840 IF p_date_master_tab(l_index_rec.date_index).pow_evt_ind IS NOT NULL
3841 THEN
3842 --
3843 l_placeholder_rec.pow_band_sk_fk := p_date_master_tab(l_index_rec.date_index).pow_band_sk_fk;
3844 l_placeholder_rec.pow_extn_strt_dt := p_date_master_tab(l_index_rec.date_index).pow_extn_strt_dt;
3845 --
3846 END IF;
3847 --
3848 -- Store any changes to perf_index
3849 -- Store the previous value of the performance index into another variable
3850 -- which will be required for comparison purpose during setting up of
3851 -- indicators in set_indicator procedure
3852 --
3853 IF (p_date_master_tab(l_index_rec.date_index).perf_index IS NOT NULL) THEN
3854 --
3855 l_index_rec.perf_index_prev := l_index_rec.perf_index;
3856 l_index_rec.perf_index := p_date_master_tab(l_index_rec.date_index).perf_index;
3857 --
3858 -- For incremental refresh the previous performance values are populated
3859 -- in 0th node of the perfomance arrays. Therefore for the first
3860 -- iteration of the loop, get the previous performance rating values from the 0th
3861 -- node in the performance rating arrays
3862 --
3863 IF l_index_rec.date_index = p_date_master_tab.FIRST THEN
3864 --
3865 IF (g_full_refresh = 'N' AND g_refresh_start_date > g_dbi_collection_start_date) THEN
3866 --
3867 l_index_rec.perf_index_prev := 0;
3868 --
3869 -- If the refresh start on the DBI collection start date and there is a perf
3870 -- event on the same day, then set the previous perf value to the value of
3871 -- perf that existed before the DBI collection start date. Else, set the
3872 -- value of the previous perf to the value of perf as it exists on
3873 -- DBI collection start date
3874 --
3875 ELSIF (g_refresh_start_date = g_dbi_collection_start_date) THEN
3876 --
3877 IF (p_date_master_tab(l_index_rec.date_index).perf_evt_ind = 1) THEN
3878 --
3879 l_index_rec.perf_index_prev := l_index_rec.perf_index - 1;
3880 --
3881 ELSE
3882 --
3883 l_index_rec.perf_index_prev := l_index_rec.perf_index;
3884 --
3885 END IF;
3886 --
3887 END IF;
3888 --
3889 END IF;
3890 --
3891 END IF;
3892 --
3893 -- Call to the set_indicator procedure which sets all the indicators and
3894 -- return their values
3895 --
3896 SET_INDICATORS(
3897 p_asg_dates => p_asg_dates
3898 ,p_asg_change_tab => p_asg_change_tab
3899 ,p_sal_change_tab => p_sal_change_tab
3900 ,p_perf_change_tab => p_perf_change_tab
3901 ,p_date_master_tab => p_date_master_tab
3902 ,p_index_rec => l_index_rec
3903 ,p_placeholder_rec => l_placeholder_rec
3904 ,p_indicator_rec => l_indicator_rec
3905 ,p_effective_start_date => l_effective_start_date
3906 ,p_effective_end_date => l_effective_end_date);
3907 --
3908 -- Change the summarization related indicators only if the fast formula
3909 -- HRI_MAP_ASG_SUMMARIZATION exists
3910 --
3911 IF (l_asg_sumrzn_ff_id IS NOT NULL) THEN
3912 --
3913 -- Call to find out the summarization related indicators
3914 --
3915 check_update_smrztn_rqrmnt(p_effective_start_date => l_effective_start_date,
3916 p_indicator_rec => l_indicator_rec,
3917 p_summarization_ind_prev => l_summarization_ind_prev);
3918 --
3919 -- Store the previous indicator value for comparison purpose in the next
3920 -- iteration
3921 --
3922 l_summarization_ind_prev := l_indicator_rec.summarization_rqd_ind;
3923 --
3924 END IF;
3925 --
3926 -- Store the last promotion date (to calculate days since last promotion)
3927 --
3928 IF (l_indicator_rec.promotion_ind = 1 OR
3929 l_last_promotion_date IS NULL) THEN
3930 l_last_promotion_date := l_effective_start_date;
3931 END IF;
3932 --
3933 -- -------------------------------------------------------------------------
3934 -- Store the value of columns into PL/SQL tables for bulk insert
3935 -- -------------------------------------------------------------------------
3936 --
3937 -- Maintain counter of rows to insert
3938 --
3939 l_row_count := l_row_count + 1;
3940 --
3941 -- Primary Key
3942 --
3943 p_asgn_events_tab(l_row_count).assignment_id := g_assignment_id;
3944 p_asgn_events_tab(l_row_count).effective_change_date := l_effective_start_date;
3945 p_asgn_events_tab(l_row_count).effective_change_end_date := l_effective_end_date;
3946 --
3947 --Id Keys
3948 --
3949 p_asgn_events_tab(l_row_count).person_id :=
3950 p_asg_change_tab(l_index_rec.asg_index).person_id;
3951 --
3952 -- Assignment related FK ID's which are present in the
3953 -- assignment records after the event
3954 --
3955 p_asgn_events_tab(l_row_count).business_group_id :=
3956 p_asg_change_tab(l_index_rec.asg_index).business_group_id;
3957 p_asgn_events_tab(l_row_count).grade_id :=
3958 p_asg_change_tab(l_index_rec.asg_index).grade_id;
3959 p_asgn_events_tab(l_row_count).job_id :=
3960 p_asg_change_tab(l_index_rec.asg_index).job_id;
3961 p_asgn_events_tab(l_row_count).location_id :=
3962 p_asg_change_tab(l_index_rec.asg_index).location_id;
3963 p_asgn_events_tab(l_row_count).organization_id :=
3964 p_asg_change_tab(l_index_rec.asg_index).organization_id;
3965 p_asgn_events_tab(l_row_count).supervisor_id :=
3966 p_asg_change_tab(l_index_rec.asg_index).supervisor_id;
3967 p_asgn_events_tab(l_row_count).position_id :=
3968 p_asg_change_tab(l_index_rec.asg_index).position_id;
3969 p_asgn_events_tab(l_row_count).primary_flag :=
3970 p_asg_change_tab(l_index_rec.asg_index).primary_flag;
3971 p_asgn_events_tab(l_row_count).pow_start_date :=
3972 p_asg_change_tab(l_index_rec.asg_index).hire_date;
3973 p_asgn_events_tab(l_row_count).pow_start_date_adj :=
3974 p_asg_dates.pow_start_date_adj;
3975 p_asgn_events_tab(l_row_count).change_reason_code :=
3976 p_asg_change_tab(l_index_rec.asg_index).change_reason_code;
3977 p_asgn_events_tab(l_row_count).leaving_reason_code :=
3978 p_asg_change_tab(l_index_rec.asg_index).leaving_reason_code;
3979 p_asgn_events_tab(l_row_count).asg_type_code :=
3980 p_asg_change_tab(l_index_rec.asg_index).type;
3981 --
3982 -- Assignment releated FK ID's existing prior to the event
3983 --
3984 p_asgn_events_tab(l_row_count).grade_prv_id :=
3985 p_asg_change_tab(l_index_rec.asg_index_prev).grade_id;
3986 p_asgn_events_tab(l_row_count).job_prv_id :=
3987 p_asg_change_tab(l_index_rec.asg_index_prev).job_id;
3988 p_asgn_events_tab(l_row_count).location_prv_id :=
3989 p_asg_change_tab(l_index_rec.asg_index_prev).location_id;
3990 p_asgn_events_tab(l_row_count).organization_prv_id :=
3991 p_asg_change_tab(l_index_rec.asg_index_prev).organization_id;
3992 p_asgn_events_tab(l_row_count).supervisor_prv_id :=
3993 p_asg_change_tab(l_index_rec.asg_index_prev).supervisor_id;
3994 p_asgn_events_tab(l_row_count).position_prv_id :=
3995 p_asg_change_tab(l_index_rec.asg_index_prev).position_id;
3996 p_asgn_events_tab(l_row_count).primary_flag_prv :=
3997 p_asg_change_tab(l_index_rec.asg_index_prev).primary_flag;
3998 --
3999 -- Separation Category related measure for a person
4000 -- Bug 4519711 - only call function if assignment type is 'E' and there
4001 -- is a separation
4002 --
4003 IF (p_asg_change_tab(l_index_rec.asg_index_prev).type = 'E' AND
4004 (l_indicator_rec.term_voluntary_ind = 1 OR
4005 l_indicator_rec.term_involuntary_ind = 1))
4006 THEN
4007 --
4008 p_asgn_events_tab(l_row_count).separation_category :=
4009 hri_bpl_termination.get_separation_category
4010 (p_asg_change_tab(l_index_rec.asg_index).leaving_reason_code);
4011 --
4012 ELSE
4013 --
4014 p_asgn_events_tab(l_row_count).separation_category := 'NA_EDW';
4015 --
4016 END IF;
4017 --
4018 -- bug 4558443 - use pow_start_date_adj
4019 --
4020 p_asgn_events_tab(l_row_count).pow_days_on_event_date :=
4021 l_effective_start_date - p_asg_dates.pow_start_date_adj;
4022 p_asgn_events_tab(l_row_count).pow_months_on_event_date :=
4023 MONTHS_BETWEEN(l_effective_start_date,
4024 p_asg_dates.pow_start_date_adj);
4025 --
4026 -- Headcount related Measures and information for an assignment
4027 --
4028 p_asgn_events_tab(l_row_count).fte := l_placeholder_rec.fte;
4029 p_asgn_events_tab(l_row_count).fte_prv := l_placeholder_rec.fte_prv;
4030 p_asgn_events_tab(l_row_count).headcount := l_placeholder_rec.headcount;
4031 p_asgn_events_tab(l_row_count).headcount_prv := l_placeholder_rec.headcount_prv;
4032 --
4033 -- Salary related Measures and information for a person
4034 --
4035 p_asgn_events_tab(l_row_count).anl_slry :=
4036 p_sal_change_tab(l_index_rec.sal_index).anl_slry;
4037 p_asgn_events_tab(l_row_count).anl_slry_prv :=
4038 p_sal_change_tab(l_index_rec.sal_index_prev).anl_slry;
4039 p_asgn_events_tab(l_row_count).anl_slry_currency :=
4040 p_sal_change_tab(l_index_rec.sal_index).anl_slry_currency;
4041 p_asgn_events_tab(l_row_count).anl_slry_currency_prv :=
4042 p_sal_change_tab(l_index_rec.sal_index_prev).anl_slry_currency;
4043 p_asgn_events_tab(l_row_count).pay_proposal_id :=
4044 p_sal_change_tab(l_index_rec.sal_index).pay_proposal_id;
4045 --
4046 -- Performance related measure for a person
4047 --
4048 p_asgn_events_tab(l_row_count).perf_nrmlsd_rating :=
4049 NVL(p_perf_change_tab(l_index_rec.perf_index).nrmlsd_rating, -1);
4050 p_asgn_events_tab(l_row_count).perf_nrmlsd_rating_prv :=
4051 NVL(p_perf_change_tab(l_index_rec.perf_index_prev).nrmlsd_rating, -1);
4052 p_asgn_events_tab(l_row_count).perf_band :=
4053 NVL(p_perf_change_tab(l_index_rec.perf_index).band, g_perf_not_rated_id);
4054 p_asgn_events_tab(l_row_count).perf_band_prv :=
4055 NVL(p_perf_change_tab(l_index_rec.perf_index_prev).band, g_perf_not_rated_id);
4056 p_asgn_events_tab(l_row_count).performance_review_id :=
4057 p_perf_change_tab(l_index_rec.perf_index).review_id;
4058 p_asgn_events_tab(l_row_count).perf_review_type_cd :=
4059 p_perf_change_tab(l_index_rec.perf_index).review_type_cd;
4060 p_asgn_events_tab(l_row_count).performance_rating_cd :=
4061 p_perf_change_tab(l_index_rec.perf_index).rating_cd;
4062 p_asgn_events_tab(l_row_count).days_since_last_prmtn :=
4063 l_effective_start_date - l_last_promotion_date;
4064 p_asgn_events_tab(l_row_count).months_since_last_prmtn :=
4065 MONTHS_BETWEEN(l_effective_start_date, l_last_promotion_date);
4066 --
4067 -- Person type related measures for a person
4068 --
4069 p_asgn_events_tab(l_row_count).prsntyp_sk_fk :=
4070 NVL(p_asg_change_tab(l_index_rec.asg_index).prsntyp_sk_fk,-1);
4071 p_asgn_events_tab(l_row_count).summarization_rqd_ind :=
4072 NVL(l_indicator_rec.summarization_rqd_ind, 1);
4073 p_asgn_events_tab(l_row_count).summarization_rqd_chng_ind :=
4074 NVL(l_indicator_rec.summarization_rqd_chng_ind, 0);
4075 p_asgn_events_tab(l_row_count).summarization_rqd_chng_nxt_ind :=
4076 NVL(l_indicator_rec.summarization_rqd_chng_nxt_ind, 0);
4077 --
4078 -- Indicator assignment with values that gets set in the set_indicators procedure
4079 --
4080 p_asgn_events_tab(l_row_count).asg_rtrspctv_strt_event_ind :=
4081 l_indicator_rec.asg_rtrspctv_strt_event_ind;
4082 p_asgn_events_tab(l_row_count).assignment_change_ind :=
4083 l_indicator_rec.assignment_change_ind;
4084 p_asgn_events_tab(l_row_count).salary_change_ind :=
4085 l_indicator_rec.salary_change_ind;
4086 --
4087 -- Setting the performance rating indicators
4088 --
4089 p_asgn_events_tab(l_row_count).perf_rating_change_ind :=
4090 l_indicator_rec.perf_change_ind;
4091 p_asgn_events_tab(l_row_count).perf_band_change_ind :=
4092 l_indicator_rec.perf_band_change_ind;
4093 --
4094 -- Setting the period of work related measure for a person
4095 --
4096 p_asgn_events_tab(l_row_count).pow_band_sk_fk :=
4097 l_placeholder_rec.pow_band_sk_fk;
4098 p_asgn_events_tab(l_row_count).pow_band_prv_sk_fk :=
4099 l_placeholder_rec.pow_band_sk_fk_prv;
4100
4101 p_asgn_events_tab(l_row_count).pow_extn_strt_dt :=
4102 l_placeholder_rec.pow_extn_strt_dt;
4103 --
4104 -- Setting the period of work related indicators
4105 --
4106 p_asgn_events_tab(l_row_count).pow_band_change_ind :=
4107 l_indicator_rec.pow_band_change_ind;
4108 --
4109 p_asgn_events_tab(l_row_count).headcount_gain_ind :=
4110 l_indicator_rec.headcount_gain_ind;
4111 p_asgn_events_tab(l_row_count).headcount_loss_ind :=
4112 l_indicator_rec.headcount_loss_ind;
4113 p_asgn_events_tab(l_row_count).fte_gain_ind :=
4114 l_indicator_rec.fte_gain_ind;
4115 p_asgn_events_tab(l_row_count).fte_loss_ind :=
4116 l_indicator_rec.fte_loss_ind;
4117 p_asgn_events_tab(l_row_count).contingent_ind :=
4118 l_indicator_rec.contingent_ind;
4119 p_asgn_events_tab(l_row_count).employee_ind :=
4120 l_indicator_rec.employee_ind;
4121 p_asgn_events_tab(l_row_count).grade_change_ind :=
4122 l_indicator_rec.grade_change_ind;
4123 p_asgn_events_tab(l_row_count).job_change_ind :=
4124 l_indicator_rec.job_change_ind;
4125 p_asgn_events_tab(l_row_count).position_change_ind :=
4126 l_indicator_rec.position_change_ind;
4127 p_asgn_events_tab(l_row_count).location_change_ind :=
4128 l_indicator_rec.location_change_ind;
4129 p_asgn_events_tab(l_row_count).organization_change_ind :=
4130 l_indicator_rec.organization_change_ind;
4131 p_asgn_events_tab(l_row_count).supervisor_change_ind :=
4132 l_indicator_rec.supervisor_change_ind;
4133 p_asgn_events_tab(l_row_count).worker_hire_ind :=
4134 l_indicator_rec.worker_hire_ind;
4135 p_asgn_events_tab(l_row_count).post_hire_asgn_start_ind :=
4136 l_indicator_rec.post_hire_asgn_start_ind;
4137 p_asgn_events_tab(l_row_count).pre_sprtn_asgn_end_ind :=
4138 l_indicator_rec.pre_sprtn_asgn_end_ind;
4139 p_asgn_events_tab(l_row_count).term_voluntary_ind :=
4140 l_indicator_rec.term_voluntary_ind;
4141 p_asgn_events_tab(l_row_count).term_involuntary_ind :=
4142 l_indicator_rec.term_involuntary_ind;
4143 p_asgn_events_tab(l_row_count).worker_term_ind :=
4144 l_indicator_rec.worker_term_ind;
4145 p_asgn_events_tab(l_row_count).start_asg_sspnsn_ind :=
4146 l_indicator_rec.start_asg_sspnsn_ind;
4147 p_asgn_events_tab(l_row_count).end_asg_sspnsn_ind :=
4148 l_indicator_rec.end_asg_sspnsn_ind;
4149 p_asgn_events_tab(l_row_count).promotion_ind :=
4150 l_indicator_rec.promotion_ind;
4151 --
4152 IF (l_row_count > l_first_row_index) THEN
4153 --
4154 p_asgn_events_tab(l_row_count - 1).worker_term_nxt_ind :=
4155 l_indicator_rec.worker_term_ind;
4156 p_asgn_events_tab(l_row_count - 1).term_voluntary_nxt_ind :=
4157 l_indicator_rec.term_voluntary_ind;
4158 p_asgn_events_tab(l_row_count - 1).term_involuntary_nxt_ind :=
4159 l_indicator_rec.term_involuntary_ind;
4160 p_asgn_events_tab(l_row_count - 1).supervisor_change_nxt_ind :=
4161 l_indicator_rec.supervisor_change_ind;
4162 p_asgn_events_tab(l_row_count - 1).pre_sprtn_asgn_end_nxt_ind :=
4163 l_indicator_rec.pre_sprtn_asgn_end_ind;
4164 --
4165 -- Separation Category Changes
4166 --
4167 p_asgn_events_tab(l_row_count - 1).separation_category_nxt :=
4168 p_asgn_events_tab(l_row_count).separation_category;
4169 --
4170 --
4171 --
4172 p_asgn_events_tab(l_row_count - 1).summarization_rqd_chng_nxt_ind :=
4173 p_asgn_events_tab(l_row_count).summarization_rqd_chng_ind;
4174 --
4175 END IF;
4176 --
4177 -- End of populate Events table with change details
4178 --
4179 -- -------------------------------------------------------------------------
4180 -- Setting up variables to be used in the next loop iteration
4181 --
4182 -- Assign the previous assignment and salary index values to be
4183 -- used for the next iteration of the loop
4184 --
4185 l_index_rec.asg_index_prev := l_index_rec.asg_index;
4186 l_index_rec.sal_index_prev := l_index_rec.sal_index;
4187 --
4188 -- Assign the previous performance index values to be
4189 -- used for the next iteration of the loop
4190 --
4191 l_index_rec.perf_index_prev := l_index_rec.perf_index;
4192 --
4193 -- Store the previous ABV values for use in the next iteration of the loop
4194 --
4195 l_placeholder_rec.fte_prv := l_placeholder_rec.fte;
4196 l_placeholder_rec.headcount_prv := l_placeholder_rec.headcount;
4197 --
4198 -- Assign the previous period of work index values to be
4199 -- used for the next iteration of the loop
4200 --
4201 l_placeholder_rec.pow_band_sk_fk_prv := l_placeholder_rec.pow_band_sk_fk;
4202 --
4203 -- Move to next record
4204 --
4205 l_index_rec.date_index := p_date_master_tab.NEXT(l_index_rec.date_index);
4206 --
4207 dbg('change date='||p_asgn_events_tab(l_row_count).effective_change_date);
4208 --
4209 END LOOP;
4210 --
4211 -- Finished Looping through all changes in the date index table
4212 --
4213 -- Add missing indicators
4214 p_asgn_events_tab(l_row_count).worker_term_nxt_ind := TO_NUMBER(NULL);
4215 p_asgn_events_tab(l_row_count).term_voluntary_nxt_ind := TO_NUMBER(NULL);
4216 p_asgn_events_tab(l_row_count).term_involuntary_nxt_ind := TO_NUMBER(NULL);
4217 p_asgn_events_tab(l_row_count).supervisor_change_nxt_ind := TO_NUMBER(NULL);
4218 p_asgn_events_tab(l_row_count).pre_sprtn_asgn_end_nxt_ind := TO_NUMBER(NULL);
4219 p_asgn_events_tab(l_row_count).separation_category_nxt := 'NA_EDW';
4220 --
4221 -- Assigning the indicator values for updating the next indicator columns in
4222 -- table HRI_MB_ASGN_EVENTS
4223 --
4224 p_nxt_ind_rec.worker_term_nxt_ind :=
4225 p_asgn_events_tab(l_first_row_index).worker_term_ind;
4226 p_nxt_ind_rec.term_voluntary_nxt_ind :=
4227 p_asgn_events_tab(l_first_row_index).term_voluntary_ind;
4228 p_nxt_ind_rec.term_involuntary_nxt_ind :=
4229 p_asgn_events_tab(l_first_row_index).term_involuntary_ind;
4230 p_nxt_ind_rec.supervisor_change_nxt_ind :=
4231 p_asgn_events_tab(l_first_row_index).supervisor_change_ind;
4232 p_nxt_ind_rec.pre_sprtn_asgn_end_nxt_ind :=
4233 p_asgn_events_tab(l_first_row_index).pre_sprtn_asgn_end_ind;
4234 p_nxt_ind_rec.summarization_rqd_chng_nxt_ind :=
4235 p_asgn_events_tab(l_first_row_index).summarization_rqd_chng_ind;
4236 --
4237 END merge_and_insert_data;
4238 --
4239 -- ----------------------------------------------------------------------------
4240 -- 5H Update End Record
4241 -- For Incremental Refresh, this process end dates the previous assignment
4242 -- record (with change_date < earliest_change_date) and populates the
4243 -- NXT type indicator columns
4244 -- ----------------------------------------------------------------------------
4245 --
4246 PROCEDURE update_end_record(p_nxt_ind_rec IN g_nxt_ind_record
4247 ,p_row_id IN ROWID) IS
4248 BEGIN
4249 --
4250 dbg('Entering update_end_record');
4251 --
4252 -- End date the assignment records for the assignment that ovelap the
4253 -- earliest event date
4254 --
4255 UPDATE hri_mb_asgn_events_ct
4256 SET effective_change_end_date = (g_refresh_start_date - 1),
4257 worker_term_nxt_ind = p_nxt_ind_rec.worker_term_nxt_ind,
4258 term_voluntary_nxt_ind = p_nxt_ind_rec.term_voluntary_nxt_ind,
4259 term_involuntary_nxt_ind = p_nxt_ind_rec.term_involuntary_nxt_ind,
4260 supervisor_change_nxt_ind = p_nxt_ind_rec.supervisor_change_nxt_ind,
4261 pre_sprtn_asgn_end_nxt_ind = p_nxt_ind_rec.pre_sprtn_asgn_end_nxt_ind,
4262 summarization_rqd_chng_nxt_ind= p_nxt_ind_rec.summarization_rqd_chng_nxt_ind
4263 WHERE ROWID = p_row_id;
4264 --
4265 dbg('Existing update_end_record');
4266 --
4267 END update_end_record;
4268 --
4269 -- ----------------------------------------------------------------------------
4270 -- 5 Collect
4271 -- The Main Collection Process which is called from archive_code.
4272 -- It calls procedures 5A to 5H
4273 -- ----------------------------------------------------------------------------
4274 --
4275 PROCEDURE collect(p_assignment_id IN NUMBER,
4276 p_asgn_events_tab IN OUT NOCOPY g_asgn_events_tab_type) IS
4277 --
4278 -- Data structures that are passed to the assignment, abv and
4279 -- salary routines. These variables cannot be included in a single
4280 -- array to facilitate bulk fetch from cursors and bulk insert into
4281 -- the events table.
4282 --
4283 --
4284 -- Assignment related pl/sql tables
4285 --
4286 l_asg_change_tab g_asg_change_tab_type;
4287 --
4288 -- Salary related pl/sql tables
4289 --
4290 l_sal_change_tab g_sal_change_tab_type;
4291 --
4292 -- Performance rating related PLSQL tables
4293 --
4294 l_perf_change_tab g_perf_change_tab_type;
4295 --
4296 -- PLSQL table representing master date-transposed database table
4297 --
4298 l_date_master_tab g_master_tab_type;
4299
4300 --
4301 -- Type containing hiring date, termination date, secondary assignment start
4302 -- date, secondary assignment end date, assignment start date, assignment end
4303 -- date
4304 --
4305 l_asg_dates g_asg_date_type;
4306 --
4307 -- Type for previous records
4308 --
4309 l_prv_rec g_prv_record;
4310 --
4311 -- Holds the present and previous fte and headcount values and is passed to
4312 -- set_indicators procedure to set the indicators
4313 --
4314 l_placeholder_rec g_placeholder_rec;
4315 --
4316 -- Holds the indicator values to update the next indicator columns in the
4317 -- table HRI_MB_ASGN_EVENTS_CT during incremental refresh
4318 --
4319 l_nxt_ind_rec g_nxt_ind_record;
4320 --
4321 --
4322 BEGIN
4323 --
4324 dbg('Inside collect');
4325 dbg('-------------------------------------------------------------------');
4326 dbg('Collecting assignment events data for assignment_id:'||p_assignment_id);
4327 --
4328 -- Initialize global variables
4329 --
4330 g_assignment_id := p_assignment_id;
4331 g_refresh_start_date := GREATEST(g_refresh_start_date, g_dbi_collection_start_date);
4332 dbg('Collecting data from '||g_refresh_start_date);
4333 --
4334 -- 5B Identify Assignment Changes
4335 --
4336 dbg('Calling identify_assignment_changes');
4337 --
4338 IDENTIFY_ASSIGNMENT_CHANGES(
4339 p_date_master_tab => l_date_master_tab
4340 ,p_asg_change_tab => l_asg_change_tab
4341 ,p_asg_dates => l_asg_dates);
4342 --
4343 -- 5C Identify ABV Changes
4344 --
4345 dbg('Calling identify_abv_changes');
4346 --
4347 IDENTIFY_ABV_CHANGES(
4348 p_asg_dates => l_asg_dates
4349 ,p_date_master_tab => l_date_master_tab
4350 ,p_prv_rec => l_prv_rec);
4351 --
4352 -- 5D Fill gaps in ABV history
4353 -- Fill the gaps through fast formula if ABV is not assignned for the period
4354 --
4355 dbg('Calling fill_gaps_in_abv_history');
4356 --
4357 FILL_GAPS_IN_ABV_HISTORY(
4358 p_asg_dates => l_asg_dates
4359 ,p_date_master_tab => l_date_master_tab
4360 ,p_business_group_id => l_asg_change_tab(1).business_group_id);
4361 --
4362 -- 5E Identify Salary Changes
4363 --
4364 dbg('Calling identify_salary_changes');
4365 --
4366 IDENTIFY_SALARY_CHANGES(
4367 p_asg_dates => l_asg_dates
4368 ,p_date_master_tab => l_date_master_tab
4369 ,p_sal_change_tab => l_sal_change_tab);
4370 --
4371 -- 5F Identify Performance Rating Changes
4372 --
4373 dbg('Calling identify_perf_rating_changes');
4374 --
4375 IDENTIFY_PERF_RATING_CHANGES (
4376 p_asg_dates => l_asg_dates,
4377 p_person_id => l_asg_change_tab(1).person_id,
4378 p_business_group_id => l_asg_change_tab(1).business_group_id,
4379 p_date_master_tab => l_date_master_tab,
4380 p_perf_change_tab => l_perf_change_tab);
4381 --
4382 -- 5G Identify Period Of Work Band Changes
4383 --
4384 dbg('Calling identify_pow_band_changes');
4385 --
4386 IDENTIFY_POW_BAND_CHANGES(
4387 p_asg_dates => l_asg_dates,
4388 p_person_id => l_asg_change_tab(1).person_id,
4389 p_date_master_tab => l_date_master_tab,
4390 p_assignment_type => l_asg_change_tab(1).type);
4391 --
4392 -- 5G1 Identify Person Type Changes
4393 --
4394 dbg('Calling identify_prsn_typ_changes');
4395 --
4396 --
4397 -- 5H Set Previous Values
4398 --
4399 -- Set the previous values of grade_id, job_id, location_id, organization_id,
4400 -- supervisor_id, position_id, primary_flag, fte, headcount, anl_slry and
4401 -- anl_slry_currency, as they exists one day before the incremental refresh
4402 -- For full refresh set up a default record for values before the refresh
4403 -- start date
4404 --
4405 dbg('Calling set_previous_values');
4406 --
4407 SET_PREVIOUS_VALUES(
4408 p_prv_rec => l_prv_rec
4409 ,p_business_group_id => l_asg_change_tab(1).business_group_id
4410 ,p_date_master_tab => l_date_master_tab
4411 ,p_hire_date => l_asg_dates.hire_date);
4412 --
4413 -- Set the values that existed before the refresh start date. The values were
4414 -- set in the procedure set_previous_values
4415 --
4416 --
4417 -- Set the assignment related values
4418 --
4419 dbg('Assigning assignment related previous values to local arrays');
4420 l_asg_change_tab(0).organization_id := l_prv_rec.organization_prv_id;
4421 l_asg_change_tab(0).location_id := l_prv_rec.location_prv_id;
4422 l_asg_change_tab(0).job_id := l_prv_rec.job_prv_id;
4423 l_asg_change_tab(0).grade_id := l_prv_rec.grade_prv_id;
4424 l_asg_change_tab(0).position_id := l_prv_rec.position_prv_id;
4425 l_asg_change_tab(0).supervisor_id := l_prv_rec.supervisor_prv_id;
4426 l_asg_change_tab(0).primary_flag := l_prv_rec.primary_flag_prv;
4427 l_asg_change_tab(0).summarization_rqd_ind := l_prv_rec.summarization_rqd_ind_prv;
4428 l_asg_change_tab(0).status_code := 'NA_EDW';
4429 --
4430 -- Set the salary related values
4431 --
4432 dbg('Assigning salary related previous values to local arrays');
4433 l_sal_change_tab(0).anl_slry := l_prv_rec.anl_slry_prv;
4434 l_sal_change_tab(0).pay_proposal_id := -1;
4435 l_sal_change_tab(0).anl_slry_currency := l_prv_rec.anl_slry_currency_prv ;
4436 --
4437 -- Set the performance change related values
4438 --
4439 l_perf_change_tab(0).review_id := -1;
4440 l_perf_change_tab(0).review_type_cd := -1;
4441 l_perf_change_tab(0).rating_cd := -1;
4442 l_perf_change_tab(0).nrmlsd_rating := l_prv_rec.perf_nrmlsd_rating_prv;
4443 l_perf_change_tab(0).band := l_prv_rec.perf_band_prv;
4444 --
4445 -- Set the period of work change related changes
4446 --
4447 --
4448 -- Set the ABV related values
4449 --
4450 dbg('Assigning ABV related previous values to local arrays');
4451 l_placeholder_rec.fte_prv := l_prv_rec.fte_prv;
4452 l_placeholder_rec.headcount_prv := l_prv_rec.headcount_prv;
4453 --
4454 -- Assign the person type fk's
4455 --
4456 l_placeholder_rec.pow_band_sk_fk_prv := l_prv_rec.pow_band_sk_fk_prv;
4457 --
4458 -- 5I Merge and insert data
4459 -- Merge the data collected and insert into the table HRI_MB_ASGN_EVENTS_CT
4460 --
4461 dbg('Calling merge_and_insert_data');
4462 --
4463 MERGE_AND_INSERT_DATA(
4464 p_date_master_tab => l_date_master_tab
4465 ,p_asg_change_tab => l_asg_change_tab
4466 ,p_sal_change_tab => l_sal_change_tab
4467 ,p_perf_change_tab => l_perf_change_tab
4468 ,p_asg_dates => l_asg_dates
4469 ,p_prv_rec => l_prv_rec
4470 ,p_nxt_ind_rec => l_nxt_ind_rec
4471 ,p_asgn_events_tab => p_asgn_events_tab);
4472 --
4473 -- If incremental refresh
4474 --
4475 IF g_full_refresh = 'N' THEN
4476 --
4477 -- 5J Update End Record
4478 --
4479 -- End date the assignment records for the assignment that ovelap the earliest
4480 -- event date
4481 --
4482 dbg('Calling update_end_record');
4483 --
4484 UPDATE_END_RECORD(p_nxt_ind_rec => l_nxt_ind_rec
4485 ,p_row_id => l_prv_rec.row_id );
4486 --
4487 END IF;
4488 --
4489 dbg('Finished collecting assignment events for assignment_id:'||p_assignment_id);
4490 dbg('-------------------------------------------------------------------');
4491 --
4492 END collect;
4493 --
4494 -- ----------------------------------------------------------------------------
4495 -- shared_hrms_dflt_prcss
4496 -- This process will be launched by shared_hrms_dflt_prcss (OVERLOADED).
4497 -- ============================================================================
4498 -- This procedure contains the code required to populate hri_mb_asgn_events_ct in shared
4499 -- HR.
4500 --
4501 PROCEDURE shared_hrms_dflt_prcss
4502 IS
4503 --
4504 l_dummy1 VARCHAR2(2000);
4505 l_dummy2 VARCHAR2(2000);
4506 l_schema VARCHAR2(400);
4507 l_sql_stmt VARCHAR2(500);
4508 l_user_id NUMBER;
4509 l_current_time DATE;
4510 --
4511 BEGIN
4512 --
4513 dbg('Populating hri_mb_asgn_events_ct in shared HR');
4514 --
4515 l_user_id := fnd_global.user_id;
4516 l_current_time := SYSDATE;
4517 --
4518 -- 4126398, Added new columns and changed all default values for indicators to 0
4519 -- Inserts row
4520 --
4521 INSERT /*+ APPEND */ INTO hri_mb_asgn_events_ct
4522 (event_id,
4523 effective_change_date,
4524 effective_change_end_date,
4525 assignment_id,
4526 person_id,
4527 grade_id,
4528 grade_prv_id,
4529 job_id,
4530 job_prv_id,
4531 location_id,
4532 location_prv_id,
4533 organization_id,
4534 organization_prv_id,
4535 supervisor_id,
4536 supervisor_prv_id,
4537 position_id,
4538 position_prv_id,
4539 primary_flag,
4540 primary_flag_prv,
4541 pow_start_date_adj,
4542 change_reason_code,
4543 leaving_reason_code,
4544 fte,
4545 fte_prv,
4546 headcount,
4547 headcount_prv,
4548 anl_slry,
4549 anl_slry_prv,
4550 anl_slry_currency,
4551 anl_slry_currency_prv,
4552 pay_proposal_id,
4553 asg_rtrspctv_strt_event_ind,
4554 assignment_change_ind,
4555 salary_change_ind,
4556 headcount_gain_ind,
4557 headcount_loss_ind,
4558 fte_gain_ind,
4559 fte_loss_ind,
4560 contingent_ind,
4561 employee_ind,
4562 grade_change_ind,
4563 job_change_ind,
4564 position_change_ind,
4565 location_change_ind,
4566 organization_change_ind,
4567 supervisor_change_ind,
4568 worker_hire_ind,
4569 post_hire_asgn_start_ind,
4570 pre_sprtn_asgn_end_ind,
4571 term_voluntary_ind,
4572 term_involuntary_ind,
4573 worker_term_ind,
4574 start_asg_sspnsn_ind,
4575 end_asg_sspnsn_ind,
4576 last_update_date,
4577 last_updated_by,
4578 last_update_login,
4579 created_by,
4580 creation_date,
4581 effective_change_date_prv,
4582 worker_term_nxt_ind,
4583 pre_sprtn_asgn_end_nxt_ind,
4584 supervisor_change_nxt_ind,
4585 term_voluntary_nxt_ind,
4586 term_involuntary_nxt_ind,
4587 pow_days_on_event_date,
4588 separation_category,
4589 separation_category_nxt,
4590 pow_months_on_event_date,
4591 pow_start_date,
4592 pow_band_change_ind,
4593 perf_nrmlsd_rating,
4594 perf_nrmlsd_rating_prv,
4595 performance_review_id,
4596 perf_review_type_cd,
4597 performance_rating_cd,
4598 perf_band,
4599 perf_band_prv,
4600 perf_rating_change_ind,
4601 perf_band_change_ind,
4602 prsntyp_sk_fk,
4603 summarization_rqd_ind
4604 )
4605 SELECT
4606 hri_mb_asgn_events_ct_s.nextval event_id,
4607 GREATEST(pos.date_start, g_dbi_collection_start_date) effective_change_date,
4608 NVL(pos.actual_termination_date,g_end_of_time) effective_change_end_date,
4609 asg.assignment_id assignment_id,
4610 asg.person_id person_id,
4611 -1 grade_id,
4612 -1 grade_prv_id,
4613 NVL(asg.job_id,-1) job_id,
4614 -1 job_prv_id,
4615 NVL(asg.location_id,-1) location_id,
4616 -1 location_prv_id,
4617 NVL(asg.organization_id,-1) organization_id,
4618 -1 organization_prv_id,
4619 NVL(asg.supervisor_id,-1) supervisor_id,
4620 -1 supervisor_prv_id,
4621 NVL(asg.position_id,-1) position_id,
4622 -1 position_prv_id,
4623 asg.primary_flag primary_flag,
4624 'NA_EDW' primary_flag_prv,
4625 pos.adjusted_svc_date adjusted_svc_date,
4626 'NA_EDW' change_reason_code,
4627 'NA_EDW' leaving_reason_code,
4628 1 fte,
4629 0 fte_prv,
4630 1 headcount,
4631 0 headcount_prv,
4632 0 anl_slry,
4633 0 anl_slry_prv,
4634 'NA_EDW' anl_slry_currency,
4635 'NA_EDW' anl_slry_currency_prv,
4636 -1 pay_proposal_id,
4637 0 asg_rtrspctv_strt_event_ind,
4638 0 assignment_change_ind,
4639 0 salary_change_ind,
4640 0 headcount_gain_ind,
4641 0 headcount_loss_ind,
4642 0 fte_gain_ind,
4643 0 fte_loss_ind,
4644 0 contingent_ind,
4645 1 employee_ind,
4646 0 grade_change_ind,
4647 0 job_change_ind,
4648 0 position_change_ind,
4649 0 location_change_ind,
4650 0 organization_change_ind,
4651 0 supervisor_change_ind,
4652 0 worker_hire_ind,
4653 0 post_hire_asgn_start_ind,
4654 0 pre_sprtn_asgn_end_ind,
4655 0 term_voluntary_ind,
4656 0 term_involuntary_ind,
4657 0 worker_term_ind,
4658 0 start_asg_sspnsn_ind,
4659 0 end_asg_sspnsn_ind,
4660 l_current_time last_update_date,
4661 l_user_id last_updated_by,
4662 l_user_id last_update_login,
4663 l_user_id created_by,
4664 l_current_time creation_date,
4665 null effective_change_date_prv,
4666 0 worker_term_nxt_ind,
4667 0 pre_sprtn_asgn_end_nxt_ind,
4668 0 supervisor_change_nxt_ind,
4669 0 term_voluntary_nxt_ind,
4670 0 term_involuntary_nxt_ind,
4671 0 pow_days_on_event_date,
4672 'NA_EDW' separation_category,
4673 'NA_EDW' separation_category_nxt,
4674 months_between(SYSDATE,pos.date_start) pow_months_on_event_date,
4675 pos.date_start pow_start_date,
4676 0 pow_band_change_ind,
4677 -1 perf_nrmlsd_rating,
4678 -1 perf_nrmlsd_rating_prv,
4679 -1 performance_review_id,
4680 'NA_EDW' perf_review_type_cd,
4681 'NA_EDW' performance_rating_cd,
4682 g_perf_not_rated_id perf_band,
4683 g_perf_not_rated_id perf_band_prv,
4684 0 perf_rating_change_ind,
4685 0 perf_band_change_ind,
4686 hpt.prsntyp_sk_pk prsntyp_sk_fk,
4687 1 summarization_rqd_ind
4688 FROM per_all_assignments_f asg,
4689 per_periods_of_service pos,
4690 hri_cs_prsntyp_ct hpt,
4691 per_person_type_usages_f ptu
4692 WHERE asg.assignment_type = 'E'
4693 AND asg.primary_flag = 'Y'
4694 AND asg.period_of_service_id = pos.period_of_service_id
4695 AND ptu.person_id = asg.person_id
4696 AND hpt.person_type_id = ptu.person_type_id
4697 AND hpt.employment_category_code = NVL(asg.employment_category,'NA_EDW')
4698 AND hpt.primary_flag_code = NVL(asg.primary_flag,'NA_EDW')
4699 AND hpt.assignment_type_code = asg.assignment_type
4700 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date
4701 AND asg.effective_end_date
4702 AND TRUNC(SYSDATE) BETWEEN ptu.effective_start_date
4703 AND ptu.effective_end_date;
4704 --
4705 END shared_hrms_dflt_prcss;
4706 --
4707 -- ----------------------------------------------------------------------------
4708 -- shared_hrms_dflt_prcss (OVERLOADED)
4709 -- Default process executed when PYUGEN is not available.
4710 -- ============================================================================
4711 -- This process will be launched by the package HRI_BPL_PYUGEN_WRAPPER
4712 -- whenever it detects PYUGEN is not installed.
4713 --
4714 -- The parameters of this function are standard for all default processes
4715 -- called where PYUGEN does not exist. This particular package IGNORES THEM
4716 --
4717 PROCEDURE shared_hrms_dflt_prcss
4718 (
4719 errbuf OUT NOCOPY VARCHAR2
4720 ,retcode OUT NOCOPY NUMBER
4721 ,p_collect_from_date IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
4722 ,p_collect_to_date IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
4723 ,p_full_refresh IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
4724 ,p_attribute1 IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
4725 ,p_attribute2 IN VARCHAR2 DEFAULT NULL -- Optional Param default NULL
4726 )
4727 IS
4728 --
4729 BEGIN
4730 --
4731 -- Do not pass throuh IN parameters, as they are not used.
4732 --
4733 dbg('Entering the default collection process,'||
4734 ' called when foundation HR is detected.');
4735 shared_hrms_dflt_prcss;
4736 --
4737 EXCEPTION
4738 WHEN OTHERS
4739 THEN
4740 --
4741 errbuf := SQLERRM;
4742 retcode := SQLCODE;
4743 --
4744 RAISE;
4745 --
4746 --
4747 END shared_hrms_dflt_prcss;
4748 --
4749 -- -----------------------------------------------------------------------------
4750 -- Multithreading Calls --
4751 -- -----------------------------------------------------------------------------
4752 -- The Multithreading Utility Provides the Framework for processing collection
4753 -- using multiple threads. The utility dynamically invokes the following
4754 -- procedure to complete the collection process
4755 -- a) Invoke the PRE_PROCESS procedure to
4756 -- Initialize the global variables
4757 -- Manage the Indexes, MV logs and Trigger
4758 -- Return a SQL based on which the processing ranges will be created.
4759 --
4760 -- In case of Foundation HR environment the pre-process will not return
4761 -- any SQL. This will prompt the mulithtreading utility to stop processing
4762 -- without invoking the PROCESS_RANGE and POST_PROCESS procedures.
4763 -- b) Invoke the PROCESS_RANGE procedure to process the assignments in the range.
4764 -- This procedure will be invoked by all the threads that are running in
4765 -- parallel.
4766 -- c) Invoke the POST_PROCESS procedure to perform the post processing tasks
4767 -- Initialize the global variables
4768 -- Manage the Indexes, MV logs and Trigger
4769 -- Update BIS refresh Log table
4770 -- ----------------------------------------------------------------------------
4771 -- SET_PARAMETERS
4772 -- sets up global variables required for the assignment events process
4773 -- ----------------------------------------------------------------------------
4774 --
4775 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
4776 p_mthd_stage_code IN VARCHAR2) IS
4777 --
4778 l_assignment_id NUMBER;
4779 --
4780 BEGIN
4781
4782 -- If parameters haven't already been set, then set them
4783 IF (g_refresh_start_date IS NULL) THEN
4784
4785 g_dbi_collection_start_date := hri_oltp_conc_param.get_date_parameter_value
4786 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
4787 p_process_table_name => 'HRI_MB_ASGN_EVENTS_CT');
4788
4789 -- If called for the first time set the defaulted parameters
4790 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
4791
4792 g_full_refresh := hri_oltp_conc_param.get_parameter_value
4793 (p_parameter_name => 'FULL_REFRESH',
4794 p_process_table_name => 'HRI_MB_ASGN_EVENTS_CT');
4795
4796 -- Log defaulted parameters so the slave processes pick up
4797 hri_opl_multi_thread.update_parameters
4798 (p_mthd_action_id => p_mthd_action_id,
4799 p_full_refresh => g_full_refresh,
4800 p_global_start_date => g_dbi_collection_start_date);
4801
4802 END IF;
4803
4804 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
4805 (p_mthd_action_id);
4806 --
4807 g_full_refresh := g_mthd_action_array.full_refresh_flag;
4808 g_refresh_start_date := g_mthd_action_array.collect_from_date;
4809 g_refresh_end_date := hr_general.end_of_time;
4810
4811 -- Set FTE/HDC parameters from profiles
4812 IF (fnd_profile.value('HRI_COLLECT_FTE') = 'Y') THEN
4813 g_collect_fte := 'Y';
4814 ELSE
4815 g_collect_fte := 'N';
4816 END IF;
4817 IF (fnd_profile.value('HRI_COLLECT_HDC') = 'Y') THEN
4818 g_collect_hdc := 'Y';
4819 ELSE
4820 g_collect_hdc := 'N';
4821 END IF;
4822
4823 -- Set DBI parameters
4824 IF (fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
4825 g_implement_dbi := 'Y';
4826 END IF;
4827
4828 -- Set OBIEE parameters
4829 IF (fnd_profile.value('HRI_IMPL_OBIEE') = 'Y') THEN
4830 g_implement_obiee := 'Y';
4831 IF (fnd_profile.value('HRI_COL_SUP_HRCHY_EQ') = 'Y') THEN
4832 g_implement_obiee_mgrh := 'Y';
4833 ELSE
4834 g_implement_obiee_mgrh := 'N';
4835 END IF;
4836 IF (fnd_profile.value('HRI_COL_ORG_HRCHY_EQ') = 'Y') THEN
4837 g_implement_obiee_orgh := 'Y';
4838 ELSE
4839 g_implement_obiee_orgh := 'N';
4840 END IF;
4841 ELSE
4842 g_implement_obiee := 'N';
4843 g_implement_obiee_mgrh := 'N';
4844 g_implement_obiee_orgh := 'N';
4845 END IF;
4846
4847 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
4848 hri_bpl_conc_log.dbg('Collect from: ' || to_char(g_refresh_start_date));
4849 --
4850 -- Set the global variable to the performance rating query
4851 --
4852 BEGIN
4853 g_perf_query := hri_bpl_perf_rating.get_perf_sql;
4854 EXCEPTION WHEN OTHERS THEN
4855 g_msg_sub_group := NVL(g_msg_sub_group, 'GET_PERF_SQL');
4856 RAISE;
4857 END;
4858 --
4859 --
4860 END IF;
4861 --
4862 END set_parameters;
4863 --
4864 -- ----------------------------------------------------------------------------
4865 -- PRE_PROCESS
4866 -- This procedure includes the logic required for performing the pre_process
4867 -- task of HRI multithreading utility.
4868 -- ----------------------------------------------------------------------------
4869 --
4870 PROCEDURE PRE_PROCESS(
4871 p_mthd_action_id IN NUMBER,
4872 p_sqlstr OUT NOCOPY VARCHAR2) IS
4873 --
4874 l_dummy1 VARCHAR2(2000);
4875 l_dummy2 VARCHAR2(2000);
4876 l_schema VARCHAR2(400);
4877 l_message fnd_new_messages.message_text%type;
4878 --
4879 BEGIN
4880 --
4881 -- Initialize the global to hold the procedure name
4882 --
4883 -- Record the process start
4884 -- Set up the parameters
4885 --
4886 set_parameters
4887 (p_mthd_action_id => p_mthd_action_id,
4888 p_mthd_stage_code => 'PRE_PROCESS');
4889 --
4890 -- Feedback parameters selected
4891 --
4892 dbg('Parameters selected:');
4893 dbg(' Full Refresh: ' || g_full_refresh);
4894 dbg(' Collect HEAD: ' || g_collect_hdc);
4895 dbg(' Collect FTE: ' || g_collect_fte);
4896 --
4897 -- Raise a ff compile error if either of the seeded ffs to be used are not
4898 -- compiled (these are not used in shared HR mode)
4899 --
4900 IF (g_mthd_action_array.foundation_hr_flag = 'N') THEN
4901 IF (g_collect_fte = 'Y') THEN
4902 --
4903 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_FTE' );
4904 --
4905 END IF;
4906 --
4907 IF (g_collect_hdc = 'Y') THEN
4908 --
4909 hri_bpl_abv.check_ff_name_compiled( p_formula_name => 'TEMPLATE_HEAD' );
4910 --
4911 END IF;
4912 END IF;
4913 --
4914 -- Disable the WHO trigger
4915 --
4916 EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MB_ASGN_EVENTS_CT_WHO DISABLE';
4917 EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MDP_ORGH_TRANSFERS_CT_WHO DISABLE';
4918 --
4919 -- ---------------------------------------------------------------------------
4920 -- Full Refresh Section (including shared HR)
4921 -- ---------------------------------------------------------------------------
4922 --
4923 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
4924 --
4925 -- If it's a full refresh or shared HR
4926 --
4927 IF (g_full_refresh = 'Y' OR
4928 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
4929 --
4930 -- Drop Indexes
4931 --
4932 hri_utl_ddl.log_and_drop_indexes(
4933 p_application_short_name => 'HRI',
4934 p_table_name => 'HRI_MB_ASGN_EVENTS_CT',
4935 p_table_owner => l_schema);
4936 hri_utl_ddl.log_and_drop_indexes(
4937 p_application_short_name => 'HRI',
4938 p_table_name => 'HRI_MDP_ORGH_TRANSFERS_CT',
4939 p_table_owner => l_schema);
4940 --
4941 -- Truncate the tables
4942 --
4943 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_ASGN_EVENTS_CT';
4944 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MDP_ORGH_TRANSFERS_CT';
4945 --
4946 -- In shared HR mode populate the table in a single direct insert
4947 -- Do not return a SQL statement so that the process_range and
4948 -- post_process will not be executed
4949 --
4950 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
4951 --
4952 -- Call API to insert rows
4953 --
4954 shared_hrms_dflt_prcss;
4955 --
4956 -- Call post processing API
4957 --
4958 POST_PROCESS(p_mthd_action_id => p_mthd_action_id);
4959 --
4960 --
4961 -- Else full refresh in full HR mode
4962 --
4963 ELSE
4964 --
4965 --
4966 -- Select all people with employee assignments in the collection range.
4967 -- The bind variable must be present for this sql to work when called
4968 -- by PYUGEN, else itwill give error.
4969 --
4970 p_sqlstr :=
4971 'SELECT /*+ PARALLEL(asgn, DEFAULT, DEFAULT) */
4972 DISTINCT
4973 asgn.assignment_id object_id
4974 FROM per_all_assignments_f asgn
4975 WHERE asgn.assignment_type in (''E'',''C'')
4976 AND asgn.effective_end_date >= to_date(''' ||
4977 to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
4978 ''',''DD-MM-YYYY'') - 1
4979 ORDER BY asgn.assignment_id';
4980 --
4981 END IF;
4982 --
4983 -- Return the Incremental Refresh SQL based on the asg events queue
4984 --
4985 ELSE
4986 --
4987 -- Insert the period of work related incremental chnages to the assignment
4988 -- events queue.
4989 --
4990 insert_pow_change_events;
4991 --
4992 -- Populate workforce event queues
4993 --
4994 populate_wrkfc_evt_eq;
4995 populate_wrkfc_evt_mgrh_eq;
4996 populate_wrkfc_evt_orgh_eq;
4997 --
4998 -- Select all people for whom events have occurred. The bind variable must
4999 -- be present for this sql to work when called by PYUGEN, else it will
5000 -- give error.
5001 --
5002 p_sqlstr :=
5003 'SELECT /*+ PARALLEL(evt, DEFAULT, DEFAULT) */
5004 evt.assignment_id object_id
5005 FROM hri_eq_asgn_evnts evt
5006 ORDER BY evt.assignment_id';
5007 --
5008 END IF;
5009 --
5010 END IF;
5011 --
5012 EXCEPTION
5013 --
5014 WHEN others THEN
5015 --
5016 dbg('Exception raised in procedure PRE_PROCESS');
5017 --
5018 l_message := nvl(fnd_message.get,sqlerrm);
5019 --
5020 dbg(l_message);
5021 --
5022 -- Bug 4105868: Collection Diagnostic Call
5023 --
5024 g_msg_sub_group := NVL(g_msg_sub_group, 'PRE_PROCESS');
5025 --
5026 hri_bpl_conc_log.log_process_info
5027 (p_package_name => 'HRI_OPL_ASGN_EVENTS'
5028 ,p_msg_type => 'ERROR'
5029 ,p_note => l_message
5030 ,p_msg_group => 'ASG_EVT_FCT'
5031 ,p_msg_sub_group => g_msg_sub_group
5032 ,p_sql_err_code => SQLCODE);
5033 --
5034 raise;
5035 --
5036 --
5037 END PRE_PROCESS;
5038 --
5039 -- ----------------------------------------------------------------------------
5040 -- PROCESS_RANGE
5041 -- This procedure includes the logic required for processing the assignments
5042 -- which have been included in the range. It is dynamically invoked by the
5043 -- multithreading child process. It manages the multithreading ranges and
5044 -- for each range it invokes the overaloaded process_range procedure defined below.
5045 -- ----------------------------------------------------------------------------
5046 --
5047 PROCEDURE process_range(
5048 errbuf OUT NOCOPY VARCHAR2
5049 ,retcode OUT NOCOPY NUMBER
5050 ,p_mthd_action_id IN NUMBER
5051 ,p_mthd_range_id IN NUMBER
5052 ,p_start_object_id IN NUMBER
5053 ,p_end_object_id IN NUMBER)
5054 IS
5055 --
5056 l_sql VARCHAR2(2000);
5057 l_assignment_id NUMBER;
5058 l_change_date DATE;
5059 l_error_step NUMBER;
5060 l_mthd_range_id NUMBER;
5061 l_start_object_id NUMBER;
5062 l_end_object_id NUMBER;
5063 --
5064 BEGIN
5065 --
5066 --
5067 --
5068 set_parameters
5069 (p_mthd_action_id => p_mthd_action_id,
5070 p_mthd_stage_code => 'PROCESS_RANGE');
5071 --
5072 dbg('calling process_range code');
5073 process_range(p_object_range_id => p_mthd_range_id
5074 ,p_start_object_id => p_start_object_id
5075 ,p_end_object_id => p_end_object_id);
5076 --
5077 IF g_raise_warning = 'Y' THEN
5078 --
5079 errbuf := 'SUCCESS';
5080 retcode := 0;
5081 --
5082 ELSE
5083 --
5084 errbuf := 'SUCCESS';
5085 retcode := 0;
5086 --
5087 END IF;
5088 --
5089 EXCEPTION
5090 when hri_opl_multi_thread.other_thread_in_error THEN
5091 --
5092 errbuf := SQLERRM;
5093 retcode := 2;
5094 --
5095 raise hri_opl_multi_thread.child_process_failure;
5096 when others then
5097 --
5098 dbg('Error at step '||l_error_step );
5099 output(sqlerrm);
5100 --
5101 errbuf := SQLERRM;
5102 retcode := SQLCODE;
5103 --
5104 raise;
5105 --
5106 --
5107 END process_range;
5108 --
5109 -- ----------------------------------------------------------------------------
5110 -- PROCESS_RANGE
5111 -- This is an overloaded procedure which is invoked by PROCESS_RANGE above.
5112 -- For each of the assignment in the range, this procedure invokes the
5113 -- collect procedure to populate the assingment events fact table.
5114 -- ----------------------------------------------------------------------------
5115 --
5116 PROCEDURE process_range(
5117 p_object_range_id IN NUMBER
5118 ,p_start_object_id IN NUMBER
5119 ,p_end_object_id IN NUMBER )
5120 IS
5121 --
5122 -- Cursor to get the assignment_id for the assignment action for full refresh
5123 --
5124 -- Declare the ref cursor
5125 --
5126 type asg_to_process is ref cursor;
5127 --
5128 c_asg_to_process ASG_TO_PROCESS;
5129 --
5130 -- Holds assignment from the cursor
5131 --
5132 l_assignment_id NUMBER;
5133 l_change_date DATE;
5134 l_error_step NUMBER;
5135 l_message fnd_new_messages.message_text%type;
5136 l_system_date DATE;
5137 --
5138 -- PL/SQL of rows to insert into database table for the range
5139 --
5140 l_asgn_events_tab g_asgn_events_tab_type;
5141 --
5142 BEGIN
5143 --
5144 dbg('Inside Process_Range');
5145 --
5146 l_system_date := TRUNC(SYSDATE);
5147 hri_opl_wrkfc_trnsfr_events.initialize_globals;
5148 --
5149 -- Depending on the type of refresh, open the ref cursor to determine the assignments
5150 -- in a range.
5151 --
5152 IF (g_full_refresh = 'Y') THEN
5153 --
5154 l_error_step := 30;
5155 --
5156 OPEN c_asg_to_process FOR
5157 SELECT DISTINCT
5158 asg.assignment_id assignment_id,
5159 l_system_date change_date
5160 FROM per_all_assignments_f asg
5161 ,per_assignment_status_types ast
5162 WHERE asg.assignment_type in ('E','C')
5163 AND asg.assignment_id BETWEEN p_start_object_id and p_end_object_id
5164 AND ast.assignment_status_type_id = asg.assignment_status_type_id
5165 AND ast.per_system_status <> 'TERM_ASSIGN'
5166 AND asg.effective_end_date >= g_refresh_start_date - 1;
5167 --
5168 ELSE
5169 --
5170 -- Open the ref cursor for incremental assingments events. For incremental
5171 -- collection the range is created based on the events queue.
5172 --
5173 l_error_step := 40;
5174 --
5175 -- Bug 4299875
5176 -- The query for determining the list of assingments in the range should
5177 -- not connect to asg table as this prevents some of the records from
5178 -- getting processed e.g. when the person record is delete etc.
5179 --
5180 OPEN c_asg_to_process FOR
5181 SELECT DISTINCT
5182 evts.assignment_id assignment_id,
5183 erlst_evnt_effective_date change_date
5184 FROM hri_eq_asgn_evnts evts
5185 WHERE evts.assignment_id BETWEEN p_start_object_id and p_end_object_id;
5186 --
5187 END IF;
5188 --
5189 l_error_step := 50;
5190 --
5191 -- Collect the assignment event details for every assingment in the
5192 -- multithreading range.
5193 --
5194 LOOP
5195 --
5196 FETCH c_asg_to_process INTO l_assignment_id,l_change_date;
5197 EXIT WHEN c_asg_to_process%NOTFOUND;
5198 --
5199 IF g_full_refresh = 'N' THEN
5200 --
5201 g_refresh_start_date := l_change_date;
5202 --
5203 END IF;
5204 --
5205 dbg('asg = '||l_assignment_id||' l_change_date= '||l_change_date);
5206 --
5207 BEGIN
5208 --
5209 -- Call the collect procedure which collects the assignments events
5210 -- records for the assignment
5211 --
5212 COLLECT
5213 (p_assignment_id => l_assignment_id,
5214 p_asgn_events_tab => l_asgn_events_tab);
5215 --
5216 EXCEPTION
5217 --
5218 WHEN hri_bpl_abv.ff_not_compiled OR
5219 hri_bpl_perf_rating.ff_returned_invalid_value OR
5220 hri_bpl_perf_rating.ff_perf_rating_not_compiled
5221 THEN
5222 --
5223 -- Incase the fast fromula raises an exception then raise the error
5224 --
5225 l_message := fnd_message.get;
5226 --
5227 output(l_message);
5228 --
5229 raise;
5230 --
5231 WHEN NO_ASSIGNMENT_RECORD_FOUND THEN
5232 --
5233 -- Bug 4299875
5234 -- This exception is raised by procedure identify_assignment_changes
5235 -- when no records are found for assignment that are valid on the
5236 -- event date. This will occur in following cases
5237 -- a. The assignment is not a employee or congtingent asg
5238 -- b. The persson has been terminated and asg rec is updated
5239 -- after the termination date
5240 -- Details pertaining to such events need not be tracked in assignment
5241 -- event fact and can be rejected. Therefore do not throw any error
5242 --
5243 dbg('No valid assignment records found for assignment_id = '||g_assignment_id);
5244 --
5245 WHEN OTHERS THEN
5246 --
5247 g_raise_warning := 'Y';
5248 --
5249 fnd_message.set_name('HRI', 'HRI_407288_NO_ASG_RCRD_FND');
5250 fnd_message.set_token('ASSIGNMENT_ID', g_assignment_id);
5251 --
5252 l_message := fnd_message.get;
5253 --
5254 output(l_message);
5255 output(sqlerrm);
5256 --
5257 g_msg_sub_group := NVL(g_msg_sub_group, 'PROCESS_RANGE');
5258 --
5259 hri_bpl_conc_log.log_process_info
5260 (p_package_name => 'HRI_OPL_ASGN_EVENTS'
5261 ,p_msg_type => 'WARNING'
5262 ,p_note => l_message
5263 ,p_msg_group => 'ASG_EVT_FCT'
5264 ,p_msg_sub_group => g_msg_sub_group
5265 ,p_assignment_id => g_assignment_id
5266 ,p_sql_err_code => SQLCODE
5267 );
5268 --
5269 END;
5270 --
5271 END LOOP;
5272 --
5273 dbg('Done processing all assignments in the range.');
5274 --
5275 IF c_asg_to_process%ISOPEN THEN
5276 --
5277 l_error_step := 60;
5278 CLOSE c_asg_to_process;
5279 --
5280 END IF;
5281 --
5282 -- If incremental refresh
5283 --
5284 IF g_full_refresh = 'N' then
5285 --
5286 -- 5A Delete Records
5287 -- Delete all the records from the table HRI_MB_ASGN_EVENTS that starts on
5288 -- or after the refresh start date
5289 --
5290 DELETE_RECORDS
5291 (p_start_assignment_id => p_start_object_id,
5292 p_end_assignment_id => p_end_object_id);
5293 hri_opl_wrkfc_trnsfr_events.delete_transfers
5294 (p_start_object_id => p_start_object_id,
5295 p_end_object_id => p_end_object_id);
5296 --
5297 END IF;
5298 --
5299 -- Bulk insert stored rows
5300 --
5301 bulk_insert_rows(p_asgn_events_tab => l_asgn_events_tab);
5302 --
5303 hri_opl_wrkfc_trnsfr_events.bulk_insert_transfers;
5304 --
5305 -- Commit
5306 Commit;
5307 --
5308 EXCEPTION
5309 WHEN OTHERS THEN
5310 --
5311 --
5312 -- Set the warning global so the request raises a warning
5313 --
5314 g_raise_warning := 'Y';
5315 output('WARNING: Error processing assignment_id ' || TO_CHAR(g_assignment_id));
5316 output(sqlerrm);
5317 --
5318 IF c_asg_to_process%ISOPEN THEN
5319 --
5320 l_error_step := 60;
5321 CLOSE c_asg_to_process;
5322 --
5323 END IF;
5324 --
5325 -- Bug 4105868: Collection Diagnostic Call
5326 --
5327 g_msg_sub_group := NVL(g_msg_sub_group, 'PROCESS_RANGE');
5328 --
5329 hri_bpl_conc_log.log_process_info
5330 (p_package_name => 'HRI_OPL_ASGN_EVENTS'
5331 ,p_msg_type => 'ERROR'
5332 ,p_note => nvl(l_message, SQLERRM)
5333 ,p_msg_group => 'ASG_EVT_FCT'
5334 ,p_msg_sub_group => g_msg_sub_group
5335 ,p_sql_err_code => SQLCODE);
5336 --
5337 RAISE;
5338 --
5339 END process_range;
5340 --
5341 -- ----------------------------------------------------------------------------
5342 -- POST_PROCESS
5343 -- This procedure is dynamically invoked by the HRI Multithreading utility.
5344 -- It performs all the clean up action for assignment events collection program
5345 -- like Instate the indexes and triggers
5346 -- Enable the MV logs
5347 -- Purge the assignment events incremental events queue
5348 -- Update BIS Refresh Log
5349 -- ----------------------------------------------------------------------------
5350 --
5351 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
5352 --
5353 l_dummy1 VARCHAR2(2000);
5354 l_dummy2 VARCHAR2(2000);
5355 l_schema VARCHAR2(400);
5356 --
5357 --
5358 BEGIN
5359 --
5360 dbg('Inside post_process');
5361 --
5362 set_parameters
5363 (p_mthd_action_id => p_mthd_action_id,
5364 p_mthd_stage_code => 'POST_PROCESS');
5365 --
5366 hri_bpl_conc_log.record_process_start('HRI_MB_ASGN_EVENTS_CT');
5367 --
5368 -- Recreate indexes and gather stats for full refresh or shared HR insert
5369 --
5370 IF (g_full_refresh = 'Y' OR
5371 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
5372 --
5373 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
5374 --
5375 dbg('Full Refresh selected - Creating indexes');
5376 --
5377 HRI_UTL_DDL.recreate_indexes(
5378 p_application_short_name => 'HRI',
5379 p_table_name => 'HRI_MB_ASGN_EVENTS_CT',
5380 p_table_owner => l_schema);
5381 hri_utl_ddl.recreate_indexes(
5382 p_application_short_name => 'HRI',
5383 p_table_name => 'HRI_MDP_ORGH_TRANSFERS_CT',
5384 p_table_owner => l_schema);
5385 --
5386 dbg('Full Refresh selected - gathering stats');
5387 --
5388 fnd_stats.gather_table_stats(l_schema,'HRI_MB_ASGN_EVENTS_CT');
5389 --
5390 END IF;
5391 --
5392 ELSE
5393 --
5394 -- 4259598 Incremental Changes
5395 -- Populate the assignment event delta queue in order to incrementally refresh
5396 -- the assignment delta table
5397 --
5398 dbg('populating the assignment delta events queue...');
5399 --
5400 populate_asg_delta_eq;
5401 --
5402 END IF;
5403 --
5404 -- Enable the WHO trigger on the events fact table
5405 --
5406 dbg('Enabling the who trigger');
5407 EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MB_ASGN_EVENTS_CT_WHO ENABLE';
5408 EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MDP_ORGH_TRANSFERS_CT_WHO ENABLE';
5409 --
5410 -- Purge the Events Queue. The events queue needs to be purged
5411 -- even after the after full refresh. Recollecting incremental changes
5412 -- will be useless if a full refresh has been run.
5413 --
5414 dbg('Purging the events queue');
5415 hri_opl_event_capture.purge_queue('HRI_EQ_ASGN_EVNTS');
5416 --
5417 hri_bpl_conc_log.log_process_end(
5418 p_status => TRUE
5419 ,p_period_from => TRUNC(g_refresh_start_date)
5420 ,p_period_to => TRUNC(SYSDATE)
5421 ,p_attribute1 => g_collect_fte
5422 ,p_attribute2 => g_collect_hdc
5423 ,p_attribute3 => g_full_refresh);
5424 --
5425 dbg('Exiting post_process');
5426 --
5427 EXCEPTION
5428 --
5429 WHEN OTHERS THEN
5430 --
5431 g_msg_sub_group := NVL(g_msg_sub_group, 'POST_PROCESS');
5432 --
5433 hri_bpl_conc_log.log_process_info
5434 (p_package_name => 'HRI_OPL_ASGN_EVENTS'
5435 ,p_msg_type => 'ERROR'
5436 ,p_note => SQLERRM
5437 ,p_msg_group => 'ASG_EVT_FCT'
5438 ,p_msg_sub_group => g_msg_sub_group
5439 ,p_sql_err_code => SQLCODE);
5440 --
5441 RAISE;
5442 --
5443 END post_process;
5444 --
5445 -- -----------------------------------------------------------------------------
5446 -- Debugging procedure to run for a single assignment
5447 -- -----------------------------------------------------------------------------
5448 --
5449 PROCEDURE run_for_asg(p_assignment_id IN NUMBER) IS
5450 --
5451 l_asgn_events_tab g_asgn_events_tab_type;
5452 --
5453 BEGIN
5454 --
5455 g_refresh_start_date := g_dbi_collection_start_date;
5456 g_refresh_end_date := hr_general.end_of_time;
5457 g_full_refresh := 'Y';
5458 g_collect_fte := 'N';
5459 g_collect_hdc := 'Y';
5460 g_perf_query := hri_bpl_perf_rating.get_perf_sql;
5461 --
5462 DELETE
5463 FROM hri_mb_asgn_events_ct
5464 WHERE assignment_id = p_assignment_id;
5465 --
5466 collect(p_assignment_id => p_assignment_id,
5467 p_asgn_events_tab => l_asgn_events_tab) ;
5468 --
5469 bulk_insert_rows(l_asgn_events_tab);
5470 --
5471 COMMIT;
5472 --
5473 END run_for_asg;
5474 --
5475 -- Initialization Block
5476 --
5477 BEGIN
5478 --
5479 g_dbi_collection_start_date := bis_common_parameters.get_global_start_date;
5480 g_end_of_time := hr_general.end_of_time;
5481 --
5482 -- This profile is used for POW calculations.
5483 --
5484 g_adj_svc_profile := NVL(fnd_profile.value('HRI_POW_DT_STRT_SRC'),'STRT_DT');
5485 --
5486 -- For future usage
5487 --
5488 g_collect_perf_rating := 'Y';
5489 g_collect_prsn_typ := 'Y';
5490 --
5491 -- Not Rated records should have the performance band set to -5
5492 --
5493 g_perf_not_rated_id := hri_bpl_dimension_utilities.get_not_rated_id;
5494 --
5495 -- CAUTION : Don't change the underlying intialization. Used in dynamic SQL
5496 --
5497 g_rtn := '
5498 ';
5499 --
5500 END HRI_OPL_ASGN_EVENTS;
[Home] [Help]