1 PACKAGE BODY HR_SUIT_MATCH_UTIL_SS AS
2 /* $Header: hrsmgutl.pkb 120.6 2011/02/09 12:49:50 kgowripe ship $ */
3 --- New global variable added by KRISHNA for perf. fix 7452233
4 g_prev_person_id NUMBER(15);
5 g_prev_ess_count NUMBER;
6 g_prev_des_count NUMBER;
7 g_ess_total_count NUMBER;
8 g_des_total_count NUMBER;
9 -- END Changes by KMG
10 --
11 -- declare global cursor for total essential/desired count
12 CURSOR csr_total_ess_des_count(p_mandatory in varchar2) IS
13 SELECT count(competence_id)
14 FROM per_suitmatch_comp smtmp
15 where smtmp.mandatory = (p_mandatory);
16
17 -- declare global cursor for essential/desired match count
18 CURSOR csr_ess_des_match_count(p_person_id in number, p_mandatory in varchar2)IS
19 select /*+ leading(smtmp) index(pce, PER_COMPETENCE_ELEMENTS_FK7) index(r1, PER_RATING_LEVELS_PK) */
20 count(pce.competence_id)
21 FROM per_competence_elements pce,
22 per_suitmatch_comp smtmp,
23 per_rating_levels r1
24 where pce.competence_id = smtmp.competence_id
25 AND nvl(r1.step_value,-1) >= nvl(smtmp.min_step_value, -1)
26 AND pce.type = 'PERSONAL'
27 AND trunc(sysdate) BETWEEN pce.effective_date_from AND
28 NVL(pce.effective_date_to, trunc(sysdate))
29 AND pce.proficiency_level_id = r1.rating_level_id(+)
30 AND pce.person_id = p_person_id
31 AND smtmp.mandatory = p_mandatory;
32
33 -- declare global cursor for essential/desired match count for a work opp
34 -- with competencies for person and work opp coming from base table
35 CURSOR csr_workopp_ed_match_count(
36 p_person_id in number
37 ,p_enterprise_id in number
38 ,p_organization_id in number
39 ,p_job_id in number
40 ,p_position_id in number
41 ,p_mandatory in varchar2) IS
42 SELECT count(pcep.competence_id)
43 FROM per_competence_elements pce,
44 per_competence_elements pcep,
45 per_rating_levels r1,
46 per_rating_levels r2
47 WHERE pce.type = 'REQUIREMENT'
48 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
49 and nvl(pce.effective_date_to, trunc(sysdate))
50 AND pce.proficiency_level_id = r1.rating_level_id(+)
51 AND (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
52 pce.job_id = p_job_id or pce.position_id = p_position_id)
53 AND pcep.person_id = p_person_id
54 AND pce.competence_id = pcep.competence_id
55 AND pcep.type = 'PERSONAL'
56 AND trunc(sysdate) BETWEEN pcep.effective_date_from AND
57 NVL(pcep.effective_date_to, trunc(sysdate))
58 AND pcep.proficiency_level_id = r2.rating_level_id(+)
59 AND nvl(r2.step_value, -1) >= nvl(r1.step_value, -1)
60 AND pce.mandatory = p_mandatory;
61
62
63 -- declare global cursor for total essential/desired match count for a work opp
64 -- with competencies work opp coming from base table
65 CURSOR csr_total_workopp_match_count(
66 p_enterprise_id in number
67 ,p_organization_id in number
68 ,p_job_id in number
69 ,p_position_id in number
70 ,p_mandatory in varchar2) IS
71 SELECT count(pce.competence_id)
72 FROM per_competence_elements pce
73 WHERE pce.type = 'REQUIREMENT'
74 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
75 and nvl(pce.effective_date_to, trunc(sysdate))
76 AND (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
77 pce.job_id = p_job_id or pce.position_id = p_position_id)
78 AND pce.mandatory = p_mandatory;
79
80
81 -- declare global cursor for essential/desired match count for a work opp
82 -- with competencies for person coming from base table and competencies for
83 -- work opp coming from temp table
84 CURSOR csr_wp_tmp_ed_match_count(
85 p_person_id in number
86 ,p_mandatory in varchar2) IS
87 SELECT /*+ leading(smtmp) index(pcep, PER_COMPETENCE_ELEMENTS_FK7) index(r2, PER_RATING_LEVELS_PK) */
88 count(pcep.competence_id)
89 FROM per_suitmatch_comp smtmp,
90 per_competence_elements pcep,
91 per_rating_levels r2
92 WHERE pcep.person_id = p_person_id
93 AND smtmp.competence_id = pcep.competence_id
94 AND pcep.type = 'PERSONAL'
95 AND trunc(sysdate) BETWEEN pcep.effective_date_from AND
96 NVL(pcep.effective_date_to, trunc(sysdate))
97 AND pcep.proficiency_level_id = r2.rating_level_id(+)
98 AND nvl(r2.step_value, -1) >= nvl(smtmp.min_step_value, -1)
99 AND smtmp.mandatory = p_mandatory;
100
101
102 -- declare global cursor for essential/desired match count for a work opp
103 -- with competencies for person coming from temp table and competencies for
104 -- work opp coming from base table
105 CURSOR csr_wp_per_tmp_ed_match_count(
106 p_enterprise_id in number
107 ,p_organization_id in number
108 ,p_job_id in number
109 ,p_position_id in number
110 ,p_mandatory in varchar2) IS
111 SELECT /*+ leading(ptmp) index(pce, PER_COMPETENCE_ELEMENTS_N1) index(r1, PER_RATING_LEVELS_PK) */
112 count(ptmp.competence_id)
113 FROM per_competence_elements pce,
114 per_suitmatch_person ptmp,
115 per_rating_levels r1
116 WHERE pce.type = 'REQUIREMENT'
117 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
118 and nvl(pce.effective_date_to, trunc(sysdate))
119 AND pce.proficiency_level_id = r1.rating_level_id(+)
120 AND (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
121 pce.job_id = p_job_id or pce.position_id = p_position_id)
122 AND pce.competence_id = ptmp.competence_id
123 AND nvl(ptmp.min_step_value, -1) >= nvl(r1.step_value, -1)
124 AND pce.mandatory = p_mandatory;
125
126
127 -- declare global cursor for essential/desired match count for a work opp
128 -- with competencies for person and work opp coming from temp table
129 CURSOR csr_tmp_ed_match_count(
130 p_mandatory in varchar2) IS
131 SELECT count(ptmp.competence_id)
132 FROM per_suitmatch_comp smtmp,
133 per_suitmatch_person ptmp
134 WHERE smtmp.competence_id = ptmp.competence_id
135 AND nvl(ptmp.min_step_value, -1) >= nvl(smtmp.min_step_value, -1)
136 AND smtmp.mandatory = p_mandatory;
137
138
139 --cursor to get position dets
140 CURSOR csr_position_dets(p_position_id in number) IS
141 SELECT hpf.position_id,
142 hpf.organization_id,
143 hpf.job_id
144 FROM hr_all_positions_f hpf
145 WHERE TRUNC(sysdate) BETWEEN hpf.effective_start_date
146 AND hpf.effective_end_date
147 AND TRUNC(sysdate) BETWEEN hpf.date_effective
148 AND NVL(hpf.date_end, TRUNC(sysdate))
149 AND (hpf.status is null OR hpf.status <> 'INVALID')
150 AND hpf.position_id = p_position_id;
151
152 -- cursor to get vacancy dets
153 CURSOR csr_vacancy_dets(p_vacancy_id in number) IS
154 SELECT pv.vacancy_id,
155 pv.organization_id,
156 pv.job_id,
157 pv.position_id
158 FROM per_all_vacancies pv
159 WHERE TRUNC(sysdate) BETWEEN pv.date_from
160 AND NVL(pv.date_to, TRUNC(sysdate))
161 AND pv.vacancy_id = p_vacancy_id;
162
163 FUNCTION getTableSchema RETURN VARCHAR2 IS
164 l_status VARCHAR2(100) := '';
165 l_industry VARCHAR2(100) := '';
166 l_result BOOLEAN;
167 l_schema_owner VARCHAR2(10) := '';
168 BEGIN
169 l_result := FND_INSTALLATION.GET_APP_INFO(
170 'PER',
171 l_status,
172 l_industry,
173 l_schema_owner);
174
175 IF l_result THEN
176 RETURN l_schema_owner;
177 ELSE
178 RETURN 'HR';
179 END IF;
180 END getTableSchema;
181
182
183 /**
184 * applyOverridingRules applies the overriding rules.
185 * Overriding rules are applied only for position and vacancy
186 * and the logic is ...
187 * For each competence required by the position only then
188 * use the low, high and matching level from the position requirements.
189 *
190 * For each competence required by the position's organization or position's
191 * job that is not explicitly required by the position then
192 *
193 * 1. if the competence is required by job only, then
194 * use the low, high and matching level from the job requirements.
195 * 2. if the competence is required by org only, then
196 * use the low, high and matching level from the org requirements.
197 * 3. if the competence is essential for org and job then
198 * use the low, high and matching level from the org requirements.
199 * 4. if the competence is desirable for org and job, then
200 * use greatest (org low, job low), least (org high, job high) and
201 * matching level.
202 * 5. if the competence is essential for org and desirable for job or
203 * essential for job and desirable for org, then 2 requirements are used.
204 * One from the org requirements and one from job requirements.
205 * 6. Competence at lower level always overrides BG level.
206 *
207 */
208 procedure apply_overridding_rules(
209 p_enterprise_id in number
210 ,p_organization_id in number
211 ,p_job_id in number
212 ,p_position_id in number
213 )
214 IS
215
216 -- cursor to get cummulative competency requirements
217 CURSOR csr_workopp_comp_reqs(
218 p_enterprise_id in number
219 ,p_organization_id in number
220 ,p_job_id in number
221 ,p_position_id in number) IS
222 SELECT pce.competence_id,
223 r1.step_value low_step_value,
224 r2.step_value high_step_value,
225 pce.mandatory,
226 'BUS' lookup_code
227 FROM per_competence_elements pce,
228 per_rating_levels r1,
229 per_rating_levels r2
230 WHERE pce.type = 'REQUIREMENT'
231 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
232 and nvl(pce.effective_date_to, trunc(sysdate))
233 AND pce.proficiency_level_id = r1.rating_level_id(+)
234 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
235 AND pce.enterprise_id = p_enterprise_id
236 UNION ALL
237 SELECT pce.competence_id,
238 r1.step_value low_step_value,
239 r2.step_value high_step_value,
240 pce.mandatory,
241 'ORG' lookup_code
242 FROM per_competence_elements pce,
243 per_rating_levels r1,
244 per_rating_levels r2
245 WHERE pce.type = 'REQUIREMENT'
246 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
247 and nvl(pce.effective_date_to, trunc(sysdate))
248 AND pce.proficiency_level_id = r1.rating_level_id(+)
249 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
250 AND pce.organization_id = p_organization_id
251 UNION ALL
252 SELECT pce.competence_id,
253 r1.step_value low_step_value,
254 r2.step_value high_step_value,
255 pce.mandatory,
256 'JOB' lookup_code
257 FROM per_competence_elements pce,
258 per_rating_levels r1,
259 per_rating_levels r2
260 WHERE pce.type = 'REQUIREMENT'
261 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
262 and nvl(pce.effective_date_to, trunc(sysdate))
263 AND pce.proficiency_level_id = r1.rating_level_id(+)
264 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
265 AND pce.job_id = p_job_id
266 UNION ALL
267 SELECT pce.competence_id,
268 r1.step_value low_step_value,
269 r2.step_value high_step_value,
270 pce.mandatory,
271 'POS' lookup_code
272 FROM per_competence_elements pce,
273 per_rating_levels r1,
274 per_rating_levels r2
275 WHERE pce.type = 'REQUIREMENT'
276 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
277 and nvl(pce.effective_date_to, trunc(sysdate))
278 AND pce.proficiency_level_id = r1.rating_level_id(+)
279 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
280 AND pce.position_id = p_position_id;
281
282 isSameCompetence boolean default false;
283 isSameStructureType boolean default false;
284 isIgnore boolean default false;
285 isBEssential boolean default false;
286 isBDesired boolean default false;
287
288 TYPE sel_comp_tab IS TABLE OF csr_workopp_comp_reqs%ROWTYPE INDEX BY BINARY_INTEGER ;
289
290 l_sel_comp_table sel_comp_tab;
291 l_mat_comp_table sel_comp_tab;
292 I integer default 0;
293
294 BEGIN
295
296 OPEN csr_workopp_comp_reqs(p_enterprise_id, p_organization_id, p_job_id, p_position_id);
297 LOOP
298 I := I + 1;
299 FETCH csr_workopp_comp_reqs into l_sel_comp_table(I);
300 EXIT WHEN csr_workopp_comp_reqs%NOTFOUND;
301 END LOOP;
302 CLOSE csr_workopp_comp_reqs; -- close cursor variable
303
304 l_mat_comp_table := l_sel_comp_table;
305
306 -- execute the cursor and apply the overriding rules
307 FOR J IN 1 ..l_sel_comp_table.count LOOP
308 FOR K IN 1 ..l_mat_comp_table.count LOOP
309 BEGIN
310 isSameCompetence := (l_sel_comp_table(J).competence_id = l_mat_comp_table(K).competence_id);
311 isSameStructureType := (l_sel_comp_table(J).lookup_code = l_mat_comp_table(K).lookup_code);
312 isIgnore := ('I' = l_mat_comp_table(K).mandatory);
313
314 if(NOT isIgnore and isSameCompetence and NOT isSameStructureType)
315 then
316 if(l_sel_comp_table(J).mandatory <> 'I')
317 then
318 isBEssential := ('Y' = l_mat_comp_table(K).mandatory and
319 'Y' = l_sel_comp_table(J).mandatory);
320 isBDesired := ('N' = l_mat_comp_table(K).mandatory and
321 'N' = l_sel_comp_table(J).mandatory);
322 --if competence is required for position only
323 if('POS' = l_sel_comp_table(J).lookup_code) then
324 l_mat_comp_table(K).mandatory := 'I';
325 end if;
326 --if the competence is essential for org and job then
327 --use the low, high and matching level from the org requirements.
328 if(isBEssential and 'ORG' = l_sel_comp_table(J).lookup_code and
329 'JOB' = l_mat_comp_table(K).lookup_code)
330 then
331 l_mat_comp_table(K).mandatory := 'I';
332 end if;
333 --if the competence is desirable for org and job, then
334 --use greatest (org low, job low), least (org high, job high) and
335 --matching level.
336 if(isBDesired and ('ORG' = l_sel_comp_table(J).lookup_code and
337 'JOB' = l_mat_comp_table(K).lookup_code))
338 then
339 if(l_mat_comp_table(K).low_step_value > l_sel_comp_table(J).low_step_value) then
340 l_sel_comp_table(J).low_step_value := l_mat_comp_table(K).low_step_value;
341 l_mat_comp_table(K).mandatory := 'I';
342 end if;
343 if(l_mat_comp_table(K).high_step_value < l_sel_comp_table(J).high_step_value) then
344 l_sel_comp_table(J).high_step_value := l_mat_comp_table(K).high_step_value;
345 l_mat_comp_table(K).mandatory := 'I';
346 end if;
347 end if;
348 if(isBDesired and ('JOB' = l_sel_comp_table(J).lookup_code and
349 'ORG' = l_mat_comp_table(K).lookup_code))
350 then
351 if(l_mat_comp_table(K).low_step_value > l_sel_comp_table(J).low_step_value) then
352 l_sel_comp_table(J).low_step_value := l_mat_comp_table(K).low_step_value;
353 l_mat_comp_table(K).mandatory := 'I';
354 end if;
355 if(l_mat_comp_table(K).high_step_value < l_sel_comp_table(J).high_step_value) then
356 l_sel_comp_table(J).high_step_value := l_mat_comp_table(K).high_step_value;
357 l_mat_comp_table(K).mandatory := 'I';
358 end if;
359 end if;
360 --competence at lower level always overrides BG level
361 if(l_sel_comp_table(J).lookup_code <> 'BUS' and
362 ('BUS' = l_mat_comp_table(K).lookup_code))
363 then
364 l_mat_comp_table(K).mandatory := 'I';
365 end if;
366 --if the competence is essential for org and desirable for job or
367 --essential for job and desirable for org, then 2 requirements are used..
368 --one from the org requirements and one from job requirements.
369 end if;
370 end if;
371 END;
372 END LOOP;
373 END LOOP;
374
375 -- Now insert the competencies into temp table.
376 DELETE FROM per_suitmatch_comp;
377 FOR L IN 1 ..l_mat_comp_table.count LOOP
378 IF (l_mat_comp_table(L).mandatory <> 'I') THEN
379 insert into per_suitmatch_comp (competence_id,
380 mandatory, min_step_value)
381 values (to_number(l_mat_comp_table(L).competence_id)
382 ,l_mat_comp_table(L).mandatory
383 ,to_number(l_mat_comp_table(L).low_step_value));
384 END IF;
385 END LOOP;
386 commit;
387
388 END apply_overridding_rules;
389
390 FUNCTION compare_counts(
391 p_ess_match_count in integer
392 ,p_total_ess_count in integer
393 ,p_des_match_count in integer
394 ,p_total_des_count in integer
395 )
396 RETURN VARCHAR2
397 IS
398 BEGIN
399
400 IF ((p_total_ess_count <> 0 AND p_total_des_count <> 0) AND
401 ((p_ess_match_count = p_total_ess_count ) AND
402 (p_des_match_count = p_total_des_count ))) THEN
403 RETURN 'ALL';
404 END IF;
405
406 IF ((p_total_ess_count <> 0 AND (p_ess_match_count = p_total_ess_count))
407 AND (p_total_des_count = 0 OR (p_des_match_count <> p_total_des_count))) THEN
408 RETURN 'ESS';
409 END IF;
410
411 IF ((p_total_des_count <> 0 AND (p_des_match_count = p_total_des_count))
412 AND (p_total_ess_count = 0 OR (p_ess_match_count <> p_total_ess_count))) THEN
413 RETURN 'DES';
414 END IF;
415
416 IF (((p_total_ess_count = 0 OR
417 (p_ess_match_count <> p_total_ess_count)) AND
418 (p_total_des_count = 0 OR
419 (p_des_match_count <> p_total_des_count )))OR
420 (p_total_ess_count = 0 AND p_total_des_count = 0)) THEN
421 RETURN 'ED';
422 END IF;
423
424 END compare_counts;
425
426
427 FUNCTION get_workopp_ed_match_count (
428 p_person_id in number
429 ,p_enterprise_id in number
430 ,p_organization_id in number
431 ,p_job_id in number
432 ,p_position_id in number
433 ,p_req in varchar2
434 ,p_vac_pos boolean default false
435 ,p_person_temp boolean default false
436 )
437 RETURN INTEGER
438 IS
439 l_ret_ess_des_match_count integer;
440 BEGIN
441
442 IF (p_vac_pos and p_person_temp) THEN
443 OPEN csr_tmp_ed_match_count(p_req);
444 FETCH csr_tmp_ed_match_count INTO l_ret_ess_des_match_count;
445 IF csr_tmp_ed_match_count%NOTFOUND
446 THEN
447 l_ret_ess_des_match_count := 0;
448 END IF;
449 CLOSE csr_tmp_ed_match_count;
450 ELSIF (p_vac_pos) THEN
451 OPEN csr_wp_tmp_ed_match_count(p_person_id, p_req);
452 FETCH csr_wp_tmp_ed_match_count INTO l_ret_ess_des_match_count;
453 IF csr_wp_tmp_ed_match_count%NOTFOUND
454 THEN
455 l_ret_ess_des_match_count := 0;
456 END IF;
457 CLOSE csr_wp_tmp_ed_match_count;
458 ELSIF (p_person_temp) THEN
459 OPEN csr_wp_per_tmp_ed_match_count(p_enterprise_id, p_organization_id,
460 p_job_id, p_position_id, p_req);
461 FETCH csr_wp_per_tmp_ed_match_count INTO l_ret_ess_des_match_count;
462 IF csr_wp_per_tmp_ed_match_count%NOTFOUND
463 THEN
464 l_ret_ess_des_match_count := 0;
465 END IF;
466 CLOSE csr_wp_per_tmp_ed_match_count;
467 ELSE
468 OPEN csr_workopp_ed_match_count(p_person_id, p_enterprise_id
469 ,p_organization_id, p_job_id, p_position_id, p_req);
470 FETCH csr_workopp_ed_match_count INTO l_ret_ess_des_match_count;
471 IF csr_workopp_ed_match_count%NOTFOUND
472 THEN
473 l_ret_ess_des_match_count := 0;
474 END IF;
475 CLOSE csr_workopp_ed_match_count;
476 END IF;
477
478 RETURN l_ret_ess_des_match_count;
479
480 END get_workopp_ed_match_count;
481
482
483 FUNCTION get_workopp_ed_total_count (
484 p_enterprise_id in number
485 ,p_organization_id in number
486 ,p_job_id in number
487 ,p_position_id in number
488 ,p_req in varchar2
489 ,p_vac_pos boolean default false
490 )
491 RETURN INTEGER
492 IS
493 l_ret_ess_des_total_count integer;
494 BEGIN
495
496 IF (p_vac_pos) THEN
497 OPEN csr_total_ess_des_count(p_req);
498 FETCH csr_total_ess_des_count INTO l_ret_ess_des_total_count;
499 IF csr_total_ess_des_count%NOTFOUND
500 THEN
501 l_ret_ess_des_total_count := 0;
502 END IF;
503 CLOSE csr_total_ess_des_count;
504 ELSE
505 OPEN csr_total_workopp_match_count(p_enterprise_id
506 ,p_organization_id, p_job_id, p_position_id, p_req);
507 FETCH csr_total_workopp_match_count INTO l_ret_ess_des_total_count;
508 IF csr_total_workopp_match_count%NOTFOUND
509 THEN
510 l_ret_ess_des_total_count := 0;
511 END IF;
512 CLOSE csr_total_workopp_match_count;
513 END IF;
514
515 RETURN l_ret_ess_des_total_count;
516 END get_workopp_ed_total_count;
517
518
519 PROCEDURE get_ess_desired_match (
520 p_person_id in number
521 ,p_enterprise_id in number default -1
522 ,p_organization_id in number default -1
523 ,p_job_id in number default -1
524 ,p_position_id in number default -1
525 ,p_vacancy_id in number default -1
526 ,p_req in varchar2
527 ,p_person_temp in number default 0
528 ,p_ess_des_match_count out nocopy number
529 ,p_total_ess_des_count out nocopy number
530
531 )
532 IS
533
534 l_enterprise_id per_competence_elements.enterprise_id%type;
535 l_organization_id per_competence_elements.organization_id%type;
536 l_job_id per_competence_elements.job_id%type;
537 l_position_id per_competence_elements.position_id%type;
538 l_vacancy_id per_all_vacancies.vacancy_id%type;
539 is_vac_pos boolean default false;
540
541 BEGIN
542
543 -- move into local variables
544 l_enterprise_id := p_enterprise_id;
545 l_organization_id := p_organization_id;
546 l_job_id := p_job_id;
547 l_position_id := nvl(p_position_id, -1);
548 l_vacancy_id := nvl(p_vacancy_id, -1);
549
550 -- now get the position and vacancy dets, if any
551 IF(l_vacancy_id <> -1) THEN
552 is_vac_pos := true;
553 OPEN csr_vacancy_dets(p_vacancy_id);
554 FETCH csr_vacancy_dets INTO l_vacancy_id, l_organization_id,
555 l_job_id, l_position_id;
556 CLOSE csr_vacancy_dets;
557 END IF;
558 IF(l_position_id <> -1) THEN
559 is_vac_pos := true;
560 OPEN csr_position_dets(p_position_id);
561 FETCH csr_position_dets INTO l_position_id, l_organization_id,
562 l_job_id;
563 CLOSE csr_position_dets;
564 END IF;
565
566
567 -- if the passed in is a vacancy id/position id need to apply overridding rules
568 -- and populate the temp table
569 IF (is_vac_pos) THEN
570 apply_overridding_rules(l_enterprise_id, l_organization_id, l_job_id, l_position_id);
571 END IF;
572
573 -- get ess match count
574 p_ess_des_match_count := get_workopp_ed_match_count(p_person_id, l_enterprise_id,
575 l_organization_id, l_job_id, l_position_id, p_req, is_vac_pos, (p_person_temp = 1));
576 -- get total ess count
577 p_total_ess_des_count := get_workopp_ed_total_count(l_enterprise_id
578 ,l_organization_id, l_job_id, l_position_id, p_req, is_vac_pos);
579
580 END get_ess_desired_match;
581
582
583 PROCEDURE populate_comp_temp_table (
584 p_temp_tab IN SSHR_SM_COMP_DETAILS_TAB_TYP
585 )
586 IS
587 BEGIN
588
589 DELETE FROM per_suitmatch_comp;
590 FOR I IN 1 ..p_temp_tab.count LOOP
591 insert into per_suitmatch_comp (competence_id,
592 mandatory, min_step_value)
593 values (to_number(p_temp_tab(I).competence_id)
594 ,p_temp_tab(I).mandatory
595 ,to_number(p_temp_tab(I).min_step_value));
596 END LOOP;
597 commit;
598 END populate_comp_temp_table;
599
600 PROCEDURE populate_per_temp_table (
601 p_temp_tab IN SSHR_SM_COMP_DETAILS_TAB_TYP
602 )
603 IS
604 --cursor to get the suitable work opp ids
605 CURSOR csr_suit_workopps(p_person_id in number) IS
606 SELECT /*+ leading(pcp) index(pce, PER_COMPETENCE_ELEMENTS_N1) */
607 decode(pce.enterprise_id, null,
608 (decode(pce.organization_id, null,
609 (decode(pce.job_id, null,
610 (decode(pce.position_id,null,
611 -1, pce.position_id)),pce.job_id)),
612 pce.organization_id)),pce.enterprise_id) workopp_id,
613 decode(pce.enterprise_id, null,
614 (decode(pce.organization_id, null,
615 (decode(pce.job_id, null,
616 (decode(pce.position_id, null,
617 'SM','POS')),'JOB')),'ORG')),'BUS') type,
618 ppf.person_id
619 FROM per_competence_elements pce,
620 per_suitmatch_person pcp,
621 per_all_people_f ppf
622 WHERE pce.type = 'REQUIREMENT'
623 AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
624 and nvl(pce.effective_date_to, trunc(sysdate))
625 AND pce.business_group_id = decode(hr_general.get_xbg_profile,
626 'Y', pce.business_group_id, ppf.business_group_id)
627 AND pce.competence_id = pcp.competence_id
628 AND pcp.mandatory = p_person_id
629 AND ppf.person_id = pcp.mandatory
630 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
631 AND pce.object_name is null
632 GROUP BY pce.enterprise_id, pce.organization_id, pce.job_id, pce.position_id, ppf.person_id
633 ORDER BY type;
634
635 TYPE csr_workopp_tab IS TABLE OF csr_suit_workopps%ROWTYPE INDEX BY BINARY_INTEGER;
636 l_csr_workopp_tab csr_workopp_tab;
637 l_person_id number;
638 J integer := 0;
639 l_ess_match varchar2(30);
640 l_des_match varchar2(30);
641 l_meets varchar2(30);
642 l_ess_match_count integer;
643 l_total_ess_count integer;
644 l_des_match_count integer;
645 l_total_des_count integer;
646
647 BEGIN
648 --insert the person competencies into per_suitmatch_person temp table
649 DELETE FROM per_suitmatch_person;
650 FOR I IN 1 ..p_temp_tab.count LOOP
651 l_person_id := p_temp_tab(I).mandatory;
652 insert into per_suitmatch_person (competence_id,
653 mandatory, min_step_value)
654 values (to_number(p_temp_tab(I).competence_id)
655 ,p_temp_tab(I).mandatory
656 ,to_number(p_temp_tab(I).min_step_value));
657 END LOOP;
658
659 --get suitable work opportunities using person competencies of per_suitmatch_person temp table
660 OPEN csr_suit_workopps(l_person_id);
661 LOOP
662 EXIT WHEN csr_suit_workopps%NOTFOUND;
663 J := J + 1;
664 FETCH csr_suit_workopps into l_csr_workopp_tab(J);
665 END LOOP;
666 CLOSE csr_suit_workopps; -- close cursor variable
667
668 --now populate the per_suitmatch_workopps with the suitable work opp, essential, desired match and meets
669 DELETE FROM per_suitmatch_workopps;
670 FOR I IN 1 ..l_csr_workopp_tab.count LOOP
671 BEGIN
672 -- get the essential and desired match
673 l_ess_match := null;
674 l_des_match := null;
675 l_meets := null;
676 IF (l_csr_workopp_tab(I).type = 'ORG') THEN
677 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, l_csr_workopp_tab(I).workopp_id, -1, -1, -1,
678 'Y', 1, l_ess_match_count, l_total_ess_count);
679 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, l_csr_workopp_tab(I).workopp_id, -1, -1, -1,
680 'N', 1, l_des_match_count, l_total_des_count);
681 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
682 l_des_match := l_des_match_count || '/' || l_total_des_count;
683 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
684 l_des_match_count, l_total_des_count);
685 ELSIF (l_csr_workopp_tab(I).type = 'JOB') THEN
686 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, l_csr_workopp_tab(I).workopp_id, -1, -1,
687 'Y', 1, l_ess_match_count, l_total_ess_count);
688 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, l_csr_workopp_tab(I).workopp_id, -1, -1,
689 'N', 1, l_des_match_count, l_total_des_count);
690 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
691 l_des_match := l_des_match_count || '/' || l_total_des_count;
692 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
693 l_des_match_count, l_total_des_count);
694 ELSIF (l_csr_workopp_tab(I).type = 'POS') THEN
695 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, -1, l_csr_workopp_tab(I).workopp_id, -1,
696 'Y', 1, l_ess_match_count, l_total_ess_count);
697 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, -1, l_csr_workopp_tab(I).workopp_id, -1,
698 'N', 1, l_des_match_count, l_total_des_count);
699 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
700 l_des_match := l_des_match_count || '/' || l_total_des_count;
701 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
702 l_des_match_count, l_total_des_count);
703 ELSIF (l_csr_workopp_tab(I).type = 'VAC') THEN
704 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, -1, -1, l_csr_workopp_tab(I).workopp_id,
705 'Y', 1, l_ess_match_count, l_total_ess_count);
706 get_ess_desired_match(l_csr_workopp_tab(I).person_id, -1, -1, -1, -1, l_csr_workopp_tab(I).workopp_id,
707 'N', 1, l_des_match_count, l_total_des_count);
708 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
709 l_des_match := l_des_match_count || '/' || l_total_des_count;
710 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
711 l_des_match_count, l_total_des_count);
712 END IF;
713 insert into per_suitmatch_workopps (workopp_id,
714 type, ess_match, des_match, meets)
715 values (to_number(l_csr_workopp_tab(I).workopp_id)
716 ,l_csr_workopp_tab(I).type, l_ess_match, l_des_match, l_meets);
717 END;
718 END LOOP;
719 commit;
720 END populate_per_temp_table;
721
722 PROCEDURE populate_workopp_temp_table (
723 p_temp_tab IN SSHR_SM_COMP_DETAILS_TAB_TYP
724 )IS
725
726 l_ess_match varchar2(30);
727 l_des_match varchar2(30);
728 l_meets varchar2(30);
729 l_ess_match_count integer;
730 l_total_ess_count integer;
731 l_des_match_count integer;
732 l_total_des_count integer;
733
734 BEGIN
735 DELETE FROM per_suitmatch_workopps;
736 FOR I IN 1 ..p_temp_tab.count LOOP
737 -- get the essential and desired match
738 IF (p_temp_tab(I).mandatory = 'ORG') THEN
739 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, p_temp_tab(I).competence_id, -1, -1, -1,
740 'Y', 0, l_ess_match_count, l_total_ess_count);
741 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, p_temp_tab(I).competence_id, -1, -1, -1,
742 'N', 0, l_des_match_count, l_total_des_count);
743 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
744 l_des_match := l_des_match_count || '/' || l_total_des_count;
745 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
746 l_des_match_count, l_total_des_count);
747 ELSIF (p_temp_tab(I).mandatory = 'JOB') THEN
748 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, p_temp_tab(I).competence_id, -1, -1,
749 'Y', 0, l_ess_match_count, l_total_ess_count);
750 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, p_temp_tab(I).competence_id, -1, -1,
751 'N', 0, l_des_match_count, l_total_des_count);
752 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
753 l_des_match := l_des_match_count || '/' || l_total_des_count;
754 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
755 l_des_match_count, l_total_des_count);
756 ELSIF (p_temp_tab(I).mandatory = 'POS') THEN
757 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, -1, p_temp_tab(I).competence_id, -1,
758 'Y', 0, l_ess_match_count, l_total_ess_count);
759 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, -1, p_temp_tab(I).competence_id, -1,
760 'N', 0, l_des_match_count, l_total_des_count);
761 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
762 l_des_match := l_des_match_count || '/' || l_total_des_count;
763 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
764 l_des_match_count, l_total_des_count);
765 ELSIF (p_temp_tab(I).mandatory = 'VAC') THEN
766 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, -1, -1, p_temp_tab(I).competence_id,
767 'Y', 0, l_ess_match_count, l_total_ess_count);
768 get_ess_desired_match(p_temp_tab(I).min_step_value, -1, -1, -1, -1, p_temp_tab(I).competence_id,
769 'N', 0, l_des_match_count, l_total_des_count);
770 l_ess_match := l_ess_match_count || '/' || l_total_ess_count;
771 l_des_match := l_des_match_count || '/' || l_total_des_count;
772 l_meets := compare_counts(l_ess_match_count, l_total_ess_count,
773 l_des_match_count, l_total_des_count);
774 END IF;
775 insert into per_suitmatch_workopps (workopp_id,
776 type, ess_match, des_match, meets)
777 values (to_number(p_temp_tab(I).competence_id)
778 ,p_temp_tab(I).mandatory, l_ess_match, l_des_match, l_meets);
779 END LOOP;
780 commit;
781 END populate_workopp_temp_table;
782
783 PROCEDURE insert_workopp_temp_table (
784 p_temp_tab IN SSHR_SM_COMP_DETAILS_TAB_TYP
785 )
786 IS
787 BEGIN
788 DELETE FROM per_suitmatch_workopps;
789 FOR I IN 1 ..p_temp_tab.count LOOP
790 insert into per_suitmatch_workopps (workopp_id,
791 type)
792 values (to_number(p_temp_tab(I).competence_id)
793 ,p_temp_tab(I).mandatory);
794 END LOOP;
795 commit;
796
797 END insert_workopp_temp_table;
798
799
800 FUNCTION get_ess_des_match_count (
801 p_person_id in number
802 ,p_req in varchar2
803 )
804 RETURN INTEGER
805 IS
806 l_ret_ess_des_match_count integer;
807 BEGIN
808 IF NVL(g_prev_person_id,-1) <> p_person_id THEN
809 OPEN csr_ess_des_match_count(p_person_id, p_req);
810 FETCH csr_ess_des_match_count INTO l_ret_ess_des_match_count;
811 IF csr_ess_des_match_count%NOTFOUND
812 THEN
813 l_ret_ess_des_match_count := 0;
814 END IF;
815 CLOSE csr_ess_des_match_count;
816 hr_utility.trace('initialize globals');
817 IF p_req = 'Y' THEN
818 g_prev_ess_count := l_ret_ess_des_match_count;
819 ELSE
820 g_prev_des_count := l_ret_ess_des_match_count;
821 END IF;
822 ELSE
823 hr_utility.trace('Same person: Using Globals now:get_ess_desired_match');
824 IF p_req = 'Y' THEN
825 l_ret_ess_des_match_count := g_prev_ess_count;
826 ELSE
827 l_ret_ess_des_match_count := g_prev_des_count;
828 END IF;
829 hr_utility.trace('g_prev_des_count:'||g_prev_des_count);
830 hr_utility.trace('g_prev_ess_count:'||g_prev_ess_count);
831 END IF;
832 hr_utility.trace('Leaving get_ess_des_match_count:'||p_person_id||':'||p_req||':'||l_ret_ess_des_match_count);
833 RETURN l_ret_ess_des_match_count;
834 END get_ess_des_match_count;
835
836
837 FUNCTION get_ess_des_total_count (
838 p_req in varchar2
839 )
840 RETURN INTEGER
841 IS
842 l_ret_ess_des_total_count integer;
843 BEGIN
844 OPEN csr_total_ess_des_count(p_req);
845 FETCH csr_total_ess_des_count INTO l_ret_ess_des_total_count;
846 IF csr_total_ess_des_count%NOTFOUND
847 THEN
848 l_ret_ess_des_total_count := 0;
849 END IF;
850 CLOSE csr_total_ess_des_count;
851
852 RETURN l_ret_ess_des_total_count;
853 END get_ess_des_total_count;
854
855
856 FUNCTION compare_counts(
857 p_ess_match_count in integer
858 ,p_total_ess_count in integer
859 ,p_des_match_count in integer
860 ,p_total_des_count in integer
861 ,p_meets in varchar2
862 )
863 RETURN VARCHAR2
864 IS
865 BEGIN
866
867 IF (p_meets = 'ALL') THEN
868 IF ((p_total_ess_count <> 0 AND p_total_des_count <> 0) AND
869 ((p_ess_match_count = p_total_ess_count ) AND
870 (p_des_match_count = p_total_des_count ))) THEN
871 RETURN 'Y';
872 ELSE
873 RETURN 'N';
874 END IF;
875 ELSIF (p_meets = 'ESS') THEN
876 IF ((p_total_ess_count <> 0 AND (p_ess_match_count = p_total_ess_count))
877 AND (p_total_des_count = 0 OR (p_des_match_count <> p_total_des_count))) THEN
878 RETURN 'Y';
879 ELSE
880 RETURN 'N';
881 END IF;
882 ELSIF (p_meets = 'DES') THEN
883 IF ((p_total_des_count <> 0 AND (p_des_match_count = p_total_des_count))
884 AND (p_total_ess_count = 0 OR (p_ess_match_count <> p_total_ess_count))) THEN
885 RETURN 'Y';
886 ELSE
887 RETURN 'N';
888 END IF;
889 ELSIF (p_meets = 'ED') THEN
890 IF (((p_total_ess_count = 0 OR
891 (p_ess_match_count <> p_total_ess_count)) AND
892 (p_total_des_count = 0 OR
893 (p_des_match_count <> p_total_des_count )))OR
894 (p_total_ess_count = 0 AND p_total_des_count = 0)) THEN
895 RETURN 'Y';
896 ELSE
897 RETURN 'N';
898 END IF;
899 END IF;
900
901 END compare_counts;
902
903 FUNCTION get_ess_desired_match (
904 p_person_id in number
905 ,p_req in varchar2
906 )
907 RETURN VARCHAR2
908 IS
909
910 l_ess_des_match_count integer;
911 l_total_ess_des_count integer;
912
913 BEGIN
914 -- get ess match count
915 l_ess_des_match_count := get_ess_des_match_count(p_person_id, p_req);
916 hr_utility.trace('Return from get_ess_desired_match:'||p_req||':'||p_person_id||l_ess_des_match_count);
917 RETURN l_ess_des_match_count || '/' ;
918 END get_ess_desired_match;
919
920
921 FUNCTION get_ess_desired_match (
922 p_person_id in number
923 ,p_enterprise_id in number default -1
924 ,p_organization_id in number default -1
925 ,p_job_id in number default -1
926 ,p_position_id in number default -1
927 ,p_vacancy_id in number default -1
928 ,p_req in varchar2
929 ,p_person_temp in number default 0
930 )
931 RETURN VARCHAR2
932 IS PRAGMA AUTONOMOUS_TRANSACTION;
933
934 l_enterprise_id per_competence_elements.enterprise_id%type;
935 l_organization_id per_competence_elements.organization_id%type;
936 l_job_id per_competence_elements.job_id%type;
937 l_position_id per_competence_elements.position_id%type;
938 l_vacancy_id per_all_vacancies.vacancy_id%type;
939 l_ess_des_match_count integer;
940 l_total_ess_des_count integer;
941 is_vac_pos boolean default false;
942
943 BEGIN
944
945 -- move into local variables
946 l_enterprise_id := p_enterprise_id;
947 l_organization_id := p_organization_id;
948 l_job_id := p_job_id;
949 l_position_id := nvl(p_position_id, -1);
950 l_vacancy_id := nvl(p_vacancy_id, -1);
951
952 -- now get the position and vacancy dets, if any
953 IF(l_vacancy_id <> -1) THEN
954 is_vac_pos := true;
955 OPEN csr_vacancy_dets(p_vacancy_id);
956 FETCH csr_vacancy_dets INTO l_vacancy_id, l_organization_id,
957 l_job_id, l_position_id;
958 CLOSE csr_vacancy_dets;
959 END IF;
960 IF(l_position_id <> -1) THEN
961 is_vac_pos := true;
962 OPEN csr_position_dets(p_position_id);
963 FETCH csr_position_dets INTO l_position_id, l_organization_id,
964 l_job_id;
965 CLOSE csr_position_dets;
966 END IF;
967
968
969 -- if the passed in is a vacancy id/position id need to apply overridding rules
970 -- and populate the temp table
971 IF (is_vac_pos) THEN
972 apply_overridding_rules(l_enterprise_id, l_organization_id, l_job_id, l_position_id);
973 END IF;
974
975 -- get ess match count
976 l_ess_des_match_count := get_workopp_ed_match_count(p_person_id, l_enterprise_id,
977 l_organization_id, l_job_id, l_position_id, p_req, is_vac_pos, (p_person_temp = 1));
978 -- get total ess count
979 l_total_ess_des_count := get_workopp_ed_total_count(l_enterprise_id
980 ,l_organization_id, l_job_id, l_position_id, p_req, is_vac_pos);
981
982 --- return the ess match
983 RETURN l_ess_des_match_count || '/' || l_total_ess_des_count;
984
985 END get_ess_desired_match;
986
987 FUNCTION is_ess_des_meets (
988 p_person_id in number
989 ,p_meets in varchar2
990 )
991 RETURN VARCHAR2
992 IS
993
994 l_ess_match_count integer;
995 l_total_ess_count integer;
996 l_des_match_count integer;
997 l_total_des_count integer;
998
999
1000 BEGIN
1001 hr_utility.trace('Inside is_ess_des_meets'||p_person_id);
1002
1003 -- first get the ess match count
1004 l_ess_match_count := get_ess_des_match_count(p_person_id, 'Y');
1005 -- get the des match count
1006 l_des_match_count := get_ess_des_match_count(p_person_id, 'N');
1007 -- get the total ess count
1008 l_total_ess_count := get_ess_des_total_count('Y');
1009 -- get the total des count
1010 l_total_des_count := get_ess_des_total_count('N');
1011
1012 -- added for bug#11728082
1013 IF NVL(g_prev_person_id,-1) <> p_person_id THEN
1014 g_prev_person_id := p_person_id;
1015 END IF;
1016 -- till here
1017
1018 RETURN compare_counts(
1019 l_ess_match_count
1020 ,l_total_ess_count
1021 ,l_des_match_count
1022 ,l_total_des_count
1023 ,p_meets);
1024
1025 END is_ess_des_meets;
1026
1027
1028 FUNCTION is_ess_des_meets (
1029 p_person_id in number
1030 ,p_enterprise_id in number default -1
1031 ,p_organization_id in number default -1
1032 ,p_job_id in number default -1
1033 ,p_position_id in number default -1
1034 ,p_vacancy_id in number default -1
1035 ,p_meets in varchar2
1036 ,p_person_temp in number default 0
1037 )
1038 RETURN VARCHAR2
1039 IS PRAGMA AUTONOMOUS_TRANSACTION;
1040
1041 l_enterprise_id per_competence_elements.enterprise_id%type;
1042 l_organization_id per_competence_elements.organization_id%type;
1043 l_job_id per_competence_elements.job_id%type;
1044 l_position_id per_competence_elements.position_id%type;
1045 l_vacancy_id per_all_vacancies.vacancy_id%type;
1046
1047 l_ess_match_count integer;
1048 l_total_ess_count integer;
1049 l_des_match_count integer;
1050 l_total_des_count integer;
1051 is_vac_pos boolean default false;
1052
1053 BEGIN
1054
1055 -- move into local variables
1056 l_enterprise_id := p_enterprise_id;
1057 l_organization_id := p_organization_id;
1058 l_job_id := p_job_id;
1059 l_position_id := nvl(p_position_id, -1);
1060 l_vacancy_id := nvl(p_vacancy_id, -1);
1061
1062 -- now get the position and vacancy dets, if any
1063 IF(l_vacancy_id <> -1) THEN
1064 is_vac_pos := true;
1065 OPEN csr_vacancy_dets(p_vacancy_id);
1066 FETCH csr_vacancy_dets INTO l_vacancy_id, l_organization_id,
1067 l_job_id, l_position_id;
1068 CLOSE csr_vacancy_dets;
1069 END IF;
1070 IF(l_position_id <> -1) THEN
1071 is_vac_pos := true;
1072 OPEN csr_position_dets(p_position_id);
1073 FETCH csr_position_dets INTO l_position_id, l_organization_id,
1074 l_job_id;
1075 CLOSE csr_position_dets;
1076 END IF;
1077
1078
1079 -- if the passed in is a vacancy id/position id need to apply overridding rules
1080 -- and populate the temp table
1081 IF (is_vac_pos) THEN
1082 apply_overridding_rules(l_enterprise_id, l_organization_id, l_job_id, l_position_id);
1083 END IF;
1084
1085 -- first get the ess match count
1086 l_ess_match_count := get_workopp_ed_match_count(p_person_id, l_enterprise_id
1087 ,l_organization_id, l_job_id, l_position_id, 'Y', is_vac_pos, (p_person_temp = 1));
1088 -- get the des match count
1089 l_des_match_count := get_workopp_ed_match_count(p_person_id, l_enterprise_id
1090 ,l_organization_id, l_job_id, l_position_id, 'N', is_vac_pos, (p_person_temp = 1));
1091 -- get the total ess count
1092 l_total_ess_count := get_workopp_ed_total_count(l_enterprise_id
1093 ,l_organization_id, l_job_id, l_position_id, 'Y', is_vac_pos);
1094 -- get the total des count
1095 l_total_des_count := get_workopp_ed_total_count(l_enterprise_id
1096 ,l_organization_id, l_job_id, l_position_id, 'N', is_vac_pos);
1097
1098 RETURN compare_counts(
1099 l_ess_match_count
1100 ,l_total_ess_count
1101 ,l_des_match_count
1102 ,l_total_des_count
1103 ,p_meets);
1104
1105 END is_ess_des_meets;
1106
1107
1108 FUNCTION get_bg_name(
1109 p_bg_id in number
1110 )
1111 RETURN VARCHAR2
1112 IS
1113 CURSOR csr_bg_name IS
1114 SELECT name
1115 FROM hr_all_organization_units_tl
1116 WHERE organization_id = p_bg_id
1117 AND language = userenv('LANG');
1118
1119 l_bg_name hr_all_organization_units_tl.name%type;
1120
1121 BEGIN
1122
1123 OPEN csr_bg_name;
1124 FETCH csr_bg_name into l_bg_name;
1125 CLOSE csr_bg_name;
1126 RETURN l_bg_name;
1127
1128 END get_bg_name;
1129
1130 FUNCTION get_application_date(
1131 p_person_id in number
1132 )
1133 RETURN DATE
1134 IS
1135
1136 CURSOR csr_appl_date IS
1137 SELECT date_received
1138 FROM per_applications
1139 WHERE person_id(+) = p_person_id
1140 AND (date_end is null);
1141
1142 l_date_received per_applications.date_received%type;
1143
1144 BEGIN
1145
1146 OPEN csr_appl_date;
1147 FETCH csr_appl_date into l_date_received;
1148 CLOSE csr_appl_date;
1149 RETURN l_date_received;
1150
1151 END get_application_date;
1152
1153
1154 FUNCTION get_emp_start_date(
1155 p_period_of_service_id in number
1156 )
1157 RETURN DATE
1158 IS
1159
1160 CURSOR csr_emp_start_date IS
1161 SELECT date_start
1162 FROM per_periods_of_service
1163 WHERE period_of_service_id(+) = p_period_of_service_id;
1164
1165 l_date_start per_periods_of_service.date_start%type;
1166
1167 BEGIN
1168
1169 OPEN csr_emp_start_date;
1170 FETCH csr_emp_start_date into l_date_start;
1171 CLOSE csr_emp_start_date;
1172 RETURN l_date_start;
1173
1174 END get_emp_start_date;
1175
1176 FUNCTION get_cwk_start_date(
1177 p_person_id in number
1178 )
1179 RETURN DATE
1180 IS
1181
1182 CURSOR csr_cwk_start_date IS
1183 SELECT date_start
1184 FROM per_periods_of_placement
1185 WHERE actual_termination_date is null OR
1186 (trunc(sysdate) < actual_termination_date);
1187
1188 l_date_start per_periods_of_placement.date_start%type;
1189
1190 BEGIN
1191
1192 OPEN csr_cwk_start_date;
1193 FETCH csr_cwk_start_date into l_date_start;
1194 CLOSE csr_cwk_start_date;
1195 RETURN l_date_start;
1196
1197 END get_cwk_start_date;
1198
1199
1200 END HR_SUIT_MATCH_UTIL_SS;
[Home] [Help]