1 PACKAGE BODY MRP_KANBAN_SNAPSHOT_PK AS
2 /* $Header: MRPKSNPB.pls 120.4 2005/12/29 19:11:57 ksuleman noship $ */
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 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 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 'AND msi2.planning_make_buy_code = 1 ' ||
883 'AND ps.source_type (+) = :b_PRODUCTION_SOURCE_TYPE ';
884 /* Updated by Liye Ma. 1/23/2001
885 This check_min_priority serves no purposes...
886 ||
887 'AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority ( ' ||
888 'ps.inventory_item_id, ' ||
889 'ps.organization_id, ' ||
890 'ps.wip_line_id, ' ||
891 'bbom.alternate_bom_designator ) '; */
892
893 -- get a cursor handle
894 l_cursor := dbms_sql.open_cursor;
895
896 -- parse the sql statement that we just built
897 dbms_sql.parse (l_cursor, l_sql_stmt, dbms_sql.native);
898
899 -- put values into all the bind variables
900 flm_util.add_bind (':b_kanban_plan_id',
901 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id);
902 flm_util.add_bind (':b_organization_id',
903 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id);
904 flm_util.add_bind (':b_bom_effectivity',
905 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity);
906 flm_util.add_bind (':b_PRODUCTION_KANBAN',
907 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN);
908 flm_util.add_bind (':b_PRODUCTION_SOURCE_TYPE',
909 mrp_kanban_plan_pk.G_PRODUCTION_SOURCE_TYPE);
910 flm_util.add_bind (':b_category_set_id',
911 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id);
912 flm_util.do_binds(l_cursor);
913
914 -- now execute the sql stmt
915 l_rows_processed := dbms_sql.execute(l_cursor);
916
917 -- close the cursor
918 dbms_sql.close_cursor (l_cursor);
919
920 IF mrp_kanban_plan_pk.g_debug THEN
921 mrp_kanban_plan_pk.g_log_message :=
922 '----------------------------------------------';
923 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
924 mrp_kanban_plan_pk.g_log_message :=
925 'Successfully executed the Dynamic Sql Statement';
926 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
927 mrp_kanban_plan_pk.g_log_message := 'Inserted ' ||
928 to_char(l_rows_processed) || ' into mrp_low_level_codes table';
929 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
930 END IF;
931
932 END IF; -- so we basically did all the above only if we are not replanning
933
934 ---------------------------------------------------------------------------
935 -- Now go ahead and get all the other items that are required for planning
936 -- from the bill structure
937 -- ------------------------------------------------------------------------
938
939 IF mrp_kanban_plan_pk.g_debug THEN
940 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
941 || to_char (mrp_kanban_plan_pk.g_stmt_num);
942 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
943 END IF;
944
945 l_level_count := 1;
946
947 WHILE TRUE LOOP
948
949 -- ------------------------------------------------------------------------
950 -- Select parent of the current level items and insert into
951 -- mrp_low_level_codes table if not already present. So basically once
952 -- we got our initial list of items into mrp_low_level_codes, it becomes
953 -- driver for our select statement for insert. The rest of the logic is
954 -- similar to the above built sql statement. Notice how we are using
955 -- the levels_below column to walk our way up the bill
956 -- ------------------------------------------------------------------------
957
958 if l_eco_profile = FALSE then
959
960 INSERT INTO mrp_low_level_codes (
961 plan_id,
962 organization_id,
963 assembly_item_id,
964 to_subinventory,
965 to_locator_id,
966 component_item_id,
967 from_subinventory,
968 from_locator_id,
969 component_usage,
970 component_yield,
971 /* Updated by Liye Ma 4/30/2001
972 Add two more columns, to fix bug 1745046 and 1757798. */
973 planning_factor,
974 item_num,
975 /* End of Update */
976 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
977 basis_type,
978 wip_supply_type,
979 alternate_designator,
980 levels_below,
981 kanban_item_flag,
982 component_category_id,
983 request_id,
984 program_application_id,
985 program_id,
986 program_update_date,
987 last_updated_by,
988 last_update_date,
989 created_by,
990 creation_date)
991 SELECT /*+
992 LEADING(MLLC)
993 USE_NL(MLLC MRP_BIC BBOM MIC PS)
994 */ DISTINCT
995 mllc.plan_id,
996 mllc.organization_id,
997 bbom.assembly_item_id,
998 ps.subinventory_name,
999 ps.locator_id,
1000 mrp_bic.component_item_id,
1001 mrp_bic.supply_subinventory,
1002 mrp_bic.supply_locator_id,
1003 mrp_bic.component_quantity,
1004 mrp_bic.component_yield_factor,
1005 /* Updated by Liye Ma 4/30/2001
1006 Add two more columns, to fix bug 1745046 and 1757798. */
1007 mrp_bic.planning_factor,
1008 mrp_bic.item_num,
1009 /* End of Update */
1010 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1011 nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
1012 nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
1013 bbom.alternate_bom_designator,
1014 l_level_count + 1,
1015 NULL, -- set it to NULL and update it next stmt
1016 mic.category_id,
1017 fnd_global.conc_request_id,
1018 fnd_global.prog_appl_id,
1019 fnd_global.conc_program_id,
1020 sysdate,
1021 fnd_global.user_id,
1022 sysdate,
1023 fnd_global.user_id,
1024 sysdate
1025 FROM
1026 mtl_kanban_pull_sequences ps,
1027 bom_bill_of_materials bbom,
1028 mtl_item_categories mic,
1029 bom_inventory_components mrp_bic,
1030 mrp_low_level_codes mllc
1031 WHERE mllc.plan_id =
1032 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1033 AND mllc.organization_id =
1034 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1035 AND mllc.levels_below = l_level_count
1036 AND mrp_bic.component_item_id = mllc.assembly_item_id
1037 AND (nvl(mrp_bic.disable_date,
1038 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
1039 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1040 AND mrp_bic.effectivity_date <=
1041 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1042 AND bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
1043 AND bbom.organization_id = mllc.organization_id
1044 AND ps.kanban_plan_id (+) =
1045 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1046 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1047 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1048 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1049 AND ps.organization_id (+) = bbom.organization_id
1050 AND ps.inventory_item_id (+) = bbom.assembly_item_id
1051 AND ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
1052 /* Fix bug 2090054
1053 AND 1 = Check_Min_Priority (
1054 ps.inventory_item_id,
1055 ps.organization_id,
1056 ps.wip_line_id,
1057 bbom.alternate_bom_designator)
1058 */
1059 AND mic.organization_id (+) =
1060 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1061 AND mic.inventory_item_id (+) = mllc.assembly_item_id
1062 AND mic.category_set_id (+) =
1063 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
1064 /* Avoid re-selecting items already in mrp_low_level_codes */
1065 AND NOT EXISTS
1066 ( SELECT 'Exists'
1067 FROM mrp_low_level_codes mllc2
1068 WHERE mllc2.plan_id =
1069 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1070 AND mllc2.organization_id =
1071 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1072 AND mllc2.component_item_id = mrp_bic.component_item_id )
1073 AND EXISTS(
1074 SELECT /*+no_unnest*/ 1
1075 FROM mtl_system_items msi
1076 WHERE msi.organization_id = bbom.organization_id
1077 AND msi.inventory_item_id = bbom.assembly_item_id
1078 AND msi.planning_make_buy_code = 1);
1079 else
1080 INSERT INTO mrp_low_level_codes (
1081 plan_id,
1082 organization_id,
1083 assembly_item_id,
1084 to_subinventory,
1085 to_locator_id,
1086 component_item_id,
1087 from_subinventory,
1088 from_locator_id,
1089 component_usage,
1090 component_yield,
1091 /* Updated by Liye Ma 4/30/2001
1092 Add two more columns, to fix bug 1745046 and 1757798. */
1093 planning_factor,
1094 item_num,
1095 /* End of Update */
1096 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1097 basis_type,
1098 wip_supply_type,
1099 alternate_designator,
1100 levels_below,
1101 kanban_item_flag,
1102 component_category_id,
1103 request_id,
1104 program_application_id,
1105 program_id,
1106 program_update_date,
1107 last_updated_by,
1108 last_update_date,
1109 created_by,
1110 creation_date)
1111 SELECT /*+ INDEX(PS MTL_KANBAN_PULL_SEQUENCES_N1) */ DISTINCT
1112 mllc.plan_id,
1113 mllc.organization_id,
1114 bbom.assembly_item_id,
1115 ps.subinventory_name,
1116 ps.locator_id,
1117 mrp_bic.component_item_id,
1118 mrp_bic.supply_subinventory,
1119 mrp_bic.supply_locator_id,
1120 mrp_bic.component_quantity,
1121 mrp_bic.component_yield_factor,
1122 /* Updated by Liye Ma 4/30/2001
1123 Add two more columns, to fix bug 1745046 and 1757798. */
1124 mrp_bic.planning_factor,
1125 mrp_bic.item_num,
1126 /* End of Update */
1127 /* Modified for lot based material support. Adding query of basis_type and wip_supply_type */
1128 nvl(mrp_bic.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL),
1129 nvl(mrp_bic.wip_supply_type,WIP_CONSTANTS.PUSH),
1130 bbom.alternate_bom_designator,
1131 l_level_count + 1,
1132 NULL, -- set it to NULL and update it next stmt
1133 mic.category_id,
1134 fnd_global.conc_request_id,
1135 fnd_global.prog_appl_id,
1136 fnd_global.conc_program_id,
1137 sysdate,
1138 fnd_global.user_id,
1139 sysdate,
1140 fnd_global.user_id,
1141 sysdate
1142 FROM
1143 mtl_kanban_pull_sequences ps,
1144 bom_bill_of_materials bbom,
1145 mtl_item_categories mic,
1146 bom_inventory_components mrp_bic,
1147 mrp_low_level_codes mllc
1148 WHERE mllc.plan_id =
1149 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1150 AND mllc.organization_id =
1151 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1152 AND mllc.levels_below = l_level_count
1153 AND mrp_bic.component_item_id = mllc.assembly_item_id
1154 AND (nvl(mrp_bic.disable_date,
1155 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)) +1 >=
1156 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1157 AND mrp_bic.effectivity_date <=
1158 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1159 AND NOT EXISTS (
1160 SELECT /*+ INDEX(bic2 BOM_INVENTORY_COMPONENTS_N1) */
1161 NULL
1162 FROM bom_inventory_components bic2
1163 WHERE bic2.bill_sequence_id = mrp_bic.bill_sequence_id
1164 AND bic2.component_item_id = mrp_bic.component_item_id
1165 AND (decode(bic2.implementation_date, null,
1166 bic2.old_component_sequence_id,
1167 bic2.component_sequence_id) =
1168 decode(mrp_bic.implementation_date, null,
1169 mrp_bic.old_component_sequence_id,
1170 mrp_bic.component_sequence_id)
1171 OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
1172 AND bic2.effectivity_date <=
1173 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1174 AND bic2.effectivity_date > mrp_bic.effectivity_date
1175 AND (bic2.implementation_date is not null OR
1176 (bic2.implementation_date is null AND EXISTS
1177 (SELECT NULL
1178 FROM eng_revised_items eri
1179 WHERE bic2.revised_item_sequence_id =
1180 eri.revised_item_sequence_id
1181 AND eri.mrp_active = 1 ))))
1182 AND (mrp_bic.implementation_date is not null OR
1183 (mrp_bic.implementation_date is null AND EXISTS
1184 (SELECT NULL
1185 FROM eng_revised_items eri
1186 WHERE mrp_bic.revised_item_sequence_id =
1187 eri.revised_item_sequence_id
1188 AND eri.mrp_active = 1 )))
1189 AND bbom.common_bill_sequence_id = mrp_bic.bill_sequence_id
1190 AND bbom.organization_id = mllc.organization_id
1191 AND ps.kanban_plan_id (+) =
1192 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1193 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1194 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1195 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1196 AND ps.organization_id (+) = bbom.organization_id
1197 AND ps.inventory_item_id (+) = bbom.assembly_item_id
1198 AND ps.source_type (+) = 4 /* KANBAN_PRODUCTION */
1199 /* Fix bug 2090054
1200 AND 1 = Check_Min_Priority (
1201 ps.inventory_item_id,
1202 ps.organization_id,
1203 ps.wip_line_id,
1204 bbom.alternate_bom_designator)
1205 */
1206 AND mic.organization_id (+) =
1207 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1208 AND mic.inventory_item_id (+) = mllc.assembly_item_id
1209 AND mic.category_set_id (+) =
1210 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id
1211 /* Avoid re-selecting items already in mrp_low_level_codes */
1212 AND NOT EXISTS
1213 ( SELECT 'Exists'
1214 FROM mrp_low_level_codes mllc2
1215 WHERE mllc2.plan_id =
1216 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1217 AND mllc2.organization_id =
1218 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1219 AND mllc2.component_item_id = mrp_bic.component_item_id )
1220 AND EXISTS(
1221 SELECT /*+no_unnest*/ 1
1222 FROM mtl_system_items msi
1223 WHERE msi.organization_id = bbom.organization_id
1224 AND msi.inventory_item_id = bbom.assembly_item_id
1225 AND msi.planning_make_buy_code = 1);
1226
1227 end if;
1228
1229
1230 EXIT WHEN SQL%ROWCOUNT = 0;
1231
1232 l_level_count := l_level_count + 1;
1233
1234 END LOOP;
1235
1236
1237 -- The purpose of this statment is to improve the performance
1238 -- The above insert stmt has performance problems and
1239 -- to avoid two outer join in mtl_kanban_pull_sequences
1240 -- we decide to break it down.
1241 UPDATE mrp_low_level_codes mllc
1242 SET (mllc.kanban_item_flag) =
1243 (select nvl(max(decode(kbn_items.release_kanban_flag, 1, 'Y', 'Y')), 'N')
1244 from mtl_kanban_pull_sequences kbn_items
1245 where kbn_items.kanban_plan_id =
1246 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1247 and kbn_items.organization_id =
1248 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1249 and kbn_items.inventory_item_id = mllc.assembly_item_id)
1250 WHERE mllc.plan_id =
1251 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1252 AND mllc.organization_id =
1253 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1254 AND mllc.kanban_item_flag is null;
1255
1256
1257 mrp_kanban_plan_pk.g_stmt_num := 80;
1258 IF mrp_kanban_plan_pk.g_debug THEN
1259 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1260 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1261 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1262 END IF;
1263
1264 -- now update the mrp_low_level_codes table with operation_yield
1265 -- and net_planning_percent from the bom_operation_sequences table.
1266 -- We did this separately after inserting all that we wanted to
1267 -- insert only to make the code a little cleaner. We tried doing
1268 -- this in the above sql statement itself but obviously if got
1269 -- kinda ugly trying to achieve it.
1270
1271 -- bom_inventory_components and bom_operation_sequences are linked
1272 -- by the operation sequence number and for a particular operation
1273 -- sequence we have the net_planning_percent and the operation_yield
1274 -- (actually the reverse_cumulative_yield column) stored in
1275 -- bom_operation_sequences table
1276
1277 UPDATE mrp_low_level_codes mllc
1278 SET (mllc.operation_yield,mllc.net_planning_percent) =
1279 (SELECT min(bos.reverse_cumulative_yield),
1280 min(bos.net_planning_percent)
1281 FROM bom_operation_sequences bos,
1282 bom_operational_routings bor,
1283 bom_inventory_components mrp_bic,
1284 bom_bill_of_materials bbom
1285 WHERE bbom.assembly_item_id = mllc.assembly_item_id
1286 AND bbom.organization_id = mllc.organization_id
1287 AND nvl(bbom.alternate_bom_designator, 'xxx') =
1288 nvl(mllc.alternate_designator, 'xxx')
1289 AND mrp_bic.bill_sequence_id = bbom.common_bill_sequence_id
1290 AND mrp_bic.component_item_id = mllc.component_item_id
1291 AND (nvl(mrp_bic.disable_date,
1292 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) +1) >=
1293 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1294 AND mrp_bic.effectivity_date <=
1295 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1296 AND NOT EXISTS (
1297 SELECT NULL
1298 FROM bom_inventory_components bic2
1299 WHERE bic2.bill_sequence_id = mrp_bic.bill_sequence_id
1300 AND bic2.component_item_id = mrp_bic.component_item_id
1301 AND (decode(bic2.implementation_date, null,
1302 bic2.old_component_sequence_id,
1303 bic2.component_sequence_id) =
1304 decode(mrp_bic.implementation_date, null,
1305 mrp_bic.old_component_sequence_id,
1306 mrp_bic.component_sequence_id)
1307 OR bic2.operation_seq_num = mrp_bic.operation_seq_num)
1308 AND bic2.effectivity_date <=
1309 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1310 AND bic2.effectivity_date > mrp_bic.effectivity_date
1311 AND (bic2.implementation_date is not null OR
1312 (bic2.implementation_date is null AND EXISTS
1313 (SELECT NULL
1314 FROM eng_revised_items eri
1315 WHERE bic2.revised_item_sequence_id =
1316 eri.revised_item_sequence_id
1317 AND eri.mrp_active = 1 ))))
1318 AND (mrp_bic.implementation_date is not null OR
1319 (mrp_bic.implementation_date is null AND EXISTS
1320 (SELECT NULL
1321 FROM eng_revised_items eri
1322 WHERE mrp_bic.revised_item_sequence_id =
1323 eri.revised_item_sequence_id
1324 AND eri.mrp_active = 1 )))
1325 AND bor.organization_id = bbom.organization_id
1326 AND bor.assembly_item_id = bbom.assembly_item_id
1327 AND nvl(bor.alternate_routing_designator, 'xxx') =
1328 nvl(bbom.alternate_bom_designator, 'xxx')
1329 AND bos.routing_sequence_id = bor.routing_sequence_id
1330 AND bos.operation_seq_num = mrp_bic.operation_seq_num
1331 AND nvl(bos.operation_type, 1) = 1
1332 AND nvl(bos.disable_date,
1333 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity) + 1
1334 >= mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity
1335 AND bos.effectivity_date <=
1336 mrp_kanban_plan_pk.g_kanban_info_rec.bom_effectivity)
1337 WHERE mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id;
1338
1339 mrp_kanban_plan_pk.g_stmt_num := 90;
1340
1341
1342 IF mrp_kanban_plan_pk.g_debug THEN
1343 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1344 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1345 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1346 mrp_kanban_plan_pk.g_log_message := 'Updated mrp_low_level_codes ' ||
1347 'with net planning percent and yield information';
1348 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1349 END IF;
1350
1351
1352 -- ------------------------------------------------------------------------
1353 -- Now insert the top level assembly item
1354 -- Since the top level assembly item does not have a parent, it would not
1355 -- gotten into mrp_low_level_codes table as a component item (which is
1356 -- what we use to plan). So, we create a dummy parent of -1 for him and
1357 -- insert him into the mrp_low_level_codes table
1358 -- ------------------------------------------------------------------------
1359
1360 INSERT INTO mrp_low_level_codes (
1361 plan_id,
1362 organization_id,
1363 assembly_item_id,
1364 component_item_id,
1365 from_subinventory,
1366 from_locator_id,
1367 levels_below,
1368 kanban_item_flag,
1369 component_category_id,
1370 request_id,
1371 program_application_id,
1372 program_id,
1373 program_update_date,
1374 last_updated_by,
1375 last_update_date,
1376 created_by,
1377 creation_date )
1378 SELECT DISTINCT
1379 mllc1.plan_id,
1380 mllc1.organization_id,
1381 -1,
1382 mllc1.assembly_item_id,
1383 mllc1.to_subinventory,
1384 mllc1.to_locator_id,
1385 l_level_count + 1,
1386 decode(kbn_items.release_kanban_flag, 1, 'Y', 2, 'Y', 'N'),
1387 mic.category_id,
1388 fnd_global.conc_request_id,
1389 fnd_global.prog_appl_id,
1390 fnd_global.conc_program_id,
1391 sysdate,
1392 fnd_global.user_id,
1393 sysdate,
1394 fnd_global.user_id,
1395 sysdate
1396 FROM
1397 mtl_item_categories mic,
1398 mtl_kanban_pull_sequences kbn_items,
1399 mrp_low_level_codes mllc1
1400 WHERE
1401 mllc1.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id AND
1402 mllc1.organization_id =
1403 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id AND
1404 kbn_items.inventory_item_id (+) =
1405 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN AND
1406 kbn_items.inventory_item_id (+) = mllc1.assembly_item_id AND
1407 kbn_items.organization_id (+) = mllc1.organization_id AND
1408 mic.inventory_item_id (+) = mllc1.assembly_item_id AND
1409 mic.organization_id (+) = mllc1.organization_id AND
1410 mic.category_set_id (+) =
1411 mrp_kanban_plan_pk.g_kanban_info_rec.category_set_id AND
1412 --select only the assembly items that do not exist as components
1413 NOT EXISTS
1414 (SELECT 'Exists'
1415 FROM mrp_low_level_codes mllc2
1416 WHERE mllc2.plan_id = mllc1.plan_id AND
1417 mllc2.organization_id = mllc1.organization_id AND
1418 mllc2.component_item_id = mllc1.assembly_item_id );
1419
1420
1421
1422 mrp_kanban_plan_pk.g_stmt_num := 100;
1423 IF mrp_kanban_plan_pk.g_debug THEN
1424 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1425 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1426 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1427 END IF;
1428
1429 -- ------------------------------------------------------------------------
1430 -- Now find information in mtl_kanban_pull_sequences about inter-org and
1431 -- intra-org transfers and insert into mrp_low_level_codes
1432 -- Note here that replan flag drives whether I pull infomation from the
1433 -- production kanban plan or the current kanban plan itself. Replan_flag
1434 -- = 2 is not a replan and if its 1 then its a replan run.
1435 -- We are not including supplier kind of replenishment here because we
1436 -- can afford to not calculate the low_level_code for a supplier source type
1437 -- (since we know that's the end point in the chain, we can stop one point
1438 -- before that).
1439 -- ------------------------------------------------------------------------
1440
1441 INSERT INTO mrp_low_level_codes (
1442 plan_id,
1443 organization_id,
1444 assembly_item_id,
1445 to_subinventory,
1446 to_locator_id,
1447 component_item_id,
1448 from_subinventory,
1449 from_locator_id,
1450 component_usage,
1451 component_yield,
1452 supply_source_type,
1453 replenishment_lead_time,
1454 kanban_item_flag,
1455 component_category_id,
1456 request_id,
1457 program_application_id,
1458 program_id,
1459 program_update_date,
1460 last_updated_by,
1461 last_update_date,
1462 created_by,
1463 creation_date )
1464 SELECT DISTINCT
1465 mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1466 ps.organization_id,
1467 ps.inventory_item_id,
1468 ps.subinventory_name,
1469 ps.locator_id,
1470 ps.inventory_item_id,
1471 ps.source_subinventory,
1472 ps.source_locator_id,
1473 1,
1474 1,
1475 ps.source_type,
1476 ps.replenishment_lead_time,
1477 'Y',
1478 mllc.component_category_id,
1479 fnd_global.conc_request_id,
1480 fnd_global.prog_appl_id,
1481 fnd_global.conc_program_id,
1482 sysdate,
1483 fnd_global.user_id,
1484 sysdate,
1485 fnd_global.user_id,
1486 sysdate
1487 FROM mtl_kanban_pull_sequences ps,
1488 mrp_low_level_codes mllc
1489 WHERE ps.source_type = 3 -- only intra org replenishments
1490 AND ps.kanban_plan_id =
1491 decode(mrp_kanban_plan_pk.g_kanban_info_rec.replan_flag,
1492 2, mrp_kanban_plan_pk.G_PRODUCTION_KANBAN,
1493 1, mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id,
1494 mrp_kanban_plan_pk.G_PRODUCTION_KANBAN)
1495 AND ps.organization_id = mllc.organization_id
1496 AND ps.inventory_item_id = mllc.component_item_id
1497 AND mllc.organization_id =
1498 mrp_kanban_plan_pk.g_kanban_info_rec.organization_id
1499 AND mllc.plan_id = mrp_kanban_plan_pk.g_kanban_info_rec.kanban_plan_id
1500 AND mllc.kanban_item_flag = 'Y';
1501
1502
1503 IF mrp_kanban_plan_pk.g_debug THEN
1504 mrp_kanban_plan_pk.g_log_message :=
1505 'Completed inserting into mrp_low_level_codes table';
1506 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1507 END IF;
1508 mrp_kanban_plan_pk.g_stmt_num := 110;
1509 IF mrp_kanban_plan_pk.g_debug THEN
1510 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1511 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1512 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1513 END IF;
1514
1515 -- call the check_item_locations procedure to ensure that
1516 -- kanban items have the from-locations populated in the
1517 -- mrp_low_level_codes table. If the kanban items do not have
1518 -- the from locations populated, we can run into issues while
1519 -- calculating low_level_codes
1520
1521 IF NOT Check_Item_Locations THEN
1522 RETURN FALSE;
1523 END IF;
1524
1525 mrp_kanban_plan_pk.g_stmt_num := 130;
1526 IF mrp_kanban_plan_pk.g_debug THEN
1527 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : '
1528 || to_char (mrp_kanban_plan_pk.g_stmt_num);
1529 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1530 END IF;
1531
1532 -- now we are ready for our low level code calculation
1533 -- so call that procedure
1534
1535 IF mrp_kanban_plan_pk.g_debug THEN
1536 mrp_kanban_plan_pk.g_log_message := 'Calling CALC_LOW_LEVEL_code function';
1537 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1538 END IF;
1539
1540 IF NOT Calc_Low_Level_code THEN
1541 RETURN FALSE;
1542 END IF;
1543
1544 mrp_kanban_plan_pk.g_stmt_num := 140;
1545 IF mrp_kanban_plan_pk.g_debug THEN
1546 mrp_kanban_plan_pk.g_log_message := 'Debug Statement Number : ' ||
1547 to_char (mrp_kanban_plan_pk.g_stmt_num);
1548 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1549 mrp_kanban_plan_pk.g_log_message := 'Calling CHECK_FOR_LOOPS function';
1550 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1551 END IF;
1552
1553
1554 -- after low level code calculation, if we have component items
1555 -- in mrp_low_level_code table with no low level code assigned
1556 -- then we have loop in the bill. Call the procedure to check this
1557
1558 IF NOT Check_For_Loops THEN
1559 RETURN FALSE;
1560 END IF;
1561
1562 RETURN TRUE;
1563
1564 EXCEPTION
1565
1566 WHEN OTHERS THEN
1567 mrp_kanban_plan_pk.g_log_message := 'SNAPSHOT_ITEM_LOCATIONS Sql Error ';
1568 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1569 mrp_kanban_plan_pk.g_log_message := sqlerrm;
1570 MRP_UTIL.MRP_LOG (mrp_kanban_plan_pk.g_log_message);
1571 RETURN FALSE;
1572
1573 END SNAPSHOT_ITEM_LOCATIONS;
1574
1575
1576 END MRP_KANBAN_SNAPSHOT_PK;
1577
[Home] [Help]