1 package body WIP_WS_PTPKPI_UTIL as
2 /* $Header: WIPWSPUB.pls 120.16 2008/04/28 22:05:48 awongwai noship $ */
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
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 x_shift_start_date date;
199 x_shift_end_date date;
200 x_shift_string varchar2(100);
201 begin
202 wip_ws_util.retrieve_first_shift(
203 p_org_id,
204 p_dept_id,
205 p_resource_id,
206 p_date,
207 x_shift_seq,
208 x_shift_num,
209 x_shift_start_date,
210 x_shift_end_date,
211 x_shift_string
212 );
213 return (x_shift_seq || '.' || x_shift_num);
214 end get_shift_id_for_date;
215
216
217 /*
218 Description:
219 Get the shift start or end datetime for the specified org and shift id.
220 Parameters:
221 p_org_id - organization id
222 p_shift_id - in the format of <shift_seq_num>.<shift_num>
223 p_start_or_end - 1 to retrieve shift start; 2 to retrieve shift end.
224 Return:
225 the shift start or end time (depending on p_start_or_end)
226 */
227 function get_datetime_for_shift(
228 p_org_id in number,
229 p_shift_id in varchar2,
230 p_start_or_end in number
231 ) return date
232 is
233 x_shift_day date;
234 x_shift_start date;
235 x_shift_end date;
236 x_shift_string varchar2(100);
237 begin
238 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
239 return null;
240 end if;
241
242 load_shift_information(
243 p_org_id,
244 p_shift_id,
245 x_shift_day,
246 x_shift_start,
247 x_shift_end,
248 x_shift_string
249 );
250
251 if (p_start_or_end = 1) then
252 return x_shift_start;
253 elsif (p_start_or_end = 2) then
254 return x_shift_end;
255 else
256 return null;
257 end if;
258
259 exception
260 when others then
261 return null;
262 end get_datetime_for_shift;
263
264
265 /*
266 Description:
267 Get the shift display string for the chart.
268 Parameters:
269 p_org_id - organization id
270 p_shift_id - in the format of <shift_seq_num>.<shift_num>
271 Return:
272 the shift display string on the chart's x-axis
273 */
274 function get_chart_str_for_shift(
275 p_org_id in number,
276 p_shift_id in varchar2
277 ) return varchar2
278 is
279 x_shift_day date;
280 x_shift_start date;
281 x_shift_end date;
282 x_shift_string varchar2(100);
283 begin
284 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
285 return null;
286 end if;
287
288 load_shift_information(
289 p_org_id,
290 p_shift_id,
291 x_shift_day,
292 x_shift_start,
293 x_shift_end,
294 x_shift_string
295 );
296
297 return x_shift_string;
298 exception
299 when others then
300 return null;
301 end get_chart_str_for_shift;
302
303
304 /*
305 Description:
306 Get the day display string for the chart.
307 Parameters:
308 p_org_id - organization id
309 p_shift_id - in the format of <shift_seq_num>.<shift_num>
310 Return:
311 the day display string on the chart's x-axis
312 */
313 function get_chart_str_for_day(
314 p_org_id in number,
315 p_shift_id in varchar2
316 ) return varchar2
317 is
318 x_shift_day date;
319 x_shift_start date;
320 x_shift_end date;
321 x_shift_string varchar2(100);
322 begin
323 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
324 return null;
325 end if;
326
327 load_shift_information(
328 p_org_id,
329 p_shift_id,
330 x_shift_day,
331 x_shift_start,
332 x_shift_end,
333 x_shift_string
334 );
335
336 return x_shift_day;
337 exception
338 when others then
339 return null;
340 end get_chart_str_for_day;
341
342
343
344 /*
345 Description:
346 Get the shift-related information for the given org and shift id.
347 Parameters:
348 p_org_id - organization id
349 p_shift_id - in the format of <shift_seq_num>.<shift_num>
350 x_shift_day - the shift day (always trucated timestamp)
351 x_shift_start - the shift start timestamp (inclusive)
352 x_shift_end - the shift end timestamp (exclusive)
353 x_shift_chart_str - the shift display on the chart
354 Return:
355 */
356 procedure load_shift_information(
357 p_org_id in number,
358 p_shift_id in varchar2,
359 x_shift_day out nocopy date,
360 x_shift_start out nocopy date,
361 x_shift_end out nocopy date,
362 x_shift_chart_str out nocopy varchar2
363 )
364 is
365 l_shift_seq_num number;
366 l_shift_num number;
367 l_calendar_code varchar2(50);
368 l_shift_description varchar2(100);
369 begin
370 -- cannot proceed if shift_id is null
371 if (instr(nvl(p_shift_id,'@'),'.') <= 0) then
372 return;
373 end if;
374
375 l_shift_seq_num := substr(p_shift_id, 1, instr(p_shift_id, '.')-1);
376 l_shift_num := substr(p_shift_id, instr(p_shift_id, '.')+1, length(p_shift_id));
377
378 -- cannot proceed if shift_id is malformed
379 if (l_shift_seq_num is null or l_shift_num is null) then
380 return;
381 end if;
382
383 l_calendar_code := get_calendar_code(p_org_id);
384
385 select
386 bsd.shift_date + st.from_time/(60*60*24),
387 bsd.shift_date + st.to_time/(60*60*24),
388 trunc(bsd.shift_date),
389 bcs.description
390 into
391 x_shift_start,
392 x_shift_end,
393 x_shift_day,
394 l_shift_description
395 from
396 bom_shift_dates bsd,
397 (select
398 bst.shift_num,
399 min(bst.from_time) from_time,
400 max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
401 from bom_shift_times bst
402 where bst.calendar_code = l_calendar_code
403 and bst.shift_num = l_shift_num
404 group by bst.shift_num
405 ) st,
406 bom_calendar_shifts bcs
407 where bsd.calendar_code = l_calendar_code
408 and bsd.exception_set_id = -1
409 and bsd.seq_num = l_shift_seq_num
410 and bsd.shift_num = st.shift_num
411 and bsd.calendar_code = bcs.calendar_code
412 and bsd.shift_num = bcs.shift_num
413 ;
414
415 -- construct the "display string" here...
416 --x_shift_chart_str := x_shift_day || ':' || l_shift_num || ':' || l_shift_description;
417 x_shift_chart_str := get_shift_display_str(x_shift_day, l_shift_num, l_shift_description);
418
419 exception
420 when others then
421 return;
422 end load_shift_information;
423
424
425 function get_n_previous_working_day(
426 p_org_id number,
427 n number,
428 p_date date
429 ) return date
430 is
431 day date := null;
432 begin
433 if (n < 1) then
434 return null;
435 end if;
436 day := mrp_calendar.PREV_WORK_DAY(p_org_id,1,trunc(p_date));
437 for i in 1..(n-1) loop
438 day := mrp_calendar.PREV_WORK_DAY(207,1,day-1);
439 end loop;
440 return day;
441 end get_n_previous_working_day;
442
443 --------------------------------------------------
444 --------------------------------------------------
445 /* start: for ui -- work in progress */
446 procedure get_shifts(
447 p_organization_id in number,
448 p_department_id in number,
449 p_resource_id in number,
450 p_start_shift_date in date,
451 p_end_shift_date in date
452 ) is
453 begin
454 if (p_department_id is null) then
455 get_org_shifts(p_organization_id, p_start_shift_date, p_end_shift_date);
456 else
457 get_dept_resource_shifts(
458 p_organization_id,
459 p_department_id,
460 p_resource_id,
461 p_start_shift_date,
462 p_end_shift_date
463 );
464 end if;
465 end get_shifts;
466
467
468 procedure get_org_shifts(
469 p_organization_id in number,
470 p_start_shift_date in date,
471 p_end_shift_date in date
472 ) is
473 l_calendar_code varchar2(50) := null;
474 l_shift_info shift_info_t := null;
475 l_start_shift_day date := null;
476 l_end_shift_day date := null;
477
478 -- cursor to get all shifts for day and calendar
479 cursor c_shifts(
480 p_organization_id number,
481 start_shift_day date,
482 end_shift_day date,
483 p_calendar_code varchar2
484 ) is
485 select
486 bsd.seq_num || '.' || bsd.shift_num as shift_id,
487 to_char(
488 wip_ws_util.get_appended_date(bsd.shift_date, t.from_time),
489 'DD-MON-YYYY HH24:MI:SS'
490 ) as from_date_char,
491 to_char(
492 wip_ws_util.get_appended_date( bsd.shift_date, t.to_time),
493 'DD-MON-YYYY HH24:MI:SS'
494 ) as to_date_char,
495 wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) as from_date,
496 wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) as to_date,
497 t.shift_num as shift_num,
498 bsd.seq_num as seq_num,
499 wip_ws_util.get_shift_info_for_display(
500 p_organization_id, bsd.seq_num, t.shift_num
501 ) as display
502 from
503 bom_shift_dates bsd,
504 (
505 select
506 bst.calendar_code,
507 bst.shift_num,
508 min(bst.from_time) from_time,
509 max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
510 from bom_shift_times bst
511 where bst.calendar_code = p_calendar_code
512 group by bst.calendar_code, bst.shift_num
513 ) t
514 where bsd.calendar_code = p_calendar_code
515 and bsd.calendar_code = t.calendar_code
516 and bsd.shift_num = t.shift_num
517 and bsd.exception_set_id = -1
518 and bsd.shift_date between start_shift_day and end_shift_day
519 and bsd.seq_num is not null
520 order by from_date;
521 -- end cursor c_shifts
522
523 l_return_status varchar2(1000);
524 begin
525 l_calendar_code := get_calendar_code(p_organization_id);
526 l_start_shift_day := trunc(p_start_shift_date);
527 l_end_shift_day := trunc(p_end_shift_date);
528
529 open c_shifts(
530 p_organization_id, l_start_shift_day, l_end_shift_day, l_calendar_code
531 );
532 loop
533 fetch c_shifts into l_shift_info;
534 wip_logger.log(
535 'shift_id=' || l_shift_info.shift_id ||
536 ', from_date_char=' || l_shift_info.from_date_char ||
537 ', to_date_char=' || l_shift_info.to_date_char,l_return_status
538 );
539 exit when c_shifts%NOTFOUND;
540 end loop;
541 close c_shifts;
542 end get_org_shifts;
543
544
545 procedure get_dept_resource_shifts(
546 p_organization_id in number,
547 p_department_id in number,
548 p_resource_id in number,
549 p_start_shift_date in date,
550 p_end_shift_date in date
551 ) is
552 begin
553 null;
554 end get_dept_resource_shifts;
555
556
557
558 procedure get_candidate_shifts_for_day(
559 p_organization_id in number,
560 p_department_id in number,
561 p_day date
562 ) is
563 begin
564 null;
565 end get_candidate_shifts_for_day;
566 /* end: for ui -- work in progress */
567
568 /*
569 Used by UI to construct shift name for a given shift number
570 */
571 function get_shift_name_for_display(
572 p_shift_num in number) return varchar2 is
573 l_shift_string varchar2(240);
574 begin
575 fnd_message.SET_NAME('WIP', 'WIP_WS_PTP_SHIFT_SINGLE');
576 fnd_message.SET_TOKEN('SHIFT_NUM', p_shift_num);
577 l_shift_string := fnd_message.GET;
578 return l_shift_string;
579
580 exception when others then
581 return to_char(p_shift_num);
582
583 end get_shift_name_for_display;
584
585 function get_date_as_string(
586 p_date in date) return varchar2 is
587 begin
588
589 return trunc(p_date)||'';
590 end get_date_as_string;
591
592
593 function get_shift_display_str(
594 p_shift_date in date,
595 p_shift_num in number,
596 p_shift_desc in varchar2) return varchar2 is
597 l_shift_str varchar2(240);
598 begin
599 l_shift_str := trunc(p_shift_date) || ':' || get_shift_name_for_display(p_shift_num);
600 return l_shift_str;
601
602 end get_shift_display_str;
603
604
605
606 begin
607 -- Initialization
608 null;
609
610 end WIP_WS_PTPKPI_UTIL;
[Home] [Help]