[Home] [Help]
PACKAGE BODY: APPS.CE_CSH_FCST_POP
Source
1 PACKAGE BODY CE_CSH_FCST_POP AS
2 /* $Header: cefpcelb.pls 120.73.12020000.3 2012/07/16 22:46:16 ckansara ship $ */
3
4 FUNCTION body_revision RETURN VARCHAR2 IS
5 BEGIN
6
7 RETURN '$Revision: 120.73.12020000.3 $';
8
9 END body_revision;
10
11 FUNCTION spec_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN G_spec_revision;
15
16 END spec_revision;
17
18 /* ---------------------------------------------------------------------
19 | PUBLIC PROCEDURE |
20 | Get Average Payment Days |
21 | |
22 | DESCRIPTION |
23 | Calculates average payment days |
24 | CALLED BY |
25 | Build_XXX_Query |
26 | REQUIRES |
27 | customer_id, site_use_id, currency_code, period |
28 | HISTORY |
29 | 01-FEB-2001 Created |
30 --------------------------------------------------------------------- */
31 FUNCTION Get_Average_Payment_Days (X_customer_id Number,
32 X_site_use_id Number,
33 X_currency_code VARCHAR2,
34 X_period NUMBER ) RETURN NUMBER IS
35 l_ave_pay_days NUMBER;
36 BEGIN
37 SELECT decode(count(ar_receivable_applications.apply_date), 0, 0,
38 round(sum(ar_receivable_applications.apply_date -
39 ar_payment_schedules.trx_date) /
40 count(ar_receivable_applications.apply_date)))
41 INTO l_ave_pay_days
42 FROM ar_receivable_applications_all ar_receivable_applications,
43 ar_payment_schedules_all ar_payment_schedules
44 WHERE ar_receivable_applications.applied_payment_schedule_id =
45 ar_payment_schedules.payment_schedule_id
46 AND ar_payment_schedules.customer_id = X_customer_id
47 AND ar_payment_schedules.customer_site_use_id = X_site_use_id
48 AND ar_payment_schedules.invoice_currency_code = X_currency_code
49 AND ar_receivable_applications.apply_date between
50 add_months(sysdate, - X_period) and sysdate
51 AND ar_receivable_applications.status = 'APP'
52 AND ar_receivable_applications.display = 'Y'
53 AND NVL(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y';
54
55 RETURN l_ave_pay_days;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 cep_standard.debug('NO DATA FOUND FOR AVERAGE PAYMENT DAYS');
59 return null;
60 WHEN OTHERS THEN
61 cep_standard.debug('EXCEPTION-OTHERS Get_Average_Payment_Days');
62 raise;
63 END Get_Average_Payment_Days;
64
65 /* ---------------------------------------------------------------------|
66 | PUBLIC PROCEDURE |
67 | Set_History |
68 | |
69 | DESCRIPTION |
70 | With AP payments and AR receipts if the forecast method is |
71 | 'P'ast then we need to set the history date or period |
72 | |
73 | CALLED BY |
74 | Build_XXX_Query |
75 | REQUIRES |
76 | |
77 | HISTORY |
78 | 19-AUG-1996 Created Bidemi Carrol |
79 | 12-JUN-2012 Bug 14164001 Varun Netan |
80 --------------------------------------------------------------------- */
81 PROCEDURE Set_History
82 IS
83 CURSOR cCol IS
84 SELECT forecast_column_id, column_number, days_from, days_to
85 FROM ce_forecast_columns
86 WHERE forecast_header_id = CE_CASH_FCST.G_rp_forecast_header_id;
87
88 error_msg FND_NEW_MESSAGES.message_text%TYPE;
89
90 min_col NUMBER;
91 max_col NUMBER;
92 col_num NUMBER;
93 cid NUMBER;
94 days_from NUMBER;
95 days_to NUMBER;
96 history_date DATE;
97 history_period VARCHAR2(30);
98 BEGIN
99 cep_standard.debug('>>CE_CASH_FCST.Set_History');
100 cep_standard.debug('G_roll_forward_type : ' || CE_CASH_FCST.G_roll_forward_type);
101 cep_standard.debug('G_roll_forward_period : ' || CE_CASH_FCST.G_roll_forward_period);
102 cep_standard.debug('G_start_period : ' || CE_CASH_FCST.G_rp_forecast_start_period);
103
104 cep_standard.debug('14164001: CEFC_VIEW_CONST.period_set_name=' || CEFC_VIEW_CONST.get_period_set_name);
105
106 IF (CE_CASH_FCST.G_roll_forward_type = 'D') THEN
107 CEFC_VIEW_CONST.set_start_date(CE_CASH_FCST.G_rp_forecast_start_date - CE_CASH_FCST.G_roll_forward_period);
108 CEFC_VIEW_CONST.set_min_col(nvl(CE_CASH_FCST.G_min_col,0) + CE_CASH_FCST.G_roll_forward_period);
109 CEFC_VIEW_CONST.set_max_col(nvl(CE_CASH_FCST.G_max_col,0) + CE_CASH_FCST.G_roll_forward_period);
110
111 ELSIF (CE_CASH_FCST.G_roll_forward_type = 'M') THEN
112 history_date:= ADD_MONTHS(CE_CASH_FCST.G_rp_forecast_start_date,- CE_CASH_FCST.G_roll_forward_period);
113 CEFC_VIEW_CONST.set_start_date(history_date);
114 CEFC_VIEW_CONST.set_min_col(nvl(CE_CASH_FCST.G_min_col,0) + CE_CASH_FCST.G_roll_forward_period*30);
115 CEFC_VIEW_CONST.set_max_col(nvl(CE_CASH_FCST.G_max_col,0) + CE_CASH_FCST.G_roll_forward_period*30);
116
117 ELSIF (CE_CASH_FCST.G_roll_forward_type = 'A') THEN
118 BEGIN
119 SELECT gps.period_name
120 INTO history_period
121 FROM gl_periods gps,
122 gl_periods gp,
123 gl_period_types gpt
124 WHERE gps.period_num = DECODE(LEAST(gp.period_num-CE_CASH_FCST.G_roll_forward_period,1),
125 1,gp.period_num - CE_CASH_FCST.G_roll_forward_period,
126 gpt.number_per_fiscal_year + mod(gp.period_num-CE_CASH_FCST.G_roll_forward_period,
127 gpt.number_per_fiscal_year))
128 AND gps.period_year = gp.period_year +
129 DECODE(LEAST(gp.period_num-CE_CASH_FCST.G_roll_forward_period,1),
130 1,0,
131 DECODE(mod(gp.period_num-CE_CASH_FCST.G_roll_forward_period,gpt.number_per_fiscal_year),
132 0, FLOOR((gp.period_num -CE_CASH_FCST.G_roll_forward_period)/gpt.number_per_fiscal_year)-1,
133 FLOOR((gp.period_num -CE_CASH_FCST.G_roll_forward_period)/gpt.number_per_fiscal_year)))
134 AND gp.period_set_name = gps.period_set_name
135 AND gps.period_type = gp.period_type
136 AND gpt.period_type = gp.period_type
137 AND gp.period_name = CE_CASH_FCST.G_rp_forecast_start_period
138 AND gp.period_set_name = CEFC_VIEW_CONST.get_period_set_name;
139
140 cep_standard.debug('14164001: CEFC_VIEW_CONST.GET_START_PERIOD_NAME='||history_period);
141 CEFC_VIEW_CONST.set_start_period_name(history_period);
142 CEFC_VIEW_CONST.set_min_col(nvl(CE_CASH_FCST.G_min_col,0) + CE_CASH_FCST.G_roll_forward_period);
143 CEFC_VIEW_CONST.set_max_col(nvl(CE_CASH_FCST.G_max_col,0) + CE_CASH_FCST.G_roll_forward_period);
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 cep_standard.debug('CE_CASH_FCST.Set_History EXCEPTION - NO DATA FOUND FOR HISTORY PERIOD');
147 RAISE;
148 WHEN OTHERS THEN
149 cep_standard.debug('CE_CASH_FCST.Set_History EXCEPTION - OTHERS');
150 raise;
151 END;
152 END IF;
153
154 min_col := CEFC_VIEW_CONST.get_min_col;
155 max_col := CEFC_VIEW_CONST.get_max_col;
156 cep_standard.debug('14164001: CEFC_VIEW_CONST.get_min_col='||min_col);
157 cep_standard.debug('14164001: CEFC_VIEW_CONST.get_max_col='||max_col);
158
159 CE_CASH_FCST.G_invalid_overdue_row := FALSE;
160
161 OPEN cCol;
162 FETCH cCol INTO cid, col_num, days_from, days_to;
163 LOOP
164 EXIT WHEN cCol%NOTFOUND OR cCol%NOTFOUND IS NULL;
165
166 IF( days_from < min_col OR
167 days_from > max_col OR
168 days_to < min_col OR
169 days_to > max_col)
170 THEN
171
172 FND_MESSAGE.set_name ('CE','CE_FC_COLUMN_NOT_IN_RANGE');
173 FND_MESSAGE.set_token('COLUMN', col_num);
174 error_msg := FND_MESSAGE.GET;
175 CE_FORECAST_ERRORS_PKG.insert_row(
176 CE_CASH_FCST.G_forecast_id,
177 CE_CASH_FCST.G_rp_forecast_header_id,
178 CE_CASH_FCST.G_forecast_row_id,'CE_FC_COLUMN_NOT_IN_RANGE',
179 error_msg);
180 END IF;
181
182 IF( col_num = 0 AND days_to < min_col ) THEN
183 CE_CASH_FCST.G_invalid_overdue_row := TRUE;
184 END IF;
185
186 FETCH cCol INTO cid, col_num, days_from, days_to;
187 END LOOP;
188
189 -- 14164001: Added
190 IF(CE_CASH_FCST.G_invalid_overdue_row) THEN
191 cep_standard.debug('CE_CASH_FCST.G_invalid_overdue_row=TRUE');
192 ELSE
193 cep_standard.debug('CE_CASH_FCST.G_invalid_overdue_row=FALSE');
194 END IF;
195
196 cep_standard.debug('<<CE_CASH_FCST.Set_History');
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF(cCol%ISOPEN)THEN
201 CLOSE cCol;
202 END IF;
203 RAISE;
204 END Set_History;
205
206
207 /* ---------------------------------------------------------------------
208 | PUBLIC PROCEDURE |
209 | populate_aging_buckets |
210 | |
211 | DESCRIPTION |
212 | Return real aging buckets by considering the transaction |
213 | calendar into account |
214 | CALLED BY |
215 | execute_main_query |
216 | REQUIRES |
217 | |
218 | HISTORY |
219 | 04-AUG-1997 Wynne Chan Created |
220 | 12-JUN-2012 Varun Netan 14164001: Added debugs statements |
221 ----------------------------------------------------------------------*/
222 PROCEDURE populate_aging_buckets IS
223 CURSOR C1 IS
224 select forecast_column_id,
225 to_date(start_date,'J'),
226 to_date(end_date,'J')
227 from ce_fc_aging_buckets_v;
228
229 start_date DATE;
230 end_date DATE;
231 new_start_date DATE;
232 new_end_date DATE;
233 fid NUMBER;
234
235 BEGIN
236 cep_standard.debug('>>CE_CSH_FCST_POP.populate_aging_buckets');
237 cep_standard.debug('CE_CASH_FCST.G_transaction_calendar_id='
238 ||CE_CASH_FCST.G_transaction_calendar_id);
239 cep_standard.debug('CE_CSH_FCST_POP.G_calendar_start='
240 ||to_char(CE_CSH_FCST_POP.G_calendar_start,'dd-mon-yyyy hh24:mi:ss'));
241 cep_standard.debug('CE_CSH_FCST_POP.G_calendar_end='
242 ||to_char(CE_CSH_FCST_POP.G_calendar_end,'dd-mon-yyyy hh24:mi:ss'));
243
244 -- debug statements added for bug 14164001
245 cep_standard.debug('-- bug 14164001 ----------------------------------------------------------');
246 cep_standard.debug('CEFC_VIEW_CONST.GET_MIN_COL='||CEFC_VIEW_CONST.GET_MIN_COL);
247 cep_standard.debug('CEFC_VIEW_CONST.GET_MAX_COL='||CEFC_VIEW_CONST.GET_MAX_COL);
248 cep_standard.debug('CEFC_VIEW_CONST.GET_HEADER_ID='||CEFC_VIEW_CONST.GET_HEADER_ID);
249 cep_standard.debug('CEFC_VIEW_CONST.GET_START_PERIOD_NAME='||CEFC_VIEW_CONST.GET_START_PERIOD_NAME);
250 cep_standard.debug('CEFC_VIEW_CONST.GET_PERIOD_SET_NAME='||CEFC_VIEW_CONST.GET_PERIOD_SET_NAME);
251 cep_standard.debug('--------------------------------------------------------------------------');
252 OPEN C1;
253 FETCH C1 INTO fid, start_date, end_date;
254 LOOP
255 EXIT WHEN C1%NOTFOUND OR C1%NOTFOUND IS NULL;
256
257 cep_standard.debug('forecast_id='||fid||
258 ',start_date='||to_char(start_date,'dd-mon-yyyy hh24:mi:ss')||
259 ',end_date='||to_char(end_date,'dd-mon-yyyy hh24:mi:ss'));
260 new_start_date := NULL;
261 new_end_date := NULL;
262
263 IF(CE_CASH_FCST.G_transaction_calendar_id IS NOT NULL)
264 THEN
265 IF(start_date <= G_calendar_start OR start_date-1 > G_calendar_end)
266 THEN
267 new_start_date := start_date;
268 ELSE
269 BEGIN
270 select max(transaction_date)+1
271 into new_start_date
272 from gl_transaction_dates
273 where transaction_calendar_id = CE_CASH_FCST.G_transaction_calendar_id
274 and transaction_date < start_date
275 and business_day_flag = 'Y';
276
277 IF (new_start_date IS NULL)
278 THEN
279 new_start_date := G_calendar_start;
280 END IF;
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 -- case where all days between G_calendar_start and
284 -- start_date are non-workdays.
285 new_start_date := G_calendar_start;
286 END;
287 END IF;
288
289 IF(end_date < G_calendar_start OR end_date > G_calendar_end)
290 THEN
291 new_end_date := end_date;
292 ELSE
293 BEGIN
294 select max(transaction_date)
295 into new_end_date
296 from gl_transaction_dates
297 where transaction_calendar_id = CE_CASH_FCST.G_transaction_calendar_id
298 and transaction_date <= end_date
299 and business_day_flag = 'Y';
300
301 IF (new_end_date IS NULL)
302 THEN
303 new_end_date := G_calendar_start -1;
304 END IF;
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 -- case where all days between end_date and
308 -- G_calendar_start are non-workdays.
309 new_end_date := G_calendar_start -1;
310 END;
311 END IF;
312 ELSE
313 new_start_date := start_date;
314 new_end_date := end_date;
315 END IF;
316
317 -- Bug # 1927006
318 new_start_date := trunc(new_start_date);
319 new_end_date := to_date(to_char(new_end_date, 'DD-MM-RR') || ' 23:59:59', 'DD-MM-RR HH24:MI:SS');
320
321 cep_standard.debug('insert into CE_FORECAST_EXT_TEMP ...');
322 cep_standard.debug('forecast_request_id='||CE_CASH_FCST.G_forecast_id
323 ||',start_date='||new_start_date
324 ||',end_date='||new_end_date
325 ||',forecast_column_id='||fid
326 ||',conversion_rate='||CE_CASH_FCST.G_forecast_row_id);
327
328 INSERT INTO CE_FORECAST_EXT_TEMP
329 (context_value, forecast_request_id, start_date, end_date,
330 forecast_column_id, conversion_rate)
331 VALUES ('A', CE_CASH_FCST.G_forecast_id, new_start_date, new_end_date,
332 fid, CE_CASH_FCST.G_forecast_row_id);
333
334 FETCH C1 INTO fid, start_date, end_date;
335 END LOOP;
336 CLOSE C1;
337
338 cep_standard.debug('<<CE_CSH_FCST_POP.populate_aging_buckets');
339 EXCEPTION
340 WHEN OTHERS THEN
341 IF C1%ISOPEN THEN
342 CLOSE C1;
343 END IF;
344 cep_standard.debug('EXCEPTION:populate_aging_buckets');
345 raise;
346 END populate_aging_buckets;
347
348 PROCEDURE clear_aging_buckets IS
349 BEGIN
350 delete from ce_forecast_ext_temp
351 where context_value = 'A' and
352 forecast_request_id = CE_CASH_FCST.G_forecast_id and
353 conversion_rate = CE_CASH_FCST.G_forecast_row_id;
354
355 cep_standard.debug('<<ce_csh_fcST_POP.clear_aging_buckets');
356 EXCEPTION
357 WHEN OTHERS THEN
358 cep_standard.debug('EXCEPTION:clear_aging_buckets');
359 raise;
360 END clear_aging_buckets;
361
362 /* ---------------------------------------------------------------------
363 | PUBLIC PROCEDURE |
364 | Get Select Clause |
365 | |
366 | DESCRIPTION |
367 | Builds Select clause and returns it to calling procedure |
368 | CALLED BY |
369 | Build_XXX_Query |
370 | REQUIRES |
371 | |
372 | HISTORY |
373 | 19-AUG-1996 Created Bidemi Carrol |
374 --------------------------------------------------------------------- */
375 FUNCTION Get_Select_Clause RETURN VARCHAR2 IS
376 select_clause VARCHAR2(1500);
377 amount_string VARCHAR2(20);
378 trx_amount_string VARCHAR2(20);
379 clause_string VARCHAR2(200);
380 BEGIN
381 amount_string := 'nvl(src.amount,0)*';
382 trx_amount_string := 'src.amount';
383 IF (CE_CASH_FCST.G_trx_type IN ('API','APP','APX','OIO','PAY','POP',
384 'POR','PAT','PAO','UDO')) THEN
385 amount_string := 'nvl(-src.amount,0)*';
386 trx_amount_string := '-src.amount';
387 END IF;
388
389 IF (CE_CASH_FCST.G_trx_type IN ('APP','ARR','PAY','XTI','XTO')) THEN
390 IF(CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
391 clause_string := 'src.bank_account_id,
392 ccid.asset_code_combination_id,
393 round('||amount_string
394 ||CE_CASH_FCST.G_rp_exchange_rate
395 ||','
396 ||CE_CASH_FCST.G_precision||')';
397 ELSE
398 clause_string := 'src.bank_account_id,
399 ccid.asset_code_combination_id,
400 round('||amount_string||'curr.exchange_rate,'
401 ||CE_CASH_FCST.G_precision||')';
402 END IF;
403 ELSE
404 IF(CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
405 clause_string := 'null,
406 null,
407 round('||amount_string
408 ||CE_CASH_FCST.G_rp_exchange_rate
409 ||','
410 ||CE_CASH_FCST.G_precision||')';
411 ELSE
412 clause_string := 'null,
413 null,
414 round('||amount_string||'curr.exchange_rate,'
415 ||CE_CASH_FCST.G_precision||')';
416 END IF;
417 END IF;
418
419 -- 5609517: Remove ORDERED hint as suggested by apps perf team
420 -- select_clause := '
421 -- SELECT /*+ ORDERED USE_MERGE(src)*/
422 select_clause := '
423 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
424 '||CE_CASH_FCST.G_forecast_id||',
425 '||CE_CASH_FCST.G_rp_forecast_header_id||',
426 '||CE_CASH_FCST.G_forecast_row_id||',
427 ''Y'',
428 nvl(fnd_global.user_id,-1),
429 sysdate,
430 nvl(fnd_global.user_id,-1),
431 sysdate,
432 nvl(fnd_global.user_id,-1),
433 cab.forecast_column_id,
434 src.reference_id,
435 src.currency_code,
436 to_number(hr_ou.ORGANIZATION_ID),
437 src.trx_date +'
438 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
439 '||clause_string||',
440 '||trx_amount_string;
441
442 return select_clause;
443 END Get_Select_Clause;
444
445
446 /* ---------------------------------------------------------------------
447 | PUBLIC PROCEDURE |
448 | Get From Clause |
449 | |
450 | DESCRIPTION |
451 | Builds From clause and returns it to calling procedure |
452 | CALLED BY |
453 | Build_XXX_Query |
454 | REQUIRES |
455 | trx view name |
456 | HISTORY |
457 | 19-AUG-1996 Created Bidemi Carrol |
458 --------------------------------------------------------------------- */
459 FUNCTION Get_From_Clause (view_name VARCHAR2) RETURN VARCHAR2 IS
460 from_clause VARCHAR2(500);
461 BEGIN
462 from_clause := '
463 FROM '||view_name ||' src,
464 ce_forecast_ext_temp cab ';
465
466 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
467 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
468 from_clause := from_clause || ' ,
469 ce_currency_rates_temp curr ';
470 END IF;
471
472 IF (CE_CASH_FCST.G_trx_type IN ('APP','ARR','PAY','XTI','XTO','XTR')) THEN
473 from_clause := from_clause || ' ,
474 ce_gl_accounts_ccid ccid, ce_bank_acct_uses_all bau, ce_bank_accounts ba';
475 END IF;
476
477 IF (CE_CASH_FCST.G_trx_type not in ('XTI','XTO','XTR')) THEN
478 from_clause := from_clause || ' ,
479 hr_organization_information o3'||',
480 hr_organization_information hr_ou';
481 END IF;
482
483 IF(CE_CASH_FCST.G_rp_src_curr_type = 'F')THEN
484 IF(CE_CASH_FCST.G_app_short_name = 'AP')THEN
485 from_clause := from_clause || ' ,
486 ce_forecast_ap_orgs_v org ';
487 ELSIF(CE_CASH_FCST.G_app_short_name = 'AR')THEN
488 from_clause := from_clause || ' ,
489 ce_forecast_ar_orgs_v org ';
490 ELSIF(CE_CASH_FCST.G_app_short_name = 'AS')THEN
491 from_clause := from_clause || ' ,
492 ce_forecast_as_orgs_v org ';
493 ELSIF(CE_CASH_FCST.G_app_short_name = 'PAY')THEN
494 from_clause := from_clause || ' ,
495 ce_forecast_pay_orgs_v org ';
496 ELSIF(CE_CASH_FCST.G_app_short_name = 'PO')THEN
497 from_clause := from_clause || ' ,
498 ce_forecast_po_orgs_v org ';
499 ELSIF(CE_CASH_FCST.G_app_short_name = 'OE')THEN
500 from_clause := from_clause || ' ,
501 ce_forecast_oe_orgs_v org ';
502 ELSIF(CE_CASH_FCST.G_app_short_name = 'XTR')THEN
503 from_clause := from_clause || ' ,
504 gl_sets_of_books org ';
505 END IF;
506 END IF;
507
508 --
509 -- Special case for OE, need to join to ce_currency_rates_temp to figure out
510 -- the functional value of the transaction amount
511 --
512 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL AND
513 CE_CASH_FCST.G_rp_src_curr_type = 'F' AND
514 CE_CASH_FCST.G_trx_type IN ('OEO','XTR'))THEN
515 from_clause := from_clause || ' ,
516 ce_currency_rates_temp curr2 ';
517 END IF;
518
519 cep_standard.debug(from_clause);
520 return from_clause;
521 END Get_From_Clause;
522
523 /* ---------------------------------------------------------------------
524 | PUBLIC PROCEDURE |
525 | Add_Where |
526 | DESCRIPTION |
527 | Builds additional where clause for criteria if criteria |
528 | contains certain value |
529 | CALLED BY |
530 | Build_XXX_Query |
531 | REQUIRES |
532 | |
533 | HISTORY |
534 | 31-JUL-1997 Created Wynen Chan |
535 --------------------------------------------------------------------- */
536 FUNCTION Add_Where(criteria VARCHAR2) RETURN VARCHAR2 IS
537 l_pay_group VARCHAR2(25);
538 BEGIN
539 IF(criteria = 'SRC_CURR_TYPE')THEN
540 IF(CE_CASH_FCST.G_rp_src_curr_type = 'E')THEN
541 return ('
542 AND src.currency_code = '''||CE_CASH_FCST.G_rp_src_currency||''' ');
543 ELSIF(CE_CASH_FCST.G_rp_src_curr_type = 'F')THEN
544 IF(CE_CASH_FCST.G_app_short_name = 'PA')THEN
545 return ('
546 AND src.functional_currency_code = '''||CE_CASH_FCST.G_rp_src_currency||''' ');
547 ELSIF(CE_CASH_FCST.G_app_short_name = 'XTR')THEN
548 return ('
549 AND org.currency_code = '''||CE_CASH_FCST.G_rp_src_currency||'''
550 AND (org.set_of_books_id = src.set_of_books_id or org.set_of_books_id IS NULL) ');
551 ELSE
552 return ('
553 AND org.currency_code = '''||CE_CASH_FCST.G_rp_src_currency||'''
554 AND (org.org_id = src.org_id or org.org_id IS NULL) ');
555 END IF;
556 END IF;
557
558 ELSIF(criteria = 'EXCHANGE_TYPE')THEN
559 IF(CE_CASH_FCST.G_rp_exchange_type IS NULL OR
560 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
561 return ('
562 AND curr.forecast_request_id = cab.forecast_request_id
563 AND curr.to_currency = '''||CE_CASH_FCST.G_rp_forecast_currency||'''
564 AND curr.currency_code = src.currency_code ');
565 END IF;
566
567 ELSIF(criteria = 'VENDOR_TYPE')THEN
568 IF(CE_CASH_FCST.G_vendor_type IS NOT NULL)THEN
569 return ('
570 AND src.vendor_type = '''||CE_CASH_FCST.G_vendor_type||''' ');
571 END IF;
572
573 ELSIF(criteria = 'PAY_GROUP')THEN
574 IF(CE_CASH_FCST.G_pay_group IS NOT NULL)THEN
575 l_pay_group := replace(CE_CASH_FCST.G_pay_group, '''', '''''');
576 return ('
577 AND src.paygroup = '''||l_pay_group||''' ');
578 END IF;
579
580 ELSIF(criteria = 'PAYMENT_PRIORITY')THEN
581 IF(CE_CASH_FCST.G_payment_priority IS NOT NULL)THEN
582 return ('
583 AND src.payment_priority <= '||to_char(CE_CASH_FCST.G_payment_priority));
584 END IF;
585
586 ELSIF(criteria = 'BANK_ACCOUNT_ID')THEN
587 IF(CE_CASH_FCST.G_bank_account_id IS NOT NULL)THEN
588 return ('
589 AND src.bank_account_id = '||TO_CHAR(CE_CASH_FCST.G_bank_account_id));
590 END IF;
591
592 ELSIF(criteria = 'RECEIPT_METHOD_ID')THEN
593 IF(CE_CASH_FCST.G_receipt_method_id IS NOT NULL)THEN
594 return ('
595 AND src.receipt_method_id = '||TO_CHAR(CE_CASH_FCST.G_receipt_method_id));
596 END IF;
597
598 ELSIF(criteria = 'CUSTOMER_PROFILE_CLASS_ID')THEN
599 IF(CE_CASH_FCST.G_customer_profile_class_id IS NOT NULL)THEN
600 return ('
601 AND src.profile_class_id = '||to_char(CE_CASH_FCST.G_customer_profile_class_id));
602 END IF;
603
604 ELSIF(criteria = 'AUTHORIZATION_STATUS')THEN
605 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL)THEN
606 return ('
607 AND src.status = '''||CE_CASH_FCST.G_authorization_status ||''' ');
608 END IF;
609
610 ELSIF(criteria = 'PAYMENT_METHOD')THEN
611 IF(CE_CASH_FCST.G_payment_method IS NOT NULL)THEN
612 return ('
613 AND src.payment_method = '''||CE_CASH_FCST.G_payment_method||''' ');
614 END IF;
615
616 ELSIF(criteria = 'ORG_PAYMENT_METHOD_ID')THEN
617 IF(CE_CASH_FCST.G_org_payment_method_id IS NOT NULL)THEN
618 return ('
619 AND src.org_payment_method_id = '||to_char(CE_CASH_FCST.G_org_payment_method_id));
620 END IF;
621
622 ELSIF(criteria = 'PAYROLL_ID')THEN
623 IF( CE_CASH_FCST.G_payroll_id IS NOT NULL )THEN
624 return ('
625 AND src.payroll_id = '||to_char(CE_CASH_FCST.G_payroll_id));
626 END IF;
627
628 ELSIF(criteria = 'CHANNEL_CODE')THEN
629 IF( CE_CASH_FCST.G_channel_code IS NOT NULL )THEN
630 return ('
631 AND src.channel_code = '''||CE_CASH_FCST.G_channel_code||''' ');
632 END IF;
633
634 ELSIF(criteria = 'SALES_STAGE_ID')THEN
635 IF( CE_CASH_FCST.G_sales_stage_id IS NOT NULL )THEN
636 return ('
637 AND src.sales_stage_id = '||to_char(CE_CASH_FCST.G_sales_stage_id));
638 END IF;
639
640 ELSIF(criteria = 'SALES_FORECAST_STATUS')THEN
641 IF( CE_CASH_FCST.G_sales_forecast_status IS NOT NULL )THEN
642 return ('
643 AND src.status_code = '''|| CE_CASH_FCST.G_sales_forecast_status ||''' ');
644 END IF;
645
646 ELSIF(criteria = 'PROJECT_ID')THEN
647 IF( CE_CASH_FCST.G_rp_project_id IS NOT NULL )THEN
648 return ('
649 AND src.project_id = '||to_char(CE_CASH_FCST.G_rp_project_id));
650 END IF;
651
652 ELSIF(criteria = 'TYPE')THEN
653 IF( CE_CASH_FCST.G_type IS NOT NULL )THEN
654 return ('
655 AND src.type = '''|| CE_CASH_FCST.G_type ||''' ');
656 END IF;
657
658 ELSIF(criteria = 'BUDGET_TYPE')THEN
659 IF( CE_CASH_FCST.G_budget_type IS NOT NULL )THEN
660 return ('
661 AND src.budget_type = '''|| CE_CASH_FCST.G_budget_type ||''' ');
662 END IF;
663
664 ELSIF(criteria = 'BUDGET_VERSION')THEN
665 IF( CE_CASH_FCST.G_budget_version IS NOT NULL )THEN
666 return ('
667 AND src.version = '''|| CE_CASH_FCST.G_budget_version ||''' ');
668 END IF;
669
670 ELSIF(criteria = 'INCLUDE_HOLD_FLAG')THEN
671 IF( CE_CASH_FCST.G_include_hold_flag <> 'Y' ) THEN
672 return ('
673 AND src.on_hold = '''||CE_CASH_FCST.G_include_hold_flag ||''' ');
674 END IF;
675 ELSIF(criteria = 'EXCLUDE_INDIC_EXP')THEN
676 IF( CE_CASH_FCST.G_exclude_indic_exp = 'Y' )THEN
677 return ('
678 AND src.dda_deal_subtype <> ''INDIC'' ');
679 END IF;
680
681 ELSIF(criteria = 'XTR_BANK_ACCOUNT')THEN
682 IF( CE_CASH_FCST.G_xtr_bank_account IS NOT NULL )THEN
683 return ('
684 AND src.company_account = '''|| CE_CASH_FCST.G_xtr_bank_account ||''' ');
685 END IF;
686
687 ELSIF(criteria = 'XTR_TYPE')THEN
688 IF( CE_CASH_FCST.G_type IS NOT NULL )THEN
689 return ('
690 AND src.category = '''|| CE_CASH_FCST.G_type ||''' ');
691 END IF;
692
693 ELSIF(criteria = 'ORDER_TYPE_ID')THEN
694 IF(CE_CASH_FCST.G_order_type_id IS NOT NULL)THEN
695 return ('
696 AND src.order_type_id = '||to_char(CE_CASH_FCST.G_order_type_id));
697 END IF;
698
699 ELSE
700 cep_standard.debug('ERROR - Add_Where got invalid criteria!');
701 END IF;
702
703 return (NULL);
704 END Add_Where;
705
706
707 /* ---------------------------------------------------------------------
708 | PUBLIC PROCEDURE |
709 | Get Where Clause |
710 | |
711 | DESCRIPTION |
712 | Builds where clause and returns it to calling procedure |
713 | CALLED BY |
714 | Build_XXX_Query |
715 | REQUIRES |
716 | |
717 | HISTORY |
718 | 19-AUG-1996 Created Bidemi Carrol |
719 --------------------------------------------------------------------- */
720 FUNCTION Get_Where_Clause RETURN VARCHAR2 IS
721 where_clause VARCHAR2(1500);
722
723 BEGIN
724 cep_standard.debug('>>CE_CASH_FCST.Get_Where_Clause');
725
726 where_clause := '
727 WHERE cab.context_value = ''A''
728 AND cab.forecast_request_id = '||to_char(CE_CASH_FCST.G_forecast_id) ||'
729 AND cab.conversion_rate = '||to_char(CE_CASH_FCST.G_forecast_row_id) ||
730 Add_Where('EXCHANGE_TYPE') || Add_Where('SRC_CURR_TYPE');
731
732 IF (CE_CASH_FCST.G_trx_type IN ('APP','ARR','PAY','XTI','XTO')) THEN
733 --bug5702686, Added line 'AND bau.org_id = src.org_id'
734 where_clause := where_clause ||'
735 AND ba.bank_account_id(+) = src.bank_account_id
736 AND bau.bank_account_id(+) = ba.bank_account_id
737 AND (bau.org_id = src.org_id or bau.LEGAL_ENTITY_ID = src.org_id)'||'
738 AND ccid.bank_acct_use_id(+) = bau.bank_acct_use_id';
739 -- for bug 6343915 modified line ' AND (bau.org_id = src.org_id or bau.LEGAL_ENTITY_ID = src.org_id)'
740 --bug5358376
741 IF (CE_CASH_FCST.G_trx_type = 'APP') THEN
742 where_clause := where_clause ||'
743 AND bau.ap_use_enable_flag = ''Y''';
744 ELSIF (CE_CASH_FCST.G_trx_type = 'ARR') THEN
745 where_clause := where_clause ||'
746 AND bau.ar_use_enable_flag = ''Y''';
747 ELSIF (CE_CASH_FCST.G_trx_type = 'PAY') THEN
748 where_clause := where_clause ||'
749 AND bau.pay_use_enable_flag = ''Y''';
750 ELSE
751 where_clause := where_clause ||'
752 AND bau.xtr_use_enable_flag = ''Y''';
753 END IF;
754 --bug5358376
755 END IF;
756
757 IF (CE_CASH_FCST.G_trx_type not in ('XTI','XTO','XTR')) THEN
758 IF (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) THEN
759 where_clause := where_clause ||'
760 AND (hr_ou.organization_id = src.org_id)'||'
761 AND hr_ou.ORG_INFORMATION_CONTEXT||'''' = ''CLASS'''||'
762 AND hr_ou.ORG_INFORMATION1 = ''OPERATING_UNIT'''||'
763 AND o3.organization_id = hr_ou.organization_id'||'
764 AND o3.ORG_INFORMATION_CONTEXT = ''Operating Unit Information'''||'
765 AND hr_ou.ORG_INFORMATION2 = ''Y''';
766 ELSE
767 where_clause := where_clause ||'
768 AND ((hr_ou.organization_id = src.org_id) OR (src.org_id is null))'||'
769 AND hr_ou.ORG_INFORMATION_CONTEXT||'''' = ''CLASS'''||'
770 AND hr_ou.ORG_INFORMATION1 = ''OPERATING_UNIT'''||'
771 AND o3.organization_id = hr_ou.organization_id'||'
772 AND o3.ORG_INFORMATION_CONTEXT = ''Operating Unit Information'''||'
773 AND hr_ou.ORG_INFORMATION2 = ''Y''';
774 END IF;
775 END IF;
776
777 IF( CE_CASH_FCST.G_app_short_name = 'XTR' ) THEN
778 IF( CE_CASH_FCST.G_company_code IS NOT NULL )THEN
779 where_clause := where_clause ||'
780 AND (src.company_code = '''|| CE_CASH_FCST.G_company_code ||''') ';
781 ELSIF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
782 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
783 where_clause := where_clause ||'
784 AND (src.set_of_books_id IN (SELECT DISTINCT(set_of_books_id)
785 FROM GL_SETS_OF_BOOKS
786 WHERE set_of_books_id = '||to_char(CE_CASH_FCST.G_set_of_books_id)||' )) ';
787 END IF;
788 ELSE
789 IF( CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99 )THEN
790 where_clause := where_clause ||'
791 AND (src.org_id = '||to_char( CE_CASH_FCST.G_org_id)||' OR src.org_id IS NULL) ';
792 ELSIF ( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
793 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
794 where_clause := where_clause ||'
795 AND (src.org_id IS NULL OR src.org_id IN (SELECT DISTINCT(org_id)
796 FROM CE_FORECAST_ORGS_V
797 WHERE set_of_books_id = '||to_char(CE_CASH_FCST.G_set_of_books_id)||' )) ';
798 END IF;
799 END IF;
800
801 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
802 IF(CE_CASH_FCST.G_rp_src_curr_type = 'E')THEN
803 where_clause := where_clause || '
804 AND abs(nvl(src.amount,0)) > ' ||to_char(fnd_number.number_to_canonical(CE_CASH_FCST.G_rp_amount_threshold));
805 ELSIF(CE_CASH_FCST.G_rp_src_curr_type = 'F')THEN
806 --
807 -- Special case for OE, need to join to ce_currency_rates_temp to figure out
808 -- the functional value of the transaction amount
809 --
810 -- bug4345353 added the function fnd_number.number_to_canonical
811 IF(CE_CASH_FCST.G_trx_type <> 'OEO')THEN
812 IF(CE_CASH_FCST.G_trx_type = 'XTR')THEN
813 where_clause := where_clause || '
814 AND abs(nvl(src.amount,0)*curr2.exchange_rate) > ' ||to_char(fnd_number.number_to_canonical(CE_CASH_FCST.G_rp_amount_threshold)) ||'
815 AND curr2.currency_code = src.currency_code
816 AND curr2.to_currency = org.currency_code
817 AND org.set_of_books_id = src.set_of_books_id
818 AND curr2.forecast_request_id = '||to_char(CE_CASH_FCST.G_forecast_id);
819 ELSE
820 -- bug4345353 added the function fnd_number.number_to_canonical
821 where_clause := where_clause || '
822 AND abs(src.base_amount) > ' ||to_char(fnd_number.number_to_canonical(CE_CASH_FCST.G_rp_amount_threshold));
823 END IF;
824 ELSE
825 -- bug4345353 added the function fnd_number.number_to_canonical
826 where_clause := where_clause || '
827 AND abs(nvl(src.amount,0)*curr2.exchange_rate) > ' ||to_char(fnd_number.number_to_canonical(CE_CASH_FCST.G_rp_amount_threshold)) ||'
828 AND curr2.currency_code = src.currency_code
829 AND curr2.to_currency = org.currency_code
830 AND org.org_id = src.org_id
831 AND curr2.forecast_request_id = '||to_char(CE_CASH_FCST.G_forecast_id);
832 END IF;
833 END IF;
834 END IF;
835
836 cep_standard.debug('<<CE_CASH_FCST.Get_Where_Clause');
837 return where_clause;
838 END Get_Where_Clause;
839
840 /* ---------------------------------------------------------------------
841 | PUBLIC PROCEDURE |
842 | Insert_Fcast_Cell |
843 | |
844 | DESCRIPTION |
845 | This procedure inserts a row into the CE_FORECAST_TRX_CELLS |
846 | CALLED BY |
847 | Build_Remote_Query |
848 | REQUIRES |
849 | forecast_amount, column_id, reference_id, currency_code, |
850 | org_id, trx_date, bank_account_id |
851 | HISTORY |
852 | 19-AUG-1996 Created Bidemi Carrol |
853 --------------------------------------------------------------------- */
854 PROCEDURE Insert_Fcast_Cell( p_reference_id VARCHAR2,
855 p_currency_code VARCHAR2,
856 p_org_id NUMBER,
857 p_trx_date DATE,
858 p_bank_account_id NUMBER,
859 p_forecast_amount NUMBER,
860 p_trx_amount NUMBER,
861 p_forecast_column_id NUMBER) IS
862 forecast_rowid VARCHAR2(30):=NULL;
863 forecast_cell_id NUMBER := NULL;
864 l_code_combination_id NUMBER := NULL;
865 BEGIN
866 IF (p_bank_account_id is not null) THEN
867 if p_org_id is not null then
868 SELECT ccid.asset_code_combination_id
869 INTO l_code_combination_id
870 FROM ce_gl_accounts_ccid ccid, ce_bank_acct_uses_all bau
871 WHERE bau.bank_account_id = p_bank_account_id
872 and bau.org_id = p_org_id
873 and ccid.bank_acct_use_id = bau.bank_acct_use_id;
874 else
875 select bau.asset_code_combination_id
876 into l_code_combination_id
877 from ce_bank_accounts bau
878 where bau.bank_account_id = p_bank_account_id ;
879 End if ;
880 END IF; -- Bug 13601973
881
882 CE_FORECAST_TRX_CELLS_PKG.insert_row(
883 X_rowid =>forecast_rowid,
884 X_FORECAST_CELL_ID =>forecast_cell_id,
885 X_FORECAST_ID =>CE_CASH_FCST.G_forecast_id,
886 X_FORECAST_HEADER_ID =>CE_CASH_FCST.G_rp_forecast_header_id,
887 X_FORECAST_ROW_ID =>CE_CASH_FCST.G_forecast_row_id,
888 X_FORECAST_COLUMN_ID =>p_forecast_column_id,
889 X_AMOUNT =>round(NVL(p_forecast_amount,0), CE_CASH_FCST.G_precision),
890 X_TRX_AMOUNT =>p_trx_amount,
891 X_REFERENCE_ID =>p_reference_id,
892 X_CURRENCY_CODE =>p_currency_code,
893 X_ORG_ID => p_org_id,
894 X_INCLUDE_FLAG =>'Y',
895 X_TRX_DATE =>p_trx_date,
896 X_BANK_ACCOUNT_ID =>p_bank_account_id,
897 X_CODE_COMBINATION_ID =>l_code_combination_id,
898 X_CREATED_BY =>nvl(fnd_global.user_id,-1),
899 X_CREATION_DATE =>sysdate,
900 X_LAST_UPDATED_BY =>nvl(fnd_global.user_id,-1),
901 X_LAST_UPDATE_DATE =>sysdate,
902 X_LAST_UPDATE_LOGIN =>nvl(fnd_global.user_id,-1));
903 EXCEPTION
904 WHEN OTHERS THEN
905 cep_standard.debug('EXCEPTION:OTHERS- Insert_Fcast_Cell');
906 RAISE;
907 END Insert_Fcast_Cell;
908
909 /* ---------------------------------------------------------------------
910 | PUBLIC PROCEDURE |
911 | Zero_Fill_Cells |
912 | |
913 | DESCRIPTION |
914 | This procedure inserts a row into the CE_FORECAST_TRX_CELLS table |
915 | for those columns for which the view produces a null row |
916 | CALLED BY |
917 | Execute_Main_Query, |
918 | REQUIRES |
919 | |
920 | HISTORY |
921 | 19-AUG-1996 Created Bidemi Carrol |
922 ---------------------------------------------------------------------*/
923 PROCEDURE Zero_Fill_Cells IS
924 column_id CE_FORECAST_COLUMNS.forecast_column_id%TYPE;
925
926 CURSOR zero_fill_c IS SELECT cfc.forecast_column_id
927 FROM ce_forecast_columns cfc
928 WHERE cfc.forecast_header_id = CE_CASH_FCST.G_rp_forecast_header_id;
929
930 BEGIN
931 cep_standard.debug('>>CE_CASH_FCST_POP.Zero_Fill_Cells');
932 OPEN zero_fill_c;
933 LOOP
934 FETCH zero_fill_c into column_id;
935 EXIT WHEN zero_fill_C%NOTFOUND OR zero_fill_C%NOTFOUND IS NULL;
936 cep_standard.debug('column_id with zero amount: '|| to_char(column_id));
937 Insert_Fcast_Cell(null, null, null, null, null, 0, to_number(null), column_id);
938 END LOOP;
939
940 CLOSE zero_fill_c;
941
942 cep_standard.debug('<<CE_CASH_FCST_POP.Zero_Fill_Cells');
943 EXCEPTION
944 WHEN OTHERS THEN
945 IF zero_fill_C%ISOPEN THEN close zero_fill_C; END IF;
946 cep_standard.debug('EXCEPTION-OTHERS: Zero_fill_Cells');
947 RAISE;
948 END Zero_Fill_Cells;
949
950
951 /* ---------------------------------------------------------------------
952 | PUBLIC PROCEDURE |
953 | Execute_Main_Query |
954 | |
955 | DESCRIPTION |
956 | This procedure takes in the query string and executes it using |
957 | dynamic sql functionality. The query string is parsed and then |
958 | executed - directly inserts into ce_forecast_trx_cells |
959 | from select statement |
960 | CALLED BY |
961 | Build_XX_Query |
962 | REQUIRES |
963 | main_query |
964 | HISTORY |
965 | 29-APR-2003 Created Sunil Poonen |
966 --------------------------------------------------------------------- */
967
968 PROCEDURE Execute_Main_Query (main_query VARCHAR2) IS
969 cursor_id INTEGER;
970 exec_id INTEGER;
971 forecast_cell_id number;
972 forecast_header_id number;
973 forecast_row_id number;
974 forecast_column_id number;
975 reference_id varchar2(100);
976 currency_code varchar2(15);
977 org_id number;
978 trx_date date;
979 bank_account_id number;
980 forecast_amount number;
981 forecast_id number;
982 forecast_rowid VARCHAR2(30);
983 counter number;
984 final_query VARCHAR2(5000);
985 BEGIN
986 cep_standard.debug('>>CE_CSH_FCST_POP.Execute_Main_Query');
987
988 populate_aging_buckets;
989
990 cursor_id := DBMS_SQL.open_cursor;
991 cep_standard.debug('Cursor opened sucessfully with cursor_id: '||
992 to_char(cursor_id));
993
994 cep_standard.debug('Parsing ....');
995 final_query := 'INSERT INTO CE_FORECAST_TRX_CELLS(
996 FORECAST_CELL_ID,
997 FORECAST_ID,
998 FORECAST_HEADER_ID,
999 FORECAST_ROW_ID,
1000 INCLUDE_FLAG,
1001 CREATED_BY,
1002 CREATION_DATE,
1003 LAST_UPDATED_BY,
1004 LAST_UPDATE_DATE,
1005 LAST_UPDATE_LOGIN,
1006 FORECAST_COLUMN_ID,
1007 REFERENCE_ID,
1008 CURRENCY_CODE,
1009 ORG_ID,
1010 TRX_DATE,
1011 BANK_ACCOUNT_ID,
1012 CODE_COMBINATION_ID,
1013 AMOUNT,
1014 TRX_AMOUNT)
1015 ' || main_query;
1016 DBMS_SQL.Parse(cursor_id,
1017 final_query,
1018 DBMS_SQL.v7);
1019
1020 cep_standard.debug('Parsed sucessfully');
1021
1022 exec_id := DBMS_SQL.execute(cursor_id);
1023
1024 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1025
1026 IF(CE_CASH_FCST.G_trx_type IN ('GLB', 'GLE', 'GLA', 'PAY')) THEN
1027 DELETE from ce_forecast_trx_cells
1028 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1029 AND forecast_row_id = CE_CASH_FCST.G_forecast_row_id
1030 AND forecast_column_id = CE_CASH_FCST.G_overdue_column_id;
1031 END IF;
1032
1033 IF(CE_CASH_FCST.G_trx_type IN ('APP', 'ARR') AND
1034 CE_CASH_FCST.G_forecast_method = 'P') THEN
1035 DELETE from ce_forecast_trx_cells
1036 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1037 AND forecast_row_id = CE_CASH_FCST.G_forecast_row_id
1038 AND forecast_column_id = CE_CASH_FCST.G_overdue_column_id;
1039 END IF;
1040
1041 IF(CE_CASH_FCST.G_invalid_overdue_row) THEN
1042 DELETE from ce_forecast_trx_cells
1043 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1044 AND forecast_row_id = CE_CASH_FCST.G_forecast_row_id
1045 AND forecast_column_id = CE_CASH_FCST.G_overdue_column_id;
1046 END IF;
1047
1048 -- Populate CE_FORECAST_OPENING_BAL
1049 Populate_Opening_Bal;
1050
1051 clear_aging_buckets;
1052 zero_fill_cells;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 cep_standard.debug('EXCEPTION - OTHERS: Execute_Main_Query');
1056 IF DBMS_SQL.IS_OPEN(cursor_id) THEN
1057 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1058 cep_standard.debug('Cursor Closed');
1059 END IF;
1060 RAISE;
1061 END Execute_Main_Query;
1062
1063
1064
1065
1066 /* ---------------------------------------------------------------------
1067 | PUBLIC PROCEDURE |
1068 | Use_avg_bal_pos |
1069 | |
1070 | DESCRIPTION |
1071 | Calculates the initial cash position for the forecast start |
1072 | date using the average balance Processing |
1073 | CALLED BY |
1074 | CE_CASH_FCST_POP.Calc_Initial_Cash_Position |
1075 | REQUIRES |
1076 | |
1077 | HISTORY |
1078 | 19-MAR-1997 Created Bidemi Carrol |
1079 --------------------------------------------------------------------- */
1080 FUNCTION Use_Avg_Bal_Pos(p_ccid NUMBER) RETURN NUMBER IS
1081 l_end_of_day NUMBER;
1082 l_ptd_range NUMBER;
1083 l_period_name GL_PERIODS.period_name%TYPE;
1084 BEGIN
1085 cep_standard.debug('>>CE_CSH_FCST_POP.Use_Avg_Bal_Pos');
1086 --
1087 -- Get number of days from start of the period
1088 --
1089 BEGIN
1090 SELECT period_name,
1091 (CE_CASH_FCST.G_rp_forecast_start_date - start_date +1)
1092 INTO l_period_name,
1093 l_ptd_range
1094 FROM gl_periods
1095 WHERE CE_CASH_FCST.G_rp_forecast_start_date BETWEEN start_date and end_date
1096 AND period_set_name = CE_CASH_FCST.G_rp_calendar_name;
1097
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 return 0;
1101 cep_standard.debug('Use_Avg_Bal_Pos.no data found');
1102 WHEN TOO_MANY_ROWS THEN
1103 return 0;
1104 cep_standard.debug('Use_Avg_Bal_Pos.TOO_MANY_ROWS');
1105 WHEN OTHERS THEN
1106 return 0;
1107 cep_standard.debug('Use_Avg_Bal_Pos.OTHERS');
1108 END;
1109 --
1110 -- Get the end of day balance
1111 --
1112 BEGIN
1113 SELECT SUM(DECODE(l_ptd_range, 1, NVL(PERIOD_AGGREGATE1,0),
1114 2, NVL(PERIOD_AGGREGATE2,0)- NVL(PERIOD_AGGREGATE1,0),
1115 3, NVL(PERIOD_AGGREGATE3,0)-NVL(PERIOD_AGGREGATE2,0),
1116 4, NVL(PERIOD_AGGREGATE4,0)-NVL(PERIOD_AGGREGATE3,0),
1117 5, NVL(PERIOD_AGGREGATE5,0)-NVL(PERIOD_AGGREGATE4,0),
1118 6, NVL(PERIOD_AGGREGATE6,0)-NVL(PERIOD_AGGREGATE5,0),
1119 7, NVL(PERIOD_AGGREGATE7,0)-NVL(PERIOD_AGGREGATE6,0),
1120 8, NVL(PERIOD_AGGREGATE8,0)-NVL(PERIOD_AGGREGATE7,0),
1121 9, NVL(PERIOD_AGGREGATE9,0)-NVL(PERIOD_AGGREGATE8,0),
1122 10, NVL(PERIOD_AGGREGATE10,0)-NVL(PERIOD_AGGREGATE9,0),
1123 11, NVL(PERIOD_AGGREGATE11,0)-NVL(PERIOD_AGGREGATE10,0),
1124 12, NVL(PERIOD_AGGREGATE12,0)-NVL(PERIOD_AGGREGATE11,0),
1125 13, NVL(PERIOD_AGGREGATE13,0)-NVL(PERIOD_AGGREGATE12,0),
1126 14, NVL(PERIOD_AGGREGATE14,0)-NVL(PERIOD_AGGREGATE13,0),
1127 15, NVL(PERIOD_AGGREGATE15,0)-NVL(PERIOD_AGGREGATE14,0),
1128 16, NVL(PERIOD_AGGREGATE16,0)-NVL(PERIOD_AGGREGATE15,0),
1129 17, NVL(PERIOD_AGGREGATE17,0)-NVL(PERIOD_AGGREGATE16,0),
1130 18, NVL(PERIOD_AGGREGATE18,0)-NVL(PERIOD_AGGREGATE17,0),
1131 19, NVL(PERIOD_AGGREGATE19,0)-NVL(PERIOD_AGGREGATE18,0),
1132 20, NVL(PERIOD_AGGREGATE20,0)-NVL(PERIOD_AGGREGATE19,0),
1133 21, NVL(PERIOD_AGGREGATE21,0)-NVL(PERIOD_AGGREGATE20,0),
1134 22, NVL(PERIOD_AGGREGATE22,0)-NVL(PERIOD_AGGREGATE21,0),
1135 23, NVL(PERIOD_AGGREGATE23,0)-NVL(PERIOD_AGGREGATE22,0),
1136 24, NVL(PERIOD_AGGREGATE24,0)-NVL(PERIOD_AGGREGATE23,0),
1137 25, NVL(PERIOD_AGGREGATE25,0)-NVL(PERIOD_AGGREGATE24,0),
1138 26, NVL(PERIOD_AGGREGATE26,0)-NVL(PERIOD_AGGREGATE25,0),
1139 27, NVL(PERIOD_AGGREGATE27,0)-NVL(PERIOD_AGGREGATE26,0),
1140 28, NVL(PERIOD_AGGREGATE28,0)-NVL(PERIOD_AGGREGATE27,0),
1141 29, NVL(PERIOD_AGGREGATE29,0)-NVL(PERIOD_AGGREGATE28,0),
1142 30, NVL(PERIOD_AGGREGATE30,0)-NVL(PERIOD_AGGREGATE29,0),
1143 31, NVL(PERIOD_AGGREGATE31,0)-NVL(PERIOD_AGGREGATE30,0),
1144 32, NVL(PERIOD_AGGREGATE32,0)-NVL(PERIOD_AGGREGATE31,0),
1145 33, NVL(PERIOD_AGGREGATE33,0)-NVL(PERIOD_AGGREGATE32,0),
1146 34, NVL(PERIOD_AGGREGATE34,0)-NVL(PERIOD_AGGREGATE33,0),
1147 35, NVL(PERIOD_AGGREGATE35,0)-NVL(PERIOD_AGGREGATE34,0),0)*
1148 DECODE(CE_CASH_FCST.G_rp_exchange_type,'User',CE_CASH_FCST.G_rp_exchange_rate,curr.exchange_rate))
1149 INTO l_end_of_day
1150 FROM gl_daily_balances gdb,
1151 gl_sets_of_books org,
1152 gl_code_combinations glcc,
1153 ce_currency_rates_temp curr
1154 WHERE curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1155 AND curr.currency_code = gdb.currency_code
1156 AND gdb.period_name = l_period_name
1157 AND gdb.currency_code = org.currency_code
1158 AND gdb.currency_type = DECODE(CE_CASH_FCST.G_rp_src_curr_type, 'A', 'U',
1159 'E','C',
1160 'F','U')
1161 AND gdb.code_combination_id = glcc.code_combination_id
1162 AND gdb.actual_flag = 'A'
1163 AND gdb.ledger_id = org.set_of_books_id
1164 AND glcc.template_id IS NULL
1165 AND glcc.summary_flag = 'N'
1166 AND glcc.code_combination_id = p_ccid
1167 AND org.enable_average_balances_flag = 'Y';
1168
1169 cep_standard.debug('<<CE_CSH_FCST_POP.Use_Avg_Bal_Pos');
1170 RETURN( nvl(l_end_of_day,0) );
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN
1173 RETURN(0);
1174 WHEN OTHERS THEN
1175 CEP_STANDARD.DEBUG('EXCEPTION: OTHERS - Use_avg_bal_pos');
1176 RAISE;
1177 END;
1178 END Use_Avg_Bal_Pos;
1179
1180
1181
1182 /* ---------------------------------------------------------------------
1183 | PUBLIC PROCEDURE |
1184 | Calc_Initial_Cash_Position |
1185 | /l_ptd_range |
1186 | DESCRIPTION |
1187 | Calculates the initial cash position for the forecast start |
1188 | date |
1189 | CALLED BY |
1190 | CE_CASH_FCST_POP.Cash_Forecast |
1191 | REQUIRES |
1192 | |
1193 | HISTORY |
1194 | 19-AUG-1996 Created Bidemi Carrol |
1195 --------------------------------------------------------------------- */
1196 FUNCTION Calc_Initial_Cash_Position(p_ccid NUMBER) RETURN NUMBER IS
1197 initial_cash_pos NUMBER;
1198 begin_cash_bal NUMBER;
1199 bal_to_date NUMBER;
1200 begin_period VARCHAR2(30);
1201 avg_bal VARCHAR2(1);
1202 avg_bal_init_cash_pos NUMBER;
1203 BEGIN
1204 cep_standard.debug('>>Calc_Initial_Cash_Position ');
1205 IF (CE_CASH_FCST.G_aging_type = 'D') THEN
1206 --
1207 -- Check to see if average daily balances are used;
1208 --
1209 avg_bal_init_cash_pos := Use_Avg_Bal_Pos(p_ccid);
1210
1211 -- Get Initial Balance
1212 cep_standard.debug('Get initial balance');
1213 cep_standard.debug('CCID: ' || p_ccid);
1214 cep_standard.debug('Avg bal init cash pos: ' || avg_bal_init_cash_pos);
1215 BEGIN
1216 SELECT src.period_name,
1217 nvl(SUM((nvl(src.begin_balance_dr,0)-nvl(src.begin_balance_cr,0))*
1218 DECODE(CE_CASH_FCST.G_rp_exchange_type, 'User', CE_CASH_FCST.G_rp_exchange_rate, curr.exchange_rate)),0)
1219 INTO begin_period,
1220 begin_cash_bal
1221 FROM gl_balances src,
1222 gl_sets_of_books org,
1223 gl_periods gp,
1224 gl_code_combinations glcc,
1225 ce_currency_rates_temp curr
1226 WHERE curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1227 AND curr.currency_code = src.currency_code
1228 AND src.period_name = gp.period_name
1229 AND src.currency_code = DECODE(CE_CASH_FCST.G_rp_src_curr_type,
1230 'A',src.currency_code,
1231 'E',CE_CASH_FCST.G_rp_src_currency,
1232 org.currency_code)
1233 AND NVL(src.translated_flag,'R') = 'R'
1234 AND src.ledger_id = org.set_of_books_id
1235 AND src.actual_flag = 'A'
1236 AND glcc.template_id is NULL
1237 AND glcc.summary_flag = 'N'
1238 AND src.code_combination_id = glcc.code_combination_id
1239 AND glcc.code_combination_id = p_ccid
1240 AND CE_CASH_FCST.G_rp_forecast_start_date BETWEEN gp.start_date AND gp.end_date
1241 AND gp.period_set_name = org.period_set_name
1242 AND gp.period_set_name = CE_CASH_FCST.G_rp_calendar_name
1243 AND nvl(org.enable_average_balances_flag,'N') = 'N'
1244 AND gp.adjustment_period_flag ='N' -- Bug 14272804 Added Where Clause
1245 GROUP BY src.period_name;
1246 cep_standard.debug('INITIAL BALANCE FOR: '|| begin_period || ' is : '|| begin_Cash_bal);
1247 EXCEPTION
1248 WHEN NO_DATA_FOUND THEN
1249 cep_standard.debug('EXCEPTION: Calc Initial Cash pos:no data found');
1250 cep_standard.debug('Intial Cash position is 0');
1251 begin_cash_bal :=0;
1252 WHEN OTHERS THEN
1253 cep_standard.debug('EXCEPTION: Calc Initial Cash pos');
1254 cep_standard.debug('Exception: begin balance failed');
1255 RAISE;
1256 END;
1257
1258 cep_standard.debug('INITIAL PERIOD BALANCE FOR: '|| begin_period || ' is : '|| begin_Cash_bal);
1259 BEGIN
1260 SELECT nvl(SUM((nvl(jl.entered_dr,0) - nvl(jl.entered_cr,0))*
1261 DECODE(CE_CASH_FCST.G_rp_exchange_type, 'User', CE_CASH_FCST.G_rp_exchange_rate, curr.exchange_rate)),0)
1262 INTO bal_to_date
1263 FROM gl_je_lines jl,
1264 gl_je_headers jh,
1265 gl_sets_of_books org,
1266 gl_code_combinations glcc,
1267 ce_currency_rates_temp curr
1268 WHERE curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1269 AND curr.currency_code = jh.currency_code
1270 AND jl.effective_date <= CE_CASH_FCST.G_rp_forecast_start_date
1271 AND jl.status = 'P'
1272 AND jl.period_name = begin_period
1273 AND jh.currency_code = DECODE(CE_CASH_FCST.G_rp_src_curr_type,'E',CE_CASH_FCST.G_rp_src_currency,
1274 jh.currency_code)
1275 AND jl.ledger_id = org.set_of_books_id
1276 AND jl.je_header_id = jh.je_header_id
1277 AND glcc.template_id is NULL
1278 AND glcc.summary_flag = 'N'
1279 AND jl.code_combination_id = glcc.code_combination_id
1280 AND glcc.code_combination_id = p_ccid
1281 AND nvl(org.enable_average_balances_flag,'N') = 'N';
1282
1283 cep_standard.debug('balance to date is: '|| bal_to_date);
1284 cep_standard.debug('1 return');
1285
1286 RETURN(avg_bal_init_cash_pos + begin_cash_bal + bal_to_date);
1287 EXCEPTION
1288 WHEN NO_DATA_FOUND THEN
1289 bal_to_date :=0;
1290 cep_standard.debug('exception:no data found balance_to_date is 0');
1291 cep_standard.debug('2 return');RETURN(begin_cash_bal + bal_to_date);
1292 WHEN OTHERS THEN
1293 cep_standard.debug('EXCEPTION: Calc INitial Cash pos');
1294 cep_standard.debug('Exception: balance to date failed');
1295 RAISE;
1296 END;
1297 ELSE
1298 SELECT SUM((nvl(src.begin_balance_dr,0)-nvl(src.begin_balance_cr,0))*
1299 DECODE(CE_CASH_FCST.G_rp_exchange_type, 'User', CE_CASH_FCST.G_rp_exchange_rate, curr.exchange_rate))
1300 INTO initial_cash_pos
1301 FROM gl_balances src,
1302 gl_sets_of_books org,
1303 gl_code_combinations glcc,
1304 ce_currency_rates_temp curr
1305 WHERE curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1306 AND curr.currency_code = src.currency_code
1307 AND src.period_name = CE_CASH_FCST.G_rp_forecast_start_period
1308 AND org.period_set_name = CE_CASH_FCST.G_rp_calendar_name
1309 AND src.actual_flag = 'A'
1310 AND src.currency_code = DECODE(CE_CASH_FCST.G_rp_src_curr_type,
1311 'A',src.currency_code,
1312 'E',CE_CASH_FCST.G_rp_src_currency,
1313 org.currency_code)
1314 AND NVL(src.translated_flag,'R') = 'R'
1315 AND src.ledger_id = org.set_of_books_id
1316 AND glcc.template_id IS NULL
1317 AND glcc.code_combination_id = src.code_combination_id
1318 AND glcc.code_combination_id = p_ccid;
1319
1320 cep_standard.debug('3 return');
1321
1322 RETURN(initial_cash_pos);
1323
1324 END IF;
1325
1326 cep_standard.debug('4 return');
1327 RETURN initial_cash_pos;
1328 EXCEPTION
1329 WHEN NO_DATA_FOUND THEN
1330 cep_standard.debug('5 return');
1331 return(0);
1332 WHEN OTHERS THEN
1333 cep_standard.debug('EXCEPTION:OTHERS - Calc_Initial_Cash_Position');
1334 RAISE;
1335 END Calc_Initial_Cash_Position;
1336
1337
1338
1339
1340 /* ---------------------------------------------------------------------
1341 | PUBLIC PROCEDURE |
1342 | Populate_Opening_Bal |
1343 | |
1344 | DESCRIPTION |
1345 | This procedure populates CE_FORECAST_OPENING_BAL with the |
1346 | appropriate opening bank balance or opening GL cash account |
1347 | balance if the balance isn't already populated. |
1348 | |
1349 | CALLED BY |
1350 | Execute_Main_Query |
1351 | REQUIRES |
1352 | bank_account_id |
1353 | HISTORY |
1354 | 29-JAN-2003 Created Sunil Poonen |
1355 --------------------------------------------------------------------- */
1356 PROCEDURE Populate_Opening_Bal IS
1357
1358 CURSOR C_bank is SELECT DISTINCT bank_account_id
1359 FROM ce_forecast_trx_cells
1360 WHERE bank_account_id is not null
1361 AND forecast_id = CE_CASH_FCST.G_forecast_id
1362 AND forecast_row_id = CE_CASH_FCST.G_forecast_row_id;
1363
1364 CURSOR C_glcp is SELECT DISTINCT code_combination_id
1365 FROM ce_forecast_trx_cells
1366 WHERE code_combination_id is not null
1367 AND forecast_id = CE_CASH_FCST.G_forecast_id
1368 AND forecast_row_id = CE_CASH_FCST.G_forecast_row_id;
1369
1370 CURSOR C_sub_acct(p_fc_start_date DATE,
1371 p_le_id NUMBER) is
1372 SELECT nvl(ledger_balance,0) ledger_balance,
1373 nvl(cashflow_balance,0) cashflow_balance,
1374 nvl(int_calc_balance,0) int_calc_balance,
1375 nvl(one_day_float,0) one_day_float,
1376 nvl(two_day_float,0) two_day_float,
1377 statement_date+1 balance_date,
1378 currency_code,
1379 account_number,
1380 legal_entity_id
1381 FROM ce_cp_sub_open_bal_v
1382 WHERE trunc(statement_date) < p_fc_start_date
1383 AND trunc(next_stmt_date) >= p_fc_start_date
1384 AND legal_entity_id = nvl(p_le_id, legal_entity_id);
1385
1386
1387 counter NUMBER;
1388 counter2 NUMBER;
1389 xtr_bank NUMBER;
1390 l_opening_balance NUMBER;
1391 l_stmt_balance NUMBER;
1392 l_cflow_balance NUMBER;
1393 l_int_calc_balance NUMBER;
1394 l_one_day_float NUMBER;
1395 l_two_day_float NUMBER;
1396 l_legal_entity_id NUMBER;
1397 l_fc_start_date DATE;
1398 l_balance_date DATE;
1399 l_bank_acc_name VARCHAR2(80);
1400 l_bank_acc_curr VARCHAR2(15);
1401 l_exchange_rate NUMBER;
1402 l_app_cflow NUMBER;
1403 l_arr_cflow NUMBER;
1404 l_xtr_cflow NUMBER;
1405 l_pay_cflow NUMBER;
1406 l_prior_day_cflow NUMBER;
1407 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1408
1409 BEGIN
1410 cep_standard.debug('>>CE_CSH_FCST_POP.Populate_Opening_Bal');
1411
1412 IF (CE_CASH_FCST.G_rp_view_by in ('BANK','ALL')) THEN
1413 IF (CE_CASH_FCST.G_aging_type = 'D') THEN
1414 l_fc_start_date := trunc(CE_CASH_FCST.G_rp_forecast_start_date);
1415 ELSE
1416 select trunc(start_date)
1417 into l_fc_start_date
1418 from gl_periods
1419 where period_set_name = CE_CASH_FCST.G_rp_calendar_name
1420 and period_name = CE_CASH_FCST.G_rp_forecast_start_period;
1421 END IF;
1422
1423 IF (CE_CASH_FCST.G_trx_type IN ('APP','ARR','PAY','XTI','XTO')) THEN
1424 FOR p_bank in C_bank LOOP
1425 SELECT count(1)
1426 INTO counter
1427 FROM ce_forecast_opening_bal
1428 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1429 AND bank_account_id = p_bank.bank_account_id;
1430
1431 IF counter = 0 THEN
1432 SELECT bank_account_name,
1433 account_owner_org_id
1434 INTO l_bank_acc_name,
1435 l_legal_entity_id
1436 FROM ce_bank_accounts ba
1437 WHERE ba.bank_account_id = p_bank.bank_account_id;
1438
1439 SELECT count(1)
1440 INTO counter2
1441 FROM ce_cp_open_bal_v
1442 WHERE bank_account_id = p_bank.bank_account_id
1443 AND trunc(statement_date) < l_fc_start_date;
1444
1445 IF counter2 > 0 THEN
1446 SELECT nvl(ledger_balance,0),
1447 nvl(cashflow_balance,0),
1448 nvl(int_calc_balance,0),
1449 nvl(one_day_float,0),
1450 nvl(two_day_float,0),
1451 statement_date+1,
1452 currency_code
1453 INTO l_stmt_balance,
1454 l_cflow_balance,
1455 l_int_calc_balance,
1456 l_one_day_float,
1457 l_two_day_float,
1458 l_balance_date,
1459 l_bank_acc_curr
1460 FROM ce_cp_open_bal_v
1461 WHERE bank_account_id = p_bank.bank_account_id
1462 AND trunc(statement_date) < l_fc_start_date
1463 AND trunc(next_stmt_date) >= l_fc_start_date;
1464
1465 IF CE_CASH_FCST.G_rp_bank_balance_type = 'L' THEN
1466 l_opening_balance := l_stmt_balance;
1467 ELSIF CE_CASH_FCST.G_rp_bank_balance_type = 'C' THEN
1468 l_opening_balance := l_cflow_balance;
1469 ELSE
1470 l_opening_balance := l_int_calc_balance;
1471 END IF;
1472
1473 IF CE_CASH_FCST.G_rp_float_type = 'ADD1' THEN
1474 l_opening_balance := l_opening_balance + l_one_day_float;
1475 ELSIF CE_CASH_FCST.G_rp_float_type = 'ADD2' THEN
1476 l_opening_balance := l_opening_balance + l_two_day_float;
1477 ELSIF CE_CASH_FCST.G_rp_float_type = 'SUB1' THEN
1478 l_opening_balance := l_opening_balance - l_one_day_float;
1479 ELSIF CE_CASH_FCST.G_rp_float_type = 'SUB2' THEN
1480 l_opening_balance := l_opening_balance - l_two_day_float;
1481 END IF;
1482
1483 IF (l_bank_acc_curr <> CE_CASH_FCST.G_rp_forecast_currency) THEN
1484 IF (CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
1485 l_opening_balance := l_opening_balance * CE_CASH_FCST.G_rp_exchange_rate;
1486 ELSE
1487 BEGIN
1488 cep_standard.debug('>>Bank Account currency conversion');
1489
1490 select exchange_rate
1491 into l_exchange_rate
1492 from ce_currency_rates_temp
1493 where currency_code = l_bank_acc_curr
1494 and to_currency = CE_CASH_FCST.G_rp_forecast_currency
1495 and forecast_request_id = CE_CASH_FCST.G_forecast_id;
1496
1497 l_opening_balance := l_opening_balance * l_exchange_rate;
1498
1499 cep_standard.debug('<<Bank Account currency conversion');
1500 EXCEPTION
1501 WHEN NO_DATA_FOUND THEN
1502 UPDATE ce_forecasts
1503 SET error_status = 'X'
1504 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1505
1506 FND_MESSAGE.set_name('CE', 'CE_FC_NO_BANK_EXCH_RATE');
1507 FND_MESSAGE.set_token('FROM_CURR', l_bank_acc_curr);
1508 FND_MESSAGE.set_token('TO_CURR', CE_CASH_FCST.G_rp_forecast_currency);
1509 FND_MESSAGE.set_token('BANK_ACCOUNT_NAME', l_bank_acc_name);
1510 error_msg := FND_MESSAGE.get;
1511 CE_FORECAST_ERRORS_PKG.insert_row(
1512 CE_CASH_FCST.G_forecast_id,
1513 CE_CASH_FCST.G_rp_forecast_header_id,
1514 CE_CASH_FCST.G_forecast_row_id,
1515 'CE_FC_NO_BANK_EXCH_RATE',
1516 error_msg);
1517 cep_standard.debug('EXCEPTION: Populate_Opening_Bal - No exchange rate found');
1518
1519 l_opening_balance := 0;
1520 WHEN OTHERS Then
1521 cep_standard.debug('EXCEPTION: Populate_Opening_Bal - Bank Account Currency exchange rate conversion');
1522 raise;
1523 END;
1524 END IF;
1525 END IF;
1526
1527 l_opening_balance := round(l_opening_balance,CE_CASH_FCST.G_precision);
1528
1529 -- If overdue and prior-day overlap, issue warning
1530 IF (l_balance_date < l_fc_start_date AND
1531 CE_CASH_FCST.G_overdue_transactions = 'INCLUDE') THEN
1532 UPDATE ce_forecasts
1533 SET error_status = 'X'
1534 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1535
1536 FND_MESSAGE.set_name('CE', 'CE_FC_PD_OD_OVERLAP');
1537 FND_MESSAGE.set_token('BANK_ACCOUNT_NAME', l_bank_acc_name);
1538 error_msg := FND_MESSAGE.get;
1539 CE_FORECAST_ERRORS_PKG.insert_row(
1540 CE_CASH_FCST.G_forecast_id,
1541 CE_CASH_FCST.G_rp_forecast_header_id,
1542 CE_CASH_FCST.G_forecast_row_id,
1543 'CE_FC_PD_OD_OVERLAP',
1544 error_msg);
1545 END IF;
1546
1547 -- Calculate Prior-day Cashflow
1548 IF(CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
1549 SELECT SUM(amount*CE_CASH_FCST.G_rp_exchange_rate)
1550 INTO l_app_cflow
1551 FROM ce_ap_fc_payments_v
1552 WHERE bank_account_id = p_bank.bank_account_id
1553 AND payment_date >= l_balance_date
1554 AND payment_date < l_fc_start_date;
1555
1556 SELECT SUM(amount*CE_CASH_FCST.G_rp_exchange_rate)
1557 INTO l_arr_cflow
1558 FROM ce_ar_fc_receipts_v
1559 WHERE bank_account_id = p_bank.bank_account_id
1560 AND cash_activity_date >= l_balance_date
1561 AND cash_activity_date < l_fc_start_date;
1562
1563 SELECT SUM(amount*CE_CASH_FCST.G_rp_exchange_rate)
1564 INTO l_xtr_cflow
1565 FROM ce_xtr_cashflows_v
1566 WHERE bank_account_id = p_bank.bank_account_id
1567 AND trx_date >= l_balance_date
1568 AND trx_date < l_fc_start_date;
1569
1570 SELECT SUM(amount)
1571 INTO l_pay_cflow
1572 FROM ce_pay_fc_payroll_v
1573 WHERE bank_account_id = p_bank.bank_account_id
1574 AND trx_date >= l_balance_date
1575 AND trx_date < l_fc_start_date;
1576 ELSE
1577 SELECT SUM(src.amount*curr.exchange_rate)
1578 INTO l_app_cflow
1579 FROM ce_ap_fc_payments_v src,
1580 ce_currency_rates_temp curr
1581 WHERE src.bank_account_id = p_bank.bank_account_id
1582 AND src.payment_date >= l_balance_date
1583 AND src.payment_date < l_fc_start_date
1584 AND curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1585 AND curr.to_currency = CE_CASH_FCST.G_rp_forecast_currency
1586 AND curr.currency_code = src.currency_code;
1587
1588 SELECT SUM(src.amount*curr.exchange_rate)
1589 INTO l_arr_cflow
1590 FROM ce_ar_fc_receipts_v src,
1591 ce_currency_rates_temp curr
1592 WHERE src.bank_account_id = p_bank.bank_account_id
1593 AND src.cash_activity_date >= l_balance_date
1594 AND src.cash_activity_date < l_fc_start_date
1595 AND curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1596 AND curr.to_currency = CE_CASH_FCST.G_rp_forecast_currency
1597 AND curr.currency_code = src.currency_code;
1598
1599 SELECT SUM(src.amount*curr.exchange_rate)
1600 INTO l_xtr_cflow
1601 FROM ce_xtr_cashflows_v src,
1602 ce_currency_rates_temp curr
1603 WHERE src.bank_account_id = p_bank.bank_account_id
1604 AND src.trx_date >= l_balance_date
1605 AND src.trx_date < l_fc_start_date
1606 AND curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1607 AND curr.to_currency = CE_CASH_FCST.G_rp_forecast_currency
1608 AND curr.currency_code = src.currency_code;
1609
1610 SELECT SUM(src.amount*curr.exchange_rate)
1611 INTO l_pay_cflow
1612 FROM ce_pay_fc_payroll_v src,
1613 ce_currency_rates_temp curr
1614 WHERE src.bank_account_id = p_bank.bank_account_id
1615 AND src.trx_date >= l_balance_date
1616 AND src.trx_date < l_fc_start_date
1617 AND curr.forecast_request_id = CE_CASH_FCST.G_forecast_id
1618 AND curr.to_currency = CE_CASH_FCST.G_rp_forecast_currency
1619 AND curr.currency_code = src.currency_code;
1620 END IF;
1621
1622 l_prior_day_cflow := round(nvl(l_arr_cflow,0) - nvl(l_app_cflow,0)
1623 + nvl(l_xtr_cflow,0) - nvl(l_pay_cflow,0),CE_CASH_FCST.G_precision);
1624
1625
1626 ELSE -- no bank balances available before forecast start date
1627 l_opening_balance := 0;
1628 l_prior_day_cflow := 0;
1629 l_balance_date := null;
1630
1631 UPDATE ce_forecasts
1632 SET error_status = 'X'
1633 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1634
1635 FND_MESSAGE.set_name('CE', 'CE_FC_NO_BANK_BALANCE');
1636 FND_MESSAGE.set_token('BANK_ACCOUNT_NAME', l_bank_acc_name);
1637 error_msg := FND_MESSAGE.get;
1638 CE_FORECAST_ERRORS_PKG.insert_row(
1639 CE_CASH_FCST.G_forecast_id,
1640 CE_CASH_FCST.G_rp_forecast_header_id,
1641 CE_CASH_FCST.G_forecast_row_id,
1642 'CE_FC_NO_BANK_BALANCE',
1643 error_msg);
1644 END IF;
1645
1646 INSERT INTO ce_forecast_opening_bal
1647 (balance_id,
1648 forecast_id,
1649 balance_type,
1650 bank_account_id,
1651 code_combination_id,
1652 opening_balance,
1653 balance_date,
1654 prior_day_cflow,
1655 legal_entity_id,
1656 created_by,
1657 creation_date,
1658 last_updated_by,
1659 last_update_date,
1660 last_update_login)
1661 VALUES
1662 (CE_FORECAST_OPENING_BAL_S.nextval,
1663 CE_CASH_FCST.G_forecast_id,
1664 'BANK',
1665 p_bank.bank_account_id,
1666 null,
1667 l_opening_balance,
1668 l_balance_date,
1669 l_prior_day_cflow,
1670 l_legal_entity_id,
1671 nvl(fnd_global.user_id,-1),
1672 sysdate,
1673 nvl(fnd_global.user_id,-1),
1674 sysdate,
1675 nvl(fnd_global.user_id,-1));
1676
1677 END IF;
1678 END LOOP;
1679 END IF;
1680
1681 l_opening_balance := 0;
1682
1683 -- Insert subsidiary bank balances as well
1684 IF (nvl(CE_CASH_FCST.G_rp_include_sub_account,'N') = 'Y') THEN
1685 IF (nvl(G_sub_accounts_complete,'N') = 'N') THEN
1686 IF (CE_CASH_FCST.G_legal_entity_id is null) THEN
1687 G_sub_accounts_complete := 'Y';
1688 END IF;
1689
1690 -- Check if we have already processed this legal entity
1691 SELECT count(1)
1692 INTO counter
1693 FROM ce_forecast_opening_bal
1694 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1695 AND bank_account_id = -2
1696 AND legal_entity_id = nvl(CE_CASH_FCST.G_legal_entity_id,-1);
1697
1698 IF counter = 0 THEN
1699 FOR p_sub_acct in C_sub_acct(l_fc_start_date,CE_CASH_FCST.G_legal_entity_id) LOOP
1700 IF CE_CASH_FCST.G_rp_bank_balance_type = 'L' THEN
1701 l_opening_balance := p_sub_acct.ledger_balance;
1702 ELSIF CE_CASH_FCST.G_rp_bank_balance_type = 'C' THEN
1703 l_opening_balance := p_sub_acct.cashflow_balance;
1704 ELSE
1705 l_opening_balance := p_sub_acct.int_calc_balance;
1706 END IF;
1707
1708 IF CE_CASH_FCST.G_rp_float_type = 'ADD1' THEN
1709 l_opening_balance := l_opening_balance + p_sub_acct.one_day_float;
1710 ELSIF CE_CASH_FCST.G_rp_float_type = 'ADD2' THEN
1711 l_opening_balance := l_opening_balance + p_sub_acct.two_day_float;
1712 ELSIF CE_CASH_FCST.G_rp_float_type = 'SUB1' THEN
1713 l_opening_balance := l_opening_balance - p_sub_acct.one_day_float;
1714 ELSIF CE_CASH_FCST.G_rp_float_type = 'SUB2' THEN
1715 l_opening_balance := l_opening_balance - p_sub_acct.two_day_float;
1716 END IF;
1717
1718 IF (p_sub_acct.currency_code <> CE_CASH_FCST.G_rp_forecast_currency) THEN
1719 IF (CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
1720 l_opening_balance := l_opening_balance * CE_CASH_FCST.G_rp_exchange_rate;
1721 ELSE
1722 BEGIN
1723 cep_standard.debug('>>Subsidiary Bank Account currency conversion');
1724
1725 select exchange_rate
1726 into l_exchange_rate
1727 from ce_currency_rates_temp
1728 where currency_code = p_sub_acct.currency_code
1729 and to_currency = CE_CASH_FCST.G_rp_forecast_currency
1730 and forecast_request_id = CE_CASH_FCST.G_forecast_id;
1731
1732 l_opening_balance := l_opening_balance * l_exchange_rate;
1733
1734 cep_standard.debug('<<Subsidiary Bank Account currency conversion');
1735 EXCEPTION
1736 WHEN NO_DATA_FOUND THEN
1737 UPDATE ce_forecasts
1738 SET error_status = 'X'
1739 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1740
1741 FND_MESSAGE.set_name('CE', 'CE_FC_NO_SUB_EXCH_RATE');
1742 FND_MESSAGE.set_token('FROM_CURR', p_sub_acct.currency_code);
1743 FND_MESSAGE.set_token('TO_CURR', CE_CASH_FCST.G_rp_forecast_currency);
1744 FND_MESSAGE.set_token('ACCOUNT_NUMBER', p_sub_acct.account_number);
1745 error_msg := FND_MESSAGE.get;
1746 CE_FORECAST_ERRORS_PKG.insert_row(
1747 CE_CASH_FCST.G_forecast_id,
1748 CE_CASH_FCST.G_rp_forecast_header_id,
1749 CE_CASH_FCST.G_forecast_row_id,
1750 'CE_FC_NO_SUB_EXCH_RATE',
1751 error_msg);
1752 cep_standard.debug('EXCEPTION: Populate_Opening_Bal - Subsidiary - No exchange rate found');
1753
1754 l_opening_balance := 0;
1755 WHEN OTHERS Then
1756 cep_standard.debug('EXCEPTION: Populate_Opening_Bal - Subsidiary Bank Account Currency exchange rate conversion');
1757 raise;
1758 END;
1759 END IF;
1760 END IF;
1761
1762 l_opening_balance := round(l_opening_balance,CE_CASH_FCST.G_precision);
1763
1764 INSERT INTO ce_forecast_opening_bal
1765 (balance_id,
1766 forecast_id,
1767 balance_type,
1768 bank_account_id,
1769 code_combination_id,
1770 opening_balance,
1771 balance_date,
1772 prior_day_cflow,
1773 legal_entity_id,
1774 created_by,
1775 creation_date,
1776 last_updated_by,
1777 last_update_date,
1778 last_update_login)
1779 VALUES
1780 (CE_FORECAST_OPENING_BAL_S.nextval,
1781 CE_CASH_FCST.G_forecast_id,
1782 'BANK',
1783 -2,
1784 null,
1785 l_opening_balance,
1786 p_sub_acct.balance_date,
1787 null,
1788 p_sub_acct.legal_entity_id,
1789 nvl(fnd_global.user_id,-1),
1790 sysdate,
1791 nvl(fnd_global.user_id,-1),
1792 sysdate,
1793 nvl(fnd_global.user_id,-1));
1794
1795 END LOOP;
1796
1797 END IF;
1798 END IF;
1799 END IF;
1800 END IF;
1801 IF (CE_CASH_FCST.G_rp_view_by in ('GLCP','ALL')) THEN
1802 IF (CE_CASH_FCST.G_trx_type IN ('APP','ARR','PAY','XTI','XTO')) THEN
1803 FOR p_glcp in C_glcp LOOP
1804 SELECT count(1)
1805 INTO counter
1806 FROM ce_forecast_opening_bal
1807 WHERE forecast_id = CE_CASH_FCST.G_forecast_id
1808 AND code_combination_id = p_glcp.code_combination_id;
1809 IF (counter = 0) THEN
1810 l_opening_balance := Calc_Initial_Cash_Position(p_glcp.code_combination_id);
1811 l_opening_balance := round(l_opening_balance,CE_CASH_FCST.G_precision);
1812
1813 INSERT INTO ce_forecast_opening_bal
1814 (balance_id,
1815 forecast_id,
1816 balance_type,
1817 bank_account_id,
1818 code_combination_id,
1819 opening_balance,
1820 legal_entity_id,
1821 created_by,
1822 creation_date,
1823 last_updated_by,
1824 last_update_date,
1825 last_update_login)
1826 VALUES
1827 (CE_FORECAST_OPENING_BAL_S.nextval,
1828 CE_CASH_FCST.G_forecast_id,
1829 'GLCP',
1830 null,
1831 p_glcp.code_combination_id,
1832 l_opening_balance,
1833 null,
1834 nvl(fnd_global.user_id,-1),
1835 sysdate,
1836 nvl(fnd_global.user_id,-1),
1837 sysdate,
1838 nvl(fnd_global.user_id,-1));
1839
1840 END IF;
1841 END LOOP;
1842 END IF;
1843 END IF;
1844
1845 cep_standard.debug('<<CE_CSH_FCST_POP.Populate_Opening_Bal');
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 CEP_STANDARD.DEBUG('EXCEPTION:Populate_Opening_Bal');
1849 raise;
1850 END Populate_Opening_Bal;
1851
1852
1853 /* ---------------------------------------------------------------------
1854 | PUBLIC PROCEDURE |
1855 | Build_AP_Pay_Query |
1856 | |
1857 | DESCRIPTION |
1858 | This procedure builds the query to calculate the forecast |
1859 | amounts for AP payments that were made in the past. |
1860 | CALLED BY |
1861 | Populate_Cells |
1862 | REQUIRES |
1863 | main_query |
1864 | HISTORY |
1865 | 12-JUL-1996 Created Bidemi Carrol |
1866 --------------------------------------------------------------------- */
1867 PROCEDURE Build_AP_Pay_Query IS
1868 from_clause VARCHAR2(500);
1869 where_clause varchar2(2000);
1870 select_clause varchar2(2000);
1871 main_query varchar2(3500) := null;
1872 counter number;
1873 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1874
1875 BEGIN
1876
1877 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_AP_Pay_Query');
1878
1879 from_clause := Get_From_Clause('ce_ap_fc_payments_v');
1880 cep_standard.debug('Built From Clause');
1881
1882 where_clause := Get_Where_Clause || Add_Where('PAYMENT_METHOD') || Add_Where('BANK_ACCOUNT_ID');
1883
1884 IF (NVL(CE_CASH_FCST.G_forecast_method,'F') = 'P') THEN
1885
1886 BEGIN
1887 Set_History;
1888
1889 EXCEPTION
1890 When NO_DATA_FOUND Then
1891 cep_standard.debug('row_id = ' || to_char(CE_CASH_FCST.G_forecast_row_id));
1892
1893 UPDATE ce_forecasts
1894 SET error_status = 'E'
1895 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1896
1897 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
1898 error_msg := FND_MESSAGE.get;
1899 CE_FORECAST_ERRORS_PKG.insert_row(
1900 CE_CASH_FCST.G_forecast_id,
1901 CE_CASH_FCST.G_rp_forecast_header_id,
1902 CE_CASH_FCST.G_forecast_row_id,
1903 'CE_NO_HIST_START_PERIOD',
1904 error_msg);
1905 zero_fill_cells;
1906 cep_standard.debug('EXCEPTION: No history data found for APP');
1907 return;
1908 When OTHERS Then
1909 cep_standard.debug('EXCEPTION: Build APP query - Set History');
1910 raise;
1911 END;
1912 IF (CE_CASH_FCST.G_order_date_type = 'V') THEN
1913 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
1914 select_clause := '
1915 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
1916 '||CE_CASH_FCST.G_forecast_id||',
1917 '||CE_CASH_FCST.G_rp_forecast_header_id||',
1918 '||CE_CASH_FCST.G_forecast_row_id||',
1919 ''Y'',
1920 nvl(fnd_global.user_id,-1),
1921 sysdate,
1922 nvl(fnd_global.user_id,-1),
1923 sysdate,
1924 nvl(fnd_global.user_id,-1),
1925 cab.forecast_column_id,
1926 src.reference_id,
1927 src.currency_code,
1928 to_number(hr_ou.ORGANIZATION_ID),
1929 NVL(src.actual_value_date, src.cleared_date),
1930 src.bank_account_id,
1931 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
1932 round(nvl(-src.amount,0)*'
1933 ||CE_CASH_FCST.G_rp_exchange_rate
1934 ||','||CE_CASH_FCST.G_precision||'),
1935 -src.amount';
1936 ELSE
1937 select_clause := '
1938 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
1939 '||CE_CASH_FCST.G_forecast_id||',
1940 '||CE_CASH_FCST.G_rp_forecast_header_id||',
1941 '||CE_CASH_FCST.G_forecast_row_id||',
1942 ''Y'',
1943 nvl(fnd_global.user_id,-1),
1944 sysdate,
1945 nvl(fnd_global.user_id,-1),
1946 sysdate,
1947 nvl(fnd_global.user_id,-1),
1948 cab.forecast_column_id,
1949 src.reference_id,
1950 src.currency_code,
1951 to_number(hr_ou.ORGANIZATION_ID),
1952 NVL(src.actual_value_date, src.cleared_date),
1953 src.bank_account_id,
1954 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
1955 round(nvl(-src.amount,0)*curr.exchange_rate'
1956 ||','||CE_CASH_FCST.G_precision||'),
1957 -src.amount';
1958 END IF;
1959 cep_standard.debug('Built Select Clause');
1960
1961 where_clause := where_clause || '
1962 AND NVL(src.actual_value_date, src.cleared_date) BETWEEN cab.start_date and cab.end_date
1963 AND src.status <> ''NEGOTIABLE'' ';
1964 ELSE
1965 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
1966 select_clause := '
1967 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
1968 '||CE_CASH_FCST.G_forecast_id||',
1969 '||CE_CASH_FCST.G_rp_forecast_header_id||',
1970 '||CE_CASH_FCST.G_forecast_row_id||',
1971 ''Y'',
1972 nvl(fnd_global.user_id,-1),
1973 sysdate,
1974 nvl(fnd_global.user_id,-1),
1975 sysdate,
1976 nvl(fnd_global.user_id,-1),
1977 cab.forecast_column_id,
1978 src.reference_id,
1979 src.currency_code,
1980 to_number(hr_ou.ORGANIZATION_ID),
1981 src.cleared_date,
1982 src.bank_account_id,
1983 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
1984 round(nvl(-src.amount,0)*'
1985 ||CE_CASH_FCST.G_rp_exchange_rate
1986 ||','||CE_CASH_FCST.G_precision||'),
1987 -src.amount';
1988 ELSE
1989 select_clause := '
1990 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
1991 '||CE_CASH_FCST.G_forecast_id||',
1992 '||CE_CASH_FCST.G_rp_forecast_header_id||',
1993 '||CE_CASH_FCST.G_forecast_row_id||',
1994 ''Y'',
1995 nvl(fnd_global.user_id,-1),
1996 sysdate,
1997 nvl(fnd_global.user_id,-1),
1998 sysdate,
1999 nvl(fnd_global.user_id,-1),
2000 cab.forecast_column_id,
2001 src.reference_id,
2002 src.currency_code,
2003 to_number(hr_ou.ORGANIZATION_ID),
2004 src.cleared_date,
2005 src.bank_account_id,
2006 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
2007 round(nvl(-src.amount,0)*curr.exchange_rate'
2008 ||','||CE_CASH_FCST.G_precision||'),
2009 -src.amount';
2010 END IF;
2011 cep_standard.debug('Built Select Clause');
2012
2013 where_clause := where_clause || '
2014 AND src.cleared_date BETWEEN cab.start_date and cab.end_date
2015 AND src.status <> ''NEGOTIABLE'' ';
2016 END IF;
2017 ELSE
2018 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
2019 select_clause := '
2020 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2021 '||CE_CASH_FCST.G_forecast_id||',
2022 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2023 '||CE_CASH_FCST.G_forecast_row_id||',
2024 ''Y'',
2025 nvl(fnd_global.user_id,-1),
2026 sysdate,
2027 nvl(fnd_global.user_id,-1),
2028 sysdate,
2029 nvl(fnd_global.user_id,-1),
2030 cab.forecast_column_id,
2031 src.reference_id,
2032 src.currency_code,
2033 to_number(hr_ou.ORGANIZATION_ID),
2034 NVL(src.actual_value_date,NVL(src.anticipated_value_date,NVL(src.maturity_date,src.payment_date))) +'
2035 ||to_char(CE_CASH_FCST.G_lead_time) || ',
2036 src.bank_account_id,
2037 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
2038 round(nvl(-src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
2039 ||','||CE_CASH_FCST.G_precision||'),
2040 -src.amount';
2041 ELSE
2042 select_clause := '
2043 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2044 '||CE_CASH_FCST.G_forecast_id||',
2045 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2046 '||CE_CASH_FCST.G_forecast_row_id||',
2047 ''Y'',
2048 nvl(fnd_global.user_id,-1),
2049 sysdate,
2050 nvl(fnd_global.user_id,-1),
2051 sysdate,
2052 nvl(fnd_global.user_id,-1),
2053 cab.forecast_column_id,
2054 src.reference_id,
2055 src.currency_code,
2056 to_number(hr_ou.ORGANIZATION_ID),
2057 NVL(src.actual_value_date,NVL(src.anticipated_value_date,NVL(src.maturity_date,src.payment_date))) +'
2058 ||to_char(CE_CASH_FCST.G_lead_time) || ',
2059 src.bank_account_id,
2060 nvl(ccid.ap_asset_ccid, ccid.asset_code_combination_id),
2061 round(nvl(-src.amount,0)*curr.exchange_rate'
2062 ||','||CE_CASH_FCST.G_precision||'),
2063 -src.amount';
2064 END IF;
2065 cep_standard.debug('Built Select Clause');
2066
2067 where_clause := where_clause || '
2068 AND NVL(src.actual_value_date,NVL(src.anticipated_value_date,NVL(src.maturity_date,src.payment_date))) BETWEEN cab.start_date - '
2069 ||to_char(CE_CASH_FCST.G_lead_time)||
2070 ' and cab.end_date - '
2071 ||to_char(CE_CASH_FCST.G_lead_time)||
2072 ' AND src.status in (''NEGOTIABLE'',''ISSUED'') ';
2073 END IF;
2074 cep_standard.debug('Built Where Clause');
2075
2076 main_query := select_clause || from_clause || where_clause;
2077
2078 Execute_Main_Query (main_query);
2079 cep_standard.debug('<<ce_csh_fcST_POP.Build_AP_Pay_Query');
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 CEP_STANDARD.DEBUG('EXCEPTION:Build_AP_Pay_Query');
2083 raise;
2084 END Build_AP_Pay_Query;
2085
2086
2087 /* ---------------------------------------------------------------------
2088 | PUBLIC PROCEDURE |
2089 | Build_AP_Project_Inv_Query |
2090 | |
2091 | DESCRIPTION |
2092 | This procedure builds the query to calculate the forecast |
2093 | amounts for AP invoice distributions that have not been paid, |
2094 | but projected to be paid within the aging date ranges. |
2095 | It is assumed that payments will be made on one of the discount |
2096 | dates |
2097 | CALLED BY |
2098 | Populate_Cells |
2099 | REQUIRES |
2100 | main_query |
2101 | HISTORY |
2102 | 12-JUL-1996 Created Bidemi Carrol |
2103 --------------------------------------------------------------------- */
2104 PROCEDURE Build_AP_Project_Inv_Query IS
2105 from_clause VARCHAR2(500);
2106 where_clause varchar2(1500);
2107 select_clause varchar2(1500);
2108 main_query varchar2(3500) := null;
2109 view_name VARCHAR2(50);
2110
2111 BEGIN
2112 cep_standard.debug('>>Build_AP_Project_Inv_Query');
2113
2114 select_clause := Get_Select_Clause;
2115 cep_standard.debug('Built Select Clause');
2116
2117 IF (NVL(CE_CASH_FCST.G_discount_option,'N') = 'N') THEN
2118 cep_standard.debug('Discount NOT taken');
2119 from_clause := Get_From_Clause('ce_due_project_inv_v');
2120 ELSE cep_standard.debug('Discount taken');
2121 from_clause := Get_From_Clause('ce_disc_project_inv_v');
2122 END IF;
2123 cep_standard.debug('Built From Clause');
2124
2125 where_clause := Get_Where_Clause || '
2126 AND src.trx_date BETWEEN cab.start_date - '
2127 ||to_char(CE_CASH_FCST.G_lead_time)||
2128 ' and cab.end_date - '
2129 ||to_char(CE_CASH_FCST.G_lead_time)||
2130 Add_Where('PROJECT_ID') ||
2131 Add_Where('PAYMENT_PRIORITY') || Add_Where('PAY_GROUP') || Add_Where('VENDOR_TYPE') ||
2132 Add_Where('INCLUDE_HOLD_FLAG');
2133
2134 cep_standard.debug('Built Where Clause');
2135
2136 main_query := select_clause || from_clause || where_clause;
2137
2138 Execute_Main_Query (main_query);
2139
2140 cep_standard.debug('<<CE_CSH_FCST_POP.Build_AP_Project_Inv_Query');
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 CEP_STANDARD.DEBUG('EXCEPTION:Build_AP_Project_Inv_Query');
2144 raise;
2145 END Build_AP_Project_Inv_Query;
2146
2147
2148 /* ---------------------------------------------------------------------
2149 | PUBLIC PROCEDURE |
2150 | Build_AP_Invoice_Query |
2151 | |
2152 | DESCRIPTION |
2153 | This procedure builds the query to calculate the forecast |
2154 | amounts for AP invoices that have not been paid, but projected |
2155 | to be paid within the aging date ranges . It is assumed that |
2156 | payments will be made on one of the discount dates |
2157 | CALLED BY |
2158 | Populate_Cells |
2159 | REQUIRES |
2160 | main_query |
2161 | HISTORY |
2162 | 12-JUL-1996 Created Bidemi Carrol |
2163 | 17-Feb-2010 Bug 9252881 Changed the value for select_clause|
2164 | so that the value ccid.asset_code_combination_id|
2165 | is modified to |
2166 | nvl(ccid.ar_asset_ccid, ccid.asset_code_combination_id) |
2167 --------------------------------------------------------------------- */
2168 PROCEDURE Build_AP_Invoice_Query IS
2169 from_clause VARCHAR2(500);
2170 where_clause varchar2(1500);
2171 select_clause varchar2(1500);
2172 main_query varchar2(3500) := null;
2173 view_name VARCHAR2(50);
2174
2175 BEGIN
2176 cep_standard.debug('>>Build_AP_Invoice_Query');
2177
2178 select_clause := Get_Select_Clause;
2179 cep_standard.debug('Built Select Clause');
2180
2181 IF (NVL(CE_CASH_FCST.G_discount_option,'N') = 'N') THEN
2182 cep_standard.debug('Discount NOT taken');
2183 from_clause := Get_From_Clause('ce_ap_fc_due_invoices_v');
2184 ELSE
2185 cep_standard.debug('Discount taken');
2186 from_clause := Get_From_Clause('ce_disc_invoices_v');
2187 END IF;
2188 cep_standard.debug('Built From Clause');
2189
2190 where_clause := Get_Where_Clause || '
2191 AND src.trx_date BETWEEN cab.start_date - '
2192 ||to_char(CE_CASH_FCST.G_lead_time)||
2193 ' and cab.end_date - '
2194 ||to_char(CE_CASH_FCST.G_lead_time)||
2195 Add_Where('PROJECT_ID') ||
2196 Add_Where('PAYMENT_PRIORITY') || Add_Where('PAY_GROUP') || Add_Where('VENDOR_TYPE') ||
2197 Add_Where('INCLUDE_HOLD_FLAG');
2198
2199 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL) THEN
2200 where_clause := where_clause || '
2201 AND src.invoice_id NOT IN ( select invoice_id
2202 from ap_invoice_distributions_all
2203 where project_id = ' || to_char(CE_CASH_FCST.G_rp_project_id) || ')';
2204
2205 END IF;
2206
2207 cep_standard.debug('Built Where Clause');
2208
2209 main_query := select_clause || from_clause || where_clause;
2210
2211 Execute_Main_Query (main_query);
2212
2213 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL) THEN
2214 Build_AP_Project_Inv_Query;
2215 END IF;
2216
2217 cep_standard.debug('<<CE_CSH_FCST_POP.Build_AP_Invoice_Query');
2218 EXCEPTION
2219 WHEN OTHERS THEN
2220 CEP_STANDARD.DEBUG('EXCEPTION:Build_AP_Invoice_Query');
2221 raise;
2222 END Build_AP_Invoice_Query;
2223
2224
2225 /* ---------------------------------------------------------------------
2226 | PUBLIC PROCEDURE |
2227 | Build_PA_Invoice_Query |
2228 | |
2229 | DESCRIPTION |
2230 | This procedure builds the query to calculate the forecast |
2231 | amounts for PA invoices which does not transferred to AR yet. |
2232 | CALLED BY |
2233 | Build_AR_Invoice_Query |
2234 | REQUIRES |
2235 | main_query |
2236 | HISTORY |
2237 | 20-NOV-1998 Created BHChung |
2238 --------------------------------------------------------------------- */
2239 PROCEDURE Build_PA_Invoice_Query IS
2240 from_clause VARCHAR2(500);
2241 where_clause varchar2(1500);
2242 select_clause varchar2(1500);
2243 main_query varchar2(3500) := null;
2244 BEGIN
2245 CE_CASH_FCST.G_app_short_name := 'PA';
2246
2247 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
2248 select_clause := '
2249 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2250 '||CE_CASH_FCST.G_forecast_id||',
2251 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2252 '||CE_CASH_FCST.G_forecast_row_id||',
2253 ''Y'',
2254 nvl(fnd_global.user_id,-1),
2255 sysdate,
2256 nvl(fnd_global.user_id,-1),
2257 sysdate,
2258 nvl(fnd_global.user_id,-1),
2259 cab.forecast_column_id,
2260 ''PA'' || src.project_id || ''X'' || src.trx_number
2261 || ''X'' || src.line_num,
2262 src.currency_code,
2263 to_number(hr_ou.ORGANIZATION_ID),
2264 src.trx_date,
2265 null,
2266 null,
2267 round(nvl(src.amount,0)*'
2268 ||CE_CASH_FCST.G_rp_exchange_rate
2269 ||','||CE_CASH_FCST.G_precision||'),
2270 src.amount';
2271 ELSE
2272 select_clause := '
2273 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2274 '||CE_CASH_FCST.G_forecast_id||',
2275 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2276 '||CE_CASH_FCST.G_forecast_row_id||',
2277 ''Y'',
2278 nvl(fnd_global.user_id,-1),
2279 sysdate,
2280 nvl(fnd_global.user_id,-1),
2281 sysdate,
2282 nvl(fnd_global.user_id,-1),
2283 cab.forecast_column_id,
2284 ''PA'' || src.project_id || ''X'' || src.trx_number
2285 || ''X'' || src.line_num,
2286 src.currency_code,
2287 to_number(hr_ou.ORGANIZATION_ID),
2288 src.trx_date,
2289 null,
2290 null,
2291 round(nvl(src.amount,0)*curr.exchange_rate'
2292 ||','||CE_CASH_FCST.G_precision||'),
2293 src.amount';
2294 END IF;
2295
2296 from_clause := Get_From_Clause ('pa_ce_invoices_v');
2297 where_clause := Get_Where_Clause || '
2298 AND src.trx_date BETWEEN cab.start_date - '
2299 ||to_char(CE_CASH_FCST.G_lead_time)||
2300 ' and cab.end_date - '
2301 ||to_char(CE_CASH_FCST.G_lead_time)||
2302 Add_Where('PROJECT_ID') || Add_Where('CUSTOMER_PROFILE_CLASS_ID');
2303
2304 main_query := select_clause || from_clause || where_clause;
2305
2306 Execute_Main_Query (main_query);
2307
2308 cep_standard.debug('<<CE_CSH_FCST_POP.Build_PA_Invoices_Query');
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 cep_standard.debug('EXCEPTION:OTHERS-Build_PA_Invoice_Query');
2312 RAISE;
2313 END Build_PA_Invoice_Query;
2314
2315
2316 /* ---------------------------------------------------------------------
2317 | PUBLIC PROCEDURE |
2318 | Build_AR_Invoice_Query |
2319 | |
2320 | DESCRIPTION |
2321 | This procedure builds the query to calculate the forecast |
2322 | amounts for AR invoices on which payments are due to be |
2323 | received. Fully received invoices are exclude, but credit memos |
2324 | debit memos and adjustments are included. |
2325 | CALLED BY |
2326 | Populate_Cells |
2327 | REQUIRES |
2328 | main_query |
2329 | HISTORY |
2330 | 12-JUL-1996 Created Bidemi Carrol |
2331 --------------------------------------------------------------------- */
2332 PROCEDURE Build_AR_Invoice_Query IS
2333 from_clause VARCHAR2(500);
2334 where_clause varchar2(1500);
2335 select_clause varchar2(1500);
2336 main_query varchar2(3500) := null;
2337 BEGIN
2338 cep_standard.debug('>>Build_AR_Invoice_Query');
2339
2340 IF (CE_CASH_FCST.G_include_dispute_flag = 'N') THEN
2341 IF( CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
2342 select_clause := ' SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2343 '||CE_CASH_FCST.G_forecast_id||',
2344 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2345 '||CE_CASH_FCST.G_forecast_row_id||',
2346 ''Y'',
2347 nvl(fnd_global.user_id,-1),
2348 sysdate,
2349 nvl(fnd_global.user_id,-1),
2350 sysdate,
2351 nvl(fnd_global.user_id,-1),
2352 cab.forecast_column_id,
2353 src.reference_id,
2354 src.currency_code,
2355 to_number(hr_ou.ORGANIZATION_ID),
2356 src.trx_date +'
2357 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
2358 null,
2359 null,
2360 round((nvl(src.amount,0)-nvl(src.dispute_amount,0))*'
2361 ||CE_CASH_FCST.G_rp_exchange_rate
2362 ||','||CE_CASH_FCST.G_precision||'),
2363 src.amount-src.dispute_amount';
2364 ELSE
2365 select_clause := ' SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2366 '||CE_CASH_FCST.G_forecast_id||',
2367 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2368 '||CE_CASH_FCST.G_forecast_row_id||',
2369 ''Y'',
2370 nvl(fnd_global.user_id,-1),
2371 sysdate,
2372 nvl(fnd_global.user_id,-1),
2373 sysdate,
2374 nvl(fnd_global.user_id,-1),
2375 cab.forecast_column_id,
2376 src.reference_id,
2377 src.currency_code,
2378 to_number(hr_ou.ORGANIZATION_ID),
2379 src.trx_date +'
2380 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
2381 null,
2382 null,
2383 round((nvl(src.amount,0)-nvl(src.dispute_amount,0))*curr.exchange_rate'
2384 ||','||CE_CASH_FCST.G_precision||'),
2385 src.amount-src.dispute_amount';
2386 END IF;
2387 ELSE
2388 select_clause := Get_Select_Clause;
2389 END IF;
2390
2391 cep_standard.debug('Built Select Clause');
2392
2393 from_clause := Get_From_Clause ('ce_ar_fc_invoices_v');
2394 cep_standard.debug('Built From Clause');
2395
2396 IF (CE_CASH_FCST.G_use_average_payment_days = 'Y') THEN
2397 where_clause := Get_Where_Clause || ' AND
2398 nvl(src.invoice_date, src.trx_date) + decode(src.invoice_date, null, '
2399 || to_char(CE_CASH_FCST.G_lead_time)
2400 || ', nvl( CE_CSH_FCST_POP.Get_Average_Payment_Days (src.customer_id, src.site_use_id, '
2401 || ' src.currency_code, '
2402 || to_char(CE_CASH_FCST.G_apd_period)
2403 || '), (src.trx_date - src.invoice_date + '
2404 || to_char(CE_CASH_FCST.G_lead_time)
2405 || ') '
2406 || ' ) ) BETWEEN cab.start_date and cab.end_date '||
2407 Add_Where('CUSTOMER_PROFILE_CLASS_ID');
2408 ELSE
2409 where_clause := Get_Where_Clause || '
2410 AND src.trx_date BETWEEN cab.start_date - '
2411 ||to_char(CE_CASH_FCST.G_lead_time)||
2412 ' and cab.end_date - '
2413 ||to_char(CE_CASH_FCST.G_lead_time)||
2414 Add_Where('CUSTOMER_PROFILE_CLASS_ID');
2415 END IF;
2416
2417 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL) THEN
2418 where_clause := where_clause || '
2419 AND src.customer_trx_id IN (select ctl.customer_trx_id
2420 from ra_customer_trx_lines_all ctl,
2421 pa_projects_all pa
2422 where ctl.interface_line_attribute1 = pa.segment1
2423 and pa.project_id = ' || to_char(CE_CASH_FCST.G_rp_project_id) || ')';
2424
2425 END IF;
2426
2427 cep_standard.debug('Built Where Clause');
2428
2429 main_query := select_clause || from_clause || where_clause;
2430
2431 Execute_Main_Query (main_query);
2432
2433 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL) THEN
2434 Build_PA_Invoice_Query;
2435 END IF;
2436
2437 cep_standard.debug('<<CE_CSH_FCST_POP.Build_AR_Invoices_Query');
2438 EXCEPTION
2439 WHEN OTHERS THEN
2440 cep_standard.debug('EXCEPTION:OTHERS-Build_AR_Invoice_Query');
2441 RAISE;
2442 END Build_AR_Invoice_Query;
2443
2444
2445
2446 /* ---------------------------------------------------------------------
2447 | PUBLIC PROCEDURE |
2448 | Build_AR_Receipt_Query |
2449 | |
2450 | DESCRIPTION |
2451 | This procedure builds the query to calculate the forecast |
2452 | amounts for AP checks that have not cleared the bank. |
2453 | CALLED BY |
2454 | Populate_Cells |
2455 | REQUIRES |
2456 | main_query |
2457 | HISTORY |
2458 | 12-JUL-1996 Created Bidemi Carrol |
2459 | 17-Feb-2010 Bug 9252881 Changed the value for select_clause|
2460 | ,ccid.asset_code_combination_id value is replace with |
2461 | nvl(ccid.ar_asset_ccid, ccid.asset_code_combination_id)|
2462 --------------------------------------------------------------------- */
2463 PROCEDURE Build_AR_Receipt_Query IS
2464 from_clause VARCHAR2(500);
2465 where_clause varchar2(1500);
2466 select_clause varchar2(1500);
2467 trx_date_clause varchar2(100);
2468 view_name VARCHAR2(50);
2469 main_query varchar2(3500) := null;
2470 counter number;
2471 error_msg FND_NEW_MESSAGES.message_text%TYPE;
2472
2473 BEGIN
2474 cep_standard.debug('>>Build_AR_Receipt_Query');
2475
2476 from_clause := Get_From_Clause ('ce_ar_fc_receipts_v');
2477 cep_standard.debug('Built From Clause');
2478
2479 where_clause := Get_Where_Clause || Add_Where('BANK_ACCOUNT_ID') || Add_Where('RECEIPT_METHOD_ID');
2480
2481 IF (NVL(CE_CASH_FCST.G_forecast_method,'F') = 'P') THEN
2482 BEGIN
2483 Set_History;
2484
2485 EXCEPTION
2486 When NO_DATA_FOUND Then
2487 cep_standard.debug('row_id = ' || to_char(CE_CASH_FCST.G_forecast_row_id));
2488
2489 UPDATE ce_forecasts
2490 SET error_status = 'E'
2491 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
2492
2493 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
2494 error_msg := FND_MESSAGE.get;
2495 CE_FORECAST_ERRORS_PKG.insert_row(
2496 CE_CASH_FCST.G_forecast_id,
2497 CE_CASH_FCST.G_rp_forecast_header_id,
2498 CE_CASH_FCST.G_forecast_row_id,
2499 'CE_NO_HIST_START_PERIOD',
2500 error_msg);
2501 zero_fill_cells;
2502 cep_standard.debug('EXCEPTION: No history data found for ARR');
2503 return;
2504 When OTHERS Then
2505 cep_standard.debug('EXCEPTION: Build ARR query - Set History');
2506 raise;
2507 END;
2508 IF (CE_CASH_FCST.G_order_date_type = 'V') THEN
2509 where_clause := where_clause || '
2510 AND NVL(src.actual_value_date,src.trx_date) BETWEEN cab.start_date and cab.end_date
2511 AND src.status in (''CLEARED'',''RISK_ELIMINATED'') ';
2512 trx_date_clause := 'NVL(src.actual_value_date,src.trx_date)';
2513 ELSE
2514 where_clause := where_clause || '
2515 AND src.trx_date BETWEEN cab.start_date and cab.end_date
2516 AND src.status in (''CLEARED'',''RISK_ELIMINATED'') ';
2517 trx_date_clause := 'src.trx_date';
2518 END IF;
2519 ELSE -- if Forecast Method is Future
2520 where_clause := where_clause || '
2521 AND src.cash_activity_date BETWEEN cab.start_date - '
2522 ||to_char(CE_CASH_FCST.G_lead_time)||
2523 ' and cab.end_date - '
2524 ||to_char(CE_CASH_FCST.G_lead_time)||
2525 ' AND src.status not in (''CLEARED'',''RISK_ELIMINATED'') ';
2526 trx_date_clause := 'src.cash_activity_date';
2527 END IF;
2528
2529 cep_standard.debug('Built Where Clause');
2530
2531 IF(CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
2532 select_clause := 'SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2533 '||CE_CASH_FCST.G_forecast_id||',
2534 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2535 '||CE_CASH_FCST.G_forecast_row_id||',
2536 ''Y'',
2537 nvl(fnd_global.user_id,-1),
2538 sysdate,
2539 nvl(fnd_global.user_id,-1),
2540 sysdate,
2541 nvl(fnd_global.user_id,-1),
2542 cab.forecast_column_id,
2543 src.reference_id,
2544 src.currency_code,
2545 to_number(hr_ou.ORGANIZATION_ID),
2546 '||trx_date_clause||'+'
2547 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
2548 src.bank_account_id,
2549 nvl(ccid.ar_asset_ccid, ccid.asset_code_combination_id),
2550 round(nvl(src.amount,0)*'
2551 ||CE_CASH_FCST.G_rp_exchange_rate
2552 ||','
2553 ||CE_CASH_FCST.G_precision||'),
2554 src.amount';
2555 ELSE
2556 select_clause := 'SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2557 '||CE_CASH_FCST.G_forecast_id||',
2558 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2559 '||CE_CASH_FCST.G_forecast_row_id||',
2560 ''Y'',
2561 nvl(fnd_global.user_id,-1),
2562 sysdate,
2563 nvl(fnd_global.user_id,-1),
2564 sysdate,
2565 nvl(fnd_global.user_id,-1),
2566 cab.forecast_column_id,
2567 src.reference_id,
2568 src.currency_code,
2569 to_number(hr_ou.ORGANIZATION_ID),
2570 '||trx_date_clause||'+'
2571 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
2572 src.bank_account_id,
2573 nvl(ccid.ar_asset_ccid, ccid.asset_code_combination_id),
2574 round(nvl(src.amount,0)*curr.exchange_rate,'
2575 ||CE_CASH_FCST.G_precision||'),
2576 src.amount';
2577 END IF;
2578 cep_standard.debug('Built Select Clause');
2579
2580 main_query := select_clause || from_clause || where_clause;
2581
2582 commit;
2583
2584 Execute_Main_Query (main_query);
2585 cep_standard.debug('<<ce_csh_fcST_POP.Build_AR_Receipt_Query');
2586 EXCEPTION
2587 WHEN OTHERS THEN
2588 cep_standard.debug('EXCEPTION-OTHERS:Build_AR_Receipt_Query');
2589 RAISE;
2590 END Build_AR_Receipt_Query;
2591
2592
2593 /* ---------------------------------------------------------------------
2594 | PUBLIC PROCEDURE |
2595 | Get_GL_General_Query |
2596 | |
2597 | DESCRIPTION |
2598 | |
2599 | CALLED BY |
2600 | CE_CASH_FCST_POP.Build_GL_XXX_Query |
2601 | REQUIRES |
2602 | |
2603 | HISTORY |
2604 | 19-AUG-1996 Created Bidemi Carrol |
2605 --------------------------------------------------------------------- */
2606 FUNCTION Get_GL_General_Query RETURN VARCHAR2 IS
2607 main_query varchar2(2000) := null;
2608 BEGIN
2609 cep_standard.debug('>>Get_GL_General_Query');
2610 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
2611 main_query := ' SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2612 '||CE_CASH_FCST.G_forecast_id||',
2613 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2614 '||CE_CASH_FCST.G_forecast_row_id||',
2615 ''Y'',
2616 nvl(fnd_global.user_id,-1),
2617 sysdate,
2618 nvl(fnd_global.user_id,-1),
2619 sysdate,
2620 nvl(fnd_global.user_id,-1),
2621 cab.forecast_column_id,
2622 null,
2623 src.currency_code,
2624 null,
2625 gp.start_date,
2626 null,
2627 null,
2628 round((nvl(src.period_net_dr,0)-nvl(src.period_net_cr,0))*'||CE_CASH_FCST.G_rp_exchange_rate
2629 ||','||CE_CASH_FCST.G_precision||'),
2630 (nvl(src.period_net_dr,0)-nvl(src.period_net_cr,0))'; --bug4495616
2631 ELSE
2632 main_query := ' SELECT CE_FORECAST_TRX_CELLS_S.nextval,
2633 '||CE_CASH_FCST.G_forecast_id||',
2634 '||CE_CASH_FCST.G_rp_forecast_header_id||',
2635 '||CE_CASH_FCST.G_forecast_row_id||',
2636 ''Y'',
2637 nvl(fnd_global.user_id,-1),
2638 sysdate,
2639 nvl(fnd_global.user_id,-1),
2640 sysdate,
2641 nvl(fnd_global.user_id,-1),
2642 cab.forecast_column_id,
2643 null,
2644 src.currency_code,
2645 null,
2646 gp.start_date,
2647 null,
2648 null,
2649 round((nvl(src.period_net_dr,0)-nvl(src.period_net_cr,0))*curr.exchange_rate '
2650 ||','||CE_CASH_FCST.G_precision||'),
2651 (nvl(src.period_net_dr,0)-nvl(src.period_net_cr,0))'; --bug4495616
2652 END IF;
2653
2654 main_query := main_query || '
2655 FROM gl_balances src,
2656 gl_periods gp,
2657 ce_forecast_ext_temp cab ';
2658
2659 IF( CE_CASH_FCST.G_rp_exchange_type <> 'User' OR
2660 CE_CASH_FCST.G_rp_exchange_type IS NULL)THEN
2661 main_query := main_query || ',
2662 ce_currency_rates_temp curr ';
2663 END IF;
2664
2665 main_query := main_query || '
2666 WHERE gp.start_date(+) = cab.start_date
2667 AND gp.period_name = src.period_name
2668 AND gp.period_type = src.period_type
2669 AND gp.period_year = src.period_year
2670 AND gp.period_set_name = '''||CE_CASH_FCST.G_rp_calendar_name||'''
2671 AND src.ledger_id = '||CE_CASH_FCST.G_set_of_books_id||'
2672 AND src.code_combination_id = '||CE_CASH_FCST.G_code_combination_id ||
2673 Add_Where('EXCHANGE_TYPE');
2674
2675 IF(CE_CASH_FCST.G_rp_src_curr_type = 'E')THEN
2676 main_query := main_query || '
2677 AND src.currency_code = '''||CE_CASH_FCST.G_rp_src_currency||''' ';
2678 ELSIF(CE_CASH_FCST.G_rp_src_curr_type = 'F')THEN
2679 main_query := main_query || '
2680 AND src.currency_code = DECODE('''||CE_CASH_FCST.G_sob_currency_code||''', '''||CE_CASH_FCST.G_rp_src_currency||''',src.currency_code, ''-1'')';
2681 END IF;
2682
2683 return (main_query);
2684 EXCEPTION
2685 WHEN OTHERS THEN
2686 cep_standard.debug('EXCEPTION-OTHERS: Get_GL_General_Query');
2687 RAISE;
2688 END Get_GL_General_Query;
2689
2690
2691
2692 /* ---------------------------------------------------------------------
2693 | PUBLIC PROCEDURE |
2694 | Build_GL_Budget_Query |
2695 | |
2696 | DESCRIPTION |
2697 | |
2698 | CALLED BY |
2699 | CE_CASH_FCST_POP.Cash_Forecast |
2700 | REQUIRES |
2701 | |
2702 | HISTORY |
2703 | 19-AUG-1996 Created Bidemi Carrol |
2704 --------------------------------------------------------------------- */
2705 PROCEDURE Build_GL_Budget_Query IS
2706 main_query varchar2(2000) := null;
2707
2708 BEGIN
2709 cep_standard.debug('>>Build_GL_Budget_Query');
2710 main_query := Get_GL_General_Query || '
2711 AND src.budget_version_id = '||CE_CASH_FCST.G_budget_version_id||'
2712 AND src.actual_flag = ''B'' ';
2713
2714 Execute_Main_Query (main_query);
2715
2716 cep_standard.debug('<<Build_GL_Budget_Query');
2717
2718 EXCEPTION
2719 WHEN OTHERS THEN
2720 cep_standard.debug('EXCEPTION-OTHERS: Build_GL_Budget_Query');
2721 RAISE;
2722 END Build_GL_Budget_Query;
2723
2724
2725 /* ---------------------------------------------------------------------
2726 | PUBLIC PROCEDURE |
2727 | Build_GL_Encumb_Query |
2728 | DESCRIPTION |
2729 | Calculates the GL encumbrances over specified periods, for a |
2730 | given code_combination_id |
2731 | CALLED BY |
2732 | CE_CASH_FCST_POP.Cash_Forecast |
2733 | REQUIRES |
2734 | |
2735 | HISTORY |
2736 | 19-AUG-1996 Created Bidemi Carrol |
2737 --------------------------------------------------------------------- */
2738 PROCEDURE Build_GL_Encumb_Query IS
2739 main_query varchar2(2000) := null;
2740 BEGIN
2741 cep_standard.debug('>>Build_GL_Encumb_Query');
2742 main_query := Get_GL_General_Query || '
2743 AND src.encumbrance_type_id = '||CE_CASH_FCST.G_encumbrance_type_id||'
2744 AND src.actual_flag = ''E'' ';
2745
2746 Execute_Main_Query (main_query);
2747
2748 cep_standard.debug('<<Build_GL_Encumbrance_Query');
2749
2750 EXCEPTION
2751 WHEN OTHERS THEN
2752 cep_standard.debug('EXCEPTION-OTHERS: Build_GL_Encumbrance_Query');
2753 RAISE;
2754 END Build_GL_Encumb_Query;
2755
2756
2757 /* ---------------------------------------------------------------------
2758 | PUBLIC PROCEDURE |
2759 | Build_GL_Actuals_Query |
2760 | |
2761 | DESCRIPTION |
2762 | |
2763 | CALLED BY |
2764 | CE_CASH_FCST_POP.Cash_Forecast |
2765 | REQUIRES |
2766 | |
2767 | HISTORY |
2768 | 19-AUG-1996 Created Bidemi Carrol |
2769 --------------------------------------------------------------------- */
2770 PROCEDURE Build_GL_Actuals_Query IS
2771 main_query varchar2(2000) := null;
2772
2773 BEGIN
2774 cep_standard.debug('>>Build_GL_Actuals_Query');
2775 main_query := Get_GL_General_Query || '
2776 AND src.actual_flag = ''A''
2777 AND src.template_id is null ';
2778
2779 Execute_Main_Query (main_query);
2780
2781 cep_standard.debug('<<Build_GL_Actuals_Query');
2782
2783 EXCEPTION
2784 WHEN OTHERS THEN
2785 cep_standard.debug('EXCEPTION-OTHERS: Build_GL_Actuals_Query');
2786 RAISE;
2787 END Build_GL_Actuals_Query;
2788
2789
2790 /* ---------------------------------------------------------------------
2791 | PUBLIC PROCEDURE |
2792 | Build_Pay_Exp_Query |
2793 | |
2794 | DESCRIPTION |
2795 | Payroll amounts paid out. |
2796 | CALLED BY |
2797 | Populate_Cells |
2798 | REQUIRES |
2799 | main_query |
2800 | HISTORY |
2801 | 1-JUL-1997 Created Wynne Chan |
2802 --------------------------------------------------------------------- */
2803 PROCEDURE Build_Pay_Exp_Query IS
2804 from_clause VARCHAR2(500);
2805 where_clause varchar2(1500);
2806 select_clause varchar2(1500);
2807 main_query varchar2(3500) := null;
2808 error_msg FND_NEW_MESSAGES.message_text%TYPE;
2809
2810 BEGIN
2811 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_PAY_Exp_Query');
2812 select_clause := Get_Select_Clause;
2813 cep_standard.debug('Built Select Clause');
2814
2815 from_clause := Get_From_Clause('ce_pay_fc_payroll_v');
2816 cep_standard.debug('Built From Clause');
2817
2818 where_clause := Get_Where_Clause || '
2819 AND src.effective_date BETWEEN cab.start_date and cab.end_date ' ||
2820 Add_Where('ORG_PAYMENT_METHOD_ID') || Add_Where('BANK_ACCOUNT_ID') || Add_Where('PAYROLL_ID');
2821
2822 IF(CE_CASH_FCST.G_rp_src_curr_type = 'F')THEN
2823 IF(CE_CASH_FCST.G_set_of_books_id IS NULL)THEN
2824 where_clause := where_clause || '
2825 AND org.set_of_books_id IS NULL ';
2826 ELSE
2827 where_clause := where_clause || '
2828 AND org.set_of_books_id = '||to_char(CE_CASH_FCST.G_set_of_books_id);
2829 END IF;
2830 END IF;
2831
2832
2833 BEGIN
2834 Set_History;
2835
2836 EXCEPTION
2837 When NO_DATA_FOUND Then
2838 cep_standard.debug('row_id = ' || to_char(CE_CASH_FCST.G_forecast_row_id));
2839
2840 UPDATE ce_forecasts
2841 SET error_status = 'E'
2842 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
2843
2844 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
2845 error_msg := FND_MESSAGE.get;
2846 CE_FORECAST_ERRORS_PKG.insert_row(
2847 CE_CASH_FCST.G_forecast_id,
2848 CE_CASH_FCST.G_rp_forecast_header_id,
2849 CE_CASH_FCST.G_forecast_row_id,
2850 'CE_NO_HIST_START_PERIOD',
2851 error_msg);
2852 zero_fill_cells;
2853 cep_standard.debug('EXCEPTION: No Payroll historical data found');
2854 return;
2855 When OTHERS Then
2856 cep_standard.debug('EXCEPTION: Build Payroll query - Set History');
2857 raise;
2858 END;
2859
2860 main_query := select_clause || from_clause || where_clause;
2861
2862 Execute_Main_Query (main_query);
2863
2864 cep_standard.debug('<<CE_CSH_FCST_POP.Build_PAY_Exp_Query');
2865
2866 END Build_Pay_Exp_Query;
2867
2868
2869 /* ---------------------------------------------------------------------
2870 | PUBLIC PROCEDURE |
2871 | Build_PO_Orders_Query |
2872 | |
2873 | DESCRIPTION |
2874 | Purchase orders that have not been fully invoiced or cancelled |
2875 | CALLED BY |
2876 | Populate_Cells |
2877 | REQUIRES |
2878 | main_query |
2879 | HISTORY |
2880 | 12-JUL-1996 Created Bidemi Carrol |
2881 --------------------------------------------------------------------- */
2882 PROCEDURE Build_PO_Orders_Query IS
2883 from_clause_1 VARCHAR2(500); -- Payment terms checkbox not checked
2884 from_clause_2 VARCHAR2(500); -- Checkbox checked and terms available
2885 from_clause_3 VARCHAR2(500); -- Checkbox checked and terms are null
2886 where_clause varchar2(1500);
2887 where_clause_1 varchar2(1500);
2888 where_clause_2 varchar2(1500);
2889 where_clause_3 varchar2(1500);
2890 select_clause_1 varchar2(2000);
2891 select_clause_2 varchar2(2000);
2892 select_clause_3 varchar2(2000);
2893 main_query_1 varchar2(3500) := null;
2894 main_query_2 varchar2(3500) := null;
2895 main_query_3 varchar2(3500) := null;
2896
2897 l_amount NUMBER;
2898 l_org_id NUMBER;
2899 l_legal_entity_id NUMBER;
2900 l_dummy NUMBER;
2901 l_rate NUMBER;
2902 remain_amount NUMBER;
2903
2904 l_start_date DATE;
2905 l_end_date DATE;
2906 l_max_end_date DATE;
2907 error_flag BOOLEAN := FALSE;
2908 error_msg FND_NEW_MESSAGES.message_text%TYPE;
2909
2910
2911 CURSOR C_period IS
2912 SELECT start_date,
2913 end_date,
2914 forecast_column_id
2915 FROM ce_forecast_ext_temp
2916 WHERE context_value = 'A'
2917 AND forecast_request_id = CE_CASH_FCST.G_forecast_id
2918 AND conversion_rate = CE_CASH_FCST.G_forecast_row_id;
2919
2920 CURSOR C_sob(p_org_id NUMBER) IS
2921 SELECT 1
2922 FROM CE_FORECAST_ORGS_V
2923 WHERE set_of_books_id = CE_CASH_FCST.G_set_of_books_id
2924 AND org_id = p_org_id;
2925
2926 CURSOR C_rate(p_currency_code VARCHAR2) IS
2927 SELECT exchange_rate
2928 FROM ce_currency_rates_temp
2929 WHERE forecast_request_id = CE_CASH_FCST.G_forecast_id
2930 AND to_currency = CE_CASH_FCST.G_rp_forecast_currency
2931 AND currency_code = p_currency_code;
2932
2933 -- CE_CASH_FCST.G_rp_project_id IS NULL
2934 -- CE_CASH_FCST.G_use_payment_terms = 'Y'
2935 CURSOR C_orders_terms(p_start_date DATE, p_end_date DATE) IS
2936 (SELECT reference_id,
2937 currency_code,
2938 org_id,
2939 status,
2940 payment_priority,
2941 paygroup,
2942 vendor_type,
2943 amount,
2944 due_amount,
2945 term_id,
2946 decode(end_date,null,(trunc(l_max_end_date)-trunc(start_date)+1),
2947 (trunc(end_date)-trunc(start_date)+1)) total_dates,
2948 decode(end_date, null, (nvl(decode(src.due_amount, 0,
2949 0,
2950 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))/(trunc(l_max_end_date)-trunc(start_date)+1),
2951 (nvl(decode(src.due_amount, 0,
2952 0,
2953 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))/(trunc(end_date)-trunc(start_date)+1)) per_day_amount,
2954 start_date,
2955 nvl(end_date, l_max_end_date)end_date,
2956 end_date trx_end_date
2957 FROM ce_po_fc_orders_terms_temp_v src
2958 WHERE start_date <= p_end_date
2959 AND (end_date >= p_start_date
2960 OR end_date is NULL)
2961 UNION ALL
2962 SELECT reference_id,
2963 currency_code,
2964 org_id,
2965 status,
2966 payment_priority,
2967 paygroup,
2968 vendor_type,
2969 null,
2970 null,
2971 null,
2972 null,
2973 decode(end_date, null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
2974 (nvl(amount,0)/(trunc(end_date)-trunc(start_date)+1))) per_day_amount,
2975 start_date,
2976 nvl(end_date, l_max_end_date)end_date,
2977 end_date trx_end_date
2978 FROM ce_po_fc_no_terms_temp_v
2979 WHERE start_date <= p_end_date
2980 AND (end_date >= p_start_date
2981 OR end_date is NULL));
2982
2983 -- CE_CASH_FCST.G_rp_project_id IS NULL
2984 -- CE_CASH_FCST.G_use_payment_terms = 'N'
2985 CURSOR C_orders(p_start_date DATE, p_end_date DATE) IS
2986 SELECT reference_id,
2987 currency_code,
2988 org_id,
2989 status,
2990 payment_priority,
2991 paygroup,
2992 vendor_type,
2993 decode(end_date, null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
2994 (nvl(amount,0)/(trunc(end_date)-trunc(start_date)+1))) per_day_amount,
2995 start_date,
2996 nvl(end_date, l_max_end_date)end_date,
2997 end_date trx_end_date
2998 FROM ce_po_fc_orders_temp_v
2999 WHERE start_date <= p_end_date
3000 AND (end_date >= p_start_date
3001 OR end_date is NULL);
3002
3003 -- CE_CASH_FCST.G_rp_project_id IS NOT NULL
3004 -- CE_CASH_FCST.G_use_payment_terms = 'Y'
3005 CURSOR C_orders_terms_proj(p_start_date DATE, p_end_date DATE) IS
3006 (SELECT reference_id,
3007 currency_code,
3008 org_id,
3009 status,
3010 payment_priority,
3011 paygroup,
3012 vendor_type,
3013 amount,
3014 due_amount,
3015 term_id,
3016 decode(end_date,null,(trunc(l_max_end_date)-trunc(start_date)+1),
3017 (trunc(end_date)-trunc(start_date)+1)) total_dates,
3018 decode(end_date, null, (nvl(decode(src.due_amount, 0,
3019 0,
3020 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))/(trunc(l_max_end_date)-trunc(start_date)+1),
3021 (nvl(decode(src.due_amount, 0,
3022 0,
3023 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))/(trunc(end_date)-trunc(start_date)+1)) per_day_amount,
3024 start_date,
3025 nvl(end_date, l_max_end_date)end_date,
3026 end_date trx_end_date
3027 FROM ce_po_fc_orders_terms_temp_v src
3028 WHERE start_date <= p_end_date
3029 AND (end_date >= p_start_date
3030 OR end_date is NULL)
3031 AND project_id = CE_CASH_FCST.G_rp_project_id
3032 UNION ALL
3033 SELECT reference_id,
3034 currency_code,
3035 org_id,
3036 status,
3037 payment_priority,
3038 paygroup,
3039 vendor_type,
3040 null,
3041 null,
3042 null,
3043 null,
3044 decode(end_date, null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
3045 (nvl(amount,0)/(trunc(end_date)-trunc(start_date)+1))) per_day_amount,
3046 start_date,
3047 nvl(end_date, l_max_end_date)end_date,
3048 end_date trx_end_date
3049 FROM ce_po_fc_no_terms_temp_v
3050 WHERE start_date <= p_end_date
3051 AND (end_date >= p_start_date
3052 OR end_date is NULL)
3053 AND project_id = CE_CASH_FCST.G_rp_project_id);
3054
3055
3056 -- CE_CASH_FCST.G_rp_project_id IS NOT NULL
3057 -- CE_CASH_FCST.G_use_payment_terms = 'Y'
3058 CURSOR C_orders_proj(p_start_date DATE, p_end_date DATE) IS
3059 SELECT reference_id,
3060 currency_code,
3061 org_id,
3062 status,
3063 payment_priority,
3064 paygroup,
3065 vendor_type,
3066 decode(end_date, null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
3067 (nvl(amount,0)/(trunc(end_date)-trunc(start_date)+1))) per_day_amount,
3068 start_date,
3069 nvl(end_date, l_max_end_date)end_date,
3070 end_date trx_end_date
3071 FROM ce_po_fc_orders_temp_v
3072 WHERE start_date <= p_end_date
3073 AND (end_date >= p_start_date
3074 OR end_date is NULL)
3075 AND project_id = CE_CASH_FCST.G_rp_project_id;
3076
3077 BEGIN
3078
3079 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_PO_Orders_Query');
3080
3081 where_clause := Get_Where_Clause||
3082 Add_Where('AUTHORIZATION_STATUS') || Add_Where('PAYMENT_PRIORITY') ||
3083 Add_Where('PAY_GROUP') || Add_Where('VENDOR_TYPE') || Add_Where('PROJECT_ID');
3084
3085 IF (CE_CASH_FCST.G_use_payment_terms = 'Y') THEN
3086 IF (CE_CASH_FCST.G_rp_exchange_type = 'User') THEN
3087 -- 5609517: Remove ORDERED hint as suggested by apps perf team
3088 -- select_clause_2 := 'SELECT /*+ ORDERED USE_MERGE(src)*/ CE_FORECAST_TRX_CELLS_S.nextval,
3089 select_clause_2 := 'SELECT CE_FORECAST_TRX_CELLS_S.nextval,
3090 '||CE_CASH_FCST.G_forecast_id||',
3091 '||CE_CASH_FCST.G_rp_forecast_header_id||',
3092 '||CE_CASH_FCST.G_forecast_row_id||',
3093 ''Y'',
3094 nvl(fnd_global.user_id,-1),
3095 sysdate,
3096 nvl(fnd_global.user_id,-1),
3097 sysdate,
3098 nvl(fnd_global.user_id,-1),
3099 cab.forecast_column_id,
3100 src.reference_id,
3101 src.currency_code,
3102 to_number(hr_ou.ORGANIZATION_ID),
3103 decode(src.fixed_due_date, null,
3104 decode(src.due_days, null,
3105 decode(src.due_months_forward, null, src.trx_date,
3106 (TRUNC(ADD_MONTHS(src.trx_date, src.due_months_forward),
3107 ''MONTH'')
3108 + src.due_day_of_month - 1)),
3109 src.trx_date + src.due_days),
3110 src.fixed_due_date) + '
3111 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
3112 null,
3113 null,
3114 round(-(nvl(decode(src.due_amount, 0,
3115 (select nvl(src.amount,0) - sum(t.due_amount) from ap_terms_lines t where t.term_id = src.term_id),
3116 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100)))) * ' || CE_CASH_FCST.G_rp_exchange_rate
3117 ||','||CE_CASH_FCST.G_precision||'),
3118 -(nvl(decode(src.due_amount, 0,
3119 (select nvl(src.amount,0) - sum(t.due_amount) from ap_terms_lines t where t.term_id = src.term_id),
3120 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))';
3121 ELSE
3122 -- 5609517: Remove ORDERED hint as suggested by apps perf team
3123 -- select_clause_2 := 'SELECT /*+ ORDERED USE_MERGE(src)*/ CE_FORECAST_TRX_CELLS_S.nextval,
3124 select_clause_2 := 'SELECT CE_FORECAST_TRX_CELLS_S.nextval,
3125 '||CE_CASH_FCST.G_forecast_id||',
3126 '||CE_CASH_FCST.G_rp_forecast_header_id||',
3127 '||CE_CASH_FCST.G_forecast_row_id||',
3128 ''Y'',
3129 nvl(fnd_global.user_id,-1),
3130 sysdate,
3131 nvl(fnd_global.user_id,-1),
3132 sysdate,
3133 nvl(fnd_global.user_id,-1),
3134 cab.forecast_column_id,
3135 src.reference_id,
3136 src.currency_code,
3137 to_number(hr_ou.ORGANIZATION_ID),
3138 decode(src.fixed_due_date, null,
3139 decode(src.due_days, null,
3140 decode(src.due_months_forward, null, src.trx_date,
3141 (TRUNC(ADD_MONTHS(src.trx_date, src.due_months_forward),
3142 ''MONTH'')
3143 + src.due_day_of_month - 1)),
3144 src.trx_date + src.due_days),
3145 src.fixed_due_date) + '
3146 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
3147 null,
3148 null,
3149 round(-(nvl(decode(src.due_amount, 0,
3150 (select nvl(src.amount,0) - sum(t.due_amount) from ap_terms_lines t where t.term_id = src.term_id),
3151 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))) * curr.exchange_rate)'
3152 ||','||CE_CASH_FCST.G_precision||'),
3153 -(nvl(decode(src.due_amount, 0,
3154 (select nvl(src.amount,0) - sum(t.due_amount) from ap_terms_lines t where t.term_id = src.term_id),
3155 src.due_amount),(nvl(src.amount,0) * (nvl(src.due_percent,100)/100))))';
3156 END IF;
3157
3158 select_clause_3 := Get_Select_Clause;
3159
3160 from_clause_2 := Get_From_Clause('ce_po_fc_orders_terms_v');
3161 from_clause_3 := Get_From_Clause('ce_po_fc_orders_no_terms_v');
3162
3163 where_clause_2 := where_clause || ' AND
3164 decode(src.fixed_due_date, null,
3165 decode(src.due_days, null,
3166 decode(src.due_months_forward, null, src.trx_date,
3167 (TRUNC(ADD_MONTHS(src.trx_date, src.due_months_forward), ''MONTH'')
3168 + src.due_day_of_month - 1)),
3169 src.trx_date + src.due_days),
3170 src.fixed_due_date) BETWEEN cab.start_date - '
3171 ||to_char(CE_CASH_FCST.G_lead_time)||
3172 ' and cab.end_date - '
3173 ||to_char(CE_CASH_FCST.G_lead_time)||
3174 ' AND nvl(src.amount,0) <> 0';
3175
3176 where_clause_3 := where_clause || '
3177 AND src.trx_date BETWEEN cab.start_date - '
3178 ||to_char(CE_CASH_FCST.G_lead_time)||
3179 ' and cab.end_date - '
3180 ||to_char(CE_CASH_FCST.G_lead_time);
3181
3182 main_query_2 := select_clause_2 || from_clause_2 || where_clause_2;
3183 main_query_3 := select_clause_3 || from_clause_3 || where_clause_3;
3184
3185 Execute_Main_Query (main_query_2);
3186 Execute_Main_Query (main_query_3);
3187 ELSE -- G_use_payment_terms = 'N'
3188 select_clause_1 := Get_Select_Clause;
3189
3190 from_clause_1 := Get_From_Clause('ce_po_fc_orders_v');
3191
3192 where_clause_1 := where_clause || '
3193 AND src.trx_date BETWEEN cab.start_date - '
3194 ||to_char(CE_CASH_FCST.G_lead_time)||
3195 ' and cab.end_date - '
3196 ||to_char(CE_CASH_FCST.G_lead_time);
3197
3198 main_query_1 := select_clause_1 || from_clause_1 || where_clause_1;
3199
3200 Execute_Main_Query (main_query_1);
3201 END IF;
3202
3203 IF (CE_CASH_FCST.G_include_temp_labor_flag = 'Y') THEN
3204
3205 populate_aging_buckets;
3206
3207 SELECT max(end_date)
3208 INTO l_max_end_date
3209 FROM ce_forecast_ext_temp
3210 WHERE context_value = 'A'
3211 AND forecast_request_id = CE_CASH_FCST.G_forecast_id
3212 AND conversion_rate = CE_CASH_FCST.G_forecast_row_id;
3213 cep_standard.debug('l_max_end_date = ' || to_char(l_max_end_date, 'DD-MON-YYYY'));
3214
3215 IF (CE_CASH_FCST.G_rp_project_id IS NULL) THEN
3216
3217 IF (CE_CASH_FCST.G_use_payment_terms = 'Y') THEN
3218
3219 cep_standard.debug('PROJECT_ID IS NULL');
3220 cep_standard.debug('USE_PAYMENT_TERMS IS Y');
3221
3222 FOR C_rec IN C_period LOOP
3223 FOR C_req_rec in C_orders_terms(C_rec.start_date, C_rec.end_date) LOOP
3224 IF (C_req_rec.start_date < C_rec.start_date) THEN
3225 IF (C_req_rec.end_date < C_rec.end_date) THEN
3226 l_start_date := trunc(C_rec.start_date);
3227 l_end_date := trunc(C_req_rec.end_date);
3228 ELSE
3229 l_start_date := trunc(C_rec.start_date);
3230 l_end_date := trunc(C_rec.end_date);
3231 END IF;
3232 ELSE
3233 IF (C_req_rec.end_date > C_rec.end_date) THEN
3234 l_start_date := trunc(C_req_rec.start_date);
3235 l_end_date := trunc(C_rec.end_date);
3236 ELSE
3237 l_start_date := trunc(C_req_rec.start_date);
3238 l_end_date := trunc(C_req_rec.end_date);
3239 END IF;
3240 END IF;
3241 cep_standard.debug('l_start_date = ' || to_char(l_start_date, 'DD-MON-YYYY'));
3242 cep_standard.debug('l_end_date = ' || to_char(l_end_date, 'DD-MON-YYYY'));
3243
3244 /* In the case where we use terms and due amount is 0,
3245 we want to sum the term amounts and subtract them from the original
3246 source amount. Since this is too complex to perform within the cursor
3247 we do it within the FOR loop */
3248
3249 if C_req_rec.due_amount = 0 THEN
3250 select nvl(C_req_rec.amount,0) - sum(t.due_amount)
3251 INTO remain_amount
3252 from ap_terms_lines t
3253 where t.term_id = C_req_rec.term_id;
3254
3255 cep_standard.debug('remain_amount = ' || to_char(remain_amount));
3256
3257 l_amount := remain_amount/C_req_rec.total_dates * (l_end_date - l_start_date + 1);
3258
3259 end if;
3260
3261 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
3262
3263
3264 cep_standard.debug('per_day_amount = ' || to_char(C_req_rec.per_day_amount));
3265 cep_standard.debug('l_amount = ' || to_char(l_amount));
3266
3267 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
3268 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
3269 l_amount := 0;
3270 END IF;
3271 cep_standard.debug('currency_code');
3272
3273
3274 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
3275 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
3276 l_amount := 0;
3277 END IF;
3278 cep_standard.debug('authorization_status');
3279
3280
3281 IF(CE_CASH_FCST.G_payment_priority IS NOT NULL AND
3282 C_req_rec.payment_priority <> CE_CASH_FCST.G_payment_priority ) THEN
3283 l_amount := 0;
3284 END IF;
3285 cep_standard.debug('payment_priority ');
3286
3287
3288 IF(CE_CASH_FCST.G_pay_group IS NOT NULL AND
3289 C_req_rec.paygroup <> CE_CASH_FCST.G_pay_group) THEN
3290 l_amount := 0;
3291 END IF;
3292 cep_standard.debug('paygroup');
3293
3294
3295 IF(CE_CASH_FCST.G_vendor_type IS NOT NULL AND
3296 C_req_rec.vendor_type <> CE_CASH_FCST.G_vendor_type) THEN
3297 l_amount := 0;
3298 END IF;
3299 cep_standard.debug('vendor_type');
3300
3301 SELECT to_number(ORGANIZATION_ID)
3302 INTO l_legal_entity_id
3303 FROM hr_operating_units
3304 WHERE organization_id = C_req_rec.org_id;
3305 cep_standard.debug('legal_entity_id');
3306
3307
3308 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
3309 (nvl(C_req_rec.org_id,CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
3310 l_amount := 0;
3311 END IF;
3312 cep_standard.debug('org_id');
3313
3314
3315 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
3316 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
3317 OPEN C_sob(C_req_rec.org_id);
3318 FETCH C_sob INTO l_dummy;
3319 IF C_sob%NOTFOUND THEN
3320 CLOSE C_sob;
3321 l_amount := 0;
3322 END IF;
3323 CLOSE C_sob;
3324 END IF;
3325 cep_standard.debug('set_of_books_id');
3326
3327 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
3328 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
3329 OPEN C_rate(C_req_rec.currency_code);
3330 FETCH C_rate INTO l_rate;
3331 IF C_rate%NOTFOUND THEN
3332 l_rate := 1;
3333 END IF;
3334 CLOSE C_rate;
3335 ELSIF (CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
3336 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
3337 ELSE
3338 l_rate := 1;
3339 END IF;
3340 cep_standard.debug('exchange_rate');
3341
3342
3343 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
3344 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
3345 l_amount := 0;
3346 END IF;
3347 END IF;
3348 cep_standard.debug('amount_threshold');
3349
3350 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
3351 CE_CASH_FCST.G_invalid_overdue_row)THEN
3352 l_amount := 0;
3353 END IF;
3354 cep_standard.debug('OVERDUE_COLUMN');
3355
3356 IF (l_amount <> 0) THEN
3357 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
3358
3359 UPDATE ce_forecasts
3360 SET error_status = 'X'
3361 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
3362
3363 FND_MESSAGE.set_name('CE', 'CE_FC_POP_NO_END_DATE');
3364 error_msg := FND_MESSAGE.get;
3365 CE_FORECAST_ERRORS_PKG.insert_row(
3366 CE_CASH_FCST.G_forecast_id,
3367 CE_CASH_FCST.G_rp_forecast_header_id,
3368 CE_CASH_FCST.G_forecast_row_id,
3369 'CE_FC_POP_NO_END_DATE',
3370 error_msg);
3371 error_flag := TRUE;
3372 END IF;
3373 cep_standard.debug('error_message');
3374
3375 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
3376 END IF;
3377 END LOOP;
3378 END LOOP;
3379
3380 clear_aging_buckets;
3381 zero_fill_cells;
3382
3383 ELSE
3384 cep_standard.debug('PROJECT_ID IS NULL');
3385 cep_standard.debug('USE_PAYMENT_TERMS IS N');
3386
3387 FOR C_rec IN C_period LOOP
3388 FOR C_req_rec in C_orders(C_rec.start_date, C_rec.end_date) LOOP
3389 IF (C_req_rec.start_date < C_rec.start_date) THEN
3390 IF (C_req_rec.end_date < C_rec.end_date) THEN
3391 l_start_date := trunc(C_rec.start_date);
3392 l_end_date := trunc(C_req_rec.end_date);
3393 ELSE
3394 l_start_date := trunc(C_rec.start_date);
3395 l_end_date := trunc(C_rec.end_date);
3396 END IF;
3397 ELSE
3398 IF (C_req_rec.end_date > C_rec.end_date) THEN
3399 l_start_date := trunc(C_req_rec.start_date);
3400 l_end_date := trunc(C_rec.end_date);
3401 ELSE
3402 l_start_date := trunc(C_req_rec.start_date);
3403 l_end_date := trunc(C_req_rec.end_date);
3404 END IF;
3405 END IF;
3406 cep_standard.debug('l_start_date = ' || to_char(l_start_date, 'DD-MON-YYYY'));
3407 cep_standard.debug('l_end_date = ' || to_char(l_end_date, 'DD-MON-YYYY'));
3408
3409 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
3410
3411 cep_standard.debug('per_day_amount = ' || to_char(C_req_rec.per_day_amount));
3412 cep_standard.debug('l_amount = ' || to_char(l_amount));
3413
3414 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
3415 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
3416 l_amount := 0;
3417 END IF;
3418 cep_standard.debug('currency_code');
3419
3420
3421 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
3422 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
3423 l_amount := 0;
3424 END IF;
3425 cep_standard.debug('authorization_status');
3426
3427
3428 IF(CE_CASH_FCST.G_payment_priority IS NOT NULL AND
3429 C_req_rec.payment_priority <> CE_CASH_FCST.G_payment_priority ) THEN
3430 l_amount := 0;
3431 END IF;
3432 cep_standard.debug('payment_priority ');
3433
3434
3435 IF(CE_CASH_FCST.G_pay_group IS NOT NULL AND
3436 C_req_rec.paygroup <> CE_CASH_FCST.G_pay_group) THEN
3437 l_amount := 0;
3438 END IF;
3439 cep_standard.debug('paygroup');
3440
3441
3442 IF(CE_CASH_FCST.G_vendor_type IS NOT NULL AND
3443 C_req_rec.vendor_type <> CE_CASH_FCST.G_vendor_type) THEN
3444 l_amount := 0;
3445 END IF;
3446 cep_standard.debug('vendor_type');
3447
3448 SELECT to_number(ORGANIZATION_ID)
3449 INTO l_legal_entity_id
3450 FROM hr_operating_units
3451 WHERE organization_id = C_req_rec.org_id;
3452 cep_standard.debug('legal_entity_id');
3453
3454
3455 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
3456 (nvl(C_req_rec.org_id,CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
3457 l_amount := 0;
3458 END IF;
3459 cep_standard.debug('org_id');
3460
3461
3462 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
3463 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
3464 OPEN C_sob(C_req_rec.org_id);
3465 FETCH C_sob INTO l_dummy;
3466 IF C_sob%NOTFOUND THEN
3467 CLOSE C_sob;
3468 l_amount := 0;
3469 END IF;
3470 CLOSE C_sob;
3471 END IF;
3472 cep_standard.debug('set_of_books_id');
3473
3474 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
3475 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
3476 OPEN C_rate(C_req_rec.currency_code);
3477 FETCH C_rate INTO l_rate;
3478 IF C_rate%NOTFOUND THEN
3479 l_rate := 1;
3480 END IF;
3481 CLOSE C_rate;
3482 ELSIF (CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
3483 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
3484 ELSE
3485 l_rate := 1;
3486 END IF;
3487 cep_standard.debug('exchange_rate');
3488
3489 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
3490 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
3491 l_amount := 0;
3492 END IF;
3493 END IF;
3494 cep_standard.debug('amount_threshold');
3495
3496 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
3497 CE_CASH_FCST.G_invalid_overdue_row)THEN
3498 l_amount := 0;
3499 END IF;
3500 cep_standard.debug('OVERDUE_COLUMN');
3501
3502 IF (l_amount <> 0) THEN
3503 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
3504
3505 UPDATE ce_forecasts
3506 SET error_status = 'X'
3507 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
3508
3509 FND_MESSAGE.set_name('CE', 'CE_FC_POP_NO_END_DATE');
3510 error_msg := FND_MESSAGE.get;
3511 CE_FORECAST_ERRORS_PKG.insert_row(
3512 CE_CASH_FCST.G_forecast_id,
3513 CE_CASH_FCST.G_rp_forecast_header_id,
3514 CE_CASH_FCST.G_forecast_row_id,
3515 'CE_FC_POP_NO_END_DATE',
3516 error_msg);
3517 error_flag := TRUE;
3518 END IF;
3519 cep_standard.debug('error_message');
3520
3521 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
3522 END IF;
3523 END LOOP;
3524 END LOOP;
3525 clear_aging_buckets;
3526 zero_fill_cells;
3527 END IF;
3528 ELSE
3529 IF (CE_CASH_FCST.G_use_payment_terms = 'Y') THEN
3530
3531 cep_standard.debug('PROJECT_ID IS NOT NULL');
3532 cep_standard.debug('USE_PAYMENT_TERMS IS Y');
3533
3534 FOR C_rec IN C_period LOOP
3535 FOR C_req_rec in C_orders_terms_proj(C_rec.start_date, C_rec.end_date) LOOP
3536 IF (C_req_rec.start_date < C_rec.start_date) THEN
3537 IF (C_req_rec.end_date < C_rec.end_date) THEN
3538 l_start_date := trunc(C_rec.start_date);
3539 l_end_date := trunc(C_req_rec.end_date);
3540 ELSE
3541 l_start_date := trunc(C_rec.start_date);
3542 l_end_date := trunc(C_rec.end_date);
3543 END IF;
3544 ELSE
3545 IF (C_req_rec.end_date > C_rec.end_date) THEN
3546 l_start_date := trunc(C_req_rec.start_date);
3547 l_end_date := trunc(C_rec.end_date);
3548 ELSE
3549 l_start_date := trunc(C_req_rec.start_date);
3550 l_end_date := trunc(C_req_rec.end_date);
3551 END IF;
3552 END IF;
3553 cep_standard.debug('l_start_date_proj = ' || to_char(l_start_date, 'DD-MON-YYYY'));
3554 cep_standard.debug('l_end_date_proj = ' || to_char(l_end_date, 'DD-MON-YYYY'));
3555
3556 /* In the case where we use terms and due amount is 0,
3557 we want to sum the term amounts and subtract them from the original
3558 source amount. Since this is too complex to perform within the cursor
3559 we do it within the FOR loop */
3560
3561 if C_req_rec.due_amount = 0 THEN
3562 select nvl(C_req_rec.amount,0) - sum(t.due_amount)
3563 INTO remain_amount
3564 from ap_terms_lines t
3565 where t.term_id = C_req_rec.term_id;
3566
3567 cep_standard.debug('remain_amount = ' || to_char(remain_amount));
3568
3569 l_amount := remain_amount/C_req_rec.total_dates * (l_end_date - l_start_date + 1);
3570
3571 end if;
3572
3573 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
3574
3575 cep_standard.debug('per_day_amount_proj = ' || to_char(C_req_rec.per_day_amount));
3576 cep_standard.debug('l_amount_proj = ' || to_char(l_amount));
3577
3578 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
3579 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
3580 l_amount := 0;
3581 END IF;
3582 cep_standard.debug('currency_code_proj');
3583
3584
3585 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
3586 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
3587 l_amount := 0;
3588 END IF;
3589 cep_standard.debug('authorization_status_proj');
3590
3591
3592 IF(CE_CASH_FCST.G_payment_priority IS NOT NULL AND
3593 C_req_rec.payment_priority <> CE_CASH_FCST.G_payment_priority ) THEN
3594 l_amount := 0;
3595 END IF;
3596 cep_standard.debug('payment_priority_proj');
3597
3598
3599 IF(CE_CASH_FCST.G_pay_group IS NOT NULL AND
3600 C_req_rec.paygroup <> CE_CASH_FCST.G_pay_group) THEN
3601 l_amount := 0;
3602 END IF;
3603 cep_standard.debug('paygroup_proj');
3604
3605
3606 IF(CE_CASH_FCST.G_vendor_type IS NOT NULL AND
3607 C_req_rec.vendor_type <> CE_CASH_FCST.G_vendor_type) THEN
3608 l_amount := 0;
3609 END IF;
3610 cep_standard.debug('vendor_type_proj');
3611
3612 SELECT to_number(ORGANIZATION_ID)
3613 INTO l_legal_entity_id
3614 FROM hr_operating_units
3615 WHERE organization_id = C_req_rec.org_id;
3616 cep_standard.debug('legal_entity_id_proj');
3617
3618
3619 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
3620 (nvl(C_req_rec.org_id,CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
3621 l_amount := 0;
3622 END IF;
3623 cep_standard.debug('org_id_proj');
3624
3625 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
3626 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
3627 OPEN C_sob(C_req_rec.org_id);
3628 FETCH C_sob INTO l_dummy;
3629 IF C_sob%NOTFOUND THEN
3630 CLOSE C_sob;
3631 l_amount := 0;
3632 END IF;
3633 CLOSE C_sob;
3634 END IF;
3635 cep_standard.debug('set_of_books_id_proj');
3636
3637 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
3638 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
3639 OPEN C_rate(C_req_rec.currency_code);
3640 FETCH C_rate INTO l_rate;
3641 IF C_rate%NOTFOUND THEN
3642 l_rate := 1;
3643 END IF;
3644 CLOSE C_rate;
3645 ELSIF (CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
3646 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
3647 ELSE
3648 l_rate := 1;
3649 END IF;
3650 cep_standard.debug('exchange_rate_proj');
3651
3652 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
3653 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
3654 l_amount := 0;
3655 END IF;
3656 END IF;
3657 cep_standard.debug('amount_threshold_proj');
3658
3659 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
3660 CE_CASH_FCST.G_invalid_overdue_row)THEN
3661 l_amount := 0;
3662 END IF;
3663 cep_standard.debug('OVERDUE_COLUMN_proj');
3664
3665 IF (l_amount <> 0) THEN
3666 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
3667
3668 UPDATE ce_forecasts
3669 SET error_status = 'X'
3670 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
3671
3672 FND_MESSAGE.set_name('CE', 'CE_FC_POP_NO_END_DATE');
3673 error_msg := FND_MESSAGE.get;
3674 CE_FORECAST_ERRORS_PKG.insert_row(
3675 CE_CASH_FCST.G_forecast_id,
3676 CE_CASH_FCST.G_rp_forecast_header_id,
3677 CE_CASH_FCST.G_forecast_row_id,
3678 'CE_FC_POP_NO_END_DATE',
3679 error_msg);
3680 error_flag := TRUE;
3681 END IF;
3682 cep_standard.debug('error_message_proj');
3683
3684 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
3685 END IF;
3686 END LOOP;
3687 END LOOP;
3688 clear_aging_buckets;
3689 zero_fill_cells;
3690 ELSE
3691
3692 cep_standard.debug('PROJECT_ID IS NOT NULL');
3693 cep_standard.debug('USE_PAYMENT_TERMS IS N');
3694
3695 FOR C_rec IN C_period LOOP
3696 FOR C_req_rec in C_orders_proj(C_rec.start_date, C_rec.end_date) LOOP
3697 IF (C_req_rec.start_date < C_rec.start_date) THEN
3698 IF (C_req_rec.end_date < C_rec.end_date) THEN
3699 l_start_date := trunc(C_rec.start_date);
3700 l_end_date := trunc(C_req_rec.end_date);
3701 ELSE
3702 l_start_date := trunc(C_rec.start_date);
3703 l_end_date := trunc(C_rec.end_date);
3704 END IF;
3705 ELSE
3706 IF (C_req_rec.end_date > C_rec.end_date) THEN
3707 l_start_date := trunc(C_req_rec.start_date);
3708 l_end_date := trunc(C_rec.end_date);
3709 ELSE
3710 l_start_date := trunc(C_req_rec.start_date);
3711 l_end_date := trunc(C_req_rec.end_date);
3712 END IF;
3713 END IF;
3714 cep_standard.debug('l_start_date_proj = ' || to_char(l_start_date, 'DD-MON-YYYY'));
3715 cep_standard.debug('l_end_date_proj = ' || to_char(l_end_date, 'DD-MON-YYYY'));
3716
3717 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
3718
3719 cep_standard.debug('per_day_amount_proj = ' || to_char(C_req_rec.per_day_amount));
3720 cep_standard.debug('l_amount_proj = ' || to_char(l_amount));
3721
3722 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
3723 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
3724 l_amount := 0;
3725 END IF;
3726 cep_standard.debug('currency_code_proj');
3727
3728
3729 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
3730 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
3731 l_amount := 0;
3732 END IF;
3733 cep_standard.debug('authorization_status_proj');
3734
3735
3736 IF(CE_CASH_FCST.G_payment_priority IS NOT NULL AND
3737 C_req_rec.payment_priority <> CE_CASH_FCST.G_payment_priority ) THEN
3738 l_amount := 0;
3739 END IF;
3740 cep_standard.debug('payment_priority_proj');
3741
3742
3743 IF(CE_CASH_FCST.G_pay_group IS NOT NULL AND
3744 C_req_rec.paygroup <> CE_CASH_FCST.G_pay_group) THEN
3745 l_amount := 0;
3746 END IF;
3747 cep_standard.debug('paygroup_proj');
3748
3749
3750 IF(CE_CASH_FCST.G_vendor_type IS NOT NULL AND
3751 C_req_rec.vendor_type <> CE_CASH_FCST.G_vendor_type) THEN
3752 l_amount := 0;
3753 END IF;
3754 cep_standard.debug('vendor_type_proj');
3755
3756 SELECT to_number(ORGANIZATION_ID)
3757 INTO l_legal_entity_id
3758 FROM hr_operating_units
3759 WHERE organization_id = C_req_rec.org_id;
3760 cep_standard.debug('legal_entity_id_proj');
3761
3762 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
3763 (nvl(C_req_rec.org_id,CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
3764 l_amount := 0;
3765 END IF;
3766 cep_standard.debug('org_id_proj');
3767
3768 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
3769 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
3770 OPEN C_sob(C_req_rec.org_id);
3771 FETCH C_sob INTO l_dummy;
3772 IF C_sob%NOTFOUND THEN
3773 CLOSE C_sob;
3774 l_amount := 0;
3775 END IF;
3776 CLOSE C_sob;
3777 END IF;
3778 cep_standard.debug('set_of_books_id_proj');
3779
3780 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
3781 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
3782 OPEN C_rate(C_req_rec.currency_code);
3783 FETCH C_rate INTO l_rate;
3784 IF C_rate%NOTFOUND THEN
3785 l_rate := 1;
3786 END IF;
3787 CLOSE C_rate;
3788 ELSIF (CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
3789 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
3790 ELSE
3791 l_rate := 1;
3792 END IF;
3793 cep_standard.debug('exchange_rate_proj');
3794
3795 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
3796 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
3797 l_amount := 0;
3798 END IF;
3799 END IF;
3800 cep_standard.debug('amount_threshold_proj');
3801
3802 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
3803 CE_CASH_FCST.G_invalid_overdue_row)THEN
3804 l_amount := 0;
3805 END IF;
3806 cep_standard.debug('OVERDUE_COLUMN_proj');
3807
3808 IF (l_amount <> 0) THEN
3809 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
3810
3811 UPDATE ce_forecasts
3812 SET error_status = 'X'
3813 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
3814
3815 FND_MESSAGE.set_name('CE', 'CE_FC_POP_NO_END_DATE');
3816 error_msg := FND_MESSAGE.get;
3817 CE_FORECAST_ERRORS_PKG.insert_row(
3818 CE_CASH_FCST.G_forecast_id,
3819 CE_CASH_FCST.G_rp_forecast_header_id,
3820 CE_CASH_FCST.G_forecast_row_id,
3821 'CE_FC_POP_NO_END_DATE',
3822 error_msg);
3823 error_flag := TRUE;
3824 END IF;
3825 cep_standard.debug('error_message_proj');
3826
3827 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
3828 END IF;
3829 END LOOP;
3830 END LOOP;
3831 clear_aging_buckets;
3832 zero_fill_cells;
3833 END IF;
3834 END IF;
3835 END IF;
3836 cep_standard.debug('<<ce_csh_fcST_POP.Build_PO_Orders_Query');
3837 EXCEPTION
3838 WHEN OTHERS THEN
3839 CEP_STANDARD.DEBUG('EXCEPTION:Build_PO_Orders_Query');
3840 raise;
3841 END Build_PO_Orders_Query;
3842
3843 /* ---------------------------------------------------------------------
3844 | PUBLIC PROCEDURE |
3845 | Build_PO_Req_Query |
3846 | |
3847 | DESCRIPTION |
3848 | Requisitions made but not fully ordered or cancelled |
3849 | CALLED BY |
3850 | Populate_Cells |
3851 | REQUIRES |
3852 | main_query |
3853 | HISTORY |
3854 | 12-JUL-1996 Created Bidemi Carrol |
3855 --------------------------------------------------------------------- */
3856 PROCEDURE Build_PO_Req_Query IS
3857 from_clause VARCHAR2(500);
3858 where_clause varchar2(1500);
3859 select_clause varchar2(1500);
3860 main_query varchar2(2000) := null;
3861
3862 l_amount NUMBER;
3863 l_legal_entity_id NUMBER;
3864 l_dummy NUMBER;
3865 l_rate NUMBER;
3866
3867 l_start_date DATE;
3868 l_end_date DATE;
3869 l_max_end_date DATE;
3870
3871 error_flag BOOLEAN := FALSE;
3872 error_msg FND_NEW_MESSAGES.message_text%TYPE;
3873
3874
3875 CURSOR C_period IS
3876 SELECT start_date,
3877 end_date,
3878 forecast_column_id
3879 FROM ce_forecast_ext_temp
3880 WHERE context_value = 'A'
3881 AND forecast_request_id = CE_CASH_FCST.G_forecast_id
3882 AND conversion_rate = CE_CASH_FCST.G_forecast_row_id;
3883
3884
3885 CURSOR C_sob(p_org_id NUMBER) IS
3886 SELECT 1
3887 FROM CE_FORECAST_ORGS_V
3888 WHERE set_of_books_id = CE_CASH_FCST.G_set_of_books_id
3889 AND org_id = p_org_id;
3890
3891 CURSOR C_rate(p_currency_code VARCHAR2) IS
3892 SELECT exchange_rate
3893 FROM ce_currency_rates_temp
3894 WHERE forecast_request_id = CE_CASH_FCST.G_forecast_id
3895 AND to_currency = CE_CASH_FCST.G_rp_forecast_currency
3896 AND currency_code = p_currency_code;
3897
3898 CURSOR C_requisitions(p_start_date DATE, p_end_date DATE) IS
3899 SELECT reference_id,
3900 currency_code,
3901 org_id,
3902 status,
3903 decode(end_date, null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
3904 (nvl(amount,0)/(trunc(end_date)-trunc(start_date)+1))) per_day_amount,
3905 start_date,
3906 nvl(end_date, l_max_end_date)end_date,
3907 end_date trx_end_date
3908 FROM ce_po_fc_requisitions_temp_v
3909 WHERE start_date <= p_end_date
3910 AND (end_date >= p_start_date
3911 OR end_date is NULL);
3912
3913
3914 CURSOR C_requisitions_proj(p_start_date DATE, p_end_date DATE) IS
3915 SELECT reference_id,
3916 currency_code,
3917 org_id,
3918 status,
3919 decode(trunc(end_date), null, (nvl(amount,0)/(trunc(l_max_end_date)-trunc(start_date)+1)),
3920 (nvl(amount,0)/(trunc(end_date)-trunc(start_date+1)))) per_day_amount,
3921 start_date,
3922 nvl(end_date, l_max_end_date)end_date,
3923 end_date trx_end_date
3924 FROM ce_po_fc_requisitions_temp_v
3925 WHERE start_date <= p_end_date
3926 AND (end_date >= p_start_date
3927 OR end_date is NULL)
3928 AND project_id = CE_CASH_FCST.G_rp_project_id;
3929
3930
3931
3932 BEGIN
3933
3934 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_PO_Req_Query');
3935
3936 select_clause := Get_Select_Clause;
3937 cep_standard.debug('Built Select Clause');
3938
3939 from_clause := Get_From_Clause('ce_po_fc_requisitions_v');
3940 cep_standard.debug('Built From Clause');
3941
3942 where_clause := Get_Where_Clause || '
3943 AND src.trx_date BETWEEN cab.start_date - '
3944 ||to_char(CE_CASH_FCST.G_lead_time)||
3945 ' and cab.end_date - '
3946 ||to_char(CE_CASH_FCST.G_lead_time)||
3947 Add_Where('AUTHORIZATION_STATUS') || Add_Where('PROJECT_ID');
3948
3949
3950 cep_standard.debug('Built Where Clause');
3951
3952 main_query := select_clause || from_clause || where_clause;
3953
3954 Execute_Main_Query (main_query);
3955
3956 cep_standard.debug('Execute_Main_Query');
3957
3958 IF (CE_CASH_FCST.G_include_temp_labor_flag = 'Y') THEN
3959
3960 populate_aging_buckets;
3961
3962 SELECT trunc(max(end_date))
3963 INTO l_max_end_date
3964 FROM ce_forecast_ext_temp
3965 WHERE context_value = 'A'
3966 AND forecast_request_id = CE_CASH_FCST.G_forecast_id
3967 AND conversion_rate = CE_CASH_FCST.G_forecast_row_id;
3968 cep_standard.debug('l_max_end_date = ' || to_char(l_max_end_date, 'DD-MON-YYYY'));
3969
3970 IF (CE_CASH_FCST.G_rp_project_id IS NULL) THEN
3971 cep_standard.debug('PROJECT_ID IS NULL');
3972
3973 FOR C_rec IN C_period LOOP
3974 FOR C_req_rec in C_requisitions(C_rec.start_date, C_rec.end_date) LOOP
3975 IF (C_req_rec.start_date < C_rec.start_date) THEN
3976 IF (C_req_rec.end_date < C_rec.end_date) THEN
3977 l_start_date := trunc(C_rec.start_date);
3978 l_end_date := trunc(C_req_rec.end_date);
3979 ELSE
3980 l_start_date := trunc(C_rec.start_date);
3981 l_end_date := trunc(C_rec.end_date);
3982 END IF;
3983 ELSE
3984 IF (C_req_rec.end_date > C_rec.end_date) THEN
3985 l_start_date := trunc(C_req_rec.start_date);
3986 l_end_date := trunc(C_rec.end_date);
3987 ELSE
3988 l_start_date := trunc(C_req_rec.start_date);
3989 l_end_date := trunc(C_req_rec.end_date);
3990 END IF;
3991 END IF;
3992 cep_standard.debug('l_start_date = ' || to_char(l_start_date, 'DD-MON-YYYY'));
3993 cep_standard.debug('l_end_date = ' || to_char(l_end_date, 'DD-MON-YYYY'));
3994
3995 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
3996
3997 cep_standard.debug('per_day_amount = ' || to_char(C_req_rec.per_day_amount));
3998 cep_standard.debug('l_amount = ' || to_char(l_amount));
3999
4000 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
4001 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
4002 l_amount := 0;
4003 END IF;
4004 cep_standard.debug('currency_code');
4005
4006 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
4007 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
4008 l_amount := 0;
4009 END IF;
4010 cep_standard.debug('authorization_status');
4011
4012 SELECT to_number(ORGANIZATION_ID)
4013 INTO l_legal_entity_id
4014 FROM hr_operating_units
4015 WHERE organization_id = C_req_rec.org_id;
4016 cep_standard.debug('legal_entity_id');
4017
4018 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
4019 (nvl(C_req_rec.org_id, CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
4020 l_amount := 0;
4021 END IF;
4022 cep_standard.debug('org_id');
4023
4024 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
4025 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
4026 OPEN C_sob(C_req_rec.org_id);
4027 FETCH C_sob INTO l_dummy;
4028 IF C_sob%NOTFOUND THEN
4029 CLOSE C_sob;
4030 l_amount := 0;
4031 END IF;
4032 CLOSE C_sob;
4033 END IF;
4034 cep_standard.debug('set_of_books_id');
4035
4036 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
4037 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
4038 OPEN C_rate(C_req_rec.currency_code);
4039 FETCH C_rate INTO l_rate;
4040 IF C_rate%NOTFOUND THEN
4041 l_rate := 1;
4042 END IF;
4043 CLOSE C_rate;
4044 ELSIF( CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4045 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
4046 ELSE
4047 l_rate := 1;
4048 END IF;
4049 cep_standard.debug('exchange_rate');
4050
4051 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
4052 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
4053 l_amount := 0;
4054 END IF;
4055 END IF;
4056 cep_standard.debug('amount_threshold');
4057
4058 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
4059 CE_CASH_FCST.G_invalid_overdue_row)THEN
4060 l_amount := 0;
4061 END IF;
4062 cep_standard.debug('OVERDUE_COLUMN');
4063
4064 IF (l_amount <> 0) THEN
4065 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
4066
4067 UPDATE ce_forecasts
4068 SET error_status = 'X'
4069 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
4070
4071 FND_MESSAGE.set_name('CE', 'CE_FC_POR_NO_END_DATE');
4072 error_msg := FND_MESSAGE.get;
4073 CE_FORECAST_ERRORS_PKG.insert_row(
4074 CE_CASH_FCST.G_forecast_id,
4075 CE_CASH_FCST.G_rp_forecast_header_id,
4076 CE_CASH_FCST.G_forecast_row_id,
4077 'CE_FC_POR_NO_END_DATE',
4078 error_msg);
4079 error_flag := TRUE;
4080 END IF;
4081 cep_standard.debug('error_message');
4082
4083 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
4084 END IF;
4085 cep_standard.debug('INSERT_FCAST_CELL');
4086
4087 END LOOP;
4088 END LOOP;
4089
4090 clear_aging_buckets;
4091 zero_fill_cells;
4092
4093 ELSE
4094 cep_standard.debug('PROJECT_ID NOT NULL');
4095
4096 FOR C_rec IN C_period LOOP
4097 FOR C_req_rec in C_requisitions_proj(C_rec.start_date, C_rec.end_date) LOOP
4098 IF (C_req_rec.start_date < C_rec.start_date) THEN
4099 IF (C_req_rec.end_date < C_rec.end_date) THEN
4100 l_start_date := trunc(C_rec.start_date);
4101 l_end_date := trunc(C_req_rec.end_date);
4102 ELSE
4103 l_start_date := trunc(C_rec.start_date);
4104 l_end_date := trunc(C_rec.end_date);
4105 END IF;
4106 ELSE
4107 IF (C_req_rec.end_date > C_rec.end_date) THEN
4108 l_start_date := trunc(C_req_rec.start_date);
4109 l_end_date := trunc(C_rec.end_date);
4110 ELSE
4111 l_start_date := trunc(C_req_rec.start_date);
4112 l_end_date := trunc(C_req_rec.end_date);
4113 END IF;
4114 END IF;
4115
4116 cep_standard.debug('l_start_date_proj = ' || to_char(l_start_date, 'DD-MON-YYYY'));
4117 cep_standard.debug('l_end_date_proj = ' || to_char(l_end_date, 'DD-MON-YYYY'));
4118
4119 l_amount := C_req_rec.per_day_amount * (l_end_date - l_start_date + 1);
4120
4121 cep_standard.debug('per_day_amount_proj = ' || to_char(C_req_rec.per_day_amount));
4122 cep_standard.debug('l_amount_proj = ' || to_char(l_amount));
4123
4124 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
4125 C_req_rec.currency_code <> CE_CASH_FCST.G_rp_src_currency) THEN
4126 l_amount := 0;
4127 END IF;
4128 cep_standard.debug('currency_code_proj');
4129
4130 IF(CE_CASH_FCST.G_authorization_status IS NOT NULL AND
4131 C_req_rec.status <> CE_CASH_FCST.G_authorization_status) THEN
4132 l_amount := 0;
4133 END IF;
4134 cep_standard.debug('authorization_status_proj');
4135
4136 SELECT to_number(ORGANIZATION_ID)
4137 INTO l_legal_entity_id
4138 FROM hr_operating_units
4139 WHERE organization_id = C_req_rec.org_id;
4140 cep_standard.debug('legal_entity_id_proj');
4141
4142 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
4143 (nvl(C_req_rec.org_id, CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) ) THEN
4144 l_amount := 0;
4145 END IF;
4146 cep_standard.debug('org_id_proj');
4147
4148 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
4149 CE_CASH_FCST.G_set_of_books_id <> -1) THEN
4150 OPEN C_sob(C_req_rec.org_id);
4151 FETCH C_sob INTO l_dummy;
4152 IF C_sob%NOTFOUND THEN
4153 CLOSE C_sob;
4154 l_amount := 0;
4155 END IF;
4156 CLOSE C_sob;
4157 END IF;
4158 cep_standard.debug('set_of_books_id_proj');
4159
4160 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
4161 CE_CASH_FCST.G_rp_exchange_type <> 'User')THEN
4162 OPEN C_rate(C_req_rec.currency_code);
4163 FETCH C_rate INTO l_rate;
4164 IF C_rate%NOTFOUND THEN
4165 l_rate := 1;
4166 END IF;
4167 CLOSE C_rate;
4168 ELSIF( CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4169 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
4170 ELSE
4171 l_rate := 1;
4172 END IF;
4173 cep_standard.debug('exchange_rate_proj');
4174
4175 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL) THEN
4176 IF (abs(l_amount) <= CE_CASH_FCST.G_rp_amount_threshold) THEN
4177 l_amount := 0;
4178 END IF;
4179 END IF;
4180 cep_standard.debug('amount_threshold_proj');
4181
4182 IF(C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id AND
4183 CE_CASH_FCST.G_invalid_overdue_row)THEN
4184 l_amount := 0;
4185 END IF;
4186 cep_standard.debug('OVERDUE_COLUMN_proj');
4187
4188 IF (l_amount <> 0) THEN
4189 IF (C_req_rec.trx_end_date IS NULL and error_flag = FALSE) THEN
4190
4191 UPDATE ce_forecasts
4192 SET error_status = 'X'
4193 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
4194
4195 FND_MESSAGE.set_name('CE', 'CE_FC_POR_NO_END_DATE');
4196 error_msg := FND_MESSAGE.get;
4197 CE_FORECAST_ERRORS_PKG.insert_row(
4198 CE_CASH_FCST.G_forecast_id,
4199 CE_CASH_FCST.G_rp_forecast_header_id,
4200 CE_CASH_FCST.G_forecast_row_id,
4201 'CE_FC_POR_NO_END_DATE',
4202 error_msg);
4203 error_flag := TRUE;
4204 END IF;
4205 cep_standard.debug('error_message_proj');
4206
4207 Insert_Fcast_Cell(C_req_rec.reference_id, C_req_rec.currency_code, l_legal_entity_id, null, null, -(l_amount*l_rate), -(l_amount), C_rec.forecast_column_id);
4208 cep_standard.debug('INSERT_FCAST_CELL_proj');
4209 END IF;
4210
4211 END LOOP;
4212 END LOOP;
4213
4214 clear_aging_buckets;
4215 zero_fill_cells;
4216
4217 END IF;
4218 END IF;
4219
4220 cep_standard.debug('<<ce_csh_fcST_POP.Build_PO_Req_Query');
4221 EXCEPTION
4222 WHEN OTHERS THEN
4223 CEP_STANDARD.DEBUG('EXCEPTION:Build_PO_req_Query');
4224 raise;
4225 END Build_PO_Req_Query ;
4226
4227 /* ---------------------------------------------------------------------
4228 | PUBLIC PROCEDURE |
4229 | Build_Sales_Fcst_Query |
4230 | |
4231 | DESCRIPTION |
4232 | Sales forecasted to be made within a certain accounting period |
4233 | CALLED BY |
4234 | Populate_Cells |
4235 | REQUIRES |
4236 | main_query |
4237 | HISTORY |
4238 | 12-JUL-1996 Created Bidemi Carrol |
4239 | 21-MAY-1998 OSM Integration Byung-Hyun Chung |
4240 --------------------------------------------------------------------- */
4241 PROCEDURE Build_Sales_Fcst_Query IS
4242 from_clause VARCHAR2(500);
4243 where_clause varchar2(1500);
4244 select_clause varchar2(1500);
4245 view_name VARCHAR2(50);
4246 main_query varchar2(3500) := null;
4247 BEGIN
4248 cep_standard.debug('>>Build_Sales_Fcst_Query');
4249 select_clause := Get_Select_Clause;
4250 cep_standard.debug('Built Select Clause');
4251
4252 from_clause := Get_From_Clause ('ce_as_fc_sales_fcst_v');
4253 cep_standard.debug('Built From Clause');
4254
4255 where_clause := Get_Where_Clause || '
4256 AND src.trx_date BETWEEN cab.start_date - '
4257 ||to_char(CE_CASH_FCST.G_lead_time)||
4258 ' and cab.end_date - '
4259 ||to_char(CE_CASH_FCST.G_lead_time)||
4260 ' AND NVL(src.win_probability, 0) >= ' ||to_char(CE_CASH_FCST.G_win_probability)||
4261 Add_Where('CHANNEL_CODE') || Add_Where('SALES_STAGE_ID') || Add_Where('SALES_FORECAST_STATUS');
4262
4263 cep_standard.debug('Built Where Clause');
4264
4265
4266 main_query := select_clause || from_clause || where_clause;
4267 Execute_Main_Query (main_query);
4268
4269 cep_standard.debug('<<ce_csh_fcST_POP.Build_Sales_Fcst_Query');
4270 EXCEPTION
4271 WHEN OTHERS THEN
4272 cep_standard.debug('EXCEPTION:OTHERS-Build_Sales_Fcst_Query');
4273 raise;
4274 END Build_Sales_Fcst_Query ;
4275
4276
4277 /* ---------------------------------------------------------------------
4278 | PUBLIC PROCEDURE |
4279 | Build_Sales_Order_Query |
4280 | |
4281 | DESCRIPTION |
4282 | Sales orders that have not been fully invoiced and/or paid |
4283 | CALLED BY |
4284 | Populate_Cells |
4285 | REQUIRES |
4286 | main_query |
4287 | HISTORY |
4288 | 12-JUL-1996 Created Bidemi Carrol |
4289 --------------------------------------------------------------------- */
4290 PROCEDURE Build_Sales_Order_Query IS
4291 from_clause_1 VARCHAR2(500); -- Payment terms checkbox not checked
4292 from_clause_2 VARCHAR2(500); -- Checkbox checked and terms available
4293 from_clause_3 VARCHAR2(500); -- Checkbox checked and terms are null
4294 where_clause varchar2(2000);
4295 where_clause_1 varchar2(2000);
4296 where_clause_2 varchar2(2000);
4297 where_clause_3 varchar2(2000);
4298 select_clause_1 varchar2(2000);
4299 select_clause_2 varchar2(2000);
4300 select_clause_3 varchar2(2000);
4301 main_query_1 varchar2(3500) := null;
4302 main_query_2 varchar2(3500) := null;
4303 main_query_3 varchar2(3500) := null;
4304 BEGIN
4305 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_Sales_Order_Query');
4306
4307 --select_clause := Get_Select_Clause;
4308 --cep_standard.debug('Built Select Clause');
4309
4310 from_clause_1 := Get_From_Clause('ce_so_fc_orders_v');
4311 from_clause_2 := Get_From_Clause('ce_so_fc_orders_terms_v');
4312 from_clause_3 := Get_From_Clause('ce_so_fc_orders_no_terms_v');
4313 cep_standard.debug('Built From Clause');
4314
4315 where_clause := Get_Where_Clause ||
4316 Add_Where('CUSTOMER_PROFILE_CLASS_ID') || Add_Where('PROJECT_ID')
4317 || Add_Where('ORDER_TYPE_ID');
4318
4319 IF( nvl(CE_CASH_FCST.G_order_status,'A') = 'O') THEN
4320 where_clause := where_clause || '
4321 AND NVL(src.booked_flag, ''N'') = ''N'' ';
4322 ELSIF( nvl(CE_CASH_FCST.G_order_status,'A') = 'B')THEN
4323 where_clause := where_clause || '
4324 AND NVL(src.booked_flag, ''N'') = ''Y'' ';
4325 END IF;
4326
4327 IF (CE_CASH_FCST.G_use_payment_terms = 'Y') THEN
4328 IF(CE_CASH_FCST.G_order_date_type = 'R')THEN
4329 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4330 select_clause_2 := '
4331 SELECT /*+ USE_MERGE(o3,hr_ou) +*/ CE_FORECAST_TRX_CELLS_S.nextval,
4332 '||CE_CASH_FCST.G_forecast_id||',
4333 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4334 '||CE_CASH_FCST.G_forecast_row_id||',
4335 ''Y'',
4336 nvl(fnd_global.user_id,-1),
4337 sysdate,
4338 nvl(fnd_global.user_id,-1),
4339 sysdate,
4340 nvl(fnd_global.user_id,-1),
4341 cab.forecast_column_id,
4342 src.reference_id,
4343 src.currency_code,
4344 to_number(hr_ou.ORGANIZATION_ID),
4345 decode(src.term_due_date, null,
4346 decode(src.term_due_days, null,
4347 decode(src.term_due_months_forward, null, src.date_requested,
4348 (TRUNC(ADD_MONTHS(src.date_requested, src.term_due_months_forward),
4349 ''MONTH'')+ src.term_due_day_of_month - 1)),
4350 src.date_requested + src.term_due_days),
4351 src.term_due_date) + '
4352 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4353 null,
4354 null,
4355 round(nvl(src.amount,0) * (nvl(src.relative_amount,100)/100)
4356 *'||CE_CASH_FCST.G_rp_exchange_rate
4357 ||','||CE_CASH_FCST.G_precision||'),
4358 nvl(src.amount,0) * (nvl(src.relative_amount,100)/100)';
4359 ELSE
4360 select_clause_2 := '
4361 SELECT /*+ USE_MERGE(o3,hr_ou) +*/ CE_FORECAST_TRX_CELLS_S.nextval,
4362 '||CE_CASH_FCST.G_forecast_id||',
4363 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4364 '||CE_CASH_FCST.G_forecast_row_id||',
4365 ''Y'',
4366 nvl(fnd_global.user_id,-1),
4367 sysdate,
4368 nvl(fnd_global.user_id,-1),
4369 sysdate,
4370 nvl(fnd_global.user_id,-1),
4371 cab.forecast_column_id,
4372 src.reference_id,
4373 src.currency_code,
4374 to_number(hr_ou.ORGANIZATION_ID),
4375 decode(src.term_due_date, null,
4376 decode(src.term_due_days, null,
4377 decode(src.term_due_months_forward, null, src.date_requested,
4378 (TRUNC(ADD_MONTHS(src.date_requested, src.term_due_months_forward),
4379 ''MONTH'')+ src.term_due_day_of_month - 1)),
4380 src.date_requested + src.term_due_days),
4381 src.term_due_date) + '
4382 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4383 null,
4384 null,
4385 round(nvl(src.amount,0) * (nvl(src.relative_amount,100)/100) * curr.exchange_rate'
4386 ||','||CE_CASH_FCST.G_precision||'),
4387 nvl(src.amount,0) * (nvl(src.relative_amount,100)/100)';
4388 END IF;
4389
4390 where_clause_2 := where_clause || ' AND
4391 decode(src.term_due_date, null,
4392 decode(src.term_due_days, null,
4393 decode(src.term_due_months_forward, null, src.date_requested,
4394 (TRUNC(ADD_MONTHS(src.date_requested, src.term_due_months_forward),
4395 ''MONTH'')+ src.term_due_day_of_month - 1)),
4396 src.date_requested + src.term_due_days),
4397 src.term_due_date) BETWEEN cab.start_date - '
4398 ||to_char(CE_CASH_FCST.G_lead_time)||
4399 ' and cab.end_date - '
4400 ||to_char(CE_CASH_FCST.G_lead_time);
4401 ELSE
4402 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4403 select_clause_2 := '
4404 SELECT /*+ USE_MERGE(o3,hr_ou) +*/ CE_FORECAST_TRX_CELLS_S.nextval,
4405 '||CE_CASH_FCST.G_forecast_id||',
4406 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4407 '||CE_CASH_FCST.G_forecast_row_id||',
4408 ''Y'',
4409 nvl(fnd_global.user_id,-1),
4410 sysdate,
4411 nvl(fnd_global.user_id,-1),
4412 sysdate,
4413 nvl(fnd_global.user_id,-1),
4414 cab.forecast_column_id,
4415 src.reference_id,
4416 src.currency_code,
4417 to_number(hr_ou.ORGANIZATION_ID),
4418 decode(src.term_due_date, null,
4419 decode(src.term_due_days, null,
4420 decode(src.term_due_months_forward, null, src.date_ordered,
4421 TRUNC(ADD_MONTHS(src.date_ordered, src.term_due_months_forward),
4422 ''MONTH'')+ src.term_due_day_of_month - 1),
4423 src.date_ordered + src.term_due_days),
4424 src.term_due_date) + '
4425 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4426 null,
4427 null,
4428 round(nvl(src.amount,0)*(nvl(src.relative_amount,100)/100)*'
4429 ||CE_CASH_FCST.G_rp_exchange_rate
4430 ||','||CE_CASH_FCST.G_precision||'),
4431 nvl(src.amount,0) * (nvl(src.relative_amount,100)/100)';
4432 ELSE
4433 select_clause_2 := '
4434 SELECT /*+ USE_MERGE(o3,hr_ou) +*/ CE_FORECAST_TRX_CELLS_S.nextval,
4435 '||CE_CASH_FCST.G_forecast_id||',
4436 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4437 '||CE_CASH_FCST.G_forecast_row_id||',
4438 ''Y'',
4439 nvl(fnd_global.user_id,-1),
4440 sysdate,
4441 nvl(fnd_global.user_id,-1),
4442 sysdate,
4443 nvl(fnd_global.user_id,-1),
4444 cab.forecast_column_id,
4445 src.reference_id,
4446 src.currency_code,
4447 to_number(hr_ou.ORGANIZATION_ID),
4448 decode(src.term_due_date, null,
4449 decode(src.term_due_days, null,
4450 decode(src.term_due_months_forward, null, src.date_ordered,
4451 TRUNC(ADD_MONTHS(src.date_ordered, src.term_due_months_forward),
4452 ''MONTH'')+ src.term_due_day_of_month - 1),
4453 src.date_ordered + src.term_due_days),
4454 src.term_due_date) + '
4455 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4456 null,
4457 null,
4458 round(nvl(src.amount,0)*(nvl(src.relative_amount,100)/100)*curr.exchange_rate'
4459 ||','||CE_CASH_FCST.G_precision||'),
4460 nvl(src.amount,0) * (nvl(src.relative_amount,100)/100)';
4461 END IF;
4462 where_clause_2 := where_clause || ' AND
4463 decode(src.term_due_date, null,
4464 decode(src.term_due_days, null,
4465 decode(src.term_due_months_forward, null, src.date_ordered,
4466 TRUNC(ADD_MONTHS(src.date_ordered, src.term_due_months_forward),
4467 ''MONTH'')+ src.term_due_day_of_month - 1),
4468 src.date_ordered + src.term_due_days),
4469 src.term_due_date) BETWEEN cab.start_date - '
4470 ||to_char(CE_CASH_FCST.G_lead_time)||
4471 ' and cab.end_date - '
4472 ||to_char(CE_CASH_FCST.G_lead_time);
4473 END IF;
4474 ELSE -- payment_term = 'N'
4475 IF(CE_CASH_FCST.G_order_date_type = 'R')THEN
4476 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4477 select_clause_1 := '
4478 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4479 '||CE_CASH_FCST.G_forecast_id||',
4480 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4481 '||CE_CASH_FCST.G_forecast_row_id||',
4482 ''Y'',
4483 nvl(fnd_global.user_id,-1),
4484 sysdate,
4485 nvl(fnd_global.user_id,-1),
4486 sysdate,
4487 nvl(fnd_global.user_id,-1),
4488 cab.forecast_column_id,
4489 src.reference_id,
4490 src.currency_code,
4491 to_number(hr_ou.ORGANIZATION_ID),
4492 src.date_requested +'
4493 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4494 null,
4495 null,
4496 round(nvl(src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
4497 ||','||CE_CASH_FCST.G_precision||'),
4498 src.amount';
4499 ELSE
4500 select_clause_1 := '
4501 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4502 '||CE_CASH_FCST.G_forecast_id||',
4503 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4504 '||CE_CASH_FCST.G_forecast_row_id||',
4505 ''Y'',
4506 nvl(fnd_global.user_id,-1),
4507 sysdate,
4508 nvl(fnd_global.user_id,-1),
4509 sysdate,
4510 nvl(fnd_global.user_id,-1),
4511 cab.forecast_column_id,
4512 src.reference_id,
4513 src.currency_code,
4514 to_number(hr_ou.ORGANIZATION_ID),
4515 src.date_requested +'
4516 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4517 null,
4518 null,
4519 round(nvl(src.amount,0)*curr.exchange_rate'
4520 ||','||CE_CASH_FCST.G_precision||'),
4521 src.amount';
4522 END IF;
4523 where_clause_1 := where_clause || '
4524 AND src.date_requested BETWEEN cab.start_date - '
4525 ||to_char(CE_CASH_FCST.G_lead_time)||
4526 ' and cab.end_date - '
4527 ||to_char(CE_CASH_FCST.G_lead_time);
4528 ELSE
4529 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4530 select_clause_1 := '
4531 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4532 '||CE_CASH_FCST.G_forecast_id||',
4533 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4534 '||CE_CASH_FCST.G_forecast_row_id||',
4535 ''Y'',
4536 nvl(fnd_global.user_id,-1),
4537 sysdate,
4538 nvl(fnd_global.user_id,-1),
4539 sysdate,
4540 nvl(fnd_global.user_id,-1),
4541 cab.forecast_column_id,
4542 src.reference_id,
4543 src.currency_code,
4544 to_number(hr_ou.ORGANIZATION_ID),
4545 src.date_ordered +'
4546 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4547 null,
4548 null,
4549 round(nvl(src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
4550 ||','||CE_CASH_FCST.G_precision||'),
4551 src.amount';
4552 ELSE
4553 select_clause_1 := '
4554 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4555 '||CE_CASH_FCST.G_forecast_id||',
4556 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4557 '||CE_CASH_FCST.G_forecast_row_id||',
4558 ''Y'',
4559 nvl(fnd_global.user_id,-1),
4560 sysdate,
4561 nvl(fnd_global.user_id,-1),
4562 sysdate,
4563 nvl(fnd_global.user_id,-1),
4564 cab.forecast_column_id,
4565 src.reference_id,
4566 src.currency_code,
4567 to_number(hr_ou.ORGANIZATION_ID),
4568 src.date_ordered +'
4569 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4570 null,
4571 null,
4572 round(nvl(src.amount,0)*curr.exchange_rate'
4573 ||','||CE_CASH_FCST.G_precision||'),
4574 src.amount';
4575 END IF;
4576 where_clause_1 := where_clause || '
4577 AND src.date_ordered BETWEEN cab.start_date - '
4578 ||to_char(CE_CASH_FCST.G_lead_time)||
4579 ' and cab.end_date - '
4580 ||to_char(CE_CASH_FCST.G_lead_time);
4581 END IF;
4582 END IF;
4583
4584 IF(CE_CASH_FCST.G_order_date_type = 'R')THEN
4585 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4586 select_clause_3 := '
4587 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4588 '||CE_CASH_FCST.G_forecast_id||',
4589 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4590 '||CE_CASH_FCST.G_forecast_row_id||',
4591 ''Y'',
4592 nvl(fnd_global.user_id,-1),
4593 sysdate,
4594 nvl(fnd_global.user_id,-1),
4595 sysdate,
4596 nvl(fnd_global.user_id,-1),
4597 cab.forecast_column_id,
4598 src.reference_id,
4599 src.currency_code,
4600 to_number(hr_ou.ORGANIZATION_ID),
4601 src.date_requested +'
4602 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4603 null,
4604 null,
4605 round(nvl(src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
4606 ||','||CE_CASH_FCST.G_precision||'),
4607 src.amount';
4608 ELSE
4609 select_clause_3 := '
4610 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4611 '||CE_CASH_FCST.G_forecast_id||',
4612 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4613 '||CE_CASH_FCST.G_forecast_row_id||',
4614 ''Y'',
4615 nvl(fnd_global.user_id,-1),
4616 sysdate,
4617 nvl(fnd_global.user_id,-1),
4618 sysdate,
4619 nvl(fnd_global.user_id,-1),
4620 cab.forecast_column_id,
4621 src.reference_id,
4622 src.currency_code,
4623 to_number(hr_ou.ORGANIZATION_ID),
4624 src.date_requested +'
4625 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4626 null,
4627 null,
4628 round(nvl(src.amount,0)*curr.exchange_rate'
4629 ||','||CE_CASH_FCST.G_precision||'),
4630 src.amount';
4631 END IF;
4632 where_clause_3 := where_clause || '
4633 AND src.date_requested BETWEEN cab.start_date - '
4634 ||to_char(CE_CASH_FCST.G_lead_time)||
4635 ' and cab.end_date - '
4636 ||to_char(CE_CASH_FCST.G_lead_time);
4637 ELSE
4638 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4639 select_clause_3 := '
4640 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4641 '||CE_CASH_FCST.G_forecast_id||',
4642 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4643 '||CE_CASH_FCST.G_forecast_row_id||',
4644 ''Y'',
4645 nvl(fnd_global.user_id,-1),
4646 sysdate,
4647 nvl(fnd_global.user_id,-1),
4648 sysdate,
4649 nvl(fnd_global.user_id,-1),
4650 cab.forecast_column_id,
4651 src.reference_id,
4652 src.currency_code,
4653 to_number(hr_ou.ORGANIZATION_ID),
4654 src.date_ordered +'
4655 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4656 null,
4657 null,
4658 round(nvl(src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
4659 ||','||CE_CASH_FCST.G_precision||'),
4660 src.amount';
4661 ELSE
4662 select_clause_3 := '
4663 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4664 '||CE_CASH_FCST.G_forecast_id||',
4665 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4666 '||CE_CASH_FCST.G_forecast_row_id||',
4667 ''Y'',
4668 nvl(fnd_global.user_id,-1),
4669 sysdate,
4670 nvl(fnd_global.user_id,-1),
4671 sysdate,
4672 nvl(fnd_global.user_id,-1),
4673 cab.forecast_column_id,
4674 src.reference_id,
4675 src.currency_code,
4676 to_number(hr_ou.ORGANIZATION_ID),
4677 src.date_ordered +'
4678 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
4679 null,
4680 null,
4681 round(nvl(src.amount,0)*curr.exchange_rate'
4682 ||','||CE_CASH_FCST.G_precision||'),
4683 src.amount';
4684 END IF;
4685 where_clause_3 := where_clause || '
4686 AND src.date_ordered BETWEEN cab.start_date - '
4687 ||to_char(CE_CASH_FCST.G_lead_time)||
4688 ' and cab.end_date - '
4689 ||to_char(CE_CASH_FCST.G_lead_time);
4690 END IF;
4691
4692 cep_standard.debug('Built Select Clause');
4693 cep_standard.debug('Built Where Clause');
4694
4695
4696 main_query_1 := select_clause_1 || from_clause_1 || where_clause_1;
4697 main_query_2 := select_clause_2 || from_clause_2 || where_clause_2;
4698 main_query_3 := select_clause_3 || from_clause_3 || where_clause_3;
4699
4700 IF (CE_CASH_FCST.G_use_payment_terms = 'Y') THEN
4701 Execute_Main_Query (main_query_2);
4702 Execute_Main_Query (main_query_3);
4703 ELSE
4704 Execute_Main_Query (main_query_1);
4705 END IF;
4706
4707 cep_standard.debug('<<ce_csh_fcST_POP.Build_Sales_Order_Query');
4708 EXCEPTION
4709 WHEN OTHERS THEN
4710 CEP_STANDARD.DEBUG('EXCEPTION:Build_Sales_Order_Query');
4711 raise;
4712 END Build_Sales_Order_Query ;
4713
4714
4715
4716 /* ---------------------------------------------------------------------
4717 | PUBLIC PROCEDURE |
4718 | Build_PA_Exp_Report_Query |
4719 | |
4720 | DESCRIPTION |
4721 | This procedure builds the query to calculate the forecast |
4722 | amounts for released expense report from PA but not has been |
4723 | transferred to AP yet. |
4724 | CALLED BY |
4725 | Build_Exp_Report_Query |
4726 | REQUIRES |
4727 | main_query |
4728 | HISTORY |
4729 | 20-NOV-1998 Created BHChung |
4730 --------------------------------------------------------------------- */
4731 PROCEDURE Build_PA_Exp_Report_Query IS
4732 from_clause VARCHAR2(500);
4733 where_clause varchar2(1500);
4734 select_clause varchar2(1500);
4735 main_query varchar2(3500) := null;
4736 BEGIN
4737 CE_CASH_FCST.G_app_short_name := 'PA';
4738
4739 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4740 select_clause := '
4741 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4742 '||CE_CASH_FCST.G_forecast_id||',
4743 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4744 '||CE_CASH_FCST.G_forecast_row_id||',
4745 ''Y'',
4746 nvl(fnd_global.user_id,-1),
4747 sysdate,
4748 nvl(fnd_global.user_id,-1),
4749 sysdate,
4750 nvl(fnd_global.user_id,-1),
4751 cab.forecast_column_id,
4752 src.expenditure_item_id,
4753 src.currency_code,
4754 to_number(hr_ou.ORGANIZATION_ID),
4755 src.trx_date,
4756 null,
4757 null,
4758 round(nvl(-src.amount,0)*'
4759 ||CE_CASH_FCST.G_rp_exchange_rate
4760 ||','||CE_CASH_FCST.G_precision||'),
4761 -src.amount';
4762 ELSE
4763 select_clause := '
4764 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4765 '||CE_CASH_FCST.G_forecast_id||',
4766 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4767 '||CE_CASH_FCST.G_forecast_row_id||',
4768 ''Y'',
4769 nvl(fnd_global.user_id,-1),
4770 sysdate,
4771 nvl(fnd_global.user_id,-1),
4772 sysdate,
4773 nvl(fnd_global.user_id,-1),
4774 cab.forecast_column_id,
4775 src.expenditure_item_id,
4776 src.currency_code,
4777 to_number(hr_ou.ORGANIZATION_ID),
4778 src.trx_date,
4779 null,
4780 null,
4781 round(nvl(-src.amount,0)*curr.exchange_rate'
4782 ||','||CE_CASH_FCST.G_precision||'),
4783 -src.amount';
4784 END IF;
4785
4786 from_clause := Get_From_Clause ('pa_ce_exp_reports_v');
4787 where_clause := Get_Where_Clause || '
4788 AND src.trx_date BETWEEN cab.start_date - '
4789 ||to_char(CE_CASH_FCST.G_lead_time)||
4790 ' and cab.end_date - '
4791 ||to_char(CE_CASH_FCST.G_lead_time)||
4792 Add_Where('PROJECT_ID');
4793
4794 main_query := select_clause || from_clause || where_clause;
4795
4796 Execute_Main_Query (main_query);
4797
4798 EXCEPTION
4799 WHEN OTHERS THEN
4800 cep_standard.debug('EXCEPTION:OTHERS-Build_PA_Exp_Report_Query');
4801 RAISE;
4802 END Build_PA_Exp_Report_Query;
4803
4804 /* ---------------------------------------------------------------------
4805 | PUBLIC PROCEDURE |
4806 | Build_Exp_Report_Query |
4807 | |
4808 | DESCRIPTION |
4809 | forecast amounts for released expense report from PA and |
4810 | transferred to AP. |
4811 | CALLED BY |
4812 | Populate_Cells |
4813 | REQUIRES |
4814 | main_query |
4815 | HISTORY |
4816 | 20-NOV-1998 Created BHChung |
4817 --------------------------------------------------------------------- */
4818 PROCEDURE Build_Exp_Report_Query IS
4819 from_clause VARCHAR2(500);
4820 where_clause varchar2(1500);
4821 select_clause varchar2(1500);
4822 main_query varchar2(3500) := null;
4823 BEGIN
4824 select_clause := Get_Select_Clause;
4825 from_clause := Get_From_Clause ('ce_ap_fc_exp_reports_v');
4826 where_clause := Get_Where_Clause || '
4827 AND src.trx_date BETWEEN cab.start_date - '
4828 ||to_char(CE_CASH_FCST.G_lead_time)||
4829 ' and cab.end_date - '
4830 ||to_char(CE_CASH_FCST.G_lead_time)||
4831 ' AND src.source <> ''NonValidatedWebExpense'' '||
4832 Add_Where('PROJECT_ID') || Add_Where('INCLUDE_HOLD_FLAG');
4833
4834 main_query := select_clause || from_clause || where_clause;
4835
4836 Execute_Main_Query (main_query);
4837
4838 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL) THEN
4839 Build_PA_Exp_Report_Query;
4840 END IF;
4841 EXCEPTION
4842 WHEN OTHERS THEN
4843 cep_standard.debug('EXCEPTION:OTHERS-Build_Exp_Report_Query');
4844 RAISE;
4845 END Build_Exp_Report_Query;
4846
4847
4848 /* ---------------------------------------------------------------------
4849 | PUBLIC PROCEDURE |
4850 | Build_PA_Trx_Query |
4851 | |
4852 | DESCRIPTION |
4853 | This procedure builds the query to calculate the forecast |
4854 | amounts for Projects Transactions. |
4855 | CALLED BY |
4856 | Populate_Cells |
4857 | REQUIRES |
4858 | main_query |
4859 | HISTORY |
4860 | 23-NOV-1998 Created BHChung |
4861 --------------------------------------------------------------------- */
4862 PROCEDURE Build_PA_Trx_Query IS
4863 from_clause VARCHAR2(500);
4864 where_clause varchar2(1500);
4865 select_clause varchar2(1500);
4866 main_query varchar2(3500) := null;
4867 BEGIN
4868
4869 select_clause := Get_Select_Clause;
4870 from_clause := Get_From_Clause ('pa_ce_transactions_v');
4871 where_clause := Get_Where_Clause || '
4872 AND src.trx_date BETWEEN cab.start_date - '
4873 ||to_char(CE_CASH_FCST.G_lead_time)||
4874 ' and cab.end_date - '
4875 ||to_char(CE_CASH_FCST.G_lead_time)||
4876 Add_Where('PROJECT_ID') || Add_Where('TYPE');
4877
4878 main_query := select_clause || from_clause || where_clause;
4879
4880 Execute_Main_Query (main_query);
4881
4882 EXCEPTION
4883 WHEN OTHERS THEN
4884 cep_standard.debug('EXCEPTION:OTHERS-Build_PA_Trx_Query');
4885 RAISE;
4886 END Build_PA_Trx_Query;
4887
4888 /* ---------------------------------------------------------------------
4889 | PUBLIC PROCEDURE |
4890 | Build_PA_Billing_Query |
4891 | |
4892 | DESCRIPTION |
4893 | This procedure builds the query to calculate the forecast |
4894 | amounts for unreleased billing events that have an invoicing |
4895 | impact. |
4896 | CALLED BY |
4897 | Populate_Cells |
4898 | REQUIRES |
4899 | main_query |
4900 | HISTORY |
4901 | 02-DEC-1998 Created BHChung |
4902 --------------------------------------------------------------------- */
4903 PROCEDURE Build_PA_Billing_Query IS
4904 from_clause VARCHAR2(500);
4905 where_clause varchar2(1500);
4906 select_clause varchar2(1500);
4907 main_query varchar2(3500) := null;
4908 BEGIN
4909
4910 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
4911 select_clause := '
4912 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4913 '||CE_CASH_FCST.G_forecast_id||',
4914 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4915 '||CE_CASH_FCST.G_forecast_row_id||',
4916 ''Y'',
4917 nvl(fnd_global.user_id,-1),
4918 sysdate,
4919 nvl(fnd_global.user_id,-1),
4920 sysdate,
4921 nvl(fnd_global.user_id,-1),
4922 cab.forecast_column_id,
4923 src.project_id || ''X'' || src.event_num,
4924 src.currency_code,
4925 to_number(hr_ou.ORGANIZATION_ID),
4926 src.trx_date,
4927 null,
4928 null,
4929 round(nvl(src.amount,0)*'
4930 ||CE_CASH_FCST.G_rp_exchange_rate
4931 ||','||CE_CASH_FCST.G_precision||'),
4932 src.amount';
4933 ELSE
4934 select_clause := '
4935 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
4936 '||CE_CASH_FCST.G_forecast_id||',
4937 '||CE_CASH_FCST.G_rp_forecast_header_id||',
4938 '||CE_CASH_FCST.G_forecast_row_id||',
4939 ''Y'',
4940 nvl(fnd_global.user_id,-1),
4941 sysdate,
4942 nvl(fnd_global.user_id,-1),
4943 sysdate,
4944 nvl(fnd_global.user_id,-1),
4945 cab.forecast_column_id,
4946 src.project_id || ''X'' || src.event_num,
4947 src.currency_code,
4948 to_number(hr_ou.ORGANIZATION_ID),
4949 src.trx_date,
4950 null,
4951 null,
4952 round(nvl(src.amount,0)*curr.exchange_rate'
4953 ||','||CE_CASH_FCST.G_precision||'),
4954 src.amount';
4955 END IF;
4956
4957 from_clause := Get_From_Clause ('pa_ce_billing_events_v');
4958 where_clause := Get_Where_Clause || '
4959 AND src.trx_date BETWEEN cab.start_date - '
4960 ||to_char(CE_CASH_FCST.G_lead_time)||
4961 ' and cab.end_date - '
4962 ||to_char(CE_CASH_FCST.G_lead_time)||
4963 Add_Where('PROJECT_ID');
4964
4965 main_query := select_clause || from_clause || where_clause;
4966 Execute_Main_Query (main_query);
4967
4968 EXCEPTION
4969 WHEN OTHERS THEN
4970 cep_standard.debug('EXCEPTION:OTHERS-Build_PA_Billing_Query');
4971 RAISE;
4972 END Build_PA_Billing_Query;
4973
4974
4975 /* ---------------------------------------------------------------------
4976 | PUBLIC PROCEDURE |
4977 | Build_PA_Budget_Query |
4978 | |
4979 | DESCRIPTION |
4980 | This procedure builds the query to calculate the forecast |
4981 | amounts for inflow/outflow budgets entered for project. |
4982 | CALLED BY |
4983 | Populate_Cells |
4984 | REQUIRES |
4985 | main_query |
4986 | HISTORY |
4987 | 09-JUN-2003 Sunil Poonen Created |
4988 | 19-JUN-2012 Varun Netan Bug 14164001 |
4989 --------------------------------------------------------------------- */
4990 PROCEDURE Build_PA_Budget_Query IS
4991 l_cost_amount NUMBER;
4992 l_revenue_amount NUMBER;
4993 l_org_id NUMBER;
4994 l_legal_entity_id NUMBER;
4995 l_dummy NUMBER;
4996 l_rate NUMBER;
4997
4998 l_reference_id NUMBER;
4999 l_start_date DATE;
5000 l_end_date DATE;
5001
5002 CURSOR C_period IS
5003 SELECT start_date, end_date, forecast_column_id
5004 FROM ce_forecast_ext_temp
5005 WHERE context_value = 'A'
5006 AND forecast_request_id = CE_CASH_FCST.G_forecast_id
5007 AND conversion_rate = CE_CASH_FCST.G_forecast_row_id;
5008
5009 CURSOR C_sob(p_org_id NUMBER) IS
5010 SELECT 1
5011 FROM CE_FORECAST_ORGS_V
5012 WHERE set_of_books_id = CE_CASH_FCST.G_set_of_books_id
5013 AND org_id = p_org_id;
5014
5015 CURSOR C_rate(p_currency_code VARCHAR2) IS
5016 SELECT exchange_rate
5017 FROM ce_currency_rates_temp
5018 WHERE forecast_request_id = CE_CASH_FCST.G_forecast_id
5019 AND to_currency = CE_CASH_FCST.G_rp_forecast_currency
5020 AND currency_code = p_currency_code;
5021
5022 CURSOR C_budgets(p_start_date DATE, p_end_date DATE) IS
5023 SELECT trim(resource_assignment_id||'X'||to_char(start_date,'DD-MON-YY')) reference_id, --bug 7345336
5024 --resource_assignment_id||'X'||trunc(start_date) reference_id,
5025 projfunc_currency_code,
5026 nvl(raw_cost,0)/(trunc(end_date)-trunc(start_date)+1) per_day_raw_cost,
5027 nvl(revenue,0)/(trunc(end_date)-trunc(start_date)+1) per_day_revenue,
5028 start_date,
5029 end_date
5030 FROM pa_ce_integration_budgets_v
5031 WHERE decode(CE_CASH_FCST.G_budget_version,
5032 'C',current_flag,
5033 'O',current_original_flag) = 'Y'
5034 AND end_date >= p_start_date
5035 AND start_date <= p_end_date
5036 AND project_id = CE_CASH_FCST.G_rp_project_id
5037 AND budget_type_code = CE_CASH_FCST.G_budget_type;
5038
5039 BEGIN
5040 cep_standard.debug('>>CE_CSH_FCST_POP.Build_PA_Budget_Query');
5041 -- initialize min_col and max_col
5042 populate_aging_buckets;
5043
5044 cep_standard.debug('CE_CSH_FCST_POP.G_rp_project_id='||CE_CASH_FCST.G_rp_project_id
5045 ||', CE_CASH_FCST.G_rp_src_curr_type='||CE_CASH_FCST.G_rp_src_curr_type
5046 ||', CE_CASH_FCST.G_rp_src_currency='||CE_CASH_FCST.G_rp_src_currency
5047 ||', CE_CASH_FCST.G_org_id='||CE_CASH_FCST.G_org_id
5048 ||', CE_CASH_FCST.G_rp_exchange_rate='||CE_CASH_FCST.G_rp_exchange_rate
5049 ||', CE_CASH_FCST.G_rp_amount_threshold='||CE_CASH_FCST.G_rp_amount_threshold
5050 ||', CE_CASH_FCST.G_overdue_column_id ='||CE_CASH_FCST.G_overdue_column_id );
5051
5052 SELECT org_id
5053 INTO l_org_id
5054 FROM pa_projects_all
5055 WHERE project_id = CE_CASH_FCST.G_rp_project_id;
5056
5057 SELECT to_number(ORGANIZATION_ID)
5058 INTO l_legal_entity_id
5059 FROM hr_operating_units
5060 WHERE organization_id = l_org_id;
5061
5062 cep_standard.debug('l_org_id='||l_org_id||'l_legal_entity_id,='||l_legal_entity_id);
5063
5064 FOR C_rec IN C_period
5065 LOOP
5066 cep_standard.debug('forecast_column_id='||C_rec.forecast_column_id
5067 ||', C_rec.start_date='||to_char(C_rec.start_date,'dd-mon-yyyy hh24:mi:ss')
5068 ||', C_rec.end_date='||to_char(C_rec.end_date,'dd-mon-yyyy hh24:mi:ss'));
5069
5070 FOR C_budget_rec in C_budgets(C_rec.start_date, C_rec.end_date)
5071 LOOP
5072 cep_standard.debug('C_budget_rec.reference_id='||C_budget_rec.reference_id
5073 ||', C_budget_rec.projfunc_currency_code='||C_budget_rec.projfunc_currency_code);
5074 cep_standard.debug('C_budget_rec.per_day_raw_cost='||C_budget_rec.per_day_raw_cost
5075 ||', C_budget_rec.per_day_revenue='||C_budget_rec.per_day_revenue);
5076 cep_standard.debug(', C_budget_rec.start_date='||C_budget_rec.start_date
5077 ||', C_budget_rec.end_date='||C_budget_rec.end_date);
5078
5079
5080 IF (C_budget_rec.start_date < C_rec.start_date)
5081 THEN
5082 IF (C_budget_rec.end_date < C_rec.end_date)
5083 THEN
5084 l_start_date := trunc(C_rec.start_date);
5085 l_end_date := trunc(C_budget_rec.end_date);
5086 ELSE
5087 l_start_date := trunc(C_rec.start_date);
5088 l_end_date := trunc(C_rec.end_date);
5089 END IF;
5090 ELSE
5091 IF (C_budget_rec.end_date > C_rec.end_date)
5092 THEN
5093 l_start_date := trunc(C_budget_rec.start_date);
5094 l_end_date := trunc(C_rec.end_date);
5095 ELSE
5096 l_start_date := trunc(C_budget_rec.start_date);
5097 l_end_date := trunc(C_budget_rec.end_date);
5098 END IF;
5099 END IF;
5100 cep_standard.debug('l_start_date='||l_start_date||', l_end_date='||l_end_date);
5101
5102 l_cost_amount := C_budget_rec.per_day_raw_cost * (l_end_date - l_start_date + 1);
5103 l_revenue_amount := C_budget_rec.per_day_revenue * (l_end_date - l_start_date + 1);
5104
5105 cep_standard.debug('l_cost_amount='||l_cost_amount);
5106 cep_standard.debug('l_revenue_amount='||l_revenue_amount);
5107
5108 IF(CE_CASH_FCST.G_rp_src_curr_type in ('E','F') AND
5109 C_budget_rec.projfunc_currency_code <> CE_CASH_FCST.G_rp_src_currency)
5110 THEN
5111 cep_standard.debug('RS001: revenue/cost set to 0');
5112 l_revenue_amount := 0;
5113 l_cost_amount := 0;
5114 END IF;
5115
5116 IF( (CE_CASH_FCST.G_org_id <> -1 AND CE_CASH_FCST.G_org_id <> -99) AND
5117 (nvl(l_org_id,CE_CASH_FCST.G_org_id) <> CE_CASH_FCST.G_org_id) )
5118 THEN
5119 cep_standard.debug('RS002: revenue/cost set to 0');
5120 l_revenue_amount := 0;
5121 l_cost_amount := 0;
5122 END IF;
5123
5124 IF( CE_CASH_FCST.G_set_of_books_id IS NOT NULL AND
5125 CE_CASH_FCST.G_set_of_books_id <> -1)
5126 THEN
5127 cep_standard.debug('RS002: revenue/cost set per set of books');
5128 OPEN C_sob(l_org_id);
5129 FETCH C_sob INTO l_dummy;
5130 IF C_sob%NOTFOUND
5131 THEN
5132 cep_standard.debug('RS002: C_sob NOTFOUND');
5133 CLOSE C_sob;
5134 l_revenue_amount := 0;
5135 l_cost_amount := 0;
5136 END IF;
5137 CLOSE C_sob;
5138 END IF;
5139
5140 IF( CE_CASH_FCST.G_rp_exchange_type IS NULL OR
5141 CE_CASH_FCST.G_rp_exchange_type <> 'User')
5142 THEN
5143 OPEN C_rate(C_budget_rec.projfunc_currency_code);
5144 FETCH C_rate INTO l_rate;
5145 IF C_rate%NOTFOUND
5146 THEN
5147 cep_standard.debug('C_rate NOTFOUND');
5148 l_rate := 1;
5149 END IF;
5150 CLOSE C_rate;
5151 ELSIF( CE_CASH_FCST.G_rp_exchange_type = 'User')
5152 THEN
5153 l_rate := CE_CASH_FCST.G_rp_exchange_rate;
5154 ELSE
5155 cep_standard.debug('No user rate');
5156 l_rate := 1;
5157 END IF;
5158 cep_standard.debug('l_rate='||l_rate);
5159
5160 IF(CE_CASH_FCST.G_rp_amount_threshold IS NOT NULL)
5161 THEN
5162 IF (CE_CASH_FCST.G_trx_type = 'PAI')
5163 THEN
5164 IF (abs(l_revenue_amount) <= CE_CASH_FCST.G_rp_amount_threshold)
5165 THEN
5166 cep_standard.debug('RS003a:revenue below threshold');
5167 l_revenue_amount := 0;
5168 END IF;
5169 ELSE
5170 IF (abs(l_cost_amount) <= CE_CASH_FCST.G_rp_amount_threshold)
5171 THEN
5172 cep_standard.debug('RS003b:cost below threshold');
5173 l_cost_amount := 0;
5174 END IF;
5175 END IF;
5176 END IF;
5177
5178 IF( C_rec.forecast_column_id = CE_CASH_FCST.G_overdue_column_id
5179 AND CE_CASH_FCST.G_invalid_overdue_row)
5180 THEN
5181 cep_standard.debug('RS004: revenue/cost column overdue');
5182 l_revenue_amount := 0;
5183 l_cost_amount := 0;
5184 END IF;
5185
5186 IF (CE_CASH_FCST.G_trx_type = 'PAI')
5187 THEN
5188 IF (l_revenue_amount <> 0)
5189 THEN
5190 cep_standard.debug('inserting revenue_amount='||l_revenue_amount*l_rate);
5191 Insert_Fcast_Cell(
5192 p_reference_id => C_budget_rec.reference_id,
5193 p_currency_code => C_budget_rec.projfunc_currency_code,
5194 p_org_id => l_legal_entity_id,
5195 p_trx_date => null,
5196 p_bank_account_id => null,
5197 p_forecast_amount => l_revenue_amount*l_rate,
5198 p_trx_amount => l_revenue_amount,
5199 p_forecast_column_id => C_rec.forecast_column_id);
5200 END IF;
5201 ELSE
5202 IF (l_cost_amount <> 0)
5203 THEN
5204 cep_standard.debug('inserting l_cost_amount='||to_char(-(l_cost_amount*l_rate)));
5205 Insert_Fcast_Cell(
5206 p_reference_id => C_budget_rec.reference_id,
5207 p_currency_code => C_budget_rec.projfunc_currency_code,
5208 p_org_id => l_legal_entity_id,
5209 p_trx_date => null,
5210 p_bank_account_id => null,
5211 p_forecast_amount => -(l_cost_amount*l_rate),
5212 p_trx_amount => -l_cost_amount,
5213 p_forecast_column_id => C_rec.forecast_column_id);
5214 END IF;
5215 END IF;
5216 END LOOP;
5217 END LOOP;
5218
5219 clear_aging_buckets;
5220 zero_fill_cells;
5221 cep_standard.debug('>>CE_CSH_FCST_POP.Build_PA_Budget_Query');
5222
5223 EXCEPTION
5224 WHEN OTHERS THEN
5225 cep_standard.debug('EXCEPTION:OTHERS-Build_PA_Budget_Query');
5226 RAISE;
5227 END Build_PA_Budget_Query;
5228
5229
5230 /* ---------------------------------------------------------------------
5231 | PUBLIC PROCEDURE |
5232 | Build_PA_Billing_Query |
5233 | |
5234 | DESCRIPTION |
5235 | This procedure builds the query to calculate the forecast |
5236 | amounts for unreleased billing events that have an invoicing |
5237 | impact. |
5238 | CALLED BY |
5239 | Populate_Cells |
5240 | REQUIRES |
5241 | main_query |
5242 | HISTORY |
5243 | 02-DEC-1998 Created BHChung |
5244 --------------------------------------------------------------------- */
5245 PROCEDURE Build_Treasury_Query IS
5246 from_clause VARCHAR2(500);
5247 where_clause varchar2(1500);
5248 select_clause varchar2(1500);
5249 main_query varchar2(3500) := null;
5250 BEGIN
5251 IF(CE_CASH_FCST.G_rp_exchange_type = 'User')THEN
5252 select_clause := '
5253 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
5254 '||CE_CASH_FCST.G_forecast_id||',
5255 '||CE_CASH_FCST.G_rp_forecast_header_id||',
5256 '||CE_CASH_FCST.G_forecast_row_id||',
5257 ''Y'',
5258 nvl(fnd_global.user_id,-1),
5259 sysdate,
5260 nvl(fnd_global.user_id,-1),
5261 sysdate,
5262 nvl(fnd_global.user_id,-1),
5263 cab.forecast_column_id,
5264 src.reference_id,
5265 src.currency_code,
5266 src.org_id,
5267 src.trx_date +'
5268 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
5269 src.bank_account_id,
5270 ccid.asset_code_combination_id,
5271 round(nvl(src.amount,0)*'||CE_CASH_FCST.G_rp_exchange_rate
5272 ||','||CE_CASH_FCST.G_precision||'),
5273 src.amount';
5274 ELSE
5275 select_clause := '
5276 SELECT CE_FORECAST_TRX_CELLS_S.nextval,
5277 '||CE_CASH_FCST.G_forecast_id||',
5278 '||CE_CASH_FCST.G_rp_forecast_header_id||',
5279 '||CE_CASH_FCST.G_forecast_row_id||',
5280 ''Y'',
5281 nvl(fnd_global.user_id,-1),
5282 sysdate,
5283 nvl(fnd_global.user_id,-1),
5284 sysdate,
5285 nvl(fnd_global.user_id,-1),
5286 cab.forecast_column_id,
5287 src.reference_id,
5288 src.currency_code,
5289 src.org_id,
5290 src.trx_date +'
5291 ||to_char(CE_CASH_FCST.G_lead_time)|| ',
5292 src.bank_account_id,
5293 ccid.asset_code_combination_id,
5294 round(nvl(src.amount,0)*curr.exchange_rate'
5295 ||','||CE_CASH_FCST.G_precision||'),
5296 src.amount';
5297 END IF;
5298 from_clause := Get_From_Clause('ce_xtr_cashflows_v');
5299 where_clause := Get_Where_Clause || '
5300 AND src.trx_date BETWEEN cab.start_date and cab.end_date '||
5301 Add_Where('XTR_TYPE') || Add_Where('XTR_BANK_ACCOUNT') || Add_Where('EXCLUDE_INDIC_EXP');
5302
5303 IF (CE_CASH_FCST.G_trx_type = 'XTO') THEN
5304 where_clause := where_clause || ' AND src.amount < 0';
5305 ELSE
5306 where_clause := where_clause || ' AND src.amount > 0';
5307 END IF;
5308
5309 IF CE_CASH_FCST.G_overdue_transactions = 'INCLUDE' THEN
5310 where_clause := where_clause ||
5311 ' AND decode(cab.forecast_column_id,'||CE_CASH_FCST.G_overdue_column_id
5312 ||',src.reconciled_reference,null) is null';
5313 END IF;
5314 main_query := select_clause || from_clause || where_clause;
5315 Execute_Main_Query (main_query);
5316
5317 EXCEPTION
5318 WHEN OTHERS THEN
5319 cep_standard.debug('EXCEPTION:OTHERS-Build_Treasury_Query');
5320 RAISE;
5321 END Build_Treasury_Query;
5322
5323 /* ---------------------------------------------------------------------
5324 | PUBLIC PROCEDURE |
5325 | Build_Remote_Query |
5326 | |
5327 | DESCRIPTION |
5328 | This procedure builds the query to calculate the forecast |
5329 | amounts from the remote transactions |
5330 | CALLED BY |
5331 | Populate_Cells |
5332 | REQUIRES |
5333 | main_query |
5334 | HISTORY |
5335 | 12-JUL-1996 Created Bidemi Carrol |
5336 --------------------------------------------------------------------- */
5337 PROCEDURE Build_Remote_Query IS
5338 db_link varchar2(128);
5339 main_query VARCHAR2(6000) := null;
5340 cursor_id INTEGER;
5341 exec_id INTEGER;
5342 error_msg VARCHAR2(2000);
5343 BEGIN
5344 cep_standard.debug('>>CE_CSH_FCAST_POP.Build_Remote_Query');
5345 --
5346 -- Get view and db information from the external source type
5347 --
5348 cep_standard.debug('Get database information for database: '||CE_CASH_FCST.G_external_source_type);
5349 BEGIN
5350
5351 SELECT external_source_view, db_link_name
5352 INTO source_view, db_link
5353 FROM ce_forecast_ext_views
5354 WHERE external_source_type = CE_CASH_FCST.G_external_source_type;
5355
5356 EXCEPTION
5357 WHEN NO_DATA_FOUND THEN
5358 cep_standard.debug('EXCEPTION:Build_Remote_Query - View def not found');
5359 FND_MESSAGE.set_name('CE','CE_FC_EXT_SOURCE_UNDEFINED');
5360 FND_MESSAGE.set_token('EXT_TYPE', CE_CASH_FCST.G_external_source_type);
5361 error_msg := FND_MESSAGE.get;
5362 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5363 CE_CASH_FCST.G_forecast_row_id, 'CE_FC_EXT_SOURCE_UNDEFINED', error_msg);
5364 RETURN;
5365 END;
5366
5367 populate_aging_buckets;
5368
5369 IF( db_link IS NOT NULL )THEN
5370 db_link := '@'||db_link;
5371 END IF;
5372 cep_standard.debug(' source_view = '||source_view||', db_link = '||db_link);
5373
5374 main_query := '
5375 declare
5376 counter NUMBER;
5377 error_code NUMBER;
5378 error_msg VARCHAR2(2000);
5379 aging_table CE_FORECAST_REMOTE_SOURCES.AgingTab'||db_link||';
5380 conversion_table CE_FORECAST_REMOTE_SOURCES.ConversionTab'||db_link||';
5381 amount_table CE_FORECAST_REMOTE_SOURCES.AmountTab'||db_link||';
5382
5383 CURSOR conversion_cursor IS SELECT currency_code, exchange_rate
5384 FROM ce_currency_rates_temp
5385 WHERE forecast_request_id = CE_CASH_FCST.G_forecast_id; ';
5386
5387 IF( db_link IS NOT NULL )THEN
5388 main_query := main_query ||'
5389 CURSOR aging_cursor IS SELECT forecast_column_id, start_date, end_date
5390 FROM ce_forecast_ext_temp
5391 WHERE context_value = ''A'' and
5392 forecast_request_id = CE_CASH_FCST.G_forecast_id and
5393 conversion_rate = CE_CASH_FCST.G_forecast_row_id; ';
5394 END IF;
5395
5396 main_query := main_query ||'
5397 begin ';
5398
5399 IF( db_link IS NOT NULL )THEN
5400 main_query := main_query ||'
5401 --
5402 -- Store aging bucket information into aging table
5403 --
5404 counter := 1;
5405 open aging_cursor;
5406 cep_standard.debug(''Building aging information'');
5407 LOOP
5408 FETCH aging_cursor INTO aging_table(counter).column_id,
5409 aging_table(counter).start_date,
5410 aging_table(counter).end_date;
5411 EXIT WHEN aging_cursor%NOTFOUND or aging_cursor%NOTFOUND IS NULL;
5412 counter := counter + 1;
5413 END LOOP;
5414 cep_standard.debug(''counter for aging have '' || to_char(counter));
5415 close aging_cursor;
5416 aging_table.delete(counter);
5417 cep_standard.debug(''Done building aging information''); ';
5418 END IF;
5419
5420 main_query := main_query ||'
5421
5422 --
5423 -- Store conversion rate information into conversion table
5424 --
5425 counter := 1;
5426 open conversion_cursor;
5427 cep_standard.debug(''Building conversion information'');
5428 LOOP
5429 FETCH conversion_cursor INTO conversion_table(counter).from_currency_code,
5430 conversion_table(counter).conversion_rate;
5431 EXIT WHEN conversion_cursor%NOTFOUND or conversion_cursor%NOTFOUND IS NULL;
5432 counter := counter + 1;
5433 END LOOP;
5434 close conversion_cursor;
5435 conversion_table.delete(counter);
5436 cep_standard.debug(''Done building conversion information'');
5437
5438 --
5439 -- Built query to be executed in the remote/local database
5440 --
5441 error_code := CE_FORECAST_REMOTE_SOURCES.populate_remote_amounts';
5442
5443 --
5444 -- Append db_link if applicable
5445 --
5446 IF( db_link IS NOT NULL) THEN
5447 main_query := main_query||db_link;
5448 END IF;
5449
5450 main_query := main_query ||'(
5451 CE_CASH_FCST.G_forecast_id,
5452 '''||source_view||''',
5453 '''||db_link||''',
5454 CE_CASH_FCST.G_forecast_row_id,
5455 aging_table,
5456 conversion_table,
5457 CE_CASH_FCST.G_rp_forecast_currency,
5458 CE_CASH_FCST.G_rp_exchange_date,
5459 CE_CASH_FCST.G_rp_exchange_type,
5460 CE_CASH_FCST.G_rp_exchange_rate,
5461 CE_CASH_FCST.G_rp_src_curr_type,
5462 CE_CASH_FCST.G_rp_src_currency,
5463 CE_CASH_FCST.G_rp_amount_threshold,
5464 CE_CASH_FCST.G_lead_time,
5465 CE_CASH_FCST.G_criteria1,
5466 CE_CASH_FCST.G_criteria2,
5467 CE_CASH_FCST.G_criteria3,
5468 CE_CASH_FCST.G_criteria4,
5469 CE_CASH_FCST.G_criteria5,
5470 CE_CASH_FCST.G_criteria6,
5471 CE_CASH_FCST.G_criteria7,
5472 CE_CASH_FCST.G_criteria8,
5473 CE_CASH_FCST.G_criteria9,
5474 CE_CASH_FCST.G_criteria10,
5475 CE_CASH_FCST.G_criteria11,
5476 CE_CASH_FCST.G_criteria12,
5477 CE_CASH_FCST.G_criteria13,
5478 CE_CASH_FCST.G_criteria14,
5479 CE_CASH_FCST.G_criteria15,
5480 amount_table);
5481 IF( error_code = 0 )THEN
5482 --
5483 -- For the amount calculated from the remote database, insert it to
5484 -- the cell table
5485 --
5486 FOR i IN 1 .. amount_table.count LOOP
5487 IF (CE_CASH_FCST.G_trx_type = ''OII'') THEN
5488 cep_standard.debug(''insert column_id = ''||to_char(amount_table(i).forecast_column_id)||'' with amount ''||
5489 to_char(amount_table(i).forecast_amount));
5490 CE_CSH_FCST_POP.Insert_Fcast_Cell(null, amount_table(i).currency_code, null, amount_table(i).trx_date, amount_table(i).bank_account_id, amount_table(i).forecast_amount, amount_table(i).trx_amount, amount_table(i).forecast_column_id);
5491 ELSE
5492 cep_standard.debug(''insert column_id = ''||to_char(amount_table(i).forecast_column_id)||'' with amount ''||
5493 to_char(-(amount_table(i).forecast_amount)));
5494 CE_CSH_FCST_POP.Insert_Fcast_Cell(null, amount_table(i).currency_code, null, amount_table(i).trx_date, amount_table(i).bank_account_id, -(amount_table(i).forecast_amount), -(amount_table(i).trx_amount), amount_table(i).forecast_column_id);
5495 END IF;
5496 END LOOP;
5497 CE_CSH_FCST_POP.Zero_Fill_Cells;
5498 ELSIF( error_code = -1 )THEN
5499 cep_standard.debug(''Remote error: missing view'');
5500 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_MISSING_VIEW_EXPT'');
5501 error_msg := FND_MESSAGE.get;
5502 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5503 CE_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_MISSING_VIEW_EXPT'', error_msg);
5504 return;
5505 ELSIF( error_code = -2 )THEN
5506 cep_standard.debug(''Remote error: invalid view'');
5507 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_INVALID_VIEW_EXPT'');
5508 error_msg := FND_MESSAGE.get;
5509 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5510 CE_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_INVALID_VIEW_EXPT'', error_msg);
5511 return;
5512 ELSIF( error_code = -3 )THEN
5513 cep_standard.debug(''Remote error: others'');
5514 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_EXCEPTION'');
5515 error_msg := FND_MESSAGE.get;
5516 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5517 CE_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_EXCEPTION'', error_msg);
5518 return;
5519 END IF;
5520 end; ';
5521
5522 BEGIN
5523 cursor_id := DBMS_SQL.open_cursor;
5524 DBMS_SQL.parse(cursor_id, main_query, DBMS_SQL.v7);
5525 exec_id := DBMS_SQL.execute(cursor_id);
5526 DBMS_SQL.close_cursor(cursor_id);
5527 EXCEPTION
5528 WHEN OTHERS THEN
5529 clear_aging_buckets;
5530 IF DBMS_SQL.is_open(cursor_id) THEN
5531 DBMS_SQL.close_cursor(cursor_id);
5532 END IF;
5533 FND_MESSAGE.set_name('CE', 'CE_FC_RMT_DB_EXCEPTION');
5534 error_msg := FND_MESSAGE.get;
5535 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5536 CE_CASH_FCST.G_forecast_row_id, 'CE_FC_RMT_DB_EXCEPTION', error_msg);
5537 return;
5538 END;
5539
5540 clear_aging_buckets;
5541
5542 cep_standard.debug('<<CE_CSH_FCST_POP.Build_Remote_Query');
5543 EXCEPTION
5544 WHEN OTHERS THEN
5545 clear_aging_buckets;
5546 cep_standard.debug('EXCEPTION:Build_Remote_Query');
5547 FND_MESSAGE.set_name('CE', 'CE_FC_RMT_EXCEPTION');
5548 error_msg := FND_MESSAGE.get;
5549 CE_FORECAST_ERRORS_PKG.insert_row(CE_CASH_FCST.G_forecast_id, CE_CASH_FCST.G_rp_forecast_header_id,
5550 CE_CASH_FCST.G_forecast_row_id, 'CE_FC_RMT_EXCEPTION', error_msg);
5551 END Build_Remote_Query;
5552
5553
5554
5555
5556 /* ---------------------------------------------------------------------
5557 | PUBLIC PROCEDURE |
5558 | Populate_Cells |
5559 | |
5560 | DESCRIPTION |
5561 | This procedure calls the appropriate build query procedure for |
5562 | each transaction type. |
5563 | |
5564 | CALLED BY |
5565 | |
5566 | REQUIRES |
5567 | |
5568 | HISTORY |
5569 | 12-JUL-1996 Created Bidemi Carrol |
5570 --------------------------------------------------------------------- */
5571 PROCEDURE Populate_Cells IS
5572 error_msg VARCHAR2(2000);
5573
5574 BEGIN
5575 --
5576 -- Based on the source_trx_type call the different procedures
5577 -- to build the queries dynamically
5578 --
5579 cep_standard.debug('>>CE_CSH_FCST_POP.Populate_Cells');
5580 IF (CE_CASH_FCST.G_trx_type = 'API')
5581 THEN
5582 Build_AP_Invoice_Query;
5583 ELSIF (CE_CASH_FCST.G_trx_type = 'APP' AND CE_CASH_FCST.G_rp_project_id IS NULL)
5584 THEN
5585 Build_AP_Pay_Query;
5586 ELSIF (CE_CASH_FCST.G_trx_type = 'ARI')
5587 THEN
5588 Build_AR_Invoice_Query;
5589 ELSIF (CE_CASH_FCST.G_trx_type = 'ARR' AND CE_CASH_FCST.G_rp_project_id IS NULL)
5590 THEN
5591 Build_AR_Receipt_Query;
5592 ELSIF (CE_CASH_FCST.G_trx_type = 'GLB')
5593 THEN
5594 Build_GL_Budget_Query;
5595 ELSIF (CE_CASH_FCST.G_trx_type = 'GLE')
5596 THEN
5597 Build_GL_Encumb_Query;
5598 ELSIF (CE_CASH_FCST.G_trx_type = 'GLA')
5599 THEN
5600 Build_GL_Actuals_Query;
5601 ELSIF (CE_CASH_FCST.G_trx_type = 'OII')
5602 THEN
5603 Build_Remote_Query;
5604 ELSIF (CE_CASH_FCST.G_trx_type = 'OIO')
5605 THEN
5606 Build_Remote_Query;
5607 ELSIF (CE_CASH_FCST.G_trx_type = 'PAY' AND CE_CASH_FCST.G_rp_project_id IS NULL)
5608 THEN
5609 Build_Pay_Exp_Query;
5610 ELSIF (CE_CASH_FCST.G_trx_type = 'POP')
5611 THEN
5612 Build_PO_Orders_Query;
5613 ELSIF (CE_CASH_FCST.G_trx_type = 'POR')
5614 THEN
5615 Build_PO_Req_Query;
5616 ELSIF (CE_CASH_FCST.G_trx_type = 'ASF' AND CE_CASH_FCST.G_rp_project_id IS NULL)
5617 THEN
5618 Build_Sales_Fcst_Query;
5619 ELSIF (CE_CASH_FCST.G_trx_type = 'APX')
5620 THEN
5621 Build_Exp_Report_Query;
5622 ELSIF (CE_CASH_FCST.G_trx_type = 'PAT')
5623 THEN
5624 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL)
5625 THEN
5626 Build_PA_Trx_Query;
5627 ELSE
5628 UPDATE ce_forecasts
5629 SET error_status = 'X'
5630 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
5631
5632 FND_MESSAGE.set_name ('CE','CE_FC_NO_PROJECT_RANGE');
5633 error_msg := FND_MESSAGE.GET;
5634 CE_FORECAST_ERRORS_PKG.insert_row(
5635 CE_CASH_FCST.G_forecast_id,
5636 CE_CASH_FCST.G_rp_forecast_header_id,
5637 CE_CASH_FCST.G_forecast_row_id,
5638 'CE_FC_NO_PROJECT_RANGE',
5639 error_msg);
5640 END IF;
5641
5642 ELSIF (CE_CASH_FCST.G_trx_type in ('PAI', 'PAO'))
5643 THEN
5644 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL)
5645 THEN
5646 Build_PA_Budget_Query;
5647 ELSE
5648 UPDATE ce_forecasts
5649 SET error_status = 'X'
5650 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
5651
5652 FND_MESSAGE.set_name ('CE','CE_FC_NO_PROJECT_RANGE');
5653 error_msg := FND_MESSAGE.GET;
5654 CE_FORECAST_ERRORS_PKG.insert_row(
5655 CE_CASH_FCST.G_forecast_id,
5656 CE_CASH_FCST.G_rp_forecast_header_id,
5657 CE_CASH_FCST.G_forecast_row_id,
5658 'CE_FC_NO_PROJECT_RANGE',
5659 error_msg);
5660 END IF;
5661
5662 ELSIF (CE_CASH_FCST.G_trx_type = 'PAB')
5663 THEN
5664 IF (CE_CASH_FCST.G_rp_project_id IS NOT NULL)
5665 THEN
5666 Build_PA_Billing_Query;
5667 ELSE
5668 UPDATE ce_forecasts
5669 SET error_status = 'X'
5670 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
5671
5672 FND_MESSAGE.set_name ('CE','CE_FC_NO_PROJECT_RANGE');
5673 error_msg := FND_MESSAGE.GET;
5674 CE_FORECAST_ERRORS_PKG.insert_row(
5675 CE_CASH_FCST.G_forecast_id,
5676 CE_CASH_FCST.G_rp_forecast_header_id,
5677 CE_CASH_FCST.G_forecast_row_id,
5678 'CE_FC_NO_PROJECT_RANGE',
5679 error_msg);
5680 END IF;
5681
5682 ELSIF (CE_CASH_FCST.G_trx_type = 'OEO')
5683 THEN
5684 Build_Sales_Order_Query;
5685 ELSIF (CE_CASH_FCST.G_trx_type in ('XTR','XTI','XTO') AND
5686 CE_CASH_FCST.G_rp_project_id IS NULL)
5687 THEN
5688 Build_Treasury_Query;
5689 ELSIF (CE_CASH_FCST.G_trx_type = 'UDI')
5690 THEN
5691 Zero_Fill_Cells;
5692 ELSIF (CE_CASH_FCST.G_trx_type = 'UDO')
5693 THEN
5694 Zero_Fill_Cells;
5695 END IF;
5696
5697 cep_standard.debug('<<CE_CSH_FCST_POP.Populate_Cells');
5698 END Populate_Cells;
5699
5700
5701 END CE_CSH_FCST_POP;