[Home] [Help]
PACKAGE BODY: APPS.MRP_FORECAST
Source
1 PACKAGE BODY MRP_FORECAST AS
2 /* $Header: MRFCSTBB.pls 115.6 2004/02/19 07:39:51 rgurugub ship $ */
3 TYPE_DAILY_BUCKET CONSTANT NUMBER := 1;
4 TYPE_WEEKLY_BUCKET CONSTANT NUMBER := 2;
5 TYPE_MONTHLY_BUCKET CONSTANT NUMBER := 3;
6
7 SYS_YES CONSTANT NUMBER := 1;
8
9 ENTRY_MODE CONSTANT NUMBER := 1;
10 QUERY_MODE CONSTANT NUMBER := 2;
11
12 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
13 FUNCTION BUCKET_FC_DESIG( arg_org_id IN NUMBER,
14 arg_query_id IN NUMBER,
15 arg_secondary_query_id IN NUMBER,
16 arg_bucket_type IN NUMBER,
17 arg_past_due IN NUMBER,
18 arg_forecast_designator IN VARCHAR2,
19 arg_inventory_item_id IN NUMBER,
20 arg_start_date IN DATE,
21 arg_cutoff_date IN DATE,
22 arg_bucket_start_date IN DATE,
23 arg_form_mode IN NUMBER) RETURN BOOLEAN IS
24 var_calendar_code VARCHAR2(10);
25 var_forecast_desig VARCHAR2(10);
26 var_exception_set_id NUMBER;
27 var_curr_qty NUMBER;
28 var_orig_qty NUMBER;
29 var_rep_curr_qty NUMBER;
30 var_rep_orig_qty NUMBER;
31 var_bucket_type NUMBER;
32 var_org_id NUMBER;
33 var_days_in_bucket NUMBER;
34 var_begin_date DATE;
35 var_fc_begin_date DATE;
36 var_start_date DATE;
37 var_end_date DATE;
38 var_rate_end_date DATE;
39 var_bucket_start_date DATE;
40 var_bucket_end_date DATE;
41 var_temp_date DATE;
42 var_curr_date DATE;
43 var_next_date DATE;
44 var_item_cost NUMBER;
45 var_first_bucket_workday DATE;
46
47 BEGIN
48 mrp_calendar.select_calendar_defaults(arg_org_id,
49 var_calendar_code, var_exception_set_id);
50
51 BEGIN
52 SELECT NVL(item_cost,0)
53 INTO var_item_cost
54 FROM cst_item_costs_for_gl_view
55 WHERE organization_id = arg_org_id
56 AND inventory_item_id = arg_inventory_item_id;
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 var_item_cost := 0;
61 END;
62
63 SELECT cal1.calendar_date
64 INTO var_fc_begin_date
65 FROM bom_calendar_dates cal1,
66 bom_calendar_dates cal2,
67 mrp_forecast_designators desig
68 WHERE cal1.seq_num =
69 cal2.prior_seq_num -
70 LEAST(NVL(desig.backward_update_time_fence, 0),
71 cal2.seq_num - 1)
72 AND cal1.calendar_code = var_calendar_code
73 AND cal1.exception_set_id = var_exception_set_id
74 AND cal2.calendar_date =
75 TRUNC(arg_start_date)
76 AND cal2.calendar_code = var_calendar_code
77 AND cal2.exception_set_id = var_exception_set_id
78 AND desig.forecast_designator = arg_forecast_designator
79 AND desig.organization_id = arg_org_id;
80
81 DECLARE
82 CURSOR FORECAST_RECORDS IS
83 SELECT DECODE(arg_past_due, SYS_YES,
84 forecast_date,
85 GREATEST(arg_start_date,forecast_date)),
86 NVL(rate_end_date, forecast_date),
87 current_forecast_quantity,
88 original_forecast_quantity,
89 forecast_designator,
90 NVL(bucket_type, 0),
91 arg_org_id
92 FROM mrp_forecast_dates
93 WHERE DECODE(arg_past_due, SYS_YES, arg_start_date,
94 DECODE(bucket_type, 1,
95 DECODE(rate_end_date, NULL,
96 forecast_date, rate_end_date), arg_start_date))
97 >= arg_start_date
98 AND forecast_designator = arg_forecast_designator
99 AND organization_id = arg_org_id
100 AND inventory_item_id = arg_inventory_item_id
101 ORDER BY 1,2,4,5;
102 BEGIN
103 OPEN FORECAST_RECORDS;
104
105 <<get_item_loop>>
106 LOOP
107 FETCH FORECAST_RECORDS
108 INTO var_start_date,
109 var_rate_end_date,
110 var_curr_qty,
111 var_orig_qty,
112 var_forecast_desig,
113 var_bucket_type,
114 var_org_id;
115
116 IF FORECAST_RECORDS%FOUND THEN -- fetch succeed
117
118 var_end_date := mrp_calendar.date_offset(arg_org_id,
119 var_bucket_type , var_rate_end_date, 1);
120
121 var_bucket_start_date := mrp_calendar.prev_work_day(arg_org_id,
122 var_bucket_type, var_start_date);
123
124 var_begin_date := mrp_calendar.prev_work_day(arg_org_id,
125 var_bucket_type, var_fc_begin_date);
126
127 IF arg_past_due <> SYS_YES AND var_end_date <= var_begin_date THEN
128 GOTO get_item_loop;
129 END IF;
130
131 /*--------------------------------------------------------------+
132 | Bug 631859 bbaumbac 25-FEB-1998 |
133 | Problem: If a monthly calendar is used then there is a |
134 | possibility that the first day of the bucket will |
135 | not be a workday. In this case, a quantity is |
136 | seen for that day because we were starting with |
137 | the first day of the forecast. |
138 | Fix: Added a variable, var_first_bucket_workday, and a call |
139 | to mrp_calendar.next_work_day. If the first day of the|
140 | forecast is a workday, it will just return that day, |
141 | otherwise, the next valid workday is returned. |
142 +--------------------------------------------------------------*/
143 var_first_bucket_workday := mrp_calendar.next_work_day(arg_org_id,
144 1,var_start_date);
145 var_temp_date := var_first_bucket_workday;
146
147 <<fc_loop>>
148 WHILE(var_temp_date < var_end_date) LOOP
149 var_bucket_end_date := mrp_calendar.date_offset(arg_org_id,
150 var_bucket_type, var_temp_date, 1);
151 var_bucket_start_date := mrp_calendar.next_work_day(arg_org_id,
152 1,var_bucket_start_date);
153 var_bucket_end_date := mrp_calendar.next_work_day(arg_org_id,
154 1,var_bucket_end_date);
155
156 IF (var_bucket_type = TYPE_DAILY_BUCKET) THEN
157 var_days_in_bucket := 1;
158 ELSE
159 var_days_in_bucket := mrp_calendar.days_between(arg_org_id,
160 TYPE_DAILY_BUCKET, var_bucket_start_date,
161 var_bucket_end_date);
162 END IF;
163
164 var_rep_curr_qty := ROUND(var_curr_qty/var_days_in_bucket,10);
165 var_rep_orig_qty := ROUND(var_orig_qty/var_days_in_bucket,10);
166
167 IF (arg_past_due <> SYS_YES AND
168 var_bucket_end_date <= var_begin_date) THEN
169 var_temp_date := var_bucket_end_date;
170 var_bucket_start_date := var_temp_date;
171 GOTO fc_loop;
172 END IF;
173
174 IF (var_temp_date <= var_begin_date AND
175 var_begin_date < var_bucket_end_date) THEN
176 var_curr_date := var_begin_date;
177 ELSE
178 var_curr_date := var_temp_date;
179 END IF;
180
181 LOOP
182
183 INSERT INTO mrp_form_query
184 (QUERY_ID,
185 LAST_UPDATE_DATE,
186 LAST_UPDATED_BY,
187 CREATION_DATE,
188 CREATED_BY,
189 DATE1, /* forecast date */
190 NUMBER1, /* org id */
191 NUMBER2, /* inventory item id */
192 NUMBER3, /* daily curr qty */
193 NUMBER5, /* daily orig qty */
194 NUMBER7, /* daily sales order qty */
195 CHAR1) /* forecast designator (added for web inquiries) */
196 VALUES
197 (arg_query_id,
198 sysdate,
199 -1,
200 sysdate,
201 -1,
202 var_curr_date,
203 arg_org_id,
204 arg_inventory_item_id,
205 decode(arg_form_mode,QUERY_MODE,decode(sign(var_rep_curr_qty),-1,0,
206 var_rep_curr_qty),var_rep_curr_qty),
207 var_rep_orig_qty,
208 var_rep_orig_qty - var_rep_curr_qty,
209 arg_forecast_designator); /* (added for web inquiries) */
210
211 var_next_date := mrp_calendar.next_work_day(arg_org_id,
212 1, var_curr_date+1);
213 var_curr_date := var_next_date;
214 EXIT WHEN var_curr_date >= var_bucket_end_date;
215 END LOOP;
216
217 var_temp_date := var_bucket_end_date;
218 var_bucket_start_date := var_temp_date;
219 --COMMIT WORK;
220
221 END LOOP fc_loop;
222 ELSE /* no entires were found, insert a psuedo one */
223 INSERT INTO mrp_form_query
224 (QUERY_ID,
225 LAST_UPDATE_DATE,
226 LAST_UPDATED_BY,
227 CREATION_DATE,
228 CREATED_BY,
229 DATE1, /* forecast date */
230 NUMBER1, /* org id */
231 NUMBER2, /* inventory item id */
232 NUMBER3, /* daily curr qty */
233 NUMBER5, /* daily orig qty */
234 NUMBER7, /* daily sales order qty */
235 CHAR1) /* forecast designator (added for web inquiries) */
236 VALUES
237 (arg_query_id,
238 sysdate,
239 -1,
240 sysdate,
241 -1,
242 arg_start_date,
243 arg_org_id,
244 arg_inventory_item_id,
245 0,
246 0,
247 0,
248 arg_forecast_designator); /* (added for web inquiries) */
249 --COMMIT WORK;
250 EXIT;
251 END IF;
252 END LOOP get_item_loop;
253 CLOSE FORECAST_RECORDS;
254 END;
255 RETURN TRUE;
256 /*
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 return FALSE;
260 WHEN OTHERS THEN
261 dbms_output.put_line(sqlerrm);
262 */
263 END BUCKET_FC_DESIG;
264
265 FUNCTION FC_MRP_FORM_QUERY( arg_org_id IN NUMBER,
266 arg_query_id IN NUMBER,
267 arg_secondary_query_id IN NUMBER,
268 arg_bucket_type IN NUMBER,
269 arg_past_due IN NUMBER,
270 arg_cutoff_date IN DATE,
271 arg_bucket_start_date IN DATE) RETURN BOOLEAN IS
272 BEGIN
273 IF arg_bucket_type = TYPE_DAILY_BUCKET THEN
274 INSERT INTO mrp_form_query
275 (QUERY_ID,
276 LAST_UPDATE_DATE,
277 LAST_UPDATED_BY,
278 CREATION_DATE,
279 CREATED_BY,
280 DATE1, /* bucket start date */
281 DATE2, /* next bucket start date */
282 NUMBER1, /* bucket curr qty */
283 NUMBER3, /* bucket cum curr qty */
284 NUMBER5, /* bucket orig qty */
285 NUMBER7, /* bucket cum orig qty */
286 NUMBER9, /* bucket sales order qty */
287 NUMBER11, /* bucket cum sales order qty */
288 NUMBER12, /* item id (added for web inquiries) */
289 NUMBER13, /* org id (added for web inquiries) */
290 CHAR1) /* forecast designator (added for web inquiries) */
291 SELECT arg_secondary_query_id,
292 sysdate,
293 -1,
294 sysdate,
295 -1,
296 DATES.CALENDAR_DATE,
297 DATES.NEXT_DATE,
298 /* curr qty */
299 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
300 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
301 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
302 MRP_FQ.DATE1, MRP_FQ.NUMBER3, 0),
303 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
304 MRP_FQ.NUMBER3,0))),
305 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
306 MRP_FQ.NUMBER3,0))),0), 6),
307 /*cum curr qty */
308 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
309 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
310 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
311 MRP_FQ.DATE1, MRP_FQ.NUMBER3, 0),
312 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
313 MRP_FQ.DATE1,
314 MRP_FQ.NUMBER3,0))),
315 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
316 MRP_FQ.DATE1,
317 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
318 MRP_FQ.DATE1,
319 MRP_FQ.NUMBER3,0)))),0), 6),
320 /*orig qty */
321 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
322 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
323 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
324 MRP_FQ.DATE1, MRP_FQ.NUMBER5, 0),
325 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
326 MRP_FQ.NUMBER5,0))),
327 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
328 MRP_FQ.NUMBER5,0))),0), 6),
329 /*cum orig qty*/
330 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
331 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
332 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
333 MRP_FQ.DATE1, MRP_FQ.NUMBER5, 0),
334 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
335 MRP_FQ.DATE1,
336 MRP_FQ.NUMBER5,0))),
337 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
338 MRP_FQ.DATE1,
339 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
340 MRP_FQ.DATE1,
341 MRP_FQ.NUMBER5,0)))),0), 6),
342 /* sales order qty */
343 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
344 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
345 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
346 MRP_FQ.DATE1, MRP_FQ.NUMBER7, 0),
347 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
348 MRP_FQ.NUMBER7,0))),
349 DECODE(MRP_FQ.DATE1, DATES.CALENDAR_DATE,
350 MRP_FQ.NUMBER7,0))),0), 6),
351 /*cum sales order qty*/
352 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
353 (DECODE(DATES.CALENDAR_DATE, arg_bucket_start_date,
354 DECODE(LEAST(arg_bucket_start_date, MRP_FQ.DATE1),
355 MRP_FQ.DATE1, MRP_FQ.NUMBER7, 0),
356 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
357 MRP_FQ.DATE1,
358 MRP_FQ.NUMBER7,0))),
359 DECODE(LEAST(MRP_FQ.DATE1, DATES.CALENDAR_DATE),
360 MRP_FQ.DATE1,
361 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
362 MRP_FQ.DATE1,
363 MRP_FQ.NUMBER7,0)))),0), 6),
364 MRP_FQ.NUMBER2, /* (added for web inquiries) */
365 MRP_FQ.NUMBER1, /* (added for web inquiries) */
366 MRP_FQ.CHAR1 /* (added for web inquiries) */
367 FROM BOM_CALENDAR_DATES DATES,
368 MTL_PARAMETERS PARAM,
369 MRP_FORM_QUERY MRP_FQ
370 WHERE DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
371 AND DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE
372 AND DATES.SEQ_NUM is not NULL
373 AND DATES.CALENDAR_DATE BETWEEN arg_bucket_start_date
374 AND arg_cutoff_date
375 AND PARAM.ORGANIZATION_ID = arg_org_id
376 AND MRP_FQ.query_id = arg_query_id
377 AND MRP_FQ.DATE1 <= arg_cutoff_date
378 GROUP BY MRP_FQ.QUERY_ID,
379 MRP_FQ.NUMBER2, /* (added for web inquiries) */
380 MRP_FQ.NUMBER1, /* (added for web inquiries) */
381 MRP_FQ.CHAR1, /* (added for web inquiries) */
382 DATES.CALENDAR_DATE,
383 DATES.NEXT_DATE;
384
385 ELSIF arg_bucket_type = TYPE_WEEKLY_BUCKET THEN
386 INSERT INTO mrp_form_query
387 (QUERY_ID,
388 LAST_UPDATE_DATE,
389 LAST_UPDATED_BY,
390 CREATION_DATE,
391 CREATED_BY,
392 DATE1, /* bucket date */
393 DATE2, /* next bucket start date */
394 NUMBER1, /* bucket curr qty */
395 NUMBER3, /* bucket cum curr qty */
396 NUMBER5, /* bucket orig qty */
397 NUMBER7, /* bucket cum orig qty */
398 NUMBER9, /* bucket sales order qty */
399 NUMBER11, /* bucket cum sales order qty */
400 NUMBER12, /* item id (added for web inquiries) */
401 NUMBER13, /* org id (added for web inquiries) */
402 CHAR1) /* forecast designator (added for web inquiries) */
403 SELECT
404 arg_secondary_query_id,
405 sysdate,
406 -1,
407 sysdate,
408 -1,
409 DATES.WEEK_START_DATE,
410 DATES.NEXT_DATE,
411 /*curr qty*/
412 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
413 DECODE(DATES.WEEK_START_DATE, arg_bucket_start_date,
414 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
415 MRP_FQ.DATE1,
416 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
417 MRP_FQ.NUMBER3),0),
418 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
419 MRP_FQ.DATE1,
420 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
421 MRP_FQ.DATE1,
422 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
423 MRP_FQ.NUMBER3),0))),
424 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
425 MRP_FQ.DATE1,
426 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
427 MRP_FQ.DATE1,
428 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
429 MRP_FQ.NUMBER3),0)))),0), 6),
430 /*cum curr qty*/
431 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
432 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
433 MRP_FQ.DATE1,
434 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
435 MRP_FQ.NUMBER3),0),
436 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
437 MRP_FQ.DATE1,
438 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
439 MRP_FQ.DATE1,
440 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
441 MRP_FQ.NUMBER3),0)))),0), 6),
442 /* orig qty */
443 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
444 DECODE(DATES.WEEK_START_DATE, arg_bucket_start_date,
445 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
446 MRP_FQ.DATE1,
447 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
448 MRP_FQ.NUMBER5),0),
449 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
450 MRP_FQ.DATE1,
451 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
452 MRP_FQ.DATE1,
453 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
454 MRP_FQ.NUMBER5),0))),
455 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
456 MRP_FQ.DATE1,
457 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
458 MRP_FQ.DATE1,
459 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
460 MRP_FQ.NUMBER5),0)))),0), 6),
461 /* cum orig qty */
462 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
463 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
464 MRP_FQ.DATE1,
465 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
466 MRP_FQ.NUMBER5),0),
467 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
468 MRP_FQ.DATE1,
469 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
470 MRP_FQ.DATE1,
471 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
472 MRP_FQ.NUMBER5),0)))),0), 6),
473 /* sales order qty */
474 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
475 DECODE(DATES.WEEK_START_DATE, arg_bucket_start_date,
476 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
477 MRP_FQ.DATE1,
478 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
479 MRP_FQ.NUMBER7),0),
480 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
481 MRP_FQ.DATE1,
482 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
483 MRP_FQ.DATE1,
484 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
485 MRP_FQ.NUMBER7),0))),
486 DECODE(GREATEST(MRP_FQ.DATE1, DATES.WEEK_START_DATE),
487 MRP_FQ.DATE1,
488 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
489 MRP_FQ.DATE1,
490 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
491 MRP_FQ.NUMBER7),0)))),0), 6),
492 /* cum sales order qty */
493 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
494 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
495 MRP_FQ.DATE1,
496 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
497 MRP_FQ.NUMBER7),0),
498 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
499 MRP_FQ.DATE1,
500 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
501 MRP_FQ.DATE1,
502 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
503 MRP_FQ.NUMBER7),0)))),0), 6),
504 MRP_FQ.NUMBER2, /* (added for web inquiries) */
505 MRP_FQ.NUMBER1, /* (added for web inquiries) */
506 MRP_FQ.CHAR1 /* (added for web inquiries) */
507 FROM BOM_CAL_WEEK_START_DATES DATES,
508 MTL_PARAMETERS PARAM,
509 MRP_FORM_QUERY MRP_FQ
510 WHERE DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
511 AND DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE
512 AND DATES.WEEK_START_DATE BETWEEN arg_bucket_start_date
513 AND arg_cutoff_date
514 AND PARAM.ORGANIZATION_ID = arg_org_id
515 AND MRP_FQ.query_id = arg_query_id
516 AND MRP_FQ.DATE1 <= arg_cutoff_date
517 GROUP BY MRP_FQ.QUERY_ID,
518 MRP_FQ.NUMBER2, /* (added for web inquiries) */
519 MRP_FQ.NUMBER1, /* (added for web inquiries) */
520 MRP_FQ.CHAR1, /* (added for web inquiries) */
521 DATES.WEEK_START_DATE,
522 DATES.NEXT_DATE;
523 ELSE
524 INSERT INTO mrp_form_query
525 (QUERY_ID,
526 LAST_UPDATE_DATE,
527 LAST_UPDATED_BY,
528 CREATION_DATE,
529 CREATED_BY,
530 DATE1, /* bucket date */
531 DATE2, /* next bucket start date */
532 NUMBER1, /* bucket curr qty */
533 NUMBER3, /* bucket cum curr qty */
534 NUMBER5, /* bucket orig qty */
535 NUMBER7, /* bucket cum orig qty */
536 NUMBER9, /* bucket sales order qty */
537 NUMBER11, /* bucket cum sales order qty */
538 NUMBER12, /* item id (added for web inquiries) */
539 NUMBER13, /* org id (added for web inquiries) */
540 CHAR1) /* forecast designator (added for web inquiries) */
541 SELECT
542 arg_secondary_query_id,
543 sysdate,
544 -1,
545 sysdate,
546 -1,
547 DATES.PERIOD_START_DATE,
548 DATES.NEXT_DATE,
549 /*curr qty*/
550 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
551 DECODE(DATES.PERIOD_START_DATE, arg_bucket_start_date,
552 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
553 MRP_FQ.DATE1,
554 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
555 MRP_FQ.NUMBER3),0),
556 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
557 MRP_FQ.DATE1,
558 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
559 MRP_FQ.DATE1,
560 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
561 MRP_FQ.NUMBER3),0))),
562 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
563 MRP_FQ.DATE1,
564 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
565 MRP_FQ.DATE1,
566 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
567 MRP_FQ.NUMBER3),0)))),0), 6),
568 /*cum curr qty*/
569 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
570 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
571 MRP_FQ.DATE1,
572 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
573 MRP_FQ.NUMBER3),0),
574 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
575 MRP_FQ.DATE1,
576 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
577 MRP_FQ.DATE1,
578 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
579 MRP_FQ.NUMBER3),0)))),0), 6),
580 /* orig qty */
581 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
582 DECODE(DATES.PERIOD_START_DATE, arg_bucket_start_date,
583 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
584 MRP_FQ.DATE1,
585 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
586 MRP_FQ.NUMBER5),0),
587 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
588 MRP_FQ.DATE1,
589 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
590 MRP_FQ.DATE1,
591 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
592 MRP_FQ.NUMBER5),0))),
593 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
594 MRP_FQ.DATE1,
595 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
596 MRP_FQ.DATE1,
597 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
598 MRP_FQ.NUMBER5),0)))),0), 6),
599 /* cum orig qty */
600 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
601 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
602 MRP_FQ.DATE1,
603 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
604 MRP_FQ.NUMBER5),0),
605 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
606 MRP_FQ.DATE1,
607 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
608 MRP_FQ.DATE1,
609 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
610 MRP_FQ.NUMBER5),0)))),0), 6),
611 /* sales order qty */
612 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
613 DECODE(DATES.PERIOD_START_DATE, arg_bucket_start_date,
614 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
615 MRP_FQ.DATE1,
616 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
617 MRP_FQ.NUMBER7),0),
618 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
619 MRP_FQ.DATE1,
620 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
621 MRP_FQ.DATE1,
622 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
623 MRP_FQ.NUMBER7),0))),
624 DECODE(GREATEST(MRP_FQ.DATE1, DATES.PERIOD_START_DATE),
625 MRP_FQ.DATE1,
626 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
627 MRP_FQ.DATE1,
628 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
629 MRP_FQ.NUMBER7),0)))),0), 6),
630 /* cum sales order qty */
631 ROUND(NVL(SUM(DECODE(arg_past_due, 1,
632 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
633 MRP_FQ.DATE1,
634 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
635 MRP_FQ.NUMBER7),0),
636 DECODE(LEAST(MRP_FQ.DATE1, DATES.NEXT_DATE),
637 MRP_FQ.DATE1,
638 DECODE(GREATEST(MRP_FQ.DATE1, arg_bucket_start_date),
639 MRP_FQ.DATE1,
640 DECODE(MRP_FQ.DATE1, DATES.NEXT_DATE, 0,
641 MRP_FQ.NUMBER7),0)))),0), 6),
642 MRP_FQ.NUMBER2, /* (added for web inquiries) */
643 MRP_FQ.NUMBER1, /* (added for web inquiries) */
644 MRP_FQ.CHAR1 /* (added for web inquiries) */
645 FROM MTL_PARAMETERS PARAM,
646 BOM_PERIOD_START_DATES DATES,
647 MRP_FORM_QUERY MRP_FQ
648 WHERE DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID
649 AND DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE
650 AND PARAM.ORGANIZATION_ID = arg_org_id
651 AND MRP_FQ.query_id = arg_query_id
652 AND MRP_FQ.DATE1 <= arg_cutoff_date
653 AND DATES.PERIOD_START_DATE BETWEEN arg_bucket_start_date
654 AND arg_cutoff_date
655 GROUP BY MRP_FQ.QUERY_ID,
656 MRP_FQ.NUMBER2, /* (added for web inquiries) */
657 MRP_FQ.NUMBER1, /* (added for web inquiries) */
658 MRP_FQ.CHAR1, /* (added for web inquiries) */
659 DATES.PERIOD_START_DATE,
660 DATES.NEXT_DATE;
661 END IF;
662 --COMMIT WORK;
663 return TRUE;
664 /*
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 return FALSE;
668 WHEN OTHERS THEN
669 dbms_output.put_line(sqlerrm);
670 */
671 END FC_MRP_FORM_QUERY;
672
673 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
674 PROCEDURE BUCKET_ENTRIES( arg_form_mode IN NUMBER,
675 arg_org_id IN NUMBER,
676 arg_query_id IN NUMBER,
677 arg_secondary_query_id IN NUMBER,
678 arg_bucket_type IN NUMBER,
679 arg_past_due IN NUMBER,
680 arg_forecast_designator IN VARCHAR2,
681 arg_forecast_set IN VARCHAR2,
682 arg_inventory_item_id IN NUMBER,
683 arg_start_date IN DATE,
684 arg_cutoff_date IN DATE) IS
685 var_return_value BOOLEAN := TRUE;
686 var_forecast_designator VARCHAR(10);
687 var_start_date DATE;
688 var_bucket_start_date DATE;
689 BEGIN
690 var_start_date := mrp_calendar.prev_work_day(arg_org_id,
691 1, arg_start_date);
692
693 var_bucket_start_date := mrp_calendar.prev_work_day(arg_org_id,
694 arg_bucket_type, arg_start_date);
695
696 -- entry mode, show entries associate with the specific forecast
697 -- or forecast set.
698
699 IF arg_form_mode = ENTRY_MODE THEN
700 IF (arg_forecast_designator is not NULL
701 AND arg_forecast_designator <> ' ') THEN
702 var_forecast_designator := arg_forecast_designator;
703 ELSE
704 var_forecast_designator := arg_forecast_set;
705 END IF;
706
707 var_return_value := bucket_fc_desig(
708 arg_org_id,
709 arg_query_id,
710 arg_secondary_query_id,
711 arg_bucket_type,
712 arg_past_due,
713 var_forecast_designator,
714 arg_inventory_item_id,
715 var_start_date,
716 arg_cutoff_date,
717 var_bucket_start_date,
718 arg_form_mode);
719 /*
720 if var_return_value = FALSE THEN
721 APP_EXCEPTION.RAISE_EXCEPTION;
722 -- raise exception later
723 -- need to modify the following later for message dictionary
724 END IF;
725 */
726 ELSIF arg_form_mode = QUERY_MODE THEN
727 IF (arg_forecast_designator is not NULL
728 AND arg_forecast_designator <> ' ') THEN
729 var_return_value := bucket_fc_desig(
730 arg_org_id,
731 arg_query_id,
732 arg_secondary_query_id,
733 arg_bucket_type,
734 arg_past_due,
735 arg_forecast_designator,
736 arg_inventory_item_id,
737 var_start_date,
738 arg_cutoff_date,
739 var_bucket_start_date,
740 arg_form_mode);
741 /*
742 if var_return_value = FALSE THEN
743 APP_EXCEPTION.RAISE_EXCEPTION;
744 -- raise exception later
745 -- need to modify the following later for message dictionary
746 END IF;
747 */
748 ELSE
749 DECLARE
750 CURSOR FORECAST_DESIGS IS
751 SELECT forecast_designator
752 FROM mrp_forecast_designators
753 WHERE organization_id = arg_org_id
754 AND (forecast_set = arg_forecast_set
755 OR forecast_designator = arg_forecast_set);
756 BEGIN
757 FOR FORECAST_DESIGS_REC in FORECAST_DESIGS LOOP
758 var_forecast_designator := FORECAST_DESIGS_REC.forecast_designator;
759 var_return_value := bucket_fc_desig(
760 arg_org_id,
761 arg_query_id,
762 arg_secondary_query_id,
763 arg_bucket_type,
764 arg_past_due,
765 var_forecast_designator,
766 arg_inventory_item_id,
767 var_start_date,
768 arg_cutoff_date,
769 var_bucket_start_date,
770 arg_form_mode);
771 /*
772 if var_return_value = FALSE THEN
773 APP_EXCEPTION.RAISE_EXCEPTION;
774 -- need to modify the following later for message dictionary
775 END IF;
776 */
777 END LOOP;
778 END;
779 END IF;
780 END IF;
781
782 /*bucket the entries into mrp_form_query */
783 var_return_value := fc_mrp_form_query(
784 arg_org_id,
785 arg_query_id,
786 arg_secondary_query_id,
787 arg_bucket_type,
788 arg_past_due,
789 arg_cutoff_date,
790 var_bucket_start_date);
791 /*
792 EXCEPTION
793 WHEN NO_DATA_FOUND THEN
794 APP_EXCEPTION.RAISE_EXCEPTION;
795 WHEN OTHERS THEN
796 dbms_output.put_line(sqlerrm);
797 */
798 END BUCKET_ENTRIES;
799
800 END MRP_FORECAST;