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