[Home] [Help]
PACKAGE BODY: APPS.XTR_CSH_FCST_POP
Source
1 PACKAGE BODY XTR_CSH_FCST_POP AS
2 /* $Header: xtrfpclb.pls 115.15 2003/05/12 16:58:55 rvallams ship $ */
3
4
5 /* ---------------------------------------------------------------------
6 | PUBLIC PROCEDURE |
7 | Set_History |
8 | |
9 | DESCRIPTION |
10 | With AP payments and AR receipts if the forecast method is |
11 | 'P'ast then we need to set the history date or period |
12 | |
13 | CALLED BY |
14 | Build_XXX_Query |
15 | REQUIRES |
16 | |
17 | HISTORY |
18 | 19-AUG-1996 Created Bidemi Carrol |
19 --------------------------------------------------------------------- */
20 PROCEDURE Set_History IS
21 CURSOR cCol IS SELECT forecast_column_id, column_number, days_from, days_to
22 FROM ce_forecast_columns
23 WHERE forecast_header_id = XTR_CASH_FCST.G_rp_forecast_header_id;
24 error_msg FND_NEW_MESSAGES.message_text%TYPE;
25
26 min_col NUMBER;
27 max_col NUMBER;
28 col_num NUMBER;
29 cid NUMBER;
30 days_from NUMBER;
31 days_to NUMBER;
32 history_date DATE;
33 history_period VARCHAR2(30);
34 BEGIN
35 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
36 xtr_debug_pkg.debug('>>XTR_CASH_FCST.Set_History');
37
38 xtr_debug_pkg.debug('G_roll_forward_type: '|| XTR_CASH_FCST.G_roll_forward_type);
39
40 xtr_debug_pkg.debug('G_roll_forward_period : ' || XTR_CASH_FCST.G_roll_forward_period);
41
42 xtr_debug_pkg.debug('G_start_period: ' || XTR_CASH_FCST.G_rp_forecast_start_period);
43
44 xtr_debug_pkg.debug('period_set_name: ' || CEFC_VIEW_CONST.get_period_set_name);
45
46 END IF;
47
48 IF (XTR_CASH_FCST.G_roll_forward_type = 'D') THEN
49 CEFC_VIEW_CONST.set_start_date(XTR_CASH_FCST.G_rp_forecast_start_date - XTR_CASH_FCST.G_roll_forward_period);
50 CEFC_VIEW_CONST.set_min_col(XTR_CASH_FCST.G_min_col + XTR_CASH_FCST.G_roll_forward_period);
51 CEFC_VIEW_CONST.set_max_col(XTR_CASH_FCST.G_max_col + XTR_CASH_FCST.G_roll_forward_period);
52 ELSIF (XTR_CASH_FCST.G_roll_forward_type = 'M') THEN
53 history_date:= ADD_MONTHS(XTR_CASH_FCST.G_rp_forecast_start_date,- XTR_CASH_FCST.G_roll_forward_period);
54 CEFC_VIEW_CONST.set_start_date(history_date);
55 CEFC_VIEW_CONST.set_min_col(XTR_CASH_FCST.G_min_col + XTR_CASH_FCST.G_roll_forward_period*30);
56 CEFC_VIEW_CONST.set_max_col(XTR_CASH_FCST.G_max_col + XTR_CASH_FCST.G_roll_forward_period*30);
57 ELSIF (XTR_CASH_FCST.G_roll_forward_type = 'A') THEN
58 BEGIN
59 SELECT gps.period_name
60 INTO history_period
61 FROM gl_periods gps,
62 gl_periods gp,
63 gl_period_types gpt
64 WHERE gps.period_num =DECODE(LEAST(gp.period_num-XTR_CASH_FCST.G_roll_forward_period,1),
65 1,gp.period_num - XTR_CASH_FCST.G_roll_forward_period,
66 gpt.number_per_fiscal_year +
67 mod(gp.period_num-XTR_CASH_FCST.G_roll_forward_period,gpt.number_per_fiscal_year))
68 AND gps.period_year = gp.period_year +
69 DECODE(LEAST(gp.period_num-XTR_CASH_FCST.G_roll_forward_period,1),1,0,
70 DECODE(mod(gp.period_num-XTR_CASH_FCST.G_roll_forward_period,gpt.number_per_fiscal_year),0,
71 FLOOR((gp.period_num -XTR_CASH_FCST.G_roll_forward_period)/gpt.number_per_fiscal_year)-1,
72 FLOOR((gp.period_num -XTR_CASH_FCST.G_roll_forward_period)/gpt.number_per_fiscal_year)))
73 AND gp.period_set_name = gps.period_set_name
74 AND gps.period_type = gp.period_type
75 AND gpt.period_type = gp.period_type
76 AND gp.period_name = XTR_CASH_FCST.G_rp_forecast_start_period
77 AND gp.period_set_name = CEFC_VIEW_CONST.get_period_set_name;
78
79 CEFC_VIEW_CONST.set_start_period_name(history_period);
80 CEFC_VIEW_CONST.set_min_col(XTR_CASH_FCST.G_min_col + XTR_CASH_FCST.G_roll_forward_period);
81 CEFC_VIEW_CONST.set_max_col(XTR_CASH_FCST.G_max_col + XTR_CASH_FCST.G_roll_forward_period);
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
85 xtr_debug_pkg.debug('NO DATA FOUND FOR HISTORY PERIOD');
86 END IF;
87 RAISE;
88 WHEN OTHERS THEN
89 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
90 xtr_debug_pkg.debug('EXCEPTION-OTHERS Set_History');
91 END IF;
92 raise;
93 END;
94 END IF;
95
96 min_col := CEFC_VIEW_CONST.get_min_col;
97 max_col := CEFC_VIEW_CONST.get_max_col;
98 XTR_CASH_FCST.G_invalid_overdue_row := FALSE;
99 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
100 xtr_debug_pkg.debug('<<XTR_CASH_FCST.Set_History');
101 END IF;
102 EXCEPTION
103 WHEN OTHERS THEN
104 IF(cCol%ISOPEN)THEN CLOSE cCol; END IF;
105 RAISE;
106 END Set_History;
107
108 /* ---------------------------------------------------------------------
109 | PUBLIC PROCEDURE |
110 | populate_temp_buckets |
111 | |
112 | DESCRIPTION |
113 | CALLED BY |
114 | populate_ging_buckets |
115 | REQUIRES |
116 | |
117 | HISTORY |
118 | 30-DEC-1998 Created BHChung |
119 --------------------------------------------------------------------- */
120 PROCEDURE populate_temp_buckets IS
121 CURSOR C_periods IS
122 SELECT period_number,
123 level_of_summary,
124 length_of_period,
125 length_type
126 FROM xtr_forecast_periods_v
127 ORDER BY period_number;
128
129 l_period_number NUMBER;
130 l_level_of_summary VARCHAR2(1);
131 l_length_of_period NUMBER;
132 l_length_type VARCHAR2(1);
133
134 l_start_date DATE;
135 l_end_date DATE;
136 l_start DATE;
137 l_end DATE;
138
139 l_od_start DATE;
140 l_od_end DATE;
141
142 l_period_id NUMBER := 0;
143 l_count NUMBER;
144 BEGIN
145 BEGIN
146 DELETE FROM xtr_forecast_period_temp;
147 IF SQL%FOUND THEN
148 COMMIT;
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 XTR_DEBUG_PKG.DEBUG('EXCEPTION:populate_temp_buckets-->delete');
153 RAISE;
154 END;
155
156 l_start_date := XTR_CASH_FCST.G_rp_forecast_start_date;
157
158 FOR p_rec IN C_periods LOOP
159 IF p_rec.length_type = 'D' THEN
160 l_end_date := LAST_DAY(l_start_date + p_rec.length_of_period);
161 ELSIF p_rec.length_type = 'W' THEN
162 l_end_date := LAST_DAY(l_start_date + p_rec.length_of_period * 7);
163 ELSIF p_rec.length_type = 'M' THEN
164 l_end_date := LAST_DAY(ADD_MONTHS(l_start_date,p_rec.length_of_period) - 1);
165 ELSE
166 l_end_date := LAST_DAY(ADD_MONTHS(l_start_date,p_rec.length_of_period * 12) - 1);
167 END IF;
168
169 IF p_rec.level_of_summary = 'D' THEN
170 l_count := l_end_date - l_start_date + 1;
171 FOR i IN 1 .. l_count LOOP
172 IF NEXT_DAY(l_start_date-1,to_char(to_date('07/03/1997','DD/MM/YYYY'),'DY')) = l_start_date THEN
173 l_start := l_start_date + 2;
174 IF LAST_DAY(l_start_date) < l_start THEN
175 l_start := LAST_DAY(l_start_date);
176 END IF;
177 ELSE
178 l_start := l_start_date;
179 END IF;
180
181 INSERT INTO xtr_forecast_period_temp(forecast_period_temp_id, start_date, end_date, level_of_summary)
182 VALUES (l_period_id, l_start_date, l_start, 'D');
183
184 IF l_start = l_end_date THEN
185 l_period_id := l_period_id + 1;
186 l_start_date := l_start + 1;
187 EXIT;
188 ELSE
189 l_period_id := l_period_id + 1;
190 l_start_date := l_start + 1;
191 END IF;
192 END LOOP;
193 ELSIF p_rec.level_of_summary = 'O' THEN
194 l_od_end := l_start_date - 1;
195 l_od_start := l_start_date + p_rec.length_of_period - 1;
196
197 -- start date can't be sunday or saturday.
198 -- get previous friday in this case.
199 IF NEXT_DAY(l_od_start-1,to_char(to_date('08/03/1997','DD/MM/YYYY'),'DY')) = l_od_start THEN
200 l_od_start := l_od_start - 1;
201 ELSIF NEXT_DAY(l_od_start-1,to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')) = l_od_start THEN
202 l_od_start := l_od_start - 2;
203 END IF;
204
205 WHILE l_od_start < l_start_date LOOP
206 IF NEXT_DAY(l_od_start-1,to_char(to_date('07/03/1997','DD/MM/YYYY'),'DY')) = l_od_start THEN
207 l_start := l_od_start + 2;
208 IF LAST_DAY(l_od_start) < l_start THEN
209 l_start := LAST_DAY(l_od_start);
210 END IF;
211 IF l_start > l_od_end THEN
212 l_start := l_od_end;
213 END IF;
214 ELSE
215 l_start := l_od_start;
216 END IF;
217
218 INSERT INTO xtr_forecast_period_temp(forecast_period_temp_id, start_date, end_date, level_of_summary)
219 VALUES (l_period_id, l_od_start, l_start, 'D');
220
221 l_period_id := l_period_id + 1;
222 l_od_start := l_start + 1;
223 END LOOP;
224 ELSIF p_rec.level_of_summary = 'W' THEN
225 l_end := NEXT_DAY(l_start_date,to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY') );
226 WHILE l_end <= l_end_date AND l_start_date <= l_end_date LOOP
227
228 IF l_end > LAST_DAY(l_start_date) THEN
229 l_end := LAST_DAY(l_start_date);
230 END IF;
231
232 INSERT INTO xtr_forecast_period_temp(forecast_period_temp_id, start_date, end_date, level_of_summary)
233 VALUES (l_period_id, l_start_date, l_end, 'W');
234
235 l_period_id := l_period_id + 1;
236 l_start_date := l_end + 1;
237 l_end := NEXT_DAY(l_start_date, to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY'));
238 IF l_end > l_end_date THEN
239 l_end := l_end_date;
240 END IF;
241 END LOOP;
242 ELSIF p_rec.level_of_summary = 'M' THEN
243 l_end := LAST_DAY(l_start_date);
244 WHILE l_end <= l_end_date LOOP
245
246 INSERT INTO xtr_forecast_period_temp(forecast_period_temp_id, start_date, end_date, level_of_summary)
247 VALUES (l_period_id, l_start_date, l_end, 'M');
248
249 l_start_date := l_end + 1;
250 l_end := LAST_DAY(l_start_date);
251 l_period_id := l_period_id + 1;
252 END LOOP;
253 END IF;
254 END LOOP;
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF C_periods%ISOPEN THEN CLOSE C_periods; END IF;
258 XTR_DEBUG_PKG.DEBUG('EXCEPTION:populate_temp_buckets');
259 raise;
260 END populate_temp_buckets;
261
262 /* ---------------------------------------------------------------------
263 | PUBLIC PROCEDURE |
264 | populate_aging_buckets |
265 | |
266 | DESCRIPTION |
267 | Return real aging buckets by considering the transaction |
268 | calendar into account |
269 | CALLED BY |
270 | XTR_CASH_FCST.create_forecast |
271 | REQUIRES |
272 | |
273 | HISTORY |
274 | 30-DEC-1998 Created BHChung |
275 --------------------------------------------------------------------- */
276 PROCEDURE populate_aging_buckets IS
277 CURSOR C1 IS select forecast_period_temp_id, start_date, end_date
278 from xtr_forecast_period_temp;
279 start_date DATE;
280 end_date DATE;
281 new_start_date DATE;
282 new_end_date DATE;
283 fid NUMBER;
284 BEGIN
285 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
286 xtr_debug_pkg.debug('>>ce_csh_fcst_pop.populate_aging_buckets');
287 END IF;
288 populate_temp_buckets;
289
290 IF(XTR_CASH_FCST.G_transaction_calendar_id IS NOT NULL)THEN
291 OPEN C1;
292 FETCH C1 INTO fid, start_date, end_date;
293
294 LOOP
295 EXIT WHEN C1%NOTFOUND OR C1%NOTFOUND IS NULL;
296
297 new_start_date := NULL;
298 new_end_date := NULL;
299
300 IF(XTR_CASH_FCST.G_transaction_calendar_id IS NOT NULL)THEN
301 IF(start_date <= G_calendar_start OR
302 start_date-1 > G_calendar_end) THEN
303 new_start_date := start_date;
304 ELSE
305 BEGIN
306 select max(transaction_date)+1
307 into new_start_date
308 from gl_transaction_dates
309 where transaction_calendar_id = XTR_CASH_FCST.G_transaction_calendar_id
310 and transaction_date < start_date
311 and business_day_flag = 'Y';
312
313 IF (new_start_date IS NULL) THEN
314 new_start_date := G_calendar_start;
315 END IF;
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 -- case where all days between G_calendar_start and start_date are non-workdays.
319 new_start_date := G_calendar_start;
320 END;
321 END IF;
322
323 IF(end_date < G_calendar_start OR
324 end_date > G_calendar_end) THEN
325 new_end_date := end_date;
326 ELSE
327 BEGIN
328 select max(transaction_date)
329 into new_end_date
330 from gl_transaction_dates
331 where transaction_calendar_id = XTR_CASH_FCST.G_transaction_calendar_id
332 and transaction_date <= end_date
336 new_end_date := G_calendar_start -1;
333 and business_day_flag = 'Y';
334
335 IF (new_end_date IS NULL) THEN
337 END IF;
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 -- case where all days between end_date and G_calendar_start and non-workdays.
341 new_end_date := G_calendar_start -1;
342 END;
343 END IF;
344
345 UPDATE xtr_forecast_period_temp
346 SET start_date = new_start_date,
347 end_date = new_end_date
348 WHERE forecast_period_temp_id = fid;
349 END IF;
350
351 FETCH C1 INTO fid, start_date, end_date;
352 END LOOP;
353 CLOSE C1;
354 END IF;
355 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
356 xtr_debug_pkg.debug('<<xtr_csh_fcst_pop.populate_aging_buckets');
357 END IF;
358 EXCEPTION
359 WHEN OTHERS THEN
360 IF C1%ISOPEN THEN CLOSE C1; END IF;
361 XTR_DEBUG_PKG.DEBUG('EXCEPTION:populate_aging_buckets');
362 raise;
363 END populate_aging_buckets;
364
365 PROCEDURE clear_aging_buckets IS
366 BEGIN
367 delete from xtr_forecast_period_temp;
368 IF SQL%FOUND THEN
369 COMMIT;
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 XTR_DEBUG_PKG.DEBUG('EXCEPTION:clear_aging_buckets-->delete');
374 raise;
375 END clear_aging_buckets;
376
377 /* ----------------------------------------------------------------------
378 | PUBLIC PROCEDURE |
379 | Get Select Clause |
380 | |
381 | DESCRIPTION |
382 | Builds Select clause and returns it to calling procedure |
383 | CALLED BY |
384 | Build_XXX_Query |
385 | REQUIRES |
386 | |
387 | HISTORY |
388 | 19-AUG-1996 Created Bidemi Carrol |
389 --------------------------------------------------------------------- */
390 FUNCTION Get_Select_Clause RETURN VARCHAR2 IS
391 select_clause VARCHAR2(300);
392 BEGIN
393 if XTR_CASH_FCST.G_trx_type in ('APP','ARR','PAY') then -- AW Bug 2261452
394 select_clause := '
395 SELECT cab.forecast_period_temp_id,
396 src.currency_code,
397 SUM(src.amount),
398 src.bank_account_id ';
399 else
400 select_clause := '
401 SELECT cab.forecast_period_temp_id,
402 src.currency_code,
403 SUM(src.amount) ';
404 end if;
405 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
406 xtr_debug_pkg.debug(select_clause);
407 END IF;
408 return select_clause;
409 END Get_Select_Clause;
410
411
412 /* ---------------------------------------------------------------------
413 | PUBLIC PROCEDURE |
414 | Get From Clause |
415 | |
416 | DESCRIPTION |
417 | Builds From clause and returns it to calling procedure |
418 | CALLED BY |
419 | Build_XXX_Query |
420 | REQUIRES |
421 | trx view name |
422 | HISTORY |
423 | 19-AUG-1996 Created Bidemi Carrol |
424 --------------------------------------------------------------------- */
425
426
427 FUNCTION Get_From_Clause (view_name VARCHAR2) RETURN VARCHAR2 IS
428 from_clause VARCHAR2(500);
429 BEGIN
430 from_clause := '
431 FROM '||view_name ||' src,
432 xtr_forecast_period_temp cab ';
433
434 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
435 xtr_debug_pkg.debug('Get_From_Clause: ' || from_clause);
436 END IF;
437 return from_clause;
438 END Get_From_Clause;
439
440
441 /* ---------------------------------------------------------------------
442 | PUBLIC PROCEDURE |
443 | Get Group Clause |
444 | |
445 | DESCRIPTION |
446 | Builds group clause and returns it to calling procedure |
447 | CALLED BY |
448 | Build_XXX_Query |
449 | REQUIRES |
450 | |
451 | HISTORY |
452 | 19-AUG-1996 Created Bidemi Carrol |
453 --------------------------------------------------------------------- */
454 FUNCTION Get_Group_Clause RETURN VARCHAR2 IS
455 group_clause VARCHAR2(100);
456 BEGIN
457 if XTR_CASH_FCST.G_trx_type in ('APP','ARR','PAY') then -- AW Bug 2261452
458 group_clause := '
459 GROUP BY cab.forecast_period_temp_id, src.currency_code,
460 src.bank_account_id ';
461 else
462 group_clause := '
463 GROUP BY cab.forecast_period_temp_id, src.currency_code ';
464 end if;
465 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
466 xtr_debug_pkg.debug(group_clause);
467 END IF;
468 return group_clause;
469 END Get_Group_Clause;
470
471
472 /* ---------------------------------------------------------------------
473 | PUBLIC PROCEDURE |
474 | Add_Where |
475 | DESCRIPTION |
476 | Builds additional where clause for criteria if criteria |
477 | contains certain value |
478 | CALLED BY |
479 | Build_XXX_Query |
480 | REQUIRES |
481 | |
482 | HISTORY |
483 | 31-JUL-1997 Created Wynen Chan |
487 IF(criteria = 'SRC_CURR_TYPE')THEN
484 --------------------------------------------------------------------- */
485 FUNCTION Add_Where(criteria VARCHAR2) RETURN VARCHAR2 IS
486 BEGIN
488 IF(XTR_CASH_FCST.G_rp_src_curr_type = 'E')THEN
489 return ('
490 AND src.currency_code = '''||XTR_CASH_FCST.G_rp_src_currency||''' ');
491 ELSIF(XTR_CASH_FCST.G_rp_src_curr_type = 'F')THEN
492 return ('
493 AND org.currency_code = '''||XTR_CASH_FCST.G_rp_src_currency||'''
494 AND (org.org_id = src.org_id or org.org_id IS NULL) ');
495 END IF;
496
497 ELSIF(criteria = 'EXCHANGE_TYPE')THEN
498 IF(XTR_CASH_FCST.G_rp_exchange_type IS NULL OR
499 XTR_CASH_FCST.G_rp_exchange_type <> 'User')THEN
500 return ('
501 AND curr.forecast_request_id = '||XTR_CASH_FCST.G_forecast_id||'
502 AND curr.to_currency = '''||XTR_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(XTR_CASH_FCST.G_vendor_type IS NOT NULL)THEN
508 return ('
509 AND src.vendor_type = '''||replace(XTR_CASH_FCST.G_vendor_type,'''','''''')||''' ');
510 END IF;
511
512 ELSIF(criteria = 'PAY_GROUP')THEN
513 IF(XTR_CASH_FCST.G_pay_group IS NOT NULL)THEN
514 return ('
515 AND src.paygroup = '''||replace(XTR_CASH_FCST.G_pay_group,'''','''''')||''' ');
516 END IF;
517
518 ELSIF(criteria = 'PAYMENT_PRIORITY')THEN
519 IF(XTR_CASH_FCST.G_payment_priority IS NOT NULL)THEN
520 return ('
521 AND src.payment_priority <= '||to_char(XTR_CASH_FCST.G_payment_priority));
522 END IF;
523
524 ELSIF(criteria = 'BANK_ACCOUNT_ID')THEN
525 IF(XTR_CASH_FCST.G_bank_account_id IS NOT NULL)THEN
526 return ('
527 AND src.bank_account_id = '||TO_CHAR(XTR_CASH_FCST.G_bank_account_id));
528 END IF;
529
530 ELSIF(criteria = 'RECEIPT_METHOD_ID')THEN
531 IF(XTR_CASH_FCST.G_receipt_method_id IS NOT NULL)THEN
532 return ('
533 AND src.receipt_method_id = '||TO_CHAR(XTR_CASH_FCST.G_receipt_method_id));
534 END IF;
535
536 ELSIF(criteria = 'CUSTOMER_PROFILE_CLASS_ID')THEN
537 IF(XTR_CASH_FCST.G_customer_profile_class_id IS NOT NULL)THEN
538 return ('
539 AND src.profile_class_id = '||to_char(XTR_CASH_FCST.G_customer_profile_class_id));
540 END IF;
541
542 ELSIF(criteria = 'AUTHORIZATION_STATUS')THEN
543 IF(XTR_CASH_FCST.G_authorization_status IS NOT NULL)THEN
544 return ('
545 AND src.status = '''||replace(XTR_CASH_FCST.G_authorization_status,'''','''''')||''' ');
546 END IF;
547
548 ELSIF(criteria = 'PAYMENT_METHOD')THEN
549 IF(XTR_CASH_FCST.G_payment_method IS NOT NULL)THEN
550 return ('
551 AND src.payment_method = '''||replace(XTR_CASH_FCST.G_payment_method,'''','''''')||''' ');
552 END IF;
553
554 ELSIF(criteria = 'ORG_PAYMENT_METHOD_ID')THEN
555 IF(XTR_CASH_FCST.G_org_payment_method_id IS NOT NULL)THEN
556 return ('
557 AND src.org_payment_method_id = '||to_char(XTR_CASH_FCST.G_org_payment_method_id));
558 END IF;
559
560 ELSIF(criteria = 'PAYROLL_ID')THEN
561 IF( XTR_CASH_FCST.G_payroll_id IS NOT NULL )THEN
562 return ('
563 AND src.payroll_id = '||to_char(XTR_CASH_FCST.G_payroll_id));
564 END IF;
565
566 ELSIF(criteria = 'CHANNEL_CODE')THEN
567 IF( XTR_CASH_FCST.G_channel_code IS NOT NULL )THEN
568 return ('
569 AND src.channel_code = '''||replace(XTR_CASH_FCST.G_channel_code,'''','''''')||''' ');
570 END IF;
571
572 ELSIF(criteria = 'SALES_STAGE_ID')THEN
573 IF( XTR_CASH_FCST.G_sales_stage_id IS NOT NULL )THEN
574 return ('
575 AND src.sales_stage_id = '||to_char(XTR_CASH_FCST.G_sales_stage_id));
576 END IF;
577
578 ELSIF(criteria = 'SALES_FORECAST_STATUS')THEN
579 IF( XTR_CASH_FCST.G_sales_forecast_status IS NOT NULL )THEN
580 return ('
581 AND src.status_code = '''||replace(XTR_CASH_FCST.G_sales_forecast_status,'''','''''')||''' ');
582 END IF;
583
584 ELSE
585 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
586 xtr_debug_pkg.debug('ERROR - Add_Where got invalid criteria!');
587 END IF;
588 END IF;
589
590 return (NULL);
591 END Add_Where;
592
593
594 /* ---------------------------------------------------------------------
595 | PUBLIC PROCEDURE |
596 | Get Where Clause |
597 | |
598 | DESCRIPTION |
599 | Builds where clause and returns it to calling procedure |
600 | CALLED BY |
601 | Build_XXX_Query |
602 | REQUIRES |
603 | |
604 | HISTORY |
605 | 19-AUG-1996 Created Bidemi Carrol |
606 --------------------------------------------------------------------- */
607 FUNCTION Get_Where_Clause RETURN VARCHAR2 IS
608 where_clause VARCHAR2(1000);
609
610 BEGIN
611 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
612 xtr_debug_pkg.debug('>>XTR_CASH_FCST.Get_Where_Clause');
613 END IF;
614
615 where_clause := ' WHERE src.org_id IN ' ||XTR_CASH_FCST.G_rp_org_ids;
616
617 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
621 END Get_Where_Clause;
618 xtr_debug_pkg.debug('<<XTR_CASH_FCST.Get_Where_Clause');
619 END IF;
620 return where_clause;
622
623
624 /* ---------------------------------------------------------------------
625 | PUBLIC PROCEDURE |
626 | Execute_Main_Query |
627 | |
628 | DESCRIPTION |
629 | This procedure takes in the query string and executes it using |
630 | dynamic sql functionality. The query string is parsed and then |
631 | executed |
632 | CALLED BY |
633 | Build_XX_Query |
634 | REQUIRES |
635 | main_query |
636 | HISTORY |
637 | 12-JUL-1996 Created Bidemi Carrol |
638 --------------------------------------------------------------------- */
639 PROCEDURE Execute_Main_Query (main_query VARCHAR2) IS
640 CURSOR C_cur(p_cur VARCHAR2) IS
641 SELECT nvl(derive_type, 'NONE')
642 FROM gl_currencies
643 WHERE currency_code(+) = p_cur;
644
645 CURSOR C_period(p_pid NUMBER) IS
646 SELECT end_date,
647 level_of_summary
648 FROM xtr_forecast_period_temp
649 WHERE forecast_period_temp_id = p_pid;
650
651 cursor_id INTEGER;
652 exec_id INTEGER;
653 counter number;
654
655 forecast_period_temp_id NUMBER;
656 amount_date DATE;
657 company_code XTR_PARTY_INFO.party_code%TYPE;
658 trx_type CE_FORECAST_ROWS.trx_type%TYPE;
659 level_of_summary XTR_FORECAST_PERIODS.level_of_summary%TYPE;
660 currency GL_CURRENCIES.currency_code%TYPE;
661 bank_account_id NUMBER;
662 forecast_amount NUMBER;
663
664 l_emu GL_CURRENCIES.currency_code%TYPE;
665 error_msg fnd_new_messages.message_text%TYPE;
666 BEGIN
667
668 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
669 xtr_debug_pkg.debug('>>XTR_CSH_FCST_POP.Execute_Main_Query');
670 END IF;
671
672 cursor_id := DBMS_SQL.open_cursor;
673 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
674 xtr_debug_pkg.debug('Execute_Main_Query: Cursor opened sucessfully with cursor_id: '||
675 to_char(cursor_id));
676 xtr_debug_pkg.debug('Execute_Main_Query: Parsing ....');
677 END IF;
678
679 DBMS_SQL.Parse(cursor_id,
680 main_query,
681 DBMS_SQL.v7);
682
683 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
684 xtr_debug_pkg.debug('Execute_Main_Query: Parsed sucessfully');
685 END IF;
686
687 DBMS_SQL.Define_Column(cursor_id, 1, forecast_period_temp_id);
688 DBMS_SQL.Define_Column(cursor_id, 2, currency, 15);
689 DBMS_SQL.Define_Column(cursor_id, 3, forecast_amount);
690 if XTR_CASH_FCST.G_trx_type in ('APP','ARR','PAY') then -- AW Bug 2261452
691 DBMS_SQL.Define_Column(cursor_id, 4, bank_account_id);
692 end if;
693
694 exec_id := DBMS_SQL.execute(cursor_id);
695 LOOP
696 IF (DBMS_SQL.FETCH_ROWS(cursor_id) >0 ) THEN
697 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
698 xtr_debug_pkg.debug('Execute_Main_Query: Getting column information');
699 END IF;
700
701 DBMS_SQL.Column_Value(cursor_id, 1, forecast_period_temp_id);
702 DBMS_SQL.Column_Value(cursor_id, 2, currency);
703 DBMS_SQL.Column_Value(cursor_id, 3, forecast_amount);
704 if XTR_CASH_FCST.G_trx_type in ('APP','ARR','PAY') then -- AW Bug 2261452
705 DBMS_SQL.Column_Value(cursor_id, 4, bank_account_id);
706 end if;
707
708 IF(amount_date < XTR_CASH_FCST.G_rp_forecast_start_date)THEN -- Overdue Periods
709 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
710 xtr_debug_pkg.debug('Execute_Main_Query: Overdue transaction period');
711 xtr_debug_pkg.debug('Execute_Main_Query: trx_type = '
712 || XTR_CASH_FCST.G_trx_type || ', forecast_method = ' || XTR_CASH_FCST.G_forecast_method);
713 END IF;
714
715 IF(XTR_CASH_FCST.G_trx_type = 'PAY')THEN
716 forecast_amount := 0;
717 END IF;
718 IF(XTR_CASH_FCST.G_trx_type IN ('APP', 'ARR') AND
719 XTR_CASH_FCST.G_forecast_method = 'P')THEN
720 forecast_amount := 0;
721 END IF;
722 IF(XTR_CASH_FCST.G_invalid_overdue_row)THEN
723 forecast_amount := 0;
724 END IF;
725 END IF;
726
727 OPEN C_cur(currency);
728 FETCH C_cur INTO l_emu;
729 CLOSE C_cur;
730
731 IF l_emu = 'EMU' THEN
732 BEGIN
733 forecast_amount := GL_CURRENCY_API.convert_amount(currency,
734 'EUR',
735 XTR_CASH_FCST.G_rp_forecast_start_date,
736 'EMU-FIXED',
737 forecast_amount);
738
739 currency := 'EUR';
740 EXCEPTION
741 WHEN OTHERS THEN
742 FND_MESSAGE.set_name('GL', 'GL_JE_INVALID_CONVERSION_INFO');
743 error_msg := fnd_message.get;
744 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id,
745 XTR_CASH_FCST.G_rp_forecast_header_id, XTR_CASH_FCST.G_forecast_row_id,
746 'GL_JE_INVALID_CONVERSION_INFO', error_msg);
747 END;
748 END IF;
749
750 OPEN C_period(forecast_period_temp_id);
751 FETCH C_period INTO amount_date,
752 level_of_summary;
753 CLOSE C_period;
754
758 company_code,
755 INSERT INTO xtr_external_cashflows(amount_date,
756 amount,
757 currency,
759 trx_type,
760 ap_bank_account_id,
761 level_of_summary)
762 VALUES (amount_date,
763 nvl(forecast_amount,0),
764 currency,
765 XTR_CASH_FCST.G_party_code,
766 XTR_CASH_FCST.G_trx_type,
767 bank_account_id,
768 level_of_summary);
769 ELSE
770 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
771 xtr_debug_pkg.debug('Execute_Main_Query: No More Rows');
772 END IF;
773 EXIT;
774 END IF;
775 END LOOP;
776
777 DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
778
779 EXCEPTION
780 WHEN OTHERS THEN
781 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
782 xtr_debug_pkg.debug('EXCEPTION - OTHERS: Execute_Main_Query');
783 END IF;
784 IF DBMS_SQL.IS_OPEN(cursor_id) THEN
785 DBMS_SQL.CLOSE_CURSOR(cursor_id);
786 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
787 xtr_debug_pkg.debug('Execute_Main_Query: Cursor Closed');
788 END IF;
789 END IF;
790 RAISE;
791 END Execute_Main_Query;
792
793
794 /* ---------------------------------------------------------------------
795 | PUBLIC PROCEDURE |
796 | Build_AP_Pay_Query |
797 | |
798 | DESCRIPTION |
799 | This procedure builds the query to calculate the forecast |
800 | amounts for AP payments that were made in the past. |
801 | CALLED BY |
802 | Populate_Cells |
803 | REQUIRES |
804 | main_query |
805 | HISTORY |
806 | 12-JUL-1996 Created Bidemi Carrol |
807 --------------------------------------------------------------------- */
808 PROCEDURE Build_AP_Pay_Query IS
809 from_clause VARCHAR2(500);
810 where_clause varchar2(1000);
811 GROUP_clause varchar2(100);
812 select_clause varchar2(300);
813 main_query varchar2(2000) := null;
814 counter number;
815 error_msg FND_NEW_MESSAGES.message_text%TYPE;
816
817 BEGIN
818 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
819 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_AP_Pay_Query');
820 END IF;
821
822 select_clause := Get_Select_Clause;
823 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
824 xtr_debug_pkg.debug('Built Select Clause');
825 END IF;
826
827 from_clause := Get_From_Clause('ce_ap_fc_payments_v');
828 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
829 xtr_debug_pkg.debug('Built From Clause');
830 END IF;
831
832 where_clause := Get_Where_Clause || Add_Where('PAYMENT_METHOD') || Add_Where('BANK_ACCOUNT_ID');
833
834 IF (NVL(XTR_CASH_FCST.G_forecast_method,'F') = 'P') THEN
835 BEGIN
836 Set_History;
837
838 EXCEPTION
839 When NO_DATA_FOUND Then
840 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
841 xtr_debug_pkg.debug('row_id = ' || to_char(XTR_CASH_FCST.G_forecast_row_id));
842 END IF;
843 UPDATE ce_forecasts
844 SET error_status = 'E'
845 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
846
847 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
848 error_msg := FND_MESSAGE.get;
849 CE_FORECAST_ERRORS_PKG.insert_row(
850 CE_CASH_FCST.G_forecast_id,
851 CE_CASH_FCST.G_rp_forecast_header_id,
852 CE_CASH_FCST.G_forecast_row_id,
853 'CE_NO_HIST_START_PERIOD',
854 error_msg);
855 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
856 xtr_debug_pkg.debug('EXCEPTION: No history data found for APP');
857 END IF;
858 return;
859 When OTHERS Then
860 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
861 xtr_debug_pkg.debug('EXCEPTION: Build APP query - Set History');
862 END IF;
863 raise;
864 END;
865
866 where_clause := where_clause || '
867 AND src.cleared_date(+) BETWEEN cab.start_date and cab.end_date
868 AND src.status <> ''NEGOTIABLE'' ';
869
870 ELSE
871 where_clause := where_clause || '
872 AND NVL(src.maturity_date(+),src.payment_date(+)) +'
873 ||to_char(XTR_CASH_FCST.G_lead_time)||
874 ' BETWEEN cab.start_date and cab.end_date
875 AND src.status = ''NEGOTIABLE'' ';
876 END IF;
877 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
878 xtr_debug_pkg.debug('Built Where Clause');
879 END IF;
880
881 group_clause := Get_Group_Clause;
882 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
883 xtr_debug_pkg.debug('Built Group Clause');
884 END IF;
885
886 main_query := select_clause || from_clause || where_clause || group_clause;
887
888 Execute_Main_Query (main_query);
889 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
890 xtr_debug_pkg.debug('<<ce_csh_fcst_pop.Build_AP_Pay_Query');
891 END IF;
892 EXCEPTION
893 WHEN OTHERS THEN
894 XTR_DEBUG_PKG.DEBUG('EXCEPTION:Build_AP_Pay_Query');
895 raise;
896 END Build_AP_Pay_Query;
897
898
899 /* ---------------------------------------------------------------------
900 | PUBLIC PROCEDURE |
901 | Build_AP_Invoice_Query |
902 | |
903 | DESCRIPTION |
907 | payments will be made on one of the discount dates |
904 | This procedure builds the query to calculate the forecast |
905 | amounts for AP invoices that have not been paid, but projected |
906 | to be paid within the aging date ranges . It is assumed that |
908 | CALLED BY |
909 | Populate_Cells |
910 | REQUIRES |
911 | main_query |
912 | HISTORY |
913 | 12-JUL-1996 Created Bidemi Carrol |
914 --------------------------------------------------------------------- */
915 PROCEDURE Build_AP_Invoice_Query IS
916 from_clause VARCHAR2(500);
917 where_clause varchar2(1000);
918 group_clause varchar2(100);
919 select_clause varchar2(300);
920 main_query varchar2(2000) := null;
921 view_name VARCHAR2(50);
922
923 BEGIN
924 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
925 xtr_debug_pkg.debug('>>Build_AP_Invoice_Query');
926 END IF;
927
928 select_clause := Get_Select_Clause;
929 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
930 xtr_debug_pkg.debug('Built Select Clause');
931 END IF;
932
933 IF (NVL(XTR_CASH_FCST.G_discount_option,'N') = 'N') THEN
934 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
935 xtr_debug_pkg.debug('Discount NOT taken');
936 END IF;
937 from_clause := Get_From_Clause('ce_ap_fc_due_invoices_v');
938 ELSE
939 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
940 xtr_debug_pkg.debug('Discount taken');
941 END IF;
942 from_clause := Get_From_Clause('ce_disc_invoices_v');
943 END IF;
944 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
945 xtr_debug_pkg.debug('Built From Clause');
946 END IF;
947
948 where_clause := Get_Where_Clause || '
949 AND src.trx_date(+) +'
950 ||to_char(XTR_CASH_FCST.G_lead_time)||
951 ' BETWEEN cab.start_date and cab.end_date ' ||
952 Add_Where('PAYMENT_PRIORITY') || Add_Where('PAY_GROUP') || Add_Where('VENDOR_TYPE');
953
954 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
955 xtr_debug_pkg.debug('Built Where Clause');
956 END IF;
957
958 group_clause := Get_Group_Clause;
959 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
960 xtr_debug_pkg.debug('Built Where Clause');
961 END IF;
962
963 main_query := select_clause || from_clause || where_clause || group_clause;
964
965 Execute_Main_Query (main_query);
966
967 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
968 xtr_debug_pkg.debug('<<XTR_CSH_FCST_POP.Build_AP_Invoice_Query');
969 END IF;
970 EXCEPTION
971 WHEN OTHERS THEN
972 XTR_DEBUG_PKG.DEBUG('EXCEPTION:Build_AP_Invoice_Query');
973 raise;
974 END Build_AP_Invoice_Query;
975
976
977
978 /* ---------------------------------------------------------------------
979 | PUBLIC PROCEDURE |
980 | Build_AR_Invoice_Query |
981 | |
982 | DESCRIPTION |
983 | This procedure builds the query to calculate the forecast |
984 | amounts for AR invoices on which payments are due to be |
985 | received. Fully received invoices are exclude, but credit memos |
986 | debit memos and adjustments are included. |
987 | CALLED BY |
988 | Populate_Cells |
989 | REQUIRES |
990 | main_query |
991 | HISTORY |
992 | 12-JUL-1996 Created Bidemi Carrol |
993 --------------------------------------------------------------------- */
994 PROCEDURE Build_AR_Invoice_Query IS
995 from_clause VARCHAR2(500);
996 where_clause varchar2(1000);
997 group_clause varchar2(100);
998 select_clause varchar2(500);
999 main_query varchar2(3000) := null;
1000 BEGIN
1001 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1002 xtr_debug_pkg.debug('>>Build_AR_Invoice_Query');
1003 END IF;
1004 IF (XTR_CASH_FCST.G_include_dispute_flag = 'N') THEN
1005 select_clause := ' SELECT cab.forecast_period_temp_id,
1006 src.currency_code,
1007 SUM(src.amount-src.dispute_amount) ';
1008 ELSE
1009 select_clause := Get_Select_Clause;
1010 END IF;
1011 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1012 xtr_debug_pkg.debug('Built Select Clause');
1013 xtr_debug_pkg.debug(select_clause);
1014 END IF;
1015
1016 from_clause := Get_From_Clause ('ce_ar_fc_invoices_v');
1017 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1018 xtr_debug_pkg.debug('Built From Clause');
1019 END IF;
1020
1021 where_clause := Get_Where_Clause || '
1022 AND src.trx_date +'
1023 ||to_char(XTR_CASH_FCST.G_lead_time)||
1024 ' BETWEEN cab.start_date and cab.end_date '||
1025 Add_Where('CUSTOMER_PROFILE_CLASS_ID');
1026 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1027 xtr_debug_pkg.debug('Built Where Clause');
1028 END IF;
1029
1030 group_clause := Get_Group_Clause;
1031 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1032 xtr_debug_pkg.debug('Built Group Clause');
1033 END IF;
1034
1035 main_query := select_clause || from_clause || where_clause ||group_clause;
1036
1037 Execute_Main_Query (main_query);
1038 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1039 xtr_debug_pkg.debug('<<XTR_CSH_FCST_POP.Build_AR_Invoices_Query');
1040 END IF;
1044 xtr_debug_pkg.debug('EXCEPTION:OTHERS-Build_AR_Invoice_Query');
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1045 END IF;
1046 RAISE;
1047 END Build_AR_Invoice_Query;
1048
1049
1050
1051 /* ---------------------------------------------------------------------
1052 | PUBLIC PROCEDURE |
1053 | Build_AR_Receipt_Query |
1054 | |
1055 | DESCRIPTION |
1056 | This procedure builds the query to calculate the forecast |
1057 | amounts for AP checks that have not cleared the bank. |
1058 | CALLED BY |
1059 | Populate_Cells |
1060 | REQUIRES |
1061 | main_query |
1062 | HISTORY |
1063 | 12-JUL-1996 Created Bidemi Carrol |
1064 --------------------------------------------------------------------- */
1065 PROCEDURE Build_AR_Receipt_Query IS
1066 from_clause VARCHAR2(500);
1067 where_clause varchar2(1000);
1068 group_clause varchar2(100);
1069 select_clause varchar2(300);
1070 view_name VARCHAR2(50);
1071 main_query varchar2(3000) := null;
1072 counter number;
1073 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1074
1075 BEGIN
1076 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1077 xtr_debug_pkg.debug('>>Build_AR_Receipt_Query');
1078 END IF;
1079
1080 select_clause := Get_Select_Clause;
1081 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1082 xtr_debug_pkg.debug('Built Select Clause');
1083 END IF;
1084
1085 from_clause := Get_From_Clause ('ce_ar_fc_receipts_v');
1086 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1087 xtr_debug_pkg.debug('Built From Clause');
1088 END IF;
1089
1090 where_clause := Get_Where_Clause || Add_Where('BANK_ACCOUNT_ID') || Add_Where('RECEIPT_METHOD_ID');
1091
1092 IF (NVL(XTR_CASH_FCST.G_forecast_method,'F') = 'P') THEN
1093 BEGIN
1094 Set_History;
1095
1096 EXCEPTION
1097 When NO_DATA_FOUND Then
1098 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1099 xtr_debug_pkg.debug('row_id = ' || to_char(XTR_CASH_FCST.G_forecast_row_id));
1100 END IF;
1101
1102 UPDATE ce_forecasts
1103 SET error_status = 'E'
1104 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1105
1106 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
1107 error_msg := FND_MESSAGE.get;
1108 CE_FORECAST_ERRORS_PKG.insert_row(
1109 CE_CASH_FCST.G_forecast_id,
1110 CE_CASH_FCST.G_rp_forecast_header_id,
1111 CE_CASH_FCST.G_forecast_row_id,
1112 'CE_NO_HIST_START_PERIOD',
1113 error_msg);
1114 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1115 xtr_debug_pkg.debug('EXCEPTION: No history data found for ARR');
1116 END IF;
1117 return;
1118 When OTHERS Then
1119 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1120 xtr_debug_pkg.debug('EXCEPTION: Build ARR query - Set History');
1121 END IF;
1122 raise;
1123 END;
1124
1125 where_clause := where_clause || '
1126 AND src.trx_date(+) BETWEEN cab.start_date and cab.end_date
1127 AND src.status = ''CLEARED'' ';
1128 ELSE
1129 where_clause := where_clause || '
1130 AND nvl(src.effective_date(+),NVL(src.maturity_date(+),src.trx_date(+))) +'
1131 ||to_char(XTR_CASH_FCST.G_lead_time)||
1132 ' BETWEEN cab.start_date and cab.end_date
1133 AND src.status <> ''CLEARED'' ';
1134
1135
1136 END IF;
1137 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1138 xtr_debug_pkg.debug('Built Where Clause');
1139 END IF;
1140
1141 group_clause := Get_Group_Clause;
1142 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1143 xtr_debug_pkg.debug('Built Group Clause');
1144 END IF;
1145
1146 main_query := select_clause || from_clause || where_clause ||group_clause;
1147
1148 commit;
1149
1150 Execute_Main_Query (main_query);
1151 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1152 xtr_debug_pkg.debug('<<xtr_csh_fcst_pop.Build_AR_Receipt_Query');
1153 END IF;
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1157 xtr_debug_pkg.debug('EXCEPTION-OTHERS:Build_AR_Receipt_Query');
1158 END IF;
1159 RAISE;
1160 END Build_AR_Receipt_Query;
1161
1162 /* ---------------------------------------------------------------------
1163 | PUBLIC PROCEDURE |
1164 | Build_Pay_Exp_Query |
1165 | |
1166 | DESCRIPTION |
1167 | Payroll amounts paid out. |
1168 | CALLED BY |
1169 | Populate_Cells |
1170 | REQUIRES |
1171 | main_query |
1172 | HISTORY |
1173 | 1-JUL-1997 Created Wynne Chan |
1174 --------------------------------------------------------------------- */
1175 PROCEDURE Build_Pay_Exp_Query IS
1176 from_clause VARCHAR2(500);
1177 where_clause varchar2(1000);
1178 group_clause varchar2(100);
1179 select_clause varchar2(300);
1180 main_query varchar2(2000) := null;
1181 error_msg FND_NEW_MESSAGES.message_text%TYPE;
1182
1183 BEGIN
1184 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1185 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_PAY_Exp_Query');
1186 END IF;
1190 xtr_debug_pkg.debug('Built Select Clause');
1187
1188 select_clause := Get_Select_Clause;
1189 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1191 END IF;
1192
1193 from_clause := Get_From_Clause('ce_pay_fc_payroll_v');
1194 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1195 xtr_debug_pkg.debug('Built From Clause');
1196 END IF;
1197
1198 where_clause := Get_Where_Clause || '
1199 AND src.effective_date(+) BETWEEN cab.start_date and cab.end_date ' ||
1200 Add_Where('ORG_PAYMENT_METHOD_ID') || Add_Where('BANK_ACCOUNT_ID') || Add_Where('PAYROLL_ID');
1201
1202 group_clause := Get_Group_Clause;
1203 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1204 xtr_debug_pkg.debug('Built Group Clause');
1205 END IF;
1206
1207 BEGIN
1208 Set_History;
1209 EXCEPTION
1210 When NO_DATA_FOUND Then
1211 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1212 xtr_debug_pkg.debug('row_id = ' || to_char(XTR_CASH_FCST.G_forecast_row_id));
1213 END IF;
1214 UPDATE ce_forecasts
1215 SET error_status = 'E'
1216 WHERE forecast_id = CE_CASH_FCST.G_forecast_id;
1217
1218 FND_MESSAGE.set_name('CE', 'CE_NO_HIST_START_PERIOD');
1219 error_msg := FND_MESSAGE.get;
1220 CE_FORECAST_ERRORS_PKG.insert_row(
1221 CE_CASH_FCST.G_forecast_id,
1222 CE_CASH_FCST.G_rp_forecast_header_id,
1223 CE_CASH_FCST.G_forecast_row_id,
1224 'CE_NO_HIST_START_PERIOD',
1225 error_msg);
1226 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1227 xtr_debug_pkg.debug('EXCEPTION: No Payroll historical data found');
1228 END IF;
1229 return;
1230 When OTHERS Then
1231 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1232 xtr_debug_pkg.debug('EXCEPTION: Build Payroll query - Set History');
1233 END IF;
1234 raise;
1235 END;
1236
1237 main_query := select_clause || from_clause || where_clause || group_clause;
1238
1239 Execute_Main_Query (main_query);
1240 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1241 xtr_debug_pkg.debug('<<XTR_CSH_FCST_POP.Build_PAY_Exp_Query');
1242 END IF;
1243
1244 END Build_Pay_Exp_Query;
1245
1246
1247 /* ---------------------------------------------------------------------
1248 | PUBLIC PROCEDURE |
1249 | Build_PO_Orders_Query |
1250 | |
1251 | DESCRIPTION |
1252 | Purchase orders that have not been fully invoiced or cancelled |
1253 | CALLED BY |
1254 | Populate_Cells |
1255 | REQUIRES |
1256 | main_query |
1257 | HISTORY |
1258 | 12-JUL-1996 Created Bidemi Carrol |
1259 --------------------------------------------------------------------- */
1260 PROCEDURE Build_PO_Orders_Query IS
1261 from_clause VARCHAR2(500);
1262 where_clause varchar2(1000);
1263 group_clause varchar2(100);
1264 select_clause varchar2(300);
1265 main_query varchar2(2000) := null;
1266 BEGIN
1267 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1268 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_PO_Orders_Query');
1269 END IF;
1270
1271 select_clause := Get_Select_Clause;
1272 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1273 xtr_debug_pkg.debug('Built Select Clause');
1274 END IF;
1275
1276 from_clause := Get_From_Clause('ce_po_fc_orders_v');
1277 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1278 xtr_debug_pkg.debug('Built From Clause');
1279 END IF;
1280
1281 where_clause := Get_Where_Clause|| '
1282 AND src.trx_date(+) +'||to_char(XTR_CASH_FCST.G_lead_time)||'
1283 BETWEEN cab.start_date and cab.end_date ' ||
1284 Add_Where('AUTHORIZATION_STATUS') || Add_Where('PAYMENT_PRIORITY') ||
1285 Add_Where('PAY_GROUP') || Add_Where('VENDOR_TYPE');
1286
1287 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1288 xtr_debug_pkg.debug('Built Where Clause');
1289 END IF;
1290
1291 group_clause := Get_Group_Clause;
1292 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1293 xtr_debug_pkg.debug('Built Group Clause');
1294 END IF;
1295
1296 main_query := select_clause || from_clause || where_clause || group_clause;
1297
1298 Execute_Main_Query (main_query);
1299 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1300 xtr_debug_pkg.debug('<<xtr_csh_fcst_pop.Build_PO_Orders_Query');
1301 END IF;
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 XTR_DEBUG_PKG.DEBUG('EXCEPTION:Build_PO_Orders_Query');
1305 raise;
1306 END Build_PO_Orders_Query;
1307
1308
1309 /* ---------------------------------------------------------------------
1310 | PUBLIC PROCEDURE |
1311 | Build_PO_Req_Query |
1312 | |
1313 | DESCRIPTION |
1314 | Requisitions made but not fully ordered or cancelled |
1315 | CALLED BY |
1316 | Populate_Cells |
1317 | REQUIRES |
1318 | main_query |
1319 | HISTORY |
1320 | 12-JUL-1996 Created Bidemi Carrol |
1321 --------------------------------------------------------------------- */
1322 PROCEDURE Build_PO_Req_Query IS
1323 from_clause VARCHAR2(500);
1324 where_clause varchar2(1000);
1325 group_clause varchar2(100);
1326 select_clause varchar2(300);
1327 main_query varchar2(2000) := null;
1328 BEGIN
1332
1329 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1330 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_PO_Req_Query');
1331 END IF;
1333 select_clause := Get_Select_Clause;
1334 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1335 xtr_debug_pkg.debug('Built Select Clause');
1336 END IF;
1337
1338 from_clause := Get_From_Clause('ce_po_fc_requisitions_v');
1339 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1340 xtr_debug_pkg.debug('Built From Clause');
1341 END IF;
1342
1343 where_clause := Get_Where_Clause || '
1344 AND src.trx_date(+) +'||to_char(XTR_CASH_FCST.G_lead_time)|| '
1345 BETWEEN cab.start_date and cab.end_date '||
1346 Add_Where('AUTHORIZATION_STATUS');
1347
1348 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1349 xtr_debug_pkg.debug('Built Where Clause');
1350 END IF;
1351
1352 group_clause := Get_Group_Clause;
1353 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1354 xtr_debug_pkg.debug('Built Group Clause');
1355 END IF;
1356
1357 main_query := select_clause || from_clause || where_clause || group_clause;
1358
1359 Execute_Main_Query (main_query);
1360 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1361 xtr_debug_pkg.debug('<<xtr_csh_fcst_pop.Build_PO_Req_Query');
1362 END IF;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 XTR_DEBUG_PKG.DEBUG('EXCEPTION:Build_PO_req_Query');
1366 raise;
1367 END Build_PO_Req_Query ;
1368
1369 /* ---------------------------------------------------------------------
1370 | PUBLIC PROCEDURE |
1371 | Build_Sales_Order_Query |
1372 | |
1373 | DESCRIPTION |
1374 | Sales orders that have not been fully invoiced and/or paid |
1375 | CALLED BY |
1376 | Populate_Cells |
1377 | REQUIRES |
1378 | main_query |
1379 | HISTORY |
1380 | 12-JUL-1996 Created Bidemi Carrol |
1381 --------------------------------------------------------------------- */
1382 PROCEDURE Build_Sales_Order_Query IS
1383 from_clause VARCHAR2(500);
1384 where_clause varchar2(1000);
1385 group_clause varchar2(100);
1386 select_clause varchar2(300);
1387 main_query varchar2(2000) := null;
1388 BEGIN
1389 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1390 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_Sales_Order_Query');
1391 END IF;
1392
1393 select_clause := Get_Select_Clause;
1394 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1395 xtr_debug_pkg.debug('Built Select Clause');
1396 END IF;
1397
1398 from_clause := Get_From_Clause('ce_so_fc_orders_v');
1399 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1400 xtr_debug_pkg.debug('Built From Clause');
1401 END IF;
1402
1403 where_clause := Get_Where_Clause ||
1404 Add_Where('CUSTOMER_PROFILE_CLASS_ID');
1405
1406 IF( XTR_CASH_FCST.G_order_status = 'O') THEN
1407 where_clause := where_clause || '
1408 AND src.booked_flag = ''N'' ';
1409 ELSIF( XTR_CASH_FCST.G_order_status = 'B')THEN
1410 where_clause := where_clause || '
1411 AND src.booked_flag = ''Y'' ';
1412 END IF;
1413
1414 IF(XTR_CASH_FCST.G_order_date_type = 'R')THEN
1415 where_clause := where_clause || '
1416 AND src.date_requested(+) +'||to_char(XTR_CASH_FCST.G_lead_time)||'
1417 BETWEEN cab.start_date and cab.end_date ';
1418 ELSE
1419 where_clause := where_clause || '
1420 AND src.date_ordered(+) +'||to_char(XTR_CASH_FCST.G_lead_time)||'
1421 BETWEEN cab.start_date and cab.end_date ';
1422 END IF;
1423 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1424 xtr_debug_pkg.debug('Built Where Clause');
1425 END IF;
1426
1427 group_clause := Get_Group_Clause;
1428 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1429 xtr_debug_pkg.debug('Built Group Clause');
1430 END IF;
1431
1432 main_query := select_clause || from_clause || where_clause || group_clause;
1433
1434 Execute_Main_Query (main_query);
1435 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1436 xtr_debug_pkg.debug('<<xtr_csh_fcst_pop.Build_Sales_Order_Query');
1437 END IF;
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 XTR_DEBUG_PKG.DEBUG('EXCEPTION:Build_Sales_Order_Query');
1441 raise;
1442 END Build_Sales_Order_Query ;
1443
1444
1445 /* ---------------------------------------------------------------------
1446 | PUBLIC PROCEDURE |
1447 | Build_Remote_Query |
1448 | |
1449 | DESCRIPTION |
1450 | This procedure builds the query to calculate the forecast |
1451 | amounts from the remote transactions |
1452 | CALLED BY |
1453 | Populate_Cells |
1454 | REQUIRES |
1455 | main_query |
1456 | HISTORY |
1457 | 12-JUL-1996 Created Bidemi Carrol |
1458 --------------------------------------------------------------------- */
1462 cursor_id INTEGER;
1459 PROCEDURE Build_Remote_Query IS
1460 db_link varchar2(128);
1461 main_query VARCHAR2(5000) := null;
1463 exec_id INTEGER;
1464 error_msg VARCHAR2(2000);
1465 BEGIN
1466 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1467 xtr_debug_pkg.debug('>>XTR_CSH_FCAST_POP.Build_Remote_Query');
1468 END IF;
1469 --
1470 -- Get view and db information from the external source type
1471 --
1472 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1473 xtr_debug_pkg.debug('Get database information for database: '||XTR_CASH_FCST.G_external_source_type);
1474 END IF;
1475 BEGIN
1476
1477 SELECT external_source_view, db_link_name
1478 INTO source_view, db_link
1479 FROM ce_forecast_ext_views
1480 WHERE external_source_type = XTR_CASH_FCST.G_external_source_type;
1481
1482 EXCEPTION
1483 WHEN NO_DATA_FOUND THEN
1484 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1485 xtr_debug_pkg.debug('EXCEPTION:Build_Remote_Query - View def not found');
1486 END IF;
1487 FND_MESSAGE.set_name('CE','CE_FC_EXT_SOURCE_UNDEFINED');
1488 FND_MESSAGE.set_token('EXT_TYPE', XTR_CASH_FCST.G_external_source_type);
1489 error_msg := FND_MESSAGE.get;
1490 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1491 XTR_CASH_FCST.G_forecast_row_id, 'CE_FC_EXT_SOURCE_UNDEFINED', error_msg);
1492 RETURN;
1493 END;
1494
1495
1496 IF( db_link IS NOT NULL )THEN
1497 db_link := '@'||db_link;
1498 END IF;
1499 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1500 xtr_debug_pkg.debug(' source_view = '||source_view||', db_link = '||db_link);
1501 END IF;
1502
1503 main_query := '
1504 declare
1505 counter NUMBER;
1506 error_code NUMBER;
1507 error_msg VARCHAR2(2000); ';
1508
1509 main_query := main_query ||'
1510 begin
1511 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1512 xtr_debug_pkg.enable_file_debug;
1513 END IF;
1514 ';
1515
1516
1517 main_query := main_query ||'
1518 --
1519 -- Built query to be executed in the remote/local database
1520 --
1521 error_code := XTR_FORECAST_REMOTE_SOURCES.populate_remote_amounts';
1522
1523 --
1524 -- Append db_link if applicable
1525 --
1526 IF( db_link IS NOT NULL) THEN
1527 source_view := source_view||db_link;
1528 END IF;
1529
1530 main_query := main_query ||'(
1531 '''||source_view||''',
1532 '''||db_link||''',
1533 XTR_CASH_FCST.G_criteria1,
1534 XTR_CASH_FCST.G_criteria2,
1535 XTR_CASH_FCST.G_criteria3,
1536 XTR_CASH_FCST.G_criteria4,
1537 XTR_CASH_FCST.G_criteria5,
1538 XTR_CASH_FCST.G_criteria6,
1539 XTR_CASH_FCST.G_criteria7,
1540 XTR_CASH_FCST.G_criteria8,
1541 XTR_CASH_FCST.G_criteria9,
1542 XTR_CASH_FCST.G_criteria10,
1543 XTR_CASH_FCST.G_criteria11,
1544 XTR_CASH_FCST.G_criteria12,
1545 XTR_CASH_FCST.G_criteria13,
1546 XTR_CASH_FCST.G_criteria14,
1547 XTR_CASH_FCST.G_criteria15);
1548
1549 IF( error_code = 0 )THEN
1550 null;
1551 ELSIF( error_code = -1 )THEN
1552 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1553 xtr_debug_pkg.debug(''Remote error: missing view'');
1554 END IF;
1555 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_MISSING_VIEW_EXPT'');
1556 error_msg := FND_MESSAGE.get;
1557 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1558 XTR_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_MISSING_VIEW_EXPT'', error_msg);
1559 return;
1560 ELSIF( error_code = -2 )THEN
1561 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1562 xtr_debug_pkg.debug(''Remote error: invalid view'');
1563 END IF;
1564 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_INVALID_VIEW_EXPT'');
1565 error_msg := FND_MESSAGE.get;
1566 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1567 XTR_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_INVALID_VIEW_EXPT'', error_msg);
1568 return;
1569 ELSE
1570 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1571 xtr_debug_pkg.debug(''Remote error: others'');
1572 END IF;
1573 FND_MESSAGE.set_name(''CE'', ''CE_FC_RMT_EXCEPTION'');
1574 error_msg := FND_MESSAGE.get;
1578 END IF;
1575 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1576 XTR_CASH_FCST.G_forecast_row_id, ''CE_FC_RMT_EXCEPTION'', error_msg);
1577 return;
1579 end; ';
1580 BEGIN
1581 cursor_id := DBMS_SQL.open_cursor;
1582 DBMS_SQL.parse(cursor_id, main_query, DBMS_SQL.v7);
1583 exec_id := DBMS_SQL.execute(cursor_id);
1584 DBMS_SQL.close_cursor(cursor_id);
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 IF DBMS_SQL.is_open(cursor_id) THEN
1588 DBMS_SQL.close_cursor(cursor_id);
1589 END IF;
1590 FND_MESSAGE.set_name('CE', 'CE_FC_RMT_DB_EXCEPTION');
1591 error_msg := FND_MESSAGE.get;
1592 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1593 XTR_CASH_FCST.G_forecast_row_id, 'CE_FC_RMT_DB_EXCEPTION', error_msg);
1594 return;
1595 END;
1596 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1597 xtr_debug_pkg.debug('<<XTR_CSH_FCST_POP.Build_Remote_Query');
1598 END IF;
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1602 xtr_debug_pkg.debug('EXCEPTION:Build_Remote_Query');
1603 END IF;
1604 FND_MESSAGE.set_name('CE', 'CE_FC_RMT_EXCEPTION');
1605 error_msg := FND_MESSAGE.get;
1606 CE_FORECAST_ERRORS_PKG.insert_row(XTR_CASH_FCST.G_forecast_id, XTR_CASH_FCST.G_rp_forecast_header_id,
1607 XTR_CASH_FCST.G_forecast_row_id, 'CE_FC_RMT_EXCEPTION', error_msg);
1608 END Build_Remote_Query;
1609
1610 /* ---------------------------------------------------------------------
1611 | PUBLIC PROCEDURE |
1612 | Populate_Cells |
1613 | |
1614 | DESCRIPTION |
1615 | This procedure calls the appropriate build query procedure for |
1616 | each transaction type. |
1617 | CALLED BY |
1618 | |
1619 | REQUIRES |
1620 | |
1621 | HISTORY |
1622 | 12-JUL-1996 Created Bidemi Carrol |
1623 --------------------------------------------------------------------- */
1624 PROCEDURE Populate_Cells IS
1625 BEGIN
1626 --
1627 -- Based on the source_trx_type call the different procedures
1628 -- to build the queries dynamically
1629 --
1630 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1631 xtr_debug_pkg.debug('>>XTR_CSH_FCST_POP.Populate_Cells');
1632 END IF;
1633
1634 IF (XTR_CASH_FCST.G_trx_type = 'API') THEN
1635 Build_AP_Invoice_Query;
1636 ELSIF (XTR_CASH_FCST.G_trx_type = 'APP') THEN
1637 Build_AP_Pay_Query;
1638 ELSIF (XTR_CASH_FCST.G_trx_type = 'ARI') THEN
1639 Build_AR_Invoice_Query;
1640 ELSIF (XTR_CASH_FCST.G_trx_type = 'ARR') THEN
1641 Build_AR_Receipt_Query;
1642 ELSIF (XTR_CASH_FCST.G_trx_type = 'PAY') THEN
1643 Build_Pay_Exp_Query;
1644 ELSIF (XTR_CASH_FCST.G_trx_type = 'POP') THEN
1645 Build_PO_Orders_Query;
1646 ELSIF (XTR_CASH_FCST.G_trx_type = 'POR') THEN
1647 Build_PO_Req_Query;
1648 ELSIF (XTR_CASH_FCST.G_trx_type = 'OEO') THEN
1649 Build_Sales_Order_Query;
1650 ELSIF (XTR_CASH_FCST.G_trx_type = 'OII') THEN
1651 Build_Remote_Query;
1652 ELSIF (XTR_CASH_FCST.G_trx_type = 'OIO') THEN
1653 Build_Remote_Query;
1654 END IF;
1655
1656 END Populate_Cells;
1657
1658 END XTR_CSH_FCST_POP;
1659