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