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