[Home] [Help]
PACKAGE BODY: APPS.CE_FORECASTS_PKG
Source
1 PACKAGE BODY CE_FORECASTS_PKG AS
2 /* $Header: cefcastb.pls 120.4 2003/07/31 23:44:32 sspoonen ship $ */
3
4 FUNCTION body_revision RETURN VARCHAR2 IS
5 BEGIN
6
7 RETURN '$Revision: 120.4 $';
8
9 END body_revision;
10
11 FUNCTION spec_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN G_spec_revision;
15
16 END spec_revision;
17
18 PROCEDURE set_factor(X_factor NUMBER) IS
19 BEGIN
20 CE_FORECASTS_PKG.G_factor := X_factor;
21 END;
22
23 FUNCTION get_factor RETURN NUMBER IS
24 BEGIN
25 return (CE_FORECASTS_PKG.G_factor);
26 END;
27
28 PROCEDURE create_empty_forecast(X_rowid IN OUT NOCOPY VARCHAR2,
29 X_forecast_id IN OUT NOCOPY NUMBER,
30 X_forecast_header_id NUMBER,
31 X_forecast_name VARCHAR2,
32 X_forecast_dsp VARCHAR2,
33 X_start_date DATE,
34 X_period_set_name VARCHAR2,
35 X_start_period VARCHAR2,
36 X_forecast_currency VARCHAR2,
37 X_currency_type VARCHAR2,
38 X_source_currency VARCHAR2,
39 X_exchange_rate_type VARCHAR2,
40 X_exchange_date DATE,
41 X_exchange_rate NUMBER,
42 X_amount_threshold NUMBER,
43 X_project_id NUMBER,
44 X_created_by NUMBER,
45 X_creation_date DATE,
46 X_last_updated_by NUMBER,
47 X_last_update_date DATE,
48 X_last_update_login NUMBER) IS
49 cursor cr is select forecast_row_id, trx_type
50 from ce_forecast_rows
51 where forecast_header_id = X_forecast_header_id
52 order by row_number;
53 cursor cc is select forecast_column_id
54 from ce_forecast_columns
55 where forecast_header_id = X_forecast_header_id
56 order by column_number;
57 cid number;
58 rid number;
59 cell_id number;
60 rcount number;
61 ccount number;
62 num_rows number;
63 num_cols number;
64 p_rowid VARCHAR2(100);
65 trx ce_forecast_rows.trx_type%TYPE;
66 glc_rowid number default 0;
67
68 BEGIN
69 --
70 -- Insert new forecast into forecast table
71 --
72 CE_FORECASTS_TABLE_PKG.insert_row(
73 X_rowid => X_rowid,
74 X_forecast_id => X_forecast_id,
75 X_forecast_header_id => X_forecast_header_id,
76 X_name => X_forecast_name,
77 X_description => X_forecast_dsp,
78 X_start_date => X_start_date,
79 X_period_set_name => X_period_set_name,
80 X_start_period => X_start_period,
81 X_forecast_currency => X_forecast_currency,
82 X_currency_type => X_currency_type,
83 X_source_currency => X_source_currency,
84 X_exchange_rate_type => X_exchange_rate_type,
85 X_exchange_date => X_exchange_date,
86 X_exchange_rate => X_exchange_rate,
87 X_error_status => 'S',
88 X_amount_threshold => X_amount_threshold,
89 X_project_id => X_project_id,
90 X_drilldown_flag => null,
91 X_bank_balance_type => null,
92 X_float_type => null,
93 X_view_by => null,
94 X_include_sub_account => null,
95 X_factor => 0,
96 X_request_id => null,
97 X_created_by => X_created_by,
98 X_creation_date => X_creation_date,
99 X_last_updated_by => X_last_updated_by,
100 X_last_update_date => X_last_update_date,
101 X_last_update_login => X_last_update_login,
102 X_attribute_category => null,
103 X_attribute1 => null,
104 X_attribute2 => null,
105 X_attribute3 => null,
106 X_attribute4 => null,
107 X_attribute5 => null,
108 X_attribute6 => null,
109 X_attribute7 => null,
110 X_attribute8 => null,
111 X_attribute9 => null,
112 X_attribute10 => null,
113 X_attribute11 => null,
114 X_attribute12 => null,
115 X_attribute13 => null,
116 X_attribute14 => null,
117 X_attribute15 => null);
118
119 select count(r.forecast_row_id)
120 into num_rows
121 from ce_forecast_rows r
122 where r.forecast_header_id = X_forecast_header_id;
123
124 select count(r.forecast_column_id)
125 into num_cols
126 from ce_forecast_columns r
127 where r.forecast_header_id = X_forecast_header_id;
128
129 --
130 -- Create cells for the forecast
131 --
132 open cr;
133 for rcount in 1..num_rows loop
134 fetch cr into rid, trx;
135 if (trx = 'GLC') then
136 glc_rowid := rid;
137 else
138 open cc;
139
140 for ccount in 1..num_cols loop
141 fetch cc into cid;
142 cell_id := null;
143 p_rowid := null;
144 CE_FORECAST_CELLS_PKG.insert_row(
145 X_Rowid => p_rowid,
146 X_forecast_cell_id => cell_id,
147 X_forecast_id => X_forecast_id,
148 X_forecast_header_id => X_forecast_header_id,
149 X_forecast_row_id => rid,
150 X_forecast_column_id => cid,
151 X_amount => 0,
152 X_Created_By => X_created_by,
153 X_Creation_Date => X_creation_date,
154 X_Last_Updated_By => X_last_updated_by,
155 X_Last_Update_Date => X_last_update_date,
156 X_Last_Update_Login => X_last_update_login);
157 end loop;
158 close cc;
159 end if;
160 end loop;
161 close cr;
162
163 /* Create one single row for all GLC amounts, using last GLC row id #. */
164
165 If (glc_rowid <> 0) then
166 open cc;
167 for ccount in 1..num_cols loop
168 fetch cc into cid;
169 cell_id := null;
170 CE_FORECAST_CELLS_PKG.insert_row(
171 X_Rowid => p_rowid,
172 X_forecast_cell_id => cell_id,
173 X_forecast_id => X_forecast_id,
174 X_forecast_header_id => X_forecast_header_id,
175 X_forecast_row_id => glc_rowid,
176 X_forecast_column_id => cid,
177 X_amount => 0,
178 X_Created_By => X_created_by,
179 X_Creation_Date => X_creation_date,
180 X_Last_Updated_By => X_last_updated_by,
181 X_Last_Update_Date => X_last_update_date,
182 X_Last_Update_Login => X_last_update_login);
183 end loop;
184 close cc;
185 end if;
186 EXCEPTION
187 WHEN OTHERS THEN
188 if (cc%ISOPEN) then close cc; end if;
189 if (cr%ISOPEN) then close cr; end if;
190 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.create_empty_forecast');
191 RAISE;
192 END;
193
194 PROCEDURE add_column( X_new_forecast VARCHAR2,
195 X_forecast_column_id IN OUT NOCOPY NUMBER,
196 X_forecast_header_id IN OUT NOCOPY NUMBER,
197 X_column_number NUMBER,
198 X_days_from NUMBER,
199 X_days_to NUMBER,
200 X_created_by NUMBER,
201 X_creation_date DATE,
202 X_last_updated_by NUMBER,
203 X_last_update_date DATE,
204 X_last_update_login NUMBER,
205 X_forecast_id NUMBER DEFAULT NULL,
206 X_name VARCHAR2 DEFAULT NULL) IS
207 cursor cr is select row_number
208 from ce_forecast_rows
209 where forecast_header_id = X_forecast_header_id
210 and trx_type = 'GLC';
211 p_rowid VARCHAR2(100);
212 p_amount NUMBER;
213 p_line_number NUMBER;
214 p_forecast_row_id NUMBER;
215 p_forecast_cell_id NUMBER;
216 p_last_column_id NUMBER;
217 p_developer_column_num NUMBER;
218 BEGIN
219 cep_standard.debug('>> CE_FORECASTS_PKG.add_column');
220 IF(X_new_forecast = 'Y')THEN
221 CE_FORECASTS_PKG.duplicate_template_header(
222 X_forecast_header_id => X_forecast_header_id,
223 X_created_by => X_created_by,
224 X_creation_date => X_creation_date,
225 X_last_updated_by => X_last_updated_by,
226 X_last_update_date => X_last_update_date,
227 X_last_update_login => X_last_update_login,
228 X_forecast_id => X_forecast_id,
229 X_name => X_name);
230 END IF;
231
232 cep_standard.debug(' - insert new column');
233 --
234 -- Insert new column into column table
235 --
236 CE_FORECAST_COLUMNS_PKG.insert_row(
237 X_rowid => p_rowid,
238 X_forecast_column_id => X_forecast_column_id,
239 X_forecast_header_id => X_forecast_header_id,
240 X_column_number => X_column_number,
241 X_days_from => X_days_from,
242 X_days_to => X_days_to,
243 X_developer_column_num => to_number(null),
244 X_created_by => X_created_by,
245 X_creation_date => X_creation_date,
246 X_last_updated_by => X_last_updated_by,
247 X_last_update_date => X_last_update_date,
248 X_last_update_login => X_last_update_login,
249 X_attribute_category => null,
250 X_attribute1 => null,
251 X_attribute2 => null,
252 X_attribute3 => null,
253 X_attribute4 => null,
254 X_attribute5 => null,
255 X_attribute6 => null,
256 X_attribute7 => null,
257 X_attribute8 => null,
258 X_attribute9 => null,
259 X_attribute10 => null,
260 X_attribute11 => null,
261 X_attribute12 => null,
262 X_attribute13 => null,
263 X_attribute14 => null,
264 X_attribute15 => null);
265
266 cep_standard.debug(' - insert new cells');
267 CE_FORECASTS_PKG.fill_cells( X_forecast_header_id,
268 'COLUMN',
269 X_forecast_column_id,
270 X_created_by,
271 X_creation_date,
272 X_last_updated_by,
273 X_last_update_date,
274 X_last_update_login );
275
276 cep_standard.debug(' - rearrange column number');
277 CE_FORECASTS_PKG.rearrange_column_number( X_forecast_header_id );
278
279 cep_standard.debug(' - arrange GLC line');
280 --
281 -- If template contains GLC line, copy the GLC amount from the last
282 -- column to the new column for GLC
283 --
284 select developer_column_num
285 into p_developer_column_num
286 from ce_forecast_columns
287 where forecast_column_id = X_forecast_column_id;
288
289 select forecast_column_id
290 into p_last_column_id
291 from ce_forecast_columns
292 where forecast_header_id = X_forecast_header_id and
293 developer_column_num = p_developer_column_num-1;
294
295 OPEN cr;
296 loop
297 fetch cr into p_line_number;
298 exit when cr%NOTFOUND;
299
300 select forecast_row_id
301 into p_forecast_row_id
302 from ce_forecast_rows
303 where forecast_header_id = X_forecast_header_id and
304 row_number = p_line_number;
305
306 select amount
307 into p_amount
308 from ce_forecast_cells
309 where forecast_header_id = X_forecast_header_id and
310 forecast_column_id = p_last_column_id and
311 forecast_row_id = p_forecast_row_id;
312
313 select forecast_cell_id
314 into p_forecast_cell_id
315 from ce_Forecast_cells
316 where forecast_header_id = X_forecast_header_id and
317 forecast_column_id = X_forecast_column_id and
318 forecast_row_id = p_forecast_row_id;
319
320 CE_FORECAST_CELLS_PKG.update_row(
321 X_CELLID => p_forecast_cell_id,
322 X_AMOUNT => p_amount,
323 X_LAST_UPDATED_BY => X_last_updated_by,
324 X_LAST_UPDATE_DATE => X_last_update_date,
325 X_LAST_UPDATE_LOGIN => X_last_update_login );
326 end loop;
327
328 --
329 -- Commit work
330 --
331 COMMIT;
332 cep_standard.debug('<< CE_FORECASTS_PKG.add_column');
333
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 null;
337 WHEN OTHERS THEN
338 IF SQLCODE <> -1422 THEN
339 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.add_column');
340 RAISE;
341 END IF;
342 END;
343
344 PROCEDURE add_row( X_new_forecast VARCHAR2,
345 X_forecast_row_id IN OUT NOCOPY NUMBER,
346 X_forecast_header_id IN OUT NOCOPY NUMBER,
347 X_row_number NUMBER,
348 X_trx_type VARCHAR2,
349 X_created_by NUMBER,
350 X_creation_date DATE,
351 X_last_updated_by NUMBER,
352 X_last_update_date DATE,
353 X_last_update_login NUMBER,
354 X_forecast_id NUMBER,
355 X_name VARCHAR2,
356 X_description VARCHAR2) IS
357 p_rowid varchar2(100);
358 BEGIN
359 IF(X_new_forecast = 'Y')THEN
360 CE_FORECASTS_PKG.duplicate_template_header(
361 X_forecast_header_id => X_forecast_header_id,
362 X_created_by => X_created_by,
363 X_creation_date => X_creation_date,
364 X_last_updated_by => X_last_updated_by,
365 X_last_update_date => X_last_update_date,
366 X_last_update_login => X_last_update_login,
367 X_forecast_id => X_forecast_id,
368 X_name => X_name);
369 CE_FORECASTS_PKG.rearrange_column_number(X_forecast_header_id);
370 END IF;
371
372 CE_FORECAST_ROWS1_PKG.Insert_Row(
373 X_rowid => p_rowid,
374 X_forecast_row_id => X_forecast_row_id,
375 X_forecast_header_id => X_forecast_header_id,
376 X_row_number => X_row_number,
377 X_trx_type => X_trx_type,
378 X_lead_time => to_number(null),
379 X_forecast_method => null,
380 X_discount_option => null,
381 X_order_status => null,
382 X_order_date_type => null,
383 X_code_combination_id => to_number(null),
384 X_set_of_books_id => to_number(null),
385 X_org_id => to_number(null),
386 X_chart_of_accounts_id => to_number(null),
387 X_budget_name => null,
388 X_budget_version_id => to_number(null),
389 X_encumbrance_type_id => to_number(null),
390 X_roll_forward_type => null,
391 X_roll_forward_period => to_number(null),
392 X_customer_profile_class_id => to_number(null),
393 X_include_dispute_flag => null,
394 X_sales_stage_id => to_number(null),
395 X_channel_code => null,
396 X_win_probability => to_number(null),
397 X_sales_forecast_status => null,
398 X_receipt_method_id => to_number(null),
399 X_bank_account_id => to_number(null),
400 X_payment_method => null,
401 X_pay_group => null,
402 X_payment_priority => to_number(null),
403 X_vendor_type => null,
404 X_authorization_status => null,
405 X_type => null,
406 X_budget_type => null,
407 X_budget_version => null,
408 X_include_hold_flag => null,
409 X_include_net_cash_flag => null,
410 X_created_by => X_created_by,
411 X_creation_date => X_creation_date,
412 X_last_updated_by => X_last_updated_by,
413 X_last_update_date => X_last_update_date,
414 X_last_update_login => X_last_update_login,
415 X_org_payment_method_id => to_number(null),
416 X_xtr_bank_account => null,
417 X_exclude_indic_exp => null,
418 X_company_code => null,
419 X_attribute_category => null,
420 X_attribute1 => null,
421 X_attribute2 => null,
422 X_attribute3 => null,
423 X_attribute4 => null,
424 X_attribute5 => null,
425 X_attribute6 => null,
426 X_attribute7 => null,
427 X_attribute8 => null,
428 X_attribute9 => null,
429 X_attribute10 => null,
430 X_attribute11 => null,
431 X_attribute12 => null,
432 X_attribute13 => null,
433 X_attribute14 => null,
434 X_attribute15 => null,
435 X_description => X_description,
436 X_payroll_id => to_number(null),
437 X_external_source_type => null,
438 X_criteria_category => null,
439 X_criteria1 => null,
440 X_criteria2 => null,
441 X_criteria3 => null,
442 X_criteria4 => null,
443 X_criteria5 => null,
444 X_criteria6 => null,
445 X_criteria7 => null,
446 X_criteria8 => null,
447 X_criteria9 => null,
448 X_criteria10 => null,
449 X_criteria11 => null,
450 X_criteria12 => null,
451 X_criteria13 => null,
452 X_criteria14 => null,
453 X_criteria15 => null,
454 X_use_average_payment_days
455 => null,
456 X_period => to_number(null),
457 X_order_type_id => to_number(null),
458 X_use_payment_terms => null);
459
460 CE_FORECASTS_PKG.fill_cells( X_forecast_header_id,
461 'ROW',
462 X_forecast_row_id,
463 X_created_by,
464 X_creation_date,
465 X_last_updated_by,
466 X_last_update_date,
467 X_last_update_login );
468 --
469 -- Commit work
470 --
471 COMMIT;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.add_row');
476 RAISE;
477 END;
478
479 PROCEDURE fill_cells( X_header_id NUMBER,
480 X_col_or_row VARCHAR2,
481 X_new_id NUMBER,
482 X_created_by NUMBER,
483 X_creation_date DATE,
484 X_last_updated_by NUMBER,
485 X_last_update_date DATE,
486 X_last_update_login NUMBER) IS
487 cursor cf is select forecast_id
488 from ce_forecasts
489 where forecast_header_id = X_header_id;
490 cursor cr(ffid number) is
491 select distinct(forecast_row_id)
492 from ce_forecast_cells
493 where forecast_id = ffid;
494 cursor cc is select forecast_column_id
495 from ce_forecast_columns
496 where forecast_header_id = X_header_id;
497 p_rowid varchar2(100);
498 cid number;
499 rid number;
500 fid number;
501 cell_id number;
502 ccount number;
503 fcount number;
504 BEGIN
505 IF( X_col_or_row = 'COLUMN' )THEN
506 cid := X_new_id;
507 ELSE
508 rid := X_new_id;
509 END IF;
510
511 open cf;
512 loop
513 fetch cf into fid;
514 EXIT WHEN cf%NOTFOUND or cf%NOTFOUND IS NULL;
515 if( X_col_or_row = 'COLUMN' ) THEN
516 open cr(fid);
517 else
518 open cc;
519 end if;
520 loop
521 if( X_col_or_row = 'COLUMN')THEN
522 fetch cr into rid;
523 EXIT WHEN cr%NOTFOUND or cr%NOTFOUND IS NULL;
524 else
525 fetch cc into cid;
526 EXIT WHEN cc%NOTFOUND or cc%NOTFOUND IS NULL;
527 end if;
528 cell_id := null;
529 CE_FORECAST_CELLS_PKG.insert_row(
530 X_Rowid => p_rowid,
531 X_forecast_cell_id => cell_id,
532 X_forecast_id => fid,
533 X_forecast_header_id => X_header_id,
534 X_forecast_row_id => rid,
535 X_forecast_column_id => cid,
536 X_amount => 0,
537 X_Created_By => X_created_by,
538 X_Creation_Date => X_creation_date,
539 X_Last_Updated_By => X_last_updated_by,
540 X_Last_Update_Date => X_last_update_date,
541 X_Last_Update_Login => X_last_update_login);
542 end loop;
543 if( X_col_or_row = 'COLUMN')THEN
544 close cr;
545 else
546 close cc;
547 end if;
548 end loop;
549 close cf;
550
551 EXCEPTION
552 WHEN OTHERS THEN
553 if (cc%ISOPEN) then close cc; end if;
554 if (cr%ISOPEN) then close cr; end if;
555 if (cf%ISOPEN) then close cf; end if;
556 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.add_row');
557 RAISE;
558 END;
559
560 PROCEDURE rearrange_column_number( X_forecast_header_id NUMBER ) IS
561 CURSOR cc IS select forecast_column_id
562 from ce_forecast_columns
563 where forecast_header_id = X_forecast_header_id
564 and column_number <> 0
565 order by column_number;
566 cid NUMBER;
567 n NUMBER;
568 begin
569 n := 0;
570 open cc;
571 loop
572 fetch cc into cid;
573 n := n + 1;
574 EXIT WHEN cc%NOTFOUND or cc%NOTFOUND IS NULL;
575 UPDATE ce_forecast_columns
576 SET developer_column_num = n
577 WHERE forecast_column_id = cid;
578 end loop;
579 close cc;
580
581 --
582 -- Commit work
583 --
584 COMMIT;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 if (cc%ISOPEN) then close cc; end if;
589 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.rearrage_column_number');
590 RAISE;
591 END;
592
593 PROCEDURE duplicate_template_header(
594 X_forecast_header_id IN OUT NOCOPY NUMBER,
595 X_created_by NUMBER,
596 X_creation_date DATE,
597 X_last_updated_by NUMBER,
598 X_last_update_date DATE,
599 X_last_update_login NUMBER,
600 X_forecast_id NUMBER,
601 X_name VARCHAR2) IS
602 p_rowid VARCHAR2(100);
603 cid NUMBER;
604 rid NUMBER;
605 p_forecast_header_id NUMBER;
606 CURSOR CH IS SELECT *
607 FROM CE_FORECAST_HEADERS
608 WHERE forecast_header_id = X_forecast_header_id;
609 HdrInfo CH%ROWTYPE;
610 CURSOR CC IS SELECT *
611 FROM CE_FORECAST_COLUMNS
612 WHERE forecast_header_id = X_forecast_header_id
613 AND developer_column_num <> 0;
614 ColInfo CC%ROWTYPE;
615 CURSOR CR IS SELECT *
616 FROM CE_FORECAST_ROWS
617 WHERE forecast_header_id = X_forecast_header_id;
618 RowInfo CR%ROWTYPE;
619
620 BEGIN
621 cep_standard.debug('>> CE_FORECASTS_PKG.duplicate_forecast_header');
622 --
623 -- Create duplicate header
624 --
625 open CH;
626 fetch CH into HdrInfo;
627 IF (CH%NOTFOUND) THEN
628 Raise NO_DATA_FOUND;
629 END IF;
630
631 cep_standard.debug(' - insert header');
632 CE_FORECAST_HEADERS_PKG.insert_row(
633 X_rowid => p_rowid,
634 X_forecast_header_id => p_forecast_header_id,
635 X_name => X_name,
636 X_description => null,
637 X_aging_type => HdrInfo.aging_type,
638 X_overdue_transactions => HdrInfo.overdue_transactions,
639 X_cutoff_period => HdrInfo.cutoff_period,
640 X_transaction_calendar_id => HdrInfo.transaction_calendar_id,
641 X_start_project_id => HdrInfo.start_project_id,
642 X_end_project_id => HdrInfo.end_project_id,
643 X_treasury_template => HdrInfo.treasury_template,
644 X_created_by => X_created_by,
645 X_creation_date => X_creation_date,
646 X_last_updated_by => X_last_updated_by,
647 X_last_update_date => X_last_update_date,
648 X_last_update_login => X_last_update_login,
649 X_attribute_category => HdrInfo.attribute_category,
650 X_attribute1 => HdrInfo.attribute1,
651 X_attribute2 => HdrInfo.attribute2,
652 X_attribute3 => HdrInfo.attribute3,
653 X_attribute4 => HdrInfo.attribute4,
654 X_attribute5 => HdrInfo.attribute5,
655 X_attribute6 => HdrInfo.attribute6,
656 X_attribute7 => HdrInfo.attribute7,
657 X_attribute8 => HdrInfo.attribute8,
658 X_attribute9 => HdrInfo.attribute9,
659 X_attribute10 => HdrInfo.attribute10,
660 X_attribute11 => HdrInfo.attribute10,
661 X_attribute12 => HdrInfo.attribute12,
662 X_attribute13 => HdrInfo.attribute13,
663 X_attribute14 => HdrInfo.attribute14,
664 X_attribute15 => HdrInfo.attribute15);
665
666 close CH;
667 cep_standard.debug(' DONE inserted header with id = '||to_char(p_forecast_header_id));
668
669 --
670 -- Update forecast and forecast cells with new forecast_header_id
671 --
672 UPDATE CE_FORECAST_CELLS
673 SET forecast_header_id = p_forecast_header_id
674 WHERE forecast_id = X_forecast_id;
675
676 UPDATE CE_FORECASTS
677 SET forecast_header_id = p_forecast_header_id
678 WHERE forecast_id = X_forecast_id;
679
680 --
681 -- Create duplicate columns definition with new header id
682 --
683 open CC;
684 fetch CC into ColInfo;
685 WHILE (CC%FOUND) LOOP
686 cep_standard.debug(' - insert column, hid = '||to_char(p_forecast_header_id)||', col num= '
687 ||to_char(ColInfo.column_number));
688 cid := NULL;
689 CE_FORECAST_COLUMNS_PKG.insert_row(
690 X_rowid => p_rowid,
691 X_forecast_column_id => cid,
692 X_forecast_header_id => p_forecast_header_id,
693 X_column_number => ColInfo.column_number,
694 X_days_from => ColInfo.days_from,
695 X_days_to => ColInfo.days_to,
696 X_developer_column_num => to_number(null),
697 X_created_by => X_created_by,
698 X_creation_date => X_creation_date,
699 X_last_updated_by => X_last_updated_by,
700 X_last_update_date => X_last_update_date,
701 X_last_update_login => X_last_update_login,
702 X_attribute_category => ColInfo.attribute_category,
703 X_attribute1 => ColInfo.attribute1,
704 X_attribute2 => ColInfo.attribute2,
705 X_attribute3 => ColInfo.attribute3,
706 X_attribute4 => ColInfo.attribute4,
707 X_attribute5 => ColInfo.attribute5,
708 X_attribute6 => ColInfo.attribute6,
709 X_attribute7 => ColInfo.attribute7,
710 X_attribute8 => ColInfo.attribute8,
711 X_attribute9 => ColInfo.attribute9,
712 X_attribute10 => ColInfo.attribute10,
713 X_attribute11 => ColInfo.attribute11,
714 X_attribute12 => ColInfo.attribute12,
715 X_attribute13 => ColInfo.attribute13,
716 X_attribute14 => ColInfo.attribute14,
717 X_attribute15 => ColInfo.attribute15);
718
719 UPDATE CE_FORECAST_CELLS
720 SET forecast_column_id = cid
721 WHERE forecast_id = X_forecast_id AND
722 forecast_column_id = ColInfo.forecast_column_id;
723
724 fetch CC into ColInfo;
725 END LOOP;
726 close CC;
727
728 --
729 -- Create duplicate rows definition with new header id
730 --
731 open CR;
732 fetch CR into RowInfo;
733 WHILE (CR%FOUND) LOOP
734 cep_standard.debug(' - insert row');
735 rid := NULL;
736 CE_FORECAST_ROWS1_PKG.Insert_Row(
737 X_rowid => p_rowid,
738 X_forecast_row_id => rid,
739 X_forecast_header_id => p_forecast_header_id,
740 X_row_number => RowInfo.row_number,
741 X_trx_type => RowInfo.trx_type,
742 X_lead_time => RowInfo.lead_time,
743 X_forecast_method => RowInfo.forecast_method,
744 X_discount_option => RowInfo.discount_option,
745 X_order_status => RowInfo.order_status,
746 X_order_date_type => RowInfo.order_date_type,
747 X_code_combination_id => RowInfo.code_combination_id,
748 X_set_of_books_id => RowInfo.set_of_books_id,
749 X_org_id => RowInfo.org_id,
750 X_chart_of_accounts_id => RowInfo.chart_of_accounts_id,
751 X_budget_name => RowInfo.budget_name,
752 X_budget_version_id => RowInfo.budget_version_id,
753 X_encumbrance_type_id => RowInfo.encumbrance_type_id,
754 X_roll_forward_type => RowInfo.roll_forward_type,
755 X_roll_forward_period => RowInfo.roll_forward_period,
756 X_customer_profile_class_id => RowInfo.customer_profile_class_id,
757 X_include_dispute_flag => RowInfo.include_dispute_flag,
758 X_sales_stage_id => RowInfo.sales_stage_id,
759 X_channel_code => RowInfo.channel_code,
760 X_win_probability => RowInfo.win_probability,
761 X_sales_forecast_status => RowInfo.sales_forecast_status,
762 X_receipt_method_id => RowInfo.receipt_method_id,
763 X_bank_account_id => RowInfo.bank_account_id,
764 X_payment_method => RowInfo.payment_method,
765 X_pay_group => RowInfo.pay_group,
766 X_payment_priority => RowInfo.payment_priority,
767 X_vendor_type => RowInfo.vendor_type,
768 X_authorization_status => RowInfo.authorization_status,
769 X_type => RowInfo.type,
770 X_budget_type => RowInfo.budget_type,
771 X_budget_version => RowInfo.budget_version,
772 X_include_hold_flag => RowInfo.include_hold_flag,
773 X_include_net_cash_flag => RowInfo.include_net_cash_flag,
774 X_created_by => X_created_by,
775 X_creation_date => X_creation_date,
776 X_last_updated_by => X_last_updated_by,
777 X_last_update_date => X_last_update_date,
778 X_last_update_login => X_last_update_login,
779 X_org_payment_method_id => RowInfo.org_payment_method_id,
780 X_xtr_bank_account => RowInfo.xtr_bank_account,
781 X_exclude_indic_exp => RowInfo.exclude_indic_exp,
782 X_company_code => RowInfo.company_code,
783 X_attribute_category => RowInfo.attribute_category,
784 X_attribute1 => RowInfo.attribute1,
785 X_attribute2 => RowInfo.attribute2,
786 X_attribute3 => RowInfo.attribute3,
787 X_attribute4 => RowInfo.attribute4,
788 X_attribute5 => RowInfo.attribute5,
789 X_attribute6 => RowInfo.attribute6,
790 X_attribute7 => RowInfo.attribute7,
791 X_attribute8 => RowInfo.attribute8,
792 X_attribute9 => RowInfo.attribute9,
793 X_attribute10 => RowInfo.attribute10,
794 X_attribute11 => RowInfo.attribute11,
795 X_attribute12 => RowInfo.attribute12,
796 X_attribute13 => RowInfo.attribute13,
797 X_attribute14 => RowInfo.attribute14,
798 X_attribute15 => RowInfo.attribute15,
799 X_description => RowInfo.description,
800 X_payroll_id => RowInfo.payroll_id,
801 X_external_source_type => RowInfo.external_source_type,
802 X_criteria_category => RowInfo.criteria_category,
803 X_criteria1 => RowInfo.criteria1,
804 X_criteria2 => RowInfo.criteria2,
805 X_criteria3 => RowInfo.criteria3,
806 X_criteria4 => RowInfo.criteria4,
807 X_criteria5 => RowInfo.criteria5,
808 X_criteria6 => RowInfo.criteria6,
809 X_criteria7 => RowInfo.criteria7,
810 X_criteria8 => RowInfo.criteria8,
811 X_criteria9 => RowInfo.criteria9,
812 X_criteria10 => RowInfo.criteria10,
813 X_criteria11 => RowInfo.criteria11,
814 X_criteria12 => RowInfo.criteria12,
815 X_criteria13 => RowInfo.criteria13,
816 X_criteria14 => RowInfo.criteria14,
817 X_criteria15 => RowInfo.criteria15,
818 X_use_average_payment_days
819 => RowInfo.use_average_payment_days,
820 X_period => RowInfo.period,
821 X_order_type_id => RowInfo.order_type_id,
822 X_use_payment_terms => RowInfo.use_payment_terms);
823
824 UPDATE CE_FORECAST_CELLS
825 SET forecast_row_id = rid
826 WHERE forecast_id = X_forecast_id AND
827 forecast_row_id = RowInfo.forecast_row_id;
828
829 fetch CR into RowInfo;
830 END LOOP;
831
832 x_forecast_header_id := p_forecast_header_id;
833 cep_standard.debug('<< CE_FORECASTS_PKG.duplicate_forecast_header');
834
835 EXCEPTION
836 WHEN OTHERS THEN
837 if (ch%ISOPEN) then close ch; end if;
838 if (cc%ISOPEN) then close cc; end if;
839 if (cr%ISOPEN) then close cr; end if;
840 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.duplicate_template_header');
841 RAISE;
842 END;
843
844 PROCEDURE recalc_glc(X_hid IN NUMBER) IS
845 CURSOR C_fid (p_hid NUMBER) IS
846 SELECT forecast_id
847 FROM ce_forecasts
848 WHERE forecast_header_id = p_hid;
849
850 l_start_date CE_FORECASTS.start_date%TYPE;
851 l_calendar_name CE_FORECASTS.period_set_name%TYPE;
852 l_start_period CE_FORECASTS.start_period%TYPE;
853 l_forecast_currency CE_FORECASTS.forecast_currency%TYPE;
854 l_exchange_type CE_FORECASTS.exchange_rate_type%TYPE;
855 l_exchange_date CE_FORECASTS.exchange_date%TYPE;
856 l_exchange_rate CE_FORECASTS.exchange_rate%TYPE;
857 l_src_curr_type CE_FORECASTS.currency_type%TYPE;
858 l_source_currency CE_FORECASTS.source_currency%TYPE;
859 l_rownum_from CE_FORECAST_ROWS.row_number%TYPE;
860 l_rownum_to CE_FORECAST_ROWS.row_number%TYPE;
861 l_amount_threshold CE_FORECASTS.amount_threshold%TYPE;
862 l_project_id CE_FORECASTS.project_id%TYPE;
863
864 cnt_fc NUMBER;
865 fcount NUMBER;
866 fid_rec C_fid%ROWTYPE;
867 BEGIN
868 SELECT count(*)
869 INTO cnt_fc
870 FROM ce_forecasts
871 WHERE forecast_header_id = X_hid;
872
873 FOR fid_rec IN C_fid(X_hid) LOOP
874
875 SELECT aging_type
876 INTO CE_CASH_FCST.G_aging_type
877 FROM ce_forecast_headers
878 WHERE forecast_header_id = X_hid;
879
880 SELECT start_date,
881 period_set_name,
882 start_period,
883 forecast_currency,
884 exchange_rate_type,
885 exchange_date,
886 exchange_rate,
887 currency_type,
888 source_currency,
889 amount_threshold,
890 project_id
891 INTO l_start_date,
892 l_calendar_name,
893 l_start_period,
894 l_forecast_currency,
895 l_exchange_type,
896 l_exchange_date,
897 l_exchange_rate,
898 l_src_curr_type,
899 l_source_currency,
900 l_amount_threshold,
901 l_project_id
902 FROM ce_forecasts
903 WHERE forecast_id = fid_rec.forecast_id;
904
905 SELECT min(row_number),
906 max(row_number)
907 INTO l_rownum_from,
908 l_rownum_to
909 FROM ce_forecast_rows
910 WHERE forecast_header_id = X_hid;
911
912 CE_CASH_FCST.set_parameters(X_hid,
913 null,
914 to_char(l_start_date, 'YYYY/MM/DD'),
915 l_calendar_name,
916 l_start_period,
917 l_forecast_currency,
918 l_exchange_type,
919 to_char(l_exchange_date, 'YYYY/MM/DD'),
920 l_exchange_rate,
921 l_src_curr_type,
922 l_source_currency,
923 l_amount_threshold,
924 l_project_id,
925 l_rownum_from,
926 l_rownum_to,
927 null,
928 0,
929 'N',
930 'NONE',
931 null,
932 null,
933 fid_rec.forecast_id,
934 CE_CASH_FCST.G_display_debug,
935 CE_CASH_FCST.G_debug_path,
936 CE_CASH_FCST.G_debug_file);
937
938 -- CE_CASH_FCST.G_forecast_id := fid_rec.forecast_id;
939
940 FND_CURRENCY.get_info(l_forecast_currency,
941 CE_CASH_FCST.G_precision,
942 CE_CASH_FCST.G_ext_precision,
943 CE_CASH_FCST.G_min_acct_unit);
944
945 END LOOP;
946
947 null;
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 IF (C_fid%ISOPEN) THEN CLOSE C_fid; END IF;
952 cep_standard.debug('EXCEPTION: CE_FORECASTS_PKG.recalc_glc');
953 RAISE;
954 END recalc_glc;
955
956
957 END CE_FORECASTS_PKG;