[Home] [Help]
PACKAGE BODY: APPS.GL_FUNDS_AVAILABLE_PKG
Source
1 PACKAGE BODY gl_funds_available_pkg AS
2 /* $Header: glifundb.pls 120.16 2011/03/24 11:18:05 vensubra 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 )
236 and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
237 x_budget_version_id, bal.budget_version_id),-1)
238 and decode(actual_flag,
239 'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
240 x_encumbrance_type_id) = x_encumbrance_type_id;
241
242 /*
243 The packets cursor has the following explain plan:
244
245 Rows Execution Plan
246 ------- ---------------------------------------------------
247 0 SELECT STATEMENT HINT: RULE
248 165 SORT (AGGREGATE)
249 165 NESTED LOOPS
250 139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
251 197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
252 335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
253 */
254
255 CURSOR c_packets IS
256 SELECT
257 nvl(sum(decode(actual_flag, 'A',
258 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
259 nvl(sum(decode(actual_flag, 'B',
260 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
261 nvl(sum(decode(actual_flag, 'E',
262 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
263 nvl(sum(decode(actual_flag, 'E',
264 decode(encumbrance_type_id,
265 nvl(x_req_encumbrance_id,-2),
266 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
267 nvl(sum(decode(actual_flag, 'E',
268 decode(encumbrance_type_id,
269 nvl(x_po_encumbrance_id,-2),
270 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
271 FROM
272 gl_bc_packets pac, gl_bc_packet_arrival_order arr
273 WHERE
274 pac.code_combination_id = x_code_combination_id
275 and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
276 and pac.currency_code <> 'STAT'
277 and ((x_amount_type = 'PJTD' and
278 ((pac.period_year < x_period_year) or
279 (pac.period_num <= x_period_num and
280 pac.period_year = x_period_year))) or
281 (x_amount_type = 'PTD' and
282 pac.period_name = x_period_name) or
283 (x_amount_type = 'YTDE' and
284 pac.period_year = x_period_year and
285 pac.period_num <= x_period_num) or
286 (x_amount_type = 'QTDE' and
287 pac.period_year = x_period_year and
288 pac.quarter_num = x_quarter_num and
289 pac.period_num <= x_period_num))
290 and DECODE(pac.actual_flag , 'B', pac.budget_version_id,NVL(pac.funding_budget_version_id, x_budget_version_id)) = x_budget_version_id
291 and decode(actual_flag,
292 'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
293 x_encumbrance_type_id) = x_encumbrance_type_id
294 and pac.status_code = 'A'
295 and pac.ledger_id = arr.ledger_id
296 and pac.packet_id = arr.packet_id
297 and arr.ledger_id = x_ledger_id
298 and arr.affect_funds_flag = 'Y';
299
300 cursor c_pjtd(x_application_id NUMBER) is
301 select period_name, period_num, period_year, quarter_num
302 from gl_period_statuses
303 where application_id = x_application_id
304 and ledger_id = x_ledger_id
305 and period_name = (select latest_opened_period_name
306 from gl_ledgers
307 where ledger_id = x_ledger_id);
308
309 CURSOR c_account_category IS
310 SELECT st.account_category_code
311 FROM gl_summary_templates st
312 WHERE st.template_id = x_template_id
313 AND st.ledger_id = x_ledger_id;
314
315 budget_1 NUMBER;
316 budget_2 NUMBER;
317 actual_1 NUMBER;
318 actual_2 NUMBER;
319 encumbrance_1 NUMBER;
320 encumbrance_2 NUMBER;
321 req_encumbrance_amount_1 NUMBER;
322 req_encumbrance_amount_2 NUMBER;
323 po_encumbrance_amount_1 NUMBER;
324 po_encumbrance_amount_2 NUMBER;
325 acct_cat VARCHAR2(1);
326
327 BEGIN
328 IF (x_amount_type in ('QTDE', 'YTDE', 'PJTD')) THEN
329 IF (x_amount_type = 'YTDE') THEN
330 gl_period_statuses_pkg.select_year_1st_period(
331 101,
332 x_ledger_id,
333 x_period_year,
334 x_first_period_of_year_name );
335 END IF;
336
337 IF ( x_closing_status IN ( 'O', 'C', 'P' ) ) THEN
338 x_period_used_for_ext_actuals := x_period_name;
339 x_num_used_for_ext_actuals := x_period_num;
340 x_year_used_for_ext_actuals := x_period_year;
341 x_quarter_used_for_ext_actuals := x_quarter_num;
342 ELSIF (x_amount_type = 'QTDE') /* x_closing_status IN ( 'N', 'F' ) */
343 THEN
344 gl_period_statuses_pkg.get_extended_quarter(
345 101,
346 x_ledger_id,
347 x_period_year,
348 x_period_name,
349 x_period_set_name,
350 x_accounted_period_type,
351 x_period_used_for_ext_actuals,
352 x_num_used_for_ext_actuals,
353 x_year_used_for_ext_actuals,
354 x_quarter_used_for_ext_actuals );
355 ELSIF x_amount_type = 'YTDE' then /* x_closing_status IN ('N', 'F') */
356 gl_period_statuses_pkg.get_extended_year(
357 101,
358 x_ledger_id,
359 x_period_year,
360 x_accounted_period_type,
361 x_period_used_for_ext_actuals,
362 x_num_used_for_ext_actuals,
363 x_year_used_for_ext_actuals,
364 x_quarter_used_for_ext_actuals );
365 ELSIF x_amount_type = 'PJTD' then
366 open c_pjtd(101);
367
368 fetch c_pjtd
369 into x_period_used_for_ext_actuals,
370 x_num_used_for_ext_actuals,
371 x_year_used_for_ext_actuals,
372 x_quarter_used_for_ext_actuals;
373
374 close c_pjtd;
375
376 if ((x_period_year * 10000 + x_period_num) <=
377 (x_year_used_for_ext_actuals * 10000 + x_num_used_for_ext_actuals)) then
378 x_period_used_for_ext_actuals := x_period_name;
379 x_num_used_for_ext_actuals := x_period_num;
380 x_year_used_for_ext_actuals := x_period_year;
381 x_quarter_used_for_ext_actuals := x_quarter_num;
382 end if;
383
384 END IF;
385 ELSE
386 x_period_used_for_ext_actuals := x_period_name;
387 x_num_used_for_ext_actuals := x_period_num;
388 x_year_used_for_ext_actuals := x_period_year;
389 x_quarter_used_for_ext_actuals := x_quarter_num;
390 END IF;
391
392 OPEN c_balances;
393 FETCH c_balances INTO actual_1, budget_1, encumbrance_1,
394 req_encumbrance_amount_1, po_encumbrance_amount_1;
395 CLOSE c_balances;
396
397 OPEN c_packets;
398 FETCH c_packets INTO actual_2, budget_2, encumbrance_2,
399 req_encumbrance_amount_2, po_encumbrance_amount_2;
400 CLOSE c_packets;
401
402 x_budget := nvl(budget_1,0) + nvl(budget_2,0);
403 x_actual := nvl(actual_1,0) + nvl(actual_2,0);
404 x_encumbrance := nvl(encumbrance_1,0) + nvl(encumbrance_2,0);
405
406 -- Remember that Req encumbrances cannot be turned on unless PO
407 -- encumbrances are also turned on
408
409 IF ((x_encumbrance_type_id = -1) AND ( x_po_install_flag = 'Y' ) AND
410 (x_po_encumbrance_id IS NOT NULL )) THEN
411 x_po_encumbrance_amount := nvl(po_encumbrance_amount_1,0) +
412 nvl(po_encumbrance_amount_2,0);
413 IF (x_req_encumbrance_id IS NULL) THEN
414 x_req_encumbrance_amount := NULL;
415 ELSE
416 x_req_encumbrance_amount := nvl(req_encumbrance_amount_1,0) +
417 nvl(req_encumbrance_amount_2,0);
418 END IF;
419 x_other_encumbrance_amount := x_encumbrance -
420 nvl(x_req_encumbrance_amount,0) -
421 x_po_encumbrance_amount;
422 ELSE
423 x_req_encumbrance_amount := NULL;
424 x_po_encumbrance_amount := NULL;
425 x_other_encumbrance_amount := NULL;
426 END IF;
427
428 IF ( x_template_id IS NULL ) THEN
429 IF ( x_account_type IN ( 'C', 'D' ) ) THEN
430 x_funds_available := x_actual;
431 ELSE
432 x_funds_available := x_budget - x_encumbrance - x_actual;
433 END IF;
434 ELSE
435 OPEN c_account_category;
436 FETCH c_account_category INTO acct_cat;
437 CLOSE c_account_category;
438 IF ( acct_cat = 'B' ) THEN
439 x_funds_available := x_actual;
440 ELSIF ( acct_cat = 'P' ) THEN
441 x_funds_available := x_budget - x_encumbrance - x_actual;
442 END IF;
443 END IF;
444
445 EXCEPTION
446 WHEN app_exceptions.application_exception THEN
447 RAISE;
448 WHEN OTHERS THEN
449 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
450 fnd_message.set_token('PROCEDURE',
451 'gl_funds_available_pkg.calc_funds');
452 RAISE;
453 END calc_funds;
454
455 -- ***********************************************************************************
456
457 PROCEDURE calc_funds_period(
458 x_code_combination_id NUMBER,
459 x_account_type VARCHAR2,
460 x_template_id NUMBER,
461 x_ledger_id NUMBER,
462 x_currency_code VARCHAR2,
463 x_po_install_flag VARCHAR2,
464 x_accounted_period_type VARCHAR2,
465 x_period_set_name VARCHAR2,
466 x_period_name VARCHAR2,
467 x_period_num NUMBER,
468 x_quarter_num NUMBER,
469 x_period_year NUMBER,
470 x_closing_status VARCHAR2,
471 x_budget_version_id NUMBER,
472 x_encumbrance_type_id NUMBER,
473 x_req_encumbrance_id NUMBER,
474 x_po_encumbrance_id NUMBER,
475 x_budget IN OUT NOCOPY NUMBER,
476 x_encumbrance IN OUT NOCOPY NUMBER,
477 x_actual IN OUT NOCOPY NUMBER,
478 x_funds_available IN OUT NOCOPY NUMBER,
479 x_req_encumbrance_amount IN OUT NOCOPY NUMBER,
480 x_po_encumbrance_amount IN OUT NOCOPY NUMBER,
481 x_other_encumbrance_amount IN OUT NOCOPY NUMBER ) IS
482
483
484 x_first_period_of_year_name VARCHAR2(15);
485 x_period_used_for_ext_actuals VARCHAR2(15);
486 x_num_used_for_ext_actuals NUMBER;
487 x_year_used_for_ext_actuals NUMBER;
488 x_quarter_used_for_ext_actuals NUMBER;
489
490 /*
491 This balances SQL statement has the following explain plan:
492 Rows Execution Plan
493 ------- ---------------------------------------------------
494 0 SELECT STATEMENT HINT: RULE
495 2 SORT (AGGREGATE)
496 0 CONCATENATION
497 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
498 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
499 6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
500 7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
501 */
502
503
504 CURSOR c_balances IS
505 SELECT
506 nvl(sum(decode(actual_flag, 'A',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
507 nvl(sum(decode(actual_flag, 'B',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
508 nvl(sum(decode(actual_flag, 'E',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
509 nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
510 nvl(x_req_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0),
511 nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
512 nvl(x_po_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0)
513 FROM
514 gl_balances bal
515 WHERE
516 bal.ledger_id = x_ledger_id
517 and bal.code_combination_id = x_code_combination_id
518 --and bal.currency_code = decode(actual_flag, 'B', x_currency_code, bal.currency_code)
519 and bal.currency_code = x_currency_code
520 --and bal.currency_code <> 'STAT'
521 and bal.period_name = x_period_name
522 and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
523 x_budget_version_id, bal.budget_version_id),-1)
524 and decode(actual_flag,
525 'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
526 x_encumbrance_type_id) = x_encumbrance_type_id;
527
528 /*
529 The packets cursor has the following explain plan:
530
531 Rows Execution Plan
532 ------- ---------------------------------------------------
533 0 SELECT STATEMENT HINT: RULE
534 165 SORT (AGGREGATE)
535 165 NESTED LOOPS
536 139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
537 197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
538 335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
539 */
540
541 CURSOR c_packets IS
542 SELECT
543 nvl(sum(decode(actual_flag, 'A',
544 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
545 nvl(sum(decode(actual_flag, 'B',
546 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
547 nvl(sum(decode(actual_flag, 'E',
548 nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
549 nvl(sum(decode(actual_flag, 'E',
550 decode(encumbrance_type_id,
551 nvl(x_req_encumbrance_id,-2),
552 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
553 nvl(sum(decode(actual_flag, 'E',
554 decode(encumbrance_type_id,
555 nvl(x_po_encumbrance_id,-2),
556 nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
557 FROM
558 gl_bc_packets pac, gl_bc_packet_arrival_order arr
559 WHERE
560 pac.code_combination_id = x_code_combination_id
561 and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
562 and pac.currency_code <> 'STAT'
563 AND pac.period_name = x_period_name
564 and DECODE(pac.actual_flag , 'B', pac.budget_version_id,NVL(pac.funding_budget_version_id, x_budget_version_id)) = x_budget_version_id
565 and decode(actual_flag,
566 'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
567 x_encumbrance_type_id) = x_encumbrance_type_id
568 and pac.status_code = 'A'
569 and pac.ledger_id = arr.ledger_id
570 and pac.packet_id = arr.packet_id
571 and arr.ledger_id = x_ledger_id
572 and arr.affect_funds_flag = 'Y';
573
574
575 CURSOR c_account_category IS
576 SELECT st.account_category_code
577 FROM gl_summary_templates st
578 WHERE st.template_id = x_template_id
579 AND st.ledger_id = x_ledger_id;
580
581 budget_1 NUMBER;
582 budget_2 NUMBER;
583 actual_1 NUMBER;
584 actual_2 NUMBER;
585 encumbrance_1 NUMBER;
586 encumbrance_2 NUMBER;
587 req_encumbrance_amount_1 NUMBER;
588 req_encumbrance_amount_2 NUMBER;
589 po_encumbrance_amount_1 NUMBER;
590 po_encumbrance_amount_2 NUMBER;
591 acct_cat VARCHAR2(1);
592
593 BEGIN
594
595 OPEN c_balances;
596 FETCH c_balances INTO actual_1, budget_1, encumbrance_1,
597 req_encumbrance_amount_1, po_encumbrance_amount_1;
598 CLOSE c_balances;
599
600 OPEN c_packets;
601 FETCH c_packets INTO actual_2, budget_2, encumbrance_2,
602 req_encumbrance_amount_2, po_encumbrance_amount_2;
603 CLOSE c_packets;
604
605 x_budget := nvl(budget_1,0) + nvl(budget_2,0);
606 x_actual := nvl(actual_1,0) + nvl(actual_2,0);
607 x_encumbrance := nvl(encumbrance_1,0) + nvl(encumbrance_2,0);
608
609 -- Remember that Req encumbrances cannot be turned on unless PO
610 -- encumbrances are also turned on
611
612 IF ((x_encumbrance_type_id = -1) AND ( x_po_install_flag = 'Y' ) AND
613 (x_po_encumbrance_id IS NOT NULL )) THEN
614 x_po_encumbrance_amount := nvl(po_encumbrance_amount_1,0) +
615 nvl(po_encumbrance_amount_2,0);
616 IF (x_req_encumbrance_id IS NULL) THEN
617 x_req_encumbrance_amount := NULL;
618 ELSE
619 x_req_encumbrance_amount := nvl(req_encumbrance_amount_1,0) +
620 nvl(req_encumbrance_amount_2,0);
621 END IF;
622 x_other_encumbrance_amount := x_encumbrance -
623 nvl(x_req_encumbrance_amount,0) -
624 x_po_encumbrance_amount;
625 ELSE
626 x_req_encumbrance_amount := NULL;
627 x_po_encumbrance_amount := NULL;
628 x_other_encumbrance_amount := NULL;
629 END IF;
630
631 IF ( x_template_id IS NULL ) THEN
632 IF ( x_account_type IN ( 'C', 'D' ) ) THEN
633 x_funds_available := x_actual;
634 ELSE
635 x_funds_available := x_budget - x_encumbrance - x_actual;
636 END IF;
637 ELSE
638 OPEN c_account_category;
639 FETCH c_account_category INTO acct_cat;
640 CLOSE c_account_category;
641 IF ( acct_cat = 'B' ) THEN
642 x_funds_available := x_actual;
643 ELSIF ( acct_cat = 'P' ) THEN
644 x_funds_available := x_budget - x_encumbrance - x_actual;
645 END IF;
646 END IF;
647
648 EXCEPTION
649 WHEN app_exceptions.application_exception THEN
650 RAISE;
651 WHEN OTHERS THEN
652 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
653 fnd_message.set_token('PROCEDURE',
654 'gl_funds_available_pkg.calc_funds');
655 RAISE;
656 END calc_funds_period;
657
658
659 -- ***********************************************************************************
660
661 FUNCTION calc_funds(
662 p_ccid IN VARCHAR2,
663 p_template_id IN NUMBER,
664 p_ledger_id IN NUMBER,
665 p_period_name IN VARCHAR2,
666 p_currency_code IN VARCHAR2) RETURN NUMBER IS
667
668 CURSOR c_get_period_info(cp_ledger_id NUMBER, cp_period_name VARCHAR2) IS
669 SELECT period_num, quarter_num, period_year, closing_status
670 FROM Gl_Period_Statuses
671 WHERE application_id = 201
672 AND ledger_id = cp_ledger_id
673 AND period_name = cp_period_name;
674
675 CURSOR c_get_ledger_info(cp_ledger_id NUMBER) IS
676 SELECT period_set_name, accounted_period_type
677 FROM gl_ledgers
678 WHERE ledger_id = cp_ledger_id;
679
680 CURSOR c_get_template_info (cp_ledger_id NUMBER, cp_template_id NUMBER) IS
681 SELECT amount_type, funding_budget_version_id
682 FROM gl_summary_templates
683 WHERE ledger_id = cp_ledger_id
684 AND template_id = cp_template_id;
685
686 l_amt_type VARCHAR2(30);
687 l_amount_type VARCHAR2(30);
688 l_accounted_period_type VARCHAR2(30);
689 l_period_set_name VARCHAR2(30);
690 l_period_num NUMBER;
691 l_quarter_num NUMBER;
692 l_period_year NUMBER;
693 l_closing_status VARCHAR2(1);
694 l_budget_version_id NUMBER := NULL;
695 l_encumbrance_type_id NUMBER := -1;
696 l_req_encumbrance_id NUMBER := -1;
697 l_po_encumbrance_id NUMBER := -1;
698 l_budget NUMBER;
699 l_encumbrance NUMBER;
700 l_actual NUMBER;
701 l_funds_available NUMBER;
702 l_req_encumbrance_amount NUMBER;
703 l_po_encumbrance_amount NUMBER;
704 l_other_encumbrance_amount NUMBER;
705 BEGIN
706
707 open c_get_template_info(p_ledger_id, p_template_id);
708 fetch c_get_template_info into l_amt_type, l_budget_version_id;
709 close c_get_template_info;
710
711 open c_get_ledger_info(p_ledger_id);
712 fetch c_get_ledger_info into l_period_set_name, l_accounted_period_type;
713 close c_get_ledger_info;
714
715 open c_get_period_info(p_ledger_id, p_period_name);
716 fetch c_get_period_info into l_period_num, l_quarter_num, l_period_year, l_closing_status;
717 close c_get_period_info;
718
719 select decode(l_amt_type, 'YTD', 'YTDE', 'QTD', 'QTDE', l_amount_type)
720 into l_amount_type
721 from dual;
722
723 gl_funds_available_pkg.calc_funds(
724 x_amount_type => l_amount_type,
725 x_code_combination_id => p_ccid,
726 x_account_type => NULL,
727 x_template_id => p_template_id,
728 x_ledger_id => p_Ledger_id ,
729 x_currency_code => p_currency_code,
730 x_po_install_flag => 'Y',
731 x_accounted_period_type => l_accounted_period_type,
732 x_period_set_name => l_period_set_name,
733 x_period_name => p_period_name,
734 x_period_num => l_period_num,
735 x_quarter_num => l_quarter_num,
736 x_period_year => l_period_year,
737 x_closing_status => l_closing_status,
738 x_budget_version_id => l_budget_version_id,
739 x_encumbrance_type_id => l_encumbrance_type_id,
740 x_req_encumbrance_id => l_req_encumbrance_id,
741 x_po_encumbrance_id => l_po_encumbrance_id,
742 x_budget => l_budget,
743 x_encumbrance => l_encumbrance,
744 x_actual => l_actual,
745 x_funds_available => l_funds_available,
746 x_req_encumbrance_amount => l_req_encumbrance_amount,
747 x_po_encumbrance_amount => l_po_encumbrance_amount,
748 x_other_encumbrance_amount => l_other_encumbrance_amount );
749
750 return l_funds_available;
751 EXCEPTION
752 WHEN OTHERS THEN
753 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
754 fnd_message.set_token('PROCEDURE',
755 'gl_funds_available_pkg.calc_funds FUNCTION');
756 RAISE;
757 END calc_funds;
758
759 END gl_funds_available_pkg;