[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_TIME_ENTRY
Source
1 PACKAGE BODY WIP_WS_TIME_ENTRY AS
2 /* $Header: wipwsteb.pls 120.48.12020000.13 2013/03/22 09:49:35 sisankar ship $ */
3
4 G_WIP_WS_PREF_CHARGE_RESOURCE CONSTANT NUMBER := 5;
5 G_WIP_WS_PREF_TIME_ENTRY_MODE CONSTANT NUMBER := 26;
6 G_BOM_AUTOCHARGE_TYPE_MANUAL CONSTANT NUMBER := 2;
7
8 G_RES_CHG_FAILED Number :=wip_constants.no;
9
10 -- Insert a record into the wip_resource_actual_times table.
11 PROCEDURE record_insert(
12 p_time_entry_id in number,
13 p_organization_id in number,
14 p_wip_entity_id in number,
15 p_operation_seq_num in number,
16 p_resource_id in number,
17 p_resource_seq_num in number,
18 p_instance_id in number,
19 p_serial_number in varchar2,
20 p_last_update_date in date,
21 p_last_updated_by in number,
22 p_creation_date in date,
23 p_created_by in number,
24 p_last_update_login in number,
25 p_object_version_num in number,
26 p_time_entry_mode in number,
27 p_cost_flag in varchar2,
28 p_add_to_rtg in varchar2,
29 p_status_type in number,
30 p_start_date in date,
31 p_end_date in date,
32 p_projected_completion_date in date,
33 p_duration in number,
34 p_uom_code in varchar2,
35 p_employee_id in number,
36 x_time_entry_id out NOCOPY number,
37 x_return_status out NOCOPY varchar2)
38 IS
39 l_time_entry_id number;
40 l_object_version_num number;
41 l_status_type number;
42 l_process_status number;
43 l_time_entry_mode number;
44 l_uom_code varchar2(3);
45 l_time_uom_flag varchar2(1);
46 l_duration number;
47 l_projected_completion_date date;
48 l_organization_code varchar2(3);
49 l_resource_code varchar2(10);
50 l_wip_entity_name varchar2(240);
51 l_return_status varchar2(10);
52 l_retStatus varchar2(1);
53
54 l_resource_type number;
55 l_employee_id number;
56 l_employee_num varchar2(30);
57 l_params wip_logger.param_tbl_t;
58 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
59
60 BEGIN
61 if(l_logLevel <= wip_constants.trace_logging) then
62 l_params(1).paramName := 'p_time_entry_id';
63 l_params(1).paramValue := p_time_entry_id;
64 l_params(2).paramName := 'p_organization_id';
65 l_params(2).paramValue := p_organization_id;
66 l_params(3).paramName := 'p_wip_entity_id';
67 l_params(3).paramValue := p_wip_entity_id;
68 l_params(4).paramName := 'p_operation_seq_num';
69 l_params(4).paramValue := p_operation_seq_num;
70 l_params(5).paramName := 'p_resource_id';
71 l_params(5).paramValue := p_resource_id;
72 l_params(6).paramName := 'p_resource_seq_num';
73 l_params(6).paramValue := p_resource_seq_num;
74 l_params(7).paramName := 'p_instance_id';
75 l_params(7).paramValue := p_instance_id;
76 l_params(8).paramName := 'p_serial_number';
77 l_params(8).paramValue := p_serial_number;
78 l_params(9).paramName := 'p_last_update_date';
79 l_params(9).paramValue := p_last_update_date;
80 l_params(10).paramName := 'p_last_updated_by';
81 l_params(10).paramValue := p_last_updated_by;
82 l_params(11).paramName := 'p_creation_date';
83 l_params(11).paramValue := p_creation_date;
84 l_params(12).paramName := 'p_created_by';
85 l_params(12).paramValue := p_created_by;
86 l_params(13).paramName := 'p_last_update_login';
87 l_params(13).paramValue := p_last_update_login;
88 l_params(14).paramName := 'p_object_version_num';
89 l_params(14).paramValue := p_object_version_num;
90 l_params(15).paramName := 'p_time_entry_mode';
91 l_params(15).paramValue := p_time_entry_mode;
92 l_params(16).paramName := 'p_cost_flag';
93 l_params(16).paramValue := p_cost_flag;
94 l_params(17).paramName := 'p_add_to_rtg';
95 l_params(17).paramValue := p_add_to_rtg;
96 l_params(18).paramName := 'p_status_type';
97 l_params(18).paramValue := p_status_type;
98 l_params(19).paramName := 'p_start_date';
99 l_params(19).paramValue := p_start_date;
100 l_params(20).paramName := 'p_end_date';
101 l_params(20).paramValue := p_end_date;
102 l_params(21).paramName := 'p_projected_completion_date';
103 l_params(21).paramValue := p_projected_completion_date;
104 l_params(22).paramName := 'p_duration';
105 l_params(22).paramValue := p_duration;
106 l_params(23).paramName := 'p_uom_code';
107 l_params(23).paramValue := p_uom_code;
108 l_params(24).paramName := 'p_employee_id';
109 l_params(24).paramValue := p_employee_id;
110
111 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_insert',
112 p_params => l_params,
113 x_returnStatus => x_return_status);
114 if(x_return_status <> fnd_api.g_ret_sts_success) then
115 raise fnd_api.g_exc_unexpected_error;
116 end if;
117 end if;
118 l_status_type := 1; --pending
119 l_process_status := 2; --inserted
120 l_object_version_num := 1; --new record
121 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
122 l_return_status := 'U';
123
124 if p_time_entry_id is null then
125 select WIP_RESOURCE_ACTUAL_TIMES_S.nextval into l_time_entry_id from dual;
126 else
127 l_time_entry_id := p_time_entry_id;
128 end if;
129
130 if p_time_entry_mode is null then
131 l_time_entry_mode := get_time_entry_mode(p_wip_entity_id => p_wip_entity_id,
132 p_operation_seq_num => p_operation_seq_num);
133 else
134 l_time_entry_mode := p_time_entry_mode;
135 end if;
136
137 l_time_uom_flag := is_time_uom(p_uom_code);
138
139 if(l_logLevel <= wip_constants.full_logging) then
140 wip_logger.log('l_time_uom_flag: ' || l_time_uom_flag, l_return_status);
141 end if;
142 if( p_start_date is null and p_duration is null) then
143 /* skip, consider as empty record */
144 null;
145 elsif (l_time_uom_flag = 'Y' and p_start_date is not null) then
146
147 if (p_start_date is not null and p_end_date is not null and p_duration is null) then
148 l_duration := (p_end_date - p_start_date)*24;
149 else
150 l_duration := p_duration;
151 end if;
152
153 if l_duration is not null and l_uom_code <> p_uom_code then
154 if(l_logLevel <= wip_constants.full_logging) then
155 wip_logger.log('calling inv_convert.inv_um_convert', l_return_status);
156 end if;
157 l_duration := inv_convert.inv_um_convert(item_id => -1,
158 precision => 38,
159 from_quantity => l_duration,
160 from_unit => p_uom_code,
161 to_unit => l_uom_code,
162 from_name => null,
163 to_name => null);
164 if(l_logLevel <= wip_constants.full_logging) then
165 wip_logger.log('Value returned inv_convert.inv_um_convert: ' || l_duration, l_return_status);
166 end if;
167 end if;
168
169 if p_projected_completion_date is null then
170 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
171 p_wip_entity_id => p_wip_entity_id,
172 p_op_seq_num => p_operation_seq_num,
173 p_resource_seq_num => p_resource_seq_num,
174 p_resource_id => p_resource_id,
175 p_instance_id => p_instance_id,
176 p_start_date => p_start_date);
177 else
178 l_projected_completion_date := p_projected_completion_date;
179 end if;
180
181 if(l_logLevel <= wip_constants.full_logging) then
182 wip_logger.log('Projected Completion Date: ' || l_projected_completion_date, l_return_status);
183 wip_logger.log('Inserting into wip_resource_actual_times', l_return_status);
184 end if;
185 insert into wip_resource_actual_times
186 (time_entry_id,
187 organization_id,
188 wip_entity_id,
189 operation_seq_num,
190 resource_id,
191 resource_seq_num,
192 instance_id,
193 serial_number,
194 created_by,
195 creation_date,
196 last_updated_by,
197 last_update_date,
198 last_update_login,
199 object_version_number,
200 time_entry_mode,
201 cost_flag,
202 add_to_rtg,
203 status_type,
204 start_date,
205 end_date,
206 projected_completion_date,
207 duration,
208 uom_code,
209 employee_id,
210 process_status)
211 values
212 (l_time_entry_id,
213 p_organization_id,
214 p_wip_entity_id,
215 p_operation_seq_num,
216 p_resource_id,
217 p_resource_seq_num,
218 p_instance_id,
219 p_serial_number,
220 fnd_global.user_id,
221 sysdate,
222 fnd_global.user_id,
223 sysdate,
224 fnd_global.login_id,
225 l_object_version_num,
226 l_time_entry_mode,
227 p_cost_flag,
228 p_add_to_rtg,
229 l_status_type,
230 p_start_date,
231 p_end_date,
232 l_projected_completion_date,
233 l_duration,
234 l_uom_code,
235 p_employee_id,
236 l_process_status);
237
238 if(l_logLevel <= wip_constants.full_logging) then
239 wip_logger.log('Calling update_actual_start_dates', l_return_status);
240 end if;
241 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
242 p_operation_seq_num => p_operation_seq_num,
243 p_resource_seq_num => p_resource_seq_num);
244
245 if p_start_date is not null then
246
247 update_proj_completion_dates(p_organization_id => p_organization_id,
248 p_wip_entity_id => p_wip_entity_id,
249 p_operation_seq_num => p_operation_seq_num,
250 p_resource_seq_num => p_resource_seq_num,
251 p_resource_id => p_resource_id,
252 p_start_date => p_start_date);
253 end if;
254
255 l_return_status := 'S';
256
257 else
258 if(l_logLevel <= wip_constants.full_logging) then
259 wip_logger.log('Calling derive_insert_wcti', l_return_status);
260 end if;
261 /*Add for Bug 10097774 */
262 derive_insert_wcti(p_organization_id => p_organization_id,
263 p_wip_entity_id => p_wip_entity_id,
264 p_operation_seq_num => p_operation_seq_num,
265 p_resource_seq_num => p_resource_seq_num,
266 p_resource_id => p_resource_id,
267 p_entry_id => null,
268 p_employee_id => p_employee_id,
269 p_duration => p_duration,
270 p_txn_uom => p_uom_code,
271 p_hour_uom => l_uom_code,
272 x_return_status =>l_return_status);
273
274 end if;
275
276 x_time_entry_id := l_time_entry_id;
277 x_return_status := l_return_status;
278 if (l_logLevel <= wip_constants.trace_logging) then
279 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_insert',
280 p_procReturnStatus => x_return_status,
281 p_msg => 'normal completion',
282 x_returnStatus => l_return_status);
283 end if;
284 END record_insert;
285
286 -- Update a record in the wip_resource_actual_times table.
287 PROCEDURE record_update(
288 p_time_entry_id in number,
289 p_organization_id in number,
290 p_wip_entity_id in number,
291 p_operation_seq_num in number,
292 p_resource_id in number,
293 p_resource_seq_num in number,
294 p_instance_id in number,
295 p_serial_number in varchar2,
296 p_last_update_date in date,
297 p_last_updated_by in number,
298 p_creation_date in date,
299 p_created_by in number,
300 p_last_update_login in number,
301 p_object_version_num in number,
302 p_time_entry_mode in number,
303 p_cost_flag in varchar2,
304 p_add_to_rtg in varchar2,
305 p_status_type in number,
306 p_start_date in date,
307 p_end_date in date,
308 p_projected_completion_date in date,
309 p_duration in number,
310 p_uom_code in varchar2,
311 p_employee_id in number,
312 x_return_status out NOCOPY varchar2)
313 IS
314 l_process_status number;
315 l_uom_code varchar2(3);
316 l_duration number;
317 l_object_version_num number;
318 l_start_date date;
319 l_time_uom_flag varchar2(1);
320 l_projected_completion_date date;
321 l_return_status varchar2(10);
322 l_params wip_logger.param_tbl_t;
323 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
324 BEGIN
325 if(l_logLevel <= wip_constants.trace_logging) then
326 l_params(1).paramName := 'p_time_entry_id';
327 l_params(1).paramValue := p_time_entry_id;
328 l_params(2).paramName := 'p_organization_id';
329 l_params(2).paramValue := p_organization_id;
330 l_params(3).paramName := 'p_wip_entity_id';
331 l_params(3).paramValue := p_wip_entity_id;
332 l_params(4).paramName := 'p_operation_seq_num';
333 l_params(4).paramValue := p_operation_seq_num;
334 l_params(5).paramName := 'p_resource_id';
335 l_params(5).paramValue := p_resource_id;
336 l_params(6).paramName := 'p_resource_seq_num';
337 l_params(6).paramValue := p_resource_seq_num;
338 l_params(7).paramName := 'p_instance_id';
339 l_params(7).paramValue := p_instance_id;
340 l_params(8).paramName := 'p_serial_number';
341 l_params(8).paramValue := p_serial_number;
342 l_params(9).paramName := 'p_last_update_date';
343 l_params(9).paramValue := p_last_update_date;
344 l_params(10).paramName := 'p_last_updated_by';
345 l_params(10).paramValue := p_last_updated_by;
346 l_params(11).paramName := 'p_creation_date';
347 l_params(11).paramValue := p_creation_date;
348 l_params(12).paramName := 'p_created_by';
349 l_params(12).paramValue := p_created_by;
350 l_params(13).paramName := 'p_last_update_login';
351 l_params(13).paramValue := p_last_update_login;
352 l_params(14).paramName := 'p_object_version_num';
353 l_params(14).paramValue := p_object_version_num;
354 l_params(15).paramName := 'p_time_entry_mode';
355 l_params(15).paramValue := p_time_entry_mode;
356 l_params(16).paramName := 'p_cost_flag';
357 l_params(16).paramValue := p_cost_flag;
358 l_params(17).paramName := 'p_add_to_rtg';
359 l_params(17).paramValue := p_add_to_rtg;
360 l_params(18).paramName := 'p_status_type';
361 l_params(18).paramValue := p_status_type;
362 l_params(19).paramName := 'p_start_date';
363 l_params(19).paramValue := p_start_date;
364 l_params(20).paramName := 'p_end_date';
365 l_params(20).paramValue := p_end_date;
366 l_params(21).paramName := 'p_projected_completion_date';
367 l_params(21).paramValue := p_projected_completion_date;
368 l_params(22).paramName := 'p_duration';
369 l_params(22).paramValue := p_duration;
370 l_params(23).paramName := 'p_uom_code';
371 l_params(23).paramValue := p_uom_code;
372 l_params(24).paramName := 'p_employee_id';
373 l_params(24).paramValue := p_employee_id;
374
375 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_update',
376 p_params => l_params,
377 x_returnStatus => x_return_status);
378 if(x_return_status <> fnd_api.g_ret_sts_success) then
379 raise fnd_api.g_exc_unexpected_error;
380 end if;
381 end if;
382 l_object_version_num := p_object_version_num;
383 l_start_date := p_start_date;
384 l_process_status := 3; --updated
385 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
386 l_return_status := 'U';
387
388 l_time_uom_flag := is_time_uom(p_uom_code);
389
390 if l_time_uom_flag = 'Y' then
391 if (p_duration is null and p_start_date is not null and p_end_date is not null) then
392 l_duration := (p_end_date - p_start_date)*24;
393 else
394 l_duration := p_duration;
395 end if;
396 if(l_logLevel <= wip_constants.full_logging) then
397 wip_logger.log('Before inv_convert, l_duration: ' || l_duration, l_return_status);
398 end if;
399 if l_duration is not null and l_uom_code <> p_uom_code then
400 l_duration := inv_convert.inv_um_convert(item_id => -1,
401 precision => 38,
402 from_quantity => l_duration,
403 from_unit => p_uom_code,
404 to_unit => l_uom_code,
405 from_name => null,
406 to_name => null);
407 end if;
408 if(l_logLevel <= wip_constants.full_logging) then
409 wip_logger.log('After inv_convert, l_duration: ' || l_duration, l_return_status);
410 end if;
411
412 select object_version_number, start_date into l_object_version_num, l_start_date
413 from wip_resource_actual_times where time_entry_id = p_time_entry_id;
414
415 if(l_logLevel <= wip_constants.full_logging) then
416 wip_logger.log('l_object_version_num: ' || l_object_version_num, l_return_status);
417 wip_logger.log('l_start_date: ' || l_start_date, l_return_status);
418 end if;
419 if l_start_date <> p_start_date then
420 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
421 p_wip_entity_id => p_wip_entity_id,
422 p_op_seq_num => p_operation_seq_num,
423 p_resource_seq_num => p_resource_seq_num,
424 p_resource_id => p_resource_id,
425 p_instance_id => p_instance_id,
426 p_start_date => p_start_date);
427 else
428 l_projected_completion_date := p_projected_completion_date;
429 end if;
430
431 if l_object_version_num = p_object_version_num then
432 if(l_logLevel <= wip_constants.full_logging) then
433 wip_logger.log('updating wip_resource_actual_times', l_return_status);
434 end if;
435 update wip_resource_actual_times set
436 organization_id = p_organization_id,
437 wip_entity_id = p_wip_entity_id,
438 operation_seq_num = p_operation_seq_num,
439 resource_id = p_resource_id,
440 resource_seq_num = p_resource_seq_num,
441 instance_id = p_instance_id,
442 serial_number = p_serial_number,
443 creation_date = p_creation_date,
444 created_by = p_created_by,
445 time_entry_mode = p_time_entry_mode,
446 cost_flag = p_cost_flag,
447 add_to_rtg = p_add_to_rtg,
448 status_type = p_status_type,
449 start_date = p_start_date,
450 end_date = p_end_date,
451 projected_completion_date = l_projected_completion_date,
452 duration = l_duration,
453 uom_code = l_uom_code,
454 employee_id = p_employee_id,
455 process_status = l_process_status,
456 object_version_number = p_object_version_num + 1,
457 last_update_date = sysdate,
458 last_updated_by = fnd_global.user_id,
459 last_update_login = fnd_global.login_id
460 where time_entry_id = p_time_entry_id;
461
462 if p_start_date is not null and l_start_date <> p_start_date then
463 if(l_logLevel <= wip_constants.full_logging) then
464 wip_logger.log('calling update_actual_start_dates()', l_return_status);
465 end if;
466 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
467 p_operation_seq_num => p_operation_seq_num,
468 p_resource_seq_num => p_resource_seq_num);
469
470 if(l_logLevel <= wip_constants.full_logging) then
471 wip_logger.log('calling update_proj_completion_dates()', l_return_status);
472 end if;
473 update_proj_completion_dates(p_organization_id => p_organization_id,
474 p_wip_entity_id => p_wip_entity_id,
475 p_operation_seq_num => p_operation_seq_num,
476 p_resource_seq_num => p_resource_seq_num,
477 p_resource_id => p_resource_id,
478 p_start_date => p_start_date);
479 end if;
480
481 l_return_status := 'S';
482 else
483 l_return_status := 'U'; --error condition: stale data
484 end if;
485 else
486 l_return_status := 'U'; --error condition: non time based resources are never updated
487 end if;
488
489 x_return_status := l_return_status;
490 if (l_logLevel <= wip_constants.trace_logging) then
491 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_update',
492 p_procReturnStatus => x_return_status,
493 p_msg => 'normal completion',
494 x_returnStatus => l_return_status);
495 end if;
496 END record_update;
497
498 -- Delete a record from the wip_resource_actual_times table.
499 PROCEDURE record_delete(
500 p_time_entry_id in number,
501 p_object_version_num in number,
502 x_return_status out NOCOPY varchar2)
503 IS
504 l_process_status number;
505 l_object_version_num number;
506 l_organization_id number;
507 l_wip_entity_id number;
508 l_operation_seq_num number;
509 l_resource_id number;
510 l_resource_seq_num number;
511 l_start_date date;
512 l_return_status varchar2(10);
513
514 l_params wip_logger.param_tbl_t;
515 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
516 cursor delete_cursor is select object_version_number,
517 organization_id,
518 wip_entity_id,
519 operation_seq_num,
520 resource_id,
521 resource_seq_num,
522 start_date
523 from wip_resource_actual_times
524 where time_entry_id = p_time_entry_id;
525 BEGIN
526 if(l_logLevel <= wip_constants.trace_logging) then
527 l_params(1).paramName := 'p_time_entry_id';
528 l_params(1).paramValue := p_time_entry_id;
529 l_params(2).paramName := 'p_object_version_num';
530 l_params(2).paramValue := p_object_version_num;
531
532 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
533 p_params => l_params,
534 x_returnStatus => x_return_status);
535 if(x_return_status <> fnd_api.g_ret_sts_success) then
536 raise fnd_api.g_exc_unexpected_error;
537 end if;
538 end if;
539 l_process_status := 4; --deleted
540 l_return_status := 'U';
541
542 open delete_cursor;
543 fetch delete_cursor into l_object_version_num,
544 l_organization_id,
545 l_wip_entity_id,
546 l_operation_seq_num,
547 l_resource_id,
548 l_resource_seq_num,
549 l_start_date;
550 if delete_cursor%NOTFOUND then
551 l_return_status := 'U';
552 if(l_logLevel <= wip_constants.full_logging) then
553 wip_logger.log('delete_cursor not found', l_return_status);
554 end if;
555 else
556 if l_object_version_num = p_object_version_num then
557 if(l_logLevel <= wip_constants.full_logging) then
558 wip_logger.log('updating wip_resource_actual_times', l_return_status);
559 end if;
560 update wip_resource_actual_times set
561 process_status = l_process_status,
562 object_version_number = p_object_version_num + 1,
563 last_update_date = sysdate,
564 last_updated_by = fnd_global.user_id,
565 last_update_login = fnd_global.login_id
566 where time_entry_id = p_time_entry_id
567 and process_status <> 4;
568
569 if(l_logLevel <= wip_constants.full_logging) then
570 wip_logger.log('calling update_actual_start_dates()', l_return_status);
571 end if;
572 update_actual_start_dates(p_wip_entity_id => l_wip_entity_id,
573 p_operation_seq_num => l_operation_seq_num,
574 p_resource_seq_num => l_resource_seq_num);
575
576 if l_start_date is not null then
577 if(l_logLevel <= wip_constants.full_logging) then
578 wip_logger.log('calling update_proj_completion_dates()', l_return_status);
579 end if;
580 update_proj_completion_dates(p_organization_id => l_organization_id,
581 p_wip_entity_id => l_wip_entity_id,
582 p_operation_seq_num => l_operation_seq_num,
583 p_resource_seq_num => l_resource_seq_num,
584 p_resource_id => l_resource_id,
585 p_start_date => l_start_date);
586 end if;
587
588 l_return_status := 'S';
589 end if;
590 end if;
591 close delete_cursor;
592 x_return_status := l_return_status;
593 if (l_logLevel <= wip_constants.trace_logging) then
594 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
595 p_procReturnStatus => x_return_status,
596 p_msg => 'normal completion',
597 x_returnStatus => l_return_status);
598 end if;
599 END record_delete;
600
601 -- Delete a record from the wip_resource_actual_times table.
602 PROCEDURE record_delete(
603 p_wip_entity_id in number,
604 p_operation_seq_num in number,
605 p_employee_id in number,
606 x_return_status out NOCOPY varchar2)
607 IS
608 l_process_status number;
609 l_object_version_num number;
610 l_organization_id number;
611 l_resource_id number;
612 l_resource_seq_num number;
613 l_start_date date;
614 l_return_status varchar2(10);
615
616 l_params wip_logger.param_tbl_t;
617 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
618 cursor delete_cursor is select object_version_number,
619 organization_id,
620 resource_id,
621 resource_seq_num,
622 start_date
623 from wip_resource_actual_times
624 where wip_entity_id = p_wip_entity_id
625 and operation_seq_num = p_operation_seq_num
626 and employee_id = p_employee_id
627 and process_status <> 4;
628 BEGIN
629 if(l_logLevel <= wip_constants.trace_logging) then
630 l_params(1).paramName := 'p_wip_entity_id';
631 l_params(1).paramValue := p_wip_entity_id;
632 l_params(2).paramName := 'p_operation_seq_num';
633 l_params(2).paramValue := p_operation_seq_num;
634 l_params(3).paramName := 'p_employee_id';
635 l_params(3).paramValue := p_employee_id;
636
637 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
638 p_params => l_params,
639 x_returnStatus => x_return_status);
640 if(x_return_status <> fnd_api.g_ret_sts_success) then
641 raise fnd_api.g_exc_unexpected_error;
642 end if;
643 end if;
644 l_process_status := 4; --deleted
645 l_return_status := 'U';
646
647 open delete_cursor;
648 fetch delete_cursor into l_object_version_num,
649 l_organization_id,
650 l_resource_id,
651 l_resource_seq_num,
652 l_start_date;
653 if delete_cursor%NOTFOUND then
654 l_return_status := 'U';
655 if(l_logLevel <= wip_constants.full_logging) then
656 wip_logger.log('delete_cursor not found', l_return_status);
657 end if;
658 else
659 if(l_logLevel <= wip_constants.full_logging) then
660 wip_logger.log('updating wip_resource_actual_times', l_return_status);
661 end if;
662 update wip_resource_actual_times set
663 process_status = l_process_status,
664 object_version_number = l_object_version_num + 1,
665 last_update_date = sysdate,
666 last_updated_by = fnd_global.user_id,
667 last_update_login = fnd_global.login_id
668 where wip_entity_id = p_wip_entity_id
669 and operation_seq_num = p_operation_seq_num
670 and employee_id = p_employee_id
671 and process_status <> 4;
672 if(l_logLevel <= wip_constants.full_logging) then
673 wip_logger.log('calling update_actual_start_dates()', l_return_status);
674 end if;
675
676 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
677 p_operation_seq_num => p_operation_seq_num,
678 p_resource_seq_num => l_resource_seq_num);
679
680 if l_start_date is not null then
681 if(l_logLevel <= wip_constants.full_logging) then
682 wip_logger.log('calling update_proj_completion_dates()', l_return_status);
683 end if;
684 update_proj_completion_dates(p_organization_id => l_organization_id,
685 p_wip_entity_id => p_wip_entity_id,
686 p_operation_seq_num => p_operation_seq_num,
687 p_resource_seq_num => l_resource_seq_num,
688 p_resource_id => l_resource_id,
689 p_start_date => l_start_date);
690 end if;
691
692 l_return_status := 'S';
693 end if;
694 close delete_cursor;
695 x_return_status := l_return_status;
696 if (l_logLevel <= wip_constants.trace_logging) then
697 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
698 p_procReturnStatus => x_return_status,
699 p_msg => 'normal completion',
700 x_returnStatus => l_return_status);
701 end if;
702 END record_delete;
703
704 /*************************************************/
705 /* Local Procedures */
706 /* job_off_internal */
707 /* clock_out_labors */
708 /* clock_out_machines */
709 /*************************************************/
710 PROCEDURE job_off_internal(p_wip_entity_id IN NUMBER, p_operation_seq_num NUMBER)
711 IS
712 x_return_status varchar2(10);
713 l_params wip_logger.param_tbl_t;
714 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
715 BEGIN
716
717 if(l_logLevel <= wip_constants.trace_logging) then
718 l_params(1).paramName := 'p_wip_entity_id';
719 l_params(1).paramValue := p_wip_entity_id;
720 l_params(2).paramName := 'p_operation_seq_num';
721 l_params(2).paramValue := p_operation_seq_num;
722
723 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_off_internal',
724 p_params => l_params,
725 x_returnStatus => x_return_status);
726 if(x_return_status <> fnd_api.g_ret_sts_success) then
727 raise fnd_api.g_exc_unexpected_error;
728 end if;
729 end if;
730 update wip_operations
731 set employee_id = null
732 where wip_entity_id = p_wip_entity_id
733 and operation_seq_num = p_operation_seq_num
734 and employee_id is not null;
735
736 if (l_logLevel <= wip_constants.trace_logging) then
737 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_off_internal',
738 p_procReturnStatus => x_return_status,
739 p_msg => 'normal completion',
740 x_returnStatus => x_return_status);
741 end if;
742 END job_off_internal;
743
744 procedure clock_out_labors(p_wip_entity_id number, p_operation_seq_num number, exclude_scheduled_flag number)
745 is
746 l_uom_code varchar2(3);
747 l_date date;
748 x_return_status varchar2(10);
749 l_params wip_logger.param_tbl_t;
750 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
751 Begin
752 if(l_logLevel <= wip_constants.trace_logging) then
753 l_params(1).paramName := 'p_wip_entity_id';
754 l_params(1).paramValue := p_wip_entity_id;
755 l_params(2).paramName := 'p_operation_seq_num';
756 l_params(2).paramValue := p_operation_seq_num;
757 l_params(3).paramName := 'exclude_scheduled_flag';
758 l_params(3).paramValue := exclude_scheduled_flag;
759
760 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out_labors',
761 p_params => l_params,
762 x_returnStatus => x_return_status);
763 if(x_return_status <> fnd_api.g_ret_sts_success) then
764 raise fnd_api.g_exc_unexpected_error;
765 end if;
766 end if;
767 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
768 l_date := sysdate;
769
770 update wip_resource_actual_times t
771 set
772 end_date = l_date,
773 duration = (l_date - start_date)*24,
774 uom_code = l_uom_code,
775 process_status = '3',
776 object_version_number = object_version_number + 1,
777 last_update_date = l_date,
778 last_updated_by = fnd_global.user_id,
779 last_update_login = fnd_global.login_id
780 where wip_entity_id = p_wip_entity_id
781 and operation_seq_num = p_operation_seq_num
782 and process_status <> 4
783 and status_type = 1
784 and start_date is not null
785 and end_date is null
786 and resource_id in (select resource_id from bom_resources where resource_type = 2)
787 and (exclude_scheduled_flag is null or
788 exclude_scheduled_flag <>
789 nvl((select scheduled_flag from wip_operation_resources wor
790 where wor.wip_entity_id = t.wip_entity_id
791 and wor.operation_seq_num = t.operation_seq_num
792 and wor.resource_seq_num = t.resource_seq_num), 2) /* ad-hoc has no scheduled flag*/
793 );
794
795 if (l_logLevel <= wip_constants.trace_logging) then
796 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out_labors',
797 p_procReturnStatus => x_return_status,
798 p_msg => 'normal completion',
799 x_returnStatus => x_return_status);
800 end if;
801 End clock_out_labors;
802
803 procedure clock_out_machines(p_wip_entity_id number, p_operation_seq_num number)
804 is
805
806 l_params wip_logger.param_tbl_t;
807 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
808 cursor all_labor_clocked_out(p_scheduled_flag number) is
809 select
810 decode(
811 ( select count(*)
812 from wip_resource_actual_times wrat,
813 bom_resources br,
814 wip_operation_resources wor
815 where wrat.wip_entity_id = p_wip_entity_id
816 and wrat.operation_seq_num = p_operation_seq_num
817 and wrat.resource_id = br.resource_id
818 and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
819 and wrat.operation_seq_num = wor.operation_seq_num (+)
820 and wrat.resource_id = wor.resource_id (+)
821 and wrat.process_status <> 4
822 and wrat.status_type = 1
823 and br.resource_type = 2 /* labor */
824 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
825 and wrat.start_date is not null
826 and wrat.end_date is null
827 ), 0, 1, 0)
828 from dual;
829
830 /* max end date for yes/no labor resources */
831 cursor max_labor_end_date(p_scheduled_flag number) is
832 select max(wrat.end_date)
833 from wip_resource_actual_times wrat,
834 bom_resources br,
835 wip_operation_resources wor
836 where wrat.wip_entity_id = p_wip_entity_id
837 and wrat.operation_seq_num = p_operation_seq_num
838 and wrat.resource_id = br.resource_id
839 and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
840 and wrat.operation_seq_num = wor.operation_seq_num (+)
841 and wrat.resource_id = wor.resource_id (+)
842 and wrat.process_status <> 4
843 and br.resource_type = 2 /* labor */
844 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
845 and wrat.end_date is not null;
846
847 l_all_clocked_out number;
848 l_scheduled_flag number;
849 l_date date;
850 l_uom_code varchar2(10);
851 x_return_status varchar2(10);
852 Begin
853
854 if(l_logLevel <= wip_constants.trace_logging) then
855 l_params(1).paramName := 'p_wip_entity_id';
856 l_params(1).paramValue := p_wip_entity_id;
857 l_params(2).paramName := 'p_operation_seq_num';
858 l_params(2).paramValue := p_operation_seq_num;
859
860 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out_machines',
861 p_params => l_params,
862 x_returnStatus => x_return_status);
863 if(x_return_status <> fnd_api.g_ret_sts_success) then
864 raise fnd_api.g_exc_unexpected_error;
865 end if;
866 end if;
867 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
868
869 /* scheduled yes/no resource */
870 l_scheduled_flag := 1;
871
872 open all_labor_clocked_out(l_scheduled_flag);
873 fetch all_labor_clocked_out into l_all_clocked_out;
874 close all_labor_clocked_out;
875
876 if( l_all_clocked_out = 1) then
877 open max_labor_end_date(l_scheduled_flag);
878 fetch max_labor_end_date into l_date;
879 close max_labor_end_date;
880
881 if( l_date is null ) then l_date := sysdate; end if;
882
883 update wip_resource_actual_times t
884 set
885 end_date = l_date,
886 duration = (l_date - start_date)*24,
887 uom_code = l_uom_code,
888 process_status = '3',
889 object_version_number = object_version_number + 1,
890 last_update_date = sysdate,
891 last_updated_by = fnd_global.user_id,
892 last_update_login = fnd_global.login_id
893 where wip_entity_id = p_wip_entity_id
894 and operation_seq_num = p_operation_seq_num
895 and status_type = 1
896 and start_date is not null
897 and end_date is null
898 and resource_id in (select resource_id from bom_resources where resource_type = 1)
899 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
900 from wip_operation_resources wor
901 where wor.wip_entity_id = t.wip_entity_id
902 and wor.operation_seq_num = t.operation_seq_num
903 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
904 end if;
905
906
907 /* prior resources */
908 l_scheduled_flag := 3;
909
910 open all_labor_clocked_out(l_scheduled_flag);
911 fetch all_labor_clocked_out into l_all_clocked_out;
912 close all_labor_clocked_out;
913
914 if( l_all_clocked_out = 1) then
915 open max_labor_end_date(l_scheduled_flag);
916 fetch max_labor_end_date into l_date;
917 close max_labor_end_date;
918
919 if( l_date is null ) then l_date := sysdate; end if;
920
921 update wip_resource_actual_times t
922 set
923 end_date = l_date,
924 duration = (l_date - start_date)*24,
925 process_status = '3',
926 object_version_number = object_version_number + 1,
927 last_update_date = sysdate,
928 last_updated_by = fnd_global.user_id,
929 last_update_login = fnd_global.login_id
930 where wip_entity_id = p_wip_entity_id
931 and operation_seq_num = p_operation_seq_num
932 and status_type = 1
933 and start_date is not null
934 and end_date is null
935 and resource_id in (select resource_id from bom_resources where resource_type = 1)
936 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
937 from wip_operation_resources wor
938 where wor.wip_entity_id = t.wip_entity_id
939 and wor.operation_seq_num = t.operation_seq_num
940 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
941 end if;
942
943 /* next resources */
944 l_scheduled_flag := 4;
945
946 open all_labor_clocked_out(l_scheduled_flag);
947 fetch all_labor_clocked_out into l_all_clocked_out;
948 close all_labor_clocked_out;
949
950 if( l_all_clocked_out = 1) then
951 open max_labor_end_date(l_scheduled_flag);
952 fetch max_labor_end_date into l_date;
953 close max_labor_end_date;
954
955 if( l_date is null ) then l_date := sysdate; end if;
956
957 update wip_resource_actual_times t
958 set
959 end_date = l_date,
960 duration = (l_date - start_date)*24,
961 process_status = '3',
962 object_version_number = object_version_number + 1,
963 last_update_date = sysdate,
964 last_updated_by = fnd_global.user_id,
965 last_update_login = fnd_global.login_id
966 where wip_entity_id = p_wip_entity_id
967 and operation_seq_num = p_operation_seq_num
968 and status_type = 1
969 and start_date is not null
970 and end_date is null
971 and resource_id in (select resource_id from bom_resources where resource_type = 1)
972 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
973 from wip_operation_resources wor
974 where wor.wip_entity_id = t.wip_entity_id
975 and wor.operation_seq_num = t.operation_seq_num
976 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
977 end if;
978 if (l_logLevel <= wip_constants.trace_logging) then
979 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out_machines',
980 p_procReturnStatus => x_return_status,
981 p_msg => 'normal completion',
982 x_returnStatus => x_return_status);
983 end if;
984
985 End clock_out_machines;
986
987 function get_adhoc_resource_seq(p_wip_entity_id number, p_operation_seq_num number, p_resource_id number)
988 return number
989 Is
990 -- check if the resuorce is aleady used in wrat
991 cursor resource_seq_in_wrat is
992 select resource_seq_num
993 from wip_resource_actual_times
994 where wip_entity_id = p_wip_entity_id
995 and operation_seq_num = p_operation_seq_num
996 and resource_id = p_resource_id
997 and resource_seq_num is not null
998 and rownum=1;
999
1000 l_resource_seq_num number;
1001 l_next_resource_seq_num number;
1002 l_wor_max number;
1003 l_wrat_max number;
1004 Begin
1005
1006 l_resource_seq_num := null;
1007
1008 open resource_seq_in_wrat;
1009 fetch resource_seq_in_wrat into l_resource_seq_num;
1010
1011 if( resource_seq_in_wrat%NOTFOUND ) then
1012
1013 select max(wor.resource_seq_num) seq
1014 into l_wor_max
1015 from wip_operation_resources wor
1016 where wor.wip_entity_id = p_wip_entity_id
1017 and wor.operation_seq_num = p_operation_seq_num;
1018
1019 select max(wrat.resource_seq_num) seq
1020 into l_wrat_max
1021 from wip_resource_actual_times wrat
1022 where wrat.wip_entity_id = p_wip_entity_id
1023 and wrat.operation_seq_num = p_operation_seq_num
1024 and wrat.resource_seq_num is not null;
1025
1026 select greatest(nvl(l_wor_max,0), nvl(l_wrat_max,0)) + 10
1027 into l_resource_seq_num
1028 from dual;
1029
1030 end if;
1031 close resource_seq_in_wrat;
1032
1033 return l_resource_seq_num;
1034 End get_adhoc_resource_seq;
1035
1036 /* update the operation/resources's actual completion date based on wrat */
1037 procedure update_actual_comp_dates(p_wip_entity_id IN NUMBER,
1038 p_operation_seq_num IN NUMBER)
1039 IS
1040 Begin
1041
1042 /* update wor's actual completion date */
1043 update wip_operation_resources wor
1044 set
1045 wor.actual_completion_date =
1046 nvl( ( select max(wrat.end_date)
1047 from wip_resource_actual_times wrat
1048 where wrat.wip_entity_id = wor.wip_entity_id
1049 and wrat.operation_seq_num = wor.operation_seq_num
1050 and wrat.resource_seq_num = wor.resource_seq_num
1051 and wrat.process_status <> 4
1052 and wrat.end_date is not null), sysdate)
1053 where wip_entity_id = p_wip_entity_id
1054 and operation_seq_num = p_operation_seq_num
1055 and not exists (select wrat.end_date
1056 from wip_resource_actual_times wrat
1057 where wrat.wip_entity_id = wor.wip_entity_id
1058 and wrat.operation_seq_num = wor.operation_seq_num
1059 and wrat.resource_seq_num = wor.resource_seq_num
1060 and wrat.start_date is not null
1061 and wrat.end_date is null
1062 and wrat.process_status <> 4);
1063
1064 update wip_operations wo
1065 set
1066 wo.actual_completion_date =
1067 nvl(greatest( ( select max(wor.actual_completion_date)
1068 from wip_operation_resources wor
1069 where wor.wip_entity_id = wo.wip_entity_id
1070 and wor.operation_seq_num = wo.operation_seq_num
1071 and wor.actual_completion_date is not null)
1072 , ( select max(wrat.end_date)
1073 from wip_resource_actual_times wrat
1074 where wrat.wip_entity_id = wo.wip_entity_id
1075 and wrat.operation_seq_num = wo.operation_seq_num
1076 and wrat.end_date is not null)
1077 ), sysdate)
1078 where wip_entity_id = p_wip_entity_id
1079 and operation_seq_num = p_operation_seq_num
1080 and not exists (select 1
1081 from wip_resource_actual_times wrat
1082 where wrat.wip_entity_id = wo.wip_entity_id
1083 and wrat.operation_seq_num = wo.operation_seq_num
1084 and wrat.start_date is not null
1085 and wrat.end_date is null
1086 and wrat.process_status <> 4);
1087
1088 End update_actual_comp_dates;
1089
1090 -- Process records on report resource usages page.
1091 PROCEDURE process_time_records_resource(p_organization_id in number)
1092 IS
1093 l_wip_entity_id number;
1094 l_op_seq_num number;
1095
1096 cursor time_records_all_cursor is
1097 select distinct wrat.wip_entity_id,
1098 wrat.operation_seq_num
1099 from wip_resource_actual_times wrat
1100 where wrat.organization_id = p_organization_id
1101 and wrat.status_type = 1
1102 and wrat.process_status = 2 /* only new records */
1103 and wrat.duration is not null;
1104
1105 BEGIN
1106
1107 for time_record IN time_records_all_cursor
1108 loop
1109 l_wip_entity_id := time_record.wip_entity_id;
1110 l_op_seq_num := time_record.operation_seq_num;
1111
1112 process_time_records(p_wip_entity_id => l_wip_entity_id,
1113 p_completed_op => l_op_seq_num,
1114 p_instance_id => null,
1115 p_time_entry_source => 'resource');
1116 end loop;
1117 END process_time_records_resource;
1118
1119 -- Process records on report my time page.
1120 PROCEDURE process_time_records_my_time(p_organization_id in number,
1121 p_instance_id in number)
1122 IS
1123 l_wip_entity_id number;
1124 l_op_seq_num number;
1125
1126 cursor time_records_mytime_cursor is
1127 select distinct wrat.wip_entity_id,
1128 wrat.operation_seq_num
1129 from wip_resource_actual_times wrat
1130 where wrat.organization_id = p_organization_id
1131 and wrat.instance_id = p_instance_id
1132 and wrat.status_type = 1
1133 and wrat.process_status in (2, 3, 4)
1134 and (wrat.time_entry_mode in (3,4) or wrat.process_status = 2)
1135 and wrat.duration is not null;
1136
1137 BEGIN
1138 for time_record IN time_records_mytime_cursor
1139 loop
1140 l_wip_entity_id := time_record.wip_entity_id;
1141 l_op_seq_num := time_record.operation_seq_num;
1142 process_time_records(p_wip_entity_id => l_wip_entity_id,
1143 p_completed_op => l_op_seq_num,
1144 p_instance_id => p_instance_id,
1145 p_time_entry_source => 'mytime');
1146 end loop;
1147 END process_time_records_my_time;
1148
1149 -- Bug 14288080
1150 -- Process records on report my time page for logged in emp
1151 PROCEDURE process_time_my_time_for_emp(p_organization_id in number,
1152 p_emp_id in number)
1153 IS
1154 l_wip_entity_id number;
1155 l_op_seq_num number;
1156 l_instance_id number;
1157
1158 cursor time_records_mytime_cursor is
1159 select distinct wrat.wip_entity_id,
1160 wrat.operation_seq_num,wrat.instance_id
1161 from wip_resource_actual_times wrat
1162 where wrat.organization_id = p_organization_id
1163 and ( (wrat.instance_id in (select instance_id from bom_resource_employees where organization_id=p_organization_id and person_id=p_emp_id ) ) or (employee_id=p_emp_id and instance_id is null) )
1164 and wrat.status_type = 1
1165 and wrat.process_status in (2, 3, 4)
1166 and (wrat.time_entry_mode in (3,4) or wrat.process_status = 2)
1167 and wrat.duration is not null;
1168
1169 BEGIN
1170 for time_record IN time_records_mytime_cursor
1171 loop
1172 l_wip_entity_id := time_record.wip_entity_id;
1173 l_op_seq_num := time_record.operation_seq_num;
1174 l_instance_id := time_record.instance_id;
1175 process_time_records(p_wip_entity_id => l_wip_entity_id,
1176 p_completed_op => l_op_seq_num,
1177 p_instance_id => l_instance_id,
1178 p_time_entry_source => 'mytime');
1179 end loop;
1180 END process_time_my_time_for_emp;
1181
1182 -- Process records on move page.
1183 PROCEDURE process_time_records_move(p_wip_entity_id IN NUMBER,
1184 p_from_op IN NUMBER,
1185 p_to_op IN NUMBER)
1186 IS
1187 l_curr_op number;
1188
1189 cursor op_seq_num_cursor is
1190 select operation_seq_num
1191 from wip_operations
1192 where wip_entity_id = p_wip_entity_id
1193 and operation_seq_num >= p_from_op
1194 and operation_seq_num <= p_to_op;
1195 BEGIN
1196 for op_seq_num_record IN op_seq_num_cursor
1197 loop
1198 l_curr_op := op_seq_num_record.operation_seq_num;
1199 process_time_records(p_wip_entity_id => p_wip_entity_id,
1200 p_completed_op => l_curr_op,
1201 p_instance_id => null,
1202 p_time_entry_source => 'move');
1203 end loop;
1204 END process_time_records_move;
1205
1206 -- Process records on report job operation page.
1207 PROCEDURE process_time_records_job_op(p_wip_entity_id IN NUMBER,
1208 p_operation_seq_num IN NUMBER,
1209 p_instance_id in number)
1210 IS
1211 BEGIN
1212 process_time_records(p_wip_entity_id => p_wip_entity_id,
1213 p_completed_op => p_operation_seq_num,
1214 p_instance_id => p_instance_id,
1215 p_time_entry_source => 'jobop');
1216 END process_time_records_job_op;
1217
1218 -- Process records.
1219 PROCEDURE process_time_records(p_wip_entity_id IN NUMBER,
1220 p_completed_op IN NUMBER,
1221 p_instance_id IN NUMBER,
1222 p_time_entry_source IN VARCHAR2)
1223 IS
1224 l_object_version_num number;
1225 l_add_to_rtg varchar2(1);
1226 l_cost_flag varchar2(1);
1227 l_status_type number;
1228 l_process_status number;
1229 l_resource_seq_num number;
1230 l_org_id number;
1231 l_resource_id number;
1232 l_duration number;
1233 l_uom_code varchar2(3);
1234 l_employee_id number;
1235 l_instance_id number;
1236 l_resource_type number;
1237 l_scheduled_flag number;
1238 l_next_resource_seq_num number;
1239 l_end_date date;
1240 l_time_entry_mode number;
1241 l_last_op_qty_num number;
1242 l_last_job_qty_num number;
1243 l_last_op_qty varchar2(1);
1244 l_last_job_qty varchar2(1);
1245 l_completed_status_type number;
1246 l_completed_process_status number;
1247 l_organization_code varchar2(3);
1248 l_resource_code varchar2(10);
1249 l_wip_entity_name varchar2(240);
1250 l_change_flag varchar2(1);
1251 lx_organization_id number;
1252 lx_department_id number;
1253 lx_status varchar2(1);
1254 lx_msg_count number;
1255 lx_msg varchar2(255);
1256
1257 l_employee_num varchar2(30);
1258
1259 l_retStatus varchar2(10);
1260 l_return_status varchar2(10);
1261
1262 l_params wip_logger.param_tbl_t;
1263 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1264 --time records (for all resource types) cursor- used for setting cost_flag, add_to_rtg,
1265 -- resource_seq_num (for ad-hoc resources), inserting into wcti and updating wrat status_type to completed
1266 cursor time_records_cursor (c_time_entry_mode1 number,
1267 c_time_entry_mode2 number,
1268 c_time_entry_mode3 number) is
1269 select wrat.time_entry_id,
1270 wrat.object_version_number,
1271 wrat.add_to_rtg,
1272 wrat.cost_flag,
1273 wrat.status_type,
1274 wrat.process_status,
1275 wrat.resource_seq_num,
1276 wrat.organization_id,
1277 wrat.resource_id,
1278 wrat.duration,
1279 wrat.uom_code,
1280 wrat.end_date,
1281 wrat.time_entry_mode
1282 from wip_resource_actual_times wrat,
1283 bom_resources br,
1284 wip_operation_resources wor
1285 where wrat.wip_entity_id = p_wip_entity_id
1286 and wrat.operation_seq_num = p_completed_op
1287 and wrat.resource_id = br.resource_id
1288 and wrat.wip_entity_id = wor.wip_entity_id (+)
1289 and wrat.operation_seq_num = wor.operation_seq_num (+)
1290 and wrat.resource_seq_num = wor.resource_seq_num (+) -- Bug 14742435
1291 and wrat.resource_id = wor.resource_id (+)
1292 and wrat.status_type = 1
1293 and wrat.process_status <> 4
1294 and ( wrat.process_status in (2, 3) or
1295 ( wrat.time_entry_mode in (c_time_entry_mode1,
1296 c_time_entry_mode2, c_time_entry_mode3)
1297 and (p_instance_id is null or wrat.instance_id = p_instance_id)
1298 ) )
1299 ; -- and wrat.duration is not null;
1300
1301
1302 --find out if there are any active next resources
1303 --bug10333052 relaxed conditions since borrowed resource may not exists in WOR table
1304 --Joining WOR can cause clocking out automatcially when doing clocking in
1305 cursor active_clock_cursor is
1306 select count(*)
1307 from wip_resource_actual_times wrat
1308 --wip_operation_resources wor
1309 where wrat.wip_entity_id = p_wip_entity_id
1310 and wrat.operation_seq_num = p_completed_op
1311 and wrat.status_type = 1
1312 and wrat.process_status <> 4
1313 and wrat.start_date is not null
1314 and wrat.end_date is null;
1315 --and wrat.wip_entity_id = wor.wip_entity_id
1316 --and wrat.operation_seq_num = wor.operation_seq_num
1317 --and wrat.resource_id = wor.resource_id;
1318
1319 l_active_clocks number;
1320 l_entry_id number;
1321 BEGIN
1322
1323 if(l_logLevel <= wip_constants.trace_logging) then
1324 l_params(1).paramName := 'p_wip_entity_id';
1325 l_params(1).paramValue := p_wip_entity_id;
1326 l_params(2).paramName := 'p_completed_op';
1327 l_params(2).paramValue := p_completed_op;
1328 l_params(3).paramName := 'p_instance_id';
1329 l_params(3).paramValue := p_instance_id;
1330 l_params(4).paramName := 'p_time_entry_source';
1331 l_params(4).paramValue := p_time_entry_source;
1332
1333 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.process_time_records',
1334 p_params => l_params,
1335 x_returnStatus => l_return_status);
1336 if(l_return_status <> fnd_api.g_ret_sts_success) then
1337 raise fnd_api.g_exc_unexpected_error;
1338 end if;
1339 end if;
1340 l_completed_status_type := 2; --completed
1341 l_completed_process_status := 1; --completed
1342
1343 l_last_op_qty_num := get_last_op_qty(p_wip_entity_id => p_wip_entity_id,
1344 p_operation_seq_num => p_completed_op);
1345 if l_last_op_qty_num = 0 then
1346 l_last_op_qty := 'Y';
1347 end if;
1348
1349 if(l_logLevel <= wip_constants.full_logging) then
1350 wip_logger.log('l_last_op_qty_num: ' || l_last_op_qty_num, l_return_status);
1351 wip_logger.log('l_last_op_qty: ' || l_last_op_qty, l_return_status);
1352 end if;
1353 l_last_job_qty_num := get_last_job_qty(p_wip_entity_id => p_wip_entity_id,
1354 p_operation_seq_num => p_completed_op);
1355 --fix bug 13826369
1356 if l_last_job_qty_num <= 0 then
1357 l_last_job_qty := 'Y';
1358 end if;
1359
1360 if(l_logLevel <= wip_constants.full_logging) then
1361 wip_logger.log('l_last_job_qty_num: ' || l_last_job_qty_num, l_return_status);
1362 wip_logger.log('l_last_job_qty: ' || l_last_job_qty, l_return_status);
1363 end if;
1364 if (l_last_op_qty = 'Y' and p_time_entry_source = 'move') then
1365 if(l_logLevel <= wip_constants.full_logging) then
1366 wip_logger.log('calling clock_out_labors()', l_return_status);
1367 end if;
1368 clock_out_labors(p_wip_entity_id, p_completed_op, 4); /* don't clock out next resources */
1369 end if;
1370
1371 /*Bug 9853104: Clock out machines even if there is job qty in Queue/Run but
1372 all person resources have clocked out
1373 if (l_last_job_qty = 'Y') then*/
1374 if(l_logLevel <= wip_constants.full_logging) then
1375 wip_logger.log('calling clock_out_machines()', l_return_status);
1376 end if;
1377 clock_out_machines(p_wip_entity_id, p_completed_op);
1378 --end if;
1379
1380 /* if there is still active clock-ins */
1381 l_active_clocks := null;
1382 open active_clock_cursor;
1383 fetch active_clock_cursor into l_active_clocks;
1384 close active_clock_cursor;
1385
1386 if (p_time_entry_source = 'move') then
1387 open time_records_cursor(4,-1,-1);
1388 elsif (p_time_entry_source = 'clock') then
1389 open time_records_cursor(2,-1,-1);
1390 elsif (p_time_entry_source = 'resource') then
1391 open time_records_cursor(-1,-1,-1); /* only new records */
1392 else
1393 open time_records_cursor(3,4,4);
1394 end if;
1395
1396 loop
1397 fetch time_records_cursor into l_entry_id,
1398 l_object_version_num,
1399 l_add_to_rtg,
1400 l_cost_flag,
1401 l_status_type,
1402 l_process_status,
1403 l_resource_seq_num,
1404 l_org_id,
1405 l_resource_id,
1406 l_duration,
1407 l_uom_code,
1408 l_end_date,
1409 l_time_entry_mode;
1410 exit when time_records_cursor%NOTFOUND;
1411
1412 if(l_logLevel <= wip_constants.full_logging) then
1413 wip_logger.log('l_entry_id: ' || l_entry_id, l_return_status);
1414 wip_logger.log('l_object_version_num: ' || l_object_version_num, l_return_status);
1415 wip_logger.log('l_add_to_rtg: ' || l_add_to_rtg, l_return_status);
1416 wip_logger.log('l_cost_flag: ' || l_cost_flag, l_return_status);
1417 wip_logger.log('l_status_type: ' || l_status_type, l_return_status);
1418 wip_logger.log('l_process_status: ' || l_process_status, l_return_status);
1419 wip_logger.log('l_resource_seq_num: ' || l_resource_seq_num, l_return_status);
1420 wip_logger.log('l_org_id: ' || l_org_id, l_return_status);
1421 wip_logger.log('l_resource_id: ' || l_resource_id, l_return_status);
1422 wip_logger.log('l_duration: ' || l_duration, l_return_status);
1423 wip_logger.log('l_uom_code: ' || l_uom_code, l_return_status);
1424 wip_logger.log('l_end_date: ' || l_end_date, l_return_status);
1425 wip_logger.log('l_time_entry_mode: ' || l_time_entry_mode, l_return_status);
1426 end if;
1427 /* add the cost_flag and add_to_rtg and resource seq for the inserted rows */
1428 if ( l_process_status = 2 ) then
1429 l_change_flag := 'N';
1430
1431 if (l_cost_flag is null) then
1432 l_cost_flag := get_cost_flag(p_wip_entity_id => p_wip_entity_id,
1433 p_operation_seq_num => p_completed_op,
1434 p_resource_seq_num => l_resource_seq_num,
1435 p_time_entry_source => p_time_entry_source);
1436 l_change_flag := 'Y';
1437 end if;
1438
1439 if(l_logLevel <= wip_constants.full_logging) then
1440 wip_logger.log('l_cost_flag: ' || l_cost_flag, l_return_status);
1441 wip_logger.log('l_change_flag: ' || l_change_flag, l_return_status);
1442 end if;
1443 if (l_add_to_rtg is null) then
1444 l_add_to_rtg := get_add_to_rtg_flag(p_wip_entity_id => p_wip_entity_id,
1445 p_operation_seq_num => p_completed_op,
1446 p_resource_seq_num => l_resource_seq_num,
1447 p_cost_flag => l_cost_flag,
1448 p_time_entry_source => p_time_entry_source);
1449 l_change_flag := 'Y';
1450 end if;
1451
1452 if(l_logLevel <= wip_constants.full_logging) then
1453 wip_logger.log('l_add_to_rtg: ' || l_add_to_rtg, l_return_status);
1454 wip_logger.log('l_change_flag: ' || l_change_flag, l_return_status);
1455 end if;
1456 if (l_add_to_rtg = 'Y' and l_resource_seq_num is null) then
1457 l_resource_seq_num := get_adhoc_resource_seq(
1458 p_wip_entity_id, p_completed_op, l_resource_id);
1459 l_change_flag := 'Y';
1460 end if;
1461 if(l_logLevel <= wip_constants.full_logging) then
1462 wip_logger.log('l_resource_seq_num: ' || l_resource_seq_num, l_return_status);
1463 wip_logger.log('l_change_flag: ' || l_change_flag, l_return_status);
1464 end if;
1465
1466 if l_change_flag = 'Y' then
1467 update wip_resource_actual_times
1468 set
1469 cost_flag = l_cost_flag,
1470 add_to_rtg = l_add_to_rtg,
1471 resource_seq_num = l_resource_seq_num,
1472 object_version_number = l_object_version_num + 1,
1473 last_update_date = sysdate,
1474 last_updated_by = fnd_global.user_id,
1475 last_update_login = fnd_global.login_id
1476 where time_entry_id = l_entry_id;
1477 end if;
1478 end if;
1479
1480 /* if it's called from clock, auto-clocked in machines needs to be review later*/
1481 if( l_duration is not null
1482 and not (p_time_entry_source = 'clock' and l_time_entry_mode <> 2)
1483 and not (p_time_entry_source = 'move' and l_time_entry_mode = 3) /* clock out but not charge */
1484 ) then
1485 if (l_cost_flag = 'Y') then
1486 if(l_logLevel <= wip_constants.full_logging) then
1487 wip_logger.log('calling derive_insert_wcti', l_return_status);
1488 end if;
1489 /*Add for Bug 10097774 */
1490 derive_insert_wcti(p_organization_id => l_org_id,
1491 p_wip_entity_id => p_wip_entity_id,
1492 p_operation_seq_num => p_completed_op,
1493 p_resource_seq_num => l_resource_seq_num,
1494 p_resource_id => l_resource_id,
1495 p_entry_id => l_entry_id,
1496 p_employee_id => null,
1497 p_duration => l_duration,
1498 p_txn_uom => l_uom_code,
1499 p_hour_uom => l_uom_code,
1500 x_return_status =>l_return_status);
1501 end if;
1502
1503 if(l_logLevel <= wip_constants.full_logging) then
1504 wip_logger.log('update wip_resource_actual_times', l_return_status);
1505 end if;
1506 --mark status type complete for wrat records
1507 update wip_resource_actual_times
1508 set
1509 status_type = l_completed_status_type,
1510 process_status = l_completed_process_status,
1511 object_version_number = l_object_version_num + 1,
1512 last_update_date = sysdate,
1513 last_updated_by = fnd_global.user_id,
1514 last_update_login = fnd_global.login_id
1515 where time_entry_id = l_entry_id;
1516
1517 end if;
1518 end loop;
1519 close time_records_cursor;
1520
1521 /* job-off if no active clock-ins and is the last qty */
1522 /*Bug 9853104: Job-off even if qty exists in operation but there are
1523 no active clock-ins */
1524 /*Bug 12982934:Fix the regression caused by bug 9853104*/
1525 /* Added condition on last_op_qty for Bug 13826369 */
1526 if(l_logLevel <= wip_constants.full_logging) then
1527 wip_logger.log('l_last_job_qty : '|| l_last_job_qty, l_return_status);
1528 wip_logger.log('l_last_op_qty : '|| l_last_op_qty, l_return_status);
1529 wip_logger.log('l_active_clocks : '|| l_active_clocks, l_return_status);
1530 wip_logger.log('Before calling job_off_internal', l_return_status);
1531 wip_logger.log('p_time_entry_source:'||p_time_entry_source, l_return_status);
1532 end if;
1533 -- Added p_time_entry_source check for Bug 14705770
1534
1535 if (l_last_job_qty = 'Y' or p_time_entry_source = 'clock' ) then
1536 if( l_active_clocks = 0 and (l_last_op_qty = 'Y' or p_time_entry_source = 'clock') ) then
1537 if(l_logLevel <= wip_constants.full_logging) then
1538 wip_logger.log('While calling job_off_internal', l_return_status);
1539 end if;
1540 job_off_internal(p_wip_entity_id, p_completed_op);
1541 end if;
1542 --if (l_last_job_qty = 'Y') then
1543 update_actual_comp_dates(p_wip_entity_id, p_completed_op);
1544 end if;
1545 if(l_logLevel <= wip_constants.full_logging) then
1546 wip_logger.log('After calling job_off_internal', l_return_status);
1547 end if;
1548 /* for wrat records with process status deleted - delete records */
1549 delete from wip_resource_actual_times
1550 where wip_entity_id = p_wip_entity_id
1551 and operation_seq_num = p_completed_op
1552 and status_type = 1
1553 and process_status = 4;
1554
1555 /* reset process_status of records of inserted/updated - mark as completed */
1556 update wip_resource_actual_times set
1557 process_status = l_completed_process_status,
1558 last_update_date = sysdate,
1559 last_updated_by = fnd_global.user_id,
1560 last_update_login = fnd_global.login_id
1561 where wip_entity_id = p_wip_entity_id
1562 and operation_seq_num = p_completed_op
1563 and status_type = 1
1564 and process_status in (2,3);
1565 if (l_logLevel <= wip_constants.trace_logging) then
1566 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.process_time_records',
1567 p_procReturnStatus => l_return_status,
1568 p_msg => 'normal completion',
1569 x_returnStatus => l_return_status);
1570 end if;
1571 END process_time_records;
1572
1573 /* to check if UOM time based */
1574 FUNCTION is_time_uom(p_uom_code IN VARCHAR2) return VARCHAR2
1575 IS
1576 l_uom_class varchar2(10);
1577 l_time_based_uom_flag varchar2(1);
1578
1579 cursor time_based_uom_cursor is
1580 select distinct muc.uom_class
1581 from mtl_uom_conversions muc,
1582 mtl_uom_conversions muc2
1583 where (muc.uom_class = muc2.uom_class and
1584 nvl(muc.disable_date, sysdate + 1) > sysdate) and
1585 nvl(muc2.disable_date, sysdate + 1) > sysdate and
1586 muc.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE') and
1587 muc2.uom_code = p_uom_code;
1588 BEGIN
1589 open time_based_uom_cursor;
1590 fetch time_based_uom_cursor into l_uom_class;
1591 if time_based_uom_cursor%NOTFOUND then
1592 l_time_based_uom_flag := 'N';
1593 else
1594 l_time_based_uom_flag := 'Y';
1595 end if;
1596 close time_based_uom_cursor;
1597
1598 return l_time_based_uom_flag;
1599 END is_time_uom;
1600
1601 -- Get the value for time entry mode.
1602 FUNCTION get_time_entry_mode(p_wip_entity_id IN NUMBER,
1603 p_operation_seq_num IN NUMBER) return NUMBER
1604 IS
1605 l_time_entry_mode varchar2(2); -- Modified for Bug 6663985.
1606 lx_organization_id number;
1607 lx_department_id number;
1608 BEGIN
1609 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
1610 p_operation_seq_num => p_operation_seq_num,
1611 x_organization_id => lx_organization_id,
1612 x_department_id => lx_department_id);
1613
1614 l_time_entry_mode := wip_ws_util.get_preference_value_code(p_pref_id => G_WIP_WS_PREF_TIME_ENTRY_MODE,
1615 p_resp_key => wip_ws_util.get_current_resp_key,
1616 p_org_id => lx_organization_id,
1617 p_dept_id => lx_department_id);
1618 return mod(l_time_entry_mode,10);
1619 END get_time_entry_mode;
1620
1621 -- Get the value for cost_flag.
1622 FUNCTION get_cost_flag(p_wip_entity_id IN NUMBER,
1623 p_operation_seq_num IN NUMBER,
1624 p_resource_seq_num IN NUMBER,
1625 p_time_entry_source IN VARCHAR2) return VARCHAR2
1626 IS
1627 l_charge_time_resources_pref varchar2(1);
1628 l_autocharge_type number;
1629 l_cost_flag varchar2(1);
1630 lx_organization_id number;
1631 lx_department_id number;
1632
1633 l_retStatus varchar2(1);
1634 l_manual_exist number;
1635 l_return_status varchar2(1);
1636 l_params wip_logger.param_tbl_t;
1637 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1638 cursor has_autocharge_manual_cursor is
1639 select 1
1640 from wip_operation_resources wor
1641 where wor.wip_entity_id = p_wip_entity_id
1642 and wor.operation_seq_num = p_operation_seq_num
1643 and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1644 and rownum = 1;
1645
1646 cursor autocharge_type_res_cursor is select wor.autocharge_type
1647 from wip_operation_resources wor
1648 where wor.wip_entity_id = p_wip_entity_id
1649 and wor.operation_seq_num = p_operation_seq_num
1650 and wor.resource_seq_num = p_resource_seq_num;
1651 BEGIN
1652 if(l_logLevel <= wip_constants.trace_logging) then
1653 l_params(1).paramName := 'p_wip_entity_id';
1654 l_params(1).paramValue := p_wip_entity_id;
1655 l_params(2).paramName := 'p_operation_seq_num';
1656 l_params(2).paramValue := p_operation_seq_num;
1657 l_params(3).paramName := 'p_resource_seq_num';
1658 l_params(3).paramValue := p_resource_seq_num;
1659 l_params(4).paramName := 'p_time_entry_source';
1660 l_params(4).paramValue := p_time_entry_source;
1661
1662 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.get_cost_flag',
1663 p_params => l_params,
1664 x_returnStatus => l_return_status);
1665 if(l_return_status <> fnd_api.g_ret_sts_success) then
1666 raise fnd_api.g_exc_unexpected_error;
1667 end if;
1668 end if;
1669 if (p_time_entry_source = 'move') then --time entry source is 'move'
1670 l_cost_flag := 'Y';
1671 else
1672 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
1673 p_operation_seq_num => p_operation_seq_num,
1674 x_organization_id => lx_organization_id,
1675 x_department_id => lx_department_id);
1676 l_charge_time_resources_pref := wip_ws_util.get_preference_value_code(p_pref_id => G_WIP_WS_PREF_CHARGE_RESOURCE,
1677 p_resp_key => wip_ws_util.get_current_resp_key,
1678 p_org_id => lx_organization_id,
1679 p_dept_id => lx_department_id);
1680 if (p_time_entry_source = 'clock') then --time entry source is 'clock'
1681 if (l_charge_time_resources_pref = '2') then
1682 l_cost_flag := 'N';
1683 elsif (p_resource_seq_num is null) then --resource is ad-hoc
1684 l_cost_flag := 'N';
1685
1686 l_manual_exist := 0;
1687 open has_autocharge_manual_cursor;
1688 fetch has_autocharge_manual_cursor into l_manual_exist;
1689 close has_autocharge_manual_cursor;
1690
1691 if (l_manual_exist = 1 ) then
1692 l_cost_flag := 'Y';
1693 end if;
1694 else
1695 open autocharge_type_res_cursor;
1696 fetch autocharge_type_res_cursor into l_autocharge_type;
1697 if autocharge_type_res_cursor%NOTFOUND then
1698 l_cost_flag := 'N';
1699 elsif (l_autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL) then --resource is manual
1700 l_cost_flag := 'Y';
1701 else
1702 l_cost_flag := 'N';
1703 end if;
1704 close autocharge_type_res_cursor;
1705 end if;
1706 else --time entry source is 'mytime', 'jobop' or 'resource'
1707 if (l_charge_time_resources_pref = '2') then
1708 l_cost_flag := 'N';
1709 else
1710 l_cost_flag := 'Y';
1711 end if;
1712 end if;
1713 end if;
1714 if (l_logLevel <= wip_constants.trace_logging) then
1715 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.process_time_records',
1716 p_procReturnStatus => l_cost_flag,
1717 p_msg => 'normal completion',
1718 x_returnStatus => l_return_status);
1719 end if;
1720 return l_cost_flag;
1721 END get_cost_flag;
1722
1723 /* Get the value for add_to_rtg
1724 Since the insertion of a wor is done through cost txn, so this is simplified */
1725 FUNCTION get_add_to_rtg_flag(p_wip_entity_id IN NUMBER,
1726 p_operation_seq_num IN NUMBER,
1727 p_resource_seq_num IN NUMBER,
1728 p_cost_flag IN VARCHAR2,
1729 p_time_entry_source IN VARCHAR2) return VARCHAR2
1730 IS
1731 l_add_to_rtg_flag varchar2(1);
1732 l_cost_flag varchar(1);
1733 BEGIN
1734 l_cost_flag := p_cost_flag;
1735
1736 if( l_cost_flag is null ) then
1737 l_cost_flag := get_cost_flag(p_wip_entity_id => p_wip_entity_id,
1738 p_operation_seq_num => p_operation_seq_num,
1739 p_resource_seq_num => p_resource_seq_num,
1740 p_time_entry_source => p_time_entry_source);
1741 end if;
1742
1743 if (l_cost_flag = 'Y' and p_resource_seq_num is null) then
1744 l_add_to_rtg_flag := 'Y';
1745 else
1746 l_add_to_rtg_flag := 'N';
1747 end if;
1748
1749 return l_add_to_rtg_flag;
1750 END get_add_to_rtg_flag;
1751
1752 -- Get Organization Id and Department Id.
1753 PROCEDURE get_org_dept_ids(p_wip_entity_id IN NUMBER,
1754 p_operation_seq_num IN NUMBER,
1755 x_organization_id out NOCOPY NUMBER,
1756 x_department_id out NOCOPY NUMBER)
1757 IS
1758 l_organization_id number;
1759 l_department_id number;
1760
1761 cursor org_dept_cursor is select wo.organization_id, wo.department_id
1762 from wip_operations wo
1763 where wo.wip_entity_id = p_wip_entity_id
1764 and wo.operation_seq_num = p_operation_seq_num;
1765 BEGIN
1766 open org_dept_cursor;
1767 fetch org_dept_cursor into l_organization_id, l_department_id;
1768 if org_dept_cursor%NOTFOUND then
1769 l_organization_id := -1;
1770 l_department_id := -1;
1771 end if;
1772 x_organization_id := l_organization_id;
1773 x_department_id := l_department_id;
1774 close org_dept_cursor;
1775 END get_org_dept_ids;
1776
1777 -- Update the value of actual start date in wdj, wo and wor tables
1778 PROCEDURE update_actual_start_dates(p_wip_entity_id IN NUMBER,
1779 p_operation_seq_num IN NUMBER,
1780 p_resource_seq_num IN NUMBER)
1781 IS
1782 l_min_start_date date;
1783
1784 l_min_start_date_wrat date;
1785
1786 l_return_status varchar2(1);
1787 l_params wip_logger.param_tbl_t;
1788 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1789 cursor min_start_date_wrat_cursor is select min(wrat.start_date)
1790 from wip_resource_actual_times wrat
1791 where wrat.wip_entity_id = p_wip_entity_id
1792 and wrat.operation_seq_num = p_operation_seq_num
1793 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1794 and wrat.start_date is not null
1795 and wrat.process_status <> 4;
1796
1797 cursor min_start_date_wor_cursor is select min(wor.actual_start_date)
1798 from wip_operation_resources wor
1799 where wor.wip_entity_id = p_wip_entity_id
1800 and wor.operation_seq_num = p_operation_seq_num
1801 and wor.actual_start_date is not null;
1802
1803 cursor min_start_date_wo_cursor is select min(wo.actual_start_date)
1804 from wip_operations wo
1805 where wo.wip_entity_id = p_wip_entity_id
1806 and wo.actual_start_date is not null;
1807 BEGIN
1808
1809 if(l_logLevel <= wip_constants.trace_logging) then
1810 l_params(1).paramName := 'p_wip_entity_id';
1811 l_params(1).paramValue := p_wip_entity_id;
1812 l_params(2).paramName := 'p_operation_seq_num';
1813 l_params(2).paramValue := p_operation_seq_num;
1814 l_params(3).paramName := 'p_resource_seq_num';
1815 l_params(3).paramValue := p_resource_seq_num;
1816
1817 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_start_dates',
1818 p_params => l_params,
1819 x_returnStatus => l_return_status);
1820 if(l_return_status <> fnd_api.g_ret_sts_success) then
1821 raise fnd_api.g_exc_unexpected_error;
1822 end if;
1823 end if;
1824 l_min_start_date := null;
1825 open min_start_date_wrat_cursor;
1826 fetch min_start_date_wrat_cursor into l_min_start_date;
1827 if min_start_date_wrat_cursor%FOUND then
1828 if l_min_start_date is not null then
1829 update wip_operation_resources set
1830 actual_start_date = l_min_start_date
1831 where wip_entity_id = p_wip_entity_id
1832 and operation_seq_num = p_operation_seq_num
1833 and resource_seq_num = p_resource_seq_num;
1834 end if;
1835 end if;
1836 close min_start_date_wrat_cursor;
1837
1838 /* save it */
1839 l_min_start_date_wrat := l_min_start_date;
1840
1841 l_min_start_date := null;
1842 open min_start_date_wor_cursor;
1843 fetch min_start_date_wor_cursor into l_min_start_date;
1844 if( l_min_start_date_wrat is not null ) then
1845 select decode(l_min_start_date, null, l_min_start_date_wrat,
1846 least(l_min_start_date_wrat, l_min_start_date) )
1847 into l_min_start_date
1848 from dual;
1849 end if;
1850
1851 if l_min_start_date is not null then
1852 update wip_operations set
1853 actual_start_date = l_min_start_date
1854 where wip_entity_id = p_wip_entity_id
1855 and operation_seq_num = p_operation_seq_num;
1856 end if;
1857 close min_start_date_wor_cursor;
1858
1859 open min_start_date_wo_cursor;
1860 fetch min_start_date_wo_cursor into l_min_start_date;
1861 if min_start_date_wo_cursor%FOUND then
1862 if l_min_start_date is not null then
1863 update wip_discrete_jobs set
1864 actual_start_date = l_min_start_date
1865 where wip_entity_id = p_wip_entity_id;
1866 end if;
1867 end if;
1868 close min_start_date_wo_cursor;
1869 if (l_logLevel <= wip_constants.trace_logging) then
1870 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_start_dates',
1871 p_procReturnStatus => l_return_status,
1872 p_msg => 'normal completion',
1873 x_returnStatus => l_return_status);
1874 end if;
1875 END update_actual_start_dates;
1876
1877 -- Update the value of actual completion date in wo and wor tables
1878 PROCEDURE update_actual_completion_dates(p_wip_entity_id IN NUMBER,
1879 p_operation_seq_num IN NUMBER,
1880 p_resource_seq_num IN NUMBER)
1881 IS
1882 l_max_end_date date;
1883 l_active_next_resource_flag varchar2(1);
1884
1885 l_return_status varchar2(1);
1886 l_params wip_logger.param_tbl_t;
1887 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1888 cursor max_end_date_wrat_cursor is select max(wrat.end_date)
1889 from wip_resource_actual_times wrat
1890 where wrat.wip_entity_id = p_wip_entity_id
1891 and wrat.operation_seq_num = p_operation_seq_num
1892 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1893 and not exists (select wrat.end_date
1894 from wip_resource_actual_times wrat
1895 where wrat.wip_entity_id = p_wip_entity_id
1896 and wrat.operation_seq_num = p_operation_seq_num
1897 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1898 and wrat.end_date is null
1899 and wrat.process_status <> 4)
1900 and wrat.process_status <> 4;
1901
1902 --find out if there are any active next resources
1903 cursor active_next_resource_cursor is select 'Y'
1904 from wip_resource_actual_times wrat,
1905 wip_operation_resources wor
1906 where wrat.wip_entity_id = p_wip_entity_id
1907 and wrat.operation_seq_num = p_operation_seq_num
1908 and wrat.status_type = 1
1909 and wrat.process_status <> 4
1910 and wrat.end_date is null
1911 and wor.scheduled_flag = 4
1912 and wrat.wip_entity_id = wor.wip_entity_id
1913 and wrat.operation_seq_num = wor.operation_seq_num
1914 and wrat.resource_id = wor.resource_id;
1915
1916 cursor max_end_date_wor_cursor is select max(wor.actual_completion_date)
1917 from wip_operation_resources wor
1918 where wor.wip_entity_id = p_wip_entity_id
1919 and wor.operation_seq_num = p_operation_seq_num
1920 and not exists (select wor.actual_completion_date
1921 from wip_operation_resources wor
1922 where wor.wip_entity_id = p_wip_entity_id
1923 and wor.operation_seq_num = p_operation_seq_num
1924 and wor.actual_completion_date is null);
1925 BEGIN
1926 if(l_logLevel <= wip_constants.trace_logging) then
1927 l_params(1).paramName := 'p_wip_entity_id';
1928 l_params(1).paramValue := p_wip_entity_id;
1929 l_params(2).paramName := 'p_operation_seq_num';
1930 l_params(2).paramValue := p_operation_seq_num;
1931 l_params(3).paramName := 'p_resource_seq_num';
1932 l_params(3).paramValue := p_resource_seq_num;
1933
1934 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_completion_dates',
1935 p_params => l_params,
1936 x_returnStatus => l_return_status);
1937 if(l_return_status <> fnd_api.g_ret_sts_success) then
1938 raise fnd_api.g_exc_unexpected_error;
1939 end if;
1940 end if;
1941 open max_end_date_wrat_cursor;
1942 fetch max_end_date_wrat_cursor into l_max_end_date;
1943 if max_end_date_wrat_cursor%FOUND then
1944 if l_max_end_date is not null then
1945 update wip_operation_resources set
1946 actual_completion_date = l_max_end_date
1947 where wip_entity_id = p_wip_entity_id
1948 and operation_seq_num = p_operation_seq_num
1949 and resource_seq_num = p_resource_seq_num;
1950 end if;
1951 end if;
1952 close max_end_date_wrat_cursor;
1953
1954 open active_next_resource_cursor;
1955 fetch active_next_resource_cursor into l_active_next_resource_flag;
1956 if active_next_resource_cursor%NOTFOUND then
1957 open max_end_date_wor_cursor;
1958 fetch max_end_date_wor_cursor into l_max_end_date;
1959 if max_end_date_wor_cursor%FOUND then
1960 if l_max_end_date is not null then
1961 update wip_operations set
1962 actual_completion_date = l_max_end_date
1963 where wip_entity_id = p_wip_entity_id
1964 and operation_seq_num = p_operation_seq_num;
1965 end if;
1966 end if;
1967 close max_end_date_wor_cursor;
1968 end if;
1969 close active_next_resource_cursor;
1970 if (l_logLevel <= wip_constants.trace_logging) then
1971 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_completion_dates',
1972 p_procReturnStatus => l_return_status,
1973 p_msg => 'normal completion',
1974 x_returnStatus => l_return_status);
1975 end if;
1976 END update_actual_completion_dates;
1977
1978 -- Update the value of projected completion date in wo and wor tables.
1979 PROCEDURE update_proj_completion_dates(p_organization_id IN NUMBER,
1980 p_wip_entity_id IN NUMBER,
1981 p_operation_seq_num IN NUMBER,
1982 p_resource_seq_num IN NUMBER,
1983 p_resource_id IN NUMBER,
1984 p_start_date IN DATE)
1985 IS
1986 l_projected_completion_date date;
1987 l_return_status varchar2(1);
1988 l_params wip_logger.param_tbl_t;
1989 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1990 BEGIN
1991 if(l_logLevel <= wip_constants.trace_logging) then
1992 l_params(1).paramName := 'p_organization_id';
1993 l_params(1).paramValue := p_organization_id;
1994 l_params(2).paramName := 'p_wip_entity_id';
1995 l_params(2).paramValue := p_wip_entity_id;
1996 l_params(3).paramName := 'p_operation_seq_num';
1997 l_params(3).paramValue := p_operation_seq_num;
1998 l_params(4).paramName := 'p_resource_seq_num';
1999 l_params(4).paramValue := p_resource_seq_num;
2000 l_params(5).paramName := 'p_resource_id';
2001 l_params(5).paramValue := p_resource_id;
2002 l_params(6).paramName := 'p_start_date';
2003 l_params(6).paramValue := p_start_date;
2004
2005 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_proj_completion_dates',
2006 p_params => l_params,
2007 x_returnStatus => l_return_status);
2008 if(l_return_status <> fnd_api.g_ret_sts_success) then
2009 raise fnd_api.g_exc_unexpected_error;
2010 end if;
2011 end if;
2012 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
2013 p_wip_entity_id => p_wip_entity_id,
2014 p_op_seq_num => p_operation_seq_num,
2015 p_resource_seq_num => p_resource_seq_num,
2016 p_resource_id => p_resource_id,
2017 p_instance_id => null,
2018 p_start_date => p_start_date);
2019 update wip_operation_resources set
2020 projected_completion_date = l_projected_completion_date
2021 where wip_entity_id = p_wip_entity_id
2022 and operation_seq_num = p_operation_seq_num
2023 and resource_seq_num = p_resource_seq_num;
2024
2025 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
2026 p_wip_entity_id => p_wip_entity_id,
2027 p_op_seq_num => p_operation_seq_num,
2028 p_resource_seq_num => null,
2029 p_resource_id => null,
2030 p_instance_id => null,
2031 p_start_date => p_start_date);
2032 update wip_operations set
2033 projected_completion_date = l_projected_completion_date
2034 where wip_entity_id = p_wip_entity_id
2035 and operation_seq_num = p_operation_seq_num;
2036 if (l_logLevel <= wip_constants.trace_logging) then
2037 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_proj_completion_dates',
2038 p_procReturnStatus => l_return_status,
2039 p_msg => 'normal completion',
2040 x_returnStatus => l_return_status);
2041 end if;
2042 END update_proj_completion_dates;
2043
2044 -- Bug 15945996 Adde this function to get the job on status of all the operations of the given job.
2045 FUNCTION is_job_on(p_wip_entity_id IN NUMBER) return VARCHAR2
2046 IS
2047 l_job_status varchar2(1);
2048 l_employee_id number;
2049 cursor job_on_off_status_cursor is select employee_id
2050 from wip_operations wo
2051 where wo.wip_entity_id = p_wip_entity_id;
2052 BEGIN
2053 l_job_status := 'N';
2054 open job_on_off_status_cursor;
2055 loop
2056 fetch job_on_off_status_cursor into l_employee_id;
2057 exit when job_on_off_status_cursor%NOTFOUND;
2058 if job_on_off_status_cursor%FOUND then
2059 if l_employee_id is not null then
2060 l_job_status := 'Y';
2061 exit;
2062 end if;
2063 end if;
2064 end loop;
2065 close job_on_off_status_cursor;
2066 return l_job_status;
2067 END is_job_on;
2068
2069 -- Get the on/off status of the job.
2070 FUNCTION get_job_on_off_status(p_wip_entity_id IN NUMBER,
2071 p_operation_seq_num IN NUMBER) return VARCHAR2
2072 IS
2073 l_job_status varchar2(1);
2074 l_employee_id number;
2075
2076 cursor job_status_cursor is select employee_id
2077 from wip_operations wo
2078 where wo.wip_entity_id = p_wip_entity_id
2079 and wo.operation_seq_num = p_operation_seq_num;
2080
2081 BEGIN
2082 l_job_status := 'N';
2083
2084 open job_status_cursor;
2085 fetch job_status_cursor into l_employee_id;
2086 if job_status_cursor%FOUND then
2087 if l_employee_id is not null then
2088 l_job_status := 'Y';
2089 end if;
2090 end if;
2091 close job_status_cursor;
2092
2093
2094 return l_job_status;
2095 END get_job_on_off_status;
2096
2097 -- Set job on.
2098 PROCEDURE job_on(p_wip_entity_id IN NUMBER,
2099 p_operation_seq_num IN NUMBER,
2100 p_employee_id IN NUMBER,
2101 x_status out NOCOPY VARCHAR2,
2102 x_msg_count out NOCOPY NUMBER,
2103 x_msg out NOCOPY VARCHAR2)
2104 IS
2105 l_job_status varchar2(1);
2106 l_status varchar2(1);
2107 l_msg_count number;
2108 l_msg varchar2(10);
2109 lx_return_status varchar2(10);
2110 l_params wip_logger.param_tbl_t;
2111 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2112 BEGIN
2113 if(l_logLevel <= wip_constants.trace_logging) then
2114 l_params(1).paramName := 'p_employee_id';
2115 l_params(1).paramValue := p_employee_id;
2116 l_params(2).paramName := 'p_wip_entity_id';
2117 l_params(2).paramValue := p_wip_entity_id;
2118 l_params(3).paramName := 'p_operation_seq_num';
2119 l_params(3).paramValue := p_operation_seq_num;
2120
2121 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_on',
2122 p_params => l_params,
2123 x_returnStatus => l_status);
2124 if(l_status <> fnd_api.g_ret_sts_success) then
2125 raise fnd_api.g_exc_unexpected_error;
2126 end if;
2127 end if;
2128 l_status := 'U';
2129 l_job_status := get_job_on_off_status(p_wip_entity_id => p_wip_entity_id,
2130 p_operation_seq_num => p_operation_seq_num);
2131
2132 if (l_job_status = 'Y') then
2133 l_status := 'O';
2134 elsif (l_job_status = 'N') then
2135 update wip_operations set
2136 employee_id = p_employee_id
2137 where wip_entity_id = p_wip_entity_id
2138 and operation_seq_num = p_operation_seq_num;
2139 l_status := 'S';
2140 end if;
2141
2142 x_status := l_status;
2143 x_msg_count := l_msg_count;
2144 x_msg := l_msg;
2145 if (l_logLevel <= wip_constants.trace_logging) then
2146 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_on',
2147 p_procReturnStatus => l_status,
2148 p_msg => 'normal completion',
2149 x_returnStatus => l_status);
2150 end if;
2151 END job_on;
2152
2153
2154 -- Set job off.
2155 PROCEDURE job_off(p_wip_entity_id IN NUMBER,
2156 p_operation_seq_num IN NUMBER,
2157 x_status out NOCOPY VARCHAR2,
2158 x_msg_count out NOCOPY NUMBER,
2159 x_msg out NOCOPY VARCHAR2)
2160 IS
2161 l_job_status varchar2(1);
2162
2163 l_status varchar2(1);
2164 l_msg_count number;
2165 l_msg varchar2(255);
2166 l_params wip_logger.param_tbl_t;
2167 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2168 BEGIN
2169 if(l_logLevel <= wip_constants.trace_logging) then
2170 l_params(1).paramName := 'p_wip_entity_id';
2171 l_params(1).paramValue := p_wip_entity_id;
2172 l_params(2).paramName := 'p_operation_seq_num';
2173 l_params(2).paramValue := p_operation_seq_num;
2174
2175 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_off',
2176 p_params => l_params,
2177 x_returnStatus => l_status);
2178 if(l_status <> fnd_api.g_ret_sts_success) then
2179 raise fnd_api.g_exc_unexpected_error;
2180 end if;
2181 end if;
2182 l_status := 'U';
2183 l_job_status := get_job_on_off_status(p_wip_entity_id => p_wip_entity_id,
2184 p_operation_seq_num => p_operation_seq_num);
2185
2186 if (l_job_status = 'N') then
2187 l_status := 'S'; -- Bug 13961684 altered return status when job was already off as to return normally.
2188 -- This case only showed up when clocking into an already completed job and would throw
2189 -- an unexpected exception instead of normally returning.
2190 elsif (l_job_status = 'Y') then
2191
2192 /* clock out labors */
2193 clock_out_labors(p_wip_entity_id, p_operation_seq_num, null);
2194
2195 /* clock out all machines as well */
2196 clock_out_machines(p_wip_entity_id, p_operation_seq_num);
2197
2198 /* remove the employee stamp */
2199 job_off_internal(p_wip_entity_id, p_operation_seq_num);
2200
2201 /* process the records */
2202 process_time_records(p_wip_entity_id => p_wip_entity_id,
2203 p_completed_op => p_operation_seq_num,
2204 p_instance_id => null,
2205 p_time_entry_source => 'clock');
2206 l_status := 'S';
2207 end if;
2208
2209 x_status := l_status;
2210 x_msg_count := l_msg_count;
2211 x_msg := l_msg;
2212 if (l_logLevel <= wip_constants.trace_logging) then
2213 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.job_off',
2214 p_procReturnStatus => l_status,
2215 p_msg => 'normal completion',
2216 x_returnStatus => l_status);
2217 end if;
2218 END job_off;
2219 --Validate for Valid Employee.
2220 --Bug12747250 BadgeNumber is varchar2
2221 PROCEDURE emp_valid(p_wip_employee_id IN VARCHAR2,
2222 p_org_id IN NUMBER,
2223 x_status OUT nocopy Boolean,
2224 x_person_id OUT nocopy number)
2225 IS
2226 l_status boolean:=true;
2227 l_person_id NUMBER := null;
2228
2229 CURSOR emp_valid_cursor IS
2230 select bre.person_id
2231 from per_all_people_f papf,
2232 bom_resource_employees bre
2233 where papf.person_id = bre.person_id
2234 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
2235 and bre.organization_id = p_org_id
2236 and papf.employee_number = to_char(p_wip_employee_id)
2237 and rownum=1;
2238
2239 --Bug 14595358, Contingent Worker Project
2240 CURSOR npw_valid_cursor IS
2241 select bre.person_id
2242 from per_all_people_f papf,
2243 bom_resource_employees bre
2244 where papf.person_id = bre.person_id
2245 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
2246 and bre.organization_id = p_org_id
2247 and papf.npw_number = to_char(p_wip_employee_id)
2248 and rownum=1;
2249
2250 BEGIN
2251 --Bug 14595358, Contingent Worker Project
2252 OPEN emp_valid_cursor;
2253 FETCH emp_valid_cursor INTO l_person_id;
2254 --if couldn't find the employee number, most likely it will be contingent worker
2255 if(emp_valid_cursor%NOTFOUND) then
2256 OPEN npw_valid_cursor;
2257 FETCH npw_valid_cursor INTO l_person_id;
2258 close npw_valid_cursor;
2259 end if;
2260 close emp_valid_cursor;
2261
2262 IF (l_person_id is not null) THEN
2263 l_status := WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,l_person_id);
2264 end if;
2265
2266 x_status := l_status;
2267 x_person_id := l_person_id;
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 x_person_id := l_person_id;
2271 x_status := l_status;
2272 END emp_valid;
2273
2274 -- Set Shift in
2275 --Bug12747250 BadgeNumber is varchar2
2276 PROCEDURE shift_in(p_wip_employee_id IN VARCHAR2,
2277 p_org_id IN NUMBER,
2278 x_status OUT nocopy VARCHAR2)
2279 IS
2280 l_shift_status VARCHAR2(1);
2281 l_dummy_var NUMBER;
2282 badge_validation Boolean:=FALSE;
2283 l_person_id number;
2284 l_time_entry number;
2285 l_ret_status varchar2(200);
2286 l_params wip_logger.param_tbl_t;
2287 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2288
2289 CURSOR shift_in_cursor IS
2290 SELECT 1 FROM wip_resource_actual_times wrat
2291 WHERE wrat.wip_entity_id IS NULL
2292 AND wrat.end_date IS NULL
2293 and wrat.employee_id = l_person_id
2294 and wrat.time_entry_mode = 8
2295 and organization_id = p_org_id;
2296 BEGIN
2297 if(l_logLevel <= wip_constants.trace_logging) then
2298 l_params(1).paramName := 'p_wip_employee_id';
2299 l_params(1).paramValue := p_wip_employee_id;
2300 l_params(2).paramName := 'p_org_id';
2301 l_params(2).paramValue := p_org_id;
2302
2303 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_in',
2304 p_params => l_params,
2305 x_returnStatus => x_status);
2306 if(x_status <> fnd_api.g_ret_sts_success) then
2307 raise fnd_api.g_exc_unexpected_error;
2308 end if;
2309 end if;
2310 l_shift_status := 'U';
2311
2312 emp_valid(p_wip_employee_id =>p_wip_employee_id,
2313 p_org_id =>p_org_id,
2314 x_status =>badge_validation,
2315 x_person_id=>l_person_id);
2316
2317 OPEN shift_in_cursor;
2318 FETCH shift_in_cursor INTO l_dummy_var;
2319
2320 IF (badge_validation = true) then
2321 l_shift_status :='N';
2322 if(l_logLevel <= wip_constants.trace_logging) then
2323 wip_logger.log('l_shift_status: ' || l_shift_status, l_ret_status);
2324 end if;
2325 ELSIF shift_in_cursor % FOUND THEN
2326 --Already Shifted in
2327 l_shift_status := 'C';
2328 ELSE
2329 if(l_logLevel <= wip_constants.trace_logging) then
2330 wip_logger.log('inserting into wip_resource_actual_times', l_ret_status);
2331 end if;
2332 --shift in employee for which badge number was entered
2333 -- removed hardcoding of resource_id for bug 6969269.
2334 insert into wip_resource_actual_times
2335 (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
2336 OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
2337 INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
2338 COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
2339 END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
2340 UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
2341 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2342 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
2343 ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
2344 PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
2345 values
2346 (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
2347 null,null,null,null,null,null,8,'N','N',null,
2348 sysdate,null,null,null,
2349 fnd_profile.value('BOM:HOUR_UOM_CODE'),
2350 l_person_id,1,fnd_global.user_id,
2351 sysdate,fnd_global.user_id,sysdate,
2352 fnd_global.login_id,1,null,null,null,
2353 null,null);
2354
2355 l_shift_status := 'S';
2356 END IF;
2357 CLOSE shift_in_cursor;
2358 x_status := l_shift_status;
2359 --fix logging issue. x_status is the output parameter for this procedure
2360 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2361 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2362 if (l_logLevel <= wip_constants.trace_logging) then
2363 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_in',
2364 p_procReturnStatus => x_status,
2365 p_msg => 'normal completion',
2366 x_returnStatus => l_ret_status);
2367 end if;
2368 END shift_in;
2369
2370 -- Set Shift Out
2371 ----Bug12747250 BadgeNumber is varchar2
2372 PROCEDURE shift_out(p_wip_employee_id IN VARCHAR2,
2373 p_org_id IN NUMBER,
2374 x_status out NOCOPY VARCHAR2)
2375 is
2376 l_shift_status varchar2(1);
2377 l_start_date date;
2378 l_date date;
2379 l_duration number;
2380 badge_validation Boolean:= false;
2381 l_person_id number;
2382 l_params wip_logger.param_tbl_t;
2383 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2384 l_ret_status varchar2(1);
2385
2386 cursor shift_out_test_cursor is
2387 select start_date from wip_resource_actual_times wrat
2388 where wrat.wip_entity_id is null
2389 and wrat.EMPLOYEE_ID=l_person_id
2390 and wrat.ORGANIZATION_ID=p_org_id
2391 and wrat.end_date is null
2392 and wrat.time_entry_mode = 8;
2393 BEGIN
2394 if(l_logLevel <= wip_constants.trace_logging) then
2395 l_params(1).paramName := 'p_wip_employee_id';
2396 l_params(1).paramValue := p_wip_employee_id;
2397 l_params(2).paramName := 'p_org_id';
2398 l_params(2).paramValue := p_org_id;
2399
2400 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_out',
2401 p_params => l_params,
2402 x_returnStatus => x_status);
2403 if(x_status <> fnd_api.g_ret_sts_success) then
2404 raise fnd_api.g_exc_unexpected_error;
2405 end if;
2406 end if;
2407 l_shift_status := 'U';
2408
2409 emp_valid(p_wip_employee_id =>p_wip_employee_id,
2410 p_org_id =>p_org_id,
2411 x_status=>badge_validation,
2412 x_person_id=>l_person_id);
2413
2414 open shift_out_test_cursor;
2415 fetch shift_out_test_cursor into l_start_date;
2416
2417 IF (badge_validation = true) then
2418 l_shift_status :='N';
2419 elsif shift_out_test_cursor%NOTFOUND then
2420 l_shift_status := 'O';
2421 else
2422 l_date := sysdate;
2423 l_duration := (l_date - l_start_date)*24;
2424
2425 update wip_resource_actual_times set
2426 end_date = l_date,
2427 duration = l_duration
2428 where ORGANIZATION_ID=p_org_id
2429 and employee_id =l_person_id
2430 and end_date is null
2431 and wip_entity_id is null;
2432
2433 l_shift_status := 'S';
2434 end if;
2435 close shift_out_test_cursor;
2436 x_status := l_shift_status;
2437 --fix logging issue. x_status is the output parameter for this procedure
2438 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2439 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2440 if (l_logLevel <= wip_constants.trace_logging) then
2441 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_out',
2442 p_procReturnStatus => x_status,
2443 p_msg => 'normal completion',
2444 x_returnStatus => l_ret_status);
2445 end if;
2446 END shift_out;
2447
2448 -- Set Undo Shift In
2449 --Bug12747250 BadgeNumber is varchar2
2450 PROCEDURE undo_shift_in(p_wip_employee_id IN VARCHAR2,
2451 p_org_id IN NUMBER,
2452 x_status out NOCOPY VARCHAR2)
2453 IS
2454 l_shift_status varchar2(1);
2455 l_dummy_var number;
2456 badge_validation Boolean :=false;
2457 l_person_id number;
2458 l_ret_status varchar2(1);
2459
2460 l_params wip_logger.param_tbl_t;
2461 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2462 cursor undo_shift_in_test_cursor is
2463 select 1 from wip_resource_actual_times wrat
2464 where wrat.wip_entity_id is null
2465 and wrat.EMPLOYEE_ID=l_person_id
2466 and wrat.ORGANIZATION_ID=p_org_id
2467 and wrat.end_date is null;
2468 BEGIN
2469 if(l_logLevel <= wip_constants.trace_logging) then
2470 l_params(1).paramName := 'p_wip_employee_id';
2471 l_params(1).paramValue := p_wip_employee_id;
2472 l_params(2).paramName := 'p_org_id';
2473 l_params(2).paramValue := p_org_id;
2474
2475 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_shift_in',
2476 p_params => l_params,
2477 x_returnStatus => x_status);
2478 if(x_status <> fnd_api.g_ret_sts_success) then
2479 raise fnd_api.g_exc_unexpected_error;
2480 end if;
2481 end if;
2482 l_shift_status := 'U';
2483
2484 emp_valid(p_wip_employee_id =>p_wip_employee_id,
2485 p_org_id =>p_org_id,
2486 x_status=>badge_validation,
2487 x_person_id=>l_person_id);
2488
2489 open undo_shift_in_test_cursor;
2490 fetch undo_shift_in_test_cursor into l_dummy_var;
2491
2492 IF (badge_validation = true) then
2493 l_shift_status :='N';
2494 elsif undo_shift_in_test_cursor%NOTFOUND then
2495 l_shift_status := 'O';
2496 else
2497 delete from wip_resource_actual_times wrat
2498 where wrat.wip_entity_id is null
2499 and wrat.EMPLOYEE_ID=l_person_id
2500 and wrat.ORGANIZATION_ID=p_org_id
2501 and wrat.end_date is null;
2502 l_shift_status := 'S';
2503 end if;
2504 close undo_shift_in_test_cursor;
2505 x_status := l_shift_status;
2506 --fix logging issue. x_status is the output parameter for this procedure
2507 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2508 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2509 if (l_logLevel <= wip_constants.trace_logging) then
2510 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_shift_in',
2511 p_procReturnStatus => x_status,
2512 p_msg => 'normal completion',
2513 x_returnStatus => l_ret_status);
2514 end if;
2515 END undo_shift_in;
2516
2517 --User mode Shift functionality
2518 PROCEDURE shift_in_UM(p_wip_employee_id IN NUMBER,
2519 p_org_id IN NUMBER,
2520 x_status OUT nocopy VARCHAR2)
2521 IS
2522 l_shift_status VARCHAR2(1);
2523 l_dummy_var NUMBER;
2524 badge_validation Boolean:=FALSE;
2525 l_person_id number;
2526 l_time_entry number;
2527 l_ret_status varchar2(200);
2528 l_params wip_logger.param_tbl_t;
2529 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2530
2531 CURSOR shift_in_cursor IS
2532 SELECT 1 FROM wip_resource_actual_times wrat
2533 WHERE wrat.wip_entity_id IS NULL
2534 AND wrat.end_date IS NULL
2535 and wrat.employee_id = p_wip_employee_id
2536 and wrat.time_entry_mode = 8;
2537
2538 BEGIN
2539 if(l_logLevel <= wip_constants.trace_logging) then
2540 l_params(1).paramName := 'p_wip_employee_id';
2541 l_params(1).paramValue := p_wip_employee_id;
2542 l_params(2).paramName := 'p_org_id';
2543 l_params(2).paramValue := p_org_id;
2544
2545 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_in_UM',
2546 p_params => l_params,
2547 x_returnStatus => x_status);
2548 if(x_status <> fnd_api.g_ret_sts_success) then
2549 raise fnd_api.g_exc_unexpected_error;
2550 end if;
2551 end if;
2552 l_shift_status := 'U';
2553
2554 badge_validation := WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2555
2556 OPEN shift_in_cursor;
2557 FETCH shift_in_cursor INTO l_dummy_var;
2558
2559 IF (badge_validation = true) then
2560 l_shift_status :='N';
2561 ELSIF shift_in_cursor % FOUND THEN
2562 --Already Shifted in
2563 l_shift_status := 'C';
2564 ELSE
2565 --shift in employee for which badge number was entered
2566 -- removed hardcoding of resource_id for bug 6969269.
2567 insert into wip_resource_actual_times
2568 (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
2569 OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
2570 INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
2571 COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
2572 END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
2573 UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
2574 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2575 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
2576 ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
2577 PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
2578 values
2579 (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
2580 null,null,null,null,null,null,8,'N','N',null,
2581 sysdate,null,null,null,
2582 fnd_profile.value('BOM:HOUR_UOM_CODE'),
2583 p_wip_employee_id,1,fnd_global.user_id,
2584 sysdate,fnd_global.user_id,sysdate,
2585 fnd_global.login_id,1,null,null,null,
2586 null,null);
2587 l_shift_status := 'S';
2588 END IF;
2589 CLOSE shift_in_cursor;
2590 x_status := l_shift_status;
2591 --fix logging issue. x_status is the output parameter for this procedure
2592 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2593 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2594 if (l_logLevel <= wip_constants.trace_logging) then
2595 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_in_UM',
2596 p_procReturnStatus => x_status,
2597 p_msg => 'normal completion',
2598 x_returnStatus => l_ret_status);
2599 end if;
2600 END shift_in_UM;
2601
2602 -- Set Shift Out
2603 PROCEDURE shift_out_UM(p_wip_employee_id IN NUMBER,
2604 p_org_id IN NUMBER,
2605 x_status out NOCOPY VARCHAR2)
2606 is
2607 l_shift_status varchar2(1);
2608 l_start_date date;
2609 l_date date;
2610 l_duration number;
2611 badge_validation Boolean:= false;
2612 l_person_id number;
2613 l_ret_status varchar2(1);
2614
2615 l_params wip_logger.param_tbl_t;
2616 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2617 cursor shift_out_test_cursor is
2618 select start_date from wip_resource_actual_times wrat
2619 where wrat.wip_entity_id is null
2620 and wrat.EMPLOYEE_ID=p_wip_employee_id
2621 and wrat.ORGANIZATION_ID=p_org_id
2622 and wrat.end_date is null;
2623
2624 BEGIN
2625 if(l_logLevel <= wip_constants.trace_logging) then
2626 l_params(1).paramName := 'p_wip_employee_id';
2627 l_params(1).paramValue := p_wip_employee_id;
2628 l_params(2).paramName := 'p_org_id';
2629 l_params(2).paramValue := p_org_id;
2630
2631 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_out_UM',
2632 p_params => l_params,
2633 x_returnStatus => x_status);
2634 if(x_status <> fnd_api.g_ret_sts_success) then
2635 raise fnd_api.g_exc_unexpected_error;
2636 end if;
2637 end if;
2638 l_shift_status := 'U';
2639
2640 badge_validation := WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2641
2642 open shift_out_test_cursor;
2643 fetch shift_out_test_cursor into l_start_date;
2644
2645 IF (badge_validation = true) then
2646 l_shift_status :='N';
2647 elsif shift_out_test_cursor%NOTFOUND then
2648 l_shift_status := 'O';
2649 else
2650 l_date := sysdate;
2651 l_duration := (l_date - l_start_date)*24;
2652
2653 update wip_resource_actual_times set
2654 end_date = l_date,
2655 duration = l_duration
2656 where ORGANIZATION_ID=p_org_id
2657 and employee_id =p_wip_employee_id
2658 and end_date is null
2659 and wip_entity_id is null;
2660 l_shift_status := 'S';
2661 end if;
2662 close shift_out_test_cursor;
2663 x_status := l_shift_status;
2664 --fix logging issue. x_status is the output parameter for this procedure
2665 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2666 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2667 if (l_logLevel <= wip_constants.trace_logging) then
2668 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.shift_out_UM',
2669 p_procReturnStatus => x_status,
2670 p_msg => 'normal completion',
2671 x_returnStatus => l_ret_status);
2672 end if;
2673 END shift_out_UM;
2674
2675 -- Set Undo Shift In
2676 PROCEDURE undo_shift_in_UM(p_wip_employee_id IN NUMBER,
2677 p_org_id IN NUMBER,
2678 x_status out NOCOPY VARCHAR2)
2679 IS
2680 l_shift_status varchar2(1);
2681 l_dummy_var number;
2682 badge_validation Boolean :=false;
2683 l_person_id number;
2684 l_params wip_logger.param_tbl_t;
2685 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2686 l_ret_status varchar2(1);
2687
2688 cursor undo_shift_in_test_cursor is
2689 select 1
2690 from wip_resource_actual_times wrat
2691 where wrat.wip_entity_id is null
2692 and wrat.EMPLOYEE_ID=p_wip_employee_id
2693 and wrat.ORGANIZATION_ID=p_org_id
2694 and wrat.end_date is null;
2695 BEGIN
2696 if(l_logLevel <= wip_constants.trace_logging) then
2697 l_params(1).paramName := 'p_wip_employee_id';
2698 l_params(1).paramValue := p_wip_employee_id;
2699 l_params(2).paramName := 'p_org_id';
2700 l_params(2).paramValue := p_org_id;
2701
2702 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_shift_in_UM',
2703 p_params => l_params,
2704 x_returnStatus => x_status);
2705 if(x_status <> fnd_api.g_ret_sts_success) then
2706 raise fnd_api.g_exc_unexpected_error;
2707 end if;
2708 end if;
2709 l_shift_status := 'U';
2710
2711 badge_validation := WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2712
2713 open undo_shift_in_test_cursor;
2714 fetch undo_shift_in_test_cursor into l_dummy_var;
2715
2716 IF (badge_validation = true) then
2717 l_shift_status :='N';
2718 elsif undo_shift_in_test_cursor%NOTFOUND then
2719 l_shift_status := 'O';
2720 else
2721 delete from wip_resource_actual_times wrat
2722 where
2723 wrat.wip_entity_id is null
2724 and wrat.EMPLOYEE_ID=p_wip_employee_id
2725 and wrat.ORGANIZATION_ID=p_org_id
2726 and wrat.end_date is null;
2727 l_shift_status := 'S';
2728 end if;
2729 close undo_shift_in_test_cursor;
2730 x_status := l_shift_status;
2731 --fix logging issue. x_status is the output parameter for this procedure
2732 --we shouldn't set it as an output of wip_logger.exitPoint. Else
2733 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
2734 if (l_logLevel <= wip_constants.trace_logging) then
2735 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_shift_in_UM',
2736 p_procReturnStatus => x_status,
2737 p_msg => 'normal completion',
2738 x_returnStatus => l_ret_status);
2739 end if;
2740 END undo_shift_in_UM;
2741
2742 -- Set clock in.
2743 PROCEDURE clock_in(p_wip_entity_id IN NUMBER,
2744 p_operation_seq_num IN NUMBER,
2745 p_responsibility_key IN VARCHAR2,
2746 p_dept_id IN NUMBER,
2747 p_employee_id IN NUMBER,
2748 p_instance_id IN NUMBER,
2749 p_resource_id IN NUMBER,
2750 p_resource_seq_num IN NUMBER,
2751 x_status out NOCOPY VARCHAR2,
2752 x_msg_count out NOCOPY NUMBER,
2753 x_msg out NOCOPY VARCHAR2)
2754 IS
2755 lx_status varchar2(1);
2756 lx_msg_count number;
2757 lx_msg varchar2(255);
2758 l_clock_status varchar2(1);
2759 l_dummy_var number;
2760 l_resource_id number;
2761 l_resource_seq_num number;
2762 l_uom_code varchar2(3);
2763 l_scheduled_flag number;
2764 lx_organization_id number;
2765 lx_department_id number;
2766 lx_time_entry_id number;
2767 lx_return_status varchar2(10);
2768 l_num_job_op number;
2769 l_skill_check number;
2770 l_params wip_logger.param_tbl_t;
2771 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2772 l_ret_status varchar2(1);
2773
2774 cursor clock_in_cursor is select 1
2775 from wip_resource_actual_times wrat
2776 where wrat.wip_entity_id = p_wip_entity_id
2777 and wrat.operation_seq_num = p_operation_seq_num
2778 and wrat.resource_id = p_resource_id
2779 /*Fix Bug 9681625,it is unnecessary to filter based on resource_seq_num
2780 because user will not able to clock in if resource is not unique per op*/
2781 --and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
2782 and wrat.employee_id = p_employee_id
2783 and wrat.instance_id = p_instance_id
2784 and wrat.status_type = 1
2785 and wrat.end_date is null;
2786
2787 cursor resource_uom_cursor is select unit_of_measure
2788 from bom_resources br
2789 where br.resource_id = p_resource_id;
2790
2791 cursor scheduled_flag_cursor is
2792 select scheduled_flag
2793 from wip_operation_resources wor
2794 where wor.wip_entity_id = p_wip_entity_id
2795 and wor.operation_seq_num = p_operation_seq_num
2796 and wor.resource_seq_num = p_resource_seq_num;
2797
2798 /* check if there is no clock-in in the scheduled flag group */
2799 cursor num_job_op_cursor(p_scheduled_flag varchar2) is
2800 select count(*)
2801 from wip_resource_actual_times wrat, wip_operation_resources wor
2802 where wrat.wip_entity_id = p_wip_entity_id
2803 and wrat.operation_seq_num = p_operation_seq_num
2804 and wrat.wip_entity_id = wor.wip_entity_id (+)
2805 and wrat.operation_seq_num = wor.operation_seq_num (+)
2806 and wrat.resource_seq_num = wor.resource_seq_num(+)
2807 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) =
2808 decode(p_scheduled_flag, null, 1, 2, 1, p_scheduled_flag)
2809 and wrat.status_type = 1
2810 and wrat.start_date is not null
2811 and wrat.end_date is null;
2812
2813 cursor time_records_machine_cursor(c_scheduled_flag number) is
2814 select wor.resource_id,
2815 wor.resource_seq_num,
2816 wor.uom_code
2817 from wip_operation_resources wor,
2818 bom_resources br
2819 where wor.wip_entity_id = p_wip_entity_id
2820 and wor.operation_seq_num = p_operation_seq_num
2821 and decode(wor.scheduled_flag, 2, 1, wor.scheduled_flag) =
2822 decode(c_scheduled_flag, 2, 1, c_scheduled_flag)
2823 and br.resource_type = 1 --machine resource
2824 and wor.resource_id = br.resource_id
2825 and not exists (select 1
2826 from wip_resource_actual_times wrat
2827 where wrat.wip_entity_id = p_wip_entity_id
2828 and wrat.operation_seq_num = p_operation_seq_num
2829 and wrat.resource_id = wor.resource_id
2830 and wrat.resource_seq_num = wor.resource_seq_num
2831 and wrat.status_type = 1
2832 and wrat.end_date is null);
2833 BEGIN
2834 if(l_logLevel <= wip_constants.trace_logging) then
2835 l_params(1).paramName := 'p_wip_entity_id';
2836 l_params(1).paramValue := p_wip_entity_id;
2837 l_params(2).paramName := 'p_operation_seq_num';
2838 l_params(2).paramValue := p_operation_seq_num;
2839 l_params(3).paramName := 'p_responsibility_key';
2840 l_params(3).paramValue := p_responsibility_key;
2841 l_params(4).paramName := 'p_dept_id';
2842 l_params(4).paramValue := p_dept_id;
2843 l_params(5).paramName := 'p_employee_id';
2844 l_params(5).paramValue := p_employee_id;
2845 l_params(6).paramName := 'p_instance_id';
2846 l_params(6).paramValue := p_instance_id;
2847 l_params(7).paramName := 'p_resource_id';
2848 l_params(7).paramValue := p_resource_id;
2849 l_params(8).paramName := 'p_resource_seq_num';
2850 l_params(8).paramValue := p_resource_seq_num;
2851
2852 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_in',
2853 p_params => l_params,
2854 x_returnStatus => x_status);
2855 if(x_status <> fnd_api.g_ret_sts_success) then
2856 raise fnd_api.g_exc_unexpected_error;
2857 end if;
2858 end if;
2859 l_clock_status := 'U';
2860
2861 if WIP_TIME_ENTRY_PUB.is_emp_shift_in(p_wip_entity_id => p_wip_entity_id,
2862 p_employee_id => p_employee_id) then
2863
2864 if(l_logLevel <= wip_constants.trace_logging) then
2865 wip_logger.log('is_emp_shift_in = Y', x_status);
2866 end if;
2867 l_skill_check := WIP_WS_SKILL_CHECK_PVT.validate_skill_for_clock_in(
2868 p_wip_entity_id => p_wip_entity_id,
2869 p_op_seq_num => p_operation_seq_num,
2870 p_emp_id => p_employee_id);
2871
2872 if(l_logLevel <= wip_constants.trace_logging) then
2873 wip_logger.log('l_skill_check: ' || l_skill_check, x_status);
2874 end if;
2875 if l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_SKILL_VALIDATION_SUCCESS then
2876
2877 open clock_in_cursor;
2878 fetch clock_in_cursor into l_dummy_var;
2879 if clock_in_cursor%FOUND then
2880 l_clock_status := 'C';
2881 else
2882 if(l_logLevel <= wip_constants.trace_logging) then
2883 wip_logger.log('calling job_on', x_status);
2884 end if;
2885 job_on(p_wip_entity_id => p_wip_entity_id,
2886 p_operation_seq_num => p_operation_seq_num,
2887 p_employee_id => p_employee_id,
2888 x_status => lx_status,
2889 x_msg_count => lx_msg_count,
2890 x_msg => lx_msg);
2891
2892 if(l_logLevel <= wip_constants.trace_logging) then
2893 wip_logger.log('calling get_org_dept_ids', x_status);
2894 end if;
2895 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
2896 p_operation_seq_num => p_operation_seq_num,
2897 x_organization_id => lx_organization_id,
2898 x_department_id => lx_department_id);
2899
2900 open resource_uom_cursor;
2901 fetch resource_uom_cursor into l_uom_code;
2902 close resource_uom_cursor;
2903
2904 --clock in employee for which badge number was entered
2905 record_insert(p_time_entry_id => null,
2906 p_organization_id => lx_organization_id,
2907 p_wip_entity_id => p_wip_entity_id,
2908 p_operation_seq_num => p_operation_seq_num,
2909 p_resource_id => p_resource_id,
2910 p_resource_seq_num => p_resource_seq_num,
2911 p_instance_id => p_instance_id,
2912 p_serial_number => null,
2913 p_last_update_date => null,
2914 p_last_updated_by => null,
2915 p_creation_date => null,
2916 p_created_by => null,
2917 p_last_update_login => null,
2918 p_object_version_num => null,
2919 p_time_entry_mode => null,
2920 p_cost_flag => null,
2921 p_add_to_rtg => null,
2922 p_status_type => null,
2923 p_start_date => sysdate,
2924 p_end_date => null,
2925 p_projected_completion_date => null,
2926 p_duration => null,
2927 p_uom_code => l_uom_code,
2928 p_employee_id => p_employee_id,
2929 x_time_entry_id => lx_time_entry_id,
2930 x_return_status => lx_return_status);
2931 l_clock_status := 'S';
2932 end if;
2933 close clock_in_cursor;
2934
2935 open scheduled_flag_cursor;
2936 fetch scheduled_flag_cursor into l_scheduled_flag;
2937 close scheduled_flag_cursor;
2938
2939 /* ad-hoc resource don't have a scheduled_flag, treat it as no-scheduled*/
2940 if(l_scheduled_flag is null) then l_scheduled_flag := 2; end if;
2941
2942 open num_job_op_cursor(l_scheduled_flag);
2943 fetch num_job_op_cursor into l_num_job_op;
2944 close num_job_op_cursor;
2945
2946 --this is the first clock in for this job operation
2947 if (l_num_job_op = 1) then
2948
2949 open time_records_machine_cursor(l_scheduled_flag);
2950 loop
2951 fetch time_records_machine_cursor into l_resource_id,
2952 l_resource_seq_num,
2953 l_uom_code;
2954 exit when time_records_machine_cursor%NOTFOUND;
2955
2956 if(l_logLevel <= wip_constants.trace_logging) then
2957 wip_logger.log('calling record_insert (loop)', x_status);
2958 end if;
2959 --clock in machines that have not already been clocked in
2960 record_insert(p_time_entry_id => null,
2961 p_organization_id => lx_organization_id,
2962 p_wip_entity_id => p_wip_entity_id,
2963 p_operation_seq_num => p_operation_seq_num,
2964 p_resource_id => l_resource_id,
2965 p_resource_seq_num => l_resource_seq_num,
2966 p_instance_id => null,
2967 p_serial_number => null,
2968 p_last_update_date => null,
2969 p_last_updated_by => null,
2970 p_creation_date => null,
2971 p_created_by => null,
2972 p_last_update_login => null,
2973 p_object_version_num => null,
2974 p_time_entry_mode => null,
2975 p_cost_flag => null,
2976 p_add_to_rtg => null,
2977 p_status_type => null,
2978 p_start_date => sysdate,
2979 p_end_date => null,
2980 p_projected_completion_date => null,
2981 p_duration => null,
2982 p_uom_code => l_uom_code,
2983 p_employee_id => p_employee_id,
2984 x_time_entry_id => lx_time_entry_id,
2985 x_return_status => lx_return_status);
2986 end loop;
2987 close time_records_machine_cursor;
2988 end if;
2989
2990 if(l_logLevel <= wip_constants.trace_logging) then
2991 wip_logger.log('calling process_time_records', x_status);
2992 end if;
2993
2994 process_time_records(p_wip_entity_id => p_wip_entity_id,
2995 p_completed_op => p_operation_seq_num,
2996 p_instance_id => null,
2997 p_time_entry_source => 'clock');
2998
2999
3000 x_status := l_clock_status;
3001
3002 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_COMPETENCE_CHECK_FAIL then
3003 x_status := 'P';
3004 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_CERTIFY_CHECK_FAIL then
3005 x_status := 'Q';
3006 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_QUALIFY_CHECK_FAIL then
3007 x_status := 'R';
3008 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_SKILL_VALIDATION_EXCEPTION then
3009 x_status := 'U';
3010 end if;
3011 else
3012 x_status := 'H';
3013 end if;
3014 --fix logging issue. x_status is the output parameter for this procedure
3015 --we shouldn't set it as an output of wip_logger.exitPoint. Else
3016 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
3017 if (l_logLevel <= wip_constants.trace_logging) then
3018 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_in',
3019 p_procReturnStatus => x_status,
3020 p_msg => 'normal completion',
3021 x_returnStatus => l_ret_status);
3022 end if;
3023 END clock_in;
3024
3025 -- Set clock out.
3026 PROCEDURE clock_out(p_wip_entity_id IN NUMBER,
3027 p_operation_seq_num IN NUMBER,
3028 p_responsibility_key IN VARCHAR2,
3029 p_dept_id IN NUMBER,
3030 p_employee_id IN NUMBER,
3031 p_instance_id IN NUMBER,
3032 p_resource_id IN NUMBER,
3033 p_resource_seq_num IN NUMBER,
3034 x_status out NOCOPY VARCHAR2,
3035 x_msg_count out NOCOPY NUMBER,
3036 x_msg out NOCOPY VARCHAR2)
3037 IS
3038 l_clock_status varchar2(1);
3039 l_process_status number;
3040 l_object_version_num number;
3041 l_last_op_qty_num number;
3042 l_last_job_qty_num number;
3043 l_last_op_qty varchar2(1);
3044 l_last_job_qty varchar2(1);
3045 l_start_date date;
3046 l_date date;
3047 l_duration number;
3048 l_uom_code varchar2(3);
3049 l_ret_status varchar2(1);
3050
3051 l_params wip_logger.param_tbl_t;
3052 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3053 cursor clock_out_test_cursor is
3054 select object_version_number,
3055 start_date
3056 from wip_resource_actual_times
3057 where wip_entity_id = p_wip_entity_id
3058 and operation_seq_num = p_operation_seq_num
3059 and resource_id = p_resource_id
3060 /* and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1) BUG 7322174*/
3061 and employee_id = p_employee_id
3062 and instance_id = p_instance_id
3063 and status_type = 1
3064 and end_date is null;
3065 BEGIN
3066 if(l_logLevel <= wip_constants.trace_logging) then
3067 l_params(1).paramName := 'p_wip_entity_id';
3068 l_params(1).paramValue := p_wip_entity_id;
3069 l_params(2).paramName := 'p_operation_seq_num';
3070 l_params(2).paramValue := p_operation_seq_num;
3071 l_params(3).paramName := 'p_responsibility_key';
3072 l_params(3).paramValue := p_responsibility_key;
3073 l_params(4).paramName := 'p_dept_id';
3074 l_params(4).paramValue := p_dept_id;
3075 l_params(5).paramName := 'p_employee_id';
3076 l_params(5).paramValue := p_employee_id;
3077 l_params(6).paramName := 'p_instance_id';
3078 l_params(6).paramValue := p_instance_id;
3079 l_params(7).paramName := 'p_resource_id';
3080 l_params(7).paramValue := p_resource_id;
3081 l_params(8).paramName := 'p_resource_seq_num';
3082 l_params(8).paramValue := p_resource_seq_num;
3083
3084 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out',
3085 p_params => l_params,
3086 x_returnStatus => x_status);
3087 if(x_status <> fnd_api.g_ret_sts_success) then
3088 raise fnd_api.g_exc_unexpected_error;
3089 end if;
3090 end if;
3091 l_clock_status := 'U';
3092 l_process_status := 3; --updated
3093 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
3094
3095 open clock_out_test_cursor;
3096 fetch clock_out_test_cursor into l_object_version_num,
3097 l_start_date;
3098 if clock_out_test_cursor%NOTFOUND then
3099 l_clock_status := 'O';
3100 else
3101 if(l_logLevel <= wip_constants.trace_logging) then
3102 wip_logger.log('updating wip_resource_actual_times', x_status);
3103 end if;
3104 l_date := sysdate;
3105 l_duration := (l_date - l_start_date)*24;
3106 update wip_resource_actual_times set
3107 end_date = l_date,
3108 duration = l_duration,
3109 uom_code = l_uom_code,
3110 process_status = l_process_status,
3111 object_version_number = l_object_version_num + 1,
3112 last_update_date = l_date,
3113 last_updated_by = fnd_global.user_id,
3114 last_update_login = fnd_global.login_id
3115 where wip_entity_id = p_wip_entity_id
3116 and operation_seq_num = p_operation_seq_num
3117 and resource_id = p_resource_id
3118 /*Fix Bug 9721473, remove the filtering on resouce_seq_num from update statement so that
3119 ad hoc resource of Manual Charge type machine resource can be clocked out successfully*/
3120 -- and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
3121 and employee_id = p_employee_id
3122 and instance_id = p_instance_id
3123 and status_type = 1
3124 and end_date is null;
3125
3126 if(l_logLevel <= wip_constants.trace_logging) then
3127 wip_logger.log('calling process_time_records()', x_status);
3128 end if;
3129 process_time_records(p_wip_entity_id => p_wip_entity_id,
3130 p_completed_op => p_operation_seq_num,
3131 p_instance_id => null,
3132 p_time_entry_source => 'clock');
3133
3134 l_clock_status := 'S';
3135 end if;
3136 close clock_out_test_cursor;
3137 /* Added for bug 6891758.*/
3138 if G_RES_CHG_FAILED =wip_constants.yes then
3139 l_clock_status := 'T';
3140 end if;
3141 x_status := l_clock_status;
3142 --fix logging issue. x_status is the output parameter for this procedure
3143 --we shouldn't set it as an output of wip_logger.exitPoint. Else
3144 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
3145 if (l_logLevel <= wip_constants.trace_logging) then
3146 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.clock_out',
3147 p_procReturnStatus => x_status,
3148 p_msg => 'normal completion',
3149 x_returnStatus => l_ret_status);
3150 end if;
3151 END clock_out;
3152
3153 -- Set undo clock in.
3154 PROCEDURE undo_clock_in(p_wip_entity_id IN NUMBER,
3155 p_operation_seq_num IN NUMBER,
3156 p_responsibility_key IN VARCHAR2,
3157 p_dept_id IN NUMBER,
3158 p_employee_id IN NUMBER,
3159 p_instance_id IN NUMBER,
3160 p_resource_id IN NUMBER,
3161 p_resource_seq_num IN NUMBER,
3162 x_status out NOCOPY VARCHAR2,
3163 x_msg_count out NOCOPY NUMBER,
3164 x_msg out NOCOPY VARCHAR2)
3165 IS
3166 l_clock_status varchar2(1);
3167 lx_return_status varchar2(10);
3168 l_params wip_logger.param_tbl_t;
3169 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
3170 l_ret_status varchar2(1);
3171 BEGIN
3172 if(l_logLevel <= wip_constants.trace_logging) then
3173 l_params(1).paramName := 'p_wip_entity_id';
3174 l_params(1).paramValue := p_wip_entity_id;
3175 l_params(2).paramName := 'p_operation_seq_num';
3176 l_params(2).paramValue := p_operation_seq_num;
3177 l_params(3).paramName := 'p_responsibility_key';
3178 l_params(3).paramValue := p_responsibility_key;
3179 l_params(4).paramName := 'p_dept_id';
3180 l_params(4).paramValue := p_dept_id;
3181 l_params(5).paramName := 'p_employee_id';
3182 l_params(5).paramValue := p_employee_id;
3183 l_params(6).paramName := 'p_instance_id';
3184 l_params(6).paramValue := p_instance_id;
3185 l_params(7).paramName := 'p_resource_id';
3186 l_params(7).paramValue := p_resource_id;
3187 l_params(8).paramName := 'p_resource_seq_num';
3188 l_params(8).paramValue := p_resource_seq_num;
3189
3190 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_clock_in',
3191 p_params => l_params,
3192 x_returnStatus => x_status);
3193 if(x_status <> fnd_api.g_ret_sts_success) then
3194 raise fnd_api.g_exc_unexpected_error;
3195 end if;
3196 end if;
3197 l_clock_status := 'U';
3198 if(l_logLevel <= wip_constants.trace_logging) then
3199 wip_logger.log('calling record_delete', x_status);
3200 end if;
3201 record_delete(p_wip_entity_id => p_wip_entity_id,
3202 p_operation_seq_num => p_operation_seq_num,
3203 p_employee_id => p_employee_id,
3204 x_return_status => lx_return_status);
3205
3206 if (lx_return_status = 'S') then
3207 if(l_logLevel <= wip_constants.trace_logging) then
3208 wip_logger.log('calling process_time_records()', x_status);
3209 end if;
3210 process_time_records(p_wip_entity_id => p_wip_entity_id,
3211 p_completed_op => p_operation_seq_num,
3212 p_instance_id => null,
3213 p_time_entry_source => 'clock');
3214 l_clock_status := 'S';
3215 elsif (lx_return_status = 'U') then
3216 l_clock_status := 'E';
3217 end if;
3218 x_status := l_clock_status;
3219 --fix logging issue. x_status is the output parameter for this procedure
3220 --we shouldn't set it as an output of wip_logger.exitPoint. Else
3221 --x_status will be overwritten by wip_logger.exitPoint and wrong x_status is return
3222 if (l_logLevel <= wip_constants.trace_logging) then
3223 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.undo_clock_in',
3224 p_procReturnStatus => x_status,
3225 p_msg => 'normal completion',
3226 x_returnStatus => l_ret_status);
3227 end if;
3228 END undo_clock_in;
3229
3230 -- Get last operation quantity.
3231 FUNCTION get_last_op_qty(p_wip_entity_id IN NUMBER,
3232 p_operation_seq_num IN NUMBER) return NUMBER
3233 IS
3234 l_last_op_qty number;
3235
3236 cursor last_op_qty_cursor is
3237 select (wo.quantity_in_queue + wo.quantity_running)
3238 from wip_operations wo
3239 where wo.wip_entity_id = p_wip_entity_id
3240 and wo.operation_seq_num = p_operation_seq_num;
3241 BEGIN
3242 open last_op_qty_cursor;
3243 fetch last_op_qty_cursor into l_last_op_qty;
3244 close last_op_qty_cursor;
3245 return l_last_op_qty;
3246 END get_last_op_qty;
3247
3248 -- Get last job quantity.
3249 FUNCTION get_last_job_qty(p_wip_entity_id IN NUMBER,
3250 p_operation_seq_num IN NUMBER) return NUMBER
3251 IS
3252 l_last_job_qty number;
3253
3254 cursor last_job_qty_cursor is
3255 select (wo.scheduled_quantity - wo.quantity_completed - nvl(wo.cumulative_scrap_quantity, 0))
3256 from wip_operations wo
3257 where wo.wip_entity_id = p_wip_entity_id
3258 and wo.operation_seq_num = p_operation_seq_num;
3259 BEGIN
3260 open last_job_qty_cursor;
3261 fetch last_job_qty_cursor into l_last_job_qty;
3262 close last_job_qty_cursor;
3263 return l_last_job_qty;
3264 END get_last_job_qty;
3265
3266 -- Get the instance id.
3267 FUNCTION get_instance_id(p_org_id IN NUMBER,
3268 p_employee_id IN NUMBER) return NUMBER
3269 IS
3270 l_instance_id number;
3271
3272 cursor instance_id_cursor is
3273 select instance_id
3274 from bom_resource_employees bre
3275 where organization_id = p_org_id
3276 and person_id = p_employee_id;
3277 BEGIN
3278 open instance_id_cursor;
3279 fetch instance_id_cursor into l_instance_id;
3280 close instance_id_cursor;
3281
3282 return l_instance_id;
3283 END get_instance_id;
3284
3285 -- Check pending clockouts.
3286 FUNCTION is_clock_pending(p_wip_entity_id IN NUMBER,
3287 p_operation_seq_num IN NUMBER) return VARCHAR2
3288 IS
3289 l_status varchar2(1);
3290 l_dummy_var varchar2(1);
3291
3292 cursor pending_clockout_cursor is
3293 select count(*)
3294 from wip_resource_actual_times
3295 where wip_entity_id = p_wip_entity_id
3296 and operation_seq_num = nvl(p_operation_seq_num, operation_seq_num)
3297 and status_type = 1
3298 and start_date is not null
3299 and end_date is null;
3300 BEGIN
3301 l_status := 'U';
3302
3303 open pending_clockout_cursor;
3304 fetch pending_clockout_cursor into l_dummy_var;
3305 if l_dummy_var > 0 then
3306 l_status := 'Y';
3307 else
3308 l_status := 'N';
3309 end if;
3310 close pending_clockout_cursor;
3311
3312 return l_status;
3313 END is_clock_pending;
3314
3315 /* To Check if there are any pending clock-outs for an employee */
3316 --Bug12747250 BadgeNumber is varchar2
3317 FUNCTION is_emp_clock_out_pending(p_employee_number IN VARCHAR2,
3318 p_organization_id IN NUMBER,
3319 p_user_mode IN VARCHAR2) return NUMBER
3320 IS
3321 l_emp_clock_ins Number := 0;
3322 l_person_id Number;
3323 badge_validation boolean := true;
3324
3325 BEGIN
3326 /* Get person_id from badge entered for multi user mode.
3327 For single user mode, person_id itself is passed. */
3328
3329 if p_user_mode = 'M' then
3330 emp_valid(p_wip_employee_id => p_employee_number,
3331 p_org_id => p_organization_id,
3332 x_status => badge_validation,
3333 x_person_id => l_person_id);
3334 else
3335 l_person_id := to_number(p_employee_number);
3336 badge_validation := false;
3337 end if;
3338
3339 /* For Invalid badge we can skip this validation since Shift-Out will fail.*/
3340
3341 if not badge_validation then
3342 select count(1)
3343 into l_emp_clock_ins
3344 from dual
3345 where exists( select wip_entity_id
3346 from wip_resource_actual_times
3347 where organization_id = p_organization_id
3348 and employee_id = l_person_id
3349 and end_date is null
3350 and wip_entity_id is not null );
3351 end if;
3352
3353 return l_emp_clock_ins;
3354
3355 END is_emp_clock_out_pending;
3356
3357 /*Add for Bug 10097774*/
3358 FUNCTION get_actual_resource_rate(
3359 p_org_id in number,
3360 p_emp_id in number,
3361 p_txn_date in date) return number is
3362
3363 l_rate number;
3364 l_rate_found boolean;
3365
3366 cursor get_hourly_labor_rate(
3367 p_org_id number,
3368 p_emp_id number,
3369 p_txn_date date) is
3370 select hourly_labor_rate
3371 from wip_employee_labor_rates
3372 where employee_id = p_emp_id
3373 and organization_id = p_org_id
3374 and effective_date =
3375 (select max(effective_date)
3376 from wip_employee_labor_rates
3377 where employee_id = p_emp_id
3378 and organization_id = p_org_id
3379 and effective_date <= trunc(p_txn_date));
3380 begin
3381 open get_hourly_labor_rate(
3382 p_org_id => p_org_id,
3383 p_emp_id => p_emp_id,
3384 p_txn_date => p_txn_date);
3385 fetch get_hourly_labor_rate into l_rate;
3386 l_rate_found := get_hourly_labor_rate%FOUND;
3387 close get_hourly_labor_rate;
3388
3389 if (not l_rate_found) then
3390 l_rate := NULL;
3391 end if;
3392
3393 return l_rate;
3394 END get_actual_resource_rate;
3395
3396 /*Add for Bug 10097774
3397 This procedure is shared between record_insert and process_time_records
3398 We will not handle exception here because 2 callers expected differnt behavior.
3399 For report usage rate case, caller expected exception to be raised to display on the page.
3400 While for clock in/clock out, based on bug 6891758, we should still allow user to clock out
3401 even resource-charge has exception*/
3402
3403 PROCEDURE derive_insert_wcti(
3404 p_organization_id in number,
3405 p_wip_entity_id in number,
3406 p_operation_seq_num in number,
3407 p_resource_seq_num in number,
3408 p_resource_id in number,
3409 p_entry_id in number,
3410 p_employee_id in number,
3411 p_duration in number,
3412 p_txn_uom in varchar2,
3413 p_hour_uom in varchar2,
3414 x_return_status out NOCOPY varchar2)
3415 IS
3416 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
3417 l_params wip_logger.param_tbl_t;
3418 l_msg VARCHAR2(100);
3419 l_retStatus VARCHAR2(1);
3420
3421 l_organization_code varchar2(3);
3422 l_wip_entity_name varchar2(240);
3423 l_resource_code varchar2(10);
3424 l_resource_type number;
3425 l_employee_id number;
3426 l_employee_num varchar2(30);
3427 l_openPastPeriod boolean := false;
3428 l_acctPeriodID NUMBER;
3429 l_department_id number;
3430 l_department_code varchar2(10);
3431 l_primary_item_id number;
3432 l_project_id number;
3433 l_task_id number;
3434 l_basis_type number;
3435 l_standard_rate_flag number;
3436 l_actual_resource_rate number;
3437 l_primary_uom varchar2(3);
3438 l_primary_quantity number;
3439 conversion_rate number;
3440 x_group_id number; -- 13481688
3441 l_resource_seq_num number ; -- 13481688
3442 -- For Bug 16528843.
3443 l_activity_id number;
3444 l_activity_name varchar2(10);
3445 Begin
3446
3447 if (l_logLevel <= wip_constants.trace_logging) then
3448 l_params(1).paramName := 'p_organization_id';
3449 l_params(1).paramValue := p_organization_id;
3450 l_params(2).paramName := 'p_wip_entity_id';
3451 l_params(2).paramValue := p_wip_entity_id;
3452 l_params(3).paramName := 'p_operation_seq_num';
3453 l_params(3).paramValue := p_operation_seq_num;
3454 l_params(4).paramName := 'p_resource_seq_num';
3455 l_params(4).paramValue := p_resource_seq_num;
3456 l_params(5).paramName := 'p_resource_id';
3457 l_params(5).paramValue := p_resource_id;
3458 l_params(6).paramName := 'p_entry_id';
3459 l_params(6).paramValue := p_entry_id;
3460 l_params(7).paramName := 'p_employee_id';
3461 l_params(7).paramValue := p_employee_id;
3462 l_params(8).paramName := 'p_duration';
3463 l_params(8).paramValue := p_duration;
3464 l_params(9).paramName := 'p_txn_uom';
3465 l_params(9).paramValue := p_txn_uom;
3466 l_params(10).paramName := 'p_hour_uom';
3467 l_params(10).paramValue := p_hour_uom;
3468 wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
3469 p_params => l_params,
3470 x_returnStatus => l_retStatus);
3471 end if;
3472
3473 select organization_code
3474 into l_organization_code
3475 from mtl_parameters
3476 where organization_id = p_organization_id;
3477
3478
3479 if (l_logLevel <= wip_constants.full_logging) then
3480 wip_logger.log('derive_insert_wcti Phase 10', l_retStatus);
3481 wip_logger.log('l_organization_code = '|| l_organization_code, l_retStatus);
3482 end if;
3483
3484 select wip_entity_name
3485 into l_wip_entity_name
3486 from wip_entities
3487 where wip_entity_id = p_wip_entity_id;
3488
3489 if (l_logLevel <= wip_constants.full_logging) then
3490 wip_logger.log('derive_insert_wcti Phase 20', l_retStatus);
3491 wip_logger.log('l_wip_entity_name = '|| l_wip_entity_name, l_retStatus);
3492 end if;
3493
3494 -- Modified for Bug 16528843.
3495 select br.resource_code, br.resource_type
3496 into l_resource_code, l_resource_type
3497 from bom_resources br
3498 where br.resource_id = p_resource_id;
3499
3500 if (l_logLevel <= wip_constants.full_logging) then
3501 wip_logger.log('derive_insert_wcti Phase 30', l_retStatus);
3502 wip_logger.log('l_resource_code = '|| l_resource_code|| ' l_resource_type = '|| l_resource_type, l_retStatus);
3503 end if;
3504
3505 if( l_resource_type = 2 ) then /* for labor, try get the employee id and num */
3506
3507 if(p_employee_id is null) then
3508 /* Bug 6891758. If the employee is not associated to the business unit for the organization
3509 we will still allow them to do clock-out as it's for reporting purpose.
3510 But resource charging will not be allowed.*/
3511 begin
3512 /*select wrat.employee_id, mec.employee_num
3513 into l_employee_id, l_employee_num
3514 from wip_resource_actual_times wrat, mtl_employees_current_view mec
3515 where wrat.time_entry_id = p_entry_id
3516 and wrat.employee_id = mec.employee_id
3517 and wrat.organization_id = mec.organization_id;*/
3518 --Bug 14595358, Contingent Worker Project, can no longer use mtl_employees_current_view
3519 --as mtl_employees_current_view only have employees and doesn't have contingent worker records
3520 SELECT wrat.employee_id, NVL(P.EMPLOYEE_NUMBER, P.NPW_NUMBER)
3521 into l_employee_id, l_employee_num
3522 FROM PER_PEOPLE_F P,
3523 PER_ASSIGNMENTS_F A,
3524 PER_PERSON_TYPES T,
3525 HR_ORGANIZATION_UNITS ORG,
3526 wip_resource_actual_times wrat
3527 WHERE A.PERSON_ID = P.PERSON_ID AND
3528 ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
3529 A.PRIMARY_FLAG = 'Y' AND
3530 A.ASSIGNMENT_TYPE in ('C','E') AND
3531 P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
3532 P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
3533 TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
3534 NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
3535 TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
3536 NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
3537 (P.NPW_NUMBER IS NOT NULL OR (P.EMPLOYEE_NUMBER IS NOT NULL AND P.CURRENT_EMPLOYEE_FLAG ='Y')) AND
3538 ORG.ORGANIZATION_ID = wrat.organization_id AND
3539 wrat.time_entry_id = p_entry_id AND
3540 wrat.employee_id = P.PERSON_ID;
3541
3542 exception
3543 when no_data_found then
3544 G_RES_CHG_FAILED := wip_constants.yes;
3545 end;
3546 else
3547 /*select mec.employee_num
3548 into l_employee_num
3549 from mtl_employees_current_view mec
3550 where mec.employee_id = p_employee_id
3551 and mec.organization_id = p_organization_id;*/
3552 --Bug 14595358, Contingent Worker Project, can no longer use mtl_employees_current_view
3553 --as mtl_employees_current_view only have employees and doesn't have contingent worker records
3554 SELECT NVL(P.EMPLOYEE_NUMBER, P.NPW_NUMBER)
3555 into l_employee_num
3556 FROM PER_PEOPLE_F P,
3557 PER_ASSIGNMENTS_F A,
3558 PER_PERSON_TYPES T,
3559 HR_ORGANIZATION_UNITS ORG
3560 WHERE A.PERSON_ID = P.PERSON_ID AND
3561 ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
3562 A.PRIMARY_FLAG = 'Y' AND
3563 A.ASSIGNMENT_TYPE in ('C','E') AND
3564 P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
3565 P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
3566 TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
3567 NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
3568 TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
3569 NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
3570 (P.NPW_NUMBER IS NOT NULL OR (P.EMPLOYEE_NUMBER IS NOT NULL AND P.CURRENT_EMPLOYEE_FLAG ='Y')) AND
3571 ORG.ORGANIZATION_ID = p_organization_id AND
3572 P.PERSON_ID = p_employee_id;
3573
3574 l_employee_id := p_employee_id;
3575 end if;
3576 else
3577 l_employee_id := null;
3578 l_employee_num := null;
3579 end if;
3580
3581 if (l_logLevel <= wip_constants.full_logging) then
3582 wip_logger.log('derive_insert_wcti Phase 40', l_retStatus);
3583 wip_logger.log('l_employee_id = '|| l_employee_id ||' l_employee_num = '|| l_employee_num, l_retStatus);
3584 end if;
3585
3586 -- Bug 13481688 @Start
3587 l_resource_seq_num := p_resource_seq_num;
3588 if (l_resource_seq_num is null) then
3589 l_resource_seq_num := get_adhoc_resource_seq(
3590 p_wip_entity_id, p_operation_seq_num, p_resource_id);
3591 end if;
3592 if (l_logLevel <= wip_constants.full_logging) then
3593 wip_logger.log('derive_insert_wcti:l_resource_seq_num = '|| l_resource_seq_num , l_retStatus);
3594 end if;
3595 -- Bug 13481688 @End
3596 --Bug 10097774 get department_id and department_code
3597 select wo.department_id, bd.department_code
3598 into l_department_id, l_department_code
3599 from wip_operations wo, bom_departments bd
3600 where wo.wip_entity_id = p_wip_entity_id
3601 and wo.operation_seq_num = p_operation_seq_num
3602 and wo.department_id = bd.department_id;
3603
3604 if (l_logLevel <= wip_constants.full_logging) then
3605 wip_logger.log('derive_insert_wcti Phase 50', l_retStatus);
3606 wip_logger.log('l_department_id = '|| l_department_id ||' l_department_code = '|| l_department_code, l_retStatus);
3607 end if;
3608
3609
3610 --Bug 10097774 get resource basis_type, standard_rate_flag, primary_uom
3611 -- Modified for Bug 16528843.
3612 begin
3613 select wor.basis_type, wor.standard_rate_flag, wor.uom_code,ca.activity_id, ca.activity
3614 into l_basis_type, l_standard_rate_flag, l_primary_uom, l_activity_id, l_activity_name
3615 from wip_operation_resources wor,cst_activities ca
3616 where wor.wip_entity_id = p_wip_entity_id
3617 and wor.operation_seq_num = p_operation_seq_num
3618 and wor.resource_seq_num = l_resource_seq_num -- p_resource_seq_num Bug 13481688
3619 and wor.resource_id = p_resource_id
3620 and wor.activity_id = ca.activity_id (+)
3621 and nvl(ca.disable_date(+),sysdate+1) > sysdate
3622 and (ca.organization_id is null or ca.organization_id = p_organization_id);
3623
3624 exception
3625 when no_data_found then
3626 select br.default_basis_type, br.standard_rate_flag, br.unit_of_measure,ca.activity_id, ca.activity
3627 into l_basis_type, l_standard_rate_flag, l_primary_uom,l_activity_id, l_activity_name
3628 from bom_resources br,cst_activities ca
3629 where br.resource_id = p_resource_id
3630 and br.default_activity_id = ca.activity_id (+)
3631 and nvl(ca.disable_date(+),sysdate+1) > sysdate
3632 and (ca.organization_id is null or ca.organization_id = p_organization_id);
3633 end;
3634
3635 if (l_logLevel <= wip_constants.full_logging) then
3636 wip_logger.log('derive_insert_wcti Phase 60', l_retStatus);
3637 wip_logger.log('l_basis_type = '|| l_basis_type, l_retStatus);
3638 wip_logger.log('l_standard_rate_flag = '|| l_standard_rate_flag, l_retStatus);
3639 wip_logger.log('l_primary_uom = '|| l_primary_uom, l_retStatus);
3640 end if;
3641
3642 --Bug 10097774 get primary_item_id, project_id, task_id
3643 select primary_item_id, project_id, task_id
3644 into l_primary_item_id, l_project_id, l_task_id
3645 from wip_discrete_jobs
3646 where wip_entity_id = p_wip_entity_id;
3647
3648 if (l_logLevel <= wip_constants.full_logging) then
3649 wip_logger.log('derive_insert_wcti Phase 70', l_retStatus);
3650 wip_logger.log('l_primary_item_id = '|| l_primary_item_id, l_retStatus);
3651 wip_logger.log('l_project_id = '|| l_project_id, l_retStatus);
3652 wip_logger.log('l_task_id = '|| l_task_id, l_retStatus);
3653 end if;
3654
3655 --Bug 10097774 get account_period_id
3656 invttmtx.tdatechk(org_id => p_organization_id,
3657 transaction_date => sysdate,
3658 period_id => l_acctPeriodID,
3659 open_past_period => l_openPastPeriod);
3660
3661 if(l_acctPeriodID is null or
3662 l_acctPeriodID <= 0) then
3663 raise fnd_api.g_exc_unexpected_error;
3664 end if;
3665
3666 if (l_logLevel <= wip_constants.full_logging) then
3667 wip_logger.log('derive_insert_wcti Phase 80', l_retStatus);
3668 wip_logger.log('l_acctPeriodID = '|| l_acctPeriodID, l_retStatus);
3669 end if;
3670
3671 --Bug 10097774 get employee hourly rate for labor resoruce
3672 if(l_employee_id is not null) then
3673 l_actual_resource_rate := get_actual_resource_rate(p_org_id => p_organization_id,
3674 p_emp_id => l_employee_id,
3675 p_txn_date => sysdate);
3676
3677 --if resource' uom is not in hour, convert it since actual_resource_rate should based on resource's uom(primary uom)
3678 if(l_actual_resource_rate is not null and (l_primary_uom <> p_hour_uom)) then
3679 l_actual_resource_rate := inv_convert.inv_um_convert(
3680 item_id => 0,
3681 precision => WIP_CONSTANTS.MAX_NUMBER_PRECISION,
3682 from_quantity => l_actual_resource_rate,
3683 from_unit => l_primary_uom,
3684 to_unit => p_hour_uom,
3685 from_name => NULL,
3686 to_name => NULL);
3687 end if;
3688 else
3689 l_actual_resource_rate := null;
3690 end if;
3691
3692 if (l_logLevel <= wip_constants.full_logging) then
3693 wip_logger.log('derive_insert_wcti Phase 90', l_retStatus);
3694 wip_logger.log('l_actual_resource_rate = '|| l_actual_resource_rate, l_retStatus);
3695 end if;
3696
3697 --Bug 10097774 when transaction uom is different from resource primariry uom, recaculate the primary quantity
3698 if(l_primary_uom <> p_txn_uom) then
3699 -- get conversion rate based on UOM
3700 conversion_rate :=
3701 inv_convert.inv_um_convert(
3702 item_id => 0,
3703 precision => WIP_CONSTANTS.MAX_NUMBER_PRECISION,
3704 from_quantity => 1,
3705 from_unit => l_primary_uom,
3706 to_unit => p_txn_uom,
3707 from_name => NULL,
3708 to_name => NULL);
3709 else
3710 conversion_rate := 1;
3711 end if;
3712
3713 l_primary_quantity := p_duration/conversion_rate;
3714
3715 if (l_logLevel <= wip_constants.full_logging) then
3716 wip_logger.log('derive_insert_wcti Phase 100', l_retStatus);
3717 wip_logger.log('l_primary_quantity = '|| l_primary_quantity, l_retStatus);
3718 end if;
3719
3720 if G_RES_CHG_FAILED <> wip_constants.yes then --Bug 6891758
3721 x_group_id := -9999; -- Bug 13481688 , Assiginning dummy value to group_id to identify this group later while inserting the resources.
3722 if (l_logLevel <= wip_constants.full_logging) then
3723 wip_logger.log('derive_insert_wcti Phase 110', l_retStatus);
3724 wip_logger.log('inserting into wip_cost_txn_interface', l_retStatus);
3725 end if;
3726 insert into wip_cost_txn_interface(
3727 group_id, -- Bug 13481688
3728 created_by,
3729 created_by_name,
3730 creation_date,
3731 last_updated_by,
3732 last_updated_by_name,
3733 last_update_date,
3734 last_update_login,
3735 operation_seq_num,
3736 organization_code,
3737 organization_id,
3738 process_phase,
3739 process_status,
3740 resource_id,
3741 resource_code,
3742 resource_seq_num,
3743 source_code,
3744 transaction_date,
3745 transaction_quantity,
3746 transaction_type,
3747 transaction_uom,
3748 entity_type,
3749 wip_entity_id,
3750 wip_entity_name,
3751 employee_id,
3752 employee_num,
3753 primary_item_id,--Bug 10097774
3754 acct_period_id,--Bug 10097774
3755 department_id, --Bug 10097774
3756 department_code, --Bug 10097774
3757 resource_type, --Bug 10097774
3758 basis_type, --Bug 10097774
3759 autocharge_type, --Bug 10097774
3760 standard_rate_flag, --Bug 10097774
3761 primary_quantity, --Bug 10097774
3762 primary_uom, --Bug 10097774
3763 actual_resource_rate, --Bug 10097774
3764 project_id, --Bug 10097774
3765 task_id,
3766 activity_id,
3767 activity_name) --Bug 10097774
3768 values(
3769 x_group_id, -- Bug 13481688
3770 fnd_global.user_id,
3771 fnd_global.user_name,
3772 sysdate,
3773 fnd_global.user_id,
3774 fnd_global.user_name,
3775 sysdate,
3776 fnd_global.login_id,
3777 p_operation_seq_num,
3778 l_organization_code,
3779 p_organization_id,
3780 --WIP_CONSTANTS.RES_VAL,
3781 WIP_CONSTANTS.RES_PROC, --Bug 10097774
3782 WIP_CONSTANTS.PENDING,
3783 p_resource_id,
3784 l_resource_code,
3785 l_resource_seq_num, --p_resource_seq_num, Bug 13481688
3786 WIP_CONSTANTS.SOURCE_CODE,
3787 sysdate,
3788 -- bug 8851845 round transaction_quantity to MAX_DISPLAYED_PRECISION
3789 round(p_duration, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), --non time based resource implies no start/end time which means duration is mandatory
3790 WIP_CONSTANTS.RES_TXN,
3791 p_txn_uom,
3792 WIP_CONSTANTS.DISCRETE,
3793 p_wip_entity_id,
3794 l_wip_entity_name,
3795 l_employee_id,
3796 l_employee_num,
3797 l_primary_item_id,--Bug 10097774
3798 l_acctPeriodID,--Bug 10097774
3799 l_department_id, --Bug 10097774
3800 l_department_code, --Bug 10097774
3801 l_resource_type, --Bug 10097774
3802 l_basis_type, --Bug 10097774
3803 WIP_CONSTANTS.MANUAL, --Bug 10097774
3804 l_standard_rate_flag, --Bug 10097774
3805 round(l_primary_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), --Bug 10097774
3806 l_primary_uom, --Bug 10097774
3807 l_actual_resource_rate,--Bug 10097774
3808 l_project_id,--Bug 10097774
3809 l_task_id,
3810 l_activity_id,
3811 l_activity_name); --Bug 10097774
3812 -- Bug 13481688 @start
3813 if (l_resource_seq_num is not null ) then
3814
3815 insert into wip_operation_resources
3816 (wip_entity_id,
3817 operation_seq_num,
3818 resource_seq_num,
3819 organization_id,
3820 repetitive_schedule_id,
3821 resource_id,
3822 uom_code,
3823 basis_type,
3824 activity_id,
3825 standard_rate_flag,
3826 usage_rate_or_amount,
3827 scheduled_flag,
3828 assigned_units,
3829 autocharge_type,
3830 applied_resource_units,
3831 applied_resource_value,
3832 last_update_date,
3833 last_updated_by,
3834 creation_date,
3835 created_by,
3836 last_update_login,
3837 start_date,
3838 completion_date)
3839 select distinct
3840 wti.wip_entity_id,
3841 wti.operation_seq_num,
3842 wti.resource_seq_num,
3843 wti.organization_id,
3844 wti.repetitive_schedule_id,
3845 wti.resource_id,
3846 wti.primary_uom,
3847 wti.basis_type,
3848 br.default_activity_id,
3849 wti.standard_rate_flag,
3850 nvl(wti.usage_rate_or_amount,0),
3851 2,
3852 1,
3853 2,
3854 0,
3855 0,
3856 wti.last_update_date,
3857 wti.last_updated_by,
3858 wti.creation_date,
3859 wti.created_by,
3860 wti.last_update_login,
3861 wo.first_unit_start_date,
3862 wo.last_unit_completion_date
3863 from wip_operations wo,
3864 wip_cost_txn_interface wti,
3865 bom_resources br
3866 where wti.group_id = x_group_id
3867 and wti.process_phase = 2
3868 and wti.process_status = 1
3869 and wti.transaction_type = 1
3870 and not exists
3871 (select 'op res already exists'
3872 from wip_operation_resources wor
3873 where wor.wip_entity_id = wti.wip_entity_id
3874 and wor.operation_seq_num = wti.operation_seq_num
3875 and wor.resource_seq_num = wti.resource_seq_num
3876 and wor.organization_id = wti.organization_id
3877 and nvl(wor.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1))
3878 and wti.rowid =
3879 (select min(wti2.rowid)
3880 from wip_cost_txn_interface wti2
3881 where wti2.group_id = x_group_id
3882 and wti2.process_phase = 2
3883 and wti2.process_status = 1
3884 and wti2.transaction_type = 1
3885 and wti2.wip_entity_id = wti.wip_entity_id
3886 and wti2.operation_seq_num = wti.operation_seq_num
3887 and wti2.resource_seq_num = wti.resource_seq_num
3888 and wti2.organization_id = wti.organization_id
3889 and nvl(wti2.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1))
3890 and br.resource_id = wti.resource_id
3891 and wo.wip_entity_id = wti.wip_entity_id
3892 and wo.operation_seq_num = wti.operation_seq_num
3893 and wo.organization_id = wti.organization_id
3894 and nvl(wo.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1) ;
3895
3896 end if;
3897 -- We need to clear the group ID then only processor will process this record
3898 update wip_cost_txn_interface set group_id = NULL where group_id = x_group_id;
3899 -- Bug 13481688 @End
3900 end if;
3901 x_return_status := fnd_api.g_ret_sts_success;
3902 if (l_logLevel <= wip_constants.trace_logging) then
3903 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
3904 p_procReturnStatus => x_return_status,
3905 p_msg => l_msg,
3906 x_returnStatus => l_retStatus);
3907 wip_logger.cleanUp(x_returnStatus => l_retStatus);
3908 end if;
3909
3910 Exception
3911 when others then
3912 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
3913 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
3914 if (l_logLevel <= wip_constants.trace_logging) then
3915 wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
3916 p_procReturnStatus => x_return_status,
3917 p_msg => l_msg,
3918 x_returnStatus => l_retStatus);
3919 wip_logger.cleanUp(x_returnStatus => l_retStatus);
3920 end if;
3921 raise; --reraise the exeption back because caller expects exception to be raised when it happened.
3922 END derive_insert_wcti;
3923
3924 /*ER 12961989, Arrow Enhancement
3925 get total number of jobs that have been clocked in
3926 */
3927 FUNCTION get_total_cl_jobs(
3928 p_emp_id in number,
3929 p_org_id in number) return number is
3930
3931 l_num_jobs number := 0;
3932
3933 begin
3934
3935 select count(*)
3936 into l_num_jobs
3937 from
3938 (select distinct wrat.wip_entity_id, wrat.operation_seq_num
3939 from wip_resource_actual_times wrat,
3940 bom_resources br
3941 where wrat.organization_id = p_org_id
3942 and wrat.employee_id = p_emp_id
3943 and wrat.start_date is not null
3944 and wrat.end_date is null
3945 and wrat.time_entry_mode <> 8
3946 and wrat.resource_id = br.resource_id
3947 and wrat.organization_id = br.organization_id
3948 and br.resource_type = 2);
3949
3950 return l_num_jobs;
3951 exception when others then
3952 return 0;
3953
3954 END get_total_cl_jobs;
3955
3956 /*ER 12961989, Arrow Enhancement
3957 get currently clocked in job
3958 Return x_ret_status = 'M' if multiple jobs
3959 else return wip_entity_id and operation_seq_num
3960 */
3961 PROCEDURE get_cur_clocked_job(
3962 p_emp_id in number,
3963 p_org_id in number,
3964 x_entity_id out NOCOPY NUMBER,
3965 x_op_seq out NOCOPY NUMBER,
3966 x_start_date out NOCOPY DATE,
3967 x_ret_status out NOCOPY varchar2) is
3968
3969 l_num_jobs number := 0;
3970 l_wip_entity_id number;
3971 l_op_seq_num number;
3972 l_start_date date;
3973 l_ret_status varchar2(1);
3974
3975 begin
3976
3977 l_num_jobs := get_total_cl_jobs(p_emp_id, p_org_id);
3978 if(l_num_jobs = 1) then
3979
3980 select wrat.wip_entity_id,
3981 wrat.operation_seq_num,
3982 wrat.start_date
3983 into l_wip_entity_id, l_op_seq_num, l_start_date
3984 from wip_resource_actual_times wrat,
3985 bom_resources br
3986 where wrat.organization_id = p_org_id
3987 and wrat.employee_id = p_emp_id
3988 and wrat.start_date is not null
3989 and wrat.end_date is null
3990 and wrat.time_entry_mode <> 8
3991 and wrat.resource_id = br.resource_id
3992 and wrat.organization_id = br.organization_id
3993 and br.resource_type = 2;
3994
3995 x_entity_id := l_wip_entity_id;
3996 x_op_seq := l_op_seq_num;
3997 x_start_date := l_start_date;
3998 l_ret_status := 'S';
3999
4000 elsif (l_num_jobs > 1) then
4001 x_entity_id := null;
4002 x_op_seq := null;
4003 x_start_date := null;
4004 l_ret_status := 'M';
4005
4006 else
4007 x_entity_id := null;
4008 x_op_seq := null;
4009 x_start_date := null;
4010 l_ret_status := 'S';
4011 end if;
4012
4013 x_ret_status := l_ret_status;
4014 exception when others then
4015 x_entity_id := null;
4016 x_op_seq := null;
4017 x_start_date := null;
4018 x_ret_status := fnd_api.G_RET_STS_UNEXP_ERROR;
4019 END get_cur_clocked_job;
4020
4021 END WIP_WS_TIME_ENTRY;