[Home] [Help]
PACKAGE BODY: APPS.BOM_MIXED_MODEL_MAP_PVT
Source
1 PACKAGE BODY BOM_Mixed_Model_Map_PVT AS
2 /* $Header: BOMMMMCB.pls 120.1 2006/03/16 17:36:37 yulin noship $ */
3
4 G_FORECAST CONSTANT NUMBER := 1;
5 G_MDS CONSTANT NUMBER := 2;
6 G_MPS CONSTANT NUMBER := 3;
7 G_ACTUAL_PRODUCTION CONSTANT NUMBER := 4;
8 -- Added for enhancement bug #2538897
9 G_SALES_ORDERS CONSTANT NUMBER := 5;
10 G_PLANNED_ORDERS CONSTANT NUMBER := 6;
11
12 G_USER_CALC CONSTANT NUMBER := 2;
13 G_TOTAL_IPK CONSTANT NUMBER := 1;
14 G_DECIMAL CONSTANT NUMBER := 2;
15 G_PROCESS CONSTANT NUMBER := 2;
16 G_LINEOP CONSTANT NUMBER := 3;
17 G_HOUR CONSTANT NUMBER := 1;
18 G_MINUTE CONSTANT NUMBER := 2;
19 G_SECOND CONSTANT NUMBER := 3;
20 G_DAYS CONSTANT NUMBER := 1;
21 G_WEEKS CONSTANT NUMBER := 2;
22 G_PERIODS CONSTANT NUMBER := 3;
23 --below is new code
24 TYPE resource_id_type is TABLE of bom_resources.resource_id%TYPE
25 INDEX BY BINARY_INTEGER;
26 TYPE dept_id_type is TABLE of bom_department_resources.department_id%TYPE
27 INDEX BY BINARY_INTEGER;
28 --above is new code
29
30 -- ========================== ConvertTime ===================================
31
32 -- NAME : ConvertTime
33 -- DESCRIPTION: Converts "hour" input value into "Minutes" or "Seconds"
34 -- depending on p_time_uom.
35 -- REQUIRES : err_text out buffer to return error message
36 -- MODIFIES :
37 --
38 -- ==========================================================================
39
40 FUNCTION ConvertTime (p_input NUMBER, p_time_uom NUMBER) RETURN number IS
41 l_output NUMBER;
42 BEGIN
43 IF (p_time_uom = G_MINUTE) then
44 l_output := p_input * 60;
45 ELSIF (p_time_uom = G_SECOND) THEN
46 l_output := p_input * 3600;
47 ELSIF (p_time_uom = G_HOUR) THEN
48 l_output := p_input;
49 END IF;
50
51 RETURN l_output;
52 END ConvertTime;
53
54
55 -- ============================== GetBuckets ================================
56 --
57 -- NAME : GetBuckets
58 -- DESCRIPTION: Calculates number of buckets between a Start Date and an
59 -- End Date for Months or Periods
60 -- REQUIRES : x_calendar_code => Name of Calendar
61 -- x_exception_set_id => ID of calendar exception set
62 -- x_bucket => Bucket Type
63 -- x_start_date => Start Date
64 -- x_end_date => End Date
65 -- MODIFIES :
66 --
67 -- ==========================================================================
68
69 FUNCTION GetBuckets (x_calendar_code VARCHAR2,
70 x_exception_set_id NUMBER,
71 x_bucket NUMBER,
72 x_start_date DATE,
73 x_end_date DATE ) RETURN number IS
74 l_buckets NUMBER;
75 BEGIN
76 IF (x_bucket = G_WEEKS) THEN
77 SELECT count(week_start_date)
78 INTO l_buckets
79 FROM bom_cal_week_start_dates cal
80 WHERE cal.exception_set_id = x_exception_set_id
81 AND cal.calendar_code = x_calendar_code
82 AND cal.week_start_date between x_start_date
83 and x_end_date;
84 ELSIF (x_bucket = G_PERIODS) THEN
85 SELECT count(period_start_date)
86 INTO l_buckets
87 FROM bom_period_start_dates cal
88 WHERE cal.exception_set_id = x_exception_set_id
89 AND cal.calendar_code = x_calendar_code
90 AND cal.period_start_date between x_start_date
91 and x_end_date;
92 END IF;
93
94 RETURN l_buckets;
95
96 END GetBuckets;
97
98 -- ========================== GetWorkdaysBetween ============================
99 --
100 -- NAME : GetWorkdaysBetween
101 -- DESCRIPTION: Calculates number of workdays between a Start Date and an
102 -- End Date
103 -- REQUIRES : x_calendar_code => Name of Calendar
104 -- x_exception_set_id => ID of calendar exception set
105 -- x_start_date => Start Date
106 -- x_end_date => End Date
107 -- MODIFIES :
108 --
109 -- ==========================================================================
110
111 FUNCTION GetWorkdaysBetween (x_calendar_code VARCHAR2,
112 x_exception_set_id NUMBER,
113 x_start_date DATE,
114 x_end_date DATE ) RETURN number IS
115 l_days NUMBER;
116 BEGIN
117 SELECT count(*)
118 INTO l_days
119 FROM bom_calendar_dates
120 WHERE calendar_code = x_calendar_code
121 AND exception_set_id = x_exception_set_id
122 AND calendar_date BETWEEN x_start_date AND x_end_date
123 AND seq_num IS NOT NULL;
124
125 RETURN l_days;
126 END GetWorkdaysBetween;
127
128
129 -- ============================ GetLastWeekDay =============================
130 --
131 -- NAME : GetLastWeekDay
132 -- DESCRIPTION: Calculates the last day of a week
133 -- REQUIRES : x_calendar_code => Name of Calendar
134 -- x_exception_set_id => ID of calendar exception set
135 -- x_last_calendar_date => Last day in the mfg calendar
136 -- x_rate_end_date => Rate End Date
137 -- x_forecast_date => Forecast Date
138 -- MODIFIES :
139 --
140 -- ==========================================================================
141
142 FUNCTION GetLastWeekDay (x_calendar_code VARCHAR2,
143 x_exception_set_id NUMBER,
144 x_last_calendar_date DATE,
145 x_rate_end_date DATE,
146 x_forecast_date DATE ) RETURN date IS
147 l_last_week_day DATE;
148
149 CURSOR GetLastDay IS
150 SELECT decode(next_date, week_start_date,
151 x_last_calendar_date, (next_date -1)) last_day
152 FROM bom_cal_week_start_dates
153 WHERE calendar_code = x_calendar_code
154 AND exception_set_id = x_exception_set_id
155 AND week_start_date = nvl(x_rate_end_date, x_forecast_date);
156 BEGIN
157 FOR c1 IN GetLastDay LOOP
158 l_last_week_day := c1.last_day;
159 END LOOP;
160
161 RETURN l_last_week_day;
162 END GetLastWeekDay;
163
164 -- ============================ GetLastPeriodDay ===========================
165 --
166 -- NAME : GetLastPeriodDay
167 -- DESCRIPTION: Calculates the last day of a period
168 -- REQUIRES : x_calendar_code => Name of Calendar
169 -- x_exception_set_id => ID of calendar exception set
170 -- x_last_calendar_date => Last day in the mfg calendar
171 -- x_rate_end_date => Rate End Date
172 -- x_forecast_date => Forecast Date
173 -- MODIFIES :
174 --
175 -- ==========================================================================
176
177 FUNCTION GetLastPeriodDay (x_calendar_code VARCHAR2,
178 x_exception_set_id NUMBER,
179 x_last_calendar_date DATE,
180 x_rate_end_date DATE,
181 x_forecast_date DATE ) RETURN date IS
182 l_last_period_day DATE;
183 CURSOR GetLastDay IS
184 SELECT decode(next_date, period_start_date,
185 x_last_calendar_date, (next_date -1)) last_day
186 FROM bom_period_start_dates
187 WHERE calendar_code = x_calendar_code
188 AND exception_set_id = x_exception_set_id
189 AND period_start_date = nvl(x_rate_end_date, x_forecast_date);
190 BEGIN
191 FOR c1 IN GetLastDay LOOP
192 l_last_period_day := c1.last_day;
193 END LOOP;
194
195 RETURN l_last_period_day;
196 END GetLastPeriodDay;
197
198 --below is my code
199 PROCEDURE GetResourceId(
200 p_assembly_item_id NUMBER,
201 p_org_id NUMBER,
202 p_line_id NUMBER,
203 p_resource_type NUMBER,
204 p_op_code VARCHAR2,
205 p_process_line_op NUMBER,
206 resource_index IN OUT NOCOPY NUMBER,
207 resource_dept_table IN OUT NOCOPY dept_id_type,
208 resource_table IN OUT NOCOPY resource_id_type ) IS
209
210 CURSOR GetLineOpResourceId IS
211 SELECT bopr.resource_id id , bosv.department_id dept_id
212 FROM bom_operation_resources bopr,
213 bom_resources br,
214 bom_operation_sequences_v bosv,
215 bom_operational_routings bor
216 WHERE bopr.operation_sequence_id= bosv.operation_sequence_id
217 AND bopr.resource_id = br.resource_id
218 AND br.resource_type = p_resource_type
219 AND bor.mixed_model_map_flag = 1
220 AND bor.line_id = p_line_id
221 AND bor.assembly_item_id = p_assembly_item_id
222 AND bor.organization_id = p_org_id
223 AND bor.common_routing_sequence_id = bosv.routing_sequence_id
224 AND bosv.operation_type = 1
225 AND bosv.line_op_code = p_op_code;
226
227 CURSOR GetProcessResourceId IS
228 SELECT bopr.resource_id id , bosv.department_id dept_id
229 FROM bom_operation_resources bopr,
230 bom_resources br,
231 bom_operation_sequences_v bosv,
232 bom_operational_routings bor
233 WHERE bopr.operation_sequence_id= bosv.operation_sequence_id
234 AND bopr.resource_id = br.resource_id
235 AND br.resource_type = p_resource_type
236 AND bor.mixed_model_map_flag = 1
237 AND bor.line_id = p_line_id
238 AND bor.assembly_item_id = p_assembly_item_id
239 AND bor.organization_id = p_org_id
240 AND bor.common_routing_sequence_id = bosv.routing_sequence_id
241 AND bosv.operation_type = 1
242 AND bosv.process_code = p_op_code;
243
244
245 flag_res_stored NUMBER := 0;
246 BEGIN
247 flag_res_stored := 0;
248 IF ( p_process_line_op = G_PROCESS ) THEN
249 FOR c1rec IN GetProcessResourceId LOOP
250 FOR i IN 1..resource_index - 1 LOOP
251 IF c1rec.id = resource_table(i) AND
252 c1rec.dept_id = resource_dept_table(i) THEN
253 flag_res_stored := 1;
254 exit;
255 END IF;
256 END LOOP;
257 IF (flag_res_stored = 0) THEN
258 resource_table(resource_index) := c1rec.id;
259 resource_dept_table(resource_index) := c1rec.dept_id;
260 resource_index := resource_index + 1;
261 ELSE
262 flag_res_stored := 0;
263 END IF;
264 END LOOP; -- end for loop
265 ELSIF ( p_process_line_op = G_LINEOP ) THEN
266 FOR c1rec IN GetLineOpResourceId LOOP
267 FOR i IN 1..resource_index - 1 LOOP
268 IF c1rec.id = resource_table(i) AND
269 c1rec.dept_id = resource_dept_table(i) THEN
270 flag_res_stored := 1;
271 exit;
272 END IF;
273 END LOOP;
274 IF (flag_res_stored = 0) THEN
275 resource_table(resource_index) := c1rec.id;
276 resource_dept_table(resource_index) := c1rec.dept_id;
277 resource_index := resource_index + 1;
278 ELSE
279 flag_res_stored := 0;
280 END IF;
281 END LOOP; -- end for loop
282 END IF;
283 END;
284
285 PROCEDURE GetCapacity(
286 p_resource_index IN NUMBER,
287 p_resource_table IN resource_id_type,
288 p_dept_table IN dept_id_type,
289 x_capacity OUT NOCOPY NUMBER ) IS
290
291 BEGIN
292 x_capacity := 0;
293 FOR i IN 1..p_resource_index - 1 LOOP
294 DECLARE
295 CURSOR GetCapacityUnits IS
296 SELECT bdr.capacity_units CU
297 FROM bom_department_resources bdr
298 WHERE bdr.department_id = p_dept_table(i)
299 AND bdr.resource_id = p_resource_table(i);
300 BEGIN
301 FOR c1rec IN GetCapacityUnits LOOP
302 x_capacity := x_capacity + c1rec.CU;
303 END LOOP;
304 END;
305 END LOOP;-- aggregate machine capacity
306 END;
307 --above is my code
308
309 -- ========================== GetProcessLineOp ==============================
310 --
311 -- NAME : GetProcessLineOp
312 -- DESCRIPTION: Get Processes or Line Operations for a given Line
313 -- (and Family if the Family is specified by the user).
314 -- Insert these values into the table
315 -- BOM_MIXED_MODEL_MAP_PROCESSES.
316 -- REQUIRES : err_text out buffer to return error message
317 -- MODIFIES : BOM_MIXED_MODEL_MAP_PROCESSES
318 --
319 -- ==========================================================================
320
321 PROCEDURE GetProcessLineOp (
322 p_mmm_id NUMBER,
323 p_org_id NUMBER,
324 p_line_id NUMBER,
325 p_family_item_id NUMBER,
326 p_operation_type NUMBER,
327 p_order NUMBER,
328 p_user_id NUMBER,
329 x_err_text OUT NOCOPY VARCHAR2) IS
330
331 -- GET ALL PROCESSES/LINE OPS THAT BELONG TO ROUTINGS ASSOCIATED WITH
332 -- THE GIVEN LINE. IF "FAMILY" IS ALSO SPECIFIED, THEN ROUTING ITEMS
333 -- MUST BE MEMBERS OF THE GIVEN FAMILY.
334
335 CURSOR c1 IS
336 SELECT distinct bso.operation_code OC, bso.sequence_num SN
337 FROM bom_standard_operations bso,
338 bom_operation_sequences bos,
339 bom_operational_routings bor
340 WHERE bor.mixed_model_map_flag = 1
341 AND bor.line_id = p_line_id
342 AND bor.organization_id = p_org_id
343 AND bor.common_routing_sequence_id = bos.routing_sequence_id
344 AND bos.operation_type = p_operation_type
345 AND NVL(bos.eco_for_production,2) = 2
346 AND bso.standard_operation_id = bos.standard_operation_id
347 AND (p_family_item_id is null
348 OR
349 EXISTS (SELECT 'x'
350 FROM mtl_system_items msi
351 WHERE msi.inventory_item_id = bor.assembly_item_id
352 AND msi.organization_id = p_org_id
353 AND msi.product_family_item_id = p_family_item_id))
354 ORDER BY bso.sequence_num;
355
356
357 -- SAME AS C1, EXCEPT ORDER BY IS DIFFERENT
358 CURSOR c2 IS
359 SELECT distinct bso.operation_code OC, bso.sequence_num SN
360 FROM bom_standard_operations bso,
361 bom_operation_sequences bos,
362 bom_operational_routings bor
363 WHERE bor.mixed_model_map_flag = 1
364 AND bor.line_id = p_line_id
365 AND bor.organization_id = p_org_id
366 AND bor.common_routing_sequence_id = bos.routing_sequence_id
367 AND bos.operation_type = p_operation_type
368 AND NVL(bos.eco_for_production,2) = 2
369 AND bso.standard_operation_id = bos.standard_operation_id
370 AND (p_family_item_id is null
371 OR
372 EXISTS (SELECT 'x'
373 FROM mtl_system_items msi
374 WHERE msi.inventory_item_id = bor.assembly_item_id
375 AND msi.organization_id = p_org_id
376 AND msi.product_family_item_id = p_family_item_id))
377 ORDER BY bso.operation_code;
378
379
380 l_stmt_num NUMBER;
381 l_group_number NUMBER := 1;
382 l_seq_id NUMBER := 0;
383
384 BEGIN
385
386 -- INSERT PROCESSES/LINE OPS INTO TABLE IN GROUPS OF 5 ORDERED CORRECTLY
387
388 l_stmt_num := 1;
389
390 IF (p_order = 1) THEN
391 FOR c1rec IN c1 LOOP
392 l_stmt_num := 2;
393 l_seq_id := l_seq_id + 1;
394
395 IF (l_seq_id <= 5) THEN
396 null;
397 ELSE
398 l_group_number := l_group_number + 1;
399 l_seq_id := 1;
400 END IF;
401
402 l_stmt_num := 3;
403 INSERT INTO bom_mixed_model_map_processes
404 (MIXED_MODEL_MAP_ID,
405 GROUP_NUMBER,
406 SEQUENCE_ID,
407 OPERATION_CODE,
408 CREATED_BY,
409 CREATION_DATE,
410 LAST_UPDATED_BY,
411 LAST_UPDATE_DATE)
412 VALUES
413 (p_mmm_id,
414 l_group_number,
415 l_seq_id,
416 c1rec.OC,
417 p_user_id,
418 sysdate,
419 p_user_id,
420 sysdate);
421 END LOOP;
422 ELSE
423 FOR c2rec IN c2 LOOP
424 l_stmt_num := 2;
425 l_seq_id := l_seq_id + 1;
426
427 IF (l_seq_id <= 5) THEN
428 null;
429 ELSE
430 l_group_number := l_group_number + 1;
431 l_seq_id := 1;
432 END IF;
433
434 l_stmt_num := 3;
435 INSERT INTO bom_mixed_model_map_processes
436 (MIXED_MODEL_MAP_ID,
437 GROUP_NUMBER,
438 SEQUENCE_ID,
439 OPERATION_CODE,
440 CREATED_BY,
441 CREATION_DATE,
442 LAST_UPDATED_BY,
443 LAST_UPDATE_DATE)
444 VALUES
445 (p_mmm_id,
446 l_group_number,
447 l_seq_id,
448 c2rec.OC,
449 p_user_id,
450 sysdate,
451 p_user_id,
452 sysdate);
453 END LOOP;
454 END IF;
455
456 COMMIT;
457 EXCEPTION
458 WHEN others THEN
459 x_err_text := 'BOM_Mixed_Model_Map_PVT(GetProcessLineOp-'||l_stmt_num||
460 ') '||substrb(SQLERRM,1,500);
461 END GetProcessLineOp;
462
463
464 -- =============================== GetDemands =================================
465 --
466 -- NAME : GetDemand
467 -- DESCRIPTION: Get the total demand of a product with respect to
468 -- one sort of demand out of Forcast, MPS/MDS, Actual Production.
469 --
470 -- ==========================================================================
471 PROCEDURE GetDemand (
472 p_org_id NUMBER,
473 p_demand_type NUMBER,
474 p_line_id NUMBER,
475 p_assembly_item_id NUMBER,
476 p_calendar_code VARCHAR2,
477 p_start_date DATE,
478 p_end_date DATE,
479 p_last_calendar_date DATE,
480 p_exception_set_id NUMBER,
481 p_demand_code VARCHAR2,
482 o_demand OUT NOCOPY NUMBER,
483 o_stmt_num OUT NOCOPY NUMBER) IS
484
485 l_demand NUMBER;
486
487 l_stmt_num NUMBER;
488 l_rec_demand NUMBER := 0;
489 l_forecast_days NUMBER;
490 l_mdsmps_days NUMBER;
491 l_prorated_days NUMBER;
492 l_forecast_demand NUMBER;
493 l_mdsmps_demand NUMBER;
494
495 l_last_week_day DATE;
496 l_last_period_day DATE;
497
498 l_order_option NUMBER; --Sales/Planned Order Option
499 BEGIN
500 -- GET DEMAND FROM 1 OF 4 SOURCES
501 -- o_demand := 0;
502 -- return;
503 l_demand := 0;
504
505 -- -----------------------------------------------------------------------
506 -- FOR "ACTUAL PRODUCTION"
507 -- -----------------------------------------------------------------------
508 IF (p_demand_type = G_ACTUAL_PRODUCTION) THEN
509 /* per bug 4929489 - go directly to base tables
510 DECLARE
511 CURSOR GetActualProductionDemand IS
512 SELECT sum(planned_quantity) PQ
513 FROM mrp_line_schedules_v
514 WHERE organization_id = p_org_id
515 AND line_id = p_line_id
516 AND primary_item_id = p_assembly_item_id
517 AND trunc(scheduled_completion_date) between
518 p_start_date and p_end_date;
519 */
520 DECLARE
521 CURSOR GetActualProductionDemand IS
522 SELECT sum(planned_quantity) PQ
523 FROM
524 (
525 SELECT
526 REP.ORGANIZATION_ID,
527 REPITEM.PRIMARY_ITEM_ID,
528 REP.DAILY_PRODUCTION_RATE planned_quantity,
529 REP.LINE_ID,
530 BOM.CALENDAR_DATE scheduled_completion_date
531 FROM MFG_LOOKUPS WST,
532 MFG_LOOKUPS ML,
533 MFG_LOOKUPS WJS,
534 WIP_ENTITIES ENTITIES,
535 MTL_SYSTEM_ITEMS_KFV KFV,
536 MTL_PARAMETERS MP,
537 BOM_CALENDAR_DATES BOM,
538 WIP_REPETITIVE_ITEMS REPITEM,
539 WIP_LINES LINE,
540 WIP_REPETITIVE_SCHEDULES REP
541 WHERE WST.LOOKUP_CODE = 3
542 AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
543 AND ML.LOOKUP_TYPE = 'MRP_WORKBENCH_IMPLEMENT_AS'
544 AND ML.LOOKUP_CODE = 4
545 AND WJS.LOOKUP_TYPE = 'WIP_JOB_STATUS'
546 AND WJS.LOOKUP_CODE = REP.STATUS_TYPE
547 AND ENTITIES.WIP_ENTITY_ID = REP.WIP_ENTITY_ID
548 AND KFV.INVENTORY_ITEM_ID = REPITEM.PRIMARY_ITEM_ID
549 AND KFV.ORGANIZATION_ID = REPITEM.ORGANIZATION_ID
550 AND BOM.CALENDAR_DATE BETWEEN TRUNC(REP.FIRST_UNIT_COMPLETION_DATE) AND
551 TRUNC(REP.LAST_UNIT_COMPLETION_DATE)
552 AND BOM.SEQ_NUM IS NOT NULL
553 AND MP.CALENDAR_CODE = BOM.CALENDAR_CODE
554 AND MP.CALENDAR_EXCEPTION_SET_ID = BOM.EXCEPTION_SET_ID
555 AND MP.ORGANIZATION_ID = REP.ORGANIZATION_ID
556 AND REPITEM.LINE_ID = REP.LINE_ID
557 AND REPITEM.WIP_ENTITY_ID = REP.WIP_ENTITY_ID
558 AND REPITEM.ORGANIZATION_ID = REP.ORGANIZATION_ID
559 AND LINE.ORGANIZATION_ID = REP.ORGANIZATION_ID
560 AND LINE.LINE_ID = REP.LINE_ID
561 UNION ALL
562 SELECT
563 JOB.ORGANIZATION_ID,
564 JOB.PRIMARY_ITEM_ID,
565 JOB.START_QUANTITY planned_quantity,
566 JOB.LINE_ID,
567 JOB.SCHEDULED_COMPLETION_DATE
568 FROM MFG_LOOKUPS WST,
569 OE_ORDER_LINES_ALL SO_LINES,
570 WIP_SO_ALLOCATIONS WSA,
571 WIP_SCHEDULE_GROUPS SCH,
572 MFG_LOOKUPS WJS,
573 WIP_ENTITIES ENTITIES,
574 MTL_SYSTEM_ITEMS_KFV KFV,
575 WIP_DISCRETE_JOBS JOB,
576 WIP_LINES LINE
577 WHERE WST.LOOKUP_CODE = 1
578 AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
579 AND SO_LINES.LINE_ID(+) = TO_NUMBER(WSA.DEMAND_SOURCE_LINE)
580 AND WSA.WIP_ENTITY_ID(+) = JOB.WIP_ENTITY_ID
581 AND WSA.ORGANIZATION_ID(+) = JOB.ORGANIZATION_ID
582 AND SCH.SCHEDULE_GROUP_ID(+) = JOB.SCHEDULE_GROUP_ID
583 AND SCH.ORGANIZATION_ID(+) = JOB.ORGANIZATION_ID
584 AND WJS.LOOKUP_TYPE = 'WIP_JOB_STATUS'
585 AND WJS.LOOKUP_CODE = JOB.STATUS_TYPE
586 AND ENTITIES.ORGANIZATION_ID = JOB.ORGANIZATION_ID
587 AND ENTITIES.WIP_ENTITY_ID = JOB.WIP_ENTITY_ID
588 AND KFV.INVENTORY_ITEM_ID = JOB.PRIMARY_ITEM_ID
589 AND KFV.ORGANIZATION_ID = JOB.ORGANIZATION_ID
590 AND JOB.LINE_ID = LINE.LINE_ID
591 AND JOB.ORGANIZATION_ID = LINE.ORGANIZATION_ID
592 UNION ALL
593 SELECT
594 FLOW.ORGANIZATION_ID,
595 FLOW.PRIMARY_ITEM_ID,
596 FLOW.PLANNED_QUANTITY planned_quantity,
597 FLOW.LINE_ID,
598 FLOW.SCHEDULED_COMPLETION_DATE
599 FROM MFG_LOOKUPS WST,
600 OE_ORDER_LINES_ALL SO_LINES,
601 WIP_SCHEDULE_GROUPS SCH,
602 MTL_SYSTEM_ITEMS_KFV KFV,
603 WIP_FLOW_SCHEDULES FLOW,
604 WIP_LINES LINE,
605 MFG_LOOKUPS ST
606 WHERE WST.LOOKUP_CODE = 2
607 AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
608 AND SO_LINES.LINE_ID(+) = TO_NUMBER(FLOW.DEMAND_SOURCE_LINE)
609 AND SCH.SCHEDULE_GROUP_ID(+) = FLOW.SCHEDULE_GROUP_ID
610 AND SCH.ORGANIZATION_ID(+) = FLOW.ORGANIZATION_ID
611 AND KFV.INVENTORY_ITEM_ID = FLOW.PRIMARY_ITEM_ID
612 AND KFV.ORGANIZATION_ID = FLOW.ORGANIZATION_ID
613 AND FLOW.LINE_ID = LINE.LINE_ID
614 AND FLOW.ORGANIZATION_ID = LINE.ORGANIZATION_ID
615 AND ST.LOOKUP_TYPE = 'WIP_FLOW_SCHEDULE_STATUS'
616 AND ST.LOOKUP_CODE = FLOW.STATUS
617 )
618 WHERE organization_id = p_org_id
619 AND line_id = p_line_id
620 AND primary_item_id = p_assembly_item_id
621 AND trunc(scheduled_completion_date) between
622 p_start_date and p_end_date;
623
624 BEGIN
625 FOR c5rec IN GetActualProductionDemand LOOP
626 l_demand := nvl(c5rec.PQ ,0);
627 END LOOP; -- SELECTING ACTUAL PRODUCTION RECORDS
628 END;
629
630 -- Added for enhancement bug #2538897
631 -- -----------------------------------------------------------------------
632 -- FOR "SALES / PLANNED ORDERS"
633 -- -----------------------------------------------------------------------
634 ELSIF (p_demand_type IN (G_SALES_ORDERS, G_PLANNED_ORDERS)) THEN
635 /* perf bug 4929489 - go directly to the base tables
636 DECLARE
637 CURSOR GetUnscheduleDemand(i_option IN NUMBER) IS
638 SELECT sum(order_quantity) PQ
639 FROM mrp_unscheduled_orders_v
640 WHERE organization_id = p_org_id
641 AND line_id = p_line_id
642 AND inventory_item_id = p_assembly_item_id
643 AND trunc(order_date) between p_start_date and p_end_date
644 AND unscheduled_order_option = i_option;
645 BEGIN
646 IF p_demand_type = G_SALES_ORDERS THEN
647 l_order_option := 1;
648 ELSE
649 l_order_option := 2;
650 END IF;
651 */
652 IF p_demand_type = G_SALES_ORDERS THEN
653 DECLARE
654 CURSOR GetUnscheduleDemand IS
655 SELECT sum(order_quantity) PQ
656 FROM
657 (
658 SELECT
659 GREATEST((INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL1.SHIP_FROM_ORG_ID,
660 SL1.INVENTORY_ITEM_ID,
661 SL1.ORDER_QUANTITY_UOM,
662 SL1.ORDERED_QUANTITY) -
663 MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(SL1.LINE_ID,
664 2,
665 TO_CHAR(NULL),
666 MSI1.REPLENISH_TO_ORDER_FLAG) -
667 MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(SL1.SHIP_FROM_ORG_ID,
668 SL1.INVENTORY_ITEM_ID,
669 SL1.LINE_ID,
670 MSI1.REPLENISH_TO_ORDER_FLAG)),
671 0) order_quantity,
672 sl1.inventory_item_id,
673 sl1.ship_from_org_id organization_id,
674 wl.line_id,
675 sl1.schedule_ship_date order_date
676 FROM
677 OE_ORDER_LINES_ALL SL1,
678 MTL_SYSTEM_ITEMS_KFV MSI1,
679 WIP_LINES WL,
680 (select sl2.line_id,
681 decode((select 1
682 from oe_order_holds_all oh
683 where oh.header_id = sl2.header_id
684 and rownum = 1
685 and oh.released_flag = 'N'),
686 null,
687 0,
688 decode(sl2.ato_line_id,
689 null,
690 mrp_flow_schedule_util.check_holds(sl2.header_id,
691 sl2.line_id,
692 'OEOL',
693 'LINE_SCHEDULING'),
694 mrp_flow_schedule_util.check_holds(sl2.header_id,
695 sl2.line_id,
696 null,
697 null))) hold
698 from oe_order_lines_all sl2) line_holds,
699 (select sl2.line_id,
700 CTO_WIP_WORKFLOW_API_PK.workflow_build_status(sl2.LINE_ID) status
701 from oe_order_lines_all sl2) line_build
702 WHERE
703 line_build.line_id = sl1.line_id
704 AND 1 = decode(MSI1.REPLENISH_TO_ORDER_FLAG, 'N', 1, line_build.status)
705 AND MSI1.BUILD_IN_WIP_FLAG = 'Y'
706 AND MSI1.PICK_COMPONENTS_FLAG = 'N'
707 AND MSI1.BOM_ITEM_TYPE = 4
708 AND MSI1.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
709 AND MSI1.INVENTORY_ITEM_ID = SL1.INVENTORY_ITEM_ID
710 AND SL1.ORDERED_QUANTITY > 0
711 AND SL1.VISIBLE_DEMAND_FLAG = 'Y'
712 AND SL1.OPEN_FLAG = 'Y'
713 AND SL1.ITEM_TYPE_CODE in ('STANDARD', 'CONFIG', 'INCLUDED', 'OPTION')
714 AND OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
715 AND wl.organization_id = sl1.ship_from_org_id
716 AND wl.line_id in (select line_id
717 from bom_operational_routings bor2
718 where bor2.assembly_item_id = sl1.inventory_item_id
719 and bor2.organization_id = sl1.ship_from_org_id
720 and bor2.cfm_routing_flag = 1)
721 AND SL1.SHIPPED_QUANTITY is NULL
722 and sl1.line_id = line_holds.line_id
723 and line_holds.hold = 0
724 AND NVL(SL1.FULFILLED_FLAG, 'N') <> 'Y'
725 )
726 WHERE organization_id = p_org_id
727 AND line_id = p_line_id
728 AND inventory_item_id = p_assembly_item_id
729 AND trunc(order_date) between p_start_date and p_end_date;
730 BEGIN
731 FOR c5rec IN GetUnscheduleDemand LOOP
732 l_demand := nvl(c5rec.PQ ,0);
733 END LOOP; -- SELECTING UNSCHEDULED ORDERS RECORDS
734 END;
735 ELSE /* planned order */
736 DECLARE
737 CURSOR GetUnscheduleDemand IS
738 SELECT sum(order_quantity) PQ
739 FROM
740 (
741 SELECT
742 MR1.ORGANIZATION_ID,
743 MR1.INVENTORY_ITEM_ID,
744 WL.LINE_ID,
745 NVL(MR1.FIRM_DATE, MR1.NEW_SCHEDULE_DATE) order_date,
746 GREATEST((NVL(MR1.FIRM_QUANTITY, MR1.NEW_ORDER_QUANTITY) -
747 MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(TO_CHAR(MR1.TRANSACTION_ID),
748 100,
749 NULL,
750 NULL)),
751 0) order_quantity
752 FROM MTL_SYSTEM_ITEMS_B KFV,
753 MRP_SYSTEM_ITEMS RSI1,
754 MRP_PLANS MP1,
755 MRP_RECOMMENDATIONS MR1,
756 WIP_LINES WL
757 WHERE MP1.PLAN_COMPLETION_DATE IS NOT NULL
758 AND MP1.DATA_COMPLETION_DATE IS NOT NULL
759 AND MP1.COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
760 AND (MP1.ORGANIZATION_ID = MR1.ORGANIZATION_ID OR
761 (MP1.ORGANIZATION_ID IN
762 (SELECT ORGANIZATION_ID
763 FROM MRP_PLAN_ORGANIZATIONS
764 WHERE COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
765 AND PLANNED_ORGANIZATION = MR1.ORGANIZATION_ID)))
766 AND MR1.ORGANIZATION_ID = MR1.SOURCE_ORGANIZATION_ID
767 AND KFV.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
768 AND KFV.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
769 AND NVL(KFV.RELEASE_TIME_FENCE_CODE, -1) <> 6 /* KANBAN ITEM */
770 AND MR1.ORDER_TYPE = 5 /* PLANNED ORDER */
771 AND MR1.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
772 AND MR1.COMPILE_DESIGNATOR = RSI1.COMPILE_DESIGNATOR
773 AND MR1.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
774 AND MR1.COMPILE_DESIGNATOR =
775 (SELECT DESIGNATOR
776 FROM MRP_DESIGNATORS_VIEW
777 WHERE PRODUCTION = 1
778 AND ORGANIZATION_ID = MP1.ORGANIZATION_ID
779 AND DESIGNATOR = MR1.COMPILE_DESIGNATOR)
780 AND RSI1.BUILD_IN_WIP_FLAG = 1 /* YES */
781 AND RSI1.BOM_ITEM_TYPE = 4
782 AND (RSI1.IN_SOURCE_PLAN = 2 OR RSI1.IN_SOURCE_PLAN IS NULL)
783 AND wl.organization_id = MR1.ORGANIZATION_ID
784 AND wl.line_id in (select line_id
785 from bom_operational_routings bor2
786 where bor2.assembly_item_id = MR1.INVENTORY_ITEM_ID
787 and bor2.organization_id = MR1.ORGANIZATION_ID
788 and bor2.cfm_routing_flag = 1)
789 )
790 WHERE organization_id = p_org_id
791 AND line_id = p_line_id
792 AND inventory_item_id = p_assembly_item_id
793 AND trunc(order_date) between p_start_date and p_end_date;
794 BEGIN
795 FOR c5rec IN GetUnscheduleDemand LOOP
796 l_demand := nvl(c5rec.PQ ,0);
797 END LOOP; -- SELECTING UNSCHEDULED ORDERS RECORDS
798 END;
799 END IF;
800
801 -- -----------------------------------------------------------------------
802 -- FOR "FORECAST"
803 -- -----------------------------------------------------------------------
804 ELSIF (p_demand_type = G_FORECAST) THEN
805 DECLARE
806 CURSOR GetForecastDemand IS
807 SELECT current_forecast_quantity, rate_end_date, bucket_type,
808 forecast_date
809 FROM mrp_forecast_dates
810 WHERE organization_id = p_org_id
811 AND forecast_designator = p_demand_code
812 AND inventory_item_id = p_assembly_item_id
813 AND (line_id = p_line_id OR line_id is NULL);
814 BEGIN
815 FOR c2rec IN GetForecastDemand LOOP
816 l_stmt_num := 3;
817 l_forecast_days := 0;
818 l_prorated_days := 0;
819 l_forecast_demand := 0;
820 l_rec_demand := 0;
821
822 -- --------------------------------------------------------
823 -- FOR "DAYS" BUCKET
824 -- --------------------------------------------------------
825 IF (c2rec.bucket_type = G_DAYS) THEN
826 IF (c2rec.forecast_date >= p_start_date
827 AND c2rec.forecast_date <= p_end_date) THEN
828 IF (nvl(c2rec.rate_end_date, c2rec.forecast_date)
829 <= p_end_date) THEN
830
831 -- FORECAST FALLS WITHIN RANGE
832
833 l_stmt_num := 4;
834 IF (c2rec.rate_end_date is NULL) THEN
835 l_rec_demand := c2rec.current_forecast_quantity;
836 ELSE
837 l_rec_demand := c2rec.current_forecast_quantity *
838 (mrp_calendar.days_between(p_org_id,
839 c2rec.bucket_type, c2rec.forecast_date,
840 c2rec.rate_end_date) + 1);
841 END IF;
842 l_demand := l_demand + l_rec_demand;
843 ELSE
844
845 -- ONLY END DATE CUTS INTO FORECAST
846
847 -- GET # OF DAYS IN FORECAST
848 l_stmt_num := 5;
849 l_forecast_days := GetWorkdaysBetween(
850 p_calendar_code,
851 p_exception_set_id,
852 c2rec.forecast_date,
853 nvl(c2rec.rate_end_date,
854 c2rec.forecast_date));
855
856 -- GET # OF DAYS BETWEEN FORECAST AND END DATE
857 l_stmt_num := 6;
858 l_prorated_days := GetWorkdaysBetween(
859 p_calendar_code,
860 p_exception_set_id,
861 c2rec.forecast_date,
862 p_end_date);
863
864 -- GET TOTAL DEMAND FOR THE FORECAST
865 l_stmt_num := 7;
866 IF (c2rec.rate_end_date is NULL) THEN
867 l_forecast_demand := c2rec.current_forecast_quantity;
868 ELSE
869 l_forecast_demand :=
870 c2rec.current_forecast_quantity *
871 (mrp_calendar.days_between(p_org_id,
872 c2rec.bucket_type, c2rec.forecast_date,
873 c2rec.rate_end_date) + 1);
874 END IF;
875
876 -- CALCULATE DEMAND
877 l_stmt_num := 8;
878 l_rec_demand := (l_prorated_days/l_forecast_days) *
879 l_forecast_demand;
880 l_demand := l_demand + l_rec_demand;
881 END IF;
882 END IF;
883
884 IF (c2rec.forecast_date < p_start_date
885 AND nvl(c2rec.rate_end_date, c2rec.forecast_date) >=
886 p_start_date) THEN
887 IF (nvl(c2rec.rate_end_date, c2rec.forecast_date)
888 <= p_end_date) THEN
889
890 -- ONLY START DATE CUTS INTO FORECAST
891
892 -- GET # OF DAYS IN FORECAST
893 l_stmt_num := 9;
894 l_forecast_days := GetWorkdaysBetween(
895 p_calendar_code,
896 p_exception_set_id,
897 c2rec.forecast_date,
898 nvl(c2rec.rate_end_date,
899 c2rec.forecast_date));
900
901 -- GET # OF DAYS BTWN START DATE AND FORECAST END DATE
902 l_stmt_num := 10;
903 l_prorated_days := GetWorkdaysBetween(
904 p_calendar_code,
905 p_exception_set_id,
906 p_start_date,
907 nvl(c2rec.rate_end_date,
908 c2rec.forecast_date));
909
910 -- GET TOTAL DEMAND FOR THE FORECAST
911 l_stmt_num := 11;
912 IF (c2rec.rate_end_date is NULL) THEN
913 l_forecast_demand := c2rec.current_forecast_quantity;
914 ELSE
915 l_forecast_demand :=
916 c2rec.current_forecast_quantity *
917 (mrp_calendar.days_between(p_org_id,
918 c2rec.bucket_type, c2rec.forecast_date,
919 c2rec.rate_end_date) + 1);
920 END IF;
921
922 -- CALCULATE DEMAND
923 l_stmt_num := 12;
924 l_rec_demand := (l_prorated_days/l_forecast_days) *
925 l_forecast_demand;
926 l_demand := l_demand + l_rec_demand;
927 ELSE
928 -- BOTH START AND END DATE CUT INTO FORECAST
929
930 -- GET # OF DAYS IN FORECAST
931 l_stmt_num := 13;
932 l_forecast_days := GetWorkdaysBetween(
933 p_calendar_code,
934 p_exception_set_id,
935 c2rec.forecast_date,
936 nvl(c2rec.rate_end_date,
937 c2rec.forecast_date));
938
939 -- GET # OF DAYS BTWN START DATE AND END DATE
940 l_stmt_num := 14;
941 l_prorated_days := GetWorkdaysBetween(
942 p_calendar_code,
943 p_exception_set_id,
944 p_start_date,
945 p_end_date);
946
947 -- GET TOTAL DEMAND FOR THE FORECAST
948 l_stmt_num := 15;
949 IF (c2rec.rate_end_date is NULL) THEN
950 l_forecast_demand := c2rec.current_forecast_quantity;
951 ELSE
952 l_forecast_demand :=
953 c2rec.current_forecast_quantity *
954 (mrp_calendar.days_between(p_org_id,
955 c2rec.bucket_type, c2rec.forecast_date,
956 c2rec.rate_end_date) + 1);
957 END IF;
958
959 -- CALCULATE DEMAND
960 l_stmt_num := 16;
961 l_rec_demand := (l_prorated_days/l_forecast_days) *
962 l_forecast_demand;
963 l_demand := l_demand + l_rec_demand;
964 END IF;
965 END IF;
966
967 -- --------------------------------------------------------
968 -- FOR "WEEKS" BUCKET
969 -- --------------------------------------------------------
970
971 ELSIF (c2rec.bucket_type = G_WEEKS) THEN
972 l_stmt_num := 17;
973 l_last_week_day := GetLastWeekDay(p_calendar_code,
974 p_exception_set_id,
975 p_last_calendar_date,
976 c2rec.rate_end_date,
977 c2rec.forecast_date);
978 IF (c2rec.forecast_date >= p_start_date
979 AND c2rec.forecast_date <= p_end_date) THEN
980 IF (l_last_week_day <= p_end_date) THEN
981
982 -- FORECAST FALLS WITHIN RANGE
983 l_stmt_num := 18;
984 IF (c2rec.rate_end_date is NULL) THEN
985 l_rec_demand := c2rec.current_forecast_quantity;
986 ELSE
987 l_rec_demand := c2rec.current_forecast_quantity *
988 GetBuckets(p_calendar_code,
989 p_exception_set_id, c2rec.bucket_type,
990 c2rec.forecast_date, c2rec.rate_end_date);
991 END IF;
992 l_demand := l_demand + l_rec_demand;
993 ELSE
994
995 -- ONLY END DATE CUTS INTO FORECAST
996
997 -- GET # OF DAYS IN FORECAST
998 l_stmt_num := 18;
999 l_forecast_days := GetWorkdaysBetween(
1000 p_calendar_code,
1001 p_exception_set_id,
1002 c2rec.forecast_date,
1003 l_last_week_day);
1004
1005 -- GET # OF DAYS BETWEEN FORECAST AND END DATE
1006 l_stmt_num := 19;
1007 l_prorated_days := GetWorkdaysBetween(
1008 p_calendar_code,
1009 p_exception_set_id,
1010 c2rec.forecast_date,
1011 p_end_date);
1012
1013 -- GET TOTAL DEMAND FOR THE FORECAST
1014 l_stmt_num := 20;
1015 IF (c2rec.rate_end_date is NULL) THEN
1016 l_forecast_demand := c2rec.current_forecast_quantity;
1017 ELSE
1018 l_forecast_demand :=
1019 c2rec.current_forecast_quantity *
1020 GetBuckets(p_calendar_code,
1021 p_exception_set_id, c2rec.bucket_type,
1022 c2rec.forecast_date, c2rec.rate_end_date);
1023 END IF;
1024
1025 -- CALCULATE DEMAND
1026 l_stmt_num := 21;
1027 l_rec_demand := (l_prorated_days/l_forecast_days) *
1028 l_forecast_demand;
1029 l_demand := l_demand + l_rec_demand;
1030 END IF;
1031 END IF;
1032
1033 IF (c2rec.forecast_date < p_start_date
1034 AND l_last_week_day >= p_start_date) THEN
1035 IF (l_last_week_day <= p_end_date) THEN
1036
1037 -- ONLY START DATE CUTS INTO FORECAST
1038
1039 -- GET # OF DAYS IN FORECAST
1040 l_stmt_num := 22;
1041 l_forecast_days := GetWorkdaysBetween(
1042 p_calendar_code,
1043 p_exception_set_id,
1044 c2rec.forecast_date,
1045 l_last_week_day);
1046
1047 -- GET # OF DAYS BTWN START DATE AND FORECAST END DATE
1048 l_stmt_num := 23;
1049 l_prorated_days := GetWorkdaysBetween(
1050 p_calendar_code,
1051 p_exception_set_id,
1052 p_start_date,
1053 l_last_week_day);
1054
1055 -- GET TOTAL DEMAND FOR THE FORECAST
1056 l_stmt_num := 24;
1057 IF (c2rec.rate_end_date is NULL) THEN
1058 l_forecast_demand := c2rec.current_forecast_quantity;
1059 ELSE
1060 l_forecast_demand :=
1061 c2rec.current_forecast_quantity *
1062 GetBuckets(p_calendar_code,
1063 p_exception_set_id, c2rec.bucket_type,
1064 c2rec.forecast_date, c2rec.rate_end_date);
1065 END IF;
1066
1067 -- CALCULATE DEMAND
1068 l_stmt_num := 25;
1069 l_rec_demand := (l_prorated_days/l_forecast_days) *
1070 l_forecast_demand;
1071 l_demand := l_demand + l_rec_demand;
1072 ELSE
1073 -- BOTH START AND END DATE CUT INTO FORECAST
1074
1075 -- GET # OF DAYS IN FORECAST
1076 l_stmt_num := 26;
1077 l_forecast_days := GetWorkdaysBetween(
1078 p_calendar_code,
1079 p_exception_set_id,
1080 c2rec.forecast_date,
1081 l_last_week_day);
1082
1083 -- GET # OF DAYS BTWN START DATE AND END DATE
1084 l_stmt_num := 27;
1085 l_prorated_days := GetWorkdaysBetween(
1086 p_calendar_code,
1087 p_exception_set_id,
1088 p_start_date,
1089 p_end_date);
1090
1091 -- GET TOTAL DEMAND FOR THE FORECAST
1092 l_stmt_num := 28;
1093 IF (c2rec.rate_end_date is NULL) THEN
1094 l_forecast_demand := c2rec.current_forecast_quantity;
1095 ELSE
1096 l_forecast_demand :=
1097 c2rec.current_forecast_quantity *
1098 GetBuckets(p_calendar_code,
1099 p_exception_set_id, c2rec.bucket_type,
1100 c2rec.forecast_date, c2rec.rate_end_date);
1101 END IF;
1102
1103 -- CALCULATE DEMAND
1104 l_stmt_num := 29;
1105 l_rec_demand := (l_prorated_days/l_forecast_days) *
1106 l_forecast_demand;
1107 l_demand := l_demand + l_rec_demand;
1108 END IF;
1109 END IF;
1110
1111 -- --------------------------------------------------------
1112 -- FOR "PERIODS" BUCKET
1113 -- --------------------------------------------------------
1114 ELSIF (c2rec.bucket_type = G_PERIODS) THEN
1115 l_stmt_num := 30;
1116 l_last_period_day := GetLastPeriodDay(p_calendar_code,
1117 p_exception_set_id,
1118 p_last_calendar_date,
1119 c2rec.rate_end_date,
1120 c2rec.forecast_date);
1121 IF (c2rec.forecast_date >= p_start_date
1122 AND c2rec.forecast_date <= p_end_date) THEN
1123 IF (l_last_period_day <= p_end_date) THEN
1124
1125 -- FORECAST FALLS WITHIN RANGE
1126 l_stmt_num := 31;
1127 IF (c2rec.rate_end_date is NULL) THEN
1128 l_rec_demand := c2rec.current_forecast_quantity;
1129 ELSE
1130 l_rec_demand := c2rec.current_forecast_quantity *
1131 GetBuckets(p_calendar_code,
1132 p_exception_set_id, c2rec.bucket_type,
1133 c2rec.forecast_date, c2rec.rate_end_date);
1134 END IF;
1135 l_demand := l_demand + l_rec_demand;
1136 ELSE
1137
1138 -- ONLY END DATE CUTS INTO FORECAST
1139
1140 -- GET # OF DAYS IN FORECAST
1141 l_stmt_num := 32;
1142 l_forecast_days := GetWorkdaysBetween(
1143 p_calendar_code,
1144 p_exception_set_id,
1145 c2rec.forecast_date,
1146 l_last_period_day);
1147
1148 -- GET # OF DAYS BETWEEN FORECAST AND END DATE
1149 l_stmt_num := 33;
1150 l_prorated_days := GetWorkdaysBetween(
1151 p_calendar_code,
1152 p_exception_set_id,
1153 c2rec.forecast_date,
1154 p_end_date);
1155
1156 -- GET TOTAL DEMAND FOR THE FORECAST
1157 l_stmt_num := 34;
1158 IF (c2rec.rate_end_date is NULL) THEN
1159 l_forecast_demand := c2rec.current_forecast_quantity;
1160 ELSE
1161 l_forecast_demand :=
1162 c2rec.current_forecast_quantity *
1163 GetBuckets(p_calendar_code,
1164 p_exception_set_id, c2rec.bucket_type,
1165 c2rec.forecast_date, c2rec.rate_end_date);
1166 END IF;
1167
1168 -- CALCULATE DEMAND
1169 l_stmt_num := 35;
1170 l_rec_demand := (l_prorated_days/l_forecast_days) *
1171 l_forecast_demand;
1172 l_demand := l_demand + l_rec_demand;
1173 END IF;
1174 END IF;
1175
1176 IF (c2rec.forecast_date < p_start_date
1177 AND l_last_period_day >= p_start_date) THEN
1178 IF (l_last_period_day <= p_end_date) THEN
1179
1180 -- ONLY START DATE CUTS INTO FORECAST
1181
1182 -- GET # OF DAYS IN FORECAST
1183 l_stmt_num := 36;
1184 l_forecast_days := GetWorkdaysBetween(
1185 p_calendar_code,
1186 p_exception_set_id,
1187 c2rec.forecast_date,
1188 l_last_period_day);
1189
1190 -- GET # OF DAYS BTWN START DATE AND FORECAST END DATE
1191 l_stmt_num := 37;
1192 l_prorated_days := GetWorkdaysBetween(
1193 p_calendar_code,
1194 p_exception_set_id,
1195 p_start_date,
1196 l_last_period_day);
1197
1198 -- GET TOTAL DEMAND FOR THE FORECAST
1199 l_stmt_num := 38;
1200 IF (c2rec.rate_end_date is NULL) THEN
1201 l_forecast_demand := c2rec.current_forecast_quantity;
1202 ELSE
1203 l_forecast_demand :=
1204 c2rec.current_forecast_quantity *
1205 GetBuckets(p_calendar_code,
1206 p_exception_set_id, c2rec.bucket_type,
1207 c2rec.forecast_date, c2rec.rate_end_date);
1208 END IF;
1209
1210 -- CALCULATE DEMAND
1211 l_stmt_num := 39;
1212 l_rec_demand := (l_prorated_days/l_forecast_days) *
1213 l_forecast_demand;
1214 l_demand := l_demand + l_rec_demand;
1215 ELSE
1216 -- BOTH START AND END DATE CUT INTO FORECAST
1217
1218 -- GET # OF DAYS IN FORECAST
1219 l_stmt_num := 40;
1220 l_forecast_days := GetWorkdaysBetween(
1221 p_calendar_code,
1222 p_exception_set_id,
1223 c2rec.forecast_date,
1224 l_last_period_day);
1225
1226 -- GET # OF DAYS BTWN START DATE AND END DATE
1227 l_stmt_num := 41;
1228 l_prorated_days := GetWorkdaysBetween(
1229 p_calendar_code,
1230 p_exception_set_id,
1231 p_start_date,
1232 p_end_date);
1233
1234 -- GET TOTAL DEMAND FOR THE FORECAST
1235 l_stmt_num := 42;
1236 IF (c2rec.rate_end_date is NULL) THEN
1237 l_forecast_demand := c2rec.current_forecast_quantity;
1238 ELSE
1239 l_forecast_demand :=
1240 c2rec.current_forecast_quantity *
1241 GetBuckets(p_calendar_code,
1242 p_exception_set_id, c2rec.bucket_type,
1243 c2rec.forecast_date, c2rec.rate_end_date);
1244 END IF;
1245
1246 -- CALCULATE DEMAND
1247 l_stmt_num := 43;
1248 l_rec_demand := (l_prorated_days/l_forecast_days) *
1249 l_forecast_demand;
1250 l_demand := l_demand + l_rec_demand;
1251 END IF;
1252 END IF;
1253 END IF; -- CHECK BUCKET TYPES
1254 END LOOP; -- SELECTING FORECAST RECORDS
1255 END;
1256 -- -----------------------------------------------------------------------
1257 -- FOR "MDS" AND "MPS"
1258 -- -----------------------------------------------------------------------
1259 ELSIF (p_demand_type in (G_MDS, G_MPS)) THEN
1260 DECLARE
1261 CURSOR GetMdsMpsDemand IS
1262 SELECT repetitive_daily_rate, rate_end_date,
1263 schedule_quantity, schedule_date
1264 FROM mrp_schedule_dates
1265 WHERE organization_id = p_org_id
1266 AND schedule_designator = p_demand_code
1267 AND (line_id = p_line_id OR line_id is NULL)
1268 AND schedule_level = 2
1269 AND inventory_item_id = p_assembly_item_id;
1270 BEGIN
1271 l_stmt_num := 44;
1272 FOR c3rec IN GetMdsMpsDemand LOOP
1273 l_mdsmps_days := 0;
1274 l_prorated_days := 0;
1275 l_mdsmps_demand := 0;
1276 l_rec_demand := 0;
1277
1278 -- --------------------------------------------------------
1279 -- FOR "DAYS" BUCKET
1280 -- --------------------------------------------------------
1281
1282 IF (c3rec.schedule_date >= p_start_date
1283 AND c3rec.schedule_date <= p_end_date) THEN
1284 IF (nvl(c3rec.rate_end_date, c3rec.schedule_date)
1285 <= p_end_date) THEN
1286
1287 -- MDS/MPS FALLS WITHIN RANGE
1288 l_stmt_num := 45;
1289 IF (c3rec.rate_end_date is NULL) THEN
1290 l_rec_demand := c3rec.schedule_quantity;
1291 ELSE
1292 l_rec_demand := c3rec.repetitive_daily_rate *
1293 (mrp_calendar.days_between(p_org_id,
1294 1, c3rec.schedule_date,
1295 c3rec.rate_end_date) + 1);
1296 END IF;
1297 l_demand := l_demand + l_rec_demand;
1298 ELSE
1299
1300 -- ONLY END DATE CUTS INTO MDS/MPS
1301
1302 -- GET # OF DAYS IN MDS/MPS
1303 l_stmt_num := 46;
1304 l_mdsmps_days := GetWorkdaysBetween(
1305 p_calendar_code,
1306 p_exception_set_id,
1307 c3rec.schedule_date,
1308 nvl(c3rec.rate_end_date,
1309 c3rec.schedule_date));
1310
1311 -- GET # OF DAYS BETWEEN MDS/MPS AND END DATE
1312 l_stmt_num := 47;
1313 l_prorated_days := GetWorkdaysBetween(
1314 p_calendar_code,
1315 p_exception_set_id,
1316 c3rec.schedule_date,
1317 p_end_date);
1318
1319 -- GET TOTAL DEMAND FOR THE MDS/MPS
1320 l_stmt_num := 48;
1321 IF (c3rec.rate_end_date is NULL) THEN
1322 l_mdsmps_demand := c3rec.schedule_quantity;
1323 ELSE
1324 l_mdsmps_demand :=
1325 c3rec.repetitive_daily_rate *
1326 (mrp_calendar.days_between(p_org_id,
1327 1, c3rec.schedule_date,
1328 c3rec.rate_end_date) + 1);
1329 END IF;
1330
1331 -- CALCULATE DEMAND
1332 l_stmt_num := 49;
1333 l_rec_demand := (l_prorated_days/l_mdsmps_days) *
1334 l_mdsmps_demand;
1335 l_demand := l_demand + l_rec_demand;
1336 END IF;
1337 END IF;
1338
1339 IF (c3rec.schedule_date < p_start_date
1340 AND nvl(c3rec.rate_end_date, c3rec.schedule_date) >=
1341 p_start_date) THEN
1342 IF (nvl(c3rec.rate_end_date, c3rec.schedule_date)
1343 <= p_end_date) THEN
1344
1345 -- ONLY START DATE CUTS INTO MDS/MPS
1346
1347 -- GET # OF DAYS IN MDS/MPS
1348 l_stmt_num := 50;
1349 l_mdsmps_days := GetWorkdaysBetween(
1350 p_calendar_code,
1351 p_exception_set_id,
1352 c3rec.schedule_date,
1353 nvl(c3rec.rate_end_date,
1354 c3rec.schedule_date));
1355
1356 -- GET # OF DAYS BTWN START DATE AND MDS/MPS END DATE
1357 l_stmt_num := 51;
1358 l_prorated_days := GetWorkdaysBetween(
1359 p_calendar_code,
1360 p_exception_set_id,
1361 p_start_date,
1362 nvl(c3rec.rate_end_date,
1363 c3rec.schedule_date));
1364
1365 -- GET TOTAL DEMAND FOR THE MDS/MPS
1366 l_stmt_num := 52;
1367 IF (c3rec.rate_end_date is NULL) THEN
1368 l_mdsmps_demand := c3rec.schedule_quantity;
1369 ELSE
1370 l_mdsmps_demand :=
1371 c3rec.repetitive_daily_rate *
1372 (mrp_calendar.days_between(p_org_id,
1373 1, c3rec.schedule_date,
1374 c3rec.rate_end_date) + 1);
1375 END IF;
1376
1377 -- CALCULATE DEMAND
1378 l_stmt_num := 53;
1379 l_rec_demand := (l_prorated_days/l_mdsmps_days) *
1380 l_mdsmps_demand;
1381 l_demand := l_demand + l_rec_demand;
1382 ELSE
1383 -- BOTH START AND END DATE CUT INTO MDS/MPS
1384
1385 -- GET # OF DAYS IN MDS/MPS
1386 l_stmt_num := 54;
1387 l_mdsmps_days := GetWorkdaysBetween(
1388 p_calendar_code,
1389 p_exception_set_id,
1390 c3rec.schedule_date,
1391 nvl(c3rec.rate_end_date,
1392 c3rec.schedule_date));
1393
1394 -- GET # OF DAYS BTWN START DATE AND END DATE
1395 l_stmt_num := 55;
1396 l_prorated_days := GetWorkdaysBetween(
1397 p_calendar_code,
1398 p_exception_set_id,
1399 p_start_date,
1400 p_end_date);
1401
1402 -- GET TOTAL DEMAND FOR THE MDS/MPS
1403 l_stmt_num := 56;
1404 IF (c3rec.rate_end_date is NULL) THEN
1405 l_mdsmps_demand := c3rec.schedule_quantity;
1406 ELSE
1407 l_mdsmps_demand :=
1408 c3rec.repetitive_daily_rate *
1409 (mrp_calendar.days_between(p_org_id,
1410 1, c3rec.schedule_date,
1411 c3rec.rate_end_date) + 1);
1412 END IF;
1413
1414 -- CALCULATE DEMAND
1415 l_stmt_num := 57;
1416 l_rec_demand := (l_prorated_days/l_mdsmps_days) *
1417 l_mdsmps_demand;
1418 l_demand := l_demand + l_rec_demand;
1419 END IF;
1420 END IF;
1421 END LOOP;
1422 END;
1423 END IF; -- CHECK DEMAND TYPE
1424 o_demand := l_demand;
1425 o_stmt_num := l_stmt_num;
1426 END GetDemand;
1427
1428
1429
1430 -- =============================== GetDetails =================================
1431 --
1432 -- NAME : GetDetails
1433 -- DESCRIPTION: Get the Detail of resource usage on the line, according
1434 -- to the given demand
1435 --
1436 -- Insert these values into the table BOM_MIXED_MODEL_MAP_RES.
1437 -- REQUIRES : err_text out buffer to return error message
1438 -- MODIFIES : BOM_MIXED_MODEL_MAP_RESOURCES
1439 --
1440 -- ==========================================================================
1441
1442 PROCEDURE GetDetails (
1443 p_mmm_id NUMBER,
1444 p_line_id NUMBER,
1445 p_family_item_id NUMBER,
1446 p_org_id NUMBER,
1447 p_user_id NUMBER,
1448 p_start_date DATE,
1449 p_end_date DATE,
1450 p_demand_type NUMBER,
1451 p_demand_code VARCHAR2,
1452 p_hours_per_day NUMBER,
1453 p_demand_days NUMBER,
1454 p_boost_percent NUMBER,
1455 p_operation_type NUMBER,
1456 p_time_uom NUMBER,
1457 p_calendar_code VARCHAR2,
1458 p_exception_set_id NUMBER,
1459 p_last_calendar_date DATE,
1460 x_err_text OUT NOCOPY VARCHAR2) IS
1461
1462 l_hour_uom VARCHAR2(3);
1463 l_hour_conv NUMBER;
1464
1465 -- Cursor to Get Assembly Items
1466 CURSOR GetProducts IS
1467 SELECT assembly_item_id item_id
1468 FROM bom_operational_routings bor
1469 WHERE mixed_model_map_flag = 1
1470 AND line_id = p_line_id
1471 AND organization_id = p_org_id
1472 AND (p_family_item_id is null
1473 OR
1474 EXISTS (SELECT 'x'
1475 FROM mtl_system_items msi
1476 WHERE msi.inventory_item_id = bor.assembly_item_id
1477 AND msi.organization_id = p_org_id
1478 AND msi.product_family_item_id = p_family_item_id))
1479 ORDER BY assembly_item_id;
1480
1481 -- Cursor to Get Resource Details
1482 CURSOR ResourceDetails IS
1483 select bor.resource_id resource_id,
1484 nvl(bor.activity_id, -1) activity_id,
1485 bos1.standard_operation_id std_op_id,
1486 brtg.assembly_item_id assy_item_id,
1487 sum(bor.usage_rate_or_amount * nvl(con.conversion_rate, 0) /
1488 nvl(l_hour_conv, 1) *
1489 nvl(bos1.net_planning_percent, 100) / 100 /
1490 decode(bos1.reverse_cumulative_yield, '', 1,
1491 '0', 1, bos1.reverse_cumulative_yield)) resource_needed
1492 from bom_operation_resources bor,
1493 bom_resources br,
1494 bom_operational_routings brtg,
1495 bom_operation_sequences bos1,
1496 bom_operation_sequences bos2,
1497 mtl_uom_conversions con
1498 where brtg.line_id = p_line_id
1499 and brtg.mixed_model_map_flag = 1
1500 and bos1.routing_sequence_id = brtg.common_routing_sequence_id
1501 and bos2.routing_sequence_id = brtg.common_routing_sequence_id
1502 and br.resource_id = bor.resource_id
1503 and con.uom_code (+) = br.unit_of_measure
1504 and con.inventory_item_id (+) = 0
1505 and (( bos1.operation_type = 3
1506 and bos2.line_op_seq_id = bos1.operation_sequence_id
1507 and p_operation_type = 3) or
1508 ( bos1.operation_type = 2
1509 and bos2.process_op_seq_id = bos1.operation_sequence_id
1510 and p_operation_type = 2))
1511 and bos2.operation_sequence_id = bor.operation_sequence_id
1512 group by bor.resource_id, nvl(bor.activity_id, -1), bos1.standard_operation_id, brtg.assembly_item_id
1513 order by bor.resource_id, nvl(bor.activity_id, -1), bos1.standard_operation_id, brtg.assembly_item_id;
1514
1515 -- Local Types
1516
1517 TYPE l_demand_rec_type IS RECORD (
1518 assembly_item_id number,
1519 average_demand number);
1520
1521 TYPE l_process_lop_rec_type IS RECORD (
1522 standard_operation_id number,
1523 process_volume number,
1524 process_takt number);
1525
1526 TYPE l_resource_detail_rec_type IS RECORD (
1527 resource_id number,
1528 --resource_code varchar2(10),
1529 activity_id number,
1530 standard_operation_id number,
1531 resource_needed number);
1532
1533 TYPE l_demand_tbl_type IS TABLE OF l_demand_rec_type
1534 INDEX BY BINARY_INTEGER;
1535
1536 TYPE l_process_lop_tbl_type IS TABLE OF l_process_lop_rec_type
1537 INDEX BY BINARY_INTEGER;
1538
1539 TYPE l_resource_detail_tbl_type IS TABLE OF l_resource_detail_rec_type
1540 INDEX BY BINARY_INTEGER;
1541
1542 -- Local Variables
1543 l_demand_table l_demand_tbl_type;
1544 l_process_lop_table l_process_lop_tbl_type;
1545 l_res_detail_table l_resource_detail_tbl_type;
1546
1547 l_demand number;
1548 l_stmt_num number;
1549
1550 l_assy_id number;
1551 l_volume number;
1552
1553 l_resource_needed number;
1554 l_demand_sum number;
1555
1556 l_index number;
1557
1558 l_num number := 0;
1559 l_text varchar2(300);
1560
1561 l_std_op_id number;
1562 BEGIN
1563 -- Get Demands
1564 l_stmt_num := 0;
1565 l_demand_table.DELETE;
1566 FOR assy IN GetProducts LOOP
1567 GetDemand (
1568 p_org_id,
1569 p_demand_type,
1570 p_line_id,
1571 assy.item_id,
1572 p_calendar_code,
1573 p_start_date,
1574 p_end_date,
1575 p_last_calendar_date,
1576 p_exception_set_id,
1577 p_demand_code,
1578 l_demand,
1579 l_stmt_num);
1580 l_demand_table(assy.item_id).assembly_item_id := assy.item_id;
1581 l_demand_table(assy.item_id).average_demand := l_demand*(100+nvl(p_boost_percent, 0))/(100*p_demand_days);
1582
1583 /* l_text := 'demand '|| l_num || ' item id: '||l_demand_table(assy.item_id).assembly_item_id||' average demand: '||l_demand_table(assy.item_id).average_demand;
1584 l_num := l_num+1;
1585 insert into lm_temp (
1586 text
1587 )values(
1588 l_text
1589 );
1590 */
1591
1592 END LOOP;
1593
1594 -- Calculate Process Volumes
1595 l_process_lop_table.DELETE;
1596 IF l_demand_table.count > 0 THEN
1597 l_index := l_demand_table.FIRST;
1598 LOOP -- For each assembly item
1599 IF l_demand_table(l_index).average_demand > 0 THEN
1600 l_assy_id := l_demand_table(l_index).assembly_item_id;
1601 DECLARE
1602 CURSOR seqs IS
1603 select bos.standard_operation_id op,
1604 bos.reverse_cumulative_yield yld,
1605 bos.net_planning_percent pct
1606 from bom_operation_sequences bos,
1607 bom_operational_routings bor
1608 where bor.assembly_item_id = l_assy_id
1609 and bor.mixed_model_map_flag = 1
1610 and bor.common_routing_sequence_id = bos.routing_sequence_id
1611 and bos.operation_type = p_operation_type;
1612 BEGIN
1613 FOR seq IN seqs LOOP
1614 -- Get this Volume
1615 l_volume := l_demand_table(l_index).average_demand *
1616 nvl(seq.pct, 100) / (100 * nvl(seq.yld, 1));
1617 -- Count it In
1618 IF l_process_lop_table.EXISTS(seq.op) THEN
1619 l_process_lop_table(seq.op).process_volume := l_process_lop_table(seq.op).process_volume + l_volume;
1620 ELSE
1621 l_process_lop_table(seq.op).standard_operation_id := seq.op;
1622 l_process_lop_table(seq.op).process_volume := l_volume;
1623 END IF;
1624 END LOOP;
1625 END;
1626 END IF;
1627 EXIT WHEN l_index = l_demand_table.LAST;
1628 l_index := l_demand_table.NEXT(l_index);
1629 END LOOP;
1630 END IF;
1631
1632 -- Calculate Process Takt
1633 l_index := l_process_lop_table.FIRST;
1634 l_num := 0;
1635 LOOP
1636 -- we should assume hours, since the Time UOM doesn't really matter.
1637 l_process_lop_table(l_index).process_takt :=
1638 p_hours_per_day/l_process_lop_table(l_index).process_volume;
1639 --converttime(p_hours_per_day/l_process_lop_table(l_index).process_volume,
1640 --p_time_uom);
1641
1642 /* l_text := 'process volume '|| l_num || ' operation id: '||l_process_lop_table(l_index).standard_operation_id||' volume: '||l_process_lop_table(l_index).process_volume;
1643 l_num := l_num+1;
1644 insert into lm_temp (
1645 text
1646 )values(
1647 l_text
1648 );
1649 */
1650 EXIT WHEN l_index = l_process_lop_table.LAST;
1651 l_index := l_process_lop_table.NEXT(l_index);
1652 END LOOP;
1653
1654 l_hour_uom := FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE');
1655 BEGIN
1656 SELECT nvl(conversion_rate, 0)
1657 INTO l_hour_conv
1658 FROM mtl_uom_conversions
1659 WHERE uom_code = l_hour_uom
1660 AND inventory_item_id = 0;
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 l_hour_conv := 1;
1664 END;
1665
1666 -- Get Resource Details
1667 l_stmt_num := 2;
1668 l_index := 0;
1669 FOR res IN ResourceDetails LOOP
1670 IF l_index = 0 THEN
1671 l_res_detail_table(l_index).resource_id := res.resource_id;
1672 -- l_res_detail_table(l_index).resource_code := res.resource_code;
1673 l_res_detail_table(l_index).activity_id := res.activity_id;
1674 l_res_detail_table(l_index).standard_operation_id := res.std_op_id;
1675 l_resource_needed := res.resource_needed * l_demand_table(res.assy_item_id).average_demand;
1676 --l_demand_sum := l_demand_table(res.assy_item_id).average_demand;
1677 l_index := l_index + 1;
1678 ELSIF l_res_detail_table(l_index-1).resource_id = res.resource_id and
1679 -- l_res_detail_table(l_index-1).resource_code = res.resource_code and
1680 nvl(l_res_detail_table(l_index-1).activity_id, -1) = nvl(res.activity_id, -1) and
1681 l_res_detail_table(l_index-1).standard_operation_id = res.std_op_id THEN -- same group
1682 l_resource_needed := l_resource_needed +
1683 res.resource_needed * l_demand_table(res.assy_item_id).average_demand;
1684 --l_demand_sum := l_demand_sum +
1685 --l_demand_table(res.assy_item_id).average_demand;
1686 ELSE -- a new one
1687 -- finalize the previous one
1688 l_std_op_id := l_res_detail_table(l_index-1).standard_operation_id;
1689 IF (l_resource_needed > 0) and
1690 (l_process_lop_table(l_std_op_id).process_volume > 0) THEN
1691 l_res_detail_table(l_index-1).resource_needed :=
1692 l_resource_needed/l_process_lop_table(l_std_op_id).process_volume/
1693 l_process_lop_table(l_std_op_id).process_takt;
1694 ELSE
1695 l_res_detail_table(l_index-1).resource_needed := 0;
1696 END IF;
1697 l_res_detail_table(l_index).resource_id := res.resource_id;
1698 -- l_res_detail_table(l_index).resource_code := res.resource_code;
1699 l_res_detail_table(l_index).activity_id := res.activity_id;
1700 l_res_detail_table(l_index).standard_operation_id := res.std_op_id;
1701 l_resource_needed := res.resource_needed * l_demand_table(res.assy_item_id).average_demand;
1702 --l_demand_sum := l_demand_table(res.assy_item_id).average_demand;
1703 l_index := l_index + 1;
1704 end if;
1705 END LOOP;
1706 -- The last one
1707 l_std_op_id := l_res_detail_table(l_index-1).standard_operation_id;
1708 IF (l_resource_needed > 0) and
1709 (l_process_lop_table(l_std_op_id).process_volume > 0) THEN
1710 l_res_detail_table(l_index-1).resource_needed :=
1711 l_resource_needed/l_process_lop_table(l_std_op_id).process_volume/
1712 l_process_lop_table(l_std_op_id).process_takt;
1713 ELSE
1714 l_res_detail_table(l_index-1).resource_needed := 0;
1715 END IF;
1716 -- Write to Table
1717 l_stmt_num := 4;
1718 l_index := l_res_detail_table.FIRST;
1719 LOOP
1720 if l_res_detail_table(l_index).activity_id <> -1 then
1721 insert into BOM_MIXED_MODEL_MAP_RES (
1722 mixed_model_map_id,
1723 resource_id,
1724 resource_code,
1725 activity_id,
1726 activity,
1727 standard_operation_id,
1728 operation_code,
1729 resource_type,
1730 organization_id,
1731 resource_needed
1732 )
1733 select
1734 p_mmm_id,
1735 l_res_detail_table(l_index).resource_id,
1736 br.resource_code,
1737 l_res_detail_table(l_index).activity_id,
1738 ca.activity,
1739 l_res_detail_table(l_index).standard_operation_id,
1740 bso.operation_code,
1741 br.resource_type,
1742 p_org_id,
1743 l_res_detail_table(l_index).resource_needed
1744 from cst_activities ca,
1745 bom_standard_operations bso,
1746 bom_resources br
1747 where ca.activity_id = l_res_detail_table(l_index).activity_id
1748 and bso.standard_operation_id = l_res_detail_table(l_index).standard_operation_id
1749 and br.resource_id = l_res_detail_table(l_index).resource_id;
1750 else
1751 insert into BOM_MIXED_MODEL_MAP_RES (
1752 mixed_model_map_id,
1753 resource_id,
1754 resource_code,
1755 activity_id,
1756 activity,
1757 standard_operation_id,
1758 operation_code,
1759 resource_type,
1760 organization_id,
1761 resource_needed
1762 )
1763 select
1764 p_mmm_id,
1765 l_res_detail_table(l_index).resource_id,
1766 br.resource_code,
1767 null,
1768 null,
1769 l_res_detail_table(l_index).standard_operation_id,
1770 bso.operation_code,
1771 br.resource_type,
1772 p_org_id,
1773 l_res_detail_table(l_index).resource_needed
1774 from bom_standard_operations bso,
1775 bom_resources br
1776 where bso.standard_operation_id = l_res_detail_table(l_index).standard_operation_id
1777 and br.resource_id = l_res_detail_table(l_index).resource_id;
1778 end if;
1779 EXIT WHEN l_index = l_res_detail_table.LAST;
1780 l_index := l_res_detail_table.NEXT(l_index);
1781 END LOOP;
1782 l_stmt_num := 5;
1783 --commit;
1784 END GetDetails;
1785
1786
1787 -- =============================== GetCells =================================
1788 --
1789 -- NAME : GetCells
1790 -- DESCRIPTION: Get Products for a given Line (and Family if the Family is
1791 -- specified by the user). Calculates the Demand for each
1792 -- Product. Calculates Machine Time, Labor Time, Total Time
1793 -- and Process Volume for each of the 5 Processes/Line Ops
1794 -- passed to this procedure.
1795 --
1796 -- Insert these values into the table BOM_MIXED_MODEL_MAP_CELLS.
1797 -- REQUIRES : err_text out buffer to return error message
1798 -- MODIFIES : BOM_MIXED_MODEL_MAP_CELLS
1799 --
1800 -- ==========================================================================
1801
1802 PROCEDURE GetCells (
1803 p_mmm_id NUMBER,
1804 p_group_number NUMBER,
1805 p_line_id NUMBER,
1806 p_family_item_id NUMBER,
1807 p_org_id NUMBER,
1808 p_user_id NUMBER,
1809 p_start_date DATE,
1810 p_end_date DATE,
1811 p_demand_type NUMBER,
1812 p_demand_code VARCHAR2,
1813 p_process_line_op NUMBER,
1814 p_hours_per_day NUMBER,
1815 p_demand_days NUMBER,
1816 p_boost_percent NUMBER,
1817 p_operation_type NUMBER,
1818 p_time_type NUMBER,
1819 p_ipk_value NUMBER,
1820 p_time_uom NUMBER,
1821 p_calendar_code VARCHAR2,
1822 p_exception_set_id NUMBER,
1823 p_last_calendar_date DATE,
1824 p_op_code1 VARCHAR2,
1825 p_op_code2 VARCHAR2,
1826 p_op_code3 VARCHAR2,
1827 p_op_code4 VARCHAR2,
1828 p_op_code5 VARCHAR2,
1829 x_line_takt OUT NOCOPY NUMBER,
1830 x_err_text OUT NOCOPY VARCHAR2) IS
1831
1832 CURSOR GetProducts IS
1833 SELECT assembly_item_id AII
1834 FROM bom_operational_routings bor
1835 WHERE mixed_model_map_flag = 1
1836 AND line_id = p_line_id
1837 AND organization_id = p_org_id
1838 AND (p_family_item_id is null
1839 OR
1840 EXISTS (SELECT 'x'
1841 FROM mtl_system_items msi
1842 WHERE msi.inventory_item_id = bor.assembly_item_id
1843 AND msi.organization_id = p_org_id
1844 AND msi.product_family_item_id = p_family_item_id))
1845 ORDER BY assembly_item_id;
1846
1847 CURSOR CheckForProcessSavedMap IS
1848 SELECT mixed_model_map_id
1849 FROM bom_mixed_model_map_header
1850 WHERE line_id = p_line_id
1851 AND ((p_family_item_id is NULL AND family_item_id is NULL)
1852 OR (family_item_id = p_family_item_id))
1853 AND organization_id = p_org_id
1854 AND process_or_lineop = G_PROCESS;
1855
1856 CURSOR CheckForLineopSavedMap IS
1857 SELECT mixed_model_map_id
1858 FROM bom_mixed_model_map_header
1859 WHERE line_id = p_line_id
1860 AND ((p_family_item_id is NULL AND family_item_id is NULL)
1861 OR (family_item_id = p_family_item_id))
1862 AND organization_id = p_org_id
1863 AND process_or_lineop = G_LINEOP;
1864
1865 l_stmt_num NUMBER;
1866 l_counter NUMBER := 0;
1867 l_demand NUMBER := 0;
1868 l_demand_for_display NUMBER := 0;
1869 l_rec_demand NUMBER := 0;
1870 l_forecast_days NUMBER;
1871 l_mdsmps_days NUMBER;
1872 l_prorated_days NUMBER;
1873 l_forecast_demand NUMBER;
1874 l_mdsmps_demand NUMBER;
1875 l_last_week_day DATE;
1876 l_last_period_day DATE;
1877 l_mmm_id NUMBER;
1878 l_line_takt NUMBER;
1879
1880 l_demand_total NUMBER := 0;
1881 l_machine_time NUMBER;
1882 l_labor_time NUMBER;
1883 l_total_time NUMBER;
1884 l_process_volume NUMBER;
1885 l_process_volume_for_display NUMBER;
1886 l_op_code VARCHAR2(4);
1887
1888 /*
1889 l_total_machines1 NUMBER;
1890 l_total_machines2 NUMBER;
1891 l_total_machines3 NUMBER;
1892 l_total_machines4 NUMBER;
1893 l_total_machines5 NUMBER;
1894 */
1895
1896 l_machine_at1 NUMBER := 0;
1897 l_machine_wt1 NUMBER := 0;
1898 l_labor_at1 NUMBER := 0;
1899 l_labor_wt1 NUMBER := 0;
1900 l_total_at1 NUMBER := 0;
1901 l_total_wt1 NUMBER := 0;
1902 l_process_volume1 NUMBER := 0;
1903 l_takt1 NUMBER := 0;
1904 l_machines_needed1 NUMBER := 0;
1905 l_labor_needed1 NUMBER := 0;
1906 l_ipk1 NUMBER := 0;
1907
1908 l_machine_at2 NUMBER := 0;
1909 l_machine_wt2 NUMBER := 0;
1910 l_labor_at2 NUMBER := 0;
1911 l_labor_wt2 NUMBER := 0;
1912 l_total_at2 NUMBER := 0;
1913 l_total_wt2 NUMBER := 0;
1914 l_process_volume2 NUMBER := 0;
1915 l_takt2 NUMBER := 0;
1916 l_machines_needed2 NUMBER := 0;
1917 l_labor_needed2 NUMBER := 0;
1918 l_ipk2 NUMBER := 0;
1919
1920 l_machine_at3 NUMBER := 0;
1921 l_machine_wt3 NUMBER := 0;
1922 l_labor_at3 NUMBER := 0;
1923 l_labor_wt3 NUMBER := 0;
1924 l_total_at3 NUMBER := 0;
1925 l_total_wt3 NUMBER := 0;
1926 l_process_volume3 NUMBER := 0;
1927 l_takt3 NUMBER := 0;
1928 l_machines_needed3 NUMBER := 0;
1929 l_labor_needed3 NUMBER := 0;
1930 l_ipk3 NUMBER := 0;
1931
1932 l_machine_at4 NUMBER := 0;
1933 l_machine_wt4 NUMBER := 0;
1934 l_labor_at4 NUMBER := 0;
1935 l_labor_wt4 NUMBER := 0;
1936 l_total_at4 NUMBER := 0;
1937 l_total_wt4 NUMBER := 0;
1938 l_process_volume4 NUMBER := 0;
1939 l_takt4 NUMBER := 0;
1940 l_machines_needed4 NUMBER := 0;
1941 l_labor_needed4 NUMBER := 0;
1942 l_ipk4 NUMBER := 0;
1943
1944 l_machine_at5 NUMBER := 0;
1945 l_machine_wt5 NUMBER := 0;
1946 l_labor_at5 NUMBER := 0;
1947 l_labor_wt5 NUMBER := 0;
1948 l_total_at5 NUMBER := 0;
1949 l_total_wt5 NUMBER := 0;
1950 l_process_volume5 NUMBER := 0;
1951 l_takt5 NUMBER := 0;
1952 l_machines_needed5 NUMBER := 0;
1953 l_labor_needed5 NUMBER := 0;
1954 l_ipk5 NUMBER := 0;
1955
1956 l_mn_saved NUMBER;
1957 l_ln_saved NUMBER;
1958 l_ipk_saved1 NUMBER;
1959 l_ipk_saved2 NUMBER;
1960 l_ipk_saved3 NUMBER;
1961 l_ipk_saved4 NUMBER;
1962 l_ipk_saved5 NUMBER;
1963
1964 l_mn_delta1 NUMBER;
1965 l_ln_delta1 NUMBER;
1966 l_ipk_delta1 NUMBER;
1967 l_mn_delta2 NUMBER;
1968 l_ln_delta2 NUMBER;
1969 l_ipk_delta2 NUMBER;
1970 l_mn_delta3 NUMBER;
1971 l_ln_delta3 NUMBER;
1972 l_ipk_delta3 NUMBER;
1973 l_mn_delta4 NUMBER;
1974 l_ln_delta4 NUMBER;
1975 l_ipk_delta4 NUMBER;
1976 l_mn_delta5 NUMBER;
1977 l_ln_delta5 NUMBER;
1978 l_ipk_delta5 NUMBER;
1979 --below is new code
1980 l_mc_res_dept1 dept_id_type;
1981 l_mc_res_dept2 dept_id_type;
1982 l_mc_res_dept3 dept_id_type;
1983 l_mc_res_dept4 dept_id_type;
1984 l_mc_res_dept5 dept_id_type;
1985 l_machine_resource1 resource_id_type;
1986 l_machine_resource2 resource_id_type;
1987 l_machine_resource3 resource_id_type;
1988 l_machine_resource4 resource_id_type;
1989 l_machine_resource5 resource_id_type;
1990 l_lb_res_dept1 dept_id_type;
1991 l_lb_res_dept2 dept_id_type;
1992 l_lb_res_dept3 dept_id_type;
1993 l_lb_res_dept4 dept_id_type;
1994 l_lb_res_dept5 dept_id_type;
1995 l_labor_resource1 resource_id_type;
1996 l_labor_resource2 resource_id_type;
1997 l_labor_resource3 resource_id_type;
1998 l_labor_resource4 resource_id_type;
1999 l_labor_resource5 resource_id_type;
2000
2001 l_mc_res_index1 NUMBER := 1;
2002 l_mc_res_index2 NUMBER := 1;
2003 l_mc_res_index3 NUMBER := 1;
2004 l_mc_res_index4 NUMBER := 1;
2005 l_mc_res_index5 NUMBER := 1;
2006 l_lb_res_index1 NUMBER := 1;
2007 l_lb_res_index2 NUMBER := 1;
2008 l_lb_res_index3 NUMBER := 1;
2009 l_lb_res_index4 NUMBER := 1;
2010 l_lb_res_index5 NUMBER := 1;
2011 l_mc_assigned1 NUMBER := 0;
2012 l_mc_assigned2 NUMBER := 0;
2013 l_mc_assigned3 NUMBER := 0;
2014 l_mc_assigned4 NUMBER := 0;
2015 l_mc_assigned5 NUMBER := 0;
2016 l_lb_assigned1 NUMBER := 0;
2017 l_lb_assigned2 NUMBER := 0;
2018 l_lb_assigned3 NUMBER := 0;
2019 l_lb_assigned4 NUMBER := 0;
2020 l_lb_assigned5 NUMBER := 0;
2021 l_takt_time_for_mc_assigned1 NUMBER := 0;
2022 l_takt_time_for_mc_assigned2 NUMBER := 0;
2023 l_takt_time_for_mc_assigned3 NUMBER := 0;
2024 l_takt_time_for_mc_assigned4 NUMBER := 0;
2025 l_takt_time_for_mc_assigned5 NUMBER := 0;
2026 l_takt_time_for_lb_assigned1 NUMBER := 0;
2027 l_takt_time_for_lb_assigned2 NUMBER := 0;
2028 l_takt_time_for_lb_assigned3 NUMBER := 0;
2029 l_takt_time_for_lb_assigned4 NUMBER := 0;
2030 l_takt_time_for_lb_assigned5 NUMBER := 0;
2031 l_takt_time_for_assigned1 NUMBER := 0;
2032 l_takt_time_for_assigned2 NUMBER := 0;
2033 l_takt_time_for_assigned3 NUMBER := 0;
2034 l_takt_time_for_assigned4 NUMBER := 0;
2035 l_takt_time_for_assigned5 NUMBER := 0;
2036 --above is new code
2037 BEGIN
2038
2039 -- GET PRODUCTS
2040
2041 l_stmt_num := 1;
2042
2043 FOR c1rec IN GetProducts LOOP
2044 l_stmt_num := 2;
2045 l_demand := 0;
2046 l_demand_for_display := 0;
2047 l_rec_demand := 0;
2048 l_counter := l_counter + 1;
2049
2050 -- GET DEMAND
2051 GetDemand (
2052 p_org_id,
2053 p_demand_type,
2054 p_line_id,
2055 c1rec.AII,
2056 p_calendar_code,
2057 p_start_date,
2058 p_end_date,
2059 p_last_calendar_date,
2060 p_exception_set_id,
2061 p_demand_code,
2062 l_demand,
2063 l_stmt_num);
2064
2065 l_demand_total := l_demand_total + l_demand;
2066 l_demand_for_display := (l_demand / p_demand_days) *
2067 (1 + nvl((p_boost_percent * .01), 0));
2068
2069 -- GET CELL VALUES
2070
2071 l_stmt_num := 58;
2072
2073 -- LOOP THRU OPS 1 THRU 5
2074
2075 FOR i IN 1..5 LOOP
2076 l_machine_time := 0;
2077 l_labor_time := 0;
2078 l_total_time := 0;
2079 l_process_volume := 0;
2080
2081 IF (i = 1) THEN
2082 l_op_code := p_op_code1;
2083 ELSIF (i = 2) THEN
2084 l_op_code := p_op_code2;
2085 ELSIF (i = 3) THEN
2086 l_op_code := p_op_code3;
2087 ELSIF (i = 4) THEN
2088 l_op_code := p_op_code4;
2089 ELSIF (i = 5) THEN
2090 l_op_code := p_op_code5;
2091 END IF;
2092
2093 -- GET CELL VALUES: MACHINE TIME, LABOR TIME, TOTAL TIME
2094
2095 l_stmt_num := 59;
2096 DECLARE
2097 CURSOR GetCellValues IS
2098 SELECT sum(decode(p_time_type, G_USER_CALC, machine_time_user,
2099 machine_time_calc)) machine_time,
2100 sum(decode(p_time_type, G_USER_CALC, labor_time_user,
2101 labor_time_calc)) labor_time,
2102 sum(decode(p_time_type, G_USER_CALC, total_time_user,
2103 total_time_calc)) total_time,
2104 ((1 + nvl((p_boost_percent * .01), 0)) *
2105 nvl((1/(avg(decode(nvl(reverse_cumulative_yield, 1),0,1,nvl(reverse_cumulative_yield,1))))),1) *
2106 nvl(avg(nvl((net_planning_percent * .01), 1)),1) * l_demand)
2107 process_volume
2108 FROM bom_standard_operations bso,
2109 bom_operation_sequences bos,
2110 bom_operational_routings bor
2111 WHERE bor.assembly_item_id = c1rec.AII
2112 AND bor.mixed_model_map_flag = 1
2113 AND bor.organization_id = p_org_id
2114 AND bor.line_id = p_line_id
2115 AND bor.common_routing_sequence_id = bos.routing_sequence_id
2116 AND bos.standard_operation_id = bso.standard_operation_id
2117 AND NVL(bos.eco_for_production,2) = 2
2118 AND bso.operation_code = l_op_code
2119 AND bso.organization_id = p_org_id
2120 AND bso.line_id = p_line_id
2121 ANd bso.operation_type = p_operation_type;
2122
2123 BEGIN
2124 IF (l_op_code is NULL) THEN
2125 GOTO insert_record;
2126 END IF;
2127
2128 l_stmt_num := 60;
2129 FOR c3rec IN GetCellValues LOOP
2130 l_machine_time := nvl(c3rec.machine_time, 0);
2131 l_labor_time := nvl(c3rec.labor_time, 0);
2132 l_total_time := nvl(c3rec.total_time, 0);
2133 l_process_volume := nvl(c3rec.process_volume, 0);
2134 END LOOP;
2135
2136 /**/
2137 l_process_volume_for_display := l_process_volume / p_demand_days ;
2138 /**/
2139
2140 -- CALCULATE SUMMARY VALUES
2141
2142 l_stmt_num := 62;
2143 IF (i = 1) THEN
2144 l_machine_at1 := l_machine_at1 +
2145 (l_machine_time * l_process_volume);
2146 l_labor_at1 := l_labor_at1 +
2147 (l_labor_time * l_process_volume);
2148 l_total_at1 := l_total_at1 +
2149 (l_total_time * l_process_volume);
2150 l_process_volume1 := l_process_volume1 + l_process_volume;
2151
2152 IF (nvl(l_process_volume1, 0) = 0) THEN
2153 l_machine_wt1 := 0;
2154 l_labor_wt1 := 0;
2155 l_total_wt1 := 0;
2156 l_takt1 := null;
2157 ELSE
2158 l_machine_wt1 := l_machine_at1/l_process_volume1;
2159 l_labor_wt1 := l_labor_at1/l_process_volume1;
2160 l_total_wt1 := l_total_at1/l_process_volume1;
2161 l_takt1 := (p_hours_per_day * p_demand_days)/
2162 l_process_volume1;
2163 END IF;
2164
2165 IF (nvl(l_takt1, 0) = 0) THEN
2166 l_machines_needed1 := null;
2167 l_labor_needed1 := null;
2168 ELSE
2169 l_machines_needed1 := l_machine_wt1/l_takt1;
2170 l_labor_needed1 := l_labor_wt1/l_takt1;
2171 END IF;
2172 ELSIF (i = 2) THEN
2173 l_stmt_num := 63;
2174 l_machine_at2 := l_machine_at2 +
2175 (l_machine_time * l_process_volume);
2176 l_labor_at2 := l_labor_at2 +
2177 (l_labor_time * l_process_volume);
2178 l_total_at2 := l_total_at2 +
2179 (l_total_time * l_process_volume);
2180 l_process_volume2 := l_process_volume2 + l_process_volume;
2181
2182 IF (nvl(l_process_volume2, 0) = 0) THEN
2183 l_machine_wt2 := 0;
2184 l_labor_wt2 := 0;
2185 l_total_wt2 := 0;
2186 l_takt2 := null;
2187 ELSE
2188 l_machine_wt2 := l_machine_at2/l_process_volume2;
2189 l_labor_wt2 := l_labor_at2/l_process_volume2;
2190 l_total_wt2 := l_total_at2/l_process_volume2;
2191 l_takt2 := (p_hours_per_day * p_demand_days)/
2192 l_process_volume2;
2193 END IF;
2194
2195 IF (nvl(l_takt2, 0) = 0) THEN
2196 l_machines_needed2 := null;
2197 l_labor_needed2 := null;
2198 ELSE
2199 l_machines_needed2 := l_machine_wt2/l_takt2;
2200 l_labor_needed2 := l_labor_wt2/l_takt2;
2201 END IF;
2202 ELSIF (i = 3) THEN
2203 l_stmt_num := 64;
2204 l_machine_at3 := l_machine_at3 +
2205 (l_machine_time * l_process_volume);
2206 l_labor_at3 := l_labor_at3 +
2207 (l_labor_time * l_process_volume);
2208 l_total_at3 := l_total_at3 +
2209 (l_total_time * l_process_volume);
2210 l_process_volume3 := l_process_volume3 + l_process_volume;
2211
2212 IF (nvl(l_process_volume3, 0) = 0) THEN
2213 l_machine_wt3 := 0;
2214 l_labor_wt3 := 0;
2215 l_total_wt3 := 0;
2216 l_takt3 := null;
2217 ELSE
2218 l_machine_wt3 := l_machine_at3/l_process_volume3;
2219 l_labor_wt3 := l_labor_at3/l_process_volume3;
2220 l_total_wt3 := l_total_at3/l_process_volume3;
2221 l_takt3 := (p_hours_per_day * p_demand_days)/
2222 l_process_volume3;
2223 END IF;
2224
2225 IF (nvl(l_takt3, 0) = 0) THEN
2226 l_machines_needed3 := null;
2227 l_labor_needed3 := null;
2228 ELSE
2229 l_machines_needed3 := l_machine_wt3/l_takt3;
2230 l_labor_needed3 := l_labor_wt3/l_takt3;
2231 END IF;
2232 ELSIF (i = 4) THEN
2233 l_stmt_num := 65;
2234 l_machine_at4 := l_machine_at4 +
2235 (l_machine_time * l_process_volume);
2236 l_labor_at4 := l_labor_at4 +
2237 (l_labor_time * l_process_volume);
2238 l_total_at4 := l_total_at4 +
2239 (l_total_time * l_process_volume);
2240 l_process_volume4 := l_process_volume4 + l_process_volume;
2241
2242 IF (nvl(l_process_volume4, 0) = 0) THEN
2243 l_machine_wt4 := 0;
2244 l_labor_wt4 := 0;
2245 l_total_wt4 := 0;
2246 l_takt4 := null;
2247 ELSE
2248 l_machine_wt4 := l_machine_at4/l_process_volume4;
2249 l_labor_wt4 := l_labor_at4/l_process_volume4;
2250 l_total_wt4 := l_total_at4/l_process_volume4;
2251 l_takt4 := (p_hours_per_day * p_demand_days)/
2252 l_process_volume4;
2253 END IF;
2254
2255 IF (nvl(l_takt4, 0) = 0) THEN
2256 l_machines_needed4 := null;
2257 l_labor_needed4 := null;
2258 ELSE
2259 l_machines_needed4 := l_machine_wt4/l_takt4;
2260 l_labor_needed4 := l_labor_wt4/l_takt4;
2261 END IF;
2262 ELSIF (i = 5) THEN
2263 l_stmt_num := 66;
2264 l_machine_at5 := l_machine_at5 +
2265 (l_machine_time * l_process_volume);
2266 l_labor_at5 := l_labor_at5 +
2267 (l_labor_time * l_process_volume);
2268 l_total_at5 := l_total_at5 +
2269 (l_total_time * l_process_volume);
2270 l_process_volume5 := l_process_volume5 + l_process_volume;
2271
2272 IF (nvl(l_process_volume5, 0) = 0) THEN
2273 l_machine_wt5 := 0;
2274 l_labor_wt5 := 0;
2275 l_total_wt5 := 0;
2276 l_takt5 := null;
2277 ELSE
2278 l_machine_wt5 := l_machine_at5/l_process_volume5;
2279 l_labor_wt5 := l_labor_at5/l_process_volume5;
2280 l_total_wt5 := l_total_at5/l_process_volume5;
2281 l_takt5 := (p_hours_per_day * p_demand_days)/
2282 l_process_volume5;
2283 END IF;
2284
2285 IF (nvl(l_takt5, 0) = 0) THEN
2286 l_machines_needed5 := null;
2287 l_labor_needed5 := null;
2288 ELSE
2289 l_machines_needed5 := l_machine_wt5/l_takt5;
2290 l_labor_needed5 := l_labor_wt5/l_takt5;
2291 END IF;
2292 END IF;
2293
2294 -- INSERT CELL RECORD INTO TABLE
2295 l_stmt_num := 67;
2296 l_machine_time := ConvertTime(l_machine_time, p_time_uom);
2297 l_labor_time := ConvertTime(l_labor_time, p_time_uom);
2298 l_total_time := ConvertTime(l_total_time, p_time_uom);
2299
2300 -- below is my code
2301
2302 IF (i = 1) THEN
2303 -- for machine resource
2304 GetResourceId(
2305 c1rec.AII,
2306 p_org_id,
2307 p_line_id,
2308 1,
2309 l_op_code,
2310 p_process_line_op,
2311 l_mc_res_index1,
2312 l_mc_res_dept1,
2313 l_machine_resource1);
2314
2315 -- for labor resource
2316 GetResourceId(
2317 c1rec.AII,
2318 p_org_id,
2319 p_line_id,
2320 2,
2321 l_op_code,
2322 p_process_line_op,
2323 l_lb_res_index1,
2324 l_lb_res_dept1,
2325 l_labor_resource1);
2326
2327 ELSIF (i = 2) THEN
2328 -- for machine resource
2329 GetResourceId(
2330 c1rec.AII,
2331 p_org_id,
2332 p_line_id,
2333 1,
2334 l_op_code,
2335 p_process_line_op,
2336 l_mc_res_index2,
2337 l_mc_res_dept2,
2338 l_machine_resource2);
2339 -- for labor resource
2340 GetResourceId(
2341 c1rec.AII,
2342 p_org_id,
2343 p_line_id,
2344 2,
2345 l_op_code,
2346 p_process_line_op,
2347 l_lb_res_index2,
2348 l_lb_res_dept2,
2349 l_labor_resource2);
2350
2351 ELSIF (i = 3) THEN
2352 -- for machine resource
2353 GetResourceId(
2354 c1rec.AII,
2355 p_org_id,
2356 p_line_id,
2357 1,
2358 l_op_code,
2359 p_process_line_op,
2360 l_mc_res_index3,
2361 l_mc_res_dept3,
2362 l_machine_resource3);
2363 -- for labor resource
2364 GetResourceId(
2365 c1rec.AII,
2366 p_org_id,
2367 p_line_id,
2368 2,
2369 l_op_code,
2370 p_process_line_op,
2371 l_lb_res_index3,
2372 l_lb_res_dept3,
2373 l_labor_resource3);
2374
2375 ELSIF (i = 4) THEN
2376 -- for machine resource
2377 GetResourceId(
2378 c1rec.AII,
2379 p_org_id,
2380 p_line_id,
2381 1,
2382 l_op_code,
2383 p_process_line_op,
2384 l_mc_res_index4,
2385 l_mc_res_dept4,
2386 l_machine_resource4);
2387 -- for labor resource
2388 GetResourceId(
2389 c1rec.AII,
2390 p_org_id,
2391 p_line_id,
2392 2,
2393 l_op_code,
2394 p_process_line_op,
2395 l_lb_res_index4,
2396 l_lb_res_dept4,
2397 l_labor_resource4);
2398
2399 ELSIF (i = 5) THEN
2400 -- for machine resource
2401 GetResourceId(
2402 c1rec.AII,
2403 p_org_id,
2404 p_line_id,
2405 1,
2406 l_op_code,
2407 p_process_line_op,
2408 l_mc_res_index5,
2409 l_mc_res_dept5,
2410 l_machine_resource5);
2411 -- for labor resource
2412 GetResourceId(
2413 c1rec.AII,
2414 p_org_id,
2415 p_line_id,
2416 2,
2417 l_op_code,
2418 p_process_line_op,
2419 l_lb_res_index5,
2420 l_lb_res_dept5,
2421 l_labor_resource5);
2422
2423 END IF; --END 5 LOOPS FOR RESOURCES
2424 -- above is my code***************************************
2425 <<insert_record>>
2426 l_stmt_num := 68;
2427 IF(l_op_code IS NOT NULL) THEN
2428 INSERT INTO bom_mixed_model_map_cells
2429 (MIXED_MODEL_MAP_ID,
2430 GROUP_NUMBER,
2431 SEQUENCE_ID,
2432 PRODUCT_ITEM_ID,
2433 DEMAND,
2434 MACHINE_TIME,
2435 LABOR_TIME,
2436 TOTAL_TIME,
2437 PROCESS_VOLUME,
2438 CREATED_BY,
2439 CREATION_DATE,
2440 LAST_UPDATED_BY,
2441 LAST_UPDATE_DATE)
2442 VALUES
2443 (p_mmm_id,
2444 p_group_number,
2445 i,
2446 c1rec.AII,
2447 ROUND(nvl(l_demand_for_display, 0), G_DECIMAL),
2448 ROUND(nvl(l_machine_time, 0), G_DECIMAL),
2449 ROUND(nvl(l_labor_time, 0), G_DECIMAL),
2450 ROUND(nvl(l_total_time, 0), G_DECIMAL),
2451 ROUND(nvl(l_process_volume_for_display, 0), G_DECIMAL),
2452 p_user_id,
2453 sysdate,
2454 p_user_id,
2455 sysdate);
2456 ELSE
2457 INSERT INTO bom_mixed_model_map_cells
2458 (MIXED_MODEL_MAP_ID,
2459 GROUP_NUMBER,
2460 SEQUENCE_ID,
2461 PRODUCT_ITEM_ID,
2462 DEMAND,
2463 MACHINE_TIME,
2464 LABOR_TIME,
2465 TOTAL_TIME,
2466 PROCESS_VOLUME,
2467 CREATED_BY,
2468 CREATION_DATE,
2469 LAST_UPDATED_BY,
2470 LAST_UPDATE_DATE)
2471 VALUES
2472 (p_mmm_id,
2473 p_group_number,
2474 i,
2475 c1rec.AII,
2476 ROUND(nvl(l_demand_for_display, 0), G_DECIMAL),
2477 null,
2478 null,
2479 null,
2480 null,
2481 p_user_id,
2482 sysdate,
2483 p_user_id,
2484 sysdate);
2485 END IF;
2486 END;
2487 END LOOP; -- FOR i IN 1..5
2488 END LOOP; -- GetProducts
2489
2490 --below is new code
2491 -- for Capacity
2492 GetCapacity(
2493 l_mc_res_index1,
2494 l_machine_resource1,
2495 l_mc_res_dept1,
2496 l_mc_assigned1);
2497
2498 GetCapacity(
2499 l_lb_res_index1,
2500 l_labor_resource1,
2501 l_lb_res_dept1,
2502 l_lb_assigned1);
2503 GetCapacity(
2504 l_mc_res_index2,
2505 l_machine_resource2,
2506 l_mc_res_dept2,
2507 l_mc_assigned2);
2508
2509 GetCapacity(
2510 l_lb_res_index2,
2511 l_labor_resource2,
2512 l_lb_res_dept2,
2513 l_lb_assigned2);
2514 GetCapacity(
2515 l_mc_res_index3,
2516 l_machine_resource3,
2517 l_mc_res_dept3,
2518 l_mc_assigned3);
2519
2520 GetCapacity(
2521 l_lb_res_index3,
2522 l_labor_resource3,
2523 l_lb_res_dept3,
2524 l_lb_assigned3);
2525 GetCapacity(
2526 l_mc_res_index4,
2527 l_machine_resource4,
2528 l_mc_res_dept4,
2529 l_mc_assigned4);
2530
2531 GetCapacity(
2532 l_lb_res_index4,
2533 l_labor_resource4,
2534 l_lb_res_dept4,
2535 l_lb_assigned4);
2536 GetCapacity(
2537 l_mc_res_index5,
2538 l_machine_resource5,
2539 l_mc_res_dept5,
2540 l_mc_assigned5);
2541
2542 GetCapacity(
2543 l_lb_res_index5,
2544 l_labor_resource5,
2545 l_lb_res_dept5,
2546 l_lb_assigned5);
2547 -- calculate assigned TAKT time for first process/lineOp
2548 IF (nvl(l_machine_wt1, 0) = 0) OR
2549 (nvl(l_mc_assigned1, 0) = 0) THEN
2550 l_takt_time_for_mc_assigned1 := 0;
2551 ELSE
2552 l_takt_time_for_mc_assigned1 := l_machine_wt1/l_mc_assigned1;
2553 END IF;
2554 IF (nvl(l_labor_wt1, 0) = 0) OR
2555 (nvl(l_lb_assigned1, 0) = 0) THEN
2556 l_takt_time_for_lb_assigned1 := 0;
2557 ELSE
2558 l_takt_time_for_lb_assigned1 := l_labor_wt1/l_lb_assigned1;
2559 END IF;
2560 IF (l_takt_time_for_mc_assigned1 >= l_takt_time_for_lb_assigned1 ) THEN
2561 l_takt_time_for_assigned1 := l_takt_time_for_mc_assigned1;
2562 ELSE
2563 l_takt_time_for_assigned1 := l_takt_time_for_lb_assigned1;
2564 END IF;
2565 -- calculate assigned TAKT time for second process/lineOp
2566 IF (nvl(l_machine_wt2, 0) = 0) OR
2567 (nvl(l_mc_assigned2, 0) = 0) THEN
2568 l_takt_time_for_mc_assigned2 := 0;
2569 ELSE
2570 l_takt_time_for_mc_assigned2 := l_machine_wt2/l_mc_assigned2;
2571 END IF;
2572 IF (nvl(l_labor_wt2, 0) = 0) OR
2573 (nvl(l_lb_assigned2, 0) = 0) THEN
2574 l_takt_time_for_lb_assigned2 := 0;
2575 ELSE
2576 l_takt_time_for_lb_assigned2 := l_labor_wt2/l_lb_assigned2;
2577 END IF;
2578 IF (l_takt_time_for_mc_assigned2 >= l_takt_time_for_lb_assigned2 ) THEN
2579 l_takt_time_for_assigned2 := l_takt_time_for_mc_assigned2;
2580 ELSE
2581 l_takt_time_for_assigned2 := l_takt_time_for_lb_assigned2;
2582 END IF;
2583 -- calculate assigned TAKT time for third process/lineOp
2584 IF (nvl(l_machine_wt3, 0) = 0) OR
2585 (nvl(l_mc_assigned3, 0) = 0) THEN
2586 l_takt_time_for_mc_assigned3 := 0;
2587 ELSE
2588 l_takt_time_for_mc_assigned3 := l_machine_wt3/l_mc_assigned3;
2589 END IF;
2590 IF (nvl(l_labor_wt3, 0) = 0) OR
2591 (nvl(l_lb_assigned3, 0) = 0) THEN
2592 l_takt_time_for_lb_assigned3 := 0;
2593 ELSE
2594 l_takt_time_for_lb_assigned3 := l_labor_wt3/l_lb_assigned3;
2595 END IF;
2596 IF (l_takt_time_for_mc_assigned3 >= l_takt_time_for_lb_assigned3 ) THEN
2597 l_takt_time_for_assigned3 := l_takt_time_for_mc_assigned3;
2598 ELSE
2599 l_takt_time_for_assigned3 := l_takt_time_for_lb_assigned3;
2600 END IF;
2601 -- calculate assigned TAKT time for fourth process/lineOp
2602 IF (nvl(l_machine_wt4, 0) = 0) OR
2603 (nvl(l_mc_assigned4, 0) = 0) THEN
2604 l_takt_time_for_mc_assigned4 := 0;
2605 ELSE
2606 l_takt_time_for_mc_assigned4 := l_machine_wt4/l_mc_assigned4;
2607 END IF;
2608 IF (nvl(l_labor_wt4, 0) = 0) OR
2609 (nvl(l_lb_assigned4, 0) = 0) THEN
2610 l_takt_time_for_lb_assigned4 := 0;
2611 ELSE
2612 l_takt_time_for_lb_assigned4 := l_labor_wt4/l_lb_assigned4;
2613 END IF;
2614 IF (l_takt_time_for_mc_assigned4 >= l_takt_time_for_lb_assigned4 ) THEN
2615 l_takt_time_for_assigned4 := l_takt_time_for_mc_assigned4;
2616 ELSE
2617 l_takt_time_for_assigned4 := l_takt_time_for_lb_assigned4;
2618 END IF;
2619 -- calculate assigned TAKT time for fifth process/lineOp
2620 IF (nvl(l_machine_wt5, 0) = 0) OR
2621 (nvl(l_mc_assigned5, 0) = 0) THEN
2622 l_takt_time_for_mc_assigned5 := 0;
2623 ELSE
2624 l_takt_time_for_mc_assigned5 := l_machine_wt3/l_mc_assigned5;
2625 END IF;
2626 IF (nvl(l_labor_wt5, 0) = 0) OR
2627 (nvl(l_lb_assigned5, 0) = 0) THEN
2628 l_takt_time_for_lb_assigned5 := 0;
2629 ELSE
2630 l_takt_time_for_lb_assigned5 := l_labor_wt5/l_lb_assigned5;
2631 END IF;
2632 IF (l_takt_time_for_mc_assigned5 >= l_takt_time_for_lb_assigned5 ) THEN
2633 l_takt_time_for_assigned5 := l_takt_time_for_mc_assigned5;
2634 ELSE
2635 l_takt_time_for_assigned5 := l_takt_time_for_lb_assigned5;
2636 END IF;
2637 --above is new code
2638 -- GET LINE TAKT TIME
2639 l_stmt_num := 69;
2640 IF (nvl(l_demand_total, 0) = 0) THEN
2641 x_err_text := 'BOM_LINE_TAKT_ERROR';
2642 l_line_takt := null;
2643 x_line_takt := l_line_takt;
2644 ELSE
2645 l_line_takt := ConvertTime(p_hours_per_day/
2646 ((l_demand_total/p_demand_days)*(1 +
2647 nvl((p_boost_percent * .01), 0))), p_time_uom);
2648 x_line_takt := ROUND(l_line_takt, G_DECIMAL);
2649
2650 END IF;
2651
2652 -- CALCULATE DELTA VALUES
2653
2654 -- CHECK IF SAVED MAP EXISTS
2655 l_stmt_num := 70;
2656 IF (p_process_line_op = G_PROCESS) THEN
2657 FOR c5rec IN CheckForProcessSavedMap LOOP
2658 l_mmm_id := c5rec.mixed_model_map_id;
2659 END LOOP;
2660 ELSE
2661 l_stmt_num := 71;
2662 FOR c6rec IN CheckForLineOpSavedMap LOOP
2663 l_mmm_id := c6rec.mixed_model_map_id;
2664 END LOOP;
2665 END IF;
2666
2667 -- SAVED MAP EXISTS
2668
2669 IF (l_mmm_id is NOT NULL) THEN
2670 IF (p_op_code1 is NOT NULL) THEN
2671 DECLARE
2672 CURSOR GetSavedValues IS
2673 SELECT machines_needed MN, labor_needed LN,
2674 in_process_kanban IPK
2675 FROM bom_mixed_model_map_processes
2676 WHERE mixed_model_map_id = l_mmm_id
2677 AND operation_code = p_op_code1;
2678 BEGIN
2679 l_stmt_num := 72;
2680 l_mn_saved := null;
2681 l_ln_saved := null;
2682 l_ipk_saved1 := null;
2683 FOR c7rec IN GetSavedValues LOOP
2684 l_mn_saved := c7rec.MN;
2685 l_ln_saved := c7rec.LN;
2686 l_ipk_saved1 := c7rec.IPK;
2687 END LOOP;
2688
2689 l_mn_delta1 := nvl(l_machines_needed1, 0) - l_mn_saved;
2690 l_ln_delta1 := nvl(l_labor_needed1, 0) - l_ln_saved;
2691 END;
2692 END IF;
2693
2694 IF (p_op_code2 is NOT NULL) THEN
2695 DECLARE
2696 CURSOR GetSavedValues IS
2697 SELECT machines_needed MN, labor_needed LN,
2698 in_process_kanban IPK
2699 FROM bom_mixed_model_map_processes
2700 WHERE mixed_model_map_id = l_mmm_id
2701 AND operation_code = p_op_code2;
2702 BEGIN
2703 l_stmt_num := 73;
2704 l_mn_saved := null;
2705 l_ln_saved := null;
2706 l_ipk_saved2 := null;
2707 FOR c8rec IN GetSavedValues LOOP
2708 l_mn_saved := c8rec.MN;
2709 l_ln_saved := c8rec.LN;
2710 l_ipk_saved2 := c8rec.IPK;
2711 END LOOP;
2712
2713 l_mn_delta2 := nvl(l_machines_needed2, 0) - l_mn_saved;
2714 l_ln_delta2 := nvl(l_labor_needed2, 0) - l_ln_saved;
2715 END;
2716 END IF;
2717
2718 IF (p_op_code3 is NOT NULL) THEN
2719 DECLARE
2720 CURSOR GetSavedValues IS
2721 SELECT machines_needed MN, labor_needed LN,
2722 in_process_kanban IPK
2723 FROM bom_mixed_model_map_processes
2724 WHERE mixed_model_map_id = l_mmm_id
2725 AND operation_code = p_op_code3;
2726 BEGIN
2727 l_stmt_num := 74;
2728 l_mn_saved := null;
2729 l_ln_saved := null;
2730 l_ipk_saved3 := null;
2731 FOR c9rec IN GetSavedValues LOOP
2732 l_mn_saved := c9rec.MN;
2733 l_ln_saved := c9rec.LN;
2734 l_ipk_saved3 := c9rec.IPK;
2735 END LOOP;
2736
2737 l_mn_delta3 := nvl(l_machines_needed3, 0) - l_mn_saved;
2738 l_ln_delta3 := nvl(l_labor_needed3, 0) - l_ln_saved;
2739 END;
2740 END IF;
2741
2742 IF (p_op_code4 is NOT NULL) THEN
2743 DECLARE
2744 CURSOR GetSavedValues IS
2745 SELECT machines_needed MN, labor_needed LN,
2746 in_process_kanban IPK
2747 FROM bom_mixed_model_map_processes
2748 WHERE mixed_model_map_id = l_mmm_id
2749 AND operation_code = p_op_code4;
2750 BEGIN
2751 l_stmt_num := 75;
2752 l_mn_saved := null;
2753 l_ln_saved := null;
2754 l_ipk_saved4 := null;
2755 FOR c10rec IN GetSavedValues LOOP
2756 l_mn_saved := c10rec.MN;
2757 l_ln_saved := c10rec.LN;
2758 l_ipk_saved4 := c10rec.IPK;
2759 END LOOP;
2760
2761 l_mn_delta4 := nvl(l_machines_needed4, 0) - l_mn_saved;
2762 l_ln_delta4 := nvl(l_labor_needed4, 0) - l_ln_saved;
2763 END;
2764 END IF;
2765
2766 IF (p_op_code5 is NOT NULL) THEN
2767 DECLARE
2768 CURSOR GetSavedValues IS
2769 SELECT machines_needed MN, labor_needed LN,
2770 in_process_kanban IPK
2771 FROM bom_mixed_model_map_processes
2772 WHERE mixed_model_map_id = l_mmm_id
2773 AND operation_code = p_op_code5;
2774 BEGIN
2775 l_stmt_num := 76;
2776 l_mn_saved := null;
2777 l_ln_saved := null;
2778 l_ipk_saved5 := null;
2779 FOR c11rec IN GetSavedValues LOOP
2780 l_mn_saved := c11rec.MN;
2781 l_ln_saved := c11rec.LN;
2782 l_ipk_saved5 := c11rec.IPK;
2783 END LOOP;
2784
2785 l_mn_delta5 := nvl(l_machines_needed5, 0) - l_mn_saved;
2786 l_ln_delta5 := nvl(l_labor_needed5, 0) - l_ln_saved;
2787 END;
2788 END IF;
2789 END IF; -- IF SAVED MAP EXISTS
2790
2791
2792 -- SAVE FINAL SUMMARY INFORMATION
2793
2794 l_stmt_num := 77;
2795 l_machine_wt1 := ConvertTime(l_machine_wt1, p_time_uom);
2796 l_labor_wt1 := ConvertTime(l_labor_wt1, p_time_uom);
2797 l_total_wt1 := ConvertTime(l_total_wt1, p_time_uom);
2798 l_takt1 := ConvertTime(l_takt1, p_time_uom);
2799
2800 l_machine_wt2 := ConvertTime(l_machine_wt2, p_time_uom);
2801 l_labor_wt2 := ConvertTime(l_labor_wt2, p_time_uom);
2802 l_total_wt2 := ConvertTime(l_total_wt2, p_time_uom);
2803 l_takt2 := ConvertTime(l_takt2, p_time_uom);
2804
2805 l_machine_wt3 := ConvertTime(l_machine_wt3, p_time_uom);
2806 l_labor_wt3 := ConvertTime(l_labor_wt3, p_time_uom);
2807 l_total_wt3 := ConvertTime(l_total_wt3, p_time_uom);
2808 l_takt3 := ConvertTime(l_takt3, p_time_uom);
2809
2810 l_machine_wt4 := ConvertTime(l_machine_wt4, p_time_uom);
2811 l_labor_wt4 := ConvertTime(l_labor_wt4, p_time_uom);
2812 l_total_wt4 := ConvertTime(l_total_wt4, p_time_uom);
2813 l_takt4 := ConvertTime(l_takt4, p_time_uom);
2814
2815 l_machine_wt5 := ConvertTime(l_machine_wt5, p_time_uom);
2816 l_labor_wt5 := ConvertTime(l_labor_wt5, p_time_uom);
2817 l_total_wt5 := ConvertTime(l_total_wt5, p_time_uom);
2818 l_takt5 := ConvertTime(l_takt5, p_time_uom);
2819
2820 --below is new code
2821 l_takt_time_for_assigned1 := ConvertTime(l_takt_time_for_assigned1, p_time_uom);
2822 l_takt_time_for_assigned2 := ConvertTime(l_takt_time_for_assigned2, p_time_uom);
2823 l_takt_time_for_assigned3 := ConvertTime(l_takt_time_for_assigned3, p_time_uom);
2824 l_takt_time_for_assigned4 := ConvertTime(l_takt_time_for_assigned4, p_time_uom);
2825 l_takt_time_for_assigned5 := ConvertTime(l_takt_time_for_assigned5, p_time_uom);
2826 --above is new code
2827 l_stmt_num := 78;
2828
2829
2830 -- CALCULATE IPKS NEEDED
2831
2832 IF (nvl(l_total_wt1, 0) = 0 OR
2833 nvl(l_line_takt, 0) = 0) THEN
2834 l_ipk1 := null;
2835 ELSE
2836 IF (p_ipk_value = G_TOTAL_IPK) OR
2837 (l_mc_assigned1 = 0) THEN
2838 l_ipk1 := ((l_total_wt1 - l_line_takt) *
2839 ConvertTime(p_hours_per_day, p_time_uom))/
2840 (l_total_wt1 * l_line_takt);
2841 ELSE -- BY MACHINE
2842 l_ipk1 := ((l_total_wt1 - l_line_takt) *
2843 ConvertTime(p_hours_per_day, p_time_uom))/
2844 (l_total_wt1 * l_line_takt * nvl(l_mc_assigned1,1));
2845 END IF;
2846 IF (l_ipk1 < 0) THEN
2847 l_ipk1 := 0;
2848 END IF;
2849 END IF;
2850
2851
2852 IF (nvl(l_total_wt2, 0) = 0 OR
2853 nvl(l_line_takt, 0) = 0) THEN
2854 l_ipk2 := null;
2855 ELSE
2856 IF (p_ipk_value = G_TOTAL_IPK) OR
2857 (l_mc_assigned2 = 0) THEN
2858 l_ipk2 := ((l_total_wt2 - l_line_takt) *
2859 ConvertTime(p_hours_per_day, p_time_uom))/
2860 (l_total_wt2 * l_line_takt);
2861 ELSE -- BY MACHINE
2862 l_ipk2 := ((l_total_wt2 - l_line_takt) *
2863 ConvertTime(p_hours_per_day, p_time_uom))/
2864 (l_total_wt2 * l_line_takt * nvl(l_mc_assigned2,1));
2865 END IF;
2866 IF (l_ipk2 < 0) THEN
2867 l_ipk2 := 0;
2868 END IF;
2869 END IF;
2870
2871 IF (nvl(l_total_wt3, 0) = 0 OR
2872 nvl(l_line_takt, 0) = 0) THEN
2873 l_ipk3 := null;
2874 ELSE
2875 IF (p_ipk_value = G_TOTAL_IPK) OR
2876 (l_mc_assigned3 = 0) THEN
2877 l_ipk3 := ((l_total_wt3 - l_line_takt) *
2878 ConvertTime(p_hours_per_day, p_time_uom))/
2879 (l_total_wt3 * l_line_takt);
2880 ELSE -- BY MACHINE
2881 l_ipk3 := ((l_total_wt3 - l_line_takt) *
2882 ConvertTime(p_hours_per_day, p_time_uom))/
2883 (l_total_wt3 * l_line_takt * nvl(l_mc_assigned3,1));
2884 END IF;
2885 IF (l_ipk3 < 0) THEN
2886 l_ipk3 := 0;
2887 END IF;
2888 END IF;
2889
2890 IF (nvl(l_total_wt4, 0) = 0 OR
2891 nvl(l_line_takt, 0) = 0) THEN
2892 l_ipk4 := null;
2893 ELSE
2894 IF (p_ipk_value = G_TOTAL_IPK) OR
2895 (l_mc_assigned4 = 0) THEN
2896 l_ipk4 := ((l_total_wt4 - l_line_takt) *
2897 ConvertTime(p_hours_per_day, p_time_uom))/
2898 (l_total_wt4 * l_line_takt);
2899 ELSE -- BY MACHINE
2900 l_ipk4 := ((l_total_wt4 - l_line_takt) *
2901 ConvertTime(p_hours_per_day, p_time_uom))/
2902 (l_total_wt4 * l_line_takt * nvl(l_mc_assigned4,1));
2903 END IF;
2904 IF (l_ipk4 < 0) THEN
2905 l_ipk4 := 0;
2906 END IF;
2907 END IF;
2908
2909 IF (nvl(l_total_wt5, 0) = 0 OR
2910 nvl(l_line_takt, 0) = 0) THEN
2911 l_ipk5 := null;
2912 ELSE
2913 IF (p_ipk_value = G_TOTAL_IPK) OR
2914 (l_mc_assigned5 = 0) THEN
2915 l_ipk5 := ((l_total_wt5 - l_line_takt) *
2916 ConvertTime(p_hours_per_day, p_time_uom))/
2917 (l_total_wt5 * l_line_takt);
2918 ELSE -- BY MACHINE
2919 l_ipk5 := ((l_total_wt5 - l_line_takt) *
2920 ConvertTime(p_hours_per_day, p_time_uom))/
2921 (l_total_wt5 * l_line_takt * nvl(l_mc_assigned5,1));
2922 END IF;
2923 IF (l_ipk5 < 0) THEN
2924 l_ipk5 := 0;
2925 END IF;
2926 END IF;
2927
2928 -- CALCULATE IPK DELTAS
2929
2930 l_ipk_delta1 := nvl(l_ipk1, 0) - l_ipk_saved1;
2931 l_ipk_delta2 := nvl(l_ipk2, 0) - l_ipk_saved2;
2932 l_ipk_delta3 := nvl(l_ipk3, 0) - l_ipk_saved3;
2933 l_ipk_delta4 := nvl(l_ipk4, 0) - l_ipk_saved4;
2934 l_ipk_delta5 := nvl(l_ipk5, 0) - l_ipk_saved5;
2935
2936 -- UPDATE PROCESSES TABLE
2937 --below code changed slightly
2938 UPDATE bom_mixed_model_map_processes
2939 SET machine_weighted_time = ROUND(nvl(l_machine_wt1, 0), G_DECIMAL),
2940 labor_weighted_time = ROUND(nvl(l_labor_wt1, 0), G_DECIMAL),
2941 total_weighted_time = ROUND(nvl(l_total_wt1, 0), G_DECIMAL),
2942 machines_needed = ROUND(nvl(l_machines_needed1, 0), G_DECIMAL),
2943 machines_assigned = ROUND(nvl(l_mc_assigned1, 0), G_DECIMAL),
2944 labor_needed = ROUND(nvl(l_labor_needed1, 0), G_DECIMAL),
2945 labor_assigned = ROUND(nvl(l_lb_assigned1, 0), G_DECIMAL),
2946 takt_time = ROUND(nvl(l_takt1, 0), G_DECIMAL),
2947 takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned1, 0), G_DECIMAL),
2948 in_process_kanban = ROUND(nvl(l_ipk1, 0), G_DECIMAL),
2949 machines_needed_delta = ROUND(l_mn_delta1, G_DECIMAL),
2950 labor_needed_delta = ROUND(l_ln_delta1, G_DECIMAL),
2951 in_process_kanban_delta = ROUND(l_ipk_delta1, G_DECIMAL)
2952 WHERE mixed_model_map_id = p_mmm_id
2953 AND group_number = p_group_number
2954 AND sequence_id = 1 ;
2955
2956 l_stmt_num := 79;
2957 UPDATE bom_mixed_model_map_processes
2958 SET machine_weighted_time = ROUND(nvl(l_machine_wt2, 0), G_DECIMAL),
2959 labor_weighted_time = ROUND(nvl(l_labor_wt2, 0), G_DECIMAL),
2960 total_weighted_time = ROUND(nvl(l_total_wt2, 0), G_DECIMAL),
2961 machines_needed = ROUND(nvl(l_machines_needed2, 0), G_DECIMAL),
2962 machines_assigned = ROUND(nvl(l_mc_assigned2, 0), G_DECIMAL),
2963 labor_needed = ROUND(nvl(l_labor_needed2, 0), G_DECIMAL),
2964 labor_assigned = ROUND(nvl(l_lb_assigned2, 0), G_DECIMAL),
2965 takt_time = ROUND(nvl(l_takt2, 0), G_DECIMAL),
2966 takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned2, 0), G_DECIMAL),
2967 in_process_kanban = ROUND(nvl(l_ipk2, 0), G_DECIMAL),
2968 machines_needed_delta = ROUND(l_mn_delta2, G_DECIMAL),
2969 labor_needed_delta = ROUND(l_ln_delta2, G_DECIMAL),
2970 in_process_kanban_delta = ROUND(l_ipk_delta2, G_DECIMAL)
2971 WHERE mixed_model_map_id = p_mmm_id
2972 AND group_number = p_group_number
2973 AND sequence_id = 2 ;
2974
2975 l_stmt_num := 80;
2976 UPDATE bom_mixed_model_map_processes
2977 SET machine_weighted_time = ROUND(nvl(l_machine_wt3, 0), G_DECIMAL),
2978 labor_weighted_time = ROUND(nvl(l_labor_wt3, 0), G_DECIMAL),
2979 total_weighted_time = ROUND(nvl(l_total_wt3, 0), G_DECIMAL),
2980 machines_needed = ROUND(nvl(l_machines_needed3, 0), G_DECIMAL),
2981 machines_assigned = ROUND(nvl(l_mc_assigned3, 0), G_DECIMAL),
2982 labor_needed = ROUND(nvl(l_labor_needed3, 0), G_DECIMAL),
2983 labor_assigned = ROUND(nvl(l_lb_assigned3, 0), G_DECIMAL),
2984 takt_time = ROUND(nvl(l_takt3, 0), G_DECIMAL),
2985 takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned3, 0), G_DECIMAL),
2986 in_process_kanban = ROUND(nvl(l_ipk3, 0), G_DECIMAL),
2987 machines_needed_delta = ROUND(l_mn_delta3, G_DECIMAL),
2988 labor_needed_delta = ROUND(l_ln_delta3, G_DECIMAL),
2989 in_process_kanban_delta = ROUND(l_ipk_delta3, G_DECIMAL)
2990 WHERE mixed_model_map_id = p_mmm_id
2991 AND group_number = p_group_number
2992 AND sequence_id = 3 ;
2993
2994 l_stmt_num := 81;
2995 UPDATE bom_mixed_model_map_processes
2996 SET machine_weighted_time = ROUND(nvl(l_machine_wt4, 0), G_DECIMAL),
2997 labor_weighted_time = ROUND(nvl(l_labor_wt4, 0), G_DECIMAL),
2998 total_weighted_time = ROUND(nvl(l_total_wt4, 0), G_DECIMAL),
2999 machines_needed = ROUND(nvl(l_machines_needed4, 0), G_DECIMAL),
3000 machines_assigned = ROUND(nvl(l_mc_assigned4, 0), G_DECIMAL),
3001 labor_needed = ROUND(nvl(l_labor_needed4, 0), G_DECIMAL),
3002 labor_assigned = ROUND(nvl(l_lb_assigned4, 0), G_DECIMAL),
3003 takt_time = ROUND(nvl(l_takt4, 0), G_DECIMAL),
3004 takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned4, 0), G_DECIMAL),
3005 in_process_kanban = ROUND(nvl(l_ipk4, 0), G_DECIMAL),
3006 machines_needed_delta = ROUND(l_mn_delta4, G_DECIMAL),
3007 labor_needed_delta = ROUND(l_ln_delta4, G_DECIMAL),
3008 in_process_kanban_delta = ROUND(l_ipk_delta4, G_DECIMAL)
3009 WHERE mixed_model_map_id = p_mmm_id
3010 AND group_number = p_group_number
3011 AND sequence_id = 4 ;
3012
3013 l_stmt_num := 82;
3014 UPDATE bom_mixed_model_map_processes
3015 SET machine_weighted_time = ROUND(nvl(l_machine_wt5, 0), G_DECIMAL),
3016 labor_weighted_time = ROUND(nvl(l_labor_wt5, 0), G_DECIMAL),
3017 total_weighted_time = ROUND(nvl(l_total_wt5, 0), G_DECIMAL),
3018 machines_needed = ROUND(nvl(l_machines_needed5, 0), G_DECIMAL),
3019 machines_assigned = ROUND(nvl(l_mc_assigned5, 0), G_DECIMAL),
3020 labor_needed = ROUND(nvl(l_labor_needed5, 0), G_DECIMAL),
3021 labor_assigned = ROUND(nvl(l_lb_assigned5, 0), G_DECIMAL),
3022 takt_time = ROUND(nvl(l_takt5, 0), G_DECIMAL),
3023 takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned5, 0), G_DECIMAL),
3024 in_process_kanban = ROUND(nvl(l_ipk5, 0), G_DECIMAL),
3025 machines_needed_delta = ROUND(l_mn_delta5, G_DECIMAL),
3026 labor_needed_delta = ROUND(l_ln_delta5, G_DECIMAL),
3027 in_process_kanban_delta = ROUND(l_ipk_delta5, G_DECIMAL)
3028 WHERE mixed_model_map_id = p_mmm_id
3029 AND group_number = p_group_number
3030 AND sequence_id = 5 ;
3031
3032 --above code changed slightly
3033 l_stmt_num := 83;
3034 COMMIT;
3035 EXCEPTION
3036 WHEN others THEN
3037 x_err_text := 'BOM_Mixed_Model_Map_PVT(GetCells-'||l_stmt_num||
3038 ') '||substrb(SQLERRM,1,500);
3039 END GetCells;
3040
3041
3042
3043
3044 END BOM_Mixed_Model_Map_PVT;