1 PACKAGE BODY GL_PARENT_SEGMENT_PKG AS
2 /* $Header: glfcpsgb.pls 120.5 2008/06/11 16:44:26 dthakker ship $ */
3
4 -- Global Variable
5 g_debug_mode VARCHAR2(1) := 'N';
6
7 -- PROCEDURE
8 -- merge_child_ranges
9 -- PURPOSE
10 -- It will merge the child ranges of all parent values stored in
11 -- GL_REVAL_CHD_RANGES_GT for the passed segment and store the merged
12 -- child ranges back to GL_REVAL_CHD_RANGES_GT.
13 -- HISTORY
14 -- 07/29/03 L Poon Created
15 -- ARGUMENTS
16 -- fv_set_id Flex Value Set ID
17 -- debug_mode Debug Mode (Y or N)
18 -- NOTES
19 -- Before calling this procedure, insert all parent value(s) to be
20 -- processed to the temporary table GL_REVAL_CHD_RANGES_GT.
21
22
23 PROCEDURE merge_child_ranges(fv_set_id IN NUMBER,
24 debug_mode IN VARCHAR2) IS
25
26 p_fv_table FND_FLEX_VALIDATION_TABLES.application_table_name%TYPE;
27 p_fv_col FND_FLEX_VALIDATION_TABLES.value_column_name%TYPE;
28 p_fv_type FND_FLEX_VALUE_SETS.validation_type%TYPE;
29
30 v_CursorID INTEGER;
31 v_CursorSQL VARCHAR2(300);
32 v_detail_value FND_FLEX_VALUES.flex_value%TYPE;
33 v_dummy INTEGER;
34
35 p_parent_val GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE := NULL;
36 p_child_fv_low GL_REVAL_CHD_RANGES_GT.child_flex_value_low%TYPE := NULL;
37 p_child_fv_high GL_REVAL_CHD_RANGES_GT.child_flex_value_high%TYPE := NULL;
38 p_rowid ROWID := NULL;
39
40 p_old_parent_val GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE := NULL;
41 p_old_child_fv_low GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE := NULL;
42 p_old_child_fv_high GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE := NULL;
43 p_old_rowid ROWID := NULL;
44
45 p_rec_count NUMBER;
46 p_min_flex_val GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE;
47 p_max_flex_val GL_REVAL_CHD_RANGES_GT.parent_flex_value%TYPE;
48
49 p_changed_flag VARCHAR2(1) := 'N';
50 p_used_flag VARCHAR2(1) := 'N';
51 p_delete_flag VARCHAR2(1) := 'N';
52 p_summary_flag VARCHAR2(1) := 'N';
53
54
55 CURSOR child_range_c (c_fv_set_id IN NUMBER) IS
56 SELECT parent_flex_value
57 , child_flex_value_low
58 , child_flex_value_high
59 , rowid
60 FROM GL_REVAL_CHD_RANGES_GT
61 WHERE flex_value_set_id = c_fv_set_id
62 ORDER BY parent_flex_value
63 , NLSSORT(child_flex_value_low, 'NLS_SORT=BINARY')
64 , NLSSORT(child_flex_value_high, 'NLS_SORT=BINARY');
65
66 BEGIN
67
68 -- Initialize the variabels
69 IF (debug_mode = 'Y' or debug_mode = 'y') THEN
70 g_debug_mode := 'Y';
71 END IF;
72
73 IF g_debug_mode = 'Y' THEN
74 debug_msg('merge_child_ranges',
75 'fv_set_id='||to_char(fv_set_id)||' debug_mode='||debug_mode);
76 END IF;
77
78 -- Call the get_fv_tagble to get the flex value table name and its
79 -- flex value column name for the processed segment
80 get_fv_table(fv_set_id, p_fv_table, p_fv_col, p_fv_type);
81
82 -- Build the cursor SQL
83 v_CursorSQL := 'SELECT VAL.'||p_fv_col||' detail_value'||' FROM '
84 ||p_fv_table||' VAL' ||' WHERE VAL.'||p_fv_col
85 ||' BETWEEN :low AND :high';
86
87 IF p_fv_type <> 'F' THEN
88 v_CursorSQL := v_CursorSQL||' AND VAL.flex_value_set_id='
89 ||to_char(fv_set_id)||' AND VAL.summary_flag= :l_summary_flag';
90 END IF;
91
92 v_CursorSQL := v_CursorSQL
93 ||' ORDER BY NLSSORT(detail_value,''NLS_SORT=BINARY'')';
94
95 IF g_debug_mode = 'Y' THEN
96 debug_msg('merge_child_ranges', 'Cur SQL='||v_CursorSQL);
97 debug_msg('merge_child_ranges', 'Open cursor child_range_c loop');
98 END IF;
99 -- Open cursor
100 OPEN child_range_c (fv_set_id);
101
102 LOOP
103
104 FETCH child_range_c INTO p_parent_val,
105 p_child_fv_low,
106 p_child_fv_high,
107 p_rowid;
108 EXIT WHEN child_range_c%NOTFOUND;
109
110 IF g_debug_mode = 'Y' THEN
111 debug_msg('merge_child_ranges',
112 'p_parent_val='||p_parent_val||' p_child_fv_low='
113 ||p_child_fv_low||' p_child_fv_high='||p_child_fv_high);
114 debug_msg('merge_child_ranges',
115 'p_old_parent_val='||p_old_parent_val
116 ||' p_old_child_fv_low='||p_old_child_fv_low
117 ||' p_old_child_fv_high='||p_old_child_fv_high);
118 debug_msg('merge_child_ranges',
119 'p_changed_flag='||p_changed_flag||' p_used_flag='
120 ||p_used_flag||' p_delete_flag='||p_delete_flag
121 ||' v_detail_value='||v_detail_value);
122 END IF;
123
124 IF (p_old_parent_val IS NULL OR p_parent_val <> p_old_parent_val) THEN
125 IF g_debug_mode = 'Y' THEN
126 debug_msg('merge_child_ranges', 'Initial rec or different parent');
127 END IF;
128 IF (p_old_child_fv_high IS NOT NULL) THEN
129
130 IF p_changed_flag = 'Y' THEN
131 -- Update the old range if it is changed
132 UPDATE GL_REVAL_CHD_RANGES_GT
133 SET child_flex_value_high = p_old_child_fv_high
134 WHERE rowid = p_old_rowid;
135 END IF; -- IF p_changed_flag = 'Y' THEN
136
137 -- Close the detail_value cursor
138 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
139
140 END IF; -- IF (p_old_child_fv_high IS NOT NULL) THEN
141
142 -- Initialize the variables for the new parent value
143 p_used_flag := 'N';
144 p_changed_flag := 'N';
145 p_delete_flag := 'N';
146 v_detail_value := NULL;
147 p_summary_flag := 'N';
148
149 -- Call the get_min_max to get the record count, min and max child
150 -- flex value for the processed parent value
151 get_min_max(fv_set_id,
152 p_parent_val,
153 p_rec_count,
154 p_min_flex_val,
155 p_max_flex_val);
156
157 IF p_rec_count > 0 THEN
158 -- Open the cursor for processing
159 v_CursorID := DBMS_SQL.OPEN_CURSOR;
160
161 -- Parse the query
162 DBMS_SQL.PARSE(v_CursorID, v_CursorSQL, DBMS_SQL.v7);
163
164 -- Bind varibales
165 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':low', p_min_flex_val);
166 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':high', p_max_flex_val);
167 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':l_summary_flag', p_summary_flag);
168
169 -- Define output variable
170 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_detail_value, 150);
171
172 -- Execute the query
173 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
174
175 ELSE
176 IF g_debug_mode = 'Y' THEN
177 debug_msg('merge_child_ranges', 'No child ranges are found');
178 END IF;
179
180 END IF; -- IF p_rec_count > 0 THEN
181
182 -- Store the new range to the old range
183 p_old_parent_val := p_parent_val;
184 p_old_child_fv_low := p_child_fv_low;
185 p_old_child_fv_high := p_child_fv_high;
186 p_old_rowid := p_rowid;
187
188 ELSE
189
190 IF p_delete_flag = 'Y' THEN
191 IF g_debug_mode = 'Y' THEN
192 debug_msg('merge_child_ranges', 'Delete new range as no more detail val');
193 END IF;
194 -- Delete all remaining ranges with the same parent value when
195 -- the delete flag is set to Y because of no more detail values
196 DELETE FROM GL_REVAL_CHD_RANGES_GT
197 WHERE rowid = p_rowid;
198
199 ELSE
200 -- If the new range overlaps with the old range, merge them
201 IF (p_old_child_fv_high >= p_child_fv_low) THEN
202 IF g_debug_mode = 'Y' THEN
203 debug_msg('merge_child_ranges', 'Merge the ranges as they overlap');
204 END IF;
205
206 IF (p_child_fv_high > p_old_child_fv_high) THEN
207 -- Set the old range high to new range high and set the
208 -- p_changed_flag to Y to indicate the old range has been changed
209 p_old_child_fv_high := p_child_fv_high;
210 p_changed_flag := 'Y';
211
212 END IF; -- IF (p_child_fv_high > p_old_child_fv_high) THEN
213
214 -- Delete the new range since it has merged with the old range
215 DELETE FROM GL_REVAL_CHD_RANGES_GT
216 WHERE rowid = p_rowid;
217
218 ELSE
219 IF g_debug_mode = 'Y' THEN
220 debug_msg('merge_child_ranges', 'Two ranges do NOT overlap');
221 END IF;
222
223 LOOP -- This is the fetch loop for detail_value
224 IF (v_detail_value IS NULL) THEN
225 IF g_debug_mode = 'Y' THEN
226 debug_msg('merge_child_ranges', 'Fetch detail value');
227 END IF;
228
229 -- Fetch detail value from the cursor if it is NULL
230 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
231 IF g_debug_mode = 'Y' THEN
232 debug_msg('merge_child_ranges', 'No more detail value');
233 END IF;
234 -- No more detail value for this parent value
235
236 IF (p_changed_flag = 'Y' AND p_used_flag = 'Y') THEN
237 -- Update the table for the changed and used old range
238 UPDATE GL_REVAL_CHD_RANGES_GT
239 SET child_flex_value_high = p_old_child_fv_high
240 WHERE rowid = p_old_rowid;
241
242 ELSIF (p_used_flag = 'N') THEN
243 -- Delete the old range if it is not used
244 DELETE FROM GL_REVAL_CHD_RANGES_GT
245 WHERE rowid = p_old_rowid;
246
247 END IF; -- IF (p_changed_flag = 'Y' AND ...
248
249 -- Delete the new range as there are no more detail value
250 DELETE FROM GL_REVAL_CHD_RANGES_GT
251 WHERE rowid = p_rowid;
252
253 IF g_debug_mode = 'Y' THEN
254 debug_msg('merge_child_ranges', 'Set p_delete_flag to Y');
255 END IF;
256 -- Set p_delet_flag to Y to indicate to delete all remaining
257 -- child ranges with same parent value
258 p_delete_flag := 'Y';
259
260 -- Exit the detail_value loop and proceed for the next range
261 EXIT;
262
263 END IF; -- IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
264
265 -- Retrieve the detail value from the cursor
266 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_detail_value);
267
268 END IF; -- IF (v_detail_value IS NULL) THEN
269
270 IF (v_detail_value < p_old_child_fv_low) THEN
271 IF g_debug_mode = 'Y' THEN
272 debug_msg('merge_child_ranges', 'Detail val < old range');
273 END IF;
274
275 -- Set the detail value to NULL in order to fetch next detail
276 -- value to process
277 v_detail_value := NULL;
278
279 ELSIF (v_detail_value >= p_old_child_fv_low
280 AND v_detail_value <= p_old_child_fv_high) THEN
281 IF g_debug_mode = 'Y' THEN
282 debug_msg('merge_child_ranges', 'Detail val IN old range');
283 END IF;
284
285 -- The detail value is in the old range, so set it to NULL
286 -- in order to fetch next detail value to process and
287 -- set used flag to Y
288 p_used_flag := 'Y';
289 v_detail_value := NULL;
290
291 ELSIF (v_detail_value > p_old_child_fv_high) THEN
292 IF g_debug_mode = 'Y' THEN
293 debug_msg('merge_child_ranges', 'Detail value > old range');
294 END IF;
295 -- The detail value is beyond the old range
296
297 IF (p_changed_flag = 'Y' AND p_used_flag = 'Y') THEN
298 -- Update the table for the changed and used old range
299 UPDATE GL_REVAL_CHD_RANGES_GT
300 SET child_flex_value_high = p_old_child_fv_high
301 WHERE rowid = p_old_rowid;
302
303 ELSIF (p_used_flag = 'N') THEN
304 -- Delete the old range if it is not used
305 DELETE FROM GL_REVAL_CHD_RANGES_GT
306 WHERE rowid = p_old_rowid;
307
308 END IF; -- IF (p_changed_flag = 'Y' AND ...
309
310 -- Reset the variables for the new range
311 p_changed_flag := 'N';
312 p_used_flag := 'N';
313
314 -- If detail_value is between the new and old range, set it
315 -- to NULL in order to fetch next detail value to process.
316 -- If it is in or beyond the new range, we still need to
317 -- check it for the new range so we should not set
318 -- v_detail_value to NULL
319 IF (v_detail_value > p_old_child_fv_high
320 AND v_detail_value < p_child_fv_low) THEN
321 IF g_debug_mode = 'Y' THEN
322 debug_msg('merge_child_ranges',
323 'Detail val is between old and new ranges');
324 END IF;
325
326 v_detail_value := NULL;
327
328 END IF; -- IF (v_detail_value > p_old_child_fv_high ...
329
330 IF g_debug_mode = 'Y' THEN
331 debug_msg('merge_child_ranges', 'Set old range as new range');
332 END IF;
333 -- Store the new range to the old range
334 p_old_parent_val := p_parent_val;
335 p_old_child_fv_low := p_child_fv_low;
336 p_old_child_fv_high := p_child_fv_high;
337 p_old_rowid := p_rowid;
338
339 -- Exit the detail value loop in order to get next range
340 EXIT;
341
342 END IF; -- IF (v_detail_value >= p_old_child_fv_low ...
343 END LOOP; -- detail_value fetch loop
344 IF g_debug_mode = 'Y' THEN
345 debug_msg('merge_child_ranges', 'END LOOP for detail val');
346 END IF;
347
348 END IF; -- IF (p_old_child_fv_high >= p_child_fv_low) THEN
349 END IF; -- IF p_delete_flag = 'Y' THEN
350 END IF; -- IF (p_old_parent_val IS NULL ...
351 END LOOP; -- child_range_c cursor loop
352 IF g_debug_mode = 'Y' THEN
353 debug_msg('merge_child_ranges', 'END LOOP for child_range_c');
354 END IF;
355
356 IF (p_old_child_fv_high IS NOT NULL) THEN
357 IF g_debug_mode = 'Y' THEN
358 debug_msg('merge_child_ranges', 'Check the last range');
359 END IF;
360 IF p_changed_flag = 'Y' THEN
361 UPDATE GL_REVAL_CHD_RANGES_GT
362 SET child_flex_value_high = p_old_child_fv_high
363 WHERE rowid = p_old_rowid;
364 END IF; -- IF p_changed_flag = 'Y' THEN
365
366 -- Close the detail_value cursor
367 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
368
369 END IF; -- IF (p_old_child_fv_high IS NOT NULL) THEN
370
371 CLOSE child_range_c;
372
373 IF g_debug_mode = 'Y' THEN
374 debug_msg('merge_child_ranges', 'Complete successfully');
375 END IF;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
380 fnd_message.set_token('PROCEDURE',
381 'GL_PARENT_SEGMENT_PKG.MERGE_CHILD_RANGES');
382 RAISE_APPLICATION_ERROR(-20150, fnd_message.get||SQLERRM);
383 END merge_child_ranges;
384
385
386 -- PROCEDURE
387 -- get_min_max
388 -- PURPOSE
389 -- It will get the record count, the minimum and maximum child flex
390 -- values of the child ranges for the passed segment stored in
391 -- GL_REVAL_CHD_RANGES_GT.
392 -- HISTORY
393 -- 07/29/03 L Poon Created
394 -- ARGUMENTS
395 -- fv_set_id Flex Value Set ID
396 -- parent_val Parent Flex Value to be processed
397 -- rec_count Record Count
398 -- min_val Minimum Child Flex Value
399 -- max_val Maximum Child Flex Value
400 -- NOTES
401 --
402 PROCEDURE get_min_max(fv_set_id IN NUMBER,
403 parent_val IN VARCHAR2,
404 rec_count OUT NOCOPY NUMBER,
405 min_val OUT NOCOPY VARCHAR2,
406 max_val OUT NOCOPY VARCHAR2) IS
407 BEGIN
408
409 IF g_debug_mode = 'Y' THEN
410 debug_msg('get_min_max',
411 'fv_set_id='||to_char(fv_set_id)||' parent_val='||parent_val);
412 END IF;
413
414 BEGIN
415 SELECT count(*)
416 , min(child_flex_value_low)
417 , max(child_flex_value_high)
418 INTO rec_count
419 , min_val
420 , max_val
421 FROM GL_REVAL_CHD_RANGES_GT
422 WHERE flex_value_set_id = fv_set_id
423 AND parent_flex_value = parent_val;
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 rec_count := 0;
427 min_val := NULL;
428 max_val := NULL;
429 WHEN OTHERS THEN
430 fnd_message.set_name('SQLGL', 'MRC_TABLE_ERROR');
431 fnd_message.set_token('MODULE', 'GL_PARENT_SEGMENT_PKG.GET_MIN_MAX');
432 fnd_message.set_token('TABLE', 'GL_REVAL_CHD_RANGES_GT');
433 RAISE_APPLICATION_ERROR(-20160, fnd_message.get||SQLERRM);
434 END;
435
436 IF g_debug_mode = 'Y' THEN
437 debug_msg('get_min_max',
438 'rec_count='||to_char(rec_count)||' min_val='||min_val
439 ||' max_val='||max_val);
440 END IF;
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
445 fnd_message.set_token('PROCEDURE', 'GL_PARENT_SEGMENT_PKG.GET_MIN_MAX');
446 RAISE_APPLICATION_ERROR(-20200, fnd_message.get||SQLERRM);
447 END get_min_max;
448
449
450 -- PROCEDURE
451 -- get_fv_table
452 -- PURPOSE
453 -- It will get the name of the table which contains the flex values for
454 -- the passed segment.
455 -- HISTORY
456 -- 07/29/03 L Poon Created
457 -- ARGUMENTS
458 -- fv_set_id Flex Value Set ID
459 -- fv_table Flex Value Table Name
460 -- fv_col Flex Value Column Name
461 -- fv_type Flex Value Validation Type
462 -- NOTES
463 --
464 PROCEDURE get_fv_table(fv_set_id IN NUMBER,
465 fv_table OUT NOCOPY VARCHAR2,
466 fv_col OUT NOCOPY VARCHAR2,
467 fv_type OUT NOCOPY VARCHAR2) IS
468 BEGIN
469 IF g_debug_mode = 'Y' THEN
470 debug_msg('get_fv_table', 'fv_set_id='||to_char(fv_set_id));
471 END IF;
472
473 BEGIN
474 SELECT nvl(fvt.application_table_name, 'FND_FLEX_VALUES')
475 , nvl(fvt.value_column_name, 'FLEX_VALUE')
476 , fvs.validation_type
477 INTO fv_table
478 , fv_col
479 , fv_type
480 FROM fnd_flex_validation_tables fvt
481 , fnd_flex_value_sets fvs
482 WHERE fvs.flex_value_set_id = fv_set_id
483 AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id;
484 EXCEPTION
485 WHEN OTHERS THEN
486 fnd_message.set_name('SQLGL', 'MRC_TABLE_ERROR');
487 fnd_message.set_token('MODULE', 'GL_PARENT_SEGMENT_PKG.GET_FV_TABLE');
488 fnd_message.set_token('TABLE', 'FND_FLEX_VALUE_SETS');
489 RAISE_APPLICATION_ERROR(-20210, fnd_message.get||SQLERRM);
490 END;
491
492 IF g_debug_mode = 'Y' THEN
493 debug_msg('get_fv_table',
494 'fv_table='||fv_table||' fv_col='||fv_col||' fv_type='
495 ||fv_type);
496 END IF;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
501 fnd_message.set_token('PROCEDURE', 'GL_PARENT_SEGMENT_PKG.GET_FV_TABLE');
502 RAISE_APPLICATION_ERROR(-20250, fnd_message.get||SQLERRM);
503 END get_fv_table;
504
505 -- PROCEDURE
506 -- check_overlapping
507 -- PURPOSE
508 -- It will check whether any expanded and merged account ranges
509 -- in GL_REVAL_EXP_RANGES_GT overlap
510 -- HISTORY
511 -- 08/29/03 L Poon Created
512 -- ARGUMENTS
513 -- debug_mode Debug Mode (Y or N)
514 -- is_overlapping Indicate if any ranges overlap (Y or N)
515 PROCEDURE check_overlapping(debug_mode IN VARCHAR2,
516 is_overlapping OUT NOCOPY VARCHAR2) IS
517
518 CURSOR acct_range_c IS
519 SELECT /*+ cardinality(er1 10) */ 1
520 FROM GL_REVAL_EXP_RANGES_GT er1
521 WHERE EXISTS(
522 SELECT /*+ no_unnest index(er2) */ 'Overlapping'
523 FROM gl_reval_exp_ranges_gt er2
524 WHERE er2.ROWID <> er1.rowid
525 AND NVL(er2.segment30_low, 'X') <= NVL(er1.segment30_high, 'X')
526 AND NVL(er2.segment30_high, 'X') >= NVL(er1.segment30_low, 'X')
527 AND NVL(er2.segment29_low, 'X') <= NVL(er1.segment29_high, 'X')
528 AND NVL(er2.segment29_high, 'X') >= NVL(er1.segment29_low, 'X')
529 AND NVL(er2.segment28_low, 'X') <= NVL(er1.segment28_high, 'X')
530 AND NVL(er2.segment28_high, 'X') >= NVL(er1.segment28_low, 'X')
531 AND NVL(er2.segment27_low, 'X') <= NVL(er1.segment27_high, 'X')
532 AND NVL(er2.segment27_high, 'X') >= NVL(er1.segment27_low, 'X')
533 AND NVL(er2.segment26_low, 'X') <= NVL(er1.segment26_high, 'X')
534 AND NVL(er2.segment26_high, 'X') >= NVL(er1.segment26_low, 'X')
535 AND NVL(er2.segment25_low, 'X') <= NVL(er1.segment25_high, 'X')
536 AND NVL(er2.segment25_high, 'X') >= NVL(er1.segment25_low, 'X')
537 AND NVL(er2.segment24_low, 'X') <= NVL(er1.segment24_high, 'X')
538 AND NVL(er2.segment24_high, 'X') >= NVL(er1.segment24_low, 'X')
539 AND NVL(er2.segment23_low, 'X') <= NVL(er1.segment23_high, 'X')
540 AND NVL(er2.segment23_high, 'X') >= NVL(er1.segment23_low, 'X')
541 AND NVL(er2.segment22_low, 'X') <= NVL(er1.segment22_high, 'X')
542 AND NVL(er2.segment22_high, 'X') >= NVL(er1.segment22_low, 'X')
543 AND NVL(er2.segment21_low, 'X') <= NVL(er1.segment21_high, 'X')
544 AND NVL(er2.segment21_high, 'X') >= NVL(er1.segment21_low, 'X')
545 AND NVL(er2.segment20_low, 'X') <= NVL(er1.segment20_high, 'X')
546 AND NVL(er2.segment20_high, 'X') >= NVL(er1.segment20_low, 'X')
547 AND NVL(er2.segment19_low, 'X') <= NVL(er1.segment19_high, 'X')
548 AND NVL(er2.segment19_high, 'X') >= NVL(er1.segment19_low, 'X')
549 AND NVL(er2.segment18_low, 'X') <= NVL(er1.segment18_low, 'X')
550 AND NVL(er2.segment18_high, 'X') >= NVL(er1.segment18_low, 'X')
551 AND NVL(er2.segment17_low, 'X') <= NVL(er1.segment17_high, 'X')
552 AND NVL(er2.segment17_high, 'X') >= NVL(er1.segment17_low, 'X')
553 AND NVL(er2.segment16_low, 'X') <= NVL(er1.segment16_high, 'X')
554 AND NVL(er2.segment16_high, 'X') >= NVL(er1.segment16_low, 'X')
555 AND NVL(er2.segment15_low, 'X') <= NVL(er1.segment15_high, 'X')
556 AND NVL(er2.segment15_high, 'X') >= NVL(er1.segment15_low, 'X')
557 AND NVL(er2.segment14_low, 'X') <= NVL(er1.segment14_high, 'X')
558 AND NVL(er2.segment14_high, 'X') >= NVL(er1.segment14_low, 'X')
559 AND NVL(er2.segment13_low, 'X') <= NVL(er1.segment13_high, 'X')
560 AND NVL(er2.segment13_high, 'X') >= NVL(er1.segment13_low, 'X')
561 AND NVL(er2.segment12_low, 'X') <= NVL(er1.segment12_high, 'X')
562 AND NVL(er2.segment12_high, 'X') >= NVL(er1.segment12_low, 'X')
563 AND NVL(er2.segment11_low, 'X') <= NVL(er1.segment11_high, 'X')
564 AND NVL(er2.segment11_high, 'X') >= NVL(er1.segment11_low, 'X')
565 AND NVL(er2.segment10_low, 'X') <= NVL(er1.segment10_high, 'X')
566 AND NVL(er2.segment10_high, 'X') >= NVL(er1.segment10_low, 'X')
567 AND NVL(er2.segment9_low, 'X') <= NVL(er1.segment9_high, 'X')
568 AND NVL(er2.segment9_high, 'X') >= NVL(er1.segment9_low, 'X')
569 AND NVL(er2.segment8_low, 'X') <= NVL(er1.segment8_high, 'X')
570 AND NVL(er2.segment8_high, 'X') >= NVL(er1.segment8_low, 'X')
571 AND NVL(er2.segment7_low, 'X') <= NVL(er1.segment7_high, 'X')
572 AND NVL(er2.segment7_high, 'X') >= NVL(er1.segment7_low, 'X')
573 AND NVL(er2.segment6_low, 'X') <= NVL(er1.segment6_high, 'X')
574 AND NVL(er2.segment6_high, 'X') >= NVL(er1.segment6_low, 'X')
575 AND NVL(er2.segment5_low, 'X') <= NVL(er1.segment5_high, 'X')
576 AND NVL(er2.segment5_high, 'X') >= NVL(er1.segment5_low, 'X')
577 AND NVL(er2.segment4_low, 'X') <= NVL(er1.segment4_high, 'X')
578 AND NVL(er2.segment4_high, 'X') >= NVL(er1.segment4_low, 'X')
579 AND NVL(er2.segment3_low, 'X') <= NVL(er1.segment3_high, 'X')
580 AND NVL(er2.segment3_high, 'X') >= NVL(er1.segment3_low, 'X')
581 AND NVL(er2.segment2_low, 'X') <= NVL(er1.segment2_high, 'X')
582 AND NVL(er2.segment2_high, 'X') >= NVL(er1.segment2_low, 'X')
583 AND NVL(er2.segment1_low, 'X') <= NVL(er1.segment1_high, 'X')
584 AND NVL(er2.segment1_high, 'X') >= NVL(er1.segment1_low, 'X'))
585 and rownum = 1;
586
587 -- Low segments
588
589 l_rowid VARCHAR2(100); -- ROW ID
590 l_overlap_flag VARCHAR2(100);
591 BEGIN
592
593 -- Initialize the variabels
594 IF (debug_mode = 'Y' or debug_mode = 'y') THEN
595 g_debug_mode := 'Y';
596 END IF;
597 is_overlapping := 'N';
598
599 IF g_debug_mode = 'Y' THEN
600 debug_msg('check_overlapping', 'debug_mode='||debug_mode);
601 debug_msg('check_overlapping', 'Open cursor - acct_range_c');
602 END IF;
603
604 -- Open cursor
605 OPEN acct_range_c;
606
607 FETCH acct_range_c INTO l_overlap_flag;
608
609 IF acct_range_c%FOUND THEN
610 is_overlapping := 'Y';
611 END IF;
612 CLOSE acct_range_c;
613
614 IF (is_overlapping = 'Y')
615 THEN
616 IF g_debug_mode = 'Y' THEN
617 debug_msg('check_overlapping',
618 ' => Return as this range overlaps with other ranges');
619 END IF;
620
621 RETURN;
622 END IF;
623
624 IF g_debug_mode = 'Y' THEN
625 debug_msg('check_overlapping', 'Finish checking all the ranges');
626 END IF;
627
628 EXCEPTION
629 WHEN OTHERS THEN
630 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
631 fnd_message.set_token('PROCEDURE', 'GL_PARENT_SEGMENT_PKG.CHECK_OVERLAPPING');
632 RAISE_APPLICATION_ERROR(-20300, fnd_message.get||SQLERRM);
633 END check_overlapping;
634
635 -- PROCEDURE
636 -- debug_msg
637 -- PURPOSE
638 -- It will print the debug message
639 -- HISTORY
640 -- 07/29/03 L Poon Created
641 -- ARGUMENTS
642 -- name Procedure/Function name
643 -- msg Debug Message
644 PROCEDURE debug_msg(name IN VARCHAR2,
645 msg IN VARCHAR2) IS
646 BEGIN
647
648 IF g_debug_mode = 'Y' THEN
649 -- DBMS_OUTPUT.PUT_LINE(name||'():'||msg);
650 NULL;
651 END IF;
652
653 EXCEPTION
654 WHEN OTHERS THEN
655 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
656 fnd_message.set_token('PROCEDURE', 'GL_PARENT_SEGMENT_PKG.DEBUG_MSG');
657 RAISE_APPLICATION_ERROR(-20350, fnd_message.get||SQLERRM);
658 END debug_msg;
659
660 END GL_PARENT_SEGMENT_PKG;
[Home] [Help]