1 PACKAGE BODY gl_budget_utils_pkg AS
2 /* $Header: glubdmsb.pls 120.11 2005/08/25 22:31:18 djogg ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE get_current_budget(x_ledger_id NUMBER,
9 x_budget_version_id IN OUT NOCOPY NUMBER,
10 x_budget_name IN OUT NOCOPY VARCHAR2,
11 x_bj_required IN OUT NOCOPY VARCHAR2) IS
12
13 CURSOR chk_budget IS
14 SELECT bv.budget_version_id, b.budget_name,
15 b.require_budget_journals_flag
16 FROM gl_budgets b, gl_budget_versions bv
17 WHERE b.ledger_id = x_ledger_id
18 AND b.status = 'C'
19 AND bv.budget_name = b.budget_name
20 AND bv.budget_type = b.budget_type;
21 BEGIN
22 OPEN chk_budget;
23 FETCH chk_budget INTO x_budget_version_id, x_budget_name, x_bj_required;
24 CLOSE chk_budget;
25 END get_current_budget;
26
27 FUNCTION is_funding_budget(x_ledger_id NUMBER,
28 x_funct_curr VARCHAR2,
29 x_budget_version_id NUMBER)
30 RETURN BOOLEAN IS
31
32 CURSOR chk_funding IS
33 SELECT 'Funding budget'
34 FROM gl_budget_assignment_ranges rng, gl_budorg_bc_options bc
35 WHERE rng.ledger_id = x_ledger_id
36 AND rng.currency_code = x_funct_curr
37 AND bc.range_id = rng.range_id
38 AND bc.funds_check_level_code IN ('D', 'B')
39 AND bc.funding_budget_version_id = x_budget_version_id;
40 CURSOR chk_sum_funding IS
41 SELECT 'Summary Funding budget'
42 FROM gl_summary_templates sum, gl_summary_bc_options bc
43 WHERE sum.ledger_id = x_ledger_id
44 AND bc.template_id = sum.template_id
45 AND bc.funds_check_level_code IN ('D','B')
46 AND bc.funding_budget_version_id = x_budget_version_id;
47 dummy VARCHAR2(100);
48 dummy2 VARCHAR2(100);
49 BEGIN
50 OPEN chk_funding;
51 FETCH chk_funding INTO dummy;
52
53 OPEN chk_sum_funding;
54 FETCH chk_sum_funding INTO dummy2;
55
56 IF chk_funding%FOUND OR chk_sum_funding%FOUND THEN
57 CLOSE chk_funding;
58 CLOSE chk_sum_funding;
59 return(TRUE);
60 ELSE
61 CLOSE chk_funding;
62 CLOSE chk_sum_funding;
63 return(FALSE);
64 END IF;
65 END is_funding_budget;
66
67 FUNCTION is_master_budget(budget_id NUMBER)
68 RETURN BOOLEAN IS
69
70 CURSOR chk_master IS
71 SELECT 'Used as master'
72 FROM gl_budget_versions detail
73 WHERE detail.control_budget_version_id = budget_id;
74 dummy VARCHAR2(100);
75 BEGIN
76 OPEN chk_master;
77 FETCH chk_master INTO dummy;
78
79 IF chk_master%FOUND THEN
80 CLOSE chk_master;
81 return(TRUE);
82 ELSE
83 CLOSE chk_master;
84 return(FALSE);
85 END IF;
86 END is_master_budget;
87
88 FUNCTION frozen_budget(budget_version_id NUMBER) RETURN BOOLEAN IS
89 CURSOR chk_frozen IS
90 SELECT status
91 FROM gl_budget_versions
92 WHERE budget_version_id = frozen_budget.budget_version_id;
93 tmp_status VARCHAR2(1);
94 BEGIN
95 OPEN chk_frozen;
96 FETCH chk_frozen INTO tmp_status;
97
98 IF chk_frozen%FOUND THEN
99 CLOSE chk_frozen;
100 IF (tmp_status = 'F') THEN
101 return(TRUE);
102 ELSE
103 return(FALSE);
104 END IF;
105 ELSE
106 CLOSE chk_frozen;
107 RAISE NO_DATA_FOUND;
108 END IF;
109 END frozen_budget;
110
111 FUNCTION frozen_budget_entity(budget_version_id NUMBER,
112 budget_entity_id NUMBER) RETURN BOOLEAN IS
113 CURSOR chk_frozen IS
114 SELECT frozen_flag
115 FROM gl_entity_budgets
116 WHERE budget_version_id = frozen_budget_entity.budget_version_id
117 AND budget_entity_id = frozen_budget_entity.budget_entity_id;
118 tmp_status VARCHAR2(1);
119 BEGIN
120 OPEN chk_frozen;
121 FETCH chk_frozen INTO tmp_status;
122
123 IF chk_frozen%FOUND THEN
124 CLOSE chk_frozen;
125 IF (tmp_status = 'Y') THEN
126 return(TRUE);
127 ELSE
128 return(FALSE);
129 END IF;
130 ELSE
131 CLOSE chk_frozen;
132 RAISE NO_DATA_FOUND;
133 END IF;
134 END frozen_budget_entity;
135 /* Bug Fix 3866812 */
136 /*
137 Modications in frozen_account function:
138 Added two new parameters:
139 Parameters 1. ledger_id 2. Currency_code
140 Added four new cursors :
141 Cursors 1. get_org_name : Fetch the budget organization name
142 corresponding to budget organization id.
143 2. get_entity_id: Fetch the budget organization id to which
144 a code combination belongs.
145 3. get_status : Fetch the status of the given budget organization.
146 4. get_all_meaning : Fetch the meaning of lookup_code 'ALL'.
147 Modified cursor chk_frozen:Added parameter entity_id to the cursor.
148 Change in logic:
149 Fetch the organization name from the organization ID
150 IF organization name is found THEN
151 IF organization name is 'ALL' THEN
152 Fetch user organization (ALL is system defined),
153 based on LEDGER_ID, CCID and CURRENCY.
154 IF user organization is found THEN
155 Check if user organization is frozen
156 IF user organization frozen info found THEN
157 IF user organization is frozen THEN
158 return 'TRUE' (frozen org)
159 Else
160 Check if acccount (CCID) is frozen in the organization.
161 IF account is frozen THEN
162 return 'TRUE'
163 Else
164 return 'FALSE'
165 Else -- user organization frozen info not found then
166 -- donot register new message since this will rarely happen,
167 -- if data modified externally to the system
168 Raise NO_DATA_FOUND
169 Else -- user organization not found
170 -- happens only if data is corrupted
171 Raise NO_DATA_FOUND
172 Else -- organization name is not 'ALL'
173 Check if acccount (CCID) is frozen in the organization.
174 IF account is frozen then
175 return 'TRUE'
176 Else
177 return 'FALSE'
178 Else
179 -- happens only if data is corrupted
180 Raise NO_DATA_FOUND
181 */
182 FUNCTION frozen_account(coa_id NUMBER,
183 budget_version_id NUMBER,
184 budget_entity_id NUMBER,
185 code_combination_id NUMBER,
186 ledger_id NUMBER,
187 currency_code VARCHAR2) RETURN BOOLEAN IS
188 CURSOR chk_frozen (entity_id NUMBER) IS
189 SELECT 'Frozen'
190 FROM gl_code_combinations cc, gl_budget_frozen_ranges bfr
191 WHERE bfr.budget_version_id = frozen_account.budget_version_id
192 AND bfr.budget_entity_id = entity_id
193 AND cc.code_combination_id = frozen_account.code_combination_id
194 AND cc.chart_of_accounts_id = frozen_account.coa_id
195 AND nvl(cc.segment1,'XXX') between nvl(bfr.segment1_low,'XXX')
196 and nvl(bfr.segment1_high,'XXX')
197 AND nvl(cc.segment2,'XXX') between nvl(bfr.segment2_low,'XXX')
198 and nvl(bfr.segment2_high,'XXX')
199 AND nvl(cc.segment3,'XXX') between nvl(bfr.segment3_low,'XXX')
200 and nvl(bfr.segment3_high,'XXX')
201 AND nvl(cc.segment4,'XXX') between nvl(bfr.segment4_low,'XXX')
202 and nvl(bfr.segment4_high,'XXX')
203 AND nvl(cc.segment5,'XXX') between nvl(bfr.segment5_low,'XXX')
204 and nvl(bfr.segment5_high,'XXX')
205 AND nvl(cc.segment6,'XXX') between nvl(bfr.segment6_low,'XXX')
206 and nvl(bfr.segment6_high,'XXX')
207 AND nvl(cc.segment7,'XXX') between nvl(bfr.segment7_low,'XXX')
208 and nvl(bfr.segment7_high,'XXX')
209 AND nvl(cc.segment8,'XXX') between nvl(bfr.segment8_low,'XXX')
210 and nvl(bfr.segment8_high,'XXX')
211 AND nvl(cc.segment9,'XXX') between nvl(bfr.segment9_low,'XXX')
212 and nvl(bfr.segment9_high,'XXX')
213 AND nvl(cc.segment10,'XXX') between nvl(bfr.segment10_low,'XXX')
214 and nvl(bfr.segment10_high,'XXX')
215 AND nvl(cc.segment11,'XXX') between nvl(bfr.segment11_low,'XXX')
216 and nvl(bfr.segment11_high,'XXX')
217 AND nvl(cc.segment12,'XXX') between nvl(bfr.segment12_low,'XXX')
218 and nvl(bfr.segment12_high,'XXX')
219 AND nvl(cc.segment13,'XXX') between nvl(bfr.segment13_low,'XXX')
220 and nvl(bfr.segment13_high,'XXX')
221 AND nvl(cc.segment14,'XXX') between nvl(bfr.segment14_low,'XXX')
222 and nvl(bfr.segment14_high,'XXX')
223 AND nvl(cc.segment15,'XXX') between nvl(bfr.segment15_low,'XXX')
224 and nvl(bfr.segment15_high,'XXX')
225 AND nvl(cc.segment16,'XXX') between nvl(bfr.segment16_low,'XXX')
226 and nvl(bfr.segment16_high,'XXX')
227 AND nvl(cc.segment17,'XXX') between nvl(bfr.segment17_low,'XXX')
228 and nvl(bfr.segment17_high,'XXX')
229 AND nvl(cc.segment18,'XXX') between nvl(bfr.segment18_low,'XXX')
230 and nvl(bfr.segment18_high,'XXX')
231 AND nvl(cc.segment19,'XXX') between nvl(bfr.segment19_low,'XXX')
232 and nvl(bfr.segment19_high,'XXX')
233 AND nvl(cc.segment20,'XXX') between nvl(bfr.segment20_low,'XXX')
234 and nvl(bfr.segment20_high,'XXX')
235 AND nvl(cc.segment21,'XXX') between nvl(bfr.segment21_low,'XXX')
236 and nvl(bfr.segment21_high,'XXX')
237 AND nvl(cc.segment22,'XXX') between nvl(bfr.segment22_low,'XXX')
238 and nvl(bfr.segment22_high,'XXX')
239 AND nvl(cc.segment23,'XXX') between nvl(bfr.segment23_low,'XXX')
240 and nvl(bfr.segment23_high,'XXX')
241 AND nvl(cc.segment24,'XXX') between nvl(bfr.segment24_low,'XXX')
242 and nvl(bfr.segment24_high,'XXX')
243 AND nvl(cc.segment25,'XXX') between nvl(bfr.segment25_low,'XXX')
244 and nvl(bfr.segment25_high,'XXX')
245 AND nvl(cc.segment26,'XXX') between nvl(bfr.segment26_low,'XXX')
246 and nvl(bfr.segment26_high,'XXX')
247 AND nvl(cc.segment27,'XXX') between nvl(bfr.segment27_low,'XXX')
248 and nvl(bfr.segment27_high,'XXX')
249 AND nvl(cc.segment28,'XXX') between nvl(bfr.segment28_low,'XXX')
250 and nvl(bfr.segment28_high,'XXX')
251 AND nvl(cc.segment29,'XXX') between nvl(bfr.segment29_low,'XXX')
252 and nvl(bfr.segment29_high,'XXX')
253 AND nvl(cc.segment30,'XXX') between nvl(bfr.segment30_low,'XXX')
254 and nvl(bfr.segment30_high,'XXX');
255 CURSOR get_org_name is
256 SELECT name
257 FROM gl_budget_entities
258 WHERE budget_entity_id = frozen_account.budget_entity_id;
259
260 CURSOR get_entity_id is
261 SELECT budget_entity_id
262 FROM gl_budget_assignments
263 WHERE ledger_id = frozen_account.ledger_id AND
264 code_combination_id = frozen_account.code_combination_id AND
265 currency_code=frozen_account.currency_code;
266
267 CURSOR get_status(entity_id number) is
268 SELECT frozen_flag
269 FROM gl_entity_budgets
270 WHERE budget_version_id=frozen_account.budget_version_id and budget_entity_id=entity_id;
271
272 CURSOR get_all_meaning is
273 SELECT meaning
274 FROM gl_lookups
275 WHERE lookup_type='LITERAL' AND lookup_code='ALL';
276
277 tmp_status VARCHAR2(100);
278 budget_org_all VARCHAR2(30);
279 budget_org_name VARCHAR2(100);
280 bud_entity_id NUMBER;
281 frozen_flag VARCHAR2(2);
282 BEGIN
283 OPEN get_all_meaning;
284 FETCH get_all_meaning INTO budget_org_all;
285 IF get_all_meaning%FOUND THEN
286 CLOSE get_all_meaning;
287 ELSE
288 CLOSE get_all_meaning;
289 END IF;
290 OPEN get_org_name;
291 FETCH get_org_name INTO budget_org_name;
292 IF get_org_name%FOUND THEN
293 CLOSE get_org_name;
294 ELSE
295 CLOSE get_org_name;
296 END IF;
297 IF UPPER(budget_org_name)= UPPER(budget_org_all) THEN
298 OPEN get_entity_id;
299 FETCH get_entity_id INTO bud_entity_id;
300 IF get_entity_id%FOUND THEN
301 CLOSE get_entity_id;
302 OPEN get_status(bud_entity_id);
303 FETCH get_status INTO frozen_flag;
304 IF get_status%FOUND THEN
305 CLOSE get_status;
306 IF frozen_flag='Y' THEN
307 return(TRUE);
308 END IF;
309 ELSE
310 CLOSE get_status;
311 RAISE NO_DATA_FOUND;
312 END IF;
313 ELSE
314 CLOSE get_entity_id;
315 RAISE NO_DATA_FOUND;
316 END IF;
317 OPEN chk_frozen(bud_entity_id);
318 FETCH chk_frozen INTO tmp_status;
319 IF chk_frozen%FOUND THEN
320 CLOSE chk_frozen;
321 return(TRUE);
322 ELSE
323 CLOSE chk_frozen;
324 return(FALSE);
325 END IF;
326 ELSE
327 OPEN chk_frozen(frozen_account.budget_entity_id);
328 FETCH chk_frozen INTO tmp_status;
329 IF chk_frozen%FOUND THEN
330 CLOSE chk_frozen;
331 return(TRUE);
332 ELSE
333 CLOSE chk_frozen;
334 return(FALSE);
335 END IF;
336 END IF;
337 END frozen_account;
338
339 PROCEDURE validate_budget_account(lgr_id NUMBER,
340 coa_id NUMBER,
341 budget_version_id NUMBER,
342 code_combination_id NUMBER,
343 currency_code VARCHAR2,
344 return_code IN OUT NOCOPY VARCHAR2,
345 budget_entity_id IN OUT NOCOPY NUMBER,
346 budget_entity IN OUT NOCOPY VARCHAR2,
347 password_flag IN OUT NOCOPY VARCHAR2,
348 encrypted_password IN OUT NOCOPY VARCHAR2,
349 status_code IN OUT NOCOPY VARCHAR2) IS
350 BEGIN
351
352 -- Find out which budget organization the account is assigned to
353 DECLARE
354 entry_code VARCHAR2(1);
355 retvalue VARCHAR2(1);
356 BEGIN
357 gl_budget_assignment_pkg.select_columns(lgr_id,
358 code_combination_id,
359 currency_code,
360 budget_entity_id,
361 entry_code);
362 retvalue := fnd_data_security.check_function(1.0,'GL_DAS_BUDGET_ORG_U',
363 'GL_DAS_BUDGET_ORG', to_char(budget_entity_id),null,
364 null,null,null,fnd_global.user_name);
365 IF(retvalue <> 'T') THEN
366 return_code := 'A';
367 return;
368 END IF;
369
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 return_code := 'A';
373 return;
374 END;
375
376 -- Get various information about the budget organization
377 gl_budget_entities_pkg.select_columns(budget_entity_id,
378 budget_entity,
379 password_flag,
380 encrypted_password,
381 status_code);
382
383 -- Verify that the budget organization is not frozen
384 IF (gl_budget_utils_pkg.frozen_budget_entity(budget_version_id,
385 budget_entity_id)) THEN
386 return_code := 'O';
387 return;
388 END IF;
389
390 -- Verify that the account is not frozen
391 /* Bug Fix 3866812 */
392 /* Added two parameters lgr_id and currency_code */
393 IF (gl_budget_utils_pkg.frozen_account(coa_id,
394 budget_version_id,
395 budget_entity_id,
396 code_combination_id,
397 lgr_id,
398 currency_code)) THEN
399 return_code := 'F';
400 return;
401 END IF;
402
403 return_code := 'Z';
404 return;
405 END validate_budget_account;
406
407
408 FUNCTION get_unique_id RETURN NUMBER IS
409 -- get unique id for range_id
410 CURSOR c_getid IS
411 SELECT GL_BUDGET_FROZEN_RANGES_S.NEXTVAL
412 FROM dual;
413 id number;
414
415 BEGIN
416 OPEN c_getid;
417 FETCH c_getid INTO id;
418
419 IF c_getid%FOUND THEN
420 CLOSE c_getid;
421 RETURN( id );
422 ELSE
423 CLOSE c_getid;
424 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
425 fnd_message.set_token('SEQUENCE', 'GL_BUDGET_FROZEN_RANGES_S');
426 app_exception.raise_exception;
427 END IF;
428
429 EXCEPTION
430 WHEN APP_EXCEPTION.application_exception THEN
431 RAISE;
432 WHEN OTHERS THEN
433 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
434 fnd_message.set_token('PROCEDURE',
435 'gl_budget_utils_pkg.get_unique_id');
436 RAISE;
437 END get_unique_id;
438
439 FUNCTION get_opyr_per_range ( x_budget_version_id IN NUMBER,
440 x_start_period_year IN OUT NOCOPY NUMBER,
441 x_start_period_name IN OUT NOCOPY VARCHAR2,
442 x_start_period_num IN OUT NOCOPY NUMBER,
443 x_end_period_year IN OUT NOCOPY NUMBER,
444 x_end_period_name IN OUT NOCOPY VARCHAR2,
445 x_end_period_num IN OUT NOCOPY NUMBER)
446 RETURN BOOLEAN IS
447 CURSOR get_prd IS
448 SELECT prs.period_year,
449 prs.start_period_name,
450 prs.start_period_num,
451 pre.period_year,
452 pre.end_period_name,
453 pre.end_period_num
454 FROM gl_budget_period_ranges prs,
455 gl_budget_period_ranges pre
456 WHERE prs.budget_version_id = pre.budget_version_id
457 AND prs.budget_version_id = x_budget_version_id
458 AND prs.period_year * 100000 + prs.start_period_num =
459 (SELECT MIN(period_year * 100000 + start_period_num)
460 FROM gl_budget_period_ranges
461 WHERE budget_version_id = x_budget_version_id
462 AND open_flag = 'O')
463 AND pre.period_year * 100000 + pre.start_period_num =
464 (SELECT MAX(period_year * 100000 + start_period_num)
465 FROM gl_budget_period_ranges
466 WHERE budget_version_id = x_budget_version_id
467 AND open_flag = 'O');
468 BEGIN
469 OPEN get_prd;
470 FETCH get_prd INTO x_start_period_year,
471 x_start_period_name,
472 x_start_period_num,
473 x_end_period_year,
474 x_end_period_name,
475 x_end_period_num;
476
477 IF get_prd%FOUND THEN
478 CLOSE get_prd;
479 return (TRUE);
480 ELSE
481 CLOSE get_prd;
482 return (FALSE);
483 END IF;
484 END get_opyr_per_range;
485
486
487
488 PROCEDURE validate_budget(
489 X_Rowid IN OUT NOCOPY VARCHAR2,
490 X_Budget_Type VARCHAR2,
491 X_Budget_Name VARCHAR2,
492 X_Ledger_Id NUMBER,
493 X_Status VARCHAR2,
494 X_Date_Created DATE,
495 X_Require_Budget_Journals_Flag VARCHAR2,
496 X_Current_Version_Id NUMBER DEFAULT NULL,
497 X_Latest_Opened_Year NUMBER DEFAULT NULL,
498 X_First_Valid_Period_Name VARCHAR2 DEFAULT NULL,
499 X_Last_Valid_Period_Name VARCHAR2 DEFAULT NULL,
500 X_Description VARCHAR2 DEFAULT NULL,
501 X_Date_Closed DATE DEFAULT NULL,
502 X_Attribute1 VARCHAR2 DEFAULT NULL,
503 X_Attribute2 VARCHAR2 DEFAULT NULL,
504 X_Attribute3 VARCHAR2 DEFAULT NULL,
505 X_Attribute4 VARCHAR2 DEFAULT NULL,
506 X_Attribute5 VARCHAR2 DEFAULT NULL,
507 X_Attribute6 VARCHAR2 DEFAULT NULL,
508 X_Attribute7 VARCHAR2 DEFAULT NULL,
509 X_Attribute8 VARCHAR2 DEFAULT NULL,
510 X_Context VARCHAR2 DEFAULT NULL,
511 X_User_Id NUMBER,
512 X_Login_Id NUMBER,
513 X_Date DATE,
514 X_Budget_Version_Id NUMBER,
515 X_Master_Budget_Version_Id NUMBER DEFAULT NULL) IS
516
517 CURSOR chk_first_period IS
518 SELECT period_year, period_num
519 FROM gl_period_statuses
520 WHERE application_id = 101
521 AND ledger_id = X_Ledger_Id
522 AND period_num between 1 and 60
523 AND period_name = X_First_Valid_Period_Name;
524
525 CURSOR chk_last_period
526 (p_first_valid_period_year NUMBER,
527 p_first_valid_period_num NUMBER) IS
528 SELECT period_year, period_num
529 FROM gl_period_statuses
530 WHERE application_id = 101
531 AND ledger_id = X_Ledger_Id
532 AND period_num between 1 and 60
533 AND period_year * 10000 + period_num >=
534 p_first_valid_period_year * 10000 + p_first_valid_period_num
535 AND period_name = X_Last_Valid_Period_Name;
536
537 v_rbj_flag VARCHAR2(1);
538 v_func_curr_code VARCHAR2(15);
539 v_ledger_name VARCHAR2(30);
540 v_first_valid_period_year NUMBER;
541 v_first_valid_period_num NUMBER;
542 v_last_valid_period_year NUMBER;
543 v_last_valid_period_num NUMBER;
544
545 BEGIN
546
547 -- validate Require_Budget_Journals_Flag
548 -- the flag cannot be 'N' if
549 -- the set_of_books has its require_budget_journals_flag checked, or
550 -- the budget is a funding budget
551 IF (X_Require_Budget_Journals_Flag = 'N') THEN
552
553 SELECT require_budget_journals_flag, currency_code, name
554 INTO v_rbj_flag, v_func_curr_code, v_ledger_name
555 FROM gl_ledgers
556 WHERE ledger_id = X_Ledger_Id;
557
558 IF (v_rbj_flag = 'Y') THEN
559 fnd_message.set_name('SQLGL', 'GL_API_BUDGET_REQUIRE_JOURNALS');
560 fnd_message.set_token('LEDGER_NAME', v_ledger_name);
561 app_exception.raise_exception;
562 END IF;
563
564 IF (gl_budget_utils_pkg.is_funding_budget(
565 X_Ledger_Id,
566 v_func_curr_code,
567 X_Budget_Version_Id)) THEN
568 fnd_message.set_name('SQLGL', 'GL_API_BUDGET_FUNDING_JOURNALS');
569 fnd_message.set_token('BUDGET', X_Budget_Name);
570 app_exception.raise_exception;
571 END IF;
572 END IF;
573
574 -- validate First_Valid_Period_Name
575 OPEN chk_first_period;
576 FETCH chk_first_period
577 INTO v_first_valid_period_year, v_first_valid_period_num;
578
579 IF chk_first_period%NOTFOUND THEN
580 CLOSE chk_first_period;
581 fnd_message.set_name('SQLGL', 'GL_API_INVALID_VALUE');
582 fnd_message.set_token('VALUE', NVL(X_First_Valid_Period_Name, 'null'));
583 fnd_message.set_token('ATTRIBUTE', 'FirstValidPeriodName');
584 app_exception.raise_exception;
585 END IF;
586
587 CLOSE chk_first_period;
588
589 -- validate Last_Valid_Period_Name
590 OPEN chk_last_period(v_first_valid_period_year, v_first_valid_period_num);
591 FETCH chk_last_period
592 INTO v_last_valid_period_year, v_last_valid_period_num;
593
594 IF chk_last_period%NOTFOUND THEN
595 CLOSE chk_last_period;
596 fnd_message.set_name('SQLGL', 'GL_API_INVALID_VALUE');
597 fnd_message.set_token('VALUE', NVL(X_Last_Valid_Period_Name, 'null'));
598 fnd_message.set_token('ATTRIBUTE', 'LastValidPeriodName');
599 app_exception.raise_exception;
600 END IF;
601
602 CLOSE chk_last_period;
603
604 EXCEPTION
605 WHEN app_exceptions.application_exception THEN
606 RAISE;
607 WHEN OTHERS THEN
608 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
609 RAISE;
610
611 END validate_budget;
612
613
614 END gl_budget_utils_pkg;