[Home] [Help]
PACKAGE BODY: APPS.BOM_CALC_OP_TIMES_PK
Source
1 PACKAGE BODY BOM_CALC_OP_TIMES_PK AS
2 /* $Header: BOMOPTMB.pls 120.1 2006/05/03 06:05:21 abbhardw noship $ */
3
4 /******************calculate_operation_times***********************************/
5
6 PROCEDURE calculate_operation_times (
7 arg_org_id IN NUMBER,
8 arg_routing_sequence_id IN NUMBER) IS
9
10 CURSOR Events_cur IS
11 SELECT operation_sequence_id
12 FROM bom_operation_sequences
13 WHERE routing_sequence_id = arg_routing_sequence_id
14 AND operation_type = 1
15 AND NVL(eco_for_production,2) = 2
16 AND NVL(disable_date, TRUNC(sysdate)+1) > TRUNC(sysdate);
17
18 CURSOR Processes_cur IS
19 SELECT operation_sequence_id
20 FROM bom_operation_sequences
21 WHERE routing_sequence_id = arg_routing_sequence_id
22 AND NVL(eco_for_production,2) = 2
23 AND operation_type = 2;
24
25 CURSOR Line_ops_cur IS
26 SELECT operation_sequence_id
27 FROM bom_operation_sequences
28 WHERE routing_sequence_id = arg_routing_sequence_id
29 AND NVL(eco_for_production,2) = 2
30 AND operation_type = 3;
31
32 CURSOR All_Events_cur(v_seq_id NUMBER) IS
33 SELECT operation_sequence_id, operation_seq_num
34 FROM bom_operation_sequences
35 WHERE operation_type = 1
36 AND (line_op_seq_id = v_seq_id OR process_op_seq_id = v_seq_id)
37 AND routing_sequence_id = arg_routing_sequence_id
38 AND effectivity_date <= sysdate
39 AND NVL(eco_for_production,2) = 2
40 AND nvl(disable_date, sysdate + 1) > sysdate;
41
42 CURSOR opt_class_comps_cur(v_bill_seq_id NUMBER) IS
43 SELECT component_item_id, planning_factor
44 FROM bom_inventory_components bic, bom_bill_of_materials bom
45 WHERE bom.bill_sequence_id = v_bill_seq_id
46 AND bom.common_bill_sequence_id = bic.bill_sequence_id
47 AND NVL(bic.eco_for_production,2) = 2
48 AND bic.bom_item_type = 2;
49
50 event_seq_id NUMBER;
51 process_seq_id NUMBER;
52 lineop_seq_id NUMBER;
53 var_machine_time NUMBER;
54 var_labor_time NUMBER;
55 var_elapsed_time NUMBER;
56 hour_conv NUMBER;
57 lot_qty NUMBER;
58 hour_uom VARCHAR2(3);
59
60 x_bom_id NUMBER;
61 x_alt_bom_desg VARCHAR2(10);
62 avg_machine_time NUMBER;
63 avg_labor_time NUMBER;
64 avg_elapsed_time NUMBER;
65 v_machine_time NUMBER;
66 v_labor_time NUMBER;
67 v_elapsed_time NUMBER;
68 v_planning_factor NUMBER;
69 opt_bill_id NUMBER;
70 assoc_flag NUMBER;
71
72 BEGIN
73
74 SELECT NVL(items.lead_time_lot_size, NVL(items.std_lot_size,1))
75 INTO lot_qty
76 FROM mtl_system_items items,
77 bom_operational_routings bor
78 WHERE items.organization_id = bor.organization_id
79 AND items.inventory_item_id = bor.assembly_item_id
80 AND bor.routing_sequence_id = arg_routing_sequence_id;
81
82 hour_uom := FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE');
83
84 SELECT conversion_rate
85 INTO hour_conv
86 FROM mtl_uom_conversions
87 WHERE uom_code = hour_uom
88 AND inventory_item_id = 0;
89
90 OPEN Events_cur;
91 LOOP
92 FETCH events_cur
93 INTO event_seq_id;
94
95 EXIT WHEN events_cur%NOTFOUND;
96
97 BEGIN
98 SELECT SUM(NVL(((bor.usage_rate_or_amount)*
99 DECODE(con.conversion_rate, '', 0,'0', 0,
100 con.conversion_rate)/
101 DECODE(NVL(br.default_basis_type, 1) , 2, lot_qty,
102 1, 1)), 0))/NVL(hour_conv, 1)
103 INTO var_machine_time
104 FROM bom_operation_sequences bos,
105 bom_operation_resources bor,
106 bom_department_resources bdr2,
107 bom_department_resources bdr1,
108 bom_resources br,
109 mtl_uom_conversions con
110 -- WHERE bor.schedule_flag <> 2
111 WHERE bor.resource_id = br.resource_id
112 AND br.resource_type = 1
113 AND NVL(br.disable_date, trunc(sysdate) + 1)
114 > trunc(sysdate)
115 AND bos.operation_sequence_id = event_seq_id
116 AND bos.operation_sequence_id = bor.operation_sequence_id
117 AND bos.department_id = bdr1.department_id
118 AND bor.resource_id = bdr1.resource_id
119 AND NVL(bdr1.share_from_dept_id, bdr1.department_id)
120 = bdr2.department_id
121 AND bor.resource_id = bdr2.resource_id
122 AND bor.resource_id = br.resource_id
123 AND con.uom_code (+) = br.unit_of_measure
124 AND con.inventory_item_id (+) = 0;
125 EXCEPTION WHEN NO_DATA_FOUND THEN
126 null;
127 END;
128
129
130 BEGIN
131 SELECT SUM(NVL(((bor.usage_rate_or_amount)*
132 DECODE(con.conversion_rate, '', 0,'0', 0,
133 con.conversion_rate)/
134 DECODE(NVL(br.default_basis_type, 1), 2, lot_qty,
135 1, 1)), 0))/NVL(hour_conv, 1)
136 INTO var_labor_time
137 FROM bom_operation_sequences bos,
138 bom_operation_resources bor,
139 bom_department_resources bdr2,
140 bom_department_resources bdr1,
141 bom_resources br,
142 mtl_uom_conversions con
143 -- WHERE bor.schedule_flag <> 2
144 WHERE bor.resource_id = br.resource_id
145 AND br.resource_type = 2
146 AND NVL(br.disable_date, trunc(sysdate) + 1)
147 > trunc(sysdate)
148 AND bos.operation_sequence_id = event_seq_id
149 AND bos.operation_sequence_id = bor.operation_sequence_id
150 AND bos.department_id = bdr1.department_id
151 AND bor.resource_id = bdr1.resource_id
152 AND NVL(bdr1.share_from_dept_id, bdr1.department_id)
153 = bdr2.department_id
154 AND bor.resource_id = bdr2.resource_id
155 AND bor.resource_id = br.resource_id
156 AND con.uom_code (+) = br.unit_of_measure
157 AND con.inventory_item_id (+) = 0;
158 EXCEPTION WHEN NO_DATA_FOUND THEN
159 null;
160 END;
161
162 BEGIN
163 SELECT SUM(NVL(((bor.usage_rate_or_amount)*
164 DECODE(con.conversion_rate, '', 0,'0', 0,
165 con.conversion_rate)/
166 DECODE(NVL(br.default_basis_type, 1), 2, lot_qty,
167 1, 1)), 0))/NVL(hour_conv, 1)
168 INTO var_elapsed_time
169 FROM bom_operation_sequences bos,
170 bom_operation_resources bor,
171 bom_department_resources bdr2,
172 bom_department_resources bdr1,
173 bom_resources br,
174 mtl_uom_conversions con
175 WHERE bor.schedule_flag <> 2
176 AND bor.resource_id = br.resource_id
177 AND br.resource_type IN (1, 2)
178 AND NVL(br.disable_date, trunc(sysdate) + 1)
179 > trunc(sysdate)
180 AND bos.operation_sequence_id = event_seq_id
181 AND bos.operation_sequence_id = bor.operation_sequence_id
182 AND bos.department_id = bdr1.department_id
183 AND bor.resource_id = bdr1.resource_id
184 AND NVL(bdr1.share_from_dept_id, bdr1.department_id)
185 = bdr2.department_id
186 AND bor.resource_id = bdr2.resource_id
187 AND bor.resource_id = br.resource_id
188 AND con.uom_code (+) = br.unit_of_measure
189 AND con.inventory_item_id (+) = 0;
190 EXCEPTION WHEN NO_DATA_FOUND THEN
191 null;
192 END;
193
194
195 UPDATE bom_operation_sequences
196 SET machine_time_calc = NVL(var_machine_time, 0),
197 labor_time_calc = NVL(var_labor_time, 0),
198 total_time_calc = NVL(var_elapsed_time, 0)
199 WHERE operation_sequence_id = event_seq_id;
200 --COMMIT;
201 END LOOP;
202
203 -------------------------------------------------------------------------------------------
204 -- get the BOM to see if there are any option class comps.
205 BEGIN
206 select bill_sequence_id, alternate_bom_designator
207 into x_bom_id, x_alt_bom_desg
208 from bom_bill_of_materials bom, bom_operational_routings bor
209 where routing_sequence_id = arg_routing_sequence_id
210 and bor.assembly_item_id = bom.assembly_item_id
211 and bor.organization_id = bom.organization_id
212 and nvl(bor.alternate_routing_designator, 'NONE')
213 = nvl(bom.alternate_bom_designator, 'NONE');
214 EXCEPTION WHEN NO_DATA_FOUND THEN
215 x_bom_id := -1;
216 END;
217
218 OPEN Processes_cur;
219 LOOP
220 FETCH Processes_cur
221 INTO process_seq_id;
222
223 EXIT WHEN Processes_cur%NOTFOUND;
224
225 BEGIN
226 SELECT SUM(NVL(machine_time_calc, 0)), SUM(NVL(labor_time_calc, 0)),
227 SUM(NVL(total_time_calc, 0))
228 INTO var_machine_time, var_labor_time, var_elapsed_time
229 FROM bom_operation_sequences
230 WHERE operation_type = 1
231 AND process_op_seq_id = process_seq_id
232 AND nvl(disable_date, sysdate + 1) > sysdate -- BUG #2836627
233 AND routing_sequence_id = arg_routing_sequence_id;
234 EXCEPTION WHEN NO_DATA_FOUND THEN
235 NULL;
236 END;
237
238 BEGIN
239 avg_machine_time := 0;
240 avg_labor_time := 0;
241 avg_elapsed_time := 0;
242
243 --open cursor for every event assoc with the process
244 FOR All_Events_cur_rec IN All_Events_cur(process_seq_id) LOOP
245
246 SELECT machine_time_calc, labor_time_calc, total_time_calc
247 INTO v_machine_time, v_labor_time, v_elapsed_time
248 FROM bom_operation_sequences
249 WHERE operation_sequence_id = All_Events_cur_rec.operation_sequence_id;
250
251 -- chk to see if process is associated with a non-OC component
252 -- if it is then, skip the chk OC loop
253 assoc_flag := 1;
254 BEGIN
255 SELECT NVL(planning_factor, 100)
256 INTO v_planning_factor
257 FROM bom_inventory_components
258 WHERE bill_sequence_id = x_bom_id
259 AND operation_seq_num = All_Events_cur_rec.operation_seq_num
260 AND NVL(eco_for_production,2) = 2
261 AND effectivity_date <= sysdate
262 AND nvl(disable_date, sysdate + 1) > sysdate
263 AND rownum = 1;
264 EXCEPTION WHEN NO_DATA_FOUND THEN
265 -- If there are no rows in the main table then, it means that there are rows
266 -- in the bom_component_operations table(1-many enhancement).
267 -- So we go there to get the component for the current operation and then
268 -- the planning factor for that component.
269 begin
270 SELECT NVL(bic.planning_factor, 100)
271 INTO v_planning_factor
272 FROM bom_inventory_components bic,
273 bom_component_operations bco
274 WHERE
275 bco.bill_sequence_id = x_bom_id
276 AND bco.operation_sequence_id =
277 All_Events_cur_rec.operation_sequence_id
278 AND bco.operation_seq_num =
279 All_Events_cur_rec.operation_seq_num
280 AND bco.component_sequence_id = bic.component_sequence_id
281 AND bic.bill_sequence_id = x_bom_id
282 AND NVL(bic.eco_for_production,2) = 2
283 AND bic.effectivity_date <= sysdate
284 AND nvl(bic.disable_date, sysdate + 1) > sysdate
285 AND rownum = 1;
286 EXCEPTION WHEN NO_DATA_FOUND THEN
287 assoc_flag := 0;
288 v_planning_factor := 100;
289 end;
290 END;
291
292 IF assoc_flag = 0 THEN
293 -- for every option class component in the BOM need to get to the OC bill
294 -- and search for the operation there
295
296 FOR opt_class_comps_rec IN opt_class_comps_cur(x_bom_id) LOOP
297 BEGIN
298 SELECT bill_sequence_id
299 INTO opt_bill_id
300 FROM bom_bill_of_materials bom
301 WHERE bom.assembly_item_id = opt_class_comps_rec.component_item_id
302 AND bom.organization_id = arg_org_id
303 AND NVL(bom.alternate_bom_designator, 'NONE')
304 = NVL(x_alt_bom_desg, 'NONE');
305
306 SELECT (NVL(planning_factor, 100)
307 * NVL(opt_class_comps_rec.planning_factor, 100))/100
308 INTO v_planning_factor
309 FROM bom_bill_of_materials bom, bom_inventory_components bic
310 WHERE bom.common_bill_sequence_id = opt_bill_id
311 AND bom.common_bill_sequence_id = bic.bill_sequence_id
312 AND bic.operation_seq_num = All_Events_cur_rec.operation_seq_num
313 AND NVL(bic.eco_for_production,2) = 2
314 AND rownum = 1;
315 EXCEPTION WHEN NO_DATA_FOUND THEN
316 -- 1-many enhancement for the options class as well!
317 begin
318 SELECT (NVL(planning_factor, 100)
319 * NVL(opt_class_comps_rec.planning_factor, 100))/100
320 INTO v_planning_factor
321 FROM bom_bill_of_materials bom,
322 bom_inventory_components bic,
323 bom_component_operations bco
324 --WHERE bom.common_bill_sequence_id = opt_bill_id -- BUG 5199596
325 WHERE bom.bill_sequence_id = opt_bill_id
326 AND bom.common_bill_sequence_id = bco.bill_sequence_id
327 AND bco.operation_sequence_id =
328 All_Events_cur_rec.operation_sequence_id
329 AND bco.operation_seq_num =
330 All_Events_cur_rec.operation_seq_num
331 AND bco.component_sequence_id = bic.component_sequence_id
332 AND bom.common_bill_sequence_id = bic.bill_sequence_id
333 AND NVL(bic.eco_for_production,2) = 2
334 AND rownum = 1;
335 EXCEPTION WHEN NO_DATA_FOUND THEN
336 null;
337 END;
338 END;
339
340 END LOOP;
341 end if;
342 -- calculate the operations times
343 avg_machine_time := avg_machine_time
344 + (v_machine_time * v_planning_factor/100);
345 avg_labor_time := avg_labor_time
346 + (v_labor_time * v_planning_factor/100);
347 avg_elapsed_time := avg_elapsed_time
348 + (v_elapsed_time * v_planning_factor/100);
349 END LOOP;
350
351 EXCEPTION WHEN NO_DATA_FOUND THEN
352 NULL;
353 END;
354
355 IF (avg_machine_time <> 0
356 OR avg_labor_time <> 0 OR avg_elapsed_time <> 0) THEN
357 var_machine_time := avg_machine_time;
358 var_labor_time := avg_labor_time;
359 var_elapsed_time := avg_elapsed_time;
360 END IF;
361
362 UPDATE bom_operation_sequences
363 SET machine_time_calc = NVL(var_machine_time, 0),
364 labor_time_calc = NVL(var_labor_time, 0),
365 total_time_calc = NVL(var_elapsed_time, 0)
366 WHERE operation_sequence_id = process_seq_id;
367
368 --COMMIT;
369 END LOOP;
370
371 -------------------------------------------------------------------------------------------
372
373 OPEN Line_ops_cur;
374 LOOP
375 FETCH Line_ops_cur
376 INTO lineop_seq_id;
377
378 EXIT WHEN Line_ops_cur%NOTFOUND;
379
380 BEGIN
381 SELECT SUM(NVL(machine_time_calc, 0)), SUM(NVL(labor_time_calc, 0)),
382 SUM(NVL(total_time_calc, 0))
383 INTO var_machine_time, var_labor_time, var_elapsed_time
387 AND nvl(disable_date, sysdate + 1) > sysdate -- BUG #2836627
384 FROM bom_operation_sequences
385 WHERE operation_type = 1
386 AND line_op_seq_id = lineop_seq_id
388 AND routing_sequence_id = arg_routing_sequence_id;
389 EXCEPTION WHEN NO_DATA_FOUND THEN
390 NULL;
391 END;
392
393
394 BEGIN
395 avg_machine_time := 0;
396 avg_labor_time := 0;
397 avg_elapsed_time := 0;
398
399 --open cursor for every event assoc with the line-op
400 FOR All_Events_cur_rec IN All_Events_cur(lineop_seq_id) LOOP
401
402 SELECT machine_time_calc, labor_time_calc, total_time_calc
403 INTO v_machine_time, v_labor_time, v_elapsed_time
404 FROM bom_operation_sequences
405 WHERE operation_sequence_id = All_Events_cur_rec.operation_sequence_id;
406
407 -- chk to see if event is associated with a non-OC component
408 -- (or maybe not associated at all)
409 -- if it is then, skip the chk OC loop
410 assoc_flag := 1;
411 BEGIN
412 SELECT nvl(planning_factor, 100)
413 INTO v_planning_factor
414 FROM bom_inventory_components
415 WHERE bill_sequence_id = x_bom_id
416 AND operation_seq_num = All_Events_cur_rec.operation_seq_num
417 AND effectivity_date <= sysdate
418 AND nvl(disable_date, sysdate + 1) > sysdate
419 AND NVL(eco_for_production,2) = 2
420 AND rownum = 1;
421 EXCEPTION WHEN NO_DATA_FOUND THEN
422 -- 1-many enhancement as for the process
423 begin
424 SELECT NVL(bic.planning_factor, 100)
425 INTO v_planning_factor
426 FROM bom_inventory_components bic,
427 bom_component_operations bco
428 WHERE
429 bco.bill_sequence_id = x_bom_id
430 AND bco.operation_sequence_id =
431 All_Events_cur_rec.operation_sequence_id
432 AND bco.operation_seq_num =
433 All_Events_cur_rec.operation_seq_num
434 AND bco.component_sequence_id = bic.component_sequence_id
435 AND bic.bill_sequence_id = x_bom_id
436 AND NVL(bic.eco_for_production,2) = 2
437 AND bic.effectivity_date <= sysdate
438 AND nvl(bic.disable_date, sysdate + 1) > sysdate
439 AND rownum = 1;
440 EXCEPTION WHEN NO_DATA_FOUND THEN
441 assoc_flag := 0;
442 v_planning_factor := 100;
443 end;
444 END;
445
446 IF assoc_flag = 0 THEN
447 -- for every option class component in the BOM need to get to the OC bill
448 -- and search for the operation there
449
450 FOR opt_class_comps_rec IN opt_class_comps_cur(x_bom_id) LOOP
451 BEGIN
452 SELECT bill_sequence_id
453 INTO opt_bill_id
454 FROM bom_bill_of_materials bom
455 WHERE bom.assembly_item_id = opt_class_comps_rec.component_item_id
456 AND bom.organization_id = arg_org_id
457 AND NVL(bom.alternate_bom_designator, 'NONE')
458 = NVL(x_alt_bom_desg, 'NONE');
459
460 SELECT (nvl(planning_factor, 100)
461 * nvl(opt_class_comps_rec.planning_factor, 100))/100
462 INTO v_planning_factor
463 FROM bom_bill_of_materials bom, bom_inventory_components bic
464 WHERE bom.common_bill_sequence_id = opt_bill_id
465 AND bom.common_bill_sequence_id = bic.bill_sequence_id
466 AND bic.operation_seq_num = All_Events_cur_rec.operation_seq_num
467 AND NVL(bic.eco_for_production,2) = 2
468 AND rownum = 1;
469
470 EXCEPTION WHEN NO_DATA_FOUND THEN
471 -- 1-many enhancement as for the process option class
472 begin
473 SELECT (NVL(planning_factor, 100)
474 * NVL(opt_class_comps_rec.planning_factor, 100))/100
475 INTO v_planning_factor
476 FROM bom_bill_of_materials bom,
477 bom_inventory_components bic,
478 bom_component_operations bco
479 --WHERE bom.common_bill_sequence_id = opt_bill_id -- BUG 5199596
480 WHERE bom.bill_sequence_id = opt_bill_id
481 AND bom.common_bill_sequence_id = bco.bill_sequence_id
482 AND bco.operation_sequence_id =
483 All_Events_cur_rec.operation_sequence_id
484 AND bco.operation_seq_num =
485 All_Events_cur_rec.operation_seq_num
486 AND bco.component_sequence_id = bic.component_sequence_id
487 AND bom.common_bill_sequence_id = bic.bill_sequence_id
488 AND NVL(bic.eco_for_production,2) = 2
489 AND rownum = 1;
490 EXCEPTION WHEN NO_DATA_FOUND THEN
491 null;
492 END;
493 END;
494 END LOOP;
495 END IF;
496 -- calculate the operations times
497 avg_machine_time := avg_machine_time
498 + (v_machine_time * v_planning_factor/100);
502 + (v_elapsed_time * v_planning_factor/100);
499 avg_labor_time := avg_labor_time
500 + (v_labor_time * v_planning_factor/100);
501 avg_elapsed_time := avg_elapsed_time
503 END LOOP;
504
505 EXCEPTION WHEN NO_DATA_FOUND THEN
506 NULL;
507 END;
508 IF (avg_machine_time <> 0
509 OR avg_labor_time <> 0 OR avg_elapsed_time <> 0) THEN
510 var_machine_time := avg_machine_time;
511 var_labor_time := avg_labor_time;
512 var_elapsed_time := avg_elapsed_time;
513 END IF;
514
515 UPDATE bom_operation_sequences
516 SET machine_time_calc = NVL(var_machine_time, 0),
517 labor_time_calc = NVL(var_labor_time, 0),
518 total_time_calc = NVL(var_elapsed_time, 0)
519 WHERE operation_sequence_id = lineop_seq_id;
520
521 --COMMIT;
522 END LOOP;
523 END;
524
525 END BOM_CALC_OP_TIMES_PK;