1 PACKAGE BODY FLM_AUTO_REPLENISHMENT AS
2 /* $Header: FLMCPARB.pls 120.2.12010000.2 2008/08/07 06:20:56 bgaddam ship $ */
3
4 /************************************************************************
5 * Package variables *
6 ************************************************************************/
7 G_DEBUG BOOLEAN := (FND_PROFILE.VALUE('MRP_DEBUG') = 'Y');
8
9 /************************************************************************
10 * Private Procedures and Functions *
11 ************************************************************************/
12
13 /************************************************************************
14 * PROCEDURE log *
15 * Inserts error msg into log file. *
16 * *
17 ************************************************************************/
18 PROCEDURE log(info VARCHAR2) is
19 BEGIN
20 FND_FILE.PUT_LINE(FND_FILE.LOG, info);
21 END;
22
23
24 /************************************************************************
25 * PROCEDURE Show_Exception_Messagees *
26 * This procedure gets Exception Messages from the message stack *
27 * and prints it into the log file. *
28 * *
29 ************************************************************************/
30 PROCEDURE Show_Exception_Messages(
31 p_msg_count IN NUMBER,
32 p_msg_data IN VARCHAR2,
33 p_schedule_number IN NUMBER,
34 p_comp_id IN NUMBER,
35 p_organization_id IN NUMBER,
36 p_error_position IN VARCHAR2) IS
37
38 l_comp_name VARCHAR2(600);
39
40 BEGIN
41
42 SELECT concatenated_segments
43 INTO l_comp_name
44 FROM mtl_system_items_kfv
45 WHERE inventory_item_id = p_comp_id
46 AND organization_id = p_organization_id;
47
48 IF (p_error_position = G_Error_Create_Cards) THEN
49 fnd_message.set_name('FLM', 'FLM_AR_ERR_CREATE_CARDS');
50 ELSIF (p_error_position = G_Error_Replenish_Cards) THEN
51 fnd_message.set_name('FLM', 'FLM_AR_ERR_REPLENISH_CARDS');
52 END IF;
53
54 fnd_message.set_token('COMPONENT', l_comp_name);
55 fnd_message.set_token('SCHEDULE', p_schedule_number);
56 log(fnd_message.get);
57
58 IF (p_msg_count >= 1) THEN
59 FOR l_count IN 1..p_msg_count LOOP
60 log(FND_MSG_PUB.Get(l_count, 'F'));
61 END LOOP;
62 END IF;
63
64
65 END Show_Exception_Messages;
66
67 /************************************************************************
68 * FUNCTION Is_Valid_Seq *
69 * Returns true if the particular Line Operation sequence is a *
70 * Valid Line Operation. *
71 * A Line Operation Sequence is a Valid Line Operation : *
72 * a) If the LOP is present in the From_Op_Seq_id and is not part *
73 * of a pure rework loop or *
74 * b) If the LOP is present in the To_Op_Seq_id or this LOP is *
75 * the only operation for this routing (i.e. no network exists *
76 * for this routing). *
77 * *
78 ************************************************************************/
79 FUNCTION Is_Valid_Seq (p_op_seq_id IN NUMBER) RETURN BOOLEAN IS
80 l_cnt NUMBER;
81
82 BEGIN
83
84 IF (p_op_seq_id is null) then
85 return false;
86 END IF;
87
88 /*---------------------------------------------------------------------+
89 | Look at the from_op_seq_id to find out if the line-op |
90 | has a primary path originating from it. |
91 +---------------------------------------------------------------------*/
92 SELECT count(*)
93 INTO l_cnt
94 FROM bom_operation_networks
95 WHERE from_op_seq_id = p_op_seq_id
96 AND nvl(transition_type, 3) = 1;
97
98 IF (l_cnt = 0) THEN
99
100 /*---------------------------------------------------------------------+
101 | Look at the to_op_seq_id to find out if the line-op |
102 | is in primary path. This is the exception for the |
103 | last line-op |
104 +---------------------------------------------------------------------*/
105 SELECT count(*)
106 INTO l_cnt
107 FROM bom_operation_networks
108 WHERE to_op_seq_id = p_op_seq_id
109 AND nvl(transition_type, 3) = 1;
110
111 IF (l_cnt = 0) THEN
112 /*---------------------------------------------------------------------+
113 | If no network exists, then if only one line operation exists |
114 | for this routing, it's valid, otherwise it's not valid. |
115 +---------------------------------------------------------------------*/
116 SELECT count(*)
117 INTO l_cnt
118 FROM bom_operation_sequences
119 WHERE operation_type = 3
120 AND routing_sequence_id = (
121 SELECT max(routing_sequence_id)
122 FROM bom_operation_sequences
123 WHERE operation_sequence_id = p_op_seq_id
124 );
125
126 IF (l_cnt = 1) THEN
127 return true;
128 END IF;
129
130 return false;
131 END IF; -- end of (l_cnt = 0)
132
133 END IF; -- end of (l_cnt = 0)
134
135 return true;
136
137 END is_valid_seq;
138
139
140 /************************************************************************
141 * FUNCTION get_valid_pull_sequence *
142 * Returns the pull sequence id of the given Item if the pull *
143 * sequence is a valid pull sequence else it returns -1. *
144 * An item's pull sequence is valid if it satisfies following *
145 * conditions : *
146 * a) The POU in the Pull Sequence match with those in BOM *
147 * Inventory Components or if POU is not specified in BOM, then *
148 * POU in Pull Sequence match with those specified in the Item *
149 * Master (where POU comprises of a subinventory name and the *
150 * the locator present). *
151 * b) The Pull Sequence has Auto_Request flag set to 'Y'. *
152 * c) Pull Sequence is not a Planning Only Pull sequence i.e. *
153 * release_kanban_flag is set to 1. *
154 * d) The item has Supply type as Pull in Bom Inventory Components *
155 * e) The Item has Release Time Fence set to "Kanban Item (Do not *
156 * Release)" in the Organization Items. *
157 * *
158 ************************************************************************/
159 FUNCTION Get_Valid_Pull_Sequence(
160 p_item_id IN NUMBER,
161 p_organization_id IN NUMBER,
162 p_item_sequence_id IN NUMBER) RETURN NUMBER
163 IS
164
165 l_pull_sequence_id NUMBER;
166
167 BEGIN
168
169 BEGIN
170 SELECT mkps.pull_sequence_id
171 INTO l_pull_sequence_id
172 FROM mtl_kanban_pull_sequences mkps,
173 bom_inventory_components bic,
174 mtl_system_items msi
175 WHERE mkps.inventory_item_id = p_item_id
176 AND mkps.organization_id = p_organization_id
177 AND mkps.auto_request = 'Y'
178 AND mkps.release_kanban_flag = 1
179 AND mkps.inventory_item_id = bic.component_item_id
180 AND msi.inventory_item_id = p_item_id
181 AND msi.organization_id = p_organization_id
182 AND msi.release_time_fence_code = G_Release_Time_Kanban_Item
183 AND bic.component_sequence_id = p_item_sequence_id
184 AND ( (bic.supply_subinventory IS NOT NULL AND mkps.subinventory_name = bic.supply_subinventory)
185 OR (bic.supply_subinventory IS NULL AND mkps.subinventory_name = msi.wip_supply_subinventory) )
186 AND ( (bic.supply_subinventory IS NOT NULL AND nvl(mkps.locator_id, '-0909090909') = nvl(bic.supply_locator_id, '-0909090909'))
187 OR (bic.supply_subinventory IS NULL AND nvl(mkps.locator_id, '-0909090909') = nvl(msi.wip_supply_locator_id, '-0909090909')) )
188 AND bic.wip_supply_type IN (G_Supply_Type_Assembly_Pull, G_Supply_Type_Operation_Pull);
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 l_pull_sequence_id := -1;
193 END;
194
195 return l_pull_sequence_id;
196
197 END Get_Valid_Pull_Sequence;
198
199
200 /************************************************************************
201 * PROCEDURE Get_Subassemlies *
202 * This function returns the subassemblies that are being supplied *
203 * from another line. It takes all subassemblies that are being *
204 * used in the line operations which are in the primary path or in *
205 * the alternate path. It ignores any subassemblies that are used *
206 * in the rework operations. *
207 * This routine also explodes the phantom component. *
208 * *
209 ************************************************************************/
210 PROCEDURE Get_Subassemblies(
211 p_org_id IN NUMBER,
212 p_schedule_number IN VARCHAR2,
213 p_top_assy_id IN NUMBER,
214 p_alt_bom_desig IN VARCHAR2,
215 p_alt_rtg_desig IN VARCHAR2,
216 p_sched_start_date IN DATE,
217 p_comp_table IN OUT NOCOPY comp_list)
218 IS
219
220 l_assy_id NUMBER;
221 assy_table comp_list;
222 max_assy_count NUMBER;
223 max_comp_count NUMBER;
224 curr_assy_count NUMBER;
225 l_comp_id NUMBER;
226 l_comp_name VARCHAR(40);
227 l_usage NUMBER;
228 l_wip_supply_type NUMBER;
229 l_line_id NUMBER;
230 l_operation_seq_num NUMBER;
231 l_component_sequence_id NUMBER;
232 l_routing_sequence_id NUMBER;
233 l_line_op_seq_id NUMBER;
234 l_count NUMBER;
235 l_status BOOLEAN;
236 l_inherit_phantom NUMBER;
237 l_pull_sequence_id NUMBER;
238 l_stmt_no NUMBER;
239 l_top_bill_sequence_id NUMBER;
240 l_bill_sequence_id NUMBER;
241 l_basis_type NUMBER;
242 l_qty_per_lot NUMBER;
243
244 cnt NUMBER :=0;
245 CURSOR comp(l_assy_id number) IS
246 SELECT expl.component_item_id component_item_id,
247 comp.operation_seq_num operation_seq_num,
248 SUM(comp.component_quantity) extended_quantity,
249 MIN(DECODE(comp.wip_supply_type, NULL,
250 DECODE(sys.wip_supply_type, NULL,
251 1, sys.wip_supply_type),
252 comp.wip_supply_type)) wip_supply_type,
253 MIN(comp.component_quantity) component_quantity,
254 MIN(nvl(comp.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL)) basis_type
255 FROM mtl_system_items sys,
256 bom_inventory_components comp,
257 bom_explosions expl,
258 bom_bill_of_materials bbm
259 WHERE bbm.organization_id = sys.organization_id
260 AND comp.component_item_id = sys.inventory_item_id
261 AND comp.component_sequence_id = expl.component_sequence_id
262 AND sys.bom_item_type = 4
263 AND comp.component_item_id = expl.component_item_id
264 AND comp.bill_sequence_id = expl.bill_sequence_id
265 AND bbm.organization_id = p_org_id
266 AND bbm.assembly_item_id = l_assy_id
267 AND (NVL(bbm.alternate_bom_designator, 'ABD756fhh466')
268 = NVL(p_alt_bom_desig, 'ABD756fhh466')
269 OR
270 (bbm.alternate_bom_designator is null AND
271 NOT EXISTS
272 (SELECT null
273 FROM bom_bill_of_materials bbm1
274 WHERE bbm1.alternate_bom_designator = p_alt_bom_desig
275 AND bbm1.organization_id = bbm.organization_id
276 AND bbm1.assembly_item_id = bbm.assembly_item_id)))
277 AND bbm.common_bill_sequence_id = expl.bill_sequence_id
278 AND expl.top_bill_sequence_id = l_top_bill_sequence_id
279 AND expl.assembly_item_id is not null
280 --Bug 6691128 Removing Trunc So that Time to be considered
281 -- AND trunc(expl.effectivity_date) <= trunc(p_sched_start_date)
282 -- AND NVL(expl.disable_date, p_sched_start_date + 1)
283 -- > trunc(p_sched_start_date)
284 AND expl.effectivity_date <= p_sched_start_date
285 AND NVL(expl.disable_date, p_sched_start_date + 1)
286 > p_sched_start_date
287 AND expl.explosion_type = 'ALL'
288 GROUP BY expl.component_item_id,comp.operation_seq_num;
289
290 CURSOR component_sequence_cur IS
291 SELECT component_sequence_id
292 FROM bom_explosions
293 WHERE top_bill_sequence_id = l_top_bill_sequence_id
294 AND explosion_type = 'ALL'
295 AND component_item_id = l_comp_id
296 AND operation_seq_num = l_operation_seq_num
297 --Bug 6691128 Removing Trunc So that Time to be considered
298 -- AND trunc(effectivity_date) <= trunc(p_sched_start_date);
299 AND effectivity_date <= p_sched_start_date
300 AND NVL(disable_date, p_sched_start_date + 1)
301 > p_sched_start_date;
302
303 BEGIN
304
308 SELECT routing_sequence_id
305 l_stmt_no := 100;
306
307 -- Find the routing for the p_top_assy_id
309 INTO l_routing_sequence_id
310 FROM bom_operational_routings
311 WHERE organization_id = p_org_id
312 AND assembly_item_id = p_top_assy_id
313 AND NVL(alternate_routing_designator, 'ABD756fhh456') =
314 NVL(p_alt_rtg_desig, 'ABD756fhh456');
315
316 l_stmt_no := 105;
317
318 max_assy_count := 1;
319 curr_assy_count := 0;
320 assy_table(1).item_id := p_top_assy_id;
321 assy_table(1).usage := 1;
322 max_comp_count := 0;
323
324 l_stmt_no := 110;
325
326 -- To retrive inherit_phantom_op_seq value
327 SELECT inherit_phantom_op_seq
328 INTO l_inherit_phantom
329 FROM bom_parameters
330 WHERE organization_id = p_org_id;
331
332 BEGIN
333 l_stmt_no := 115;
334
335 l_bill_sequence_id := null;
336
337 SELECT bill_sequence_id
338 INTO l_bill_sequence_id
339 FROM bom_bill_of_materials
340 WHERE organization_id = p_org_id
341 AND assembly_item_id = p_top_assy_id
342 AND nvl(alternate_bom_designator, '@@@@') =
343 nvl(p_alt_bom_desig, '@@@@');
344
345 l_top_bill_sequence_id := l_bill_sequence_id;
346
347 l_stmt_no := 120;
348
349 SELECT max(top_bill_sequence_id)
350 INTO l_top_bill_sequence_id
351 FROM bom_explosions
352 WHERE component_item_id = p_top_assy_id
353 AND organization_id = p_org_id;
354 EXCEPTION
355 WHEN OTHERS THEN
356 l_top_bill_sequence_id := l_bill_sequence_id;
357 END;
358
359 LOOP
360 cnt := cnt + 1;
361 curr_assy_count := curr_assy_count + 1;
362
363 IF curr_assy_count > max_assy_count THEN
364 EXIT;
365 END IF;
366
367 l_assy_id := assy_table(curr_assy_count).item_id;
368
369
370 FOR comp_record IN comp(l_assy_id) LOOP
371 l_comp_id := comp_record.component_item_id;
372 l_usage := comp_record.extended_quantity;
373 l_wip_supply_type := comp_record.wip_supply_type;
374
375 -- This stores the operation_seq_num from the parent's BOM
376 l_operation_seq_num := comp_record.operation_seq_num;
377
378 -- Added for Lot Based Material Support
379 -- Retrieve the basis type and component quantity from the bom_explosions table.
380 l_basis_type := comp_record.basis_type;
381 l_qty_per_lot := comp_record.component_quantity;
382
383 IF l_wip_supply_type = G_Supply_Type_Phantom THEN
384 max_assy_count := max_assy_count + 1;
385 assy_table(max_assy_count).item_id := l_comp_id;
386 assy_table(max_assy_count).usage :=
387 assy_table(curr_assy_count).usage * l_usage;
388
389 /*---------------------------------------------------------------------+
390 | Two cases : |
391 | a. For 1st level Subassembly, the operation_seq_num is the |
392 | obtained from the BOM of its parent which is the top assembly. |
393 | b. Otherwise, the operation_seq_num is the operation_seq_num of |
394 | its parent. |
395 +---------------------------------------------------------------------*/
396 IF (l_inherit_phantom = 1) THEN
397 IF (curr_assy_count = 1) THEN
398 assy_table(max_assy_count).operation_seq_num := l_operation_seq_num;
399 ELSE
400 assy_table(max_assy_count).operation_seq_num :=
401 assy_table(curr_assy_count).operation_seq_num;
402 END IF;
403 END IF;
404
405 IF (l_inherit_phantom = 2) THEN
406 assy_table(max_assy_count).operation_seq_num := l_operation_seq_num;
407 END IF;
408
409 ELSE /* l_wip_supply_type != G_Supply_Type_Phantom */
410
411 l_stmt_no := 125;
412
413 -- Get the Component sequence id
414 FOR l_component_seequence_cur IN component_sequence_cur
415 LOOP
416 l_component_sequence_id := l_component_seequence_cur.component_sequence_id;
417
418 /*---------------------------------------------------------------------+
419 | If it's 1st level Subassembly, the component operation_seq_num |
420 | is the operation_seq_num obtained from the BOM. Otherwise, |
421 | it's the operation_seq_num of the 1st subassembly parent. |
422 +---------------------------------------------------------------------*/
423 IF ((l_inherit_phantom = 1) and (curr_assy_count <> 1)) THEN
424 l_operation_seq_num := assy_table(curr_assy_count).operation_seq_num;
425 END IF;
426
427 /*---------------------------------------------------------------------+
428 | Get the corresponding line_op_seq_id for the given operation_seq_num|
429 | in the routing of the top assembly |
430 +---------------------------------------------------------------------*/
431 BEGIN
432 l_stmt_no := 130;
433
434 SELECT line_op_seq_id
435 INTO l_line_op_seq_id
436 FROM bom_operation_sequences
437 WHERE routing_sequence_id = l_routing_sequence_id
441 (SELECT max(effectivity_date)
438 AND operation_seq_num = l_operation_seq_num
439 AND operation_type = 1
440 AND effectivity_date =
442 FROM bom_operation_sequences
443 WHERE routing_sequence_id = l_routing_sequence_id
444 AND operation_seq_num = l_operation_seq_num
445 AND operation_type = 1);
446
447 EXCEPTION
448 WHEN no_data_found THEN
449 l_line_op_seq_id := NULL;
450 END;
451
452 l_stmt_no := 135;
453
454 -- Get pull sequence id of the component
455 l_pull_sequence_id := Get_Valid_Pull_Sequence(l_comp_id, p_org_id, l_component_sequence_id);
456
457 /*---------------------------------------------------------------------+
458 | Include only components that : |
459 | 1. Has a valid Line Operation Sequence. |
460 | 2. Has a valid Pull Sequence |
461 +---------------------------------------------------------------------*/
462
463 IF (is_valid_seq(l_line_op_seq_id) and l_pull_sequence_id <> -1) THEN
464 l_stmt_no := 140;
465 IF (G_DEBUG) THEN
466 log('Valid Component is '||l_comp_id);
467 END IF;
468 max_comp_count := max_comp_count + 1;
469 p_comp_table(max_comp_count).item_id := l_comp_id;
470 p_comp_table(max_comp_count).usage := l_usage * assy_table(curr_assy_count).usage;
471 p_comp_table(max_comp_count).line_id := l_line_id;
472 p_comp_table(max_comp_count).operation_seq_num := l_operation_seq_num;
473 p_comp_table(max_comp_count).line_op_seq_id := l_line_op_seq_id;
474 p_comp_table(max_comp_count).pull_sequence_id := l_pull_sequence_id;
475 p_comp_table(max_comp_count).schedule_number := p_schedule_number;
476
477 -- Added for Lot Based Material Support
478 -- Storing the basis type and qty per lot in the p_comp_table.
479 p_comp_table(max_comp_count).basis_type := l_basis_type;
480 p_comp_table(max_comp_count).qty_per_lot := l_qty_per_lot;
481 END IF;
482
483 END LOOP; -- end of For Loop for component_sequence_cur
484
485 END IF; -- end of if for l_wip_supply_type = G_Supply_Type_Phantom
486
487 END LOOP; -- end of FOR LOOP
488
489 END LOOP;
490 EXCEPTION
491 WHEN OTHERS THEN
492 IF (G_DEBUG) THEN
493 log ('Exception in Get_subassemblies at line number '||l_stmt_no);
494 END IF;
495
496 END Get_Subassemblies; -- end of procedure
497
498
499 /************************************************************************
500 * FUNCTION Get_Operation_Times *
501 * Returns the Operation Time of a given Line Operation rounded *
502 * up to the next takt time. *
503 * *
504 ************************************************************************/
505 FUNCTION Get_Operation_Times(
506 p_line_op_seq_id NUMBER,
507 p_takt_time NUMBER) RETURN NUMBER
508 IS
509
510 l_total_time NUMBER;
511
512 BEGIN
513
514 SELECT CEIL(NVL(total_time_calc, 0) / p_takt_time) * p_takt_time
515 INTO l_total_time
516 FROM bom_operation_sequences
517 WHERE operation_sequence_id = p_line_op_seq_id;
518
519 RETURN l_total_time;
520
521 END Get_Operation_Times;
522
523
524 /************************************************************************
525 * FUNCTION Need_By_Date *
526 * Returns the need by date of a component at a particular Line *
527 * operation for the given assembly item routing on a given line. *
528 * *
529 ************************************************************************/
530 FUNCTION Need_By_Date(
531 p_organization_id NUMBER,
532 p_line_id NUMBER,
533 p_assembly_item_id NUMBER,
534 p_assembly_comp_date DATE,
535 p_assembly_start_date DATE,
536 p_line_op_seq_id NUMBER,
537 p_quantity NUMBER) RETURN DATE
538 IS
539
540 l_start_time NUMBER;
541 l_stop_time NUMBER;
542 l_takt_time NUMBER;
543 l_working_hours NUMBER;
544 l_operation_times NUMBER;
545 l_lead_time NUMBER;
546 l_op_comp_date DATE;
547 l_op_start_date DATE;
548
549 CURSOR Network_Csr (i_start_operation_sequence_id NUMBER) IS
550 SELECT to_op_seq_id
551 FROM Bom_Operation_Networks
552 CONNECT BY PRIOR to_op_seq_id = from_op_seq_id
553 AND nvl(transition_type, 0) NOT IN (2,3)
554 START WITH from_op_seq_id = i_start_operation_sequence_id
555 AND nvl(transition_type, 0) NOT IN (2,3);
556
557 BEGIN
558
559 SELECT start_time,
560 stop_time,
561 1/maximum_rate
562 INTO l_start_time,
563 l_stop_time,
564 l_takt_time
565 FROM wip_lines
566 WHERE line_id = p_line_id
567 AND organization_id = p_organization_id;
568
572 l_working_hours := (l_stop_time + 24 * 3600 - l_start_time) / 3600;
569 IF (l_stop_time > l_start_time) THEN
570 l_working_hours := (l_stop_time - l_start_time) / 3600;
571 ELSE
573 END IF;
574
575 l_operation_times := Get_Operation_Times(p_line_op_seq_id, l_takt_time);
576
577 FOR l_Network_Rec in Network_Csr(p_line_op_seq_id)
578 LOOP
579 l_operation_times := l_operation_times +
580 Get_Operation_Times(l_Network_Rec.to_op_seq_id, l_takt_time);
581 END LOOP;
582
583 -- Operation completion time
584 l_op_comp_date := MRP_LINE_SCHEDULE_ALGORITHM.calculate_begin_time(
585 p_organization_id,
586 p_assembly_comp_date,
587 l_operation_times / l_working_hours,
588 l_start_time,
589 l_stop_time);
590
591 SELECT nvl(fixed_lead_time, 0) + (p_quantity - 1) * nvl(variable_lead_time, 0)
592 INTO l_lead_time
593 FROM mtl_system_items
594 WHERE inventory_item_id = p_assembly_item_id
595 AND organization_id = p_organization_id;
596
597 -- Operation Start time
598 l_op_start_date := MRP_LINE_SCHEDULE_ALGORITHM.calculate_begin_time(
599 p_organization_id,
600 l_op_comp_date,
601 l_lead_time,
602 l_start_time,
603 l_stop_time);
604
605 return l_op_start_date;
606
607 END Need_By_Date;
608
609
610 /************************************************************************
611 * PROCEDURE Update_Flow_Schedule *
612 * Updates the Auto Replenish flag of the given Flow Schedule. *
613 * *
614 ************************************************************************/
615 PROCEDURE Update_Flow_Schedule(
616 p_schedule_number IN VARCHAR2) IS
617
618 BEGIN
619
620 UPDATE wip_flow_schedules
621 SET auto_replenish = 'Y'
622 WHERE schedule_number = p_Schedule_Number;
623
624 END Update_Flow_Schedule;
625
626
627 /************************************************************************
628 * PROCEDURE Print_Kanban_Cards *
629 * Prints the Kanban Cards. *
630 * *
631 ************************************************************************/
632 PROCEDURE Print_Kanban_Cards(
633 p_Kanban_Card_Ids IN Kanban_Card_Id_Tbl_Type) IS
634
635 l_report_id NUMBER;
636 l_card_count NUMBER;
637 l_req_id NUMBER;
638 l_sort_by NUMBER;
639 l_call_from NUMBER;
640 BEGIN
641
642 SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
643 INTO l_report_id
644 FROM dual;
645
646 FOR l_card_count in 1..p_Kanban_Card_Ids.COUNT
647 LOOP
648
649 INSERT into MTL_KANBAN_CARD_PRINT_TEMP(
650 REPORT_ID,
651 KANBAN_CARD_ID)
652 VALUES (l_report_id,
653 p_Kanban_Card_Ids(l_card_count));
654
655 END LOOP; -- end of for loop
656
657 l_sort_by := 3; -- for sort by Subinventory, locator
658 l_call_from := 2; -- as we are passing report_id
659
660 l_req_id := fnd_request.submit_request( 'INV',
661 'INVKBCPR',
662 NULL,
663 NULL,
664 FALSE,
665 NULL, /* p_org_id */
666 NULL, /* p_date_created_low */
667 NULL, /* p_date_created_high */
668 NULL, /* p_kanban_card_number_low */
669 NULL, /* p_kanban_card_number_high */
670 NULL, /* p_item_low */
671 NULL, /* p_item_high */
672 NULL, /* p_subinv */
673 NULL, /* p_locator_low */
674 NULL, /* p_locator_high */
675 NULL, /* p_source_type */
676 NULL, /* p_kanban_card_type */
677 NULL, /* p_supplier */
678 NULL, /* p_supplier_site */
679 NULL, /* p_source_org_id */
680 NULL, /* p_source_subinv */
681 NULL, /* p_source_loc_id */
682 l_sort_by, /* p_sort_by */
683 l_call_from, /* p_call_from */
684 NULL, /* p_kanban_card_id */
685 l_report_id /* p_report_id */
686 );
687
688 IF l_req_id = 0 THEN
689 DELETE FROM mtl_kanban_card_print_temp
690 WHERE report_id = l_report_id;
691 END IF;
692 Commit;
693
694 END Print_Kanban_Cards;
695
696
697 /************************************************************************
698 * Public Procedures and Functions *
699 ************************************************************************/
700
701
705 * The Input parameters for this procedure are : *
702 /************************************************************************
703 * PROCEDURE Create_And_Replenish_Cards *
704 * This is the procedure which is called by the Concurrent Request *
706 * p_organization_id - Organization Identifier *
707 * p_min_line_code - From Line Identifier *
708 * p_max_line_code - To Line Identifier *
709 * - To find flow schedules which are on lines *
710 between From and To Lines identifier *
711 * p_completion_date - Completion Date of Flow Schedules *
712 * - To find flow schedules which have scheduled *
713 * completion date less than the given date *
714 * greater than the sysdate *
715 * p_build_sequence - Build Sequence of Flow Schedules *
716 * - To find flow schedules which have build *
717 * sequence less than or equal to the given *
718 * build sequence and if this parameter is null*
719 * then find all flow schedules which have not *
720 * not build sequence *
721 * p_print_card - Print Kanban Cards Option (Yes/No) *
722 ************************************************************************/
723 PROCEDURE Create_And_Replenish_Cards(
724 o_error_code OUT NOCOPY NUMBER,
725 o_error_msg OUT NOCOPY VARCHAR2,
726 p_organization_id IN NUMBER,
727 p_min_line_code IN VARCHAR2,
728 p_max_line_code IN VARCHAR2,
729 p_completion_date IN VARCHAR2,
730 p_build_sequence IN NUMBER,
731 p_print_card IN VARCHAR2) IS
732
733 l_api_version_number CONSTANT NUMBER := 1.0;
734
735 l_comp_count NUMBER;
736 l_Card_Count NUMBER := 0;
737 l_schedule_number VARCHAR2(30);
738 l_auto_replenish VARCHAR2(1);
739 l_wip_entity_id NUMBER;
740 l_build_sequence NUMBER;
741 l_primary_item_id NUMBER;
742 l_line_id NUMBER;
743 l_open_qty NUMBER;
744 l_alt_bom VARCHAR2(10);
745 l_alt_rtg VARCHAR2(10);
746 l_Kanban_Card_Id NUMBER;
747 l_msg_count NUMBER;
748 l_msg_data VARCHAR2(2000);
749 l_return_status VARCHAR2(10);
750 l_comp_name VARCHAR2(600);
751 l_client_tz_id NUMBER;
752 l_server_tz_id NUMBER;
753
754 /*------------------------------------------------------------------------+
755 | l_program_status signifies the status of concurrent program. It has |
756 | following values :- |
757 | 'I' - Initial Status (default), when program is picking schedules |
758 | 'F' - Fail Status, which signfies that all schedules have component |
759 | to be picked, and none of them resulted in replenishment of |
760 | the card and thus status is fail. |
761 | 'S' - Success Status, which signifies that status was not fail |
762 | (Note : Even if program status has value 'S', still program |
763 | may finish with Warning status |
764 +------------------------------------------------------------------------*/
765 l_program_status VARCHAR2(1) := 'I';
766
767 l_start_date DATE;
768 l_completion_date DATE;
769 l_need_by_date DATE;
770 l_server_compl_date DATE;
771
772 l_Request_Status BOOLEAN;
773
774 l_comp_table comp_list;
775 l_Kanban_Card_Ids Kanban_Card_Id_Tbl_Type;
776
777 create_exception EXCEPTION;
778 update_exception EXCEPTION;
779 replenish_exception EXCEPTION;
780 timezone_exception EXCEPTION;
781
782 l_usage NUMBER;
783 -- cursor to retrieve the flow schedules based on the criteria
784 CURSOR flow_schedule_csr IS
785 SELECT flow.schedule_number,
786 flow.build_sequence build_sequence,
787 flow.primary_item_id primary_item_id,
788 flow.line_id line_id,
789 (flow.planned_quantity - nvl(flow.quantity_completed, 0)) open_quantity,
790 flow.scheduled_start_date scheduled_start_date,
791 flow.scheduled_completion_date scheduled_completion_date,
792 flow.alternate_bom_designator alternate_bom_designator,
793 flow.alternate_routing_designator alternate_routing_designator
794 FROM wip_flow_schedules flow,
795 wip_lines lines
796 WHERE flow.planned_quantity - nvl(flow.quantity_completed, 0) > 0
797 AND flow.status <> 2
798 AND flow.scheduled_completion_date <= (l_server_compl_date + 1)
799 AND flow.scheduled_completion_date >= sysdate
800 AND flow.line_id = lines.line_id
801 AND flow.organization_id = lines.organization_id
802 AND lines.organization_id = p_organization_id
803 AND lines.line_code BETWEEN p_min_line_code AND p_max_line_code
804 AND ( (p_build_sequence is not null AND flow.build_sequence <= p_build_sequence)
805 OR (p_build_sequence is null AND flow.build_sequence is not null))
806 AND nvl(flow.auto_replenish, 'N') = 'N';
807
808 BEGIN
809
810 Savepoint spBegin;
811
812 IF (G_DEBUG) THEN
813 log('Welcome to Auto Replenishment');
814 log('Org = '||p_organization_id);
815 log('Min Line = '||p_min_line_code);
816 log('Max Line = '||p_max_line_code);
817 log('Compl Date = '||p_completion_date);
818 log('Build Seq = '||p_build_sequence);
819 log('Print = '||p_print_card);
820 END IF;
821
825 l_server_tz_id := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
822 /* To convert the completion date entered by the user in the concurrent program to the
823 Server timezone */
824 l_client_tz_id := to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
826
827 HZ_TIMEZONE_PUB.Get_Time(
828 p_api_version => 1.0,
829 p_init_msg_list => 'F',
830 p_source_tz_id => l_client_tz_id,
831 p_dest_tz_id => l_server_tz_id,
832 p_source_day_time => fnd_date.canonical_to_date(p_completion_date),
833 x_dest_day_time => l_server_compl_date,
834 x_return_status => l_return_status,
835 x_msg_count => l_msg_count,
836 x_msg_data => l_msg_data);
837
838 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
839 raise timezone_exception;
840 END IF;
841
842 FOR l_flow_schedule_csr IN flow_schedule_csr
843 LOOP
844 l_schedule_number := l_flow_schedule_csr.schedule_number;
845 l_build_sequence := l_flow_schedule_csr.build_sequence;
846 l_primary_item_id := l_flow_schedule_csr.primary_item_id;
847 l_line_id := l_flow_schedule_csr.line_id;
848 l_open_qty := l_flow_schedule_csr.open_quantity;
849 l_start_date := l_flow_schedule_csr.scheduled_start_date;
850 l_completion_date := l_flow_schedule_csr.scheduled_completion_date;
851 l_alt_bom := l_flow_schedule_csr.alternate_bom_designator;
852 l_alt_rtg := l_flow_schedule_csr.alternate_routing_designator;
853
854 IF (G_DEBUG) THEN
855 log('Retrieved Schedules, Schedule = '|| l_schedule_number ||
856 ', Assy = '|| l_primary_item_id ||
857 ', Line = '|| l_line_id ||
858 ', Qty = '|| l_open_qty ||
859 ', Completion Date = '|| l_completion_date ||
860 ', Alt BOM = '|| l_alt_bom ||
861 ', Alt Routing = '|| l_alt_rtg);
862 END IF;
863
864 BEGIN
865 Savepoint spSchedule;
866
867 -- Lock the flow schedule for update of auto_replenish flag
868 BEGIN
869 SELECT auto_replenish
870 INTO l_auto_replenish
871 FROM wip_flow_schedules
872 WHERE schedule_number = l_schedule_number
873 FOR UPDATE OF auto_replenish NOWAIT;
874 EXCEPTION
875 WHEN OTHERS THEN
876 raise update_exception;
877 END;
878
879
880 IF (l_comp_table.COUNT <> 0) THEN
881 l_comp_table.DELETE;
882 END IF;
883
884 -- Get all the components
885 get_subassemblies(p_organization_id,
886 l_schedule_number,
887 l_primary_item_id,
888 l_alt_bom,
889 l_alt_rtg,
890 l_start_date,
891 l_comp_table);
892
893 l_comp_count := 1;
894 while l_comp_table.EXISTS(l_comp_count) LOOP
895 IF (G_DEBUG) THEN
896 log('Retrieved Valid Components in Assembly, Comp = '|| l_comp_table(l_comp_count).item_id ||
897 ', Comp Pull Sequence = '|| l_comp_table(l_comp_count).pull_sequence_id);
898 END IF;
899
900 IF (nvl(l_program_status, 'I') <> 'S') THEN
901 l_program_status := 'F';
902 END IF;
903
904 -- Added for Lot Based Material Support
905 -- For item basis type, the usage is the component cumulative usage * flow schedule open qty
906 -- For lot basis type, the usage is the qty per assembly for that component.
907 if (l_comp_table(l_comp_count).basis_type = WIP_CONSTANTS.ITEM_BASED_MTL) then
908 l_usage:=l_comp_table(l_comp_count).usage*l_open_qty;
909 else
910 l_usage:=l_comp_table(l_comp_count).qty_per_lot;
911 end if;
912
913 -- Create Non-replenishable Kanban Card
914 INV_Kanban_GRP.Create_Non_Replenishable_Card(
915 X_return_status => l_return_status,
916 X_msg_data => l_msg_data,
917 X_msg_count => l_msg_count,
918 X_Kanban_Card_Id => l_Kanban_Card_id,
919 p_pull_sequence_id => l_comp_table(l_comp_count).pull_sequence_id,
920 p_kanban_size => l_usage);
921
922 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS and l_Kanban_Card_Id IS NULL) THEN
923
924 Show_Exception_Messages(
925 p_msg_count => l_msg_count,
926 p_msg_data => l_msg_data,
927 p_schedule_number => l_schedule_number,
928 p_comp_id => l_comp_table(l_comp_count).item_id,
929 p_organization_id => p_organization_id,
930 p_error_position => G_Error_Create_Cards);
931
932 l_Request_Status := fnd_concurrent.set_completion_status(status => 'WARNING',
933 message => '');
934 raise create_exception;
935
936 END IF;
937
938 IF (G_DEBUG) THEN
939 log('Created Non-Replenishable Kanban Card, Card Id = '|| l_Kanban_Card_Id);
940 END IF;
941
942 -- Get Need By date for the component
943 l_need_by_date := Need_By_Date(p_organization_id,
944 l_line_id,
945 l_comp_table(l_comp_count).item_id,
946 l_completion_date,
947 l_start_date,
948 l_comp_table(l_comp_count).line_op_seq_id,
949 l_usage);
950
951 -- Replenish the Cards
952 SELECT wip_entity_id
953 INTO l_wip_entity_id
954 FROM wip_flow_schedules
958 INV_Kanban_GRP.Update_Card_Supply_Status(
955 WHERE schedule_number = l_comp_table(l_comp_count).schedule_number
956 AND organization_id = p_organization_id;
957
959 x_msg_count => l_msg_count,
960 x_msg_data => l_msg_data,
961 x_return_status => l_return_status,
962 p_api_version_number => l_api_version_number,
963 p_init_msg_list => NULL,
964 p_commit => NULL,
965 p_Kanban_Card_Id => l_Kanban_Card_Id,
966 p_Supply_Status => G_Supply_Status_Empty,
967 p_Document_Type => NULL,
968 p_Document_Header_Id => NULL,
969 p_Document_Detail_Id => NULL,
970 p_Need_By_Date => l_need_by_date,
971 p_Source_Wip_Entity_Id => l_wip_entity_id);
972
973 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
974
975 Show_Exception_Messages(
976 p_msg_count => l_msg_count,
977 p_msg_data => l_msg_data,
978 p_schedule_number => l_schedule_number,
979 p_comp_id => l_comp_table(l_comp_count).item_id,
980 p_organization_id => p_organization_id,
981 p_error_position => G_Error_Replenish_Cards);
982
983 l_Request_Status := fnd_concurrent.set_completion_status(status => 'WARNING',
984 message => '');
985 raise replenish_exception;
986
987 END IF;
988
989 IF (G_DEBUG) THEN
990 log('Replenished Kanban Card, Card Id = '|| l_Kanban_Card_Id);
991 END IF;
992
993 -- Update Auto_Replenish flag in Flow Scheules
994 Update_Flow_Schedule(l_comp_table(l_comp_count).schedule_number);
995
996 IF (G_DEBUG) THEN
997 log('Updated Auto Replenish flag of Flow schedules, Schedule number = '|| l_comp_table(l_comp_count).schedule_number);
998 END IF;
999
1000 l_Card_Count := l_Card_Count + 1;
1001 l_Kanban_Card_Ids(l_Card_Count) := l_Kanban_Card_Id;
1002
1003 l_comp_count := l_comp_count + 1;
1004
1005 l_program_status := 'S';
1006
1007 END LOOP; -- end of while loop
1008
1009 l_program_status := 'S';
1010
1011 EXCEPTION
1012 WHEN create_exception THEN
1013 Rollback to spSchedule;
1014 WHEN replenish_exception THEN
1015 Rollback to spSchedule;
1016 WHEN update_exception THEN
1017 fnd_message.set_name('FLM', 'FLM_AR_ERR_LOCK_SCHEDULE');
1018 log(fnd_message.get);
1019 Rollback to spSchedule;
1020 WHEN OTHERS THEN
1021 Rollback to spSchedule;
1022
1023 END; -- end of Begin
1024
1025 END LOOP; -- end of for loop
1026
1027 /*-------------------------------------------------------------------------------------------+
1028 | Print Kanban Cards |
1029 | If Print Kanban Cards option is selected and cards have been created then only print cards|
1030 +-------------------------------------------------------------------------------------------*/
1031 IF (p_print_card = 1 AND l_Kanban_Card_Ids.COUNT > 0) THEN
1032
1033 Print_Kanban_Cards(l_Kanban_Card_Ids);
1034
1035 IF (G_DEBUG) THEN
1036 log('Print request generated for creating Kanban Cards');
1037 END IF;
1038
1039 END IF;
1040
1041 /*-------------------------------------------------------------------------------------------+
1042 | If program_status has not been set to 'S', it signifies that all the flow schedules have |
1043 | failed, i.e. all schedules have components to be replenished and none of the flow schedule|
1044 | succeeded, thus setting the request completion status to ERROR. |
1045 +-------------------------------------------------------------------------------------------*/
1046
1047 IF ( nvl(l_program_status, 'I') = 'F' ) THEN
1048 l_Request_Status := fnd_concurrent.set_completion_status(status => 'ERROR',
1049 message => '');
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN timezone_exception THEN
1054 fnd_message.set_name('FLM', 'FLM_AR_ERR_TIMEZONE');
1055 log(fnd_message.get);
1056 WHEN OTHERS THEN
1057 fnd_message.set_name('FLM', 'FLM_AR_ERR_UNEXP');
1058 log(fnd_message.get);
1059 END create_and_replenish_cards;
1060
1061
1062 END FLM_AUTO_REPLENISHMENT;
[Home] [Help]