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