[Home] [Help]
PACKAGE BODY: APPS.GL_BUDGET_ENTITIES_PKG
Source
1 PACKAGE BODY gl_budget_entities_pkg AS
2 /* $Header: glibdorb.pls 120.8.12010000.2 2009/03/20 06:03:02 skotakar ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Used to select a particular budget organization row
13 -- History
14 -- 21-MAR-93 D. J. Ogg Created
15 -- Arguments
16 -- recinfo Various information about the row
17 -- Example
18 -- gl_budget_entities_pkg.select_row(recinfo)
19 -- Notes
20 --
21 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_budget_entities%ROWTYPE) IS
22 BEGIN
23 SELECT *
24 INTO recinfo
25 FROM gl_budget_entities
26 WHERE budget_entity_id = recinfo.budget_entity_id;
27 END SELECT_ROW;
28
29
30 --
31 -- PUBLIC FUNCTIONS
32 --
33
34 PROCEDURE check_unique(lgr_id NUMBER, org_name VARCHAR2,
35 row_id VARCHAR2) IS
36 CURSOR chk_duplicates is
37 SELECT status_code
38 FROM GL_BUDGET_ENTITIES be
39 WHERE be.ledger_id = lgr_id
40 AND be.name = org_name
41 AND ( row_id is null
42 OR be.rowid <> row_id);
43 status_code VARCHAR2(1);
44 BEGIN
45 OPEN chk_duplicates;
46 FETCH chk_duplicates INTO status_code;
47
48 IF chk_duplicates%FOUND THEN
49 CLOSE chk_duplicates;
50 IF ( status_code = 'D' ) THEN
51 fnd_message.set_name('SQLGL', 'GL_DUP_BUD_ORG_DEL_INP');
52 app_exception.raise_exception;
53 ELSE
54 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_BUD_ORG_NAME');
55 app_exception.raise_exception;
56 END IF;
57 END IF;
58
59 CLOSE chk_duplicates;
60
61 EXCEPTION
62 WHEN app_exceptions.application_exception THEN
63 RAISE;
64 WHEN OTHERS THEN
65 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
66 fnd_message.set_token('PROCEDURE',
67 'gl_budget_entities_pkg.check_unique');
68 RAISE;
69 END check_unique;
70
71 FUNCTION check_for_all(lgr_id NUMBER, row_id VARCHAR2) RETURN BOOLEAN IS
72 CURSOR check_for_all is
73 SELECT 'Exists'
74 FROM GL_BUDGET_ENTITIES be, gl_lookups l
75 WHERE l.lookup_type = 'LITERAL'
76 AND l.lookup_code = 'ALL'
77 AND upper(be.name) = upper(l.meaning)
78 AND be.ledger_id = lgr_id
79 AND ( row_id is null
80 OR be.rowid <> row_id);
81 dummy VARCHAR2(100);
82 BEGIN
83 OPEN check_for_all;
84 FETCH check_for_all INTO dummy;
85
86 IF check_for_all%FOUND THEN
87 CLOSE check_for_all;
88 return(TRUE);
89 ELSE
90 CLOSE check_for_all;
91 return(FALSE);
92 END IF;
93
94 EXCEPTION
95 WHEN app_exceptions.application_exception THEN
96 RAISE;
97 WHEN OTHERS THEN
98 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
99 fnd_message.set_token('PROCEDURE',
100 'gl_budget_entities_pkg.check_for_all');
101 RAISE;
102 END check_for_all;
103
104 FUNCTION has_ranges(org_id NUMBER) RETURN BOOLEAN IS
105 CURSOR check_for_ranges is
106 SELECT 'Has ranges'
107 FROM GL_BUDGET_ASSIGNMENT_RANGES bar
108 WHERE bar.budget_entity_id = org_id;
109 dummy VARCHAR2(100);
110 BEGIN
111 OPEN check_for_ranges;
112 FETCH check_for_ranges INTO dummy;
113
114 IF check_for_ranges%FOUND THEN
115 CLOSE check_for_ranges;
116 return(TRUE);
117 ELSE
118 CLOSE check_for_ranges;
119 return(FALSE);
120 END IF;
121
122 EXCEPTION
123 WHEN app_exceptions.application_exception THEN
124 RAISE;
125 WHEN OTHERS THEN
126 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
127 fnd_message.set_token('PROCEDURE',
128 'gl_budget_entities_pkg.has_ranges');
129 RAISE;
130 END has_ranges;
131
132 FUNCTION get_unique_id RETURN NUMBER IS
133 CURSOR get_new_id IS
134 SELECT gl_budget_entities_s.NEXTVAL
135 FROM dual;
136 new_id number;
137 BEGIN
138 OPEN get_new_id;
139 FETCH get_new_id INTO new_id;
140
141 IF get_new_id%FOUND THEN
142 CLOSE get_new_id;
143 return(new_id);
144 ELSE
145 CLOSE get_new_id;
146 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
147 fnd_message.set_token('SEQUENCE', 'GL_BUDGET_ENTITIES_S');
148 app_exception.raise_exception;
149 END IF;
150
151 EXCEPTION
152 WHEN app_exceptions.application_exception THEN
153 RAISE;
154 WHEN OTHERS THEN
155 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
156 fnd_message.set_token('PROCEDURE',
157 'gl_budget_entities_pkg.get_unique_id');
158 RAISE;
159 END get_unique_id;
160
161 PROCEDURE lock_organization(org_id NUMBER) IS
162 CURSOR lock_org is
163 SELECT 'Locked organization'
164 FROM GL_BUDGET_ENTITIES be
165 WHERE be.budget_entity_id = org_id
166 AND not be.status_code = 'D'
167 FOR UPDATE OF status_code;
168 dummy VARCHAR2(100);
169 BEGIN
170 OPEN lock_org;
171 FETCH lock_org INTO dummy;
172
173 IF NOT lock_org%FOUND THEN
174 CLOSE lock_org;
175 fnd_message.set_name('SQLGL', 'GL_BUDORG_DELETED');
176 app_exception.raise_exception;
177 END IF;
178
179 CLOSE lock_org;
180 EXCEPTION
181 WHEN app_exceptions.application_exception THEN
182 RAISE;
183 WHEN OTHERS THEN
184 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
185 fnd_message.set_token('PROCEDURE',
186 'gl_budget_entities_pkg.lock_organization');
187 RAISE;
188 END lock_organization;
189
190 PROCEDURE select_columns( entity_id NUMBER,
191 entity_name IN OUT NOCOPY VARCHAR2,
192 password_required_flag IN OUT NOCOPY VARCHAR2,
193 encrypted_password IN OUT NOCOPY VARCHAR2,
194 status_code IN OUT NOCOPY VARCHAR2,
195 security_flag IN OUT NOCOPY VARCHAR2--Added as part of bug7382899
196 ) IS
197 recinfo gl_budget_entities%ROWTYPE;
198
199 BEGIN
200 recinfo.budget_entity_id := entity_id;
201
202 select_row(recinfo);
203
204 entity_name := recinfo.name;
205 password_required_flag := recinfo.budget_password_required_flag;
206 encrypted_password := recinfo.encrypted_budget_password;
207 status_code := recinfo.status_code;
208 security_flag := recinfo.security_flag;--Added as part of bug7382899
209 END select_columns;
210
211 PROCEDURE budget_and_account_seg_info(
212 lgr_id NUMBER,
213 coa_id NUMBER,
214 x_budget_version_id IN OUT NOCOPY NUMBER,
215 x_budget_name IN OUT NOCOPY VARCHAR2,
216 x_bj_required IN OUT NOCOPY VARCHAR2,
217 x_segment_name OUT NOCOPY VARCHAR2) IS
218 BEGIN
219 IF (lgr_id IS NOT NULL) then
220 x_segment_name := gl_flexfields_pkg.get_account_segment(coa_id);
221 END IF;
222 gl_budget_utils_pkg.get_current_budget(lgr_id,
223 x_budget_version_id,
224 x_budget_name,
225 x_bj_required);
226 EXCEPTION
227 WHEN app_exceptions.application_exception THEN
228 RAISE;
229 WHEN OTHERS THEN
230 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
231 fnd_message.set_token('PROCEDURE',
232 'gl_budget_entities_pkg.budget_and_acount_seg_info');
233 RAISE;
234 END budget_and_account_seg_info;
235
236 --** Added Security_Flag for Definition Access Set enhancement
237 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
238 X_Budget_Entity_Id IN OUT NOCOPY NUMBER,
239 X_Name VARCHAR2,
240 X_Ledger_Id NUMBER,
241 X_Last_Update_Date DATE,
242 X_Last_Updated_By NUMBER,
243 X_Budget_Password_Required VARCHAR2,
244 X_Status_Code VARCHAR2,
245 X_Creation_Date DATE,
246 X_Created_By NUMBER,
247 X_Last_Update_Login NUMBER,
248 X_Encrypted_Budget_Password VARCHAR2,
249 X_Description VARCHAR2,
250 X_Start_Date DATE,
251 X_End_Date DATE,
252 X_Segment1_Type NUMBER,
253 X_Segment2_Type NUMBER,
254 X_Segment3_Type NUMBER,
255 X_Segment4_Type NUMBER,
256 X_Segment5_Type NUMBER,
257 X_Segment6_Type NUMBER,
258 X_Segment7_Type NUMBER,
259 X_Segment8_Type NUMBER,
260 X_Segment9_Type NUMBER,
261 X_Segment10_Type NUMBER,
262 X_Segment11_Type NUMBER,
263 X_Segment12_Type NUMBER,
264 X_Segment13_Type NUMBER,
265 X_Segment14_Type NUMBER,
266 X_Segment15_Type NUMBER,
267 X_Segment16_Type NUMBER,
268 X_Segment17_Type NUMBER,
269 X_Segment18_Type NUMBER,
270 X_Segment19_Type NUMBER,
271 X_Segment20_Type NUMBER,
272 X_Segment21_Type NUMBER,
273 X_Segment22_Type NUMBER,
274 X_Segment23_Type NUMBER,
275 X_Segment24_Type NUMBER,
276 X_Segment25_Type NUMBER,
277 X_Segment26_Type NUMBER,
278 X_Segment27_Type NUMBER,
279 X_Segment28_Type NUMBER,
280 X_Segment29_Type NUMBER,
281 X_Segment30_Type NUMBER,
282 X_Attribute1 VARCHAR2,
283 X_Attribute2 VARCHAR2,
284 X_Attribute3 VARCHAR2,
285 X_Attribute4 VARCHAR2,
286 X_Attribute5 VARCHAR2,
287 X_Attribute6 VARCHAR2,
288 X_Attribute7 VARCHAR2,
289 X_Attribute8 VARCHAR2,
290 X_Attribute9 VARCHAR2,
291 X_Attribute10 VARCHAR2,
292 X_Context VARCHAR2,
293 X_All_Name BOOLEAN,
294 X_Chart_Of_Accounts_Id NUMBER,
295 X_Security_Flag VARCHAR2
296 ) IS
297 CURSOR C IS SELECT rowid FROM gl_budget_entities
298 WHERE budget_entity_id = X_Budget_Entity_Id;
299
300 L_Status_Code VARCHAR2(1);
301
302 BEGIN
303 -- Make sure only one ALL organization
304 IF (X_All_Name AND
305 gl_budget_entities_pkg.check_for_all(
306 X_Ledger_Id,
307 X_Rowid)) THEN
308 fnd_message.set_name('SQLGL', 'GL_BUDGET_ONLY_ONE_ALL');
309 app_exception.raise_exception;
310 END IF;
311
312 -- Set budget_entity_id
313 -- Changed functionality to retrieve id from sequence only if
314 -- routine is not called from the iSpeed API, since the API
315 -- retrieves and passes in the id.
316
317 IF (X_Status_Code = 'ISPEED') THEN
318 L_Status_Code := 'C';
319 ELSE
320 L_Status_Code := X_Status_Code;
321 END IF;
322
323 -- Insert the corresponding rows in gl_entity_budgets.
324 gl_entity_budgets_pkg.insert_entity(
325 X_Budget_Entity_Id,
326 X_Ledger_Id,
327 X_Last_Updated_By,
328 X_Last_Update_Login);
329
330 -- Insert a budget timestamp for the chart of accounts,
331 -- if necessary.
332 gl_bc_event_tstamps_pkg.insert_event_timestamp(
333 X_Chart_Of_Accounts_Id,
334 'B',
335 X_Last_Updated_By,
336 X_Last_Update_Login);
337
338
339 INSERT INTO gl_budget_entities(
340
341 budget_entity_id,
342 name,
343 ledger_id,
344 last_update_date,
345 last_updated_by,
346 budget_password_required_flag,
347 status_code,
348 creation_date,
349 created_by,
350 last_update_login,
351 encrypted_budget_password,
352 description,
353 start_date,
354 end_date,
355 segment1_type,
356 segment2_type,
357 segment3_type,
358 segment4_type,
359 segment5_type,
360 segment6_type,
361 segment7_type,
362 segment8_type,
363 segment9_type,
364 segment10_type,
365 segment11_type,
366 segment12_type,
367 segment13_type,
368 segment14_type,
369 segment15_type,
370 segment16_type,
371 segment17_type,
372 segment18_type,
373 segment19_type,
374 segment20_type,
375 segment21_type,
376 segment22_type,
377 segment23_type,
378 segment24_type,
379 segment25_type,
380 segment26_type,
381 segment27_type,
382 segment28_type,
383 segment29_type,
384 segment30_type,
385 attribute1,
386 attribute2,
387 attribute3,
388 attribute4,
389 attribute5,
390 attribute6,
391 attribute7,
392 attribute8,
393 attribute9,
394 attribute10,
395 context,
396 security_flag)
397 VALUES (
398
399 X_Budget_Entity_Id,
400 X_Name,
401 X_Ledger_Id,
402 X_Last_Update_Date,
403 X_Last_Updated_By,
404 X_Budget_Password_Required,
405 L_Status_Code,
406 X_Creation_Date,
407 X_Created_By,
408 X_Last_Update_Login,
409 X_Encrypted_Budget_Password,
410 X_Description,
411 X_Start_Date,
412 X_End_Date,
413 X_Segment1_Type,
414 X_Segment2_Type,
415 X_Segment3_Type,
416 X_Segment4_Type,
417 X_Segment5_Type,
418 X_Segment6_Type,
419 X_Segment7_Type,
420 X_Segment8_Type,
421 X_Segment9_Type,
422 X_Segment10_Type,
423 X_Segment11_Type,
424 X_Segment12_Type,
425 X_Segment13_Type,
426 X_Segment14_Type,
427 X_Segment15_Type,
428 X_Segment16_Type,
429 X_Segment17_Type,
430 X_Segment18_Type,
431 X_Segment19_Type,
432 X_Segment20_Type,
433 X_Segment21_Type,
434 X_Segment22_Type,
435 X_Segment23_Type,
436 X_Segment24_Type,
437 X_Segment25_Type,
438 X_Segment26_Type,
439 X_Segment27_Type,
440 X_Segment28_Type,
441 X_Segment29_Type,
442 X_Segment30_Type,
443 X_Attribute1,
444 X_Attribute2,
445 X_Attribute3,
446 X_Attribute4,
447 X_Attribute5,
448 X_Attribute6,
449 X_Attribute7,
450 X_Attribute8,
451 X_Attribute9,
452 X_Attribute10,
453 X_Context,
454 X_Security_Flag);
455
456 OPEN C;
457 FETCH C INTO X_Rowid;
458 if (C%NOTFOUND) then
459 CLOSE C;
460 Raise NO_DATA_FOUND;
461 end if;
462 CLOSE C;
463
464 EXCEPTION
465 WHEN app_exceptions.application_exception THEN
466 RAISE;
467 WHEN OTHERS THEN
468 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
469 fnd_message.set_token('PROCEDURE',
470 'gl_budget_entities_pkg.insert_row');
471 RAISE;
472 END Insert_Row;
473
474 --** Added Security_Flag for Definition Access Set enhancement
475 PROCEDURE Update_Row(X_Rowid VARCHAR2,
476
477 X_Budget_Entity_Id NUMBER,
478 X_Name VARCHAR2,
479 X_Ledger_Id NUMBER,
480 X_Last_Update_Date DATE,
481 X_Last_Updated_By NUMBER,
482 X_Budget_Password_Required VARCHAR2,
483 X_Status_Code VARCHAR2,
484 X_Last_Update_Login NUMBER,
485 X_Encrypted_Budget_Password VARCHAR2,
486 X_Description VARCHAR2,
487 X_Start_Date DATE,
488 X_End_Date DATE,
489 X_Segment1_Type NUMBER,
490 X_Segment2_Type NUMBER,
491 X_Segment3_Type NUMBER,
492 X_Segment4_Type NUMBER,
493 X_Segment5_Type NUMBER,
494 X_Segment6_Type NUMBER,
495 X_Segment7_Type NUMBER,
496 X_Segment8_Type NUMBER,
497 X_Segment9_Type NUMBER,
498 X_Segment10_Type NUMBER,
499 X_Segment11_Type NUMBER,
500 X_Segment12_Type NUMBER,
501 X_Segment13_Type NUMBER,
502 X_Segment14_Type NUMBER,
503 X_Segment15_Type NUMBER,
504 X_Segment16_Type NUMBER,
505 X_Segment17_Type NUMBER,
506 X_Segment18_Type NUMBER,
507 X_Segment19_Type NUMBER,
508 X_Segment20_Type NUMBER,
509 X_Segment21_Type NUMBER,
510 X_Segment22_Type NUMBER,
514 X_Segment26_Type NUMBER,
511 X_Segment23_Type NUMBER,
512 X_Segment24_Type NUMBER,
513 X_Segment25_Type NUMBER,
515 X_Segment27_Type NUMBER,
516 X_Segment28_Type NUMBER,
517 X_Segment29_Type NUMBER,
518 X_Segment30_Type NUMBER,
519 X_Attribute1 VARCHAR2,
520 X_Attribute2 VARCHAR2,
521 X_Attribute3 VARCHAR2,
522 X_Attribute4 VARCHAR2,
523 X_Attribute5 VARCHAR2,
524 X_Attribute6 VARCHAR2,
525 X_Attribute7 VARCHAR2,
526 X_Attribute8 VARCHAR2,
527 X_Attribute9 VARCHAR2,
528 X_Attribute10 VARCHAR2,
529 X_Context VARCHAR2,
530 X_All_Name BOOLEAN,
531 X_Security_Flag VARCHAR2
532
533 ) IS
534 BEGIN
535
536 -- Make sure only one ALL organization
537 IF (X_All_Name AND
538 gl_budget_entities_pkg.check_for_all(
539 X_Ledger_Id,
540 X_Rowid)) THEN
541 fnd_message.set_name('SQLGL', 'GL_BUDGET_ONLY_ONE_ALL');
542 app_exception.raise_exception;
543 END IF;
544
545 UPDATE gl_budget_entities
546 SET
547 budget_entity_id = X_Budget_Entity_Id,
548 name = X_Name,
549 ledger_id = X_Ledger_Id,
550 last_update_date = X_Last_Update_Date,
551 last_updated_by = X_Last_Updated_By,
552 budget_password_required_flag = X_Budget_Password_Required,
553 status_code = X_Status_Code,
554 last_update_login = X_Last_Update_Login,
555 encrypted_budget_password = X_Encrypted_Budget_Password,
556 description = X_Description,
557 start_date = X_Start_Date,
558 end_date = X_End_Date,
559 segment1_type = X_Segment1_Type,
560 segment2_type = X_Segment2_Type,
561 segment3_type = X_Segment3_Type,
562 segment4_type = X_Segment4_Type,
563 segment5_type = X_Segment5_Type,
564 segment6_type = X_Segment6_Type,
565 segment7_type = X_Segment7_Type,
566 segment8_type = X_Segment8_Type,
567 segment9_type = X_Segment9_Type,
568 segment10_type = X_Segment10_Type,
569 segment11_type = X_Segment11_Type,
570 segment12_type = X_Segment12_Type,
571 segment13_type = X_Segment13_Type,
572 segment14_type = X_Segment14_Type,
573 segment15_type = X_Segment15_Type,
574 segment16_type = X_Segment16_Type,
575 segment17_type = X_Segment17_Type,
576 segment18_type = X_Segment18_Type,
577 segment19_type = X_Segment19_Type,
578 segment20_type = X_Segment20_Type,
579 segment21_type = X_Segment21_Type,
580 segment22_type = X_Segment22_Type,
581 segment23_type = X_Segment23_Type,
582 segment24_type = X_Segment24_Type,
583 segment25_type = X_Segment25_Type,
584 segment26_type = X_Segment26_Type,
585 segment27_type = X_Segment27_Type,
586 segment28_type = X_Segment28_Type,
587 segment29_type = X_Segment29_Type,
588 segment30_type = X_Segment30_Type,
589 attribute1 = X_Attribute1,
590 attribute2 = X_Attribute2,
591 attribute3 = X_Attribute3,
592 attribute4 = X_Attribute4,
593 attribute5 = X_Attribute5,
594 attribute6 = X_Attribute6,
595 attribute7 = X_Attribute7,
596 attribute8 = X_Attribute8,
597 attribute9 = X_Attribute9,
598 attribute10 = X_Attribute10,
599 context = X_Context,
600 security_flag = X_Security_Flag
601 WHERE rowid = X_Rowid;
602
603 if (SQL%NOTFOUND) then
604 Raise NO_DATA_FOUND;
605 end if;
606 EXCEPTION
607 WHEN app_exceptions.application_exception THEN
608 RAISE;
609 WHEN OTHERS THEN
610 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
611 fnd_message.set_token('PROCEDURE',
612 'gl_budget_entities_pkg.update_row');
613 RAISE;
614 END Update_Row;
615
616 --** Added for Definition Access Set enhancement
617 PROCEDURE Lock_Row (X_Rowid IN OUT NOCOPY VARCHAR2,
618 X_Budget_Entity_Id IN OUT NOCOPY NUMBER,
622 X_Status_Code VARCHAR2,
619 X_Name VARCHAR2,
620 X_Ledger_Id NUMBER,
621 X_Budget_Password_Required VARCHAR2,
623 X_Encrypted_Budget_Password VARCHAR2,
624 X_Description VARCHAR2,
625 X_Start_Date DATE,
626 X_End_Date DATE,
627 X_Segment1_Type NUMBER,
628 X_Segment2_Type NUMBER,
629 X_Segment3_Type NUMBER,
630 X_Segment4_Type NUMBER,
631 X_Segment5_Type NUMBER,
632 X_Segment6_Type NUMBER,
633 X_Segment7_Type NUMBER,
634 X_Segment8_Type NUMBER,
635 X_Segment9_Type NUMBER,
636 X_Segment10_Type NUMBER,
637 X_Segment11_Type NUMBER,
638 X_Segment12_Type NUMBER,
639 X_Segment13_Type NUMBER,
640 X_Segment14_Type NUMBER,
641 X_Segment15_Type NUMBER,
642 X_Segment16_Type NUMBER,
643 X_Segment17_Type NUMBER,
644 X_Segment18_Type NUMBER,
645 X_Segment19_Type NUMBER,
646 X_Segment20_Type NUMBER,
647 X_Segment21_Type NUMBER,
648 X_Segment22_Type NUMBER,
649 X_Segment23_Type NUMBER,
650 X_Segment24_Type NUMBER,
651 X_Segment25_Type NUMBER,
652 X_Segment26_Type NUMBER,
653 X_Segment27_Type NUMBER,
654 X_Segment28_Type NUMBER,
655 X_Segment29_Type NUMBER,
656 X_Segment30_Type NUMBER,
657 X_Attribute1 VARCHAR2,
658 X_Attribute2 VARCHAR2,
659 X_Attribute3 VARCHAR2,
660 X_Attribute4 VARCHAR2,
661 X_Attribute5 VARCHAR2,
662 X_Attribute6 VARCHAR2,
663 X_Attribute7 VARCHAR2,
664 X_Attribute8 VARCHAR2,
665 X_Attribute9 VARCHAR2,
666 X_Attribute10 VARCHAR2,
667 X_Context VARCHAR2,
668 X_Security_Flag VARCHAR2
669 ) IS
670 CURSOR C IS SELECT
671 Budget_Entity_Id ,
672 Name,
673 Ledger_Id,
674 Budget_Password_Required_Flag,
675 Status_Code,
676 Encrypted_Budget_Password,
677 Description,
678 Start_Date,
679 End_Date,
680 Segment1_Type,
681 Segment2_Type,
682 Segment3_Type,
683 Segment4_Type,
684 Segment5_Type,
685 Segment6_Type,
686 Segment7_Type,
687 Segment8_Type,
688 Segment9_Type,
689 Segment10_Type,
690 Segment11_Type,
691 Segment12_Type,
692 Segment13_Type,
693 Segment14_Type,
694 Segment15_Type,
695 Segment16_Type,
696 Segment17_Type,
697 Segment18_Type,
698 Segment19_Type,
699 Segment20_Type,
700 Segment21_Type,
701 Segment22_Type,
702 Segment23_Type,
703 Segment24_Type,
704 Segment25_Type,
705 Segment26_Type,
706 Segment27_Type,
707 Segment28_Type,
708 Segment29_Type,
709 Segment30_Type,
710 Attribute1,
711 Attribute2,
712 Attribute3,
713 Attribute4,
714 Attribute5,
715 Attribute6,
716 Attribute7,
717 Attribute8,
718 Attribute9,
719 Attribute10,
720 Context,
721 Security_Flag
722 FROM Gl_Budget_Entities
723 WHERE ROWID = X_Rowid
724 FOR UPDATE OF Budget_Entity_Id NOWAIT;
725 recinfo C%ROWTYPE;
726
727 BEGIN
728 OPEN C;
729 FETCH C INTO recinfo;
730 IF (C%NOTFOUND) THEN
731 CLOSE C;
732 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
733 app_exception.raise_exception;
734 END IF;
735 CLOSE C;
736
737 IF (
738 (recinfo.Budget_Entity_Id = X_Budget_Entity_Id)
739 AND (recinfo.Name = X_Name)
740 AND (recinfo.Ledger_Id = X_Ledger_Id)
741 AND (recinfo.Budget_Password_Required_Flag = X_Budget_Password_Required)
742 AND (recinfo.Status_Code = X_Status_Code)
743 AND (recinfo.Security_Flag = X_Security_Flag)
744
745 AND ((recinfo.Encrypted_Budget_Password = X_Encrypted_Budget_Password)
746 OR ((recinfo.Encrypted_Budget_Password is null)
747 AND (X_Encrypted_Budget_Password is null)))
748
749 AND ((recinfo.Description = X_Description)
750 OR ((recinfo.Description is null)
751 AND (X_Description is null)))
752
753 AND ((recinfo.Context = X_Context)
754 OR ((recinfo.Context is null)
755 AND (X_Context is null)))
756
757 AND ((recinfo.Start_Date = X_Start_Date)
758 OR ((recinfo.Start_Date is null)
759 AND (X_Start_Date is null)))
760
761 AND ((recinfo.End_Date = X_End_Date)
762 OR ((recinfo.End_Date is null)
763 AND (X_End_Date is null)))
764
765 AND ((recinfo.Segment1_Type = X_Segment1_Type)
766 OR ((recinfo.Segment1_Type is null)
767 AND (X_Segment1_Type is null)))
768
769 AND ((recinfo.Segment2_Type = X_Segment2_Type)
770 OR ((recinfo.Segment2_Type is null)
771 AND (X_Segment2_Type is null)))
772
773 AND ((recinfo.Segment3_Type = X_Segment3_Type)
774 OR ((recinfo.Segment3_Type is null)
775 AND (X_Segment3_Type is null)))
776
777 AND ((recinfo.Segment4_Type = X_Segment4_Type)
778 OR ((recinfo.Segment4_Type is null)
779 AND (X_Segment4_Type is null)))
780
781 AND ((recinfo.Segment5_Type = X_Segment5_Type)
782 OR ((recinfo.Segment5_Type is null)
783 AND (X_Segment5_Type is null)))
784
785 AND ((recinfo.Segment6_Type = X_Segment6_Type)
786 OR ((recinfo.Segment6_Type is null)
787 AND (X_Segment6_Type is null)))
788
789 AND ((recinfo.Segment7_Type = X_Segment7_Type)
790 OR ((recinfo.Segment7_Type is null)
791 AND (X_Segment7_Type is null)))
792
793 AND ((recinfo.Segment8_Type = X_Segment8_Type)
794 OR ((recinfo.Segment8_Type is null)
795 AND (X_Segment8_Type is null)))
796
797 AND ((recinfo.Segment9_Type = X_Segment9_Type)
798 OR ((recinfo.Segment9_Type is null)
799 AND (X_Segment9_Type is null)))
800
801 AND ((recinfo.Segment10_Type = X_Segment10_Type)
802 OR ((recinfo.Segment10_Type is null)
803 AND (X_Segment10_Type is null)))
804
805 AND ((recinfo.Segment11_Type = X_Segment11_Type)
806 OR ((recinfo.Segment11_Type is null)
807 AND (X_Segment11_Type is null)))
808
809 AND ((recinfo.Segment12_Type = X_Segment12_Type)
810 OR ((recinfo.Segment12_Type is null)
811 AND (X_Segment12_Type is null)))
812
813 AND ((recinfo.Segment13_Type = X_Segment13_Type)
814 OR ((recinfo.Segment13_Type is null)
815 AND (X_Segment13_Type is null)))
816
817 AND ((recinfo.Segment14_Type = X_Segment14_Type)
818 OR ((recinfo.Segment14_Type is null)
819 AND (X_Segment14_Type is null)))
820
821 AND ((recinfo.Segment15_Type = X_Segment15_Type)
822 OR ((recinfo.Segment15_Type is null)
823 AND (X_Segment15_Type is null)))
824
825 AND ((recinfo.Segment16_Type = X_Segment16_Type)
826 OR ((recinfo.Segment16_Type is null)
827 AND (X_Segment16_Type is null)))
828
829 AND ((recinfo.Segment17_Type = X_Segment17_Type)
830 OR ((recinfo.Segment17_Type is null)
831 AND (X_Segment17_Type is null)))
832
833 AND ((recinfo.Segment18_Type = X_Segment18_Type)
834 OR ((recinfo.Segment18_Type is null)
835 AND (X_Segment18_Type is null)))
836
837 AND ((recinfo.Segment19_Type = X_Segment19_Type)
838 OR ((recinfo.Segment19_Type is null)
842 OR ((recinfo.Segment20_Type is null)
839 AND (X_Segment19_Type is null)))
840
841 AND ((recinfo.Segment20_Type = X_Segment20_Type)
843 AND (X_Segment20_Type is null)))
844
845 AND ((recinfo.Segment21_Type = X_Segment21_Type)
846 OR ((recinfo.Segment21_Type is null)
847 AND (X_Segment21_Type is null)))
848
849 AND ((recinfo.Segment22_Type = X_Segment22_Type)
850 OR ((recinfo.Segment22_Type is null)
851 AND (X_Segment22_Type is null)))
852
853 AND ((recinfo.Segment23_Type = X_Segment23_Type)
854 OR ((recinfo.Segment23_Type is null)
855 AND (X_Segment23_Type is null)))
856
857 AND ((recinfo.Segment24_Type = X_Segment24_Type)
858 OR ((recinfo.Segment24_Type is null)
859 AND (X_Segment24_Type is null)))
860
861 AND ((recinfo.Segment25_Type = X_Segment25_Type)
862 OR ((recinfo.Segment25_Type is null)
863 AND (X_Segment25_Type is null)))
864
865 AND ((recinfo.Segment26_Type = X_Segment26_Type)
866 OR ((recinfo.Segment26_Type is null)
867 AND (X_Segment26_Type is null)))
868
869 AND ((recinfo.Segment27_Type = X_Segment27_Type)
870 OR ((recinfo.Segment27_Type is null)
871 AND (X_Segment27_Type is null)))
872
873 AND ((recinfo.Segment28_Type = X_Segment28_Type)
874 OR ((recinfo.Segment28_Type is null)
875 AND (X_Segment28_Type is null)))
876
877 AND ((recinfo.Segment29_Type = X_Segment29_Type)
878 OR ((recinfo.Segment29_Type is null)
879 AND (X_Segment29_Type is null)))
880
881 AND ((recinfo.Segment30_Type = X_Segment30_Type)
882 OR ((recinfo.Segment30_Type is null)
883 AND (X_Segment30_Type is null)))
884
885 AND ((recinfo.Attribute1 = X_Attribute1)
886 OR ((recinfo.Attribute1 is null)
887 AND (X_Attribute1 is null)))
888
889 AND ((recinfo.Attribute2 = X_Attribute2)
890 OR ((recinfo.Attribute2 is null)
891 AND (X_Attribute2 is null)))
892
893 AND ((recinfo.Attribute3 = X_Attribute3)
894 OR ((recinfo.Attribute3 is null)
895 AND (X_Attribute3 is null)))
896
897 AND ((recinfo.Attribute4 = X_Attribute4)
898 OR ((recinfo.Attribute4 is null)
899 AND (X_Attribute4 is null)))
900
901 AND ((recinfo.Attribute5 = X_Attribute5)
902 OR ((recinfo.Attribute5 is null)
903 AND (X_Attribute5 is null)))
904
905 AND ((recinfo.Attribute6 = X_Attribute6)
906 OR ((recinfo.Attribute6 is null)
907 AND (X_Attribute6 is null)))
908
909 AND ((recinfo.Attribute7 = X_Attribute7)
910 OR ((recinfo.Attribute7 is null)
911 AND (X_Attribute7 is null)))
912
913 AND ((recinfo.Attribute8 = X_Attribute8)
914 OR ((recinfo.Attribute8 is null)
915 AND (X_Attribute8 is null)))
916
917 AND ((recinfo.Attribute9 = X_Attribute9)
918 OR ((recinfo.Attribute9 is null)
919 AND (X_Attribute9 is null)))
920
921 AND ((recinfo.Attribute10 = X_Attribute10)
922 OR ((recinfo.Attribute10 is null)
923 AND (X_Attribute10 is null)))
924 ) THEN
925 return;
926 ELSE
927 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
928 app_exception.raise_exception;
929 END IF;
930
931 END Lock_Row;
932
933 --** Modified call to Insert_Row
934 PROCEDURE Insert_Org(X_Rowid IN OUT NOCOPY VARCHAR2,
935 X_Budget_Entity_Id IN OUT NOCOPY NUMBER,
936 X_Name VARCHAR2,
937 X_Ledger_Id NUMBER,
938 X_Last_Update_Date DATE,
939 X_Last_Updated_By NUMBER,
940 X_Budget_Password_Required VARCHAR2,
941 X_Status_Code VARCHAR2,
942 X_Creation_Date DATE,
943 X_Created_By NUMBER,
944 X_Last_Update_Login NUMBER,
945 X_Encrypted_Budget_Password VARCHAR2,
946 X_Description VARCHAR2,
947 X_Start_Date DATE,
948 X_End_Date DATE,
949 X_Segment1_Type NUMBER,
950 X_Segment2_Type NUMBER,
951 X_Segment3_Type NUMBER,
952 X_Segment4_Type NUMBER,
953 X_Segment5_Type NUMBER,
954 X_Segment6_Type NUMBER,
955 X_Segment7_Type NUMBER,
956 X_Segment8_Type NUMBER,
957 X_Segment9_Type NUMBER,
958 X_Segment10_Type NUMBER,
959 X_Segment11_Type NUMBER,
960 X_Segment12_Type NUMBER,
961 X_Segment13_Type NUMBER,
962 X_Segment14_Type NUMBER,
963 X_Segment15_Type NUMBER,
967 X_Segment19_Type NUMBER,
964 X_Segment16_Type NUMBER,
965 X_Segment17_Type NUMBER,
966 X_Segment18_Type NUMBER,
968 X_Segment20_Type NUMBER,
969 X_Segment21_Type NUMBER,
970 X_Segment22_Type NUMBER,
971 X_Segment23_Type NUMBER,
972 X_Segment24_Type NUMBER,
973 X_Segment25_Type NUMBER,
974 X_Segment26_Type NUMBER,
975 X_Segment27_Type NUMBER,
976 X_Segment28_Type NUMBER,
977 X_Segment29_Type NUMBER,
978 X_Segment30_Type NUMBER,
979 X_Attribute1 VARCHAR2,
980 X_Attribute2 VARCHAR2,
981 X_Attribute3 VARCHAR2,
982 X_Attribute4 VARCHAR2,
983 X_Attribute5 VARCHAR2,
984 X_Attribute6 VARCHAR2,
985 X_Attribute7 VARCHAR2,
986 X_Attribute8 VARCHAR2,
987 X_Attribute9 VARCHAR2,
988 X_Attribute10 VARCHAR2,
989 X_Context VARCHAR2
990 ) IS
991
992 L_All_Org BOOLEAN;
993 L_Coa_Id NUMBER(15);
994 L_Translated_All VARCHAR2(80);
995 L_Message VARCHAR2(240);
996
997 BEGIN
998
999 SELECT chart_of_accounts_id
1000 INTO L_Coa_Id
1001 FROM GL_LEDGERS
1002 WHERE ledger_id = X_Ledger_Id;
1003
1004 SELECT meaning
1005 INTO L_Translated_All
1006 FROM GL_LOOKUPS
1007 WHERE lookup_type = 'LITERAL'
1008 AND lookup_code = 'ALL';
1009
1010 IF (upper(X_Name) = L_Translated_All) THEN
1011 L_All_Org := TRUE;
1012 ELSE
1013 L_All_Org := FALSE;
1014 END IF;
1015
1016 GL_BUDGET_ENTITIES_PKG.Insert_Row(
1017 X_Rowid,
1018 X_Budget_Entity_Id,
1019 X_Name,
1020 X_Ledger_Id,
1021 X_Last_Update_Date,
1022 X_Last_Updated_By,
1023 X_Budget_Password_Required,
1024 X_Status_Code,
1025 X_Creation_Date,
1026 X_Created_By,
1027 X_Last_Update_Login,
1028 X_Encrypted_Budget_Password,
1029 X_Description,
1030 X_Start_Date,
1031 X_End_Date,
1032 X_Segment1_Type,
1033 X_Segment2_Type,
1034 X_Segment3_Type,
1035 X_Segment4_Type,
1036 X_Segment5_Type,
1037 X_Segment6_Type,
1038 X_Segment7_Type,
1039 X_Segment8_Type,
1040 X_Segment9_Type,
1041 X_Segment10_Type,
1042 X_Segment11_Type,
1043 X_Segment12_Type,
1044 X_Segment13_Type,
1045 X_Segment14_Type,
1046 X_Segment15_Type,
1047 X_Segment16_Type,
1048 X_Segment17_Type,
1049 X_Segment18_Type,
1050 X_Segment19_Type,
1051 X_Segment20_Type,
1052 X_Segment21_Type,
1053 X_Segment22_Type,
1054 X_Segment23_Type,
1055 X_Segment24_Type,
1056 X_Segment25_Type,
1057 X_Segment26_Type,
1058 X_Segment27_Type,
1059 X_Segment28_Type,
1060 X_Segment29_Type,
1061 X_Segment30_Type,
1062 X_Attribute1,
1063 X_Attribute2,
1064 X_Attribute3,
1065 X_Attribute4,
1066 X_Attribute5,
1067 X_Attribute6,
1068 X_Attribute7,
1069 X_Attribute8,
1070 X_Attribute9,
1071 X_Attribute10,
1072 X_Context,
1073 L_All_Org,
1074 L_Coa_Id,
1075 'N');
1076 EXCEPTION
1077 WHEN app_exceptions.application_exception THEN
1078 RAISE;
1079 WHEN OTHERS THEN
1080 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1081 fnd_message.set_token('PROCEDURE',
1082 'GL_BUDGET_ENTITIES_PKG.Insert_Org');
1083 RAISE;
1084 END Insert_Org;
1085
1086
1087 FUNCTION Submit_Assign_Ranges_Request(
1088 X_Ledger_id IN VARCHAR2,
1089 X_Orgid IN VARCHAR2)
1090 return NUMBER IS
1091
1092 L_request_id NUMBER;
1093 request_failed EXCEPTION;
1094
1095 BEGIN
1096
1097 L_request_id := 0;
1098
1099 L_request_id := FND_REQUEST.SUBMIT_REQUEST(
1100 'SQLGL', 'GLBAAR', NULL, NULL, FALSE,
1101 X_Ledger_id, X_Orgid, chr(0),
1102 '', '', '', '', '', '', '', '', '', '',
1103 '', '', '', '', '', '', '', '', '', '',
1104 '', '', '', '', '', '', '', '', '', '',
1105 '', '', '', '', '', '', '', '', '', '',
1106 '', '', '', '', '', '', '', '', '', '',
1107 '', '', '', '', '', '', '', '', '', '',
1108 '', '', '', '', '', '', '', '', '', '',
1109 '', '', '', '', '', '', '', '', '', '',
1110 '', '', '', '', '', '', '', '', '', '',
1111 '', '', '', '', '', '', '');
1112
1113 IF (L_request_id = 0) THEN
1114 RAISE request_failed;
1115 END IF;
1116
1117 RETURN (L_request_id);
1118
1119 EXCEPTION
1120 WHEN request_failed THEN
1121 RAISE;
1122 WHEN OTHERS THEN
1123 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1124 fnd_message.set_token('PROCEDURE',
1125 'GL_BUDGET_ENTITIES_PKG.Insert_Org');
1126 RAISE;
1127
1128 END Submit_Assign_Ranges_Request;
1129
1130
1131 PROCEDURE Set_BC_Timestamp(X_Ledger_Id NUMBER) IS
1132
1133 L_User_Id NUMBER;
1134 L_Login_Id NUMBER;
1135 L_Coa_Id NUMBER;
1136
1137 BEGIN
1138
1139 L_User_Id := FND_GLOBAL.user_id;
1140 L_Login_Id := FND_GLOBAL.login_id;
1141
1142 SELECT chart_of_accounts_id
1143 INTO L_Coa_Id
1144 FROM gl_ledgers
1145 WHERE ledger_id = X_Ledger_Id;
1146
1147 GL_BC_EVENT_TSTAMPS_PKG.Set_Event_Timestamp(L_Coa_Id,
1148 'B',
1149 L_User_Id,
1150 L_Login_Id);
1151
1152 EXCEPTION
1153 WHEN app_exceptions.application_exception THEN
1154 RAISE;
1155 WHEN OTHERS THEN
1156 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1157 fnd_message.set_token('PROCEDURE',
1158 'GL_BUDGET_ORG_PKG.Set_BC_Timestamp');
1159 RAISE;
1160 END Set_BC_Timestamp;
1161
1162 END gl_budget_entities_pkg;