1 PACKAGE BODY MRP_KANBAN_PLAN_PK AS
2 /* $Header: MRPKPLNB.pls 120.8 2006/05/26 01:47:08 ksuleman noship $ */
3
4 -- ========================================================================
5 -- This function will update the plan_start_date column in mrp_kanban_plans
6 -- table to indicate that the plan has started to run. The user will not
7 -- be able to query up this plan until the plan has finished running.
8 -- ========================================================================
9 FUNCTION START_KANBAN_PLAN
10 RETURN BOOLEAN IS
11
12 l_plan_id number;
13 l_bom_effectivity date;
14 l_start_date date;
15 l_cutoff_date date;
16
17 -- declare exceptions we want to handle here
18 exc_error_condition exception;
19 exc_replan_error exception;
20
21 BEGIN
22
23 g_stmt_num := 20;
24 IF g_debug THEN
25 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
26 MRP_UTIL.MRP_LOG (g_log_message);
27 g_log_message := 'Entering Start_Kanban_Plan function';
28 MRP_UTIL.MRP_LOG (g_log_message);
29 END IF;
30
31
32 -- ------------------------------------------------------------------------
33 -- Validate the plan information
34 -- ------------------------------------------------------------------------
35 SELECT kanban_plan_id,
36 input_type,
37 input_designator,
38 bom_effectivity_date,
39 plan_start_date,
40 plan_cutoff_date
41 INTO l_plan_id,
42 g_kanban_info_rec.input_type,
43 g_kanban_info_rec.input_designator,
44 l_bom_effectivity,
45 l_start_date,
46 l_cutoff_date
47 FROM mrp_kanban_plans
48 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
49
50 -- clean out data in some tables depending on whether we are
51 -- replanning or not
52
53 IF nvl(g_kanban_info_rec.replan_flag,2) = 1 THEN -- 1 is Yes, 2 is No
54
55 g_kanban_info_rec.bom_effectivity := l_bom_effectivity;
56 g_kanban_info_rec.start_date := l_start_date;
57 g_kanban_info_rec.cutoff_date := l_cutoff_date;
58
59 IF g_kanban_info_rec.bom_effectivity IS NULL OR
60 g_kanban_info_rec.start_date IS NULL OR
61 g_kanban_info_rec.cutoff_date IS NULL THEN
62 raise exc_replan_error;
63 END IF;
64
65 -- delete all the entries from the low level codes table except
66 -- entries list of kanban items that we had got in the very first
67 -- snapshot of items during a regular plan run
68
69 DELETE FROM mrp_low_level_codes
70 WHERE plan_id = g_kanban_info_rec.kanban_plan_id
71 AND organization_id = g_kanban_info_rec.organization_id
72 AND (levels_below <> 1 OR
73 assembly_item_id = component_item_id OR
74 assembly_item_id = -1 );
75
76 -- update the low level code of each of the remaining records to null
77 UPDATE mrp_low_level_codes
78 SET low_level_code = null
79 WHERE plan_id = g_kanban_info_rec.kanban_plan_id
80 AND organization_id = g_kanban_info_rec.organization_id;
81
82 ELSIF nvl(g_kanban_info_rec.replan_flag,2) = 2 THEN
83
84 DELETE FROM mrp_low_level_codes
85 WHERE plan_id = g_kanban_info_rec.kanban_plan_id
86 AND organization_id = g_kanban_info_rec.organization_id;
87
88 DELETE FROM mtl_kanban_pull_sequences
89 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
90
91 END IF;
92
93 DELETE FROM mrp_kanban_demand
94 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id
95 AND organization_id = g_kanban_info_rec.organization_id;
96
97 /* this part of the code was originally in plan_kanban procedure
98 but we moved it to here because we want to commit the snapshot
99 data to have less impact on rollback segments
100 So, as you will see we have put a commit after the call
101 to snapshot_item_locations procedure
102 */
103 -- call the procedure to snapshot the item/locations and
104 -- populate mrp_low_level_codes table.
105 -- Note that we are calculating low level codes
106 -- only to detect loops and not for planning purposes.
107 -- We gather demand by looking at the input to the plan
108 -- and then blow it down to the component item/locations
109
110 IF NOT mrp_kanban_snapshot_pk.snapshot_item_locations THEN
111 g_log_message := 'Error in SNAPSHOT_ITEM_LOCATIONS';
112 MRP_UTIL.MRP_LOG (g_log_message);
113 raise exc_error_condition;
114 END IF;
115
116 commit;
117
118 -- ------------------------------------------------------------------------
119 -- Update some plan information in mrp_kanban_plans table
120 -- ------------------------------------------------------------------------
121 UPDATE mrp_kanban_plans
122 SET plan_start_date = g_kanban_info_rec.start_date,
123 plan_completion_date = NULL,
124 bom_effectivity_date = g_kanban_info_rec.bom_effectivity,
125 plan_cutoff_date = g_kanban_info_rec.cutoff_date
126 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
127
128 RETURN TRUE;
129
130 EXCEPTION
131
132 WHEN exc_error_condition THEN
133 ROLLBACK;
134 g_log_message := 'Program encountered Error condition Exception';
135 MRP_UTIL.MRP_LOG (g_log_message);
136 RETURN FALSE;
137
138 WHEN exc_replan_error THEN
139 g_log_message := 'Incomplete Information For Replan';
140 MRP_UTIL.MRP_LOG (g_log_message);
141 Return FALSE;
142
143 WHEN NO_DATA_FOUND THEN
144 g_log_message := 'Invalid Kanban Plan Id';
145 MRP_UTIL.MRP_LOG (g_log_message);
146 Return FALSE;
147
148 WHEN OTHERS THEN
149 g_log_message := 'START_KANBAN_PLAN Sql Error ';
150 MRP_UTIL.MRP_LOG (g_log_message);
151 g_log_message := sqlerrm;
152 MRP_UTIL.MRP_LOG (g_log_message);
153 RETURN FALSE;
154
155 END START_KANBAN_PLAN;
156
157 -- ========================================================================
158 -- This function will update the plan_completion_date column in
159 -- mrp_kanban_plans table to indicate that the plan has successfully
160 -- finished.
161 -- ========================================================================
162 FUNCTION END_KANBAN_PLAN
163 RETURN BOOLEAN IS
164 BEGIN
165
166 IF g_debug THEN
167 g_log_message := 'In End_Kanban_Plan function';
168 fnd_file.put_line (fnd_file.log, g_log_message);
169 END IF;
170
171 -- ------------------------------------------------------------------------
172 -- Update the plan_completion_date to sysdate
173 -- ------------------------------------------------------------------------
174 UPDATE mrp_kanban_plans
175 SET plan_completion_date = sysdate
176 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
177
178 -- commit the changes to the database
179 COMMIT;
180
181 RETURN TRUE;
182
183 EXCEPTION
184
185 WHEN OTHERS THEN
186 g_log_message := 'END_KANBAN_PLAN Sql Error ';
187 MRP_UTIL.MRP_LOG (g_log_message);
188 g_log_message := sqlerrm;
189 MRP_UTIL.MRP_LOG (g_log_message);
190 RETURN FALSE;
191
192 END END_KANBAN_PLAN;
193
194 -- ========================================================================
195 -- this function gets the # of working days between start and end dates
196 -- for a MDS and mulitplies that into the repetitive demnad
197 -- this is done only for the MDS and repetitive items.
198 -- ========================================================================
199
200 FUNCTION Get_Repetitive_Demand(
201 p_schedule_date IN DATE,
202 p_rate_end_date IN DATE,
203 p_repetitive_daily_rate IN NUMBER)
204 RETURN NUMBER IS
205 l_days NUMBER :=0;
206
207 CURSOR c1 IS
208 SELECT count(*) count
209 FROM bom_calendar_dates bcd,
210 mtl_parameters mp
211 WHERE mp.organization_id = g_kanban_info_rec.organization_id
212 AND bcd.calendar_code = mp.calendar_code
213 AND bcd.exception_set_id = mp.calendar_exception_set_id
214 AND bcd.calendar_date between
215 p_schedule_date and p_rate_end_date
216 AND bcd.seq_num IS NOT NULL;
217 BEGIN
218
219 IF NOT c1%ISOPEN THEN
220 OPEN c1;
221 END IF;
222 FETCH c1
223 INTO l_days;
224 l_days:= l_days * p_repetitive_daily_rate;
225 IF c1%ISOPEN THEN
226 CLOSE c1;
227 END IF;
228
229 RETURN l_days;
230 END Get_Repetitive_Demand;
231
232
233 -- ========================================================================
234 -- This function finds out if we need to prorate forecast demand and if
235 -- necessary prorates the demand for a weekly or periodic forecast, ie., when
236 -- a part of which falls outside the kanban plan start and cutoff dates
237 -- ========================================================================
238
239 FUNCTION Get_Prorated_Demand (
240 p_bucket_type IN number,
241 p_in_demand_date IN date,
242 p_in_rate_end_date IN date,
243 p_in_demand_qty IN number,
244 p_out_demand_date IN OUT NOCOPY date,
245 p_out_demand_qty IN OUT NOCOPY number
246 )
247 RETURN BOOLEAN
248 IS
249
250 l_total_workdays number;
251 l_current_workdays number;
252 l_demand_quantity number;
253 l_demand_date date;
254 l_next_date date;
255
256 BEGIN
257
258 IF g_debug THEN
259 g_log_message := 'Entering Get_Prorated_Demand Function';
260 MRP_UTIL.MRP_LOG (g_log_message);
261 g_log_message := 'Bucket_Type : ' || to_char (p_bucket_type);
262 MRP_UTIL.MRP_LOG (g_log_message);
263 g_log_message := 'In demand date : ' ||
264 fnd_date.date_to_canonical(p_in_demand_date);
265 MRP_UTIL.MRP_LOG (g_log_message);
266 g_log_message := 'In Rate end date : ' ||
267 fnd_date.date_to_canonical(p_in_rate_end_date);
268 MRP_UTIL.MRP_LOG (g_log_message);
269 g_log_message := 'In demand qty : ' || to_char (p_in_demand_qty);
270 MRP_UTIL.MRP_LOG (g_log_message);
271 END IF;
272 --initialize
273
274 l_demand_date := p_in_demand_date;
275 l_next_date := p_in_rate_end_date;
276 p_out_demand_date := p_in_demand_date;
277 p_out_demand_qty := p_in_demand_qty;
278
279 -- first find out if we need to prorate the demand or not
280
281 IF p_bucket_type = 1 THEN
282 -- daily forecast, no need to prorate
283 RETURN TRUE;
284
285 ELSIF p_bucket_type = 2 OR p_bucket_type = 3 THEN
286
287 IF p_in_rate_end_date IS NULL AND
288 (p_in_demand_date BETWEEN g_kanban_info_rec.start_date AND
289 Get_Offset_Date (g_kanban_info_rec.cutoff_date,
290 p_bucket_type )) THEN
291 --no need to prorate
292 RETURN TRUE;
293 ELSIF p_in_rate_end_date IS NOT NULL AND
294 (p_in_demand_date >= g_kanban_info_rec.start_date AND
295 p_in_rate_end_date <= Get_Offset_Date (
296 g_kanban_info_rec.cutoff_date,
297 p_bucket_type )) THEN
298 --no need to prorate
299 RETURN TRUE;
300
301 END IF;
302
303 END IF;
304
305 --If we come here then it means that we have to prorate the demand
306
307 --First Get the total number of workdays in the period we are
308 --about to consider
309
310 IF p_bucket_type = 2 THEN
311
312 SELECT count(*)
313 INTO l_total_workdays
314 FROM bom_calendar_dates cd,
315 bom_cal_week_start_dates ws,
316 mtl_parameters mp
317 WHERE mp.organization_id = g_kanban_info_rec.organization_id
318 AND ws.calendar_code = mp.calendar_code
319 AND ws.exception_set_id = mp.calendar_exception_set_id
320 AND ws.week_start_date = l_demand_date
321 AND cd.calendar_code = ws.calendar_code
322 AND cd.exception_set_id = ws.exception_set_id
323 AND (cd.calendar_date BETWEEN ws.week_start_date AND
324 ws.next_date)
325 AND cd.seq_num IS NOT NULL;
326
327 ELSIF p_bucket_type = 3 THEN
328
329 SELECT count(*)
330 INTO l_total_workdays
331 FROM bom_calendar_dates cd,
332 bom_period_start_dates ps,
333 mtl_parameters mp
334 WHERE mp.organization_id = g_kanban_info_rec.organization_id
335 AND ps.calendar_code = mp.calendar_code
336 AND ps.exception_set_id = mp.calendar_exception_set_id
337 AND ps.period_start_date = l_demand_date
338 AND cd.calendar_code = ps.calendar_code
339 AND cd.exception_set_id = ps.exception_set_id
340 AND (cd.calendar_date BETWEEN ps.period_start_date AND
341 ps.next_date)
342 AND cd.seq_num IS NOT NULL;
343
344 END IF;
345
346 -- alter the demand_date if necessary
347 IF l_demand_date < g_kanban_info_rec.start_date THEN
348 l_demand_date := g_kanban_info_rec.start_date;
349 END IF;
350
351 -- similarly alter the next_date if necessary
352 -- first get the next_date if it is null
353
354 IF l_next_date IS NULL THEN -- which it can be for non repetitive forecasts
355 IF p_bucket_type = 2 THEN
356
357 SELECT bw.next_date
358 INTO l_next_date
359 FROM bom_cal_week_start_dates bw,
360 mtl_parameters mp
361 WHERE mp.organization_id = g_kanban_info_rec.organization_id
362 AND bw.calendar_code = mp.calendar_code
363 AND bw.exception_set_id = mp.calendar_exception_set_id
364 AND bw.week_start_date <= l_demand_date
365 AND bw.next_date >= l_demand_date;
366
367 ELSIF p_bucket_type = 3 THEN
368
369 SELECT bp.next_date
370 INTO l_next_date
371 FROM bom_period_start_dates bp,
372 mtl_parameters mp
373 WHERE mp.organization_id = g_kanban_info_rec.organization_id
374 AND bp.calendar_code = mp.calendar_code
375 AND bp.exception_set_id = mp.calendar_exception_set_id
376 AND bp.period_start_date <= l_demand_date
377 AND bp.next_date >= l_demand_date;
378
379 END IF;
380
381 END IF;
382
383 IF l_next_date > g_kanban_info_rec.cutoff_date THEN
384 l_next_date := g_kanban_info_rec.cutoff_date;
385 END IF;
386
387 -- now calculate the number of workdays for which we have to
388 -- consider the demand out of the weekly/periodic forecast we
389 -- are working with
390
391 SELECT count(*)
392 INTO l_current_workdays
393 FROM bom_calendar_dates cd,
394 mtl_parameters mp
395 WHERE mp.organization_id = g_kanban_info_rec.organization_id
396 AND cd.calendar_code = mp.calendar_code
397 AND cd.exception_set_id = mp.calendar_exception_set_id
398 AND (cd.calendar_date BETWEEN l_demand_date AND l_next_date)
399 AND cd.seq_num IS NOT NULL;
400
401 -- once we mucked around with the dates, we have to arrive at the
402 -- correct demand quantity for the length of the week/period
403 l_demand_quantity := (l_current_workdays/l_total_workdays) *
404 p_in_demand_qty;
405
406 p_out_demand_date := l_demand_date;
407 p_out_demand_qty := l_demand_quantity;
408
409 IF g_debug THEN
410 g_log_message := 'Current workdays : ' || to_char (l_current_workdays);
411 MRP_UTIL.MRP_LOG (g_log_message);
412 g_log_message := 'Total Workdays : ' || to_char (l_total_workdays);
413 MRP_UTIL.MRP_LOG (g_log_message);
414 g_log_message := 'Out Demand Date : ' || to_char (l_demand_date);
415 MRP_UTIL.MRP_LOG (g_log_message);
416 g_log_message := 'Out Demand qty : ' || to_char (l_demand_quantity);
417 MRP_UTIL.MRP_LOG (g_log_message);
418 g_log_message := 'Exiting Get_Prorated_Demand Function';
419 MRP_UTIL.MRP_LOG (g_log_message);
420 END IF;
421
422 RETURN TRUE;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 g_log_message := 'GET_PRORATED_DEMAND Sql Error ';
427 MRP_UTIL.MRP_LOG (g_log_message);
428 g_log_message := sqlerrm;
429 MRP_UTIL.MRP_LOG (g_log_message);
430 RETURN FALSE;
431
432 END Get_Prorated_Demand;
433
434 -- ========================================================================
435 -- this function explodes the demand for repetitive forecast entries
436 -- ========================================================================
437
438 FUNCTION Explode_Repetitive_Forecast (
439 p_inventory_item_id IN number,
440 p_demand_quantity IN number,
441 p_demand_date IN date,
442 p_rate_end_date IN date,
443 p_bucket_type IN number,
444 p_demand_type IN number,
445 p_line_id IN number,
446 p_item_sub_inventory IN VARCHAR2,
447 p_item_locator IN number,
448 p_parent_sub_inventory IN VARCHAR2,
449 p_parent_locator IN number,
450 p_parent_item_id IN number,
451 p_kanban_item_flag IN VARCHAR2,
452 insert_or_cascade IN boolean)
453
454
455 RETURN BOOLEAN IS
456
457 l_rate_end_date date;
458 l_rate_start_date date;
459 l_demand_date date;
460 l_next_date date;
461 l_demand_quantity number;
462 l_current_workdays number;
463 l_total_workdays number;
464 l_line_id number;
465 l_ret_val boolean;
466
467 -- cursor for repetitive periodic forecast dates
468 cursor cur_periodic_forecasts is
469 SELECT bp.period_start_date, bp.next_date
470 FROM bom_period_start_dates bp, mtl_parameters mp
471 WHERE mp.organization_id = g_kanban_info_rec.organization_id
472 AND bp.calendar_code = mp.calendar_code
473 AND bp.exception_set_id = mp.calendar_exception_set_id
474 AND (bp.period_start_date BETWEEN p_demand_date AND
475 p_rate_end_date);
476
477 -- cursor for repetitive weekly forecast dates
478 cursor cur_weekly_forecasts is
479 SELECT bw.week_start_date, bw.next_date
480 FROM bom_cal_week_start_dates bw, mtl_parameters mp
481 WHERE mp.organization_id = g_kanban_info_rec.organization_id
482 AND bw.calendar_code = mp.calendar_code
483 AND bw.exception_set_id = mp.calendar_exception_set_id
484 AND (bw.week_start_date BETWEEN p_demand_date AND
485 p_rate_end_date);
486
487 -- cursor for repetitive daily forecast dates
488 cursor cur_daily_forecasts is
489 SELECT bcd.calendar_date
490 FROM bom_calendar_dates bcd, mtl_parameters mp
491 WHERE mp.organization_id = g_kanban_info_rec.organization_id
492 AND bcd.calendar_code = mp.calendar_code
493 AND bcd.exception_set_id = mp.calendar_exception_set_id
494 AND (bcd.calendar_date BETWEEN l_rate_start_date AND
495 l_rate_end_date)
496 AND bcd.seq_num is not null;
497
498 BEGIN
499
500 IF g_debug THEN
501 g_log_message := 'Entering Explode_Repetitive_Forecast Function';
502 MRP_UTIL.MRP_LOG (g_log_message);
503 END IF;
504
505 -- some constraints we have to consider for daily forecasts. The
506 -- processing for weekly and periodic forecasts is done later
507
508 -- check from what date we need to consider the daily repetitive forecast
509 IF p_demand_date > g_kanban_info_rec.start_date THEN
510 l_rate_start_date := p_demand_date;
511 ELSE
512 l_rate_start_date := g_kanban_info_rec.start_date;
513 END IF;
514
515 -- check upto what date we need to consider the daily repetitive forecast
516 IF p_rate_end_date > g_kanban_info_rec.cutoff_date THEN
517 l_rate_end_date := g_kanban_info_rec.cutoff_date;
518 ELSE
519 l_rate_end_date := p_rate_end_date;
520 END IF;
521
522 WHILE TRUE LOOP
523
524 -- Depending on the bucket type go after the respective cursor
525
526 IF g_debug THEN
527 g_log_message := 'Going in while loop inside Explode_Repetitive_Forecast';
528 MRP_UTIL.MRP_LOG (g_log_message);
529 END IF;
530
531 IF p_bucket_type = 1 THEN -- daily buckets
532
533 IF NOT cur_daily_forecasts%ISOPEN THEN
534 OPEN cur_daily_forecasts;
535 END IF;
536
537 FETCH cur_daily_forecasts
538 INTO l_demand_date;
539
540 EXIT WHEN cur_daily_forecasts%NOTFOUND;
541
542 ELSIF p_bucket_type = 2 THEN -- weekly buckets
543
544 IF NOT cur_weekly_forecasts%ISOPEN THEN
545 OPEN cur_weekly_forecasts;
546 END IF;
547
548 FETCH cur_weekly_forecasts
549 INTO l_demand_date,
550 l_next_date;
551
552 EXIT WHEN cur_weekly_forecasts%NOTFOUND;
553
554 ELSIF p_bucket_type = 3 THEN -- periodic buckets
555
556 IF NOT cur_periodic_forecasts%ISOPEN THEN
557 OPEN cur_periodic_forecasts;
558 END IF;
559
560 FETCH cur_periodic_forecasts
561 INTO l_demand_date,
562 l_next_date;
563
564 EXIT WHEN cur_periodic_forecasts%NOTFOUND;
565
566 END IF;
567
568 IF g_debug THEN
569 g_log_message := 'Done with the buckets';
570 MRP_UTIL.MRP_LOG (g_log_message);
571 END IF;
572
573 -- we need to do some extra work for weekly and periodic
574 -- forecasts in order to figure out the correct demand in the
575 -- specified period.
576
577 IF p_bucket_type = 2 OR p_bucket_type = 3 THEN
578 -- Call the prorating function
579 IF g_debug THEN
580 g_log_message := 'Call the prorating function';
581 MRP_UTIL.MRP_LOG (g_log_message);
582 END IF;
583
584 IF NOT Get_Prorated_Demand (
585 p_bucket_type,
586 l_demand_date,
587 l_next_date,
588 p_demand_quantity,
589 l_demand_date,
590 l_demand_quantity ) THEN
591 RETURN FALSE;
592 END IF;
593
594 ELSIF p_bucket_type = 1 THEN
595 l_demand_quantity := p_demand_quantity;
596 END IF;
597
598 -- now call the function to insert the demand and explode it through
599 -- to the bottom of the bill
600
601 IF (p_line_id is NULL) THEN
602 Begin
603 SELECT line_id
604 INTO l_line_id
605 FROM bom_operational_routings
606 WHERE alternate_routing_designator is NULL
607 AND assembly_item_id = p_inventory_item_id
608 AND organization_id = g_kanban_info_rec.organization_id;
609 Exception
610 When Others Then
611 Null;
612 End;
613 ELSE
614 l_line_id := p_line_id;
615 END IF;
616 IF g_debug THEN
617 g_log_message := 'Inserting into MRP_KANBAN_DEMAND';
618 MRP_UTIL.MRP_LOG (g_log_message);
619 g_log_message := 'LineN : ' || to_char(l_line_id);
620 MRP_UTIL.MRP_LOG (g_log_message);
621 g_log_message := 'in erd ItemN : ' || to_char(p_inventory_item_id);
622 MRP_UTIL.MRP_LOG (g_log_message);
623 g_log_message := 'in erd demand date: ' || to_char(l_demand_date);
624 MRP_UTIL.MRP_LOG (g_log_message);
625 g_log_message := 'in erd demand quant: ' || to_char(l_demand_quantity);
626 MRP_UTIL.MRP_LOG (g_log_message);
627
628 END IF;
629
630
631 IF l_demand_quantity > 0 THEN
632
633 IF INSERT_OR_CASCADE = TRUE THEN
634 INSERT INTO MRP_KANBAN_DEMAND (
635 DEMAND_ID,
636 KANBAN_PLAN_ID,
637 ORGANIZATION_ID,
638 INVENTORY_ITEM_ID,
639 SUBINVENTORY,
640 LOCATOR_ID,
641 ASSEMBLY_ITEM_ID,
642 ASSEMBLY_ORG_ID,
643 ASSEMBLY_SUBINVENTORY,
644 ASSEMBLY_LOCATOR_ID,
645 DEMAND_DATE,
646 DEMAND_QUANTITY,
647 ORDER_TYPE,
648 KANBAN_ITEM_FLAG,
649 REQUEST_ID,
650 PROGRAM_APPLICATION_ID,
651 PROGRAM_ID,
652 PROGRAM_UPDATE_DATE,
653 LAST_UPDATE_DATE,
654 LAST_UPDATED_BY,
655 CREATION_DATE,
656 CREATED_BY )
657 SELECT
658 mrp_kanban_demand_s.nextval,
659 g_kanban_info_rec.kanban_plan_id,
660 g_kanban_info_rec.organization_id,
661 p_inventory_item_id,
662 ps.subinventory_name,
663 ps.locator_id,
664 NULL,
665 NULL,
666 NULL,
667 NULL,
668 l_demand_date,
669 (NVL(ps.allocation_percent, 100) *
670 l_demand_quantity/ 100),
671 p_demand_type,
672 'Y',
673 fnd_global.conc_request_id,
674 fnd_global.prog_appl_id,
675 fnd_global.conc_program_id,
676 sysdate,
677 sysdate,
678 fnd_global.user_id,
679 sysdate,
680 fnd_global.user_id
681 FROM
682 mtl_kanban_pull_sequences ps
683 WHERE ps.wip_line_id = l_line_id
684 AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
685 AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
686 2, G_PRODUCTION_KANBAN,
687 1, g_kanban_info_rec.kanban_plan_id,
688 G_PRODUCTION_KANBAN)
689 AND ps.inventory_item_id = p_inventory_item_id
690 AND ps.organization_id = g_kanban_info_rec.organization_id;
691 ELSE/* its cascade*/
692 INSERT INTO MRP_KANBAN_DEMAND (
693 DEMAND_ID,
694 KANBAN_PLAN_ID,
695 ORGANIZATION_ID,
696 INVENTORY_ITEM_ID,
697 SUBINVENTORY,
698 LOCATOR_ID,
699 ASSEMBLY_ORG_ID,
700 ASSEMBLY_ITEM_ID,
701 ASSEMBLY_SUBINVENTORY,
702 ASSEMBLY_LOCATOR_ID,
703 DEMAND_DATE,
704 DEMAND_QUANTITY,
705 ORDER_TYPE,
706 KANBAN_ITEM_FLAG,
707 REQUEST_ID,
708 PROGRAM_APPLICATION_ID,
709 PROGRAM_ID,
710 PROGRAM_UPDATE_DATE,
711 LAST_UPDATE_DATE,
712 LAST_UPDATED_BY,
713 CREATION_DATE,
714 CREATED_BY )
715 SELECT
716 mrp_kanban_demand_s.nextval,
717 g_kanban_info_rec.kanban_plan_id,
718 g_kanban_info_rec.organization_id,
719 p_inventory_item_id,
720 p_item_sub_inventory,
721 p_item_locator,
722 g_kanban_info_rec.organization_id,
723 p_parent_item_id,
724 p_parent_sub_inventory,
725 p_parent_locator,
726 l_demand_date,
727 l_demand_quantity,
728 p_demand_type,
729 p_kanban_item_flag,
730 fnd_global.conc_request_id,
731 fnd_global.prog_appl_id,
732 fnd_global.conc_program_id,
733 sysdate,
734 sysdate,
735 fnd_global.user_id,
736 sysdate,
737 fnd_global.user_id
738 FROM
739 DUAL;
740
741 END IF;
742 END IF;--demand > 0
743 END LOOP;
744
745 -- close whatever cursor is open
746 IF cur_daily_forecasts%ISOPEN THEN
747 CLOSE cur_daily_forecasts;
748 ELSIF cur_weekly_forecasts%ISOPEN THEN
749 CLOSE cur_weekly_forecasts;
750 ELSIF cur_periodic_forecasts%ISOPEN THEN
751 CLOSE cur_periodic_forecasts;
752 END IF;
753
754 RETURN TRUE;
755
756 --exception handling
757 EXCEPTION
758 WHEN OTHERS THEN
759 g_log_message := 'EXPLODE_REPETITIVE_FORECAST Sql Error ';
760 MRP_UTIL.MRP_LOG (g_log_message);
761 g_log_message := sqlerrm;
762 MRP_UTIL.MRP_LOG (g_log_message);
763 RETURN FALSE;
764 END Explode_Repetitive_Forecast;
765
766 -- ========================================================================
767 -- This function passes demand down to the components all the way down
768 -- to the bottom of the bill for the FORECAST/Actual Production
769 -- ========================================================================
770 FUNCTION Cascade_Fcst_Demand(
771 p_bill_or_ps IN NUMBER,
772 p_recursive IN BOOLEAN,
773 p_parent_line_id IN NUMBER,
774 p_line_id IN NUMBER,
775 p_top_item_id IN NUMBER,
776 p_assembly_item_id IN NUMBER,
777 p_cumulative_usage IN NUMBER,
778 p_subinventory IN VARCHAR2,
779 p_locator_id IN NUMBER,
780 p_demand_type IN NUMBER,
781 p_explode_always IN VARCHAR2,
782 p_sales_order_demand IN VARCHAR2,
783 p_assy_foq IN NUMBER )
784
785 RETURN BOOLEAN IS
786
787 --declare some local variables here
788 l_bill_or_ps number; -- 1 - bill; 2 - pull sequence
789
790 l_component_id number;
791 l_subinventory varchar2(10);
792 l_locator_id number;
793 l_component_usage number;
794 l_component_yield number;
795 l_operation_yield number;
796 l_net_planning_percent number;
797 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
798 l_planning_factor number;
799 l_item_num number;
800 /* End of Update */
801 l_kanban_item_flag varchar2(1);
802 l_demand_quantity number;
803 l_ret_val boolean;
804
805 l_line_id number;
806 l_forecast_quantity number;
807 l_forecast_date date;
808 l_rate_end_date date;
809 l_bucket_type number;
810 l_running_total_quantity number := 0;
811 l_cumulative_usage number;
812 l_wip_supply_type number;
813 l_basis_type number;
814 l_comp_foq number;
815 l_foq number;
816
817
818 CURSOR parent_schedule_entries IS
819 SELECT current_forecast_quantity,
820 forecast_date,
821 rate_end_date,
822 bucket_type
823 FROM mrp_forecast_dates
824 WHERE organization_id = g_kanban_info_rec.organization_id
825 AND ((forecast_designator = g_kanban_info_rec.input_designator) or
826 (forecast_designator in ( -- forecast set
827 select forecast_designator
828 from mrp_forecast_designators
829 where forecast_set = g_kanban_info_rec.input_designator)
830 )
831 )
832 AND inventory_item_id = p_top_item_id
833 AND origination_type = p_demand_type
834 AND nvl(line_id,0) = nvl(p_parent_line_id,0)
835 AND ((rate_end_date IS NULL AND
836 forecast_date BETWEEN Get_Offset_Date(
837 g_kanban_info_rec.start_date,
838 bucket_type )
839 AND g_kanban_info_rec.cutoff_date) OR
840 (rate_end_date is NOT NULL AND NOT
841 (rate_end_date < Get_Offset_Date(
842 g_kanban_info_rec.start_date,
843 bucket_type ) OR
844 forecast_date > g_kanban_info_rec.cutoff_date)));
845
846
847 -- cursor component_cursor1 is the cursor that passes down demand
848 -- to the components feeding into a line. Notice that we are driving
849 -- off of bom_operational_routings
850 -- also if supply sub and locator are null in bom_inventory_components
851 -- we get it from wip supply locations from mtl_system_items - this
852 -- is ok for R-11, though we might have an issue here for R12
853
854 CURSOR component_cursor1 IS
855 SELECT DISTINCT
856 bic.component_item_id,
857 decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
858 bic.supply_subinventory),
859 decode(bic.supply_locator_id, NULL, msi.wip_supply_locator_id,
860 bic.supply_locator_id),
861 bic.component_quantity,
862 bic.component_yield_factor,
863 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
864 bic.planning_factor,
865 bic.item_num,
866 /* End of Update */
867 mllc.operation_yield,
868 mllc.net_planning_percent,
869 mllc.kanban_item_flag,
870 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
871 mllc.wip_supply_type,
872 mllc.basis_type,
873 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
874 FROM mtl_system_items msi,
875 mrp_low_level_codes mllc,
876 bom_inventory_components bic,
877 bom_bill_of_materials bbom,
878 bom_operational_routings bor,
879 mtl_parameters mp
880 WHERE mp.organization_id = g_kanban_info_rec.organization_id
881 AND bor.line_id (+) = p_line_id
882 AND bor.assembly_item_id (+) = p_assembly_item_id
883 AND bor.organization_id (+) = mp.organization_id
884 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
885 bor.assembly_item_id,
886 bor.organization_id,
887 bor.line_id,
888 bor.alternate_routing_designator)
889 /* BUG: 1668867 Double kanban demand */
890 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
891 p_assembly_item_id,
892 bic.component_item_id,
893 mp.organization_id)
894 AND bbom.assembly_item_id = p_assembly_item_id
895 AND bbom.organization_id = mp.organization_id
896 AND nvl(bbom.alternate_bom_designator, 'xxx') =
897 nvl(bor.alternate_routing_designator, 'xxx')
898 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
899 AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
900 >= g_kanban_info_rec.bom_effectivity
901 AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
902 AND NOT EXISTS (
903 SELECT NULL
904 FROM bom_inventory_components bic2
905 WHERE bic2.bill_sequence_id = bic.bill_sequence_id
906 AND bic2.component_item_id = bic.component_item_id
907 AND (decode(bic2.implementation_date, null,
908 bic2.old_component_sequence_id,
909 bic2.component_sequence_id) =
910 decode(bic.implementation_date, null,
911 bic.old_component_sequence_id,
912 bic.component_sequence_id)
913 OR bic2.operation_seq_num = bic.operation_seq_num)
914 AND bic2.effectivity_date <=
915 g_kanban_info_rec.bom_effectivity
916 AND bic2.effectivity_date > bic.effectivity_date
917 AND (bic2.implementation_date is not null OR
918 (bic2.implementation_date is null AND EXISTS
919 (SELECT NULL
920 FROM eng_revised_items eri
921 WHERE bic2.revised_item_sequence_id =
922 eri.revised_item_sequence_id
923 AND eri.mrp_active = 1 ))))
924 AND (bic.implementation_date is not null OR
925 (bic.implementation_date is null AND EXISTS
926 (SELECT NULL
927 FROM eng_revised_items eri
928 WHERE bic.revised_item_sequence_id =
929 eri.revised_item_sequence_id
930 AND eri.mrp_active = 1 )))
931 AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
932 AND mllc.organization_id = bbom.organization_id
933 AND mllc.assembly_item_id = bbom.assembly_item_id
934 AND mllc.component_item_id = bic.component_item_id
935 AND nvl(mllc.alternate_designator, 'xxx') =
936 nvl(bbom.alternate_bom_designator, 'xxx')
937 AND msi.inventory_item_id = mllc.component_item_id
938 AND msi.organization_id = mllc.organization_id
939 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
940 OR p_explode_always = 'Y'
941 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
942
943 -- cursor component_cursor2 blows down demand to the components
944 -- as stored in the mrp_low_level_codes table
945 CURSOR component_cursor2 IS
946 SELECT DISTINCT
947 mllc.component_item_id,
948 mllc.from_subinventory,
949 mllc.from_locator_id,
950 mllc.component_usage,
951 mllc.component_yield,
952 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
953 mllc.planning_factor,
954 mllc.item_num,
955 /* End of Update */
956 mllc.operation_yield,
957 mllc.net_planning_percent,
958 mllc.kanban_item_flag,
959 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
960 mllc.wip_supply_type,
961 mllc.basis_type,
962 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
963 FROM mtl_system_items msi,
964 mrp_low_level_codes mllc
965 WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
966 AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
967 AND mllc.assembly_item_id = p_assembly_item_id
968 AND ((mllc.to_subinventory = p_subinventory
969 AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
970 (mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
971 AND msi.inventory_item_id = mllc.component_item_id
972 AND msi.organization_id = mllc.organization_id
973 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
974 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
975 mllc.assembly_item_id,
976 mllc.organization_id,
977 null,
978 mllc.alternate_designator)
979 /* End of Update */
980 /* BUG 1668867, Double Kanban demand problem */
981 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
982 p_assembly_item_id,
983 mllc.component_item_id,
984 mllc.organization_id)
985 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
986 OR p_explode_always = 'Y'
987 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
988
989 BEGIN
990
991 IF g_debug THEN
992
993 g_log_message := 'Entering Cascade_Fcst_Demand function';
994 MRP_UTIL.MRP_LOG (g_log_message);
995 g_log_message := 'Cascading Demand For : ';
996 MRP_UTIL.MRP_LOG (g_log_message);
997 g_log_message := 'Line : ' || to_char(p_line_id);
998 MRP_UTIL.MRP_LOG (g_log_message);
999 g_log_message := 'Item : ' || to_char(p_assembly_item_id);
1000 MRP_UTIL.MRP_LOG (g_log_message);
1001 g_log_message := 'Sub : ' || p_subinventory;
1002 MRP_UTIL.MRP_LOG (g_log_message);
1003 g_log_message := 'Loc : ' || p_locator_id;
1004 MRP_UTIL.MRP_LOG (g_log_message);
1005 END IF;
1006
1007 -- Depending on the boolean flag p_recursive, we decide what cursor
1008 -- we want to go after. We know that we will have a line reference
1009 -- on the demand. So, when we call Cacade_Demand the first time, ie
1010 -- to pass down demand to components feeding into the line, then
1011 -- p_recursive is false. We just blow the demand down one level. Once
1012 -- we do that, we call Cascade_Fcst_Ap_Demand in the recursive mode with
1013 -- p_recursive set to true when we want to go after mrp_low_level_codes
1014 -- recursively and blow the demand all the way down.
1015
1016 IF NOT p_recursive THEN
1017 IF NOT component_cursor1%ISOPEN THEN
1018 OPEN component_cursor1;
1019 END IF;
1020
1021 ELSE
1022
1023 IF NOT component_cursor2%ISOPEN THEN
1024 OPEN component_cursor2;
1025 END IF;
1026 END IF;
1027
1028 WHILE TRUE LOOP
1029 IF not p_recursive THEN
1030 FETCH component_cursor1
1031 INTO l_component_id,
1032 l_subinventory,
1033 l_locator_id,
1034 l_component_usage,
1035 l_component_yield,
1036 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1037 l_planning_factor,
1038 l_item_num,
1039 /* End of Update */
1040 l_operation_yield,
1041 l_net_planning_percent,
1042 l_kanban_item_flag,
1043 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1044 l_wip_supply_type,
1045 l_basis_type,
1046 l_comp_foq;
1047 EXIT WHEN component_cursor1%NOTFOUND;
1048
1049 ELSE
1050
1051 FETCH component_cursor2
1052 INTO l_component_id,
1053 l_subinventory,
1054 l_locator_id,
1055 l_component_usage,
1056 l_component_yield,
1057 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1058 l_planning_factor,
1059 l_item_num,
1060 /* End of Update */
1061 l_operation_yield,
1062 l_net_planning_percent,
1063 l_kanban_item_flag,
1064 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1065 l_wip_supply_type,
1066 l_basis_type,
1067 l_comp_foq;
1068 EXIT WHEN component_cursor2%NOTFOUND;
1069
1070
1071 END IF;
1072
1073 IF g_debug THEN
1074 g_log_message := 'component_usage is : ' || to_char (l_component_usage);
1075 MRP_UTIL.MRP_LOG (g_log_message);
1076 g_log_message := 'Net_Planning_Percent is : ' ||
1077 to_char (l_net_planning_percent);
1078 MRP_UTIL.MRP_LOG (g_log_message);
1079 g_log_message := 'Operation_Yield is : ' || to_char (l_operation_yield);
1080 MRP_UTIL.MRP_LOG (g_log_message);
1081 g_log_message := 'Component_Yield is : ' || to_char (l_component_yield);
1082 MRP_UTIL.MRP_LOG (g_log_message);
1083
1084 g_log_message := 'Sub: ' || l_subinventory;
1085 MRP_UTIL.MRP_LOG (g_log_message);
1086 END IF;
1087
1088
1089 -- calculate the demand quantity to be passed down using all the
1090 -- percentages and yields stuff that we've got
1091
1092 IF NOT parent_schedule_entries%ISOPEN THEN
1093 OPEN parent_schedule_entries;
1094 END IF;
1095 WHILE TRUE LOOP
1096
1097 FETCH parent_schedule_entries
1098 INTO l_forecast_quantity,
1099 l_forecast_date,
1100 l_rate_end_date,
1101 l_bucket_type;
1102 EXIT WHEN parent_schedule_entries%NOTFOUND;
1103
1104 IF l_rate_end_date IS NULL THEN
1105 -- not a repetitive forecast - simple processing
1106
1107 IF l_bucket_type = 2 OR l_bucket_type = 3 THEN
1108 --Call the pro-rating function
1109 IF NOT Get_Prorated_Demand (
1110 l_bucket_type,
1111 l_forecast_date,
1112 l_rate_end_date,
1113 l_forecast_quantity,
1114 l_forecast_date,
1115 l_forecast_quantity) THEN
1116 RETURN FALSE;
1117 END IF;
1118 END IF;
1119
1120 l_running_total_quantity := l_running_total_quantity + nvl(
1121 l_forecast_quantity,0);
1122
1123 /* Added for lot based material support
1124 The p_assy_foq is the fixed order quantity of the assembly.
1125 It can be either the foq from the item master or it's parent (for phantom assembly).
1126 The p_assy_foq will be used to calculate the component's demand when the
1127 component has lot basis type and the demand is not from the pull sequence chain. */
1128 if (l_basis_type = WIP_CONSTANTS.LOT_BASED_MTL and l_component_id <> p_assembly_item_id) then
1129 l_foq := p_assy_foq;
1130 else
1131 l_foq := 1;
1132 end if;
1133
1134 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1135 /* l_demand_quantity := ROUND((l_forecast_quantity* nvl(l_component_usage, 1) *
1136 nvl(p_cumulative_usage,1)*
1137 (nvl(l_net_planning_percent, 100) /100)) /
1138 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
1139 l_demand_quantity := ((l_forecast_quantity* nvl(l_component_usage, 1) *
1140 nvl(p_cumulative_usage,1)*
1141 (nvl(l_planning_factor, 100) /100)) /
1142 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)))/l_foq;
1143 /* End of Update */
1144
1145 IF g_debug THEN
1146 g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
1147 MRP_UTIL.MRP_LOG (g_log_message);
1148 END IF;
1149
1150 -- now insert the demand into the kanban demand table if its > 0
1151
1152 IF l_demand_quantity > 0 THEN
1153
1154 INSERT INTO MRP_KANBAN_DEMAND (
1155 DEMAND_ID,
1156 KANBAN_PLAN_ID,
1157 ORGANIZATION_ID,
1158 INVENTORY_ITEM_ID,
1159 SUBINVENTORY,
1160 LOCATOR_ID,
1161 ASSEMBLY_ORG_ID,
1162 ASSEMBLY_ITEM_ID,
1163 ASSEMBLY_SUBINVENTORY,
1164 ASSEMBLY_LOCATOR_ID,
1165 DEMAND_DATE,
1166 DEMAND_QUANTITY,
1167 ORDER_TYPE,
1168 KANBAN_ITEM_FLAG,
1169 REQUEST_ID,
1170 PROGRAM_APPLICATION_ID,
1171 PROGRAM_ID,
1172 PROGRAM_UPDATE_DATE,
1173 LAST_UPDATE_DATE,
1174 LAST_UPDATED_BY,
1175 CREATION_DATE,
1176 CREATED_BY )
1177 SELECT
1178 mrp_kanban_demand_s.nextval,
1179 g_kanban_info_rec.kanban_plan_id,
1180 g_kanban_info_rec.organization_id,
1181 l_component_id,
1182 l_subinventory,
1183 l_locator_id,
1184 g_kanban_info_rec.organization_id,
1185 p_assembly_item_id,
1186 p_subinventory,
1187 p_locator_id,
1188 l_forecast_date,
1189 l_demand_quantity,
1190 8,
1191 l_kanban_item_flag,
1192 fnd_global.conc_request_id,
1193 fnd_global.prog_appl_id,
1194 fnd_global.conc_program_id,
1195 sysdate,
1196 sysdate,
1197 fnd_global.user_id,
1198 sysdate,
1199 fnd_global.user_id
1200 FROM
1201 DUAL;
1202
1203 END IF;
1204
1205 ELSIF l_rate_end_date IS NOT NULL THEN
1206 -- this is repetitive forecast entry - needs explosion of forecast
1207
1208 l_running_total_quantity := l_running_total_quantity + nvl(
1209 l_forecast_quantity,0);
1210
1211 if (l_basis_type = WIP_CONSTANTS.LOT_BASED_MTL and l_component_id <> p_assembly_item_id) then
1212 l_foq := p_assy_foq;
1213 else
1214 l_foq := 1;
1215 end if;
1216
1217 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1218 /* l_demand_quantity := ROUND((l_forecast_quantity* nvl(l_component_usage, 1) *
1219 nvl(p_cumulative_usage,1)*
1220 (nvl(l_net_planning_percent, 100) /100)) /
1221 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
1222 l_demand_quantity := ((l_forecast_quantity* nvl(l_component_usage, 1) *
1223 nvl(p_cumulative_usage,1)*
1224 (nvl(l_planning_factor, 100) /100)) /
1225 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)))/l_foq;
1226 /* End of Update */
1227
1228 IF g_debug THEN
1229 g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
1230 MRP_UTIL.MRP_LOG (g_log_message);
1231 END IF;
1232
1233 -- call the function that explodes repetitive foreast demand
1234
1235 l_ret_val := Explode_Repetitive_Forecast (
1236 l_component_id,
1237 l_demand_quantity,
1238 l_forecast_date,
1239 l_rate_end_date,
1240 l_bucket_type,
1241 8,
1242 l_line_id,
1243 l_subinventory,
1244 l_locator_id,
1245 p_subinventory,
1246 p_locator_id,
1247 p_assembly_item_id,
1248 l_kanban_item_flag,
1249 FALSE);
1250 IF NOT l_ret_val THEN
1251 return FALSE;
1252 END IF;
1253
1254 END IF;
1255
1256 END LOOP;-- end of my cursor
1257 IF parent_schedule_entries%ISOPEN THEN
1258 CLOSE parent_schedule_entries;
1259 END IF;
1260
1261 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1262 -- l_cumulative_usage := p_cumulative_usage * l_component_usage;
1263 l_cumulative_usage := p_cumulative_usage * l_component_usage * (nvl(l_planning_factor, 100)/100) / l_foq;
1264 /* End of Update */
1265
1266 IF ( l_running_total_quantity > 0) THEN
1267 IF g_debug THEN
1268 g_log_message := 'Calling Cascade_Forecast_Demand in recursive mode';
1269 MRP_UTIL.MRP_LOG (g_log_message);
1270 END IF;
1271
1272 if (p_assembly_item_id = l_component_id) then
1273 l_bill_or_ps := 2;
1274 else
1275 l_bill_or_ps := 1;
1276 end if;
1277
1278 /* Added for lot based material support
1279 For phantom, we do not use fixed order quantity of the component. We used
1280 the fixed order quantity of top level parent, that is the first non-phantom parent. */
1281 if (l_wip_supply_type = WIP_CONSTANTS.PHANTOM) then
1282 l_comp_foq := p_assy_foq;
1283 end if;
1284
1285 /* Modified for lot based material support.
1286 Push down the fixed order qty (l_comp_foq) to the component. */
1287 l_ret_val := Cascade_Fcst_Demand(
1288 l_bill_or_ps,
1289 TRUE,
1290 p_parent_line_id,
1291 NULL,
1292 p_top_item_id,
1293 l_component_id,
1294 l_cumulative_usage,
1295 l_subinventory,
1296 l_locator_id,
1297 p_demand_type,
1298 p_explode_always,
1299 p_sales_order_demand,
1300 l_comp_foq);
1301
1302 IF g_debug THEN
1303 g_log_message := 'returned from the Cascade_Fcst_Demand call';
1304 MRP_UTIL.MRP_LOG (g_log_message);
1305 END IF;
1306
1307 IF NOT l_ret_val THEN
1308 RETURN FALSE;
1309 END IF;
1310
1311 END IF;
1312
1313 END LOOP;
1314
1315 IF component_cursor1%ISOPEN THEN
1316 CLOSE component_cursor1;
1317 END IF;
1318 IF component_cursor2%ISOPEN THEN
1319 CLOSE component_cursor2;
1320 END IF;
1321
1322 RETURN TRUE;
1323
1324 --exception handling
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328 g_log_message := 'Cascade_Fcst_Demand Sql Error ';
1329 MRP_UTIL.MRP_LOG (g_log_message);
1330 g_log_message := sqlerrm;
1331 MRP_UTIL.MRP_LOG (g_log_message);
1332 RETURN FALSE;
1333
1334 END Cascade_Fcst_Demand;
1335
1336 -- ========================================================================
1337 -- This function passes demand down to the components all the way down
1338 -- to the bottom of the bill
1339 -- ========================================================================
1340 FUNCTION Cascade_Ap_Demand (
1341 p_bill_or_ps IN NUMBER,
1342 p_recursive IN BOOLEAN,
1343 p_parent_line_id IN NUMBER,
1344 p_line_id IN NUMBER,
1345 p_top_item_id IN NUMBER,
1346 p_top_alt IN VARCHAR2,
1347 p_assembly_item_id IN NUMBER,
1348 p_alt IN VARCHAR2,
1349 p_cumulative_usage IN NUMBER,
1350 p_subinventory IN VARCHAR2,
1351 p_locator_id IN NUMBER,
1352 p_demand_type IN NUMBER,
1353 p_explode_always IN VARCHAR2,
1354 p_sales_order_demand IN VARCHAR2,
1355 p_assy_foq IN NUMBER )
1356
1357 RETURN BOOLEAN IS
1358
1359 --declare some local variables here
1360 l_bill_or_ps number; -- 1 - bill; 2 - pull sequence
1361
1362 l_component_id number;
1363 l_subinventory varchar2(10);
1364 l_locator_id number;
1365 l_component_usage number;
1366 l_component_yield number;
1367 l_operation_yield number;
1368 l_net_planning_percent number;
1369 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1370 l_planning_factor number;
1371 l_item_num number;
1372 /* End of Update */
1373 l_kanban_item_flag varchar2(1);
1374 l_demand_quantity number;
1375 l_ret_val boolean;
1376 l_cumulative_usage number;
1377
1378 l_running_total_quantity number := 0;
1379 l_schedule_quantity number;
1380 l_schedule_date date;
1381
1382 l_wip_supply_type number;
1383 l_basis_type number;
1384 l_comp_foq number;
1385 l_foq number;
1386
1387 CURSOR parent_schedule_entries IS
1388 SELECT sum(planned_quantity) PQ,
1389 scheduled_completion_date
1390 FROM mrp_kanban_actual_prod_v
1391 WHERE organization_id = g_kanban_info_rec.organization_id
1392 AND scheduled_completion_date between g_kanban_info_rec.start_date AND
1393 g_kanban_info_rec.cutoff_date
1394 AND primary_item_id IN
1395 ( select COMPONENT_ITEM_ID from mrp_low_level_codes
1396 WHERE ORGANIZATION_ID = g_kanban_info_rec.organization_id
1397 AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
1398 AND primary_item_id = p_top_item_id
1399 AND nvl(alternate_bom_designator, 'NONE') = nvl(p_top_alt, 'NONE')
1400 AND nvl(line_id,0)=nvl(p_parent_line_id,0)
1401 group by scheduled_completion_date,schedule_type,line_id;
1402
1403
1404 -- cursor component_cursor1 is the cursor that passes down demand
1405 -- to the components feeding into a line. Notice that we are driving
1406 -- off of bom_operational_routings
1407 -- also if supply sub and locator are null in bom_inventory_components
1408 -- we get it from wip supply locations from mtl_system_items - this
1409 -- is ok for R-11, though we might have an issue here for R12
1410
1411 CURSOR component_cursor1 IS
1412 SELECT DISTINCT
1413 bic.component_item_id,
1414 decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
1415 bic.supply_subinventory),
1416 decode(bic.supply_subinventory, NULL, msi.wip_supply_locator_id,
1417 bic.supply_locator_id),
1418 bic.component_quantity,
1419 bic.component_yield_factor,
1420 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1421 bic.planning_factor,
1422 bic.item_num,
1423 /* End of Update */
1424 mllc.operation_yield,
1425 mllc.net_planning_percent,
1426 mllc.kanban_item_flag,
1427 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1428 mllc.wip_supply_type,
1429 mllc.basis_type,
1430 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
1431 FROM mtl_system_items msi,
1432 mrp_low_level_codes mllc,
1433 bom_inventory_components bic,
1434 bom_bill_of_materials bbom
1435 WHERE
1436 bbom.assembly_item_id = p_assembly_item_id
1437 AND bbom.organization_id = g_kanban_info_rec.organization_id
1438 AND nvl(bbom.alternate_bom_designator, 'NONE') = nvl(p_top_alt, 'NONE')
1439 /* Bug 2279877, not pick up discrete jobs w/o line_id
1440 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
1441 bor.assembly_item_id,
1442 bor.organization_id,
1443 bor.line_id,
1444 bor.alternate_routing_designator)
1445 */
1446 /* BUG: 1668867 , Fix for double demand */
1447 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
1448 p_assembly_item_id,
1449 bic.component_item_id,
1450 g_kanban_info_rec.organization_id)
1451 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
1452 AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
1453 >= g_kanban_info_rec.bom_effectivity
1454 AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
1455 AND NOT EXISTS (
1456 SELECT NULL
1457 FROM bom_inventory_components bic2
1458 WHERE bic2.bill_sequence_id = bic.bill_sequence_id
1459 AND bic2.component_item_id = bic.component_item_id
1460 AND (decode(bic2.implementation_date, null,
1461 bic2.old_component_sequence_id,
1462 bic2.component_sequence_id) =
1463 decode(bic.implementation_date, null,
1464 bic.old_component_sequence_id,
1465 bic.component_sequence_id)
1466 OR bic2.operation_seq_num = bic.operation_seq_num)
1467 AND bic2.effectivity_date <=
1468 g_kanban_info_rec.bom_effectivity
1469 AND bic2.effectivity_date > bic.effectivity_date
1470 AND (bic2.implementation_date is not null OR
1471 (bic2.implementation_date is null AND EXISTS
1472 (SELECT NULL
1473 FROM eng_revised_items eri
1474 WHERE bic2.revised_item_sequence_id =
1475 eri.revised_item_sequence_id
1476 AND eri.mrp_active = 1 ))))
1477 AND (bic.implementation_date is not null OR
1478 (bic.implementation_date is null AND EXISTS
1479 (SELECT NULL
1480 FROM eng_revised_items eri
1481 WHERE bic.revised_item_sequence_id =
1482 eri.revised_item_sequence_id
1483 AND eri.mrp_active = 1 )))
1484 AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
1485 AND mllc.organization_id = bbom.organization_id
1486 AND mllc.assembly_item_id = bbom.assembly_item_id
1487 AND mllc.component_item_id = bic.component_item_id
1488 AND nvl(mllc.alternate_designator, 'xxx') =
1489 nvl(bbom.alternate_bom_designator, 'xxx')
1490 AND msi.inventory_item_id = mllc.component_item_id
1491 AND msi.organization_id = mllc.organization_id
1492 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
1493 OR p_explode_always = 'Y'
1494 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
1495
1496 -- cursor component_cursor2 blows down demand to the components
1497 -- as stored in the mrp_low_level_codes table
1498 CURSOR component_cursor2 IS
1499 SELECT DISTINCT
1500 mllc.component_item_id,
1501 mllc.from_subinventory,
1502 mllc.from_locator_id,
1503 mllc.component_usage,
1504 mllc.component_yield,
1505 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1506 mllc.planning_factor,
1507 mllc.item_num,
1508 /* End of Update */
1509 mllc.operation_yield,
1510 mllc.net_planning_percent,
1511 mllc.kanban_item_flag,
1512 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1513 mllc.wip_supply_type,
1514 mllc.basis_type,
1515 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
1516 FROM mtl_system_items msi,
1517 mrp_low_level_codes mllc
1518 WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
1519 AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
1520 AND mllc.assembly_item_id = p_assembly_item_id
1521 AND ((mllc.to_subinventory = p_subinventory
1522 AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
1523 (mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
1524 AND msi.inventory_item_id = mllc.component_item_id
1525 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1526 /* Bug 2279877, not pick up discrete jobs w/o line_id
1527 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
1528 mllc.assembly_item_id,
1529 mllc.organization_id,
1530 null,
1531 mllc.alternate_designator)
1532 */
1533 /* End of Update */
1534 /* Bug 1668867 : Double Kanban demand */
1535 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
1536 p_assembly_item_id,
1537 mllc.component_item_id,
1538 mllc.organization_id)
1539 AND msi.organization_id = mllc.organization_id
1540 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
1541 OR p_explode_always = 'Y'
1542 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
1543
1544 BEGIN
1545
1546 IF g_debug THEN
1547
1548 g_log_message := 'Entering Cascade_Ap_Demand function';
1549 MRP_UTIL.MRP_LOG (g_log_message);
1550 g_log_message := 'Cascading Demand For : ';
1551 MRP_UTIL.MRP_LOG (g_log_message);
1552 g_log_message := 'Line : ' || to_char(p_line_id);
1553 MRP_UTIL.MRP_LOG (g_log_message);
1554 g_log_message := 'Item : ' || to_char(p_assembly_item_id);
1555 MRP_UTIL.MRP_LOG (g_log_message);
1556 g_log_message := 'Sub : ' || p_subinventory;
1557 MRP_UTIL.MRP_LOG (g_log_message);
1558 g_log_message := 'Loc : ' || p_locator_id;
1559 MRP_UTIL.MRP_LOG (g_log_message);
1560
1561 END IF;
1562
1563
1564 -- Depending on the boolean flag p_recursive, we decide what cursor
1565 -- we want to go after. We know that we will have a line reference
1566 -- on the demand. So, when we call Cacade_Demand the first time, ie
1567 -- to pass down demand to components feeding into the line, then
1568 -- p_recursive is false. We just blow the demand down one level. Once
1569 -- we do that, we call Cascade_Mds_Mps_Demand in the recursive mode with
1570 -- p_recursive set to true when we want to go after mrp_low_level_codes
1571 -- recursively and blow the demand all the way down.
1572
1573 IF NOT p_recursive THEN
1574
1575 IF NOT component_cursor1%ISOPEN THEN
1576 OPEN component_cursor1;
1577 END IF;
1578
1579 ELSE
1580
1581 IF NOT component_cursor2%ISOPEN THEN
1582 OPEN component_cursor2;
1583 END IF;
1584 END IF;
1585
1586 WHILE TRUE LOOP
1587
1588 IF not p_recursive THEN
1589 FETCH component_cursor1
1590 INTO l_component_id,
1591 l_subinventory,
1592 l_locator_id,
1593 l_component_usage,
1594 l_component_yield,
1595 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1596 l_planning_factor,
1597 l_item_num,
1598 /* End of Update */
1599 l_operation_yield,
1600 l_net_planning_percent,
1601 l_kanban_item_flag,
1602 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1603 l_wip_supply_type,
1604 l_basis_type,
1605 l_comp_foq;
1606 EXIT WHEN component_cursor1%NOTFOUND;
1607
1608 ELSE
1609
1610 FETCH component_cursor2
1611 INTO l_component_id,
1612 l_subinventory,
1613 l_locator_id,
1614 l_component_usage,
1615 l_component_yield,
1616 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1617 l_planning_factor,
1618 l_item_num,
1619 /* End of Update */
1620 l_operation_yield,
1621 l_net_planning_percent,
1622 l_kanban_item_flag,
1623 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1624 l_wip_supply_type,
1625 l_basis_type,
1626 l_comp_foq;
1627 EXIT WHEN component_cursor2%NOTFOUND;
1628
1629
1630 END IF;
1631
1632 IF g_debug THEN
1633 g_log_message := 'component_usage is : ' || to_char (l_component_usage);
1634 MRP_UTIL.MRP_LOG (g_log_message);
1635 g_log_message := 'Net_Planning_Percent is : ' ||
1636 to_char (l_net_planning_percent);
1637 MRP_UTIL.MRP_LOG (g_log_message);
1638 g_log_message := 'Operation_Yield is : ' || to_char (l_operation_yield);
1639 MRP_UTIL.MRP_LOG (g_log_message);
1640 g_log_message := 'Component_Yield is : ' || to_char (l_component_yield);
1641 MRP_UTIL.MRP_LOG (g_log_message);
1642 END IF;
1643
1644 -- calculate the demand quantity to be passed down using all the
1645 -- percentages and yields stuff that we've got
1646
1647 IF NOT parent_schedule_entries%ISOPEN THEN
1648 OPEN parent_schedule_entries;
1649 END IF;
1650 WHILE TRUE LOOP
1651
1652 FETCH parent_schedule_entries
1653 INTO l_schedule_quantity,
1654 l_schedule_date;
1655 EXIT WHEN parent_schedule_entries%NOTFOUND;
1656
1657 l_running_total_quantity := l_running_total_quantity + nvl(
1658 l_schedule_quantity,0);
1659
1660 /* Added for lot based material support
1661 The p_assy_foq is the fixed order quantity of the assembly.
1662 It can be either the foq from the item master or it's parent (for phantom assembly).
1663 The p_assy_foq will be used to calculate the component's demand when the
1664 component has lot basis type and the demand is not from the pull sequence chain. */
1665 if (l_basis_type = WIP_CONSTANTS.LOT_BASED_MTL and l_component_id <> p_assembly_item_id) then
1666 l_foq := p_assy_foq;
1667 else
1668 l_foq := 1;
1669 end if;
1670
1671 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1672 /* l_demand_quantity := ROUND((l_schedule_quantity* nvl(l_component_usage, 1) *
1673 nvl(p_cumulative_usage,1)*
1674 (nvl(l_net_planning_percent, 100) /100)) /
1675 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
1676 l_demand_quantity := ((l_schedule_quantity* nvl(l_component_usage, 1) *
1677 nvl(p_cumulative_usage,1)*
1678 (nvl(l_planning_factor, 100) /100)) /
1679 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)))/l_foq;
1680 /* End of Update */
1681
1682 IF g_debug THEN
1683 g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
1684 MRP_UTIL.MRP_LOG (g_log_message);
1685 END IF;
1686
1687 -- now insert the demand into the kanban demand table if its > 0
1688
1689 IF l_demand_quantity > 0 THEN
1690
1691 INSERT INTO MRP_KANBAN_DEMAND (
1692 DEMAND_ID,
1693 KANBAN_PLAN_ID,
1694 ORGANIZATION_ID,
1695 INVENTORY_ITEM_ID,
1696 SUBINVENTORY,
1697 LOCATOR_ID,
1698 ASSEMBLY_ORG_ID,
1699 ASSEMBLY_ITEM_ID,
1700 ASSEMBLY_SUBINVENTORY,
1701 ASSEMBLY_LOCATOR_ID,
1702 DEMAND_DATE,
1703 DEMAND_QUANTITY,
1704 ORDER_TYPE,
1705 KANBAN_ITEM_FLAG,
1706 REQUEST_ID,
1707 PROGRAM_APPLICATION_ID,
1708 PROGRAM_ID,
1709 PROGRAM_UPDATE_DATE,
1710 LAST_UPDATE_DATE,
1711 LAST_UPDATED_BY,
1712 CREATION_DATE,
1713 CREATED_BY )
1714 SELECT
1715 mrp_kanban_demand_s.nextval,
1716 g_kanban_info_rec.kanban_plan_id,
1717 g_kanban_info_rec.organization_id,
1718 l_component_id,
1719 l_subinventory,
1720 l_locator_id,
1721 g_kanban_info_rec.organization_id,
1722 p_assembly_item_id,
1723 p_subinventory,
1724 p_locator_id,
1725 l_schedule_date,
1726 l_demand_quantity,
1727 8,
1728 l_kanban_item_flag,
1729 fnd_global.conc_request_id,
1730 fnd_global.prog_appl_id,
1731 fnd_global.conc_program_id,
1732 sysdate,
1733 sysdate,
1734 fnd_global.user_id,
1735 sysdate,
1736 fnd_global.user_id
1737 FROM
1738 DUAL;
1739
1740 END IF;
1741 END LOOP;-- end of my cursor
1742 IF parent_schedule_entries%ISOPEN THEN
1743 CLOSE parent_schedule_entries;
1744 END IF;
1745
1746 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1747 --l_cumulative_usage := p_cumulative_usage * l_component_usage;
1748 l_cumulative_usage := p_cumulative_usage * l_component_usage * (nvl(l_planning_factor, 100)/100) / l_foq;
1749 /* End of Update */
1750
1751 IF ( l_running_total_quantity > 0) THEN
1752 IF g_debug THEN
1753 g_log_message := 'Calling Cascade_Ap_Demand in recursive mode';
1754 MRP_UTIL.MRP_LOG (g_log_message);
1755 END IF;
1756
1757 if (p_assembly_item_id = l_component_id) then
1758 l_bill_or_ps := 2;
1759 else
1760 l_bill_or_ps := 1;
1761 end if;
1762
1763 /* Added for lot based material support
1764 For phantom, we do not use fixed order quantity of the component. We used
1765 the fixed order quantity of top level parent, that is the first non-phantom parent. */
1766 if (l_wip_supply_type = WIP_CONSTANTS.PHANTOM) then
1767 l_comp_foq := p_assy_foq;
1768 end if;
1769
1770 /* Modified for lot based material support.
1771 Push down the fixed order qty (l_comp_foq) to the component. */
1772 l_ret_val := Cascade_Ap_Demand(
1773 l_bill_or_ps,
1774 TRUE,
1775 p_parent_line_id,
1776 NULL,
1777 p_top_item_id,
1778 p_top_alt,
1779 l_component_id,
1780 null,
1781 l_cumulative_usage,
1782 l_subinventory,
1783 l_locator_id,
1784 p_demand_type,
1785 p_explode_always,
1786 p_sales_order_demand,
1787 l_comp_foq);
1788
1789 IF g_debug THEN
1790 g_log_message := 'returned from the cascade call';
1791 MRP_UTIL.MRP_LOG (g_log_message);
1792 END IF;
1793
1794
1795 IF NOT l_ret_val THEN
1796 RETURN FALSE;
1797 END IF;
1798
1799 END IF;
1800
1801 END LOOP;
1802
1803 IF component_cursor1%ISOPEN THEN
1804 CLOSE component_cursor1;
1805 END IF;
1806 IF component_cursor2%ISOPEN THEN
1807 CLOSE component_cursor2;
1808 END IF;
1809
1810 RETURN TRUE;
1811
1812 --exception handling
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 g_log_message := 'Cascade_Ap_Demand Sql Error ';
1816 MRP_UTIL.MRP_LOG (g_log_message);
1817 g_log_message := sqlerrm;
1818 MRP_UTIL.MRP_LOG (g_log_message);
1819 RETURN FALSE;
1820
1821 END Cascade_Ap_Demand;
1822
1823
1824 -- ========================================================================
1825 -- This function passes demand down to the components all the way down
1826 -- to the bottom of the bill
1827 -- ========================================================================
1828 FUNCTION Cascade_Mds_Mps_Demand (
1829 p_bill_or_ps IN NUMBER,
1830 p_recursive IN BOOLEAN,
1831 p_parent_line_id IN NUMBER,
1832 p_line_id IN NUMBER,
1833 p_top_item_id IN NUMBER,
1834 p_assembly_item_id IN NUMBER,
1835 p_cumulative_usage IN NUMBER,
1836 p_subinventory IN VARCHAR2,
1837 p_locator_id IN NUMBER,
1838 p_demand_type IN NUMBER,
1839 p_explode_always IN VARCHAR2,
1840 p_sales_order_demand IN VARCHAR2,
1841 p_assy_foq IN NUMBER )
1842 RETURN BOOLEAN IS
1843
1844 --declare some local variables here
1845 l_bill_or_ps number; -- 1 - bill; 2 - pull sequence
1846
1847 l_component_id number;
1848 l_subinventory varchar2(10);
1849 l_locator_id number;
1850 l_component_usage number;
1851 l_component_yield number;
1852 l_operation_yield number;
1853 l_net_planning_percent number;
1854 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1855 l_planning_factor number;
1856 l_item_num number;
1857 /* End of Update */
1858 l_kanban_item_flag varchar2(1);
1859 l_demand_quantity number;
1860 l_ret_val boolean;
1861 l_cumulative_usage number;
1862
1863 l_running_total_quantity number := 0;
1864 l_schedule_quantity number;
1865 l_schedule_date date;
1866
1867 l_wip_supply_type number;
1868 l_basis_type number;
1869 l_comp_foq number;
1870 l_foq number;
1871
1872
1873 CURSOR parent_schedule_entries IS
1874 SELECT
1875 decode(schedule_quantity,NULL,MRP_KANBAN_PLAN_PK.Get_Repetitive_Demand(
1876 schedule_date,rate_end_date,repetitive_daily_rate),schedule_quantity),
1877 schedule_date
1878 FROM mrp_schedule_dates
1879 WHERE organization_id = g_kanban_info_rec.organization_id
1880 AND schedule_designator = g_kanban_info_rec.input_designator
1881 AND schedule_level = 2
1882 AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
1883 g_kanban_info_rec.cutoff_date
1884 AND inventory_item_id = p_top_item_id
1885 AND nvl(line_id,0)=nvl(p_parent_line_id,0)
1886 AND schedule_origination_type = p_demand_type ;
1887
1888
1889 -- cursor component_cursor1 is the cursor that passes down demand
1890 -- to the components feeding into a line. Notice that we are driving
1891 -- off of bom_operational_routings
1892 -- also if supply sub and locator are null in bom_inventory_components
1893 -- we get it from wip supply locations from mtl_system_items - this
1894 -- is ok for R-11, though we might have an issue here for R12
1895
1896 CURSOR component_cursor1 IS
1897 SELECT DISTINCT
1898 bic.component_item_id,
1899 decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
1900 bic.supply_subinventory),
1901 decode(bic.supply_locator_id, NULL, msi.wip_supply_locator_id,
1902 bic.supply_locator_id),
1903 bic.component_quantity,
1904 bic.component_yield_factor,
1905 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1906 bic.planning_factor,
1907 bic.item_num,
1908 /* End of Update */
1909 mllc.operation_yield,
1910 mllc.net_planning_percent,
1911 mllc.kanban_item_flag,
1912 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
1913 mllc.wip_supply_type,
1914 mllc.basis_type,
1915 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
1916 FROM mtl_system_items msi,
1917 mrp_low_level_codes mllc,
1918 bom_inventory_components bic,
1919 bom_bill_of_materials bbom,
1920 bom_operational_routings bor,
1921 mtl_parameters mp
1922 WHERE mp.organization_id = g_kanban_info_rec.organization_id
1923 AND bor.line_id (+) = p_line_id
1924 AND bor.assembly_item_id (+) = p_assembly_item_id
1925 AND bor.organization_id (+) = mp.organization_id
1926 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
1927 bor.assembly_item_id,
1928 bor.organization_id,
1929 bor.line_id,
1930 bor.alternate_routing_designator)
1931 AND bbom.assembly_item_id = p_assembly_item_id
1932 AND bbom.organization_id = mp.organization_id
1933 AND nvl(bbom.alternate_bom_designator, 'xxx') =
1934 nvl(bor.alternate_routing_designator, 'xxx')
1935 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
1936 AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
1937 >= g_kanban_info_rec.bom_effectivity
1938 AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
1939 /* BUG: 1821216 Double kanban demand */
1940 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
1941 p_assembly_item_id,
1942 bic.component_item_id,
1943 mp.organization_id)
1944 AND NOT EXISTS (
1945 SELECT NULL
1946 FROM bom_inventory_components bic2
1947 WHERE bic2.bill_sequence_id = bic.bill_sequence_id
1948 AND bic2.component_item_id = bic.component_item_id
1949 AND (decode(bic2.implementation_date, null,
1950 bic2.old_component_sequence_id,
1951 bic2.component_sequence_id) =
1952 decode(bic.implementation_date, null,
1953 bic.old_component_sequence_id,
1954 bic.component_sequence_id)
1955 OR bic2.operation_seq_num = bic.operation_seq_num)
1956 AND bic2.effectivity_date <=
1957 g_kanban_info_rec.bom_effectivity
1958 AND bic2.effectivity_date > bic.effectivity_date
1959 AND (bic2.implementation_date is not null OR
1960 (bic2.implementation_date is null AND EXISTS
1961 (SELECT NULL
1962 FROM eng_revised_items eri
1963 WHERE bic2.revised_item_sequence_id =
1964 eri.revised_item_sequence_id
1965 AND eri.mrp_active = 1 ))))
1966 AND (bic.implementation_date is not null OR
1967 (bic.implementation_date is null AND EXISTS
1968 (SELECT NULL
1969 FROM eng_revised_items eri
1970 WHERE bic.revised_item_sequence_id =
1971 eri.revised_item_sequence_id
1972 AND eri.mrp_active = 1 )))
1973 AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
1974 AND mllc.organization_id = bbom.organization_id
1975 AND mllc.assembly_item_id = bbom.assembly_item_id
1976 AND mllc.component_item_id = bic.component_item_id
1977 AND nvl(mllc.alternate_designator, 'xxx') =
1978 nvl(bbom.alternate_bom_designator, 'xxx')
1979 AND msi.inventory_item_id = mllc.component_item_id
1980 AND msi.organization_id = mllc.organization_id
1981 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
1982 OR p_explode_always = 'Y'
1983 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
1984
1985 -- cursor component_cursor2 blows down demand to the components
1986 -- as stored in the mrp_low_level_codes table
1987 CURSOR component_cursor2 IS
1988 SELECT DISTINCT
1989 mllc.component_item_id,
1990 mllc.from_subinventory,
1991 mllc.from_locator_id,
1992 mllc.component_usage,
1993 mllc.component_yield,
1994 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
1995 mllc.planning_factor,
1996 mllc.item_num,
1997 /* End of Update */
1998 mllc.operation_yield,
1999 mllc.net_planning_percent,
2000 mllc.kanban_item_flag,
2001 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
2002 mllc.wip_supply_type,
2003 mllc.basis_type,
2004 nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
2005 FROM mtl_system_items msi,
2006 mrp_low_level_codes mllc
2007 WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
2008 AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
2009 AND mllc.assembly_item_id = p_assembly_item_id
2010 AND ((mllc.to_subinventory = p_subinventory
2011 AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
2012 (mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
2013 /* Bug 1668867 : Double Kanban demand */
2014 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
2015 p_assembly_item_id,
2016 mllc.component_item_id,
2017 mllc.organization_id)
2018 AND msi.inventory_item_id = mllc.component_item_id
2019 AND msi.organization_id = mllc.organization_id
2020 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
2021 AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
2022 mllc.assembly_item_id,
2023 mllc.organization_id,
2024 null,
2025 mllc.alternate_designator)
2026 /* End of Update */
2027 AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
2028 OR p_explode_always = 'Y'
2029 OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
2030
2031 BEGIN
2032
2033 IF g_debug THEN
2034
2035 g_log_message := 'Entering Cascade_Mds_Mps_Demand function';
2036 MRP_UTIL.MRP_LOG (g_log_message);
2037 g_log_message := 'Cascading Demand For : ';
2038 MRP_UTIL.MRP_LOG (g_log_message);
2039 g_log_message := 'Line : ' || to_char(p_line_id);
2040 MRP_UTIL.MRP_LOG (g_log_message);
2041 g_log_message := 'Item : ' || to_char(p_assembly_item_id);
2042 MRP_UTIL.MRP_LOG (g_log_message);
2043 g_log_message := 'Sub : ' || p_subinventory;
2044 MRP_UTIL.MRP_LOG (g_log_message);
2045 g_log_message := 'Loc : ' || p_locator_id;
2046 MRP_UTIL.MRP_LOG (g_log_message);
2047
2048
2049 END IF;
2050
2051
2052 -- Depending on the boolean flag p_recursive, we decide what cursor
2053 -- we want to go after. We know that we will have a line reference
2054 -- on the demand. So, when we call Cacade_Demand the first time, ie
2055 -- to pass down demand to components feeding into the line, then
2056 -- p_recursive is false. We just blow the demand down one level. Once
2057 -- we do that, we call Cascade_Mds_Mps_Demand in the recursive mode with
2058 -- p_recursive set to true when we want to go after mrp_low_level_codes
2059 -- recursively and blow the demand all the way down.
2060
2061 IF NOT p_recursive THEN
2062
2063 IF NOT component_cursor1%ISOPEN THEN
2064 OPEN component_cursor1;
2065 END IF;
2066
2067 ELSE
2068
2069 IF NOT component_cursor2%ISOPEN THEN
2070 OPEN component_cursor2;
2071 END IF;
2072 END IF;
2073
2074 WHILE TRUE LOOP
2075
2076 IF not p_recursive THEN
2077 FETCH component_cursor1
2078 INTO l_component_id,
2079 l_subinventory,
2080 l_locator_id,
2081 l_component_usage,
2082 l_component_yield,
2083 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
2084 l_planning_factor,
2085 l_item_num,
2086 /* End of Update */
2087 l_operation_yield,
2088 l_net_planning_percent,
2089 l_kanban_item_flag,
2090 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
2091 l_wip_supply_type,
2092 l_basis_type,
2093 l_comp_foq;
2094 EXIT WHEN component_cursor1%NOTFOUND;
2095
2096 ELSE
2097
2098 FETCH component_cursor2
2099 INTO l_component_id,
2100 l_subinventory,
2101 l_locator_id,
2102 l_component_usage,
2103 l_component_yield,
2104 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
2105 l_planning_factor,
2106 l_item_num,
2107 /* End of Update */
2108 l_operation_yield,
2109 l_net_planning_percent,
2110 l_kanban_item_flag,
2111 /* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
2112 l_wip_supply_type,
2113 l_basis_type,
2114 l_comp_foq;
2115 EXIT WHEN component_cursor2%NOTFOUND;
2116
2117
2118 END IF;
2119
2120 IF g_debug THEN
2121 g_log_message := 'component_usage is : ' || to_char (l_component_usage);
2122 MRP_UTIL.MRP_LOG (g_log_message);
2123 g_log_message := 'Net_Planning_Percent is : ' ||
2124 to_char (l_net_planning_percent);
2125 MRP_UTIL.MRP_LOG (g_log_message);
2126 g_log_message := 'Operation_Yield is : ' || to_char (l_operation_yield);
2127 MRP_UTIL.MRP_LOG (g_log_message);
2128 g_log_message := 'Component_Yield is : ' || to_char (l_component_yield);
2129 MRP_UTIL.MRP_LOG (g_log_message);
2130 END IF;
2131
2132 -- calculate the demand quantity to be passed down using all the
2133 -- percentages and yields stuff that we've got
2134
2135 IF NOT parent_schedule_entries%ISOPEN THEN
2136 OPEN parent_schedule_entries;
2137 END IF;
2138 WHILE TRUE LOOP
2139
2140 FETCH parent_schedule_entries
2141 INTO l_schedule_quantity,
2142 l_schedule_date;
2143 EXIT WHEN parent_schedule_entries%NOTFOUND;
2144
2145 l_running_total_quantity := l_running_total_quantity + nvl(
2146 l_schedule_quantity,0);
2147
2148 /* Added for lot based material support
2149 The p_assy_foq is the fixed order quantity of the assembly.
2150 It can be either the foq from the item master or it's parent (for phantom assembly).
2151 The p_assy_foq will be used to calculate the component's demand when the
2152 component has lot basis type and the demand is not from the pull sequence chain. */
2153 if (l_basis_type = WIP_CONSTANTS.LOT_BASED_MTL and l_component_id <> p_assembly_item_id) then
2154 l_foq := p_assy_foq;
2155 else
2156 l_foq := 1;
2157 end if;
2158
2159 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
2160 /* l_demand_quantity := ROUND((l_schedule_quantity* nvl(l_component_usage, 1) *
2161 nvl(p_cumulative_usage,1)*
2162 (nvl(l_net_planning_percent, 100) /100)) /
2163 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)));*/
2164 l_demand_quantity := ((l_schedule_quantity* nvl(l_component_usage, 1) *
2165 nvl(p_cumulative_usage,1)*
2166 (nvl(l_planning_factor, 100) /100)) /
2167 (nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)))/l_foq;
2168 /* End of Update */
2169
2170 IF g_debug THEN
2171 g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
2172 MRP_UTIL.MRP_LOG (g_log_message);
2173 END IF;
2174
2175 -- now insert the demand into the kanban demand table if its > 0
2176
2177 IF l_demand_quantity > 0 THEN
2178
2179 INSERT INTO MRP_KANBAN_DEMAND (
2180 DEMAND_ID,
2181 KANBAN_PLAN_ID,
2182 ORGANIZATION_ID,
2183 INVENTORY_ITEM_ID,
2184 SUBINVENTORY,
2185 LOCATOR_ID,
2186 ASSEMBLY_ORG_ID,
2187 ASSEMBLY_ITEM_ID,
2188 ASSEMBLY_SUBINVENTORY,
2189 ASSEMBLY_LOCATOR_ID,
2190 DEMAND_DATE,
2191 DEMAND_QUANTITY,
2192 ORDER_TYPE,
2193 KANBAN_ITEM_FLAG,
2194 REQUEST_ID,
2195 PROGRAM_APPLICATION_ID,
2196 PROGRAM_ID,
2197 PROGRAM_UPDATE_DATE,
2198 LAST_UPDATE_DATE,
2199 LAST_UPDATED_BY,
2200 CREATION_DATE,
2201 CREATED_BY )
2202 SELECT
2203 mrp_kanban_demand_s.nextval,
2204 g_kanban_info_rec.kanban_plan_id,
2205 g_kanban_info_rec.organization_id,
2206 l_component_id,
2207 l_subinventory,
2208 l_locator_id,
2209 g_kanban_info_rec.organization_id,
2210 p_assembly_item_id,
2211 p_subinventory,
2212 p_locator_id,
2213 l_schedule_date,
2214 l_demand_quantity,
2215 8,
2216 l_kanban_item_flag,
2217 fnd_global.conc_request_id,
2218 fnd_global.prog_appl_id,
2219 fnd_global.conc_program_id,
2220 sysdate,
2221 sysdate,
2222 fnd_global.user_id,
2223 sysdate,
2224 fnd_global.user_id
2225 FROM
2226 DUAL;
2227
2228 END IF;
2229 END LOOP;-- end of my cursor
2230 IF parent_schedule_entries%ISOPEN THEN
2231 CLOSE parent_schedule_entries;
2232 END IF;
2233
2234 /* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
2235 --l_cumulative_usage := p_cumulative_usage * l_component_usage;
2236 l_cumulative_usage := p_cumulative_usage * l_component_usage * (nvl(l_planning_factor, 100)/100) / l_foq;
2237 /* End of Update */
2238
2239 IF ( l_running_total_quantity > 0) THEN
2240 IF g_debug THEN
2241 g_log_message := 'Calling Cascade_Mds_Mps_Demand in recursive mode';
2242 MRP_UTIL.MRP_LOG (g_log_message);
2243 END IF;
2244
2245 if (p_assembly_item_id = l_component_id) then
2246 l_bill_or_ps := 2;
2247 else
2248 l_bill_or_ps := 1;
2249 end if;
2250
2251 /* Added for lot based material support
2252 For phantom, we do not use fixed order quantity of the component. We used
2253 the fixed order quantity of top level parent, that is the first non-phantom parent. */
2254 if (l_wip_supply_type = WIP_CONSTANTS.PHANTOM) then
2255 l_comp_foq := p_assy_foq;
2256 end if;
2257
2258 /* Modified for lot based material support.
2259 Push down the fixed order qty (l_comp_foq) to the component. */
2260 l_ret_val := Cascade_Mds_Mps_Demand(
2261 l_bill_or_ps,
2262 TRUE,
2263 p_parent_line_id,
2264 NULL,
2265 p_top_item_id,
2266 l_component_id,
2267 l_cumulative_usage,
2268 l_subinventory,
2269 l_locator_id,
2270 p_demand_type,
2271 p_explode_always,
2272 p_sales_order_demand,
2273 l_comp_foq);
2274
2275 IF g_debug THEN
2276 g_log_message := 'returned from the cascade call';
2277 MRP_UTIL.MRP_LOG (g_log_message);
2278 END IF;
2279
2280
2281 IF NOT l_ret_val THEN
2282 RETURN FALSE;
2283 END IF;
2284
2285 END IF;
2286
2287 END LOOP;
2288
2289 IF component_cursor1%ISOPEN THEN
2290 CLOSE component_cursor1;
2291 END IF;
2292 IF component_cursor2%ISOPEN THEN
2293 CLOSE component_cursor2;
2294 END IF;
2295
2296 RETURN TRUE;
2297
2298 --exception handling
2299 EXCEPTION
2300 WHEN OTHERS THEN
2301 g_log_message := 'Cascade_Mds_Mps_Demand Sql Error ';
2302 MRP_UTIL.MRP_LOG (g_log_message);
2303 g_log_message := sqlerrm;
2304 MRP_UTIL.MRP_LOG (g_log_message);
2305 RETURN FALSE;
2306
2307 END Cascade_Mds_Mps_Demand;
2308
2309 -- ========================================================================
2310 -- this function inserts demand for FORECAST entries and
2311 -- explodes the demand all the way down to the bottom of the bill
2312 -- ========================================================================
2313 FUNCTION Insert_Fcst_Demand(
2314 p_inventory_item_id IN number,
2315 p_demand_type IN number,
2316 p_line_id IN number )
2317 RETURN BOOLEAN IS
2318 l_line_id number;
2319 l_bom_item_type number;
2320 l_ret_val boolean;
2321 l_explode_always VARCHAR2(1) := 'N'; /* This is a very important flag. It
2322 essentially tells us whether to overlook the forecast
2323 control setting on a component item and explode down
2324 the demand in the function Cascade_Fcst_Ap_Demand*/
2325 l_sales_order_demand VARCHAR2(1) := 'N'; /* Again this flag helps us in
2326 deciding whether to explode down demand from an MDS or
2327 not */
2328
2329 l_forecast_quantity number;
2330 l_forecast_date date;
2331 l_rate_end_date date;
2332 l_bucket_type number;
2333 l_origination_type number;
2334 l_foq number;
2335
2336 CURSOR item_schedule_entries IS
2337 SELECT current_forecast_quantity,
2338 forecast_date,
2339 rate_end_date,
2340 bucket_type,
2341 origination_type,
2342 line_id
2343 FROM mrp_forecast_dates
2344 WHERE organization_id = g_kanban_info_rec.organization_id
2345 /*
2346 AND ((forecast_designator = g_kanban_info_rec.input_designator) or
2347 (forecast_designator in ( -- forecast set
2348 select forecast_designator
2349 from mrp_forecast_designators
2350 where forecast_set = g_kanban_info_rec.input_designator)
2351 )
2352 )
2353 */ --bug 5237549
2354 AND FORECAST_DESIGNATOR in (
2355 select g_kanban_info_rec.input_designator from dual
2356 union all
2357 SELECT FORECAST_DESIGNATOR
2358 FROM MRP_FORECAST_DESIGNATORS
2359 WHERE FORECAST_SET = g_kanban_info_rec.input_designator )
2360 AND inventory_item_id = p_inventory_item_id
2361 AND origination_type = p_demand_type
2362 AND nvl(line_id,0) = nvl(p_line_id,0)
2363 AND ((rate_end_date IS NULL AND
2364 forecast_date BETWEEN Get_Offset_Date(
2365 g_kanban_info_rec.start_date,
2366 bucket_type )
2367 AND g_kanban_info_rec.cutoff_date) OR
2368 (rate_end_date is NOT NULL AND NOT
2369 (rate_end_date < Get_Offset_Date(
2370 g_kanban_info_rec.start_date,
2371 bucket_type ) OR
2372 forecast_date > g_kanban_info_rec.cutoff_date)));
2373
2374 BEGIN
2375
2376 IF g_debug THEN
2377 g_log_message := 'Inserting Demand For :';
2378 MRP_UTIL.MRP_LOG (g_log_message);
2379 g_log_message := 'Item : ' || to_char(p_inventory_item_id) ;
2380 MRP_UTIL.MRP_LOG (g_log_message);
2381 g_log_message := 'Line Reference :' || to_char(p_line_id);
2382 MRP_UTIL.MRP_LOG (g_log_message);
2383 END IF;
2384
2385 g_stmt_num := 170;
2386 IF g_debug THEN
2387 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
2388 MRP_UTIL.MRP_LOG (g_log_message);
2389 END IF;
2390
2391 -- the first insert here is for the item for which we have
2392 -- foreacast based on the allocation percentages mentioned
2393 -- in the mtl_kanban_pull_sequences table
2394
2395 IF NOT item_schedule_entries%ISOPEN THEN
2396 OPEN item_schedule_entries;
2397 END IF;
2398
2399 WHILE TRUE LOOP
2400
2401 FETCH item_schedule_entries
2402 INTO l_forecast_quantity,
2403 l_forecast_date,
2404 l_rate_end_date,
2405 l_bucket_type,
2406 l_origination_type,
2407 l_line_id;
2408 EXIT WHEN item_schedule_entries%NOTFOUND;
2409
2410 IF (l_line_id is NULL) THEN
2411 Begin
2412 SELECT line_id
2413 INTO l_line_id
2414 FROM bom_operational_routings
2415 WHERE alternate_routing_designator is NULL
2416 AND assembly_item_id = p_inventory_item_id
2417 AND organization_id = g_kanban_info_rec.organization_id;
2418 Exception
2419 When Others Then
2420 Null;
2421 End;
2422 END IF;
2423 IF g_debug THEN
2424 g_log_message := 'demand quantity:'||to_char(l_forecast_quantity);
2425 MRP_UTIL.MRP_LOG (g_log_message);
2426 g_log_message := 'demand date:'||to_char(l_forecast_date);
2427 MRP_UTIL.MRP_LOG (g_log_message);
2428 END IF;
2429
2430 IF l_rate_end_date IS NULL THEN
2431 -- not a repetitive forecast - simple processing
2432
2433 IF l_bucket_type = 2 OR l_bucket_type = 3 THEN
2434 --Call the pro-rating function
2435 IF NOT Get_Prorated_Demand (
2436 l_bucket_type,
2437 l_forecast_date,
2438 l_rate_end_date,
2439 l_forecast_quantity,
2440 l_forecast_date,
2441 l_forecast_quantity) THEN
2442 RETURN FALSE;
2443 END IF;
2444 END IF;
2445
2446 INSERT INTO MRP_KANBAN_DEMAND (
2447 DEMAND_ID,
2448 KANBAN_PLAN_ID,
2449 ORGANIZATION_ID,
2450 INVENTORY_ITEM_ID,
2451 SUBINVENTORY,
2452 LOCATOR_ID,
2453 ASSEMBLY_ITEM_ID,
2454 ASSEMBLY_ORG_ID,
2455 ASSEMBLY_SUBINVENTORY,
2456 ASSEMBLY_LOCATOR_ID,
2457 DEMAND_DATE,
2458 DEMAND_QUANTITY,
2459 ORDER_TYPE,
2460 KANBAN_ITEM_FLAG,
2461 REQUEST_ID,
2462 PROGRAM_APPLICATION_ID,
2463 PROGRAM_ID,
2464 PROGRAM_UPDATE_DATE,
2465 LAST_UPDATE_DATE,
2466 LAST_UPDATED_BY,
2467 CREATION_DATE,
2468 CREATED_BY )
2469 SELECT
2470 mrp_kanban_demand_s.nextval,
2471 g_kanban_info_rec.kanban_plan_id,
2472 g_kanban_info_rec.organization_id,
2473 p_inventory_item_id,
2474 ps.subinventory_name,
2475 ps.locator_id,
2476 NULL,
2477 NULL,
2478 NULL,
2479 NULL,
2480 l_forecast_date,
2481 (NVL(ps.allocation_percent, 100) *
2482 l_forecast_quantity/ 100),
2483 l_origination_type,
2484 'Y',
2485 fnd_global.conc_request_id,
2486 fnd_global.prog_appl_id,
2487 fnd_global.conc_program_id,
2488 sysdate,
2489 sysdate,
2490 fnd_global.user_id,
2491 sysdate,
2492 fnd_global.user_id
2493 FROM
2494 mtl_kanban_pull_sequences ps
2495 WHERE ps.wip_line_id = l_line_id
2496 AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
2497 AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2498 2, G_PRODUCTION_KANBAN,
2499 1, g_kanban_info_rec.kanban_plan_id,
2500 G_PRODUCTION_KANBAN)
2501 AND ps.inventory_item_id = p_inventory_item_id
2502 AND ps.organization_id = g_kanban_info_rec.organization_id;
2503
2504
2505 ELSIF l_rate_end_date IS NOT NULL THEN
2506
2507 -- this is repetitive forecast entry - needs explosion of forecast
2508 -- call the function that explodes repetitive foreast demand
2509 l_ret_val := Explode_Repetitive_Forecast (
2510 p_inventory_item_id,
2511 l_forecast_quantity,
2512 l_forecast_date,
2513 l_rate_end_date,
2514 l_bucket_type,
2515 l_origination_type,
2516 l_line_id,
2517 NULL,
2518 NULL,
2519 NULL,
2520 NULL,
2521 NULL,
2522 NULL,
2523 TRUE);
2524 IF NOT l_ret_val THEN
2525 return FALSE;
2526 END IF;
2527 END IF;
2528
2529 END LOOP; -- loop for my cursor
2530 IF item_schedule_entries%ISOPEN THEN
2531 CLOSE item_schedule_entries;
2532 END IF;
2533
2534
2535 -- -------------------------------------------------
2536 -- Pass down the dependent demand for each component
2537 -- By calling the recusive function
2538 -- First the demand flows to the components that are
2539 -- supplying to the line on which we have the demand
2540 -- and as we follow through our recursion process
2541 -- the demand flows to the components all the way
2542 -- to the bottom of the bill
2543 -- -------------------------------------------------
2544
2545 g_stmt_num := 175;
2546 IF g_debug THEN
2547 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
2548 MRP_UTIL.MRP_LOG (g_log_message);
2549 g_log_message := 'Calling Cascade_Fcst_Demand in NON Recursive Mode';
2550 MRP_UTIL.MRP_LOG (g_log_message);
2551 END IF;
2552
2553 -- Before we call the function Cascade_Fcst_Demand,
2554 -- we need to set the flags
2555 -- l_explode_always and l_sales_order_demand.
2556 -- Setting l_sales_order demand is very straight forward.
2557 -- l_explode_always is set to TRUE if the demand_type is Manual for
2558 -- either forecast or schedule inputs or if the item type is 'Standard'
2559
2560 l_sales_order_demand := 'N';
2561
2562 -- check to see if we are dealing with a standard item
2563 SELECT bom_item_type,
2564 nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
2565 INTO l_bom_item_type,
2566 l_foq
2567 FROM mtl_system_items
2568 WHERE inventory_item_id = p_inventory_item_id
2569 AND organization_id = g_kanban_info_rec.organization_id;
2570
2571 IF p_demand_type = 1 OR l_bom_item_type = 4 THEN
2572 l_explode_always := 'Y';
2573 ELSE
2574 l_explode_always := 'N';
2575 END IF;
2576
2577 l_ret_val := Cascade_Fcst_Demand(
2578 1,
2579 FALSE,
2580 p_line_id,
2581 l_line_id,
2582 p_inventory_item_id,
2583 p_inventory_item_id,
2584 1,
2585 NULL,
2586 NULL,
2587 p_demand_type,
2588 l_explode_always,
2589 l_sales_order_demand,
2590 l_foq);
2591
2592 IF NOT l_ret_val THEN
2593 RETURN FALSE;
2594 END IF;
2595
2596 RETURN TRUE;
2597
2598 --exception handling
2599 EXCEPTION
2600 WHEN OTHERS THEN
2601 g_log_message := 'INSERT_FCST_DEMAND Sql Error ';
2602 MRP_UTIL.MRP_LOG (g_log_message);
2603 g_log_message := sqlerrm;
2604 MRP_UTIL.MRP_LOG (g_log_message);
2605 RETURN FALSE;
2606
2607 END Insert_Fcst_Demand;
2608
2609
2610 -- ========================================================================
2611 -- this function inserts demand for Acutal Production entries and explodes
2612 -- the demand all the way down to the bottom of the bill
2613 -- ========================================================================
2614 FUNCTION Insert_Ap_Demand(
2615 p_inventory_item_id IN number,
2616 p_alt_bom IN varchar,
2617 p_line_id IN number )
2618 RETURN BOOLEAN IS
2619
2620 l_item_id number;
2621 l_schedule_quantity number;
2622 l_schedule_date date;
2623 l_schedule_type number;
2624 l_line_id number;
2625 l_bom_item_type number;
2626 l_ret_val boolean;
2627 l_explode_always VARCHAR2(1) := 'N'; /* This is a very important flag. It
2628 essentially tells us whether to overlook the forecast
2629 control setting on a component item and explode down
2630 the demand in the function Cascade_Mds_Mps_Demand*/
2631 l_sales_order_demand VARCHAR2(1) := 'N'; /* Again this flag helps us in
2632 deciding whether to explode down demand from an MDS or
2633 not */
2634 l_foq number;
2635
2636 CURSOR item_schedule_entries IS
2637 SELECT sum(planned_quantity) PQ,
2638 scheduled_completion_date,
2639 schedule_type,
2640 line_id
2641 FROM mrp_kanban_actual_prod_v
2642 WHERE organization_id = g_kanban_info_rec.organization_id
2643 AND scheduled_completion_date between g_kanban_info_rec.start_date AND
2644 g_kanban_info_rec.cutoff_date
2645 AND primary_item_id IN
2646 ( select COMPONENT_ITEM_ID from mrp_low_level_codes
2647 WHERE ORGANIZATION_ID = g_kanban_info_rec.organization_id
2648 AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
2649 AND primary_item_id = p_inventory_item_id
2650 AND nvl(alternate_bom_designator, 'NONE') = nvl(p_alt_bom , 'NONE')
2651 AND nvl(line_id,0)=nvl(p_line_id,0)
2652 group by scheduled_completion_date,schedule_type,line_id;
2653
2654 BEGIN
2655 -- ---------------------------------------------
2656 -- Attribute the independent demand using
2657 -- allocation percent to the completion sub s
2658 -- as maintained in the pull sequences table
2659 -- ---------------------------------------------
2660
2661 IF g_debug THEN
2662 g_log_message := 'Inserting Demand For :';
2663 MRP_UTIL.MRP_LOG (g_log_message);
2664 g_log_message := 'Item : ' || to_char(p_inventory_item_id) ;
2665 MRP_UTIL.MRP_LOG (g_log_message);
2666 g_log_message := 'Alternate : ' || p_alt_bom ;
2667 MRP_UTIL.MRP_LOG (g_log_message);
2668 g_log_message := 'Line Reference :' || to_char(p_line_id);
2669 MRP_UTIL.MRP_LOG (g_log_message);
2670 END IF;
2671
2672 g_stmt_num := 170;
2673 IF g_debug THEN
2674 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
2675 MRP_UTIL.MRP_LOG (g_log_message);
2676 END IF;
2677
2678 -- the first insert here is for the item for which we have
2679 -- mds/mps based on the allocation percentages mentioned
2680 -- in the mtl_kanban_pull_sequences table
2681
2682 l_item_id := p_inventory_item_id;
2683 IF NOT item_schedule_entries%ISOPEN THEN
2684 OPEN item_schedule_entries;
2685 END IF;
2686
2687 WHILE TRUE LOOP
2688
2689 FETCH item_schedule_entries
2690 INTO l_schedule_quantity,
2691 l_schedule_date,
2692 l_schedule_type,
2693 l_line_id;
2694 EXIT WHEN item_schedule_entries%NOTFOUND;
2695
2696 IF g_debug THEN
2697 g_log_message := 'demand quantity:'||to_char(l_schedule_quantity);
2698 MRP_UTIL.MRP_LOG (g_log_message);
2699 g_log_message := 'demand date:'||to_char(l_schedule_date);
2700 MRP_UTIL.MRP_LOG (g_log_message);
2701 END IF;
2702
2703 /* Bug 2279877, we allow null-line for discrete and not get line from primary rtg
2704 IF (l_line_id is NULL) THEN
2705 Begin
2706 SELECT line_id
2707 INTO l_line_id
2708 FROM bom_operational_routings
2709 WHERE alternate_routing_designator is NULL
2710 AND assembly_item_id = p_inventory_item_id
2711 AND organization_id = g_kanban_info_rec.organization_id;
2712 Exception
2713 When Others Then
2714 Null;
2715 End;
2716 END IF;
2717 */
2718
2719 INSERT INTO MRP_KANBAN_DEMAND (
2720 DEMAND_ID,
2721 KANBAN_PLAN_ID,
2722 ORGANIZATION_ID,
2723 INVENTORY_ITEM_ID,
2724 SUBINVENTORY,
2725 LOCATOR_ID,
2726 ASSEMBLY_ITEM_ID,
2727 ASSEMBLY_ORG_ID,
2728 ASSEMBLY_SUBINVENTORY,
2729 ASSEMBLY_LOCATOR_ID,
2730 DEMAND_DATE,
2731 DEMAND_QUANTITY,
2732 ORDER_TYPE,
2733 KANBAN_ITEM_FLAG,
2734 REQUEST_ID,
2735 PROGRAM_APPLICATION_ID,
2736 PROGRAM_ID,
2737 PROGRAM_UPDATE_DATE,
2738 LAST_UPDATE_DATE,
2739 LAST_UPDATED_BY,
2740 CREATION_DATE,
2741 CREATED_BY )
2742 SELECT
2743 mrp_kanban_demand_s.nextval,
2744 g_kanban_info_rec.kanban_plan_id,
2745 g_kanban_info_rec.organization_id,
2746 p_inventory_item_id,
2747 ps.subinventory_name,
2748 ps.locator_id,
2749 NULL,
2750 NULL,
2751 NULL,
2752 NULL,
2753 l_schedule_date,
2754 (NVL(ps.allocation_percent, 100) *
2755 l_schedule_quantity/ 100),
2756 l_schedule_type,
2757 'Y',
2758 fnd_global.conc_request_id,
2759 fnd_global.prog_appl_id,
2760 fnd_global.conc_program_id,
2761 sysdate,
2762 sysdate,
2763 fnd_global.user_id,
2764 sysdate,
2765 fnd_global.user_id
2766 FROM
2767 mtl_kanban_pull_sequences ps
2768 WHERE ps.wip_line_id = l_line_id
2769 AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
2770 AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2771 2, G_PRODUCTION_KANBAN,
2772 1, g_kanban_info_rec.kanban_plan_id,
2773 G_PRODUCTION_KANBAN)
2774 AND ps.inventory_item_id = p_inventory_item_id
2775 AND ps.organization_id = g_kanban_info_rec.organization_id;
2776
2777 END LOOP; -- loop for my cursor
2778 IF item_schedule_entries%ISOPEN THEN
2779 CLOSE item_schedule_entries;
2780 END IF;
2781
2782 -- -------------------------------------------------
2783 -- Pass down the dependent demand for each component
2784 -- By calling the recusive function
2785 -- First the demand flows to the components that are
2786 -- supplying to the line on which we have the demand
2787 -- and as we follow through our recursion process
2788 -- the demand flows to the components all the way
2789 -- to the bottom of the bill
2790 -- -------------------------------------------------
2791
2792 g_stmt_num := 175;
2793 IF g_debug THEN
2794 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
2795 MRP_UTIL.MRP_LOG (g_log_message);
2796 g_log_message := 'Calling Cascade_Ap_Demand in NON Recursive Mode';
2797 MRP_UTIL.MRP_LOG (g_log_message);
2798 END IF;
2799
2800 -- Before we call the function Cascade_Ap_Demand,
2801 -- we need to set the flags
2802 -- l_explode_always and l_sales_order_demand.
2803 -- Setting l_sales_order demand is very straight forward.
2804
2805 l_sales_order_demand := 'N';
2806
2807 -- check to see if we are dealing with a standard item
2808 SELECT bom_item_type,
2809 nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
2810 INTO l_bom_item_type,
2811 l_foq
2812 FROM mtl_system_items
2813 WHERE inventory_item_id = p_inventory_item_id
2814 AND organization_id = g_kanban_info_rec.organization_id;
2815
2816 l_explode_always := 'Y';
2817
2818 l_ret_val := Cascade_Ap_Demand(
2819 1,
2820 FALSE,
2821 p_line_id,
2822 l_line_id,
2823 p_inventory_item_id,
2824 p_alt_bom,
2825 p_inventory_item_id,
2826 p_alt_bom,
2827 1,
2828 NULL,
2829 NULL,
2830 l_schedule_type,
2831 l_explode_always,
2832 l_sales_order_demand,
2833 l_foq);
2834
2835 IF NOT l_ret_val THEN
2836 RETURN FALSE;
2837 END IF;
2838
2839 RETURN TRUE;
2840
2841 --exception handling
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844 g_log_message := 'INSERT_AP_DEMAND Sql Error ';
2845 MRP_UTIL.MRP_LOG (g_log_message);
2846 g_log_message := sqlerrm;
2847 MRP_UTIL.MRP_LOG (g_log_message);
2848 RETURN FALSE;
2849
2850 END Insert_Ap_Demand;
2851
2852 -- ========================================================================
2853 -- this function inserts demand for MDS/MPS entries and explodes
2854 -- the demand all the way down to the bottom of the bill
2855 -- ========================================================================
2856 FUNCTION Insert_Mds_Mps_Demand(
2857 p_inventory_item_id IN number,
2858 p_demand_type IN number,
2859 p_line_id IN number )
2860 RETURN BOOLEAN IS
2861 l_item_id number;
2862 l_schedule_quantity number;
2863 l_schedule_date date;
2864 l_schedule_origination_type number;
2865 l_line_id number;
2866 l_bom_item_type number;
2867 l_ret_val boolean;
2868 l_explode_always VARCHAR2(1) := 'N'; /* This is a very important flag. It
2869 essentially tells us whether to overlook the forecast
2870 control setting on a component item and explode down
2871 the demand in the function Cascade_Mds_Mps_Demand*/
2872 l_sales_order_demand VARCHAR2(1) := 'N'; /* Again this flag helps us in
2873 deciding whether to explode down demand from an MDS or
2874 not */
2875 l_foq number;
2876
2877 CURSOR item_schedule_entries IS
2878 SELECT
2879 decode(schedule_quantity,NULL,MRP_KANBAN_PLAN_PK.Get_Repetitive_Demand(
2880 schedule_date,rate_end_date,repetitive_daily_rate),schedule_quantity),
2881 schedule_date,
2882 schedule_origination_type,
2883 line_id
2884 FROM mrp_schedule_dates
2885 WHERE organization_id = g_kanban_info_rec.organization_id
2886 AND schedule_designator = g_kanban_info_rec.input_designator
2887 AND schedule_level = 2
2888 AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
2889 g_kanban_info_rec.cutoff_date
2890 AND inventory_item_id = p_inventory_item_id
2891 AND nvl(line_id,0)=nvl(p_line_id,0)
2892 AND schedule_origination_type = p_demand_type ;
2893
2894
2895
2896 BEGIN
2897 -- ---------------------------------------------
2898 -- Attribute the independent demand using
2899 -- allocation percent to the completion sub s
2900 -- as maintained in the pull sequences table
2901 -- ---------------------------------------------
2902
2903 IF g_debug THEN
2904 g_log_message := 'Inserting Demand For :';
2905 MRP_UTIL.MRP_LOG (g_log_message);
2906 g_log_message := 'Item : ' || to_char(p_inventory_item_id) ;
2907 MRP_UTIL.MRP_LOG (g_log_message);
2908 g_log_message := 'Line Reference :' || to_char(p_line_id);
2909 MRP_UTIL.MRP_LOG (g_log_message);
2910 END IF;
2911
2912 g_stmt_num := 170;
2913 IF g_debug THEN
2914 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
2915 MRP_UTIL.MRP_LOG (g_log_message);
2916 END IF;
2917
2918 -- the first insert here is for the item for which we have
2919 -- mds/mps based on the allocation percentages mentioned
2920 -- in the mtl_kanban_pull_sequences table
2921
2922 l_item_id := p_inventory_item_id;
2923 IF NOT item_schedule_entries%ISOPEN THEN
2924 OPEN item_schedule_entries;
2925 END IF;
2926
2927 WHILE TRUE LOOP
2928
2929 FETCH item_schedule_entries
2930 INTO l_schedule_quantity,
2931 l_schedule_date,
2932 l_schedule_origination_type,
2933 l_line_id;
2934 EXIT WHEN item_schedule_entries%NOTFOUND;
2935
2936 IF g_debug THEN
2937 g_log_message := 'demand quantity:'||to_char(l_schedule_quantity);
2938 MRP_UTIL.MRP_LOG (g_log_message);
2939 g_log_message := 'demand date:'||to_char(l_schedule_date);
2940 MRP_UTIL.MRP_LOG (g_log_message);
2941 END IF;
2942
2943 IF (l_line_id is NULL) THEN
2944 Begin
2945 SELECT line_id
2946 INTO l_line_id
2947 FROM bom_operational_routings
2948 WHERE alternate_routing_designator is NULL
2949 AND assembly_item_id = p_inventory_item_id
2950 AND organization_id = g_kanban_info_rec.organization_id;
2951 Exception
2952 When Others Then
2953 Null;
2954 End;
2955 END IF;
2956
2957 INSERT INTO MRP_KANBAN_DEMAND (
2958 DEMAND_ID,
2959 KANBAN_PLAN_ID,
2960 ORGANIZATION_ID,
2961 INVENTORY_ITEM_ID,
2962 SUBINVENTORY,
2963 LOCATOR_ID,
2964 ASSEMBLY_ITEM_ID,
2965 ASSEMBLY_ORG_ID,
2966 ASSEMBLY_SUBINVENTORY,
2967 ASSEMBLY_LOCATOR_ID,
2968 DEMAND_DATE,
2969 DEMAND_QUANTITY,
2970 ORDER_TYPE,
2971 KANBAN_ITEM_FLAG,
2972 REQUEST_ID,
2973 PROGRAM_APPLICATION_ID,
2974 PROGRAM_ID,
2975 PROGRAM_UPDATE_DATE,
2976 LAST_UPDATE_DATE,
2977 LAST_UPDATED_BY,
2978 CREATION_DATE,
2979 CREATED_BY )
2980 SELECT
2981 mrp_kanban_demand_s.nextval,
2982 g_kanban_info_rec.kanban_plan_id,
2983 g_kanban_info_rec.organization_id,
2984 p_inventory_item_id,
2985 ps.subinventory_name,
2986 ps.locator_id,
2987 NULL,
2988 NULL,
2989 NULL,
2990 NULL,
2991 l_schedule_date,
2992 (NVL(ps.allocation_percent, 100) *
2993 l_schedule_quantity/ 100),
2994 l_schedule_origination_type,
2995 'Y',
2996 fnd_global.conc_request_id,
2997 fnd_global.prog_appl_id,
2998 fnd_global.conc_program_id,
2999 sysdate,
3000 sysdate,
3001 fnd_global.user_id,
3002 sysdate,
3003 fnd_global.user_id
3004 FROM
3005 mtl_kanban_pull_sequences ps
3006 WHERE ps.wip_line_id = l_line_id
3007 AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
3008 AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
3009 2, G_PRODUCTION_KANBAN,
3010 1, g_kanban_info_rec.kanban_plan_id,
3011 G_PRODUCTION_KANBAN)
3012 AND ps.inventory_item_id = p_inventory_item_id
3013 AND ps.organization_id = g_kanban_info_rec.organization_id;
3014
3015 END LOOP; -- loop for my cursor
3016 IF item_schedule_entries%ISOPEN THEN
3017 CLOSE item_schedule_entries;
3018 END IF;
3019
3020 -- -------------------------------------------------
3021 -- Pass down the dependent demand for each component
3022 -- By calling the recusive function
3023 -- First the demand flows to the components that are
3024 -- supplying to the line on which we have the demand
3025 -- and as we follow through our recursion process
3026 -- the demand flows to the components all the way
3027 -- to the bottom of the bill
3028 -- -------------------------------------------------
3029
3030 g_stmt_num := 175;
3031 IF g_debug THEN
3032 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3033 MRP_UTIL.MRP_LOG (g_log_message);
3034 g_log_message := 'Calling Cascade_Mds_Mps_Demand in NON Recursive Mode';
3035 MRP_UTIL.MRP_LOG (g_log_message);
3036 END IF;
3037
3038 -- Before we call the function Cascade_Mds_Mps_Demand,
3039 -- we need to set the flags
3040 -- l_explode_always and l_sales_order_demand.
3041 -- Setting l_sales_order demand is very straight forward.
3042 -- l_explode_always is set to TRUE if the demand_type is Manual for
3043 -- either forecast or schedule inputs or if the item type is 'Standard'
3044
3045 IF ((g_kanban_info_rec.input_type = 2 OR
3046 g_kanban_info_rec.input_type = 3) AND p_demand_type = 3) THEN
3047 l_sales_order_demand := 'Y';
3048 ELSE
3049 l_sales_order_demand := 'N';
3050 END IF;
3051
3052 -- check to see if we are dealing with a standard item
3053 SELECT bom_item_type,
3054 nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
3055 INTO l_bom_item_type,
3056 l_foq
3057 FROM mtl_system_items
3058 WHERE inventory_item_id = p_inventory_item_id
3059 AND organization_id = g_kanban_info_rec.organization_id;
3060
3061 IF p_demand_type = 1 OR l_bom_item_type = 4 THEN
3062 l_explode_always := 'Y';
3063 ELSE
3064 l_explode_always := 'N';
3065 END IF;
3066
3067 l_ret_val := Cascade_Mds_Mps_Demand(
3068 1,
3069 FALSE,
3070 p_line_id,
3071 l_line_id,
3072 p_inventory_item_id,
3073 p_inventory_item_id,
3074 1,
3075 NULL,
3076 NULL,
3077 p_demand_type,
3078 l_explode_always,
3079 l_sales_order_demand,
3080 l_foq);
3081
3082 IF NOT l_ret_val THEN
3083 RETURN FALSE;
3084 END IF;
3085
3086 RETURN TRUE;
3087
3088 --exception handling
3089 EXCEPTION
3090 WHEN OTHERS THEN
3091 g_log_message := 'INSERT_DEMAND Sql Error ';
3092 MRP_UTIL.MRP_LOG (g_log_message);
3093 g_log_message := sqlerrm;
3094 MRP_UTIL.MRP_LOG (g_log_message);
3095 RETURN FALSE;
3096
3097 END Insert_Mds_Mps_Demand;
3098
3099
3100 -- ========================================================================
3101 -- This function gets the offset start date to be considered when we look
3102 -- at forecast demand. for example a weekly forecast demand might have
3103 -- a start date 2 days before our kanban start date and we would have to
3104 -- consider a part of this forecast demand for our kanban calculation, else
3105 -- we would be underestimating our demand
3106 -- ========================================================================
3107 FUNCTION Get_Offset_Date (
3108 p_start_date IN date,
3109 p_bucket_type IN NUMBER
3110 )
3111 RETURN DATE IS
3112
3113 l_offset_date date;
3114
3115 BEGIN
3116
3117 IF p_bucket_type = 1 THEN
3118 -- no offsetting here
3119 l_offset_date := p_start_date;
3120
3121 ELSIF p_bucket_type = 2 THEN
3122
3123 SELECT /*+ first_rows */ bw.week_start_date --bug 5237549
3124 INTO l_offset_date
3125 FROM bom_cal_week_start_dates bw,
3126 mtl_parameters mp
3127 WHERE mp.organization_id = g_kanban_info_rec.organization_id
3128 AND bw.calendar_code = mp.calendar_code
3129 AND bw.exception_set_id = mp.calendar_exception_set_id
3130 AND bw.week_start_date <= p_start_date
3131 AND bw.next_date >= p_start_date;
3132
3133 ELSIF p_bucket_type = 3 THEN
3134
3135 SELECT bp.period_start_date
3136 INTO l_offset_date
3137 FROM bom_period_start_dates bp,
3138 mtl_parameters mp
3139 WHERE mp.organization_id = g_kanban_info_rec.organization_id
3140 AND bp.calendar_code = mp.calendar_code
3141 AND bp.exception_set_id = mp.calendar_exception_set_id
3142 AND bp.period_start_date <= p_start_date
3143 AND bp.next_date >= p_start_date;
3144
3145 END IF;
3146
3147 RETURN l_offset_date;
3148
3149 EXCEPTION
3150 WHEN OTHERS THEN
3151 RETURN p_start_date;
3152
3153 END Get_Offset_Date;
3154
3155
3156 -- ========================================================================
3157 -- this function retrieves the kanban demand based on the
3158 -- input to the kanban plan and passes it down to the components
3159 -- as in the mrp_low_level_codes table
3160 -- ========================================================================
3161 FUNCTION Retrieve_Kanban_Demand RETURN BOOLEAN IS
3162
3163 -- declare local variables
3164
3165 l_demand_rec demand_rec_type; -- record that stores demand info
3166 l_rate_end_date date;
3167 l_bucket_type number;
3168 l_line_id number;
3169 l_alt_bom varchar(10);
3170 l_demand_type number;
3171 l_repetitive_forecast boolean := FALSE;
3172 l_ret_val boolean;
3173
3174 -- declare cursors
3175
3176 -- cursor to retrieve forecast entries
3177 CURSOR cur_forecast_entries IS
3178 SELECT inventory_item_id,
3179 origination_type,
3180 line_id
3181 FROM mrp_forecast_dates
3182 WHERE organization_id = g_kanban_info_rec.organization_id
3183 /*
3184 AND ((forecast_designator = g_kanban_info_rec.input_designator) or
3185 (forecast_designator in ( -- forecast set
3186 select forecast_designator
3187 from mrp_forecast_designators
3188 where forecast_set = g_kanban_info_rec.input_designator)
3189 )
3190 )
3191 */ --bug 5237549
3192 AND FORECAST_DESIGNATOR in (
3193 select g_kanban_info_rec.input_designator from dual
3194 union all
3195 SELECT FORECAST_DESIGNATOR
3196 FROM MRP_FORECAST_DESIGNATORS
3197 WHERE FORECAST_SET = g_kanban_info_rec.input_designator )
3198
3199 AND ((rate_end_date IS NULL AND
3200 forecast_date BETWEEN Get_Offset_Date(
3201 g_kanban_info_rec.start_date,
3202 bucket_type )
3203 AND g_kanban_info_rec.cutoff_date) OR
3204 (rate_end_date is NOT NULL AND NOT
3205 (rate_end_date < Get_Offset_Date(
3206 g_kanban_info_rec.start_date,
3207 bucket_type ) OR
3208 forecast_date > g_kanban_info_rec.cutoff_date)))
3209 GROUP BY inventory_item_id,origination_type,line_id;
3210
3211 -- cursor to retrieve MPS/MDS entries
3212 CURSOR cur_schedule_entries IS
3213 SELECT inventory_item_id,
3214 schedule_origination_type,
3215 line_id
3216 FROM mrp_schedule_dates
3217 WHERE organization_id = g_kanban_info_rec.organization_id
3218 AND schedule_designator = g_kanban_info_rec.input_designator
3219 AND schedule_level = 2
3220 AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
3221 g_kanban_info_rec.cutoff_date
3222 GROUP BY inventory_item_id,schedule_origination_type,line_id;
3223
3224 -- cursor to retrieve actual production
3225 CURSOR GetActualProductionDemand IS
3226 SELECT primary_item_id,
3227 alternate_bom_designator,
3228 line_id
3229 FROM mrp_kanban_actual_prod_v
3230 WHERE organization_id = g_kanban_info_rec.organization_id
3231 AND scheduled_completion_date between g_kanban_info_rec.start_date AND
3232 g_kanban_info_rec.cutoff_date
3233 AND primary_item_id IN
3234 ( select COMPONENT_ITEM_ID from mrp_low_level_codes
3235 where ORGANIZATION_ID = g_kanban_info_rec.organization_id
3236 AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
3237 group by primary_item_id,alternate_bom_designator,line_id;
3238 BEGIN
3239
3240
3241 WHILE TRUE LOOP -- begin demand entries loop
3242
3243 IF g_kanban_info_rec.input_type = 1 THEN
3244 -- input type is a forecast
3245
3246 IF NOT cur_forecast_entries%ISOPEN THEN
3247 OPEN cur_forecast_entries;
3248 END IF;
3249
3250 FETCH cur_forecast_entries
3251 INTO l_demand_rec.inventory_item_id,
3252 l_demand_type,
3253 l_line_id;
3254
3255 EXIT WHEN cur_forecast_entries%NOTFOUND;
3256
3257 IF g_debug THEN
3258 g_log_message := 'Forecast Entry Details : ' ;
3259 MRP_UTIL.MRP_LOG (g_log_message);
3260 g_log_message := 'Item : ' || to_char(l_demand_rec.inventory_item_id) ;
3261 MRP_UTIL.MRP_LOG (g_log_message);
3262 END IF;
3263
3264 ELSIF ( g_kanban_info_rec.input_type = 2 OR
3265 g_kanban_info_rec.input_type = 3 ) THEN
3266 -- input is an MDS (type = 2) or MPS (type = 3)
3267
3268 IF NOT cur_schedule_entries%ISOPEN THEN
3269 OPEN cur_schedule_entries;
3270 END IF;
3271
3272 FETCH cur_schedule_entries
3273 INTO l_demand_rec.inventory_item_id,
3274 l_demand_type,
3275 l_line_id;
3276
3277 EXIT WHEN cur_schedule_entries%NOTFOUND;
3278
3279 IF g_debug THEN
3280 g_log_message := 'Schedule Entry Details : ' ;
3281 MRP_UTIL.MRP_LOG (g_log_message);
3282 g_log_message := 'Item : ' || to_char(l_demand_rec.inventory_item_id) ;
3283 MRP_UTIL.MRP_LOG (g_log_message);
3284 g_log_message := 'Line Reference :' || to_char(l_line_id);
3285 MRP_UTIL.MRP_LOG (g_log_message);
3286 END IF;
3287 ELSIF ( g_kanban_info_rec.input_type = 4) THEN
3288 -- input is Actual Production (type = 4)
3289
3290 IF NOT GetActualProductionDemand%ISOPEN THEN
3291 OPEN GetActualProductionDemand;
3292 END IF;
3293
3294 FETCH GetActualProductionDemand
3295 INTO l_demand_rec.inventory_item_id,
3296 l_alt_bom,
3297 l_line_id;
3298
3299 EXIT WHEN GetActualProductionDemand%NOTFOUND;
3300
3301 IF g_debug THEN
3302 g_log_message := 'Schedule Entry Details : ' ;
3303 MRP_UTIL.MRP_LOG (g_log_message);
3304 g_log_message := 'Item : ' || to_char(l_demand_rec.inventory_item_id) ;
3305 MRP_UTIL.MRP_LOG (g_log_message);
3306 g_log_message := 'Alternate : ' || l_alt_bom;
3307 MRP_UTIL.MRP_LOG (g_log_message);
3308 g_log_message := 'Line Reference :' || to_char(l_line_id);
3309 MRP_UTIL.MRP_LOG (g_log_message);
3310 END IF;
3311 END IF;
3312
3313 g_stmt_num := 160;
3314 IF g_debug THEN
3315 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3316 MRP_UTIL.MRP_LOG (g_log_message);
3317 g_log_message := 'Calling Insert Demand function';
3318 MRP_UTIL.MRP_LOG (g_log_message);
3319 END IF;
3320
3321 -- call the function to insert demand and explode it through
3322 -- to the bottom of the bill
3323 IF g_kanban_info_rec.input_type = 1 THEN
3324 -- input is an Forecast
3325 l_ret_val := Insert_Fcst_Demand( l_demand_rec.inventory_item_id,
3326 l_demand_type,
3327 l_line_id );
3328 ELSIF ( g_kanban_info_rec.input_type = 2 OR
3329 g_kanban_info_rec.input_type = 3 ) THEN
3330 -- input is an MDS (type = 2) or MPS (type = 3)
3331 l_ret_val := Insert_Mds_Mps_Demand( l_demand_rec.inventory_item_id,
3332 l_demand_type,
3333 l_line_id );
3334 ELSIF g_kanban_info_rec.input_type = 4 THEN
3335 -- input is an Actual Production
3336 l_ret_val := Insert_Ap_Demand( l_demand_rec.inventory_item_id,
3337 l_alt_bom,
3338 l_line_id );
3339
3340 END IF;
3341
3342 IF NOT l_ret_val THEN
3343 RETURN FALSE;
3344 END IF;
3345
3346 END LOOP; -- demand entries loop
3347
3348 IF cur_forecast_entries%ISOPEN THEN
3349 CLOSE cur_forecast_entries;
3350 ELSIF cur_schedule_entries%ISOPEN THEN
3351 CLOSE cur_schedule_entries;
3352 ELSIF GetActualProductionDemand%ISOPEN THEN
3353 CLOSE GetActualProductionDemand;
3354 END IF;
3355
3356 RETURN TRUE;
3357
3358 --exception handling
3359 EXCEPTION
3360 WHEN OTHERS THEN
3361 g_log_message := 'RETRIEVE_KANBAN_DEMAND Sql Error ';
3362 MRP_UTIL.MRP_LOG (g_log_message);
3363 g_log_message := sqlerrm;
3364 MRP_UTIL.MRP_LOG (g_log_message);
3365 RETURN FALSE;
3366
3367 END Retrieve_Kanban_Demand;
3368
3369 -- ========================================================================
3370 -- This function calculates the Kanban Size/Number
3371 -- ========================================================================
3372 function Kanban_Calculation_Pvt (
3373 p_average_demand IN NUMBER,
3374 p_minimum_order_quantity IN NUMBER,
3375 p_fixed_lot_multiplier IN NUMBER,
3376 p_safety_stock_days IN NUMBER,
3377 p_replenishment_lead_time IN NUMBER,
3378 p_kanban_flag IN NUMBER,
3379 p_kanban_size IN OUT NOCOPY NUMBER,
3380 p_kanban_number IN OUT NOCOPY NUMBER )
3381 RETURN BOOLEAN IS
3382
3383 l_current_demand NUMBER;
3384 l_order_quantity NUMBER;
3385 l_kanban_number NUMBER;
3386
3387 BEGIN
3388
3389 -- check if p_kanban_number is passed in as 1 and bump it
3390 -- upto 2 so that the math does'nt croak
3391 IF p_kanban_number = 1 THEN
3392 l_kanban_number := 2;
3393 ELSE
3394 l_kanban_number := p_kanban_number;
3395 END If;
3396
3397 -- first calculate the Kanban Size or Kanban Number
3398 -- depending on the Kanban flag
3399
3400 IF p_kanban_flag = G_CALC_KANBAN_NUMBER THEN
3401
3402 p_kanban_number :=
3403 CEIL(((p_average_demand * (nvl(p_replenishment_lead_time,1) +
3404 nvl(p_safety_stock_days, 0) ))/p_kanban_size) + 1);
3405
3406 ELSIF p_kanban_flag = G_CALC_KANBAN_SIZE THEN
3407
3408 p_kanban_size :=
3409 CEIL((p_average_demand * (nvl(p_replenishment_lead_time,1) +
3410 nvl(p_safety_stock_days, 0)))/(l_kanban_number - 1));
3411 END IF;
3412
3413 -- now go ahead and apply the order modifiers
3414 -- If we are calculating Kanban size, we look at all the three order
3415 -- modifiers, ie, fixed days supply, min order quantity and fixed
3416 -- lot multiplier.
3417 -- If we are calculating number of Kanban cards, then we look at only
3418 -- the min order quantity.
3419
3420 IF p_kanban_flag = G_CALC_KANBAN_SIZE THEN
3421
3422 IF p_minimum_order_quantity IS NOT NULL THEN
3423 IF p_kanban_size < p_minimum_order_quantity THEN
3424 p_kanban_size := p_minimum_order_quantity;
3425 END IF;
3426 END IF;
3427
3428 IF p_fixed_lot_multiplier IS NOT NULL THEN
3429 IF p_kanban_size < p_fixed_lot_multiplier THEN
3430 p_kanban_size := p_fixed_lot_multiplier;
3431 ELSIF MOD (p_kanban_size, p_fixed_lot_multiplier) > 0 THEN
3432 p_kanban_size := p_kanban_size + ( p_fixed_lot_multiplier
3433 - MOD (p_kanban_size, p_fixed_lot_multiplier));
3434 END IF;
3435 END IF;
3436
3437 ELSIF p_kanban_flag = G_CALC_KANBAN_NUMBER THEN
3438
3439 -- Take min order quantity into consideration such that
3440 -- (Num. of Cards - 1) * Kanban Size is NOT LESS THAN
3441 -- (Min. Order Qty + Demand Over Lead Time ).
3442
3443 IF (p_minimum_order_quantity IS NOT NULL AND
3444 p_minimum_order_quantity > p_kanban_size) THEN
3445 IF (p_kanban_size * (p_kanban_number - 1)) <
3446 nvl(p_minimum_order_quantity,0) + (p_average_demand *
3447 nvl(p_replenishment_lead_time,1)) THEN
3448 p_kanban_number := CEIL((nvl(p_minimum_order_quantity,0) +
3449 (p_average_demand * nvl(p_replenishment_lead_time,1)))
3450 / p_kanban_size) + 1;
3451 END IF;
3452 END IF;
3453
3454 END IF;
3455
3456 RETURN TRUE;
3457
3458 Exception
3459 WHEN OTHERS THEN
3460 g_log_message := 'KANBAN_CALCULATION_PVT Sql Error ';
3461 MRP_UTIL.MRP_LOG (g_log_message);
3462 g_log_message := sqlerrm;
3463 MRP_UTIL.MRP_LOG (g_log_message);
3464 RETURN FALSE;
3465
3466 END Kanban_Calculation_Pvt;
3467 -- ========================================================================
3468 -- this function calculates the kanban size/number for each
3469 -- kanban item/location that we need to plan in this plan run
3470 -- and inserts the information into mtl_kanban_pull_sequences table
3471 -- ========================================================================
3472
3473 FUNCTION Calculate_Kanban_Quantity (p_total_workdays IN NUMBER)
3474 RETURN BOOLEAN IS
3475
3476 -- declare local variables here
3477 l_average_demand number;
3478 l_item_id number;
3479 l_subinventory varchar2(10);
3480 l_locator_id number;
3481 l_replenishment_lead_time number;
3482 l_fixed_lot_multiplier number;
3483 l_safety_stock_days number;
3484 l_minimum_order_quantity number;
3485 l_api_version number;
3486 l_pull_sequence_rec INV_Kanban_PVT.pull_sequence_Rec_type;
3487 l_return_status varchar2(1);
3488
3489 -- declare a cursor to summarize the demand for each
3490 -- distinct item/location and calculate the average demand
3491 -- for this item/location over the user defined time period
3492
3493 CURSOR cur_kanban_demand IS
3494 SELECT (sum(demand_quantity)/p_total_workdays),
3495 inventory_item_id,
3496 subinventory,
3497 locator_id
3498 FROM mrp_kanban_demand
3499 WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id
3500 AND organization_id = g_kanban_info_rec.organization_id
3501 AND (demand_date >= g_kanban_info_rec.start_date
3502 AND demand_date <= g_kanban_info_rec.cutoff_date )
3503 AND kanban_item_flag = 'Y'
3504 GROUP BY
3505 inventory_item_id,
3506 subinventory,
3507 locator_id;
3508
3509 -- Cursor to retrieve information from pull sequences table
3510 CURSOR cur_pull_sequence IS
3511 SELECT source_type,
3512 supplier_id,
3513 supplier_site_id,
3514 source_organization_id,
3515 source_subinventory,
3516 source_locator_id,
3517 wip_line_id,
3518 replenishment_lead_time,
3519 calculate_kanban_flag,
3520 kanban_size,
3521 fixed_lot_multiplier,
3522 safety_stock_days,
3523 number_of_cards,
3524 minimum_order_quantity,
3525 aggregation_type,
3526 allocation_percent,
3527 release_kanban_flag
3528 FROM mtl_kanban_pull_sequences
3529 WHERE kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
3530 2, G_PRODUCTION_KANBAN,
3531 1, g_kanban_info_rec.kanban_plan_id,
3532 G_PRODUCTION_KANBAN)
3533 AND organization_id = g_kanban_info_rec.organization_id
3534 AND inventory_item_id = l_item_id
3535 AND subinventory_name = l_subinventory
3536 AND nvl(locator_id,-1) = nvl(l_locator_id,-1);
3537
3538 BEGIN
3539
3540 IF g_debug THEN
3541 g_log_message := 'Entering Calculate_Kanban_Quantity Function';
3542 MRP_UTIL.MRP_LOG (g_log_message);
3543 END IF;
3544
3545 OPEN cur_kanban_demand;
3546
3547 WHILE TRUE LOOP
3548 FETCH cur_kanban_demand
3549 INTO l_average_demand,
3550 l_item_id,
3551 l_subinventory,
3552 l_locator_id;
3553
3554 IF g_debug THEN
3555 g_log_message := 'Item Id : ' || to_char (l_item_id);
3556 MRP_UTIL.MRP_LOG (g_log_message);
3557 g_log_message := 'SubInventory : ' || l_subinventory;
3558 MRP_UTIL.MRP_LOG (g_log_message);
3559 g_log_message := 'Locator : ' || to_char(l_locator_id);
3560 MRP_UTIL.MRP_LOG (g_log_message);
3561 END IF;
3562
3563
3564 EXIT WHEN cur_kanban_demand%NOTFOUND;
3565
3566 -- now get some information about this item/location from
3567 -- mtl_kanban_pull_sequences
3568
3569 OPEN cur_pull_sequence;
3570
3571 FETCH cur_pull_sequence
3572 INTO l_pull_sequence_rec.source_type,
3573 l_pull_sequence_rec.supplier_id,
3574 l_pull_sequence_rec.supplier_site_id,
3575 l_pull_sequence_rec.source_organization_id,
3576 l_pull_sequence_rec.source_subinventory,
3577 l_pull_sequence_rec.source_locator_id,
3578 l_pull_sequence_rec.wip_line_id,
3579 l_pull_sequence_rec.replenishment_lead_time,
3580 l_pull_sequence_rec.calculate_kanban_flag,
3581 l_pull_sequence_rec.kanban_size,
3582 l_pull_sequence_rec.fixed_lot_multiplier,
3583 l_pull_sequence_rec.safety_stock_days,
3584 l_pull_sequence_rec.number_of_cards,
3585 l_pull_sequence_rec.minimum_order_quantity,
3586 l_pull_sequence_rec.aggregation_type,
3587 l_pull_sequence_rec.allocation_percent,
3588 l_pull_sequence_rec.release_kanban_flag;
3589
3590
3591 --call the kanban quantity calculation api if we find pull sequence info
3592
3593 IF cur_pull_sequence%FOUND THEN
3594
3595 l_return_status := 'S'; -- initialize to success
3596
3597 -- initialize either kanban size/number to null depending on what
3598 -- we are calculating. This is important because we call our local
3599 -- kanban calculation API if the public API (stubbed out) returns
3600 -- a null value in what we want to calculate
3601
3602 IF l_pull_sequence_rec.calculate_kanban_flag = G_CALC_KANBAN_SIZE THEN
3603 l_pull_sequence_rec.kanban_size := NULL;
3604 ELSIF l_pull_sequence_rec.calculate_kanban_flag =
3605 G_CALC_KANBAN_NUMBER THEN
3606 l_pull_sequence_rec.number_of_cards := NULL;
3607 END IF;
3608
3609
3610 IF g_debug THEN
3611 g_log_message := 'Calling Kanban Qty Calc API';
3612 MRP_UTIL.MRP_LOG (g_log_message);
3613 g_log_message := 'Parameters Passed to the Kanban Qty Calc API : ';
3614 MRP_UTIL.MRP_LOG (g_log_message);
3615 g_log_message := 'Demand :' || to_char (l_average_demand);
3616 MRP_UTIL.MRP_LOG (g_log_message);
3617 g_log_message := 'Min Order Qty :'
3618 || to_char (l_pull_sequence_rec.minimum_order_quantity);
3619 MRP_UTIL.MRP_LOG (g_log_message);
3620 g_log_message := 'Fixed Lot Multiplier : '
3621 || to_char (l_pull_sequence_rec.fixed_lot_multiplier);
3622 MRP_UTIL.MRP_LOG (g_log_message);
3623 g_log_message := 'Fixed Days Supply :'
3624 || to_char (l_pull_sequence_rec.safety_stock_days);
3625 MRP_UTIL.MRP_LOG (g_log_message);
3626 g_log_message := 'Replenishment Lead Time :'
3627 || to_char (l_pull_sequence_rec.replenishment_lead_time);
3628 MRP_UTIL.MRP_LOG (g_log_message);
3629 g_log_message := 'Calculate Kanban Flag :'
3630 || to_char (l_pull_sequence_rec.calculate_kanban_flag);
3631 MRP_UTIL.MRP_LOG (g_log_message);
3632 g_log_message := 'Kanban Size :'
3633 || to_char (l_pull_sequence_rec.kanban_size);
3634 MRP_UTIL.MRP_LOG (g_log_message);
3635 g_log_message := 'Number of Cards :'
3636 || to_char (l_pull_sequence_rec.number_of_cards);
3637 MRP_UTIL.MRP_LOG (g_log_message);
3638 END IF;
3639
3640 -- specify the version of the API we want to call
3641 l_api_version := 1.0;
3642
3643 MRP_PUB_KANBAN_QTY_CALC.Calculate_Kanban_Quantity (
3644 l_api_version,
3645 l_average_demand,
3646 l_pull_sequence_rec.minimum_order_quantity,
3647 l_pull_sequence_rec.fixed_lot_multiplier,
3648 l_pull_sequence_rec.safety_stock_days,
3649 l_pull_sequence_rec.replenishment_lead_time,
3650 l_pull_sequence_rec.calculate_kanban_flag,
3651 l_pull_sequence_rec.kanban_size,
3652 l_pull_sequence_rec.number_of_cards,
3653 l_return_status );
3654
3655 IF l_return_status <> 'S' THEN
3656 IF g_debug THEN
3657 g_log_message := 'Error in Kanban Quantity Calculation API';
3658 MRP_UTIL.MRP_LOG (g_log_message);
3659 END IF;
3660 RETURN FALSE;
3661 END IF;
3662
3663 IF l_pull_sequence_rec.kanban_size IS NULL OR
3664 l_pull_sequence_rec.number_of_cards IS NULL THEN
3665
3666 IF NOT Kanban_Calculation_Pvt (
3667 l_average_demand,
3668 l_pull_sequence_rec.minimum_order_quantity,
3669 l_pull_sequence_rec.fixed_lot_multiplier,
3670 l_pull_sequence_rec.safety_stock_days,
3671 l_pull_sequence_rec.replenishment_lead_time,
3672 l_pull_sequence_rec.calculate_kanban_flag,
3673 l_pull_sequence_rec.kanban_size,
3674 l_pull_sequence_rec.number_of_cards ) THEN
3675
3676 IF g_debug THEN
3677 g_log_message := 'Error in Kanban Calculation function';
3678 MRP_UTIL.MRP_LOG (g_log_message);
3679 END IF;
3680 RETURN FALSE;
3681
3682 END IF;
3683 END IF;
3684
3685 -- Now go ahead and insert/update into mtl_kanban_pull_sequences table
3686 -- information about this kanban item/location for this kanban plan
3687
3688 SELECT
3689 fnd_global.conc_request_id,
3690 fnd_global.prog_appl_id,
3691 fnd_global.conc_program_id,
3692 sysdate,
3693 sysdate,
3694 fnd_global.user_id,
3695 sysdate,
3696 fnd_global.user_id
3697 INTO
3698 l_pull_sequence_rec.request_id,
3699 l_pull_sequence_rec.program_application_id,
3700 l_pull_sequence_rec.program_id,
3701 l_pull_sequence_rec.program_update_date,
3702 l_pull_sequence_rec.last_update_date,
3703 l_pull_sequence_rec.last_updated_by,
3704 l_pull_sequence_rec.creation_date,
3705 l_pull_sequence_rec.created_by
3706 FROM dual;
3707
3708 l_pull_sequence_rec.organization_id := g_kanban_info_rec.organization_id;
3709 l_pull_sequence_rec.kanban_plan_id := g_kanban_info_rec.kanban_plan_id;
3710 l_pull_sequence_rec.inventory_item_id := l_item_id;
3711 l_pull_sequence_rec.subinventory_name := l_subinventory;
3712 l_pull_sequence_rec.locator_id := l_locator_id;
3713 l_pull_sequence_rec.pull_sequence_id := NULL;
3714
3715 IF g_debug THEN
3716 g_log_message := 'Kanban Size after Calculation : '
3717 || to_char(l_pull_sequence_rec.kanban_size);
3718 MRP_UTIL.MRP_LOG (g_log_message);
3719 g_log_message := 'Kanban Number after Calculation : '
3720 || to_char(l_pull_sequence_rec.number_of_cards);
3721 MRP_UTIL.MRP_LOG (g_log_message);
3722 END IF;
3723
3724 -- call the inventory api for inserting into mtl_pull_sequences
3725 l_return_status := 'S'; -- initialize to success
3726
3727 IF nvl(g_kanban_info_rec.replan_flag,2) = 2 THEN -- not replan
3728 INV_Kanban_PVT.Insert_pull_sequence
3729 (l_return_status,
3730 l_pull_sequence_rec);
3731 ELSIF g_kanban_info_rec.replan_flag = 1 THEN -- replan
3732 INV_Kanban_PVT.Update_pull_sequence
3733 (l_return_status,
3734 l_pull_sequence_rec);
3735 END If;
3736
3737 IF l_return_status <> 'S' THEN
3738 IF g_debug THEN
3739 g_log_message := 'Error in Inventory Insert/Update API';
3740 MRP_UTIL.MRP_LOG (g_log_message);
3741 g_log_message := 'Return Code : ' || l_return_status;
3742 MRP_UTIL.MRP_LOG (g_log_message);
3743 END IF;
3744 RETURN FALSE;
3745 END IF;
3746
3747 END IF;
3748
3749 CLOSE cur_pull_sequence;
3750
3751 END LOOP;
3752
3753 --we are now done and can close the cursor
3754 CLOSE cur_kanban_demand;
3755
3756 RETURN TRUE;
3757
3758 --exception handling
3759 EXCEPTION
3760 WHEN OTHERS THEN
3761 g_log_message := 'CALCULATE_KANBAN_QUANTITY Sql Error ';
3762 MRP_UTIL.MRP_LOG (g_log_message);
3763 g_log_message := sqlerrm;
3764 MRP_UTIL.MRP_LOG (g_log_message);
3765 RETURN FALSE;
3766
3767 END Calculate_Kanban_Quantity;
3768
3769 -- ========================================================================
3770 -- This is the main procedure that controls the flow of the kanban planning
3771 -- process
3772 -- ========================================================================
3773
3774 PROCEDURE PLAN_KANBAN( ERRBUF OUT NOCOPY VARCHAR2,
3775 RETCODE OUT NOCOPY NUMBER,
3776 p_organization_id IN NUMBER,
3777 p_kanban_plan_id IN NUMBER,
3778 p_from_item IN VARCHAR2,
3779 p_to_item IN VARCHAR2,
3780 p_category_set_id IN NUMBER,
3781 p_category_structure_id IN NUMBER,
3782 p_from_category IN VARCHAR2,
3783 p_to_category IN VARCHAR2,
3784 p_bom_effectivity IN VARCHAR2,
3785 p_start_date IN VARCHAR2,
3786 p_cutoff_date IN VARCHAR2,
3787 p_replan_flag IN NUMBER ) IS
3788
3789 -- declare some local variable here
3790 l_llc_rec llc_rec_type;
3791 l_curr_ll_code number;
3792 l_ret_val boolean;
3793 l_total_workdays number;
3794
3795 var_trace boolean;
3796 c integer;
3797 statement varchar2(255);
3798 rows_processed integer;
3799
3800 -- declare exceptions we want to handle here
3801 exc_error_condition exception;
3802
3803 BEGIN
3804
3805 g_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
3806
3807 var_trace := fnd_profile.value('MRP_TRACE') = 'Y';
3808 if var_trace then
3809 c := dbms_sql.open_cursor;
3810 statement := 'alter session set sql_trace=true';
3811 dbms_sql.parse(c, statement, dbms_sql.native);
3812 rows_processed := dbms_sql.execute(c);
3813 dbms_sql.close_cursor(c);
3814 end if;
3815
3816 g_stmt_num := 10;
3817 IF g_debug THEN
3818 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3819 MRP_UTIL.MRP_LOG (g_log_message);
3820 END IF;
3821
3822 -- check for mandatory parameters
3823 -- and issue appropriate error messages
3824 IF g_debug THEN
3825 IF p_organization_id IS NULL THEN
3826 g_log_message := 'Error : Organization Id is null';
3827 ELSIF p_kanban_plan_id IS NULL THEN
3828 g_log_message := 'Error : Kanban Plan Id is null';
3829 ELSIF (p_bom_effectivity IS NULL AND p_replan_flag = 2) THEN
3830 g_log_message := 'Error : BOM effectivity date is null';
3831 ELSIF (p_start_date IS NULL AND p_replan_flag = 2) THEN
3832 g_log_message := 'Error : Start date is null';
3833 ELSIF (p_cutoff_date IS NULL AND p_replan_flag = 2) THEN
3834 g_log_message := 'Error : Cutoff date is null';
3835 END IF;
3836 END IF;
3837
3838 IF p_organization_id IS NULL OR p_kanban_plan_id IS NULL OR
3839 ((p_bom_effectivity IS NULL OR p_cutoff_date IS NULL) AND
3840 p_replan_flag = 2) THEN
3841 MRP_UTIL.MRP_LOG (g_log_message);
3842 raise exc_error_condition;
3843 END IF;
3844
3845 -- get all the parameters passed to this concurrent program into
3846 -- the global record variable
3847 g_kanban_info_rec.organization_id := p_organization_id;
3848 g_kanban_info_rec.kanban_plan_id := p_kanban_plan_id;
3849 g_kanban_info_rec.from_item := p_from_item;
3850 g_kanban_info_rec.to_item := p_to_item;
3851 g_kanban_info_rec.category_set_id := p_category_set_id;
3852 g_kanban_info_rec.category_structure_id := p_category_structure_id;
3853 g_kanban_info_rec.from_category := p_from_category;
3854 g_kanban_info_rec.to_category := p_to_category;
3855 g_kanban_info_rec.bom_effectivity :=
3856 to_date(p_bom_effectivity,'YYYY/MM/DD HH24:MI:SS');
3857 g_kanban_info_rec.start_date :=
3858 to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
3859 g_kanban_info_rec.cutoff_date :=
3860 to_date(p_cutoff_date,'YYYY/MM/DD HH24:MI:SS');
3861 g_kanban_info_rec.replan_flag := p_replan_flag;
3862
3863 IF g_debug THEN
3864 g_log_message := 'Parameters passed to the program :';
3865 MRP_UTIL.MRP_LOG (g_log_message);
3866 g_log_message := 'organization_id : ' || p_organization_id;
3867 MRP_UTIL.MRP_LOG (g_log_message);
3868 g_log_message := 'kanban_plan_id : ' || p_kanban_plan_id;
3869 MRP_UTIL.MRP_LOG (g_log_message);
3870 g_log_message := 'from_item_id : ' || p_from_item;
3871 MRP_UTIL.MRP_LOG (g_log_message);
3872 g_log_message := 'to_item_id : ' || p_to_item;
3873 MRP_UTIL.MRP_LOG (g_log_message);
3874 g_log_message := 'category_set_id : ' || p_category_set_id;
3875 MRP_UTIL.MRP_LOG (g_log_message);
3876 g_log_message := 'category_structure_id : ' || p_category_structure_id;
3877 MRP_UTIL.MRP_LOG (g_log_message);
3878 g_log_message := 'from_category_id : ' || p_from_category;
3879 MRP_UTIL.MRP_LOG (g_log_message);
3880 g_log_message := 'to_category_id : ' || p_to_category;
3881 MRP_UTIL.MRP_LOG (g_log_message);
3882 g_log_message := 'bom_effectivity : ' || p_bom_effectivity;
3883 MRP_UTIL.MRP_LOG (g_log_message);
3884 g_log_message := 'start_date : ' || p_start_date;
3885 MRP_UTIL.MRP_LOG (g_log_message);
3886 g_log_message := 'cutoff_date : ' || p_cutoff_date;
3887 MRP_UTIL.MRP_LOG (g_log_message);
3888 g_log_message := 'replan_flag : ' || p_replan_flag;
3889 MRP_UTIL.MRP_LOG (g_log_message);
3890 END IF;
3891
3892 -- call the start_kanban function
3893 IF NOT Start_Kanban_Plan THEN
3894 g_log_message := 'Error in START_KANBAN_PLAN';
3895 MRP_UTIL.MRP_LOG (g_log_message);
3896 raise exc_error_condition;
3897 END IF;
3898
3899 /*
3900 this has been moved to Start_Kanban_Plan procedure
3901 because we now want to commit after the snapshot is over so that
3902 there is less impact on rollback segment
3903
3904 -- call the procedure to snapshot the item/locations and
3905 -- populate mrp_low_level_codes table.
3906 -- Note that we are calculating low level codes
3907 -- only to detect loops and not for planning purposes.
3908 -- We gather demand by looking at the input to the plan
3909 -- and then blow it down to the component item/locations
3910
3911 IF NOT mrp_kanban_snapshot_pk.snapshot_item_locations THEN
3912 g_log_message := 'Error in SNAPSHOT_ITEM_LOCATIONS';
3913 MRP_UTIL.MRP_LOG (g_log_message);
3914 raise exc_error_condition;
3915 END IF;
3916 */
3917 g_stmt_num := 150;
3918 IF g_debug THEN
3919 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3920 MRP_UTIL.MRP_LOG (g_log_message);
3921 END IF;
3922
3923 -- now call the procedure to retrieve the kanban demand based on
3924 -- the input to the kanban plan and pass it down to the components
3925 -- as in the mrp_low_level_codes table
3926
3927 IF NOT Retrieve_Kanban_Demand THEN
3928 g_log_message := 'Error in RETRIEVE_KANBAN_DEMAND';
3929 MRP_UTIL.MRP_LOG (g_log_message);
3930 raise exc_error_condition;
3931 END IF;
3932
3933 -- We now have all the demand for this plan run stored in the
3934 -- mrp_kanban_demand table. So go ahead and call the procedure
3935 -- to calculate Kanban quantities for the kanban items included
3936 -- in this plan
3937
3938 --first calculate the total number of workdays between start date
3939 --and cutoff date specified
3940 SELECT count(*)
3941 INTO l_total_workdays
3942 FROM bom_calendar_dates bcd,
3943 mtl_parameters mp
3944 WHERE mp.organization_id = g_kanban_info_rec.organization_id
3945 AND bcd.calendar_code = mp.calendar_code
3946 AND bcd.seq_num IS NOT NULL
3947 AND (bcd.calendar_date BETWEEN g_kanban_info_rec.start_date AND
3948 g_kanban_info_rec.cutoff_date );
3949
3950
3951 g_stmt_num := 180;
3952 IF g_debug THEN
3953 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3954 MRP_UTIL.MRP_LOG (g_log_message);
3955 END IF;
3956
3957 IF NOT (Calculate_Kanban_Quantity (l_total_workdays)) THEN
3958 g_log_message := 'Error in CALCULATE_KANBAN_QUANTITY';
3959 MRP_UTIL.MRP_LOG (g_log_message);
3960 raise exc_error_condition;
3961 END IF;
3962
3963 g_stmt_num := 190;
3964 IF g_debug THEN
3965 g_log_message := 'Debug Statement Number : ' || to_char (g_stmt_num);
3966 MRP_UTIL.MRP_LOG (g_log_message);
3967 END IF;
3968
3969 -- if we come here then we are done with the planning and we can
3970 -- update the plan_completion_date
3971
3972 l_ret_val := End_Kanban_Plan;
3973
3974 IF NOT l_ret_val THEN
3975 g_log_message := 'Error in END_KANBAN_PLAN';
3976 MRP_UTIL.MRP_LOG (g_log_message);
3977 raise exc_error_condition;
3978 END IF;
3979
3980 IF g_raise_warning THEN
3981 ERRBUF := 'Kanban Planning Engine completed with warning';
3982 RETCODE := G_WARNING;
3983 g_raise_warning := FALSE;
3984 ELSE
3985 ERRBUF := 'Kanban Planning Engine completed a successful run';
3986 RETCODE := G_SUCCESS;
3987 END IF;
3988
3989 -- Exception Handling
3990
3991 EXCEPTION
3992
3993 WHEN exc_error_condition THEN
3994 ROLLBACK;
3995 RETCODE := G_ERROR;
3996 ERRBUF := 'Program Completed with Error : ' || sqlerrm;
3997 g_log_message := 'Program encountered Error condition Exception';
3998 MRP_UTIL.MRP_LOG (g_log_message);
3999
4000 WHEN OTHERS THEN
4001 ROLLBACK;
4002 RETCODE := G_ERROR;
4003 ERRBUF := 'Program Completed with Error : ' || sqlerrm;
4004 g_log_message := 'PLAN_KANBAN Sql Error ';
4005 MRP_UTIL.MRP_LOG (g_log_message);
4006 g_log_message := sqlerrm;
4007 MRP_UTIL.MRP_LOG (g_log_message);
4008
4009
4010 END PLAN_KANBAN;
4011
4012 END MRP_KANBAN_PLAN_PK;
[Home] [Help]