1 PACKAGE BODY WIP_WPS_COMMON AS
2 /* $Header: wipzcomb.pls 120.1 2005/08/26 12:10:42 seli 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 wip_wps_res_instance_avail.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 wip_wps_res_instance_avail.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 wip_wps_res_instance_avail.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 wip_wps_res_instance_avail.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 select 'T'
1403 into ret
1404 from dual
1405 where exists (
1406 select 1
1407 from HZ_CUST_ACCOUNTS cust_accnt, HZ_PARTIES cust_party,
1408 mtl_reservations mr, oe_order_lines_all ool
1409 where mr.demand_source_line_id = ool.line_id
1410 and mr.demand_source_type_id = 2
1411 and mr.supply_source_type_id = 5
1412 and cust_party.party_name like p_cust_name
1413 and cust_accnt.cust_account_id = ool.sold_to_org_id
1414 and cust_party.party_id = cust_accnt.party_id
1415 and mr.supply_source_header_id = p_wip_entity_id
1416 );
1417 end if;
1418 return ret;
1419
1420 end job_has_customer;
1421
1422 function job_has_sales_order(p_wip_entity_id IN NUMBER, p_so_name IN VARCHAR2)
1423 return VARCHAR2
1424 IS
1425
1426 ret VARCHAR2(1);
1427 BEGIN
1428 ret := 'F';
1429
1430 if (p_so_name is not null) then
1431 select 'T'
1432 into ret
1433 from dual
1434 where exists (
1435 select 1
1436 from mtl_reservations mr , mtl_sales_orders mso
1437 where mso.sales_order_id = mr.demand_source_header_id
1438 and mr.demand_source_type_id = 2
1439 and mr.supply_source_type_id = 5
1440 and mso.segment1 like p_so_name
1441 and mr.supply_source_header_id = p_wip_entity_id
1442 );
1443 end if;
1444 return ret;
1445
1446 end job_has_sales_order;
1447
1448 function get_cust_so_info(p_wip_entity_id IN NUMBER)
1449 return VARCHAR2
1450 IS
1451
1452 cust_name VARCHAR2(1024);
1453 so_name VARCHAR2(256);
1454 cust_cnt NUMBER;
1455 so_cnt NUMBER;
1456 so_id NUMBER;
1457 cust_id NUMBER;
1458
1459 BEGIN
1460 cust_name := '';
1461 so_name := '';
1462
1463 select count(distinct ool.sold_to_org_id), count(distinct mr.demand_source_header_id)
1464 into cust_cnt, so_cnt
1465 from mtl_reservations mr, oe_order_lines_all ool, wip_discrete_jobs wdj
1466 where mr.demand_source_line_id = ool.line_id
1467 and mr.demand_source_type_id = 2
1468 and mr.supply_source_type_id = 5
1469 and mr.supply_source_header_id = p_wip_entity_id
1470 and wdj.wip_entity_id = p_wip_entity_id
1471 and wdj.organization_id = mr.organization_id
1472 and mr.inventory_item_id = wdj.primary_item_id;
1473
1474 if ( cust_cnt >0 ) then -- so_cnt should >0 too
1475 select mso.segment1, cust_party.party_name
1476 into so_name, cust_name
1477 from mtl_reservations mr, oe_order_lines_all ool,
1478 hz_cust_accounts cust_accnt, hz_parties cust_party,
1479 mtl_sales_orders mso
1480 where mr.demand_source_line_id = ool.line_id
1481 and mr.demand_source_type_id = 2
1482 and mr.supply_source_type_id = 5
1483 and cust_accnt.cust_account_id = ool.sold_to_org_id
1484 and cust_party.party_id = cust_accnt.party_id
1485 and mso.sales_order_id = mr.demand_source_header_id
1486 and mr.supply_source_header_id = p_wip_entity_id
1487 and rownum = 1;
1488 end if;
1489
1490 return cust_cnt || ESC_CHR || so_cnt || ESC_CHR || cust_name || ESC_CHR || so_name;
1491
1492 end get_cust_so_info;
1493
1494 function cancel_request(request_id in NUMBER,
1495 message out NOCOPY VARCHAR2)
1496 return number IS
1497 success BOOLEAN;
1498
1499 PRAGMA AUTONOMOUS_TRANSACTION;
1500
1501 BEGIN
1502 success := fnd_concurrent.cancel_request(request_id, message);
1503
1504 commit;
1505
1506 if success then
1507 return 1;
1508 else
1509 return 0;
1510 end if;
1511
1512 end cancel_request;
1513
1514
1515
1516
1517 procedure update_scheduling_request_id(p_request_id in NUMBER,
1518 p_wip_entity_id IN NUMBER,
1519 p_organization_id IN NUMBER)
1520 IS
1521 RESOURCE_BUSY EXCEPTION;
1522 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -00054);
1523
1524 cursor lock_curs is
1525 select wip_entity_id
1526 from wip_discrete_jobs
1527 where wip_entity_id = p_wip_entity_id
1528 and organization_id = p_organization_id
1529 for update nowait;
1530
1531 l_wid NUMBER;
1532
1533 begin
1534
1535 select wip_entity_id
1536 into l_wid
1537 from wip_discrete_jobs
1538 where wip_entity_id = p_wip_entity_id
1539 and organization_id = p_organization_id
1540 for update nowait;
1541
1542 --open lock_curs;
1543
1544 --fetch lock_curs into l_wid;
1545
1546 --close lock_curs;
1547
1548 update wip_discrete_jobs
1549 set scheduling_request_id = p_request_id
1550 where wip_entity_id = p_wip_entity_id
1551 and organization_id = p_organization_id;
1552
1553
1554
1555 exception
1556 WHEN RESOURCE_BUSY THEN
1557 --dbms_output.put_line('resource busy wip_id = ' || p_wip_entity_id);
1558 NULL;
1559 when no_data_found then
1560 --dbms_output.put_line('no data found wip_id = ' || p_wip_entity_id);
1561 NULL;
1562 END update_scheduling_request_id;
1563
1564
1565
1566 procedure update_scheduling_request_id(p_request_id in NUMBER,
1567 p_wip_entity_id_table IN Number_Tbl_Type,
1568 p_wip_entity_table_size IN NUMBER,
1569 p_organization_id NUMBER)
1570 IS
1571 i number := 1;
1572
1573 BEGIN
1574
1575 LOOP
1576 EXIT when i > p_wip_entity_table_size;
1577 update_scheduling_request_id(p_request_id,
1578 p_wip_entity_id_table(i),
1579 p_organization_id);
1580 i := i+1;
1581 END LOOP;
1582
1583 END update_scheduling_request_id;
1584
1585
1586 function get_DiscreteJob_Progress(p_wip_entity_id in NUMBER) return NUMBER
1587
1588 IS
1589 progress number := 0;
1590 completed number := 0;
1591 total number := 1;
1592 start_qty number := 0;
1593 l_wip_entity_id number := 0;
1594
1595 BEGIN
1596
1597 select start_quantity into start_qty
1598 from wip_discrete_jobs
1599 where wip_entity_id = p_wip_entity_id;
1600
1601 if (start_qty = 0) then
1602 progress := 0;
1603 return progress;
1604 end if;
1605
1606 SELECT SUM(((WO.QUANTITY_COMPLETED+wo.QUANTITY_SCRAPPED)/wo.SCHEDULED_QUANTITY)*(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE)),
1607 SUM(wo.LAST_UNIT_COMPLETION_DATE - wo.FIRST_UNIT_START_DATE) into completed, total
1608 from wip_operations wo,
1609 wip_discrete_jobs wdj
1610 where wdj.wip_entity_id = p_wip_entity_id
1611 and wo.wip_entity_id = wdj.wip_entity_id
1612 and wo.organization_id = wdj.organization_id;
1613
1614 if ( total = 0 ) then
1615 select ((QUANTITY_COMPLETED+ QUANTITY_SCRAPPED)/START_QUANTITY) INTO progress
1616 from wip_discrete_jobs
1617 where wip_entity_id = p_wip_entity_id;
1618 else
1619 progress := completed/total;
1620 end if;
1621
1622 return progress;
1623
1624 END get_DiscreteJob_Progress;
1625
1626
1627
1628 procedure update_wip_op_resource -- for wip table
1629 (
1630 p_wip_entity_id IN NUMBER,
1631 p_operation_seq_num IN NUMBER,
1632 p_resource_seq_num IN NUMBER,
1633 p_new_start_date IN DATE,
1634 p_new_completion_date IN DATE,
1635 x_status OUT NOCOPY VARCHAR2,
1636 x_msg_count OUT NOCOPY NUMBER,
1637 x_msg_data OUT NOCOPY VARCHAR2
1638 )
1639 IS
1640
1641 l_old_start_date DATE;
1642 l_old_completion_date DATE;
1643 l_old_duration NUMBER;
1644
1645 l_new_start_date DATE;
1646 l_new_completion_date DATE;
1647
1648 l_sched_seq_num NUMBER;
1649
1650 l_offset NUMBER;
1651
1652 BEGIN
1653 select wor.start_date, wor.completion_date
1654 into l_old_start_date, l_old_completion_date
1655 from wip_operation_resources wor
1656 where wor.wip_entity_id = p_wip_entity_id
1657 and wor.operation_seq_num = p_operation_seq_num
1658 and wor.resource_seq_num = p_resource_seq_num
1659 for update;
1660
1661 if( l_old_start_date = p_new_start_date and
1662 l_old_completion_date = p_new_completion_date ) then
1663 return;
1664 end if;
1665
1666 select wor.schedule_seq_num
1667 into l_sched_seq_num
1668 from wip_operation_resources wor
1669 where wor.wip_entity_id = p_wip_entity_id
1670 and wor.operation_seq_num = p_operation_seq_num
1671 and wor.resource_seq_num = p_resource_seq_num;
1672
1673 if( l_sched_seq_num is null) then
1674 -- update op resource
1675 update wip_operation_resources wor
1676 set wor.start_date = p_new_start_date,
1677 wor.completion_date = p_new_completion_date,
1678 wor.last_update_date = sysdate
1679 where wor.wip_entity_id = p_wip_entity_id
1680 and wor.operation_seq_num = p_operation_seq_num
1681 and wor.resource_seq_num = p_resource_seq_num;
1682 -- delete usage
1683 delete wip_operation_resource_usage woru
1684 where woru.wip_entity_id = p_wip_entity_id
1685 and woru.operation_seq_num = p_operation_seq_num
1686 and woru.resource_seq_num = p_resource_seq_num;
1687 -- update instances if any
1688 update wip_op_resource_instances wori
1689 set wori.start_date = p_new_start_date,
1690 wori.completion_date = p_new_completion_date,
1691 wori.last_update_date = sysdate
1692 where wori.wip_entity_id = p_wip_entity_id
1693 and wori.operation_seq_num = p_operation_seq_num
1694 and wori.resource_seq_num = p_resource_seq_num;
1695 else -- for simutaneous resources
1696 l_offset := p_new_start_date - l_old_start_date;
1697 -- yl_debug.dump('offset=' || l_offset || ' subgrpnum=' || l_sub_grp_num);
1698 -- update op resources
1699 update wip_operation_resources wor
1700 set wor.start_date = wor.start_date + l_offset,
1701 wor.completion_date = wor.completion_date + l_offset,
1702 wor.last_update_date = sysdate
1703 where wor.wip_entity_id = p_wip_entity_id
1704 and wor.operation_seq_num = p_operation_seq_num
1705 and wor.schedule_seq_num = l_sched_seq_num;
1706 -- delete usages
1707 delete wip_operation_resource_usage woru
1708 where woru.wip_entity_id = p_wip_entity_id
1709 and woru.operation_seq_num = p_operation_seq_num
1710 and woru.resource_seq_num in
1711 ( select wor.resource_seq_num from wip_operation_resources wor
1712 where wor.wip_entity_id = p_wip_entity_id
1713 and wor.operation_seq_num = p_operation_seq_num
1714 and wor.schedule_seq_num = l_sched_seq_num
1715 );
1716 -- update instances if any
1717 update wip_op_resource_instances wori
1718 set wori.start_date = wori.start_date + l_offset,
1719 wori.completion_date = wori.completion_date + l_offset,
1720 wori.last_update_date = sysdate
1721 where wori.wip_entity_id = p_wip_entity_id
1722 and wori.operation_seq_num in
1723 ( select wor.resource_seq_num from wip_operation_resources wor
1724 where wor.wip_entity_id = p_wip_entity_id
1725 and wor.operation_seq_num = p_operation_seq_num
1726 and wor.schedule_seq_num = l_sched_seq_num
1727 );
1728 end if;
1729
1730
1731 /* check if operation start/completion dates need to be updated also */
1732 /* here asume the first/last unit will be the same ??*/
1733 select wo.first_unit_start_date, wo.last_unit_completion_date
1734 into l_old_start_date, l_old_completion_date
1735 from wip_operations wo
1736 where wo.wip_entity_id = p_wip_entity_id
1737 and wo.operation_seq_num = p_operation_seq_num
1738 for update;
1739
1740 if( l_old_start_date > p_new_start_date or
1741 l_old_completion_date < p_new_completion_date ) then
1742 -- op need to be updated
1743 if( l_old_start_date < p_new_start_date ) then
1744 l_new_start_date := l_old_start_date;
1745 else
1746 l_new_start_date := p_new_start_date;
1747 end if;
1748
1749 if( l_old_completion_date > p_new_completion_date ) then
1750 l_new_completion_date := l_old_completion_date;
1751 else
1752 l_new_completion_date := p_new_completion_date;
1753 end if;
1754
1755 update wip_operations wo
1756 set wo.first_unit_start_date = l_new_start_date,
1757 wo.last_unit_start_date = l_new_start_date,
1758 wo.first_unit_completion_date = l_new_completion_date,
1759 wo.last_unit_completion_date = l_new_completion_date,
1760 wo.last_update_date = sysdate
1761 where wo.wip_entity_id = p_wip_entity_id
1762 and wo.operation_seq_num = p_operation_seq_num;
1763
1764 /* check if job needs to be updated */
1765 select wdj.scheduled_start_date, wdj.scheduled_completion_date
1766 into l_old_start_date, l_old_completion_date
1767 from wip_discrete_jobs wdj
1768 where wdj.wip_entity_id = p_wip_entity_id
1769 for update;
1770
1771 if( l_old_start_date > p_new_start_date or
1772 l_old_completion_date < p_new_completion_date ) then
1773
1774 if( l_old_start_date < p_new_start_date ) then
1775 l_new_start_date := l_old_start_date;
1776 else
1777 l_new_start_date := p_new_start_date;
1778 end if;
1779
1780 if( l_old_completion_date > p_new_completion_date ) then
1781 l_new_completion_date := l_old_completion_date;
1782 else
1783 l_new_completion_date := p_new_completion_date;
1784 end if;
1785
1786 update wip_discrete_jobs wdj
1787 set wdj.scheduled_start_date = l_new_start_date,
1788 wdj.scheduled_completion_date = l_new_completion_date,
1789 wdj.last_update_date = sysdate
1790 where wdj.wip_entity_id = p_wip_entity_id;
1791
1792 end if;
1793 end if;
1794
1795 END update_wip_op_resource;
1796
1797 procedure update_wsm_copy_op_resource -- for wip table
1798 (
1799 p_wip_entity_id IN NUMBER,
1800 p_operation_seq_num IN NUMBER,
1801 p_resource_seq_num IN NUMBER,
1802 p_new_start_date IN DATE,
1803 p_new_completion_date IN DATE,
1804 x_status OUT NOCOPY VARCHAR2,
1805 x_msg_count OUT NOCOPY NUMBER,
1806 x_msg_data OUT NOCOPY VARCHAR2
1807 )
1808 IS
1809
1810 l_old_start_date DATE;
1811 l_old_completion_date DATE;
1812 l_old_duration NUMBER;
1813
1814 l_new_start_date DATE;
1815 l_new_completion_date DATE;
1816
1817 l_sched_seq_num NUMBER;
1818
1819 l_offset NUMBER;
1820
1821 BEGIN
1822 select wcor.reco_start_date, wcor.reco_completion_date
1823 into l_old_start_date, l_old_completion_date
1824 from wsm_copy_op_resources wcor
1825 where wcor.wip_entity_id = p_wip_entity_id
1826 and wcor.operation_seq_num = p_operation_seq_num
1827 and wcor.resource_seq_num = p_resource_seq_num
1828 for update;
1829
1830 if( l_old_start_date = p_new_start_date and
1831 l_old_completion_date = p_new_completion_date ) then
1832 return;
1833 end if;
1834
1835 --yl_debug.dump('Updating op resource...');
1836 select wcor.schedule_seq_num
1837 into l_sched_seq_num
1838 from wsm_copy_op_resources wcor
1839 where wcor.wip_entity_id = p_wip_entity_id
1840 and wcor.operation_seq_num = p_operation_seq_num
1841 and wcor.resource_seq_num = p_resource_seq_num;
1842
1843 if( l_sched_seq_num is null) then
1844 -- update op resource
1845 update wsm_copy_op_resources wcor
1846 set wcor.reco_start_date = p_new_start_date,
1847 wcor.reco_completion_date = p_new_completion_date,
1848 wcor.last_update_date = sysdate
1849 where wcor.wip_entity_id = p_wip_entity_id
1850 and wcor.operation_seq_num = p_operation_seq_num
1851 and wcor.resource_seq_num = p_resource_seq_num;
1852 -- delete usage
1853 delete wsm_copy_op_resource_usage wcoru
1854 where wcoru.wip_entity_id = p_wip_entity_id
1855 and wcoru.operation_seq_num = p_operation_seq_num
1856 and wcoru.resource_seq_num = p_resource_seq_num;
1857 -- update instances if any
1858 update wsm_copy_op_resource_instances wcori
1859 set wcori.start_date = p_new_start_date,
1860 wcori.completion_date = p_new_completion_date,
1861 wcori.last_update_date = sysdate
1862 where wcori.wip_entity_id = p_wip_entity_id
1863 and wcori.operation_seq_num = p_operation_seq_num
1864 and wcori.resource_seq_num = p_resource_seq_num;
1865 else
1866 l_offset := p_new_start_date - l_old_start_date;
1867 -- update operation resources
1868 update wsm_copy_op_resources wcor
1869 set wcor.reco_start_date = wcor.reco_start_date + l_offset,
1870 wcor.reco_completion_date = wcor.reco_completion_date + l_offset,
1871 wcor.last_update_date = sysdate
1872 where wcor.wip_entity_id = p_wip_entity_id
1873 and wcor.operation_seq_num = p_operation_seq_num
1874 and wcor.schedule_seq_num = l_sched_seq_num
1875 and wcor.recommended = 'Y';
1876 -- delete usages
1877 delete wsm_copy_op_resource_usage wcoru
1878 where wcoru.wip_entity_id = p_wip_entity_id
1879 and wcoru.operation_seq_num = p_operation_seq_num
1880 and wcoru.resource_seq_num in
1881 ( select wcor.resource_seq_num
1882 from wsm_copy_op_resources wcor
1883 where wcor.wip_entity_id = p_wip_entity_id
1884 and wcor.operation_seq_num = p_operation_seq_num
1885 and wcor.schedule_seq_num = l_sched_seq_num
1886 );
1887 /* update instances if have any */
1888 update wsm_copy_op_resource_instances wcori
1889 set wcori.start_date = wcori.start_date + l_offset,
1890 wcori.completion_date = wcori.completion_date + l_offset,
1891 wcori.last_update_date = sysdate
1892 where wcori.wip_entity_id = p_wip_entity_id
1893 and wcori.operation_seq_num = p_operation_seq_num
1894 and wcori.resource_seq_num in
1895 ( select wcor.resource_seq_num
1896 from wsm_copy_op_resources wcor
1897 where wcor.wip_entity_id = p_wip_entity_id
1898 and wcor.operation_seq_num = p_operation_seq_num
1899 and wcor.schedule_seq_num = l_sched_seq_num
1900 );
1901 end if;
1902
1903
1904 /* check if operation start/completion dates need to be updated also */
1905 /* here asume the first/last unit will be the same ??*/
1906 select wco.reco_start_date, wco.reco_completion_date
1907 into l_old_start_date, l_old_completion_date
1908 from wsm_copy_operations wco
1909 where wco.wip_entity_id = p_wip_entity_id
1910 and wco.operation_seq_num = p_operation_seq_num
1911 for update;
1912
1913 if( l_old_start_date > p_new_start_date or
1914 l_old_completion_date < p_new_completion_date ) then
1915 -- op need to be updated
1916 if( l_old_start_date < p_new_start_date ) then
1917 l_new_start_date := l_old_start_date;
1918 else
1919 l_new_start_date := p_new_start_date;
1920 end if;
1921
1922 if( l_old_completion_date > p_new_completion_date ) then
1923 l_new_completion_date := l_old_completion_date;
1924 else
1925 l_new_completion_date := p_new_completion_date;
1926 end if;
1927
1928 update wsm_copy_operations wco
1929 set wco.reco_start_date = l_new_start_date,
1930 wco.reco_completion_date = l_new_completion_date,
1931 wco.last_update_date = sysdate
1932 where wco.wip_entity_id = p_wip_entity_id
1933 and wco.operation_seq_num = p_operation_seq_num;
1934
1935 /* check if job needs to be updated */
1936 select wdj.scheduled_start_date, wdj.scheduled_completion_date
1937 into l_old_start_date, l_old_completion_date
1938 from wip_discrete_jobs wdj
1939 where wdj.wip_entity_id = p_wip_entity_id
1940 for update;
1941
1942 if( l_old_start_date > p_new_start_date or
1943 l_old_completion_date < p_new_completion_date ) then
1944
1945 if( l_old_start_date < p_new_start_date ) then
1946 l_new_start_date := l_old_start_date;
1947 else
1948 l_new_start_date := p_new_start_date;
1949 end if;
1950
1951 if( l_old_completion_date > p_new_completion_date ) then
1952 l_new_completion_date := l_old_completion_date;
1953 else
1954 l_new_completion_date := p_new_completion_date;
1955 end if;
1956
1957 update wip_discrete_jobs wdj
1958 set wdj.scheduled_start_date = l_new_start_date,
1959 wdj.scheduled_completion_date = l_new_completion_date,
1960 wdj.last_update_date = sysdate
1961 where wdj.wip_entity_id = p_wip_entity_id;
1962
1963 end if;
1964 end if;
1965
1966 END update_wsm_copy_op_resource;
1967
1968
1969 PROCEDURE update_operation_resource
1970 (
1971 p_entity_type IN NUMBER,
1972 p_source IN NUMBER,
1973 p_wip_entity_id IN NUMBER,
1974 p_operation_seq_num IN NUMBER,
1975 p_resource_seq_num IN NUMBER,
1976 p_new_start_date IN DATE,
1977 p_new_completion_date IN DATE,
1978 x_status OUT NOCOPY VARCHAR2,
1979 x_msg_count OUT NOCOPY NUMBER,
1980 x_msg_data OUT NOCOPY VARCHAR2
1981 )
1982 IS
1983
1984 l_old_start_date DATE;
1985 l_old_completion_date DATE;
1986 l_old_duration NUMBER;
1987
1988 l_new_start_date DATE;
1989 l_new_completion_date DATE;
1990 Begin
1991
1992 if (p_new_start_date is null or p_new_completion_date is null) then
1993 return;
1994 end if;
1995
1996 if( p_entity_type = JOB_TYPE_DISCRETE ) then -- discrete
1997 update_wip_op_resource(
1998 p_wip_entity_id,
1999 p_operation_seq_num,
2000 p_resource_seq_num,
2001 p_new_start_date,
2002 p_new_completion_date,
2003 x_status,
2004 x_msg_count,
2005 x_msg_data);
2006
2007 elsif ( p_entity_type = JOB_TYPE_WSM ) then --osfm
2008
2009 if(p_source = WSM_DATA_SOURCE_EXE ) then
2010 update_wip_op_resource(
2011 p_wip_entity_id,
2012 p_operation_seq_num,
2013 p_resource_seq_num,
2014 p_new_start_date,
2015 p_new_completion_date,
2016 x_status,
2017 x_msg_count,
2018 x_msg_data);
2019 else
2020 update_wsm_copy_op_resource(
2021 p_wip_entity_id,
2022 p_operation_seq_num,
2023 p_resource_seq_num,
2024 p_new_start_date,
2025 p_new_completion_date,
2026 x_status,
2027 x_msg_count,
2028 x_msg_data);
2029 end if;
2030 end if;
2031
2032 End update_operation_resource;
2033
2034
2035 FUNCTION get_component_on_hand
2036 (
2037 p_organization_id IN NUMBER,
2038 p_inventory_item_id IN NUMBER
2039 ) return NUMBER
2040 IS
2041 CURSOR qoh_net_cur IS
2042 SELECT NVL(SUM(QUANTITY),0)
2043 FROM MTL_SECONDARY_INVENTORIES MSS,
2044 MTL_ITEM_QUANTITIES_VIEW MOQ,
2045 MTL_SYSTEM_ITEMS MSI
2046 WHERE MOQ.ORGANIZATION_ID = p_organization_id
2047 AND MSI.ORGANIZATION_ID = p_organization_id
2048 AND MSS.ORGANIZATION_ID = p_organization_id
2049 AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
2050 AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
2051 AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
2052 AND MSS.AVAILABILITY_TYPE = 1;
2053 CURSOR qoh_all_cur IS
2054 SELECT NVL(SUM(QUANTITY),0)
2055 FROM MTL_ITEM_QUANTITIES_VIEW MOQ
2056 WHERE MOQ.ORGANIZATION_ID = p_organization_id
2057 AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id;
2058
2059 l_on_hand NUMBER := 0;
2060 sub_inv_option NUMBER;
2061 BEGIN
2062
2063 sub_inv_option := FND_PROFILE.VALUE('WIP_REQUIREMENT_ATT_OPTION');
2064
2065 if( sub_inv_option = 1 ) then -- net
2066 OPEN qoh_net_cur;
2067 FETCH qoh_net_cur INTO l_on_hand;
2068 CLOSE qoh_net_cur;
2069 elsif ( sub_inv_option = 2 ) then
2070 OPEN qoh_all_cur;
2071 FETCH qoh_all_cur INTO l_on_hand;
2072 CLOSE qoh_all_cur;
2073 end if;
2074
2075 return l_on_hand;
2076
2077 END get_component_on_hand;
2078
2079
2080
2081 END WIP_WPS_Common;
[Home] [Help]