[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_WRKFC_EVT_MGRH
Source
1 PACKAGE BODY hri_opl_wrkfc_evt_mgrh AS
2 /* $Header: hriowevtmgr.pkb 120.4.12000000.2 2007/04/12 13:23:24 smohapat noship $ */
3
4 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
6 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
7
8 g_tab_sup_person_fk g_number_tab_type;
9 g_tab_sup_mngrsc_fk g_number_tab_type;
10 g_tab_sup_directs_only_flag g_varchar2_tab_type;
11 g_tab_time_day_mnth_start_fk g_date_tab_type;
12 g_tab_time_day_mnth_end_fk g_date_tab_type;
13 g_tab_time_month_snp_fk g_number_tab_type;
14 g_tab_job_function_fk g_varchar2_tab_type;
15 g_tab_job_family_fk g_varchar2_tab_type;
16 g_tab_geo_country_fk g_varchar2_tab_type;
17 g_tab_prfm_perfband_fk g_number_tab_type;
18 g_tab_pow_powband_fk g_number_tab_type;
19 g_tab_ptyp_wrktyp_fk g_varchar2_tab_type;
20 g_tab_cur_currency_fk g_varchar2_tab_type;
21 g_tab_headcount_start g_number_tab_type;
22 g_tab_headcount_end g_number_tab_type;
23 g_tab_headcount_hire g_number_tab_type;
24 g_tab_headcount_term g_number_tab_type;
25 g_tab_headcount_sep_vol g_number_tab_type;
26 g_tab_headcount_sep_invol g_number_tab_type;
27 g_tab_headcount_prmtn g_number_tab_type;
28 g_tab_fte_start g_number_tab_type;
29 g_tab_fte_end g_number_tab_type;
30 g_tab_fte_hire g_number_tab_type;
31 g_tab_fte_term g_number_tab_type;
32 g_tab_fte_sep_vol g_number_tab_type;
33 g_tab_fte_sep_invol g_number_tab_type;
34 g_tab_fte_prmtn g_number_tab_type;
35 g_tab_count_pasg_end g_number_tab_type;
36 g_tab_count_pasg_hire g_number_tab_type;
37 g_tab_count_pasg_term g_number_tab_type;
38 g_tab_count_pasg_sep_vol g_number_tab_type;
39 g_tab_count_pasg_sep_invol g_number_tab_type;
40 g_tab_count_asg_end g_number_tab_type;
41 g_tab_count_asg_hire g_number_tab_type;
42 g_tab_count_asg_term g_number_tab_type;
43 g_tab_count_asg_sep_vol g_number_tab_type;
44 g_tab_count_asg_sep_invol g_number_tab_type;
45 g_tab_count_asg_prmtn g_number_tab_type;
46 g_tab_pow_days_on_end_date g_number_tab_type;
47 g_tab_pow_months_on_end_date g_number_tab_type;
48 g_tab_days_since_last_prmtn g_number_tab_type;
49 g_tab_months_since_last_prmtn g_number_tab_type;
50 g_tab_anl_slry_start g_number_tab_type;
51 g_tab_anl_slry_end g_number_tab_type;
52 g_tab_employee_ind g_number_tab_type;
53 g_tab_contingent_ind g_number_tab_type;
54 g_tab_adt_pow_band g_number_tab_type;
55 g_row_count NUMBER;
56
57 -- End of time
58 g_end_of_time DATE := hr_general.end_of_time;
59
60 -- Global HRI Multithreading Array
61 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
62
63 -- Global parameters
64 g_refresh_start_date DATE;
65 g_refresh_to_date DATE;
66 g_full_refresh VARCHAR2(30);
67 g_sysdate DATE;
68 g_user NUMBER;
69 g_dbi_start_date DATE;
70
71 -- Number of quarters to process
72 g_no_qtrs_to_process PLS_INTEGER;
73
74
75 -- ----------------------------------------------------------------------------
76 -- Runs given sql statement dynamically
77 -- ----------------------------------------------------------------------------
78 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
79
80 BEGIN
81
82 EXECUTE IMMEDIATE p_sql_stmt;
83
84 EXCEPTION WHEN OTHERS THEN
85
86 null;
87
88 END run_sql_stmt_noerr;
89
90 -- ----------------------------------------------------------------------------
91 -- Sets global parameters from multi-threading process parameters
92 -- ----------------------------------------------------------------------------
93 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
94 p_mthd_stage_code IN VARCHAR2) IS
95
96 l_dbi_collection_start_date DATE;
97 l_first_qtr_start_date DATE;
98 l_last_qtr_start_date DATE;
99
100 BEGIN
101
102 -- If parameters haven't already been set, then set them
103 IF (g_refresh_start_date IS NULL OR
104 p_mthd_stage_code = 'PRE_PROCESS') THEN
105
106 l_dbi_collection_start_date :=
107 hri_oltp_conc_param.get_date_parameter_value
108 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
109 p_process_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT');
110
111 -- If called for the first time set the defaulted parameters
112 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
113
114 g_full_refresh :=
115 hri_oltp_conc_param.get_parameter_value
116 (p_parameter_name => 'FULL_REFRESH',
117 p_process_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT');
118
119 -- Log defaulted parameters so the slave processes pick up
120 hri_opl_multi_thread.update_parameters
121 (p_mthd_action_id => p_mthd_action_id,
122 p_full_refresh => g_full_refresh,
123 p_global_start_date => l_dbi_collection_start_date);
124
125 END IF;
126
127 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
128 (p_mthd_action_id);
129 g_refresh_start_date := g_mthd_action_array.collect_from_date;
130 g_refresh_to_date := g_mthd_action_array.collect_to_date;
131 g_full_refresh := g_mthd_action_array.full_refresh_flag;
132 g_sysdate := sysdate;
133 g_user := fnd_global.user_id;
134 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
135
136 -- Calculate number of quarters to process
137 l_first_qtr_start_date := trunc(g_dbi_start_date, 'Q');
138 l_last_qtr_start_date := trunc(g_refresh_to_date, 'Q');
139 g_no_qtrs_to_process := (MONTHS_BETWEEN(l_last_qtr_start_date,
140 l_first_qtr_start_date) / 3) + 1;
141
142 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
143 hri_bpl_conc_log.dbg('Collect from: N/A');
144
145 END IF;
146
147 END set_parameters;
148
149 PROCEDURE bulk_insert_rows IS
150
151 BEGIN
152
153 IF g_row_count > 0 THEN
154
155 FORALL i IN 1..g_row_count
156 INSERT INTO hri_mds_wrkfc_mgrh_c01_ct
157 (sup_person_fk
158 ,sup_mngrsc_fk
159 ,sup_directs_only_flag
160 ,time_day_mnth_start_fk
161 ,time_day_mnth_end_fk
162 ,time_month_snp_fk
163 ,job_function_fk
164 ,job_family_fk
165 ,geo_country_fk
166 ,prfm_perfband_fk
167 ,pow_powband_fk
168 ,ptyp_wrktyp_fk
169 ,cur_currency_fk
170 ,headcount_start
171 ,headcount_end
172 ,headcount_hire
173 ,headcount_term
174 ,headcount_sep_vol
175 ,headcount_sep_invol
176 ,headcount_prmtn
177 ,fte_start
178 ,fte_end
179 ,fte_hire
180 ,fte_term
181 ,fte_sep_vol
182 ,fte_sep_invol
183 ,fte_prmtn
184 ,count_pasg_end
185 ,count_pasg_hire
186 ,count_pasg_term
187 ,count_pasg_sep_vol
188 ,count_pasg_sep_invol
189 ,count_asg_end
190 ,count_asg_hire
191 ,count_asg_term
192 ,count_asg_sep_vol
193 ,count_asg_sep_invol
194 ,count_asg_prmtn
195 ,pow_days_on_end_date
196 ,pow_months_on_end_date
197 ,days_since_last_prmtn
198 ,months_since_last_prmtn
199 ,anl_slry_start
200 ,anl_slry_end
201 ,employee_ind
202 ,contingent_ind
203 ,adt_pow_band
204 ,creation_date
205 ,created_by
206 ,last_updated_by
207 ,last_update_login
208 ,last_update_date)
209 VALUES
210 (g_tab_sup_person_fk(i)
211 ,g_tab_sup_mngrsc_fk(i)
212 ,g_tab_sup_directs_only_flag(i)
213 ,g_tab_time_day_mnth_start_fk(i)
214 ,g_tab_time_day_mnth_end_fk(i)
215 ,g_tab_time_month_snp_fk(i)
216 ,g_tab_job_function_fk(i)
217 ,g_tab_job_family_fk(i)
218 ,g_tab_geo_country_fk(i)
219 ,g_tab_prfm_perfband_fk(i)
220 ,g_tab_pow_powband_fk(i)
221 ,g_tab_ptyp_wrktyp_fk(i)
222 ,g_tab_cur_currency_fk(i)
223 ,g_tab_headcount_start(i)
224 ,g_tab_headcount_end(i)
225 ,g_tab_headcount_hire(i)
226 ,g_tab_headcount_term(i)
227 ,g_tab_headcount_sep_vol(i)
228 ,g_tab_headcount_sep_invol(i)
229 ,g_tab_headcount_prmtn(i)
230 ,g_tab_fte_start(i)
231 ,g_tab_fte_end(i)
232 ,g_tab_fte_hire(i)
233 ,g_tab_fte_term(i)
234 ,g_tab_fte_sep_vol(i)
235 ,g_tab_fte_sep_invol(i)
236 ,g_tab_fte_prmtn(i)
237 ,g_tab_count_pasg_end(i)
238 ,g_tab_count_pasg_hire(i)
239 ,g_tab_count_pasg_term(i)
240 ,g_tab_count_pasg_sep_vol(i)
241 ,g_tab_count_pasg_sep_invol(i)
242 ,g_tab_count_asg_end(i)
243 ,g_tab_count_asg_hire(i)
244 ,g_tab_count_asg_term(i)
245 ,g_tab_count_asg_sep_vol(i)
246 ,g_tab_count_asg_sep_invol(i)
247 ,g_tab_count_asg_prmtn(i)
248 ,g_tab_pow_days_on_end_date(i)
249 ,g_tab_pow_months_on_end_date(i)
250 ,g_tab_days_since_last_prmtn(i)
251 ,g_tab_months_since_last_prmtn(i)
252 ,g_tab_anl_slry_start(i)
253 ,g_tab_anl_slry_end(i)
254 ,g_tab_employee_ind(i)
255 ,g_tab_contingent_ind(i)
256 ,g_tab_adt_pow_band(i)
257 ,g_sysdate
258 ,g_user
259 ,g_user
260 ,g_user
261 ,g_sysdate);
262
263 g_row_count := 0;
264 commit;
265
266 END IF;
267
268 END bulk_insert_rows;
269
270 PROCEDURE insert_row
271 (p_sup_person_fk IN NUMBER
272 ,p_sup_mngrsc_fk IN NUMBER
273 ,p_sup_directs_only_flag IN VARCHAR2
274 ,p_time_day_mnth_start_fk IN DATE
275 ,p_time_day_mnth_end_fk IN DATE
276 ,p_time_month_snp_fk IN NUMBER
277 ,p_job_function_fk IN VARCHAR2
278 ,p_job_family_fk IN VARCHAR2
279 ,p_geo_country_fk IN VARCHAR2
280 ,p_prfm_perfband_fk IN NUMBER
281 ,p_pow_powband_fk IN NUMBER
282 ,p_ptyp_wrktyp_fk IN VARCHAR2
283 ,p_cur_currency_fk IN VARCHAR2
284 ,p_headcount_start IN NUMBER
285 ,p_headcount_end IN NUMBER
286 ,p_headcount_hire IN NUMBER
287 ,p_headcount_term IN NUMBER
288 ,p_headcount_sep_vol IN NUMBER
289 ,p_headcount_sep_invol IN NUMBER
290 ,p_headcount_prmtn IN NUMBER
291 ,p_fte_start IN NUMBER
292 ,p_fte_end IN NUMBER
293 ,p_fte_hire IN NUMBER
294 ,p_fte_term IN NUMBER
295 ,p_fte_sep_vol IN NUMBER
296 ,p_fte_sep_invol IN NUMBER
297 ,p_fte_prmtn IN NUMBER
298 ,p_count_pasg_end IN NUMBER
299 ,p_count_pasg_hire IN NUMBER
300 ,p_count_pasg_term IN NUMBER
301 ,p_count_pasg_sep_vol IN NUMBER
302 ,p_count_pasg_sep_invol IN NUMBER
303 ,p_count_asg_end IN NUMBER
304 ,p_count_asg_hire IN NUMBER
305 ,p_count_asg_term IN NUMBER
306 ,p_count_asg_sep_vol IN NUMBER
307 ,p_count_asg_sep_invol IN NUMBER
308 ,p_count_asg_prmtn IN NUMBER
309 ,p_pow_days_on_end_date IN NUMBER
310 ,p_pow_months_on_end_date IN NUMBER
311 ,p_days_since_last_prmtn IN NUMBER
312 ,p_months_since_last_prmtn IN NUMBER
313 ,p_anl_slry_start IN NUMBER
314 ,p_anl_slry_end IN NUMBER
315 ,p_employee_ind IN NUMBER
316 ,p_contingent_ind IN NUMBER
317 ,p_adt_pow_band IN NUMBER) IS
318
319 BEGIN
320
321 g_row_count := g_row_count + 1;
322 g_tab_sup_person_fk(g_row_count) := p_sup_person_fk;
323 g_tab_sup_mngrsc_fk(g_row_count) := p_sup_mngrsc_fk;
324 g_tab_sup_directs_only_flag(g_row_count) := p_sup_directs_only_flag;
325 g_tab_time_day_mnth_start_fk(g_row_count) := p_time_day_mnth_start_fk;
326 g_tab_time_day_mnth_end_fk(g_row_count) := p_time_day_mnth_end_fk;
327 g_tab_time_month_snp_fk(g_row_count) := p_time_month_snp_fk;
328 g_tab_job_function_fk(g_row_count) := p_job_function_fk;
329 g_tab_job_family_fk(g_row_count) := p_job_family_fk;
330 g_tab_geo_country_fk(g_row_count) := p_geo_country_fk;
331 g_tab_prfm_perfband_fk(g_row_count) := p_prfm_perfband_fk;
332 g_tab_pow_powband_fk(g_row_count) := p_pow_powband_fk;
333 g_tab_ptyp_wrktyp_fk(g_row_count) := p_ptyp_wrktyp_fk;
334 g_tab_cur_currency_fk(g_row_count) := p_cur_currency_fk;
335 g_tab_headcount_start(g_row_count) := p_headcount_start;
336 g_tab_headcount_end(g_row_count) := p_headcount_end;
337 g_tab_headcount_hire(g_row_count) := p_headcount_hire;
338 g_tab_headcount_term(g_row_count) := p_headcount_term;
339 g_tab_headcount_sep_vol(g_row_count) := p_headcount_sep_vol;
340 g_tab_headcount_sep_invol(g_row_count) := p_headcount_sep_invol;
341 g_tab_headcount_prmtn(g_row_count) := p_headcount_prmtn;
342 g_tab_fte_start(g_row_count) := p_fte_start;
343 g_tab_fte_end(g_row_count) := p_fte_end;
344 g_tab_fte_hire(g_row_count) := p_fte_hire;
345 g_tab_fte_term(g_row_count) := p_fte_term;
346 g_tab_fte_sep_vol(g_row_count) := p_fte_sep_vol;
347 g_tab_fte_sep_invol(g_row_count) := p_fte_sep_invol;
348 g_tab_fte_prmtn(g_row_count) := p_fte_prmtn;
349 g_tab_count_pasg_end(g_row_count) := p_count_pasg_end;
350 g_tab_count_pasg_hire(g_row_count) := p_count_pasg_hire;
351 g_tab_count_pasg_term(g_row_count) := p_count_pasg_term;
352 g_tab_count_pasg_sep_vol(g_row_count) := p_count_pasg_sep_vol;
353 g_tab_count_pasg_sep_invol(g_row_count) := p_count_pasg_sep_invol;
354 g_tab_count_asg_end(g_row_count) := p_count_asg_end;
355 g_tab_count_asg_hire(g_row_count) := p_count_asg_hire;
356 g_tab_count_asg_term(g_row_count) := p_count_asg_term;
357 g_tab_count_asg_sep_vol(g_row_count) := p_count_asg_sep_vol;
358 g_tab_count_asg_sep_invol(g_row_count) := p_count_asg_sep_invol;
359 g_tab_count_asg_prmtn(g_row_count) := p_count_asg_prmtn;
360 g_tab_pow_days_on_end_date(g_row_count) := p_pow_days_on_end_date;
361 g_tab_pow_months_on_end_date(g_row_count) := p_pow_months_on_end_date;
362 g_tab_days_since_last_prmtn(g_row_count) := p_days_since_last_prmtn;
363 g_tab_months_since_last_prmtn(g_row_count) := p_months_since_last_prmtn;
364 g_tab_anl_slry_start(g_row_count) := p_anl_slry_start;
365 g_tab_anl_slry_end(g_row_count) := p_anl_slry_end;
366 g_tab_employee_ind(g_row_count) := p_employee_ind;
367 g_tab_contingent_ind(g_row_count) := p_contingent_ind;
368 g_tab_adt_pow_band(g_row_count) := p_adt_pow_band;
369
370 END insert_row;
371
372
373 -- ----------------------------------------------------------------------------
374 -- Processes snapshot in full refresh mode
375 -- ----------------------------------------------------------------------------
376 PROCEDURE process_snapshot(p_manager_id IN NUMBER,
377 p_snapshot_date IN DATE) IS
378
379 CURSOR snp_csr(v_month_id IN NUMBER) IS
380 SELECT
381 tab.sup_person_fk
382 ,tab.sup_mngrsc_fk
383 ,tab.sup_directs_only_flag
384 ,tab.time_day_mnth_start_fk
385 ,tab.time_day_mnth_end_fk
386 ,tab.time_month_snp_fk
387 ,tab.job_function_fk
388 ,tab.job_family_fk
389 ,tab.geo_country_fk
390 ,tab.prfm_perfband_fk
391 ,tab.pow_powband_fk
392 ,tab.ptyp_wrktyp_fk
393 ,tab.cur_currency_fk
394 ,SUM(tab.headcount_start) headcount_start
395 ,SUM(tab.headcount_end) headcount_end
396 ,SUM(tab.headcount_hire) headcount_hire
397 ,SUM(tab.headcount_term) headcount_term
398 ,SUM(tab.headcount_sep_vol) headcount_sep_vol
399 ,SUM(tab.headcount_sep_invol) headcount_sep_invol
400 ,SUM(tab.headcount_prmtn) headcount_prmtn
401 ,SUM(tab.fte_start) fte_start
402 ,SUM(tab.fte_end) fte_end
403 ,SUM(tab.fte_hire) fte_hire
404 ,SUM(tab.fte_term) fte_term
405 ,SUM(tab.fte_sep_vol) fte_sep_vol
406 ,SUM(tab.fte_sep_invol) fte_sep_invol
407 ,SUM(tab.fte_prmtn) fte_prmtn
408 ,SUM(tab.count_pasg_end) count_pasg_end
409 ,SUM(tab.count_pasg_hire) count_pasg_hire
410 ,SUM(tab.count_pasg_term) count_pasg_term
411 ,SUM(tab.count_pasg_sep_vol) count_pasg_sep_vol
412 ,SUM(tab.count_pasg_sep_invol) count_pasg_sep_invol
413 ,SUM(tab.count_asg_end) count_asg_end
414 ,SUM(tab.count_asg_hire) count_asg_hire
415 ,SUM(tab.count_asg_term) count_asg_term
416 ,SUM(tab.count_asg_sep_vol) count_asg_sep_vol
417 ,SUM(tab.count_asg_sep_invol) count_asg_sep_invol
418 ,SUM(tab.count_asg_prmtn) count_asg_prmtn
419 ,SUM(tab.pow_days_on_end_date) pow_days_on_end_date
420 ,SUM(tab.pow_months_on_end_date) pow_months_on_end_date
421 ,SUM(tab.days_since_last_prmtn) days_since_last_prmtn
422 ,SUM(tab.months_since_last_prmtn) months_since_last_prmtn
423 ,SUM(tab.anl_slry_start) anl_slry_start
424 ,SUM(tab.anl_slry_end) anl_slry_end
425 ,tab.employee_ind
426 ,tab.contingent_ind
427 ,tab.adt_pow_band
428 FROM
429 (SELECT
430 suph.sup_person_id sup_person_fk
431 ,chn.mgrs_mngrsc_pk sup_mngrsc_fk
432 ,'N' sup_directs_only_flag
433 ,fct.time_day_mnth_start_fk
434 ,fct.time_day_mnth_end_fk
435 ,fct.time_month_snp_fk
436 ,fct.job_function_fk
437 ,fct.job_family_fk
438 ,fct.geo_country_fk
439 ,fct.prfm_perfband_fk
440 ,fct.pow_powband_fk
441 ,fct.ptyp_wrktyp_fk
442 ,fct.cur_currency_fk
443 ,fct.headcount_start
444 ,fct.headcount_end
445 ,fct.headcount_hire
446 ,fct.headcount_term
447 ,fct.headcount_sep_vol
448 ,fct.headcount_sep_invol
449 ,fct.headcount_prmtn
450 ,fct.fte_start
451 ,fct.fte_end
452 ,fct.fte_hire
453 ,fct.fte_term
454 ,fct.fte_sep_vol
455 ,fct.fte_sep_invol
456 ,fct.fte_prmtn
457 ,fct.count_pasg_end
458 ,fct.count_pasg_hire
459 ,fct.count_pasg_term
460 ,fct.count_pasg_sep_vol
461 ,fct.count_pasg_sep_invol
462 ,fct.count_asg_end
463 ,fct.count_asg_hire
464 ,fct.count_asg_term
465 ,fct.count_asg_sep_vol
466 ,fct.count_asg_sep_invol
467 ,fct.count_asg_prmtn
468 ,fct.pow_days_on_end_date
469 ,fct.pow_months_on_end_date
470 ,fct.days_since_last_prmtn
471 ,fct.months_since_last_prmtn
472 ,fct.anl_slry_start
473 ,fct.anl_slry_end
474 ,fct.employee_ind
475 ,fct.contingent_ind
476 ,fct.adt_pow_band
477 FROM
478 hri_cs_mngrsc_ct chn
479 ,hri_cs_suph suph
480 ,hri_mds_wrkfc_mgrh_c01_ct fct
481 WHERE chn.mgrs_person_fk = p_manager_id
482 AND p_snapshot_date BETWEEN chn.mgrs_date_start
483 AND chn.mgrs_date_end
484 AND suph.sup_person_id = chn.mgrs_person_fk
485 AND suph.sub_relative_level = 1
486 AND p_snapshot_date BETWEEN suph.effective_start_date
487 AND suph.effective_end_date
488 AND suph.sub_person_id = fct.sup_person_fk
489 AND fct.time_month_snp_fk = v_month_id
490 AND fct.sup_directs_only_flag = 'N'
491 UNION ALL
492 SELECT /*+ ORDERED USE_NL(wevt ctr jobh ptyp) */
493 wevt.per_person_mgr_fk sup_person_fk
494 ,wevt.mgr_mngrsc_fk sup_mngrsc_fk
495 ,dcts.sup_directs_only_flag sup_directs_only_flag
496 ,wevt.time_day_mnth_start_fk time_day_mnth_start_fk
497 ,wevt.time_day_mnth_end_fk time_day_mnth_end_fk
498 ,wevt.time_month_snp_fk time_month_snp_fk
499 ,jobh.job_fnctn_code job_function_fk
500 ,jobh.job_fmly_code job_family_fk
501 ,ctr.country_code geo_country_fk
502 ,wevt.prfm_perfband_fk prfm_perfband_fk
503 ,wevt.pow_powband_fk pow_powband_fk
504 ,ptyp.wkth_wktyp_sk_fk ptyp_wrktyp_fk
505 ,wevt.cur_currency_fk cur_currency_fk
506 ,wevt.headcount_start headcount_start
507 ,wevt.headcount_end headcount_end
508 ,wevt.headcount_hire headcount_hire
509 ,wevt.headcount_term headcount_term
510 ,wevt.headcount_sep_vol headcount_sep_vol
511 ,wevt.headcount_sep_invol headcount_sep_invol
512 ,wevt.headcount_prmtn headcount_prmtn
513 ,wevt.fte_start fte_start
514 ,wevt.fte_end fte_end
515 ,wevt.fte_hire fte_hire
516 ,wevt.fte_term fte_term
517 ,wevt.fte_sep_vol fte_sep_vol
518 ,wevt.fte_sep_invol fte_sep_invol
519 ,wevt.fte_prmtn fte_prmtn
520 ,wevt.count_pasg_end count_pasg_end
521 ,wevt.count_pasg_hire count_pasg_hire
522 ,wevt.count_pasg_term count_pasg_term
523 ,wevt.count_pasg_sep_vol count_pasg_sep_vol
524 ,wevt.count_pasg_sep_invol count_pasg_sep_invol
525 ,wevt.count_asg_end count_asg_end
526 ,wevt.count_asg_hire count_asg_hire
527 ,wevt.count_asg_term count_asg_term
528 ,wevt.count_asg_sep_vol count_asg_sep_vol
529 ,wevt.count_asg_sep_invol count_asg_sep_invol
530 ,wevt.count_asg_prmtn count_asg_prmtn
531 ,wevt.pow_days_on_end_date pow_days_on_end_date
532 ,wevt.pow_months_on_end_date pow_months_on_end_date
533 ,wevt.days_since_last_prmtn days_since_last_prmtn
534 ,wevt.months_since_last_prmtn months_since_last_prmtn
535 ,wevt.anl_slry_start anl_slry_start
536 ,wevt.anl_slry_end anl_slry_end
537 ,wevt.employee_ind employee_ind
538 ,wevt.contingent_ind contingent_ind
539 ,wevt.adt_pow_band adt_pow_band
540 FROM
541 hri_cs_mngrsc_ct chn
542 ,hri_mds_wrkfc_mnth_ct wevt
543 ,hri_cs_geo_lochr_ct ctr
544 ,hri_cs_jobh_ct jobh
545 ,hri_cs_prsntyp_ct ptyp
546 ,(SELECT 'Y' sup_directs_only_flag FROM dual
547 UNION ALL
548 SELECT 'N' sup_directs_only_flag FROM dual
549 ) dcts
550 WHERE chn.mgrs_person_fk = p_manager_id
551 AND p_snapshot_date BETWEEN chn.mgrs_date_start
552 AND chn.mgrs_date_end
553 AND wevt.mgr_mngrsc_fk = chn.mgrs_mngrsc_pk
554 AND wevt.time_month_snp_fk = v_month_id
555 AND ctr.location_id = wevt.geo_location_fk
556 AND jobh.job_id = wevt.job_job_fk
557 AND ptyp.prsntyp_sk_pk = wevt.ptyp_pertyp_fk
558 ) tab
559 GROUP BY
560 tab.sup_person_fk
561 ,tab.sup_mngrsc_fk
562 ,tab.sup_directs_only_flag
563 ,tab.time_day_mnth_start_fk
564 ,tab.time_day_mnth_end_fk
565 ,tab.time_month_snp_fk
566 ,tab.cur_currency_fk
567 ,tab.job_function_fk
568 ,tab.job_family_fk
569 ,tab.geo_country_fk
570 ,tab.prfm_perfband_fk
571 ,tab.pow_powband_fk
572 ,tab.ptyp_wrktyp_fk
573 ,tab.cur_currency_fk
574 ,tab.employee_ind
575 ,tab.contingent_ind
576 ,tab.adt_pow_band;
577
578 l_month_id NUMBER;
579 l_sup_person_fk g_number_tab_type;
580 l_sup_mngrsc_fk g_number_tab_type;
581 l_sup_directs_only_flag g_varchar2_tab_type;
582 l_time_day_mnth_start_fk g_date_tab_type;
583 l_time_day_mnth_end_fk g_date_tab_type;
584 l_time_month_snp_fk g_number_tab_type;
585 l_job_function_fk g_varchar2_tab_type;
586 l_job_family_fk g_varchar2_tab_type;
587 l_geo_country_fk g_varchar2_tab_type;
588 l_prfm_perfband_fk g_number_tab_type;
589 l_pow_powband_fk g_number_tab_type;
590 l_ptyp_wrktyp_fk g_varchar2_tab_type;
591 l_cur_currency_fk g_varchar2_tab_type;
592 l_headcount_start g_number_tab_type;
593 l_headcount_end g_number_tab_type;
594 l_headcount_hire g_number_tab_type;
595 l_headcount_term g_number_tab_type;
596 l_headcount_sep_vol g_number_tab_type;
597 l_headcount_sep_invol g_number_tab_type;
598 l_headcount_prmtn g_number_tab_type;
599 l_fte_start g_number_tab_type;
600 l_fte_end g_number_tab_type;
601 l_fte_hire g_number_tab_type;
602 l_fte_term g_number_tab_type;
603 l_fte_sep_vol g_number_tab_type;
604 l_fte_sep_invol g_number_tab_type;
605 l_fte_prmtn g_number_tab_type;
606 l_count_pasg_end g_number_tab_type;
607 l_count_pasg_hire g_number_tab_type;
608 l_count_pasg_term g_number_tab_type;
609 l_count_pasg_sep_vol g_number_tab_type;
610 l_count_pasg_sep_invol g_number_tab_type;
611 l_count_asg_end g_number_tab_type;
612 l_count_asg_hire g_number_tab_type;
613 l_count_asg_term g_number_tab_type;
614 l_count_asg_sep_vol g_number_tab_type;
615 l_count_asg_sep_invol g_number_tab_type;
616 l_count_asg_prmtn g_number_tab_type;
617 l_pow_days_on_end_date g_number_tab_type;
618 l_pow_months_on_end_date g_number_tab_type;
619 l_days_since_last_prmtn g_number_tab_type;
620 l_months_since_last_prmtn g_number_tab_type;
621 l_anl_slry_start g_number_tab_type;
622 l_anl_slry_end g_number_tab_type;
623 l_employee_ind g_number_tab_type;
624 l_contingent_ind g_number_tab_type;
625 l_adt_pow_band g_number_tab_type;
626
627 BEGIN
628
629 l_month_id := to_number(to_char(p_snapshot_date, 'YYYYQMM'));
630
631 OPEN snp_csr(l_month_id);
632 FETCH snp_csr BULK COLLECT INTO
633 l_sup_person_fk,
634 l_sup_mngrsc_fk,
635 l_sup_directs_only_flag,
636 l_time_day_mnth_start_fk,
637 l_time_day_mnth_end_fk,
638 l_time_month_snp_fk,
639 l_job_function_fk,
640 l_job_family_fk,
641 l_geo_country_fk,
642 l_prfm_perfband_fk,
643 l_pow_powband_fk,
644 l_ptyp_wrktyp_fk,
645 l_cur_currency_fk,
646 l_headcount_start,
647 l_headcount_end,
648 l_headcount_hire,
649 l_headcount_term,
650 l_headcount_sep_vol,
651 l_headcount_sep_invol,
652 l_headcount_prmtn,
653 l_fte_start,
654 l_fte_end,
655 l_fte_hire,
656 l_fte_term,
657 l_fte_sep_vol,
658 l_fte_sep_invol,
659 l_fte_prmtn,
660 l_count_pasg_end,
661 l_count_pasg_hire,
662 l_count_pasg_term,
663 l_count_pasg_sep_vol,
664 l_count_pasg_sep_invol,
665 l_count_asg_end,
666 l_count_asg_hire,
667 l_count_asg_term,
668 l_count_asg_sep_vol,
669 l_count_asg_sep_invol,
670 l_count_asg_prmtn,
671 l_pow_days_on_end_date,
672 l_pow_months_on_end_date,
673 l_days_since_last_prmtn,
674 l_months_since_last_prmtn,
675 l_anl_slry_start,
676 l_anl_slry_end,
677 l_employee_ind,
678 l_contingent_ind,
679 l_adt_pow_band;
680 CLOSE snp_csr;
681
682 -- Transfer results to global array for bulk insert
683 IF l_sup_person_fk.EXISTS(1) THEN
684 FOR i IN 1..l_sup_person_fk.LAST LOOP
685 insert_row
686 (p_sup_person_fk => l_sup_person_fk(i)
687 ,p_sup_mngrsc_fk => l_sup_mngrsc_fk(i)
688 ,p_sup_directs_only_flag => l_sup_directs_only_flag(i)
689 ,p_time_day_mnth_start_fk => l_time_day_mnth_start_fk(i)
690 ,p_time_day_mnth_end_fk => l_time_day_mnth_end_fk(i)
691 ,p_time_month_snp_fk => l_time_month_snp_fk(i)
692 ,p_job_function_fk => l_job_function_fk(i)
693 ,p_job_family_fk => l_job_family_fk(i)
694 ,p_geo_country_fk => l_geo_country_fk(i)
695 ,p_prfm_perfband_fk => l_prfm_perfband_fk(i)
696 ,p_pow_powband_fk => l_pow_powband_fk(i)
697 ,p_ptyp_wrktyp_fk => l_ptyp_wrktyp_fk(i)
698 ,p_cur_currency_fk => l_cur_currency_fk(i)
699 ,p_headcount_start => l_headcount_start(i)
700 ,p_headcount_end => l_headcount_end(i)
701 ,p_headcount_hire => l_headcount_hire(i)
702 ,p_headcount_term => l_headcount_term(i)
703 ,p_headcount_sep_vol => l_headcount_sep_vol(i)
704 ,p_headcount_sep_invol => l_headcount_sep_invol(i)
705 ,p_headcount_prmtn => l_headcount_prmtn(i)
706 ,p_fte_start => l_fte_start(i)
707 ,p_fte_end => l_fte_end(i)
708 ,p_fte_hire => l_fte_hire(i)
709 ,p_fte_term => l_fte_term(i)
710 ,p_fte_sep_vol => l_fte_sep_vol(i)
711 ,p_fte_sep_invol => l_fte_sep_invol(i)
712 ,p_fte_prmtn => l_fte_prmtn(i)
713 ,p_count_pasg_end => l_count_pasg_end(i)
714 ,p_count_pasg_hire => l_count_pasg_hire(i)
715 ,p_count_pasg_term => l_count_pasg_term(i)
716 ,p_count_pasg_sep_vol => l_count_pasg_sep_vol(i)
717 ,p_count_pasg_sep_invol => l_count_pasg_sep_invol(i)
718 ,p_count_asg_end => l_count_asg_end(i)
719 ,p_count_asg_hire => l_count_asg_hire(i)
720 ,p_count_asg_term => l_count_asg_term(i)
721 ,p_count_asg_sep_vol => l_count_asg_sep_vol(i)
722 ,p_count_asg_sep_invol => l_count_asg_sep_invol(i)
723 ,p_count_asg_prmtn => l_count_asg_prmtn(i)
724 ,p_pow_days_on_end_date => l_pow_days_on_end_date(i)
725 ,p_pow_months_on_end_date => l_pow_months_on_end_date(i)
726 ,p_days_since_last_prmtn => l_days_since_last_prmtn(i)
727 ,p_months_since_last_prmtn => l_months_since_last_prmtn(i)
728 ,p_anl_slry_start => l_anl_slry_start(i)
729 ,p_anl_slry_end => l_anl_slry_end(i)
730 ,p_employee_ind => l_employee_ind(i)
731 ,p_contingent_ind => l_contingent_ind(i)
732 ,p_adt_pow_band => l_adt_pow_band(i));
733 END LOOP;
734 END IF;
735
736 -- Bulk insert rows periodically
737 IF g_row_count > 2000 THEN
738 bulk_insert_rows;
739 END IF;
740
741 END process_snapshot;
742
743
744 -- ----------------------------------------------------------------------------
745 -- Processes chunk in full refresh mode
746 -- ----------------------------------------------------------------------------
747 PROCEDURE process_range_full(p_start_object_id IN NUMBER,
748 p_end_object_id IN NUMBER,
749 p_mthd_range_lvl IN NUMBER) IS
750
751 CURSOR work_csr IS
752 SELECT DISTINCT
753 mgr.mgrs_person_fk manager_id
754 ,mnth.end_date snapshot_date
755 FROM
756 hri_cs_mngrsc_ct mgr
757 ,hri_cl_wkr_sup_status_ct stt
758 ,(SELECT
759 month.month_id
760 ,add_months(to_date(SUBSTR(to_char(month.month_id), 1, 4) || '-' ||
761 SUBSTR(to_char(month.month_id), 6, 2), 'YYYY-MM'),
762 1) - 1 end_date
763 FROM fii_time_month month
764 ) mnth
765 WHERE mnth.month_id >= to_number(to_char(g_dbi_start_date, 'YYYYQMM'))
766 AND mnth.month_id <= to_number(to_char(g_sysdate, 'YYYYQMM'))
767 AND mgr.mgrs_level = p_mthd_range_lvl
768 AND mgr.mgrs_person_fk BETWEEN p_start_object_id
769 AND p_end_object_id
770 AND mgr.mgrs_person_fk = stt.person_id
771 AND mnth.end_date BETWEEN mgr.mgrs_date_start
772 AND mgr.mgrs_date_end
773 AND mnth.end_date BETWEEN stt.effective_start_date
774 AND ADD_MONTHS(stt.effective_end_date, 3)
775 AND stt.supervisor_flag = 'Y'
776 AND mgr.mgrs_date_start <= ADD_MONTHS(stt.effective_end_date, 3)
777 AND mgr.mgrs_date_end >= stt.effective_start_date;
778
779 BEGIN
780
781 FOR mgr_snap IN work_csr LOOP
782
783 process_snapshot
784 (p_manager_id => mgr_snap.manager_id,
785 p_snapshot_date => mgr_snap.snapshot_date);
786
787 END LOOP;
788
789 END process_range_full;
790
791
792 -- ----------------------------------------------------------------------------
793 -- Processes chunk in incremental refresh mode
794 -- ----------------------------------------------------------------------------
795 PROCEDURE process_range_incr(p_start_object_id IN NUMBER,
796 p_end_object_id IN NUMBER,
797 p_mthd_range_lvl IN NUMBER) IS
798
799 CURSOR work_csr IS
800 SELECT DISTINCT
801 mgr.mgrs_person_fk manager_id
802 ,mnth.end_date snapshot_date
803 FROM
804 hri_eq_wrkfc_evt_mgrh eq
805 ,hri_cs_mngrsc_ct mgr
806 ,hri_cl_wkr_sup_status_ct stt
807 ,(SELECT
808 month.month_id
809 ,add_months(to_date(SUBSTR(to_char(month.month_id), 1, 4) || '-' ||
810 SUBSTR(to_char(month.month_id), 6, 2), 'YYYY-MM'),
811 1) - 1 end_date
812 FROM fii_time_month month
813 ) mnth
814 WHERE eq.sup_person_id BETWEEN p_start_object_id
815 AND p_end_object_id
816 AND eq.sup_person_id = mgr.mgrs_person_fk
817 AND mgr.mgrs_level = p_mthd_range_lvl
818 AND mgr.mgrs_date_end >= eq.erlst_evnt_effective_date
819 AND mgr.mgrs_person_fk = stt.person_id
820 AND stt.supervisor_flag = 'Y'
821 AND stt.effective_end_date >= eq.erlst_evnt_effective_date
822 AND mnth.month_id >= to_number(to_char(eq.erlst_evnt_effective_date, 'YYYYQMM'))
823 AND mnth.month_id >= to_number(to_char(g_dbi_start_date, 'YYYYQMM'))
824 AND mnth.month_id <= to_number(to_char(g_sysdate, 'YYYYQMM'))
825 AND mnth.end_date BETWEEN mgr.mgrs_date_start
826 AND mgr.mgrs_date_end
827 AND mnth.end_date BETWEEN stt.effective_start_date
828 AND ADD_MONTHS(stt.effective_end_date, 3)
829 AND mgr.mgrs_date_start <= ADD_MONTHS(stt.effective_end_date, 3)
830 AND mgr.mgrs_date_end >= stt.effective_start_date;
831
832 BEGIN
833
834 -- Remove rows to be replaced for range
835 DELETE FROM hri_mds_wrkfc_mgrh_c01_ct snp
836 WHERE snp.rowid IN
837 (SELECT /*+ ORDERED */
838 snp2.rowid
839 FROM
840 hri_eq_wrkfc_evt_mgrh eq
841 ,hri_cs_mngrsc_ct mgr
842 ,hri_mds_wrkfc_mgrh_c01_ct snp2
843 WHERE eq.sup_person_id = snp2.sup_person_fk
844 AND eq.sup_person_id = mgr.mgrs_person_fk
845 AND mgr.mgrs_level = p_mthd_range_lvl
846 AND eq.erlst_evnt_effective_date <= mgr.mgrs_date_end
847 AND eq.sup_person_id BETWEEN p_start_object_id AND p_end_object_id
848 AND snp2.time_month_snp_fk >=
849 to_number(to_char(eq.erlst_evnt_effective_date, 'YYYYQMM'))
850 );
851
852 -- Loop through snapshots to reprocess
853 FOR mgr_snap IN work_csr LOOP
854
855 process_snapshot
856 (p_manager_id => mgr_snap.manager_id,
857 p_snapshot_date => mgr_snap.snapshot_date);
858
859 END LOOP;
860
861 END process_range_incr;
862
863
864 -- ----------------------------------------------------------------------------
865 -- PROCESS_RANGE
866 -- This procedure includes the logic required for processing the assignments
867 -- which have been included in the range. It is dynamically invoked by the
868 -- multithreading child process. It manages the multithreading ranges.
869 -- ----------------------------------------------------------------------------
870 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
871 ,retcode OUT NOCOPY NUMBER
872 ,p_mthd_action_id IN NUMBER
873 ,p_mthd_range_id IN NUMBER
874 ,p_mthd_range_lvl IN NUMBER
875 ,p_start_object_id IN NUMBER
876 ,p_end_object_id IN NUMBER) IS
877
878 BEGIN
879
880 -- Set the parameters
881 set_parameters
882 (p_mthd_action_id => p_mthd_action_id,
883 p_mthd_stage_code => 'PROCESS_RANGE');
884
885 -- Set sysdate parameter
886 g_sysdate := sysdate;
887
888 -- Initialize stored record count
889 g_row_count := 0;
890
891 -- Process range in corresponding refresh mode
892 IF g_full_refresh = 'Y' THEN
893 process_range_full
894 (p_start_object_id => p_start_object_id,
895 p_end_object_id => p_end_object_id,
896 p_mthd_range_lvl => p_mthd_range_lvl);
897 ELSE
898 process_range_incr
899 (p_start_object_id => p_start_object_id,
900 p_end_object_id => p_end_object_id,
901 p_mthd_range_lvl => p_mthd_range_lvl);
902 END IF;
903
904 -- Bulk insert any leftover rows
905 IF g_row_count > 0 THEN
906 bulk_insert_rows;
907 END IF;
908
909 END process_range;
910
911
912 -- ----------------------------------------------------------------------------
913 -- Removes duplicates in event queue
914 -- ----------------------------------------------------------------------------
915 PROCEDURE remove_eq_duplicates IS
916
917 BEGIN
918
919 -- Remove records in event queue that have duplicate records in event queue for
920 -- an earlier refresh date
921 DELETE FROM hri_eq_wrkfc_evt_mgrh eq
922 WHERE EXISTS
923 (SELECT null
924 FROM hri_eq_wrkfc_evt_mgrh eq2
925 WHERE eq2.sup_person_id = eq.sup_person_id
926 AND (eq2.erlst_evnt_effective_date < eq.erlst_evnt_effective_date
927 OR (eq2.rowid < eq.rowid AND
928 eq2.erlst_evnt_effective_date = eq.erlst_evnt_effective_date)));
929
930 END remove_eq_duplicates;
931
932 -- ----------------------------------------------------------------------------
933 -- Pre process entry point
934 -- ----------------------------------------------------------------------------
935 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
936 p_sqlstr OUT NOCOPY VARCHAR2) IS
937
938 l_sql_stmt VARCHAR2(2000);
939 l_dummy1 VARCHAR2(2000);
940 l_dummy2 VARCHAR2(2000);
941 l_schema VARCHAR2(400);
942
943 BEGIN
944
945 IF (p_mthd_action_id > -1) THEN
946
947 -- Set parameter globals
948 set_parameters
949 (p_mthd_action_id => p_mthd_action_id,
950 p_mthd_stage_code => 'PRE_PROCESS');
951
952 END IF;
953
954 -- Get HRI schema name - get_app_info populates l_schema
955 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
956 null;
957 END IF;
958
959 -- Disable WHO trigger
960 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_MGRH_C01_CT_WHO DISABLE');
961
962 -- ********************
963 -- Full Refresh Section
964 -- ********************
965 IF (g_full_refresh = 'Y' OR
966 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
967
968 -- Empty out absence dimension table
969 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MDS_WRKFC_MGRH_C01_CT';
970 EXECUTE IMMEDIATE(l_sql_stmt);
971
972 -- In shared HR mode do not return a SQL statement so that the
973 -- process_range and post_process will not be executed
974 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
975
976 -- Call post processing API
977 post_process
978 (p_mthd_action_id => p_mthd_action_id);
979
980 ELSE
981
982 -- Drop all the indexes on the table
983 hri_utl_ddl.log_and_drop_indexes
984 (p_application_short_name => 'HRI',
985 p_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT',
986 p_table_owner => l_schema,
987 p_index_excptn_lst => 'HRI_MDS_WRKFC_MGRH_C01_CT_N2');
988
989 -- Set the SQL statement for the entire range
990 p_sqlstr :=
991 'SELECT /*+ PARALLEL(mgr, default, default) */ DISTINCT
992 mgr.mgrs_person_fk object_id
993 ,mgr.mgrs_level object_lvl
994 FROM
995 hri_cs_mngrsc_ct mgr
996 WHERE mgr.mgrs_person_fk <> -1
997 AND mgr.mgrs_date_end >= hri_bpl_parameter.get_bis_global_start_date
998 AND mgr.mgrs_date_start <= trunc(sysdate)
999 ORDER BY
1000 mgr.mgrs_level DESC
1001 ,mgr.mgrs_person_fk';
1002
1003 END IF;
1004
1005 ELSE
1006
1007 -- Remove event queue duplicates
1008 remove_eq_duplicates;
1009
1010 -- Set the SQL statement for the incremental range
1011 p_sqlstr :=
1012 'SELECT DISTINCT
1013 mgr.mgrs_person_fk object_id
1014 ,mgr.mgrs_level object_lvl
1015 FROM
1016 hri_eq_wrkfc_evt_mgrh eq
1017 ,hri_cs_mngrsc_ct mgr
1018 WHERE eq.sup_person_id = mgr.mgrs_person_fk
1019 AND mgr.mgrs_date_end >= eq.erlst_evnt_effective_date
1020 AND mgr.mgrs_date_end >= hri_bpl_parameter.get_bis_global_start_date
1021 AND mgr.mgrs_date_start <= trunc(sysdate)
1022 ORDER BY
1023 mgr.mgrs_level DESC
1024 ,mgr.mgrs_person_fk';
1025
1026 END IF;
1027
1028 END pre_process;
1029
1030 -- ----------------------------------------------------------------------------
1031 -- Post process entry point
1032 -- ----------------------------------------------------------------------------
1033 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
1034
1035 l_sql_stmt VARCHAR2(2000);
1036 l_dummy1 VARCHAR2(2000);
1037 l_dummy2 VARCHAR2(2000);
1038 l_schema VARCHAR2(400);
1039
1040 BEGIN
1041
1042 IF (p_mthd_action_id > -1) THEN
1043
1044 -- Check parameters are set
1045 set_parameters
1046 (p_mthd_action_id => p_mthd_action_id,
1047 p_mthd_stage_code => 'POST_PROCESS');
1048
1049 -- Log process end
1050 hri_bpl_conc_log.record_process_start('HRI_MDS_WRKFC_MGRH_C01_CT');
1051 hri_bpl_conc_log.log_process_end(
1052 p_status => TRUE
1053 ,p_period_from => TRUNC(g_refresh_start_date)
1054 ,p_period_to => TRUNC(SYSDATE)
1055 ,p_attribute1 => g_full_refresh);
1056
1057 END IF;
1058
1059 -- Enable WHO trigger
1060 run_sql_stmt_noerr('ALTER TRIGGER HRI_MGR_WRKFC_MGRH_C01_CT_WHO ENABLE');
1061
1062 -- Get HRI schema name - get_app_info populates l_schema
1063 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
1064 null;
1065 END IF;
1066
1067 -- Recreate indexes in full refresh mode
1068 IF (g_full_refresh = 'Y') THEN
1069 hri_utl_ddl.recreate_indexes
1070 (p_application_short_name => 'HRI',
1071 p_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT',
1072 p_table_owner => l_schema);
1073
1074 END IF;
1075
1076 -- Empty out workforce manager summary event queue
1077 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_EQ_WRKFC_EVT_MGRH';
1078 EXECUTE IMMEDIATE(l_sql_stmt);
1079
1080 END post_process;
1081
1082 -- Populates table in a single thread
1083 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
1084
1085 CURSOR chunk_csr IS
1086 SELECT
1087 mthd_range_id
1088 ,object_lvl
1089 ,min(object_id) start_object_id
1090 ,max(object_id) end_object_id
1091 FROM
1092 (SELECT
1093 1000 - object_lvl + CEIL(ROWNUM / 20) mthd_range_id
1094 ,object_lvl
1095 ,object_id
1096 FROM
1097 (SELECT object_id, object_lvl
1098 FROM
1099 (SELECT /*+ PARALLEL(mgr, default, default) */ DISTINCT
1100 mgr.mgrs_person_fk object_id
1101 ,mgr.mgrs_level object_lvl
1102 FROM
1103 hri_cs_mngrsc_ct mgr
1104 WHERE mgr.mgrs_date_end >= hri_bpl_parameter.get_bis_global_start_date
1105 AND mgr.mgrs_date_start <= trunc(sysdate))
1106 ORDER BY object_lvl DESC, object_id))
1107 GROUP BY
1108 mthd_range_id
1109 ,object_lvl
1110 ORDER BY object_lvl DESC, mthd_range_id;
1111
1112 CURSOR chunk_csr_incr IS
1113 SELECT
1114 chunk_no
1115 ,MIN(object_id) start_object_id
1116 ,MAX(object_id) end_object_id
1117 FROM
1118 (SELECT
1119 object_id
1120 ,CEIL(ROWNUM / 20) chunk_no
1121 FROM
1122 (SELECT DISTINCT
1123 (mgr.person_id * g_no_qtrs_to_process) +
1124 (months_between(qtr.start_date,
1125 trunc(g_dbi_start_date,'Q')) / 3) object_id
1126 FROM
1127 hri_eq_wrkfc_evt_mgrh eq
1128 ,hri_cl_wkr_sup_status_ct mgr
1129 ,fii_time_qtr qtr
1130 WHERE eq.sup_person_id = mgr.person_id
1131 AND qtr.end_date >= eq.erlst_evnt_effective_date
1132 AND mgr.supervisor_flag = 'Y'
1133 AND qtr.end_date BETWEEN mgr.effective_start_date
1134 AND ADD_MONTHS(mgr.effective_end_date, 3)
1135 AND qtr.end_date >= hri_bpl_parameter.get_bis_global_start_date
1136 AND qtr.start_date <= trunc(sysdate)
1137 ORDER BY 1
1138 ) tab
1139 ) chunks
1140 GROUP BY
1141 chunk_no;
1142
1143 l_dummy VARCHAR2(2000);
1144 l_first_qtr_start_date DATE;
1145 l_last_qtr_start_date DATE;
1146 l_max_sup_lvl PLS_INTEGER;
1147
1148 BEGIN
1149
1150 g_full_refresh := p_full_refresh_flag;
1151 g_refresh_to_date := trunc(sysdate);
1152 g_sysdate := trunc(sysdate);
1153 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
1154 l_first_qtr_start_date := trunc(g_dbi_start_date, 'Q');
1155 l_last_qtr_start_date := trunc(g_refresh_to_date, 'Q');
1156 g_no_qtrs_to_process := (MONTHS_BETWEEN(l_last_qtr_start_date,
1157 l_first_qtr_start_date) / 3) + 1;
1158
1159 pre_process(-1, l_dummy);
1160
1161 IF g_full_refresh = 'Y' THEN
1162 FOR chunk_rec IN chunk_csr LOOP
1163 process_range_full
1164 (chunk_rec.start_object_id
1165 ,chunk_rec.end_object_id
1166 ,chunk_rec.object_lvl);
1167 END LOOP;
1168 ELSE
1169 FOR chunk_rec IN chunk_csr_incr LOOP
1170 process_range_incr(chunk_rec.start_object_id, chunk_rec.end_object_id, 1);
1171 END LOOP;
1172 END IF;
1173
1174
1175 post_process(-1);
1176
1177 END single_thread_process;
1178
1179 END hri_opl_wrkfc_evt_mgrh;