1 PACKAGE BODY MRP_LINE_SCHEDULE_ALGORITHM AS
2 /* $Header: MRPLSCHB.pls 120.1.12010000.2 2008/08/20 19:25:03 adasa ship $ */
3
4 -- Global Variable to hold min wip_entity Id
5 G_WIP_ENTITY_ID NUMBER;
6
7 -- This cursor is a list of unique items in the flow schedules to be scheduled on
8 -- the specified line.
9
10 CURSOR item_list (p_org_id NUMBER) IS
11 SELECT DISTINCT primary_item_id
12 FROM wip_flow_schedules
13 WHERE request_id = USERENV('SESSIONID')
14 AND organization_id = p_org_id
15 AND wip_entity_id >= G_WIP_ENTITY_ID;
16
17 -- This cursor is a list of unique schedule groups in the flow schedules to be
18 -- scheduled on the specified line.
19
20 CURSOR schedule_group_list (p_org_id NUMBER) IS
21 SELECT DISTINCT schedule_group_id schedule_group
22 FROM wip_flow_schedules
23 WHERE request_id = USERENV('SESSIONID')
24 AND organization_id = p_org_id
25 AND wip_entity_id >= G_WIP_ENTITY_ID;
26
27 /* Fix for bug 2977987: Added columns primary_item_id, bom_revision_date
28 and routing_revision_date to be selected by the sursor. */
29 CURSOR fs_list (p_line_id NUMBER, p_org_id NUMBER) IS
30 SELECT wip_entity_id, schedule_group_id, scheduled_completion_date,
31 primary_item_id, bom_revision_date, routing_revision_date
32 FROM wip_flow_schedules
33 WHERE request_id = USERENV('SESSIONID')
34 AND line_id = p_line_id
35 AND organization_id = p_org_id
36 AND scheduled_flag = C_YES
37 AND wip_entity_id >= G_WIP_ENTITY_ID
38 ORDER BY scheduled_completion_date;
39
40 -- This cursor is used in the level daily rate algorithm. It retrieves the items
41 -- to be scheduled, ordered in descending order the fraction from calculation of
42 -- the production plan for each item. This is used for rounding.
43
44 CURSOR production_round (p_date NUMBER, p_query_id IN NUMBER) IS
45 SELECT number1, number2,number3
46 FROM mrp_form_query
47 WHERE date1 = to_date(p_date,'J')
48 AND query_id = p_query_id
49 ORDER BY number3 DESC;
50
51 -- This cursor is a list of existing flow schedules ordered by schedule group
52 -- and build sequence so that time stamps can be assigned to them.
53 -- hwenas: query never used, so decided to not change for bug#3783650
54 CURSOR flow_schedule_list (p_line_id NUMBER, p_org_id NUMBER, p_date NUMBER)
55 IS
56 SELECT wip_entity_id,planned_quantity,quantity_completed,
57 schedule_group_id,build_sequence,primary_item_id
58 FROM wip_flow_schedules
59 WHERE to_number(to_char(scheduled_completion_date,'J'))
60 = p_date
61 AND line_id = p_line_id
62 AND organization_id = p_org_id
63 AND scheduled_flag = C_YES
64 ORDER BY schedule_group_id,build_sequence;
65
66 -- This procedure constructs the main select cursor and returns
67 -- a handle to the calling functions
68
69 FUNCTION Create_Cursor( p_rule_id IN NUMBER,
70 p_org_id IN NUMBER,
71 p_line_id IN NUMBER,
72 p_order IN NUMBER,
73 p_type IN NUMBER,
74 p_item_id IN NUMBER)
75 RETURN INTEGER
76 IS
77 v_select VARCHAR2(5000);
78 dummy INTEGER;
79 cursor_name INTEGER;
80 v_seq_criteria VARCHAR2(500);
81 fs_select_rec fs_select_type;
82
83 BEGIN
84 V_PROCEDURE_NAME := 'Create_Cursor';
85 V_ERROR_LINE := 1;
86
87 v_seq_criteria := order_scheduling_rule(p_rule_id, p_order);
88
89 v_select := 'SELECT '||
90 ' fs.wip_entity_id wip_entity, '||
91 ' sol.creation_date creation_date, '||
92 ' NVL(sol.schedule_ship_date,fs.scheduled_completion_date) schedule_date,'||
93 ' sol.promise_date promise_date, '||
94 ' sol.request_date request_date, '||
95 ' sol.planning_priority planning_priority, '||
96 ' fs.primary_item_id primary_item_id, '||
97 ' fs.planned_quantity planned_quantity, '||
98 ' fs.schedule_group_id schedule_group_id '||
99 ' FROM oe_order_lines_all sol,wip_flow_schedules fs '||
100 ' WHERE fs.request_id = :v_session_id '||
101 ' AND fs.organization_id = :p_org_id '||
102 ' AND fs.line_id = :p_line_id '||
103 ' AND sol.line_id(+) = fs.demand_source_line '||
104 ' AND fs.scheduled_flag = 3 '||
105 ' AND :p_item_id > 0 ';
106
107 IF p_type <> C_NORM THEN
108 v_select := v_select || ' AND fs.primary_item_id = :p_item_id ';
109 END IF;
110
111 v_select := v_select || ' ORDER BY '|| v_seq_criteria;
112
113 cursor_name := dbms_sql.open_cursor;
114 dbms_sql.parse(cursor_name, v_select, dbms_sql.v7);
115 dbms_sql.bind_variable(cursor_name, ':v_session_id', USERENV('SESSIONID'));
116 dbms_sql.bind_variable(cursor_name, ':p_org_id', p_org_id);
117 dbms_sql.bind_variable(cursor_name, ':p_line_id', p_line_id);
118 dbms_sql.bind_variable(cursor_name, ':p_item_id', p_item_id);
119
120 dbms_sql.define_column(cursor_name,1, fs_select_rec.wip_entity);
121 dbms_sql.define_column(cursor_name,2, fs_select_rec.creation_date);
122 dbms_sql.define_column(cursor_name,3, fs_select_rec.schedule_date);
123 dbms_sql.define_column(cursor_name,4, fs_select_rec.promise_date);
124 dbms_sql.define_column(cursor_name,5, fs_select_rec.request_date);
125 dbms_sql.define_column(cursor_name,6, fs_select_rec.planning_priority);
126 dbms_sql.define_column(cursor_name,7, fs_select_rec.primary_item_id);
127 dbms_sql.define_column(cursor_name,8, fs_select_rec.planned_quantity);
128 dbms_sql.define_column(cursor_name,9, fs_select_rec.schedule_group_id);
129 dummy := dbms_sql.execute(cursor_name);
130
131 return(cursor_name);
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF (dbms_sql.is_open(cursor_name)) THEN
136 dbms_sql.close_cursor(cursor_name);
137 END IF;
138
139 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
140 FND_MSG_PUB.Add_Exc_Msg(
141 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
142 END IF;
143
144 RETURN NULL;
145 END Create_Cursor;
146
147 FUNCTION get_flex_tolerance( p_org_id IN NUMBER,
148 p_line_id IN NUMBER,
149 p_date IN NUMBER) RETURN NUMBER
150 IS
151 v_days_out NUMBER;
152 v_fence_days NUMBER;
153 v_tol_percent NUMBER;
154 BEGIN
155
156 v_days_out := mrp_calendar.DAYS_BETWEEN(p_org_id,1,sysdate,to_date(p_date,'J'));
157
158 select nvl(max(FENCE_DAYS),-1)
159 into v_fence_days
160 from bom_resource_flex_fences
161 where fence_days <= v_days_out
162 and department_id = p_line_id;
163
164 if v_fence_days > -1 then
165 select max(tolerance_percentage)
166 into v_tol_percent
167 from bom_resource_flex_fences
168 where fence_days = v_fence_days
169 and department_id = p_line_id;
170 else
171 return 0;
172 end if;
173
174 return ( v_tol_percent/100);
175 END get_flex_tolerance;
176
177
178 -- This procedure calculates the daily net available capacity on the line
179 -- by subtracting the line rate by the capacity used by existing work orders.
180
181 PROCEDURE calculate_linecap(
182 p_line_id IN NUMBER,
183 p_org_id IN NUMBER,
184 p_flex_tolerance IN NUMBER,
185 p_cap_tab IN OUT NOCOPY CapTabTyp,
186 p_time_tab IN TimeTabTyp,
187 p_schedule_start_date IN NUMBER,
188 p_schedule_start_time IN NUMBER,
189 p_schedule_end_date IN NUMBER,
190 p_schedule_end_time IN NUMBER
191 )
192 IS
193
194 v_cal_code VARCHAR2(10);
195 v_excep_code NUMBER;
196 v_line_rate NUMBER;
197 v_sum_dj NUMBER;
198 v_sum_fs NUMBER;
199 v_sum_rs NUMBER;
200 v_date DATE;
201 v_capacity NUMBER;
202 v_hr_rate NUMBER;
203 v_line_start_time NUMBER;
204 v_line_stop_time NUMBER;
205 v_start_time NUMBER;
206 v_stop_time NUMBER;
207 v_current_date NUMBER;
208 v_current_date2 NUMBER;
209 v_qty_to_sched NUMBER;
210 v_add_capacity NUMBER;
211 v_adj_add_capacity NUMBER;
212 v_flex_tol NUMBER;
213 v_line_rate_cur NUMBER;
214 v_line_rate_new NUMBER;
215
216 v_current_date_in_client00 DATE; --fix bug#3783650
217
218 BEGIN
219
220 V_PROCEDURE_NAME := 'calculate_linecap';
221 V_ERROR_LINE := 1;
222
223 SELECT calendar_code,calendar_exception_set_id
224 INTO v_cal_code,v_excep_code
225 FROM mtl_parameters
226 WHERE organization_id = p_org_id;
227
228 V_ERROR_LINE := 2;
229
230 -- For each work day, loop through all the dates and initialize the capacity to the
231 -- line rate. Calculate the sum of all existing work orders, discrete jobs,
232 -- repetitive schedules, and flow schedules and decrement the sum from the line rate
233 -- for each day.
234
235 -- Fix bug 939061, add 24 hrs to stop_time if stop_time <= start_time
236 SELECT maximum_rate, start_time, stop_time
237 INTO v_hr_rate, v_line_start_time, v_line_stop_time
238 FROM wip_lines
239 WHERE line_id = p_line_id
240 AND organization_id = p_org_id;
241
242 if (v_line_stop_time <= v_line_start_time) then
243 v_line_stop_time := v_line_stop_time + 24*3600;
244 end if;
245
246 v_line_rate := v_hr_rate * (v_line_stop_time - v_line_start_time)/3600;
247
248 V_ERROR_LINE := 3;
249
250 -- Get total_quantity to be scheduled
251 select sum(planned_quantity)
252 into v_qty_to_sched
253 from wip_flow_schedules
254 where request_id = USERENV('SESSIONID')
255 and line_id = p_line_id
256 and organization_id = p_org_id
257 and scheduled_flag = C_NO
258 and wip_entity_id >= G_WIP_ENTITY_ID;
259
260 v_current_date := p_time_tab.FIRST;
261
262 LOOP
263
264 p_cap_tab(v_current_date).capacity := v_line_rate;
265
266 --fix bug#3783650
267 v_date := to_date(v_current_date,'J');
268 v_current_date_in_client00 := flm_timezone.client00_in_server(v_date+(v_line_start_time/86400));
269 --end of fix bug#3783650
270
271 SELECT NVL(SUM(NVL(start_quantity,0)
272 - NVL(quantity_completed,0)
273 - NVL(quantity_scrapped,0)),0)
274 INTO v_sum_dj
275 FROM wip_discrete_jobs
276 WHERE line_id = p_line_id
277 AND organization_id = p_org_id
278 --fix bug#3783650
279 --AND to_number(to_char(scheduled_completion_date,'J'))
280 -- = v_current_date;
281 AND scheduled_completion_date BETWEEN v_current_date_in_client00
282 AND v_current_date_in_client00+1-(1/86400);
283 --end of fix bug#3783650
284
285 V_ERROR_LINE := 4;
286
287 --fix bug#3783650: move up v_date := to_date(v_current_date,'J');
288
289 IF (v_sum_dj > 0) THEN
290
291 p_cap_tab(v_current_date).capacity :=
292 p_cap_tab(v_current_date).capacity - v_sum_dj;
293 END IF;
294
295 SELECT NVL(SUM(NVL(planned_quantity,0)
296 -NVL(quantity_completed,0)),0)
297 INTO v_sum_fs
298 FROM wip_flow_schedules
299 WHERE line_id = p_line_id
300 AND organization_id = p_org_id
301 --fix bug#3783650
302 --AND to_number(to_char(scheduled_completion_date,'J'))
303 -- = v_current_date
304 AND scheduled_completion_date BETWEEN v_current_date_in_client00
305 AND v_current_date_in_client00+1-(1/86400)
306 AND scheduled_flag = C_YES;
307 --end of fix bug#3783650
308
309 V_ERROR_LINE := 5;
310
311 IF (v_sum_fs > 0) THEN
312
313 p_cap_tab(v_current_date).capacity :=
314 p_cap_tab(v_current_date).capacity - v_sum_fs;
315
316 END IF;
317
318 SELECT NVL(SUM(NVL(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(
319 v_cal_code,
320 v_excep_code,
321 wip_repetitive_schedules.daily_production_rate,
322 wip_repetitive_schedules.quantity_completed,
323 wip_repetitive_schedules.first_unit_completion_date,
324 to_date(v_current_date,'J')),0)),0)
325 INTO v_sum_rs
326 FROM wip_repetitive_schedules
327 WHERE line_id = p_line_id
328 AND organization_id = p_org_id
329 --fix bug#3783650
330 --AND v_current_date BETWEEN
331 -- to_number(to_char(first_unit_completion_date,'J'))
332 --AND to_number(to_char(last_unit_completion_date,'J'));
333 AND v_current_date_in_client00 BETWEEN
334 flm_timezone.client00_in_server(first_unit_completion_date)
335 AND flm_timezone.client00_in_server(last_unit_completion_date+1)-1/86400;
336 --end of fix bug#3783650
337
338 V_ERROR_LINE := 6;
339
340 IF (v_sum_rs > 0) THEN
341
342 p_cap_tab(v_current_date).capacity :=
343 p_cap_tab(v_current_date).capacity - v_sum_rs;
344
345 END IF;
346
347 v_capacity := v_hr_rate * (p_time_tab(v_current_date).end_completion_time -
348 p_time_tab(v_current_date).start_completion_time) / 3600;
349 -- if v_capacity < 0 means that schedule_end_time is less than line start time
350 if (v_capacity < 0) then
351 v_capacity := 0;
352 end if;
353 if (v_capacity < p_cap_tab(v_current_date).capacity) then
354 p_cap_tab(v_current_date).capacity := v_capacity;
355 end if;
356
357 -- The capacity left in the line will be used to fulfill the quantity to
358 -- be scheduled. Thus, we reduce the quantity to be scheduled with the
359 -- capacity left in the line.
360 if (v_qty_to_sched > 0) then
361 v_qty_to_sched := v_qty_to_sched -
362 p_cap_tab(v_current_date).capacity;
363 end if;
364
365 exit when p_time_tab.LAST = v_current_date;
366 v_current_date := p_time_tab.NEXT(v_current_date);
367 END LOOP;
368
369 -- This variable hold the additional capacity that can be used.
370 -- It's the different between previous line rate and current line rate.
371 v_add_capacity := 0;
372
373 -- If there is quantity to be scheduled, it means that the system can't
374 -- schedule all quantity using the capacity defined for that line.
375 -- Then it will use flex tolerances if any, to increase the line capacity
376 -- that will be used to schedule the remaining quantity.
377 -- The line capacity will be increased in steps. It will use the
378 -- flex tolerance starting from the start schedule date to the
379 -- end schedule date. Using the tolerance from day X (which start from
380 -- start schedule date to end schedule date), it will increase the
381 -- line capacity from day X till end schedule date. At any point, it
382 -- will exit, if the capacity is enough for scheduling the remaining
383 -- quantity. Then it will use the flex tolerance from day X+1 to
384 -- increase the line capacity from day X+1 till end schedule date.
385 -- Remember that this capacity increase should be the increment from
386 -- the previous capacity increase. e.g : in day X. the flex tolerance = 10
387 -- , and in day X+1, the flex tolerance = 15, the capacity increase
388 -- in day X+1 must be 5 (15-10).
389
390 if (p_flex_tolerance = 1 and v_qty_to_sched > 0) then
391
392 -- Start the loop to get the day from schedule start date to
393 -- schedule end date.
394 v_current_date := p_cap_tab.FIRST;
395 v_line_rate_cur := v_line_rate;
396
397 loop
398
399 -- Get the flex tolerance for a given day
400 v_flex_tol := get_flex_tolerance(p_org_id, p_line_id, v_current_date);
401 v_line_rate_new := round(v_line_rate * (1+v_flex_tol));
402
403 -- The additional capacity that can be added to the line is the
404 -- increment between previous line rate (v_line_rate_cur) and
405 -- the new line rate v_line_rate_new
406 v_add_capacity := v_line_rate_new - v_line_rate_cur;
407
408 -- Set the current line rate to the new line rate
409 v_line_rate_cur := v_line_rate_new;
410
411 v_current_date2 := v_current_date;
412
413 loop
414 -- Adjust the additional capacity with over capacity on the current schedule.
415 if (p_cap_tab(v_current_date2).capacity < 0) then
416 v_adj_add_capacity := v_add_capacity + p_cap_tab(v_current_date2).capacity;
417 else
418 v_adj_add_capacity := v_add_capacity;
419 end if;
420
421 v_start_time := v_line_start_time;
422 v_stop_time := v_line_stop_time;
423 if (v_current_date2 = p_schedule_start_date and p_schedule_start_time > v_start_time) then
424 v_start_time := p_schedule_start_time;
425 elsif (v_current_date2 = p_schedule_end_date and p_schedule_end_time < v_stop_time) then
426 v_stop_time := p_schedule_end_time;
427 end if;
428
429 -- To adjust the capacity based on the user defined schedule start and end time.
430 v_adj_add_capacity := round((v_stop_time-v_start_time)/
431 (v_line_stop_time-v_line_start_time)*v_adj_add_capacity);
432
433 if (v_adj_add_capacity > 0) then
434 -- If all the remaning quantity can be fulfill by the addition
435 -- of the capacity, increase line capacity on that day with the
436 -- remaning quantity. Then we complete.
437 -- Otherwise, increase the line capacity with the additional
438 -- capacity. Also need to decrease the quantity to be scheduled with
439 -- additional capacity, because some portion of that quantity can
440 -- be fulfilled with this additional capacity
441 if (v_qty_to_sched <= v_adj_add_capacity) then
442 p_cap_tab(v_current_date2).capacity := p_cap_tab(v_current_date2).capacity + v_qty_to_sched;
443 return;
444 else
445 p_cap_tab(v_current_date2).capacity := p_cap_tab(v_current_date2).capacity + v_adj_add_capacity;
446 v_qty_to_sched := v_qty_to_sched - v_adj_add_capacity;
447 end if;
448 end if;
449 exit when v_current_date2 = p_cap_tab.LAST;
450 v_current_date2 := p_cap_tab.NEXT(v_current_date2);
451 end loop;
452
453 exit when v_current_date = p_cap_tab.LAST;
454 v_current_date := p_cap_tab.NEXT(v_current_date);
455
456 end loop;
457
458 end if;
459
460 -- Set the capacity to 0 for any negative value on the capacity
461 v_current_date := p_cap_tab.FIRST;
462 loop
463 if (p_cap_tab(v_current_date).capacity < 0) then
464 p_cap_tab(v_current_date).capacity := 0;
465 end if;
466 exit when v_current_date = p_cap_tab.LAST;
467 v_current_date := p_cap_tab.NEXT(v_current_date);
468 end loop;
469
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 RETURN;
473
474 WHEN OTHERS THEN
475
476 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
477 FND_MSG_PUB.Add_Exc_Msg(
478 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
479 END IF;
480
481 return;
482
483 END calculate_linecap;
484
485 -- This function returns a string containing the sequencing criteria of a scheduling
486 -- rule, ordered by the priority of each criteria.
487
488 FUNCTION order_scheduling_rule(p_rule_id IN NUMBER,
489 p_order IN NUMBER) RETURN VARCHAR2
490 IS
491 v_ordered_criteria VARCHAR2(500);
492 v_source_type NUMBER;
493 i NUMBER;
494
495 CURSOR criteria_select IS
496 SELECT usage_code
497 FROM mrp_scheduling_rules
498 WHERE rule_id = p_rule_id
499 AND NVL(user_defined,C_USER_DEFINE_NO) = C_USER_DEFINE_NO
500 ORDER BY sequence_number;
501
502 BEGIN
503 V_PROCEDURE_NAME := 'order_scheduling_rule';
504 V_ERROR_LINE := 1;
505
506 -- Determine if flow schedules originate from sales orders or planned orders
507 SELECT demand_source_type
508 INTO v_source_type
509 FROM wip_flow_schedules
510 WHERE request_id = USERENV('SESSIONID')
511 AND scheduled_flag = C_NO
512 AND rownum = 1
513 AND wip_entity_id >= G_WIP_ENTITY_ID;
514
515 V_ERROR_LINE := 2;
516
517 IF v_source_type = C_PLANNED_ORDER THEN
518 v_ordered_criteria := 'fs.scheduled_completion_date';
519
520 RETURN(v_ordered_criteria);
521
522 ELSIF v_source_type IN (C_EXT_SALES_ORDER, C_INT_SALES_ORDER) THEN
523
524 v_ordered_criteria := NULL;
525
526 i := 1;
527
528 FOR criteria_select_rec IN criteria_select LOOP
529
530 IF i > 1 THEN
531 v_ordered_criteria := v_ordered_criteria ||',';
532 END IF;
533
534 IF criteria_select_rec.usage_code = 1 THEN
535 v_ordered_criteria := v_ordered_criteria || 'sol.request_date';
536
537 ELSIF criteria_select_rec.usage_code = 2 THEN
538 v_ordered_criteria := v_ordered_criteria || 'sol.schedule_ship_date';
539
540 ELSIF criteria_select_rec.usage_code = 3 THEN
541 v_ordered_criteria := v_ordered_criteria || 'sol.promise_date';
542
543 ELSIF criteria_select_rec.usage_code = 4 THEN
544 v_ordered_criteria := v_ordered_criteria || 'sol.creation_date';
545
546 ELSIF criteria_select_rec.usage_code = 5 THEN
547 v_ordered_criteria := v_ordered_criteria || 'sol.planning_priority';
548
549 ELSIF criteria_select_rec.usage_code = 7 THEN
550 v_ordered_criteria := v_ordered_criteria || 'sol.cust_production_seq_num';
551
552 ELSIF criteria_select_rec.usage_code = 8 THEN
553 v_ordered_criteria := v_ordered_criteria || 'sol.cust_production_seq_num desc';
554
555 END IF;
556
557 IF p_order = C_DESC THEN
558 v_ordered_criteria := v_ordered_criteria || ' DESC';
559 END IF;
560
561 i := i + 1;
562
563 END LOOP;
564
565 IF v_ordered_criteria IS NULL THEN
566 v_ordered_criteria := 'fs.scheduled_completion_date';
567 END IF;
568 RETURN(v_ordered_criteria);
569
570 END IF;
571
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 RETURN(NULL);
575
576 WHEN OTHERS THEN
577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
578 FND_MSG_PUB.Add_Exc_Msg(
579 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
580 END IF;
581
582 return(NULL);
583
584 END order_scheduling_rule;
585
586 -- This procedure takes each item that has unscheduled flow schedules on the
587 -- line and calculates the acceptable order quantities depending on the order
588 -- modifiers associated with the item.
589
590 PROCEDURE calculate_order_quantities(
591 p_org_id IN NUMBER,
592 p_order_mod_tab IN OUT NOCOPY OrderModTabTyp)
593 IS
594 v_fixed_qty NUMBER;
595 v_lot_multiple NUMBER;
596 v_min_qty NUMBER;
597 v_max_qty NUMBER;
598
599 BEGIN
600
601 V_PROCEDURE_NAME := 'Calculate_Order_Quantities';
602 V_ERROR_LINE := 1;
603
604 -- From the order modifiers for each item, find out what order quantities can be
605 -- used. The order modifier columns are fixed_order_qty, fixed_lot_multiplier,
606 -- min_qty, and max_qty.
607
608 -- The calculated order quantities are stored in a pl/sql table.
609
610 FOR item_list_rec IN item_list(p_org_id) LOOP
611
612 SELECT NVL(fixed_order_quantity,0), NVL(fixed_lot_multiplier,0),
613 NVL(minimum_order_quantity,0), NVL(maximum_order_quantity,0)
614 INTO v_fixed_qty, v_lot_multiple, v_min_qty, v_max_qty
615 FROM mtl_system_items
616 WHERE inventory_item_id = item_list_rec.primary_item_id
617 AND organization_id = p_org_id;
618
619 V_ERROR_LINE := 2;
620
621 -- If fixed order quantity exists for the item, then that will simply be
622 -- the order quantity
623
624 IF v_fixed_qty > 0 THEN
625
626 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_fixed_qty;
627 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_fixed_qty;
628
629 -- If the fixed order quantity does not exist for the item, and the fixed lot
630 -- multiplier exists, make sure that the fixed lot multiple quantity is between
631 -- the minimum and maximum values.
632
633 ELSIF v_lot_multiple > 0 THEN
634
635 IF (v_lot_multiple >= v_min_qty AND v_lot_multiple <= v_max_qty) OR
636 (v_min_qty = 0 AND v_max_qty = 0) THEN
637
638 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_lot_multiple;
639 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_lot_multiple;
640
641 ELSIF v_lot_multiple < v_min_qty THEN
642 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_min_qty;
643 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_min_qty;
644
645 ELSIF v_lot_multiple > v_max_qty THEN
646 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_max_qty;
647 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_max_qty;
648 END IF;
649
650 -- If only the minimum and maximum order quantities exist, both values
651 -- will be stored. However, at this point, we will only consider the
652 -- minimum value. In the future, we need to figure out a way to consider
653 -- the maximum value so that order quantities can take different values as
654 -- long as they fall within the minimum and maximum values.
655
656 ELSIF (v_min_qty > 0) AND (v_max_qty > 0) THEN
657 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_min_qty;
658 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_max_qty;
659
660 -- If only the minimum or maximum order quantity is defined:
661
662 ELSIF (v_min_qty > 0) THEN
663 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_min_qty;
664 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_min_qty;
665
666 ELSIF (v_max_qty > 0) THEN
667 p_order_mod_tab(item_list_rec.primary_item_id).minVal := v_max_qty;
668 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := v_max_qty;
669
670 -- If there are no order modifier values, then no order modifier constraints exist.
671 ELSE
672 p_order_mod_tab(item_list_rec.primary_item_id).minVal := 0;
673 p_order_mod_tab(item_list_rec.primary_item_id).maxVal := 0;
674 END IF;
675
676 END LOOP;
677
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 RETURN;
681
682 WHEN OTHERS THEN
683
684 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
685 FND_MSG_PUB.Add_Exc_Msg(
686 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
687 END IF;
688
689 return;
690
691 END calculate_order_quantities;
692
693 -- This procedure obtains the highest build sequence for each schedule group and
694 -- populates that information in a pl/sql table.
695
696 PROCEDURE calculate_build_sequences (
697 p_org_id IN NUMBER,
698 p_line_id IN NUMBER,
699 p_build_seq_tab IN OUT NOCOPY BuildSeqTabTyp)
700 IS
701
702 v_null_build_seq NUMBER;
703
704 BEGIN
705
706 V_PROCEDURE_NAME := 'Calculate_Build_Sequences';
707 V_ERROR_LINE := 1;
708
709 FOR schedule_group_rec IN schedule_group_list(p_org_id) LOOP
710
711 IF schedule_group_rec.schedule_group IS NOT NULL THEN
712 SELECT NVL(MAX(build_sequence),0)
713 INTO p_build_seq_tab(schedule_group_rec.schedule_group).buildseq
714 FROM wip_flow_schedules fs
715 WHERE fs.schedule_group_id = schedule_group_rec.schedule_group
716 AND fs.line_id = p_line_id
717 AND fs.organization_id = p_org_id
718 AND scheduled_flag = C_YES;
719
720 V_ERROR_LINE := 2;
721
722 ELSE
723
724 SELECT NVL(MAX(build_sequence),0)
725 INTO v_null_build_seq
726 FROM wip_flow_schedules fs
727 WHERE fs.schedule_group_id IS NULL
728 AND fs.line_id = p_line_id
729 AND fs.organization_id = p_org_id
730 AND scheduled_flag = C_YES;
731
732 V_ERROR_LINE := 3;
733
734 END IF;
735 END LOOP;
736
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 RETURN;
740
741 WHEN OTHERS THEN
742
743 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
744 FND_MSG_PUB.Add_Exc_Msg(
745 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
746 END IF;
747
748 return;
749
750 END calculate_build_sequences;
751
752 -- This procedure time stamps all existing flow schedules on the line for all valid
753 -- days within the scheduling window before scheduling new flow schedules on the line.
754
755 PROCEDURE time_existing_fs(
756 p_org_id IN NUMBER,
757 p_line_id IN NUMBER,
758 p_schedule_start_date IN NUMBER,
759 p_schedule_start_time IN NUMBER,
760 p_schedule_end_date IN NUMBER,
761 p_schedule_end_time IN NUMBER,
762 p_time_tab IN OUT NOCOPY TimeTabTyp)
763 IS
764
765 /* Performance fix for Bug No #2720049
766 Description : Changed the sql so that index on calendar_date could be used. */
767
768 CURSOR valid_dates(l_cal_code IN VARCHAR2, l_excep_code IN NUMBER,
769 l_schedule_start_date IN NUMBER, l_schedule_end_date IN NUMBER) IS
770 SELECT to_number(to_char(bom_cal.calendar_date,'J')) workday
771 FROM bom_calendar_dates bom_cal
772 WHERE bom_cal.calendar_code = l_cal_code
773 AND bom_cal.exception_set_id = l_excep_code
774 AND bom_cal.calendar_date between to_date(l_schedule_start_date,'j') and
775 to_date(l_schedule_end_date,'j')
776 -- AND to_number(to_char(bom_cal.calendar_date,'J'))
777 -- between l_schedule_start_date and l_schedule_end_date
778 AND bom_cal.seq_num is NOT NULL
779 ORDER BY bom_cal.calendar_date;
780
781 v_date NUMBER;
782 v_hr_line_rate NUMBER;
783 v_line_rate NUMBER;
784 v_start_time NUMBER;
785 v_end_time NUMBER;
786 v_required_time NUMBER;
787 v_begin_time DATE;
788 v_completion_time DATE;
789 v_final_time NUMBER;
790 l_fixed_lead_time NUMBER;
791 l_variable_lead_time NUMBER;
792 l_lead_time NUMBER;
793 l_cal_code VARCHAR2(10);
794 l_excep_code NUMBER;
795 l_last_comp_time NUMBER;
796 l_last_comp_date DATE;
797 l_user_start_time NUMBER;
798 l_sec NUMBER;
799 l_schedule_start_time NUMBER;
800 l_schedule_start_date NUMBER;
801
802 BEGIN
803
804 V_PROCEDURE_NAME := 'Time_Existing_fs';
805 V_ERROR_LINE := 1;
806
807 l_schedule_start_date := p_schedule_start_date;
808 l_schedule_start_time := p_schedule_start_time;
809
810 -- Obtain information on the hourly line rate, start time and end time of the line.
811
812 SELECT maximum_rate, start_time, stop_time
813 INTO v_hr_line_rate, v_start_time, v_end_time
814 FROM wip_lines
815 WHERE line_id = p_line_id
816 AND organization_id = p_org_id;
817
818 V_ERROR_LINE := 2;
819
820 -- The line rate per day is the hourly line rate multiplied by the number of hours
821 -- in a day.
822
823 -- Fix bug 939061, add 24 hrs to stop_time if stop_time <= start_time
824 if (v_end_time <= v_start_time) then
825 if (p_schedule_start_time < v_end_time ) then
826 l_schedule_start_date := l_schedule_start_date - 1;
827 l_schedule_start_time := l_schedule_start_time + 24*3600;
828 end if;
829 v_end_time := v_end_time + 24*3600;
830 end if;
831
832 SELECT calendar_code,calendar_exception_set_id
833 INTO l_cal_code,l_excep_code
834 FROM mtl_parameters
835 WHERE organization_id = p_org_id;
836
837 FOR valid_dates_rec IN valid_dates(l_cal_code,l_excep_code,
838 l_schedule_start_date,p_schedule_end_date) LOOP
839
840 v_date := valid_dates_rec.workday;
841
842 -- The value of v_end_time can be one of the following :
843 -- 1. line_end_time if the date is not schedule end date
844 -- 2. if the date is schedule end date :
845 -- a. schedule_end_time if schedule_end_time in between line start and stop time
846 -- b. line start time if schedule_end_time is smaller than line start time
847 -- c. line end time if schedule_end_time is higher than line start time
848 if (v_date = p_schedule_end_date) then
849 if (p_schedule_end_time < v_end_time and p_schedule_end_time > v_start_time) then
850 v_end_time := p_schedule_end_time;
851 elsif (p_schedule_end_time < v_start_time) then
852 v_end_time := v_start_time;
853 end if;
854 end if;
855 v_line_rate := TRUNC(v_hr_line_rate * (v_end_time - v_start_time)/3600);
856
857 SELECT max(scheduled_completion_date)
858 INTO l_last_comp_date
859 FROM wip_flow_schedules
860 WHERE scheduled_completion_date >= to_date(v_date,'J')+v_start_time/86400
861 AND scheduled_completion_date <= to_date(v_date,'J')+v_end_time/86400
862 AND line_id = p_line_id
863 AND organization_id = p_org_id
864 AND scheduled_flag = C_YES;
865
866 if (l_last_comp_date IS NOT NULL) then
867 l_last_comp_time := to_char(l_last_comp_date,'SSSSS');
868 if (trunc(l_last_comp_date) > to_date(v_date,'J')) then
869 l_last_comp_time := l_last_comp_time + 24*3600;
870 end if;
871 if l_last_comp_time < v_start_time then
872 l_last_comp_time := v_start_time;
873 end if;
874 else
875 l_last_comp_time := v_start_time;
876 end if;
877
878 if (v_date = l_schedule_start_date) then
879 if (l_schedule_start_time > v_end_time) then
880 l_last_comp_time := v_end_time;
881 elsif (l_schedule_start_time > l_last_comp_time) then
882 l_sec := 3600/v_hr_line_rate; -- Time to produce 1 qty in seconds
883 l_last_comp_time := v_start_time+
884 ceil((l_schedule_start_time-v_start_time)/l_sec) * l_sec;
885 l_last_comp_time := l_last_comp_time - l_sec;
886 end if;
887 end if;
888
889 p_time_tab(v_date).start_completion_time := l_last_comp_time;
890 p_time_tab(v_date).end_completion_time := v_end_time;
891
892 END LOOP;
893
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 RETURN;
897
898 WHEN OTHERS THEN
899
900 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
901 FND_MSG_PUB.Add_Exc_Msg(
902 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
903 END IF;
904
905 return;
906 END time_existing_fs;
907
908 -- This procedure calculates the total demand for each item and populates these
909 -- values into the PL/SQL table. The procedure also calculates the global demand
910 -- across all items and populates this value into the pl/sql table. The table also
911 -- contains the round type of the item.
912
913 PROCEDURE calculate_demand(
914 p_line_id IN NUMBER,
915 p_org_id IN NUMBER,
916 p_demand_tab IN OUT NOCOPY DemandTabTyp)
917 IS
918
919 -- Define Variables and Cursors
920
921 v_demand_total NUMBER;
922
923 BEGIN
924
925 V_PROCEDURE_NAME := 'Calculate_Demand';
926 V_ERROR_LINE := 1;
927
928 -- Calculate total demand for each item and populate into PS/SQL table indexed
929 -- by the item id. Also, calculate the global demand across all items.
930
931 v_demand_total := 0;
932
933 FOR item_list_rec IN item_list(p_org_id) LOOP
934
935 -- Find the round type of the item and populate into table.
936
937 SELECT NVL(rounding_control_type,2)
938 INTO p_demand_tab(item_list_rec.primary_item_id).roundType
939 FROM mtl_system_items
940 WHERE inventory_item_id = item_list_rec.primary_item_id
941 AND organization_id = p_org_id;
942
943 V_ERROR_LINE := 2;
944
945 p_demand_tab(item_list_rec.primary_item_id).sequence := 0;
946
947 -- For each item, calculate the total demand from flow schedules to be scheduled.
948
949 SELECT NVL(SUM(NVL(planned_quantity,0)
950 -NVL(quantity_completed,0)),0)
951 INTO p_demand_tab(item_list_rec.primary_item_id).totalDemand
952 FROM wip_flow_schedules fs
953 WHERE fs.request_id = USERENV('SESSIONID')
954 AND fs.primary_item_id = item_list_rec.primary_item_id
955 AND fs.line_id = p_line_id
956 AND fs.organization_id = p_org_id
957 AND fs.scheduled_flag = C_NO
958 AND wip_entity_id >= G_WIP_ENTITY_ID;
959
960 V_ERROR_LINE := 3;
961
962 v_demand_total := v_demand_total +
963 p_demand_tab(item_list_rec.primary_item_id).totalDemand;
964
965 END LOOP;
966
967 V_GLOBAL_DEMAND := v_demand_total;
968
969 EXCEPTION
970 WHEN NO_DATA_FOUND THEN
971 RETURN;
972 WHEN OTHERS THEN
973
974 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
975 FND_MSG_PUB.Add_Exc_Msg(
976 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
977 END IF;
978
979 END calculate_demand;
980
981 -- This procedure calculates the total demand for each item and populates this
982 -- information into a pl/sql table ordered by the value of the demand.
983
984 PROCEDURE calculate_demand_mix (
985 p_line_id IN NUMBER,
986 p_org_id IN NUMBER,
987 p_item_demand_tab IN OUT NOCOPY ItemDemandTabTyp)
988 IS
989
990 -- Define Variables and Cursors
991 v_item_id NUMBER;
992 v_quantity NUMBER;
993 v_fixed_lead_time NUMBER;
994 v_var_lead_time NUMBER;
995 i NUMBER;
996
997 CURSOR item_demand_cursor IS
998 SELECT primary_item_id, SUM(TRUNC(NVL(planned_quantity,0)
999 -NVL(quantity_completed,0)-0.00000001)+1) quantity
1000 FROM wip_flow_schedules fs
1001 WHERE fs.request_id = USERENV('SESSIONID')
1002 AND fs.line_id = p_line_id
1003 AND fs.organization_id = p_org_id
1004 AND fs.scheduled_flag = C_NO
1005 AND wip_entity_id >= G_WIP_ENTITY_ID
1006 GROUP BY primary_item_id
1007 ORDER BY quantity;
1008
1009 BEGIN
1010
1011 V_PROCEDURE_NAME := 'Calculate_Demand_Mix';
1012 V_ERROR_LINE := 1;
1013
1014 -- Calculate total demand for each item and populate into PS/SQL table.
1015 -- Populate the item column and the demand column for the item.
1016
1017 i := 1;
1018
1019 OPEN item_demand_cursor;
1020 LOOP
1021
1022 FETCH item_demand_cursor INTO v_item_id, v_quantity;
1023 EXIT WHEN item_demand_cursor%NOTFOUND;
1024
1025 SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
1026 INTO v_fixed_lead_time, v_var_lead_time
1027 FROM MTL_SYSTEM_ITEMS
1028 WHERE inventory_item_id = v_item_id
1029 AND organization_id = p_org_id;
1030
1031 p_item_demand_tab(i).item := v_item_id;
1032
1033 p_item_demand_tab(i).qty := v_quantity;
1034
1035 p_item_demand_tab(i).fixed_lead_time := v_fixed_lead_time;
1036
1037 p_item_demand_tab(i).var_lead_time := v_var_lead_time;
1038
1039 i := i + 1;
1040
1041 END LOOP;
1042 CLOSE item_demand_cursor;
1043
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 RETURN;
1047 WHEN OTHERS THEN
1048 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1049 FND_MSG_PUB.Add_Exc_Msg(
1050 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
1051 END IF;
1052
1053 END calculate_demand_mix;
1054
1055 -- This procedure establishes the manufacturing pattern for the mix model
1056 -- scheduling algorithm.
1057
1058 FUNCTION mix_model (
1059 p_item_demand_tab IN ItemDemandTabTyp) RETURN LONG
1060 IS
1061
1062 -- Define Variables and Cursors
1063 mmm_tab1 ItemDemandTabTyp;
1064 mmm_tab2 ItemDemandTabTyp;
1065 curr_total_pattern long;
1066 final_total_pattern long;
1067 tmp_total_pattern long;
1068 num_pos Number := -1;
1069 new_num_pos Number;
1070 curr_pos Number;
1071 occur_num Number;
1072 curr_sub_cnt Number ;
1073 tmp_cnt Number;
1074 curr_qty Number;
1075 curr_ind_qty Number;
1076 curr_ind Number :=1;
1077 next_ind Number;
1078
1079 BEGIN
1080
1081 V_PROCEDURE_NAME := 'mix_model';
1082
1083 mmm_tab1 := p_item_demand_tab;
1084
1085 FOR i in 1..mmm_tab1.COUNT Loop
1086 mmm_tab2(i).item := mmm_tab1(i).item;
1087 -- dbms_output.put_line('The index is: '||to_char(i));
1088 -- dbms_output.put_line('The item is: '||to_char(mmm_tab2(i).item));
1089 mmm_tab2(i).qty := mmm_tab1(i).qty;
1090 -- dbms_output.put_line(mmm_tab2(i).qty);
1091
1092 END LOOP;
1093
1094 /* FOR i in 1..3 Loop
1095 mmm_tab2(i).item := i;
1096 mmm_tab2(i).qty := 10;
1097 END LOOP;
1098 mmm_tab2(4).item := 4;
1099 mmm_tab2(4).qty := 30;*/
1100 Loop
1101 declare
1102 curr_pat_tab pat_tab_type;
1103 curr_pattern long;
1104 curr_sub_pattern long;
1105 BEGIN
1106 -- dbms_output.put_line('curr_pat_tab count: '||to_char(curr_pat_tab.COUNT));
1107 -- dbms_output.put_line('curr_ind: '||to_char(curr_ind));
1108 -- dbms_output.put_line('mmm_tab2 count: '||to_char(mmm_tab2.COUNT));
1109 curr_pattern := null;
1110 curr_sub_pattern := null;
1111 for i in REVERSE curr_ind..mmm_tab2.COUNT LOOP
1112 if (i <> curr_ind ) then
1113 curr_sub_pattern := curr_sub_pattern || to_char(i)|| '*';
1114 else
1115 curr_sub_pattern := curr_sub_pattern || to_char(i);
1116 end if;
1117 end loop;
1118 -- dbms_output.put_line('curr_sub_pattern: '||curr_sub_pattern);
1119 if (num_pos = -1 ) then
1120 curr_pat_tab(1).curr_pattern := curr_sub_pattern;
1121 num_pos := 1;
1122 FOR i in 1..mmm_tab2(curr_ind).qty Loop
1123 curr_total_pattern := curr_total_pattern || '*-'||curr_sub_pattern ;
1124 num_pos := num_pos +1;
1125 end loop;
1126 curr_total_pattern := curr_total_pattern || '*-' ;
1127 -- dbms_output.put_line('initial curr_total_pattern: '||curr_total_pattern);
1128 -- dbms_output.put_line('initial num_pos: '||to_char(num_pos));
1129 else
1130 -- dbms_output.put_line('curr_qty: '||to_char(mmm_tab2(curr_ind).qty));
1131 if (mmm_tab2(curr_ind).qty > num_pos) then
1132
1133 curr_qty := mmm_tab2(curr_ind).qty;
1134 tmp_cnt := 1;
1135 Loop
1136 curr_sub_cnt := ceil(curr_qty / (num_pos-tmp_cnt+1));
1137 -- dbms_output.put_line('i am here '||to_char(curr_sub_cnt));
1138 curr_pattern:=null;
1139 FOR i in 1..curr_sub_cnt Loop
1140 if (curr_pattern is null) then
1141 curr_pattern := curr_sub_pattern;
1142 else
1143 curr_pattern := curr_pattern ||'*'|| curr_sub_pattern;
1144 end if;
1145 end loop;
1146 -- dbms_output.put_line('i am here curr_pattern '||curr_pattern);
1147 curr_pat_tab(tmp_cnt).curr_pattern := curr_pattern;
1148 curr_qty := curr_qty - curr_sub_cnt;
1149 exit when curr_qty <= 0;
1150 tmp_cnt := tmp_cnt +1;
1151 end Loop;
1152 elsif (mmm_tab2(curr_ind).qty < num_pos) then
1153 -- need to choose positions
1154 FOR i in 1..mmm_tab2(curr_ind).qty Loop
1155 curr_pat_tab(i).curr_pattern := curr_sub_pattern;
1156 end loop;
1157 else
1158 FOR i in 1..mmm_tab2(curr_ind).qty Loop
1159 curr_pat_tab(i).curr_pattern := curr_sub_pattern;
1160 end loop;
1161 end if;
1162 -- insert strings into positions
1163 occur_num := 1;
1164 new_num_pos := num_pos;
1165 -- dbms_output.put_line('mid curr_pat_tab count: '||to_char(curr_pat_tab.COUNT));
1166
1167 For i in 1..curr_pat_tab.COUNT Loop
1168 curr_pos := instr(curr_total_pattern,'-',1,occur_num);
1169 -- dbms_output.put_line('in loop curr_pattern: '||curr_pat_tab(i).curr_pattern);
1170 curr_total_pattern := substr(curr_total_pattern,1,curr_pos) || curr_pat_tab(i).curr_pattern || '*'||substr(curr_total_pattern,curr_pos);
1171 -- dbms_output.put_line('curr_total_pattern: '||curr_total_pattern);
1172 -- dbms_output.put_line('tmp_total_pattern: '||tmp_total_pattern);
1173 new_num_pos := new_num_pos +1;
1174 num_pos := new_num_pos;
1175 occur_num := occur_num +2;
1176 /* if (occur_num > num_pos) then
1177 -- dbms_output.put_line('error condition ');
1178 exit;
1179 end if; */
1180 end Loop;
1181 -- curr_total_pattern := tmp_total_pattern;
1182 -- dbms_output.put_line('final curr_total_pattern: '||curr_total_pattern);
1183 num_pos := new_num_pos;
1184 -- dbms_output.put_line('new num_pos: '||to_char(num_pos));
1185 -- curr_pat_tab := NULL;
1186 end if;
1187 -- dbms_output.put_line('i am here curr_pattern: '||curr_pattern);
1188 next_ind := null;
1189 curr_ind_qty := mmm_tab2(curr_ind).qty;
1190 For i in 1..mmm_tab2.COUNT Loop
1191 -- dbms_output.put_line('i am here22 curr_pattern: '||curr_pattern);
1192 mmm_tab2(i).qty :=mmm_tab2(i).qty - curr_ind_qty;
1193 if (mmm_tab2(i).qty > 0 and next_ind is null) then
1194 next_ind := i;
1195 end if;
1196 end loop;
1197 exit when next_ind is null;
1198 curr_ind := next_ind;
1199 -- dbms_output.put_line('next_ind: '||to_char(next_ind));
1200 -- dbms_output.put_line(' ENDcurr_pat_tab count: '||to_char(curr_pat_tab.COUNT));
1201 END;
1202 END Loop;
1203 final_total_pattern := replace(curr_total_pattern,'-');
1204 -- dbms_output.put_line('The pattern in mix model: '||final_total_pattern);
1205
1206 tmp_cnt := ceil(length(curr_total_pattern) / 255);
1207 -- for i in 1..tmp_cnt Loop
1208 -- dbms_output.put_line(substr(curr_total_pattern,255*(i-1),255));
1209 -- end loop;
1210 -- dbms_output.put_line('length: '||to_char(length(curr_total_pattern)));
1211 -- dbms_output.put_line('actual length: '||to_char(length(replace(replace(curr_total_pattern,'-'),'*'))));
1212
1213 -- dbms_output.put_line('FINAL PATTERN:');
1214 -- tmp_cnt := ceil(length(final_total_pattern) / 255);
1215 -- for i in 1..tmp_cnt Loop
1216 -- dbms_output.put_line(substr(final_total_pattern,255*(i-1),255));
1217 -- end loop;
1218 -- dbms_output.put_line('The pattern in mix model: '||final_total_pattern);
1219
1220 RETURN final_total_pattern;
1221
1222 EXCEPTION
1223 WHEN NO_DATA_FOUND THEN
1224 RETURN NULL;
1225 WHEN OTHERS THEN
1226
1227 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1228 FND_MSG_PUB.Add_Exc_Msg(V_PKG_NAME, V_PROCEDURE_NAME);
1229 END IF;
1230
1231 END mix_model;
1232
1233 -- This procedure converts flow schedules of type planned order into new flow
1234 -- schedules that respect the order modifiers of the items. After the conversion,
1235 -- the flow schedules can be scheduled on the line.
1236
1237 PROCEDURE create_po_fs(
1238 p_org_id IN NUMBER,
1239 p_line_id IN NUMBER,
1240 p_rule_id IN NUMBER,
1241 p_orderMod_tab IN OUT NOCOPY OrderModTabTyp)
1242
1243 IS
1244
1245 v_order_quantity NUMBER;
1246 v_temp NUMBER;
1247 v_num_flow NUMBER;
1248 v_itemQty_tab ItemQtyTabTyp;
1249 fs_select_rec fs_select_type;
1250 cursor_name INTEGER;
1251 v_last_wip NUMBER;
1252 v_schedule_number VARCHAR2(30);
1253 to_schedule_qty NUMBER;
1254 v_planned_quantity NUMBER;
1255
1256 BEGIN
1257
1258 V_PROCEDURE_NAME := 'Create_Po_Fs';
1259 V_ERROR_LINE := 1;
1260
1261 cursor_name := Create_Cursor(p_rule_id,p_org_id,p_line_id,C_ASC,C_NORM,C_NORM);
1262
1263 -- dbms_output.put_line('Looping through each planned order flow schedule
1264 -- and creating new flow schedules with order modifier quantities.');
1265
1266 LOOP
1267 if dbms_sql.fetch_rows(cursor_name) > 0 then
1268 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
1269 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
1270 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
1271 dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
1272 end if;
1273
1274 IF v_last_wip = fs_select_rec.wip_entity THEN
1275 EXIT;
1276 END IF;
1277
1278 v_last_wip := fs_select_rec.wip_entity;
1279
1280 -- dbms_output.put_line('Current flow schedule is: '||
1281 -- to_char(fs_select_rec.wip_entity));
1282 -- dbms_output.put_line('Current item is: '||
1283 -- to_char(fs_select_rec.primary_item_id));
1284
1285 -- Check the item quantity table to see what the remaining quantity of the
1286 -- newest flow schedule is. If the remaining quantity is greater than the
1287 -- quantity we are trying to schedule,then leave the flow schedule as is.
1288
1289 -- If the remaining quantity is less than the quantity we need to
1290 -- schedule, then split the flow schedule into multiple flow schedules
1291 -- to correspond to the original flow schedule.
1292 -- Update the item quantity table.
1293
1294 IF v_itemQty_tab.EXISTS(fs_select_rec.primary_item_id) = FALSE THEN
1295 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty := 0;
1296 END IF;
1297
1298 -- dbms_output.put_line('Remain Qty in ItemQty table is '||to_char
1299 -- (v_itemQty_tab(fs_select_rec.primary_item_id).remainQty));
1300
1301 -- dbms_output.put_line('Planned quantity is '||
1302 -- to_char(fs_select_rec.planned_quantity));
1303
1304 IF v_itemQty_tab(fs_select_rec.primary_item_id).remainQty >=
1305 fs_select_rec.planned_quantity THEN
1306
1307 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty :=
1308 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty - fs_select_rec.planned_quantity;
1309
1310 -- dbms_output.put_line('Enough quantity remaining for allocation...');
1311 -- dbms_output.put_line('Remaining quantity = '||
1312 -- to_char(v_itemQty_tab(fs_select_rec.primary_item_id).remainQty));
1313
1314 ELSIF v_itemQty_tab(fs_select_rec.primary_item_id).remainQty <
1315 fs_select_rec.planned_quantity THEN
1316 -- dbms_output.put_line('NOT enough quantity remaining for allocation.');
1317
1318 IF p_orderMod_tab(fs_select_rec.primary_item_id).minVal = 0 AND
1319 p_orderMod_tab(fs_select_rec.primary_item_id).maxVal = 0 THEN
1320
1321 -- dbms_output.put_line('no meaningful values for min and max values!!');
1322 -- dbms_output.put_line('Order quantity = planned quantity');
1323
1324 v_order_quantity := fs_select_rec.planned_quantity;
1325
1326 ELSIF p_orderMod_tab(fs_select_rec.primary_item_id).minVal > 0 THEN
1327
1328 -- dbms_output.put_line('minimum value exists and = order quantity!!');
1329
1330 v_order_quantity := p_orderMod_tab(fs_select_rec.primary_item_id).minVal;
1331
1332 -- We need to find a way to include the maximum in the future.
1333 END IF;
1334
1335 V_ERROR_LINE := 2;
1336
1337 SELECT TRUNC((fs_select_rec.planned_quantity -
1338 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty)/
1339 v_order_quantity-0.000000001)+1
1340 INTO v_num_flow
1341 FROM DUAL;
1342
1343 V_ERROR_LINE := 3;
1344
1345 UPDATE wip_flow_schedules
1346 SET planned_quantity =
1347 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty
1348 WHERE wip_entity_id = fs_select_rec.wip_entity
1349 AND organization_id = p_org_id;
1350
1351 V_ERROR_LINE := 4;
1352
1353 -- dbms_output.put_line('We need to create this number of new flow schedules:'
1354 -- ||v_num_flow);
1355
1356 to_schedule_qty := fs_select_rec.planned_quantity -
1357 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty;
1358
1359 FOR i IN 1..v_num_flow LOOP
1360
1361 -- Create a new flow schedule to carry over the unscheduled quantity
1362 SELECT wip_entities_s.nextval
1363 INTO v_temp
1364 FROM dual;
1365
1366 V_ERROR_LINE := 5;
1367
1368 IF to_schedule_qty >= v_order_quantity THEN
1369 v_planned_quantity := v_order_quantity;
1370 ELSE
1371 v_planned_quantity := to_schedule_qty;
1372 END IF;
1373
1374 --Bug 6122344
1375 --v_schedule_number := NVL(substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20),'X')
1376 -- || to_char(v_temp);
1377 v_schedule_number := 'FLM-INTERNAL'|| to_char(v_temp);
1378
1379 INSERT INTO wip_flow_schedules(
1380 scheduled_flag,
1381 wip_entity_id,
1382 organization_id,
1383 last_update_date,
1384 last_updated_by,
1385 creation_date,
1386 created_by,
1387 class_code,
1388 line_id,
1389 primary_item_id,
1390 scheduled_start_date,
1391 planned_quantity,
1392 quantity_completed,
1393 quantity_scrapped,
1394 scheduled_completion_date,
1395 schedule_group_id,
1396 status,
1397 schedule_number,
1398 demand_source_header_id,
1399 demand_source_line,
1400 demand_source_delivery,
1401 demand_source_type,
1402 project_id,
1403 task_id,
1404 end_item_unit_number,
1405 request_id,
1406 attribute1,
1407 attribute2,
1408 attribute3,
1409 attribute4,
1410 attribute5,
1411 attribute6,
1412 attribute7,
1413 attribute8,
1414 attribute9,
1415 attribute10,
1416 attribute11,
1417 attribute12,
1418 attribute13,
1419 attribute14,
1420 attribute15,
1421 material_account,
1422 material_overhead_account,
1423 resource_account,
1424 outside_processing_account,
1425 material_variance_account,
1426 resource_variance_account,
1427 outside_proc_variance_account,
1428 std_cost_adjustment_account,
1429 overhead_account,
1430 overhead_variance_account,
1431 bom_revision, /* Added for bug 2185087 */
1432 routing_revision,
1433 bom_revision_date,
1434 routing_revision_date,
1435 alternate_bom_designator,
1436 alternate_routing_designator,
1437 completion_subinventory,
1438 completion_locator_id,
1439 demand_class,
1440 attribute_category,
1441 kanban_card_id)
1442 SELECT C_NO,
1443 v_temp,
1444 p_org_id,
1445 SYSDATE,
1446 fs.last_updated_by,
1447 SYSDATE,
1448 fs.created_by,
1449 fs.class_code,
1450 fs.line_id,
1451 fs.primary_item_id,
1452 fs.scheduled_completion_date,
1453 v_planned_quantity,
1454 0,
1455 0,
1456 fs.scheduled_completion_date,
1457 fs.schedule_group_id,
1458 fs.status,
1459 v_schedule_number,
1460 fs.demand_source_header_id,
1461 fs.demand_source_line,
1462 fs.demand_source_delivery,
1463 fs.demand_source_type,
1464 fs.project_id,
1465 fs.task_id,
1466 fs.end_item_unit_number,
1467 USERENV('SESSIONID'),
1468 fs.attribute1,
1469 fs.attribute2,
1470 fs.attribute3,
1471 fs.attribute4,
1472 fs.attribute5,
1473 fs.attribute6,
1474 fs.attribute7,
1475 fs.attribute8,
1476 fs.attribute9,
1477 fs.attribute10,
1478 fs.attribute11,
1479 fs.attribute12,
1480 fs.attribute13,
1481 fs.attribute14,
1482 fs.attribute15,
1483 fs.material_account,
1484 fs.material_overhead_account,
1485 fs.resource_account,
1486 fs.outside_processing_account,
1487 fs.material_variance_account,
1488 fs.resource_variance_account,
1489 fs.outside_proc_variance_account,
1490 fs.std_cost_adjustment_account,
1491 fs.overhead_account,
1492 fs.overhead_variance_account,
1493 fs.bom_revision, /* Added for bug 2185087 */
1494 fs.routing_revision,
1495 fs.bom_revision_date,
1496 fs.routing_revision_date,
1497 fs.alternate_bom_designator,
1498 fs.alternate_routing_designator,
1499 fs.completion_subinventory,
1500 fs.completion_locator_id,
1501 fs.demand_class,
1502 fs.attribute_category,
1503 fs.kanban_card_id
1504 FROM wip_flow_schedules fs
1505 WHERE fs.wip_entity_id = fs_select_rec.wip_entity
1506 AND line_id = p_line_id
1507 AND organization_id = p_org_id;
1508
1509 V_ERROR_LINE := 6;
1510
1511 to_schedule_qty := to_schedule_qty - v_planned_quantity;
1512
1513 -- dbms_output.put_line('Number '||to_char(i)||'flow schedule created!!!');
1514 -- dbms_output.put_line('Planned quantity = '||to_char(v_planned_quantity));
1515
1516 END LOOP;
1517
1518 -- Calculate the remaining quantity that can be scheduled in the newest
1519 -- flow schedule for the item.
1520
1521 v_itemQty_tab(fs_select_rec.primary_item_id).remainQty :=
1522 v_order_quantity - v_planned_quantity;
1523
1524 v_itemQty_tab(fs_select_rec.primary_item_id).wip_id := fs_select_rec.wip_entity;
1525
1526 -- dbms_output.put_line('Remain quantity is:'||
1527 -- to_char(v_itemQty_tab(fs_select_rec.primary_item_id).remainQty));
1528
1529 END IF;
1530 END LOOP;
1531
1532 FOR item_list_rec IN item_list(p_org_id) LOOP
1533 IF v_itemQty_tab(item_list_rec.primary_item_id).remainQty > 0 THEN
1534
1535 V_ERROR_LINE := 7;
1536
1537 SELECT wip_entities_s.nextval
1538 INTO v_temp
1539 FROM dual;
1540
1541 V_ERROR_LINE := 8;
1542
1543 --Bug 6122344
1544 --v_schedule_number := NVL(substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20),'X')
1545 -- || to_char(v_temp);
1546 v_schedule_number := 'FLM-INTERNAL'|| to_char(v_temp);
1547
1548 INSERT INTO wip_flow_schedules(
1549 scheduled_flag,
1550 wip_entity_id,
1551 organization_id,
1552 last_update_date,
1553 last_updated_by,
1554 creation_date,
1555 created_by,
1556 class_code,
1557 line_id,
1558 primary_item_id,
1559 scheduled_start_date,
1560 planned_quantity,
1561 quantity_completed,
1562 quantity_scrapped,
1563 scheduled_completion_date,
1564 schedule_group_id,
1565 status,
1566 schedule_number,
1567 demand_source_header_id,
1568 demand_source_line,
1569 demand_source_delivery,
1570 demand_source_type,
1571 project_id,
1572 task_id,
1573 end_item_unit_number,
1574 request_id,
1575 attribute1,
1576 attribute2,
1577 attribute3,
1578 attribute4,
1579 attribute5,
1580 attribute6,
1581 attribute7,
1582 attribute8,
1583 attribute9,
1584 attribute10,
1585 attribute11,
1586 attribute12,
1587 attribute13,
1588 attribute14,
1589 attribute15,
1590 material_account,
1591 material_overhead_account,
1592 resource_account,
1593 outside_processing_account,
1594 material_variance_account,
1595 resource_variance_account,
1596 outside_proc_variance_account,
1597 std_cost_adjustment_account,
1598 overhead_account,
1599 overhead_variance_account,
1600 bom_revision, /* Added for bug 2185087 */
1601 routing_revision,
1602 bom_revision_date,
1603 routing_revision_date,
1604 alternate_bom_designator,
1605 alternate_routing_designator,
1606 completion_subinventory,
1607 completion_locator_id,
1608 demand_class,
1609 attribute_category,
1610 kanban_card_id)
1611
1612
1613 SELECT C_NO,
1614 v_temp,
1615 p_org_id,
1616 SYSDATE,
1617 fs.last_updated_by,
1618 SYSDATE,
1619 fs.created_by,
1620 fs.class_code,
1621 fs.line_id,
1622 fs.primary_item_id,
1623 fs.scheduled_completion_date,
1624 v_itemQty_tab(item_list_rec.primary_item_id).remainQty,
1625 0,
1626 0,
1627 fs.scheduled_completion_date,
1628 fs.schedule_group_id,
1629 fs.status,
1630 v_schedule_number,
1631 fs.demand_source_header_id,
1632 fs.demand_source_line,
1633 fs.demand_source_delivery,
1634 fs.demand_source_type,
1635 fs.project_id,
1636 fs.task_id,
1637 fs.end_item_unit_number,
1638 USERENV('SESSIONID'),
1639 fs.attribute1,
1640 fs.attribute2,
1641 fs.attribute3,
1642 fs.attribute4,
1643 fs.attribute5,
1644 fs.attribute6,
1645 fs.attribute7,
1646 fs.attribute8,
1647 fs.attribute9,
1648 fs.attribute10,
1649 fs.attribute11,
1650 fs.attribute12,
1651 fs.attribute13,
1652 fs.attribute14,
1653 fs.attribute15,
1654 fs.material_account,
1655 fs.material_overhead_account,
1656 fs.resource_account,
1657 fs.outside_processing_account,
1658 fs.material_variance_account,
1659 fs.resource_variance_account,
1660 fs.outside_proc_variance_account,
1661 fs.std_cost_adjustment_account,
1662 fs.overhead_account,
1663 fs.overhead_variance_account,
1664 fs.bom_revision, /* Added for bug 2185087 */
1665 fs.routing_revision,
1666 fs.bom_revision_date,
1667 fs.routing_revision_date,
1668 fs.alternate_bom_designator,
1669 fs.alternate_routing_designator,
1670 fs.completion_subinventory,
1671 fs.completion_locator_id,
1672 fs.demand_class,
1673 fs.attribute_category,
1674 fs.kanban_card_id
1675 FROM wip_flow_schedules fs
1676 WHERE fs.wip_entity_id = v_itemQty_tab(item_list_rec.primary_item_id).wip_id
1677 AND line_id = p_line_id
1678 AND organization_id = p_org_id;
1679
1680 V_ERROR_LINE := 9;
1681
1682 END IF;
1683 END LOOP;
1684
1685 DELETE
1686 FROM wip_flow_schedules
1687 WHERE planned_quantity = 0
1688 AND line_id = p_line_id
1689 AND organization_id = p_org_id;
1690
1691
1692 IF (dbms_sql.is_open(cursor_name)) THEN
1693 dbms_sql.close_cursor(cursor_name);
1694 END IF;
1695 EXCEPTION
1696 WHEN NO_DATA_FOUND THEN
1697 -- dbms_output.put_line('i did not find data');
1698 IF (dbms_sql.is_open(cursor_name)) THEN
1699 dbms_sql.close_cursor(cursor_name);
1700 END IF;
1701 RETURN;
1702 WHEN OTHERS THEN
1703
1704 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1705 FND_MSG_PUB.Add_Exc_Msg(
1706 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
1707 END IF;
1708
1709 IF (dbms_sql.is_open(cursor_name)) THEN
1710 dbms_sql.close_cursor(cursor_name);
1711 END IF;
1712 -- dbms_output.put_line('!!!ERROR!!!:' || to_char(sqlcode) ||
1713 -- substr(sqlerrm,1,60));
1714 RETURN;
1715
1716 END create_po_fs;
1717
1718 -- This procedure rounds up the planned quantity of all flow schedules
1719 -- that have fractional quantities with item rounding attribute set to yes
1720 -- Excess is trimmed off from flow schedules starting from the end of the sequence.
1721
1722 PROCEDURE rounding_process(
1723 p_org_id IN NUMBER,
1724 p_line_id IN NUMBER,
1725 p_rule_id IN NUMBER,
1726 p_demand_tab IN OUT NOCOPY DemandTabTyp)
1727 IS
1728
1729 cursor_name INTEGER;
1730 fs_select_rec fs_select_type;
1731 v_last_wip NUMBER;
1732 v_round_total NUMBER;
1733 v_round_qty NUMBER;
1734
1735 BEGIN
1736
1737 V_PROCEDURE_NAME := 'Rounding_Process';
1738 V_ERROR_LINE := 1;
1739
1740 v_last_wip := 0;
1741 v_round_total := 0;
1742
1743 FOR item_list_rec IN item_list(p_org_id) LOOP
1744
1745 IF p_demand_tab(item_list_rec.primary_item_id).roundType = C_ROUND_TYPE THEN
1746
1747 V_ERROR_LINE := 2;
1748 cursor_name := Create_Cursor(p_rule_id,p_org_id,p_line_id,C_ASC,C_ITEM,
1749 item_list_rec.primary_item_id);
1750
1751 V_ERROR_LINE := 3;
1752
1753 LOOP
1754 if dbms_sql.fetch_rows(cursor_name) > 0 then
1755 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
1756 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
1757 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
1758 end if;
1759
1760 IF v_last_wip = fs_select_rec.wip_entity THEN
1761 EXIT;
1762 END IF;
1763
1764 v_round_qty := TRUNC(fs_select_rec.planned_quantity-0.0000000001) + 1;
1765
1766 IF v_round_qty > fs_select_rec.planned_quantity THEN
1767
1768 v_round_total := v_round_total +
1769 (v_round_qty - fs_select_rec.planned_quantity);
1770
1771 UPDATE wip_flow_schedules fs
1772 SET planned_quantity = v_round_qty
1773 WHERE fs.wip_entity_id = fs_select_rec.wip_entity
1774 AND organization_id = p_org_id;
1775
1776 V_ERROR_LINE := 4;
1777
1778 END IF;
1779
1780 v_last_wip := fs_select_rec.wip_entity;
1781
1782 END LOOP;
1783
1784 v_round_total := TRUNC(v_round_total);
1785
1786 v_last_wip := 0;
1787
1788 V_ERROR_LINE := 5;
1789
1790 cursor_name := Create_Cursor(p_rule_id,p_org_id,p_line_id,C_DESC,C_ITEM,
1791 item_list_rec.primary_item_id);
1792
1793 V_ERROR_LINE := 6;
1794
1795
1796
1797 LOOP
1798 if dbms_sql.fetch_rows(cursor_name) > 0 then
1799 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
1800 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
1801 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
1802 end if;
1803
1804 IF v_last_wip = fs_select_rec.wip_entity THEN
1805 EXIT;
1806 END IF;
1807
1808 IF fs_select_rec.planned_quantity > v_round_total THEN
1809
1810 V_ERROR_LINE := 7;
1811
1812 UPDATE wip_flow_schedules fs
1813 SET planned_quantity = fs_select_rec.planned_quantity -
1814 v_round_total
1815 WHERE fs.wip_entity_id = fs_select_rec.wip_entity
1816 AND organization_id = p_org_id;
1817
1818 V_ERROR_LINE := 8;
1819
1820 v_round_total := 0;
1821
1822 ELSE
1823 v_round_total := v_round_total - fs_select_rec.planned_quantity;
1824
1825 V_ERROR_LINE := 9;
1826
1827 DELETE
1828 FROM wip_flow_schedules
1829 WHERE wip_entity_id = fs_select_rec.wip_entity
1830 AND organization_id = p_org_id;
1831
1832 V_ERROR_LINE := 10;
1833
1834 END IF;
1835
1836 IF v_round_total = 0 THEN
1837 EXIT;
1838 END IF;
1839
1840 v_last_wip := fs_select_rec.wip_entity;
1841
1842 END LOOP;
1843 END IF;
1844 END LOOP;
1845
1846 EXCEPTION
1847 WHEN NO_DATA_FOUND THEN
1848 -- IF (dbms_sql.is_open(cursor_name)) THEN
1849 -- dbms_sql.close_cursor(cursor_name);
1850 -- END IF;
1851
1852 RETURN;
1853
1854 WHEN OTHERS THEN
1855
1856 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1857 FND_MSG_PUB.Add_Exc_Msg(
1858 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
1859 END IF;
1860
1861 IF (dbms_sql.is_open(cursor_name)) THEN
1862 dbms_sql.close_cursor(cursor_name);
1863 end IF;
1864
1865 return;
1866
1867 END rounding_process;
1868
1869
1870 -- This procedure sequences the unscheduled flow schedules on the line using
1871 -- the specified criteria and then schedules them on the line.
1872
1873 PROCEDURE schedule_orders (
1874 p_line_id IN NUMBER,
1875 p_org_id IN NUMBER,
1876 p_rule_id IN NUMBER,
1877 p_cap_tab IN CapTabTyp,
1878 p_demand_tab IN DemandTabTyp,
1879 p_time_tab IN OUT NOCOPY TimeTabTyp)
1880 IS
1881
1882 v_current_date NUMBER;
1883 v_current_cap NUMBER;
1884 v_remain_qty NUMBER;
1885 v_current_wip NUMBER;
1886 v_build_seq NUMBER;
1887 v_temp NUMBER;
1888 v_null_build_seq NUMBER;
1889 v_build_seq_tab BuildSeqTabTyp;
1890 v_start_time NUMBER;
1891 v_end_time NUMBER;
1892 v_hr_line_rate NUMBER;
1893 v_line_rate NUMBER;
1894 v_required_time NUMBER;
1895 v_begin_time DATE;
1896 v_completion_time DATE;
1897 v_final_time NUMBER;
1898 fs_select_rec fs_select_type;
1899 cursor_name INTEGER;
1900 v_last_wip NUMBER;
1901 v_schedule_number VARCHAR2(30);
1902 v_current_item NUMBER;
1903 qty_temp NUMBER;
1904 date_temp date;
1905 trans_temp number;
1906 l_fixed_lead_time NUMBER;
1907 l_variable_lead_time NUMBER;
1908 l_lead_time NUMBER;
1909
1910 BEGIN
1911
1912 V_PROCEDURE_NAME := 'Schedule_Orders';
1913 V_ERROR_LINE := 1;
1914
1915 -- Set current date to be the first valid date in the scheduling window.
1916 -- This is the first index in the capacity table.
1917
1918 v_current_date := p_cap_tab.FIRST;
1919 v_current_cap := p_cap_tab(v_current_date).capacity;
1920
1921 -- dbms_output.put_line('First valid date in scheduling window is:' ||
1922 -- to_date(v_current_date,'J'));
1923 --dbms_output.put_line('Available capacity on this date is:' ||
1924 -- to_char(v_current_cap));
1925
1926 -- dbms_output.put_line('Calculating max build sequences');
1927 -- Obtain the maximum build sequence for each schedule group from scheduled
1928 -- flow schedules on the line.
1929
1930 calculate_build_sequences(p_org_id, p_line_id, v_build_seq_tab);
1931
1932 --dbms_output.put_line('AFTER the Schedule group loop!!!');
1933 -- dbms_output.put_line('AFTER the Schedule group loop!!!');
1934
1935 -- Obtain the maximum sequence for the null schedule group
1936 SELECT NVL(MAX(build_sequence),0)
1937 INTO v_null_build_seq
1938 FROM wip_flow_schedules fs
1939 WHERE fs.schedule_group_id IS NULL
1940 AND fs.line_id = p_line_id
1941 AND fs.organization_id = p_org_id
1942 AND scheduled_flag = C_YES;
1943
1944 V_ERROR_LINE := 2;
1945
1946 -- dbms_output.put_line('The max sequence for the null schedule group is: '||
1947 -- to_char(v_null_build_seq));
1948
1949 -- Obtain information on the start time and end time of the line for
1950 -- time stamping flow schedules.
1951
1952 SELECT maximum_rate, start_time, stop_time
1953 INTO v_hr_line_rate, v_start_time, v_end_time
1954 FROM wip_lines
1955 WHERE line_id = p_line_id
1956 AND organization_id = p_org_id;
1957
1958 V_ERROR_LINE := 3;
1959
1960 -- Fix bug 939061, add 24 hrs to stop_time if stop_time <= start_time
1961 if (v_end_time <= v_start_time) then
1962 v_end_time := v_end_time+24*3600;
1963 end if;
1964
1965 v_line_rate := TRUNC(v_hr_line_rate * (v_end_time - v_start_time)/3600);
1966
1967 -- dbms_output.put_line('Maximum rate = '||to_char(v_line_rate));
1968 -- dbms_output.put_line('Start time = '||to_char(v_start_time));
1969 -- dbms_output.put_line('Maximum rate = '||to_char(v_end_time));
1970
1971 -- New and existing flow schedules will be time stamped and
1972 -- scheduled before existing wip jobs and repetitive schedules.
1973
1974 -- Order the existing flow schedules by schedule group id and build sequence
1975 -- and assign the appropriate time stamps to these flow schedules first.
1976 -- dbms_output.put_line('Putting time stamps on existing flow schedules');
1977
1978 -- time_existing_fs(p_org_id, p_line_id, p_cap_tab, v_time_tab);
1979
1980 V_ERROR_LINE := 4;
1981
1982 cursor_name := Create_Cursor(p_rule_id,p_org_id,p_line_id,C_ASC,C_NORM,C_NORM);
1983
1984 V_ERROR_LINE := 5;
1985
1986 LOOP
1987 if dbms_sql.fetch_rows(cursor_name) > 0 then
1988 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
1989 dbms_sql.column_value(cursor_name,2, fs_select_rec.creation_date);
1990 dbms_sql.column_value(cursor_name,3, fs_select_rec.schedule_date);
1991 dbms_sql.column_value(cursor_name,4, fs_select_rec.promise_date);
1992 dbms_sql.column_value(cursor_name,5, fs_select_rec.request_date);
1993 dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
1994 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
1995 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
1996 dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
1997
1998 end if;
1999
2000
2001 SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
2002 INTO l_fixed_lead_time, l_variable_lead_time
2003 FROM MTL_SYSTEM_ITEMS
2004 WHERE inventory_item_id = fs_select_rec.primary_item_id
2005 AND organization_id = p_org_id;
2006
2007
2008 -- dbms_output.put_line('INSIDE the FLOW SCHEDULE CURSOR loop!!!');
2009
2010 IF v_last_wip = fs_select_rec.wip_entity THEN
2011 RETURN;
2012 END IF;
2013
2014 v_last_wip := fs_select_rec.wip_entity;
2015 v_remain_qty := fs_select_rec.planned_quantity;
2016 v_current_wip := fs_select_rec.wip_entity;
2017
2018 select scheduled_completion_date, demand_source_line
2019 into date_temp, trans_temp
2020 from wip_flow_schedules
2021 where wip_entity_id = v_current_wip
2022 and organization_id = p_org_id;
2023
2024 -- LOOP used for making sure that all quantity of the flow schedule has
2025 -- been scheduled.
2026
2027 -- dbms_output.put_line('Inside the flow schedule loop');
2028
2029 WHILE v_remain_qty > 0 LOOP
2030
2031
2032
2033 -- dbms_output.put_line('The current flow schedule is: ' ||to_char
2034 -- (v_current_wip));
2035 -- dbms_output.put_line('Remain quantity for current flow schedule ' ||
2036 -- to_char(v_current_wip)||' is ' ||to_char(v_remain_qty));
2037
2038 -- Get the next build sequence for the schedule group of the current
2039 -- flow schedule.
2040
2041 IF fs_select_rec.schedule_group_id IS NOT NULL THEN
2042 v_build_seq :=
2043 v_build_seq_tab(fs_select_rec.schedule_group_id).buildseq + 1;
2044 v_build_seq_tab(fs_select_rec.schedule_group_id).buildseq := v_build_seq;
2045 ELSE
2046 v_build_seq := v_null_build_seq + 1;
2047 v_null_build_seq := v_build_seq;
2048 END IF;
2049
2050 -- dbms_output.put_line('The next build sequence is '||to_char
2051 -- (v_build_seq)|| 'for schedule group '||
2052 -- to_char(fs_select_rec.schedule_group_id));
2053
2054 -- If the capacity of the current date is 0, get the next valid work
2055 -- date and set the capacity to the capacity of this new date.
2056 -- If there is no next valid work date, we are at the end of the
2057 -- scheduling window and we are finished with scheduling.
2058
2059 WHILE v_current_cap = 0 LOOP
2060 -- dbms_output.put_line('0 capacity on '||to_date(v_current_date,'J'));
2061
2062 IF v_current_date = p_cap_tab.LAST THEN
2063 -- dbms_output.put_line(to_date(v_current_date,'J')||'was the last day
2064 -- of the scheduling window. scheduling is completed. exiting ...');
2065 RETURN;
2066 ELSE
2067 v_current_date := p_cap_tab.NEXT(v_current_date);
2068 v_current_cap := p_cap_tab(v_current_date).capacity;
2069
2070 -- dbms_output.put_line('Next valid work date is: '||
2071 -- to_date(v_current_date,'J'));
2072 -- dbms_output.put_line('Capacity available = '||to_char(v_current_cap));
2073
2074 END IF;
2075 END LOOP;
2076
2077 -- If there is no entry in the completion time table for the date
2078 -- initialize it with the start time
2079
2080 IF p_time_tab.EXISTS(v_current_date) = FALSE THEN
2081 SELECT start_time
2082 INTO v_final_time
2083 FROM wip_lines
2084 WHERE line_id = p_line_id
2085 AND organization_id = p_org_id;
2086
2087 V_ERROR_LINE := 6;
2088
2089 p_time_tab(v_current_date).start_completion_time := v_final_time;
2090
2091 -- dbms_output.put_line('Initialization:
2092 -- For '||to_date(v_current_date,'J')||',the completion time is '||
2093 -- to_char(p_time_tab(v_current_date).start_time));
2094 END IF;
2095
2096 -- After scheduling, set the completion date to today's date and
2097 -- set the scheduled flag to yes. Update the build sequence of the
2098 -- flow schedule.
2099
2100 IF v_remain_qty <= v_current_cap THEN
2101 -- dbms_output.put_line(to_char(v_current_wip)||'can be scheduled');
2102
2103 v_required_time := TRUNC(v_remain_qty * 3600/v_hr_line_rate);
2104 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
2105 v_required_time := p_time_tab(v_current_date).end_completion_time -
2106 p_time_tab(v_current_date).start_completion_time;
2107 end if;
2108
2109 v_completion_time := to_date(v_current_date,'J') +
2110 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
2111
2112 l_lead_time := l_fixed_lead_time +
2113 (l_variable_lead_time * (v_remain_qty-1));
2114
2115 IF l_lead_time = 0 THEN
2116 v_begin_time := v_completion_time;
2117 ELSE
2118 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
2119 p_org_id,
2120 v_completion_time,
2121 l_lead_time,
2122 v_start_time,
2123 v_end_time);
2124 END IF;
2125
2126
2127 UPDATE wip_flow_schedules
2128 SET scheduled_start_date = v_begin_time,
2129 scheduled_completion_date = v_completion_time,
2130 scheduled_flag = C_YES,
2131 build_sequence = v_build_seq
2132 WHERE wip_entity_id = v_current_wip
2133 AND organization_id = p_org_id;
2134
2135 select planned_quantity
2136 into qty_temp
2137 from wip_flow_schedules
2138 where wip_entity_id = v_current_wip
2139 and organization_id = p_org_id;
2140
2141 V_ERROR_LINE := 7;
2142
2143 p_time_tab(v_current_date).start_completion_time :=
2144 p_time_tab(v_current_date).start_completion_time + v_required_time;
2145
2146 -- Decrement capacity on the line for the current date by the scheduled
2147 -- quantity.
2148
2149 v_current_cap := v_current_cap - v_remain_qty;
2150
2151 -- dbms_output.put_line('Capacity on '||to_date(v_current_date,'J')||' = '||
2152 -- to_char(v_current_cap));
2153
2154 -- The entire flow schedule has been scheduled and the
2155 -- remaining quantity = 0
2156
2157 v_remain_qty := 0;
2158
2159 ELSIF (v_remain_qty > v_current_cap) THEN
2160 -- dbms_output.put_line(to_char(v_current_wip)||'can not be scheduled');
2161 -- dbms_output.put_line('Capacity on '||to_date(v_current_date,'J')||' = '||
2162 -- to_char(v_current_cap));
2163
2164 SELECT primary_item_id
2165 INTO v_current_item
2166 FROM wip_flow_schedules
2167 WHERE wip_entity_id = v_current_wip
2168 AND organization_id = p_org_id;
2169
2170 V_ERROR_LINE := 8;
2171
2172 IF p_demand_tab(v_current_item).roundType = 1 THEN
2173 v_current_cap := TRUNC(v_current_cap);
2174
2175 WHILE v_current_cap = 0 LOOP
2176
2177 IF v_current_date = p_cap_tab.LAST THEN
2178 RETURN;
2179 ELSE
2180 v_current_date := p_cap_tab.NEXT(v_current_date);
2181 v_current_cap := p_cap_tab(v_current_date).capacity;
2182 v_current_cap := TRUNC(v_current_cap);
2183 END IF;
2184 END LOOP;
2185 END IF;
2186
2187 -- dbms_output.put_line('current date is: '||to_char(v_current_date));
2188 -- dbms_output.put_line('Current cap is: '||to_char(v_current_cap));
2189
2190 IF v_current_cap < v_remain_qty THEN
2191 -- If there is no entry in the completion time table for the date
2192 -- initialize it with the start time
2193
2194 IF p_time_tab.EXISTS(v_current_date) = FALSE THEN
2195 SELECT start_time
2196 INTO v_final_time
2197 FROM wip_lines
2198 WHERE line_id = p_line_id
2199 AND organization_id = p_org_id;
2200
2201 V_ERROR_LINE := 9;
2202
2203 p_time_tab(v_current_date).start_completion_time := v_final_time;
2204 END IF;
2205
2206 v_required_time := TRUNC(v_current_cap * 3600/v_hr_line_rate);
2207
2208 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
2209 v_required_time := p_time_tab(v_current_date).end_completion_time -
2210 p_time_tab(v_current_date).start_completion_time;
2211 end if;
2212
2213 v_completion_time := to_date(v_current_date,'J') +
2214 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
2215
2216 l_lead_time := l_fixed_lead_time +
2217 (l_variable_lead_time * (v_current_cap-1));
2218
2219 IF l_lead_time = 0 THEN
2220 v_begin_time := v_completion_time;
2221 ELSE
2222 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
2223 p_org_id,
2224 v_completion_time,
2225 l_lead_time,
2226 v_start_time,
2227 v_end_time);
2228 END IF;
2229
2230
2231 UPDATE wip_flow_schedules
2232 SET scheduled_start_date = v_begin_time,
2233 scheduled_completion_date = v_completion_time,
2234 planned_quantity = v_current_cap,
2235 scheduled_flag = C_YES,
2236 build_sequence = v_build_seq
2237 WHERE wip_entity_id = v_current_wip
2238 AND organization_id = p_org_id;
2239
2240 select planned_quantity
2241 into qty_temp
2242 from wip_flow_schedules
2243 where wip_entity_id = v_current_wip
2244 and organization_id = p_org_id;
2245
2246 V_ERROR_LINE := 10;
2247
2248 p_time_tab(v_current_date).start_completion_time :=
2249 p_time_tab(v_current_date).start_completion_time + v_required_time;
2250
2251 -- Create a new flow schedule to carry over the unscheduled quantity
2252 SELECT wip_entities_s.nextval
2253 INTO v_temp
2254 FROM dual;
2255
2256 V_ERROR_LINE := 11;
2257
2258 --Bug 6122344
2259 --v_schedule_number := NVL(substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20),'X') || to_char(v_temp);
2260 v_schedule_number := 'FLM-INTERNAL'|| to_char(v_temp);
2261
2262 INSERT INTO wip_flow_schedules(
2263 scheduled_flag,
2264 wip_entity_id,
2265 organization_id,
2266 last_update_date,
2267 last_updated_by,
2268 creation_date,
2269 created_by,
2270 class_code,
2271 line_id,
2272 primary_item_id,
2273 scheduled_start_date,
2274 planned_quantity,
2275 quantity_completed,
2276 quantity_scrapped,
2277 scheduled_completion_date,
2278 schedule_group_id,
2279 status,
2280 schedule_number,
2281 demand_source_header_id,
2282 demand_source_line,
2283 demand_source_delivery,
2284 demand_source_type,
2285 project_id,
2286 task_id,
2287 end_item_unit_number,
2288 request_id,
2289 attribute1,
2290 attribute2,
2291 attribute3,
2292 attribute4,
2293 attribute5,
2294 attribute6,
2295 attribute7,
2296 attribute8,
2297 attribute9,
2298 attribute10,
2299 attribute11,
2300 attribute12,
2301 attribute13,
2302 attribute14,
2303 attribute15,
2304 material_account,
2305 material_overhead_account,
2306 resource_account,
2307 outside_processing_account,
2308 material_variance_account,
2309 resource_variance_account,
2310 outside_proc_variance_account,
2311 std_cost_adjustment_account,
2312 overhead_account,
2313 overhead_variance_account,
2314 bom_revision, /* Added for bug 2185087 */
2315 routing_revision,
2316 bom_revision_date,
2317 routing_revision_date,
2318 alternate_bom_designator,
2319 alternate_routing_designator,
2320 completion_subinventory,
2321 completion_locator_id,
2322 demand_class,
2323 attribute_category,
2324 kanban_card_id)
2325
2326 SELECT C_NO,
2327 v_temp,
2328 p_org_id,
2329 SYSDATE,
2330 fs.last_updated_by,
2331 SYSDATE,
2332 fs.created_by,
2333 fs.class_code,
2334 fs.line_id,
2335 fs.primary_item_id,
2336 to_date(v_current_date,'J'),
2337 v_remain_qty - v_current_cap,
2338 0,
2339 0,
2340 to_date(v_current_date,'J'),
2341 fs.schedule_group_id,
2342 fs.status,
2343 v_schedule_number,
2344 fs.demand_source_header_id,
2345 fs.demand_source_line,
2346 fs.demand_source_delivery,
2347 fs.demand_source_type,
2348 fs.project_id,
2349 fs.task_id,
2350 fs.end_item_unit_number,
2351 USERENV('SESSIONID'),
2352 fs.attribute1,
2353 fs.attribute2,
2354 fs.attribute3,
2355 fs.attribute4,
2356 fs.attribute5,
2357 fs.attribute6,
2358 fs.attribute7,
2359 fs.attribute8,
2360 fs.attribute9,
2361 fs.attribute10,
2362 fs.attribute11,
2363 fs.attribute12,
2364 fs.attribute13,
2365 fs.attribute14,
2366 fs.attribute15,
2367 fs.material_account,
2368 fs.material_overhead_account,
2369 fs.resource_account,
2370 fs.outside_processing_account,
2371 fs.material_variance_account,
2372 fs.resource_variance_account,
2373 fs.outside_proc_variance_account,
2374 fs.std_cost_adjustment_account,
2375 fs.overhead_account,
2376 fs.overhead_variance_account,
2377 fs.bom_revision, /* Added for bug 2185087 */
2378 fs.routing_revision,
2379 fs.bom_revision_date,
2380 fs.routing_revision_date,
2381 fs.alternate_bom_designator,
2382 fs.alternate_routing_designator,
2383 fs.completion_subinventory,
2384 fs.completion_locator_id,
2385 fs.demand_class,
2386 fs.attribute_category,
2387 fs.kanban_card_id
2388 FROM wip_flow_schedules fs
2389 WHERE fs.wip_entity_id = fs_select_rec.wip_entity
2390 AND organization_id = p_org_id;
2391
2392 V_ERROR_LINE := 12;
2393
2394 -- Reset the remaining quantity to be scheduled as the
2395 -- left over quantity from the last flow schedule.
2396
2397 v_remain_qty := v_remain_qty - v_current_cap;
2398
2399 -- Set the capacity for current date to 0.
2400
2401 v_current_cap := 0;
2402
2403 -- Point to the newly created flow schedule in order to
2404 -- schedule the quantity.
2405
2406 v_current_wip := v_temp;
2407 END IF;
2408 END IF;
2409 END LOOP;
2410 END LOOP;
2411
2412 IF (dbms_sql.is_open(cursor_name)) THEN
2413 dbms_sql.close_cursor(cursor_name);
2414 END IF;
2415
2416 EXCEPTION
2417 WHEN NO_DATA_FOUND THEN
2418 IF (dbms_sql.is_open(cursor_name)) THEN
2419 dbms_sql.close_cursor(cursor_name);
2420 END IF;
2421 RETURN;
2422
2423 WHEN OTHERS THEN
2424
2425 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2426 FND_MSG_PUB.Add_Exc_Msg(
2427 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
2428 END IF;
2429
2430 IF (dbms_sql.is_open(cursor_name)) THEN
2431 dbms_sql.close_cursor(cursor_name);
2432 end IF;
2433 -- dbms_output.put_line('!!!ERROR!!!:' || to_char(sqlcode) ||
2434 -- substr(sqlerrm,1,60));
2435 return;
2436
2437 END schedule_orders;
2438
2439 -- This procedure calculates the production plan for each item for each date
2440 -- between the first date to the calculated end date in the scheduling window.
2441
2442 PROCEDURE calculate_production_plan(
2443 p_org_id IN NUMBER,
2444 p_line_id IN NUMBER,
2445 p_first_date IN DATE,
2446 p_last_date IN DATE,
2447 p_cap_tab IN CapTabTyp,
2448 p_demand_tab IN OUT NOCOPY DemandTabTyp)
2449 IS
2450 v_begin_date NUMBER;
2451 v_finish_date NUMBER;
2452 v_current_date NUMBER;
2453 v_ratio NUMBER;
2454 v_production NUMBER;
2455 v_trunc_sum NUMBER;
2456 v_cap_diff NUMBER;
2457 v_item_id NUMBER;
2458 v_quantity NUMBER;
2459 v_max_diff NUMBER;
2460 v_decrement_qty NUMBER;
2461 v_finish_flag NUMBER;
2462 v_round_type NUMBER;
2463 v_item_round NUMBER;
2464 v_item_round_qty NUMBER;
2465
2466 -- Cursor used to determine if any items of no rounding exists.
2467 CURSOR no_round_cursor IS
2468 SELECT number1,number2
2469 FROM mrp_form_query mfq, mtl_system_items mtl
2470 WHERE mfq.date1 = to_date(v_current_date,'J')
2471 AND mfq.query_id = v_query_id
2472 AND mfq.number1 = mtl.inventory_item_id
2473 AND NVL(mtl.rounding_control_type,2) <> C_ROUND_TYPE
2474 AND mtl.organization_id = p_org_id
2475 ORDER BY number2;
2476
2477 BEGIN
2478
2479 V_PROCEDURE_NAME := 'Calculate_Production_Plan';
2480 V_ERROR_LINE := 1;
2481
2482 v_begin_date := to_number(to_char(p_first_date,'J'));
2483 v_finish_date := to_number(to_char(p_last_date,'J'));
2484
2485 SELECT mrp_form_query_s.nextval
2486 INTO v_query_id
2487 FROM DUAL;
2488
2489 V_ERROR_LINE := 2;
2490
2491 FOR i in v_begin_date..v_finish_date LOOP
2492
2493 v_current_date := i;
2494
2495 -- dbms_output.put_line('The date is: '||to_date(v_current_date,'J'));
2496
2497 IF p_cap_tab.EXISTS(v_current_date)AND
2498 p_cap_tab(v_current_date).capacity > 0 THEN
2499
2500 -- dbms_output.put_line('The capacity is: '||
2501 -- to_char(p_cap_tab(v_current_date).capacity));
2502
2503 FOR item_list_rec IN item_list(p_org_id) LOOP
2504
2505 -- dbms_output.put_line('The item is: '||to_char(item_list_rec.primary_item_id));
2506 v_ratio := p_demand_tab(item_list_rec.primary_item_id).totalDemand/
2507 V_GLOBAL_DEMAND;
2508
2509 -- dbms_output.put_line('The ratio is: '||to_char(v_ratio));
2510
2511 -- Use whole number for line capacity
2512
2513 v_production := TRUNC((v_ratio *
2514 p_cap_tab(v_current_date).capacity),2);
2515
2516 -- dbms_output.put_line('The production is: '||to_char(v_production));
2517
2518 INSERT INTO MRP_FORM_QUERY (
2519 QUERY_ID,
2520 LAST_UPDATE_DATE,
2521 LAST_UPDATED_BY,
2522 CREATION_DATE,
2523 CREATED_BY,
2524 DATE1,
2525 NUMBER1,
2526 NUMBER2,
2527 NUMBER3)
2528 VALUES (
2529 v_query_id,
2530 SYSDATE,
2531 1,
2532 SYSDATE,
2533 1,
2534 to_date(v_current_date,'J'),
2535 item_list_rec.primary_item_id,
2536 TRUNC(v_production),
2537 v_production - TRUNC(v_production));
2538
2539 -- If the item is of no round type, then we don't want to round.
2540 -- Therefore, we use the original production plan and we set
2541 -- the difference between production and truncated production to 0.
2542
2543 IF p_demand_tab(item_list_rec.primary_item_id).roundType <> C_ROUND_TYPE THEN
2544
2545 --dbms_output.put_line('Item '||to_char(item_list_rec.primary_item_id)||'is not a round item');
2546
2547 UPDATE MRP_FORM_QUERY
2548 SET number2 = v_production,
2549 number3 = 0
2550 WHERE number1 = item_list_rec.primary_item_id
2551 AND query_id = v_query_id
2552 AND date1 = to_date(v_current_date,'J');
2553
2554 END IF;
2555
2556 V_ERROR_LINE := 3;
2557
2558 -- dbms_output.put_line('The diff for item is: '||
2559 -- to_char(v_production - TRUNC(v_production)));
2560
2561 END LOOP;
2562
2563 -- Find the sum of all production for the current date.
2564
2565 SELECT SUM(number2)
2566 INTO v_trunc_sum
2567 FROM mrp_form_query
2568 WHERE query_id = v_query_id
2569 AND date1 = to_date(v_current_date,'J');
2570
2571 V_ERROR_LINE := 4;
2572
2573 -- dbms_output.put_line('The planned sum is: '||to_char(v_trunc_sum));
2574
2575 -- Find the difference between line capacity and sum of all planned
2576 -- production.
2577
2578 v_cap_diff := p_cap_tab(v_current_date).capacity - v_trunc_sum;
2579
2580 -- dbms_output.put_line('The diff is: '||to_char(v_cap_diff));
2581
2582 FOR production_round_rec IN production_round(v_current_date,v_query_id)
2583 LOOP
2584
2585 IF v_cap_diff = 0 THEN
2586 EXIT;
2587
2588 ELSIF v_cap_diff < 1 THEN
2589
2590 OPEN no_round_cursor;
2591 FETCH no_round_cursor INTO v_item_round, v_item_round_qty;
2592 IF no_round_cursor%NOTFOUND THEN
2593 CLOSE no_round_cursor;
2594 EXIT;
2595
2596 ELSE
2597
2598 UPDATE mrp_form_query
2599 SET number2 = v_item_round_qty + v_cap_diff
2600 WHERE number1 = v_item_round
2601 AND date1 = to_date(v_current_date,'J')
2602 AND query_id = v_query_id;
2603 CLOSE no_round_cursor;
2604 EXIT;
2605 END IF;
2606
2607 ELSE
2608
2609 -- dbms_output.put_line('Updating ...'||to_char(production_round_rec.number1));
2610
2611 UPDATE mrp_form_query
2612 SET number2 =(SELECT number2 +1
2613 FROM mrp_form_query
2614 WHERE date1 = to_date(v_current_date,'J')
2615 AND query_id = v_query_id
2616 AND number1 = production_round_rec.number1)
2617 WHERE date1 = to_date(v_current_date,'J')
2618 AND query_id = v_query_id
2619 AND number1 = production_round_rec.number1;
2620
2621 V_ERROR_LINE := 5;
2622
2623 v_cap_diff := v_cap_diff - 1 ;
2624
2625 -- dbms_output.put_line('Max item is: '||to_char(v_item_id));
2626 -- dbms_output.put_line('Quantity of item is: '||to_char(v_quantity));
2627 -- dbms_output.put_line('Max diff is: '||to_char(v_max_diff));
2628 END IF;
2629 END LOOP;
2630
2631 -- Update the total demand for each item as well as the global demand
2632 -- to reflect what has been allocated.
2633 FOR item_list_rec IN item_list(p_org_id) LOOP
2634
2635 v_finish_flag := -1;
2636
2637 SELECT number2
2638 INTO v_decrement_qty
2639 FROM mrp_form_query
2640 WHERE query_id = v_query_id
2641 AND number1 = item_list_rec.primary_item_id
2642 AND date1 = to_date(v_current_date,'J');
2643
2644 V_ERROR_LINE := 6;
2645
2646 -- If the current production plan is greater than the demand left
2647 -- for the item, then the decrement quantity is the demand itself.
2648
2649 IF p_demand_tab(item_list_rec.primary_item_id).totalDemand -
2650 v_decrement_qty < 0 THEN
2651 v_decrement_qty :=
2652 p_demand_tab(item_list_rec.primary_item_id).totalDemand;
2653 END IF;
2654
2655 p_demand_tab(item_list_rec.primary_item_id).totalDemand :=
2656 p_demand_tab(item_list_rec.primary_item_id).totalDemand -
2657 v_decrement_qty;
2658
2659 V_GLOBAL_DEMAND := V_GLOBAL_DEMAND - v_decrement_qty;
2660 -- dbms_output.put_line('GLOBAL DEMAND IS: '||to_char(V_GLOBAL_DEMAND));
2661
2662 -- If the global demand becomes 0, then the production allocation
2663 -- process is finished.
2664
2665 IF V_GLOBAL_DEMAND > 0 THEN
2666 v_finish_flag := 1;
2667 END IF;
2668
2669 END LOOP;
2670 ELSE
2671 FOR item_list_rec IN item_list(p_org_id) LOOP
2672 INSERT INTO MRP_FORM_QUERY (
2673 QUERY_ID,
2674 LAST_UPDATE_DATE,
2675 LAST_UPDATED_BY,
2676 CREATION_DATE,
2677 CREATED_BY,
2678 DATE1,
2679 NUMBER1,
2680 NUMBER2,
2681 NUMBER3)
2682 VALUES (
2683 v_query_id,
2684 SYSDATE,
2685 1,
2686 SYSDATE,
2687 1,
2688 to_date(v_current_date,'J'),
2689 item_list_rec.primary_item_id,
2690 0,
2691 0);
2692 END LOOP;
2693 END IF;
2694
2695 IF v_finish_flag = -1 THEN
2696 -- dbms_output.put_line('ALL FINISHED!!!');
2697 RETURN;
2698 END IF;
2699
2700 -- Roll the date forward to next date
2701 IF v_current_date = p_cap_tab.LAST THEN
2702 return;
2703 END IF;
2704
2705 END LOOP;
2706
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709
2710 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2711 FND_MSG_PUB.Add_Exc_Msg(
2712 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
2713 END IF;
2714
2715 RETURN;
2716
2717 END calculate_production_plan;
2718
2719 -- This procedure schedules the flow schedules on the line using the level
2720 -- daily rate algorithm
2721
2722 PROCEDURE schedule_orders_level(
2723 p_line_id IN NUMBER,
2724 p_org_id IN NUMBER,
2725 p_rule_id IN NUMBER,
2726 p_cap_tab IN CapTabTyp,
2727 p_time_tab IN OUT NOCOPY TimeTabTyp)
2728
2729 IS
2730
2731 v_demand_tab DemandTabTyp;
2732 v_build_seq_tab BuildSeqTabTyp;
2733 v_null_build_seq NUMBER;
2734 v_hr_line_rate NUMBER;
2735 v_line_rate NUMBER;
2736 v_start_time NUMBER;
2737 v_end_time NUMBER;
2738 v_item_alloc_tab ItemAllocTabTyp;
2739 v_remain_qty NUMBER;
2740 v_current_wip NUMBER;
2741 v_current_date NUMBER;
2742 v_alloc_qty NUMBER;
2743 v_build_seq NUMBER;
2744 v_final_time NUMBER;
2745 v_required_time NUMBER;
2746 v_completion_time DATE;
2747 v_begin_time DATE;
2748 v_temp NUMBER;
2749 fs_select_rec fs_select_type;
2750 cursor_name INTEGER;
2751 v_last_wip NUMBER;
2752 v_schedule_number VARCHAR2(30);
2753 v_end_date DATE;
2754 v_first_date DATE;
2755 v_finish_flag NUMBER;
2756 l_fixed_lead_time NUMBER;
2757 l_variable_lead_time NUMBER;
2758 l_lead_time NUMBER;
2759
2760 BEGIN
2761
2762 V_PROCEDURE_NAME := 'Schedule_Orders_Level';
2763 V_ERROR_LINE := 1;
2764
2765 -- Calculate total demand and demand ratio for each item
2766 calculate_demand(p_line_id, p_org_id, v_demand_tab);
2767
2768 -- Obtain information on the start time and end time of the line for
2769 -- time stamping flow schedules.
2770
2771 SELECT maximum_rate, start_time, stop_time
2772 INTO v_hr_line_rate, v_start_time, v_end_time
2773 FROM wip_lines
2774 WHERE line_id = p_line_id
2775 AND organization_id = p_org_id;
2776
2777 if (v_end_time <= v_start_time) then
2778 v_end_time := v_end_time+24*3600;
2779 end if;
2780
2781 V_ERROR_LINE := 2;
2782
2783 -- Calculate the production plan for each item on each date from the first
2784 -- date of the scheduling horizon to the date where all demand has been
2785 -- met or until the end of the scheduling window.
2786
2787 v_first_date := to_date(p_cap_tab.FIRST,'J');
2788 v_end_date := to_date(p_cap_tab.LAST,'J');
2789
2790 -- dbms_output.put_line('Calculating production plan ...');
2791
2792 calculate_production_plan(p_org_id, p_line_id, v_first_date, v_end_date,
2793 p_cap_tab, v_demand_tab);
2794
2795 -- dbms_output.put_line('Finished calculating production plan ...');
2796
2797 -- New and existing flow schedules will be time stamped and
2798 -- scheduled before existing wip jobs and repetitive schedules.
2799 -- Order the existing flow schedules by schedule group id and build sequence
2800 -- and assign the appropriate time stamps to these flow schedules first.
2801
2802 -- time_existing_fs(p_org_id, p_line_id, p_cap_tab, v_time_tab);
2803
2804 V_ERROR_LINE := 3;
2805
2806 cursor_name := Create_Cursor(p_rule_id, p_org_id, p_line_id, C_ASC,
2807 C_NORM,C_NORM);
2808
2809 V_ERROR_LINE := 4;
2810
2811 -- Set initial current date
2812
2813 v_current_date := p_cap_tab.FIRST;
2814
2815 UPDATE mrp_form_query
2816 SET number4 = C_DATE_ON
2817 WHERE query_id = v_query_id
2818 AND date1 = to_date(v_current_date,'J');
2819
2820 V_ERROR_LINE := 5;
2821
2822 LOOP
2823 IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
2824 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
2825 dbms_sql.column_value(cursor_name,2, fs_select_rec.creation_date);
2826 dbms_sql.column_value(cursor_name,3, fs_select_rec.schedule_date);
2827 dbms_sql.column_value(cursor_name,4, fs_select_rec.promise_date);
2828 dbms_sql.column_value(cursor_name,5, fs_select_rec.request_date);
2829 dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
2830 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
2831 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
2832 dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
2833 END IF;
2834
2835
2836 SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
2837 INTO l_fixed_lead_time, l_variable_lead_time
2838 FROM MTL_SYSTEM_ITEMS
2839 WHERE inventory_item_id = fs_select_rec.primary_item_id
2840 AND organization_id = p_org_id;
2841
2842
2843 IF v_last_wip = fs_select_rec.wip_entity THEN
2844 EXIT;
2845 END IF;
2846
2847 v_last_wip := fs_select_rec.wip_entity;
2848
2849 SELECT number2, to_number(to_char(date1,'J'))
2850 INTO v_alloc_qty, v_current_date
2851 FROM mrp_form_query
2852 WHERE query_id = v_query_id
2853 AND number1 = fs_select_rec.primary_item_id
2854 AND number4 = C_DATE_ON;
2855
2856 V_ERROR_LINE := 6;
2857
2858 v_remain_qty := fs_select_rec.planned_quantity;
2859 v_current_wip := fs_select_rec.wip_entity;
2860
2861 WHILE v_remain_qty > 0 LOOP
2862
2863 SELECT number5
2864 INTO v_finish_flag
2865 FROM mrp_form_query
2866 WHERE number1 = fs_select_rec.primary_item_id
2867 AND number4 = C_DATE_ON
2868 AND query_id = v_query_id;
2869
2870 V_ERROR_LINE := 7;
2871
2872 IF v_finish_flag = C_COMPLETE THEN
2873 EXIT;
2874 END IF;
2875
2876 -- If the remaining planned production of the current date is 0,
2877 -- get the planned production of the next valid work date until
2878 -- a date that allows a quantity for planned production greater
2879 -- than 0 for the item.
2880
2881 -- If we reach the last date of the scheduling window, we are finished
2882 -- and may exit the procedure.
2883
2884 WHILE v_alloc_qty = 0 LOOP
2885
2886 SELECT number5
2887 INTO v_finish_flag
2888 FROM mrp_form_query
2889 WHERE number1 = fs_select_rec.primary_item_id
2890 AND number4 = C_DATE_ON
2891 AND query_id = v_query_id;
2892
2893 V_ERROR_LINE := 8;
2894
2895 IF v_finish_flag = C_COMPLETE THEN
2896 EXIT;
2897 END IF;
2898
2899 IF v_current_date = p_cap_tab.LAST THEN
2900
2901 UPDATE mrp_form_query
2902 SET number5 = C_COMPLETE
2903 WHERE number4 = C_DATE_ON
2904 AND query_id = v_query_id
2905 AND number1 = fs_select_rec.primary_item_id;
2906
2907 V_ERROR_LINE := 9;
2908
2909 ELSE
2910
2911 UPDATE mrp_form_query
2912 SET number4 = NULL
2913 WHERE date1 = to_date(v_current_date,'J')
2914 AND query_id = v_query_id
2915 AND number1 = fs_select_rec.primary_item_id;
2916
2917 V_ERROR_LINE := 10;
2918
2919 v_current_date := p_cap_tab.NEXT(v_current_date);
2920
2921 UPDATE mrp_form_query
2922 SET number4 = C_DATE_ON
2923 WHERE date1 = to_date(v_current_date,'J')
2924 AND query_id = v_query_id
2925 AND number1 = fs_select_rec.primary_item_id;
2926
2927 V_ERROR_LINE := 11;
2928
2929 SELECT number2
2930 INTO v_alloc_qty
2931 FROM mrp_form_query
2932 WHERE query_id = v_query_id
2933 AND number1 = fs_select_rec.primary_item_id
2934 AND number4 = C_DATE_ON;
2935
2936 V_ERROR_LINE := 12;
2937
2938 END IF;
2939 END LOOP;
2940
2941 SELECT number5
2942 INTO v_finish_flag
2943 FROM mrp_form_query
2944 WHERE number1 = fs_select_rec.primary_item_id
2945 AND number4 = C_DATE_ON
2946 AND query_id = v_query_id;
2947
2948 V_ERROR_LINE := 13;
2949
2950 IF v_finish_flag = C_COMPLETE THEN
2951 EXIT;
2952 END IF;
2953
2954 -- If there is no entry in the completion time table for the date
2955 -- initialize it with the start time
2956
2957 IF p_time_tab.EXISTS(v_current_date) = FALSE THEN
2958 SELECT start_time
2959 INTO v_final_time
2960 FROM wip_lines
2961 WHERE line_id = p_line_id
2962 AND organization_id = p_org_id;
2963
2964 V_ERROR_LINE := 14;
2965
2966 p_time_tab(v_current_date).start_completion_time := v_final_time;
2967
2968 END IF;
2969
2970 IF v_remain_qty <= v_alloc_qty THEN
2971
2972 v_required_time := TRUNC(v_remain_qty * 3600/v_hr_line_rate);
2973 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
2974 v_required_time := p_time_tab(v_current_date).end_completion_time -
2975 p_time_tab(v_current_date).start_completion_time;
2976 end if;
2977
2978 v_completion_time := to_date(v_current_date,'J') +
2979 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
2980
2981 l_lead_time := l_fixed_lead_time +
2982 (l_variable_lead_time * (v_remain_qty-1));
2983
2984 IF l_lead_time = 0 THEN
2985 v_begin_time := v_completion_time;
2986 ELSE
2987 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
2988 p_org_id,
2989 v_completion_time,
2990 l_lead_time,
2991 v_start_time,
2992 v_end_time);
2993 END IF;
2994
2995
2996 UPDATE wip_flow_schedules
2997 SET scheduled_start_date = v_begin_time,
2998 scheduled_completion_date = v_completion_time,
2999 scheduled_flag = C_YES
3000 WHERE wip_entity_id = v_current_wip
3001 AND organization_id = p_org_id;
3002
3003 V_ERROR_LINE := 15;
3004
3005 p_time_tab(v_current_date).start_completion_time :=
3006 p_time_tab(v_current_date).start_completion_time + v_required_time;
3007
3008 -- Decrement remaining allocation quantity for the date by the
3009 -- scheduled quantity
3010
3011 UPDATE mrp_form_query
3012 SET number2 = (SELECT number2 - v_remain_qty
3013 FROM mrp_form_query
3014 WHERE date1 = to_date(v_current_date,'J')
3015 AND query_id = v_query_id
3016 AND number1 = fs_select_rec.primary_item_id)
3017 WHERE date1 = to_date(v_current_date,'J')
3018 AND query_id = v_query_id
3019 AND number1 = fs_select_rec.primary_item_id;
3020
3021 V_ERROR_LINE := 16;
3022
3023 -- The entire flow schedule has been scheduled and the
3024 -- remaining quantity = 0
3025
3026 v_remain_qty := 0;
3027
3028 ELSIF v_remain_qty > v_alloc_qty THEN
3029 v_required_time := TRUNC(v_alloc_qty * 3600/v_hr_line_rate);
3030 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
3031 v_required_time := p_time_tab(v_current_date).end_completion_time -
3032 p_time_tab(v_current_date).start_completion_time;
3033 end if;
3034
3035 v_completion_time := to_date(v_current_date,'J') +
3036 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
3037
3038 l_lead_time := l_fixed_lead_time +
3039 (l_variable_lead_time * (v_alloc_qty-1));
3040
3041 IF l_lead_time = 0 THEN
3042 v_begin_time := v_completion_time;
3043 ELSE
3044 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
3045 p_org_id,
3046 v_completion_time,
3047 l_lead_time,
3048 v_start_time,
3049 v_end_time);
3050 END IF;
3051
3052
3053 UPDATE wip_flow_schedules
3054 SET scheduled_start_date = v_begin_time,
3055 scheduled_completion_date = v_completion_time,
3056 planned_quantity = v_alloc_qty,
3057 scheduled_flag = C_YES
3058 WHERE wip_entity_id = v_current_wip
3059 AND organization_id = p_org_id;
3060
3061 V_ERROR_LINE := 17;
3062
3063 p_time_tab(v_current_date).start_completion_time :=
3064 p_time_tab(v_current_date).start_completion_time + v_required_time;
3065
3066 -- Create a new flow schedule to carry over the unscheduled quantity
3067 SELECT wip_entities_s.nextval
3068 INTO v_temp
3069 FROM dual;
3070
3071 V_ERROR_LINE := 18;
3072
3073 --Bug 6122344
3074 --v_schedule_number := NVL(substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20),'X')
3075 -- || to_char(v_temp);
3076 v_schedule_number := 'FLM-INTERNAL'|| to_char(v_temp);
3077
3078 INSERT INTO wip_flow_schedules(
3079 scheduled_flag,
3080 wip_entity_id,
3081 organization_id,
3082 last_update_date,
3083 last_updated_by,
3084 creation_date,
3085 created_by,
3086 class_code,
3087 line_id,
3088 primary_item_id,
3089 scheduled_start_date,
3090 planned_quantity,
3091 quantity_completed,
3092 quantity_scrapped,
3093 scheduled_completion_date,
3094 schedule_group_id,
3095 status,
3096 schedule_number,
3097 demand_source_header_id,
3098 demand_source_line,
3099 demand_source_delivery,
3100 demand_source_type,
3101 project_id,
3102 task_id,
3103 end_item_unit_number,
3104 request_id,
3105 attribute1,
3106 attribute2,
3107 attribute3,
3108 attribute4,
3109 attribute5,
3110 attribute6,
3111 attribute7,
3112 attribute8,
3113 attribute9,
3114 attribute10,
3115 attribute11,
3116 attribute12,
3117 attribute13,
3118 attribute14,
3119 attribute15,
3120 material_account,
3121 material_overhead_account,
3122 resource_account,
3123 outside_processing_account,
3124 material_variance_account,
3125 resource_variance_account,
3126 outside_proc_variance_account,
3127 std_cost_adjustment_account,
3128 overhead_account,
3129 overhead_variance_account,
3130 bom_revision, /* Added for bug 2185087 */
3131 routing_revision,
3132 bom_revision_date,
3133 routing_revision_date,
3134 alternate_bom_designator,
3135 alternate_routing_designator,
3136 completion_subinventory,
3137 completion_locator_id,
3138 demand_class,
3139 attribute_category,
3140 kanban_card_id)
3141
3142 SELECT C_NO,
3143 v_temp,
3144 p_org_id,
3145 SYSDATE,
3146 fs.last_updated_by,
3147 SYSDATE,
3148 fs.created_by,
3149 fs.class_code,
3150 fs.line_id,
3151 fs.primary_item_id,
3152 to_date(v_current_date,'J'),
3153 v_remain_qty - v_alloc_qty,
3154 0,
3155 0,
3156 to_date(v_current_date,'J'),
3157 fs.schedule_group_id,
3158 fs.status,
3159 v_schedule_number,
3160 fs.demand_source_header_id,
3161 fs.demand_source_line,
3162 fs.demand_source_delivery,
3163 fs.demand_source_type,
3164 fs.project_id,
3165 fs.task_id,
3166 fs.end_item_unit_number,
3167 USERENV('SESSIONID'),
3168 fs.attribute1,
3169 fs.attribute2,
3170 fs.attribute3,
3171 fs.attribute4,
3172 fs.attribute5,
3173 fs.attribute6,
3174 fs.attribute7,
3175 fs.attribute8,
3176 fs.attribute9,
3177 fs.attribute10,
3178 fs.attribute11,
3179 fs.attribute12,
3180 fs.attribute13,
3181 fs.attribute14,
3182 fs.attribute15,
3183 fs.material_account,
3184 fs.material_overhead_account,
3185 fs.resource_account,
3186 fs.outside_processing_account,
3187 fs.material_variance_account,
3188 fs.resource_variance_account,
3189 fs.outside_proc_variance_account,
3190 fs.std_cost_adjustment_account,
3191 fs.overhead_account,
3192 fs.overhead_variance_account,
3193 fs.bom_revision, /* added for bug 2185087 */
3194 fs.routing_revision,
3195 fs.bom_revision_date,
3196 fs.routing_revision_date,
3197 fs.alternate_bom_designator,
3198 fs.alternate_routing_designator,
3199 fs.completion_subinventory,
3200 fs.completion_locator_id,
3201 fs.demand_class,
3202 fs.attribute_category,
3203 fs.kanban_card_id
3204 FROM wip_flow_schedules fs
3205 WHERE fs.wip_entity_id = fs_select_rec.wip_entity
3206 AND organization_id = p_org_id;
3207
3208 V_ERROR_LINE := 19;
3209
3210 -- Reset the remaining quantity to be scheduled as the
3211 -- left over quantity from the last flow schedule.
3212
3213 v_remain_qty := v_remain_qty - v_alloc_qty;
3214
3215 -- Set the remaining allocation for current date to 0.
3216
3217 v_alloc_qty := 0;
3218
3219 -- Point to the newly created flow schedule in order to
3220 -- schedule the quantity.
3221
3222 v_current_wip := v_temp;
3223 END IF;
3224 END LOOP;
3225
3226 END LOOP;
3227
3228 IF (dbms_sql.is_open(cursor_name)) THEN
3229 dbms_sql.close_cursor(cursor_name);
3230 END IF;
3231
3232 EXCEPTION
3233 WHEN NO_DATA_FOUND THEN
3234 IF (dbms_sql.is_open(cursor_name)) THEN
3235 dbms_sql.close_cursor(cursor_name);
3236 END IF;
3237 RETURN;
3238
3239 WHEN OTHERS THEN
3240
3241 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3242 FND_MSG_PUB.Add_Exc_Msg(
3243 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
3244 END IF;
3245
3246 IF (dbms_sql.is_open(cursor_name)) THEN
3247 dbms_sql.close_cursor(cursor_name);
3248 END IF;
3249
3250 return;
3251
3252 END schedule_orders_level;
3253
3254 PROCEDURE update_buildseq(
3255 p_line_id IN NUMBER,
3256 p_org_id IN NUMBER)
3257
3258 IS
3259
3260 v_build_seq NUMBER;
3261 v_null_build_seq NUMBER;
3262 v_build_seq_tab BuildSeqTabTyp;
3263
3264 BEGIN
3265
3266 V_PROCEDURE_NAME := 'Update_Buildseq';
3267 V_ERROR_LINE := 1;
3268
3269 -- Obtain the maximum build sequence for each schedule group from scheduled
3270 -- flow schedules on the line.
3271 calculate_build_sequences(p_org_id, p_line_id, v_build_seq_tab);
3272
3273 -- Obtain the maximum sequence for the null schedule group
3274 SELECT NVL(MAX(build_sequence),0)
3275 INTO v_null_build_seq
3276 FROM wip_flow_schedules fs
3277 WHERE fs.schedule_group_id IS NULL
3278 AND fs.line_id = p_line_id
3279 AND fs.organization_id = p_org_id
3280 AND scheduled_flag = C_YES;
3281
3282 V_ERROR_LINE := 2;
3283
3284 -- dbms_output.put_line('The max build sequence for null schedule group is: '||
3285 -- to_char(v_null_build_seq));
3286
3287 FOR fs_list_rec IN fs_list(p_line_id,p_org_id) LOOP
3288 -- Get the next build sequence for the schedule group of the current
3289 -- flow schedule.
3290
3291 -- dbms_output.put_line('The flow schedule is: '||
3292 -- to_char(fs_list_rec.wip_entity_id));
3293 -- dbms_output.put_line('The schedule group id is: '||
3294 -- to_char(fs_list_rec.schedule_group_id));
3295
3296 IF fs_list_rec.schedule_group_id IS NOT NULL THEN
3297 v_build_seq :=
3298 v_build_seq_tab(fs_list_rec.schedule_group_id).buildseq + 1;
3299 v_build_seq_tab(fs_list_rec.schedule_group_id).buildseq := v_build_seq;
3300
3301 -- dbms_output.put_line('The build sequence is: '||to_char(v_build_seq));
3302 ELSE
3303 -- dbms_output.put_line('The schedule group is null.');
3304 v_build_seq := v_null_build_seq + 1;
3305 v_null_build_seq := v_build_seq;
3306 -- dbms_output.put_line('The build sequence is: '||to_char(v_build_seq));
3307
3308 END IF;
3309
3310 UPDATE wip_flow_schedules
3311 SET build_sequence = v_build_seq
3312 WHERE wip_entity_id = fs_list_rec.wip_entity_id
3313 AND organization_id = p_org_id;
3314
3315 V_ERROR_LINE := 3;
3316
3317 END LOOP;
3318
3319 EXCEPTION
3320
3321 WHEN OTHERS THEN
3322
3323 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3324 FND_MSG_PUB.Add_Exc_Msg(
3325 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
3326 END IF;
3327
3328 return;
3329
3330 END update_buildseq;
3331
3332 -- This procedure schedules the flow schedules using the mix model algorithm.
3333 -- The flow schedules are first temporarily populated in a pl/sql table
3334 -- and then written to the wip_flow_schedules table.
3335
3336 PROCEDURE schedule_mix_model(
3337 p_line_id IN NUMBER,
3338 p_org_id IN NUMBER,
3339 p_rule_id IN NUMBER,
3340 p_cap_tab IN CapTabTyp,
3341 p_demand_tab IN OUT NOCOPY DemandTabTyp,
3342 p_item_demand_tab IN ItemDemandTabTyp,
3343 p_time_tab IN OUT NOCOPY TimeTabTyp)
3344
3345
3346 IS
3347
3348 v_hr_line_rate NUMBER;
3349 v_start_time NUMBER;
3350 v_end_time NUMBER;
3351 v_line_rate NUMBER;
3352 v_build_seq_tab BuildSeqTabTyp;
3353 v_pattern LONG;
3354 v_current_num NUMBER;
3355 v_current_date NUMBER;
3356 v_current_wip NUMBER;
3357 v_position NUMBER;
3358 fs_select_rec fs_select_type;
3359 cursor_name INTEGER;
3360 v_last_wip NUMBER;
3361 v_min_build_seq NUMBER;
3362 v_current_cap NUMBER;
3363 v_schedule_number VARCHAR2(30);
3364 v_schedule_group NUMBER;
3365 v_sequence NUMBER;
3366 v_flow_schedule_tab FlowScheduleTabTyp;
3367 v_update_date DATE;
3368 v_updated_by NUMBER;
3369 v_creation_date DATE;
3370 v_created_by NUMBER;
3371 v_class_code VARCHAR2(10);
3372 v_status NUMBER;
3373 v_schedule VARCHAR2(30);
3374 v_header_id NUMBER;
3375 v_source_line VARCHAR2(30);
3376 v_source_delivery VARCHAR2(30);
3377 v_current_schedule NUMBER;
3378 v_build_seq NUMBER;
3379 v_null_build_seq NUMBER;
3380 v_planned_quantity NUMBER;
3381 v_final_time NUMBER;
3382 v_required_time NUMBER;
3383 v_completion_time DATE;
3384 v_begin_time DATE;
3385 v_source_type NUMBER;
3386 v_project_id NUMBER;
3387 v_task_id NUMBER;
3388 v_end_item_unit_number VARCHAR2(30);
3389 v_temp NUMBER;
3390 v_finish NUMBER;
3391 l_fixed_lead_time NUMBER;
3392 l_variable_lead_time NUMBER;
3393 l_lead_time NUMBER;
3394 v_attribute1 VARCHAR2(150);
3395 v_attribute2 VARCHAR2(150);
3396 v_attribute3 VARCHAR2(150);
3397 v_attribute4 VARCHAR2(150);
3398 v_attribute5 VARCHAR2(150);
3399 v_attribute6 VARCHAR2(150);
3400 v_attribute7 VARCHAR2(150);
3401 v_attribute8 VARCHAR2(150);
3402 v_attribute9 VARCHAR2(150);
3403 v_attribute10 VARCHAR2(150);
3404 v_attribute11 VARCHAR2(150);
3405 v_attribute12 VARCHAR2(150);
3406 v_attribute13 VARCHAR2(150);
3407 v_attribute14 VARCHAR2(150);
3408 v_attribute15 VARCHAR2(150);
3409 v_material_account NUMBER;
3410 v_material_overhead_account NUMBER;
3411 v_resource_account NUMBER;
3412 v_outside_processing_account NUMBER;
3413 v_material_variance_account NUMBER;
3414 v_resource_variance_account NUMBER;
3415 v_outside_proc_var_account NUMBER;
3416 v_std_cost_adjustment_account NUMBER;
3417 v_overhead_account NUMBER;
3418 v_overhead_variance_account NUMBER;
3419 v_bom_revision VARCHAR2(3); /* Added for bug 2185087 */
3420 v_routing_revision VARCHAR2(3);
3421 v_bom_revision_date DATE ;
3422 v_routing_revision_date DATE ;
3423 v_alternate_bom_designator VARCHAR2(10);
3424 v_alternate_routing_designator VARCHAR2(10);
3425 v_completion_subinventory VARCHAR2(10);
3426 v_completion_locator_id NUMBER;
3427 v_demand_class VARCHAR2(30);
3428 v_attribute_category VARCHAR2(30);
3429 v_kanban_card_id NUMBER;
3430
3431
3432 BEGIN
3433
3434 V_PROCEDURE_NAME := 'Schedule_mix_model';
3435 V_ERROR_LINE := 1;
3436
3437 -- dbms_output.put_line('Beginning of mix model procedure ...');
3438
3439 -- Obtain information on the start time and end time of the line for
3440 -- time stamping flow schedules.
3441
3442 SELECT maximum_rate, start_time, stop_time
3443 INTO v_hr_line_rate, v_start_time, v_end_time
3444 FROM wip_lines
3445 WHERE line_id = p_line_id
3446 AND organization_id = p_org_id;
3447
3448 V_ERROR_LINE := 2;
3449
3450 -- Fix bug 939061, add 24 hrs to stop_time if stop_time <= start_time
3451 if (v_end_time <= v_start_time) then
3452 v_end_time := v_end_time+24*3600;
3453 end if;
3454 v_line_rate := TRUNC(v_hr_line_rate * (v_end_time - v_start_time)/3600);
3455
3456 -- New and existing flow schedules will be time stamped and
3457 -- scheduled before existing wip jobs and repetitive schedules.
3458
3459 -- Order the existing flow schedules by schedule group id and build sequence
3460 -- and assign the appropriate time stamps to these flow schedules first.
3461
3462 -- dbms_output.put_line('Time stamping existing flow schedules');
3463
3464 -- MRP_UTIL.MRP_TIMING('Before taking care of existing flow schedules');
3465 -- time_existing_fs(p_org_id, p_line_id, p_cap_tab, v_time_tab);
3466
3467 -- MRP_UTIL.MRP_TIMING('After taking care of existing flow schedules');
3468 -- dbms_output.put_line('Establishing the mix model pattern');
3469
3470 -- MRP_UTIL.MRP_TIMING('Before establishing mix model');
3471 v_pattern := mix_model(p_item_demand_tab);
3472
3473 -- MRP_UTIL.MRP_TIMING('After establishing mix model');
3474 -- dbms_output.put_line('After establishing mix model');
3475
3476 -- dbms_output.put_line('The pattern is: '||v_pattern);
3477
3478 -- Trim off the first asterisk
3479 v_pattern := LTRIM(v_pattern,'*');
3480 -- dbms_output.put_line('The pattern is now: '||v_pattern);
3481
3482 V_ERROR_LINE := 3;
3483
3484 -- MRP_UTIL.MRP_TIMING('Before establishing main cursor');
3485 cursor_name := Create_Cursor(p_rule_id, p_org_id, p_line_id, C_ASC,
3486 C_NORM, C_NORM);
3487
3488 -- MRP_UTIL.MRP_TIMING('After creating main cursor');
3489
3490 V_ERROR_LINE := 4;
3491
3492 -- In this loop, populate build sequences of all flow schedules to
3493 -- be scheduled.
3494
3495 LOOP
3496 IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
3497 dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
3498 dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
3499 dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
3500 dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
3501 dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
3502 END IF;
3503
3504
3505
3506 IF v_last_wip = fs_select_rec.wip_entity THEN
3507 EXIT;
3508 END IF;
3509
3510 v_last_wip := fs_select_rec.wip_entity;
3511
3512 -- dbms_output.put_line('Inside the cursor loop');
3513
3514 -- Order the flow schedules by the item id by populating the order in
3515 -- the build sequence column. The latest sequence for the item is stored
3516 -- in the v_item_demand_tab table.
3517
3518 v_sequence :=
3519 p_demand_tab(fs_select_rec.primary_item_id).sequence + 1;
3520 p_demand_tab(fs_select_rec.primary_item_id).sequence := v_sequence;
3521
3522 -- dbms_output.put_line('The item is: '||to_char(fs_select_rec.primary_item_id));
3523 -- dbms_output.put_line('The sequence is: '||to_char(v_sequence));
3524
3525 -- MRP_UTIL.MRP_TIMING('Inside cursor loop, BEGIN update flow schedule');
3526
3527 UPDATE wip_flow_schedules
3528 SET build_sequence = v_sequence
3529 WHERE wip_entity_id = fs_select_rec.wip_entity
3530 AND organization_id = p_org_id;
3531
3532 -- MRP_UTIL.MRP_TIMING('Inside cursor loop, FINISH update flow schedule');
3533
3534 V_ERROR_LINE := 5;
3535
3536 END LOOP;
3537
3538 -- Reset sequence of all item to 1 to be used for keeping track of latest
3539 -- sequence for an item to be scheduled.
3540
3541 FOR item_list_rec IN item_list(p_org_id) LOOP
3542 p_demand_tab(item_list_rec.primary_item_id).sequence := 1;
3543 END LOOP;
3544
3545 IF (dbms_sql.is_open(cursor_name)) THEN
3546 dbms_sql.close_cursor(cursor_name);
3547 END IF;
3548
3549 -- Set current date to be the first valid date in the scheduling window.
3550 -- This is the first index in the capacity table.
3551
3552 v_current_date := p_cap_tab.FIRST;
3553 v_current_cap := p_cap_tab(v_current_date).capacity;
3554
3555 -- dbms_output.put_line('First valid date in scheduling window is:' ||
3556 -- to_date(v_current_date,'J'));
3557 -- dbms_output.put_line('Available capacity on this date is:' ||
3558 -- to_char(v_current_cap));
3559
3560 -- Obtain the maximum build sequence for each schedule group from scheduled
3561 -- flow schedules on the line.
3562 calculate_build_sequences(p_org_id, p_line_id, v_build_seq_tab);
3563
3564 -- Obtain the maximum sequence for the null schedule group
3565 SELECT NVL(MAX(build_sequence),0)
3566 INTO v_null_build_seq
3567 FROM wip_flow_schedules fs
3568 WHERE fs.schedule_group_id IS NULL
3569 AND fs.line_id = p_line_id
3570 AND fs.organization_id = p_org_id
3571 AND scheduled_flag = C_YES;
3572
3573 -- MRP_UTIL.MRP_TIMING('BEGIN OF GOING THROUGH MIX MODEL PATTERN');
3574 WHILE instr(v_pattern,'*') <> 0 LOOP
3575 -- MRP_UTIL.MRP_TIMING('Find out pattern');
3576 IF v_finish = 1 THEN
3577 -- MRP_UTIL.MRP_TIMING('Finished');
3578 EXIT;
3579 END IF;
3580
3581 WHILE v_current_cap = 0 LOOP
3582
3583 IF v_current_date = p_cap_tab.LAST THEN
3584 -- MRP_UTIL.MRP_TIMING('This is last day of scheduling window.');
3585 V_FINISH := 1;
3586 EXIT;
3587 ELSE
3588 v_current_date := p_cap_tab.NEXT(v_current_date);
3589 v_current_cap := p_cap_tab(v_current_date).capacity;
3590 END IF;
3591 END LOOP;
3592
3593 IF v_finish = 1 THEN
3594 EXIT;
3595 END IF;
3596
3597 -- Find out the item that is to be scheduled next.
3598
3599 v_position := instr(v_pattern,'*');
3600 v_current_num := TO_NUMBER(RTRIM(SUBSTR(v_pattern,1,v_position),'*'));
3601 v_pattern := SUBSTR(v_pattern,v_position+1);
3602 v_sequence := p_demand_tab(p_item_demand_tab(v_current_num).item).sequence;
3603
3604 -- MRP_UTIL.MRP_TIMING('Inside pattern, BEGIN select statement');
3605
3606 SELECT wip_entity_id,planned_quantity,schedule_group_id
3607 INTO v_current_wip,v_planned_quantity,v_schedule_group
3608 FROM wip_flow_schedules
3609 WHERE primary_item_id = p_item_demand_tab(v_current_num).item
3610 AND organization_id = p_org_id
3611 AND line_id = p_line_id
3612 AND scheduled_flag = C_NO
3613 AND build_sequence = v_sequence
3614 AND request_id = USERENV('SESSIONID')
3615 AND rownum = 1
3616 AND wip_entity_id >= G_WIP_ENTITY_ID;
3617
3618 l_fixed_lead_time := p_item_demand_tab(v_current_num).fixed_lead_time;
3619 l_variable_lead_time := p_item_demand_tab(v_current_num).var_lead_time;
3620
3621 -- MRP_UTIL.MRP_TIMING('Inside pattern, FINISH select statement');
3622
3623 V_ERROR_LINE := 6;
3624
3625 -- Get the next build sequence for the schedule group of the current
3626 -- flow schedule.
3627
3628 IF v_schedule_group IS NOT NULL THEN
3629 v_build_seq :=
3630 v_build_seq_tab(v_schedule_group).buildseq + 1;
3631 v_build_seq_tab(v_schedule_group).buildseq := v_build_seq;
3632
3633 ELSE
3634 v_build_seq := v_null_build_seq + 1;
3635 v_null_build_seq := v_build_seq;
3636 END IF;
3637
3638 -- If the flow schedule has 1 or less than 1 unit to be scheduled
3639 IF v_planned_quantity <= 1 THEN
3640
3641 -- If the capacity is less than what needs to be scheduled
3642
3643 WHILE v_current_cap < v_planned_quantity LOOP
3644 IF v_current_date = p_cap_tab.LAST THEN
3645 V_FINISH := 1;
3646 EXIT;
3647 ELSE
3648 v_current_date := p_cap_tab.NEXT(v_current_date);
3649 v_current_cap := p_cap_tab(v_current_date).capacity;
3650 END IF;
3651 END LOOP;
3652
3653 IF v_finish = 1 THEN
3654 EXIT;
3655 END IF;
3656
3657 -- If there is no entry in the completion time table for the date
3658 -- initialize it with the start time
3659
3660 IF p_time_tab.EXISTS(v_current_date) = FALSE THEN
3661 SELECT start_time
3662 INTO v_final_time
3663 FROM wip_lines
3664 WHERE line_id = p_line_id
3665 AND organization_id = p_org_id;
3666
3667 V_ERROR_LINE := 8;
3668
3669 p_time_tab(v_current_date).start_completion_time := v_final_time;
3670 END IF;
3671
3672 v_required_time := v_planned_quantity*TRUNC(3600/v_hr_line_rate);
3673 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
3674 v_required_time := p_time_tab(v_current_date).end_completion_time -
3675 p_time_tab(v_current_date).start_completion_time;
3676 end if;
3677
3678
3679 v_completion_time := to_date(v_current_date,'J') +
3680 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
3681
3682 l_lead_time := l_fixed_lead_time +
3683 (l_variable_lead_time * (v_planned_quantity-1));
3684
3685 IF l_lead_time = 0 THEN
3686 v_begin_time := v_completion_time;
3687 ELSE
3688 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
3689 p_org_id,
3690 v_completion_time,
3691 l_lead_time,
3692 v_start_time,
3693 v_end_time);
3694 END IF;
3695
3696
3697 p_time_tab(v_current_date).start_completion_time :=
3698 p_time_tab(v_current_date).start_completion_time + v_required_time;
3699
3700 -- MRP_UTIL.MRP_TIMING('BEGIN select information');
3701
3702 SELECT last_update_date, last_updated_by, creation_date, created_by,
3703 class_code, status, schedule_number, demand_source_header_id,
3704 demand_source_line, demand_source_delivery, demand_source_type,
3705 project_id, task_id, end_item_unit_number, attribute1, attribute2, attribute3, attribute4,
3706 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
3707 attribute11, attribute12, attribute13, attribute14, attribute15,
3708 material_account, material_overhead_account, resource_account,
3709 outside_processing_account, material_variance_account, resource_variance_account,
3710 outside_proc_variance_account, std_cost_adjustment_account, overhead_account,
3711 overhead_variance_account,bom_revision,routing_revision,/*Added for bug2185087*/
3712 bom_revision_date, routing_revision_date,
3713 alternate_bom_designator, alternate_routing_designator,
3714 completion_subinventory,completion_locator_id,
3715 demand_class, attribute_category, kanban_card_id
3716 INTO v_update_date, v_updated_by, v_creation_date, v_created_by,
3717 v_class_code, v_status, v_schedule, v_header_id, v_source_line,
3718 v_source_delivery, v_source_type, v_project_id, v_task_id, v_end_item_unit_number, v_attribute1,
3719 v_attribute2, v_attribute3, v_attribute4,
3720 v_attribute5, v_attribute6, v_attribute7, v_attribute8, v_attribute9, v_attribute10,
3721 v_attribute11, v_attribute12, v_attribute13, v_attribute14, v_attribute15,
3722 v_material_account, v_material_overhead_account, v_resource_account,
3723 v_outside_processing_account, v_material_variance_account, v_resource_variance_account,
3724 v_outside_proc_var_account, v_std_cost_adjustment_account, v_overhead_account,
3725 v_overhead_variance_account,v_bom_revision,v_routing_revision, /* 2185087*/
3726 v_bom_revision_date, v_routing_revision_date,
3727 v_alternate_bom_designator,v_alternate_routing_designator,
3728 v_completion_subinventory, v_completion_locator_id,
3729 v_demand_class, v_attribute_category,v_kanban_card_id
3730 FROM wip_flow_schedules
3731 WHERE wip_entity_id = v_current_wip
3732 AND organization_id = p_org_id
3733 AND request_id = userenv('sessionid')
3734 AND wip_entity_id >= G_WIP_ENTITY_ID;
3735
3736 -- MRP_UTIL.MRP_TIMING('BEGIN Insert');
3737
3738
3739 -- Insert into pl/sql table
3740 v_flow_schedule_tab(v_current_wip).scheduled_flag := C_YES;
3741 v_flow_schedule_tab(v_current_wip).organization_id := p_org_id;
3742 v_flow_schedule_tab(v_current_wip).last_update_date := v_update_date;
3743 v_flow_schedule_tab(v_current_wip).last_updated_by := v_updated_by;
3744 v_flow_schedule_tab(v_current_wip).creation_date := v_creation_date;
3745 v_flow_schedule_tab(v_current_wip).created_by := v_created_by;
3746 v_flow_schedule_tab(v_current_wip).class_code := v_class_code;
3747 v_flow_schedule_tab(v_current_wip).line_id := p_line_id;
3748 v_flow_schedule_tab(v_current_wip).primary_item_id
3749 := p_item_demand_tab(v_current_num).item;
3750 v_flow_schedule_tab(v_current_wip).scheduled_start_date := v_begin_time;
3751 v_flow_schedule_tab(v_current_wip).planned_quantity := v_planned_quantity;
3752 v_flow_schedule_tab(v_current_wip).quantity_completed := 0;
3753 v_flow_schedule_tab(v_current_wip).scheduled_completion_date := v_completion_time;
3754 v_flow_schedule_tab(v_current_wip).schedule_group_id := v_schedule_group;
3755 v_flow_schedule_tab(v_current_wip).build_sequence := v_build_seq;
3756 v_flow_schedule_tab(v_current_wip).status := v_status;
3757 v_flow_schedule_tab(v_current_wip).schedule_number := v_schedule;
3758 v_flow_schedule_tab(v_current_wip).demand_source_header_id := v_header_id;
3759 v_flow_schedule_tab(v_current_wip).demand_source_line := v_source_line;
3760 v_flow_schedule_tab(v_current_wip).demand_source_delivery := v_source_delivery;
3761 v_flow_schedule_tab(v_current_wip).demand_source_type := v_source_type;
3762 v_flow_schedule_tab(v_current_wip).project_id := v_project_id;
3763 v_flow_schedule_tab(v_current_wip).task_id := v_task_id;
3764 v_flow_schedule_tab(v_current_wip).end_item_unit_number := v_end_item_unit_number;
3765 v_flow_schedule_tab(v_current_wip).request_id := userenv('sessionid');
3766 v_flow_schedule_tab(v_current_wip).attribute1 := v_attribute1;
3767 v_flow_schedule_tab(v_current_wip).attribute2 := v_attribute2;
3768 v_flow_schedule_tab(v_current_wip).attribute3 := v_attribute3;
3769 v_flow_schedule_tab(v_current_wip).attribute4 := v_attribute4;
3770 v_flow_schedule_tab(v_current_wip).attribute5 := v_attribute5;
3771 v_flow_schedule_tab(v_current_wip).attribute6 := v_attribute6;
3772 v_flow_schedule_tab(v_current_wip).attribute7 := v_attribute7;
3773 v_flow_schedule_tab(v_current_wip).attribute8 := v_attribute8;
3774 v_flow_schedule_tab(v_current_wip).attribute9 := v_attribute9;
3775 v_flow_schedule_tab(v_current_wip).attribute10 := v_attribute10;
3776 v_flow_schedule_tab(v_current_wip).attribute11 := v_attribute11;
3777 v_flow_schedule_tab(v_current_wip).attribute12 := v_attribute12;
3778 v_flow_schedule_tab(v_current_wip).attribute13 := v_attribute13;
3779 v_flow_schedule_tab(v_current_wip).attribute14 := v_attribute14;
3780 v_flow_schedule_tab(v_current_wip).attribute15 := v_attribute15;
3781 v_flow_schedule_tab(v_current_wip).material_account := v_material_account;
3782 v_flow_schedule_tab(v_current_wip).material_overhead_account := v_material_overhead_account;
3783 v_flow_schedule_tab(v_current_wip).resource_account := v_resource_account;
3784 v_flow_schedule_tab(v_current_wip).outside_processing_account := v_outside_processing_account;
3785 v_flow_schedule_tab(v_current_wip).material_variance_account := v_material_variance_account;
3786 v_flow_schedule_tab(v_current_wip).resource_variance_account := v_resource_variance_account;
3787 v_flow_schedule_tab(v_current_wip).outside_proc_var_account := v_outside_proc_var_account;
3788 v_flow_schedule_tab(v_current_wip).std_cost_adjustment_account := v_std_cost_adjustment_account;
3789 v_flow_schedule_tab(v_current_wip).overhead_account := v_overhead_account;
3790 v_flow_schedule_tab(v_current_wip).overhead_variance_account := v_overhead_variance_account;
3791 /* Added for bug 2185087 */
3792 v_flow_schedule_tab(v_current_wip).bom_revision := v_bom_revision;
3793 v_flow_schedule_tab(v_current_wip).routing_revision := v_routing_revision;
3794 v_flow_schedule_tab(v_current_wip).bom_revision_date := v_bom_revision_date;
3795 v_flow_schedule_tab(v_current_wip).routing_revision_date := v_routing_revision_date;
3796 v_flow_schedule_tab(v_current_wip).alternate_bom_designator := v_alternate_bom_designator;
3797 v_flow_schedule_tab(v_current_wip).alternate_routing_designator := v_alternate_routing_designator;
3798 v_flow_schedule_tab(v_current_wip).completion_subinventory := v_completion_subinventory;
3799 v_flow_schedule_tab(v_current_wip).completion_locator_id := v_completion_locator_id;
3800 v_flow_schedule_tab(v_current_wip).demand_class := v_demand_class;
3801 v_flow_schedule_tab(v_current_wip).attribute_category := v_attribute_category;
3802 v_flow_schedule_tab(v_current_wip).kanban_card_id := v_kanban_card_id;
3803
3804 -- MRP_UTIL.MRP_TIMING('END insert');
3805
3806 DELETE
3807 FROM wip_flow_schedules
3808 WHERE wip_entity_id = v_current_wip
3809 AND organization_id = p_org_id;
3810
3811 V_ERROR_LINE := 9;
3812
3813 v_current_cap := v_current_cap - v_planned_quantity;
3814 p_demand_tab(p_item_demand_tab(v_current_num).item).sequence :=
3815 p_demand_tab(p_item_demand_tab(v_current_num).item).sequence + 1;
3816
3817 ELSE
3818
3819 -- Create a new flow schedule since the flow schedule has quantity greater than 1.
3820
3821 -- If the capacity of the current date is less than 1, then check
3822 -- capacity of subsequent dates
3823 WHILE v_current_cap < 1 LOOP
3824 IF v_current_date = p_cap_tab.LAST THEN
3825 V_FINISH := 1;
3826 EXIT;
3827 ELSE
3828 v_current_date := p_cap_tab.NEXT(v_current_date);
3829 v_current_cap := p_cap_tab(v_current_date).capacity;
3830 END IF;
3831 END LOOP;
3832
3833 IF v_finish = 1 THEN
3834 EXIT;
3835 END IF;
3836
3837 -- If there is no entry in the completion time table for the date
3838 -- initialize it with the start time
3839
3840 IF p_time_tab.EXISTS(v_current_date) = FALSE THEN
3841 SELECT start_time
3842 INTO v_final_time
3843 FROM wip_lines
3844 WHERE line_id = p_line_id
3845 AND organization_id = p_org_id;
3846
3847 V_ERROR_LINE := 10;
3848
3849 p_time_tab(v_current_date).start_completion_time := v_final_time;
3850 END IF;
3851
3852 v_required_time := TRUNC(3600/v_hr_line_rate);
3853 if (p_time_tab(v_current_date).start_completion_time+v_required_time > p_time_tab(v_current_date).end_completion_time) then
3854 v_required_time := p_time_tab(v_current_date).end_completion_time -
3855 p_time_tab(v_current_date).start_completion_time;
3856 end if;
3857
3858 v_completion_time := to_date(v_current_date,'J') +
3859 ((p_time_tab(v_current_date).start_completion_time+v_required_time)/86400);
3860
3861 -- planned quantity will be '1'
3862 l_lead_time := l_fixed_lead_time ;
3863
3864 IF l_lead_time = 0 THEN
3865 v_begin_time := v_completion_time;
3866 ELSE
3867 v_begin_time := mrp_line_schedule_algorithm.calculate_begin_time(
3868 p_org_id,
3869 v_completion_time,
3870 l_lead_time,
3871 v_start_time,
3872 v_end_time);
3873 END IF;
3874
3875
3876 p_time_tab(v_current_date).start_completion_time :=
3877 p_time_tab(v_current_date).start_completion_time + v_required_time;
3878
3879 SELECT wip_entities_s.nextval
3880 INTO v_temp
3881 FROM dual;
3882
3883 V_ERROR_LINE := 11;
3884
3885 --Bug 6122344
3886 --v_schedule_number := NVL(substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20),'X')
3887 -- || to_char(v_temp);
3888 v_schedule_number := 'FLM-INTERNAL'|| to_char(v_temp);
3889
3890
3891 -- dbms_output.put_line('The schedule number is: '||v_schedule_number);
3892
3893
3894 -- MRP_UTIL.MRP_TIMING('BEGIN select information');
3895 SELECT last_update_date, last_updated_by, creation_date, created_by,
3896 class_code, status, schedule_number, demand_source_header_id,
3897 demand_source_line, demand_source_delivery, demand_source_type,
3898 project_id, task_id, end_item_unit_number, attribute1, attribute2, attribute3, attribute4,
3899 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
3900 attribute11, attribute12, attribute13, attribute14, attribute15,
3901 material_account, material_overhead_account, resource_account,
3902 outside_processing_account, material_variance_account, resource_variance_account,
3903 outside_proc_variance_account, std_cost_adjustment_account, overhead_account,
3904 overhead_variance_account,bom_revision,routing_revision , /*2185087 */
3905 bom_revision_date, routing_revision_date,
3906 alternate_bom_designator, alternate_routing_designator,
3907 completion_subinventory, completion_locator_id,
3908 demand_class,attribute_category, kanban_card_id
3909 INTO v_update_date, v_updated_by, v_creation_date, v_created_by,
3910 v_class_code, v_status, v_schedule, v_header_id, v_source_line,
3911 v_source_delivery, v_source_type, v_project_id, v_task_id, v_end_item_unit_number,
3912 v_attribute1, v_attribute2, v_attribute3, v_attribute4,
3913 v_attribute5, v_attribute6, v_attribute7, v_attribute8, v_attribute9, v_attribute10,
3914 v_attribute11, v_attribute12, v_attribute13, v_attribute14, v_attribute15,
3915 v_material_account, v_material_overhead_account, v_resource_account,
3916 v_outside_processing_account, v_material_variance_account, v_resource_variance_account,
3917 v_outside_proc_var_account, v_std_cost_adjustment_account, v_overhead_account,
3918 v_overhead_variance_account,v_bom_revision,v_routing_revision, /*2185087*/
3919 v_bom_revision_date, v_routing_revision_date,
3920 v_alternate_bom_designator,v_alternate_routing_designator,
3921 v_completion_subinventory, v_completion_locator_id,
3922 v_demand_class, v_attribute_category, v_kanban_card_id
3923 FROM wip_flow_schedules
3924 WHERE wip_entity_id = v_current_wip
3925 AND organization_id = p_org_id;
3926
3927 -- MRP_UTIL.MRP_TIMING('BEGIN insert');
3928
3929 -- Insert into pl/sql table
3930
3931 v_flow_schedule_tab(v_temp).scheduled_flag := C_YES;
3932 v_flow_schedule_tab(v_temp).organization_id := p_org_id;
3933 v_flow_schedule_tab(v_temp).last_update_date := v_update_date;
3934 v_flow_schedule_tab(v_temp).last_updated_by := v_updated_by;
3935 v_flow_schedule_tab(v_temp).creation_date := v_creation_date;
3936 v_flow_schedule_tab(v_temp).created_by := v_created_by;
3937 v_flow_schedule_tab(v_temp).class_code := v_class_code;
3938 v_flow_schedule_tab(v_temp).line_id := p_line_id;
3939 v_flow_schedule_tab(v_temp).primary_item_id := p_item_demand_tab(v_current_num).item;
3940 v_flow_schedule_tab(v_temp).scheduled_start_date := v_begin_time;
3941 v_flow_schedule_tab(v_temp).planned_quantity := 1;
3942 v_flow_schedule_tab(v_temp).quantity_completed := 0;
3943 v_flow_schedule_tab(v_temp).scheduled_completion_date := v_completion_time;
3944 v_flow_schedule_tab(v_temp).schedule_group_id := v_schedule_group;
3945 v_flow_schedule_tab(v_temp).build_sequence := v_build_seq;
3946 v_flow_schedule_tab(v_temp).status := v_status;
3947 v_flow_schedule_tab(v_temp).schedule_number := v_schedule_number;
3948 v_flow_schedule_tab(v_temp).demand_source_header_id := v_header_id;
3949 v_flow_schedule_tab(v_temp).demand_source_line := v_source_line;
3950 v_flow_schedule_tab(v_temp).demand_source_delivery := v_source_delivery;
3951 v_flow_schedule_tab(v_temp).demand_source_type := v_source_type;
3952 v_flow_schedule_tab(v_temp).project_id := v_project_id;
3953 v_flow_schedule_tab(v_temp).task_id := v_task_id;
3954 v_flow_schedule_tab(v_temp).end_item_unit_number := v_end_item_unit_number;
3955 v_flow_schedule_tab(v_temp).request_id := userenv('sessionid');
3956 v_flow_schedule_tab(v_temp).attribute1 := v_attribute1;
3957 v_flow_schedule_tab(v_temp).attribute2 := v_attribute2;
3958 v_flow_schedule_tab(v_temp).attribute3 := v_attribute3;
3959 v_flow_schedule_tab(v_temp).attribute4 := v_attribute4;
3960 v_flow_schedule_tab(v_temp).attribute5 := v_attribute5;
3961 v_flow_schedule_tab(v_temp).attribute6 := v_attribute6;
3962 v_flow_schedule_tab(v_temp).attribute7 := v_attribute7;
3963 v_flow_schedule_tab(v_temp).attribute8 := v_attribute8;
3964 v_flow_schedule_tab(v_temp).attribute9 := v_attribute9;
3965 v_flow_schedule_tab(v_temp).attribute10 := v_attribute10;
3966 v_flow_schedule_tab(v_temp).attribute11 := v_attribute11;
3967 v_flow_schedule_tab(v_temp).attribute12 := v_attribute12;
3968 v_flow_schedule_tab(v_temp).attribute13 := v_attribute13;
3969 v_flow_schedule_tab(v_temp).attribute14 := v_attribute14;
3970 v_flow_schedule_tab(v_temp).attribute15 := v_attribute15;
3971 v_flow_schedule_tab(v_temp).material_account := v_material_account;
3972 v_flow_schedule_tab(v_temp).material_overhead_account := v_material_overhead_account;
3973 v_flow_schedule_tab(v_temp).resource_account := v_resource_account;
3974 v_flow_schedule_tab(v_temp).outside_processing_account := v_outside_processing_account;
3975 v_flow_schedule_tab(v_temp).material_variance_account := v_material_variance_account;
3976 v_flow_schedule_tab(v_temp).resource_variance_account := v_resource_variance_account;
3977 v_flow_schedule_tab(v_temp).outside_proc_var_account := v_outside_proc_var_account;
3978 v_flow_schedule_tab(v_temp).std_cost_adjustment_account := v_std_cost_adjustment_account;
3979 v_flow_schedule_tab(v_temp).overhead_account := v_overhead_account;
3980 v_flow_schedule_tab(v_temp).overhead_variance_account := v_overhead_variance_account;
3981 v_flow_schedule_tab(v_temp).bom_revision := v_bom_revision; /*2185087 */
3982 v_flow_schedule_tab(v_temp).routing_revision := v_routing_revision;
3983 v_flow_schedule_tab(v_temp).bom_revision_date := v_bom_revision_date;
3984 v_flow_schedule_tab(v_temp).routing_revision_date := v_routing_revision_date;
3985 v_flow_schedule_tab(v_temp).alternate_bom_designator := v_alternate_bom_designator;
3986 v_flow_schedule_tab(v_temp).alternate_routing_designator := v_alternate_routing_designator;
3987 v_flow_schedule_tab(v_temp).completion_subinventory := v_completion_subinventory;
3988 v_flow_schedule_tab(v_temp).completion_locator_id := v_completion_locator_id;
3989 v_flow_schedule_tab(v_temp).demand_class := v_demand_class;
3990 v_flow_schedule_tab(v_temp).attribute_category := v_attribute_category;
3991 v_flow_schedule_tab(v_temp).kanban_card_id := v_kanban_card_id;
3992
3993
3994
3995 -- MRP_UTIL.MRP_TIMING('end insert');
3996
3997 UPDATE wip_flow_schedules
3998 SET planned_quantity = planned_quantity - 1
3999 WHERE wip_entity_id = v_current_wip
4000 AND organization_id = p_org_id;
4001
4002 -- MRP_UTIL.MRP_TIMING('end update');
4003
4004 V_ERROR_LINE := 12;
4005
4006 -- Decrement capacity on the line for the current date by the scheduled quantity.
4007
4008 v_current_cap := v_current_cap - 1;
4009
4010 END IF;
4011
4012
4013 -- dbms_output.put_line('Capacity on '||to_date(v_current_date,'J')||' = '||
4014 -- to_char(v_current_cap));
4015
4016 END LOOP;
4017
4018 -- Insert flow schedules from pl/sql table to wip flow schedules table.
4019
4020 v_current_schedule := v_flow_schedule_tab.FIRST;
4021
4022 -- MRP_UTIL.MRP_TIMING('BEGIN INSERTING INTO wip flow schedules table');
4023
4024 WHILE v_current_schedule IS NOT NULL LOOP
4025
4026 -- MRP_UTIL.MRP_TIMING('Begin insert');
4027
4028 INSERT INTO wip_flow_schedules(
4029 scheduled_flag,
4030 wip_entity_id,
4031 organization_id,
4032 last_update_date,
4033 last_updated_by,
4034 creation_date,
4035 created_by,
4036 class_code,
4037 line_id,
4038 primary_item_id,
4039 scheduled_start_date,
4040 planned_quantity,
4041 quantity_completed,
4042 quantity_scrapped,
4043 scheduled_completion_date,
4044 schedule_group_id,
4045 build_sequence,
4046 status,
4047 schedule_number,
4048 demand_source_header_id,
4049 demand_source_line,
4050 demand_source_delivery,
4051 demand_source_type,
4052 project_id,
4053 task_id,
4054 end_item_unit_number,
4055 request_id,
4056 attribute1,
4057 attribute2,
4058 attribute3,
4059 attribute4,
4060 attribute5,
4061 attribute6,
4062 attribute7,
4063 attribute8,
4064 attribute9,
4065 attribute10,
4066 attribute11,
4067 attribute12,
4068 attribute13,
4069 attribute14,
4070 attribute15,
4071 material_account,
4072 material_overhead_account,
4073 resource_account,
4074 outside_processing_account,
4075 material_variance_account,
4076 resource_variance_account,
4077 outside_proc_variance_account,
4078 std_cost_adjustment_account,
4079 overhead_account,
4080 overhead_variance_account,
4081 bom_revision, /* 2185087 */
4082 routing_revision,
4083 bom_revision_date,
4084 routing_revision_date,
4085 alternate_bom_designator,
4086 alternate_routing_designator,
4087 completion_subinventory,
4088 completion_locator_id,
4089 demand_class,
4090 attribute_category,
4091 kanban_card_id)
4092 VALUES (
4093 v_flow_schedule_tab(v_current_schedule).scheduled_flag,
4094 v_current_schedule,
4095 v_flow_schedule_tab(v_current_schedule).organization_id,
4096 v_flow_schedule_tab(v_current_schedule).last_update_date,
4097 v_flow_schedule_tab(v_current_schedule).last_updated_by,
4098 v_flow_schedule_tab(v_current_schedule).creation_date,
4099 v_flow_schedule_tab(v_current_schedule).created_by,
4100 v_flow_schedule_tab(v_current_schedule).class_code,
4101 v_flow_schedule_tab(v_current_schedule).line_id,
4102 v_flow_schedule_tab(v_current_schedule).primary_item_id,
4103 v_flow_schedule_tab(v_current_schedule).scheduled_start_date,
4104 v_flow_schedule_tab(v_current_schedule).planned_quantity,
4105 v_flow_schedule_tab(v_current_schedule).quantity_completed,
4106 0,
4107 v_flow_schedule_tab(v_current_schedule).scheduled_completion_date,
4108 v_flow_schedule_tab(v_current_schedule).schedule_group_id,
4109 v_flow_schedule_tab(v_current_schedule).build_sequence,
4110 v_flow_schedule_tab(v_current_schedule).status,
4111 v_flow_schedule_tab(v_current_schedule).schedule_number,
4112 v_flow_schedule_tab(v_current_schedule).demand_source_header_id,
4113 v_flow_schedule_tab(v_current_schedule).demand_source_line,
4114 v_flow_schedule_tab(v_current_schedule).demand_source_delivery,
4115 v_flow_schedule_tab(v_current_schedule).demand_source_type,
4116 v_flow_schedule_tab(v_current_schedule).project_id, v_flow_schedule_tab(v_current_schedule).task_id,
4117 v_flow_schedule_tab(v_current_schedule).end_item_unit_number,
4118 userenv('sessionid'),
4119 v_flow_schedule_tab(v_current_schedule).attribute1,
4120 v_flow_schedule_tab(v_current_schedule).attribute2,
4121 v_flow_schedule_tab(v_current_schedule).attribute3,
4122 v_flow_schedule_tab(v_current_schedule).attribute4,
4123 v_flow_schedule_tab(v_current_schedule).attribute5,
4124 v_flow_schedule_tab(v_current_schedule).attribute6,
4125 v_flow_schedule_tab(v_current_schedule).attribute7,
4126 v_flow_schedule_tab(v_current_schedule).attribute8,
4127 v_flow_schedule_tab(v_current_schedule).attribute9,
4128 v_flow_schedule_tab(v_current_schedule).attribute10,
4129 v_flow_schedule_tab(v_current_schedule).attribute11,
4130 v_flow_schedule_tab(v_current_schedule).attribute12,
4131 v_flow_schedule_tab(v_current_schedule).attribute13,
4132 v_flow_schedule_tab(v_current_schedule).attribute14,
4133 v_flow_schedule_tab(v_current_schedule).attribute15,
4134 v_flow_schedule_tab(v_current_schedule).material_account,
4135 v_flow_schedule_tab(v_current_schedule).material_overhead_account,
4136 v_flow_schedule_tab(v_current_schedule).resource_account,
4137 v_flow_schedule_tab(v_current_schedule).outside_processing_account,
4138 v_flow_schedule_tab(v_current_schedule).material_variance_account,
4139 v_flow_schedule_tab(v_current_schedule).resource_variance_account,
4140 v_flow_schedule_tab(v_current_schedule).outside_proc_var_account,
4141 v_flow_schedule_tab(v_current_schedule).std_cost_adjustment_account,
4142 v_flow_schedule_tab(v_current_schedule).overhead_account,
4143 v_flow_schedule_tab(v_current_schedule).overhead_variance_account,
4144 v_flow_schedule_tab(v_current_schedule).bom_revision, /*2185087*/
4145 v_flow_schedule_tab(v_current_schedule).routing_revision,
4146 v_flow_schedule_tab(v_current_schedule).bom_revision_date,
4147 v_flow_schedule_tab(v_current_schedule).routing_revision_date,
4148 v_flow_schedule_tab(v_current_schedule).alternate_bom_designator,
4149 v_flow_schedule_tab(v_current_schedule).alternate_routing_designator,
4150 v_flow_schedule_tab(v_current_schedule).completion_subinventory,
4151 v_flow_schedule_tab(v_current_schedule).completion_locator_id,
4152 v_flow_schedule_tab(v_current_schedule).demand_class,
4153 v_flow_schedule_tab(v_current_schedule).attribute_category,
4154 v_flow_schedule_tab(v_current_schedule).kanban_card_id
4155
4156 );
4157 -- MRP_UTIL.MRP_TIMING('End of insert into wip flow schedules.');
4158
4159 v_current_schedule := v_flow_schedule_tab.NEXT(v_current_schedule);
4160
4161 END LOOP;
4162
4163 EXCEPTION
4164 WHEN NO_DATA_FOUND THEN
4165 IF (dbms_sql.is_open(cursor_name)) THEN
4166 dbms_sql.close_cursor(cursor_name);
4167 END IF;
4168
4169 RETURN;
4170
4171 WHEN OTHERS THEN
4172
4173 IF (dbms_sql.is_open(cursor_name)) THEN
4174 dbms_sql.close_cursor(cursor_name);
4175 END IF;
4176
4177 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4178 FND_MSG_PUB.Add_Exc_Msg(
4179 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
4180 END IF;
4181
4182 return;
4183
4184 END schedule_mix_model;
4185
4186 -- This procedure schedules the new flow schedules created from
4187 -- unscheduled orders on the line within the defined scheduling window.
4188
4189 PROCEDURE Schedule(
4190 p_rule_id IN NUMBER,
4191 p_line_id IN NUMBER,
4192 p_org_id IN NUMBER,
4193 p_scheduling_start_date IN DATE,
4194 p_scheduling_end_date IN DATE,
4195 p_flex_tolerance IN NUMBER,
4196 x_return_status OUT NOCOPY VARCHAR2,
4197 x_msg_count OUT NOCOPY NUMBER,
4198 x_msg_data OUT NOCOPY VARCHAR2)
4199 IS
4200
4201 v_algorithm NUMBER;
4202 v_start_date NUMBER;
4203 v_end_date NUMBER;
4204 v_cap_tab CapTabTyp;
4205 v_itemQty_tab ItemQtyTabTyp;
4206 v_orderMod_tab OrderModTabTyp;
4207 v_item_alloc_tab ItemAllocTabTyp;
4208 v_early_date DATE;
4209 v_num_flow NUMBER;
4210 v_item_id NUMBER;
4211 v_order_quantity NUMBER;
4212 i NUMBER;
4213 v_source_type NUMBER;
4214 v_current_item NUMBER;
4215 v_remain_qty NUMBER;
4216 v_alloc_qty NUMBER;
4217 v_item_demand_tab ItemDemandTabTyp;
4218 v_demand_tab DemandTabTyp;
4219 v_time_tab TimeTabTyp;
4220 v_start_time NUMBER;
4221 v_end_time NUMBER;
4222 v_bom_rev VARCHAR2(3); /* Added for bug 2977987 */
4223 v_bom_rev_date DATE;
4224 v_rout_rev VARCHAR2(3);
4225 v_rout_rev_date DATE;
4226
4227 BEGIN
4228
4229 SAVEPOINT MRP_begin_PVT;
4230
4231 V_PROCEDURE_NAME := 'Schedule';
4232 V_ERROR_LINE := 1;
4233
4234 -- Lets convert the schedule dates to julian format
4235 SELECT to_number(to_char(p_scheduling_start_date,'J')),
4236 to_number(to_char(p_scheduling_end_date,'J')),
4237 to_char(p_scheduling_start_date,'SSSSS'),
4238 to_char(p_scheduling_end_date,'SSSSS')
4239 INTO v_start_date, v_end_date, v_start_time, v_end_time
4240 FROM dual;
4241
4242 -- Get the minimum wip_entity_id that having request id
4243
4244 SELECT MIN(wip_entity_id)
4245 INTO G_WIP_ENTITY_ID
4246 FROM wip_flow_schedules
4247 WHERE line_id = p_line_id
4248 and organization_id = p_org_id
4249 and request_id = USERENV('SESSIONID');
4250
4251 -- Time stamp the existing flow schedules
4252 time_existing_fs(p_org_id, p_line_id, v_start_date, v_start_time,
4253 v_end_date, v_end_time, v_time_tab);
4254
4255 -- First off, lets find the net capacity of the line in question
4256 -- Note that this procedure populates the plsql table
4257
4258 -- MRP_UTIL.MRP_TIMING('Before calculating line capacity ...');
4259
4260 calculate_linecap(
4261 p_line_id,
4262 p_org_id,
4263 p_flex_tolerance,
4264 v_cap_tab,
4265 v_time_tab,
4266 v_start_date,
4267 v_start_time,
4268 v_end_date,
4269 v_end_time);
4270
4271 -- dbms_output.put_line('Hi there!!!');
4272
4273 -- MRP_UTIL.MRP_TIMING('After calculating line capacity ...');
4274
4275 --Check the algorithm associated with the scheduling rule
4276
4277 SELECT DISTINCT heuristic_code
4278 INTO v_algorithm
4279 FROM mrp_scheduling_rules
4280 WHERE rule_id = p_rule_id
4281 AND NVL(user_defined,C_USER_DEFINE_NO) = C_USER_DEFINE_NO ;
4282
4283 V_ERROR_LINE := 2;
4284
4285 -- Check for the mode of operation or type of unscheduled orders,
4286 -- Sales Orders or Planned Orders
4287
4288 SELECT demand_source_type
4289 INTO v_source_type
4290 FROM wip_flow_schedules
4291 WHERE request_id = USERENV('SESSIONID')
4292 AND organization_id = p_org_id
4293 AND scheduled_flag = C_NO
4294 AND rownum = 1
4295 AND wip_entity_id >= G_WIP_ENTITY_ID;
4296
4297 V_ERROR_LINE := 3;
4298
4299 -- MRP_UTIL.MRP_TIMING('Before calculating demand and round type for each item');
4300 -- Calculate demand and find round type for all items
4301
4302 calculate_demand(p_line_id, p_org_id, v_demand_tab);
4303
4304 -- MRP_UTIL.MRP_TIMING('After calculating demand and round type for each item');
4305
4306 IF v_algorithm = C_NO_LEVEL_LOAD THEN
4307 -- dbms_output.put_line('Algorithm is no level load');
4308
4309 -- If source type is sales orders
4310
4311 IF v_source_type IN (C_INT_SALES_ORDER,C_EXT_SALES_ORDER) THEN
4312 -- dbms_output.put_line('Source type is SALES ORDERS');
4313
4314 -- round up planned quantities of all flow schedules which are fractional
4315 -- with round attribute = yes.
4316
4317 rounding_process(p_org_id, p_line_id, p_rule_id, v_demand_tab);
4318
4319 -- dbms_output.put_line('Scheduling...');
4320 schedule_orders(p_line_id, p_org_id, p_rule_id, v_cap_tab, v_demand_tab,
4321 v_time_tab);
4322
4323 END IF;
4324
4325 -- IF demand source of the flow schedules is planned orders, order modifiers
4326 -- will be applied.
4327
4328 IF v_source_type = C_PLANNED_ORDER THEN
4329
4330 -- Find round type for all items
4331 calculate_demand(p_line_id, p_org_id, v_demand_tab);
4332
4333 -- dbms_output.put_line('Source type is PLANNED ORDERS');
4334
4335 -- Calculate the order quantities for each item based on order modifiers
4336 -- that have associated unscheduled flow schedules.
4337
4338 -- dbms_output.put_line('Calculating order quantities...');
4339 calculate_order_quantities(p_org_id, v_orderMod_tab);
4340
4341 -- Create new flow schedules to replace the original ones to respect order
4342 -- modifiers.
4343
4344 -- dbms_output.put_line('Creating new flow schedules with order modifiers');
4345 create_po_fs(p_org_id, p_line_id, p_rule_id, v_orderMod_tab);
4346
4347 -- round up planned quantities of all flow schedules which are fractional
4348 -- with round attribute = yes.
4349
4350 rounding_process(p_org_id, p_line_id, p_rule_id, v_demand_tab);
4351
4352 -- NOW, we need to sequence the flow schedules and then schedule them.
4353 -- The algorithm used here is same as that used for sales orders.
4354 -- dbms_output.put_line('Scheduling...');
4355
4356 schedule_orders(p_line_id, p_org_id, p_rule_id, v_cap_tab, v_demand_tab,
4357 v_time_tab);
4358
4359 END IF;
4360 ELSIF v_algorithm = C_LEVEL_LOAD THEN
4361 -- dbms_output.put_line('Algorithm is level load');
4362
4363 IF v_source_type IN (C_INT_SALES_ORDER,C_EXT_SALES_ORDER) THEN
4364 -- dbms_output.put_line('Source type is SALES ORDERS');
4365
4366 -- round up planned quantities of all flow schedules which are fractional
4367 -- with round attribute = yes.
4368 -- dbms_output.put_line('Rounding...');
4369 rounding_process(p_org_id, p_line_id, p_rule_id, v_demand_tab);
4370
4371 -- Schedule sales orders to match the production rate by updating
4372 -- the scheduled completion date and the build sequence for the
4373 -- schedule group. If only a partial flow schedule quantity is
4374 -- required to fill the production rate, then the flow schedule
4375 -- will be split into more than one flow schedules.
4376
4377 -- dbms_output.put_line('Scheduling...');
4378 schedule_orders_level(p_line_id, p_org_id, p_rule_id, v_cap_tab,
4379 v_time_tab);
4380
4381 -- dbms_output.put_line('updatiing build sequences...');
4382 update_buildseq(p_line_id, p_org_id);
4383
4384 ELSIF v_source_type = C_PLANNED_ORDER THEN
4385
4386 -- dbms_output.put_line('Source type is PLANNED ORDERS');
4387
4388 -- Calculate the order quantities for each item based on order modifiers
4389 -- that have associated unscheduled flow schedules.
4390
4391 -- dbms_output.put_line('Calculating order modifier quantities...');
4392 -- dbms_output.put_line('Calculating order modifier quantities...');
4393
4394 calculate_order_quantities(p_org_id, v_orderMod_tab);
4395
4396 -- Create new flow schedules to replace the original ones to respect order
4397 -- modifiers.
4398
4399 -- dbms_output.put_line('Creating new flow schedules with order modifier quantities.');
4400
4401 -- dbms_output.put_line('Creating new flow schedules with order modifier quantities.');
4402 create_po_fs(p_org_id, p_line_id, p_rule_id, v_orderMod_tab);
4403
4404 -- round up planned quantities of all flow schedules which are fractional
4405 -- with round attribute = yes.
4406
4407 rounding_process(p_org_id, p_line_id, p_rule_id, v_demand_tab);
4408 -- dbms_output.put_line('Scheduling ...');
4409 schedule_orders_level(p_line_id, p_org_id, p_rule_id, v_cap_tab, v_time_tab);
4410
4411 -- dbms_output.put_line('updateing build sequences...');
4412 update_buildseq(p_line_id, p_org_id);
4413
4414 END IF;
4415 ELSIF v_algorithm = C_MIXED_MODEL THEN
4416
4417 -- MRP_UTIL.MRP_TIMING('Beginning of MIX MODEL ALGORITHM');
4418
4419 -- dbms_output.put_line('Algorithm is mixed model');
4420
4421 -- In the mixed model algorithm, flow schedules from sales orders and
4422 -- planned orders are manipulated in the same manner. A manufacturing
4423 -- pattern of items is first established based on the demand of each
4424 -- item with respect to the total demand of all unscheduled flow schedules.
4425 -- The flow schedules are then scheduled respecting this pattern and
4426 -- the order of the flow schedules which is established using the user-
4427 -- chosen criteria.
4428
4429 -- Round up planned quantities of all flow schedules which are fractional
4430 -- with round attribute = yes for the item.
4431
4432 -- MRP_UTIL.MRP_TIMING('Before Rounding Process');
4433
4434 rounding_process(p_org_id, p_line_id, p_rule_id, v_demand_tab);
4435
4436 -- Calculate total demand for each item
4437
4438 -- MRP_UTIL.MRP_TIMING('Before Calculating total demand for each item');
4439
4440 calculate_demand_mix(p_line_id, p_org_id, v_item_demand_tab);
4441
4442 -- MRP_UTIL.MRP_TIMING('After Calculating total demand for each item');
4443 schedule_mix_model(p_line_id, p_org_id, p_rule_id, v_cap_tab, v_demand_tab,
4444 v_item_demand_tab, v_time_tab);
4445
4446 END IF;
4447
4448 -- Delete all flow schedules which have not been scheduled within the
4449 -- scheduling window.
4450
4451 DELETE
4452 FROM wip_flow_schedules
4453 WHERE scheduled_flag = C_NO
4454 AND request_id = USERENV('SESSIONID')
4455 AND organization_id = p_org_id
4456 AND wip_entity_id >= G_WIP_ENTITY_ID;
4457
4458
4459 /* Start of fix for bug 2977987: We get each flow schedule record
4460 scheduled in the present run, set bom and routing revision dates
4461 to its scheduled_completion_date and then re-calculate the
4462 effective bom and routing revisions active on that date */
4463
4464 FOR fs_list_rec IN fs_list(p_line_id,p_org_id) LOOP
4465
4466 v_bom_rev_date := NULL;
4467 v_bom_rev := NULL;
4468 v_rout_rev_date := NULL;
4469 v_rout_rev := NULL;
4470 v_item_id := fs_list_rec.primary_item_id;
4471
4472 if (fs_list_rec.bom_revision_date is not null) then
4473
4474 v_bom_rev_date := fs_list_rec.scheduled_completion_date;
4475
4476 BOM_REVISIONS.Get_Revision(
4477 type => 'PART',
4478 eco_status => 'EXCLUDE_OPEN_HOLD',
4479 examine_type => 'ALL',
4480 org_id => p_org_id,
4481 item_id => v_item_id,
4482 rev_date => v_bom_rev_date,
4483 itm_rev => v_bom_rev);
4484
4485 end if;
4486
4487 if (fs_list_rec.routing_revision_date is not null) then
4488
4489 v_rout_rev_date := fs_list_rec.scheduled_completion_date;
4490
4491 BOM_REVISIONS.Get_Revision(
4492 type => 'PROCESS',
4493 eco_status => 'EXCLUDE_OPEN_HOLD',
4494 examine_type => 'ALL',
4495 org_id => p_org_id,
4496 item_id => v_item_id,
4497 rev_date => v_rout_rev_date,
4498 itm_rev => v_rout_rev);
4499
4500 end if;
4501
4502 UPDATE wip_flow_schedules
4503 SET bom_revision = v_bom_rev,
4504 bom_revision_date = v_bom_rev_date,
4505 routing_revision = v_rout_rev,
4506 routing_revision_date = v_rout_rev_date
4507 WHERE wip_entity_id = fs_list_rec.wip_entity_id
4508 AND organization_id = p_org_id;
4509
4510 END LOOP;
4511
4512 /* End of fix for bug 2977987 */
4513
4514 V_ERROR_LINE := 4;
4515
4516 x_return_status := FND_API.G_RET_STS_SUCCESS;
4517
4518 -- dbms_output.put_line('End of Scheduling...');
4519
4520 -- MRP_UTIL.MRP_TIMING('End of Scheduling');
4521
4522 EXCEPTION
4523
4524 WHEN OTHERS THEN
4525 ROLLBACK TO MRP_begin_PVT;
4526
4527 -- dbms_output.put_line('There is an error!!!!');
4528
4529 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4530 FND_MSG_PUB.Add_Exc_Msg(
4531 V_PKG_NAME,V_PROCEDURE_NAME||': Line '||to_char(V_ERROR_LINE));
4532 END IF;
4533
4534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4535 -- dbms_output.put_line('!!!ERROR!!!:' || to_char(sqlcode) ||
4536 -- substr(sqlerrm,1,60));
4537 FND_MSG_PUB.Count_And_Get
4538 (p_count => x_msg_count,
4539 p_data => x_msg_data
4540 );
4541 return;
4542 END Schedule;
4543
4544
4545 -- This function calculates proper schedule start time
4546 -- based on completion_date, completion_time, and lead_time.
4547 -- All time formats are Julian calendar format.
4548 FUNCTION calculate_begin_time(
4549 p_org_id IN NUMBER,
4550 p_completion_date IN DATE,
4551 p_lead_time IN NUMBER,
4552 p_start_time IN NUMBER,
4553 p_end_time IN NUMBER) RETURN DATE IS
4554 l_completion_time NUMBER;
4555 l_completion_date DATE;
4556 l_begin_date DATE;
4557 l_working_time NUMBER;
4558 l_lead_time NUMBER;
4559 l_offset_time NUMBER;
4560 l_temp_date DATE;
4561 l_end_time NUMBER;
4562 BEGIN
4563
4564
4565 l_completion_date := trunc(p_completion_date);
4566 l_completion_time := to_char(p_completion_date,'SSSSS');
4567
4568 l_end_time := p_end_time;
4569 if (p_end_time <= p_start_time) then
4570 if (l_completion_time < l_end_time ) then
4571 l_completion_date := trunc(p_completion_date)-1;
4572 l_completion_time := to_char(p_completion_date,'SSSSS')+24*3600;
4573 end if;
4574 l_end_time := l_end_time + 24*3600;
4575 end if;
4576
4577
4578 -- total working hrs in workday
4579 l_working_time := l_end_time - p_start_time;
4580
4581
4582
4583 -- exclude workable time in completion_date from lead_time
4584 l_lead_time := (p_lead_time*l_working_time) - (l_completion_time-p_start_time);
4585 if (l_lead_time <= 0) then
4586 l_lead_time := p_lead_time*l_working_time;
4587 l_temp_date := trunc(l_completion_date);
4588 l_end_time := l_completion_time;
4589 else
4590 -- find out the schedule start_date based on the offset
4591 -- first, we find out the offset date and then use MOD function
4592 -- to find out the offset time
4593 l_temp_date := mrp_calendar.date_offset(
4594 p_org_id,
4595 1, -- daily bucket
4596 l_completion_date,
4597 -ceil(l_lead_time/l_working_time));
4598 end if;
4599
4600
4601 -- find out specific time to start
4602 -- exception case, when lead_time is same as working_hrs
4603 -- MOD function will return '0', thus, offset_time should be start_time
4604 IF l_lead_time = l_working_time THEN
4605 l_offset_time := p_start_time;
4606 ELSE
4607 l_offset_time := l_end_time - mod(l_lead_time, l_working_time);
4608
4609 END IF;
4610
4611 l_begin_date := l_temp_date + l_offset_time/86400;
4612
4613 return l_begin_date;
4614
4615 END calculate_begin_time;
4616
4617 FUNCTION calculate_completion_time(
4618 p_org_id IN NUMBER,
4619 p_item_id IN NUMBER,
4620 p_qty IN NUMBER,
4621 p_line_id IN NUMBER,
4622 p_start_date IN DATE) RETURN DATE IS
4623 l_lead_time NUMBER;
4624 l_fixed_time NUMBER;
4625 l_variable_time NUMBER;/*Added for bugfix:7211657 */
4626 l_line_start_time NUMBER;
4627 l_line_end_time NUMBER;
4628 l_working_time NUMBER;
4629 l_time NUMBER;
4630 l_date DATE;
4631 l_offset_time NUMBER;
4632 l_offset_day NUMBER;
4633 l_remain_time NUMBER;
4634 l_start_time NUMBER;
4635 l_start_date DATE;
4636 l_sec_rate NUMBER; -- time to produce 1 quantity in seconds
4637 BEGIN
4638
4639 l_start_date := trunc(p_start_date);
4640 l_start_time := to_char(p_start_date,'SSSSS');
4641
4642 select start_time, stop_time, 3600/maximum_rate
4643 into l_line_start_time, l_line_end_time, l_sec_rate
4644 from wip_lines
4645 where organization_id = p_org_id
4646 and line_id = p_line_id;
4647
4648 if (l_line_end_time <= l_line_start_time) then
4649 l_line_end_time := l_line_end_time + 24*3600;
4650 end if;
4651
4652 l_working_time := l_line_end_time - l_line_start_time;
4653
4654 /*Changed the query to fetch variable_lead_time also for bug fix 7211657 */
4655
4656 select NVL(fixed_lead_time,0)*l_working_time,NVL(variable_lead_time,0)*l_working_time
4657 into l_fixed_time,l_variable_time
4658 from mtl_system_items
4659 where organization_id = p_org_id
4660 and inventory_item_id = p_item_id;
4661
4662 -- l_lead_time := l_fixed_time;
4663 /*Changed lead time calculation for bugfix:7211657 */
4664 l_lead_time := l_fixed_time + ((p_qty-1)*l_variable_time);
4665 -- l_date and l_time to store the possible start date and time
4666
4667 -- get the first workday. If it's not the same as p_start_date, need
4668 -- to move it to next workday and also set the l_time to start of line
4669 l_date := mrp_calendar.next_work_day(p_org_id,1,p_start_date);
4670 if (trunc(l_start_date) <> trunc(l_date)) then
4671 l_time := l_line_start_time;
4672 else
4673 l_time := l_start_time;
4674 if (l_time > l_line_start_time) then
4675 l_time := l_line_start_time + ceil((l_time-l_line_start_time)/l_sec_rate)*l_sec_rate;
4676 end if;
4677 end if;
4678
4679 -- If the l_time greater than the end time of line, move it to the
4680 -- workday at the line start time
4681 -- If l_time less than the start of line, move the start time to
4682 -- line start time of the day.
4683 -- If it's between start and end time of line, use up the line and
4684 -- move to next workday.
4685 if (l_time > l_line_end_time) then
4686 l_date := mrp_calendar.next_work_day(p_org_id,1,l_date+1);
4687 l_time := l_line_start_time;
4688 elsif (l_time < l_line_start_time) then
4689 l_time := l_line_start_time;
4690 else
4691 l_remain_time := l_line_end_time-l_time;
4692 if (l_lead_time - l_remain_time > 0) then
4693 l_lead_time := l_lead_time - l_remain_time;
4694 l_date := mrp_calendar.next_work_day(p_org_id,1,l_date+1);
4695 l_time := l_line_start_time;
4696 end if;
4697 end if;
4698
4699
4700 if l_lead_time = l_working_time then
4701 l_offset_time := l_line_end_time;
4702 l_offset_day := l_lead_time/l_working_time - 1;
4703 else
4704 l_offset_time := l_time + mod(l_lead_time, l_working_time);
4705 l_offset_day := floor(l_lead_time/l_working_time);
4706 end if;
4707
4708 l_date := mrp_calendar.date_offset(
4709 p_org_id,
4710 1, -- daily bucket
4711 l_date,
4712 l_offset_day);
4713
4714
4715 if (l_offset_time = l_line_start_time and l_fixed_time <> 0) then
4716 l_date := mrp_calendar.prev_work_day(p_org_id,1,l_date-1);
4717 l_offset_time := l_line_end_time;
4718 end if;
4719
4720 return ( l_date + l_offset_time/86400 );
4721
4722 END calculate_completion_time;
4723
4724 END;
[Home] [Help]