[Home] [Help]
PACKAGE BODY: APPS.GL_BUD_ASSIGN_RANGE_PKG
Source
1 PACKAGE BODY gl_bud_assign_range_pkg AS
2 /* $Header: glibdarb.pls 120.12.12010000.1 2008/07/28 13:23:06 appldev ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9
10 FUNCTION is_funds_check_not_none(
11 x_ledger_id NUMBER ) RETURN BOOLEAN IS
12
13 CURSOR c_no_fc IS
14 SELECT 'found'
15 FROM GL_BUDGET_ASSIGNMENT_RANGES bar
16 WHERE bar.ledger_id = x_ledger_id
17 AND EXISTS (SELECT 'found'
18 FROM GL_BUDORG_BC_OPTIONS bco
19 WHERE bar.range_id = bco.range_id);
20
21 dummy VARCHAR2(100);
22
23 BEGIN
24
25 OPEN c_no_fc;
26 FETCH c_no_fc INTO dummy;
27
28 IF c_no_fc%FOUND THEN
29 CLOSE c_no_fc;
30 RETURN( TRUE );
31 ELSE
32 CLOSE c_no_fc;
33 RETURN( FALSE );
34 END IF;
35
36 CLOSE c_no_fc;
37
38 EXCEPTION
39 WHEN app_exceptions.application_exception THEN
40 RAISE;
41 WHEN OTHERS THEN
42 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
43 fnd_message.set_token('PROCEDURE',
44 'GL_BUD_ASSIGN_RANGE_PKG.is_funds_check_not_none');
45 RAISE;
46
47 END is_funds_check_not_none;
48
49
50 PROCEDURE check_unique(org_id NUMBER, seq_num NUMBER,
51 row_id VARCHAR2) IS
52 CURSOR chk_duplicates is
53 SELECT 'Duplicate'
54 FROM GL_BUDGET_ASSIGNMENT_RANGES bar
55 WHERE bar.budget_entity_id = org_id
56 AND bar.sequence_number = seq_num
57 AND ( row_id is null
58 OR bar.rowid <> row_id);
59 dummy VARCHAR2(100);
60 BEGIN
61 OPEN chk_duplicates;
62 FETCH chk_duplicates INTO dummy;
63
64 IF chk_duplicates%FOUND THEN
65 CLOSE chk_duplicates;
66 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_BUD_RANGE_SEQ');
67 app_exception.raise_exception;
68 END IF;
69
70 CLOSE chk_duplicates;
71
72 EXCEPTION
73 WHEN app_exceptions.application_exception THEN
74 RAISE;
75 WHEN OTHERS THEN
76 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
77 fnd_message.set_token('PROCEDURE',
78 'GL_BUD_ASSIGN_RANGE_PKG.check_unique');
79 RAISE;
80 END check_unique;
81
82
83 PROCEDURE lock_range(x_range_id NUMBER) IS
84 CURSOR lock_rng is
85 SELECT 'Range locked'
86 FROM GL_BUDGET_ASSIGNMENT_RANGES bar
87 WHERE bar.range_id = x_range_id
88 FOR UPDATE OF status;
89 dummy VARCHAR2(100);
90 BEGIN
91 OPEN lock_rng;
92 FETCH lock_rng INTO dummy;
93
94 IF NOT lock_rng%FOUND THEN
95 CLOSE lock_rng;
96 fnd_message.set_name('SQLGL', 'GL_BUDORG_CANNOT_LOCK_RANGE');
97 app_exception.raise_exception;
98 END IF;
99
100 CLOSE lock_rng;
101
102 EXCEPTION
103 WHEN app_exceptions.application_exception THEN
104 RAISE;
105 WHEN OTHERS THEN
106 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
107 fnd_message.set_token('PROCEDURE',
108 'GL_BUD_ASSIGN_RANGE_PKG.lock_range');
109 RAISE;
110 END lock_range;
111
112
113 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
114 X_Budget_Entity_Id NUMBER,
115 X_Ledger_Id NUMBER,
116 X_Currency_Code VARCHAR2,
117 X_Entry_Code VARCHAR2,
118 X_Range_Id IN OUT NOCOPY NUMBER,
119 X_Status VARCHAR2,
120 X_Last_Update_Date DATE,
121 X_Created_By NUMBER,
122 X_Creation_Date DATE,
123 X_Last_Updated_By NUMBER,
124 X_Last_Update_Login NUMBER,
125 X_Sequence_Number NUMBER,
126 X_Segment1_Low VARCHAR2,
127 X_Segment1_High VARCHAR2,
128 X_Segment2_Low VARCHAR2,
129 X_Segment2_High VARCHAR2,
130 X_Segment3_Low VARCHAR2,
131 X_Segment3_High VARCHAR2,
132 X_Segment4_Low VARCHAR2,
133 X_Segment4_High VARCHAR2,
134 X_Segment5_Low VARCHAR2,
135 X_Segment5_High VARCHAR2,
136 X_Segment6_Low VARCHAR2,
137 X_Segment6_High VARCHAR2,
138 X_Segment7_Low VARCHAR2,
139 X_Segment7_High VARCHAR2,
140 X_Segment8_Low VARCHAR2,
141 X_Segment8_High VARCHAR2,
142 X_Segment9_Low VARCHAR2,
143 X_Segment9_High VARCHAR2,
144 X_Segment10_Low VARCHAR2,
145 X_Segment10_High VARCHAR2,
146 X_Segment11_Low VARCHAR2,
147 X_Segment11_High VARCHAR2,
148 X_Segment12_Low VARCHAR2,
149 X_Segment12_High VARCHAR2,
150 X_Segment13_Low VARCHAR2,
151 X_Segment13_High VARCHAR2,
152 X_Segment14_Low VARCHAR2,
153 X_Segment14_High VARCHAR2,
154 X_Segment15_Low VARCHAR2,
155 X_Segment15_High VARCHAR2,
156 X_Segment16_Low VARCHAR2,
157 X_Segment16_High VARCHAR2,
158 X_Segment17_Low VARCHAR2,
159 X_Segment17_High VARCHAR2,
160 X_Segment18_Low VARCHAR2,
161 X_Segment18_High VARCHAR2,
162 X_Segment19_Low VARCHAR2,
163 X_Segment19_High VARCHAR2,
164 X_Segment20_Low VARCHAR2,
165 X_Segment20_High VARCHAR2,
166 X_Segment21_Low VARCHAR2,
167 X_Segment21_High VARCHAR2,
168 X_Segment22_Low VARCHAR2,
169 X_Segment22_High VARCHAR2,
170 X_Segment23_Low VARCHAR2,
171 X_Segment23_High VARCHAR2,
172 X_Segment24_Low VARCHAR2,
173 X_Segment24_High VARCHAR2,
174 X_Segment25_Low VARCHAR2,
175 X_Segment25_High VARCHAR2,
176 X_Segment26_Low VARCHAR2,
177 X_Segment26_High VARCHAR2,
178 X_Segment27_Low VARCHAR2,
179 X_Segment27_High VARCHAR2,
180 X_Segment28_Low VARCHAR2,
181 X_Segment28_High VARCHAR2,
182 X_Segment29_Low VARCHAR2,
183 X_Segment29_High VARCHAR2,
184 X_Segment30_Low VARCHAR2,
185 X_Segment30_High VARCHAR2,
186 X_Context VARCHAR2,
187 X_Attribute1 VARCHAR2,
188 X_Attribute2 VARCHAR2,
189 X_Attribute3 VARCHAR2,
190 X_Attribute4 VARCHAR2,
191 X_Attribute5 VARCHAR2,
192 X_Attribute6 VARCHAR2,
193 X_Attribute7 VARCHAR2,
194 X_Attribute8 VARCHAR2,
195 X_Attribute9 VARCHAR2,
196 X_Attribute10 VARCHAR2,
197 X_Attribute11 VARCHAR2,
198 X_Attribute12 VARCHAR2,
199 X_Attribute13 VARCHAR2,
200 X_Attribute14 VARCHAR2,
201 X_Attribute15 VARCHAR2,
202 X_Chart_Of_Accounts_Id NUMBER
203 ) IS
204
205 CURSOR get_new_id IS
206 SELECT gl_budget_assignment_ranges_s.NEXTVAL
207 FROM dual;
208
209
210 CURSOR C IS SELECT rowid FROM GL_BUDGET_ASSIGNMENT_RANGES
211
212 WHERE range_id = X_Range_Id;
213
214 CURSOR check_overlaps IS
215 SELECT 'Overlapping'
216 FROM DUAL
217 WHERE EXISTS
218 (SELECT 'X'
219 FROM GL_BUDGET_ASSIGNMENT_RANGES
220 WHERE LEDGER_ID = X_LEDGER_ID
221 AND CURRENCY_CODE = X_CURRENCY_CODE
222 AND (NVL(SEGMENT30_LOW,'X') <= NVL(X_SEGMENT30_HIGH,'X')
223 AND NVL(SEGMENT30_HIGH,'X') >= NVL(X_SEGMENT30_LOW,'X')
224 AND NVL(SEGMENT29_LOW,'X') <= NVL(X_SEGMENT29_HIGH,'X')
225 AND NVL(SEGMENT29_HIGH,'X') >= NVL(X_SEGMENT29_LOW,'X')
226 AND NVL(SEGMENT28_LOW,'X') <= NVL(X_SEGMENT28_HIGH,'X')
227 AND NVL(SEGMENT28_HIGH,'X') >= NVL(X_SEGMENT28_LOW,'X')
228 AND NVL(SEGMENT27_LOW,'X') <= NVL(X_SEGMENT27_HIGH,'X')
229 AND NVL(SEGMENT27_HIGH,'X') >= NVL(X_SEGMENT27_LOW,'X')
230 AND NVL(SEGMENT26_LOW,'X') <= NVL(X_SEGMENT26_HIGH,'X')
231 AND NVL(SEGMENT26_HIGH,'X') >= NVL(X_SEGMENT26_LOW,'X')
232 AND NVL(SEGMENT25_LOW,'X') <= NVL(X_SEGMENT25_HIGH,'X')
233 AND NVL(SEGMENT25_HIGH,'X') >= NVL(X_SEGMENT25_LOW,'X')
234 AND NVL(SEGMENT24_LOW,'X') <= NVL(X_SEGMENT24_HIGH,'X')
235 AND NVL(SEGMENT24_HIGH,'X') >= NVL(X_SEGMENT24_LOW,'X')
236 AND NVL(SEGMENT23_LOW,'X') <= NVL(X_SEGMENT23_HIGH,'X')
237 AND NVL(SEGMENT23_HIGH,'X') >= NVL(X_SEGMENT23_LOW,'X')
238 AND NVL(SEGMENT22_LOW,'X') <= NVL(X_SEGMENT22_HIGH,'X')
239 AND NVL(SEGMENT22_HIGH,'X') >= NVL(X_SEGMENT22_LOW,'X')
240 AND NVL(SEGMENT21_LOW,'X') <= NVL(X_SEGMENT21_HIGH,'X')
241 AND NVL(SEGMENT21_HIGH,'X') >= NVL(X_SEGMENT21_LOW,'X')
242 AND NVL(SEGMENT20_LOW,'X') <= NVL(X_SEGMENT20_HIGH,'X')
243 AND NVL(SEGMENT20_HIGH,'X') >= NVL(X_SEGMENT20_LOW,'X')
244 AND NVL(SEGMENT19_LOW,'X') <= NVL(X_SEGMENT19_HIGH,'X')
245 AND NVL(SEGMENT19_HIGH,'X') >= NVL(X_SEGMENT19_LOW,'X')
246 AND NVL(SEGMENT18_LOW,'X') <= NVL(X_SEGMENT18_HIGH,'X')
247 AND NVL(SEGMENT18_HIGH,'X') >= NVL(X_SEGMENT18_LOW,'X')
248 AND NVL(SEGMENT17_LOW,'X') <= NVL(X_SEGMENT17_HIGH,'X')
249 AND NVL(SEGMENT17_HIGH,'X') >= NVL(X_SEGMENT17_LOW,'X')
250 AND NVL(SEGMENT16_LOW,'X') <= NVL(X_SEGMENT16_HIGH,'X')
251 AND NVL(SEGMENT16_HIGH,'X') >= NVL(X_SEGMENT16_LOW,'X')
252 AND NVL(SEGMENT15_LOW,'X') <= NVL(X_SEGMENT15_HIGH,'X')
253 AND NVL(SEGMENT15_HIGH,'X') >= NVL(X_SEGMENT15_LOW,'X'))
254 AND NVL(SEGMENT14_LOW,'X') <= NVL(X_SEGMENT14_HIGH,'X')
255 AND NVL(SEGMENT14_HIGH,'X') >= NVL(X_SEGMENT14_LOW,'X')
256 AND NVL(SEGMENT13_LOW,'X') <= NVL(X_SEGMENT13_HIGH,'X')
257 AND NVL(SEGMENT13_HIGH,'X') >= NVL(X_SEGMENT13_LOW,'X')
258 AND NVL(SEGMENT12_LOW,'X') <= NVL(X_SEGMENT12_HIGH,'X')
259 AND NVL(SEGMENT12_HIGH,'X') >= NVL(X_SEGMENT12_LOW,'X')
260 AND NVL(SEGMENT11_LOW,'X') <= NVL(X_SEGMENT11_HIGH,'X')
261 AND NVL(SEGMENT11_HIGH,'X') >= NVL(X_SEGMENT11_LOW,'X')
262 AND NVL(SEGMENT10_LOW,'X') <= NVL(X_SEGMENT10_HIGH,'X')
263 AND NVL(SEGMENT10_HIGH,'X') >= NVL(X_SEGMENT10_LOW,'X')
264 AND NVL(SEGMENT9_LOW,'X') <= NVL(X_SEGMENT9_HIGH,'X')
265 AND NVL(SEGMENT9_HIGH,'X') >= NVL(X_SEGMENT9_LOW,'X')
266 AND NVL(SEGMENT8_LOW,'X') <= NVL(X_SEGMENT8_HIGH,'X')
267 AND NVL(SEGMENT8_HIGH,'X') >= NVL(X_SEGMENT8_LOW,'X')
268 AND NVL(SEGMENT7_LOW,'X') <= NVL(X_SEGMENT7_HIGH,'X')
269 AND NVL(SEGMENT7_HIGH,'X') >= NVL(X_SEGMENT7_LOW,'X')
270 AND NVL(SEGMENT6_LOW,'X') <= NVL(X_SEGMENT6_HIGH,'X')
271 AND NVL(SEGMENT6_HIGH,'X') >= NVL(X_SEGMENT6_LOW,'X')
272 AND NVL(SEGMENT5_LOW,'X') <= NVL(X_SEGMENT5_HIGH,'X')
273 AND NVL(SEGMENT5_HIGH,'X') >= NVL(X_SEGMENT5_LOW,'X')
274 AND NVL(SEGMENT4_LOW,'X') <= NVL(X_SEGMENT4_HIGH,'X')
275 AND NVL(SEGMENT4_HIGH,'X') >= NVL(X_SEGMENT4_LOW,'X')
276 AND NVL(SEGMENT3_LOW,'X') <= NVL(X_SEGMENT3_HIGH,'X')
277 AND NVL(SEGMENT3_HIGH,'X') >= NVL(X_SEGMENT3_LOW,'X')
278 AND NVL(SEGMENT2_LOW,'X') <= NVL(X_SEGMENT2_HIGH,'X')
279 AND NVL(SEGMENT2_HIGH,'X') >= NVL(X_SEGMENT2_LOW,'X')
280 AND NVL(SEGMENT1_LOW,'X') <= NVL(X_SEGMENT1_HIGH,'X')
281 AND NVL(SEGMENT1_HIGH,'X') >= NVL(X_SEGMENT1_LOW,'X'));
282
283 dummy VARCHAR2(100);
284 L_Status VARCHAR2(1);
285
286 BEGIN
287 -- get new range_id for the range
288 -- Changed functionality to retrieve id from sequence only if
289 -- routine is not called from the iSpeed API, since the API
290 -- retrieves and passes in the id.
291
292 IF (X_Status = 'ISPEED') THEN
293 L_Status := 'A';
294 ELSE
295 OPEN get_new_id;
296 FETCH get_new_id INTO X_Range_Id;
297
298 IF get_new_id%FOUND THEN
299 CLOSE get_new_id;
300 ELSE
301 CLOSE get_new_id;
302 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
303 fnd_message.set_token('SEQUENCE', 'GL_BUDGET_ASSIGNMENT_RANGES_S');
304 app_exception.raise_exception;
305 END IF;
306 L_Status := X_Status;
307 END IF;
308
309 -- Make sure the budget organization isn't deleted as the range
310 -- is being inserted
311 gl_budget_entities_pkg.lock_organization(X_BUDGET_ENTITY_ID);
312
313 -- Lock the timestamp to prevent coordination problems
314 gl_bc_event_tstamps_pkg.lock_event_timestamp(X_Chart_Of_Accounts_Id, 'B');
315
316 -- Check for overlapping ranges
317 OPEN check_overlaps;
318 FETCH check_overlaps INTO dummy;
319
320 IF check_overlaps%FOUND THEN
321 CLOSE check_overlaps;
322 fnd_message.set_name('SQLGL', 'GL_BUDORG_RANGES_OVERLAP');
323 app_exception.raise_exception;
324 ELSE
325 CLOSE check_overlaps;
326 END IF;
327
328 INSERT INTO GL_BUDGET_ASSIGNMENT_RANGES(
329 budget_entity_id,
330 ledger_id,
331 currency_code,
332 entry_code,
333 range_id,
334 status,
335 last_update_date,
336 created_by,
337 creation_date,
338 last_updated_by,
339 last_update_login,
340 sequence_number,
341 segment1_low,
342 segment1_high,
343 segment2_low,
344 segment2_high,
345 segment3_low,
346 segment3_high,
347 segment4_low,
348 segment4_high,
349 segment5_low,
350 segment5_high,
351 segment6_low,
352 segment6_high,
353 segment7_low,
354 segment7_high,
355 segment8_low,
356 segment8_high,
357 segment9_low,
358 segment9_high,
359 segment10_low,
360 segment10_high,
361 segment11_low,
362 segment11_high,
363 segment12_low,
364 segment12_high,
365 segment13_low,
366 segment13_high,
367 segment14_low,
368 segment14_high,
369 segment15_low,
370 segment15_high,
371 segment16_low,
372 segment16_high,
373 segment17_low,
374 segment17_high,
375 segment18_low,
376 segment18_high,
377 segment19_low,
378 segment19_high,
379 segment20_low,
380 segment20_high,
381 segment21_low,
382 segment21_high,
383 segment22_low,
384 segment22_high,
385 segment23_low,
386 segment23_high,
387 segment24_low,
388 segment24_high,
389 segment25_low,
390 segment25_high,
391 segment26_low,
392 segment26_high,
393 segment27_low,
394 segment27_high,
395 segment28_low,
396 segment28_high,
397 segment29_low,
398 segment29_high,
399 segment30_low,
400 segment30_high,
401 context,
402 attribute1,
403 attribute2,
404 attribute3,
405 attribute4,
406 attribute5,
407 attribute6,
408 attribute7,
409 attribute8,
410 attribute9,
411 attribute10,
412 attribute11,
413 attribute12,
414 attribute13,
415 attribute14,
416 attribute15
417 ) VALUES (
418 X_Budget_Entity_Id,
419 X_Ledger_Id,
420 X_Currency_Code,
421 X_Entry_Code,
422 X_Range_Id,
423 L_Status,
424 X_Last_Update_Date,
425 X_Created_By,
426 X_Creation_Date,
427 X_Last_Updated_By,
428 X_Last_Update_Login,
429 X_Sequence_Number,
430 X_Segment1_Low,
431 X_Segment1_High,
432 X_Segment2_Low,
433 X_Segment2_High,
434 X_Segment3_Low,
435 X_Segment3_High,
436 X_Segment4_Low,
437 X_Segment4_High,
438 X_Segment5_Low,
439 X_Segment5_High,
440 X_Segment6_Low,
441 X_Segment6_High,
442 X_Segment7_Low,
443 X_Segment7_High,
444 X_Segment8_Low,
445 X_Segment8_High,
446 X_Segment9_Low,
447 X_Segment9_High,
448 X_Segment10_Low,
449 X_Segment10_High,
450 X_Segment11_Low,
451 X_Segment11_High,
452 X_Segment12_Low,
453 X_Segment12_High,
454 X_Segment13_Low,
455 X_Segment13_High,
456 X_Segment14_Low,
457 X_Segment14_High,
458 X_Segment15_Low,
459 X_Segment15_High,
460 X_Segment16_Low,
461 X_Segment16_High,
462 X_Segment17_Low,
463 X_Segment17_High,
464 X_Segment18_Low,
465 X_Segment18_High,
466 X_Segment19_Low,
467 X_Segment19_High,
468 X_Segment20_Low,
469 X_Segment20_High,
470 X_Segment21_Low,
471 X_Segment21_High,
472 X_Segment22_Low,
473 X_Segment22_High,
474 X_Segment23_Low,
475 X_Segment23_High,
476 X_Segment24_Low,
477 X_Segment24_High,
478 X_Segment25_Low,
479 X_Segment25_High,
480 X_Segment26_Low,
481 X_Segment26_High,
482 X_Segment27_Low,
483 X_Segment27_High,
484 X_Segment28_Low,
485 X_Segment28_High,
486 X_Segment29_Low,
487 X_Segment29_High,
488 X_Segment30_Low,
489 X_Segment30_High,
490 X_Context,
491 X_Attribute1,
492 X_Attribute2,
493 X_Attribute3,
494 X_Attribute4,
495 X_Attribute5,
496 X_Attribute6,
497 X_Attribute7,
498 X_Attribute8,
499 X_Attribute9,
500 X_Attribute10,
501 X_Attribute11,
502 X_Attribute12,
503 X_Attribute13,
504 X_Attribute14,
505 X_Attribute15
506 );
507
508 OPEN C;
509 FETCH C INTO X_Rowid;
510 if (C%NOTFOUND) then
511 CLOSE C;
512 RAISE NO_DATA_FOUND;
513 end if;
514 CLOSE C;
515 END Insert_Row;
516
517 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
518
519 X_Budget_Entity_Id NUMBER,
520 X_Ledger_Id NUMBER,
521 X_Currency_Code VARCHAR2,
522 X_Entry_Code VARCHAR2,
523 X_Range_Id NUMBER,
524 X_Status VARCHAR2,
525 X_Sequence_Number NUMBER,
526 X_Segment1_Low VARCHAR2,
527 X_Segment1_High VARCHAR2,
528 X_Segment2_Low VARCHAR2,
529 X_Segment2_High VARCHAR2,
530 X_Segment3_Low VARCHAR2,
531 X_Segment3_High VARCHAR2,
532 X_Segment4_Low VARCHAR2,
533 X_Segment4_High VARCHAR2,
534 X_Segment5_Low VARCHAR2,
535 X_Segment5_High VARCHAR2,
536 X_Segment6_Low VARCHAR2,
537 X_Segment6_High VARCHAR2,
538 X_Segment7_Low VARCHAR2,
539 X_Segment7_High VARCHAR2,
540 X_Segment8_Low VARCHAR2,
541 X_Segment8_High VARCHAR2,
542 X_Segment9_Low VARCHAR2,
543 X_Segment9_High VARCHAR2,
544 X_Segment10_Low VARCHAR2,
545 X_Segment10_High VARCHAR2,
546 X_Segment11_Low VARCHAR2,
547 X_Segment11_High VARCHAR2,
548 X_Segment12_Low VARCHAR2,
549 X_Segment12_High VARCHAR2,
550 X_Segment13_Low VARCHAR2,
551 X_Segment13_High VARCHAR2,
552 X_Segment14_Low VARCHAR2,
553 X_Segment14_High VARCHAR2,
554 X_Segment15_Low VARCHAR2,
555 X_Segment15_High VARCHAR2,
556 X_Segment16_Low VARCHAR2,
557 X_Segment16_High VARCHAR2,
558 X_Segment17_Low VARCHAR2,
559 X_Segment17_High VARCHAR2,
560 X_Segment18_Low VARCHAR2,
561 X_Segment18_High VARCHAR2,
562 X_Segment19_Low VARCHAR2,
563 X_Segment19_High VARCHAR2,
564 X_Segment20_Low VARCHAR2,
565 X_Segment20_High VARCHAR2,
566 X_Segment21_Low VARCHAR2,
567 X_Segment21_High VARCHAR2,
568 X_Segment22_Low VARCHAR2,
569 X_Segment22_High VARCHAR2,
570 X_Segment23_Low VARCHAR2,
571 X_Segment23_High VARCHAR2,
572 X_Segment24_Low VARCHAR2,
573 X_Segment24_High VARCHAR2,
574 X_Segment25_Low VARCHAR2,
575 X_Segment25_High VARCHAR2,
576 X_Segment26_Low VARCHAR2,
577 X_Segment26_High VARCHAR2,
578 X_Segment27_Low VARCHAR2,
579 X_Segment27_High VARCHAR2,
580 X_Segment28_Low VARCHAR2,
581 X_Segment28_High VARCHAR2,
582 X_Segment29_Low VARCHAR2,
583 X_Segment29_High VARCHAR2,
584 X_Segment30_Low VARCHAR2,
585 X_Segment30_High VARCHAR2,
586 X_Context VARCHAR2,
587 X_Attribute1 VARCHAR2,
588 X_Attribute2 VARCHAR2,
589 X_Attribute3 VARCHAR2,
590 X_Attribute4 VARCHAR2,
591 X_Attribute5 VARCHAR2,
592 X_Attribute6 VARCHAR2,
593 X_Attribute7 VARCHAR2,
594 X_Attribute8 VARCHAR2,
595 X_Attribute9 VARCHAR2,
596 X_Attribute10 VARCHAR2,
597 X_Attribute11 VARCHAR2,
598 X_Attribute12 VARCHAR2,
599 X_Attribute13 VARCHAR2,
600 X_Attribute14 VARCHAR2,
601 X_Attribute15 VARCHAR2
602 ) IS
603 CURSOR C IS
604 SELECT *
605 FROM GL_BUDGET_ASSIGNMENT_RANGES
606 WHERE rowid = X_Rowid
607 FOR UPDATE of Range_Id NOWAIT;
608 Recinfo C%ROWTYPE;
609 BEGIN
610 OPEN C;
611 FETCH C INTO Recinfo;
612 if (C%NOTFOUND) then
613 CLOSE C;
614 RAISE NO_DATA_FOUND;
615 end if;
616 CLOSE C;
617 if (
618 ( (Recinfo.budget_entity_id = X_Budget_Entity_Id)
619 OR ( (Recinfo.budget_entity_id IS NULL)
620 AND (X_Budget_Entity_Id IS NULL)))
621 AND ( (Recinfo.ledger_id = X_Ledger_Id)
622 OR ( (Recinfo.ledger_id IS NULL)
623 AND (X_Ledger_Id IS NULL)))
624 AND ( (Recinfo.currency_code = X_Currency_Code)
625 OR ( (Recinfo.currency_code IS NULL)
626 AND (X_Currency_Code IS NULL)))
627 AND ( (Recinfo.entry_code = X_Entry_Code)
628 OR ( (Recinfo.entry_code IS NULL)
629 AND (X_Entry_Code IS NULL)))
630 AND ( (Recinfo.range_id = X_Range_Id)
631 OR ( (Recinfo.range_id IS NULL)
632 AND (X_Range_Id IS NULL)))
633 AND ( (Recinfo.status = X_Status)
634 OR ( (Recinfo.status IS NULL)
635 AND (X_Status IS NULL)))
636 AND ( (Recinfo.sequence_number = X_Sequence_Number)
637 OR ( (Recinfo.sequence_number IS NULL)
638 AND (X_Sequence_Number IS NULL)))
639 AND ( (Recinfo.segment1_low = X_Segment1_Low)
640 OR ( (Recinfo.segment1_low IS NULL)
641 AND (X_Segment1_Low IS NULL)))
642 AND ( (Recinfo.segment1_high = X_Segment1_High)
643 OR ( (Recinfo.segment1_high IS NULL)
644 AND (X_Segment1_High IS NULL)))
645 AND ( (Recinfo.segment2_low = X_Segment2_Low)
646 OR ( (Recinfo.segment2_low IS NULL)
647 AND (X_Segment2_Low IS NULL)))
648 AND ( (Recinfo.segment2_high = X_Segment2_High)
649 OR ( (Recinfo.segment2_high IS NULL)
650 AND (X_Segment2_High IS NULL)))
651 AND ( (Recinfo.segment3_low = X_Segment3_Low)
652 OR ( (Recinfo.segment3_low IS NULL)
653 AND (X_Segment3_Low IS NULL)))
654 AND ( (Recinfo.segment3_high = X_Segment3_High)
655 OR ( (Recinfo.segment3_high IS NULL)
656 AND (X_Segment3_High IS NULL)))
657 AND ( (Recinfo.segment4_low = X_Segment4_Low)
658 OR ( (Recinfo.segment4_low IS NULL)
659 AND (X_Segment4_Low IS NULL)))
660 AND ( (Recinfo.segment4_high = X_Segment4_High)
661 OR ( (Recinfo.segment4_high IS NULL)
662 AND (X_Segment4_High IS NULL)))
663 AND ( (Recinfo.segment5_low = X_Segment5_Low)
664 OR ( (Recinfo.segment5_low IS NULL)
665 AND (X_Segment5_Low IS NULL)))
666 AND ( (Recinfo.segment5_high = X_Segment5_High)
667 OR ( (Recinfo.segment5_high IS NULL)
668 AND (X_Segment5_High IS NULL)))
669 AND ( (Recinfo.segment6_low = X_Segment6_Low)
670 OR ( (Recinfo.segment6_low IS NULL)
671 AND (X_Segment6_Low IS NULL)))
672 AND ( (Recinfo.segment6_high = X_Segment6_High)
673 OR ( (Recinfo.segment6_high IS NULL)
674 AND (X_Segment6_High IS NULL)))
675 AND ( (Recinfo.segment7_low = X_Segment7_Low)
676 OR ( (Recinfo.segment7_low IS NULL)
677 AND (X_Segment7_Low IS NULL)))
678 AND ( (Recinfo.segment7_high = X_Segment7_High)
679 OR ( (Recinfo.segment7_high IS NULL)
680 AND (X_Segment7_High IS NULL)))
681 AND ( (Recinfo.segment8_low = X_Segment8_Low)
682 OR ( (Recinfo.segment8_low IS NULL)
683 AND (X_Segment8_Low IS NULL)))
684 AND ( (Recinfo.segment8_high = X_Segment8_High)
685 OR ( (Recinfo.segment8_high IS NULL)
686 AND (X_Segment8_High IS NULL)))
687 AND ( (Recinfo.segment9_low = X_Segment9_Low)
688 OR ( (Recinfo.segment9_low IS NULL)
689 AND (X_Segment9_Low IS NULL)))
690 AND ( (Recinfo.segment9_high = X_Segment9_High)
691 OR ( (Recinfo.segment9_high IS NULL)
692 AND (X_Segment9_High IS NULL)))
693 AND ( (Recinfo.segment10_low = X_Segment10_Low)
694 OR ( (Recinfo.segment10_low IS NULL)
695 AND (X_Segment10_Low IS NULL)))
696 AND ( (Recinfo.segment10_high = X_Segment10_High)
697 OR ( (Recinfo.segment10_high IS NULL)
698 AND (X_Segment10_High IS NULL)))
699 AND ( (Recinfo.segment11_low = X_Segment11_Low)
700 OR ( (Recinfo.segment11_low IS NULL)
701 AND (X_Segment11_Low IS NULL)))
702 AND ( (Recinfo.segment11_high = X_Segment11_High)
703 OR ( (Recinfo.segment11_high IS NULL)
704 AND (X_Segment11_High IS NULL)))
705 AND ( (Recinfo.segment12_low = X_Segment12_Low)
706 OR ( (Recinfo.segment12_low IS NULL)
707 AND (X_Segment12_Low IS NULL)))
708 AND ( (Recinfo.segment12_high = X_Segment12_High)
709 OR ( (Recinfo.segment12_high IS NULL)
710 AND (X_Segment12_High IS NULL)))
711 AND ( (Recinfo.segment13_low = X_Segment13_Low)
712 OR ( (Recinfo.segment13_low IS NULL)
713 AND (X_Segment13_Low IS NULL)))
714 AND ( (Recinfo.segment13_high = X_Segment13_High)
715 OR ( (Recinfo.segment13_high IS NULL)
716 AND (X_Segment13_High IS NULL)))
717 AND ( (Recinfo.segment14_low = X_Segment14_Low)
718 OR ( (Recinfo.segment14_low IS NULL)
719 AND (X_Segment14_Low IS NULL)))
720 AND ( (Recinfo.segment14_high = X_Segment14_High)
721 OR ( (Recinfo.segment14_high IS NULL)
722 AND (X_Segment14_High IS NULL)))
723 AND ( (Recinfo.segment15_low = X_Segment15_Low)
724 OR ( (Recinfo.segment15_low IS NULL)
725 AND (X_Segment15_Low IS NULL)))
726 AND ( (Recinfo.segment15_high = X_Segment15_High)
727 OR ( (Recinfo.segment15_high IS NULL)
728 AND (X_Segment15_High IS NULL)))
729 AND ( (Recinfo.segment16_low = X_Segment16_Low)
730 OR ( (Recinfo.segment16_low IS NULL)
731 AND (X_Segment16_Low IS NULL)))
732 AND ( (Recinfo.segment16_high = X_Segment16_High)
733 OR ( (Recinfo.segment16_high IS NULL)
734 AND (X_Segment16_High IS NULL)))
735 AND ( (Recinfo.segment17_low = X_Segment17_Low)
736 OR ( (Recinfo.segment17_low IS NULL)
737 AND (X_Segment17_Low IS NULL)))
738 AND ( (Recinfo.segment17_high = X_Segment17_High)
739 OR ( (Recinfo.segment17_high IS NULL)
740 AND (X_Segment17_High IS NULL)))
741 AND ( (Recinfo.segment18_low = X_Segment18_Low)
742 OR ( (Recinfo.segment18_low IS NULL)
743 AND (X_Segment18_Low IS NULL)))
744 AND ( (Recinfo.segment18_high = X_Segment18_High)
745 OR ( (Recinfo.segment18_high IS NULL)
746 AND (X_Segment18_High IS NULL)))
747 AND ( (Recinfo.segment19_low = X_Segment19_Low)
748 OR ( (Recinfo.segment19_low IS NULL)
749 AND (X_Segment19_Low IS NULL)))
750 AND ( (Recinfo.segment19_high = X_Segment19_High)
751 OR ( (Recinfo.segment19_high IS NULL)
752 AND (X_Segment19_High IS NULL)))
753 AND ( (Recinfo.segment20_low = X_Segment20_Low)
754 OR ( (Recinfo.segment20_low IS NULL)
755 AND (X_Segment20_Low IS NULL)))
756 AND ( (Recinfo.segment20_high = X_Segment20_High)
757 OR ( (Recinfo.segment20_high IS NULL)
758 AND (X_Segment20_High IS NULL)))
759 AND ( (Recinfo.segment21_low = X_Segment21_Low)
760 OR ( (Recinfo.segment21_low IS NULL)
761 AND (X_Segment21_Low IS NULL)))
762 AND ( (Recinfo.segment21_high = X_Segment21_High)
763 OR ( (Recinfo.segment21_high IS NULL)
764 AND (X_Segment21_High IS NULL)))
765 AND ( (Recinfo.segment22_low = X_Segment22_Low)
766 OR ( (Recinfo.segment22_low IS NULL)
767 AND (X_Segment22_Low IS NULL)))
768 AND ( (Recinfo.segment22_high = X_Segment22_High)
769 OR ( (Recinfo.segment22_high IS NULL)
770 AND (X_Segment22_High IS NULL)))
771 AND ( (Recinfo.segment23_low = X_Segment23_Low)
772 OR ( (Recinfo.segment23_low IS NULL)
773 AND (X_Segment23_Low IS NULL)))
774 AND ( (Recinfo.segment23_high = X_Segment23_High)
775 OR ( (Recinfo.segment23_high IS NULL)
776 AND (X_Segment23_High IS NULL)))
777 AND ( (Recinfo.segment24_low = X_Segment24_Low)
778 OR ( (Recinfo.segment24_low IS NULL)
779 AND (X_Segment24_Low IS NULL)))
780 AND ( (Recinfo.segment24_high = X_Segment24_High)
781 OR ( (Recinfo.segment24_high IS NULL)
782 AND (X_Segment24_High IS NULL)))
783 AND ( (Recinfo.segment25_low = X_Segment25_Low)
784 OR ( (Recinfo.segment25_low IS NULL)
785 AND (X_Segment25_Low IS NULL)))
786 AND ( (Recinfo.segment25_high = X_Segment25_High)
787 OR ( (Recinfo.segment25_high IS NULL)
788 AND (X_Segment25_High IS NULL)))) THEN
789 IF (
790 ( (Recinfo.segment26_low = X_Segment26_Low)
791 OR ( (Recinfo.segment26_low IS NULL)
792 AND (X_Segment26_Low IS NULL)))
793 AND ( (Recinfo.segment26_high = X_Segment26_High)
794 OR ( (Recinfo.segment26_high IS NULL)
795 AND (X_Segment26_High IS NULL)))
796 AND ( (Recinfo.segment27_low = X_Segment27_Low)
797 OR ( (Recinfo.segment27_low IS NULL)
798 AND (X_Segment27_Low IS NULL)))
799 AND ( (Recinfo.segment27_high = X_Segment27_High)
800 OR ( (Recinfo.segment27_high IS NULL)
801 AND (X_Segment27_High IS NULL)))
802 AND ( (Recinfo.segment28_low = X_Segment28_Low)
803 OR ( (Recinfo.segment28_low IS NULL)
804 AND (X_Segment28_Low IS NULL)))
805 AND ( (Recinfo.segment28_high = X_Segment28_High)
806 OR ( (Recinfo.segment28_high IS NULL)
807 AND (X_Segment28_High IS NULL)))
808 AND ( (Recinfo.segment29_low = X_Segment29_Low)
809 OR ( (Recinfo.segment29_low IS NULL)
810 AND (X_Segment29_Low IS NULL)))
811 AND ( (Recinfo.segment29_high = X_Segment29_High)
812 OR ( (Recinfo.segment29_high IS NULL)
813 AND (X_Segment29_High IS NULL)))
814 AND ( (Recinfo.segment30_low = X_Segment30_Low)
815 OR ( (Recinfo.segment30_low IS NULL)
816 AND (X_Segment30_Low IS NULL)))
817 AND ( (Recinfo.segment30_high = X_Segment30_High)
818 OR ( (Recinfo.segment30_high IS NULL)
819 AND (X_Segment30_High IS NULL)))
820 AND ( (Recinfo.context = X_Context)
821 OR ( (Recinfo.context IS NULL)
822 AND (X_Context IS NULL)))
823 AND ( (Recinfo.attribute1 = X_Attribute1)
824 OR ( (Recinfo.attribute1 IS NULL)
825 AND (X_Attribute1 IS NULL)))
826 AND ( (Recinfo.attribute2 = X_Attribute2)
827 OR ( (Recinfo.attribute2 IS NULL)
828 AND (X_Attribute2 IS NULL)))
829 AND ( (Recinfo.attribute3 = X_Attribute3)
830 OR ( (Recinfo.attribute3 IS NULL)
831 AND (X_Attribute3 IS NULL)))
832 AND ( (Recinfo.attribute4 = X_Attribute4)
833 OR ( (Recinfo.attribute4 IS NULL)
834 AND (X_Attribute4 IS NULL)))
835 AND ( (Recinfo.attribute5 = X_Attribute5)
836 OR ( (Recinfo.attribute5 IS NULL)
837 AND (X_Attribute5 IS NULL)))
838 AND ( (Recinfo.attribute6 = X_Attribute6)
839 OR ( (Recinfo.attribute6 IS NULL)
840 AND (X_Attribute6 IS NULL)))
841 AND ( (Recinfo.attribute7 = X_Attribute7)
842 OR ( (Recinfo.attribute7 IS NULL)
843 AND (X_Attribute7 IS NULL)))
844 AND ( (Recinfo.attribute8 = X_Attribute8)
845 OR ( (Recinfo.attribute8 IS NULL)
846 AND (X_Attribute8 IS NULL)))
847 AND ( (Recinfo.attribute9 = X_Attribute9)
848 OR ( (Recinfo.attribute9 IS NULL)
849 AND (X_Attribute9 IS NULL)))
850 AND ( (Recinfo.attribute10 = X_Attribute10)
851 OR ( (Recinfo.attribute10 IS NULL)
852 AND (X_Attribute10 IS NULL)))
853 AND ( (Recinfo.attribute11 = X_Attribute11)
854 OR ( (Recinfo.attribute11 IS NULL)
855 AND (X_Attribute11 IS NULL)))
856 AND ( (Recinfo.attribute12 = X_Attribute12)
857 OR ( (Recinfo.attribute12 IS NULL)
858 AND (X_Attribute12 IS NULL)))
859 AND ( (Recinfo.attribute13 = X_Attribute13)
860 OR ( (Recinfo.attribute13 IS NULL)
861 AND (X_Attribute13 IS NULL)))
862 AND ( (Recinfo.attribute14 = X_Attribute14)
863 OR ( (Recinfo.attribute14 IS NULL)
864 AND (X_Attribute14 IS NULL)))
865 AND ( (Recinfo.attribute15 = X_Attribute15)
866 OR ( (Recinfo.attribute15 IS NULL)
867 AND (X_Attribute15 IS NULL)))
868 ) then
869 return;
870 else
871 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
872 APP_EXCEPTION.RAISE_EXCEPTION;
873 end if;
874 else
875 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
876 APP_EXCEPTION.RAISE_EXCEPTION;
877 end if;
878 END Lock_Row;
879
880 PROCEDURE Update_Row(X_Rowid VARCHAR2,
881 X_Budget_Entity_Id NUMBER,
882 X_Ledger_Id NUMBER,
883 X_Currency_Code VARCHAR2,
884 X_Entry_Code VARCHAR2,
885 X_Range_Id NUMBER,
886 X_Status VARCHAR2,
887 X_Last_Update_Date DATE,
888 X_Last_Updated_By NUMBER,
889 X_Last_Update_Login NUMBER,
890 X_Sequence_Number NUMBER,
891 X_Segment1_Low VARCHAR2,
892 X_Segment1_High VARCHAR2,
893 X_Segment2_Low VARCHAR2,
894 X_Segment2_High VARCHAR2,
895 X_Segment3_Low VARCHAR2,
896 X_Segment3_High VARCHAR2,
897 X_Segment4_Low VARCHAR2,
898 X_Segment4_High VARCHAR2,
899 X_Segment5_Low VARCHAR2,
900 X_Segment5_High VARCHAR2,
901 X_Segment6_Low VARCHAR2,
902 X_Segment6_High VARCHAR2,
903 X_Segment7_Low VARCHAR2,
904 X_Segment7_High VARCHAR2,
905 X_Segment8_Low VARCHAR2,
906 X_Segment8_High VARCHAR2,
907 X_Segment9_Low VARCHAR2,
908 X_Segment9_High VARCHAR2,
909 X_Segment10_Low VARCHAR2,
910 X_Segment10_High VARCHAR2,
911 X_Segment11_Low VARCHAR2,
912 X_Segment11_High VARCHAR2,
913 X_Segment12_Low VARCHAR2,
914 X_Segment12_High VARCHAR2,
915 X_Segment13_Low VARCHAR2,
916 X_Segment13_High VARCHAR2,
917 X_Segment14_Low VARCHAR2,
918 X_Segment14_High VARCHAR2,
919 X_Segment15_Low VARCHAR2,
920 X_Segment15_High VARCHAR2,
921 X_Segment16_Low VARCHAR2,
922 X_Segment16_High VARCHAR2,
923 X_Segment17_Low VARCHAR2,
924 X_Segment17_High VARCHAR2,
925 X_Segment18_Low VARCHAR2,
926 X_Segment18_High VARCHAR2,
927 X_Segment19_Low VARCHAR2,
928 X_Segment19_High VARCHAR2,
929 X_Segment20_Low VARCHAR2,
930 X_Segment20_High VARCHAR2,
931 X_Segment21_Low VARCHAR2,
932 X_Segment21_High VARCHAR2,
933 X_Segment22_Low VARCHAR2,
934 X_Segment22_High VARCHAR2,
935 X_Segment23_Low VARCHAR2,
936 X_Segment23_High VARCHAR2,
937 X_Segment24_Low VARCHAR2,
938 X_Segment24_High VARCHAR2,
939 X_Segment25_Low VARCHAR2,
940 X_Segment25_High VARCHAR2,
941 X_Segment26_Low VARCHAR2,
942 X_Segment26_High VARCHAR2,
943 X_Segment27_Low VARCHAR2,
944 X_Segment27_High VARCHAR2,
945 X_Segment28_Low VARCHAR2,
946 X_Segment28_High VARCHAR2,
947 X_Segment29_Low VARCHAR2,
948 X_Segment29_High VARCHAR2,
949 X_Segment30_Low VARCHAR2,
950 X_Segment30_High VARCHAR2,
951 X_Context VARCHAR2,
952 X_Attribute1 VARCHAR2,
953 X_Attribute2 VARCHAR2,
954 X_Attribute3 VARCHAR2,
955 X_Attribute4 VARCHAR2,
956 X_Attribute5 VARCHAR2,
957 X_Attribute6 VARCHAR2,
958 X_Attribute7 VARCHAR2,
959 X_Attribute8 VARCHAR2,
960 X_Attribute9 VARCHAR2,
961 X_Attribute10 VARCHAR2,
962 X_Attribute11 VARCHAR2,
963 X_Attribute12 VARCHAR2,
964 X_Attribute13 VARCHAR2,
965 X_Attribute14 VARCHAR2,
966 X_Attribute15 VARCHAR2
967 ) IS
968 BEGIN
969 UPDATE GL_BUDGET_ASSIGNMENT_RANGES
970 SET
971
972 budget_entity_id = X_Budget_Entity_Id,
973 ledger_id = X_Ledger_Id,
974 currency_code = X_Currency_Code,
975 entry_code = X_Entry_Code,
976 range_id = X_Range_Id,
977 status = X_Status,
978 last_update_date = X_Last_Update_Date,
979 last_updated_by = X_Last_Updated_By,
980 last_update_login = X_Last_Update_Login,
981 sequence_number = X_Sequence_Number,
982 segment1_low = X_Segment1_Low,
983 segment1_high = X_Segment1_High,
984 segment2_low = X_Segment2_Low,
985 segment2_high = X_Segment2_High,
986 segment3_low = X_Segment3_Low,
987 segment3_high = X_Segment3_High,
988 segment4_low = X_Segment4_Low,
989 segment4_high = X_Segment4_High,
990 segment5_low = X_Segment5_Low,
991 segment5_high = X_Segment5_High,
992 segment6_low = X_Segment6_Low,
993 segment6_high = X_Segment6_High,
994 segment7_low = X_Segment7_Low,
995 segment7_high = X_Segment7_High,
996 segment8_low = X_Segment8_Low,
997 segment8_high = X_Segment8_High,
998 segment9_low = X_Segment9_Low,
999 segment9_high = X_Segment9_High,
1000 segment10_low = X_Segment10_Low,
1001 segment10_high = X_Segment10_High,
1002 segment11_low = X_Segment11_Low,
1003 segment11_high = X_Segment11_High,
1004 segment12_low = X_Segment12_Low,
1005 segment12_high = X_Segment12_High,
1006 segment13_low = X_Segment13_Low,
1007 segment13_high = X_Segment13_High,
1008 segment14_low = X_Segment14_Low,
1009 segment14_high = X_Segment14_High,
1010 segment15_low = X_Segment15_Low,
1011 segment15_high = X_Segment15_High,
1012 segment16_low = X_Segment16_Low,
1013 segment16_high = X_Segment16_High,
1014 segment17_low = X_Segment17_Low,
1015 segment17_high = X_Segment17_High,
1016 segment18_low = X_Segment18_Low,
1017 segment18_high = X_Segment18_High,
1018 segment19_low = X_Segment19_Low,
1019 segment19_high = X_Segment19_High,
1020 segment20_low = X_Segment20_Low,
1021 segment20_high = X_Segment20_High,
1022 segment21_low = X_Segment21_Low,
1023 segment21_high = X_Segment21_High,
1024 segment22_low = X_Segment22_Low,
1025 segment22_high = X_Segment22_High,
1026 segment23_low = X_Segment23_Low,
1027 segment23_high = X_Segment23_High,
1028 segment24_low = X_Segment24_Low,
1029 segment24_high = X_Segment24_High,
1030 segment25_low = X_Segment25_Low,
1031 segment25_high = X_Segment25_High,
1032 segment26_low = X_Segment26_Low,
1033 segment26_high = X_Segment26_High,
1034 segment27_low = X_Segment27_Low,
1035 segment27_high = X_Segment27_High,
1036 segment28_low = X_Segment28_Low,
1037 segment28_high = X_Segment28_High,
1038 segment29_low = X_Segment29_Low,
1039 segment29_high = X_Segment29_High,
1040 segment30_low = X_Segment30_Low,
1041 segment30_high = X_Segment30_High,
1042 context = X_Context,
1043 attribute1 = X_Attribute1,
1044 attribute2 = X_Attribute2,
1045 attribute3 = X_Attribute3,
1046 attribute4 = X_Attribute4,
1047 attribute5 = X_Attribute5,
1048 attribute6 = X_Attribute6,
1049 attribute7 = X_Attribute7,
1050 attribute8 = X_Attribute8,
1051 attribute9 = X_Attribute9,
1052 attribute10 = X_Attribute10,
1053 attribute11 = X_Attribute11,
1054 attribute12 = X_Attribute12,
1055 attribute13 = X_Attribute13,
1056 attribute14 = X_Attribute14,
1057 attribute15 = X_Attribute15
1058 WHERE rowid = X_rowid;
1059
1060 if (SQL%NOTFOUND) then
1061 RAISE NO_DATA_FOUND;
1062 end if;
1063
1064 END Update_Row;
1065
1066 PROCEDURE Delete_Row(X_Range_Id NUMBER, X_Rowid VARCHAR2) IS
1067 BEGIN
1068
1069 -- Delete any budgetary control options defined for this range
1070 gl_budorg_bc_options_pkg.delete_budorg_bc_options(X_Range_id);
1071
1072 -- Delete any assignments made by this range
1073 gl_budget_assignment_pkg.delete_range_assignments(X_Range_Id);
1074
1075 DELETE FROM GL_BUDGET_ASSIGNMENT_RANGES
1076 WHERE rowid = X_Rowid;
1077
1078 if (SQL%NOTFOUND) then
1079 RAISE NO_DATA_FOUND;
1080 end if;
1081 END Delete_Row;
1082
1083 PROCEDURE Insert_Range(
1084 X_Rowid IN OUT NOCOPY VARCHAR2,
1085 X_Budget_Entity_Id NUMBER,
1086 X_Ledger_Id NUMBER,
1087 X_Currency_Code VARCHAR2,
1088 X_Entry_Code VARCHAR2,
1089 X_Range_Id NUMBER,
1090 X_Status VARCHAR2,
1091 X_Last_Update_Date DATE,
1092 X_Created_By NUMBER,
1093 X_Creation_Date DATE,
1094 X_Last_Updated_By NUMBER,
1095 X_Last_Update_Login NUMBER,
1096 X_Sequence_Number NUMBER,
1097 X_Segment1_Low VARCHAR2,
1098 X_Segment1_High VARCHAR2,
1099 X_Segment2_Low VARCHAR2,
1100 X_Segment2_High VARCHAR2,
1101 X_Segment3_Low VARCHAR2,
1102 X_Segment3_High VARCHAR2,
1103 X_Segment4_Low VARCHAR2,
1104 X_Segment4_High VARCHAR2,
1105 X_Segment5_Low VARCHAR2,
1106 X_Segment5_High VARCHAR2,
1107 X_Segment6_Low VARCHAR2,
1108 X_Segment6_High VARCHAR2,
1109 X_Segment7_Low VARCHAR2,
1110 X_Segment7_High VARCHAR2,
1111 X_Segment8_Low VARCHAR2,
1112 X_Segment8_High VARCHAR2,
1113 X_Segment9_Low VARCHAR2,
1114 X_Segment9_High VARCHAR2,
1115 X_Segment10_Low VARCHAR2,
1116 X_Segment10_High VARCHAR2,
1117 X_Segment11_Low VARCHAR2,
1118 X_Segment11_High VARCHAR2,
1119 X_Segment12_Low VARCHAR2,
1120 X_Segment12_High VARCHAR2,
1121 X_Segment13_Low VARCHAR2,
1122 X_Segment13_High VARCHAR2,
1123 X_Segment14_Low VARCHAR2,
1124 X_Segment14_High VARCHAR2,
1125 X_Segment15_Low VARCHAR2,
1126 X_Segment15_High VARCHAR2,
1127 X_Segment16_Low VARCHAR2,
1128 X_Segment16_High VARCHAR2,
1129 X_Segment17_Low VARCHAR2,
1130 X_Segment17_High VARCHAR2,
1131 X_Segment18_Low VARCHAR2,
1132 X_Segment18_High VARCHAR2,
1133 X_Segment19_Low VARCHAR2,
1134 X_Segment19_High VARCHAR2,
1135 X_Segment20_Low VARCHAR2,
1136 X_Segment20_High VARCHAR2,
1137 X_Segment21_Low VARCHAR2,
1138 X_Segment21_High VARCHAR2,
1139 X_Segment22_Low VARCHAR2,
1140 X_Segment22_High VARCHAR2,
1141 X_Segment23_Low VARCHAR2,
1142 X_Segment23_High VARCHAR2,
1143 X_Segment24_Low VARCHAR2,
1144 X_Segment24_High VARCHAR2,
1145 X_Segment25_Low VARCHAR2,
1146 X_Segment25_High VARCHAR2,
1147 X_Segment26_Low VARCHAR2,
1148 X_Segment26_High VARCHAR2,
1149 X_Segment27_Low VARCHAR2,
1150 X_Segment27_High VARCHAR2,
1151 X_Segment28_Low VARCHAR2,
1152 X_Segment28_High VARCHAR2,
1153 X_Segment29_Low VARCHAR2,
1154 X_Segment29_High VARCHAR2,
1155 X_Segment30_Low VARCHAR2,
1156 X_Segment30_High VARCHAR2,
1157 X_Context VARCHAR2,
1158 X_Attribute1 VARCHAR2,
1159 X_Attribute2 VARCHAR2,
1160 X_Attribute3 VARCHAR2,
1161 X_Attribute4 VARCHAR2,
1162 X_Attribute5 VARCHAR2,
1163 X_Attribute6 VARCHAR2,
1164 X_Attribute7 VARCHAR2,
1165 X_Attribute8 VARCHAR2,
1166 X_Attribute9 VARCHAR2,
1167 X_Attribute10 VARCHAR2,
1168 X_Attribute11 VARCHAR2,
1169 X_Attribute12 VARCHAR2,
1170 X_Attribute13 VARCHAR2,
1171 X_Attribute14 VARCHAR2,
1172 X_Attribute15 VARCHAR2
1173 ) IS
1174
1175 CURSOR active_currencies IS
1176 SELECT 'X'
1177 FROM FND_CURRENCIES_ACTIVE_V fnd
1178 WHERE fnd.currency_code = X_Currency_Code;
1179
1180 CURSOR check_entry_code IS
1181 SELECT 'X'
1182 FROM GL_LOOKUPS
1183 WHERE LOOKUP_TYPE = 'ENTRY_CODE'
1184 AND lookup_code = X_Entry_Code;
1185
1186 CURSOR check_bud_org_name IS
1187 SELECT lk.meaning
1188 FROM GL_BUDGET_ENTITIES bud,
1189 GL_LOOKUPS lk
1190 WHERE bud.budget_entity_id = X_Budget_Entity_Id
1191 AND lk.lookup_type = 'LITERAL'
1192 AND lk.lookup_code = 'ALL'
1193 AND lk.meaning = bud.name;
1194
1195 L_Range_Id NUMBER;
1196 L_Chart_Of_Accounts_Id NUMBER;
1197 L_Functional_Currency VARCHAR2(15);
1198 L_Budgetary_Control_Flag VARCHAR2(1);
1199 dummy VARCHAR2(80);
1200
1201 BEGIN
1202
1203 L_Range_Id := X_Range_Id;
1204
1205 -- Validate Entry Code
1206 OPEN check_entry_code;
1207 FETCH check_entry_code INTO dummy;
1208 IF check_entry_code%NOTFOUND THEN
1209 CLOSE check_entry_code;
1210 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
1211 fnd_message.set_token('VALUE', X_Entry_Code);
1212 fnd_message.set_token('ATTRIBUTE', 'EntryCode');
1213 app_exception.raise_exception;
1214 END IF;
1215 CLOSE check_entry_code;
1216
1217 -- Validate Budget Name
1218 OPEN check_bud_org_name;
1219 FETCH check_bud_org_name INTO dummy;
1220 IF check_bud_org_name%FOUND THEN
1221 CLOSE check_bud_org_name;
1222 fnd_message.set_name('SQLGL', 'GL_API_BUDORG_RANGES_FOR_ALL');
1223 fnd_message.set_token('ALL', dummy);
1224 app_exception.raise_exception;
1225 END IF;
1226 CLOSE check_bud_org_name;
1227
1228 -- Validate currency_code exists and is enabled in FND_CURRENCIES
1229 -- Also, if entry_code is E, only functional currency and STAT is allowed.
1230
1231 SELECT currency_code,
1232 enable_budgetary_control_flag,
1233 chart_of_accounts_id
1234 INTO L_Functional_Currency,
1235 L_Budgetary_Control_Flag,
1236 L_Chart_Of_Accounts_Id
1237 FROM gl_ledgers
1238 WHERE ledger_id = X_Ledger_Id;
1239
1240 -- If entry code is C for Calculated then only valid values for currency are
1241 -- the functional currency and STAT.
1242 IF (X_Entry_Code = 'C') THEN
1243 IF ((X_Currency_Code <> L_Functional_Currency) AND
1244 (X_Currency_Code <> 'STAT')) THEN
1245 fnd_message.set_name('SQLGL', 'GL_API_BUDORG_CALC_CURR_ERR');
1246 app_exception.raise_exception;
1247 END IF;
1248 END IF;
1249
1250 -- Validate that the currency selected is an active currency.
1251 OPEN active_currencies;
1252 FETCH active_currencies INTO dummy;
1253 IF active_currencies%NOTFOUND THEN
1254 CLOSE active_currencies;
1255 fnd_message.set_name('SQLGL', 'GL_API_INVALID_CURR');
1256 app_exception.raise_exception;
1257 END IF;
1258 CLOSE active_currencies;
1259
1260
1261 GL_BUD_ASSIGN_RANGE_PKG.Insert_Row(
1262 X_Rowid,
1263 X_Budget_Entity_Id,
1264 X_Ledger_Id,
1265 X_Currency_Code,
1266 X_Entry_Code,
1267 L_Range_Id,
1268 X_Status,
1269 X_Last_Update_Date,
1270 X_Created_By,
1271 X_Creation_Date,
1272 X_Last_Updated_By,
1273 X_Last_Update_Login,
1274 X_Sequence_Number,
1275 X_Segment1_Low,
1276 X_Segment1_High,
1277 X_Segment2_Low,
1278 X_Segment2_High,
1279 X_Segment3_Low,
1280 X_Segment3_High,
1281 X_Segment4_Low,
1282 X_Segment4_High,
1283 X_Segment5_Low,
1284 X_Segment5_High,
1285 X_Segment6_Low,
1286 X_Segment6_High,
1287 X_Segment7_Low,
1288 X_Segment7_High,
1289 X_Segment8_Low,
1290 X_Segment8_High,
1291 X_Segment9_Low,
1292 X_Segment9_High,
1293 X_Segment10_Low,
1294 X_Segment10_High,
1295 X_Segment11_Low,
1296 X_Segment11_High,
1297 X_Segment12_Low,
1298 X_Segment12_High,
1299 X_Segment13_Low,
1300 X_Segment13_High,
1301 X_Segment14_Low,
1302 X_Segment14_High,
1303 X_Segment15_Low,
1304 X_Segment15_High,
1305 X_Segment16_Low,
1306 X_Segment16_High,
1307 X_Segment17_Low,
1308 X_Segment17_High,
1309 X_Segment18_Low,
1310 X_Segment18_High,
1311 X_Segment19_Low,
1312 X_Segment19_High,
1313 X_Segment20_Low,
1314 X_Segment20_High,
1315 X_Segment21_Low,
1316 X_Segment21_High,
1317 X_Segment22_Low,
1318 X_Segment22_High,
1319 X_Segment23_Low,
1320 X_Segment23_High,
1321 X_Segment24_Low,
1322 X_Segment24_High,
1323 X_Segment25_Low,
1324 X_Segment25_High,
1325 X_Segment26_Low,
1326 X_Segment26_High,
1327 X_Segment27_Low,
1328 X_Segment27_High,
1329 X_Segment28_Low,
1330 X_Segment28_High,
1331 X_Segment29_Low,
1332 X_Segment29_High,
1333 X_Segment30_Low,
1334 X_Segment30_High,
1335 X_Context,
1336 X_Attribute1,
1337 X_Attribute2,
1338 X_Attribute3,
1339 X_Attribute4,
1340 X_Attribute5,
1341 X_Attribute6,
1342 X_Attribute7,
1343 X_Attribute8,
1344 X_Attribute9,
1345 X_Attribute10,
1346 X_Attribute11,
1347 X_Attribute12,
1348 X_Attribute13,
1349 X_Attribute14,
1350 X_Attribute15,
1351 L_Chart_Of_Accounts_Id);
1352
1353 EXCEPTION
1354 WHEN app_exceptions.application_exception THEN
1355 RAISE;
1356 WHEN OTHERS THEN
1357 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1358 fnd_message.set_token('PROCEDURE',
1359 'GL_BUDGET_ASSIGN_RANGE_PKG.Insert_Range');
1360 RAISE;
1361 END Insert_Range;
1362
1363 END gl_bud_assign_range_pkg;