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