[Home] [Help]
PACKAGE BODY: APPS.RG_REPORT_PARAMETERS_PKG
Source
1 PACKAGE BODY RG_REPORT_PARAMETERS_PKG AS
2 /* $Header: rgirparb.pls 120.4 2003/04/29 00:47:50 djogg ship $ */
3
4 FUNCTION get_new_id
5 RETURN NUMBER
6 IS
7 new_id NUMBER;
8 BEGIN
9 SELECT rg_report_parameters_s.nextval
10 INTO new_id
11 FROM dual;
12
13 RETURN new_id;
14 END get_new_id;
15
16 FUNCTION dup_parameter_num(para_set_id IN NUMBER,
17 para_num IN NUMBER,
18 para_type IN VARCHAR2,
19 row_id IN VARCHAR2)
20 RETURN BOOLEAN
21 IS
22 dummy NUMBER;
23 BEGIN
24 SELECT 1 INTO dummy FROM dual
25 WHERE NOT EXISTS
26 (SELECT 1 FROM rg_report_parameters
27 WHERE parameter_set_id = para_set_id
28 AND parameter_num = para_num
29 AND data_type = para_type
30 AND ((row_id IS NULL) OR (row_id <> rowid)));
31
32 RETURN (FALSE);
33
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 RETURN (TRUE);
37
38 END dup_parameter_num;
39
40 FUNCTION Duplicate_Row(from_parameter_set_id IN NUMBER)
41 RETURN NUMBER
42 IS
43 to_parameter_set_id NUMBER;
44 BEGIN
45 SELECT rg_report_parameters_s.nextval
46 INTO to_parameter_set_id
47 FROM dual;
48
49 INSERT INTO rg_report_parameters
50 (parameter_set_id,
51 last_update_date,
52 last_updated_by,
53 last_update_login,
54 creation_date,
55 created_by,
56 parameter_num,
57 data_type,
58 parameter_id,
59 currency_type,
60 entered_currency,
61 ledger_currency,
62 period_num,
63 fiscal_year_offset,
64 context,
65 attribute1,
66 attribute2,
67 attribute3,
68 attribute4,
69 attribute5,
70 attribute6,
71 attribute7,
72 attribute8,
73 attribute9,
74 attribute10,
75 attribute11,
76 attribute12,
77 attribute13,
78 attribute14,
79 attribute15)
80 SELECT to_parameter_set_id,
81 last_update_date,
82 last_updated_by,
83 last_update_login,
84 creation_date,
85 created_by,
86 parameter_num,
87 data_type,
88 parameter_id,
89 currency_type,
90 entered_currency,
91 ledger_currency,
92 period_num,
93 fiscal_year_offset,
94 context,
95 attribute1,
96 attribute2,
97 attribute3,
98 attribute4,
99 attribute5,
100 attribute6,
101 attribute7,
102 attribute8,
103 attribute9,
104 attribute10,
105 attribute11,
106 attribute12,
107 attribute13,
108 attribute14,
109 attribute15
110 FROM
111 rg_report_parameters
112 WHERE
113 parameter_set_id = from_parameter_set_id;
114
115 RETURN(to_parameter_set_id);
116 END Duplicate_Row;
117
118
119
120 FUNCTION insert_row(X_Rowid IN OUT NOCOPY VARCHAR2,
121 X_Parameter_Set_Id IN OUT NOCOPY NUMBER,
122 X_Last_Update_Date DATE,
123 X_Last_Updated_By NUMBER,
124 X_Last_Update_Login NUMBER,
125 X_Creation_Date DATE,
126 X_Created_By NUMBER,
127 X_Parameter_Num NUMBER,
128 X_Data_Type VARCHAR2,
129 X_Parameter_Id NUMBER,
130 X_Currency_Type VARCHAR2,
131 X_Entered_Currency VARCHAR2,
132 X_Ledger_Currency VARCHAR2,
133 X_Period_Num NUMBER,
134 X_Fiscal_Year_Offset NUMBER,
135 X_Context VARCHAR2,
136 X_Attribute1 VARCHAR2,
137 X_Attribute2 VARCHAR2,
138 X_Attribute3 VARCHAR2,
139 X_Attribute4 VARCHAR2,
140 X_Attribute5 VARCHAR2,
141 X_Attribute6 VARCHAR2,
142 X_Attribute7 VARCHAR2,
143 X_Attribute8 VARCHAR2,
144 X_Attribute9 VARCHAR2,
145 X_Attribute10 VARCHAR2,
146 X_Attribute11 VARCHAR2,
147 X_Attribute12 VARCHAR2,
148 X_Attribute13 VARCHAR2,
149 X_Attribute14 VARCHAR2,
150 X_Attribute15 VARCHAR2
151 ) RETURN BOOLEAN IS
152 CURSOR C IS SELECT rowid FROM rg_report_parameters
153 WHERE parameter_set_id = X_Parameter_Set_Id
154 AND parameter_num = X_Parameter_Num
155 AND data_type = X_Data_Type;
156
157 CURSOR C2 IS SELECT rg_report_parameters_s.nextval FROM dual;
158 BEGIN
159
160 IF (X_Parameter_Set_Id is NULL) then
161 OPEN C2;
162 FETCH C2 INTO X_Parameter_Set_Id;
163 CLOSE C2;
164 END IF;
165
166 IF (dup_parameter_num(X_Parameter_Set_Id,
167 X_Parameter_Num,
168 X_Data_Type,
169 X_Rowid)) THEN
170 RETURN(FALSE);
171 END IF;
172
173 INSERT INTO rg_report_parameters(
174 parameter_set_id,
175 last_update_date,
176 last_updated_by,
177 last_update_login,
178 creation_date,
179 created_by,
180 parameter_num,
181 data_type,
182 parameter_id,
183 currency_type,
184 entered_currency,
185 ledger_currency,
186 period_num,
187 fiscal_year_offset,
188 context,
189 attribute1,
190 attribute2,
191 attribute3,
192 attribute4,
193 attribute5,
194 attribute6,
195 attribute7,
196 attribute8,
197 attribute9,
198 attribute10,
199 attribute11,
200 attribute12,
201 attribute13,
202 attribute14,
203 attribute15
204 ) VALUES (
205 X_Parameter_Set_Id,
206 X_Last_Update_Date,
207 X_Last_Updated_By,
208 X_Last_Update_Login,
209 X_Creation_Date,
210 X_Created_By,
211 X_Parameter_Num,
212 X_Data_Type,
213 X_Parameter_Id,
214 X_Currency_Type,
215 X_Entered_Currency,
216 X_Ledger_Currency,
217 X_Period_Num,
218 X_Fiscal_Year_Offset,
219 X_Context,
220 X_Attribute1,
221 X_Attribute2,
222 X_Attribute3,
223 X_Attribute4,
224 X_Attribute5,
225 X_Attribute6,
226 X_Attribute7,
227 X_Attribute8,
228 X_Attribute9,
229 X_Attribute10,
230 X_Attribute11,
231 X_Attribute12,
232 X_Attribute13,
233 X_Attribute14,
234 X_Attribute15
235 );
236
237 OPEN C;
238 FETCH C INTO X_Rowid;
239 IF (C%NOTFOUND) then
240 CLOSE C;
241 RAISE NO_DATA_FOUND;
242 END IF;
243 CLOSE C;
244 RETURN(TRUE);
245 END insert_row;
246
247 PROCEDURE lock_row(X_Rowid VARCHAR2,
248 X_Parameter_Set_Id NUMBER,
249 X_Parameter_Num NUMBER,
250 X_Data_Type VARCHAR2,
251 X_Parameter_Id NUMBER,
252 X_Currency_Type VARCHAR2,
253 X_Entered_Currency VARCHAR2,
254 X_Ledger_Currency VARCHAR2,
255 X_Period_Num NUMBER,
256 X_Fiscal_Year_Offset NUMBER,
257 X_Context VARCHAR2,
258 X_Attribute1 VARCHAR2,
259 X_Attribute2 VARCHAR2,
260 X_Attribute3 VARCHAR2,
261 X_Attribute4 VARCHAR2,
262 X_Attribute5 VARCHAR2,
263 X_Attribute6 VARCHAR2,
264 X_Attribute7 VARCHAR2,
265 X_Attribute8 VARCHAR2,
266 X_Attribute9 VARCHAR2,
267 X_Attribute10 VARCHAR2,
268 X_Attribute11 VARCHAR2,
269 X_Attribute12 VARCHAR2,
270 X_Attribute13 VARCHAR2,
271 X_Attribute14 VARCHAR2,
272 X_Attribute15 VARCHAR2
273 ) IS
274 CURSOR C IS
275 SELECT *
276 FROM rg_report_parameters
277 WHERE rowid = X_Rowid
278 FOR UPDATE of Parameter_Set_Id NOWAIT;
279 Recinfo C%ROWTYPE;
280 BEGIN
281 OPEN C;
282 FETCH C INTO Recinfo;
283 IF (C%NOTFOUND) then
284 CLOSE C;
285 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 END IF;
288 CLOSE C;
289 IF (
290 ( (Recinfo.parameter_set_id = X_Parameter_Set_Id)
291 OR ( (Recinfo.parameter_set_id IS NULL)
292 AND (X_Parameter_Set_Id IS NULL)))
293 AND ( (Recinfo.parameter_num = X_Parameter_Num)
294 OR ( (Recinfo.parameter_num IS NULL)
295 AND (X_Parameter_Num IS NULL)))
296 AND ( (Recinfo.data_type = X_Data_Type)
297 OR ( (Recinfo.data_type IS NULL)
298 AND (X_Data_Type IS NULL)))
299 AND ( (Recinfo.parameter_id = X_Parameter_Id)
300 OR ( (Recinfo.parameter_id IS NULL)
301 AND (X_Parameter_Id IS NULL)))
302 AND ( (Recinfo.currency_type = X_Currency_Type)
303 OR ( (Recinfo.currency_type IS NULL)
304 AND (X_Currency_Type IS NULL)))
305 AND ( (Recinfo.entered_currency = X_Entered_Currency)
306 OR ( (Recinfo.entered_currency IS NULL)
307 AND (X_Entered_Currency IS NULL)))
308 AND ( (Recinfo.ledger_currency = X_Ledger_Currency)
309 OR ( (Recinfo.ledger_currency IS NULL)
310 AND (X_Ledger_Currency IS NULL)))
311 AND ( (Recinfo.period_num = X_Period_Num)
312 OR ( (Recinfo.period_num IS NULL)
313 AND (X_Period_Num IS NULL)))
314 AND ( (Recinfo.fiscal_year_offset = X_Fiscal_Year_Offset)
315 OR ( (Recinfo.fiscal_year_offset IS NULL)
316 AND (X_Fiscal_Year_Offset IS NULL)))
317 AND ( (Recinfo.context = X_Context)
318 OR ( (Recinfo.context IS NULL)
319 AND (X_Context IS NULL)))
320 AND ( (Recinfo.attribute1 = X_Attribute1)
321 OR ( (Recinfo.attribute1 IS NULL)
322 AND (X_Attribute1 IS NULL)))
323 AND ( (Recinfo.attribute2 = X_Attribute2)
324 OR ( (Recinfo.attribute2 IS NULL)
325 AND (X_Attribute2 IS NULL)))
326 AND ( (Recinfo.attribute3 = X_Attribute3)
327 OR ( (Recinfo.attribute3 IS NULL)
328 AND (X_Attribute3 IS NULL)))
329 AND ( (Recinfo.attribute4 = X_Attribute4)
330 OR ( (Recinfo.attribute4 IS NULL)
331 AND (X_Attribute4 IS NULL)))
332 AND ( (Recinfo.attribute5 = X_Attribute5)
333 OR ( (Recinfo.attribute5 IS NULL)
334 AND (X_Attribute5 IS NULL)))
335 AND ( (Recinfo.attribute6 = X_Attribute6)
336 OR ( (Recinfo.attribute6 IS NULL)
337 AND (X_Attribute6 IS NULL)))
338 AND ( (Recinfo.attribute7 = X_Attribute7)
339 OR ( (Recinfo.attribute7 IS NULL)
340 AND (X_Attribute7 IS NULL)))
341 AND ( (Recinfo.attribute8 = X_Attribute8)
342 OR ( (Recinfo.attribute8 IS NULL)
343 AND (X_Attribute8 IS NULL)))
344 AND ( (Recinfo.attribute9 = X_Attribute9)
345 OR ( (Recinfo.attribute9 IS NULL)
346 AND (X_Attribute9 IS NULL)))
347 AND ( (Recinfo.attribute10 = X_Attribute10)
348 OR ( (Recinfo.attribute10 IS NULL)
349 AND (X_Attribute10 IS NULL)))
350 AND ( (Recinfo.attribute11 = X_Attribute11)
351 OR ( (Recinfo.attribute11 IS NULL)
352 AND (X_Attribute11 IS NULL)))
353 AND ( (Recinfo.attribute12 = X_Attribute12)
354 OR ( (Recinfo.attribute12 IS NULL)
355 AND (X_Attribute12 IS NULL)))
356 AND ( (Recinfo.attribute13 = X_Attribute13)
357 OR ( (Recinfo.attribute13 IS NULL)
358 AND (X_Attribute13 IS NULL)))
359 AND ( (Recinfo.attribute14 = X_Attribute14)
360 OR ( (Recinfo.attribute14 IS NULL)
361 AND (X_Attribute14 IS NULL)))
362 AND ( (Recinfo.attribute15 = X_Attribute15)
363 OR ( (Recinfo.attribute15 IS NULL)
364 AND (X_Attribute15 IS NULL)))
365 ) then
366 return;
367 else
368 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 END IF;
371 END lock_row;
372
373 FUNCTION update_row(X_Rowid VARCHAR2,
374 X_Parameter_Set_Id NUMBER,
375 X_Last_Update_Date DATE,
376 X_Last_Updated_By NUMBER,
377 X_Last_Update_Login NUMBER,
378 X_Parameter_Num NUMBER,
379 X_Data_Type VARCHAR2,
380 X_Parameter_Id NUMBER,
381 X_Currency_Type VARCHAR2,
382 X_Entered_Currency VARCHAR2,
383 X_Ledger_Currency VARCHAR2,
384 X_Period_Num NUMBER,
385 X_Fiscal_Year_Offset NUMBER,
386 X_Context VARCHAR2,
387 X_Attribute1 VARCHAR2,
388 X_Attribute2 VARCHAR2,
389 X_Attribute3 VARCHAR2,
390 X_Attribute4 VARCHAR2,
391 X_Attribute5 VARCHAR2,
395 X_Attribute9 VARCHAR2,
392 X_Attribute6 VARCHAR2,
393 X_Attribute7 VARCHAR2,
394 X_Attribute8 VARCHAR2,
396 X_Attribute10 VARCHAR2,
397 X_Attribute11 VARCHAR2,
398 X_Attribute12 VARCHAR2,
399 X_Attribute13 VARCHAR2,
400 X_Attribute14 VARCHAR2,
401 X_Attribute15 VARCHAR2
402 ) RETURN BOOLEAN IS
403 BEGIN
404 IF (dup_parameter_num(X_Parameter_Set_Id,
405 X_Parameter_Num,
406 X_Data_Type,
407 X_Rowid)) THEN
408 RETURN(FALSE);
409 END IF;
410
411 UPDATE rg_report_parameters
412 SET
413 parameter_set_id = X_Parameter_Set_Id,
414 last_update_date = X_Last_Update_Date,
415 last_updated_by = X_Last_Updated_By,
416 last_update_login = X_Last_Update_Login,
417 parameter_num = X_Parameter_Num,
418 data_type = X_Data_Type,
419 parameter_id = X_Parameter_Id,
420 currency_type = X_Currency_Type,
421 entered_currency = X_Entered_Currency,
422 ledger_currency = X_Ledger_Currency,
423 period_num = X_Period_Num,
424 fiscal_year_offset = X_Fiscal_Year_Offset,
425 context = X_Context,
426 attribute1 = X_Attribute1,
427 attribute2 = X_Attribute2,
428 attribute3 = X_Attribute3,
429 attribute4 = X_Attribute4,
430 attribute5 = X_Attribute5,
431 attribute6 = X_Attribute6,
432 attribute7 = X_Attribute7,
433 attribute8 = X_Attribute8,
434 attribute9 = X_Attribute9,
435 attribute10 = X_Attribute10,
436 attribute11 = X_Attribute11,
437 attribute12 = X_Attribute12,
438 attribute13 = X_Attribute13,
439 attribute14 = X_Attribute14,
440 attribute15 = X_Attribute15
441 WHERE rowid = X_rowid;
442
443 IF (SQL%NOTFOUND) then
444 RAISE NO_DATA_FOUND;
445 END IF;
446 RETURN(TRUE);
447 END update_row;
448
449 PROCEDURE delete_row(X_Rowid VARCHAR2) IS
450 BEGIN
451 DELETE FROM rg_report_parameters
452 WHERE rowid = X_Rowid;
453
454 IF (SQL%NOTFOUND) then
455 RAISE NO_DATA_FOUND;
456 END IF;
457 END delete_row;
458
459 END RG_REPORT_PARAMETERS_PKG;