[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_TIME_ENTRY
Source
1 PACKAGE BODY WIP_WS_TIME_ENTRY AS
2 /* $Header: wipwsteb.pls 120.35.12010000.3 2008/11/13 15:07:16 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
53 l_resource_type number;
54 l_employee_id number;
55 l_employee_num varchar2(30);
56
57 BEGIN
58 l_status_type := 1; --pending
59 l_process_status := 2; --inserted
60 l_object_version_num := 1; --new record
61 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
62 l_return_status := 'U';
63
64 if p_time_entry_id is null then
65 select WIP_RESOURCE_ACTUAL_TIMES_S.nextval into l_time_entry_id from dual;
66 else
67 l_time_entry_id := p_time_entry_id;
68 end if;
69
70 if p_time_entry_mode is null then
71 l_time_entry_mode := get_time_entry_mode(p_wip_entity_id => p_wip_entity_id,
72 p_operation_seq_num => p_operation_seq_num);
73 else
74 l_time_entry_mode := p_time_entry_mode;
75 end if;
76
77 l_time_uom_flag := is_time_uom(p_uom_code);
78
79 if( p_start_date is null and p_duration is null) then
80 /* skip, consider as empty record */
81 null;
82 elsif (l_time_uom_flag = 'Y' and p_start_date is not null) then
83
84 if (p_start_date is not null and p_end_date is not null and p_duration is null) then
85 l_duration := (p_end_date - p_start_date)*24;
86 else
87 l_duration := p_duration;
88 end if;
89
90 if l_duration is not null and l_uom_code <> p_uom_code then
91 l_duration := inv_convert.inv_um_convert(item_id => -1,
92 precision => 38,
93 from_quantity => l_duration,
94 from_unit => p_uom_code,
95 to_unit => l_uom_code,
96 from_name => null,
97 to_name => null);
98 end if;
99
100 if p_projected_completion_date is null then
101 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
102 p_wip_entity_id => p_wip_entity_id,
103 p_op_seq_num => p_operation_seq_num,
104 p_resource_seq_num => p_resource_seq_num,
105 p_resource_id => p_resource_id,
106 p_instance_id => p_instance_id,
107 p_start_date => p_start_date);
108 else
109 l_projected_completion_date := p_projected_completion_date;
110 end if;
111
112 insert into wip_resource_actual_times
113 (time_entry_id,
114 organization_id,
115 wip_entity_id,
116 operation_seq_num,
117 resource_id,
118 resource_seq_num,
119 instance_id,
120 serial_number,
121 created_by,
122 creation_date,
123 last_updated_by,
124 last_update_date,
125 last_update_login,
126 object_version_number,
127 time_entry_mode,
128 cost_flag,
129 add_to_rtg,
130 status_type,
131 start_date,
132 end_date,
133 projected_completion_date,
134 duration,
135 uom_code,
136 employee_id,
137 process_status)
138 values
139 (l_time_entry_id,
140 p_organization_id,
141 p_wip_entity_id,
142 p_operation_seq_num,
143 p_resource_id,
144 p_resource_seq_num,
145 p_instance_id,
146 p_serial_number,
147 fnd_global.user_id,
148 sysdate,
149 fnd_global.user_id,
150 sysdate,
151 fnd_global.login_id,
152 l_object_version_num,
153 l_time_entry_mode,
154 p_cost_flag,
155 p_add_to_rtg,
156 l_status_type,
157 p_start_date,
158 p_end_date,
159 l_projected_completion_date,
160 l_duration,
161 l_uom_code,
162 p_employee_id,
163 l_process_status);
164
165 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
166 p_operation_seq_num => p_operation_seq_num,
167 p_resource_seq_num => p_resource_seq_num);
168
169 if p_start_date is not null then
170 update_proj_completion_dates(p_organization_id => p_organization_id,
171 p_wip_entity_id => p_wip_entity_id,
172 p_operation_seq_num => p_operation_seq_num,
173 p_resource_seq_num => p_resource_seq_num,
174 p_resource_id => p_resource_id,
175 p_start_date => p_start_date);
176 end if;
177
178 l_return_status := 'S';
179
180 else
181
182 select organization_code
183 into l_organization_code
184 from mtl_parameters
185 where organization_id = p_organization_id;
186
187 select wip_entity_name
188 into l_wip_entity_name
189 from wip_entities
190 where wip_entity_id = p_wip_entity_id;
191
192 select resource_code, resource_type
193 into l_resource_code, l_resource_type
194 from bom_resources
195 where resource_id = p_resource_id;
196
197 if( l_resource_type = 2 ) then /* for labor, try get the employee id and num */
198 select mec.employee_num
199 into l_employee_num
200 from mtl_employees_current_view mec
201 where mec.employee_id = p_employee_id
202 and mec.organization_id = p_organization_id;
203 l_employee_id := p_employee_id;
204 else
205 l_employee_id := null;
206 l_employee_num := null;
207 end if;
208
209 insert into wip_cost_txn_interface
210 (created_by,
211 created_by_name,
212 creation_date,
213 last_updated_by,
214 last_updated_by_name,
215 last_update_date,
216 last_update_login,
217 operation_seq_num,
218 organization_code,
219 organization_id,
220 process_phase,
221 process_status,
222 resource_id,
223 resource_code,
224 resource_seq_num,
225 source_code,
226 transaction_date,
227 transaction_quantity,
228 transaction_type,
229 transaction_uom,
230 entity_type,
231 wip_entity_id,
232 wip_entity_name,
233 employee_id,
234 employee_num)
235 values
236 (fnd_global.user_id,
237 fnd_global.user_name,
238 sysdate,
239 fnd_global.user_id,
240 fnd_global.user_name,
241 sysdate,
242 fnd_global.login_id,
243 p_operation_seq_num,
244 l_organization_code,
245 p_organization_id,
246 WIP_CONSTANTS.RES_VAL,
247 WIP_CONSTANTS.PENDING,
248 p_resource_id,
249 l_resource_code,
250 p_resource_seq_num,
251 WIP_CONSTANTS.SOURCE_CODE,
252 sysdate,
253 p_duration, --non time based resource implies no start/end time which means duration is mandatory
254 WIP_CONSTANTS.RES_TXN,
255 p_uom_code,
256 WIP_CONSTANTS.DISCRETE,
257 p_wip_entity_id,
258 l_wip_entity_name,
259 l_employee_id,
260 l_employee_num);
261 l_return_status := 'S';
262 end if;
263
264 x_time_entry_id := l_time_entry_id;
265 x_return_status := l_return_status;
266 END record_insert;
267
268 -- Update a record in the wip_resource_actual_times table.
269 PROCEDURE record_update(
270 p_time_entry_id in number,
271 p_organization_id in number,
272 p_wip_entity_id in number,
273 p_operation_seq_num in number,
274 p_resource_id in number,
275 p_resource_seq_num in number,
276 p_instance_id in number,
277 p_serial_number in varchar2,
278 p_last_update_date in date,
279 p_last_updated_by in number,
280 p_creation_date in date,
281 p_created_by in number,
282 p_last_update_login in number,
283 p_object_version_num in number,
284 p_time_entry_mode in number,
285 p_cost_flag in varchar2,
286 p_add_to_rtg in varchar2,
287 p_status_type in number,
288 p_start_date in date,
289 p_end_date in date,
290 p_projected_completion_date in date,
291 p_duration in number,
292 p_uom_code in varchar2,
293 p_employee_id in number,
294 x_return_status out NOCOPY varchar2)
295 IS
296 l_process_status number;
297 l_uom_code varchar2(3);
298 l_duration number;
299 l_object_version_num number;
300 l_start_date date;
301 l_time_uom_flag varchar2(1);
302 l_projected_completion_date date;
303 l_return_status varchar2(10);
304 BEGIN
305 l_object_version_num := p_object_version_num;
306 l_start_date := p_start_date;
307 l_process_status := 3; --updated
308 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
309 l_return_status := 'U';
310
311 l_time_uom_flag := is_time_uom(p_uom_code);
312
313 if l_time_uom_flag = 'Y' then
314 if (p_duration is null and p_start_date is not null and p_end_date is not null) then
315 l_duration := (p_end_date - p_start_date)*24;
316 else
317 l_duration := p_duration;
318 end if;
319
320 if l_duration is not null and l_uom_code <> p_uom_code then
321 l_duration := inv_convert.inv_um_convert(item_id => -1,
322 precision => 38,
323 from_quantity => l_duration,
324 from_unit => p_uom_code,
325 to_unit => l_uom_code,
326 from_name => null,
327 to_name => null);
328 end if;
329
330 select object_version_number, start_date into l_object_version_num, l_start_date
331 from wip_resource_actual_times where time_entry_id = p_time_entry_id;
332
333 if l_start_date <> p_start_date then
334 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
335 p_wip_entity_id => p_wip_entity_id,
336 p_op_seq_num => p_operation_seq_num,
337 p_resource_seq_num => p_resource_seq_num,
338 p_resource_id => p_resource_id,
339 p_instance_id => p_instance_id,
340 p_start_date => p_start_date);
341 else
342 l_projected_completion_date := p_projected_completion_date;
343 end if;
344
345 if l_object_version_num = p_object_version_num then
346 update wip_resource_actual_times set
347 organization_id = p_organization_id,
348 wip_entity_id = p_wip_entity_id,
349 operation_seq_num = p_operation_seq_num,
350 resource_id = p_resource_id,
351 resource_seq_num = p_resource_seq_num,
352 instance_id = p_instance_id,
353 serial_number = p_serial_number,
354 creation_date = p_creation_date,
355 created_by = p_created_by,
356 time_entry_mode = p_time_entry_mode,
357 cost_flag = p_cost_flag,
358 add_to_rtg = p_add_to_rtg,
359 status_type = p_status_type,
360 start_date = p_start_date,
361 end_date = p_end_date,
362 projected_completion_date = l_projected_completion_date,
363 duration = l_duration,
364 uom_code = l_uom_code,
365 employee_id = p_employee_id,
366 process_status = l_process_status,
367 object_version_number = p_object_version_num + 1,
368 last_update_date = sysdate,
369 last_updated_by = fnd_global.user_id,
370 last_update_login = fnd_global.login_id
371 where time_entry_id = p_time_entry_id;
372
373 if p_start_date is not null and l_start_date <> p_start_date then
374 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
375 p_operation_seq_num => p_operation_seq_num,
376 p_resource_seq_num => p_resource_seq_num);
377
378 update_proj_completion_dates(p_organization_id => p_organization_id,
379 p_wip_entity_id => p_wip_entity_id,
380 p_operation_seq_num => p_operation_seq_num,
381 p_resource_seq_num => p_resource_seq_num,
382 p_resource_id => p_resource_id,
383 p_start_date => p_start_date);
384 end if;
385
386 l_return_status := 'S';
387 else
388 l_return_status := 'U'; --error condition: stale data
389 end if;
390 else
391 l_return_status := 'U'; --error condition: non time based resources are never updated
392 end if;
393
394 x_return_status := l_return_status;
395 END record_update;
396
397 -- Delete a record from the wip_resource_actual_times table.
398 PROCEDURE record_delete(
399 p_time_entry_id in number,
400 p_object_version_num in number,
401 x_return_status out NOCOPY varchar2)
402 IS
403 l_process_status number;
404 l_object_version_num number;
405 l_organization_id number;
406 l_wip_entity_id number;
407 l_operation_seq_num number;
408 l_resource_id number;
409 l_resource_seq_num number;
410 l_start_date date;
411 l_return_status varchar2(10);
412
413 cursor delete_cursor is select object_version_number,
414 organization_id,
415 wip_entity_id,
416 operation_seq_num,
417 resource_id,
418 resource_seq_num,
419 start_date
420 from wip_resource_actual_times
421 where time_entry_id = p_time_entry_id;
422 BEGIN
423 l_process_status := 4; --deleted
424 l_return_status := 'U';
425
426 open delete_cursor;
427 fetch delete_cursor into l_object_version_num,
428 l_organization_id,
429 l_wip_entity_id,
430 l_operation_seq_num,
431 l_resource_id,
432 l_resource_seq_num,
433 l_start_date;
434 if delete_cursor%NOTFOUND then
435 l_return_status := 'U';
436 else
437 if l_object_version_num = p_object_version_num then
438 update wip_resource_actual_times set
439 process_status = l_process_status,
440 object_version_number = p_object_version_num + 1,
441 last_update_date = sysdate,
442 last_updated_by = fnd_global.user_id,
443 last_update_login = fnd_global.login_id
444 where time_entry_id = p_time_entry_id
445 and process_status <> 4;
446
447 update_actual_start_dates(p_wip_entity_id => l_wip_entity_id,
448 p_operation_seq_num => l_operation_seq_num,
449 p_resource_seq_num => l_resource_seq_num);
450
451 if l_start_date is not null then
452 update_proj_completion_dates(p_organization_id => l_organization_id,
453 p_wip_entity_id => l_wip_entity_id,
454 p_operation_seq_num => l_operation_seq_num,
455 p_resource_seq_num => l_resource_seq_num,
456 p_resource_id => l_resource_id,
457 p_start_date => l_start_date);
458 end if;
459
460 l_return_status := 'S';
461 end if;
462 end if;
463 close delete_cursor;
464 x_return_status := l_return_status;
465 END record_delete;
466
467 -- Delete a record from the wip_resource_actual_times table.
468 PROCEDURE record_delete(
469 p_wip_entity_id in number,
470 p_operation_seq_num in number,
471 p_employee_id in number,
472 x_return_status out NOCOPY varchar2)
473 IS
474 l_process_status number;
475 l_object_version_num number;
476 l_organization_id number;
477 l_resource_id number;
478 l_resource_seq_num number;
479 l_start_date date;
480 l_return_status varchar2(10);
481
482 cursor delete_cursor is select object_version_number,
483 organization_id,
484 resource_id,
485 resource_seq_num,
486 start_date
487 from wip_resource_actual_times
488 where wip_entity_id = p_wip_entity_id
489 and operation_seq_num = p_operation_seq_num
490 and employee_id = p_employee_id
491 and process_status <> 4;
492 BEGIN
493 l_process_status := 4; --deleted
494 l_return_status := 'U';
495
496 open delete_cursor;
497 fetch delete_cursor into l_object_version_num,
498 l_organization_id,
499 l_resource_id,
500 l_resource_seq_num,
501 l_start_date;
502 if delete_cursor%NOTFOUND then
503 l_return_status := 'U';
504 else
505 update wip_resource_actual_times set
506 process_status = l_process_status,
507 object_version_number = l_object_version_num + 1,
508 last_update_date = sysdate,
509 last_updated_by = fnd_global.user_id,
510 last_update_login = fnd_global.login_id
511 where wip_entity_id = p_wip_entity_id
512 and operation_seq_num = p_operation_seq_num
513 and employee_id = p_employee_id
514 and process_status <> 4;
515
516 update_actual_start_dates(p_wip_entity_id => p_wip_entity_id,
517 p_operation_seq_num => p_operation_seq_num,
518 p_resource_seq_num => l_resource_seq_num);
519
520 if l_start_date is not null then
521 update_proj_completion_dates(p_organization_id => l_organization_id,
522 p_wip_entity_id => p_wip_entity_id,
523 p_operation_seq_num => p_operation_seq_num,
524 p_resource_seq_num => l_resource_seq_num,
525 p_resource_id => l_resource_id,
526 p_start_date => l_start_date);
527 end if;
528
529 l_return_status := 'S';
530 end if;
531 close delete_cursor;
532 x_return_status := l_return_status;
533 END record_delete;
534
535 /*************************************************/
536 /* Local Procedures */
537 /* job_off_internal */
538 /* clock_out_labors */
539 /* clock_out_machines */
540 /*************************************************/
541 PROCEDURE job_off_internal(p_wip_entity_id IN NUMBER, p_operation_seq_num NUMBER)
542 IS
543 BEGIN
544
545 update wip_operations
546 set employee_id = null
547 where wip_entity_id = p_wip_entity_id
548 and operation_seq_num = p_operation_seq_num
549 and employee_id is not null;
550
551 END job_off_internal;
552
553 procedure clock_out_labors(p_wip_entity_id number, p_operation_seq_num number, exclude_scheduled_flag number)
554 is
555 l_uom_code varchar2(3);
556 l_date date;
557 Begin
558 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
559 l_date := sysdate;
560
561 update wip_resource_actual_times t
562 set
563 end_date = l_date,
564 duration = (l_date - start_date)*24,
565 uom_code = l_uom_code,
566 process_status = '3',
567 object_version_number = object_version_number + 1,
568 last_update_date = l_date,
569 last_updated_by = fnd_global.user_id,
570 last_update_login = fnd_global.login_id
571 where wip_entity_id = p_wip_entity_id
572 and operation_seq_num = p_operation_seq_num
573 and process_status <> 4
574 and status_type = 1
575 and start_date is not null
576 and end_date is null
577 and resource_id in (select resource_id from bom_resources where resource_type = 2)
578 and (exclude_scheduled_flag is null or
579 exclude_scheduled_flag <>
580 nvl((select scheduled_flag from wip_operation_resources wor
581 where wor.wip_entity_id = t.wip_entity_id
582 and wor.operation_seq_num = t.operation_seq_num
583 and wor.resource_seq_num = t.resource_seq_num), 2) /* ad-hoc has no scheduled flag*/
584 );
585
586 End clock_out_labors;
587
588 procedure clock_out_machines(p_wip_entity_id number, p_operation_seq_num number)
589 is
590
591 cursor all_labor_clocked_out(p_scheduled_flag number) is
592 select
593 decode(
594 ( select count(*)
595 from wip_resource_actual_times wrat,
596 bom_resources br,
597 wip_operation_resources wor
598 where wrat.wip_entity_id = p_wip_entity_id
599 and wrat.operation_seq_num = p_operation_seq_num
600 and wrat.resource_id = br.resource_id
601 and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
602 and wrat.operation_seq_num = wor.operation_seq_num (+)
603 and wrat.resource_id = wor.resource_id (+)
604 and wrat.process_status <> 4
605 and wrat.status_type = 1
606 and br.resource_type = 2 /* labor */
607 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
608 and wrat.start_date is not null
609 and wrat.end_date is null
610 ), 0, 1, 0)
611 from dual;
612
613 /* max end date for yes/no labor resources */
614 cursor max_labor_end_date(p_scheduled_flag number) is
615 select max(wrat.end_date)
616 from wip_resource_actual_times wrat,
617 bom_resources br,
618 wip_operation_resources wor
619 where wrat.wip_entity_id = p_wip_entity_id
620 and wrat.operation_seq_num = p_operation_seq_num
621 and wrat.resource_id = br.resource_id
622 and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
623 and wrat.operation_seq_num = wor.operation_seq_num (+)
624 and wrat.resource_id = wor.resource_id (+)
625 and wrat.process_status <> 4
626 and br.resource_type = 2 /* labor */
627 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
628 and wrat.end_date is not null;
629
630 l_all_clocked_out number;
631 l_scheduled_flag number;
632 l_date date;
633 l_uom_code varchar2(10);
634 Begin
635
636 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
637
638 /* scheduled yes/no resource */
639 l_scheduled_flag := 1;
640
641 open all_labor_clocked_out(l_scheduled_flag);
642 fetch all_labor_clocked_out into l_all_clocked_out;
643 close all_labor_clocked_out;
644
645 if( l_all_clocked_out = 1) then
646 open max_labor_end_date(l_scheduled_flag);
647 fetch max_labor_end_date into l_date;
648 close max_labor_end_date;
649
650 if( l_date is null ) then l_date := sysdate; end if;
651
652 update wip_resource_actual_times t
653 set
654 end_date = l_date,
655 duration = (l_date - start_date)*24,
656 uom_code = l_uom_code,
657 process_status = '3',
658 object_version_number = object_version_number + 1,
659 last_update_date = sysdate,
660 last_updated_by = fnd_global.user_id,
661 last_update_login = fnd_global.login_id
662 where wip_entity_id = p_wip_entity_id
663 and operation_seq_num = p_operation_seq_num
664 and status_type = 1
665 and start_date is not null
666 and end_date is null
667 and resource_id in (select resource_id from bom_resources where resource_type = 1)
668 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
669 from wip_operation_resources wor
670 where wor.wip_entity_id = t.wip_entity_id
671 and wor.operation_seq_num = t.operation_seq_num
672 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
673 end if;
674
675
676 /* prior resources */
677 l_scheduled_flag := 3;
678
679 open all_labor_clocked_out(l_scheduled_flag);
680 fetch all_labor_clocked_out into l_all_clocked_out;
681 close all_labor_clocked_out;
682
683 if( l_all_clocked_out = 1) then
684 open max_labor_end_date(l_scheduled_flag);
685 fetch max_labor_end_date into l_date;
686 close max_labor_end_date;
687
688 if( l_date is null ) then l_date := sysdate; end if;
689
690 update wip_resource_actual_times t
691 set
692 end_date = l_date,
693 duration = (l_date - start_date)*24,
694 process_status = '3',
695 object_version_number = object_version_number + 1,
696 last_update_date = sysdate,
697 last_updated_by = fnd_global.user_id,
698 last_update_login = fnd_global.login_id
699 where wip_entity_id = p_wip_entity_id
700 and operation_seq_num = p_operation_seq_num
701 and status_type = 1
702 and start_date is not null
703 and end_date is null
704 and resource_id in (select resource_id from bom_resources where resource_type = 1)
705 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
706 from wip_operation_resources wor
707 where wor.wip_entity_id = t.wip_entity_id
708 and wor.operation_seq_num = t.operation_seq_num
709 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
710 end if;
711
712 /* next resources */
713 l_scheduled_flag := 4;
714
715 open all_labor_clocked_out(l_scheduled_flag);
716 fetch all_labor_clocked_out into l_all_clocked_out;
717 close all_labor_clocked_out;
718
719 if( l_all_clocked_out = 1) then
720 open max_labor_end_date(l_scheduled_flag);
721 fetch max_labor_end_date into l_date;
722 close max_labor_end_date;
723
724 if( l_date is null ) then l_date := sysdate; end if;
725
726 update wip_resource_actual_times t
727 set
728 end_date = l_date,
729 duration = (l_date - start_date)*24,
730 process_status = '3',
731 object_version_number = object_version_number + 1,
732 last_update_date = sysdate,
733 last_updated_by = fnd_global.user_id,
734 last_update_login = fnd_global.login_id
735 where wip_entity_id = p_wip_entity_id
736 and operation_seq_num = p_operation_seq_num
737 and status_type = 1
738 and start_date is not null
739 and end_date is null
740 and resource_id in (select resource_id from bom_resources where resource_type = 1)
741 and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
742 from wip_operation_resources wor
743 where wor.wip_entity_id = t.wip_entity_id
744 and wor.operation_seq_num = t.operation_seq_num
745 and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
746 end if;
747
748 End clock_out_machines;
749
750 function get_adhoc_resource_seq(p_wip_entity_id number, p_operation_seq_num number, p_resource_id number)
751 return number
752 Is
753 -- check if the resuorce is aleady used in wrat
754 cursor resource_seq_in_wrat is
755 select resource_seq_num
756 from wip_resource_actual_times
757 where wip_entity_id = p_wip_entity_id
758 and operation_seq_num = p_operation_seq_num
759 and resource_id = p_resource_id
760 and resource_seq_num is not null
761 and rownum=1;
762
763 l_resource_seq_num number;
764 l_next_resource_seq_num number;
765 l_wor_max number;
766 l_wrat_max number;
767 Begin
768
769 l_resource_seq_num := null;
770
771 open resource_seq_in_wrat;
772 fetch resource_seq_in_wrat into l_resource_seq_num;
773
774 if( resource_seq_in_wrat%NOTFOUND ) then
775
776 select max(wor.resource_seq_num) seq
777 into l_wor_max
778 from wip_operation_resources wor
779 where wor.wip_entity_id = p_wip_entity_id
780 and wor.operation_seq_num = p_operation_seq_num;
781
782 select max(wrat.resource_seq_num) seq
783 into l_wrat_max
784 from wip_resource_actual_times wrat
785 where wrat.wip_entity_id = p_wip_entity_id
786 and wrat.operation_seq_num = p_operation_seq_num
787 and wrat.resource_seq_num is not null;
788
789 select greatest(nvl(l_wor_max,0), nvl(l_wrat_max,0)) + 10
790 into l_resource_seq_num
791 from dual;
792
793 end if;
794 close resource_seq_in_wrat;
795
796 return l_resource_seq_num;
797 End get_adhoc_resource_seq;
798
799 /* update the operation/resources's actual completion date based on wrat */
800 procedure update_actual_comp_dates(p_wip_entity_id IN NUMBER,
801 p_operation_seq_num IN NUMBER)
802 IS
803 Begin
804
805 /* update wor's actual completion date */
806 update wip_operation_resources wor
807 set
808 wor.actual_completion_date =
809 nvl( ( select max(wrat.end_date)
810 from wip_resource_actual_times wrat
811 where wrat.wip_entity_id = wor.wip_entity_id
812 and wrat.operation_seq_num = wor.operation_seq_num
813 and wrat.resource_seq_num = wor.resource_seq_num
814 and wrat.process_status <> 4
815 and wrat.end_date is not null), sysdate)
816 where wip_entity_id = p_wip_entity_id
817 and operation_seq_num = p_operation_seq_num
818 and not exists (select wrat.end_date
819 from wip_resource_actual_times wrat
820 where wrat.wip_entity_id = wor.wip_entity_id
821 and wrat.operation_seq_num = wor.operation_seq_num
822 and wrat.resource_seq_num = wor.resource_seq_num
823 and wrat.start_date is not null
824 and wrat.end_date is null
825 and wrat.process_status <> 4);
826
827 update wip_operations wo
828 set
829 wo.actual_completion_date =
830 nvl(greatest( ( select max(wor.actual_completion_date)
831 from wip_operation_resources wor
832 where wor.wip_entity_id = wo.wip_entity_id
833 and wor.operation_seq_num = wo.operation_seq_num
834 and wor.actual_completion_date is not null)
835 , ( select max(wrat.end_date)
836 from wip_resource_actual_times wrat
837 where wrat.wip_entity_id = wo.wip_entity_id
838 and wrat.operation_seq_num = wo.operation_seq_num
839 and wrat.end_date is not null)
840 ), sysdate)
841 where wip_entity_id = p_wip_entity_id
842 and operation_seq_num = p_operation_seq_num
843 and not exists (select 1
844 from wip_resource_actual_times wrat
845 where wrat.wip_entity_id = wo.wip_entity_id
846 and wrat.operation_seq_num = wo.operation_seq_num
847 and wrat.start_date is not null
848 and wrat.end_date is null
849 and wrat.process_status <> 4);
850
851 End update_actual_comp_dates;
852
853 -- Process records on report resource usages page.
854 PROCEDURE process_time_records_resource(p_organization_id in number)
855 IS
856 l_wip_entity_id number;
857 l_op_seq_num number;
858
859 cursor time_records_all_cursor is
860 select distinct wrat.wip_entity_id,
861 wrat.operation_seq_num
862 from wip_resource_actual_times wrat
863 where wrat.organization_id = p_organization_id
864 and wrat.status_type = 1
865 and wrat.process_status = 2 /* only new records */
866 and wrat.duration is not null;
867
868 BEGIN
869 for time_record IN time_records_all_cursor
870 loop
871 l_wip_entity_id := time_record.wip_entity_id;
872 l_op_seq_num := time_record.operation_seq_num;
873 process_time_records(p_wip_entity_id => l_wip_entity_id,
874 p_completed_op => l_op_seq_num,
875 p_instance_id => null,
876 p_time_entry_source => 'resource');
877 end loop;
878 END process_time_records_resource;
879
880 -- Process records on report my time page.
881 PROCEDURE process_time_records_my_time(p_organization_id in number,
882 p_instance_id in number)
883 IS
884 l_wip_entity_id number;
885 l_op_seq_num number;
886
887 cursor time_records_mytime_cursor is
888 select distinct wrat.wip_entity_id,
889 wrat.operation_seq_num
890 from wip_resource_actual_times wrat
891 where wrat.organization_id = p_organization_id
892 and wrat.instance_id = p_instance_id
893 and wrat.status_type = 1
894 and wrat.process_status in (2, 3, 4)
895 and (wrat.time_entry_mode in (3,4) or wrat.process_status = 2)
896 and wrat.duration is not null;
897
898 BEGIN
899 for time_record IN time_records_mytime_cursor
900 loop
901 l_wip_entity_id := time_record.wip_entity_id;
902 l_op_seq_num := time_record.operation_seq_num;
903 process_time_records(p_wip_entity_id => l_wip_entity_id,
904 p_completed_op => l_op_seq_num,
905 p_instance_id => p_instance_id,
906 p_time_entry_source => 'mytime');
907 end loop;
908 END process_time_records_my_time;
909
910 -- Process records on move page.
911 PROCEDURE process_time_records_move(p_wip_entity_id IN NUMBER,
912 p_from_op IN NUMBER,
913 p_to_op IN NUMBER)
914 IS
915 l_curr_op number;
916
917 cursor op_seq_num_cursor is
918 select operation_seq_num
919 from wip_operations
920 where wip_entity_id = p_wip_entity_id
921 and operation_seq_num >= p_from_op
922 and operation_seq_num <= p_to_op;
923 BEGIN
924 for op_seq_num_record IN op_seq_num_cursor
925 loop
926 l_curr_op := op_seq_num_record.operation_seq_num;
927 process_time_records(p_wip_entity_id => p_wip_entity_id,
928 p_completed_op => l_curr_op,
929 p_instance_id => null,
930 p_time_entry_source => 'move');
931 end loop;
932 END process_time_records_move;
933
934 -- Process records on report job operation page.
935 PROCEDURE process_time_records_job_op(p_wip_entity_id IN NUMBER,
936 p_operation_seq_num IN NUMBER,
937 p_instance_id in number)
938 IS
939 BEGIN
940 process_time_records(p_wip_entity_id => p_wip_entity_id,
941 p_completed_op => p_operation_seq_num,
942 p_instance_id => p_instance_id,
943 p_time_entry_source => 'jobop');
944 END process_time_records_job_op;
945
946 -- Process records.
947 PROCEDURE process_time_records(p_wip_entity_id IN NUMBER,
948 p_completed_op IN NUMBER,
949 p_instance_id IN NUMBER,
950 p_time_entry_source IN VARCHAR2)
951 IS
952 l_object_version_num number;
953 l_add_to_rtg varchar2(1);
954 l_cost_flag varchar2(1);
955 l_status_type number;
956 l_process_status number;
957 l_resource_seq_num number;
958 l_org_id number;
959 l_resource_id number;
960 l_duration number;
961 l_uom_code varchar2(3);
962 l_employee_id number;
963 l_instance_id number;
964 l_resource_type number;
965 l_scheduled_flag number;
966 l_next_resource_seq_num number;
967 l_end_date date;
968 l_time_entry_mode number;
969 l_last_op_qty_num number;
970 l_last_job_qty_num number;
971 l_last_op_qty varchar2(1);
972 l_last_job_qty varchar2(1);
973 l_completed_status_type number;
974 l_completed_process_status number;
975 l_organization_code varchar2(3);
976 l_resource_code varchar2(10);
977 l_wip_entity_name varchar2(240);
978 l_change_flag varchar2(1);
979 lx_organization_id number;
980 lx_department_id number;
981 lx_status varchar2(1);
982 lx_msg_count number;
983 lx_msg varchar2(255);
984
985 l_employee_num varchar2(30);
986
987 --time records (for all resource types) cursor- used for setting cost_flag, add_to_rtg,
988 -- resource_seq_num (for ad-hoc resources), inserting into wcti and updating wrat status_type to completed
989 cursor time_records_cursor (c_time_entry_mode1 number,
990 c_time_entry_mode2 number,
991 c_time_entry_mode3 number) is
992 select wrat.time_entry_id,
993 wrat.object_version_number,
994 wrat.add_to_rtg,
995 wrat.cost_flag,
996 wrat.status_type,
997 wrat.process_status,
998 wrat.resource_seq_num,
999 wrat.organization_id,
1000 wrat.resource_id,
1001 wrat.duration,
1002 wrat.uom_code,
1003 wrat.end_date,
1004 wrat.time_entry_mode
1005 from wip_resource_actual_times wrat,
1006 bom_resources br,
1007 wip_operation_resources wor
1008 where wrat.wip_entity_id = p_wip_entity_id
1009 and wrat.operation_seq_num = p_completed_op
1010 and wrat.resource_id = br.resource_id
1011 and wrat.wip_entity_id = wor.wip_entity_id (+)
1012 and wrat.operation_seq_num = wor.operation_seq_num (+)
1013 and wrat.resource_id = wor.resource_id (+)
1014 and wrat.status_type = 1
1015 and wrat.process_status <> 4
1016 and ( wrat.process_status in (2, 3) or
1017 ( wrat.time_entry_mode in (c_time_entry_mode1,
1018 c_time_entry_mode2, c_time_entry_mode3)
1019 and (p_instance_id is null or wrat.instance_id = p_instance_id)
1020 ) )
1021 ; -- and wrat.duration is not null;
1022
1023
1024 --find out if there are any active next resources
1025 cursor active_clock_cursor is
1026 select count(*)
1027 from wip_resource_actual_times wrat,
1028 wip_operation_resources wor
1029 where wrat.wip_entity_id = p_wip_entity_id
1030 and wrat.operation_seq_num = p_completed_op
1031 and wrat.status_type = 1
1032 and wrat.process_status <> 4
1033 and wrat.start_date is not null
1034 and wrat.end_date is null
1035 and wrat.wip_entity_id = wor.wip_entity_id
1036 and wrat.operation_seq_num = wor.operation_seq_num
1037 and wrat.resource_id = wor.resource_id;
1038
1039 l_active_clocks number;
1040 l_entry_id number;
1041 BEGIN
1042 l_completed_status_type := 2; --completed
1043 l_completed_process_status := 1; --completed
1044
1045 l_last_op_qty_num := get_last_op_qty(p_wip_entity_id => p_wip_entity_id,
1046 p_operation_seq_num => p_completed_op);
1047 if l_last_op_qty_num = 0 then
1048 l_last_op_qty := 'Y';
1049 end if;
1050
1051 l_last_job_qty_num := get_last_job_qty(p_wip_entity_id => p_wip_entity_id,
1052 p_operation_seq_num => p_completed_op);
1053 if l_last_job_qty_num = 0 then
1054 l_last_job_qty := 'Y';
1055 end if;
1056
1057 if (l_last_op_qty = 'Y' and p_time_entry_source = 'move') then
1058 clock_out_labors(p_wip_entity_id, p_completed_op, 4); /* don't clock out next resources */
1059 end if;
1060
1061 if (l_last_job_qty = 'Y') then
1062 clock_out_machines(p_wip_entity_id, p_completed_op);
1063 end if;
1064
1065 /* if there is still active clock-ins */
1066 l_active_clocks := null;
1067 open active_clock_cursor;
1068 fetch active_clock_cursor into l_active_clocks;
1069 close active_clock_cursor;
1070
1071 if (p_time_entry_source = 'move') then
1072 open time_records_cursor(4,-1,-1);
1073 elsif (p_time_entry_source = 'clock') then
1074 open time_records_cursor(2,-1,-1);
1075 elsif (p_time_entry_source = 'resource') then
1076 open time_records_cursor(-1,-1,-1); /* only new records */
1077 else
1078 open time_records_cursor(3,4,4);
1079 end if;
1080
1081 loop
1082 fetch time_records_cursor into l_entry_id,
1083 l_object_version_num,
1084 l_add_to_rtg,
1085 l_cost_flag,
1086 l_status_type,
1087 l_process_status,
1088 l_resource_seq_num,
1089 l_org_id,
1090 l_resource_id,
1091 l_duration,
1092 l_uom_code,
1093 l_end_date,
1094 l_time_entry_mode;
1095 exit when time_records_cursor%NOTFOUND;
1096
1097 /* add the cost_flag and add_to_rtg and resource seq for the inserted rows */
1098 if ( l_process_status = 2 ) then
1099 l_change_flag := 'N';
1100
1101 if (l_cost_flag is null) then
1102 l_cost_flag := get_cost_flag(p_wip_entity_id => p_wip_entity_id,
1103 p_operation_seq_num => p_completed_op,
1104 p_resource_seq_num => l_resource_seq_num,
1105 p_time_entry_source => p_time_entry_source);
1106 l_change_flag := 'Y';
1107 end if;
1108
1109 if (l_add_to_rtg is null) then
1110 l_add_to_rtg := get_add_to_rtg_flag(p_wip_entity_id => p_wip_entity_id,
1111 p_operation_seq_num => p_completed_op,
1112 p_resource_seq_num => l_resource_seq_num,
1113 p_cost_flag => l_cost_flag,
1114 p_time_entry_source => p_time_entry_source);
1115 l_change_flag := 'Y';
1116 end if;
1117
1118 if (l_add_to_rtg = 'Y' and l_resource_seq_num is null) then
1119 l_resource_seq_num := get_adhoc_resource_seq(
1120 p_wip_entity_id, p_completed_op, l_resource_id);
1121 l_change_flag := 'Y';
1122 end if;
1123
1124 if l_change_flag = 'Y' then
1125 update wip_resource_actual_times
1126 set
1127 cost_flag = l_cost_flag,
1128 add_to_rtg = l_add_to_rtg,
1129 resource_seq_num = l_resource_seq_num,
1130 object_version_number = l_object_version_num + 1,
1131 last_update_date = sysdate,
1132 last_updated_by = fnd_global.user_id,
1133 last_update_login = fnd_global.login_id
1134 where time_entry_id = l_entry_id;
1135 end if;
1136 end if;
1137
1138 /* if it's called from clock, auto-clocked in machines needs to be review later*/
1139 if( l_duration is not null
1140 and not (p_time_entry_source = 'clock' and l_time_entry_mode <> 2)
1141 and not (p_time_entry_source = 'move' and l_time_entry_mode = 3) /* clock out but not charge */
1142 ) then
1143 if (l_cost_flag = 'Y') then
1144 select organization_code
1145 into l_organization_code
1146 from mtl_parameters
1147 where organization_id = l_org_id;
1148
1149 select wip_entity_name
1150 into l_wip_entity_name
1151 from wip_entities
1152 where wip_entity_id = p_wip_entity_id;
1153
1154 select resource_code, resource_type
1155 into l_resource_code, l_resource_type
1156 from bom_resources
1157 where resource_id = l_resource_id;
1158
1159 if( l_resource_type = 2 ) then /* for labor, try get the employee id and num */
1160
1161 /* Bug 6891758. If the employee is not associated to the business unit for the organization
1162 we will still allow them to do clock-out as it's for reporting purpose.
1163 But resource charging will not be allowed.*/
1164 begin
1165 select wrat.employee_id, mec.employee_num
1166 into l_employee_id, l_employee_num
1167 from wip_resource_actual_times wrat, mtl_employees_current_view mec
1168 where wrat.time_entry_id = l_entry_id
1169 and wrat.employee_id = mec.employee_id
1170 and wrat.organization_id = mec.organization_id;
1171 exception
1172 when no_data_found then
1173 G_RES_CHG_FAILED := wip_constants.yes;
1174 end;
1175 else
1176 l_employee_id := null;
1177 l_employee_num := null;
1178 end if;
1179
1180 if G_RES_CHG_FAILED <> wip_constants.yes then
1181
1182 insert into wip_cost_txn_interface(
1183 created_by,
1184 created_by_name,
1185 creation_date,
1186 last_updated_by,
1187 last_updated_by_name,
1188 last_update_date,
1189 last_update_login,
1190 operation_seq_num,
1191 organization_code,
1192 organization_id,
1193 process_phase,
1194 process_status,
1195 resource_id,
1196 resource_code,
1197 resource_seq_num,
1198 source_code,
1199 transaction_date,
1200 transaction_quantity,
1201 transaction_type,
1202 transaction_uom,
1203 entity_type,
1204 wip_entity_id,
1205 wip_entity_name,
1206 employee_id,
1207 employee_num)
1208 values(
1209 fnd_global.user_id,
1210 fnd_global.user_name,
1211 sysdate,
1212 fnd_global.user_id,
1213 fnd_global.user_name,
1214 sysdate,
1215 fnd_global.login_id,
1216 p_completed_op,
1217 l_organization_code,
1218 l_org_id,
1219 WIP_CONSTANTS.RES_VAL,
1220 WIP_CONSTANTS.PENDING,
1221 l_resource_id,
1222 l_resource_code,
1223 l_resource_seq_num,
1224 WIP_CONSTANTS.SOURCE_CODE,
1225 sysdate,
1226 l_duration,
1227 WIP_CONSTANTS.RES_TXN,
1228 l_uom_code,
1229 WIP_CONSTANTS.DISCRETE,
1230 p_wip_entity_id,
1231 l_wip_entity_name,
1232 l_employee_id,
1233 l_employee_num);
1234
1235 end if;
1236 end if;
1237
1238 --mark status type complete for wrat records
1239 update wip_resource_actual_times
1240 set
1241 status_type = l_completed_status_type,
1242 process_status = l_completed_process_status,
1243 object_version_number = l_object_version_num + 1,
1244 last_update_date = sysdate,
1245 last_updated_by = fnd_global.user_id,
1246 last_update_login = fnd_global.login_id
1247 where time_entry_id = l_entry_id;
1248
1249 end if;
1250 end loop;
1251 close time_records_cursor;
1252
1253 /* job-off if no active clock-ins and is the last qty */
1254 if (l_last_job_qty = 'Y') then
1255 if( l_active_clocks = 0 ) then
1256 job_off_internal(p_wip_entity_id, p_completed_op);
1257 end if;
1258 update_actual_comp_dates(p_wip_entity_id, p_completed_op);
1259 end if;
1260
1261 /* for wrat records with process status deleted - delete records */
1262 delete from wip_resource_actual_times
1263 where wip_entity_id = p_wip_entity_id
1264 and operation_seq_num = p_completed_op
1265 and status_type = 1
1266 and process_status = 4;
1267
1268 /* reset process_status of records of inserted/updated - mark as completed */
1269 update wip_resource_actual_times set
1270 process_status = l_completed_process_status,
1271 last_update_date = sysdate,
1272 last_updated_by = fnd_global.user_id,
1273 last_update_login = fnd_global.login_id
1274 where wip_entity_id = p_wip_entity_id
1275 and operation_seq_num = p_completed_op
1276 and status_type = 1
1277 and process_status in (2,3);
1278 END process_time_records;
1279
1280 /* to check if UOM time based */
1281 FUNCTION is_time_uom(p_uom_code IN VARCHAR2) return VARCHAR2
1282 IS
1283 l_uom_class varchar2(10);
1284 l_time_based_uom_flag varchar2(1);
1285
1286 cursor time_based_uom_cursor is
1287 select distinct muc.uom_class
1288 from mtl_uom_conversions muc,
1289 mtl_uom_conversions muc2
1290 where (muc.uom_class = muc2.uom_class and
1291 nvl(muc.disable_date, sysdate + 1) > sysdate) and
1292 nvl(muc2.disable_date, sysdate + 1) > sysdate and
1293 muc.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE') and
1294 muc2.uom_code = p_uom_code;
1295 BEGIN
1296 open time_based_uom_cursor;
1297 fetch time_based_uom_cursor into l_uom_class;
1298 if time_based_uom_cursor%NOTFOUND then
1299 l_time_based_uom_flag := 'N';
1300 else
1301 l_time_based_uom_flag := 'Y';
1302 end if;
1303 close time_based_uom_cursor;
1304
1305 return l_time_based_uom_flag;
1306 END is_time_uom;
1307
1308 -- Get the value for time entry mode.
1309 FUNCTION get_time_entry_mode(p_wip_entity_id IN NUMBER,
1310 p_operation_seq_num IN NUMBER) return NUMBER
1311 IS
1312 l_time_entry_mode varchar2(2); -- Modified for Bug 6663985.
1313 lx_organization_id number;
1314 lx_department_id number;
1315 BEGIN
1316 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
1317 p_operation_seq_num => p_operation_seq_num,
1318 x_organization_id => lx_organization_id,
1319 x_department_id => lx_department_id);
1320
1321 l_time_entry_mode := wip_ws_util.get_preference_value_code(p_pref_id => G_WIP_WS_PREF_TIME_ENTRY_MODE,
1322 p_resp_key => wip_ws_util.get_current_resp_key,
1323 p_org_id => lx_organization_id,
1324 p_dept_id => lx_department_id);
1325 return mod(l_time_entry_mode,10);
1326 END get_time_entry_mode;
1327
1328 -- Get the value for cost_flag.
1329 FUNCTION get_cost_flag(p_wip_entity_id IN NUMBER,
1330 p_operation_seq_num IN NUMBER,
1331 p_resource_seq_num IN NUMBER,
1332 p_time_entry_source IN VARCHAR2) return VARCHAR2
1333 IS
1334 l_charge_time_resources_pref varchar2(1);
1335 l_autocharge_type number;
1336 l_cost_flag varchar2(1);
1337 lx_organization_id number;
1338 lx_department_id number;
1339
1340 l_manual_exist number;
1341 cursor has_autocharge_manual_cursor is
1342 select 1
1343 from wip_operation_resources wor
1344 where wor.wip_entity_id = p_wip_entity_id
1345 and wor.operation_seq_num = p_operation_seq_num
1346 and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1347 and rownum = 1;
1348
1349 cursor autocharge_type_res_cursor is select wor.autocharge_type
1350 from wip_operation_resources wor
1351 where wor.wip_entity_id = p_wip_entity_id
1352 and wor.operation_seq_num = p_operation_seq_num
1353 and wor.resource_seq_num = p_resource_seq_num;
1354 BEGIN
1355 if (p_time_entry_source = 'move') then --time entry source is 'move'
1356 l_cost_flag := 'Y';
1357 else
1358 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
1359 p_operation_seq_num => p_operation_seq_num,
1360 x_organization_id => lx_organization_id,
1361 x_department_id => lx_department_id);
1362 l_charge_time_resources_pref := wip_ws_util.get_preference_value_code(p_pref_id => G_WIP_WS_PREF_CHARGE_RESOURCE,
1363 p_resp_key => wip_ws_util.get_current_resp_key,
1364 p_org_id => lx_organization_id,
1365 p_dept_id => lx_department_id);
1366 if (p_time_entry_source = 'clock') then --time entry source is 'clock'
1367 if (l_charge_time_resources_pref = '2') then
1368 l_cost_flag := 'N';
1369 elsif (p_resource_seq_num is null) then --resource is ad-hoc
1370 l_cost_flag := 'N';
1371
1372 l_manual_exist := 0;
1373 open has_autocharge_manual_cursor;
1374 fetch has_autocharge_manual_cursor into l_manual_exist;
1375 close has_autocharge_manual_cursor;
1376
1377 if (l_manual_exist = 1 ) then
1378 l_cost_flag := 'Y';
1379 end if;
1380 else
1381 open autocharge_type_res_cursor;
1382 fetch autocharge_type_res_cursor into l_autocharge_type;
1383 if autocharge_type_res_cursor%NOTFOUND then
1384 l_cost_flag := 'N';
1385 elsif (l_autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL) then --resource is manual
1386 l_cost_flag := 'Y';
1387 else
1388 l_cost_flag := 'N';
1389 end if;
1390 close autocharge_type_res_cursor;
1391 end if;
1392 else --time entry source is 'mytime', 'jobop' or 'resource'
1393 if (l_charge_time_resources_pref = '2') then
1394 l_cost_flag := 'N';
1395 else
1396 l_cost_flag := 'Y';
1397 end if;
1398 end if;
1399 end if;
1400 return l_cost_flag;
1401 END get_cost_flag;
1402
1403 /* Get the value for add_to_rtg
1404 Since the insertion of a wor is done through cost txn, so this is simplified */
1405 FUNCTION get_add_to_rtg_flag(p_wip_entity_id IN NUMBER,
1406 p_operation_seq_num IN NUMBER,
1407 p_resource_seq_num IN NUMBER,
1408 p_cost_flag IN VARCHAR2,
1409 p_time_entry_source IN VARCHAR2) return VARCHAR2
1410 IS
1411 l_add_to_rtg_flag varchar2(1);
1412 l_cost_flag varchar(1);
1413 BEGIN
1414 l_cost_flag := p_cost_flag;
1415
1416 if( l_cost_flag is null ) then
1417 l_cost_flag := get_cost_flag(p_wip_entity_id => p_wip_entity_id,
1418 p_operation_seq_num => p_operation_seq_num,
1419 p_resource_seq_num => p_resource_seq_num,
1420 p_time_entry_source => p_time_entry_source);
1421 end if;
1422
1423 if (l_cost_flag = 'Y' and p_resource_seq_num is null) then
1424 l_add_to_rtg_flag := 'Y';
1425 else
1426 l_add_to_rtg_flag := 'N';
1427 end if;
1428
1429 return l_add_to_rtg_flag;
1430 END get_add_to_rtg_flag;
1431
1432 -- Get Organization Id and Department Id.
1433 PROCEDURE get_org_dept_ids(p_wip_entity_id IN NUMBER,
1434 p_operation_seq_num IN NUMBER,
1435 x_organization_id out NOCOPY NUMBER,
1436 x_department_id out NOCOPY NUMBER)
1437 IS
1438 l_organization_id number;
1439 l_department_id number;
1440
1441 cursor org_dept_cursor is select wo.organization_id, wo.department_id
1442 from wip_operations wo
1443 where wo.wip_entity_id = p_wip_entity_id
1444 and wo.operation_seq_num = p_operation_seq_num;
1445 BEGIN
1446 open org_dept_cursor;
1447 fetch org_dept_cursor into l_organization_id, l_department_id;
1448 if org_dept_cursor%NOTFOUND then
1449 l_organization_id := -1;
1450 l_department_id := -1;
1451 end if;
1452 x_organization_id := l_organization_id;
1453 x_department_id := l_department_id;
1454 close org_dept_cursor;
1455 END get_org_dept_ids;
1456
1457 -- Update the value of actual start date in wdj, wo and wor tables
1458 PROCEDURE update_actual_start_dates(p_wip_entity_id IN NUMBER,
1459 p_operation_seq_num IN NUMBER,
1460 p_resource_seq_num IN NUMBER)
1461 IS
1462 l_min_start_date date;
1463
1464 l_min_start_date_wrat date;
1465
1466 cursor min_start_date_wrat_cursor is select min(wrat.start_date)
1467 from wip_resource_actual_times wrat
1468 where wrat.wip_entity_id = p_wip_entity_id
1469 and wrat.operation_seq_num = p_operation_seq_num
1470 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1471 and wrat.start_date is not null
1472 and wrat.process_status <> 4;
1473
1474 cursor min_start_date_wor_cursor is select min(wor.actual_start_date)
1475 from wip_operation_resources wor
1476 where wor.wip_entity_id = p_wip_entity_id
1477 and wor.operation_seq_num = p_operation_seq_num
1478 and wor.actual_start_date is not null;
1479
1480 cursor min_start_date_wo_cursor is select min(wo.actual_start_date)
1481 from wip_operations wo
1482 where wo.wip_entity_id = p_wip_entity_id
1483 and wo.actual_start_date is not null;
1484 BEGIN
1485
1486 l_min_start_date := null;
1487 open min_start_date_wrat_cursor;
1488 fetch min_start_date_wrat_cursor into l_min_start_date;
1489 if min_start_date_wrat_cursor%FOUND then
1490 if l_min_start_date is not null then
1491 update wip_operation_resources set
1492 actual_start_date = l_min_start_date
1493 where wip_entity_id = p_wip_entity_id
1494 and operation_seq_num = p_operation_seq_num
1495 and resource_seq_num = p_resource_seq_num;
1496 end if;
1497 end if;
1498 close min_start_date_wrat_cursor;
1499
1500 /* save it */
1501 l_min_start_date_wrat := l_min_start_date;
1502
1503 l_min_start_date := null;
1504 open min_start_date_wor_cursor;
1505 fetch min_start_date_wor_cursor into l_min_start_date;
1506 if( l_min_start_date_wrat is not null ) then
1507 select decode(l_min_start_date, null, l_min_start_date_wrat,
1508 least(l_min_start_date_wrat, l_min_start_date) )
1509 into l_min_start_date
1510 from dual;
1511 end if;
1512
1513 if l_min_start_date is not null then
1514 update wip_operations set
1515 actual_start_date = l_min_start_date
1516 where wip_entity_id = p_wip_entity_id
1517 and operation_seq_num = p_operation_seq_num;
1518 end if;
1519 close min_start_date_wor_cursor;
1520
1521 open min_start_date_wo_cursor;
1522 fetch min_start_date_wo_cursor into l_min_start_date;
1523 if min_start_date_wo_cursor%FOUND then
1524 if l_min_start_date is not null then
1525 update wip_discrete_jobs set
1526 actual_start_date = l_min_start_date
1527 where wip_entity_id = p_wip_entity_id;
1528 end if;
1529 end if;
1530 close min_start_date_wo_cursor;
1531 END update_actual_start_dates;
1532
1533 -- Update the value of actual completion date in wo and wor tables
1534 PROCEDURE update_actual_completion_dates(p_wip_entity_id IN NUMBER,
1535 p_operation_seq_num IN NUMBER,
1536 p_resource_seq_num IN NUMBER)
1537 IS
1538 l_max_end_date date;
1539 l_active_next_resource_flag varchar2(1);
1540
1541 cursor max_end_date_wrat_cursor is select max(wrat.end_date)
1542 from wip_resource_actual_times wrat
1543 where wrat.wip_entity_id = p_wip_entity_id
1544 and wrat.operation_seq_num = p_operation_seq_num
1545 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1546 and not exists (select wrat.end_date
1547 from wip_resource_actual_times wrat
1548 where wrat.wip_entity_id = p_wip_entity_id
1549 and wrat.operation_seq_num = p_operation_seq_num
1550 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1551 and wrat.end_date is null
1552 and wrat.process_status <> 4)
1553 and wrat.process_status <> 4;
1554
1555 --find out if there are any active next resources
1556 cursor active_next_resource_cursor is select 'Y'
1557 from wip_resource_actual_times wrat,
1558 wip_operation_resources wor
1559 where wrat.wip_entity_id = p_wip_entity_id
1560 and wrat.operation_seq_num = p_operation_seq_num
1561 and wrat.status_type = 1
1562 and wrat.process_status <> 4
1563 and wrat.end_date is null
1564 and wor.scheduled_flag = 4
1565 and wrat.wip_entity_id = wor.wip_entity_id
1566 and wrat.operation_seq_num = wor.operation_seq_num
1567 and wrat.resource_id = wor.resource_id;
1568
1569 cursor max_end_date_wor_cursor is select max(wor.actual_completion_date)
1570 from wip_operation_resources wor
1571 where wor.wip_entity_id = p_wip_entity_id
1572 and wor.operation_seq_num = p_operation_seq_num
1573 and not exists (select wor.actual_completion_date
1574 from wip_operation_resources wor
1575 where wor.wip_entity_id = p_wip_entity_id
1576 and wor.operation_seq_num = p_operation_seq_num
1577 and wor.actual_completion_date is null);
1578 BEGIN
1579 open max_end_date_wrat_cursor;
1580 fetch max_end_date_wrat_cursor into l_max_end_date;
1581 if max_end_date_wrat_cursor%FOUND then
1582 if l_max_end_date is not null then
1583 update wip_operation_resources set
1584 actual_completion_date = l_max_end_date
1585 where wip_entity_id = p_wip_entity_id
1586 and operation_seq_num = p_operation_seq_num
1587 and resource_seq_num = p_resource_seq_num;
1588 end if;
1589 end if;
1590 close max_end_date_wrat_cursor;
1591
1592 open active_next_resource_cursor;
1593 fetch active_next_resource_cursor into l_active_next_resource_flag;
1594 if active_next_resource_cursor%NOTFOUND then
1595 open max_end_date_wor_cursor;
1596 fetch max_end_date_wor_cursor into l_max_end_date;
1597 if max_end_date_wor_cursor%FOUND then
1598 if l_max_end_date is not null then
1599 update wip_operations set
1600 actual_completion_date = l_max_end_date
1601 where wip_entity_id = p_wip_entity_id
1602 and operation_seq_num = p_operation_seq_num;
1603 end if;
1604 end if;
1605 close max_end_date_wor_cursor;
1606 end if;
1607 close active_next_resource_cursor;
1608 END update_actual_completion_dates;
1609
1610 -- Update the value of projected completion date in wo and wor tables.
1611 PROCEDURE update_proj_completion_dates(p_organization_id IN NUMBER,
1612 p_wip_entity_id IN NUMBER,
1613 p_operation_seq_num IN NUMBER,
1614 p_resource_seq_num IN NUMBER,
1615 p_resource_id IN NUMBER,
1616 p_start_date IN DATE)
1617 IS
1618 l_projected_completion_date date;
1619 BEGIN
1620 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
1621 p_wip_entity_id => p_wip_entity_id,
1622 p_op_seq_num => p_operation_seq_num,
1623 p_resource_seq_num => p_resource_seq_num,
1624 p_resource_id => p_resource_id,
1625 p_instance_id => null,
1626 p_start_date => p_start_date);
1627 update wip_operation_resources set
1628 projected_completion_date = l_projected_completion_date
1629 where wip_entity_id = p_wip_entity_id
1630 and operation_seq_num = p_operation_seq_num
1631 and resource_seq_num = p_resource_seq_num;
1632
1633 l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
1634 p_wip_entity_id => p_wip_entity_id,
1635 p_op_seq_num => p_operation_seq_num,
1636 p_resource_seq_num => null,
1637 p_resource_id => null,
1638 p_instance_id => null,
1639 p_start_date => p_start_date);
1640 update wip_operations set
1641 projected_completion_date = l_projected_completion_date
1642 where wip_entity_id = p_wip_entity_id
1643 and operation_seq_num = p_operation_seq_num;
1644 END update_proj_completion_dates;
1645
1646 -- Get the on/off status of the job.
1647 FUNCTION get_job_on_off_status(p_wip_entity_id IN NUMBER,
1648 p_operation_seq_num IN NUMBER) return VARCHAR2
1649 IS
1650 l_job_status varchar2(1);
1651 l_employee_id number;
1652
1653 cursor job_status_cursor is select employee_id
1654 from wip_operations wo
1655 where wo.wip_entity_id = p_wip_entity_id
1656 and wo.operation_seq_num = p_operation_seq_num;
1657 BEGIN
1658 l_job_status := 'N';
1659
1660 open job_status_cursor;
1661 fetch job_status_cursor into l_employee_id;
1662 if job_status_cursor%FOUND then
1663 if l_employee_id is not null then
1664 l_job_status := 'Y';
1665 end if;
1666 end if;
1667 close job_status_cursor;
1668
1669 return l_job_status;
1670 END get_job_on_off_status;
1671
1672 -- Set job on.
1673 PROCEDURE job_on(p_wip_entity_id IN NUMBER,
1674 p_operation_seq_num IN NUMBER,
1675 p_employee_id IN NUMBER,
1676 x_status out NOCOPY VARCHAR2,
1677 x_msg_count out NOCOPY NUMBER,
1678 x_msg out NOCOPY VARCHAR2)
1679 IS
1680 l_job_status varchar2(1);
1681 l_status varchar2(1);
1682 l_msg_count number;
1683 l_msg varchar2(10);
1684 lx_return_status varchar2(10);
1685 BEGIN
1686 l_status := 'U';
1687 l_job_status := get_job_on_off_status(p_wip_entity_id => p_wip_entity_id,
1688 p_operation_seq_num => p_operation_seq_num);
1689
1690 if (l_job_status = 'Y') then
1691 l_status := 'O';
1692 elsif (l_job_status = 'N') then
1693 update wip_operations set
1694 employee_id = p_employee_id
1695 where wip_entity_id = p_wip_entity_id
1696 and operation_seq_num = p_operation_seq_num;
1697 l_status := 'S';
1698 end if;
1699
1700 x_status := l_status;
1701 x_msg_count := l_msg_count;
1702 x_msg := l_msg;
1703 END job_on;
1704
1705
1706 -- Set job off.
1707 PROCEDURE job_off(p_wip_entity_id IN NUMBER,
1708 p_operation_seq_num IN NUMBER,
1709 x_status out NOCOPY VARCHAR2,
1710 x_msg_count out NOCOPY NUMBER,
1711 x_msg out NOCOPY VARCHAR2)
1712 IS
1713 l_job_status varchar2(1);
1714
1715 l_status varchar2(1);
1716 l_msg_count number;
1717 l_msg varchar2(255);
1718 BEGIN
1719 l_status := 'U';
1720 l_job_status := get_job_on_off_status(p_wip_entity_id => p_wip_entity_id,
1721 p_operation_seq_num => p_operation_seq_num);
1722
1723 if (l_job_status = 'N') then
1724 l_status := 'O';
1725 elsif (l_job_status = 'Y') then
1726
1727 /* clock out labors */
1728 clock_out_labors(p_wip_entity_id, p_operation_seq_num, null);
1729
1730 /* clock out all machines as well */
1731 clock_out_machines(p_wip_entity_id, p_operation_seq_num);
1732
1733 /* remove the employee stamp */
1734 job_off_internal(p_wip_entity_id, p_operation_seq_num);
1735
1736 /* process the records */
1737 process_time_records(p_wip_entity_id => p_wip_entity_id,
1738 p_completed_op => p_operation_seq_num,
1739 p_instance_id => null,
1740 p_time_entry_source => 'clock');
1741 l_status := 'S';
1742 end if;
1743
1744 x_status := l_status;
1745 x_msg_count := l_msg_count;
1746 x_msg := l_msg;
1747 END job_off;
1748 --emp_valid
1749
1750
1751 PROCEDURE emp_valid(p_wip_employee_id IN NUMBER,
1752 p_org_id IN NUMBER,
1753 x_status OUT nocopy Boolean,
1754 x_person_id OUT nocopy number
1755 ) IS
1756
1757 l_status boolean:=true;
1758 l_person_id NUMBER := null;
1759 l_dummy_var NUMBER;
1760
1761 CURSOR emp_valid_cursor IS
1762 select bre.person_id
1763 from per_all_people_f papf,
1764 bom_resource_employees bre
1765 where papf.person_id = bre.person_id
1766 and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
1767 and bre.organization_id = p_org_id
1768 and papf.employee_number = p_wip_employee_id
1769 and rownum=1;
1770
1771 BEGIN
1772 --if multiple persons are there then it will give one person id
1773
1774 OPEN emp_valid_cursor;
1775 FETCH emp_valid_cursor
1776 INTO l_person_id;
1777
1778 --IF emp_valid_cursor % FOUND THEN
1779 IF (l_person_id is not null) THEN
1780 l_status:=WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,l_person_id);
1781 end if;
1782
1783 x_status:=l_status;
1784 x_person_id:=l_person_id;
1785
1786 EXCEPTION
1787 WHEN OTHERS
1788 THEN
1789 l_person_id := null;
1790
1791 close emp_valid_cursor;
1792 END emp_valid;
1793
1794 -- Set Shift in
1795 PROCEDURE shift_in(p_wip_employee_id IN NUMBER,
1796 p_org_id IN NUMBER,
1797 x_status OUT nocopy VARCHAR2
1798 ) IS
1799 l_shift_status VARCHAR2(1);
1800 l_dummy_var NUMBER;
1801 badge_validation Boolean:=FALSE;
1802 l_person_id number;
1803 l_time_entry number;
1804 l_ret_status varchar2(200);
1805
1806 CURSOR shift_in_cursor IS
1807 SELECT 1
1808 FROM wip_resource_actual_times wrat
1809 WHERE wrat.wip_entity_id IS NULL
1810 AND wrat.end_date IS NULL
1811 and wrat.employee_id = l_person_id
1812 and wrat.time_entry_mode = 8
1813 and organization_id = p_org_id;
1814
1815
1816 BEGIN
1817
1818 l_shift_status := 'U';
1819
1820
1821
1822 emp_valid(p_wip_employee_id =>p_wip_employee_id,
1823 p_org_id =>p_org_id,
1824 x_status =>badge_validation,
1825 x_person_id=>l_person_id
1826 );
1827
1828 OPEN shift_in_cursor;
1829 FETCH shift_in_cursor
1830 INTO l_dummy_var;
1831
1832 IF (badge_validation = true) then
1833 l_shift_status :='N';
1834 ELSIF shift_in_cursor % FOUND THEN
1835 --Already Shifted in
1836 l_shift_status := 'C';
1837 ELSE
1838 --shift in employee for which badge number was entered
1839 -- removed hardcoding of resource_id for bug 6969269.
1840 insert into wip_resource_actual_times
1841 (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
1842 OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
1843 INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
1844 COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
1845 END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
1846 UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
1847 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
1848 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
1849 ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
1850 PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
1851 values
1852 (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
1853 null,null,null,null,null,null,8,'N','N',null,
1854 sysdate,null,null,null,
1855 fnd_profile.value('BOM:HOUR_UOM_CODE'),
1856 l_person_id,1,fnd_global.user_id,
1857 sysdate,fnd_global.user_id,sysdate,
1858 fnd_global.login_id,1,null,null,null,
1859 null,null);
1860
1861
1862 l_shift_status := 'S';
1863 END IF;
1864
1865 CLOSE shift_in_cursor;
1866 x_status := l_shift_status;
1867 END shift_in;
1868
1869 -- Set Shift Out
1870 PROCEDURE shift_out(p_wip_employee_id IN NUMBER,
1871 p_org_id IN NUMBER,
1872 x_status out NOCOPY VARCHAR2
1873 ) is
1874
1875 l_shift_status varchar2(1);
1876 l_start_date date;
1877 l_date date;
1878 l_duration number;
1879 badge_validation Boolean:= false;
1880 l_person_id number;
1881
1882 cursor shift_out_test_cursor is
1883 select start_date
1884 from wip_resource_actual_times wrat
1885 where wrat.wip_entity_id is null
1886 and wrat.EMPLOYEE_ID=l_person_id
1887 and wrat.ORGANIZATION_ID=p_org_id
1888 and wrat.end_date is null
1889 and wrat.time_entry_mode = 8;
1890
1891 BEGIN
1892 l_shift_status := 'U';
1893
1894
1895 emp_valid(p_wip_employee_id =>p_wip_employee_id,
1896 p_org_id =>p_org_id,
1897 x_status=>badge_validation,
1898 x_person_id=>l_person_id
1899 );
1900
1901 open shift_out_test_cursor;
1902 fetch shift_out_test_cursor into l_start_date;
1903
1904
1905 IF (badge_validation = true) then
1906 l_shift_status :='N';
1907 elsif shift_out_test_cursor%NOTFOUND then
1908 l_shift_status := 'O';
1909 else
1910 l_date := sysdate;
1911 l_duration := (l_date - l_start_date)*24;
1912
1913 update wip_resource_actual_times set
1914 end_date = l_date,
1915 duration = l_duration
1916 where ORGANIZATION_ID=p_org_id
1917 and employee_id =l_person_id
1918 and end_date is null
1919 and wip_entity_id is null;
1920
1921 l_shift_status := 'S';
1922 end if;
1923 close shift_out_test_cursor;
1924
1925 x_status := l_shift_status;
1926
1927 END shift_out;
1928
1929 -- Set Undo Shift In
1930 PROCEDURE undo_shift_in(p_wip_employee_id IN NUMBER,
1931 p_org_id IN NUMBER,
1932 x_status out NOCOPY VARCHAR2
1933 )
1934 IS
1935 l_shift_status varchar2(1);
1936 l_dummy_var number;
1937 badge_validation Boolean :=false;
1938 l_person_id number;
1939
1940 cursor undo_shift_in_test_cursor is
1941 select 1
1942 from wip_resource_actual_times wrat
1943 where wrat.wip_entity_id is null
1944 and wrat.EMPLOYEE_ID=l_person_id
1945 and wrat.ORGANIZATION_ID=p_org_id
1946 and wrat.end_date is null;
1947 BEGIN
1948 l_shift_status := 'U';
1949
1950 emp_valid(p_wip_employee_id =>p_wip_employee_id,
1951 p_org_id =>p_org_id,
1952 x_status=>badge_validation,
1953 x_person_id=>l_person_id
1954 );
1955
1956 open undo_shift_in_test_cursor;
1957 fetch undo_shift_in_test_cursor into l_dummy_var;
1958
1959
1960 IF (badge_validation = true) then
1961 l_shift_status :='N';
1962 elsif undo_shift_in_test_cursor%NOTFOUND then
1963 l_shift_status := 'O';
1964 else
1965 delete from wip_resource_actual_times wrat
1966 where
1967 wrat.wip_entity_id is null
1968 and wrat.EMPLOYEE_ID=l_person_id
1969 and wrat.ORGANIZATION_ID=p_org_id
1970 and wrat.end_date is null;
1971 l_shift_status := 'S';
1972 end if;
1973 x_status := l_shift_status;
1974
1975 END undo_shift_in;
1976 --User mode Shift functionality
1977 PROCEDURE shift_in_UM(p_wip_employee_id IN NUMBER,
1978 p_org_id IN NUMBER,
1979 x_status OUT nocopy VARCHAR2
1980 ) IS
1981 l_shift_status VARCHAR2(1);
1982 l_dummy_var NUMBER;
1983 badge_validation Boolean:=FALSE;
1984 l_person_id number;
1985 l_time_entry number;
1986 l_ret_status varchar2(200);
1987
1988 CURSOR shift_in_cursor IS
1989 SELECT 1
1990 FROM wip_resource_actual_times wrat
1991 WHERE wrat.wip_entity_id IS NULL
1992 AND wrat.end_date IS NULL
1993 and wrat.employee_id = p_wip_employee_id
1994 and wrat.time_entry_mode = 8;
1995
1996
1997 BEGIN
1998
1999 l_shift_status := 'U';
2000
2001 badge_validation:=WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2002
2003 OPEN shift_in_cursor;
2004 FETCH shift_in_cursor
2005 INTO l_dummy_var;
2006
2007 IF (badge_validation = true) then
2008 l_shift_status :='N';
2009 ELSIF shift_in_cursor % FOUND THEN
2010 --Already Shifted in
2011 l_shift_status := 'C';
2012 ELSE
2013 --shift in employee for which badge number was entered
2014 -- removed hardcoding of resource_id for bug 6969269.
2015 insert into wip_resource_actual_times
2016 (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
2017 OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
2018 INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
2019 COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
2020 END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
2021 UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
2022 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
2023 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
2024 ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
2025 PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
2026 values
2027 (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
2028 null,null,null,null,null,null,8,'N','N',null,
2029 sysdate,null,null,null,
2030 fnd_profile.value('BOM:HOUR_UOM_CODE'),
2031 p_wip_employee_id,1,fnd_global.user_id,
2032 sysdate,fnd_global.user_id,sysdate,
2033 fnd_global.login_id,1,null,null,null,
2034 null,null);
2035
2036
2037 l_shift_status := 'S';
2038 END IF;
2039
2040 CLOSE shift_in_cursor;
2041 x_status := l_shift_status;
2042 END shift_in_UM;
2043
2044 -- Set Shift Out
2045 PROCEDURE shift_out_UM(p_wip_employee_id IN NUMBER,
2046 p_org_id IN NUMBER,
2047 x_status out NOCOPY VARCHAR2
2048 ) is
2049
2050 l_shift_status varchar2(1);
2051 l_start_date date;
2052 l_date date;
2053 l_duration number;
2054 badge_validation Boolean:= false;
2055 l_person_id number;
2056
2057 cursor shift_out_test_cursor is
2058 select start_date
2059 from wip_resource_actual_times wrat
2060 where wrat.wip_entity_id is null
2061 and wrat.EMPLOYEE_ID=p_wip_employee_id
2062 and wrat.ORGANIZATION_ID=p_org_id
2063 and wrat.end_date is null;
2064
2065 BEGIN
2066 l_shift_status := 'U';
2067
2068
2069 badge_validation:=WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2070
2071 open shift_out_test_cursor;
2072 fetch shift_out_test_cursor into l_start_date;
2073
2074
2075 IF (badge_validation = true) then
2076 l_shift_status :='N';
2077 elsif shift_out_test_cursor%NOTFOUND then
2078 l_shift_status := 'O';
2079 else
2080 l_date := sysdate;
2081 l_duration := (l_date - l_start_date)*24;
2082
2083 update wip_resource_actual_times set
2084 end_date = l_date,
2085 duration = l_duration
2086 where ORGANIZATION_ID=p_org_id
2087 and employee_id =p_wip_employee_id
2088 and end_date is null
2089 and wip_entity_id is null;
2090
2091 l_shift_status := 'S';
2092 end if;
2093 close shift_out_test_cursor;
2094
2095 x_status := l_shift_status;
2096
2097 END shift_out_UM;
2098
2099 -- Set Undo Shift In
2100 PROCEDURE undo_shift_in_UM(p_wip_employee_id IN NUMBER,
2101 p_org_id IN NUMBER,
2102 x_status out NOCOPY VARCHAR2
2103 )
2104 IS
2105 l_shift_status varchar2(1);
2106 l_dummy_var number;
2107 badge_validation Boolean :=false;
2108 l_person_id number;
2109
2110 cursor undo_shift_in_test_cursor is
2111 select 1
2112 from wip_resource_actual_times wrat
2113 where wrat.wip_entity_id is null
2114 and wrat.EMPLOYEE_ID=p_wip_employee_id
2115 and wrat.ORGANIZATION_ID=p_org_id
2116 and wrat.end_date is null;
2117 BEGIN
2118 l_shift_status := 'U';
2119
2120 badge_validation:=WIP_TIME_ENTRY_PUB.is_emp_invalid(p_org_id,null,null,p_wip_employee_id);
2121
2122 open undo_shift_in_test_cursor;
2123 fetch undo_shift_in_test_cursor into l_dummy_var;
2124
2125
2126 IF (badge_validation = true) then
2127 l_shift_status :='N';
2128 elsif undo_shift_in_test_cursor%NOTFOUND then
2129 l_shift_status := 'O';
2130 else
2131 delete from wip_resource_actual_times wrat
2132 where
2133 wrat.wip_entity_id is null
2134 and wrat.EMPLOYEE_ID=p_wip_employee_id
2135 and wrat.ORGANIZATION_ID=p_org_id
2136 and wrat.end_date is null;
2137 l_shift_status := 'S';
2138 end if;
2139 x_status := l_shift_status;
2140
2141 END undo_shift_in_UM;
2142
2143
2144 -- Set clock in.
2145 PROCEDURE clock_in(p_wip_entity_id IN NUMBER,
2146 p_operation_seq_num IN NUMBER,
2147 p_responsibility_key IN VARCHAR2,
2148 p_dept_id IN NUMBER,
2149 p_employee_id IN NUMBER,
2150 p_instance_id IN NUMBER,
2151 p_resource_id IN NUMBER,
2152 p_resource_seq_num IN NUMBER,
2153 x_status out NOCOPY VARCHAR2,
2154 x_msg_count out NOCOPY NUMBER,
2155 x_msg out NOCOPY VARCHAR2)
2156 IS
2157 lx_status varchar2(1);
2158 lx_msg_count number;
2159 lx_msg varchar2(255);
2160 l_clock_status varchar2(1);
2161 l_dummy_var number;
2162 l_resource_id number;
2163 l_resource_seq_num number;
2164 l_uom_code varchar2(3);
2165 l_scheduled_flag number;
2166 lx_organization_id number;
2167 lx_department_id number;
2168 lx_time_entry_id number;
2169 lx_return_status varchar2(10);
2170 l_num_job_op number;
2171 l_skill_check number;
2172
2173 cursor clock_in_cursor is select 1
2174 from wip_resource_actual_times wrat
2175 where wrat.wip_entity_id = p_wip_entity_id
2176 and wrat.operation_seq_num = p_operation_seq_num
2177 and wrat.resource_id = p_resource_id
2178 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
2179 and wrat.employee_id = p_employee_id
2180 and wrat.instance_id = p_instance_id
2181 and wrat.status_type = 1
2182 and wrat.end_date is null;
2183
2184 cursor resource_uom_cursor is select unit_of_measure
2185 from bom_resources br
2186 where br.resource_id = p_resource_id;
2187
2188 cursor scheduled_flag_cursor is
2189 select scheduled_flag
2190 from wip_operation_resources wor
2191 where wor.wip_entity_id = p_wip_entity_id
2192 and wor.operation_seq_num = p_operation_seq_num
2193 and wor.resource_seq_num = p_resource_seq_num;
2194
2195 /* check if there is no clock-in in the scheduled flag group */
2196 cursor num_job_op_cursor(p_scheduled_flag varchar2) is
2197 select count(*)
2198 from wip_resource_actual_times wrat, wip_operation_resources wor
2199 where wrat.wip_entity_id = p_wip_entity_id
2200 and wrat.operation_seq_num = p_operation_seq_num
2201 and wrat.wip_entity_id = wor.wip_entity_id (+)
2202 and wrat.operation_seq_num = wor.operation_seq_num (+)
2203 and wrat.resource_seq_num = wor.resource_seq_num(+)
2204 and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) =
2205 decode(p_scheduled_flag, null, 1, 2, 1, p_scheduled_flag)
2206 and wrat.status_type = 1
2207 and wrat.start_date is not null
2208 and wrat.end_date is null;
2209
2210 cursor time_records_machine_cursor(c_scheduled_flag number) is
2211 select wor.resource_id,
2212 wor.resource_seq_num,
2213 wor.uom_code
2214 from wip_operation_resources wor,
2215 bom_resources br
2216 where wor.wip_entity_id = p_wip_entity_id
2217 and wor.operation_seq_num = p_operation_seq_num
2218 and decode(wor.scheduled_flag, 2, 1, wor.scheduled_flag) =
2219 decode(c_scheduled_flag, 2, 1, c_scheduled_flag)
2220 and br.resource_type = 1 --machine resource
2221 and wor.resource_id = br.resource_id
2222 and not exists (select 1
2223 from wip_resource_actual_times wrat
2224 where wrat.wip_entity_id = p_wip_entity_id
2225 and wrat.operation_seq_num = p_operation_seq_num
2226 and wrat.resource_id = wor.resource_id
2227 and wrat.resource_seq_num = wor.resource_seq_num
2228 and wrat.status_type = 1
2229 and wrat.end_date is null);
2230 BEGIN
2231 l_clock_status := 'U';
2232
2233 if WIP_TIME_ENTRY_PUB.is_emp_shift_in(p_wip_entity_id => p_wip_entity_id,
2234 p_employee_id => p_employee_id) then
2235
2236 l_skill_check := WIP_WS_SKILL_CHECK_PVT.validate_skill_for_clock_in(
2237 p_wip_entity_id => p_wip_entity_id,
2238 p_op_seq_num => p_operation_seq_num,
2239 p_emp_id => p_employee_id);
2240
2241 if l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_SKILL_VALIDATION_SUCCESS then
2242
2243 open clock_in_cursor;
2244 fetch clock_in_cursor into l_dummy_var;
2245 if clock_in_cursor%FOUND then
2246 l_clock_status := 'C';
2247 else
2248 job_on(p_wip_entity_id => p_wip_entity_id,
2249 p_operation_seq_num => p_operation_seq_num,
2250 p_employee_id => p_employee_id,
2251 x_status => lx_status,
2252 x_msg_count => lx_msg_count,
2253 x_msg => lx_msg);
2254
2255 get_org_dept_ids(p_wip_entity_id => p_wip_entity_id,
2256 p_operation_seq_num => p_operation_seq_num,
2257 x_organization_id => lx_organization_id,
2258 x_department_id => lx_department_id);
2259
2260 open resource_uom_cursor;
2261 fetch resource_uom_cursor into l_uom_code;
2262 close resource_uom_cursor;
2263
2264 --clock in employee for which badge number was entered
2265 record_insert(p_time_entry_id => null,
2266 p_organization_id => lx_organization_id,
2267 p_wip_entity_id => p_wip_entity_id,
2268 p_operation_seq_num => p_operation_seq_num,
2269 p_resource_id => p_resource_id,
2270 p_resource_seq_num => p_resource_seq_num,
2271 p_instance_id => p_instance_id,
2272 p_serial_number => null,
2273 p_last_update_date => null,
2274 p_last_updated_by => null,
2275 p_creation_date => null,
2276 p_created_by => null,
2277 p_last_update_login => null,
2278 p_object_version_num => null,
2279 p_time_entry_mode => null,
2280 p_cost_flag => null,
2281 p_add_to_rtg => null,
2282 p_status_type => null,
2283 p_start_date => sysdate,
2284 p_end_date => null,
2285 p_projected_completion_date => null,
2286 p_duration => null,
2287 p_uom_code => l_uom_code,
2288 p_employee_id => p_employee_id,
2289 x_time_entry_id => lx_time_entry_id,
2290 x_return_status => lx_return_status);
2291 l_clock_status := 'S';
2292 end if;
2293 close clock_in_cursor;
2294
2295 open scheduled_flag_cursor;
2296 fetch scheduled_flag_cursor into l_scheduled_flag;
2297 close scheduled_flag_cursor;
2298
2299 /* ad-hoc resource don't have a scheduled_flag, treat it as no-scheduled*/
2300 if(l_scheduled_flag is null) then l_scheduled_flag := 2; end if;
2301
2302 open num_job_op_cursor(l_scheduled_flag);
2303 fetch num_job_op_cursor into l_num_job_op;
2304 close num_job_op_cursor;
2305
2306 --this is the first clock in for this job operation
2307 if (l_num_job_op = 1) then
2308
2309 open time_records_machine_cursor(l_scheduled_flag);
2310 loop
2311 fetch time_records_machine_cursor into l_resource_id,
2312 l_resource_seq_num,
2313 l_uom_code;
2314 exit when time_records_machine_cursor%NOTFOUND;
2315
2316 --clock in machines that have not already been clocked in
2317 record_insert(p_time_entry_id => null,
2318 p_organization_id => lx_organization_id,
2319 p_wip_entity_id => p_wip_entity_id,
2320 p_operation_seq_num => p_operation_seq_num,
2321 p_resource_id => l_resource_id,
2322 p_resource_seq_num => l_resource_seq_num,
2323 p_instance_id => null,
2324 p_serial_number => null,
2325 p_last_update_date => null,
2326 p_last_updated_by => null,
2327 p_creation_date => null,
2328 p_created_by => null,
2329 p_last_update_login => null,
2330 p_object_version_num => null,
2331 p_time_entry_mode => null,
2332 p_cost_flag => null,
2333 p_add_to_rtg => null,
2334 p_status_type => null,
2335 p_start_date => sysdate,
2336 p_end_date => null,
2337 p_projected_completion_date => null,
2338 p_duration => null,
2339 p_uom_code => l_uom_code,
2340 p_employee_id => p_employee_id,
2341 x_time_entry_id => lx_time_entry_id,
2342 x_return_status => lx_return_status);
2343 end loop;
2344 close time_records_machine_cursor;
2345 end if;
2346
2347 process_time_records(p_wip_entity_id => p_wip_entity_id,
2348 p_completed_op => p_operation_seq_num,
2349 p_instance_id => null,
2350 p_time_entry_source => 'clock');
2351
2352
2353 x_status := l_clock_status;
2354
2355 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_COMPETENCE_CHECK_FAIL then
2356 x_status := 'P';
2357 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_CERTIFY_CHECK_FAIL then
2358 x_status := 'Q';
2359 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_QUALIFY_CHECK_FAIL then
2360 x_status := 'R';
2361 elsif l_skill_check = WIP_WS_SKILL_CHECK_PVT.G_SKILL_VALIDATION_EXCEPTION then
2362 x_status := 'U';
2363 end if;
2364 else
2365 x_status := 'H';
2366 end if;
2367 END clock_in;
2368
2369 -- Set clock out.
2370 PROCEDURE clock_out(p_wip_entity_id IN NUMBER,
2371 p_operation_seq_num IN NUMBER,
2372 p_responsibility_key IN VARCHAR2,
2373 p_dept_id IN NUMBER,
2374 p_employee_id IN NUMBER,
2375 p_instance_id IN NUMBER,
2376 p_resource_id IN NUMBER,
2377 p_resource_seq_num IN NUMBER,
2378 x_status out NOCOPY VARCHAR2,
2379 x_msg_count out NOCOPY NUMBER,
2380 x_msg out NOCOPY VARCHAR2)
2381 IS
2382 l_clock_status varchar2(1);
2383 l_process_status number;
2384 l_object_version_num number;
2385 l_last_op_qty_num number;
2386 l_last_job_qty_num number;
2387 l_last_op_qty varchar2(1);
2388 l_last_job_qty varchar2(1);
2389 l_start_date date;
2390 l_date date;
2391 l_duration number;
2392 l_uom_code varchar2(3);
2393
2394 cursor clock_out_test_cursor is
2395 select object_version_number,
2396 start_date
2397 from wip_resource_actual_times
2398 where wip_entity_id = p_wip_entity_id
2399 and operation_seq_num = p_operation_seq_num
2400 and resource_id = p_resource_id
2401 /* and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1) BUG 7322174*/
2402 and employee_id = p_employee_id
2403 and instance_id = p_instance_id
2404 and status_type = 1
2405 and end_date is null;
2406 BEGIN
2407 l_clock_status := 'U';
2408 l_process_status := 3; --updated
2409 l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
2410
2411 open clock_out_test_cursor;
2412 fetch clock_out_test_cursor into l_object_version_num,
2413 l_start_date;
2414 if clock_out_test_cursor%NOTFOUND then
2415 l_clock_status := 'O';
2416 else
2417 l_date := sysdate;
2418 l_duration := (l_date - l_start_date)*24;
2419 update wip_resource_actual_times set
2420 end_date = l_date,
2421 duration = l_duration,
2422 uom_code = l_uom_code,
2423 process_status = l_process_status,
2424 object_version_number = l_object_version_num + 1,
2425 last_update_date = l_date,
2426 last_updated_by = fnd_global.user_id,
2427 last_update_login = fnd_global.login_id
2428 where wip_entity_id = p_wip_entity_id
2429 and operation_seq_num = p_operation_seq_num
2430 and resource_id = p_resource_id
2431 and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
2432 and employee_id = p_employee_id
2433 and instance_id = p_instance_id
2434 and status_type = 1
2435 and end_date is null;
2436
2437 process_time_records(p_wip_entity_id => p_wip_entity_id,
2438 p_completed_op => p_operation_seq_num,
2439 p_instance_id => null,
2440 p_time_entry_source => 'clock');
2441
2442 l_clock_status := 'S';
2443 end if;
2444 close clock_out_test_cursor;
2445 /* Added for bug 6891758.*/
2446 if G_RES_CHG_FAILED =wip_constants.yes then
2447 l_clock_status := 'T';
2448 end if;
2449 x_status := l_clock_status;
2450 END clock_out;
2451
2452 -- Set undo clock in.
2453 PROCEDURE undo_clock_in(p_wip_entity_id IN NUMBER,
2454 p_operation_seq_num IN NUMBER,
2455 p_responsibility_key IN VARCHAR2,
2456 p_dept_id IN NUMBER,
2457 p_employee_id IN NUMBER,
2458 p_instance_id IN NUMBER,
2459 p_resource_id IN NUMBER,
2460 p_resource_seq_num IN NUMBER,
2461 x_status out NOCOPY VARCHAR2,
2462 x_msg_count out NOCOPY NUMBER,
2463 x_msg out NOCOPY VARCHAR2)
2464 IS
2465 l_clock_status varchar2(1);
2466 lx_return_status varchar2(10);
2467 BEGIN
2468 l_clock_status := 'U';
2469 record_delete(p_wip_entity_id => p_wip_entity_id,
2470 p_operation_seq_num => p_operation_seq_num,
2471 p_employee_id => p_employee_id,
2472 x_return_status => lx_return_status);
2473
2474 if (lx_return_status = 'S') then
2475 process_time_records(p_wip_entity_id => p_wip_entity_id,
2476 p_completed_op => p_operation_seq_num,
2477 p_instance_id => null,
2478 p_time_entry_source => 'clock');
2479 l_clock_status := 'S';
2480 elsif (lx_return_status = 'U') then
2481 l_clock_status := 'E';
2482 end if;
2483 x_status := l_clock_status;
2484 END undo_clock_in;
2485
2486 -- Get last operation quantity.
2487 FUNCTION get_last_op_qty(p_wip_entity_id IN NUMBER,
2488 p_operation_seq_num IN NUMBER) return NUMBER
2489 IS
2490 l_last_op_qty number;
2491
2492 cursor last_op_qty_cursor is
2493 select (wo.quantity_in_queue + wo.quantity_running)
2494 from wip_operations wo
2495 where wo.wip_entity_id = p_wip_entity_id
2496 and wo.operation_seq_num = p_operation_seq_num;
2497 BEGIN
2498 open last_op_qty_cursor;
2499 fetch last_op_qty_cursor into l_last_op_qty;
2500 close last_op_qty_cursor;
2501 return l_last_op_qty;
2502 END get_last_op_qty;
2503
2504 -- Get last job quantity.
2505 FUNCTION get_last_job_qty(p_wip_entity_id IN NUMBER,
2506 p_operation_seq_num IN NUMBER) return NUMBER
2507 IS
2508 l_last_job_qty number;
2509
2510 cursor last_job_qty_cursor is
2511 select (wo.scheduled_quantity - wo.quantity_completed - nvl(wo.cumulative_scrap_quantity, 0))
2512 from wip_operations wo
2513 where wo.wip_entity_id = p_wip_entity_id
2514 and wo.operation_seq_num = p_operation_seq_num;
2515 BEGIN
2516 open last_job_qty_cursor;
2517 fetch last_job_qty_cursor into l_last_job_qty;
2518 close last_job_qty_cursor;
2519 return l_last_job_qty;
2520 END get_last_job_qty;
2521
2522 -- Get the instance id.
2523 FUNCTION get_instance_id(p_org_id IN NUMBER,
2524 p_employee_id IN NUMBER) return NUMBER
2525 IS
2526 l_instance_id number;
2527
2528 cursor instance_id_cursor is
2529 select instance_id
2530 from bom_resource_employees bre
2531 where organization_id = p_org_id
2532 and person_id = p_employee_id;
2533 BEGIN
2534 open instance_id_cursor;
2535 fetch instance_id_cursor into l_instance_id;
2536 close instance_id_cursor;
2537
2538 return l_instance_id;
2539 END get_instance_id;
2540
2541 -- Check pending clockouts.
2542 FUNCTION is_clock_pending(p_wip_entity_id IN NUMBER,
2543 p_operation_seq_num IN NUMBER) return VARCHAR2
2544 IS
2545 l_status varchar2(1);
2546 l_dummy_var varchar2(1);
2547
2548 cursor pending_clockout_cursor is
2549 select count(*)
2550 from wip_resource_actual_times
2551 where wip_entity_id = p_wip_entity_id
2552 and operation_seq_num = nvl(p_operation_seq_num, operation_seq_num)
2553 and status_type = 1
2554 and start_date is not null
2555 and end_date is null;
2556 BEGIN
2557 l_status := 'U';
2558
2559 open pending_clockout_cursor;
2560 fetch pending_clockout_cursor into l_dummy_var;
2561 if l_dummy_var > 0 then
2562 l_status := 'Y';
2563 else
2564 l_status := 'N';
2565 end if;
2566 close pending_clockout_cursor;
2567
2568 return l_status;
2569 END is_clock_pending;
2570
2571 /* To Check if there are any pending clock-outs for an employee */
2572 FUNCTION is_emp_clock_out_pending(p_employee_number IN NUMBER,
2573 p_organization_id IN NUMBER,
2574 p_user_mode IN VARCHAR2) return NUMBER
2575 IS
2576 l_emp_clock_ins Number := 0;
2577 l_person_id Number;
2578 badge_validation boolean := true;
2579
2580 BEGIN
2581
2582 /* Get person_id from badge entered for multi user mode.
2583 For single user mode, person_id itself is passed. */
2584
2585 if p_user_mode = 'M' then
2586 emp_valid(p_wip_employee_id => p_employee_number,
2587 p_org_id => p_organization_id,
2588 x_status => badge_validation,
2589 x_person_id => l_person_id);
2590 else
2591 l_person_id := p_employee_number;
2592 badge_validation := false;
2593 end if;
2594
2595 /* For Invalid badge we can skip this validation since Shift-Out will fail.*/
2596
2597 if not badge_validation then
2598 select count(1)
2599 into l_emp_clock_ins
2600 from dual
2601 where exists( select wip_entity_id
2602 from wip_resource_actual_times
2603 where organization_id = p_organization_id
2604 and employee_id = l_person_id
2605 and end_date is null
2606 and wip_entity_id is not null );
2607 end if;
2608
2609 return l_emp_clock_ins;
2610
2611 END is_emp_clock_out_pending;
2612
2613 END WIP_WS_TIME_ENTRY;