[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_SUP_WRKFC_ASG
Source
1 PACKAGE BODY HRI_OPL_SUP_WRKFC_ASG AS
2 /* $Header: hrioswka.pkb 120.7 2006/02/09 06:19:01 jtitmas noship $ */
3 --
4 -- Global Multi Threading Array
5 --
6 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%ROWTYPE;
7 --
8 -- Global variables representing parameters
9 --
10 g_refresh_start_date DATE;
11 g_refresh_end_date DATE;
12 g_full_refresh VARCHAR2(5);
13 g_redo_reduction VARCHAR2(5);
14 g_worker_id NUMBER;
15 --
16 -- Global flag which determines whether debugging is turned on
17 --
18 g_debug_flag VARCHAR2(5);
19 --
20 -- Whether called from a concurrent program
21 --
22 g_concurrent_flag VARCHAR2(5);
23 --
24 -- Whether an MV Log exist on the table
25 --
26 g_mv_log_exists_flag VARCHAR2(5);
27 --
28 -- ----------------------------------------------------------------------------
29 -- Inserts row into concurrent program log
30 --
31 --
32 PROCEDURE output(p_text VARCHAR2) IS
33 BEGIN
34 --
35 IF (g_concurrent_flag = 'Y') THEN
36 --
37 -- Write to the concurrent request log
38 --
39 fnd_file.put_line(fnd_file.log, p_text);
40 --
41 ELSE
42 --
43 hr_utility.trace(p_text);
44 --
45 END IF;
46 --
47 END output;
48 --
49 -- -----------------------------------------------------------------------------
50 -- Inserts row into concurrent program log if debugging is enabled
51 -- -----------------------------------------------------------------------------
52 --
53 PROCEDURE dbg(p_text VARCHAR2) IS
54 --
55 BEGIN
56 --
57 IF (g_debug_flag = 'Y' OR g_mthd_action_array.debug_flag = 'Y') THEN
58 --
59 -- Write to output
60 --
61 output(p_text);
62 --
63 END IF;
64 --
65 END dbg;
66 --
67 -- ----------------------------------------------------------------------------
68 -- Runs given sql statement dynamically without raising an exception
69 -- ----------------------------------------------------------------------------
70 --
71 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
72 IS
73 --
74 BEGIN
75 --
76 EXECUTE IMMEDIATE p_sql_stmt;
77 --
78 EXCEPTION WHEN OTHERS THEN
79 --
80 output('Could not run the following sql:');
81 output(SUBSTR(p_sql_stmt,1,230));
82 --
83 END run_sql_stmt_noerr;
84 --
85 -- -----------------------------------------------------------------------------
86 -- This procedure manages the operations done on the materialized view logs of
87 -- the master table HRI_MAP_SUP_WRKFC_ASG
88 -- -----------------------------------------------------------------------------
89 --
90 PROCEDURE manage_mview_logs(p_schema IN VARCHAR2
91 ,p_enable_disable IN VARCHAR2) IS
92 --
93 -- Cursor to check the presence of materialized view log
94 --
95 CURSOR mvlog_exist_csr IS
96 SELECT 1
97 FROM dba_mview_logs
98 WHERE master = 'HRI_MAP_SUP_WRKFC_ASG'
99 AND log_owner = p_schema;
100 --
101 -- Variable to hold the number of materialized log existing
102 --
103 l_no_of_logs PLS_INTEGER;
104 --
105 BEGIN
106 --
107 -- Open the cursor for first time only
108 -- The value of the global flag gets set to 'Y' if materialized view log exists
109 -- Once its presence is determined, the cursor should not be opened again
110 --
111 IF g_mv_log_exists_flag IS NULL THEN
112 --
113 -- Open the cursor to check for the presence of materialized view log
114 --
115 OPEN mvlog_exist_csr;
116 FETCH mvlog_exist_csr INTO l_no_of_logs;
117 --
118 -- If materialized view log(s) is present then set the flag to Yes, else
119 -- set it to No
120 --
121 IF l_no_of_logs > 0 THEN
122 --
123 g_mv_log_exists_flag := 'Y';
124 --
125 ELSE
126 --
127 g_mv_log_exists_flag := 'N';
128 --
129 END IF;
130 --
131 CLOSE mvlog_exist_csr;
132 --
133 END IF;
134 --
135 -- If materialized view log(s) exists then process them
136 --
137 IF g_mv_log_exists_flag = 'Y' THEN
138 --
139 -- For enabling the materialized view logs
140 --
141 IF p_enable_disable = 'E' THEN
142 --
143 -- This procedure purges rows from the materialized view log.
144 --
145 dbms_mview.purge_log(master => p_schema || '.HRI_MAP_SUP_WRKFC_ASG'
146 ,num => 99999);
147 --
148 -- This procedure ensures that the materialized view data for the master
149 -- table is valid and that the master table is in the proper state. It
150 -- must be called after a master table is reorganized
151 --
152 dbms_mview.end_table_reorganization(tabowner => p_schema
153 ,tabname => 'HRI_MAP_SUP_WRKFC_ASG');
154 --
155 -- This procedure purges rows from the materialized view log.
156 --
157 dbms_mview.purge_log(master => p_schema || '.HRI_MAP_SUP_WRKFC_ASG',
158 num => 99999);
159 --
160 -- For disabling the materialized view logs
161 --
162 ELSE
163 --
164 -- This procedure performs a process to preserve materialized view data
165 -- needed for refresh. It must be called before a master table is reorganized
166 --
167 dbms_mview.begin_table_reorganization(tabowner => p_schema,
168 tabname => 'HRI_MAP_SUP_WRKFC_ASG');
169 --
170 END IF;
171 --
172 END IF;
173 --
174 EXCEPTION
175 WHEN OTHERS THEN
176 --
177 -- Close the cursor if it is open
178 --
179 IF mvlog_exist_csr%ISOPEN THEN
180 --
181 CLOSE mvlog_exist_csr;
182 --
183 END IF;
184 --
185 RAISE;
186 --
187 END manage_mview_logs;
188 --
189 -- ----------------------------------------------------------------------------
190 -- UPDATE_JOB_CHANGES
191 -- This procedure is used for incrementally refreshing the asg delta table
192 -- when the job family or job function dimesion levels are changed for a job
193 -- -------------------------------------------------------------------------
194 --
195 PROCEDURE update_job_changes(p_start_object_id IN NUMBER
196 ,p_end_object_id IN NUMBER )
197 IS
198 BEGIN
199 --
200 dbg('Inside update_job_changes');
201 --
202 -- Update all records for which the job family / function information
203 --
204 UPDATE hri_map_sup_wrkfc_asg asg_dlt
205 SET (asg_dlt.job_fmly_code , asg_dlt.job_fnctn_code) =
206 (SELECT jobh.job_fmly_code ,
207 jobh.job_fnctn_code
208 FROM hri_cs_jobh_ct jobh
209 WHERE jobh.job_id = asg_dlt.job_id)
210 WHERE (assignment_id, evts_effective_end_date) in
211 (SELECT asgn.assignment_id,
212 asgn.effective_change_end_date
213 FROM hri_mb_asgn_events_ct asgn,
214 hri_eq_asg_sup_wrfc eq
215 WHERE asgn.job_id = eq.source_id
216 AND eq.source_type = 'JOB'
217 AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
218 --
219 dbg(sql%rowcount || ' records update due to job changes');
220 --
221 COMMIT;
222 --
223 END update_job_changes;
224 --
225 -- ----------------------------------------------------------------------------
226 -- UPDATE_PRMRY_JOB_ROLE_CHANGES
227 -- This procedure is used for incrementally refreshing the asg delta table
228 -- with job roles when the job family or job function dimesion levels are
229 -- changed for a job
230 -- -------------------------------------------------------------------------
231 --
232 PROCEDURE update_prmry_job_role_changes(p_start_object_id IN NUMBER
233 ,p_end_object_id IN NUMBER )
234 IS
235 BEGIN
236 --
237 dbg('Inside update_prmry_job_role_changes');
238 --
239 -- Update all records for which the job family / function information
240 -- have changed
241 --
242 UPDATE hri_map_sup_wrkfc_asg asg_dlt
243 SET asg_dlt.primary_job_role_code =
244 (SELECT jbrl.job_role_code
245 FROM hri_cs_job_job_role_ct jbrl
246 WHERE jbrl.job_id = asg_dlt.job_id
247 AND jbrl.primary_role_for_job_flag = 'Y')
248 WHERE (assignment_id, evts_effective_end_date) IN
249 (SELECT asgn.assignment_id,
250 asgn.effective_change_end_date
251 FROM hri_mb_asgn_events_ct asgn,
252 hri_eq_asg_sup_wrfc eq
253 WHERE asgn.job_id = eq.source_id
254 AND eq.source_type = 'PRIMARY_JOB_ROLE'
255 AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
256 --
257 dbg(sql%rowcount || ' records updated due to primary job role changes');
258 --
259 COMMIT;
260 --
261 END update_prmry_job_role_changes;
262 --
263 -- ----------------------------------------------------------------------------
264 -- UPDATE_LOCATION_CHANGES
265 -- This procedure is used for incrementally refreshing the asg delta table
266 -- when the location related details are changed for a location
267 -- -------------------------------------------------------------------------
268 --
269 PROCEDURE update_location_changes(p_start_object_id IN NUMBER
270 ,p_end_object_id IN NUMBER )
271 IS
272 BEGIN
273 --
274 dbg('Inside update_location_changes');
275 --
276 -- Update all records for which the location information has changed
277 --
278 UPDATE hri_map_sup_wrkfc_asg asg_dlt
279 SET (asg_dlt.geo_area_code,asg_dlt.geo_country_code,asg_dlt.geo_region_code,asg_dlt.geo_city_cid) =
280 (SELECT geoh.area_code,
281 geoh.country_code,
282 geoh.region_code,
283 geoh.city_cid
284 FROM hri_cs_geo_lochr_ct geoh
285 WHERE geoh.location_id = asg_dlt.location_id)
286 WHERE (assignment_id, evts_effective_end_date) IN
287 (SELECT asgn.assignment_id,
288 asgn.effective_change_end_date
289 FROM hri_mb_asgn_events_ct asgn,
290 hri_eq_asg_sup_wrfc eq
291 WHERE asgn.location_id = eq.source_id
292 AND eq.source_type = 'LOCATION'
293 AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
294 --
295 dbg(sql%rowcount || ' records updated due to location changes');
296 --
297 COMMIT;
298 --
299 END update_location_changes;
300 --
301 -- ----------------------------------------------------------------------------
302 -- UPDATE_PERSON_TYPE_CHANGES
303 -- This procedure is used for incrementally refreshing the asg delta table
304 -- when the person type related details are changed
305 -- -------------------------------------------------------------------------
306 --
307 PROCEDURE update_person_type_changes(p_start_object_id IN NUMBER
308 ,p_end_object_id IN NUMBER )
309 IS
310 BEGIN
311 null;
312 --
313 dbg('Inside update_person_type_changes');
314 --
315 -- Update all records for which the person type information has changed
316 --
317 UPDATE hri_map_sup_wrkfc_asg asg_dlt
318 SET (asg_dlt.wkth_wktyp_sk_fk,asg_dlt.wkth_lvl1_sk_fk,asg_dlt.wkth_lvl2_sk_fk,asg_dlt.wkth_wktyp_code) =
319 (SELECT prsn.wkth_wktyp_sk_fk,
320 prsn.wkth_lvl1_sk_fk,
321 prsn.wkth_lvl2_sk_fk,
322 prsn.wkth_wktyp_code
323 FROM hri_cs_prsntyp_ct prsn,
324 hri_mb_asgn_events_ct asgn
325 WHERE prsn.prsntyp_sk_pk = asgn.prsntyp_sk_fk
326 AND asgn.assignment_id = asg_dlt.assignment_id
327 AND ROWNUM < 2
328 )
329 WHERE (assignment_id, evts_effective_end_date) IN
330 (SELECT asgn.assignment_id,
331 asgn.effective_change_end_date
332 FROM hri_mb_asgn_events_ct asgn,
333 hri_eq_asg_sup_wrfc eq
334 WHERE asgn.prsntyp_sk_fk = eq.source_id
335 AND eq.source_type = 'PERSON_TYPE'
336 AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
337 --
338 dbg(sql%rowcount || ' records updated due to person type changes');
339 --
340 COMMIT;
341 --
342 END update_person_type_changes;
343 --
344 -- ----------------------------------------------------------------------------
345 -- ASG_EVENT_CHANGES
346 -- This procedure is used for incrementally refreshing the asg delta table
347 -- when incremental changes happen to the asg events fact table.
348 -- The the details about the changes are stored in the asg delta event queue
349 -- -------------------------------------------------------------------------
350 --
351 PROCEDURE asg_event_changes(p_start_object_id IN NUMBER
352 ,p_end_object_id IN NUMBER )
353 IS
354 --
355 l_current_time DATE;
356 l_user_id NUMBER;
357 --
358 BEGIN
359 --
360 dbg('Inside asg_event_changes');
361 --
362 l_current_time := SYSDATE;
363 l_user_id := fnd_global.user_id;
364 --
365 -- First remove the deleted asg event records
366 --
367 DELETE hri_map_sup_wrkfc_asg asg_sph
368 WHERE asg_sph.assignment_id in
369 (select source_id
370 from hri_eq_asg_sup_wrfc evt
371 where evt.source_id between p_start_object_id and p_end_object_id
372 and evt.source_id = asg_sph.assignment_id
373 AND evt.source_type = 'ASG_EVENT')
374 AND asg_sph.evts_effective_end_date >=
375 (select evt.erlst_evnt_effective_date - 1
376 from hri_eq_asg_sup_wrfc evt
377 where evt.source_id = asg_sph.assignment_id
378 AND evt.source_type = 'ASG_EVENT');
379 --
380 dbg(sql%rowcount || ' records deleted due to assignment event changes');
381 --
382 -- NOTE : If the underlying SQL is changed, you might have to make the
383 -- similiar changes to the query in SUP_CHANGES procedure
384 --
385 INSERT INTO HRI_MAP_SUP_WRKFC_ASG (
386 --
387 -- Supervisor id's
388 --
389 supervisor_person_id
390 ,direct_supervisor_person_id
391 --
392 -- Effective Dates
393 --
394 ,effective_date
395 --
396 -- 3986188 a end date column is required which should contain the least end date
397 -- from events or supervisor hiearchy tables
398 --
399 ,effective_end_date
400 ,evts_effective_end_date
401 ,suph_effective_end_date
402 --
403 -- Period of work start date
404 --
405 ,pow_start_date
406 --
407 -- 4234485, Period of work start date in Julian days.
408 --
409 ,pow_value_days_julian
410 ,pow_extn_days_julian
411 --
412 -- Unique key generated for the events fact
413 --
414 ,event_id
415 --
416 -- Assignment related FK id's
417 --
418 ,person_id
419 ,assignment_id
420 ,location_id
421 ,job_id
422 ,organization_id
423 ,position_id
424 ,grade_id
425 --
426 -- Workforce related FK id's
427 --
428 ,wkth_wktyp_sk_fk
429 ,wkth_lvl1_sk_fk
430 ,wkth_lvl2_sk_fk
431 --
432 -- Length of work related FK id
433 --
434 ,pow_band_sk_fk
435 --
436 -- Job codes
437 --
438 ,job_fmly_code
439 ,job_fnctn_code
440 --
441 -- Priamry job role code
442 --
443 ,primary_job_role_code
444 --
445 --
446 -- Location codes
447 --
448 ,geo_area_code
449 ,geo_country_code
450 ,geo_region_code
451 ,geo_city_cid
452 --
453 -- Termination reason and category
454 --
455 ,leaving_reason_code
456 ,separation_category
457 --
458 -- Performance band
459 --
460 ,perf_band
461 --
462 -- Workforce type code
463 --
464 ,wkth_wktyp_code
465 --
466 -- Salary currency and value
467 --
468 ,anl_slry_currency
469 ,anl_slry_value
470 --
471 -- Headcount and FTE value
472 --
473 ,headcount_value
474 ,fte_value
475 --
476 -- Indicators
477 --
478 ,worker_hire_ind
479 ,post_hire_asgn_start_ind
480 ,worker_term_ind
481 ,term_voluntary_ind
482 ,term_involuntary_ind
483 ,pre_sprtn_asgn_end_ind
484 ,transfer_in_ind
485 ,transfer_out_ind
486 --
487 ,direct_ind
488 ,primary_flag_ind
489 ,primary_asg_with_hdc_ind
490 --
491 -- Indicators to decide summarization requirements
492 --
493 ,summarization_rqd_ind
494 ,summarization_rqd_chng_ind
495 --
496 -- Indicates gain and loss events
497 --
498 ,metric_adjust_multiplier
499 --
500 -- Relative supervisor level
501 --
502 ,supervisor_level
503 --
504 -- Admin columns
505 --
506 ,admin_row_type
507 ,admin_evts_rowid
508 ,admin_suph_rowid
509 ,admin_jobh_rowid
510 ,admin_geoh_rowid
511 --
512 -- WHO Columns
513 --
514 ,last_update_date
515 ,last_update_login
516 ,last_updated_by
517 ,created_by
518 ,creation_date
519 --
520 -- Incremental changes
521 --
522 ,sub_assignment_id)
523 SELECT /*+ ORDERED */
524 suph.sup_person_id supervisor_person_id
525 ,evts.supervisor_id direct_supervisor_person_id
526 ,GREATEST(evts.effective_change_date,
527 suph.effective_start_date) effective_date
528 --
529 -- 3986188 a end date column is required which should contain the least end date
530 -- from events or supervisor hiearchy tables
531 --
532 ,LEAST(evts.effective_change_end_date,
533 suph.effective_end_date ) effective_end_date
534 ,evts.effective_change_end_date evts_effective_end_date
535 ,suph.effective_end_date suph_effective_end_date
536 ,evts.pow_start_date_adj pow_start_date
537 ,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
538 pow_value_days_julian
539 ,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
540 pow_extn_days_julian
541 ,evts.event_id event_id
542 ,evts.person_id person_id
543 ,evts.assignment_id assignment_id
544 ,evts.location_id location_id
545 ,evts.job_id job_id
546 ,evts.organization_id organization_id
547 ,evts.position_id position_id
548 ,evts.grade_id grade_id
549 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
550 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
551 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
552 ,evts.pow_band_sk_fk pow_band_sk_fk
553 ,jobh.job_fmly_code job_fmly_code
554 ,jobh.job_fnctn_code job_fnctn_code
555 --
556 -- Assign job role only for primary job roles
557 --
558 ,CASE
559 WHEN rolj.primary_role_for_job_flag = 'Y' THEN
560 rolj.job_role_code
561 ELSE
562 'NA_EDW'
563 END primary_job_role_code
564 --
565 ,geoh.area_code geo_area_code
566 ,geoh.country_code geo_country_code
567 ,geoh.region_code geo_region_code
568 ,geoh.city_cid geo_city_cid
569 ,evts.leaving_reason_code leaving_reason_code
570 ,'NA_EDW' separation_category
571 ,evts.perf_band perf_band
572 ,prsn.wkth_wktyp_code wkth_wktyp_code
573 ,evts.anl_slry_currency anl_slry_currency
574 --
575 -- Set salary, headcount and fte to 0 when summarization is not
576 -- required
577 --
578 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
579 ,evts.headcount * evts.summarization_rqd_ind headcount_value
580 ,evts.fte * evts.summarization_rqd_ind fte_value
581 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
582 THEN 0
583 ELSE evts.worker_hire_ind
584 END worker_hire_ind
585 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
586 THEN 0
587 ELSE evts.post_hire_asgn_start_ind
588 END post_hire_asgn_start_ind
589 ,0 worker_term_ind
590 ,0 term_voluntary_ind
591 ,0 term_involuntary_ind
592 ,0 pre_sprtn_asgn_end_ind
593 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
594 THEN 1
595 WHEN evts.effective_change_date > suph.effective_start_date
596 THEN evts.supervisor_change_ind
597 ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
598 END transfer_in_ind
599 ,0 transfer_out_ind
600 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
601 --
602 -- 4013742
603 -- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
604 -- when summarization is not required
605 --
606 ,CASE WHEN evts.primary_flag = 'Y'
607 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
608 primary_flag_ind
609 ,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
610 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
611 primary_asg_with_hdc_ind
612 ,evts.summarization_rqd_ind summarization_rqd_ind
613 ,CASE
614 --
615 -- Only set for assignment change events
616 --
617 WHEN evts.effective_change_date >= suph.effective_start_date THEN
618 evts.summarization_rqd_chng_ind
619 --
620 -- For supervisor change events, set as 0
621 --
622 ELSE
623 0
624 END summarization_rqd_chng_ind
625 ,1 metric_adjust_multiplier
626 ,suph.sup_level supervisor_level
627 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
628 THEN 'GAIN SUP EVENT ONLY'
629 WHEN evts.effective_change_date > suph.effective_start_date
630 THEN 'GAIN ASG EVENT ONLY'
631 ELSE 'GAIN ASG SUP EVENT'
632 END admin_row_type
633 ,evts.rowid admin_evts_rowid
634 ,suph.rowid admin_suph_rowid
635 ,jobh.rowid admin_jobh_rowid
636 ,geoh.rowid admin_geoh_rowid
637 --
638 -- WHO Columns
639 --
640 , SYSDATE
641 ,l_user_id
642 ,l_user_id
643 ,l_user_id
644 ,SYSDATE
645 --
646 -- Incremental Changes
647 --
648 ,sub_assignment_id sub_assignment_id
649 FROM
650 hri_eq_asg_sup_wrfc eq
651 ,hri_mb_asgn_events_ct evts
652 ,hri_cs_jobh_ct jobh
653 ,hri_cs_geo_lochr_ct geoh
654 ,hri_cs_prsntyp_ct prsn
655 ,hri_cs_job_job_role_ct rolj
656 ,hri_cs_suph suph
657 WHERE suph.sub_person_id = evts.supervisor_id
658 AND suph.sup_invalid_flag_code = 'N'
659 AND (evts.effective_change_date BETWEEN suph.effective_start_date AND suph.effective_end_date
660 OR suph.effective_start_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date)
661 AND evts.pre_sprtn_asgn_end_ind = 0
662 AND evts.worker_term_ind = 0
663 AND geoh.location_id = evts.location_id
664 AND jobh.job_id = evts.job_id
665 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
666 AND evts.job_id = rolj.job_id
667 AND eq.source_id between p_start_object_id and p_end_object_id
668 AND eq.source_type = 'ASG_EVENT'
669 AND eq.source_id = evts.assignment_id
670 AND eq.erlst_evnt_effective_date -1 <= evts.effective_change_end_date
671 UNION ALL
672 SELECT /*+ ORDERED */
673 suph.sup_person_id supervisor_person_id
674 ,evts.supervisor_id direct_supervisor_person_id
675 ,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
676 effective_date
677 --
678 -- 3986188 a end date column is required which should contain the least end date
679 -- from events or supervisor hiearchy tables
680 --
681 ,null effective_end_date
682 ,evts.effective_change_end_date evts_effective_end_date
683 ,suph.effective_end_date suph_effective_end_date
684 ,evts.pow_start_date_adj pow_start_date
685 ,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
686 pow_value_days_julian
687 ,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
688 pow_extn_days_julian
689 ,evts.event_id event_id
690 ,evts.person_id person_id
691 ,evts.assignment_id assignment_id
692 ,evts.location_id location_id
693 ,evts.job_id job_id
694 ,evts.organization_id organization_id
695 ,evts.position_id position_id
696 ,evts.grade_id grade_id
697 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
698 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
699 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
700 ,evts.pow_band_sk_fk pow_band_sk_fk
701 ,jobh.job_fmly_code job_fmly_code
702 ,jobh.job_fnctn_code job_fnctn_code
703 ,CASE
704 WHEN rolj.primary_role_for_job_flag = 'Y' THEN
705 rolj.job_role_code
706 ELSE
707 'NA_EDW'
708 END primary_job_role_code
709 --
710 ,geoh.area_code geo_area_code
711 ,geoh.country_code geo_country_code
712 ,geoh.region_code geo_region_code
713 ,geoh.city_cid geo_city_cid
714 ,evts.leaving_reason_code leaving_reason_code
715 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
716 THEN 'NA_EDW'
717 ELSE evts.separation_category_nxt
718 END separation_category
719 ,evts.perf_band perf_band
720 ,prsn.wkth_wktyp_code wkth_wktyp_code
721 ,evts.anl_slry_currency anl_slry_currency
722 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
723 ,evts.headcount * evts.summarization_rqd_ind headcount_value
724 ,evts.fte * evts.summarization_rqd_ind fte_value
725 ,0 worker_hire_ind
726 ,0 post_hire_asgn_start_ind
727 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
728 THEN 0
729 ELSE evts.worker_term_nxt_ind
730 END worker_term_ind
731 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
732 THEN 0
733 ELSE evts.term_voluntary_nxt_ind
734 END term_voluntary_ind
735 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
736 THEN 0
737 ELSE evts.term_involuntary_nxt_ind
738 END term_involuntary_ind
739 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
740 THEN 0
741 ELSE evts.pre_sprtn_asgn_end_nxt_ind
742 END pre_sprtn_asgn_end_ind
743 ,0 transfer_in_ind
744 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
745 THEN 1
746 WHEN suph.effective_end_date > evts.effective_change_end_date
747 THEN evts.supervisor_change_nxt_ind
748 ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
749 END transfer_out_ind
750 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
751 --
752 -- 4013742
753 -- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
754 -- when summarization is not required
755 --
756 ,CASE WHEN evts.primary_flag = 'Y'
757 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
758 primary_flag_ind
759 ,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
760 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
761 primary_asg_with_hdc_ind
762 ,evts.summarization_rqd_ind summarization_rqd_ind
763 ,CASE
764 WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
765 evts.summarization_rqd_chng_nxt_ind
766 ELSE
767 0
768 END summarization_rqd_chng_ind
769 ,-1 metric_adjust_multiplier
770 ,suph.sup_level supervisor_level
771 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
772 THEN 'LOSS SUP EVENT ONLY'
773 WHEN suph.effective_end_date > evts.effective_change_end_date
774 THEN 'LOSS ASG EVENT ONLY'
775 ELSE 'LOSS ASG SUP EVENT'
776 END admin_row_type
777 ,evts.rowid admin_evts_rowid
778 ,suph.rowid admin_suph_rowid
779 ,jobh.rowid admin_jobh_rowid
780 ,geoh.rowid admin_geoh_rowid
781 --
782 -- WHO Columns
783 --
784 ,SYSDATE
785 ,l_user_id
786 ,l_user_id
787 ,l_user_id
788 ,SYSDATE
789 --
790 -- Incremental Changes
791 --
792 ,sub_assignment_id sub_assignment_id
793 FROM
794 hri_eq_asg_sup_wrfc eq
795 ,hri_mb_asgn_events_ct evts
796 ,hri_cs_jobh_ct jobh
797 ,hri_cs_geo_lochr_ct geoh
798 ,hri_cs_prsntyp_ct prsn
799 ,hri_cs_job_job_role_ct rolj
800 ,hri_cs_suph suph
801 WHERE suph.sub_person_id = evts.supervisor_id
802 AND suph.sup_invalid_flag_code = 'N'
803 AND (suph.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
804 OR evts.effective_change_end_date BETWEEN suph.effective_start_date AND suph.effective_end_date)
805 AND LEAST(suph.effective_end_date, evts.effective_change_end_date) < to_date('31-12-4712','DD-MM-YYYY')
806 AND evts.pre_sprtn_asgn_end_ind = 0
807 AND evts.worker_term_ind = 0
808 AND geoh.location_id = evts.location_id
809 AND jobh.job_id = evts.job_id
810 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
811 AND evts.job_id = rolj.job_id
812 AND eq.source_id between p_start_object_id and p_end_object_id
813 AND eq.source_id = evts.assignment_id
814 AND eq.source_type = 'ASG_EVENT'
815 AND eq.erlst_evnt_effective_date -1 <= evts.effective_change_end_date;
816 --
817 dbg(SQL%ROWCOUNT||' records inserted for asg events changes');
818 --
819 COMMIT;
820 --
821 dbg('Exiting asg_event_changes');
822 --
823 END asg_event_changes;
824 --
825 -- ----------------------------------------------------------------------------
826 -- SUP_CHANGES
827 -- This procedure is used for incrementally refreshing the asg delta table
828 -- when incremental changes happen to the sup hierarchy table.
829 -- The details about the changes are stored in the asg delta event queue
830 -- -------------------------------------------------------------------------
831 --
832 PROCEDURE sup_changes(p_start_object_id IN NUMBER
833 ,p_end_object_id IN NUMBER )
834 IS
835 --
836 l_current_time DATE;
837 l_user_id NUMBER;
838 --
839 BEGIN
840 --
841 dbg('Inside sup_changes');
842 --
843 l_current_time := SYSDATE;
844 l_user_id := fnd_global.user_id;
845 --
846 -- SUPERVISOR HIERARCHY CHANGES
847 -- Delete all records from asg delta table which have been removed from the supervisor
848 -- hierarchy table during incremental refresh. The variour metrics
849 -- stored in asg delta are derived by joining the supervisor_id of asg event record
850 -- with the sub_assignment_id of supervisor. The assignment event delta table stores
851 -- the list of assignment_id for which the supervisor hierarchy records have been
852 -- changed. The impacted asg delta records can be derived using the SUB_ASSIGNMENT_ID
853 -- and SUPH_EFFECTIVE_END_DATE column in the table. However, there is no poin in
854 -- deleting all records for the assignment, only records that have SUPH_EFFECTIVE_END_DATE
855 -- less than (earliest event date stored - 1) (This is because the previous records in the
856 -- hierarchy have also been end dated so those records cannot be ignored)
857 --
858 DELETE hri_map_sup_wrkfc_asg asg_sph
859 WHERE asg_sph.sub_assignment_id in
860 (SELECT evt.source_id
861 FROM hri_eq_asg_sup_wrfc evt
862 WHERE evt.source_id between p_start_object_id and p_end_object_id
863 AND evt.source_id = asg_sph.sub_assignment_id
864 AND evt.source_type = 'SUPERVISOR')
865 AND asg_sph.suph_effective_end_date >=
866 (SELECT evt.erlst_evnt_effective_date - 1
867 FROM hri_eq_asg_sup_wrfc evt
868 WHERE evt.source_id = asg_sph.sub_assignment_id
869 AND evt.source_type = 'SUPERVISOR');
870 --
871 dbg(sql%rowcount || ' records deleted due to sup eq');
872 --
873 -- Insert all the records for the sub_assignment_id that are
874 --
875 -- NOTE : If the underlying SQL is changed, you might have to make the
876 -- similiar changes to the query in ASG_EVENT_CHANGES procedure
877 --
878 INSERT INTO HRI_MAP_SUP_WRKFC_ASG (
879 --
880 -- Supervisor id's
881 --
882 supervisor_person_id
883 ,direct_supervisor_person_id
884 --
885 -- Effective Dates
886 --
887 ,effective_date
888 --
889 -- 3986188 a end date column is required which should contain the least end date
890 -- from events or supervisor hiearchy tables
891 --
892 ,effective_end_date
893 ,evts_effective_end_date
894 ,suph_effective_end_date
895 --
896 -- Period of work start date
897 --
898 ,pow_start_date
899 --
900 -- 4234485, Period of work start date in Julian days.
901 --
902 ,pow_value_days_julian
903 ,pow_extn_days_julian
904 --
905 -- Unique key generated for the events fact
906 --
907 ,event_id
908 --
909 -- Assignment related FK id's
910 --
911 ,person_id
912 ,assignment_id
913 ,location_id
914 ,job_id
915 ,organization_id
916 ,position_id
917 ,grade_id
918 --
919 -- Workforce related FK id's
920 --
921 ,wkth_wktyp_sk_fk
922 ,wkth_lvl1_sk_fk
923 ,wkth_lvl2_sk_fk
924 --
925 -- Length of work related FK id
926 --
927 ,pow_band_sk_fk
928 --
929 -- Job codes
930 --
931 ,job_fmly_code
932 ,job_fnctn_code
933 --
934 -- Priamry job role code
935 --
936 ,primary_job_role_code
937 --
938 --
939 -- Location codes
940 --
941 ,geo_area_code
942 ,geo_country_code
943 ,geo_region_code
944 ,geo_city_cid
945 --
946 -- Termination reason and category
947 --
948 ,leaving_reason_code
949 ,separation_category
950 --
951 -- Performance band
952 --
953 ,perf_band
954 --
955 -- Workforce type code
956 --
957 ,wkth_wktyp_code
958 --
959 -- Salary currency and value
960 --
961 ,anl_slry_currency
962 ,anl_slry_value
963 --
964 -- Headcount and FTE value
965 --
966 ,headcount_value
967 ,fte_value
968 --
969 -- Indicators
970 --
971 ,worker_hire_ind
972 ,post_hire_asgn_start_ind
973 ,worker_term_ind
974 ,term_voluntary_ind
975 ,term_involuntary_ind
976 ,pre_sprtn_asgn_end_ind
977 ,transfer_in_ind
978 ,transfer_out_ind
979 --
980 ,direct_ind
981 ,primary_flag_ind
982 ,primary_asg_with_hdc_ind
983 --
984 -- Indicators to decide summarization requirements
985 --
986 ,summarization_rqd_ind
987 ,summarization_rqd_chng_ind
988 --
989 -- Indicates gain and loss events
990 --
991 ,metric_adjust_multiplier
992 --
993 -- Relative supervisor level
994 --
995 ,supervisor_level
996 --
997 -- Admin columns
998 --
999 ,admin_row_type
1000 ,admin_evts_rowid
1001 ,admin_suph_rowid
1002 ,admin_jobh_rowid
1003 ,admin_geoh_rowid
1004 --
1005 -- WHO Columns
1006 --
1007 ,last_update_date
1008 ,last_update_login
1009 ,last_updated_by
1010 ,created_by
1011 ,creation_date
1012 --
1013 -- Incremental changes
1014 --
1015 ,sub_assignment_id)
1016 SELECT
1017 suph.sup_person_id supervisor_person_id
1018 ,evts.supervisor_id direct_supervisor_person_id
1019 ,GREATEST(evts.effective_change_date,
1020 suph.effective_start_date) effective_date
1021 --
1022 -- 3986188 a end date column is required which should contain the least end date
1023 -- from events or supervisor hiearchy tables
1024 --
1025 ,LEAST(evts.effective_change_end_date,
1026 suph.effective_end_date ) effective_end_date
1027 ,evts.effective_change_end_date evts_effective_end_date
1028 ,suph.effective_end_date suph_effective_end_date
1029 ,evts.pow_start_date_adj pow_start_date
1030 ,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
1031 pow_value_days_julian
1032 ,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
1033 pow_extn_days_julian
1034 ,evts.event_id event_id
1035 ,evts.person_id person_id
1036 ,evts.assignment_id assignment_id
1037 ,evts.location_id location_id
1038 ,evts.job_id job_id
1039 ,evts.organization_id organization_id
1040 ,evts.position_id position_id
1041 ,evts.grade_id grade_id
1042 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
1043 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
1044 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
1045 ,evts.pow_band_sk_fk pow_band_sk_fk
1046 ,jobh.job_fmly_code job_fmly_code
1047 ,jobh.job_fnctn_code job_fnctn_code
1048 --
1049 -- Assign job role only for primary job roles
1050 --
1051 ,CASE
1052 WHEN rolj.primary_role_for_job_flag = 'Y' THEN
1053 rolj.job_role_code
1054 ELSE
1055 'NA_EDW'
1056 END primary_job_role_code
1057 --
1058 ,geoh.area_code geo_area_code
1059 ,geoh.country_code geo_country_code
1060 ,geoh.region_code geo_region_code
1061 ,geoh.city_cid geo_city_cid
1062 ,evts.leaving_reason_code leaving_reason_code
1063 ,'NA_EDW' separation_category
1064 ,evts.perf_band perf_band
1065 ,prsn.wkth_wktyp_code wkth_wktyp_code
1066 ,evts.anl_slry_currency anl_slry_currency
1067 --
1068 -- Set salary, headcount and fte to 0 when summarization is not
1069 -- required
1070 --
1071 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
1072 ,evts.headcount * evts.summarization_rqd_ind headcount_value
1073 ,evts.fte * evts.summarization_rqd_ind fte_value
1074 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1075 THEN 0
1076 ELSE evts.worker_hire_ind
1077 END worker_hire_ind
1078 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1079 THEN 0
1080 ELSE evts.post_hire_asgn_start_ind
1081 END post_hire_asgn_start_ind
1082 ,0 worker_term_ind
1083 ,0 term_voluntary_ind
1084 ,0 term_involuntary_ind
1085 ,0 pre_sprtn_asgn_end_ind
1086 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1087 THEN 1
1088 WHEN evts.effective_change_date > suph.effective_start_date
1089 THEN evts.supervisor_change_ind
1090 ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
1091 END transfer_in_ind
1092 ,0 transfer_out_ind
1093 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
1094 --
1095 -- 4013742
1096 -- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
1097 -- when summarization is not required
1098 --
1099 ,CASE WHEN evts.primary_flag = 'Y'
1100 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1101 primary_flag_ind
1102 ,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
1103 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1104 primary_asg_with_hdc_ind
1105 ,evts.summarization_rqd_ind summarization_rqd_ind
1106 ,CASE
1107 --
1108 -- Only set for assignment change events
1109 --
1110 WHEN evts.effective_change_date >= suph.effective_start_date THEN
1111 evts.summarization_rqd_chng_ind
1112 --
1113 -- For supervisor change events, set as 0
1114 --
1115 ELSE
1116 0
1117 END summarization_rqd_chng_ind
1118 ,1 metric_adjust_multiplier
1119 ,suph.sup_level supervisor_level
1120 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1121 THEN 'GAIN SUP EVENT ONLY'
1122 WHEN evts.effective_change_date > suph.effective_start_date
1123 THEN 'GAIN ASG EVENT ONLY'
1124 ELSE 'GAIN ASG SUP EVENT'
1125 END admin_row_type
1126 ,evts.rowid admin_evts_rowid
1127 ,suph.rowid admin_suph_rowid
1128 ,jobh.rowid admin_jobh_rowid
1129 ,geoh.rowid admin_geoh_rowid
1130 --
1131 -- WHO Columns
1132 --
1133 , SYSDATE
1134 ,l_user_id
1135 ,l_user_id
1136 ,l_user_id
1137 ,SYSDATE
1138 --
1139 -- Incremental Changes
1140 --
1141 ,sub_assignment_id sub_assignment_id
1142 FROM
1143 hri_mb_asgn_events_ct evts
1144 ,hri_cs_jobh_ct jobh
1145 ,hri_cs_geo_lochr_ct geoh
1146 ,hri_cs_suph suph
1147 ,hri_cs_prsntyp_ct prsn
1148 ,hri_cs_job_job_role_ct rolj
1149 ,hri_eq_asg_sup_wrfc eq
1150 WHERE suph.sub_person_id = evts.supervisor_id
1151 AND suph.sup_invalid_flag_code = 'N'
1152 AND (evts.effective_change_date BETWEEN suph.effective_start_date AND suph.effective_end_date
1153 OR suph.effective_start_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date)
1154 AND evts.pre_sprtn_asgn_end_ind = 0
1155 AND evts.worker_term_ind = 0
1156 AND geoh.location_id = evts.location_id
1157 AND jobh.job_id = evts.job_id
1158 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
1159 AND evts.job_id = rolj.job_id
1160 AND eq.source_id between p_start_object_id and p_end_object_id
1161 AND eq.source_type = 'SUPERVISOR'
1162 AND eq.source_id = suph.sub_assignment_id
1163 AND eq.erlst_evnt_effective_date - 1 <= suph.effective_end_date
1164 UNION ALL
1165 SELECT
1166 suph.sup_person_id supervisor_person_id
1167 ,evts.supervisor_id direct_supervisor_person_id
1168 ,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
1169 effective_date
1170 --
1171 -- 3986188 a end date column is required which should contain the least end date
1172 -- from events or supervisor hiearchy tables
1173 --
1174 ,null effective_end_date
1175 ,evts.effective_change_end_date evts_effective_end_date
1176 ,suph.effective_end_date suph_effective_end_date
1177 ,evts.pow_start_date_adj pow_start_date
1178 ,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
1179 pow_value_days_julian
1180 ,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
1181 pow_extn_days_julian
1182 ,evts.event_id event_id
1183 ,evts.person_id person_id
1184 ,evts.assignment_id assignment_id
1185 ,evts.location_id location_id
1186 ,evts.job_id job_id
1187 ,evts.organization_id organization_id
1188 ,evts.position_id position_id
1189 ,evts.grade_id grade_id
1190 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
1191 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
1192 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
1193 ,evts.pow_band_sk_fk pow_band_sk_fk
1194 ,jobh.job_fmly_code job_fmly_code
1195 ,jobh.job_fnctn_code job_fnctn_code
1196 ,CASE
1197 WHEN rolj.primary_role_for_job_flag = 'Y' THEN
1198 rolj.job_role_code
1199 ELSE
1200 'NA_EDW'
1201 END primary_job_role_code
1202 --
1203 ,geoh.area_code geo_area_code
1204 ,geoh.country_code geo_country_code
1205 ,geoh.region_code geo_region_code
1206 ,geoh.city_cid geo_city_cid
1207 ,evts.leaving_reason_code leaving_reason_code
1208 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1209 THEN 'NA_EDW'
1210 ELSE evts.separation_category_nxt
1211 END separation_category
1212 ,evts.perf_band perf_band
1213 ,prsn.wkth_wktyp_code wkth_wktyp_code
1214 ,evts.anl_slry_currency anl_slry_currency
1215 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
1216 ,evts.headcount * evts.summarization_rqd_ind headcount_value
1217 ,evts.fte * evts.summarization_rqd_ind fte_value
1218 ,0 worker_hire_ind
1219 ,0 post_hire_asgn_start_ind
1220 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1221 THEN 0
1222 ELSE evts.worker_term_nxt_ind
1223 END worker_term_ind
1224 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1225 THEN 0
1226 ELSE evts.term_voluntary_nxt_ind
1227 END term_voluntary_ind
1228 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1229 THEN 0
1230 ELSE evts.term_involuntary_nxt_ind
1231 END term_involuntary_ind
1232 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1233 THEN 0
1234 ELSE evts.pre_sprtn_asgn_end_nxt_ind
1235 END pre_sprtn_asgn_end_ind
1236 ,0 transfer_in_ind
1237 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1238 THEN 1
1239 WHEN suph.effective_end_date > evts.effective_change_end_date
1240 THEN evts.supervisor_change_nxt_ind
1241 ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
1242 END transfer_out_ind
1243 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
1244 --
1245 -- 4013742
1246 -- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
1247 -- when summarization is not required
1248 --
1249 ,CASE WHEN evts.primary_flag = 'Y'
1250 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1251 primary_flag_ind
1252 ,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
1253 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1254 primary_asg_with_hdc_ind
1255 ,evts.summarization_rqd_ind summarization_rqd_ind
1256 ,CASE
1257 WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
1258 evts.summarization_rqd_chng_nxt_ind
1259 ELSE
1260 0
1261 END summarization_rqd_chng_ind
1262 ,-1 metric_adjust_multiplier
1263 ,suph.sup_level supervisor_level
1264 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1265 THEN 'LOSS SUP EVENT ONLY'
1266 WHEN suph.effective_end_date > evts.effective_change_end_date
1267 THEN 'LOSS ASG EVENT ONLY'
1268 ELSE 'LOSS ASG SUP EVENT'
1269 END admin_row_type
1270 ,evts.rowid admin_evts_rowid
1271 ,suph.rowid admin_suph_rowid
1272 ,jobh.rowid admin_jobh_rowid
1273 ,geoh.rowid admin_geoh_rowid
1274 --
1275 -- WHO Columns
1276 --
1277 ,SYSDATE
1278 ,l_user_id
1279 ,l_user_id
1280 ,l_user_id
1281 ,SYSDATE
1282 --
1283 -- Incremental Changes
1284 --
1285 ,sub_assignment_id sub_assignment_id
1286 FROM
1287 hri_mb_asgn_events_ct evts
1288 ,hri_cs_jobh_ct jobh
1289 ,hri_cs_geo_lochr_ct geoh
1290 ,hri_cs_suph suph
1291 ,hri_cs_prsntyp_ct prsn
1292 ,hri_cs_job_job_role_ct rolj
1293 ,hri_eq_asg_sup_wrfc eq
1294 WHERE suph.sub_person_id = evts.supervisor_id
1295 AND suph.sup_invalid_flag_code = 'N'
1296 AND (suph.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
1297 OR evts.effective_change_end_date BETWEEN suph.effective_start_date AND suph.effective_end_date)
1298 AND LEAST(suph.effective_end_date, evts.effective_change_end_date) < to_date('31-12-4712','DD-MM-YYYY')
1299 AND evts.pre_sprtn_asgn_end_ind = 0
1300 AND evts.worker_term_ind = 0
1301 AND geoh.location_id = evts.location_id
1302 AND jobh.job_id = evts.job_id
1303 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
1304 AND evts.job_id = rolj.job_id
1305 AND eq.source_id between p_start_object_id and p_end_object_id
1306 AND eq.source_type = 'SUPERVISOR'
1307 AND eq.source_id = suph.sub_assignment_id
1308 AND eq.erlst_evnt_effective_date -1 <= suph.effective_end_date;
1309 --
1310 dbg(SQL%ROWCOUNT||' records inserted into HRI_MAP_SUP_WRKFC_ASG due to sup eq');
1311 --
1312 COMMIT;
1313 --
1314 dbg('Exiting sup_changes');
1315 --
1316 END sup_changes;
1317 --
1318 -- ----------------------------------------------------------------------------
1319 -- Sets up global list of parameters
1320 -- ----------------------------------------------------------------------------
1321 --
1322 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER
1323 ,p_mthd_stage_code IN VARCHAR2) IS
1324
1325 l_dbi_collection_start_date DATE;
1326
1327 BEGIN
1328
1329 -- Called from test harness
1330 IF p_mthd_action_id IS NULL THEN
1331 g_refresh_start_date := bis_common_parameters.get_global_start_date;
1332 g_refresh_end_date := hr_general.end_of_time;
1333 g_full_refresh := 'Y';
1334 g_concurrent_flag := 'Y';
1335 g_debug_flag := 'Y';
1336 g_redo_reduction := NVL(fnd_profile.value('HRI_ENBL_REDO_REDUCTION'),'N');
1337 g_worker_id := 1;
1338
1339 -- If parameters haven't already been set, then set them
1340 ELSIF (g_refresh_start_date IS NULL) THEN
1341
1342 l_dbi_collection_start_date := hri_oltp_conc_param.get_date_parameter_value
1343 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
1344 p_process_table_name => 'HRI_MAP_SUP_WRKFC_ASG');
1345
1346 -- If called for the first time set the defaulted parameters
1347 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
1348
1349 g_full_refresh := hri_oltp_conc_param.get_parameter_value
1350 (p_parameter_name => 'FULL_REFRESH',
1351 p_process_table_name => 'HRI_MAP_SUP_WRKFC_ASG');
1352
1353 -- Log defaulted parameters so the slave processes pick up
1354 hri_opl_multi_thread.update_parameters
1355 (p_mthd_action_id => p_mthd_action_id,
1356 p_full_refresh => g_full_refresh,
1357 p_global_start_date => l_dbi_collection_start_date);
1358
1359 END IF;
1360 --
1361 -- Populate the multithreading action array to populate the global parameters
1362 --
1363 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
1364 --
1365 g_refresh_start_date := g_mthd_action_array.collect_from_date;
1366 g_refresh_end_date := hr_general.end_of_time;
1367 g_full_refresh := g_mthd_action_array.full_refresh_flag;
1368 g_concurrent_flag := 'Y';
1369 g_debug_flag := g_mthd_action_array.debug_flag;
1370 g_redo_reduction := NVL(fnd_profile.value('HRI_ENBL_REDO_REDUCTION'),'N');
1371 g_worker_id := hri_opl_multi_thread.get_worker_id;
1372 --
1373 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
1374 hri_bpl_conc_log.dbg('Collect from: ' || to_char(g_refresh_start_date));
1375 --
1376 END IF;
1377 --
1378 END set_parameters;
1379 --
1380 -- -----------------------------------------------------------------------------
1381 -- This procedure incrementally refreshes the assignment delta table. The procedure
1382 -- is invoked by the process_range procedure which passes the start and end
1383 -- object id of the multithreading range. The logic followed in the procedure is
1384 --
1385 -- There are four tables based on which the assignment delta is populated
1386 -- Incremental refresh of each of these tables affect the assignment delta table
1387 -- in a different manner
1388 --
1389 -- 1. Asg Events: All person related metrics are derived from this table.
1390 -- During incremental refresh of asg events, all records for the asg that are
1391 -- after the event date are deleted and new records are inserted. To refresh the asg
1392 -- delta table incrementally all records for the asg on or after the event date
1393 -- should be deleted and new records for the asg should be inserted. The list of
1394 -- changed assignment with the earliest event date are populated in the asg delta
1395 -- event queue (populated by asg event collection program)
1396 --
1397 -- 2. Supervisor Hierarchy: This table is used to rollup the asg event fact data with
1398 -- the asg record and to derive the various metrics for the supervisor. The incre
1399 -- sup hierarchy program deletes all records for the person on the event date and
1400 -- reinsert the hierarchy for the him. For affecting these changes during incr
1401 -- refresh of asg delta, all record for the affected supervisor should re-calculated
1402 --
1403 -- 3. Job Hierarchy: If the Job function and Job Family details of a job record are changed
1404 -- all record for the job_id should be update with the changes. The list of
1405 -- changed jobs are populated in the asg delta event queue (populated by job
1406 -- collection program)
1407 --
1408 -- 4. Georgraphy: Currently only the country information is used in HRI reports.
1409 -- The country details of a location record cannot be changed. Therefore there
1410 -- is no impact of incremental changes to geography details on assignment delta
1411 --
1412 -- The Assignment Delta Event Queue (HRI_EQ_ASG_SUP_WRFC) stores the following
1413 -- information
1414 --
1415 -- SOURCE_TYPE = JOB, SUPERVISOR, ASG_EVENT (Depending on the change)
1416 -- SOURCE_ID = Primary Key of the changed entity
1417 -- When SOURCE_TYPE = JOB then JOB_ID
1418 -- When SOURCE_TYPE = SUPERVISOR then SUB_ASSIGNMENT_ID
1419 -- ERLST_EVNT_EFFECTIVE_DATE = Stores the earliest event date for the entity. It is
1420 -- Null for when SOURCE_TYPE = JOB
1421 -- -----------------------------------------------------------------------------
1422 --
1423 PROCEDURE incremental_process(p_start_object_id IN NUMBER
1424 ,p_end_object_id IN NUMBER )
1425 IS
1426 --
1427 --
1428 BEGIN
1429 --
1430 -- perform the incremental changes due to changes to job
1431 -- family and job function dimension level
1432 --
1433 update_job_changes(p_start_object_id => p_start_object_id
1434 ,p_end_object_id => p_end_object_id);
1435 --
1436 --
1437 -- perform the incremental changes due to changes to job
1438 -- family and job function dimension level for primary job
1439 -- roles
1440 --
1441 update_prmry_job_role_changes(p_start_object_id => p_start_object_id
1442 ,p_end_object_id => p_end_object_id);
1443 --
1444 --
1445 -- perform the incremental changes due to changes to location
1446 -- details
1447 --
1448 update_location_changes(p_start_object_id => p_start_object_id
1449 ,p_end_object_id => p_end_object_id);
1450 --
1451 -- perform the incremental changes due to changes in person type
1452 -- details
1453 update_person_type_changes(p_start_object_id => p_start_object_id
1454 ,p_end_object_id => p_end_object_id);
1455
1456 --
1457 -- perform the incremental changes due to changes to
1458 -- assignment events fact table
1459 --
1460 asg_event_changes(p_start_object_id => p_start_object_id
1461 ,p_end_object_id => p_end_object_id);
1462 --
1463 -- perform the incremental changes due to changes to
1464 -- supervisor hierarchy table
1465 --
1466 sup_changes(p_start_object_id => p_start_object_id
1467 ,p_end_object_id => p_end_object_id);
1468 --
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 --
1472 output(sqlerrm);
1473 --
1474 RAISE;
1475 --
1476 --
1477 END incremental_process;
1478 --
1479 -- -----------------------------------------------------------------------------
1480 -- This procedure inserts data in the table for every range which is being
1481 -- processed.
1482 -- -----------------------------------------------------------------------------
1483 --
1484 PROCEDURE process_range(p_start_object_id IN NUMBER
1485 ,p_end_object_id IN NUMBER )
1486 IS
1487 --
1488 -- Variables to populate WHO Columns
1489 --
1490 l_current_time DATE;
1491 l_user_id NUMBER;
1492 --
1493 -- Dynamic SQL
1494 --
1495 l_hint VARCHAR2(100);
1496 l_partition_clause VARCHAR2(100);
1497 l_partition_column VARCHAR2(100);
1498 l_part_col_value VARCHAR2(100);
1499 l_table_name VARCHAR2(30);
1500 l_sql_stmt VARCHAR2(32000);
1501 l_rtn VARCHAR2(30) := '
1502 ';
1503 --
1504 BEGIN
1505 --
1506 dbg('Inside process_range');
1507 --
1508 -- Set up dynamic sql for redo reduction
1509 --
1510 IF (g_redo_reduction = 'Y') THEN
1511 l_hint := '/*+ APPEND */ ';
1512 l_table_name := hri_utl_stage_table.get_staging_table_name
1513 (p_master_table_name => 'HRI_MAP_SUP_WRKFC_ASG');
1514 l_partition_clause := 'PARTITION (p' || g_worker_id || ') ';
1515 l_partition_column := l_rtn || ' ,worker_id';
1516 l_part_col_value := l_rtn || ' ,' || to_char(g_worker_id);
1517 ELSE
1518 l_table_name := 'HRI_MAP_SUP_WRKFC_ASG';
1519 END IF;
1520 --
1521 l_current_time := SYSDATE;
1522 l_user_id := fnd_global.user_id;
1523 --
1524 l_sql_stmt :=
1525 'INSERT ' || l_hint || 'INTO ' || l_table_name || ' ' || l_partition_clause || '
1526 (supervisor_person_id
1527 ,direct_supervisor_person_id
1528 ,effective_date
1529 ,effective_end_date
1530 ,evts_effective_end_date
1531 ,suph_effective_end_date
1532 ,pow_start_date
1533 ,pow_value_days_julian
1534 ,pow_extn_days_julian
1535 ,event_id
1536 ,person_id
1537 ,assignment_id
1538 ,location_id
1539 ,job_id
1540 ,organization_id
1541 ,position_id
1542 ,grade_id
1543 ,wkth_wktyp_sk_fk
1544 ,wkth_lvl1_sk_fk
1545 ,wkth_lvl2_sk_fk
1546 ,pow_band_sk_fk
1547 ,job_fmly_code
1548 ,job_fnctn_code
1549 ,primary_job_role_code
1550 ,geo_area_code
1551 ,geo_country_code
1552 ,geo_region_code
1553 ,geo_city_cid
1554 ,leaving_reason_code
1555 ,separation_category
1556 ,perf_band
1557 ,wkth_wktyp_code
1558 ,anl_slry_currency
1559 ,anl_slry_value
1560 ,headcount_value
1561 ,fte_value
1562 ,worker_hire_ind
1563 ,post_hire_asgn_start_ind
1564 ,worker_term_ind
1565 ,term_voluntary_ind
1566 ,term_involuntary_ind
1567 ,pre_sprtn_asgn_end_ind
1568 ,transfer_in_ind
1569 ,transfer_out_ind
1570 ,direct_ind
1571 ,primary_flag_ind
1572 ,primary_asg_with_hdc_ind
1573 ,summarization_rqd_ind
1574 ,summarization_rqd_chng_ind
1575 ,metric_adjust_multiplier
1576 ,supervisor_level
1577 ,admin_row_type
1578 ,admin_evts_rowid
1579 ,admin_suph_rowid
1580 ,admin_jobh_rowid
1581 ,admin_geoh_rowid
1582 ,last_update_date
1583 ,last_update_login
1584 ,last_updated_by
1585 ,created_by
1586 ,creation_date
1587 ,sub_assignment_id' ||
1588 l_partition_column || ')
1589 SELECT /*+ ORDERED */
1590 suph.sup_person_id supervisor_person_id
1591 ,evts.supervisor_id direct_supervisor_person_id
1592 ,GREATEST(evts.effective_change_date,
1593 suph.effective_start_date) effective_date
1594 ,LEAST(evts.effective_change_end_date,
1595 suph.effective_end_date ) effective_end_date
1596 ,evts.effective_change_end_date evts_effective_end_date
1597 ,suph.effective_end_date suph_effective_end_date
1598 ,evts.pow_start_date_adj pow_start_date
1599 ,to_char(evts.pow_start_date_adj,''J'') * evts.summarization_rqd_ind
1600 pow_value_days_julian
1601 ,nvl(to_char(evts.pow_extn_strt_dt,''J'') * evts.summarization_rqd_ind,0)
1602 pow_extn_days_julian
1603 ,evts.event_id event_id
1604 ,evts.person_id person_id
1605 ,evts.assignment_id assignment_id
1606 ,evts.location_id location_id
1607 ,evts.job_id job_id
1608 ,evts.organization_id organization_id
1609 ,evts.position_id position_id
1610 ,evts.grade_id grade_id
1611 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
1612 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
1613 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
1614 ,evts.pow_band_sk_fk pow_band_sk_fk
1615 ,jobh.job_fmly_code job_fmly_code
1616 ,jobh.job_fnctn_code job_fnctn_code
1617 ,CASE WHEN rolj.primary_role_for_job_flag = ''Y''
1618 THEN rolj.job_role_code
1619 ELSE ''NA_EDW''
1620 END primary_job_role_code
1621 ,geoh.area_code geo_area_code
1622 ,geoh.country_code geo_country_code
1623 ,geoh.region_code geo_region_code
1624 ,geoh.city_cid geo_city_cid
1625 ,evts.leaving_reason_code leaving_reason_code
1626 ,''NA_EDW'' separation_category
1627 ,evts.perf_band perf_band
1628 ,prsn.wkth_wktyp_code wkth_wktyp_code
1629 ,evts.anl_slry_currency anl_slry_currency
1630 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
1631 ,evts.headcount * evts.summarization_rqd_ind headcount_value
1632 ,evts.fte * evts.summarization_rqd_ind fte_value
1633 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1634 THEN 0
1635 ELSE evts.worker_hire_ind
1636 END worker_hire_ind
1637 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1638 THEN 0
1639 ELSE evts.post_hire_asgn_start_ind
1640 END post_hire_asgn_start_ind
1641 ,0 worker_term_ind
1642 ,0 term_voluntary_ind
1643 ,0 term_involuntary_ind
1644 ,0 pre_sprtn_asgn_end_ind
1645 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1646 THEN 1
1647 WHEN evts.effective_change_date > suph.effective_start_date
1648 THEN evts.supervisor_change_ind
1649 ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
1650 END transfer_in_ind
1651 ,0 transfer_out_ind
1652 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
1653 ,CASE WHEN evts.primary_flag = ''Y''
1654 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1655 primary_flag_ind
1656 ,CASE WHEN evts.primary_flag = ''Y'' and evts.headcount > 0
1657 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1658 primary_asg_with_hdc_ind
1659 ,evts.summarization_rqd_ind summarization_rqd_ind
1660 ,CASE WHEN evts.effective_change_date >= suph.effective_start_date
1661 THEN evts.summarization_rqd_chng_ind
1662 ELSE 0
1663 END summarization_rqd_chng_ind
1664 ,1 metric_adjust_multiplier
1665 ,suph.sup_level supervisor_level
1666 ,CASE WHEN evts.effective_change_date < suph.effective_start_date
1667 THEN ''GAIN SUP EVENT ONLY''
1668 WHEN evts.effective_change_date > suph.effective_start_date
1669 THEN ''GAIN ASG EVENT ONLY''
1670 ELSE ''GAIN ASG SUP EVENT''
1671 END admin_row_type
1672 ,evts.rowid admin_evts_rowid
1673 ,suph.rowid admin_suph_rowid
1674 ,jobh.rowid admin_jobh_rowid
1675 ,geoh.rowid admin_geoh_rowid
1676 ,:l_current_time
1677 ,' || l_user_id || '
1678 ,' || l_user_id || '
1679 ,' || l_user_id || '
1680 ,:l_current_time
1681 ,sub_assignment_id sub_assignment_id' ||
1682 l_part_col_value || '
1683 FROM
1684 hri_mb_asgn_events_ct evts
1685 ,hri_cs_jobh_ct jobh
1686 ,hri_cs_geo_lochr_ct geoh
1687 ,hri_cs_prsntyp_ct prsn
1688 ,hri_cs_job_job_role_ct rolj
1689 ,hri_cs_suph suph
1690 WHERE suph.sub_person_id = evts.supervisor_id
1691 AND suph.sup_invalid_flag_code = ''N''
1692 AND (evts.effective_change_date BETWEEN suph.effective_start_date
1693 AND suph.effective_end_date
1694 OR suph.effective_start_date BETWEEN evts.effective_change_date
1695 AND evts.effective_change_end_date)
1696 AND evts.pre_sprtn_asgn_end_ind = 0
1697 AND evts.worker_term_ind = 0
1698 AND geoh.location_id = evts.location_id
1699 AND jobh.job_id = evts.job_id
1700 AND evts.assignment_id between :start_object_id and :end_object_id
1701 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
1702 AND evts.job_id = rolj.job_id
1703 UNION ALL
1704 SELECT /*+ ORDERED */
1705 suph.sup_person_id supervisor_person_id
1706 ,evts.supervisor_id direct_supervisor_person_id
1707 ,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
1708 effective_date
1709 ,null effective_end_date
1710 ,evts.effective_change_end_date evts_effective_end_date
1711 ,suph.effective_end_date suph_effective_end_date
1712 ,evts.pow_start_date_adj pow_start_date
1713 ,to_char(evts.pow_start_date_adj,''J'') * evts.summarization_rqd_ind
1714 pow_value_days_julian
1715 ,nvl(to_char(evts.pow_extn_strt_dt,''J'') * evts.summarization_rqd_ind,0)
1716 pow_extn_days_julian
1717 ,evts.event_id event_id
1718 ,evts.person_id person_id
1719 ,evts.assignment_id assignment_id
1720 ,evts.location_id location_id
1721 ,evts.job_id job_id
1722 ,evts.organization_id organization_id
1723 ,evts.position_id position_id
1724 ,evts.grade_id grade_id
1725 ,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
1726 ,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
1727 ,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
1728 ,evts.pow_band_sk_fk pow_band_sk_fk
1729 ,jobh.job_fmly_code job_fmly_code
1730 ,jobh.job_fnctn_code job_fnctn_code
1731 ,CASE WHEN rolj.primary_role_for_job_flag = ''Y''
1732 THEN rolj.job_role_code
1733 ELSE ''NA_EDW''
1734 END primary_job_role_code
1735 ,geoh.area_code geo_area_code
1736 ,geoh.country_code geo_country_code
1737 ,geoh.region_code geo_region_code
1738 ,geoh.city_cid geo_city_cid
1739 ,evts.leaving_reason_code leaving_reason_code
1740 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1741 THEN ''NA_EDW''
1742 ELSE evts.separation_category_nxt
1743 END separation_category
1744 ,evts.perf_band perf_band
1745 ,prsn.wkth_wktyp_code wkth_wktyp_code
1746 ,evts.anl_slry_currency anl_slry_currency
1747 ,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
1748 ,evts.headcount * evts.summarization_rqd_ind headcount_value
1749 ,evts.fte * evts.summarization_rqd_ind fte_value
1750 ,0 worker_hire_ind
1751 ,0 post_hire_asgn_start_ind
1752 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1753 THEN 0
1754 ELSE evts.worker_term_nxt_ind
1755 END worker_term_ind
1756 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1757 THEN 0
1758 ELSE evts.term_voluntary_nxt_ind
1759 END term_voluntary_ind
1760 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1761 THEN 0
1762 ELSE evts.term_involuntary_nxt_ind
1763 END term_involuntary_ind
1764 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1765 THEN 0
1766 ELSE evts.pre_sprtn_asgn_end_nxt_ind
1767 END pre_sprtn_asgn_end_ind
1768 ,0 transfer_in_ind
1769 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1770 THEN 1
1771 WHEN suph.effective_end_date > evts.effective_change_end_date
1772 THEN evts.supervisor_change_nxt_ind
1773 ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
1774 END transfer_out_ind
1775 ,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
1776 --
1777 -- 4013742
1778 -- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
1779 -- when summarization is not required
1780 --
1781 ,CASE WHEN evts.primary_flag = ''Y''
1782 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1783 primary_flag_ind
1784 ,CASE WHEN evts.primary_flag = ''Y'' and evts.headcount > 0
1785 THEN 1 * evts.summarization_rqd_ind ELSE 0 END
1786 primary_asg_with_hdc_ind
1787 ,evts.summarization_rqd_ind summarization_rqd_ind
1788 ,CASE
1789 WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
1790 evts.summarization_rqd_chng_nxt_ind
1791 ELSE
1792 0
1793 END summarization_rqd_chng_ind
1794 ,-1 metric_adjust_multiplier
1795 ,suph.sup_level supervisor_level
1796 ,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
1797 THEN ''LOSS SUP EVENT ONLY''
1798 WHEN suph.effective_end_date > evts.effective_change_end_date
1799 THEN ''LOSS ASG EVENT ONLY''
1800 ELSE ''LOSS ASG SUP EVENT''
1801 END admin_row_type
1802 ,evts.rowid admin_evts_rowid
1803 ,suph.rowid admin_suph_rowid
1804 ,jobh.rowid admin_jobh_rowid
1805 ,geoh.rowid admin_geoh_rowid
1806 ,:l_current_time
1807 ,' || l_user_id || '
1808 ,' || l_user_id || '
1809 ,' || l_user_id || '
1810 ,:l_current_time
1811 ,sub_assignment_id sub_assignment_id' ||
1812 l_part_col_value || '
1813 FROM
1814 hri_mb_asgn_events_ct evts
1815 ,hri_cs_jobh_ct jobh
1816 ,hri_cs_geo_lochr_ct geoh
1817 ,hri_cs_prsntyp_ct prsn
1818 ,hri_cs_job_job_role_ct rolj
1819 ,hri_cs_suph suph
1820 WHERE suph.sub_person_id = evts.supervisor_id
1821 AND suph.sup_invalid_flag_code = ''N''
1822 AND (suph.effective_end_date BETWEEN evts.effective_change_date
1823 AND evts.effective_change_end_date
1824 OR evts.effective_change_end_date BETWEEN suph.effective_start_date
1825 AND suph.effective_end_date)
1826 AND evts.pre_sprtn_asgn_end_ind = 0
1827 AND evts.worker_term_ind = 0
1828 AND geoh.location_id = evts.location_id
1829 AND jobh.job_id = evts.job_id
1830 AND evts.assignment_id between :start_object_id and :end_object_id
1831 AND LEAST(suph.effective_end_date,
1832 evts.effective_change_end_date) < :end_of_time
1833 AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
1834 AND evts.job_id = rolj.job_id';
1835 --
1836 EXECUTE IMMEDIATE l_sql_stmt USING
1837 l_current_time, l_current_time, p_start_object_id, p_end_object_id,
1838 l_current_time, l_current_time, p_start_object_id, p_end_object_id,
1839 hr_general.end_of_time;
1840 --
1841 dbg(SQL%ROWCOUNT||' records inserted into ' || l_table_name);
1842 --
1843 dbg('Exiting process_range');
1844 --
1845 EXCEPTION WHEN OTHERS THEN
1846 --
1847 output(sqlerrm);
1848 --
1849 --
1850 RAISE;
1851 --
1852 --
1853 END process_range;
1854 --
1855 -- ----------------------------------------------------------------------------
1856 -- PRE_PROCESS
1857 -- This procedure includes all the logic required for performing the pre_process
1858 -- task of HRI multithreading utility. It drops the indexes and return the SQL
1859 -- required for generating the ranges
1860 -- ----------------------------------------------------------------------------
1861 --
1862 PROCEDURE PRE_PROCESS(
1863 --
1864 p_mthd_action_id IN NUMBER,
1865 p_sqlstr OUT NOCOPY VARCHAR2) IS
1866 --
1867 l_dummy1 VARCHAR2(2000);
1868 l_dummy2 VARCHAR2(2000);
1869 l_schema VARCHAR2(400);
1870 --
1871 BEGIN
1872 --
1873 -- Record the process start
1874 --
1875 dbg('Inside pre_process');
1876 --
1877 -- Set up the parameters
1878 --
1879 set_parameters
1880 (p_mthd_action_id => p_mthd_action_id,
1881 p_mthd_stage_code => 'PRE_PROCESS');
1882 --
1883 -- Disable the WHO trigger
1884 --
1885 run_sql_stmt_noerr('ALTER TRIGGER HRI_MAP_SUP_WRKFC_ASG_WHO DISABLE');
1886 --
1887 -- ---------------------------------------------------------------------------
1888 -- Full Refresh Section
1889 -- ---------------------------------------------------------------------------
1890 --
1891 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1892 --
1893 -- If it's a full refresh
1894 --
1895 IF (g_full_refresh = 'Y') THEN
1896 --
1897 dbg('Inside Full Refresh');
1898 --
1899 -- Set up staging table for redo reduction
1900 --
1901 IF (g_redo_reduction = 'Y') THEN
1902 hri_utl_stage_table.set_up
1903 (p_owner => l_schema,
1904 p_master_table_name => 'HRI_MAP_SUP_WRKFC_ASG');
1905 END IF;
1906 --
1907 -- Disable the materilized view logs
1908 --
1909 manage_mview_logs(p_schema => l_schema ,
1910 p_enable_disable => 'D');
1911 --
1912 -- Truncate the table
1913 --
1914 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MAP_SUP_WRKFC_ASG';
1915 --
1916 -- Drop Indexes
1917 --
1918 hri_utl_ddl.log_and_drop_indexes(
1919 p_application_short_name => 'HRI',
1920 p_table_name => 'HRI_MAP_SUP_WRKFC_ASG',
1921 p_table_owner => l_schema);
1922 --
1923 -- Select all people with employee assignments in the collection range.
1924 -- The bind variable must be present for this sql to work when called
1925 -- by PYUGEN, else itwill give error.
1926 --
1927 p_sqlstr :=
1928 'SELECT DISTINCT
1929 assignment_id object_id
1930 FROM hri_mb_asgn_events_ct
1931 ORDER BY assignment_id';
1932 --
1933 -- End of Full Refresh Section
1934 -- -------------------------------------------------------------------------
1935 --
1936 -- -------------------------------------------------------------------------
1937 -- Start of Incremental Refresh Section
1938 --
1939 ELSE
1940 dbg('Inside Incremental Refresh');
1941 --
1942 -- Select all people for whom events have occurred. The bind variable must
1943 -- be present for this sql to work when called by PYUGEN, else it will
1944 -- give error.
1945 --
1946 p_sqlstr :=
1947 'SELECT /*+ parallel (EQ, default, default) */ DISTINCT source_id object_id
1948 FROM hri_eq_asg_sup_wrfc eq
1949 ORDER BY object_id';
1950
1951 --
1952 -- End of Incremental Refresh Section
1953 -- -------------------------------------------------------------------------
1954 --
1955 END IF;
1956 --
1957 END IF;
1958 --
1959 dbg('Exiting pre_process');
1960 --
1961 END PRE_PROCESS;
1962 --
1963 -- ----------------------------------------------------------------------------
1964 -- PROCESS_RANGE
1965 -- This procedure is dynamically the HRI multithreading utility child threads
1966 -- for processing the assignment ranges. The procedure invokes the overloaded
1967 -- process_range procedure to process the range.
1968 -- ----------------------------------------------------------------------------
1969 --
1970 PROCEDURE process_range(
1971 errbuf OUT NOCOPY VARCHAR2
1972 ,retcode OUT NOCOPY NUMBER
1973 ,p_mthd_action_id IN NUMBER
1974 ,p_mthd_range_id IN NUMBER
1975 ,p_start_object_id IN NUMBER
1976 ,p_end_object_id IN NUMBER)
1977 IS
1978 --
1979 l_error_step NUMBER;
1980 --
1981 BEGIN
1982 --
1983 --
1984 --
1985 set_parameters
1986 (p_mthd_action_id => p_mthd_action_id,
1987 p_mthd_stage_code => 'PROCESS_RANGE');
1988 --
1989 dbg('calling process_range for object range from '||p_start_object_id || ' to '|| p_end_object_id);
1990 --
1991 -- Based on the refresh type call the corresponding procedure
1992 --
1993 IF g_full_refresh = 'Y' THEN
1994 --
1995 process_range(p_start_object_id => p_start_object_id
1996 ,p_end_object_id => p_end_object_id);
1997 --
1998 ELSE
1999 --
2000 incremental_process(p_start_object_id => p_start_object_id
2001 ,p_end_object_id => p_end_object_id);
2002 --
2003 END IF;
2004 --
2005 errbuf := 'SUCCESS';
2006 retcode := 0;
2007 EXCEPTION
2008 WHEN others THEN
2009 output('Error encountered while processing' );
2010 output(sqlerrm);
2011 errbuf := SQLERRM;
2012 retcode := SQLCODE;
2013 --
2014 RAISE;
2015 --
2016 END process_range;
2017 --
2018 -- ----------------------------------------------------------------------------
2019 -- POST_PROCESS
2020 -- This procedure is dynamically invoked by the HRI Multithreading utility.
2021 -- It finishes the processing by updating the BIS_REFRESH_LOG table
2022 -- ----------------------------------------------------------------------------
2023 --
2024 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
2025 --
2026 l_dummy1 VARCHAR2(2000);
2027 l_dummy2 VARCHAR2(2000);
2028 l_schema VARCHAR2(400);
2029 --
2030 --
2031 BEGIN
2032 --
2033 dbg('Inside post_process');
2034 --
2035 set_parameters
2036 (p_mthd_action_id => p_mthd_action_id,
2037 p_mthd_stage_code => 'POST_PROCESS');
2038 --
2039 hri_bpl_conc_log.record_process_start('HRI_OPL_SUP_WRKFC_ASG');
2040 --
2041 -- Collect stats for full refresh
2042 --
2043 IF (g_full_refresh = 'Y') THEN
2044 --
2045 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
2046 --
2047 -- Redo reduction: Move data to master table and purge staging table
2048 --
2049 IF (g_redo_reduction = 'Y') THEN
2050 hri_utl_stage_table.clean_up
2051 (p_owner => l_schema,
2052 p_master_table_name => 'HRI_MAP_SUP_WRKFC_ASG');
2053 END IF;
2054 --
2055 -- Enable the materialized view logs
2056 --
2057 manage_mview_logs(p_schema => l_schema,
2058 p_enable_disable => 'E');
2059 --
2060 --
2061 -- Create indexes
2062 --
2063 dbg('Full Refresh selected - Creating indexes');
2064 --
2065 hri_utl_ddl.recreate_indexes(
2066 p_application_short_name => 'HRI',
2067 p_table_name => 'HRI_MAP_SUP_WRKFC_ASG',
2068 p_table_owner => l_schema);
2069 --
2070 -- Collect the statistics only when the process is not called by a concurrent manager
2071 --
2072 IF fnd_global.conc_request_id is null THEN
2073 --
2074 dbg('Running from outside the request set - gathering stats');
2075 fnd_stats.gather_table_stats(l_schema,'HRI_MAP_SUP_WRKFC_ASG');
2076 --
2077 END IF;
2078 --
2079 END IF;
2080 --
2081 ELSE
2082 --
2083 -- Remove duplicates in incremental mode
2084 -- Bug 4404897
2085 --
2086 DELETE /*+ INDEX(dlt hri_map_sup_wrkfc_asg_n4) */
2087 FROM hri_map_sup_wrkfc_asg dlt
2088 WHERE assignment_id IN
2089 (SELECT source_id
2090 FROM hri_eq_asg_sup_wrfc evt
2091 WHERE evt.source_type = 'ASG_EVENT')
2092 AND EXISTS
2093 (SELECT /*+ INDEX(dlt2 hri_map_sup_wrkfc_asg_n4) */
2094 NULL
2095 FROM
2096 hri_map_sup_wrkfc_asg dlt2
2097 WHERE dlt2.assignment_id = dlt.assignment_id
2098 AND dlt2.evts_effective_end_date = dlt.evts_effective_end_date
2099 AND dlt2.supervisor_person_id = dlt.supervisor_person_id
2100 AND dlt2.effective_date = dlt.effective_date
2101 AND dlt2.metric_adjust_multiplier = dlt.metric_adjust_multiplier
2102 AND dlt2.ROWID > dlt.ROWID);
2103 --
2104 END IF;
2105 --
2106 -- Truncate the assignment delta events queue
2107 --
2108 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
2109 --
2110 dbg('Truncating the assignment events equeue');
2111 --
2112 run_sql_stmt_noerr('TRUNCATE TABLE '||l_schema||'.HRI_EQ_ASG_SUP_WRFC');
2113 --
2114 END IF;
2115 --
2116 -- Enable the WHO trigger on the events fact table
2117 --
2118 run_sql_stmt_noerr('ALTER TRIGGER HRI_MAP_SUP_WRKFC_ASG_WHO ENABLE');
2119 --
2120 hri_bpl_conc_log.log_process_end(
2121 p_status => TRUE
2122 ,p_period_from => TRUNC(g_refresh_start_date)
2123 ,p_period_to => TRUNC(SYSDATE)
2124 ,p_attribute1 => g_full_refresh);
2125 --
2126 dbg('Exiting post_process');
2127 --
2128 END post_process;
2129 --
2130 -- ----------------------------------------------------------------------------
2131 -- LOAD_TABLE
2132 -- This procedure can be called from the Test harness to populate the table.
2133 -- ----------------------------------------------------------------------------
2134 --
2135 PROCEDURE load_table
2136 IS
2137 --
2138 l_sqlstr VARCHAR2(4000);
2139 --
2140 CURSOR c_range_cursor IS
2141 SELECT mthd_range_id,
2142 min(object_id) start_object_id,
2143 max(object_id) end_object_id
2144 FROM (SELECT hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
2145 ,object_id
2146 FROM (SELECT DISTINCT assignment_id object_id
2147 FROM hri_mb_asgn_events_ct
2148 ORDER BY assignment_id)
2149 )
2150 GROUP BY mthd_range_id;
2151 --
2152 BEGIN
2153 --
2154 dbg('Inside load_table');
2155 --
2156 -- Call Pre Process
2157 --
2158 pre_process(p_mthd_action_id => null,
2159 p_sqlstr => l_sqlstr);
2160 --
2161 -- Call Process Range
2162 --
2163 FOR l_range IN c_range_cursor LOOP
2164 --
2165 dbg('range ='||l_range.start_object_id|| ' - '||l_range.end_object_id );
2166 process_range(p_start_object_id => l_range.start_object_id
2167 ,p_end_object_id => l_range.end_object_id);
2168 --
2169 COMMIT;
2170 --
2171 END LOOP;
2172 --
2173 -- Call Post Process
2174 --
2175 post_process (p_mthd_action_id => null);
2176 --
2177 dbg('Exiting load_table');
2178 --
2179 EXCEPTION
2180 --
2181 WHEN OTHERS THEN
2182 --
2183 dbg('Error in load_table = ');
2184 dbg(SQLERRM);
2185 RAISE;
2186 --
2187 END load_table;
2188 --
2189 END HRI_OPL_SUP_WRKFC_ASG;