1 PACKAGE BODY CE_BANK_ACCT_BALANCE_REPORT AS
2 /* $Header: cexmlbrb.pls 120.9 2006/01/06 22:37:55 shawang noship $ */
3
4 -- if exchange rate is not available for all bank account currencies, return -1, else return 1
5 function get_total_balance_flag
6 (
7 p_branch_party_id varchar2,
8 p_bank_acct_id varchar2,
9 p_bank_acct_currency VARCHAR2,
10 p_legal_entity_id number,
11 l_date date,
12 p_reporting_currency varchar2,
13 p_exchange_rate_type varchar2,
14 p_exchange_rate_date varchar2
15 )
16 return number
17 is
18 cursor cursor_bank_currency_code is
19 select distinct ba.CURRENCY_CODE
20 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
21 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
22 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
23 and bb.BALANCE_DATE = l_date
24 and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
25 and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
26 and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
27 and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
28 l_total_balance_flag number;
29 v_currency_code varchar2(10);
30
31 begin
32 open cursor_bank_currency_code;
33 loop
34 fetch cursor_bank_currency_code into v_currency_code;
35 exit when cursor_bank_currency_code%NOTFOUND;
36 if -1 = CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) then
37 close cursor_bank_currency_code;
38 return -1;
39 end if;
40 end loop;
41 close cursor_bank_currency_code;
42 return 1;
43
44 end get_total_balance_flag;
45
46 procedure get_balance_near_date
47 (p_bank_account_id varchar2,
48 p_date date,
49 p_BalanceAC OUT NOCOPY number,
50 p_nearest_Date OUT NOCOPY date ,
51 p_balance_type varchar2)
52 is
53 v_date_offset number;
54 v_date date;
55 v_balance number;
56
57 begin
58
59 v_date := p_date;
60 --fnd_file.put_line(FND_FILE.LOG, p_bank_account_id);
61 --fnd_file.put_line(fnd_file.log, 'as of date ' || to_char(p_date, 'YYYY/MM/DD HH24:MI:SS'));
62 --fnd_file.put_line(fnd_file.log, 'balance type ' || p_balance_type);
63 --fnd_file.put_line(fnd_file.log, 'exchange rate type ' || p_exchange_rate_type);
64
65 /*
66 v_LegerBalanceAC number;
67 v_AvailableBalanceAC number;
68 v_IntCalBalanceAC number;
69 v_OneDayFloatAC number;
70 v_TwoDayFloatAC number;
71 v_AvgLegerMTDAC number;
72 v_AvgLegerYTDAC number;
73 v_AvgAvailableMTDAC number;
74 v_AvgAvailableYTDAC number;
75 */
76
77 -- when balance type is LegerBalance
78 if p_balance_type = 'LegerBalance' then
79
80 select min(abs(BALANCE_DATE - p_date))
81 into v_date_offset
82 from ce_bank_acct_balances bb
83 where bb.BANK_ACCOUNT_ID = p_bank_account_id
84 and bb.LEDGER_BALANCE is not null;
85
86 if v_date_offset is not null then
87 select min(balance_date)
88 into v_date
89 from ce_bank_acct_balances bb
90 where bb.BANK_ACCOUNT_ID = p_bank_account_id
91 and abs(BALANCE_DATE - p_date) = v_date_offset
92 and bb.LEDGER_BALANCE is not null;
93
94 select LEDGER_BALANCE
95 into v_balance
96 from ce_bank_acct_balances
97 where BANK_ACCOUNT_ID = p_bank_account_id
98 and BALANCE_DATE = v_date;
99
100 end if;
101 end if;
102
103 -- when balance type is AvailableBalance
104 if p_balance_type = 'AvailableBalance' then
105
106 select min(abs(BALANCE_DATE - p_date))
107 into v_date_offset
108 from ce_bank_acct_balances bb
109 where bb.BANK_ACCOUNT_ID = p_bank_account_id
110 and bb.AVAILABLE_BALANCE is not null;
111
112 if v_date_offset is not null then
113 select min(balance_date)
114 into v_date
115 from ce_bank_acct_balances bb
116 where bb.BANK_ACCOUNT_ID = p_bank_account_id
117 and abs(BALANCE_DATE - p_date) = v_date_offset
118 and bb.AVAILABLE_BALANCE is not null;
119
120 select AVAILABLE_BALANCE
121 into v_balance
122 from ce_bank_acct_balances
123 where BANK_ACCOUNT_ID = p_bank_account_id
124 and BALANCE_DATE = v_date;
125
126 --fnd_file.put_line(fnd_file.log, 'v balance ' || v_balance);
127
128 end if;
129 end if;
130
131 -- when balance type is VALUE_DATED_BALANCE
132 if p_balance_type = 'IntCalBalance' then
133
134 select min(abs(BALANCE_DATE - p_date))
135 into v_date_offset
136 from ce_bank_acct_balances bb
137 where bb.BANK_ACCOUNT_ID = p_bank_account_id
138 and bb.VALUE_DATED_BALANCE is not null;
139
140 if v_date_offset is not null then
141 select min(balance_date)
142 into v_date
143 from ce_bank_acct_balances bb
144 where bb.BANK_ACCOUNT_ID = p_bank_account_id
145 and abs(BALANCE_DATE - p_date) = v_date_offset
146 and bb.VALUE_DATED_BALANCE is not null;
147
148 select VALUE_DATED_BALANCE
149 into v_balance
150 from ce_bank_acct_balances
151 where BANK_ACCOUNT_ID = p_bank_account_id
152 and BALANCE_DATE = v_date;
153
154 end if;
155 end if;
156
157 -- when balance type is ONE_DAY_FLOAT
158 if p_balance_type = 'OneDayFloat' then
159
160 select min(abs(BALANCE_DATE - p_date))
161 into v_date_offset
162 from ce_bank_acct_balances bb
163 where bb.BANK_ACCOUNT_ID = p_bank_account_id
164 and bb.ONE_DAY_FLOAT is not null;
165
166 if v_date_offset is not null then
167 select min(balance_date)
168 into v_date
169 from ce_bank_acct_balances bb
170 where bb.BANK_ACCOUNT_ID = p_bank_account_id
171 and abs(BALANCE_DATE - p_date) = v_date_offset
172 and bb.ONE_DAY_FLOAT is not null;
173
174 select ONE_DAY_FLOAT
175 into v_balance
176 from ce_bank_acct_balances
177 where BANK_ACCOUNT_ID = p_bank_account_id
178 and BALANCE_DATE = v_date;
179
180 end if;
181 end if;
182
183 -- when balance type is TWO_DAY_FLOAT
184 if p_balance_type = 'TwoDayFloat' then
185
186 select min(abs(BALANCE_DATE - p_date))
187 into v_date_offset
188 from ce_bank_acct_balances bb
189 where bb.BANK_ACCOUNT_ID = p_bank_account_id
190 and bb.TWO_DAY_FLOAT is not null;
191
192 if v_date_offset is not null then
193 select min(balance_date)
194 into v_date
195 from ce_bank_acct_balances bb
196 where bb.BANK_ACCOUNT_ID = p_bank_account_id
197 and abs(BALANCE_DATE - p_date) = v_date_offset
198 and bb.TWO_DAY_FLOAT is not null;
199
200 select TWO_DAY_FLOAT
201 into v_balance
202 from ce_bank_acct_balances
203 where BANK_ACCOUNT_ID = p_bank_account_id
204 and BALANCE_DATE = v_date;
205
206 end if;
207 end if;
208
212 select min(abs(BALANCE_DATE - p_date))
209 -- when balance type is AVERAGE_CLOSE_LEDGER_MTD
210 if p_balance_type = 'AvgLegerMTD' then
211
213 into v_date_offset
214 from ce_bank_acct_balances bb
215 where bb.BANK_ACCOUNT_ID = p_bank_account_id
216 and bb.AVERAGE_CLOSE_LEDGER_MTD is not null;
217
218 if v_date_offset is not null then
219 select min(balance_date)
220 into v_date
221 from ce_bank_acct_balances bb
222 where bb.BANK_ACCOUNT_ID = p_bank_account_id
223 and abs(BALANCE_DATE - p_date) = v_date_offset
224 and bb.AVERAGE_CLOSE_LEDGER_MTD is not null;
225
226 select AVERAGE_CLOSE_LEDGER_MTD
227 into v_balance
228 from ce_bank_acct_balances
229 where BANK_ACCOUNT_ID = p_bank_account_id
230 and BALANCE_DATE = v_date;
231
232 end if;
233 end if;
234
235 -- when balance type is AVERAGE_CLOSE_LEDGER_YTD
236 if p_balance_type = 'AvgLegerYTD' then
237
238 select min(abs(BALANCE_DATE - p_date))
239 into v_date_offset
240 from ce_bank_acct_balances bb
241 where bb.BANK_ACCOUNT_ID = p_bank_account_id
242 and bb.AVERAGE_CLOSE_LEDGER_YTD is not null;
243
244 if v_date_offset is not null then
245 select min(balance_date)
246 into v_date
247 from ce_bank_acct_balances bb
248 where bb.BANK_ACCOUNT_ID = p_bank_account_id
249 and abs(BALANCE_DATE - p_date) = v_date_offset
250 and bb.AVERAGE_CLOSE_LEDGER_YTD is not null;
251
252 select AVERAGE_CLOSE_LEDGER_YTD
253 into v_balance
254 from ce_bank_acct_balances
255 where BANK_ACCOUNT_ID = p_bank_account_id
256 and BALANCE_DATE = v_date;
257
258 end if;
259 end if;
260
261 -- when balance type is AVERAGE_CLOSE_AVAILABLE_MTD
262 if p_balance_type = 'AvgAvailableMTD' then
263
264 select min(abs(BALANCE_DATE - p_date))
265 into v_date_offset
266 from ce_bank_acct_balances bb
267 where bb.BANK_ACCOUNT_ID = p_bank_account_id
268 and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null;
269
270 if v_date_offset is not null then
271 select min(balance_date)
272 into v_date
273 from ce_bank_acct_balances bb
274 where bb.BANK_ACCOUNT_ID = p_bank_account_id
275 and abs(BALANCE_DATE - p_date) = v_date_offset
276 and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null;
277
278 select AVERAGE_CLOSE_AVAILABLE_MTD
279 into v_balance
280 from ce_bank_acct_balances
281 where BANK_ACCOUNT_ID = p_bank_account_id
282 and BALANCE_DATE = v_date;
283
284 end if;
285 end if;
286
287 -- when balance type is AVERAGE_CLOSE_AVAILABLE_YTD
288 if p_balance_type = 'AvgAvailableYTD' then
289
290 select min(abs(BALANCE_DATE - p_date))
291 into v_date_offset
292 from ce_bank_acct_balances bb
293 where bb.BANK_ACCOUNT_ID = p_bank_account_id
294 and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null;
295
296 if v_date_offset is not null then
297 select min(balance_date)
298 into v_date
299 from ce_bank_acct_balances bb
300 where bb.BANK_ACCOUNT_ID = p_bank_account_id
301 and abs(BALANCE_DATE - p_date) = v_date_offset
302 and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null;
303
304 select AVERAGE_CLOSE_AVAILABLE_YTD
305 into v_balance
306 from ce_bank_acct_balances
307 where BANK_ACCOUNT_ID = p_bank_account_id
308 and BALANCE_DATE = v_date;
309
310 end if;
311 end if;
312
313 p_BalanceAC := v_balance;
314 p_nearest_Date := v_date;
315
316 end get_balance_near_date;
317
318
319 procedure single_day_balance_report
320 (errbuf OUT NOCOPY VARCHAR2,
321 retcode OUT NOCOPY NUMBER,
322 p_branch_party_id varchar2,
323 p_bank_acct_id varchar2,
324 p_bank_acct_currency VARCHAR2,
325 p_legal_entity_id number,
326 p_as_of_date varchar2,
327 p_reporting_currency varchar2,
328 p_exchange_rate_type varchar2,
329 p_exchange_rate_date varchar2
330 )
331 is
332
333 l_length number;
334 l_offset number;
335 l_amount number;
336 l_buffer varchar2(32767);
337 l_xml_doc clob;
338 l_xml xmltype;
339 l_date date;
340 l_date_offset number;
341 l_exchange_rate number;
342 xrate number;
343 l_total_balance_flag number;
344 l_Bank_Branch_Name varchar2(100);
345 l_Bank_ACCT_NAME varchar2(100);
346 l_exchange_rate_date varchar2(200);
347 l_legal_entity_name varchar2(200);
348
349 v_currency_code varchar2(15);
350 v_bank_account_id number;
351 v_date date;
352
353 v_LegerBalanceAC number;
354 v_AvailableBalanceAC number;
355 v_IntCalBalanceAC number;
356 v_OneDayFloatAC number;
357 v_TwoDayFloatAC number;
358 v_AvgLegerMTDAC number;
359 v_AvgLegerYTDAC number;
360 v_AvgAvailableMTDAC number;
361 v_AvgAvailableYTDAC number;
362
363 v_LegerBalance_Date date;
364 v_AvailableBalance_Date date;
365 v_IntCalBalance_Date date;
366 v_OneDayFloat_Date date;
367 v_TwoDayFloat_Date date;
368 v_AvgLegerMTD_Date date;
369 v_AvgLegerYTD_Date date;
370 v_AvgAvailableMTD_Date date;
371 v_AvgAvailableYTD_Date date;
372
373 v_LegerBalanceSubTAC number;
374 v_AvailableBalanceSubTAC number;
375 v_IntCalBalanceSubTAC number;
376 v_OneDayFloatSubTAC number;
377 v_TwoDayFloatSubTAC number;
378 v_AvgLegerMTDSubTAC number;
379 v_AvgLegerYTDSubTAC number;
383 v_LegerBalanceRC number;
380 v_AvgAvailableMTDSubTAC number;
381 v_AvgAvailableYTDSubTAC number;
382
384 v_AvailableBalanceRC number;
385 v_IntCalBalanceRC number;
386 v_OneDayFloatRC number;
387 v_TwoDayFloatRC number;
388 v_AvgLegerMTDRC number;
389 v_AvgLegerYTDRC number;
390 v_AvgAvailableMTDRC number;
391 v_AvgAvailableYTDRC number;
392
393 v_LegerBalanceSubTRC number;
394 v_AvailableBalanceSubTRC number;
395 v_IntCalBalanceSubTRC number;
396 v_OneDayFloatSubTRC number;
397 v_TwoDayFloatSubTRC number;
398 v_AvgLegerMTDSubTRC number;
399 v_AvgLegerYTDSubTRC number;
400 v_AvgAvailableMTDSubTRC number;
401 v_AvgAvailableYTDSubTRC number;
402
403 v_LegerBalanceTotal number;
404 v_AvailableBalanceTotal number;
405 v_IntCalBalanceTotal number;
406 v_OneDayFloatTotal number;
407 v_TwoDayFloatTotal number;
408 v_AvgLegerMTDTotal number;
409 v_AvgLegerYTDTotal number;
410 v_AvgAvailableMTDTotal number;
411 v_AvgAvailableYTDTotal number;
412
413 cursor cursor_bank_currency_code is
414 select distinct ba.CURRENCY_CODE
415 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
416 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
417 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
418 and bb.BALANCE_DATE = l_date
419 and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
420 and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
421 and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
422 and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
423
424 cursor cursor_bank_account_id (currency varchar2) is
425 select ba.bank_account_id
426 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
427 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
428 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
429 and bb.BALANCE_DATE = l_date
430 and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
431 and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
432 and ba.CURRENCY_CODE = currency
433 and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
434
435 v_xml_1 xmltype;
436 v_xml_2 xmltype;
437 v_xml_3 xmltype;
438 v_exchange_rate number;
439 v_dummy number;
440
441 v_xml_seg1 xmltype;
442 v_xml_seg2 xmltype;
443 v_xml_seg3 xmltype;
444
445 n_loop number :=0;
446
447 begin
448 --fnd_file.put_line(FND_FILE.LOG, 'start single day reporting processing');
449 --fnd_file.put_line(fnd_file.log, 'as of date ' || to_char(to_date(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS')));
450 --fnd_file.put_line(fnd_file.log, 'reportint currency ' || p_reporting_currency);
451 --fnd_file.put_line(fnd_file.log, 'exchange rate type ' || p_exchange_rate_type);
452
453 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
454 -- CEP_STANDARD.init_security;
455
456 --if p_exchange_rate_date is not null then
457 -- p_exchange_rate_date := to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS');
458 --end if;
459
460 -- set l_date to p_as_of_date
461 l_date := to_date(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS');
462
463 -- get total_balance_flag, 1 when exchange rate exists for all currencies for query conditions
464 l_total_balance_flag := get_total_balance_flag(p_branch_party_id,p_bank_acct_id,p_bank_acct_currency,p_legal_entity_id,l_date,p_reporting_currency,p_exchange_rate_type,nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')));
465 --fnd_file.put_line(fnd_file.log, 'l_total_balance_flag '||to_char(l_total_balance_flag));
466
467 -- get bank branch name
468 if p_branch_party_id is not null then
469 select BANK_BRANCH_NAME
470 into l_Bank_Branch_Name
471 from ce_bank_branches_v
472 where BRANCH_PARTY_ID = p_branch_party_id;
473 end if;
474
475 -- get bank account name
476 if p_bank_acct_id is not null then
477 select BANK_ACCOUNT_NAME
478 into l_Bank_ACCT_NAME
479 from ce_bank_accounts
480 where BANK_ACCOUNT_ID = p_bank_acct_id;
481 end if;
482
483
484 -- get exchange rate date
485 if p_reporting_currency is null then
486 l_exchange_rate_date := null;
487 else
488 l_exchange_rate_date := to_char(to_date(nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'));
489 end if;
490
491 -- get legal entity name
492 if p_legal_entity_id is not null then
493 select name
494 into l_legal_entity_name
495 from CE_LE_BG_OU_VS_V
496 where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
497 end if;
498
499 -- loop through all currencies to generate the XML node BankAccttGroupByCurrency
500 open cursor_bank_currency_code;
501 loop
502 fetch cursor_bank_currency_code into v_currency_code;
503 exit when cursor_bank_currency_code%NOTFOUND;
504
505 -- reset xml_seg varialble to null
506 v_xml_seg1 := null;
507 v_xml_seg2 := null;
508 v_xml_seg3 := null;
509
510 -- get exchange rate, if exchange rate is -1, set all RC subtotal to XXX
511 v_exchange_rate := CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')) ,p_exchange_rate_type);
512
513 -- set all subtotals to null
514
515 v_LegerBalanceSubTAC := null;
516 v_AvailableBalanceSubTAC := null;
517 v_IntCalBalanceSubTAC := null;
518 v_OneDayFloatSubTAC := null;
519 v_TwoDayFloatSubTAC := null;
520 v_AvgLegerMTDSubTAC := null;
521 v_AvgLegerYTDSubTAC := null;
522 v_AvgAvailableMTDSubTAC := null;
526 v_AvailableBalanceSubTRC := null;
523 v_AvgAvailableYTDSubTAC := null;
524
525 v_LegerBalanceSubTRC := null;
527 v_IntCalBalanceSubTRC := null;
528 v_OneDayFloatSubTRC := null;
529 v_TwoDayFloatSubTRC := null;
530 v_AvgLegerMTDSubTRC := null;
531 v_AvgLegerYTDSubTRC := null;
532 v_AvgAvailableMTDSubTRC := null;
533 v_AvgAvailableYTDSubTRC := null;
534
535 n_loop := n_loop+1;
536 fnd_file.put_line(FND_FILE.LOG, 'n_loop ' || n_loop);
537 fnd_file.put_line(FND_FILE.LOG, 'currency ' || v_currency_code);
538
539 open cursor_bank_account_id(v_currency_code);
540 loop
541 fetch cursor_bank_account_id into v_bank_account_id;
542 exit when cursor_bank_account_id%NOTFOUND;
543
544
545 fnd_file.put_line(FND_FILE.LOG, 'bank account id ' || v_bank_account_id);
546
547 -- get the xml segment containing only the current account id info
548
549 select bb.LEDGER_BALANCE, bb.AVAILABLE_BALANCE, bb.VALUE_DATED_BALANCE, bb.ONE_DAY_FLOAT, bb.TWO_DAY_FLOAT, bb.AVERAGE_CLOSE_LEDGER_MTD, bb.AVERAGE_CLOSE_LEDGER_YTD, bb.AVERAGE_CLOSE_AVAILABLE_MTD, bb.AVERAGE_CLOSE_AVAILABLE_YTD
550 into v_LegerBalanceAC, v_AvailableBalanceAC, v_IntCalBalanceAC, v_OneDayFloatAC, v_TwoDayFloatAC, v_AvgLegerMTDAC, v_AvgLegerYTDAC, v_AvgAvailableMTDAC, v_AvgAvailableYTDAC
551 from ce_bank_acct_balances bb
552 where bb.BALANCE_DATE = l_date
553 and bb.BANK_ACCOUNT_ID = v_bank_account_id;
554
555 -- reset all dates to today
556
557 v_LegerBalance_Date := l_date;
558 v_AvailableBalance_Date := l_date;
559 v_IntCalBalance_Date := l_date;
560 v_OneDayFloat_Date := l_date;
561 v_TwoDayFloat_Date := l_date;
562 v_AvgLegerMTD_Date := l_date;
563 v_AvgLegerYTD_Date := l_date;
564 v_AvgAvailableMTD_Date := l_date;
565 v_AvgAvailableYTD_Date := l_date;
566
567
568
569 -- get v_LegerBalanceAC from the nearest date
570 if v_LegerBalanceAC is null then
571 get_balance_near_date(v_bank_account_id, l_date, v_LegerBalanceAC, v_LegerBalance_Date, 'LegerBalance');
572 end if;
573
574 -- set the v_LegerBalanceRC
575 select decode(v_exchange_rate, -1, v_dummy, v_LegerBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')),p_exchange_rate_type))
576 into v_LegerBalanceRC
577 from dual;
578
579 -- get v_AvailableBalanceAC from the nearest date
580 if v_AvailableBalanceAC is null then
581 get_balance_near_date(v_bank_account_id, l_date, v_AvailableBalanceAC, v_AvailableBalance_Date, 'AvailableBalance');
582 end if;
583
584 --fnd_file.put_line(FND_FILE.LOG, 'available balance ' || v_AvailableBalanceAC );
585
586 -- set the v_AvailableBalanceRC
587 select decode(v_exchange_rate, -1, v_dummy, v_AvailableBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')) ,p_exchange_rate_type))
588 into v_AvailableBalanceRC
589 from dual;
590
591 -- get v_IntCalBalanceAC from the nearest date
592 if v_IntCalBalanceAC is null then
593 get_balance_near_date(v_bank_account_id, l_date, v_IntCalBalanceAC, v_IntCalBalance_Date, 'IntCalBalance');
594 end if;
595
596 -- set the v_IntCalBalanceRC
597 select decode(v_exchange_rate, -1, v_dummy, v_IntCalBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')),p_exchange_rate_type))
598 into v_IntCalBalanceRC
599 from dual;
600
601 -- get v_OneDayFloatAC from the nearest date
602 if v_OneDayFloatAC is null then
603 get_balance_near_date(v_bank_account_id, l_date, v_OneDayFloatAC, v_OneDayFloat_Date, 'OneDayFloat');
604 end if;
605
606 -- set the v_OneDayFloatRC
607 select decode(v_exchange_rate, -1, v_dummy, v_OneDayFloatAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')) ,p_exchange_rate_type))
608 into v_OneDayFloatRC
609 from dual;
610
611 -- get v_TwoDayFloatAC from the nearest date
612 if v_TwoDayFloatAC is null then
613 get_balance_near_date(v_bank_account_id, l_date, v_TwoDayFloatAC, v_TwoDayFloat_Date, 'TwoDayFloat');
614 end if;
615
616 -- set the v_TwoDayFloatRC
617 select decode(v_exchange_rate, -1, v_dummy, v_TwoDayFloatAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
618 into v_TwoDayFloatRC
619 from dual;
620
621 -- get v_AvgLegerMTDAC from the nearest date
622 if v_AvgLegerMTDAC is null then
623 get_balance_near_date(v_bank_account_id, l_date, v_AvgLegerMTDAC, v_AvgLegerMTD_Date, 'AvgLegerMTD');
624 end if;
625
626 -- set the v_AvgLegerMTDRC
627 select decode(v_exchange_rate, -1, v_dummy, v_AvgLegerMTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
628 into v_AvgLegerMTDRC
629 from dual;
630
631 -- get v_AvgLegerYTDAC from the nearest date
632 if v_AvgLegerYTDAC is null then
633 get_balance_near_date(v_bank_account_id, l_date, v_AvgLegerYTDAC, v_AvgLegerYTD_Date, 'AvgLegerYTD');
634 end if;
635
636 -- set the v_AvgLegerYTDRC
637 select decode(v_exchange_rate, -1, v_dummy, v_AvgLegerYTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
638 into v_AvgLegerYTDRC
639 from dual;
640
641 -- get v_AvgAvailableMTDAC from the nearest date
642 if v_AvgAvailableMTDAC is null then
646 -- set the v_AvgAvailableMTDRC
643 get_balance_near_date(v_bank_account_id, l_date, v_AvgAvailableMTDAC, v_AvgAvailableMTD_Date, 'AvgAvailableMTD');
644 end if;
645
647 select decode(v_exchange_rate, -1, v_dummy, v_AvgAvailableMTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
648 into v_AvgAvailableMTDRC
649 from dual;
650
651 -- get v_AvgAvailableYTDAC from the nearest date
652 if v_AvgAvailableYTDAC is null then
653 get_balance_near_date(v_bank_account_id, l_date, v_AvgAvailableYTDAC, v_AvgAvailableYTD_Date, 'AvgAvailableYTD');
654 end if;
655
656 -- set the v_AvgAvailableYTDRC
657 select decode(v_exchange_rate, -1, v_dummy, v_AvgAvailableYTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
658 into v_AvgAvailableYTDRC
659 from dual;
660
661 -- set the subtotal values
662
663 -- ledgeBalance Sub Total
664 if v_LegerBalanceSubTAC is null AND v_LegerBalanceAC is not null then
665 v_LegerBalanceSubTAC := 0;
666 if v_exchange_rate <> -1 then
667 v_LegerBalanceSubTRC := 0;
668 end if;
669 end if;
670
671 if v_LegerBalanceSubTAC is not null AND v_LegerBalanceAC is not null then
672 v_LegerBalanceSubTAC := v_LegerBalanceSubTAC + v_LegerBalanceAC;
673 if v_exchange_rate <> -1 then
674 v_LegerBalanceSubTRC := v_LegerBalanceSubTRC + v_LegerBalanceRC;
675 end if;
676 if l_total_balance_flag = 1 then
677 if v_LegerBalanceTotal is null then
678 v_LegerBalanceTotal := 0;
679 end if;
680 v_LegerBalanceTotal := v_LegerBalanceTotal + v_LegerBalanceRC;
681 end if;
682 end if;
683
684 -- Available balance sub total
685 if v_AvailableBalanceSubTAC is null AND v_AvailableBalanceAC is not null then
686 v_AvailableBalanceSubTAC := 0;
687 if v_exchange_rate <> -1 then
688 v_AvailableBalanceSubTRC := 0;
689 end if;
690 end if;
691
692 if v_AvailableBalanceSubTAC is not null AND v_AvailableBalanceAC is not null then
693 v_AvailableBalanceSubTAC := v_AvailableBalanceSubTAC + v_AvailableBalanceAC;
694 if v_exchange_rate <> -1 then
695 v_AvailableBalanceSubTRC := v_AvailableBalanceSubTRC + v_AvailableBalanceRC;
696 end if;
697 if l_total_balance_flag = 1 then
698 if v_AvailableBalanceTotal is null then
699 v_AvailableBalanceTotal := 0;
700 end if;
701 v_AvailableBalanceTotal := v_AvailableBalanceTotal + v_AvailableBalanceRC;
702 end if;
703 end if;
704
705 -- Int calculated balance sub total
706 if v_IntCalBalanceSubTAC is null AND v_IntCalBalanceAC is not null then
707 v_IntCalBalanceSubTAC := 0;
708 if v_exchange_rate <> -1 then
709 v_IntCalBalanceSubTRC:= 0;
710 end if;
711 end if;
712
713 if v_IntCalBalanceSubTAC is not null AND v_IntCalBalanceAC is not null then
714 v_IntCalBalanceSubTAC := v_IntCalBalanceSubTAC + v_IntCalBalanceAC;
715 if v_exchange_rate <> -1 then
716 v_IntCalBalanceSubTRC := v_IntCalBalanceSubTRC + v_IntCalBalanceRC;
717 end if;
718 if l_total_balance_flag = 1 then
719 if v_IntCalBalanceTotal is null then
720 v_IntCalBalanceTotal := 0;
721 end if;
722 v_IntCalBalanceTotal := v_IntCalBalanceTotal + v_IntCalBalanceRC;
723 end if;
724 end if;
725
726 -- One Day Folat sub total
727 if v_OneDayFloatSubTAC is null AND v_OneDayFloatAC is not null then
728 v_OneDayFloatSubTAC := 0;
729 if v_exchange_rate <> -1 then
730 v_OneDayFloatSubTRC := 0;
731 end if;
732 end if;
733
734 if v_OneDayFloatSubTAC is not null AND v_OneDayFloatAC is not null then
735 v_OneDayFloatSubTAC := v_OneDayFloatSubTAC + v_OneDayFloatAC;
736 if v_exchange_rate <> -1 then
737 v_OneDayFloatSubTRC := v_OneDayFloatSubTRC + v_OneDayFloatRC;
738 end if;
739 if l_total_balance_flag = 1 then
740 if v_OneDayFloatTotal is null then
741 v_OneDayFloatTotal := 0;
742 end if;
743 v_OneDayFloatTotal := v_OneDayFloatTotal + v_OneDayFloatRC;
744 end if;
745 end if;
746
747 -- Two Day Folat sub total
748 if v_TwoDayFloatSubTAC is null AND v_TwoDayFloatAC is not null then
749 v_TwoDayFloatSubTAC := 0;
750 if v_exchange_rate <> -1 then
751 v_TwoDayFloatSubTRC := 0;
752 end if;
753 end if;
754
755 if v_TwoDayFloatSubTAC is not null AND v_TwoDayFloatAC is not null then
756 v_TwoDayFloatSubTAC := v_TwoDayFloatSubTAC + v_TwoDayFloatAC;
757 if v_exchange_rate <> -1 then
758 v_TwoDayFloatSubTRC := v_TwoDayFloatSubTRC + v_TwoDayFloatRC;
759 end if;
760 if l_total_balance_flag = 1 then
761 if v_TwoDayFloatTotal is null then
762 v_TwoDayFloatTotal := 0;
763 end if;
764 v_TwoDayFloatTotal := v_TwoDayFloatTotal + v_TwoDayFloatRC;
765 end if;
766 end if;
767
768 -- Avg Leger MTD Sub Total
769 if v_AvgLegerMTDSubTAC is null AND v_AvgLegerMTDAC is not null then
770 v_AvgLegerMTDSubTAC := 0;
771 if v_exchange_rate <> -1 then
772 v_AvgLegerMTDSubTRC := 0;
773 end if;
774 end if;
775
776 if v_AvgLegerMTDSubTAC is not null AND v_AvgLegerMTDAC is not null then
777 v_AvgLegerMTDSubTAC := v_AvgLegerMTDSubTAC + v_AvgLegerMTDAC;
778 if v_exchange_rate <> -1 then
779 v_AvgLegerMTDSubTRC := v_AvgLegerMTDSubTRC + v_AvgLegerMTDRC;
780 end if;
781 if l_total_balance_flag = 1 then
782 if v_AvgLegerMTDTotal is null then
783 v_AvgLegerMTDTotal := 0;
784 end if;
785 v_AvgLegerMTDTotal := v_AvgLegerMTDTotal + v_AvgLegerMTDRC;
789 -- Avg Leger YTD Sub Total
786 end if;
787 end if;
788
790 if v_AvgLegerYTDSubTAC is null AND v_AvgLegerYTDAC is not null then
791 v_AvgLegerYTDSubTAC := 0;
792 if v_exchange_rate <> -1 then
793 v_AvgLegerYTDSubTRC := 0;
794 end if;
795 end if;
796
797 if v_AvgLegerYTDSubTAC is not null AND v_AvgLegerYTDAC is not null then
798 v_AvgLegerYTDSubTAC := v_AvgLegerYTDSubTAC + v_AvgLegerYTDAC;
799 if v_exchange_rate <> -1 then
800 v_AvgLegerYTDSubTRC := v_AvgLegerYTDSubTRC + v_AvgLegerYTDRC;
801 end if;
802 if l_total_balance_flag = 1 then
803 if v_AvgLegerYTDTotal is null then
804 v_AvgLegerYTDTotal := 0;
805 end if;
806 v_AvgLegerYTDTotal := v_AvgLegerYTDTotal + v_AvgLegerYTDRC;
807 end if;
808 end if;
809
810 -- Avg Available MTD Sub T
811 if v_AvgAvailableMTDSubTAC is null AND v_AvgAvailableMTDAC is not null then
812 v_AvgAvailableMTDSubTAC:= 0;
813 if v_exchange_rate <> -1 then
814 v_AvgAvailableMTDSubTRC := 0;
815 end if;
816 end if;
817
818 if v_AvgAvailableMTDSubTAC is not null AND v_AvgAvailableMTDAC is not null then
819 v_AvgAvailableMTDSubTAC := v_AvgAvailableMTDSubTAC + v_AvgAvailableMTDAC;
820 if v_exchange_rate <> -1 then
821 v_AvgAvailableMTDSubTRC := v_AvgAvailableMTDSubTRC + v_AvgAvailableMTDRC;
822 end if;
823 if l_total_balance_flag = 1 then
824 if v_AvgAvailableMTDTotal is null then
825 v_AvgAvailableMTDTotal := 0;
826 end if;
827 v_AvgAvailableMTDTotal := v_AvgAvailableMTDTotal + v_AvgAvailableMTDRC;
828 end if;
829 end if;
830
831 -- Avg Available YTD Sub T
832 if v_AvgAvailableYTDSubTAC is null AND v_AvgAvailableYTDAC is not null then
833 v_AvgAvailableYTDSubTAC:= 0;
834 if v_exchange_rate <> -1 then
835 v_AvgAvailableYTDSubTRC := 0;
836 end if;
837 end if;
838
839 if v_AvgAvailableYTDSubTAC is not null AND v_AvgAvailableYTDAC is not null then
840 v_AvgAvailableYTDSubTAC := v_AvgAvailableYTDSubTAC + v_AvgAvailableYTDAC;
841 if v_exchange_rate <> -1 then
842 v_AvgAvailableYTDSubTRC := v_AvgAvailableYTDSubTRC + v_AvgAvailableYTDRC;
843 end if;
844 if l_total_balance_flag = 1 then
845 if v_AvgAvailableYTDTotal is null then
846 v_AvgAvailableYTDTotal := 0;
847 end if;
848 v_AvgAvailableYTDTotal := v_AvgAvailableYTDTotal + v_AvgAvailableYTDRC;
849 end if;
850 end if;
851
852 select xmlelement("BankAccount",
853 xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
854 ba.BANK_ACCOUNT_NAME as "BankAccountName",
855 ba.BANK_ACCOUNT_NUM as "BankAccountNum",
856 ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
857 bh.BANK_NAME as "BankName",
858 bh.BANK_BRANCH_NAME as "BankBranchName",
859 ba.CURRENCY_CODE as "BankAccountCurrency",
860 p_reporting_currency as "ReportingCurrency",
861 ba.MIN_TARGET_BALANCE as "TargetBalanceMinimum",
862 ba.MAX_TARGET_BALANCE as "TargetBalanceMaximum",
863 bb.BALANCE_DATE as "BalanceDate"),
864 xmlelement("LedgerBalanceAC", xmlattributes(v_LegerBalance_Date as "BalanceDate",
865 decode(v_LegerBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceAC, ba.CURRENCY_CODE)),
866 xmlelement("LedgerBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceRC, p_reporting_currency)),
867 xmlelement("AvailableBalanceAC", xmlattributes(v_AvailableBalance_Date as "BalanceDate", decode(v_AvailableBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceAC, ba.CURRENCY_CODE)),
868 xmlelement("AvailableBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceRC, p_reporting_currency)),
869 xmlelement("IntCalBalanceAC", xmlattributes(v_IntCalBalance_Date as "BalanceDate", decode(v_IntCalBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceAC, ba.CURRENCY_CODE)),
870 xmlelement("IntCalBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceRC, p_reporting_currency)),
871 xmlelement("OneDayFloatAC", xmlattributes(v_OneDayFloat_Date as "BalanceDate", decode(v_OneDayFloat_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatAC, ba.CURRENCY_CODE)),
872 xmlelement("OneDayFloatRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatRC, p_reporting_currency)),
873 xmlelement("TwoDayFloatAC", xmlattributes(v_TwoDayFloat_Date as "BalanceDate", decode(v_TwoDayFloat_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatAC, ba.CURRENCY_CODE)),
874 xmlelement("TwoDayFloatRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatRC, p_reporting_currency)),
875 xmlelement("AvgLegerMTDAC", xmlattributes(v_AvgLegerMTD_Date as "BalanceDate", decode(v_AvgLegerMTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDAC, ba.CURRENCY_CODE)),
876 xmlelement("AvgLegerMTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDRC, p_reporting_currency)),
877 xmlelement("AvgLegerYTDAC", xmlattributes(v_AvgLegerYTD_Date as "BalanceDate", decode(v_AvgLegerYTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDAC, ba.CURRENCY_CODE)),
878 xmlelement("AvgLegerYTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDRC, p_reporting_currency)),
879 xmlelement("AvgAvailableMTDAC", xmlattributes(v_AvgAvailableMTD_Date as "BalanceDate", decode(v_AvgAvailableMTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDAC, ba.CURRENCY_CODE)),
880 xmlelement("AvgAvailableMTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDRC, p_reporting_currency)),
884 into v_xml_seg1
881 xmlelement("AvgAvailableYTDAC", xmlattributes(v_AvgAvailableYTD_Date as "BalanceDate", decode(v_AvgAvailableYTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDAC, ba.CURRENCY_CODE)),
882 xmlelement("AvgAvailableYTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDRC, p_reporting_currency))
883 )
885 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
886 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
887 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
888 and bb.BALANCE_DATE = l_date
889 and ba.BANK_ACCOUNT_ID = v_bank_account_id;
890
891
892 -- concat all xml nodes
893 select xmlconcat(v_xml_seg1, v_xml_seg2)
894 into v_xml_seg3
895 from dual;
896
897 v_xml_seg2 := v_xml_seg3;
898 end loop;
899 close cursor_bank_account_id;
900
901 select
902 xmlelement("BankAcctGroupByCurrency", xmlattributes(v_currency_code as "AccountCurrency", v_exchange_rate as "ExchangeRate"),
903 v_xml_seg2,
904 xmlforest(CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceSubTAC, v_currency_code) as "SubTotalLedgerBalanceAC",
905 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceSubTAC, v_currency_code) as "SubTotalAvailableBalanceAC",
906 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceSubTAC, v_currency_code) as "SubTotalIntCalBalanceAC",
907 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatSubTAC, v_currency_code) as "SubTotalOneDayFloatAC",
908 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatSubTAC, v_currency_code) as "SubtotalTwoDayFloatAC",
909 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDSubTAC, v_currency_code) as "SubTotalAvgLegerMTDAC",
910 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDSubTAC, v_currency_code) as "SubTotalAvgLegerYTDAC",
911 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDSubTAC, v_currency_code) as "SubTotalAvgAvailableMTDAC",
912 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDSubTAC, v_currency_code) as "SubTotalAvgAvailableYTDAC",
913 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceSubTRC, p_reporting_currency) as "SubTotalLedgerBalanceRC",
914 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceSubTRC, p_reporting_currency) as "SubTotalAvailableBalanceRC",
915 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceSubTRC, p_reporting_currency) as "SubTotalIntCalBalanceRC",
916 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatSubTRC, p_reporting_currency) as "SubTotalOneDayFloatRC",
917 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatSubTRC, p_reporting_currency) as "SubtotalTwoDayFloatRC",
918 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDSubTRC, p_reporting_currency) as "SubTotalAvgLegerMTDRC",
919 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDSubTRC, p_reporting_currency) as "SubTotalAvgLegerYTDRC",
920 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDSubTRC, p_reporting_currency) as "SubTotalAvgAvailableMTDRC",
921 CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDSubTRC, p_reporting_currency) as "SubTotalAvgAvailableYTDRC"
922 )
923 )
924 into v_xml_1
925 from dual;
926
927 -- concat all xml nodes
928 select xmlconcat(v_xml_1, v_xml_2)
929 into v_xml_3
930 from dual;
931
932 v_xml_2 := v_xml_3;
933 end loop;
934 close cursor_bank_currency_code;
935
936 -- generate the xml as a whole
937 select
938 xmlelement("BankAccountList",
939 xmlelement("BankBranchName", l_Bank_Branch_Name),
940 xmlelement("BankAcctNum", l_Bank_ACCT_NAME),
941 xmlelement("BankAC", p_bank_acct_currency),
942 xmlelement("LegalEntity", l_legal_entity_name),
943 xmlelement("ReportingCurrency", p_reporting_currency),
944 xmlelement("ReportDate", sysdate),
945 xmlelement("AsOfDate", to_char(l_date)),
946 xmlelement("ExchangeRateType", p_exchange_rate_type),
947 xmlelement("ExchangeRateDate", l_exchange_rate_date),
948 xmlelement("TotalBalanceSummationFlag", l_total_balance_flag),
949 v_xml_2, -- xml node 'BankAccttGroupByCurrency'
950 xmlelement("LegerBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceTotal, p_reporting_currency)),
951 xmlelement("AvailableBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceTotal, p_reporting_currency)),
952 xmlelement("IntCalBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceTotal, p_reporting_currency)),
953 xmlelement("OneDayFloatTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatTotal, p_reporting_currency)),
954 xmlelement("TwoDayFloatTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatTotal, p_reporting_currency)),
955 xmlelement("AvgLegerMTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDTotal, p_reporting_currency)),
956 xmlelement("AvgLegerYTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDTotal, p_reporting_currency)),
957 xmlelement("AvgAvailableMTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDTotal, p_reporting_currency)),
958 xmlelement("AvgAvailableYTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDTotal, p_reporting_currency))
959 )
960 into l_xml
961 from dual;
962
963 l_xml_doc := l_xml.getClobVal();
964
965 CE_BANKACCT_BA_REPORT_UTIL.printClobOut(l_xml_doc);
966
967 /*
968 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
969 l_offset := 1;
970 l_amount := 32767;
971
972 loop
973 exit when l_length <= 0;
974 dbms_lob.read(l_xml_doc, l_amount, l_offset, l_buffer);
975 fnd_file.put(FND_FILE.OUTPUT, l_buffer);
976 l_length := l_length-l_amount;
977 l_offset := l_offset + l_amount;
978 end loop;
979 */
980 /*
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 dbms_output.put_line('End of data');
984 */
985 -- dbms_output.put_line(to_char(l_amount));
986
987 exception
991 fnd_file.put_line(fnd_file.log, xrate);
988 WHEN OTHERS THEN
989 xrate := -1;
990
992
993 end single_day_balance_report;
994
995
996
997 procedure range_day_balance_report
998 (errbuf OUT NOCOPY VARCHAR2,
999 retcode OUT NOCOPY NUMBER,
1000 p_branch_party_id varchar2,
1001 p_bank_acct_id varchar2,
1002 p_bank_acct_currency VARCHAR2,
1003 p_legal_entity_id varchar2,
1004 p_from_date varchar2,
1005 p_to_date varchar2,
1006 p_reporting_currency varchar2,
1007 p_exchange_rate_type varchar2,
1008 p_exchange_rate_date varchar2
1009 )
1010 is
1011 l_length number;
1012 l_offset number;
1013 l_amount number;
1014 l_buffer varchar2(32767);
1015 l_xml_doc clob;
1016 l_xml xmltype;
1017 l_exchange_rate number;
1018 xrate number;
1019 l_Bank_Branch_Name varchar2(100);
1020 l_Bank_ACCT_NAME varchar2(100);
1021 l_exchange_rate_date varchar2(200);
1022 l_legal_entity_name varchar2(200);
1023 l_Bank_Name varchar2(100);
1024 l_from_date varchar2(100);
1025 l_to_date varchar2(100);
1026 e_date_exp EXCEPTION;
1027
1028 begin
1029 --fnd_file.put_line(FND_FILE.LOG, 'hello world');
1030 --fnd_file.put_line(FND_FILE.LOG, p_as_of_date);
1031 --fnd_file.put_line(fnd_file.log, to_char(to_date(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS')));
1032 --fnd_file.put_line(fnd_file.log, p_branch_name);
1033
1034 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
1035 -- CEP_STANDARD.init_security;
1036
1037 if p_branch_party_id is not null then
1038 select BANK_BRANCH_NAME, BANK_NAME
1039 into l_Bank_Branch_Name, l_Bank_Name
1040 from ce_bank_branches_v
1041 where BRANCH_PARTY_ID = p_branch_party_id;
1042 end if;
1043
1044 if p_bank_acct_id is not null then
1045 select BANK_ACCOUNT_NAME
1046 into l_Bank_ACCT_NAME
1047 from ce_bank_accounts
1048 where BANK_ACCOUNT_ID = p_bank_acct_id;
1049 end if;
1050
1051 if p_reporting_currency is null then
1052 l_exchange_rate_date := null;
1053 else
1054 l_exchange_rate_date := to_char(to_date(nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'));
1055 end if;
1056
1057 if p_legal_entity_id is not null then
1058 select name
1059 into l_legal_entity_name
1060 from CE_LE_BG_OU_VS_V
1061 where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
1062 end if;
1063
1064 if (p_from_date is not null) and p_to_date is not null and p_from_date > p_to_date then
1065 RAISE e_date_exp;
1066 end if;
1067
1068
1069 select
1070 xmlelement("BankAccountList",
1071 xmlelement("BankName", l_Bank_Name),
1072 xmlelement("BankBranchName", l_Bank_Branch_Name),
1073 xmlelement("BankAcctName", l_Bank_ACCT_NAME),
1074 xmlelement("BankAC", p_bank_acct_currency),
1075 xmlelement("LegalEntity", l_legal_entity_name),
1076 xmlelement("ReportingCurrency", p_reporting_currency),
1077 xmlelement("ReportDate", sysdate),
1078 xmlelement("FromDate", to_char(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'))),
1079 xmlelement("ToDate", to_char(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'))),
1080 xmlelement("ExchangeRateType", p_exchange_rate_type),
1081 xmlelement("ExchangeRateDate", l_exchange_rate_date),
1082 xmlagg(xmlelement("BankAccount",
1083 xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
1084 ba.BANK_ACCOUNT_NAME as "BankAccountName",
1085 ba.BANK_ACCOUNT_NUM as "BankAccountNum",
1086 ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
1087 bh.BANK_NAME as "BankName",
1088 bh.BANK_BRANCH_NAME as "BankBranchName",
1089 ba.CURRENCY_CODE as "BankAccountCurrency",
1090 p_reporting_currency as "ReportingCurrency",
1091 CE_BANKACCT_BA_REPORT_UTIL.get_rate(ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "ExchangeRate",
1092 to_char(ba.MIN_TARGET_BALANCE, FND_CURRENCY.GET_FORMAT_MASK(ba.CURRENCY_CODE, 30)) as "TargetBalanceMinimum",
1093 to_char(ba.MAX_TARGET_BALANCE, FND_CURRENCY.GET_FORMAT_MASK(ba.CURRENCY_CODE, 30)) as "TargetBalanceMaximum",
1094 bb.BALANCE_DATE as "BalanceDate",
1095 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE) as "LedgerBalanceAC",
1096 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "LedgerBalanceRC",
1097 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE) as "AvailableBalanceAC",
1098 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvailableBalanceRC",
1099 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE) as "InterestCalBalAC",
1100 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "InterestCalBalRC",
1101 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE) as "OneDayFloatAC",
1102 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "OneDayFloatRC",
1103 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE) as "TwoDayFloatAC",
1104 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "TwoDayFloatRC",
1108 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseLedgerYTDRC",
1105 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE) as "AvgCloseLedgerMTDAC",
1106 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseLedgerMTDRC",
1107 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE) as "AvgCloseLedgerYTDAC",
1109 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE) as "AvgCloseAvailableMTDAC",
1110 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseAvailableMTDRC",
1111 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE) as "AvgCloseAvailableYTDAC",
1112 CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseAvailableYTDRC"
1113 ))))
1114 into l_xml
1115 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
1116 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
1117 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
1118 and bb.BALANCE_DATE between nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE) and nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE)
1119 and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
1120 and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
1121 and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
1122 and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID)
1123 and (bb.LEDGER_BALANCE is not null
1124 or bb.AVAILABLE_BALANCE is not null
1125 or bb.VALUE_DATED_BALANCE is not null
1126 or bb.ONE_DAY_FLOAT is not null
1127 or bb.TWO_DAY_FLOAT is not null
1128 or bb.AVERAGE_CLOSE_LEDGER_MTD is not null
1129 or bb.AVERAGE_CLOSE_LEDGER_YTD is not null
1130 or bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null
1131 or bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null);
1132
1133
1134 --select c.accountBalance
1135 --into l_xml
1136 --from ce_balance_reporting_xml_v c;
1137
1138 fnd_file.put_line(fnd_file.log, 'xml query end');
1139
1140 l_xml_doc := l_xml.getClobVal();
1141 CE_BANKACCT_BA_REPORT_UTIL.printClobOut(l_xml_doc);
1142
1143 /*
1144 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
1145 l_offset := 1;
1146 l_amount := 32767;
1147
1148 fnd_file.put_line(fnd_file.log, concat('clob length is ', l_length));
1149
1150 loop
1151 exit when l_length <= 0;
1152 dbms_lob.read(l_xml_doc, l_amount, l_offset, l_buffer);
1153 -- fnd_file.put_line(fnd_file.log, 'buffer read');
1154 fnd_file.put(FND_FILE.OUTPUT, l_buffer);
1155 -- fnd_file.put_line(fnd_file.log, 'buffer write');
1156 l_length := l_length-l_amount;
1157 l_offset := l_offset + l_amount;
1158 end loop;
1159 */
1160 /*
1161 EXCEPTION
1162 WHEN NO_DATA_FOUND THEN
1163 dbms_output.put_line('End of data');
1164
1165 -- dbms_output.put_line(to_char(l_amount));
1166 */
1167
1168 exception
1169
1170 WHEN e_date_exp then
1171 fnd_file.put_line(fnd_file.log, 'ERROR:from date can not be later than to date');
1172
1173 WHEN OTHERS THEN
1174 xrate := -1;
1175
1176 fnd_file.put_line(fnd_file.log, xrate);
1177 fnd_file.put_line(fnd_file.log, SQLCODE);
1178 fnd_file.put_line(fnd_file.log, SQLERRM);
1179 end;
1180
1181
1182 procedure act_proj_balance_report
1183 (errbuf OUT NOCOPY VARCHAR2,
1184 retcode OUT NOCOPY NUMBER,
1185 p_branch_party_id varchar2,
1186 p_bank_acct_id varchar2,
1187 p_bank_acct_currency VARCHAR2,
1188 p_legal_entity_id varchar2,
1189 p_from_date varchar2,
1190 p_to_date varchar2,
1191 p_actual_balance_type varchar2
1192 )
1193 is
1194 l_length number;
1195 l_offset number;
1196 l_amount number;
1197 l_buffer varchar2(32767);
1198 l_xml_doc clob;
1199 l_xml xmltype;
1200 l_exchange_rate number;
1201 xrate number;
1202 l_Bank_Branch_Name varchar2(100);
1203 l_Bank_ACCT_NAME varchar2(100);
1204 l_exchange_rate_date varchar2(200);
1205 l_legal_entity_name varchar2(200);
1206 l_Bank_Name varchar2(100);
1207 l_balance_type_meaning varchar2(200);
1208 e_date_exp EXCEPTION;
1209 begin
1210 --fnd_file.put_line(FND_FILE.LOG, 'hello world');
1211 --fnd_file.put_line(FND_FILE.LOG, p_as_of_date);
1212 --fnd_file.put_line(fnd_file.log, to_char(to_date(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS')));
1213 --fnd_file.put_line(fnd_file.log, p_branch_name);
1214
1215 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
1216 -- CEP_STANDARD.init_security;
1217
1218 if p_branch_party_id is not null then
1219 select BANK_BRANCH_NAME, BANK_NAME
1220 into l_Bank_Branch_Name, l_Bank_Name
1221 from ce_bank_branches_v
1222 where BRANCH_PARTY_ID = p_branch_party_id;
1223 end if;
1224
1225 if p_bank_acct_id is not null then
1226 select BANK_ACCOUNT_NAME
1227 into l_Bank_ACCT_NAME
1228 from ce_bank_accounts
1229 where BANK_ACCOUNT_ID = p_bank_acct_id;
1230 end if;
1231
1232 if p_legal_entity_id is not null then
1233 select name
1234 into l_legal_entity_name
1235 from CE_LE_BG_OU_VS_V
1236 where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
1237 end if;
1238
1242
1239 if (p_from_date is not null) and p_to_date is not null and p_from_date > p_to_date then
1240 RAISE e_date_exp;
1241 end if;
1243 select meaning
1244 into l_balance_type_meaning
1245 from ce_lookups
1246 where lookup_code = p_actual_balance_type
1247 and lookup_type = 'BANK_ACC_BAL_TYPE';
1248
1249 select
1250 xmlelement("BankAccountList",
1251 xmlelement("BankName", l_Bank_Name),
1252 xmlelement("BankBranchName", l_Bank_Branch_Name),
1253 xmlelement("BankAcctName", l_Bank_ACCT_NAME),
1254 xmlelement("BankAC", p_bank_acct_currency),
1255 xmlelement("LegalEntity", l_legal_entity_name),
1256 xmlelement("ReportDate", sysdate),
1257 xmlelement("FromDate", to_char(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'))),
1258 xmlelement("ToDate", to_char(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'))),
1259 xmlelement("ActualBalanceType", l_balance_type_meaning),
1260 xmlagg(xmlelement("BankAccount",
1261 xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
1262 ba.BANK_ACCOUNT_NAME as "BankAccountName",
1263 ba.BANK_ACCOUNT_NUM as "BankAccountNum",
1264 ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
1265 bh.BANK_NAME as "BankName",
1266 bh.BANK_BRANCH_NAME as "BankBranchName",
1267 ba.CURRENCY_CODE as "BankAccountCurrency",
1268 CE_BANKACCT_BA_REPORT_UTIL.get_balance(ba.MIN_TARGET_BALANCE,ba.CURRENCY_CODE) as "TargetBalanceMinimum",
1269 CE_BANKACCT_BA_REPORT_UTIL.get_balance(ba.MAX_TARGET_BALANCE, ba.CURRENCY_CODE) as "TargetBalanceMaximum",
1270 bb.BALANCE_DATE as "BalanceDate",
1271 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE) as "LedgerBalanceAC",
1272 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE) as "AvailableBalanceAC",
1273 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE) as "InterestCalBalAC",
1274 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE) as "OneDayFloatAC",
1275 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE) as "TwoDayFloatAC",
1276 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE) as "AvgCloseLedgerMTDAC",
1277 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE) as "AvgCloseLedgerYTDAC",
1278 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE) as "AvgCloseAvailableMTDAC",
1279 CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE) as "AvgCloseAvailableYTDAC",
1280 CE_BANKACCT_BA_REPORT_UTIL.get_balance(CE_BANKACCT_BA_REPORT_UTIL.get_variance(bb.BANK_ACCOUNT_ID, bb.BALANCE_DATE, p_actual_balance_type),ba.CURRENCY_CODE) as "Variance",
1281 l_balance_type_meaning as "ActualBalanceType",
1282 CE_BANKACCT_BA_REPORT_UTIL.get_balance(pb.PROJECTED_BALANCE,ba.CURRENCY_CODE) as "ProjectedBalance"
1283 ))))
1284 into l_xml
1285 from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb, ce_projected_balances pb
1286 where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
1287 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
1288 and bb.BALANCE_DATE between nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE) and nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE)
1289 and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
1290 and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
1291 and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
1292 and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID)
1293 and pb.BANK_ACCOUNT_ID (+) = bb.BANK_ACCOUNT_ID
1294 and pb.BALANCE_DATE (+) = bb.BALANCE_DATE;
1295
1296
1297
1298 --select c.accountBalance
1299 --into l_xml
1300 --from ce_balance_reporting_xml_v c;
1301
1302 l_xml_doc := l_xml.getClobVal();
1303
1304 CE_BANKACCT_BA_REPORT_UTIL.printClobOut(l_xml_doc);
1305 /*
1306 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
1307 l_offset := 1;
1308 l_amount := 32767;
1309
1310 loop
1311 exit when l_length <= 0;
1312 dbms_lob.read(l_xml_doc, l_amount, l_offset, l_buffer);
1313 fnd_file.put(FND_FILE.OUTPUT, l_buffer);
1314 l_length := l_length-l_amount;
1315 l_offset := l_offset + l_amount;
1316 end loop;
1317 */
1318 /*
1319 EXCEPTION
1320 WHEN NO_DATA_FOUND THEN
1321 dbms_output.put_line('End of data');
1322 */
1323 -- dbms_output.put_line(to_char(l_amount));
1324
1325 exception
1326
1327 WHEN e_date_exp then
1328 fnd_file.put_line(fnd_file.log, 'ERROR: from date can not be later than to date');
1329
1330 WHEN OTHERS THEN
1331 xrate := -1;
1332
1333 fnd_file.put_line(fnd_file.log, xrate);
1334 fnd_file.put_line(fnd_file.log, SQLCODE);
1335 fnd_file.put_line(fnd_file.log, SQLERRM);
1336
1337 end;
1338
1339
1340 END CE_BANK_ACCT_BALANCE_REPORT;
[Home] [Help]