[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_UTIL
Source
1 package body wip_ws_util as
2 /* $Header: wipwsutb.pls 120.25.12010000.4 2008/09/16 21:53:58 awongwai 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, p_org_id number)
343 return number
344 Is
345 l_emp_id number;
346 l_count number;
347 Begin
348
349 SELECT count(distinct p.person_id), min(p.PERSON_ID)
350 into l_count, l_emp_id
351 FROM PER_PEOPLE_F P,
352 PER_ASSIGNMENTS_F A,
353 PER_PERSON_TYPES T,
354 HR_ORGANIZATION_UNITS ORG
355 WHERE A.PERSON_ID = P.PERSON_ID AND
356 ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
357 A.PRIMARY_FLAG = 'Y' AND
358 A.ASSIGNMENT_TYPE = 'E' AND
359 P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
360 P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
361 TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
362 NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
363 TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
364 NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
365 P.EMPLOYEE_NUMBER IS NOT NULL AND
366 P.EMPLOYEE_NUMBER = p_employee_number AND
367 ORG.ORGANIZATION_ID = p_org_id;
368
369 if( l_count > 1 ) then
370 l_emp_id := -1;
371 end if;
372
373 return l_emp_id;
374
375 Exception when others then
376 return null;
377 End get_employee_id;
378
379 function get_first_workday(p_org_id number, p_dept_id number, p_date date)
380 return date
381 Is
382 l_in_date date;
383
384 x_shift_seq number;
385 x_shift_num number;
386 x_shift_start_date date;
387 x_shift_end_date date;
388
389 Begin
390
391 /* if the date is null, use sysdate */
392 l_in_date := nvl(p_date, sysdate);
393
394 wip_ws_dl_util.get_first_shift(get_calendar_code(p_org_id), p_dept_id, null, l_in_date,
395 x_shift_seq, x_shift_num, x_shift_start_date, x_shift_end_date);
396
397 return trunc(x_shift_start_date);
398
399 End get_first_workday;
400
401 function get_appended_date(p_date date, p_time number)
402 return date
403 Is
404 Begin
405 return (p_date + p_time/(24*60*60));
406 end get_appended_date;
407
408 function get_next_date(p_date date)
409 return date
410 Is
411 Begin
412 return (p_date + 1);
413 end get_next_date;
414
415 function get_next_work_date_by_calcode(p_calendar_code varchar2, p_date date) return date
416 is
417 l_next_working_date date;
418 begin
419 if (p_date is null) then
420 return null;
421 end if;
422
423 select min(bsd.shift_date)
424 into l_next_working_date
425 from bom_shift_dates bsd
426 where bsd.calendar_code = p_calendar_code
427 and bsd.shift_date > trunc(p_date,'ddd')
428 and bsd.seq_num is not null;
429
430 return l_next_working_date;
431 end get_next_work_date_by_calcode;
432
433 function get_next_work_date_by_org_id(p_org_id number, p_date date) return date
434 is
435 begin
436 return get_next_work_date_by_calcode(get_calendar_code(p_org_id),p_date);
437 end get_next_work_date_by_org_id;
438
439 function get_calendar_code(p_org_id number) return varchar2
440 is
441 l_calendar_code varchar2(10);
442 begin
443 select calendar_code
444 into l_calendar_code
445 from mtl_parameters
446 where organization_id = p_org_id;
447
448 return l_calendar_code;
449 end get_calendar_code;
450
451 function get_shift_info_for_display(p_org_id number, p_shift_seq number, p_shift_num number)
452 return varchar2
453 Is
454 l_info varchar(256);
455 l_start_date date;
456 l_end_date date;
457
458 l_from_date date;
459 l_to_date date;
460 Begin
461
462 if( p_shift_seq is null ) then
463 l_info := null;
464 elsif ( p_shift_num is not null ) then
465 select bsd.shift_date + at.from_time /(24*60*60),
466 bsd.shift_date + at.to_time/(24*60*60)
467 into l_start_date, l_end_date
468 from bom_shift_dates bsd,
469 (select min(bst.from_time) from_time,
470 max(bst.to_time + decode(sign(to_time - from_time), -1, 24*60*60, 0)) to_time,
471 mp.calendar_code
472 from bom_shift_times bst, mtl_parameters mp
473 where bst.calendar_code = mp.calendar_code and
474 mp.organization_id = p_org_id and
475 bst.shift_num = p_shift_num
476 group by mp.calendar_code
477 ) at
478 where bsd.calendar_code = at.calendar_code and
479 bsd.exception_set_id = -1 and
480 bsd.seq_num = p_shift_seq and
481 bsd.shift_num = p_shift_num;
482
483 if ( g_timezone_enabled ) then
484 l_from_date := hz_timezone_pub.convert_datetime(
485 g_server_id,
486 g_client_id,
487 l_start_date
488 );
489 l_to_date := hz_timezone_pub.convert_datetime(
490 g_server_id,
491 g_client_id,
492 l_end_date
493 );
494 else
495 l_from_date := l_start_date;
496 l_to_date := l_end_date;
497 end if;
498 fnd_message.SET_NAME('WIP', 'WIP_WS_HOME_SHIFT_INFO_F');
499 fnd_message.SET_TOKEN('NUM', p_shift_num);
500 fnd_message.SET_TOKEN('FROM', fnd_date.date_to_displayDT(l_from_date) );
501 fnd_message.SET_TOKEN('TO', fnd_date.date_to_displayDT(l_to_date) );
502 l_info := fnd_message.GET;
503 else
504 l_info := null;
505 end if;
506
507
508 return l_info;
509 End get_shift_info_for_display;
510
511 function get_job_note_header(p_wip_entity_id number, p_op_seq number, p_employee_id number)
512 return varchar2
513 Is
514 l_header varchar2(1024);
515 l_emp_name varchar2(256);
516 l_dept_code varchar2(30) := null;
517 l_date date;
518 l_date_str varchar2(256);
519 Begin
520
521 l_emp_name := wip_ws_util.get_employee_name(p_employee_id, null);
522
523 /* seem we have re-read the timezone profile to be synch with oa */
524 init_timezone;
525
526 if ( g_timezone_enabled ) then
527 l_date := hz_timezone_pub.convert_datetime(
528 g_server_id,
529 g_client_id,
530 sysdate);
531 else
532 l_date := sysdate;
533 end if;
534
535 l_date_str := fnd_date.date_to_displayDT(l_date);
536
537 if( p_op_seq is not null ) then
538 select bd.department_code
539 into l_dept_code
540 from wip_operations wo, bom_departments bd
541 where wo.department_id = bd.department_id
542 and wo.wip_entity_id = p_wip_entity_id
543 and wo.operation_seq_num = p_op_seq;
544
545 fnd_message.set_name('WIP', 'WIP_WS_JOB_NOTE_HDR');
546 fnd_message.SET_token('EMP', l_emp_name);
547 fnd_message.SET_TOKEN('DATE', l_date_str);
548 fnd_message.set_token('OP', p_op_seq);
549 fnd_message.set_token('DEPT', l_dept_code);
550 fnd_message.set_token('TZ', fnd_timezones.get_client_timezone_code);
551 l_header := fnd_message.GET;
552 else
553 fnd_message.set_name('WIP', 'WIP_WS_JOB_NOTE_HDR_JOB');
554 fnd_message.SET_token('EMP', l_emp_name);
555 fnd_message.SET_TOKEN('DATE', l_date_str);
556 fnd_message.set_token('TZ', fnd_timezones.get_client_timezone_code);
557 l_header := fnd_message.GET;
558
559 end if;
560
561 return l_header;
562
563 Exception when others then
564 return null;
565 end get_job_note_header;
566
567 procedure clear_msg_stack
568 Is
569 Begin
570 fnd_msg_pub.Initialize;
571
572 End clear_msg_stack;
573
574 function get_current_resp_key return varchar2
575 Is
576 l_resp_key varchar2(256);
577 Begin
578 select fr.responsibility_key
579 into l_resp_key
580 from fnd_responsibility fr
581 where fr.responsibility_id = fnd_global.RESP_ID
582 and fr.application_id = fnd_global.RESP_APPL_ID
583 and rownum = 1;
584
585 return l_resp_key;
586 Exception when others then
587 return null;
588 end get_current_resp_key;
589
590 procedure append_job_note(p_wip_entity_id number, p_msg varchar2,
591 p_init_msg_list IN VARCHAR2,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_msg_count OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2)
595 Is
596 Begin
597
598 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
599 THEN
600 FND_MSG_PUB.initialize;
601 END IF;
602 x_return_status := FND_API.G_RET_STS_SUCCESS;
603
604 update wip_discrete_jobs wdj
605 set wdj.job_note = wdj.job_note || p_msg
606 where wdj.wip_entity_id = p_wip_entity_id;
607
608 exception when others then
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
610
611 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
612 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_job_note');
613 END IF;
614
615 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
616 end append_job_note;
617
618 procedure append_job_note(p_wip_entity_id number, p_clob_msg clob,
619 p_init_msg_list IN VARCHAR2,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2)
623 Is
624 job_note clob;
625 Begin
626
627 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
628 THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633 select wdj.job_note
634 into job_note
635 from wip_discrete_jobs wdj
636 where wdj.wip_entity_id = p_wip_entity_id
637 for update;
638
639 dbms_lob.append(job_note, p_clob_msg);
640
641 commit;
642
643 exception when others then
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645
646 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
647 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_job_note');
648 END IF;
649
650 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
651 end append_job_note;
652
653 procedure append_exception_note(p_exception_id number, p_msg varchar2,
654 p_init_msg_list IN VARCHAR2,
655 x_return_status OUT NOCOPY VARCHAR2,
656 x_msg_count OUT NOCOPY NUMBER,
657 x_msg_data OUT NOCOPY VARCHAR2)
658 Is
659 l_note CLOB;
660
661 Begin
662
663 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
664 THEN
665 FND_MSG_PUB.initialize;
666 END IF;
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668
669 select note into l_note from wip_exceptions
670 where exception_id = p_exception_id;
671
672 if(l_note is null OR l_note ='') then
673 l_note := p_msg;
674 else
675 l_note := l_note ||'<br><br>'||p_msg;
676 end if;
677
678 update wip_exceptions we
679 set we.note = l_note
680 where we.exception_id = p_exception_id;
681
682 exception when others then
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684
685 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
686 FND_MSG_PUB.Add_Exc_Msg ('wip_ws_util' ,'append_exception_note');
687 END IF;
688
689 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
690 end append_exception_note;
691
692
693 function get_resource_remaining_usage
694 (
695 p_organization_id number,
696 p_wip_entity_id number,
697 p_op_seq_num number,
698 p_department_id number,
699 p_resource_seq_num number,
700 p_resource_id number
701 ) return number
702 Is
703 l_remaining_usage number;
704 l_req_usage number;
705
706 l_used_usage number;
707 Begin
708
709 l_remaining_usage := null;
710
711 if( p_resource_seq_num is not null ) then
712 l_req_usage := wip_ws_dl_util.get_col_res_usage_req(
713 p_wip_entity_id, p_op_seq_num, p_department_id, p_resource_id, p_resource_seq_num);
714
715 l_remaining_usage := nvl(l_req_usage, 0) - 0;
716 end if;
717
718 return l_remaining_usage;
719 End get_resource_remaining_usage;
720
721 function project_resource_end_date
722 (
723 p_organization_id number,
724 p_department_id number,
725 p_resource_id number,
726 p_start_date date,
727 p_duration_hrs number
728 ) return date
729 Is
730 cursor c_shift_periods(p_cal_code varchar2, p_date date, p_dept_id number, p_resource_id number)
731 is
732 select bsd.shift_date, bst.from_time, bst.to_time
733 from bom_shift_dates bsd, bom_resource_shifts brs,
734 bom_shift_times bst, mtl_parameters mp
735 where mp.organization_id = p_organization_id and
736 bsd.calendar_code = p_cal_code and
737 bsd.exception_set_id = mp.calendar_exception_set_id and
738 bsd.shift_date >= trunc(p_date) - 1 and
739 bsd.shift_date + bst.to_time/(24*60*60) +
740 decode(sign(bst.to_time - bst.from_time), -1, 1, 0) > p_date and
741 brs.department_id = p_dept_id and
742 brs.resource_id = p_resource_id and
743 brs.shift_num = bsd.shift_num and
744 bst.shift_num = brs.shift_num and
745 bst.calendar_code = bsd.calendar_code and
746 bsd.seq_num is not null
747 order by bsd.shift_date + bst.from_time/(24*60*60);
748
749 l_end_date date;
750
751 l_remaining_duration number;
752 l_new_start_date date;
753
754 l_24hr_resource number;
755
756 l_shift_date date;
757 l_from_time number;
758 l_to_time number;
759
760 l_period_start_date date;
761 l_period_end_date date;
762 Begin
763
764 if( p_duration_hrs <= 0 ) then
765 l_end_date := p_start_date;
766 else
767 l_remaining_duration := p_duration_hrs / 24;
768 l_new_start_date := p_start_date;
769
770 select bdr.available_24_hours_flag
771 into l_24hr_resource
772 from bom_department_resources bdr
773 where bdr.department_id = p_department_id
774 and bdr.resource_id = p_resource_id;
775
776 if( l_24hr_resource = 1 /*yes*/ ) then
777
778 l_new_start_date := p_start_date + l_remaining_duration;
779
780 else
781
782
783 open c_shift_periods(get_calendar_code(p_organization_id), p_start_date, p_department_id, p_resource_id);
784
785
786 loop
787 fetch c_shift_periods
788 into l_shift_date, l_from_time, l_to_time;
789
790 exit when c_shift_periods%NOTFOUND;
791
792 -- dbms_output.put_line('start=' || to_char(l_new_start_date, 'YYYY/MM/DD HH24:MI:SS') || ' usage=' || l_remaining_duration * 24);
793 -- dbms_output.put_line('shift=' || l_shift_date || ' from=' || l_from_time/(60*60) || ' to=' || l_to_time/(60*60));
794
795 l_period_start_date := l_shift_date + l_from_time/(24*60*60);
796 if( l_to_time < l_from_time) then
797 l_period_end_date := l_shift_date + l_to_time/(24*60*60) + 1;
798 else
799 l_period_end_date := l_shift_date + l_to_time/(24*60*60);
800 end if;
801
802 if( l_period_end_date > l_new_start_date ) then
803
804 if( l_new_start_date < l_period_start_date ) then
805 l_new_start_date := l_period_start_date;
806 end if;
807
808 if( l_period_end_date - l_new_start_date >= l_remaining_duration ) then
809 l_new_start_date := l_new_start_date + l_remaining_duration;
810 l_remaining_duration := 0;
811 else
812 l_remaining_duration := l_remaining_duration - (l_period_end_date - l_new_start_date);
813 l_new_start_date := l_period_end_date;
814 end if;
815
816 end if; /* l_period_end_date > l_new_start_date */
817
818 if( l_remaining_duration <= 0 ) then
819 exit;
820 end if;
821
822 end loop;
823 l_end_date := l_new_start_date;
824
825 end if; /* end if not 24 hr resource */
826 end if; /* end if p_duration_hrs < 0 */
827
828 return l_end_date;
829 End project_resource_end_date;
830
831
832 function project_op_res_end_date
833 (
834 p_organization_id number,
835 p_wip_entity_id number,
836 p_op_seq_num number,
837 p_resource_seq_num number,
838 p_resource_id number,
839 p_start_date date
840 ) return date
841 Is
842 l_utilization number;
843 l_efficiency number;
844 l_units number;
845 l_actual_units number;
846 l_department_id number;
847
848 l_remaining_usage number;
849 l_duration number;
850 l_completion_date date;
851 Begin
852
853 /* if it's ad-hoc resource, no usage is defined so no completion date can be projected */
854 if( p_resource_seq_num is null ) then
855 l_completion_date := null;
856 else
857 select nvl(wor.department_id, wo.department_id)
858 into l_department_id
859 from wip_operations wo, wip_operation_resources wor
860 where wo.wip_entity_id = p_wip_entity_id
861 and wo.organization_id = p_organization_id
862 and wo.operation_seq_num = p_op_seq_num
863 and wor.wip_entity_id = wo.wip_entity_id
864 and wor.organization_id = wo.organization_id
865 and wor.operation_seq_num = wo.operation_seq_num
866 and wor.resource_seq_num = p_resource_seq_num;
867
868 l_remaining_usage := get_resource_remaining_usage(p_organization_id,
869 p_wip_entity_id, p_op_seq_num, l_department_id, p_resource_seq_num, p_resource_id);
870
871 -- yl_debug.dump('req=' || l_remaining_usage);
872 if( l_remaining_usage is not null ) then
873
874 select least(wor.assigned_units, bdr.capacity_units),
875 decode(wp.include_resource_utilization,
876 wip_constants.yes, nvl(bdr.utilization, 1), 1),
877 decode(wp.include_resource_efficiency,
878 wip_constants.yes, nvl(bdr.efficiency, 1), 1)
879 into l_units, l_utilization, l_efficiency
880 from wip_operation_resources wor,
881 bom_department_resources bdr,
882 wip_parameters wp
883 where wor.organization_id = p_organization_id
884 and wor.wip_entity_id = p_wip_entity_id
885 and wor.operation_seq_num = p_op_seq_num
886 and wor.resource_seq_num = p_resource_seq_num
887 and bdr.resource_id = wor.resource_id
888 and bdr.department_id = l_department_id
889 and wp.organization_id = p_organization_id;
890
891 -- dbms_output.put_line('units=' || l_units);
892
893 select count(distinct wrat.instance_id)
894 into l_actual_units
895 from wip_resource_actual_times wrat
896 where wrat.organization_id = p_organization_id
897 and wrat.wip_entity_id = p_wip_entity_id
898 and wrat.operation_seq_num = p_op_seq_num
899 and wrat.resource_seq_num = p_resource_seq_num
900 and wrat.status_type = 1
901 and wrat.end_date is null;
902
903 if( l_actual_units > l_units) then
904 l_units := l_actual_units;
905 end if;
906
907 -- dbms_output.put_line('units=' || l_units);
908 -- dbms_output.put_line('dept=' || l_department_id);
909
910 l_duration := l_remaining_usage / (l_units * l_utilization * l_efficiency);
911
912 l_completion_date := project_resource_end_date(
913 p_organization_id, l_department_id, p_resource_id,
914 p_start_date, l_duration);
915
916 end if;
917 end if;
918
919 return l_completion_date;
920 end project_op_res_end_date;
921
922
923 function get_emp_projected_end_date
924 (
925 p_organization_id number,
926 p_wip_entity_id number,
927 p_op_seq_num number,
928 p_resource_seq_num number,
929 p_resource_id number,
930 p_instance_id number,
931 p_start_date date
932 ) return date
933 Is
934
935 Begin
936
937 return project_op_res_end_date(
938 p_organization_id, p_wip_entity_id, p_op_seq_num,
939 p_resource_seq_num, p_resource_id, p_start_date);
940
941 End get_emp_projected_end_date;
942
943 function get_res_projected_end_date
944 (
945 p_organization_id number,
946 p_wip_entity_id number,
947 p_op_seq_num number,
948 p_resource_seq_num number,
949 p_resource_id number,
950 p_start_date date
951 ) return date
952 Is
953 l_resource_type number;
954 l_is_time_uom varchar2(10);
955
956 l_completion_date date;
957 Begin
958
959 select br.resource_type, wip_ws_time_entry.is_time_uom(br.unit_of_measure)
960 into l_resource_type, l_is_time_uom
961 from bom_resources br
962 where br.organization_id = p_organization_id
963 and br.resource_id = p_resource_id;
964
965 if( l_is_time_uom = 'Y') then
966
967
968 if( l_resource_type = 1 ) then /* machine */
969
970 l_completion_date := project_op_res_end_date(
971 p_organization_id, p_wip_entity_id,
972 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
973
974
975 elsif (l_resource_type = 2 ) then /* labor */
976 l_completion_date := project_op_res_end_date(
977 p_organization_id, p_wip_entity_id,
978 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
979
980 else
981 l_completion_date := project_op_res_end_date(
982 p_organization_id, p_wip_entity_id,
983 p_op_seq_num, p_resource_seq_num, p_resource_id, p_start_date);
984
985 end if;
986 else
987 l_completion_date := null;
988 end if; /* end if is_time_uom */
989
990 return l_completion_date;
991 end get_res_projected_end_date;
992
993 function get_op_projected_end_date
994 (
995 p_organization_id number,
996 p_wip_entity_id number,
997 p_op_seq_num number
998 ) return date
999 Is
1000 l_completion_date date;
1001 l_not_projected_count number;
1002 Begin
1003
1004 l_completion_date := null;
1005
1006 select count(wor.resource_seq_num)
1007 into l_not_projected_count
1008 from wip_operation_resources wor
1009 where wor.wip_entity_id = p_wip_entity_id
1010 and wor.operation_seq_num = p_op_seq_num
1011 and wor.organization_id = p_organization_id
1012 and wor.projected_completion_date is null
1013 and wor.scheduled_flag in (1, 3, 4);
1014
1015 if( l_not_projected_count = 0 ) then
1016
1017 select max(wor.projected_completion_date)
1018 into l_completion_date
1019 from wip_operation_resources wor
1020 where wor.wip_entity_id = p_wip_entity_id
1021 and wor.operation_seq_num = p_op_seq_num
1022 and wor.organization_id = p_organization_id
1023 and wor.projected_completion_date is not null
1024 and wor.scheduled_flag in (1, 3, 4);
1025 end if;
1026
1027 return l_completion_date;
1028
1029 end get_op_projected_end_date;
1030
1031 function get_projected_completion_date
1032 (
1033 p_organization_id number,
1034 p_wip_entity_id number,
1035 p_op_seq_num number,
1036 p_resource_seq_num number,
1037 p_resource_id number,
1038 p_instance_id number,
1039 p_start_date date
1040 ) return date
1041 Is
1042
1043 l_completion_date date;
1044 Begin
1045
1046 if( p_start_date is null ) then
1047 l_completion_date := null;
1048
1049 elsif( p_instance_id is not null) then
1050 l_completion_date := get_emp_projected_end_date(
1051 p_organization_id, p_wip_entity_id, p_op_seq_num, p_resource_seq_num, p_resource_id,
1052 p_instance_id, p_start_date);
1053
1054 elsif ( p_resource_seq_num is not null or p_resource_id is not null) then
1055 l_completion_date := get_res_projected_end_date(
1056 p_organization_id, p_wip_entity_id, p_op_seq_num, p_resource_seq_num, p_resource_id,
1057 p_start_date);
1058
1059 elsif ( p_op_seq_num is not null ) then
1060 l_completion_date := get_op_projected_end_date(
1061 p_organization_id, p_wip_entity_id, p_op_seq_num);
1062
1063 else
1064 l_completion_date := null;
1065
1066 end if;
1067
1068 return l_completion_date;
1069
1070 End get_projected_completion_date;
1071
1072 procedure set_legal_entity_ctx(
1073 p_org_id number
1074 )
1075 Is
1076 l_le_id number;
1077 Begin
1078 select to_number(ORG_INFORMATION2) into l_le_id
1079 from hr_organization_information
1080 where organization_id = p_org_id
1081 and org_information_context = 'Accounting Information';
1082
1083 GL_GLOBAL.set_aff_validation('LE', l_le_id);
1084
1085 exception when others then
1086 null;
1087 End set_legal_entity_ctx;
1088
1089 function get_instance_name(p_resource_id IN NUMBER,
1090 p_instance_id IN NUMBER,
1091 p_serial_number IN VARCHAR2) return VARCHAR2
1092 IS
1093 l_resource_type number;
1094 l_emp_id number;
1095 l_emp_name varchar2(255);
1096 l_equipment_prefix varchar2(255);
1097 l_equipment_name varchar2(255);
1098 l_instance_name varchar2(255);
1099
1100 cursor resource_type_cursor is
1101 select resource_type
1102 from bom_resources br
1103 where resource_id = p_resource_id;
1104
1105 cursor emp_id_cursor is
1106 select person_id
1107 from bom_resource_employees bremp
1108 where instance_id = p_instance_id;
1109
1110 cursor equipment_cursor is
1111 select msik.concatenated_segments
1112 from bom_resource_equipments breq,
1113 mtl_system_items_kfv msik
1114 where breq.instance_id = p_instance_id
1115 and msik.inventory_item_id = breq.inventory_item_id
1116 and msik.organization_id = breq.organization_id;
1117 BEGIN
1118 open resource_type_cursor;
1119 fetch resource_type_cursor into l_resource_type;
1120 close resource_type_cursor;
1121
1122 if l_resource_type = 2 then --person resource
1123 open emp_id_cursor;
1124 fetch emp_id_cursor into l_emp_id;
1125 close emp_id_cursor;
1126
1127 l_emp_name := get_employee_name(p_employee_id => l_emp_id,
1128 p_date => null);
1129 return l_emp_name;
1130 elsif l_resource_type = 1 then --machine resource
1131 open equipment_cursor;
1132 fetch equipment_cursor into l_equipment_prefix;
1133 close equipment_cursor;
1134
1135 l_equipment_name := get_instance_name(p_instance_name => l_equipment_prefix,
1136 p_serial_number => p_serial_number);
1137 return l_equipment_name;
1138 else --other resource
1139 return null;
1140 end if;
1141 END get_instance_name;
1142
1143 procedure init_timezone
1144 IS
1145 Begin
1146
1147 /* reset the timezone profiles */
1148 g_timezone_enabled := (fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' AND
1149 fnd_profile.value('CLIENT_TIMEZONE_ID') IS NOT NULL AND
1150 fnd_profile.value('SERVER_TIMEZONE_ID') IS NOT NULL AND
1151 fnd_profile.value('CLIENT_TIMEZONE_ID') <>
1152 fnd_profile.value('SERVER_TIMEZONE_ID'));
1153
1154
1155 g_client_id := fnd_profile.value('CLIENT_TIMEZONE_ID');
1156 g_server_id := fnd_profile.value('SERVER_TIMEZONE_ID');
1157
1158 Exception
1159 when others then
1160 null;
1161 End init_timezone;
1162
1163 function get_page_title(p_oahp varchar2, p_oasf varchar2)
1164 return varchar2
1165 IS
1166 l_home_menu_id number;
1167 l_function_id number;
1168 l_f_menu_id number;
1169 l_name varchar2(255);
1170 Begin
1171 l_name := '';
1172
1173 select user_function_name
1174 into l_name
1175 from fnd_form_functions_vl t
1176 where t.function_name = p_oasf;
1177
1178 return l_name;
1179 Exception when others then
1180 return null;
1181 End get_page_title;
1182
1183
1184 function get_multival_pref_seq(p_pref_id IN NUMBER,
1185 p_level_id IN NUMBER,
1186 p_attribute_name IN VARCHAR2,
1187 p_attribute_val IN VARCHAR2) return NUMBER IS
1188 CURSOR pref_row_seq_csr IS
1189 select wpv.sequence_number
1190 from wip_preference_values wpv
1191 where wpv.preference_id = p_pref_id
1192 and wpv.level_id = p_level_id
1193 and wpv.attribute_name = p_attribute_name
1194 and wpv.attribute_value_code = p_attribute_val;
1195 l_seq_num NUMBER;
1196 BEGIN
1197 for c_pref_row_seq_csr in pref_row_seq_csr loop
1198 l_seq_num := c_pref_row_seq_csr.sequence_number;
1199 end loop;
1200 return l_seq_num;
1201 END get_multival_pref_seq;
1202
1203
1204 function get_multival_pref_val_code(p_pref_id IN NUMBER,
1205 p_level_id IN NUMBER,
1206 p_seq_num IN NUMBER,
1207 p_attribute_name IN VARCHAR2) return VARCHAR2 IS
1208 CURSOR att_val_csr IS
1209 select wpv.attribute_value_code
1210 from wip_preference_values wpv
1211 where wpv.preference_id = p_pref_id
1212 and wpv.level_id = p_level_id
1213 and wpv.sequence_number = p_seq_num
1214 and wpv.attribute_name = p_attribute_name;
1215 l_att_val VARCHAR2(80);
1216 BEGIN
1217 for c_att_val_csr in att_val_csr loop
1218 l_att_val := c_att_val_csr.attribute_value_code;
1219 end loop;
1220 return l_att_val;
1221 END get_multival_pref_val_code;
1222
1223
1224 procedure log_time(p_msg IN VARCHAR2, p_date IN DATE DEFAULT SYSDATE) IS
1225 l_returnStatus varchar2(1);
1226 BEGIN
1227
1228 if (g_logLevel <= wip_constants.trace_logging) then
1229 wip_logger.log((to_char(p_date,'hh:mi:ss') || '-' || p_msg),l_returnStatus);
1230 end if;
1231 END log_time;
1232
1233
1234 FUNCTION get_lock_handle (
1235 p_org_id IN NUMBER,
1236 p_lock_prefix IN Varchar2) RETURN VARCHAR2 IS
1237
1238 PRAGMA AUTONOMOUS_TRANSACTION;
1239 l_lock_handle VARCHAR2(128);
1240 l_lock_name VARCHAR2(30);
1241 BEGIN
1242 l_lock_name := p_lock_prefix || p_org_id;
1243 trace_log('get_lock_handle: lock_name='||l_lock_name);
1244 dbms_lock.allocate_unique(
1245 lockname => l_lock_name
1246 ,lockhandle => l_lock_handle);
1247 trace_log('get_lock_handle: lock_handle='||l_lock_handle);
1248 return l_lock_handle;
1249 END get_lock_handle;
1250
1251
1252 PROCEDURE get_lock(
1253 x_return_status OUT nocopy varchar2,
1254 x_msg_count OUT nocopy number,
1255 x_msg_data OUT nocopy varchar2,
1256 x_lock_status OUT nocopy number,
1257 p_org_id IN NUMBER,
1258 p_lock_prefix IN Varchar2) IS
1259 l_lock_handle varchar2(128);
1260 l_returnStatus varchar2(1);
1261 BEGIN
1262 trace_log('get_lock: Entering for org_id='||p_org_id);
1263 l_lock_handle := get_lock_handle (p_org_id => p_org_id,
1264 p_lock_prefix => p_lock_prefix);
1265 -- request lock with release_on_commit TRUE so that we dont have to manually
1266 -- release the lock later.
1267 x_lock_status := dbms_lock.request(
1268 lockhandle => l_lock_handle,
1269 lockmode => dbms_lock.x_mode,
1270 timeout => dbms_lock.maxwait,
1271 release_on_commit => TRUE);
1272 trace_log('get_lock: got lock for lock handle with status ='||x_lock_status);
1273 trace_log('get_lock: Returning from lock_for_match');
1274 EXCEPTION
1275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1276 trace_log('get_lock: Exception: Unexpected Error '||sqlerrm);
1277 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1278
1279 WHEN OTHERS then
1280 trace_log('get_lock: Exception: Others Exception : ' || sqlerrm);
1281 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1282
1283 END get_lock;
1284
1285
1286 PROCEDURE release_lock(
1287 x_return_status OUT NOCOPY VARCHAR2,
1288 x_msg_count OUT NOCOPY NUMBER,
1289 x_msg_data OUT NOCOPY VARCHAR2,
1290 p_org_id IN NUMBER,
1291 p_lock_prefix IN varchar2) IS
1292 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1293 l_lock_handle VARCHAR2(128);
1294 l_status INTEGER;
1295 l_returnStatus varchar2(1);
1296 BEGIN
1297 trace_log('release_lock: Entering release_lock for org_id'||p_org_id);
1298 --get lock handle by calling helper function
1299 l_lock_handle := get_lock_handle(p_org_id => p_org_id,
1300 p_lock_prefix => p_lock_prefix);
1301 trace_log('release_lock: lock_handle='||l_lock_handle);
1302
1303 l_status := dbms_lock.release(l_lock_handle);
1304 trace_log('release_lock: release returned with status:'||l_status);
1305
1306 --if success (status = 0) or session does not own lock (status=4),
1307 -- do nothing
1308 --if parameter error or illegal lock handle (internal error)
1309
1310 if l_status IN (3,5) THEN
1311 trace_log('release_lock: Error releasing lock');
1312 RAISE fnd_api.g_exc_error;
1313 end if;
1314
1315 x_return_status := l_return_status;
1316
1317
1318 EXCEPTION
1319
1320 WHEN fnd_api.g_exc_error THEN
1321 trace_log('release_lock: Exception: expected error');
1322 x_return_status := fnd_api.g_ret_sts_error;
1323
1324 WHEN fnd_api.g_exc_unexpected_error THEN
1325 trace_log('release_lock: Exception: Unexpected error');
1326 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1327
1328 WHEN OTHERS THEN
1329 trace_log('release_lock: Exception: Others Exception: '||sqlerrm);
1330 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1331
1332 END release_lock;
1333
1334 PROCEDURE trace_log(p_msg IN VARCHAR2) IS
1335 l_returnStatus varchar2(1);
1336 BEGIN
1337 fnd_file.put_line(fnd_file.log,p_msg);
1338 if (g_logLevel <= wip_constants.trace_logging) then
1339 wip_logger.log(p_msg,l_returnStatus);
1340 end if;
1341 END trace_log;
1342
1343
1344 --check shortages in work orders tab supervisor dashboard
1345 -- 1 is shortage
1346 -- 2 is no shortage
1347 -- 3 is not applicable
1348 FUNCTION check_comp_shortage(p_wip_entity_id IN NUMBER,
1349 p_org_id IN NUMBER) RETURN NUMBER IS
1350 l_comp_shortage NUMBER := 3;
1351 l_row_seq_num NUMBER;
1352 l_shortage_calc_level NUMBER;
1353 BEGIN
1354 --Bug 6889755 - Return component shortage depending on the calculation level setup
1355 l_row_seq_num := wip_ws_util.get_multival_pref_seq(
1356 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));
1357
1358 l_shortage_calc_level := wip_ws_util.get_multival_pref_val_code(
1359 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);
1360 --End Bug 6889755
1361
1362 --Bug 6889755 - added IF condition and the ELSE block for subinv level check
1363 IF (l_shortage_calc_level = ORG_CALCULATION_LEVEL) THEN
1364 select decode(nvl(shortage_qty,-1),-1,3,0,2,1)
1365 into l_comp_shortage
1366 from wip_ws_comp_shortage
1367 where wip_entity_id = p_wip_entity_id
1368 and organization_id = p_org_id
1369 and SUPPLY_SUBINVENOTRY is null
1370 and rownum =1;
1371 ELSIF (l_shortage_calc_level = SUBINV_CALCULATION_LEVEL) THEN
1372 select 1
1373 into l_comp_shortage
1374 from wip_ws_comp_shortage
1375 where wip_entity_id = p_wip_entity_id
1376 and organization_id = p_org_id
1377 and SUPPLY_SUBINVENOTRY is not null
1378 and shortage_qty > 0
1379 and rownum =1;
1380 END IF;
1381
1382 --l_comp_shortage :=1;
1383 return l_comp_shortage;
1384
1385
1386 Exception
1387 when no_data_found then
1388 --for organization not setup for comp shortage calculation
1389 l_comp_shortage := 3;
1390 return l_comp_shortage;
1391 When others then
1392 l_comp_shortage := 3;
1393 -- l_comp_shortage :=1;
1394 return l_comp_shortage;
1395 END check_comp_shortage;
1396
1397 FUNCTION check_res_shortage(p_wip_entity_id IN NUMBER,
1398 p_org_id IN NUMBER) RETURN NUMBER IS
1399 l_res_shortage NUMBER := 3;
1400 BEGIN
1401 select decode(nvl(RESOURCE_SHORTAGE,-1),-1,3,0,2,1)
1402 into l_res_shortage
1403 from wip_ws_res_shortage
1404 where wip_entity_id = p_wip_entity_id
1405 and organization_id = p_org_id
1406 and rownum =1;
1407
1408 --l_res_shortage :=1;
1409 return l_res_shortage;
1410 Exception
1411 when no_data_found then
1412 --for organization not setup for res shortage calculation
1413 l_res_shortage := 3;
1414 return l_res_shortage;
1415
1416 When others then
1417 l_res_shortage := 3;
1418 -- l_res_shortage :=1;
1419 return l_res_shortage;
1420 END check_res_shortage;
1421
1422 --new function for bugfix 6755623
1423 function get_csh_calc_level(p_org_id Number) return NUMBER IS
1424 l_calc_level NUMBER;
1425 l_row_seq_num NUMBER;
1426 BEGIN
1427 --get the sequence number
1428 l_row_seq_num := wip_ws_util.get_multival_pref_seq(
1429 g_pref_id_comp_short, g_pref_level_id_site, g_pref_val_mast_org_att, to_char(p_org_id));
1430
1431 if(l_row_seq_num is null) then
1432 return 1; --return org level by default inorder to avoid error
1433 end if;
1434
1435 l_calc_level := wip_ws_util.get_multival_pref_val_code(
1436 g_pref_id_comp_short, g_pref_level_id_site, l_row_seq_num, g_pref_val_calclevel_att);
1437
1438 return l_calc_level;
1439
1440 END get_csh_calc_level;
1441
1442 FUNCTION get_no_of_running_concurrent(
1443 p_program_application_id in number,
1444 p_concurrent_program_id in number,
1445 p_org_id in number) RETURN number
1446 IS
1447 l_pgm_count number;
1448 BEGIN
1449 wip_ws_util.trace_log('Program application id '|| p_program_application_id);
1450 wip_ws_util.trace_log('Concurrent program id '|| p_concurrent_program_id);
1451 wip_ws_util.trace_log('Oraganization id '|| p_org_id);
1452 SELECT count(1)
1453 INTO l_pgm_count
1454 FROM fnd_concurrent_requests
1455 WHERE program_application_id = p_program_application_id
1456 AND concurrent_program_id = p_concurrent_program_id
1457 AND upper(phase_code) = 'R'
1458 AND argument1 = to_char(p_org_id);
1459 wip_ws_util.trace_log('Running concurrent program '|| l_pgm_count);
1460 return l_pgm_count;
1461 END get_no_of_running_concurrent;
1462
1463
1464 PROCEDURE log_for_duplicate_concurrent(
1465 p_org_id in number,
1466 p_program_name in varchar2)
1467 IS
1468 l_org_code varchar2(3);
1469 BEGIN
1470
1471 wip_ws_util.trace_log('Unable to run '|| p_program_name ||' calculation program for organization ' || get_calendar_code(p_org_id));
1472 wip_ws_util.trace_log('Another instance may be running. Please try again after some time.');
1473
1474 END log_for_duplicate_concurrent;
1475
1476
1477 end wip_ws_util;
1478