1 PACKAGE BODY WIP_WS_SKILL_CHECK_PVT AS
2 /* $Header: wipwsscb.pls 120.0.12010000.6 2010/01/29 13:33:38 sisankar noship $ */
3
4 /* This procedure sets package variables for preference parameters */
5 procedure get_skill_parameters(p_organization_id in number)
6 is
7 cursor get_preferences is
8 select attribute_name,
9 attribute_value_code
10 from wip_preference_values
11 where preference_id = 41
12 and level_id = 1
13 and attribute_name <> G_PREF_ORG_ATTRIBUTE
14 and sequence_number = (select sequence_number
15 from wip_preference_values
16 where preference_id = 41
17 and level_id = 1
18 and attribute_name = G_PREF_ORG_ATTRIBUTE
19 and attribute_value_code = to_char(p_organization_id))
20 order by 1 desc;
21 begin
22 G_PREF_CLOCK_VALUE := G_DISABLE_CLOCK_VALIDATION;
23 G_PREF_MOVE_VALUE := G_DISABLE_MOVE_VALIDATION;
24 G_PREF_CERTIFY_VALUE := G_DISABLE_CERTIFICATION_CHECK;
25 for preferences in get_preferences loop
26 if preferences.attribute_name = G_PREF_CLOCK_ATTRIBUTE then
27 G_PREF_CLOCK_VALUE := preferences.attribute_value_code;
28 elsif preferences.attribute_name =G_PREF_MOVE_ATTRIBUTE then
29 G_PREF_MOVE_VALUE := preferences.attribute_value_code;
30 elsif preferences.attribute_name =G_PREF_CERTIFY_ATTRIBUTE then
31 G_PREF_CERTIFY_VALUE := preferences.attribute_value_code;
32 end if;
33 end loop;
34 end get_skill_parameters;
35
36 procedure set_message_context(p_wip_entity_id in Number,
37 p_emp_id in Number)
38 is
39 begin
40 select wip_entity_name
41 into G_WIP_ENTITY_NAME
42 from wip_entities
43 where wip_entity_id = p_wip_entity_id;
44
45 select full_name
46 into G_EMPLOYEE
47 from per_all_people_f
48 where person_id = p_emp_id
49 and sysdate between effective_start_date and nvl(effective_end_date,sysdate+1);
50 exception
51 when others then
52 null;
53 end set_message_context;
54
55 function get_operation_skill_check(p_wip_entity_id in number,
56 p_op_seq_num in number)
57 return number
58 is
59 l_check_skill number;
60 begin
61 select nvl(check_skill,2)
62 into l_check_skill
63 from wip_operations
64 where wip_entity_id = p_wip_entity_id
65 and operation_seq_num = p_op_seq_num;
66
67 return l_check_skill;
68 exception
69 when others then
70 return 2;
71 end get_operation_skill_check;
72
73 /* This function will be called to validate employee skill for a job operation.
74 We are not passing Clock-In time or Move transaction date to these methods to validate
75 effectivity of competence/Certification since MES Move transactions are stamped with sysdate.
76 We need to pass additional date parameter when we allow updating transaction dates in MES. */
77
78 function validate_skill_for_operation(p_wip_entity_id in number,
79 p_organization_id in number,
80 p_operation in number,
81 p_emp_id in number)
82 return Number
83 is
84
85 cursor get_operation_competence is
86 select competence_id,rating_level_id,qualification_type_id
87 from wip_operation_competencies
88 where wip_entity_id = p_wip_entity_id
89 and operation_seq_num = p_operation;
90
91 l_counter Number;
92 l_validate_skill number := G_SKILL_VALIDATION_SUCCESS;
93 l_err_msg varchar2(2000) := null;
94 l_certify_date date;
95 l_next_review_date date;
96 l_comp_certify_required varchar2(1);
97
98 type job_op_competence_rec is record (
99 competency_id wip_operation_competencies.competence_id%type,
100 rating_level_id wip_operation_competencies.rating_level_id%type,
101 qualification_type_id wip_operation_competencies.qualification_type_id%type);
102
103 type t_job_op_competence is table of job_op_competence_rec index by binary_integer;
104 v_job_op_competence t_job_op_competence;
105
106 begin
107
108 if p_emp_id is null then
109 return G_INV_SKILL_CHECK_EMP;
110 end if;
111
112 open get_operation_competence;
113 fetch get_operation_competence bulk collect into v_job_op_competence;
114 close get_operation_competence;
115
116 l_counter := v_job_op_competence.first;
117 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
118 while l_counter is not null loop
119 if v_job_op_competence(l_counter).competency_id is not null then
120 begin
121 SELECT certification_date, next_certification_date
122 into l_certify_date,l_next_review_date
123 FROM per_competence_elements
124 WHERE type = 'PERSONAL'
125 AND person_id = p_emp_id
126 AND trunc(sysdate) between effective_date_from and NVL(effective_date_to,trunc(sysdate))
127 and competence_id = v_job_op_competence(l_counter).competency_id
128 and nvl(proficiency_level_id,-1) = nvl(v_job_op_competence(l_counter).rating_level_id ,nvl(proficiency_level_id,-1));
129
130 exception
131 when others then
132 l_validate_skill := G_COMPETENCE_CHECK_FAIL;
133 end;
134 if l_validate_skill=G_SKILL_VALIDATION_SUCCESS and
135 G_PREF_CERTIFY_VALUE = G_ENABLE_CERTIFICATION_CHECK then
136
137 select nvl(certification_required,'N')
138 into l_comp_certify_required
139 FROM per_competences
140 WHERE competence_id = v_job_op_competence(l_counter).competency_id;
141
142 if l_comp_certify_required='Y' and
143 (l_certify_date is null or
144 l_certify_date > sysdate or
145 nvl(l_next_review_date,sysdate+1) < sysdate) then
146 l_validate_skill := G_CERTIFY_CHECK_FAIL;
147 end if;
148 end if;
149 end if;
150 if l_validate_skill= G_SKILL_VALIDATION_SUCCESS and
151 v_job_op_competence(l_counter).qualification_type_id is not null then
152 begin
153 select 1
154 into l_validate_skill
155 from dual
156 where exists (select 'x'
157 from per_qualifications
158 where person_id = p_emp_id
159 and qualification_type_id = v_job_op_competence(l_counter).qualification_type_id);
160 exception
161 when others then
162 l_validate_skill := G_QUALIFY_CHECK_FAIL;
163 end;
164 end if;
165 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
166 exit;
167 end if;
168 l_counter := v_job_op_competence.next(l_counter);
169 end loop;
170 return l_validate_skill;
171 end validate_skill_for_operation;
172
173 /* Main Function for skill Validation for a Op in Move Transaction. */
174 function validate_skill_for_move_ops(p_wip_entity_id in number,
175 p_organization_id in number,
176 p_operation in number,
177 p_emp_id in number)
178 return number
179 is
180 l_validate_skill Number := G_SKILL_VALIDATION_SUCCESS;
181 l_op_competence_exist Number := 0;
182 cursor get_clocked_employees is
183 select distinct employee_id
184 from wip_resource_actual_times
185 where organization_id = p_organization_id
186 and wip_entity_id = p_wip_entity_id
187 and operation_seq_num = p_operation;
188
189 begin
190 if G_PREF_MOVE_VALUE = G_ENABLE_MOVE_VALIDATION then
191 l_validate_skill := validate_skill_for_operation(p_wip_entity_id => p_wip_entity_id,
192 p_organization_id => p_organization_id,
193 p_operation => p_operation,
194 p_emp_id => p_emp_id);
195 end if;
196 if G_PREF_CLOCK_VALUE in (G_ALLOW_ONLY_SKILL_OPERATORS,G_ALLOW_ALL_OPERATORS) AND
197 l_validate_skill= G_SKILL_VALIDATION_SUCCESS then
198 begin
199 select 1 into l_op_competence_exist
200 from dual
201 where exists (select 1
202 from wip_operation_competencies
203 where wip_entity_id = p_wip_entity_id
204 and operation_seq_num = p_operation );
205 exception
206 when others then
207 l_op_competence_exist := 0;
208 end;
209 if l_op_competence_exist = 0 then
210 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
211 else
212 l_validate_skill := G_NO_SKILL_EMP_CLOCKIN;
213 end if;
214 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
215 for employees in get_clocked_employees loop
216 if employees.employee_id = p_emp_id then
217 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
218 exit;
219 else
220 l_validate_skill := validate_skill_for_operation(p_wip_entity_id => p_wip_entity_id,
221 p_organization_id => p_organization_id,
222 p_operation => p_operation,
223 p_emp_id => employees.employee_id);
224 exit when l_validate_skill = G_SKILL_VALIDATION_SUCCESS;
225 end if;
226 end loop;
227 end if;
228 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
229 l_validate_skill := G_NO_SKILL_EMP_CLOCKIN;
230 end if;
231 end if;
232 return l_validate_skill;
233 end validate_skill_for_move_ops;
234
235 /* Main Function for skill Validation for Clock-In. */
236 function validate_skill_for_clock_in(p_wip_entity_id in number,
237 p_op_seq_num in number,
238 p_emp_id in number)
239 return number
240 is
241 l_check_skill number;
242 l_org_id number;
243 l_validate_skill number := G_SKILL_VALIDATION_SUCCESS;
244 begin
245
246 select organization_id
247 into l_org_id
248 from wip_entities
249 where wip_entity_id = p_wip_entity_id
250 and rownum=1;
251
252 get_skill_parameters(l_org_id);
253 /* validate only if clock in is allowed only for skilled operators and
254 skill check is enabled for the operation*/
255 if G_PREF_CLOCK_VALUE = G_ALLOW_ONLY_SKILL_OPERATORS then
256
257 l_check_skill := get_operation_skill_check(p_wip_entity_id,p_op_seq_num);
258
259 if l_check_skill=G_SKILL_CHECK_ENABLED then
260 l_validate_skill := validate_skill_for_operation(p_wip_entity_id => p_wip_entity_id,
261 p_organization_id => l_org_id,
262 p_operation => p_op_seq_num,
263 p_emp_id => p_emp_id);
264 end if;
265 end if;
266 return l_validate_skill;
267 exception
268 when others then
269 return G_SKILL_VALIDATION_EXCEPTION;
270 end validate_skill_for_clock_in;
271
272 /* Main Function for skill Validation for Move Transaction. */
273 procedure validate_skill_for_move_txn(p_wip_entity_id in number,
274 p_organization_id in number,
275 p_from_op in number,
276 p_to_op in number,
277 p_from_step in number,
278 p_to_step in number,
279 p_emp_id in number,
280 l_validate_skill out nocopy number,
281 l_move_pref out nocopy varchar2,
282 l_certify_pref out nocopy varchar2,
283 l_err_msg out nocopy varchar2)
284 is
285
286 l_sql varchar2(4000);
287 l_add_where_clause varchar2(1000);
288 l_from_op number;
289 l_to_op number;
290 l_from_step number;
291 l_to_step number;
292 l_op_seq_num number;
293 l_check_skill number;
294 l_cursor integer;
295 l_sql_exec integer;
296 begin
297 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
298 get_skill_parameters(p_organization_id);
299 if G_PREF_MOVE_VALUE = G_ENABLE_MOVE_VALIDATION OR
300 G_PREF_CLOCK_VALUE in (G_ALLOW_ONLY_SKILL_OPERATORS,G_ALLOW_ALL_OPERATORS) then
301
302 if (p_from_op > p_to_op OR (p_from_op = p_to_op and p_from_step > p_to_step)) then
303 l_from_op := p_to_op;
304 l_to_op := p_from_op;
305 l_to_step := p_from_step;
306 l_from_step := p_to_step;
307 else
308 l_from_op := p_from_op;
309 l_to_op := p_to_op;
310 l_to_step := p_to_step;
311 l_from_step := p_from_step;
312 end if;
313
314 if l_from_step = WIP_CONSTANTS.TOMOVE then
315 l_add_where_clause := ' and operation_seq_num > :3 ';
316 else
317 l_add_where_clause := ' and operation_seq_num >= :3 ';
318 end if;
319
320 if l_to_step = WIP_CONSTANTS.QUEUE then
321 l_add_where_clause := l_add_where_clause || ' and operation_seq_num < :4 ';
322 else
323 l_add_where_clause := l_add_where_clause || ' and operation_seq_num <= :4 ';
324 end if;
325
326 l_sql := ' select operation_seq_num,nvl(check_skill,2) '||
327 ' from wip_operations '||
328 ' where organization_id = :1 '||
329 ' and wip_entity_id = :2 '||
330 l_add_where_clause;
331
332 l_cursor := dbms_sql.open_cursor;
333 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
334 dbms_sql.define_column(l_cursor, 1, l_op_seq_num);
335 dbms_sql.define_column(l_cursor, 2, l_check_skill);
336 dbms_sql.bind_variable(l_cursor, ':1', p_organization_id);
337 dbms_sql.bind_variable(l_cursor, ':2', p_wip_entity_id);
338 dbms_sql.bind_variable(l_cursor, ':3', l_from_op);
339 dbms_sql.bind_variable(l_cursor, ':4', l_to_op);
340 l_sql_exec := dbms_sql.execute(l_cursor);
341
342 loop
343 exit when dbms_sql.fetch_rows(l_cursor) = 0 OR l_validate_skill <> G_SKILL_VALIDATION_SUCCESS;
344 dbms_sql.column_value(l_cursor, 1, l_op_seq_num);
345 dbms_sql.column_value(l_cursor, 2, l_check_skill);
346 if l_check_skill=G_SKILL_CHECK_ENABLED then
347 l_validate_skill := validate_skill_for_move_ops(p_wip_entity_id => p_wip_entity_id,
348 p_organization_id => p_organization_id,
349 p_operation => l_op_seq_num,
350 p_emp_id => p_emp_id);
351 end if;
352
353 end loop;
354 dbms_sql.close_cursor(l_cursor);
355 end if;
356 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
357 if p_wip_entity_id is not null and p_emp_id is not null then
358 set_message_context(p_wip_entity_id,p_emp_id);
359 end if;
360 if l_validate_skill = G_COMPETENCE_CHECK_FAIL then
361 fnd_message.set_name('WIP','WIP_COMPETENCE_CHECK_FAIL');
362 fnd_message.set_token('EMP', G_EMPLOYEE);
363 elsif l_validate_skill = G_CERTIFY_CHECK_FAIL then
364 fnd_message.set_name('WIP','WIP_CERTIFY_CHECK_FAIL');
365 fnd_message.set_token('EMP', G_EMPLOYEE);
366 elsif l_validate_skill = G_QUALIFY_CHECK_FAIL then
367 fnd_message.set_name('WIP','WIP_QUALIFY_CHECK_FAIL');
368 fnd_message.set_token('EMP', G_EMPLOYEE);
369 elsif l_validate_skill = G_NO_SKILL_EMP_CLOCKIN then
370 fnd_message.set_name('WIP','WIP_NO_SKILL_EMP_CLOCKIN');
371 end if;
372 fnd_message.set_token('JOB', G_WIP_ENTITY_NAME);
373 fnd_message.set_token('OP', to_char(l_op_seq_num));
374 if l_validate_skill = G_INV_SKILL_CHECK_EMP then
375 fnd_message.set_name('WIP','WIP_SKILL_CHECK_EMP_NULL');
376 end if;
377 l_err_msg := fnd_message.get;
378 end if;
379 if G_PREF_MOVE_VALUE = G_ENABLE_MOVE_VALIDATION then
380 fnd_message.set_name('WIP','WIP_YES');
381 l_move_pref := fnd_message.get;
382 else
383 fnd_message.set_name('WIP','WIP_NO');
384 l_move_pref := fnd_message.get;
385 end if;
386 if G_PREF_CERTIFY_VALUE = G_ENABLE_CERTIFICATION_CHECK then
387 fnd_message.set_name('WIP','WIP_YES');
388 l_certify_pref := fnd_message.get;
389 else
390 fnd_message.set_name('WIP','WIP_NO');
391 l_certify_pref := fnd_message.get;
392 end if;
393 exception
394 when others then
395 l_validate_skill := G_SKILL_VALIDATION_EXCEPTION;
396 l_err_msg := 'Exception during Skill Validation'||sqlerrm(sqlcode);
397 end validate_skill_for_move_txn;
398
399 /* This function will be called to validate skill for Express Move.*/
400 procedure validate_skill_for_exp_move(p_wip_entity_id in number,
401 p_organization_id in number,
402 p_op_seq_num in number,
403 p_emp_id in number,
404 l_validate_skill out nocopy number,
405 l_err_msg out nocopy varchar2)
406 is
407 l_skill_check number;
408 begin
409 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
410 get_skill_parameters(p_organization_id);
411 l_skill_check := get_operation_skill_check(p_wip_entity_id => p_wip_entity_id,
412 p_op_seq_num => p_op_seq_num);
413 if l_skill_check=G_SKILL_CHECK_ENABLED then
414 l_validate_skill := validate_skill_for_move_ops(p_wip_entity_id => p_wip_entity_id,
415 p_organization_id => p_organization_id,
416 p_operation => p_op_seq_num,
417 p_emp_id => p_emp_id);
418 end if;
419 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
420 if p_wip_entity_id is not null and p_emp_id is not null then
421 set_message_context(p_wip_entity_id,p_emp_id);
422 end if;
423 if l_validate_skill = G_COMPETENCE_CHECK_FAIL then
424 fnd_message.set_name('WIP','WIP_COMPETENCE_CHECK_FAIL');
425 fnd_message.set_token('EMP', G_EMPLOYEE);
426 elsif l_validate_skill = G_CERTIFY_CHECK_FAIL then
427 fnd_message.set_name('WIP','WIP_CERTIFY_CHECK_FAIL');
428 fnd_message.set_token('EMP', G_EMPLOYEE);
429 elsif l_validate_skill = G_QUALIFY_CHECK_FAIL then
430 fnd_message.set_name('WIP','WIP_QUALIFY_CHECK_FAIL');
431 fnd_message.set_token('EMP', G_EMPLOYEE);
432 elsif l_validate_skill = G_NO_SKILL_EMP_CLOCKIN then
433 fnd_message.set_name('WIP','WIP_NO_SKILL_EMP_CLOCKIN');
434 end if;
435 fnd_message.set_token('JOB', G_WIP_ENTITY_NAME);
436 fnd_message.set_token('OP', to_char(p_op_seq_num));
437 if l_validate_skill = G_INV_SKILL_CHECK_EMP then
438 fnd_message.set_name('WIP','WIP_SKILL_CHECK_EMP_NULL');
439 end if;
440 l_err_msg := fnd_message.get;
441 end if;
442 exception
443 when others then
444 l_validate_skill := G_SKILL_VALIDATION_EXCEPTION;
445 l_err_msg := 'Exception during Skill Validation'||sqlerrm(sqlcode);
446 end validate_skill_for_exp_move;
447
448 END WIP_WS_SKILL_CHECK_PVT;
[Home] [Help]