[Home] [Help]
PACKAGE BODY: APPS.FLM_MMM_CALCULATION
Source
1 PACKAGE BODY flm_mmm_calculation AS
2 /* $Header: FLMMMMCB.pls 120.2.12000000.2 2007/02/26 19:52:51 ksuleman ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : FLMMMMCB.pls |
9 | DESCRIPTION : This package contains functions used to calculate values |
10 | for the New Mixed Model Map Form |
11 | Coders : Liye Ma (01/09/02 - 03/30/02) |
12 | Hadi Wenas (04/01/02 - present ) |
13 | Navin Rajpal |
14 +===========================================================================*/
15
16
17
18 /************************************************************************
19 * Package variables *
20 ************************************************************************/
21 G_DEBUG BOOLEAN := (FND_PROFILE.VALUE('MRP_DEBUG') = 'Y');
22
23 /************************************************************************
24 * Private Procedures and Functions *
25 ************************************************************************/
26
27 /************************************************************************
28 * PROCEDURE print_log *
29 * Inserts debug msg into log file. *
30 * *
31 ************************************************************************/
32 PROCEDURE print_log (buf VARCHAR2) IS
33 BEGIN
34 FND_FILE.PUT_LINE(FND_FILE.LOG, buf);
35 END;
36
37
38 /************************************************************************
39 * PROCEDURE calculate_avg_daily_demand *
40 * Calculates and returns average daily demand based on input. *
41 ************************************************************************/
42 PROCEDURE calculate_avg_daily_demand(
43 i_plan_id IN NUMBER,
44 i_organization_id IN NUMBER,
45 i_product_family_id IN NUMBER,
46 i_line_id IN NUMBER,
47 i_demand_type IN NUMBER,
48 i_demand_code IN VARCHAR2,
49 i_start_date IN DATE,
50 i_end_date IN DATE,
51 i_demand_days IN NUMBER,
52 i_boost_percent IN NUMBER,
53 i_calendar_code IN VARCHAR2,
54 i_exception_set_id IN NUMBER,
55 i_last_calendar_date IN DATE,
56 o_demand OUT NOCOPY t_demand_table) IS
57
58 l_demand NUMBER; -- Average Daily Demand
59 l_line_id NUMBER;
60 l_dummy NUMBER;
61 l_info VARCHAR2(1000);
62 l_order_option NUMBER; -- Sales/Planned Order Option
63 l_demand_days NUMBER; -- Demand days on a particular line
64 l_product_family_id NUMBER; -- Product Family Id
65
66 -- cursor to retrieve forecast entries
67 CURSOR forecast_entries IS
68 SELECT DISTINCT inventory_item_id item_id
69 FROM mrp_forecast_dates
70 WHERE organization_id = i_organization_id
71 AND forecast_designator = i_demand_code
72 AND ((rate_end_date IS NULL
73 AND
74 forecast_date BETWEEN get_offset_date(i_organization_id,
75 i_start_date,
76 bucket_type )
77 AND i_end_date
78 ) OR
79 (rate_end_date is NOT NULL
80 AND
81 NOT (rate_end_date < get_offset_date(i_organization_id,
82 i_start_date,
83 bucket_type )
84 OR
85 forecast_date > i_end_date
86 )
87 )
88 );
89
90 -- cursor to retrieve MPS/MDS entries
91 CURSOR schedule_entries IS
92 SELECT DISTINCT
93 inventory_item_id item_id,
94 line_id
95 FROM mrp_schedule_dates
96 WHERE organization_id = i_organization_id
97 AND schedule_designator = i_demand_code
98 AND schedule_level = 2
99 AND trunc(schedule_date) BETWEEN i_start_date AND i_end_date;
100
101 -- cursor to retrieve actual production
102 CURSOR actual_production_entries IS
103 /* avoid using this view, it's non-mergable */
104 /* SELECT DISTINCT
105 primary_item_id item_id,
106 line_id
107 FROM mrp_line_sch_avail_v
108 WHERE organization_id = i_organization_id
109 AND trunc(scheduled_completion_date) between i_start_date AND i_end_date;
110 */
111 SELECT distinct PRIMARY_ITEM_ID ITEM_ID, LINE_ID
112 FROM
113 (
114 SELECT
115 REPITEM.PRIMARY_ITEM_ID,
116 REP.LINE_ID
117 FROM BOM_CALENDAR_DATES BOM,
118 WIP_REPETITIVE_ITEMS REPITEM,
119 WIP_REPETITIVE_SCHEDULES REP,
120 MTL_PARAMETERS MP
121 WHERE BOM.CALENDAR_DATE BETWEEN TRUNC(REP.FIRST_UNIT_COMPLETION_DATE) AND
122 TRUNC(REP.LAST_UNIT_COMPLETION_DATE)
123 AND BOM.SEQ_NUM IS NOT NULL
124 AND MP.CALENDAR_CODE = BOM.CALENDAR_CODE
125 AND MP.CALENDAR_EXCEPTION_SET_ID = BOM.EXCEPTION_SET_ID
126 AND MP.ORGANIZATION_ID = REP.ORGANIZATION_ID
127 AND REPITEM.LINE_ID = REP.LINE_ID
128 AND REPITEM.WIP_ENTITY_ID = REP.WIP_ENTITY_ID
129 AND REPITEM.ORGANIZATION_ID = REP.ORGANIZATION_ID
130 AND REPITEM.LINE_ID IS NOT NULL
131 AND REP.organization_id = i_organization_id
132 and trunc(BOM.CALENDAR_DATE) between i_start_date AND i_end_date
133 UNION ALL
134 SELECT
135 JOB.PRIMARY_ITEM_ID,
136 JOB.LINE_ID
137 FROM WIP_DISCRETE_JOBS JOB
138 WHERE JOB.LINE_ID IS NOT NULL
139 AND JOB.organization_id = i_organization_id
140 and trunc(JOB.scheduled_completion_date) between i_start_date AND i_end_date
141 UNION ALL
142 SELECT
143 FLOW.PRIMARY_ITEM_ID,
144 FLOW.LINE_ID
145 FROM WIP_FLOW_SCHEDULES FLOW
146 WHERE FLOW.LINE_ID IS NOT NULL
147 AND FLOW.organization_id = i_organization_id
148 and trunc(FLOW.scheduled_completion_date) between i_start_date AND i_end_date
149 );
150
151
152 -- cursor to retrieve sales orders and planned orders
153 /* performance bug 14597218 - try avoid using view mrp_unscheduled_orders_v */
154 /* use two more cursors instead*/
155 /* CURSOR order_entries(i_option IN NUMBER) IS
156 SELECT DISTINCT
157 inventory_item_id item_id,
158 line_id
159 FROM mrp_unscheduled_orders_v
160 WHERE organization_id = i_organization_id
161 AND trunc(order_date) BETWEEN i_start_date AND i_end_date
162 AND unscheduled_order_option = i_option;
163 */
164
165 CURSOR order_entries(i_option IN NUMBER) IS
166 SELECT null item_id, null line_id from dual;
167 so_po_rec order_entries%rowtype;
168
169 CURSOR so_order_entries IS
170 SELECT distinct
171 sl1.inventory_item_id item_id,
172 wl.line_id
173 FROM
174 OE_ORDER_LINES_ALL SL1,
175 MTL_SYSTEM_ITEMS_KFV MSI1,
176 WIP_LINES WL,
177 (select sl2.line_id,
178 decode((select 1
179 from oe_order_holds_all oh
180 where oh.header_id = sl2.header_id
181 and rownum = 1
182 and oh.released_flag = 'N'),
183 null,
184 0,
185 decode(sl2.ato_line_id,
186 null,
187 mrp_flow_schedule_util.check_holds(sl2.header_id,
188 sl2.line_id,
189 'OEOL',
190 'LINE_SCHEDULING'),
191 mrp_flow_schedule_util.check_holds(sl2.header_id,
192 sl2.line_id,
193 null,
194 null))) hold
195 from oe_order_lines_all sl2) line_holds,
196 (select sl2.line_id,
197 CTO_WIP_WORKFLOW_API_PK.workflow_build_status(sl2.LINE_ID) status
198 from oe_order_lines_all sl2) line_build
199 WHERE
200 line_build.line_id = sl1.line_id
201 AND 1 = decode(MSI1.REPLENISH_TO_ORDER_FLAG, 'N', 1, line_build.status)
202 AND MSI1.BUILD_IN_WIP_FLAG = 'Y'
203 AND MSI1.PICK_COMPONENTS_FLAG = 'N'
204 AND MSI1.BOM_ITEM_TYPE = 4
205 AND MSI1.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
206 AND MSI1.INVENTORY_ITEM_ID = SL1.INVENTORY_ITEM_ID
207 AND SL1.ORDERED_QUANTITY > 0
208 AND SL1.VISIBLE_DEMAND_FLAG = 'Y'
209 AND SL1.OPEN_FLAG = 'Y'
210 AND SL1.ITEM_TYPE_CODE in ('STANDARD', 'CONFIG', 'INCLUDED', 'OPTION')
211 AND OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
212 AND wl.organization_id = sl1.ship_from_org_id
213 AND wl.line_id in (select line_id
214 from bom_operational_routings bor2
215 where bor2.assembly_item_id = sl1.inventory_item_id
216 and bor2.organization_id = sl1.ship_from_org_id
217 and bor2.cfm_routing_flag = 1)
218 AND SL1.SHIPPED_QUANTITY is NULL
219 and sl1.line_id = line_holds.line_id
220 and line_holds.hold = 0
221 AND NVL(SL1.FULFILLED_FLAG, 'N') <> 'Y'
222 /* cursor specific criteria */
223 AND msi1.organization_id = i_organization_id
224 AND trunc(sl1.schedule_ship_date) BETWEEN i_start_date AND i_end_date;
225
226 CURSOR po_order_entries IS
227 SELECT distinct
228 MR1.INVENTORY_ITEM_ID item_id,
229 WL.LINE_ID
230 FROM MTL_SYSTEM_ITEMS_B KFV,
231 MRP_SYSTEM_ITEMS RSI1,
232 MRP_PLANS MP1,
233 MRP_RECOMMENDATIONS MR1,
234 WIP_LINES WL
235 WHERE MP1.PLAN_COMPLETION_DATE IS NOT NULL
236 AND MP1.DATA_COMPLETION_DATE IS NOT NULL
237 AND MP1.COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
238 AND (MP1.ORGANIZATION_ID = MR1.ORGANIZATION_ID OR
239 (MP1.ORGANIZATION_ID IN
240 (SELECT ORGANIZATION_ID
241 FROM MRP_PLAN_ORGANIZATIONS
242 WHERE COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
243 AND PLANNED_ORGANIZATION = MR1.ORGANIZATION_ID)))
244 AND MR1.ORGANIZATION_ID = MR1.SOURCE_ORGANIZATION_ID
245 AND KFV.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
246 AND KFV.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
247 AND NVL(KFV.RELEASE_TIME_FENCE_CODE, -1) <> 6 /* KANBAN ITEM */
248 AND MR1.ORDER_TYPE = 5 /* PLANNED ORDER */
249 AND MR1.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
250 AND MR1.COMPILE_DESIGNATOR = RSI1.COMPILE_DESIGNATOR
251 AND MR1.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
252 AND MR1.COMPILE_DESIGNATOR =
253 /* (SELECT DESIGNATOR
254 FROM MRP_DESIGNATORS_VIEW
255 WHERE PRODUCTION = 1
256 AND ORGANIZATION_ID = MP1.ORGANIZATION_ID
257 AND DESIGNATOR = MR1.COMPILE_DESIGNATOR) */ /* bug 4911869 - flatten view mrp_designators_view */
258 ( SELECT S.SCHEDULE_DESIGNATOR FROM MRP_SCHEDULE_DESIGNATORS S
259 WHERE s.production = 1 and s.organization_id = mp1.organization_id
260 and s.schedule_designator = mr1.compile_designator
261 UNION ALL
262 SELECT D.COMPILE_DESIGNATOR
263 FROM MRP_DESIGNATORS D
264 WHERE d.production = 1 and d.organization_id = mp1.organization_id
265 and d.COMPILE_DESIGNATOR = mr1.compile_designator)
266 AND RSI1.BUILD_IN_WIP_FLAG = 1 /* YES */
267 AND RSI1.BOM_ITEM_TYPE = 4
268 AND (RSI1.IN_SOURCE_PLAN = 2 OR RSI1.IN_SOURCE_PLAN IS NULL)
269 AND wl.organization_id = MR1.ORGANIZATION_ID
270 AND wl.line_id in (select line_id
271 from bom_operational_routings bor2
272 where bor2.assembly_item_id = MR1.INVENTORY_ITEM_ID
273 and bor2.organization_id = MR1.ORGANIZATION_ID
274 and bor2.cfm_routing_flag = 1)
275 /* cursor specific criteria */
276 AND MR1.organization_id = i_organization_id
277 AND trunc(NVL(MR1.FIRM_DATE, MR1.NEW_SCHEDULE_DATE))
278 BETWEEN i_start_date AND i_end_date;
279
280
281 -- cursor to retrieve line_id for given item_id
282 CURSOR line(i_assembly_item_id IN NUMBER) IS
283 SELECT distinct line_id
284 FROM bom_operational_routings
285 WHERE organization_id = i_organization_id
286 AND assembly_item_id = i_assembly_item_id
287 AND cfm_routing_flag = 1
288 AND mixed_model_map_flag = 1;
289
290 -- cursor to retrieve demand days on a line, in case of Actual Production
291 CURSOR schedule_days(i_line_id IN NUMBER) IS
292 /* This query has performance issue: Shared Memory Size > 1MB
293 SELECT count(distinct(to_char(scheduled_completion_date))) num_days
294 FROM mrp_line_schedules_v
295 WHERE organization_id = i_organization_id
296 AND line_id = i_line_id
297 AND trunc(scheduled_completion_date) BETWEEN i_start_date AND i_end_date;
298 */
299 --fix bug#3293206:
300 -- change sum(num_days) to max(num_days)
301 -- add MTL_PARAMETERS criteria when querying BOM_CALENDAR_DATES
302 SELECT max(num_days)
303 FROM (
304 SELECT count(distinct(to_char(BOM.calendar_Date))) num_days
305 FROM BOM_CALENDAR_DATES BOM,
306 WIP_REPETITIVE_SCHEDULES REP,
307 MTL_PARAMETERS MP
308 WHERE REP.line_id =i_line_id
309 AND BOM.CALENDAR_DATE BETWEEN TRUNC(REP.FIRST_UNIT_COMPLETION_DATE)
310 AND TRUNC(REP.LAST_UNIT_COMPLETION_DATE)
311 AND BOM.SEQ_NUM IS NOT NULL
312 AND REP.organization_id = i_organization_id
313 AND BOM.calendar_date BETWEEN i_start_date
314 AND i_end_date+1-(1/86400)
315 AND MP.CALENDAR_CODE = BOM.CALENDAR_CODE
316 AND MP.CALENDAR_EXCEPTION_SET_ID = BOM.EXCEPTION_SET_ID
317 AND MP.ORGANIZATION_ID = REP.ORGANIZATION_ID
318 UNION ALL
319 SELECT count(distinct(to_char(JOB.scheduled_completion_date))) num_days
320 FROM WIP_DISCRETE_JOBS JOB
321 WHERE JOB.organization_id = i_organization_id
322 AND JOB.line_id = i_line_id
323 AND JOB.scheduled_completion_date BETWEEN i_start_date
324 AND i_end_date+1-(1/86400)
325 UNION ALL
326 SELECT count(distinct(to_char(flow.scheduled_completion_date))) num_days
327 FROM WIP_FLOW_SCHEDULES FLOW,
328 WIP_LINES LINE
329 WHERE FLOW.LINE_ID = LINE.LINE_ID
330 AND FLOW.ORGANIZATION_ID = LINE.ORGANIZATION_ID
331 AND FLOW.organization_id = i_organization_id
332 AND FLOW.line_id = i_line_id
333 AND FLOW.scheduled_completion_date BETWEEN i_start_date
334 AND i_end_date+1-(1/86400)
335 );
336
337 -- cursor to retrieve demand days on a line, in case of Sales and Planned Orders
338 CURSOR unschedule_days(i_line_id IN NUMBER, i_option IN NUMBER) IS
339 SELECT count(distinct(to_char(order_date))) num_days
340 FROM mrp_unscheduled_orders_v
341 WHERE organization_id = i_organization_id
342 AND line_id = i_line_id
343 AND trunc(order_date) BETWEEN i_start_date AND i_end_date
344 AND unscheduled_order_option = i_option;
345
346 -- cursor to retrieve product_family_item_id for given item_id
347 CURSOR product_family(i_inventory_item_id IN NUMBER) IS
348 SELECT product_family_item_id
349 FROM mtl_system_items_b
350 WHERE organization_id = i_organization_id
351 AND inventory_item_id = i_inventory_item_id;
352
353 BEGIN
354 -- retrieve AND calculate average daily demand for each relevant item
355 -- put into the demand table and return it
356 o_demand.DELETE;
357
358 IF i_demand_type = C_DEMAND_TYPE_FORECAST THEN
359 FOR forecast_rec IN forecast_entries LOOP
360 OPEN line(forecast_rec.item_id);
361 l_line_id := NULL;
362 FETCH line INTO l_line_id;
363 CLOSE line;
364
365 OPEN product_family(forecast_rec.item_id);
366 l_product_family_id := NULL;
367 FETCH product_family INTO l_product_family_id;
368 CLOSE product_family;
369
370 IF G_DEBUG THEN
371 l_info := 'item: '||forecast_rec.item_id||' line: '||l_line_id ||
372 'product family: '||l_product_family_id;
373 print_log(l_info);
374 END IF;
375
376 IF l_line_id IS NOT NULL AND
377 (l_line_id = i_line_id OR i_line_id IS NULL) AND
378 (i_product_family_id IS NULL OR
379 l_product_family_id = i_product_family_id) THEN
380
381 BOM_MIXED_MODEL_MAP_PVT.getdemand(
382 i_organization_id,
383 i_demand_type,
384 l_line_id,
385 forecast_rec.item_id,
386 i_calendar_code,
387 i_start_date,
388 i_end_date,
389 i_last_calendar_date,
390 i_exception_set_id,
391 i_demand_code,
392 l_demand,
393 l_dummy);
394
395 IF G_DEBUG THEN
396 l_info := 'total demand: '||l_demand;
397 print_log(l_info);
398 END IF;
399
400 IF l_demand > 0 THEN
401 l_demand := l_demand*(100+nvl(i_boost_percent,0))/100/i_demand_days;
402
403 o_demand(forecast_rec.item_id).assembly_item_id := forecast_rec.item_id;
404 o_demand(forecast_rec.item_id).line_id := l_line_id;
405 o_demand(forecast_rec.item_id).average_daily_demand := l_demand;
406
407 END IF;
408 END IF;
409 END LOOP;
410
411 ELSIF i_demand_type IN (C_DEMAND_TYPE_MDS, C_DEMAND_TYPE_MPS) THEN
412 FOR mds_mps_rec IN schedule_entries LOOP
413 OPEN line(mds_mps_rec.item_id);
414 l_line_id := NULL;
415 FETCH line INTO l_line_id;
416 CLOSE line;
417
418 OPEN product_family(mds_mps_rec.item_id);
419 l_product_family_id := NULL;
420 FETCH product_family INTO l_product_family_id;
421 CLOSE product_family;
422
423 IF l_line_id IS NOT NULL AND
424 (l_line_id = i_line_id OR i_line_id IS NULL) AND
425 (l_line_id = mds_mps_rec.line_id OR mds_mps_rec.line_id IS NULL) AND
426 (i_product_family_id IS NULL OR
427 l_product_family_id = i_product_family_id) THEN
428 BOM_MIXED_MODEL_MAP_PVT.getdemand (
429 i_organization_id,
430 i_demand_type,
431 l_line_id,
432 mds_mps_rec.item_id,
433 i_calendar_code,
434 i_start_date,
435 i_end_date,
436 i_last_calendar_date,
437 i_exception_set_id,
438 i_demand_code,
439 l_demand,
440 l_dummy);
441
442 IF l_demand > 0 THEN
443 -- To consider boost percent and demand days while calculating avg daily demand
444 l_demand := l_demand * (100 + nvl(i_boost_percent, 0)) / 100;
445 IF (i_demand_days <> 0) THEN
446 l_demand := l_demand / i_demand_days;
447 END IF;
448
449 o_demand(mds_mps_rec.item_id).assembly_item_id := mds_mps_rec.item_id;
450 o_demand(mds_mps_rec.item_id).line_id := l_line_id;
451 o_demand(mds_mps_rec.item_id).average_daily_demand := l_demand;
452
453 END IF;
454 END IF;
455 END LOOP;
456
457 ELSIF i_demand_type = C_DEMAND_TYPE_AP THEN
458
459 FOR ap_rec IN actual_production_entries LOOP
460 OPEN line(ap_rec.item_id);
461 l_line_id := NULL;
462 FETCH line INTO l_line_id;
463 CLOSE line;
464
465 OPEN product_family(ap_rec.item_id);
466 l_product_family_id := NULL;
467 FETCH product_family INTO l_product_family_id;
468 CLOSE product_family;
469
470 IF l_line_id IS NOT NULL AND
471 (l_line_id = i_line_id OR i_line_id IS NULL) AND
472 l_line_id = ap_rec.line_id AND
473 (i_product_family_id IS NULL OR
474 l_product_family_id = i_product_family_id) THEN
475 BOM_MIXED_MODEL_MAP_PVT.getdemand (
476 i_organization_id,
477 i_demand_type,
478 l_line_id,
479 ap_rec.item_id,
480 i_calendar_code,
481 i_start_date,
482 i_end_date,
483 i_last_calendar_date,
484 i_exception_set_id,
485 i_demand_code,
486 l_demand,
487 l_dummy);
488
489 IF l_demand > 0 THEN
490 -- To consider boost percent and demand days while calculating avg daily demand
491 l_demand := l_demand * (100 + nvl(i_boost_percent, 0)) / 100;
492
493 OPEN schedule_days(l_line_id);
494 FETCH schedule_days INTO l_demand_days;
495 CLOSE schedule_days;
496
497 IF (l_demand_days <> 0) THEN
498 l_demand := l_demand / l_demand_days;
499 END IF;
500
501 o_demand(ap_rec.item_id).assembly_item_id := ap_rec.item_id;
502 o_demand(ap_rec.item_id).line_id := l_line_id;
503 o_demand(ap_rec.item_id).average_daily_demand := l_demand;
504
505 END IF;
506 END IF;
507 END LOOP;
508
509 ELSIF i_demand_type IN (C_DEMAND_TYPE_SO, C_DEMAND_TYPE_PO) THEN
510
511 IF i_demand_type = C_DEMAND_TYPE_SO THEN
512 l_order_option := 1;
513 open so_order_entries;
514 ELSE
515 l_order_option := 2;
516 open po_order_entries;
517 END IF;
518
519 /* convert for to simple loop */
520 /* FOR so_po_rec IN order_entries(l_order_option) LOOP */
521 loop
522 if( l_order_option = 1 ) then
523 fetch so_order_entries into so_po_rec;
524 exit when so_order_entries%NOTFOUND;
525 else
526 fetch po_order_entries into so_po_rec;
527 exit when po_order_entries%NOTFOUND;
528 end if;
529
530 OPEN line(so_po_rec.item_id);
531 l_line_id := NULL;
532 FETCH line INTO l_line_id;
533 CLOSE line;
534
535 OPEN product_family(so_po_rec.item_id);
536 l_product_family_id := NULL;
537 FETCH product_family INTO l_product_family_id;
538 CLOSE product_family;
539
540 IF l_line_id IS NOT NULL AND
541 (l_line_id = i_line_id OR i_line_id IS NULL) AND
542 l_line_id = so_po_rec.line_id AND
543 (i_product_family_id IS NULL OR
544 l_product_family_id = i_product_family_id) THEN
545 BOM_MIXED_MODEL_MAP_PVT.getdemand (
546 i_organization_id,
547 i_demand_type,
548 l_line_id,
549 so_po_rec.item_id,
550 i_calendar_code,
551 i_start_date,
552 i_end_date,
553 i_last_calendar_date,
554 i_exception_set_id,
555 i_demand_code,
556 l_demand,
557 l_dummy);
558
559 IF l_demand > 0 THEN
560 l_demand := l_demand * (100 + nvl(i_boost_percent, 0)) / 100;
561
562 OPEN unschedule_days(l_line_id, l_order_option);
563 FETCH unschedule_days INTO l_demand_days;
564 CLOSE unschedule_days;
565
566 IF (l_demand_days <> 0) THEN
567 l_demand := l_demand / l_demand_days;
568 END IF;
569
570 o_demand(so_po_rec.item_id).assembly_item_id := so_po_rec.item_id;
571 o_demand(so_po_rec.item_id).line_id := l_line_id;
572 o_demand(so_po_rec.item_id).average_daily_demand := l_demand;
573
574 END IF;
575 END IF;
576 END LOOP;
577
578 if( l_order_option = 1 ) then
579 close so_order_entries;
580 else
581 close po_order_entries;
582 end if;
583
584 END IF; -- demand type
585
586 IF o_demand.COUNT > 0 THEN
587 l_dummy := o_demand.first;
588
589 LOOP
590 IF G_DEBUG THEN
591 l_info := 'item id: ' || o_demand(l_dummy).assembly_item_id ||
592 ' line id: '|| o_demand(l_dummy).line_id ||
593 ' demand: ' || o_demand(l_dummy).average_daily_demand;
594 print_log(l_info);
595 -- debug_log(l_info); -- change info col to hold more chars
596 END IF;
597
598 EXIT WHEN l_dummy = o_demand.last;
599 l_dummy := o_demand.next(l_dummy);
600
601 END LOOP;
602
603 ELSE
604 IF G_DEBUG THEN
605 print_log('no demand');
606 END IF;
607 END IF;
608
609 /* o_demand(1).assembly_item_id := 315; -- MC97160
610 o_demand(1).line_id := 201;
611 o_demand(1).average_daily_demand := 40;
612 o_demand(2).assembly_item_id := 173; -- MC31749
613 o_demand(2).line_id := 202;
614 o_demand(2).average_daily_demand := 23;
615 o_demand(3).assembly_item_id := 950; -- MC31750
616 o_demand(3).line_id := 202;
617 o_demand(3).average_daily_demand := 51;
618 */
619 IF G_DEBUG THEN
620 print_log('calculate retrieve demand');
621 END IF;
622
623 END calculate_avg_daily_demand;
624
625
626 /************************************************************************
627 * PROCEDURE calculate_line_takt *
628 * Calculates the Line TAKT for each relevant lines and *
629 * saves it in FLM_MMM_LINES. *
630 ************************************************************************/
631 PROCEDURE calculate_line_takt(
632 i_plan_id IN NUMBER,
633 i_organization_id IN NUMBER,
634 i_calculation_operation_type IN NUMBER,
635 i_line_id IN NUMBER,
636 i_hours_per_day IN NUMBER,
637 i_replan_flag IN VARCHAR2,
638 i_demand IN t_demand_table) IS
639
640 TYPE t_line_rec IS RECORD (
641 line_id NUMBER,
642 hours_per_day NUMBER,
643 total_demand NUMBER,
644 line_takt NUMBER);
645
646 TYPE t_line_table IS TABLE OF t_line_rec
647 INDEX BY BINARY_INTEGER;
648
649 CURSOR all_lines IS
650 SELECT line_id,
651 hours_per_day
652 FROM flm_mmm_lines
653 WHERE plan_id = i_plan_id
654 AND organization_id = i_organization_id
655 AND calculation_operation_type = i_calculation_operation_type;
656
657 l_index NUMBER;
658 l_lines t_line_table;
659 l_old_lines t_line_table;
660
661 --fix bug#3773193
662 l_start_time NUMBER;
663 l_stop_time NUMBER;
664 --end of fix bug#3773193
665
666 BEGIN
667 -- if replan, record line_hours history for this plan
668 -- then clear the historical records
669 IF i_replan_flag = C_REPLAN_FLAG_YES THEN
670 l_old_lines.DELETE;
671 FOR line_rec IN all_lines LOOP
672 l_old_lines(line_rec.line_id).line_id := line_rec.line_id;
673 l_old_lines(line_rec.line_id).hours_per_day := line_rec.hours_per_day;
674 END LOOP;
675
676 DELETE FROM flm_mmm_lines
677 WHERE plan_id = i_plan_id
678 AND organization_id = i_organization_id
679 AND calculation_operation_type = i_calculation_operation_type;
680
681 END IF;
682
683 IF G_DEBUG THEN
684 print_log('calculating line takt');
685 END IF;
686
687 -- based on demand, calculate line takt for each line
688 -- then save into table
689 IF i_demand.COUNT > 0 THEN
690 l_lines.DELETE;
691
692 IF i_line_id IS NOT NULL THEN
693 l_lines(i_line_id).line_id := i_line_id;
694 l_lines(i_line_id).total_demand := 0;
695
696 IF i_hours_per_day IS NOT NULL then
697 l_lines(i_line_id).hours_per_day := i_hours_per_day;
698
699 ELSE
700 --fix bug#3773193
701
702 SELECT start_time, stop_time
703 INTO l_start_time, l_stop_time
704 FROM wip_lines
705 WHERE line_id = i_line_id
706 AND organization_id = i_organization_id;
707
708 IF (l_stop_time <= l_start_time) THEN
709 l_stop_time := l_stop_time + 24*3600;
710 END IF;
711
712 l_lines(i_line_id).hours_per_day := (l_stop_time-l_start_time)/3600;
713 --end of fix bug#3773193
714
715 END IF;
716 END IF;
717
718 l_index := i_demand.first;
719 -- sum up demands
720 LOOP
721 IF l_lines.exists(i_demand(l_index).line_id) THEN
722 l_lines(i_demand(l_index).line_id).total_demand :=
723 i_demand(l_index).average_daily_demand
724 + l_lines(i_demand(l_index).line_id).total_demand;
725
726 ELSE
727 l_lines(i_demand(l_index).line_id).line_id :=
728 i_demand(l_index).line_id;
729 l_lines(i_demand(l_index).line_id).total_demand :=
730 i_demand(l_index).average_daily_demand;
731
732 --fix bug#3773193
733
734 SELECT start_time, stop_time
735 INTO l_start_time, l_stop_time
736 FROM wip_lines
737 WHERE line_id = i_demand(l_index).line_id
738 AND organization_id = i_organization_id;
739
740 IF (l_stop_time <= l_start_time) THEN
741 l_stop_time := l_stop_time + 24*3600;
742 END IF;
743
744 l_lines(i_demand(l_index).line_id).hours_per_day :=
745 (l_stop_time-l_start_time)/3600;
746 --end of fix bug#3773193
747
748 END IF;
749
750 EXIT WHEN l_index = i_demand.last;
751 l_index := i_demand.next(l_index);
752
753 END LOOP;
754
755 -- adjust hours per day if replan
756 IF i_replan_flag = C_REPLAN_FLAG_YES THEN
757 l_index := l_lines.FIRST;
758
759 LOOP
760 IF l_old_lines.EXISTS(l_index) THEN
761 l_lines(l_index).hours_per_day := l_old_lines(l_index).hours_per_day;
762 END IF;
763
764 EXIT WHEN l_index = l_lines.LAST;
765 l_index := l_lines.NEXT(l_index);
766
767 END LOOP;
768 END IF;
769
770 -- calculate line_takt
771 l_index := l_lines.first;
772 LOOP l_lines(l_index).line_takt :=
773 l_lines(l_index).hours_per_day/l_lines(l_index).total_demand;
774
775 INSERT INTO flm_mmm_lines (
776 plan_id,
777 organization_id,
778 line_id,
779 calculation_operation_type,
780 created_by,
781 creation_date,
782 last_updated_by,
783 last_update_date,
784 line_takt,
785 hours_per_day
786 ) VALUES (
787 i_plan_id,
788 i_organization_id,
789 l_lines(l_index).line_id,
790 i_calculation_operation_type,
791 fnd_global.user_id,
792 sysdate,
793 fnd_global.user_id,
794 sysdate,
795 l_lines(l_index).line_takt,
796 l_lines(l_index).hours_per_day
797 );
798
799 EXIT WHEN l_index = l_lines.last;
800 l_index := l_lines.next(l_index);
801
802 END LOOP;
803 END IF; -- demand count > 0
804
805 IF G_DEBUG THEN
806 print_log('calculate line takt ends');
807 END IF;
808
809 END calculate_line_takt;
810
811
812 /************************************************************************
813 * PROCEDURE calculate_process_volume *
814 * Calculates the process volume for each item *
815 * sat each line operation/process of each line. *
816 ************************************************************************/
817 PROCEDURE calculate_process_volume(
818 i_plan_id IN NUMBER,
819 i_organization_id IN NUMBER,
820 i_calc_op_type IN NUMBER,
821 i_demand IN t_demand_table) IS
822
823 CURSOR op_seqs(i_assembly_item_id NUMBER) IS
824 SELECT bos.standard_operation_id standard_operation_id,
825 nvl(bos.net_planning_percent, 100) net_planning_percent,
826 nvl(bos.reverse_cumulative_yield, 1) reverse_cumulative_yield,
827 bos.operation_type operation_type,
828 bos.machine_time_calc machine_time,
829 bos.labor_time_calc labor_time,
830 bos.total_time_calc elapsed_time,
831 bor.line_id line_id
832 FROM bom_operational_routings bor,
833 bom_operation_sequences bos
834 WHERE bor.assembly_item_id = i_assembly_item_id
835 AND bor.organization_id = i_organization_id
836 AND bor.cfm_routing_flag = 1
837 AND bor.mixed_model_map_flag = 1
838 AND bor.common_routing_sequence_id = bos.routing_sequence_id
839 AND bos.operation_type = i_calc_op_type
840 ORDER BY bos.standard_operation_id;
841
842 l_npp NUMBER;
843 l_rcy NUMBER;
844 l_index NUMBER;
845
846 BEGIN
847 IF G_DEBUG THEN
848 print_log('calculating process volume');
849 print_log(i_demand.count);
850 END IF;
851 -- based on demand AND routing info, calculate process volume for each
852 -- item at line operation/process of each line.
853 -- then save into table
854 IF i_demand.COUNT > 0 THEN
855 l_index := i_demand.first;
856
857 LOOP
858
859 FOR op_seq_rec in op_seqs(i_demand(l_index).assembly_item_id) LOOP
860 IF G_DEBUG THEN
861 print_log('npp: '||op_seq_rec.net_planning_percent);
862 print_log('rcv: '||op_seq_rec.reverse_cumulative_yield);
863 print_log('demand: '||i_demand(l_index).average_daily_demand);
864 END IF;
865
866 l_npp := op_seq_rec.net_planning_percent;
867 l_rcy := op_seq_rec.reverse_cumulative_yield;
868
869 IF l_npp = 0 THEN
870 l_npp := 100;
871 END IF;
872
873 IF l_rcy = 0 THEN
874 l_rcy := 1;
875 END IF;
876
877 IF G_DEBUG THEN
878 print_log('l_npp:'||l_npp);
879 print_log('l_rcy:'||l_rcy);
880
881 print_log('parameter:' ||':'||
882 i_plan_id || ':'||
883 i_organization_id || ':'||
884 i_demand(l_index).assembly_item_id || ':'||
885 op_seq_rec.line_id || ':'||
886 op_seq_rec.operation_type || ':'||
887 op_seq_rec.standard_operation_id || ':'||
888 fnd_global.user_id || ':'||
889 sysdate || ':'||
890 fnd_global.user_id || ':'||
891 sysdate || ':'||
892 i_demand(l_index).average_daily_demand
893 * l_npp / 100
894 / l_rcy || ':'||
895 op_seq_rec.machine_time || ':'||
896 op_seq_rec.labor_time || ':'||
897 op_seq_rec.elapsed_time
898 );
899 END IF;
900
901 INSERT INTO flm_mmm_op_items (
902 plan_id,
903 organization_id,
904 assembly_item_id,
905 line_id,
906 operation_type,
907 standard_operation_id,
908 created_by,
909 creation_date,
910 last_updated_by,
911 last_update_date,
912 process_volume,
913 machine_time,
914 labor_time,
915 elapsed_time
916 ) VALUES (
917 i_plan_id,
918 i_organization_id,
919 i_demand(l_index).assembly_item_id,
920 op_seq_rec.line_id,
921 op_seq_rec.operation_type,
922 op_seq_rec.standard_operation_id,
923 fnd_global.user_id,
924 sysdate,
925 fnd_global.user_id,
926 sysdate,
927 i_demand(l_index).average_daily_demand
928 * l_npp / 100
929 / l_rcy,
930 op_seq_rec.machine_time,
931 op_seq_rec.labor_time,
932 op_seq_rec.elapsed_time
933 );
934
935 END LOOP;
936
937 EXIT WHEN l_index = i_demand.last;
938 l_index := i_demand.next(l_index);
939
940 END LOOP;
941 END IF; -- demand count > 0
942
943 IF G_DEBUG THEN
944 print_log('********calculate process volume ends');
945 END IF;
946
947 END calculate_process_volume;
948
949
950 /************************************************************************
951 * PROCEDURE calculate_operation_takt *
952 * Calculates operation takt for each line operation/process at *
953 * each line, based on the process volume. *
954 ************************************************************************/
955 PROCEDURE calculate_operation_takt(
956 i_plan_id IN NUMBER,
957 i_organization_id IN NUMBER,
958 i_calc_op_type IN NUMBER,
959 i_hours_per_day IN NUMBER,
960 i_replan_flag IN VARCHAR2) IS
961
962 -- local type
963 TYPE tl_ipk_rec IS RECORD (
964 standard_operation_id NUMBER,
965 ipk_assigned NUMBER);
966 TYPE tl_ipk_tbl IS TABLE OF tl_ipk_rec
967 INDEX BY BINARY_INTEGER;
968
969 CURSOR op_takt IS
970 SELECT it.standard_operation_id,
971 it.line_id,
972 it.operation_type,
973 nvl(line.hours_per_day,1)/
974 nvl(sum(it.process_volume), 1) operation_takt
975 FROM flm_mmm_lines line,
976 flm_mmm_op_items it
977 WHERE line.plan_id = i_plan_id
978 AND line.organization_id = i_organization_id
979 AND line.calculation_operation_type = it.operation_type --bug 5725595
980 AND it.plan_id = i_plan_id
981 AND it.organization_id = i_organization_id
982 AND it.line_id = line.line_id
983 AND it.operation_type = i_calc_op_type
984 GROUP BY it.standard_operation_id, it.line_id,
985 it.operation_type, line.hours_per_day;
986
987 CURSOR all_ipks IS
988 SELECT standard_operation_id op_id,
989 ipk_assigned
990 FROM flm_mmm_operations
991 WHERE plan_id = i_plan_id
992 AND organization_id = i_organization_id
993 AND operation_type = i_calc_op_type;
994
995 l_ipks tl_ipk_tbl;
996 l_ipk_assigned NUMBER;
997
998 BEGIN
999 -- if replan, record ipk assigned history for this plan
1000 -- then clear the historical records
1001 IF i_replan_flag = C_REPLAN_FLAG_YES THEN
1002 l_ipks.DELETE;
1003
1004 FOR ipk_rec IN all_ipks LOOP
1005 l_ipks(ipk_rec.op_id).standard_operation_id := ipk_rec.op_id;
1006 l_ipks(ipk_rec.op_id).ipk_assigned := ipk_rec.ipk_assigned;
1007 END LOOP;
1008
1009 DELETE FROM flm_mmm_operations
1010 WHERE plan_id = i_plan_id
1011 AND organization_id = i_organization_id
1012 AND operation_type = i_calc_op_type;
1013
1014 END IF;
1015
1016 -- based on process volume, calculate operation takt
1017 -- then save into table
1018 IF G_DEBUG THEN
1019 print_log('calculating operation takt');
1020 END IF;
1021
1022 FOR op_takt_rec IN op_takt LOOP
1023 IF l_ipks.EXISTS(op_takt_rec.standard_operation_id) THEN
1024 l_ipk_assigned := l_ipks(op_takt_rec.standard_operation_id).ipk_assigned;
1025
1026 ELSE
1027 BEGIN
1028 SELECT ipk_assigned
1029 INTO l_ipk_assigned
1030 FROM flm_mmm_operations
1031 WHERE plan_id = -1
1032 AND organization_id = i_organization_id
1033 AND standard_operation_id = op_takt_rec.standard_operation_id;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 l_ipk_assigned := NULL;
1038
1039 END;
1040
1041 END IF;
1042
1043 INSERT INTO flm_mmm_operations (
1044 plan_id,
1045 organization_id,
1046 standard_operation_id,
1047 created_by,
1048 creation_date,
1049 last_updated_by,
1050 last_update_date,
1051 line_id,
1052 operation_type,
1053 operation_takt,
1054 ipk_assigned
1055 ) VALUES (
1056 i_plan_id,
1057 i_organization_id,
1058 op_takt_rec.standard_operation_id,
1059 fnd_global.user_id,
1060 sysdate,
1061 fnd_global.user_id,
1062 sysdate,
1063 op_takt_rec.line_id,
1064 op_takt_rec.operation_type,
1065 op_takt_rec.operation_takt,
1066 nvl(l_ipk_assigned, 0) -- set to 0 if not defined
1067 );
1068
1069 END LOOP;
1070
1071 IF G_DEBUG THEN
1072 print_log('calculate operation takt ends');
1073 END IF;
1074
1075 END calculate_operation_takt;
1076
1077
1078 /************************************************************************
1079 * PROCEDURE calculate_weighted_times *
1080 * Calculates weighted times for each line operation/process *
1081 * at each line, based on the process volume and item routings. *
1082 ************************************************************************/
1083 PROCEDURE calculate_weighted_times(
1084 i_plan_id IN NUMBER,
1085 i_organization_id IN NUMBER,
1086 i_calc_op_type IN NUMBER) IS
1087
1088 l_machine_wt NUMBER;
1089 l_labor_wt NUMBER;
1090 l_elapsed_wt NUMBER;
1091
1092 CURSOR operations IS
1093 SELECT standard_operation_id
1094 FROM flm_mmm_operations
1095 WHERE plan_id = i_plan_id
1096 AND organization_id = i_organization_id
1097 AND operation_type = i_calc_op_type;
1098
1099 BEGIN
1100 -- based on process volume and routing info, calculate weighted times
1101 -- then save into table
1102 FOR op_rec IN operations LOOP
1103 -- calculate
1104 SELECT sum(machine_time*process_volume) / sum(process_volume)
1105 machine_weighted_time,
1106 sum(labor_time*process_volume) / sum(process_volume)
1107 labor_weighted_time,
1108 sum(elapsed_time*process_volume) / sum(process_volume)
1109 elapsed_weighted_time
1110 INTO l_machine_wt,
1111 l_labor_wt,
1112 l_elapsed_wt
1113 FROM flm_mmm_op_items
1114 WHERE plan_id = i_plan_id
1115 AND organization_id = i_organization_id
1116 AND standard_operation_id = op_rec.standard_operation_id
1117 GROUP BY standard_operation_id;
1118
1119 -- update operation records
1120 UPDATE flm_mmm_operations
1121 SET machine_weighted_time = l_machine_wt,
1122 labor_weighted_time = l_labor_wt,
1123 elapsed_weighted_time = l_elapsed_wt
1124 WHERE plan_id = i_plan_id
1125 AND organization_id = i_organization_id
1126 AND standard_operation_id = op_rec.standard_operation_id;
1127
1128 END LOOP;
1129
1130 IF G_DEBUG THEN
1131 print_log('calculate weighted times ends');
1132 END IF;
1133
1134 END calculate_weighted_times;
1135
1136
1137 /************************************************************************
1138 * PROCEDURE calculate_weighted_res_usage *
1139 * Calculates weighted resource usage for each resource at each *
1140 * line operation/process of each line. *
1141 ************************************************************************/
1142 PROCEDURE calculate_weighted_res_usage (
1143 i_plan_id IN NUMBER,
1144 i_organization_id IN NUMBER,
1145 i_calc_op_type IN NUMBER,
1146 i_replan_flag IN VARCHAR2) IS
1147
1148 -- local types
1149 TYPE tl_res_rec IS RECORD(
1150 resource_id NUMBER,
1151 standard_operation_id NUMBER,
1152 resource_assigned NUMBER);
1153 TYPE tl_res_tbl IS TABLE OF tl_res_rec
1154 INDEX BY BINARY_INTEGER;
1155
1156 l_hour_uom VARCHAR2(3);
1157 l_hour_conv NUMBER;
1158
1159 CURSOR operations IS
1160 SELECT fmo.standard_operation_id,
1161 fmo.operation_type,
1162 fmo.line_id,
1163 fmo.operation_takt,
1164 fml.hours_per_day
1165 FROM flm_mmm_operations fmo,
1166 flm_mmm_lines fml
1167 WHERE fmo.plan_id = i_plan_id
1168 AND fmo.organization_id = i_organization_id
1169 AND fmo.operation_type = i_calc_op_type
1170 AND fml.plan_id = i_plan_id
1171 AND fml.organization_id = i_organization_id
1172 AND fml.calculation_operation_type = i_calc_op_type
1173 AND fml.line_id = fmo.line_id;
1174
1175 CURSOR resources(i_standard_operation_id NUMBER) IS
1176 SELECT bor.resource_id,
1177 it.assembly_item_id,
1178 it.process_volume,
1179 sum(bor.usage_rate_or_amount * nvl(muc.conversion_rate, 0) /
1180 nvl(l_hour_conv, 1)) resource_usage
1181 FROM flm_mmm_operations op,
1182 flm_mmm_op_items it,
1183 bom_operational_routings brtg,
1184 bom_operation_sequences bos1,
1185 bom_operation_sequences bos2,
1186 bom_operation_resources bor,
1187 bom_resources br,
1188 mtl_uom_conversions muc
1189 WHERE op.plan_id = i_plan_id
1190 AND op.organization_id = i_organization_id
1191 AND op.standard_operation_id = i_standard_operation_id
1192 AND it.plan_id = i_plan_id
1193 AND it.organization_id = i_organization_id
1194 AND it.standard_operation_id = op.standard_operation_id
1195 AND brtg.organization_id = i_organization_id
1196 AND brtg.assembly_item_id = it.assembly_item_id
1197 AND brtg.mixed_model_map_flag = 1
1198 AND brtg.common_routing_sequence_id = bos1.routing_sequence_id
1199 AND brtg.common_routing_sequence_id = bos2.routing_sequence_id
1200 AND bos1.standard_operation_id = it.standard_operation_id
1201 AND (( bos1.operation_type = 3
1202 AND bos2.line_op_seq_id = bos1.operation_sequence_id) or
1203 ( bos1.operation_type = 2
1204 AND bos2.process_op_seq_id = bos1.operation_sequence_id))
1205 AND bos2.operation_sequence_id = bor.operation_sequence_id
1206 AND br.resource_id = bor.resource_id
1207 AND muc.uom_code(+) = br.unit_of_measure
1208 AND muc.inventory_item_id(+) = 0
1209 GROUP BY bor.resource_id, it.assembly_item_id, it.process_volume
1210 ORDER BY bor.resource_id;
1211
1212 CURSOR all_res_assigned IS
1213 SELECT resource_id,
1214 standard_operation_id op_id,
1215 resource_assigned
1216 FROM flm_mmm_op_resources
1217 WHERE plan_id = i_plan_id
1218 AND organization_id = i_organization_id
1219 AND operation_type = i_calc_op_type;
1220
1221 l_total_pv NUMBER;
1222 l_total_rs NUMBER;
1223 l_res_id NUMBER;
1224
1225 i NUMBER;
1226 l_resources tl_res_tbl;
1227 l_resource_assigned NUMBER;
1228
1229 BEGIN
1230 -- if replan, record resource assigned history for this plan
1231 -- then clear the historical records
1232 IF i_replan_flag = C_REPLAN_FLAG_YES THEN
1233 l_resources.DELETE;
1234 i := 0;
1235 FOR res_rec IN all_res_assigned LOOP
1236 l_resources(i).resource_id := res_rec.resource_id;
1237 l_resources(i).standard_operation_id := res_rec.op_id;
1238 l_resources(i).resource_assigned := nvl(res_rec.resource_assigned, 0);
1239 i := i+1;
1240 END LOOP;
1241
1242 DELETE FROM flm_mmm_op_resources
1243 WHERE plan_id = i_plan_id
1244 AND organization_id = i_organization_id
1245 AND operation_type = i_calc_op_type;
1246
1247 END IF;
1248
1249 -- based on process volume AND routing info, calculate weighted
1250 -- resource usage
1251 -- then save into table
1252 -- Hourly UOM and Conversion Rate
1253 l_hour_uom := FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE');
1254 BEGIN
1255 SELECT nvl(conversion_rate, 0)
1256 INTO l_hour_conv
1257 FROM mtl_uom_conversions
1258 WHERE uom_code = l_hour_uom
1259 AND inventory_item_id = 0;
1260
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 l_hour_conv := 1;
1264
1265 END;
1266
1267 FOR op_rec IN operations LOOP
1268 IF op_rec.operation_takt > 0 THEN
1269 l_total_pv := op_rec.hours_per_day / op_rec.operation_takt;
1270 ELSE
1271 l_total_pv := 0;
1272 END IF;
1273
1274 l_total_rs := 0;
1275 l_res_id := null;
1276 FOR rs_rec IN resources(op_rec.standard_operation_id) LOOP
1277 IF G_DEBUG THEN
1278 print_log('op: '||op_rec.standard_operation_id||' res: '||
1279 rs_rec.resource_id||' item: '||rs_rec.assembly_item_id);
1280 END IF;
1281
1282 IF l_res_id IS NULL THEN
1283 l_res_id := rs_rec.resource_id;
1284 l_total_rs := rs_rec.process_volume * rs_rec.resource_usage;
1285
1286 ELSIF l_res_id = rs_rec.resource_id THEN
1287 l_total_rs := l_total_rs + rs_rec.process_volume *
1288 rs_rec.resource_usage;
1289
1290 ELSE
1291 IF l_total_pv > 0 AND
1292 l_total_rs > 0 THEN
1293 BEGIN
1294 SELECT resource_assigned
1295 INTO l_resource_assigned
1296 FROM flm_mmm_op_resources
1297 WHERE plan_id = -1
1298 AND organization_id = i_organization_id
1299 AND standard_operation_id = op_rec.standard_operation_id
1300 AND resource_id = l_res_id;
1301
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 l_resource_assigned := NULL;
1305
1306 END;
1307
1308 INSERT INTO flm_mmm_op_resources (
1309 plan_id,
1310 organization_id,
1311 resource_id,
1312 standard_operation_id,
1313 created_by,
1314 creation_date,
1315 last_updated_by,
1316 last_update_date,
1317 line_id,
1318 operation_type,
1319 weighted_resource_usage,
1320 resource_assigned
1321 ) VALUES (
1322 i_plan_id,
1323 i_organization_id,
1324 l_res_id,
1325 op_rec.standard_operation_id,
1326 fnd_global.user_id,
1327 sysdate,
1328 fnd_global.user_id,
1329 sysdate,
1330 op_rec.line_id,
1331 op_rec.operation_type,
1332 l_total_rs / l_total_pv,
1333 nvl(l_resource_assigned, 0)
1334 );
1335
1336 END IF;
1337
1338 l_res_id := rs_rec.resource_id;
1339 l_total_rs := rs_rec.process_volume * rs_rec.resource_usage;
1340
1341 END IF;
1342
1343 END LOOP;
1344
1345 -- last one
1346 IF l_res_id IS NOT NULL AND
1347 l_total_pv > 0 AND l_total_rs > 0 THEN
1348 BEGIN
1349 SELECT resource_assigned
1350 INTO l_resource_assigned
1351 FROM flm_mmm_op_resources
1352 WHERE plan_id = -1
1353 AND organization_id = i_organization_id
1354 AND standard_operation_id = op_rec.standard_operation_id
1355 AND resource_id = l_res_id;
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 l_resource_assigned := NULL;
1360
1361 END;
1362
1363 INSERT INTO flm_mmm_op_resources (
1364 plan_id,
1365 organization_id,
1366 resource_id,
1367 standard_operation_id,
1368 created_by,
1369 creation_date,
1370 last_updated_by,
1371 last_update_date,
1372 line_id,
1373 operation_type,
1374 weighted_resource_usage,
1375 resource_assigned
1376 ) VALUES (
1377 i_plan_id,
1378 i_organization_id,
1379 l_res_id,
1380 op_rec.standard_operation_id,
1381 fnd_global.user_id,
1382 sysdate,
1383 fnd_global.user_id,
1384 sysdate,
1385 op_rec.line_id,
1386 op_rec.operation_type,
1387 l_total_rs / l_total_pv,
1388 nvl(l_resource_assigned, 0)
1389 );
1390
1391 END IF;
1392
1393 END LOOP;
1394
1395 -- if replan, overwrite the resource assigned information with
1396 -- the historical data for this plan
1397 IF i_replan_flag = C_REPLAN_FLAG_YES AND
1398 l_resources.COUNT > 0 THEN
1399 i := 0;
1400 LOOP
1401 UPDATE flm_mmm_op_resources
1402 SET resource_assigned = l_resources(i).resource_assigned
1403 WHERE plan_id = i_plan_id
1404 AND organization_id = i_organization_id
1405 AND resource_id = l_resources(i).resource_id
1406 AND standard_operation_id = l_resources(i).standard_operation_id;
1407
1408 EXIT WHEN i = l_resources.LAST;
1409 i := l_resources.NEXT(i);
1410
1411 END LOOP;
1412
1413 END IF;
1414
1415 IF G_DEBUG THEN
1416 print_log('calculate weighted resource usage ends');
1417 END IF;
1418
1419 END calculate_weighted_res_usage;
1420
1421
1422 /************************************************************************
1423 * PROCEDURE calculate_res_ipk_needed *
1424 * Calculates Resources/IPK Needed for each resource and operation.*
1425 ************************************************************************/
1426 PROCEDURE calculate_res_ipk_needed(
1427 i_plan_id IN NUMBER,
1428 i_organization_id IN NUMBER,
1429 i_calc_op_type IN NUMBER,
1430 i_calculate_option IN NUMBER) IS
1431
1432 CURSOR resources IS
1433 SELECT resource_id,
1434 standard_operation_id,
1435 weighted_resource_usage,
1436 resource_assigned
1437 FROM flm_mmm_op_resources fmor
1438 WHERE plan_id = i_plan_id
1439 AND organization_id = i_organization_id
1440 AND operation_type = i_calc_op_type;
1441
1442 CURSOR operations IS
1443 SELECT fmo.standard_operation_id,
1444 fmo.operation_takt,
1445 fmo.ipk_assigned,
1446 fml.hours_per_day
1447 FROM flm_mmm_operations fmo,
1448 flm_mmm_lines fml
1449 WHERE fmo.plan_id = i_plan_id
1450 AND fmo.organization_id = i_organization_id
1451 AND fml.plan_id = i_plan_id
1452 AND fml.organization_id = i_organization_id
1453 AND fmo.line_id = fml.line_id
1454 AND fmo.operation_type = i_calc_op_type
1455 AND fml.calculation_operation_type = fmo.operation_type;
1456
1457 l_max_rs_usage NUMBER;
1458 l_ipk_needed NUMBER;
1459
1460 l_count NUMBER;
1461
1462 BEGIN
1463 -- based on weighted resource usage, ipk/resource assigned, AND
1464 -- calculate option, calculate resource/ipk needed.
1465 -- then save into table
1466 --
1467 -- branch on calculate option
1468 --
1469 IF i_calculate_option = C_CALC_OPTION_NO_IPK THEN
1470 -- Resource Needed = Weighted Resource Usage / Operation TAKT
1471 FOR op_rec IN operations LOOP
1472 IF op_rec.operation_takt > 0 THEN -- otherwise, sth must be wrong with the op, leave it null
1473 UPDATE flm_mmm_op_resources
1474 SET resource_needed = weighted_resource_usage / op_rec.operation_takt
1475 WHERE plan_id = i_plan_id
1476 AND organization_id = i_organization_id
1477 AND standard_operation_id = op_rec.standard_operation_id;
1478
1479 END IF;
1480
1481 END LOOP;
1482
1483 -- IPK Needed = 0;
1484 UPDATE flm_mmm_operations
1485 SET ipk_needed = 0
1486 WHERE plan_id = i_plan_id
1487 AND organization_id = i_organization_id
1488 AND operation_type = i_calc_op_type;
1489
1490 ELSIF i_calculate_option = C_CALC_OPTION_ONE_RESOURCE THEN
1491 -- Resource Needed = 1
1492 UPDATE flm_mmm_op_resources fmor
1493 SET resource_needed = 1
1494 WHERE plan_id = i_plan_id
1495 AND organization_id = i_organization_id
1496 AND operation_type = i_calc_op_type;
1497
1498 -- IPK Needed = ( MAX(Weighted Resource Usage) - Operation TAKT ) /
1499 -- MAX(Weighted Resource Usage) *
1500 -- HOURS PER DAY /
1501 -- Operation TAKT
1502 FOR op_rec IN operations LOOP
1503 IF op_rec.operation_takt > 0 AND op_rec.hours_per_day > 0 THEN
1504 -- Max Weigthed Resource Usage
1505 SELECT max(nvl(weighted_resource_usage,0))
1506 INTO l_max_rs_usage
1507 FROM flm_mmm_op_resources
1508 WHERE plan_id = i_plan_id
1509 AND organization_id = i_organization_id
1510 AND standard_operation_id = op_rec.standard_operation_id;
1511
1512 -- Update FLM_MMM_OPERATIONS
1513 IF l_max_rs_usage > 0 THEN
1514 UPDATE flm_mmm_operations
1515 SET ipk_needed =
1516 ceil((l_max_rs_usage - op_rec.operation_takt)/l_max_rs_usage *
1517 op_rec.hours_per_day / op_rec.operation_takt)
1518 WHERE plan_id = i_plan_id
1519 AND organization_id = i_organization_id
1520 AND standard_operation_id = op_rec.standard_operation_id;
1521
1522 ELSE -- No Resource Usage, set IPK = 0
1523 UPDATE flm_mmm_operations
1524 SET ipk_needed = 0
1525 WHERE plan_id = i_plan_id
1526 AND organization_id = i_organization_id
1527 AND standard_operation_id = op_rec.standard_operation_id;
1528
1529 END IF;
1530
1531 END IF; -- operation_takt > 0 AND op_rec.hours_per_day
1532
1533 END LOOP;
1534
1535 UPDATE flm_mmm_operations
1536 SET ipk_needed = 0
1537 WHERE plan_id = i_plan_id
1538 AND organization_id = i_organization_id
1539 AND ipk_needed < 0;
1540
1541 ELSIF i_calculate_option = C_CALC_OPTION_RES_ASSIGNED THEN
1542 -- IPK Needed = ( MAX(Weighted Resource Usage / Resource Assigned) -
1543 -- Operation TAKT ) /
1544 -- MAX(Weighted Resource Usage / Resource Assigned) *
1545 -- HOURS PER DAY /
1546 -- Operation TAKT
1547 FOR op_rec IN operations LOOP
1548 -- if any required resource is not assigned, we can not perform the calculation here
1549 SELECT count(*)
1550 INTO l_count
1551 FROM flm_mmm_op_resources
1552 WHERE plan_id = i_plan_id
1553 AND organization_id = i_organization_id
1554 AND standard_operation_id = op_rec.standard_operation_id
1555 AND (resource_assigned IS NULL OR
1556 resource_assigned = 0);
1557
1558 IF l_count = 0 AND op_rec.operation_takt > 0 AND
1559 op_rec.hours_per_day > 0 THEN -- no unassigned resource
1560
1561 -- Max ( Weigthed Resource Usage / Resource Assigned )
1562 SELECT max(weighted_resource_usage / nvl(resource_assigned, 0)) -- ???
1563 INTO l_max_rs_usage
1564 FROM flm_mmm_op_resources
1565 WHERE plan_id = i_plan_id
1566 AND organization_id = i_organization_id
1567 AND standard_operation_id = op_rec.standard_operation_id;
1568
1569 -- Update FLM_MMM_OPERATIONS, Set IPK Needed
1570 l_ipk_needed := ceil((l_max_rs_usage - op_rec.operation_takt) /
1571 l_max_rs_usage * op_rec.hours_per_day /
1572 op_rec.operation_takt);
1573
1574 IF l_ipk_needed < 0 OR l_ipk_needed IS NULL THEN
1575 l_ipk_needed := 0;
1576 END IF;
1577
1578 UPDATE flm_mmm_operations
1579 SET ipk_needed = l_ipk_needed
1580 WHERE plan_id = i_plan_id
1581 AND organization_id = i_organization_id
1582 AND standard_operation_id = op_rec.standard_operation_id;
1583
1584 -- Resource Needed = ( Hours Per Day/Operation TAKT - IPK Needed) *
1585 -- Weighted Resource Usage / Hours Per Day
1586 UPDATE flm_mmm_op_resources
1587 SET resource_needed =
1588 (op_rec.hours_per_day/op_rec.operation_takt - l_ipk_needed) *
1589 weighted_resource_usage / op_rec.hours_per_day
1590 WHERE plan_id = i_plan_id
1591 AND organization_id = i_organization_id
1592 AND standard_operation_id = op_rec.standard_operation_id;
1593
1594 END IF; -- l_count = 0 ...
1595
1596 END LOOP;
1597
1598 ELSIF i_calculate_option = C_CALC_OPTION_IPK_ASSIGNED THEN
1599 FOR op_rec IN operations LOOP
1600 IF op_rec.hours_per_day > 0 THEN
1601 -- Resource Needed = ( Hours Per Day/Operation TAKT - IPK Assigned) *
1602 -- Weighted Resource Usage / Hours Per Day
1603 UPDATE flm_mmm_op_resources
1604 SET resource_needed =
1605 (op_rec.hours_per_day/op_rec.operation_takt - nvl(op_rec.ipk_assigned, 0)) *
1606 weighted_resource_usage / op_rec.hours_per_day
1607 WHERE plan_id = i_plan_id
1608 AND organization_id = i_organization_id
1609 AND standard_operation_id = op_rec.standard_operation_id;
1610
1611 UPDATE flm_mmm_op_resources
1612 SET resource_needed = 0
1613 WHERE plan_id = i_plan_id
1614 AND organization_id = i_organization_id
1615 AND standard_operation_id = op_rec.standard_operation_id
1616 AND resource_needed < 0;
1617
1618 END IF;
1619
1620 END LOOP;
1621
1622 -- IPK Needed = IPK Assigned
1623 UPDATE flm_mmm_operations
1624 SET ipk_needed = ipk_assigned
1625 WHERE plan_id = i_plan_id
1626 AND organization_id = i_organization_id
1627 AND operation_type = i_calc_op_type;
1628
1629 ELSE
1630
1631 IF G_DEBUG THEN
1632 print_log('Invalid Calculate Option!!!');
1633 END IF;
1634
1635 END IF;
1636
1637 IF G_DEBUG THEN
1638 print_log('calculate resource/ipk needed');
1639 END IF;
1640
1641 END calculate_res_ipk_needed;
1642
1643
1644 /************************************************************************
1645 * PROCEDURE calculate_needed_by_op *
1646 * Calculates Resources/IPK Needed for the given *
1647 * operation and related resources. *
1648 ************************************************************************/
1649 PROCEDURE calculate_needed_by_op(
1650 i_plan_id IN NUMBER,
1651 i_organization_id IN NUMBER,
1652 i_standard_operation_id IN NUMBER,
1653 i_calculate_option IN NUMBER) IS
1654
1655 CURSOR resources IS
1656 SELECT resource_id,
1657 standard_operation_id,
1658 weighted_resource_usage,
1659 resource_assigned
1660 FROM flm_mmm_op_resources fmor
1661 WHERE plan_id = i_plan_id
1662 AND organization_id = i_organization_id
1663 AND standard_operation_id = i_standard_operation_id;
1664
1665 CURSOR op_parameter IS
1666 SELECT fmo.operation_takt,
1667 fmo.ipk_assigned,
1668 fml.hours_per_day
1669 FROM flm_mmm_operations fmo,
1670 flm_mmm_lines fml
1671 WHERE fmo.plan_id = i_plan_id
1672 AND fmo.organization_id = i_organization_id
1673 AND fmo.standard_operation_id = i_standard_operation_id
1674 AND fml.plan_id = i_plan_id
1675 AND fml.organization_id = i_organization_id
1676 AND fml.calculation_operation_type = fmo.operation_type
1677 AND fml.line_id = fmo.line_id;
1678
1679 l_max_rs_usage NUMBER;
1680 l_ipk_needed NUMBER;
1681 l_ipk_assigned NUMBER;
1682 l_hours_per_day NUMBER;
1683 l_operation_takt NUMBER;
1684 l_count NUMBER;
1685
1686 BEGIN
1687 -- based on weighted resource usage, ipk/resource assigned, AND
1688 -- calculate option, calculate resource/ipk needed.
1689 -- then save into table
1690 -- unlike procedure 'calculate_res_ipk_needed', in this procedure
1691 -- calculation is done only for the specified operation
1692 --
1693 OPEN op_parameter;
1694 FETCH op_parameter INTO l_operation_takt, l_ipk_assigned, l_hours_per_day;
1695
1696 IF op_parameter%NOTFOUND THEN
1697 CLOSE op_parameter;
1698 RETURN;
1699 END IF;
1700
1701 CLOSE op_parameter;
1702
1703 --
1704 -- branch on calculate option
1705 --
1706 IF i_calculate_option IN (C_CALC_OPTION_ONE_RESOURCE, C_CALC_OPTION_RES_ASSIGNED) THEN
1707 -- IPK Needed = ( MAX(Weighted Resource Usage / Resource Assigned) -
1708 -- Operation TAKT ) /
1709 -- MAX(Weighted Resource Usage / Resource Assigned) *
1710 -- HOURS PER DAY /
1711 -- Operation TAKT
1712 -- if any required resource is not assigned, then we can not perform
1713 -- the calculation here
1714 SELECT count(*)
1715 INTO l_count
1716 FROM flm_mmm_op_resources
1717 WHERE plan_id = i_plan_id
1718 AND organization_id = i_organization_id
1719 AND standard_operation_id = i_standard_operation_id
1720 AND (resource_assigned IS NULL OR
1721 resource_assigned = 0);
1722
1723 IF l_count = 0 AND l_operation_takt > 0 AND
1724 l_hours_per_day > 0 THEN -- no unassigned resource
1725 -- Max ( Weigthed Resource Usage / Resource Assigned )
1726 SELECT max(weighted_resource_usage / resource_assigned)
1727 INTO l_max_rs_usage
1728 FROM flm_mmm_op_resources
1729 WHERE plan_id = i_plan_id
1730 AND organization_id = i_organization_id
1731 AND standard_operation_id = i_standard_operation_id;
1732
1733 -- Update FLM_MMM_OPERATIONS, Set IPK Needed
1734 l_ipk_needed := ceil((l_max_rs_usage - l_operation_takt) /
1735 l_max_rs_usage * l_hours_per_day /
1736 l_operation_takt);
1737 IF G_DEBUG THEN
1738 print_log('ipk needed: '||l_ipk_needed);
1739 END IF;
1740
1741 IF l_ipk_needed < 0 OR l_ipk_needed IS NULL THEN
1742 l_ipk_needed := 0;
1743 END IF;
1744
1745 UPDATE flm_mmm_operations
1746 SET ipk_needed = l_ipk_needed
1747 WHERE plan_id = i_plan_id
1748 AND organization_id = i_organization_id
1749 AND standard_operation_id = i_standard_operation_id;
1750
1751 -- Resource Needed = ( Hours Per Day/Operation TAKT - IPK Needed) *
1752 -- Weighted Resource Usage / Hours Per Day
1753 UPDATE flm_mmm_op_resources
1754 SET resource_needed =
1755 (l_hours_per_day/l_operation_takt - l_ipk_needed) *
1756 weighted_resource_usage / l_hours_per_day
1757 WHERE plan_id = i_plan_id
1758 AND organization_id = i_organization_id
1759 AND standard_operation_id = i_standard_operation_id;
1760
1761 UPDATE flm_mmm_op_resources
1762 SET resource_needed = 0
1763 WHERE plan_id = i_plan_id
1764 AND organization_id = i_organization_id
1765 AND standard_operation_id = i_standard_operation_id
1766 AND resource_needed < 0;
1767
1768 END IF; -- l_count = 0 ...
1769
1770 ELSIF i_calculate_option IN (C_CALC_OPTION_NO_IPK, C_CALC_OPTION_IPK_ASSIGNED) THEN
1771 IF l_hours_per_day > 0 THEN
1772 -- Resource Needed = ( Hours Per Day/Operation TAKT - IPK Assigned) *
1773 -- Weighted Resource Usage / Hours Per Day
1774 --debug_log('op takt: '||l_operation_takt);
1775 --debug_log('ipk assigned: '||l_ipk_assigned);
1776 UPDATE flm_mmm_op_resources
1777 SET resource_needed =
1778 (l_hours_per_day/l_operation_takt - nvl(l_ipk_assigned, 0)) *
1779 weighted_resource_usage / l_hours_per_day
1780 WHERE plan_id = i_plan_id
1781 AND organization_id = i_organization_id
1782 AND standard_operation_id = i_standard_operation_id;
1783
1784 UPDATE flm_mmm_op_resources
1785 SET resource_needed = 0
1786 WHERE plan_id = i_plan_id
1787 AND organization_id = i_organization_id
1788 AND standard_operation_id = i_standard_operation_id
1789 AND resource_needed < 0;
1790
1791 END IF;
1792
1793 -- IPK Needed = IPK Assigned
1794 UPDATE flm_mmm_operations
1795 SET ipk_needed = ipk_assigned
1796 WHERE plan_id = i_plan_id
1797 AND organization_id = i_organization_id
1798 AND standard_operation_id = i_standard_operation_id;
1799
1800 ELSE
1801 IF G_DEBUG THEN
1802 print_log('Invalid Calculate Option!!!');
1803 END IF;
1804
1805 END IF;
1806
1807 END calculate_needed_by_op;
1808
1809
1810 /************************************************************************
1811 * PROCEDURE calculate_takt_assigned *
1812 * Calculates the operation and line takt as in accordance *
1813 * with the assigned resources. *
1814 ************************************************************************/
1815 PROCEDURE calculate_takt_assigned(
1816 i_plan_id IN NUMBER,
1817 i_organization_id IN NUMBER,
1818 i_calc_op_type IN NUMBER) IS
1819
1820 l_takt_assigned NUMBER;
1821
1822 l_count NUMBER;
1823
1824 CURSOR lines IS
1825 SELECT line_id
1826 FROM flm_mmm_lines
1827 WHERE plan_id = i_plan_id
1828 AND organization_id = i_organization_id
1829 AND calculation_operation_type = i_calc_op_type;
1830
1831 CURSOR operations IS
1832 SELECT standard_operation_id
1833 FROM flm_mmm_operations
1834 WHERE plan_id = i_plan_id
1835 AND organization_id = i_organization_id
1836 AND operation_type = i_calc_op_type;
1837
1838 CURSOR op_takt_assigned(i_operation_id NUMBER) IS
1839 SELECT max(weighted_resource_usage/resource_assigned) takt_assigned
1840 FROM flm_mmm_op_resources
1841 WHERE plan_id = i_plan_id
1842 AND organization_id = i_organization_id
1843 AND standard_operation_id = i_operation_id
1844 AND resource_assigned > 0;
1845
1846 CURSOR line_takt_assigned(i_line_id NUMBER) IS
1847 SELECT max(op_takt_as_assigned/operation_takt) max_ratio
1848 FROM flm_mmm_operations
1849 WHERE plan_id = i_plan_id
1850 AND organization_id = i_organization_id
1851 AND line_id = i_line_id
1852 AND operation_type = i_calc_op_type
1853 AND operation_takt > 0;
1854
1855 BEGIN
1856 -- Operation TAKT As Assigned
1857 FOR op_rec IN operations LOOP -- For each operation
1858 -- Infinite Over Limit?
1859 SELECT count(*)
1860 INTO l_count
1861 FROM flm_mmm_op_resources
1862 WHERE plan_id = i_plan_id
1863 AND organization_id = i_organization_id
1864 AND standard_operation_id = op_rec.standard_operation_id
1865 AND weighted_resource_usage > 0
1866 AND (resource_assigned = 0 or resource_assigned IS NULL);
1867
1868 IF l_count <= 0 THEN
1869 OPEN op_takt_assigned(op_rec.standard_operation_id);
1870 FETCH op_takt_assigned INTO l_takt_assigned;
1871 CLOSE op_takt_assigned;
1872
1873 IF l_takt_assigned IS NULL THEN -- No Resource Required?
1874 l_takt_assigned := 0;
1875 END IF;
1876
1877 UPDATE flm_mmm_operations
1878 SET op_takt_as_assigned = l_takt_assigned,
1879 last_updated_by = fnd_global.user_id,
1880 last_update_date = sysdate,
1881 last_update_login = fnd_global.user_id
1882 WHERE plan_id = i_plan_id
1883 AND organization_id = i_organization_id
1884 AND standard_operation_id = op_rec.standard_operation_id;
1885
1886 ELSE
1887 UPDATE flm_mmm_operations
1888 SET op_takt_as_assigned = NULL,
1889 last_updated_by = fnd_global.user_id,
1890 last_update_date = sysdate,
1891 last_update_login = fnd_global.user_id
1892 WHERE plan_id = i_plan_id
1893 AND organization_id = i_organization_id
1894 AND standard_operation_id = op_rec.standard_operation_id;
1895
1896 END IF;
1897
1898 END LOOP; -- op_rec
1899
1900 -- Line TAKT As Assigned
1901 FOR line_rec IN lines LOOP -- Each Line
1902 -- Unknown Operation TAKT?
1903 SELECT count(*)
1904 INTO l_count
1905 FROM flm_mmm_operations
1906 WHERE plan_id = i_plan_id
1907 AND organization_id = i_organization_id
1908 AND operation_type = i_calc_op_type
1909 AND line_id = line_rec.line_id
1910 AND op_takt_as_assigned IS NULL;
1911
1912 IF l_count <= 0 THEN
1913 OPEN line_takt_assigned(line_rec.line_id);
1914 FETCH line_takt_assigned INTO l_takt_assigned;
1915 CLOSE line_takt_assigned;
1916
1917 IF l_takt_assigned IS NULL THEN -- No Operation?
1918 l_takt_assigned := 0;
1919 END IF;
1920
1921 UPDATE flm_mmm_lines
1922 SET line_takt_as_assigned = l_takt_assigned * line_takt,
1923 last_updated_by = fnd_global.user_id,
1924 last_update_date = sysdate,
1925 last_update_login = fnd_global.user_id
1926 WHERE plan_id = i_plan_id
1927 AND organization_id = i_organization_id
1928 AND line_id = line_rec.line_id
1929 AND calculation_operation_type = i_calc_op_type;
1930
1931 ELSE
1932
1933 UPDATE flm_mmm_lines
1934 SET line_takt_as_assigned = NULL,
1935 last_updated_by = fnd_global.user_id,
1936 last_update_date = sysdate,
1937 last_update_login = fnd_global.user_id
1938 WHERE plan_id = i_plan_id
1939 AND organization_id = i_organization_id
1940 AND line_id = line_rec.line_id
1941 AND calculation_operation_type = i_calc_op_type;
1942
1943 END IF;
1944 END LOOP; -- line_rec
1945 END calculate_takt_assigned;
1946
1947
1948 /************************************************************************
1949 * PROCEDURE calculate_res_ipk_overcapacity *
1950 * Calculates the overcapacity percentage for resources and IPKs *
1951 * for each line and line operation/process. *
1952 ************************************************************************/
1953 PROCEDURE calculate_res_ipk_overcapacity(
1954 i_plan_id IN NUMBER,
1955 i_organization_id IN NUMBER,
1956 i_calc_op_type IN NUMBER) IS
1957
1958 l_res_over_pct NUMBER;
1959 l_ipk_over_pct NUMBER;
1960
1961 l_count NUMBER;
1962
1963 CURSOR lines IS
1964 SELECT line_id
1965 FROM flm_mmm_lines
1966 WHERE plan_id = i_plan_id
1967 AND organization_id = i_organization_id
1968 AND calculation_operation_type = i_calc_op_type;
1969
1970 CURSOR operations IS
1971 SELECT standard_operation_id
1972 FROM flm_mmm_operations
1973 WHERE plan_id = i_plan_id
1974 AND organization_id = i_organization_id
1975 AND operation_type = i_calc_op_type;
1976
1977 CURSOR res_overcapacity_op(i_operation_id NUMBER) IS
1978 SELECT
1979 max(100*(resource_needed-resource_assigned)/resource_assigned) over_pct
1980 FROM flm_mmm_op_resources
1981 WHERE plan_id = i_plan_id
1982 AND organization_id = i_organization_id
1983 AND standard_operation_id = i_operation_id
1984 AND resource_assigned > 0;
1985
1986 CURSOR res_overcapacity_ln(i_line_id NUMBER) IS
1987 SELECT max(bottleneck_resource_percent) over_pct
1988 FROM flm_mmm_operations
1989 WHERE plan_id = i_plan_id
1990 AND organization_id = i_organization_id
1991 AND line_id = i_line_id
1992 AND operation_type = i_calc_op_type;
1993
1994 CURSOR ipk_overcapacity(i_line_id NUMBER) IS
1995 SELECT max(100*(ipk_needed-ipk_assigned)/ipk_assigned) over_pct
1996 FROM flm_mmm_operations
1997 WHERE plan_id = i_plan_id
1998 AND organization_id = i_organization_id
1999 AND line_id = i_line_id
2000 AND operation_type = i_calc_op_type
2001 AND ipk_assigned > 0;
2002
2003 BEGIN
2004 -- Resource Overcapacity For Operations
2005 FOR op_rec IN operations LOOP -- For each operation
2006 -- Infinite Over Capacity?
2007 SELECT count(*)
2008 INTO l_count
2009 FROM flm_mmm_op_resources
2010 WHERE plan_id = i_plan_id
2011 AND organization_id = i_organization_id
2012 AND standard_operation_id = op_rec.standard_operation_id
2013 AND (resource_needed > 0 or resource_needed is null)
2014 AND resource_assigned = 0;
2015
2016 IF l_count <= 0 THEN
2017 OPEN res_overcapacity_op(op_rec.standard_operation_id);
2018 FETCH res_overcapacity_op INTO l_res_over_pct;
2019 CLOSE res_overcapacity_op;
2020
2021 IF l_res_over_pct < 0 OR l_res_over_pct IS NULL THEN
2022 l_res_over_pct := 0;
2023 END IF;
2024
2025 UPDATE flm_mmm_operations
2026 SET bottleneck_resource_percent = l_res_over_pct,
2027 last_updated_by = fnd_global.user_id,
2028 last_update_date = sysdate,
2029 last_update_login = fnd_global.user_id
2030 WHERE plan_id = i_plan_id
2031 AND organization_id = i_organization_id
2032 AND standard_operation_id = op_rec.standard_operation_id;
2033
2034 ELSE
2035 UPDATE flm_mmm_operations
2036 SET bottleneck_resource_percent = NULL,
2037 last_updated_by = fnd_global.user_id,
2038 last_update_date = sysdate,
2039 last_update_login = fnd_global.user_id
2040 WHERE plan_id = i_plan_id
2041 AND organization_id = i_organization_id
2042 AND standard_operation_id = op_rec.standard_operation_id;
2043
2044 END IF;
2045 END LOOP; -- op_rec
2046
2047 -- Resource/IPK Overcapacity For Lines
2048 FOR line_rec IN lines LOOP
2049 -- Resource
2050 SELECT count(*)
2051 INTO l_count
2052 FROM flm_mmm_operations
2053 WHERE plan_id = i_plan_id
2054 AND organization_id = i_organization_id
2055 AND line_id = line_rec.line_id
2056 AND operation_type = i_calc_op_type
2057 AND bottleneck_resource_percent IS NULL;
2058
2059 IF l_count <= 0 THEN
2060 OPEN res_overcapacity_ln(line_rec.line_id);
2061 FETCH res_overcapacity_ln INTO l_res_over_pct;
2062 CLOSE res_overcapacity_ln;
2063
2064 IF l_res_over_pct < 0 THEN
2065 l_res_over_pct := 0;
2066 END IF;
2067
2068 ELSE
2069 l_res_over_pct := NULL;
2070 END IF;
2071
2072 -- IPK
2073 SELECT count(*)
2074 INTO l_count
2075 FROM flm_mmm_operations
2076 WHERE plan_id = i_plan_id
2077 AND organization_id = i_organization_id
2078 AND line_id = line_rec.line_id
2079 AND operation_type = i_calc_op_type
2080 AND (ipk_needed > 0 or ipk_needed IS NULL)
2081 AND ipk_assigned = 0;
2082
2083 IF l_count <= 0 THEN -- No Infinite Overcapacity
2084 OPEN ipk_overcapacity(line_rec.line_id);
2085 FETCH ipk_overcapacity INTO l_ipk_over_pct;
2086 CLOSE ipk_overcapacity;
2087
2088 IF l_ipk_over_pct < 0 OR l_ipk_over_pct IS NULL THEN
2089 l_ipk_over_pct := 0;
2090 END IF;
2091
2092 -- Update
2093 UPDATE flm_mmm_lines
2094 SET bottleneck_resource_percent = l_res_over_pct,
2095 bottleneck_ipk_percent = l_ipk_over_pct,
2096 last_updated_by = fnd_global.user_id,
2097 last_update_date = sysdate,
2098 last_update_login = fnd_global.user_id
2099 WHERE plan_id = i_plan_id
2100 AND organization_id = i_organization_id
2101 AND line_id = line_rec.line_id
2102 AND calculation_operation_type = i_calc_op_type;
2103
2104 ELSE
2105 -- Update
2106 UPDATE flm_mmm_lines
2107 SET bottleneck_resource_percent = l_res_over_pct,
2108 bottleneck_ipk_percent = NULL,
2109 last_updated_by = fnd_global.user_id,
2110 last_update_date = sysdate,
2111 last_update_login = fnd_global.user_id
2112 WHERE plan_id = i_plan_id
2113 AND organization_id = i_organization_id
2114 AND line_id = line_rec.line_id
2115 AND calculation_operation_type = i_calc_op_type;
2116
2117 END IF;
2118 END LOOP;
2119 END calculate_res_ipk_overcapacity;
2120
2121
2122 /************************************************************************
2123 * PROCEDURE calculate_res_ipk_undercap *
2124 * Calculates the undercapacity percentage for resources and IPKs *
2125 * for each line and line operation/process. *
2126 ************************************************************************/
2127 PROCEDURE calculate_res_ipk_undercap(
2128 i_plan_id IN NUMBER,
2129 i_organization_id IN NUMBER,
2130 i_calc_op_type IN NUMBER) IS
2131
2132 l_res_under_pct NUMBER;
2133 l_ipk_under_pct NUMBER;
2134
2135 l_count NUMBER;
2136 l_temp NUMBER;
2137
2138 CURSOR lines IS
2139 SELECT line_id
2140 FROM flm_mmm_lines
2141 WHERE plan_id = i_plan_id
2142 AND organization_id = i_organization_id
2143 AND calculation_operation_type = i_calc_op_type;
2144
2145 CURSOR operations IS
2146 SELECT standard_operation_id
2147 FROM flm_mmm_operations
2148 WHERE plan_id = i_plan_id
2149 AND organization_id = i_organization_id
2150 AND operation_type = i_calc_op_type;
2151
2152 -- retrieve all resource assigned and needed
2153 CURSOR res_assigned_needed(i_operation_id NUMBER) IS
2154 SELECT resource_assigned,resource_needed
2155 FROM flm_mmm_op_resources
2156 WHERE plan_id = i_plan_id
2157 AND organization_id = i_organization_id
2158 AND standard_operation_id = i_operation_id;
2159
2160 CURSOR res_undercapacity_ln(i_line_id NUMBER) IS
2161 SELECT max(resource_undercapacity) under_pct
2162 FROM flm_mmm_operations
2163 WHERE plan_id = i_plan_id
2164 AND organization_id = i_organization_id
2165 AND line_id = i_line_id
2166 AND operation_type = i_calc_op_type;
2167
2168 -- retrieve all ipk assigned and needed
2169 CURSOR ipk_assigned_needed(i_line_id NUMBER) IS
2170 SELECT ipk_assigned, ipk_needed
2171 FROM flm_mmm_operations
2172 WHERE plan_id = i_plan_id
2173 AND organization_id = i_organization_id
2174 AND line_id = i_line_id
2175 AND operation_type = i_calc_op_type;
2176
2177 BEGIN
2178 -- Resource Undercapacity For Operations
2179 FOR op_rec IN operations LOOP -- For each operation
2180 l_res_under_pct := 0; -- initial undercapacity pct
2181
2182 FOR res_rec IN res_assigned_needed(op_rec.standard_operation_id) LOOP
2183
2184 -- if OC(overcapacity) instead of UC(undercapacity), current UC remains the same
2185 IF res_rec.resource_assigned IS NULL OR
2186 res_rec.resource_assigned = 0 OR
2187 res_rec.resource_needed > res_rec.resource_assigned THEN
2188 NULL;
2189
2190 -- else, calculate and update as necessary
2191 ELSE
2192
2193 l_temp := 100*(res_rec.resource_assigned-res_rec.resource_needed)/res_rec.resource_assigned;
2194
2195 IF l_temp>l_res_under_pct THEN
2196 l_res_under_pct := l_temp;
2197 END IF;
2198
2199 END IF;
2200 END LOOP;
2201
2202 UPDATE flm_mmm_operations
2203 SET resource_undercapacity = l_res_under_pct,
2204 last_updated_by = fnd_global.user_id,
2205 last_update_date = sysdate,
2206 last_update_login = fnd_global.user_id
2207 WHERE plan_id = i_plan_id
2208 AND organization_id = i_organization_id
2209 AND standard_operation_id = op_rec.standard_operation_id;
2210
2211 END LOOP; -- new_op_rec
2212
2213 -- Resource/IPK Undercapacity For Lines
2214 FOR line_rec IN lines LOOP
2215
2216 -- Resource
2217 OPEN res_undercapacity_ln(line_rec.line_id);
2218 FETCH res_undercapacity_ln INTO l_res_under_pct;
2219 CLOSE res_undercapacity_ln;
2220
2221 -- IPK
2222 l_ipk_under_pct := 0;
2223
2224 FOR ipk_rec IN ipk_assigned_needed(line_rec.line_id) LOOP
2225
2226 -- if OC instead of UC, current UC remains the same
2227 IF ipk_rec.ipk_assigned IS NULL OR
2228 ipk_rec.ipk_assigned = 0 OR
2229 ipk_rec.ipk_needed > ipk_rec.ipk_assigned THEN
2230 NULL;
2231
2232 -- else, calculate and update as necessary
2233 ELSE
2234 l_temp := 100*(ipk_rec.ipk_assigned-ipk_rec.ipk_needed)/ipk_rec.ipk_assigned;
2235
2236 IF l_temp>l_ipk_under_pct THEN
2237 l_ipk_under_pct := l_temp;
2238 END IF;
2239
2240 END IF;
2241 END LOOP;
2242
2243 -- Update
2244 UPDATE flm_mmm_lines
2245 SET resource_undercapacity = l_res_under_pct,
2246 ipk_undercapacity = l_ipk_under_pct,
2247 last_updated_by = fnd_global.user_id,
2248 last_update_date = sysdate,
2249 last_update_login = fnd_global.user_id
2250 WHERE plan_id = i_plan_id
2251 AND organization_id = i_organization_id
2252 AND line_id = line_rec.line_id
2253 AND calculation_operation_type = i_calc_op_type;
2254
2255 END LOOP;
2256 END calculate_res_ipk_undercap;
2257
2258 /************************************************************************
2259 * PROCEDURE calculate_process_efficiency *
2260 * Calculates the process efficiency for each *
2261 * line operation/process. *
2262 ************************************************************************/
2263 PROCEDURE calculate_process_efficiency(
2264 i_plan_id IN NUMBER,
2265 i_organization_id IN NUMBER,
2266 i_calc_op_type IN NUMBER) IS
2267
2268 l_weighted_process_efficiency NUMBER;
2269 l_operation_process_volume NUMBER;
2270
2271 CURSOR operations IS
2272 SELECT line_id,
2273 standard_operation_id
2274 FROM flm_mmm_operations
2275 WHERE plan_id = i_plan_id
2276 AND organization_id = i_organization_id
2277 AND operation_type = i_calc_op_type;
2278
2279 BEGIN
2280 -- Weighted Process Efficiency For Operations
2281 FOR operation_rec IN operations LOOP -- For each operation
2282 SELECT sum(nvl(bos.total_process_efficiency, 0) * nvl(it.process_volume, 0) ),
2283 sum(nvl(it.process_volume, 0))
2284 INTO l_weighted_process_efficiency,
2285 l_operation_process_volume
2286 FROM flm_mmm_op_items it,
2287 bom_operational_routings bor,
2288 bom_operation_sequences bos
2289 WHERE it.plan_id = i_plan_id
2290 AND it.organization_id = i_organization_id
2291 AND it.operation_type = i_calc_op_type
2292 AND it.line_id = operation_rec.line_id
2293 AND it.standard_operation_id = operation_rec.standard_operation_id
2294 AND bor.organization_id = i_organization_id
2295 AND bor.line_id = operation_rec.line_id
2296 AND bor.assembly_item_id = it.assembly_item_id
2297 AND bor.cfm_routing_flag = 1
2298 AND bor.mixed_model_map_flag =1
2299 AND bor.alternate_routing_designator IS NULL
2300 AND bor.common_routing_sequence_id = bos.routing_sequence_id
2301 AND bos.operation_type = i_calc_op_type
2302 AND bos.standard_operation_id = operation_rec.standard_operation_id;
2303
2304 IF (l_operation_process_volume <> 0) THEN
2305 UPDATE flm_mmm_operations
2306 SET process_efficiency = l_weighted_process_efficiency / l_operation_process_volume
2307 WHERE plan_id = i_plan_id
2308 AND organization_id = i_organization_id
2309 AND standard_operation_id = operation_rec.standard_operation_id;
2310 END IF;
2311
2312 END LOOP;
2313
2314 END calculate_process_efficiency;
2315
2316
2317 /************************************************************************
2318 * PROCEDURE delete_old_records *
2319 * *
2320 ************************************************************************/
2321 PROCEDURE delete_old_records(
2322 i_plan_id IN NUMBER,
2323 i_organization_id IN NUMBER,
2324 i_calc_op_type IN NUMBER,
2325 i_replan_flag IN VARCHAR2) IS
2326
2327 BEGIN
2328 -- FLM_MMM_LINES delete if not re-plan
2329 -- if re-plan, the records will be changed(delete, then insert)
2330 -- later (when calculating line takt).
2331 -- this is because we might need to use its line hours
2332 IF NOT (i_replan_flag = C_REPLAN_FLAG_YES) THEN
2333 DELETE FROM flm_mmm_lines
2334 WHERE plan_id = i_plan_id
2335 AND organization_id = i_organization_id
2336 AND calculation_operation_type = i_calc_op_type;
2337 END IF;
2338
2339 -- FLM_MMM_OPERATIONS delete if not re-plan
2340 -- if re-plan, the records will be changed(delete, then insert)
2341 -- when calculating operation takt.
2342 -- this is because we might need to use its ipk_assigned information
2343 IF NOT (i_replan_flag = C_REPLAN_FLAG_YES) THEN
2344 DELETE FROM flm_mmm_operations
2345 WHERE plan_id = i_plan_id
2346 AND organization_id = i_organization_id
2347 AND operation_type = i_calc_op_type;
2348 END IF;
2349
2350 -- FLM_MMM_OP_ITEMS
2351 DELETE FROM flm_mmm_op_items
2352 WHERE plan_id = i_plan_id
2353 AND organization_id = i_organization_id
2354 AND operation_type = i_calc_op_type;
2355
2356 -- FLM_MMM_OP_RESOURCES, delete if not re-plan
2357 -- if re-plan, the records will be changed(delete, then insert)
2358 -- when calculating weighted resource usage
2359 -- this is because we might need to use its resource_assigned information
2360 IF NOT (i_replan_flag = C_REPLAN_FLAG_YES) THEN
2361 DELETE FROM flm_mmm_op_resources
2362 WHERE plan_id = i_plan_id
2363 AND organization_id = i_organization_id
2364 AND operation_type = i_calc_op_type;
2365 END IF;
2366
2367 END delete_old_records;
2368
2369
2370 /************************************************************************
2371 * Published Procedures and Functions *
2372 ************************************************************************/
2373
2374 /************************************************************************
2375 * FUNCTION get_offset_date *
2376 * Gets the offset start date to be considered *
2377 * when we look at FORECAST demand. *
2378 ************************************************************************/
2379 FUNCTION get_offset_date (i_organization_id IN NUMBER,
2380 i_start_date IN DATE,
2381 i_bucket_type IN NUMBER)
2382 RETURN DATE IS
2383 l_offset_date date;
2384
2385 BEGIN
2386 IF i_bucket_type = C_BUCKET_DAYS THEN
2387 -- no offsetting here
2388 l_offset_date := i_start_date;
2389
2390 ELSIF i_bucket_type = C_BUCKET_WEEKS THEN
2391 SELECT bw.week_start_date
2392 INTO l_offset_date
2393 FROM bom_cal_week_start_dates bw,
2394 mtl_parameters mp
2395 WHERE mp.organization_id = i_organization_id
2396 AND bw.calendar_code = mp.calendar_code
2397 AND bw.exception_set_id = mp.calendar_exception_set_id
2398 AND bw.week_start_date <= i_start_date
2399 AND bw.next_date >= i_start_date;
2400
2401 ELSIF i_bucket_type = C_BUCKET_PERIODS THEN
2402 SELECT bp.period_start_date
2403 INTO l_offset_date
2404 FROM bom_period_start_dates bp,
2405 mtl_parameters mp
2406 WHERE mp.organization_id = i_organization_id
2407 AND bp.calendar_code = mp.calendar_code
2408 AND bp.exception_set_id = mp.calendar_exception_set_id
2409 AND bp.period_start_date <= i_start_date
2410 AND bp.next_date >= i_start_date;
2411
2412 END IF;
2413
2414 RETURN l_offset_date;
2415
2416 EXCEPTION
2417 WHEN OTHERS THEN
2418 RETURN i_start_date;
2419
2420 END get_offset_date;
2421
2422
2423 /************************************************************************
2424 * PROCEDURE calculate *
2425 * *
2426 ************************************************************************/
2427 PROCEDURE calculate(
2428 i_plan_id IN NUMBER,
2429 i_organization_id IN NUMBER,
2430 i_calculation_operation_type IN NUMBER,
2431 i_product_family_id IN NUMBER,
2432 i_line_id IN NUMBER,
2433 i_demand_type IN NUMBER,
2434 i_demand_code IN VARCHAR2,
2435 i_start_date IN DATE,
2436 i_end_date IN DATE,
2437 i_demand_days IN NUMBER,
2438 i_hours_per_day IN NUMBER,
2439 i_boost_percent IN NUMBER,
2440 i_calculation_option IN NUMBER,
2441 i_calendar_code IN VARCHAR2,
2442 i_exception_set_id IN NUMBER,
2443 i_last_calendar_date IN DATE,
2444 i_replan_flag IN VARCHAR2,
2445 o_error_code OUT NOCOPY NUMBER,
2446 o_error_msg OUT NOCOPY VARCHAR2) IS
2447
2448 l_demands t_demand_table;
2449 l_error_msg VARCHAR2(1000);
2450
2451 BEGIN
2452 -- Clean up history as necessary
2453 l_error_msg := 'delete old records. ';
2454 delete_old_records(
2455 i_plan_id,
2456 i_organization_id,
2457 i_calculation_operation_type,
2458 i_replan_flag);
2459
2460 -- Average Daily Demand
2461 l_demands.DELETE;
2462 l_error_msg := l_error_msg || 'retrieve demand. ';
2463 calculate_avg_daily_demand(
2464 i_plan_id,
2465 i_organization_id,
2466 i_product_family_id,
2467 i_line_id,
2468 i_demand_type,
2469 i_demand_code,
2470 i_start_date,
2471 i_end_date,
2472 i_demand_days,
2473 i_boost_percent,
2474 i_calendar_code,
2475 i_exception_set_id,
2476 i_last_calendar_date,
2477 l_demands);
2478
2479 -- Line TAKT
2480 l_error_msg := l_error_msg || 'calc line takt. ';
2481 calculate_line_takt(
2482 i_plan_id,
2483 i_organization_id,
2484 i_calculation_operation_type,
2485 i_line_id,
2486 i_hours_per_day,
2487 i_replan_flag,
2488 l_demands);
2489
2490 -- Process Volume
2491 l_error_msg := l_error_msg || 'calc process volume. ';
2492 l_error_msg := l_error_msg || i_plan_id || ':' || i_organization_id ||
2493 ':' || i_calculation_operation_type || ':' || l_demands.COUNT;
2494 calculate_process_volume(
2495 i_plan_id,
2496 i_organization_id,
2497 i_calculation_operation_type,
2498 l_demands);
2499
2500 -- Operation TAKT
2501 l_error_msg := l_error_msg || 'calc operation takt. ';
2502 calculate_operation_takt(
2503 i_plan_id,
2504 i_organization_id,
2505 i_calculation_operation_type,
2506 i_hours_per_day,
2507 i_replan_flag);
2508
2509 -- Weighted Times
2510 l_error_msg := l_error_msg || 'calc weighted times. ';
2511 calculate_weighted_times(
2512 i_plan_id,
2513 i_organization_id,
2514 i_calculation_operation_type);
2515
2516 -- Weighted Resource Usage
2517 l_error_msg := l_error_msg || 'calc weighted usage. ';
2518 calculate_weighted_res_usage(
2519 i_plan_id,
2520 i_organization_id,
2521 i_calculation_operation_type,
2522 i_replan_flag);
2523
2524 -- Resource/IPK Needed
2525 l_error_msg := l_error_msg || 'calc res/ipk needed. ';
2526 calculate_res_ipk_needed(
2527 i_plan_id,
2528 i_organization_id,
2529 i_calculation_operation_type,
2530 i_calculation_option);
2531
2532 -- TAKT For Assigned
2533 l_error_msg := l_error_msg || 'calc takt as assigned. ';
2534 calculate_takt_assigned(
2535 i_plan_id,
2536 i_organization_id,
2537 i_calculation_operation_type);
2538
2539 l_error_msg := l_error_msg || 'calc overcapacity. ';
2540 -- Resource/IPK Overcapacity
2541 calculate_res_ipk_overcapacity(
2542 i_plan_id,
2543 i_organization_id,
2544 i_calculation_operation_type);
2545
2546 l_error_msg := l_error_msg || 'calc undercapacity. ';
2547 -- Resource/IPK Undercapacity
2548 calculate_res_ipk_undercap(
2549 i_plan_id,
2550 i_organization_id,
2551 i_calculation_operation_type);
2552
2553 -- Process Efficiency
2554 l_error_msg := l_error_msg || 'calc process efficiency. ';
2555 calculate_process_efficiency(
2556 i_plan_id,
2557 i_organization_id,
2558 i_calculation_operation_type);
2559
2560 -- Commit Work
2561 commit;
2562 o_error_code := C_ERROR_CODE_SUCCESS;
2563
2564 EXCEPTION
2565 WHEN others then
2566 l_error_msg := 'ERROR: '||l_error_msg;
2567 o_error_msg := l_error_msg;
2568 o_error_code := C_ERROR_CODE_FAILURE;
2569
2570 END calculate;
2571
2572
2573 /************************************************************************
2574 * PROCEDURE recalculate *
2575 * *
2576 ************************************************************************/
2577 PROCEDURE recalculate(
2578 i_plan_id IN NUMBER,
2579 i_organization_id IN NUMBER,
2580 i_calculation_operation_type IN NUMBER,
2581 i_calculation_option IN NUMBER,
2582 i_standard_operation_id IN NUMBER,
2583 o_error_code OUT NOCOPY NUMBER,
2584 o_error_msg OUT NOCOPY VARCHAR2) IS
2585
2586 BEGIN
2587 -- Verify Input
2588 -- Resource/IPK Needed
2589 IF i_standard_operation_id IS NULL THEN
2590 calculate_res_ipk_needed(
2591 i_plan_id,
2592 i_organization_id,
2593 i_calculation_operation_type,
2594 i_calculation_option);
2595 ELSE
2596 calculate_needed_by_op(
2597 i_plan_id,
2598 i_organization_id,
2599 i_standard_operation_id,
2600 i_calculation_option);
2601 END IF;
2602
2603 -- TAKT For Assigned
2604 calculate_takt_assigned(
2605 i_plan_id,
2606 i_organization_id,
2607 i_calculation_operation_type);
2608
2609 -- Resource/IPK Overcapacity
2610 calculate_res_ipk_overcapacity(
2611 i_plan_id,
2612 i_organization_id,
2613 i_calculation_operation_type);
2614
2615 -- Resource/IPK Undercapacity
2616 calculate_res_ipk_undercap(
2617 i_plan_id,
2618 i_organization_id,
2619 i_calculation_operation_type);
2620
2621 o_error_code := C_ERROR_CODE_SUCCESS;
2622
2623 EXCEPTION
2624 WHEN others then
2625 o_error_msg := 'ERROR';
2626 o_error_code := C_ERROR_CODE_FAILURE;
2627
2628 END recalculate;
2629
2630
2631 /************************************************************************
2632 * PROCEDURE update_assigned_with_needed *
2633 * *
2634 ************************************************************************/
2635 PROCEDURE update_assigned_with_needed(
2636 i_plan_id IN NUMBER,
2637 i_organization_id IN NUMBER,
2638 i_line_id IN NUMBER,
2639 i_standard_operation_id IN NUMBER,
2640 i_resource_id IN NUMBER,
2641 i_calc_op_type IN NUMBER,
2642 o_error_code OUT NOCOPY NUMBER) IS
2643
2644 l_dummy NUMBER;
2645
2646 CURSOR lock_res IS
2647 SELECT resource_id
2648 FROM flm_mmm_op_resources
2649 WHERE plan_id = i_plan_id
2650 AND organization_id = i_organization_id
2651 AND operation_type = i_calc_op_type
2652 FOR UPDATE NOWAIT;
2653
2654 CURSOR lock_op IS
2655 SELECT standard_operation_id
2656 FROM flm_mmm_operations
2657 WHERE plan_id = i_plan_id
2658 AND organization_id = i_organization_id
2659 AND operation_type = i_calc_op_type
2660 FOR UPDATE NOWAIT;
2661
2662 CURSOR lock_res_res IS
2663 SELECT resource_id
2664 FROM flm_mmm_op_resources
2665 WHERE plan_id = i_plan_id
2666 AND organization_id = i_organization_id
2667 AND operation_type = i_calc_op_type
2668 AND resource_id = i_resource_id
2669 FOR UPDATE NOWAIT;
2670
2671 CURSOR lock_res_line IS
2672 SELECT resource_id
2673 FROM flm_mmm_op_resources
2674 WHERE plan_id = i_plan_id
2675 AND organization_id = i_organization_id
2676 AND operation_type = i_calc_op_type
2677 AND line_id = i_line_id
2678 FOR UPDATE NOWAIT;
2679
2680 CURSOR lock_op_line IS
2681 SELECT standard_operation_id
2682 FROM flm_mmm_operations
2683 WHERE plan_id = i_plan_id
2684 AND organization_id = i_organization_id
2685 AND operation_type = i_calc_op_type
2686 AND line_id = i_line_id
2687 FOR UPDATE NOWAIT;
2688
2689 CURSOR lock_res_op IS
2690 SELECT resource_id
2691 FROM flm_mmm_op_resources
2692 WHERE plan_id = i_plan_id
2693 AND organization_id = i_organization_id
2694 AND operation_type = i_calc_op_type
2695 AND standard_operation_id = i_standard_operation_id
2696 FOR UPDATE NOWAIT;
2697
2698 CURSOR lock_op_op IS
2699 SELECT standard_operation_id
2700 FROM flm_mmm_operations
2701 WHERE plan_id = i_plan_id
2702 AND organization_id = i_organization_id
2703 AND operation_type = i_calc_op_type
2704 AND standard_operation_id = i_standard_operation_id
2705 FOR UPDATE NOWAIT;
2706
2707 BEGIN
2708 -- Update
2709 IF i_line_id IS NULL AND i_resource_id IS NULL THEN -- for whole plan
2710 -- lock records
2711 FOR res_rec IN lock_res LOOP
2712 NULL;
2713 END LOOP;
2714
2715 FOR op_rec IN lock_op LOOP
2716 NULL;
2717 END LOOP;
2718
2719 -- update records
2720 UPDATE flm_mmm_op_resources
2721 SET resource_assigned = resource_needed,
2722 last_updated_by = fnd_global.user_id,
2723 last_update_date = sysdate,
2724 last_update_login = fnd_global.user_id
2725 WHERE plan_id = i_plan_id
2726 AND organization_id = i_organization_id
2727 AND operation_type = i_calc_op_type;
2728
2729 UPDATE flm_mmm_operations
2730 SET ipk_assigned = ipk_needed,
2731 last_updated_by = fnd_global.user_id,
2732 last_update_date = sysdate,
2733 last_update_login = fnd_global.user_id
2734 WHERE plan_id = i_plan_id
2735 AND organization_id = i_organization_id
2736 AND operation_type = i_calc_op_type;
2737
2738 ELSIF i_resource_id IS NOT NULL THEN -- for a specific resource
2739 -- lock records
2740 FOR res_rec IN lock_res_res LOOP
2741 NULL;
2742 END LOOP;
2743
2744 -- update records
2745 UPDATE flm_mmm_op_resources
2746 SET resource_assigned = resource_needed,
2747 last_updated_by = fnd_global.user_id,
2748 last_update_date = sysdate,
2749 last_update_login = fnd_global.user_id
2750 WHERE plan_id = i_plan_id
2751 AND organization_id = i_organization_id
2752 AND operation_type = i_calc_op_type
2753 AND resource_id = i_resource_id;
2754
2755 ELSIF i_line_id IS NOT NULL AND
2756 i_standard_operation_id IS NULL THEN -- for a specific line
2757 -- lock records
2758 FOR res_rec IN lock_res_line LOOP
2759 NULL;
2760 END LOOP;
2761
2762 FOR op_rec IN lock_op_line LOOP
2763 NULL;
2764 END LOOP;
2765
2766 -- update records
2767 UPDATE flm_mmm_op_resources
2768 SET resource_assigned = resource_needed,
2769 last_updated_by = fnd_global.user_id,
2770 last_update_date = sysdate,
2771 last_update_login = fnd_global.user_id
2772 WHERE plan_id = i_plan_id
2773 AND organization_id = i_organization_id
2774 AND operation_type = i_calc_op_type
2775 AND line_id = i_line_id;
2776
2777 UPDATE flm_mmm_operations
2778 SET ipk_assigned = ipk_needed,
2779 last_updated_by = fnd_global.user_id,
2780 last_update_date = sysdate,
2781 last_update_login = fnd_global.user_id
2782 WHERE plan_id = i_plan_id
2783 AND organization_id = i_organization_id
2784 AND operation_type = i_calc_op_type
2785 AND line_id = i_line_id;
2786
2787 ELSIF i_standard_operation_id IS NOT NULL THEN -- for a specific operation
2788 -- lock records
2789 FOR res_rec IN lock_res_op LOOP
2790 NULL;
2791 END LOOP;
2792
2793 FOR op_rec IN lock_op_op LOOP
2794 NULL;
2795 END LOOP;
2796
2797 -- update records
2798 UPDATE flm_mmm_op_resources
2799 SET resource_assigned = resource_needed,
2800 last_updated_by = fnd_global.user_id,
2801 last_update_date = sysdate,
2802 last_update_login = fnd_global.user_id
2803 WHERE plan_id = i_plan_id
2804 AND organization_id = i_organization_id
2805 AND operation_type = i_calc_op_type
2806 AND standard_operation_id = i_standard_operation_id;
2807
2808 UPDATE flm_mmm_operations
2809 SET ipk_assigned = ipk_needed,
2810 last_updated_by = fnd_global.user_id,
2811 last_update_date = sysdate,
2812 last_update_login = fnd_global.user_id
2813 WHERE plan_id = i_plan_id
2814 AND organization_id = i_organization_id
2815 AND operation_type = i_calc_op_type
2816 AND standard_operation_id = i_standard_operation_id;
2817 END IF; -- Input Types
2818
2819 -- Re-Calculate TAKT For Assigned
2820 calculate_takt_assigned(
2821 i_plan_id,
2822 i_organization_id,
2823 i_calc_op_type);
2824
2825 -- Re-Calculate Overcapacity
2826 calculate_res_ipk_overcapacity(
2827 i_plan_id,
2828 i_organization_id,
2829 i_calc_op_type);
2830
2831 -- Re-Calculate Undercapacity
2832 calculate_res_ipk_undercap(
2833 i_plan_id,
2834 i_organization_id,
2835 i_calc_op_type);
2836
2837 o_error_code := C_ERROR_CODE_SUCCESS;
2838
2839 EXCEPTION
2840 WHEN OTHERS THEN -- row lock
2841 o_error_code := C_ERROR_CODE_FAILURE;
2842
2843 END update_assigned_with_needed;
2844
2845
2846 /************************************************************************
2847 * PROCEDURE save *
2848 * Copies the Resource/IPK assigned info *
2849 * to the master set up area FROM this plan. *
2850 ************************************************************************/
2851 PROCEDURE save(
2852 i_plan_id IN NUMBER,
2853 i_organization_id IN NUMBER,
2854 i_operation_type IN NUMBER) IS
2855
2856 l_count NUMBER;
2857
2858 CURSOR all_operations IS
2859 SELECT standard_operation_id operation_id,
2860 ipk_assigned,
2861 line_id
2862 FROM flm_mmm_operations
2863 WHERE plan_id = i_plan_id
2864 AND organization_id = i_organization_id
2865 AND operation_type = i_operation_type;
2866
2867 CURSOR all_resources IS
2868 SELECT resource_id,
2869 standard_operation_id operation_id,
2870 resource_assigned,
2871 line_id
2872 FROM flm_mmm_op_resources
2873 WHERE plan_id = i_plan_id
2874 AND organization_id = i_organization_id
2875 AND operation_type = i_operation_type;
2876
2877 BEGIN
2878 -- IPKs
2879 FOR op_rec IN all_operations LOOP
2880 SELECT count(*)
2881 INTO l_count
2882 FROM flm_mmm_operations
2883 WHERE organization_id = i_organization_id
2884 AND plan_id = -1
2885 AND standard_operation_id = op_rec.operation_id;
2886
2887 IF l_count > 0 THEN
2888 UPDATE flm_mmm_operations
2889 SET ipk_assigned = op_rec.ipk_assigned,
2890 last_updated_by = fnd_global.user_id,
2891 last_update_date = sysdate,
2892 last_update_login = fnd_global.user_id
2893 WHERE organization_id = i_organization_id
2894 AND plan_id = -1
2895 AND standard_operation_id = op_rec.operation_id;
2896
2897 ELSE
2898 INSERT INTO FLM_MMM_OPERATIONS (
2899 PLAN_ID,
2900 ORGANIZATION_ID,
2901 STANDARD_OPERATION_ID,
2902 LAST_UPDATE_DATE,
2903 LAST_UPDATED_BY,
2904 CREATION_DATE,
2905 CREATED_BY,
2906 LAST_UPDATE_LOGIN,
2907 OPERATION_TYPE,
2908 LINE_ID,
2909 IPK_ASSIGNED
2910 ) VALUES (
2911 -1,
2912 i_organization_id,
2913 op_rec.operation_id,
2914 sysdate,
2915 fnd_global.user_id,
2916 sysdate,
2917 fnd_global.user_id,
2918 fnd_global.user_id,
2919 i_operation_type,
2920 op_rec.LINE_ID,
2921 op_rec.ipk_assigned
2922 );
2923
2924 END IF;
2925 END LOOP;
2926
2927 -- Resources
2928 FOR res_rec IN all_resources LOOP
2929 SELECT count(*)
2930 INTO l_count
2931 FROM flm_mmm_op_resources
2932 WHERE organization_id = i_organization_id
2933 AND plan_id = -1
2934 AND resource_id = res_rec.resource_id
2935 AND standard_operation_id = res_rec.operation_id;
2936
2937 IF l_count > 0 THEN
2938 UPDATE flm_mmm_op_resources
2939 SET resource_assigned = res_rec.resource_assigned,
2940 last_updated_by = fnd_global.user_id,
2941 last_update_date = sysdate,
2942 last_update_login = fnd_global.user_id
2943 WHERE organization_id = i_organization_id
2944 AND plan_id = -1
2945 AND resource_id = res_rec.resource_id
2946 AND standard_operation_id = res_rec.operation_id;
2947
2948 ELSE
2949 INSERT INTO FLM_MMM_OP_RESOURCES (
2950 PLAN_ID,
2951 ORGANIZATION_ID,
2952 RESOURCE_ID,
2953 STANDARD_OPERATION_ID,
2954 LAST_UPDATE_DATE,
2955 LAST_UPDATED_BY,
2956 CREATION_DATE,
2957 CREATED_BY,
2958 LAST_UPDATE_LOGIN,
2959 OPERATION_TYPE,
2960 LINE_ID,
2961 RESOURCE_ASSIGNED
2962 ) VALUES (
2963 -1,
2964 i_organization_id,
2965 res_rec.resource_id,
2966 res_rec.operation_id,
2967 sysdate,
2968 fnd_global.user_id,
2969 sysdate,
2970 fnd_global.user_id,
2971 fnd_global.user_id,
2972 i_operation_type,
2973 res_rec.line_id,
2974 res_rec.resource_assigned
2975 );
2976
2977 END IF;
2978 END LOOP;
2979 END save;
2980
2981
2982 END flm_mmm_calculation;