[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_REC_CAND_PIPLN
Source
1 PACKAGE BODY hri_opl_rec_cand_pipln AS
2 /* $Header: hriprpipln.pkb 120.7.12000000.2 2007/04/12 13:28:03 smohapat noship $ */
3
4 TYPE g_info_rec_type IS RECORD
5 (event_seq NUMBER
6 ,asg_id NUMBER
7 ,psn_id NUMBER
8 ,appl_ended_ind NUMBER
9 ,idx_strt_date DATE
10 ,appl_strt_date DATE
11 ,asmt_strt_date DATE
12 ,asmt_int1_date DATE
13 ,asmt_int2_date DATE
14 ,asmt_end_date DATE
15 ,offr_extd_date DATE
16 ,offr_rjct_date DATE
17 ,offr_acpt_date DATE
18 ,appl_end_date DATE
19 ,hire_date DATE
20 ,appl_term_date DATE
21 ,pow1_date DATE
22 ,perf_date DATE
23 ,emp_sprtn_date DATE
24 ,curr_strt_date DATE
25 ,appl_term_rsn VARCHAR2(30)
26 ,perf_norm_rtng NUMBER
27 ,perf_band NUMBER
28 ,latest_stage VARCHAR2(30)
29 ,last_apl_idx NUMBER);
30
31 TYPE g_apl_event_rec_type IS RECORD
32 (time_day_evt_fk DATE
33 ,time_day_evt_end_fk DATE
34 ,time_event DATE
35 ,person_cand_fk NUMBER
36 ,person_mngr_fk NUMBER
37 ,person_rcrt_fk NUMBER
38 ,person_rmgr_fk NUMBER
39 ,person_auth_fk NUMBER
40 ,person_refr_fk NUMBER
41 ,person_rsed_fk NUMBER
42 ,person_mrgd_fk NUMBER
43 ,org_organztn_fk NUMBER
44 ,org_organztn_mrgd_fk NUMBER
45 ,org_organztn_recr_fk NUMBER
46 ,geo_location_fk NUMBER
47 ,job_job_fk NUMBER
48 ,grd_grade_fk NUMBER
49 ,pos_position_fk NUMBER
50 ,prfm_perfband_fk NUMBER
51 ,rvac_vacncy_fk NUMBER
52 ,ract_recactvy_fk NUMBER
53 ,rern_recevtrn_fk VARCHAR2(30)
54 ,tarn_trmaplrn_fk VARCHAR2(30)
55 ,headcount NUMBER
56 ,fte NUMBER
57 ,perf_norm_rtng NUMBER
58 ,adt_application_id NUMBER
59 ,adt_business_group_id NUMBER
60 ,event_date DATE
61 ,stage_start_date DATE
62 ,assignment_status_type_id NUMBER
63 ,user_status VARCHAR2(80)
64 ,per_system_status VARCHAR2(30));
65
66 TYPE g_ind_rec_type IS RECORD
67 (appl_ind NUMBER
68 ,appl_new_ind NUMBER
69 ,appl_emp_ind NUMBER
70 ,appl_cwk_ind NUMBER
71 ,appl_strt_evnt_ind NUMBER
72 ,appl_strt_nevnt_ind NUMBER
73 ,asmt_strt_evnt_ind NUMBER
74 ,asmt_strt_nevnt_ind NUMBER
75 ,asmt_end_evnt_ind NUMBER
76 ,asmt_end_nevnt_ind NUMBER
77 ,offr_extd_evnt_ind NUMBER
78 ,offr_extd_nevnt_ind NUMBER
79 ,offr_rjct_evnt_ind NUMBER
80 ,offr_rjct_nevnt_ind NUMBER
81 ,offr_acpt_evnt_ind NUMBER
82 ,offr_acpt_nevnt_ind NUMBER
83 ,appl_term_evnt_ind NUMBER
84 ,appl_term_nevnt_ind NUMBER
85 ,appl_term_vol_evnt_ind NUMBER
86 ,appl_term_vol_nevnt_ind NUMBER
87 ,appl_term_invol_evnt_ind NUMBER
88 ,appl_term_invol_nevnt_ind NUMBER
89 ,appl_hire_evnt_ind NUMBER
90 ,appl_hire_nevnt_ind NUMBER
91 ,hire_evnt_ind NUMBER
92 ,hire_nevnt_ind NUMBER
93 ,pow1_end_evnt_ind NUMBER
94 ,pow1_end_nevnt_ind NUMBER
95 ,perf_rtng_evnt_ind NUMBER
96 ,perf_rtng_nevnt_ind NUMBER
97 ,emp_sprtn_evnt_ind NUMBER
98 ,emp_sprtn_nevnt_ind NUMBER
99 ,init_appl_stg_ind NUMBER
100 ,asmt_stg_ind NUMBER
101 ,offr_extd_stg_ind NUMBER
102 ,strt_pndg_stg_ind NUMBER
103 ,hire_stg_ind NUMBER
104 ,hire_org_chng_ind NUMBER
105 ,hire_job_chng_ind NUMBER
106 ,hire_grd_chng_ind NUMBER
107 ,hire_pos_chng_ind NUMBER
108 ,hire_loc_chng_ind NUMBER
109 ,current_record_ind NUMBER);
110
111 TYPE g_master_evt_rec_type IS RECORD
112 (apl_idx NUMBER
113 ,event_code VARCHAR2(30)
114 ,stage_code VARCHAR2(30)
115 ,event_seq NUMBER
116 ,cnstrct_evt VARCHAR2(30)
117 ,event_ind g_ind_rec_type);
118
119 -- Table Types
120 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(80)
121 INDEX BY BINARY_INTEGER;
122 TYPE g_number_tab_type IS TABLE OF NUMBER
123 INDEX BY BINARY_INTEGER;
124 TYPE g_date_tab_type IS TABLE OF DATE
125 INDEX BY BINARY_INTEGER;
126 TYPE g_apl_tab_type IS TABLE OF g_apl_event_rec_type
127 INDEX BY BINARY_INTEGER;
128 TYPE g_master_evt_tab_type IS TABLE OF g_master_evt_rec_type
129 INDEX BY BINARY_INTEGER;
130 TYPE g_master_tab_type IS TABLE OF g_master_evt_tab_type
131 INDEX BY BINARY_INTEGER;
132 TYPE g_event_tab_type IS TABLE OF VARCHAR2(30)
133 INDEX BY VARCHAR2(30);
134
135 -- Event cache
136 g_event_cache g_event_tab_type;
137 g_empty_event_cache g_event_tab_type;
138
139 -- PL/SQL table representing database table
140 g_time_day_evt_fk g_date_tab_type;
141 g_time_day_evt_end_fk g_date_tab_type;
142 g_time_day_stg_evt_eff_end_fk g_date_tab_type;
143 g_person_cand_fk g_number_tab_type;
144 g_person_mngr_fk g_number_tab_type;
145 g_person_rcrt_fk g_number_tab_type;
146 g_person_rmgr_fk g_number_tab_type;
147 g_person_auth_fk g_number_tab_type;
148 g_person_refr_fk g_number_tab_type;
149 g_person_rsed_fk g_number_tab_type;
150 g_person_mrgd_fk g_number_tab_type;
151 g_org_organztn_fk g_number_tab_type;
152 g_org_organztn_mrgd_fk g_number_tab_type;
153 g_org_organztn_recr_fk g_number_tab_type;
154 g_geo_location_fk g_number_tab_type;
155 g_job_job_fk g_number_tab_type;
156 g_grd_grade_fk g_number_tab_type;
157 g_pos_position_fk g_number_tab_type;
158 g_prfm_perfband_fk g_number_tab_type;
159 g_rvac_vacncy_fk g_number_tab_type;
160 g_ract_recactvy_fk g_number_tab_type;
161 g_rev_recevent_fk g_varchar2_tab_type;
162 g_rern_recevtrn_fk g_varchar2_tab_type;
163 g_tarn_trmaplrn_fk g_varchar2_tab_type;
164 g_headcount g_number_tab_type;
165 g_fte g_number_tab_type;
166 g_nrmlsd_perf_rtng g_number_tab_type;
167 g_event_seq g_number_tab_type;
168 g_appl_ind g_number_tab_type;
169 g_appl_new_ind g_number_tab_type;
170 g_appl_emp_ind g_number_tab_type;
171 g_appl_cwk_ind g_number_tab_type;
172 g_appl_strt_evnt_ind g_number_tab_type;
173 g_appl_strt_nevnt_ind g_number_tab_type;
174 g_asmt_strt_evnt_ind g_number_tab_type;
175 g_asmt_strt_nevnt_ind g_number_tab_type;
176 g_asmt_end_evnt_ind g_number_tab_type;
177 g_asmt_end_nevnt_ind g_number_tab_type;
178 g_offr_extd_evnt_ind g_number_tab_type;
179 g_offr_extd_nevnt_ind g_number_tab_type;
180 g_offr_rjct_evnt_ind g_number_tab_type;
181 g_offr_rjct_nevnt_ind g_number_tab_type;
182 g_offr_acpt_evnt_ind g_number_tab_type;
183 g_offr_acpt_nevnt_ind g_number_tab_type;
184 g_appl_term_evnt_ind g_number_tab_type;
185 g_appl_term_nevnt_ind g_number_tab_type;
186 g_appl_term_vol_evnt_ind g_number_tab_type;
187 g_appl_term_vol_nevnt_ind g_number_tab_type;
188 g_appl_term_invol_evnt_ind g_number_tab_type;
189 g_appl_term_invol_nevnt_ind g_number_tab_type;
190 g_appl_hire_evnt_ind g_number_tab_type;
191 g_appl_hire_nevnt_ind g_number_tab_type;
192 g_hire_evnt_ind g_number_tab_type;
193 g_hire_nevnt_ind g_number_tab_type;
194 g_pow1_end_evnt_ind g_number_tab_type;
195 g_pow1_end_nevnt_ind g_number_tab_type;
196 g_perf_rtng_evnt_ind g_number_tab_type;
197 g_perf_rtng_nevnt_ind g_number_tab_type;
198 g_emp_sprtn_evnt_ind g_number_tab_type;
199 g_emp_sprtn_nevnt_ind g_number_tab_type;
200 g_hire_org_chng_ind g_number_tab_type;
201 g_hire_job_chng_ind g_number_tab_type;
202 g_hire_pos_chng_ind g_number_tab_type;
203 g_hire_grd_chng_ind g_number_tab_type;
204 g_hire_loc_chng_ind g_number_tab_type;
205 g_current_record_ind g_number_tab_type;
206 g_current_stage_strt_ind g_number_tab_type;
207 g_gen_record_ind g_number_tab_type;
208 g_appl_strt_to_asmt_strt_days g_number_tab_type;
209 g_appl_strt_to_asmt_end_days g_number_tab_type;
210 g_appl_strt_to_offr_extd_days g_number_tab_type;
211 g_appl_strt_to_offr_rjct_days g_number_tab_type;
212 g_appl_strt_to_offr_acpt_days g_number_tab_type;
213 g_appl_strt_to_hire_days g_number_tab_type;
214 g_appl_strt_to_term_days g_number_tab_type;
215 g_init_appl_stg_ind g_number_tab_type;
216 g_asmt_stg_ind g_number_tab_type;
217 g_offr_extd_stg_ind g_number_tab_type;
218 g_strt_pndg_stg_ind g_number_tab_type;
219 g_hire_stg_ind g_number_tab_type;
220 g_appl_strt_date g_date_tab_type;
221 g_asmt_strt_date g_date_tab_type;
222 g_asmt_end_date g_date_tab_type;
223 g_offr_extd_date g_date_tab_type;
224 g_offr_rjct_date g_date_tab_type;
225 g_offr_acpt_date g_date_tab_type;
226 g_appl_term_date g_date_tab_type;
227 g_hire_date g_date_tab_type;
228 g_emp_sprtn_date g_date_tab_type;
229 g_event_date g_date_tab_type;
230 g_stage_start_date g_date_tab_type;
231 g_adt_assignment_id g_number_tab_type;
232 g_adt_asg_effctv_start_date g_date_tab_type;
233 g_adt_asg_effctv_end_date g_date_tab_type;
234 g_adt_application_id g_number_tab_type;
235 g_no_rows PLS_INTEGER;
236
237
238 -- Global HRI Multithreading Array
239 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
240
241 -- Global parameters
242 g_refresh_start_date DATE;
243 g_refresh_to_date DATE;
244 g_full_refresh VARCHAR2(30);
245 g_sysdate DATE;
246 g_user NUMBER;
247 g_dbi_start_date DATE;
248 g_end_of_time DATE := hr_general.end_of_time;
249 g_debug BOOLEAN;
250
251 -- Length of POW1 Band
252 g_pow1_no_days NUMBER;
253 g_pow1_no_months NUMBER;
254
255
256 -- ----------------------------------------------------------------------------
257 -- Debug Output
258 -- ----------------------------------------------------------------------------
259 PROCEDURE dbg(p_text IN VARCHAR2) IS
260
261 BEGIN
262
263 IF g_debug THEN
264 hri_bpl_conc_log.dbg(p_text);
265 END IF;
266
267 --dbms_output.put_line(p_text);
268
269 END dbg;
270
271 -- ----------------------------------------------------------------------------
272 -- Runs given sql statement dynamically
273 -- ----------------------------------------------------------------------------
274 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
275
276 BEGIN
277
278 EXECUTE IMMEDIATE p_sql_stmt;
279
280 EXCEPTION WHEN OTHERS THEN
281
282 null;
283
284 END run_sql_stmt_noerr;
285
286
287 -- ----------------------------------------------------------------------------
288 -- Initializes an indicator record
289 -- ----------------------------------------------------------------------------
290 FUNCTION initialize_indicator_rec RETURN g_ind_rec_type IS
291
292 l_ind_rec g_ind_rec_type;
293
294 BEGIN
295
296 l_ind_rec.appl_ind := 0;
297 l_ind_rec.appl_new_ind := 0;
298 l_ind_rec.appl_emp_ind := 0;
299 l_ind_rec.appl_cwk_ind := 0;
300 l_ind_rec.appl_strt_evnt_ind := 0;
301 l_ind_rec.appl_strt_nevnt_ind := 0;
302 l_ind_rec.asmt_strt_evnt_ind := 0;
303 l_ind_rec.asmt_strt_nevnt_ind := 0;
304 l_ind_rec.asmt_end_evnt_ind := 0;
305 l_ind_rec.asmt_end_nevnt_ind := 0;
306 l_ind_rec.offr_extd_evnt_ind := 0;
307 l_ind_rec.offr_extd_nevnt_ind := 0;
308 l_ind_rec.offr_rjct_evnt_ind := 0;
309 l_ind_rec.offr_rjct_nevnt_ind := 0;
310 l_ind_rec.offr_acpt_evnt_ind := 0;
311 l_ind_rec.offr_acpt_nevnt_ind := 0;
312 l_ind_rec.appl_term_evnt_ind := 0;
313 l_ind_rec.appl_term_nevnt_ind := 0;
314 l_ind_rec.appl_term_vol_evnt_ind := 0;
315 l_ind_rec.appl_term_vol_nevnt_ind := 0;
316 l_ind_rec.appl_term_invol_evnt_ind := 0;
317 l_ind_rec.appl_term_invol_nevnt_ind := 0;
318 l_ind_rec.appl_hire_evnt_ind := 0;
319 l_ind_rec.appl_hire_nevnt_ind := 0;
320 l_ind_rec.hire_evnt_ind := 0;
321 l_ind_rec.hire_nevnt_ind := 0;
322 l_ind_rec.pow1_end_evnt_ind := 0;
323 l_ind_rec.pow1_end_nevnt_ind := 0;
324 l_ind_rec.perf_rtng_evnt_ind := 0;
325 l_ind_rec.perf_rtng_nevnt_ind := 0;
326 l_ind_rec.emp_sprtn_evnt_ind := 0;
327 l_ind_rec.emp_sprtn_nevnt_ind := 0;
328 l_ind_rec.init_appl_stg_ind := 0;
329 l_ind_rec.asmt_stg_ind := 0;
330 l_ind_rec.offr_extd_stg_ind := 0;
331 l_ind_rec.strt_pndg_stg_ind := 0;
332 l_ind_rec.hire_stg_ind := 0;
333 l_ind_rec.hire_org_chng_ind := 0;
334 l_ind_rec.hire_job_chng_ind := 0;
335 l_ind_rec.hire_grd_chng_ind := 0;
336 l_ind_rec.hire_pos_chng_ind := 0;
337 l_ind_rec.hire_loc_chng_ind := 0;
338 l_ind_rec.current_record_ind := 0;
339
340 RETURN l_ind_rec;
341
342 END initialize_indicator_rec;
343
344
345 -- ----------------------------------------------------------------------------
346 -- Sets global parameters from multi-threading process parameters
347 -- ----------------------------------------------------------------------------
348 PROCEDURE reset_event_cache IS
349
350 BEGIN
351
352 g_event_cache := g_empty_event_cache;
353
354 END reset_event_cache;
355
356 -- ----------------------------------------------------------------------------
357 -- Returns the event index for the last record on the master table
358 -- ----------------------------------------------------------------------------
359 FUNCTION get_event_idx(p_master_tab IN g_master_tab_type,
360 p_master_idx IN NUMBER)
361 RETURN NUMBER IS
362
363 l_event_idx NUMBER;
364
365 BEGIN
366
367 -- Trap exception when master table is empty at specified index
368 BEGIN
369
370 -- Get last index value
371 l_event_idx := p_master_tab(p_master_idx).LAST;
372
373 -- This execption should be raised automatically
374 IF l_event_idx IS NULL THEN
375 RAISE no_data_found;
376 END IF;
377
378 EXCEPTION WHEN OTHERS THEN
379
380 -- If not found return 0
381 l_event_idx := 0;
382
383 END;
384
385 RETURN l_event_idx;
386
387 END get_event_idx;
388
389 -- ----------------------------------------------------------------------------
390 -- Sets global parameters from multi-threading process parameters
391 -- ----------------------------------------------------------------------------
392 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
393 p_mthd_stage_code IN VARCHAR2) IS
394
395 l_dbi_collection_start_date DATE;
396
397 CURSOR pow1_csr IS
398 SELECT
399 CASE WHEN set_uom = 'Years'
400 THEN (band_range_high - band_range_low) * 12
401 WHEN set_uom = 'Months'
402 THEN (band_range_high - band_range_low)
403 ELSE to_number(null)
404 END pow1_no_months
405 ,CASE WHEN set_uom = 'Weeks'
406 THEN (band_range_high - band_range_low) * 7
407 WHEN set_uom = 'Days'
408 THEN (band_range_high - band_range_low)
409 ELSE to_number(null)
410 END pow1_no_days
411 FROM hri_cs_pow_band_ct
412 WHERE band_sequence = 1
413 AND wkth_wktyp_sk_fk = 'EMP';
414
415 BEGIN
416
417 -- If parameters haven't already been set, then set them
418 IF (p_mthd_action_id = -1) THEN
419
420 g_sysdate := sysdate;
421 g_user := fnd_global.user_id;
422
423 OPEN pow1_csr;
424 FETCH pow1_csr INTO g_pow1_no_months, g_pow1_no_days;
425 CLOSE pow1_csr;
426
427 ELSIF (g_refresh_start_date IS NULL OR
428 p_mthd_stage_code = 'PRE_PROCESS') THEN
429
430 l_dbi_collection_start_date :=
431 hri_oltp_conc_param.get_date_parameter_value
432 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
433 p_process_table_name => 'HRI_MB_REC_CAND_PIPLN_CT');
434
435 -- If called for the first time set the defaulted parameters
436 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
437
438 g_full_refresh :=
439 hri_oltp_conc_param.get_parameter_value
440 (p_parameter_name => 'FULL_REFRESH',
441 p_process_table_name => 'HRI_MB_REC_CAND_PIPLN_CT');
442
443 -- Log defaulted parameters so the slave processes pick up
444 hri_opl_multi_thread.update_parameters
445 (p_mthd_action_id => p_mthd_action_id,
446 p_full_refresh => g_full_refresh,
447 p_global_start_date => l_dbi_collection_start_date);
448
449 END IF;
450
451 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
452 (p_mthd_action_id);
453 g_refresh_start_date := g_mthd_action_array.collect_from_date;
454 g_refresh_to_date := g_mthd_action_array.collect_to_date;
455 g_full_refresh := g_mthd_action_array.full_refresh_flag;
456 g_sysdate := sysdate;
457 g_user := fnd_global.user_id;
458 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
459 g_debug := FALSE;
460
461 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
462 hri_bpl_conc_log.dbg('Collect from: N/A');
463
464 OPEN pow1_csr;
465 FETCH pow1_csr INTO g_pow1_no_months, g_pow1_no_days;
466 CLOSE pow1_csr;
467
468 END IF;
469
470 -- Currently only full refresh is supported
471 g_full_refresh := 'Y';
472
473 END set_parameters;
474
475
476 -- ----------------------------------------------------------------------------
477 -- Commits records to DB from PL/SQL tables
478 -- ----------------------------------------------------------------------------
479 PROCEDURE bulk_insert_rows IS
480
481 BEGIN
482
483 IF g_no_rows > 0 THEN
484
485 FORALL i IN 1..g_no_rows
486 INSERT INTO hri_mb_rec_cand_pipln_ct
487 (time_day_evt_fk,
488 time_day_evt_end_fk,
489 time_day_stg_evt_eff_end_fk,
490 per_person_cand_fk,
491 per_person_mngr_fk,
492 per_person_rcrt_fk,
493 per_person_rmgr_fk,
494 per_person_auth_fk,
495 per_person_refr_fk,
496 per_person_rsed_fk,
497 per_person_mrgd_fk,
498 org_organztn_fk,
499 org_organztn_mrgd_fk,
500 org_organztn_recr_fk,
501 geo_location_fk,
502 job_job_fk,
503 grd_grade_fk,
504 pos_position_fk,
505 prfm_perfband_fk,
506 rvac_vacncy_fk,
507 ract_recactvy_fk,
508 rev_recevent_fk,
509 rern_recevtrn_fk,
510 tarn_trmaplrn_fk,
511 headcount,
512 fte,
513 post_hire_nrmlsd_perf_rtng,
514 event_seq,
515 appl_ind,
516 appl_new_ind,
517 appl_emp_ind,
518 appl_cwk_ind,
519 appl_strt_evnt_ind,
520 appl_strt_nevnt_ind,
521 asmt_strt_evnt_ind,
522 asmt_strt_nevnt_ind,
523 asmt_end_evnt_ind,
524 asmt_end_nevnt_ind,
525 offr_extd_evnt_ind,
526 offr_extd_nevnt_ind,
527 offr_rjct_evnt_ind,
528 offr_rjct_nevnt_ind,
529 offr_acpt_evnt_ind,
530 offr_acpt_nevnt_ind,
531 appl_term_evnt_ind,
532 appl_term_nevnt_ind,
533 appl_term_vol_evnt_ind,
534 appl_term_vol_nevnt_ind,
535 appl_term_invol_evnt_ind,
536 appl_term_invol_nevnt_ind,
537 appl_hire_evnt_ind,
538 appl_hire_nevnt_ind,
539 hire_evnt_ind,
540 hire_nevnt_ind,
541 post_hire_pow1_end_evnt_ind,
542 post_hire_pow1_end_nevnt_ind,
543 post_hire_perf_evnt_ind,
544 post_hire_perf_nevnt_ind,
545 emp_sprtn_evnt_ind,
546 emp_sprtn_nevnt_ind,
547 hire_org_chng_ind,
548 hire_job_chng_ind,
549 hire_pos_chng_ind,
550 hire_grd_chng_ind,
551 hire_loc_chng_ind,
552 current_record_ind,
553 current_stage_strt_ind,
554 gen_record_ind,
555 appl_strt_to_asmt_strt_days,
556 appl_strt_to_asmt_end_days,
557 appl_strt_to_offr_extd_days,
558 appl_strt_to_offr_rjct_days,
559 appl_strt_to_offr_acpt_days,
560 appl_strt_to_hire_days,
561 appl_strt_to_term_days,
562 init_appl_stg_ind,
563 asmt_stg_ind,
564 offr_extd_stg_ind,
565 strt_pndg_stg_ind,
566 hire_stg_ind,
567 appl_strt_date,
568 asmt_strt_date,
569 asmt_end_date,
570 offr_extd_date,
571 offr_rjct_date,
572 offr_acpt_date,
573 appl_term_date,
574 hire_date,
575 emp_sprtn_date,
576 event_date,
577 stage_start_date,
578 adt_assignment_id,
579 adt_asg_effctv_start_date,
580 adt_asg_effctv_end_date,
581 adt_application_id,
582 last_update_date,
583 last_updated_by,
584 last_update_login,
585 created_by,
586 creation_date)
587 VALUES
588 (g_time_day_evt_fk(i),
589 g_time_day_evt_end_fk(i),
590 g_time_day_stg_evt_eff_end_fk(i),
591 g_person_cand_fk(i),
592 g_person_mngr_fk(i),
593 g_person_rcrt_fk(i),
594 g_person_rmgr_fk(i),
595 g_person_auth_fk(i),
596 g_person_refr_fk(i),
597 g_person_rsed_fk(i),
598 g_person_mrgd_fk(i),
599 g_org_organztn_fk(i),
600 g_org_organztn_mrgd_fk(i),
601 g_org_organztn_recr_fk(i),
602 g_geo_location_fk(i),
603 g_job_job_fk(i),
604 g_grd_grade_fk(i),
605 g_pos_position_fk(i),
606 g_prfm_perfband_fk(i),
607 g_rvac_vacncy_fk(i),
608 g_ract_recactvy_fk(i),
609 g_rev_recevent_fk(i),
610 g_rern_recevtrn_fk(i),
611 g_tarn_trmaplrn_fk(i),
612 g_headcount(i),
613 g_fte(i),
614 g_nrmlsd_perf_rtng(i),
615 g_event_seq(i),
616 g_appl_ind(i),
617 g_appl_new_ind(i),
618 g_appl_emp_ind(i),
619 g_appl_cwk_ind(i),
620 g_appl_strt_evnt_ind(i),
621 g_appl_strt_nevnt_ind(i),
622 g_asmt_strt_evnt_ind(i),
623 g_asmt_strt_nevnt_ind(i),
624 g_asmt_end_evnt_ind(i),
625 g_asmt_end_nevnt_ind(i),
626 g_offr_extd_evnt_ind(i),
627 g_offr_extd_nevnt_ind(i),
628 g_offr_rjct_evnt_ind(i),
629 g_offr_rjct_nevnt_ind(i),
630 g_offr_acpt_evnt_ind(i),
631 g_offr_acpt_nevnt_ind(i),
632 g_appl_term_evnt_ind(i),
633 g_appl_term_nevnt_ind(i),
634 g_appl_term_vol_evnt_ind(i),
635 g_appl_term_vol_nevnt_ind(i),
636 g_appl_term_invol_evnt_ind(i),
637 g_appl_term_invol_nevnt_ind(i),
638 g_appl_hire_evnt_ind(i),
639 g_appl_hire_nevnt_ind(i),
640 g_hire_evnt_ind(i),
641 g_hire_nevnt_ind(i),
642 g_pow1_end_evnt_ind(i),
643 g_pow1_end_nevnt_ind(i),
644 g_perf_rtng_evnt_ind(i),
645 g_perf_rtng_nevnt_ind(i),
646 g_emp_sprtn_evnt_ind(i),
647 g_emp_sprtn_nevnt_ind(i),
648 g_hire_org_chng_ind(i),
649 g_hire_job_chng_ind(i),
650 g_hire_pos_chng_ind(i),
651 g_hire_grd_chng_ind(i),
652 g_hire_loc_chng_ind(i),
653 g_current_record_ind(i),
654 g_current_stage_strt_ind(i),
655 g_gen_record_ind(i),
656 g_appl_strt_to_asmt_strt_days(i),
657 g_appl_strt_to_asmt_end_days(i),
658 g_appl_strt_to_offr_extd_days(i),
659 g_appl_strt_to_offr_rjct_days(i),
660 g_appl_strt_to_offr_acpt_days(i),
661 g_appl_strt_to_hire_days(i),
662 g_appl_strt_to_term_days(i),
663 g_init_appl_stg_ind(i),
664 g_asmt_stg_ind(i),
665 g_offr_extd_stg_ind(i),
666 g_strt_pndg_stg_ind(i),
667 g_hire_stg_ind(i),
668 g_appl_strt_date(i),
669 g_asmt_strt_date(i),
670 g_asmt_end_date(i),
671 g_offr_extd_date(i),
672 g_offr_rjct_date(i),
673 g_offr_acpt_date(i),
674 g_appl_term_date(i),
675 g_hire_date(i),
676 g_emp_sprtn_date(i),
677 g_event_date(i),
678 g_stage_start_date(i),
679 g_adt_assignment_id(i),
680 g_adt_asg_effctv_start_date(i),
681 g_adt_asg_effctv_end_date(i),
682 g_adt_application_id(i),
683 g_sysdate,
684 g_user,
685 g_user,
686 g_user,
687 g_sysdate);
688
689 -- Commit
690 COMMIT;
691
692 -- Reset counter
693 g_no_rows := 0;
694
695 END IF;
696
697 END bulk_insert_rows;
698
699
700 -- ----------------------------------------------------------------------------
701 -- Inserts a row into PL/SQL tables
702 -- ----------------------------------------------------------------------------
703 PROCEDURE insert_row
704 (p_time_day_evt_fk IN DATE,
705 p_time_day_evt_end_fk IN DATE,
706 p_time_day_stg_evt_eff_end_fk IN DATE,
707 p_person_cand_fk IN NUMBER,
708 p_person_mngr_fk IN NUMBER,
709 p_person_rcrt_fk IN NUMBER,
710 p_person_rmgr_fk IN NUMBER,
711 p_person_auth_fk IN NUMBER,
712 p_person_refr_fk IN NUMBER,
713 p_person_rsed_fk IN NUMBER,
714 p_person_mrgd_fk IN NUMBER,
715 p_org_organztn_fk IN NUMBER,
716 p_org_organztn_mrgd_fk IN NUMBER,
717 p_org_organztn_recr_fk IN NUMBER,
718 p_geo_location_fk IN NUMBER,
719 p_job_job_fk IN NUMBER,
720 p_grd_grade_fk IN NUMBER,
721 p_pos_position_fk IN NUMBER,
722 p_prfm_perfband_fk IN NUMBER,
723 p_rvac_vacncy_fk IN NUMBER,
724 p_ract_recactvy_fk IN NUMBER,
725 p_rev_recevent_fk IN VARCHAR2,
726 p_rern_recevtrn_fk IN VARCHAR2,
727 p_tarn_trmaplrn_fk IN VARCHAR2,
728 p_headcount IN NUMBER,
729 p_fte IN NUMBER,
730 p_nrmlsd_perf_rtng IN NUMBER,
731 p_event_seq IN NUMBER,
732 p_appl_ind IN NUMBER,
733 p_appl_new_ind IN NUMBER,
734 p_appl_emp_ind IN NUMBER,
735 p_appl_cwk_ind IN NUMBER,
736 p_appl_strt_evnt_ind IN NUMBER,
737 p_appl_strt_nevnt_ind IN NUMBER,
738 p_asmt_strt_evnt_ind IN NUMBER,
739 p_asmt_strt_nevnt_ind IN NUMBER,
740 p_asmt_end_evnt_ind IN NUMBER,
741 p_asmt_end_nevnt_ind IN NUMBER,
742 p_offr_extd_evnt_ind IN NUMBER,
743 p_offr_extd_nevnt_ind IN NUMBER,
744 p_offr_rjct_evnt_ind IN NUMBER,
745 p_offr_rjct_nevnt_ind IN NUMBER,
746 p_offr_acpt_evnt_ind IN NUMBER,
747 p_offr_acpt_nevnt_ind IN NUMBER,
748 p_appl_term_evnt_ind IN NUMBER,
749 p_appl_term_nevnt_ind IN NUMBER,
750 p_appl_term_vol_evnt_ind IN NUMBER,
751 p_appl_term_vol_nevnt_ind IN NUMBER,
752 p_appl_term_invol_evnt_ind IN NUMBER,
753 p_appl_term_invol_nevnt_ind IN NUMBER,
754 p_appl_hire_evnt_ind IN NUMBER,
755 p_appl_hire_nevnt_ind IN NUMBER,
756 p_hire_evnt_ind IN NUMBER,
757 p_hire_nevnt_ind IN NUMBER,
758 p_pow1_end_evnt_ind IN NUMBER,
759 p_pow1_end_nevnt_ind IN NUMBER,
760 p_perf_rtng_evnt_ind IN NUMBER,
761 p_perf_rtng_nevnt_ind IN NUMBER,
762 p_emp_sprtn_evnt_ind IN NUMBER,
763 p_emp_sprtn_nevnt_ind IN NUMBER,
764 p_hire_org_chng_ind IN NUMBER,
765 p_hire_job_chng_ind IN NUMBER,
766 p_hire_pos_chng_ind IN NUMBER,
767 p_hire_grd_chng_ind IN NUMBER,
768 p_hire_loc_chng_ind IN NUMBER,
769 p_current_record_ind IN NUMBER,
770 p_current_stage_strt_ind IN NUMBER,
771 p_gen_record_ind IN NUMBER,
772 p_appl_strt_to_asmt_strt_days IN NUMBER,
773 p_appl_strt_to_asmt_end_days IN NUMBER,
774 p_appl_strt_to_offr_extd_days IN NUMBER,
775 p_appl_strt_to_offr_rjct_days IN NUMBER,
776 p_appl_strt_to_offr_acpt_days IN NUMBER,
777 p_appl_strt_to_hire_days IN NUMBER,
778 p_appl_strt_to_term_days IN NUMBER,
779 p_init_appl_stg_ind IN NUMBER,
780 p_asmt_stg_ind IN NUMBER,
781 p_offr_extd_stg_ind IN NUMBER,
782 p_strt_pndg_stg_ind IN NUMBER,
783 p_hire_stg_ind IN NUMBER,
784 p_appl_strt_date IN DATE,
785 p_asmt_strt_date IN DATE,
786 p_asmt_end_date IN DATE,
787 p_offr_extd_date IN DATE,
788 p_offr_rjct_date IN DATE,
789 p_offr_acpt_date IN DATE,
790 p_appl_term_date IN DATE,
791 p_hire_date IN DATE,
792 p_emp_sprtn_date IN DATE,
793 p_event_date IN DATE,
794 p_stage_start_date IN DATE,
795 p_adt_assignment_id IN NUMBER,
796 p_adt_asg_effctv_start_date IN DATE,
797 p_adt_asg_effctv_end_date IN DATE,
798 p_adt_application_id IN NUMBER) IS
799
800 BEGIN
801
802 g_no_rows := g_no_rows + 1;
803 g_time_day_evt_fk(g_no_rows) := p_time_day_evt_fk;
804 g_time_day_evt_end_fk(g_no_rows) := p_time_day_evt_end_fk;
805 g_time_day_stg_evt_eff_end_fk(g_no_rows) := p_time_day_stg_evt_eff_end_fk;
806 g_person_cand_fk(g_no_rows) := p_person_cand_fk;
807 g_person_mngr_fk(g_no_rows) := p_person_mngr_fk;
808 g_person_rcrt_fk(g_no_rows) := p_person_rcrt_fk;
809 g_person_rmgr_fk(g_no_rows) := p_person_rmgr_fk;
810 g_person_auth_fk(g_no_rows) := p_person_auth_fk;
811 g_person_refr_fk(g_no_rows) := p_person_refr_fk;
812 g_person_rsed_fk(g_no_rows) := p_person_rsed_fk;
813 g_person_mrgd_fk(g_no_rows) := p_person_mrgd_fk;
814 g_org_organztn_fk(g_no_rows) := p_org_organztn_fk;
815 g_org_organztn_mrgd_fk(g_no_rows) := p_org_organztn_mrgd_fk;
816 g_org_organztn_recr_fk(g_no_rows) := p_org_organztn_recr_fk;
817 g_geo_location_fk(g_no_rows) := p_geo_location_fk;
818 g_job_job_fk(g_no_rows) := p_job_job_fk;
819 g_grd_grade_fk(g_no_rows) := p_grd_grade_fk;
820 g_pos_position_fk(g_no_rows) := p_pos_position_fk;
821 g_prfm_perfband_fk(g_no_rows) := p_prfm_perfband_fk;
822 g_rvac_vacncy_fk(g_no_rows) := p_rvac_vacncy_fk;
823 g_ract_recactvy_fk(g_no_rows) := p_ract_recactvy_fk;
824 g_rev_recevent_fk(g_no_rows) := p_rev_recevent_fk;
825 g_rern_recevtrn_fk(g_no_rows) := p_rern_recevtrn_fk;
826 g_tarn_trmaplrn_fk(g_no_rows) := p_tarn_trmaplrn_fk;
827 g_headcount(g_no_rows) := p_headcount;
828 g_fte(g_no_rows) := p_fte;
829 g_nrmlsd_perf_rtng(g_no_rows) := p_nrmlsd_perf_rtng;
830 g_event_seq(g_no_rows) := p_event_seq;
831 g_appl_ind(g_no_rows) := p_appl_ind;
832 g_appl_new_ind(g_no_rows) := p_appl_new_ind;
833 g_appl_emp_ind(g_no_rows) := p_appl_emp_ind;
834 g_appl_cwk_ind(g_no_rows) := p_appl_cwk_ind;
835 g_appl_strt_evnt_ind(g_no_rows) := p_appl_strt_evnt_ind;
836 g_appl_strt_nevnt_ind(g_no_rows) := p_appl_strt_nevnt_ind;
837 g_asmt_strt_evnt_ind(g_no_rows) := p_asmt_strt_evnt_ind;
838 g_asmt_strt_nevnt_ind(g_no_rows) := p_asmt_strt_nevnt_ind;
839 g_asmt_end_evnt_ind(g_no_rows) := p_asmt_end_evnt_ind;
840 g_asmt_end_nevnt_ind(g_no_rows) := p_asmt_end_nevnt_ind;
841 g_offr_extd_evnt_ind(g_no_rows) := p_offr_extd_evnt_ind;
842 g_offr_extd_nevnt_ind(g_no_rows) := p_offr_extd_nevnt_ind;
843 g_offr_rjct_evnt_ind(g_no_rows) := p_offr_rjct_evnt_ind;
844 g_offr_rjct_nevnt_ind(g_no_rows) := p_offr_rjct_nevnt_ind;
845 g_offr_acpt_evnt_ind(g_no_rows) := p_offr_acpt_evnt_ind;
846 g_offr_acpt_nevnt_ind(g_no_rows) := p_offr_acpt_nevnt_ind;
847 g_appl_term_evnt_ind(g_no_rows) := p_appl_term_evnt_ind;
848 g_appl_term_nevnt_ind(g_no_rows) := p_appl_term_nevnt_ind;
849 g_appl_term_vol_evnt_ind(g_no_rows) := p_appl_term_vol_evnt_ind;
850 g_appl_term_vol_nevnt_ind(g_no_rows) := p_appl_term_vol_nevnt_ind;
851 g_appl_term_invol_evnt_ind(g_no_rows) := p_appl_term_invol_evnt_ind;
852 g_appl_term_invol_nevnt_ind(g_no_rows) := p_appl_term_invol_nevnt_ind;
853 g_appl_hire_evnt_ind(g_no_rows) := p_appl_hire_evnt_ind;
854 g_appl_hire_nevnt_ind(g_no_rows) := p_appl_hire_nevnt_ind;
855 g_hire_evnt_ind(g_no_rows) := p_hire_evnt_ind;
856 g_hire_nevnt_ind(g_no_rows) := p_hire_nevnt_ind;
857 g_pow1_end_evnt_ind(g_no_rows) := p_pow1_end_evnt_ind;
858 g_pow1_end_nevnt_ind(g_no_rows) := p_pow1_end_nevnt_ind;
859 g_perf_rtng_evnt_ind(g_no_rows) := p_perf_rtng_evnt_ind;
860 g_perf_rtng_nevnt_ind(g_no_rows) := p_perf_rtng_nevnt_ind;
861 g_emp_sprtn_evnt_ind(g_no_rows) := p_emp_sprtn_evnt_ind;
862 g_emp_sprtn_nevnt_ind(g_no_rows) := p_emp_sprtn_nevnt_ind;
863 g_hire_org_chng_ind(g_no_rows) := p_hire_org_chng_ind;
864 g_hire_job_chng_ind(g_no_rows) := p_hire_job_chng_ind;
865 g_hire_pos_chng_ind(g_no_rows) := p_hire_pos_chng_ind;
866 g_hire_grd_chng_ind(g_no_rows) := p_hire_grd_chng_ind;
867 g_hire_loc_chng_ind(g_no_rows) := p_hire_loc_chng_ind;
868 g_current_record_ind(g_no_rows) := p_current_record_ind;
869 g_current_stage_strt_ind(g_no_rows) := p_current_stage_strt_ind;
870 g_gen_record_ind(g_no_rows) := p_gen_record_ind;
871 g_appl_strt_to_asmt_strt_days(g_no_rows) := p_appl_strt_to_asmt_strt_days;
872 g_appl_strt_to_asmt_end_days(g_no_rows) := p_appl_strt_to_asmt_end_days;
873 g_appl_strt_to_offr_extd_days(g_no_rows) := p_appl_strt_to_offr_extd_days;
874 g_appl_strt_to_offr_rjct_days(g_no_rows) := p_appl_strt_to_offr_rjct_days;
875 g_appl_strt_to_offr_acpt_days(g_no_rows) := p_appl_strt_to_offr_acpt_days;
876 g_appl_strt_to_hire_days(g_no_rows) := p_appl_strt_to_hire_days;
877 g_appl_strt_to_term_days(g_no_rows) := p_appl_strt_to_term_days;
878 g_init_appl_stg_ind(g_no_rows) := p_init_appl_stg_ind;
879 g_asmt_stg_ind(g_no_rows) := p_asmt_stg_ind;
880 g_offr_extd_stg_ind(g_no_rows) := p_offr_extd_stg_ind;
881 g_strt_pndg_stg_ind(g_no_rows) := p_strt_pndg_stg_ind;
882 g_hire_stg_ind(g_no_rows) := p_hire_stg_ind;
883 g_appl_strt_date(g_no_rows) := p_appl_strt_date;
884 g_asmt_strt_date(g_no_rows) := p_asmt_strt_date;
885 g_asmt_end_date(g_no_rows) := p_asmt_end_date;
886 g_offr_extd_date(g_no_rows) := p_offr_extd_date;
887 g_offr_rjct_date(g_no_rows) := p_offr_rjct_date;
888 g_offr_acpt_date(g_no_rows) := p_offr_acpt_date;
889 g_appl_term_date(g_no_rows) := p_appl_term_date;
890 g_hire_date(g_no_rows) := p_hire_date;
891 g_emp_sprtn_date(g_no_rows) := p_emp_sprtn_date;
892 g_event_date(g_no_rows) := p_event_date;
893 g_stage_start_date(g_no_rows) := p_stage_start_date;
894 g_adt_assignment_id(g_no_rows) := p_adt_assignment_id;
895 g_adt_asg_effctv_start_date(g_no_rows) := p_adt_asg_effctv_start_date;
896 g_adt_asg_effctv_end_date(g_no_rows) := p_adt_asg_effctv_end_date;
897 g_adt_application_id(g_no_rows) := p_adt_application_id;
898
899 END insert_row;
900
901
902 -- ----------------------------------------------------------------------------
903 -- Returns date tracked end date for event row
904 -- ----------------------------------------------------------------------------
905 FUNCTION get_end_date(p_dt_idx_tab IN g_number_tab_type,
906 p_info_rec IN g_info_rec_type,
907 p_master_idx IN NUMBER,
908 p_event_idx IN NUMBER)
909 RETURN DATE IS
910
911 l_dt_end_idx NUMBER;
912 l_end_date DATE;
913
914 BEGIN
915
916 -- Set end date if event is flagged as a date-track event
917 IF p_dt_idx_tab.EXISTS(p_master_idx) THEN
918
919 IF (p_dt_idx_tab(p_master_idx) = p_event_idx) THEN
920
921 l_dt_end_idx := p_dt_idx_tab.NEXT(p_master_idx);
922
923 -- If no further date tracked records then use end of time
924 IF l_dt_end_idx IS NULL THEN
925 l_end_date := g_end_of_time;
926 ELSE
927 l_end_date := p_info_rec.idx_strt_date + l_dt_end_idx - 1;
928 END IF;
929
930 END IF;
931
932 END IF;
933
934 RETURN l_end_date;
935
936 END get_end_date;
937
938
939 -- ----------------------------------------------------------------------------
940 -- Loops through data structures and inserts records into PL/SQL structure
941 -- ready for bulk insert
942 -- ----------------------------------------------------------------------------
943 PROCEDURE merge_and_insert_data
944 (p_master_tab IN g_master_tab_type,
945 p_apl_tab IN g_apl_tab_type,
946 p_dt_idx_tab IN g_number_tab_type,
947 p_info_rec IN g_info_rec_type,
948 p_ind_rec IN g_ind_rec_type) IS
949
950 l_master_idx NUMBER;
951 l_last_idx NUMBER;
952 l_event_idx NUMBER;
953 l_apl_idx NUMBER;
954 l_ind_rec g_ind_rec_type;
955 l_gen_record_ind NUMBER;
956 l_end_date DATE;
957 l_master_tab g_master_tab_type;
958 l_current_stage_strt_ind PLS_INTEGER;
959 l_current_stage_strt_seq PLS_INTEGER;
960
961 BEGIN
962
963 -- Copy the master table
964 l_master_tab := p_master_tab;
965
966 -- Set master index to first record
967 -- Set current indicator for last record
968 BEGIN
969
970 -- Set indexes
971 l_master_idx := p_master_tab.FIRST;
972 l_last_idx := p_master_tab.LAST;
973 l_event_idx := p_master_tab(l_last_idx).LAST;
974
975 -- Set current indicator
976 l_master_tab(l_last_idx)(l_event_idx).event_ind.current_record_ind := 1;
977
978 -- Trap any exception that occurs if the master table is empty
979 EXCEPTION WHEN OTHERS THEN
980 null;
981 END;
982
983 -- Loop until all records have been processed
984 WHILE l_master_idx IS NOT NULL LOOP
985
986 -- Loop through events occurring on the same day
987 FOR l_event_idx IN 1..l_master_tab(l_master_idx).LAST LOOP
988
989 -- Extract information from master table
990 l_apl_idx := l_master_tab(l_master_idx)(l_event_idx).apl_idx;
991 l_ind_rec := l_master_tab(l_master_idx)(l_event_idx).event_ind;
992 IF l_master_tab(l_master_idx)(l_event_idx).cnstrct_evt = 'Y' THEN
993 l_gen_record_ind := 1;
994 ELSE
995 l_gen_record_ind := 0;
996 END IF;
997
998 -- Determine current_stage_start
999 IF l_master_tab(l_master_idx)(l_event_idx).stage_code = p_info_rec.latest_stage AND
1000 l_current_stage_strt_seq IS NULL THEN
1001 l_current_stage_strt_seq := l_master_tab(l_master_idx)(l_event_idx).event_seq;
1002 l_current_stage_strt_ind := 1;
1003 ELSE
1004 l_current_stage_strt_ind := 0;
1005 END IF;
1006
1007 -- Set date-track end date
1008 l_end_date := get_end_date
1009 (p_dt_idx_tab => p_dt_idx_tab,
1010 p_info_rec => p_info_rec,
1011 p_master_idx => l_master_idx,
1012 p_event_idx => l_event_idx);
1013
1014 -- Insert record for each event
1015 insert_row
1016 (p_time_day_evt_fk => p_apl_tab(l_apl_idx).time_day_evt_fk
1017 ,p_time_day_evt_end_fk => p_apl_tab(l_apl_idx).time_day_evt_end_fk
1018 ,p_time_day_stg_evt_eff_end_fk => l_end_date
1019 ,p_person_cand_fk => p_apl_tab(l_apl_idx).person_cand_fk
1020 ,p_person_mngr_fk => p_apl_tab(l_apl_idx).person_mngr_fk
1021 ,p_person_rcrt_fk => p_apl_tab(l_apl_idx).person_rcrt_fk
1022 ,p_person_rmgr_fk => p_apl_tab(l_apl_idx).person_rmgr_fk
1023 ,p_person_auth_fk => p_apl_tab(l_apl_idx).person_auth_fk
1024 ,p_person_refr_fk => p_apl_tab(l_apl_idx).person_refr_fk
1025 ,p_person_rsed_fk => p_apl_tab(l_apl_idx).person_rsed_fk
1026 ,p_person_mrgd_fk => p_apl_tab(l_apl_idx).person_mrgd_fk
1027 ,p_org_organztn_fk => p_apl_tab(l_apl_idx).org_organztn_fk
1028 ,p_org_organztn_mrgd_fk => p_apl_tab(l_apl_idx).org_organztn_mrgd_fk
1029 ,p_org_organztn_recr_fk => p_apl_tab(l_apl_idx).org_organztn_recr_fk
1030 ,p_geo_location_fk => p_apl_tab(l_apl_idx).geo_location_fk
1031 ,p_job_job_fk => p_apl_tab(l_apl_idx).job_job_fk
1032 ,p_grd_grade_fk => p_apl_tab(l_apl_idx).grd_grade_fk
1033 ,p_pos_position_fk => p_apl_tab(l_apl_idx).pos_position_fk
1034 ,p_prfm_perfband_fk => p_info_rec.perf_band
1035 ,p_rvac_vacncy_fk => p_apl_tab(l_apl_idx).rvac_vacncy_fk
1036 ,p_ract_recactvy_fk => p_apl_tab(l_apl_idx).ract_recactvy_fk
1037 ,p_rev_recevent_fk => l_master_tab(l_master_idx)(l_event_idx).event_code
1038 ,p_rern_recevtrn_fk => p_apl_tab(l_apl_idx).rern_recevtrn_fk
1039 ,p_tarn_trmaplrn_fk => p_apl_tab(l_apl_idx).tarn_trmaplrn_fk
1040 ,p_headcount => p_apl_tab(l_apl_idx).headcount
1041 ,p_fte => p_apl_tab(l_apl_idx).fte
1042 ,p_nrmlsd_perf_rtng => p_info_rec.perf_norm_rtng
1043 ,p_event_seq => l_master_tab(l_master_idx)(l_event_idx).event_seq
1044 ,p_appl_ind => l_ind_rec.appl_ind
1045 ,p_appl_new_ind => l_ind_rec.appl_new_ind
1046 ,p_appl_emp_ind => l_ind_rec.appl_emp_ind
1047 ,p_appl_cwk_ind => l_ind_rec.appl_cwk_ind
1048 ,p_appl_strt_evnt_ind => l_ind_rec.appl_strt_evnt_ind
1049 ,p_appl_strt_nevnt_ind => p_ind_rec.appl_strt_nevnt_ind
1050 ,p_asmt_strt_evnt_ind => l_ind_rec.asmt_strt_evnt_ind
1051 ,p_asmt_strt_nevnt_ind => p_ind_rec.asmt_strt_nevnt_ind
1052 ,p_asmt_end_evnt_ind => l_ind_rec.asmt_end_evnt_ind
1053 ,p_asmt_end_nevnt_ind => p_ind_rec.asmt_end_nevnt_ind
1054 ,p_offr_extd_evnt_ind => l_ind_rec.offr_extd_evnt_ind
1055 ,p_offr_extd_nevnt_ind => p_ind_rec.offr_extd_nevnt_ind
1056 ,p_offr_rjct_evnt_ind => l_ind_rec.offr_rjct_evnt_ind
1057 ,p_offr_rjct_nevnt_ind => p_ind_rec.offr_rjct_nevnt_ind
1058 ,p_offr_acpt_evnt_ind => l_ind_rec.offr_acpt_evnt_ind
1059 ,p_offr_acpt_nevnt_ind => p_ind_rec.offr_acpt_nevnt_ind
1060 ,p_appl_term_evnt_ind => l_ind_rec.appl_term_evnt_ind
1061 ,p_appl_term_nevnt_ind => p_ind_rec.appl_term_nevnt_ind
1062 ,p_appl_term_vol_evnt_ind => l_ind_rec.appl_term_vol_evnt_ind
1063 ,p_appl_term_vol_nevnt_ind => p_ind_rec.appl_term_vol_nevnt_ind
1064 ,p_appl_term_invol_evnt_ind => l_ind_rec.appl_term_invol_evnt_ind
1065 ,p_appl_term_invol_nevnt_ind => p_ind_rec.appl_term_invol_nevnt_ind
1066 ,p_appl_hire_evnt_ind => l_ind_rec.appl_hire_evnt_ind
1067 ,p_appl_hire_nevnt_ind => p_ind_rec.appl_hire_nevnt_ind
1068 ,p_hire_evnt_ind => l_ind_rec.hire_evnt_ind
1069 ,p_hire_nevnt_ind => p_ind_rec.hire_nevnt_ind
1070 ,p_pow1_end_evnt_ind => l_ind_rec.pow1_end_evnt_ind
1071 ,p_pow1_end_nevnt_ind => p_ind_rec.pow1_end_nevnt_ind
1072 ,p_perf_rtng_evnt_ind => l_ind_rec.perf_rtng_evnt_ind
1073 ,p_perf_rtng_nevnt_ind => p_ind_rec.perf_rtng_nevnt_ind
1074 ,p_emp_sprtn_evnt_ind => l_ind_rec.emp_sprtn_evnt_ind
1075 ,p_emp_sprtn_nevnt_ind => p_ind_rec.emp_sprtn_nevnt_ind
1076 ,p_hire_org_chng_ind => l_ind_rec.hire_org_chng_ind
1077 ,p_hire_job_chng_ind => l_ind_rec.hire_job_chng_ind
1078 ,p_hire_pos_chng_ind => l_ind_rec.hire_pos_chng_ind
1079 ,p_hire_grd_chng_ind => l_ind_rec.hire_grd_chng_ind
1080 ,p_hire_loc_chng_ind => l_ind_rec.hire_loc_chng_ind
1081 ,p_current_record_ind => l_ind_rec.current_record_ind
1082 ,p_current_stage_strt_ind => l_current_stage_strt_ind
1083 ,p_gen_record_ind => l_gen_record_ind
1084 ,p_appl_strt_to_asmt_strt_days => p_info_rec.asmt_strt_date - p_info_rec.appl_strt_date
1085 ,p_appl_strt_to_asmt_end_days => p_info_rec.asmt_end_date - p_info_rec.appl_strt_date
1086 ,p_appl_strt_to_offr_extd_days => p_info_rec.offr_extd_date - p_info_rec.appl_strt_date
1087 ,p_appl_strt_to_offr_rjct_days => p_info_rec.offr_rjct_date - p_info_rec.appl_strt_date
1088 ,p_appl_strt_to_offr_acpt_days => p_info_rec.offr_acpt_date - p_info_rec.appl_strt_date
1089 ,p_appl_strt_to_hire_days => p_info_rec.hire_date - p_info_rec.appl_strt_date
1090 ,p_appl_strt_to_term_days => p_info_rec.appl_term_date - p_info_rec.appl_strt_date
1091 ,p_init_appl_stg_ind => l_ind_rec.init_appl_stg_ind
1092 ,p_asmt_stg_ind => l_ind_rec.asmt_stg_ind
1093 ,p_offr_extd_stg_ind => l_ind_rec.offr_extd_stg_ind
1094 ,p_strt_pndg_stg_ind => l_ind_rec.strt_pndg_stg_ind
1095 ,p_hire_stg_ind => l_ind_rec.hire_stg_ind
1096 ,p_appl_strt_date => p_info_rec.appl_strt_date
1097 ,p_asmt_strt_date => p_info_rec.asmt_strt_date
1098 ,p_asmt_end_date => p_info_rec.asmt_end_date
1099 ,p_offr_extd_date => p_info_rec.offr_extd_date
1100 ,p_offr_rjct_date => p_info_rec.offr_rjct_date
1101 ,p_offr_acpt_date => p_info_rec.offr_acpt_date
1102 ,p_appl_term_date => p_info_rec.appl_term_date
1103 ,p_hire_date => p_info_rec.hire_date
1104 ,p_emp_sprtn_date => to_date(null)
1105 ,p_event_date => p_apl_tab(l_apl_idx).event_date
1106 ,p_stage_start_date => p_apl_tab(l_apl_idx).stage_start_date
1107 ,p_adt_assignment_id => p_info_rec.asg_id
1108 ,p_adt_asg_effctv_start_date => p_apl_tab(l_apl_idx).time_day_evt_fk
1109 ,p_adt_asg_effctv_end_date => p_apl_tab(l_apl_idx).time_day_evt_end_fk
1110 ,p_adt_application_id => p_apl_tab(l_apl_idx).adt_application_id);
1111
1112 END LOOP;
1113
1114 -- Increment master index
1115 l_master_idx := l_master_tab.NEXT(l_master_idx);
1116
1117 END LOOP;
1118
1119 END merge_and_insert_data;
1120
1121
1122 -- ----------------------------------------------------------------------------
1123 -- Determines event for a particular assignment status and event history
1124 -- ----------------------------------------------------------------------------
1125 FUNCTION get_event_code(p_apl_tab IN g_apl_tab_type,
1126 p_apl_idx IN NUMBER)
1127 RETURN VARCHAR2 IS
1128
1129 l_status_change BOOLEAN;
1130 l_event_code VARCHAR2(30);
1131
1132 BEGIN
1133
1134 -- Detect whether event is a status change
1135 IF p_apl_idx = 1 THEN
1136 l_status_change := TRUE;
1137 ELSE
1138 IF (p_apl_tab(p_apl_idx).assignment_status_type_id <>
1139 p_apl_tab(p_apl_idx - 1).assignment_status_type_id) THEN
1140 l_status_change := TRUE;
1141 ELSE
1142 l_status_change := FALSE;
1143 END IF;
1144 END IF;
1145
1146 -- If event is a status change, determine the event code
1147 IF l_status_change THEN
1148 l_event_code := hri_bpl_rec_pipln.get_event_code
1149 (p_system_status => p_apl_tab(p_apl_idx).per_system_status,
1150 p_status_id => p_apl_tab(p_apl_idx).assignment_status_type_id,
1151 p_user_status => p_apl_tab(p_apl_idx).user_status);
1152 -- Otherwise classify the event as a non-pipeline event
1153 ELSE
1154 l_event_code := 'NA_EDW';
1155 END IF;
1156
1157 -- Check if event has already occurred - if so then
1158 -- subsequent identical events are not classified
1159 IF g_event_cache.EXISTS(l_event_code) THEN
1160 l_event_code := 'NA_EDW';
1161
1162 -- Otherwise note the first occurrence of the event
1163 ELSE
1164 g_event_cache(l_event_code) := 'Y';
1165 END IF;
1166
1167 RETURN l_event_code;
1168
1169 END get_event_code;
1170
1171
1172 -- ----------------------------------------------------------------------------
1173 -- Interprets a given applicant assignment event within the framework of
1174 -- recruitment pipeline stages and events
1175 -- ----------------------------------------------------------------------------
1176 PROCEDURE interpret_appl_event(p_master_tab IN OUT NOCOPY g_master_tab_type,
1177 p_apl_tab IN g_apl_tab_type,
1178 p_apl_idx IN NUMBER,
1179 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1180 p_info_rec IN OUT NOCOPY g_info_rec_type,
1181 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1182
1183 l_stage_code VARCHAR2(30);
1184 l_event_code VARCHAR2(30);
1185 l_master_idx PLS_INTEGER;
1186 l_event_idx PLS_INTEGER;
1187 l_ind_rec g_ind_rec_type;
1188 l_con_rec g_ind_rec_type;
1189
1190 l_construct_appl_strt_stg BOOLEAN;
1191 l_construct_asmt_strt_stg BOOLEAN;
1192 l_construct_offr_extd_stg BOOLEAN;
1193
1194 BEGIN
1195
1196 -- dbg('Interpreting event on: ' || to_char(p_apl_tab(p_apl_idx).time_day_evt_fk));
1197
1198 -- Copy indicator record
1199 l_ind_rec := p_ind_rec;
1200
1201 -- Set master table index
1202 l_master_idx := p_apl_tab(p_apl_idx).time_day_evt_fk - p_info_rec.idx_strt_date;
1203
1204 -- Set event index to the last index on the current day
1205 l_event_idx := get_event_idx
1206 (p_master_tab => p_master_tab,
1207 p_master_idx => l_master_idx);
1208
1209 -- Get Stage Code
1210 l_stage_code := hri_bpl_rec_pipln.get_stage_code
1211 (p_system_status => p_apl_tab(p_apl_idx).per_system_status,
1212 p_status_id => p_apl_tab(p_apl_idx).assignment_status_type_id,
1213 p_user_status => p_apl_tab(p_apl_idx).user_status);
1214
1215 -- Get Event Code
1216 l_event_code := get_event_code
1217 (p_apl_tab => p_apl_tab,
1218 p_apl_idx => p_apl_idx);
1219
1220 -- dbg('Event: ' || l_event_code || ' Stage: ' || l_stage_code);
1221
1222 -- -----------------------------------------------------
1223 -- STAGES - Identify events signifying new stage reached
1224 -- -----------------------------------------------------
1225
1226 -- Application Start
1227 IF (l_stage_code = 'INIT_APPL_STG' AND
1228 p_info_rec.appl_strt_date IS NULL) THEN
1229
1230 p_info_rec.appl_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1231 p_info_rec.latest_stage := 'INIT_APPL_STG';
1232 p_info_rec.curr_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1233 l_ind_rec.appl_strt_evnt_ind := 1;
1234 p_ind_rec.appl_strt_nevnt_ind := 1;
1235 l_ind_rec.init_appl_stg_ind := 1;
1236 p_ind_rec.init_appl_stg_ind := 1;
1237
1238 -- Assessment Start
1239 ELSIF (l_stage_code = 'ASMT_STG' AND
1240 p_info_rec.asmt_strt_date IS NULL) THEN
1241
1242 -- Construct Application Start Stage if it was skipped
1243 IF (p_info_rec.appl_strt_date IS NULL) THEN
1244 l_construct_appl_strt_stg := TRUE;
1245 END IF;
1246
1247 -- Construct Assessment Start Stage if it is not directly mapped
1248 IF (l_event_code <> 'ASMT_STRT') THEN
1249 l_construct_asmt_strt_stg := TRUE;
1250 ELSE
1251 l_ind_rec.asmt_strt_evnt_ind := 1;
1252 p_ind_rec.asmt_strt_nevnt_ind := 1;
1253 END IF;
1254
1255 p_info_rec.asmt_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1256 p_info_rec.latest_stage := 'ASMT_STG';
1257 p_info_rec.curr_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1258 l_ind_rec.init_appl_stg_ind := 0;
1259 p_ind_rec.init_appl_stg_ind := 0;
1260 l_ind_rec.asmt_stg_ind := 1;
1261 p_ind_rec.asmt_stg_ind := 1;
1262
1263 -- Offer
1264 ELSIF (l_stage_code = 'OFFR_EXTD_STG' AND
1265 p_info_rec.offr_extd_date IS NULL) THEN
1266
1267 -- Construct Application Start Stage if it was skipped
1268 IF (p_info_rec.appl_strt_date IS NULL) THEN
1269 l_construct_appl_strt_stg := TRUE;
1270 END IF;
1271
1272 -- Construct Assessment Start Stage if it was skipped
1273 IF (p_info_rec.asmt_strt_date IS NULL) THEN
1274 l_construct_asmt_strt_stg := TRUE;
1275 END IF;
1276
1277 -- Flag Assessment End
1278 IF (p_info_rec.asmt_end_date IS NULL) THEN
1279 p_info_rec.asmt_end_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1280 l_ind_rec.asmt_end_evnt_ind := 1;
1281 p_ind_rec.asmt_end_nevnt_ind := 1;
1282 END IF;
1283
1284 p_info_rec.offr_extd_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1285 p_info_rec.latest_stage := 'OFFR_EXTD_STG';
1286 p_info_rec.curr_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1287 l_ind_rec.offr_extd_evnt_ind := 1;
1288 p_ind_rec.offr_extd_nevnt_ind := 1;
1289 l_ind_rec.init_appl_stg_ind := 0;
1290 p_ind_rec.init_appl_stg_ind := 0;
1291 l_ind_rec.asmt_stg_ind := 0;
1292 p_ind_rec.asmt_stg_ind := 0;
1293 l_ind_rec.offr_extd_stg_ind := 1;
1294 p_ind_rec.offr_extd_stg_ind := 1;
1295
1296 -- Offer Accepted
1297 ELSIF (l_stage_code = 'STRT_PNDG_STG' AND
1298 p_info_rec.offr_acpt_date IS NULL) THEN
1299
1300 -- Construct Application Start Stage if it was skipped
1301 IF (p_info_rec.appl_strt_date IS NULL) THEN
1302 l_construct_appl_strt_stg := TRUE;
1303 END IF;
1304
1305 -- Construct Assessment Start Stage if it was skipped
1306 IF (p_info_rec.asmt_strt_date IS NULL) THEN
1307 l_construct_asmt_strt_stg := TRUE;
1308 END IF;
1309
1310 -- Construct Offer Stage if it was skipped
1311 IF (p_info_rec.offr_extd_date IS NULL) THEN
1312 l_construct_offr_extd_stg := TRUE;
1313 END IF;
1314
1315 -- Flag Assessment End
1316 IF (p_info_rec.asmt_end_date IS NULL) THEN
1317 p_info_rec.asmt_end_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1318 l_ind_rec.asmt_end_evnt_ind := 1;
1319 p_ind_rec.asmt_end_nevnt_ind := 1;
1320 END IF;
1321
1322 p_info_rec.offr_acpt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1323 p_info_rec.latest_stage := 'STRT_PNDG_STG';
1324 p_info_rec.curr_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1325 l_ind_rec.offr_acpt_evnt_ind := 1;
1326 p_ind_rec.offr_acpt_nevnt_ind := 1;
1327 l_ind_rec.init_appl_stg_ind := 0;
1328 p_ind_rec.init_appl_stg_ind := 0;
1329 l_ind_rec.asmt_stg_ind := 0;
1330 p_ind_rec.asmt_stg_ind := 0;
1331 l_ind_rec.offr_extd_stg_ind := 0;
1332 p_ind_rec.offr_extd_stg_ind := 0;
1333 l_ind_rec.strt_pndg_stg_ind := 1;
1334 p_ind_rec.strt_pndg_stg_ind := 1;
1335
1336 -- Termination
1337 ELSIF (l_stage_code = 'APPL_TERM_STG') THEN
1338
1339 p_info_rec.appl_term_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1340 p_info_rec.latest_stage := 'APPL_TERM_STG';
1341 p_info_rec.curr_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1342 l_ind_rec.appl_term_evnt_ind := 1;
1343 p_ind_rec.appl_term_nevnt_ind := 1;
1344 l_ind_rec.init_appl_stg_ind := 0;
1345 p_ind_rec.init_appl_stg_ind := 0;
1346 l_ind_rec.asmt_stg_ind := 0;
1347 p_ind_rec.asmt_stg_ind := 0;
1348 l_ind_rec.offr_extd_stg_ind := 0;
1349 p_ind_rec.offr_extd_stg_ind := 0;
1350 l_ind_rec.strt_pndg_stg_ind := 0;
1351 p_ind_rec.strt_pndg_stg_ind := 0;
1352
1353 END IF;
1354
1355
1356 -- ------------------------------------------------------------
1357 -- CONSTRUCTED STAGE EVENTS - Fill in events for skipped stages
1358 -- ------------------------------------------------------------
1359
1360 -- Construct application start stage
1361 IF l_construct_appl_strt_stg THEN
1362 l_con_rec := p_ind_rec;
1363 l_con_rec.init_appl_stg_ind := 1;
1364 l_con_rec.asmt_stg_ind := 0;
1365 l_con_rec.offr_extd_stg_ind := 0;
1366 l_con_rec.strt_pndg_stg_ind := 0;
1367 l_con_rec.appl_strt_evnt_ind := 1;
1368 p_ind_rec.appl_strt_nevnt_ind := 1;
1369 l_event_idx := l_event_idx + 1;
1370 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1371 p_info_rec.appl_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1372 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_apl_idx;
1373 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1374 p_master_tab(l_master_idx)(l_event_idx).event_code := 'APPL_STRT';
1375 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'INIT_APPL_STG';
1376 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_con_rec;
1377 p_master_tab(l_master_idx)(l_event_idx).cnstrct_evt := 'Y';
1378 p_dt_idx_tab(l_master_idx) := l_event_idx;
1379 END IF;
1380
1381 -- Construct assessment start stage
1382 IF l_construct_asmt_strt_stg THEN
1383 l_con_rec := p_ind_rec;
1384 l_con_rec.init_appl_stg_ind := 0;
1385 l_con_rec.asmt_stg_ind := 1;
1386 l_con_rec.offr_extd_stg_ind := 0;
1387 l_con_rec.strt_pndg_stg_ind := 0;
1388 l_con_rec.asmt_strt_evnt_ind := 1;
1389 p_ind_rec.asmt_strt_nevnt_ind := 1;
1390 l_event_idx := l_event_idx + 1;
1391 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1392 p_info_rec.asmt_strt_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1393 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_apl_idx;
1394 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1395 p_master_tab(l_master_idx)(l_event_idx).event_code := 'ASMT_STRT';
1396 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'ASMT_STG';
1397 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_con_rec;
1398 p_master_tab(l_master_idx)(l_event_idx).cnstrct_evt := 'Y';
1399 p_dt_idx_tab(l_master_idx) := l_event_idx;
1400 END IF;
1401
1402 -- Construct offer extended stage
1403 IF l_construct_offr_extd_stg THEN
1404 l_con_rec := p_ind_rec;
1405 l_con_rec.init_appl_stg_ind := 0;
1406 l_con_rec.asmt_stg_ind := 0;
1407 l_con_rec.offr_extd_stg_ind := 1;
1408 l_con_rec.strt_pndg_stg_ind := 0;
1409 l_con_rec.offr_extd_evnt_ind := 1;
1410 p_ind_rec.offr_extd_nevnt_ind := 1;
1411 l_event_idx := l_event_idx + 1;
1412 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1413 p_info_rec.offr_extd_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1414 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_apl_idx;
1415 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1416 p_master_tab(l_master_idx)(l_event_idx).event_code := 'OFFR_EXTD';
1417 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'OFFR_EXTD_STG';
1418 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_con_rec;
1419 p_master_tab(l_master_idx)(l_event_idx).cnstrct_evt := 'Y';
1420 p_dt_idx_tab(l_master_idx) := l_event_idx;
1421 END IF;
1422
1423 -- -------------------------------------------------------
1424 -- STAGE EVENTS - Identify events occurring within a stage
1425 -- -------------------------------------------------------
1426
1427 -- Assessment Step 1
1428 IF (l_event_code = 'ASMT_INT1' AND
1429 p_info_rec.asmt_int1_date IS NULL) THEN
1430
1431 p_info_rec.asmt_int1_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1432
1433 -- Assessment Step 2
1434 ELSIF (l_event_code = 'ASMT_INT2' AND
1435 p_info_rec.asmt_int2_date IS NULL) THEN
1436
1437 p_info_rec.asmt_int2_date := p_apl_tab(p_apl_idx).time_day_evt_fk;
1438
1439 -- Other user defined
1440 ELSIF l_stage_code IS NULL THEN
1441
1442 -- Add code to handle user defined stages/events
1443 null;
1444
1445 END IF;
1446
1447 -- Update master record with event if it is not unassigned, skip or null
1448 IF l_event_code <> 'NA_EDW' AND
1449 l_event_code <> 'SKIP' THEN
1450
1451 l_event_idx := l_event_idx + 1;
1452 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1453 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_apl_idx;
1454 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1455 p_master_tab(l_master_idx)(l_event_idx).event_code := l_event_code;
1456 p_master_tab(l_master_idx)(l_event_idx).stage_code := l_stage_code;
1457 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1458
1459 -- Keep date-tracked status for pipeline events
1460 IF l_stage_code <> 'NON_PIPLN_STG' THEN
1461 p_dt_idx_tab(l_master_idx) := l_event_idx;
1462 END IF;
1463
1464 END IF;
1465
1466 END interpret_appl_event;
1467
1468
1469 -- ----------------------------------------------------------------------------
1470 -- Adds application fail event
1471 -- ----------------------------------------------------------------------------
1472 PROCEDURE add_appl_fail_event
1473 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1474 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1475 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1476 p_info_rec IN OUT NOCOPY g_info_rec_type,
1477 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1478
1479 l_stage_code VARCHAR2(30);
1480 l_event_code VARCHAR2(30);
1481 l_master_idx PLS_INTEGER;
1482 l_event_idx PLS_INTEGER;
1483 l_apl_idx PLS_INTEGER;
1484 l_ind_rec g_ind_rec_type;
1485 l_term_type VARCHAR2(30);
1486
1487 BEGIN
1488
1489 -- Copy indicator record
1490 l_ind_rec := p_ind_rec;
1491
1492 -- Set master table index
1493 l_master_idx := p_info_rec.appl_end_date - p_info_rec.idx_strt_date + 1;
1494
1495 -- Set event index to the last index on the current day
1496 l_event_idx := get_event_idx
1497 (p_master_tab => p_master_tab,
1498 p_master_idx => l_master_idx);
1499
1500 -- Add termination record to applicant cursor
1501 l_apl_idx := p_info_rec.last_apl_idx;
1502 p_apl_tab(l_apl_idx+1).time_day_evt_fk := p_apl_tab(l_apl_idx).time_day_evt_end_fk + 1;
1503 p_apl_tab(l_apl_idx+1).time_day_evt_end_fk := g_end_of_time;
1504 p_apl_tab(l_apl_idx+1).person_cand_fk := p_apl_tab(l_apl_idx).person_cand_fk;
1505 p_apl_tab(l_apl_idx+1).person_mngr_fk := p_apl_tab(l_apl_idx).person_mngr_fk;
1506 p_apl_tab(l_apl_idx+1).person_rcrt_fk := p_apl_tab(l_apl_idx).person_rcrt_fk;
1507 p_apl_tab(l_apl_idx+1).person_rmgr_fk := p_apl_tab(l_apl_idx).person_rmgr_fk;
1508 p_apl_tab(l_apl_idx+1).person_auth_fk := p_apl_tab(l_apl_idx).person_auth_fk;
1509 p_apl_tab(l_apl_idx+1).person_refr_fk := p_apl_tab(l_apl_idx).person_refr_fk;
1510 p_apl_tab(l_apl_idx+1).person_rsed_fk := p_apl_tab(l_apl_idx).person_rsed_fk;
1511 p_apl_tab(l_apl_idx+1).person_mrgd_fk := p_apl_tab(l_apl_idx).person_mrgd_fk;
1512 p_apl_tab(l_apl_idx+1).org_organztn_fk := p_apl_tab(l_apl_idx).org_organztn_fk;
1513 p_apl_tab(l_apl_idx+1).org_organztn_mrgd_fk := p_apl_tab(l_apl_idx).org_organztn_mrgd_fk;
1514 p_apl_tab(l_apl_idx+1).org_organztn_recr_fk := p_apl_tab(l_apl_idx).org_organztn_recr_fk;
1515 p_apl_tab(l_apl_idx+1).geo_location_fk := p_apl_tab(l_apl_idx).geo_location_fk;
1516 p_apl_tab(l_apl_idx+1).job_job_fk := p_apl_tab(l_apl_idx).job_job_fk;
1517 p_apl_tab(l_apl_idx+1).grd_grade_fk := p_apl_tab(l_apl_idx).grd_grade_fk;
1518 p_apl_tab(l_apl_idx+1).pos_position_fk := p_apl_tab(l_apl_idx).pos_position_fk;
1519 p_apl_tab(l_apl_idx+1).prfm_perfband_fk := p_apl_tab(l_apl_idx).prfm_perfband_fk;
1520 p_apl_tab(l_apl_idx+1).rvac_vacncy_fk := p_apl_tab(l_apl_idx).rvac_vacncy_fk;
1521 p_apl_tab(l_apl_idx+1).ract_recactvy_fk := p_apl_tab(l_apl_idx).ract_recactvy_fk;
1522 p_apl_tab(l_apl_idx+1).rern_recevtrn_fk := 'NA_EDW';
1523 p_apl_tab(l_apl_idx+1).tarn_trmaplrn_fk := p_apl_tab(l_apl_idx).tarn_trmaplrn_fk;
1524 p_apl_tab(l_apl_idx+1).adt_application_id := p_apl_tab(l_apl_idx).adt_application_id;
1525 p_apl_tab(l_apl_idx+1).adt_business_group_id := p_apl_tab(l_apl_idx).adt_business_group_id;
1526 p_apl_tab(l_apl_idx+1).event_date := p_apl_tab(l_apl_idx).time_day_evt_end_fk + 1;
1527 p_apl_tab(l_apl_idx+1).stage_start_date := p_apl_tab(l_apl_idx).time_day_evt_end_fk + 1;
1528 p_apl_tab(l_apl_idx+1).per_system_status := null;
1529 p_apl_tab(l_apl_idx+1).user_status := null;
1530 p_apl_tab(l_apl_idx+1).assignment_status_type_id := to_number(null);
1531 p_apl_tab(l_apl_idx+1).headcount := p_apl_tab(l_apl_idx).headcount;
1532 p_apl_tab(l_apl_idx+1).fte := p_apl_tab(l_apl_idx).fte;
1533
1534 -- Get Stage Code
1535 l_stage_code := 'APPL_TERM_STG';
1536
1537 -- Get Event Code
1538 IF p_info_rec.latest_stage = 'INIT_APPL_STG' THEN
1539 l_event_code := 'APPL_TERM_INIT';
1540 ELSIF p_info_rec.latest_stage = 'ASMT_STG' THEN
1541 l_event_code := 'APPL_TERM_ASMT';
1542 ELSIF p_info_rec.latest_stage = 'OFFR_EXTD_STG' THEN
1543 l_event_code := 'APPL_TERM_OFFR';
1544 p_info_rec.offr_rjct_date := p_info_rec.appl_end_date + 1;
1545 p_ind_rec.offr_rjct_nevnt_ind := 1;
1546 l_ind_rec.offr_rjct_evnt_ind := 1;
1547 ELSIF p_info_rec.latest_stage = 'STRT_PNDG_STG' THEN
1548 l_event_code := 'APPL_TERM_ACPT';
1549 ELSE
1550 l_event_code := 'APPL_TERM';
1551 END IF;
1552
1553 -- Get vol/invol status
1554 l_term_type := hri_bpl_rec_pipln.get_appl_term_type
1555 (p_appl_term_rsn => p_info_rec.appl_term_rsn);
1556
1557 -- Set Indicators/Info
1558 p_info_rec.latest_stage := l_stage_code;
1559 p_info_rec.appl_term_date := p_info_rec.appl_end_date + 1;
1560 l_ind_rec.appl_term_evnt_ind := 1;
1561 p_ind_rec.appl_term_nevnt_ind := 1;
1562 l_ind_rec.init_appl_stg_ind := 0;
1563 l_ind_rec.asmt_stg_ind := 0;
1564 l_ind_rec.offr_extd_stg_ind := 0;
1565 l_ind_rec.strt_pndg_stg_ind := 0;
1566 l_ind_rec.hire_stg_ind := 0;
1567
1568 -- Set vol/invol indicators
1569 IF l_term_type = 'V' THEN
1570 l_ind_rec.appl_term_vol_evnt_ind := 1;
1571 p_ind_rec.appl_term_vol_nevnt_ind := 1;
1572 ELSE
1573 l_ind_rec.appl_term_invol_evnt_ind := 1;
1574 p_ind_rec.appl_term_invol_nevnt_ind := 1;
1575 END IF;
1576
1577 -- Add Application Termination Event
1578 l_event_idx := l_event_idx + 1;
1579 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1580 p_master_tab(l_master_idx)(l_event_idx).apl_idx := l_apl_idx + 1;
1581 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1582 p_master_tab(l_master_idx)(l_event_idx).event_code := l_event_code;
1583 p_master_tab(l_master_idx)(l_event_idx).stage_code := l_stage_code;
1584 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1585 p_dt_idx_tab(l_master_idx) := l_event_idx;
1586
1587 END add_appl_fail_event;
1588
1589
1590 -- ----------------------------------------------------------------------------
1591 -- Adds hire event
1592 -- ----------------------------------------------------------------------------
1593 PROCEDURE add_hire_event
1594 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1595 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1596 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1597 p_info_rec IN OUT NOCOPY g_info_rec_type,
1598 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1599
1600 l_stage_code VARCHAR2(30);
1601 l_event_code VARCHAR2(30);
1602 l_master_idx PLS_INTEGER;
1603 l_event_idx PLS_INTEGER;
1604 l_apl_idx PLS_INTEGER;
1605 l_ind_rec g_ind_rec_type;
1606
1607 BEGIN
1608
1609 -- Copy indicator record
1610 l_ind_rec := p_ind_rec;
1611
1612 -- Set apl idx
1613 l_apl_idx := p_info_rec.last_apl_idx;
1614
1615 -- Set info details
1616 p_info_rec.hire_date := p_apl_tab(l_apl_idx).event_date;
1617 p_info_rec.latest_stage := 'HIRE_STG';
1618 IF (p_info_rec.idx_strt_date IS NULL) THEN
1619 p_info_rec.idx_strt_date := p_info_rec.hire_date;
1620 END IF;
1621
1622 -- Set indicators for hire
1623 l_ind_rec.appl_hire_evnt_ind := l_ind_rec.appl_ind;
1624 p_ind_rec.appl_hire_nevnt_ind := l_ind_rec.appl_ind;
1625 l_ind_rec.hire_evnt_ind := 1;
1626 p_ind_rec.hire_nevnt_ind := 1;
1627 l_ind_rec.init_appl_stg_ind := 0;
1628 p_ind_rec.init_appl_stg_ind := 0;
1629 l_ind_rec.asmt_stg_ind := 0;
1630 p_ind_rec.asmt_stg_ind := 0;
1631 l_ind_rec.offr_extd_stg_ind := 0;
1632 p_ind_rec.offr_extd_stg_ind := 0;
1633 l_ind_rec.strt_pndg_stg_ind := 0;
1634 p_ind_rec.strt_pndg_stg_ind := 0;
1635 l_ind_rec.hire_stg_ind := 1;
1636 p_ind_rec.hire_stg_ind := 1;
1637
1638 -- Set dimension change indicators
1639 IF l_ind_rec.appl_ind = 1 THEN
1640 IF p_apl_tab(l_apl_idx).org_organztn_fk <> p_apl_tab(1).org_organztn_fk THEN
1641 l_ind_rec.hire_org_chng_ind := 1;
1642 END IF;
1643 IF p_apl_tab(l_apl_idx).geo_location_fk <> p_apl_tab(1).geo_location_fk THEN
1644 l_ind_rec.hire_loc_chng_ind := 1;
1645 END IF;
1646 IF p_apl_tab(l_apl_idx).job_job_fk <> p_apl_tab(1).job_job_fk THEN
1647 l_ind_rec.hire_job_chng_ind := 1;
1648 END IF;
1649 IF p_apl_tab(l_apl_idx).grd_grade_fk <> p_apl_tab(1).grd_grade_fk THEN
1650 l_ind_rec.hire_grd_chng_ind := 1;
1651 END IF;
1652 IF p_apl_tab(l_apl_idx).pos_position_fk <> p_apl_tab(1).pos_position_fk THEN
1653 l_ind_rec.hire_pos_chng_ind := 1;
1654 END IF;
1655 END IF;
1656
1657 -- Set master table index
1658 l_master_idx := p_info_rec.hire_date - p_info_rec.idx_strt_date;
1659
1660 l_event_idx := get_event_idx
1661 (p_master_tab => p_master_tab,
1662 p_master_idx => l_master_idx);
1663
1664 -- Add Hire
1665 l_event_idx := l_event_idx + 1;
1666 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1667 p_master_tab(l_master_idx)(l_event_idx).apl_idx := l_apl_idx;
1668 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1669 p_master_tab(l_master_idx)(l_event_idx).event_code := 'EMPL_HIRE';
1670 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'HIRE_STG';
1671 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1672 p_dt_idx_tab(l_master_idx) := l_event_idx;
1673
1674 END add_hire_event;
1675
1676
1677 -- ----------------------------------------------------------------------------
1678 -- Adds POW band 1 event
1679 -- ----------------------------------------------------------------------------
1680 PROCEDURE add_pow1_event
1681 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1682 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1683 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1684 p_info_rec IN OUT NOCOPY g_info_rec_type,
1685 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1686
1687 l_stage_code VARCHAR2(30);
1688 l_event_code VARCHAR2(30);
1689 l_master_idx PLS_INTEGER;
1690 l_event_idx PLS_INTEGER;
1691 l_ind_rec g_ind_rec_type;
1692 l_apl_rec g_apl_event_rec_type;
1693
1694 BEGIN
1695
1696 -- Create pipeline record
1697 l_apl_rec := p_apl_tab(p_info_rec.last_apl_idx);
1698 p_info_rec.last_apl_idx := p_info_rec.last_apl_idx + 1;
1699 p_apl_tab(p_info_rec.last_apl_idx) := l_apl_rec;
1700 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_fk := p_info_rec.pow1_date;
1701 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_end_fk := p_info_rec.pow1_date;
1702 p_apl_tab(p_info_rec.last_apl_idx).event_date := p_info_rec.pow1_date;
1703
1704 -- Copy indicator record
1705 l_ind_rec := p_ind_rec;
1706
1707 -- Set indicators for pow1 end event
1708 l_ind_rec.hire_stg_ind := 0;
1709 l_ind_rec.pow1_end_evnt_ind := 1;
1710 p_ind_rec.pow1_end_nevnt_ind := 1;
1711
1712 -- Set master table index
1713 l_master_idx := p_info_rec.pow1_date - p_info_rec.idx_strt_date;
1714
1715 l_event_idx := get_event_idx
1716 (p_master_tab => p_master_tab,
1717 p_master_idx => l_master_idx);
1718
1719 -- Add LOW1 band end event
1720 l_event_idx := l_event_idx + 1;
1721 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1722 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_info_rec.last_apl_idx;
1723 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1724 p_master_tab(l_master_idx)(l_event_idx).event_code := 'EMPL_LOW1_END';
1725 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'HIRE_STG';
1726 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1727 p_dt_idx_tab(l_master_idx) := l_event_idx;
1728
1729 END add_pow1_event;
1730
1731
1732 -- ----------------------------------------------------------------------------
1733 -- Adds PERF event
1734 -- ----------------------------------------------------------------------------
1735 PROCEDURE add_perf_event
1736 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1737 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1738 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1739 p_info_rec IN OUT NOCOPY g_info_rec_type,
1740 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1741
1742 l_stage_code VARCHAR2(30);
1743 l_event_code VARCHAR2(30);
1744 l_master_idx PLS_INTEGER;
1745 l_event_idx PLS_INTEGER;
1746 l_ind_rec g_ind_rec_type;
1747 l_apl_rec g_apl_event_rec_type;
1748
1749 BEGIN
1750
1751 -- Create pipeline record
1752 l_apl_rec := p_apl_tab(p_info_rec.last_apl_idx);
1753 p_info_rec.last_apl_idx := p_info_rec.last_apl_idx + 1;
1754 p_apl_tab(p_info_rec.last_apl_idx) := l_apl_rec;
1755 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_fk := p_info_rec.perf_date;
1756 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_end_fk := p_info_rec.perf_date;
1757 p_apl_tab(p_info_rec.last_apl_idx).event_date := p_info_rec.perf_date;
1758 p_apl_tab(p_info_rec.last_apl_idx).prfm_perfband_fk := p_info_rec.perf_band;
1759 p_apl_tab(p_info_rec.last_apl_idx).perf_norm_rtng := p_info_rec.perf_norm_rtng;
1760
1761 -- Copy indicator record
1762 l_ind_rec := p_ind_rec;
1763
1764 -- Set indicators for hire
1765 l_ind_rec.perf_rtng_evnt_ind := 1;
1766 p_ind_rec.perf_rtng_nevnt_ind := 1;
1767
1768 -- Set master table index
1769 l_master_idx := p_info_rec.perf_date - p_info_rec.idx_strt_date;
1770
1771 l_event_idx := get_event_idx
1772 (p_master_tab => p_master_tab,
1773 p_master_idx => l_master_idx);
1774
1775 -- Add Performance Review
1776 l_event_idx := l_event_idx + 1;
1777 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1778 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_info_rec.last_apl_idx;
1779 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1780 p_master_tab(l_master_idx)(l_event_idx).event_code := 'EMPL_APR1';
1781 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'HIRE_STG';
1782 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1783 p_dt_idx_tab(l_master_idx) := l_event_idx;
1784
1785 END add_perf_event;
1786
1787
1788 -- ----------------------------------------------------------------------------
1789 -- Adds EMPL_TERM event if employment is ended before first LOW band is reached
1790 -- ----------------------------------------------------------------------------
1791 PROCEDURE add_emp_sprtn_event
1792 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1793 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1794 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1795 p_info_rec IN OUT NOCOPY g_info_rec_type,
1796 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1797
1798 l_stage_code VARCHAR2(30);
1799 l_event_code VARCHAR2(30);
1800 l_master_idx PLS_INTEGER;
1801 l_event_idx PLS_INTEGER;
1802 l_ind_rec g_ind_rec_type;
1803 l_apl_rec g_apl_event_rec_type;
1804
1805 BEGIN
1806
1807 -- Create pipeline record
1808 l_apl_rec := p_apl_tab(p_info_rec.last_apl_idx);
1809 p_info_rec.last_apl_idx := p_info_rec.last_apl_idx + 1;
1810 p_apl_tab(p_info_rec.last_apl_idx) := l_apl_rec;
1811 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_fk := p_info_rec.emp_sprtn_date;
1812 p_apl_tab(p_info_rec.last_apl_idx).time_day_evt_end_fk := p_info_rec.emp_sprtn_date;
1813 p_apl_tab(p_info_rec.last_apl_idx).event_date := p_info_rec.emp_sprtn_date;
1814
1815 -- Copy indicator record
1816 l_ind_rec := p_ind_rec;
1817
1818 -- Set indicators for separation event
1819 l_ind_rec.hire_stg_ind := 0;
1820 l_ind_rec.emp_sprtn_evnt_ind := 1;
1821 p_ind_rec.emp_sprtn_nevnt_ind := 1;
1822
1823 -- Set master table index
1824 l_master_idx := p_info_rec.emp_sprtn_date - p_info_rec.idx_strt_date;
1825
1826 l_event_idx := get_event_idx
1827 (p_master_tab => p_master_tab,
1828 p_master_idx => l_master_idx);
1829
1830 -- Add Employee Separation
1831 l_event_idx := l_event_idx + 1;
1832 p_info_rec.event_seq := p_info_rec.event_seq + 1;
1833 p_master_tab(l_master_idx)(l_event_idx).apl_idx := p_info_rec.last_apl_idx;
1834 p_master_tab(l_master_idx)(l_event_idx).event_seq := p_info_rec.event_seq;
1835 p_master_tab(l_master_idx)(l_event_idx).event_code := 'EMPL_TERM';
1836 p_master_tab(l_master_idx)(l_event_idx).stage_code := 'HIRE_STG';
1837 p_master_tab(l_master_idx)(l_event_idx).event_ind := l_ind_rec;
1838 p_dt_idx_tab(l_master_idx) := l_event_idx;
1839
1840 END add_emp_sprtn_event;
1841
1842
1843 -- ----------------------------------------------------------------------------
1844 -- Merges FKs into a single value for reporting
1845 -- ----------------------------------------------------------------------------
1846 FUNCTION get_merged_person_fk
1847 (p_vac_manager_irec IN NUMBER,
1848 p_vac_recruiter IN NUMBER,
1849 p_req_raised_by IN NUMBER,
1850 p_vac_org_id IN NUMBER,
1851 p_vac_bgr_id IN NUMBER)
1852 RETURN NUMBER IS
1853
1854 l_return_value NUMBER;
1855
1856 BEGIN
1857
1858 -- Default the return value
1859 l_return_value := -1;
1860
1861 -- Check recruitment manager from iRecruitment
1862 IF p_vac_manager_irec <> -1 THEN
1863
1864 l_return_value := p_vac_manager_irec;
1865
1866 ELSE
1867
1868 -- If that is not available, use PUI keys if profile enabled
1869 IF fnd_profile.value('HRI_REC_USE_PUI_MGR_KEYS') = 'Y' THEN
1870
1871 IF p_vac_recruiter <> -1 THEN
1872 l_return_value := p_vac_recruiter;
1873 ELSE
1874 l_return_value := p_req_raised_by;
1875 END IF;
1876
1877 END IF;
1878
1879 -- If no value has been found then try cost centre managers for vacancy org
1880 IF l_return_value = -1 AND p_vac_org_id <> -1 THEN
1881 l_return_value := hri_bpl_ccmgr.get_ccmgr_id
1882 (p_organization_id => p_vac_org_id);
1883 END IF;
1884
1885 -- ... and vacancy business group as a last resort
1886 IF l_return_value = -1 THEN
1887 l_return_value := hri_bpl_ccmgr.get_ccmgr_id
1888 (p_organization_id => p_vac_bgr_id);
1889 END IF;
1890
1891 END IF;
1892
1893 RETURN l_return_value;
1894
1895 END get_merged_person_fk;
1896
1897 -- ----------------------------------------------------------------------------
1898 -- Merges FKs into a single value for reporting
1899 -- ----------------------------------------------------------------------------
1900 FUNCTION get_merged_org_fk
1901 (p_vac_org_id IN NUMBER,
1902 p_vac_bgr_id IN NUMBER)
1903 RETURN NUMBER IS
1904
1905 BEGIN
1906
1907 IF p_vac_org_id IS NULL THEN
1908 RETURN p_vac_bgr_id;
1909 END IF;
1910
1911 RETURN p_vac_org_id;
1912
1913 END get_merged_org_fk;
1914
1915 -- ----------------------------------------------------------------------------
1916 -- Builds PL/SQL data structures up with applicant stage information
1917 -- ----------------------------------------------------------------------------
1918 PROCEDURE get_appl_stages
1919 (p_master_tab IN OUT NOCOPY g_master_tab_type,
1920 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
1921 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
1922 p_info_rec IN OUT NOCOPY g_info_rec_type,
1923 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
1924
1925 -- Get applicant events
1926 CURSOR appl_stage_csr IS
1927 SELECT
1928 asg.effective_start_date time_day_evt_fk
1929 ,asg.effective_end_date time_day_evt_end_fk
1930 ,asg.person_id person_cand_fk
1931 ,NVL(asg.supervisor_id, -1) person_mngr_fk
1932 ,NVL(vac.recruiter_id, -1) person_rcrt_fk
1933 ,NVL(vac.manager_id, -1) person_rmgr_fk
1934 ,NVL(pra.authorising_person_id, -1) person_auth_fk
1935 ,NVL(asg.person_referred_by_id, -1) person_refr_fk
1936 ,NVL(prq.person_id, -1) person_rsed_fk
1937 ,NVL(vac.organization_id, -1) org_organztn_fk
1938 ,vac.business_group_id org_organztn_bgrp_fk
1939 ,NVL(pra.run_by_organization_id, -1) org_organztn_recr_fk
1940 ,NVL(vac.location_id, -1) geo_location_fk
1941 ,NVL(vac.job_id, -1) job_job_fk
1942 ,NVL(vac.grade_id, -1) grd_grade_fk
1943 ,NVL(vac.position_id, -1) pos_position_fk
1944 ,NVL(vac.vacancy_id, -1) rvac_vacncy_fk
1945 ,NVL(asg.recruitment_activity_id, -1) ract_recactvy_fk
1946 ,NVL(ias.status_change_reason, NVL(asg.change_reason, 'NA_EDW'))
1947 rern_recevtrn_fk
1948 ,asg.application_id adt_application_id
1949 ,asg.business_group_id adt_business_group_id
1950 ,NVL(TRUNC(ias.status_change_date), asg.effective_start_date)
1951 event_date
1952 ,NVL(ast_irc.per_system_status, ast.per_system_status)
1953 stage_code
1954 ,NVL(ast_irc.user_status, ast.user_status)
1955 stage_name
1956 ,NVL(ast_irc.assignment_status_type_id, ast.assignment_status_type_id)
1957 assignment_status_type_id
1958 ,NVL(apl.termination_reason, 'NA_EDW') termination_reason
1959 FROM
1960 per_all_assignments_f asg
1961 ,per_assignment_status_types ast
1962 ,per_assignment_status_types ast_irc
1963 ,per_all_vacancies vac
1964 ,irc_assignment_statuses ias
1965 ,per_applications apl
1966 ,per_recruitment_activities pra
1967 ,per_requisitions prq
1968 WHERE asg.assignment_id = p_info_rec.asg_id
1969 AND apl.application_id = asg.application_id
1970 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1971 AND ias.assignment_status_type_id = ast_irc.assignment_status_type_id (+)
1972 AND asg.assignment_type = 'A'
1973 AND asg.recruitment_activity_id = pra.recruitment_activity_id (+)
1974 AND asg.vacancy_id = vac.vacancy_id (+)
1975 AND vac.requisition_id = prq.requisition_id (+)
1976 AND asg.assignment_id = ias.assignment_id (+)
1977 AND ias.status_change_date (+) BETWEEN asg.effective_start_date
1978 AND asg.effective_end_date
1979 AND vac.date_from (+) >= g_dbi_start_date
1980 AND apl.date_received >= g_dbi_start_date
1981 AND ast_irc.per_system_status (+) <> 'TERM_APL'
1982 ORDER BY asg.effective_start_date;
1983
1984 -- Get person type of applicant on application date
1985 CURSOR appl_ptyp_csr(v_person_id IN NUMBER,
1986 v_effective_date IN DATE) IS
1987 SELECT
1988 ppt.system_person_type
1989 FROM
1990 per_person_type_usages_f ptu
1991 ,per_person_types ppt
1992 WHERE ptu.person_id = v_person_id
1993 AND v_effective_date BETWEEN ptu.effective_start_date
1994 AND ptu.effective_end_date
1995 AND ptu.person_type_id = ppt.person_type_id
1996 AND ppt.system_person_type IN ('EMP','CWK');
1997
1998 l_time_day_evt_fk g_date_tab_type;
1999 l_time_day_evt_end_fk g_date_tab_type;
2000 l_person_cand_fk g_number_tab_type;
2001 l_person_mngr_fk g_number_tab_type;
2002 l_person_rcrt_fk g_number_tab_type;
2003 l_person_rmgr_fk g_number_tab_type;
2004 l_person_auth_fk g_number_tab_type;
2005 l_person_refr_fk g_number_tab_type;
2006 l_person_rsed_fk g_number_tab_type;
2007 l_org_organztn_fk g_number_tab_type;
2008 l_org_organztn_bgrp_fk g_number_tab_type;
2009 l_org_organztn_recr_fk g_number_tab_type;
2010 l_geo_location_fk g_number_tab_type;
2011 l_job_job_fk g_number_tab_type;
2012 l_grd_grade_fk g_number_tab_type;
2013 l_pos_position_fk g_number_tab_type;
2014 l_rvac_vacncy_fk g_number_tab_type;
2015 l_ract_recactvy_fk g_number_tab_type;
2016 l_rern_recevtrn_fk g_varchar2_tab_type;
2017 l_adt_application_id g_number_tab_type;
2018 l_adt_business_group_id g_number_tab_type;
2019 l_event_date g_date_tab_type;
2020 l_per_system_status g_varchar2_tab_type;
2021 l_user_status g_varchar2_tab_type;
2022 l_assignment_status_type_id g_number_tab_type;
2023 l_term_rsn g_varchar2_tab_type;
2024
2025 BEGIN
2026
2027 -- Bulk load cursor into PL/SQL tables
2028 OPEN appl_stage_csr;
2029 FETCH appl_stage_csr
2030 BULK COLLECT INTO
2031 l_time_day_evt_fk,
2032 l_time_day_evt_end_fk,
2033 l_person_cand_fk,
2034 l_person_mngr_fk,
2035 l_person_rcrt_fk,
2036 l_person_rmgr_fk,
2037 l_person_auth_fk,
2038 l_person_refr_fk,
2039 l_person_rsed_fk,
2040 l_org_organztn_fk,
2041 l_org_organztn_bgrp_fk,
2042 l_org_organztn_recr_fk,
2043 l_geo_location_fk,
2044 l_job_job_fk,
2045 l_grd_grade_fk,
2046 l_pos_position_fk,
2047 l_rvac_vacncy_fk,
2048 l_ract_recactvy_fk,
2049 l_rern_recevtrn_fk,
2050 l_adt_application_id,
2051 l_adt_business_group_id,
2052 l_event_date,
2053 l_per_system_status,
2054 l_user_status,
2055 l_assignment_status_type_id,
2056 l_term_rsn;
2057 CLOSE appl_stage_csr;
2058
2059 -- If data is returned, translate to table of records
2060 IF l_time_day_evt_fk.EXISTS(1) THEN
2061
2062 -- If data is found record information and indicators
2063 p_info_rec.idx_strt_date := l_time_day_evt_fk(1);
2064 p_info_rec.last_apl_idx := l_time_day_evt_fk.LAST;
2065 p_info_rec.appl_term_rsn := l_term_rsn(1);
2066 p_ind_rec.appl_ind := 1;
2067 p_ind_rec.appl_new_ind := 1;
2068
2069 -- Set applicant person type indicators
2070 FOR ptyp_rec IN appl_ptyp_csr(l_person_cand_fk(1), l_time_day_evt_fk(1)) LOOP
2071 IF ptyp_rec.system_person_type = 'EMP' THEN
2072 p_ind_rec.appl_emp_ind := 1;
2073 p_ind_rec.appl_new_ind := 0;
2074 ELSIF ptyp_rec.system_person_type = 'CWK' THEN
2075 p_ind_rec.appl_cwk_ind := 1;
2076 p_ind_rec.appl_new_ind := 0;
2077 END IF;
2078 END LOOP;
2079
2080 -- Loop through fetched data and populate table of records
2081 FOR i IN 1..l_time_day_evt_fk.LAST LOOP
2082
2083 -- Populate stage table
2084 p_apl_tab(i).time_day_evt_fk := l_time_day_evt_fk(i);
2085 p_apl_tab(i).time_day_evt_end_fk := l_time_day_evt_end_fk(i);
2086 p_apl_tab(i).person_cand_fk := l_person_cand_fk(i);
2087 p_apl_tab(i).person_mngr_fk := l_person_mngr_fk(i);
2088 p_apl_tab(i).person_rcrt_fk := l_person_rcrt_fk(i);
2089 p_apl_tab(i).person_rmgr_fk := l_person_rmgr_fk(i);
2090 p_apl_tab(i).person_auth_fk := l_person_auth_fk(i);
2091 p_apl_tab(i).person_refr_fk := l_person_refr_fk(i);
2092 p_apl_tab(i).person_rsed_fk := l_person_rsed_fk(i);
2093 p_apl_tab(i).org_organztn_fk := l_org_organztn_fk(i);
2094 p_apl_tab(i).org_organztn_recr_fk := l_org_organztn_recr_fk(i);
2095 p_apl_tab(i).geo_location_fk := l_geo_location_fk(i);
2096 p_apl_tab(i).job_job_fk := l_job_job_fk(i);
2097 p_apl_tab(i).grd_grade_fk := l_grd_grade_fk(i);
2098 p_apl_tab(i).pos_position_fk := l_pos_position_fk(i);
2099 p_apl_tab(i).prfm_perfband_fk := -5;
2100 p_apl_tab(i).rvac_vacncy_fk := l_rvac_vacncy_fk(i);
2101 p_apl_tab(i).ract_recactvy_fk := l_ract_recactvy_fk(i);
2102 p_apl_tab(i).rern_recevtrn_fk := l_rern_recevtrn_fk(i);
2103 p_apl_tab(i).tarn_trmaplrn_fk := l_term_rsn(i);
2104 p_apl_tab(i).adt_application_id := l_adt_application_id(i);
2105 p_apl_tab(i).adt_business_group_id := l_adt_business_group_id(i);
2106 p_apl_tab(i).event_date := l_event_date(i);
2107 p_apl_tab(i).per_system_status := l_per_system_status(i);
2108 p_apl_tab(i).user_status := l_user_status(i);
2109 p_apl_tab(i).assignment_status_type_id := l_assignment_status_type_id(i);
2110 p_apl_tab(i).headcount
2111 := hri_bpl_abv.calc_abv
2112 (p_assignment_id => p_info_rec.asg_id
2113 ,p_business_group_id => l_adt_business_group_id(i)
2114 ,p_budget_type => 'HEAD'
2115 ,p_effective_date => l_time_day_evt_fk(i));
2116 p_apl_tab(i).fte
2117 := hri_bpl_abv.calc_abv
2118 (p_assignment_id => p_info_rec.asg_id
2119 ,p_business_group_id => l_adt_business_group_id(i)
2120 ,p_budget_type => 'FTE'
2121 ,p_effective_date => l_time_day_evt_fk(i));
2122 p_apl_tab(i).person_mrgd_fk
2123 := get_merged_person_fk
2124 (p_vac_manager_irec => l_person_rmgr_fk(i),
2125 p_vac_recruiter => l_person_rcrt_fk(i),
2126 p_req_raised_by => l_person_rsed_fk(i),
2127 p_vac_org_id => l_org_organztn_fk(i),
2128 p_vac_bgr_id => l_org_organztn_bgrp_fk(i));
2129 p_apl_tab(i).org_organztn_mrgd_fk
2130 := get_merged_org_fk
2131 (p_vac_org_id => l_org_organztn_fk(i),
2132 p_vac_bgr_id => l_org_organztn_bgrp_fk(i));
2133
2134 -- Interpret recruitment event
2135 interpret_appl_event
2136 (p_master_tab => p_master_tab,
2137 p_apl_tab => p_apl_tab,
2138 p_apl_idx => i,
2139 p_dt_idx_tab => p_dt_idx_tab,
2140 p_info_rec => p_info_rec,
2141 p_ind_rec => p_ind_rec);
2142
2143 -- Add stage start date
2144 p_apl_tab(i).stage_start_date := p_info_rec.curr_strt_date;
2145
2146 END LOOP;
2147
2148 -- Determine if application is ended
2149 IF (l_time_day_evt_end_fk(l_time_day_evt_fk.LAST) < g_end_of_time OR
2150 p_info_rec.latest_stage = 'APPL_TERM_STG') THEN
2151 p_info_rec.appl_ended_ind := 1;
2152 p_info_rec.appl_end_date := l_time_day_evt_end_fk(l_time_day_evt_fk.LAST);
2153
2154 -- If application did not reach ACCEPTED stage then it failed
2155 IF (p_info_rec.latest_stage = 'OFFR_EXTD_STG' OR
2156 p_info_rec.latest_stage = 'ASMT_STG' OR
2157 p_info_rec.latest_stage = 'INIT_APPL_STG') THEN
2158
2159 -- add rejection stage
2160 add_appl_fail_event
2161 (p_master_tab => p_master_tab,
2162 p_apl_tab => p_apl_tab,
2163 p_dt_idx_tab => p_dt_idx_tab,
2164 p_info_rec => p_info_rec,
2165 p_ind_rec => p_ind_rec);
2166
2167 END IF;
2168 ELSE
2169 p_info_rec.appl_ended_ind := 0;
2170 END IF;
2171
2172 ELSE
2173
2174 -- No applicant data found - assignment is a hire that bypassed the
2175 -- recruitment process
2176 p_ind_rec.appl_ind := 0;
2177
2178 END IF;
2179
2180 END get_appl_stages;
2181
2182
2183 -- ----------------------------------------------------------------------------
2184 -- Builds PL/SQL data structures up with employment stage information
2185 -- ----------------------------------------------------------------------------
2186 PROCEDURE get_empl_stages
2187 (p_master_tab IN OUT NOCOPY g_master_tab_type,
2188 p_apl_tab IN OUT NOCOPY g_apl_tab_type,
2189 p_dt_idx_tab IN OUT NOCOPY g_number_tab_type,
2190 p_info_rec IN OUT NOCOPY g_info_rec_type,
2191 p_ind_rec IN OUT NOCOPY g_ind_rec_type) IS
2192
2193 CURSOR emp_appl_details_csr IS
2194 SELECT
2195 pos.date_start
2196 ,pos.actual_termination_date
2197 ,emp.assignment_id
2198 ,emp.effective_start_date
2199 ,emp.effective_end_date
2200 ,emp.person_id
2201 ,NVL(emp.supervisor_id, -1)
2202 ,emp.organization_id
2203 ,NVL(emp.location_id, -1)
2204 ,NVL(emp.job_id, -1)
2205 ,NVL(emp.grade_id, -1)
2206 ,NVL(emp.position_id, -1)
2207 ,emp.business_group_id
2208 FROM
2209 per_all_assignments_f emp
2210 ,per_periods_of_service pos
2211 WHERE emp.person_id = p_info_rec.psn_id
2212 AND emp.assignment_type = 'E'
2213 AND p_info_rec.appl_end_date + 1 BETWEEN emp.effective_start_date
2214 AND emp.effective_end_date
2215 AND (emp.assignment_id = p_info_rec.asg_id OR
2216 (emp.primary_flag = 'Y' and
2217 emp.effective_start_date = p_info_rec.appl_end_date + 1))
2218 AND emp.period_of_service_id = pos.period_of_service_id;
2219
2220 CURSOR emp_details_csr IS
2221 SELECT
2222 pos.date_start
2223 ,pos.actual_termination_date
2224 ,emp.assignment_id
2225 ,emp.effective_start_date
2226 ,emp.effective_end_date
2227 ,emp.person_id
2228 ,NVL(emp.supervisor_id, -1)
2229 ,emp.organization_id
2230 ,NVL(emp.location_id, -1)
2231 ,NVL(emp.job_id, -1)
2232 ,NVL(emp.grade_id, -1)
2233 ,NVL(emp.position_id, -1)
2234 ,emp.business_group_id
2235 FROM
2236 per_all_assignments_f emp
2237 ,per_periods_of_service pos
2238 WHERE emp.assignment_id = p_info_rec.asg_id
2239 AND emp.effective_start_date = pos.date_start
2240 AND emp.period_of_service_id = pos.period_of_service_id
2241 AND emp.effective_start_date >= g_dbi_start_date;
2242
2243 CURSOR perf_details_csr(v_asg_id IN NUMBER,
2244 v_hire_date IN DATE) IS
2245 SELECT
2246 effective_change_date
2247 ,perf_nrmlsd_rating
2248 ,perf_band
2249 FROM hri_mb_asgn_events_ct evt
2250 WHERE assignment_id = v_asg_id
2251 AND effective_change_date BETWEEN v_hire_date
2252 AND add_months(v_hire_date, 24)
2253 AND perf_rating_change_ind = 1;
2254
2255 l_hire_date DATE;
2256 l_term_date DATE;
2257 l_apl_idx NUMBER;
2258 l_asg_id NUMBER;
2259 l_pow2_start_date DATE;
2260 l_perf_event_date DATE;
2261 l_perf_rating NUMBER;
2262 l_perf_band NUMBER;
2263
2264 BEGIN
2265
2266 -- If testing for applicant success use emp_appl cursor
2267 IF p_ind_rec.appl_ind = 1 THEN
2268
2269 l_apl_idx := p_info_rec.last_apl_idx + 1;
2270
2271 OPEN emp_appl_details_csr;
2272 FETCH emp_appl_details_csr INTO
2273 l_hire_date,
2274 l_term_date,
2275 l_asg_id,
2276 p_apl_tab(l_apl_idx).time_day_evt_fk,
2277 p_apl_tab(l_apl_idx).time_day_evt_end_fk,
2278 p_apl_tab(l_apl_idx).person_cand_fk,
2279 p_apl_tab(l_apl_idx).person_mngr_fk,
2280 p_apl_tab(l_apl_idx).org_organztn_fk,
2281 p_apl_tab(l_apl_idx).geo_location_fk,
2282 p_apl_tab(l_apl_idx).job_job_fk,
2283 p_apl_tab(l_apl_idx).grd_grade_fk,
2284 p_apl_tab(l_apl_idx).pos_position_fk,
2285 p_apl_tab(l_apl_idx).adt_business_group_id;
2286 CLOSE emp_appl_details_csr;
2287
2288 -- If no hire date was found, then application was unsuccessful
2289 IF l_hire_date IS NULL THEN
2290
2291 -- add rejection stage
2292 add_appl_fail_event
2293 (p_master_tab => p_master_tab,
2294 p_apl_tab => p_apl_tab,
2295 p_dt_idx_tab => p_dt_idx_tab,
2296 p_info_rec => p_info_rec,
2297 p_ind_rec => p_ind_rec);
2298
2299 ELSE
2300
2301 -- Default application details
2302 p_apl_tab(l_apl_idx).person_rcrt_fk := p_apl_tab(l_apl_idx-1).person_rcrt_fk;
2303 p_apl_tab(l_apl_idx).person_rmgr_fk := p_apl_tab(l_apl_idx-1).person_rmgr_fk;
2304 p_apl_tab(l_apl_idx).person_auth_fk := p_apl_tab(l_apl_idx-1).person_auth_fk;
2305 p_apl_tab(l_apl_idx).person_refr_fk := p_apl_tab(l_apl_idx-1).person_refr_fk;
2306 p_apl_tab(l_apl_idx).person_rsed_fk := p_apl_tab(l_apl_idx-1).person_rsed_fk;
2307 p_apl_tab(l_apl_idx).person_mrgd_fk := p_apl_tab(l_apl_idx-1).person_mrgd_fk;
2308 p_apl_tab(l_apl_idx).org_organztn_mrgd_fk := p_apl_tab(l_apl_idx-1).org_organztn_mrgd_fk;
2309 p_apl_tab(l_apl_idx).org_organztn_recr_fk := p_apl_tab(l_apl_idx-1).org_organztn_recr_fk;
2310 p_apl_tab(l_apl_idx).prfm_perfband_fk := p_apl_tab(l_apl_idx-1).prfm_perfband_fk;
2311 p_apl_tab(l_apl_idx).rvac_vacncy_fk := p_apl_tab(l_apl_idx-1).rvac_vacncy_fk;
2312 p_apl_tab(l_apl_idx).ract_recactvy_fk := p_apl_tab(l_apl_idx-1).ract_recactvy_fk;
2313 p_apl_tab(l_apl_idx).rern_recevtrn_fk := 'NA_EDW';
2314 p_apl_tab(l_apl_idx).tarn_trmaplrn_fk := 'NA_EDW';
2315 p_apl_tab(l_apl_idx).adt_application_id := p_apl_tab(l_apl_idx-1).adt_application_id;
2316 p_apl_tab(l_apl_idx).per_system_status := null;
2317 p_apl_tab(l_apl_idx).user_status := null;
2318 p_apl_tab(l_apl_idx).assignment_status_type_id := to_number(null);
2319 p_info_rec.last_apl_idx := l_apl_idx;
2320
2321 END IF;
2322
2323 -- Otherwise use emp cursor
2324 ELSE
2325
2326 l_apl_idx := 1;
2327
2328 OPEN emp_details_csr;
2329 FETCH emp_details_csr INTO
2330 l_hire_date,
2331 l_term_date,
2332 l_asg_id,
2333 p_apl_tab(l_apl_idx).time_day_evt_fk,
2334 p_apl_tab(l_apl_idx).time_day_evt_end_fk,
2335 p_apl_tab(l_apl_idx).person_cand_fk,
2336 p_apl_tab(l_apl_idx).person_mngr_fk,
2337 p_apl_tab(l_apl_idx).org_organztn_fk,
2338 p_apl_tab(l_apl_idx).geo_location_fk,
2339 p_apl_tab(l_apl_idx).job_job_fk,
2340 p_apl_tab(l_apl_idx).grd_grade_fk,
2341 p_apl_tab(l_apl_idx).pos_position_fk,
2342 p_apl_tab(l_apl_idx).adt_business_group_id;
2343 CLOSE emp_details_csr;
2344
2345 -- Default application details
2346 p_apl_tab(l_apl_idx).person_rcrt_fk := -1;
2347 p_apl_tab(l_apl_idx).person_rmgr_fk := -1;
2348 p_apl_tab(l_apl_idx).person_auth_fk := -1;
2349 p_apl_tab(l_apl_idx).person_refr_fk := -1;
2350 p_apl_tab(l_apl_idx).person_rsed_fk := -1;
2351 p_apl_tab(l_apl_idx).person_mrgd_fk := p_apl_tab(l_apl_idx).person_mngr_fk;
2352 p_apl_tab(l_apl_idx).org_organztn_mrgd_fk := p_apl_tab(l_apl_idx).org_organztn_fk;
2353 p_apl_tab(l_apl_idx).org_organztn_recr_fk := -1;
2354 p_apl_tab(l_apl_idx).prfm_perfband_fk := -5;
2355 p_apl_tab(l_apl_idx).rvac_vacncy_fk := -1;
2356 p_apl_tab(l_apl_idx).ract_recactvy_fk := -1;
2357 p_apl_tab(l_apl_idx).rern_recevtrn_fk := 'NA_EDW';
2358 p_apl_tab(l_apl_idx).tarn_trmaplrn_fk := 'NA_EDW';
2359 p_apl_tab(l_apl_idx).adt_application_id := to_number(null);
2360 p_apl_tab(l_apl_idx).per_system_status := null;
2361 p_apl_tab(l_apl_idx).user_status := null;
2362 p_apl_tab(l_apl_idx).assignment_status_type_id := to_number(null);
2363 p_info_rec.last_apl_idx := l_apl_idx;
2364
2365 END IF;
2366
2367 -- If employment data is found, add a record to the applicant details table
2368 IF l_hire_date IS NOT NULL THEN
2369
2370 -- Add in derived values
2371 p_apl_tab(l_apl_idx).event_date := p_apl_tab(l_apl_idx).time_day_evt_fk;
2372 p_apl_tab(l_apl_idx).stage_start_date := p_apl_tab(l_apl_idx).time_day_evt_fk;
2373 p_apl_tab(l_apl_idx).headcount
2374 := hri_bpl_abv.calc_abv
2375 (p_assignment_id => l_asg_id
2376 ,p_business_group_id => p_apl_tab(l_apl_idx).adt_business_group_id
2377 ,p_budget_type => 'HEAD'
2378 ,p_effective_date => p_apl_tab(l_apl_idx).time_day_evt_fk);
2379 p_apl_tab(l_apl_idx).fte
2380 := hri_bpl_abv.calc_abv
2381 (p_assignment_id => l_asg_id
2382 ,p_business_group_id => p_apl_tab(l_apl_idx).adt_business_group_id
2383 ,p_budget_type => 'FTE'
2384 ,p_effective_date => p_apl_tab(l_apl_idx).time_day_evt_fk);
2385
2386 -- add hire stage
2387 add_hire_event
2388 (p_master_tab => p_master_tab,
2389 p_apl_tab => p_apl_tab,
2390 p_dt_idx_tab => p_dt_idx_tab,
2391 p_info_rec => p_info_rec,
2392 p_ind_rec => p_ind_rec);
2393
2394 -- Set POW2 start date
2395 IF g_pow1_no_days IS NOT NULL THEN
2396 l_pow2_start_date := l_hire_date + g_pow1_no_days;
2397 ELSE
2398 l_pow2_start_date := ADD_MONTHS(l_hire_date, g_pow1_no_months);
2399 END IF;
2400
2401 -- Check if first period of work band is passed
2402 IF ((l_term_date IS NULL AND
2403 TRUNC(g_sysdate) >= l_pow2_start_date) OR
2404 l_term_date >= l_pow2_start_date) THEN
2405
2406 -- Log event date
2407 p_info_rec.pow1_date := l_pow2_start_date;
2408
2409 -- add pow1 event
2410 add_pow1_event
2411 (p_master_tab => p_master_tab,
2412 p_apl_tab => p_apl_tab,
2413 p_dt_idx_tab => p_dt_idx_tab,
2414 p_info_rec => p_info_rec,
2415 p_ind_rec => p_ind_rec);
2416
2417 ELSIF l_term_date IS NOT NULL THEN
2418
2419 -- Log event date
2420 p_info_rec.emp_sprtn_date := l_term_date;
2421
2422 -- add employee separation before reaching pow1
2423 add_emp_sprtn_event
2424 (p_master_tab => p_master_tab,
2425 p_apl_tab => p_apl_tab,
2426 p_dt_idx_tab => p_dt_idx_tab,
2427 p_info_rec => p_info_rec,
2428 p_ind_rec => p_ind_rec);
2429
2430 END IF;
2431
2432 -- Check if performance review has occurred since hire
2433 OPEN perf_details_csr(l_asg_id, l_hire_date);
2434 FETCH perf_details_csr INTO l_perf_event_date, l_perf_rating, l_perf_band;
2435 CLOSE perf_details_csr;
2436
2437 -- If so add perf event
2438 IF l_perf_band IS NOT NULL THEN
2439
2440 -- Log event info
2441 p_info_rec.perf_date := l_perf_event_date;
2442 p_info_rec.perf_band := l_perf_band;
2443 p_info_rec.perf_norm_rtng := l_perf_rating;
2444
2445 -- add pow1 event
2446 add_perf_event
2447 (p_master_tab => p_master_tab,
2448 p_apl_tab => p_apl_tab,
2449 p_dt_idx_tab => p_dt_idx_tab,
2450 p_info_rec => p_info_rec,
2451 p_ind_rec => p_ind_rec);
2452
2453 END IF;
2454
2455 -- If separation is required add that event...
2456 -- IF l_term_date IS NOT NULL THEN
2457 -- add_sepn_event
2458 -- (p_master_tab => p_master_tab,
2459 -- p_apl_tab => p_apl_tab,
2460 -- p_dt_idx_tab => p_dt_idx_tab,
2461 -- p_info_rec => p_info_rec,
2462 -- p_ind_rec => p_ind_rec);
2463 -- END IF;
2464
2465 END IF;
2466
2467 -- get appraisal
2468
2469 END get_empl_stages;
2470
2471
2472 -- ----------------------------------------------------------------------------
2473 -- Processes a single assignment
2474 -- ----------------------------------------------------------------------------
2475 PROCEDURE process_assignment(p_asg_id IN NUMBER,
2476 p_psn_id IN NUMBER) IS
2477
2478 l_apl_tab g_apl_tab_type;
2479 l_master_tab g_master_tab_type;
2480 l_dt_idx_tab g_number_tab_type;
2481 l_info_rec g_info_rec_type;
2482 l_ind_rec g_ind_rec_type;
2483
2484 BEGIN
2485
2486 -- dbg('In process_assignment for: ' || to_char(p_asg_id));
2487
2488 -- Initialize data structures
2489 reset_event_cache;
2490 l_ind_rec := initialize_indicator_rec;
2491 l_info_rec.asg_id := p_asg_id;
2492 l_info_rec.psn_id := p_psn_id;
2493 l_info_rec.event_seq := 0;
2494 l_info_rec.perf_band := -5;
2495
2496 -- Load Recruitment Stages
2497 get_appl_stages
2498 (p_master_tab => l_master_tab,
2499 p_apl_tab => l_apl_tab,
2500 p_dt_idx_tab => l_dt_idx_tab,
2501 p_ind_rec => l_ind_rec,
2502 p_info_rec => l_info_rec);
2503
2504 -- Don't load employment stages if application is still open
2505 -- or if application failed
2506 IF (l_info_rec.appl_ended_ind = 0 OR
2507 l_info_rec.appl_term_date IS NOT NULL) THEN
2508
2509 -- Skip
2510 null;
2511
2512 ELSE
2513
2514 -- Load Employment Stages
2515 get_empl_stages
2516 (p_master_tab => l_master_tab,
2517 p_apl_tab => l_apl_tab,
2518 p_dt_idx_tab => l_dt_idx_tab,
2519 p_ind_rec => l_ind_rec,
2520 p_info_rec => l_info_rec);
2521
2522 END IF;
2523
2524 -- Load data into PL/SQL tables ready for bulk insert
2525 merge_and_insert_data
2526 (p_master_tab => l_master_tab,
2527 p_apl_tab => l_apl_tab,
2528 p_dt_idx_tab => l_dt_idx_tab,
2529 p_info_rec => l_info_rec,
2530 p_ind_rec => l_ind_rec);
2531
2532 END process_assignment;
2533
2534 -- ----------------------------------------------------------------------------
2535 -- PROCESS_RANGE
2536 -- This procedure includes the logic required for processing the assignments
2537 -- which have been included in the range. It is dynamically invoked by the
2538 -- multithreading child process. It manages the multithreading ranges.
2539 -- ----------------------------------------------------------------------------
2540 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
2541 ,retcode OUT NOCOPY NUMBER
2542 ,p_mthd_action_id IN NUMBER
2543 ,p_mthd_range_id IN NUMBER
2544 ,p_start_object_id IN NUMBER
2545 ,p_end_object_id IN NUMBER) IS
2546
2547 CURSOR full_asg_csr IS
2548 SELECT DISTINCT
2549 assignment_id
2550 ,person_id
2551 FROM per_all_assignments_f
2552 WHERE assignment_type IN ('E','A')
2553 AND effective_end_date >= g_dbi_start_date
2554 AND assignment_id BETWEEN p_start_object_id AND p_end_object_id;
2555
2556 BEGIN
2557
2558 -- Initialize global data structure
2559 g_no_rows := 0;
2560
2561 -- Set the parameters
2562 set_parameters
2563 (p_mthd_action_id => p_mthd_action_id,
2564 p_mthd_stage_code => 'PROCESS_RANGE');
2565
2566 -- Set sysdate parameter
2567 g_sysdate := sysdate;
2568
2569 -- Process range in corresponding refresh mode
2570 IF g_full_refresh = 'Y' THEN
2571
2572 FOR asg_rec IN full_asg_csr LOOP
2573
2574 process_assignment
2575 (p_asg_id => asg_rec.assignment_id,
2576 p_psn_id => asg_rec.person_id);
2577
2578 END LOOP;
2579
2580 END IF;
2581
2582 -- Insert any stored rows
2583 bulk_insert_rows;
2584
2585 END process_range;
2586
2587
2588 -- ----------------------------------------------------------------------------
2589 -- Adds default records for vacancies
2590 -- ----------------------------------------------------------------------------
2591 PROCEDURE process_vacancies IS
2592
2593 BEGIN
2594
2595 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
2596
2597 INSERT /*+ APPEND */ INTO hri_mb_rec_cand_pipln_ct
2598 (time_day_evt_fk,
2599 time_day_evt_end_fk,
2600 time_day_stg_evt_eff_end_fk,
2601 per_person_cand_fk,
2602 per_person_mngr_fk,
2603 per_person_rcrt_fk,
2604 per_person_rmgr_fk,
2605 per_person_auth_fk,
2606 per_person_refr_fk,
2607 per_person_rsed_fk,
2608 per_person_mrgd_fk,
2609 org_organztn_fk,
2610 org_organztn_mrgd_fk,
2611 org_organztn_recr_fk,
2612 geo_location_fk,
2613 job_job_fk,
2614 grd_grade_fk,
2615 pos_position_fk,
2616 prfm_perfband_fk,
2617 rvac_vacncy_fk,
2618 ract_recactvy_fk,
2619 rev_recevent_fk,
2620 rern_recevtrn_fk,
2621 tarn_trmaplrn_fk,
2622 event_seq,
2623 appl_ind,
2624 appl_new_ind,
2625 appl_emp_ind,
2626 appl_cwk_ind,
2627 appl_strt_evnt_ind,
2628 appl_strt_nevnt_ind,
2629 asmt_strt_evnt_ind,
2630 asmt_strt_nevnt_ind,
2631 asmt_end_evnt_ind,
2632 asmt_end_nevnt_ind,
2633 offr_extd_evnt_ind,
2634 offr_extd_nevnt_ind,
2635 offr_rjct_evnt_ind,
2636 offr_rjct_nevnt_ind,
2637 offr_acpt_evnt_ind,
2638 offr_acpt_nevnt_ind,
2639 appl_term_evnt_ind,
2640 appl_term_nevnt_ind,
2641 appl_term_vol_evnt_ind,
2642 appl_term_vol_nevnt_ind,
2643 appl_term_invol_evnt_ind,
2644 appl_term_invol_nevnt_ind,
2645 appl_hire_evnt_ind,
2646 appl_hire_nevnt_ind,
2647 hire_evnt_ind,
2648 hire_nevnt_ind,
2649 post_hire_pow1_end_evnt_ind,
2650 post_hire_pow1_end_nevnt_ind,
2651 post_hire_perf_evnt_ind,
2652 post_hire_perf_nevnt_ind,
2653 emp_sprtn_evnt_ind,
2654 emp_sprtn_nevnt_ind,
2655 hire_org_chng_ind,
2656 hire_job_chng_ind,
2657 hire_pos_chng_ind,
2658 hire_grd_chng_ind,
2659 hire_loc_chng_ind,
2660 current_record_ind,
2661 current_stage_strt_ind,
2662 init_appl_stg_ind,
2663 asmt_stg_ind,
2664 offr_extd_stg_ind,
2665 strt_pndg_stg_ind,
2666 hire_stg_ind,
2667 gen_record_ind,
2668 adt_assignment_id,
2669 adt_application_id,
2670 last_update_date,
2671 last_updated_by,
2672 last_update_login,
2673 created_by,
2674 creation_date)
2675 SELECT
2676 date_from
2677 ,NVL(date_to, g_end_of_time)
2678 ,to_date(null)
2679 ,-1
2680 ,-1
2681 ,-1
2682 ,-1
2683 ,-1
2684 ,-1
2685 ,NVL(recruiter_id, -1)
2686 ,NVL(manager_id, -1)
2687 ,NVL(organization_id, -1)
2688 ,-1
2689 ,business_group_id
2690 ,NVL(location_id, -1)
2691 ,NVL(job_id, -1)
2692 ,NVL(grade_id, -1)
2693 ,NVL(position_id, -1)
2694 ,-5
2695 ,vacancy_id
2696 ,-1
2697 ,'VAC_OPEN'
2698 ,'NA_EDW'
2699 ,'NA_EDW'
2700 ,-1
2701 ,0
2702 ,0
2703 ,0
2704 ,0
2705 ,0
2706 ,0
2707 ,0
2708 ,0
2709 ,0
2710 ,0
2711 ,0
2712 ,0
2713 ,0
2714 ,0
2715 ,0
2716 ,0
2717 ,0
2718 ,0
2719 ,0
2720 ,0
2721 ,0
2722 ,0
2723 ,0
2724 ,0
2725 ,0
2726 ,0
2727 ,0
2728 ,0
2729 ,0
2730 ,0
2731 ,0
2732 ,0
2733 ,0
2734 ,0
2735 ,0
2736 ,0
2737 ,0
2738 ,0
2739 ,0
2740 ,0
2741 ,0
2742 ,0
2743 ,0
2744 ,0
2745 ,1
2746 ,-1
2747 ,-1
2748 ,g_sysdate
2749 ,g_user
2750 ,g_user
2751 ,g_user
2752 ,g_sysdate
2753 FROM per_all_vacancies
2754 WHERE date_from >= g_dbi_start_date;
2755
2756 -- Commit
2757 COMMIT;
2758
2759 END process_vacancies;
2760
2761
2762 -- ----------------------------------------------------------------------------
2763 -- Pre process entry point
2764 -- ----------------------------------------------------------------------------
2765 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
2766 p_sqlstr OUT NOCOPY VARCHAR2) IS
2767
2768 l_sql_stmt VARCHAR2(2000);
2769 l_dummy1 VARCHAR2(2000);
2770 l_dummy2 VARCHAR2(2000);
2771 l_schema VARCHAR2(400);
2772
2773 BEGIN
2774
2775 IF (p_mthd_action_id > -1) THEN
2776
2777 -- Set parameter globals
2778 set_parameters
2779 (p_mthd_action_id => p_mthd_action_id,
2780 p_mthd_stage_code => 'PRE_PROCESS');
2781
2782 END IF;
2783
2784 -- Get HRI schema name - get_app_info populates l_schema
2785 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2786 null;
2787 END IF;
2788
2789 -- ********************
2790 -- Full Refresh Section
2791 -- ********************
2792 IF (g_full_refresh = 'Y' OR
2793 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
2794
2795 -- Empty out absence dimension table
2796 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_REC_CAND_PIPLN_CT';
2797 EXECUTE IMMEDIATE(l_sql_stmt);
2798
2799 -- In shared HR mode do not return a SQL statement so that the
2800 -- process_range and post_process will not be executed
2801 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
2802
2803 -- Call post processing API
2804 post_process
2805 (p_mthd_action_id => p_mthd_action_id);
2806
2807 ELSE
2808
2809 -- Disable WHO trigger
2810 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_REC_CAND_PIPLN_CT_WHO DISABLE');
2811
2812 -- Drop all the indexes on the table
2813 hri_utl_ddl.log_and_drop_indexes
2814 (p_application_short_name => 'HRI',
2815 p_table_name => 'HRI_MB_REC_CAND_PIPLN_CT',
2816 p_table_owner => l_schema);
2817
2818 -- Set the SQL statement for the entire range
2819 p_sqlstr :=
2820 'SELECT DISTINCT
2821 asg.assignment_id object_id
2822 FROM
2823 per_all_assignments_f asg
2824 WHERE asg.assignment_type IN (''E'',''A'')
2825 AND asg.effective_end_date >=
2826 hri_bpl_parameter.get_bis_global_start_date
2827 ORDER BY 1';
2828
2829 END IF;
2830
2831 ELSE
2832
2833 -- Set the SQL statement for the incremental range
2834 p_sqlstr :=
2835 'SELECT DISTINCT
2836 assignment_id object_id
2837 FROM per_all_assignments_f
2838 WHERE 1 = 0
2839 ORDER BY 1';
2840
2841 END IF;
2842
2843 END pre_process;
2844
2845 -- ----------------------------------------------------------------------------
2846 -- Post process entry point
2847 -- ----------------------------------------------------------------------------
2848 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
2849
2850 l_sql_stmt VARCHAR2(2000);
2851 l_dummy1 VARCHAR2(2000);
2852 l_dummy2 VARCHAR2(2000);
2853 l_schema VARCHAR2(400);
2854
2855 BEGIN
2856
2857 IF (p_mthd_action_id > -1) THEN
2858
2859 -- Check parameters are set
2860 set_parameters
2861 (p_mthd_action_id => p_mthd_action_id,
2862 p_mthd_stage_code => 'POST_PROCESS');
2863
2864 -- Log process end
2865 hri_bpl_conc_log.record_process_start('HRI_MB_REC_CAND_PIPLN_CT');
2866 hri_bpl_conc_log.log_process_end(
2867 p_status => TRUE
2868 ,p_period_from => TRUNC(g_refresh_start_date)
2869 ,p_period_to => TRUNC(SYSDATE)
2870 ,p_attribute1 => g_full_refresh);
2871
2872 END IF;
2873
2874 -- Get HRI schema name - get_app_info populates l_schema
2875 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2876 null;
2877 END IF;
2878
2879 -- Recreate indexes in full refresh mode
2880 IF (g_full_refresh = 'Y') THEN
2881
2882 -- Add in vacancy records
2883 process_vacancies;
2884
2885 -- Enable WHO trigger
2886 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_REC_CAND_PIPLN_CT_WHO ENABLE');
2887
2888 hri_utl_ddl.recreate_indexes
2889 (p_application_short_name => 'HRI',
2890 p_table_name => 'HRI_MB_REC_CAND_PIPLN_CT',
2891 p_table_owner => l_schema);
2892
2893 END IF;
2894
2895 -- Empty out workforce manager summary event queue
2896 -- l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_EQ_WRKFC_EVT_MGRH';
2897 -- EXECUTE IMMEDIATE(l_sql_stmt);
2898
2899 END post_process;
2900
2901 -- Populates table in a single thread
2902 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
2903
2904 CURSOR chunk_csr IS
2905 SELECT
2906 chunk_no
2907 ,MIN(assignment_id) start_asg_id
2908 ,MAX(assignment_id) end_asg_id
2909 FROM
2910 (SELECT
2911 assignment_id
2912 ,CEIL(ROWNUM / 20) chunk_no
2913 FROM
2914 (SELECT DISTINCT
2915 assignment_id
2916 FROM per_all_assignments_f
2917 WHERE assignment_type IN ('E','A')
2918 AND effective_end_date >= g_dbi_start_date
2919 ORDER BY assignment_id
2920 ) tab
2921 ) chunks
2922 GROUP BY
2923 chunk_no;
2924
2925 l_dummy VARCHAR2(2000);
2926 l_errbuf VARCHAR2(1000);
2927 l_retcode VARCHAR2(1000);
2928
2929 BEGIN
2930
2931 g_full_refresh := p_full_refresh_flag;
2932 g_refresh_to_date := trunc(sysdate);
2933 g_sysdate := trunc(sysdate);
2934 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
2935 g_debug := FALSE;
2936
2937 pre_process(-1, l_dummy);
2938
2939 FOR chunk_rec IN chunk_csr LOOP
2940
2941 process_range
2942 (l_errbuf, l_retcode, -1, -1, chunk_rec.start_asg_id, chunk_rec.end_asg_id);
2943
2944 END LOOP;
2945
2946 post_process(-1);
2947
2948 END single_thread_process;
2949
2950 PROCEDURE process_assignment(p_asg_id IN NUMBER) IS
2951
2952 l_psn_id NUMBER;
2953 l_dummy VARCHAR2(2000);
2954
2955 BEGIN
2956
2957 g_full_refresh := 'Y';
2958 g_refresh_to_date := trunc(sysdate);
2959 g_sysdate := trunc(sysdate);
2960 g_dbi_start_date := hri_bpl_parameter.get_bis_global_start_date;
2961 g_no_rows := 0;
2962 g_debug := TRUE;
2963
2964 SELECT DISTINCT person_id INTO l_psn_id
2965 FROM per_all_assignments_f
2966 WHERE assignment_id = p_asg_id;
2967
2968 DELETE FROM hri_mb_rec_cand_pipln_ct
2969 WHERE adt_assignment_id = p_asg_id;
2970
2971 process_assignment(p_asg_id, l_psn_id);
2972
2973 bulk_insert_rows;
2974
2975 END process_assignment;
2976
2977 END hri_opl_rec_cand_pipln;