1 PACKAGE BODY FLM_CREATE_PRODUCT_SYNCH AS
2 /* $Header: FLMCPSYB.pls 120.2 2005/08/04 12:06:28 asuherma noship $ */
3 -- Modified to support lot based material
4 -- Added basis type and qty per lot
5 type comp_rec is record
6 (item_id NUMBER,
7 usage NUMBER,
8 line_id NUMBER,
9 operation_seq_num NUMBER,
10 line_op_seq_id NUMBER,
11 basis_type NUMBER,
12 qty_per_lot NUMBER);
13
14 type comp_list is table of comp_rec index by BINARY_INTEGER;
15
16 g_debug boolean := FALSE;
17
18 -- Return the operation time of a particular line-op rounded up to the
19 -- next takt time.
20 FUNCTION get_operation_times (p_line_op_seq_id NUMBER, p_takt_time NUMBER) return NUMBER IS
21 l_total_time NUMBER;
22 BEGIN
23 select CEIL(NVL(total_time_calc,0)/p_takt_time) * p_takt_time
24 into l_total_time
25 from bom_operation_sequences
26 where operation_sequence_id = p_line_op_seq_id;
27
28 return (l_total_time);
29 END get_operation_times;
30
31 -- To calculate the schedule start time of the feeder line schedule
32 FUNCTION feeder_line_start_date (p_org_id NUMBER,
33 p_feeder_line_id NUMBER,
34 p_item_id NUMBER,
35 p_qty NUMBER,
36 p_completion_date DATE) return DATE
37 IS
38 l_start_time NUMBER;
39 l_stop_time NUMBER;
40 l_lead_time NUMBER;
41 BEGIN
42 select start_time, stop_time
43 into l_start_time, l_stop_time
44 from wip_lines
45 where line_id = p_feeder_line_id
46 and organization_id = p_org_id;
47
48 select nvl(fixed_lead_time,0) + (p_qty - 1)*nvl(variable_lead_time,0)
49 into l_lead_time
50 from mtl_system_items
51 where organization_id = p_org_id
52 and inventory_item_id = p_item_id;
53
54 return ( MRP_LINE_SCHEDULE_ALGORITHM.calculate_begin_time(
55 p_org_id ,
56 p_completion_date,
57 l_lead_time ,
58 l_start_time,
59 l_stop_time) );
60
61 END feeder_line_start_date;
62
63 -- This function is used to calculate the completion date
64 -- of the feeder line. The input parameter is the line_op
65 -- sequence id from where the feeder line goes in.
66 -- Using the routing networks information, it sums up the
67 -- operation time (rounded up to the multiple of takt time)
68 -- of the line-op from the given line op
69 -- to the last completion line op by tracing only the primary path.
70 -- Then substract that time from the completion time
71 -- (using calculate_begin_time routine) to get required
72 -- completion time of the feeder line at particular line op.
73 -- 2 parameters p_qty and p_fast_feeder_line added for bug 2373141
74 FUNCTION feeder_line_comp_date (p_org_id NUMBER,
75 p_assembly_line_id NUMBER,
76 p_assembly_item_id NUMBER,
77 p_assembly_start_date DATE,
78 p_assembly_comp_date DATE,
79 p_line_op_seq_id NUMBER,
80 p_qty NUMBER,
81 p_fast_feeder_line NUMBER) return DATE
82 IS
83 l_takt_time NUMBER;
84 l_operation_times NUMBER;
85 l_date DATE;
86 l_start_time NUMBER;
87 l_stop_time NUMBER;
88 l_working_hours NUMBER;
89 l_assembly_comp_date DATE;
90
91 cursor network_cur (cv_start_operation_sequence_id number) is
92 select bon.to_op_seq_id to_op_seq_id
93 from bom_operation_networks bon
94 connect by prior to_op_seq_id = from_op_seq_id
95 and
96 nvl(bon.transition_type, 0) not in (2, 3)
97 start with from_op_seq_id = cv_start_operation_sequence_id
98 and
99 nvl(bon.transition_type, 0) not in (2, 3);
100
101 BEGIN
102
103 select start_time, stop_time, 1/maximum_rate
104 into l_start_time, l_stop_time, l_takt_time
105 from wip_lines
106 where line_id = p_assembly_line_id
107 and organization_id = p_org_id;
108
109 if (l_stop_time > l_start_time) then
110 l_working_hours := (l_stop_time - l_start_time)/3600;
111 else
112 l_working_hours := (l_stop_time + 24*3600 - l_start_time)/3600;
113 end if;
114
115 l_operation_times := get_operation_times(p_line_op_seq_id, l_takt_time);
116 for network_rec in network_cur (p_line_op_seq_id) loop
117 l_operation_times := l_operation_times +
118 get_operation_times(network_rec.to_op_seq_id,l_takt_time);
119 end loop;
120
121 /* Added for bug 2373141 */
122 if ( p_fast_feeder_line > 0 ) then
123 l_assembly_comp_date := p_assembly_comp_date - ((((p_qty -1) * l_takt_time) * 3600)/86400);
124 else
125 l_assembly_comp_date := p_assembly_comp_date;
126 end if;
127 /* Added for bug 2373141 */
128
129 l_date := MRP_LINE_SCHEDULE_ALGORITHM.calculate_begin_time(
130 p_org_id ,
131 l_assembly_comp_date,
132 l_operation_times/l_working_hours ,
133 l_start_time,
134 l_stop_time);
135
136 -- If completion time is the same as line start time in the day, then
137 -- the completion time is set to the previous work day at line stop time
138 --fix bug#3170105
139 --if ( l_date = (trunc(l_date)+l_start_time/(24*3600)) ) then
140 -- l_date := mrp_calendar.prev_work_day(p_org_id,1,trunc(l_date)-1)+l_stop_time/(24*3600);
141 --end if;
142 if ( l_date = (flm_timezone.server_to_calendar(l_date)+l_start_time/(24*3600)) ) then
143 l_date := mrp_calendar.prev_work_day(p_org_id,1,
144 flm_timezone.server_to_calendar(l_date)-1)+l_stop_time/(24*3600);
145 end if;
146 --end of fix bug#3170105
147
148 return (l_date);
149
150 END feeder_line_comp_date;
151
152 -- Find out if the line-op is a valid line-op.
153 -- Valid line-op is line-op that is in the primary-path
154 FUNCTION is_valid_seq (p_op_seq_id in number) return boolean is
155 l_cnt NUMBER;
156 BEGIN
157 if (p_op_seq_id is null) then
158 return false;
159 end if;
160
161 -- Look at the from_op_seq_id to find out if the line-op
162 -- is in primary path.
163 select count(*)
164 into l_cnt
165 from bom_operation_networks
166 where from_op_seq_id = p_op_seq_id
167 and nvl(transition_type, 3) = 1;
168
169 if (l_cnt = 0) then
170 -- Look at the to_op_seq_id to find out if the line-op
171 -- is in primary path. This is the exception for the
172 -- last line-op
173 select count(*)
174 into l_cnt
175 from bom_operation_networks
176 where to_op_seq_id = p_op_seq_id
177 and nvl(transition_type, 3) = 1;
178
179 if (l_cnt = 0) then
180 -- If not network exists, then if only one line operation exists
181 -- for this routing, it's valid, otherwise it's not valid.
182 select count(*)
183 into l_cnt
184 from bom_operation_sequences
185 where operation_type = 3
186 and routing_sequence_id = (
187 select max(routing_sequence_id)
188 from bom_operation_sequences
189 where operation_sequence_id = p_op_seq_id
190 );
191 if l_cnt = 1 then
192 return true;
193 end if;
194 return false;
195 end if;
196
197 end if;
198
199 return true;
200
201 END is_valid_seq;
202
203 -- This function returns the subassemblies that are being supplied from
204 -- another line. It takes all subassemblies that are being used in the
205 -- line operations which are in the primary path or in the alternate path.
206 -- It ignores any subassemblies that are used in the rework operations.
207 -- This routine also explodes the phantom component.
208 PROCEDURE get_subassemblies(
209 arg_org_id in number,
210 arg_schedule_number in varchar2,
211 arg_top_assy_id in number,
212 arg_alt_bom_desig in VARCHAR2,
213 arg_alt_rtg_desig in VARCHAR2,
214 arg_sched_start_date in DATE,
215 arg_comp_table in out NOCOPY comp_list)
216 IS
217
218 var_assy_id NUMBER;
219 assy_table comp_list;
220 max_assy_count NUMBER;
221 max_comp_count NUMBER;
222 curr_assy_count NUMBER;
223 var_comp_id NUMBER;
224 var_comp_name VARCHAR(40);
225 var_usage NUMBER;
226 var_wip_supply_type NUMBER;
227 var_line_id NUMBER;
228 var_operation_seq_num NUMBER;
229 var_routing_sequence_id NUMBER;
230 var_line_op_seq_id NUMBER;
231 var_count NUMBER;
232 var_status BOOLEAN;
233 var_inherit_phantom NUMBER;
234 var_basis_type NUMBER;
235 var_qty_per_lot NUMBER;
236
237 l_top_bill_sequence_id NUMBER;
238 l_bill_sequence_id NUMBER;
239
240 CURSOR comp(var_assy_id NUMBER,p_start_date DATE) IS --fix bug#3170105
241 select expl.component_item_id component_item_id,
242 comp.operation_seq_num operation_seq_num,
243 SUM(comp.component_quantity) extended_quantity,
244 MIN(DECODE(comp.wip_supply_type, NULL,
245 DECODE(sys.wip_supply_type, NULL,
246 1, sys.wip_supply_type),
247 comp.wip_supply_type)) wip_supply_type,
248 MIN(comp.component_quantity) component_quantity,
249 MIN(nvl(comp.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL)) basis_type
250 from mtl_system_items sys,
251 bom_inventory_components comp,
252 bom_explosions expl,
253 bom_bill_of_materials bbm
254 where sys.planning_make_buy_code = 1
255 and bbm.organization_id = sys.organization_id
256 and comp.component_item_id = sys.inventory_item_id
257 /* Fixed bug #2503750
258 Added condition to take into consideration bom effectivity dates. */
259 and comp.component_sequence_id = expl.component_sequence_id
260 /* Updated by Liye Ma. Mar.5th 2001
261 Fixed bug 1668713
262 Select only components whose type is standard */
263 and sys.bom_item_type = 4
264 /* End of Update */
265 and comp.component_item_id = expl.component_item_id
266 and comp.bill_sequence_id = expl.bill_sequence_id
267 and bbm.organization_id = arg_org_id
268 and bbm.assembly_item_id = var_assy_id
269 and (NVL(bbm.alternate_bom_designator, 'ABD756fhh466')
270 = NVL(arg_alt_bom_desig, 'ABD756fhh466')
271 or
272 (bbm.alternate_bom_designator is null and
273 not exists
274 (select null
275 from bom_bill_of_materials bbm1
276 where bbm1.alternate_bom_designator =
277 arg_alt_bom_desig
278 and bbm1.organization_id = bbm.organization_id
279 and bbm1.assembly_item_id = bbm.assembly_item_id)))
280 and bbm.common_bill_sequence_id = expl.bill_sequence_id
281 /* Added the following clause for the bug 1817962 */
282 /* and expl.top_bill_sequence_id =
283 (select bill_sequence_id from
284 bom_bill_of_materials
285 where assembly_item_id = arg_top_assy_id
286 and organization_id = arg_org_id
287 and nvl(alternate_bom_designator,'@@@') =
288 nvl(arg_alt_bom_desig,'@@@'))
289 */
290 and expl.top_bill_sequence_id = l_top_bill_sequence_id
291 and expl.assembly_item_id is not null
292 and expl.effectivity_date <= p_start_date --fix bug#3170105
293 and NVL(expl.disable_date,p_start_date+1) > p_start_date
294 and expl.explosion_type = 'ALL'
295 group by expl.component_item_id,comp.operation_seq_num;
296
297 BEGIN
298 -- Find the routing for the arg_top_assy_id
299 select --routing_sequence_id
300 common_routing_sequence_id --3701766 3891345.999
301 into var_routing_sequence_id
302 from bom_operational_routings
303 where organization_id = arg_org_id
304 and assembly_item_id = arg_top_assy_id
305 and NVL(alternate_routing_designator, 'ABD756fhh456') =
306 NVL(arg_alt_rtg_desig, 'ABD756fhh456');
307
308 max_assy_count := 1;
309 curr_assy_count := 0;
310 assy_table(1).item_id := arg_top_assy_id;
311 assy_table(1).usage := 1;
312 max_comp_count := 0;
313
314 if g_debug then
315 MRP_UTIL.MRP_LOG('In get assemblies');
316 end if;
317 /* Added to retrive inherit_phantom_op_seq value for Bug # 1973152 */
318 SELECT inherit_phantom_op_seq INTO var_inherit_phantom
319 from bom_parameters
320 where organization_id = arg_org_id;
321
322 BEGIN
323 l_bill_sequence_id := null;
324 select bill_sequence_id
325 into l_bill_sequence_id
326 from bom_bill_of_materials
327 where organization_id = arg_org_id
328 and assembly_item_id = arg_top_assy_id
329 and nvl(alternate_bom_designator, '@@@@') =
330 nvl(arg_alt_bom_desig, '@@@@');
331 l_top_bill_sequence_id := l_bill_sequence_id;
332 select max(top_bill_sequence_id)
333 into l_top_bill_sequence_id
334 from bom_explosions
335 where component_item_id = arg_top_assy_id
336 and organization_id = arg_org_id;
337 EXCEPTION
338 WHEN OTHERS THEN
339 l_top_bill_sequence_id := l_bill_sequence_id;
340 END;
341
342 LOOP
343 curr_assy_count := curr_assy_count + 1;
344 if curr_assy_count > max_assy_count then
345 exit;
346 end if;
347
348 var_assy_id := assy_table(curr_assy_count).item_id;
349
350 FOR comp_record IN comp(var_assy_id,arg_sched_start_date) LOOP
351 var_comp_id := comp_record.component_item_id;
352 var_usage := comp_record.extended_quantity;
353 var_wip_supply_type := comp_record.wip_supply_type;
354 -- This stores the operation_seq_num from the parent's BOM
355 var_operation_seq_num := comp_record.operation_seq_num;
356
357 -- Added to support lot based material
358 -- Retrieve the basis type and component quantity from bom_explosions table.
359 var_basis_type := comp_record.basis_type;
360 var_qty_per_lot := comp_record.component_quantity;
361
362 if g_debug then
363 MRP_UTIL.MRP_LOG(' Component ' || to_char(var_comp_id) ||
364 ' Usage '|| to_char(var_usage) ||
365 ' Wip Supply type '|| to_char(var_wip_supply_type) ||
366 ' Basis Type '||to_char(var_basis_type) ||
367 ' Qty '||to_char(var_qty_per_lot));
368 end if;
369 if var_wip_supply_type = 6 then
370 if g_debug then
371 MRP_UTIL.MRP_LOG('Phantom Component ');
372 end if;
373 max_assy_count := max_assy_count + 1;
374 assy_table(max_assy_count).item_id := var_comp_id;
375 assy_table(max_assy_count).usage :=
376 assy_table(curr_assy_count).usage * var_usage;
377
378 -- Two cases :
379 -- a. For 1st level Subassembly, the operation_seq_num is the
380 -- obtained from the BOM of its parent which is the top assembly.
381 -- b. Otherwise, the operation_seq_num is the operation_seq_num of
382 -- its parent.
383 if (var_inherit_phantom = 1) then
384 if (curr_assy_count = 1) then
385 assy_table(max_assy_count).operation_seq_num := var_operation_seq_num;
386 else
387 assy_table(max_assy_count).operation_seq_num :=
388 assy_table(curr_assy_count).operation_seq_num;
389 end if;
390 end if;
391 if (var_inherit_phantom = 2) then
392 assy_table(max_assy_count).operation_seq_num := var_operation_seq_num;
393 end if;
394 else
395 begin
396 /* Changed the where clause of the query for bug #2508196 to
397 pick up the line on which primary routing of the item exists. */
398 select line_id
399 into var_line_id
400 from bom_operational_routings flow_rtg
401 where flow_rtg.assembly_item_id = var_comp_id
402 and flow_rtg.organization_id = arg_org_id
403 and flow_rtg.cfm_routing_flag = 1
404 and flow_rtg.alternate_routing_designator is null;
405
406 /* where flow_rtg.routing_sequence_id =
407 (select min(routing_sequence_id)
408 from bom_operational_routings rtg1
409 where rtg1.assembly_item_id = var_comp_id
410 and rtg1.organization_id = arg_org_id
411 and rtg1.cfm_routing_flag = 1
412 and NVL(rtg1.priority, 1) =
413 (select NVL(min(priority), 1)
414 from bom_operational_routings rtg
415 where rtg.assembly_item_id = var_comp_id
416 and rtg.organization_id = arg_org_id));
417 */
418 exception
419 WHEN no_data_found THEN
420 var_line_id := NULL;
421 end;
422 if g_debug then
423 MRP_UTIL.MRP_LOG(' Line '|| to_char(var_line_id));
424 end if;
425 if var_line_id is not null then
426 -- If it's 1st level Subassembly, the component operation_seq_num
427 -- is the operation_seq_num obtained from the BOM. Otherwise,
428 -- it's the operation_seq_num of the 1st subassembly parent.
429 /* commented out for bug #1973152
430 if (curr_assy_count <> 1) then
431 var_operation_seq_num := assy_table(curr_assy_count).operation_seq_num;
432 end if;
433 */
434
435 if ((var_inherit_phantom = 1) and (curr_assy_count <> 1)) then
436 var_operation_seq_num := assy_table(curr_assy_count).operation_seq_num;
437 end if;
438
439 -- Get the corresponding line_op_seq_id for the given operation_seq_num in
440 -- the routing of the top assembly
441 begin
442 select line_op_seq_id
443 into var_line_op_seq_id
444 from bom_operation_sequences
445 where routing_sequence_id = var_routing_sequence_id
446 and operation_seq_num = var_operation_seq_num
447 and operation_type = 1
448 and effectivity_date =
449 (select max(effectivity_date)
450 from bom_operation_sequences
451 where routing_sequence_id = var_routing_sequence_id
452 and operation_seq_num = var_operation_seq_num
453 and operation_type = 1);
454
455 exception
456 WHEN no_data_found THEN
457 select concatenated_segments
458 into var_comp_name
459 from mtl_system_items_kfv
460 where inventory_item_id = var_comp_id
461 and organization_id = arg_org_id;
462
463 fnd_message.set_name('FLM','FLM_SYNCH_INVALID_SEQ');
464 fnd_message.set_token('SCHEDULE',arg_schedule_number);
465 fnd_message.set_token('COMPONENT',var_comp_name);
466 MRP_UTIL.MRP_LOG(fnd_message.get);
467 var_line_op_seq_id := NULL;
468 var_status := fnd_concurrent.set_completion_status( status => 'WARNING',
469 message => '');
470
471 end;
472 if g_debug then
473 MRP_UTIL.MRP_LOG(' Line-Op : '|| to_char(var_line_op_seq_id));
474 end if;
475 if g_debug then
476 MRP_UTIL.MRP_LOG(' Line-op : '|| to_char(var_operation_seq_num));
477 end if;
478
479 -- Find out if the component used in the particular operation seq number has
480 -- been synchronized
481 select count(*)
482 into var_count
483 from wip_flow_schedules
484 where primary_item_id = var_comp_id
485 and NVL(synch_schedule_num,FND_API.G_MISS_CHAR) = arg_schedule_number
486 and NVL(synch_operation_seq_num,FND_API.G_MISS_NUM) = var_operation_seq_num;
487
488 -- Include only components that :
489 -- 1. hasn't been synchronized.
490 -- 2. feeder line goes into the primary path
491 if (var_count = 0 and is_valid_seq(var_line_op_seq_id)) then
492 max_comp_count := max_comp_count + 1;
493 arg_comp_table(max_comp_count).item_id := var_comp_id;
494 arg_comp_table(max_comp_count).usage := var_usage *
495 assy_table(curr_assy_count).usage;
496 arg_comp_table(max_comp_count).line_id := var_line_id;
497 arg_comp_table(max_comp_count).operation_seq_num := var_operation_seq_num;
498 arg_comp_table(max_comp_count).line_op_seq_id := var_line_op_seq_id;
499
500 -- Added to support lot based material
501 -- Stores the basis type and qty per lot in the arg_comp_table
502 arg_comp_table(max_comp_count).basis_type := var_basis_type;
503 arg_comp_table(max_comp_count).qty_per_lot := var_qty_per_lot;
504
505 end if;
506 end if;
507 end if;
508 END LOOP;
509 END LOOP;
510 END; -- end of procedure
511
512 PROCEDURE create_schedules(
513 errbuf out NOCOPY varchar2,
514 retcode out NOCOPY number,
515 arg_org_id in number,
516 arg_min_line_code in varchar2,
517 arg_max_line_code in varchar2,
518 arg_start_date in varchar2,
519 arg_end_date in varchar2,
520 arg_commit in varchar2 DEFAULT NULL)
521 IS
522 CURSOR scheds(p_start_date DATE, p_end_date DATE, p_sysdate DATE) IS --fix bug#3170105
523 SELECT flow.schedule_number,
524 flow.build_sequence build_sequence,
525 flow.primary_item_id primary_item_id,
526 flow.line_id line_id,
527 flow.planned_quantity planned_quantity,
528 flow.scheduled_start_date scheduled_start_date,
529 flow.scheduled_completion_date scheduled_completion_date,
530 flow.alternate_bom_designator alternate_bom_designator,
531 flow.alternate_routing_designator alternate_routing_designator,
532 nvl(flow.roll_forwarded_flag,2) roll_forwarded_flag
533 FROM wip_flow_schedules flow,
534 wip_lines lines
535 WHERE flow.planned_quantity - nvl(flow.quantity_completed, 0) > 0
536 AND flow.scheduled_start_date >= p_sysdate
537 AND flow.scheduled_start_date between p_start_date and p_end_date
538 AND flow.line_id = lines.line_id
539 AND flow.organization_id = lines.organization_id
540 AND lines.organization_id = arg_org_id
541 AND lines.line_code BETWEEN arg_min_line_code AND
542 arg_max_line_code;
543
544 var_schedule_number varchar2(30);
545 var_build_seq_id number;
546 var_assy_item_id number;
547 var_line_id number;
548 var_qty number;
549 var_start_date date;
550 var_completion_date date;
551 var_alt_bom varchar2(10);
552 var_alt_rtg varchar2(10);
553 var_comp_tbl comp_list;
554 var_x number;
555 var_y number;
556 var_count number;
557 var_current_row number;
558 var_bom_exists number;
559 var_rtg_exists number;
560 loop_count number;
561 var_roll_forwarded_flag number;
562
563 -- declarations reqd for Flow schedule API
564 l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type;
565 l_x_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type;
566 l_old_flow_schedule_rec
567 MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type;
568 l_control_rec MRP_GLOBALS.Control_Rec_Type := MRP_GLOBALS.G_MISS_CONTROL_REC;
569 l_return_status VARCHAR2(1);
570 l_msg_count NUMBER;
571 l_msg_data VARCHAR2(240);
572
573 l_fast_feeder_line NUMBER;
574
575 --fix bug#3170105
576 l_start_date DATE;
577 l_end_date DATE;
578 l_sysdate DATE;
579 --end of fix bug#3170105
580
581 begin
582
583 g_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
584
585 --fix bug#3170105
586 flm_timezone.init_timezone(arg_org_id);
587 l_start_date := flm_timezone.client_to_server(fnd_date.canonical_to_date(arg_start_date));
588 l_end_date := flm_timezone.client_to_server(fnd_date.canonical_to_date(arg_end_date))+1-1/(24*60*60);
589 l_sysdate := flm_timezone.sysdate00_in_server;
590 --end of fix bug#3170105
591
592 FOR scheds1 IN scheds(l_start_date, l_end_date, l_sysdate) LOOP --fix bug#3170105
593 var_build_seq_id := scheds1.build_sequence;
594 var_schedule_number := scheds1.schedule_number;
595 var_assy_item_id := scheds1.primary_item_id;
596 var_line_id := scheds1.line_id;
597 var_qty := scheds1.planned_quantity;
598 var_start_date := scheds1.scheduled_start_date;
599 var_completion_date := scheds1.scheduled_completion_date;
600 var_alt_bom := scheds1.alternate_bom_designator;
601 var_alt_rtg := scheds1.alternate_routing_designator;
602 var_roll_forwarded_flag := scheds1.roll_forwarded_flag;
603
604 if (var_roll_forwarded_flag = 1) then
605 fnd_message.set_name('FLM','FLM_SYNCH_ROLL_FORWARDED');
606 fnd_message.set_token('SCHEDULE',var_schedule_number);
607 MRP_UTIL.MRP_LOG(fnd_message.get);
608 else
609 if g_debug then
610 MRP_UTIL.MRP_LOG('Retrieved Schedules '||
611 ' Assy '|| to_char(var_assy_item_id) ||
612 ' Line '|| to_char(var_line_id) ||
613 ' Qty '|| to_char(var_qty) ||
614 ' Start Date'|| to_char(var_start_date) ||
615 ' Alt BOM ' || var_alt_bom ||
616 ' Alt Rtg ' || var_alt_rtg);
617 end if;
618
619 -- Make sure to empty out the pl/sql table. Fix bug 1125219
620 if (var_comp_tbl.COUNT <> 0) then
621 var_comp_tbl.DELETE;
622 end if;
623
624 -- get all the sub components for this assembly
625 get_subassemblies(arg_org_id,
626 var_schedule_number,
627 var_assy_item_id,
628 var_alt_bom,
629 var_alt_rtg,
630 var_start_date,
631 var_comp_tbl);
632
633 var_current_row := 1;
634
635 while var_comp_tbl.EXISTS(var_current_row) LOOP
636 if g_debug then
637 MRP_UTIL.MRP_LOG(
638 ' Component '||
639 to_char(var_comp_tbl(var_current_row).item_id) ||
640 ' Usage '||
641 to_char(var_comp_tbl(var_current_row).usage));
642 end if;
643 select count(*)
644 INTO var_rtg_exists
645 from bom_operational_routings rtg
646 where rtg.organization_id = arg_org_id
647 and rtg.assembly_item_id =
648 var_comp_tbl(var_current_row).item_id
649 and NVL(rtg.alternate_routing_designator, 'ABD756fhh456') =
650 NVL(var_alt_rtg, 'ABD756fhh456');
651
652 select count(*)
653 INTO var_bom_exists
654 from bom_bill_of_materials bom
655 where bom.organization_id = arg_org_id
656 and bom.assembly_item_id =
657 var_comp_tbl(var_current_row).item_id
658 and NVL(bom.alternate_bom_designator, 'ABD756fhh456') =
659 NVL(var_alt_bom, 'ABD756fhh456');
660
661 -- create flow schedule using the API
662 l_flow_schedule_rec.organization_id := arg_org_id;
663 l_flow_schedule_rec.primary_item_id :=
664 var_comp_tbl(var_current_row).item_id;
665
666 -- Added for Lot Based Material Support
667 -- For item basis type, the usage is the subassembly cumulative usage * flow schedule qty
668 -- For lot basis type, the usage is the qty per assembly for that subassembly.
669 if (var_comp_tbl(var_current_row).basis_type = WIP_CONSTANTS.ITEM_BASED_MTL) then
670 l_flow_schedule_rec.planned_quantity := var_comp_tbl(var_current_row).usage * var_qty;
671 else
672 l_flow_schedule_rec.planned_quantity := var_comp_tbl(var_current_row).qty_per_lot;
673 end if;
674
675 l_flow_schedule_rec.line_id :=
676 var_comp_tbl(var_current_row).line_id;
677 if var_bom_exists = 1 then
678 l_flow_schedule_rec.alternate_bom_designator := var_alt_bom;
679 else
680 l_flow_schedule_rec.alternate_bom_designator := null;
681 end if;
682 if var_rtg_exists = 1 then
683 l_flow_schedule_rec.alternate_routing_desig := var_alt_rtg;
684 else
685 l_flow_schedule_rec.alternate_routing_desig := null;
686 end if;
687
688 /* Added for bug 2373141 */
689 Select (fl.maximum_rate - ml.maximum_rate)
690 INTO l_fast_feeder_line
691 FROM wip_lines fl, wip_lines ml
692 Where ml.line_id = var_line_id
693 and fl.line_id = var_comp_tbl(var_current_row).line_id;
694 /* Added for bug 2373141 */
695
696 l_flow_schedule_rec.scheduled_completion_date :=
697 feeder_line_comp_date ( arg_org_id, var_line_id,
698 var_assy_item_id, var_start_date, var_completion_date,
699 var_comp_tbl(var_current_row).line_op_seq_id ,var_qty, l_fast_feeder_line);
700
701 l_flow_schedule_rec.scheduled_start_date :=
702 feeder_line_start_date ( arg_org_id, l_flow_schedule_rec.line_id,
703 l_flow_schedule_rec.primary_item_id,
704 l_flow_schedule_rec.planned_quantity,
705 l_flow_schedule_rec.scheduled_completion_date);
706
707 l_flow_schedule_rec.synch_schedule_num := var_schedule_number;
708 l_flow_schedule_rec.synch_operation_seq_num := var_comp_tbl(var_current_row).operation_seq_num;
709 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
710
711 -- check if build sequence exists for same line, org,
712 -- component combination from wip_flow_schedules
713 var_count := 0;
714 loop_count := 1;
715 var_build_seq_id := scheds1.build_sequence;
716 LOOP
717 select count(*)
718 into var_count
719 from wip_flow_schedules flow
720 where
721 -- flow.primary_item_id =
722 -- var_comp_tbl(var_current_row).item_id
723 -- and
724 flow.line_id =
725 var_comp_tbl(var_current_row).line_id
726 and flow.organization_id = arg_org_id
727 and flow.build_sequence = var_build_seq_id
728 and scheduled_completion_date between --fix bug#3170105
729 l_flow_schedule_rec.scheduled_completion_date and
730 l_flow_schedule_rec.scheduled_completion_date+1-(1/(24*60*60));
731
732 loop_count := loop_count + 1;
733
734 EXIT WHEN var_count = 0;
735 if g_debug then
736 MRP_UTIL.MRP_LOG('Found build sequence '||
737 to_char(var_build_seq_id));
738 end if;
739 var_x := ROUND(1/(10*loop_count), 6);
740 var_y := ROUND(1/(10*(loop_count-1)), 6);
741 if var_x = var_y then
742 var_x := ROUND(1/(10*loop_count),1) + 0.1;
743 end if;
744 if var_count > 0 then
745 var_build_seq_id := var_build_seq_id + var_x;
746 end if;
747 END LOOP;
748
749 -- at this point, var_build_seq_id holds the correct build_sequence
750 l_flow_schedule_rec.build_sequence := var_build_seq_id;
751
752 if g_debug then
753 MRP_UTIL.MRP_LOG('Creating schedule: '||
754 ' Assy ' ||
755 to_char(l_flow_schedule_rec.primary_item_id) ||
756 ' Line ' ||
757 to_char(l_flow_schedule_rec.line_id) ||
758 ' Alt BOM '||
759 l_flow_schedule_rec.alternate_bom_designator ||
760 ' Alt Rtg '||
761 l_flow_schedule_rec.alternate_routing_desig ||
762 ' Date '||
763 to_char(l_flow_schedule_rec.scheduled_completion_date) ||
764 ' Build Sequence ' ||
765 to_char(l_flow_schedule_rec.build_sequence) ||
766 ' Qty ' ||
767 to_char(l_flow_schedule_rec.planned_quantity));
768 end if;
769
770 /* Added p_explode_bom for bug number 2079836 */
771 MRP_Flow_Schedule_PVT.Process_Flow_Schedule(
772 p_api_version_number => 1.0,
773 p_init_msg_list => FND_API.G_TRUE,
774 x_return_status => l_return_status,
775 x_msg_count => l_msg_count,
776 x_msg_data => l_msg_data,
777 p_control_rec => l_control_rec,
778 p_flow_schedule_rec => l_flow_schedule_rec,
779 x_flow_schedule_rec => l_x_flow_schedule_rec,
780 p_old_flow_schedule_rec => l_old_flow_schedule_rec,
781 p_explode_bom => 'Y');
782
783 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788
789 var_current_row := var_current_row + 1;
790
791 end loop; -- end of while loop
792
793 end if; -- end of if (var_roll_forwarded_flag = 2)
794
795 END LOOP; -- end of FOR scheds1 IN scheds LOOP
796
797 if NVL(arg_commit,'Y') = 'Y' then
798 COMMIT;
799 end if;
800
801 end; -- end of procedure
802
803
804 END; -- package
[Home] [Help]