1 PACKAGE BODY WIP_WS_SKILL_CHECK_PVT AS
2 /* $Header: wipwsscb.pls 120.0.12010000.3 2008/11/21 07:25:38 hvutukur 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
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 l_validate_skill := G_NO_SKILL_EMP_CLOCKIN;
199 for employees in get_clocked_employees loop
200 if employees.employee_id = p_emp_id then
201 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
202 exit;
203 else
204 l_validate_skill := validate_skill_for_operation(p_wip_entity_id => p_wip_entity_id,
205 p_organization_id => p_organization_id,
206 p_operation => p_operation,
207 p_emp_id => employees.employee_id);
208 exit when l_validate_skill = G_SKILL_VALIDATION_SUCCESS;
209 end if;
210 end loop;
211 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
212 l_validate_skill := G_NO_SKILL_EMP_CLOCKIN;
213 end if;
214 end if;
215 return l_validate_skill;
216 end validate_skill_for_move_ops;
217
218 /* Main Function for skill Validation for Clock-In. */
219 function validate_skill_for_clock_in(p_wip_entity_id in number,
220 p_op_seq_num in number,
221 p_emp_id in number)
222 return number
223 is
224 l_check_skill number;
225 l_org_id number;
226 l_validate_skill number := G_SKILL_VALIDATION_SUCCESS;
227 begin
228
229 select organization_id
230 into l_org_id
231 from wip_entities
232 where wip_entity_id = p_wip_entity_id
233 and rownum=1;
234
235 get_skill_parameters(l_org_id);
236 /* validate only if clock in is allowed only for skilled operators and
237 skill check is enabled for the operation*/
238 if G_PREF_CLOCK_VALUE = G_ALLOW_ONLY_SKILL_OPERATORS then
239
240 l_check_skill := get_operation_skill_check(p_wip_entity_id,p_op_seq_num);
241
242 if l_check_skill=G_SKILL_CHECK_ENABLED then
243 l_validate_skill := validate_skill_for_operation(p_wip_entity_id => p_wip_entity_id,
244 p_organization_id => l_org_id,
245 p_operation => p_op_seq_num,
246 p_emp_id => p_emp_id);
247 end if;
248 end if;
249 return l_validate_skill;
250 exception
251 when others then
252 return G_SKILL_VALIDATION_EXCEPTION;
253 end validate_skill_for_clock_in;
254
255 /* Main Function for skill Validation for Move Transaction. */
256 procedure validate_skill_for_move_txn(p_wip_entity_id in number,
257 p_organization_id in number,
258 p_from_op in number,
259 p_to_op in number,
260 p_from_step in number,
261 p_to_step in number,
262 p_emp_id in number,
263 l_validate_skill out nocopy number,
264 l_move_pref out nocopy varchar2,
265 l_certify_pref out nocopy varchar2,
266 l_err_msg out nocopy varchar2)
267 is
268
269 l_sql varchar2(4000);
270 l_add_where_clause varchar2(1000);
271 l_from_op number;
272 l_to_op number;
273 l_from_step number;
274 l_to_step number;
275 l_op_seq_num number;
276 l_check_skill number;
277 l_cursor integer;
278 l_sql_exec integer;
279 begin
280 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
281 get_skill_parameters(p_organization_id);
282 if G_PREF_MOVE_VALUE = G_ENABLE_MOVE_VALIDATION OR
283 G_PREF_CLOCK_VALUE in (G_ALLOW_ONLY_SKILL_OPERATORS,G_ALLOW_ALL_OPERATORS) then
284
285 if (p_from_op > p_to_op OR (p_from_op = p_to_op and p_from_step > p_to_step)) then
286 l_from_op := p_to_op;
287 l_to_op := p_from_op;
288 l_to_step := p_from_step;
289 l_from_step := p_to_step;
290 else
291 l_from_op := p_from_op;
292 l_to_op := p_to_op;
293 l_to_step := p_to_step;
294 l_from_step := p_from_step;
295 end if;
296
297 if l_from_step = WIP_CONSTANTS.TOMOVE then
298 l_add_where_clause := ' and operation_seq_num > :3 ';
299 else
300 l_add_where_clause := ' and operation_seq_num >= :3 ';
301 end if;
302
303 if l_to_step = WIP_CONSTANTS.QUEUE then
304 l_add_where_clause := l_add_where_clause || ' and operation_seq_num < :4 ';
305 else
306 l_add_where_clause := l_add_where_clause || ' and operation_seq_num <= :4 ';
307 end if;
308
309 l_sql := ' select operation_seq_num,nvl(check_skill,2) '||
310 ' from wip_operations '||
311 ' where organization_id = :1 '||
312 ' and wip_entity_id = :2 '||
313 l_add_where_clause;
314
315 l_cursor := dbms_sql.open_cursor;
316 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
317 dbms_sql.define_column(l_cursor, 1, l_op_seq_num);
318 dbms_sql.define_column(l_cursor, 2, l_check_skill);
319 dbms_sql.bind_variable(l_cursor, ':1', p_organization_id);
320 dbms_sql.bind_variable(l_cursor, ':2', p_wip_entity_id);
321 dbms_sql.bind_variable(l_cursor, ':3', l_from_op);
322 dbms_sql.bind_variable(l_cursor, ':4', l_to_op);
323 l_sql_exec := dbms_sql.execute(l_cursor);
324
325 loop
326 exit when dbms_sql.fetch_rows(l_cursor) = 0 OR l_validate_skill <> G_SKILL_VALIDATION_SUCCESS;
327 dbms_sql.column_value(l_cursor, 1, l_op_seq_num);
328 dbms_sql.column_value(l_cursor, 2, l_check_skill);
329 if l_check_skill=G_SKILL_CHECK_ENABLED then
330 l_validate_skill := validate_skill_for_move_ops(p_wip_entity_id => p_wip_entity_id,
331 p_organization_id => p_organization_id,
332 p_operation => l_op_seq_num,
333 p_emp_id => p_emp_id);
334 end if;
335
336 end loop;
337 dbms_sql.close_cursor(l_cursor);
338 end if;
339 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
340 if p_wip_entity_id is not null and p_emp_id is not null then
341 set_message_context(p_wip_entity_id,p_emp_id);
342 end if;
343 if l_validate_skill = G_COMPETENCE_CHECK_FAIL then
344 fnd_message.set_name('WIP','WIP_COMPETENCE_CHECK_FAIL');
345 fnd_message.set_token('EMP', G_EMPLOYEE);
346 elsif l_validate_skill = G_CERTIFY_CHECK_FAIL then
347 fnd_message.set_name('WIP','WIP_CERTIFY_CHECK_FAIL');
348 fnd_message.set_token('EMP', G_EMPLOYEE);
349 elsif l_validate_skill = G_QUALIFY_CHECK_FAIL then
350 fnd_message.set_name('WIP','WIP_QUALIFY_CHECK_FAIL');
351 fnd_message.set_token('EMP', G_EMPLOYEE);
352 elsif l_validate_skill = G_NO_SKILL_EMP_CLOCKIN then
353 fnd_message.set_name('WIP','WIP_NO_SKILL_EMP_CLOCKIN');
354 end if;
355 fnd_message.set_token('JOB', G_WIP_ENTITY_NAME);
356 fnd_message.set_token('OP', to_char(l_op_seq_num));
357 if l_validate_skill = G_INV_SKILL_CHECK_EMP then
358 fnd_message.set_name('WIP','WIP_SKILL_CHECK_EMP_NULL');
359 end if;
360 l_err_msg := fnd_message.get;
361 end if;
362 if G_PREF_MOVE_VALUE = G_ENABLE_MOVE_VALIDATION then
363 fnd_message.set_name('WIP','WIP_YES');
364 l_move_pref := fnd_message.get;
365 else
366 fnd_message.set_name('WIP','WIP_NO');
367 l_move_pref := fnd_message.get;
368 end if;
369 if G_PREF_CERTIFY_VALUE = G_ENABLE_CERTIFICATION_CHECK then
370 fnd_message.set_name('WIP','WIP_YES');
371 l_certify_pref := fnd_message.get;
372 else
373 fnd_message.set_name('WIP','WIP_NO');
374 l_certify_pref := fnd_message.get;
375 end if;
376 exception
377 when others then
378 l_validate_skill := G_SKILL_VALIDATION_EXCEPTION;
379 l_err_msg := 'Exception during Skill Validation'||sqlerrm(sqlcode);
380 end validate_skill_for_move_txn;
381
382 /* This function will be called to validate skill for Express Move.*/
383 procedure validate_skill_for_exp_move(p_wip_entity_id in number,
384 p_organization_id in number,
385 p_op_seq_num in number,
386 p_emp_id in number,
387 l_validate_skill out nocopy number,
388 l_err_msg out nocopy varchar2)
389 is
390 l_skill_check number;
391 begin
392 l_validate_skill := G_SKILL_VALIDATION_SUCCESS;
393 get_skill_parameters(p_organization_id);
394 l_skill_check := get_operation_skill_check(p_wip_entity_id => p_wip_entity_id,
395 p_op_seq_num => p_op_seq_num);
396 if l_skill_check=G_SKILL_CHECK_ENABLED then
397 l_validate_skill := validate_skill_for_move_ops(p_wip_entity_id => p_wip_entity_id,
398 p_organization_id => p_organization_id,
399 p_operation => p_op_seq_num,
400 p_emp_id => p_emp_id);
401 end if;
402 if l_validate_skill <> G_SKILL_VALIDATION_SUCCESS then
403 if p_wip_entity_id is not null and p_emp_id is not null then
404 set_message_context(p_wip_entity_id,p_emp_id);
405 end if;
406 if l_validate_skill = G_COMPETENCE_CHECK_FAIL then
407 fnd_message.set_name('WIP','WIP_COMPETENCE_CHECK_FAIL');
408 fnd_message.set_token('EMP', G_EMPLOYEE);
409 elsif l_validate_skill = G_CERTIFY_CHECK_FAIL then
410 fnd_message.set_name('WIP','WIP_CERTIFY_CHECK_FAIL');
411 fnd_message.set_token('EMP', G_EMPLOYEE);
412 elsif l_validate_skill = G_QUALIFY_CHECK_FAIL then
413 fnd_message.set_name('WIP','WIP_QUALIFY_CHECK_FAIL');
414 fnd_message.set_token('EMP', G_EMPLOYEE);
415 elsif l_validate_skill = G_NO_SKILL_EMP_CLOCKIN then
416 fnd_message.set_name('WIP','WIP_NO_SKILL_EMP_CLOCKIN');
417 end if;
418 fnd_message.set_token('JOB', G_WIP_ENTITY_NAME);
419 fnd_message.set_token('OP', to_char(p_op_seq_num));
420 if l_validate_skill = G_INV_SKILL_CHECK_EMP then
421 fnd_message.set_name('WIP','WIP_SKILL_CHECK_EMP_NULL');
422 end if;
423 l_err_msg := fnd_message.get;
424 end if;
425 exception
426 when others then
427 l_validate_skill := G_SKILL_VALIDATION_EXCEPTION;
428 l_err_msg := 'Exception during Skill Validation'||sqlerrm(sqlcode);
429 end validate_skill_for_exp_move;
430
431 END WIP_WS_SKILL_CHECK_PVT;
[Home] [Help]