[Home] [Help]
PACKAGE BODY: APPS.GL_CONS_ACCOUNTS_PKG
Source
1 PACKAGE BODY GL_CONS_ACCOUNTS_PKG AS
2 /* $Header: glicoacb.pls 120.3 2005/05/05 01:03:53 kvora ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION Insert_Consolidation_Accounts(
9 X_Consolidation_Run_Id NUMBER,
10 X_Consolidation_Id NUMBER,
11 X_From_Ledger_Id NUMBER) RETURN BOOLEAN IS
12 cons_run_id NUMBER;
13 CURSOR accts is
14 SELECT consolidation_id
15 FROM GL_CONSOLIDATION_ACCOUNTS glca
16 WHERE glca.consolidation_id = X_Consolidation_Id
17 AND glca.consolidation_run_id = cons_run_id ;
18
19 dummy NUMBER;
20
21 BEGIN
22 SELECT max(consolidation_run_id)
23 INTO cons_run_id
24 FROM gl_consolidation_history
25 WHERE consolidation_id = X_Consolidation_Id;
26
27 OPEN accts;
28 FETCH accts INTO dummy;
29
30 IF (accts%FOUND) THEN
31 CLOSE accts;
32 LOCK TABLE gl_consolidation_accounts IN SHARE UPDATE MODE;
33 INSERT INTO GL_CONSOLIDATION_ACCOUNTS
34 (consolidation_run_id, consolidation_id, last_update_date,
35 last_updated_by, from_ledger_id, element_sequence,
36 segment1_low, segment1_high, segment2_low, segment2_high,
37 segment3_low, segment3_high, segment4_low, segment4_high,
38 segment5_low, segment5_high, segment6_low, segment6_high,
39 segment7_low, segment7_high, segment8_low, segment8_high,
40 segment9_low, segment9_high, segment10_low, segment10_high,
41 segment11_low, segment11_high, segment12_low, segment12_high,
42 segment13_low, segment13_high, segment14_low, segment14_high,
43 segment15_low, segment15_high, segment16_low, segment16_high,
44 segment17_low, segment17_high, segment18_low, segment18_high,
45 segment19_low, segment19_high, segment20_low, segment20_high,
46 segment21_low, segment21_high, segment22_low, segment22_high,
47 segment23_low, segment23_high, segment24_low, segment24_high,
48 segment25_low, segment25_high, segment26_low, segment26_high,
49 segment27_low, segment27_high, segment28_low, segment28_high,
50 segment29_low, segment29_high, segment30_low, segment30_high)
51 SELECT X_Consolidation_Run_Id, X_Consolidation_Id, last_update_date,
52 last_updated_by, X_From_Ledger_Id, element_sequence,
53 segment1_low, segment1_high, segment2_low, segment2_high,
54 segment3_low, segment3_high, segment4_low, segment4_high,
55 segment5_low, segment5_high, segment6_low, segment6_high,
56 segment7_low, segment7_high, segment8_low, segment8_high,
57 segment9_low, segment9_high, segment10_low, segment10_high,
58 segment11_low, segment11_high, segment12_low, segment12_high,
59 segment13_low, segment13_high, segment14_low, segment14_high,
60 segment15_low, segment15_high, segment16_low, segment16_high,
61 segment17_low, segment17_high, segment18_low, segment18_high,
62 segment19_low, segment19_high, segment20_low, segment20_high,
63 segment21_low, segment21_high, segment22_low, segment22_high,
64 segment23_low, segment23_high, segment24_low, segment24_high,
65 segment25_low, segment25_high, segment26_low, segment26_high,
66 segment27_low, segment27_high, segment28_low, segment28_high,
67 segment29_low, segment29_high, segment30_low, segment30_high
68 FROM GL_CONSOLIDATION_ACCOUNTS
69 WHERE X_Consolidation_Id = consolidation_id
70 AND consolidation_run_id =
71 (select max(consolidation_run_id)
72 from gl_consolidation_accounts
73 where consolidation_id = X_Consolidation_Id);
74 RETURN TRUE;
75 ELSE
76 CLOSE accts;
77 RETURN FALSE;
78 END IF;
79
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 RETURN FALSE;
83
84 END Insert_Consolidation_Accounts;
85
86
87 PROCEDURE Check_Unique_Element_Sequence(
88 X_Rowid VARCHAR2,
89 X_Consolidation_Id NUMBER,
90 X_Consolidation_Run_Id NUMBER,
91 X_Element_Sequence NUMBER) IS
92
93 CURSOR elem_seq IS
94 SELECT 'x'
95 FROM GL_CONSOLIDATION_ACCOUNTS gca
96 WHERE gca.consolidation_id = X_Consolidation_Id
97 AND gca.consolidation_run_id = X_Consolidation_Run_Id
98 AND gca.element_sequence = X_Element_Sequence
99 AND (X_Rowid is null OR X_Rowid <> gca.rowid);
100
101 dummy VARCHAR2(2);
102
103 BEGIN
104 OPEN elem_seq;
105 FETCH elem_seq INTO dummy;
106
107 IF (elem_seq%FOUND) THEN
108 CLOSE elem_seq;
109 fnd_message.set_name('SQLGL','GL_DUP_LINE_NUMBER');
110 app_exception.raise_exception;
111 END IF;
112
113 CLOSE elem_seq;
114
115 END Check_Unique_Element_Sequence;
116
117 PROCEDURE Check_Account_Ranges_Overlap(
118 X_Rowid VARCHAR2,
119 X_Consolidation_Id NUMBER,
120 X_Consolidation_Run_Id NUMBER,
121 X_Segment1_Low VARCHAR2,
122 X_Segment1_High VARCHAR2,
123 X_Segment2_Low VARCHAR2,
124 X_Segment2_High VARCHAR2,
125 X_Segment3_Low VARCHAR2,
126 X_Segment3_High VARCHAR2,
127 X_Segment4_Low VARCHAR2,
128 X_Segment4_High VARCHAR2,
129 X_Segment5_Low VARCHAR2,
130 X_Segment5_High VARCHAR2,
131 X_Segment6_Low VARCHAR2,
132 X_Segment6_High VARCHAR2,
133 X_Segment7_Low VARCHAR2,
134 X_Segment7_High VARCHAR2,
135 X_Segment8_Low VARCHAR2,
136 X_Segment8_High VARCHAR2,
137 X_Segment9_Low VARCHAR2,
138 X_Segment9_High VARCHAR2,
139 X_Segment10_Low VARCHAR2,
140 X_Segment10_High VARCHAR2,
141 X_Segment11_Low VARCHAR2,
142 X_Segment11_High VARCHAR2,
143 X_Segment12_Low VARCHAR2,
144 X_Segment12_High VARCHAR2,
145 X_Segment13_Low VARCHAR2,
146 X_Segment13_High VARCHAR2,
147 X_Segment14_Low VARCHAR2,
148 X_Segment14_High VARCHAR2,
149 X_Segment15_Low VARCHAR2,
150 X_Segment15_High VARCHAR2,
151 X_Segment16_Low VARCHAR2,
152 X_Segment16_High VARCHAR2,
153 X_Segment17_Low VARCHAR2,
154 X_Segment17_High VARCHAR2,
155 X_Segment18_Low VARCHAR2,
156 X_Segment18_High VARCHAR2,
157 X_Segment19_Low VARCHAR2,
158 X_Segment19_High VARCHAR2,
159 X_Segment20_Low VARCHAR2,
160 X_Segment20_High VARCHAR2,
161 X_Segment21_Low VARCHAR2,
162 X_Segment21_High VARCHAR2,
163 X_Segment22_Low VARCHAR2,
164 X_Segment22_High VARCHAR2,
165 X_Segment23_Low VARCHAR2,
166 X_Segment23_High VARCHAR2,
167 X_Segment24_Low VARCHAR2,
168 X_Segment24_High VARCHAR2,
169 X_Segment25_Low VARCHAR2,
170 X_Segment25_High VARCHAR2,
171 X_Segment26_Low VARCHAR2,
172 X_Segment26_High VARCHAR2,
173 X_Segment27_Low VARCHAR2,
174 X_Segment27_High VARCHAR2,
175 X_Segment28_Low VARCHAR2,
176 X_Segment28_High VARCHAR2,
177 X_Segment29_Low VARCHAR2,
178 X_Segment29_High VARCHAR2,
179 X_Segment30_Low VARCHAR2,
180 X_Segment30_High VARCHAR2
181 ) IS
182 CURSOR Account_Ranges_Overlap IS
183 SELECT 'x'
184 FROM GL_CONSOLIDATION_ACCOUNTS
185 WHERE consolidation_id = X_Consolidation_Id
186 AND consolidation_run_id = X_Consolidation_Run_Id
187 AND (X_Rowid is null OR X_Rowid <> rowid)
188 AND ( nvl(segment1_low, 'x') <= nvl(X_Segment1_High, 'x')
189 AND nvl(segment1_high, 'x') >= nvl(X_Segment1_Low, 'x')
190 AND nvl(segment2_low, 'x') <= nvl(X_Segment2_High, 'x')
191 AND nvl(segment2_high, 'x') >= nvl(X_Segment2_Low, 'x')
192 AND nvl(segment3_low, 'x') <= nvl(X_Segment3_High, 'x')
193 AND nvl(segment3_high, 'x') >= nvl(X_Segment3_Low, 'x')
194 AND nvl(segment4_low, 'x') <= nvl(X_Segment4_High, 'x')
195 AND nvl(segment4_high, 'x') >= nvl(X_Segment4_Low, 'x')
196 AND nvl(segment5_low, 'x') <= nvl(X_Segment5_High, 'x')
197 AND nvl(segment5_high, 'x') >= nvl(X_Segment5_Low, 'x')
198 AND nvl(segment6_low, 'x') <= nvl(X_Segment6_High, 'x')
199 AND nvl(segment6_high, 'x') >= nvl(X_Segment6_Low, 'x')
200 AND nvl(segment7_low, 'x') <= nvl(X_Segment7_High, 'x')
201 AND nvl(segment7_high, 'x') >= nvl(X_Segment7_Low, 'x')
202 AND nvl(segment8_low, 'x') <= nvl(X_Segment8_High, 'x')
203 AND nvl(segment8_high, 'x') >= nvl(X_Segment8_Low, 'x')
204 AND nvl(segment9_low, 'x') <= nvl(X_Segment9_High, 'x')
205 AND nvl(segment9_high, 'x') >= nvl(X_Segment9_Low, 'x')
206 AND nvl(segment10_low, 'x') <= nvl(X_Segment10_High, 'x')
207 AND nvl(segment10_high, 'x') >= nvl(X_Segment10_Low, 'x')
208 AND nvl(segment11_low, 'x') <= nvl(X_Segment11_High, 'x')
209 AND nvl(segment11_high, 'x') >= nvl(X_Segment11_Low, 'x')
210 AND nvl(segment12_low, 'x') <= nvl(X_Segment12_High, 'x')
211 AND nvl(segment12_high, 'x') >= nvl(X_Segment12_Low, 'x')
212 AND nvl(segment13_low, 'x') <= nvl(X_Segment13_High, 'x')
213 AND nvl(segment13_high, 'x') >= nvl(X_Segment13_Low, 'x')
214 AND nvl(segment14_low, 'x') <= nvl(X_Segment14_High, 'x')
215 AND nvl(segment14_high, 'x') >= nvl(X_Segment14_Low, 'x')
216 AND nvl(segment15_low, 'x') <= nvl(X_Segment15_High, 'x')
217 AND nvl(segment15_high, 'x') >= nvl(X_Segment15_Low, 'x')
218 AND nvl(segment16_low, 'x') <= nvl(X_Segment16_High, 'x')
219 AND nvl(segment16_high, 'x') >= nvl(X_Segment16_Low, 'x')
220 AND nvl(segment17_low, 'x') <= nvl(X_Segment17_High, 'x')
221 AND nvl(segment17_high, 'x') >= nvl(X_Segment17_Low, 'x')
222 AND nvl(segment18_low, 'x') <= nvl(X_Segment18_High, 'x')
223 AND nvl(segment18_high, 'x') >= nvl(X_Segment18_Low, 'x')
224 AND nvl(segment19_low, 'x') <= nvl(X_Segment19_High, 'x')
225 AND nvl(segment19_high, 'x') >= nvl(X_Segment19_Low, 'x')
226 AND nvl(segment20_low, 'x') <= nvl(X_Segment20_High, 'x')
227 AND nvl(segment20_high, 'x') >= nvl(X_Segment20_Low, 'x')
228 AND nvl(segment21_low, 'x') <= nvl(X_Segment21_High, 'x')
229 AND nvl(segment21_high, 'x') >= nvl(X_Segment21_Low, 'x')
230 AND nvl(segment22_low, 'x') <= nvl(X_Segment22_High, 'x')
231 AND nvl(segment22_high, 'x') >= nvl(X_Segment22_Low, 'x')
232 AND nvl(segment23_low, 'x') <= nvl(X_Segment23_High, 'x')
233 AND nvl(segment23_high, 'x') >= nvl(X_Segment23_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(segment25_low, 'x') <= nvl(X_Segment25_High, 'x')
237 AND nvl(segment25_high, 'x') >= nvl(X_Segment25_Low, 'x')
238 AND nvl(segment26_low, 'x') <= nvl(X_Segment26_High, 'x')
239 AND nvl(segment26_high, 'x') >= nvl(X_Segment26_Low, 'x')
240 AND nvl(segment27_low, 'x') <= nvl(X_Segment27_High, 'x')
241 AND nvl(segment27_high, 'x') >= nvl(X_Segment27_Low, 'x')
242 AND nvl(segment28_low, 'x') <= nvl(X_Segment28_High, 'x')
243 AND nvl(segment28_high, 'x') >= nvl(X_Segment28_Low, 'x')
244 AND nvl(segment29_low, 'x') <= nvl(X_Segment29_High, 'x')
245 AND nvl(segment29_high, 'x') >= nvl(X_Segment29_Low, 'x')
246 AND nvl(segment30_low, 'x') <= nvl(X_Segment30_High, 'x')
247 AND nvl(segment30_high, 'x') >= nvl(X_Segment30_Low, 'x')
248 );
249 dummy VARCHAR2(2);
250
251 BEGIN
252 OPEN Account_Ranges_Overlap;
253 FETCH Account_Ranges_Overlap INTO dummy;
254
255 IF (Account_Ranges_Overlap%FOUND) THEN
256 CLOSE Account_Ranges_Overlap;
257 fnd_message.set_name('SQLGL','GL_CONS_ACCOUNT_RANGES_OVERLAP');
258 app_exception.raise_exception;
259 END IF;
260
261 CLOSE Account_Ranges_Overlap;
262
263 END Check_Account_Ranges_Overlap;
264
265 FUNCTION Count_Ranges(
266 X_Consolidation_Id NUMBER,
267 X_Consolidation_Run_Id NUMBER) RETURN BOOLEAN IS
268
269 CURSOR range_count IS
270 SELECT 'y'
271 FROM GL_CONSOLIDATION_ACCOUNTS gca
272 WHERE gca.consolidation_id = X_Consolidation_Id
273 AND gca.consolidation_run_id = X_Consolidation_Run_Id;
274
275 var1 VARCHAR2(2);
276
277 BEGIN
278 OPEN range_count;
279 FETCH range_count INTO var1;
280
281 IF (range_count%NOTFOUND) THEN
282 CLOSE range_count;
283 return FALSE;
284 END IF;
285
286 CLOSE range_count;
287 return TRUE;
288
289 END Count_Ranges;
290
291
292 /* Name: copy_ranges
293 * Desc: Copies the ranges for the source run id to the target run id.
294 */
295 PROCEDURE copy_ranges(
296 ConsolidationId NUMBER,
297 SourceRunId NUMBER,
298 TargetRunId NUMBER) IS
299 BEGIN
300 INSERT INTO GL_CONSOLIDATION_ACCOUNTS(
301 consolidation_run_id,
302 consolidation_id,
303 last_update_date,
304 last_updated_by,
305 from_ledger_id,
306 element_sequence,
307 creation_date,
308 created_by,
309 last_update_login,
310 segment1_low,
311 segment1_high,
312 segment2_low,
313 segment2_high,
314 segment3_low,
315 segment3_high,
316 segment4_low,
317 segment4_high,
318 segment5_low,
319 segment5_high,
320 segment6_low,
321 segment6_high,
322 segment7_low,
323 segment7_high,
324 segment8_low,
325 segment8_high,
326 segment9_low,
327 segment9_high,
328 segment10_low,
329 segment10_high,
330 segment11_low,
331 segment11_high,
332 segment12_low,
333 segment12_high,
334 segment13_low,
335 segment13_high,
336 segment14_low,
337 segment14_high,
338 segment15_low,
339 segment15_high,
340 segment16_low,
341 segment16_high,
342 segment17_low,
343 segment17_high,
344 segment18_low,
345 segment18_high,
346 segment19_low,
347 segment19_high,
348 segment20_low,
349 segment20_high,
350 segment21_low,
351 segment21_high,
352 segment22_low,
353 segment22_high,
354 segment23_low,
355 segment23_high,
356 segment24_low,
357 segment24_high,
358 segment25_low,
359 segment25_high,
360 segment26_low,
361 segment26_high,
362 segment27_low,
363 segment27_high,
364 segment28_low,
365 segment28_high,
366 segment29_low,
367 segment29_high,
368 segment30_low,
369 segment30_high
370 ) SELECT
371 TargetRunId,
372 consolidation_id,
373 last_update_date,
374 last_updated_by,
375 from_ledger_id,
376 element_sequence,
377 creation_date,
378 created_by,
379 last_update_login,
380 segment1_low,
381 segment1_high,
382 segment2_low,
383 segment2_high,
384 segment3_low,
385 segment3_high,
386 segment4_low,
387 segment4_high,
388 segment5_low,
389 segment5_high,
390 segment6_low,
391 segment6_high,
392 segment7_low,
393 segment7_high,
394 segment8_low,
395 segment8_high,
396 segment9_low,
397 segment9_high,
398 segment10_low,
399 segment10_high,
400 segment11_low,
401 segment11_high,
402 segment12_low,
403 segment12_high,
404 segment13_low,
405 segment13_high,
406 segment14_low,
407 segment14_high,
408 segment15_low,
409 segment15_high,
410 segment16_low,
411 segment16_high,
412 segment17_low,
413 segment17_high,
414 segment18_low,
415 segment18_high,
416 segment19_low,
417 segment19_high,
418 segment20_low,
419 segment20_high,
420 segment21_low,
421 segment21_high,
422 segment22_low,
423 segment22_high,
424 segment23_low,
425 segment23_high,
426 segment24_low,
427 segment24_high,
428 segment25_low,
429 segment25_high,
430 segment26_low,
431 segment26_high,
432 segment27_low,
433 segment27_high,
434 segment28_low,
435 segment28_high,
436 segment29_low,
437 segment29_high,
438 segment30_low,
439 segment30_high
440 FROM gl_consolidation_accounts
441 WHERE NOT EXISTS (SELECT 1
442 FROM gl_consolidation_accounts
443 WHERE consolidation_id = ConsolidationId
444 AND consolidation_run_id = TargetRunId)
445 AND consolidation_id = ConsolidationId
446 AND consolidation_run_id = SourceRunId;
447
448 END copy_ranges;
449
450
451 PROCEDURE Delete_Account_Range(
452 ConsolidationId NUMBER,
453 StdRunId NUMBER,
454 AvgRunId NUMBER) IS
455 BEGIN
456
457 DELETE FROM GL_CONSOLIDATION_ACCOUNTS
458 WHERE consolidation_id = ConsolidationId
459 AND (consolidation_run_id = StdRunId OR
460 consolidation_run_id = AvgRunId);
461
462 END Delete_Account_Range;
463
464 END GL_CONS_ACCOUNTS_PKG;