[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_UTILS
Source
1 PACKAGE BODY CE_FORECAST_UTILS as
2 /* $Header: cefutilb.pls 120.3.12020000.2 2013/02/19 11:46:51 ckansara 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,
1068 -- Bug 13903877 Start
1069 p_attribute_category IN VARCHAR2,
1070 p_attribute1 IN VARCHAR2,
1071 p_attribute2 IN VARCHAR2,
1072 p_attribute3 IN VARCHAR2,
1073 p_attribute4 IN VARCHAR2,
1074 p_attribute5 IN VARCHAR2,
1075 p_attribute6 IN VARCHAR2,
1076 p_attribute7 IN VARCHAR2,
1077 p_attribute8 IN VARCHAR2,
1078 p_attribute9 IN VARCHAR2,
1079 p_attribute10 IN VARCHAR2,
1080 p_attribute11 IN VARCHAR2,
1081 p_attribute12 IN VARCHAR2,
1082 p_attribute13 IN VARCHAR2,
1083 p_attribute14 IN VARCHAR2,
1084 p_attribute15 IN VARCHAR2
1085 -- Bug 13903877 End
1086 ) IS
1087
1088 l_forecast_rowid VARCHAR2(30);
1089 l_forecast_id NUMBER;
1090 l_aging_type VARCHAR2(1);
1091 l_request_id NUMBER;
1092 BEGIN
1093 SELECT aging_type
1094 INTO l_aging_type
1095 FROM ce_forecast_headers
1096 WHERE forecast_header_id = p_forecast_header_id;
1097
1098 IF (p_start_project_num is null AND p_end_project_num is null
1099 AND nvl(p_sub_request,'N') = 'N') THEN
1100 CE_FORECASTS_TABLE_PKG.Insert_Row(
1101 X_Rowid => l_forecast_rowid,
1102 X_forecast_id => l_forecast_id,
1103 X_forecast_header_id => p_forecast_header_id,
1104 X_name => p_forecast_name,
1105 X_description => null,
1106 X_start_date => to_date(p_forecast_start_date,'DD/MM/RRRR'),
1107 X_period_set_name => p_calendar_name,
1108 X_start_period => p_forecast_start_period,
1109 X_forecast_currency => p_forecast_currency,
1110 X_currency_type => p_src_curr_type,
1111 X_source_currency => p_src_currency,
1112 X_exchange_rate_type => p_exchange_type,
1113 X_exchange_date => to_date(p_exchange_date,'DD/MM/RRRR'),
1114 X_exchange_rate => p_exchange_rate,
1115 X_error_status => 'P',
1116 X_amount_threshold => p_amount_threshold,
1117 X_project_id => null,
1118 X_drilldown_flag => 'Y',
1119 X_bank_balance_type => p_bank_balance_type,
1120 X_float_type => p_float_type,
1121 X_view_by => p_view_by,
1122 X_include_sub_account => p_include_sub_account,
1123 X_factor => p_factor,
1124 X_request_id => null,
1125 X_created_by => nvl(fnd_global.user_id, -1),
1126 X_creation_date => sysdate,
1127 X_last_updated_by => nvl(fnd_global.user_id, -1),
1128 X_last_update_date => sysdate,
1129 X_last_update_login => nvl(fnd_global.user_id, -1),
1130 X_attribute_category => p_attribute_category, -- Bug 13903877
1131 X_attribute1 => p_attribute1,
1132 X_attribute2 => p_attribute2,
1133 X_attribute3 => p_attribute3,
1134 X_attribute4 => p_attribute4,
1135 X_attribute5 => p_attribute5,
1136 X_attribute6 => p_attribute6,
1137 X_attribute7 => p_attribute7,
1138 X_attribute8 => p_attribute8,
1139 X_attribute9 => p_attribute9,
1140 X_attribute10 => p_attribute10,
1141 X_attribute11 => p_attribute11,
1142 X_attribute12 => p_attribute12,
1143 X_attribute13 => p_attribute13,
1144 X_attribute14 => p_attribute14,
1145 X_attribute15 => p_attribute15); -- Bug 13903877
1146 END IF;
1147
1148 IF l_aging_type = 'D' THEN
1149 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1150 'CE', 'CEFCSTBD','','',NULL,
1151 p_forecast_header_id,
1152 p_forecast_name,
1153 p_factor,
1154 p_start_project_num,
1155 p_end_project_num,
1156 p_calendar_name,
1157 to_char(to_date(p_forecast_start_date,'DD-MM-RRRR'),
1158 'YYYY/MM/DD HH24:MI:SS'),
1159 p_forecast_currency,
1160 p_src_curr_type,
1161 null,
1162 p_src_currency,
1163 to_char(to_date(p_exchange_date,'DD-MM-RRRR'),
1164 'YYYY/MM/DD HH24:MI:SS'),
1165 p_exchange_type,
1166 p_exchange_rate,
1167 p_rownum_from,
1168 p_rownum_to,
1169 p_amount_threshold,
1170 'N',
1171 p_view_by,
1172 null,
1173 p_bank_balance_type,
1174 p_float_type,
1175 p_include_sub_account,
1176 to_char(l_forecast_id),
1177 'N',
1178 null,
1179 null,
1180 p_fc_name_exists,
1181 fnd_global.local_chr(0),'',
1182 '','','','','','','','','','',
1183 '','','','','','','','','','',
1184 '','','','','','','','','','',
1185 '','','','','','','','','','',
1186 '','','','','','','','','','',
1187 '','','','','','','','','','');
1188 ELSE
1189 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1190 'CE', 'CEFCSHAP','','',NULL,
1191 p_forecast_header_id,
1192 p_forecast_name,
1193 p_factor,
1194 p_start_project_num,
1195 p_end_project_num,
1196 p_calendar_name,
1197 p_forecast_start_period,
1198 p_forecast_currency,
1199 p_src_curr_type,
1200 null,
1201 p_src_currency,
1202 to_char(to_date(p_exchange_date,'DD-MM-RRRR'),
1203 'YYYY/MM/DD HH24:MI:SS'),
1204 p_exchange_type,
1205 p_exchange_rate,
1206 p_rownum_from,
1207 p_rownum_to,
1208 p_amount_threshold,
1209 'N',
1210 p_view_by,
1211 null,
1212 p_bank_balance_type,
1213 p_float_type,
1214 p_include_sub_account,
1215 to_char(l_forecast_id),
1216 null,
1217 'N',
1218 null,
1219 null,
1220 p_fc_name_exists,
1221 fnd_global.local_chr(0),
1222 '','','','','','','','','','',
1223 '','','','','','','','','','',
1224 '','','','','','','','','','',
1225 '','','','','','','','','','',
1226 '','','','','','','','','','',
1227 '','','','','','','','','','',
1228 '','','','','','','','','','');
1229 END IF;
1230
1231 UPDATE ce_forecasts
1232 SET request_id = l_request_id
1233 WHERE forecast_id = l_forecast_id;
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 cep_standard.debug('EXCEPTION: CE_FORECAST_UTILS.Submit_Forecast');
1238 RAISE;
1239
1240 END Submit_Forecast;
1241
1242
1243 /* ---------------------------------------------------------------------
1244 | PUBLIC PROCEDRE |
1245 | Refresh_Processing_Status |
1246 | |
1247 | DESCRIPTION |
1248 | This procedure refreshes the processing status of the forecast |
1249 | |
1250 | CALLED BY |
1251 | OA Controller Classes |
1252 | REQUIRES |
1253 | |
1254 | HISTORY |
1255 | 12-MAY-2003 Created Sunil Poonen |
1256 --------------------------------------------------------------------- */
1257
1258 PROCEDURE Refresh_Processing_Status IS
1259 CURSOR C_fc IS SELECT forecast_id,
1260 request_id
1261 FROM ce_forecasts
1262 WHERE error_status in ('P','R','X')
1263 AND request_id is not null;
1264 call_status BOOLEAN;
1265 rphase VARCHAR2(80);
1266 rstatus VARCHAR2(80);
1267 dphase VARCHAR2(30);
1268 dstatus VARCHAR2(30);
1269 message VARCHAR2(240);
1270 BEGIN
1271 FOR p_fc in C_fc LOOP
1272 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(p_fc.request_id,'','',
1273 rphase, rstatus, dphase, dstatus, message);
1274 IF (dstatus in ('ERROR','CANCELLED','TERMINATED')) THEN
1275 UPDATE ce_forecasts
1276 SET error_status = 'F'
1277 WHERE forecast_id = p_fc.forecast_id;
1278 END IF;
1279 IF (dphase = 'RUNNING') THEN
1280 UPDATE ce_forecasts
1281 SET error_status = 'R'
1282 WHERE forecast_id = p_fc.forecast_id
1283 AND error_status = 'P';
1284 END IF;
1285 END LOOP;
1286 END Refresh_Processing_Status;
1287
1288 /* ---------------------------------------------------------------------
1289 | PUBLIC FUNCTION |
1290 | FUNCTION IS_INSTALLED |
1291 | |
1292 | DESCRIPTION |
1293 | This function checks the installation status |
1294 | |
1295 | CALLED BY |
1296 | OA Controller Classes |
1297 | |
1298 | REQUIRES |
1299 | |
1300 | HISTORY |
1301 | 24-MAY-2003 (2am) Created Helen Han |
1302 --------------------------------------------------------------------- */
1303
1304 FUNCTION IS_INSTALLED(X_prod_id number) RETURN VARCHAR2 IS
1305 l_temp BOOLEAN;
1306 l_status VARCHAR2(1);
1307 l_dummy VARCHAR2(100);
1308 BEGIN
1309 l_temp := FND_INSTALLATION.get(X_prod_id, X_prod_id, l_status, l_dummy);
1310 if (l_status <> 'I') then
1311 return 'N';
1312 else
1313 return 'Y';
1314 end if;
1315 END IS_INSTALLED;
1316
1317
1318 /* ---------------------------------------------------------------------
1319 | PUBLIC FUNCTION |
1320 | XTR_USER |
1321 | |
1322 | DESCRIPTION |
1323 | This function checks whether the current user |
1324 | is a Treasury User |
1325 | |
1326 | CALLED BY |
1327 | CHECK_SECURITY |
1328 | |
1329 | REQUIRES |
1330 | |
1331 | HISTORY |
1332 | 27-FEB-2002 Created Sunil Poonen |
1333 --------------------------------------------------------------------- */
1334
1335 FUNCTION XTR_USER RETURN NUMBER IS
1336 l_cnt number;
1337 BEGIN
1338 select count(1)
1339 into l_cnt
1340 from xtr_dealer_codes
1341 where user_id = fnd_global.user_id;
1342
1343 if l_cnt = 0 then
1344 return 0;
1345 else
1346 return 1;
1347 end if;
1348 END XTR_USER;
1349
1350 /* ---------------------------------------------------------------------
1351 | PUBLIC FUNCTION |
1352 | CHECK_SECURITY |
1353 | |
1354 | DESCRIPTION |
1355 | This function enforces Treasury's Legal Entity security |
1356 | |
1357 | CALLED BY |
1358 | Forecasting Results views |
1359 | |
1360 | REQUIRES |
1361 | Legal Enitity ID |
1362 | HISTORY |
1363 | 27-FEB-2002 Created Sunil Poonen |
1364 --------------------------------------------------------------------- */
1365
1366 FUNCTION CHECK_SECURITY (X_le_id NUMBER) RETURN NUMBER IS
1367 l_user_id number;
1368 l_cnt number;
1369 BEGIN
1370 if (XTR_USER = 0 OR X_le_id is null) then -- not an XTR user
1371 return 1;
1372 end if;
1373
1374 select count(1)
1375 into l_cnt
1376 from xtr_parties_v
1377 where legal_entity_id = X_le_id;
1378
1379 if l_cnt = 0 then
1380 return 0;
1381 else
1382 return 1;
1383 end if;
1384 END CHECK_SECURITY;
1385
1386 PROCEDURE populate_dev_columns ( p_forecast_header_id NUMBER) IS
1387
1388 cursor col_cur (hid number) is
1389 select rowid, forecast_column_id, forecast_header_id, column_number
1390 from ce_forecast_columns
1391 where forecast_header_id = hid
1392 and column_number <> 0 -- Bug 8998714
1393 order by column_number
1394 for update nowait;
1395
1396 TYPE RowIDTab IS TABLE OF VARCHAR2(18) INDEX BY BINARY_INTEGER;
1397 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1398 l_rowid RowIDTab;
1399 l_hid Num15Tab;
1400 l_cid Num15Tab;
1401 l_col_num Num15Tab;
1402 l_dev_num Num15Tab;
1403 ct NUMBER;
1404
1405 BEGIN
1406
1407 if p_forecast_header_id is not null then
1408 OPEN col_cur(p_forecast_header_id);
1409 l_rowid.delete;
1410 l_dev_num.delete;
1411 l_cid.delete;
1412 l_hid.delete;
1413 l_col_num.delete;
1414 ct := 0;
1415
1416 FETCH col_cur BULK COLLECT INTO
1417 l_rowid, l_cid, l_hid, l_col_num;
1418
1419 if (l_cid.count > 0) then
1420
1421 for i in l_cid.first..l_cid.last
1422 loop
1423 ct := ct+1;
1424 l_dev_num(i) := ct;
1425 end loop;
1426
1427 forall i in l_cid.first..l_cid.last
1428 update ce_forecast_columns
1429 set developer_column_num = l_dev_num(i)
1430 where forecast_column_id = l_cid(i);
1431
1432 end if;
1433
1434 CLOSE col_cur;
1435
1436 end if;
1437
1438 END populate_dev_columns;
1439
1440
1441 END CE_FORECAST_UTILS;