1 package body WIP_WS_PTPKPI_UTIL as
2 /* $Header: WIPWSPUB.pls 120.16.12020000.2 2012/12/26 07:08:30 sjallipa ship $ */
3
4 /*
5 Description:
6 Get the calendar code from inventory parameters based on the
7 Parameters:
8 p_organization_id - the organization id
9 Return:
10 the calendar code of the given organization.
11 */
12 function get_calendar_code(p_organization_id number)
13 return varchar2 is
14 l_calendar_code varchar2(100) := null;
15 begin
16 -- get the calendar_code based on organization_id
17 select mp.calendar_code into l_calendar_code
18 from mtl_parameters mp
19 where mp.organization_id = p_organization_id;
20 return l_calendar_code;
21 exception when others then
22 return null;
23 end get_calendar_code;
24
25
26 /*
27 Description:
28 Get the primary uom code for the given org and wip entity.
29 Parameters:
30 p_org_id - organization id
31 p_wip_entity_id - the wip entity id (job, schedule, etc.)
32 Return:
33 the primary uom of the assembly
34 */
35 function get_primary_uom_code(
36 p_org_id in number,
37 p_wip_entity_id in number
38 ) return varchar2
39 is
40 l_primary_uom_code varchar2(10) := null;
41 cursor retrieve_primary_uom_code_c is
42 select primary_uom_code
43 from mtl_system_items msi, wip_entities we
44 where msi.organization_id = we.organization_id
45 and we.primary_item_id = msi.inventory_item_id
46 and we.organization_id = p_org_id
47 and we.wip_entity_id = p_wip_entity_id
48 ;
49 begin
50 open retrieve_primary_uom_code_c;
51 fetch retrieve_primary_uom_code_c into l_primary_uom_code;
52 close retrieve_primary_uom_code_c;
53
54 return l_primary_uom_code;
55 end get_primary_uom_code;
56
57
58 /*
59 Description:
60 Get the project id for the given discrete job.
61 Parameters:
62 p_org_id - the organization id
63 p_wip_entity_id - the discrete job wip entity id
64 Return:
65 */
66 function get_project_id(
67 p_org_id in number,
68 p_wip_entity_id in number
69 ) return number
70 is
71 l_project_id number := null;
72 cursor retrieve_project_id_c is
73 select project_id
74 from wip_discrete_jobs
75 where organization_id = p_org_id
76 and wip_entity_id = p_wip_entity_id
77 ;
78 begin
79 open retrieve_project_id_c;
80 fetch retrieve_project_id_c into l_project_id;
81 close retrieve_project_id_c;
82
83 return l_project_id;
84 end get_project_id;
85
86
87 /*
88 Description:
89 Get the task id for the given discrete job.
90 Parameters:
91 p_org_id - the organization id
92 p_wip_entity_id - the discrete job wip entity id
93 Return:
94 */
95 function get_task_id(
96 p_org_id in number,
97 p_wip_entity_id in number
98 ) return number
99 is
100 l_task_id number := null;
101 cursor retrieve_task_id_c is
102 select task_id
103 from wip_discrete_jobs
104 where organization_id = p_org_id
105 and wip_entity_id = p_wip_entity_id
106 ;
107 begin
108 open retrieve_task_id_c;
109 fetch retrieve_task_id_c into l_task_id;
110 close retrieve_task_id_c;
111
112 return l_task_id;
113 end get_task_id;
114
115
116 /*
117 Get the operation lead time (in minutes) for the given job operation.
118
119 The operation lead time is found as:
120 operation lead time = (item lead time) * (operation lead time %)
121
122 The item lead time is the one stored at the item level, which is
123 calculated by a concurrent program. The operation lead time % is
124 defined at the BOM level, which is also calculated by a concurrent
125 program.
126
127 For MES Production To Plan KPI, we assume that the item's lead time
128 and operation lead time % always exist.
129 */
130 function get_operation_lead_time(
131 p_org_id in number,
132 p_wip_entity_id in number,
133 p_op_seq_num in number
134
135 ) return number
136 is
137 l_hrUOM varchar2(3);
138 l_lead_time number;
139 begin
140
141 l_hrUOM := fnd_profile.value('BOM:HOUR_UOM_CODE');
142 select sum(lead_time)
143 into l_lead_time
144 from
145 (select max((case when (inv_convert.inv_um_convert(0,wor.uom_code,l_hrUOM) = -99999)
146 then 0
147 else inv_convert.inv_um_convert(0,wor.uom_code,l_hrUOM)*wor.usage_rate_or_amount
148 end)*decode(wor.basis_type,WIP_CONSTANTS.PER_LOT,1,wo.scheduled_quantity)
149 /least(wor.assigned_units,bdr.capacity_units)/(nvl( bdr.utilization,1))/(nvl(bdr.efficiency,1))) lead_time
150 from wip_operation_resources wor,
151 wip_operations wo,
152 bom_department_resources bdr,
153 bom_resources br
154 where wo.wip_entity_id = p_wip_entity_id
155 and wo.wip_entity_id = wor.wip_entity_id
156 and wo.operation_seq_num = p_op_seq_num
157 and wo.operation_seq_num = wor.operation_seq_num
158 and wo.department_id = bdr.department_id
159 and wor.resource_id = bdr.resource_id
160 and wor.resource_id = br.resource_id
161 and br.resource_type in (WIP_CONSTANTS.RES_MACHINE, WIP_CONSTANTS.RES_PERSON)
162 and wor.scheduled_flag <> WIP_CONSTANTS.SCHED_NO
163 group by to_char(nvl(to_char(wor.schedule_seq_num),rowidtochar(wor.rowid)))
164 );
165
166 return l_lead_time;
167 end get_operation_lead_time;
168
169
170 /*
171 Description:
172 Given the organization, department, resource, and a timestamp,
173 find out which shift the timestamp belongs to. It uses the existing
174 shift definition as defined in the wip_ws_util package.
175 Parameters:
176 p_org_id - the organization id
177 p_dept_id - the department id
178 p_resource_id - the resource id
179 p_date - the timestamp
180 */
181 function get_shift_id_for_date
182 (
183 p_org_id in number,
184 p_dept_id in number,
185 p_resource_id in number,
186 p_date in date
187 ) return varchar2
188 is
189 l_cal_code varchar2(30);
190 -- Bug 16001235 @Start
191 -- l_c_start_date date;
192 -- l_c_end_date date;
193 -- l_c_from_time varchar2(60);
194 -- l_c_to_time varchar2(60);
195 -- l_24hr_resource number;
196 x_shift_seq number;
197 x_shift_num number;
198 l_cur_date date;
199 l_prior_date date;
200 l_next_date date;
201 x_shift_id varchar2(30);
202 -- x_shift_start_date date;
203 -- x_shift_end_date date;
204 -- x_shift_string varchar2(100);
205 begin
206
207 /* wip_ws_util.retrieve_first_shift(
208 p_org_id,
209 p_dept_id,
210 p_resource_id,
211 p_date,
212 x_shift_seq,
213 x_shift_num,
214 x_shift_start_date,
215 x_shift_end_date,
216 x_shift_string
217 );*/
218 x_shift_seq := null;
219 x_shift_num := null;
220 x_shift_id := null;
221 begin
222 select calendar_code
223 into l_cal_code
224 from mtl_parameters
225 where organization_id = p_org_id;
226
227 /* find out the day that is on */
228 select min(bsd.shift_date)
229 into l_cur_date
230 from bom_shift_dates bsd, bom_resource_shifts brs
231 where bsd.calendar_code = l_cal_code and
232 bsd.shift_date >= trunc( p_date )and
233 brs.department_id = p_dept_id and
234 brs.resource_id = nvl( p_resource_id, brs.resource_id) and
235 brs.shift_num = bsd.shift_num and
236 bsd.exception_set_id = -1 and
237 bsd.seq_num is not null;
238
239 /* find out prior and next day in calendar */
240 select max(bsd.shift_date)
241 into l_prior_date
242 from bom_shift_dates bsd, bom_resource_shifts brs
243 where bsd.calendar_code = l_cal_code and
244 bsd.shift_date < l_cur_date and
245 brs.department_id = p_dept_id and
246 brs.resource_id = nvl( p_resource_id, brs.resource_id) and
247 brs.shift_num = bsd.shift_num and
248 bsd.exception_set_id = -1 and
249 bsd.seq_num is not null;
250
251 select min(bsd.shift_date)
252 into l_next_date
253 from bom_shift_dates bsd, bom_resource_shifts brs
254 where bsd.calendar_code = l_cal_code and
255 bsd.shift_date > l_cur_date and
256 brs.department_id = p_dept_id and
257 brs.resource_id = nvl( p_resource_id, brs.resource_id) and
258 brs.shift_num = bsd.shift_num and
259 bsd.exception_set_id = -1 and
260 bsd.seq_num is not null;
261
262 /* find out the closest shift that is running or going to run */
263 select
264 seq_num,
265 shift_num
266 into
267 x_shift_seq,
268 x_shift_num
269
270 from
271 (
272 select bsd.shift_num,
273 bsd.seq_num
274 from bom_shift_dates bsd,
275 ( select bst.shift_num,
276 min(bst.from_time) from_time,
277 max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
278 from bom_shift_times bst
279 where bst.calendar_code = l_cal_code
280 group by bst.shift_num
281 ) st ,
282 bom_resource_shifts brs
283 where bsd.calendar_code = l_cal_code and
284 bsd.shift_num = st.shift_num and
285 brs.department_id = p_dept_id and
286 brs.resource_id = nvl( p_resource_id, brs.resource_id) and
287 brs.shift_num = bsd.shift_num and
288 (bsd.shift_date + st.to_time / (60 * 60 * 24)) >= p_date and
289 bsd.shift_date in (l_cur_date, l_prior_date, l_next_date)
290 order by bsd.shift_date + st.from_time / (60 * 60 * 24)
291 ) t
292 where rownum = 1;
293 exception
294 when others then
295 null;
296 end;
297
298 if(x_shift_seq is not null and x_shift_num is not null ) then
299 x_shift_id := x_shift_seq || '.' || x_shift_num;
300 end if;
301 -- return (x_shift_seq || '.' || x_shift_num);
302 return x_shift_id;
303 -- Bug 16001235 @End
304 end get_shift_id_for_date;
305
306
307 /*
308 Description:
309 Get the shift start or end datetime for the specified org and shift id.
310 Parameters:
311 p_org_id - organization id
312 p_shift_id - in the format of <shift_seq_num>.<shift_num>
313 p_start_or_end - 1 to retrieve shift start; 2 to retrieve shift end.
314 Return:
315 the shift start or end time (depending on p_start_or_end)
316 */
317 function get_datetime_for_shift(
318 p_org_id in number,
319 p_shift_id in varchar2,
320 p_start_or_end in number
321 ) return date
322 is
323 x_shift_day date;
324 x_shift_start date;
325 x_shift_end date;
326 x_shift_string varchar2(100);
327 begin
328 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
329 return null;
330 end if;
331
332 load_shift_information(
333 p_org_id,
334 p_shift_id,
335 x_shift_day,
336 x_shift_start,
337 x_shift_end,
338 x_shift_string
339 );
340
341 if (p_start_or_end = 1) then
342 return x_shift_start;
343 elsif (p_start_or_end = 2) then
344 return x_shift_end;
345 else
346 return null;
347 end if;
348
349 exception
350 when others then
351 return null;
352 end get_datetime_for_shift;
353
354
355 /*
356 Description:
357 Get the shift display string for the chart.
358 Parameters:
359 p_org_id - organization id
360 p_shift_id - in the format of <shift_seq_num>.<shift_num>
361 Return:
362 the shift display string on the chart's x-axis
363 */
364 function get_chart_str_for_shift(
365 p_org_id in number,
366 p_shift_id in varchar2
367 ) return varchar2
368 is
369 x_shift_day date;
370 x_shift_start date;
371 x_shift_end date;
372 x_shift_string varchar2(100);
373 begin
374 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
375 return null;
376 end if;
377
378 load_shift_information(
379 p_org_id,
380 p_shift_id,
381 x_shift_day,
382 x_shift_start,
383 x_shift_end,
384 x_shift_string
385 );
386
387 return x_shift_string;
388 exception
389 when others then
390 return null;
391 end get_chart_str_for_shift;
392
393
397 Parameters:
394 /*
395 Description:
396 Get the day display string for the chart.
398 p_org_id - organization id
399 p_shift_id - in the format of <shift_seq_num>.<shift_num>
400 Return:
401 the day display string on the chart's x-axis
402 */
403 function get_chart_str_for_day(
404 p_org_id in number,
405 p_shift_id in varchar2
406 ) return varchar2
407 is
408 x_shift_day date;
409 x_shift_start date;
410 x_shift_end date;
411 x_shift_string varchar2(100);
412 begin
413 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
414 return null;
415 end if;
416
417 load_shift_information(
418 p_org_id,
419 p_shift_id,
420 x_shift_day,
421 x_shift_start,
422 x_shift_end,
423 x_shift_string
424 );
425
426 return x_shift_day;
427 exception
428 when others then
429 return null;
430 end get_chart_str_for_day;
431
432
433
434 /*
435 Description:
436 Get the shift-related information for the given org and shift id.
437 Parameters:
438 p_org_id - organization id
439 p_shift_id - in the format of <shift_seq_num>.<shift_num>
440 x_shift_day - the shift day (always trucated timestamp)
441 x_shift_start - the shift start timestamp (inclusive)
442 x_shift_end - the shift end timestamp (exclusive)
443 x_shift_chart_str - the shift display on the chart
444 Return:
445 */
446 procedure load_shift_information(
447 p_org_id in number,
448 p_shift_id in varchar2,
449 x_shift_day out nocopy date,
450 x_shift_start out nocopy date,
451 x_shift_end out nocopy date,
452 x_shift_chart_str out nocopy varchar2
453 )
454 is
455 l_shift_seq_num number;
456 l_shift_num number;
457 l_calendar_code varchar2(50);
458 l_shift_description varchar2(100);
459 begin
460 -- cannot proceed if shift_id is null
461 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
462 return;
463 end if;
464
465 l_shift_seq_num := substr(p_shift_id, 1, instr(p_shift_id, '.')-1);
466 l_shift_num := substr(p_shift_id, instr(p_shift_id, '.')+1, length(p_shift_id));
467
468 -- cannot proceed if shift_id is malformed
469 if (l_shift_seq_num is null or l_shift_num is null) then
470 return;
471 end if;
472
473 l_calendar_code := get_calendar_code(p_org_id);
474
475 select
476 bsd.shift_date + st.from_time/(60*60*24),
477 bsd.shift_date + st.to_time/(60*60*24),
478 trunc(bsd.shift_date),
479 bcs.description
480 into
481 x_shift_start,
482 x_shift_end,
483 x_shift_day,
484 l_shift_description
485 from
486 bom_shift_dates bsd,
487 (select
488 bst.shift_num,
489 min(bst.from_time) from_time,
490 max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
491 from bom_shift_times bst
492 where bst.calendar_code = l_calendar_code
493 and bst.shift_num = l_shift_num
494 group by bst.shift_num
495 ) st,
496 bom_calendar_shifts bcs
497 where bsd.calendar_code = l_calendar_code
498 and bsd.exception_set_id = -1
499 and bsd.seq_num = l_shift_seq_num
500 and bsd.shift_num = st.shift_num
501 and bsd.calendar_code = bcs.calendar_code
502 and bsd.shift_num = bcs.shift_num
503 ;
504
505 -- construct the "display string" here...
506 --x_shift_chart_str := x_shift_day || ':' || l_shift_num || ':' || l_shift_description;
507 x_shift_chart_str := get_shift_display_str(x_shift_day, l_shift_num, l_shift_description);
508
509 exception
510 when others then
511 return;
512 end load_shift_information;
513
514
515 function get_n_previous_working_day(
516 p_org_id number,
517 n number,
518 p_date date
519 ) return date
520 is
521 day date := null;
522 begin
523 if (n < 1) then
524 return null;
525 end if;
526 day := mrp_calendar.PREV_WORK_DAY(p_org_id,1,trunc(p_date));
527 for i in 1..(n-1) loop
528 day := mrp_calendar.PREV_WORK_DAY(207,1,day-1);
529 end loop;
530 return day;
531 end get_n_previous_working_day;
532
533 --------------------------------------------------
534 --------------------------------------------------
535 /* start: for ui -- work in progress */
536 procedure get_shifts(
537 p_organization_id in number,
538 p_department_id in number,
539 p_resource_id in number,
540 p_start_shift_date in date,
541 p_end_shift_date in date
542 ) is
543 begin
544 if (p_department_id is null) then
545 get_org_shifts(p_organization_id, p_start_shift_date, p_end_shift_date);
546 else
547 get_dept_resource_shifts(
548 p_organization_id,
549 p_department_id,
550 p_resource_id,
551 p_start_shift_date,
552 p_end_shift_date
553 );
554 end if;
555 end get_shifts;
556
557
558 procedure get_org_shifts(
559 p_organization_id in number,
560 p_start_shift_date in date,
561 p_end_shift_date in date
562 ) is
563 l_calendar_code varchar2(50) := null;
564 l_shift_info shift_info_t := null;
565 l_start_shift_day date := null;
566 l_end_shift_day date := null;
567
568 -- cursor to get all shifts for day and calendar
569 cursor c_shifts(
570 p_organization_id number,
571 start_shift_day date,
572 end_shift_day date,
573 p_calendar_code varchar2
574 ) is
575 select
576 bsd.seq_num || '.' || bsd.shift_num as shift_id,
577 to_char(
578 wip_ws_util.get_appended_date(bsd.shift_date, t.from_time),
579 'DD-MON-YYYY HH24:MI:SS'
580 ) as from_date_char,
581 to_char(
582 wip_ws_util.get_appended_date( bsd.shift_date, t.to_time),
583 'DD-MON-YYYY HH24:MI:SS'
584 ) as to_date_char,
585 wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) as from_date,
586 wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) as to_date,
587 t.shift_num as shift_num,
588 bsd.seq_num as seq_num,
589 wip_ws_util.get_shift_info_for_display(
590 p_organization_id, bsd.seq_num, t.shift_num
591 ) as display
592 from
593 bom_shift_dates bsd,
594 (
595 select
596 bst.calendar_code,
597 bst.shift_num,
598 min(bst.from_time) from_time,
599 max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
600 from bom_shift_times bst
601 where bst.calendar_code = p_calendar_code
602 group by bst.calendar_code, bst.shift_num
603 ) t
604 where bsd.calendar_code = p_calendar_code
605 and bsd.calendar_code = t.calendar_code
606 and bsd.shift_num = t.shift_num
607 and bsd.exception_set_id = -1
608 and bsd.shift_date between start_shift_day and end_shift_day
609 and bsd.seq_num is not null
610 order by from_date;
611 -- end cursor c_shifts
612
613 l_return_status varchar2(1000);
614 begin
615 l_calendar_code := get_calendar_code(p_organization_id);
616 l_start_shift_day := trunc(p_start_shift_date);
617 l_end_shift_day := trunc(p_end_shift_date);
618
619 open c_shifts(
620 p_organization_id, l_start_shift_day, l_end_shift_day, l_calendar_code
621 );
622 loop
623 fetch c_shifts into l_shift_info;
624 wip_logger.log(
625 'shift_id=' || l_shift_info.shift_id ||
626 ', from_date_char=' || l_shift_info.from_date_char ||
627 ', to_date_char=' || l_shift_info.to_date_char,l_return_status
628 );
629 exit when c_shifts%NOTFOUND;
630 end loop;
631 close c_shifts;
632 end get_org_shifts;
633
634
635 procedure get_dept_resource_shifts(
636 p_organization_id in number,
637 p_department_id in number,
638 p_resource_id in number,
639 p_start_shift_date in date,
640 p_end_shift_date in date
641 ) is
642 begin
643 null;
644 end get_dept_resource_shifts;
645
646
647
648 procedure get_candidate_shifts_for_day(
649 p_organization_id in number,
650 p_department_id in number,
651 p_day date
652 ) is
653 begin
654 null;
655 end get_candidate_shifts_for_day;
656 /* end: for ui -- work in progress */
657
658 /*
659 Used by UI to construct shift name for a given shift number
660 */
661 function get_shift_name_for_display(
662 p_shift_num in number) return varchar2 is
663 l_shift_string varchar2(240);
664 begin
665 fnd_message.SET_NAME('WIP', 'WIP_WS_PTP_SHIFT_SINGLE');
666 fnd_message.SET_TOKEN('SHIFT_NUM', p_shift_num);
667 l_shift_string := fnd_message.GET;
668 return l_shift_string;
669
670 exception when others then
671 return to_char(p_shift_num);
672
673 end get_shift_name_for_display;
674
675 function get_date_as_string(
676 p_date in date) return varchar2 is
677 begin
678
679 return trunc(p_date)||'';
680 end get_date_as_string;
681
682
683 function get_shift_display_str(
684 p_shift_date in date,
685 p_shift_num in number,
686 p_shift_desc in varchar2) return varchar2 is
687 l_shift_str varchar2(240);
688 begin
689 l_shift_str := trunc(p_shift_date) || ':' || get_shift_name_for_display(p_shift_num);
690 return l_shift_str;
691
692 end get_shift_display_str;
693
694
695
696 begin
697 -- Initialization
698 null;
699
700 end WIP_WS_PTPKPI_UTIL;
[Home] [Help]