1 PACKAGE BODY CE_BANKACCT_BA_REPORT_UTIL AS
2 /* $Header: cexmlb1b.pls 120.5.12010000.2 2008/12/18 10:44:43 csutaria ship $ */
3
4 function get_rate
5 (
6 p_from_curr varchar2,
7 p_to_curr varchar2,
8 p_exchange_rate_date varchar2,
9 p_exchange_rate_type varchar2
10 )
11 return number
12 is
13 xrate number;
14 begin
15
16 -- no reporting currency
17 if p_to_curr is null then
18 return -1;
19 end if;
20
21 -- same currency
22 if p_to_curr = p_from_curr then
23 return 1;
24 end if;
25
26 xrate := GL_CURRENCY_API.get_rate(p_from_curr, p_to_curr, to_date(nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'), p_exchange_rate_type);
27
28 --fnd_file.put_line(fnd_file.log, p_from_curr);
29 --fnd_file.put_line(fnd_file.log, p_to_curr);
30 --fnd_file.put_line(fnd_file.log, xrate);
31
32
33 return xrate;
34
35 exception
36 WHEN OTHERS THEN
37 xrate := -1;
38 return xrate;
39 END;
40
41 function get_reporting_balance
42 (
43 p_balance number,
44 p_from_curr varchar2,
45 p_to_curr varchar2,
46 p_exchange_rate_date varchar2,
47 p_exchange_rate_type varchar2
48 )
49 return varchar2
50 is
51 v_rate number;
52 begin
53
54 v_rate := get_rate(p_from_curr, p_to_curr, p_exchange_rate_date, p_exchange_rate_type);
55
56 if v_rate = -1 then
57 return null;
58 else
59 if p_balance is null then
60 return null;
61 else
62 if p_balance >= 0 then
63 return to_char(p_balance*v_rate, FND_CURRENCY.GET_FORMAT_MASK(p_to_curr, 30)); else
64 return concat('-', to_char(-1*p_balance*v_rate, FND_CURRENCY.GET_FORMAT_MASK(p_to_curr, 30)));
65 end if;
66 end if;
67 end if;
68
69 END;
70
71 function get_balance
72 (
73 p_balance number,
74 p_from_curr varchar2
75 )
76 return varchar2
77 is
78
79 begin
80
81 if p_from_curr is null then
82 return null;
83 end if;
84
85 if p_balance is null then
86 return null;
87 else
88 if p_balance >= 0 then
89 return to_char(p_balance, FND_CURRENCY.GET_FORMAT_MASK(p_from_curr, 30)); else
90 return concat('-', to_char(-1*p_balance, FND_CURRENCY.GET_FORMAT_MASK(p_from_curr, 30)));
91 end if;
92 end if;
93 END;
94
95 function get_variance
96 (
97 p_bank_acct_id number,
98 p_balance_date date,
99 p_actual_balance_type varchar2
100 )
101 return number
102 is
103 v_variance number :=0;
104 v_record_num varchar2(30);
105 v_currency_code ce_bank_accounts.currency_code%type;
106 begin
107
108 if p_actual_balance_type = 'L' then
109 select count(1)
110 into v_record_num
111 from ce_bank_acct_balances bb, ce_projected_balances pb
112 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
113 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
114 and bb.BALANCE_DATE = p_balance_date
115 and pb.BALANCE_DATE = p_balance_date
116 and bb.LEDGER_BALANCE is not null
117 and pb.PROJECTED_BALANCE is not null;
118 if v_record_num = 1 then
119 select bb.LEDGER_BALANCE - pb.PROJECTED_BALANCE, ba.CURRENCY_CODE
120 into v_variance, v_currency_code
121 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
122 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
123 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
124 and bb.BALANCE_DATE = p_balance_date
125 and pb.BALANCE_DATE = p_balance_date
126 and bb.LEDGER_BALANCE is not null
127 and pb.PROJECTED_BALANCE is not null
128 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
129
130 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
131 return v_variance;
132 end if;
133 end if;
134
135 if p_actual_balance_type = 'I' then
136 select count(1)
137 into v_record_num
138 from ce_bank_acct_balances bb, ce_projected_balances pb
139 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
140 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
141 and bb.BALANCE_DATE = p_balance_date
142 and pb.BALANCE_DATE = p_balance_date
143 and bb.VALUE_DATED_BALANCE is not null
144 and pb.PROJECTED_BALANCE is not null;
145
146 if v_record_num = 1 then
147 select bb.VALUE_DATED_BALANCE - pb.PROJECTED_BALANCE, ba.CURRENCY_CODE
148 into v_variance, v_currency_code
149 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
150 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
151 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
152 and bb.BALANCE_DATE = p_balance_date
153 and pb.BALANCE_DATE = p_balance_date
154 and bb.VALUE_DATED_BALANCE is not null
155 and pb.PROJECTED_BALANCE is not null
156 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
157 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
158 return v_variance;
159 end if;
160 end if;
161
162 if p_actual_balance_type = 'C' then
163 select count(1)
164 into v_record_num
165 from ce_bank_acct_balances bb, ce_projected_balances pb
166 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
167 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
168 and bb.BALANCE_DATE = p_balance_date
169 and pb.BALANCE_DATE = p_balance_date
170 and bb.AVAILABLE_BALANCE is not null
171 and pb.PROJECTED_BALANCE is not null;
172 if v_record_num = 1 then
173 select bb.AVAILABLE_BALANCE - pb.PROJECTED_BALANCE, ba.currency_code
174 into v_variance, v_currency_code
175 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
176 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
177 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
178 and bb.BALANCE_DATE = p_balance_date
179 and pb.BALANCE_DATE = p_balance_date
180 and bb.AVAILABLE_BALANCE is not null
181 and pb.PROJECTED_BALANCE is not null
182 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
183 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
184 return v_variance;
185 end if;
186 end if;
187
188 if p_actual_balance_type = 'O' then
189 select count(1)
190 into v_record_num
191 from ce_bank_acct_balances bb, ce_projected_balances pb
192 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
193 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
194 and bb.BALANCE_DATE = p_balance_date
195 and pb.BALANCE_DATE = p_balance_date
196 and bb.ONE_DAY_FLOAT is not null
197 and pb.PROJECTED_BALANCE is not null;
198 if v_record_num = 1 then
199 select bb.ONE_DAY_FLOAT - pb.PROJECTED_BALANCE, ba.currency_code
200 into v_variance, v_currency_code
201 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
202 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
203 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
204 and bb.BALANCE_DATE = p_balance_date
205 and pb.BALANCE_DATE = p_balance_date
206 and bb.ONE_DAY_FLOAT is not null
207 and pb.PROJECTED_BALANCE is not null
208 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
209 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
210 return v_variance;
211 end if;
212 end if;
213
214 if p_actual_balance_type = 'T' then
215 select count(1)
216 into v_record_num
217 from ce_bank_acct_balances bb, ce_projected_balances pb
218 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
219 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
220 and bb.BALANCE_DATE = p_balance_date
221 and pb.BALANCE_DATE = p_balance_date
222 and bb.TWO_DAY_FLOAT is not null
223 and pb.PROJECTED_BALANCE is not null;
224 if v_record_num = 1 then
225 select bb.TWO_DAY_FLOAT - pb.PROJECTED_BALANCE, ba.currency_code
226 into v_variance, v_currency_code
227 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
228 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
229 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
230 and bb.BALANCE_DATE = p_balance_date
231 and pb.BALANCE_DATE = p_balance_date
232 and bb.TWO_DAY_FLOAT is not null
233 and pb.PROJECTED_BALANCE is not null
234 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
235 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
236 return v_variance;
237 end if;
238 end if;
239
240 if p_actual_balance_type = 'CLM' then
241 select count(1)
242 into v_record_num
243 from ce_bank_acct_balances bb, ce_projected_balances pb
244 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
245 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
246 and bb.BALANCE_DATE = p_balance_date
247 and pb.BALANCE_DATE = p_balance_date
248 and bb.AVERAGE_CLOSE_LEDGER_MTD is not null
249 and pb.PROJECTED_BALANCE is not null;
250 if v_record_num = 1 then
251 select bb.AVERAGE_CLOSE_LEDGER_MTD - pb.PROJECTED_BALANCE, ba.currency_code
252 into v_variance, v_currency_code
253 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
254 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
255 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
256 and bb.BALANCE_DATE = p_balance_date
257 and pb.BALANCE_DATE = p_balance_date
258 and bb.AVERAGE_CLOSE_LEDGER_MTD is not null
259 and pb.PROJECTED_BALANCE is not null
260 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
261 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
262 return v_variance;
263 end if;
264 end if;
265
266 if p_actual_balance_type = 'CLY' then
267 select count(1)
268 into v_record_num
269 from ce_bank_acct_balances bb, ce_projected_balances pb
270 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
271 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
272 and bb.BALANCE_DATE = p_balance_date
273 and pb.BALANCE_DATE = p_balance_date
274 and bb.AVERAGE_CLOSE_LEDGER_YTD is not null
275 and pb.PROJECTED_BALANCE is not null;
276 if v_record_num = 1 then
277 select bb.AVERAGE_CLOSE_LEDGER_YTD - pb.PROJECTED_BALANCE, ba.currency_code
278 into v_variance, v_currency_code
279 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
280 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
281 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
282 and bb.BALANCE_DATE = p_balance_date
283 and pb.BALANCE_DATE = p_balance_date
284 and bb.AVERAGE_CLOSE_LEDGER_YTD is not null
285 and pb.PROJECTED_BALANCE is not null
286 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
287 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
288 return v_variance;
289 end if;
290 end if;
291
292 if p_actual_balance_type = 'CAM' then
293 select count(1)
294 into v_record_num
295 from ce_bank_acct_balances bb, ce_projected_balances pb
296 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
297 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
298 and bb.BALANCE_DATE = p_balance_date
299 and pb.BALANCE_DATE = p_balance_date
300 and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null
301 and pb.PROJECTED_BALANCE is not null;
302 if v_record_num = 1 then
303 select bb.AVERAGE_CLOSE_AVAILABLE_MTD - pb.PROJECTED_BALANCE, ba.currency_code
304 into v_variance, v_currency_code
305 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
306 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
307 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
308 and bb.BALANCE_DATE = p_balance_date
309 and pb.BALANCE_DATE = p_balance_date
310 and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null
311 and pb.PROJECTED_BALANCE is not null
312 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
313 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
314 return v_variance;
315 end if;
316 end if;
317
318 if p_actual_balance_type = 'CAY' then
319 select count(1)
320 into v_record_num
321 from ce_bank_acct_balances bb, ce_projected_balances pb
322 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
323 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
324 and bb.BALANCE_DATE = p_balance_date
325 and pb.BALANCE_DATE = p_balance_date
326 and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null
327 and pb.PROJECTED_BALANCE is not null;
328 if v_record_num = 1 then
329 select bb.AVERAGE_CLOSE_AVAILABLE_YTD - pb.PROJECTED_BALANCE, ba.currency_code
330 into v_variance, v_currency_code
331 from ce_bank_acct_balances bb, ce_projected_balances pb, ce_bank_accounts ba
332 where bb.BANK_ACCOUNT_ID = p_bank_acct_id
333 and pb.BANK_ACCOUNT_ID = p_bank_acct_id
334 and bb.BALANCE_DATE = p_balance_date
335 and pb.BALANCE_DATE = p_balance_date
336 and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null
337 and pb.PROJECTED_BALANCE is not null
338 and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID;
339 -- return to_char(v_variance, FND_CURRENCY.GET_FORMAT_MASK(v_currency_code, 30));
340 return v_variance;
341 end if;
342 end if;
343
344 return null;
345
346 end;
347
348 PROCEDURE printClobOut(
349 aResult IN OUT NOCOPY CLOB
350 )
351 IS
352
353 l_posn_mark NUMBER := 1;
354 l_posn NUMBER := 1;
355 l_length NUMBER := 0;
356
357 l_max_linesize CONSTANT NUMBER := 32766;
358 l_buffer VARCHAR2(4000);
359
360 aSqlcode NUMBER;
361 aSqlerrm VARCHAR2(1000);
362 l_encoding VARCHAR2(300);
363
364 BEGIN
365
366 FND_FILE.PUT_LINE( FND_FILE.LOG,'BEGIN printClobOut');
367 -- Bug 7629651 added encoding information at the start of xml file
368 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="'||l_encoding ||'"?>' );
370 l_length:= dbms_lob.getlength(aResult);
371 l_posn_mark:= l_posn;
372
373 WHILE l_posn_mark < l_length LOOP
374
375 l_posn:= dbms_lob.instr(lob_loc => aResult,
376 pattern => '</',
377 offset => l_posn,
378 nth => 1
379 );
380
381 l_posn:= dbms_lob.instr(lob_loc => aResult,
382 pattern => '>',
383 offset => l_posn,
384 nth => 1
385 );
386
387
388 l_buffer:= dbms_lob.SUBSTR(lob_loc => aResult,
389 amount => l_posn - l_posn_mark + 1,
390 offset => l_posn_mark);
391
392 l_posn_mark:= l_posn + 1;
393 l_posn:= l_posn_mark;
394
395 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,l_buffer);
396 END LOOP;
397 FND_FILE.PUT_LINE( FND_FILE.LOG,' END printClobOut');
398
399 EXCEPTION
400 WHEN OTHERS
401 THEN
402 aSqlcode := SQLCODE;
403 aSqlerrm := SUBSTR(SQLERRM,1,300);
404 FND_FILE.PUT_LINE( FND_FILE.LOG,aSqlerrm);
405 END;
406
407
408 END CE_BANKACCT_BA_REPORT_UTIL;
[Home] [Help]