[Home] [Help]
PACKAGE BODY: APPS.GL_ALLOC_BATCHES_PKG
Source
1 PACKAGE BODY gl_alloc_batches_pkg AS
2 /* $Header: glimabab.pls 120.5 2005/05/05 01:11:27 kvora ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE check_unique(batch_name VARCHAR2, row_id VARCHAR2, coa_id NUMBER) IS
9 CURSOR chk_duplicates is
10 SELECT 'Duplicate'
11 FROM GL_ALLOC_BATCHES gab
12 WHERE gab.name = batch_name
13 AND ( row_id is null
14 OR gab.rowid <> row_id)
15 AND gab.chart_of_accounts_id = coa_id;
16 dummy VARCHAR2(100);
17 BEGIN
18 OPEN chk_duplicates;
19 FETCH chk_duplicates INTO dummy;
20
21 IF chk_duplicates%FOUND THEN
22 CLOSE chk_duplicates;
23 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_ALLOC_BATCH_NAME');
24 app_exception.raise_exception;
25 END IF;
26
27 CLOSE chk_duplicates;
28
29 EXCEPTION
30 WHEN app_exceptions.application_exception THEN
31 RAISE;
32 WHEN OTHERS THEN
33 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
34 fnd_message.set_token('PROCEDURE', 'gl_alloc_batches_pkg.check_unique');
35 RAISE;
36 END check_unique;
37
38 FUNCTION get_unique_id RETURN NUMBER IS
39 CURSOR get_new_id IS
40 SELECT gl_alloc_batches_s.NEXTVAL
41 FROM dual;
42 new_id number;
43 BEGIN
44 OPEN get_new_id;
45 FETCH get_new_id INTO new_id;
46
47 IF get_new_id%FOUND THEN
48 CLOSE get_new_id;
49 return(new_id);
50 ELSE
51 CLOSE get_new_id;
52 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
53 fnd_message.set_token('SEQUENCE', 'GL_ALLOC_BATCHES_S');
54 app_exception.raise_exception;
55 END IF;
56
57 EXCEPTION
58 WHEN app_exceptions.application_exception THEN
59 RAISE;
60 WHEN OTHERS THEN
61 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
62 fnd_message.set_token('PROCEDURE', 'gl_alloc_batches_pkg.get_unique_id');
63 RAISE;
64 END get_unique_id;
65
66 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
67 X_Allocation_Batch_Id IN OUT NOCOPY NUMBER,
68 X_Name VARCHAR2,
69 X_Chart_Of_Accounts_Id NUMBER,
70 X_Actual_Flag VARCHAR2,
71 X_Security_Flag VARCHAR2,
72 X_Last_Update_Date DATE,
73 X_Last_Updated_By NUMBER,
74 X_Creation_Date DATE,
75 X_Created_By NUMBER,
76 X_Last_Update_Login NUMBER,
77 X_Description VARCHAR2
78 ) IS
79 CURSOR C IS SELECT rowid FROM GL_ALLOC_BATCHES
80 WHERE allocation_batch_id = X_Allocation_Batch_Id;
81
82 BEGIN
83
84 -- Get batch id if it was not provided
85 IF (X_allocation_batch_id IS NULL) THEN
86 x_allocation_batch_id := gl_alloc_batches_pkg.get_unique_id;
87 END IF;
88
89 INSERT INTO GL_ALLOC_BATCHES(
90 allocation_batch_id,
91 name,
92 chart_of_accounts_id,
93 validation_status,
94 actual_flag,
95 security_flag,
96 last_update_date,
97 last_updated_by,
98 creation_date,
99 created_by,
100 last_update_login,
101 description
102 ) VALUES (
103 X_Allocation_Batch_Id,
104 X_Name,
105 X_Chart_Of_Accounts_Id,
106 'N',
107 X_Actual_Flag,
108 X_Security_Flag,
109 X_Last_Update_Date,
110 X_Last_Updated_By,
111 X_Creation_Date,
112 X_Created_By,
113 X_Last_Update_Login,
114 X_Description
115 );
116
117 OPEN C;
118 FETCH C INTO X_Rowid;
119 if (C%NOTFOUND) then
120 CLOSE C;
121 Raise NO_DATA_FOUND;
122 end if;
123 CLOSE C;
124
125 END Insert_Row;
126
127
128
129 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
130 X_Allocation_Batch_Id NUMBER,
131 X_Name VARCHAR2,
132 X_Chart_Of_Accounts_Id NUMBER,
133 X_Actual_Flag VARCHAR2,
134 X_Security_Flag VARCHAR2,
135 X_Description VARCHAR2
136
137 ) IS
138 CURSOR C IS
139 SELECT *
140 FROM GL_ALLOC_BATCHES
141 WHERE rowid = X_Rowid
142 FOR UPDATE of Allocation_Batch_Id NOWAIT;
143 Recinfo C%ROWTYPE;
144 BEGIN
145 OPEN C;
146 FETCH C INTO Recinfo;
147 if (C%NOTFOUND) then
148 CLOSE C;
149 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
150 APP_EXCEPTION.Raise_Exception;
151 end if;
152 CLOSE C;
153 if (
154 (Recinfo.allocation_batch_id = X_Allocation_Batch_Id)
155 AND (Recinfo.name = X_Name)
156 AND (Recinfo.chart_of_accounts_id = X_Chart_Of_Accounts_Id)
157 AND (Recinfo.actual_flag = X_Actual_Flag)
158 AND (Recinfo.security_flag = X_Security_Flag)
159 AND ( (Recinfo.description = X_Description)
160 OR ( (Recinfo.description IS NULL)
161 AND (X_Description IS NULL)))
162
163 ) then
164 return;
165 else
166 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
167 APP_EXCEPTION.RAISE_EXCEPTION;
168 end if;
169 END Lock_Row;
170
171
172
173 PROCEDURE Update_Row(X_Rowid VARCHAR2,
174 X_Allocation_Batch_Id NUMBER,
175 X_Name VARCHAR2,
176 X_Chart_Of_Accounts_Id NUMBER,
177 X_Actual_Flag VARCHAR2,
178 X_Security_Flag VARCHAR2,
179 X_Last_Update_Date DATE,
180 X_Last_Updated_By NUMBER,
181 X_Last_Update_Login NUMBER,
182 X_Description VARCHAR2
183
184 ) IS
185 BEGIN
186 UPDATE GL_ALLOC_BATCHES
187 SET
188 allocation_batch_id = X_Allocation_Batch_Id,
189 name = X_Name,
190 chart_of_accounts_id = X_Chart_Of_Accounts_Id,
191 actual_flag = X_Actual_Flag,
192 security_flag = X_Security_Flag,
193 last_update_date = X_Last_Update_Date,
194 last_updated_by = X_Last_Updated_By,
195 last_update_login = X_Last_Update_Login,
196 description = X_Description
197 WHERE rowid = X_rowid;
198
199 if (SQL%NOTFOUND) then
200 Raise NO_DATA_FOUND;
201 end if;
202
203 END Update_Row;
204
205 PROCEDURE Delete_Row(Allocation_Batch_Id NUMBER, X_Rowid VARCHAR2) IS
206 CURSOR chk_autoalloc_set is
207 SELECT 'Batch used in AutoAlloc set'
208 FROM GL_AUTO_ALLOC_BATCHES aab
209 WHERE aab.batch_id = Allocation_Batch_Id;
210 dummy VARCHAR2(100);
211 BEGIN
212 -- Make sure that this allocation batch is not being used by any
213 -- of the AutoAllocation set
214 OPEN chk_autoalloc_set;
215 FETCH chk_autoalloc_set INTO dummy;
216
217 IF chk_autoalloc_set%FOUND THEN
218 CLOSE chk_autoalloc_set;
219 fnd_message.set_name('SQLGL', 'GL_BATCH_USED_IN_ALLOC_SET');
220 app_exception.raise_exception;
221 END IF;
222
223 CLOSE chk_autoalloc_set;
224
225 -- Delete all of the associated formulas
226 gl_alloc_formulas_pkg.delete_rows(
227 allocation_batch_id);
228
229 DELETE FROM GL_ALLOC_BATCHES
230 WHERE rowid = X_Rowid;
231
232 if (SQL%NOTFOUND) then
233 Raise NO_DATA_FOUND;
234 end if;
235 END Delete_Row;
236
237 PROCEDURE Autocopy( X_Src_Batch_Id NUMBER,
238 X_Trg_Batch_Id NUMBER,
239 X_Last_Updated_By NUMBER,
240 X_Last_Update_Login NUMBER) IS
241
242
243 BEGIN
244
245 INSERT INTO GL_ALLOC_FORMULAS(
246 allocation_formula_id,
247 allocation_batch_id,
248 name,
249 run_sequence,
250 je_category_name,
251 full_allocation_flag,
252 validation_status,
253 conversion_method_code,
254 currency_conversion_type,
255 last_update_date,
256 last_updated_by,
257 creation_date,
258 created_by,
259 last_update_login,
260 description
261 ) (
262 Select
263 gl_alloc_formulas_s.nextval,
264 X_Trg_Batch_Id,
265 f.Name,
266 f.Run_Sequence,
267 f.Je_Category_Name,
268 f.Full_Allocation_Flag,
269 'N',
270 f.Conversion_Method_Code,
271 f.Currency_Conversion_Type,
272 sysdate,
273 X_Last_Updated_By,
274 sysdate,
275 X_Last_Updated_By,
276 X_Last_Update_Login,
277 f.Description
278 from GL_ALLOC_FORMULAS F
279 where f.Allocation_batch_id = X_Src_Batch_ID
280 );
281
282
283 INSERT INTO GL_ALLOC_FORMULA_LINES(
284 allocation_formula_id,
285 line_number,
286 line_type,
287 operator,
288 last_update_date,
289 last_updated_by,
290 creation_date,
291 created_by,
292 last_update_login,
293 amount,
294 relative_period,
295 period_name,
296 transaction_currency,
297 ledger_currency,
298 currency_type,
299 entered_currency,
300 actual_flag,
301 budget_version_id,
302 encumbrance_type_id,
303 amount_type,
304 ledger_id,
305 ledger_action_code,
306 segment_types_key,
307 segment_break_key,
308 segment1,
309 segment2,
310 segment3,
311 segment4,
312 segment5,
313 segment6,
314 segment7,
315 segment8,
316 segment9,
317 segment10,
318 segment11,
319 segment12,
320 segment13,
321 segment14,
322 segment15,
323 segment16,
324 segment17,
325 segment18,
326 segment19,
327 segment20,
328 segment21,
329 segment22,
330 segment23,
331 segment24,
332 segment25,
333 segment26,
334 segment27,
335 segment28,
336 segment29,
337 segment30
338 ) (
339 Select
340 New.Allocation_Formula_Id,
341 L.Line_Number,
342 L.Line_Type,
343 L.Operator,
344 sysdate,
345 X_Last_Updated_By,
346 sysdate,
347 X_Last_Updated_By,
348 X_Last_Update_Login,
349 L.Amount,
350 L.Relative_Period,
351 L.Period_Name,
352 L.Transaction_Currency,
353 L.Ledger_Currency,
354 L.Currency_Type,
355 L.Entered_Currency,
356 L.Actual_Flag,
357 L.Budget_Version_Id,
358 L.Encumbrance_Type_Id,
359 L.Amount_Type,
360 L.Ledger_Id,
361 L.Ledger_Action_Code,
362 L.Segment_Types_Key,
363 L.Segment_Break_Key,
364 L.Segment1,
365 L.Segment2,
366 L.Segment3,
367 L.Segment4,
368 L.Segment5,
369 L.Segment6,
370 L.Segment7,
371 L.Segment8,
372 L.Segment9,
373 L.Segment10,
374 L.Segment11,
375 L.Segment12,
376 L.Segment13,
377 L.Segment14,
378 L.Segment15,
379 L.Segment16,
380 L.Segment17,
381 L.Segment18,
382 L.Segment19,
383 L.Segment20,
384 L.Segment21,
385 L.Segment22,
386 L.Segment23,
387 L.Segment24,
388 L.Segment25,
389 L.Segment26,
390 L.Segment27,
391 L.Segment28,
392 L.Segment29,
393 L.Segment30
394 from GL_ALLOC_FORMULA_LINES L, GL_ALLOC_FORMULAS New, GL_ALLOC_FORMULAS Old
395 where L.allocation_formula_id = Old.Allocation_formula_id
396 AND New.allocation_batch_id = X_Trg_Batch_Id
397 AND New.name = Old.name
398 AND Old.allocation_batch_id = X_Src_Batch_Id
399 );
400
401
402 EXCEPTION
403 WHEN app_exceptions.application_exception THEN
404 RAISE;
405 WHEN OTHERS THEN
406 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
407 fnd_message.set_token('PROCEDURE',
408 'GL_ALLOC_BATCHES_PKG.Autocopy');
409 RAISE;
410
411 END Autocopy;
412
413 PROCEDURE check_batch(X_Alloc_Batch_Id NUMBER) IS
414 CURSOR chk_autoalloc_set IS
415 SELECT 'Batch used in AutoAlloc set'
416 FROM GL_AUTO_ALLOC_BATCHES aab
417 WHERE aab.batch_id = X_Alloc_Batch_Id;
418 dummy VARCHAR2(100);
419 BEGIN
420 -- Make sure that this allocation batch is not being used by any
421 -- of the AutoAllocation set
422 OPEN chk_autoalloc_set;
423 FETCH chk_autoalloc_set INTO dummy;
424 IF (chk_autoalloc_set%NOTFOUND) THEN
425 CLOSE chk_autoalloc_set;
426 ELSE
427 -- it is being used by some AutoAllocation sets, exit
428 CLOSE chk_autoalloc_set;
429 fnd_message.set_name('SQLGL', 'GL_BATCH_USED_IN_ALLOC_SET');
430 app_exception.raise_exception;
431 END IF;
432
433 END check_batch;
434
435 END gl_alloc_batches_pkg;