1 PACKAGE BODY GL_CARRYFORWARD_RANGES_PKG as
2 /* $Header: glicfrab.pls 120.2 2005/05/05 01:03:39 kvora ship $ */
3
4 FUNCTION get_unique_id RETURN NUMBER IS
5 CURSOR get_new_id IS
6 SELECT gl_carryforward_ranges_s.NEXTVAL
7 FROM sys.dual;
8 new_id number;
9 BEGIN
10 OPEN get_new_id;
11 FETCH get_new_id INTO new_id;
12
13 IF get_new_id%FOUND THEN
14 CLOSE get_new_id;
15 return(new_id);
16 ELSE
17 CLOSE get_new_id;
18 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
19 fnd_message.set_token('SEQUENCE', 'GL_CARRYFORWARD_RANGES_S');
20 app_exception.raise_exception;
21 END IF;
22
23 EXCEPTION
24 WHEN app_exceptions.application_exception THEN
25 RAISE;
26 WHEN OTHERS THEN
27 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
28 fnd_message.set_token('PROCEDURE', 'gl_carryforward_ranges.get_unique_id');
29 RAISE;
30 END get_unique_id;
31
32
33
34 PROCEDURE check_overlapping(x_carryforward_range_id IN NUMBER,
35 x_segment1_low IN VARCHAR2,
36 x_segment1_high IN VARCHAR2,
37 x_segment2_low IN VARCHAR2,
38 x_segment2_high IN VARCHAR2,
39 x_segment3_low IN VARCHAR2,
40 x_segment3_high IN VARCHAR2,
41 x_segment4_low IN VARCHAR2,
42 x_segment4_high IN VARCHAR2,
43 x_segment5_low IN VARCHAR2,
44 x_segment5_high IN VARCHAR2,
45 x_segment6_low IN VARCHAR2,
46 x_segment6_high IN VARCHAR2,
47 x_segment7_low IN VARCHAR2,
48 x_segment7_high IN VARCHAR2,
49 x_segment8_low IN VARCHAR2,
50 x_segment8_high IN VARCHAR2,
51 x_segment9_low IN VARCHAR2,
52 x_segment9_high IN VARCHAR2,
53 x_segment10_low IN VARCHAR2,
54 x_segment10_high IN VARCHAR2,
55 x_segment11_low IN VARCHAR2,
56 x_segment11_high IN VARCHAR2,
57 x_segment12_low IN VARCHAR2,
58 x_segment12_high IN VARCHAR2,
59 x_segment13_low IN VARCHAR2,
60 x_segment13_high IN VARCHAR2,
61 x_segment14_low IN VARCHAR2,
62 x_segment14_high IN VARCHAR2,
63 x_segment15_low IN VARCHAR2,
64 x_segment15_high IN VARCHAR2,
65 x_segment16_low IN VARCHAR2,
66 x_segment16_high IN VARCHAR2,
67 x_segment17_low IN VARCHAR2,
68 x_segment17_high IN VARCHAR2,
69 x_segment18_low IN VARCHAR2,
70 x_segment18_high IN VARCHAR2,
71 x_segment19_low IN VARCHAR2,
72 x_segment19_high IN VARCHAR2,
73 x_segment20_low IN VARCHAR2,
74 x_segment20_high IN VARCHAR2,
75 x_segment21_low IN VARCHAR2,
76 x_segment21_high IN VARCHAR2,
77 x_segment22_low IN VARCHAR2,
78 x_segment22_high IN VARCHAR2,
79 x_segment23_low IN VARCHAR2,
80 x_segment23_high IN VARCHAR2,
81 x_segment24_low IN VARCHAR2,
82 x_segment24_high IN VARCHAR2,
83 x_segment25_low IN VARCHAR2,
84 x_segment25_high IN VARCHAR2,
85 x_segment26_low IN VARCHAR2,
86 x_segment26_high IN VARCHAR2,
87 x_segment27_low IN VARCHAR2,
88 x_segment27_high IN VARCHAR2,
89 x_segment28_low IN VARCHAR2,
90 x_segment28_high IN VARCHAR2,
91 x_segment29_low IN VARCHAR2,
92 x_segment29_high IN VARCHAR2,
93 x_segment30_low IN VARCHAR2,
94 x_segment30_high IN VARCHAR2,
95 row_id VARCHAR2 ) IS
96 CURSOR chk_overlapping IS
97 SELECT 'Overlapping'
98 FROM GL_CARRYFORWARD_RANGES
99 WHERE carryforward_range_id = x_carryforward_range_id AND
100 (NVL(SEGMENT30_LOW,'X') <= NVL(X_SEGMENT30_HIGH,'X') AND
101 NVL(SEGMENT30_HIGH,'X') >= NVL(X_SEGMENT30_LOW,'X')) AND
102 NVL(SEGMENT29_LOW,'X') <= NVL(X_SEGMENT29_HIGH,'X') AND
103 NVL(SEGMENT29_HIGH,'X') >= NVL(X_SEGMENT29_LOW,'X') AND
104 NVL(SEGMENT28_LOW,'X') <= NVL(X_SEGMENT28_HIGH,'X') AND
105 NVL(SEGMENT28_HIGH,'X') >= NVL(X_SEGMENT28_LOW,'X') AND
106 NVL(SEGMENT27_LOW,'X') <= NVL(X_SEGMENT27_HIGH,'X') AND
107 NVL(SEGMENT27_HIGH,'X') >= NVL(X_SEGMENT27_LOW,'X') AND
108 NVL(SEGMENT26_LOW,'X') <= NVL(X_SEGMENT26_HIGH,'X') AND
109 NVL(SEGMENT26_HIGH,'X') >= NVL(X_SEGMENT26_LOW,'X') AND
110 NVL(SEGMENT25_LOW,'X') <= NVL(X_SEGMENT25_HIGH,'X') AND
111 NVL(SEGMENT25_HIGH,'X') >= NVL(X_SEGMENT25_LOW,'X') AND
112 NVL(SEGMENT24_LOW,'X') <= NVL(X_SEGMENT24_HIGH,'X') AND
113 NVL(SEGMENT24_HIGH,'X') >= NVL(X_SEGMENT24_LOW,'X') AND
114 NVL(SEGMENT23_LOW,'X') <= NVL(X_SEGMENT23_HIGH,'X') AND
115 NVL(SEGMENT23_HIGH,'X') >= NVL(X_SEGMENT23_LOW,'X') AND
116 NVL(SEGMENT22_LOW,'X') <= NVL(X_SEGMENT22_HIGH,'X') AND
117 NVL(SEGMENT22_HIGH,'X') >= NVL(X_SEGMENT22_LOW,'X') AND
118 NVL(SEGMENT21_LOW,'X') <= NVL(X_SEGMENT21_HIGH,'X') AND
119 NVL(SEGMENT21_HIGH,'X') >= NVL(X_SEGMENT21_LOW,'X') AND
120 NVL(SEGMENT20_LOW,'X') <= NVL(X_SEGMENT20_HIGH,'X') AND
121 NVL(SEGMENT20_HIGH,'X') >= NVL(X_SEGMENT20_LOW,'X') AND
122 NVL(SEGMENT19_LOW,'X') <= NVL(X_SEGMENT19_HIGH,'X') AND
123 NVL(SEGMENT19_HIGH,'X') >= NVL(X_SEGMENT19_LOW,'X') AND
124 NVL(SEGMENT18_LOW,'X') <= NVL(X_SEGMENT18_HIGH,'X') AND
125 NVL(SEGMENT18_HIGH,'X') >= NVL(X_SEGMENT18_LOW,'X') AND
126 NVL(SEGMENT17_LOW,'X') <= NVL(X_SEGMENT17_HIGH,'X') AND
127 NVL(SEGMENT17_HIGH,'X') >= NVL(X_SEGMENT17_LOW,'X') AND
128 NVL(SEGMENT16_LOW,'X') <= NVL(X_SEGMENT16_HIGH,'X') AND
129 NVL(SEGMENT16_HIGH,'X') >= NVL(X_SEGMENT16_LOW,'X') AND
130 NVL(SEGMENT15_LOW,'X') <= NVL(X_SEGMENT15_HIGH,'X') AND
131 NVL(SEGMENT15_HIGH,'X') >= NVL(X_SEGMENT15_LOW,'X') AND
132 NVL(SEGMENT14_LOW,'X') <= NVL(X_SEGMENT14_HIGH,'X') AND
133 NVL(SEGMENT14_HIGH,'X') >= NVL(X_SEGMENT14_LOW,'X') AND
134 NVL(SEGMENT13_LOW,'X') <= NVL(X_SEGMENT13_HIGH,'X') AND
135 NVL(SEGMENT13_HIGH,'X') >= NVL(X_SEGMENT13_LOW,'X') AND
136 NVL(SEGMENT12_LOW,'X') <= NVL(X_SEGMENT12_HIGH,'X') AND
137 NVL(SEGMENT12_HIGH,'X') >= NVL(X_SEGMENT12_LOW,'X') AND
138 NVL(SEGMENT11_LOW,'X') <= NVL(X_SEGMENT11_HIGH,'X') AND
139 NVL(SEGMENT11_HIGH,'X') >= NVL(X_SEGMENT11_LOW,'X') AND
140 NVL(SEGMENT10_LOW,'X') <= NVL(X_SEGMENT10_HIGH,'X') AND
141 NVL(SEGMENT10_HIGH,'X') >= NVL(X_SEGMENT10_LOW,'X') AND
142 NVL(SEGMENT9_LOW,'X') <= NVL(X_SEGMENT9_HIGH,'X') AND
143 NVL(SEGMENT9_HIGH,'X') >= NVL(X_SEGMENT9_LOW,'X') AND
144 NVL(SEGMENT8_LOW,'X') <= NVL(X_SEGMENT8_HIGH,'X') AND
145 NVL(SEGMENT8_HIGH,'X') >= NVL(X_SEGMENT8_LOW,'X') AND
146 NVL(SEGMENT7_LOW,'X') <= NVL(X_SEGMENT7_HIGH,'X') AND
147 NVL(SEGMENT7_HIGH,'X') >= NVL(X_SEGMENT7_LOW,'X') AND
148 NVL(SEGMENT6_LOW,'X') <= NVL(X_SEGMENT6_HIGH,'X') AND
149 NVL(SEGMENT6_HIGH,'X') >= NVL(X_SEGMENT6_LOW,'X') AND
150 NVL(SEGMENT5_LOW,'X') <= NVL(X_SEGMENT5_HIGH,'X') AND
151 NVL(SEGMENT5_HIGH,'X') >= NVL(X_SEGMENT5_LOW,'X') AND
152 NVL(SEGMENT4_LOW,'X') <= NVL(X_SEGMENT4_HIGH,'X') AND
153 NVL(SEGMENT4_HIGH,'X') >= NVL(X_SEGMENT4_LOW,'X') AND
154 NVL(SEGMENT3_LOW,'X') <= NVL(X_SEGMENT3_HIGH,'X') AND
155 NVL(SEGMENT3_HIGH,'X') >= NVL(X_SEGMENT3_LOW,'X') AND
156 NVL(SEGMENT2_LOW,'X') <= NVL(X_SEGMENT2_HIGH,'X') AND
157 NVL(SEGMENT2_HIGH,'X') >= NVL(X_SEGMENT2_LOW,'X') AND
158 NVL(SEGMENT1_LOW,'X') <= NVL(X_SEGMENT1_HIGH,'X') AND
159 NVL(SEGMENT1_HIGH,'X') >= NVL(X_SEGMENT1_LOW,'X') AND
160 (row_id is NULL OR rowid <> row_id );
161 dummy VARCHAR2(100);
162 BEGIN
163 OPEN chk_overlapping;
164 FETCH chk_overlapping INTO dummy;
165
166 IF chk_overlapping%FOUND THEN
167 CLOSE chk_overlapping;
168 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_CARRYFWD_RANGE');
169 app_exception.raise_exception;
170 END IF;
171
172 CLOSE chk_overlapping;
173
174 EXCEPTION
175 WHEN app_exceptions.application_exception THEN
176 RAISE;
177 WHEN OTHERS THEN
178 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
179 fnd_message.set_token('PROCEDURE', 'GL_CARRYFORWARD_RANGES_PKG.check_overlapping');
180 RAISE;
181 END check_overlapping;
182
183 END GL_CARRYFORWARD_RANGES_PKG;
[Home] [Help]