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