[Home] [Help]
PACKAGE BODY: APPS.FV_FUNDS_AVAILABLE_PKG
Source
1 PACKAGE BODY fv_funds_available_pkg AS
2 --$Header: FVIFUNDB.pls 120.19 2010/07/14 15:23:46 amaddula ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) := 'fv.plsql.fv_funds_available_pkg.';
5
6
7 /*******************************************************************/
8 /***** Variable Declaration For All Processes ******/
9 /*******************************************************************/
10 v_budget fv_fund_parameters.budget_authority%TYPE;
11 v_commitment fv_fund_parameters.unliquid_commitments%TYPE;
12 v_obligation fv_fund_parameters.unliquid_obligations%TYPE;
13 v_expenditure fv_fund_parameters.expended_amount%TYPE;
14
15 v_transfers_in fv_fund_parameters.transfers_in%TYPE;
16 v_transfers_out fv_fund_parameters.transfers_out%TYPE;
17 v_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
18 v_fund_value fv_fund_parameters.fund_value%TYPE;
19 v_acct_seg_name varchar2(30);
20 v_bal_seg_name varchar2(30);
21 bud_authority_type varchar2(50);
22
23 --Changed treasury_symbol to treasury_symbol_id #Bug :1575992
24 v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
25 temp_v_treasury_symbol_id varchar2(20) ; --Bug 1575992
26 v_statement varchar2(100);
27 err_message varchar2(300);
28 acct_flex_id fnd_flex_values.flex_value_id%TYPE;
29 v_ca_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
30 bud_child_value_low fnd_flex_value_hierarchies.child_flex_value_low%TYPE;
31 bud_child_value_high fnd_flex_value_hierarchies.child_flex_value_high%TYPE;
32 spd_child_value_low fnd_flex_value_hierarchies.child_flex_value_low%TYPE;
33 spd_child_value_high fnd_flex_value_hierarchies.child_flex_value_high%TYPE;
34 x_period_num gl_period_statuses.period_num%TYPE;
35 v_segment_low_name number;
36 v_segment_num number;
37 v_segment_low_value varchar2(25);
38 v_segment_high_name number;
39 v_segment_high_value varchar2(25);
40 v_child_budget number;
41 v_child_obligations number;
42 v_child_commitments number;
43 v_child_expenditure number;
44
45
46 v_child_transfers_in number;
47 v_child_transfers_out number;
48 v_delim_occur number;
49 v_diff number;
50 v_substr_to number;
51 v_substr_from number;
52 v_last_seg number;
53 parent_flag varchar2(1);
54 rollup_type varchar2(1);
55 v_compiled_value varchar2(1);
56 bal_seg_name_num number(2);
57 acct_ff_low varchar2(2000);
58 acct_ff_high varchar2(2000);
59 tot_budget_bal number;
60 tot_budget_packet number;
61 budget_bal number;
62 budget_packet number;
63 tot_transfers_bal number;
64 tot_transfers_packet number;
65 transfers_bal number;
66 transfers_packet number;
67 x_transfers_in number;
68 x_transfers_out number;
69 x_budget number;
70 segment_2 varchar2(25);
71 segment_3 varchar2(25);
72 segment_4 varchar2(25);
73 segment_5 varchar2(25);
74 segment_6 varchar2(25);
75 segment_7 varchar2(25);
76 acct_seg_name_num number(2);
77 --sob_id gl_balances.set_of_books_id%TYPE;
78 sob_id gl_balances.ledger_id%TYPE;
79 curr_code gl_balances.currency_code%TYPE;
80 pd_name gl_balances.period_name%TYPE;
81 v_period_year gl_period_statuses.period_year%TYPE;
82 v_period_status gl_period_statuses.closing_status%TYPE;
83 v_effective_period_num gl_period_statuses.effective_period_num%TYPE;
84
85
86 g_curr_code gl_balances.currency_code%TYPE;
87
88 TYPE t_segment_name_table IS TABLE OF varchar2(25)
89 INDEX BY BINARY_INTEGER;
90 v_segment_name t_segment_name_table;
91
92 TYPE t_value_low_table IS TABLE OF varchar2(25)
93 INDEX BY BINARY_INTEGER;
94 v_value_low t_value_low_table;
95
96 TYPE t_value_high_table IS TABLE OF varchar2(25)
97 INDEX BY BINARY_INTEGER;
98 v_value_high t_value_high_table;
99
100 CURSOR c_segment_info
101 IS
102 SELECT UPPER(application_column_name) application_column_name
103 FROM fnd_id_flex_segments
104 WHERE application_id = 101
105 AND id_flex_code = 'GL#'
106 AND id_flex_num = v_ca_id
107 ORDER BY segment_num;
108
109 --Changed from treasury_symbol to treasury_symbol_id #Bug:1575992
110 --Changed from v_treasury_symbol_id to temp_v_treasury_symbol_id for BUG 1575992
111 CURSOR c_treasury_fund_values
112 IS
113 SELECT fp.fund_value,
114 fp.budget_authority,
115 fp.unliquid_commitments,
116 fp.unliquid_obligations,
117 fp.expended_amount,
118 fp.transfers_in,
119 fp.transfers_out
120 FROM fv_fund_parameters fp
121 WHERE fp.set_of_books_id = sob_id
122 and decode(rollup_type,'F',fp.fund_value,'T',treasury_symbol_id)
123 between decode(rollup_type,
124 'T',temp_v_treasury_symbol_id,
125 'F',nvl(v_value_low(bal_seg_name_num),v_value_high(bal_seg_name_num))
126 ) --fund segment
127 and decode(rollup_type,
128 'T',temp_v_treasury_symbol_id,
129 'F',nvl(v_value_high(bal_seg_name_num),v_value_low(bal_seg_name_num))
130 ); --fund segment
131
132
133 /******************************************************************/
134 /* This procedure sums the spending accounts for the account */
135 /* passed to it. */
136 /******************************************************************/
137 PROCEDURE sum_children_spending
138 (v_parent_value IN fnd_flex_values.flex_value%TYPE,
139 parent_value_id IN fnd_flex_values.flex_value_id%TYPE,
140 grand_total OUT NOCOPY number)
141
142 IS
143 l_module_name VARCHAR2(200) := g_module_name || 'sum_children_spending';
144 balances number;
145 packets number;
146 tot_balances number;
147
148 v_bal_stmt VARCHAR2(5000) := NULL;
149 v_pac_stmt VARCHAR2(5000) := NULL;
150
151 tot_packets number;
152
153 CURSOR c_parent_value_spending IS
154 SELECT child_flex_value_low, child_flex_value_high
155 FROM fnd_flex_value_hierarchies
156 WHERE parent_flex_value = v_parent_value
157 AND flex_value_set_id = parent_value_id;
158 BEGIN
159
160 -- initialize totals
161 tot_balances := 0;
162 tot_packets := 0;
163 balances := 0;
164 packets := 0;
165 grand_total := 0;
166 parent_flag := 'N';
167
168 OPEN c_parent_value_spending;
169 LOOP
170 FETCH c_parent_value_spending into v_value_low(acct_seg_name_num),
171 v_value_high(acct_seg_name_num);
172 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SP CHILD_LOW = '||V_VALUE_LOW(ACCT_SEG_NAME_NUM));
174 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SP CHILD_HIGH = '||V_VALUE_HIGH(ACCT_SEG_NAME_NUM));
175 END IF;
176
177 v_bal_stmt := NULL;
178 v_pac_stmt := NULL;
179
180 v_bal_stmt :=
181 ' SELECT sum((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0)) +
182 (nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0)))
183 FROM gl_balances glb,
184 gl_code_combinations glc
185 WHERE glb.code_combination_id = glc.code_combination_id
186 AND glc.chart_of_accounts_id = ' ||v_ca_id ||
187 ' AND glb.ledger_id = '|| sob_id ||
188 ' AND glb.currency_code = '||''''|| curr_code ||''''||
189 ' AND glb.actual_flag = ''A''
190 AND glc.enabled_flag = ''Y''
191 AND glc.template_id IS NULL
192 AND glb.period_name = '||''''||pd_name||'''' ||
193 ' AND FV_BE_UTIL_PKG.has_segments_access( NULL, glc.code_combination_id ,
194 glc.chart_of_accounts_id ,'
195 || sob_id || ') = ''TRUE''' ;
196
197 v_pac_stmt :=
198 ' SELECT sum(nvl(accounted_dr,0) - nvl(accounted_cr,0))
199 FROM gl_bc_packets pac,
200 gl_code_combinations glc
201 WHERE pac.code_combination_id = glc.code_combination_id
202 AND glc.chart_of_accounts_id = '|| v_ca_id ||
203 ' AND glc.template_id IS NULL
204 AND pac.ledger_id = '|| sob_id ||
205 ' AND pac.currency_code = '||''''||curr_code||''''||
206 ' AND pac.actual_flag = ''A''
207 AND pac.status_code in (''A'',''P'')
208 AND pac.result_code like ''P%''
209 AND glc.enabled_flag = ''Y''
210 AND period_year = '||''''||v_period_year||''''||
211 ' AND period_num between 1 and '||x_period_num ||
212 ' AND FV_BE_UTIL_PKG.has_segments_access( NULL, glc.code_combination_id ,
213 glc.chart_of_accounts_id ,'
214 || sob_id || ') = ''TRUE''' ;
215
216
217 IF c_parent_value_spending%FOUND THEN
218 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
219 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BAL_SEG_NAME = '||BAL_SEG_NAME_NUM);
220 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SEGMENT2= '||SEGMENT_2);
221 END IF;
222
223 FOR app_col_name IN c_segment_info
224 LOOP
225 v_bal_stmt := v_bal_stmt||' AND '||app_col_name.application_column_name||' BETWEEN '||
226 ''''||NVL(v_value_low(substr(app_col_name.application_column_name,8,2)),'0')||''''||' AND '||
227 ''''||NVL(v_value_high(substr(app_col_name.application_column_name,8,2)),'zzzzzzzzzzzzzzzzzzzzzzzzz')||'''';
228 END LOOP;
229
230 EXECUTE IMMEDIATE v_bal_stmt INTO balances;
231 fv_utility.log_mesg('bal stmt: '||v_bal_stmt);
232 fv_utility.log_mesg('bal amount: '||balances);
233 tot_balances := nvl(tot_balances,0) + nvl(balances,0);
234
235 FOR app_col_name IN c_segment_info
236 LOOP
237 v_pac_stmt := v_pac_stmt||' AND '||app_col_name.application_column_name||' BETWEEN '||
238 ''''||NVL(v_value_low(substr(app_col_name.application_column_name,8,2)),'0')||''''||' AND '||
239 ''''||NVL(v_value_high(substr(app_col_name.application_column_name,8,2)),'zzzzzzzzzzzzzzzzzzzzzzzzz')||'''';
240
241 END LOOP;
242
243 EXECUTE IMMEDIATE v_pac_stmt INTO packets;
244 fv_utility.log_mesg('pac stmt: '||v_pac_stmt);
245 fv_utility.log_mesg('pac amount: '||packets);
246 tot_packets := nvl(tot_packets,0) + nvl(packets,0);
247
248 parent_flag := 'Y';
249 ELSIF c_parent_value_spending%NOTFOUND THEN
250 -- no child record found
251 IF parent_flag = 'N' THEN
252 fv_utility.log_mesg('No parent records');
253 -- since there are no children assign account to high and low
254 v_value_low(acct_seg_name_num) := v_parent_value;
255 v_value_high(acct_seg_name_num) := v_parent_value;
256
257 FOR app_col_name IN c_segment_info
258 LOOP
259 v_bal_stmt := v_bal_stmt||' AND '||app_col_name.application_column_name||' BETWEEN '||
260 ''''||NVL(v_value_low(substr(app_col_name.application_column_name,8,2)),'0')||''''||' AND '||
261 ''''||NVL(v_value_high(substr(app_col_name.application_column_name,8,2)),'zzzzzzzzzzzzzzzzzzzzzzzzz')||'''';
262
263 END LOOP;
264
265 EXECUTE IMMEDIATE v_bal_stmt INTO tot_balances;
266 fv_utility.log_mesg('bal stmt: '||v_bal_stmt);
267 fv_utility.log_mesg('bal amount: '||tot_balances);
268
269 FOR app_col_name IN c_segment_info
270 LOOP
271 v_pac_stmt := v_pac_stmt||' AND '||app_col_name.application_column_name||' BETWEEN '||
272 ''''||NVL(v_value_low(substr(app_col_name.application_column_name,8,2)),'0')||''''||' AND '||
273 ''''||NVL(v_value_high(substr(app_col_name.application_column_name,8,2)),'zzzzzzzzzzzzzzzzzzzzzzzzz')||'''';
274
275 END LOOP;
276
277 EXECUTE IMMEDIATE v_pac_stmt INTO tot_packets;
278 fv_utility.log_mesg('pac stmt: '||v_pac_stmt);
279 fv_utility.log_mesg('pac amount: '||tot_packets);
280
281 END IF;
282 exit;
283 END IF;
284 END LOOP;
285 CLOSE c_parent_value_spending;
286
287 begin
288 select substr(compiled_value_attributes,5,1)
289 into v_compiled_value
290 from fnd_flex_values
291 where flex_value = v_parent_value
292 and flex_value_set_id = parent_value_id;
293 if (v_compiled_value = 'C') then
294 grand_total := (nvl(tot_balances,0) + nvl(tot_packets,0))*-1;
295 else
296 grand_total := (nvl(tot_balances,0) + nvl(tot_packets,0));
297 end if;
298 EXCEPTION
299 WHEN NO_DATA_FOUND then
300 grand_total := (nvl(tot_balances,0) + nvl(tot_packets,0));
301 end;
302 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TOT_BAL = '||TOT_BALANCES);
304 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TOT_PACK = '||TOT_PACKETS);
305 END IF;
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 err_message := SQLERRM;
310 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message) ;
311 RAISE;
312
313 END sum_children_spending;
314
315 /******************************************************************/
316 /* This function finds the flex_value_id for the account being processed.*/
317 /******************************************************************/
318 FUNCTION get_flex_value_id(v_flex_value fnd_flex_values.flex_value%TYPE)
319 return number
320 IS
321 l_module_name VARCHAR2(200) := g_module_name || 'get_flex_value_id';
322 v_flex_value_id fnd_flex_values.flex_value_id%TYPE;
323 BEGIN
324 v_statement := 'L';
325 SELECT flex_value_set_id
326 INTO v_flex_value_id
327 FROM fnd_id_flex_segments_vl
328 WHERE id_flex_num = v_ca_id
329 AND application_id = 101
330 AND id_flex_code = 'GL#'
331 AND application_column_name = v_acct_seg_name;
332
333 RETURN v_flex_value_id;
334 EXCEPTION
335 when others then
336 err_message := 'GET_FLEX_VALUE_ID.L.'||sqlerrm;
337 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message) ;
338 app_exception.raise_exception;
339 END get_flex_value_id;
340
341 /******************************************************************/
342 /* This procedure parses the account flexfield low range using the*/
343 /* substringed segment name as the index. */
344 /******************************************************************/
345 PROCEDURE find_low_values
346 IS
347 l_module_name VARCHAR2(200) := g_module_name || 'find_low_values';
348 BEGIN
349 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
350 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN FIND_LOW_VALUES');
351 END IF;
352 -- delete all segments in table
353 FOR v_segment_low_name IN 1..30 LOOP
354 v_value_low(v_segment_low_name) := null;
355 END LOOP;
356
357 -- set counter to 1
358 v_delim_occur := 1;
359
360 v_diff := 0;
361
362 FOR low_segment_info_rec IN c_segment_info LOOP
363 v_segment_low_name :=
364 to_number(substr(low_segment_info_rec.application_column_name,8,2));
365 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
366 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_SEGMENT_LOW_NAME = '||V_SEGMENT_LOW_NAME);
367 END IF;
368
369 IF v_delim_occur = 1 THEN
370 -- first time through
371
372 v_statement := 'D';
373 SELECT instr(acct_ff_low,v_delimiter,1,1)
374 INTO v_substr_to
375 FROM dual;
376 -- find position of first delimiter
377
378 v_statement := 'E';
379 SELECT substr(acct_ff_low,1,v_substr_to - 1)
380 INTO v_segment_low_value
381 FROM dual;
382 -- get first segment value
383
384 v_value_low(v_segment_low_name) := v_segment_low_value;
385 -- assign value to table
386 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
387 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_LOW = '||V_VALUE_LOW(V_SEGMENT_LOW_NAME));
388 END IF;
389
390 v_delim_occur := v_delim_occur + 1;
391 ELSE
392
393 v_statement := 'F';
394 SELECT instr(acct_ff_low,v_delimiter,1,v_delim_occur)
395 INTO v_substr_from
396 FROM dual;
397 -- find position of delimiter occurance
398
399 v_diff := v_substr_from - v_substr_to;
400 --bug 7534611. changed the condition from = to < and find value for last segment
401
402 IF v_diff < 0 THEN
403 -- this is the last delimiter
404
405 v_last_seg := length(acct_ff_low) - v_substr_to;
406 v_value_low(v_segment_low_name) := substr(acct_ff_low,-1 * v_last_seg,v_last_seg);
407 --v_value_low(v_segment_low_name) := null;
408 ELSIF v_diff = 1 THEN
409 -- if = 1 then no value in segment so null
410 v_value_low(v_segment_low_name) := null;
411 ELSE
412
413 v_statement := 'G';
414 SELECT substr(acct_ff_low,v_substr_to + 1,v_diff - 1)
415 INTO v_segment_low_value
416 FROM dual;
417 -- find value of segment
418
419 v_value_low(v_segment_low_name) := v_segment_low_value;
420 END IF;
421
422 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_LOW = '||V_VALUE_LOW(V_SEGMENT_LOW_NAME));
424 END IF;
425 v_substr_to := v_substr_from;
426 v_delim_occur := v_delim_occur + 1;
427 END IF;
428 END LOOP;
429 EXCEPTION
430 WHEN OTHERS THEN
431 err_message := SQLERRM;
432 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message) ;
433 RAISE;
434 END find_low_values;
435
436 /******************************************************************/
437 /* This procedure parses the account flexfield high range using the*/
438 /* substringed segment name as the index. */
439 /******************************************************************/
440 PROCEDURE find_high_values
441 IS
442 l_module_name VARCHAR2(200) := g_module_name || 'find_high_values';
443 BEGIN
444 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
445 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FIND_HIGH_VALUES');
446 END IF;
447 -- delete all segments in table
448 FOR v_segment_high_name IN 1..30 LOOP
449 v_value_high(v_segment_high_name) := null;
450 END LOOP;
451
452 -- set counter to 1
453 v_delim_occur := 1;
454
455 v_diff := 0;
456
457 FOR high_segment_info_rec IN c_segment_info LOOP
458 v_segment_high_name :=
459 to_number(substr(high_segment_info_rec.application_column_name,8,2));
460 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
461 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_SEGMENT_HIGH_NAME = '||V_SEGMENT_HIGH_NAME);
462 END IF;
463
464 IF v_delim_occur = 1 THEN
465 -- first time through
466
467 v_statement := 'H';
468 SELECT instr(acct_ff_high,v_delimiter,1,1)
469 INTO v_substr_to
470 FROM dual;
471 -- find position of first delimiter
472
473 v_statement := 'I';
474 SELECT substr(acct_ff_high,1,v_substr_to - 1)
475 INTO v_segment_high_value
476 FROM dual;
477 -- get first segment value
478
479 v_value_high(v_segment_high_name) := v_segment_high_value;
480 -- assign value to table
481 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
482 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_HIGH = '||V_VALUE_HIGH(V_SEGMENT_HIGH_NAME));
483 END IF;
484
485 v_delim_occur := v_delim_occur + 1;
486 ELSE
487
488 v_statement := 'J';
489 SELECT instr(acct_ff_high,v_delimiter,1,v_delim_occur)
490 INTO v_substr_from
491 FROM dual;
492 -- find position of delimiter occurance
493
494 v_diff := v_substr_from - v_substr_to;
495
496 --bug 7534611. changed the condition from = to < and find value for last segment
497 IF v_diff < 0 THEN
498 -- this is the last delimiter
499 v_last_seg := length(acct_ff_high) - v_substr_to;
500 v_value_high(v_segment_high_name) := substr(acct_ff_high,-1 * v_last_seg,v_last_seg);
501 --v_value_high(v_segment_high_name) := null;
502 ELSIF v_diff = 1 THEN
503 -- if = 1 then no value in segment so null
504 v_value_high(v_segment_high_name) := null;
505 ELSE
506
507 v_statement := 'K';
508 SELECT substr(acct_ff_high,v_substr_to + 1,v_diff - 1)
509 INTO v_segment_high_value
510 FROM dual;
511 -- find value of segment
512
513 v_value_high(v_segment_high_name) := v_segment_high_value;
514 END IF;
515
516 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_HIGH = '||V_VALUE_HIGH(V_SEGMENT_HIGH_NAME));
518 END IF;
519 v_substr_to := v_substr_from;
520 v_delim_occur := v_delim_occur + 1;
521 END IF;
522 END LOOP;
523 EXCEPTION
524 WHEN OTHERS THEN
525 err_message := SQLERRM;
526 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message) ;
527 RAISE;
528 END find_high_values;
529 /******************************************************************/
530 /* This procedure finds user's segment names and substrings the */
531 /* name to use it as the index. The segment name is then stored */
532 /* as the value */
533 /******************************************************************/
534 PROCEDURE find_segments
535 IS
536 l_module_name VARCHAR2(200) := g_module_name || 'find_segments';
537 BEGIN
538 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN FIND_SEGMENTS');
540 END IF;
541 -- delete all segments in table
542
543 FOR v_segment_num IN 1..30 LOOP
544 v_segment_name(v_segment_num) := null;
545 END LOOP;
546
547 FOR segment_info_rec IN c_segment_info LOOP
548 v_segment_num := to_number(substr(segment_info_rec.application_column_name,8,2));
549
550 v_segment_name(v_segment_num) := segment_info_rec.application_column_name;
551
552 END LOOP;
553 EXCEPTION
554 WHEN OTHERS THEN
555 err_message := SQLERRM;
556 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message) ;
557 RAISE;
558 END find_segments;
559
560 -- **********************************************************************
561 PROCEDURE calc_funds(
562 x_acct_ff_low VARCHAR2,
563 x_acct_ff_high VARCHAR2,
564 x_rollup_type VARCHAR2,
565 x_treasury_symbol_id NUMBER,
566 x_balance_seg_name VARCHAR2,
567 x_acct_seg_name VARCHAR2,
568 x_set_of_books_id NUMBER,
569 x_currency_code VARCHAR2,
570 x_period_name VARCHAR2,
571 x_total_budget IN OUT NOCOPY NUMBER,
572 x_commitments IN OUT NOCOPY NUMBER,
573 x_obligations IN OUT NOCOPY NUMBER,
574 x_expenditure IN OUT NOCOPY NUMBER,
575 x_total IN OUT NOCOPY NUMBER,
576 x_funds_available IN OUT NOCOPY NUMBER) IS
577
578 l_module_name VARCHAR2(200) := g_module_name || 'calc_funds';
579
580 BEGIN
581 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN');
583 END IF;
584
585 -- reassign incoming variables
586 acct_ff_low := x_acct_ff_low;
587 acct_ff_high := x_acct_ff_high;
588 rollup_type := x_rollup_type;
589 sob_id := x_set_of_books_id;
590 curr_code := x_currency_code;
591 pd_name := x_period_name;
592 acct_seg_name_num := to_number(substr(x_acct_seg_name,8,2));
593 bal_seg_name_num := to_number(substr(x_balance_seg_name,8,2));
594 v_treasury_symbol_id := x_treasury_symbol_id; --Modified to Fix Bug 1575992
595 temp_v_treasury_symbol_id := v_treasury_symbol_id ; -- Modified for Bug 1575992
596 v_acct_seg_name := x_acct_seg_name;
597 v_bal_seg_name := x_balance_seg_name;
598
599 g_curr_code := x_currency_code;
600
601 v_statement := 'A';
602 -- find period number
603 --Bug#3377847
604 select distinct period_num,
605 period_year,
606 closing_status,
607 effective_period_num
608 into x_period_num,
609 v_period_year,
610 v_period_status,
611 v_effective_period_num
612 from gl_period_statuses
613 where period_name = x_period_name
614 and ledger_id = sob_id
615 and application_id = 101;
616
617 v_statement := 'A.A';
618 if (v_period_status = 'F') THEN
619 select gps1.period_name
620 into pd_name
621 from gl_period_statuses gps1
622 where gps1.application_id=101
623 and gps1.ledger_id=sob_id
624 and effective_period_num = (select max(effective_period_num)
625 from gl_period_statuses gps2
626 where gps2.application_id = gps1.application_id
627 and gps2.ledger_id = gps1.ledger_id
628 and gps2.closing_status <> 'F'
629 and gps2.effective_period_num <= v_effective_period_num);
630 end if;
631
632 v_statement := 'B';
633 -- find chart of account id
634 SELECT chart_of_accounts_id
635 INTO v_ca_id
636 FROM gl_ledgers_public_v
637 WHERE ledger_id = sob_id;
638
639 v_statement := 'C';
640 -- find delimiter
641 SELECT concatenated_segment_delimiter
642 INTO v_delimiter
643 FROM fnd_id_flex_structures
644 WHERE id_flex_num = v_ca_id
645 AND id_flex_code = 'GL#'
646 and application_id = 101;
647
648 -- find user's segment names
649 find_segments;
650
651 -- parse the accounting flexfield low range
652 find_low_values;
653
654 -- parse the accounting flexfield high range
655 find_high_values;
656
657 /* will use this later.
658 EXCEPTION
659 when no_data_found then
660 fnd_message.set_name('FV','FV_FAI_NO_ACCOUNTS_FOUND');
661 app_exception.raise_exception;
662 when others then
663 err_message := 'FV_FUNDS_AVAILABLE_PKG.CALC_FUNDS '||sqlerrm;
664 fnd_message.set_name('FV','FV_FAI_GENERAL');
665 fnd_message.set_token('MSG',err_message);
666 app_exception.raise_exception;
667 */
668
669 -- set sums to 0
670 x_total := 0;
671 x_funds_available := 0;
672 x_budget := 0;
673 x_total_budget := 0;
674 x_commitments := 0;
675 x_obligations := 0;
676 x_expenditure := 0;
677
678 x_transfers_in := 0;
679 x_transfers_out := 0;
680 v_child_budget := 0;
681 v_child_commitments := 0;
682 v_child_obligations := 0;
683 v_child_expenditure := 0;
684
685 v_child_transfers_in := 0;
686 v_child_transfers_out := 0;
687
688 FOR c_treasury_fund_value_rec IN c_treasury_fund_values LOOP
689 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'*********************************');
691 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FUND VALUE = '||V_VALUE_LOW(BAL_SEG_NAME_NUM));
692 END IF;
693 -- assign fund and process
694 v_fund_value := c_treasury_fund_value_rec.fund_value;
695 v_budget := nvl(c_treasury_fund_value_rec.budget_authority,'-1');
696 v_commitment := nvl(c_treasury_fund_value_rec.unliquid_commitments,'-1');
697 v_obligation := nvl(c_treasury_fund_value_rec.unliquid_obligations,'-1');
698 v_expenditure:= nvl(c_treasury_fund_value_rec.expended_amount,'-1');
699 v_transfers_in := nvl(c_treasury_fund_value_rec.transfers_in,'-1');
700 v_transfers_out := nvl(c_treasury_fund_value_rec.transfers_out,'-1');
701
702 -- assign the current fund to the segment index
703 v_value_low(bal_seg_name_num) := v_fund_value;
704 v_value_high(bal_seg_name_num) := v_fund_value;
705
706 -- Processing the budget authority account
707
708 -- Find flex_value_id
709 acct_flex_id := get_flex_value_id(v_budget);
710 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
711 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUDGET_ID = '||ACCT_FLEX_ID);
712 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUDGET = '||V_BUDGET);
713 END IF;
714
715 -- sum budget authority accounts (parent and children)
716 sum_children_spending(v_budget, acct_flex_id,v_child_budget);
717
718 -- running total for the fund(s)
719 x_budget := nvl(x_budget,0) + nvl(v_child_budget,0);
720
721
722 -- Processing the transfers-in account
723
724 -- Find flex_value_id
725 acct_flex_id := get_flex_value_id(v_transfers_in);
726 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
727 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_IN_ID = '||ACCT_FLEX_ID);
728 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_IN = '||V_TRANSFERS_IN);
729 END IF;
730
731 -- sum transfers_in accounts (parent and children)
732 sum_children_spending(v_transfers_in, acct_flex_id, v_child_transfers_in);
733
734 -- running total for the fund(s)
735 x_transfers_in := nvl(x_transfers_in,0) + nvl(v_child_transfers_in,0);
736
737 -- Processing the transfers-out account
738
739 -- Find flex_value_id
740 acct_flex_id := get_flex_value_id(v_transfers_out);
741 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_OUT_ID = '||ACCT_FLEX_ID);
743 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_OUT = '||V_TRANSFERS_OUT);
744 END IF;
745
746 -- sum transfers_out accounts (parent and children)
747 sum_children_spending(v_transfers_out,acct_flex_id,v_child_transfers_out);
748
749 -- running total for the fund(s)
750 x_transfers_out := nvl(x_transfers_out,0) + nvl(v_child_transfers_out,0);
751
752 -- Processing the commitment account
753
754 -- Find flex_value_id
755 acct_flex_id := get_flex_value_id(v_commitment);
756 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
757 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COMMIT_ID = '||ACCT_FLEX_ID);
758 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COMMIT = '||V_COMMITMENT);
759 END IF;
760
761 -- sum commitment accounts (parent and children)
762 sum_children_spending(v_commitment, acct_flex_id, v_child_commitments);
763
764 -- running total for the fund(s)
765 x_commitments := nvl(x_commitments,0) + nvl(v_child_commitments,0);
766
767 -- Processing the obligation account
768
769 -- Find flex_value_id
770 acct_flex_id := get_flex_value_id(v_obligation);
771 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBL_ID = '||ACCT_FLEX_ID);
773 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBL = '||V_OBLIGATION);
774 END IF;
775
776 -- sum obligation accounts (parent and children)
777 sum_children_spending(v_obligation, acct_flex_id, v_child_obligations);
778
779 -- running total for the fund(s)
780 x_obligations := nvl(x_obligations,0) + nvl(v_child_obligations,0);
781
782 -- Processing the expenditure account
783
784 -- Find flex_value_id
785 acct_flex_id := get_flex_value_id(v_expenditure);
786 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXP_ID = '||ACCT_FLEX_ID);
788 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXP = '||V_EXPENDITURE);
789 END IF;
790
791 -- sum expenditure accounts (parent and children)
792 sum_children_spending(v_expenditure, acct_flex_id, v_child_expenditure);
793
794 -- running total for the fund(s)
795 x_expenditure := nvl(x_expenditure,0) + nvl(v_child_expenditure,0);
796
797 END LOOP;
798
799 -- calculate totals
800 x_total_budget :=x_budget + x_transfers_in - x_transfers_out;
801 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
802 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'X_TOTAL_BUDGET = '||X_TOTAL_BUDGET);
803 END IF;
804 x_total :=nvl(x_commitments,0) + nvl(x_obligations,0) + nvl(x_expenditure,0);
805
806 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'X_TOTAL = '||X_TOTAL);
808 END IF;
809 x_funds_available :=nvl(x_total_budget,0) - nvl(x_total,0);
810
811 -- Bug 9721186
812 x_total_budget := x_funds_available + x_total ;
813
814 EXCEPTION
815 when others then
816 IF err_message is null then
817 err_message := 'CALC_FUNDS.'||v_statement||'.'||sqlerrm;
818 END IF;
819
820 IF c_treasury_fund_values%ISOPEN THEN
821 close c_treasury_fund_values;
822 END IF;
823 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message);
824 fnd_message.set_name('FV','FV_FAI_GENERAL');
825 fnd_message.set_token('MSG',err_message);
826 app_exception.raise_exception;
827
828 END calc_funds;
829
830
831 END fv_funds_available_pkg;
832