[Home] [Help]
PACKAGE BODY: APPS.MRP_SCHEDULE
Source
1 PACKAGE BODY MRP_SCHEDULE AS
2 /* $Header: MRSCHDBB.pls 120.1 2005/06/16 14:02:21 ichoudhu noship $ */
3
4 TYPE_DAILY_BUCKET CONSTANT NUMBER := 1;
5 TYPE_WEEKLY_BUCKET CONSTANT NUMBER := 2;
6 TYPE_MONTHLY_BUCKET CONSTANT NUMBER := 3;
7
8 SYS_YES CONSTANT NUMBER := 1;
9 TYPE_CURRENT_QTY CONSTANT NUMBER := 2;
10
11
12 Procedure BUCKET_ENTRIES( arg_query_id1 IN NUMBER,
13 arg_query_id2 IN NUMBER,
14 arg_org_id IN NUMBER,
15 arg_schedule_designator IN VARCHAR2,
16 arg_inventory_item_id IN NUMBER,
17 arg_bucket_type IN NUMBER,
18 arg_quantity_type IN NUMBER,
19 arg_version_type IN NUMBER,
20 arg_past_due IN NUMBER,
21 arg_start_date IN DATE,
22 arg_cutoff_date IN DATE ) IS
23
24 var_start_date DATE;
25 var_cutoff_date DATE;
26 var_prev_valid_date DATE;
27 var_last_cal_date DATE;
28 var_tmp_quantity NUMBER;
29 var_tmp_value NUMBER;
30 var_tmp_copied_sched NUMBER;
31 var_tmp_mps_plan NUMBER;
32 var_tmp_manual NUMBER;
33 var_tmp_forecast NUMBER;
34 var_tmp_sales_order NUMBER;
35 var_tmp_exploded NUMBER;
36 var_tmp_interorg NUMBER;
37
38 var_quantity NUMBER;
39 var_cum_quantity NUMBER := 0;
40 var_rowid ROWID;
41
42 CURSOR form_query IS
43 SELECT number1, rowid
44 FROM mrp_form_query
45 WHERE query_id = arg_query_id1
46 ORDER BY date1;
47
48 BEGIN
49 --
50 -- Get last calendar date
51 --
52 var_last_cal_date := mrp_calendar.next_work_day(arg_org_id,
53 TYPE_DAILY_BUCKET, arg_cutoff_date);
54
55 --
56 -- Populate MRP_FORM_QUERY
57 --
58 -- The view MRP_DAILY_SCHEDULES_V has already:
59 --
60 -- - bucketed the discrete schedule entries into the correct
61 -- workdate (previous workdate for invalid workdate)
62 --
63 -- - bucketed the repetitive schedule into daily schedule on
64 -- valid workdate
65 --
66 -- - selected only schedules of type DEMAND for MDS,
67 -- SUPPLY for MPS
68 --
69 IF (arg_bucket_type = TYPE_DAILY_BUCKET) THEN
70
71 var_start_date := mrp_calendar.prev_work_day(arg_org_id,
72 TYPE_DAILY_BUCKET, arg_start_date);
73
74 --
75 -- Insert schedules in daily bucket
76 --
77 INSERT INTO mrp_form_query
78 (QUERY_ID,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 DATE1, -- valid work date
84 DATE2, -- next valid work date
85 NUMBER1, -- daily quantity
86 NUMBER3, -- daily cum qty
87 NUMBER5, -- orig: copied schedule
88 NUMBER6, -- orig: MPS plan
89 NUMBER7, -- orig: manual entry
90 NUMBER8, -- orig: forecast
91 NUMBER9, -- orig: sales order
92 NUMBER11, -- orig: exploded
93 NUMBER12) -- orig: interorg order
94 SELECT
95 arg_query_id1,
96 sysdate,
97 -1,
98 sysdate,
99 -1,
100 dates.calendar_date,
101 dates.next_date,
102 -- daily quantity
103 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
104 sched.current_quantity,
105 sched.original_quantity)),
106 0),
107 -- cumulative quantity
108 0,
109 -- orig: copied schedule
110 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
111 sched.copied_sched_qty,
112 sched.original_copied_sched_qty)),
113 0),
114 -- orig: MPS plan
115 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
116 sched.mps_plan_qty,
117 sched.original_mps_plan_qty)),
118 0),
119 -- orig: manual entry
120 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
121 sched.manual_qty,
122 sched.original_manual_qty)),
123 0),
124 -- orig: forecast
125 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
126 sched.forecast_qty,
127 sched.original_forecast_qty)),
128 0),
129 -- orig: sales order
130 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
131 sched.sales_order_qty,
132 sched.original_sales_order_qty)),
133 0),
134 -- orig: exploded
135 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
136 sched.exploded_qty,
137 sched.original_exploded_qty)),
138 0),
139 -- orig: interorg order
140 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
141 sched.interorg_qty,
142 sched.original_interorg_qty)),
143 0)
144 FROM bom_calendar_dates dates,
145 mrp_daily_schedules_v sched,
146 mtl_parameters param
147 WHERE param.organization_id = arg_org_id
148 AND param.calendar_exception_set_id = dates.exception_set_id
149 AND param.calendar_code = dates.calendar_code
150 AND sched.organization_id (+)= arg_org_id
151 AND sched.schedule_designator (+)= arg_schedule_designator
152 AND sched.inventory_item_id (+)= arg_inventory_item_id
153 AND sched.schedule_level (+)= arg_version_type
154 AND sched.bucket_date (+)= dates.calendar_date
155 AND sched.schedule_date (+)>= arg_start_date
156 AND dates.calendar_date BETWEEN var_start_date
157 AND arg_cutoff_date
158 AND dates.seq_num is not NULL
159 GROUP BY arg_query_id1, dates.calendar_date, dates.next_date;
160
161
162 ELSIF (arg_bucket_type = TYPE_WEEKLY_BUCKET) THEN
163
164 var_start_date := mrp_calendar.prev_work_day(arg_org_id,
165 TYPE_WEEKLY_BUCKET, arg_start_date);
166
167 --
168 -- Insert schedules in weekly buckets for those weeks that have
169 -- entries
170 --
171 INSERT INTO mrp_form_query
172 (QUERY_ID,
173 LAST_UPDATE_DATE,
174 LAST_UPDATED_BY,
175 CREATION_DATE,
176 CREATED_BY,
177 DATE1, -- week start date
178 DATE2, -- next week start date
179 NUMBER1, -- weekly quantity
180 NUMBER3, -- weekly cum qty
181 NUMBER5, -- orig: copied schedule
182 NUMBER6, -- orig: MPS plan
183 NUMBER7, -- orig: manual entry
184 NUMBER8, -- orig: forecast
185 NUMBER9, -- orig: sales order
186 NUMBER11, -- orig: exploded
187 NUMBER12) -- orig: interorg order
188 SELECT
189 arg_query_id2,
190 sysdate,
191 -1,
192 sysdate,
193 -1,
194 dates.week_start_date,
195 dates.next_date,
196 -- weekly quantity
197 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
198 sched.current_quantity,
199 sched.original_quantity)),
200 0),
201 -- cumulative quantity
202 0,
203 -- orig: copied schedule
204 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
205 sched.copied_sched_qty,
206 sched.original_copied_sched_qty)),
207 0),
208 -- orig: MPS plan
209 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
210 sched.mps_plan_qty,
211 sched.original_mps_plan_qty)),
212 0),
213 -- orig: manual entry
214 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
215 sched.manual_qty,
216 sched.original_manual_qty)),
217 0),
218 -- orig: forecast
219 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
220 sched.forecast_qty,
221 sched.original_forecast_qty)),
222 0),
223 -- orig: sales order
224 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
225 sched.sales_order_qty,
226 sched.original_sales_order_qty)),
227 0),
228 -- orig: exploded
229 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
230 sched.exploded_qty,
231 sched.original_exploded_qty)),
232 0),
233 -- orig: interorg order
234 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
235 sched.interorg_qty,
236 sched.original_interorg_qty)),
237 0)
238 FROM bom_cal_week_start_dates dates,
239 mrp_daily_schedules_v sched,
240 mtl_parameters param
241 WHERE param.organization_id = arg_org_id
242 AND param.calendar_exception_set_id = dates.exception_set_id
243 AND param.calendar_code = dates.calendar_code
244 AND sched.organization_id = arg_org_id
245 AND sched.schedule_designator = arg_schedule_designator
246 AND sched.inventory_item_id = arg_inventory_item_id
247 AND sched.schedule_level = arg_version_type
248 AND sched.bucket_date >= dates.week_start_date
249 AND sched.bucket_date <
250 DECODE(dates.next_date, dates.week_start_date, var_last_cal_date,
251 dates.next_date)
252 AND sched.schedule_date BETWEEN arg_start_date
253 AND arg_cutoff_date
254 GROUP BY arg_query_id2, dates.week_start_date, dates.next_date;
255
256 --
257 -- Outer-joined with BOM_CAL_WEEK_START_DATES to get those
258 -- weeks that do not have entries
259 --
260 INSERT INTO mrp_form_query
261 (QUERY_ID,
262 LAST_UPDATE_DATE,
263 LAST_UPDATED_BY,
264 CREATION_DATE,
265 CREATED_BY,
266 DATE1, -- week start date
267 DATE2, -- next week start date
268 NUMBER1, -- weekly quantity
269 NUMBER3, -- weekly cum qty
270 NUMBER5, -- orig: copied schedule
271 NUMBER6, -- orig: MPS plan
272 NUMBER7, -- orig: manual entry
273 NUMBER8, -- orig: forecast
274 NUMBER9, -- orig: sales order
275 NUMBER11, -- orig: exploded
276 NUMBER12) -- orig: interorg order
277 SELECT
278 arg_query_id1,
279 sysdate,
280 -1,
281 sysdate,
282 -1,
283 dates.week_start_date,
284 dates.next_date,
285 NVL(SUM(query.number1), 0), -- weekly quantity
286 0, -- cumulative quantity
287 NVL(SUM(query.number5), 0), -- orig: copied schedule
288 NVL(SUM(query.number6), 0), -- orig: MPS plan
289 NVL(SUM(query.number7), 0), -- orig: manual entry
290 NVL(SUM(query.number8), 0), -- orig: forecast
291 NVL(SUM(query.number9), 0), -- orig: sales order
292 NVL(SUM(query.number11), 0), -- orig: exploded
293 NVL(SUM(query.number12), 0) -- orig: interorg order
294 FROM bom_cal_week_start_dates dates,
295 mrp_form_query query,
296 mtl_parameters param
297 WHERE param.organization_id = arg_org_id
298 AND param.calendar_exception_set_id = dates.exception_set_id
299 AND param.calendar_code = dates.calendar_code
300 AND query.query_id (+)= arg_query_id2
301 AND query.date1 (+)= dates.week_start_date
302 AND dates.week_start_date BETWEEN var_start_date
303 AND arg_cutoff_date
304 GROUP BY arg_query_id1, dates.week_start_date, dates.next_date;
305
306
307 ELSIF (arg_bucket_type = TYPE_MONTHLY_BUCKET) THEN
308
309 var_start_date := mrp_calendar.prev_work_day(arg_org_id,
310 TYPE_MONTHLY_BUCKET, arg_start_date);
311
312 --
313 -- Insert schedules in periodic buckets for those periods that have
314 -- entries
315 --
316 INSERT INTO mrp_form_query
317 (QUERY_ID,
318 LAST_UPDATE_DATE,
319 LAST_UPDATED_BY,
320 CREATION_DATE,
321 CREATED_BY,
322 DATE1, -- period start date
323 DATE2, -- next period start date
324 NUMBER1, -- period quantity
325 NUMBER3, -- period cum qty
326 NUMBER5, -- orig: copied schedule
327 NUMBER6, -- orig: MPS plan
328 NUMBER7, -- orig: manual entry
329 NUMBER8, -- orig: forecast
330 NUMBER9, -- orig: sales order
331 NUMBER11, -- orig: exploded
332 NUMBER12) -- orig: interorg order
333 SELECT
334 arg_query_id2,
335 sysdate,
336 -1,
337 sysdate,
338 -1,
339 dates.period_start_date,
340 dates.next_date,
341 -- period quantity
342 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
343 sched.current_quantity,
344 sched.original_quantity)),
345 0),
346 -- cumulative quantity
347 0,
348 -- orig: copied schedule
349 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
350 sched.copied_sched_qty,
351 sched.original_copied_sched_qty)),
352 0),
353 -- orig: MPS plan
354 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
355 sched.mps_plan_qty,
356 sched.original_mps_plan_qty)),
357 0),
358 -- orig: manual entry
359 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
360 sched.manual_qty,
361 sched.original_manual_qty)),
362 0),
363 -- orig: forecast
364 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
365 sched.forecast_qty,
366 sched.original_forecast_qty)),
367 0),
368 -- orig: sales order
369 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
370 sched.sales_order_qty,
371 sched.original_sales_order_qty)),
372 0),
373 -- orig: exploded
374 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
375 sched.exploded_qty,
376 sched.original_exploded_qty)),
377 0),
378 -- orig: interorg order
379 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
380 sched.interorg_qty,
381 sched.original_interorg_qty)),
382 0)
383 FROM bom_period_start_dates dates,
384 mrp_daily_schedules_v sched,
385 mtl_parameters param
386 WHERE param.organization_id = arg_org_id
387 AND param.calendar_exception_set_id = dates.exception_set_id
388 AND param.calendar_code = dates.calendar_code
389 AND sched.organization_id = arg_org_id
390 AND sched.schedule_designator = arg_schedule_designator
391 AND sched.inventory_item_id = arg_inventory_item_id
392 AND sched.schedule_level = arg_version_type
393 AND sched.bucket_date >= dates.period_start_date
394 AND sched.bucket_date <
395 DECODE(dates.next_date, dates.period_start_date,
396 var_last_cal_date, dates.next_date)
397 AND sched.schedule_date BETWEEN arg_start_date
398 AND arg_cutoff_date
399 GROUP BY arg_query_id2, dates.period_start_date, dates.next_date;
400
401 --
402 -- Outer-joined with BOM_PERIOD_START_DATES to get those
403 -- periods that do not have entries
404 --
405 INSERT INTO mrp_form_query
406 (QUERY_ID,
407 LAST_UPDATE_DATE,
408 LAST_UPDATED_BY,
409 CREATION_DATE,
410 CREATED_BY,
411 DATE1, -- period start date
412 DATE2, -- next period start date
413 NUMBER1, -- period quantity
414 NUMBER3, -- period cum qty
415 NUMBER5, -- orig: copied schedule
416 NUMBER6, -- orig: MPS plan
417 NUMBER7, -- orig: manual entry
418 NUMBER8, -- orig: forecast
419 NUMBER9, -- orig: sales order
420 NUMBER11, -- orig: exploded
421 NUMBER12) -- orig: interorg order
422 SELECT
423 arg_query_id1,
424 sysdate,
425 -1,
426 sysdate,
427 -1,
428 dates.period_start_date,
429 dates.next_date,
430 NVL(SUM(query.number1), 0), -- period quantity
431 0, -- cumulative quantity
432 NVL(SUM(query.number5), 0), -- orig: copied schedule
433 NVL(SUM(query.number6), 0), -- orig: MPS plan
434 NVL(SUM(query.number7), 0), -- orig: manual entry
435 NVL(SUM(query.number8), 0), -- orig: forecast
436 NVL(SUM(query.number9), 0), -- orig: sales order
437 NVL(SUM(query.number11), 0), -- orig: exploded
438 NVL(SUM(query.number12), 0) -- orig: interorg order
439 FROM bom_period_start_dates dates,
440 mrp_form_query query,
441 mtl_parameters param
442 WHERE param.organization_id = arg_org_id
443 AND param.calendar_exception_set_id = dates.exception_set_id
444 AND param.calendar_code = dates.calendar_code
445 AND query.query_id (+)= arg_query_id2
446 AND query.date1 (+)= dates.period_start_date
447 AND dates.period_start_date BETWEEN var_start_date
448 AND arg_cutoff_date
449 GROUP BY arg_query_id1, dates.period_start_date, dates.next_date;
450
451 END IF;
452
453 IF (arg_past_due = SYS_YES) THEN
454
455 --
456 -- Sum up Past Due quantities
457 --
458 SELECT NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
459 sched.current_quantity,
460 sched.original_quantity)),
461 0),
462 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
463 sched.copied_sched_qty,
464 sched.original_copied_sched_qty)),
465 0),
466 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
467 sched.mps_plan_qty,
468 sched.original_mps_plan_qty)),
469 0),
470 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
471 sched.manual_qty,
472 sched.original_manual_qty)),
473 0),
474 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
475 sched.forecast_qty,
476 sched.original_forecast_qty)),
477 0),
478 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
479 sched.sales_order_qty,
480 sched.original_sales_order_qty)),
481 0),
482 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
483 sched.exploded_qty,
484 sched.original_exploded_qty)),
485 0),
486 NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
487 sched.interorg_qty,
488 sched.original_interorg_qty)),
489 0)
490 INTO var_tmp_quantity,
491 var_tmp_copied_sched,
492 var_tmp_mps_plan,
493 var_tmp_manual,
494 var_tmp_forecast,
495 var_tmp_sales_order,
496 var_tmp_exploded,
497 var_tmp_interorg
498 FROM mrp_daily_schedules_v sched
499 WHERE sched.organization_id = arg_org_id
500 AND sched.schedule_designator = arg_schedule_designator
501 AND sched.inventory_item_id = arg_inventory_item_id
502 AND sched.schedule_level = arg_version_type
503 AND sched.bucket_date < arg_start_date;
504
505 --
506 -- Add the past due quantities to the start date bucket
507 --
508 UPDATE MRP_FORM_QUERY
509 SET NUMBER1 = NUMBER1 + var_tmp_quantity,
510 NUMBER5 = NUMBER5 + var_tmp_copied_sched,
511 NUMBER6 = NUMBER6 + var_tmp_mps_plan,
512 NUMBER7 = NUMBER7 + var_tmp_manual,
513 NUMBER8 = NUMBER8 + var_tmp_forecast,
514 NUMBER9 = NUMBER9 + var_tmp_sales_order,
515 NUMBER11 = NUMBER11 + var_tmp_exploded,
516 NUMBER12 = NUMBER12 + var_tmp_interorg
517 WHERE QUERY_ID = arg_query_id1
518 AND DATE1 = var_start_date;
519
520 END IF;
521
522 --
523 -- Calculate cumulative quantities
524 --
525 OPEN form_query;
526
527 LOOP
528 FETCH form_query INTO var_quantity, var_rowid;
529
530 EXIT WHEN form_query%NOTFOUND;
531
532 var_cum_quantity := var_cum_quantity + var_quantity;
533
534 UPDATE mrp_form_query
535 SET number3 = var_cum_quantity
536 WHERE rowid = var_rowid;
537 END LOOP;
538
539 --COMMIT WORK;
540
541 END BUCKET_ENTRIES;
542
543
544
545 PROCEDURE Get_Nextval( X_query_id1 IN OUT NOCOPY NUMBER,
546 X_query_id2 IN OUT NOCOPY NUMBER ) IS
547 BEGIN
548
549 SELECT MRP_FORM_QUERY_S.NEXTVAL
550 INTO X_query_id1
551 FROM dual;
552
553 SELECT MRP_FORM_QUERY_S.NEXTVAL
554 INTO X_query_id2
555 FROM dual;
556
557 END Get_Nextval;
558
559
560
561 PROCEDURE Get_Cost( X_org_id IN NUMBER,
562 X_inventory_item_id IN NUMBER,
563 X_cost IN OUT NOCOPY NUMBER ) IS
564 BEGIN
565
566 SELECT NVL(item_cost,0)
567 INTO X_cost
568 FROM cst_item_costs_for_gl_view
569 WHERE organization_id = X_org_id
570 AND inventory_item_id = X_inventory_item_id;
571
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 X_cost := 0;
575
576 END Get_Cost;
577
578
579 PROCEDURE Get_Max_BOM_Level(X_organization_id NUMBER,
580 X_mps_explosion_level IN OUT NOCOPY NUMBER) IS
581
582 CURSOR C IS
583 SELECT NVL(maximum_bom_level, 20)
584 FROM BOM_PARAMETERS
585 WHERE organization_id = X_organization_id;
586
587 BEGIN
588
589 OPEN C;
590
591 FETCH C INTO X_mps_explosion_level;
592
593 if (C%NOTFOUND) then
594 CLOSE C;
595 X_mps_explosion_level := 20;
596 end if;
597
598 CLOSE C;
599
600 END Get_Max_BOM_Level;
601
602
603 END MRP_SCHEDULE;