[Home] [Help]
PACKAGE BODY: APPS.GLF02220_PKG
Source
1 PACKAGE BODY glf02220_pkg AS
2 /* $Header: glfbdenb.pls 120.5 2005/05/05 02:04:37 kvora ship $ */
3
4 --
5 -- PRIVATE VARIABLES
6 --
7 maximum_allowed_amount_value NUMBER := 999999999999999999999999; -- (24)9
8
9 --
10 -- PRIVATE PROCEDURES AND FUNCTIONS
11 --
12
13 PROCEDURE get_approved_budget_amounts(
14 X_ledger_id IN NUMBER,
15 X_code_combination_id IN NUMBER,
16 X_currency_code IN VARCHAR2,
17 X_actual_flag IN VARCHAR2,
18 X_budget_version_id IN NUMBER,
19 X_period_year IN NUMBER,
20 X_start_period_num IN NUMBER,
21 X_end_period_num IN NUMBER,
22 X_dr_sign IN NUMBER,
23 X_data_found IN OUT NOCOPY NUMBER,
24 X_period1_amount IN OUT NOCOPY NUMBER,
25 X_period2_amount IN OUT NOCOPY NUMBER,
26 X_period3_amount IN OUT NOCOPY NUMBER,
27 X_period4_amount IN OUT NOCOPY NUMBER,
28 X_period5_amount IN OUT NOCOPY NUMBER,
29 X_period6_amount IN OUT NOCOPY NUMBER,
30 X_period7_amount IN OUT NOCOPY NUMBER,
31 X_period8_amount IN OUT NOCOPY NUMBER,
32 X_period9_amount IN OUT NOCOPY NUMBER,
33 X_period10_amount IN OUT NOCOPY NUMBER,
34 X_period11_amount IN OUT NOCOPY NUMBER,
35 X_period12_amount IN OUT NOCOPY NUMBER,
36 X_period13_amount IN OUT NOCOPY NUMBER) IS
37 CURSOR gbp IS
38 SELECT
39 nvl(sum(decode(PS.period_num,
40 floor((X_start_period_num-1)/13) * 13 + 1,
41 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
42 nvl(sum(decode(PS.period_num,
43 floor((X_start_period_num-1)/13) * 13 + 2,
44 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
45 nvl(sum(decode(PS.period_num,
46 floor((X_start_period_num-1)/13) * 13 + 3,
47 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
48 nvl(sum(decode(PS.period_num,
49 floor((X_start_period_num-1)/13) * 13 + 4,
50 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
51 nvl(sum(decode(PS.period_num,
52 floor((X_start_period_num-1)/13) * 13 + 5,
53 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
54 nvl(sum(decode(PS.period_num,
55 floor((X_start_period_num-1)/13) * 13 + 6,
56 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
57 nvl(sum(decode(PS.period_num,
58 floor((X_start_period_num-1)/13) * 13 + 7,
59 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
60 nvl(sum(decode(PS.period_num,
61 floor((X_start_period_num-1)/13) * 13 + 8,
62 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
63 nvl(sum(decode(PS.period_num,
64 floor((X_start_period_num-1)/13) * 13 + 9,
65 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
66 nvl(sum(decode(PS.period_num,
67 floor((X_start_period_num-1)/13) * 13 + 10,
68 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
69 nvl(sum(decode(PS.period_num,
70 floor((X_start_period_num-1)/13) * 13 + 11,
71 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
72 nvl(sum(decode(PS.period_num,
73 floor((X_start_period_num-1)/13) * 13 + 12,
74 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
75 nvl(sum(decode(PS.period_num,
76 floor((X_start_period_num-1)/13) * 13 + 13,
77 (nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
78 decode(max(BP.rowid), null, 0, 1)
79 FROM
80 GL_BC_PACKET_ARRIVAL_ORDER AO,
81 GL_BC_PACKETS BP,
82 GL_PERIOD_STATUSES PS
83 WHERE
84 PS.application_id = 101
85 AND PS.ledger_id = X_ledger_id
86 AND PS.period_year = X_period_year
87 AND PS.period_num BETWEEN X_start_period_num
88 AND X_end_period_num
89 AND BP.code_combination_id = X_code_combination_id
90 AND BP.period_name = PS.period_name||''
91 AND BP.actual_flag = X_actual_flag
92 AND nvl(BP.budget_version_id,-1) = decode(X_actual_flag,
93 'B', X_budget_version_id,
94 -1)
95 AND BP.currency_code = X_currency_code
96 AND BP.ledger_id = X_ledger_id
97 AND BP.status_code = 'A'
98 AND AO.packet_id = BP.packet_id
99 AND AO.ledger_id = X_ledger_id
100 AND AO.affect_funds_flag = 'Y';
101
102 BEGIN
103 OPEN gbp;
104 FETCH gbp INTO X_period1_amount,
105 X_period2_amount,
106 X_period3_amount,
107 X_period4_amount,
108 X_period5_amount,
109 X_period6_amount,
110 X_period7_amount,
111 X_period8_amount,
112 X_period9_amount,
113 X_period10_amount,
114 X_period11_amount,
115 X_period12_amount,
116 X_period13_amount,
117 X_data_found;
118
119 CLOSE gbp;
120
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 NULL;
124 WHEN OTHERS THEN
125 app_exception.raise_exception;
126
127 END get_approved_budget_amounts;
128
129 PROCEDURE get_balances_info(
130 X_ledger_id IN NUMBER,
131 X_bc_on IN VARCHAR2,
132 X_currency_code IN VARCHAR2,
133 X_budget_version_id IN NUMBER,
134 X_period_year IN NUMBER,
135 X_start_period_num IN NUMBER,
136 X_end_period_num IN NUMBER,
137 X_status_num IN NUMBER,
138 X_code_combination_id IN NUMBER,
139 X_row_id IN OUT NOCOPY VARCHAR2,
140 X_DR_FLAG IN OUT NOCOPY VARCHAR2,
141 X_STATUS_NUMBER IN OUT NOCOPY NUMBER,
142 X_LAST_UPDATE_DATE IN OUT NOCOPY DATE,
143 X_LAST_UPDATED_BY IN OUT NOCOPY NUMBER,
144 X_LAST_UPDATE_LOGIN IN OUT NOCOPY NUMBER,
145 X_CREATION_DATE IN OUT NOCOPY DATE,
146 X_CREATED_BY IN OUT NOCOPY NUMBER,
147 X_ACCOUNT_TYPE IN OUT NOCOPY VARCHAR2,
148 X_CC_ACTIVE_FLAG IN OUT NOCOPY VARCHAR2,
149 X_CC_BUDGETING_ALLOWED_FLAG IN OUT NOCOPY VARCHAR2,
150 X_PERIOD1_AMOUNT_QRY IN OUT NOCOPY NUMBER,
151 X_PERIOD2_AMOUNT_QRY IN OUT NOCOPY NUMBER,
152 X_PERIOD3_AMOUNT_QRY IN OUT NOCOPY NUMBER,
153 X_PERIOD4_AMOUNT_QRY IN OUT NOCOPY NUMBER,
154 X_PERIOD5_AMOUNT_QRY IN OUT NOCOPY NUMBER,
155 X_PERIOD6_AMOUNT_QRY IN OUT NOCOPY NUMBER,
156 X_PERIOD7_AMOUNT_QRY IN OUT NOCOPY NUMBER,
157 X_PERIOD8_AMOUNT_QRY IN OUT NOCOPY NUMBER,
158 X_PERIOD9_AMOUNT_QRY IN OUT NOCOPY NUMBER,
159 X_PERIOD10_AMOUNT_QRY IN OUT NOCOPY NUMBER,
160 X_PERIOD11_AMOUNT_QRY IN OUT NOCOPY NUMBER,
161 X_PERIOD12_AMOUNT_QRY IN OUT NOCOPY NUMBER,
162 X_PERIOD13_AMOUNT_QRY IN OUT NOCOPY NUMBER,
163 X_OLD_PERIOD1_AMOUNT_QRY IN OUT NOCOPY NUMBER,
164 X_OLD_PERIOD2_AMOUNT_QRY IN OUT NOCOPY NUMBER,
165 X_OLD_PERIOD3_AMOUNT_QRY IN OUT NOCOPY NUMBER,
166 X_OLD_PERIOD4_AMOUNT_QRY IN OUT NOCOPY NUMBER,
167 X_OLD_PERIOD5_AMOUNT_QRY IN OUT NOCOPY NUMBER,
168 X_OLD_PERIOD6_AMOUNT_QRY IN OUT NOCOPY NUMBER,
169 X_OLD_PERIOD7_AMOUNT_QRY IN OUT NOCOPY NUMBER,
170 X_OLD_PERIOD8_AMOUNT_QRY IN OUT NOCOPY NUMBER,
171 X_OLD_PERIOD9_AMOUNT_QRY IN OUT NOCOPY NUMBER,
172 X_OLD_PERIOD10_AMOUNT_QRY IN OUT NOCOPY NUMBER,
173 X_OLD_PERIOD11_AMOUNT_QRY IN OUT NOCOPY NUMBER,
174 X_OLD_PERIOD12_AMOUNT_QRY IN OUT NOCOPY NUMBER,
175 X_OLD_PERIOD13_AMOUNT_QRY IN OUT NOCOPY NUMBER,
176 X_SEGMENT1 IN OUT NOCOPY VARCHAR2,
177 X_SEGMENT2 IN OUT NOCOPY VARCHAR2,
178 X_SEGMENT3 IN OUT NOCOPY VARCHAR2,
179 X_SEGMENT4 IN OUT NOCOPY VARCHAR2,
180 X_SEGMENT5 IN OUT NOCOPY VARCHAR2,
181 X_SEGMENT6 IN OUT NOCOPY VARCHAR2,
182 X_SEGMENT7 IN OUT NOCOPY VARCHAR2,
183 X_SEGMENT8 IN OUT NOCOPY VARCHAR2,
184 X_SEGMENT9 IN OUT NOCOPY VARCHAR2,
185 X_SEGMENT10 IN OUT NOCOPY VARCHAR2,
186 X_SEGMENT11 IN OUT NOCOPY VARCHAR2,
187 X_SEGMENT12 IN OUT NOCOPY VARCHAR2,
188 X_SEGMENT13 IN OUT NOCOPY VARCHAR2,
189 X_SEGMENT14 IN OUT NOCOPY VARCHAR2,
190 X_SEGMENT15 IN OUT NOCOPY VARCHAR2,
191 X_SEGMENT16 IN OUT NOCOPY VARCHAR2,
192 X_SEGMENT17 IN OUT NOCOPY VARCHAR2,
193 X_SEGMENT18 IN OUT NOCOPY VARCHAR2,
194 X_SEGMENT19 IN OUT NOCOPY VARCHAR2,
195 X_SEGMENT20 IN OUT NOCOPY VARCHAR2,
196 X_SEGMENT21 IN OUT NOCOPY VARCHAR2,
197 X_SEGMENT22 IN OUT NOCOPY VARCHAR2,
198 X_SEGMENT23 IN OUT NOCOPY VARCHAR2,
199 X_SEGMENT24 IN OUT NOCOPY VARCHAR2,
200 X_SEGMENT25 IN OUT NOCOPY VARCHAR2,
201 X_SEGMENT26 IN OUT NOCOPY VARCHAR2,
202 X_SEGMENT27 IN OUT NOCOPY VARCHAR2,
203 X_SEGMENT28 IN OUT NOCOPY VARCHAR2,
204 X_SEGMENT29 IN OUT NOCOPY VARCHAR2,
205 X_SEGMENT30 IN OUT NOCOPY VARCHAR2,
206 X_JE_DRCR_SIGN_REFERENCE IN OUT NOCOPY VARCHAR2,
207 X_JE_LINE_DESCRIPTION1 IN OUT NOCOPY VARCHAR2,
208 X_JE_LINE_DESCRIPTION2 IN OUT NOCOPY VARCHAR2,
209 X_JE_LINE_DESCRIPTION3 IN OUT NOCOPY VARCHAR2,
210 X_JE_LINE_DESCRIPTION4 IN OUT NOCOPY VARCHAR2,
211 X_JE_LINE_DESCRIPTION5 IN OUT NOCOPY VARCHAR2,
212 X_JE_LINE_DESCRIPTION6 IN OUT NOCOPY VARCHAR2,
213 X_JE_LINE_DESCRIPTION7 IN OUT NOCOPY VARCHAR2,
214 X_JE_LINE_DESCRIPTION8 IN OUT NOCOPY VARCHAR2,
215 X_JE_LINE_DESCRIPTION9 IN OUT NOCOPY VARCHAR2,
216 X_JE_LINE_DESCRIPTION10 IN OUT NOCOPY VARCHAR2,
217 X_JE_LINE_DESCRIPTION11 IN OUT NOCOPY VARCHAR2,
218 X_JE_LINE_DESCRIPTION12 IN OUT NOCOPY VARCHAR2,
219 X_JE_LINE_DESCRIPTION13 IN OUT NOCOPY VARCHAR2,
220 X_STAT_AMOUNT1 IN OUT NOCOPY NUMBER,
221 X_STAT_AMOUNT2 IN OUT NOCOPY NUMBER,
222 X_STAT_AMOUNT3 IN OUT NOCOPY NUMBER,
223 X_STAT_AMOUNT4 IN OUT NOCOPY NUMBER,
224 X_STAT_AMOUNT5 IN OUT NOCOPY NUMBER,
225 X_STAT_AMOUNT6 IN OUT NOCOPY NUMBER,
226 X_STAT_AMOUNT7 IN OUT NOCOPY NUMBER,
227 X_STAT_AMOUNT8 IN OUT NOCOPY NUMBER,
228 X_STAT_AMOUNT9 IN OUT NOCOPY NUMBER,
229 X_STAT_AMOUNT10 IN OUT NOCOPY NUMBER,
230 X_STAT_AMOUNT11 IN OUT NOCOPY NUMBER,
231 X_STAT_AMOUNT12 IN OUT NOCOPY NUMBER,
232 X_STAT_AMOUNT13 IN OUT NOCOPY NUMBER,
233 X_old_period1_amount IN OUT NOCOPY NUMBER,
234 X_old_period2_amount IN OUT NOCOPY NUMBER,
235 X_old_period3_amount IN OUT NOCOPY NUMBER,
236 X_old_period4_amount IN OUT NOCOPY NUMBER,
237 X_old_period5_amount IN OUT NOCOPY NUMBER,
238 X_old_period6_amount IN OUT NOCOPY NUMBER,
239 X_old_period7_amount IN OUT NOCOPY NUMBER,
240 X_old_period8_amount IN OUT NOCOPY NUMBER,
241 X_old_period9_amount IN OUT NOCOPY NUMBER,
242 X_old_period10_amount IN OUT NOCOPY NUMBER,
243 X_old_period11_amount IN OUT NOCOPY NUMBER,
244 X_old_period12_amount IN OUT NOCOPY NUMBER,
245 X_old_period13_amount IN OUT NOCOPY NUMBER,
246 X_period1_amount IN OUT NOCOPY NUMBER,
247 X_period2_amount IN OUT NOCOPY NUMBER,
248 X_period3_amount IN OUT NOCOPY NUMBER,
249 X_period4_amount IN OUT NOCOPY NUMBER,
250 X_period5_amount IN OUT NOCOPY NUMBER,
251 X_period6_amount IN OUT NOCOPY NUMBER,
252 X_period7_amount IN OUT NOCOPY NUMBER,
253 X_period8_amount IN OUT NOCOPY NUMBER,
254 X_period9_amount IN OUT NOCOPY NUMBER,
255 X_period10_amount IN OUT NOCOPY NUMBER,
256 X_period11_amount IN OUT NOCOPY NUMBER,
257 X_period12_amount IN OUT NOCOPY NUMBER,
258 X_period13_amount IN OUT NOCOPY NUMBER)
259
260 IS
261 X_dr_sign NUMBER;
262 BEGIN
263
264 -- this SELECT is part of the view glvbdrgi.sql which is not being used
265 -- anymore in attempt to improve performance (bug #254358)
266 SELECT
267 BI.rowid,
268 decode(CC.account_type,
269 'A', 'Y',
270 'E', 'Y',
271 'D', 'Y', 'N'),
272 nvl(BI.status_number,0),
273 BI.last_update_date,
274 BI.last_updated_by,
275 BI.last_update_login,
276 BI.creation_date,
277 BI.created_by,
278 CC.account_type,
279 decode(CC.enabled_flag,
280 'N', 'N',
281 decode(sign(trunc(SYSDATE)-
282 trunc(nvl(CC.start_date_active,SYSDATE))),
283 -1, 'N',
284 decode(sign(trunc(nvl(CC.end_date_active,SYSDATE))-
285 trunc(SYSDATE)),
286 -1, 'N','Y'))),
287 CC.detail_budgeting_allowed_flag,
288 nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
289 nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
290 nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
291 nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
292 nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
293 nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
294 nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
295 nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
296 nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
297 nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
298 nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
299 nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
300 nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
301 0,
302 0,
303 0,
304 0,
305 0,
306 0,
307 0,
308 0,
309 0,
310 0,
311 0,
312 0,
313 0,
314 CC.segment1,
315 CC.segment2,
316 CC.segment3,
317 CC.segment4,
318 CC.segment5,
319 CC.segment6,
320 CC.segment7,
321 CC.segment8,
322 CC.segment9,
323 CC.segment10,
324 CC.segment11,
325 CC.segment12,
326 CC.segment13,
327 CC.segment14,
328 CC.segment15,
329 CC.segment16,
330 CC.segment17,
331 CC.segment18,
332 CC.segment19,
333 CC.segment20,
334 CC.segment21,
335 CC.segment22,
336 CC.segment23,
337 CC.segment24,
338 CC.segment25,
339 CC.segment26,
340 CC.segment27,
341 CC.segment28,
342 CC.segment29,
343 CC.segment30,
344 BI.je_drcr_sign_reference,
345 BI.je_line_description1,
346 BI.je_line_description2,
347 BI.je_line_description3,
348 BI.je_line_description4,
349 BI.je_line_description5,
350 BI.je_line_description6,
351 BI.je_line_description7,
352 BI.je_line_description8,
353 BI.je_line_description9,
354 BI.je_line_description10,
355 BI.je_line_description11,
356 BI.je_line_description12,
357 BI.je_line_description13,
358 BI.stat_amount1,
359 BI.stat_amount2,
360 BI.stat_amount3,
361 BI.stat_amount4,
362 BI.stat_amount5,
363 BI.stat_amount6,
364 BI.stat_amount7,
365 BI.stat_amount8,
366 BI.stat_amount9,
367 BI.stat_amount10,
368 BI.stat_amount11,
369 BI.stat_amount12,
370 BI.stat_amount13
371 INTO
372 X_row_id,
373 X_DR_FLAG,
374 X_STATUS_NUMBER,
375 X_LAST_UPDATE_DATE,
376 X_LAST_UPDATED_BY,
377 X_LAST_UPDATE_LOGIN,
378 X_CREATION_DATE,
379 X_CREATED_BY,
380 X_ACCOUNT_TYPE,
381 X_CC_ACTIVE_FLAG,
382 X_CC_BUDGETING_ALLOWED_FLAG,
383 X_PERIOD1_AMOUNT_QRY,
384 X_PERIOD2_AMOUNT_QRY,
385 X_PERIOD3_AMOUNT_QRY,
386 X_PERIOD4_AMOUNT_QRY,
387 X_PERIOD5_AMOUNT_QRY,
388 X_PERIOD6_AMOUNT_QRY,
389 X_PERIOD7_AMOUNT_QRY,
390 X_PERIOD8_AMOUNT_QRY,
391 X_PERIOD9_AMOUNT_QRY,
392 X_PERIOD10_AMOUNT_QRY,
393 X_PERIOD11_AMOUNT_QRY,
394 X_PERIOD12_AMOUNT_QRY,
395 X_PERIOD13_AMOUNT_QRY,
396 X_OLD_PERIOD1_AMOUNT_QRY,
397 X_OLD_PERIOD2_AMOUNT_QRY,
398 X_OLD_PERIOD3_AMOUNT_QRY,
399 X_OLD_PERIOD4_AMOUNT_QRY,
400 X_OLD_PERIOD5_AMOUNT_QRY,
401 X_OLD_PERIOD6_AMOUNT_QRY,
402 X_OLD_PERIOD7_AMOUNT_QRY,
403 X_OLD_PERIOD8_AMOUNT_QRY,
404 X_OLD_PERIOD9_AMOUNT_QRY,
405 X_OLD_PERIOD10_AMOUNT_QRY,
406 X_OLD_PERIOD11_AMOUNT_QRY,
407 X_OLD_PERIOD12_AMOUNT_QRY,
408 X_OLD_PERIOD13_AMOUNT_QRY,
409 X_SEGMENT1,
410 X_SEGMENT2,
411 X_SEGMENT3,
412 X_SEGMENT4,
413 X_SEGMENT5,
414 X_SEGMENT6,
415 X_SEGMENT7,
416 X_SEGMENT8,
417 X_SEGMENT9,
418 X_SEGMENT10,
419 X_SEGMENT11,
420 X_SEGMENT12,
421 X_SEGMENT13,
422 X_SEGMENT14,
423 X_SEGMENT15,
424 X_SEGMENT16,
425 X_SEGMENT17,
426 X_SEGMENT18,
427 X_SEGMENT19,
428 X_SEGMENT20,
429 X_SEGMENT21,
430 X_SEGMENT22,
431 X_SEGMENT23,
432 X_SEGMENT24,
433 X_SEGMENT25,
434 X_SEGMENT26,
435 X_SEGMENT27,
436 X_SEGMENT28,
437 X_SEGMENT29,
438 X_SEGMENT30,
439 X_JE_DRCR_SIGN_REFERENCE,
440 X_JE_LINE_DESCRIPTION1,
441 X_JE_LINE_DESCRIPTION2,
442 X_JE_LINE_DESCRIPTION3,
443 X_JE_LINE_DESCRIPTION4,
444 X_JE_LINE_DESCRIPTION5,
445 X_JE_LINE_DESCRIPTION6,
446 X_JE_LINE_DESCRIPTION7,
447 X_JE_LINE_DESCRIPTION8,
448 X_JE_LINE_DESCRIPTION9,
449 X_JE_LINE_DESCRIPTION10,
450 X_JE_LINE_DESCRIPTION11,
451 X_JE_LINE_DESCRIPTION12,
452 X_JE_LINE_DESCRIPTION13,
453 X_STAT_AMOUNT1,
454 X_STAT_AMOUNT2,
455 X_STAT_AMOUNT3,
456 X_STAT_AMOUNT4,
457 X_STAT_AMOUNT5,
458 X_STAT_AMOUNT6,
459 X_STAT_AMOUNT7,
460 X_STAT_AMOUNT8,
461 X_STAT_AMOUNT9,
462 X_STAT_AMOUNT10,
463 X_STAT_AMOUNT11,
464 X_STAT_AMOUNT12,
465 X_STAT_AMOUNT13
466 FROM
467 GL_BUDGET_RANGE_INTERIM BI,
468 GL_CODE_COMBINATIONS CC
469 WHERE
470 BI.ledger_id (+) = X_ledger_id
471 AND BI.code_combination_id (+) = CC.code_combination_id
472 AND BI.currency_code (+) = X_currency_code
473 AND BI.budget_version_id (+) = X_budget_version_id
474 AND BI.period_year (+) = X_period_year
475 AND BI.start_period_num (+) = X_start_period_num
476 AND BI.status_number(+) = X_status_num
477 AND CC.code_combination_id = X_code_combination_id;
478
479
480
481 -- Get the account type of the flexfield we are analyzing
482 IF ( X_dr_flag = 'N' ) THEN
483 X_dr_sign := -1;
484 ELSE
485 X_dr_sign := 1;
486 END IF;
487
488 -- get period balances for each period. This SELECT was moved here from view to optimize
489 -- performance
490 SELECT
491 nvl(sum(decode(GB.period_num,
492 floor((X_start_period_num-1)/13) * 13 + 1,
493 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
494 nvl(sum(decode(GB.period_num,
495 floor((X_start_period_num-1)/13) * 13 + 2,
496 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
497 nvl(sum(decode(GB.period_num,
498 floor((X_start_period_num-1)/13) * 13 + 3,
499 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
500 nvl(sum(decode(GB.period_num,
501 floor((X_start_period_num-1)/13) * 13 + 4,
502 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
503 nvl(sum(decode(GB.period_num,
504 floor((X_start_period_num-1)/13) * 13 + 5,
505 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
506 nvl(sum(decode(GB.period_num,
507 floor((X_start_period_num-1)/13) * 13 + 6,
508 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
509 nvl(sum(decode(GB.period_num,
510 floor((X_start_period_num-1)/13) * 13 + 7,
511 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
512 nvl(sum(decode(GB.period_num,
513 floor((X_start_period_num-1)/13) * 13 + 8,
514 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
515 nvl(sum(decode(GB.period_num,
516 floor((X_start_period_num-1)/13) * 13 + 9,
517 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
518 nvl(sum(decode(GB.period_num,
519 floor((X_start_period_num-1)/13) * 13 + 10,
520 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
521 nvl(sum(decode(GB.period_num,
522 floor((X_start_period_num-1)/13) * 13 + 11,
523 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
524 nvl(sum(decode(GB.period_num,
525 floor((X_start_period_num-1)/13) * 13 + 12,
526 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
527 nvl(sum(decode(GB.period_num,
528 floor((X_start_period_num-1)/13) * 13 + 13,
529 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0)
530 INTO X_old_period1_amount, X_old_period2_amount, X_old_period3_amount,
531 X_old_period4_amount, X_old_period5_amount, X_old_period6_amount,
532 X_old_period7_amount, X_old_period8_amount, X_old_period9_amount,
533 X_old_period10_amount, X_old_period11_amount, X_old_period12_amount,
534 X_old_period13_amount
535 FROM GL_BALANCES GB
536 WHERE GB.ledger_id (+) = X_ledger_id
537 AND GB.code_combination_id (+) = X_code_combination_id
538 AND GB.currency_code (+) = X_currency_code
539 AND GB.actual_flag (+) = 'B'
540 AND GB.budget_version_id (+) = X_budget_version_id
541 AND GB.period_year (+) = X_period_year
542 AND GB.period_num (+) BETWEEN X_start_period_num
543 AND X_end_period_num;
544
545 X_period1_amount := X_period1_amount_qry + X_old_period1_amount;
546 X_period2_amount := X_period2_amount_qry + X_old_period2_amount;
547 X_period3_amount := X_period3_amount_qry + X_old_period3_amount;
548 X_period4_amount := X_period4_amount_qry + X_old_period4_amount;
549 X_period5_amount := X_period5_amount_qry + X_old_period5_amount;
550 X_period6_amount := X_period6_amount_qry + X_old_period6_amount;
551 X_period7_amount := X_period7_amount_qry + X_old_period7_amount;
552 X_period8_amount := X_period8_amount_qry + X_old_period8_amount;
553 X_period9_amount := X_period9_amount_qry + X_old_period9_amount;
554 X_period10_amount := X_period10_amount_qry + X_old_period10_amount;
555 X_period11_amount := X_period11_amount_qry + X_old_period11_amount;
556 X_period12_amount := X_period12_amount_qry + X_old_period12_amount;
557 X_period13_amount := X_period13_amount_qry + X_old_period13_amount;
558
559 -- here we are calling add_approved_budget_amounts depending on
560 -- whether BudgetControl is On or Off
561 IF (X_bc_on = 'Y') THEN
562 glf02220_pkg.add_approved_budget_amounts( X_ledger_id,
563 X_code_combination_id,
564 X_currency_code,
565 X_budget_version_id,
566 X_period_year,
567 X_start_period_num,
568 X_end_period_num,
569 X_dr_sign,
570 X_period1_amount,
571 X_period2_amount,
572 X_period3_amount,
573 X_period4_amount,
574 X_period5_amount,
575 X_period6_amount,
576 X_period7_amount,
577 X_period8_amount,
578 X_period9_amount,
579 X_period10_amount,
580 X_period11_amount,
581 X_period12_amount,
582 X_period13_amount,
583 X_old_period1_amount,
584 X_old_period2_amount,
585 X_old_period3_amount,
586 X_old_period4_amount,
587 X_old_period5_amount,
588 X_old_period6_amount,
589 X_old_period7_amount,
590 X_old_period8_amount,
591 X_old_period9_amount,
592 X_old_period10_amount,
593 X_old_period11_amount,
594 X_old_period12_amount,
595 X_old_period13_amount );
596
597 END IF;
598
599 EXCEPTION
600 WHEN app_exceptions.application_exception THEN
601 RAISE;
602 WHEN OTHERS THEN
603 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
604 fnd_message.set_token('PROCEDURE', 'glf02220_pkg.get_balances_info');
605 RAISE;
606 END get_balances_info;
607
608 --
609 -- PUBLIC PROCEDURES
610 --
611
612 PROCEDURE get_period_prompts( X_ledger_id IN NUMBER,
613 X_period_year IN NUMBER,
614 X_start_period_num IN NUMBER,
615 X_end_period_num IN NUMBER,
616 X_period1_name IN OUT NOCOPY VARCHAR2,
617 X_period2_name IN OUT NOCOPY VARCHAR2,
618 X_period3_name IN OUT NOCOPY VARCHAR2,
619 X_period4_name IN OUT NOCOPY VARCHAR2,
620 X_period5_name IN OUT NOCOPY VARCHAR2,
621 X_period6_name IN OUT NOCOPY VARCHAR2,
622 X_period7_name IN OUT NOCOPY VARCHAR2,
623 X_period8_name IN OUT NOCOPY VARCHAR2,
624 X_period9_name IN OUT NOCOPY VARCHAR2,
625 X_period10_name IN OUT NOCOPY VARCHAR2,
626 X_period11_name IN OUT NOCOPY VARCHAR2,
627 X_period12_name IN OUT NOCOPY VARCHAR2,
628 X_period13_name IN OUT NOCOPY VARCHAR2,
629 X_adj_period1_flag IN OUT NOCOPY VARCHAR2,
630 X_adj_period2_flag IN OUT NOCOPY VARCHAR2,
631 X_adj_period3_flag IN OUT NOCOPY VARCHAR2,
632 X_adj_period4_flag IN OUT NOCOPY VARCHAR2,
633 X_adj_period5_flag IN OUT NOCOPY VARCHAR2,
634 X_adj_period6_flag IN OUT NOCOPY VARCHAR2,
635 X_adj_period7_flag IN OUT NOCOPY VARCHAR2,
636 X_adj_period8_flag IN OUT NOCOPY VARCHAR2,
637 X_adj_period9_flag IN OUT NOCOPY VARCHAR2,
638 X_adj_period10_flag IN OUT NOCOPY VARCHAR2,
639 X_adj_period11_flag IN OUT NOCOPY VARCHAR2,
640 X_adj_period12_flag IN OUT NOCOPY VARCHAR2,
641 X_adj_period13_flag IN OUT NOCOPY VARCHAR2,
642 X_num_adj_per_in_range IN OUT NOCOPY NUMBER,
643 X_num_adj_per_before IN OUT NOCOPY NUMBER,
644 X_num_adj_per_total IN OUT NOCOPY NUMBER ) IS
645
646 CURSOR gpp IS
647 SELECT
648 max(decode(PS.period_num,
649 floor((X_start_period_num-1)/13) * 13 + 1, PS.period_name,
650 NULL)),
651 max(decode(PS.period_num,
652 floor((X_start_period_num-1)/13) * 13 + 2, PS.period_name,
653 NULL)),
654 max(decode(PS.period_num,
655 floor((X_start_period_num-1)/13) * 13 + 3, PS.period_name,
656 NULL)),
657 max(decode(PS.period_num,
658 floor((X_start_period_num-1)/13) * 13 + 4, PS.period_name,
659 NULL)),
660 max(decode(PS.period_num,
661 floor((X_start_period_num-1)/13) * 13 + 5, PS.period_name,
662 NULL)),
663 max(decode(PS.period_num,
664 floor((X_start_period_num-1)/13) * 13 + 6, PS.period_name,
665 NULL)),
666 max(decode(PS.period_num,
667 floor((X_start_period_num-1)/13) * 13 + 7, PS.period_name,
668 NULL)),
669 max(decode(PS.period_num,
670 floor((X_start_period_num-1)/13) * 13 + 8, PS.period_name,
671 NULL)),
672 max(decode(PS.period_num,
673 floor((X_start_period_num-1)/13) * 13 + 9, PS.period_name,
674 NULL)),
675 max(decode(PS.period_num,
676 floor((X_start_period_num-1)/13) * 13 + 10, PS.period_name,
677 NULL)),
678 max(decode(PS.period_num,
679 floor((X_start_period_num-1)/13) * 13 + 11, PS.period_name,
680 NULL)),
681 max(decode(PS.period_num,
682 floor((X_start_period_num-1)/13) * 13 + 12, PS.period_name,
683 NULL)),
684 max(decode(PS.period_num,
685 floor((X_start_period_num-1)/13) * 13 + 13, PS.period_name,
686 NULL)),
687 max(decode(PS.period_num,
688 floor((X_start_period_num-1)/13) * 13 + 1, PS.adjustment_period_flag,
689 NULL)),
690 max(decode(PS.period_num,
691 floor((X_start_period_num-1)/13) * 13 + 2, PS.adjustment_period_flag,
692 NULL)),
693 max(decode(PS.period_num,
694 floor((X_start_period_num-1)/13) * 13 + 3, PS.adjustment_period_flag,
695 NULL)),
696 max(decode(PS.period_num,
697 floor((X_start_period_num-1)/13) * 13 + 4, PS.adjustment_period_flag,
698 NULL)),
699 max(decode(PS.period_num,
700 floor((X_start_period_num-1)/13) * 13 + 5, PS.adjustment_period_flag,
701 NULL)),
702 max(decode(PS.period_num,
703 floor((X_start_period_num-1)/13) * 13 + 6, PS.adjustment_period_flag,
704 NULL)),
705 max(decode(PS.period_num,
706 floor((X_start_period_num-1)/13) * 13 + 7, PS.adjustment_period_flag,
707 NULL)),
708 max(decode(PS.period_num,
709 floor((X_start_period_num-1)/13) * 13 + 8, PS.adjustment_period_flag,
710 NULL)),
711 max(decode(PS.period_num,
712 floor((X_start_period_num-1)/13) * 13 + 9, PS.adjustment_period_flag,
713 NULL)),
714 max(decode(PS.period_num,
715 floor((X_start_period_num-1)/13) * 13 + 10, PS.adjustment_period_flag,
716 NULL)),
717 max(decode(PS.period_num,
718 floor((X_start_period_num-1)/13) * 13 + 11, PS.adjustment_period_flag,
719 NULL)),
720 max(decode(PS.period_num,
721 floor((X_start_period_num-1)/13) * 13 + 12, PS.adjustment_period_flag,
722 NULL)),
723 max(decode(PS.period_num,
724 floor((X_start_period_num-1)/13) * 13 + 13, PS.adjustment_period_flag,
725 NULL)),
726 sum(decode(PS.adjustment_period_flag,'Y',1,0)),
727 max(PS.application_id)
728 FROM
729 GL_PERIOD_STATUSES PS
730 WHERE
731 PS.application_id = 101
732 AND PS.ledger_id = X_ledger_id
733 AND PS.period_year = X_period_year
734 AND PS.period_num BETWEEN X_start_period_num and X_end_period_num;
735
736 dummy NUMBER := NULL; -- for testing NO_DATA_FOUND only
737
738 BEGIN
739 OPEN gpp;
740 FETCH gpp INTO X_period1_name,
741 X_period2_name,
742 X_period3_name,
743 X_period4_name,
744 X_period5_name,
745 X_period6_name,
746 X_period7_name,
747 X_period8_name,
748 X_period9_name,
749 X_period10_name,
750 X_period11_name,
751 X_period12_name,
752 X_period13_name,
753 X_adj_period1_flag,
754 X_adj_period2_flag,
755 X_adj_period3_flag,
756 X_adj_period4_flag,
757 X_adj_period5_flag,
758 X_adj_period6_flag,
759 X_adj_period7_flag,
760 X_adj_period8_flag,
761 X_adj_period9_flag,
762 X_adj_period10_flag,
763 X_adj_period11_flag,
764 X_adj_period12_flag,
765 X_adj_period13_flag,
766 X_num_adj_per_in_range,
767 dummy;
768 CLOSE gpp;
769
770 -- Check explicitly here for null row returned, i.e. NO_DATA_FOUND,
771 -- since the group function always returns at least one row
772 IF (dummy IS NULL) THEN
773 RAISE NO_DATA_FOUND;
774 END IF;
775
776 SELECT
777 count(*),
778 nvl(sum(decode(sign(X_start_period_num-period_num),1,1,0)),0)
779 INTO X_num_adj_per_total,X_num_adj_per_before
780 FROM
781 GL_PERIOD_STATUSES PS
782 WHERE
783 PS.application_id = 101
784 AND PS.ledger_id = X_ledger_id
785 AND PS.period_year = X_period_year
786 AND PS.adjustment_period_flag = 'Y';
787
788
789 EXCEPTION
790 WHEN NO_DATA_FOUND THEN
791 fnd_message.set_name('SQLGL', 'GL_BUD_PERIODS_NOTFOUND');
792 fnd_message.set_token('FUNCTION', 'glf02220_pkg.get_period_prompts',
793 FALSE);
794 app_exception.raise_exception;
795 WHEN OTHERS THEN
796 app_exception.raise_exception;
797
798 END get_period_prompts;
799
800
801 PROCEDURE add_approved_budget_amounts(
802 X_ledger_id IN NUMBER,
803 X_code_combination_id IN NUMBER,
804 X_currency_code IN VARCHAR2,
805 X_budget_version_id IN NUMBER,
806 X_period_year IN NUMBER,
807 X_start_period_num IN NUMBER,
808 X_end_period_num IN NUMBER,
809 X_dr_sign IN NUMBER,
810 X_period1_amount IN OUT NOCOPY NUMBER,
811 X_period2_amount IN OUT NOCOPY NUMBER,
812 X_period3_amount IN OUT NOCOPY NUMBER,
813 X_period4_amount IN OUT NOCOPY NUMBER,
814 X_period5_amount IN OUT NOCOPY NUMBER,
815 X_period6_amount IN OUT NOCOPY NUMBER,
816 X_period7_amount IN OUT NOCOPY NUMBER,
817 X_period8_amount IN OUT NOCOPY NUMBER,
818 X_period9_amount IN OUT NOCOPY NUMBER,
819 X_period10_amount IN OUT NOCOPY NUMBER,
820 X_period11_amount IN OUT NOCOPY NUMBER,
821 X_period12_amount IN OUT NOCOPY NUMBER,
822 X_period13_amount IN OUT NOCOPY NUMBER,
823 X_old_period1_amount IN OUT NOCOPY NUMBER,
824 X_old_period2_amount IN OUT NOCOPY NUMBER,
825 X_old_period3_amount IN OUT NOCOPY NUMBER,
826 X_old_period4_amount IN OUT NOCOPY NUMBER,
827 X_old_period5_amount IN OUT NOCOPY NUMBER,
828 X_old_period6_amount IN OUT NOCOPY NUMBER,
829 X_old_period7_amount IN OUT NOCOPY NUMBER,
830 X_old_period8_amount IN OUT NOCOPY NUMBER,
831 X_old_period9_amount IN OUT NOCOPY NUMBER,
832 X_old_period10_amount IN OUT NOCOPY NUMBER,
833 X_old_period11_amount IN OUT NOCOPY NUMBER,
834 X_old_period12_amount IN OUT NOCOPY NUMBER,
835 X_old_period13_amount IN OUT NOCOPY NUMBER ) IS
836 amt1 NUMBER;
837 amt2 NUMBER;
838 amt3 NUMBER;
839 amt4 NUMBER;
840 amt5 NUMBER;
841 amt6 NUMBER;
842 amt7 NUMBER;
843 amt8 NUMBER;
844 amt9 NUMBER;
845 amt10 NUMBER;
846 amt11 NUMBER;
847 amt12 NUMBER;
848 amt13 NUMBER;
849
850 X_found_amounts NUMBER;
851 BEGIN
852 get_approved_budget_amounts( X_ledger_id,
853 X_code_combination_id,
854 X_currency_code,
855 'B',
856 X_budget_version_id,
857 X_period_year,
858 X_start_period_num,
859 X_end_period_num,
860 X_dr_sign,
861 X_found_amounts,
862 amt1,
863 amt2,
864 amt3,
865 amt4,
866 amt5,
867 amt6,
868 amt7,
869 amt8,
870 amt9,
871 amt10,
872 amt11,
873 amt12,
874 amt13);
875
876 X_period1_amount := X_period1_amount + amt1;
877 X_period2_amount := X_period2_amount + amt2;
878 X_period3_amount := X_period3_amount + amt3;
879 X_period4_amount := X_period4_amount + amt4;
880 X_period5_amount := X_period5_amount + amt5;
881 X_period6_amount := X_period6_amount + amt6;
882 X_period7_amount := X_period7_amount + amt7;
883 X_period8_amount := X_period8_amount + amt8;
884 X_period9_amount := X_period9_amount + amt9;
885 X_period10_amount := X_period10_amount + amt10;
886 X_period11_amount := X_period11_amount + amt11;
887 X_period12_amount := X_period12_amount + amt12;
888 X_period13_amount := X_period13_amount + amt13;
889 X_old_period1_amount := X_old_period1_amount + amt1;
890 X_old_period2_amount := X_old_period2_amount + amt2;
891 X_old_period3_amount := X_old_period3_amount + amt3;
892 X_old_period4_amount := X_old_period4_amount + amt4;
893 X_old_period5_amount := X_old_period5_amount + amt5;
894 X_old_period6_amount := X_old_period6_amount + amt6;
895 X_old_period7_amount := X_old_period7_amount + amt7;
896 X_old_period8_amount := X_old_period8_amount + amt8;
897 X_old_period9_amount := X_old_period9_amount + amt9;
898 X_old_period10_amount := X_old_period10_amount + amt10;
899 X_old_period11_amount := X_old_period11_amount + amt11;
900 X_old_period12_amount := X_old_period12_amount + amt12;
901 X_old_period13_amount := X_old_period13_amount + amt13;
902
903 EXCEPTION
904 WHEN NO_DATA_FOUND THEN
905 NULL;
906 WHEN OTHERS THEN
907 app_exception.raise_exception;
908
909 END add_approved_budget_amounts;
910
911
912 PROCEDURE get_brule_budget_amounts(
913 X_budget_rule IN VARCHAR2,
914 X_rule_amount IN NUMBER,
915 X_status_number IN NUMBER,
916 X_ledger_id IN NUMBER,
917 X_bc_on IN VARCHAR2,
918 X_code_combination_id IN NUMBER,
919 X_currency_code IN VARCHAR2,
920 X_budget_version_id IN NUMBER,
921 X_period_year IN NUMBER,
922 X_start_period_num IN NUMBER,
923 X_end_period_num IN NUMBER,
924 X_dr_sign IN NUMBER,
925 X_period1_amount IN OUT NOCOPY NUMBER,
926 X_period2_amount IN OUT NOCOPY NUMBER,
927 X_period3_amount IN OUT NOCOPY NUMBER,
928 X_period4_amount IN OUT NOCOPY NUMBER,
929 X_period5_amount IN OUT NOCOPY NUMBER,
930 X_period6_amount IN OUT NOCOPY NUMBER,
931 X_period7_amount IN OUT NOCOPY NUMBER,
932 X_period8_amount IN OUT NOCOPY NUMBER,
933 X_period9_amount IN OUT NOCOPY NUMBER,
934 X_period10_amount IN OUT NOCOPY NUMBER,
935 X_period11_amount IN OUT NOCOPY NUMBER,
936 X_period12_amount IN OUT NOCOPY NUMBER,
937 X_period13_amount IN OUT NOCOPY NUMBER ) IS
938
939 -- hold defined currency
940 defined_currency GL_BALANCES.currency_code%TYPE;
941
942 CURSOR gbi IS
943 SELECT
944 sum(decode(GB.period_num,
945 floor((X_start_period_num-1)/13) * 13 + 1,
946 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
947 max(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
948 sum(decode(GB.period_num,
949 floor((X_start_period_num-1)/13) * 13 + 2,
950 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
951 max(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
952 sum(decode(GB.period_num,
953 floor((X_start_period_num-1)/13) * 13 + 3,
954 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
955 max(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
956 sum(decode(GB.period_num,
957 floor((X_start_period_num-1)/13) * 13 + 4,
958 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
959 max(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
960 sum(decode(GB.period_num,
961 floor((X_start_period_num-1)/13) * 13 + 5,
962 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
963 max(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
964 sum(decode(GB.period_num,
965 floor((X_start_period_num-1)/13) * 13 + 6,
966 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
967 max(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
968 sum(decode(GB.period_num,
969 floor((X_start_period_num-1)/13) * 13 + 7,
970 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
971 max(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
972 sum(decode(GB.period_num,
973 floor((X_start_period_num-1)/13) * 13 + 8,
974 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
975 max(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
976 sum(decode(GB.period_num,
977 floor((X_start_period_num-1)/13) * 13 + 9,
978 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
979 max(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
980 sum(decode(GB.period_num,
981 floor((X_start_period_num-1)/13) * 13 + 10,
982 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
983 max(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
984 sum(decode(GB.period_num,
985 floor((X_start_period_num-1)/13) * 13 + 11,
986 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
987 max(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
988 sum(decode(GB.period_num,
989 floor((X_start_period_num-1)/13) * 13 + 12,
990 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
991 max(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
992 sum(decode(GB.period_num,
993 floor((X_start_period_num-1)/13) * 13 + 13,
994 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
995 max(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
996 decode(max(nvl(GB.rowid, BI.rowid)), null, 0, 1)
997 FROM
998 GL_CODE_COMBINATIONS CC,
999 GL_BALANCES GB,
1000 GL_BUDGET_RANGE_INTERIM BI
1001 WHERE CC.code_combination_id = X_code_combination_id
1002 AND GB.ledger_id (+) = X_ledger_id
1003 AND GB.code_combination_id (+) = CC.code_combination_id
1004 AND GB.currency_code (+) = defined_currency
1005 AND GB.actual_flag (+) = 'B'
1006 AND GB.budget_version_id (+) = X_budget_version_id
1007 AND GB.period_year (+) = X_period_year -
1008 decode(X_budget_rule,
1009 'PYBS', 1,
1010 'PYBM', 1,
1011 0)
1012 AND GB.period_num (+) BETWEEN X_start_period_num
1013 AND X_end_period_num
1014 /* */
1015 AND BI.ledger_id (+) = X_ledger_id
1016 AND BI.code_combination_id (+) = CC.code_combination_id
1017 AND BI.currency_code (+) = defined_currency
1018 AND BI.budget_version_id (+) = X_budget_version_id
1019 AND BI.period_year (+) = X_period_year -
1020 decode(X_budget_rule,
1021 'PYBS', 1,
1022 'PYBM', 1,
1023 0)
1024 AND BI.start_period_num (+) = X_start_period_num
1025 AND BI.status_number(+) = X_status_number;
1026
1027 p1_amount NUMBER := 0;
1028 p2_amount NUMBER := 0;
1029 p3_amount NUMBER := 0;
1030 p4_amount NUMBER := 0;
1031 p5_amount NUMBER := 0;
1032 p6_amount NUMBER := 0;
1033 p7_amount NUMBER := 0;
1034 p8_amount NUMBER := 0;
1035 p9_amount NUMBER := 0;
1036 p10_amount NUMBER := 0;
1037 p11_amount NUMBER := 0;
1038 p12_amount NUMBER := 0;
1039 p13_amount NUMBER := 0;
1040
1041 /* Keep track of whether or not balance and
1042 funds reservation data was found */
1043 bal_data_found NUMBER := 0;
1044 res_data_found NUMBER := 0;
1045
1046 -- variables for keeping temporary values of X_periodi_amount
1047 p_period1_amount NUMBER;
1048 p_period2_amount NUMBER;
1049 p_period3_amount NUMBER;
1050 p_period4_amount NUMBER;
1051 p_period5_amount NUMBER;
1052 p_period6_amount NUMBER;
1053 p_period7_amount NUMBER;
1054 p_period8_amount NUMBER;
1055 p_period9_amount NUMBER;
1056 p_period10_amount NUMBER;
1057 p_period11_amount NUMBER;
1058 p_period12_amount NUMBER;
1059 p_period13_amount NUMBER;
1060
1061 eff_period_year NUMBER := X_period_year;
1062
1063 BEGIN
1064
1065 -- define currency code to pass to get_approved_budget_amounts
1066 IF (X_budget_rule = 'PYBS' OR X_budget_rule = 'CYBS') THEN
1067 defined_currency := 'STAT';
1068 ELSE
1069 defined_currency := X_currency_code;
1070 END IF;
1071
1072 OPEN gbi;
1073 FETCH gbi INTO p_period1_amount,
1074 p_period2_amount,
1075 p_period3_amount,
1076 p_period4_amount,
1077 p_period5_amount,
1078 p_period6_amount,
1079 p_period7_amount,
1080 p_period8_amount,
1081 p_period9_amount,
1082 p_period10_amount,
1083 p_period11_amount,
1084 p_period12_amount,
1085 p_period13_amount,
1086 bal_data_found;
1087 CLOSE gbi;
1088
1089 -- Add the reserved amounts, if necessary.
1090 IF (X_bc_on = 'Y') THEN
1091 IF (X_budget_rule IN ('PYBS', 'PYBM')) THEN
1092 eff_period_year := X_period_year - 1;
1093 END IF;
1094 get_approved_budget_amounts( X_ledger_id,
1095 X_code_combination_id,
1096 defined_currency,
1097 'B',
1098 X_budget_version_id,
1099 eff_period_year,
1100 X_start_period_num,
1101 X_end_period_num,
1102 X_dr_sign,
1103 res_data_found,
1104 p1_amount,
1105 p2_amount,
1106 p3_amount,
1107 p4_amount,
1108 p5_amount,
1109 p6_amount,
1110 p7_amount,
1111 p8_amount,
1112 p9_amount,
1113 p10_amount,
1114 p11_amount,
1115 p12_amount,
1116 p13_amount);
1117
1118 END IF;
1119
1120 -- If no data has been found, then error out.
1121 IF ( (bal_data_found = 0)
1122 AND (res_data_found = 0)
1123 ) THEN
1124 RAISE No_Data_Found;
1125 END IF;
1126
1127 -- Calculate the new amounts and put the resilts into the temporary variables
1128 p_period1_amount := round( ( nvl(p_period1_amount, 0)
1129 + nvl(p1_amount, 0))
1130 * X_rule_amount, 2);
1131 p_period2_amount := round( ( nvl(p_period2_amount, 0)
1132 + nvl(p2_amount, 0))
1133 * X_rule_amount, 2);
1134 p_period3_amount := round( ( nvl(p_period3_amount, 0)
1135 + nvl(p3_amount, 0))
1136 * X_rule_amount, 2);
1137 p_period4_amount := round( ( nvl(p_period4_amount, 0)
1138 + nvl(p4_amount, 0))
1139 * X_rule_amount, 2);
1140 p_period5_amount := round( ( nvl(p_period5_amount, 0)
1141 + nvl(p5_amount, 0))
1142 * X_rule_amount, 2);
1143 p_period6_amount := round( ( nvl(p_period6_amount, 0)
1144 + nvl(p6_amount, 0))
1145 * X_rule_amount, 2);
1146 p_period7_amount := round( ( nvl(p_period7_amount, 0)
1147 + nvl(p7_amount, 0))
1148 * X_rule_amount, 2);
1149 p_period8_amount := round( ( nvl(p_period8_amount, 0)
1150 + nvl(p8_amount, 0))
1151 * X_rule_amount, 2);
1152 p_period9_amount := round( ( nvl(p_period9_amount, 0)
1153 + nvl(p9_amount, 0))
1154 * X_rule_amount, 2);
1155 p_period10_amount := round( ( nvl(p_period10_amount, 0)
1156 + nvl(p10_amount, 0))
1157 * X_rule_amount, 2);
1158 p_period11_amount := round( ( nvl(p_period11_amount, 0)
1159 + nvl(p11_amount, 0))
1160 * X_rule_amount, 2);
1161 p_period12_amount := round( ( nvl(p_period12_amount, 0)
1162 + nvl(p12_amount, 0))
1163 * X_rule_amount, 2);
1164 p_period13_amount := round( ( nvl(p_period13_amount, 0)
1165 + nvl(p13_amount, 0))
1166 * X_rule_amount, 2);
1167
1168 -- if at least one the temporary variables exceeds the maximum allowed
1169 -- (by FORMS implemetation) value, give a message and leave old values
1170 IF( p_period1_amount > maximum_allowed_amount_value OR
1171 p_period2_amount > maximum_allowed_amount_value OR
1172 p_period3_amount > maximum_allowed_amount_value OR
1173 p_period4_amount > maximum_allowed_amount_value OR
1174 p_period5_amount > maximum_allowed_amount_value OR
1175 p_period6_amount > maximum_allowed_amount_value OR
1176 p_period7_amount > maximum_allowed_amount_value OR
1177 p_period8_amount > maximum_allowed_amount_value OR
1178 p_period9_amount > maximum_allowed_amount_value OR
1179 p_period10_amount > maximum_allowed_amount_value OR
1180 p_period11_amount > maximum_allowed_amount_value OR
1181 p_period12_amount > maximum_allowed_amount_value OR
1182 p_period13_amount > maximum_allowed_amount_value
1183 ) THEN
1184 fnd_message.set_name('SQLGL', 'GL_BUD_RULE_TOO_BIG_AMOUNT');
1185 app_exception.raise_exception;
1186 ELSE
1187 X_period1_amount := p_period1_amount;
1188 X_period2_amount := p_period2_amount;
1189 X_period3_amount := p_period3_amount;
1190 X_period4_amount := p_period4_amount;
1191 X_period5_amount := p_period5_amount;
1192 X_period6_amount := p_period6_amount;
1193 X_period7_amount := p_period7_amount;
1194 X_period8_amount := p_period8_amount;
1195 X_period9_amount := p_period9_amount;
1196 X_period10_amount := p_period10_amount;
1197 X_period11_amount := p_period11_amount;
1198 X_period12_amount := p_period12_amount;
1199 X_period13_amount := p_period13_amount;
1200 END IF;
1201 EXCEPTION
1202 WHEN NO_DATA_FOUND THEN
1203 fnd_message.set_name('SQLGL', 'GL_BUD_RULE_NO_DATA_FOUND');
1204 app_exception.raise_exception;
1205 WHEN OTHERS THEN
1206 app_exception.raise_exception;
1207
1208 END get_brule_budget_amounts;
1209
1210
1211 PROCEDURE get_brule_actual_amounts(
1212 X_budget_rule IN VARCHAR2,
1213 X_rule_amount IN NUMBER,
1214 X_ledger_id IN NUMBER,
1215 X_bc_on IN VARCHAR2,
1216 X_code_combination_id IN NUMBER,
1217 X_currency_code IN VARCHAR2,
1218 X_budget_version_id IN NUMBER,
1219 X_period_year IN NUMBER,
1220 X_start_period_num IN NUMBER,
1221 X_end_period_num IN NUMBER,
1222 X_dr_sign IN NUMBER,
1223 X_period1_amount IN OUT NOCOPY NUMBER,
1224 X_period2_amount IN OUT NOCOPY NUMBER,
1225 X_period3_amount IN OUT NOCOPY NUMBER,
1226 X_period4_amount IN OUT NOCOPY NUMBER,
1227 X_period5_amount IN OUT NOCOPY NUMBER,
1228 X_period6_amount IN OUT NOCOPY NUMBER,
1229 X_period7_amount IN OUT NOCOPY NUMBER,
1230 X_period8_amount IN OUT NOCOPY NUMBER,
1231 X_period9_amount IN OUT NOCOPY NUMBER,
1232 X_period10_amount IN OUT NOCOPY NUMBER,
1233 X_period11_amount IN OUT NOCOPY NUMBER,
1234 X_period12_amount IN OUT NOCOPY NUMBER,
1235 X_period13_amount IN OUT NOCOPY NUMBER ) IS
1236
1237 CURSOR gbl IS
1238 SELECT
1239 sum(decode(GB.period_num,
1240 floor((X_start_period_num-1)/13) * 13 + 1,
1241 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1242 sum(decode(GB.period_num,
1243 floor((X_start_period_num-1)/13) * 13 + 2,
1244 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1245 sum(decode(GB.period_num,
1246 floor((X_start_period_num-1)/13) * 13 + 3,
1247 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1248 sum(decode(GB.period_num,
1249 floor((X_start_period_num-1)/13) * 13 + 4,
1250 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1251 sum(decode(GB.period_num,
1252 floor((X_start_period_num-1)/13) * 13 + 5,
1253 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1254 sum(decode(GB.period_num,
1255 floor((X_start_period_num-1)/13) * 13 + 6,
1256 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1257 sum(decode(GB.period_num,
1258 floor((X_start_period_num-1)/13) * 13 + 7,
1259 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1260 sum(decode(GB.period_num,
1261 floor((X_start_period_num-1)/13) * 13 + 8,
1262 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1263 sum(decode(GB.period_num,
1264 floor((X_start_period_num-1)/13) * 13 + 9,
1265 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1266 sum(decode(GB.period_num,
1267 floor((X_start_period_num-1)/13) * 13 + 10,
1268 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1269 sum(decode(GB.period_num,
1270 floor((X_start_period_num-1)/13) * 13 + 11,
1271 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1272 sum(decode(GB.period_num,
1273 floor((X_start_period_num-1)/13) * 13 + 12,
1274 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1275 sum(decode(GB.period_num,
1276 floor((X_start_period_num-1)/13) * 13 + 13,
1277 (nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
1278 decode(max(GB.rowid), null, 0, 1)
1279 FROM
1280 GL_BALANCES GB
1281 WHERE
1282 GB.ledger_id = X_ledger_id
1283 AND GB.code_combination_id = X_code_combination_id
1284 AND GB.currency_code = decode(X_budget_rule,
1285 'PYAS', 'STAT', 'CYAS', 'STAT',
1286 X_currency_code)
1287 AND GB.actual_flag = 'A'
1288 AND GB.period_year = X_period_year -
1289 decode(X_budget_rule, 'PYAS', 1, 'PYAM', 1, 0)
1290 AND GB.period_num BETWEEN X_start_period_num AND X_end_period_num
1291 AND nvl(GB.translated_flag, 'R') NOT IN ('Y', 'N');
1292
1293 p1_amount NUMBER := 0;
1294 p2_amount NUMBER := 0;
1295 p3_amount NUMBER := 0;
1296 p4_amount NUMBER := 0;
1297 p5_amount NUMBER := 0;
1298 p6_amount NUMBER := 0;
1299 p7_amount NUMBER := 0;
1300 p8_amount NUMBER := 0;
1301 p9_amount NUMBER := 0;
1302 p10_amount NUMBER := 0;
1303 p11_amount NUMBER := 0;
1304 p12_amount NUMBER := 0;
1305 p13_amount NUMBER := 0;
1306
1307
1308 /* Keep track of whether or not balance and
1309 funds reservation data was found */
1310 bal_data_found NUMBER := 0;
1311 res_data_found NUMBER := 0;
1312
1313 eff_period_year NUMBER := X_period_year;
1314
1315 BEGIN
1316
1317 OPEN gbl;
1318 FETCH gbl INTO X_period1_amount,
1319 X_period2_amount,
1320 X_period3_amount,
1321 X_period4_amount,
1322 X_period5_amount,
1323 X_period6_amount,
1324 X_period7_amount,
1325 X_period8_amount,
1326 X_period9_amount,
1327 X_period10_amount,
1328 X_period11_amount,
1329 X_period12_amount,
1330 X_period13_amount,
1331 bal_data_found;
1332 CLOSE gbl;
1333
1334 -- Add the reserved amounts, if necessary.
1335 IF (X_bc_on = 'Y') THEN
1336 IF (X_budget_rule IN ('PYAS', 'PYAM')) THEN
1337 eff_period_year := X_period_year - 1;
1338 END IF;
1339 get_approved_budget_amounts( X_ledger_id,
1340 X_code_combination_id,
1341 X_currency_code,
1342 'A',
1343 -1,
1344 eff_period_year,
1345 X_start_period_num,
1346 X_end_period_num,
1347 X_dr_sign,
1348 res_data_found,
1349 p1_amount,
1350 p2_amount,
1351 p3_amount,
1352 p4_amount,
1353 p5_amount,
1354 p6_amount,
1355 p7_amount,
1356 p8_amount,
1357 p9_amount,
1358 p10_amount,
1359 p11_amount,
1360 p12_amount,
1361 p13_amount);
1362 END IF;
1363
1364 -- If no data has been found, then error out.
1365 IF ( (bal_data_found = 0)
1366 AND (res_data_found = 0)
1367 ) THEN
1368 RAISE No_Data_Found;
1369 END IF;
1370
1371 -- Calculate the new amounts
1372 X_period1_amount := round( ( nvl(X_period1_amount, 0)
1373 + nvl(p1_amount, 0))
1374 * X_rule_amount, 2);
1375 X_period2_amount := round( ( nvl(X_period2_amount, 0)
1376 + nvl(p2_amount, 0))
1377 * X_rule_amount, 2);
1378 X_period3_amount := round( ( nvl(X_period3_amount, 0)
1379 + nvl(p3_amount, 0))
1380 * X_rule_amount, 2);
1381 X_period4_amount := round( ( nvl(X_period4_amount, 0)
1382 + nvl(p4_amount, 0))
1383 * X_rule_amount, 2);
1384 X_period5_amount := round( ( nvl(X_period5_amount, 0)
1385 + nvl(p5_amount, 0))
1386 * X_rule_amount, 2);
1387 X_period6_amount := round( ( nvl(X_period6_amount, 0)
1388 + nvl(p6_amount, 0))
1389 * X_rule_amount, 2);
1390 X_period7_amount := round( ( nvl(X_period7_amount, 0)
1391 + nvl(p7_amount, 0))
1392 * X_rule_amount, 2);
1393 X_period8_amount := round( ( nvl(X_period8_amount, 0)
1394 + nvl(p8_amount, 0))
1395 * X_rule_amount, 2);
1396 X_period9_amount := round( ( nvl(X_period9_amount, 0)
1397 + nvl(p9_amount, 0))
1398 * X_rule_amount, 2);
1399 X_period10_amount := round( ( nvl(X_period10_amount, 0)
1400 + nvl(p10_amount, 0))
1401 * X_rule_amount, 2);
1402 X_period11_amount := round( ( nvl(X_period11_amount, 0)
1403 + nvl(p11_amount, 0))
1404 * X_rule_amount, 2);
1405 X_period12_amount := round( ( nvl(X_period12_amount, 0)
1406 + nvl(p12_amount, 0))
1407 * X_rule_amount, 2);
1408 X_period13_amount := round( ( nvl(X_period13_amount, 0)
1409 + nvl(p13_amount, 0))
1410 * X_rule_amount, 2);
1411
1412 EXCEPTION
1413 WHEN NO_DATA_FOUND THEN
1414 fnd_message.set_name('SQLGL', 'GL_BUD_RULE_NO_DATA_FOUND');
1415 app_exception.raise_exception;
1416 WHEN OTHERS THEN
1417 app_exception.raise_exception;
1418
1419 END get_brule_actual_amounts;
1420
1421 PROCEDURE DB_get_account_properties (
1422 X_ledger_id NUMBER,
1423 X_coa_id NUMBER,
1424 X_code_combination_id NUMBER,
1425 X_budget_version_id NUMBER,
1426 X_budget_entity_id NUMBER,
1427 X_check_is_acct_stat_enterable VARCHAR2,
1428 X_check_is_account_frozen VARCHAR2,
1429 X_cc_stat_enterable_flag IN OUT NOCOPY VARCHAR2,
1430 X_cc_frozen_flag IN OUT NOCOPY VARCHAR2,
1431 X_currency_code VARCHAR2)
1432 IS
1433 BEGIN
1434
1435 IF X_check_is_acct_stat_enterable = 'Y' THEN
1436 -- Call server side function to check whether you can
1437 -- enter STAT amount for this account
1438 IF ( gl_budget_assignment_pkg.is_acct_stat_enterable(
1439 X_ledger_id,
1440 X_code_combination_id )) THEN
1441 X_cc_stat_enterable_flag := 'Y';
1442 END IF;
1443 END IF;
1444
1445 IF X_check_is_account_frozen ='Y' THEN
1446 -- Call server side function to check whether it's a frozen account
1447 -- w.r.t. the selected budget and org, if it hasn't been checked before
1448 -- Bug Fix 3866812
1449 -- Added X_ledger_id and X_currency_code parameters in the function call
1450 IF (gl_budget_utils_pkg.frozen_account(X_coa_id,
1451 X_budget_version_id,
1452 X_budget_entity_id,
1453 X_code_combination_id,
1454 X_ledger_id,
1455 X_currency_code)) THEN
1456 X_cc_frozen_flag := 'Y';
1457 ELSE
1458 X_cc_frozen_flag := 'N';
1459 END IF;
1460 END IF;
1461
1462 END DB_get_account_properties;
1463
1464 PROCEDURE get_cashe_data( x_ledger_period_type IN OUT NOCOPY VARCHAR2,
1465 x_num_periods_per_year IN OUT NOCOPY NUMBER,
1466 x_ledger_id NUMBER,
1467 x_budget_version_id IN OUT NOCOPY NUMBER,
1468 x_budget_name IN OUT NOCOPY VARCHAR2,
1469 x_bj_required IN OUT NOCOPY VARCHAR2,
1470 x_coa_id NUMBER,
1471 x_account_column_name IN OUT NOCOPY VARCHAR2
1472 ) IS
1473 tmpbuf VARCHAR2(100);
1474 BEGIN
1475
1476 -- Initialize num_periods_per_year (for 445/454/544 budget rules)
1477 gl_period_types_pkg.select_columns(x_ledger_period_type, tmpbuf, tmpbuf,
1478 x_num_periods_per_year);
1479 -- Default Budget Name to current budget
1480 gl_budget_utils_pkg.get_current_budget(x_ledger_id,
1481 x_budget_version_id,
1482 x_budget_name,
1483 x_bj_required);
1484 -- Initialize ACCOUNT segment column name with flex API
1485 IF ( NOT fnd_flex_apis.get_segment_column( 101,
1486 'GL#',
1487 x_coa_id,
1488 'GL_ACCOUNT',
1489 x_account_column_name )) THEN
1490 fnd_message.set_name('SQLGL', 'GL_NO_ACCOUNT_SEG_DEFINED');
1491 app_exception.raise_exception;
1492 END IF;
1493
1494 END get_cashe_data;
1495
1496 END glf02220_pkg;