1 PACKAGE BODY MRP_KANBAN_SNAPSHOT_PK AS
2 /* $Header: MRPKSNPB.pls 120.5.12020000.2 2012/07/12 09:58:26 sisankar ship $ */
3
4
5 -- ========================================================================
6 -- This function checks if the from locations (from subinventory and from
7 -- locator ) are null in the mrp_low_level_codes table and populates them
8 -- ========================================================================
9 FUNCTION CHECK_ITEM_LOCATIONS RETURN BOOLEAN IS
10
11 l_count number;
12 l_item varchar2(40);
13
14 -- declare a cursor for detailed debug information. This will be used
15 -- only if debug is turned on.
16 CURSOR cur_debug is
17 SELECT distinct msi.concatenated_segments
18 FROM mtl_system_items_kfv msi, mrp_low_level_codes mllc
19 WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
20 AND mllc.organization_id =
21 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
22 AND mllc.from_subinventory IS NULL
23 AND mllc.from_locator_id IS NULL
24 AND mllc.kanban_item_flag = 'Y'
25 AND mllc.assembly_item_id <> mllc.component_item_id
26 AND msi.organization_id = mllc.organization_id
27 AND msi.inventory_item_id = mllc.component_item_id;
28
29
30 BEGIN
31
32 mrp_kanban_plan_pk.g_stmt_num := 120;
33 IF mrp_kanban_plan_pk.g_debug THEN
34 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
35 || to_char (mrp_kanban_plan_pk.g_stmt_num);
36 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
37 mrp_kanban_plan_pk.g_log_message := 'Entering Check_Item_Locations Procedure';
38 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
39 END IF;
40
41
42 -- first check if any of the kanban items have null locations
43 SELECT count(*)
44 INTO l_count
45 FROM mrp_low_level_codes
46 WHERE plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
47 AND organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
48 AND from_subinventory IS NULL
49 AND from_locator_id IS NULL
50 AND kanban_item_flag = 'Y'
51 AND assembly_item_id <> component_item_id;
52
53
54 /* This section will be enabled for R12, We will not have any data in
55 bom_inventory_backflush_subinv for R11
56
57
58 -- this is the first thing we do to get the sub and locator information
59 IF l_count > 0 THEN
60
61 UPDATE mrp_low_level_codes mllc
62 SET (mllc.from_subinventory, mllc.from_locator_id) =
63 (SELECT bibs.SUBINVENTORY_NAME, bibs.LOCATOR_ID
64 FROM bom_inventory_backflush_subinv bibs
65 AND bibs.inventory_item_id = mllc.component_item_id
66 AND bibs.organization_id = mllc.organization_id
67 AND bibs.location_type = 1) -- ??
68 WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
69 AND mllc.organization_id =
70 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
71 AND mllc.from_subinventory IS NULL
72 AND mllc.from_locator_id IS NULL
73 AND mllc.kanban_item_flag = 'Y'
74 AND mllc.assembly_item_id <> mllc.component_item_id;
75
76 END IF;
77
78
79 -- now check again to see if we have any kanban items with null locations
80 SELECT count(*)
81 INTO l_count
82 FROM mrp_low_level_codes
83 WHERE plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
84 AND organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
85 AND from_subinventory IS NULL
86 AND from_locator_id IS NULL
87 AND kanban_item_flag = 'Y'
88 AND assembly_item_id <> component_item_id;
89
90 end of section - enable for R12 */
91
92 -- if we now still have some records with null sub and locator
93 -- we go after the item master for the information
94
95 IF l_count > 0 THEN
96
97 UPDATE mrp_low_level_codes mllc
98 SET (mllc.from_subinventory, mllc.from_locator_id) =
99 (SELECT msi.wip_supply_subinventory,
100 msi.wip_supply_locator_id
101 FROM mtl_system_items msi
102 WHERE msi.organization_id = mllc.organization_id
103 AND msi.inventory_item_id = mllc.component_item_id)
104 WHERE mllc.plan_id =
105 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
106 AND mllc.organization_id =
107 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
108 AND mllc.from_subinventory IS NULL
109 AND mllc.from_locator_id IS NULL
110 AND mllc.kanban_item_flag = 'Y'
111 AND mllc.assembly_item_id <> mllc.component_item_id;
112
113 END IF;
114
115 -- now check again if we have any kanban items with incomplete from
116 -- location information. If so error out
117 SELECT count(*)
118 INTO l_count
119 FROM mrp_low_level_codes
120 WHERE plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
121 AND organization_id = mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
122 AND from_subinventory IS NULL
123 AND from_locator_id IS NULL
124 AND kanban_item_flag = 'Y'
125 AND assembly_item_id <> component_item_id;
126
127 IF l_count > 0 THEN
128 IF mrp_kanban_plan_pk.g_debug THEN
129 mrp_kanban_plan_pk.g_log_message := 'End of CHECK_ITEM_LOCATIONS function';
130 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
131 END IF;
132
133 l_count := 0;
134 OPEN cur_debug;
135
136 WHILE TRUE LOOP
137
138 FETCH cur_debug INTO l_item;
139
140 EXIT WHEN cur_debug%NOTFOUND;
141
142 IF mrp_kanban_plan_pk.g_debug THEN
143 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_ITEM_INCOMP_LOC');
144 FND_MESSAGE.SET_TOKEN ('ITEMNAME', l_item);
145 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
146 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
147 END IF;
148
149 l_count := l_count + 1;
150
151 END LOOP;
152
153 IF mrp_kanban_plan_pk.g_debug THEN
154 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_INCOMP_LOC');
155 FND_MESSAGE.SET_TOKEN ('NUMITEMS', to_char(l_count));
156 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
157 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
158 END IF;
159
160 --set the flag to return warning here
161 MRP_KANBAN_PLAN_PK.g_raise_warning := TRUE;
162
163 END IF;
164
165 RETURN TRUE;
166
167 --Exception handling
168 EXCEPTION
169
170 WHEN OTHERS THEN
171 mrp_kanban_plan_pk.g_log_message := 'CHECK_ITEM_LOCATIONS Sql Error ';
172 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
173 mrp_kanban_plan_pk.g_log_message := sqlerrm;
174 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
175 RETURN FALSE;
176
177 END CHECK_ITEM_LOCATIONS;
178
179 -- ========================================================================
180 -- This function checks for presence of loops in the bill structure
181 -- build in the mrp_low_level_codes table and errors out if we find one
182 -- ========================================================================
183 FUNCTION CHECK_FOR_LOOPS RETURN BOOLEAN IS
184
185 l_count number;
186 exc_loop_error exception;
187 l_loop_found boolean := FALSE;
188 l_logged_loop_err_msg boolean := FALSE;
189
190 l_assembly_item_id number;
191 l_to_subinventory varchar2(10);
192 l_to_locator_id number;
193 l_component_item_id number;
194 l_from_subinventory varchar2(10);
195 l_from_locator_id number;
196 l_parent_item varchar2(40);
197 l_child_item varchar2(40);
198 l_parent_loc number;
199 l_child_loc number;
200
201 CURSOR cur_loop_check IS
202 SELECT parent.concatenated_segments assembly_item,
203 mllc.to_subinventory,
204 parent_loc.inventory_location_id to_location,
205 child.concatenated_segments component_item,
206 mllc.from_subinventory,
207 child_loc.inventory_location_id from_location
208 FROM
209 mtl_item_locations parent_loc,
210 mtl_item_locations child_loc,
211 mtl_system_items_kfv parent,
212 mtl_system_items_kfv child,
213 mrp_low_level_codes mllc
214 WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
215 AND mllc.organization_id =
216 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
217 AND mllc.low_level_code IS NULL
218 AND parent.inventory_item_id = mllc.assembly_item_id
219 AND parent.organization_id = mllc.organization_id
220 AND child.inventory_item_id = mllc.component_item_id
221 AND child.organization_id = mllc.organization_id
222 AND parent_loc.inventory_location_id (+) = mllc.to_locator_id
223 AND parent_loc.organization_id (+) = mllc.organization_id
224 AND child_loc.inventory_location_id (+) = mllc.from_locator_id
225 AND child_loc.organization_id (+) = mllc.organization_id
226 ORDER BY
227 assembly_item,
228 component_item;
229
230 BEGIN
231
232 IF mrp_kanban_plan_pk.g_debug THEN
233 mrp_kanban_plan_pk.g_log_message := 'In Check_For_Loops Procedure';
234 fnd_file.put_line (fnd_file.log, mrp_kanban_plan_pk.g_log_message);
235 END IF;
236
237 -- We just go and check if we have assigned a low level code value
238 -- to every entry for the current plan. If we have at least one
239 -- record without a low level code, this indicates the presence of
240 -- a loop and we error out
241
242 l_logged_loop_err_msg := FALSE; -- flag to help us log the loop check
243 -- error message only once in the log file
244
245 OPEN cur_loop_check;
246
247 WHILE TRUE LOOP
248 FETCH cur_loop_check
249 INTO l_parent_item,
250 l_to_subinventory,
251 l_parent_loc,
252 l_child_item,
253 l_from_subinventory,
254 l_child_loc;
255
256 EXIT WHEN cur_loop_check%NOTFOUND;
257
258 l_loop_found := TRUE;
259
260 IF not l_logged_loop_err_msg THEN
261 IF mrp_kanban_plan_pk.g_debug THEN
262 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_LOOP_ERROR');
263 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
264 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
265
266 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_LOOP_INFO_START');
267 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
268 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
269 END IF;
270
271 l_logged_loop_err_msg := TRUE;
272
273 END IF;
274
275 -- now go ahead and log messages giving details of the loop found
276
277 IF mrp_kanban_plan_pk.g_debug THEN
278 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_LOOP_INFO');
279 FND_MESSAGE.SET_TOKEN ('PARENT_ITEM', l_parent_item);
280 FND_MESSAGE.SET_TOKEN ('PARENT_SUB', l_to_subinventory);
281 FND_MESSAGE.SET_TOKEN ('PARENT_LOC', to_char(l_parent_loc));
282 FND_MESSAGE.SET_TOKEN ('CHILD_ITEM', l_child_item);
283 FND_MESSAGE.SET_TOKEN ('CHILD_SUB', l_from_subinventory);
284 FND_MESSAGE.SET_TOKEN ('CHILD_LOC', to_char(l_child_loc));
285 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
286 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
287 END IF;
288
289 END LOOP;
290
291 CLOSE cur_loop_check;
292
293 IF l_loop_found THEN
294
295 IF mrp_kanban_plan_pk.g_debug THEN
296 FND_MESSAGE.SET_NAME ('MRP','MRP_KANBAN_LOOP_INFO_END');
297 mrp_kanban_plan_pk.g_log_message := FND_MESSAGE.GET;
298 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
299 END IF;
300
301 raise exc_loop_error;
302 END IF;
303
304 RETURN TRUE;
305
306 EXCEPTION
307 WHEN exc_loop_error THEN
308 RETURN FALSE;
309
310 WHEN OTHERS THEN
311 mrp_kanban_plan_pk.g_log_message := 'CHECK_FOR_LOOPS Sql Error ';
312 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
313 mrp_kanban_plan_pk.g_log_message := sqlerrm;
314 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
315 RETURN FALSE;
316
317
318 END CHECK_FOR_LOOPS;
319
320 -- ========================================================================
321 -- location combinations in mrp_low_level_codes table
322 -- ========================================================================
323
324 FUNCTION CALC_LOW_LEVEL_CODE RETURN BOOLEAN
325 IS
326
327 l_low_level_code number;
328
329 BEGIN
330
331 IF mrp_kanban_plan_pk.g_debug THEN
332 mrp_kanban_plan_pk.g_log_message := 'In Calc_Low_Level_Codes Procedure';
333 fnd_file.put_line (fnd_file.log, mrp_kanban_plan_pk.g_log_message);
334 END IF;
335
336 -- start calculating the low level codes
337 -- we start by assigning a low_level_code of 1000 to the lowest level
338 -- item/location combinations in the bill structure and then go up the
339 -- bill structure in a loop.
340 -- Note that the item for which low level code is being assigned is
341 -- the component_item_id NOT the assembly_item_id
342
343 l_low_level_code := 1000; /* initialize */
344
345 WHILE TRUE LOOP
346
347 UPDATE mrp_low_level_codes mllc1
348 SET mllc1.low_level_code = l_low_level_code
349 WHERE mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
350 AND mllc1.organization_id =
351 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
352 AND mllc1.low_level_code IS NULL
353 AND NOT EXISTS
354 (SELECT /*+index(mllc2 MRP_LOW_LEVEL_CODES_N1)*/ 'Exists as parent' /* Bug 4608294 - added hint*/
355 FROM mrp_low_level_codes mllc2
356 WHERE mllc2.plan_id = mllc1.plan_id
357 AND mllc2.organization_id = mllc1.organization_id
358 AND mllc2.low_level_code IS NULL
359 AND ( mllc2.assembly_item_id = mllc1.component_item_id AND
360 ((((mllc2.to_subinventory = mllc1.from_subinventory AND
361 nvl(mllc2.to_locator_id,-1) =
362 nvl(mllc1.from_locator_id, -1) ) OR
363 mllc2.to_subinventory is NULL ) AND
364 nvl(mllc1.kanban_item_flag,'N') = 'Y') OR
365 nvl(mllc1.kanban_item_flag,'N') = 'N'))
366
367 );
368
369 EXIT WHEN SQL%ROWCOUNT = 0;
370
371 l_low_level_code := l_low_level_code - 1;
372
373 END LOOP; /* end while loop */
374
375 RETURN TRUE;
376
377 -- exception handling
378 EXCEPTION
379 WHEN OTHERS THEN
380 mrp_kanban_plan_pk.g_log_message := 'CALC_LOW_LEVEL_CODE Sql Error ';
381 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
382 mrp_kanban_plan_pk.g_log_message := sqlerrm;
383 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
384 RETURN FALSE;
385
386 END CALC_LOW_LEVEL_CODE;
387
388 -- ========================================================================
389 -- This function builds the where clause for the item range specified
390 -- The where clause is used in the first select statement while
391 -- snapshotting kanban items
392 -- ========================================================================
393 FUNCTION Item_Where_Clause( p_item_lo IN VARCHAR2,
394 p_item_hi IN VARCHAR2,
395 p_table_name IN VARCHAR2,
396 p_where OUT NOCOPY VARCHAR2 )
397 RETURN BOOLEAN IS
398
399 /* This function is obsoleted since we now use the same-named function in
400 package 'flm_util', where we use bind variables. */
401
402 BEGIN
403
404 RETURN TRUE;
405
406 --exception handling
407 EXCEPTION
408 WHEN OTHERS THEN
409 RETURN FALSE;
410
411 END Item_Where_Clause;
412
413
414 -- ========================================================================
415 -- This function builds the where clause for the category range specified
416 -- The where clause is used in the first select statement while
417 -- snapshotting kanban items
418 -- ========================================================================
419 FUNCTION Category_Where_Clause ( p_cat_lo IN VARCHAR2,
420 p_cat_hi IN VARCHAR2,
421 p_table_name IN VARCHAR2,
422 p_where OUT NOCOPY VARCHAR2 )
423 RETURN BOOLEAN IS
424
425 /* This function is obsoleted since we now use the same-named function in
426 package 'flm_util', where we use bind variables. */
427
428 BEGIN
429
430 RETURN TRUE;
431
432 --exception handling
433 EXCEPTION
434 WHEN OTHERS THEN
435 RETURN FALSE;
436
437 END Category_Where_Clause;
438
439 -- ========================================================================
440 -- This function returns 1 if the alternate designator passed corresponds
441 -- to the alternate_routing_designator with the highest priority, else
442 -- it returns -1
443 -- ========================================================================
444 FUNCTION Check_Min_Priority
445 ( p_assembly_item_id IN NUMBER,
446 p_organization_id IN NUMBER,
447 p_line_id IN NUMBER,
448 p_alternate_designator IN VARCHAR2)
449 RETURN NUMBER IS
450
451 l_dummy VARCHAR2(30);
452 l_highest_priority NUMBER := NULL;
453 l_num_routings NUMBER := 0;
454
455 BEGIN
456
457 IF p_assembly_item_id IS NULL OR p_organization_id IS NULL
458 OR p_line_id IS NULL THEN
459 IF p_alternate_designator IS NULL THEN
460 RETURN 1;
461 ELSE
462 RETURN -1;
463 END IF;
464 END IF;
465
466 -- ---------------------------------------------------
467 -- Find the number of routings for this given line
468 -- Also find the highest priority among the routings
469 -- ---------------------------------------------------
470 SELECT min(bor.priority), count(*)
471 INTO l_highest_priority, l_num_routings
472 FROM bom_operational_routings bor
473 WHERE bor.organization_id = p_organization_id
474 AND bor.assembly_item_id = p_assembly_item_id
475 AND bor.cfm_routing_flag = 1
476 AND bor.line_id = p_line_id;
477
478 -- ---------------------------------------------------
479 -- If there no routings for this given line
480 -- we return true if p_alternate_designator is the primary
481 -- routing designator (ie IS NULL)
482 -- ---------------------------------------------------
483 IF l_num_routings = 0 THEN
484 IF p_alternate_designator IS NULL THEN
485 RETURN 1;
486 ELSE
487 RETURN -1;
488 END IF;
489 END IF;
490
491 -- ---------------------------------------------------
492 -- IF there are multiple routings for this given line
493 -- all of which has NULL for the priority, we return
494 -- false
495 -- ---------------------------------------------------
496 IF ((l_highest_priority IS NULL) AND (l_num_routings > 1) AND (p_alternate_designator IS NOT NULL) ) THEN
497 RETURN -1;
498 END IF;
499
500
501 SELECT 'Condition Satisfied'
502 INTO l_dummy
503 FROM bom_operational_routings bor
504 WHERE bor.organization_id = p_organization_id
505 AND bor.assembly_item_id = p_assembly_item_id
506 AND bor.line_id = p_line_id
507 AND bor.cfm_routing_flag = 1
508 AND NVL(bor.priority,-1) = NVL(l_highest_priority,-1)
509 AND NVL(bor.alternate_routing_designator,'xx') =
510 NVL(p_alternate_designator, 'xx');
511
512 RETURN 1;
513
514 Exception
515 WHEN NO_DATA_FOUND THEN
516 RETURN -1;
517
518 WHEN OTHERS THEN
519 RETURN -1;
520
521 END Check_Min_Priority;
522
523 FUNCTION Check_assy_cfgitem
524 (p_assembly_item_id IN NUMBER,
525 p_comp_item_id IN NUMBER,
526 p_organization_id IN NUMBER)
527 RETURN NUMBER IS
528
529 /* Declare cursor for assembly item to check whether it is a configured item */
530
531 CURSOR config_item_flag(p_assemply_item_id IN NUMBER,
532 p_organization_id IN NUMBER) IS
533
534 SELECT msi.base_item_id,msi.bom_item_type
535 FROM mtl_system_items msi
536 WHERE msi.inventory_item_id = p_assembly_item_id
537 AND msi.organization_id = p_organization_id;
538
539 config_item_flag_rec config_item_flag%ROWTYPE;
540
541 /* Declare cursor to determine if the component is a Model or an Option class */
542
543 CURSOR comp_type(p_comp_item_id IN NUMBER,
544 p_organization_id IN NUMBER) IS
545
546 SELECT msi.bom_item_type
547 FROM mtl_system_items msi
548 WHERE msi.inventory_item_id = p_comp_item_id
549 AND msi.organization_id = p_organization_id;
550
551 comp_type_rec comp_type%ROWTYPE;
552
553
554 BEGIN
555
556
557 OPEN config_item_flag(p_assembly_item_id,
558 p_organization_id);
559 FETCH config_item_flag into config_item_flag_rec;
560
561 -- If the assembly item is not a configured item, return 1
562
563 IF (config_item_flag_rec.base_item_id IS NULL) THEN
564 RETURN 1;
565 ELSE
566 OPEN comp_type(p_comp_item_id ,
567 p_organization_id );
568 FETCH comp_type into comp_type_rec;
569
570 -- If assembly is a configured item and the component is Model or Option class,
571 -- do not create demand for the item
572
573 IF(comp_type_rec.bom_item_type IN (1,2)) THEN
574 RETURN -1;
575 ELSE
576 RETURN 1;
577 END IF;
578 END IF;
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 RETURN -1;
583
584 END check_assy_cfgitem;
585
586 -- ========================================================================
587 --
588 -- This procedure will identify the list of items that need to be included
589 -- in the current kanban plan run. User can limit thelist of items by
590 -- specifying an item range or item categroy. It will select the bill
591 -- structure of these items and insert them into the table mrp_kanban_ll_code.
592 --
593 -- ========================================================================
594 FUNCTION SNAPSHOT_ITEM_LOCATIONS RETURN BOOLEAN IS
595
596 l_level_count number;
597 l_sql_stmt varchar2(5000):= NULL;
598 l_item_where_clause varchar2(750) := NULL;
599 l_cat_where_clause varchar2(750) := NULL;
600 l_additional_tables varchar2(150) := NULL;
601 l_additional_where varchar2(2000):= NULL;
602 l_cursor integer;
603 l_rows_processed integer;
604 l_quote varchar2(1) := '''';
605 l_ret_val boolean;
606 l_eco_profile boolean := TRUE;
607 l_error_buf varchar2(2000);
608
609 BEGIN
610
611 mrp_kanban_plan_pk.g_stmt_num := 30;
612 IF mrp_kanban_plan_pk.g_debug THEN
613 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
614 || to_char (mrp_kanban_plan_pk.g_stmt_num);
615 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
616 mrp_kanban_plan_pk.g_log_message :=
617 'Entering Snapshot_Item_Locations function';
618 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
619 END IF;
620
621
622 l_eco_profile := FND_PROFILE.VALUE('FLM_KANBAN_ECO') = 'Y';
623
624 if (l_eco_profile is null)
625 then
626 l_eco_profile := TRUE ;
627 end if ;
628
629 -- ------------------------------------------------------------------------
630 -- if we are not replanning, then go ahead and get the initial set of items
631 -- based on the parameters passed to the concurrent program
632 -- ------------------------------------------------------------------------
633
634 IF nvl(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag, 2 ) = 2 THEN
635
636 -- now lets go ahead and build our dynamic sql statement based on
637 -- the item/category range and category set parameter values
638
639 flm_util.init_bind;
640
641 -- include category set in the sql stmt
642 l_additional_tables := ' mtl_item_categories mic, ';
643 l_additional_where :=
644 ' AND mic.category_set_id (+) = :b_category_set_id' ||
645 ' AND mic.inventory_item_id (+) = msi.inventory_item_id' ||
646 ' AND mic.organization_id (+) = msi.organization_id ';
647
648 -- now check if item range has been specified and add the extra condition
649 -- if necessary
650
651 mrp_kanban_plan_pk.g_stmt_num := 40;
652 IF mrp_kanban_plan_pk.g_debug THEN
653 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
654 || to_char (mrp_kanban_plan_pk.g_stmt_num);
655 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
656 END IF;
657
658 IF mrp_kanban_plan_pk.g_kanban_info_rec.from_item IS NOT NULL OR
659 mrp_kanban_plan_pk.g_kanban_info_rec.to_item IS NOT NULL THEN
660
661 -- call the function that builds the item where clause
662 l_ret_val := flm_util.Item_Where_Clause(
663 mrp_kanban_plan_pk.g_kanban_info_rec.from_item,
664 mrp_kanban_plan_pk.g_kanban_info_rec.to_item,
665 'msi',
666 l_item_where_clause,
667 l_error_buf);
668
669 IF NOT l_ret_val THEN
670 RETURN FALSE;
671 END IF;
672
673 IF l_item_where_clause IS NOT NULL and trim(l_item_where_clause) IS NOT NULL THEN
674 l_additional_where := ' AND ' || l_item_where_clause ||
675 l_additional_where;
676
677 IF mrp_kanban_plan_pk.g_debug THEN
678 mrp_kanban_plan_pk.g_log_message := 'Item Where Clause is : ';
679 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
680 mrp_kanban_plan_pk.g_log_message := substr(l_item_where_clause,1,2000);
681 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
682 END IF;
683 END IF;
684
685 END IF;
686
687 -- similarly check if category range has been specified and add the extra
688 -- condition if necessary
689
690 mrp_kanban_plan_pk.g_stmt_num := 50;
691 IF mrp_kanban_plan_pk.g_debug THEN
692 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
693 || to_char (mrp_kanban_plan_pk.g_stmt_num);
694 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
695 END IF;
696
697 IF (mrp_kanban_plan_pk.g_kanban_info_rec.from_category IS NOT NULL OR
698 mrp_kanban_plan_pk.g_kanban_info_rec.to_category IS NOT NULL) AND
699 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id IS NOT NULL THEN
700
701 l_ret_val := flm_util.Category_Where_Clause(
702 mrp_kanban_plan_pk.g_kanban_info_rec.from_category,
703 mrp_kanban_plan_pk.g_kanban_info_rec.to_category,
704 'mcat',
705 mrp_kanban_plan_pk.g_kanban_info_rec.category_structure_id,
706 l_cat_where_clause,
707 l_error_buf);
708
709 IF NOT l_ret_val THEN
710 RETURN FALSE;
711 END IF;
712
713 IF l_cat_where_clause IS NOT NULL and trim(l_cat_where_clause) IS NOT NULL THEN
714 l_additional_tables := 'mtl_categories mcat, ' || l_additional_tables;
715 l_additional_where := l_additional_where || ' AND ' ||
716 ' mcat.category_id = mic.category_id AND ' ||
717 l_cat_where_clause||' ';
718 IF mrp_kanban_plan_pk.g_debug THEN
719 mrp_kanban_plan_pk.g_log_message := 'Category Where Clause is : ';
720 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
721 mrp_kanban_plan_pk.g_log_message := substr(l_cat_where_clause,1,2000);
722 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
723 END IF;
724 END IF;
725
726 END IF;
727
728 -- now here goes the 'cool' sql statement that gets all the kanban planned
729 -- items based on our constraints specified above and inserts into
730 -- mrp_low_codes table
731
732 -- Probably this sql statement needs a little explanation. We are driving
733 -- off of mrp_kanban_plans table where we get the organization id in which
734 -- this plan is defined, then we hit mtl_system_items table to get the
735 -- list of kanban items (here if category set or item/category range is
736 -- is specified, then we impose extra where conditions and join a couple
737 -- of more tables as seen in the sql statement we just built). As we get
738 -- the inventory item id from mtl_system_items , we also get location
739 -- information for this item by joining to bom_inventory_components. We
740 -- are not satisfied with that. So we join bom_bill_of_materials
741 -- to get the assembly_item_id and its location information which is
742 -- (if its a kanban item) stored (hopefully) in mtl_kanban_pull_sequences
743 -- table. The catch here is that for a production kind of source type in
744 -- in the replenishment chain we specify a line for the source. We want
745 -- this to be the line that's specified in the CFM routing with priority = 1
746 -- so we end up joining bom_operational_routings table also.
747
748 mrp_kanban_plan_pk.g_stmt_num := 60;
749 IF mrp_kanban_plan_pk.g_debug THEN
750 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
751 || to_char (mrp_kanban_plan_pk.g_stmt_num);
752 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
753 END IF;
754
755 l_sql_stmt :=
756 'INSERT INTO mrp_low_level_codes ( ' ||
757 'plan_id,' ||
758 'organization_id,' ||
759 'assembly_item_id,' ||
760 'to_subinventory,' ||
761 'to_locator_id,' ||
762 'component_item_id,' ||
763 'from_subinventory,' ||
764 'from_locator_id,' ||
765 'component_usage,' ||
766 'component_yield,' ||
767 /* Updated by Liye Ma 4/30/2001
768 Add two more columns, to fix bug 1745046 and 1757798. */
769 'planning_factor,' ||
770 'item_num,' ||
771 /* End of update */
772 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
773 'basis_type,' ||
774 'wip_supply_type,' ||
775 'alternate_designator,' ||
776 'kanban_item_flag,' ||
777 'component_category_id,' ||
778 'levels_below,' ||
779 'request_id,' ||
780 'program_application_id,' ||
781 'program_id,' ||
782 'program_update_date,' ||
783 'last_updated_by,' ||
784 'last_update_date,' ||
785 'created_by,' ||
786 'creation_date )' ||
787 'SELECT /*+ ordered */' ||
788 'mkp.kanban_plan_id,' ||
789 'mkp.organization_id,' ||
790 'bbom.assembly_item_id,' ||
791 'ps.subinventory_name,' ||
792 'ps.locator_id,' ||
793 'msi.inventory_item_id,' ||
794 'mrp_bic.supply_subinventory,' ||
795 'mrp_bic.supply_locator_id,' ||
796 'mrp_bic.component_quantity,' ||
797 'mrp_bic.component_yield_factor,' ||
798 /* Updated by Liye Ma 4/30/2001
799 Add two more columns, to fix bug 1745046 and 1757798. */
800 'mrp_bic.planning_factor,' ||
801 'mrp_bic.item_num,' ||
802 /* End of Update */
803 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
804 /* Basis type of 1 = WIP_CONSTANTS.ITEM_BASED_MTL */
805 'nvl(mrp_bic.basis_type,1),' ||
806 /* Supply type of 1 = WIP_CONSTANTS.PUSH */
807 'nvl(mrp_bic.wip_supply_type,1),' ||
808 'bbom.alternate_bom_designator,' ||
809 l_quote || 'Y' || l_quote || ',' ||
810 'mic.category_id,' ||
811 '1,' ||
812 'fnd_global.conc_request_id,' ||
813 'fnd_global.prog_appl_id,' ||
814 'fnd_global.conc_program_id,' ||
815 'sysdate,' ||
816 'fnd_global.user_id,' ||
817 'sysdate,' ||
818 'fnd_global.user_id,' ||
819 'sysdate ' ||
820 'FROM ' ||
821 'mrp_kanban_plans mkp, ' ||
822 'mtl_system_items msi, ' ||
823 '( SELECT /*+ no_merge */ distinct inventory_item_id ,organization_id ' ||
824 ' FROM mtl_kanban_pull_sequences ' ||
825 ' WHERE kanban_plan_id = :b_PRODUCTION_KANBAN ) iv, ' ||
826 'bom_inventory_components mrp_bic, ' ||
827 'bom_bill_of_materials bbom, ' ||
828 'mtl_kanban_pull_sequences ps, ' ||
829 l_additional_tables ||
830 'mtl_system_items msi2 ' ||
831 'WHERE mkp.kanban_plan_id = :b_kanban_plan_id ' ||
832 'AND mkp.organization_id = :b_organization_id ' ||
833 'AND msi.organization_id = mkp.organization_id ' ||
834 'AND iv.inventory_item_id= msi.inventory_item_id ' ||
835 'AND iv.organization_id = msi.organization_id ' ||
836 l_additional_where ||
837 'AND mrp_bic.component_item_id = msi.inventory_item_id ' ||
838 'AND nvl(mrp_bic.disable_date,:b_bom_effectivity) + 1 >= :b_bom_effectivity ' ||
839 'AND mrp_bic.effectivity_date <= :b_bom_effectivity ';
840
841 if (l_eco_profile = TRUE)
842 then
843 l_sql_stmt := l_sql_stmt ||
844 'AND NOT EXISTS ( ' ||
845 'SELECT /*+ INDEX(bic2 BOM_INVENTORY_COMPONENTS_N1) */ '||
846 'NULL ' ||
847 'FROM bom_inventory_components bic2 ' ||
848 'WHERE bic2.bill_sequence_id = mrp_bic.bill_sequence_id ' ||
849 'AND bic2.component_item_id = mrp_bic.component_item_id ' ||
850 'AND (decode(bic2.implementation_date, null, ' ||
851 'bic2.old_component_sequence_id, ' ||
852 'bic2.component_sequence_id) = ' ||
853 'decode(mrp_bic.implementation_date, null, ' ||
854 'mrp_bic.old_component_sequence_id, ' ||
855 'mrp_bic.component_sequence_id) ' ||
856 'OR bic2.operation_seq_num = mrp_bic.operation_seq_num) ' ||
857 'AND bic2.effectivity_date <= :b_bom_effectivity ' ||
858 'AND bic2.effectivity_date > mrp_bic.effectivity_date ' ||
859 'AND (bic2.implementation_date is not null OR ' ||
860 '(bic2.implementation_date is null AND EXISTS ' ||
861 '(SELECT NULL ' ||
862 'FROM eng_revised_items eri ' ||
863 'WHERE bic2.revised_item_sequence_id = ' ||
864 'eri.revised_item_sequence_id ' ||
865 'AND eri.mrp_active = 1 )))) ' ||
866 'AND (mrp_bic.implementation_date is not null OR ' ||
867 '(mrp_bic.implementation_date is null AND EXISTS ' ||
868 '(SELECT NULL ' ||
869 'FROM eng_revised_items eri ' ||
870 'WHERE mrp_bic.revised_item_sequence_id = ' ||
871 'eri.revised_item_sequence_id ' ||
872 'AND eri.mrp_active = 1 ))) ';
873 end if;
874 l_sql_stmt := l_sql_stmt ||
875 'AND bbom.organization_id = msi.organization_id ' ||
876 'AND bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id ' ||
877 'AND ps.kanban_plan_id (+) = :b_PRODUCTION_KANBAN ' ||
878 'AND ps.organization_id (+) = bbom.organization_id ' ||
879 'AND ps.inventory_item_id (+) = bbom.assembly_item_id ' ||
880 'AND msi2.inventory_item_id = bbom.assembly_item_id ' ||
881 'AND msi2.organization_id = bbom.organization_id ' ||
882 -- Should consider Both Make and Buy Items as suggested by Richard Rodgers.
883 -- 'AND msi2.planning_make_buy_code = 1 ' ||
884 ' AND ps.source_type (+) = :b_PRODUCTION_SOURCE_TYPE ';
885 /* Updated by Liye Ma. 1/23/2001
886 This check_min_priority serves no purposes...
887 ||
888 'AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority ( ' ||
889 'ps.inventory_item_id, ' ||
890 'ps.organization_id, ' ||
891 'ps.wip_line_id, ' ||
892 'bbom.alternate_bom_designator ) '; */
893
894 -- get a cursor handle
895 l_cursor := dbms_sql.open_cursor;
896
897 -- parse the sql statement that we just built
898 dbms_sql.parse (l_cursor, l_sql_stmt, dbms_sql.native);
899
900 -- put values into all the bind variables
901 flm_util.add_bind (':b_kanban_plan_id',
902 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id);
903 flm_util.add_bind (':b_organization_id',
904 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id);
905 flm_util.add_bind (':b_bom_effectivity',
906 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity);
907 flm_util.add_bind (':b_PRODUCTION_KANBAN',
908 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN);
909 flm_util.add_bind (':b_PRODUCTION_SOURCE_TYPE',
910 mrp_kanban_plan_pk.G_PRODUCTION_SOURCE_TYPE);
911 flm_util.add_bind (':b_category_set_id',
912 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id);
913 flm_util.do_binds(l_cursor);
914
915 -- now execute the sql stmt
916 l_rows_processed := dbms_sql.execute(l_cursor);
917
918 -- close the cursor
919 dbms_sql.close_cursor (l_cursor);
920
921 IF mrp_kanban_plan_pk.g_debug THEN
922 mrp_kanban_plan_pk.g_log_message :=
923 '----------------------------------------------';
924 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
925 mrp_kanban_plan_pk.g_log_message :=
926 'Successfully executed the Dynamic Sql Statement';
927 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
928 mrp_kanban_plan_pk.g_log_message := 'Inserted ' ||
929 to_char(l_rows_processed) || ' into mrp_low_level_codes table';
930 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
931 END IF;
932
933 END IF; -- so we basically did all the above only if we are not replanning
934
935 ---------------------------------------------------------------------------
936 -- Now go ahead and get all the other items that are required for planning
937 -- from the bill structure
938 -- ------------------------------------------------------------------------
939
940 IF mrp_kanban_plan_pk.g_debug THEN
941 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
942 || to_char (mrp_kanban_plan_pk.g_stmt_num);
943 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
944 END IF;
945
946 l_level_count := 1;
947
948 WHILE TRUE LOOP
949
950 -- ------------------------------------------------------------------------
951 -- Select parent of the current level items and insert into
952 -- mrp_low_level_codes table if not already present. So basically once
953 -- we got our initial list of items into mrp_low_level_codes, it becomes
954 -- driver for our select statement for insert. The rest of the logic is
955 -- similar to the above built sql statement. Notice how we are using
956 -- the levels_below column to walk our way up the bill
957 -- ------------------------------------------------------------------------
958
959 if l_eco_profile = FALSE then
960
961 INSERT INTO mrp_low_level_codes (
962 plan_id,
963 organization_id,
964 assembly_item_id,
965 to_subinventory,
966 to_locator_id,
967 component_item_id,
968 from_subinventory,
969 from_locator_id,
970 component_usage,
971 component_yield,
972 /* Updated by Liye Ma 4/30/2001
973 Add two more columns, to fix bug 1745046 and 1757798. */
974 planning_factor,
975 item_num,
976 /* End of Update */
977 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
978 basis_type,
979 wip_supply_type,
980 alternate_designator,
981 levels_below,
982 kanban_item_flag,
983 component_category_id,
984 request_id,
985 program_application_id,
986 program_id,
987 program_update_date,
988 last_updated_by,
989 last_update_date,
990 created_by,
991 creation_date)
992 SELECT /*+
993 LEADING(MLLC)
994 USE_NL(MLLC MRP_BIC BBOM MIC PS)
995 */ DISTINCT
996 mllc.plan_id,
997 mllc.organization_id,
998 bbom.assembly_item_id,
999 ps.subinventory_name,
1000 ps.locator_id,
1001 mrp_bic.component_item_id,
1002 mrp_bic.supply_subinventory,
1003 mrp_bic.supply_locator_id,
1004 mrp_bic.component_quantity,
1005 mrp_bic.component_yield_factor,
1006 /* Updated by Liye Ma 4/30/2001
1007 Add two more columns, to fix bug 1745046 and 1757798. */
1008 mrp_bic.planning_factor,
1009 mrp_bic.item_num,
1010 /* End of Update */
1011 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1012 nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
1013 nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
1014 bbom.alternate_bom_designator,
1015 l_level_count + 1,
1016 NULL, -- set it to NULL and update it next stmt
1017 mic.category_id,
1018 fnd_global.conc_request_id,
1019 fnd_global.prog_appl_id,
1020 fnd_global.conc_program_id,
1021 sysdate,
1022 fnd_global.user_id,
1023 sysdate,
1024 fnd_global.user_id,
1025 sysdate
1026 FROM
1027 mtl_kanban_pull_sequences ps,
1028 bom_bill_of_materials bbom,
1029 mtl_item_categories mic,
1030 bom_inventory_components mrp_bic,
1031 mrp_low_level_codes mllc
1032 WHERE mllc.plan_id =
1033 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1034 AND mllc.organization_id =
1035 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1036 AND mllc.levels_below = l_level_count
1037 AND mrp_bic.component_item_id = mllc.assembly_item_id
1038 AND (nvl(mrp_bic.disable_date,
1039 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
1040 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1041 AND mrp_bic.effectivity_date <=
1042 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1043 AND bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
1044 AND bbom.organization_id = mllc.organization_id
1045 AND ps.kanban_plan_id (+) =
1046 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1047 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1048 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1049 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1050 AND ps.organization_id (+) = bbom.organization_id
1051 AND ps.inventory_item_id (+) = bbom.assembly_item_id
1052 AND ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
1053 /* Fix bug 2090054
1054 AND 1 = Check_Min_Priority (
1055 ps.inventory_item_id,
1056 ps.organization_id,
1057 ps.wip_line_id,
1058 bbom.alternate_bom_designator)
1059 */
1060 AND mic.organization_id (+) =
1061 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1062 AND mic.inventory_item_id (+) = mllc.assembly_item_id
1063 AND mic.category_set_id (+) =
1064 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
1065 /* Avoid re-selecting items already in mrp_low_level_codes */
1066 AND NOT EXISTS
1067 ( SELECT 'Exists'
1068 FROM mrp_low_level_codes mllc2
1069 WHERE mllc2.plan_id =
1070 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1071 AND mllc2.organization_id =
1072 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1073 AND mllc2.component_item_id = mrp_bic.component_item_id )
1074 AND EXISTS(
1075 SELECT /*+no_unnest*/ 1
1076 FROM mtl_system_items msi
1077 WHERE msi.organization_id = bbom.organization_id
1078 AND msi.inventory_item_id = bbom.assembly_item_id
1079 /*AND msi.planning_make_buy_code = 1 */);
1080 else
1081 INSERT INTO mrp_low_level_codes (
1082 plan_id,
1083 organization_id,
1084 assembly_item_id,
1085 to_subinventory,
1086 to_locator_id,
1087 component_item_id,
1088 from_subinventory,
1089 from_locator_id,
1090 component_usage,
1091 component_yield,
1092 /* Updated by Liye Ma 4/30/2001
1093 Add two more columns, to fix bug 1745046 and 1757798. */
1094 planning_factor,
1095 item_num,
1096 /* End of Update */
1097 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1098 basis_type,
1099 wip_supply_type,
1100 alternate_designator,
1101 levels_below,
1102 kanban_item_flag,
1103 component_category_id,
1104 request_id,
1105 program_application_id,
1106 program_id,
1107 program_update_date,
1108 last_updated_by,
1109 last_update_date,
1110 created_by,
1111 creation_date)
1112 SELECT /*+ INDEX(PS MTL_KANBAN_PULL_SEQUENCES_N1) */ DISTINCT
1113 mllc.plan_id,
1114 mllc.organization_id,
1115 bbom.assembly_item_id,
1116 ps.subinventory_name,
1117 ps.locator_id,
1118 mrp_bic.component_item_id,
1119 mrp_bic.supply_subinventory,
1120 mrp_bic.supply_locator_id,
1121 mrp_bic.component_quantity,
1122 mrp_bic.component_yield_factor,
1123 /* Updated by Liye Ma 4/30/2001
1124 Add two more columns, to fix bug 1745046 and 1757798. */
1125 mrp_bic.planning_factor,
1126 mrp_bic.item_num,
1127 /* End of Update */
1128 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1129 nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
1130 nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
1131 bbom.alternate_bom_designator,
1132 l_level_count + 1,
1133 NULL, -- set it to NULL and update it next stmt
1134 mic.category_id,
1135 fnd_global.conc_request_id,
1136 fnd_global.prog_appl_id,
1137 fnd_global.conc_program_id,
1138 sysdate,
1139 fnd_global.user_id,
1140 sysdate,
1141 fnd_global.user_id,
1142 sysdate
1143 FROM
1144 mtl_kanban_pull_sequences ps,
1145 bom_bill_of_materials bbom,
1146 mtl_item_categories mic,
1147 bom_inventory_components mrp_bic,
1148 mrp_low_level_codes mllc
1149 WHERE mllc.plan_id =
1150 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1151 AND mllc.organization_id =
1152 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1153 AND mllc.levels_below = l_level_count
1154 AND mrp_bic.component_item_id = mllc.assembly_item_id
1155 AND (nvl(mrp_bic.disable_date,
1156 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
1157 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1158 AND mrp_bic.effectivity_date <=
1159 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1160 AND NOT EXISTS (
1161 SELECT /*+ INDEX(bic2 BOM_INVENTORY_COMPONENTS_N1) */
1162 NULL
1163 FROM bom_inventory_components bic2
1164 WHERE bic2.bill_sequence_id = mrp_bic.bill_sequence_id
1165 AND bic2.component_item_id = mrp_bic.component_item_id
1166 AND (decode(bic2.implementation_date, null,
1167 bic2.old_component_sequence_id,
1168 bic2.component_sequence_id) =
1169 decode(mrp_bic.implementation_date, null,
1170 mrp_bic.old_component_sequence_id,
1171 mrp_bic.component_sequence_id)
1172 OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
1173 AND bic2.effectivity_date <=
1174 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1175 AND bic2.effectivity_date > mrp_bic.effectivity_date
1176 AND (bic2.implementation_date is not null OR
1177 (bic2.implementation_date is null AND EXISTS
1178 (SELECT NULL
1179 FROM eng_revised_items eri
1180 WHERE bic2.revised_item_sequence_id =
1181 eri.revised_item_sequence_id
1182 AND eri.mrp_active = 1 ))))
1183 AND (mrp_bic.implementation_date is not null OR
1184 (mrp_bic.implementation_date is null AND EXISTS
1185 (SELECT NULL
1186 FROM eng_revised_items eri
1187 WHERE mrp_bic.revised_item_sequence_id =
1188 eri.revised_item_sequence_id
1189 AND eri.mrp_active = 1 )))
1190 AND bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
1191 AND bbom.organization_id = mllc.organization_id
1192 AND ps.kanban_plan_id (+) =
1193 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1194 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1195 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1196 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1197 AND ps.organization_id (+) = bbom.organization_id
1198 AND ps.inventory_item_id (+) = bbom.assembly_item_id
1199 AND ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
1200 /* Fix bug 2090054
1201 AND 1 = Check_Min_Priority (
1202 ps.inventory_item_id,
1203 ps.organization_id,
1204 ps.wip_line_id,
1205 bbom.alternate_bom_designator)
1206 */
1207 AND mic.organization_id (+) =
1208 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1209 AND mic.inventory_item_id (+) = mllc.assembly_item_id
1210 AND mic.category_set_id (+) =
1211 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
1212 /* Avoid re-selecting items already in mrp_low_level_codes */
1213 AND NOT EXISTS
1214 ( SELECT 'Exists'
1215 FROM mrp_low_level_codes mllc2
1216 WHERE mllc2.plan_id =
1217 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1218 AND mllc2.organization_id =
1219 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1220 AND mllc2.component_item_id = mrp_bic.component_item_id )
1221 AND EXISTS(
1222 SELECT /*+no_unnest*/ 1
1223 FROM mtl_system_items msi
1224 WHERE msi.organization_id = bbom.organization_id
1225 AND msi.inventory_item_id = bbom.assembly_item_id
1226 /*AND msi.planning_make_buy_code = 1*/);
1227
1228 end if;
1229
1230
1231 EXIT WHEN SQL%ROWCOUNT = 0;
1232
1233 l_level_count := l_level_count + 1;
1234
1235 END LOOP;
1236
1237
1238 -- The purpose of this statment is to improve the performance
1239 -- The above insert stmt has performance problems and
1240 -- to avoid two outer join in mtl_kanban_pull_sequences
1241 -- we decide to break it down.
1242 UPDATE mrp_low_level_codes mllc
1243 SET (mllc.kanban_item_flag) =
1244 (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1245 from mtl_kanban_pull_sequences kbn_items
1246 where kbn_items.kanban_plan_id =
1247 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1248 and kbn_items.organization_id =
1249 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1250 and kbn_items.inventory_item_id = mllc.assembly_item_id)
1251 WHERE mllc.plan_id =
1252 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1253 AND mllc.organization_id =
1254 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1255 AND mllc.kanban_item_flag is null;
1256
1257
1258 mrp_kanban_plan_pk.g_stmt_num := 80;
1259 IF mrp_kanban_plan_pk.g_debug THEN
1260 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1261 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1262 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1263 END IF;
1264
1265 -- now update the mrp_low_level_codes table with operation_yield
1266 -- and net_planning_percent from the bom_operation_sequences table.
1267 -- We did this separately after inserting all that we wanted to
1268 -- insert only to make the code a little cleaner. We tried doing
1269 -- this in the above sql statement itself but obviously if got
1270 -- kinda ugly trying to achieve it.
1271
1272 -- bom_inventory_components and bom_operation_sequences are linked
1273 -- by the operation sequence number and for a particular operation
1274 -- sequence we have the net_planning_percent and the operation_yield
1275 -- (actually the reverse_cumulative_yield column) stored in
1276 -- bom_operation_sequences table
1277
1278 UPDATE mrp_low_level_codes mllc
1279 SET (mllc.operation_yield,mllc.net_planning_percent) =
1280 (SELECT min(bos.reverse_cumulative_yield),
1281 min(bos.net_planning_percent)
1282 FROM bom_operation_sequences bos,
1283 bom_operational_routings bor,
1284 bom_inventory_components mrp_bic,
1285 bom_bill_of_materials bbom
1286 WHERE bbom.assembly_item_id = mllc.assembly_item_id
1287 AND bbom.organization_id = mllc.organization_id
1288 AND nvl(bbom.alternate_bom_designator, 'xxx') =
1289 nvl(mllc.alternate_designator, 'xxx')
1290 AND mrp_bic.bill_sequence_id = bbom.common_bill_sequence_id
1291 AND mrp_bic.component_item_id = mllc.component_item_id
1292 AND (nvl(mrp_bic.disable_date,
1293 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) +1) >=
1294 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1295 AND mrp_bic.effectivity_date <=
1296 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1297 AND NOT EXISTS (
1298 SELECT NULL
1299 FROM bom_inventory_components bic2
1300 WHERE bic2.bill_sequence_id = mrp_bic.bill_sequence_id
1301 AND bic2.component_item_id = mrp_bic.component_item_id
1302 AND (decode(bic2.implementation_date, null,
1303 bic2.old_component_sequence_id,
1304 bic2.component_sequence_id) =
1305 decode(mrp_bic.implementation_date, null,
1306 mrp_bic.old_component_sequence_id,
1307 mrp_bic.component_sequence_id)
1308 OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
1309 AND bic2.effectivity_date <=
1310 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1311 AND bic2.effectivity_date > mrp_bic.effectivity_date
1312 AND (bic2.implementation_date is not null OR
1313 (bic2.implementation_date is null AND EXISTS
1314 (SELECT NULL
1315 FROM eng_revised_items eri
1316 WHERE bic2.revised_item_sequence_id =
1317 eri.revised_item_sequence_id
1318 AND eri.mrp_active = 1 ))))
1319 AND (mrp_bic.implementation_date is not null OR
1320 (mrp_bic.implementation_date is null AND EXISTS
1321 (SELECT NULL
1322 FROM eng_revised_items eri
1323 WHERE mrp_bic.revised_item_sequence_id =
1324 eri.revised_item_sequence_id
1325 AND eri.mrp_active = 1 )))
1326 AND bor.organization_id = bbom.organization_id
1327 AND bor.assembly_item_id = bbom.assembly_item_id
1328 AND nvl(bor.alternate_routing_designator, 'xxx') =
1329 nvl(bbom.alternate_bom_designator, 'xxx')
1330 AND bos.routing_sequence_id = bor.routing_sequence_id
1331 AND bos.operation_seq_num = mrp_bic.operation_seq_num
1332 AND nvl(bos.operation_type, 1) = 1
1333 AND nvl(bos.disable_date,
1334 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) + 1
1335 >= mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1336 AND bos.effectivity_date <=
1337 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)
1338 WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id;
1339
1340 mrp_kanban_plan_pk.g_stmt_num := 90;
1341
1342
1343 IF mrp_kanban_plan_pk.g_debug THEN
1344 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1345 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1346 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1347 mrp_kanban_plan_pk.g_log_message := 'Updated mrp_low_level_codes ' ||
1348 'with net planning percent and yield information';
1349 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1350 END IF;
1351
1352
1353 -- ------------------------------------------------------------------------
1354 -- Now insert the top level assembly item
1355 -- Since the top level assembly item does not have a parent, it would not
1356 -- gotten into mrp_low_level_codes table as a component item (which is
1357 -- what we use to plan). So, we create a dummy parent of -1 for him and
1358 -- insert him into the mrp_low_level_codes table
1359 -- ------------------------------------------------------------------------
1360
1361 INSERT INTO mrp_low_level_codes (
1362 plan_id,
1363 organization_id,
1364 assembly_item_id,
1365 component_item_id,
1366 from_subinventory,
1367 from_locator_id,
1368 levels_below,
1369 kanban_item_flag,
1370 component_category_id,
1371 request_id,
1372 program_application_id,
1373 program_id,
1374 program_update_date,
1375 last_updated_by,
1376 last_update_date,
1377 created_by,
1378 creation_date )
1379 SELECT DISTINCT
1380 mllc1.plan_id,
1381 mllc1.organization_id,
1382 -1,
1383 mllc1.assembly_item_id,
1384 mllc1.to_subinventory,
1385 mllc1.to_locator_id,
1386 l_level_count + 1,
1387 decode(kbn_items.release_kanban_flag, 1, 'Y', 2, 'Y', 'N'),
1388 mic.category_id,
1389 fnd_global.conc_request_id,
1390 fnd_global.prog_appl_id,
1391 fnd_global.conc_program_id,
1392 sysdate,
1393 fnd_global.user_id,
1394 sysdate,
1395 fnd_global.user_id,
1396 sysdate
1397 FROM
1398 mtl_item_categories mic,
1399 mtl_kanban_pull_sequences kbn_items,
1400 mrp_low_level_codes mllc1
1401 WHERE
1402 mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id AND
1403 mllc1.organization_id =
1404 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id AND
1405 kbn_items.inventory_item_id (+) =
1406 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN AND
1407 kbn_items.inventory_item_id (+) = mllc1.assembly_item_id AND
1408 kbn_items.organization_id (+) = mllc1.organization_id AND
1409 mic.inventory_item_id (+) = mllc1.assembly_item_id AND
1410 mic.organization_id (+) = mllc1.organization_id AND
1411 mic.category_set_id (+) =
1412 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id AND
1413 --select only the assembly items that do not exist as components
1414 NOT EXISTS
1415 (SELECT 'Exists'
1416 FROM mrp_low_level_codes mllc2
1417 WHERE mllc2.plan_id = mllc1.plan_id AND
1418 mllc2.organization_id = mllc1.organization_id AND
1419 mllc2.component_item_id = mllc1.assembly_item_id );
1420
1421
1422
1423 mrp_kanban_plan_pk.g_stmt_num := 100;
1424 IF mrp_kanban_plan_pk.g_debug THEN
1425 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1426 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1427 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1428 END IF;
1429
1430 -- ------------------------------------------------------------------------
1431 -- Now find information in mtl_kanban_pull_sequences about inter-org and
1432 -- intra-org transfers and insert into mrp_low_level_codes
1433 -- Note here that replan flag drives whether I pull infomation from the
1434 -- production kanban plan or the current kanban plan itself. Replan_flag
1435 -- = 2 is not a replan and if its 1 then its a replan run.
1436 -- We are not including supplier kind of replenishment here because we
1437 -- can afford to not calculate the low_level_code for a supplier source type
1438 -- (since we know that's the end point in the chain, we can stop one point
1439 -- before that).
1440 -- ------------------------------------------------------------------------
1441
1442 INSERT INTO mrp_low_level_codes (
1443 plan_id,
1444 organization_id,
1445 assembly_item_id,
1446 to_subinventory,
1447 to_locator_id,
1448 component_item_id,
1449 from_subinventory,
1450 from_locator_id,
1451 component_usage,
1452 component_yield,
1453 supply_source_type,
1454 replenishment_lead_time,
1455 kanban_item_flag,
1456 component_category_id,
1457 request_id,
1458 program_application_id,
1459 program_id,
1460 program_update_date,
1461 last_updated_by,
1462 last_update_date,
1463 created_by,
1464 creation_date )
1465 SELECT DISTINCT
1466 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1467 ps.organization_id,
1468 ps.inventory_item_id,
1469 ps.subinventory_name,
1470 ps.locator_id,
1471 ps.inventory_item_id,
1472 ps.source_subinventory,
1473 ps.source_locator_id,
1474 1,
1475 1,
1476 ps.source_type,
1477 ps.replenishment_lead_time,
1478 'Y',
1479 mllc.component_category_id,
1480 fnd_global.conc_request_id,
1481 fnd_global.prog_appl_id,
1482 fnd_global.conc_program_id,
1483 sysdate,
1484 fnd_global.user_id,
1485 sysdate,
1486 fnd_global.user_id,
1487 sysdate
1488 FROM mtl_kanban_pull_sequences ps,
1489 mrp_low_level_codes mllc
1490 WHERE ps.source_type = 3 -- only intra org replenishments
1491 AND ps.kanban_plan_id =
1492 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1493 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1494 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1495 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1496 AND ps.organization_id = mllc.organization_id
1497 AND ps.inventory_item_id = mllc.component_item_id
1498 AND mllc.organization_id =
1499 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1500 AND mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1501 AND mllc.kanban_item_flag = 'Y';
1502
1503
1504 IF mrp_kanban_plan_pk.g_debug THEN
1505 mrp_kanban_plan_pk.g_log_message :=
1506 'Completed inserting into mrp_low_level_codes table';
1507 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1508 END IF;
1509 mrp_kanban_plan_pk.g_stmt_num := 110;
1510 IF mrp_kanban_plan_pk.g_debug THEN
1511 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1512 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1513 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1514 END IF;
1515
1516 -- call the check_item_locations procedure to ensure that
1517 -- kanban items have the from-locations populated in the
1518 -- mrp_low_level_codes table. If the kanban items do not have
1519 -- the from locations populated, we can run into issues while
1520 -- calculating low_level_codes
1521
1522 IF NOT Check_Item_Locations THEN
1523 RETURN FALSE;
1524 END IF;
1525
1526 mrp_kanban_plan_pk.g_stmt_num := 130;
1527 IF mrp_kanban_plan_pk.g_debug THEN
1528 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1529 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1530 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1531 END IF;
1532
1533 -- now we are ready for our low level code calculation
1534 -- so call that procedure
1535
1536 IF mrp_kanban_plan_pk.g_debug THEN
1537 mrp_kanban_plan_pk.g_log_message := 'Calling CALC_LOW_LEVEL_code function';
1538 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1539 END IF;
1540
1541 IF NOT Calc_Low_Level_code THEN
1542 RETURN FALSE;
1543 END IF;
1544
1545 mrp_kanban_plan_pk.g_stmt_num := 140;
1546 IF mrp_kanban_plan_pk.g_debug THEN
1547 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : ' ||
1548 to_char (mrp_kanban_plan_pk.g_stmt_num);
1549 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1550 mrp_kanban_plan_pk.g_log_message := 'Calling CHECK_FOR_LOOPS function';
1551 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1552 END IF;
1553
1554
1555 -- after low level code calculation, if we have component items
1556 -- in mrp_low_level_code table with no low level code assigned
1557 -- then we have loop in the bill. Call the procedure to check this
1558
1559 IF NOT Check_For_Loops THEN
1560 RETURN FALSE;
1561 END IF;
1562
1563 RETURN TRUE;
1564
1565 EXCEPTION
1566
1567 WHEN OTHERS THEN
1568 mrp_kanban_plan_pk.g_log_message := 'SNAPSHOT_ITEM_LOCATIONS Sql Error ';
1569 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1570 mrp_kanban_plan_pk.g_log_message := sqlerrm;
1571 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1572 RETURN FALSE;
1573
1574 END SNAPSHOT_ITEM_LOCATIONS;
1575
1576
1577 END MRP_KANBAN_SNAPSHOT_PK;
1578
[Home] [Help]