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