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