1 PACKAGE BODY hri_edw_fct_recruitment AS
2 /* $Header: hriefwrt.pkb 120.0 2005/05/29 07:10:44 appldev noship $ */
3
4 g_instance_fk VARCHAR2(40); -- Holds data source
5
6 /* Holds stage dates and reasons for each applicant */
7 g_application_date DATE;
8 g_interview1_date DATE;
9 g_interview2_date DATE;
10 g_offer_date DATE;
11 g_accept_date DATE;
12 g_separation_date DATE;
13
14 g_application_reason VARCHAR2(30);
15 g_interview1_reason VARCHAR2(30);
16 g_interview2_reason VARCHAR2(30);
17 g_offer_reason VARCHAR2(30);
18 g_accept_reason VARCHAR2(30);
19 g_separation_reason VARCHAR2(30);
20
21 g_application_success NUMBER;
22 g_interview1_success NUMBER;
23 g_interview2_success NUMBER;
24 g_offer_success NUMBER;
25 g_accept_success NUMBER;
26
27 g_success NUMBER;
28
29 /******************************************************************************/
30 /* This is a dummy function which calls the calc_abv function in the business */
31 /* process layer. It returns the assignment budget value of an applicant */
32 /* given their assignment, the vacancy they are applying for, the effective */
33 /* date the budget measurement type (BMT) and the applicant's business group */
34 /******************************************************************************/
35 FUNCTION calc_abv(p_assignment_id IN NUMBER,
36 p_business_group_id IN NUMBER,
37 p_budget_type IN VARCHAR2,
38 p_effective_date IN DATE)
39 RETURN NUMBER IS
40
41 l_return_value NUMBER := to_number(null); -- Keeps the ABV to be returned
42
43 BEGIN
44
45 l_return_value := hri_bpl_abv.calc_abv
46 ( p_assignment_id => p_assignment_id
47 , p_business_group_id => p_business_group_id
48 , p_budget_type => p_budget_type
49 , p_effective_date => p_effective_date );
50
51 RETURN l_return_value;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 RETURN to_number(null);
56
57 END calc_abv;
58
59 /******************************************************************************/
60 /* This function takes in the assignment and populates global variables with */
61 /* the dates of and reasons for stages. If a stages is not reached the global */
62 /* will be null, if a stage is reached more than once the first is returned. */
63 /******************************************************************************/
64 PROCEDURE find_stages (p_assignment_id IN NUMBER) IS
65
66 /* Selects the first date on which the system status passed in is */
67 /* set on the given assignment, and whether or not the stage was */
68 /* successful (based on whether another assignment record exists with */
69 /* a different status and whether the overall application was successful */
70 CURSOR assign_csr
71 (v_csr_system_status VARCHAR2) IS
72 SELECT asg.effective_start_date
73 ,asg.change_reason
74 ,DECODE(next_asg.assignment_id, to_number(null), g_success, 1)
75 FROM per_all_assignments_f asg
76 ,per_assignment_status_types ast
77 ,per_all_assignments_f next_asg
78 WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
79 AND asg.assignment_id = p_assignment_id
80 AND ast.per_system_status = v_csr_system_status
81 AND next_asg.assignment_id (+) = asg.assignment_id
82 AND next_asg.effective_start_date (+) > asg.effective_start_date
83 AND next_asg.assignment_status_type_id (+) <> asg.assignment_status_type_id
84 ORDER BY asg.effective_start_date ASC;
85
86 BEGIN
87
88 OPEN assign_csr('ACTIVE_APL');
89 FETCH assign_csr into g_application_date,
90 g_application_reason,
91 g_application_success;
92 CLOSE assign_csr;
93
94 IF (g_application_date IS NULL) THEN
95 /* Application successful, status changed immediately */
96 g_application_success := 1;
97 END IF;
98
99 OPEN assign_csr('INTERVIEW1');
100 FETCH assign_csr into g_interview1_date,
101 g_interview1_reason,
102 g_interview1_success;
103 CLOSE assign_csr;
104
105 OPEN assign_csr('INTERVIEW2');
106 FETCH assign_csr into g_interview2_date,
107 g_interview2_reason,
108 g_interview2_success;
109 CLOSE assign_csr;
110
111 OPEN assign_csr('OFFER');
112 FETCH assign_csr into g_offer_date,
113 g_offer_reason,
114 g_offer_success;
115 CLOSE assign_csr;
116
117 OPEN assign_csr('ACCEPTED');
118 FETCH assign_csr into g_accept_date,
119 g_accept_reason,
120 g_accept_success;
121 CLOSE assign_csr;
122
123 END find_stages;
124
125 /* Same as the procedure above, but for separation */
126 PROCEDURE find_employment_end (p_person_id NUMBER,
127 p_date_start DATE) IS
128
129 /* Cursor selecting the end date of the period of employment immediately */
130 /* following the application */
131 CURSOR end_emp_cur IS
132 SELECT pps.actual_termination_date, pps.leaving_reason
133 FROM per_periods_of_service pps
134 WHERE person_id = p_person_id
135 AND (p_date_start BETWEEN date_start
136 AND actual_termination_date
137 OR p_date_start > date_start
138 AND actual_termination_date IS NULL);
139
140 BEGIN
141
142 OPEN end_emp_cur;
143 FETCH end_emp_cur INTO g_separation_date, g_separation_reason;
144 CLOSE end_emp_cur;
145
146 END find_employment_end;
147
148
149 /* Returns the hire reason first looking at the applicant assignment */
150 /* and if that doesn't become an employee assignment then looking at */
151 /* the primary employee assignment */
152 FUNCTION find_hire_reason(p_assignment_id IN NUMBER,
153 p_person_id IN NUMBER,
154 p_hire_date IN DATE)
155 RETURN VARCHAR2 IS
156
157 l_reason VARCHAR2(400);
158
159 /* 115.3 Added hr_lookups to cursor below to filter out rogue reasons */
160 /* See bugs 1787981 and 1785779 */
161
162 /* Looking at the applicant assignment */
163 CURSOR smpl_hire_reason_cur IS
164 SELECT
165 asg.change_reason
166 FROM
167 per_all_assignments_f asg,
168 hr_lookups hrl
169 WHERE
170 asg.assignment_id = p_assignment_id
171 AND asg.change_reason = hrl.lookup_code
172 AND hrl.lookup_type = 'EMP_ASSIGN_REASON'
173 AND asg.effective_start_date = p_hire_date;
174
175 /* Looking at the primary employee assignment */
176 CURSOR prmry_hire_reason_cur IS
177 SELECT
178 asg.change_reason
179 FROM
180 per_all_assignments_f asg,
181 hr_lookups hrl
182 WHERE
183 asg.person_id = p_person_id
184 AND asg.effective_start_date = p_hire_date
185 AND asg.change_reason = hrl.lookup_code
186 AND hrl.lookup_type = 'EMP_ASSIGN_REASON'
187 AND asg.primary_flag = 'Y';
188
189 BEGIN
190
191 OPEN smpl_hire_reason_cur;
192 FETCH smpl_hire_reason_cur INTO l_reason;
193 IF (smpl_hire_reason_cur%NOTFOUND
194 OR smpl_hire_reason_cur%NOTFOUND IS NULL) THEN
195 /* If that didn't return any rows check the primary assignment */
196 CLOSE smpl_hire_reason_cur;
197 OPEN prmry_hire_reason_cur;
198 FETCH prmry_hire_reason_cur INTO l_reason;
199 CLOSE prmry_hire_reason_cur;
200 ELSE
201 CLOSE smpl_hire_reason_cur;
202 END IF;
203
204 RETURN l_reason;
205
206 END find_hire_reason;
207
208
209 /******************************************************************************/
210 /* Takes the stage, the gain type and whether the stage was successful and */
211 /* returns the movement pk for that event */
212 /******************************************************************************/
213 FUNCTION find_movement_pk(p_system_status IN VARCHAR2,
214 p_gain_type IN VARCHAR2,
215 p_success_flag IN NUMBER)
216 RETURN VARCHAR2 IS
217
218 l_return_string VARCHAR2(800); -- Keeps the string to return
219 l_stage VARCHAR2(30); -- Holds current stage
220
221 /* Movement Type PK is made by concatenating the following primary keys */
222 l_gain_type_pk VARCHAR2(400); -- Holds gain type pk
223 l_loss_type_pk VARCHAR2(400); -- Holds loss type pk
224 l_recruitment_pk VARCHAR2(400); -- Holds recruitment stage pk
225 l_separation_pk VARCHAR2(400); -- Holds separation stage pk
226 l_na_edw_pk VARCHAR2(140); -- Points to N/A row
227
228 BEGIN
229
230 l_na_edw_pk := 'NA_EDW-' || g_instance_fk || '-NA_EDW-' ||
231 g_instance_fk || '-NA_EDW-' || g_instance_fk;
232
233 l_separation_pk := l_na_edw_pk;
234
235 /* Populate gain and loss type pks */
236 /***********************************/
237
238 /* Break out all the definite gains and losses */
239 IF (p_success_flag = 1 AND
240 (p_system_status = 'ACCEPTED' OR p_system_status = 'HIRE')) THEN
241 /* A successful applicant at the HIRE or ACCEPTED stage is a */
242 /* gain (actual) - break out by gain type to get the gain pk */
243 IF p_gain_type = 'NEW_HIRE' THEN
244 l_loss_type_pk := l_na_edw_pk;
245 l_gain_type_pk := 'GAINS-' || g_instance_fk || '-GAIN_HIRE-' ||
246 g_instance_fk || '-HIRE_NEW-' || g_instance_fk;
247 ELSIF p_gain_type = 'RE_HIRE' THEN
248 l_loss_type_pk := l_na_edw_pk;
249 l_gain_type_pk := 'GAINS-' || g_instance_fk || '-GAIN_HIRE-' ||
250 g_instance_fk || '-HIRE_RE-' || g_instance_fk;
251 ELSIF p_gain_type = 'ASG_START' THEN
252 l_loss_type_pk := l_na_edw_pk;
253 l_gain_type_pk := 'GAINS-' || g_instance_fk || '-GAIN_ASG-' ||
254 g_instance_fk || '-GAIN_ASG-' || g_instance_fk;
255 ELSE
256 l_loss_type_pk := 'LOSSES-' || g_instance_fk ||'-LOSS_ORG-' ||
257 g_instance_fk || '-LOSS_ORG-' || g_instance_fk;
258 l_gain_type_pk := 'GAINS-' || g_instance_fk || '-GAIN_ORG-' ||
259 g_instance_fk || '-GAIN_ORG-' || g_instance_fk;
260 END IF;
261 ELSIF (p_success_flag = 1 AND p_system_status = 'END_EMP') THEN
262 /* Formulate loss type pk - using push down of separation until a method */
263 /* has been agreed for resolving the difference with involuntary */
264 l_loss_type_pk := 'LOSSES-' || g_instance_fk || '-LOSS_SEP-' ||
265 g_instance_fk || '-LOSS_SEP-' || g_instance_fk;
266 l_separation_pk := 'SEP_STAGE-' || g_instance_fk || '-SEP-' ||
267 g_instance_fk || '-SEP-' || g_instance_fk;
268 l_gain_type_pk := l_na_edw_pk;
269 ELSIF (p_success_flag = -1) THEN
270 l_loss_type_pk := l_na_edw_pk;
271 l_gain_type_pk := l_na_edw_pk;
272 ELSE
273 /* Application stage still pending, so formulate the Potential Gain PK */
274 IF p_gain_type = 'NEW_HIRE' THEN
275 l_loss_type_pk := l_na_edw_pk;
276 l_gain_type_pk := 'POT_GAINS-' || g_instance_fk || '-POT_HIRE-' ||
277 g_instance_fk || '-HIRE_NEW-' || g_instance_fk;
278 ELSIF p_gain_type = 'RE_HIRE' THEN
279 l_loss_type_pk := l_na_edw_pk;
280 l_gain_type_pk := 'POT_GAINS-' || g_instance_fk || '-POT_HIRE-' ||
281 g_instance_fk || '-HIRE_RE-' || g_instance_fk;
282 ELSIF p_gain_type = 'ASG_START' THEN
283 l_loss_type_pk := l_na_edw_pk;
284 l_gain_type_pk := 'POT_GAINS-' || g_instance_fk || '-POT_ASG-' ||
285 g_instance_fk || '-POT_ASG-' || g_instance_fk;
286 ELSE
287 l_loss_type_pk := 'POT_LOSSES-' || g_instance_fk ||'-POT_ORG-' ||
288 g_instance_fk || '-POT_ORG-' || g_instance_fk;
289 l_gain_type_pk := 'POT_GAINS-' || g_instance_fk || '-POT_ORG-' ||
290 g_instance_fk || '-POT_ORG-' || g_instance_fk;
291 END IF;
292 END IF;
293
294
295 /* Populate recruitment pk */
296 /***************************/
297
298 IF (p_system_status = 'HIRE') THEN
299 l_recruitment_pk := 'REC_STAGE-' || g_instance_fk || '-END-' ||
300 g_instance_fk || '-END_SCCSS-' || g_instance_fk;
301 ELSIF (p_system_status = 'TERM_APL') THEN
302 l_recruitment_pk := 'REC_STAGE-' || g_instance_fk || '-END-' ||
303 g_instance_fk || '-END_FAIL-' || g_instance_fk;
304 ELSIF (p_system_status = 'END_EMP') THEN
305 l_recruitment_pk := l_na_edw_pk;
306 ELSE
307 l_stage := p_system_status;
308 l_recruitment_pk := 'REC_STAGE-' || g_instance_fk || '-' ||
309 l_stage || '-' || g_instance_fk;
310 IF (p_success_flag = 1) THEN
311 l_stage := l_stage || '_ACC';
312 ELSIF (p_success_flag = 0) THEN
313 l_stage := l_stage || '_PEND';
314 ELSE
315 l_stage := l_stage || '_REJ';
316 END IF;
317 l_recruitment_pk := l_recruitment_pk || '-' || l_stage
318 || '-' || g_instance_fk;
319 END IF;
320
321
322 /* Compose dimension fk */
323 /************************/
324 /* Concatenate pks into movement type pk */
325 l_return_string := l_gain_type_pk || '-' || l_loss_type_pk || '-' ||
326 l_recruitment_pk || '-' || l_separation_pk || '-' || g_instance_fk;
327
328 RETURN l_return_string;
329
330 END find_movement_pk;
331
332
333 /***************************************************************************/
334 /* This function returns 1 if the applicant was successful and 0 otherwise */
335 /* It is assumed that there is no termination reason given for the */
336 /* application end and that the applicant is successful if either the */
337 /* applicant assignment becomes an employee assignment, or if the existing */
338 /* primary (employee) assignment of the applicant changes on the day after */
339 /* the application assignment ends. */
340 /***************************************************************************/
341 FUNCTION is_successful(p_assignment_id IN NUMBER,
342 p_person_id IN NUMBER,
343 p_date_end IN DATE)
344 RETURN NUMBER IS
345
346 l_result NUMBER; -- Whether the applicant was successful
347
348 /* Cursor returns 1 if the applicant assignment becomes an employee */
349 /* assignment */
350 CURSOR successful_asg_csr IS
351 SELECT 1
352 FROM per_all_assignments_f
353 WHERE assignment_id = p_assignment_id
354 AND effective_start_date > p_date_end
355 AND assignment_type = 'E';
356
357 /* Cursor returns 1 if there is a date-tracked change to the primary */
358 /* assignment on the day after the application terminated */
359 CURSOR primary_success_csr IS
360 SELECT 1
361 FROM per_all_assignments_f prim
362 WHERE prim.person_id = p_person_id
363 AND prim.primary_flag = 'Y'
364 AND prim.effective_start_date = (p_date_end + 1);
365
366 BEGIN
367
368 IF p_date_end IS NULL THEN
369 /* Application active still */
370 RETURN 0;
371 END IF;
372
373 OPEN successful_asg_csr;
374 FETCH successful_asg_csr INTO l_result;
375
376 IF successful_asg_csr%FOUND THEN
377 /* Applicant assignment becomes an employee assignment - success */
378 CLOSE successful_asg_csr;
379 RETURN 1;
380 ELSE
381 CLOSE successful_asg_csr;
382 OPEN primary_success_csr;
383 FETCH primary_success_csr INTO l_result;
384
385 IF primary_success_csr%FOUND THEN
386 /* Primary (employee) assignment changes - success */
387 CLOSE primary_success_csr;
388 RETURN 1;
389 /* Otherwise unsuccessful */
390 ELSE
391 CLOSE primary_success_csr;
392 RETURN -1;
393 END IF;
394
395 END IF;
396
397 RETURN -1;
398
399 END is_successful;
400
401
402 /* To improve performance on collecting recruitment fact the following */
403 /* procedure populates a temporary table with information about each */
404 /* assignment and its recruitment stages */
405 PROCEDURE populate_recruitment_table IS
406
407 /* Selects the first assignment record for each assignment */
408 CURSOR initial_assignment_cursor IS
409 SELECT asg.assignment_id assignment_id
410 ,asg.person_id person_id
411 ,asg.business_group_id business_group_id
412 ,asg.assignment_type assignment_type
413 ,asg.application_id application_id
414 ,asg.effective_start_date assignment_start
415 ,apl.date_end application_end
416 ,apl.projected_hire_date planned_start_date
417 ,apl.termination_reason termination_reason
418 ,decode(prev_pps.date_start,
419 /* If a previous period of service doesn't exist then applicant is new hire */
420 to_date(NULL),'NEW_HIRE',
421 decode(SIGN(prev_pps.actual_termination_date - asg.effective_start_date),
422 /* If the latest previous period of service has a past actual termination */
423 /* date then the applicant was an ex_employee */
424 -1,'RE_HIRE',
425 decode(emp_asg.organization_id,
426 /* If the applicant is an employee and is applying within the */
427 /* same organization then assignment start else org transfer */
428 asg.organization_id,'ASG_START',
429 'ORG_TRANS')))
430 gain_type
431 ,decode(apl.application_id,
432 /* If person is an employee type they were successful */
433 to_number(null),1,
434 decode(apl.date_end,
435 /* If the application has not ended it is neither successful nor unsuccessful */
436 to_date(null),0,
437 decode(apl.termination_reason,
438 /* If there is a termination reason it is not successful */
439 null,hri_edw_fct_recruitment.is_successful(
440 asg.assignment_id,
441 apl.person_id,
442 apl.date_end),
443 -1))) success_flag
444 ,GREATEST(
445 NVL(asg.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
446 NVL(apl.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
447 last_update_date
448 ,asg.creation_date creation_date
449 FROM per_all_assignments_f asg -- Initial assignment record
450 ,per_applications apl -- Application for assignment
451 ,per_all_assignments_f emp_asg -- Pre-existing employee assignment
452 ,per_periods_of_service prev_pps -- Previously ended period of service
453 WHERE asg.assignment_type IN ('E','A')
454 AND apl.application_id (+) = asg.application_id
455 AND asg.person_id = emp_asg.person_id (+)
456 AND emp_asg.primary_flag (+) = 'Y'
457 /* 115.3 Added following line to filter out benefits assignments */
458 AND emp_asg.assignment_type (+) = 'E'
459 AND asg.effective_start_date - 1
460 BETWEEN emp_asg.effective_start_date (+) AND emp_asg.effective_end_date (+)
461 /* 115.6 If an employee assignment exists then only include applicant assignments */
462 AND (emp_asg.assignment_id IS NULL
463 OR (emp_asg.assignment_id IS NOT NULL AND asg.assignment_type = 'A'))
464 AND asg.person_id = prev_pps.person_id (+)
465 AND prev_pps.date_start (+) < asg.effective_start_date
466 /* If a previous period of service exists, restrict to the most recent */
467 AND NOT EXISTS
468 (SELECT 1
469 FROM per_periods_of_service dummy
470 WHERE dummy.person_id = apl.person_id
471 AND dummy.date_start > prev_pps.date_start)
472 /* Filter out all but first assignment */
473 AND asg.effective_start_date = (SELECT MIN(asg1.effective_start_date)
474 FROM per_all_assignments_f asg1
475 WHERE asg1.assignment_id = asg.assignment_id);
476
477 l_head NUMBER;
478 l_fte NUMBER;
479 l_hire_date DATE;
480 l_hire_reason VARCHAR2(30);
481 l_termination_date DATE;
482 l_termination_reason VARCHAR2(30);
483
484 BEGIN
485
486 DELETE FROM hri_recruitment_stages;
487
488 FOR new_asg_rec IN initial_assignment_cursor LOOP
489
490 g_application_date := TO_DATE(null);
491 g_interview1_date := TO_DATE(null);
492 g_interview2_date := TO_DATE(null);
493 g_offer_date := TO_DATE(null);
494 g_accept_date := TO_DATE(null);
495 g_separation_date := TO_DATE(null);
496
497 g_application_reason := null;
498 g_interview1_reason := null;
499 g_interview2_reason := null;
500 g_offer_reason := null;
501 g_accept_reason := null;
502 g_separation_reason := null;
503
504 g_application_success := null;
505 g_interview1_success := null;
506 g_interview2_success := null;
507 g_offer_success := null;
508 g_accept_success := null;
509
510 g_success := new_asg_rec.success_flag;
511
512 IF (new_asg_rec.application_id IS NOT NULL) THEN
513 find_stages(new_asg_rec.assignment_id);
514 END IF;
515
516 IF (new_asg_rec.success_flag = 1) THEN
517 l_hire_date := NVL(new_asg_rec.application_end+1,new_asg_rec.assignment_start);
518 l_hire_reason := find_hire_reason
519 ( new_asg_rec.assignment_id
520 , new_asg_rec.person_id
521 , NVL(new_asg_rec.application_end+1, new_asg_rec.assignment_start));
522 ELSE
523 l_hire_date := TO_DATE(null);
524 l_hire_reason := null;
525 END IF;
526
527 IF (new_asg_rec.success_flag = 1) THEN
528 find_employment_end
529 ( new_asg_rec.person_id
530 , NVL(new_asg_rec.application_end+1, new_asg_rec.assignment_start));
531 END IF;
532
533 IF (new_asg_rec.success_flag = -1) THEN
534 l_termination_reason := new_asg_rec.termination_reason;
535 l_termination_date := new_asg_rec.application_end;
536 ELSE
537 l_termination_reason := null;
538 l_termination_date := TO_DATE(null);
539 END IF;
540
541 -- l_head := calc_abv
542 -- ( new_asg_rec.assignment_id
543 -- , new_asg_rec.business_group_id
544 -- , 'HEAD'
545 -- , new_asg_rec.assignment_start );
546
547 -- l_fte := calc_abv
548 -- ( new_asg_rec.assignment_id
549 -- , new_asg_rec.business_group_id
550 -- , 'FTE'
551 -- , new_asg_rec.assignment_start );
552
553
554 INSERT INTO hri_recruitment_stages
555 ( assignment_id
556 , assignment_start_date
557 , business_group_id
558 , assignment_type
559 , application_id
560 , person_id
561 , gain_type
562 , success
563 , application_date
564 , application_end_date
565 , planned_start_date
566 , application_reason
567 , application_success
568 , interview1_date
569 , interview1_reason
570 , interview1_success
571 , interview2_date
572 , interview2_reason
573 , interview2_success
574 , offer_date
575 , offer_reason
576 , offer_success
577 , accept_date
578 , accept_reason
579 , accept_success
580 , hire_date
581 , hire_reason
582 , termination_date
583 , termination_reason
584 , separation_date
585 , separation_reason
586 , last_update_date
587 , creation_date )
588 VALUES
589 ( new_asg_rec.assignment_id
590 , new_asg_rec.assignment_start
591 , new_asg_rec.business_group_id
592 , new_asg_rec.assignment_type
593 , new_asg_rec.application_id
594 , new_asg_rec.person_id
595 , new_asg_rec.gain_type
596 , new_asg_rec.success_flag
597 , g_application_date
598 , new_asg_rec.application_end
599 , new_asg_rec.planned_start_date
600 , g_application_reason
601 , g_application_success
602 , g_interview1_date
603 , g_interview1_reason
604 , g_interview1_success
605 , g_interview2_date
606 , g_interview2_reason
607 , g_interview2_success
608 , g_offer_date
609 , g_offer_reason
610 , g_offer_success
611 , g_accept_date
612 , g_accept_reason
613 , g_accept_success
614 , l_hire_date
615 , l_hire_reason
616 , l_termination_date
617 , l_termination_reason
618 , g_separation_date
619 , g_separation_reason
620 , new_asg_rec.last_update_date
621 , new_asg_rec.creation_date );
622
623 END LOOP;
624
625 END populate_recruitment_table;
626
627 BEGIN
628
629 SELECT instance_code INTO g_instance_fk
630 FROM edw_local_instance;
631
632 END hri_edw_fct_recruitment;