1 Package body PerCompStatus AS
2 /* $Header: hrcpstats.pkb 120.0 2005/05/31 23:55:44 appldev noship $*/
3 /* this fucntion returns the status of the competency
4 it checks for the outcomes defined for the competency
5 and if all the outcomes are achieved then return ACHIEVED else
6 IN_PROGRESS*/
7 FUNCTION Get_Competence_Status
8 (p_competence_id in varchar2
9 ,p_competence_element_id in varchar2
10 ,p_item_type IN VARCHAR2 DEFAULT null
11 ,p_item_key IN VARCHAR2 DEFAULT null
12 ,p_activity_id IN VARCHAR2 DEFAULT null
13 ,p_eff_date in date default trunc(sysdate)
14 ) return VARCHAR2 is
15 CURSOR getEndDatedOutcomes(compEleId IN Number) is
16 Select max(ceo.date_to),ceo.outcome_id
17 FROM per_comp_element_outcomes ceo , per_competence_outcomes pco
18 where competence_element_id = compEleId
19 AND ceo.date_to < p_eff_date
20 and pco.outcome_id = ceo.outcome_id
21 and pco.date_from <= p_eff_date
22 and nvl(pco.date_to,p_eff_date) >= p_eff_date
23 group by ceo.outcome_id;
24
25 l_competence_cluster per_competences_vl.competence_cluster%type;
26 l_noof_outcomes number;
27 p_person_id per_competence_elements.person_id%type;
28 l_status per_competence_elements.status%type;
29 l_achieved_date per_competence_elements.Achieved_date%type;
30 begin
31 Select competence_cluster INTO l_competence_cluster FROM per_competences_vl
32 WHERE competence_id = p_competence_id;
33 IF l_competence_cluster = 'UNIT_STANDARD' then
34 IF p_competence_element_id IS NOT NULL then
35 -- added for the 4187713 bug.
36 -- Getting the End date outcome rec which were achieved earlier.
37 Select pce.status, pce.Achieved_date, pce.person_id
38 INTO l_status, l_achieved_date, p_person_id
39 FROM per_competence_elements pce
40 Where pce.competence_element_id = p_competence_element_id;
41 IF l_status = 'ACHIEVED' AND l_achieved_date <= p_eff_date then
42 FOR EndDatedRec IN getEndDatedOutcomes(p_competence_element_id)
43 loop
44 -- checking if the end dated outcome has achieved rec for the effective date.
45 Select count(*) INTO l_noof_outcomes
46 FROM Per_comp_element_outcomes CEO
47 Where ceo.outcome_id = EndDatedRec.outcome_id
48 AND ceo.competence_element_id = p_competence_element_id
49 AND ceo.date_from <= p_eff_date
50 AND nvl(ceo.date_to,p_eff_date) >= p_eff_date;
51 IF l_noof_outcomes = 0 Then
52 IF p_item_type IS NULL OR p_item_key IS NULL then
53 RETURN 'IN_PROGRESS';
54 else
55 -- checking if the outcome is achieved in current session.
56 Select count(*) INTO l_noof_outcomes
57 FROM hr_api_transaction_steps S, hr_api_transaction_values tv,
58 hr_api_transaction_values tv1, hr_api_transaction_values tv2,
59 hr_api_transaction_values tv3
60 Where s.item_type = p_item_type
61 and s.item_key = p_item_key
62 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
63 AND s.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
64 and tv.transaction_step_id = s.transaction_step_id
65 and tv1.transaction_step_id = s.transaction_step_id
66 and tv2.transaction_step_id = s.transaction_step_id
67 and tv3.transaction_step_id = s.transaction_step_id
68 AND tv3.NAME = 'P_COMPETENCE_ELEMENT_ID'
69 AND tv3.Number_Value = p_competence_element_id
70 AND tv.NAME = 'P_OUTCOME_ID'
71 AND tv1.NAME = 'P_DATE_FROM'
72 AND tv2.NAME = 'P_DATE_TO'
73 AND tv.number_value = EndDatedRec.outcome_id
74 AND tv1.date_value <= p_eff_date
75 AND nvl(tv2.date_value,p_eff_date) >= p_eff_date;
76
77
78 IF l_noof_outcomes = 0 then
79 RETURN 'IN_PROGRESS';
80 END if;
81 END if;
82 END if;
83
84 END loop;
85
86 IF p_item_type IS NULL OR p_item_key IS NULL then
87 RETURN 'ACHIEVED';
88 ELSE -- p_ietm_type and p_item_key is not null
89 Select count(*) into l_noof_outcomes FROM hr_api_transaction_steps S, hr_api_transaction_values C,
90 Per_comp_element_outcomes CEO
91 Where s.item_type = p_item_type
92 and s.item_key = p_item_key
93 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
94 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
95 and c.transaction_step_id = s.transaction_step_id
96 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
97 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID
98 AND ceo.Competence_element_id = p_competence_element_id;
99
100 IF l_noof_outcomes > 0 then
101 RETURN 'IN_PROGRESS';
102 ELSE -- l_noof_outcomes = 0
103
104 Select count(*) INTO l_noof_outcomes
105 FROM hr_api_transaction_steps ts,
106 hr_api_transaction_values tv,
107 hr_api_transaction_values tv1, hr_api_transaction_values tv2,
108 hr_api_transaction_values tv3, per_competence_outcomes pco
109 Where ts.ITEM_TYPE = p_item_type
110 AND ts.item_key = p_item_key
111 And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
112 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
113 AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
114 AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
115 AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID
116 AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
117 AND tv3.NAME = 'P_PERSON_ID'
118 AND tv3.Number_Value = p_person_id
119 AND tv.NAME = 'P_OUTCOME_ID'
120 AND tv1.NAME = 'P_DATE_FROM'
121 AND tv2.NAME = 'P_DATE_TO'
122 And tv.number_value = pco.outcome_id
123 AND pco.competence_id = p_competence_id
124 AND nvl(p_eff_date,trunc(sysdate)) BETWEEN pco.date_from AND nvl(pco.date_to,nvl(p_eff_date,trunc(sysdate)))
125 AND nvl(tv2.date_value,trunc(p_eff_date)) < trunc(p_eff_date);
126 IF l_noof_outcomes = 0 then
127 RETURN 'ACHIEVED';
128 ELSE
129 RETURN 'IN_PROGRESS';
130 END if;
131
132 END if; -- end of l_noof_outcomes > 0
133
134
135 END if; --End p_ietm_type or p_item_key is null
136
137 END if; -- end of status = 'ACHIEVED'
138
139
140
141 -- END for the 4187713 bug.
142 Select count(*) INTO l_noof_outcomes from
143 per_competence_outcomes pco
144 WHERE competence_id = p_competence_id
145 AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
146 AND NOT EXISTS ( (Select 1 FROM per_comp_element_outcomes
147 Where competence_element_id = p_competence_element_id
148 AND per_comp_element_outcomes.outcome_id = pco.outcome_id
149 AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
150 AND NOT EXISTS
151 (Select 1 FROM hr_api_transaction_steps S, hr_api_transaction_values C,
152 Per_comp_element_outcomes CEO
153 Where s.item_type = p_item_type
154 and s.item_key = p_item_key
155 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
156 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
157 and c.transaction_step_id = s.transaction_step_id
158 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
159 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID
160 AND ceo.Competence_element_id = p_competence_element_id
161 AND ceo.Outcome_id = pco.Outcome_id))
162 Union All
163 (Select 1 FROM hr_api_transaction_values tv,
164 hr_api_transaction_values tv1, hr_api_transaction_values tv2,
165 hr_api_transaction_values tv3 , hr_api_transaction_steps ts
166 Where ts.ITEM_TYPE = p_item_type
167 AND ts.item_key = p_item_key
168 And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
169 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
170 AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
171 AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
172 AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
173 AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
174 AND tv3.NAME = 'P_PERSON_ID'
175 AND tv3.Number_Value = p_person_id
176 AND tv.NAME = 'P_OUTCOME_ID'
177 AND tv1.NAME = 'P_DATE_FROM'
178 AND tv2.NAME(+) = 'P_DATE_TO'
179 And tv.number_value = pco.outcome_id
180 And trunc(p_eff_date) BETWEEN tv1.date_value
181 AND nvl(tv2.date_value,trunc(p_eff_date))));
182 else
183 Select count(*) INTO l_noof_outcomes from
184 per_competence_outcomes pco
185 Where pco.Competence_id = p_competence_id
186 AND pco.date_from <= p_eff_date
187 AND nvl(pco.date_to,p_eff_date) >= p_eff_date;
188 END if;
189 IF l_noof_outcomes = 0 then
190 RETURN 'ACHIEVED';
191 else
192 RETURN 'IN_PROGRESS';
193 END if;
194 ELSE
195 Return 'ACHIEVED';
196 END if;
197 End Get_Competence_Status;
198 ------------
199 function get_status_meaning_and_id
200 (p_competence_id in varchar2
201 ,p_competence_element_id in varchar2
202 ,p_item_type IN VARCHAR2 DEFAULT null
203 ,p_item_key IN VARCHAR2 DEFAULT null
204 ,p_activity_id IN VARCHAR2 DEFAULT null
205 ,p_eff_date IN DATE DEFAULT trunc(sysdate))
206 RETURN VARCHAR2 is
207 l_status_id per_competence_elements.status%type;
208 l_status_meaning varchar2(100);
209 l_noof_outcomes number;
210 l_competence_cluster per_competences.competence_cluster%type;
211 p_person_id per_competence_elements.person_id%type;
212 begin
213 l_status_id := PerCompStatus.Get_Competence_Status(
214 p_competence_id => p_competence_id
215 ,p_competence_element_id => p_competence_element_id
216 ,p_item_type => p_item_type
217 ,p_item_key => p_item_key
218 ,p_activity_id => p_activity_id
219 ,p_eff_date => p_eff_date );
220 Select HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_COMP_STATUS', l_status_id) INTO l_status_meaning
221 FROM dual;
222 RETURN l_status_meaning;
223 END get_status_meaning_and_id;
224 ----------
225 FUNCTION Get_Competence_Status
226 (p_item_type in varchar2
227 ,p_item_key IN varchar2
228 ,p_activity_id IN varchar2
229 ,p_competence_id in number
230 ,p_competence_element_id IN NUMBER DEFAULT null
231 ,p_person_id IN number
232 ,p_eff_date in date default trunc(sysdate)
233 ) return VARCHAR2 is
234 l_competence_cluster per_competences_vl.competence_cluster%type;
235 l_noof_outcomes number;
236 l_comp_status per_competence_elements.status%type;
237 begin
238 Select competence_cluster INTO l_competence_cluster FROM per_competences_vl
239 WHERE competence_id = p_competence_id;
240 IF l_competence_cluster = 'UNIT_STANDARD' then
241 IF p_competence_element_id IS NOT NULL OR p_competence_element_id > 0 then
242 l_comp_status := Get_Competence_Status(
243 p_competence_id => p_competence_id
244 ,p_competence_element_id => p_competence_element_id
245 ,p_item_type => p_item_type
246 ,p_item_key => p_item_key
247 ,p_activity_id => p_activity_id
248 ,p_eff_date => p_eff_date );
249 RETURN l_comp_status;
250 END if;
251 Select count(*) INTO l_noof_outcomes
252 from
253 per_competence_outcomes pco
254 WHERE pco.competence_id = p_competence_id
255 AND nvl(p_eff_date,sysdate) BETWEEN pco.date_from AND nvl(pco.date_to,nvl(p_eff_date,trunc(sysdate)))
256 AND NOT exists (Select 1 FROM hr_api_transaction_values tv,
257 hr_api_transaction_values tv1, hr_api_transaction_values tv2,
258 hr_api_transaction_values tv3 , hr_api_transaction_steps ts
259 Where ts.ITEM_TYPE = p_item_type
260 AND ts.item_key = p_item_key
261 And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
262 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
263 AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
264 AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
265 AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
266 AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
267 AND tv3.NAME = 'P_PERSON_ID'
268 AND tv3.Number_Value = p_person_id
269 AND tv.NAME = 'P_OUTCOME_ID'
270 AND tv1.NAME = 'P_DATE_FROM'
271 AND tv2.NAME(+) = 'P_DATE_TO'
272 And tv.number_value = pco.outcome_id
273 And trunc(p_eff_date) BETWEEN tv1.date_value
274 AND nvl(tv2.date_value,trunc(p_eff_date))
275 Union all
276 (Select 1 FROM Per_comp_element_outcomes CEO
277 Where ceo.Competence_element_id = p_competence_element_id
278 AND ceo.Outcome_id = pco.Outcome_id
279 And ceo.date_from <= trunc(p_eff_date)
280 AND nvl(ceo.date_to,trunc(p_eff_date)) >= trunc(p_eff_date)
281 AND NOT EXISTS ( SELECT 1 from
282 hr_api_transaction_steps S, hr_api_transaction_values C
283 Where s.item_type = p_item_type
284 and s.item_key = p_item_key
285 and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
286 and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
287 and c.transaction_step_id = s.transaction_step_id
288 AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
289 AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID
290 )));
291 IF l_noof_outcomes = 0 then
292 Select count(*) INTO l_noof_outcomes
293 FROM hr_api_transaction_steps ts,
294 hr_api_transaction_values tv,
295 hr_api_transaction_values tv1, hr_api_transaction_values tv2,
296 hr_api_transaction_values tv3, per_competence_outcomes pco
297 Where ts.ITEM_TYPE = p_item_type
298 AND ts.item_key = p_item_key
299 And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
300 AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
301 AND ts.TRANSACTION_STEP_ID = tv.TRANSACTION_STEP_ID
302 AND ts.TRANSACTION_STEP_ID = tv1.TRANSACTION_STEP_ID
303 AND ts.TRANSACTION_STEP_ID = tv2.TRANSACTION_STEP_ID(+)
304 AND ts.TRANSACTION_STEP_ID = tv3.TRANSACTION_STEP_ID
305 AND tv3.NAME = 'P_PERSON_ID'
306 AND tv3.Number_Value = p_person_id
307 AND tv.NAME = 'P_OUTCOME_ID'
308 AND tv1.NAME = 'P_DATE_FROM'
309 AND tv2.NAME(+) = 'P_DATE_TO'
310 And tv.number_value = pco.outcome_id
311 AND pco.competence_id = p_competence_id
312 AND p_eff_date BETWEEN pco.date_from AND nvl(pco.date_to,p_eff_date)
313 AND nvl(tv2.date_value,p_eff_date) < p_eff_date;
314 IF l_noof_outcomes = 0 then
315 RETURN 'ACHIEVED';
316 ELSE
317 RETURN 'IN_PROGRESS';
318 END if;
319 else
320 RETURN 'IN_PROGRESS';
321 END if;
322 ELSE
323 Return 'ACHIEVED';
324 END if;
325 End Get_Competence_Status;
326
327
328 Function IsAllCompAchieved
329 ( p_qualification_type_id IN number
330 , p_person_id IN number)
331 RETURN VARCHAR2 IS
332 CURSOR getCompIds(p_qualification_type_id IN number) is
333 SELECT pce.competence_id from
334 per_competence_elements pce
335 Where pce.TYPE = 'QUALIFICATION'
336 AND pce.Qualification_type_id = p_qualification_type_id
337 AND pce.effective_date_from <= trunc(sysdate)
338 AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);
339 Cursor getCompEleId(p_competence_id IN number
340 , p_person_id IN number) is
341 Select competence_element_id from
342 per_competence_elements pce
343 Where pce.TYPE = 'PERSONAL'
344 AND pce.competence_id = p_competence_id
345 AND pce.person_id = p_person_id
346 AND pce.effective_date_from <= trunc(sysdate)
347 AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);
348 l_ret_val varchar2(10);
349 l_NoOfCompAchieved number;
350 l_TotComps number;
351 l_comp_status varchar2(20);
352 Begin
353 l_ret_val := 'NOT';
354 l_NoOfCompAchieved := 0;
355 l_TotComps := 0;
356 Select count(*) INTO l_TotComps
357 from
358 per_competence_elements pce
359 Where pce.TYPE = 'QUALIFICATION'
360 AND pce.Qualification_type_id = p_qualification_type_id
361 AND pce.effective_date_from <= trunc(sysdate)
362 AND nvl(pce.effective_date_to,trunc(sysdate)) >= trunc(sysdate);
363 IF l_TotComps = 0 then
364 l_ret_val := 'ACHIEVED';
365 RETURN l_ret_val;
366 END if;
367 For CompIds IN getCompIds ( p_qualification_type_id => p_qualification_type_id)
368 loop
369 FOR CompEled IN getCompEleId ( p_competence_id => CompIds.competence_id
370 ,p_person_id => p_person_id)
371 loop
372 l_comp_status := Get_Competence_Status
373 (p_competence_id => CompIds.Competence_id
374 ,p_competence_element_id => CompEled.competence_element_id
375 ,p_item_type => null
376 ,p_item_key => null
377 ,p_activity_id => null
378 ,p_eff_date => trunc(sysdate));
379 IF l_comp_status = 'ACHIEVED' then
380 l_NoOfCompAchieved := l_NoOfCompAchieved +1;
381 else
382 RETURN l_ret_val;
383 END if;
384 END loop;
385 END loop;
386 If l_NoOfCompAchieved = l_TotComps Then
387 l_ret_val := 'ACHIEVED';
388 End if;
389 Return l_ret_val;
390 End IsAllCompAchieved;
391 END PerCompStatus;
[Home] [Help]