[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_PLANNING_UTIL
Source
1 PACKAGE BODY FLM_KANBAN_PLANNING_UTIL AS
2 /* $Header: flmkputb.pls 120.9.12020000.3 2012/08/23 12:32:06 sisankar ship $ */
3
4 G_CALC_KANBAN_SIZE CONSTANT NUMBER := 1;
5 G_CALC_KANBAN_NUMBER CONSTANT NUMBER := 2;
6
7 Function GET_CALCULATED_CARDS(P_PULL_SEQUENCE_ID IN NUMBER,
8 P_CALCULATE_KANBAN_FLAG IN NUMBER,
9 P_KANBAN_SIZE IN NUMBER,
10 P_AVG_DEMAND IN NUMBER,
11 P_ALLOCATION_PERCENT IN NUMBER,
12 P_REPLENISHMENT_LEAD_TIME IN NUMBER,
13 P_SAFETY_STOCK_DAYS IN NUMBER,
14 P_MINIMUM_ORDER_QUANTITY IN NUMBER,
15 P_FIXED_LOT_MULTIPLIER IN NUMBER)
16 RETURN NUMBER IS
17 l_no_of_cards Number;
18 l_card_size Number;
19 l_return_status Varchar2(1);
20 BEGIN
21
22 if p_calculate_kanban_flag <> G_CALC_KANBAN_NUMBER then
23 return null;
24 end if;
25 l_card_size := P_KANBAN_SIZE;
26 MRP_PUB_KANBAN_QTY_CALC.CALCULATE_KANBAN_QUANTITY ( 1,
27 p_pull_sequence_id,
28 p_avg_demand,
29 p_allocation_percent,
30 p_minimum_order_quantity,
31 p_fixed_lot_multiplier,
32 p_safety_stock_days,
33 p_replenishment_lead_time,
34 p_calculate_kanban_flag,
35 l_card_size,
36 l_no_of_cards,
37 l_return_status );
38
39 if l_return_status = 'S' and l_no_of_cards is not null then
40 return l_no_of_cards;
41 end if;
42 if l_card_size is null then
43 l_card_size := P_KANBAN_SIZE;
44 end if;
45 l_no_of_cards := CEIL( ((nvl(p_avg_demand,0) * (nvl(p_allocation_percent,100)/100) * (nvl(p_replenishment_lead_time,1) + nvl(p_safety_stock_days,0)))/nvl(l_card_size,0)) + 1 );
46
47 if (p_minimum_order_quantity is not null and p_minimum_order_quantity > nvl(l_card_size,0)) then
48 if (nvl(l_card_size,0) *(nvl(l_no_of_cards,0) - 1)) < nvl(p_minimum_order_quantity,0) + (nvl(p_avg_demand,0) * (nvl(p_allocation_percent,100)/100) * nvl(p_replenishment_lead_time,1)) then
49 l_no_of_cards := CEIL((nvl(p_minimum_order_quantity,0) + (nvl(p_avg_demand,0) * (nvl(p_allocation_percent,100)/100) * nvl(p_replenishment_lead_time,1)))/ nvl(l_card_size,0)) + 1;
50 end if;
51 end if;
52 return l_no_of_cards;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 return null;
57 END GET_CALCULATED_CARDS;
58
59 Function GET_CALCULATED_SIZE(P_PULL_SEQUENCE_ID IN NUMBER,
60 P_CALCULATE_KANBAN_FLAG IN NUMBER,
61 P_NUMBER_OF_CARDS IN NUMBER,
62 P_AVG_DEMAND IN NUMBER,
63 P_ALLOCATION_PERCENT IN NUMBER,
64 P_REPLENISHMENT_LEAD_TIME IN NUMBER,
65 P_SAFETY_STOCK_DAYS IN NUMBER,
66 P_MINIMUM_ORDER_QUANTITY IN NUMBER,
67 P_FIXED_LOT_MULTIPLIER IN NUMBER)
68 RETURN NUMBER IS
69 l_no_of_cards Number;
70 l_card_size Number;
71 l_return_status Varchar2(1);
72 BEGIN
73 if p_calculate_kanban_flag <> G_CALC_KANBAN_SIZE then
74 return null;
75 end if;
76 l_no_of_cards := p_number_of_cards;
77 MRP_PUB_KANBAN_QTY_CALC.CALCULATE_KANBAN_QUANTITY ( 1,
78 p_pull_sequence_id,
79 p_avg_demand,
80 p_allocation_percent,
81 p_minimum_order_quantity,
82 p_fixed_lot_multiplier,
83 p_safety_stock_days,
84 p_replenishment_lead_time,
85 p_calculate_kanban_flag,
86 l_card_size,
87 l_no_of_cards,
88 l_return_status );
89
90 if l_return_status = 'S' and l_card_size is not null then
91 return l_card_size;
92 end if;
93 if l_no_of_cards is null then
94 l_no_of_cards := p_number_of_cards;
95 end if;
96 if l_no_of_cards = 1 then
97 l_no_of_cards := 2;
98 end if;
99 l_card_size := CEIL((nvl(p_avg_demand,0)*(nvl(p_allocation_percent,100)/100)*(nvl(p_replenishment_lead_time,1) + nvl(p_safety_stock_days, 0)))/(l_no_of_cards - 1));
100
101 if p_minimum_order_quantity is not null then
102 if nvl(l_card_size,0) < p_minimum_order_quantity then
103 l_card_size := p_minimum_order_quantity;
104 end if;
105 end if;
106 if p_fixed_lot_multiplier is not null then
107 if nvl(l_card_size,0) < p_fixed_lot_multiplier then
108 l_card_size := p_fixed_lot_multiplier;
109 elsif MOD(nvl(l_card_size,0), p_fixed_lot_multiplier) > 0 then
110 l_card_size := nvl(l_card_size,0) + ( p_fixed_lot_multiplier - MOD(nvl(l_card_size,0), p_fixed_lot_multiplier));
111 end if;
112 end if;
113 return l_card_size;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 return null;
118 END GET_CALCULATED_SIZE;
119
120 Function get_average_demand(p_org_id IN Number,
121 p_kanban_plan_id IN Number,
122 p_item_id IN Number,
123 p_subinv IN varchar2,
124 p_locator IN Number)
125 RETURN NUMBER
126 IS
127 l_avg_demand Number;
128 l_interval Number;
129 Begin
130
131 l_interval := 30; -- Can later be replaced with a parameter. Default is 30 days
132 select sum(demand_quantity)/count(distinct demand_date)
133 into l_avg_demand
134 from mrp_kanban_demand
135 where kanban_plan_id = p_kanban_plan_id
136 and organization_id = p_org_id
137 and inventory_item_id = p_item_id
138 and subinventory = p_subinv
139 and nvl(locator_id,-10) = nvl(p_locator,-10)
140 and demand_quantity is not null
141 and trunc(demand_date) between trunc(sysdate) and trunc(sysdate + l_interval);
142
143 return l_avg_demand;
144 exception
145 when others then
146 return null;
147 End get_average_demand;
148
149 procedure generate_plan_data(p_pull_sequence_id IN Number,
150 p_org_id IN Number,
151 p_kanban_plan_id IN Number,
152 p_item_id IN Number,
153 p_subinv IN Number,
154 p_locator IN Number,
155 p_safety_stock IN Number,
156 p_kanban_size IN Number,
157 p_kanban_cards IN Number,
158 p_avg_demand IN Number)
159 Is
160 Begin
161 /* This procedure is not used anywhere .*/
162 null;
163 End generate_plan_data;
164
165 -- This procedure creates horizontal plan for multiple pull sequences comma separated.
166 procedure generate_plan_data(p_pull_seq_ids IN VARCHAR2)
167 Is
168 Begin
169 /* This procedure is not used anywhere .*/
170 null;
171 End generate_plan_data;
172
173 procedure set_flexfields_prompt
174 is
175 cursor flexfield_defn is
176 select application_column_name col_name,form_left_prompt prompt from FND_DESCR_FLEX_COL_USAGE_TL
177 where descriptive_flexfield_name = 'MTL_KANBAN_PULL_SEQUENCES'
178 and language = userenv('LANG')
179 and descriptive_flex_context_code = 'Global Data Elements';
180 Begin
181
182 if p_attr1_prompt is not null or
183 p_attr2_prompt is not null or
184 p_attr3_prompt is not null or
185 p_attr4_prompt is not null or
186 p_attr5_prompt is not null or
187 p_attr6_prompt is not null or
188 p_attr7_prompt is not null or
189 p_attr8_prompt is not null or
190 p_attr9_prompt is not null or
191 p_attr10_prompt is not null or
192 p_attr11_prompt is not null or
193 p_attr12_prompt is not null or
194 p_attr13_prompt is not null or
195 p_attr14_prompt is not null or
196 p_attr15_prompt is not null then
197 null;
198 else
199 for definition in flexfield_defn
200 loop
201 if definition.col_name = 'ATTRIBUTE1' then
202 p_attr1_prompt := definition.prompt;
203 elsif definition.col_name = 'ATTRIBUTE2' then
204 p_attr2_prompt := definition.prompt;
205 elsif definition.col_name = 'ATTRIBUTE3' then
206 p_attr3_prompt := definition.prompt;
207 elsif definition.col_name = 'ATTRIBUTE4' then
208 p_attr4_prompt := definition.prompt;
209 elsif definition.col_name = 'ATTRIBUTE5' then
210 p_attr5_prompt := definition.prompt;
211 elsif definition.col_name = 'ATTRIBUTE6' then
212 p_attr6_prompt := definition.prompt;
213 elsif definition.col_name = 'ATTRIBUTE7' then
214 p_attr7_prompt := definition.prompt;
215 elsif definition.col_name = 'ATTRIBUTE8' then
216 p_attr8_prompt := definition.prompt;
217 elsif definition.col_name = 'ATTRIBUTE9' then
218 p_attr9_prompt := definition.prompt;
219 elsif definition.col_name = 'ATTRIBUTE10' then
220 p_attr10_prompt := definition.prompt;
221 elsif definition.col_name = 'ATTRIBUTE11' then
222 p_attr11_prompt := definition.prompt;
223 elsif definition.col_name = 'ATTRIBUTE12' then
224 p_attr12_prompt := definition.prompt;
225 elsif definition.col_name = 'ATTRIBUTE13' then
226 p_attr13_prompt := definition.prompt;
227 elsif definition.col_name = 'ATTRIBUTE14' then
228 p_attr14_prompt := definition.prompt;
229 elsif definition.col_name = 'ATTRIBUTE15' then
230 p_attr15_prompt := definition.prompt;
231 end if;
232 end loop;
233 end if;
234 End set_flexfields_prompt;
235
236 FUNCTION GET_ATTR_1_PROMPT
237 RETURN VARCHAR2
238 is
239 begin
240 return p_attr1_prompt;
241 End GET_ATTR_1_PROMPT;
242
243 FUNCTION GET_ATTR_2_PROMPT
244 RETURN VARCHAR2
245 is
246 begin
247 return p_attr2_prompt;
248 End GET_ATTR_2_PROMPT;
249
250 FUNCTION GET_ATTR_3_PROMPT
251 RETURN VARCHAR2
252 is
253 begin
254 return p_attr3_prompt;
255 End GET_ATTR_3_PROMPT;
256
257 FUNCTION GET_ATTR_4_PROMPT
258 RETURN VARCHAR2
259 is
260 begin
261 return p_attr4_prompt;
262 End GET_ATTR_4_PROMPT;
263
264 FUNCTION GET_ATTR_5_PROMPT
265 RETURN VARCHAR2
266 is
267 begin
268 return p_attr5_prompt;
269 End GET_ATTR_5_PROMPT;
270
271 FUNCTION GET_ATTR_6_PROMPT
272 RETURN VARCHAR2
273 is
274 begin
275 return p_attr6_prompt;
276 End GET_ATTR_6_PROMPT;
277
278 FUNCTION GET_ATTR_7_PROMPT
279 RETURN VARCHAR2
280 is
281 begin
282 return p_attr7_prompt;
283 End GET_ATTR_7_PROMPT;
284
285 FUNCTION GET_ATTR_8_PROMPT
286 RETURN VARCHAR2
287 is
288 begin
289 return p_attr8_prompt;
290 End GET_ATTR_8_PROMPT;
291
292 FUNCTION GET_ATTR_9_PROMPT
293 RETURN VARCHAR2
294 is
295 begin
296 return p_attr9_prompt;
297 End GET_ATTR_9_PROMPT;
298
299 FUNCTION GET_ATTR_10_PROMPT
300 RETURN VARCHAR2
301 is
302 begin
303 return p_attr10_prompt;
304 End GET_ATTR_10_PROMPT;
305
306 FUNCTION GET_ATTR_11_PROMPT
307 RETURN VARCHAR2
308 is
309 begin
310 return p_attr11_prompt;
311 End GET_ATTR_11_PROMPT;
312
313 FUNCTION GET_ATTR_12_PROMPT
314 RETURN VARCHAR2
315 is
316 begin
317 return p_attr12_prompt;
318 End GET_ATTR_12_PROMPT;
319
320 FUNCTION GET_ATTR_13_PROMPT
321 RETURN VARCHAR2
322 is
323 begin
324 return p_attr13_prompt;
325 End GET_ATTR_13_PROMPT;
326
327 FUNCTION GET_ATTR_14_PROMPT
328 RETURN VARCHAR2
329 is
330 begin
331 return p_attr14_prompt;
332 End GET_ATTR_14_PROMPT;
333
334 FUNCTION GET_ATTR_15_PROMPT
335 RETURN VARCHAR2
336 is
337 begin
338 return p_attr15_prompt;
339 End GET_ATTR_15_PROMPT;
340
341 Function set_dff
342 return number
343 is
344 begin
345 set_flexfields_prompt;
346 return 1;
347 exception
348 when others then
349 return 0;
350 End set_dff;
351
352 procedure gen_sys_plan_pull_sequence(p_pull_sequence_id IN Number,
353 x_pull_sequence_id out nocopy number )
354 is
355 l_pull_seq_id Number;
356 Begin
357 select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL into l_pull_seq_id from dual;
358
359 INSERT INTO MTL_KANBAN_PULL_SEQUENCES
360 (Pull_sequence_id,
361 Inventory_item_id,
362 Organization_id,
363 Subinventory_name,
364 Kanban_Plan_id,
365 Source_type,
366 Last_Update_Date,
367 Last_Updated_By,
368 Creation_Date,
369 Created_By,
370 Last_Update_Login,
371 Locator_id,
372 Calculate_Kanban_flag,
373 Kanban_size,
374 Number_of_cards,
375 Minimum_order_quantity,
376 Allocation_Percent,
377 Replenishment_lead_time,
378 Fixed_Lot_multiplier,
379 Safety_Stock_Days,
380 Attribute_Category,
381 Attribute1,
382 Attribute2,
383 Attribute3,
384 Attribute4,
385 Attribute5,
386 Attribute6,
387 Attribute7,
388 Attribute8,
389 Attribute9,
390 Attribute10,
391 Attribute11,
392 Attribute12,
393 Attribute13,
394 Attribute14,
395 Attribute15,
396 FUTURE_NO_OF_CARDS,
397 FUTURE_CARD_SIZE,
398 PLANNING_EFFECTIVITY)
399 select l_pull_seq_id,
400 Inventory_item_id,
401 Organization_id,
402 Subinventory_name,
403 0,
404 Source_type,
405 sysdate,
406 fnd_global.user_id,
407 sysdate,
408 fnd_global.user_id,
409 fnd_global.login_id,
410 Locator_id,
411 Calculate_Kanban_flag,
412 Kanban_size,
413 Number_of_cards,
414 Minimum_order_quantity,
415 Allocation_Percent,
416 Replenishment_lead_time,
417 Fixed_Lot_multiplier,
418 Safety_Stock_Days,
419 Attribute_Category,
420 Attribute1,
421 Attribute2,
422 Attribute3,
423 Attribute4,
424 Attribute5,
425 Attribute6,
426 Attribute7,
427 Attribute8,
428 Attribute9,
429 Attribute10,
430 Attribute11,
431 Attribute12,
432 Attribute13,
433 Attribute14,
434 Attribute15,
435 FUTURE_NO_OF_CARDS,
436 FUTURE_CARD_SIZE,
437 PLANNING_EFFECTIVITY
438 from MTL_KANBAN_PULL_SEQUENCES
439 where pull_sequence_id = p_pull_sequence_id;
440
441 x_pull_sequence_id := l_pull_seq_id;
442 Exception
443 when others then
444 null;
445 End gen_sys_plan_pull_sequence;
446
447 procedure upd_production(p_pull_sequence_id IN Number)
448 is
449
450 Begin
451 null;
452 End upd_production;
453
454 procedure replan(p_request_id IN Number)
455 is
456
457 type planning_temp_rec IS record(PLANNING_TEMP_ID number,
458 PULL_SEQUENCE_ID number,
459 BEGINNING_ONHAND number,
460 EXPECTED_RECEIPTS number,
461 DAILY_DEMAND number,
462 ADJUSTED_DAILY_DEMAND number,
463 AVAILABLE_ONHAND number,
464 ITEM_UNIT_COST number,
465 INVENTORY_VALUE number,
466 CARDS_ONHAND number,
467 SAFETY_STOCK_DAYS number,
468 AVG_DEPENDENT_DEMAND number,
469 SAFETY_STOCK_TARGET number,
470 ADJUSTED_SAFETY_STOCK_DAYS number,
471 REPLENISHMENT_LEAD_TIME number,
472 KANBAN_SIZE number,
473 PLAN_DATE date,
474 CARDS_REPLENISHED number);
475
476
477 type planning_temp_tbl is table of planning_temp_rec index by binary_integer;
478 v_planning_temp_tbl planning_temp_tbl;
479
480 type t_exp_date is table of date index by binary_integer;
481 type t_exp_repl_qty is table of number index by binary_integer;
482 type t_repl_no_of_cards is table of number index by binary_integer;
483
484 v_exp_pull_seq_id t_exp_repl_qty;
485 v_exp_date t_exp_date;
486 v_exp_repl_qty t_exp_repl_qty;
487 v_repl_no_of_cards t_repl_no_of_cards;
488 v_repl_date t_exp_date;
489
490 type t_qty_temp is table of number index by varchar2(25);
491 v_expected_receipts t_qty_temp;
492 v_cards_replenished t_qty_temp;
493
494 l_counter number;
495 l_updated_pullseq number;
496 l_prev_pull_seq number;
497 l_kanban_size number;
498 l_no_of_cards number;
499 l_kanban_plan_id number;
500 l_init_onhand number;
501 l_exp_receipt number;
502 l_plan_start_date Date;
503 l_plan_end_date Date;
504 l_new_counter number;
505 l_temp number;
506
507 l_temp_onhand Number := 0;
508 l_temp_receipts Number := 0;
509 l_already_receipt Number :=0;
510 l_rep_counter Number;
511 l_org_id Number;
512 l_new_date Date;
513 Begin
514
515 update MTL_KANBAN_PULL_SEQUENCES
516 set Number_of_cards = decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,
517 KANBAN_SIZE,AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,
518 MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),number_of_cards ),
519 Kanban_size = decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,
520 AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size ),
521 FUTURE_NO_OF_CARDS = decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,
522 AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards ),
523 FUTURE_CARD_SIZE = decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,
524 NUMBER_OF_CARDS,AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,
525 FIXED_LOT_MULTIPLIER),Kanban_size ),
526 NO_OF_TEMP_CARDS = null,
527 NO_OF_TEMP_CYCLES = null,
528 TEMP_EXPIRY_DATE = null
529 where UPDATED_FLAG = p_request_id;
530
531 select min(HP.plan_date),max(HP.plan_date), min(HP.organization_id)
532 into l_plan_start_date,l_plan_end_date, l_org_id
533 from FLM_EKB_HORIZONTAL_PLAN_TEMP HP,MTL_KANBAN_PULL_SEQUENCES PS
534 where HP.PULL_SEQUENCE_ID = PS.PULL_SEQUENCE_ID
535 and PS.UPDATED_FLAG = p_request_id;
536
537 v_exp_date.delete;
538 v_exp_repl_qty.delete;
539 v_repl_no_of_cards.delete;
540 v_exp_pull_seq_id.delete;
541 v_repl_date.delete;
542
543 select pull_seq,
544 expected_date,
545 replenished_date,
546 sum(qty) expected_qty,
547 count(distinct card) replenished_card
548 bulk collect into v_exp_pull_seq_id,
549 v_exp_date,
550 v_repl_date,
551 v_exp_repl_qty,
552 v_repl_no_of_cards
553 from (select plan.pull_sequence_id pull_seq,
554 mkc.kanban_card_id card,
555 mkc.kanban_size qty,
556 min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,
557 case when trunc(mkca.creation_date+mkps.replenishment_lead_time) < l_plan_start_date then l_plan_start_date
558 else trunc(mkca.creation_date+mkps.replenishment_lead_time) end )) expected_date,
559 min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,trunc(mkca.creation_date))) replenished_date
560 from mtl_kanban_card_activity mkca,
561 mtl_kanban_cards mkc,
562 mtl_kanban_pull_sequences mkps,
563 mtl_kanban_pull_sequences plan
564 where mkps.organization_id = plan.organization_id
565 and mkps.inventory_item_id = plan.inventory_item_id
566 and mkps.subinventory_name = plan.subinventory_name
567 and nvl(mkps.locator_id,-100) = nvl(plan.locator_id,-100)
568 and mkps.kanban_plan_id = -1
569 and mkps.pull_sequence_id = mkc.pull_sequence_id
570 and trunc(mkc.supply_status) in (4,5,6,7)
571 and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
572 and mkc.KANBAN_CARD_ID = mkca.kanban_card_id
573 and plan.UPDATED_FLAG = p_request_id
574 group by plan.pull_sequence_id,mkc.kanban_card_id,mkc.kanban_size)
575 where expected_date between l_plan_start_date and l_plan_end_date
576 or replenished_date between l_plan_start_date and l_plan_end_date
577 group by pull_seq,expected_date,replenished_date;
578
579 v_expected_receipts.delete;
580 v_cards_replenished.delete;
581
582 l_temp := v_exp_pull_seq_id.first;
583 if l_temp is not null then
584 while l_temp <= v_exp_pull_seq_id.last loop
585 v_expected_receipts(to_char(v_exp_pull_seq_id(l_temp)||'-:-'||v_exp_date(l_temp))) := v_exp_repl_qty(l_temp);
586 v_cards_replenished(to_char(v_exp_pull_seq_id(l_temp)||'-:-'||v_repl_date(l_temp))) := v_repl_no_of_cards(l_temp);
587 l_temp := v_exp_pull_seq_id.next(l_temp);
588 end loop;
589 end if;
590
591 select HP.PLANNING_TEMP_ID,HP.PULL_SEQUENCE_ID,HP.BEGINNING_ONHAND,0 EXPECTED_RECEIPTS,HP.DAILY_DEMAND,
592 HP.ADJUSTED_DAILY_DEMAND,HP.AVAILABLE_ONHAND,HP.ITEM_UNIT_COST,HP.INVENTORY_VALUE,HP.CARDS_ONHAND,
593 PS.SAFETY_STOCK_DAYS,PS.AVG_DEPENDENT_DEMAND ,HP.SAFETY_STOCK_TARGET,HP.ADJUSTED_SAFETY_STOCK_DAYS,
594 nvl(ceil(PS.REPLENISHMENT_LEAD_TIME),0) REPLENISHMENT_LEAD_TIME,decode( nvl(PS.KANBAN_SIZE,0),0,1,PS.KANBAN_SIZE),
595 trunc(HP.PLAN_DATE),0 CARDS_REPLENISHED
596 bulk collect into v_planning_temp_tbl
597 from FLM_EKB_HORIZONTAL_PLAN_TEMP HP,
598 MTL_KANBAN_PULL_SEQUENCES PS
599 where HP.PULL_SEQUENCE_ID = PS.PULL_SEQUENCE_ID
600 and PS.UPDATED_FLAG = p_request_id
601 order by HP.PULL_SEQUENCE_ID,HP.plan_date;
602
603 l_counter := v_planning_temp_tbl.first;
604 l_prev_pull_seq := null;
605 if l_counter is not null then
606 while l_counter <= v_planning_temp_tbl.last loop
607 if nvl(l_prev_pull_seq,0) <> v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID then
608 select decode(nvl(kanban_size,0),0,1,kanban_size),decode(nvl(number_of_cards,0),0,1,number_of_cards),kanban_plan_id
609 into l_kanban_size,l_no_of_cards,l_kanban_plan_id
610 from mtl_kanban_pull_sequences mkps
611 where PULL_SEQUENCE_ID = v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID;
612 end if;
613 if nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,-1) <> nvl(v_planning_temp_tbl(l_counter).AVG_DEPENDENT_DEMAND,-2) and
614 nvl(v_planning_temp_tbl(l_counter).AVG_DEPENDENT_DEMAND,-1) <> 0 and l_kanban_plan_id = 0 then
615 v_planning_temp_tbl(l_counter).DAILY_DEMAND := nvl(v_planning_temp_tbl(l_counter).AVG_DEPENDENT_DEMAND,0);
616 end if;
617 v_planning_temp_tbl(l_counter).SAFETY_STOCK_TARGET := nvl(v_planning_temp_tbl(l_counter).ADJUSTED_SAFETY_STOCK_DAYS,
618 nvl(v_planning_temp_tbl(l_counter).SAFETY_STOCK_DAYS,0))*
619 nvl(nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,
620 v_planning_temp_tbl(l_counter).DAILY_DEMAND),0);
621
622 if v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID = l_prev_pull_seq then
623 v_planning_temp_tbl(l_counter).BEGINNING_ONHAND := nvl(v_planning_temp_tbl(l_counter-1).AVAILABLE_ONHAND,0);
624 else
625 l_temp_onhand := nvl(l_kanban_size,0)*nvl(l_no_of_cards,0);
626 if l_temp_onhand = 0 then
627 l_temp_onhand := v_planning_temp_tbl(l_counter).BEGINNING_ONHAND;
628 end if;
629 l_init_onhand := v_planning_temp_tbl(l_counter).BEGINNING_ONHAND;
630 end if;
631
632 if v_expected_receipts.exists(to_char(v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID||'-:-'||v_planning_temp_tbl(l_counter).PLAN_DATE)) then
633 v_planning_temp_tbl(l_counter).expected_receipts := nvl(v_planning_temp_tbl(l_counter).expected_receipts,0) +
634 nvl(v_expected_receipts(to_char(v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID||'-:-'||v_planning_temp_tbl(l_counter).PLAN_DATE)),0) ;
635 l_temp_receipts := nvl(v_expected_receipts(to_char(v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID||'-:-'||v_planning_temp_tbl(l_counter).PLAN_DATE)),0) ;
636 else
637 v_planning_temp_tbl(l_counter).expected_receipts := nvl(v_planning_temp_tbl(l_counter).expected_receipts,0);
638 l_temp_receipts := 0;
639 end if;
640 if v_cards_replenished.exists(to_char(v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID||'-:-'||v_planning_temp_tbl(l_counter).PLAN_DATE)) then
641 v_planning_temp_tbl(l_counter).cards_replenished := nvl(v_planning_temp_tbl(l_counter).cards_replenished,0) +
642 nvl(v_cards_replenished(to_char(v_planning_temp_tbl(l_counter).Pull_Sequence_Id||'-:-'||v_planning_temp_tbl(l_counter).PLAN_DATE)),0) ;
643 else
644 v_planning_temp_tbl(l_counter).cards_replenished := nvl(v_planning_temp_tbl(l_counter).cards_replenished,0);
645 end if;
646
647 if ((l_init_onhand-(nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,0)))+
648 l_temp_receipts) < l_temp_onhand and
649 ((l_temp_onhand - (l_init_onhand-(nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,0)))
650 + l_temp_receipts)) >= nvl(v_planning_temp_tbl(l_counter).KANBAN_SIZE,0))) then
651 l_exp_receipt := nvl(trunc((l_temp_onhand - (l_init_onhand-(nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,0)))
652 + l_temp_receipts))/l_kanban_size)*l_kanban_size,0);
653
654 if (l_exp_receipt > l_temp_onhand) then
655 l_exp_receipt := l_temp_onhand;
656 end if;
657
658 l_already_receipt := 0;
659 if l_exp_receipt > 0 then
660 l_rep_counter := 1;
661 while l_rep_counter < nvl(v_planning_temp_tbl(l_counter).REPLENISHMENT_LEAD_TIME,0) loop
662 if v_planning_temp_tbl.exists(l_counter-l_rep_counter) and v_planning_temp_tbl(l_counter-l_rep_counter).PULL_SEQUENCE_ID = v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID then
663 l_already_receipt := l_already_receipt + (v_planning_temp_tbl(l_counter-l_rep_counter).cards_replenished*l_kanban_size);
664 else
665 exit;
666 end if;
667 l_rep_counter := l_rep_counter+1;
668 end loop;
669 end if;
670 if (l_exp_receipt > (l_temp_onhand-l_already_receipt)) then
671 l_exp_receipt := l_temp_onhand-l_already_receipt;
672 end if;
673 l_new_date := v_planning_temp_tbl(l_counter).plan_date;
674 for i in 1..nvl(v_planning_temp_tbl(l_counter).REPLENISHMENT_LEAD_TIME,0) loop
675 l_new_date := mrp_calendar.NEXT_WORK_DAY(l_org_id,1,l_new_date+1);
676 end loop;
677 if l_new_date <= l_plan_end_date then
678 l_new_counter := l_counter + v_planning_temp_tbl(l_counter).REPLENISHMENT_LEAD_TIME;
679 v_planning_temp_tbl(l_new_counter).EXPECTED_RECEIPTS := l_exp_receipt+nvl(v_planning_temp_tbl(l_new_counter).EXPECTED_RECEIPTS,0);
680 end if;
681 v_planning_temp_tbl(l_counter).CARDS_REPLENISHED := ceil(nvl(l_exp_receipt/l_kanban_size,0)) +
682 nvl(v_planning_temp_tbl(l_counter).CARDS_REPLENISHED,0);
683 end if;
684 v_planning_temp_tbl(l_counter).AVAILABLE_ONHAND := nvl(v_planning_temp_tbl(l_counter).BEGINNING_ONHAND,0) +
685 nvl(v_planning_temp_tbl(l_counter).EXPECTED_RECEIPTS,0) -
686 nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,0));
687 v_planning_temp_tbl(l_counter).INVENTORY_VALUE := nvl(v_planning_temp_tbl(l_counter).Item_unit_cost,0) * nvl(v_planning_temp_tbl(l_counter).AVAILABLE_ONHAND,0);
688 l_init_onhand := l_init_onhand+l_temp_receipts - nvl(v_planning_temp_tbl(l_counter).ADJUSTED_DAILY_DEMAND,nvl(v_planning_temp_tbl(l_counter).DAILY_DEMAND,0)) +
689 nvl((l_kanban_size*v_planning_temp_tbl(l_counter).CARDS_REPLENISHED),0);
690 if l_kanban_size = 0 then
691 l_kanban_size := ceil( nvl(v_planning_temp_tbl(l_counter).AVAILABLE_ONHAND,0)/l_no_of_cards);
692 end if;
693 v_planning_temp_tbl(l_counter).CARDS_ONHAND := ceil(nvl(v_planning_temp_tbl(l_counter).AVAILABLE_ONHAND,0)/nvl(l_kanban_size,1));
694 if v_planning_temp_tbl(l_counter).CARDS_ONHAND < 0 then
695 v_planning_temp_tbl(l_counter).CARDS_ONHAND := 0;
696 end if;
697 l_prev_pull_seq := v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID;
698 l_counter := v_planning_temp_tbl.next(l_counter);
699 end loop;
700 forall cntr in v_planning_temp_tbl.first..v_planning_temp_tbl.last
701 update FLM_EKB_HORIZONTAL_PLAN_TEMP
702 set BEGINNING_ONHAND = v_planning_temp_tbl(cntr).BEGINNING_ONHAND,
703 AVAILABLE_ONHAND = v_planning_temp_tbl(cntr).AVAILABLE_ONHAND,
704 INVENTORY_VALUE = v_planning_temp_tbl(cntr).INVENTORY_VALUE,
705 CARDS_ONHAND = v_planning_temp_tbl(cntr).CARDS_ONHAND,
706 SAFETY_STOCK_DAYS = v_planning_temp_tbl(cntr).SAFETY_STOCK_DAYS,
707 DAILY_DEMAND = v_planning_temp_tbl(cntr).DAILY_DEMAND,
708 SAFETY_STOCK_TARGET = v_planning_temp_tbl(cntr).SAFETY_STOCK_TARGET,
709 EXPECTED_RECEIPTS = v_planning_temp_tbl(cntr).EXPECTED_RECEIPTS,
710 CARDS_REPLENISHED = v_planning_temp_tbl(cntr).CARDS_REPLENISHED
711 where PLANNING_TEMP_ID = v_planning_temp_tbl(cntr).PLANNING_TEMP_ID;
712 end if;
713 commit;
714 Exception
715 when others then
716 null;
717 End replan;
718
719 procedure regen_sys_plan_pullseq(p_pull_sequence_id IN Number,
720 p_org_id IN Number,
721 p_item_id IN NUMBER,
722 p_subinv IN VARCHAR2,
723 p_locator IN NUMBER,
724 p_kanban_plan IN NUMBER,
725 p_set_prmopts IN VARCHAR2)
726 is
727 l_pullseq_id Number;
728 l_kanban_plan Number;
729 l_start_date date;
730 l_end_date date;
731 Begin
732
733 if p_kanban_plan = -1 then
734
735 delete from MTL_KANBAN_PULL_SEQUENCES
736 where inventory_item_id = p_item_id and
737 organization_id = p_org_id and
738 Subinventory_name = p_subinv and
739 Locator_id = p_locator and
740 Kanban_Plan_id = 0;
741
742 gen_sys_plan_pull_sequence(p_pull_sequence_id => p_pull_sequence_id,
743 x_pull_sequence_id => l_pullseq_id);
744
745 l_kanban_plan := 0;
746 l_start_date := trunc(sysdate);
747 l_end_date := l_start_date+30;
748 else
749 l_pullseq_id := p_pull_sequence_id;
750 l_kanban_plan := p_kanban_plan;
751 select nvl(trunc(plan_start_date),trunc(sysdate)), nvl(trunc(plan_cutoff_date),nvl(trunc(plan_start_date),trunc(sysdate))+30)
752 into l_start_date,l_end_date
753 from mrp_kanban_plans where kanban_plan_id = p_kanban_plan;
754 end if;
755
756 if p_set_prmopts = 'Y' then
757 delete from FLM_EKB_HORIZONTAL_PLAN_TEMP where Kanban_Plan_id = l_kanban_plan;
758 set_flexfields_prompt;
759 end if;
760 /*
761 generate_plan_data( p_pull_sequence_id => l_pullseq_id,
762 p_org_id => p_org_id,
763 p_kanban_plan_id => l_kanban_plan,
764 p_item_id => p_item_id,
765 p_subinv => p_subinv,
766 p_locator => p_locator,
767 p_start_date => l_start_date,
768 P_end_date => l_end_date);
769 */
770 Exception
771 when others then
772 null;
773 End regen_sys_plan_pullseq;
774
775
776 procedure process_interface_records(retcode out nocopy number,
777 errbuf out nocopy varchar2,
778 p_organization_id in number)
779 is
780
781
782 g_user_id number;
783 g_user_login_id number;
784 g_program_appl_id number;
785 g_request_id number;
786 g_program_id number;
787 g_logLevel number;
788
789 l_stmt_num number;
790 l_org_code varchar2(3);
791 l_conc_status boolean;
792 l_return_status number;
793
794 v_flm_kanban_demand_intf t_flm_kanban_demand_intf;
795
796 cursor running_intf_records(org_id Number) is
797 select *
798 from FLM_EKB_DEMAND_INTERFACE
799 where process_status = running
800 and organization_id = org_id;
801
802 e_null_org_id exception;
803
804 begin
805
806 l_stmt_num := 10;
807
808 if p_organization_id is null then
809 raise e_null_org_id;
810 end if;
811
812 select organization_code
813 into l_org_code
814 from mtl_parameters
815 where organization_id = p_organization_id;
816
817 g_user_id := fnd_global.user_id;
818 g_user_login_id := fnd_global.login_id;
819 g_program_appl_id := fnd_global.prog_appl_id;
820 g_request_id := fnd_global.conc_request_id;
821 g_program_id := fnd_global.conc_program_id;
822 g_logLevel := FND_LOG.g_current_runtime_level;
823
824 l_stmt_num := 20;
825
826 update FLM_EKB_DEMAND_INTERFACE FEDI
827 set process_status = running,
828 organization_id = nvl(organization_id,p_organization_id),
829 request_id = fnd_global.conc_request_id,
830 program_id = fnd_global.conc_program_id,
831 program_application_id = fnd_global.prog_appl_id,
832 program_update_date = sysdate,
833 last_updated_by = fnd_global.user_id,
834 last_update_date = sysdate,
835 LAST_UPDATED_LOGIN = fnd_global.login_id
836 where
837 process_status = pending
838 and (organization_id = p_organization_id or
839 organization_code = (select mp.organization_code
840 from mtl_parameters mp
841 where mp.organization_id = p_organization_id) or
842 exists (select 1 from mtl_kanban_pull_sequences mkps
843 where mkps.organization_id = p_organization_id and
844 mkps.pull_sequence_id = fedi.pull_sequence_id and rownum = 1 ));
845
846 open running_intf_records(p_organization_id);
847 fetch running_intf_records bulk collect into v_flm_kanban_demand_intf;
848 close running_intf_records;
849
850 l_stmt_num := 70;
851
852
853 process( p_flm_kanban_demand_int_tbl => v_flm_kanban_demand_intf,
854 x_ret_status => l_return_status);
855
856 l_stmt_num := 80;
857
858 commit;
859
860 if l_return_status = 1 then
861 retcode :=1;
862 errbuf := 'The Import program successfully imported all records';
863 fnd_file.put_line(fnd_file.log,errbuf);
864 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',errbuf);
865 else
866 retcode := 1;
867 errbuf := 'The Import program marked at least one row as errored';
868 fnd_file.put_line(fnd_file.log,errbuf);
869 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
870 end if;
871
872 exception
873 when e_null_org_id then
874 retcode := -1;
875 errbuf := 'Organization parameter cannot be null';
876 fnd_file.put_line(fnd_file.log,errbuf);
877 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
878 when others then
879 retcode := -1;
880 errbuf := 'Errors encountered in interface txn, please check the log file.';
881 fnd_file.put_line(fnd_file.log,errbuf);
882 fnd_file.put_line(fnd_file.log,sqlerrm(sqlcode));
883 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
884 end process_interface_records;
885
886 function get_item_id(p_org_id in number,
887 p_item_segments in varchar2)
888 return number is
889 l_item_id number;
890 begin
891 select inventory_item_id
892 into l_item_id
893 from mtl_system_items_kfv
894 where organization_id = p_org_id
895 and concatenated_segments = p_item_segments;
896 return l_item_id;
897 exception
898 when others then
899 return null;
900 end get_item_id;
901
902 function get_locator_id(p_org_id in number,
903 p_locator_segments in varchar2)
904 return number is
905 l_locator_id number;
906 begin
907 select inventory_location_id
908 into l_locator_id
909 from mtl_item_locations_kfv
910 where organization_id = p_org_id
911 and concatenated_segments = p_locator_segments;
912 return l_locator_id;
913 exception
914 when others then
915 return null;
916 end get_locator_id;
917
918 procedure process(p_flm_kanban_demand_int_tbl in t_flm_kanban_demand_intf,
919 x_ret_status out nocopy number)
920 is
921
922 l_flm_kanban_demand_intf t_flm_kanban_demand_intf;
923
924 type t_mrp_kanban_demand is table of mrp_kanban_demand%rowtype index by binary_integer;
925 v_flm_kanban_demand t_mrp_kanban_demand;
926
927 type t_interface_id is table of FLM_EKB_DEMAND_INTERFACE.interface_id%type index by binary_integer;
928 type t_process_status is table of FLM_EKB_DEMAND_INTERFACE.process_status%type index by binary_integer;
929 type t_error is table of FLM_EKB_DEMAND_INTERFACE.error%type index by binary_integer;
930
931 v_interface_id t_interface_id;
932 v_process_status t_process_status;
933 v_error t_error;
934
935 pop_counter number :=1;
936 l_temp number;
937 ins_counter number;
938 l_counter number;
939 l_error_count number :=0;
940 begin
941
942 l_flm_kanban_demand_intf := p_flm_kanban_demand_int_tbl;
943 l_counter := l_flm_kanban_demand_intf.first;
944 ins_counter := 1;
945 while l_counter is not null loop
946 if l_flm_kanban_demand_intf(l_counter).PULL_SEQUENCE_ID is not null then
947 begin
948 select organization_id,inventory_item_id,subinventory_name,locator_id
949 into l_flm_kanban_demand_intf(l_counter).organization_id,
950 l_flm_kanban_demand_intf(l_counter).inventory_item_id,
951 l_flm_kanban_demand_intf(l_counter).subinventory,
952 l_flm_kanban_demand_intf(l_counter).locator_id
953 from mtl_kanban_pull_sequences where pull_sequence_id = l_flm_kanban_demand_intf(l_counter).PULL_SEQUENCE_ID
954 and organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
955 and kanban_plan_id = -1;
956 exception
957 when others then
958 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
959 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
960 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pull_Sequence_Id');
961 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
962 l_error_count := l_error_count+1;
963 end;
964 else
965 if l_flm_kanban_demand_intf(l_counter).INVENTORY_ITEM_ID is null and
966 l_flm_kanban_demand_intf(l_counter).ITEM_SEGMENTS is null then
967 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
968 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
969 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item');
970 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
971 l_error_count := l_error_count+1;
972 elsif l_flm_kanban_demand_intf(l_counter).INVENTORY_ITEM_ID is null and
973 l_flm_kanban_demand_intf(l_counter).ITEM_SEGMENTS is not null then
974 l_flm_kanban_demand_intf(l_counter).inventory_item_id := get_item_id (l_flm_kanban_demand_intf(l_counter).organization_id,
975 l_flm_kanban_demand_intf(l_counter).ITEM_SEGMENTS);
976 if l_flm_kanban_demand_intf(l_counter).inventory_item_id is null then
977 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
978 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
979 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item');
980 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
981 l_error_count := l_error_count+1;
982 end if;
983 elsif l_flm_kanban_demand_intf(l_counter).INVENTORY_ITEM_ID is not null then
984 begin
985 select 1 into l_temp
986 from mtl_system_items_kfv
987 where organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
988 and inventory_item_id = l_flm_kanban_demand_intf(l_counter).INVENTORY_ITEM_ID;
989 exception
990 when others then
991 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
992 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
993 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item');
994 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
995 l_error_count := l_error_count+1;
996 end;
997 end if;
998
999 if l_flm_kanban_demand_intf(l_counter).subinventory is null then
1000 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1001 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1002 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Subinventory');
1003 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1004 l_error_count := l_error_count+1;
1005 else
1006 begin
1007 select 1 into l_temp
1008 FROM mtl_secondary_inventories
1009 WHERE secondary_inventory_name = l_flm_kanban_demand_intf(l_counter).subinventory
1010 AND organization_id = l_flm_kanban_demand_intf(l_counter).organization_id;
1011 exception
1012 when others then
1013 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1014 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1015 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Subinventory');
1016 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1017 l_error_count := l_error_count+1;
1018 end;
1019 end if;
1020 if l_flm_kanban_demand_intf(l_counter).locator_id is null and
1021 l_flm_kanban_demand_intf(l_counter).LOCATOR_SEGMENTS is not null then
1022 l_flm_kanban_demand_intf(l_counter).locator_id := get_locator_id(l_flm_kanban_demand_intf(l_counter).organization_id,
1023 l_flm_kanban_demand_intf(l_counter).LOCATOR_SEGMENTS);
1024 if l_flm_kanban_demand_intf(l_counter).locator_id is null then
1025 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1026 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1027 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Locator');
1028 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1029 l_error_count := l_error_count+1;
1030 end if;
1031 elsif l_flm_kanban_demand_intf(l_counter).locator_id is not null then
1032 begin
1033 select 1 into l_temp
1034 FROM mtl_item_locations_kfv
1035 WHERE inventory_location_id = l_flm_kanban_demand_intf(l_counter).locator_id
1036 AND organization_id = l_flm_kanban_demand_intf(l_counter).organization_id;
1037 exception
1038 when others then
1039 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1040 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1041 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Locator');
1042 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1043 l_error_count := l_error_count+1;
1044 end;
1045 end if;
1046 begin
1047 select pull_sequence_id into l_flm_kanban_demand_intf(l_counter).PULL_SEQUENCE_ID
1048 FROM mtl_kanban_pull_sequences
1049 WHERE organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
1050 AND inventory_item_id = l_flm_kanban_demand_intf(l_counter).inventory_item_id
1051 and subinventory_name = l_flm_kanban_demand_intf(l_counter).subinventory
1052 and nvl(locator_id,-1) = nvl(l_flm_kanban_demand_intf(l_counter).locator_id,-1)
1053 and kanban_plan_id = -1;
1054 exception
1055 when others then
1056 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1057 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1058 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item/Subinventory/Locator');
1059 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1060 l_error_count := l_error_count+1;
1061 end;
1062 end if;
1063 if l_flm_kanban_demand_intf(l_counter).DEMAND_QUANTITY is null and
1064 l_flm_kanban_demand_intf(l_counter).AVERAGE_DEMAND is null then
1065 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1066 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1067 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand');
1068 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1069 l_error_count := l_error_count+1;
1070 elsif l_flm_kanban_demand_intf(l_counter).DEMAND_QUANTITY is not null then
1071 if l_flm_kanban_demand_intf(l_counter).AVERAGE_DEMAND is not null then
1072 l_flm_kanban_demand_intf(l_counter).process_status := WARNING;
1073 FND_MESSAGE.SET_NAME('FLM','FLM_IGNORE_ATTR_DEM_QTY');
1074 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Average Demand');
1075 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1076 end if;
1077 if l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE is not null then
1078 l_flm_kanban_demand_intf(l_counter).process_status := WARNING;
1079 FND_MESSAGE.SET_NAME('FLM','FLM_IGNORE_ATTR_DEM_QTY');
1080 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand Start Date');
1081 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1082 end if;
1083 if l_flm_kanban_demand_intf(l_counter).DEMAND_END_DATE is not null then
1084 l_flm_kanban_demand_intf(l_counter).process_status := WARNING;
1085 FND_MESSAGE.SET_NAME('FLM','FLM_IGNORE_ATTR_DEM_QTY');
1086 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand End Date');
1087 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1088 end if;
1089 if l_flm_kanban_demand_intf(l_counter).DEMAND_DATE is null then
1090 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1091 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1092 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand Date');
1093 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1094 l_error_count := l_error_count+1;
1095 end if;
1096 l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE := l_flm_kanban_demand_intf(l_counter).DEMAND_DATE;
1097 l_flm_kanban_demand_intf(l_counter).DEMAND_END_DATE := l_flm_kanban_demand_intf(l_counter).DEMAND_DATE;
1098 l_flm_kanban_demand_intf(l_counter).AVERAGE_DEMAND := l_flm_kanban_demand_intf(l_counter).DEMAND_QUANTITY;
1099 elsif l_flm_kanban_demand_intf(l_counter).AVERAGE_DEMAND is not null then
1100 if l_flm_kanban_demand_intf(l_counter).DEMAND_DATE is not null then
1101 l_flm_kanban_demand_intf(l_counter).process_status := WARNING;
1102 FND_MESSAGE.SET_NAME('FLM','FLM_IGNORE_ATTR_AVG_DEMAND');
1103 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand Date');
1104 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1105 end if;
1106 if l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE is null or
1107 l_flm_kanban_demand_intf(l_counter).DEMAND_END_DATE is null or
1108 l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE >
1109 l_flm_kanban_demand_intf(l_counter).DEMAND_END_DATE then
1110 l_flm_kanban_demand_intf(l_counter).process_status := ERROR;
1111 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1112 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Demand Start Date/Demand End Date');
1113 l_flm_kanban_demand_intf(l_counter).error := l_flm_kanban_demand_intf(l_counter).error||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1114 l_error_count := l_error_count+1;
1115 end if;
1116 end if;
1117
1118 if l_flm_kanban_demand_intf(l_counter).process_status = running or
1119 l_flm_kanban_demand_intf(l_counter).process_status = warning then
1120
1121 while l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE <=
1122 l_flm_kanban_demand_intf(l_counter).DEMAND_END_DATE
1123 loop
1124
1125 select mrp_kanban_demand_s.nextval into v_flm_kanban_demand(ins_counter).DEMAND_ID from dual;
1126 v_flm_kanban_demand(ins_counter).kanban_plan_id := 0;
1127 v_flm_kanban_demand(ins_counter).organization_id := l_flm_kanban_demand_intf(l_counter).organization_id;
1128 v_flm_kanban_demand(ins_counter).inventory_item_id := l_flm_kanban_demand_intf(l_counter).inventory_item_id;
1129 v_flm_kanban_demand(ins_counter).subinventory := l_flm_kanban_demand_intf(l_counter).subinventory;
1130 v_flm_kanban_demand(ins_counter).locator_id := l_flm_kanban_demand_intf(l_counter).locator_id;
1131 v_flm_kanban_demand(ins_counter).assembly_item_id := null;
1132 v_flm_kanban_demand(ins_counter).assembly_org_id := null;
1133 v_flm_kanban_demand(ins_counter).assembly_subinventory := null;
1134 v_flm_kanban_demand(ins_counter).assembly_locator_id := null;
1135 v_flm_kanban_demand(ins_counter).demand_date := l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE;
1136 v_flm_kanban_demand(ins_counter).demand_quantity := l_flm_kanban_demand_intf(l_counter).AVERAGE_DEMAND;
1137 v_flm_kanban_demand(ins_counter).order_type := -1;
1138 v_flm_kanban_demand(ins_counter).kanban_item_flag := 'Y';
1139 v_flm_kanban_demand(ins_counter).created_by := fnd_global.user_id;
1140 v_flm_kanban_demand(ins_counter).creation_date := sysdate;
1141 v_flm_kanban_demand(ins_counter).last_updated_by := fnd_global.user_id;
1142 v_flm_kanban_demand(ins_counter).last_update_date := sysdate;
1143 v_flm_kanban_demand(ins_counter).last_updated_login := fnd_global.login_id;
1144 v_flm_kanban_demand(ins_counter).request_id := fnd_global.conc_request_id;
1145 v_flm_kanban_demand(ins_counter).program_id := fnd_global.conc_program_id;
1146 v_flm_kanban_demand(ins_counter).program_application_id := fnd_global.prog_appl_id;
1147 v_flm_kanban_demand(ins_counter).program_update_date := sysdate;
1148
1149 ins_counter := ins_counter+1;
1150 l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE := l_flm_kanban_demand_intf(l_counter).DEMAND_START_DATE +1;
1151 end loop;
1152 end if;
1153
1154 v_process_status(pop_counter) := l_flm_kanban_demand_intf(l_counter).process_status;
1155 v_error(pop_counter) := l_flm_kanban_demand_intf(l_counter).error;
1156 v_interface_id(pop_counter) := l_flm_kanban_demand_intf(l_counter).interface_id;
1157
1158 if l_flm_kanban_demand_intf(l_counter).process_status = WARNING then
1159 fnd_file.put_line(fnd_file.log,' Record with Interface Id : '|| l_flm_kanban_demand_intf(l_counter).interface_id ||
1160 ' have following warnings : '|| l_flm_kanban_demand_intf(l_counter).error );
1161 end if;
1162 if l_flm_kanban_demand_intf(l_counter).process_status = ERROR then
1163 fnd_file.put_line(fnd_file.log,' Record with Interface Id : '|| l_flm_kanban_demand_intf(l_counter).interface_id ||
1164 ' failed with the error : '|| l_flm_kanban_demand_intf(l_counter).error );
1165 end if;
1166
1167 pop_counter := pop_counter+1;
1168 l_counter := l_flm_kanban_demand_intf.next(l_counter);
1169 end loop;
1170
1171 forall ins_index in v_flm_kanban_demand.first..v_flm_kanban_demand.last
1172 INSERT into MRP_KANBAN_DEMAND values v_flm_kanban_demand(ins_index);
1173
1174 forall upd_index in v_interface_id.first..v_interface_id.last
1175 update FLM_EKB_DEMAND_INTERFACE set
1176 process_status = decode(v_process_status(upd_index),running,completed,v_process_status(upd_index)),
1177 error = v_error(upd_index),
1178 last_update_date = sysdate
1179 where interface_id = v_interface_id(upd_index);
1180
1181 if l_error_count > 0 then
1182 x_ret_status := -1;
1183 else
1184 x_ret_status := 1;
1185 end if;
1186 exception
1187 when others then
1188 x_ret_status := -1;
1189 end process;
1190
1191 Function get_next_pull_seq_id
1192 Return Number
1193 is
1194 l_next_pull_seq_id Number;
1195 Begin
1196 select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL
1197 into l_next_pull_seq_id
1198 from dual;
1199
1200 return l_next_pull_seq_id;
1201 End get_next_pull_seq_id;
1202
1203 Function get_temp_cards_count(p_pull_sequence_id IN Number)
1204 RETURN NUMBER
1205 is
1206 l_temp_count number;
1207 Begin
1208 select count(1)
1209 into l_temp_count
1210 from mtl_kanban_cards
1211 where pull_sequence_id = p_pull_sequence_id and
1212 card_status in (1,2) and ( disable_date is not null or max_replenishments is not null);
1213
1214 return l_temp_count;
1215 End get_temp_cards_count;
1216
1217 Function get_actual_demand(p_org_id IN Number,
1218 p_pull_sequence_id IN Number)
1219 RETURN NUMBER
1220 is
1221 l_actual_demand number;
1222 l_end_date Date := sysdate;
1223 l_start_date Date;
1224 l_range number;
1225 Begin
1226
1227 l_start_date := l_end_date - nvl(flm_kanban_config_params.get_demand_range_days(p_org_id),30);
1228 l_range := nvl(trunc(l_end_date - l_start_date),0);
1229
1230 SELECT ( SUM(KANBAN_SIZE) / decode(l_range,0,1,l_range) )
1231 into l_actual_demand
1232 FROM FLM_EKB_ACT_SNAPSHOT
1233 WHERE PULL_SEQUENCE_ID = p_pull_sequence_id
1234 and REPLENISHMENT_DATE between l_start_date and l_end_date;
1235
1236 return l_actual_demand;
1237 End get_actual_demand;
1238
1239 Function get_actual_lead_time(p_org_id IN Number,
1240 p_pull_sequence_id IN Number)
1241 RETURN NUMBER
1242 is
1243 l_act_lead_time number;
1244 l_end_date Date := sysdate;
1245 l_start_date Date;
1246 Begin
1247
1248 l_start_date := l_end_date - nvl(flm_kanban_config_params.get_lt_range_days(p_org_id),30);
1249
1250 SELECT ( SUM(LEAD_TIME) / decode(count(1),0,1,count(1)) )
1251 into l_act_lead_time
1252 FROM FLM_EKB_ACT_SNAPSHOT
1253 WHERE PULL_SEQUENCE_ID = p_pull_sequence_id
1254 and LEAD_TIME > 0
1255 and RECEIVED_DATE between l_start_date and l_end_date;
1256
1257 return l_act_lead_time;
1258 End get_actual_lead_time;
1259
1260 -- This procedure creates horizontal plan for multiple pull sequences comma separated.
1261 procedure generate_plan_data(p_pull_seq_ids IN VARCHAR2,
1262 p_kanban_plan_id IN Number,
1263 p_request_id out nocopy Number)
1264 Is
1265
1266 l_kanban_plan_id Number;
1267 l_Kanban_size Number;
1268 l_value number;
1269 l_sql varchar2(6000);
1270 l_first number;
1271 l_temp number;
1272 l_counter number;
1273 g_user_id number;
1274 g_user_login_id number;
1275 l_plan_pull_seq number;
1276 l_item_unit_cost number;
1277
1278 type t_flm_kanban_planning_temp is table of flm_ekb_horizontal_plan_temp%rowtype index by binary_integer;
1279 v_flm_kanban_planning_temp t_flm_kanban_planning_temp;
1280
1281 type t_exp_date is table of date index by binary_integer;
1282 type t_exp_repl_qty is table of number index by binary_integer;
1283 type t_repl_no_of_cards is table of number index by binary_integer;
1284
1285 v_exp_pull_seq_id t_exp_repl_qty;
1286 v_exp_date t_exp_date;
1287 v_exp_repl_qty t_exp_repl_qty;
1288 v_repl_no_of_cards t_repl_no_of_cards;
1289 v_repl_date t_exp_date;
1290
1291 type t_qty_temp is table of number index by varchar2(25);
1292 v_expected_receipts t_qty_temp;
1293 v_cards_replenished t_qty_temp;
1294
1295 v_demand_date t_exp_date;
1296 v_demand_qty t_exp_repl_qty;
1297 v_dem_pull_seq_id t_exp_repl_qty;
1298
1299 v_demand_quantity t_qty_temp;
1300
1301 type t_plan_pull_seq_rec is record(pull_sequence_id number,organization_id number,kanban_plan_id number,inventory_item_id number,
1302 subinventory_name varchar2(10),locator_id number,safety_stock_days number,kanban_size number,
1303 number_of_cards number,avg_dependent_demand number,begin_onhand number,replenishment_lead_time number);
1304 type t_plan_pull_seq_tbl is table of t_plan_pull_seq_rec index by binary_integer;
1305 v_plan_pull_seq_tbl t_plan_pull_seq_tbl;
1306
1307 l_start_date date;
1308 l_end_date date;
1309 l_temp_start_date date;
1310
1311 l_exp_receipt Number;
1312 l_new_counter Number;
1313 l_temp_onhand Number := 0;
1314 l_temp_receipts Number := 0;
1315 l_current_onhand Number := 0;
1316 l_already_receipt Number :=0;
1317 l_rep_counter Number;
1318 l_org_id Number;
1319 l_new_date Date;
1320 Begin
1321 l_value := trunc(dbms_random.value(0,999999999999));
1322 if p_kanban_plan_id = -1 then
1323 l_sql := 'delete from MTL_KANBAN_PULL_SEQUENCES mkps1 where mkps1.Kanban_Plan_id = 0 and mkps1.pull_sequence_id in'||
1324 '(select syspullseq.pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES syspullseq, MTL_KANBAN_PULL_SEQUENCES prodpullSeq'||
1325 ' where syspullseq.kanban_plan_id = 0 and prodpullSeq.kanban_plan_id = -1 '||
1326 ' and syspullseq.organization_id = prodpullSeq.organization_id '||
1327 ' and syspullseq.inventory_item_id = prodpullSeq.inventory_item_id '||
1328 ' and syspullseq.Subinventory_name = prodpullSeq.Subinventory_name '||
1329 ' and nvl(syspullseq.Locator_id,-1) = nvl(prodpullSeq.Locator_id ,-1) '||
1330 ' and prodpullSeq.pull_sequence_id in ( '|| p_pull_seq_ids || ' )) ';
1331 execute immediate l_sql;
1332 l_kanban_plan_id := 0;
1333
1334 l_sql := 'INSERT INTO MTL_KANBAN_PULL_SEQUENCES (Pull_sequence_id,Inventory_item_id,Organization_id,Subinventory_name,Kanban_Plan_id,'||
1335 'Source_type,Last_Update_Date,Last_Updated_By,Creation_Date,Created_By,Last_Update_Login,Locator_id,Calculate_Kanban_flag,'||
1336 'Kanban_size,Number_of_cards,Minimum_order_quantity,Allocation_Percent,Replenishment_lead_time,Fixed_Lot_multiplier,'||
1337 'Safety_Stock_Days,Attribute_Category,Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,Attribute7,Attribute8,'||
1338 'Attribute9,Attribute10,Attribute11,Attribute12,Attribute13,Attribute14,Attribute15,'||
1339 'PLANNING_EFFECTIVITY,UPDATED_FLAG, RELEASE_KANBAN_FLAG,AVG_DEPENDENT_DEMAND,FUTURE_CARD_SIZE,FUTURE_NO_OF_CARDS)'||
1340 'select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL,Inventory_item_id,Organization_id,Subinventory_name,0,'||
1341 'Source_type,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,Locator_id,Calculate_Kanban_flag,'||
1342 'decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,'||
1343 'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),ALLOCATION_PERCENT,'||
1344 'REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size),'||
1345 'decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,'||
1346 'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),ALLOCATION_PERCENT,'||
1347 'REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards ),'||
1348 'Minimum_order_quantity,Allocation_Percent,Replenishment_lead_time,Fixed_Lot_multiplier,'||
1349 'Safety_Stock_Days,Attribute_Category,Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,Attribute7,Attribute8,'||
1350 'Attribute9,Attribute10,Attribute11,Attribute12,Attribute13,Attribute14,Attribute15,'||
1351 'PLANNING_EFFECTIVITY,'||to_char(l_value)||',RELEASE_KANBAN_FLAG,'||
1352 'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),'||
1353 'decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,'||
1354 'FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),'||
1355 'ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size),'||
1356 'decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,'||
1357 'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),'||
1358 'ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards)'||
1359 ' from MTL_KANBAN_PULL_SEQUENCES where pull_sequence_id in ('||p_pull_seq_ids||')' ;
1360 else
1361 l_kanban_plan_id := p_kanban_plan_id;
1362 l_sql := 'update MTL_KANBAN_PULL_SEQUENCES set UPDATED_FLAG = '||l_value||
1363 ',FUTURE_NO_OF_CARDS = Number_of_cards,FUTURE_CARD_SIZE= Kanban_size where pull_sequence_id in ('||p_pull_seq_ids||')';
1364 end if;
1365 execute immediate l_sql;
1366 -- Generate Horizontal Plan Data.
1367 v_plan_pull_seq_tbl.delete;
1368 select mkps.pull_sequence_id,mkps.organization_id,mkps.kanban_plan_id,mkps.inventory_item_id,mkps.Subinventory_name,mkps.locator_id,
1369 mkps.Safety_Stock_Days,decode(nvl(mkps.Kanban_size,0),0,1,mkps.Kanban_size) Kanban_size,decode(nvl(mkps.Number_of_cards,0),0,1,Number_of_cards) Number_of_cards,
1370 mkps.avg_dependent_demand,nvl(sum(nvl(moqd.primary_transaction_quantity,0)),0),nvl(ceil(mkps.replenishment_lead_time),0)
1371 bulk collect into v_plan_pull_seq_tbl
1372 FROM mtl_onhand_quantities_detail moqd,
1373 mtl_kanban_pull_sequences mkps
1374 WHERE moqd.inventory_item_id(+) = mkps.inventory_item_id
1375 AND moqd.organization_id(+) = mkps.organization_id
1376 AND moqd.subinventory_code(+) = mkps.subinventory_name
1377 and nvl(moqd.locator_id(+),-100) = nvl(mkps.locator_id,-100)
1378 and mkps.UPDATED_FLAG = l_value and mkps.kanban_plan_id =l_kanban_plan_id
1379 group by mkps.pull_sequence_id,mkps.organization_id,mkps.kanban_plan_id,mkps.inventory_item_id,mkps.Subinventory_name,mkps.locator_id,
1380 mkps.Safety_Stock_Days,decode(nvl(mkps.Kanban_size,0),0,1,mkps.Kanban_size),decode(nvl(mkps.Number_of_cards,0),0,1,Number_of_cards),mkps.avg_dependent_demand,
1381 nvl(ceil(mkps.replenishment_lead_time),0);
1382 l_plan_pull_seq := v_plan_pull_seq_tbl.first;
1383 if l_plan_pull_seq is not null then
1384 l_org_id := v_plan_pull_seq_tbl(l_plan_pull_seq).organization_id;
1385 else
1386 p_request_id := l_value;
1387 return;
1388 end if;
1389 if l_kanban_plan_id = 0 then
1390 l_start_date := mrp_calendar.NEXT_WORK_DAY(l_org_id,1,trunc(sysdate));
1391 l_end_date := l_start_date;
1392 for i in 1..30 loop
1393 l_end_date := mrp_calendar.NEXT_WORK_DAY(l_org_id,1,l_end_date+1);
1394 end loop;
1395 else
1396 select mrp_calendar.NEXT_WORK_DAY(organization_id,1,nvl(greatest(trunc(plan_start_date),trunc(sysdate)),trunc(sysdate))),
1397 mrp_calendar.NEXT_WORK_DAY(organization_id,1,trunc(plan_cutoff_date))
1398 into l_start_date,l_end_date
1399 from mrp_kanban_plans where kanban_plan_id = l_kanban_plan_id;
1400 end if;
1401 v_exp_date.delete;
1402 v_exp_repl_qty.delete;
1403 v_repl_no_of_cards.delete;
1404 v_exp_pull_seq_id.delete;
1405 v_repl_date.delete;
1406 select pull_seq,
1407 expected_date,
1408 replenished_date,
1409 sum(qty) expected_qty,
1410 count(distinct card) replenished_card
1411 bulk collect into v_exp_pull_seq_id,
1412 v_exp_date,
1413 v_repl_date,
1414 v_exp_repl_qty,
1415 v_repl_no_of_cards
1416 from (select plan.pull_sequence_id pull_seq,
1417 mkc.kanban_card_id card,
1418 mkc.kanban_size qty,
1419 min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,
1420 case when trunc(mkca.creation_date+mkps.replenishment_lead_time) < l_start_date then l_start_date
1421 else trunc(mkca.creation_date+mkps.replenishment_lead_time) end)) expected_date,
1422 min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,trunc(mkca.creation_date))) replenished_date
1423 from mtl_kanban_card_activity mkca,
1424 mtl_kanban_cards mkc,
1425 mtl_kanban_pull_sequences mkps,
1426 mtl_kanban_pull_sequences plan
1427 where mkps.organization_id = plan.organization_id
1428 and mkps.inventory_item_id = plan.inventory_item_id
1429 and mkps.subinventory_name = plan.subinventory_name
1430 and nvl(mkps.locator_id,-100) = nvl(plan.locator_id,-100)
1431 and mkps.kanban_plan_id = -1
1432 and mkps.pull_sequence_id = mkc.pull_sequence_id
1433 and trunc(mkc.supply_status) in (4,5,6,7)
1434 and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
1435 and mkc.KANBAN_CARD_ID = mkca.kanban_card_id
1436 and plan.UPDATED_FLAG = l_value
1437 and plan.kanban_plan_id = l_kanban_plan_id
1438 group by plan.pull_sequence_id,mkc.kanban_card_id,mkc.kanban_size)
1439 where expected_date between l_start_date and l_end_date
1440 or replenished_date between l_start_date and l_end_date
1441 group by pull_seq,expected_date,replenished_date;
1442
1443 v_expected_receipts.delete;
1444 v_cards_replenished.delete;
1445 l_temp := v_exp_pull_seq_id.first;
1446 if l_temp is not null then
1447 while l_temp <= v_exp_pull_seq_id.last loop
1448 v_expected_receipts(to_char(v_exp_pull_seq_id(l_temp)||'-:-'||v_exp_date(l_temp))) := v_exp_repl_qty(l_temp);
1449 v_cards_replenished(to_char(v_exp_pull_seq_id(l_temp)||'-:-'||v_repl_date(l_temp))) := v_repl_no_of_cards(l_temp);
1450 l_temp := v_exp_pull_seq_id.next(l_temp);
1451 end loop;
1452 end if;
1453
1454 v_dem_pull_seq_id.delete;
1455 v_demand_date.delete;
1456 v_demand_qty.delete;
1457 -- This SQL gets item demand at a day level.
1458 select plan.pull_sequence_id,
1459 mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date)),
1460 sum(nvl(demand_quantity,0))
1461 bulk collect into v_dem_pull_seq_id,
1462 v_demand_date,
1463 v_demand_qty
1464 from mrp_kanban_demand mkd,
1465 mtl_kanban_pull_sequences plan
1466 where mkd.kanban_plan_id = l_kanban_plan_id
1467 and mkd.organization_id = plan.organization_id
1468 and mkd.inventory_item_id = plan.inventory_item_id
1469 and mkd.subinventory = plan.subinventory_name
1470 and nvl(mkd.locator_id,-100) = nvl(plan.locator_id,-100)
1471 and plan.UPDATED_FLAG = l_value and plan.kanban_plan_id = mkd.kanban_plan_id
1472 and mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date)) between l_start_date and l_end_date
1473 group by plan.pull_sequence_id,mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date));
1474 v_demand_quantity.delete;
1475 l_temp := v_dem_pull_seq_id.first;
1476 if l_temp is not null then
1477 while l_temp <= v_dem_pull_seq_id.last loop
1478 v_demand_quantity(to_char(v_dem_pull_seq_id(l_temp)||'-:-'||v_demand_date(l_temp))) := v_demand_qty(l_temp);
1479 l_temp := v_dem_pull_seq_id.next(l_temp);
1480 end loop;
1481 end if;
1482 g_user_id := fnd_global.user_id;
1483 g_user_login_id := fnd_global.login_id;
1484 l_counter := 0;
1485 l_temp_start_date := l_start_date;
1486 v_flm_kanban_planning_temp.delete;
1487 l_plan_pull_seq := v_plan_pull_seq_tbl.first;
1488 if l_plan_pull_seq is not null then
1489 while l_plan_pull_seq <= v_plan_pull_seq_tbl.last loop
1490
1491 l_item_unit_cost := CST_COST_API.get_item_cost (p_api_version => 1,
1492 p_inventory_item_id => v_plan_pull_seq_tbl(l_plan_pull_seq).inventory_item_id,
1493 p_organization_id => v_plan_pull_seq_tbl(l_plan_pull_seq).organization_id);
1494 l_start_date := l_temp_start_date;
1495 l_first := l_counter+1;
1496 WHILE l_start_date <= l_end_date
1497 LOOP
1498 l_counter := l_counter+1;
1499 select FLM_KANBAN_PLANNING_TEMP_S.nextval into v_flm_kanban_planning_temp(l_counter).Planning_temp_id from dual;
1500 v_flm_kanban_planning_temp(l_counter).Kanban_Plan_Id := l_kanban_plan_id;
1501 v_flm_kanban_planning_temp(l_counter).Pull_Sequence_Id := v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id;
1502 v_flm_kanban_planning_temp(l_counter).Organization_Id := v_plan_pull_seq_tbl(l_plan_pull_seq).organization_id;
1503 v_flm_kanban_planning_temp(l_counter).Inventory_Item_Id := v_plan_pull_seq_tbl(l_plan_pull_seq).inventory_item_id;
1504 v_flm_kanban_planning_temp(l_counter).SubInventory_Code := v_plan_pull_seq_tbl(l_plan_pull_seq).subinventory_name;
1505 v_flm_kanban_planning_temp(l_counter).Locator_Id := v_plan_pull_seq_tbl(l_plan_pull_seq).locator_id;
1506 v_flm_kanban_planning_temp(l_counter).plan_date := l_start_date;
1507 v_flm_kanban_planning_temp(l_counter).LAST_UPDATE_DATE := sysdate;
1508 v_flm_kanban_planning_temp(l_counter).LAST_UPDATED_BY := g_user_id;
1509 v_flm_kanban_planning_temp(l_counter).CREATION_DATE := sysdate;
1510 v_flm_kanban_planning_temp(l_counter).LAST_UPDATED_LOGIN := g_user_login_id;
1511 v_flm_kanban_planning_temp(l_counter).CREATED_BY := g_user_id;
1512 v_flm_kanban_planning_temp(l_counter).Adjusted_Daily_demand := null;
1513 v_flm_kanban_planning_temp(l_counter).ADJUSTED_SAFETY_STOCK_DAYS := null;
1514 v_flm_kanban_planning_temp(l_counter).Item_unit_cost := nvl(l_item_unit_cost,0);
1515 v_flm_kanban_planning_temp(l_counter).Safety_stock_days := v_plan_pull_seq_tbl(l_plan_pull_seq).safety_stock_days;
1516 if l_counter = l_first then
1517 v_flm_kanban_planning_temp(l_counter).Beginning_onhand := nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).begin_onhand,0);
1518 l_temp_onhand := nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size,0)*
1519 nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).number_of_cards,0);
1520 if l_temp_onhand = 0 then
1521 l_temp_onhand := nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).begin_onhand,0);
1522 end if;
1523 l_current_onhand := v_flm_kanban_planning_temp(l_counter).Beginning_onhand;
1524 else
1525 v_flm_kanban_planning_temp(l_counter).Beginning_onhand := nvl(v_flm_kanban_planning_temp(l_counter-1).Available_Onhand,0);
1526 end if;
1527
1528 if v_expected_receipts.exists(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)) then
1529 v_flm_kanban_planning_temp(l_counter).expected_receipts := nvl(v_flm_kanban_planning_temp(l_counter).expected_receipts,0) +
1530 nvl(v_expected_receipts(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)),0) ;
1531 l_temp_receipts := nvl(v_expected_receipts(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)),0);
1532 else
1533 v_flm_kanban_planning_temp(l_counter).expected_receipts := nvl(v_flm_kanban_planning_temp(l_counter).expected_receipts,0);
1534 l_temp_receipts := 0;
1535 end if;
1536 if v_cards_replenished.exists(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)) then
1537 v_flm_kanban_planning_temp(l_counter).cards_replenished := nvl(v_flm_kanban_planning_temp(l_counter).cards_replenished,0) +
1538 nvl(v_cards_replenished(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)),0) ;
1539 else
1540 v_flm_kanban_planning_temp(l_counter).cards_replenished := nvl(v_flm_kanban_planning_temp(l_counter).cards_replenished,0);
1541 end if;
1542
1543 if v_demand_quantity.exists(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)) then
1544 v_flm_kanban_planning_temp(l_counter).Daily_Demand := nvl(v_demand_quantity(to_char(v_plan_pull_seq_tbl(l_plan_pull_seq).Pull_Sequence_Id||'-:-'||l_start_date)),0);
1545 end if;
1546 if v_flm_kanban_planning_temp(l_counter).Daily_Demand is null then
1547 l_temp := v_dem_pull_seq_id.first;
1548 if l_temp is not null then
1549 while l_temp <= v_dem_pull_seq_id.last loop
1550 if v_dem_pull_seq_id(l_temp) = v_flm_kanban_planning_temp(l_counter).Pull_Sequence_Id then
1551 v_flm_kanban_planning_temp(l_counter).Daily_Demand := 0;
1552 end if;
1553 l_temp := v_dem_pull_seq_id.next(l_temp);
1554 end loop;
1555 end if;
1556 end if;
1557 if v_flm_kanban_planning_temp(l_counter).Daily_Demand is null then
1558 v_flm_kanban_planning_temp(l_counter).Daily_Demand := nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).avg_dependent_demand,0) ;
1559 end if;
1560
1561 if ((l_current_onhand-v_flm_kanban_planning_temp(l_counter).Daily_Demand+l_temp_receipts)< l_temp_onhand and
1562 ((l_temp_onhand -(l_current_onhand-v_flm_kanban_planning_temp(l_counter).Daily_Demand+l_temp_receipts)) >= v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size)) then
1563 l_exp_receipt := nvl(trunc((l_temp_onhand - (l_current_onhand-v_flm_kanban_planning_temp(l_counter).Daily_Demand+l_temp_receipts ))
1564 /v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size)*v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size,0);
1565 if (l_exp_receipt > l_temp_onhand) then
1566 l_exp_receipt := l_temp_onhand;
1567 end if;
1568 l_already_receipt := 0;
1569 if l_exp_receipt > 0 then
1570 l_rep_counter := 1;
1571 while l_rep_counter < nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).replenishment_lead_time,0) loop
1572 if l_counter-l_rep_counter >= l_first and v_flm_kanban_planning_temp.exists(l_counter-l_rep_counter) then
1573 l_already_receipt := l_already_receipt + (v_flm_kanban_planning_temp(l_counter-l_rep_counter).cards_replenished*v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size);
1574 else
1575 exit;
1576 end if;
1577 l_rep_counter := l_rep_counter+1;
1578 end loop;
1579 end if;
1580 if (l_exp_receipt > (l_temp_onhand-l_already_receipt)) then
1581 l_exp_receipt := l_temp_onhand-l_already_receipt;
1582 end if;
1583 l_new_date := l_start_date;
1584 for i in 1..nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).replenishment_lead_time,0) loop
1585 l_new_date := mrp_calendar.NEXT_WORK_DAY(l_org_id,1,l_new_date+1);
1586 end loop;
1587 if l_new_date <= l_end_date then
1588 l_new_counter := l_counter+v_plan_pull_seq_tbl(l_plan_pull_seq).replenishment_lead_time;
1589 if v_flm_kanban_planning_temp.exists(l_new_counter) then
1590 v_flm_kanban_planning_temp(l_new_counter).expected_receipts := l_exp_receipt+nvl(v_flm_kanban_planning_temp(l_new_counter).expected_receipts,0);
1591 else
1592 v_flm_kanban_planning_temp(l_new_counter).expected_receipts := l_exp_receipt;
1593 end if;
1594 end if;
1595 v_flm_kanban_planning_temp(l_counter).cards_replenished := nvl(v_flm_kanban_planning_temp(l_counter).cards_replenished,0)+
1596 nvl(ceil(l_exp_receipt/v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size),0);
1597 end if;
1598
1599 v_flm_kanban_planning_temp(l_counter).Available_Onhand := (v_flm_kanban_planning_temp(l_counter).Beginning_onhand +
1600 nvl(v_flm_kanban_planning_temp(l_counter).expected_receipts,0) -
1601 nvl(v_flm_kanban_planning_temp(l_counter).Daily_Demand,0));
1602 l_kanban_size := v_plan_pull_seq_tbl(l_plan_pull_seq).kanban_size;
1603 l_current_onhand := l_current_onhand+l_temp_receipts - nvl(v_flm_kanban_planning_temp(l_counter).Daily_Demand,0) +
1604 nvl((l_kanban_size*v_flm_kanban_planning_temp(l_counter).cards_replenished),0);
1605 if l_kanban_size = 0 then
1606 l_kanban_size := ceil( v_flm_kanban_planning_temp(l_counter).Available_Onhand/nvl(v_plan_pull_seq_tbl(l_plan_pull_seq).number_of_cards,1));
1607 end if;
1608 v_flm_kanban_planning_temp(l_counter).Cards_Onhand := nvl(ceil(v_flm_kanban_planning_temp(l_counter).Available_Onhand/l_kanban_size),0);
1609 if v_flm_kanban_planning_temp(l_counter).Cards_Onhand < 0 then
1610 v_flm_kanban_planning_temp(l_counter).Cards_Onhand := 0;
1611 end if;
1612 v_flm_kanban_planning_temp(l_counter).inventory_value := v_flm_kanban_planning_temp(l_counter).Available_Onhand * l_item_unit_cost;
1613 v_flm_kanban_planning_temp(l_counter).safety_stock_target := nvl(v_flm_kanban_planning_temp(l_counter).Safety_stock_days,0)*
1614 nvl(v_flm_kanban_planning_temp(l_counter).Daily_Demand,0);
1615 l_start_date := mrp_calendar.NEXT_WORK_DAY(l_org_id,1,l_start_date +1);
1616 END LOOP;
1617 l_plan_pull_seq := v_plan_pull_seq_tbl.next(l_plan_pull_seq);
1618 end loop;
1619 end if;
1620 delete from FLM_EKB_HORIZONTAL_PLAN_TEMP where kanban_plan_id = l_kanban_plan_id;
1621 forall cntr in v_flm_kanban_planning_temp.first..v_flm_kanban_planning_temp.last
1622 INSERT into FLM_EKB_HORIZONTAL_PLAN_TEMP values v_flm_kanban_planning_temp(cntr);
1623 commit;
1624 p_request_id := l_value;
1625 Exception
1626 When others then
1627 p_request_id := -1;
1628 End generate_plan_data;
1629
1630 procedure update_production(p_plan_pull_seq_ids IN VARCHAR2,
1631 p_effective_date IN DATE,
1632 x_ret_status OUT NOCOPY Number)
1633 IS
1634 l_sql varchar2(4000);
1635 l_cursor integer;
1636 l_sql_exec integer;
1637 l_plan_pull_seq_id Number;
1638 l_no_of_cards Number;
1639 l_card_size Number;
1640 l_calc_flag Number;
1641 l_eff_date Date;
1642 l_no_of_temp_cards Number;
1643 l_no_of_cycles Number;
1644 l_temp_exp_date Date;
1645 l_imp_plan varchar2(10);
1646 l_plan_st_dt Date;
1647 l_plan_end_dt Date;
1648 l_prod_pull_seq_id Number;
1649 l_future_size Number;
1650 l_future_cards Number;
1651 l_avg_demand Number;
1652 l_moq Number;
1653 l_alloc_perc Number;
1654 l_lead_time Number;
1655 l_lot_multiplier Number;
1656 l_safety_stock Number;
1657 l_curr_card_count Number;
1658 l_card_count Number;
1659 l_upd_card_count Number;
1660 r_kanban_card_rec FLM_EKANBAN_PUB.kanban_card_rec_type;
1661 l_kanban_card_rec FLM_EKANBAN_PUB.kanban_card_rec_type;
1662 l_item_id Number;
1663 l_org_id Number;
1664 l_subinv varchar2(20);
1665 l_src_type Number;
1666 l_kanban_size Number;
1667 l_locator Number;
1668 l_src_org_id Number;
1669 l_src_subinv varchar2(20);
1670 l_src_locator Number;
1671 l_wip_line_id Number;
1672 l_attr_1 varchar2(150);
1673 l_attr_2 varchar2(150);
1674 l_attr_3 varchar2(150);
1675 l_attr_4 varchar2(150);
1676 l_attr_5 varchar2(150);
1677 l_attr_6 varchar2(150);
1678 l_attr_7 varchar2(150);
1679 l_attr_8 varchar2(150);
1680 l_attr_9 varchar2(150);
1681 l_attr_10 varchar2(150);
1682 l_attr_11 varchar2(150);
1683 l_attr_12 varchar2(150);
1684 l_attr_13 varchar2(150);
1685 l_attr_14 varchar2(150);
1686 l_attr_15 varchar2(150);
1687 l_ret_status VARCHAR2(1);
1688 l_err_msg VARCHAR2(2000);
1689 l_calculate_card_count Number;
1690 l_release_kanban_flag Number;
1691
1692 l_source_type Number;
1693 Begin
1694
1695 l_sql := 'select plan.pull_sequence_id,plan.Number_of_cards,plan.Kanban_size, '||
1696 'plan.Calculate_Kanban_flag,null,0,null,null, '||
1697 'plan.LAST_IMPLEMENTED_PLAN,plan.plan_start_date,plan.plan_end_date,prod.pull_sequence_id, '||
1698 'plan.avg_dependent_demand,plan.minimum_order_quantity,plan.allocation_percent,plan.replenishment_lead_time,plan.fixed_lot_multiplier,plan.safety_stock_days, '||
1699 'plan.attribute1,plan.attribute2,plan.attribute3,plan.attribute4,plan.attribute5,plan.attribute6,plan.attribute7,plan.attribute8, '||
1700 'plan.attribute9,plan.attribute10,plan.attribute11,plan.attribute12,plan.attribute13,plan.attribute14,plan.attribute15,prod.release_kanban_flag '||
1701 'from mtl_kanban_pull_sequences plan,mtl_kanban_pull_sequences prod '||
1702 'where plan.pull_sequence_id in ( '||p_plan_pull_seq_ids||' ) '||
1703 'and plan.organization_id = prod.organization_id '||
1704 'and plan.inventory_item_id = prod.inventory_item_id '||
1705 'and plan.subinventory_name = prod.subinventory_name '||
1706 'and nvl(plan.locator_id,-5) = nvl(prod.locator_id,-5) '||
1707 'and prod.kanban_plan_id = -1 '||
1708 'and plan.kanban_plan_id <> -1 ';
1709
1710 l_cursor := dbms_sql.open_cursor;
1711 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1712
1713 dbms_sql.define_column(l_cursor, 1, l_plan_pull_seq_id);
1714 dbms_sql.define_column(l_cursor, 2, l_no_of_cards);
1715 dbms_sql.define_column(l_cursor, 3, l_card_size);
1716 dbms_sql.define_column(l_cursor, 4, l_calc_flag);
1717 dbms_sql.define_column(l_cursor, 5, l_eff_date);
1718 dbms_sql.define_column(l_cursor, 6, l_no_of_temp_cards);
1719 dbms_sql.define_column(l_cursor, 7, l_no_of_cycles);
1720 dbms_sql.define_column(l_cursor, 8, l_temp_exp_date);
1721 dbms_sql.define_column(l_cursor, 9, l_imp_plan, 10);
1722 dbms_sql.define_column(l_cursor, 10,l_plan_st_dt);
1723 dbms_sql.define_column(l_cursor, 11,l_plan_end_dt);
1724 dbms_sql.define_column(l_cursor, 12,l_prod_pull_seq_id);
1725 dbms_sql.define_column(l_cursor, 13,l_avg_demand);
1726 dbms_sql.define_column(l_cursor, 14,l_moq);
1727 dbms_sql.define_column(l_cursor, 15,l_alloc_perc);
1728 dbms_sql.define_column(l_cursor, 16,l_lead_time);
1729 dbms_sql.define_column(l_cursor, 17,l_lot_multiplier);
1730 dbms_sql.define_column(l_cursor, 18,l_safety_stock);
1731 dbms_sql.define_column(l_cursor, 19, l_attr_1, 150);
1732 dbms_sql.define_column(l_cursor, 20, l_attr_2, 150);
1733 dbms_sql.define_column(l_cursor, 21, l_attr_3, 150);
1734 dbms_sql.define_column(l_cursor, 22, l_attr_4, 150);
1735 dbms_sql.define_column(l_cursor, 23, l_attr_5, 150);
1736 dbms_sql.define_column(l_cursor, 24, l_attr_6, 150);
1737 dbms_sql.define_column(l_cursor, 25, l_attr_7, 150);
1738 dbms_sql.define_column(l_cursor, 26, l_attr_8, 150);
1739 dbms_sql.define_column(l_cursor, 27, l_attr_9, 150);
1740 dbms_sql.define_column(l_cursor, 28, l_attr_10, 150);
1741 dbms_sql.define_column(l_cursor, 29, l_attr_11, 150);
1742 dbms_sql.define_column(l_cursor, 30, l_attr_12, 150);
1743 dbms_sql.define_column(l_cursor, 31, l_attr_13, 150);
1744 dbms_sql.define_column(l_cursor, 32, l_attr_14, 150);
1745 dbms_sql.define_column(l_cursor, 33, l_attr_15, 150);
1746 dbms_sql.define_column(l_cursor, 34, l_release_kanban_flag);
1747
1748 l_sql_exec := dbms_sql.execute(l_cursor);
1749
1750 loop
1751 exit when dbms_sql.fetch_rows(l_cursor) = 0;
1752 dbms_sql.column_value(l_cursor, 1, l_plan_pull_seq_id);
1753 dbms_sql.column_value(l_cursor, 2, l_no_of_cards);
1754 dbms_sql.column_value(l_cursor, 3, l_card_size);
1755 dbms_sql.column_value(l_cursor, 4, l_calc_flag);
1756 dbms_sql.column_value(l_cursor, 5, l_eff_date);
1757 dbms_sql.column_value(l_cursor, 6, l_no_of_temp_cards);
1758 dbms_sql.column_value(l_cursor, 7, l_no_of_cycles);
1759 dbms_sql.column_value(l_cursor, 8, l_temp_exp_date);
1760 dbms_sql.column_value(l_cursor, 9, l_imp_plan);
1761 dbms_sql.column_value(l_cursor, 10,l_plan_st_dt);
1762 dbms_sql.column_value(l_cursor, 11,l_plan_end_dt);
1763 dbms_sql.column_value(l_cursor, 12,l_prod_pull_seq_id);
1764 dbms_sql.column_value(l_cursor, 13, l_avg_demand);
1765 dbms_sql.column_value(l_cursor, 14, l_moq);
1766 dbms_sql.column_value(l_cursor, 15, l_alloc_perc);
1767 dbms_sql.column_value(l_cursor, 16,l_lead_time);
1768 dbms_sql.column_value(l_cursor, 17,l_lot_multiplier);
1769 dbms_sql.column_value(l_cursor, 18,l_safety_stock);
1770 dbms_sql.column_value(l_cursor, 19, l_attr_1);
1771 dbms_sql.column_value(l_cursor, 20, l_attr_2);
1772 dbms_sql.column_value(l_cursor, 21, l_attr_3);
1773 dbms_sql.column_value(l_cursor, 22, l_attr_4);
1774 dbms_sql.column_value(l_cursor, 23, l_attr_5);
1775 dbms_sql.column_value(l_cursor, 24, l_attr_6);
1776 dbms_sql.column_value(l_cursor, 25, l_attr_7);
1777 dbms_sql.column_value(l_cursor, 26, l_attr_8);
1778 dbms_sql.column_value(l_cursor, 27, l_attr_9);
1779 dbms_sql.column_value(l_cursor, 28, l_attr_10);
1780 dbms_sql.column_value(l_cursor, 29, l_attr_11);
1781 dbms_sql.column_value(l_cursor, 30, l_attr_12);
1782 dbms_sql.column_value(l_cursor, 31, l_attr_13);
1783 dbms_sql.column_value(l_cursor, 32, l_attr_14);
1784 dbms_sql.column_value(l_cursor, 33, l_attr_15);
1785 dbms_sql.column_value(l_cursor, 34, l_release_kanban_flag);
1786 l_eff_date := p_effective_date;
1787 l_kanban_size := l_card_size;
1788 if l_calc_flag = 1 then -- Calculate Size
1789 l_no_of_cards := null;
1790 else
1791 l_card_size := null;
1792 end if;
1793 l_calculate_card_count := l_no_of_cards+l_no_of_temp_cards;
1794 l_release_kanban_flag := 1;
1795 if l_eff_date is null or trunc(l_eff_date) < sysdate then
1796 l_eff_date := null;
1797 l_future_size := null;
1798 l_future_cards := null;
1799 end if;
1800 if trunc(l_eff_date) > sysdate then
1801 l_future_size := l_card_size;
1802 l_future_cards := l_no_of_cards;
1803 l_no_of_cards := null;
1804 l_card_size := null;
1805 end if;
1806
1807 -- Update Planning Pull sequence
1808 update mtl_kanban_pull_sequences
1809 set future_no_of_cards = Number_of_cards,
1810 future_card_size = Kanban_size,
1811 planning_effectivity = null,
1812 no_of_temp_cards = null,
1813 no_of_temp_cycles = null,
1814 temp_expiry_date = null
1815 where pull_sequence_id = l_plan_pull_seq_id;
1816
1817 -- Update Production pull sequence
1818 update mtl_kanban_pull_sequences
1819 set number_of_cards = decode(Calculate_Kanban_flag,2,nvl(l_no_of_cards,number_of_cards),number_of_cards),
1820 future_no_of_cards = decode(l_eff_date,null,null,nvl(l_future_cards,number_of_cards)),
1821 kanban_size = decode(Calculate_Kanban_flag,1,nvl(l_card_size,kanban_size),kanban_size),
1822 future_card_size = decode(l_eff_date,null,null,nvl(l_future_size,kanban_size)),
1823 planning_effectivity = l_eff_date,
1824 last_implemented_plan = l_imp_plan,
1825 plan_start_date = l_plan_st_dt,
1826 plan_end_date = l_plan_end_dt,
1827 avg_dependent_demand = l_avg_demand,
1828 minimum_order_quantity = l_moq,
1829 allocation_percent = l_alloc_perc,
1830 replenishment_lead_time = l_lead_time,
1831 fixed_lot_multiplier = l_lot_multiplier,
1832 safety_stock_days = l_safety_stock,
1833 last_update_date = sysdate,
1834 last_updated_by = fnd_global.user_id,
1835 last_update_login = fnd_global.login_id,
1836 attribute1 =l_attr_1,
1837 attribute2 =l_attr_2,
1838 attribute3 =l_attr_3,
1839 attribute4 =l_attr_4,
1840 attribute5 =l_attr_5,
1841 attribute6 =l_attr_6,
1842 attribute7 =l_attr_7,
1843 attribute8 =l_attr_8,
1844 attribute9 =l_attr_9,
1845 attribute10=l_attr_10,
1846 attribute11=l_attr_11,
1847 attribute12=l_attr_12,
1848 attribute13=l_attr_13,
1849 attribute14=l_attr_14,
1850 attribute15=l_attr_15
1851 where pull_sequence_id = l_prod_pull_seq_id
1852 returning source_type into l_source_type;
1853
1854 if l_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
1855 FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards --Bug#14230963
1856 (p_pull_seq_id => l_prod_pull_seq_id,
1857 x_retcode => l_ret_status,
1858 x_err_msg => l_err_msg);
1859 end if;
1860
1861 -- Generate Plan cards as required.
1862 if l_calc_flag = 2 then -- Calculate cards. Also exclude Temporary cards.
1863 select count(1)
1864 into l_curr_card_count
1865 from mtl_kanban_cards
1866 where pull_sequence_id = l_prod_pull_seq_id
1867 and max_replenishments is null
1868 and disable_date is null
1869 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
1870 and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned);
1871
1872 l_card_count := 0;
1873 if l_curr_card_count < l_calculate_card_count then
1874
1875 SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUBINVENTORY_NAME,SOURCE_TYPE,LOCATOR_ID,SOURCE_ORGANIZATION_ID,SOURCE_SUBINVENTORY,SOURCE_LOCATOR_ID,WIP_LINE_ID
1876 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
1877 FROM MTL_KANBAN_PULL_SEQUENCES
1878 WHERE PULL_SEQUENCE_ID = l_prod_pull_seq_id;
1879
1880 while (l_card_count < (l_calculate_card_count - l_curr_card_count))
1881 loop
1882 l_card_count := l_card_count+1;
1883 r_kanban_card_rec := null;
1884 r_kanban_card_rec.pull_sequence_id := l_prod_pull_seq_id;
1885 r_kanban_card_rec.kanban_card_id := null;
1886 r_kanban_card_rec.kanban_card_number := null;
1887 r_kanban_card_rec.inventory_item_code := null;
1888 r_kanban_card_rec.inventory_item_id := l_item_id;
1889 r_kanban_card_rec.organization_code := null;
1890 r_kanban_card_rec.organization_id := l_org_id;
1891 r_kanban_card_rec.subinventory_name := l_subinv;
1892 r_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_New;
1893 if l_eff_date is null then
1894 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Active;
1895 else
1896 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Planned;
1897 end if;
1898 r_kanban_card_rec.kanban_card_type := INV_Kanban_Pvt.g_card_type_replenishable;
1899 r_kanban_card_rec.source_type := l_src_type;
1900 r_kanban_card_rec.kanban_size := l_kanban_size;
1901 r_kanban_card_rec.locator_id := l_locator;
1902 r_kanban_card_rec.supplier_id := null;
1903 r_kanban_card_rec.supplier_name := null;
1904 r_kanban_card_rec.supplier_site_id := null;
1905 r_kanban_card_rec.supplier_site_code := null;
1906 r_kanban_card_rec.source_organization_id := l_src_org_id;
1907 r_kanban_card_rec.source_subinventory := l_src_subinv;
1908 r_kanban_card_rec.source_locator_id := l_src_locator;
1909 r_kanban_card_rec.current_replnsh_cycle_id := null;
1910 r_kanban_card_rec.kanban_error_code := null;
1911 r_kanban_card_rec.wip_line_code := null;
1912 r_kanban_card_rec.wip_line_id := l_wip_line_id;
1913 r_kanban_card_rec.attribute_category := null;
1914 r_kanban_card_rec.attribute1 := null;
1915 r_kanban_card_rec.attribute2 := null;
1916 r_kanban_card_rec.attribute3 := null;
1917 r_kanban_card_rec.attribute4 := null;
1918 r_kanban_card_rec.attribute5 := null;
1919 r_kanban_card_rec.attribute6 := null;
1920 r_kanban_card_rec.attribute7 := null;
1921 r_kanban_card_rec.attribute8 := null;
1922 r_kanban_card_rec.attribute9 := null;
1923 r_kanban_card_rec.attribute10 := null;
1924 r_kanban_card_rec.attribute11 := null;
1925 r_kanban_card_rec.attribute12 := null;
1926 r_kanban_card_rec.attribute13 := null;
1927 r_kanban_card_rec.attribute14 := null;
1928 r_kanban_card_rec.attribute15 := null;
1929 r_kanban_card_rec.last_print_date := null;
1930 r_kanban_card_rec.last_update_date := sysdate;
1931 r_kanban_card_rec.last_update_by := fnd_global.user_id;
1932 r_kanban_card_rec.creation_date := sysdate;
1933 r_kanban_card_rec.created_by := fnd_global.user_id;
1934 r_kanban_card_rec.last_update_login := fnd_global.login_id;
1935 r_kanban_card_rec.request_id := null;
1936 r_kanban_card_rec.program_application_id := null;
1937 r_kanban_card_rec.program_id := null;
1938 r_kanban_card_rec.program_update_date := null;
1939 r_kanban_card_rec.replenishment_count := null;
1940 r_kanban_card_rec.replacement_flag := null;
1941 if l_card_count <= nvl(l_no_of_temp_cards,0) then
1942 r_kanban_card_rec.max_replenishments := l_no_of_cycles;
1943 r_kanban_card_rec.disable_date := l_temp_exp_date;
1944 else
1945 r_kanban_card_rec.max_replenishments := null;
1946 r_kanban_card_rec.disable_date := null;
1947 end if;
1948 /* Call API Without Validation to Make planning UI Lighter.
1949 FLM_KANBAN_PUB.process_cards(p_kanban_card_rec => r_kanban_card_rec
1950 ,p_transaction_type => FLM_KANBAN_MASSLOAD.KANBAN_ADD
1951 ,p_commit_flag => 'N'
1952 ,x_ret_status => l_ret_status
1953 ,x_err_msg => l_err_msg);
1954 */
1955 FLM_KANBAN_PUB.process_kanban_cards(p_kanban_card_rec => r_kanban_card_rec
1956 ,p_release_kanban_flag => l_release_kanban_flag
1957 ,p_transaction_type => FLM_KANBAN_MASSLOAD.KANBAN_ADD
1958 ,x_ret_status => l_ret_status
1959 ,x_err_msg => l_err_msg);
1960
1961 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
1962 x_ret_status := -1;
1963 exit;
1964 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1965 x_ret_status := -1;
1966 exit;
1967 END IF;
1968 end loop;
1969 end if;
1970 -- Convert Plan cards into Active if needed.
1971 if l_eff_date is null then
1972 select count(1)
1973 into l_curr_card_count
1974 from mtl_kanban_cards
1975 where pull_sequence_id = l_prod_pull_seq_id
1976 and max_replenishments is null
1977 and disable_date is null
1978 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
1979 and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold);
1980
1981 l_upd_card_count := 0;
1982 --l_calculate_card_count := l_calculate_card_count-l_card_count;
1983 while (l_upd_card_count < (l_calculate_card_count - l_curr_card_count))
1984 loop
1985 l_card_count := l_card_count+1;
1986 l_upd_card_count := l_upd_card_count+1;
1987 l_kanban_card_rec := null;
1988 -- select plan cards and convert to active cards if needed.
1989 select
1990 kanban_card_id
1991 , kanban_card_number
1992 , pull_sequence_id
1993 , inventory_item_id
1994 , organization_id
1995 , subinventory_name
1996 , supply_status
1997 , INV_Kanban_PVT.G_Card_Status_Active
1998 , INV_Kanban_Pvt.g_card_type_replenishable
1999 , source_type
2000 , kanban_size
2001 , creation_date
2002 , created_by
2003 , last_update_login
2004 , last_print_date
2005 , locator_id
2006 , supplier_id
2007 , supplier_site_id
2008 , source_organization_id
2009 , source_subinventory
2010 , source_locator_id
2011 , wip_line_id
2012 , current_replnsh_cycle_id
2013 , attribute_category
2014 , attribute1
2015 , attribute2
2016 , attribute3
2017 , attribute4
2018 , attribute5
2019 , attribute6
2020 , attribute7
2021 , attribute8
2022 , attribute9
2023 , attribute10
2024 , attribute11
2025 , attribute12
2026 , attribute13
2027 , attribute14
2028 , attribute15
2029 , replenishment_count
2030 , max_replenishments
2031 , disable_date
2032 , replacement_flag
2033 INTO l_kanban_card_rec.kanban_card_id
2034 , l_kanban_card_rec.kanban_card_number
2035 , l_kanban_card_rec.pull_sequence_id
2036 , l_kanban_card_rec.inventory_item_id
2037 , l_kanban_card_rec.organization_id
2038 , l_kanban_card_rec.subinventory_name
2039 , l_kanban_card_rec.supply_status
2040 , l_kanban_card_rec.card_status
2041 , l_kanban_card_rec.kanban_card_type
2042 , l_kanban_card_rec.source_type
2043 , l_kanban_card_rec.kanban_size
2044 , l_kanban_card_rec.creation_date
2045 , l_kanban_card_rec.created_by
2046 , l_kanban_card_rec.last_update_login
2047 , l_kanban_card_rec.last_print_date
2048 , l_kanban_card_rec.locator_id
2049 , l_kanban_card_rec.supplier_id
2050 , l_kanban_card_rec.supplier_site_id
2051 , l_kanban_card_rec.source_organization_id
2052 , l_kanban_card_rec.source_subinventory
2053 , l_kanban_card_rec.source_locator_id
2054 , l_kanban_card_rec.wip_line_id
2055 , l_kanban_card_rec.current_replnsh_cycle_id
2056 , l_kanban_card_rec.attribute_category
2057 , l_kanban_card_rec.attribute1
2058 , l_kanban_card_rec.attribute2
2059 , l_kanban_card_rec.attribute3
2060 , l_kanban_card_rec.attribute4
2061 , l_kanban_card_rec.attribute5
2062 , l_kanban_card_rec.attribute6
2063 , l_kanban_card_rec.attribute7
2064 , l_kanban_card_rec.attribute8
2065 , l_kanban_card_rec.attribute9
2066 , l_kanban_card_rec.attribute10
2067 , l_kanban_card_rec.attribute11
2068 , l_kanban_card_rec.attribute12
2069 , l_kanban_card_rec.attribute13
2070 , l_kanban_card_rec.attribute14
2071 , l_kanban_card_rec.attribute15
2072 , l_kanban_card_rec.replenishment_count
2073 , l_kanban_card_rec.max_replenishments
2074 , l_kanban_card_rec.disable_date
2075 , l_kanban_card_rec.replacement_flag
2076 from mtl_kanban_cards
2077 where pull_sequence_id = l_prod_pull_seq_id
2078 and card_status = INV_Kanban_PVT.G_Card_Status_Planned
2079 and max_replenishments is null
2080 and disable_date is null
2081 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
2082 and rownum = 1;
2083
2084 if l_card_count <= nvl(l_no_of_temp_cards,0) then
2085 l_kanban_card_rec.max_replenishments := l_no_of_cycles;
2086 l_kanban_card_rec.disable_date := l_temp_exp_date;
2087 else
2088 l_kanban_card_rec.max_replenishments := null;
2089 l_kanban_card_rec.disable_date := null;
2090 end if;
2091
2092 inv_kanbancard_pkg.update_row(
2093 x_return_status => l_ret_status
2094 , p_kanban_card_id => l_kanban_card_rec.kanban_card_id
2095 , p_kanban_card_number => l_kanban_card_rec.kanban_card_number
2096 , p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
2097 , p_inventory_item_id => l_kanban_card_rec.inventory_item_id
2098 , p_organization_id => l_kanban_card_rec.organization_id
2099 , p_subinventory_name => l_kanban_card_rec.subinventory_name
2100 , p_supply_status => l_kanban_card_rec.supply_status
2101 , p_card_status => l_kanban_card_rec.card_status
2102 , p_kanban_card_type => l_kanban_card_rec.kanban_card_type
2103 , p_source_type => l_kanban_card_rec.source_type
2104 , p_kanban_size => l_kanban_card_rec.kanban_size
2105 , p_last_update_date => sysdate
2106 , p_last_updated_by => fnd_global.user_id
2107 , p_creation_date => l_kanban_card_rec.creation_date
2108 , p_created_by => l_kanban_card_rec.created_by
2109 , p_last_update_login => fnd_global.login_id
2110 , p_last_print_date => l_kanban_card_rec.last_print_date
2111 , p_locator_id => l_kanban_card_rec.locator_id
2112 , p_supplier_id => l_kanban_card_rec.supplier_id
2113 , p_supplier_site_id => l_kanban_card_rec.supplier_site_id
2114 , p_source_organization_id => l_kanban_card_rec.source_organization_id
2115 , p_source_subinventory => l_kanban_card_rec.source_subinventory
2116 , p_source_locator_id => l_kanban_card_rec.source_locator_id
2117 , p_wip_line_id => l_kanban_card_rec.wip_line_id
2118 , p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id
2119 , p_document_type => null
2120 , p_document_header_id => null
2121 , p_document_detail_id => null
2122 , p_error_code => null
2123 , p_attribute_category => l_kanban_card_rec.attribute_category
2124 , p_attribute1 => l_kanban_card_rec.attribute1
2125 , p_attribute2 => l_kanban_card_rec.attribute2
2126 , p_attribute3 => l_kanban_card_rec.attribute3
2127 , p_attribute4 => l_kanban_card_rec.attribute4
2128 , p_attribute5 => l_kanban_card_rec.attribute5
2129 , p_attribute6 => l_kanban_card_rec.attribute6
2130 , p_attribute7 => l_kanban_card_rec.attribute7
2131 , p_attribute8 => l_kanban_card_rec.attribute8
2132 , p_attribute9 => l_kanban_card_rec.attribute9
2133 , p_attribute10 => l_kanban_card_rec.attribute10
2134 , p_attribute11 => l_kanban_card_rec.attribute11
2135 , p_attribute12 => l_kanban_card_rec.attribute12
2136 , p_attribute13 => l_kanban_card_rec.attribute13
2137 , p_attribute14 => l_kanban_card_rec.attribute14
2138 , p_attribute15 => l_kanban_card_rec.attribute15
2139 , p_replenishment_count => l_kanban_card_rec.replenishment_count
2140 , p_max_replenishments => l_kanban_card_rec.max_replenishments
2141 , p_disable_date => l_kanban_card_rec.disable_date
2142 , p_replacement_flag => l_kanban_card_rec.replacement_flag);
2143
2144 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2145 x_ret_status := -1;
2146 exit;
2147 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2148 x_ret_status := -1;
2149 exit;
2150 END IF;
2151 end loop;
2152 end if;
2153 end if;
2154 end loop;
2155 dbms_sql.close_cursor(l_cursor);
2156
2157 commit;
2158 x_ret_status := 0;
2159 Exception
2160 when others then
2161 x_ret_status := -1;
2162 End update_production;
2163
2164 procedure update_production(p_plan_pull_seq_id IN NUMBER,
2165 x_ret_status OUT NOCOPY Number)
2166 IS
2167 l_sql varchar2(4000);
2168 l_cursor integer;
2169 l_sql_exec integer;
2170 l_plan_pull_seq_id Number;
2171 l_no_of_cards Number;
2172 l_card_size Number;
2173 l_calc_flag Number;
2174 l_eff_date Date;
2175 l_no_of_temp_cards Number;
2176 l_no_of_cycles Number;
2177 l_temp_exp_date Date;
2178 l_imp_plan varchar2(10);
2179 l_plan_st_dt Date;
2180 l_plan_end_dt Date;
2181 l_prod_pull_seq_id Number;
2182 l_future_size Number;
2183 l_future_cards Number;
2184 l_avg_demand Number;
2185 l_moq Number;
2186 l_alloc_perc Number;
2187 l_lead_time Number;
2188 l_lot_multiplier Number;
2189 l_safety_stock Number;
2190 l_curr_card_count Number;
2191 l_card_count Number;
2192 l_upd_card_count Number;
2193 r_kanban_card_rec FLM_EKANBAN_PUB.kanban_card_rec_type;
2194 l_kanban_card_rec FLM_EKANBAN_PUB.kanban_card_rec_type;
2195 l_item_id Number;
2196 l_org_id Number;
2197 l_subinv varchar2(20);
2198 l_src_type Number;
2199 l_kanban_size Number;
2200 l_locator Number;
2201 l_src_org_id Number;
2202 l_src_subinv varchar2(20);
2203 l_src_locator Number;
2204 l_wip_line_id Number;
2205 l_attr_1 varchar2(150);
2206 l_attr_2 varchar2(150);
2207 l_attr_3 varchar2(150);
2208 l_attr_4 varchar2(150);
2209 l_attr_5 varchar2(150);
2210 l_attr_6 varchar2(150);
2211 l_attr_7 varchar2(150);
2212 l_attr_8 varchar2(150);
2213 l_attr_9 varchar2(150);
2214 l_attr_10 varchar2(150);
2215 l_attr_11 varchar2(150);
2216 l_attr_12 varchar2(150);
2217 l_attr_13 varchar2(150);
2218 l_attr_14 varchar2(150);
2219 l_attr_15 varchar2(150);
2220 l_ret_status VARCHAR2(1);
2221 l_err_msg VARCHAR2(2000);
2222 l_calculate_card_count Number;
2223 l_release_kanban_flag Number;
2224 l_source_type Number;
2225 Begin
2226
2227 select plan.pull_sequence_id,nvl(plan.FUTURE_NO_OF_CARDS,plan.Number_of_cards),nvl(plan.FUTURE_CARD_SIZE,plan.Kanban_size),
2228 plan.Calculate_Kanban_flag,plan.PLANNING_EFFECTIVITY,nvl(plan.NO_OF_TEMP_CARDS,0),plan.NO_OF_TEMP_CYCLES,plan.TEMP_EXPIRY_DATE,
2229 plan.LAST_IMPLEMENTED_PLAN,plan.plan_start_date,plan.plan_end_date,prod.pull_sequence_id,
2230 plan.avg_dependent_demand,plan.minimum_order_quantity,plan.allocation_percent,plan.replenishment_lead_time,plan.fixed_lot_multiplier,plan.safety_stock_days,
2231 plan.attribute1,plan.attribute2,plan.attribute3,plan.attribute4,plan.attribute5,plan.attribute6,plan.attribute7,plan.attribute8,
2232 plan.attribute9,plan.attribute10,plan.attribute11,plan.attribute12,plan.attribute13,plan.attribute14,plan.attribute15,prod.release_kanban_flag
2233 into l_plan_pull_seq_id,l_no_of_cards,l_card_size,l_calc_flag,l_eff_date,l_no_of_temp_cards,l_no_of_cycles,
2234 l_temp_exp_date,l_imp_plan,l_plan_st_dt,l_plan_end_dt,l_prod_pull_seq_id, l_avg_demand, l_moq, l_alloc_perc,
2235 l_lead_time,l_lot_multiplier,l_safety_stock, l_attr_1, l_attr_2, l_attr_3, l_attr_4, l_attr_5, l_attr_6, l_attr_7,
2236 l_attr_8, l_attr_9, l_attr_10, l_attr_11, l_attr_12, l_attr_13, l_attr_14, l_attr_15,l_release_kanban_flag
2237 from mtl_kanban_pull_sequences plan,mtl_kanban_pull_sequences prod
2238 where plan.pull_sequence_id = p_plan_pull_seq_id
2239 and plan.organization_id = prod.organization_id
2240 and plan.inventory_item_id = prod.inventory_item_id
2241 and plan.subinventory_name = prod.subinventory_name
2242 and nvl(plan.locator_id,-5) = nvl(prod.locator_id,-5)
2243 and prod.kanban_plan_id = -1
2244 and plan.kanban_plan_id <> -1;
2245
2246 l_kanban_size := l_card_size;
2247 if l_calc_flag = 1 then -- Calculate Size
2248 l_no_of_cards := null;
2249 else
2250 l_card_size := null;
2251 end if;
2252 l_calculate_card_count := l_no_of_cards+l_no_of_temp_cards;
2253 l_release_kanban_flag := 1;
2254 if l_eff_date is null or trunc(l_eff_date) < sysdate then
2255
2256 l_eff_date := null;
2257 l_future_size := null;
2258 l_future_cards := null;
2259 end if;
2260 if trunc(l_eff_date) > sysdate then
2261
2262 l_future_size := l_card_size;
2263 l_future_cards := l_no_of_cards;
2264 l_no_of_cards := null;
2265 l_card_size := null;
2266
2267 end if;
2268
2269 -- Update Planning Pull sequence
2270 update mtl_kanban_pull_sequences
2271 set future_no_of_cards = Number_of_cards,
2272 future_card_size = Kanban_size,
2273 planning_effectivity = null,
2274 no_of_temp_cards = null,
2275 no_of_temp_cycles = null,
2276 temp_expiry_date = null
2277 where pull_sequence_id = l_plan_pull_seq_id;
2278
2279 -- Update Production pull sequence
2280 update mtl_kanban_pull_sequences
2281 set number_of_cards = decode(Calculate_Kanban_flag,2,nvl(l_no_of_cards,number_of_cards),number_of_cards),
2282 future_no_of_cards = decode(l_eff_date,null,null,nvl(l_future_cards,number_of_cards)),
2283 kanban_size = decode(Calculate_Kanban_flag,1,nvl(l_card_size,kanban_size),kanban_size),
2284 future_card_size = decode(l_eff_date,null,null,nvl(l_future_size,kanban_size)),
2285 planning_effectivity = l_eff_date,
2286 last_implemented_plan = l_imp_plan,
2287 plan_start_date = l_plan_st_dt,
2288 plan_end_date = l_plan_end_dt,
2289 avg_dependent_demand = l_avg_demand,
2290 minimum_order_quantity = l_moq,
2291 allocation_percent = l_alloc_perc,
2292 replenishment_lead_time = l_lead_time,
2293 fixed_lot_multiplier = l_lot_multiplier,
2294 safety_stock_days = l_safety_stock,
2295 last_update_date = sysdate,
2296 last_updated_by = fnd_global.user_id,
2297 last_update_login = fnd_global.login_id,
2298 attribute1 =l_attr_1,
2299 attribute2 =l_attr_2,
2300 attribute3 =l_attr_3,
2301 attribute4 =l_attr_4,
2302 attribute5 =l_attr_5,
2303 attribute6 =l_attr_6,
2304 attribute7 =l_attr_7,
2305 attribute8 =l_attr_8,
2306 attribute9 =l_attr_9,
2307 attribute10=l_attr_10,
2308 attribute11=l_attr_11,
2309 attribute12=l_attr_12,
2310 attribute13=l_attr_13,
2311 attribute14=l_attr_14,
2312 attribute15=l_attr_15
2313 where pull_sequence_id = l_prod_pull_seq_id
2314 returning source_type into l_source_type;
2315
2316
2317 if l_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
2318 FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards
2319 (p_pull_seq_id => l_prod_pull_seq_id,
2320 x_retcode => l_ret_status,
2321 x_err_msg => l_err_msg);
2322 end if;
2323
2324 -- Generate Plan cards as required. Also exclude Temporary cards.
2325 if l_calc_flag = 2 then -- Calculate cards
2326 select count(1)
2327 into l_curr_card_count
2328 from mtl_kanban_cards
2329 where pull_sequence_id = l_prod_pull_seq_id
2330 and max_replenishments is null
2331 and disable_date is null
2332 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
2333 and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned);
2334
2335 l_card_count := 0;
2336 if l_curr_card_count < l_calculate_card_count then
2337
2338 SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUBINVENTORY_NAME,SOURCE_TYPE,LOCATOR_ID,SOURCE_ORGANIZATION_ID,SOURCE_SUBINVENTORY,SOURCE_LOCATOR_ID,WIP_LINE_ID
2339 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
2340 FROM MTL_KANBAN_PULL_SEQUENCES
2341 WHERE PULL_SEQUENCE_ID = l_prod_pull_seq_id;
2342 while (l_card_count < (l_calculate_card_count - l_curr_card_count))
2343 loop
2344 l_card_count := l_card_count+1;
2345 r_kanban_card_rec := null;
2346 r_kanban_card_rec.pull_sequence_id := l_prod_pull_seq_id;
2347 r_kanban_card_rec.kanban_card_id := null;
2348 r_kanban_card_rec.kanban_card_number := null;
2349 r_kanban_card_rec.inventory_item_code := null;
2350 r_kanban_card_rec.inventory_item_id := l_item_id;
2351 r_kanban_card_rec.organization_code := null;
2352 r_kanban_card_rec.organization_id := l_org_id;
2353 r_kanban_card_rec.subinventory_name := l_subinv;
2354 r_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_New;
2355 if l_eff_date is null then
2356 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Active;
2357 else
2358 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Planned;
2359 end if;
2360 r_kanban_card_rec.kanban_card_type := INV_Kanban_Pvt.g_card_type_replenishable;
2361 r_kanban_card_rec.source_type := l_src_type;
2362 r_kanban_card_rec.kanban_size := l_kanban_size;
2363 r_kanban_card_rec.locator_id := l_locator;
2364 r_kanban_card_rec.supplier_id := null;
2365 r_kanban_card_rec.supplier_name := null;
2366 r_kanban_card_rec.supplier_site_id := null;
2367 r_kanban_card_rec.supplier_site_code := null;
2368 r_kanban_card_rec.source_organization_id := l_src_org_id;
2369 r_kanban_card_rec.source_subinventory := l_src_subinv;
2370 r_kanban_card_rec.source_locator_id := l_src_locator;
2371 r_kanban_card_rec.current_replnsh_cycle_id := null;
2372 r_kanban_card_rec.kanban_error_code := null;
2373 r_kanban_card_rec.wip_line_code := null;
2374 r_kanban_card_rec.wip_line_id := l_wip_line_id;
2375 r_kanban_card_rec.attribute_category := null;
2376 r_kanban_card_rec.attribute1 := null;
2377 r_kanban_card_rec.attribute2 := null;
2378 r_kanban_card_rec.attribute3 := null;
2379 r_kanban_card_rec.attribute4 := null;
2380 r_kanban_card_rec.attribute5 := null;
2381 r_kanban_card_rec.attribute6 := null;
2382 r_kanban_card_rec.attribute7 := null;
2383 r_kanban_card_rec.attribute8 := null;
2384 r_kanban_card_rec.attribute9 := null;
2385 r_kanban_card_rec.attribute10 := null;
2386 r_kanban_card_rec.attribute11 := null;
2387 r_kanban_card_rec.attribute12 := null;
2388 r_kanban_card_rec.attribute13 := null;
2389 r_kanban_card_rec.attribute14 := null;
2390 r_kanban_card_rec.attribute15 := null;
2391 r_kanban_card_rec.last_print_date := null;
2392 r_kanban_card_rec.last_update_date := sysdate;
2393 r_kanban_card_rec.last_update_by := fnd_global.user_id;
2394 r_kanban_card_rec.creation_date := sysdate;
2395 r_kanban_card_rec.created_by := fnd_global.user_id;
2396 r_kanban_card_rec.last_update_login := fnd_global.login_id;
2397 r_kanban_card_rec.request_id := null;
2398 r_kanban_card_rec.program_application_id := null;
2399 r_kanban_card_rec.program_id := null;
2400 r_kanban_card_rec.program_update_date := null;
2401 r_kanban_card_rec.replenishment_count := null;
2402 r_kanban_card_rec.replacement_flag := null;
2403 if l_card_count <= nvl(l_no_of_temp_cards,0) then
2404 r_kanban_card_rec.max_replenishments := l_no_of_cycles;
2405 r_kanban_card_rec.disable_date := l_temp_exp_date;
2406 else
2407 r_kanban_card_rec.max_replenishments := null;
2408 r_kanban_card_rec.disable_date := null;
2409 end if;
2410 /* Call API Without Validation to Make planning UI Lighter.
2411 FLM_KANBAN_PUB.process_cards(p_kanban_card_rec => r_kanban_card_rec
2412 ,p_transaction_type => FLM_KANBAN_MASSLOAD.KANBAN_ADD
2413 ,p_commit_flag => 'N'
2414 ,x_ret_status => l_ret_status
2415 ,x_err_msg => l_err_msg);
2416 */
2417 FLM_KANBAN_PUB.process_kanban_cards(p_kanban_card_rec => r_kanban_card_rec
2418 ,p_release_kanban_flag => l_release_kanban_flag
2419 ,p_transaction_type => FLM_KANBAN_MASSLOAD.KANBAN_ADD
2420 ,x_ret_status => l_ret_status
2421 ,x_err_msg => l_err_msg);
2422 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2423 x_ret_status := -1;
2424 exit;
2425 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2426 x_ret_status := -1;
2427 exit;
2428 END IF;
2429 end loop;
2430 end if;
2431 -- Convert Plan cards into Active if needed.
2432 if l_eff_date is null then
2433 select count(1)
2434 into l_curr_card_count
2435 from mtl_kanban_cards
2436 where pull_sequence_id = l_prod_pull_seq_id
2437 and max_replenishments is null
2438 and disable_date is null
2439 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
2440 and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold);
2441 l_upd_card_count := 0;
2442 --l_calculate_card_count := l_calculate_card_count - l_card_count;
2443 while (l_upd_card_count < (l_calculate_card_count - l_curr_card_count))
2444 loop
2445 l_card_count := l_card_count+1;
2446 l_upd_card_count := l_upd_card_count+1;
2447 l_kanban_card_rec := null;
2448 -- select plan cards and convert to active cards if needed.
2449 select
2450 kanban_card_id
2451 , kanban_card_number
2452 , pull_sequence_id
2453 , inventory_item_id
2454 , organization_id
2455 , subinventory_name
2456 , supply_status
2457 , INV_Kanban_PVT.G_Card_Status_Active
2458 , INV_Kanban_Pvt.g_card_type_replenishable
2459 , source_type
2460 , kanban_size
2461 , creation_date
2462 , created_by
2463 , last_update_login
2464 , last_print_date
2465 , locator_id
2466 , supplier_id
2467 , supplier_site_id
2468 , source_organization_id
2469 , source_subinventory
2470 , source_locator_id
2471 , wip_line_id
2472 , current_replnsh_cycle_id
2473 , attribute_category
2474 , attribute1
2475 , attribute2
2476 , attribute3
2477 , attribute4
2478 , attribute5
2479 , attribute6
2480 , attribute7
2481 , attribute8
2482 , attribute9
2483 , attribute10
2484 , attribute11
2485 , attribute12
2486 , attribute13
2487 , attribute14
2488 , attribute15
2489 , replenishment_count
2490 , max_replenishments
2491 , disable_date
2492 , replacement_flag
2493 INTO l_kanban_card_rec.kanban_card_id
2494 , l_kanban_card_rec.kanban_card_number
2495 , l_kanban_card_rec.pull_sequence_id
2496 , l_kanban_card_rec.inventory_item_id
2497 , l_kanban_card_rec.organization_id
2498 , l_kanban_card_rec.subinventory_name
2499 , l_kanban_card_rec.supply_status
2500 , l_kanban_card_rec.card_status
2501 , l_kanban_card_rec.kanban_card_type
2502 , l_kanban_card_rec.source_type
2503 , l_kanban_card_rec.kanban_size
2504 , l_kanban_card_rec.creation_date
2505 , l_kanban_card_rec.created_by
2506 , l_kanban_card_rec.last_update_login
2507 , l_kanban_card_rec.last_print_date
2508 , l_kanban_card_rec.locator_id
2509 , l_kanban_card_rec.supplier_id
2510 , l_kanban_card_rec.supplier_site_id
2511 , l_kanban_card_rec.source_organization_id
2512 , l_kanban_card_rec.source_subinventory
2513 , l_kanban_card_rec.source_locator_id
2514 , l_kanban_card_rec.wip_line_id
2515 , l_kanban_card_rec.current_replnsh_cycle_id
2516 , l_kanban_card_rec.attribute_category
2517 , l_kanban_card_rec.attribute1
2518 , l_kanban_card_rec.attribute2
2519 , l_kanban_card_rec.attribute3
2520 , l_kanban_card_rec.attribute4
2521 , l_kanban_card_rec.attribute5
2522 , l_kanban_card_rec.attribute6
2523 , l_kanban_card_rec.attribute7
2524 , l_kanban_card_rec.attribute8
2525 , l_kanban_card_rec.attribute9
2526 , l_kanban_card_rec.attribute10
2527 , l_kanban_card_rec.attribute11
2528 , l_kanban_card_rec.attribute12
2529 , l_kanban_card_rec.attribute13
2530 , l_kanban_card_rec.attribute14
2531 , l_kanban_card_rec.attribute15
2532 , l_kanban_card_rec.replenishment_count
2533 , l_kanban_card_rec.max_replenishments
2534 , l_kanban_card_rec.disable_date
2535 , l_kanban_card_rec.replacement_flag
2536 from mtl_kanban_cards
2537 where pull_sequence_id = l_prod_pull_seq_id
2538 and card_status = INV_Kanban_PVT.G_Card_Status_Planned
2539 and max_replenishments is null
2540 and disable_date is null
2541 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
2542 and rownum = 1;
2543 if l_card_count <= nvl(l_no_of_temp_cards,0) then
2544 l_kanban_card_rec.max_replenishments := l_no_of_cycles;
2545 l_kanban_card_rec.disable_date := l_temp_exp_date;
2546 else
2547 l_kanban_card_rec.max_replenishments := null;
2548 l_kanban_card_rec.disable_date := null;
2549 end if;
2550 inv_kanbancard_pkg.update_row(
2551 x_return_status => l_ret_status
2552 , p_kanban_card_id => l_kanban_card_rec.kanban_card_id
2553 , p_kanban_card_number => l_kanban_card_rec.kanban_card_number
2554 , p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
2555 , p_inventory_item_id => l_kanban_card_rec.inventory_item_id
2556 , p_organization_id => l_kanban_card_rec.organization_id
2557 , p_subinventory_name => l_kanban_card_rec.subinventory_name
2558 , p_supply_status => l_kanban_card_rec.supply_status
2559 , p_card_status => l_kanban_card_rec.card_status
2560 , p_kanban_card_type => l_kanban_card_rec.kanban_card_type
2561 , p_source_type => l_kanban_card_rec.source_type
2562 , p_kanban_size => l_kanban_card_rec.kanban_size
2563 , p_last_update_date => sysdate
2564 , p_last_updated_by => fnd_global.user_id
2565 , p_creation_date => l_kanban_card_rec.creation_date
2566 , p_created_by => l_kanban_card_rec.created_by
2567 , p_last_update_login => fnd_global.login_id
2568 , p_last_print_date => l_kanban_card_rec.last_print_date
2569 , p_locator_id => l_kanban_card_rec.locator_id
2570 , p_supplier_id => l_kanban_card_rec.supplier_id
2571 , p_supplier_site_id => l_kanban_card_rec.supplier_site_id
2572 , p_source_organization_id => l_kanban_card_rec.source_organization_id
2573 , p_source_subinventory => l_kanban_card_rec.source_subinventory
2574 , p_source_locator_id => l_kanban_card_rec.source_locator_id
2575 , p_wip_line_id => l_kanban_card_rec.wip_line_id
2576 , p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id
2577 , p_document_type => null
2578 , p_document_header_id => null
2579 , p_document_detail_id => null
2580 , p_error_code => null
2581 , p_attribute_category => l_kanban_card_rec.attribute_category
2582 , p_attribute1 => l_kanban_card_rec.attribute1
2583 , p_attribute2 => l_kanban_card_rec.attribute2
2584 , p_attribute3 => l_kanban_card_rec.attribute3
2585 , p_attribute4 => l_kanban_card_rec.attribute4
2586 , p_attribute5 => l_kanban_card_rec.attribute5
2587 , p_attribute6 => l_kanban_card_rec.attribute6
2588 , p_attribute7 => l_kanban_card_rec.attribute7
2589 , p_attribute8 => l_kanban_card_rec.attribute8
2590 , p_attribute9 => l_kanban_card_rec.attribute9
2591 , p_attribute10 => l_kanban_card_rec.attribute10
2592 , p_attribute11 => l_kanban_card_rec.attribute11
2593 , p_attribute12 => l_kanban_card_rec.attribute12
2594 , p_attribute13 => l_kanban_card_rec.attribute13
2595 , p_attribute14 => l_kanban_card_rec.attribute14
2596 , p_attribute15 => l_kanban_card_rec.attribute15
2597 , p_replenishment_count => l_kanban_card_rec.replenishment_count
2598 , p_max_replenishments => l_kanban_card_rec.max_replenishments
2599 , p_disable_date => l_kanban_card_rec.disable_date
2600 , p_replacement_flag => l_kanban_card_rec.replacement_flag);
2601
2602 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2603 x_ret_status := -1;
2604 exit;
2605 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2606 x_ret_status := -1;
2607 exit;
2608 END IF;
2609 end loop;
2610 end if;
2611 end if;
2612 commit;
2613 x_ret_status := 0;
2614 Exception
2615 when others then
2616 x_ret_status := -1;
2617 End update_production;
2618
2619 PROCEDURE launch_request(p_user_id IN NUMBER,
2620 p_responsibility_id IN NUMBER,
2621 p_resp_appl_id IN NUMBER,
2622 p_organization_id IN NUMBER,
2623 p_kanban_plan_id IN NUMBER,
2624 p_from_item IN VARCHAR2,
2625 p_to_item IN VARCHAR2,
2626 p_category_set IN VARCHAR2,
2627 p_from_category IN VARCHAR2,
2628 p_to_category IN VARCHAR2,
2629 p_bom_effectivity IN DATE,
2630 p_start_date IN DATE,
2631 p_cutoff_date IN DATE) AS
2632 l_category_set_id NUMBER;
2633 l_request_id NUMBER;
2634 l_date_format VARCHAR2(25) := 'YYYY/MM/DD HH24:MI:SS';
2635 PRAGMA AUTONOMOUS_TRANSACTION;
2636 BEGIN
2637
2638 SELECT category_set_id
2639 INTO l_category_set_id
2640 FROM mtl_default_sets_view
2641 WHERE functional_area_id=3
2642 AND category_set_name = Nvl(p_category_set, category_set_name);
2643
2644 FND_GLOBAL.apps_initialize (p_user_id,p_responsibility_id,p_resp_appl_id);
2645
2646 l_request_id := FND_REQUEST.submit_request( 'MRP',
2647 'MRPKQCAL',
2648 NULL,
2649 NULL,
2650 FALSE,
2651 p_organization_id,
2652 p_kanban_plan_id,
2653 p_from_item,
2654 p_to_item,
2655 l_category_set_id,
2656 NULL,
2657 p_from_category,
2658 p_to_category,
2659 to_char(p_bom_effectivity,l_date_format),
2660 to_char(p_start_date, l_date_format),
2661 to_char(p_cutoff_date, l_date_format),
2662 2);
2663
2664 UPDATE mrp_kanban_plans SET request_id = l_request_id WHERE kanban_plan_id = p_kanban_plan_id;
2665
2666 COMMIT;
2667
2668 END launch_request;
2669
2670 END FLM_KANBAN_PLANNING_UTIL;