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