[Home] [Help]
PACKAGE BODY: APPS.CE_CASH_FCST
Source
1 PACKAGE BODY CE_CASH_FCST AS
2 /* $Header: cefcshfb.pls 120.22.12000000.2 2007/03/30 15:53:29 kbabu ship $ */
3
4 --
5 -- Get Header Information
6 --
7 CURSOR f_header_cursor (p_forecast_header_id NUMBER) IS
8 SELECT cfh.name, cfh.aging_type,
9 cfh.overdue_transactions, cfh.transaction_calendar_id,
10 cfh.start_project_id, cfh.end_project_id
11 FROM CE_FORECAST_HEADERS cfh
12 WHERE cfh.forecast_header_id = p_forecast_header_id;
13
14 --
15 -- Get row information
16 --
17 CURSOR f_row_cursor (p_forecast_header_id NUMBER, p_rownum_from NUMBER, p_rownum_to NUMBER) IS
18 SELECT rowid, forecast_row_id, row_number, trx_type, nvl(lead_time,0),
19 NVL(forecast_method,'F'), discount_option, DECODE(trx_type, 'PAY', 'PAY', 'XTR', 'XTR', 'XTI', 'XTR', 'XTO', 'XTR', SUBSTR(trx_type, 1,2)),
20 include_float_flag, NVL(order_status,'A'),
21 order_date_type, code_combination_id, budget_name,
22 encumbrance_type_id, chart_of_accounts_id,
23 set_of_books_id, NVL(org_id,-99), legal_entity_id,
24 roll_forward_type, roll_forward_period,
25 NVL(include_dispute_flag,'N'), sales_stage_id,
26 channel_code, NVL(win_probability,0), sales_forecast_status,
27 customer_profile_class_id, bank_account_id,
28 receipt_method_id, vendor_type, payment_method, pay_group,
29 payment_priority, authorization_status, type, budget_type,
30 budget_version, include_hold_flag, include_net_cash_flag, budget_version_id,
31 payroll_id, xtr_bank_account, company_code, exclude_indic_exp, org_payment_method_id,
32 external_source_type, criteria_category,
33 criteria1, criteria2, criteria3, criteria4, criteria5,
34 criteria6, criteria7, criteria8, criteria9, criteria10,
35 criteria11, criteria12, criteria13, criteria14, criteria15,
36 use_average_payment_days, period, order_type_id,
37 use_payment_terms, include_temp_labor_flag
38 FROM ce_forecast_rows
39 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
40 NVL(p_rownum_to, row_number)
41 AND forecast_header_id = p_forecast_header_id
42 AND trx_type <> 'GLC';
43
44 CURSOR f_glc_cursor (p_forecast_header_id NUMBER, p_rownum_from NUMBER, p_rownum_to NUMBER) IS
45
46 SELECT rowid, forecast_row_id, row_number,
47 set_of_books_id, code_combination_id, chart_of_accounts_id
48 FROM ce_forecast_rows
49 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
50 NVL(p_rownum_to, row_number)
51 AND forecast_header_id = p_forecast_header_id
52 AND trx_type = 'GLC';
53
54 FUNCTION body_revision RETURN VARCHAR2 IS
55 BEGIN
56
57 RETURN '$Revision: 120.22.12000000.2 $';
58
59 END body_revision;
60
61 FUNCTION spec_revision RETURN VARCHAR2 IS
62 BEGIN
63
64 RETURN G_spec_revision;
65
66 END spec_revision;
67
68 /* ---------------------------------------------------------------------
69 | PUBLIC PROCEDURE |
70 | set_parameters |
71 | |
72 | DESCRIPTION |
73 | This procedure sets the global parameters |
74 | |
75 | CALLED BY |
76 | |
77 | REQUIRES |
78 | all runtime parameters |
79 | HISTORY |
80 | 04-OCT-1996 Created Bidemi Carrol |
81 --------------------------------------------------------------------- */
82 PROCEDURE set_parameters (p_forecast_header_id IN NUMBER,
83 p_forecast_runname IN VARCHAR2,
84 p_forecast_start_date IN VARCHAR2,
85 p_calendar_name IN VARCHAR2,
86 p_forecast_start_period IN VARCHAR2,
87 p_forecast_currency IN VARCHAR2,
88 p_exchange_type IN VARCHAR2,
89 p_exchange_date IN VARCHAR2,
90 p_exchange_rate IN NUMBER,
91 p_src_curr_type IN VARCHAR2,
92 p_src_currency IN VARCHAR2,
93 p_amount_threshold IN NUMBER,
94 p_project_id IN NUMBER,
95 p_rownum_from IN NUMBER,
96 p_rownum_to IN NUMBER,
97 p_sub_request IN VARCHAR2,
98 p_factor IN NUMBER,
99 p_include_sub_account IN VARCHAR2,
100 p_view_by IN VARCHAR2,
101 p_bank_balance_type IN VARCHAR2,
102 p_float_type IN VARCHAR2,
103 p_forecast_id IN NUMBER,
104 p_display_debug IN VARCHAR2,
105 p_debug_path IN VARCHAR2,
106 p_debug_file IN VARCHAR2) IS
107
108 x_exchange_type GL_DAILY_RATES.conversion_type%TYPE;
109 l_is_fixed_rate BOOLEAN;
110 l_relationship VARCHAR2(30);
111 BEGIN
112
113 /* In the case where p_src_curr_type = 'Entered', ensure that there is no
114 fixed rate between the forecast and source currencies (both EMU currencies.
115 If so, then override the p_exchange_type to 'EMU FIXED'. This is to
116 handle the case where the user enters an exchange type of 'User' from
117 the SRS (concurrent submission form) when there is a fixed rate. This
118 scenario can occur due to the inability of flex fields to handle
119 conditional value sets the way CE needs it and will result in erroneous
120 forecast amounts. */
121
122 IF (p_src_curr_type = 'E') then
123 GL_CURRENCY_API.get_relation(p_forecast_currency,
124 p_src_currency,
125 to_char(to_date(p_exchange_date,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY'),
126 l_is_fixed_rate,
127 l_relationship);
128
129 IF (p_forecast_currency = p_src_currency OR
130 l_relationship NOT IN ('EURO-EMU', 'EMU-EURO',
131 'EMU-EMU', 'EURO-EURO')) THEN
132 x_exchange_type := p_exchange_type;
133 ELSIF (l_is_fixed_rate = TRUE) THEN
134 x_exchange_type := 'EMU FIXED';
135 ELSE
136 x_exchange_type := p_exchange_type;
137 END IF;
138 ELSE
139 x_exchange_type := p_exchange_type;
140 END IF;
141
142 cep_standard.debug('>>CE_CASH_FCST.set_parameters');
143 G_rp_forecast_header_id := p_forecast_header_id;
144 G_rp_forecast_runname := p_forecast_runname;
145 G_rp_forecast_start_date := to_date(p_forecast_start_date,'YYYY/MM/DD HH24:MI:SS');
146 G_rp_calendar_name := p_calendar_name ;
147 G_rp_forecast_start_period := p_forecast_start_period;
148 G_rp_forecast_currency := p_forecast_currency;
149 G_rp_exchange_type := x_exchange_type;
150 G_rp_exchange_date := to_date(p_exchange_date,'YYYY/MM/DD HH24:MI:SS');
151 G_rp_exchange_rate := p_exchange_rate;
152 G_rp_src_curr_type := p_src_curr_type;
153 G_rp_src_currency := p_src_currency;
154 G_rp_amount_threshold := p_amount_threshold;
155 G_rp_project_id := p_project_id;
156 G_rp_rownum_from := p_rownum_from;
157 G_rp_rownum_to := p_rownum_to;
158 G_rp_sub_request := p_sub_request;
159 G_forecast_id := p_forecast_id;
160 G_display_debug := p_display_debug;
161 G_debug_path := p_debug_path;
162 G_debug_file := p_debug_file;
163
164 G_rp_bank_balance_type := p_bank_balance_type;
165 G_rp_float_type := p_float_type;
166 G_rp_view_by := p_view_by;
167 G_rp_include_sub_account := p_include_sub_account;
168 G_rp_factor := p_factor;
169
170 IF (p_src_currency IS NULL) THEN
171 CE_CASH_FCST.G_rp_src_curr_type := 'A';
172 END IF;
173 IF(p_sub_request = 'Y' AND p_forecast_id IS NULL)THEN
174 G_parent_process := TRUE;
175 ELSE
176 G_parent_process := FALSE;
177 END IF;
178
179 cep_standard.debug('CE_CASH_FCST.G_rp_forecast_header_id : '||G_rp_forecast_header_id);
180 cep_standard.debug('CE_CASH_FCST.G_rp_forecast_runname : '||G_rp_forecast_runname);
181 cep_standard.debug('CE_CASH_FCST.G_rp_calendar_name : '||G_rp_calendar_name);
182 cep_standard.debug('CE_CASH_FCST.G_rp_forecast_start_date : '||G_rp_forecast_start_date);
183 cep_standard.debug('CE_CASH_FCST.G_rp_forecast_start_period : '||G_rp_forecast_start_period);
184 cep_standard.debug('CE_CASH_FCST.G_rp_forecast_currency : '||G_rp_forecast_currency);
185 cep_standard.debug('CE_CASH_FCST.G_rp_exchange_type : '||G_rp_exchange_type);
186 cep_standard.debug('CE_CASH_FCST.G_rp_exchange_date : '||G_rp_exchange_date);
187 cep_standard.debug('CE_CASH_FCST.G_rp_exchange_rate : '||G_rp_exchange_rate);
188 cep_standard.debug('CE_CASH_FCST.G_rp_src_curr_type : '||G_rp_src_curr_type);
189 cep_standard.debug('CE_CASH_FCST.G_rp_src_currency : '||G_rp_src_currency);
190 cep_standard.debug('CE_CASH_FCST.G_rp_rownum_from : '||G_rp_rownum_from);
191 cep_standard.debug('CE_CASH_FCST.G_rp_rownum_to : '||G_rp_rownum_to);
192 cep_standard.debug('CE_CASH_FCST.G_rp_sub_request : '||G_rp_sub_request);
193 cep_standard.debug('CE_CASH_FCST.G_forecast_id : '||G_forecast_id);
194 cep_standard.debug('CE_CASH_FCST.G_display_debug : '||G_display_debug);
195 cep_standard.debug('CE_CASH_FCST.G_debug_path : '||G_debug_path);
196 cep_standard.debug('CE_CASH_FCST.G_debug_file : '||G_debug_file);
197
198 --
199 -- Set View constants
200 --
201 CEFC_VIEW_CONST.set_constants(G_rp_forecast_header_id,
202 G_rp_calendar_name,
203 G_rp_forecast_start_period,
204 G_rp_forecast_start_date);
205
206 END;
207
208
209
210 /* ---------------------------------------------------------------------
211 | PUBLIC PROCEDURE |
212 | Print_Report |
213 | |
214 | DESCRIPTION |
215 | This procedure submits a concurrent request to print the |
216 | Cash Forecast Report after a succesful run. |
217 | |
218 | CALLED BY |
219 | |
220 | REQUIRES |
221 | p_forecast_header_id forecast header id |
222 | p_forecast_start_date forecast date |
223 | HISTORY |
224 | 04-OCT-1996 Created Bidemi Carrol |
225 --------------------------------------------------------------------- */
226 PROCEDURE Print_Report IS
227 req_id NUMBER;
228 request_id NUMBER;
229 reqid VARCHAR2(30);
230 number_of_copies NUMBER;
231 printer VARCHAR2(30);
232 print_style VARCHAR2(30);
233 save_output_flag VARCHAR2(30);
234 save_output_bool BOOLEAN;
235 BEGIN
236 --
237 -- Get original request id
238 --
239 fnd_profile.get('CONC_REQUEST_ID', reqid);
240 request_id := to_number(reqid);
241 --
242 -- Get print options
243 --
244 cep_standard.debug('Request Id is ' || request_id);
245 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
246 number_of_copies,
247 print_style,
248 printer,
249 save_output_flag))THEN
250 cep_standard.debug('Message: get print options failed');
251 ELSE
252 IF (save_output_flag = 'Y') THEN
253 save_output_bool := TRUE;
254 ELSE
255 save_output_bool := FALSE;
256 END IF;
257
258 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
259 'CEFCERR',
260 printer,
261 print_style,
262 save_output_flag))THEN
263 cep_standard.debug('Message: get print options failed');
264 END IF;
265 --
266 -- Set print options
267 --
268 IF (NOT FND_REQUEST.set_print_options(printer,
269 print_style,
270 number_of_copies,
271 save_output_bool)) THEN
272 cep_standard.debug('Set print options failed');
273 END IF;
274 END IF;
275 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
276 'CEFCERR',
277 NULL,
278 trunc(sysdate),
279 FALSE,
280 G_forecast_id);
281 END Print_Report;
282
283 /* ---------------------------------------------------------------------
284 | PUBLIC PROCEDURE |
285 | Print_Forecast_Report |
286 | |
287 | DESCRIPTION |
288 | This procedure submits a concurrent request to print the |
289 | Cash Forecast Report after a succesful run. |
290 | |
291 | CALLED BY |
292 | |
293 | REQUIRES |
294 | p_forecast_header_id forecast header id |
295 | p_forecast_id forecast_id |
296 | p_forecast_start_date forecast date |
297 | HISTORY |
298 | 06-24-1998 Created BHCHUNG |
299 --------------------------------------------------------------------- */
300 PROCEDURE Print_Forecast_Report IS
301 req_id NUMBER;
302 request_id NUMBER;
303 reqid VARCHAR2(30);
304 number_of_copies NUMBER;
305 printer VARCHAR2(30);
306 print_style VARCHAR2(30);
307 save_output_flag VARCHAR2(30);
308 save_output_bool BOOLEAN;
309 l_forecast_header_id NUMBER;
310 BEGIN
311 --
312 -- Get original request id
313 --
314 fnd_profile.get('CONC_REQUEST_ID', reqid);
315 request_id := to_number(reqid);
316 --
317 -- Get print options
318 --
319 cep_standard.debug('Request Id is ' || request_id);
320 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
321 number_of_copies,
322 print_style,
323 printer,
324 save_output_flag))THEN
325 cep_standard.debug('Message: get print options failed');
326
327 ELSE
328 IF (save_output_flag = 'Y') THEN
329 save_output_bool := TRUE;
330 ELSE
331 save_output_bool := FALSE;
332 END IF;
333
334 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
335 'CEFCAMTS',
336 printer,
337 print_style,
338 save_output_flag))THEN
339 cep_standard.debug('Message: get print options failed');
340 END IF;
341
342 --
343 -- Set print options
344 --
345 IF (NOT FND_REQUEST.set_print_options(printer,
346 print_style,
347 number_of_copies,
348 save_output_bool)) THEN
349 cep_standard.debug('Set print options failed');
350 END IF;
351 END IF;
352
353 SELECT forecast_header_id
354 INTO l_forecast_header_id
355 FROM ce_forecasts
356 WHERE forecast_id = G_forecast_id;
357
358 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
359 'CEFCAMTS',
360 NULL,
361 trunc(sysdate),
362 FALSE,
363 l_forecast_header_id,
364 G_forecast_id);
365 END Print_Forecast_Report;
366
367 /* ---------------------------------------------------------------------
368 | PUBLIC PROCEDURE |
369 | populate_base_xrate_table |
370 | |
371 | DESCRIPTION |
372 | populate the exchange rate information from GL table to |
373 | CE_CURRENCY_RATES_TEMP for base currency |
374 | |
375 | CALLED BY |
376 | |
377 | REQUIRES |
378 | |
379 | HISTORY |
380 | 21-JUL-1997 Created Wynne Chan |
381 ---------------------------------------------------------------------*/
382 FUNCTION populate_base_xrate_table RETURN BOOLEAN IS
383 CURSOR from_curr(orig_curr VARCHAR2) IS
384 SELECT currency_code
385 FROM fnd_currencies
386 WHERE enabled_flag = 'Y'
387 AND currency_code <> CE_CASH_FCST.G_rp_forecast_currency
388 AND currency_code <> 'STAT'
389 AND CE_CASH_FCST.G_rp_exchange_date BETWEEN
390 NVL(start_date_active,CE_CASH_FCST.G_rp_exchange_date) and
391 NVL(end_date_active,CE_CASH_FCST.G_rp_exchange_date);
392
393 CURSOR bad_curr IS
394 SELECT currency_code
395 FROM fnd_currencies
396 WHERE enabled_flag = 'Y'
397 AND currency_code <> CE_CASH_FCST.G_rp_forecast_currency
398 AND currency_code <> 'STAT'
399 AND ( CE_CASH_FCST.G_rp_exchange_date <
400 NVL(start_date_active,CE_CASH_FCST.G_rp_exchange_date) OR
401 CE_CASH_FCST.G_rp_exchange_date >
402 NVL(end_date_active,CE_CASH_FCST.G_rp_exchange_date));
403
404 CURSOR base_curr IS
405 SELECT distinct(org.currency_code)
406 FROM ce_forecast_rows r,
407 ce_forecast_oe_orgs_v org
408 WHERE r.row_number BETWEEN
409 NVL(CE_CASH_FCST.G_rp_rownum_from, row_number) and
413 AND r.trx_type = 'OEO'
410 NVL(CE_CASH_FCST.G_rp_rownum_to, row_number)
411 AND r.forecast_header_id = CE_CASH_FCST.G_rp_forecast_header_id
412 AND org.currency_code <> CE_CASH_FCST.G_rp_forecast_currency
414 AND org.org_id = NVL(r.org_id, org.org_id);
415
416 curr FND_CURRENCIES.currency_code%TYPE;
417 to_curr FND_CURRENCIES.currency_code%TYPE;
418 error_msg FND_NEW_MESSAGES.message_text%TYPE;
419 xrate NUMBER;
420 all_exist_flag BOOLEAN := TRUE;
421 BEGIN
422 cep_standard.debug('>>CE_CASH_FCST.populate_base_xrate_table');
423
424 --
425 -- For each base currencies used by OE rows, determine it's xrate from
426 -- valid currencies to the base currency.
427 --
428 OPEN base_curr;
429 LOOP
430 FETCH base_curr INTO to_curr;
431 EXIT WHEN (base_curr%NOTFOUND OR base_curr%NOTFOUND IS NULL);
432
433 IF( to_curr <> CE_CASH_FCST.G_rp_forecast_currency)THEN
434 OPEN from_curr(to_curr);
435 LOOP
436
437 FETCH from_curr INTO curr;
438 EXIT WHEN (from_curr%NOTFOUND OR from_curr%NOTFOUND IS NULL);
439 BEGIN
440 xrate := GL_CURRENCY_API.get_rate(curr, to_curr,
441 CE_CASH_FCST.G_rp_exchange_date,
442 CE_CASH_FCST.G_rp_exchange_type);
443 insert into CE_CURRENCY_RATES_TEMP
444 (forecast_request_id, currency_code, exchange_rate, to_currency)
445 values (CE_CASH_FCST.G_forecast_id, curr, xrate, to_curr);
446 cep_standard.debug(' Exchange info - '||curr||'->'||to_curr||' has rate '||to_char(xrate));
447 EXCEPTION
448 WHEN OTHERS THEN
449 -- WHEN NO_RATE THEN
450 -- bug 1200912
451 IF (curr = CE_CASH_FCST.G_rp_src_currency) THEN
452 all_exist_flag := FALSE;
453 FND_MESSAGE.set_name ('CE','CE_FC_MISSING_EXCHANGE_RATE');
454 FND_MESSAGE.set_token('FROM_CURR', curr);
455 FND_MESSAGE.set_token('TO_CURR',
456 CE_CASH_FCST.G_rp_forecast_currency);
457 error_msg := FND_MESSAGE.GET;
458 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,
459 G_rp_forecast_header_id, null,'CE_FC_MISSING_RATE', error_msg);
460 END IF;
461 END;
462 END LOOP;
463 CLOSE from_curr;
464
465 --
466 -- Insert constant rate (1) for base_currency to base_currency
467 --
468 insert into CE_CURRENCY_RATES_TEMP (forecast_request_id, currency_code,
469 exchange_rate, to_currency)
470 values (CE_CASH_FCST.G_forecast_id, to_curr, 1, to_curr);
471
472 OPEN bad_curr;
473 LOOP
474 FETCH bad_curr INTO curr;
475 EXIT WHEN (bad_curr%NOTFOUND OR bad_curr%NOTFOUND IS NULL);
476 BEGIN
477 all_exist_flag := FALSE;
478 FND_MESSAGE.set_name ('CE','CE_FC_BAD_CURRENCY');
479 FND_MESSAGE.set_token('BAD_CURR', curr);
480 error_msg := FND_MESSAGE.GET;
481 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,
482 G_rp_forecast_header_id, null,'CE_FC_BAD_CURRENCY', error_msg);
483 END;
484 END LOOP;
485 CLOSE bad_curr;
486
487 END IF;
488 END LOOP;
489 CLOSE base_curr;
490
491 cep_standard.debug('<<CE_CASH_FCST.populate_base_xrate_table');
492 return (all_exist_flag);
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 cep_standard.debug('EXCEPTION - OTHERS:populate_base_xrate_table');
497 IF from_curr%ISOPEN THEN CLOSE from_curr; END IF;
498 IF base_curr%ISOPEN THEN CLOSE base_curr; END IF;
499 IF bad_curr%ISOPEN THEN CLOSE bad_curr; END IF;
500 RAISE;
501 return (FALSE);
502 END populate_base_xrate_table;
503
504 /* ---------------------------------------------------------------------
505 | PUBLIC PROCEDURE |
506 | populate_xrate_table |
507 | |
508 | DESCRIPTION |
509 | populate the exchange rate information from GL table to |
510 | CE_CURRENCY_RATES_TEMP |
511 | |
512 | CALLED BY |
513 | |
514 | REQUIRES |
515 | |
516 | HISTORY |
517 | 21-JUL-1997 Created Wynne Chan |
518 ---------------------------------------------------------------------*/
519 FUNCTION populate_xrate_table RETURN BOOLEAN IS
520 CURSOR C IS
521 SELECT currency_code
522 FROM fnd_currencies
523 WHERE enabled_flag = 'Y'
524 AND currency_code <> CE_CASH_FCST.G_rp_forecast_currency
525 AND currency_code <> 'STAT'
526 AND CE_CASH_FCST.G_rp_exchange_date BETWEEN
527 NVL(start_date_active,CE_CASH_FCST.G_rp_exchange_date)
528 and NVL(end_date_active,CE_CASH_FCST.G_rp_exchange_date);
529
530 CURSOR bad_curr IS
531 SELECT currency_code
532 FROM fnd_currencies
533 WHERE enabled_flag = 'Y'
534 AND currency_code <> CE_CASH_FCST.G_rp_forecast_currency
535 AND currency_code <> 'STAT'
536 AND ( CE_CASH_FCST.G_rp_exchange_date <
537 NVL(start_date_active,CE_CASH_FCST.G_rp_exchange_date) OR
538 CE_CASH_FCST.G_rp_exchange_date >
539 NVL(end_date_active,CE_CASH_FCST.G_rp_exchange_date));
540
541 curr FND_CURRENCIES.currency_code%TYPE;
542 from_curr FND_CURRENCIES.currency_code%TYPE;
543 to_curr FND_CURRENCIES.currency_code%TYPE;
544 euro_curr FND_CURRENCIES.currency_code%TYPE;
545 error_msg FND_NEW_MESSAGES.message_text%TYPE;
549 fcast_curr_is_emu VARCHAR2(1);
546 xrate NUMBER;
547 skip_err_log BOOLEAN;
548 all_exist_flag BOOLEAN := TRUE;
550 src_curr_is_emu VARCHAR2(1);
551 from_curr_is_emu VARCHAR2(1);
552 l_currency_type VARCHAR2(5);
553
554 BEGIN
555 cep_standard.debug('>>CE_CASH_FCST.populate_xrate_table');
556
557 --
558 -- Insert constant rate (1) for forecast_currency to forecast_currency
559 --
560 insert into CE_CURRENCY_RATES_TEMP
561 (forecast_request_id, currency_code, exchange_rate, to_currency)
562 values (CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_currency, 1,
563 CE_CASH_FCST.G_rp_forecast_currency);
564
565 IF( G_rp_forecast_currency <> G_rp_src_currency OR
566 G_rp_src_curr_type <> 'E')THEN
567
568 SELECT DECODE(COUNT(*),0,'N','Y')
569 INTO fcast_curr_is_emu
570 FROM FND_CURRENCIES
571 WHERE currency_code = CE_CASH_FCST.G_rp_forecast_currency AND
572 derive_type = 'EMU';
573
574 SELECT DECODE(COUNT(*),0,'N','Y')
575 INTO src_curr_is_emu
576 FROM FND_CURRENCIES
577 WHERE currency_code = CE_CASH_FCST.G_rp_src_currency AND
578 derive_type = 'EMU';
579
580 IF ( (fcast_curr_is_emu = 'Y' OR src_curr_is_emu = 'Y') OR
581 G_rp_src_curr_type = 'A' ) THEN
582 BEGIN
583 SELECT currency_code
584 INTO euro_curr
585 FROM fnd_currencies
586 WHERE derive_type = 'EURO';
587 EXCEPTION
588 WHEN NO_DATA_FOUND THEN
589 FND_MESSAGE.set_name ('CE','CE_FC_EURO_NOT_DEFINED');
590 error_msg := FND_MESSAGE.GET;
591 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,
592 G_rp_forecast_header_id, null,'CE_FC_EURO_NOT_DEFINED',
593 error_msg);
594 END;
595 END IF;
596
597 OPEN C;
598 LOOP
599 FETCH C INTO from_curr;
600
601 SELECT DECODE(COUNT(*),0,'N','Y')
602 INTO from_curr_is_emu
603 FROM FND_CURRENCIES
604 WHERE currency_code = from_curr AND
605 derive_type IN ('EMU','EURO');
606
607 EXIT WHEN (C%NOTFOUND OR C%NOTFOUND IS NULL);
608 BEGIN
609 xrate := GL_CURRENCY_API.get_rate(from_curr, G_rp_forecast_currency,
610 CE_CASH_FCST.G_rp_exchange_date,
611 CE_CASH_FCST.G_rp_exchange_type);
612 insert into CE_CURRENCY_RATES_TEMP (forecast_request_id,
613 currency_code, exchange_rate, to_currency)
614 values (CE_CASH_FCST.G_forecast_id, from_curr, xrate,
615 G_rp_forecast_currency);
616 cep_standard.debug(' Exchange info - '||from_curr||' has rate '||to_char(xrate));
617 EXCEPTION
618 WHEN OTHERS THEN
619 -- WHEN NO_RATE THEN
620 skip_err_log := FALSE;
621
622 -- bug 1200912
623 -- from_curr always <> CE_CASH_FCST.G_rp_src_currency
624 IF (G_rp_src_curr_type <> 'A'
625 AND from_curr <> CE_CASH_FCST.G_rp_src_currency) THEN
626 skip_err_log := TRUE;
627 END IF;
628
629 to_curr := CE_CASH_FCST.G_rp_forecast_currency;
630
631 IF (from_curr_is_emu = 'N' and fcast_curr_is_emu = 'Y') THEN
632 SELECT decode( derive_type,
633 'EURO', 'EURO',
634 'EMU', decode( sign( trunc(CE_CASH_FCST.G_rp_exchange_date)
635 - trunc(derive_effective)),
636 -1, 'OTHER',
637 'EMU'),
638 'OTHER' )
639 INTO l_currency_type
640 FROM FND_CURRENCIES
641 WHERE currency_code = to_curr;
642 IF (l_currency_type = 'EMU') THEN
643 to_curr := euro_curr;
644 END IF;
645 ELSIF (from_curr_is_emu = 'Y' and fcast_curr_is_emu = 'N') THEN
646 SELECT decode( derive_type,
647 'EURO', 'EURO',
648 'EMU', decode( sign( trunc(CE_CASH_FCST.G_rp_exchange_date)
649 - trunc(derive_effective)),
650 -1, 'OTHER',
651 'EMU'),
652 'OTHER' )
653 INTO l_currency_type
654 FROM FND_CURRENCIES
655 WHERE currency_code = from_curr;
656 IF (l_currency_type = 'EMU') THEN
657 from_curr := euro_curr;
658 END IF;
659 ELSIF (from_curr_is_emu = 'Y' and fcast_curr_is_emu = 'Y') THEN
660 skip_err_log := TRUE;
661 ELSIF (from_curr_is_emu = 'N' and fcast_curr_is_emu = 'N') THEN
662 IF from_curr = to_curr THEN
663 skip_err_log := TRUE;
664 END IF;
665 END IF;
666
667 IF skip_err_log = FALSE THEN
668 all_exist_flag := FALSE;
669 FND_MESSAGE.set_name ('CE','CE_FC_MISSING_EXCHANGE_RATE');
670 FND_MESSAGE.set_token('FROM_CURR', from_curr);
671 FND_MESSAGE.set_token('TO_CURR',to_curr);
672 error_msg := FND_MESSAGE.GET;
673 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,
674 G_rp_forecast_header_id, null,'CE_FC_MISSING_RATE',
675 error_msg);
676 END IF;
677 END;
678 END LOOP;
679 CLOSE C;
680
681 OPEN bad_curr;
682 LOOP
683 FETCH bad_curr INTO curr;
687 IF (G_rp_src_curr_type = 'A'
684 EXIT WHEN (bad_curr%NOTFOUND OR bad_curr%NOTFOUND IS NULL);
685 BEGIN
686 all_exist_flag := FALSE;
688 OR curr = CE_CASH_FCST.G_rp_src_currency) THEN
689 FND_MESSAGE.set_name ('CE','CE_FC_BAD_CURRENCY');
690 FND_MESSAGE.set_token('BAD_CURR', curr);
691 error_msg := FND_MESSAGE.GET;
692 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,
693 G_rp_forecast_header_id, null,'CE_FC_BAD_CURRENCY', error_msg);
694 END IF;
695 END;
696 END LOOP;
697 CLOSE bad_curr;
698
699 END IF;
700
701 cep_standard.debug('<<CE_CASH_FCST.populate_xrate_table');
702 return (all_exist_flag);
703
704 EXCEPTION
705 WHEN OTHERS THEN
706 cep_standard.debug('EXCEPTION - OTHERS:populate_xrate_table');
707 IF C%ISOPEN THEN CLOSE C; END IF;
708 IF bad_curr%ISOPEN THEN CLOSE bad_curr; END IF;
709 RAISE;
710 return (FALSE);
711 END populate_xrate_table;
712
713
714 /* ---------------------------------------------------------------------
715 | PRIVATE PROCEDURE |
716 | clear_xrate_table |
717 | |
718 | DESCRIPTION |
719 | clear the exchange rate information in CE_CURRENCY_RATES_TEMP |
720 | |
721 | CALLED BY |
722 | |
723 | REQUIRES |
724 | |
725 | HISTORY |
726 | 21-JUL-1997 Created Wynne Chan |
727 ---------------------------------------------------------------------*/
728 PROCEDURE clear_xrate_table IS
729 BEGIN
730 cep_standard.debug('Delete all xrate information');
731 delete from CE_CURRENCY_RATES_TEMP
732 where forecast_request_id = CE_CASH_FCST.G_forecast_id;
733 EXCEPTION
734 WHEN OTHERS THEN
735 cep_standard.debug('EXCEPTION - OTHERS: clear_xrate_table');
736 RAISE;
737 END clear_xrate_table;
738
739
740 /* ---------------------------------------------------------------------
741 | PRIVATE PROCEDURE |
742 | validate_transaction_calendar |
743 | |
744 | DESCRIPTION |
745 | checks to make sure that the period set name for all set of |
746 | books are the same |
747 | CALLED BY |
748 | |
749 | REQUIRES |
750 | |
751 | HISTORY |
752 | 05-AUG-1997 Created Wynne Chan |
753 ---------------------------------------------------------------------*/
754 PROCEDURE validate_transaction_calendar IS
755 BEGIN
756
757 cep_standard.debug('>>validate_transaction_calendar');
758
759 SELECT min(transaction_date), max(transaction_date)
760 INTO CE_CSH_FCST_POP.G_calendar_start, CE_CSH_FCST_POP.G_calendar_end
761 FROM gl_transaction_dates
762 WHERE transaction_calendar_id = G_transaction_calendar_id;
763
764 IF(CE_CSH_FCST_POP.G_calendar_start IS NULL OR
765 CE_CSH_FCST_POP.G_calendar_end IS NULL)THEN
766 cep_standard.debug('Cannot find transaction calendar');
767 G_transaction_calendar_id := NULL;
768 END IF;
769
770 cep_standard.debug('<<validate_transaction_calendar');
771 EXCEPTION
772 WHEN OTHERS THEN
773 cep_standard.debug('EXCEPTION - OTHERS: validate_transaction_calendar');
774 RAISE;
775 END validate_transaction_calendar;
776
777
778 /* ---------------------------------------------------------------------
779 | PUBLIC PROCEDURE |
780 | valid_calendar_name |
781 | |
782 | DESCRIPTION |
783 | checks to make sure that the period set name for all set of |
784 | books are the same |
785 | CALLED BY |
786 | valid_row_info |
787 | HISTORY |
788 | 04-OCT-1996 Created Bidemi Carrol |
789 ---------------------------------------------------------------------*/
790 FUNCTION valid_calendar_name RETURN BOOLEAN IS
791 valid_period BOOLEAN := TRUE;
792 sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
793 calendar GL_PERIODS.period_set_name%TYPE;
794 error_msg fnd_new_messages.message_text%TYPE;
795 sob_name GL_SETS_OF_BOOKS.name%TYPE;
796
797 CURSOR sob_c IS
798 SELECT gsb.period_set_name, org.set_of_books_id, org.set_of_books_name
799 FROM ce_forecast_orgs_v org,
800 gl_sets_of_books gsb
801 WHERE gsb.set_of_books_id = org.set_of_books_id
802 AND org.app_short_name = G_app_short_name
803 AND org.set_of_books_id = NVL(G_set_of_books_id,org.set_of_books_id)
804 AND (org.org_id = DECODE(G_org_id, -1, org.org_id,-99, org.org_id, G_org_id)
805 or org.org_id IS NULL);
806
807 BEGIN
808 cep_standard.debug('>>CE_CASH_FCST.valid_calendar_name');
809
810 IF(G_app_short_name = 'GL')THEN
811 SELECT period_set_name, set_of_books_id, name, currency_code
812 INTO calendar, sob_id, sob_name, G_sob_currency_code
813 FROM gl_sets_of_books gsb
814 WHERE gsb.set_of_books_id = G_set_of_books_id;
815
816 IF (calendar <> G_rp_calendar_name) THEN
817 valid_period := FALSE;
818 FND_MESSAGE.set_name('CE', 'CE_FC_INVALID_PERIOD_SET_NAME');
819 FND_MESSAGE.set_token('SOB_NAME', sob_name);
820 FND_MESSAGE.set_token('START_PERIOD', G_rp_calendar_name);
821 error_msg := fnd_message.get;
822 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, G_forecast_row_id,
823 'CE_FC_INVALID_PERIOD', error_msg);
827 LOOP
824 END IF;
825 ELSE
826 open sob_c;
828 FETCH sob_c INTO calendar, sob_id, sob_name;
829 EXIT WHEN sob_c%NOTFOUND or sob_c%NOTFOUND IS NULL;
830
831 IF (calendar <> G_rp_calendar_name) THEN
832 valid_period := FALSE;
833 FND_MESSAGE.set_name('CE', 'CE_FC_INVALID_PERIOD_SET_NAME');
834 FND_MESSAGE.set_token('SOB_NAME', sob_name);
835 FND_MESSAGE.set_token('START_PERIOD', G_rp_calendar_name);
836 error_msg := fnd_message.get;
837 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, G_forecast_row_id,
838 'CE_FC_INVALID_PERIOD', error_msg);
839 END IF;
840 END LOOP;
841 END IF;
842
843 cep_standard.debug('<<CE_CASH_FCST.valid_calendar_name');
844 return (valid_period);
845 EXCEPTION
846 WHEN OTHERS THEN
847 cep_standard.debug('EXCEPTION -OTHERS:valid_calendar_name');
848 IF sob_c%ISOPEN THEN CLOSE sob_c; END IF;
849 RAISE;
850 END valid_calendar_name;
851
852 /* ---------------------------------------------------------------------
853 | PRIVATE PROCEDURE |
854 | valid_col_info |
855 | DESCRIPTION |
856 | Validate column information to ensure all information in a |
857 | column is valid |
858 | CALLED BY |
859 | valid_forecast_run |
860 | HISTORY |
861 | 21-AUG-1997 Created Wynne Chan |
862 --------------------------------------------------------------------- */
863 FUNCTION valid_col_info RETURN BOOLEAN IS
864 error_msg FND_NEW_MESSAGES.message_text%TYPE;
865 valid_col BOOLEAN := TRUE;
866 col_count NUMBER;
867 BEGIN
868 SELECT count(1)
869 INTO col_count
870 FROM ce_forecast_columns
871 WHERE forecast_header_id = G_rp_forecast_header_id;
872
873 IF( col_count = 0)THEN
874 SELECT count(1)
875 INTO col_count
876 FROM ce_forecast_periods
877 WHERE forecast_header_id = G_rp_forecast_header_id;
878
879 IF (col_count = 0) THEN
880 valid_col := FALSE;
881 FND_MESSAGE.set_name('CE', 'CE_FC_NO_COLUMN');
882 error_msg := fnd_message.get;
883 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, null,
884 'CE_FC_NO_COLUMN', error_msg);
885 END IF;
886 END IF;
887
888 return valid_col;
889 EXCEPTION
890 WHEN OTHERS THEN
891 cep_standard.debug('EXCEPTION: CE_CASH_FCST.valid_col_info');
892 RAISE;
893 END valid_col_info;
894
895 /* ---------------------------------------------------------------------
896 | PRIVATE PROCEDURE |
897 | valid_col_range |
898 | DESCRIPTION |
899 | Make sure columns are defined such that it is not exceeding |
900 | the database limit |
901 | CALLED BY |
902 | create_forecast |
903 | HISTORY |
904 | 21-AUG-1997 Created Wynne Chan |
905 --------------------------------------------------------------------- */
906 FUNCTION valid_col_range RETURN BOOLEAN IS
907 CURSOR cCol IS SELECT forecast_column_id, column_number, days_from, days_to
908 FROM ce_forecast_columns
909 WHERE forecast_header_id = G_rp_forecast_header_id;
910
911 error_msg FND_NEW_MESSAGES.message_text%TYPE;
912 col_num NUMBER;
913 cid NUMBER;
914 days_from NUMBER;
915 days_to NUMBER;
916 all_valid BOOLEAN DEFAULT TRUE;
917 BEGIN
918 cep_standard.debug('>>CE_CASH_FCST.valid_col_range');
919
920 G_min_col := CEFC_VIEW_CONST.get_min_col;
921 G_max_col := CEFC_VIEW_CONST.get_max_col;
922 G_invalid_overdue := FALSE;
923 cep_standard.debug('G_min_col = '||to_char(G_min_col));
924 cep_standard.debug('G_max_col = '||to_char(G_max_col));
925
926 OPEN cCol;
927 FETCH cCol INTO cid, col_num, days_from, days_to;
928 LOOP
929 EXIT WHEN cCol%NOTFOUND OR cCol%NOTFOUND IS NULL;
930
931 IF( days_from < G_min_col OR
932 days_from > G_max_col OR
933 days_to < G_min_col OR
934 days_to > G_max_col) THEN
935
936 all_valid := FALSE;
937 FND_MESSAGE.set_name ('CE','CE_FC_COLUMN_NOT_IN_RANGE');
938 FND_MESSAGE.set_token('COLUMN', col_num);
939 error_msg := FND_MESSAGE.GET;
940 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id,
941 null,'CE_FC_COLUMN_NOT_IN_RANGE', error_msg);
942 END IF;
943
944 IF(col_num = 0 AND days_to < G_min_col )THEN
945 G_invalid_overdue := TRUE;
946 END IF;
947
948 FETCH cCol INTO cid, col_num, days_from, days_to;
949 END LOOP;
950
951 cep_standard.debug('<<CE_CASH_FCST.valid_col_range');
952 return(all_valid);
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 IF(cCol%ISOPEN)THEN CLOSE cCol; END IF;
960
957 cep_standard.debug('EXCEPTION: CE_CASH_FCST.valid_col_range');
958 RAISE;
959 END valid_col_range;
961
962 /* ---------------------------------------------------------------------
963 | PRIVATE PROCEDURE |
964 | valid_row_info |
965 | DESCRIPTION |
966 | Validate row information to ensure all information in a row is |
967 | valid |
968 | CALLED BY |
969 | valid_forecast_run |
970 | HISTORY |
971 | 21-AUG-1997 Created Wynne Chan |
972 --------------------------------------------------------------------- */
973 FUNCTION valid_row_info RETURN BOOLEAN IS
974 error_msg FND_NEW_MESSAGES.message_text%TYPE;
975 valid_row BOOLEAN := TRUE;
976 row_exists BOOLEAN := FALSE;
977 BEGIN
978 G_gl_cash_only := FALSE;
979
980 OPEN f_row_cursor (G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
981 LOOP
982 FETCH f_row_cursor INTO G_rowid, G_forecast_row_id,
983 G_row_number,G_trx_type,
984 G_lead_time, G_forecast_method,
985 G_discount_option,G_app_short_name, G_include_float_flag,
986 G_order_status, G_order_date_type,
987 G_code_combination_id, G_budget_name,
988 G_encumbrance_type_id, G_chart_of_accounts_id ,
989 G_set_of_books_id, G_org_id, G_legal_entity_id,
990 G_roll_forward_type, G_roll_forward_period,
991 G_include_dispute_flag, G_sales_stage_id,G_channel_code,
992 G_win_probability, G_sales_forecast_status, G_customer_profile_class_id,
993 G_bank_account_id, G_receipt_method_id,G_vendor_type,
994 G_payment_method, G_pay_group,G_payment_priority,
995 G_authorization_status, G_type, G_budget_type, G_budget_version,
996 G_include_hold_flag, G_include_net_cash_flag, G_budget_version_id,
997 G_payroll_id, G_xtr_bank_account, G_company_code, G_exclude_indic_exp, G_org_payment_method_id,
998 G_external_source_type, G_criteria_category,
999 G_criteria1, G_criteria2, G_criteria3, G_criteria4, G_criteria5,
1000 G_criteria6, G_criteria7, G_criteria8, G_criteria9, G_criteria10,
1001 G_criteria11, G_criteria12, G_criteria13, G_criteria14, G_criteria15,
1002 G_use_average_payment_days, G_apd_period, G_order_type_id,
1003 G_use_payment_terms, G_include_temp_labor_flag;
1004
1005 EXIT WHEN f_row_cursor%NOTFOUND OR f_row_cursor%NOTFOUND IS NULL;
1006 row_exists := TRUE;
1007
1008 IF (G_aging_type = 'A') THEN
1009 IF( NOT valid_calendar_name )THEN
1010 valid_row := FALSE;
1011 END IF;
1012 END IF;
1013
1014 END LOOP;
1015 CLOSE f_row_cursor;
1016
1017 IF( NOT row_exists )THEN
1018 -- If row does not exist, this implies either no rows or only gl cash position row.
1019 -- The following cursor retrieve any row in this case to confirm that there is at
1020 -- least one gl cash position before flagging an error.
1021 OPEN f_glc_cursor(G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
1022 FETCH f_glc_cursor INTO G_rowid, G_forecast_row_id, G_row_number,
1023 G_set_of_books_id, G_code_combination_id, G_chart_of_accounts_id;
1024 IF f_glc_cursor%found THEN
1025 G_gl_cash_only := TRUE;
1026 END IF;
1027
1028 CLOSE f_glc_cursor;
1029
1030
1031 IF (NOT G_gl_cash_only) THEN
1032
1033 valid_row := FALSE;
1034 FND_MESSAGE.set_name('CE', 'CE_FC_NO_ROW');
1035 error_msg := fnd_message.get;
1036 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, null,
1037 'CE_FC_NO_ROW', error_msg);
1038 END IF;
1039 END IF;
1040
1041 return valid_row;
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 IF f_row_cursor%ISOPEN THEN CLOSE f_row_cursor; END IF;
1045 cep_standard.debug('EXCEPTION: CE_CASH_FCST.valid_row_info');
1046 RAISE;
1047 END valid_row_info;
1048
1049
1050 /* ---------------------------------------------------------------------
1051 | PRIVATE FUNCTION |
1052 | standalone_process |
1053 | parent_process |
1054 | child_process |
1055 | wrap_up_process |
1056 | DESCRIPTION |
1057 | Determine if the current process is a standalone process, |
1058 | parent process, child process, or wrap-up process (the |
1059 | finishing part to be called by parent process) |
1060 | CALLED BY |
1061 | create_forecast |
1062 | HISTORY |
1063 | 21-AUG-1997 Created Wynne Chan |
1064 --------------------------------------------------------------------- */
1065 FUNCTION standalone_process RETURN BOOLEAN IS
1066 BEGIN
1067 return (G_rp_sub_request = 'N');
1068 END;
1069
1070 FUNCTION parent_process(req_data VARCHAR2) RETURN BOOLEAN IS
1071 BEGIN
1072 return (G_parent_process and req_data IS NULL);
1073 END;
1074
1075 FUNCTION child_process RETURN BOOLEAN IS
1076 BEGIN
1077 return (G_rp_sub_request = 'Y' AND NOT G_parent_process);
1078 END;
1079
1080 FUNCTION wrap_up_process(req_data VARCHAR2) RETURN BOOLEAN IS
1081 BEGIN
1082 return( req_data IS NOT NULL);
1083 END;
1084
1085 /* ---------------------------------------------------------------------
1089 | Called by the parent process to submit forecast request for |
1086 | PRIVATE PROCEDURE |
1087 | submit_child_requests |
1088 | DESCRIPTION |
1090 | each row requested by the user |
1091 | CALLED BY |
1092 | create_forecast |
1093 | HISTORY |
1094 | 21-AUG-1997 Created Wynne Chan |
1095 --------------------------------------------------------------------- */
1096 PROCEDURE submit_child_requests IS
1097 request_id NUMBER;
1098 CURSOR CRowNumber(p_forecast_header_id NUMBER, p_rownum_from NUMBER, p_rownum_to NUMBER) IS
1099 SELECT row_number
1100 FROM ce_forecast_rows
1101 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
1102 NVL(p_rownum_to, row_number)
1103 AND forecast_header_id = p_forecast_header_id
1104 AND trx_type <> 'GLC';
1105
1106 BEGIN
1107 cep_standard.debug('>> CE_CASH_FCST.submit_child_requests ');
1108
1109 open CRowNumber(G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
1110 LOOP
1111 FETCH CRowNumber INTO G_row_number;
1112 EXIT WHEN CRowNumber%NOTFOUND OR CRowNumber%NOTFOUND IS NULL;
1113
1114 IF(G_rp_forecast_start_date IS NOT NULL)THEN
1115 request_id := FND_REQUEST.SUBMIT_REQUEST(
1116 'CE', 'CEFCSTBD',to_char(G_row_number),'',TRUE,
1117 G_rp_forecast_header_id,
1118 G_rp_forecast_runname,
1119 G_rp_factor,
1120 G_start_project_no,
1121 G_end_project_no,
1122 G_rp_calendar_name,
1123 to_char(G_rp_forecast_start_date,'YYYY/MM/DD HH24:MI:SS'),
1124 G_rp_forecast_currency,
1125 G_rp_src_curr_type,
1126 null,
1127 G_rp_src_currency,
1128 to_char(G_rp_exchange_date,'YYYY/MM/DD HH24:MI:SS'),
1129 G_rp_exchange_type,
1130 G_rp_exchange_rate,
1131 to_char(G_row_number),
1132 to_char(G_row_number),
1133 G_rp_amount_threshold,
1134 'Y',
1135 G_rp_view_by,
1136 null,
1137 G_rp_bank_balance_type,
1138 G_rp_float_type,
1139 G_rp_include_sub_account,
1140 to_char(G_forecast_id),
1141 G_display_debug,
1142 G_debug_path,
1143 G_debug_file,
1144 'N',
1145 fnd_global.local_chr(0),'',
1146 '','','','','','','','','','',
1147 '','','','','','','','','','',
1148 '','','','','','','','','','',
1149 '','','','','','','','','','',
1150 '','','','','','','','','','',
1151 '','','','','','','','','','');
1152 ELSE
1153 request_id := FND_REQUEST.SUBMIT_REQUEST(
1154 'CE', 'CEFCSHAP',to_char(G_row_number),'',TRUE,
1155 G_rp_forecast_header_id,
1156 G_rp_forecast_runname,
1157 G_rp_factor,
1158 G_start_project_no,
1159 G_end_project_no,
1160 G_rp_calendar_name,
1161 G_rp_forecast_start_period,
1162 G_rp_forecast_currency,
1163 G_rp_src_curr_type,
1164 null,
1165 G_rp_src_currency,
1166 to_char(G_rp_exchange_date,'YYYY/MM/DD HH24:MI:SS'),
1167 G_rp_exchange_type,
1168 G_rp_exchange_rate,
1169 to_char(G_row_number),
1170 to_char(G_row_number),
1171 G_rp_amount_threshold,
1172 'Y',
1173 G_rp_view_by,
1174 null,
1175 G_rp_bank_balance_type,
1176 G_rp_float_type,
1177 G_rp_include_sub_account,
1178 to_char(G_forecast_id),
1179 null,
1180 G_display_debug,
1181 G_debug_path,
1182 G_debug_file,
1183 'N',
1184 fnd_global.local_chr(0),
1185 '','','','','','','','','','',
1186 '','','','','','','','','','',
1187 '','','','','','','','','','',
1188 '','','','','','','','','','',
1189 '','','','','','','','','','',
1190 '','','','','','','','','','',
1191 '','','','','','','','','','');
1192 END IF;
1193 END LOOP;
1194 CLOSE CRowNumber;
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 IF CRowNumber%ISOPEN THEN CLOSE CRowNumber; END IF;
1198 cep_standard.debug('EXCEPTION: CE_CASH_FCST.submit_child_requests');
1199 RAISE;
1200 END submit_child_requests;
1201
1202 /* ---------------------------------------------------------------------
1203 | PRIVATE PROCEDURE |
1204 | create_forecast_header |
1205 | DESCRIPTION |
1206 | Create forecast header for new forecast in ce_forecasts table |
1207 | CALLED BY |
1208 | create_forecast |
1209 | HISTORY |
1210 | 12-JUL-1996 Created Bidemi Carrol |
1211 --------------------------------------------------------------------- */
1212 PROCEDURE create_forecast_header IS
1213 l_forecast_rowid VARCHAR2(30);
1214 fid NUMBER;
1215 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1219 l_request_id NUMBER;
1216 duplicate_name BOOLEAN DEFAULT FALSE;
1217 l_fc_count NUMBER;
1218 l_reqid NUMBER;
1220 BEGIN
1221 --
1222 -- Get original request id
1223 --
1224 fnd_profile.get('CONC_REQUEST_ID', l_reqid);
1225 l_request_id := to_number(l_reqid);
1226
1227 IF (G_rp_forecast_runname IS NULL) THEN
1228 G_rp_forecast_runname:= G_forecast_name||'/'||to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
1229 ELSE
1230 BEGIN
1231 IF G_forecast_id is null THEN
1232 SELECT forecast_id
1233 INTO fid
1234 FROM ce_forecasts
1235 WHERE name = G_rp_forecast_runname;
1236 ELSE
1237 SELECT forecast_id
1238 INTO fid
1239 FROM ce_forecasts
1240 WHERE name = G_rp_forecast_runname
1241 AND forecast_id <> G_forecast_id;
1242 END IF;
1243
1244 duplicate_name := TRUE;
1245 G_rp_forecast_runname := G_rp_forecast_runname||'/'||to_char(sysdate,'DD-MON-RRRR HH:MI:SS');
1246 EXCEPTION
1247 WHEN NO_DATA_FOUND THEN
1248 null;
1249 END;
1250 END IF;
1251
1252 SELECT count(1)
1253 INTO l_fc_count
1254 FROM ce_forecasts
1255 WHERE forecast_id = G_forecast_id;
1256
1257 IF l_fc_count = 0 THEN
1258 CE_FORECASTS_TABLE_PKG.Insert_Row(
1259 X_Rowid => l_forecast_rowid,
1260 X_forecast_id => G_forecast_id,
1261 X_forecast_header_id => G_rp_forecast_header_id,
1262 X_name => G_rp_forecast_runname,
1263 X_description => null,
1264 X_start_date => G_rp_forecast_start_date,
1265 X_period_set_name => G_rp_calendar_name,
1266 X_start_period => G_rp_forecast_start_period,
1267 X_forecast_currency => G_rp_forecast_currency,
1268 X_currency_type => NVL(G_rp_src_curr_type,'F'),
1269 X_source_currency => G_rp_src_currency,
1270 X_exchange_rate_type => G_rp_exchange_type,
1271 X_exchange_date => G_rp_exchange_date,
1272 X_exchange_rate => G_rp_exchange_rate,
1273 X_error_status => 'R',
1274 X_amount_threshold => G_rp_amount_threshold,
1275 X_project_id => G_rp_project_id,
1276 X_drilldown_flag => 'Y',
1277 X_bank_balance_type => nvl(G_rp_bank_balance_type,'L'),
1278 X_float_type => nvl(G_rp_float_type,'NONE'),
1279 X_view_by => nvl(G_rp_view_by,'NONE'),
1280 X_include_sub_account => nvl(G_rp_include_sub_account,'N'),
1281 X_factor => nvl(G_rp_factor,0),
1282 X_request_id => l_request_id,
1283 X_created_by => nvl(fnd_global.user_id, -1),
1284 X_creation_date => sysdate,
1285 X_last_updated_by => nvl(fnd_global.user_id, -1),
1286 X_last_update_date => sysdate,
1287 X_last_update_login => nvl(fnd_global.user_id, -1),
1288 X_attribute_category => null,
1289 X_attribute1 => null,
1290 X_attribute2 => null,
1291 X_attribute3 => null,
1292 X_attribute4 => null,
1293 X_attribute5 => null,
1294 X_attribute6 => null,
1295 X_attribute7 => null,
1296 X_attribute8 => null,
1297 X_attribute9 => null,
1298 X_attribute10 => null,
1299 X_attribute11 => null,
1300 X_attribute12 => null,
1301 X_attribute13 => null,
1302 X_attribute14 => null,
1303 X_attribute15 => null);
1304 commit;
1305 END IF;
1306
1307 IF(duplicate_name)THEN
1308 FND_MESSAGE.set_name ('CE','CE_FC_DUPLICATE_FORECAST_NAME');
1309 error_msg := FND_MESSAGE.GET;
1310 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id,
1311 null,'CE_FC_DUPLICATE_FORECAST_NAME', error_msg);
1312 UPDATE ce_forecasts
1313 SET error_status = 'X'
1314 WHERE forecast_id = G_forecast_id;
1315 END IF;
1316
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 cep_standard.debug('EXCEPTION: CE_CASH_FCST.create_forecast_header ');
1320 RAISE;
1321 END create_forecast_header;
1322
1323 /* ---------------------------------------------------------------------
1324 | PUBLIC FUNCTION |
1325 | valid_exchange_pmr |
1326 | |
1327 | DESCRIPTION |
1328 | This is used to ensure all SRS parameters are correctly |
1329 | entered. |
1330 | CALLED BY |
1331 | |
1332 | REQUIRES |
1333 | |
1334 | HISTORY |
1335 | 26-SEP-1997 Created Wynne Chan |
1336 --------------------------------------------------------------------- */
1337 FUNCTION valid_exchange_pmr RETURN BOOLEAN IS
1338 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1339 valid_pmr BOOLEAN := TRUE;
1340 BEGIN
1341 IF((G_rp_src_curr_type = 'E' AND
1342 G_rp_forecast_currency <> G_rp_src_currency) OR
1343 G_rp_src_curr_type IN ('F', 'A'))THEN
1344 IF(G_rp_exchange_date IS NULL OR
1345 G_rp_exchange_type IS NULL )THEN
1346 valid_pmr := FALSE;
1347 END IF;
1348 END IF;
1352 valid_pmr := FALSE;
1349
1350 IF (G_rp_exchange_type = 'User' AND
1351 G_rp_exchange_rate IS NULL)THEN
1353 END IF;
1354
1355 IF(not valid_pmr)THEN
1356 FND_MESSAGE.set_name('CE', 'CE_FC_PMR_MISSING_XINFO');
1357 error_msg := fnd_message.get;
1358 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, null,
1359 'CE_FC_PMR_MISSING_XINFO', error_msg);
1360 END IF;
1361
1362 return valid_pmr;
1363 END valid_exchange_pmr;
1364
1365
1366 /* ---------------------------------------------------------------------
1367 | PUBLIC FUNCTION |
1368 | valid_forecast_run |
1369 | |
1370 | DESCRIPTION |
1371 | This is used to ensure all SRS parameters are correctly |
1372 | entered. |
1373 | CALLED BY |
1374 | |
1375 | REQUIRES |
1376 | |
1377 | HISTORY |
1378 | 26-SEP-1997 Created Wynne Chan |
1379 --------------------------------------------------------------------- */
1380 FUNCTION valid_forecast_run RETURN BOOLEAN IS
1381 valid_pmr BOOLEAN := TRUE;
1382 BEGIN
1383 IF( NOT valid_exchange_pmr )THEN
1384 valid_pmr := FALSE;
1385 END IF;
1386
1387 IF( NOT valid_col_info )THEN
1388 valid_pmr := FALSE;
1389 END IF;
1390
1391 IF( NOT valid_row_info )THEN
1392 valid_pmr := FALSE;
1393 END IF;
1394
1395 IF(NOT valid_pmr)THEN
1396 UPDATE ce_forecasts
1397 SET error_status = 'E'
1398 WHERE forecast_id = G_forecast_id;
1399
1400 cep_standard.debug('Forecast NOT run');
1401 commit;
1402 print_report;
1403 END IF;
1404
1405 return valid_pmr;
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 cep_standard.debug('EXCEPTION: CE_CASH_FCST.valid_forecast_run');
1409 END valid_forecast_run;
1410
1411 /* ---------------------------------------------------------------------
1412 | PUBLIC PROCEDURE |
1413 | create_forecast |
1414 | |
1415 | DESCRIPTION |
1416 | This is the main cash forecast procedure |
1417 | Depending if the current run is a parent process, child process |
1418 | standalone process, or the wrap-up process from parent, |
1419 | create_forecast performs perform different task |
1420 | |
1421 | CALLED BY |
1422 | |
1423 | REQUIRES |
1424 | p_forecast_header_id forecast header id |
1425 | p_forecast_start_date forecast date |
1426 | HISTORY |
1427 | 12-JUL-1996 Created Bidemi Carrol |
1428 --------------------------------------------------------------------- */
1429 PROCEDURE create_forecast IS
1430 counter NUMBER;
1431 req_data VARCHAR2(30);
1432 l_status VARCHAR2(1);
1433 col_setup VARCHAR2(1);
1434 BEGIN
1435 cep_standard.debug('>>CE_CASH_FCST.create_forecast');
1436
1437 counter := 0;
1438 --
1439 -- Get forecast header info
1440 --
1441 OPEN f_header_cursor(G_rp_forecast_header_id);
1442 FETCH f_header_cursor INTO G_forecast_name, G_aging_type,
1443 G_overdue_transactions, G_transaction_calendar_id,
1444 G_start_project_id, G_end_project_id;
1445 CLOSE f_header_cursor;
1446 cep_standard.debug('Aging type: ' || G_aging_type);
1447 cep_Standard.debug('Name: '|| G_forecast_name);
1448
1449 FND_CURRENCY.get_info(G_rp_forecast_currency, G_precision, G_ext_precision, G_min_acct_unit);
1450
1451 IF(G_overdue_transactions = 'INCLUDE')THEN
1452 BEGIN
1453 SELECT forecast_column_id
1454 INTO G_overdue_column_id
1455 FROM ce_forecast_columns
1456 WHERE forecast_header_id = G_rp_forecast_header_id AND
1457 developer_column_num = 0;
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 cep_standard.debug('ERROR: cannot get overdue column id');
1461 RAISE;
1462 END;
1463 END IF;
1464
1465 req_data := fnd_conc_global.request_data;
1466 if(req_data IS NOT NULL)THEN
1467 G_forecast_id := to_number(req_data);
1468 END IF;
1469
1470 if( parent_process(req_data) OR standalone_process)THEN
1471
1472 IF(G_transaction_calendar_id IS NOT NULL)THEN
1473 validate_transaction_calendar;
1474 END IF;
1475
1476 create_forecast_header;
1477
1478 IF( NOT valid_forecast_run )THEN
1479 return;
1480 END IF;
1481
1482 --
1483 -- Populate exchange information from GL for forecast currency
1484 --
1485 IF( CE_CASH_FCST.G_rp_exchange_type <> 'User' OR
1486 CE_CASH_FCST.G_rp_exchange_type IS NULL )THEN
1487 IF( NOT populate_xrate_table ) THEN
1488 UPDATE ce_forecasts
1489 SET error_status = 'X'
1490 WHERE forecast_id = G_forecast_id;
1491 END IF;
1492 END IF;
1493
1494 --
1495 -- Populate exchange information from GL for base currency.
1499 --
1496 -- This is done specifically for the OE transactions since OE
1497 -- transaction stores the order amount in transaction currency
1498 -- only and not in functional currency.
1500
1501 IF (NOT G_gl_cash_only) THEN
1502 IF( CE_CASH_FCST.G_rp_src_curr_type = 'F' AND
1503 CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL )THEN
1504 IF( NOT populate_base_xrate_table )THEN
1505 UPDATE ce_forecasts
1506 set error_status = 'X'
1507 where forecast_id = G_forecast_id;
1508 END IF;
1509 END IF;
1510 END IF;
1511
1512 IF( NOT valid_col_range )THEN
1513 UPDATE ce_forecasts
1514 SET error_status = 'X'
1515 WHERE forecast_id = G_forecast_id;
1516 END IF;
1517 END IF;
1518
1519 IF(parent_process(req_data))THEN
1520 IF (NOT G_gl_cash_only) THEN
1521 submit_child_requests;
1522 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1523 request_data => to_char(G_forecast_id));
1524 END IF;
1525 END IF;
1526
1527 -- Add validation here for bug 1346485.
1528 IF(standalone_process OR child_process)THEN
1529 if (not valid_row_info) then
1530 return;
1531 end if;
1532 END IF;
1533
1534 IF ((standalone_process AND (NOT G_gl_cash_only)) OR child_process) THEN
1535 OPEN f_row_cursor(G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
1536 cep_standard.debug('Forecast defn valid');
1537
1538 -- If column setup is automatic populate CE_FORECAST_COLUMNS
1539 -- before populating cells.
1540 SELECT nvl(column_setup,'M')
1541 INTO col_setup
1542 FROM ce_forecast_headers
1543 WHERE forecast_header_id = G_rp_forecast_header_id;
1544
1545 /*
1546 IF (col_setup = 'A') THEN
1547 CE_FORECAST_UTILS.populate_temp_buckets(G_rp_forecast_header_id, G_rp_forecast_start_date);
1548 END IF;
1549 */
1550
1551 LOOP
1552 FETCH f_row_cursor INTO G_rowid, G_forecast_row_id,
1553 G_row_number, G_trx_type,
1554 G_lead_time, G_forecast_method,
1555 G_discount_option, G_app_short_name,G_include_float_flag,
1556 G_order_status, G_order_date_type,
1557 G_code_combination_id, G_budget_name,
1558 G_encumbrance_type_id, G_chart_of_accounts_id ,
1559 G_set_of_books_id, G_org_id, G_legal_entity_id,
1560 G_roll_forward_type, G_roll_forward_period,
1561 G_include_dispute_flag, G_sales_stage_id,G_channel_code,
1562 G_win_probability, G_sales_forecast_status, G_customer_profile_class_id,
1563 G_bank_account_id, G_receipt_method_id, G_vendor_type,
1564 G_payment_method, G_pay_group,G_payment_priority,
1565 G_authorization_status, G_type, G_budget_type, G_budget_version,
1566 G_include_hold_flag, G_include_net_cash_flag, G_budget_version_id,
1567 G_payroll_id, G_xtr_bank_account, G_company_code, G_exclude_indic_exp, G_org_payment_method_id,
1568 G_external_source_type, G_criteria_category,
1569 G_criteria1, G_criteria2, G_criteria3, G_criteria4, G_criteria5,
1570 G_criteria6, G_criteria7, G_criteria8, G_criteria9, G_criteria10,
1571 G_criteria11, G_criteria12, G_criteria13, G_criteria14, G_criteria15,
1572 G_use_average_payment_days, G_apd_period,
1573 G_order_type_id, G_use_payment_terms, G_include_temp_labor_flag;
1574 EXIT WHEN f_row_cursor%NOTFOUND OR f_row_cursor%NOTFOUND IS NULL;
1575
1576 --
1577 -- Set Changing View Constants
1578 --
1579 CEFC_VIEW_CONST.set_rowid(G_rowid);
1580 CEFC_VIEW_CONST.set_constants(G_rp_forecast_header_id,
1581 G_rp_calendar_name, G_rp_forecast_start_period,
1582 G_rp_forecast_start_date, G_min_col, G_max_col);
1583 G_invalid_overdue_row := G_invalid_overdue;
1584
1585 cep_standard.debug('Calling Pop Cells...for trx : ' || G_trx_type||
1586 ' and row_number :' || G_row_number);
1587 CE_CSH_FCST_POP.Populate_Cells;
1588 END LOOP;
1589 CLOSE f_row_cursor;
1590 END IF;
1591
1592 SELECT error_status
1593 INTO l_status
1594 FROM ce_forecasts
1595 WHERE forecast_id = G_forecast_id;
1596
1597 IF (wrap_up_process(req_data) OR standalone_process) THEN
1598 clear_xrate_table;
1599 commit;
1600 print_report;
1601
1602 IF l_status <> 'E' THEN
1603 print_forecast_report;
1604 END IF;
1605 END IF;
1606
1607 IF l_status = 'R' THEN
1608 UPDATE ce_forecasts
1609 SET error_status = 'S'
1610 WHERE forecast_id = G_forecast_id;
1611 ELSIF l_status = 'X' THEN
1612 UPDATE ce_forecasts
1613 SET error_status = 'W'
1614 WHERE forecast_id = G_forecast_id;
1615 END IF;
1616
1617 return;
1618
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 IF f_row_cursor%ISOPEN THEN CLOSE f_row_cursor; END IF;
1622 IF f_header_cursor%ISOPEN THEN CLOSE f_header_cursor; END IF;
1623 cep_standard.debug('EXCEPTION: CE_CASH_FCST.cash_forecast');
1624 RAISE;
1625 END create_forecast;
1626
1627
1628 /* ---------------------------------------------------------------------
1629 | PUBLIC PROCEDURE |
1630 | Forecast |
1631 | |
1632 | DESCRIPTION |
1633 | The forecast program is divided into two parts just for easy |
1634 | submission. This procedure only calls doesn't do much but calls |
1638 | REQUIRES |
1635 | other procedures which do the work |
1636 | CALLED BY |
1637 | |
1639 | p_forecast_header_id template id |
1640 | p_forecast_runname forecast name |
1641 | p_forecast_start_period start forecast at this period |
1642 | p_forecast_currency amount currency |
1643 | p_exchange_type exchange type |
1644 | p_exchange_date exchange date |
1645 | p_src_curr_type functional/entered |
1646 | p_src_currency filter currency for transactions |
1647 | p_rownum_from which rows |
1648 | p_rownum_to |
1649 | p_dummy just to differentiate this procedure |
1650 | p_project_id project_id |
1651 | HISTORY |
1652 | 12-JUL-1996 Created Bidemi Carrol |
1653 --------------------------------------------------------------------- */
1654 PROCEDURE Forecast(errbuf OUT NOCOPY VARCHAR2,
1655 retcode OUT NOCOPY NUMBER,
1656 p_forecast_header_id IN NUMBER,
1657 p_forecast_runname IN VARCHAR2,
1658 p_factor IN NUMBER,
1659 p_start_project_num IN VARCHAR2,
1660 p_end_project_num IN VARCHAR2,
1661 p_calendar_name IN VARCHAR2,
1662 p_forecast_start_period IN VARCHAR2,
1663 p_forecast_currency IN VARCHAR2,
1664 p_src_curr_type IN VARCHAR2,
1665 p_src_curr_dummy in varchar2,
1666 p_src_currency IN VARCHAR2,
1667 p_exchange_date IN VARCHAR2,
1668 p_exchange_type IN VARCHAR2,
1669 p_exchange_rate IN NUMBER,
1670 p_rownum_from IN NUMBER,
1671 p_rownum_to IN NUMBER,
1672 p_amount_threshold_x IN VARCHAR2,
1673 p_sub_request IN VARCHAR2,
1674 p_view_by IN VARCHAR2,
1675 p_view_dummy IN VARCHAR2,
1676 p_bank_balance_type IN VARCHAR2,
1677 p_float_type IN VARCHAR2,
1678 p_include_sub_account IN VARCHAR2,
1679 p_forecast_id IN NUMBER,
1680 p_dummy IN VARCHAR2,
1681 p_display_debug IN VARCHAR2,
1682 p_debug_path IN VARCHAR2,
1683 p_debug_file IN VARCHAR2,
1684 p_fc_name_exists IN VARCHAR2)IS
1685 CURSOR C_fpid IS
1686 SELECT project_id, segment1
1687 FROM pa_projects_all
1688 WHERE segment1 >= p_start_project_num
1689 AND segment1 <= p_end_project_num;
1690 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1691 p_amount_threshold NUMBER;
1692 BEGIN
1693
1694 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
1695 CEP_STANDARD.init_security; --for bug 5702438
1696
1697 -- Now the process is officially 'Running' and not 'Pending'
1698 --bug 4345353 convert amount threshold from canonical to number
1699 p_amount_threshold := fnd_number.canonical_to_number(p_amount_threshold_x);
1700 UPDATE ce_forecasts
1701 SET error_status = 'R'
1702 WHERE forecast_id = p_forecast_id
1703 AND error_status = 'P';
1704 commit;
1705
1706 IF (p_fc_name_exists = 'Y') THEN
1707 FND_MESSAGE.set_name ('CE','CE_FC_DUPLICATE_FORECAST_NAME');
1708 error_msg := FND_MESSAGE.GET;
1709 CE_FORECAST_ERRORS_PKG.insert_row(p_forecast_id,p_forecast_header_id,
1710 null,'CE_FC_DUPLICATE_FORECAST_NAME', error_msg);
1711 UPDATE ce_forecasts
1712 SET error_status = 'X'
1713 WHERE forecast_id = p_forecast_id;
1714 END IF;
1715
1716 If (p_display_debug = 'Y') then
1717 cep_standard.enable_debug(p_debug_path,
1718 p_debug_file);
1719 end if;
1720 cep_standard.debug('>>CE_CASH_FCST.Forecast');
1721
1722 IF (p_start_project_num IS NULL AND
1723 p_end_project_num IS NULL) THEN
1724 set_parameters( p_forecast_header_id,
1725 p_forecast_runname,
1726 NULL,
1727 p_calendar_name,
1728 p_forecast_start_period,
1729 p_forecast_currency,
1730 p_exchange_type,
1731 p_exchange_date,
1732 p_exchange_rate,
1733 p_src_curr_type,
1734 p_src_currency,
1735 p_amount_threshold,
1736 NULL,
1737 p_rownum_from,
1738 p_rownum_to,
1739 p_sub_request,
1740 p_factor,
1741 p_include_sub_account,
1742 p_view_by,
1743 p_bank_balance_type,
1744 p_float_type,
1745 p_forecast_id,
1746 p_display_debug,
1747 p_debug_path,
1748 p_debug_file);
1749 create_forecast;
1750 ELSE
1751 FOR fpid_rec IN C_fpid LOOP
1752 set_parameters( p_forecast_header_id,
1753 p_forecast_runname || '-' || fpid_rec.segment1,
1754 NULL,
1755 p_calendar_name,
1756 p_forecast_start_period,
1757 p_forecast_currency,
1758 p_exchange_type,
1759 p_exchange_date,
1760 p_exchange_rate,
1761 p_src_curr_type,
1762 p_src_currency,
1763 p_amount_threshold,
1764 fpid_rec.project_id,
1765 p_rownum_from,
1766 p_rownum_to,
1767 p_sub_request,
1768 p_factor,
1769 p_include_sub_account,
1770 p_view_by,
1771 p_bank_balance_type,
1772 p_float_type,
1773 p_forecast_id,
1774 p_display_debug,
1775 p_debug_path,
1776 p_debug_file);
1777 create_forecast;
1778 END LOOP;
1779 IF (p_display_debug = 'Y') THEN
1780 cep_standard.disable_debug(p_display_debug);
1781 END IF;
1782 END IF;
1783
1784 END Forecast;
1785
1786
1787
1788 /* ---------------------------------------------------------------------
1789 | PUBLIC PROCEDURE |
1790 | Forecast |
1791 | |
1795 | other procedures which do the work |
1792 | DESCRIPTION |
1793 | The forecast program is divided into two parts just for easy |
1794 | submission. This procedure only calls doesn't do much but calls |
1796 | CALLED BY |
1797 | |
1798 | REQUIRES |
1799 | p_forecast_header_id template id |
1800 | p_forecast_runname forecast name |
1801 | p_forecast_start_date start forecast on this date |
1802 | p_forecast_currency amount currency |
1803 | p_exchange_type exchange type |
1804 | p_exchange_date exchange date |
1805 | p_src_curr_type functional/entered |
1806 | p_src_currency filter currency for transactions |
1807 | p_rownum_from which rows |
1808 | p_rownum_to |
1809 | p_project_id project id |
1810 | |
1811 | HISTORY |
1812 | 12-JUL-1996 Created Bidemi Carrol |
1813 --------------------------------------------------------------------- */
1814 PROCEDURE Forecast(errbuf OUT NOCOPY VARCHAR2,
1815 retcode OUT NOCOPY NUMBER,
1816 p_forecast_header_id IN NUMBER,
1817 p_forecast_runname IN VARCHAR2,
1818 p_factor IN NUMBER,
1819 p_start_project_num IN VARCHAR2,
1820 p_end_project_num IN VARCHAR2,
1821 p_calendar_name IN VARCHAR2,
1822 p_forecast_start_date IN VARCHAR2,
1823 p_forecast_currency IN VARCHAR2,
1824 p_src_curr_type IN VARCHAR2,
1825 p_src_curr_dummy in varchar2,
1826 p_src_currency IN VARCHAR2,
1827 p_exchange_date IN VARCHAR2,
1828 p_exchange_type IN VARCHAR2,
1829 p_exchange_rate IN NUMBER,
1830 p_rownum_from IN NUMBER,
1831 p_rownum_to IN NUMBER,
1832 p_amount_threshold_x IN VARCHAR2,
1833 p_sub_request IN VARCHAR2,
1834 p_view_by IN VARCHAR2,
1835 p_view_dummy IN VARCHAR2,
1836 p_bank_balance_type IN VARCHAR2,
1837 p_float_type IN VARCHAR2,
1838 p_include_sub_account IN VARCHAR2,
1839 p_forecast_id IN NUMBER,
1840 p_display_debug IN VARCHAR2,
1841 p_debug_path IN VARCHAR2,
1842 p_debug_file IN VARCHAR2,
1843 p_fc_name_exists IN VARCHAR2) IS
1844 CURSOR C_fpid IS
1845 SELECT project_id, segment1
1846 FROM pa_projects_all
1847 WHERE segment1 >= p_start_project_num
1848 AND segment1 <= p_end_project_num;
1849 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1850 p_amount_threshold NUMBER;
1851 BEGIN
1852 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
1853 CEP_STANDARD.init_security;
1854
1855 -- Now the process is officially 'Running' and not 'Pending'
1856 -- --bug 4345353 convert amount threshold from canonical to number
1857 p_amount_threshold := fnd_number.canonical_to_number(p_amount_threshold_x);
1858
1859 UPDATE ce_forecasts
1860 SET error_status = 'R'
1861 WHERE forecast_id = p_forecast_id
1862 AND error_status = 'P';
1863 commit;
1864
1865 IF (p_fc_name_exists = 'Y') THEN
1866 FND_MESSAGE.set_name ('CE','CE_FC_DUPLICATE_FORECAST_NAME');
1867 error_msg := FND_MESSAGE.GET;
1868 CE_FORECAST_ERRORS_PKG.insert_row(p_forecast_id,p_forecast_header_id,
1869 null,'CE_FC_DUPLICATE_FORECAST_NAME', error_msg);
1870 UPDATE ce_forecasts
1871 SET error_status = 'X'
1872 WHERE forecast_id = p_forecast_id;
1873 END IF;
1874
1875 If (p_display_debug = 'Y') then
1876 cep_standard.enable_debug(p_debug_path,
1877 p_debug_file);
1878 end if;
1879 G_start_project_no := p_start_project_num;
1880 G_end_project_no := p_end_project_num;
1881
1882 IF (p_start_project_num IS NULL AND
1883 p_end_project_num IS NULL) THEN
1884 set_parameters( p_forecast_header_id,
1885 p_forecast_runname,
1886 p_forecast_start_date,
1887 p_calendar_name,
1888 NULL,
1889 p_forecast_currency,
1890 p_exchange_type,
1891 p_exchange_date,
1892 p_exchange_rate,
1893 p_src_curr_type,
1894 p_src_currency,
1895 p_amount_threshold,
1896 NULL,
1897 p_rownum_from,
1898 p_rownum_to,
1899 p_sub_request,
1900 p_factor,
1901 p_include_sub_account,
1902 p_view_by,
1903 p_bank_balance_type,
1904 p_float_type,
1905 p_forecast_id,
1906 p_display_debug,
1907 p_debug_path,
1908 p_debug_file);
1909 create_forecast;
1910 ELSE
1911 FOR fpid_rec IN C_fpid LOOP
1912 set_parameters( p_forecast_header_id,
1913 p_forecast_runname || '-' || fpid_rec.segment1,
1914 p_forecast_start_date,
1915 p_calendar_name,
1916 NULL,
1917 p_forecast_currency,
1918 p_exchange_type,
1919 p_exchange_date,
1920 p_exchange_rate,
1921 p_src_curr_type,
1922 p_src_currency,
1923 p_amount_threshold,
1924 fpid_rec.project_id,
1925 p_rownum_from,
1926 p_rownum_to,
1927 p_sub_request,
1928 p_factor,
1929 p_include_sub_account,
1930 p_view_by,
1931 p_bank_balance_type,
1932 p_float_type,
1933 p_forecast_id,
1934 p_display_debug,
1935 p_debug_path,
1936 p_debug_file);
1937 create_forecast;
1938 END LOOP;
1939 END IF;
1940 IF (p_display_debug = 'Y') THEN
1941 cep_standard.disable_debug(p_display_debug);
1942 END IF;
1943 END Forecast;
1944
1945
1946
1947
1948 END CE_CASH_FCST;