[Home] [Help]
PACKAGE BODY: APPS.MRP_FLOW_DEMAND
Source
1 PACKAGE BODY MRP_FLOW_DEMAND AS
2 /* $Header: MRPFLOWB.pls 120.2 2005/06/30 05:14:04 gkooruve noship $ */
3
4 g_organization_id NUMBER;
5 g_planned_quantity NUMBER;
6 g_primary_item_id NUMBER;
7 g_quantity_completed NUMBER;
8 g_scheduled_completion_date DATE;
9 g_scheduled_start_date DATE;
10 g_wip_entity_id NUMBER;
11 g_RN NUMBER;
12
13 g_wip_entity_id_arr NUMBER_ARR;
14
15 PROCEDURE Main_Flow_Demand( i_RN IN NUMBER,
16 o_return_code OUT NOCOPY NUMBER,
17 o_error_message OUT NOCOPY VARCHAR2)
18 IS
19 sqls varchar2(100);
20 l_retval BOOLEAN;
21 l_applsys_schema VARCHAR2(10);
22 dummy1 VARCHAR2(10);
23 dummy2 VARCHAR2(10);
24
25
26 lv_changed_job NUMBER;
27 lv_temp_sql_stmt VARCHAR2(2000);
28
29 BEGIN
30
31 /* -----------------------------------------------
32 Cleanup MRP_FLOW_DEMANDS data
33 -------------------------------------------------*/
34
35 l_retval := FND_INSTALLATION.GET_APP_INFO('MRP', dummy1, dummy2, l_applsys_schema);
36
37 IF (i_RN = -1) THEN
38 -- complete refresh
39 sqls := 'TRUNCATE TABLE '||l_applsys_schema||'.MRP_FLOW_DEMANDS';
40 execute immediate sqls;
41 ELSE
42 DELETE FROM MRP_FLOW_DEMANDS
43 WHERE wip_entity_id IN ( SELECT wip_entity_id
44 FROM MRP_AD_FLOW_SCHDS
45 WHERE RN= i_RN);
46 END IF;
47
48 COMMIT;
49
50 /* -----------------------------------------------
51 Execute bill and flow jobs changes
52 -------------------------------------------------*/
53
54 -- execute bill change for old jobs
55
56 Get_Bill_Change( i_RN,
57 o_return_code,
58 o_error_message);
59
60 -- performance fix for Bug 3550414.
61 IF (i_RN <> -1) THEN
62
63 -- if jobs are affected by current bill changes
64 IF (g_wip_entity_id_arr.COUNT <> 0 ) THEN
65
66 lv_temp_sql_stmt := ' DELETE /*+ parallel(mfd) */ '
67 ||' FROM MRP_FLOW_DEMANDS MFD '
68 ||' WHERE EXISTS (SELECT 1 '
69 ||' FROM MRP_SN_INV_COMPS MSIC '
70 ||' WHERE MSIC.RN = :i_RN'
71 ||' AND MSIC.BILL_SEQUENCE_ID = MFD.BILL_SEQUENCE_ID) ';
72
73 EXECUTE IMMEDIATE lv_temp_sql_stmt USING i_RN;
74
75 END IF;
76
77 -- if any of the existing jobs have changed
78 lv_temp_sql_stmt := ' SELECT count(*) '
79 ||' FROM MRP_SN_FLOW_SCHDS '
80 ||' WHERE RN >= :i_RN '
81 ||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED ';
82
83
84 EXECUTE IMMEDIATE lv_temp_sql_stmt
85 INTO lv_changed_job USING i_RN;
86
87 IF (lv_changed_job > 0 ) THEN
88
89 lv_temp_sql_stmt := ' DELETE FROM MRP_FLOW_DEMANDS MFD '
90 ||' WHERE EXISTS (SELECT 1 FROM MRP_SN_FLOW_SCHDS MFS '
91 ||' WHERE MFS.RN >= :i_RN '
92 ||' AND MFS.PLANNED_QUANTITY > MFS.QUANTITY_COMPLETED '
93 ||' AND MFS.WIP_ENTITY_ID = MFD.WIP_ENTITY_ID) ';
94
95 EXECUTE IMMEDIATE lv_temp_sql_stmt USING i_RN;
96
97 END IF;
98
99 END IF;
100
101 COMMIT;
102
103 -- updatinge old flow jobs and inserting new flow jobs
104 Execute_Flow_Demand( i_RN,
105 o_return_code,
106 o_error_message);
107
108 -- Execute Remained Bill Change JOBs
109
110 Execute_Remained_JOBS(i_RN,
111 o_return_code,
112 o_error_message);
113
114 g_wip_entity_id_arr.DELETE;
115
116
117 END Main_Flow_Demand;
118
119
120
121 PROCEDURE Get_Bill_Change( i_RN IN NUMBER,
122 o_return_code OUT NOCOPY NUMBER,
123 o_error_message OUT NOCOPY VARCHAR2)
124 IS
125 /* THis is being commented for New Patching Strategy wherin this will be a dynamic clause
126 CURSOR Bill_Change(i_RN_index IN NUMBER) IS
127 SELECT DISTINCT
128 BILL_SEQUENCE_ID
129 FROM MRP_SN_INV_COMPS
130 WHERE RN = i_RN_index
131 ;
132 */
133
134 CURSOR WIP_FLOW_JOBS_AFFECTED(i_RN_index IN NUMBER, i_bill_sequence_id IN NUMBER) IS
135 SELECT DISTINCT -- for performance we can remove distinct
136 wip_entity_id
137 FROM MRP_FLOW_DEMANDS
138 WHERE BILL_SEQUENCE_ID = i_bill_sequence_id;
139
140 l_bill_sequence_id NUMBER;
141 l_wip_entity_id NUMBER;
142 l_index NUMBER;
143 lv_cursor_stmt VARCHAR2(5000);
144 TYPE CurTyp IS REF CURSOR;
145 Bill_Change CurTyp;
146
147
148
149
150 BEGIN
151
152 IF (i_RN = -1) THEN
153 -- we need not to perform bill on complete refresh mode.
154 RETURN;
155 END IF;
156
157 lv_cursor_stmt :=
158 ' SELECT /*+ index(bic bom_inv_comps_sn_n1) */ DISTINCT'
159 ||' BILL_SEQUENCE_ID '
160 ||' FROM MRP_SN_INV_COMPS bic'
161 ||' WHERE RN = '||i_RN;
162
163 OPEN Bill_Change for lv_cursor_stmt;
164
165 LOOP
166 FETCH Bill_Change INTO
167 l_bill_sequence_id;
168 EXIT WHEN Bill_Change%NOTFOUND;
169
170 OPEN WIP_FLOW_JOBS_AFFECTED(i_RN, l_bill_sequence_id);
171
172 LOOP
173
174 FETCH WIP_FLOW_JOBS_AFFECTED INTO
175 l_wip_entity_id;
176 EXIT WHEN WIP_FLOW_JOBS_AFFECTED%NOTFOUND;
177
178 g_wip_entity_id_arr(l_wip_entity_id) := 1;
179
180 END LOOP;
181
182 CLOSE WIP_FLOW_JOBS_AFFECTED;
183
184 END LOOP;
185
186 CLOSE Bill_Change;
187
188 END;
189
190 PROCEDURE Execute_Remained_JOBS(i_RN IN NUMBER,
191 o_return_code OUT NOCOPY NUMBER,
192 o_error_message OUT NOCOPY VARCHAR2)
193
194 IS
195
196 l_index NUMBER;
197 lv_cursor_stmt varchar2(5000);
198
199 BEGIN
200
201
202 IF (g_wip_entity_id_arr.COUNT = 0 ) THEN
203 -- no old bills been affected by current bill changes
204 RETURN;
205 END IF;
206
207 l_index := g_wip_entity_id_arr.FIRST;
208
209 LOOP
210
211 IF (g_wip_entity_id_arr(l_index) = 1) THEN
212
213 -- get top flow job information
214
215 BEGIN
216 /* THis is being commented for New Patching Strategy wherin this will be a dynamic clause
217 SELECT
218 ORGANIZATION_ID,
219 PLANNED_QUANTITY,
220 PRIMARY_ITEM_ID,
221 QUANTITY_COMPLETED,
222 SCHEDULED_COMPLETION_DATE,
223 SCHEDULED_START_DATE,
224 WIP_ENTITY_ID,
225 i_RN
226 INTO
227 g_organization_id,
228 g_planned_quantity,
229 g_primary_item_id,
230 g_quantity_completed,
231 g_scheduled_completion_date,
232 g_scheduled_start_date,
233 g_wip_entity_id,
234 g_RN
235 FROM MRP_SN_FLOW_SCHDS
236 WHERE wip_entity_id = l_index
237 AND PLANNED_QUANTITY > QUANTITY_COMPLETED;
238 */
239
240 lv_cursor_stmt :=
241 ' SELECT'
242 ||' ORGANIZATION_ID,'
243 ||' PLANNED_QUANTITY,'
244 ||' PRIMARY_ITEM_ID,'
245 ||' QUANTITY_COMPLETED,'
246 ||' SCHEDULED_COMPLETION_DATE,'
247 ||' SCHEDULED_START_DATE,'
248 ||' WIP_ENTITY_ID,'
249 || i_RN
250 ||' FROM MRP_SN_FLOW_SCHDS'
251 ||' WHERE wip_entity_id = '||l_index
252 ||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED';
253
254 EXECUTE IMMEDIATE lv_cursor_stmt
255 INTO
256 g_organization_id,
257 g_planned_quantity,
258 g_primary_item_id,
259 g_quantity_completed,
260 g_scheduled_completion_date,
261 g_scheduled_start_date,
262 g_wip_entity_id,
263 g_RN;
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN NULL;
266 WHEN OTHERS THEN RAISE;
267 END;
268
269 IF SQL%ROWCOUNT > 0 THEN
270
271 ReExplode_Flow_Demands(o_return_code,
272 o_error_message);
273 END IF;
274
275 END IF;
276
277 EXIT WHEN l_index = g_wip_entity_id_arr.LAST;
278 l_index := g_wip_entity_id_arr.NEXT(l_index);
279
280 END LOOP;
281
282
283 END;
284
285 PROCEDURE Execute_Flow_Demand( i_RN IN NUMBER,
286 o_return_code OUT NOCOPY NUMBER,
287 o_error_message OUT NOCOPY VARCHAR2) IS
288 /*
289 CURSOR FLOW_SCHDS(i_RN_index IN NUMBER) IS
290 SELECT
291 ORGANIZATION_ID,
292 PLANNED_QUANTITY,
293 PRIMARY_ITEM_ID,
294 QUANTITY_COMPLETED,
295 SCHEDULED_COMPLETION_DATE,
296 SCHEDULED_START_DATE,
297 WIP_ENTITY_ID,
298 RN
299 FROM MRP_SN_FLOW_SCHDS
300 WHERE RN >= i_RN_index
301 AND PLANNED_QUANTITY > QUANTITY_COMPLETED;
302 */
303 lv_cursor_stmt varchar2(5000);
304 l_job_status NUMBER;
305 TYPE CurTyp IS REF CURSOR;
306 FLOW_SCHDS CurTyp;
307
308 BEGIN
309 lv_cursor_stmt :=
310 ' SELECT'
311 ||' ORGANIZATION_ID,'
312 ||' PLANNED_QUANTITY,'
313 ||' PRIMARY_ITEM_ID,'
314 ||' QUANTITY_COMPLETED,'
315 ||' SCHEDULED_COMPLETION_DATE,'
316 ||' SCHEDULED_START_DATE,'
317 ||' WIP_ENTITY_ID,'
318 ||' RN'
319 ||' FROM MRP_SN_FLOW_SCHDS'
320 ||' WHERE RN >= '||i_RN
321 ||' AND PLANNED_QUANTITY > QUANTITY_COMPLETED';
322
323 OPEN FLOW_SCHDS for lv_cursor_stmt;
324
325 LOOP
326 FETCH FLOW_SCHDS INTO
327 g_organization_id,
328 g_planned_quantity,
329 g_primary_item_id,
330 g_quantity_completed,
331 g_scheduled_completion_date,
332 g_scheduled_start_date,
333 g_wip_entity_id,
334 g_RN;
335
339 Explode_Flow_Demands( o_return_code,
336 EXIT WHEN FLOW_SCHDS%NOTFOUND;
337
338 IF (i_RN = -1) THEN
340 o_error_message);
341 ELSE
342 ReExplode_Flow_Demands( o_return_code,
343 o_error_message);
344
345 /* remove the IF clause, if the g_wip_entity_id is not
346 in the array, it will cause ORA-1403 error.
347
348 IF g_wip_entity_id_arr(g_wip_entity_id) = 1 THEN
349 g_wip_entity_id_arr(g_wip_entity_id) := 0;
350 END IF; */
351
352 g_wip_entity_id_arr(g_wip_entity_id) := 0;
353
354 END IF;
355
356 END LOOP;
357
358 CLOSE FLOW_SCHDS;
359
360 END Execute_Flow_Demand;
361
362
363 PROCEDURE Explode_Flow_Demands( o_return_code OUT NOCOPY NUMBER,
364 o_error_message OUT NOCOPY VARCHAR2) IS
365
366 parent_item parent_item_type;
367 o_phantom_items parent_item_type;
368 l_level NUMBER := 0; -- top item level = 0
369
370 BEGIN
371
372 parent_item.inventory_item_id(1) := g_primary_item_id;
373 parent_item.quantity_completed(1) := g_quantity_completed;
374 parent_item.planned_quantity(1) := g_planned_quantity;
375
376 LOOP
377 Insert_Demands(parent_item,
378 l_level,
379 o_return_code,
380 o_error_message);
381 IF (o_return_code = 0) THEN
382 RETURN;
383 END IF;
384 Get_Phantoms(parent_item,
385 o_phantom_items,
386 o_return_code,
387 o_error_message);
388
389 IF (o_return_code = 0) THEN
390 RETURN;
391 END IF;
392
393
394 EXIT WHEN o_phantom_items.inventory_item_id.COUNT <= 0;
395
396 parent_item := o_phantom_items;
397 l_level := l_level + 1;
398
399 END LOOP;
400
401 END Explode_Flow_Demands;
402
403
404 PROCEDURE ReExplode_Flow_Demands(o_return_code OUT NOCOPY NUMBER,
405 o_error_message OUT NOCOPY VARCHAR2)
406 IS
407
408 BEGIN
409
410 -- delete old demand
411 -- performance fix for Bug 3550414.
412 -- DELETE MRP_FLOW_DEMANDS
413 -- WHERE wip_entity_id = g_wip_entity_id;
414
415
416 -- insert demand for new bill
417
418 Explode_Flow_Demands( o_return_code,
419 o_error_message);
420
421
422 END;
423
424
425 PROCEDURE Get_Phantoms(i_parent_items IN parent_item_type,
426 o_phantom_items OUT NOCOPY parent_item_type,
427 o_return_code OUT NOCOPY NUMBER,
428 o_error_message OUT NOCOPY VARCHAR2)
429 IS
430 TYPE CurTyp IS REF CURSOR;
431 get_phantom_items CurTyp;
432 /* THis will be a dynamic clause in New Patching Strategy
433 CURSOR get_phantom_items(i_parent_item IN NUMBER) IS
434 SELECT
435 bic.component_item_id,
436 bic.component_quantity
437 FROM
438 MRP_SN_BOMS bom,
439 MRP_SN_INV_COMPS bic
440 WHERE
441 bom.assembly_item_id = i_parent_item
442 AND bom.organization_id = g_organization_id
443 AND bom.alternate_bom_designator IS NULL --- primary bill
444 AND bic.bill_sequence_id = bom.common_bill_sequence_id
445 AND bic.effectivity_date < g_scheduled_completion_date
446 AND NVL(bic.disable_date, g_scheduled_completion_date + 1)
447 > g_scheduled_completion_date
448 AND bic.WIP_SUPPLY_TYPE = 6;
449 */
450
451 l_j NUMBER;
452 l_out_index NUMBER;
453 l_inventory_item_id NUMBER;
454 l_component_qty NUMBER;
455 l_phantom_items parent_item_type;
456 lv_cursor_stmt varchar2(5000);
457 lv_date varchar2(20);
458
459 BEGIN
460
461 l_j := i_parent_items.inventory_item_id.FIRST;
462 l_out_index := 1;
463 lv_date := g_scheduled_completion_date;
464
465 lv_cursor_stmt :=
466 ' SELECT'
467 ||' bic.component_item_id,'
468 ||' bic.component_quantity'
469 ||' FROM'
470 ||' MRP_SN_BOMS bom,'
471 ||' MRP_SN_INV_COMPS bic,'
472 ||' MTL_SYSTEM_ITEMS msi1,'
473 ||' MTL_SYSTEM_ITEMS msi2'
474 ||' WHERE'
475 ||' bom.assembly_item_id = :inventory_item_id'
476 ||' AND bom.organization_id = :organization_id'
477 ||' AND msi1.inventory_item_id = bom.assembly_item_id'
478 ||' AND msi1.organization_id = bom.organization_id'
479 ||' AND bom.alternate_bom_designator IS NULL '
480 ||' AND bic.bill_sequence_id = bom.common_bill_sequence_id'
481 ||' AND bic.effectivity_date < :g_scheduled_completion_date '
482 ||' AND NVL(bic.disable_date, :g_scheduled_completion_date + 1) '
483 ||' > :g_scheduled_completion_date '
487 ||' AND NOT (msi1.AUTO_CREATED_CONFIG_FLAG=''Y'' and msi1.base_item_id is NOT NULL'
484 ||' AND bic.WIP_SUPPLY_TYPE = 6'
485 ||' AND msi2.inventory_item_id = bic.component_item_id'
486 ||' AND msi2.organization_id = msi1.organization_id'
488 ||' and (msi2.BOM_ITEM_TYPE = 1 OR msi2.BOM_ITEM_TYPE = 2))';
489
490 LOOP
491
492 OPEN get_phantom_items for lv_cursor_stmt USING
493 i_parent_items.inventory_item_id(l_j),
494 g_organization_id,
495 g_scheduled_completion_date,
496 g_scheduled_completion_date,
497 g_scheduled_completion_date;
498
499 LOOP
500 FETCH get_phantom_items INTO
501 l_inventory_item_id,
502 l_component_qty;
503 EXIT WHEN get_phantom_items%NOTFOUND;
504
505 l_phantom_items.inventory_item_id(l_out_index)
506 := l_inventory_item_id;
507 l_phantom_items.planned_quantity(l_out_index)
508 := l_component_qty * i_parent_items.planned_quantity(l_j);
509 l_phantom_items.quantity_completed(l_out_index)
510 := l_component_qty * i_parent_items.quantity_completed(l_j);
511
512 l_out_index:= l_out_index+1;
513
514 END LOOP;
515
516 CLOSE get_phantom_items;
517
518 EXIT WHEN l_j = i_parent_items.inventory_item_id.LAST;
519 l_j:= i_parent_items.inventory_item_id.NEXT(l_j);
520
521 END LOOP;
522
523 o_phantom_items := l_phantom_items;
524
525 END;
526
527
528
529 PROCEDURE Insert_Demands(i_parent_items IN PARENT_ITEM_TYPE,
530 i_level IN NUMBER,
531 o_return_code OUT NOCOPY NUMBER,
532 o_error_message OUT NOCOPY VARCHAR2) IS
533
534 TYPE CurTyp IS REF CURSOR;
535 lv_ins CurTyp;
536 l_index NUMBER;
537 lv_cursor_stmt varchar2(5000);
538 lv_organization_id mrp_flow_demands.organization_id%TYPE;
539 lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540 lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541 lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542 lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543 lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544 lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545 lv_plan_level mrp_flow_demands.plan_level%TYPE;
546 lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547 lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548 lv_RN mrp_flow_demands.rn%TYPE;
549
550 TYPE NumTblTyp IS TABLE OF NUMBER;
551 TYPE DateTblTyp IS TABLE OF DATE;
552
553 lv_organization_id_tab NumTblTyp := NumTblTyp();
554 lv_planned_quantity_tab NumTblTyp := NumTblTyp();
555 lv_primary_item_id_tab NumTblTyp := NumTblTyp();
556 lv_quantity_completed_tab NumTblTyp := NumTblTyp();
557 lv_scheduled_comp_date_tab DateTblTyp := DateTblTyp();
558 lv_scheduled_start_date_tab DateTblTyp := DateTblTyp();
559 lv_wip_entity_id_tab NumTblTyp := NumTblTyp();
560 lv_plan_level_tab NumTblTyp := NumTblTyp();
561 lv_wip_supply_type_tab NumTblTyp := NumTblTyp();
562 lv_bill_sequence_id_tab NumTblTyp := NumTblTyp();
563 l_count PLS_INTEGER;
564
565 BEGIN
566
567 l_index := i_parent_items.inventory_item_id.FIRST;
568
569
570 LOOP
571 l_count := 0;
572
573 lv_cursor_stmt :=
574 ' SELECT :g_organization_id '
575 ||' ,bic.component_quantity * :parent_planned_quantity,'
576 ||' bic.Component_Item_ID, '
577 ||' bic.component_quantity * :parent_quantity_completed,'
578 ||' :g_scheduled_completion_date,'
579 ||' :g_scheduled_start_date,'
580 ||' :g_wip_entity_id,'
581 ||' :i_level,'
582 ||' bic.WIP_SUPPLY_TYPE, '
583 ||' bom.bill_sequence_id,'
584 ||' :g_RN '
585 ||' FROM'
586 ||' MRP_SN_BOMS bom,'
587 ||' MRP_SN_INV_COMPS bic'
588 ||' WHERE'
589 ||' bom.assembly_item_id = :parent_inventory_item_id'
590 ||' AND bom.organization_id = :g_organization_id'
591 ||' AND bom.alternate_bom_designator IS NULL'
592 ||' AND bic.bill_sequence_id = bom.common_bill_sequence_id'
593 ||' AND bic.effectivity_date < :g_scheduled_completion_date'
594 ||' AND NVL(bic.disable_date, :g_scheduled_completion_date + 1) '
595 ||' > :g_scheduled_completion_date '
596 ||' AND bic.wip_supply_type <> 6';
597
598 OPEN lv_ins for lv_cursor_stmt USING
599 g_organization_id,
600 i_parent_items.planned_quantity(l_index),
601 i_parent_items.quantity_completed(l_index),
602 g_scheduled_completion_date,
606 g_RN,
603 g_scheduled_start_date,
604 g_wip_entity_id,
605 i_level,
607 i_parent_items.inventory_item_id(l_index),
608 g_organization_id,
609 g_scheduled_completion_date,
610 g_scheduled_completion_date,
611 g_scheduled_completion_date;
612 LOOP
613
614 FETCH lv_ins INTO
615 lv_organization_id,
616 lv_planned_quantity,
617 lv_primary_item_id,
618 lv_quantity_completed,
619 lv_scheduled_comp_date,
620 lv_scheduled_start_date,
621 lv_wip_entity_id,
622 lv_plan_level,
623 lv_wip_supply_type,
624 lv_bill_sequence_id,
625 lv_RN;
626
627 EXIT WHEN lv_ins%NOTFOUND;
628
629 l_count := l_count+1;
630
631 lv_organization_id_tab.EXTEND(1);
632 lv_planned_quantity_tab.EXTEND(1);
633 lv_primary_item_id_tab.EXTEND(1);
634 lv_quantity_completed_tab.EXTEND(1);
635 lv_scheduled_comp_date_tab.EXTEND(1);
636 lv_scheduled_start_date_tab.EXTEND(1);
637 lv_wip_entity_id_tab.EXTEND(1);
638 lv_plan_level_tab.EXTEND(1);
639 lv_wip_supply_type_tab.EXTEND(1);
640 lv_bill_sequence_id_tab.EXTEND(1);
641
642
643 lv_organization_id_tab(l_count) := lv_organization_id;
644 lv_planned_quantity_tab(l_count) := lv_planned_quantity;
645 lv_primary_item_id_tab(l_count) := lv_primary_item_id;
646 lv_quantity_completed_tab(l_count) := lv_quantity_completed;
647 lv_scheduled_comp_date_tab(l_count) := lv_scheduled_comp_date;
648 lv_scheduled_start_date_tab(l_count) := lv_scheduled_start_date;
649 lv_wip_entity_id_tab(l_count) := lv_wip_entity_id;
650 lv_plan_level_tab(l_count) := lv_plan_level;
651 lv_wip_supply_type_tab(l_count) := lv_wip_supply_type;
652 lv_bill_sequence_id_tab(l_count) := lv_bill_sequence_id;
653
654
655
656
657 END LOOP;
658
659 FORALL k IN 1..lv_organization_id_tab.count
660 INSERT INTO MRP_FLOW_DEMANDS(
661 organization_id,
662 planned_quantity,
663 primary_item_id,
664 quantity_completed,
665 scheduled_completion_date,
666 scheduled_start_date,
667 wip_entity_id,
668 plan_level,
669 wip_supply_type,
670 bill_sequence_id, -- bill identifier
671 RN)
672 VALUES(
673 lv_organization_id_tab(k),
674 lv_planned_quantity_tab(k),
675 lv_primary_item_id_tab(k),
676 lv_quantity_completed_tab(k),
677 lv_scheduled_comp_date_tab(k),
678 lv_scheduled_start_date_tab(k),
679 lv_wip_entity_id_tab(k),
680 lv_plan_level_tab(k),
681 lv_wip_supply_type_tab(k),
682 lv_bill_sequence_id_tab(k),
683 lv_RN);
684
685 EXIT WHEN l_index = i_parent_items.inventory_item_id.LAST;
686 l_index := i_parent_items.inventory_item_id.NEXT(l_index);
687
688 END LOOP;
689
690
691
692
693
694 /* This is to be made a dynamic query for New Patching Strategy
695 INSERT INTO MRP_FLOW_DEMANDS(
696 organization_id,
697 planned_quantity,
698 primary_item_id,
699 quantity_completed,
700 scheduled_completion_date,
701 scheduled_start_date,
702 wip_entity_id,
703 plan_level,
704 wip_supply_type,
705 bill_sequence_id, -- bill identifier
706 RN)
707 SELECT
708 g_organization_id,
709 bic.component_quantity * i_parent_items.planned_quantity(l_index),
710 bic.Component_Item_ID, -- i_parent_items.inventory_item_id(l_index)
711 bic.component_quantity * i_parent_items.quantity_completed(l_index),
712 g_scheduled_completion_date,
713 g_scheduled_start_date,
714 g_wip_entity_id,
715 i_level,
716 bic.WIP_SUPPLY_TYPE,
717 bom.bill_sequence_id,
718 g_RN
719 FROM
720 MRP_SN_BOMS bom,
721 MRP_SN_INV_COMPS bic
722 WHERE
723 bom.assembly_item_id = i_parent_items.inventory_item_id(l_index)
724 AND bom.organization_id = g_organization_id
725 AND bom.alternate_bom_designator IS NULL
726 AND bic.bill_sequence_id = bom.common_bill_sequence_id
727 AND bic.effectivity_date < g_scheduled_completion_date
728 AND NVL(bic.disable_date, g_scheduled_completion_date + 1)
729 > g_scheduled_completion_date
730 AND bic.wip_supply_type <> 6;
731 */
732
733
734
735
736 END Insert_Demands;
737
738
739 END MRP_FLOW_DEMAND;