[Home] [Help]
PACKAGE BODY: APPS.RG_DSS_HIERARCHIES_PKG
Source
1 PACKAGE BODY RG_DSS_HIERARCHIES_PKG as
2 /* $Header: rgidhrcb.pls 120.2 2002/11/14 02:58:24 djogg ship $ */
3
4
5
6 /*** PUBLIC FUNCTIONS ***/
7
8 FUNCTION get_new_id RETURN NUMBER IS
9 next_hierarchy_id NUMBER;
10 BEGIN
11 SELECT rg_dss_hierarchies_s.nextval
12 INTO next_hierarchy_id
13 FROM dual;
14
15 RETURN (next_hierarchy_id);
16 END get_new_id;
17
18
19 FUNCTION used_in_frozen_system(X_Hierarchy_Id NUMBER) RETURN BOOLEAN IS
20 dummy NUMBER;
21 BEGIN
22 SELECT 1
23 INTO dummy
24 FROM dual
25 WHERE NOT EXISTS
26 (SELECT 1
27 FROM rg_dss_hierarchies
28 WHERE hierarchy_id = X_Hierarchy_Id
29 AND RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(dimension_id) = 1);
30 RETURN(FALSE);
31 EXCEPTION
32 WHEN NO_DATA_FOUND THEN
33 RETURN(TRUE);
34 END used_in_frozen_system;
35
36
37 PROCEDURE check_unique_name(X_Rowid VARCHAR2, X_Name VARCHAR2) IS
38 dummy NUMBER;
39 BEGIN
40 SELECT 1
41 INTO dummy
42 FROM rg_dss_hierarchies
43 WHERE name = X_Name
44 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
45
46 -- name already exists for a different hierarchy: ERROR
47 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS');
48 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_HIERARCHY', TRUE);
49 APP_EXCEPTION.raise_exception;
50
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 -- name doesn't exist, so do nothing
54 NULL;
55 END check_unique_name;
56
57
58 FUNCTION details_exists(X_Hierarchy_Id NUMBER) RETURN BOOLEAN IS
59 dummy NUMBER;
60 BEGIN
61 SELECT 1
62 INTO dummy
63 FROM dual
64 WHERE NOT EXISTS
65 (SELECT 1
66 FROM rg_dss_hierarchy_details
67 WHERE hierarchy_id = X_Hierarchy_Id);
68 RETURN(FALSE);
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 RETURN(TRUE);
72 END details_exists;
73
74
75 FUNCTION num_details(X_Hierarchy_Id NUMBER) RETURN NUMBER IS
76 NumRecords NUMBER;
77 BEGIN
78 NumRecords := 0;
79
80 SELECT COUNT(hierarchy_id)
81 INTO NumRecords
82 FROM rg_dss_hierarchy_details
83 WHERE hierarchy_id = X_Hierarchy_Id;
84
85 RETURN(NumRecords);
86
87 EXCEPTION
88 WHEN app_exceptions.application_exception THEN
89 RAISE;
90 WHEN OTHERS THEN
91 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
92 RAISE;
93
94 END num_details;
95
96
97 FUNCTION num_segments_for_dim(X_Dimension_Id NUMBER) RETURN NUMBER IS
98 NumSegments NUMBER;
99 BEGIN
100 SELECT COUNT(*)
101 INTO NumSegments
102 FROM rg_dss_dim_segments
103 WHERE dimension_id = X_Dimension_Id;
104
105 RETURN(NumSegments);
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
110 RAISE;
111
112 END num_segments_for_dim;
113
114
115 PROCEDURE check_references(X_Hierarchy_Id NUMBER) IS
116 dummy NUMBER;
117 BEGIN
118 IF (used_in_frozen_system(X_Hierarchy_Id)) THEN
119 FND_MESSAGE.set_name('RG','RG_DSS_REF_HIERARCHY');
120 APP_EXCEPTION.raise_exception;
121 END IF;
122 END check_references;
123
124
125 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
126 X_Hierarchy_Id IN OUT NOCOPY NUMBER,
127 X_Name VARCHAR2,
128 X_Id_Flex_Code VARCHAR2,
129 X_Id_Flex_Num NUMBER,
130 X_Dimension_Id NUMBER,
131 X_Description VARCHAR2,
132 X_Last_Update_Date DATE,
133 X_Last_Updated_By NUMBER,
134 X_Last_Update_Login NUMBER,
135 X_Creation_Date DATE,
136 X_Created_By NUMBER,
137 X_Context VARCHAR2,
138 X_Attribute1 VARCHAR2,
139 X_Attribute2 VARCHAR2,
140 X_Attribute3 VARCHAR2,
141 X_Attribute4 VARCHAR2,
142 X_Attribute5 VARCHAR2,
143 X_Attribute6 VARCHAR2,
144 X_Attribute7 VARCHAR2,
145 X_Attribute8 VARCHAR2,
146 X_Attribute9 VARCHAR2,
147 X_Attribute10 VARCHAR2,
148 X_Attribute11 VARCHAR2,
149 X_Attribute12 VARCHAR2,
150 X_Attribute13 VARCHAR2,
151 X_Attribute14 VARCHAR2,
152 X_Attribute15 VARCHAR2) IS
153 num_segments NUMBER;
154 num_detail_rows NUMBER;
155
156 CURSOR C IS
157 SELECT rowid
158 FROM rg_dss_hierarchies
159 WHERE hierarchy_id = X_Hierarchy_Id;
160 BEGIN
161
162 check_unique_name(X_Rowid, X_Name);
163
164 IF (X_Hierarchy_Id IS NULL) THEN
165 X_Hierarchy_Id := get_new_id;
166 END IF;
167
168 /* Ensure that there are as many detail records
169 as there are segments for the dimension */
170
171 num_segments := num_segments_for_dim(X_dimension_id);
172 num_detail_rows := num_details(X_Hierarchy_Id);
173
174 IF (num_detail_rows <> num_segments) THEN
175 /* Every segment must have a detail hierarchy row */
176 FND_MESSAGE.set_name('RG','RG_DSS_HIR_ROOT_NODE');
177 APP_EXCEPTION.raise_exception;
178 END IF;
179
180 INSERT INTO rg_dss_hierarchies(
181 hierarchy_id,
182 name,
183 id_flex_code,
184 id_flex_num,
185 dimension_id,
186 description,
187 last_update_date,
188 last_updated_by,
189 last_update_login,
190 creation_date,
191 created_by,
192 context,
193 attribute1,
194 attribute2,
195 attribute3,
196 attribute4,
197 attribute5,
198 attribute6,
199 attribute7,
200 attribute8,
201 attribute9,
202 attribute10,
203 attribute11,
204 attribute12,
205 attribute13,
206 attribute14,
207 attribute15
208 ) VALUES (
209 X_Hierarchy_Id,
210 X_Name,
211 X_Id_Flex_Code,
212 X_Id_Flex_Num,
213 X_Dimension_Id,
214 X_Description,
215 X_Last_Update_Date,
216 X_Last_Updated_By,
217 X_Last_Update_Login,
218 X_Creation_Date,
219 X_Created_By,
220 X_Context,
221 X_Attribute1,
222 X_Attribute2,
223 X_Attribute3,
224 X_Attribute4,
225 X_Attribute5,
226 X_Attribute6,
227 X_Attribute7,
228 X_Attribute8,
229 X_Attribute9,
230 X_Attribute10,
231 X_Attribute11,
232 X_Attribute12,
233 X_Attribute13,
234 X_Attribute14,
235 X_Attribute15
236 );
237
238
239 OPEN C;
240 FETCH C INTO X_Rowid;
241
242 IF (C%NOTFOUND) THEN
243 CLOSE C;
244 RAISE NO_DATA_FOUND;
245 END IF;
246
247 CLOSE C;
248
249 END Insert_Row;
250
251
252 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
253 X_Hierarchy_Id NUMBER,
254 X_Name VARCHAR2,
255 X_Id_Flex_Code VARCHAR2,
256 X_Id_Flex_Num NUMBER,
257 X_Dimension_Id NUMBER,
258 X_Description VARCHAR2,
259 X_Context VARCHAR2,
260 X_Attribute1 VARCHAR2,
261 X_Attribute2 VARCHAR2,
262 X_Attribute3 VARCHAR2,
263 X_Attribute4 VARCHAR2,
264 X_Attribute5 VARCHAR2,
265 X_Attribute6 VARCHAR2,
266 X_Attribute7 VARCHAR2,
267 X_Attribute8 VARCHAR2,
268 X_Attribute9 VARCHAR2,
269 X_Attribute10 VARCHAR2,
270 X_Attribute11 VARCHAR2,
271 X_Attribute12 VARCHAR2,
272 X_Attribute13 VARCHAR2,
273 X_Attribute14 VARCHAR2,
274 X_Attribute15 VARCHAR2
275 ) IS
276 CURSOR C IS
277 SELECT *
278 FROM rg_dss_hierarchies
279 WHERE rowid = X_Rowid
280 FOR UPDATE of hierarchy_id NOWAIT;
281 Recinfo C%ROWTYPE;
282 BEGIN
283 OPEN C;
284 FETCH C INTO Recinfo;
285 IF (C%NOTFOUND) THEN
286 CLOSE C;
287 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
288 APP_EXCEPTION.RAISE_EXCEPTION;
289 END IF;
290 CLOSE C;
291
292 IF (
293 ( (Recinfo.hierarchy_id = X_Hierarchy_Id)
294 OR ( (Recinfo.hierarchy_id IS NULL)
295 AND (X_Hierarchy_Id IS NULL)))
296 AND ( (Recinfo.name = X_Name)
297 OR ( (Recinfo.name IS NULL)
298 AND (X_Name IS NULL)))
299 AND ( (Recinfo.id_flex_code = X_Id_Flex_Code)
300 OR ( (Recinfo.id_flex_code IS NULL)
301 AND (X_Id_Flex_Code IS NULL)))
302 AND ( (Recinfo.id_flex_num = X_Id_Flex_Num)
303 OR ( (Recinfo.id_flex_num IS NULL)
304 AND (X_Id_Flex_Num IS NULL)))
305 AND ( (Recinfo.dimension_id = X_Dimension_Id)
306 OR ( (Recinfo.dimension_id IS NULL)
307 AND (X_Dimension_Id IS NULL)))
308 AND ( (Recinfo.description = X_Description)
309 OR ( (Recinfo.description IS NULL)
310 AND (X_Description IS NULL)))
311 AND ( (Recinfo.context = X_Context)
312 OR ( (Recinfo.context IS NULL)
313 AND (X_Context IS NULL)))
314 AND ( (Recinfo.attribute1 = X_Attribute1)
315 OR ( (Recinfo.attribute1 IS NULL)
316 AND (X_Attribute1 IS NULL)))
317 AND ( (Recinfo.attribute2 = X_Attribute2)
318 OR ( (Recinfo.attribute2 IS NULL)
319 AND (X_Attribute2 IS NULL)))
320 AND ( (Recinfo.attribute3 = X_Attribute3)
321 OR ( (Recinfo.attribute3 IS NULL)
322 AND (X_Attribute3 IS NULL)))
323 AND ( (Recinfo.attribute4 = X_Attribute4)
324 OR ( (Recinfo.attribute4 IS NULL)
325 AND (X_Attribute4 IS NULL)))
326 AND ( (Recinfo.attribute5 = X_Attribute5)
327 OR ( (Recinfo.attribute5 IS NULL)
328 AND (X_Attribute5 IS NULL)))
329 AND ( (Recinfo.attribute6 = X_Attribute6)
330 OR ( (Recinfo.attribute6 IS NULL)
331 AND (X_Attribute6 IS NULL)))
332 AND ( (Recinfo.attribute7 = X_Attribute7)
333 OR ( (Recinfo.attribute7 IS NULL)
334 AND (X_Attribute7 IS NULL)))
335 AND ( (Recinfo.attribute8 = X_Attribute8)
336 OR ( (Recinfo.attribute8 IS NULL)
337 AND (X_Attribute8 IS NULL)))
338 AND ( (Recinfo.attribute9 = X_Attribute9)
339 OR ( (Recinfo.attribute9 IS NULL)
340 AND (X_Attribute9 IS NULL)))
341 AND ( (Recinfo.attribute10 = X_Attribute10)
342 OR ( (Recinfo.attribute10 IS NULL)
343 AND (X_Attribute10 IS NULL)))
344 AND ( (Recinfo.attribute11 = X_Attribute11)
345 OR ( (Recinfo.attribute11 IS NULL)
346 AND (X_Attribute11 IS NULL)))
347 AND ( (Recinfo.attribute12 = X_Attribute12)
348 OR ( (Recinfo.attribute12 IS NULL)
349 AND (X_Attribute12 IS NULL)))
350 AND ( (Recinfo.attribute13 = X_Attribute13)
351 OR ( (Recinfo.attribute13 IS NULL)
352 AND (X_Attribute13 IS NULL)))
353 AND ( (Recinfo.attribute14 = X_Attribute14)
354 OR ( (Recinfo.attribute14 IS NULL)
355 AND (X_Attribute14 IS NULL)))
356 AND ( (Recinfo.attribute15 = X_Attribute15)
357 OR ( (Recinfo.attribute15 IS NULL)
358 AND (X_Attribute15 IS NULL)))
359 ) THEN
360 RETURN;
361 ELSE
362 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 END IF;
365 END Lock_Row;
366
367
368 PROCEDURE Update_Row(X_Rowid VARCHAR2,
369 X_Hierarchy_Id NUMBER,
370 X_Name VARCHAR2,
371 X_Id_Flex_Code VARCHAR2,
372 X_Id_Flex_Num NUMBER,
373 X_Dimension_Id NUMBER,
374 X_Description VARCHAR2,
375 X_Last_Update_Date DATE,
376 X_Last_Updated_By NUMBER,
377 X_Last_Update_Login NUMBER,
378 X_Context VARCHAR2,
379 X_Attribute1 VARCHAR2,
380 X_Attribute2 VARCHAR2,
381 X_Attribute3 VARCHAR2,
382 X_Attribute4 VARCHAR2,
383 X_Attribute5 VARCHAR2,
384 X_Attribute6 VARCHAR2,
385 X_Attribute7 VARCHAR2,
386 X_Attribute8 VARCHAR2,
387 X_Attribute9 VARCHAR2,
388 X_Attribute10 VARCHAR2,
389 X_Attribute11 VARCHAR2,
390 X_Attribute12 VARCHAR2,
391 X_Attribute13 VARCHAR2,
392 X_Attribute14 VARCHAR2,
393 X_Attribute15 VARCHAR2) IS
394 num_segments NUMBER;
395 num_detail_rows NUMBER;
396 BEGIN
397
398 IF (used_in_frozen_system(X_Hierarchy_Id)) THEN
399 -- Can't update the record if the hierarchy is used in a frozen system
400 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
401 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_HIERARCHY', TRUE);
402 APP_EXCEPTION.raise_exception;
403 END IF;
404
405 /* Ensure that there are as many detail records
406 as there are segments for the dimension */
407
408 num_segments := num_segments_for_dim(X_dimension_id);
409 num_detail_rows := num_details(X_Hierarchy_Id);
410
411 IF (num_detail_rows <> num_segments) THEN
412 /* Every segment must have a detail hierarchy row */
413 FND_MESSAGE.set_name('RG','RG_DSS_HIR_ROOT_NODE');
414 APP_EXCEPTION.raise_exception;
415 END IF;
416
417 UPDATE rg_dss_hierarchies
418 SET
419 hierarchy_id = X_Hierarchy_Id,
420 name = X_Name,
421 id_flex_code = X_Id_Flex_Code,
422 id_flex_num = X_Id_Flex_Num,
423 dimension_id = X_Dimension_Id,
424 description = X_Description,
425 last_update_date = X_Last_Update_Date,
426 last_updated_by = X_Last_Updated_By,
427 last_update_login = X_Last_Update_Login,
428 context = X_Context,
429 attribute1 = X_Attribute1,
430 attribute2 = X_Attribute2,
431 attribute3 = X_Attribute3,
432 attribute4 = X_Attribute4,
433 attribute5 = X_Attribute5,
434 attribute6 = X_Attribute6,
435 attribute7 = X_Attribute7,
436 attribute8 = X_Attribute8,
437 attribute9 = X_Attribute9,
438 attribute10 = X_Attribute10,
439 attribute11 = X_Attribute11,
440 attribute12 = X_Attribute12,
441 attribute13 = X_Attribute13,
442 attribute14 = X_Attribute14,
443 attribute15 = X_Attribute15
444 WHERE rowid = X_rowid;
445
446 IF (SQL%NOTFOUND) THEN
447 RAISE NO_DATA_FOUND;
448 END IF;
449
450 END Update_Row;
451
452 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Hierarchy_Id NUMBER) IS
453 BEGIN
454
455 IF (used_in_frozen_system(X_Hierarchy_Id)) THEN
456 -- Can't delete the record if the hierarchy is used in a frozen system
457 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
458 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_HIERARCHY', TRUE);
459 APP_EXCEPTION.raise_exception;
460 END IF;
461
462 DELETE FROM rg_dss_hierarchies
463 WHERE rowid = X_Rowid;
464
465 IF (SQL%NOTFOUND) THEN
466 RAISE NO_DATA_FOUND;
467 END IF;
468
469 END Delete_Row;
470
471
472 END RG_DSS_HIERARCHIES_PKG;