[Home] [Help]
PACKAGE BODY: APPS.XTR_CASH_FCST
Source
1 PACKAGE BODY XTR_CASH_FCST AS
2 /* $Header: xtrcshfb.pls 120.4 2005/10/05 20:19:09 eaggarwa 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, cfh.treasury_template
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', 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),
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, 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 FROM ce_forecast_rows
37 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
38 NVL(p_rownum_to, row_number)
39 AND forecast_header_id = p_forecast_header_id
40 AND trx_type <> 'GLC';
41
42 CURSOR f_glc_cursor (p_forecast_header_id NUMBER, p_rownum_from NUMBER, p_rownum_to NUMBER) IS
43 SELECT rowid, forecast_row_id, row_number,
44 set_of_books_id, code_combination_id, chart_of_accounts_id
45 FROM ce_forecast_rows
46 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
47 NVL(p_rownum_to, row_number)
48 AND forecast_header_id = p_forecast_header_id
49 AND trx_type = 'GLC';
50
51 /* ---------------------------------------------------------------------
52 | PUBLIC PROCEDURE |
53 | set_parameters |
54 | |
55 | DESCRIPTION |
56 | This procedure sets the global parameters |
57 | |
58 | CALLED BY |
59 | |
60 | REQUIRES |
61 | all runtime parameters |
62 | HISTORY |
63 | 04-OCT-1996 Created Bidemi Carrol |
64 --------------------------------------------------------------------- */
65 FUNCTION set_parameters (p_forecast_header_id IN NUMBER,
66 p_forecast_runname IN VARCHAR2,
67 p_forecast_start_date IN VARCHAR2,
68 p_forecast_currency IN VARCHAR2,
69 p_src_curr_type IN VARCHAR2,
70 p_company_code IN VARCHAR2,
71 p_rownum_from IN NUMBER,
72 p_rownum_to IN NUMBER,
73 p_sub_request IN VARCHAR2) RETURN NUMBER IS
74
75 l_is_fixed_rate BOOLEAN;
76 l_relationship VARCHAR2(30);
77 error_msg VARCHAR2(2000);
78 CURSOR C IS SELECT CE_FORECASTS_S.nextval FROM sys.dual;
79 CURSOR H IS SELECT CE_FORECAST_HEADERS_S.nextval FROM sys.dual;
80 BEGIN
81
82 /* In the case where p_src_curr_type = 'Entered', ensure that there is no
83 fixed rate between the forecast and source currencies (both EMU currencies.
84
85
86 If so, then override the p_exchange_type to 'EMU FIXED'. This is to
87 handle the case where the user enters an exchange type of 'User' from
88 the SRS (concurrent submission form) when there is a fixed rate. This
89 scenario can occur due to the inability of flex fields to handle
90 conditional value sets the way CE needs it and will result in erroneous
91 forecast amounts. */
92
93 IF p_forecast_header_id IS NULL THEN
94 BEGIN
95 select forecast_header_id
96 into G_rp_forecast_header_id
97 from ce_forecast_headers
98 where treasury_template = 'Y';
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 OPEN C;
102 FETCH C INTO G_forecast_id;
103 CLOSE C;
104
105 print_report;
106 return (1);
107 END;
108 ELSE
109 G_rp_forecast_header_id := p_forecast_header_id;
110 END IF;
111
112 SELECT legal_entity_id
113 INTO G_rp_legal_entity_id
114 FROM xtr_party_info
115 WHERE party_code = p_company_code
116 AND party_type = 'C';
117
118 /* RV BUG # 1548223 */
119
120 DECLARE
121 l_orgs varchar2(500);
122 CURSOR C_ORG is
123 SELECT DISTINCT(organization_id) organization_id
124 FROM HR_OPERATING_UNITS hou
125 WHERE hou.set_of_books_id = (SELECT glle.ledger_id FROM gl_ledger_le_v glle
126 WHERE glle.legal_entity_id = G_rp_legal_entity_id
127 AND glle.ledger_category_code = 'PRIMARY'); -- bug 4654775
128
129
130 BEGIN
131 FOR r in C_ORG LOOP
132 l_orgs := l_orgs||','||r.organization_id ;
133 END LOOP;
134 l_orgs := '('||nvl(substr(l_orgs,2),NULL)||')';
135 G_rp_org_ids := l_orgs;
136 EXCEPTION
137 WHEN OTHERS THEN
138 -- G_rp_org_ids := NULL;
139 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
140 xtr_debug_pkg.debug('XTR_CASH_FCST.set_parameters-->NO_ORGS');
141 END IF;
142 Raise;
143 END;
144
145 /* RV END */
146
147
148 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
149 xtr_debug_pkg.debug('>>CE_CASH_FCST.set_parameters');
150 END IF;
151 G_rp_forecast_runname := p_forecast_runname;
152 G_rp_forecast_start_date := to_date(p_forecast_start_date,'YYYY/MM/DD HH24:MI:SS');
153 G_rp_forecast_currency := p_forecast_currency;
154 G_rp_src_curr_type := p_src_curr_type;
155 G_rp_rownum_from := p_rownum_from;
156 G_rp_rownum_to := p_rownum_to;
157 G_rp_sub_request := p_sub_request;
158 G_forecast_id := NULL;
159 G_party_code := p_company_code;
160 IF(p_sub_request = 'Y')THEN
161 G_parent_process := TRUE;
162 ELSE
163 G_parent_process := FALSE;
164 END IF;
165 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
166 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_forecast_header_id : '||G_rp_forecast_header_id);
167 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_forecast_runname : '||G_rp_forecast_runname);
168 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_forecast_start_date : '||G_rp_forecast_start_date);
169 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_forecast_start_period : '||G_rp_forecast_start_period);
170 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_forecast_currency : '||G_rp_forecast_currency);
171 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_exchange_type : '||G_rp_exchange_type);
172 -- xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_exchange_date : '||G_rp_exchange_date);
173 -- xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_exchange_rate : '||G_rp_exchange_rate);
174 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_src_curr_type : '||G_rp_src_curr_type);
175 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_src_currency : '||G_rp_src_currency);
176 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_rownum_from : '||G_rp_rownum_from);
177 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_rownum_to : '||G_rp_rownum_to);
178 xtr_debug_pkg.debug('XTR_CASH_FCST.G_rp_sub_request : '||G_rp_sub_request);
179 xtr_debug_pkg.debug('XTR_CASH_FCST.G_forecast_id : '||G_forecast_id);
180 END IF;
181 --
182 -- Set View constants
183 --
184 CEFC_VIEW_CONST.set_constants(G_rp_forecast_header_id,
185 G_rp_calendar_name,
186 G_rp_forecast_start_period,
187 G_rp_forecast_start_date);
188
189 return (0);
190 END;
191
192
193
194 /* ---------------------------------------------------------------------
195 | PUBLIC PROCEDURE |
196 | Print_Report |
197 | |
198 | DESCRIPTION |
199 | This procedure submits a concurrent request to print the |
200 | Cash Forecast Report after a succesful run. |
201 | |
202 | CALLED BY |
203 | |
204 | REQUIRES |
205 | p_forecast_header_id forecast header id |
206 | p_forecast_start_date forecast date |
207 | HISTORY |
208 | 04-OCT-1996 Created Bidemi Carrol |
209 --------------------------------------------------------------------- */
210 PROCEDURE Print_Report IS
211 req_id NUMBER;
212 request_id NUMBER;
213 reqid VARCHAR2(30);
214 number_of_copies NUMBER;
215 printer VARCHAR2(30);
216 print_style VARCHAR2(30);
217 save_output_flag VARCHAR2(30);
218 save_output_bool BOOLEAN;
219 BEGIN
220 --
221 -- Get original request id
222 --
223 fnd_profile.get('CONC_REQUEST_ID', reqid);
224 request_id := to_number(reqid);
225 --
226 -- Get print options
227 --
228 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
229 xtr_debug_pkg.debug('Request Id is ' || request_id);
230 END IF;
231 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
232 number_of_copies,
233 print_style,
234 printer,
235 save_output_flag))THEN
236 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
237 xtr_debug_pkg.debug('Message: get print options failed');
238 END IF;
239 ELSE
240 IF (save_output_flag = 'Y') THEN
241 save_output_bool := TRUE;
242 ELSE
243 save_output_bool := FALSE;
244 END IF;
245 --
246 -- Set print options
247 --
248 IF (NOT FND_REQUEST.set_print_options(printer,
249 print_style,
250 number_of_copies,
251 save_output_bool)) THEN
252 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
253 xtr_debug_pkg.debug('Set print options failed');
254 END IF;
255 END IF;
256 END IF;
257 req_id := FND_REQUEST.SUBMIT_REQUEST('XTR',
258 'XTRFCERR',
259 NULL,
260 trunc(sysdate),
261 FALSE,
262 G_forecast_id);
263
264 END Print_Report;
265
266 /* ---------------------------------------------------------------------
267 | PRIVATE PROCEDURE |
268 | validate_transaction_calendar |
269 | |
270 | DESCRIPTION |
271 | checks to make sure that the period set name for all set of |
272 | books are the same |
273 | CALLED BY |
274 | |
275 | REQUIRES |
276 | |
277 | HISTORY |
278 | 05-AUG-1997 Created Wynne Chan |
279 ---------------------------------------------------------------------*/
280 PROCEDURE validate_transaction_calendar IS
281 BEGIN
282 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
283 xtr_debug_pkg.debug('>>validate_transaction_calendar');
284 END IF;
285
286 SELECT min(transaction_date), max(transaction_date)
287 INTO XTR_CSH_FCST_POP.G_calendar_start, XTR_CSH_FCST_POP.G_calendar_end
288 FROM gl_transaction_dates
289 WHERE transaction_calendar_id = G_transaction_calendar_id;
290
291 IF(XTR_CSH_FCST_POP.G_calendar_start IS NULL OR
292 XTR_CSH_FCST_POP.G_calendar_end IS NULL)THEN
293 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
294 xtr_debug_pkg.debug('Cannot find transaction calendar');
295 END IF;
296 G_transaction_calendar_id := NULL;
297 END IF;
298 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
299 xtr_debug_pkg.debug('<<validate_transaction_calendar');
300 END IF;
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
304 xtr_debug_pkg.debug('EXCEPTION - OTHERS: validate_transaction_calendar');
305 END IF;
306
307 RAISE;
308 END validate_transaction_calendar;
309
310
311 /* ---------------------------------------------------------------------
312 | PUBLIC PROCEDURE |
313 | valid_calendar_name |
314 | |
315 | DESCRIPTION |
316 | checks to make sure that the period set name for all set of |
317 | books are the same |
318 | CALLED BY |
319 | valid_row_info |
320 | HISTORY |
321 | 04-OCT-1996 Created Bidemi Carrol |
322 ---------------------------------------------------------------------*/
323 FUNCTION valid_calendar_name RETURN BOOLEAN IS
324 valid_period BOOLEAN := TRUE;
325 sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
326 calendar GL_PERIODS.period_set_name%TYPE;
327 error_msg fnd_new_messages.message_text%TYPE;
328 sob_name GL_SETS_OF_BOOKS.name%TYPE;
329
330 CURSOR sob_c IS
331 SELECT gsb.period_set_name, org.set_of_books_id, org.set_of_books_name
332 FROM ce_forecast_orgs_v org,
333 gl_sets_of_books gsb
334 WHERE gsb.set_of_books_id = org.set_of_books_id
335 AND org.app_short_name = G_app_short_name
336 AND org.set_of_books_id = NVL(G_set_of_books_id,org.set_of_books_id)
337 AND (org.org_id = DECODE(G_org_id, -1, org.org_id,-99, org.org_id, G_org_id)
338 or org.org_id IS NULL);
339
340 BEGIN
341 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
342 xtr_debug_pkg.debug('>>XTR_CASH_FCST.valid_calendar_name');
343 END IF;
344
345 IF(G_app_short_name = 'GL')THEN
346 SELECT period_set_name, set_of_books_id, name, currency_code
347 INTO calendar, sob_id, sob_name, G_sob_currency_code
348 FROM gl_sets_of_books gsb
349 WHERE gsb.set_of_books_id = G_set_of_books_id;
350
351 IF (calendar <> G_rp_calendar_name) THEN
352 valid_period := FALSE;
353 FND_MESSAGE.set_name('CE', 'CE_FC_INVALID_PERIOD_SET_NAME');
354 FND_MESSAGE.set_token('SOB_NAME', sob_name);
355 error_msg := fnd_message.get;
356 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
357 xtr_debug_pkg.debug(error_msg);
358 END IF;
359 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, G_forecast_row_id,
360 'CE_FC_INVALID_PERIOD', error_msg);
361 END IF;
362 ELSE
363 open sob_c;
364 LOOP
365 FETCH sob_c INTO calendar, sob_id, sob_name;
366 EXIT WHEN sob_c%NOTFOUND or sob_c%NOTFOUND IS NULL;
367
368 IF (calendar <> G_rp_calendar_name) THEN
369 valid_period := FALSE;
370 FND_MESSAGE.set_name('CE', 'CE_FC_INVALID_PERIOD_SET_NAME');
371 FND_MESSAGE.set_token('SOB_NAME', sob_name);
372 error_msg := fnd_message.get;
373 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
374 xtr_debug_pkg.debug(error_msg);
375 END IF;
376 CE_FORECAST_ERRORS_PKG.insert_row(G_forecast_id,G_rp_forecast_header_id, G_forecast_row_id,
377 'CE_FC_INVALID_PERIOD', error_msg);
378 END IF;
379 END LOOP;
380 END IF;
381 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
382 xtr_debug_pkg.debug('<<XTR_CASH_FCST.valid_calendar_name');
383 END IF;
384 return (valid_period);
385 EXCEPTION
386 WHEN OTHERS THEN
387 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
388 xtr_debug_pkg.debug('EXCEPTION -OTHERS:valid_calendar_name');
389 END IF;
390 IF sob_c%ISOPEN THEN CLOSE sob_c; END IF;
391 RAISE;
392 END valid_calendar_name;
393
394
395 /* ---------------------------------------------------------------------
396 | PRIVATE FUNCTION |
397 | standalone_process |
398 | parent_process |
399 | child_process |
400 | wrap_up_process |
401 | DESCRIPTION |
402 | Determine if the current process is a standalone process, |
403 | parent process, child process, or wrap-up process (the |
404 | finishing part to be called by parent process) |
405 | CALLED BY |
406 | create_forecast |
407 | HISTORY |
408 | 21-AUG-1997 Created Wynne Chan |
409 --------------------------------------------------------------------- */
410 FUNCTION standalone_process RETURN BOOLEAN IS
411 BEGIN
412 return (G_rp_sub_request = 'N');
413 END;
414
415 FUNCTION parent_process(req_data VARCHAR2) RETURN BOOLEAN IS
416 BEGIN
417 return (G_parent_process and req_data IS NULL);
418 END;
419
420 FUNCTION child_process RETURN BOOLEAN IS
421 BEGIN
422 return (G_rp_sub_request = 'Y' AND NOT G_parent_process);
423 END;
424
425 FUNCTION wrap_up_process(req_data VARCHAR2) RETURN BOOLEAN IS
426 BEGIN
427 return( req_data IS NOT NULL);
428 END;
429
430 /* ---------------------------------------------------------------------
431 | PRIVATE PROCEDURE |
432 | submit_child_requests |
433 | DESCRIPTION |
434 | Called by the parent process to submit forecast request for |
435 | each row requested by the user |
436 | CALLED BY |
437 | create_forecast |
438 | HISTORY |
439 | 21-AUG-1997 Created Wynne Chan |
440 --------------------------------------------------------------------- */
441 PROCEDURE submit_child_requests IS
442 request_id NUMBER;
443 CURSOR CRowNumber(p_forecast_header_id NUMBER, p_rownum_from NUMBER, p_rownum_to NUMBER) IS
444 SELECT row_number
445 FROM ce_forecast_rows
446 WHERE row_number BETWEEN NVL(p_rownum_from, row_number) AND
447 NVL(p_rownum_to, row_number)
448 AND forecast_header_id = p_forecast_header_id
449 AND trx_type <> 'GLC';
450
451 BEGIN
452 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
453 xtr_debug_pkg.debug('>> XTR_CASH_FCST.submit_child_requests ');
454 END IF;
455 open CRowNumber(G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
456 LOOP
457 FETCH CRowNumber INTO G_row_number;
458 EXIT WHEN CRowNumber%NOTFOUND OR CRowNumber%NOTFOUND IS NULL;
459
460 request_id := FND_REQUEST.SUBMIT_REQUEST(
461 'XTR', 'XTRFCAST',to_char(G_row_number),'',TRUE,
462 G_rp_forecast_header_id,
463 G_rp_forecast_runname,
464 G_party_code,
465 to_char(G_rp_forecast_start_date,'YYYY/MM/DD HH24:MI:SS'),
466 to_char(G_row_number),
467 to_char(G_row_number),
468 'Y',
469 chr(0),'','','','','','','','','','','','','','',
470 '','','','','','','','','','',
471 '','','','','','','','','','',
472 '','','','','','','','','','',
473 '','','','','','','','','','',
474 '','','','','','','','','','',
475 '','','','','','','','','','',
476 '','','','','','','','');
477
478 END LOOP;
479 CLOSE CRowNumber;
480 EXCEPTION
481 WHEN OTHERS THEN
482 IF CRowNumber%ISOPEN THEN CLOSE CRowNumber; END IF;
483 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
484 xtr_debug_pkg.debug('EXCEPTION: XTR_CASH_FCST.submit_child_requests');
485 END IF;
486 RAISE;
487 END submit_child_requests;
488
489 /* ---------------------------------------------------------------------
490 | PRIVATE PROCEDURE |
491 | create_forecast_header |
492 | DESCRIPTION |
493 | Create forecast header for new forecast in ce_forecasts table |
494 | CALLED BY |
495 | create_forecast |
496 | HISTORY |
497 | 12-JAN-1999 Created BHCHUNG |
498 --------------------------------------------------------------------- */
499 PROCEDURE create_forecast_header IS
500 l_forecast_rowid VARCHAR2(30);
501 fid NUMBER;
502 error_msg FND_NEW_MESSAGES.message_text%TYPE;
503 duplicate_name BOOLEAN DEFAULT FALSE;
504
505 CURSOR C IS SELECT CE_FORECASTS_S.nextval FROM sys.dual;
506 BEGIN
507 OPEN C;
508 FETCH C INTO G_forecast_id;
509 CLOSE C;
510
511 IF (G_rp_forecast_runname IS NULL) THEN
512 G_rp_forecast_runname:= G_forecast_name||'/'||to_char(sysdate,'DD-MON-RRRR HH:MI:SS');
513 ELSE
514 BEGIN
515 SELECT forecast_id
516 INTO fid
517 FROM ce_forecasts
518 WHERE name = G_rp_forecast_runname;
519
520 duplicate_name := TRUE;
521 G_rp_forecast_runname := G_rp_forecast_runname||'/'||to_char(sysdate,'DD-MON-RRRR HH:MI:SS');
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 null;
525 END;
526 END IF;
527
528 /* AW 1378198
529 DELETE FROM ce_forecasts
530 WHERE forecast_header_id = G_rp_forecast_header_id;
531
532 DELETE FROM ce_forecast_errors
533 WHERE forecast_header_id = G_rp_forecast_header_id;
534 */
535
536 INSERT INTO ce_forecasts(
537 FORECAST_ID,
538 FORECAST_HEADER_ID,
539 NAME,
540 START_DATE,
541 FORECAST_CURRENCY,
542 CURRENCY_TYPE,
543 CREATED_BY,
544 CREATION_DATE,
545 LAST_UPDATED_BY,
546 LAST_UPDATE_DATE,
547 ERROR_STATUS)
548 VALUES(G_forecast_id,
549 G_rp_forecast_header_id,
550 G_rp_forecast_runname,
551 G_rp_forecast_start_date,
552 G_party_code,
553 'A',
554 nvl(fnd_global.user_id, -1),
555 sysdate,
556 nvl(fnd_global.user_id, -1),
557 sysdate,
558 'S');
559
560 commit;
561 EXCEPTION
562 WHEN OTHERS THEN
563 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
564 xtr_debug_pkg.debug('EXCEPTION: XTR_CASH_FCST.create_forecast_header ');
565 END IF;
566 RAISE;
567 END create_forecast_header;
568
569
570 /* ---------------------------------------------------------------------
571 | PUBLIC PROCEDURE |
572 | create_forecast |
573 | |
574 | DESCRIPTION |
575 | This is the main cash forecast procedure |
576 | Depending if the current run is a parent process, child process |
577 | standalone process, or the wrap-up process from parent, |
578 | create_forecast performs perform different task |
579 | |
580 | CALLED BY |
581 | |
582 | REQUIRES |
583 | p_forecast_header_id forecast header id |
584 | p_forecast_start_date forecast date |
585 | HISTORY |
586 | 28-DEC-1998 Created BHCHUNG |
587 --------------------------------------------------------------------- */
588 PROCEDURE create_forecast IS
589 counter NUMBER;
590 req_data VARCHAR2(30);
591 l_status VARCHAR2(1);
592 BEGIN
593 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
594 xtr_debug_pkg.debug('>>XTR_CASH_FCST.create_xtr_forecast');
595 END IF;
596 counter := 0;
597 --
598 -- Get forecast header info
599 --
600 OPEN f_header_cursor(G_rp_forecast_header_id);
601 FETCH f_header_cursor INTO G_forecast_name, G_aging_type,
602 G_overdue_transactions, G_transaction_calendar_id,
603 G_start_project_id, G_end_project_id, G_treasury_template;
604 CLOSE f_header_cursor;
605 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
606 xtr_debug_pkg.debug('Aging type: ' || G_aging_type);
607 xtr_debug_pkg.debug('Name: '|| G_forecast_name);
608 END IF;
609
610 FND_CURRENCY.get_info(G_rp_forecast_currency, G_precision, G_ext_precision, G_min_acct_unit);
611
612 IF(G_overdue_transactions = 'INCLUDE')THEN
613 BEGIN
614 SELECT forecast_period_id
615 INTO G_overdue_period_id
616 FROM xtr_forecast_periods
617 WHERE forecast_header_id = G_rp_forecast_header_id AND
618 level_of_summary = 'O';
619 EXCEPTION
620 WHEN OTHERS THEN
621 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
622 xtr_debug_pkg.debug('ERROR: cannot get overdue period id');
623 END IF;
624 RAISE;
625 END;
626 END IF;
627
628 req_data := fnd_conc_global.request_data;
629 if(req_data IS NOT NULL)THEN
630 G_forecast_id := to_number(req_data);
631 END IF;
632
633 if( parent_process(req_data) OR standalone_process)THEN
634
635 IF(G_transaction_calendar_id IS NOT NULL)THEN
636 validate_transaction_calendar;
637 END IF;
638 create_forecast_header;
639 end if;
640
641 IF(parent_process(req_data))THEN
642 submit_child_requests;
643 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
644 request_data => to_char(G_forecast_id));
645 END IF;
646
647 IF (standalone_process OR child_process) THEN
648 OPEN f_row_cursor(G_rp_forecast_header_id,G_rp_rownum_from, G_rp_rownum_to);
649
650 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
651 xtr_debug_pkg.debug('Forecast defn valid');
652 END IF;
653
654 -- RV
655 BEGIN
656
657 DELETE FROM xtr_external_cashflows
658 WHERE company_code = G_party_code; -- AW Bug 1378198
659 IF SQL%FOUND THEN
660 COMMIT;
661 END IF;
662 EXCEPTION
663 WHEN OTHERS THEN
664 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
665 xtr_debug_pkg.debug('EXCEPTION: XTR_CASH_FCST.create_forecast-->delete');
666 END IF;
667 RAISE;
668 END;
669
670 XTR_CSH_FCST_POP.populate_aging_buckets;
671
672 LOOP
673 FETCH f_row_cursor INTO G_rowid, G_forecast_row_id,
674 G_row_number, G_trx_type,
675 G_lead_time, G_forecast_method,
676 G_discount_option, G_app_short_name,G_include_float_flag,
677 G_order_status, G_order_date_type,
678 G_code_combination_id, G_budget_name,
679 G_encumbrance_type_id, G_chart_of_accounts_id ,
680 G_set_of_books_id, G_org_id,
681 G_roll_forward_type, G_roll_forward_period,
682 G_include_dispute_flag, G_sales_stage_id,G_channel_code,
683 G_win_probability, G_sales_forecast_status, G_customer_profile_class_id,
684 G_bank_account_id, G_receipt_method_id, G_vendor_type,
685 G_payment_method, G_pay_group,G_payment_priority,
686 G_authorization_status, G_type, G_budget_type, G_budget_version,
687 G_include_hold_flag, G_include_net_cash_flag, G_budget_version_id,
688 G_payroll_id, G_org_payment_method_id,
689 G_external_source_type, G_criteria_category,
690 G_criteria1, G_criteria2, G_criteria3, G_criteria4, G_criteria5,
691 G_criteria6, G_criteria7, G_criteria8, G_criteria9, G_criteria10,
692 G_criteria11, G_criteria12, G_criteria13, G_criteria14, G_criteria15;
693 EXIT WHEN f_row_cursor%NOTFOUND OR f_row_cursor%NOTFOUND IS NULL;
694
695 --
696 -- Set Changing View Constants
697 --
698 CEFC_VIEW_CONST.set_rowid(G_rowid);
699 CEFC_VIEW_CONST.set_constants(G_rp_forecast_header_id,
700 G_rp_calendar_name, G_rp_forecast_start_period,
701 G_rp_forecast_start_date, G_min_col, G_max_col);
702 G_invalid_overdue_row := G_invalid_overdue;
703 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
704 xtr_debug_pkg.debug('Calling Pop Cells...for trx : ' || G_trx_type||
705 ' and row_number :' || G_row_number);
706 END IF;
707 XTR_CSH_FCST_POP.Populate_Cells;
708 END LOOP;
709 CLOSE f_row_cursor;
710 END IF;
711
712 IF (wrap_up_process(req_data) OR standalone_process) THEN
713 SELECT error_status
714 INTO l_status
715 FROM ce_forecasts
716 WHERE forecast_id = G_forecast_id;
717
718 IF l_status <> 'E' THEN
719 print_report;
720 END IF;
721 END IF;
722
723 return;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 IF f_row_cursor%ISOPEN THEN CLOSE f_row_cursor; END IF;
728 IF f_header_cursor%ISOPEN THEN CLOSE f_header_cursor; END IF;
729 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
730 xtr_debug_pkg.debug('EXCEPTION: XTR_CASH_FCST.cash_xtr_forecast');
731 END IF;
732 RAISE;
733 END create_forecast;
734
735
736 /* ---------------------------------------------------------------------
737 | PUBLIC PROCEDURE |
738 | Forecast |
739 | |
740 | DESCRIPTION |
741 | The forecast program is divided into two parts just for easy |
742 | submission. This procedure only calls doesn't do much but calls |
743 | other procedures which do the work |
744 | CALLED BY |
745 | |
746 | REQUIRES |
747 | p_forecast_header_id template id |
748 | p_forecast_runname forecast name |
749 | p_forecast_start_date start forecast on this date |
750 | p_forecast_currency amount currency |
751 | p_exchange_type exchange type |
752 | p_exchange_date exchange date |
753 | p_src_curr_type functional/entered |
754 | p_src_currency filter currency for transactions |
755 | p_rownum_from which rows |
756 | p_rownum_to |
757 | p_project_id project id |
758 | |
759 | HISTORY |
760 | 12-JUL-1996 Created Bidemi Carrol |
761 --------------------------------------------------------------------- */
762
763
764 PROCEDURE Forecast(errbuf OUT NOCOPY VARCHAR2,
765 retcode OUT NOCOPY NUMBER,
766 p_forecast_header_id IN NUMBER,
767 p_forecast_runname IN VARCHAR2,
768 p_company_code IN VARCHAR2,
769 p_forecast_start_date IN VARCHAR2,
770 p_rownum_from IN NUMBER,
771 p_rownum_to IN NUMBER,
772 p_sub_request IN VARCHAR2) IS
773
774 l_error_code NUMBER;
775
776 -- AW Bug 1378198
777 l_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE;
778
779 /* RV BUG 1548223
780 CURSOR c_company IS
781 SELECT party_code
782 FROM xtr_party_info
783 WHERE party_code = nvl(p_company_code, party_code)
784 AND party_type = 'C';
785 */
786
787 CURSOR c_company IS
788 SELECT party_code
789 FROM xtr_parties_v
790 WHERE party_code = nvl(p_company_code, party_code)
791 AND party_type = 'C';
792
793
794 BEGIN
795 -- xtr_debug_pkg.enable_debug;
796
797 -- AW Bug 1378198 The following is taken from procedure CREATE_FORECAST_HEADER
798 --
799 BEGIN
800 DELETE FROM ce_forecasts
801 WHERE forecast_header_id = G_rp_forecast_header_id;
802 IF SQL%FOUND THEN
803 COMMIT;
804 END IF;
805
806 DELETE FROM ce_forecast_errors
807 WHERE forecast_header_id = G_rp_forecast_header_id;
808 IF SQL%FOUND THEN
809 COMMIT;
810 END IF;
811 EXCEPTION
812 WHEN OTHERS THEN
813 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
814 xtr_debug_pkg.debug('EXCEPTION: XTR_CASH_FCST.forecast-->delete');
815 END IF;
816 RAISE;
817 END;
818 --
819 OPEN c_company;
820 LOOP
821 FETCH c_company INTO l_company_code;
822 EXIT WHEN c_company%NOTFOUND or c_company%NOTFOUND IS NULL;
823 l_error_code := set_parameters( p_forecast_header_id,
824 p_forecast_runname,
825 p_forecast_start_date,
826 'SOURCE',
827 'A',
828 l_company_code,
829 p_rownum_from,
830 p_rownum_to,
831 p_sub_request);
832 IF l_error_code = 0 THEN
833 create_forecast;
834 END IF;
835 END LOOP;
836 CLOSE c_company;
837
838 END Forecast;
839
840 END XTR_CASH_FCST;