[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_UTILS
Source
1 PACKAGE BODY CE_FORECAST_UTILS as
2 /* $Header: cefutilb.pls 120.2 2006/03/14 23:26:23 eliu ship $ */
3
4 FUNCTION get_xtr_user RETURN VARCHAR2 IS
5 BEGIN
6 if (G_xtr_user is null) then
7 if XTR_USER = 1 then
8 G_xtr_user := 'Y';
9 else
10 G_xtr_user := 'N';
11 end if;
12 end if;
13 return G_xtr_user;
14 END;
15
16 FUNCTION get_xtr_user_first RETURN VARCHAR2 IS
17 BEGIN
18 if XTR_USER = 1 then
19 G_xtr_user := 'Y';
20 else
21 G_xtr_user := 'N';
22 end if;
23 return G_xtr_user;
24 END;
25
26
27 /* ---------------------------------------------------------------------
28 | PUBLIC FUNCTION |
29 | Aging_Buckets_String |
30 | |
31 | DESCRIPTION |
32 | This function returns the aging buckets for a forecast as a |
33 | single string that will be parsed in Java by the appropriate OA |
34 | controller. |
35 | CALLED BY |
36 | OA Controller Classes |
37 | REQUIRES |
38 | Forecast_Id |
39 | HISTORY |
40 | 13-JAN-2003 Created Sunil Poonen |
41 --------------------------------------------------------------------- */
42
43 FUNCTION Aging_Buckets_String ( X_forecast_id NUMBER,
44 X_forecast_header_id NUMBER DEFAULT NULL,
45 X_start_date DATE DEFAULT NULL,
46 X_start_period VARCHAR2 DEFAULT NULL,
47 X_period_set_name VARCHAR2 DEFAULT NULL)
48 RETURN VARCHAR2 IS
49 l_all_buckets VARCHAR2(3000);
50 l_current_bucket VARCHAR2(15);
51 l_forecast_header_id NUMBER;
52 l_period_set_name VARCHAR2(15);
53 l_start_period VARCHAR2(15);
54 l_start_date DATE;
55 l_aging_type VARCHAR2(1);
56 l_count NUMBER;
57
58 cursor cDate is SELECT start_date,
59 end_date
60 FROM ce_fc_aging_buckets_v
61 WHERE developer_column_num > 0
62 ORDER BY developer_column_num;
63 cursor cAcct is SELECT forecast_column_id,
64 period_from,
65 period_to
66 FROM ce_fc_aging_buckets_v
67 WHERE developer_column_num > 0
68 ORDER BY developer_column_num;
69 cursor cCol(phid NUMBER) is SELECT forecast_column_id
70 FROM ce_forecast_columns
71 WHERE forecast_header_id = phid
72 AND developer_column_num > 0
73 ORDER BY developer_column_num;
74
75 BEGIN
76 if X_forecast_header_id is null then
77 select forecast_header_id,
78 period_set_name,
79 start_period,
80 start_date
81 into l_forecast_header_id,
82 l_period_set_name,
83 l_start_period,
84 l_start_date
85 from ce_forecasts
86 where forecast_id = X_forecast_id;
87 else
88 l_forecast_header_id := X_forecast_header_id;
89 l_period_set_name := X_period_set_name;
90 l_start_period := X_start_period;
91 l_start_date := X_start_date;
92 end if;
93
94 select aging_type
95 into l_aging_type
96 from ce_forecast_headers
97 where forecast_header_id = l_forecast_header_id;
98
99 CEFC_VIEW_CONST.set_constants(l_forecast_header_id,
100 l_period_set_name,
101 l_start_period,
102 trunc(l_start_date),
103 NULL, NULL);
104 if l_aging_type = 'D' then
105 l_all_buckets := '';
106 l_count := 0;
107 for c_rec in cDate loop
108 l_count := l_count + 1;
109 if c_rec.start_date = c_rec.end_date then
110 l_all_buckets := l_all_buckets || to_date(c_rec.start_date,'J') || '|';
111 else
112 l_all_buckets := l_all_buckets || to_date(c_rec.start_date,'J')
113 || ' - ' || to_date(c_rec.end_date,'J') || '|';
114 end if;
115 end loop;
116 else
117 l_all_buckets := '';
118 l_count := 0;
119 for c_rec in cAcct loop
120 l_count := l_count + 1;
121 if c_rec.period_from = c_rec.period_to then
122 l_all_buckets := l_all_buckets || c_rec.period_from || '|';
123 else
124 l_all_buckets := l_all_buckets || c_rec.period_from || ' - '
125 || c_rec.period_to || '|';
126 end if;
127 end loop;
128 end if;
129
130 return l_all_buckets;
131
132 END Aging_Buckets_String;
133
134 /* ---------------------------------------------------------------------
135 | PUBLIC PROCEDRE |
136 | Delete_Forecast_Children |
137 | |
138 | DESCRIPTION |
139 | This procedure deletes all children of a particular forecast |
140 | from CE_FORECAST_CELLS, CE_FORECAST_TRX_CELLS and |
141 | CE_FORECAST_OPENING_BAL tables. |
142 | |
143 | CALLED BY |
144 | OA Controller Classes |
145 | REQUIRES |
146 | Forecast_Id |
147 | HISTORY |
148 | 04-FEB-2003 Created Sunil Poonen |
149 --------------------------------------------------------------------- */
150
151 PROCEDURE Delete_Forecast_Children (X_forecast_id NUMBER) IS
152 l_count NUMBER;
153 BEGIN
154 select count(1)
155 into l_count
156 from ce_forecast_cells
157 where forecast_id = X_forecast_id;
158
159 if l_count > 0 then
160 delete from ce_forecast_cells where forecast_id = X_forecast_id;
161 end if;
162
163 select count(1)
164 into l_count
165 from ce_forecast_trx_cells
166 where forecast_id = X_forecast_id;
167
168 if l_count > 0 then
169 delete from ce_forecast_trx_cells where forecast_id = X_forecast_id;
170 end if;
171
172 select count(1)
173 into l_count
174 from ce_forecast_opening_bal
175 where forecast_id = X_forecast_id;
176
177 if l_count > 0 then
178 delete from ce_forecast_opening_bal where forecast_id = X_forecast_id;
179 end if;
180
181 select count(1)
182 into l_count
183 from ce_forecast_errors
184 where forecast_id = X_forecast_id;
185
186 if l_count > 0 then
187 delete from ce_forecast_errors where forecast_id = X_forecast_id;
188 end if;
189
190 END Delete_Forecast_Children;
191
192 /* ---------------------------------------------------------------------
193 | PUBLIC PROCEDRE |
194 | Create_Dummy_Rows |
195 | |
196 | DESCRIPTION |
197 | This procedure fills the new manually created rows of |
198 | User-defined Inflow or Outflow in a forecast with zeroes |
199 | |
200 | CALLED BY |
201 | OA Controller Classes |
202 | REQUIRES |
203 | Forecast_Header_Id |
204 | HISTORY |
205 | 26-FEB-2003 Created Sunil Poonen |
206 --------------------------------------------------------------------- */
207
208 PROCEDURE Create_Dummy_Rows (X_forecast_header_id NUMBER) IS
209 CURSOR C_fc IS SELECT forecast_id
210 FROM ce_forecasts
211 WHERE forecast_header_id = X_forecast_header_id;
212 CURSOR C_frow(p_forecast_id NUMBER) IS SELECT forecast_row_id
213 FROM ce_forecast_rows
214 WHERE forecast_header_id = X_forecast_header_id
215 AND forecast_row_id not in
216 (select forecast_row_id
217 from ce_forecast_cells
218 where forecast_header_id = X_forecast_header_id
219 and forecast_id = p_forecast_id);
220 CURSOR C_frow_trx(p_forecast_id NUMBER) IS SELECT forecast_row_id
221 FROM ce_forecast_rows
222 WHERE forecast_header_id = X_forecast_header_id
223 AND forecast_row_id not in
224 (select forecast_row_id
225 from ce_forecast_trx_cells
226 where forecast_header_id = X_forecast_header_id
227 and forecast_id = p_forecast_id);
228
229 forecast_rowid VARCHAR2(30);
230 forecast_cell_id NUMBER;
231 l_forecast_column_id NUMBER;
232 l_count NUMBER;
233 BEGIN
234 SELECT forecast_column_id
235 INTO l_forecast_column_id
236 FROM ce_forecast_columns
237 WHERE forecast_header_id = X_forecast_header_id
238 AND developer_column_num = 1;
239
240 FOR p_fc IN C_fc LOOP
241 SELECT count(1)
242 INTO l_count
243 FROM ce_forecast_cells
244 WHERE forecast_id = p_fc.forecast_id;
245 IF l_count > 0 THEN
246 FOR p_frow IN C_frow(p_fc.forecast_id) LOOP
247 forecast_cell_id := NULL;
248 forecast_rowid := NULL;
249 CE_FORECAST_CELLS_PKG.insert_row(
250 X_rowid =>forecast_rowid,
251 X_FORECAST_CELL_ID =>forecast_cell_id,
252 X_FORECAST_ID =>p_fc.forecast_id,
253 X_FORECAST_HEADER_ID =>X_forecast_header_id,
254 X_FORECAST_ROW_ID =>p_frow.forecast_row_id,
255 X_FORECAST_COLUMN_ID =>l_forecast_column_id,
256 X_AMOUNT =>0,
257 X_CREATED_BY =>nvl(fnd_global.user_id,-1),
258 X_CREATION_DATE =>sysdate,
259 X_LAST_UPDATED_BY =>nvl(fnd_global.user_id,-1),
260 X_LAST_UPDATE_DATE =>sysdate,
261 X_LAST_UPDATE_LOGIN =>nvl(fnd_global.user_id,-1));
262 END LOOP;
263 END IF;
264
265 SELECT count(1)
266 INTO l_count
267 FROM ce_forecast_trx_cells
268 WHERE forecast_id = p_fc.forecast_id;
269 IF l_count > 0 THEN
270 FOR p_frow_trx IN C_frow_trx(p_fc.forecast_id) LOOP
271 forecast_cell_id := NULL;
272 forecast_rowid := NULL;
273 CE_FORECAST_TRX_CELLS_PKG.insert_row(
274 X_rowid =>forecast_rowid,
275 X_FORECAST_CELL_ID =>forecast_cell_id,
276 X_FORECAST_ID =>p_fc.forecast_id,
277 X_FORECAST_HEADER_ID =>X_forecast_header_id,
278 X_FORECAST_ROW_ID =>p_frow_trx.forecast_row_id,
279 X_FORECAST_COLUMN_ID =>l_forecast_column_id,
280 X_AMOUNT =>0,
281 X_TRX_AMOUNT =>to_number(null),
282 X_REFERENCE_ID =>null,
283 X_CURRENCY_CODE =>null,
284 X_ORG_ID =>null,
285 X_INCLUDE_FLAG =>'Y',
286 X_TRX_DATE =>null,
287 X_BANK_ACCOUNT_ID =>null,
288 X_CODE_COMBINATION_ID =>null,
289 X_CREATED_BY =>nvl(fnd_global.user_id,-1),
290 X_CREATION_DATE =>sysdate,
291 X_LAST_UPDATED_BY =>nvl(fnd_global.user_id,-1),
292 X_LAST_UPDATE_DATE =>sysdate,
293 X_LAST_UPDATE_LOGIN =>nvl(fnd_global.user_id,-1));
294 END LOOP;
295 END IF;
296 END LOOP;
297
298 END Create_Dummy_Rows;
299
300 /* ---------------------------------------------------------------------
301 | PUBLIC PROCEDRE |
302 | Update_Column_Setup |
303 | |
304 | DESCRIPTION |
305 | This procedure updates the column setup from Automatic |
306 | to Manual for the case where columns were manually added |
307 | |
308 | CALLED BY |
309 | OA Controller Classes |
310 | REQUIRES |
311 | Forecast Header Id |
312 | HISTORY |
313 | 14-MAR-2003 Created Sunil Poonen |
314 --------------------------------------------------------------------- */
315
316 PROCEDURE Update_Column_Setup (X_forecast_header_id NUMBER) IS
317 l_column_setup VARCHAR2(1);
318 BEGIN
319 select column_setup
320 into l_column_setup
321 from ce_forecast_headers
322 where forecast_header_id = X_forecast_header_id;
323
324 if l_column_setup = 'A' then
325 update ce_forecast_headers
326 set column_setup = 'M'
327 where forecast_header_id = X_forecast_header_id;
328 end if;
329
330 END Update_Column_Setup;
331
332 /* ---------------------------------------------------------------------
333 | PUBLIC PROCEDRE |
334 | Duplicate_Template |
335 | |
336 | DESCRIPTION |
337 | This procedure duplicates a forecast template |
338 | |
339 | CALLED BY |
340 | OA Controller Classes |
341 | REQUIRES |
342 | Forecast Header Id, New Template Name |
343 | HISTORY |
344 | 03-MAR-2003 Created Sunil Poonen |
345 --------------------------------------------------------------------- */
346
347 PROCEDURE Duplicate_Template (X_forecast_header_id NUMBER,
348 X_new_name VARCHAR2,
349 X_forecast_id NUMBER DEFAULT NULL) IS
350 CURSOR C_frow IS SELECT forecast_row_id,
351 forecast_header_id,
352 row_number,
353 trx_type,
354 lead_time,
355 forecast_method,
356 discount_option,
357 order_status,
358 order_date_type,
359 code_combination_id,
360 set_of_books_id,
361 org_id,
362 chart_of_accounts_id,
363 budget_name,
364 budget_version_id,
365 encumbrance_type_id,
366 roll_forward_type,
367 roll_forward_period,
368 customer_profile_class_id,
369 include_dispute_flag,
370 sales_stage_id,
371 channel_code,
372 win_probability,
373 sales_forecast_status,
374 receipt_method_id,
375 bank_account_id,
376 payment_method,
377 pay_group,
378 payment_priority,
379 vendor_type,
380 authorization_status,
381 type,
382 budget_type,
383 budget_version,
384 include_hold_flag,
385 include_net_cash_flag,
386 xtr_bank_account,
387 exclude_indic_exp,
388 company_code,
389 created_by,
390 creation_date,
391 last_updated_by,
392 last_update_date,
393 last_update_login,
394 org_payment_method_id,
395 attribute_category,
396 attribute1,
397 attribute2,
398 attribute3,
399 attribute4,
400 attribute5,
401 attribute6,
402 attribute7,
403 attribute8,
404 attribute9,
405 attribute10,
406 attribute11,
407 attribute12,
408 attribute13,
409 attribute14,
410 attribute15,
411 description,
412 payroll_id,
413 external_source_type,
414 criteria_category,
415 criteria1,
416 criteria2,
417 criteria3,
418 criteria4,
419 criteria5,
420 criteria6,
421 criteria7,
422 criteria8,
423 criteria9,
424 criteria10,
425 criteria11,
426 criteria12,
427 criteria13,
428 criteria14,
429 criteria15,
430 use_average_payment_days,
431 period,
432 order_type_id,
433 use_payment_terms
434 FROM ce_forecast_rows
435 WHERE forecast_header_id = X_forecast_header_id;
436
437 CURSOR C_fcol IS SELECT forecast_column_id,
438 forecast_header_id,
439 column_number,
440 days_from,
441 days_to,
442 developer_column_num,
443 created_by,
444 creation_date,
445 last_updated_by,
446 last_update_date,
447 last_update_login,
448 attribute_category,
449 attribute1,
450 attribute2,
451 attribute3,
452 attribute4,
453 attribute5,
454 attribute6,
455 attribute7,
456 attribute8,
457 attribute9,
458 attribute10,
459 attribute11,
460 attribute12,
461 attribute13,
462 attribute14,
463 attribute15
464 FROM ce_forecast_columns
465 WHERE forecast_header_id = X_forecast_header_id;
466
467 CURSOR C_fperiod IS SELECT forecast_period_id,
468 forecast_header_id,
469 level_of_summary,
470 period_number,
471 length_of_period,
472 length_type,
473 created_by,
474 creation_date,
475 last_updated_by,
476 last_update_date,
477 last_update_login
478 FROM ce_forecast_periods
479 WHERE forecast_header_id = X_forecast_header_id;
480
481 l_forecast_header_id NUMBER;
482 l_forecast_row_id NUMBER;
483 l_forecast_column_id NUMBER;
484 l_forecast_period_id NUMBER;
485 l_drilldown_flag VARCHAR2(1);
486 BEGIN
487 select ce_forecast_headers_s.nextval
488 into l_forecast_header_id
489 from dual;
490
491 INSERT INTO ce_forecast_headers(
492 forecast_header_id,
493 name,
494 description,
495 aging_type,
496 overdue_transactions,
497 cutoff_period,
498 transaction_calendar_id,
499 start_project_id,
500 end_project_id,
501 treasury_template,
502 created_by,
503 creation_date,
504 last_updated_by,
505 last_update_date,
506 last_update_login,
507 attribute_category,
508 attribute1,
509 attribute2,
510 attribute3,
511 attribute4,
512 attribute5,
513 attribute6,
514 attribute7,
515 attribute8,
516 attribute9,
517 attribute10,
518 attribute11,
519 attribute12,
520 attribute13,
521 attribute14,
522 attribute15
523 ) SELECT l_forecast_header_id,
524 X_new_name,
525 description,
526 aging_type,
527 overdue_transactions,
528 cutoff_period,
529 transaction_calendar_id,
530 start_project_id,
531 end_project_id,
532 treasury_template,
533 nvl(fnd_global.user_id,-1),
534 sysdate,
535 nvl(fnd_global.user_id,-1),
536 sysdate,
537 nvl(fnd_global.user_id,-1),
538 attribute_category,
539 attribute1,
540 attribute2,
541 attribute3,
542 attribute4,
543 attribute5,
544 attribute6,
545 attribute7,
546 attribute8,
547 attribute9,
548 attribute10,
549 attribute11,
550 attribute12,
551 attribute13,
552 attribute14,
553 attribute15
554 FROM ce_forecast_headers
555 WHERE forecast_header_id = X_forecast_header_id;
556
557 if X_forecast_id is not null then
558 select nvl(drilldown_flag,'N')
559 into l_drilldown_flag
560 from ce_forecasts
561 where forecast_id = X_forecast_id;
562
563 UPDATE ce_forecasts
564 SET forecast_header_id = l_forecast_header_id
565 WHERE forecast_id = X_forecast_id;
566
567 if l_drilldown_flag = 'Y' then
568 UPDATE ce_forecast_trx_cells
569 SET forecast_header_id = l_forecast_header_id
570 WHERE forecast_id = X_forecast_id;
571 else
572 UPDATE ce_forecast_cells
573 SET forecast_header_id = l_forecast_header_id
574 WHERE forecast_id = X_forecast_id;
575 end if;
576 end if;
577
578
579 FOR p_frow in C_frow LOOP
580 select ce_forecast_rows_s.nextval
581 into l_forecast_row_id
582 from dual;
583
584 INSERT INTO ce_forecast_rows(
585 forecast_row_id,
586 forecast_header_id,
587 row_number,
588 trx_type,
589 lead_time,
590 forecast_method,
591 discount_option,
592 order_status,
593 order_date_type,
594 code_combination_id,
595 set_of_books_id,
596 org_id,
597 chart_of_accounts_id,
598 budget_name,
599 budget_version_id,
600 encumbrance_type_id,
601 roll_forward_type,
602 roll_forward_period,
603 customer_profile_class_id,
604 include_dispute_flag,
605 sales_stage_id,
606 channel_code,
607 win_probability,
608 sales_forecast_status,
609 receipt_method_id,
610 bank_account_id,
611 payment_method,
612 pay_group,
613 payment_priority,
614 vendor_type,
615 authorization_status,
616 type,
617 budget_type,
618 budget_version,
619 include_hold_flag,
620 include_net_cash_flag,
621 xtr_bank_account,
622 exclude_indic_exp,
623 company_code,
624 created_by,
625 creation_date,
626 last_updated_by,
627 last_update_date,
628 last_update_login,
629 org_payment_method_id,
630 attribute_category,
631 attribute1,
632 attribute2,
633 attribute3,
634 attribute4,
635 attribute5,
636 attribute6,
637 attribute7,
638 attribute8,
639 attribute9,
640 attribute10,
641 attribute11,
642 attribute12,
643 attribute13,
644 attribute14,
645 attribute15,
646 description,
647 payroll_id,
648 external_source_type,
649 criteria_category,
650 criteria1,
651 criteria2,
652 criteria3,
653 criteria4,
654 criteria5,
655 criteria6,
656 criteria7,
657 criteria8,
658 criteria9,
659 criteria10,
660 criteria11,
661 criteria12,
662 criteria13,
663 criteria14,
664 criteria15,
665 use_average_payment_days,
666 period,
667 order_type_id,
668 use_payment_terms
669 ) VALUES (l_forecast_row_id,
670 l_forecast_header_id,
671 p_frow.row_number,
672 p_frow.trx_type,
673 p_frow.lead_time,
674 p_frow.forecast_method,
675 p_frow.discount_option,
676 p_frow.order_status,
677 p_frow.order_date_type,
678 p_frow.code_combination_id,
679 p_frow.set_of_books_id,
680 p_frow.org_id,
681 p_frow.chart_of_accounts_id,
682 p_frow.budget_name,
683 p_frow.budget_version_id,
684 p_frow.encumbrance_type_id,
685 p_frow.roll_forward_type,
686 p_frow.roll_forward_period,
687 p_frow.customer_profile_class_id,
688 p_frow.include_dispute_flag,
689 p_frow.sales_stage_id,
690 p_frow.channel_code,
691 p_frow.win_probability,
692 p_frow.sales_forecast_status,
693 p_frow.receipt_method_id,
694 p_frow.bank_account_id,
695 p_frow.payment_method,
696 p_frow.pay_group,
697 p_frow.payment_priority,
698 p_frow.vendor_type,
699 p_frow.authorization_status,
700 p_frow.type,
701 p_frow.budget_type,
702 p_frow.budget_version,
703 p_frow.include_hold_flag,
704 p_frow.include_net_cash_flag,
705 p_frow.xtr_bank_account,
706 p_frow.exclude_indic_exp,
707 p_frow.company_code,
708 nvl(fnd_global.user_id,-1),
709 sysdate,
710 nvl(fnd_global.user_id,-1),
711 sysdate,
712 nvl(fnd_global.user_id,-1),
713 p_frow.org_payment_method_id,
714 p_frow.attribute_category,
715 p_frow.attribute1,
716 p_frow.attribute2,
717 p_frow.attribute3,
718 p_frow.attribute4,
719 p_frow.attribute5,
720 p_frow.attribute6,
721 p_frow.attribute7,
722 p_frow.attribute8,
723 p_frow.attribute9,
724 p_frow.attribute10,
725 p_frow.attribute11,
726 p_frow.attribute12,
727 p_frow.attribute13,
728 p_frow.attribute14,
729 p_frow.attribute15,
730 p_frow.description,
731 p_frow.payroll_id,
732 p_frow.external_source_type,
733 p_frow.criteria_category,
734 p_frow.criteria1,
735 p_frow.criteria2,
736 p_frow.criteria3,
737 p_frow.criteria4,
738 p_frow.criteria5,
739 p_frow.criteria6,
740 p_frow.criteria7,
741 p_frow.criteria8,
742 p_frow.criteria9,
743 p_frow.criteria10,
744 p_frow.criteria11,
745 p_frow.criteria12,
746 p_frow.criteria13,
747 p_frow.criteria14,
748 p_frow.criteria15,
749 p_frow.use_average_payment_days,
750 p_frow.period,
751 p_frow.order_type_id,
752 p_frow.use_payment_terms);
753
754 if X_forecast_id is not null then
755 if l_drilldown_flag = 'Y' then
756 update ce_forecast_trx_cells
757 set forecast_row_id = l_forecast_row_id
758 where forecast_row_id = p_frow.forecast_row_id
759 and forecast_id = X_forecast_id;
760 else
761 update ce_forecast_cells
762 set forecast_row_id = l_forecast_row_id
763 where forecast_row_id = p_frow.forecast_row_id
764 and forecast_id = X_forecast_id;
765 end if;
766 end if;
767 END LOOP;
768
769 FOR p_fcol in C_fcol LOOP
770 select ce_forecast_columns_s.nextval
771 into l_forecast_column_id
772 from dual;
773
774 INSERT INTO ce_forecast_columns(
775 forecast_column_id,
776 forecast_header_id,
777 column_number,
778 days_from,
779 days_to,
780 developer_column_num,
781 created_by,
782 creation_date,
783 last_updated_by,
784 last_update_date,
785 last_update_login,
786 attribute_category,
787 attribute1,
788 attribute2,
789 attribute3,
790 attribute4,
791 attribute5,
792 attribute6,
793 attribute7,
794 attribute8,
795 attribute9,
796 attribute10,
797 attribute11,
798 attribute12,
799 attribute13,
800 attribute14,
801 attribute15
802 ) VALUES (l_forecast_column_id,
803 l_forecast_header_id,
804 p_fcol.column_number,
805 p_fcol.days_from,
806 p_fcol.days_to,
807 p_fcol.developer_column_num,
808 nvl(fnd_global.user_id,-1),
809 sysdate,
810 nvl(fnd_global.user_id,-1),
811 sysdate,
812 nvl(fnd_global.user_id,-1),
813 p_fcol.attribute_category,
814 p_fcol.attribute1,
815 p_fcol.attribute2,
816 p_fcol.attribute3,
817 p_fcol.attribute4,
818 p_fcol.attribute5,
819 p_fcol.attribute6,
820 p_fcol.attribute7,
821 p_fcol.attribute8,
822 p_fcol.attribute9,
823 p_fcol.attribute10,
824 p_fcol.attribute11,
825 p_fcol.attribute12,
826 p_fcol.attribute13,
827 p_fcol.attribute14,
828 p_fcol.attribute15);
829
830 if X_forecast_id is not null then
831 if l_drilldown_flag = 'Y' then
832 update ce_forecast_trx_cells
833 set forecast_column_id = l_forecast_column_id
834 where forecast_column_id = p_fcol.forecast_column_id
835 and forecast_id = X_forecast_id;
836 else
837 update ce_forecast_cells
838 set forecast_column_id = l_forecast_column_id
839 where forecast_column_id = p_fcol.forecast_column_id
840 and forecast_id = X_forecast_id;
841 end if;
842 end if;
843 END LOOP;
844
845 FOR p_fperiod in C_fperiod LOOP
846 select ce_forecast_periods_s.nextval
847 into l_forecast_period_id
848 from dual;
849
850 INSERT INTO ce_forecast_periods(
851 forecast_period_id,
852 forecast_header_id,
853 level_of_summary,
854 period_number,
855 length_of_period,
856 length_type,
857 created_by,
858 creation_date,
859 last_updated_by,
860 last_update_date,
861 last_update_login
862 ) VALUES (l_forecast_period_id,
863 p_fperiod.forecast_header_id,
864 p_fperiod.level_of_summary,
865 p_fperiod.period_number,
866 p_fperiod.length_of_period,
867 p_fperiod.length_type,
868 nvl(fnd_global.user_id,-1),
869 sysdate,
870 nvl(fnd_global.user_id,-1),
871 sysdate,
872 nvl(fnd_global.user_id,-1)
873 );
874 END LOOP;
875
876 END Duplicate_Template;
877
878
879 /* ---------------------------------------------------------------------
880 | PUBLIC PROCEDURE |
881 | populate_temp_buckets |
882 | |
883 | DESCRIPTION |
884 | Populates ce_forecast_columns based on the aging-bucket |
885 | information in ce_forecast_periods |
886 | CALLED BY |
887 | populate_aging_buckets |
888 | REQUIRES |
889 | |
890 | HISTORY |
891 | 29-OCT-2001 Created Sunil Poonen |
892 --------------------------------------------------------------------- */
893 PROCEDURE populate_temp_buckets ( p_forecast_header_id NUMBER,
894 p_start_date DATE) IS
895 CURSOR C_periods IS
896 SELECT forecast_header_id,
897 period_number,
898 level_of_summary,
899 length_of_period,
900 length_type,
901 created_by,
902 creation_date,
903 last_updated_by,
904 last_update_date,
905 last_update_login
906 FROM ce_forecast_periods
907 WHERE forecast_header_id = p_forecast_header_id
908 ORDER BY period_number;
909
910
911 l_period_number NUMBER;
912 l_level_of_summary VARCHAR2(1);
913 l_length_of_period NUMBER;
914 l_length_type VARCHAR2(1);
915
916 l_start_date DATE;
917 l_end_date DATE;
918
919 l_current_date DATE;
920 l_new_current_date DATE;
921
922 l_period_id NUMBER := 0;
923 l_count NUMBER := 0;
924 l_column_num NUMBER := 0;
925
926 l_days_from NUMBER := 1;
927 l_days_to NUMBER := 0;
928
929 BEGIN
930
931 /* BEGIN
932 DELETE FROM ce_forecast_columns
933 WHERE forecast_header_id = p_forecast_header_id
934 AND developer_column_num > 0;
935 IF SQL%FOUND THEN
936 COMMIT;
937 END IF;
938 EXCEPTION
939 WHEN OTHERS THEN
940 CEP_STANDARD.DEBUG('EXCEPTION:populate_temp_buckets-->delete');
941 RAISE;
942 END;
943 */
944
945 l_start_date := p_start_date;
946 l_start_date := TRUNC(l_start_date);
947 FOR p_rec IN C_periods LOOP
948
949 IF p_rec.length_type = 'D' THEN
950 l_end_date := l_start_date + p_rec.length_of_period - 1;
951 ELSIF p_rec.length_type = 'W' THEN
952 l_end_date := (l_start_date-1 + (p_rec.length_of_period * 7));
953 ELSIF p_rec.length_type = 'M' THEN
954 l_end_date := LAST_DAY(ADD_MONTHS(l_start_date,(p_rec.length_of_period-1)));
955 ELSE
956 l_end_date := LAST_DAY(ADD_MONTHS(l_start_date,((p_rec.length_of_period*12)-1)));
957 END IF;
958
959 IF p_rec.level_of_summary = 'D' THEN
960 l_count := l_end_date - l_start_date + 1;
961 l_current_date := l_start_date;
962 FOR i IN 1 .. l_count LOOP
963 l_column_num := l_column_num + 1;
964 l_days_to := l_days_from;
965
966 IF l_column_num <= 80 THEN
967 INSERT INTO ce_forecast_columns(forecast_column_id, forecast_header_id, column_number, days_from, days_to, developer_column_num, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
968 VALUES (ce_forecast_columns_s.nextval, p_rec.forecast_header_id, l_column_num, l_days_from, l_days_to, l_column_num, p_rec.created_by, p_rec.creation_date, p_rec.last_updated_by, p_rec.last_update_date, p_rec.last_update_login);
969 ELSE
970 --More than 80 columns
971 EXIT;
972 END IF;
973
974 l_days_from := l_days_to + 1;
975 l_current_date := l_current_date + 1;
976 END LOOP;
977
978 ELSIF p_rec.level_of_summary = 'W' THEN
979 l_current_date := l_start_date;
980 WHILE (l_current_date < l_end_date) LOOP
981 l_column_num := l_column_num + 1;
982 l_days_to := l_days_from + 6;
983
984 IF l_column_num <= 80 THEN
985 INSERT INTO ce_forecast_columns(forecast_column_id, forecast_header_id, column_number, days_from, days_to, developer_column_num, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
986 VALUES (ce_forecast_columns_s.nextval, p_rec.forecast_header_id, l_column_num, l_days_from, l_days_to, l_column_num, p_rec.created_by, p_rec.creation_date, p_rec.last_updated_by, p_rec.last_update_date, p_rec.last_update_login);
987 ELSE
988 --More than 80 columns
989 EXIT;
990 END IF;
991
992 l_days_from := l_days_to + 1;
993 l_current_date := l_current_date + 7;
994
995 END LOOP;
996 ELSIF p_rec.level_of_summary = 'M' THEN
997 l_current_date := l_start_date;
998 WHILE (l_current_date < l_end_date) LOOP
999 l_column_num := l_column_num + 1;
1000 l_days_to := l_days_from + TRUNC(LAST_DAY(l_current_date)) - l_current_date;
1001
1002 IF l_column_num <= 80 THEN
1003 INSERT INTO ce_forecast_columns(forecast_column_id, forecast_header_id, column_number, days_from, days_to, developer_column_num, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1004 VALUES (ce_forecast_columns_s.nextval, p_rec.forecast_header_id, l_column_num, l_days_from, l_days_to, l_column_num, p_rec.created_by, p_rec.creation_date, p_rec.last_updated_by, p_rec.last_update_date, p_rec.last_update_login);
1005 ELSE
1006 --More than 80 columns
1007 EXIT;
1008 END IF;
1009
1010 l_days_from := l_days_to + 1;
1011 l_current_date := TRUNC(ADD_MONTHS(l_current_date,1), 'MONTH');
1012
1013 END LOOP;
1014 END IF;
1015
1016 cep_standard.debug('Start Date: '||l_start_date);
1017 cep_standard.debug('End Date: '||l_end_date);
1018 l_start_date := l_current_date;
1019
1020 END LOOP;
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 IF C_periods%ISOPEN THEN CLOSE C_periods; END IF;
1025 CEP_STANDARD.DEBUG('EXCEPTION:populate_temp_buckets');
1026 raise;
1027 END populate_temp_buckets;
1028
1029
1030 /* ---------------------------------------------------------------------
1031 | PUBLIC PROCEDRE |
1032 | Submit_Forecast |
1033 | |
1034 | DESCRIPTION |
1035 | This procedure submits the forecast via a concurrent program |
1036 | |
1037 | CALLED BY |
1038 | OA Controller Classes |
1039 | REQUIRES |
1040 | |
1041 | HISTORY |
1042 | 20-MAY-2003 Created Sunil Poonen |
1043 --------------------------------------------------------------------- */
1044
1045 PROCEDURE Submit_Forecast(p_forecast_header_id IN NUMBER,
1046 p_forecast_name IN VARCHAR2,
1047 p_start_project_num IN VARCHAR2,
1048 p_end_project_num IN VARCHAR2,
1049 p_calendar_name IN VARCHAR2,
1050 p_forecast_start_date IN VARCHAR2,
1051 p_forecast_start_period IN VARCHAR2,
1052 p_forecast_currency IN VARCHAR2,
1053 p_src_curr_type IN VARCHAR2,
1054 p_src_currency IN VARCHAR2,
1055 p_exchange_date IN VARCHAR2,
1056 p_exchange_type IN VARCHAR2,
1057 p_exchange_rate IN NUMBER,
1058 p_amount_threshold IN NUMBER,
1059 p_rownum_from IN NUMBER,
1060 p_rownum_to IN NUMBER,
1061 p_sub_request IN VARCHAR2,
1062 p_factor IN NUMBER,
1063 p_include_sub_account IN VARCHAR2,
1064 p_view_by IN VARCHAR2,
1065 p_bank_balance_type IN VARCHAR2,
1066 p_float_type IN VARCHAR2,
1067 p_fc_name_exists IN VARCHAR2) IS
1068 l_forecast_rowid VARCHAR2(30);
1069 l_forecast_id NUMBER;
1070 l_aging_type VARCHAR2(1);
1071 l_request_id NUMBER;
1072 BEGIN
1073 SELECT aging_type
1074 INTO l_aging_type
1075 FROM ce_forecast_headers
1076 WHERE forecast_header_id = p_forecast_header_id;
1077
1078 IF (p_start_project_num is null AND p_end_project_num is null
1079 AND nvl(p_sub_request,'N') = 'N') THEN
1080 CE_FORECASTS_TABLE_PKG.Insert_Row(
1081 X_Rowid => l_forecast_rowid,
1082 X_forecast_id => l_forecast_id,
1083 X_forecast_header_id => p_forecast_header_id,
1084 X_name => p_forecast_name,
1085 X_description => null,
1086 X_start_date => to_date(p_forecast_start_date,'DD/MM/RRRR'),
1087 X_period_set_name => p_calendar_name,
1088 X_start_period => p_forecast_start_period,
1089 X_forecast_currency => p_forecast_currency,
1090 X_currency_type => p_src_curr_type,
1091 X_source_currency => p_src_currency,
1092 X_exchange_rate_type => p_exchange_type,
1093 X_exchange_date => to_date(p_exchange_date,'DD/MM/RRRR'),
1094 X_exchange_rate => p_exchange_rate,
1095 X_error_status => 'P',
1096 X_amount_threshold => p_amount_threshold,
1097 X_project_id => null,
1098 X_drilldown_flag => 'Y',
1099 X_bank_balance_type => p_bank_balance_type,
1100 X_float_type => p_float_type,
1101 X_view_by => p_view_by,
1102 X_include_sub_account => p_include_sub_account,
1103 X_factor => p_factor,
1104 X_request_id => null,
1105 X_created_by => nvl(fnd_global.user_id, -1),
1106 X_creation_date => sysdate,
1107 X_last_updated_by => nvl(fnd_global.user_id, -1),
1108 X_last_update_date => sysdate,
1109 X_last_update_login => nvl(fnd_global.user_id, -1),
1110 X_attribute_category => null,
1111 X_attribute1 => null,
1112 X_attribute2 => null,
1113 X_attribute3 => null,
1114 X_attribute4 => null,
1115 X_attribute5 => null,
1116 X_attribute6 => null,
1117 X_attribute7 => null,
1118 X_attribute8 => null,
1119 X_attribute9 => null,
1120 X_attribute10 => null,
1121 X_attribute11 => null,
1122 X_attribute12 => null,
1123 X_attribute13 => null,
1124 X_attribute14 => null,
1125 X_attribute15 => null);
1126 END IF;
1127
1128 IF l_aging_type = 'D' THEN
1129 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1130 'CE', 'CEFCSTBD','','',NULL,
1131 p_forecast_header_id,
1132 p_forecast_name,
1133 p_factor,
1134 p_start_project_num,
1135 p_end_project_num,
1136 p_calendar_name,
1137 to_char(to_date(p_forecast_start_date,'DD-MM-RRRR'),
1138 'YYYY/MM/DD HH24:MI:SS'),
1139 p_forecast_currency,
1140 p_src_curr_type,
1141 null,
1142 p_src_currency,
1143 to_char(to_date(p_exchange_date,'DD-MM-RRRR'),
1144 'YYYY/MM/DD HH24:MI:SS'),
1145 p_exchange_type,
1146 p_exchange_rate,
1147 p_rownum_from,
1148 p_rownum_to,
1149 p_amount_threshold,
1150 'N',
1151 p_view_by,
1152 null,
1153 p_bank_balance_type,
1154 p_float_type,
1155 p_include_sub_account,
1156 to_char(l_forecast_id),
1157 'N',
1158 null,
1159 null,
1160 p_fc_name_exists,
1161 fnd_global.local_chr(0),'',
1162 '','','','','','','','','','',
1163 '','','','','','','','','','',
1164 '','','','','','','','','','',
1165 '','','','','','','','','','',
1166 '','','','','','','','','','',
1167 '','','','','','','','','','');
1168 ELSE
1169 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1170 'CE', 'CEFCSHAP','','',NULL,
1171 p_forecast_header_id,
1172 p_forecast_name,
1173 p_factor,
1174 p_start_project_num,
1175 p_end_project_num,
1176 p_calendar_name,
1177 p_forecast_start_period,
1178 p_forecast_currency,
1179 p_src_curr_type,
1180 null,
1181 p_src_currency,
1182 to_char(to_date(p_exchange_date,'DD-MM-RRRR'),
1183 'YYYY/MM/DD HH24:MI:SS'),
1184 p_exchange_type,
1185 p_exchange_rate,
1186 p_rownum_from,
1187 p_rownum_to,
1188 p_amount_threshold,
1189 'N',
1190 p_view_by,
1191 null,
1192 p_bank_balance_type,
1193 p_float_type,
1194 p_include_sub_account,
1195 to_char(l_forecast_id),
1196 null,
1197 'N',
1198 null,
1199 null,
1200 p_fc_name_exists,
1201 fnd_global.local_chr(0),
1202 '','','','','','','','','','',
1203 '','','','','','','','','','',
1204 '','','','','','','','','','',
1205 '','','','','','','','','','',
1206 '','','','','','','','','','',
1207 '','','','','','','','','','',
1208 '','','','','','','','','','');
1209 END IF;
1210
1211 UPDATE ce_forecasts
1212 SET request_id = l_request_id
1213 WHERE forecast_id = l_forecast_id;
1214
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 cep_standard.debug('EXCEPTION: CE_FORECAST_UTILS.Submit_Forecast');
1218 RAISE;
1219
1220 END Submit_Forecast;
1221
1222
1223 /* ---------------------------------------------------------------------
1224 | PUBLIC PROCEDRE |
1225 | Refresh_Processing_Status |
1226 | |
1227 | DESCRIPTION |
1228 | This procedure refreshes the processing status of the forecast |
1229 | |
1230 | CALLED BY |
1231 | OA Controller Classes |
1232 | REQUIRES |
1233 | |
1234 | HISTORY |
1235 | 12-MAY-2003 Created Sunil Poonen |
1236 --------------------------------------------------------------------- */
1237
1238 PROCEDURE Refresh_Processing_Status IS
1239 CURSOR C_fc IS SELECT forecast_id,
1240 request_id
1241 FROM ce_forecasts
1242 WHERE error_status in ('P','R','X')
1243 AND request_id is not null;
1244 call_status BOOLEAN;
1245 rphase VARCHAR2(80);
1246 rstatus VARCHAR2(80);
1247 dphase VARCHAR2(30);
1248 dstatus VARCHAR2(30);
1249 message VARCHAR2(240);
1250 BEGIN
1251 FOR p_fc in C_fc LOOP
1252 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(p_fc.request_id,'','',
1253 rphase, rstatus, dphase, dstatus, message);
1254 IF (dstatus in ('ERROR','CANCELLED','TERMINATED')) THEN
1255 UPDATE ce_forecasts
1256 SET error_status = 'F'
1257 WHERE forecast_id = p_fc.forecast_id;
1258 END IF;
1259 IF (dphase = 'RUNNING') THEN
1260 UPDATE ce_forecasts
1261 SET error_status = 'R'
1262 WHERE forecast_id = p_fc.forecast_id
1263 AND error_status = 'P';
1264 END IF;
1265 END LOOP;
1266 END Refresh_Processing_Status;
1267
1268 /* ---------------------------------------------------------------------
1269 | PUBLIC FUNCTION |
1270 | FUNCTION IS_INSTALLED |
1271 | |
1272 | DESCRIPTION |
1273 | This function checks the installation status |
1274 | |
1275 | CALLED BY |
1276 | OA Controller Classes |
1277 | |
1278 | REQUIRES |
1279 | |
1280 | HISTORY |
1281 | 24-MAY-2003 (2am) Created Helen Han |
1282 --------------------------------------------------------------------- */
1283
1284 FUNCTION IS_INSTALLED(X_prod_id number) RETURN VARCHAR2 IS
1285 l_temp BOOLEAN;
1286 l_status VARCHAR2(1);
1287 l_dummy VARCHAR2(100);
1288 BEGIN
1289 l_temp := FND_INSTALLATION.get(X_prod_id, X_prod_id, l_status, l_dummy);
1290 if (l_status <> 'I') then
1291 return 'N';
1292 else
1293 return 'Y';
1294 end if;
1295 END IS_INSTALLED;
1296
1297
1298 /* ---------------------------------------------------------------------
1299 | PUBLIC FUNCTION |
1300 | XTR_USER |
1301 | |
1302 | DESCRIPTION |
1303 | This function checks whether the current user |
1304 | is a Treasury User |
1305 | |
1306 | CALLED BY |
1307 | CHECK_SECURITY |
1308 | |
1309 | REQUIRES |
1310 | |
1311 | HISTORY |
1312 | 27-FEB-2002 Created Sunil Poonen |
1313 --------------------------------------------------------------------- */
1314
1315 FUNCTION XTR_USER RETURN NUMBER IS
1316 l_cnt number;
1317 BEGIN
1318 select count(1)
1319 into l_cnt
1320 from xtr_dealer_codes
1321 where user_id = fnd_global.user_id;
1322
1323 if l_cnt = 0 then
1324 return 0;
1325 else
1326 return 1;
1327 end if;
1328 END XTR_USER;
1329
1330 /* ---------------------------------------------------------------------
1331 | PUBLIC FUNCTION |
1332 | CHECK_SECURITY |
1333 | |
1334 | DESCRIPTION |
1335 | This function enforces Treasury's Legal Entity security |
1336 | |
1337 | CALLED BY |
1338 | Forecasting Results views |
1339 | |
1340 | REQUIRES |
1341 | Legal Enitity ID |
1342 | HISTORY |
1343 | 27-FEB-2002 Created Sunil Poonen |
1344 --------------------------------------------------------------------- */
1345
1346 FUNCTION CHECK_SECURITY (X_le_id NUMBER) RETURN NUMBER IS
1347 l_user_id number;
1348 l_cnt number;
1349 BEGIN
1350 if (XTR_USER = 0 OR X_le_id is null) then -- not an XTR user
1351 return 1;
1352 end if;
1353
1354 select count(1)
1355 into l_cnt
1356 from xtr_parties_v
1357 where legal_entity_id = X_le_id;
1358
1359 if l_cnt = 0 then
1360 return 0;
1361 else
1362 return 1;
1363 end if;
1364 END CHECK_SECURITY;
1365
1366 PROCEDURE populate_dev_columns ( p_forecast_header_id NUMBER) IS
1367
1368 cursor col_cur (hid number) is
1369 select rowid, forecast_column_id, forecast_header_id, column_number
1370 from ce_forecast_columns
1371 where forecast_header_id = hid
1372 order by column_number
1373 for update nowait;
1374
1375 TYPE RowIDTab IS TABLE OF VARCHAR2(18) INDEX BY BINARY_INTEGER;
1376 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1377 l_rowid RowIDTab;
1378 l_hid Num15Tab;
1379 l_cid Num15Tab;
1380 l_col_num Num15Tab;
1381 l_dev_num Num15Tab;
1382 ct NUMBER;
1383
1384 BEGIN
1385
1386 if p_forecast_header_id is not null then
1387 OPEN col_cur(p_forecast_header_id);
1388 l_rowid.delete;
1389 l_dev_num.delete;
1390 l_cid.delete;
1391 l_hid.delete;
1392 l_col_num.delete;
1393 ct := 0;
1394
1395 FETCH col_cur BULK COLLECT INTO
1396 l_rowid, l_cid, l_hid, l_col_num;
1397
1398 if (l_cid.count > 0) then
1399
1400 for i in l_cid.first..l_cid.last
1401 loop
1402 ct := ct+1;
1403 l_dev_num(i) := ct;
1404 end loop;
1405
1406 forall i in l_cid.first..l_cid.last
1407 update ce_forecast_columns
1408 set developer_column_num = l_dev_num(i)
1409 where forecast_column_id = l_cid(i);
1410
1411 end if;
1412
1413 CLOSE col_cur;
1414
1415 end if;
1416
1417 END populate_dev_columns;
1418
1419
1420 END CE_FORECAST_UTILS;