[Home] [Help]
PACKAGE BODY: APPS.GLXRVSUB_PKG
Source
1 PACKAGE BODY GLXRVSUB_PKG AS
2 /* $Header: glfcrrvb.pls 120.7 2006/03/02 19:40:44 ticheng ship $ */
3
4 /* ------------------------------------------------------------------------- */
5
6 FUNCTION get_unique_id
7 RETURN NUMBER IS
8 CURSOR get_new_id IS
9 SELECT gl_revaluations_s.NEXTVAL
10 FROM DUAL;
11
12 new_id NUMBER;
13 BEGIN
14 OPEN get_new_id;
15 FETCH get_new_id INTO new_id;
16
17 IF get_new_id%FOUND THEN
18 CLOSE get_new_id;
19 RETURN (new_id);
20 ELSE
21 CLOSE get_new_id;
22 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
23 fnd_message.set_token('SEQUENCE', 'GL_REVALUATIONS_S');
24 app_exception.raise_exception;
25 END IF;
26
27 EXCEPTION
28 WHEN app_exceptions.application_exception THEN
29 RAISE;
30 WHEN OTHERS THEN
31 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
32 fnd_message.set_token('PROCEDURE', 'GLXRVSUB_PKG.get_unique_id');
33 RAISE;
34 END get_unique_id;
35
36 /* ------------------------------------------------------------------------- */
37
38 FUNCTION get_segment_num(
39 coa_id IN VARCHAR2,
40 segment IN VARCHAR2)
41 RETURN NUMBER IS
42 l_application_column_name VARCHAR2(30);
43 l_seg_num NUMBER;
44
45 /* Defined the cursor seg_cursor to fix bug 2981493 */
46 CURSOR seg_cursor (l_coa_id fnd_segment_attribute_values.id_flex_num%type,
47 l_segment fnd_segment_attribute_values.segment_attribute_type%type) IS
48 SELECT segment_num
49 FROM fnd_segment_attribute_values fsav, fnd_id_flex_segments fifs
50 WHERE fsav.application_id = 101
51 AND fsav.id_flex_code = 'GL#'
52 AND fsav.id_flex_num = l_coa_id
53 AND fsav.segment_attribute_type = l_segment
54 AND fsav.attribute_value = 'Y'
55 AND fsav.application_id = fifs.application_id
56 AND fsav.id_flex_code = fifs.id_flex_code
57 AND fsav.id_flex_num = fifs.id_flex_num
58 AND fsav.application_column_name = fifs.application_column_name;
59
60 BEGIN
61 OPEN seg_cursor (coa_id , SEGMENT) ;
62 FETCH seg_cursor into l_seg_num;
63 CLOSE seg_cursor;
64
65 RETURN l_seg_num;
66 END get_segment_num;
67
68 /* ------------------------------------------------------------------------- */
69
70 FUNCTION is_summary_account(
71 coa_id IN VARCHAR2,
72 segment_name IN VARCHAR2,
73 account_num IN VARCHAR2)
74 RETURN BOOLEAN IS
75 l_coa_id NUMBER(15);
76 l_segment_name VARCHAR2(30);
77 l_flex_value_set_id NUMBER(10);
78 l_application_column_name VARCHAR2(30);
79 p_table VARCHAR2(240);
80 p_col VARCHAR2(240);
81 l_summary_flag VARCHAR2(1);
82 BEGIN
83 l_coa_id := TO_NUMBER(coa_id);
84 l_segment_name := segment_name;
85
86 BEGIN
87 SELECT flex_value_set_id
88 INTO l_flex_value_set_id
89 FROM fnd_id_flex_segments
90 WHERE application_id = 101
91 AND id_flex_code = 'GL#'
92 AND id_flex_num = l_coa_id
93 AND application_column_name = l_segment_name;
94 EXCEPTION
95 WHEN OTHERS THEN
96 RETURN FALSE;
97 END;
98
99 BEGIN
100 SELECT NVL(fvt.application_table_name, 'FND_FLEX_VALUES'),
101 NVL(fvt.summary_column_name, 'SUMMARY_FLAG')
102 INTO p_table,
103 p_col
104 FROM fnd_flex_validation_tables fvt, fnd_flex_value_sets fvs
105 WHERE fvs.flex_value_set_id = l_flex_value_set_id
106 AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id;
107 EXCEPTION
108 WHEN OTHERS THEN
109 p_table := 'FND_FLEX_VALUES';
110 p_col := 'SUMMARY_FLAG';
111 END;
112
113 IF ((p_table = 'FND_FLEX_VALUES') AND (p_col = 'SUMMARY_FLAG')) THEN
114 BEGIN
115 SELECT summary_flag
116 INTO l_summary_flag
117 FROM fnd_flex_values
118 WHERE flex_value_set_id = l_flex_value_set_id
119 AND flex_value = account_num;
120 EXCEPTION
121 WHEN OTHERS THEN
122 l_summary_flag := 'N';
123 END;
124 ELSE
125 DECLARE
126 stmt VARCHAR2(200);
127 BEGIN
128 stmt := 'SELECT ' || p_col ||
129 ' FROM ' || p_table ||
130 ' WHERE flex_value_set_id = :flex_vs_id ' ||
131 ' AND flex_value = :flex_val';
132
133 EXECUTE IMMEDIATE stmt INTO l_summary_flag
134 USING l_flex_value_set_id, account_num;
135 EXCEPTION
136 WHEN OTHERS THEN
137 l_summary_flag := 'N';
138 END;
139 END IF;
140
141 IF (l_summary_flag = 'Y') THEN
142 RETURN TRUE;
143 ELSE
144 RETURN FALSE;
145 END IF;
146 END is_summary_account;
147
148 /* ------------------------------------------------------------------------- */
149
150 FUNCTION has_overlapping(
151 x_revaluation_id NUMBER,
152 x_row_id VARCHAR2,
153 x_segment1_low VARCHAR2,
154 x_segment1_high VARCHAR2,
155 x_segment2_low VARCHAR2,
156 x_segment2_high VARCHAR2,
157 x_segment3_low VARCHAR2,
158 x_segment3_high VARCHAR2,
159 x_segment4_low VARCHAR2,
160 x_segment4_high VARCHAR2,
161 x_segment5_low VARCHAR2,
162 x_segment5_high VARCHAR2,
163 x_segment6_low VARCHAR2,
164 x_segment6_high VARCHAR2,
165 x_segment7_low VARCHAR2,
166 x_segment7_high VARCHAR2,
167 x_segment8_low VARCHAR2,
168 x_segment8_high VARCHAR2,
169 x_segment9_low VARCHAR2,
170 x_segment9_high VARCHAR2,
171 x_segment10_low VARCHAR2,
172 x_segment10_high VARCHAR2,
173 x_segment11_low VARCHAR2,
174 x_segment11_high VARCHAR2,
175 x_segment12_low VARCHAR2,
176 x_segment12_high VARCHAR2,
177 x_segment13_low VARCHAR2,
178 x_segment13_high VARCHAR2,
179 x_segment14_low VARCHAR2,
180 x_segment14_high VARCHAR2,
181 x_segment15_low VARCHAR2,
182 x_segment15_high VARCHAR2,
183 x_segment16_low VARCHAR2,
184 x_segment16_high VARCHAR2,
185 x_segment17_low VARCHAR2,
186 x_segment17_high VARCHAR2,
187 x_segment18_low VARCHAR2,
188 x_segment18_high VARCHAR2,
189 x_segment19_low VARCHAR2,
190 x_segment19_high VARCHAR2,
191 x_segment20_low VARCHAR2,
192 x_segment20_high VARCHAR2,
193 x_segment21_low VARCHAR2,
194 x_segment21_high VARCHAR2,
195 x_segment22_low VARCHAR2,
196 x_segment22_high VARCHAR2,
197 x_segment23_low VARCHAR2,
198 x_segment23_high VARCHAR2,
199 x_segment24_low VARCHAR2,
200 x_segment24_high VARCHAR2,
201 x_segment25_low VARCHAR2,
202 x_segment25_high VARCHAR2,
203 x_segment26_low VARCHAR2,
204 x_segment26_high VARCHAR2,
205 x_segment27_low VARCHAR2,
206 x_segment27_high VARCHAR2,
207 x_segment28_low VARCHAR2,
208 x_segment28_high VARCHAR2,
209 x_segment29_low VARCHAR2,
210 x_segment29_high VARCHAR2,
211 x_segment30_low VARCHAR2,
212 x_segment30_high VARCHAR2)
213 RETURN BOOLEAN IS
214 CURSOR check_overlaps IS
215 SELECT 'Overlapping'
216 FROM DUAL
217 WHERE EXISTS( SELECT 'X'
218 FROM gl_reval_account_ranges
219 WHERE revaluation_id = x_revaluation_id
220 AND ( x_row_id IS NULL
221 OR x_row_id <> ROWID)
222 AND NVL(segment30_low, 'X') <=
223 NVL(x_segment30_high, 'X')
224 AND NVL(segment30_high, 'X') >=
225 NVL(x_segment30_low, 'X')
226 AND NVL(segment29_low, 'X') <=
227 NVL(x_segment29_high, 'X')
228 AND NVL(segment29_high, 'X') >=
229 NVL(x_segment29_low, 'X')
230 AND NVL(segment28_low, 'X') <=
231 NVL(x_segment28_high, 'X')
232 AND NVL(segment28_high, 'X') >=
233 NVL(x_segment28_low, 'X')
234 AND NVL(segment27_low, 'X') <=
235 NVL(x_segment27_high, 'X')
236 AND NVL(segment27_high, 'X') >=
237 NVL(x_segment27_low, 'X')
238 AND NVL(segment26_low, 'X') <=
239 NVL(x_segment26_high, 'X')
240 AND NVL(segment26_high, 'X') >=
241 NVL(x_segment26_low, 'X')
242 AND NVL(segment25_low, 'X') <=
243 NVL(x_segment25_high, 'X')
244 AND NVL(segment25_high, 'X') >=
245 NVL(x_segment25_low, 'X')
246 AND NVL(segment24_low, 'X') <=
247 NVL(x_segment24_high, 'X')
248 AND NVL(segment24_high, 'X') >=
249 NVL(x_segment24_low, 'X')
250 AND NVL(segment23_low, 'X') <=
251 NVL(x_segment23_high, 'X')
252 AND NVL(segment23_high, 'X') >=
253 NVL(x_segment23_low, 'X')
254 AND NVL(segment22_low, 'X') <=
255 NVL(x_segment22_high, 'X')
256 AND NVL(segment22_high, 'X') >=
257 NVL(x_segment22_low, 'X')
258 AND NVL(segment21_low, 'X') <=
259 NVL(x_segment21_high, 'X')
260 AND NVL(segment21_high, 'X') >=
261 NVL(x_segment21_low, 'X')
262 AND NVL(segment20_low, 'X') <=
263 NVL(x_segment20_high, 'X')
264 AND NVL(segment20_high, 'X') >=
265 NVL(x_segment20_low, 'X')
266 AND NVL(segment19_low, 'X') <=
267 NVL(x_segment19_high, 'X')
268 AND NVL(segment19_high, 'X') >=
269 NVL(x_segment19_low, 'X')
270 AND NVL(segment18_low, 'X') <=
271 NVL(x_segment18_high, 'X')
272 AND NVL(segment18_high, 'X') >=
273 NVL(x_segment18_low, 'X')
274 AND NVL(segment17_low, 'X') <=
275 NVL(x_segment17_high, 'X')
276 AND NVL(segment17_high, 'X') >=
277 NVL(x_segment17_low, 'X')
278 AND NVL(segment16_low, 'X') <=
279 NVL(x_segment16_high, 'X')
280 AND NVL(segment16_high, 'X') >=
281 NVL(x_segment16_low, 'X')
282 AND NVL(segment15_low, 'X') <=
283 NVL(x_segment15_high, 'X')
284 AND NVL(segment15_high, 'X') >=
285 NVL(x_segment15_low, 'X')
286 AND NVL(segment14_low, 'X') <=
287 NVL(x_segment14_high, 'X')
288 AND NVL(segment14_high, 'X') >=
289 NVL(x_segment14_low, 'X')
290 AND NVL(segment13_low, 'X') <=
291 NVL(x_segment13_high, 'X')
292 AND NVL(segment13_high, 'X') >=
293 NVL(x_segment13_low, 'X')
294 AND NVL(segment12_low, 'X') <=
295 NVL(x_segment12_high, 'X')
296 AND NVL(segment12_high, 'X') >=
297 NVL(x_segment12_low, 'X')
298 AND NVL(segment11_low, 'X') <=
299 NVL(x_segment11_high, 'X')
300 AND NVL(segment11_high, 'X') >=
301 NVL(x_segment11_low, 'X')
302 AND NVL(segment10_low, 'X') <=
303 NVL(x_segment10_high, 'X')
304 AND NVL(segment10_high, 'X') >=
305 NVL(x_segment10_low, 'X')
306 AND NVL(segment9_low, 'X') <=
307 NVL(x_segment9_high, 'X')
308 AND NVL(segment9_high, 'X') >=
309 NVL(x_segment9_low, 'X')
310 AND NVL(segment8_low, 'X') <=
311 NVL(x_segment8_high, 'X')
312 AND NVL(segment8_high, 'X') >=
313 NVL(x_segment8_low, 'X')
314 AND NVL(segment7_low, 'X') <=
315 NVL(x_segment7_high, 'X')
316 AND NVL(segment7_high, 'X') >=
317 NVL(x_segment7_low, 'X')
318 AND NVL(segment6_low, 'X') <=
319 NVL(x_segment6_high, 'X')
320 AND NVL(segment6_high, 'X') >=
321 NVL(x_segment6_low, 'X')
322 AND NVL(segment5_low, 'X') <=
323 NVL(x_segment5_high, 'X')
324 AND NVL(segment5_high, 'X') >=
325 NVL(x_segment5_low, 'X')
326 AND NVL(segment4_low, 'X') <=
327 NVL(x_segment4_high, 'X')
328 AND NVL(segment4_high, 'X') >=
329 NVL(x_segment4_low, 'X')
330 AND NVL(segment3_low, 'X') <=
331 NVL(x_segment3_high, 'X')
332 AND NVL(segment3_high, 'X') >=
333 NVL(x_segment3_low, 'X')
334 AND NVL(segment2_low, 'X') <=
335 NVL(x_segment2_high, 'X')
336 AND NVL(segment2_high, 'X') >=
337 NVL(x_segment2_low, 'X')
338 AND NVL(segment1_low, 'X') <=
339 NVL(x_segment1_high, 'X')
340 AND NVL(segment1_high, 'X') >=
341 NVL(x_segment1_low, 'X'));
342
343 dummy VARCHAR2(100);
344 BEGIN
345 OPEN check_overlaps;
346 FETCH check_overlaps INTO dummy;
347
348 IF check_overlaps%FOUND THEN
349 CLOSE check_overlaps;
350 RETURN TRUE;
351 END IF;
352
353 CLOSE check_overlaps;
354 RETURN FALSE;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
359 fnd_message.set_token('PROCEDURE', 'GLXRVSUB_PKG.check_overlapping');
360 RAISE;
361 END has_overlapping;
362
363 /* ------------------------------------------------------------------------- */
364
365 PROCEDURE raise_exception(
366 err_type NUMBER,
367 segment_name VARCHAR2) IS
368 l_err_type NUMBER(1);
369 l_token VARCHAR2(50);
370 BEGIN
371 l_err_type := err_type;
372 l_token := segment_name;
373
374 IF (l_err_type = 1) THEN
375 fnd_message.set_name('SQLGL', 'GL_REVAL_SEGMENT_NULL_VALUE');
376 fnd_message.set_token('SEG', l_token);
377 app_exception.raise_exception;
378 ELSIF (l_err_type = 2) THEN
379 fnd_message.set_name('SQLGL', 'GL_REVAL_WRONG_RATE');
380 app_exception.raise_exception;
381 ELSIF (l_err_type = 3) THEN
382 fnd_message.set_name('SQLGL', 'GL_REVAL_NO_REVAL_OPTIONS');
383 app_exception.raise_exception;
384 ELSIF (l_err_type = 4) THEN
385 fnd_message.set_name('SQLGL', 'GL_REVAL_DUPLICATE_NAME');
386 app_exception.raise_exception;
387 ELSIF (l_err_type = 5) THEN
388 fnd_message.set_name('SQLGL', 'GL_REVALUE_OVERLAP_RANGE');
389 app_exception.raise_exception;
390 ELSE
391 app_exception.raise_exception;
392 END IF;
393 END raise_exception;
394
395 /* ------------------------------------------------------------------------- */
396
397 FUNCTION name_existed(
398 reval_name VARCHAR2,
399 reval_id NUMBER,
400 coa_id NUMBER)
401 RETURN BOOLEAN IS
402 dummy VARCHAR2(100);
403
404 CURSOR checkname(revaluation_name VARCHAR2, r_id NUMBER, c_id NUMBER) IS
405 SELECT NAME
406 FROM gl_revaluations
407 WHERE name = revaluation_name
408 AND chart_of_accounts_id = c_id
409 AND revaluation_id <> r_id;
410 BEGIN
411 OPEN checkname(reval_name, reval_id, coa_id);
412 FETCH checkname INTO dummy;
413
414 IF checkname%FOUND THEN
415 CLOSE checkname;
416 RETURN TRUE;
417 END IF;
418
419 CLOSE checkname;
420 RETURN FALSE;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
425 fnd_message.set_token('PROCEDURE', 'GLXRVSUB_PKG.name_existed');
426 RAISE;
427 END name_existed;
428
429 /* ------------------------------------------------------------------------- */
430
431 FUNCTION range_found(
432 reval_id NUMBER)
433 RETURN BOOLEAN IS
434 dummy NUMBER(30);
435 r_id NUMBER(30);
436
437 CURSOR check_range_exist IS
438 SELECT revaluation_id
439 FROM gl_reval_account_ranges
440 WHERE revaluation_id = r_id AND ROWNUM = 1;
441 BEGIN
442 r_id := reval_id;
443 OPEN check_range_exist;
444 FETCH check_range_exist INTO dummy;
445
446 IF check_range_exist%FOUND THEN
447 CLOSE check_range_exist;
448 RETURN TRUE;
449 ELSE
450 CLOSE check_range_exist;
451 RETURN FALSE;
452 END IF;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
457 fnd_message.set_token('PROCEDURE', 'GLXRVSUB_PKG.no_range_found');
458 RAISE;
459 END range_found;
460
461 END glxrvsub_pkg;