[Home] [Help]
PACKAGE BODY: APPS.MRP_FORECAST_INTERFACE_PK
Source
1 PACKAGE BODY MRP_FORECAST_INTERFACE_PK AS
2 /* $Header: MRPFAPIB.pls 120.0.12000000.2 2007/04/11 09:09:58 gmalhotr ship $ */
3
4 mrdebug BOOLEAN := FALSE;
5 REJECT_INVALID_DATE CONSTANT INTEGER := 1;
6 IMPORTED_ITEM_FORECAST CONSTANT INTEGER := 3;
7 SHIFT_INVALID_DATE_FWD CONSTANT INTEGER := 2;
8 SHIFT_INVALID_DATE_BWD CONSTANT INTEGER := 3;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Forecast_Interface_PK';
11
12 FUNCTION adjust_date(
13 in_date IN DATE,
14 out_date IN OUT NOCOPY DATE,
15 bucket_type NUMBER,
16 workday_control NUMBER,
17 org_id NUMBER) RETURN BOOLEAN IS
18 BEGIN
19 IF workday_control = SHIFT_INVALID_DATE_FWD OR
20 workday_control = REJECT_INVALID_DATE THEN
21 out_date := mrp_calendar.next_work_day(org_id, bucket_type, in_date);
22 /* Bug 1849709 */
23 IF workday_control = REJECT_INVALID_DATE AND
24 to_char(out_date,'MM/DD/RRRR') <> to_char(in_date,'MM/DD/RRRR')
25 THEN
26 RETURN FALSE;
27 END IF;
28 ELSIF workday_control = SHIFT_INVALID_DATE_BWD
29 THEN
30 out_date := mrp_calendar.prev_work_day(org_id, bucket_type, in_date);
31 END IF;
32 RETURN TRUE;
33 END adjust_date;
34
35 PROCEDURE set_interface_error(
36 counter IN NUMBER ,
37 forecast_interface IN OUT NOCOPY t_forecast_interface,
38 error_message IN VARCHAR2 ) IS
39 BEGIN
40 forecast_interface(counter).process_status := 4;
41 forecast_interface(counter).error_message := error_message;
42 -- IF mrdebug = TRUE THEN
43 -- dbms_output.put_line('Error for row # '||to_char(counter));
44 -- dbms_output.put_line('Error message - '|| error_message);
45 -- END IF;
46 END;
47
48 FUNCTION create_for_entries(
49 forecast_interface IN OUT NOCOPY t_forecast_interface)
50 RETURN BOOLEAN IS
51 var_low_index NUMBER := 0;
52 var_high_index NUMBER := 0;
53 counter NUMBER := 0;
54 var_max_trx_id NUMBER;
55 dummy_var VARCHAR2(2);
56 var_for_date DATE;
57 var_for_end_date DATE;
58 var_request_id NUMBER;
59 var_user_id NUMBER;
60 error_message VARCHAR2(240);
61 delete_row BOOLEAN := FALSE;
62 p_result varchar2(1);
63 errcode VARCHAR2(240);
64 record_exists NUMBER;
65 BEGIN
66 SELECT mrp_forecast_dates_s.nextval
67 INTO var_max_trx_id
68 FROM dual;
69
70 var_low_index := forecast_interface.first;
71
72 IF var_low_index IS NULL THEN
73 RETURN TRUE;
74 END IF;
75 var_high_index := forecast_interface.last;
76 counter := var_low_index;
77 WHILE counter <= var_high_index
78 LOOP
79 delete_row := FALSE;
80 error_message := NULL;
81 IF forecast_interface(counter).process_status <> 2 THEN
82 goto skip_row;
83 END IF;
84 --dbms_output.put_line('Action '||forecast_interface(counter).action);
85 /* Begin change for Bug 1849709 */
86 IF forecast_interface(counter).action IS NOT NULL AND
87 forecast_interface(counter).action not in ('I','U','D') THEN
88 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
89 fnd_message.set_token('ENTITY', 'E_FORECAST_ACTION', TRUE);
90 fnd_message.set_token('VALUE',
91 forecast_interface(counter).action);
92 error_message := fnd_message.get;
93 set_interface_error(counter, forecast_interface, error_message);
94 -- IF mrdebug = TRUE THEN
95 -- dbms_output.put_line('Error for action - '||
96 -- forecast_interface(counter).action);
97 -- END IF;
98 goto skip_row;
99 END IF;
100
101 IF forecast_interface(counter).action IS NULL AND
102 forecast_interface(counter).quantity >= 0 THEN
103 IF forecast_interface(counter).transaction_id IS NOT NULL then
104 IF forecast_interface(counter).quantity = 0 THEN
105 forecast_interface(counter).action := 'D';
106 ELSE
107 forecast_interface(counter).action := 'U';
108 END IF;
109 ELSE
110 forecast_interface(counter).action := 'I';
111 END IF;
112 END IF;
113
114
115 IF forecast_interface(counter).action = 'D' AND
116 forecast_interface(counter).transaction_id IS NOT NULL THEN
117 delete_row := TRUE;
118 END IF;
119
120 /* Bug 1849709 */
121 IF forecast_interface(counter).quantity < 0
122 -- OR
123 -- forecast_interface(counter).quantity > 99999999.9 OR
124 -- forecast_interface(counter).quantity = 0
125 AND delete_row = FALSE
126 THEN
127 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
128 fnd_message.set_token('ENTITY', 'E_FORECAST_QTY', TRUE);
129 fnd_message.set_token('VALUE',
130 to_char(forecast_interface(counter).quantity));
131 error_message := fnd_message.get;
132 set_interface_error(counter, forecast_interface, error_message);
133 -- IF mrdebug = TRUE THEN
134 -- dbms_output.put_line('Error for Quantity - '||
135 -- to_char(forecast_interface(counter).quantity));
136 -- END IF;
137 goto skip_row;
138 END IF;
139
140 /* End of Change */
141 IF forecast_interface(counter).confidence_percentage IS NULL
142 THEN
143 forecast_interface(counter).confidence_percentage := 0.0;
144 END IF;
145
146 IF forecast_interface(counter).confidence_percentage <= 0 OR
147 forecast_interface(counter).confidence_percentage > 100
148 THEN
149 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
150 fnd_message.set_token('ENTITY', 'E_CONFIDENCE_PERCENT', TRUE);
151 fnd_message.set_token('VALUE',
152 to_char(forecast_interface(counter).confidence_percentage));
153 error_message := fnd_message.get;
154 set_interface_error(counter, forecast_interface, error_message);
155 -- IF mrdebug = TRUE THEN
156 -- dbms_output.put_line('Error for Confidence Pctg - '||
157 -- to_char(forecast_interface(counter).confidence_percentage));
158 -- END IF;
159 goto skip_row;
160 END IF;
161
162 IF forecast_interface(counter).workday_control is NULL
163 THEN
164 forecast_interface(counter).workday_control := REJECT_INVALID_DATE;
165 END IF;
166
167 IF forecast_interface(counter).workday_control <>
168 SHIFT_INVALID_DATE_FWD AND
169 forecast_interface(counter).workday_control <>
170 SHIFT_INVALID_DATE_BWD AND
171 forecast_interface(counter).workday_control <>
172 REJECT_INVALID_DATE
173 THEN
174 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
175 fnd_message.set_token('ENTITY', 'E_WORKDAY_CONTROL', TRUE);
176 fnd_message.set_token('VALUE',
177 to_char(forecast_interface(counter).workday_control), FALSE);
178 error_message := fnd_message.get;
179 set_interface_error(counter, forecast_interface, error_message);
180 -- IF mrdebug = TRUE THEN
181 -- dbms_output.put_line('Error for Workday control - '||
182 -- to_char(forecast_interface(counter).workday_control));
183 -- END IF;
184 goto skip_row;
185 END IF;
186
187 BEGIN
188 /* change for Bug 1849709 */
189 IF forecast_interface(counter).action = 'U' OR
190 forecast_interface(counter).action = 'D' THEN
191 IF forecast_interface(counter).transaction_id IS NOT NULL THEN
192 DELETE FROM mrp_Forecast_dates
193 WHERE transaction_id = forecast_interface(counter).transaction_id
194 and organization_id =
195 forecast_interface(counter).organization_id
196 and forecast_designator =
197 forecast_interface(counter).forecast_designator;
198 IF SQL%NOTFOUND THEN
199 IF forecast_interface(counter).transaction_id > var_max_trx_id
200 THEN
201 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
202 fnd_message.set_token('ENTITY',
203 'E_TRANSACTION_ID', TRUE);
204 fnd_message.set_token('VALUE',
205 to_char(forecast_interface(counter).transaction_id));
206 error_message := fnd_message.get;
207 set_interface_error(counter,
208 forecast_interface, error_message);
209 -- IF mrdebug = TRUE THEN
210 -- dbms_output.put_line('Error for Transaction Id - '||
211 -- to_char(forecast_interface(counter).transaction_id));
212 -- END IF;
213 goto skip_row;
214 ELSE
215 fnd_message.set_name('MRP', 'IMP-INVALID TRAN_ID');
216 error_message := fnd_message.get;
217 set_interface_error(counter,
218 forecast_interface, error_message);
219 -- IF mrdebug = TRUE THEN
220 -- dbms_output.put_line('Error for Transaction Id - '||
221 -- to_char(forecast_interface(counter).transaction_id));
222 -- END IF;
223 goto skip_row;
224 END IF;
225 END IF;
226 END IF;
227 END IF;
228 END;
229
230 BEGIN
231 SELECT 'x'
232 INTO dummy_var
233 FROM mtl_parameters
234 WHERE organization_id =
235 forecast_interface(counter).organization_id
236 AND process_enabled_flag = 'N'; /* 1485309 */
237
238 EXCEPTION
239 WHEN no_data_found THEN
240 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
241 fnd_message.set_token('ENTITY', 'E_ORGANIZATION', TRUE);
242 fnd_message.set_token('VALUE',
243 to_char(forecast_interface(counter).organization_id));
244 error_message := fnd_message.get;
245 set_interface_error(counter, forecast_interface, error_message);
246 -- IF mrdebug = TRUE THEN
247 -- dbms_output.put_line('Error for Organization Id - '||
248 -- to_char(forecast_interface(counter).organization_id));
249 -- END IF;
250 goto skip_row;
251 END;
252
253 BEGIN
254 SELECT 'x'
255 INTO dummy_var
256 FROM mtl_system_items
257 WHERE organization_id =
258 forecast_interface(counter).organization_id
259 AND inventory_item_id =
260 forecast_interface(counter).inventory_item_id;
261
262 EXCEPTION
263 WHEN no_data_found THEN
264 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
265 fnd_message.set_token('ENTITY', 'E_INVENTORY_ITEM', TRUE);
266 fnd_message.set_token('VALUE',
267 to_char(forecast_interface(counter).inventory_item_id));
268 error_message := fnd_message.get;
269 set_interface_error(counter, forecast_interface, error_message);
270 -- IF mrdebug = TRUE THEN
271 -- dbms_output.put_line('Error for Item - '||
272 -- to_char(forecast_interface(counter).inventory_item_id));
273 -- END IF;
274 goto skip_row;
275 END;
276
277 BEGIN
278 SELECT 'x'
279 INTO dummy_var
280 FROM mrp_forecast_designators
281 WHERE forecast_designator =
282 forecast_interface(counter).forecast_designator
283 AND NVL(disable_date, SYSDATE + 2) > TRUNC(SYSDATE)
284 AND forecast_set IS NOT NULL
285 AND organization_id =
286 forecast_interface(counter).organization_id;
287
288 EXCEPTION
289 WHEN no_data_found THEN
290 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
291 fnd_message.set_token('ENTITY',
292 'E_FORECAST_NAME', TRUE);
293 fnd_message.set_token('VALUE',
294 forecast_interface(counter).forecast_designator);
295 error_message := fnd_message.get;
296 set_interface_error(counter, forecast_interface,
297 error_message);
298 -- IF mrdebug = TRUE THEN
299 -- dbms_output.put_line('Error for Forecast name - '||
300 -- forecast_interface(counter).forecast_designator);
301 -- END IF;
302 goto skip_row;
303 END;
304
305 IF forecast_interface(counter).bucket_type IS null
306 THEN
307 forecast_interface(counter).bucket_type := 1;
308 END IF;
309
310 IF forecast_interface(counter).bucket_type <> 1 AND
311 forecast_interface(counter).bucket_type <> 2 AND
312 forecast_interface(counter).bucket_type <> 3
313 THEN
314 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
315 fnd_message.set_token('ENTITY', 'EC_BUCKET_TYPE', TRUE);
316 fnd_message.set_token('VALUE',
317 to_char(forecast_interface(counter).bucket_type));
318 error_message := fnd_message.get;
319 set_interface_error(counter, forecast_interface, error_message);
320 -- IF mrdebug = TRUE THEN
321 -- dbms_output.put_line('Error for Bucket type - '||
322 -- to_char(forecast_interface(counter).bucket_type));
323 -- END IF;
324 goto skip_row;
325 END IF;
326
327 IF forecast_interface(counter).forecast_date is null
328 THEN
329 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
330 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
331 fnd_message.set_token('VALUE', 'NULL');
332 error_message := fnd_message.get;
333 set_interface_error(counter, forecast_interface, error_message);
334 -- IF mrdebug = TRUE THEN
335 -- dbms_output.put_line('Error for Forecast date - NULL');
336 -- END IF;
337 goto skip_row;
338 END IF;
339
340 IF adjust_date(forecast_interface(counter).forecast_date,
341 var_for_date,
342 forecast_interface(counter).bucket_type,
343 forecast_interface(counter).workday_control,
344 forecast_interface(counter).organization_id) = FALSE
345 THEN
346 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
347 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
348 fnd_message.set_token('VALUE',
349 to_char(forecast_interface(counter).forecast_date));
350 error_message := fnd_message.get;
351 set_interface_error(counter, forecast_interface, error_message);
352 -- IF mrdebug = TRUE THEN
353 -- dbms_output.put_line('Error for Forecast date - '||
354 -- to_char(forecast_interface(counter).forecast_date));
355 -- END IF;
356 goto skip_row;
357 END IF;
358
359 IF forecast_interface(counter).forecast_end_date IS NOT NULL THEN
360 IF adjust_date(forecast_interface(counter).forecast_end_date,
361 var_for_end_date,
362 forecast_interface(counter).bucket_type,
363 forecast_interface(counter).workday_control,
364 forecast_interface(counter).organization_id) = FALSE
365 THEN
366 fnd_message.set_name('MRP', 'IMP-invalid rate_end_date');
367 error_message := fnd_message.get;
368 set_interface_error(counter, forecast_interface, error_message);
369 -- IF mrdebug = TRUE THEN
370 -- dbms_output.put_line('Error for Forecast end date - '||
371 -- to_char(forecast_interface(counter).forecast_end_date));
372 -- END IF;
373 goto skip_row;
374 END IF;
375 IF var_for_end_date < var_for_date THEN
376 fnd_message.set_name('MRP', 'IMP-invalid rate_end_date');
377 error_message := fnd_message.get;
378 set_interface_error(counter, forecast_interface, error_message);
379 -- IF mrdebug = TRUE THEN
380 -- dbms_output.put_line('Error for Forecast end date - '||
381 -- to_char(forecast_interface(counter).forecast_end_date));
382 -- END IF;
383 goto skip_row;
384 END IF;
385 ELSE
386 var_for_end_date := NULL ;
387 END IF;
388
389 IF (forecast_interface(counter).line_id IS NOT NULL) THEN
390 BEGIN
391 SELECT 'x'
392 INTO dummy_var
393 FROM wip_lines
394 WHERE organization_id =
395 forecast_interface(counter).organization_id
396 AND line_id = forecast_interface(counter).line_id;
397
398 EXCEPTION
399 WHEN no_data_found THEN
400 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
401 fnd_message.set_token('ENTITY', 'E_LINE', TRUE);
402 fnd_message.set_token('VALUE',
403 to_char(forecast_interface(counter).line_id));
404 error_message := fnd_message.get;
405 set_interface_error(counter, forecast_interface, error_message);
406 -- IF mrdebug = TRUE THEN
407 -- dbms_output.put_line('Error for Line - '||
408 -- to_char(forecast_interface(counter).line_id));
409 -- END IF;
410 goto skip_row;
411 END;
412 END IF; /* if f_i().line_id is not null */
413
414 p_result := 'S';
415 IF forecast_interface(counter).project_id IS NOT NULL THEN
416 p_result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
417 ( X_inventory_org_id => forecast_interface(counter).organization_id
418 , X_project_id => forecast_interface(counter).project_id
419 , X_task_id => forecast_interface(counter).task_id
420 , X_date1 => var_for_date
421 , X_date2 => var_for_end_date
422 , X_calling_function => 'MRPFAPIB'
423 , X_error_code => errcode
424 );
425 END IF;
426
427 IF (p_result = 'E') THEN
428 error_message := SUBSTRB(fnd_message.get,1,240);
429 set_interface_error(counter, forecast_interface, error_message);
430 goto skip_row;
431 END IF;
432
433
434 /* Change for Bug 1849709 */
435 IF (forecast_interface(counter).action in ('I','U') AND
436 delete_row = FALSE )
437 THEN
438 -- dbms_output.put_line('Inserting row '||
439 -- forecast_interface(counter).forecast_designator);
440
441 /* 1336039 - SVAIDYAN: Insert attribute_category also. */
442
443 INSERT INTO mrp_forecast_dates
444 (
445 transaction_id,
446 last_update_date,
447 last_updated_by,
448 creation_date,
449 created_by,
450 last_update_login,
451 inventory_item_id,
452 organization_id,
453 forecast_designator,
454 forecast_date,
455 rate_end_date,
456 bucket_type,
457 original_forecast_quantity,
458 current_forecast_quantity,
459 comments,
460 confidence_percentage,
461 source_organization_id,
462 source_forecast_designator,
463 origination_type,
464 request_id,
465 source_code,
466 source_line_id,
467 attribute1,
468 attribute2,
469 attribute3,
470 attribute4,
471 attribute5,
472 attribute6,
473 attribute7,
474 attribute8,
475 attribute9,
476 attribute10,
477 attribute11,
478 attribute12,
479 attribute13,
480 attribute14,
481 attribute15,
482 old_transaction_id,
483 to_update,
484 project_id,
485 task_id,
486 line_id,
487 attribute_category)
488 VALUES
489 (
490 NVL(forecast_interface(counter).transaction_id,
491 mrp_forecast_dates_s.nextval),
492 SYSDATE,
493 NVL(forecast_interface(counter).last_updated_by, -1),
494 SYSDATE,
495 NVL(forecast_interface(counter).created_by, -1),
496 NVL(forecast_interface(counter).last_update_login, -1),
497 forecast_interface(counter).inventory_item_id,
498 forecast_interface(counter).organization_id,
499 forecast_interface(counter).forecast_designator,
500 var_for_date,
501 var_for_end_date,
502 forecast_interface(counter).bucket_type,
503 ROUND(forecast_interface(counter).quantity, 6),
504 ROUND(forecast_interface(counter).quantity, 6),
505 forecast_interface(counter).comments,
506 forecast_interface(counter).confidence_percentage,
507 NULL,
508 NULL,
509 IMPORTED_ITEM_FORECAST,
510 forecast_interface(counter).request_id,
511 forecast_interface(counter).source_code,
512 forecast_interface(counter).source_line_id,
513 forecast_interface(counter).attribute1,
514 forecast_interface(counter).attribute2,
515 forecast_interface(counter).attribute3,
516 forecast_interface(counter).attribute4,
517 forecast_interface(counter).attribute5,
518 forecast_interface(counter).attribute6,
519 forecast_interface(counter).attribute7,
520 forecast_interface(counter).attribute8,
521 forecast_interface(counter).attribute9,
522 forecast_interface(counter).attribute10,
523 forecast_interface(counter).attribute11,
524 forecast_interface(counter).attribute12,
525 forecast_interface(counter).attribute13,
526 forecast_interface(counter).attribute14,
527 forecast_interface(counter).attribute15,
528 NULL,
529 NULL,
530 forecast_interface(counter).project_id,
531 forecast_interface(counter).task_id,
532 forecast_interface(counter).line_id,
533 forecast_interface(counter).attribute_category
534 );
535
536 -- dbms_output.put_line(to_Char(SQL%ROWCOUNT));
537
538 BEGIN
539
540 SELECT 1 INTO record_exists
541 FROM mrp_forecast_items
542 WHERE organization_id = forecast_interface(counter).organization_id
543 AND inventory_item_id = forecast_interface(counter).inventory_item_id
544 AND forecast_designator = forecast_interface(counter).forecast_designator;
545
546 EXCEPTION
547 WHEN no_data_found THEN
548 INSERT INTO mrp_forecast_items
549 (
550 inventory_item_id,
551 organization_id,
552 forecast_designator,
553 last_update_date,
554 last_updated_by,
555 creation_date,
556 created_by,
557 last_update_login
558 )
559 values(
560 forecast_interface(counter).inventory_item_id,
561 forecast_interface(counter).organization_id,
562 forecast_interface(counter).forecast_designator,
563 SYSDATE,
564 NVL(forecast_interface(counter).created_by, -1),
565 SYSDATE,
566 NVL(forecast_interface(counter).last_updated_by, -1),
567 NVL(forecast_interface(counter).last_update_login, -1));
568
569 END;
570 -- COMMIT;
571 END IF;
572
573 forecast_interface(counter).process_status := 5;
574
575 <<skip_row>>
576 counter := forecast_interface.next(counter);
577 END LOOP;
578
579 /**
580 mrp_manager_pk.create_forecast_items(-1, -1, NULL);
581 **/
582 RETURN TRUE;
583 END create_for_entries;
584
585
586 FUNCTION del_for_entries(
587 tab_forecast_designator IN OUT NOCOPY t_forecast_designator)
588 RETURN BOOLEAN IS
589 var_low_index NUMBER := 0;
590 var_high_index NUMBER := 0;
591 counter NUMBER := 0;
592 BEGIN
593 var_low_index := tab_forecast_designator.first;
594 IF var_low_index IS NULL
595 THEN
596 RETURN TRUE;
597 END IF;
598 var_high_index := tab_forecast_designator.last;
599
600 counter := var_low_index;
601 WHILE counter <= var_high_index
602 LOOP
603 BEGIN
604
605 IF tab_forecast_designator(counter).inventory_item_id IS NULL THEN
606 delete from mrp_forecast_dates
607 where forecast_designator =
608 tab_forecast_designator(counter).forecast_designator
609 and organization_id =
610 tab_forecast_designator(counter).organization_id;
611
612 ELSE
613 delete from mrp_forecast_dates
614 where forecast_designator =
615 tab_forecast_designator(counter).forecast_designator
616 and organization_id =
617 tab_forecast_designator(counter).organization_id
618 and inventory_item_id =
619 tab_forecast_designator(counter).inventory_item_id;
620 END IF;
621
622 -- IF SQL%NOTFOUND THEN
623 -- IF mrdebug = TRUE THEN
624 -- dbms_output.put_line('Forecast designator/Org '||
625 -- tab_forecast_designator(counter).forecast_designator||'/'||
626 -- to_char(tab_forecast_designator(counter).organization_id) ||
627 -- ' has no rows in mrp_forecast_dates');
628 -- END IF;
629 -- END IF;
630 END;
631
632 BEGIN
633
634 IF tab_forecast_designator(counter).inventory_item_id IS NULL THEN
635 delete from mrp_forecast_items
636 where forecast_designator =
637 tab_forecast_designator(counter).forecast_designator
638 and organization_id =
639 tab_forecast_designator(counter).organization_id;
640 ELSE
641 delete from mrp_forecast_items
642 where forecast_designator =
643 tab_forecast_designator(counter).forecast_designator
644 and organization_id =
645 tab_forecast_designator(counter).organization_id
646 and inventory_item_id =
647 tab_forecast_designator(counter).inventory_item_id;
648 END IF;
649
650 -- IF SQL%NOTFOUND THEN
651 -- IF mrdebug = TRUE THEN
652 -- dbms_output.put_line('Forecast designator/Org '||
653 -- tab_forecast_designator(counter).forecast_designator||'/'||
654 -- to_char(tab_forecast_designator(counter).organization_id) ||
655 -- ' has no rows in mrp_forecast_items');
656 -- END IF;
657 -- END IF;
658 END;
659
660 counter := tab_forecast_designator.next(counter);
661 END LOOP;
662 RETURN TRUE;
663 END del_for_entries;
664
665 FUNCTION mrp_forecast_interface(
666 forecast_interface IN OUT NOCOPY t_forecast_interface)
667 RETURN BOOLEAN IS
668 var_bool BOOLEAN;
669 BEGIN
670 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
671 var_bool := create_for_entries(forecast_interface);
672 RETURN var_bool;
673 -- COMMIT;
674 END mrp_forecast_interface;
675
676 FUNCTION mrp_forecast_interface(
677 forecast_designator IN OUT NOCOPY t_forecast_designator)
678 RETURN BOOLEAN IS
679 var_bool BOOLEAN;
680 BEGIN
681 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
682 var_bool := del_for_entries(forecast_designator);
683 RETURN var_bool;
684 -- COMMIT;
685 END mrp_forecast_interface;
686
687 FUNCTION mrp_forecast_interface(
688 forecast_interface IN OUT NOCOPY t_forecast_interface,
689 forecast_designator IN OUT NOCOPY t_forecast_designator)
690 RETURN BOOLEAN IS
691 var_bool BOOLEAN;
692 BEGIN
693
694 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
695
696 var_bool := del_for_entries(forecast_designator);
697 IF var_bool = FALSE THEN
698 RETURN FALSE;
699 END IF;
700 var_bool := create_for_entries(forecast_interface);
701
702 IF var_bool = FALSE THEN
703 RETURN FALSE;
704 END IF;
705 -- COMMIT;
706 RETURN TRUE;
707 END mrp_forecast_interface;
708
709 PROCEDURE quantity_per_day(x_return_status OUT NOCOPY VARCHAR2,
710 x_msg_count OUT NOCOPY NUMBER,
711 x_msg_data OUT NOCOPY VARCHAR2,
712 p_organization_id IN NUMBER,
713 p_workday_control IN NUMBER,
714 p_start_date IN DATE,
715 p_end_date IN DATE,
716 p_quantity IN NUMBER,
717 x_workday_count OUT NOCOPY NUMBER,
718 x_quantity_per_day OUT NOCOPY QUANTITY_PER_DAY_TBL_TYPE) IS
719
720 CURSOR C1(p_count NUMBER, p_start_date DATE, p_end_date DATE) IS
721 SELECT calendar_date,
722 p_quantity/p_count
723 FROM mtl_parameters param,
724 bom_calendar_dates cal
725 WHERE param.organization_id = p_organization_id
726 AND param.calendar_exception_set_id = cal.exception_set_id
727 AND param.calendar_code = cal.calendar_code
728 AND cal.seq_num IS NOT NULL
729 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
730 AND cal.calendar_date <> p_end_date;
731
732 l_new_start_date DATE;
733 l_new_end_date DATE;
734 l_count NUMBER;
735 l_quantity_per_day QUANTITY_PER_DAY_TBL_TYPE;
736 l_work_date DATE;
737 l_quantity NUMBER;
738 i NUMBER := 1;
739
740 BEGIN
741
742 IF adjust_date(p_start_date, l_new_start_date, 1, p_workday_control,
743 p_organization_id) = FALSE
744 THEN
745 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
746 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
747 fnd_message.set_token('VALUE', to_char(p_start_date));
748 fnd_msg_pub.add;
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751 IF adjust_date(p_end_date, l_new_end_date, 1, p_workday_control,
752 p_organization_id) = FALSE
753 THEN
754 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
755 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
756 fnd_message.set_token('VALUE', to_char(p_end_date));
757 fnd_msg_pub.add;
758 RAISE FND_API.G_EXC_ERROR;
759 END IF;
760
761 l_count := mrp_calendar.days_between(p_organization_id,1,
762 l_new_start_date,l_new_end_date);
763
764 x_workday_count := l_count;
765
766 OPEN C1(l_count, l_new_start_date, l_new_end_date);
767 LOOP
768 EXIT WHEN C1%NOTFOUND;
769 FETCH C1 INTO
770 l_work_date,
771 l_quantity;
772
773 l_quantity_per_day(i).work_date := l_work_date;
774 l_quantity_per_day(i).quantity := l_quantity;
775
776 i := i + 1;
777 END LOOP;
778 CLOSE C1;
779
780 x_quantity_per_day := l_quantity_per_day;
781
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783
784 EXCEPTION
785
786 WHEN FND_API.G_EXC_ERROR THEN
787
788 x_return_status := FND_API.G_RET_STS_ERROR;
789
790 -- Get message count and data
791
792 FND_MSG_PUB.Count_And_Get
793 ( p_count => x_msg_count
794 , p_data => x_msg_data
795 );
796
797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
798
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
800
801 -- Get message count and data
802
803 FND_MSG_PUB.Count_And_Get
804 ( p_count => x_msg_count
805 , p_data => x_msg_data
806 );
807
808 WHEN OTHERS THEN
809
810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
811
812 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
813 THEN
814 FND_MSG_PUB.Add_Exc_Msg
815 ( G_PKG_NAME
816 , 'Quantity_Per_Day'
817 );
818 END IF;
819
820 -- Get message count and data
821
822 FND_MSG_PUB.Count_And_Get
823 ( p_count => x_msg_count
824 , p_data => x_msg_data
825 );
826
827 END quantity_per_day;
828 END; -- package