1 PACKAGE BODY RG_REPORT_DISPLAY_SETS_PKG AS
2 /* $Header: rgirdpsb.pls 120.2 2002/11/14 03:01:06 djogg ship $ */
3 --
4 -- PUBLIC FUNCTIONS
5 --
6
7 PROCEDURE check_unique( X_rowid VARCHAR2,
8 X_name VARCHAR2 ) IS
9 dummy NUMBER;
10 BEGIN
11 select 1 into dummy from dual
12 where not exists
13 (select 1 from rg_report_display_sets
14 where name = X_name
15 and ((X_rowid IS NULL) OR (rowid <> X_rowid)));
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 fnd_message.set_name('RG','RG_FORMS_OBJECT_EXISTS');
19 fnd_message.set_token('OBJECT','RG_REPORT_DISPLAY_SET',TRUE);
20 app_exception.raise_exception;
21 END check_unique;
22
23 PROCEDURE check_references(X_report_display_set_id NUMBER) IS
24 dummy NUMBER;
25 BEGIN
26 select 1 into dummy from dual
27 where not exists
28 (select 1 from rg_reports
29 where report_display_set_id = X_report_display_set_id);
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 fnd_message.set_name('RG','RG_FORMS_REF_OBJECT');
33 fnd_message.set_token('OBJECT','RG_REPORT_DISPLAY_SET', TRUE);
34 app_exception.raise_exception;
35 END check_references;
36
37 FUNCTION check_display_exists(X_report_display_set_id NUMBER)
38 RETURN BOOLEAN IS
39 dummy NUMBER;
40 BEGIN
41 select 1 into dummy from dual
42 where not exists
43 (select 1
44 from rg_report_displays
45 where report_display_set_id = X_report_display_set_id);
46 RETURN (FALSE);
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 RETURN (TRUE);
51 END check_display_exists;
52
53 FUNCTION check_displays_row_set(X_rowid VARCHAR2,
54 X_report_display_set_id NUMBER,
55 X_row_set_id_saved NUMBER)
56 RETURN BOOLEAN IS
57 dummy NUMBER;
58 BEGIN
59 -- check whether at least one of the display options of this display
60 -- set uses a row group which references the same row set as this
61 -- display set does.
62 select 1 into dummy from dual
63 where not exists
64 (select 1
65 from rg_report_displays dpo,
66 rg_report_display_groups dpg
67 where dpo.row_group_id = dpg.report_display_group_id
68 and dpo.report_display_set_id = X_report_display_set_id
69 and dpg.row_set_id = nvl(X_row_set_id_saved,-1)
70 and X_rowid IS NOT NULL);
71 RETURN (FALSE);
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 RETURN (TRUE);
76 END check_displays_row_set;
77
78 FUNCTION check_reports_row_set(X_rowid VARCHAR2,
79 X_report_display_set_id NUMBER,
80 X_row_set_id NUMBER,
81 X_row_set_id_saved NUMBER)
82 RETURN BOOLEAN IS
83 dummy NUMBER;
84 BEGIN
85 -- check if there is any report that uses this display set uses
86 -- the old row set and if any report that references a row set
87 -- other than the new row set
88 --
89 select 1 into dummy from dual
90 where not exists
91 (select 1
92 from rg_reports
93 where report_display_set_id = X_report_display_set_id
94 and row_set_id = nvl(X_row_set_id_saved,row_set_id)
95 and row_set_id <> nvl(X_row_set_id,row_set_id)
96 and X_rowid IS NOT NULL);
97 RETURN (FALSE);
98
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 RETURN (TRUE);
102 END check_reports_row_set;
103
104 FUNCTION check_displays_column_set(X_rowid VARCHAR2,
105 X_report_display_set_id NUMBER,
106 X_column_set_id_saved NUMBER)
107 RETURN BOOLEAN IS
108 dummy NUMBER;
109 BEGIN
110 -- check whether at least one of the display options of this display
111 -- set uses a column group which references the same column set as this
112 -- display set does. If so, column set
113 -- update is not allowed
114 --
115 select 1 into dummy from dual
116 where not exists
117 (select 1
118 from rg_report_displays dpo,
119 rg_report_display_groups dpg
120 where dpo.column_group_id = dpg.report_display_group_id
121 and dpo.report_display_set_id = X_report_display_set_id
122 and dpg.column_set_id = nvl(X_column_set_id_saved,-1)
123 and X_rowid IS NOT NULL);
124 RETURN (FALSE);
125
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 RETURN (TRUE);
129 END check_displays_column_set;
130
131
132 FUNCTION check_reports_column_set(X_rowid VARCHAR2,
133 X_report_display_set_id NUMBER,
134 X_column_set_id NUMBER,
135 X_column_set_id_saved NUMBER)
136 RETURN BOOLEAN IS
137 dummy NUMBER;
138 BEGIN
139 -- check if there is any report that uses this display set uses
140 -- the old column set and if any report that references a column set
141 -- other than the new column set. If so,
142 -- column set update is not allowed
143 --
144 select 1 into dummy from dual
145 where not exists
146 (select 1
147 from rg_reports
148 where report_display_set_id = X_report_display_set_id
149 and column_set_id = nvl(X_column_set_id_saved,column_set_id)
150 and column_set_id <> nvl(X_column_set_id,column_set_id)
151 and X_rowid IS NOT NULL);
152 RETURN (FALSE);
153
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 RETURN (TRUE);
157 END check_reports_column_set;
158
159 FUNCTION get_unique_id RETURN NUMBER IS
160 next_id NUMBER;
161 BEGIN
162 select rg_report_display_sets_s.nextval
163 into next_id
164 from dual;
165
166 RETURN (next_id);
167 END get_unique_id;
168
169
170 PROCEDURE insert_row(X_rowid IN OUT NOCOPY VARCHAR2,
171 X_report_display_set_id NUMBER,
172 X_name VARCHAR2,
173 X_description VARCHAR2,
174 X_row_set_id NUMBER,
175 X_column_set_id NUMBER,
176 X_creation_date DATE,
177 X_created_by NUMBER,
178 X_last_update_date DATE,
179 X_last_updated_by NUMBER,
180 X_last_update_login NUMBER,
181 X_context VARCHAR2,
182 X_attribute1 VARCHAR2,
183 X_attribute2 VARCHAR2,
184 X_attribute3 VARCHAR2,
185 X_attribute4 VARCHAR2,
186 X_attribute5 VARCHAR2,
187 X_attribute6 VARCHAR2,
188 X_attribute7 VARCHAR2,
189 X_attribute8 VARCHAR2,
190 X_attribute9 VARCHAR2,
191 X_attribute10 VARCHAR2,
192 X_attribute11 VARCHAR2,
193 X_attribute12 VARCHAR2,
194 X_attribute13 VARCHAR2,
195 X_attribute14 VARCHAR2,
196 X_attribute15 VARCHAR2) IS
197 CURSOR C IS SELECT rowid FROM rg_report_display_sets
198 WHERE report_display_set_id = X_report_display_set_id;
199 BEGIN
200 INSERT INTO rg_report_display_sets
201 (report_display_set_id ,
202 name ,
203 description ,
204 row_set_id ,
205 column_set_id ,
206 creation_date ,
207 created_by ,
208 last_update_date ,
209 last_updated_by ,
210 last_update_login ,
211 context ,
212 attribute1 ,
213 attribute2 ,
214 attribute3 ,
215 attribute4 ,
216 attribute5 ,
217 attribute6 ,
218 attribute7 ,
219 attribute8 ,
220 attribute9 ,
221 attribute10 ,
222 attribute11 ,
223 attribute12 ,
224 attribute13 ,
225 attribute14 ,
226 attribute15 )
227 VALUES
228 (X_report_display_set_id ,
229 X_name ,
230 X_description ,
231 X_row_set_id ,
232 X_column_set_id ,
233 X_creation_date ,
234 X_created_by ,
235 X_last_update_date ,
236 X_last_updated_by ,
237 X_last_update_login ,
238 X_context ,
239 X_attribute1 ,
240 X_attribute2 ,
241 X_attribute3 ,
242 X_attribute4 ,
243 X_attribute5 ,
244 X_attribute6 ,
245 X_attribute7 ,
246 X_attribute8 ,
247 X_attribute9 ,
248 X_attribute10 ,
249 X_attribute11 ,
250 X_attribute12 ,
251 X_attribute13 ,
252 X_attribute14 ,
253 X_attribute15 );
254
255 OPEN C;
256 FETCH C INTO X_rowid;
257 IF (C%NOTFOUND) THEN
258 CLOSE C;
259 RAISE NO_DATA_FOUND;
260 END IF;
261 CLOSE C;
262 END insert_row;
263
264 PROCEDURE lock_row(X_rowid IN OUT NOCOPY VARCHAR2,
265 X_report_display_set_id NUMBER,
266 X_name VARCHAR2,
267 X_description VARCHAR2,
268 X_row_set_id NUMBER,
269 X_column_set_id NUMBER,
270 X_context VARCHAR2,
271 X_attribute1 VARCHAR2,
272 X_attribute2 VARCHAR2,
273 X_attribute3 VARCHAR2,
274 X_attribute4 VARCHAR2,
275 X_attribute5 VARCHAR2,
276 X_attribute6 VARCHAR2,
277 X_attribute7 VARCHAR2,
278 X_attribute8 VARCHAR2,
279 X_attribute9 VARCHAR2,
280 X_attribute10 VARCHAR2,
281 X_attribute11 VARCHAR2,
282 X_attribute12 VARCHAR2,
283 X_attribute13 VARCHAR2,
284 X_attribute14 VARCHAR2,
285 X_attribute15 VARCHAR2) IS
286 CURSOR C IS
287 SELECT *
288 FROM rg_report_display_sets
289 WHERE rowid = X_rowid
290 FOR UPDATE OF name NOWAIT;
291 Recinfo C%ROWTYPE;
292 BEGIN
293 OPEN C;
294 FETCH C INTO Recinfo;
295 IF (C%NOTFOUND) THEN
296 CLOSE C;
297 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
298 APP_EXCEPTION.RAISE_EXCEPTION;
299 END IF;
300 CLOSE C;
301
302 IF (
303 ( (Recinfo.report_display_set_id = X_report_display_set_id)
304 OR ( (Recinfo.report_display_set_id IS NULL)
305 AND (X_report_display_set_id IS NULL)))
306 AND ( (Recinfo.name = X_name)
307 OR ( (Recinfo.name IS NULL)
308 AND (X_name IS NULL)))
309 AND ( (Recinfo.description = X_description)
310 OR ( (Recinfo.description IS NULL)
311 AND (X_description IS NULL)))
312 AND ( (Recinfo.row_set_id = X_row_set_id)
313 OR ( (Recinfo.row_set_id IS NULL)
314 AND (X_row_set_id IS NULL)))
315 AND ( (Recinfo.column_set_id = X_column_set_id)
316 OR ( (Recinfo.column_set_id IS NULL)
317 AND (X_column_set_id IS NULL)))
318 AND ( (Recinfo.context = X_context)
319 OR ( (Recinfo.context IS NULL)
320 AND (X_context IS NULL)))
321 AND ( (Recinfo.attribute1 = X_attribute1)
322 OR ( (Recinfo.attribute1 IS NULL)
323 AND (X_attribute1 IS NULL)))
324 AND ( (Recinfo.attribute2 = X_attribute2)
325 OR ( (Recinfo.attribute2 IS NULL)
326 AND (X_attribute2 IS NULL)))
327 AND ( (Recinfo.attribute3 = X_attribute3)
328 OR ( (Recinfo.attribute3 IS NULL)
329 AND (X_attribute3 IS NULL)))
330 AND ( (Recinfo.attribute4 = X_attribute4)
331 OR ( (Recinfo.attribute4 IS NULL)
332 AND (X_attribute4 IS NULL)))
333 AND ( (Recinfo.attribute5 = X_attribute5)
334 OR ( (Recinfo.attribute5 IS NULL)
335 AND (X_attribute5 IS NULL)))
336 AND ( (Recinfo.attribute6 = X_attribute6)
337 OR ( (Recinfo.attribute6 IS NULL)
338 AND (X_attribute6 IS NULL)))
339 AND ( (Recinfo.attribute7 = X_attribute7)
340 OR ( (Recinfo.attribute7 IS NULL)
341 AND (X_attribute7 IS NULL)))
342 AND ( (Recinfo.attribute8 = X_attribute8)
343 OR ( (Recinfo.attribute8 IS NULL)
344 AND (X_attribute8 IS NULL)))
345 AND ( (Recinfo.attribute9 = X_attribute9)
346 OR ( (Recinfo.attribute9 IS NULL)
347 AND (X_attribute9 IS NULL)))
348 AND ( (Recinfo.attribute10 = X_attribute10)
349 OR ( (Recinfo.attribute10 IS NULL)
350 AND (X_attribute10 IS NULL)))
351 AND ( (Recinfo.attribute11 = X_attribute11)
352 OR ( (Recinfo.attribute11 IS NULL)
353 AND (X_attribute11 IS NULL)))
354 AND ( (Recinfo.attribute12 = X_attribute12)
355 OR ( (Recinfo.attribute12 IS NULL)
356 AND (X_attribute12 IS NULL)))
357 AND ( (Recinfo.attribute13 = X_attribute13)
358 OR ( (Recinfo.attribute13 IS NULL)
359 AND (X_attribute13 IS NULL)))
360 AND ( (Recinfo.attribute14 = X_attribute14)
361 OR ( (Recinfo.attribute4 IS NULL)
362 AND (X_attribute14 IS NULL)))
363 AND ( (Recinfo.attribute15 = X_attribute15)
364 OR ( (Recinfo.attribute15 IS NULL)
365 AND (X_attribute15 IS NULL)))
366 ) THEN
370 APP_EXCEPTION.RAISE_EXCEPTION;
367 RETURN;
368 ELSE
369 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
371 END IF;
372 END lock_row;
373
374 PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2,
375 X_report_display_set_id NUMBER,
376 X_name VARCHAR2,
377 X_description VARCHAR2,
378 X_row_set_id NUMBER,
379 X_column_set_id NUMBER,
380 X_last_update_date DATE,
381 X_last_updated_by NUMBER,
382 X_last_update_login NUMBER,
383 X_context VARCHAR2,
384 X_attribute1 VARCHAR2,
385 X_attribute2 VARCHAR2,
386 X_attribute3 VARCHAR2,
387 X_attribute4 VARCHAR2,
388 X_attribute5 VARCHAR2,
389 X_attribute6 VARCHAR2,
390 X_attribute7 VARCHAR2,
391 X_attribute8 VARCHAR2,
392 X_attribute9 VARCHAR2,
393 X_attribute10 VARCHAR2,
394 X_attribute11 VARCHAR2,
395 X_attribute12 VARCHAR2,
396 X_attribute13 VARCHAR2,
397 X_attribute14 VARCHAR2,
398 X_attribute15 VARCHAR2) IS
399 BEGIN
400 UPDATE rg_report_display_sets
401 SET report_display_set_id = X_report_display_set_id ,
402 name = X_name ,
403 description = X_description ,
404 row_set_id = X_row_set_id ,
405 column_set_id = X_column_set_id ,
406 last_update_date = X_last_update_date ,
407 last_updated_by = X_last_updated_by ,
408 last_update_login = X_last_update_login ,
409 context = X_context ,
410 attribute1 = X_attribute1 ,
411 attribute2 = X_attribute2 ,
412 attribute3 = X_attribute3 ,
413 attribute4 = X_attribute4 ,
414 attribute5 = X_attribute5 ,
415 attribute6 = X_attribute6 ,
416 attribute7 = X_attribute7 ,
417 attribute8 = X_attribute8 ,
418 attribute9 = X_attribute9 ,
419 attribute10 = X_attribute10 ,
420 attribute11 = X_attribute11 ,
421 attribute12 = X_attribute12 ,
422 attribute13 = X_attribute13 ,
423 attribute14 = X_attribute14 ,
424 attribute15 = X_attribute15
425 WHERE rowid = X_rowid;
426
427 IF (SQL%NOTFOUND) THEN
428 RAISE NO_DATA_FOUND;
429 END IF;
430 END update_row;
431
432 PROCEDURE delete_row(X_rowid VARCHAR2) IS
433 BEGIN
434 DELETE FROM rg_report_display_sets
435 WHERE rowid = X_rowid;
436
437 IF (SQL%NOTFOUND) THEN
438 RAISE NO_DATA_FOUND;
439 END IF;
440 END delete_row;
441
442 END RG_REPORT_DISPLAY_SETS_PKG;
[Home] [Help]