1 PACKAGE BODY WPS_COMMON AS
2 /* $Header: wpscommb.pls 120.1 2005/08/30 11:09:15 sjchen noship $ */
3
4 --
5 --
6 -- Public Functions
7 --
8
9 -- use this as a separator, need to change because
10 -- it might not work in language other than english
11 ESC_CHR VARCHAR(4) := FND_GLOBAL.Local_Chr(27);
12
13 FUNCTION Get_Install_Status RETURN VARCHAR2
14 IS
15 l_retval BOOLEAN;
16 l_status VARCHAR2(1);
17 l_industry VARCHAR2(1);
18
19 BEGIN
20
21 l_retval := fnd_installation.get(WPS_APPLICATION_ID,
22 WPS_APPLICATION_ID,
23 l_status,
24 l_industry);
25
26 IF (l_status IN ('I', 'S', 'N')) THEN
27 RETURN (l_status);
28 ELSE
29 RETURN ('N');
30 END IF;
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 return('N');
35
36 END ;
37
38
39 PROCEDURE GetParameters(p_org_id IN NUMBER,
40 x_use_finite_scheduler OUT NOCOPY NUMBER,
41 x_material_constrained OUT NOCOPY NUMBER,
42 x_horizon_length OUT NOCOPY NUMBER)
43 IS
44 BEGIN
45
46 SELECT
47 Nvl(USE_FINITE_SCHEDULER,2),
48 Nvl(MATERIAL_CONSTRAINED,2),
49 Nvl(HORIZON_LENGTH,1)
50 INTO x_use_finite_scheduler,
51 x_material_constrained,
52 x_horizon_length
53 FROM WIP_PARAMETERS
54 WHERE ORGANIZATION_ID = p_org_id;
55
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 x_use_finite_scheduler := 2;
60 x_material_constrained := 2;
61 x_horizon_length := 1;
62
63 END GetParameters;
64
65
66 /*
67 * Procedure that populates the resource availability into
68 * MRP_NET_RESOURCE_AVAIL if not already there.
69 */
70 PROCEDURE Populate_Resource_Avails (p_simulation_set IN VARCHAR2,
71 p_organization_id IN NUMBER,
72 p_start_date IN DATE,
73 p_cutoff_date IN DATE,
74 p_wip_entity_id IN NUMBER,
75 p_errnum OUT NOCOPY NUMBER,
76 p_errmesg OUT NOCOPY VARCHAR2,
77 p_reload IN NUMBER)
78 IS
79 x_date_from DATE := trunc(p_start_date);
80 x_date_to DATE := trunc(p_cutoff_date);
81 BEGIN
82
83 p_errnum := 0;
84 p_errmesg := 'Success';
85
86 if (p_reload = 0) then -- check if we want to refresh the data
87 -- Check to make sure that if the resource information is already inserted
88 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
89 -- The p_start_date, p_cutoff_date can be modified by
90 -- resource_info_found_in_mrp function if this function
91 -- returns FALSE so that we can get a new from_date to
92 -- call MRP_RHX_RESOURCE_AVAILABILITY
93 if (resource_info_found_in_mrp(p_simulation_set,
94 p_organization_id,
95 x_date_from,
96 x_date_to)) then
97 RETURN;
98 end if;
99 end if;
100
101 -- information not found in MRP, call MRP
102 populate_mrp_avail_resources(
103 p_simulation_set => p_simulation_set,
104 p_organization_id => p_organization_id,
105 p_start_date => x_date_from,
106 p_cutoff_date => x_date_to,
107 p_wip_entity_id => p_wip_entity_id);
108
109 RETURN ;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 p_errnum := -1 ;
114 p_errmesg := 'Populate_Resource_Avails Failed - ' ||
115 to_char(SQLCODE) || ': ' || SQLERRM;
116
117 RETURN ;
118
119 END Populate_Resource_Avails ;
120
121 PROCEDURE Populate_Resource_Avails
122 (p_simulation_set IN VARCHAR2,
123 p_organization_id IN NUMBER,
124 p_start_date IN DATE,
125 p_cutoff_date IN DATE,
126 p_resource_table IN Number_Tbl_Type,
127 p_dept_table IN Number_Tbl_Type,
128 p_24hour_flag_table IN Number_Tbl_Type,
129 p_errnum OUT NOCOPY NUMBER,
130 p_errmesg OUT NOCOPY VARCHAR2,
131 p_reload IN NUMBER,
132 p_tbl_size IN NUMBER,
133 p_delete_data IN NUMBER)
134 IS
135 x_date_from DATE := trunc(p_start_date);
136 x_date_to DATE := trunc(p_cutoff_date);
137 x NUMBER := 1;
138 BEGIN
139
140 p_errnum := 0;
141 p_errmesg := 'Success';
142
143 if (p_reload = 0) then -- check if we want to refresh the data
144 -- Check to make sure that if the resource information is already inserted
145 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
146 -- The p_start_date, p_cutoff_date can be modified by
147 -- resource_info_found_in_mrp function if this function
148 -- returns FALSE so that we can get a new from_date to
149 -- call MRP_RHX_RESOURCE_AVAILABILITY
150 if (resource_info_found_in_mrp(p_simulation_set,
151 p_organization_id,
152 x_date_from,
153 x_date_to)) then
154 RETURN;
155 end if;
156 end if;
157
158 -- information not found in MRP, call MRP
159 if(p_delete_data = 1) then
160 delete from mrp_net_resource_avail
161 where organization_id = p_organization_id
162 and simulation_set = p_simulation_set
163 and trunc(shift_date) >= trunc(p_start_date)
164 and trunc(shift_date) <= trunc(p_cutoff_date);
165 end if;
166
167 LOOP
168 EXIT WHEN p_resource_table(x) = 0;
169
170 MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail(p_organization_id,
171 p_dept_table(x),
172 p_resource_table(x),
173 p_simulation_set,
174 p_24hour_flag_table(x),
175 p_start_date,
176 p_cutoff_date);
177
178 x := x+1;
179
180
181
182 END LOOP
183
184 RETURN ;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 p_errnum := -1 ;
189 p_errmesg := 'Populate_Resource_Avails Failed - ' ||
190 to_char(SQLCODE) || ': ' || SQLERRM;
191
192 RETURN ;
193
194 END Populate_Resource_Avails;
195
196
197 PROCEDURE Populate_Res_Instance_Avails
198 (p_simulation_set IN VARCHAR2,
199 p_organization_id IN NUMBER,
200 p_start_date IN DATE,
201 p_cutoff_date IN DATE,
202 p_wip_entity_id IN NUMBER,
203 p_errnum OUT NOCOPY NUMBER,
204 p_errmesg OUT NOCOPY VARCHAR2,
205 p_reload IN NUMBER)
206 IS
207 x_date_from DATE := trunc(p_start_date);
208 x_date_to DATE := trunc(p_cutoff_date);
209 BEGIN
210
211 p_errnum := 0;
212 p_errmesg := 'Success';
213
214 if (p_reload = 0) then -- check if we want to refresh the data
215 -- Check to make sure that if the resource information is already inserted
216 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
217 -- The p_start_date, p_cutoff_date can be modified by
218 -- resource_info_found_in_mrp function if this function
219 -- returns FALSE so that we can get a new from_date to
220 -- call MRP_RHX_RESOURCE_AVAILABILITY
221 if (resource_info_found_in_mrp(p_simulation_set,
222 p_organization_id,
223 x_date_from,
224 x_date_to)) then
225 RETURN;
226 end if;
227 end if;
228
229 -- information not found in MRP, call MRP
230 populate_mrp_avail_res_inst(
231 p_simulation_set => p_simulation_set,
232 p_organization_id => p_organization_id,
233 p_start_date => x_date_from,
234 p_cutoff_date => x_date_to,
235 p_wip_entity_id => p_wip_entity_id);
236
237 RETURN ;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 p_errnum := -1 ;
242 p_errmesg := 'Populate_Resource_Instance_Avails Failed - ' ||
243 to_char(SQLCODE) || ': ' || SQLERRM;
244
245 RETURN ;
246
247 END Populate_Res_Instance_Avails;
248
249
250 PROCEDURE Populate_Res_Instance_Avails
251 (p_simulation_set IN VARCHAR2,
252 p_organization_id IN NUMBER,
253 p_start_date IN DATE,
254 p_cutoff_date IN DATE,
255 p_resource_table IN Number_Tbl_Type,
256 p_dept_table IN Number_Tbl_Type,
257 p_24hour_flag_table IN Number_Tbl_Type,
258 p_instance_table IN Number_Tbl_Type,
259 p_serial_num_table IN Varchar30_Tbl_Type,
260 p_errnum OUT NOCOPY NUMBER,
261 p_errmesg OUT NOCOPY VARCHAR2,
262 p_reload IN NUMBER,
263 p_tbl_size IN NUMBER,
264 p_delete_data IN NUMBER)
265 IS
266 x_date_from DATE := trunc(p_start_date);
267 x_date_to DATE := trunc(p_cutoff_date);
268 x NUMBER := 1;
269
270 BEGIN
271 p_errnum := 0;
272 p_errmesg := 'Success';
273
274 if (p_reload = 0) then -- check if we want to refresh the data
275 -- Check to make sure that if the resource information is already inserted
276 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
277 -- The p_start_date, p_cutoff_date can be modified by
278 -- resource_info_found_in_mrp function if this function
279 -- returns FALSE so that we can get a new from_date to
280 -- call MRP_RHX_RESOURCE_AVAILABILITY
281 if (resource_info_found_in_mrp(p_simulation_set,
282 p_organization_id,
283 x_date_from,
284 x_date_to)) then
285 RETURN;
286 end if;
287 end if;
288
289 -- information not found in MRP, call MRP
290 if(p_delete_data = 1) then
291 delete from mrp_net_resource_avail
292 where organization_id = p_organization_id
293 and simulation_set = p_simulation_set
294 and trunc(shift_date) >= trunc(p_start_date)
295 and trunc(shift_date) <= trunc(p_cutoff_date);
296 end if;
297
298 LOOP
299 EXIT WHEN p_resource_table(x) = 0;
300
301 wps_res_instance_availability.calc_ins_avail(p_organization_id,
302 p_dept_table(x),
303 p_resource_table(x),
304 p_simulation_set,
305 p_instance_table(x),
306 p_serial_num_table(x),
307 p_24hour_flag_table(x),
308 p_start_date,
309 p_cutoff_date);
310
311 x := x+1;
312
313 END LOOP
314
315 RETURN ;
316 EXCEPTION
317 WHEN OTHERS THEN
318 p_errnum := -1 ;
319 p_errmesg := 'Populate_Resource_Avails Failed - ' ||
320 to_char(SQLCODE) || ': ' || SQLERRM;
321
322 RETURN ;
323 END Populate_Res_Instance_Avails;
324
325
326
327
328
329 /*
330 * Procedure that populates the resource availability into
331 * MRP_NET_RESOURCE_AVAIL.
332 */
333 PROCEDURE Populate_Individual_Res_Avails (p_simulation_set IN VARCHAR2,
334 p_organization_id IN NUMBER,
335 p_resource_id IN NUMBER,
336 p_start_date IN DATE,
337 p_cutoff_date IN DATE,
338 p_errnum OUT NOCOPY NUMBER,
339 p_errmesg OUT NOCOPY VARCHAR2,
340 p_reload IN NUMBER,
341 p_department_id IN NUMBER)
342 IS
343 x_date_from DATE := trunc(p_start_date);
344 x_date_to DATE := trunc(p_cutoff_date);
345
346 BEGIN
347
348 p_errnum := 0;
349 p_errmesg := 'Success';
350
351 IF (p_reload <> 0) then
352 -- remove all entries for this resource and repopulate
353 delete from mrp_net_resource_avail
354 where organization_id = p_organization_id
355 and simulation_set = p_simulation_set
356 and resource_id = p_resource_id
357 and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
358 --and trunc(shift_date) >= trunc(p_start_date)
359 --and trunc(shift_date) <= trunc(p_cutoff_date);
360
361 ELSE
362 -- Check to make sure that if the resource information is already inserted
363 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
364 -- The p_start_date, p_cutoff_date can be modified by
365 -- individual_res_info_found_in_mrp function if this function
366 -- returns FALSE so that we can get a new from_date and to_date to
367 -- call MRP_RHX_RESOURCE_AVAILABILITY
368 IF (single_res_info_found_in_mrp(p_simulation_set,
369 p_organization_id,
370 p_resource_id,
371 x_date_from,
372 x_date_to,
373 p_department_id)) THEN
374 RETURN;
375 END IF;
376 END IF;
377
378
379 -- information not found in MRP, call MRP
380 populate_single_mrp_avail_res(p_simulation_set,
381 p_organization_id,
382 p_resource_id,
383 x_date_from,
384 x_date_to,
385 p_department_id);
386
387 RETURN ;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 p_errnum := -1 ;
392 p_errmesg := 'Populate_Individual_Res_Avails Failed - ' ||
393 to_char(SQLCODE) || ': ' || SQLERRM;
394
395 RETURN ;
396
397 END Populate_Individual_Res_Avails ;
398
399 /*
400 * Procedure that populates the resource availability into
401 * MRP_NET_RESOURCE_AVAIL.
402 */
403 PROCEDURE Populate_Individual_Ins_Avails (p_simulation_set IN VARCHAR2,
404 p_organization_id IN NUMBER,
405 p_resource_id IN NUMBER,
406 p_instance_id IN NUMBER,
407 p_serial_number IN VARCHAR2,
408 p_start_date IN DATE,
409 p_cutoff_date IN DATE,
410 p_errnum OUT NOCOPY NUMBER,
411 p_errmesg OUT NOCOPY VARCHAR2,
412 p_reload IN NUMBER,
413 p_department_id IN NUMBER)
414 IS
415 x_date_from DATE := trunc(p_start_date);
416 x_date_to DATE := trunc(p_cutoff_date);
417
418 BEGIN
419
420 p_errnum := 0;
421 p_errmesg := 'Success';
422
423 /*dbms_output.put_line('Populate_Individual_Ins_Avails '||
424 to_char(p_resource_id)|| ': '||
425 to_char(p_instance_id)||': '||
426 p_serial_number||': ' ||
427 to_char(p_start_date)|| '--'||
428 to_char(p_cutoff_date));
429 */
430 IF (p_reload <> 0) then
431 -- remove all entries for this resource and repopulate
432 delete from mrp_net_resource_avail
433 where organization_id = p_organization_id
434 and simulation_set = p_simulation_set
435 and resource_id = p_resource_id
436 and instance_id = p_instance_id
437 and nvl(serial_number,-1) = nvl(p_serial_number,-1)
438 and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
439 --and trunc(shift_date) >= trunc(p_start_date)
440 --and trunc(shift_date) <= trunc(p_cutoff_date);
441
442 ELSE
443 -- Check to make sure that if the resource information is already inserted
444 -- in MRP_NET_RESOURCE_AVAIL, then no need to call MRP again
445 -- The p_start_date, p_cutoff_date can be modified by
446 -- individual_res_info_found_in_mrp function if this function
447 -- returns FALSE so that we can get a new from_date and to_date to
448 -- call MRP_RHX_RESOURCE_AVAILABILITY
449 IF (single_ins_info_found_in_mrp(p_simulation_set,
450 p_organization_id,
451 p_resource_id,
452 p_instance_id,
453 p_serial_number,
454 x_date_from,
455 x_date_to,
456 p_department_id)) THEN
457 -- dbms_output.put_line('record existed in mrp table.');
458 RETURN;
459 END IF;
460 END IF;
461
462
463 -- information not found in MRP, call MRP
464 populate_single_mrp_avail_ins(p_simulation_set,
465 p_organization_id,
466 p_resource_id,
467 p_instance_id,
468 p_serial_number,
469 x_date_from,
470 x_date_to,
471 p_department_id);
472
473 RETURN ;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 p_errnum := -1 ;
478 p_errmesg := 'Populate_Individual_Res_Avails Failed - ' ||
479 to_char(SQLCODE) || ': ' || SQLERRM;
480
481 RETURN ;
482
483 END Populate_Individual_Ins_Avails ;
484
485 /*
486 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
487 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
488 * range specified by p_start_date and p_cutoff_date and for the passed
489 * in simulation_set identifier.
490 */
491 PROCEDURE populate_mrp_avail_resources(p_simulation_set IN varchar2,
492 p_organization_id IN number,
493 p_start_date IN date,
494 p_cutoff_date IN date,
495 p_wip_entity_id IN number)
496 IS
497 x_department_id NUMBER;
498 x_resource_id NUMBER;
499 x_24hr_flag NUMBER;
500
501 cursor dept_res is
502 select dept_res.department_id,
503 dept_res.resource_id,
504 NVL(dept_res.available_24_hours_flag, 2)
505 from bom_department_resources dept_res,
506 bom_departments dept
507 where dept_res.department_id = dept.department_id
508 AND dept_res.share_from_dept_id is null
509 AND dept.organization_id = p_organization_id;
510
511 cursor wip_res is
512 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
513 dept_res.resource_id,
514 NVL(dept_res.available_24_hours_flag, 2)
515 from bom_department_resources dept_res,
516 wip_operations wo,
517 wip_operation_resources wor
518 WHERE wo.wip_entity_id = p_wip_entity_id
519 AND wo.organization_id = p_organization_id
520 AND wor.wip_entity_id = wo.wip_entity_id
521 AND wor.organization_id = wo.organization_id
522 AND wor.operation_seq_num = wo.operation_seq_num
523 AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
524 AND dept_res.resource_id = wor.resource_id
525 union
526 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
527 dept_res.resource_id,
528 NVL(dept_res.available_24_hours_flag, 2)
529 from bom_department_resources dept_res,
530 wip_operations wo,
531 wip_sub_operation_resources wsor
532 WHERE wo.wip_entity_id = p_wip_entity_id
533 AND wo.organization_id = p_organization_id
534 AND wsor.wip_entity_id = wo.wip_entity_id
535 AND wsor.organization_id = wo.organization_id
536 AND wsor.operation_seq_num = wo.operation_seq_num
537 AND dept_res.department_id = nvl(wsor.department_id, wo.department_id)
538 AND dept_res.resource_id = wsor.resource_id
539 union
540 select distinct bdr.department_id department_id,
541 bdr.resource_id,
542 nvl(bdr.available_24_hours_flag, 2)
543 from bom_std_op_resources bsor,
544 bom_standard_operations bso,
545 bom_department_resources bdr,
546 bom_setup_transitions bst,
547 wip_operation_resources wor
548 WHERE wor.organization_id = p_organization_id
549 AND wor.wip_entity_id = p_wip_entity_id
550 AND wor.setup_id is not null
551 AND bst.resource_id = wor.resource_id
552 AND bst.to_setup_id = wor.setup_id
553 AND bso.standard_operation_id = bst.operation_id
554 AND bsor.standard_operation_id = bso.standard_operation_id
555 AND bdr.department_id = bso.department_id
556 AND bdr.resource_id = bsor.resource_id
557 union
558 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
559 dept_res.resource_id,
560 NVL(dept_res.available_24_hours_flag, 2)
561 from bom_department_resources dept_res,
562 wip_sub_operation_resources wsor,
563 bom_setup_transitions bst,
564 bom_standard_operations bso,
565 bom_std_op_resources bsor
566 where wsor.wip_entity_id = p_wip_entity_id
567 and wsor.organization_id = p_organization_id
568 and wsor.setup_id is not null
569 and bst.resource_id = wsor.resource_id
570 and bst.to_setup_id = wsor.setup_id
571 and bso.standard_operation_id = bst.operation_id
572 and bsor.standard_operation_id = bso.standard_operation_id
573 and dept_res.department_id = bso.department_id
574 and dept_res.resource_id = bsor.resource_id;
575
576
577
578 BEGIN
579 -- clean up the table for the date range first
580 delete from mrp_net_resource_avail
581 where organization_id = p_organization_id
582 and simulation_set = p_simulation_set
583 and trunc(shift_date) >= trunc(p_start_date)
584 and trunc(shift_date) <= trunc(p_cutoff_date);
585
586 -- open the cursor and loop through each department resource and call
587 -- MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail to insert resource
588 -- availability information into MRP_NET_RESOURCE_AVAIL
589 IF (p_wip_entity_id IS NULL) THEN
590 OPEN dept_res;
591 LOOP
592 FETCH dept_res into x_department_id,
593 x_resource_id,
594 x_24hr_flag;
595 EXIT WHEN dept_res%NOTFOUND;
596 MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail(p_organization_id,
597 x_department_id,
598 x_resource_id,
599 p_simulation_set,
600 x_24hr_flag,
601 p_start_date,
602 p_cutoff_date);
603 END LOOP;
604 CLOSE dept_res;
605 ELSE
606 OPEN wip_res;
607 LOOP
608 FETCH wip_res into x_department_id,
609 x_resource_id,
610 x_24hr_flag;
611 EXIT WHEN wip_res%NOTFOUND;
612 MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail(p_organization_id,
613 x_department_id,
614 x_resource_id,
615 p_simulation_set,
616 x_24hr_flag,
617 p_start_date,
618 p_cutoff_date);
619 END LOOP;
620 CLOSE wip_res;
621 END IF;
622 END populate_mrp_avail_resources;
623
624
625 /*
626 * Wrapper on top of wps_res_instance_availability.calc_ins_avail.
627 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
628 * range specified by p_start_date and p_cutoff_date and for the passed
629 * in simulation_set identifier.
630 */
631 PROCEDURE populate_mrp_avail_res_inst
632 (p_simulation_set IN varchar2,
633 p_organization_id IN number,
634 p_start_date IN date,
635 p_cutoff_date IN date,
636 p_wip_entity_id IN number)
637 IS
638 x_department_id NUMBER;
639 x_resource_id NUMBER;
640 x_instance_id NUMBER;
641 x_serial_number VARCHAR2(30);
642 x_24hr_flag NUMBER;
643
644 cursor dept_ins is
645 select dept_ins.department_id,
646 NVL(dept_res.available_24_hours_flag, 2),
647 dept_ins.resource_id,
648 dept_ins.instance_id,
649 dept_ins.serial_number
650 from bom_dept_res_instances dept_ins,
651 bom_department_resources dept_res,
652 bom_departments dept
653 where dept_res.department_id = dept.department_id
654 AND dept_res.share_from_dept_id is null
655 AND dept_ins.resource_id = dept_res.resource_id
656 AND dept_ins.department_id = dept_res.department_id
657 AND dept.organization_id = p_organization_id;
658
659
660 cursor wip_res is
661 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
662 NVL(dept_res.available_24_hours_flag, 2),
663 dept_res.resource_id,
664 dept_ins.instance_id,
665 dept_ins.serial_number
666 from bom_department_resources dept_res,
667 bom_dept_res_instances dept_ins,
668 wip_operations wo,
669 wip_operation_resources wor
670 WHERE wo.wip_entity_id = p_wip_entity_id
671 AND wo.organization_id = p_organization_id
672 AND wor.wip_entity_id = wo.wip_entity_id
673 AND wor.organization_id = wo.organization_id
674 AND wor.operation_seq_num = wo.operation_seq_num
675 AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
676 AND dept_res.resource_id = wor.resource_id
677 AND dept_ins.department_id = dept_res.department_id
678 AND dept_ins.resource_id = dept_res.resource_id
679 union
680 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
681 NVL(dept_res.available_24_hours_flag, 2),
682 dept_res.resource_id,
683 ins_changes.instance_id,
684 ins_changes.serial_number
685 from bom_department_resources dept_res,
686 bom_res_instance_changes ins_changes,
687 wip_operations wo,
688 wip_operation_resources wor
689 WHERE wo.wip_entity_id = p_wip_entity_id
690 AND wo.organization_id = p_organization_id
691 AND wor.wip_entity_id = wo.wip_entity_id
692 AND wor.organization_id = wo.organization_id
693 AND wor.operation_seq_num = wo.operation_seq_num
694 AND dept_res.department_id = nvl(wor.department_id, wo.department_id)
695 AND dept_res.resource_id = wor.resource_id
696 AND ins_changes.department_id = dept_res.department_id
697 AND ins_changes.resource_id = dept_res.resource_id
698 AND not exists
699 ( select 1
700 from bom_dept_res_instances dept_ins
701 where dept_ins.department_id = ins_changes.department_id
702 and dept_ins.resource_id = ins_changes.resource_id
703 and dept_ins.instance_id = ins_changes.instance_id
704 and nvl(dept_ins.serial_number, -1) = nvl(ins_changes.serial_number, -1))
705 union
706 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
707 NVL(dept_res.available_24_hours_flag, 2),
708 dept_res.resource_id,
709 dept_ins.instance_id,
710 dept_ins.serial_number
711 from bom_department_resources dept_res,
712 bom_dept_res_instances dept_ins,
713 wip_operations wo,
714 wip_sub_operation_resources wsor
715 WHERE wo.wip_entity_id = p_wip_entity_id
716 AND wo.organization_id = p_organization_id
717 AND wsor.wip_entity_id = wo.wip_entity_id
718 AND wsor.organization_id = wo.organization_id
719 AND wsor.operation_seq_num = wo.operation_seq_num
720 AND dept_res.department_id = nvl(wsor.department_id, wo.department_id)
721 AND dept_res.resource_id = wsor.resource_id
722 AND dept_ins.department_id = dept_res.department_id
723 AND dept_ins.resource_id = dept_res.resource_id
724 union
725 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
726 NVL(dept_res.available_24_hours_flag, 2),
727 dept_res.resource_id,
728 dept_ins.instance_id,
729 dept_ins.serial_number
730 from bom_department_resources dept_res,
731 bom_dept_res_instances dept_ins,
732 wip_operation_resources wor,
733 bom_setup_transitions bst,
734 bom_standard_operations bso,
735 bom_std_op_resources bsor
736 where wor.wip_entity_id = p_wip_entity_id
737 and wor.organization_id = p_organization_id
738 and wor.setup_id is not null
739 and bst.resource_id = wor.resource_id
740 and bst.to_setup_id = wor.setup_id
741 and bso.standard_operation_id = bst.operation_id
742 and bsor.standard_operation_id = bso.standard_operation_id
743 and dept_res.department_id = bso.department_id
744 and dept_res.resource_id = bsor.resource_id
745 AND dept_ins.department_id = dept_res.department_id
746 AND dept_ins.resource_id = dept_res.resource_id
747 union
748 select distinct nvl(dept_res.share_from_dept_id, dept_res.department_id),
749 NVL(dept_res.available_24_hours_flag, 2),
750 dept_res.resource_id,
751 dept_ins.instance_id,
752 dept_ins.serial_number
753 from bom_department_resources dept_res,
754 bom_dept_res_instances dept_ins,
755 wip_sub_operation_resources wsor,
756 bom_setup_transitions bst,
757 bom_standard_operations bso,
758 bom_std_op_resources bsor
759 where wsor.wip_entity_id = p_wip_entity_id
760 and wsor.organization_id = p_organization_id
761 and wsor.setup_id is not null
762 and bst.resource_id = wsor.resource_id
763 and bst.to_setup_id = wsor.setup_id
764 and bso.standard_operation_id = bst.operation_id
765 and bsor.standard_operation_id = bso.standard_operation_id
766 and dept_res.department_id = bso.department_id
767 and dept_res.resource_id = bsor.resource_id
768 AND dept_ins.department_id = dept_res.department_id
769 AND dept_ins.resource_id = dept_res.resource_id;
770
771
772
773 BEGIN
774 -- clean up the table for the date range first
775 delete from mrp_net_resource_avail
776 where organization_id = p_organization_id
777 and simulation_set = p_simulation_set
778 and trunc(shift_date) >= trunc(p_start_date)
779 and trunc(shift_date) <= trunc(p_cutoff_date);
780
781 -- open the cursor and loop through each department resource and call
782 -- MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail to insert resource
783 -- availability information into MRP_NET_RESOURCE_AVAIL
784 IF (p_wip_entity_id IS NULL) THEN
785 OPEN dept_ins;
786 LOOP
787 FETCH dept_ins into x_department_id,
788 x_24hr_flag,
789 x_resource_id,
790 x_instance_id,
791 x_serial_number;
792 EXIT WHEN dept_ins%NOTFOUND;
793 wps_res_instance_availability.calc_ins_avail(p_organization_id,
794 x_department_id,
795 x_resource_id,
796 p_simulation_set,
797 x_instance_id,
798 x_serial_number,
799 x_24hr_flag,
800 p_start_date,
801 p_cutoff_date);
802 END LOOP;
803 CLOSE dept_ins;
804 ELSE
805 OPEN wip_res;
806 LOOP
807 FETCH wip_res into x_department_id,
808 x_24hr_flag,
809 x_resource_id,
810 x_instance_id,
811 x_serial_number;
812 EXIT WHEN wip_res%NOTFOUND;
813
814 wps_res_instance_availability.calc_ins_avail(p_organization_id,
815 x_department_id,
816 x_resource_id,
817 p_simulation_set,
818 x_instance_id,
819 x_serial_number,
820 x_24hr_flag,
821 p_start_date,
822 p_cutoff_date);
823 END LOOP;
824 CLOSE wip_res;
825 END IF;
826 END populate_mrp_avail_res_inst;
827
828
829
830 /*
831 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
832 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
833 * range specified by p_start_date and p_cutoff_date and for the passed
834 * in simulation_set identifier.
835 */
836 PROCEDURE populate_single_mrp_avail_res(p_simulation_set IN varchar2,
837 p_organization_id IN number,
838 p_resource_id IN number,
839 p_start_date IN date,
840 p_cutoff_date IN date,
841 p_department_id IN NUMBER)
842 IS
843 x_department_id NUMBER;
844 x_resource_id NUMBER;
845 x_24hr_flag NUMBER;
846
847 cursor dept_res is
848 select dept_res.department_id,
849 NVL(dept_res.available_24_hours_flag, 2)
850 from bom_department_resources dept_res,
851 bom_departments dept
852 where dept_res.department_id = dept.department_id
853 AND dept_res.resource_id = p_resource_id
854 AND dept_res.share_from_dept_id is null
855 AND dept.organization_id = p_organization_id
856 AND decode(p_department_id,null,-1,dept.department_id) = nvl(p_department_id,-1);
857
858 BEGIN
859 -- open the cursor and loop through each department resource and call
860 -- MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail to insert resource
861 -- availability information into MRP_NET_RESOURCE_AVAIL
862 OPEN dept_res;
863 LOOP
864 FETCH dept_res into x_department_id,
865 x_24hr_flag;
866 EXIT WHEN dept_res%NOTFOUND;
867 MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail(p_organization_id,
868 x_department_id,
869 p_resource_id,
870 p_simulation_set,
871 x_24hr_flag,
872 p_start_date,
873 p_cutoff_date);
874 END LOOP;
875 CLOSE dept_res;
876
877 RETURN;
878
879 END populate_single_mrp_avail_res;
880
881 /*
882 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
883 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
884 * range specified by p_start_date and p_cutoff_date and for the passed
885 * in simulation_set identifier.
886 */
887 PROCEDURE populate_single_mrp_avail_ins(p_simulation_set IN varchar2,
888 p_organization_id IN number,
889 p_resource_id IN number,
890 p_instance_id IN number,
891 p_serial_number IN varchar2,
892 p_start_date IN date,
893 p_cutoff_date IN date,
894 p_department_id IN NUMBER)
895 IS
896 x_department_id NUMBER;
897 x_resource_id NUMBER;
898 x_24hr_flag NUMBER;
899
900 cursor dept_ins is
901 select dept_ins.department_id,
902 NVL(dept_res.available_24_hours_flag, 2)
903 from bom_dept_res_instances dept_ins,
904 bom_department_resources dept_res,
905 bom_departments dept
906 where dept_ins.department_id = dept.department_id
907 AND dept_res.department_id = dept_ins.department_id
908 and dept_res.resource_id = p_resource_id
909 AND dept_ins.resource_id = p_resource_id
910 AND dept_ins.instance_id = p_instance_id
911 AND dept.organization_id = p_organization_id
912 AND decode(p_department_id,null,-1,dept.department_id) = nvl(p_department_id,-1);
913
914 BEGIN
915 -- open the cursor and loop through each department resource and call
916 -- MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail to insert resource
917 -- availability information into MRP_NET_RESOURCE_AVAIL
918 -- dbms_output.put_line('In populate single mrp avail ins.......');
919
920 OPEN dept_ins;
921 LOOP
922 FETCH dept_ins into x_department_id,
923 x_24hr_flag;
924 EXIT WHEN dept_ins%NOTFOUND;
925 wps_res_instance_availability.calc_ins_avail(p_organization_id,
926 x_department_id,
927 p_resource_id,
928 p_simulation_set,
929 p_instance_id,
930 p_serial_number,
931 x_24hr_flag,
932 p_start_date,
933 p_cutoff_date);
934 END LOOP;
935 CLOSE dept_ins;
936
937 RETURN;
938
939 END populate_single_mrp_avail_ins;
940
941 /*
942 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
943 * if the resource availability for the organization is already populated.
944 * If not, returns the p_from_date to the latest date in the table so
945 * that the caller can use the p_date_from and p_date_to to call MRP to
946 * populate the missing data
947 */
948 FUNCTION resource_info_found_in_mrp(p_simulation_set IN VARCHAR2,
949 p_organization_id IN NUMBER,
950 p_date_from IN OUT NOCOPY DATE,
951 p_date_to IN OUT NOCOPY DATE)
952 RETURN BOOLEAN IS
953 max_date_from DATE;
954 max_date_to DATE;
955 status BOOLEAN := TRUE;
956 BEGIN
957
958 -- fetch the max shift_date and min shift_date from mrp_net_resource_avail
959 -- for the specified org and simulation set
960 select trunc(min(shift_date)), trunc(max(shift_date))
961 into max_date_from, max_date_to
962 from mrp_net_resource_avail
963 where organization_id = p_organization_id
964 and simulation_set = p_simulation_set;
965
966 if (max_date_from is NULL) then
967 return FALSE;
968 end if;
969
970 -- compare and see if the passed in start and from date are in the
971 -- mrp_net_resource_avail or not, if not, set p_date_from and p_date_to
972 -- to cover the date range that are not already in the mrp table
973 if (p_date_from < max_date_from) then
974 status := FALSE;
975 if (p_date_to <= max_date_to) then
976 p_date_to := max_date_from-1;
977 end if;
978 elsif (p_date_to > max_date_to) then
979 status := FALSE;
980 p_date_from := max_date_to+1;
981 end if;
982
983 RETURN status;
984
985 END resource_info_found_in_mrp;
986
987 /*
988 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
989 * if the resource availability for the organization is already populated.
990 * If not, returns the p_from_date to the latest date in the table so
991 * that the caller can use the p_date_from and p_date_to to call MRP to
992 * populate the missing data.
993 * Same as resource_info_found_in_mrp but for one resource.
994 */
995 FUNCTION single_res_info_found_in_mrp(p_simulation_set IN VARCHAR2,
996 p_organization_id IN NUMBER,
997 p_resource_id IN NUMBER,
998 p_date_from IN OUT NOCOPY DATE,
999 p_date_to IN OUT NOCOPY DATE,
1000 p_department_id IN NUMBER)
1001 RETURN BOOLEAN IS
1002 max_date_from DATE;
1003 max_date_to DATE;
1004 status BOOLEAN := TRUE;
1005 BEGIN
1006
1007 -- fetch the max shift_date and min shift_date from mrp_net_resource_avail
1008 -- for the specified org and simulation set
1009 select trunc(min(shift_date)), trunc(max(shift_date))
1010 into max_date_from, max_date_to
1011 from mrp_net_resource_avail
1012 where organization_id = p_organization_id
1013 and simulation_set = p_simulation_set
1014 and resource_id = p_resource_id
1015 and instance_id is null
1016 and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
1017
1018 if (max_date_from is NULL) then
1019 return FALSE;
1020 end if;
1021
1022 -- compare and see if the passed in start and from date are in the
1023 -- mrp_net_resource_avail or not, if not, set p_date_from and p_date_to
1024 -- to cover the date range that are not already in the mrp table
1025 if (p_date_from < max_date_from) then
1026 status := FALSE;
1027 if (p_date_to <= max_date_to) then
1028 p_date_to := max_date_from-1;
1029 end if;
1030 elsif (p_date_to > max_date_to) then
1031 status := FALSE;
1032 p_date_from := max_date_to+1;
1033 end if;
1034
1035 RETURN status;
1036
1037 END single_res_info_found_in_mrp;
1038
1039 /*
1040 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
1041 * if the resource availability for the organization is already populated.
1042 * If not, returns the p_from_date to the latest date in the table so
1043 * that the caller can use the p_date_from and p_date_to to call MRP to
1044 * populate the missing data.
1045 * Same as resource_info_found_in_mrp but for one resource.
1046 */
1047 FUNCTION single_ins_info_found_in_mrp(p_simulation_set IN VARCHAR2,
1048 p_organization_id IN NUMBER,
1049 p_resource_id IN NUMBER,
1050 p_instance_id IN NUMBER,
1051 p_serial_number IN VARCHAR2,
1052 p_date_from IN OUT NOCOPY DATE,
1053 p_date_to IN OUT NOCOPY DATE,
1054 p_department_id IN NUMBER)
1055 RETURN BOOLEAN IS
1056 max_date_from DATE;
1057 max_date_to DATE;
1058 status BOOLEAN := TRUE;
1059 BEGIN
1060
1061 -- fetch the max shift_date and min shift_date from mrp_net_resource_avail
1062 -- for the specified org and simulation set
1063 select trunc(min(shift_date)), trunc(max(shift_date))
1064 into max_date_from, max_date_to
1065 from mrp_net_resource_avail
1066 where organization_id = p_organization_id
1067 and simulation_set = p_simulation_set
1068 and resource_id = p_resource_id
1069 and instance_id = p_instance_id
1070 and nvl(serial_number, -1)= nvl(p_serial_number, -1)
1071 and decode(p_department_id,null,-1,department_id) = nvl(p_department_id,-1);
1072
1073 if (max_date_from is NULL) then
1074 return FALSE;
1075 end if;
1076
1077 -- compare and see if the passed in start and from date are in the
1078 -- mrp_net_resource_avail or not, if not, set p_date_from and p_date_to
1079 -- to cover the date range that are not already in the mrp table
1080 if (p_date_from < max_date_from) then
1081 status := FALSE;
1082 if (p_date_to <= max_date_to) then
1083 p_date_to := max_date_from-1;
1084 end if;
1085 elsif (p_date_to > max_date_to) then
1086 status := FALSE;
1087 p_date_from := max_date_to+1;
1088 end if;
1089
1090 RETURN status;
1091
1092 END single_ins_info_found_in_mrp;
1093
1094 PROCEDURE INCREMENT_BATCH_SEQ(NUMBER_OF_NEW_BATCHES NUMBER) IS
1095 dummy NUMBER;
1096 BEGIN
1097 for i in 1..NUMBER_OF_NEW_BATCHES loop
1098 SELECT WIP_PROCESSING_BATCH_S.NEXTVAL INTO dummy FROM DUAL;
1099 end loop;
1100
1101 END increment_batch_seq;
1102
1103
1104 function submit_shopfloor_sched_request
1105 (
1106 p_org_id IN NUMBER,
1107 p_scheduling_mode IN NUMBER,
1108 p_direction IN NUMBER,
1109 p_use_substiture_resource IN NUMBER,
1110 p_entity_type IN NUMBER,
1111 p_firm_window_date IN VARCHAR2,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_msg_count OUT NOCOPY NUMBER,
1114 x_msg_data OUT NOCOPY VARCHAR2
1115 ) return NUMBER
1116 IS
1117 l_request_id NUMBER;
1118
1119 PRAGMA AUTONOMOUS_TRANSACTION;
1120
1121 BEGIN
1122 l_request_id :=
1123 submit_scheduling_request
1124 (
1125 p_org_id => p_org_id,
1126 p_scheduling_mode => p_scheduling_mode, -- 2 all jobs, 3 Pending
1127 p_wip_entity_id => -1, -- all jobs?
1128 p_direction => p_direction, -- 1 backward, 2 forward
1129 p_midpt_operation => '-1',
1130 p_start_date => null,
1131 p_end_date => null,
1132 p_horizon_start => null,
1133 p_horizon_length => -1,
1134 p_resource_constraint => -1,
1135 p_material_constraint => -1,
1136 p_connect_to_comm => '0',
1137 p_ip_address => '',
1138 p_port_number => null,
1139 p_user_id => null,
1140 p_ident => null,
1141 p_use_substiture_resource => p_use_substiture_resource,
1142 p_chosen_operation => '-1',
1143 p_chosen_subset_group => '-1',
1144 p_entity_type => p_entity_type,
1145 p_midpt_op_res => '-1',
1146 p_instance_id => '-1',
1147 p_serial_number => '',
1148 p_firm_window_date => p_firm_window_date,
1149 x_return_status => x_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data
1152 );
1153
1154 commit;
1155 return l_request_id;
1156 END submit_shopfloor_sched_request;
1157
1158 function submit_scheduling_request
1159 (
1160 p_org_id IN NUMBER,
1161 p_scheduling_mode IN NUMBER,
1162 p_wip_entity_id IN NUMBER,
1163 p_direction IN NUMBER,
1164 p_midpt_operation IN VARCHAR2,
1165 p_start_date IN DATE,
1166 p_end_date IN DATE,
1167 p_horizon_start IN DATE,
1168 p_horizon_length IN NUMBER,
1169 p_resource_constraint IN NUMBER,
1170 p_material_constraint IN NUMBER,
1171 p_connect_to_comm IN VARCHAR2,
1172 p_ip_address IN VARCHAR2,
1173 p_port_number IN NUMBER,
1174 p_user_id IN NUMBER,
1175 p_ident IN NUMBER,
1176 p_use_substiture_resource IN NUMBER,
1177 p_chosen_operation IN VARCHAR2,
1178 p_chosen_subset_group IN VARCHAR2,
1179 p_entity_type IN NUMBER,
1180 p_midpt_op_res IN VARCHAR2,
1181 p_instance_id IN VARCHAR2,
1182 p_serial_number IN VARCHAR2,
1183 p_firm_window_date IN VARCHAR2,
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_count OUT NOCOPY NUMBER,
1186 x_msg_data OUT NOCOPY VARCHAR2
1187 ) return NUMBER
1188 IS
1189
1190 g_app_short_name VARCHAR2(10) := 'WPS';
1191 g_scheduer_program VARCHAR2(10) := 'WPCWFS';
1192 g_req_description VARCHAR2(255) := 'Schedule Work Order';
1193
1194 l_request_id NUMBER;
1195 BEGIN
1196
1197 null;
1198 l_request_id :=
1199 FND_REQUEST.SUBMIT_REQUEST
1200 (
1201 g_app_short_name,
1202 g_scheduer_program,
1203 g_req_description,
1204 '',
1205 false,
1206 to_char(p_org_id), -- arg 1:
1207 to_char(p_scheduling_mode),
1208 to_char(p_wip_entity_id),
1209 to_char(p_direction),
1210 p_midpt_operation,
1211 Nvl(fnd_number.number_to_canonical(wip_datetimes.dt_to_float(p_start_date)), '-1'),
1212 Nvl(fnd_number.number_to_canonical(wip_datetimes.dt_to_float(p_end_date)), '-1'),
1213 to_char(Nvl(wip_datetimes.dt_to_float(p_horizon_start),-1)),
1214 to_char(p_horizon_length),
1215 to_char(p_resource_constraint), -- arg 10
1216 to_char(p_material_constraint),
1217 p_connect_to_comm,
1218 p_ip_address,
1219 to_char(p_port_number),
1220 to_char(p_user_id),
1221 to_char(p_ident),
1222 to_char(p_use_substiture_resource),
1223 p_chosen_operation,
1224 p_chosen_subset_group,
1225 to_char(p_entity_type), -- arg 20
1226 p_midpt_op_res,
1227 p_instance_id,
1228 p_serial_number,
1229 p_firm_window_date,
1230 chr(0), -- arg 25, end
1231 '','','','','',
1232 '','','','','','','','','','',
1233 '','','','','','','','','','',
1234 '','','','','','','','','','',
1235 '','','','','','','','','','',
1236 '','','','','','','','','','',
1237 '','','','','','','','','','',
1238 '','','','','','','','','','');
1239 return l_request_id;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 x_msg_data := sqlerrm;
1243 x_return_status := FND_API.g_Ret_Sts_Unexp_Error;
1244 x_msg_count := 1;
1245 END submit_scheduling_request;
1246
1247 function submit_launch_sched_request
1248 (
1249 p_connect_to_comm IN VARCHAR2,
1250 p_ip_address IN VARCHAR2,
1251 p_port_number IN VARCHAR2,
1252 p_user_id IN VARCHAR2,
1253 p_ident IN VARCHAR2,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_count OUT NOCOPY NUMBER,
1256 x_msg_data OUT NOCOPY VARCHAR2
1257 ) return NUMBER
1258 IS
1259
1260 g_app_short_name VARCHAR2(10) := 'WPS';
1261 g_scheduer_program VARCHAR2(10) := 'WPCCBS';
1262 g_req_description VARCHAR2(255) := '';
1263
1264 l_request_id NUMBER;
1265
1266 PRAGMA AUTONOMOUS_TRANSACTION;
1267
1268 BEGIN
1269
1270 null;
1271
1272 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1273 (
1274 g_app_short_name, -- application
1275 g_scheduer_program, -- program
1276 g_req_description, -- description
1277 '', -- start_time
1278 false, -- sub_request
1279 '', -- arg 1
1280 '', -- arg 2
1281 '', -- arg 3
1282 '', -- arg 4
1283 '', -- arg 5
1284 '', -- arg 6
1285 '', -- arg 7
1286 '', -- arg 8
1287 '', -- arg 9
1288 '', -- arg 10
1289 '', -- arg 11
1290 p_connect_to_comm, -- arg 12
1291 p_ip_address, -- arg 13
1292 p_port_number, -- arg 14
1293 p_user_id, -- arg 15
1294 p_ident, -- arg 16
1295 chr(0), -- arg 17
1296 '','','', -- arg 18, 19, 20
1297 '','','','','','','','','','', -- arg 21 to end
1298 '','','','','','','','','','',
1299 '','','','','','','','','','',
1300 '','','','','','','','','','',
1301 '','','','','','','','','','',
1302 '','','','','','','','','','',
1303 '','','','','','','','','','',
1304 '','','','','','','','','','');
1305 commit;
1306 return l_request_id;
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 x_msg_data := sqlerrm;
1310 x_return_status := FND_API.g_Ret_Sts_Unexp_Error;
1311 x_msg_count := 1;
1312 END submit_launch_sched_request;
1313
1314 function get_request_status
1315 (
1316 p_request_id IN NUMBER,
1317 p_app_name IN VARCHAR2,
1318 p_program IN VARCHAR2,
1319 x_request_id OUT NOCOPY NUMBER,
1320 x_phase OUT NOCOPY VARCHAR2,
1321 x_status OUT NOCOPY VARCHAR2,
1322 x_dev_phase OUT NOCOPY VARCHAR2,
1323 x_dev_status OUT NOCOPY VARCHAR2,
1324 x_message OUT NOCOPY VARCHAR2
1325 ) RETURN VARCHAR2
1326 IS
1327 result boolean;
1328 retVal VARCHAR2(2);
1329 begin
1330
1331 x_request_id := p_request_id;
1332 -- Call the function
1333 result := fnd_concurrent.get_request_status(
1334 request_id => x_request_id,
1335 appl_shortname => p_app_name,
1336 program => p_program,
1337 phase => x_phase,
1338 status => x_status,
1339 dev_phase => x_dev_phase,
1340 dev_status => x_dev_status,
1341 message => x_message);
1342
1343 retVal := to_char(sys.diutil.bool_to_int(result));
1344
1345 return retVal;
1346 END get_request_status;
1347
1348
1349 PROCEDURE get_scheduling_param_options
1350 (
1351 x_forward OUT NOCOPY VARCHAR2,
1352 x_backward OUT NOCOPY VARCHAR2,
1353 x_yes OUT NOCOPY VARCHAR2,
1354 x_no OUT NOCOPY VARCHAR2
1355 ) IS
1356
1357 CURSOR c_dir IS
1358 SELECT MEANING
1359 FROM MFG_LOOKUPS
1360 WHERE LOOKUP_TYPE = 'WIP_SCHED_DIRECTION'
1361 AND ENABLED_FLAG = 'Y'
1362 AND sysdate BETWEEN
1363 NVL(START_DATE_ACTIVE, sysdate-1) AND NVL(END_DATE_ACTIVE, sysdate + 1)
1364 AND LOOKUP_CODE IN (1,4)
1365 ORDER BY LOOKUP_CODE;
1366
1367 CURSOR c_yesno IS
1368 SELECT MEANING
1369 FROM MFG_LOOKUPS
1370 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
1371 AND ENABLED_FLAG = 'Y'
1372 AND sysdate BETWEEN
1373 NVL(START_DATE_ACTIVE, sysdate-1) AND NVL(END_DATE_ACTIVE, sysdate + 1)
1374 ORDER BY LOOKUP_CODE;
1375
1376 BEGIN
1377
1378 open c_dir;
1379 fetch c_dir into x_forward;
1380 fetch c_dir into x_backward;
1381 close c_dir;
1382
1383 open c_yesno;
1384 fetch c_yesno into x_yes;
1385 fetch c_yesno into x_no;
1386 close c_yesno;
1387
1388 EXCEPTION WHEN OTHERS THEN
1389 null;
1390 END get_scheduling_param_options;
1391
1392
1393 function job_has_customer(p_wip_entity_id IN NUMBER, p_cust_name IN VARCHAR2)
1394 return VARCHAR2
1395 IS
1396
1397 ret VARCHAR2(1);
1398 BEGIN
1399 ret := 'F';
1400
1401 if (p_cust_name is not null) then
1402 begin
1403 select 'T'
1404 into ret
1405 from dual
1406 where exists (
1407 select 1
1408 from hz_cust_accounts hca, hz_parties hp,
1409 mtl_reservations mr, oe_order_lines_all ool
1410 where mr.demand_source_line_id = ool.line_id
1411 and mr.demand_source_type_id = 2
1412 and mr.supply_source_type_id = 5
1413 and hp.party_name like p_cust_name
1414 and hca.cust_account_id = ool.sold_to_org_id
1415 and hp.party_id = hca.party_id
1416 and mr.supply_source_header_id = p_wip_entity_id
1417 );
1418 exception when others then
1419 null;
1420 end;
1421 end if;
1422 return ret;
1423
1424 end job_has_customer;
1425
1426 function job_has_sales_order(p_wip_entity_id IN NUMBER, p_so_name IN VARCHAR2)
1427 return VARCHAR2
1428 IS
1429
1430 ret VARCHAR2(1);
1431 BEGIN
1432 ret := 'F';
1433
1434 if (p_so_name is not null) then
1435 select 'T'
1436 into ret
1437 from dual
1438 where exists (
1439 select 1
1440 from mtl_reservations mr , mtl_sales_orders mso
1441 where mso.sales_order_id = mr.demand_source_header_id
1442 and mr.demand_source_type_id = 2
1443 and mr.supply_source_type_id = 5
1444 and mso.segment1 like p_so_name
1445 and mr.supply_source_header_id = p_wip_entity_id
1446 );
1447 end if;
1448 return ret;
1449
1450 end job_has_sales_order;
1451
1452 function get_cust_so_info(p_wip_entity_id IN NUMBER)
1453 return VARCHAR2
1454 IS
1455
1456 cust_name VARCHAR2(1024);
1457 so_name VARCHAR2(256);
1458 cust_cnt NUMBER;
1459 so_cnt NUMBER;
1460 so_id NUMBER;
1461 cust_id NUMBER;
1462
1463 BEGIN
1464 cust_name := '';
1465 so_name := '';
1466
1467 select count(distinct ool.sold_to_org_id), count(distinct mr.demand_source_header_id)
1468 into cust_cnt, so_cnt
1469 from mtl_reservations mr, oe_order_lines_all ool, wip_discrete_jobs wdj
1470 where mr.demand_source_line_id = ool.line_id
1471 and mr.demand_source_type_id = 2
1472 and mr.supply_source_type_id = 5
1473 and mr.supply_source_header_id = p_wip_entity_id
1474 and wdj.wip_entity_id = p_wip_entity_id
1475 and wdj.organization_id = mr.organization_id
1476 and mr.inventory_item_id = wdj.primary_item_id;
1477
1478 if ( cust_cnt >0 ) then -- so_cnt should >0 too
1479 select mso.segment1, hp.party_name
1480 into so_name, cust_name
1481 from mtl_reservations mr, oe_order_lines_all ool,
1482 hz_cust_accounts hca, hz_parties hp,
1483 mtl_sales_orders mso
1484 where mr.demand_source_line_id = ool.line_id
1485 and mr.demand_source_type_id = 2
1486 and mr.supply_source_type_id = 5
1487 and hca.cust_account_id = ool.sold_to_org_id
1488 and hp.party_id = hca.party_id
1489 and mso.sales_order_id = mr.demand_source_header_id
1490 and mr.supply_source_header_id = p_wip_entity_id
1491 and rownum = 1;
1492 end if;
1493
1494 return cust_cnt || ESC_CHR || so_cnt || ESC_CHR || cust_name || ESC_CHR || so_name;
1495
1496 end get_cust_so_info;
1497
1498 function cancel_request(request_id in NUMBER,
1499 message out NOCOPY VARCHAR2)
1500 return number IS
1501 success BOOLEAN;
1502
1503 PRAGMA AUTONOMOUS_TRANSACTION;
1504
1505 BEGIN
1506 success := fnd_concurrent.cancel_request(request_id, message);
1507
1508 commit;
1509
1510 if success then
1511 return 1;
1512 else
1513 return 0;
1514 end if;
1515
1516 end cancel_request;
1517
1518
1519
1520
1521 procedure update_scheduling_request_id(p_request_id in NUMBER,
1522 p_wip_entity_id IN NUMBER,
1523 p_organization_id IN NUMBER)
1524 IS
1525 RESOURCE_BUSY EXCEPTION;
1526 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -00054);
1527
1528 cursor lock_curs is
1529 select wip_entity_id
1530 from wip_discrete_jobs
1531 where wip_entity_id = p_wip_entity_id
1532 and organization_id = p_organization_id
1533 for update nowait;
1534
1535 l_wid NUMBER;
1536
1537 begin
1538
1539 select wip_entity_id
1540 into l_wid
1541 from wip_discrete_jobs
1542 where wip_entity_id = p_wip_entity_id
1543 and organization_id = p_organization_id
1544 for update nowait;
1545
1546 --open lock_curs;
1547
1548 --fetch lock_curs into l_wid;
1549
1550 --close lock_curs;
1551
1552 update wip_discrete_jobs
1553 set scheduling_request_id = p_request_id
1554 where wip_entity_id = p_wip_entity_id
1555 and organization_id = p_organization_id;
1556
1557
1558
1559 exception
1560 WHEN RESOURCE_BUSY THEN
1561 --dbms_output.put_line('resource busy wip_id = ' || p_wip_entity_id);
1562 NULL;
1563 when no_data_found then
1564 --dbms_output.put_line('no data found wip_id = ' || p_wip_entity_id);
1565 NULL;
1566 END update_scheduling_request_id;
1567
1568
1569
1570 procedure update_scheduling_request_id(p_request_id in NUMBER,
1571 p_wip_entity_id_table IN Number_Tbl_Type,
1572 p_wip_entity_table_size IN NUMBER,
1573 p_organization_id NUMBER)
1574 IS
1575 i number := 1;
1576
1577 BEGIN
1578
1579 LOOP
1580 EXIT when i > p_wip_entity_table_size;
1581 update_scheduling_request_id(p_request_id,
1582 p_wip_entity_id_table(i),
1583 p_organization_id);
1584 i := i+1;
1585 END LOOP;
1586
1587 END update_scheduling_request_id;
1588
1589
1590 function get_DiscreteJob_Progress(p_wip_entity_id in NUMBER) return NUMBER
1591
1592 IS
1593 progress number := 0;
1594 completed number := 0;
1595 total number := 1;
1596 l_wip_entity_id number := 0;
1597
1598 BEGIN
1599
1600 SELECT SUM(((WO.QUANTITY_COMPLETED+wo.QUANTITY_SCRAPPED)/wo.SCHEDULED_QUANTITY)*(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE)),
1601 SUM(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE) into completed, total
1602 from wip_operations wo,
1603 wip_discrete_jobs wdj
1604 where wdj.wip_entity_id = p_wip_entity_id
1605 and wo.wip_entity_id = wdj.wip_entity_id
1606 and wo.organization_id = wdj.organization_id;
1607
1608 if ( total = 0 ) then
1609 select ((QUANTITY_COMPLETED+ QUANTITY_SCRAPPED)/START_QUANTITY) INTO progress
1610 from wip_discrete_jobs
1611 where wip_entity_id = p_wip_entity_id;
1612 else
1613 progress := completed/total;
1614 end if;
1615
1616 return progress;
1617
1618 END get_DiscreteJob_Progress;
1619
1620
1621
1622 END WPS_Common;
[Home] [Help]