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