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