1 PACKAGE BODY GL_CONS_FLEX_HIER_PKG as
2 /* $Header: glicocrb.pls 120.7 2005/05/05 01:04:39 kvora ship $ */
3
4 --
5 -- PUBLIC PROCEDURES
6 --
7
8 FUNCTION Overlap(X_Rowid VARCHAR2,
9 X_Segment_Map_Id NUMBER,
10 X_Coa_Mapping_Id NUMBER,
11 X_To_Value_Set_Id NUMBER,
12 X_From_Value_Set_Id NUMBER,
13 X_Segment_Map_Type VARCHAR2,
14 X_To_Application_Column_Name VARCHAR2,
15 X_From_Application_Column_Name VARCHAR2,
16 X_Parent_Flex_Value VARCHAR2,
17 X_Child_Flex_Value_Low VARCHAR2,
18 X_Child_Flex_Value_High VARCHAR2
19 ) RETURN NUMBER IS
20
21 -- The first cursor catches overlaps to the same target
22 CURSOR C1 IS SELECT 'Overlaps'
23 FROM gl_cons_flex_hierarchies cfh, gl_cons_segment_map csm
24 WHERE csm.coa_mapping_id = X_Coa_Mapping_Id
25 AND csm.single_value = X_Parent_Flex_Value
26 AND csm.to_value_set_id = X_To_Value_Set_Id
27 AND csm.from_value_set_id = X_From_Value_Set_Id
28 AND csm.to_application_column_name = X_to_application_column_name
29 AND csm.from_application_column_name = X_from_application_column_name
30 AND csm.segment_map_type = 'R'
31 AND csm.segment_map_id = cfh.segment_map_id
32 AND ((cfh.child_flex_value_low between
33 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
34 OR
35 (cfh.child_flex_value_high between
36 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
37 OR
38 (X_Child_Flex_Value_Low between
39 cfh.child_flex_value_low and cfh.child_flex_value_high)
40 OR
41 (X_Child_Flex_Value_High between
42 cfh.child_flex_value_low and cfh.child_flex_value_high))
43 AND ROWIDTOCHAR(cfh.rowid) <> nvl(X_Rowid,'x')
44 UNION
45 SELECT 'Overlaps'
46 FROM fnd_flex_value_hierarchies fvh, gl_cons_segment_map csm
47 WHERE csm.coa_mapping_id = X_Coa_Mapping_Id
48 AND csm.single_value = X_Parent_Flex_Value
49 AND csm.to_value_set_id = X_To_Value_Set_Id
50 AND csm.from_value_set_id = X_From_Value_Set_Id
51 AND csm.to_application_column_name = X_To_Application_Column_Name
52 AND csm.from_application_column_name = X_From_Application_Column_Name
53 AND csm.segment_map_type = 'P'
54 AND fvh.flex_value_set_id = X_From_Value_Set_Id
55 AND csm.parent_rollup_value = fvh.parent_flex_value
56 AND ((fvh.child_flex_value_low between
57 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
58 OR
59 (fvh.child_flex_value_high between
60 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
61 OR
62 (X_Child_Flex_Value_Low between
63 fvh.child_flex_value_low and fvh.child_flex_value_high)
64 OR
65 (X_Child_Flex_Value_High between
66 fvh.child_flex_value_low and fvh.child_flex_value_high))
67 ;
68
69 -- The second cursor catches overlaps to different targets
70 CURSOR C2 IS SELECT 'Overlaps'
71 FROM gl_cons_flex_hierarchies cfh, gl_cons_segment_map csm
72 WHERE csm.coa_mapping_id = X_Coa_Mapping_Id
73 AND csm.to_value_set_id = X_To_Value_Set_Id
74 AND csm.from_value_set_id = X_From_Value_Set_Id
75 AND csm.to_application_column_name = X_to_application_column_name
76 AND csm.from_application_column_name = X_from_application_column_name
77 AND csm.segment_map_type = 'R'
78 AND csm.segment_map_id = cfh.segment_map_id
79 AND ((cfh.child_flex_value_low between
80 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
81 OR
82 (cfh.child_flex_value_high between
83 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
84 OR
85 (X_Child_Flex_Value_Low between
86 cfh.child_flex_value_low and cfh.child_flex_value_high)
87 OR
88 (X_Child_Flex_Value_High between
89 cfh.child_flex_value_low and cfh.child_flex_value_high))
90 AND ROWIDTOCHAR(cfh.rowid) <> nvl(X_Rowid,'x')
91 UNION
92 SELECT 'Overlaps'
93 FROM fnd_flex_value_hierarchies fvh, gl_cons_segment_map csm
94 WHERE csm.coa_mapping_id = X_Coa_Mapping_Id
95 AND csm.to_value_set_id = X_To_Value_Set_Id
96 AND csm.from_value_set_id = X_From_Value_Set_Id
97 AND csm.to_application_column_name = X_To_Application_Column_Name
98 AND csm.from_application_column_name = X_From_Application_Column_Name
99 AND csm.segment_map_type = 'P'
100 AND fvh.flex_value_set_id = X_From_Value_Set_Id
101 AND csm.parent_rollup_value = fvh.parent_flex_value
102 AND ((fvh.child_flex_value_low between
103 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
104 OR
105 (fvh.child_flex_value_high between
106 X_Child_Flex_Value_Low and X_Child_Flex_Value_High)
107 OR
108 (X_Child_Flex_Value_Low between
109 fvh.child_flex_value_low and fvh.child_flex_value_high)
110 OR
111 (X_Child_Flex_Value_High between
112 fvh.child_flex_value_low and fvh.child_flex_value_high))
113 ;
114
115 V1 VARCHAR2(21);
116
117 BEGIN
118 IF ( X_Segment_Map_Type IN ( 'R', 'P' ) ) THEN
119 OPEN C1;
120 FETCH C1 INTO V1;
121 IF (C1%FOUND) THEN
122 CLOSE C1;
123 fnd_message.set_name('SQLGL', 'GL_OVERLAPPING_ROLLUP_RANGES');
124 app_exception.raise_exception;
125 END IF;
126 CLOSE C1;
127
128 OPEN C2;
129 FETCH C2 INTO V1;
130 IF (C2%FOUND) THEN
131 CLOSE C2;
132 return(1);
133 END IF;
134 CLOSE C2;
135 END IF;
136 return(0);
137 END Overlap;
138
139 PROCEDURE Count_Ranges(X_Segment_Map_Id NUMBER) IS
140
141 CURSOR C2 IS SELECT '1' FROM gl_cons_flex_hierarchies
142 WHERE segment_map_id = X_Segment_Map_Id;
143
144 Range_Count VARCHAR2(2);
145
146 BEGIN
147 OPEN C2;
148 FETCH C2 INTO Range_Count;
149 IF (Range_Count < 2) THEN
150 CLOSE C2;
151 fnd_message.set_name('SQLGL','GL_ENTER_SEGMENT_RANGES');
152 app_exception.raise_exception;
153 END IF;
154 END Count_Ranges;
155
156 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
157 X_Segment_Map_Id IN OUT NOCOPY NUMBER,
158 X_Parent_Flex_Value VARCHAR2,
159 X_Child_Flex_Value_Low VARCHAR2,
160 X_Child_Flex_Value_High VARCHAR2,
161 X_Last_Update_Date DATE,
162 X_Last_Updated_By NUMBER,
163 X_Creation_Date DATE,
164 X_Created_By NUMBER,
165 X_Last_Update_Login NUMBER,
166 X_Attribute1 VARCHAR2,
167 X_Attribute2 VARCHAR2,
168 X_Attribute3 VARCHAR2,
169 X_Attribute4 VARCHAR2,
170 X_Attribute5 VARCHAR2,
171 X_Context VARCHAR2
172 ) IS
173 CURSOR C IS SELECT rowid FROM gl_cons_flex_hierarchies
174
175 WHERE segment_map_id = X_Segment_Map_Id;
176 CURSOR C2 IS SELECT gl_cons_segment_map_s.nextval FROM dual;
177 BEGIN
178
179 if (X_Segment_Map_Id is NULL) then
180 OPEN C2;
181 FETCH C2 INTO X_Segment_Map_Id;
182 CLOSE C2;
183 end if;
184
185 INSERT INTO gl_cons_flex_hierarchies(
186 segment_map_id,
187 parent_flex_value,
188 child_flex_value_low,
189 child_flex_value_high,
190 last_update_date,
191 last_updated_by,
192 creation_date,
193 created_by,
194 last_update_login,
195 attribute1,
196 attribute2,
197 attribute3,
198 attribute4,
199 attribute5,
200 context
201 ) VALUES (
202 X_Segment_Map_Id,
203 X_Parent_Flex_Value,
204 X_Child_Flex_Value_Low,
205 X_Child_Flex_Value_High,
206 X_Last_Update_Date,
207 X_Last_Updated_By,
208 X_Creation_Date,
209 X_Created_By,
210 X_Last_Update_Login,
211 X_Attribute1,
212 X_Attribute2,
213 X_Attribute3,
214 X_Attribute4,
215 X_Attribute5,
216 X_Context
217 );
218
219 OPEN C;
220 FETCH C INTO X_Rowid;
221 if (C%NOTFOUND) then
222 CLOSE C;
223 RAISE NO_DATA_FOUND;
224 end if;
225 CLOSE C;
226 END Insert_Row;
227
228 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
229 X_Segment_Map_Id NUMBER,
230 X_Parent_Flex_Value VARCHAR2,
231 X_Child_Flex_Value_Low VARCHAR2,
232 X_Child_Flex_Value_High VARCHAR2,
233 X_Last_Update_Date DATE,
234 X_Last_Updated_By NUMBER,
235 X_Creation_Date DATE,
236 X_Created_By NUMBER,
237 X_Last_Update_Login NUMBER,
238 X_Attribute1 VARCHAR2,
239 X_Attribute2 VARCHAR2,
240 X_Attribute3 VARCHAR2,
241 X_Attribute4 VARCHAR2,
242 X_Attribute5 VARCHAR2,
243 X_Context VARCHAR2
244 ) IS
245 CURSOR C IS
246 SELECT *
247 FROM gl_cons_flex_hierarchies
248 WHERE rowid = X_Rowid
249 FOR UPDATE of Segment_Map_Id NOWAIT;
250 Recinfo C%ROWTYPE;
251 BEGIN
252 OPEN C;
253 FETCH C INTO Recinfo;
254 if (C%NOTFOUND) then
255 CLOSE C;
256 RAISE NO_DATA_FOUND;
257 end if;
258 CLOSE C;
259 if (
260 ( (Recinfo.segment_map_id = X_Segment_Map_Id)
261 OR ( (Recinfo.segment_map_id IS NULL)
262 AND (X_Segment_Map_Id IS NULL)))
263 AND ( (Recinfo.parent_flex_value = X_parent_flex_value)
264 OR ( (Recinfo.parent_flex_value IS NULL)
265 AND (X_parent_flex_value IS NULL)))
266 AND ( (Recinfo.child_flex_value_low = X_child_flex_value_low)
267 OR ( (Recinfo.child_flex_value_low IS NULL)
268 AND (X_child_flex_value_low IS NULL)))
269 AND ( (Recinfo.child_flex_value_high = X_child_flex_value_high)
270 OR ( (Recinfo.child_flex_value_high IS NULL)
271 AND (X_child_flex_value_high IS NULL)))
272 AND ( (Recinfo.last_update_date = X_last_update_date)
273 OR ( (Recinfo.last_update_date IS NULL)
274 AND (X_last_update_date IS NULL)))
275 AND ( (Recinfo.last_updated_by = X_last_updated_by)
276 OR ( (Recinfo.last_updated_by IS NULL)
277 AND (X_last_updated_by IS NULL)))
278 AND ( (Recinfo.creation_date = X_creation_date)
279 OR ( (Recinfo.creation_date IS NULL)
280 AND (X_creation_date IS NULL)))
281 AND ( (Recinfo.created_by = X_created_by)
282 OR ( (Recinfo.created_by IS NULL)
283 AND (X_created_by IS NULL)))
284 AND ( (Recinfo.last_update_login = X_last_update_login)
285 OR ( (Recinfo.last_update_login IS NULL)
286 AND (X_last_update_login IS NULL)))
287 AND ( (Recinfo.attribute1 = X_Attribute1)
288 OR ( (Recinfo.attribute1 IS NULL)
289 AND (X_Attribute1 IS NULL)))
290 AND ( (Recinfo.attribute2 = X_Attribute2)
291 OR ( (Recinfo.attribute2 IS NULL)
292 AND (X_Attribute2 IS NULL)))
293 AND ( (Recinfo.attribute3 = X_Attribute3)
294 OR ( (Recinfo.attribute3 IS NULL)
295 AND (X_Attribute3 IS NULL)))
296 AND ( (Recinfo.attribute4 = X_Attribute4)
297 OR ( (Recinfo.attribute4 IS NULL)
298 AND (X_Attribute4 IS NULL)))
299 AND ( (Recinfo.attribute5 = X_Attribute5)
300 OR ( (Recinfo.attribute5 IS NULL)
301 AND (X_Attribute5 IS NULL)))
302 AND ( (Recinfo.context = X_Context)
303 OR ( (Recinfo.context IS NULL)
304 AND (X_Context IS NULL)))
305 ) then
306 return;
307 else
308 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
309 APP_EXCEPTION.RAISE_EXCEPTION;
310 end if;
311 END Lock_Row;
312
313 PROCEDURE Update_Row(X_Rowid VARCHAR2,
314 X_Segment_Map_Id NUMBER,
315 X_Parent_Flex_Value VARCHAR2,
316 X_Child_Flex_Value_Low VARCHAR2,
317 X_Child_Flex_Value_High VARCHAR2,
318 X_Last_Update_Date DATE,
319 X_Last_Updated_By NUMBER,
320 X_Creation_Date DATE,
321 X_Created_By NUMBER,
322 X_Last_Update_Login NUMBER,
323 X_Attribute1 VARCHAR2,
324 X_Attribute2 VARCHAR2,
325 X_Attribute3 VARCHAR2,
326 X_Attribute4 VARCHAR2,
327 X_Attribute5 VARCHAR2,
328 X_Context VARCHAR2
329 ) IS
330 BEGIN
331
332
333 -- Issue check_duplicate_rules call from server instead of in client.
334
335 -- Check_Duplicate_Rules( X_Rowid,
336 -- X_Single_Value,
337 -- X_Consolidation_Id,
338 -- X_To_Application_Column_Name,
339 -- X_To_Value_Set_Id,
340 -- X_Segment_Map_Type );
341 --
342
343 UPDATE gl_cons_flex_hierarchies
344 SET
345
346 segment_map_id = X_Segment_Map_Id,
347 parent_flex_value = X_Parent_Flex_Value,
348 child_flex_value_low = X_Child_Flex_Value_Low,
349 child_flex_value_high = X_Child_Flex_Value_high,
350 last_update_date = X_Last_Update_Date,
351 last_updated_by = X_Last_Updated_By,
352 creation_date = X_Creation_Date,
353 last_update_login = X_Last_Update_Login,
354 attribute1 = X_Attribute1,
355 attribute2 = X_Attribute2,
356 attribute3 = X_Attribute3,
357 attribute4 = X_Attribute4,
358 attribute5 = X_Attribute5,
359 context = X_Context
360 WHERE rowid = X_rowid;
361
362 if (SQL%NOTFOUND) then
363 RAISE NO_DATA_FOUND;
364 end if;
365
366 END Update_Row;
367
368
369 PROCEDURE Update_Parent_Values(
370 X_Segment_Map_Id NUMBER,
371 X_Parent_Flex_Value VARCHAR2,
372 X_Last_Update_Date DATE,
373 X_Last_Updated_By NUMBER,
374 X_Last_Update_Login NUMBER
375 ) IS
376 BEGIN
377
378 UPDATE gl_cons_flex_hierarchies
379 SET
380 parent_flex_value = X_Parent_Flex_Value,
381 last_update_date = X_Last_Update_Date,
382 last_updated_by = X_Last_Updated_By,
383 last_update_login = X_Last_Update_Login
384 WHERE segment_map_id = X_Segment_Map_Id;
385
386 if (SQL%NOTFOUND) then
387 RAISE NO_DATA_FOUND;
388 end if;
389
390 END Update_Parent_Values;
391
392 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Segment_Map_Id NUMBER ) IS
393 BEGIN
394
395 --Previously from Pre-Delete
396 --DELETE FROM GL_CONS_FLEX_HIERARCHIES
397 --WHERE SEGMENT_MAP_ID = X_Segment_Map_Id;
398
399
400 DELETE FROM gl_cons_flex_hierarchies
401 WHERE rowid = X_Rowid;
402
403 if (SQL%NOTFOUND) then
404 RAISE NO_DATA_FOUND;
405 end if;
406 END Delete_Row;
407
408 END GL_CONS_FLEX_HIER_PKG;
[Home] [Help]