[Home] [Help]
PACKAGE BODY: APPS.MRP_FORECAST_INTERFACE_PK
Source
1 PACKAGE BODY MRP_FORECAST_INTERFACE_PK AS
2 /* $Header: MRPFAPIB.pls 120.4 2008/05/06 11:42:19 rnatukul noship $ */
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 /* OPM-CONVERGE-START*/
237 /*Commented as the process forecast has been merged with Oracle forecast.
238 Also the migrated forecast data from the interface tables for the process
239 organizations should be pushed to the final tables*/
240 /* AND process_enabled_flag = 'N'; -- 1485309 */
241 /* OPM-CONVERGE-END*/
242
243 EXCEPTION
244 WHEN no_data_found THEN
245 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
246 fnd_message.set_token('ENTITY', 'E_ORGANIZATION', TRUE);
247 fnd_message.set_token('VALUE',
248 to_char(forecast_interface(counter).organization_id));
249 error_message := fnd_message.get;
250 set_interface_error(counter, forecast_interface, error_message);
251 -- IF mrdebug = TRUE THEN
252 -- dbms_output.put_line('Error for Organization Id - '||
253 -- to_char(forecast_interface(counter).organization_id));
254 -- END IF;
255 goto skip_row;
256 END;
257
258 BEGIN
259 SELECT 'x'
260 INTO dummy_var
261 FROM mtl_system_items
262 WHERE organization_id =
263 forecast_interface(counter).organization_id
264 AND inventory_item_id =
265 forecast_interface(counter).inventory_item_id;
266
267 EXCEPTION
268 WHEN no_data_found THEN
269 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
270 fnd_message.set_token('ENTITY', 'E_INVENTORY_ITEM', TRUE);
271 fnd_message.set_token('VALUE',
272 to_char(forecast_interface(counter).inventory_item_id));
273 error_message := fnd_message.get;
274 set_interface_error(counter, forecast_interface, error_message);
275 -- IF mrdebug = TRUE THEN
276 -- dbms_output.put_line('Error for Item - '||
277 -- to_char(forecast_interface(counter).inventory_item_id));
278 -- END IF;
279 goto skip_row;
280 END;
281
282 BEGIN
283 SELECT 'x'
284 INTO dummy_var
285 FROM mrp_forecast_designators
286 WHERE forecast_designator =
287 forecast_interface(counter).forecast_designator
288 AND NVL(disable_date, SYSDATE + 2) > TRUNC(SYSDATE)
289 AND forecast_set IS NOT NULL
290 AND organization_id =
291 forecast_interface(counter).organization_id;
292
293 EXCEPTION
294 WHEN no_data_found THEN
295 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
296 fnd_message.set_token('ENTITY',
297 'E_FORECAST_NAME', TRUE);
298 fnd_message.set_token('VALUE',
299 forecast_interface(counter).forecast_designator);
300 error_message := fnd_message.get;
301 set_interface_error(counter, forecast_interface,
302 error_message);
303 -- IF mrdebug = TRUE THEN
304 -- dbms_output.put_line('Error for Forecast name - '||
305 -- forecast_interface(counter).forecast_designator);
306 -- END IF;
307 goto skip_row;
308 END;
309
310 IF forecast_interface(counter).bucket_type IS null
311 THEN
312 forecast_interface(counter).bucket_type := 1;
313 END IF;
314
315 IF forecast_interface(counter).bucket_type <> 1 AND
316 forecast_interface(counter).bucket_type <> 2 AND
317 forecast_interface(counter).bucket_type <> 3
318 THEN
319 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
320 fnd_message.set_token('ENTITY', 'EC_BUCKET_TYPE', TRUE);
321 fnd_message.set_token('VALUE',
322 to_char(forecast_interface(counter).bucket_type));
323 error_message := fnd_message.get;
324 set_interface_error(counter, forecast_interface, error_message);
325 -- IF mrdebug = TRUE THEN
326 -- dbms_output.put_line('Error for Bucket type - '||
327 -- to_char(forecast_interface(counter).bucket_type));
328 -- END IF;
329 goto skip_row;
330 END IF;
331
332 IF forecast_interface(counter).forecast_date is null
333 THEN
334 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
335 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
336 fnd_message.set_token('VALUE', 'NULL');
337 error_message := fnd_message.get;
338 set_interface_error(counter, forecast_interface, error_message);
339 -- IF mrdebug = TRUE THEN
340 -- dbms_output.put_line('Error for Forecast date - NULL');
341 -- END IF;
342 goto skip_row;
343 END IF;
344
345 IF adjust_date(forecast_interface(counter).forecast_date,
346 var_for_date,
347 forecast_interface(counter).bucket_type,
348 forecast_interface(counter).workday_control,
349 forecast_interface(counter).organization_id) = FALSE
350 THEN
351 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
352 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
353 fnd_message.set_token('VALUE',
354 to_char(forecast_interface(counter).forecast_date));
355 error_message := fnd_message.get;
356 set_interface_error(counter, forecast_interface, error_message);
357 -- IF mrdebug = TRUE THEN
358 -- dbms_output.put_line('Error for Forecast date - '||
359 -- to_char(forecast_interface(counter).forecast_date));
360 -- END IF;
361 goto skip_row;
362 END IF;
363
364 IF forecast_interface(counter).forecast_end_date IS NOT NULL THEN
365 IF adjust_date(forecast_interface(counter).forecast_end_date,
366 var_for_end_date,
367 forecast_interface(counter).bucket_type,
368 forecast_interface(counter).workday_control,
369 forecast_interface(counter).organization_id) = FALSE
370 THEN
371 fnd_message.set_name('MRP', 'IMP-invalid rate_end_date');
372 error_message := fnd_message.get;
373 set_interface_error(counter, forecast_interface, error_message);
374 -- IF mrdebug = TRUE THEN
375 -- dbms_output.put_line('Error for Forecast end date - '||
376 -- to_char(forecast_interface(counter).forecast_end_date));
377 -- END IF;
378 goto skip_row;
379 END IF;
380 IF var_for_end_date < var_for_date THEN
381 fnd_message.set_name('MRP', 'IMP-invalid rate_end_date');
382 error_message := fnd_message.get;
383 set_interface_error(counter, forecast_interface, error_message);
384 -- IF mrdebug = TRUE THEN
385 -- dbms_output.put_line('Error for Forecast end date - '||
386 -- to_char(forecast_interface(counter).forecast_end_date));
387 -- END IF;
388 goto skip_row;
389 END IF;
390 ELSE
391 var_for_end_date := NULL ;
392 END IF;
393
394 IF (forecast_interface(counter).line_id IS NOT NULL) THEN
395 BEGIN
396 SELECT 'x'
397 INTO dummy_var
398 FROM wip_lines
399 WHERE organization_id =
400 forecast_interface(counter).organization_id
401 AND line_id = forecast_interface(counter).line_id;
402
403 EXCEPTION
404 WHEN no_data_found THEN
405 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
406 fnd_message.set_token('ENTITY', 'E_LINE', TRUE);
407 fnd_message.set_token('VALUE',
408 to_char(forecast_interface(counter).line_id));
409 error_message := fnd_message.get;
410 set_interface_error(counter, forecast_interface, error_message);
411 -- IF mrdebug = TRUE THEN
412 -- dbms_output.put_line('Error for Line - '||
413 -- to_char(forecast_interface(counter).line_id));
414 -- END IF;
415 goto skip_row;
416 END;
417 END IF; /* if f_i().line_id is not null */
418
419 p_result := 'S';
420 IF forecast_interface(counter).project_id IS NOT NULL THEN
421 p_result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
422 ( X_inventory_org_id => forecast_interface(counter).organization_id
423 , X_project_id => forecast_interface(counter).project_id
424 , X_task_id => forecast_interface(counter).task_id
425 , X_date1 => var_for_date
426 , X_date2 => var_for_end_date
427 , X_calling_function => 'MRPFAPIB'
428 , X_error_code => errcode
429 );
430 END IF;
431
432 IF (p_result = 'E') THEN
433 error_message := SUBSTRB(fnd_message.get,1,240);
434 set_interface_error(counter, forecast_interface, error_message);
435 goto skip_row;
436 END IF;
437
438
439 /* Change for Bug 1849709 */
440 IF (forecast_interface(counter).action in ('I','U') AND
441 delete_row = FALSE )
442 THEN
443 -- dbms_output.put_line('Inserting row '||
444 -- forecast_interface(counter).forecast_designator);
445
446 /* 1336039 - SVAIDYAN: Insert attribute_category also. */
447
448 INSERT INTO mrp_forecast_dates
449 (
450 transaction_id,
451 last_update_date,
452 last_updated_by,
453 creation_date,
454 created_by,
455 last_update_login,
456 inventory_item_id,
457 organization_id,
458 forecast_designator,
459 forecast_date,
460 rate_end_date,
461 bucket_type,
462 original_forecast_quantity,
463 current_forecast_quantity,
464 comments,
465 confidence_percentage,
466 source_organization_id,
467 source_forecast_designator,
468 origination_type,
469 request_id,
470 source_code,
471 source_line_id,
472 attribute1,
473 attribute2,
474 attribute3,
475 attribute4,
476 attribute5,
477 attribute6,
478 attribute7,
479 attribute8,
480 attribute9,
481 attribute10,
482 attribute11,
483 attribute12,
484 attribute13,
485 attribute14,
486 attribute15,
487 old_transaction_id,
488 to_update,
489 project_id,
490 task_id,
491 line_id,
492 attribute_category)
493 VALUES
494 (
495 NVL(forecast_interface(counter).transaction_id,
496 mrp_forecast_dates_s.nextval),
497 SYSDATE,
498 NVL(forecast_interface(counter).last_updated_by, -1),
499 SYSDATE,
500 NVL(forecast_interface(counter).created_by, -1),
501 NVL(forecast_interface(counter).last_update_login, -1),
502 forecast_interface(counter).inventory_item_id,
503 forecast_interface(counter).organization_id,
504 forecast_interface(counter).forecast_designator,
505 var_for_date,
506 var_for_end_date,
507 forecast_interface(counter).bucket_type,
508 ROUND(forecast_interface(counter).quantity, 6),
509 ROUND(forecast_interface(counter).quantity, 6),
510 forecast_interface(counter).comments,
511 forecast_interface(counter).confidence_percentage,
512 NULL,
513 NULL,
514 IMPORTED_ITEM_FORECAST,
515 forecast_interface(counter).request_id,
516 forecast_interface(counter).source_code,
517 forecast_interface(counter).source_line_id,
518 forecast_interface(counter).attribute1,
519 forecast_interface(counter).attribute2,
520 forecast_interface(counter).attribute3,
521 forecast_interface(counter).attribute4,
522 forecast_interface(counter).attribute5,
523 forecast_interface(counter).attribute6,
524 forecast_interface(counter).attribute7,
525 forecast_interface(counter).attribute8,
526 forecast_interface(counter).attribute9,
527 forecast_interface(counter).attribute10,
528 forecast_interface(counter).attribute11,
529 forecast_interface(counter).attribute12,
530 forecast_interface(counter).attribute13,
531 forecast_interface(counter).attribute14,
532 forecast_interface(counter).attribute15,
533 NULL,
534 NULL,
535 forecast_interface(counter).project_id,
536 forecast_interface(counter).task_id,
537 forecast_interface(counter).line_id,
538 forecast_interface(counter).attribute_category
539 );
540
541 -- dbms_output.put_line(to_Char(SQL%ROWCOUNT));
542
543 BEGIN
544
545 SELECT 1 INTO record_exists
546 FROM mrp_forecast_items
547 WHERE organization_id = forecast_interface(counter).organization_id
548 AND inventory_item_id = forecast_interface(counter).inventory_item_id
549 AND forecast_designator = forecast_interface(counter).forecast_designator;
550
551 EXCEPTION
552 WHEN no_data_found THEN
553 INSERT INTO mrp_forecast_items
554 (
555 inventory_item_id,
556 organization_id,
557 forecast_designator,
558 last_update_date,
559 last_updated_by,
560 creation_date,
561 created_by,
562 last_update_login
563 )
564 values(
565 forecast_interface(counter).inventory_item_id,
566 forecast_interface(counter).organization_id,
567 forecast_interface(counter).forecast_designator,
568 SYSDATE,
569 NVL(forecast_interface(counter).created_by, -1),
570 SYSDATE,
571 NVL(forecast_interface(counter).last_updated_by, -1),
572 NVL(forecast_interface(counter).last_update_login, -1));
573
574 END;
575 -- COMMIT;
576 END IF;
577
578 forecast_interface(counter).process_status := 5;
579
580 <<skip_row>>
581 counter := forecast_interface.next(counter);
582 END LOOP;
583
584 /**
585 mrp_manager_pk.create_forecast_items(-1, -1, NULL);
586 **/
587 RETURN TRUE;
588 END create_for_entries;
589
590
591 FUNCTION del_for_entries(
592 tab_forecast_designator IN OUT NOCOPY t_forecast_designator)
593 RETURN BOOLEAN IS
594 var_low_index NUMBER := 0;
595 var_high_index NUMBER := 0;
596 counter NUMBER := 0;
597 BEGIN
598 var_low_index := tab_forecast_designator.first;
599 IF var_low_index IS NULL
600 THEN
601 RETURN TRUE;
602 END IF;
603 var_high_index := tab_forecast_designator.last;
604
605 counter := var_low_index;
606 WHILE counter <= var_high_index
607 LOOP
608 BEGIN
609
610 IF tab_forecast_designator(counter).inventory_item_id IS NULL THEN
611 delete from mrp_forecast_dates
612 where forecast_designator =
613 tab_forecast_designator(counter).forecast_designator
614 and organization_id =
615 tab_forecast_designator(counter).organization_id;
616
617 ELSE
618 delete from mrp_forecast_dates
619 where forecast_designator =
620 tab_forecast_designator(counter).forecast_designator
621 and organization_id =
622 tab_forecast_designator(counter).organization_id
623 and inventory_item_id =
624 tab_forecast_designator(counter).inventory_item_id;
625 END IF;
626
627 -- IF SQL%NOTFOUND THEN
628 -- IF mrdebug = TRUE THEN
629 -- dbms_output.put_line('Forecast designator/Org '||
630 -- tab_forecast_designator(counter).forecast_designator||'/'||
631 -- to_char(tab_forecast_designator(counter).organization_id) ||
632 -- ' has no rows in mrp_forecast_dates');
633 -- END IF;
634 -- END IF;
635 END;
636
637 BEGIN
638
639 IF tab_forecast_designator(counter).inventory_item_id IS NULL THEN
640 delete from mrp_forecast_items
641 where forecast_designator =
642 tab_forecast_designator(counter).forecast_designator
643 and organization_id =
644 tab_forecast_designator(counter).organization_id;
645 ELSE
646 delete from mrp_forecast_items
647 where forecast_designator =
648 tab_forecast_designator(counter).forecast_designator
649 and organization_id =
650 tab_forecast_designator(counter).organization_id
651 and inventory_item_id =
652 tab_forecast_designator(counter).inventory_item_id;
653 END IF;
654
655 -- IF SQL%NOTFOUND THEN
656 -- IF mrdebug = TRUE THEN
657 -- dbms_output.put_line('Forecast designator/Org '||
658 -- tab_forecast_designator(counter).forecast_designator||'/'||
659 -- to_char(tab_forecast_designator(counter).organization_id) ||
660 -- ' has no rows in mrp_forecast_items');
661 -- END IF;
662 -- END IF;
663 END;
664
665 counter := tab_forecast_designator.next(counter);
666 END LOOP;
667 RETURN TRUE;
668 END del_for_entries;
669
670 FUNCTION mrp_forecast_interface(
671 forecast_interface IN OUT NOCOPY t_forecast_interface)
672 RETURN BOOLEAN IS
673 var_bool BOOLEAN;
674 BEGIN
675 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
676 var_bool := create_for_entries(forecast_interface);
677 RETURN var_bool;
678 -- COMMIT;
679 END mrp_forecast_interface;
680
681 FUNCTION mrp_forecast_interface(
682 forecast_designator IN OUT NOCOPY t_forecast_designator)
683 RETURN BOOLEAN IS
684 var_bool BOOLEAN;
685 BEGIN
686 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
687 var_bool := del_for_entries(forecast_designator);
688 RETURN var_bool;
689 -- COMMIT;
690 END mrp_forecast_interface;
691
692 FUNCTION mrp_forecast_interface(
693 forecast_interface IN OUT NOCOPY t_forecast_interface,
694 forecast_designator IN OUT NOCOPY t_forecast_designator)
695 RETURN BOOLEAN IS
696 var_bool BOOLEAN;
697 BEGIN
698
699 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
700
701 var_bool := del_for_entries(forecast_designator);
702 IF var_bool = FALSE THEN
703 RETURN FALSE;
704 END IF;
705 var_bool := create_for_entries(forecast_interface);
706
707 IF var_bool = FALSE THEN
708 RETURN FALSE;
709 END IF;
710 -- COMMIT;
711 RETURN TRUE;
712 END mrp_forecast_interface;
713
714 PROCEDURE quantity_per_day(x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 p_organization_id IN NUMBER,
718 p_workday_control IN NUMBER,
719 p_start_date IN DATE,
720 p_end_date IN DATE,
721 p_quantity IN NUMBER,
722 x_workday_count OUT NOCOPY NUMBER,
723 x_quantity_per_day OUT NOCOPY QUANTITY_PER_DAY_TBL_TYPE) IS
724
725 CURSOR C1(p_count NUMBER, p_start_date DATE, p_end_date DATE) IS
726 SELECT calendar_date,
727 p_quantity/p_count
728 FROM mtl_parameters param,
729 bom_calendar_dates cal
730 WHERE param.organization_id = p_organization_id
731 AND param.calendar_exception_set_id = cal.exception_set_id
732 AND param.calendar_code = cal.calendar_code
733 AND cal.seq_num IS NOT NULL
734 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
735 AND cal.calendar_date <> p_end_date;
736
737 l_new_start_date DATE;
738 l_new_end_date DATE;
739 l_count NUMBER;
740 l_quantity_per_day QUANTITY_PER_DAY_TBL_TYPE;
741 l_work_date DATE;
742 l_quantity NUMBER;
743 i NUMBER := 1;
744
745 BEGIN
746
747 IF adjust_date(p_start_date, l_new_start_date, 1, p_workday_control,
748 p_organization_id) = FALSE
749 THEN
750 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
751 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
752 fnd_message.set_token('VALUE', to_char(p_start_date));
753 fnd_msg_pub.add;
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756 IF adjust_date(p_end_date, l_new_end_date, 1, p_workday_control,
757 p_organization_id) = FALSE
758 THEN
759 fnd_message.set_name('MRP', 'GEN-INVALID ENTITY');
760 fnd_message.set_token('ENTITY', 'EC_FORECAST_DATE', TRUE);
761 fnd_message.set_token('VALUE', to_char(p_end_date));
762 fnd_msg_pub.add;
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765
766 l_count := mrp_calendar.days_between(p_organization_id,1,
767 l_new_start_date,l_new_end_date);
768
769 x_workday_count := l_count;
770
771 OPEN C1(l_count, l_new_start_date, l_new_end_date);
772 LOOP
773 EXIT WHEN C1%NOTFOUND;
774 FETCH C1 INTO
775 l_work_date,
776 l_quantity;
777
778 l_quantity_per_day(i).work_date := l_work_date;
779 l_quantity_per_day(i).quantity := l_quantity;
780
781 i := i + 1;
782 END LOOP;
783 CLOSE C1;
784
785 x_quantity_per_day := l_quantity_per_day;
786
787 x_return_status := FND_API.G_RET_STS_SUCCESS;
788
789 EXCEPTION
790
791 WHEN FND_API.G_EXC_ERROR THEN
792
793 x_return_status := FND_API.G_RET_STS_ERROR;
794
795 -- Get message count and data
796
797 FND_MSG_PUB.Count_And_Get
798 ( p_count => x_msg_count
799 , p_data => x_msg_data
800 );
801
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
805
806 -- Get message count and data
807
808 FND_MSG_PUB.Count_And_Get
809 ( p_count => x_msg_count
810 , p_data => x_msg_data
811 );
812
813 WHEN OTHERS THEN
814
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
816
817 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
818 THEN
819 FND_MSG_PUB.Add_Exc_Msg
820 ( G_PKG_NAME
821 , 'Quantity_Per_Day'
822 );
823 END IF;
824
825 -- Get message count and data
826
827 FND_MSG_PUB.Count_And_Get
828 ( p_count => x_msg_count
829 , p_data => x_msg_data
830 );
831
832 END quantity_per_day;
833 END; -- package