1 PACKAGE BODY FLM_AUTO_REPLENISHMENT AS
2 /* $Header: FLMCPARB.pls 120.5.12020000.2 2012/07/13 10:55:37 sisankar 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
305 l_stmt_no := 100;
306
307 -- Find the routing for the p_top_assy_id
308 SELECT routing_sequence_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
438 AND operation_seq_num = l_operation_seq_num
439 AND operation_type = 1
440 AND effectivity_date =
441 (SELECT max(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
569 IF (l_stop_time > l_start_time) THEN
570 l_working_hours := (l_stop_time - l_start_time) / 3600;
571 ELSE
572 l_working_hours := (l_stop_time + 24 * 3600 - l_start_time) / 3600;
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 * e-Kanban Changes to launch the correct report *
631 ************************************************************************/
632 PROCEDURE Print_Kanban_Cards( p_Kanban_Card_Ids IN Kanban_Card_Id_Tbl_Type) IS
633
634 l_report_id NUMBER;
635 l_card_count NUMBER;
636 l_req_id NUMBER;
637 l_sort_by NUMBER;
638 l_call_from NUMBER;
639
640 l_conc_prog VARCHAR2(15);
641 l_add_layout BOOLEAN;
642 l_language VARCHAR2 (10);
643 l_territory VARCHAR2 (10);
644
645 BEGIN
646
647 SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval INTO l_report_id FROM dual;
648
649 FOR l_card_count in 1..p_Kanban_Card_Ids.COUNT LOOP
650 INSERT into MTL_KANBAN_CARD_PRINT_TEMP( REPORT_ID,KANBAN_CARD_ID)
651 VALUES (l_report_id,p_Kanban_Card_Ids(l_card_count));
652 END LOOP; -- end of for loop
653
654 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 THEN
655 l_conc_prog := 'INVKBCPR_XML';
656 BEGIN
657 SELECT LOWER(FL.iso_language),ISO_TERRITORY
658 INTO l_language,l_territory
659 FROM NLS_SESSION_PARAMETERS NSP,fnd_languages FL
660 WHERE NSP.parameter = 'NLS_LANGUAGE'
661 AND NSP.value = FL.nls_language;
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 FND_MESSAGE.set_name('FLM', 'FLM_SET_NLS_PARAM');
665 fnd_file.put_line(fnd_file.log, fnd_message.get);
666 END;
667 l_add_layout := fnd_request.add_layout ('INV', 'INVKBCPR_XML', l_language, l_territory, 'RTF');
668 ELSE
669 l_conc_prog := 'INVKBCPR';
670 END IF;
671
672 l_sort_by := 3; -- for sort by Subinventory, locator
673 l_call_from := 2; -- as we are passing report_id
674
675 l_req_id := fnd_request.submit_request( 'INV',
676 l_conc_prog,
677 NULL,
678 NULL,
679 FALSE,
680 NULL, /* p_org_id */
681 NULL, /* p_date_created_low */
682 NULL, /* p_date_created_high */
683 NULL, /* p_kanban_card_number_low */
684 NULL, /* p_kanban_card_number_high */
685 NULL, /* p_item_low */
686 NULL, /* p_item_high */
687 NULL, /* p_subinv */
688 NULL, /* p_locator_low */
689 NULL, /* p_locator_high */
690 NULL, /* p_source_type */
691 NULL, /* p_kanban_card_type */
692 NULL, /* p_supplier */
693 NULL, /* p_supplier_site */
694 NULL, /* p_source_org_id */
695 NULL, /* p_source_subinv */
696 NULL, /* p_source_loc_id */
697 l_sort_by, /* p_sort_by */
698 l_call_from, /* p_call_from */
699 NULL, /* p_kanban_card_id */
700 l_report_id /* p_report_id */
701 );
702
703 IF l_req_id = 0 THEN
704 DELETE FROM mtl_kanban_card_print_temp WHERE report_id = l_report_id;
705 END IF;
706 Commit;
707
708 END Print_Kanban_Cards;
709
710
711 /************************************************************************
712 * Public Procedures and Functions *
713 ************************************************************************/
714
715
716 /************************************************************************
717 * PROCEDURE Create_And_Replenish_Cards *
718 * This is the procedure which is called by the Concurrent Request *
719 * The Input parameters for this procedure are : *
720 * p_organization_id - Organization Identifier *
721 * p_min_line_code - From Line Identifier *
722 * p_max_line_code - To Line Identifier *
723 * - To find flow schedules which are on lines *
724 between From and To Lines identifier *
725 * p_completion_date - Completion Date of Flow Schedules *
726 * - To find flow schedules which have scheduled *
727 * completion date less than the given date *
728 * greater than the sysdate *
729 * p_build_sequence - Build Sequence of Flow Schedules *
730 * - To find flow schedules which have build *
731 * sequence less than or equal to the given *
732 * build sequence and if this parameter is null*
733 * then find all flow schedules which have not *
734 * not build sequence *
735 * p_print_card - Print Kanban Cards Option (Yes/No) *
736 ************************************************************************/
737 PROCEDURE Create_And_Replenish_Cards(
738 o_error_code OUT NOCOPY NUMBER,
739 o_error_msg OUT NOCOPY VARCHAR2,
740 p_organization_id IN NUMBER,
741 p_min_line_code IN VARCHAR2,
742 p_max_line_code IN VARCHAR2,
743 p_from_completion_date IN VARCHAR2, /*Added for bug 6816497 */
744 p_completion_date IN VARCHAR2,
745 p_from_build_sequence IN NUMBER, /*Added for bug 6816497 */
746 p_build_sequence IN NUMBER,
747 p_print_card IN VARCHAR2) IS
748
749 l_api_version_number CONSTANT NUMBER := 1.0;
750
751 l_comp_count NUMBER;
752 l_Card_Count NUMBER := 0;
753 l_schedule_number VARCHAR2(30);
754 l_auto_replenish VARCHAR2(1);
755 l_wip_entity_id NUMBER;
756 l_build_sequence NUMBER;
757 l_primary_item_id NUMBER;
758 l_line_id NUMBER;
759 l_open_qty NUMBER;
760 l_alt_bom VARCHAR2(10);
761 l_alt_rtg VARCHAR2(10);
762 l_Kanban_Card_Id NUMBER;
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(2000);
765 l_return_status VARCHAR2(10);
766 l_comp_name VARCHAR2(600);
767 l_client_tz_id NUMBER;
768 l_server_tz_id NUMBER;
769
770 /*------------------------------------------------------------------------+
771 | l_program_status signifies the status of concurrent program. It has |
772 | following values :- |
773 | 'I' - Initial Status (default), when program is picking schedules |
774 | 'F' - Fail Status, which signfies that all schedules have component |
775 | to be picked, and none of them resulted in replenishment of |
776 | the card and thus status is fail. |
777 | 'S' - Success Status, which signifies that status was not fail |
778 | (Note : Even if program status has value 'S', still program |
779 | may finish with Warning status |
780 +------------------------------------------------------------------------*/
781 l_program_status VARCHAR2(1) := 'I';
782
783 l_start_date DATE;
784 l_completion_date DATE;
785 l_need_by_date DATE;
786 l_server_compl_date DATE;
787 l_server_from_compl_date DATE; /*Added for bug 6816497 */
788
789 l_Request_Status BOOLEAN;
790
791 l_comp_table comp_list;
792 l_Kanban_Card_Ids Kanban_Card_Id_Tbl_Type;
793
794 create_exception EXCEPTION;
795 update_exception EXCEPTION;
796 replenish_exception EXCEPTION;
797 timezone_exception EXCEPTION;
798 update_ar_exception EXCEPTION; --bug 6816497
799
800 l_usage NUMBER;
801 -- cursor to retrieve the flow schedules based on the criteria
802 CURSOR flow_schedule_csr IS
803 SELECT flow.schedule_number,
804 flow.build_sequence build_sequence,
805 flow.primary_item_id primary_item_id,
806 flow.line_id line_id,
807 (flow.planned_quantity - nvl(flow.quantity_completed, 0)) open_quantity,
808 flow.scheduled_start_date scheduled_start_date,
809 flow.scheduled_completion_date scheduled_completion_date,
810 flow.alternate_bom_designator alternate_bom_designator,
811 flow.alternate_routing_designator alternate_routing_designator
812 FROM wip_flow_schedules flow,
813 wip_lines lines
814 WHERE flow.planned_quantity - nvl(flow.quantity_completed, 0) > 0
815 AND flow.status <> 2
816 AND flow.scheduled_completion_date <= (l_server_compl_date + 1)
817 AND flow.scheduled_completion_date >= nvl(l_server_from_compl_date,sysdate) /*Added for bug 6816497 */
818 AND flow.line_id = lines.line_id
819 AND flow.organization_id = lines.organization_id
820 AND lines.organization_id = p_organization_id
821 AND lines.line_code BETWEEN p_min_line_code AND p_max_line_code
822 AND ( (p_build_sequence is not null AND flow.build_sequence <= p_build_sequence AND
823 flow.build_sequence >= nvl(p_from_build_sequence,flow.build_sequence)) /*Added for bug 6816497 */
824 OR (p_build_sequence is null AND flow.build_sequence is not null))
825 AND nvl(flow.auto_replenish, 'N') = 'N';
826
827
828 r_kanban_card_rec FLM_EKANBAN_PUB.kanban_card_rec_type;
829 l_item_id Number;
830 l_org_id Number;
831 l_subinv varchar2(20);
832 l_src_type Number;
833 l_kanban_size Number;
834 l_locator Number;
835 l_src_org_id Number;
836 l_src_subinv varchar2(20);
837 l_src_locator Number;
838 l_wip_line_id Number;
839 l_release_kanban_flag Number;
840
841
842 BEGIN
843
844 Savepoint spBegin;
845
846 IF (G_DEBUG) THEN
847 log('Welcome to Auto Replenishment');
848 log('Org = '||p_organization_id);
849 log('Min Line = '||p_min_line_code);
850 log('Max Line = '||p_max_line_code);
851 log('From Compl Date ='||p_from_completion_date); /*Added for bug 6816497 */
852 log('To Compl Date = '||p_completion_date);
853 log('From Build Seq = '||p_from_build_sequence); /*Added for bug 6816497 */
854 log('To Build Seq = '||p_build_sequence);
855 log('Print = '||p_print_card);
856 END IF;
857
858 /* To convert the completion date entered by the user in the concurrent program to the
859 Server timezone */
860 l_client_tz_id := to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
861 l_server_tz_id := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
862
863 HZ_TIMEZONE_PUB.Get_Time(
864 p_api_version => 1.0,
865 p_init_msg_list => 'F',
866 p_source_tz_id => l_client_tz_id,
867 p_dest_tz_id => l_server_tz_id,
868 p_source_day_time => fnd_date.canonical_to_date(p_completion_date),
869 x_dest_day_time => l_server_compl_date,
870 x_return_status => l_return_status,
871 x_msg_count => l_msg_count,
872 x_msg_data => l_msg_data);
873
874 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
875 raise timezone_exception;
876 END IF;
877
878 /************Added for bug 6816497 ************ */
879
880 HZ_TIMEZONE_PUB.Get_Time(
881 p_api_version => 1.0,
882 p_init_msg_list => 'F',
883 p_source_tz_id => l_client_tz_id,
884 p_dest_tz_id => l_server_tz_id,
885 p_source_day_time => fnd_date.canonical_to_date(p_from_completion_date),
886 x_dest_day_time => l_server_from_compl_date,
887 x_return_status => l_return_status,
888 x_msg_count => l_msg_count,
889 x_msg_data => l_msg_data);
890
891 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
892 raise timezone_exception;
893 END IF;
894
895 /*********************************************************/
896
897
898 FOR l_flow_schedule_csr IN flow_schedule_csr
899 LOOP
900 l_schedule_number := l_flow_schedule_csr.schedule_number;
901 l_build_sequence := l_flow_schedule_csr.build_sequence;
902 l_primary_item_id := l_flow_schedule_csr.primary_item_id;
903 l_line_id := l_flow_schedule_csr.line_id;
904 l_open_qty := l_flow_schedule_csr.open_quantity;
905 l_start_date := l_flow_schedule_csr.scheduled_start_date;
906 l_completion_date := l_flow_schedule_csr.scheduled_completion_date;
907 l_alt_bom := l_flow_schedule_csr.alternate_bom_designator;
908 l_alt_rtg := l_flow_schedule_csr.alternate_routing_designator;
909
910 IF (G_DEBUG) THEN
911 log('Retrieved Schedules, Schedule = '|| l_schedule_number ||
912 ', Assy = '|| l_primary_item_id ||
913 ', Line = '|| l_line_id ||
914 ', Qty = '|| l_open_qty ||
915 ', Completion Date = '|| l_completion_date ||
916 ', Alt BOM = '|| l_alt_bom ||
917 ', Alt Routing = '|| l_alt_rtg);
918 END IF;
919
920 BEGIN
921 Savepoint spSchedule;
922
923 -- Lock the flow schedule for update of auto_replenish flag
924 BEGIN
925 SELECT auto_replenish
926 INTO l_auto_replenish
927 FROM wip_flow_schedules
928 WHERE schedule_number = l_schedule_number
929 FOR UPDATE OF auto_replenish NOWAIT;
930 EXCEPTION
931 WHEN OTHERS THEN
932 raise update_exception;
933 END;
934
935 --Bug 6816497
936 BEGIN
937 --before locking check the auto_replenish flag, this schedule may have been updated by other request
938 SELECT NVL(auto_replenish, 'N')
939 INTO l_auto_replenish
940 FROM wip_flow_schedules
941 WHERE schedule_number = l_schedule_number;
942
943
944 IF l_auto_replenish = 'Y' then
945 raise update_ar_exception;
946 END IF;
947 END;
948
949 IF (l_comp_table.COUNT <> 0) THEN
950 l_comp_table.DELETE;
951 END IF;
952
953 -- Get all the components
954 get_subassemblies(p_organization_id,
955 l_schedule_number,
956 l_primary_item_id,
957 l_alt_bom,
958 l_alt_rtg,
959 l_start_date,
960 l_comp_table);
961
962 l_comp_count := 1;
963 while l_comp_table.EXISTS(l_comp_count) LOOP
964 IF (G_DEBUG) THEN
965 log('Retrieved Valid Components in Assembly, Comp = '|| l_comp_table(l_comp_count).item_id ||
966 ', Comp Pull Sequence = '|| l_comp_table(l_comp_count).pull_sequence_id);
967 END IF;
968
969 IF (nvl(l_program_status, 'I') <> 'S') THEN
970 l_program_status := 'F';
971 END IF;
972
973 -- Added for Lot Based Material Support
974 -- For item basis type, the usage is the component cumulative usage * flow schedule open qty
975 -- For lot basis type, the usage is the qty per assembly for that component.
976 if (l_comp_table(l_comp_count).basis_type = WIP_CONSTANTS.ITEM_BASED_MTL) then
977 l_usage:=l_comp_table(l_comp_count).usage*l_open_qty;
978 else
979 l_usage:=l_comp_table(l_comp_count).qty_per_lot;
980 end if;
981
982
983 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 THEN
984
985 -- In eKanban Create temporary Cards for 1 cycle, rather than NRK
986
987 SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUBINVENTORY_NAME,SOURCE_TYPE,LOCATOR_ID,SOURCE_ORGANIZATION_ID,
988 SOURCE_SUBINVENTORY,SOURCE_LOCATOR_ID,WIP_LINE_ID,1
989 INTO l_item_id,l_org_id,l_subinv,l_src_type,l_locator,l_src_org_id,l_src_subinv,l_src_locator,l_wip_line_id,l_release_kanban_flag
990 FROM MTL_KANBAN_PULL_SEQUENCES WHERE PULL_SEQUENCE_ID = l_comp_table(l_comp_count).pull_sequence_id;
991
992 r_kanban_card_rec := null;
993 r_kanban_card_rec.pull_sequence_id := l_comp_table(l_comp_count).pull_sequence_id;
994 r_kanban_card_rec.kanban_card_id := null;
995 r_kanban_card_rec.kanban_card_number := null;
996 r_kanban_card_rec.inventory_item_code := null;
997 r_kanban_card_rec.inventory_item_id := l_item_id;
998 r_kanban_card_rec.organization_code := null;
999 r_kanban_card_rec.organization_id := l_org_id;
1000 r_kanban_card_rec.subinventory_name := l_subinv;
1001 r_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_New;
1002 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Active;
1003 r_kanban_card_rec.kanban_card_type := INV_Kanban_Pvt.g_card_type_replenishable;
1004 r_kanban_card_rec.source_type := l_src_type;
1005 r_kanban_card_rec.kanban_size := l_usage;
1006 r_kanban_card_rec.locator_id := l_locator;
1007 r_kanban_card_rec.supplier_id := null;
1008 r_kanban_card_rec.supplier_name := null;
1009 r_kanban_card_rec.supplier_site_id := null;
1010 r_kanban_card_rec.supplier_site_code := null;
1011 r_kanban_card_rec.source_organization_id := l_src_org_id;
1012 r_kanban_card_rec.source_subinventory := l_src_subinv;
1013 r_kanban_card_rec.source_locator_id := l_src_locator;
1014 r_kanban_card_rec.current_replnsh_cycle_id := null;
1015 r_kanban_card_rec.kanban_error_code := null;
1016 r_kanban_card_rec.wip_line_code := null;
1017 r_kanban_card_rec.wip_line_id := l_wip_line_id;
1018 r_kanban_card_rec.attribute_category := null;
1019 r_kanban_card_rec.attribute1 := null;
1020 r_kanban_card_rec.attribute2 := null;
1021 r_kanban_card_rec.attribute3 := null;
1022 r_kanban_card_rec.attribute4 := null;
1023 r_kanban_card_rec.attribute5 := null;
1024 r_kanban_card_rec.attribute6 := null;
1025 r_kanban_card_rec.attribute7 := null;
1026 r_kanban_card_rec.attribute8 := null;
1027 r_kanban_card_rec.attribute9 := null;
1028 r_kanban_card_rec.attribute10 := null;
1029 r_kanban_card_rec.attribute11 := null;
1030 r_kanban_card_rec.attribute12 := null;
1031 r_kanban_card_rec.attribute13 := null;
1032 r_kanban_card_rec.attribute14 := null;
1033 r_kanban_card_rec.attribute15 := null;
1034 r_kanban_card_rec.last_print_date := null;
1035 r_kanban_card_rec.last_update_date := sysdate;
1036 r_kanban_card_rec.last_update_by := fnd_global.user_id;
1037 r_kanban_card_rec.creation_date := sysdate;
1038 r_kanban_card_rec.created_by := fnd_global.user_id;
1039 r_kanban_card_rec.last_update_login := fnd_global.login_id;
1040 r_kanban_card_rec.request_id := null;
1041 r_kanban_card_rec.program_application_id := null;
1042 r_kanban_card_rec.program_id := null;
1043 r_kanban_card_rec.program_update_date := null;
1044 r_kanban_card_rec.replenishment_count := null;
1045 r_kanban_card_rec.replacement_flag := null;
1046 r_kanban_card_rec.max_replenishments := 1;
1047 r_kanban_card_rec.disable_date := null;
1048
1049
1050 FLM_KANBAN_PUB.process_kanban_cards(p_kanban_card_rec => r_kanban_card_rec
1051 ,p_release_kanban_flag => l_release_kanban_flag
1052 ,p_transaction_type => FLM_KANBAN_MASSLOAD.KANBAN_ADD
1053 ,x_ret_status => l_return_status
1054 ,x_err_msg => l_msg_data);
1055
1056 l_Kanban_Card_id := r_kanban_card_rec.kanban_card_id;
1057
1058 ELSE
1059 -- Create Non-replenishable Kanban Card
1060 INV_Kanban_GRP.Create_Non_Replenishable_Card( X_return_status => l_return_status,
1061 X_msg_data => l_msg_data,
1062 X_msg_count => l_msg_count,
1063 X_Kanban_Card_Id => l_Kanban_Card_id,
1064 p_pull_sequence_id => l_comp_table(l_comp_count).pull_sequence_id,
1065 p_kanban_size => l_usage);
1066
1067 END IF;
1068
1069 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS and l_Kanban_Card_Id IS NULL) THEN
1070
1071 Show_Exception_Messages(
1072 p_msg_count => l_msg_count,
1073 p_msg_data => l_msg_data,
1074 p_schedule_number => l_schedule_number,
1075 p_comp_id => l_comp_table(l_comp_count).item_id,
1076 p_organization_id => p_organization_id,
1077 p_error_position => G_Error_Create_Cards);
1078
1079 l_Request_Status := fnd_concurrent.set_completion_status(status => 'WARNING',
1080 message => '');
1081 raise create_exception;
1082
1083 END IF;
1084
1085 IF (G_DEBUG) THEN
1086 log('Created Non-Replenishable Kanban Card, Card Id = '|| l_Kanban_Card_Id);
1087 END IF;
1088
1089 -- Get Need By date for the component
1090 l_need_by_date := Need_By_Date(p_organization_id,
1091 l_line_id,
1092 l_comp_table(l_comp_count).item_id,
1093 l_completion_date,
1094 l_start_date,
1095 l_comp_table(l_comp_count).line_op_seq_id,
1096 l_usage);
1097
1098 -- Replenish the Cards
1099 SELECT wip_entity_id
1100 INTO l_wip_entity_id
1101 FROM wip_flow_schedules
1102 WHERE schedule_number = l_comp_table(l_comp_count).schedule_number
1103 AND organization_id = p_organization_id;
1104
1105 INV_Kanban_GRP.Update_Card_Supply_Status(
1106 x_msg_count => l_msg_count,
1107 x_msg_data => l_msg_data,
1108 x_return_status => l_return_status,
1109 p_api_version_number => l_api_version_number,
1110 p_init_msg_list => NULL,
1111 p_commit => NULL,
1112 p_Kanban_Card_Id => l_Kanban_Card_Id,
1113 p_Supply_Status => G_Supply_Status_Empty,
1114 p_Document_Type => NULL,
1115 p_Document_Header_Id => NULL,
1116 p_Document_Detail_Id => NULL,
1117 p_Need_By_Date => l_need_by_date,
1118 p_Source_Wip_Entity_Id => l_wip_entity_id);
1119
1120 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1121
1122 Show_Exception_Messages(
1123 p_msg_count => l_msg_count,
1124 p_msg_data => l_msg_data,
1125 p_schedule_number => l_schedule_number,
1126 p_comp_id => l_comp_table(l_comp_count).item_id,
1127 p_organization_id => p_organization_id,
1128 p_error_position => G_Error_Replenish_Cards);
1129
1130 l_Request_Status := fnd_concurrent.set_completion_status(status => 'WARNING',
1131 message => '');
1132 raise replenish_exception;
1133
1134 END IF;
1135
1136 IF (G_DEBUG) THEN
1137 log('Replenished Kanban Card, Card Id = '|| l_Kanban_Card_Id);
1138 END IF;
1139
1140 -- Update Auto_Replenish flag in Flow Scheules
1141 Update_Flow_Schedule(l_comp_table(l_comp_count).schedule_number);
1142
1143 IF (G_DEBUG) THEN
1144 log('Updated Auto Replenish flag of Flow schedules, Schedule number = '|| l_comp_table(l_comp_count).schedule_number);
1145 END IF;
1146
1147 l_Card_Count := l_Card_Count + 1;
1148 l_Kanban_Card_Ids(l_Card_Count) := l_Kanban_Card_Id;
1149
1150 l_comp_count := l_comp_count + 1;
1151
1152 l_program_status := 'S';
1153
1154 END LOOP; -- end of while loop
1155
1156 l_program_status := 'S';
1157
1158 EXCEPTION
1159 WHEN create_exception THEN
1160 Rollback to spSchedule;
1161 WHEN replenish_exception THEN
1162 Rollback to spSchedule;
1163 WHEN update_exception THEN
1164 fnd_message.set_name('FLM', 'FLM_AR_ERR_LOCK_SCHEDULE');
1165 log(fnd_message.get);
1166 Rollback to spSchedule;
1167 --Bug 6816497
1168 WHEN update_ar_exception THEN
1169 fnd_message.set_name('FLM', 'FLM_AR_ERR_SCHEDULE_UPDATED');
1170 log(fnd_message.get);
1171 Rollback to spSchedule;
1172
1173 WHEN OTHERS THEN
1174 Rollback to spSchedule;
1175
1176 END; -- end of Begin
1177
1178 END LOOP; -- end of for loop
1179
1180 /*-------------------------------------------------------------------------------------------+
1181 | Print Kanban Cards |
1182 | If Print Kanban Cards option is selected and cards have been created then only print cards|
1183 +-------------------------------------------------------------------------------------------*/
1184 IF (p_print_card = 1 AND l_Kanban_Card_Ids.COUNT > 0) THEN
1185
1186 Print_Kanban_Cards(l_Kanban_Card_Ids);
1187
1188 IF (G_DEBUG) THEN
1189 log('Print request generated for creating Kanban Cards');
1190 END IF;
1191
1192 END IF;
1193
1194 /*-------------------------------------------------------------------------------------------+
1195 | If program_status has not been set to 'S', it signifies that all the flow schedules have |
1196 | failed, i.e. all schedules have components to be replenished and none of the flow schedule|
1197 | succeeded, thus setting the request completion status to ERROR. |
1198 +-------------------------------------------------------------------------------------------*/
1199
1200 IF ( nvl(l_program_status, 'I') = 'F' ) THEN
1201 l_Request_Status := fnd_concurrent.set_completion_status(status => 'ERROR',
1202 message => '');
1203 END IF;
1204
1205 EXCEPTION
1206 WHEN timezone_exception THEN
1207 fnd_message.set_name('FLM', 'FLM_AR_ERR_TIMEZONE');
1208 log(fnd_message.get);
1209 WHEN OTHERS THEN
1210 fnd_message.set_name('FLM', 'FLM_AR_ERR_UNEXP');
1211 log(fnd_message.get);
1212 END create_and_replenish_cards;
1213
1214
1215 END FLM_AUTO_REPLENISHMENT;