[Home] [Help]
PACKAGE BODY: APPS.GL_BUDGET_ASSIGNMENT_PKG
Source
1 PACKAGE BODY gl_budget_assignment_pkg AS
2 /* $Header: glibdasb.pls 120.6 2005/08/25 22:55:46 djogg ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Used to select a particular budget assignment 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_assignments_pkg.select_row(recinfo)
19 -- Notes
20 --
21 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_budget_assignments%ROWTYPE) IS
22 BEGIN
23 SELECT *
24 INTO recinfo
25 FROM gl_budget_assignments
26 WHERE ledger_id = recinfo.ledger_id
27 AND code_combination_id = recinfo.code_combination_id
28 AND currency_code = recinfo.currency_code
29 AND rownum = 1;
30 END SELECT_ROW;
31
32
33 --
34 -- PUBLIC FUNCTIONS
35 --
36
37 PROCEDURE check_unique(lgr_id NUMBER, ccid NUMBER, curr_code VARCHAR2,
38 rng_id NUMBER, row_id VARCHAR2) IS
39 CURSOR chk_duplicates is
40 SELECT 'Duplicate'
41 FROM GL_BUDGET_ASSIGNMENTS ba
42 WHERE ba.ledger_id = lgr_id
43 AND ba.code_combination_id = ccid
44 AND ba.currency_code = curr_code
45 AND ba.range_id = rng_id
46 AND ( row_id is null
47 OR ba.rowid <> row_id);
48 dummy VARCHAR2(100);
49 BEGIN
50 OPEN chk_duplicates;
51 FETCH chk_duplicates INTO dummy;
52
53 IF chk_duplicates%FOUND THEN
54 CLOSE chk_duplicates;
55 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_BUD_ASSIGNMENT');
56 app_exception.raise_exception;
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_assignment_pkg.check_unique');
68 RAISE;
69 END check_unique;
70
71 PROCEDURE delete_range_assignments(xrange_id NUMBER) IS
72 BEGIN
73 DELETE GL_BUDGET_ASSIGNMENTS ba
74 WHERE ba.range_id = xrange_id;
75
76 EXCEPTION
77 WHEN no_data_found THEN
78 RETURN;
79 WHEN OTHERS THEN
80 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
81 fnd_message.set_token(
82 'PROCEDURE',
83 'gl_budget_assignment_pkg.delete_range_assignments');
84 RAISE;
85 END delete_range_assignments;
86
87
88 PROCEDURE delete_assignment(lgr_id NUMBER, ccid NUMBER,
89 curr_code VARCHAR2, rng_id NUMBER) IS
90 BEGIN
91 DELETE GL_BUDGET_ASSIGNMENTS ba
92 WHERE ba.ledger_id = lgr_id
93 AND ba.code_combination_id = ccid
94 AND ba.currency_code = curr_code
95 AND ba.range_id = rng_id
96 AND rownum = 1;
97
98 EXCEPTION
99 WHEN no_data_found THEN
100 RETURN;
101 WHEN OTHERS THEN
102 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
103 fnd_message.set_token(
104 'PROCEDURE',
105 'gl_budget_assignment_pkg.delete_assignment');
106 RAISE;
107 END delete_assignment;
108
109
110
111 FUNCTION is_budget_calculated( xlgr_id NUMBER,
112 xccid NUMBER,
113 xcurr_code VARCHAR2 ) RETURN BOOLEAN IS
114
115 CURSOR c IS
116 SELECT 'Calculated'
117 FROM gl_budget_assignments ba
118 WHERE ba.ledger_id = xlgr_id
119 AND ba.code_combination_id = xccid
120 AND ba.currency_code = xcurr_code
121 AND ba.entry_code = 'C';
122
123 dummy VARCHAR2(100);
124
125 BEGIN
126 OPEN c;
127 FETCH c INTO dummy;
128
129 IF c%FOUND THEN
130 CLOSE c;
131 RETURN( TRUE );
132 ELSE
133 CLOSE c;
134 RETURN( FALSE );
135 END IF;
136
137 EXCEPTION
138 WHEN app_exceptions.application_exception THEN
139 RAISE;
140 WHEN OTHERS THEN
141 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
142 fnd_message.set_token('PROCEDURE',
143 'GL_BUDGET_ASSIGNMENT_PKG.is_budget_calculated');
144 RAISE;
145
146 END is_budget_calculated;
147
148
149 FUNCTION is_acct_stat_enterable( xlgr_id NUMBER,
150 xccid NUMBER ) RETURN BOOLEAN IS
151
152 CURSOR c IS
153 SELECT 'Stat Enterable'
154 FROM gl_budget_assignments ba
155 WHERE ba.ledger_id = xlgr_id
156 AND ba.code_combination_id = xccid
157 AND ba.currency_code = 'STAT'
158 AND ba.entry_code = 'E';
159
160 dummy VARCHAR2(100);
161
162 BEGIN
163 OPEN c;
164 FETCH c INTO dummy;
165
166 IF c%FOUND THEN
167 CLOSE c;
168 RETURN( TRUE );
169 ELSE
170 CLOSE c;
171 RETURN( FALSE );
172 END IF;
173
174 EXCEPTION
175 WHEN app_exceptions.application_exception THEN
176 RAISE;
177 WHEN OTHERS THEN
178 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
179 fnd_message.set_token('PROCEDURE',
180 'GL_BUDGET_ASSIGNMENT_PKG.is_acct_stat_enterable');
181 RAISE;
182
183 END is_acct_stat_enterable;
184
185
186 PROCEDURE select_columns( xlgr_id NUMBER,
187 xccid NUMBER,
188 xcurr_code VARCHAR2,
189 xentity_id IN OUT NOCOPY NUMBER,
190 xentry_code IN OUT NOCOPY VARCHAR2) IS
191
192 recinfo gl_budget_assignments%ROWTYPE;
193
194 BEGIN
195 recinfo.ledger_id := xlgr_id;
196 recinfo.code_combination_id := xccid;
197 recinfo.currency_code := xcurr_code;
198
199 select_row(recinfo);
200
201 xentity_id := recinfo.budget_entity_id;
202 xentry_code := recinfo.entry_code;
203 END select_columns;
204
205
206
207
208 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
209 X_Ledger_Id NUMBER,
210 X_Budget_Entity_Id NUMBER,
211 X_Code_Combination_Id NUMBER,
212 X_Currency_Code VARCHAR2,
213 X_Entry_Code VARCHAR2,
214 X_Ordering_Value VARCHAR2,
215 X_Last_Update_Date DATE,
216 X_Last_Updated_By NUMBER,
217 X_Creation_Date DATE,
218 X_Created_By NUMBER,
219 X_Last_Update_Login NUMBER,
220 X_Attribute1 VARCHAR2,
221 X_Attribute2 VARCHAR2,
222 X_Attribute3 VARCHAR2,
223 X_Attribute4 VARCHAR2,
224 X_Attribute5 VARCHAR2,
225 X_Attribute6 VARCHAR2,
226 X_Attribute7 VARCHAR2,
227 X_Attribute8 VARCHAR2,
228 X_Context VARCHAR2,
229 X_Range_Id NUMBER
230 ) IS
231 CURSOR C IS SELECT rowid FROM GL_BUDGET_ASSIGNMENTS
232
233 WHERE ledger_id = X_Ledger_Id
234
235 AND code_combination_id = X_Code_Combination_Id
236
237 AND currency_code = X_Currency_Code
238 AND range_id = X_Range_Id;
239
240 BEGIN
241
242 -- Make sure the budget organization isn't deleted as the range
243 -- is being inserted
244 gl_budget_entities_pkg.lock_organization(X_BUDGET_ENTITY_ID);
245
246 INSERT INTO GL_BUDGET_ASSIGNMENTS(
247 ledger_id,
248 budget_entity_id,
249 code_combination_id,
250 currency_code,
251 entry_code,
252 ordering_value,
253 last_update_date,
254 last_updated_by,
255 creation_date,
256 created_by,
257 last_update_login,
258 attribute1,
259 attribute2,
260 attribute3,
261 attribute4,
262 attribute5,
263 attribute6,
264 attribute7,
265 attribute8,
266 context,
267 range_id
268 ) VALUES (
269 X_Ledger_Id,
270 X_Budget_Entity_Id,
271 X_Code_Combination_Id,
272 X_Currency_Code,
273 X_Entry_Code,
274 X_Ordering_Value,
275 X_Last_Update_Date,
276 X_Last_Updated_By,
277 X_Creation_Date,
278 X_Created_By,
279 X_Last_Update_Login,
280 X_Attribute1,
281 X_Attribute2,
282 X_Attribute3,
283 X_Attribute4,
284 X_Attribute5,
285 X_Attribute6,
286 X_Attribute7,
287 X_Attribute8,
288 X_Context,
289 X_Range_Id
290 );
291
292 OPEN C;
293 FETCH C INTO X_Rowid;
294 if (C%NOTFOUND) then
295 CLOSE C;
296 RAISE NO_DATA_FOUND;
297 end if;
298 CLOSE C;
299 END Insert_Row;
300
301 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
302
303 X_Ledger_Id NUMBER,
304 X_Budget_Entity_Id NUMBER,
305 X_Code_Combination_Id NUMBER,
306 X_Currency_Code VARCHAR2,
307 X_Entry_Code VARCHAR2,
308 X_Ordering_Value VARCHAR2,
309 X_Attribute1 VARCHAR2,
310 X_Attribute2 VARCHAR2,
311 X_Attribute3 VARCHAR2,
312 X_Attribute4 VARCHAR2,
313 X_Attribute5 VARCHAR2,
314 X_Attribute6 VARCHAR2,
315 X_Attribute7 VARCHAR2,
316 X_Attribute8 VARCHAR2,
317 X_Context VARCHAR2,
318 X_Range_Id NUMBER
319 ) IS
320 CURSOR C IS
321 SELECT *
322 FROM GL_BUDGET_ASSIGNMENTS
323 WHERE rowid = X_Rowid
324 FOR UPDATE of Ledger_Id NOWAIT;
325 Recinfo C%ROWTYPE;
326 BEGIN
327 OPEN C;
328 FETCH C INTO Recinfo;
329 if (C%NOTFOUND) then
330 CLOSE C;
331 RAISE NO_DATA_FOUND;
332 end if;
333 CLOSE C;
334 if (
335 ( (Recinfo.ledger_id = X_Ledger_Id)
336 OR ( (Recinfo.ledger_id IS NULL)
337 AND (X_Ledger_Id IS NULL)))
338 AND ( (Recinfo.budget_entity_id = X_Budget_Entity_Id)
339 OR ( (Recinfo.budget_entity_id IS NULL)
340 AND (X_Budget_Entity_Id IS NULL)))
341 AND ( (Recinfo.code_combination_id = X_Code_Combination_Id)
342 OR ( (Recinfo.code_combination_id IS NULL)
343 AND (X_Code_Combination_Id IS NULL)))
344 AND ( (Recinfo.currency_code = X_Currency_Code)
345 OR ( (Recinfo.currency_code IS NULL)
346 AND (X_Currency_Code IS NULL)))
347 AND ( (Recinfo.entry_code = X_Entry_Code)
348 OR ( (Recinfo.entry_code IS NULL)
349 AND (X_Entry_Code IS NULL)))
350 AND ( (Recinfo.ordering_value = X_Ordering_Value)
351 OR ( (Recinfo.ordering_value IS NULL)
352 AND (X_Ordering_Value IS NULL)))
353 AND ( (Recinfo.attribute1 = X_Attribute1)
354 OR ( (Recinfo.attribute1 IS NULL)
355 AND (X_Attribute1 IS NULL)))
356 AND ( (Recinfo.attribute2 = X_Attribute2)
357 OR ( (Recinfo.attribute2 IS NULL)
358 AND (X_Attribute2 IS NULL)))
359 AND ( (Recinfo.attribute3 = X_Attribute3)
360 OR ( (Recinfo.attribute3 IS NULL)
361 AND (X_Attribute3 IS NULL)))
362 AND ( (Recinfo.attribute4 = X_Attribute4)
363 OR ( (Recinfo.attribute4 IS NULL)
364 AND (X_Attribute4 IS NULL)))
365 AND ( (Recinfo.attribute5 = X_Attribute5)
366 OR ( (Recinfo.attribute5 IS NULL)
367 AND (X_Attribute5 IS NULL)))
368 AND ( (Recinfo.attribute6 = X_Attribute6)
369 OR ( (Recinfo.attribute6 IS NULL)
370 AND (X_Attribute6 IS NULL)))
371 AND ( (Recinfo.attribute7 = X_Attribute7)
372 OR ( (Recinfo.attribute7 IS NULL)
373 AND (X_Attribute7 IS NULL)))
374 AND ( (Recinfo.attribute8 = X_Attribute8)
375 OR ( (Recinfo.attribute8 IS NULL)
376 AND (X_Attribute8 IS NULL)))
377 AND ( (Recinfo.context = X_Context)
378 OR ( (Recinfo.context IS NULL)
379 AND (X_Context IS NULL)))
380 AND ( (Recinfo.range_id = X_Range_Id)
381 OR ( (Recinfo.range_id IS NULL)
382 AND (X_Range_Id IS NULL)))
383 ) then
384 return;
385 else
386 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
387 APP_EXCEPTION.RAISE_EXCEPTION;
388 end if;
389 END Lock_Row;
390
391 PROCEDURE Update_Row(X_Rowid VARCHAR2,
392 X_Ledger_Id NUMBER,
393 X_Budget_Entity_Id NUMBER,
394 X_Code_Combination_Id NUMBER,
395 X_Currency_Code VARCHAR2,
396 X_Entry_Code VARCHAR2,
397 X_Ordering_Value VARCHAR2,
398 X_Last_Update_Date DATE,
399 X_Last_Updated_By NUMBER,
400 X_Last_Update_Login NUMBER,
401 X_Attribute1 VARCHAR2,
402 X_Attribute2 VARCHAR2,
403 X_Attribute3 VARCHAR2,
404 X_Attribute4 VARCHAR2,
405 X_Attribute5 VARCHAR2,
406 X_Attribute6 VARCHAR2,
407 X_Attribute7 VARCHAR2,
408 X_Attribute8 VARCHAR2,
409 X_Context VARCHAR2,
410 X_Range_Id NUMBER
411 ) IS
412 BEGIN
413 UPDATE GL_BUDGET_ASSIGNMENTS
414 SET
415
416 ledger_id = X_Ledger_Id,
417 budget_entity_id = X_Budget_Entity_Id,
418 code_combination_id = X_Code_Combination_Id,
419 currency_code = X_Currency_Code,
423 last_updated_by = X_Last_Updated_By,
420 entry_code = X_Entry_Code,
421 ordering_value = X_Ordering_Value,
422 last_update_date = X_Last_Update_Date,
424 last_update_login = X_Last_Update_Login,
425 attribute1 = X_Attribute1,
426 attribute2 = X_Attribute2,
427 attribute3 = X_Attribute3,
428 attribute4 = X_Attribute4,
429 attribute5 = X_Attribute5,
430 attribute6 = X_Attribute6,
431 attribute7 = X_Attribute7,
432 attribute8 = X_Attribute8,
433 context = X_Context,
434 range_id = X_Range_Id
435 WHERE rowid = X_rowid;
436
437 if (SQL%NOTFOUND) then
438 RAISE NO_DATA_FOUND;
439 end if;
440
441 END Update_Row;
442
443 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
444 BEGIN
445 DELETE FROM GL_BUDGET_ASSIGNMENTS
446 WHERE rowid = X_Rowid;
447
448 if (SQL%NOTFOUND) then
449 RAISE NO_DATA_FOUND;
450 end if;
451 END Delete_Row;
452
453 END gl_budget_assignment_pkg;