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
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
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
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:
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;
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;
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:
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;
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;
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';
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';
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');
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:
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
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
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
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:
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');
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
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:
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:
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
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
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