[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_UTIL
Source
1 package body wip_ws_util as
2 /* $Header: wipwsutb.pls 120.31.12020000.4 2012/12/25 00:47:13 hliew ship $ */
3
4 --Bug 6889755 Component Shortage Constants
5 ORG_CALCULATION_LEVEL CONSTANT NUMBER := 1;
6 SUBINV_CALCULATION_LEVEL CONSTANT NUMBER := 2;
7 --End Bug 6889755
8
9 function get_instance_name(p_instance_name varchar2, p_serial_number varchar2)
10 return VARCHAR2
11 IS
12 begin
13
14 if( p_serial_number is not null) then
15 fnd_message.SET_NAME('WIP', 'WIP_WS_INSTANCE_NAME');
16 fnd_message.SET_TOKEN('INSTANCE', p_instance_name);
17 fnd_message.SET_TOKEN('SERIAL', p_serial_number);
18 return fnd_message.GET;
19 else
20 return p_instance_name;
21 end if;
22 end;
23
24 function get_preference_value_code(
25 p_pref_id number,
26 p_resp_key varchar2,
27 p_org_id number,
28 p_dept_id number
29 ) return varchar2
30 Is
31 Begin
32 return
33 get_preference_value_code
34 (
35 p_pref_id,
36 get_preference_level_id(p_pref_id, p_resp_key, p_org_id, p_dept_id)
37 );
38 End get_preference_value_code;
39
40 function get_preference_level_id(
41 p_pref_id number,
42 p_resp_key varchar2,
43 p_org_id number,
44 p_dept_id number
45 ) return number
46 IS
47 l_level_code number;
48 l_module_id number;
49 l_level_id number;
50 BEGIN
51 /* get the module id from preference */
52 select wp.module_id
53 into l_module_id
54 from wip_preference_definitions wp
55 where wp.preference_id = p_pref_id;
56
57 /* get the level code */
58 /* 0 - site, 1 - role, 2 - org, 3 - dept */
59 l_level_code := 0;
60 /* Check the case where org_id is not null but role_id is null
61 that means role_id is not used in that app */
62 if( p_resp_key is not null or p_org_id is not null) then
63 l_level_code := 1;
64 if( p_org_id is not null) then
65 l_level_code := 2;
66 if( p_dept_id is not null) then
67 l_level_code := 3;
68 end if;
69 end if;
70 end if;
71
72 /* find out the level_id */
73 select level_id
74 into l_level_id
75 from
76 (
77 select level_id
78 from wip_preference_levels v
79 where
80 v.module_id = l_module_id and
81 v.level_code <= l_level_code and
82 ( v.resp_key is null or v.resp_key = p_resp_key) and
83 ( v.organization_id is null or v.organization_id = p_org_id) and
84 ( v.department_id is null or v.department_id = p_dept_id) and
85 exists
86 (
87 select 1
88 from wip_preference_values v1
89 where v1.preference_id = p_pref_id and
90 v1.level_id = v.level_id
91 )
92 order by v.level_code desc
93 )
94 where rownum = 1;
95
96 return l_level_id;
97
98 exception when others then
99 return null;
100 END;
101
102
103 function get_preference_value_code(p_pref_id number, p_level_id number) return varchar2
104 is
105 l_result VARCHAR2(30) := null;
106 begin
107
108 BEGIN
109 select wpv.attribute_value_code
110 into l_result
111 from wip_preference_values wpv
112 where wpv.preference_id = p_pref_id and
113 wpv.level_id = p_level_id and
114 rownum = 1;
115 Exception
116 When others then
117 null;
118 END;
119
120 return(l_result);
121 end get_preference_value_code;
122
123
124 function get_jobop_name(p_job_name varchar2, p_op_seq number) return varchar2
125 is
126 begin
127 fnd_message.SET_NAME('WIP', 'WIP_WS_JOBOP_FORMAT');
128 fnd_message.SET_TOKEN('JOB', p_job_name);
129 fnd_message.SET_TOKEN('OP', p_op_seq);
130 return fnd_message.GET;
131
132 end get_jobop_name;
133
134
135 procedure retrieve_first_shift
136 (
137 p_org_id number,
138 p_dept_id number,
139 p_resource_id number,
140 p_date date,
141 x_shift_seq out nocopy number,
142 x_shift_num out nocopy number,
143 x_shift_start_date out nocopy date,
144 x_shift_end_date out nocopy date,
145 x_shift_string out nocopy varchar2
146 )
147 Is
148 l_cal_code varchar2(10);
149
150 l_c_start_date date;
151 l_c_end_date date;
152 l_c_from_time varchar2(60);
153 l_c_to_time varchar2(60);
154 l_24hr_resource number;
155
156 l_in_date date;
157 Begin
158
159 /* if the date is null, use sysdate */
160 if( p_date is null ) then
161 l_in_date := sysdate;
162 else
163 l_in_date := p_date;
164 end if;
165
166 l_cal_code := get_calendar_code(p_org_id);
167
168
169 if( p_resource_id is not null ) then /* test if it has shifts or not */
170 select bdr.available_24_hours_flag
171 into l_24hr_resource
172 from bom_department_resources bdr
173 where bdr.resource_id = p_resource_id and
174 bdr.department_id = p_dept_id;
175 else
176 l_24hr_resource := 2;
177 end if;
178
179 if( l_24hr_resource = 2 ) then
180 wip_ws_dl_util.get_first_shift(l_cal_code, p_dept_id, p_resource_id, l_in_date,
181 x_shift_seq, x_shift_num, x_shift_start_date, x_shift_end_date);
182 else
183 wip_ws_dl_util.get_first_calendar_date(l_cal_code, l_in_date,
184 x_shift_seq, x_shift_start_date, x_shift_end_date);
185 x_shift_num := null;
186 end if;
187
188 /* seem we have re-read the timezone profile to be synch with oa */
189 init_timezone;
190
191 if ( g_timezone_enabled ) then
192 l_c_start_date := hz_timezone_pub.convert_datetime(
193 g_server_id,
194 g_client_id,
195 x_shift_start_date
196 );
197 l_c_end_date := hz_timezone_pub.convert_datetime(
198 g_server_id,
199 g_client_id,
200 x_shift_end_date
201 );
202 else
203 l_c_start_date := x_shift_start_date;
204 l_c_end_date := x_shift_end_date;
205 end if;
206
207 select to_char(l_c_start_date, 'HH24:MI:SS')
208 into l_c_from_time
209 from dual;
210
211 select to_char(l_c_end_date, 'HH24:MI:SS')
212 into l_c_to_time
213 from dual;
214
215 fnd_message.SET_NAME('WIP', 'WIP_WS_SHIFT_INFO');
216 fnd_message.SET_TOKEN('SHIFT_NUM', x_shift_num);
217 fnd_message.SET_TOKEN('FROM_TIME', l_c_from_time);
218 fnd_message.SET_TOKEN('TO_TIME', l_c_to_time);
219
220 x_shift_string := fnd_message.GET;
221
222 --exception when others then
223 -- null;
224 End retrieve_first_shift;
225
226 function get_component_avail(p_org_id number, p_component_id number)
227 return number
228 Is
229 l_is_revision_control boolean;
230 l_is_lot_control boolean;
231 l_is_serial_control boolean;
232 l_lot_control_code number;
233 l_revision_control_code number;
234 l_serial_control_code number;
235
236 x_qoh number;
237 x_rqoh number;
238 x_qr number;
239 x_qs number;
240 x_att number;
241 x_atr number;
242
243 x_return_status varchar2(2);
244 x_msg_count number;
245 x_msg_data varchar2(256);
246 begin
247 select msi.revision_qty_control_code,
248 msi.lot_control_code,
249 msi.serial_number_control_code
250 into l_revision_control_code, l_lot_control_code, l_serial_control_code
251 from mtl_system_items_b msi
252 where msi.organization_id = p_org_id
253 and msi.inventory_item_id = p_component_id;
254
255 if ( l_lot_control_code = WIP_CONSTANTS.LOT ) then
256 l_is_lot_control := true;
257 else
258 l_is_lot_control := false;
259 end if;
260
261 if( l_revision_control_code = WIP_CONSTANTS.REV ) then
262 l_is_revision_control := true;
263 else
264 l_is_revision_control := false;
265 end if;
266
267 if( l_serial_control_code in (WIP_CONSTANTS.FULL_SN, WIP_CONSTANTS.DYN_RCV_SN) ) then
268 l_is_serial_control := true;
269 else
270 l_is_serial_control := false;
271 end if;
272
273 fnd_msg_pub.Delete_Msg;
274
275 -- Call the procedure
276 inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
277 p_init_msg_lst => 'T',
278 x_return_status => x_return_status,
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data,
281 p_organization_id => p_org_id,
282 p_inventory_item_id => p_component_id,
283 p_tree_mode => 2,
284 p_is_revision_control => l_is_revision_control,
285 p_is_lot_control => l_is_lot_control,
286 p_is_serial_control => l_is_serial_control,
287 p_lot_expiration_date => sysdate,
288 p_revision => null,
289 p_lot_number => null,
290 p_subinventory_code => null,
291 p_locator_id => null,
292 p_onhand_source => 3,
293 x_qoh => x_qoh,
294 x_rqoh => x_rqoh,
295 x_qr => x_qr,
296 x_qs => x_qs,
297 x_att => x_att,
298 x_atr => x_atr
299 );
300
301 /* call to clear the in memory cache */
302 inv_quantity_tree_pub.clear_quantity_cache;
303
304 return x_att;
305 end get_component_avail;
306
307 function get_employee_name(p_employee_id number, p_date date)
308 return varchar2
309 Is
310 l_name varchar2(256);
311 l_date date;
312 Begin
313 l_date := nvl(p_date, sysdate);
314
315 Begin
316 select papf.full_name
317 into l_name
318 from per_all_people_f papf
319 where papf.person_id = p_employee_id and
320 papf.effective_start_date <= l_date and
321 papf.effective_end_date > l_date;
322 Exception when others then
323 l_name := '';
324 End;
325
326 if( l_name is null ) then
327 Begin
328 select papf.full_name
329 into l_name
330 from per_all_people_f papf
331 where papf.person_id = p_employee_id
332 and rownum = 1;
333 Exception when others then
334 l_name := null;
335 End;
336 End if;
337
338 return l_name;
339 end get_employee_name;
340
341
342 function get_employee_id(p_employee_number varchar2,
343 p_org_id number)
344 return number
345 Is
346 l_emp_id number := null;
347 l_count number := 0;
348
349 -- Bug 14595358, Contingent Worker Project
350 CURSOR emp_valid_cursor IS
351 SELECT count(distinct BRE.PERSON_ID) employee_count,
352 min(BRE.PERSON_ID) employee_id
353 FROM PER_PEOPLE_F P,
354 PER_ASSIGNMENTS_F A,
355 PER_PERSON_TYPES T,
356 HR_ORGANIZATION_UNITS ORG,
357 BOM_RESOURCE_EMPLOYEES BRE
358 WHERE A.PERSON_ID = P.PERSON_ID AND
359 ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
360 A.PRIMARY_FLAG = 'Y' AND
361 A.ASSIGNMENT_TYPE = 'E' AND
362 P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
363 P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
364 TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
365 NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
366 TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
367 NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
368 P.EMPLOYEE_NUMBER IS NOT NULL AND
369 P.EMPLOYEE_NUMBER = p_employee_number AND
370 BRE.PERSON_ID = P.PERSON_ID AND
371 BRE.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND
372 ORG.ORGANIZATION_ID = p_org_id;
373
374 -- Bug 14595358, Contingent Worker Project
375 CURSOR npw_valid_cursor IS
376 SELECT count(distinct BRE.PERSON_ID) employee_count,
377 min(BRE.PERSON_ID) employee_id
378 FROM PER_PEOPLE_F P,
379 PER_ASSIGNMENTS_F A,
380 PER_PERSON_TYPES T,
381 HR_ORGANIZATION_UNITS ORG,
382 BOM_RESOURCE_EMPLOYEES BRE
383 WHERE A.PERSON_ID = P.PERSON_ID AND
384 ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
385 A.PRIMARY_FLAG = 'Y' AND
386 A.ASSIGNMENT_TYPE = 'C' AND
387 P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
388 P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
389 TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
390 NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
391 TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
392 NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
393 P.NPW_NUMBER IS NOT NULL AND
394 P.NPW_NUMBER = p_employee_number AND
395 BRE.PERSON_ID = P.PERSON_ID AND
396 BRE.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND
397 ORG.ORGANIZATION_ID = p_org_id;
398 Begin
399
400 OPEN emp_valid_cursor;
401 FETCH emp_valid_cursor INTO l_count,l_emp_id;
402 --if couldn't find the employee number, most likely it will be contingent worker
403 if(l_count = 0) then
404 OPEN npw_valid_cursor;
405 FETCH npw_valid_cursor INTO l_count,l_emp_id;
406 close npw_valid_cursor;
407 end if;
408 close emp_valid_cursor;
409
410 if( l_count > 1 ) then
411 l_emp_id := -1;
412 end if;
413
414 return l_emp_id;
415
416 Exception when others then
417 return null;
418 End get_employee_id;
419
420 function get_first_workday(p_org_id number, p_dept_id number, p_date date)
421 return date
422 Is
423 l_in_date date;
424
425 x_shift_seq number;
426 x_shift_num number;
427 x_shift_start_date date;
428 x_shift_end_date date;
429
430 Begin
431
432 /* if the date is null, use sysdate */
433 l_in_date := nvl(p_date, sysdate);
434
435 wip_ws_dl_util.get_first_shift(get_calendar_code(p_org_id), p_dept_id, null, l_in_date,
436 x_shift_seq, x_shift_num, x_shift_start_date, x_shift_end_date);
437
438 return trunc(x_shift_start_date);
439
440 End get_first_workday;
441
442 function get_appended_date(p_date date, p_time number)
443 return date
444 Is
445 Begin
446 return (p_date + p_time/(24*60*60));
447 end get_appended_date;
448
449 function get_next_date(p_date date)
450 return date
451 Is
452 Begin
453 return (p_date + 1);
454 end get_next_date;
455
456 function get_next_work_date_by_calcode(p_calendar_code varchar2, p_date date) return date
457 is
458 l_next_working_date date;
459 begin
460 if (p_date is null) then
461 return null;
462 end if;
463
464 select min(bsd.shift_date)
465 into l_next_working_date
466 from bom_shift_dates bsd
467 where bsd.calendar_code = p_calendar_code
468 and bsd.shift_date > trunc(p_date,'ddd')
469 and bsd.seq_num is not null;
470
471 return l_next_working_date;
472 end get_next_work_date_by_calcode;
473
474 function get_next_work_date_by_org_id(p_org_id number, p_date date) return date
475 is
476 begin
477 return get_next_work_date_by_calcode(get_calendar_code(p_org_id),p_date);
478 end get_next_work_date_by_org_id;
479
480 function get_calendar_code(p_org_id number) return varchar2
481 is
482 l_calendar_code varchar2(10);
483 begin
484 select calendar_code
485 into l_calendar_code
486 from mtl_parameters
487 where organization_id = p_org_id;
488
489 return l_calendar_code;
490 end get_calendar_code;
491
492 function get_shift_info_for_display(p_org_id number, p_shift_seq number, p_shift_num number)
493 return varchar2
494 Is
495 l_info varchar(256);
496 l_start_date date;
497 l_end_date date;
498
499 l_from_date date;
500 l_to_date date;
501 Begin
502
503 if( p_shift_seq is null ) then
504 l_info := null;
505 elsif ( p_shift_num is not null ) then
506 select bsd.shift_date + at.from_time /(24*60*60),
507 bsd.shift_date + at.to_time/(24*60*60)
508 into l_start_date, l_end_date
509 from bom_shift_dates bsd,
510 (select min(bst.from_time) from_time,
511 max(bst.to_time + decode(sign(to_time - from_time), -1, 24*60*60, 0)) to_time,
512 mp.calendar_code
513 from bom_shift_times bst, mtl_parameters mp
514 where bst.calendar_code = mp.calendar_code and
515 mp.organization_id = p_org_id and
516 bst.shift_num = p_shift_num
517 group by mp.calendar_code
518 ) at
519 where bsd.calendar_code = at.calendar_code and
520 bsd.exception_set_id = -1 and
521 bsd.seq_num = p_shift_seq and
522 bsd.shift_num = p_shift_num;
523
524 if ( g_timezone_enabled ) then
525 l_from_date := hz_timezone_pub.convert_datetime(
526 g_server_id,
527 g_client_id,
528 l_start_date
529 );
530 l_to_date := hz_timezone_pub.convert_datetime(
531 g_server_id,
532 g_client_id,
533 l_end_date
534 );
535 else
536 l_from_date := l_start_date;
537 l_to_date := l_end_date;
538 end if;
539 fnd_message.SET_NAME('WIP', 'WIP_WS_HOME_SHIFT_INFO_F');
540 fnd_message.SET_TOKEN('NUM', p_shift_num);
541 fnd_message.SET_TOKEN('FROM', fnd_date.date_to_displayDT(dateval => l_from_date,calendar_aware => 2) );
542 fnd_message.SET_TOKEN('TO', fnd_date.date_to_displayDT(dateval => l_to_date,calendar_aware => 2) );
543 l_info := fnd_message.GET;
544 else
545 l_info := null;
546 end if;
547
548
549 return l_info;
550 End get_shift_info_for_display;
551
552 function get_job_note_header(p_wip_entity_id number, p_op_seq number, p_employee_id number)
553 return varchar2
554 Is
555 l_header varchar2(1024);
556 l_emp_name varchar2(256);
557 l_dept_code varchar2(30) := null;
558 l_date date;
559 l_date_str varchar2(256);
560 Begin
561
562 l_emp_name := wip_ws_util.get_employee_name(p_employee_id, null);
563
564 /* seem we have re-read the timezone profile to be synch with oa */
565 init_timezone;
566
567 if ( g_timezone_enabled ) then
568 l_date := hz_timezone_pub.convert_datetime(
569 g_server_id,
570 g_client_id,
571 sysdate);
572 else
573 l_date := sysdate;
574 end if;
575
576 l_date_str := fnd_date.date_to_displayDT(dateval => l_date,calendar_aware => 2);
577
578 if( p_op_seq is not null ) then
579 select bd.department_code
580 into l_dept_code
581 from wip_operations wo, bom_departments bd
582 where wo.department_id = bd.department_id
583 and wo.wip_entity_id = p_wip_entity_id
584 and wo.operation_seq_num = p_op_seq;
585
586 fnd_message.set_name('WIP', 'WIP_WS_JOB_NOTE_HDR');
587 fnd_message.SET_token('EMP', l_emp_name);
588 fnd_message.SET_TOKEN('DATE', l_date_str);
589 fnd_message.set_token('OP', p_op_seq);
590 fnd_message.set_token('DEPT', l_dept_code);
591 fnd_message.set_token('TZ', fnd_timezones.get_client_timezone_code);
592 l_header := fnd_message.GET;
593 else
594 fnd_message.set_name('WIP', 'WIP_WS_JOB_NOTE_HDR_JOB');
595 fnd_message.SET_token('EMP', l_emp_name);
596 fnd_message.SET_TOKEN('DATE', l_date_str);
597 fnd_message.set_token('TZ', fnd_timezones.get_client_timezone_code);
598 l_header := fnd_message.GET;
599
600 end if;
601
602 return l_header;
603
604 Exception when others then
605 return null;
606 end get_job_note_header;
607
608 procedure clear_msg_stack
609 Is
610 Begin
611 fnd_msg_pub.Initialize;
612
613 End clear_msg_stack;
614
615 function get_current_resp_key return varchar2
616 Is
617 l_resp_key varchar2(256);
618 Begin
619 select fr.responsibility_key
620 into l_resp_key
621 from fnd_responsibility fr
622 where fr.responsibility_id = fnd_global.RESP_ID
623 and fr.application_id = fnd_global.RESP_APPL_ID
624 and rownum = 1;
625
626 return l_resp_key;
627 Exception when others then
628 return null;
629 end get_current_resp_key;
630
631 procedure append_job_note(p_wip_entity_id number, p_msg varchar2,
632 p_init_msg_list IN VARCHAR2,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2)
636 Is
637 Begin
638
639 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
640 THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645 update wip_discrete_jobs wdj
646 set wdj.job_note = wdj.job_note || p_msg
647 where wdj.wip_entity_id = p_wip_entity_id;
648
649 exception when others then
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651
652 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
653 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_job_note');
654 END IF;
655
656 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
657 end append_job_note;
658
659 procedure append_job_note(p_wip_entity_id number, p_clob_msg clob,
660 p_init_msg_list IN VARCHAR2,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2)
664 Is
665 job_note clob;
666 Begin
667
668 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
669 THEN
670 FND_MSG_PUB.initialize;
671 END IF;
672 x_return_status := FND_API.G_RET_STS_SUCCESS;
673
674 select wdj.job_note
675 into job_note
676 from wip_discrete_jobs wdj
677 where wdj.wip_entity_id = p_wip_entity_id
678 for update;
679
680 dbms_lob.append(job_note, p_clob_msg);
681
682 commit;
683
684 exception when others then
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686
687 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
688 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_job_note');
689 END IF;
690
691 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
692 end append_job_note;
693
694 procedure append_exception_note(p_exception_id number, p_msg varchar2,
695 p_init_msg_list IN VARCHAR2,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2)
699 Is
700 l_note CLOB;
701
702 Begin
703
704 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
705 THEN
706 FND_MSG_PUB.initialize;
707 END IF;
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709
710 select note into l_note from wip_exceptions
711 where exception_id = p_exception_id;
712
713 if(l_note is null OR l_note ='') then
714 l_note := p_msg;
715 else
716 l_note := l_note ||'<br><br>'||p_msg;
717 end if;
718
719 update wip_exceptions we
720 set we.note = l_note
721 where we.exception_id = p_exception_id;
722
723 exception when others then
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725
726 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
727 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_exception_note');
728 END IF;
729
730 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
731 end append_exception_note;
732
733
734 function get_resource_remaining_usage
735 (
736 p_organization_id number,
737 p_wip_entity_id number,
738 p_op_seq_num number,
739 p_department_id number,
740 p_resource_seq_num number,
741 p_resource_id number
742 ) return number
743 Is
744 l_remaining_usage number;
745 l_req_usage number;
746
747 l_used_usage number;
748 Begin
749
750 l_remaining_usage := null;
751
752 if( p_resource_seq_num is not null ) then
753 l_req_usage := wip_ws_dl_util.get_col_res_usage_req(
754 p_wip_entity_id, p_op_seq_num, p_department_id, p_resource_id, p_resource_seq_num);
755
756 l_remaining_usage := nvl(l_req_usage, 0) - 0;
757 end if;
758
759 return l_remaining_usage;
760 End get_resource_remaining_usage;
761
762 function project_resource_end_date
763 (
764 p_organization_id number,
765 p_department_id number,
766 p_resource_id number,
767 p_start_date date,
768 p_duration_hrs number
769 ) return date
770 Is
771 cursor c_shift_periods(p_cal_code varchar2, p_date date, p_dept_id number, p_resource_id number)
772 is
773 select bsd.shift_date, bst.from_time, bst.to_time
774 from bom_shift_dates bsd, bom_resource_shifts brs,
775 bom_shift_times bst, mtl_parameters mp
776 where mp.organization_id = p_organization_id and
777 bsd.calendar_code = p_cal_code and
778 bsd.exception_set_id = mp.calendar_exception_set_id and
779 bsd.shift_date >= trunc(p_date) - 1 and
780 bsd.shift_date + bst.to_time/(24*60*60) +
781 decode(sign(bst.to_time - bst.from_time), -1, 1, 0) > p_date and
782 brs.department_id = p_dept_id and
783 brs.resource_id = p_resource_id and
784 brs.shift_num = bsd.shift_num and
785 bst.shift_num = brs.shift_num and
786 bst.calendar_code = bsd.calendar_code and
787 bsd.seq_num is not null
788 order by bsd.shift_date + bst.from_time/(24*60*60);
789
790 l_end_date date;
791
792 l_remaining_duration number;
793 l_new_start_date date;
794
795 l_24hr_resource number;
796
797 l_shift_date date;
798 l_from_time number;
799 l_to_time number;
800
801 l_period_start_date date;
802 l_period_end_date date;
803 Begin
804
805 if( p_duration_hrs <= 0 ) then
806 l_end_date := p_start_date;
807 else
808 l_remaining_duration := p_duration_hrs / 24;
809 l_new_start_date := p_start_date;
810
811 select bdr.available_24_hours_flag
812 into l_24hr_resource
813 from bom_department_resources bdr
814 where bdr.department_id = p_department_id
815 and bdr.resource_id = p_resource_id;
816
817 if( l_24hr_resource = 1 /*yes*/ ) then
818
819 l_new_start_date := p_start_date + l_remaining_duration;
820
821 else
822
823
824 open c_shift_periods(get_calendar_code(p_organization_id), p_start_date, p_department_id, p_resource_id);
825
826
827 loop
828 fetch c_shift_periods
829 into l_shift_date, l_from_time, l_to_time;
830
831 exit when c_shift_periods%NOTFOUND;
832
833 -- dbms_output.put_line('start=' || to_char(l_new_start_date, 'YYYY/MM/DD HH24:MI:SS') || ' usage=' || l_remaining_duration * 24);
834 -- dbms_output.put_line('shift=' || l_shift_date || ' from=' || l_from_time/(60*60) || ' to=' || l_to_time/(60*60));
835
836 l_period_start_date := l_shift_date + l_from_time/(24*60*60);
837 if( l_to_time < l_from_time) then
838 l_period_end_date := l_shift_date + l_to_time/(24*60*60) + 1;
839 else
840 l_period_end_date := l_shift_date + l_to_time/(24*60*60);
841 end if;
842
843 if( l_period_end_date > l_new_start_date ) then
844
845 if( l_new_start_date < l_period_start_date ) then
846 l_new_start_date := l_period_start_date;
847 end if;
848
849 if( l_period_end_date - l_new_start_date >= l_remaining_duration ) then
850 l_new_start_date := l_new_start_date + l_remaining_duration;
851 l_remaining_duration := 0;
852 else
853 l_remaining_duration := l_remaining_duration - (l_period_end_date - l_new_start_date);
854 l_new_start_date := l_period_end_date;
855 end if;
856
857 end if; /* l_period_end_date > l_new_start_date */
858
859 if( l_remaining_duration <= 0 ) then
860 exit;
861 end if;
862
863 end loop;
864 l_end_date := l_new_start_date;
865
866 end if; /* end if not 24 hr resource */
867 end if; /* end if p_duration_hrs < 0 */
868
869 return l_end_date;
870 End project_resource_end_date;
871
872
873 function project_op_res_end_date
874 (
875 p_organization_id number,
876 p_wip_entity_id number,
877 p_op_seq_num number,
878 p_resource_seq_num number,
879 p_resource_id number,
880 p_start_date date
881 ) return date
882 Is
883 l_utilization number;
884 l_efficiency number;
885 l_units number;
886 l_actual_units number;
887 l_department_id number;
888
889 l_remaining_usage number;
890 l_duration number;
891 l_completion_date date;
892 Begin
893
894 /* if it's ad-hoc resource, no usage is defined so no completion date can be projected */
895 if( p_resource_seq_num is null ) then
896 l_completion_date := null;
897 else
898 select nvl(wor.department_id, wo.department_id)
899 into l_department_id
900 from wip_operations wo, wip_operation_resources wor
901 where wo.wip_entity_id = p_wip_entity_id
902 and wo.organization_id = p_organization_id
903 and wo.operation_seq_num = p_op_seq_num
904 and wor.wip_entity_id = wo.wip_entity_id
905 and wor.organization_id = wo.organization_id
906 and wor.operation_seq_num = wo.operation_seq_num
907 and wor.resource_seq_num = p_resource_seq_num;
908
909 l_remaining_usage := get_resource_remaining_usage(p_organization_id,
910 p_wip_entity_id, p_op_seq_num, l_department_id, p_resource_seq_num, p_resource_id);
911
912 -- yl_debug.dump('req=' || l_remaining_usage);
913 if( l_remaining_usage is not null ) then
914
915 select least(wor.assigned_units, bdr.capacity_units),
916 decode(wp.include_resource_utilization,
917 wip_constants.yes, nvl(bdr.utilization, 1), 1),
918 decode(wp.include_resource_efficiency,
919 wip_constants.yes, nvl(bdr.efficiency, 1), 1)
920 into l_units, l_utilization, l_efficiency
921 from wip_operation_resources wor,
922 bom_department_resources bdr,
923 wip_parameters wp
924 where wor.organization_id = p_organization_id
925 and wor.wip_entity_id = p_wip_entity_id
926 and wor.operation_seq_num = p_op_seq_num
927 and wor.resource_seq_num = p_resource_seq_num
928 and bdr.resource_id = wor.resource_id
929 and bdr.department_id = l_department_id
930 and wp.organization_id = p_organization_id;
931
932 -- dbms_output.put_line('units=' || l_units);
933
934 select count(distinct wrat.instance_id)
935 into l_actual_units
936 from wip_resource_actual_times wrat
937 where wrat.organization_id = p_organization_id
938 and wrat.wip_entity_id = p_wip_entity_id
939 and wrat.operation_seq_num = p_op_seq_num
940 and wrat.resource_seq_num = p_resource_seq_num
941 and wrat.status_type = 1
942 and wrat.end_date is null;
943
944 if( l_actual_units > l_units) then
945 l_units := l_actual_units;
946 end if;
947
948 -- dbms_output.put_line('units=' || l_units);
949 -- dbms_output.put_line('dept=' || l_department_id);
950
951 l_duration := l_remaining_usage / (l_units * l_utilization * l_efficiency);
952
953 l_completion_date := project_resource_end_date(
954 p_organization_id, l_department_id, p_resource_id,
955 p_start_date, l_duration);
956
957 end if;
958 end if;
959
960 return l_completion_date;
961 end project_op_res_end_date;
962
963
964 function get_emp_projected_end_date
965 (
966 p_organization_id number,
967 p_wip_entity_id number,
968 p_op_seq_num number,
969 p_resource_seq_num number,
970 p_resource_id number,
971 p_instance_id number,
972 p_start_date date
973 ) return date
974 Is
975
976 Begin
977
978 return project_op_res_end_date(
979 p_organization_id, p_wip_entity_id, p_op_seq_num,
980 p_resource_seq_num, p_resource_id, p_start_date);
981
982 End get_emp_projected_end_date;
983
984 function get_res_projected_end_date
985 (
986 p_organization_id number,
987 p_wip_entity_id number,
988 p_op_seq_num number,
989 p_resource_seq_num number,
990 p_resource_id number,
991 p_start_date date
992 ) return date
993 Is
994 l_resource_type number;
995 l_is_time_uom varchar2(10);
996
997 l_completion_date date;
998 Begin
999
1000 select br.resource_type, wip_ws_time_entry.is_time_uom(br.unit_of_measure)
1001 into l_resource_type, l_is_time_uom
1002 from bom_resources br
1003 where br.organization_id = p_organization_id
1004 and br.resource_id = p_resource_id;
1005
1006 if( l_is_time_uom = 'Y') then
1007
1008
1009 if( l_resource_type = 1 ) then /* machine */
1010
1011 l_completion_date := project_op_res_end_date(
1012 p_organization_id, p_wip_entity_id,
1013 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
1014
1015
1016 elsif (l_resource_type = 2 ) then /* labor */
1017 l_completion_date := project_op_res_end_date(
1018 p_organization_id, p_wip_entity_id,
1019 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
1020
1021 else
1022 l_completion_date := project_op_res_end_date(
1023 p_organization_id, p_wip_entity_id,
1024 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
1025
1026 end if;
1027 else
1028 l_completion_date := null;
1029 end if; /* end if is_time_uom */
1030
1031 return l_completion_date;
1032 end get_res_projected_end_date;
1033
1034 function get_op_projected_end_date
1035 (
1036 p_organization_id number,
1037 p_wip_entity_id number,
1038 p_op_seq_num number
1039 ) return date
1040 Is
1041 l_completion_date date;
1042 l_not_projected_count number;
1043 Begin
1044
1045 l_completion_date := null;
1046
1047 select count(wor.resource_seq_num)
1048 into l_not_projected_count
1049 from wip_operation_resources wor
1050 where wor.wip_entity_id = p_wip_entity_id
1051 and wor.operation_seq_num = p_op_seq_num
1052 and wor.organization_id = p_organization_id
1053 and wor.projected_completion_date is null
1054 and wor.scheduled_flag in (1, 3, 4);
1055
1056 if( l_not_projected_count = 0 ) then
1057
1058 select max(wor.projected_completion_date)
1059 into l_completion_date
1060 from wip_operation_resources wor
1061 where wor.wip_entity_id = p_wip_entity_id
1062 and wor.operation_seq_num = p_op_seq_num
1063 and wor.organization_id = p_organization_id
1064 and wor.projected_completion_date is not null
1065 and wor.scheduled_flag in (1, 3, 4);
1066 end if;
1067
1068 return l_completion_date;
1069
1070 end get_op_projected_end_date;
1071
1072 function get_projected_completion_date
1073 (
1074 p_organization_id number,
1075 p_wip_entity_id number,
1076 p_op_seq_num number,
1077 p_resource_seq_num number,
1078 p_resource_id number,
1079 p_instance_id number,
1080 p_start_date date
1081 ) return date
1082 Is
1083
1084 l_completion_date date;
1085 Begin
1086
1087 if( p_start_date is null ) then
1088 l_completion_date := null;
1089
1090 elsif( p_instance_id is not null) then
1091 l_completion_date := get_emp_projected_end_date(
1092 p_organization_id, p_wip_entity_id, p_op_seq_num, p_resource_seq_num, p_resource_id,
1093 p_instance_id, p_start_date);
1094
1095 elsif ( p_resource_seq_num is not null or p_resource_id is not null) then
1096 l_completion_date := get_res_projected_end_date(
1097 p_organization_id, p_wip_entity_id, p_op_seq_num, p_resource_seq_num, p_resource_id,
1098 p_start_date);
1099
1100 elsif ( p_op_seq_num is not null ) then
1101 l_completion_date := get_op_projected_end_date(
1102 p_organization_id, p_wip_entity_id, p_op_seq_num);
1103
1104 else
1105 l_completion_date := null;
1106
1107 end if;
1108
1109 return l_completion_date;
1110
1111 End get_projected_completion_date;
1112
1113 procedure set_legal_entity_ctx(
1114 p_org_id number
1115 )
1116 Is
1117 l_le_id number;
1118 Begin
1119 select to_number(ORG_INFORMATION2) into l_le_id
1120 from hr_organization_information
1121 where organization_id = p_org_id
1122 and org_information_context = 'Accounting Information';
1123
1124 GL_GLOBAL.set_aff_validation('LE', l_le_id);
1125
1126 exception when others then
1127 null;
1128 End set_legal_entity_ctx;
1129
1130 function get_instance_name(p_resource_id IN NUMBER,
1131 p_instance_id IN NUMBER,
1132 p_serial_number IN VARCHAR2) return VARCHAR2
1133 IS
1134 l_resource_type number;
1135 l_emp_id number;
1136 l_emp_name varchar2(255);
1137 l_equipment_prefix varchar2(255);
1138 l_equipment_name varchar2(255);
1139 l_instance_name varchar2(255);
1140
1141 cursor resource_type_cursor is
1142 select resource_type
1143 from bom_resources br
1144 where resource_id = p_resource_id;
1145
1146 cursor emp_id_cursor is
1147 select person_id
1148 from bom_resource_employees bremp
1149 where instance_id = p_instance_id;
1150
1151 cursor equipment_cursor is
1152 select msik.concatenated_segments
1153 from bom_resource_equipments breq,
1154 mtl_system_items_kfv msik
1155 where breq.instance_id = p_instance_id
1156 and msik.inventory_item_id = breq.inventory_item_id
1157 and msik.organization_id = breq.organization_id;
1158 BEGIN
1159 open resource_type_cursor;
1160 fetch resource_type_cursor into l_resource_type;
1161 close resource_type_cursor;
1162
1163 if l_resource_type = 2 then --person resource
1164 open emp_id_cursor;
1165 fetch emp_id_cursor into l_emp_id;
1166 close emp_id_cursor;
1167
1168 l_emp_name := get_employee_name(p_employee_id => l_emp_id,
1169 p_date => null);
1170 return l_emp_name;
1171 elsif l_resource_type = 1 then --machine resource
1172 open equipment_cursor;
1173 fetch equipment_cursor into l_equipment_prefix;
1174 close equipment_cursor;
1175
1176 l_equipment_name := get_instance_name(p_instance_name => l_equipment_prefix,
1177 p_serial_number => p_serial_number);
1178 return l_equipment_name;
1179 else --other resource
1180 return null;
1181 end if;
1182 END get_instance_name;
1183
1184 procedure init_timezone
1185 IS
1186 Begin
1187
1188 /* reset the timezone profiles */
1189 g_timezone_enabled := (fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' AND
1190 fnd_profile.value('CLIENT_TIMEZONE_ID') IS NOT NULL AND
1191 fnd_profile.value('SERVER_TIMEZONE_ID') IS NOT NULL AND
1192 fnd_profile.value('CLIENT_TIMEZONE_ID') <>
1193 fnd_profile.value('SERVER_TIMEZONE_ID'));
1194
1195
1196 g_client_id := fnd_profile.value('CLIENT_TIMEZONE_ID');
1197 g_server_id := fnd_profile.value('SERVER_TIMEZONE_ID');
1198
1199 Exception
1200 when others then
1201 null;
1202 End init_timezone;
1203
1204 function get_page_title(p_oahp varchar2, p_oasf varchar2)
1205 return varchar2
1206 IS
1207 l_home_menu_id number;
1208 l_function_id number;
1209 l_f_menu_id number;
1210 l_name varchar2(255);
1211 Begin
1212 l_name := '';
1213
1214 select user_function_name
1215 into l_name
1216 from fnd_form_functions_vl t
1217 where t.function_name = p_oasf;
1218
1219 return l_name;
1220 Exception when others then
1221 return null;
1222 End get_page_title;
1223
1224
1225 function get_multival_pref_seq(p_pref_id IN NUMBER,
1226 p_level_id IN NUMBER,
1227 p_attribute_name IN VARCHAR2,
1228 p_attribute_val IN VARCHAR2) return NUMBER IS
1229 CURSOR pref_row_seq_csr IS
1230 select wpv.sequence_number
1231 from wip_preference_values wpv
1232 where wpv.preference_id = p_pref_id
1233 and wpv.level_id = p_level_id
1234 and wpv.attribute_name = p_attribute_name
1235 and wpv.attribute_value_code = p_attribute_val;
1236 l_seq_num NUMBER;
1237 BEGIN
1238 for c_pref_row_seq_csr in pref_row_seq_csr loop
1239 l_seq_num := c_pref_row_seq_csr.sequence_number;
1240 end loop;
1241 return l_seq_num;
1242 END get_multival_pref_seq;
1243
1244
1245 function get_multival_pref_val_code(p_pref_id IN NUMBER,
1246 p_level_id IN NUMBER,
1247 p_seq_num IN NUMBER,
1248 p_attribute_name IN VARCHAR2) return VARCHAR2 IS
1249 CURSOR att_val_csr IS
1250 select wpv.attribute_value_code
1251 from wip_preference_values wpv
1252 where wpv.preference_id = p_pref_id
1253 and wpv.level_id = p_level_id
1254 and wpv.sequence_number = p_seq_num
1255 and wpv.attribute_name = p_attribute_name;
1256 l_att_val VARCHAR2(80);
1257 BEGIN
1258 for c_att_val_csr in att_val_csr loop
1259 l_att_val := c_att_val_csr.attribute_value_code;
1260 end loop;
1261 return l_att_val;
1262 END get_multival_pref_val_code;
1263
1264
1265 procedure log_time(p_msg IN VARCHAR2, p_date IN DATE DEFAULT SYSDATE) IS
1266 l_returnStatus varchar2(1);
1267 BEGIN
1268
1269 if (g_logLevel <= wip_constants.trace_logging) then
1270 wip_logger.log((to_char(p_date,'hh:mi:ss') || '-' || p_msg),l_returnStatus);
1271 end if;
1272 END log_time;
1273
1274
1275 FUNCTION get_lock_handle (
1276 p_org_id IN NUMBER,
1277 p_lock_prefix IN Varchar2) RETURN VARCHAR2 IS
1278
1279 PRAGMA AUTONOMOUS_TRANSACTION;
1280 l_lock_handle VARCHAR2(128);
1281 l_lock_name VARCHAR2(30);
1282 BEGIN
1283 l_lock_name := p_lock_prefix || p_org_id;
1284 trace_log('get_lock_handle: lock_name='||l_lock_name);
1285 dbms_lock.allocate_unique(
1286 lockname => l_lock_name
1287 ,lockhandle => l_lock_handle);
1288 trace_log('get_lock_handle: lock_handle='||l_lock_handle);
1289 return l_lock_handle;
1290 END get_lock_handle;
1291
1292
1293 PROCEDURE get_lock(
1294 x_return_status OUT nocopy varchar2,
1295 x_msg_count OUT nocopy number,
1296 x_msg_data OUT nocopy varchar2,
1297 x_lock_status OUT nocopy number,
1298 p_org_id IN NUMBER,
1299 p_lock_prefix IN Varchar2) IS
1300 l_lock_handle varchar2(128);
1301 l_returnStatus varchar2(1);
1302 BEGIN
1303 trace_log('get_lock: Entering for org_id='||p_org_id);
1304 l_lock_handle := get_lock_handle (p_org_id => p_org_id,
1305 p_lock_prefix => p_lock_prefix);
1306 -- request lock with release_on_commit TRUE so that we dont have to manually
1307 -- release the lock later.
1308 x_lock_status := dbms_lock.request(
1309 lockhandle => l_lock_handle,
1310 lockmode => dbms_lock.x_mode,
1311 timeout => dbms_lock.maxwait,
1312 release_on_commit => TRUE);
1313 trace_log('get_lock: got lock for lock handle with status ='||x_lock_status);
1314 trace_log('get_lock: Returning from lock_for_match');
1315 EXCEPTION
1316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1317 trace_log('get_lock: Exception: Unexpected Error '||sqlerrm);
1318 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1319
1320 WHEN OTHERS then
1321 trace_log('get_lock: Exception: Others Exception : ' || sqlerrm);
1322 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1323
1324 END get_lock;
1325
1326
1327 PROCEDURE release_lock(
1328 x_return_status OUT NOCOPY VARCHAR2,
1329 x_msg_count OUT NOCOPY NUMBER,
1330 x_msg_data OUT NOCOPY VARCHAR2,
1331 p_org_id IN NUMBER,
1332 p_lock_prefix IN varchar2) IS
1333 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1334 l_lock_handle VARCHAR2(128);
1335 l_status INTEGER;
1336 l_returnStatus varchar2(1);
1337 BEGIN
1338 trace_log('release_lock: Entering release_lock for org_id'||p_org_id);
1339 --get lock handle by calling helper function
1340 l_lock_handle := get_lock_handle(p_org_id => p_org_id,
1341 p_lock_prefix => p_lock_prefix);
1342 trace_log('release_lock: lock_handle='||l_lock_handle);
1343
1344 l_status := dbms_lock.release(l_lock_handle);
1345 trace_log('release_lock: release returned with status:'||l_status);
1346
1347 --if success (status = 0) or session does not own lock (status=4),
1348 -- do nothing
1349 --if parameter error or illegal lock handle (internal error)
1350
1351 if l_status IN (3,5) THEN
1352 trace_log('release_lock: Error releasing lock');
1353 RAISE fnd_api.g_exc_error;
1354 end if;
1355
1356 x_return_status := l_return_status;
1357
1358
1359 EXCEPTION
1360
1361 WHEN fnd_api.g_exc_error THEN
1362 trace_log('release_lock: Exception: expected error');
1363 x_return_status := fnd_api.g_ret_sts_error;
1364
1365 WHEN fnd_api.g_exc_unexpected_error THEN
1366 trace_log('release_lock: Exception: Unexpected error');
1367 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1368
1369 WHEN OTHERS THEN
1370 trace_log('release_lock: Exception: Others Exception: '||sqlerrm);
1371 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1372
1373 END release_lock;
1374
1375 PROCEDURE trace_log(p_msg IN VARCHAR2) IS
1376 l_returnStatus varchar2(1);
1377 BEGIN
1378 fnd_file.put_line(fnd_file.log,p_msg);
1379 if (g_logLevel <= wip_constants.trace_logging) then
1380 wip_logger.log(p_msg,l_returnStatus);
1381 end if;
1382 END trace_log;
1383
1384
1385 --check shortages in work orders tab supervisor dashboard
1386 -- 1 is shortage
1387 -- 2 is no shortage
1388 -- 3 is not applicable
1389 FUNCTION check_comp_shortage(p_wip_entity_id IN NUMBER,
1390 p_org_id IN NUMBER) RETURN NUMBER IS
1391 l_comp_shortage NUMBER := 3;
1392 l_row_seq_num NUMBER;
1393 l_shortage_calc_level NUMBER;
1394 BEGIN
1395 --Bug 6889755 - Return component shortage depending on the calculation level setup
1396 l_row_seq_num := wip_ws_util.get_multival_pref_seq(
1397 WIP_WS_SHORTAGE.g_pref_id_comp_short, WIP_WS_SHORTAGE.g_pref_level_id_site, WIP_WS_SHORTAGE.g_pref_val_mast_org_att, to_char(p_org_id));
1398
1399 l_shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
1400 WIP_WS_SHORTAGE.g_pref_id_comp_short, WIP_WS_SHORTAGE.g_pref_level_id_site, l_row_seq_num, WIP_WS_SHORTAGE.g_pref_val_calclevel_att);
1401 --End Bug 6889755
1402
1403 --Bug 6889755 - added IF condition and the ELSE block for subinv level check
1404 /* Bug 9221212 , remove rownum =1 as we should consider the shortage_qty
1405 for all components not only the first component*/
1406 IF (l_shortage_calc_level = ORG_CALCULATION_LEVEL) THEN
1407 select decode(nvl(sum(shortage_qty),-1),-1,3,0,2,1)
1408 into l_comp_shortage
1409 from wip_ws_comp_shortage
1410 where wip_entity_id = p_wip_entity_id
1411 and organization_id = p_org_id
1412 and SUPPLY_SUBINVENOTRY is null;
1413
1414 ELSIF (l_shortage_calc_level = SUBINV_CALCULATION_LEVEL) THEN
1415 /* Bug 9221212 , remove rownum =1 as we should consider the shortage_qty
1416 for all components not only the first component*/
1417 select decode(nvl(sum(shortage_qty),-1),-1,3,0,2,1)
1418 into l_comp_shortage
1419 from wip_ws_comp_shortage
1420 where wip_entity_id = p_wip_entity_id
1421 and organization_id = p_org_id
1422 and SUPPLY_SUBINVENOTRY is not null;
1423
1424 END IF;
1425
1426 --l_comp_shortage :=1;
1427 return l_comp_shortage;
1428
1429
1430 Exception
1431 when no_data_found then
1432 --for organization not setup for comp shortage calculation
1433 l_comp_shortage := 3;
1434 return l_comp_shortage;
1435 When others then
1436 l_comp_shortage := 3;
1437 -- l_comp_shortage :=1;
1438 return l_comp_shortage;
1439 END check_comp_shortage;
1440
1441 FUNCTION check_res_shortage(p_wip_entity_id IN NUMBER,
1442 p_org_id IN NUMBER) RETURN NUMBER IS
1443 l_res_shortage NUMBER := 3;
1444 BEGIN
1445
1446 /* Bug 9221212 , remove rownum =1 as we should consider the resource_shortage
1447 for all resources not only the first resource*/
1448 select decode(nvl(sum(RESOURCE_SHORTAGE),-1),-1,3,0,2,1)
1449 into l_res_shortage
1450 from wip_ws_res_shortage
1451 where wip_entity_id = p_wip_entity_id
1452 and organization_id = p_org_id;
1453
1454 --l_res_shortage :=1;
1455 return l_res_shortage;
1456 Exception
1457 when no_data_found then
1458 --for organization not setup for res shortage calculation
1459 l_res_shortage := 3;
1460 return l_res_shortage;
1461
1462 When others then
1463 l_res_shortage := 3;
1464 -- l_res_shortage :=1;
1465 return l_res_shortage;
1466 END check_res_shortage;
1467
1468 --new function for bugfix 6755623
1469 function get_csh_calc_level(p_org_id Number) return NUMBER IS
1470 l_calc_level NUMBER;
1471 l_row_seq_num NUMBER;
1472 BEGIN
1473 --get the sequence number
1474 l_row_seq_num := wip_ws_util.get_multival_pref_seq(
1475 g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_mast_org_att, to_char(p_org_id));
1476
1477 if(l_row_seq_num is null) then
1478 return 1; --return org level by default inorder to avoid error
1479 end if;
1480
1481 l_calc_level := wip_ws_util.get_multival_pref_val_code(
1482 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
1483
1484 return l_calc_level;
1485
1486 END get_csh_calc_level;
1487
1488 FUNCTION get_no_of_running_concurrent(
1489 p_program_application_id in number,
1490 p_concurrent_program_id in number,
1491 p_org_id in number) RETURN number
1492 IS
1493 l_pgm_count number;
1494 BEGIN
1495 wip_ws_util.trace_log('Program application id '|| p_program_application_id);
1496 wip_ws_util.trace_log('Concurrent program id '|| p_concurrent_program_id);
1497 wip_ws_util.trace_log('Oraganization id '|| p_org_id);
1498 SELECT count(1)
1499 INTO l_pgm_count
1500 FROM fnd_concurrent_requests
1501 WHERE program_application_id = p_program_application_id
1502 AND concurrent_program_id = p_concurrent_program_id
1503 AND upper(phase_code) = 'R'
1504 AND argument1 = to_char(p_org_id);
1505 wip_ws_util.trace_log('Running concurrent program '|| l_pgm_count);
1506 return l_pgm_count;
1507 END get_no_of_running_concurrent;
1508
1509
1510 PROCEDURE log_for_duplicate_concurrent(
1511 p_org_id in number,
1512 p_program_name in varchar2)
1513 IS
1514 l_org_code varchar2(3);
1515 BEGIN
1516
1517 wip_ws_util.trace_log('Unable to run '|| p_program_name ||' calculation program for organization ' || get_calendar_code(p_org_id));
1518 wip_ws_util.trace_log('Another instance may be running. Please try again after some time.');
1519
1520 END log_for_duplicate_concurrent;
1521
1522 -- Bug 10364497.
1523 function check_attachment_exists(p_org_id in number,
1524 p_job_id in number,
1525 p_op_seq in number,
1526 p_dep_id in number)
1527 return number
1528 is
1529 l_attach_count number := 0;
1530 l_level_id number;
1531
1532 cursor pref_attach_source(p_level_id number) is
1533 select sequence_number,attribute_value_code
1534 from wip_preference_values
1535 where preference_id = g_pref_id_attachment
1536 and level_id = p_level_id;
1537 begin
1538 l_level_id := get_preference_level_id(g_pref_id_attachment,
1539 get_current_resp_key(),
1540 p_org_id,
1541 p_dep_id);
1542 for pref_attach in pref_attach_source(l_level_id) loop
1543 if pref_attach.attribute_value_code = g_att_src_discrete_job then
1544 begin
1545 select 1 into l_attach_count
1546 from dual
1547 where exists (select 1
1548 from FND_ATTACHED_DOCUMENTS
1549 where entity_name = 'WIP_DISCRETE_JOBS'
1550 and pk1_value = p_job_id
1551 and pk2_value = p_org_id
1552 and rownum = 1);
1553 exit;
1554 exception
1555 when others then
1556 l_attach_count := 0;
1557 end;
1558 elsif pref_attach.attribute_value_code = g_att_src_job_operation then
1559 begin
1560 select 1 into l_attach_count
1561 from dual
1562 where exists (select 1
1563 from FND_ATTACHED_DOCUMENTS
1564 where entity_name = 'WIP_DISCRETE_OPERATIONS'
1565 and pk1_value = p_job_id
1566 and pk2_value = p_op_seq
1567 and pk3_value = p_org_id
1568 and rownum = 1);
1569 exit;
1570 exception
1571 when others then
1572 l_attach_count := 0;
1573 end;
1574 elsif pref_attach.attribute_value_code = g_att_src_assembly_item then
1575 begin
1576 select 1 into l_attach_count
1577 from dual
1578 where exists (select 1
1579 from FND_ATTACHED_DOCUMENTS fad,
1580 wip_entities we
1581 where fad.entity_name = 'MTL_SYSTEM_ITEMS'
1582 and fad.pk1_value = p_org_id
1583 and fad.pk2_value = we.primary_item_id
1584 and we.wip_entity_id= p_job_id
1585 and fad.pk1_value = we.organization_id
1586 and rownum = 1);
1587 exit;
1588 exception
1589 when others then
1590 l_attach_count := 0;
1591 end;
1592 elsif pref_attach.attribute_value_code = g_att_src_bill_header then
1593 begin
1594 select 1 into l_attach_count
1595 from dual
1596 where exists (select 1
1597 from FND_ATTACHED_DOCUMENTS fad,
1598 wip_discrete_jobs wdj
1599 where fad.entity_name = 'BOM_BILL_OF_MATERIALS'
1600 and fad.pk1_value = wdj.common_bom_sequence_id
1601 and wdj.wip_entity_id = p_job_id
1602 and wdj.organization_id = p_org_id
1603 and rownum = 1);
1604 exit;
1605 exception
1606 when others then
1607 l_attach_count := 0;
1608 end;
1609 elsif pref_attach.attribute_value_code = g_att_src_rtg_header then
1610 begin
1611 select 1 into l_attach_count
1612 from dual
1613 where exists (select 1
1614 from FND_ATTACHED_DOCUMENTS fad,
1615 wip_discrete_jobs wdj
1616 where fad.entity_name = 'BOM_OPERATIONAL_ROUTINGS'
1617 and fad.pk1_value = wdj.common_routing_sequence_id
1618 and wdj.wip_entity_id = p_job_id
1619 and wdj.organization_id = p_org_id
1620 and rownum = 1);
1621 exit;
1622 exception
1623 when others then
1624 l_attach_count := 0;
1625 end;
1626 elsif pref_attach.attribute_value_code = g_att_src_bill_comps then
1627 begin
1628 select 1 into l_attach_count
1629 from dual
1630 where exists (select 1
1631 from FND_ATTACHED_DOCUMENTS fad
1632 where fad.entity_name = 'MTL_SYSTEM_ITEMS'
1633 and pk1_value = p_org_id
1634 and pk2_value IN ( select inventory_item_id
1635 from wip_requirement_operations
1636 where wip_entity_id = p_job_id
1637 and operation_seq_num = p_op_seq
1638 and organization_id = p_org_id)
1639 and rownum = 1);
1640 exit;
1641 exception
1642 when others then
1643 l_attach_count := 0;
1644 end;
1645 elsif pref_attach.attribute_value_code = g_att_src_so_header then
1646 begin
1647 select 1 into l_attach_count
1648 from dual
1649 where exists (select 1
1650 from FND_ATTACHED_DOCUMENTS fad
1651 where fad.entity_name = 'OE_ORDER_HEADERS'
1652 and pk1_value IN ( select header_id
1653 from oe_order_lines_all
1654 where line_id in ( select mr.demand_source_line_id
1655 from mtl_reservations mr
1656 where mr.demand_source_type_id in (2,8)
1657 and mr.supply_source_type_id = 5
1658 and mr.supply_source_header_id = p_job_id
1659 and mr.organization_id = p_org_id ))
1660 and rownum = 1);
1661
1662 exit;
1663 exception
1664 when others then
1665 l_attach_count := 0;
1666 end;
1667 elsif pref_attach.attribute_value_code = g_att_src_so_line then
1668 begin
1669 select 1 into l_attach_count
1670 from dual
1671 where exists (select 1
1672 from FND_ATTACHED_DOCUMENTS fad
1673 where fad.entity_name = 'OE_ORDER_LINES'
1674 and pk1_value IN ( select mr.demand_source_line_id
1675 from mtl_reservations mr
1676 where mr.demand_source_type_id in (2,8)
1677 and mr.supply_source_type_id = 5
1678 and mr.supply_source_header_id = p_job_id
1679 and mr.organization_id = p_org_id )
1680 and rownum = 1);
1681
1682 exit;
1683 exception
1684 when others then
1685 l_attach_count := 0;
1686 end;
1687 else
1688 l_attach_count := 0;
1689 end if;
1690 end loop;
1691 return l_attach_count;
1692 exception
1693 when others then
1694 return 0;
1695 end check_attachment_exists;
1696
1697 /*ER 12961989, Arrow Enhancement*/
1698 function get_shift_info(
1699 p_emp_id in number,
1700 p_org_id in number
1701 ) return varchar2
1702 is
1703 l_result VARCHAR2(100) := null;
1704 l_shift_date DATE;
1705 l_c_shift_date DATE;
1706 begin
1707
1708 begin
1709 select wrat.start_date
1710 into l_shift_date
1711 from wip_resource_actual_times wrat
1712 where wrat.organization_id = p_org_id
1713 and wrat.employee_id = p_emp_id
1714 and wrat.start_date is not null
1715 and wrat.end_date is null
1716 and wrat.time_entry_mode = 8;
1717 exception
1718 when no_data_found then
1719 return null;
1720 end;
1721
1722 if(l_shift_date is not null) then
1723 if ( g_timezone_enabled ) then
1724 l_c_shift_date := hz_timezone_pub.convert_datetime(
1725 g_server_id,
1726 g_client_id,
1727 l_shift_date
1728 );
1729 else
1730 l_c_shift_date := l_shift_date;
1731 end if;
1732
1733 select to_char(l_shift_date, WIP_CONSTANTS.DATETIME_FMT)
1734 into l_result
1735 from dual;
1736 else
1737 l_result := null;
1738 end if;
1739
1740 return l_result;
1741
1742 exception when others then
1743 return null;
1744 end get_shift_info;
1745
1746 /*ER 12961989, Arrow Enhancement*/
1747 function get_clock_info(
1748 p_emp_id in number,
1749 p_org_id in number
1750 ) return varchar2
1751 is
1752 l_result VARCHAR2(255) := null;
1753 l_clock_desc VARCHAR2(100) := null;
1754 l_job_desc VARCHAR2(100) := null;
1755 l_wip_entity_id NUMBER;
1756 l_op_seq NUMBER;
1757 l_clock_date DATE;
1758 l_c_clock_date DATE;
1759 l_status VARCHAR2(1);
1760 l_job_name VARCHAR2(80) := null;
1761 begin
1762
1763 WIP_WS_TIME_ENTRY.get_cur_clocked_job(p_emp_id => p_emp_id,
1764 p_org_id => p_org_id,
1765 x_entity_id => l_wip_entity_id,
1766 x_op_seq => l_op_seq,
1767 x_start_date => l_clock_date,
1768 x_ret_status => l_status);
1769 --return 'M' if there are multiple clocked in records
1770 if(l_status = 'M') then
1771 l_result := 'M';
1772 --if only one clocked in record set l_clock_date
1773 elsif (l_wip_entity_id is not null) then
1774
1775 if ( g_timezone_enabled ) then
1776 l_c_clock_date := hz_timezone_pub.convert_datetime(
1777 g_server_id,
1778 g_client_id,
1779 l_clock_date
1780 );
1781 else
1782 l_c_clock_date := l_clock_date;
1783 end if;
1784
1785 select we.wip_entity_name
1786 into l_job_name
1787 from wip_entities we
1788 where we.wip_entity_id = l_wip_entity_id
1789 and we.organization_id = p_org_id;
1790
1791 select to_char(l_c_clock_date, WIP_CONSTANTS.DATETIME_FMT)
1792 into l_clock_desc
1793 from dual;
1794
1795 select wip_ws_util.get_jobop_name(l_job_name, l_op_seq)
1796 into l_job_desc
1797 from dual;
1798
1799 l_result := l_job_desc || ', ' || l_clock_desc;
1800 --if no clocked in record return null
1801 else
1802 l_result := null;
1803 end if;
1804
1805 return l_result;
1806 exception when others then
1807 return null;
1808 end get_clock_info;
1809
1810 function get_assy_serial_issue_qty(p_org_id in number,
1811 p_job_id in number,
1812 p_op_seq in number,
1813 p_assy_item_id in number,
1814 p_assy_serial in varchar2,
1815 p_comp_item_id in number)
1816 return number
1817 is
1818 l_lot_control Number;
1819 l_serial_control Number;
1820 l_serial_tagged Number;
1821 l_control_code Number := 1; /* 1- NC , 2 - LC , 3 - SC/ST */
1822 Begin
1823 select lot_control_code,serial_number_control_code
1824 into l_lot_control,l_serial_control
1825 from mtl_system_items_b
1826 where inventory_item_id = p_comp_item_id
1827 and organization_id = p_org_id;
1828
1829 if l_lot_control = 2 then
1830 l_control_code := 2;
1831 elsif l_serial_control in (2,5) then
1832 l_control_code := 3;
1833 else
1834 if inv_cache.get_serial_tagged(p_org_id,p_comp_item_id,WIP_CONSTANTS.ISSCOMP_TYPE) = WIP_CONSTANTS.SER_TAGGED then
1835 l_control_code := 3;
1836 else
1837 l_control_code := 1;
1838 end if;
1839 end if;
1840
1841 return get_assy_serial_issue_qty(p_org_id,
1842 p_job_id,
1843 p_op_seq,
1844 p_assy_item_id,
1845 p_assy_serial,
1846 p_comp_item_id,
1847 l_control_code);
1848 Exception
1849 when others then
1850 return null;
1851 End get_assy_serial_issue_qty;
1852
1853 function get_assy_serial_issue_qty(p_org_id in number,
1854 p_job_id in number,
1855 p_op_seq in number,
1856 p_assy_item_id in number,
1857 p_assy_serial in varchar2,
1858 p_comp_item_id in number,
1859 p_comp_code in number)
1860 return number
1861 is
1862 l_issue_qty Number := 0;
1863 Begin
1864 if p_comp_code = 1 then
1865 l_issue_qty := wip_ws_custom.get_assy_serial_issue_qty(p_org_id => p_org_id,
1866 p_job_id => p_job_id,
1867 p_op_seq => p_op_seq,
1868 p_assy_item_id => p_assy_item_id,
1869 p_assy_serial => p_assy_serial,
1870 p_comp_item_id => p_comp_item_id);
1871 elsif p_comp_code = 2 then
1872 select abs(nvl(sum(mtln.transaction_quantity),0))
1873 into l_issue_qty
1874 from MTL_TRANSACTION_LOT_NUMBERS mtln
1875 where mtln.transaction_source_type_id = 5
1876 and mtln.transaction_source_id = p_job_id
1877 and mtln.parent_object_type = 2
1878 and mtln.parent_object_number = p_assy_serial
1879 and mtln.parent_item_id = p_assy_item_id
1880 and mtln.organization_id= p_org_id
1881 and mtln.inventory_item_id = p_comp_item_id
1882 and exists (select 1 from mtl_material_transactions mmt
1883 where mmt.transaction_id = mtln.transaction_id
1884 and mmt.transaction_source_type_id = mtln.transaction_source_type_id
1885 and mmt.transaction_source_id = mtln.transaction_source_id
1886 and mmt.organization_id = mtln.organization_id
1887 and mmt.inventory_item_id = mtln.inventory_item_id
1888 and mmt.operation_seq_num = p_op_seq
1889 and mmt.transaction_action_id in (1,27,33,34));
1890 else
1891 select nvl(sum(decode(mut.receipt_issue_type,1,1,-1)),0)
1892 into l_issue_qty
1893 from
1894 mtl_unit_transactions mut
1895 where mut.transaction_source_type_id = 5
1896 and mut.transaction_source_id = p_job_id
1897 and mut.parent_object_type = 2
1898 and mut.parent_object_number = p_assy_serial
1899 and mut.parent_item_id = p_assy_item_id
1900 and mut.organization_id= p_org_id
1901 and mut.receipt_issue_type in (1,2)
1902 and mut.inventory_item_id = p_comp_item_id
1903 and exists (select 1 from mtl_material_transactions mmt
1904 where mmt.transaction_id = mut.transaction_id
1905 and mmt.transaction_source_type_id = mut.transaction_source_type_id
1906 and mmt.transaction_source_id = mut.transaction_source_id
1907 and mmt.organization_id = mut.organization_id
1908 and mmt.inventory_item_id = mut.inventory_item_id
1909 and mmt.operation_seq_num = p_op_seq
1910 and mmt.transaction_action_id in (1,27,33,34));
1911 end if;
1912 return l_issue_qty;
1913 End get_assy_serial_issue_qty;
1914
1915
1916 end wip_ws_util;
1917