[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_WRKFC_EVENTS
Source
1 PACKAGE BODY hri_opl_wrkfc_events AS
2 /* $Header: hriowevt.pkb 120.12.12000000.2 2007/04/12 13:22:41 smohapat noship $ */
3
4 -- End of time
5 g_end_of_time DATE := hr_general.end_of_time;
6
7 -- Global HRI Multithreading Array
8 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
9
10 -- Global parameters
11 g_refresh_start_date DATE;
12 g_full_refresh VARCHAR2(30);
13 g_sysdate DATE;
14 g_user NUMBER;
15 g_dbi_start_date DATE;
16
17
18 -- ----------------------------------------------------------------------------
19 -- Runs given sql statement dynamically
20 -- ----------------------------------------------------------------------------
21 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
22
23 BEGIN
24
25 EXECUTE IMMEDIATE p_sql_stmt;
26
27 EXCEPTION WHEN OTHERS THEN
28
29 null;
30
31 END run_sql_stmt_noerr;
32
33 -- ----------------------------------------------------------------------------
34 -- Sets global parameters from multi-threading process parameters
35 -- ----------------------------------------------------------------------------
36 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
37 p_mthd_stage_code IN VARCHAR2) IS
38
39 l_dbi_collection_start_date DATE;
40
41 BEGIN
42
43 -- If parameters haven't already been set, then set them
44 IF (g_refresh_start_date IS NULL OR
45 p_mthd_stage_code = 'PRE_PROCESS') THEN
46
47 l_dbi_collection_start_date :=
48 hri_oltp_conc_param.get_date_parameter_value
49 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
50 p_process_table_name => 'HRI_MB_WRKFC_EVT_CT');
51
52 -- If called for the first time set the defaulted parameters
53 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
54
55 g_full_refresh :=
56 hri_oltp_conc_param.get_parameter_value
57 (p_parameter_name => 'FULL_REFRESH',
58 p_process_table_name => 'HRI_MB_WRKFC_EVT_CT');
59
60 -- Log defaulted parameters so the slave processes pick up
61 hri_opl_multi_thread.update_parameters
62 (p_mthd_action_id => p_mthd_action_id,
63 p_full_refresh => g_full_refresh,
64 p_global_start_date => l_dbi_collection_start_date);
65
66 END IF;
67
68 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
69 (p_mthd_action_id);
70 g_refresh_start_date := g_mthd_action_array.collect_from_date;
71 g_full_refresh := g_mthd_action_array.full_refresh_flag;
72 g_sysdate := sysdate;
73 g_user := fnd_global.user_id;
74 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
75
76 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
77 hri_bpl_conc_log.dbg('Collect from: N/A');
78
79 END IF;
80
81 END set_parameters;
82
83
84 -- ----------------------------------------------------------------------------
85 -- Processes base fact in full refresh mode
86 -- ----------------------------------------------------------------------------
87 PROCEDURE process_month_summary_full(p_start_asg_id IN NUMBER,
88 p_end_asg_id IN NUMBER) IS
89
90 BEGIN
91
92 INSERT INTO hri_mds_wrkfc_mnth_ct
93 (wevt_evtypcmb_fk
94 ,asg_assgnmnt_fk
95 ,per_person_fk
96 ,per_person_mgr_fk
97 ,per_person_mgr_prv_fk
98 ,mgr_mngrsc_fk
99 ,mgr_mngrsc_prv_fk
100 ,org_organztn_fk
101 ,org_organztn_prv_fk
102 ,job_job_fk
103 ,job_job_prv_fk
104 ,grd_grade_fk
105 ,grd_grade_prv_fk
106 ,pos_position_fk
107 ,pos_position_prv_fk
108 ,geo_location_fk
109 ,geo_location_prv_fk
110 ,asgrsn_asgrsn_fk
111 ,sprn_sprtnrsn_fk
112 ,scat_spcatgry_fk
113 ,ptyp_pertyp_fk
114 ,prfm_perfband_fk
115 ,pow_powband_fk
116 ,time_month_snp_fk
117 ,time_day_mnth_start_fk
118 ,time_day_mnth_end_fk
119 ,time_quarter_fk
120 ,time_year_fk
121 ,cur_currency_fk
122 ,headcount_start
123 ,headcount_end
124 ,headcount_hire
125 ,headcount_term
126 ,headcount_sep_vol
127 ,headcount_sep_invol
128 ,headcount_prmtn
129 ,fte_start
130 ,fte_end
131 ,fte_hire
132 ,fte_term
133 ,fte_sep_vol
134 ,fte_sep_invol
135 ,fte_prmtn
136 ,count_pasg_end
137 ,count_pasg_hire
138 ,count_pasg_term
139 ,count_pasg_sep_vol
140 ,count_pasg_sep_invol
141 ,count_asg_end
142 ,count_asg_hire
143 ,count_asg_term
144 ,count_asg_sep_vol
145 ,count_asg_sep_invol
146 ,count_asg_prmtn
147 ,pow_days_on_end_date
148 ,pow_months_on_end_date
149 ,days_since_last_prmtn
150 ,months_since_last_prmtn
151 ,anl_slry_start
152 ,anl_slry_end
153 ,employee_ind
154 ,contingent_ind
155 ,last_month_in_qtr_ind
156 ,last_month_in_year_ind
157 ,adt_pow_band
158 ,creation_date
159 ,created_by
160 ,last_updated_by
161 ,last_update_login
162 ,last_update_date)
163 SELECT
164 to_number(null) wevt_evtypcmb_fk
165 ,fct.asg_assgnmnt_fk
166 ,fct.per_person_fk
167 ,fct.per_person_mgr_fk
168 ,fct.per_person_mgr_prv_fk
169 ,fct.mgr_mngrsc_fk
170 ,fct.mgr_mngrsc_prv_fk
171 ,fct.org_organztn_fk
172 ,fct.org_organztn_prv_fk
173 ,fct.job_job_fk
174 ,fct.job_job_prv_fk
175 ,fct.grd_grade_fk
176 ,fct.grd_grade_prv_fk
177 ,fct.pos_position_fk
178 ,fct.pos_position_prv_fk
179 ,fct.geo_location_fk
180 ,fct.geo_location_prv_fk
181 ,fct.asgrsn_asgrsn_fk
182 ,fct.sprn_sprtnrsn_fk
183 ,fct.scat_spcatgry_fk
184 ,fct.ptyp_pertyp_fk
185 ,fct.prfm_perfband_fk
186 ,fct.pow_powband_fk
187 ,CASE WHEN msrs.zero_row_ind = 0
188 THEN msrs.time_month_snp_fk
189 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
190 END time_month_snp_fk
191 ,CASE WHEN msrs.zero_row_ind = 0
192 THEN msrs.time_day_mnth_start_fk
193 ELSE msrs.time_day_mnth_end_fk + 1
194 END time_day_mnth_start_fk
195 ,CASE WHEN msrs.zero_row_ind = 0
196 THEN msrs.time_day_mnth_end_fk
197 ELSE ADD_MONTHS(msrs.time_day_mnth_start_fk, 2) - 1
198 END time_day_mnth_end_fk
199 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
200 THEN msrs.time_month_snp_fk
201 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
202 END) / 100, 0) time_quarter_end_fk
203 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
204 THEN msrs.time_month_snp_fk
205 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
206 END) / 1000, 0) time_year_end_fk
207 ,fct.cur_currency_fk
208 ,SUM(msrs.headcount_start)
209 ,SUM(msrs.headcount_end)
210 ,SUM(msrs.headcount_hire)
211 ,SUM(msrs.headcount_term)
212 ,SUM(msrs.headcount_sep_vol)
213 ,SUM(msrs.headcount_sep_invol)
214 ,SUM(msrs.headcount_prmtn)
215 ,SUM(msrs.fte_start)
216 ,SUM(msrs.fte_end)
217 ,SUM(msrs.fte_hire)
218 ,SUM(msrs.fte_term)
219 ,SUM(msrs.fte_sep_vol)
220 ,SUM(msrs.fte_sep_invol)
221 ,SUM(msrs.fte_prmtn)
222 ,SUM(msrs.count_pasg_end)
223 ,SUM(msrs.count_pasg_hire)
224 ,SUM(msrs.count_pasg_term)
225 ,SUM(msrs.count_pasg_sep_vol)
226 ,SUM(msrs.count_pasg_sep_invol)
227 ,SUM(msrs.count_asg_end)
228 ,SUM(msrs.count_asg_hire)
229 ,SUM(msrs.count_asg_term)
230 ,SUM(msrs.count_asg_sep_vol)
231 ,SUM(msrs.count_asg_sep_invol)
232 ,SUM(msrs.count_asg_prmtn)
233 ,SUM(msrs.pow_days_on_end_date)
234 ,SUM(msrs.pow_months_on_end_date)
235 ,SUM(msrs.days_since_last_prmtn)
236 ,SUM(msrs.months_since_last_prmtn)
237 ,SUM(msrs.anl_slry_start)
238 ,SUM(msrs.anl_slry_end)
239 ,fct.employee_ind
240 ,fct.contingent_ind
241 ,msrs.last_month_in_qtr_ind
242 ,msrs.last_month_in_year_ind
243 ,fct.adt_pow_band
244 ,g_sysdate
245 ,g_user
246 ,g_user
247 ,g_user
248 ,g_sysdate
249 FROM
250 (SELECT
251 wevt.asg_assgnmnt_fk
252 ,wevt.per_person_fk
253 ,wevt.per_person_mgr_fk
254 ,NVL(LAG(wevt.per_person_mgr_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
255 ORDER BY mnth.month_id)
256 ,-1) per_person_mgr_prv_fk
257 ,wevt.mgr_mngrsc_fk
258 ,NVL(LAG(wevt.mgr_mngrsc_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
259 ORDER BY mnth.month_id)
260 ,-1) mgr_mngrsc_prv_fk
261 ,wevt.org_organztn_fk
262 ,NVL(LAG(wevt.org_organztn_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
263 ORDER BY mnth.month_id)
264 ,-1) org_organztn_prv_fk
265 ,wevt.job_job_fk
266 ,NVL(LAG(wevt.job_job_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
267 ORDER BY mnth.month_id)
268 ,-1) job_job_prv_fk
269 ,wevt.grd_grade_fk
270 ,NVL(LAG(wevt.grd_grade_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
271 ORDER BY mnth.month_id)
272 ,-1) grd_grade_prv_fk
273 ,wevt.pos_position_fk
274 ,NVL(LAG(wevt.pos_position_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
275 ORDER BY mnth.month_id)
276 ,-1) pos_position_prv_fk
277 ,wevt.geo_location_fk
278 ,NVL(LAG(wevt.geo_location_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
279 ORDER BY mnth.month_id)
280 ,-1) geo_location_prv_fk
281 ,wevt.asgrsn_asgrsn_fk
282 ,wevt.sprn_sprtnrsn_fk
283 ,wevt.scat_spcatgry_fk
284 ,wevt.ptyp_pertyp_fk
285 ,wevt.prfm_perfband_fk
286 ,wevt.pow_powband_fk
287 ,mnth.month_id time_month_snp_fk
288 ,wevt.cur_currency_fk
289 ,wevt.employee_ind
290 ,wevt.contingent_ind
291 ,wevt.adt_pow_band
292 FROM
293 hri_mb_wrkfc_evt_ct wevt
294 ,fii_time_month mnth
295 WHERE mnth.end_date BETWEEN wevt.time_day_evt_fk
296 AND wevt.time_day_evt_end_fk
297 -- If assignment is ended, only snapshot the ended assignment once
298 AND ((wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0)
299 OR mnth.start_date <= wevt.time_day_evt_fk)
300 AND wevt.asg_assgnmnt_fk BETWEEN p_start_asg_id
301 AND p_end_asg_id
302 AND mnth.end_date >= g_dbi_start_date
303 AND mnth.start_date <= TRUNC(g_sysdate)
304 ) fct
305 ,(SELECT
306 mnth.month_id time_month_snp_fk
307 ,mnth.start_date time_day_mnth_start_fk
308 ,mnth.end_date time_day_mnth_end_fk
309 ,wevt.asg_assgnmnt_fk asg_assgnmnt_fk
310 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
311 AND wevt.time_day_evt_end_fk
312 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
313 THEN wevt.headcount
314 ELSE 0
315 END) headcount_start
316 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
317 AND wevt.time_day_evt_end_fk
318 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
319 THEN wevt.headcount
320 ELSE 0
321 END) headcount_end
322 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
323 AND mnth.end_date
324 THEN wevt.headcount_hire
325 ELSE 0
326 END) headcount_hire
327 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
328 AND mnth.end_date
329 THEN wevt.headcount_term
330 ELSE 0
331 END) headcount_term
332 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
333 AND mnth.end_date
334 THEN wevt.headcount_term * wevt.term_voluntary_ind
335 ELSE 0
336 END) headcount_sep_vol
337 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
338 AND mnth.end_date
339 THEN wevt.headcount_term * wevt.term_involuntary_ind
340 ELSE 0
341 END) headcount_sep_invol
342 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
343 AND mnth.end_date
344 THEN wevt.headcount * wevt.promotion_ind
345 ELSE 0
346 END) headcount_prmtn
347 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
348 AND wevt.time_day_evt_end_fk
349 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
350 THEN wevt.fte
351 ELSE 0
352 END) fte_start
353 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
354 AND wevt.time_day_evt_end_fk
355 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
356 THEN wevt.fte
357 ELSE 0
358 END) fte_end
359 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
360 AND mnth.end_date
361 THEN wevt.fte_hire
362 ELSE 0
363 END) fte_hire
364 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
365 AND mnth.end_date
366 THEN wevt.fte_term
367 ELSE 0
368 END) fte_term
369 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
370 AND mnth.end_date
371 THEN wevt.fte_term * wevt.term_voluntary_ind
372 ELSE 0
373 END) fte_sep_vol
374 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
375 AND mnth.end_date
376 THEN wevt.fte_term * wevt.term_involuntary_ind
377 ELSE 0
378 END) fte_sep_invol
379 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
380 AND mnth.end_date
381 THEN wevt.fte * wevt.promotion_ind
382 ELSE 0
383 END) fte_prmtn
384 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
385 AND wevt.time_day_evt_end_fk
386 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
387 THEN wevt.primary_ind
388 ELSE 0
389 END) count_pasg_end
390 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
391 AND mnth.end_date
392 THEN wevt.hire_or_start_ind * wevt.primary_ind
393 ELSE 0
394 END) count_pasg_hire
395 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
396 AND mnth.end_date
397 THEN wevt.term_or_end_ind * wevt.primary_ind
398 ELSE 0
399 END) count_pasg_term
400 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
401 AND mnth.end_date
402 THEN wevt.term_voluntary_ind * wevt.primary_ind
403 ELSE 0
404 END) count_pasg_sep_vol
405 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
406 AND mnth.end_date
407 THEN wevt.term_involuntary_ind * wevt.primary_ind
408 ELSE 0
409 END) count_pasg_sep_invol
410 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
411 AND wevt.time_day_evt_end_fk
412 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
413 THEN 1
414 ELSE 0
415 END) count_asg_end
416 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
417 AND mnth.end_date
418 THEN wevt.hire_or_start_ind
419 ELSE 0
420 END) count_asg_hire
421 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
422 AND mnth.end_date
423 THEN wevt.term_or_end_ind
424 ELSE 0
425 END) count_asg_term
426 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
427 AND mnth.end_date
428 THEN wevt.term_voluntary_ind
429 ELSE 0
430 END) count_asg_sep_vol
431 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
432 AND mnth.end_date
433 THEN wevt.term_involuntary_ind
434 ELSE 0
435 END) count_asg_sep_invol
436 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
437 AND mnth.end_date
438 THEN wevt.promotion_ind
439 ELSE 0
440 END) count_asg_prmtn
441 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
442 AND wevt.time_day_evt_end_fk
443 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
444 THEN wevt.pow_days_on_event_date +
445 (mnth.end_date - wevt.time_day_evt_fk)
446 ELSE 0
447 END) pow_days_on_end_date
448 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
449 AND wevt.time_day_evt_end_fk
450 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
451 THEN MONTHS_BETWEEN
452 (mnth.end_date,
453 wevt.time_day_evt_fk - wevt.pow_days_on_event_date)
454 ELSE 0
455 END) pow_months_on_end_date
456 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
457 AND wevt.time_day_evt_end_fk
458 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
459 THEN wevt.days_since_last_prmtn +
460 (mnth.end_date - wevt.time_day_evt_fk)
461 ELSE 0
462 END) days_since_last_prmtn
463 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
464 AND wevt.time_day_evt_end_fk
465 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
466 THEN MONTHS_BETWEEN
467 (mnth.end_date,
468 wevt.time_day_evt_fk - wevt.days_since_last_prmtn)
469 ELSE 0
470 END) months_since_last_prmtn
471 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
472 AND wevt.time_day_evt_end_fk
473 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
474 THEN wevt.anl_slry
475 ELSE 0
476 END) anl_slry_start
477 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
478 AND wevt.time_day_evt_end_fk
479 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
480 THEN wevt.anl_slry
481 ELSE 0
482 END) anl_slry_end
483 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Q'), 3) - 1
484 THEN 1
485 WHEN MAX(wevt.worker_term_ind) = 1 OR MAX(wevt.pre_sprtn_asgn_end_ind) = 1
486 THEN 1
487 WHEN TRUNC(g_sysdate) BETWEEN mnth.start_date AND mnth.end_date
488 THEN 1
489 ELSE 0
490 END last_month_in_qtr_ind
491 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Y'), 12) - 1
492 THEN 1
493 WHEN MAX(wevt.worker_term_ind) = 1 OR MAX(wevt.pre_sprtn_asgn_end_ind) = 1
494 THEN 1
495 WHEN TRUNC(g_sysdate) BETWEEN mnth.start_date AND mnth.end_date
496 THEN 1
497 ELSE 0
498 END last_month_in_year_ind
499 ,0 zero_row_ind
500 FROM
501 hri_mb_wrkfc_evt_ct wevt
502 ,fii_time_month mnth
503 WHERE wevt.time_day_evt_fk <= mnth.end_date
504 AND mnth.start_date <= wevt.time_day_evt_end_fk
505 -- If assignment is ended, only snapshot the ended assignment once
506 AND ((wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0)
507 OR mnth.start_date <= wevt.time_day_evt_fk)
508 AND wevt.asg_assgnmnt_fk BETWEEN p_start_asg_id
509 AND p_end_asg_id
510 AND mnth.end_date >= g_dbi_start_date
511 AND mnth.start_date <= TRUNC(g_sysdate)
512 GROUP BY
513 mnth.month_id
514 ,mnth.start_date
515 ,mnth.end_date
516 ,wevt.asg_assgnmnt_fk
517 UNION ALL
518 SELECT
519 mnth.month_id time_month_snp_fk
520 ,mnth.start_date time_day_mnth_start_fk
521 ,mnth.end_date time_day_mnth_end_fk
522 ,wevt.asg_assgnmnt_fk asg_assgnmnt_fk
523 ,0 headcount_start
524 ,0 headcount_end
525 ,0 headcount_hire
526 ,0 headcount_term
527 ,0 headcount_sep_vol
528 ,0 headcount_sep_invol
529 ,0 headcount_prmtn
530 ,0 fte_start
531 ,0 fte_end
532 ,0 fte_hire
533 ,0 fte_term
534 ,0 fte_sep_vol
535 ,0 fte_sep_invol
536 ,0 fte_prmtn
537 ,0 count_pasg_end
538 ,0 count_pasg_hire
539 ,0 count_pasg_term
540 ,0 count_pasg_sep_vol
541 ,0 count_pasg_sep_invol
542 ,0 count_asg_end
543 ,0 count_asg_hire
544 ,0 count_asg_term
545 ,0 count_asg_sep_vol
546 ,0 count_asg_sep_invol
547 ,0 count_asg_prmtn
548 ,0 pow_days_on_end_date
549 ,0 pow_months_on_end_date
550 ,to_number(null) days_since_last_prmtn
551 ,to_number(null) months_since_last_prmtn
552 ,0 anl_slry_start
553 ,0 anl_slry_end
554 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Q'), 2) - 1
555 THEN 1
556 WHEN wevt.worker_term_nxt_ind = 1 OR wevt.pre_sprtn_asgn_end_nxt_ind = 1
557 THEN 1
558 WHEN ADD_MONTHS(TRUNC(g_sysdate), -1) BETWEEN mnth.start_date AND mnth.end_date
559 THEN 1
560 ELSE 0
561 END last_month_in_qtr_ind
562 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Y'), 11) - 1
563 THEN 1
564 WHEN wevt.worker_term_nxt_ind = 1 OR wevt.pre_sprtn_asgn_end_nxt_ind = 1
565 THEN 1
566 WHEN ADD_MONTHS(TRUNC(g_sysdate), -1) BETWEEN mnth.start_date AND mnth.end_date
567 THEN 1
568 ELSE 0
569 END last_month_in_year_ind
570 ,1 zero_row_ind
571 FROM
572 hri_mb_wrkfc_evt_ct wevt
573 ,fii_time_month mnth
574 WHERE mnth.end_date BETWEEN wevt.time_day_evt_fk
575 AND wevt.time_day_evt_end_fk
576 -- If assignment is ended, no need to snapshot
577 AND wevt.worker_term_ind = 0
578 AND wevt.pre_sprtn_asgn_end_ind = 0
579 AND wevt.asg_assgnmnt_fk BETWEEN p_start_asg_id
580 AND p_end_asg_id
581 AND mnth.end_date >= g_dbi_start_date
582 AND mnth.end_date < TRUNC(g_sysdate)
583 ) msrs
584 WHERE msrs.asg_assgnmnt_fk = fct.asg_assgnmnt_fk
585 AND msrs.time_month_snp_fk = fct.time_month_snp_fk
586 GROUP BY
587 fct.asg_assgnmnt_fk
588 ,fct.per_person_fk
589 ,fct.per_person_mgr_fk
590 ,fct.per_person_mgr_prv_fk
591 ,fct.mgr_mngrsc_fk
592 ,fct.mgr_mngrsc_prv_fk
593 ,fct.org_organztn_fk
594 ,fct.org_organztn_prv_fk
595 ,fct.job_job_fk
596 ,fct.job_job_prv_fk
597 ,fct.grd_grade_fk
598 ,fct.grd_grade_prv_fk
599 ,fct.pos_position_fk
600 ,fct.pos_position_prv_fk
601 ,fct.geo_location_fk
602 ,fct.geo_location_prv_fk
603 ,fct.asgrsn_asgrsn_fk
604 ,fct.sprn_sprtnrsn_fk
605 ,fct.scat_spcatgry_fk
606 ,fct.ptyp_pertyp_fk
607 ,fct.prfm_perfband_fk
608 ,fct.pow_powband_fk
609 ,CASE WHEN msrs.zero_row_ind = 0
610 THEN msrs.time_month_snp_fk
611 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
612 END
613 ,CASE WHEN msrs.zero_row_ind = 0
614 THEN msrs.time_day_mnth_start_fk
615 ELSE msrs.time_day_mnth_end_fk + 1
616 END
617 ,CASE WHEN msrs.zero_row_ind = 0
618 THEN msrs.time_day_mnth_end_fk
619 ELSE ADD_MONTHS(msrs.time_day_mnth_start_fk, 2) - 1
620 END
621 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
622 THEN msrs.time_month_snp_fk
623 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
624 END) / 100, 0)
625 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
626 THEN msrs.time_month_snp_fk
627 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
628 END) / 1000, 0)
629 ,fct.cur_currency_fk
630 ,fct.employee_ind
631 ,fct.contingent_ind
632 ,msrs.last_month_in_qtr_ind
633 ,msrs.last_month_in_year_ind
634 ,fct.adt_pow_band;
635
636 -- Commit
637 COMMIT;
638
639 END process_month_summary_full;
640
641
642 -- ----------------------------------------------------------------------------
643 -- Processes base fact in incremental refresh mode
644 -- ----------------------------------------------------------------------------
645 PROCEDURE process_month_summary_incr(p_start_asg_id IN NUMBER,
646 p_end_asg_id IN NUMBER) IS
647
648 BEGIN
649
650 INSERT INTO hri_mds_wrkfc_mnth_ct
651 (wevt_evtypcmb_fk
652 ,asg_assgnmnt_fk
653 ,per_person_fk
654 ,per_person_mgr_fk
655 ,per_person_mgr_prv_fk
656 ,mgr_mngrsc_fk
657 ,mgr_mngrsc_prv_fk
658 ,org_organztn_fk
659 ,org_organztn_prv_fk
660 ,job_job_fk
661 ,job_job_prv_fk
662 ,grd_grade_fk
663 ,grd_grade_prv_fk
664 ,pos_position_fk
665 ,pos_position_prv_fk
666 ,geo_location_fk
667 ,geo_location_prv_fk
668 ,asgrsn_asgrsn_fk
669 ,sprn_sprtnrsn_fk
670 ,scat_spcatgry_fk
671 ,ptyp_pertyp_fk
672 ,prfm_perfband_fk
673 ,pow_powband_fk
674 ,time_month_snp_fk
675 ,time_day_mnth_start_fk
676 ,time_day_mnth_end_fk
677 ,time_quarter_fk
678 ,time_year_fk
679 ,cur_currency_fk
680 ,headcount_start
681 ,headcount_end
682 ,headcount_hire
683 ,headcount_term
684 ,headcount_sep_vol
685 ,headcount_sep_invol
686 ,headcount_prmtn
687 ,fte_start
688 ,fte_end
689 ,fte_hire
690 ,fte_term
691 ,fte_sep_vol
692 ,fte_sep_invol
693 ,fte_prmtn
694 ,count_pasg_end
695 ,count_pasg_hire
696 ,count_pasg_term
697 ,count_pasg_sep_vol
698 ,count_pasg_sep_invol
699 ,count_asg_end
700 ,count_asg_hire
701 ,count_asg_term
702 ,count_asg_sep_vol
703 ,count_asg_sep_invol
704 ,count_asg_prmtn
705 ,pow_days_on_end_date
706 ,pow_months_on_end_date
707 ,days_since_last_prmtn
708 ,months_since_last_prmtn
709 ,anl_slry_start
710 ,anl_slry_end
711 ,employee_ind
712 ,contingent_ind
713 ,last_month_in_qtr_ind
714 ,last_month_in_year_ind
715 ,adt_pow_band
716 ,creation_date
717 ,created_by
718 ,last_updated_by
719 ,last_update_login
720 ,last_update_date)
721 SELECT
722 to_number(null) wevt_evtypcmb_fk
723 ,fct.asg_assgnmnt_fk
724 ,fct.per_person_fk
725 ,fct.per_person_mgr_fk
726 ,fct.per_person_mgr_prv_fk
727 ,fct.mgr_mngrsc_fk
728 ,fct.mgr_mngrsc_prv_fk
729 ,fct.org_organztn_fk
730 ,fct.org_organztn_prv_fk
731 ,fct.job_job_fk
732 ,fct.job_job_prv_fk
733 ,fct.grd_grade_fk
734 ,fct.grd_grade_prv_fk
735 ,fct.pos_position_fk
736 ,fct.pos_position_prv_fk
737 ,fct.geo_location_fk
738 ,fct.geo_location_prv_fk
739 ,fct.asgrsn_asgrsn_fk
740 ,fct.sprn_sprtnrsn_fk
741 ,fct.scat_spcatgry_fk
742 ,fct.ptyp_pertyp_fk
743 ,fct.prfm_perfband_fk
744 ,fct.pow_powband_fk
745 ,CASE WHEN msrs.zero_row_ind = 0
746 THEN msrs.time_month_snp_fk
747 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
748 END time_month_snp_fk
749 ,CASE WHEN msrs.zero_row_ind = 0
750 THEN msrs.time_day_mnth_start_fk
751 ELSE msrs.time_day_mnth_end_fk + 1
752 END time_day_mnth_start_fk
753 ,CASE WHEN msrs.zero_row_ind = 0
754 THEN msrs.time_day_mnth_end_fk
755 ELSE ADD_MONTHS(msrs.time_day_mnth_start_fk, 2) - 1
756 END time_day_mnth_end_fk
757 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
758 THEN msrs.time_month_snp_fk
759 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
760 END) / 100, 0) time_quarter_end_fk
761 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
762 THEN msrs.time_month_snp_fk
763 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
764 END) / 1000, 0) time_year_end_fk
765 ,fct.cur_currency_fk
766 ,SUM(msrs.headcount_start)
767 ,SUM(msrs.headcount_end)
768 ,SUM(msrs.headcount_hire)
769 ,SUM(msrs.headcount_term)
770 ,SUM(msrs.headcount_sep_vol)
771 ,SUM(msrs.headcount_sep_invol)
772 ,SUM(msrs.headcount_prmtn)
773 ,SUM(msrs.fte_start)
774 ,SUM(msrs.fte_end)
775 ,SUM(msrs.fte_hire)
776 ,SUM(msrs.fte_term)
777 ,SUM(msrs.fte_sep_vol)
778 ,SUM(msrs.fte_sep_invol)
779 ,SUM(msrs.fte_prmtn)
780 ,SUM(msrs.count_pasg_end)
781 ,SUM(msrs.count_pasg_hire)
782 ,SUM(msrs.count_pasg_term)
783 ,SUM(msrs.count_pasg_sep_vol)
784 ,SUM(msrs.count_pasg_sep_invol)
785 ,SUM(msrs.count_asg_end)
786 ,SUM(msrs.count_asg_hire)
787 ,SUM(msrs.count_asg_term)
788 ,SUM(msrs.count_asg_sep_vol)
789 ,SUM(msrs.count_asg_sep_invol)
790 ,SUM(msrs.count_asg_prmtn)
791 ,SUM(msrs.pow_days_on_end_date)
792 ,SUM(msrs.pow_months_on_end_date)
793 ,SUM(msrs.days_since_last_prmtn)
794 ,SUM(msrs.months_since_last_prmtn)
795 ,SUM(msrs.anl_slry_start)
796 ,SUM(msrs.anl_slry_end)
797 ,fct.employee_ind
798 ,fct.contingent_ind
799 ,msrs.last_month_in_qtr_ind
800 ,msrs.last_month_in_year_ind
801 ,fct.adt_pow_band
802 ,g_sysdate
803 ,g_user
804 ,g_user
805 ,g_user
806 ,g_sysdate
807 FROM
808 (SELECT
809 wevt.asg_assgnmnt_fk
810 ,wevt.per_person_fk
811 ,wevt.per_person_mgr_fk
812 ,NVL(LAG(wevt.per_person_mgr_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
813 ORDER BY mnth.month_id)
814 ,-1) per_person_mgr_prv_fk
815 ,wevt.mgr_mngrsc_fk
816 ,NVL(LAG(wevt.mgr_mngrsc_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
817 ORDER BY mnth.month_id)
818 ,-1) mgr_mngrsc_prv_fk
819 ,wevt.org_organztn_fk
820 ,NVL(LAG(wevt.org_organztn_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
821 ORDER BY mnth.month_id)
822 ,-1) org_organztn_prv_fk
823 ,wevt.job_job_fk
824 ,NVL(LAG(wevt.job_job_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
825 ORDER BY mnth.month_id)
826 ,-1) job_job_prv_fk
827 ,wevt.grd_grade_fk
828 ,NVL(LAG(wevt.grd_grade_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
829 ORDER BY mnth.month_id)
830 ,-1) grd_grade_prv_fk
831 ,wevt.pos_position_fk
832 ,NVL(LAG(wevt.pos_position_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
833 ORDER BY mnth.month_id)
834 ,-1) pos_position_prv_fk
835 ,wevt.geo_location_fk
836 ,NVL(LAG(wevt.geo_location_fk, 1) OVER (PARTITION BY wevt.asg_assgnmnt_fk
837 ORDER BY mnth.month_id)
838 ,-1) geo_location_prv_fk
839 ,wevt.asgrsn_asgrsn_fk
840 ,wevt.sprn_sprtnrsn_fk
841 ,wevt.scat_spcatgry_fk
842 ,wevt.ptyp_pertyp_fk
843 ,wevt.prfm_perfband_fk
844 ,wevt.pow_powband_fk
845 ,mnth.month_id time_month_snp_fk
846 ,wevt.cur_currency_fk
847 ,wevt.employee_ind
848 ,wevt.contingent_ind
849 ,wevt.adt_pow_band
850 ,eq.erlst_evnt_effective_date adt_event_date
851 FROM
852 hri_eq_wrkfc_mnth eq
853 ,hri_mb_wrkfc_evt_ct wevt
854 ,fii_time_month mnth
855 WHERE eq.assignment_id = wevt.asg_assgnmnt_fk
856 AND mnth.end_date BETWEEN wevt.time_day_evt_fk
857 AND wevt.time_day_evt_end_fk
858 -- Snapshot previous value for zero record
859 AND mnth.end_date >= ADD_MONTHS(eq.erlst_evnt_effective_date, -2)
860 -- If assignment is ended, only snapshot the ended assignment once
861 AND ((wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0)
862 OR mnth.start_date <= wevt.time_day_evt_fk)
863 AND wevt.asg_assgnmnt_fk BETWEEN p_start_asg_id
864 AND p_end_asg_id
865 AND mnth.end_date >= g_dbi_start_date
866 AND mnth.start_date <= TRUNC(g_sysdate)
867 ) fct
868 ,(SELECT
869 mnth.month_id time_month_snp_fk
870 ,mnth.start_date time_day_mnth_start_fk
871 ,mnth.end_date time_day_mnth_end_fk
872 ,wevt.asg_assgnmnt_fk asg_assgnmnt_fk
873 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
874 AND wevt.time_day_evt_end_fk
875 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
876 THEN wevt.headcount
877 ELSE 0
878 END) headcount_start
879 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
880 AND wevt.time_day_evt_end_fk
881 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
882 THEN wevt.headcount
883 ELSE 0
884 END) headcount_end
885 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
886 AND mnth.end_date
887 THEN wevt.headcount_hire
888 ELSE 0
889 END) headcount_hire
890 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
891 AND mnth.end_date
892 THEN wevt.headcount_term
893 ELSE 0
894 END) headcount_term
895 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
896 AND mnth.end_date
897 THEN wevt.headcount_term * wevt.term_voluntary_ind
898 ELSE 0
899 END) headcount_sep_vol
900 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
901 AND mnth.end_date
902 THEN wevt.headcount_term * wevt.term_involuntary_ind
903 ELSE 0
904 END) headcount_sep_invol
905 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
906 AND mnth.end_date
907 THEN wevt.headcount * wevt.promotion_ind
908 ELSE 0
909 END) headcount_prmtn
910 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
911 AND wevt.time_day_evt_end_fk
912 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
913 THEN wevt.fte
914 ELSE 0
915 END) fte_start
916 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
917 AND wevt.time_day_evt_end_fk
918 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
919 THEN wevt.fte
920 ELSE 0
921 END) fte_end
922 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
923 AND mnth.end_date
924 THEN wevt.fte_hire
925 ELSE 0
926 END) fte_hire
927 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
928 AND mnth.end_date
929 THEN wevt.fte_term
930 ELSE 0
931 END) fte_term
932 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
933 AND mnth.end_date
934 THEN wevt.fte_term * wevt.term_voluntary_ind
935 ELSE 0
936 END) fte_sep_vol
937 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
938 AND mnth.end_date
939 THEN wevt.fte_term * wevt.term_involuntary_ind
940 ELSE 0
941 END) fte_sep_invol
942 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
943 AND mnth.end_date
944 THEN wevt.fte * wevt.promotion_ind
945 ELSE 0
946 END) fte_prmtn
947 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
948 AND wevt.time_day_evt_end_fk
949 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
950 THEN wevt.primary_ind
951 ELSE 0
952 END) count_pasg_end
953 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
954 AND mnth.end_date
955 THEN wevt.hire_or_start_ind * wevt.primary_ind
956 ELSE 0
957 END) count_pasg_hire
958 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
959 AND mnth.end_date
960 THEN wevt.term_or_end_ind * wevt.primary_ind
961 ELSE 0
962 END) count_pasg_term
963 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
964 AND mnth.end_date
965 THEN wevt.term_voluntary_ind * wevt.primary_ind
966 ELSE 0
967 END) count_pasg_sep_vol
968 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
969 AND mnth.end_date
970 THEN wevt.term_involuntary_ind * wevt.primary_ind
971 ELSE 0
972 END) count_pasg_sep_invol
973 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
974 AND wevt.time_day_evt_end_fk
975 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
976 THEN 1
977 ELSE 0
978 END) count_asg_end
979 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
980 AND mnth.end_date
981 THEN wevt.hire_or_start_ind
982 ELSE 0
983 END) count_asg_hire
984 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
985 AND mnth.end_date
986 THEN wevt.term_or_end_ind
987 ELSE 0
988 END) count_asg_term
989 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
990 AND mnth.end_date
991 THEN wevt.term_voluntary_ind
992 ELSE 0
993 END) count_asg_sep_vol
994 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
995 AND mnth.end_date
996 THEN wevt.term_involuntary_ind
997 ELSE 0
998 END) count_asg_sep_invol
999 ,SUM(CASE WHEN wevt.time_day_evt_fk BETWEEN mnth.start_date
1000 AND mnth.end_date
1001 THEN wevt.promotion_ind
1002 ELSE 0
1003 END) count_asg_prmtn
1004 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
1005 AND wevt.time_day_evt_end_fk
1006 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1007 THEN wevt.pow_days_on_event_date +
1008 (mnth.end_date - wevt.time_day_evt_fk)
1009 ELSE 0
1010 END) pow_days_on_end_date
1011 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
1012 AND wevt.time_day_evt_end_fk
1013 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1014 THEN MONTHS_BETWEEN
1015 (mnth.end_date,
1016 wevt.time_day_evt_fk - wevt.pow_days_on_event_date)
1017 ELSE 0
1018 END) pow_months_on_end_date
1019 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
1020 AND wevt.time_day_evt_end_fk
1021 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1022 THEN wevt.days_since_last_prmtn +
1023 (mnth.end_date - wevt.time_day_evt_fk)
1024 ELSE 0
1025 END) days_since_last_prmtn
1026 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
1027 AND wevt.time_day_evt_end_fk
1028 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1029 THEN MONTHS_BETWEEN
1030 (mnth.end_date,
1031 wevt.time_day_evt_fk - wevt.days_since_last_prmtn)
1032 ELSE 0
1033 END) months_since_last_prmtn
1034 ,SUM(CASE WHEN mnth.start_date BETWEEN wevt.time_day_evt_fk
1035 AND wevt.time_day_evt_end_fk
1036 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1037 THEN wevt.anl_slry
1038 ELSE 0
1039 END) anl_slry_start
1040 ,SUM(CASE WHEN mnth.end_date BETWEEN wevt.time_day_evt_fk
1041 AND wevt.time_day_evt_end_fk
1042 AND wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0
1043 THEN wevt.anl_slry
1044 ELSE 0
1045 END) anl_slry_end
1046 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Q'), 3) - 1
1047 THEN 1
1048 WHEN MAX(wevt.worker_term_ind) = 1 OR MAX(wevt.pre_sprtn_asgn_end_ind) = 1
1049 THEN 1
1050 WHEN TRUNC(g_sysdate) BETWEEN mnth.start_date AND mnth.end_date
1051 THEN 1
1052 ELSE 0
1053 END last_month_in_qtr_ind
1054 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Y'), 12) - 1
1055 THEN 1
1056 WHEN MAX(wevt.worker_term_ind) = 1 OR MAX(wevt.pre_sprtn_asgn_end_ind) = 1
1057 THEN 1
1058 WHEN TRUNC(g_sysdate) BETWEEN mnth.start_date AND mnth.end_date
1059 THEN 1
1060 ELSE 0
1061 END last_month_in_year_ind
1062 ,0 zero_row_ind
1063 FROM
1064 hri_eq_wrkfc_mnth eq
1065 ,hri_mb_wrkfc_evt_ct wevt
1066 ,fii_time_month mnth
1067 WHERE eq.assignment_id = wevt.asg_assgnmnt_fk
1068 AND mnth.end_date >= ADD_MONTHS(eq.erlst_evnt_effective_date, -1)
1069 AND wevt.time_day_evt_fk <= mnth.end_date
1070 AND mnth.start_date <= wevt.time_day_evt_end_fk
1071 -- If assignment is ended, only snapshot the ended assignment once
1072 AND ((wevt.worker_term_ind = 0 AND wevt.pre_sprtn_asgn_end_ind = 0)
1073 OR mnth.start_date <= wevt.time_day_evt_fk)
1074 AND eq.assignment_id BETWEEN p_start_asg_id
1075 AND p_end_asg_id
1076 AND mnth.end_date >= g_dbi_start_date
1077 AND mnth.start_date <= TRUNC(g_sysdate)
1078 GROUP BY
1079 mnth.month_id
1080 ,mnth.start_date
1081 ,mnth.end_date
1082 ,wevt.asg_assgnmnt_fk
1083 UNION ALL
1084 SELECT
1085 mnth.month_id time_month_snp_fk
1086 ,mnth.start_date time_day_mnth_start_fk
1087 ,mnth.end_date time_day_mnth_end_fk
1088 ,wevt.asg_assgnmnt_fk asg_assgnmnt_fk
1089 ,0 headcount_start
1090 ,0 headcount_end
1091 ,0 headcount_hire
1092 ,0 headcount_term
1093 ,0 headcount_sep_vol
1094 ,0 headcount_sep_invol
1095 ,0 headcount_prmtn
1096 ,0 fte_start
1097 ,0 fte_end
1098 ,0 fte_hire
1099 ,0 fte_term
1100 ,0 fte_sep_vol
1101 ,0 fte_sep_invol
1102 ,0 fte_prmtn
1103 ,0 count_pasg_end
1104 ,0 count_pasg_hire
1105 ,0 count_pasg_term
1106 ,0 count_pasg_sep_vol
1107 ,0 count_pasg_sep_invol
1108 ,0 count_asg_end
1109 ,0 count_asg_hire
1110 ,0 count_asg_term
1111 ,0 count_asg_sep_vol
1112 ,0 count_asg_sep_invol
1113 ,0 count_asg_prmtn
1114 ,0 pow_days_on_end_date
1115 ,0 pow_months_on_end_date
1116 ,to_number(null) days_since_last_prmtn
1117 ,to_number(null) months_since_last_prmtn
1118 ,0 anl_slry_start
1119 ,0 anl_slry_end
1120 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Q'), 2) - 1
1121 THEN 1
1122 WHEN wevt.worker_term_nxt_ind = 1 OR wevt.pre_sprtn_asgn_end_nxt_ind = 1
1123 THEN 1
1124 WHEN ADD_MONTHS(TRUNC(g_sysdate), -1) BETWEEN mnth.start_date AND mnth.end_date
1125 THEN 1
1126 ELSE 0
1127 END last_month_in_qtr_ind
1128 ,CASE WHEN mnth.end_date = ADD_MONTHS(TRUNC(mnth.end_date, 'Y'), 11) - 1
1129 THEN 1
1130 WHEN wevt.worker_term_nxt_ind = 1 OR wevt.pre_sprtn_asgn_end_nxt_ind = 1
1131 THEN 1
1132 WHEN ADD_MONTHS(TRUNC(g_sysdate), -1) BETWEEN mnth.start_date AND mnth.end_date
1133 THEN 1
1134 ELSE 0
1135 END last_month_in_year_ind
1136 ,1 zero_row_ind
1137 FROM
1138 hri_eq_wrkfc_mnth eq
1139 ,hri_mb_wrkfc_evt_ct wevt
1140 ,fii_time_month mnth
1141 WHERE eq.assignment_id = wevt.asg_assgnmnt_fk
1142 AND mnth.end_date BETWEEN wevt.time_day_evt_fk
1143 AND wevt.time_day_evt_end_fk
1144 -- Include month of change and the previous month (to zero out)
1145 AND mnth.end_date >= ADD_MONTHS(eq.erlst_evnt_effective_date, -1)
1146 -- If assignment is ended, no need to snapshot
1147 AND wevt.worker_term_ind = 0
1148 AND wevt.pre_sprtn_asgn_end_ind = 0
1149 AND eq.assignment_id BETWEEN p_start_asg_id
1150 AND p_end_asg_id
1151 AND mnth.end_date >= g_dbi_start_date
1152 AND mnth.end_date < TRUNC(g_sysdate)
1153 ) msrs
1154 WHERE msrs.asg_assgnmnt_fk = fct.asg_assgnmnt_fk
1155 AND msrs.time_month_snp_fk = fct.time_month_snp_fk
1156 -- Restrict to snapshots from event date onwards
1157 AND DECODE(msrs.zero_row_ind,
1158 0, msrs.time_day_mnth_end_fk,
1159 ADD_MONTHS(msrs.time_day_mnth_start_fk, 2) - 1) >= fct.adt_event_date
1160 GROUP BY
1161 fct.asg_assgnmnt_fk
1162 ,fct.per_person_fk
1163 ,fct.per_person_mgr_fk
1164 ,fct.per_person_mgr_prv_fk
1165 ,fct.mgr_mngrsc_fk
1166 ,fct.mgr_mngrsc_prv_fk
1167 ,fct.org_organztn_fk
1168 ,fct.org_organztn_prv_fk
1169 ,fct.job_job_fk
1170 ,fct.job_job_prv_fk
1171 ,fct.grd_grade_fk
1172 ,fct.grd_grade_prv_fk
1173 ,fct.pos_position_fk
1174 ,fct.pos_position_prv_fk
1175 ,fct.geo_location_fk
1176 ,fct.geo_location_prv_fk
1177 ,fct.asgrsn_asgrsn_fk
1178 ,fct.sprn_sprtnrsn_fk
1179 ,fct.scat_spcatgry_fk
1180 ,fct.ptyp_pertyp_fk
1181 ,fct.prfm_perfband_fk
1182 ,fct.pow_powband_fk
1183 ,CASE WHEN msrs.zero_row_ind = 0
1184 THEN msrs.time_month_snp_fk
1185 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
1186 END
1187 ,CASE WHEN msrs.zero_row_ind = 0
1188 THEN msrs.time_day_mnth_start_fk
1189 ELSE msrs.time_day_mnth_end_fk + 1
1190 END
1191 ,CASE WHEN msrs.zero_row_ind = 0
1192 THEN msrs.time_day_mnth_end_fk
1193 ELSE ADD_MONTHS(msrs.time_day_mnth_start_fk, 2) - 1
1194 END
1195 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
1196 THEN msrs.time_month_snp_fk
1197 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
1198 END) / 100, 0)
1199 ,TRUNC((CASE WHEN msrs.zero_row_ind = 0
1200 THEN msrs.time_month_snp_fk
1201 ELSE to_number(to_char(msrs.time_day_mnth_end_fk + 1, 'YYYYQMM'))
1202 END) / 1000, 0)
1203 ,fct.cur_currency_fk
1204 ,fct.employee_ind
1205 ,fct.contingent_ind
1206 ,msrs.last_month_in_qtr_ind
1207 ,msrs.last_month_in_year_ind
1208 ,fct.adt_pow_band;
1209
1210 -- Commit
1211 COMMIT;
1212
1213 END process_month_summary_incr;
1214
1215
1216 -- ----------------------------------------------------------------------------
1217 -- Deletes records from month summary for incremental maintenance
1218 -- ----------------------------------------------------------------------------
1219 PROCEDURE delete_month_summary_incr(p_start_asg_id IN NUMBER,
1220 p_end_asg_id IN NUMBER) IS
1221
1222 BEGIN
1223
1224 DELETE FROM hri_mds_wrkfc_mnth_ct snp
1225 WHERE snp.rowid IN
1226 (SELECT /*+ ORDERED */
1227 snp2.rowid
1228 FROM
1229 hri_eq_wrkfc_mnth eq
1230 ,hri_mds_wrkfc_mnth_ct snp2
1231 WHERE eq.assignment_id = snp2.asg_assgnmnt_fk
1232 AND eq.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
1233 AND snp2.time_month_snp_fk >=
1234 to_number(to_char(eq.erlst_evnt_effective_date, 'YYYYQMM')));
1235
1236 END delete_month_summary_incr;
1237
1238
1239 -- ----------------------------------------------------------------------------
1240 -- Processes base fact in full refresh mode
1241 -- ----------------------------------------------------------------------------
1242 PROCEDURE process_base_fact_full(p_start_asg_id IN NUMBER,
1243 p_end_asg_id IN NUMBER) IS
1244
1245 BEGIN
1246
1247 INSERT INTO hri_mb_wrkfc_evt_ct
1248 (wevt_evtypcmb_fk
1249 ,asg_assgnmnt_fk
1250 ,per_person_fk
1251 ,per_person_mgr_fk
1252 ,per_person_mgr_prv_fk
1253 ,mgr_mngrsc_fk
1254 ,mgr_mngrsc_prv_fk
1255 ,org_organztn_fk
1256 ,org_organztn_prv_fk
1257 ,job_job_fk
1258 ,job_job_prv_fk
1259 ,grd_grade_fk
1260 ,grd_grade_prv_fk
1261 ,pos_position_fk
1262 ,pos_position_prv_fk
1263 ,geo_location_fk
1264 ,geo_location_prv_fk
1265 ,asgrsn_asgrsn_fk
1266 ,sprn_sprtnrsn_fk
1267 ,scat_spcatgry_fk
1268 ,ptyp_pertyp_fk
1269 ,prfm_perfband_fk
1270 ,pow_powband_fk
1271 ,time_day_evt_fk
1272 ,time_day_evt_end_fk
1273 ,cur_currency_fk
1274 ,headcount
1275 ,headcount_prv
1276 ,headcount_hire
1277 ,headcount_term
1278 ,fte
1279 ,fte_prv
1280 ,fte_hire
1281 ,fte_term
1282 ,pow_days_on_event_date
1283 ,pow_months_on_event_date
1284 ,days_since_last_prmtn
1285 ,months_since_last_prmtn
1286 ,anl_slry
1287 ,anl_slry_prv
1288 ,assignment_change_ind
1289 ,primary_ind
1290 ,headcount_gain_ind
1291 ,headcount_loss_ind
1292 ,headcount_change_ind
1293 ,fte_gain_ind
1294 ,fte_loss_ind
1295 ,fte_change_ind
1296 ,contingent_ind
1297 ,employee_ind
1298 ,grade_change_ind
1299 ,job_change_ind
1300 ,position_change_ind
1301 ,location_change_ind
1302 ,organization_change_ind
1303 ,supervisor_change_ind
1304 ,worker_hire_ind
1305 ,term_voluntary_ind
1306 ,term_involuntary_ind
1307 ,worker_term_ind
1308 ,hire_or_start_ind
1309 ,term_or_end_ind
1310 ,start_asg_sspnsn_ind
1311 ,end_asg_sspnsn_ind
1312 ,post_hire_asgn_start_ind
1313 ,pre_sprtn_asgn_end_ind
1314 ,prsntyp_change_ind
1315 ,mgrh_node_change_ind
1316 ,promotion_ind
1317 ,worker_term_nxt_ind
1318 ,pre_sprtn_asgn_end_nxt_ind
1319 ,adt_event_id
1320 ,adt_assignment_id
1321 ,adt_asg_effctv_start_date
1322 ,adt_asg_effctv_end_date
1323 ,adt_business_group_id
1324 ,adt_perf_review_id
1325 ,adt_period_of_service_id
1326 ,adt_period_of_placement_id
1327 ,adt_pow_band
1328 ,creation_date
1329 ,created_by
1330 ,last_updated_by
1331 ,last_update_login
1332 ,last_update_date)
1333 SELECT
1334 CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1335 wevt.time_day_evt_fk)
1336 THEN -1
1337 ELSE wevt.wevt_evtypcmb_fk
1338 END wevt_evtypcmb_fk
1339 ,wevt.asg_assgnmnt_fk
1340 ,wevt.per_person_fk
1341 ,CASE WHEN wevt.termination_ind = 0
1342 THEN wevt.per_person_mgr_fk
1343 ELSE wevt.per_person_mgr_prv_fk
1344 END per_person_mgr_fk
1345 ,wevt.per_person_mgr_prv_fk
1346 ,NVL(chn.mgrs_mngrsc_pk, -1) mgr_mngrsc_fk
1347 ,NVL(chn_prv.mgrs_mngrsc_pk, -1) mgr_mngrsc_fk
1348 ,CASE WHEN wevt.termination_ind = 0
1349 THEN wevt.org_organztn_fk
1350 ELSE wevt.org_organztn_prv_fk
1351 END org_organztn_fk
1352 ,wevt.org_organztn_prv_fk
1353 ,CASE WHEN wevt.termination_ind = 0
1354 THEN wevt.job_job_fk
1355 ELSE wevt.job_job_prv_fk
1356 END job_job_fk
1357 ,wevt.job_job_prv_fk
1358 ,CASE WHEN wevt.termination_ind = 0
1359 THEN wevt.grd_grade_fk
1360 ELSE wevt.grd_grade_prv_fk
1361 END grd_grade_fk
1362 ,wevt.grd_grade_prv_fk
1363 ,CASE WHEN wevt.termination_ind = 0
1364 THEN wevt.pos_position_fk
1365 ELSE wevt.pos_position_prv_fk
1366 END pos_position_fk
1367 ,wevt.pos_position_prv_fk
1368 ,CASE WHEN wevt.termination_ind = 0
1369 THEN wevt.geo_location_fk
1370 ELSE wevt.geo_location_prv_fk
1371 END geo_location_fk
1372 ,wevt.geo_location_prv_fk
1373 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1374 wevt.time_day_evt_end_fk)
1375 THEN 'NA_EDW'
1376 ELSE wevt.asgrsn_asgrsn_fk
1377 END asgrsn_asgrsn_fk
1378 ,CASE WHEN wevt.employee_ind = 1 AND
1379 wevt.sprn_sprtnrsn_fk <> 'NA_EDW'
1380 THEN 'LEAV_REAS' || '-' || wevt.sprn_sprtnrsn_fk
1381 WHEN wevt.contingent_ind = 1 AND
1382 wevt.sprn_sprtnrsn_fk <> 'NA_EDW'
1383 THEN 'HR_CWK_TERMINATION_REASONS' || '-' || wevt.sprn_sprtnrsn_fk
1384 ELSE 'NA_EDW'
1385 END sprn_sprtnrsn_fk
1386 ,wevt.scat_spcatgry_fk
1387 ,CASE WHEN wevt.termination_ind = 0
1388 THEN wevt.ptyp_pertyp_fk
1389 ELSE wevt.ptyp_pertyp_prv_fk
1390 END ptyp_pertyp_fk
1391 ,CASE WHEN wevt.termination_ind = 0
1392 THEN wevt.prfm_perfband_fk
1393 ELSE wevt.prfm_perfband_prv_fk
1394 END prfm_perfband_fk
1395 ,CASE WHEN wevt.termination_ind = 0
1396 THEN wevt.pow_powband_fk
1397 ELSE wevt.pow_powband_prv_fk
1398 END pow_powband_fk
1399 ,GREATEST(wevt.time_day_evt_fk,
1400 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk))
1401 time_day_evt_fk
1402 ,LEAST(wevt.time_day_evt_end_fk,
1403 NVL(chn.mgrs_date_end, wevt.time_day_evt_end_fk))
1404 time_day_evt_end_fk
1405 ,CASE WHEN wevt.termination_ind = 0
1406 THEN wevt.cur_currency_fk
1407 ELSE wevt.cur_currency_prv_fk
1408 END cur_currency_fk
1409 ,NVL(wevt.headcount, 0) headcount
1410 ,NVL(wevt.headcount_prv, 0) headcount_prv
1411 ,CASE WHEN wevt.time_day_evt_fk >= NVL(chn.mgrs_date_start,
1412 wevt.time_day_evt_fk) AND
1413 wevt.worker_hire_ind = 1
1414 THEN wevt.headcount
1415 ELSE 0
1416 END headcount_hire
1417 ,CASE WHEN wevt.worker_term_ind = 1 OR
1418 wevt.pre_sprtn_asgn_end_ind = 1
1419 THEN wevt.headcount_prv
1420 ELSE 0
1421 END headcount_term
1422 ,NVL(wevt.fte, 0) fte
1423 ,NVL(wevt.fte_prv, 0) fte_prv
1424 ,CASE WHEN wevt.time_day_evt_fk >= NVL(chn.mgrs_date_start,
1425 wevt.time_day_evt_fk) AND
1426 wevt.worker_hire_ind = 1
1427 THEN wevt.fte
1428 ELSE 0
1429 END fte_hire
1430 ,CASE WHEN wevt.worker_term_ind = 1 OR
1431 wevt.pre_sprtn_asgn_end_ind = 1
1432 THEN wevt.fte_prv
1433 ELSE 0
1434 END fte_term
1435 ,GREATEST(wevt.time_day_evt_fk,
1436 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk))
1437 - wevt.time_day_pow_start_fk
1438 pow_days_on_event_date
1439 ,MONTHS_BETWEEN
1440 (GREATEST(wevt.time_day_evt_fk,
1441 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk)),
1442 wevt.time_day_pow_start_fk) pow_months_on_event_date
1443 ,wevt.days_since_last_prmtn
1444 ,wevt.months_since_last_prmtn
1445 ,NVL(wevt.anl_slry, 0) anl_slry
1446 ,NVL(wevt.anl_slry_prv, 0) anl_slry_prv
1447 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1448 wevt.time_day_evt_fk)
1449 THEN 0
1450 ELSE wevt.assignment_change_ind
1451 END assignment_change_ind
1452 ,wevt.primary_ind
1453 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1454 wevt.time_day_evt_fk)
1455 THEN 0
1456 ELSE wevt.headcount_gain_ind
1457 END headcount_gain_ind
1458 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1459 wevt.time_day_evt_fk)
1460 THEN 0
1461 ELSE wevt.headcount_loss_ind
1462 END headcount_loss_ind
1463 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1464 wevt.time_day_evt_fk)
1465 THEN 0
1466 ELSE wevt.headcount_loss_ind + wevt.headcount_gain_ind
1467 END headcount_change_ind
1468 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1469 wevt.time_day_evt_fk)
1470 THEN 0
1471 ELSE wevt.fte_gain_ind
1472 END fte_gain_ind
1473 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1474 wevt.time_day_evt_fk)
1475 THEN 0
1476 ELSE wevt.fte_loss_ind
1477 END fte_loss_ind
1478 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1479 wevt.time_day_evt_fk)
1480 THEN 0
1481 ELSE wevt.fte_loss_ind + wevt.fte_gain_ind
1482 END fte_change_ind
1483 ,wevt.contingent_ind
1484 ,wevt.employee_ind
1485 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1486 wevt.time_day_evt_fk)
1487 THEN 0
1488 ELSE wevt.grade_change_ind
1489 END grade_change_ind
1490 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1491 wevt.time_day_evt_fk)
1492 THEN 0
1493 ELSE wevt.job_change_ind
1494 END job_change_ind
1495 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1496 wevt.time_day_evt_fk)
1497 THEN 0
1498 ELSE wevt.position_change_ind
1499 END position_change_ind
1500 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1501 wevt.time_day_evt_fk)
1502 THEN 0
1503 ELSE wevt.location_change_ind
1504 END location_change_ind
1505 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1506 wevt.time_day_evt_fk)
1507 THEN 0
1508 ELSE wevt.organization_change_ind
1509 END organization_change_ind
1510 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1511 wevt.time_day_evt_fk)
1512 THEN 0
1513 ELSE wevt.supervisor_change_ind
1514 END supervisor_change_ind
1515 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1516 wevt.time_day_evt_fk)
1517 THEN 0
1518 ELSE wevt.worker_hire_ind
1519 END worker_hire_ind
1520 ,wevt.term_voluntary_ind
1521 ,wevt.term_involuntary_ind
1522 ,wevt.worker_term_ind
1523 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1524 wevt.time_day_evt_fk)
1525 THEN 0
1526 ELSE wevt.post_hire_asgn_start_ind + wevt.worker_hire_ind
1527 END hire_or_start_ind
1528 ,wevt.worker_term_ind + wevt.pre_sprtn_asgn_end_ind
1529 term_or_end_ind
1530 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1531 wevt.time_day_evt_fk)
1532 THEN 0
1533 ELSE wevt.start_asg_sspnsn_ind
1534 END start_asg_sspnsn_ind
1535 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1536 wevt.time_day_evt_fk)
1537 THEN 0
1538 ELSE wevt.end_asg_sspnsn_ind
1539 END end_asg_sspnsn_ind
1540 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1541 wevt.time_day_evt_fk)
1542 THEN 0
1543 ELSE wevt.post_hire_asgn_start_ind
1544 END post_hire_asgn_start_ind
1545 ,wevt.pre_sprtn_asgn_end_ind
1546 -- ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1547 -- wevt.time_day_evt_fk)
1548 -- THEN 0
1549 -- ELSE wevt.prsntyp_change_ind
1550 -- END prsntyp_change_ind
1551 ,to_number(null) prsntyp_change_ind
1552 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1553 wevt.time_day_evt_fk)
1554 THEN 1
1555 ELSE 0
1556 END mgrh_node_change_ind
1557 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1558 wevt.time_day_evt_fk)
1559 THEN 0
1560 ELSE wevt.promotion_ind
1561 END promotion_ind
1562 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1563 wevt.time_day_evt_fk)
1564 THEN 0
1565 ELSE wevt.worker_term_nxt_ind
1566 END worker_term_nxt_ind
1567 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1568 wevt.time_day_evt_fk)
1569 THEN 0
1570 ELSE wevt.pre_sprtn_asgn_end_nxt_ind
1571 END pre_sprtn_asgn_end_nxt_ind
1572 ,wevt.adt_event_id adt_event_id
1573 ,wevt.asg_assgnmnt_fk adt_assignment_id
1574 ,wevt.time_day_evt_fk adt_asg_effctv_start_date
1575 ,wevt.time_day_evt_end_fk adt_asg_effctv_end_date
1576 ,wevt.adt_business_group_id adt_business_group_id
1577 ,wevt.adt_perf_review_id adt_perf_review_id
1578 ,to_number(null) adt_period_of_service_id
1579 ,to_number(null) adt_period_of_placement_id
1580 ,pow.band_sequence adt_pow_band
1581 ,g_sysdate
1582 ,g_user
1583 ,g_user
1584 ,g_user
1585 ,g_sysdate
1586 FROM
1587 hri_cs_mngrsc_ct chn
1588 ,hri_cs_mngrsc_ct chn_prv
1589 ,hri_cs_pow_band_ct pow
1590 ,(SELECT /*+ NO_MERGE */
1591 hri_opl_wrkfc_evt_type.get_evtypcmb_fk
1592 (assignment_change_ind
1593 ,salary_change_ind
1594 ,perf_rating_change_ind
1595 ,perf_band_change_ind
1596 ,pow_band_change_ind
1597 ,headcount_gain_ind
1598 ,headcount_loss_ind
1599 ,fte_gain_ind
1600 ,fte_loss_ind
1601 ,grade_change_ind
1602 ,job_change_ind
1603 ,position_change_ind
1604 ,location_change_ind
1605 ,organization_change_ind
1606 ,supervisor_change_ind
1607 ,worker_hire_ind
1608 ,post_hire_asgn_start_ind
1609 ,pre_sprtn_asgn_end_ind
1610 ,term_voluntary_ind
1611 ,term_involuntary_ind
1612 ,worker_term_ind
1613 ,start_asg_sspnsn_ind
1614 ,end_asg_sspnsn_ind
1615 ,promotion_ind) wevt_evtypcmb_fk
1616 ,evt.assignment_id asg_assgnmnt_fk
1617 ,evt.person_id per_person_fk
1618 ,evt.supervisor_id per_person_mgr_fk
1619 ,evt.supervisor_prv_id per_person_mgr_prv_fk
1620 ,CASE WHEN evt.worker_term_ind = 0 AND
1621 evt.pre_sprtn_asgn_end_ind = 0
1622 THEN evt.supervisor_id
1623 ELSE evt.supervisor_prv_id
1624 END psn_chain_mgr_fk
1625 ,CASE WHEN evt.worker_term_ind = 0 AND
1626 evt.pre_sprtn_asgn_end_ind = 0
1627 THEN evt.effective_change_date
1628 ELSE evt.effective_change_date - 1
1629 END psn_chain_time_fk
1630 ,evt.organization_id org_organztn_fk
1631 ,evt.organization_prv_id org_organztn_prv_fk
1632 ,evt.job_id job_job_fk
1633 ,evt.job_prv_id job_job_prv_fk
1634 ,evt.grade_id grd_grade_fk
1635 ,evt.grade_prv_id grd_grade_prv_fk
1636 ,evt.position_id pos_position_fk
1637 ,evt.position_prv_id pos_position_prv_fk
1638 ,evt.location_id geo_location_fk
1639 ,evt.location_prv_id geo_location_prv_fk
1640 ,evt.change_reason_code asgrsn_asgrsn_fk
1641 ,evt.leaving_reason_code sprn_sprtnrsn_fk
1642 ,evt.separation_category scat_spcatgry_fk
1643 ,evt.prsntyp_sk_fk ptyp_pertyp_fk
1644 ,LAG(evt.prsntyp_sk_fk, 1) OVER (PARTITION BY evt.assignment_id
1645 ORDER BY evt.effective_change_date)
1646 ptyp_pertyp_prv_fk
1647 ,evt.perf_band prfm_perfband_fk
1648 ,evt.perf_band_prv prfm_perfband_prv_fk
1649 ,evt.pow_band_sk_fk pow_powband_fk
1650 ,evt.pow_band_prv_sk_fk pow_powband_prv_fk
1651 ,evt.effective_change_date time_day_evt_fk
1652 ,evt.effective_change_end_date time_day_evt_end_fk
1653 ,evt.pow_start_date_adj time_day_pow_start_fk
1654 ,evt.anl_slry_currency cur_currency_fk
1655 ,evt.anl_slry_currency_prv cur_currency_prv_fk
1656 ,evt.headcount headcount
1657 ,evt.headcount_prv headcount_prv
1658 ,evt.fte fte
1659 ,evt.fte_prv fte_prv
1660 ,evt.pow_days_on_event_date pow_days_on_event_date
1661 ,evt.pow_months_on_event_date pow_months_on_event_date
1662 ,evt.days_since_last_prmtn
1663 ,evt.months_since_last_prmtn
1664 ,evt.anl_slry anl_slry
1665 ,evt.anl_slry_prv anl_slry_prv
1666 ,evt.assignment_change_ind assignment_change_ind
1667 ,CASE WHEN evt.primary_flag = 'Y'
1668 THEN 1
1669 ELSE 0
1670 END primary_ind
1671 ,evt.headcount_gain_ind headcount_gain_ind
1672 ,evt.headcount_loss_ind headcount_loss_ind
1673 ,evt.fte_gain_ind fte_gain_ind
1674 ,evt.fte_loss_ind fte_loss_ind
1675 ,CASE WHEN evt.asg_type_code = 'C'
1676 THEN 1
1677 ELSE 0
1678 END contingent_ind
1679 ,CASE WHEN evt.asg_type_code = 'E'
1680 THEN 1
1681 ELSE 0
1682 END employee_ind
1683 ,evt.grade_change_ind grade_change_ind
1684 ,evt.job_change_ind job_change_ind
1685 ,evt.position_change_ind position_change_ind
1686 ,evt.location_change_ind location_change_ind
1687 ,evt.organization_change_ind organization_change_ind
1688 ,evt.supervisor_change_ind supervisor_change_ind
1689 ,evt.worker_hire_ind worker_hire_ind
1690 ,evt.term_voluntary_ind term_voluntary_ind
1691 ,evt.term_involuntary_ind term_involuntary_ind
1692 ,evt.worker_term_ind worker_term_ind
1693 ,evt.start_asg_sspnsn_ind start_asg_sspnsn_ind
1694 ,evt.end_asg_sspnsn_ind end_asg_sspnsn_ind
1695 ,evt.post_hire_asgn_start_ind post_hire_asgn_start_ind
1696 ,evt.pre_sprtn_asgn_end_ind pre_sprtn_asgn_end_ind
1697 ,to_number(null) prsntyp_change_ind
1698 ,CASE WHEN evt.worker_term_ind = 0 AND
1699 evt.pre_sprtn_asgn_end_ind = 0
1700 THEN 0
1701 ELSE 1
1702 END termination_ind
1703 ,evt.promotion_ind promotion_ind
1704 ,evt.worker_term_nxt_ind worker_term_nxt_ind
1705 ,evt.pre_sprtn_asgn_end_nxt_ind pre_sprtn_asgn_end_nxt_ind
1706 ,evt.event_id adt_event_id
1707 ,evt.business_group_id adt_business_group_id
1708 ,evt.performance_review_id adt_perf_review_id
1709 FROM
1710 hri_mb_asgn_events_ct evt
1711 WHERE evt.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
1712 AND evt.summarization_rqd_ind = 1
1713 ) wevt
1714 WHERE wevt.pow_powband_fk = pow.pow_band_sk_pk
1715 AND chn.mgrs_person_fk (+) = wevt.psn_chain_mgr_fk
1716 AND chn.mgrs_date_start (+) <= wevt.time_day_evt_end_fk
1717 AND chn.mgrs_date_end (+) >= wevt.psn_chain_time_fk
1718 AND chn.mgrs_date_start (+) <= DECODE(wevt.termination_ind,
1719 0, wevt.time_day_evt_end_fk,
1720 wevt.psn_chain_time_fk)
1721 AND chn_prv.mgrs_person_fk (+) = wevt.per_person_mgr_prv_fk
1722 AND wevt.time_day_evt_fk - 1 BETWEEN chn_prv.mgrs_date_start (+)
1723 AND chn_prv.mgrs_date_end (+);
1724
1725 COMMIT;
1726
1727 END process_base_fact_full;
1728
1729
1730 -- ----------------------------------------------------------------------------
1731 -- Processes base fact in incremental refresh mode
1732 -- ----------------------------------------------------------------------------
1733 PROCEDURE process_base_fact_incr(p_start_asg_id IN NUMBER,
1734 p_end_asg_id IN NUMBER) IS
1735
1736 BEGIN
1737
1738 INSERT INTO hri_mb_wrkfc_evt_ct
1739 (wevt_evtypcmb_fk
1740 ,asg_assgnmnt_fk
1741 ,per_person_fk
1742 ,per_person_mgr_fk
1743 ,per_person_mgr_prv_fk
1744 ,mgr_mngrsc_fk
1745 ,mgr_mngrsc_prv_fk
1746 ,org_organztn_fk
1747 ,org_organztn_prv_fk
1748 ,job_job_fk
1749 ,job_job_prv_fk
1750 ,grd_grade_fk
1751 ,grd_grade_prv_fk
1752 ,pos_position_fk
1753 ,pos_position_prv_fk
1754 ,geo_location_fk
1755 ,geo_location_prv_fk
1756 ,asgrsn_asgrsn_fk
1757 ,sprn_sprtnrsn_fk
1758 ,scat_spcatgry_fk
1759 ,ptyp_pertyp_fk
1760 ,prfm_perfband_fk
1761 ,pow_powband_fk
1762 ,time_day_evt_fk
1763 ,time_day_evt_end_fk
1764 ,cur_currency_fk
1765 ,headcount
1766 ,headcount_prv
1767 ,headcount_hire
1768 ,headcount_term
1769 ,fte
1770 ,fte_prv
1771 ,fte_hire
1772 ,fte_term
1773 ,pow_days_on_event_date
1774 ,pow_months_on_event_date
1775 ,days_since_last_prmtn
1776 ,months_since_last_prmtn
1777 ,anl_slry
1778 ,anl_slry_prv
1779 ,assignment_change_ind
1780 ,primary_ind
1781 ,headcount_gain_ind
1782 ,headcount_loss_ind
1783 ,headcount_change_ind
1784 ,fte_gain_ind
1785 ,fte_loss_ind
1786 ,fte_change_ind
1787 ,contingent_ind
1788 ,employee_ind
1789 ,grade_change_ind
1790 ,job_change_ind
1791 ,position_change_ind
1792 ,location_change_ind
1793 ,organization_change_ind
1794 ,supervisor_change_ind
1795 ,worker_hire_ind
1796 ,term_voluntary_ind
1797 ,term_involuntary_ind
1798 ,worker_term_ind
1799 ,hire_or_start_ind
1800 ,term_or_end_ind
1801 ,start_asg_sspnsn_ind
1802 ,end_asg_sspnsn_ind
1803 ,post_hire_asgn_start_ind
1804 ,pre_sprtn_asgn_end_ind
1805 ,prsntyp_change_ind
1806 ,mgrh_node_change_ind
1807 ,promotion_ind
1808 ,worker_term_nxt_ind
1809 ,pre_sprtn_asgn_end_nxt_ind
1810 ,adt_event_id
1811 ,adt_assignment_id
1812 ,adt_asg_effctv_start_date
1813 ,adt_asg_effctv_end_date
1814 ,adt_business_group_id
1815 ,adt_perf_review_id
1816 ,adt_period_of_service_id
1817 ,adt_period_of_placement_id
1818 ,adt_pow_band
1819 ,creation_date
1820 ,created_by
1821 ,last_updated_by
1822 ,last_update_login
1823 ,last_update_date)
1824 SELECT
1825 CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1826 wevt.time_day_evt_fk)
1827 THEN -1
1828 ELSE wevt.wevt_evtypcmb_fk
1829 END wevt_evtypcmb_fk
1830 ,wevt.asg_assgnmnt_fk
1831 ,wevt.per_person_fk
1832 ,CASE WHEN wevt.termination_ind = 0
1833 THEN wevt.per_person_mgr_fk
1834 ELSE wevt.per_person_mgr_prv_fk
1835 END per_person_mgr_fk
1836 ,wevt.per_person_mgr_prv_fk
1837 ,NVL(chn.mgrs_mngrsc_pk, -1) mgr_mngrsc_fk
1838 ,NVL(chn_prv.mgrs_mngrsc_pk, -1) mgr_mngrsc_fk
1839 ,CASE WHEN wevt.termination_ind = 0
1840 THEN wevt.org_organztn_fk
1841 ELSE wevt.org_organztn_prv_fk
1842 END org_organztn_fk
1843 ,wevt.org_organztn_prv_fk
1844 ,CASE WHEN wevt.termination_ind = 0
1845 THEN wevt.job_job_fk
1846 ELSE wevt.job_job_prv_fk
1847 END job_job_fk
1848 ,wevt.job_job_prv_fk
1849 ,CASE WHEN wevt.termination_ind = 0
1850 THEN wevt.grd_grade_fk
1851 ELSE wevt.grd_grade_prv_fk
1852 END grd_grade_fk
1853 ,wevt.grd_grade_prv_fk
1854 ,CASE WHEN wevt.termination_ind = 0
1855 THEN wevt.pos_position_fk
1856 ELSE wevt.pos_position_prv_fk
1857 END pos_position_fk
1858 ,wevt.pos_position_prv_fk
1859 ,CASE WHEN wevt.termination_ind = 0
1860 THEN wevt.geo_location_fk
1861 ELSE wevt.geo_location_prv_fk
1862 END geo_location_fk
1863 ,wevt.geo_location_prv_fk
1864 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1865 wevt.time_day_evt_end_fk)
1866 THEN 'NA_EDW'
1867 ELSE wevt.asgrsn_asgrsn_fk
1868 END asgrsn_asgrsn_fk
1869 ,CASE WHEN wevt.employee_ind = 1 AND
1870 wevt.sprn_sprtnrsn_fk <> 'NA_EDW'
1871 THEN 'LEAV_REAS' || '-' || wevt.sprn_sprtnrsn_fk
1872 WHEN wevt.contingent_ind = 1 AND
1873 wevt.sprn_sprtnrsn_fk <> 'NA_EDW'
1874 THEN 'HR_CWK_TERMINATION_REASONS' || '-' || wevt.sprn_sprtnrsn_fk
1875 ELSE 'NA_EDW'
1876 END sprn_sprtnrsn_fk
1877 ,wevt.scat_spcatgry_fk
1878 ,CASE WHEN wevt.termination_ind = 0
1879 THEN wevt.ptyp_pertyp_fk
1880 ELSE wevt.ptyp_pertyp_prv_fk
1881 END ptyp_pertyp_fk
1882 ,CASE WHEN wevt.termination_ind = 0
1883 THEN wevt.prfm_perfband_fk
1884 ELSE wevt.prfm_perfband_prv_fk
1885 END prfm_perfband_fk
1886 ,CASE WHEN wevt.termination_ind = 0
1887 THEN wevt.pow_powband_fk
1888 ELSE wevt.pow_powband_prv_fk
1889 END pow_powband_fk
1890 ,GREATEST(wevt.time_day_evt_fk,
1891 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk))
1892 time_day_evt_fk
1893 ,LEAST(wevt.time_day_evt_end_fk,
1894 NVL(chn.mgrs_date_end, wevt.time_day_evt_end_fk))
1895 time_day_evt_end_fk
1896 ,CASE WHEN wevt.termination_ind = 0
1897 THEN wevt.cur_currency_fk
1898 ELSE wevt.cur_currency_prv_fk
1899 END cur_currency_fk
1900 ,NVL(wevt.headcount, 0) headcount
1901 ,NVL(wevt.headcount_prv, 0) headcount_prv
1902 ,CASE WHEN wevt.time_day_evt_fk >= NVL(chn.mgrs_date_start,
1903 wevt.time_day_evt_fk) AND
1904 wevt.worker_hire_ind = 1
1905 THEN wevt.headcount
1906 ELSE 0
1907 END headcount_hire
1908 ,CASE WHEN wevt.worker_term_ind = 1 OR
1909 wevt.pre_sprtn_asgn_end_ind = 1
1910 THEN wevt.headcount_prv
1911 ELSE 0
1912 END headcount_term
1913 ,NVL(wevt.fte, 0) fte
1914 ,NVL(wevt.fte_prv, 0) fte_prv
1915 ,CASE WHEN wevt.time_day_evt_fk >= NVL(chn.mgrs_date_start,
1916 wevt.time_day_evt_fk) AND
1917 wevt.worker_hire_ind = 1
1918 THEN wevt.fte
1919 ELSE 0
1920 END fte_hire
1921 ,CASE WHEN wevt.worker_term_ind = 1 OR
1922 wevt.pre_sprtn_asgn_end_ind = 1
1923 THEN wevt.fte_prv
1924 ELSE 0
1925 END fte_term
1926 ,GREATEST(wevt.time_day_evt_fk,
1927 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk))
1928 - wevt.time_day_pow_start_fk
1929 pow_days_on_event_date
1930 ,MONTHS_BETWEEN
1931 (GREATEST(wevt.time_day_evt_fk,
1932 NVL(chn.mgrs_date_start, wevt.time_day_evt_fk)),
1933 wevt.time_day_pow_start_fk) pow_months_on_event_date
1934 ,wevt.days_since_last_prmtn
1935 ,wevt.months_since_last_prmtn
1936 ,NVL(wevt.anl_slry, 0) anl_slry
1937 ,NVL(wevt.anl_slry_prv, 0) anl_slry_prv
1938 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1939 wevt.time_day_evt_fk)
1940 THEN 0
1941 ELSE wevt.assignment_change_ind
1942 END assignment_change_ind
1943 ,wevt.primary_ind
1944 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1945 wevt.time_day_evt_fk)
1946 THEN 0
1947 ELSE wevt.headcount_gain_ind
1948 END headcount_gain_ind
1949 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1950 wevt.time_day_evt_fk)
1951 THEN 0
1952 ELSE wevt.headcount_loss_ind
1953 END headcount_loss_ind
1954 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1955 wevt.time_day_evt_fk)
1956 THEN 0
1957 ELSE wevt.headcount_loss_ind + wevt.headcount_gain_ind
1958 END headcount_change_ind
1959 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1960 wevt.time_day_evt_fk)
1961 THEN 0
1962 ELSE wevt.fte_gain_ind
1963 END fte_gain_ind
1964 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1965 wevt.time_day_evt_fk)
1966 THEN 0
1967 ELSE wevt.fte_loss_ind
1968 END fte_loss_ind
1969 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1970 wevt.time_day_evt_fk)
1971 THEN 0
1972 ELSE wevt.fte_loss_ind + wevt.fte_gain_ind
1973 END fte_change_ind
1974 ,wevt.contingent_ind
1975 ,wevt.employee_ind
1976 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1977 wevt.time_day_evt_fk)
1978 THEN 0
1979 ELSE wevt.grade_change_ind
1980 END grade_change_ind
1981 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1982 wevt.time_day_evt_fk)
1983 THEN 0
1984 ELSE wevt.job_change_ind
1985 END job_change_ind
1986 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1987 wevt.time_day_evt_fk)
1988 THEN 0
1989 ELSE wevt.position_change_ind
1990 END position_change_ind
1991 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1992 wevt.time_day_evt_fk)
1993 THEN 0
1994 ELSE wevt.location_change_ind
1995 END location_change_ind
1996 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
1997 wevt.time_day_evt_fk)
1998 THEN 0
1999 ELSE wevt.organization_change_ind
2000 END organization_change_ind
2001 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2002 wevt.time_day_evt_fk)
2003 THEN 0
2004 ELSE wevt.supervisor_change_ind
2005 END supervisor_change_ind
2006 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2007 wevt.time_day_evt_fk)
2008 THEN 0
2009 ELSE wevt.worker_hire_ind
2010 END worker_hire_ind
2011 ,wevt.term_voluntary_ind
2012 ,wevt.term_involuntary_ind
2013 ,wevt.worker_term_ind
2014 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2015 wevt.time_day_evt_fk)
2016 THEN 0
2017 ELSE wevt.post_hire_asgn_start_ind + wevt.worker_hire_ind
2018 END hire_or_start_ind
2019 ,wevt.worker_term_ind + wevt.pre_sprtn_asgn_end_ind
2020 term_or_end_ind
2021 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2022 wevt.time_day_evt_fk)
2023 THEN 0
2024 ELSE wevt.start_asg_sspnsn_ind
2025 END start_asg_sspnsn_ind
2026 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2027 wevt.time_day_evt_fk)
2028 THEN 0
2029 ELSE wevt.end_asg_sspnsn_ind
2030 END end_asg_sspnsn_ind
2031 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2032 wevt.time_day_evt_fk)
2033 THEN 0
2034 ELSE wevt.post_hire_asgn_start_ind
2035 END post_hire_asgn_start_ind
2036 ,wevt.pre_sprtn_asgn_end_ind
2037 -- ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2038 -- wevt.time_day_evt_fk)
2039 -- THEN 0
2040 -- ELSE wevt.prsntyp_change_ind
2041 -- END prsntyp_change_ind
2042 ,to_number(null) prsntyp_change_ind
2043 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2044 wevt.time_day_evt_fk)
2045 THEN 1
2046 ELSE 0
2047 END mgrh_node_change_ind
2048 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2049 wevt.time_day_evt_fk)
2050 THEN 0
2051 ELSE wevt.promotion_ind
2052 END promotion_ind
2053 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2054 wevt.time_day_evt_fk)
2055 THEN 0
2056 ELSE wevt.worker_term_nxt_ind
2057 END worker_term_nxt_ind
2058 ,CASE WHEN wevt.time_day_evt_fk < NVL(chn.mgrs_date_start,
2059 wevt.time_day_evt_fk)
2060 THEN 0
2061 ELSE wevt.pre_sprtn_asgn_end_nxt_ind
2062 END pre_sprtn_asgn_end_nxt_ind
2063 ,wevt.adt_event_id adt_event_id
2064 ,wevt.asg_assgnmnt_fk adt_assignment_id
2065 ,wevt.time_day_evt_fk adt_asg_effctv_start_date
2066 ,wevt.time_day_evt_end_fk adt_asg_effctv_end_date
2067 ,wevt.adt_business_group_id adt_business_group_id
2068 ,wevt.adt_perf_review_id adt_perf_review_id
2069 ,to_number(null) adt_period_of_service_id
2070 ,to_number(null) adt_period_of_placement_id
2071 ,pow.band_sequence adt_pow_band
2072 ,g_sysdate
2073 ,g_user
2074 ,g_user
2075 ,g_user
2076 ,g_sysdate
2077 FROM
2078 hri_cs_mngrsc_ct chn
2079 ,hri_cs_mngrsc_ct chn_prv
2080 ,hri_cs_pow_band_ct pow
2081 ,(SELECT /*+ NO_MERGE */
2082 hri_opl_wrkfc_evt_type.get_evtypcmb_fk
2083 (assignment_change_ind
2084 ,salary_change_ind
2085 ,perf_rating_change_ind
2086 ,perf_band_change_ind
2087 ,pow_band_change_ind
2088 ,headcount_gain_ind
2089 ,headcount_loss_ind
2090 ,fte_gain_ind
2091 ,fte_loss_ind
2092 ,grade_change_ind
2093 ,job_change_ind
2094 ,position_change_ind
2095 ,location_change_ind
2096 ,organization_change_ind
2097 ,supervisor_change_ind
2098 ,worker_hire_ind
2099 ,post_hire_asgn_start_ind
2100 ,pre_sprtn_asgn_end_ind
2101 ,term_voluntary_ind
2102 ,term_involuntary_ind
2103 ,worker_term_ind
2104 ,start_asg_sspnsn_ind
2105 ,end_asg_sspnsn_ind
2106 ,promotion_ind) wevt_evtypcmb_fk
2107 ,evt.assignment_id asg_assgnmnt_fk
2108 ,evt.person_id per_person_fk
2109 ,evt.supervisor_id per_person_mgr_fk
2110 ,evt.supervisor_prv_id per_person_mgr_prv_fk
2111 ,CASE WHEN evt.worker_term_ind = 0 AND
2112 evt.pre_sprtn_asgn_end_ind = 0
2113 THEN evt.supervisor_id
2114 ELSE evt.supervisor_prv_id
2115 END psn_chain_mgr_fk
2116 ,CASE WHEN evt.worker_term_ind = 0 AND
2117 evt.pre_sprtn_asgn_end_ind = 0
2118 THEN evt.effective_change_date
2119 ELSE evt.effective_change_date - 1
2120 END psn_chain_time_fk
2121 ,evt.organization_id org_organztn_fk
2122 ,evt.organization_prv_id org_organztn_prv_fk
2123 ,evt.job_id job_job_fk
2124 ,evt.job_prv_id job_job_prv_fk
2125 ,evt.grade_id grd_grade_fk
2126 ,evt.grade_prv_id grd_grade_prv_fk
2127 ,evt.position_id pos_position_fk
2128 ,evt.position_prv_id pos_position_prv_fk
2129 ,evt.location_id geo_location_fk
2130 ,evt.location_prv_id geo_location_prv_fk
2131 ,evt.change_reason_code asgrsn_asgrsn_fk
2132 ,evt.leaving_reason_code sprn_sprtnrsn_fk
2133 ,evt.separation_category scat_spcatgry_fk
2134 ,evt.prsntyp_sk_fk ptyp_pertyp_fk
2135 ,LAG(evt.prsntyp_sk_fk, 1) OVER (PARTITION BY evt.assignment_id
2136 ORDER BY evt.effective_change_date)
2137 ptyp_pertyp_prv_fk
2138 ,evt.perf_band prfm_perfband_fk
2139 ,evt.perf_band_prv prfm_perfband_prv_fk
2140 ,evt.pow_band_sk_fk pow_powband_fk
2141 ,evt.pow_band_prv_sk_fk pow_powband_prv_fk
2142 ,evt.effective_change_date time_day_evt_fk
2143 ,evt.effective_change_end_date time_day_evt_end_fk
2144 ,evt.pow_start_date_adj time_day_pow_start_fk
2145 ,evt.anl_slry_currency cur_currency_fk
2146 ,evt.anl_slry_currency_prv cur_currency_prv_fk
2147 ,evt.headcount headcount
2148 ,evt.headcount_prv headcount_prv
2149 ,evt.fte fte
2150 ,evt.fte_prv fte_prv
2151 ,evt.pow_days_on_event_date pow_days_on_event_date
2152 ,evt.pow_months_on_event_date pow_months_on_event_date
2153 ,evt.days_since_last_prmtn
2154 ,evt.months_since_last_prmtn
2155 ,evt.anl_slry anl_slry
2156 ,evt.anl_slry_prv anl_slry_prv
2157 ,evt.assignment_change_ind assignment_change_ind
2158 ,CASE WHEN evt.primary_flag = 'Y'
2159 THEN 1
2160 ELSE 0
2161 END primary_ind
2162 ,evt.headcount_gain_ind headcount_gain_ind
2163 ,evt.headcount_loss_ind headcount_loss_ind
2164 ,evt.fte_gain_ind fte_gain_ind
2165 ,evt.fte_loss_ind fte_loss_ind
2166 ,CASE WHEN evt.asg_type_code = 'C'
2167 THEN 1
2168 ELSE 0
2169 END contingent_ind
2170 ,CASE WHEN evt.asg_type_code = 'E'
2171 THEN 1
2172 ELSE 0
2173 END employee_ind
2174 ,evt.grade_change_ind grade_change_ind
2175 ,evt.job_change_ind job_change_ind
2176 ,evt.position_change_ind position_change_ind
2177 ,evt.location_change_ind location_change_ind
2178 ,evt.organization_change_ind organization_change_ind
2179 ,evt.supervisor_change_ind supervisor_change_ind
2180 ,evt.worker_hire_ind worker_hire_ind
2181 ,evt.term_voluntary_ind term_voluntary_ind
2182 ,evt.term_involuntary_ind term_involuntary_ind
2183 ,evt.worker_term_ind worker_term_ind
2184 ,evt.start_asg_sspnsn_ind start_asg_sspnsn_ind
2185 ,evt.end_asg_sspnsn_ind end_asg_sspnsn_ind
2186 ,evt.post_hire_asgn_start_ind post_hire_asgn_start_ind
2187 ,evt.pre_sprtn_asgn_end_ind pre_sprtn_asgn_end_ind
2188 ,to_number(null) prsntyp_change_ind
2189 ,CASE WHEN evt.worker_term_ind = 0 AND
2190 evt.pre_sprtn_asgn_end_ind = 0
2191 THEN 0
2192 ELSE 1
2193 END termination_ind
2194 ,evt.promotion_ind promotion_ind
2195 ,evt.worker_term_nxt_ind worker_term_nxt_ind
2196 ,evt.pre_sprtn_asgn_end_nxt_ind pre_sprtn_asgn_end_nxt_ind
2197 ,evt.event_id adt_event_id
2198 ,evt.business_group_id adt_business_group_id
2199 ,evt.performance_review_id adt_perf_review_id
2200 ,eq.erlst_evnt_effective_date adt_event_date
2201 FROM
2202 hri_eq_wrkfc_evt eq
2203 ,hri_mb_asgn_events_ct evt
2204 WHERE evt.assignment_id = eq.assignment_id
2205 AND evt.effective_change_end_date >= eq.erlst_evnt_effective_date - 1
2206 AND eq.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
2207 AND evt.summarization_rqd_ind = 1
2208 ) wevt
2209 WHERE wevt.pow_powband_fk = pow.pow_band_sk_pk
2210 AND chn.mgrs_person_fk (+) = wevt.psn_chain_mgr_fk
2211 AND chn.mgrs_date_start (+) <= wevt.time_day_evt_end_fk
2212 AND chn.mgrs_date_end (+) >= wevt.psn_chain_time_fk
2213 AND chn.mgrs_date_start (+) <= DECODE(wevt.termination_ind,
2214 0, wevt.time_day_evt_end_fk,
2215 wevt.psn_chain_time_fk)
2216 AND chn_prv.mgrs_person_fk (+) = wevt.per_person_mgr_prv_fk
2217 AND wevt.time_day_evt_fk - 1 BETWEEN chn_prv.mgrs_date_start (+)
2218 AND chn_prv.mgrs_date_end (+)
2219 AND wevt.time_day_evt_fk >= wevt.adt_event_date;
2220
2221 COMMIT;
2222
2223 END process_base_fact_incr;
2224
2225
2226 -- ----------------------------------------------------------------------------
2227 -- Deletes rows to be refreshed in incremental mode
2228 -- ----------------------------------------------------------------------------
2229 PROCEDURE delete_base_fact_incr(p_start_asg_id IN NUMBER,
2230 p_end_asg_id IN NUMBER) IS
2231
2232 BEGIN
2233
2234 -- Delete records later than earliest event date
2235 DELETE FROM hri_mb_wrkfc_evt_ct evt
2236 WHERE evt.rowid IN
2237 (SELECT /*+ ORDERED */
2238 evt2.rowid
2239 FROM
2240 hri_eq_wrkfc_evt eq
2241 ,hri_mb_wrkfc_evt_ct evt2
2242 WHERE evt2.asg_assgnmnt_fk = eq.assignment_id
2243 AND evt2.time_day_evt_fk >= eq.erlst_evnt_effective_date
2244 AND eq.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id);
2245
2246 -- End date records overlapping with event date
2247 UPDATE hri_mb_wrkfc_evt_ct evt
2248 SET evt.time_day_evt_end_fk =
2249 (SELECT eq.erlst_evnt_effective_date - 1
2250 FROM hri_eq_wrkfc_evt eq
2251 WHERE evt.asg_assgnmnt_fk = eq.assignment_id)
2252 WHERE evt.rowid IN
2253 (SELECT /*+ ORDERED */
2254 evt2.rowid
2255 FROM
2256 hri_eq_wrkfc_evt eq2
2257 ,hri_mb_wrkfc_evt_ct evt2
2258 WHERE evt2.asg_assgnmnt_fk = eq2.assignment_id
2259 AND eq2.erlst_evnt_effective_date BETWEEN evt2.time_day_evt_fk
2260 AND evt2.time_day_evt_end_fk
2261 AND eq2.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id);
2262
2263 END delete_base_fact_incr;
2264
2265
2266 -- ----------------------------------------------------------------------------
2267 -- Processes chunk in full refresh mode
2268 -- ----------------------------------------------------------------------------
2269 PROCEDURE process_range_full(p_start_asg_id IN NUMBER,
2270 p_end_asg_id IN NUMBER) IS
2271
2272 BEGIN
2273
2274 process_base_fact_full
2275 (p_start_asg_id => p_start_asg_id,
2276 p_end_asg_id => p_end_asg_id);
2277
2278 process_month_summary_full
2279 (p_start_asg_id => p_start_asg_id,
2280 p_end_asg_id => p_end_asg_id);
2281
2282 END process_range_full;
2283
2284
2285 -- ----------------------------------------------------------------------------
2286 -- Removes processed rows from event queue
2287 -- ----------------------------------------------------------------------------
2288 PROCEDURE delete_eq_incr(p_start_asg_id IN NUMBER,
2289 p_end_asg_id IN NUMBER) IS
2290
2291 BEGIN
2292
2293 DELETE FROM hri_eq_wrkfc_evt
2294 WHERE assignment_id BETWEEN p_start_asg_id AND p_end_asg_id;
2295
2296 DELETE FROM hri_eq_wrkfc_mnth
2297 WHERE assignment_id BETWEEN p_start_asg_id AND p_end_asg_id;
2298
2299 END delete_eq_incr;
2300
2301
2302 -- ----------------------------------------------------------------------------
2303 -- Processes chunk in incremental refresh mode
2304 -- ----------------------------------------------------------------------------
2305 PROCEDURE process_range_incr(p_start_asg_id IN NUMBER,
2306 p_end_asg_id IN NUMBER) IS
2307
2308 BEGIN
2309
2310 delete_base_fact_incr
2311 (p_start_asg_id => p_start_asg_id,
2312 p_end_asg_id => p_end_asg_id);
2313
2314 process_base_fact_incr
2315 (p_start_asg_id => p_start_asg_id,
2316 p_end_asg_id => p_end_asg_id);
2317
2318 delete_month_summary_incr
2319 (p_start_asg_id => p_start_asg_id,
2320 p_end_asg_id => p_end_asg_id);
2321
2322 process_month_summary_incr
2323 (p_start_asg_id => p_start_asg_id,
2324 p_end_asg_id => p_end_asg_id);
2325
2326 delete_eq_incr
2327 (p_start_asg_id => p_start_asg_id,
2328 p_end_asg_id => p_end_asg_id);
2329
2330 END process_range_incr;
2331
2332
2333 -- ----------------------------------------------------------------------------
2334 -- PROCESS_RANGE
2335 -- This procedure includes the logic required for processing the assignments
2336 -- which have been included in the range. It is dynamically invoked by the
2337 -- multithreading child process. It manages the multithreading ranges.
2338 -- ----------------------------------------------------------------------------
2339 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
2340 ,retcode OUT NOCOPY NUMBER
2341 ,p_mthd_action_id IN NUMBER
2342 ,p_mthd_range_id IN NUMBER
2343 ,p_start_object_id IN NUMBER
2344 ,p_end_object_id IN NUMBER) IS
2345
2346 BEGIN
2347
2348 -- Set the parameters
2349 set_parameters
2350 (p_mthd_action_id => p_mthd_action_id,
2351 p_mthd_stage_code => 'PROCESS_RANGE');
2352
2353 -- Set sysdate parameter
2354 g_sysdate := sysdate;
2355
2356 -- Process range in corresponding refresh mode
2357 IF g_full_refresh = 'Y' THEN
2358 process_range_full
2359 (p_start_asg_id => p_start_object_id,
2360 p_end_asg_id => p_end_object_id);
2361 ELSE
2362 process_range_incr
2363 (p_start_asg_id => p_start_object_id,
2364 p_end_asg_id => p_end_object_id);
2365 END IF;
2366
2367 END process_range;
2368
2369
2370 -- ----------------------------------------------------------------------------
2371 -- Removes duplicates from event queue
2372 -- ----------------------------------------------------------------------------
2373 PROCEDURE remove_eq_duplicates IS
2374
2375 BEGIN
2376
2377 -- Remove records in event queue that have duplicate records in event queue for
2378 -- an earlier refresh date
2379 DELETE FROM hri_eq_wrkfc_evt eq
2380 WHERE EXISTS
2381 (SELECT null
2382 FROM hri_eq_wrkfc_evt eq2
2383 WHERE eq2.assignment_id = eq.assignment_id
2384 AND (eq2.erlst_evnt_effective_date < eq.erlst_evnt_effective_date
2385 OR (eq2.rowid < eq.rowid AND
2386 eq2.erlst_evnt_effective_date = eq.erlst_evnt_effective_date)));
2387
2388 -- Remove records in event queue that have duplicate records in event queue for
2389 -- an earlier refresh date
2390 DELETE FROM hri_eq_wrkfc_mnth eq
2391 WHERE EXISTS
2392 (SELECT null
2393 FROM hri_eq_wrkfc_mnth eq2
2394 WHERE eq2.assignment_id = eq.assignment_id
2395 AND (eq2.erlst_evnt_effective_date < eq.erlst_evnt_effective_date
2396 OR (eq2.rowid < eq.rowid AND
2397 eq2.erlst_evnt_effective_date = eq.erlst_evnt_effective_date)));
2398
2399 END remove_eq_duplicates;
2400
2401
2402 -- ----------------------------------------------------------------------------
2403 -- Pre process entry point
2404 -- ----------------------------------------------------------------------------
2405 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
2406 p_sqlstr OUT NOCOPY VARCHAR2) IS
2407
2408 l_sql_stmt VARCHAR2(2000);
2409 l_dummy1 VARCHAR2(2000);
2410 l_dummy2 VARCHAR2(2000);
2411 l_schema VARCHAR2(400);
2412
2413 BEGIN
2414
2415 IF (p_mthd_action_id > -1) THEN
2416
2417 -- Set parameter globals
2418 set_parameters
2419 (p_mthd_action_id => p_mthd_action_id,
2420 p_mthd_stage_code => 'PRE_PROCESS');
2421
2422 END IF;
2423
2424 -- Get HRI schema name - get_app_info populates l_schema
2425 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2426 null;
2427 END IF;
2428
2429 -- Disable WHO triggers
2430 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_WRKFC_EVT_CT_WHO DISABLE');
2431 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_MNTH_CT_WHO DISABLE');
2432
2433 -- ********************
2434 -- Full Refresh Section
2435 -- ********************
2436 IF (g_full_refresh = 'Y' OR
2437 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
2438
2439 -- Empty out base fact table
2440 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_WRKFC_EVT_CT';
2441 EXECUTE IMMEDIATE(l_sql_stmt);
2442
2443 -- Empty out month summary table
2444 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MDS_WRKFC_MNTH_CT';
2445 EXECUTE IMMEDIATE(l_sql_stmt);
2446
2447 -- Empty out event type combination table
2448 hri_opl_wrkfc_evt_type.truncate_evtypcmb_table;
2449
2450 -- In shared HR mode do not return a SQL statement so that the
2451 -- process_range and post_process will not be executed
2452 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
2453
2454 -- Call post processing API
2455 post_process
2456 (p_mthd_action_id => p_mthd_action_id);
2457
2458 ELSE
2459
2460 -- Drop all the indexes on the table
2461 hri_utl_ddl.log_and_drop_indexes
2462 (p_application_short_name => 'HRI',
2463 p_table_name => 'HRI_MB_WRKFC_EVT_CT',
2464 p_table_owner => l_schema,
2465 p_index_excptn_lst => 'HRI_MB_WRKFC_EVT_CT_U1');
2466
2467 -- Drop all the indexes on the table
2468 hri_utl_ddl.log_and_drop_indexes
2469 (p_application_short_name => 'HRI',
2470 p_table_name => 'HRI_MDS_WRKFC_MNTH_CT',
2471 p_table_owner => l_schema);
2472
2473 -- Set the SQL statement for the entire range
2474 p_sqlstr :=
2475 'SELECT /*+ PARALLEL(asg, DEFAULT, DEFAULT) */ DISTINCT
2476 assignment_id object_id
2477 FROM hri_mb_asgn_events_ct
2478 ORDER BY assignment_id';
2479
2480 END IF;
2481
2482 ELSE
2483
2484 -- Remove duplicates from event queue
2485 remove_eq_duplicates;
2486
2487 -- Set the SQL statement for the incremental range
2488 p_sqlstr :=
2489 'SELECT
2490 assignment_id object_id
2491 FROM hri_eq_wrkfc_mnth
2492 ORDER BY assignment_id';
2493
2494 END IF;
2495
2496 END pre_process;
2497
2498 -- ----------------------------------------------------------------------------
2499 -- Post process entry point
2500 -- ----------------------------------------------------------------------------
2501 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
2502
2503 l_sql_stmt VARCHAR2(2000);
2504 l_dummy1 VARCHAR2(2000);
2505 l_dummy2 VARCHAR2(2000);
2506 l_schema VARCHAR2(400);
2507
2508 BEGIN
2509
2510 IF (p_mthd_action_id > -1) THEN
2511
2512 -- Check parameters are set
2513 set_parameters
2514 (p_mthd_action_id => p_mthd_action_id,
2515 p_mthd_stage_code => 'POST_PROCESS');
2516
2517 -- Log process end
2518 hri_bpl_conc_log.record_process_start('HRI_MB_WRKFC_EVT_CT');
2519 hri_bpl_conc_log.log_process_end(
2520 p_status => TRUE
2521 ,p_period_from => TRUNC(g_refresh_start_date)
2522 ,p_period_to => TRUNC(SYSDATE)
2523 ,p_attribute1 => g_full_refresh);
2524
2525 END IF;
2526
2527 -- Enable WHO trigger
2528 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_WRKFC_EVT_CT_WHO ENABLE');
2529 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_MNTH_CT_WHO ENABLE');
2530
2531 -- Get HRI schema name - get_app_info populates l_schema
2532 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2533 null;
2534 END IF;
2535
2536 -- Recreate indexes
2537 IF (g_full_refresh = 'Y') THEN
2538 hri_utl_ddl.recreate_indexes
2539 (p_application_short_name => 'HRI',
2540 p_table_name => 'HRI_MB_WRKFC_EVT_CT',
2541 p_table_owner => l_schema);
2542 hri_utl_ddl.recreate_indexes
2543 (p_application_short_name => 'HRI',
2544 p_table_name => 'HRI_MDS_WRKFC_MNTH_CT',
2545 p_table_owner => l_schema);
2546 END IF;
2547
2548 -- Empty out absence summary event queue
2549 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_EQ_WRKFC_EVT';
2550 -- EXECUTE IMMEDIATE(l_sql_stmt);
2551
2552 END post_process;
2553
2554 -- Populates table in a single thread
2555 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
2556
2557 CURSOR chunk_csr IS
2558 SELECT
2559 chunk_no
2560 ,MIN(assignment_id) start_asg_id
2561 ,MAX(assignment_id) end_asg_id
2562 FROM
2563 (SELECT
2564 assignment_id
2565 ,CEIL(ROWNUM / 20) chunk_no
2566 FROM
2567 (SELECT DISTINCT
2568 assignment_id
2569 FROM hri_mb_asgn_events_ct
2570 ORDER BY assignment_id
2571 ) tab
2572 ) chunks
2573 GROUP BY
2574 chunk_no;
2575
2576 CURSOR chunk_csr_incr IS
2577 SELECT
2578 chunk_no
2579 ,MIN(assignment_id) start_asg_id
2580 ,MAX(assignment_id) end_asg_id
2581 FROM
2582 (SELECT
2583 assignment_id
2584 ,CEIL(ROWNUM / 20) chunk_no
2585 FROM
2586 (SELECT
2587 assignment_id
2588 FROM hri_eq_wrkfc_mnth
2589 ORDER BY assignment_id
2590 ) tab
2591 ) chunks
2592 GROUP BY
2593 chunk_no;
2594
2595 l_dummy VARCHAR2(2000);
2596
2597 BEGIN
2598
2599 g_full_refresh := p_full_refresh_flag;
2600 g_sysdate := trunc(sysdate);
2601 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
2602 g_refresh_start_date := g_dbi_start_date;
2603 g_user := fnd_global.user_id;
2604
2605 pre_process(-1, l_dummy);
2606
2607 IF (p_full_refresh_flag = 'Y') THEN
2608
2609 FOR chunk_rec IN chunk_csr LOOP
2610 process_range_full(chunk_rec.start_asg_id, chunk_rec.end_asg_id);
2611 END LOOP;
2612
2613 ELSE
2614
2615 FOR chunk_rec IN chunk_csr_incr LOOP
2616 process_range_incr(chunk_rec.start_asg_id, chunk_rec.end_asg_id);
2617 END LOOP;
2618
2619 END IF;
2620
2621 post_process(-1);
2622
2623 END single_thread_process;
2624
2625 END hri_opl_wrkfc_events;