[Home] [Help]
PACKAGE BODY: APPS.RG_DSS_DIM_SEGMENTS_PKG
Source
1 PACKAGE BODY RG_DSS_DIM_SEGMENTS_PKG AS
2 /* $Header: rgiddsmb.pls 120.2 2002/11/14 02:58:11 djogg ship $ */
3 --
4 -- Name
5 -- RG_DSS_DIM_SEGMENTS_PKG
6 -- Purpose
7 -- to include all server side procedures AND packages for table
8 -- rg_dss_DIM_SEGMENTS
9 -- Notes
10 --
11 -- History
12 -- 06/16/95 A Chen Created
13 --
14 --
15 -- PRIVATE VARIABLES
16 -- None.
17 --
18 -- PRIVATE FUNCTIONS
19 -- None.
20 --
21 -- PUBLIC FUNCTIONS
22 --
23
24 PROCEDURE check_unique_sequence(X_rowid VARCHAR2,
25 X_dimension_id NUMBER,
26 X_sequence NUMBER) IS
27 dummy NUMBER;
28 BEGIN
29 SELECT 1
30 INTO dummy
31 FROM dual
32 WHERE NOT EXISTS
33 (SELECT 1
34 FROM rg_dss_dim_segments
35 WHERE dimension_id = X_dimension_id
36 AND sequence = X_sequence
37 AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
38 );
39
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS_FOR');
43 FND_MESSAGE.set_token('OBJECT1', 'RG_DSS_SEQUENCE', TRUE);
44 FND_MESSAGE.set_token('OBJECT2', 'RG_DSS_DIMENSION', TRUE);
45 APP_EXCEPTION.raise_exception;
46 END check_unique_sequence;
47
48
49 PROCEDURE check_unique_segment(X_rowid VARCHAR2,
50 X_dimension_id NUMBER,
51 X_application_column_name VARCHAR2) IS
52 dummy NUMBER;
53 BEGIN
54 SELECT 1
55 INTO dummy
56 FROM dual
57 WHERE NOT EXISTS
58 (SELECT 1
59 FROM rg_dss_dim_segments
60 WHERE dimension_id = X_dimension_id
61 AND application_column_name = X_application_column_name
62 AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
63 );
64
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS_FOR');
68 FND_MESSAGE.set_token('OBJECT1', 'RG_DSS_SEGMENT', TRUE);
69 FND_MESSAGE.set_token('OBJECT2', 'RG_DSS_DIMENSION', TRUE);
70 APP_EXCEPTION.raise_exception;
71 END check_unique_segment;
72
73
74 FUNCTION number_of_dim_segments(X_dimension_id NUMBER) RETURN NUMBER IS
75 num_of_dim_segs NUMBER;
76 BEGIN
77 SELECT count(sequence)
78 INTO num_of_dim_segs
79 FROM rg_dss_dim_segments
80 WHERE dimension_id = X_dimension_id;
81
82 RETURN num_of_dim_segs;
83 END number_of_dim_segments;
84
85 -- *********************************************************************
86 -- The following procedures are necessary to hANDle the base view form.
87
88 PROCEDURE insert_row(X_master_dimension_id IN OUT NOCOPY NUMBER,
89 X_rowid IN OUT NOCOPY VARCHAR2,
90 X_dimension_id IN OUT NOCOPY NUMBER,
91 X_sequence NUMBER,
92 X_application_column_name VARCHAR2,
93 X_id_flex_code VARCHAR2,
94 X_id_flex_num NUMBER,
95 X_max_desc_size NUMBER,
96 X_creation_date DATE,
97 X_created_by NUMBER,
98 X_last_update_date DATE,
99 X_last_updated_by NUMBER,
100 X_last_update_login NUMBER,
101 X_range_set_id NUMBER,
102 X_account_type VARCHAR2,
103 X_context VARCHAR2,
104 X_attribute1 VARCHAR2,
105 X_attribute2 VARCHAR2,
106 X_attribute3 VARCHAR2,
107 X_attribute4 VARCHAR2,
108 X_attribute5 VARCHAR2,
109 X_attribute6 VARCHAR2,
110 X_attribute7 VARCHAR2,
111 X_attribute8 VARCHAR2,
112 X_attribute9 VARCHAR2,
113 X_attribute10 VARCHAR2,
114 X_attribute11 VARCHAR2,
115 X_attribute12 VARCHAR2,
116 X_attribute13 VARCHAR2,
117 X_attribute14 VARCHAR2,
118 X_attribute15 VARCHAR2
119 ) IS
120 CURSOR C IS SELECT rowid FROM rg_dss_dim_segments
121 WHERE dimension_id = X_dimension_id
122 AND sequence = X_sequence;
123 BEGIN
124 IF (X_Master_Dimension_Id IS NULL) THEN
125 X_Master_Dimension_Id := RG_DSS_DIMENSIONS_PKG.get_new_id;
126 END IF;
127 X_dimension_id := X_Master_Dimension_Id;
128
129 IF (RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(X_Dimension_Id) = 1) THEN
130 -- can't modify a dimension that is used in a frozen system
131 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
132 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_DIMENSION', TRUE);
133 APP_EXCEPTION.raise_exception;
134 END IF;
135
136 check_unique_sequence(X_rowid, X_dimension_id, X_sequence);
137 check_unique_segment(X_rowid, X_dimension_id, X_application_column_name);
138
139 INSERT INTO rg_dss_dim_segments
140 (dimension_id ,
141 sequence ,
142 application_column_name ,
143 id_flex_code ,
144 id_flex_num ,
145 max_desc_size ,
146 creation_date ,
147 created_by ,
148 last_update_date ,
149 last_updated_by ,
150 last_update_login ,
151 range_set_id ,
152 account_type ,
153 context ,
154 attribute1 ,
155 attribute2 ,
156 attribute3 ,
157 attribute4 ,
158 attribute5 ,
159 attribute6 ,
160 attribute7 ,
161 attribute8 ,
162 attribute9 ,
163 attribute10 ,
164 attribute11 ,
165 attribute12 ,
166 attribute13 ,
167 attribute14 ,
168 attribute15 )
169 VALUES
170 (X_dimension_id ,
171 X_sequence ,
172 X_application_column_name ,
173 X_id_flex_code ,
174 X_id_flex_num ,
175 X_max_desc_size ,
176 X_creation_date ,
177 X_created_by ,
178 X_last_update_date ,
179 X_last_updated_by ,
180 X_last_update_login ,
181 X_range_set_id ,
182 X_account_type ,
183 X_context ,
184 X_attribute1 ,
185 X_attribute2 ,
186 X_attribute3 ,
187 X_attribute4 ,
188 X_attribute5 ,
189 X_attribute6 ,
190 X_attribute7 ,
191 X_attribute8 ,
192 X_attribute9 ,
193 X_attribute10 ,
194 X_attribute11 ,
195 X_attribute12 ,
196 X_attribute13 ,
197 X_attribute14 ,
198 X_attribute15 );
199
200 OPEN C;
201 FETCH C INTO X_rowid;
202 IF (C%NOTFOUND) THEN
203 CLOSE C;
204 RAISE NO_DATA_FOUND;
205 END IF;
206 CLOSE C;
207 END insert_row;
208
209
210 PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2,
211 X_dimension_id NUMBER,
212 X_sequence NUMBER,
213 X_application_column_name VARCHAR2,
214 X_id_flex_code VARCHAR2,
215 X_id_flex_num NUMBER,
216 X_max_desc_size NUMBER,
217 X_last_update_date DATE,
218 X_last_updated_by NUMBER,
219 X_last_update_login NUMBER,
220 X_range_set_id NUMBER,
221 X_account_type VARCHAR2,
222 X_context VARCHAR2,
223 X_attribute1 VARCHAR2,
224 X_attribute2 VARCHAR2,
225 X_attribute3 VARCHAR2,
226 X_attribute4 VARCHAR2,
227 X_attribute5 VARCHAR2,
228 X_attribute6 VARCHAR2,
229 X_attribute7 VARCHAR2,
230 X_attribute8 VARCHAR2,
231 X_attribute9 VARCHAR2,
232 X_attribute10 VARCHAR2,
233 X_attribute11 VARCHAR2,
234 X_attribute12 VARCHAR2,
235 X_attribute13 VARCHAR2,
236 X_attribute14 VARCHAR2,
237 X_attribute15 VARCHAR2
238 ) IS
239 BEGIN
240 IF (RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(X_Dimension_Id) = 1) THEN
241 -- can't modify a dimension that is used in a frozen system
242 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
243 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_DIMENSION', TRUE);
244 APP_EXCEPTION.raise_exception;
245 END IF;
246
247 UPDATE rg_dss_dim_segments
248 SET dimension_id = X_dimension_id ,
249 sequence = X_sequence ,
250 application_column_name = X_application_column_name ,
251 id_flex_code = X_id_flex_code ,
252 id_flex_num = X_id_flex_num ,
253 max_desc_size = X_max_desc_size ,
254 last_update_date = X_last_update_date ,
255 last_updated_by = X_last_updated_by ,
256 last_update_login = X_last_update_login ,
257 range_set_id = X_range_set_id ,
258 account_type = X_account_type ,
259 context = X_context ,
260 attribute1 = X_attribute1 ,
261 attribute2 = X_attribute2 ,
262 attribute3 = X_attribute3 ,
263 attribute4 = X_attribute4 ,
264 attribute5 = X_attribute5 ,
265 attribute6 = X_attribute6 ,
266 attribute7 = X_attribute7 ,
267 attribute8 = X_attribute8 ,
268 attribute9 = X_attribute9 ,
269 attribute10 = X_attribute10 ,
270 attribute11 = X_attribute11 ,
271 attribute12 = X_attribute12 ,
272 attribute13 = X_attribute13 ,
273 attribute14 = X_attribute14 ,
274 attribute15 = X_attribute15
275 WHERE rowid = X_rowid;
276
277 IF (SQL%NOTFOUND) THEN
278 RAISE NO_DATA_FOUND;
279 END IF;
280
281 END update_row;
282
283 PROCEDURE lock_row(X_rowid IN OUT NOCOPY VARCHAR2,
284 X_dimension_id NUMBER,
285 X_sequence NUMBER,
286 X_application_column_name VARCHAR2,
287 X_id_flex_code VARCHAR2,
288 X_id_flex_num NUMBER,
289 X_max_desc_size NUMBER,
290 X_range_set_id NUMBER,
291 X_account_type VARCHAR2,
292 X_context VARCHAR2,
293 X_attribute1 VARCHAR2,
294 X_attribute2 VARCHAR2,
295 X_attribute3 VARCHAR2,
296 X_attribute4 VARCHAR2,
297 X_attribute5 VARCHAR2,
298 X_attribute6 VARCHAR2,
299 X_attribute7 VARCHAR2,
300 X_attribute8 VARCHAR2,
301 X_attribute9 VARCHAR2,
302 X_attribute10 VARCHAR2,
303 X_attribute11 VARCHAR2,
304 X_attribute12 VARCHAR2,
305 X_attribute13 VARCHAR2,
306 X_attribute14 VARCHAR2,
307 X_attribute15 VARCHAR2
308 ) IS
309 CURSOR C IS
310 SELECT *
311 FROM rg_dss_dim_segments
312 WHERE rowid = X_rowid
313 FOR UPDATE OF sequence NOWAIT;
314 Recinfo C%ROWTYPE;
315 BEGIN
316 OPEN C;
317 FETCH C INTO Recinfo;
318 IF (C%NOTFOUND) THEN
319 CLOSE C;
320 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
321 APP_EXCEPTION.RAISE_EXCEPTION;
322 END IF;
323 CLOSE C;
324
325 IF (
326 ( (Recinfo.dimension_id = X_dimension_id)
330 OR ( (Recinfo.sequence IS NULL)
327 OR ( (Recinfo.dimension_id IS NULL)
328 AND (X_dimension_id IS NULL)))
329 AND ( (Recinfo.sequence = X_sequence)
331 AND (X_sequence IS NULL)))
332 AND ( (Recinfo.application_column_name = X_application_column_name)
333 OR ( (Recinfo.application_column_name IS NULL)
334 AND (X_application_column_name IS NULL)))
335 AND ( (Recinfo.id_flex_code = X_id_flex_code)
336 OR ( (Recinfo.id_flex_code IS NULL)
337 AND (X_id_flex_code IS NULL)))
338 AND ( (Recinfo.id_flex_num = X_id_flex_num)
339 OR ( (Recinfo.id_flex_num IS NULL)
340 AND (X_id_flex_num IS NULL)))
341 AND ( (Recinfo.max_desc_size = X_max_desc_size)
342 OR ( (Recinfo.max_desc_size IS NULL)
343 AND (X_max_desc_size IS NULL)))
344 AND ( (Recinfo.range_set_id = X_range_set_id)
345 OR ( (Recinfo.range_set_id IS NULL)
346 AND (X_range_set_id IS NULL)))
347 AND ( (Recinfo.account_type = X_account_type)
348 OR ( (Recinfo.account_type IS NULL)
349 AND (X_account_type IS NULL)))
350 AND ( (Recinfo.context = X_context)
351 OR ( (Recinfo.context IS NULL)
352 AND (X_context IS NULL)))
353 AND ( (Recinfo.attribute1 = X_attribute1)
354 OR ( (Recinfo.attribute1 IS NULL)
355 AND (X_attribute1 IS NULL)))
356 AND ( (Recinfo.attribute2 = X_attribute2)
357 OR ( (Recinfo.attribute2 IS NULL)
358 AND (X_attribute2 IS NULL)))
359 AND ( (Recinfo.attribute3 = X_attribute3)
360 OR ( (Recinfo.attribute3 IS NULL)
361 AND (X_attribute3 IS NULL)))
362 AND ( (Recinfo.attribute4 = X_attribute4)
363 OR ( (Recinfo.attribute4 IS NULL)
364 AND (X_attribute4 IS NULL)))
365 AND ( (Recinfo.attribute5 = X_attribute5)
366 OR ( (Recinfo.attribute5 IS NULL)
367 AND (X_attribute5 IS NULL)))
368 AND ( (Recinfo.attribute6 = X_attribute6)
369 OR ( (Recinfo.attribute6 IS NULL)
370 AND (X_attribute6 IS NULL)))
371 AND ( (Recinfo.attribute7 = X_attribute7)
372 OR ( (Recinfo.attribute7 IS NULL)
373 AND (X_attribute7 IS NULL)))
374 AND ( (Recinfo.attribute8 = X_attribute8)
375 OR ( (Recinfo.attribute8 IS NULL)
376 AND (X_attribute8 IS NULL)))
377 AND ( (Recinfo.attribute9 = X_attribute9)
378 OR ( (Recinfo.attribute9 IS NULL)
379 AND (X_attribute9 IS NULL)))
380 AND ( (Recinfo.attribute10 = X_attribute10)
381 OR ( (Recinfo.attribute10 IS NULL)
382 AND (X_attribute10 IS NULL)))
383 AND ( (Recinfo.attribute11 = X_attribute11)
384 OR ( (Recinfo.attribute11 IS NULL)
385 AND (X_attribute11 IS NULL)))
386 AND ( (Recinfo.attribute12 = X_attribute12)
387 OR ( (Recinfo.attribute12 IS NULL)
388 AND (X_attribute12 IS NULL)))
389 AND ( (Recinfo.attribute13 = X_attribute13)
390 OR ( (Recinfo.attribute13 IS NULL)
391 AND (X_attribute13 IS NULL)))
392 AND ( (Recinfo.attribute14 = X_attribute14)
393 OR ( (Recinfo.attribute4 IS NULL)
394 AND (X_attribute14 IS NULL)))
395 AND ( (Recinfo.attribute15 = X_attribute15)
396 OR ( (Recinfo.attribute15 IS NULL)
397 AND (X_attribute15 IS NULL)))
398 ) THEN
399 RETURN;
400 ELSE
401 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
402 APP_EXCEPTION.RAISE_EXCEPTION;
403 END IF;
404 END lock_row;
405
406 PROCEDURE delete_row(
407 X_rowid VARCHAR2,
408 X_Dimension_Id NUMBER) IS
409 BEGIN
410 IF (RG_DSS_DIMENSIONS_PKG.used_in_frozen_system(X_Dimension_Id) = 1) THEN
411 -- can't modify a dimension that is used in a frozen system
412 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
413 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_DIMENSION', TRUE);
414 APP_EXCEPTION.raise_exception;
415 END IF;
416
417 DELETE FROM rg_dss_dim_segments
418 WHERE rowid = X_rowid;
419
420 IF (SQL%NOTFOUND) THEN
421 RAISE NO_DATA_FOUND;
422 END IF;
423 END delete_row;
424
425
426 END RG_DSS_DIM_SEGMENTS_PKG;