[Home] [Help]
PACKAGE BODY: APPS.GL_FUNDS_AVAILABLE_PKG
Source
1 PACKAGE BODY gl_funds_available_pkg AS
2 /* $Header: glifundb.pls 120.10.12010000.2 2008/08/13 12:23:28 kmotepal ship $ */
3
4
5
6 --
7 -- PUBLIC FUNCTIONS
8 --
9
10 FUNCTION is_po_installed RETURN BOOLEAN IS
11
12 CURSOR c_po_install IS
13 select 'found'
14 from fnd_product_installations fpi
15 where fpi.application_id = 201
16 and fpi.status = 'I';
17
18 dummy VARCHAR2( 100 );
19
20 BEGIN
21
22 OPEN c_po_install;
23 FETCH c_po_install INTO dummy;
24
25 IF c_po_install%FOUND THEN
26 CLOSE c_po_install;
27 RETURN( TRUE );
28 ELSE
29 CLOSE c_po_install;
30 RETURN( FALSE );
31 END IF;
32
33 EXCEPTION
34 WHEN app_exceptions.application_exception THEN
35 RAISE;
36 WHEN OTHERS THEN
37 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
38 fnd_message.set_token('PROCEDURE',
39 'gl_funds_available_pkg.is_po_installed');
40 RAISE;
41
42 END is_po_installed;
43
44 --************************************************************************
45
46 PROCEDURE current_budget_info (
47 x_ledger_id NUMBER,
48 x_budget_version_id IN OUT NOCOPY NUMBER,
49 x_budget_name IN OUT NOCOPY VARCHAR2,
50 x_bj_required_flag IN OUT NOCOPY VARCHAR2,
51 x_budget_type IN OUT NOCOPY VARCHAR2,
52 x_budget_status IN OUT NOCOPY VARCHAR2,
53 x_latest_opened_year IN OUT NOCOPY NUMBER,
54 x_first_valid_period IN OUT NOCOPY VARCHAR2,
55 x_last_valid_period IN OUT NOCOPY VARCHAR2,
56 x_is_po_installed IN OUT NOCOPY BOOLEAN,
57 x_req_id IN OUT NOCOPY NUMBER,
58 x_po_id IN OUT NOCOPY NUMBER,
59 x_req_name IN OUT NOCOPY VARCHAR2,
60 x_po_name IN OUT NOCOPY VARCHAR2,
61 x_oth_name IN OUT NOCOPY VARCHAR2) IS
62 BEGIN
63 gl_budget_utils_pkg.get_current_budget(
64 x_ledger_id,
65 x_budget_version_id,
66 x_budget_name,
67 x_bj_required_flag);
68
69 gl_budgets_pkg.select_columns(
70 x_budget_name,
71 x_ledger_id,
72 x_budget_type,
73 x_budget_status,
74 x_bj_required_flag,
75 x_latest_opened_year,
76 x_first_valid_period,
77 x_last_valid_period);
78
79 x_is_po_installed := gl_funds_available_pkg.is_po_installed;
80
81 IF (x_is_po_installed) THEN
82 gl_feeder_info_pkg.get_enc_id_and_name(
83 x_req_id,
84 x_po_id,
85 x_req_name,
86 x_po_name,
87 x_oth_name);
88 END IF;
89
90
91 EXCEPTION
92 WHEN app_exceptions.application_exception THEN
93 RAISE;
94 WHEN OTHERS THEN
95 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
96 fnd_message.set_token('PROCEDURE',
97 'gl_funds_available_pkg.current_budget_info');
98 RAISE;
99 END current_budget_info;
100
101 -- **********************************************************************
102
103 PROCEDURE calc_funds(
104 x_amount_type VARCHAR2,
105 x_code_combination_id NUMBER,
106 x_account_type VARCHAR2,
107 x_template_id NUMBER,
108 x_ledger_id NUMBER,
109 x_currency_code VARCHAR2,
110 x_po_install_flag VARCHAR2,
111 x_accounted_period_type VARCHAR2,
112 x_period_set_name VARCHAR2,
113 x_period_name VARCHAR2,
114 x_period_num NUMBER,
115 x_quarter_num NUMBER,
116 x_period_year NUMBER,
117 x_closing_status VARCHAR2,
118 x_budget_version_id NUMBER,
119 x_encumbrance_type_id NUMBER,
120 x_req_encumbrance_id NUMBER,
121 x_po_encumbrance_id NUMBER,
122 x_budget IN OUT NOCOPY NUMBER,
123 x_encumbrance IN OUT NOCOPY NUMBER,
124 x_actual IN OUT NOCOPY NUMBER,
125 x_funds_available IN OUT NOCOPY NUMBER,
126 x_req_encumbrance_amount IN OUT NOCOPY NUMBER,
127 x_po_encumbrance_amount IN OUT NOCOPY NUMBER,
128 x_other_encumbrance_amount IN OUT NOCOPY NUMBER ) IS
129
130
131 x_first_period_of_year_name VARCHAR2(15);
132 x_period_used_for_ext_actuals VARCHAR2(15);
133 x_num_used_for_ext_actuals NUMBER;
134 x_year_used_for_ext_actuals NUMBER;
135 x_quarter_used_for_ext_actuals NUMBER;
136
137 /*
138 This balances SQL statement has the following explain plan:
139 Rows Execution Plan
140 ------- ---------------------------------------------------
141 0 SELECT STATEMENT HINT: RULE
142 2 SORT (AGGREGATE)
143 0 CONCATENATION
144 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
145 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
146 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
147 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
148 */
149
150
151 CURSOR c_balances IS
152 SELECT
153 nvl(sum(decode(actual_flag, 'A',
154 decode(x_amount_type,
155 'PTD',
156 decode(period_name, x_period_name,
157 nvl(period_net_dr,0) - nvl(period_net_cr,0), 0),
158 'QTDE',
159 decode(period_name, x_period_used_for_ext_actuals,
160 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
161 nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
162 0),
163 'YTDE',
164 decode(bal.period_name, x_first_period_of_year_name,
165 -(nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0)), 0) +
166 decode(bal.period_name, x_period_used_for_ext_actuals,
167 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) +
168 nvl(period_net_dr,0) - nvl(period_net_cr,0),0),
169 'PJTD',
170 decode(period_name, x_period_used_for_ext_actuals,
171 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
172 nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),0),
173 0),0)),0),
174 nvl(sum(decode(actual_flag, 'B',
175 decode(period_name, x_period_name,
176 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
177 decode(x_amount_type,
178 'QTDE',
179 nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
180 'YTDE',
181 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
182 'PJTD',
183 nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
184 0),0),0)),0),
185 nvl(sum(decode(actual_flag, 'E',
186 decode(period_name, x_period_name,
187 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
188 decode(x_amount_type,
189 'QTDE',
190 nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
191 'YTDE',
192 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
193 'PJTD',
194 nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
195 0),0),0)),0),
196 nvl(sum(decode(actual_flag, 'E',
197 decode(period_name, x_period_name,
198 decode(encumbrance_type_id,
199 nvl(x_req_encumbrance_id,-2),
200 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
201 decode(x_amount_type,
202 'QTDE',
203 nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
204 'YTDE',
205 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
206 'PJTD',
207 nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
208 0),0),0),0)),0),
209 nvl(sum(decode(actual_flag, 'E',
210 decode(period_name, x_period_name,
211 decode(encumbrance_type_id,
212 nvl(x_po_encumbrance_id,-2),
213 nvl(period_net_dr,0) - nvl(period_net_cr,0) +
214 decode(x_amount_type,
215 'QTDE',
216 nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
217 'YTDE',
218 nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
219 'PJTD',
220 nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
221 0),0),0),0)),0)
222 FROM
223 gl_balances bal
224 WHERE
225 bal.ledger_id = x_ledger_id
226 and bal.code_combination_id = x_code_combination_id
227 --and bal.currency_code = decode(actual_flag, 'B', x_currency_code, bal.currency_code)
228 and bal.currency_code = x_currency_code
229 --and bal.currency_code <> 'STAT'
230 and bal.period_name in (x_period_name, decode(x_amount_type, 'YTDE',
231 x_period_used_for_ext_actuals,'QTDE', x_period_used_for_ext_actuals,
232 'PJTD', x_period_used_for_ext_actuals, x_period_name),
233 decode(x_amount_type, 'YTDE', x_first_period_of_year_name,
234 x_period_name))
235 and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
236 x_budget_version_id, bal.budget_version_id),-1)
237 and decode(actual_flag,
238 'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
239 x_encumbrance_type_id) = x_encumbrance_type_id;
240
241 /*
242 The packets cursor has the following explain plan:
243
244 Rows Execution Plan
245 ------- ---------------------------------------------------
246 0 SELECT STATEMENT HINT: RULE
247 165 SORT (AGGREGATE)
248 165 NESTED LOOPS
249 139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
250 197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
251 335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
252 */
253
254 CURSOR c_packets IS
255 SELECT
256 nvl(sum(decode(actual_flag, 'A',
257 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
258 nvl(sum(decode(actual_flag, 'B',
259 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
260 nvl(sum(decode(actual_flag, 'E',
261 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
262 nvl(sum(decode(actual_flag, 'E',
263 decode(encumbrance_type_id,
264 nvl(x_req_encumbrance_id,-2),
265 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
266 nvl(sum(decode(actual_flag, 'E',
267 decode(encumbrance_type_id,
268 nvl(x_po_encumbrance_id,-2),
269 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
270 FROM
271 gl_bc_packets pac, gl_bc_packet_arrival_order arr
272 WHERE
273 pac.code_combination_id = x_code_combination_id
274 and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
275 and pac.currency_code <> 'STAT'
276 and ((x_amount_type = 'PJTD' and
277 ((pac.period_year < x_period_year) or
278 (pac.period_num <= x_period_num and
279 pac.period_year = x_period_year))) or
280 (x_amount_type = 'PTD' and
281 pac.period_name = x_period_name) or
282 (x_amount_type = 'YTDE' and
283 pac.period_year = x_period_year and
284 pac.period_num <= x_period_num) or
285 (x_amount_type = 'QTDE' and
286 pac.period_year = x_period_year and
287 pac.quarter_num = x_quarter_num and
288 pac.period_num <= x_period_num))
289 and NVL(pac.funding_budget_version_id, x_budget_version_id) = x_budget_version_id
290 and decode(actual_flag,
291 'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
292 x_encumbrance_type_id) = x_encumbrance_type_id
293 and pac.status_code = 'A'
294 and pac.ledger_id = arr.ledger_id
295 and pac.packet_id = arr.packet_id
296 and arr.ledger_id = x_ledger_id
297 and arr.affect_funds_flag = 'Y';
298
299 cursor c_pjtd(x_application_id NUMBER) is
300 select period_name, period_num, period_year, quarter_num
301 from gl_period_statuses
302 where application_id = x_application_id
303 and ledger_id = x_ledger_id
304 and period_name = (select latest_opened_period_name
305 from gl_ledgers
306 where ledger_id = x_ledger_id);
307
308 CURSOR c_account_category IS
309 SELECT st.account_category_code
310 FROM gl_summary_templates st
311 WHERE st.template_id = x_template_id
312 AND st.ledger_id = x_ledger_id;
313
314 budget_1 NUMBER;
315 budget_2 NUMBER;
316 actual_1 NUMBER;
317 actual_2 NUMBER;
318 encumbrance_1 NUMBER;
319 encumbrance_2 NUMBER;
320 req_encumbrance_amount_1 NUMBER;
321 req_encumbrance_amount_2 NUMBER;
322 po_encumbrance_amount_1 NUMBER;
323 po_encumbrance_amount_2 NUMBER;
324 acct_cat VARCHAR2(1);
325
326 BEGIN
327 IF (x_amount_type in ('QTDE', 'YTDE', 'PJTD')) THEN
328 IF (x_amount_type = 'YTDE') THEN
329 gl_period_statuses_pkg.select_year_1st_period(
330 101,
331 x_ledger_id,
332 x_period_year,
333 x_first_period_of_year_name );
334 END IF;
335
336 IF ( x_closing_status IN ( 'O', 'C', 'P' ) ) THEN
337 x_period_used_for_ext_actuals := x_period_name;
338 x_num_used_for_ext_actuals := x_period_num;
342 THEN
339 x_year_used_for_ext_actuals := x_period_year;
340 x_quarter_used_for_ext_actuals := x_quarter_num;
341 ELSIF (x_amount_type = 'QTDE') /* x_closing_status IN ( 'N', 'F' ) */
343 gl_period_statuses_pkg.get_extended_quarter(
344 101,
345 x_ledger_id,
346 x_period_year,
347 x_period_name,
348 x_period_set_name,
349 x_accounted_period_type,
350 x_period_used_for_ext_actuals,
351 x_num_used_for_ext_actuals,
352 x_year_used_for_ext_actuals,
353 x_quarter_used_for_ext_actuals );
354 ELSIF x_amount_type = 'YTDE' then /* x_closing_status IN ('N', 'F') */
355 gl_period_statuses_pkg.get_extended_year(
356 101,
357 x_ledger_id,
358 x_period_year,
359 x_accounted_period_type,
360 x_period_used_for_ext_actuals,
361 x_num_used_for_ext_actuals,
362 x_year_used_for_ext_actuals,
363 x_quarter_used_for_ext_actuals );
364 ELSIF x_amount_type = 'PJTD' then
365 open c_pjtd(101);
366
367 fetch c_pjtd
368 into x_period_used_for_ext_actuals,
369 x_num_used_for_ext_actuals,
370 x_year_used_for_ext_actuals,
371 x_quarter_used_for_ext_actuals;
372
373 close c_pjtd;
374
375 if ((x_period_year * 10000 + x_period_num) <=
376 (x_year_used_for_ext_actuals * 10000 + x_num_used_for_ext_actuals)) then
377 x_period_used_for_ext_actuals := x_period_name;
378 x_num_used_for_ext_actuals := x_period_num;
379 x_year_used_for_ext_actuals := x_period_year;
380 x_quarter_used_for_ext_actuals := x_quarter_num;
381 end if;
382
383 END IF;
384 ELSE
385 x_period_used_for_ext_actuals := x_period_name;
386 x_num_used_for_ext_actuals := x_period_num;
387 x_year_used_for_ext_actuals := x_period_year;
388 x_quarter_used_for_ext_actuals := x_quarter_num;
389 END IF;
390
391 OPEN c_balances;
392 FETCH c_balances INTO actual_1, budget_1, encumbrance_1,
393 req_encumbrance_amount_1, po_encumbrance_amount_1;
394 CLOSE c_balances;
395
396 OPEN c_packets;
397 FETCH c_packets INTO actual_2, budget_2, encumbrance_2,
398 req_encumbrance_amount_2, po_encumbrance_amount_2;
399 CLOSE c_packets;
400
401 x_budget := nvl(budget_1,0) + nvl(budget_2,0);
402 x_actual := nvl(actual_1,0) + nvl(actual_2,0);
403 x_encumbrance := nvl(encumbrance_1,0) + nvl(encumbrance_2,0);
404
405 -- Remember that Req encumbrances cannot be turned on unless PO
406 -- encumbrances are also turned on
407
408 IF ((x_encumbrance_type_id = -1) AND ( x_po_install_flag = 'Y' ) AND
409 (x_po_encumbrance_id IS NOT NULL )) THEN
410 x_po_encumbrance_amount := nvl(po_encumbrance_amount_1,0) +
411 nvl(po_encumbrance_amount_2,0);
412 IF (x_req_encumbrance_id IS NULL) THEN
413 x_req_encumbrance_amount := NULL;
414 ELSE
415 x_req_encumbrance_amount := nvl(req_encumbrance_amount_1,0) +
416 nvl(req_encumbrance_amount_2,0);
417 END IF;
418 x_other_encumbrance_amount := x_encumbrance -
419 nvl(x_req_encumbrance_amount,0) -
420 x_po_encumbrance_amount;
421 ELSE
422 x_req_encumbrance_amount := NULL;
423 x_po_encumbrance_amount := NULL;
424 x_other_encumbrance_amount := NULL;
425 END IF;
426
427 IF ( x_template_id IS NULL ) THEN
428 IF ( x_account_type IN ( 'C', 'D' ) ) THEN
429 x_funds_available := x_actual;
430 ELSE
431 x_funds_available := x_budget - x_encumbrance - x_actual;
432 END IF;
433 ELSE
434 OPEN c_account_category;
435 FETCH c_account_category INTO acct_cat;
436 CLOSE c_account_category;
437 IF ( acct_cat = 'B' ) THEN
438 x_funds_available := x_actual;
439 ELSIF ( acct_cat = 'P' ) THEN
440 x_funds_available := x_budget - x_encumbrance - x_actual;
441 END IF;
442 END IF;
443
444 EXCEPTION
445 WHEN app_exceptions.application_exception THEN
446 RAISE;
447 WHEN OTHERS THEN
448 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
449 fnd_message.set_token('PROCEDURE',
450 'gl_funds_available_pkg.calc_funds');
451 RAISE;
452 END calc_funds;
453
454 -- ***********************************************************************************
455
456 PROCEDURE calc_funds_period(
457 x_code_combination_id NUMBER,
458 x_account_type VARCHAR2,
459 x_template_id NUMBER,
460 x_ledger_id NUMBER,
461 x_currency_code VARCHAR2,
462 x_po_install_flag VARCHAR2,
463 x_accounted_period_type VARCHAR2,
464 x_period_set_name VARCHAR2,
465 x_period_name VARCHAR2,
466 x_period_num NUMBER,
467 x_quarter_num NUMBER,
468 x_period_year NUMBER,
472 x_req_encumbrance_id NUMBER,
469 x_closing_status VARCHAR2,
470 x_budget_version_id NUMBER,
471 x_encumbrance_type_id NUMBER,
473 x_po_encumbrance_id NUMBER,
474 x_budget IN OUT NOCOPY NUMBER,
475 x_encumbrance IN OUT NOCOPY NUMBER,
476 x_actual IN OUT NOCOPY NUMBER,
477 x_funds_available IN OUT NOCOPY NUMBER,
478 x_req_encumbrance_amount IN OUT NOCOPY NUMBER,
479 x_po_encumbrance_amount IN OUT NOCOPY NUMBER,
480 x_other_encumbrance_amount IN OUT NOCOPY NUMBER ) IS
481
482
483 x_first_period_of_year_name VARCHAR2(15);
484 x_period_used_for_ext_actuals VARCHAR2(15);
485 x_num_used_for_ext_actuals NUMBER;
486 x_year_used_for_ext_actuals NUMBER;
487 x_quarter_used_for_ext_actuals NUMBER;
488
489 /*
490 This balances SQL statement has the following explain plan:
491 Rows Execution Plan
492 ------- ---------------------------------------------------
493 0 SELECT STATEMENT HINT: RULE
494 2 SORT (AGGREGATE)
495 0 CONCATENATION
496 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
497 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
498 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
499 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
500 */
501
502
503 CURSOR c_balances IS
504 SELECT
505 nvl(sum(decode(actual_flag, 'A',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
506 nvl(sum(decode(actual_flag, 'B',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
507 nvl(sum(decode(actual_flag, 'E',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
508 nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
509 nvl(x_req_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0),
510 nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
511 nvl(x_po_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0)
512 FROM
513 gl_balances bal
514 WHERE
515 bal.ledger_id = x_ledger_id
516 and bal.code_combination_id = x_code_combination_id
517 --and bal.currency_code = decode(actual_flag, 'B', x_currency_code, bal.currency_code)
518 and bal.currency_code = x_currency_code
519 --and bal.currency_code <> 'STAT'
520 and bal.period_name = x_period_name
521 and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
522 x_budget_version_id, bal.budget_version_id),-1)
523 and decode(actual_flag,
524 'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
525 x_encumbrance_type_id) = x_encumbrance_type_id;
526
527 /*
528 The packets cursor has the following explain plan:
529
530 Rows Execution Plan
531 ------- ---------------------------------------------------
532 0 SELECT STATEMENT HINT: RULE
533 165 SORT (AGGREGATE)
534 165 NESTED LOOPS
535 139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
536 197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
537 335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
538 */
539
540 CURSOR c_packets IS
541 SELECT
542 nvl(sum(decode(actual_flag, 'A',
543 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
544 nvl(sum(decode(actual_flag, 'B',
545 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
546 nvl(sum(decode(actual_flag, 'E',
547 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
548 nvl(sum(decode(actual_flag, 'E',
549 decode(encumbrance_type_id,
550 nvl(x_req_encumbrance_id,-2),
551 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
552 nvl(sum(decode(actual_flag, 'E',
553 decode(encumbrance_type_id,
554 nvl(x_po_encumbrance_id,-2),
555 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
556 FROM
557 gl_bc_packets pac, gl_bc_packet_arrival_order arr
558 WHERE
559 pac.code_combination_id = x_code_combination_id
560 and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
561 and pac.currency_code <> 'STAT'
562 AND pac.period_name = x_period_name
563 and NVL(pac.funding_budget_version_id, x_budget_version_id) = x_budget_version_id
564 and decode(actual_flag,
565 'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
566 x_encumbrance_type_id) = x_encumbrance_type_id
567 and pac.status_code = 'A'
568 and pac.ledger_id = arr.ledger_id
569 and pac.packet_id = arr.packet_id
570 and arr.ledger_id = x_ledger_id
571 and arr.affect_funds_flag = 'Y';
572
573
574 CURSOR c_account_category IS
575 SELECT st.account_category_code
576 FROM gl_summary_templates st
577 WHERE st.template_id = x_template_id
578 AND st.ledger_id = x_ledger_id;
579
580 budget_1 NUMBER;
581 budget_2 NUMBER;
582 actual_1 NUMBER;
586 req_encumbrance_amount_1 NUMBER;
583 actual_2 NUMBER;
584 encumbrance_1 NUMBER;
585 encumbrance_2 NUMBER;
587 req_encumbrance_amount_2 NUMBER;
588 po_encumbrance_amount_1 NUMBER;
589 po_encumbrance_amount_2 NUMBER;
590 acct_cat VARCHAR2(1);
591
592 BEGIN
593
594 OPEN c_balances;
595 FETCH c_balances INTO actual_1, budget_1, encumbrance_1,
596 req_encumbrance_amount_1, po_encumbrance_amount_1;
597 CLOSE c_balances;
598
599 OPEN c_packets;
600 FETCH c_packets INTO actual_2, budget_2, encumbrance_2,
601 req_encumbrance_amount_2, po_encumbrance_amount_2;
602 CLOSE c_packets;
603
604 x_budget := nvl(budget_1,0) + nvl(budget_2,0);
605 x_actual := nvl(actual_1,0) + nvl(actual_2,0);
606 x_encumbrance := nvl(encumbrance_1,0) + nvl(encumbrance_2,0);
607
608 -- Remember that Req encumbrances cannot be turned on unless PO
609 -- encumbrances are also turned on
610
611 IF ((x_encumbrance_type_id = -1) AND ( x_po_install_flag = 'Y' ) AND
612 (x_po_encumbrance_id IS NOT NULL )) THEN
613 x_po_encumbrance_amount := nvl(po_encumbrance_amount_1,0) +
614 nvl(po_encumbrance_amount_2,0);
615 IF (x_req_encumbrance_id IS NULL) THEN
616 x_req_encumbrance_amount := NULL;
617 ELSE
618 x_req_encumbrance_amount := nvl(req_encumbrance_amount_1,0) +
619 nvl(req_encumbrance_amount_2,0);
620 END IF;
621 x_other_encumbrance_amount := x_encumbrance -
622 nvl(x_req_encumbrance_amount,0) -
623 x_po_encumbrance_amount;
624 ELSE
625 x_req_encumbrance_amount := NULL;
626 x_po_encumbrance_amount := NULL;
627 x_other_encumbrance_amount := NULL;
628 END IF;
629
630 IF ( x_template_id IS NULL ) THEN
631 IF ( x_account_type IN ( 'C', 'D' ) ) THEN
632 x_funds_available := x_actual;
633 ELSE
634 x_funds_available := x_budget - x_encumbrance - x_actual;
635 END IF;
636 ELSE
637 OPEN c_account_category;
638 FETCH c_account_category INTO acct_cat;
639 CLOSE c_account_category;
640 IF ( acct_cat = 'B' ) THEN
641 x_funds_available := x_actual;
642 ELSIF ( acct_cat = 'P' ) THEN
643 x_funds_available := x_budget - x_encumbrance - x_actual;
644 END IF;
645 END IF;
646
647 EXCEPTION
648 WHEN app_exceptions.application_exception THEN
649 RAISE;
650 WHEN OTHERS THEN
651 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
652 fnd_message.set_token('PROCEDURE',
653 'gl_funds_available_pkg.calc_funds');
654 RAISE;
655 END calc_funds_period;
656
657
658 -- ***********************************************************************************
659
660 FUNCTION calc_funds(
661 p_ccid IN VARCHAR2,
662 p_template_id IN NUMBER,
663 p_ledger_id IN NUMBER,
664 p_period_name IN VARCHAR2,
665 p_currency_code IN VARCHAR2) RETURN NUMBER IS
666
667 CURSOR c_get_period_info(cp_ledger_id NUMBER, cp_period_name VARCHAR2) IS
668 SELECT period_num, quarter_num, period_year, closing_status
669 FROM Gl_Period_Statuses
670 WHERE application_id = 201
671 AND ledger_id = cp_ledger_id
672 AND period_name = cp_period_name;
673
674 CURSOR c_get_ledger_info(cp_ledger_id NUMBER) IS
675 SELECT period_set_name, accounted_period_type
676 FROM gl_ledgers
677 WHERE ledger_id = cp_ledger_id;
678
679 CURSOR c_get_template_info (cp_ledger_id NUMBER, cp_template_id NUMBER) IS
680 SELECT amount_type, funding_budget_version_id
681 FROM gl_summary_templates
682 WHERE ledger_id = cp_ledger_id
683 AND template_id = cp_template_id;
684
685 l_amt_type VARCHAR2(30);
686 l_amount_type VARCHAR2(30);
687 l_accounted_period_type VARCHAR2(30);
688 l_period_set_name VARCHAR2(30);
689 l_period_num NUMBER;
690 l_quarter_num NUMBER;
691 l_period_year NUMBER;
692 l_closing_status VARCHAR2(1);
693 l_budget_version_id NUMBER := NULL;
694 l_encumbrance_type_id NUMBER := -1;
695 l_req_encumbrance_id NUMBER := -1;
696 l_po_encumbrance_id NUMBER := -1;
697 l_budget NUMBER;
698 l_encumbrance NUMBER;
699 l_actual NUMBER;
700 l_funds_available NUMBER;
701 l_req_encumbrance_amount NUMBER;
702 l_po_encumbrance_amount NUMBER;
703 l_other_encumbrance_amount NUMBER;
704 BEGIN
705
706 open c_get_template_info(p_ledger_id, p_template_id);
707 fetch c_get_template_info into l_amt_type, l_budget_version_id;
708 close c_get_template_info;
709
710 open c_get_ledger_info(p_ledger_id);
714 open c_get_period_info(p_ledger_id, p_period_name);
711 fetch c_get_ledger_info into l_period_set_name, l_accounted_period_type;
712 close c_get_ledger_info;
713
715 fetch c_get_period_info into l_period_num, l_quarter_num, l_period_year, l_closing_status;
716 close c_get_period_info;
717
718 select decode(l_amt_type, 'YTD', 'YTDE', 'QTD', 'QTDE', l_amount_type)
719 into l_amount_type
720 from dual;
721
722 gl_funds_available_pkg.calc_funds(
723 x_amount_type => l_amount_type,
724 x_code_combination_id => p_ccid,
725 x_account_type => NULL,
726 x_template_id => p_template_id,
727 x_ledger_id => p_Ledger_id ,
728 x_currency_code => p_currency_code,
729 x_po_install_flag => 'Y',
730 x_accounted_period_type => l_accounted_period_type,
731 x_period_set_name => l_period_set_name,
732 x_period_name => p_period_name,
733 x_period_num => l_period_num,
734 x_quarter_num => l_quarter_num,
735 x_period_year => l_period_year,
736 x_closing_status => l_closing_status,
737 x_budget_version_id => l_budget_version_id,
738 x_encumbrance_type_id => l_encumbrance_type_id,
739 x_req_encumbrance_id => l_req_encumbrance_id,
740 x_po_encumbrance_id => l_po_encumbrance_id,
741 x_budget => l_budget,
742 x_encumbrance => l_encumbrance,
743 x_actual => l_actual,
744 x_funds_available => l_funds_available,
745 x_req_encumbrance_amount => l_req_encumbrance_amount,
746 x_po_encumbrance_amount => l_po_encumbrance_amount,
747 x_other_encumbrance_amount => l_other_encumbrance_amount );
748
749 return l_funds_available;
750 EXCEPTION
751 WHEN OTHERS THEN
752 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
753 fnd_message.set_token('PROCEDURE',
754 'gl_funds_available_pkg.calc_funds FUNCTION');
755 RAISE;
756 END calc_funds;
757
758 END gl_funds_available_pkg;