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