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