[Home] [Help]
PACKAGE BODY: APPS.RG_DSS_VAR_DIMENSIONS_PKG
Source
1 PACKAGE BODY RG_DSS_VAR_DIMENSIONS_PKG as
2 /* $Header: rgidvdmb.pls 120.2 2002/11/14 02:59:25 djogg ship $ */
3
4
5
6 /*** PUBLIC FUNCTIONS ***/
7
8 PROCEDURE check_unique_sequence(X_Rowid VARCHAR2,
9 X_Variable_Id NUMBER,
10 X_Sequence NUMBER) IS
11 dummy NUMBER;
12 BEGIN
13 SELECT 1
14 INTO dummy
15 FROM rg_dss_var_dimensions
16 WHERE variable_id = X_Variable_Id
17 AND sequence = X_Sequence
18 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
19
20 -- name already exists for a different variable: ERROR
21 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS_FOR');
22 FND_MESSAGE.set_token('OBJECT1', 'RG_DSS_SEQUENCE', TRUE);
23 FND_MESSAGE.set_token('OBJECT2', 'RG_DSS_VARIABLE', TRUE);
24 APP_EXCEPTION.raise_exception;
25
26 EXCEPTION
27 WHEN NO_DATA_FOUND THEN
28 -- name doesn't exist, so do nothing
29 NULL;
30 END check_unique_sequence;
31
32
33 PROCEDURE check_unique_dimension(X_Rowid VARCHAR2,
34 X_Variable_Id NUMBER,
35 X_Dimension_Id NUMBER) IS
36 dummy NUMBER;
37 BEGIN
38 SELECT 1
39 INTO dummy
40 FROM rg_dss_var_dimensions
41 WHERE variable_id = X_Variable_Id
42 AND dimension_id = X_Dimension_Id
43 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
44
45 -- name already exists for a different variable: ERROR
46 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS_FOR');
47 FND_MESSAGE.set_token('OBJECT1', 'RG_DSS_DIMENSION', TRUE);
48 FND_MESSAGE.set_token('OBJECT2', 'RG_DSS_VARIABLE', 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_dimension;
56
57
58 PROCEDURE insert_row(
59 X_Master_Variable_Id IN OUT NOCOPY NUMBER,
60 X_Rowid IN OUT NOCOPY VARCHAR2,
61 X_Variable_Id IN OUT NOCOPY NUMBER,
62 X_Sequence NUMBER,
63 X_Dimension_Id NUMBER,
64 X_Last_Update_Date DATE,
65 X_Last_Updated_By NUMBER,
66 X_Last_Update_Login NUMBER,
67 X_Creation_Date DATE,
68 X_Created_By NUMBER,
69 X_Context VARCHAR2,
70 X_Attribute1 VARCHAR2,
71 X_Attribute2 VARCHAR2,
72 X_Attribute3 VARCHAR2,
73 X_Attribute4 VARCHAR2,
74 X_Attribute5 VARCHAR2,
75 X_Attribute6 VARCHAR2,
76 X_Attribute7 VARCHAR2,
77 X_Attribute8 VARCHAR2,
78 X_Attribute9 VARCHAR2,
79 X_Attribute10 VARCHAR2,
80 X_Attribute11 VARCHAR2,
81 X_Attribute12 VARCHAR2,
82 X_Attribute13 VARCHAR2,
83 X_Attribute14 VARCHAR2,
84 X_Attribute15 VARCHAR2) IS
85 NumRecs NUMBER;
86
87 CURSOR C IS
88 SELECT rowid
89 FROM rg_dss_var_dimensions
90 WHERE variable_id = X_Variable_Id
91 AND sequence = X_Sequence;
92
93 BEGIN
94 IF (X_Master_Variable_Id IS NULL) THEN
95 X_Master_Variable_Id := RG_DSS_VARIABLES_PKG.get_new_id;
96 END IF;
97 X_Variable_Id := X_Master_Variable_Id;
98
99 IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
100 -- can't modify a variable that is used in a frozen system
101 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
102 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
103 APP_EXCEPTION.raise_exception;
104 END IF;
105
106 SELECT count(*)
107 INTO NumRecs
108 FROM rg_dss_var_dimensions
109 WHERE variable_id = X_Variable_Id;
110
111 IF (NumRecs >= 10) THEN
112 -- Maximum number of dimensions is 10
113 FND_MESSAGE.set_name('RG', 'RG_DSS_MAX_DIMENSION_REACHED');
114 FND_MESSAGE.set_token('NUM_DIM','10');
115 APP_EXCEPTION.raise_exception;
116 END IF;
117
118 check_unique_sequence(X_Rowid, X_Variable_Id, X_Sequence);
119 check_unique_dimension(X_Rowid, X_Variable_Id, X_Dimension_Id);
120
121 INSERT INTO rg_dss_var_dimensions(
122 variable_id,
123 sequence,
124 dimension_id,
125 last_update_date,
126 last_updated_by,
127 last_update_login,
128 creation_date,
129 created_by,
130 context,
131 attribute1,
132 attribute2,
133 attribute3,
134 attribute4,
135 attribute5,
136 attribute6,
137 attribute7,
138 attribute8,
139 attribute9,
140 attribute10,
141 attribute11,
142 attribute12,
143 attribute13,
144 attribute14,
145 attribute15
146 ) VALUES (
147 X_Variable_Id,
148 X_Sequence,
149 X_Dimension_Id,
150 X_Last_Update_Date,
151 X_Last_Updated_By,
152 X_Last_Update_Login,
153 X_Creation_Date,
154 X_Created_By,
155 X_Context,
156 X_Attribute1,
157 X_Attribute2,
158 X_Attribute3,
159 X_Attribute4,
160 X_Attribute5,
161 X_Attribute6,
162 X_Attribute7,
163 X_Attribute8,
164 X_Attribute9,
165 X_Attribute10,
166 X_Attribute11,
167 X_Attribute12,
168 X_Attribute13,
169 X_Attribute14,
170 X_Attribute15
171 );
172
173
174 OPEN C;
175 FETCH C INTO X_Rowid;
176
177 IF (C%NOTFOUND) THEN
178 CLOSE C;
179 RAISE NO_DATA_FOUND;
180 END IF;
181
182 CLOSE C;
183
184 END Insert_Row;
185
186
187 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
188 X_Variable_Id NUMBER,
189 X_Sequence NUMBER,
190 X_Dimension_Id NUMBER,
191 X_Context VARCHAR2,
192 X_Attribute1 VARCHAR2,
193 X_Attribute2 VARCHAR2,
194 X_Attribute3 VARCHAR2,
195 X_Attribute4 VARCHAR2,
196 X_Attribute5 VARCHAR2,
197 X_Attribute6 VARCHAR2,
198 X_Attribute7 VARCHAR2,
199 X_Attribute8 VARCHAR2,
200 X_Attribute9 VARCHAR2,
201 X_Attribute10 VARCHAR2,
202 X_Attribute11 VARCHAR2,
203 X_Attribute12 VARCHAR2,
204 X_Attribute13 VARCHAR2,
205 X_Attribute14 VARCHAR2,
206 X_Attribute15 VARCHAR2
207 ) IS
208 CURSOR C IS
209 SELECT *
210 FROM rg_dss_var_dimensions
211 WHERE rowid = X_Rowid
212 FOR UPDATE of variable_id NOWAIT;
213 Recinfo C%ROWTYPE;
214 BEGIN
215 OPEN C;
216 FETCH C INTO Recinfo;
217 IF (C%NOTFOUND) THEN
218 CLOSE C;
219 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 END IF;
222 CLOSE C;
223
224 IF (
225 ( (Recinfo.variable_id = X_Variable_Id)
226 OR ( (Recinfo.variable_id IS NULL)
227 AND (X_Variable_Id IS NULL)))
228 AND ( (Recinfo.sequence = X_Sequence)
229 OR ( (Recinfo.sequence IS NULL)
230 AND (X_Sequence IS NULL)))
231 AND ( (Recinfo.dimension_id = X_Dimension_Id)
232 OR ( (Recinfo.dimension_id IS NULL)
233 AND (X_Dimension_Id IS NULL)))
234 AND ( (Recinfo.context = X_Context)
235 OR ( (Recinfo.context IS NULL)
236 AND (X_Context IS NULL)))
237 AND ( (Recinfo.attribute1 = X_Attribute1)
238 OR ( (Recinfo.attribute1 IS NULL)
239 AND (X_Attribute1 IS NULL)))
240 AND ( (Recinfo.attribute2 = X_Attribute2)
241 OR ( (Recinfo.attribute2 IS NULL)
242 AND (X_Attribute2 IS NULL)))
243 AND ( (Recinfo.attribute3 = X_Attribute3)
244 OR ( (Recinfo.attribute3 IS NULL)
245 AND (X_Attribute3 IS NULL)))
246 AND ( (Recinfo.attribute4 = X_Attribute4)
247 OR ( (Recinfo.attribute4 IS NULL)
248 AND (X_Attribute4 IS NULL)))
249 AND ( (Recinfo.attribute5 = X_Attribute5)
250 OR ( (Recinfo.attribute5 IS NULL)
251 AND (X_Attribute5 IS NULL)))
252 AND ( (Recinfo.attribute6 = X_Attribute6)
253 OR ( (Recinfo.attribute6 IS NULL)
254 AND (X_Attribute6 IS NULL)))
255 AND ( (Recinfo.attribute7 = X_Attribute7)
256 OR ( (Recinfo.attribute7 IS NULL)
257 AND (X_Attribute7 IS NULL)))
258 AND ( (Recinfo.attribute8 = X_Attribute8)
259 OR ( (Recinfo.attribute8 IS NULL)
260 AND (X_Attribute8 IS NULL)))
261 AND ( (Recinfo.attribute9 = X_Attribute9)
262 OR ( (Recinfo.attribute9 IS NULL)
263 AND (X_Attribute9 IS NULL)))
264 AND ( (Recinfo.attribute10 = X_Attribute10)
265 OR ( (Recinfo.attribute10 IS NULL)
266 AND (X_Attribute10 IS NULL)))
267 AND ( (Recinfo.attribute11 = X_Attribute11)
268 OR ( (Recinfo.attribute11 IS NULL)
269 AND (X_Attribute11 IS NULL)))
270 AND ( (Recinfo.attribute12 = X_Attribute12)
271 OR ( (Recinfo.attribute12 IS NULL)
272 AND (X_Attribute12 IS NULL)))
273 AND ( (Recinfo.attribute13 = X_Attribute13)
274 OR ( (Recinfo.attribute13 IS NULL)
275 AND (X_Attribute13 IS NULL)))
276 AND ( (Recinfo.attribute14 = X_Attribute14)
277 OR ( (Recinfo.attribute14 IS NULL)
278 AND (X_Attribute14 IS NULL)))
279 AND ( (Recinfo.attribute15 = X_Attribute15)
280 OR ( (Recinfo.attribute15 IS NULL)
281 AND (X_Attribute15 IS NULL)))
282 ) THEN
283 RETURN;
284 ELSE
285 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 END IF;
288 END Lock_Row;
289
290
291 PROCEDURE Update_Row(X_Rowid VARCHAR2,
292 X_Variable_Id NUMBER,
293 X_Sequence NUMBER,
294 X_Dimension_Id NUMBER,
295 X_Last_Update_Date DATE,
296 X_Last_Updated_By NUMBER,
297 X_Last_Update_Login NUMBER,
298 X_Context VARCHAR2,
299 X_Attribute1 VARCHAR2,
300 X_Attribute2 VARCHAR2,
301 X_Attribute3 VARCHAR2,
302 X_Attribute4 VARCHAR2,
303 X_Attribute5 VARCHAR2,
304 X_Attribute6 VARCHAR2,
305 X_Attribute7 VARCHAR2,
306 X_Attribute8 VARCHAR2,
307 X_Attribute9 VARCHAR2,
308 X_Attribute10 VARCHAR2,
309 X_Attribute11 VARCHAR2,
310 X_Attribute12 VARCHAR2,
311 X_Attribute13 VARCHAR2,
312 X_Attribute14 VARCHAR2,
313 X_Attribute15 VARCHAR2) IS
314 BEGIN
315
316 IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
317 -- can't modify a variable that is used in a frozen system
318 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
319 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
320 APP_EXCEPTION.raise_exception;
321 END IF;
322
323 UPDATE rg_dss_var_dimensions
324 SET
325 variable_id = X_Variable_Id,
326 sequence = X_Sequence,
327 dimension_id = X_Dimension_Id,
328 last_update_date = X_Last_Update_Date,
329 last_updated_by = X_Last_Updated_By,
330 last_update_login = X_Last_Update_Login,
331 context = X_Context,
332 attribute1 = X_Attribute1,
333 attribute2 = X_Attribute2,
334 attribute3 = X_Attribute3,
335 attribute4 = X_Attribute4,
336 attribute5 = X_Attribute5,
337 attribute6 = X_Attribute6,
338 attribute7 = X_Attribute7,
339 attribute8 = X_Attribute8,
340 attribute9 = X_Attribute9,
341 attribute10 = X_Attribute10,
342 attribute11 = X_Attribute11,
343 attribute12 = X_Attribute12,
344 attribute13 = X_Attribute13,
345 attribute14 = X_Attribute14,
346 attribute15 = X_Attribute15
347 WHERE rowid = X_rowid;
348
349 IF (SQL%NOTFOUND) THEN
350 RAISE NO_DATA_FOUND;
351 END IF;
352
353 END Update_Row;
354
355
356 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Variable_Id NUMBER) IS
357 BEGIN
358
359 IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
360 -- can't modify a variable that is used in a frozen system
361 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
362 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
363 APP_EXCEPTION.raise_exception;
364 END IF;
365
366 DELETE FROM rg_dss_var_dimensions
367 WHERE rowid = X_Rowid;
368
369 IF (SQL%NOTFOUND) THEN
370 RAISE NO_DATA_FOUND;
371 END IF;
372
373 /* Ensure that at least one dimension exists for the variable.
374 Allow summary dimension to be deleted */
375 RG_DSS_VARIABLES_PKG.check_for_details(X_Variable_Id,'D');
376 END Delete_Row;
377
378
379 END RG_DSS_VAR_DIMENSIONS_PKG;