1 PACKAGE BODY WIP_MRP_RELIEF AS
2 /* $Header: wipmrpb.pls 115.13 2003/07/14 14:15:49 soroy ship $ */
3
4
5 PROCEDURE WIP_DISCRETE_JOBS_PROC
6 (item_id IN NUMBER,
7 org_id IN NUMBER,
8 last_upd_date IN DATE,
9 last_upd_by IN NUMBER,
10 creat_date IN DATE,
11 creat_by IN NUMBER,
12 new_mps_quantity IN NUMBER,
13 old_mps_quantity IN NUMBER,
14 new_sched_compl_date IN DATE,
15 old_sched_compl_date IN DATE,
16 wip_enty_id IN NUMBER,
17 srce_code IN VARCHAR2,
18 srce_line_id IN NUMBER,
19 new_bill_desig IN VARCHAR2,
20 old_bill_desig IN VARCHAR2,
21 new_bill_rev_date IN DATE,
22 old_bill_rev_date IN DATE,
23 new_dmd_class IN VARCHAR2,
24 old_dmd_class IN VARCHAR2,
25 new_status_type IN NUMBER,
26 old_status_type IN NUMBER,
27 new_qty_completed IN NUMBER,
28 old_qty_completed IN NUMBER,
29 new_date_completed IN DATE,
30 old_date_completed IN DATE,
31 new_project_id IN NUMBER,
32 old_project_id IN NUMBER,
33 new_task_id IN NUMBER,
34 old_task_id IN NUMBER) IS
35
36 new_qty NUMBER;
37 old_qty NUMBER;
38 new_date DATE;
39 old_date DATE;
40
41 wip_req_ops_rows NUMBER;
42
43 BEGIN
44
45 /*------- Determine quantities and date for the insert ------------------*/
46 IF INSERTING THEN
47
48 /*------------------------------------------------------------+
49 | Insert - relieve if status is "effective" (procedure will
50 | only be called in these cases due to trigger's WHEN clause) |
51 +-------------------------------------------------------------*/
52 new_qty := NVL(new_mps_quantity,0);
53 old_qty := null;
54 new_date := new_sched_compl_date;
55 old_date := null;
56
57 ELSIF DELETING THEN
58
59 /* Note that we are dropping the delete trigger for Prod16, rel10.7
60 11/18/96 (djoffe) I am leaving this logic here for historical
61 purposes. */
62
63 /*------------------------------------------------------------+
64 | Delete - unrelieve if status is "effective" (procedure will |
65 | only be called in these cases due to trigger's WHEN clause) |
66 +-------------------------------------------------------------*/
67 new_qty := NVL(LEAST(old_mps_quantity, old_qty_completed),0);
68 old_qty := old_mps_quantity;
69 new_date := SYSDATE;
70 old_date := NVL(old_date_completed,old_sched_compl_date);
71
72 ELSIF UPDATING THEN
73
74 /*------------------------------------------------------------+
75 | |
76 | Update - Changes to status_type are only important if they |
77 | move between an "effective" status (1,3,4,5,6) and a |
78 | "non-effective" status (7 - 15). |
79 | |
80 | * Updates within a "non-effective" status should be |
81 | ignored |
82 | |
83 | * Updates from an "effective" status to a |
84 | "non-effective" status should be treated as deletes |
85 | |
86 | * Updates from an "non-effective" status to a |
87 | "effective" status should be treated as inserts |
88 | |
89 | * Updated within an "effective" status should ignore |
90 | the status and pass the other changed values to the |
91 | mrp_relief_interface table |
92 | |
93 +------------------------------------------------------------*/
94
95 /* NOTE - changes within a "non-effective" status have already been
96 screened out */
97
98 /* Changes from an "effective" status to a "non-effective" status */
99 IF (old_status_type < 7 AND new_status_type > 6) THEN
100 old_qty := old_mps_quantity;
101 new_qty := NVL(LEAST(old_qty, old_qty_completed), 0);
102 old_date := NVL(old_date_completed, old_sched_compl_date);
103 new_date := LEAST(TRUNC(SYSDATE), old_date);
104 IF (old_qty = new_qty) AND (old_date = new_date) THEN
105 return;
106 END IF;
107
108 /* Changes from an "non-effective" status to a "effective" status */
109 ELSIF (old_status_type > 6 AND new_status_type < 7) THEN
110 new_qty := NVL(new_mps_quantity,0);
111 old_qty := null;
112 new_date := NVL(new_date_completed,new_sched_compl_date);
113 old_date := null;
114
115 /* Changes within an "effective" status */
116 ELSIF (old_status_type < 7 AND new_status_type < 7) THEN
117 new_qty := NVL(new_mps_quantity, 0);
118 old_qty := old_mps_quantity;
119 new_date := TRUNC(NVL(new_date_completed, new_sched_compl_date));
120 old_date := TRUNC(NVL(old_date_completed, old_sched_compl_date));
121 /*--------------------------------------------------------------+
122 | This If stmt prevents us from inserting 2 rows into the |
123 | mrp interface table. Since Forms 2.3 can't handle |
124 | date/time in one field, but the db can, we update |
125 | wip_discrete_jobs on fnd_post_insert and fnd_post_update |
126 | to combine the form's date and time fields into 1 db column |
127 | This would cause the trigger to fire twice. |
128 +--------------------------------------------------------------+*/
129 IF (new_qty = old_qty AND new_date = old_date AND
130 NVL(new_dmd_class,'NONEXISTENT') =
131 NVL(old_dmd_class,'NONEXISTENT') AND
132 NVL(new_bill_desig, 'NONEXISTENT') =
133 NVL(old_bill_desig, 'NONEXISTENT') AND
134 NVL(old_project_id, -111) =
135 NVL(new_project_id, -111) AND
136 NVL(old_task_id, -111) =
137 NVL(new_task_id, -111) ) THEN
138 /*-----------------------------------------------------------------+
139 | This If stmt checks to see if any rows changes in WIP_REQ_OPS. |
140 | If the BILL_REVISION_DATE, ALT_BILL_DESIG, OR BILL_REF changed |
141 | for an unreleased job, the old bill was deleted and a new bill |
142 | was exploded. Unfortunately, because of the date/time problem |
143 | described above (in this case, for BILL_REVISION_DATE), we |
144 | must actually check and see if any requirements were changed. |
145 +-----------------------------------------------------------------+*/
146 SELECT COUNT(*)
147 INTO wip_req_ops_rows
148 FROM WIP_REQUIREMENT_OPERATIONS
149 WHERE WIP_ENTITY_ID = wip_enty_id
150 AND ORGANIZATION_ID = org_id
151 AND (NVL(MPS_REQUIRED_QUANTITY,required_quantity+1) <>
152 required_quantity
153 OR NVL(MPS_DATE_REQUIRED,date_required+1) <> date_required);
154
155 IF wip_req_ops_rows = 0 THEN
156 return;
157 END IF;
158
159 END IF;
160
161 ELSE
162 return; -- if old/new status not in above categories
163 END IF;
164
165 END IF;
166
167 /* MPS relief for assembly */
168 INSERT INTO mrp_relief_interface
169 (inventory_item_id, -- NN
170 organization_id, -- NN
171 last_update_date, -- NN sysdate
172 last_updated_by, -- NN :new.last_updated_by
173 creation_date, -- NN sysdate
174 created_by, -- NN :new.created_by
175 last_update_login, -- N -1
176 new_order_quantity, -- NN
177 old_order_quantity, -- N
178 new_order_date, -- NN
179 old_order_date, -- N
180 disposition_id, -- NN :new.wip_entity_id
181 planned_order_id, -- N :new.source_code,'MRP',
182 relief_type, -- NN 2
183 disposition_type, -- NN 1
184 demand_class, -- N
185 old_demand_class, -- N
186 line_num, -- N null
187 request_id, -- N null
188 program_application_id, -- N null
189 program_id, -- N null
190 program_update_date, -- N null
191 process_status, -- NN 2
192 source_code, -- N 'WIP'
193 source_line_id, -- N null
194 error_message, -- N null
195 transaction_id, -- NN
196 project_id,
197 old_project_id,
198 task_id,
199 old_task_id
200 )
201 VALUES (item_id,
202 org_id,
203 last_upd_date,
204 last_upd_by,
205 creat_date,
206 creat_by,
207 -1,
208 new_qty,
209 old_qty,
210 new_date,
211 old_date,
212 wip_enty_id,
213 DECODE(srce_code, 'MRP', srce_line_id, null),
214 2,
215 1,
216 new_dmd_class,
217 old_dmd_class,
218 null,
219 null,
220 null,
221 null,
222 null,
223 2,
224 'WIP',
225 null,
226 null,
227 mrp_relief_interface_s.nextval,
228 new_project_id,
229 old_project_id,
230 new_task_id,
231 old_task_id
232 );
233
234 /* Bug 3030600 - Will consider DATE_REQUIRED as new_date for phantom
235 components, except for Deletion case. Here keeping
236 new_date as SYSDATE as before */
237 IF DELETING THEN
238 /* MPS relief for phantom components */
239 INSERT INTO mrp_relief_interface
240 (inventory_item_id, -- NN
241 organization_id, -- NN
242 last_update_date, -- NN sysdate
243 last_updated_by, -- NN :new.last_updated_by
244 creation_date, -- NN sysdate
245 created_by, -- NN :new.created_by
246 last_update_login, -- N -1
247 new_order_quantity, -- NN
248 old_order_quantity, -- N
249 new_order_date, -- NN
250 old_order_date, -- N
251 disposition_id, -- NN :new.wip_entity_id
252 planned_order_id, -- N :new.source_code,'MRP',
253 relief_type, -- NN 2
254 disposition_type, -- NN 1
255 demand_class, -- N
256 old_demand_class, -- N
257 line_num, -- N null
258 request_id, -- N null
259 program_application_id, -- N null
260 program_id, -- N null
261 program_update_date, -- N null
262 process_status, -- NN 2
263 source_code, -- N 'WIP'
264 source_line_id, -- N null
265 error_message, -- N null
266 transaction_id, -- NN
267 project_id,
268 old_project_id,
269 task_id,
270 old_task_id
271 )
272 SELECT inventory_item_id,
273 organization_id,
274 last_upd_date,
275 last_upd_by,
276 creat_date,
277 creat_by,
278 -1,
279 quantity_per_assembly * new_qty,
280 quantity_per_assembly * old_qty,
281 new_date,
282 old_date,
283 wip_entity_id,
284 DECODE(srce_code, 'MRP', srce_line_id, null),
285 2,
286 1,
287 new_dmd_class,
288 old_dmd_class,
289 null,
290 null,
291 null,
292 null,
293 null,
294 2,
295 'WIP',
296 null,
297 null,
298 mrp_relief_interface_s.nextval,
299 new_project_id,
300 old_project_id,
301 new_task_id,
302 old_task_id
303 FROM wip_requirement_operations
304 WHERE wip_entity_id = wip_enty_id
305 AND organization_id = org_id
306 AND wip_supply_type = 6;
307 ELSE /** INSERTING or UPDATING **/
308 INSERT INTO mrp_relief_interface
309 (inventory_item_id, -- NN
310 organization_id, -- NN
311 last_update_date, -- NN sysdate
312 last_updated_by, -- NN :new.last_updated_by
313 creation_date, -- NN sysdate
314 created_by, -- NN :new.created_by
315 last_update_login, -- N -1
316 new_order_quantity, -- NN
317 old_order_quantity, -- N
318 new_order_date, -- NN
319 old_order_date, -- N
320 disposition_id, -- NN :new.wip_entity_id
321 planned_order_id, -- N :new.source_code,'MRP',
322 relief_type, -- NN 2
323 disposition_type, -- NN 1
324 demand_class, -- N
325 old_demand_class, -- N
326 line_num, -- N null
327 request_id, -- N null
328 program_application_id, -- N null
329 program_id, -- N null
330 program_update_date, -- N null
331 process_status, -- NN 2
332 source_code, -- N 'WIP'
333 source_line_id, -- N null
334 error_message, -- N null
335 transaction_id, -- NN
336 project_id,
337 old_project_id,
338 task_id,
339 old_task_id
340 )
341 SELECT inventory_item_id,
342 organization_id,
343 last_upd_date,
344 last_upd_by,
345 creat_date,
346 creat_by,
347 -1,
348 quantity_per_assembly * new_qty,
349 quantity_per_assembly * old_qty,
353 DECODE(srce_code, 'MRP', srce_line_id, null),
350 date_required, -- use WRO date_required as new_date
351 old_date,
352 wip_entity_id,
354 2,
355 1,
356 new_dmd_class,
357 old_dmd_class,
358 null,
359 null,
360 null,
361 null,
362 null,
363 2,
364 'WIP',
365 null,
366 null,
367 mrp_relief_interface_s.nextval,
368 new_project_id,
369 old_project_id,
370 new_task_id,
371 old_task_id
372 FROM wip_requirement_operations
373 WHERE wip_entity_id = wip_enty_id
374 AND organization_id = org_id
375 AND wip_supply_type = 6;
376 END IF;
377
378 END WIP_DISCRETE_JOBS_PROC;
379
380 PROCEDURE WIP_FLOW_SCHEDULES_PROC
381 (item_id IN NUMBER,
382 org_id IN NUMBER,
383 last_upd_date IN DATE,
384 last_upd_by IN NUMBER,
385 creat_date IN DATE,
386 creat_by IN NUMBER,
387 new_request_id IN NUMBER,
388 old_request_id IN NUMBER,
389 dmd_src_type IN NUMBER,
390 dmd_src_line IN VARCHAR2,
391 new_mps_quantity IN NUMBER,
392 old_mps_quantity IN NUMBER,
393 new_sched_compl_date IN DATE,
394 old_sched_compl_date IN DATE,
395 wip_enty_id IN NUMBER,
396 new_dmd_class IN VARCHAR2,
397 old_dmd_class IN VARCHAR2,
398 new_bill_desig IN VARCHAR2,
399 old_bill_desig IN VARCHAR2,
400 new_status_type IN NUMBER,
401 old_status_type IN NUMBER,
402 new_qty_completed IN NUMBER,
403 old_qty_completed IN NUMBER,
404 new_date_completed IN DATE,
405 old_date_completed IN DATE,
406 new_project_id IN NUMBER,
407 old_project_id IN NUMBER,
408 new_task_id IN NUMBER,
409 old_task_id IN NUMBER) IS
410
411 new_qty NUMBER;
412 old_qty NUMBER;
413 new_date DATE;
414 old_date DATE;
415
416 bill_desig_changed BOOLEAN := FALSE;
417
418 BEGIN
419
420 /*------- Determine quantities and date for the insert ------------------*/
421 IF INSERTING THEN
422
423 -- this is a kludge for the line scheduling workbench form
424 IF new_request_id IS NOT NULL THEN
425 return;
426 END IF;
427
428 /*------------------------------------------------------------+
429 | Insert - relieve if status is "Open" (procedure will |
430 | only be called in this cases due to trigger's IF clause) |
431 +-------------------------------------------------------------*/
432 new_qty := NVL(new_mps_quantity,0);
433 old_qty := null;
434 new_date := new_sched_compl_date;
435 old_date := null;
436
437 ELSIF DELETING THEN
438
439 -- this is a kludge for the line scheduling workbench form
440 IF old_request_id IS NOT NULL THEN
441 return;
442 END IF;
443
444 /*------------------------------------------------------------+
445 | Delete - unrelieve if status is "Open" (procedure will |
446 | only be called in this cases due to trigger's WHEN clause) |
447 +-------------------------------------------------------------*/
448 new_qty := NVL(LEAST(old_mps_quantity, old_qty_completed),0);
449 old_qty := old_mps_quantity;
450 new_date := SYSDATE;
451 old_date := old_sched_compl_date;
452
453 ELSIF UPDATING THEN
454 /*------------------------------------------------------------+
455 | Bug 2289820, we will not consider status when doing relief |
456 +-------------------------------------------------------------*/
457
458
459 /*------------------------------------------------------------+
460 | |
461 | Update - Changes to status_type are only important if they |
462 | move between an "Open" status (1) and a "Closed" |
463 | status (2). |
464 | |
465 | * Updates within a "Closed" status should be |
466 | ignored |
467 | |
468 | * Updates from an "Open" status to a |
469 | "Closed" status should be treated as deletes |
470 | |
471 | * Updates from a "Closed" status to an |
472 | "Open" status should be treated as inserts |
473 | |
474 | * Updated within an "Open" status should ignore |
475 | the status and pass the other changed values to the |
479
476 | mrp_relief_interface table |
477 | |
478 +------------------------------------------------------------*/
480 /* NOTE - changes within a "Closed" status have already been
481 screened out */
482
483 /* Changes from an "Open" status to a "Closed" status */
484 /* IF (old_status_type = 1 AND new_status_type = 2) THEN
485 old_qty := old_mps_quantity;
486 new_qty := NVL(LEAST(old_qty, old_qty_completed), 0);
487 old_date := old_sched_compl_date;
488 new_date := LEAST(TRUNC(SYSDATE), old_date);
489
490 IF (old_qty = new_qty) AND (old_date = new_date) THEN
491 return;
492 END IF;
493 commented the above in 2289820*/
494
495 /* Changes from an "non-effective" status to a "effective" status */
496 /* ELSIF (old_status_type = 2 AND new_status_type = 1) THEN
497 new_qty := NVL(new_mps_quantity,0);
498 old_qty := null;
499 new_date := new_sched_compl_date;
500 old_date := null;
501 commented the above in bug#2289820*/
502
503 /* Changes within an "effective" status */
504 --commented in bug#2289820 ELSIF (old_status_type = 1 AND new_status_type = 1) THEN
505 /* Added for bug number 2222628 */
506 IF (old_request_id is NOT NULL AND new_request_id is NOT NULL) then
507 IF ( new_mps_quantity = old_mps_quantity and new_mps_quantity = 0 and
508 old_mps_quantity = 0 ) then
509 null;
510 else
511 return;
512 end if;
513 END IF;
514 IF (old_request_id is NOT NULL AND new_request_id is NULL) THEN
515 -- kludge for the line scheduling workbench form - treat this
516 -- like an insert
517 new_qty := NVL(new_mps_quantity,0);
518 old_qty := null;
519 new_date := new_sched_compl_date;
520 old_date := null;
521 ELSE
522 /* new_qty := NVL(new_mps_quantity, 0);
523 old_qty := old_mps_quantity; Commented and added the following two lines in 2289820
524 new_qty := NVL(greatest(new_mps_quantity,new_qty_completed), 0);
525 old_qty := greatest(old_mps_quantity,old_qty_completed);*/
526
527 if (abs(new_mps_quantity) >= abs(new_qty_completed)) then
528 new_qty := nvl(new_mps_quantity, 0);
529 Else
530 new_qty := nvl(new_qty_completed,0);
531 End if;
532
533 if (abs(old_mps_quantity) >= abs(old_qty_completed)) then
534 old_qty := old_mps_quantity;
535 Else
536 old_qty := old_qty_completed;
537 End if;
538
539 new_date := TRUNC(new_sched_compl_date);
540 old_date := TRUNC(old_sched_compl_date);
541 END IF;
542
543 IF nvl(new_bill_desig, '@$!') <> nvl(old_bill_desig, '@$!') THEN
544 bill_desig_changed := TRUE;
545 ELSE
546 bill_desig_changed := FALSE;
547 END IF;
548 --commented in bug#2289820 END IF;
549
550 END IF;
551
552 /* Now insert a record into MRP_RELIEF_INTERFACE to take care of
553 MPS relief for assembly Note that if bill designator changes
554 we do not insert a row for the assembly. */
555
556
557 INSERT INTO mrp_relief_interface
558 (inventory_item_id, -- NN
559 organization_id, -- NN
560 last_update_date, -- NN sysdate
561 last_updated_by, -- NN :new.last_updated_by
562 creation_date, -- NN sysdate
563 created_by, -- NN :new.created_by
564 last_update_login, -- N -1
565 new_order_quantity, -- NN
566 old_order_quantity, -- N
567 new_order_date, -- NN
568 old_order_date, -- N
569 disposition_id, -- NN :new.wip_entity_id
570 planned_order_id, -- N
571 relief_type, -- NN 2
572 disposition_type, -- NN 9
573 demand_class, -- N
574 old_demand_class, -- N
575 line_num, -- N null
576 request_id, -- N null
577 program_application_id, -- N null
578 program_id, -- N null
579 program_update_date, -- N null
580 process_status, -- NN 2
581 source_code, -- N 'WIP'
582 source_line_id, -- N null
583 error_message, -- N null
584 transaction_id, -- NN
585 project_id,
586 old_project_id,
587 task_id,
588 old_task_id
589 )
590 VALUES (item_id,
591 org_id,
592 last_upd_date,
593 last_upd_by,
594 creat_date,
595 creat_by,
599 new_date,
596 -1,
597 new_qty,
598 old_qty,
600 old_date,
601 wip_enty_id,
602 DECODE (dmd_src_type, 100, to_number(dmd_src_line), null),
603 2,
604 9,
605 new_dmd_class,
606 old_dmd_class,
607 null,
608 null,
609 null,
610 null,
611 null,
612 2,
613 'WIP',
614 null,
615 null,
616 mrp_relief_interface_s.nextval,
617 new_project_id,
618 old_project_id,
619 new_task_id,
620 old_task_id
621 );
622
623 IF NOT bill_desig_changed THEN
624
625 /* insert any rows for phantom items */
626
627 INSERT
628 INTO mrp_relief_interface
629 (inventory_item_id,
630 organization_id,
631 last_update_date,
632 last_updated_by,
633 creation_date,
634 created_by,
635 last_update_login,
636 new_order_quantity,
637 old_order_quantity,
638 new_order_date,
639 old_order_date,
640 disposition_id,
641 planned_order_id,
642 relief_type,
643 disposition_type,
644 demand_class,
645 old_demand_class,
646 line_num,
647 request_id,
648 program_application_id,
649 program_id,
650 program_update_date,
651 process_status,
652 source_code,
653 source_line_id,
654 error_message,
655 transaction_id,
656 project_id,
657 old_project_id,
658 task_id,
659 old_task_id)
660 SELECT
661 bic.component_item_id,
662 org_id,
663 sysdate,
664 -1,
665 sysdate,
666 -1,
667 null,
668 nvl(new_qty,0) *
669 nvl(bic.component_quantity,1),
670 nvl(old_qty,0) *
671 nvl(bic.component_quantity,1),
672 new_date,
673 old_date,
674 wip_enty_id,
675 null,
676 2,
677 9,
678 new_dmd_class,
679 old_dmd_class,
680 null,
681 null,
682 null,
683 null,
684 null,
685 2,
686 'WIP',
687 null,
688 null,
689 mrp_relief_interface_s.nextval,
690 new_project_id,
691 old_project_id,
692 new_task_id,
693 old_task_id
694 FROM
695 mtl_system_items msi,
696 bom_inventory_components bic,
697 bom_bill_of_materials bbom
698 WHERE bbom.assembly_item_id = item_id
699 AND NVL(bbom.alternate_bom_designator, '@$!') =
700 NVL(new_bill_desig, '@$!')
701 AND bbom.organization_id = org_id
702 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
703 AND (nvl(bic.disable_date, new_sched_compl_date) +1) >=
704 new_sched_compl_date
705 AND bic.effectivity_date <= new_sched_compl_date
706 AND msi.inventory_item_id = bic.component_item_id
707 AND msi.organization_id = bbom.organization_id
708 AND msi.bom_item_type in (1, 2)
709 AND NVL(bic.wip_supply_type, NVL(msi.wip_supply_type,6)) = 6;
710
711
712 END IF;
713
714
715 /* now check if the bill designator changed and take care of handling
716 Phantom items that might be affected due to the change. What we
717 do is - treat all Phantom items that belong to the new bill desig
718 as inserted and treat all phantom items that belong to the old bill
719 designator as deleted */
720
721 IF bill_desig_changed THEN
722
723 /* inserts for the new bill designator */
724
725 INSERT
726 INTO mrp_relief_interface
727 (inventory_item_id,
728 organization_id,
729 last_update_date,
730 last_updated_by,
731 creation_date,
732 created_by,
733 last_update_login,
734 new_order_quantity,
735 old_order_quantity,
736 new_order_date,
737 old_order_date,
738 disposition_id,
739 planned_order_id,
740 relief_type,
741 disposition_type,
742 demand_class,
743 old_demand_class,
744 line_num,
745 request_id,
746 program_application_id,
747 program_id,
748 program_update_date,
749 process_status,
750 source_code,
751 source_line_id,
752 error_message,
753 transaction_id,
754 project_id,
755 old_project_id,
756 task_id,
757 old_task_id)
758 SELECT
759 bic.component_item_id,
760 org_id,
761 sysdate,
762 -1,
763 sysdate,
764 -1,
765 null,
766 (NVL(new_mps_quantity,0) *
767 nvl(bic.component_quantity,1)),
771 wip_enty_id,
768 null,
769 new_sched_compl_date,
770 null,
772 null,
773 2,
774 9,
775 new_dmd_class,
776 old_dmd_class,
777 null,
778 null,
779 null,
780 null,
781 null,
782 2,
783 'WIP',
784 null,
785 null,
786 mrp_relief_interface_s.nextval,
787 new_project_id,
788 old_project_id,
789 new_task_id,
790 old_task_id
791 FROM
792 mtl_system_items msi,
793 bom_inventory_components bic,
794 bom_bill_of_materials bbom
795 WHERE bbom.assembly_item_id = item_id
796 AND NVL(bbom.alternate_bom_designator, '@$!') =
797 NVL(new_bill_desig, '@$!')
798 AND bbom.organization_id = org_id
799 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
800 AND (nvl(bic.disable_date, new_sched_compl_date) +1) >=
801 new_sched_compl_date
802 AND bic.effectivity_date <= new_sched_compl_date
803 AND msi.inventory_item_id = bic.component_item_id
804 AND msi.organization_id = bbom.organization_id
805 AND msi.bom_item_type in (1, 2)
806 AND NVL(bic.wip_supply_type, NVL(msi.wip_supply_type,6)) = 6;
807
808
809 /* now insert for the old bill designator */
810
811 INSERT
812 INTO mrp_relief_interface
813 (inventory_item_id,
814 organization_id,
815 last_update_date,
816 last_updated_by,
817 creation_date,
818 created_by,
819 last_update_login,
820 new_order_quantity,
821 old_order_quantity,
822 new_order_date,
823 old_order_date,
824 disposition_id,
825 planned_order_id,
826 relief_type,
827 disposition_type,
828 demand_class,
829 old_demand_class,
830 line_num,
831 request_id,
832 program_application_id,
833 program_id,
834 program_update_date,
835 process_status,
836 source_code,
837 source_line_id,
838 error_message,
839 transaction_id,
840 project_id,
841 old_project_id,
842 task_id,
843 old_task_id)
844 SELECT
845 bic.component_item_id,
846 org_id,
847 sysdate,
848 -1,
849 sysdate,
850 -1,
851 null,
852 (NVL(LEAST(old_mps_quantity, old_qty_completed),0) *
853 nvl(bic.component_quantity,1)),
854 nvl(old_qty,0) *
855 nvl(bic.component_quantity,1),
856 SYSDATE,
857 old_sched_compl_date,
858 wip_enty_id,
859 null,
860 2,
861 9,
862 new_dmd_class,
863 old_dmd_class,
864 null,
865 null,
866 null,
867 null,
868 null,
869 2,
870 'WIP',
871 null,
872 null,
873 mrp_relief_interface_s.nextval,
874 new_project_id,
875 old_project_id,
876 new_task_id,
877 old_task_id
878 FROM
879 mtl_system_items msi,
880 bom_inventory_components bic,
881 bom_bill_of_materials bbom
882 WHERE bbom.assembly_item_id = item_id
883 AND NVL(bbom.alternate_bom_designator, '@$!') =
884 NVL(old_bill_desig, '@$!')
885 AND bbom.organization_id = org_id
886 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
887 AND (nvl(bic.disable_date, new_sched_compl_date) +1) >=
888 new_sched_compl_date
889 AND bic.effectivity_date <= new_sched_compl_date
890 AND msi.inventory_item_id = bic.component_item_id
891 AND msi.organization_id = bbom.organization_id
892 AND msi.bom_item_type in (1, 2)
893 AND NVL(bic.wip_supply_type, NVL(msi.wip_supply_type,6)) = 6;
894
895 END IF; /* end if bill designator changed */
896
897 END WIP_FLOW_SCHEDULES_PROC;
898
899 END WIP_MRP_RELIEF;
[Home] [Help]