[Home] [Help]
PACKAGE BODY: APPS.MRP_GRAPH_LINE_CAPACITY
Source
1 PACKAGE BODY MRP_GRAPH_LINE_CAPACITY AS
2 /* $Header: MRPGLCPB.pls 115.20 2003/09/15 17:42:44 hwenas ship $ */
3
4 PROCEDURE LOAD_CAPACITY_RECORDS(p_plan_id IN NUMBER,
5 p_line_id IN NUMBER,
6 p_org_id IN NUMBER,
7 p_start_date IN DATE)
8 IS
9
10 CURSOR DATE_CURSOR(p_org_id NUMBER,
11 p_line_id NUMBER,
12 p_start_date DATE)
13 IS
14 SELECT distinct cal.calendar_date
15 FROM mtl_parameters mp,
16 bom_calendar_dates cal
17 WHERE mp.organization_id = p_org_id
18 AND cal.calendar_date >= p_start_date
19 AND cal.seq_num IS NOT NULL
20 AND mp.calendar_exception_set_id = cal.exception_set_id
21 AND mp.calendar_code = cal.calendar_code
22 UNION ALL
23 SELECT distinct cal.calendar_date
24 FROM mtl_parameters mp,
25 mrp_line_sch_avail_v ls,
26 bom_calendar_dates cal
27 WHERE mp.organization_id = p_org_id
28 AND ls.line_id = p_line_id
29 AND cal.calendar_date >= p_start_date
30 AND ((ls.planned_quantity - nvl(ls.quantity_completed,0)) > 0 and
31 cal.seq_num IS NULL)
32 AND cal.calendar_date = ls.scheduled_completion_date
33 AND ls.organization_id = p_org_id
34 AND mp.calendar_exception_set_id = cal.exception_set_id
35 AND mp.calendar_code = cal.calendar_code;
36
37
38 CURSOR MRP_AVAIL_CAPACITY_CURSOR(p_org_id NUMBER,
39 p_dept_id NUMBER,
40 p_res_id NUMBER,
41 p_query_id NUMBER)
42 IS
43 SELECT bdr.department_id,
44 bdr.resource_id,
45 -1, -- line_id
46 mfq.number1,
47 cal.calendar_date,
48 decode(cal.seq_num, NULL, 0,
49 nvl(sum(decode(bdr.available_24_hours_flag,1,24,
50 ((decode(least(shifts.to_time,shifts.from_time),
51 shifts.to_time,shifts.to_time + 24*3600,
52 shifts.to_time) - shifts.from_time)/3600))),0))
53 FROM bom_calendar_dates cal,
54 bom_department_resources bdr,
55 bom_resource_shifts brs,
56 bom_shift_times shifts,
57 mtl_parameters mp,
58 mrp_form_query mfq
59 WHERE bdr.department_id = p_dept_id
60 AND bdr.resource_id = p_res_id
61 AND bdr.department_id = brs.department_id(+)
62 AND bdr.resource_id = brs.resource_id(+)
63 AND brs.shift_num = shifts.shift_num(+)
64 AND (mp.calendar_code = shifts.calendar_code
65 OR shifts.calendar_code IS NULL)
66 AND cal.seq_num IS NOT NULL
67 AND cal.exception_set_id = mp.calendar_exception_set_id
68 AND cal.calendar_code = mp.calendar_code
69 AND mp.organization_id = p_org_id
70 AND trunc(cal.calendar_date) = mfq.date1
71 AND mfq.query_id = p_query_id
72 GROUP BY bdr.department_id,
73 bdr.resource_id,
74 mfq.number1,
75 cal.calendar_date,
76 cal.seq_num;
77
78 CURSOR MRP_CAPACITY_CURSOR(p_line_id NUMBER,
79 p_org_id NUMBER,
80 p_query_id NUMBER,
81 p_uom_class VARCHAR2)
82 IS
83 SELECT bos.department_id,
84 br.resource_id,
85 -1, -- line_id
86 mfq.number1,
87 cal_start.calendar_date,
88 sum(br.usage_rate_or_amount * (nvl(bos1.net_planning_percent, 100)/100) /
89 nvl(bos1.reverse_cumulative_yield, 1) * /*Fix for bug 2000775*/
90 decode(br.basis_type,1,ls.planned_quantity -
91 nvl(ls.quantity_completed,0) , 2, 1))
92 FROM bom_calendar_dates cal_start,
93 bom_calendar_dates cal_order,
94 mtl_system_items items,
95 mtl_parameters mp,
96 mrp_form_query mfq,
97 bom_operation_resources br,
98 bom_operation_sequences bos,
99 bom_operation_sequences bos1,
100 bom_resources bre,
101 mtl_units_of_measure muom,
102 bom_operational_routings bor,
103 mrp_line_sch_avail_v ls
104 WHERE ls.line_id = p_line_id
105 AND ls.organization_id = p_org_id
106 AND nvl(ls.alternate_routing_designator,'@!#')
107 = nvl(bor.alternate_routing_designator,'@!#')
108 AND bor.line_id = ls.line_id
109 AND bor.assembly_item_id = ls.primary_item_id
110 AND bor.organization_id = ls.organization_id
111 AND bos.routing_sequence_id = bor.routing_sequence_id
112 AND bos.operation_type = 1
113 AND bos1.operation_sequence_id = bos.line_op_seq_id
114 AND br.operation_sequence_id = bos.operation_sequence_id
115 -- AND br.schedule_flag <> 2
116 AND br.resource_id = bre.resource_id
117 AND bre.unit_of_measure = muom.uom_code
118 AND muom.uom_class = p_uom_class
119 AND cal_start.exception_set_id = cal_order.exception_set_id
120 AND cal_start.calendar_code = cal_order.calendar_code
121 AND cal_start.seq_num = (cal_order.prior_seq_num -
122 CEIL((1 - NVL(br.resource_offset_percent,0)) *
123 (NVL(items.fixed_lead_time,0) +
124 (NVL(items.variable_lead_time,0) *
125 NVL(ls.planned_quantity,0)))))
126 AND cal_order.exception_set_id = mp.calendar_exception_set_id
127 AND cal_order.calendar_code = mp.calendar_code
128 AND cal_order.calendar_date = trunc(ls.scheduled_completion_date)
129 AND mp.organization_id = ls.organization_id
130 AND items.organization_id = ls.organization_id
131 AND items.inventory_item_id = ls.primary_item_id
132 AND trunc(ls.scheduled_completion_date) = mfq.date1
133 AND mfq.query_id = p_query_id
134 GROUP BY bos.department_id,
135 br.resource_id,
136 mfq.number1,
137 cal_start.calendar_date
138 UNION
139 SELECT -1, -- department_id
140 -1, -- resource_id
141 ls.line_id,
142 mfq.number1,
143 mfq.date1,
144 sum(ls.planned_quantity - nvl(ls.quantity_completed,0))
145 FROM mrp_form_query mfq,
146 mrp_line_sch_avail_v ls
147 WHERE ls.organization_id = p_org_id
148 AND ls.line_id = p_line_id
149 AND trunc(ls.scheduled_completion_date) = mfq.date1
150 AND mfq.query_id = p_query_id
151 GROUP BY -1,-1,
152 ls.line_id,
153 mfq.number1,
154 mfq.date1;
155
156 -- Fix bug 939061, add 24 hrs to stop_time if stop_time <= start_time
157 CURSOR LINE_CURSOR IS
158 SELECT NVL(maximum_rate * ((decode(least(stop_time,start_time),stop_time,stop_time+24*3600,stop_time)-start_time)/3600),0)
159 FROM wip_lines
160 WHERE line_id = p_line_id
161 AND organization_id = p_org_id;
162
163 l_last_dept_id NUMBER := -5;
164 l_last_res_id NUMBER := -5;
165 l_last_line_id NUMBER := -5;
166 l_max_rate NUMBER := 0;
167 l_bucket_number NUMBER := 0;
168 l_bucket_date DATE;
169 l_query_id NUMBER;
170 temp_uom VARCHAR2(3);/* Fix for bug 2000775*/
171 temp_conv_rate NUMBER := 1;
172 temp_base_uom VARCHAR2(3);
173 capacity_rec MRP_CAPACITY;
174 capacity_rec2 MRP_CAPACITY;
175 qty_cells_tab BUCKET_NUMBER; -- Holds the quantities for each bucket
176 date_cells_tab BUCKET_DATE; -- Holds the dates for each bucket
177
178 l_uom_code VARCHAR2(3);
179 l_uom_class VARCHAR2(10);
180
181 PROCEDURE flush_dept_res_rec(p_dept_id IN NUMBER,
182 p_res_id IN NUMBER,
183 p_local_line_id IN NUMBER,
184 p_supply_demand IN NUMBER) IS
185
186 BEGIN
187
188 INSERT INTO MRP_MATERIAL_PLANS (
189 plan_id, -- unique identifier
190 plan_organization_id, -- line_id
191 inventory_item_id, -- resource_id
192 organization_id, -- department_id
193 item_segments, -- description
194 horizontal_plan_type, -- type
195 last_update_date,
196 last_updated_by,
197 creation_date,
198 created_by,
199 quantity1, quantity2, quantity3, quantity4, quantity5, quantity6,
200 quantity7, quantity8, quantity9, quantity10, quantity11, quantity12,
201 quantity13, quantity14, quantity15, quantity16, quantity17, quantity18,
202 quantity19, quantity20, quantity21, quantity22, quantity23, quantity24,
203 quantity25, quantity26, quantity27, quantity28, quantity29, quantity30,
204 quantity31, quantity32, quantity33, quantity34, quantity35, quantity36,
205 compile_designator, -- not used
206 bucket_type -- not used
207 ) values (
208 p_plan_id,
209 p_local_line_id,
210 p_res_id,
211 p_dept_id,
212 ' ',
213 p_supply_demand,
214 sysdate,
215 -1,
216 sysdate,
217 -1,
218 qty_cells_tab(1), qty_cells_tab(2), qty_cells_tab(3),
219 qty_cells_tab(4), qty_cells_tab(5), qty_cells_tab(6),
220 qty_cells_tab(7), qty_cells_tab(8), qty_cells_tab(9),
221 qty_cells_tab(10), qty_cells_tab(11), qty_cells_tab(12),
222 qty_cells_tab(13), qty_cells_tab(14), qty_cells_tab(15),
223 qty_cells_tab(16), qty_cells_tab(17), qty_cells_tab(18),
224 qty_cells_tab(19), qty_cells_tab(20), qty_cells_tab(21),
225 qty_cells_tab(22), qty_cells_tab(23), qty_cells_tab(24),
226 qty_cells_tab(25), qty_cells_tab(26), qty_cells_tab(27),
227 qty_cells_tab(28), qty_cells_tab(29), qty_cells_tab(30),
228 qty_cells_tab(31), qty_cells_tab(32), qty_cells_tab(33),
229 qty_cells_tab(34), qty_cells_tab(35), qty_cells_tab(36),
230 ' ',
231 0
232 );
233
234 END flush_dept_res_rec;
235
236 PROCEDURE flush_date_rec IS
237 BEGIN
238
239 INSERT INTO MRP_WORKBENCH_BUCKET_DATES(
240 organization_id, -- plan_id
241 compile_designator, -- not used
242 bucket_type, -- not used
243 last_update_date, last_updated_by, creation_date, created_by,
244 date1, date2, date3, date4, date5, date6, date7, date8, date9,
245 date10, date11, date12, date13, date14, date15, date16, date17, date18,
246 date19, date20, date21, date22, date23, date24, date25, date26, date27,
247 date28, date29, date30, date31, date32, date33, date34, date35, date36
248 ) VALUES (
249 p_plan_id,
250 ' ',
251 1,
252 sysdate, -1, sysdate, -1,
253 date_cells_tab(1), date_cells_tab(2), date_cells_tab(3),
254 date_cells_tab(4), date_cells_tab(5), date_cells_tab(6),
255 date_cells_tab(7), date_cells_tab(8), date_cells_tab(9),
256 date_cells_tab(10), date_cells_tab(11), date_cells_tab(12),
257 date_cells_tab(13), date_cells_tab(14), date_cells_tab(15),
258 date_cells_tab(16), date_cells_tab(17), date_cells_tab(18),
259 date_cells_tab(19), date_cells_tab(20), date_cells_tab(21),
260 date_cells_tab(22), date_cells_tab(23), date_cells_tab(24),
261 date_cells_tab(25), date_cells_tab(26), date_cells_tab(27),
262 date_cells_tab(28), date_cells_tab(29), date_cells_tab(30),
263 date_cells_tab(31), date_cells_tab(32), date_cells_tab(33),
264 date_cells_tab(34), date_cells_tab(35), date_cells_tab(36)
265 );
266
267 END flush_date_rec;
268
269 PROCEDURE initialize_counter IS
270 BEGIN
271
272 -- ------------------------------------------
273 -- Initialize bucket cells to 0
274 -- ------------------------------------------
275 FOR counter IN 1..NUM_OF_COLUMNS LOOP
276 qty_cells_tab(counter) := 0;
277 END LOOP;
278
279 END initialize_counter;
280
281 PROCEDURE initialize_dates IS
282 BEGIN
283
284 -- ------------------------------------------
285 -- Initialize bucket cells to NULL
286 -- ------------------------------------------
287 FOR counter IN 1..NUM_OF_COLUMNS LOOP
288 date_cells_tab(counter) := NULL;
289 END LOOP;
290
291 END initialize_dates;
292
293 PROCEDURE get_resource_avail(l_org_id NUMBER,
294 l_dept_id NUMBER,
295 l_res_id NUMBER,
296 l_query_id NUMBER) IS
297 temp_cap_units number ;/*fix for bug 2000775*/
298 BEGIN
299
300 OPEN MRP_AVAIL_CAPACITY_CURSOR(l_org_id, l_dept_id, l_res_id, l_query_id);
301
302 LOOP
303 FETCH MRP_AVAIL_CAPACITY_CURSOR INTO capacity_rec2;
304 EXIT WHEN MRP_AVAIL_CAPACITY_CURSOR%ROWCOUNT = 0;
305
306 IF mrp_avail_capacity_cursor%NOTFOUND
307 THEN
308
309 -- ------------------------------------------
310 -- Flush the record for the previous dept/res
311 -- ------------------------------------------
312 flush_dept_res_rec(l_dept_id,
313 l_res_id,
314 -1,
315 1);
316
317 END IF;
318
319 -- --------------------------------------------
320 -- Set the value for the bucket
321 -- --------------------------------------------
322 /* Fix for bug 2000775*/
323 select capacity_units
324 into temp_cap_units
325 from bom_department_resources
326 where department_id = l_dept_id
327 and resource_id = l_res_id;
328
329 qty_cells_tab(capacity_rec2.bucket_number) := capacity_rec2.bucket_qty *
330 temp_cap_units;
331
332 EXIT WHEN MRP_AVAIL_CAPACITY_CURSOR%NOTFOUND;
333
334 END LOOP;
335
336 CLOSE MRP_AVAIL_CAPACITY_CURSOR;
337
338 END get_resource_avail;
339
340 BEGIN
341
342 -- Load and flush the dates
343 initialize_dates;
344
345 -- Get the query_id
346 SELECT mrp_form_query_s.nextval
347 INTO l_query_id
348 FROM dual;
349
350 -- We want to show all valid working days and non-working days
351 -- with quantities. Use the cursor to get the correct dates
352 -- and load them into the array for updating.
353 OPEN DATE_CURSOR(p_org_id, p_line_id, p_start_date);
354
355 LOOP
356 FETCH DATE_CURSOR INTO l_bucket_date;
357 EXIT WHEN DATE_CURSOR%NOTFOUND;
358
359 l_bucket_number := l_bucket_number + 1;
360
361 -- Load the dates into mrp_workbench_bucket_dates so that the
362 -- form can select them into the flat file.
363 date_cells_tab(l_bucket_number) := l_bucket_date;
364
365 -- Insert the dates into mrp_form_query so that we can join
366 -- to it in MRP_CAPACITY_CURSOR to get the appropriate bucket
367 -- numbers.
368 INSERT INTO MRP_FORM_QUERY(QUERY_ID, LAST_UPDATE_DATE,
369 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, DATE1,
370 NUMBER1)
371 VALUES(l_query_id, sysdate, -1, sysdate, -1,
372 l_bucket_date, l_bucket_number);
373
374 EXIT WHEN l_bucket_number = 36;
375 END LOOP;
376
377 CLOSE DATE_CURSOR;
378
379 flush_date_rec;
380
381 -- Load line availability
382
383 initialize_counter;
384
385 OPEN LINE_CURSOR;
386
387 FETCH LINE_CURSOR INTO l_max_rate;
388
389 -- Set each bucket to the max rate
390 FOR counter IN 1..NUM_OF_COLUMNS LOOP
391 qty_cells_tab(counter) := l_max_rate;
392 END LOOP;
393
394 CLOSE LINE_CURSOR;
395
396 -- Flush the record
397 l_last_line_id := p_line_id;
398
399 flush_dept_res_rec(-1,
400 -1,
401 l_last_line_id,
402 1);
403
404 -- Reinitialize variables and load line and dept/res demand
405 l_last_line_id := -5;
406 l_last_dept_id := -5;
407 l_last_res_id := -5;
408 initialize_counter;
409
410 l_uom_code := FND_PROFILE.value('BOM:HOUR_UOM_CODE');
411 select uom_class
412 into l_uom_class
413 from mtl_units_of_measure
414 where uom_code = l_uom_code;
415 OPEN MRP_CAPACITY_CURSOR(p_line_id, p_org_id, l_query_id, l_uom_class);
416
417 LOOP
418 FETCH MRP_CAPACITY_CURSOR
419 INTO capacity_rec;
420 IF (MRP_CAPACITY_CURSOR%ROWCOUNT = 0 and l_last_line_id = -5) THEN
421 -- Enter a line record with all zeros
422 flush_dept_res_rec(-1,
423 -1,
424 p_line_id,
425 2);
426 END IF;
427 EXIT WHEN MRP_CAPACITY_CURSOR%ROWCOUNT = 0;
428
429 IF ((mrp_capacity_cursor%NOTFOUND) OR
430 (capacity_rec.department_id <> l_last_dept_id) OR
431 (capacity_rec.resource_id <> l_last_res_id) OR
432 (capacity_rec.line_id <> l_last_line_id)) AND
433 l_last_dept_id <> -5 AND l_last_res_id <> -5 AND
434 l_last_line_id <> -5
435 THEN
436
437 -- ------------------------------------------
438 -- Flush the record for the previous dept/res
439 -- ------------------------------------------
440 flush_dept_res_rec(l_last_dept_id,
441 l_last_res_id,
442 l_last_line_id,
443 2);
444
445 initialize_counter;
446
447 -- --------------------------------------------
448 -- Create availability record for this dept/res
449 -- --------------------------------------------
450 get_resource_avail(p_org_id, l_last_dept_id,
451 l_last_res_id, l_query_id);
452
453 initialize_counter;
454 END IF;
455
456 -- --------------------------------------------
457 -- Set the value for the bucket
458 -- --------------------------------------------
459 /*fix for bug 2000775*/
460 l_last_res_id := capacity_rec.resource_id;
461 IF (l_last_res_id <> -1 ) Then
462 select unit_of_measure into temp_uom from bom_resources
463 where resource_id = l_last_res_id;
464 FND_PROFILE.get('BOM:HOUR_UOM_CODE',temp_base_uom);
465 inv_convert.inv_um_conversion(temp_uom,temp_base_uom,0,temp_conv_rate);
466 END IF;
467 qty_cells_tab(capacity_rec.bucket_number) := capacity_rec.bucket_qty * temp_conv_rate ;
468 l_last_dept_id := capacity_rec.department_id;
469 l_last_line_id := capacity_rec.line_id;
470
471 EXIT WHEN MRP_CAPACITY_CURSOR%NOTFOUND;
472
473 END LOOP;
474
475 CLOSE MRP_CAPACITY_CURSOR;
476
477 END LOAD_CAPACITY_RECORDS;
478
479 END MRP_GRAPH_LINE_CAPACITY;