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