[Home] [Help]
PACKAGE BODY: APPS.RG_DSS_VARIABLES_PKG
Source
1 PACKAGE BODY RG_DSS_VARIABLES_PKG as
2 /*$Header: rgidvarb.pls 120.3 2003/04/29 00:47:31 djogg ship $*/
3
4 /*** PUBLIC FUNCTIONS ***/
5
6 FUNCTION get_new_id RETURN NUMBER IS
7 new_id NUMBER;
8 BEGIN
9 SELECT rg_dss_variables_s.nextval
10 INTO new_id
11 FROM sys.dual;
12
13 RETURN(new_id);
14 END get_new_id;
15
16
17 FUNCTION num_dimensions(X_Variable_Id NUMBER) RETURN NUMBER IS
18 NumRecords NUMBER;
19 BEGIN
20 SELECT COUNT(variable_id)
21 INTO NumRecords
22 FROM rg_dss_var_dimensions
23 WHERE variable_id = X_Variable_Id;
24
25 RETURN(NumRecords);
26 END num_dimensions;
27
28
29 FUNCTION used_in_frozen_system(X_Variable_Id NUMBER) RETURN BOOLEAN IS
30 dummy NUMBER;
31 BEGIN
32 /* The line "svr.system_id > 0" is added in the query to force the query
33 to use the index on system_id and variable_id for svr. */
34 SELECT 1
35 INTO dummy
36 FROM dual
37 WHERE NOT EXISTS
38 (SELECT 1
39 FROM rg_dss_systems sys,
40 rg_dss_system_variables svr
41 WHERE svr.variable_id = X_Variable_Id
42 AND svr.system_id > 0
43 AND svr.system_id = sys.system_id
44 AND sys.freeze_flag = 'Y');
45 RETURN(FALSE);
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 RETURN(TRUE);
49 END used_in_frozen_system;
50
51
52 PROCEDURE check_for_details(X_Variable_Id NUMBER,
53 X_Level_Code VARCHAR2) IS
54 dummy NUMBER;
55 BEGIN
56
57 BEGIN
58 /* Raise exception if there is no time dimension defined for this variable */
59 SELECT 1
60 INTO dummy
61 FROM dual
62 WHERE NOT EXISTS
63 (SELECT 1
64 FROM rg_dss_var_dimensions vdm,
65 rg_dss_dimensions dim
66 WHERE vdm.variable_id = X_Variable_Id
67 AND vdm.dimension_id = dim.dimension_id
68 AND dim.dimension_type = 'T' );
69 FND_MESSAGE.set_name('RG', 'RG_DSS_DIMENSION_REQUIRED');
70 APP_EXCEPTION.raise_exception;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 NULL;
74 END;
75
76 IF (X_Level_Code = 'S') THEN
77 SELECT 1
78 INTO dummy
79 FROM dual
80 WHERE NOT EXISTS
81 (SELECT 1
82 FROM rg_dss_var_dimensions vdm,
83 rg_dss_dimensions dim
84 WHERE vdm.variable_id = X_Variable_Id
85 AND vdm.dimension_id = dim.dimension_id
86 AND dim.level_code = 'S' );
87
88 FND_MESSAGE.set_name('RG', 'RG_DSS_SUM_DIM_REQUIRED');
89 APP_EXCEPTION.raise_exception;
90 END IF;
91
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 NULL;
95 END check_for_details;
96
97
98 PROCEDURE check_unique_name(X_Rowid VARCHAR2, X_Name VARCHAR2) IS
99 dummy NUMBER;
100 BEGIN
101 SELECT 1
102 INTO dummy
103 FROM rg_dss_variables
104 WHERE name = X_Name
105 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
106
107 -- name already exists for a different variable: ERROR
108 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS');
109 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
110 APP_EXCEPTION.raise_exception;
111
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 -- name doesn't exist, so do nothing
115 NULL;
116 END check_unique_name;
117
118
119 PROCEDURE check_unique_object_name(X_Rowid VARCHAR2, X_Object_Name VARCHAR2) IS
120 dummy NUMBER;
121 BEGIN
122 SELECT 1
123 INTO dummy
124 FROM rg_dss_variables
125 WHERE object_name = X_Object_Name
126 AND ((X_Rowid IS NULL) OR (rowid <> X_Rowid));
127
128 -- name already exists for a different variable: ERROR
129 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS');
130 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_OBJECT_NAME', TRUE);
131 APP_EXCEPTION.raise_exception;
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 -- name doesn't exist, so do nothing
136 NULL;
137 END check_unique_object_name;
138
139
140 PROCEDURE check_references(X_Variable_Id NUMBER) IS
141 dummy NUMBER;
142 BEGIN
143 SELECT 1
144 INTO dummy
145 FROM dual
146 WHERE NOT EXISTS
147 (SELECT 1
148 FROM rg_dss_system_variables
149 WHERE variable_id = X_Variable_Id
150 );
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 FND_MESSAGE.set_name('RG','RG_DSS_REF_VARIABLE');
155 APP_EXCEPTION.raise_exception;
156 END check_references;
157
158 PROCEDURE generate_matching_struc(
159 X_Variable_Id NUMBER,
160 X_Chart_of_Account_Id NUMBER,
161 X_Segment1_Type IN OUT NOCOPY VARCHAR2,
162 X_Segment2_Type IN OUT NOCOPY VARCHAR2,
163 X_Segment3_Type IN OUT NOCOPY VARCHAR2,
164 X_Segment4_Type IN OUT NOCOPY VARCHAR2,
165 X_Segment5_Type IN OUT NOCOPY VARCHAR2,
166 X_Segment6_Type IN OUT NOCOPY VARCHAR2,
167 X_Segment7_Type IN OUT NOCOPY VARCHAR2,
168 X_Segment8_Type IN OUT NOCOPY VARCHAR2,
169 X_Segment9_Type IN OUT NOCOPY VARCHAR2,
170 X_Segment10_Type IN OUT NOCOPY VARCHAR2,
171 X_Segment11_Type IN OUT NOCOPY VARCHAR2,
172 X_Segment12_Type IN OUT NOCOPY VARCHAR2,
173 X_Segment13_Type IN OUT NOCOPY VARCHAR2,
174 X_Segment14_Type IN OUT NOCOPY VARCHAR2,
175 X_Segment15_Type IN OUT NOCOPY VARCHAR2,
176 X_Segment16_Type IN OUT NOCOPY VARCHAR2,
177 X_Segment17_Type IN OUT NOCOPY VARCHAR2,
178 X_Segment18_Type IN OUT NOCOPY VARCHAR2,
179 X_Segment19_Type IN OUT NOCOPY VARCHAR2,
180 X_Segment20_Type IN OUT NOCOPY VARCHAR2,
181 X_Segment21_Type IN OUT NOCOPY VARCHAR2,
182 X_Segment22_Type IN OUT NOCOPY VARCHAR2,
183 X_Segment23_Type IN OUT NOCOPY VARCHAR2,
184 X_Segment24_Type IN OUT NOCOPY VARCHAR2,
185 X_Segment25_Type IN OUT NOCOPY VARCHAR2,
186 X_Segment26_Type IN OUT NOCOPY VARCHAR2,
187 X_Segment27_Type IN OUT NOCOPY VARCHAR2,
188 X_Segment28_Type IN OUT NOCOPY VARCHAR2,
189 X_Segment29_Type IN OUT NOCOPY VARCHAR2,
190 X_Segment30_Type IN OUT NOCOPY VARCHAR2 ) IS
191 seg_name varchar2(30);
192 seg_value varchar2(25);
193
194 CURSOR coa_seg IS
195 SELECT application_column_name, 'ANY'
196 FROM fnd_id_flex_segments
197 WHERE application_id = 101
198 AND id_flex_code = 'GL#'
199 AND enabled_flag = 'Y'
200 AND id_flex_num = X_Chart_of_Account_Id;
201
202 BEGIN
203
204 OPEN coa_seg;
205
206 LOOP
207 FETCH coa_seg INTO seg_name,seg_value;
208 EXIT WHEN coa_seg%NOTFOUND;
209
210 BEGIN
211 SELECT decode(dim.level_code,'D','D','S','R','D')
212 INTO seg_value
213 FROM rg_dss_dimensions dim,
214 rg_dss_dim_segments ds,
215 rg_dss_var_dimensions vd
216 WHERE ds.dimension_id = vd.dimension_id
217 AND vd.variable_id = X_Variable_Id
218 AND ds.application_column_name = seg_name
219 AND vd.dimension_id = dim.dimension_id;
220
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 null;
224 WHEN TOO_MANY_ROWS THEN
225 FND_MESSAGE.set_name('RG', 'RG_DSS_DIM_DUP_SEG');
226 APP_EXCEPTION.RAISE_EXCEPTION;
227 WHEN OTHERS THEN
228 RAISE;
229 END;
230
231 BEGIN
232 SELECT 'DR'
233 INTO seg_value
234 FROM rg_dss_var_selections vs
235 WHERE
236 vs.variable_id = X_Variable_Id
237 AND vs.application_column_name = seg_name;
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 null;
241 WHEN OTHERS THEN
242 RAISE;
243 END;
244
245 IF (seg_name = 'SEGMENT1') THEN
246 X_Segment1_Type := seg_value;
247 ELSIF (seg_name = 'SEGMENT2') THEN
248 X_Segment2_Type := seg_value;
249 ELSIF (seg_name = 'SEGMENT3') THEN
250 X_Segment3_Type := seg_value;
251 ELSIF (seg_name = 'SEGMENT4') THEN
252 X_Segment4_Type := seg_value;
253 ELSIF (seg_name = 'SEGMENT5') THEN
254 X_Segment5_Type := seg_value;
255 ELSIF (seg_name = 'SEGMENT6') THEN
256 X_Segment6_Type := seg_value;
257 ELSIF (seg_name = 'SEGMENT7') THEN
258 X_Segment7_Type := seg_value;
259 ELSIF (seg_name = 'SEGMENT8') THEN
260 X_Segment8_Type := seg_value;
261 ELSIF (seg_name = 'SEGMENT9') THEN
262 X_Segment9_Type := seg_value;
263 ELSIF (seg_name = 'SEGMENT10') THEN
264 X_Segment10_Type := seg_value;
265 ELSIF (seg_name = 'SEGMENT11') THEN
266 X_Segment11_Type := seg_value;
267 ELSIF (seg_name = 'SEGMENT12') THEN
268 X_Segment12_Type := seg_value;
269 ELSIF (seg_name = 'SEGMENT13') THEN
270 X_Segment13_Type := seg_value;
271 ELSIF (seg_name = 'SEGMENT14') THEN
272 X_Segment14_Type := seg_value;
273 ELSIF (seg_name = 'SEGMENT15') THEN
274 X_Segment15_Type := seg_value;
275 ELSIF (seg_name = 'SEGMENT16') THEN
276 X_Segment16_Type := seg_value;
277 ELSIF (seg_name = 'SEGMENT17') THEN
278 X_Segment17_Type := seg_value;
279 ELSIF (seg_name = 'SEGMENT18') THEN
280 X_Segment18_Type := seg_value;
281 ELSIF (seg_name = 'SEGMENT19') THEN
282 X_Segment19_Type := seg_value;
283 ELSIF (seg_name = 'SEGMENT20') THEN
284 X_Segment20_Type := seg_value;
285 ELSIF (seg_name = 'SEGMENT21') THEN
286 X_Segment21_Type := seg_value;
287 ELSIF (seg_name = 'SEGMENT22') THEN
288 X_Segment22_Type := seg_value;
289 ELSIF (seg_name = 'SEGMENT23') THEN
290 X_Segment23_Type := seg_value;
291 ELSIF (seg_name = 'SEGMENT24') THEN
292 X_Segment24_Type := seg_value;
293 ELSIF (seg_name = 'SEGMENT25') THEN
294 X_Segment25_Type := seg_value;
295 ELSIF (seg_name = 'SEGMENT26') THEN
296 X_Segment26_Type := seg_value;
297 ELSIF (seg_name = 'SEGMENT27') THEN
298 X_Segment27_Type := seg_value;
299 ELSIF (seg_name = 'SEGMENT28') THEN
300 X_Segment28_Type := seg_value;
301 ELSIF (seg_name = 'SEGMENT29') THEN
302 X_Segment29_Type := seg_value;
303 ELSIF (seg_name = 'SEGMENT30') THEN
304 X_Segment30_Type := seg_value;
305 END IF;
306
307 END LOOP;
308
309 CLOSE coa_seg;
310
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN
313 null;
314 WHEN app_exceptions.application_exception THEN
315 RAISE;
316 WHEN OTHERS THEN
317 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
318 fnd_message.set_token('PROCEDURE',
319 'RG_DSS_VARIABLES_PKG.generate_matching_struc');
320 RAISE;
321 END generate_matching_struc;
322
323 PROCEDURE insert_row(X_Rowid IN OUT NOCOPY VARCHAR2,
324 X_Variable_Id IN OUT NOCOPY NUMBER,
325 X_Name VARCHAR2,
326 X_Object_Name VARCHAR2,
327 X_Column_Label VARCHAR2,
328 X_Balance_Type VARCHAR2,
329 X_Currency_Type VARCHAR2,
330 X_Currency_Code VARCHAR2,
331 X_Id_Flex_Code VARCHAR2,
332 X_Id_Flex_Num NUMBER,
336 X_Level_Code VARCHAR2,
333 X_Ledger_Id NUMBER,
334 X_Budget_Version_Id NUMBER,
335 X_Encumbrance_Type_Id NUMBER,
337 X_Status_Code VARCHAR2,
338 X_Description VARCHAR2,
339 X_Last_Update_Date DATE,
340 X_Last_Updated_By NUMBER,
341 X_Last_Update_Login NUMBER,
342 X_Creation_Date DATE,
343 X_Created_By NUMBER,
344 X_Context VARCHAR2,
345 X_Attribute1 VARCHAR2,
346 X_Attribute2 VARCHAR2,
347 X_Attribute3 VARCHAR2,
348 X_Attribute4 VARCHAR2,
349 X_Attribute5 VARCHAR2,
350 X_Attribute6 VARCHAR2,
351 X_Attribute7 VARCHAR2,
352 X_Attribute8 VARCHAR2,
353 X_Attribute9 VARCHAR2,
354 X_Attribute10 VARCHAR2,
355 X_Attribute11 VARCHAR2,
356 X_Attribute12 VARCHAR2,
357 X_Attribute13 VARCHAR2,
358 X_Attribute14 VARCHAR2,
359 X_Attribute15 VARCHAR2,
360 X_Segment1_Type IN OUT NOCOPY VARCHAR2,
361 X_Segment2_Type IN OUT NOCOPY VARCHAR2,
362 X_Segment3_Type IN OUT NOCOPY VARCHAR2,
363 X_Segment4_Type IN OUT NOCOPY VARCHAR2,
364 X_Segment5_Type IN OUT NOCOPY VARCHAR2,
365 X_Segment6_Type IN OUT NOCOPY VARCHAR2,
366 X_Segment7_Type IN OUT NOCOPY VARCHAR2,
367 X_Segment8_Type IN OUT NOCOPY VARCHAR2,
368 X_Segment9_Type IN OUT NOCOPY VARCHAR2,
369 X_Segment10_Type IN OUT NOCOPY VARCHAR2,
370 X_Segment11_Type IN OUT NOCOPY VARCHAR2,
371 X_Segment12_Type IN OUT NOCOPY VARCHAR2,
372 X_Segment13_Type IN OUT NOCOPY VARCHAR2,
373 X_Segment14_Type IN OUT NOCOPY VARCHAR2,
374 X_Segment15_Type IN OUT NOCOPY VARCHAR2,
375 X_Segment16_Type IN OUT NOCOPY VARCHAR2,
376 X_Segment17_Type IN OUT NOCOPY VARCHAR2,
377 X_Segment18_Type IN OUT NOCOPY VARCHAR2,
378 X_Segment19_Type IN OUT NOCOPY VARCHAR2,
379 X_Segment20_Type IN OUT NOCOPY VARCHAR2,
380 X_Segment21_Type IN OUT NOCOPY VARCHAR2,
381 X_Segment22_Type IN OUT NOCOPY VARCHAR2,
382 X_Segment23_Type IN OUT NOCOPY VARCHAR2,
383 X_Segment24_Type IN OUT NOCOPY VARCHAR2,
384 X_Segment25_Type IN OUT NOCOPY VARCHAR2,
385 X_Segment26_Type IN OUT NOCOPY VARCHAR2,
386 X_Segment27_Type IN OUT NOCOPY VARCHAR2,
387 X_Segment28_Type IN OUT NOCOPY VARCHAR2,
388 X_Segment29_Type IN OUT NOCOPY VARCHAR2,
389 X_Segment30_Type IN OUT NOCOPY VARCHAR2
390 ) IS
391 CURSOR C IS
392 SELECT rowid
393 FROM rg_dss_variables
394 WHERE variable_id = X_Variable_Id;
395
396 BEGIN
397 check_unique_name(X_Rowid, X_Name);
398 check_unique_object_name(X_Rowid, X_Object_Name);
399
400 IF (X_Variable_Id IS NULL) THEN
401 X_Variable_Id := get_new_id;
402 END IF;
403
404 /* Raise exception if there is no dimension defined for this variable */
405 check_for_details(X_Variable_Id,X_Level_Code);
406
407 IF (X_Level_Code = 'S') THEN
408 generate_matching_struc( X_Variable_Id,
409 X_Id_Flex_Num,
410 X_Segment1_Type,
411 X_Segment2_Type,
412 X_Segment3_Type,
413 X_Segment4_Type,
414 X_Segment5_Type,
415 X_Segment6_Type,
416 X_Segment7_Type,
417 X_Segment8_Type,
418 X_Segment9_Type,
419 X_Segment10_Type,
420 X_Segment11_Type,
421 X_Segment12_Type,
422 X_Segment13_Type,
423 X_Segment14_Type,
424 X_Segment15_Type,
425 X_Segment16_Type,
426 X_Segment17_Type,
427 X_Segment18_Type,
428 X_Segment19_Type,
429 X_Segment20_Type,
430 X_Segment21_Type,
431 X_Segment22_Type,
435 X_Segment26_Type,
432 X_Segment23_Type,
433 X_Segment24_Type,
434 X_Segment25_Type,
436 X_Segment27_Type,
437 X_Segment28_Type,
438 X_Segment29_Type,
439 X_Segment30_Type);
440 END IF;
441
442 INSERT INTO rg_dss_variables(
443 variable_id,
444 name,
445 object_name,
446 column_label,
447 balance_type,
448 currency_type,
449 currency_code,
450 id_flex_code,
451 id_flex_num,
452 ledger_id,
453 budget_version_id,
454 encumbrance_type_id,
455 level_code,
456 status_code,
457 description,
458 last_update_date,
459 last_updated_by,
460 last_update_login,
461 creation_date,
462 created_by,
463 context,
464 attribute1,
465 attribute2,
466 attribute3,
467 attribute4,
468 attribute5,
469 attribute6,
470 attribute7,
471 attribute8,
472 attribute9,
473 attribute10,
474 attribute11,
475 attribute12,
476 attribute13,
477 attribute14,
478 attribute15,
479 segment1_type,
480 segment2_type,
481 segment3_type,
482 segment4_type,
483 segment5_type,
484 segment6_type,
485 segment7_type,
486 segment8_type,
487 segment9_type,
488 segment10_type,
489 segment11_type,
490 segment12_type,
491 segment13_type,
492 segment14_type,
493 segment15_type,
494 segment16_type,
495 segment17_type,
496 segment18_type,
497 segment19_type,
498 segment20_type,
499 segment21_type,
500 segment22_type,
501 segment23_type,
502 segment24_type,
503 segment25_type,
504 segment26_type,
505 segment27_type,
506 segment28_type,
507 segment29_type,
508 segment30_type
509 ) VALUES (
510 X_Variable_Id,
511 X_Name,
512 X_Object_Name,
513 X_Column_Label,
514 X_Balance_Type,
515 X_Currency_Type,
516 X_Currency_Code,
517 X_Id_Flex_Code,
518 X_Id_Flex_Num,
519 X_Ledger_Id,
520 X_Budget_Version_Id,
521 X_Encumbrance_Type_Id,
522 X_Level_Code,
523 X_Status_Code,
524 X_Description,
525 X_Last_Update_Date,
526 X_Last_Updated_By,
527 X_Last_Update_Login,
528 X_Creation_Date,
529 X_Created_By,
530 X_Context,
531 X_Attribute1,
532 X_Attribute2,
533 X_Attribute3,
534 X_Attribute4,
535 X_Attribute5,
536 X_Attribute6,
537 X_Attribute7,
538 X_Attribute8,
539 X_Attribute9,
540 X_Attribute10,
541 X_Attribute11,
542 X_Attribute12,
543 X_Attribute13,
544 X_Attribute14,
545 X_Attribute15,
546 X_Segment1_Type,
547 X_Segment2_Type,
548 X_Segment3_Type,
549 X_Segment4_Type,
550 X_Segment5_Type,
551 X_Segment6_Type,
552 X_Segment7_Type,
553 X_Segment8_Type,
554 X_Segment9_Type,
555 X_Segment10_Type,
556 X_Segment11_Type,
557 X_Segment12_Type,
558 X_Segment13_Type,
559 X_Segment14_Type,
560 X_Segment15_Type,
561 X_Segment16_Type,
562 X_Segment17_Type,
563 X_Segment18_Type,
564 X_Segment19_Type,
565 X_Segment20_Type,
566 X_Segment21_Type,
567 X_Segment22_Type,
568 X_Segment23_Type,
569 X_Segment24_Type,
570 X_Segment25_Type,
571 X_Segment26_Type,
572 X_Segment27_Type,
573 X_Segment28_Type,
574 X_Segment29_Type,
575 X_Segment30_Type
576 );
577
578
579 OPEN C;
580 FETCH C INTO X_Rowid;
581
582 IF (C%NOTFOUND) THEN
583 CLOSE C;
584 RAISE NO_DATA_FOUND;
585 END IF;
586
587 CLOSE C;
588
589 END Insert_Row;
590
591
592 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
593 X_Variable_Id NUMBER,
594 X_Name VARCHAR2,
595 X_Object_Name VARCHAR2,
596 X_Column_Label VARCHAR2,
597 X_Balance_Type VARCHAR2,
598 X_Currency_Type VARCHAR2,
599 X_Currency_Code VARCHAR2,
600 X_Id_Flex_Code VARCHAR2,
601 X_Id_Flex_Num NUMBER,
602 X_Ledger_Id NUMBER,
606 X_Status_Code VARCHAR2,
603 X_Budget_Version_Id NUMBER,
604 X_Encumbrance_Type_Id NUMBER,
605 X_Level_Code VARCHAR2,
607 X_Description VARCHAR2,
608 X_Context VARCHAR2,
609 X_Attribute1 VARCHAR2,
610 X_Attribute2 VARCHAR2,
611 X_Attribute3 VARCHAR2,
612 X_Attribute4 VARCHAR2,
613 X_Attribute5 VARCHAR2,
614 X_Attribute6 VARCHAR2,
615 X_Attribute7 VARCHAR2,
616 X_Attribute8 VARCHAR2,
617 X_Attribute9 VARCHAR2,
618 X_Attribute10 VARCHAR2,
619 X_Attribute11 VARCHAR2,
620 X_Attribute12 VARCHAR2,
621 X_Attribute13 VARCHAR2,
622 X_Attribute14 VARCHAR2,
623 X_Attribute15 VARCHAR2,
624 X_Segment1_Type VARCHAR2,
625 X_Segment2_Type VARCHAR2,
626 X_Segment3_Type VARCHAR2,
627 X_Segment4_Type VARCHAR2,
628 X_Segment5_Type VARCHAR2,
629 X_Segment6_Type VARCHAR2,
630 X_Segment7_Type VARCHAR2,
631 X_Segment8_Type VARCHAR2,
632 X_Segment9_Type VARCHAR2,
633 X_Segment10_Type VARCHAR2,
634 X_Segment11_Type VARCHAR2,
635 X_Segment12_Type VARCHAR2,
636 X_Segment13_Type VARCHAR2,
637 X_Segment14_Type VARCHAR2,
638 X_Segment15_Type VARCHAR2,
639 X_Segment16_Type VARCHAR2,
640 X_Segment17_Type VARCHAR2,
641 X_Segment18_Type VARCHAR2,
642 X_Segment19_Type VARCHAR2,
643 X_Segment20_Type VARCHAR2,
644 X_Segment21_Type VARCHAR2,
645 X_Segment22_Type VARCHAR2,
646 X_Segment23_Type VARCHAR2,
647 X_Segment24_Type VARCHAR2,
648 X_Segment25_Type VARCHAR2,
649 X_Segment26_Type VARCHAR2,
650 X_Segment27_Type VARCHAR2,
651 X_Segment28_Type VARCHAR2,
652 X_Segment29_Type VARCHAR2,
653 X_Segment30_Type VARCHAR2
654 ) IS
655 CURSOR C IS
656 SELECT *
657 FROM rg_dss_variables
658 WHERE rowid = X_Rowid
659 FOR UPDATE of variable_id NOWAIT;
660 Recinfo C%ROWTYPE;
661 BEGIN
662 OPEN C;
663 FETCH C INTO Recinfo;
664 IF (C%NOTFOUND) THEN
665 CLOSE C;
666 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
667 APP_EXCEPTION.RAISE_EXCEPTION;
668 END IF;
669 CLOSE C;
670
671 IF (
672 (Recinfo.variable_id = X_Variable_Id)
673 AND (Recinfo.name = X_Name)
674 AND ( (Recinfo.object_name = X_Object_Name)
675 OR ( (Recinfo.object_name IS NULL)
676 AND (X_Object_Name IS NULL)))
677 AND ( (Recinfo.column_label = X_Column_Label)
678 OR ( (Recinfo.column_label IS NULL)
679 AND (X_Column_Label IS NULL)))
680 AND ( (Recinfo.balance_type = X_Balance_Type)
681 OR ( (Recinfo.balance_type IS NULL)
682 AND (X_Balance_Type IS NULL)))
683 AND ( (Recinfo.currency_type = X_Currency_Type)
684 OR ( (Recinfo.currency_type IS NULL)
685 AND (X_Currency_Type IS NULL)))
686 AND ( (Recinfo.currency_code = X_Currency_Code)
687 OR ( (Recinfo.currency_code IS NULL)
688 AND (X_Currency_Code IS NULL)))
689 AND ( (Recinfo.id_flex_code = X_Id_Flex_Code)
690 OR ( (Recinfo.id_flex_code IS NULL)
691 AND (X_Id_Flex_Code IS NULL)))
692 AND ( (Recinfo.id_flex_num = X_Id_Flex_Num)
693 OR ( (Recinfo.id_flex_num IS NULL)
694 AND (X_Id_Flex_Num IS NULL)))
695 AND ( (Recinfo.ledger_id = X_Ledger_Id)
696 OR ( (Recinfo.ledger_id IS NULL)
697 AND (X_Ledger_Id IS NULL)))
698 AND ( (Recinfo.budget_version_id = X_Budget_Version_Id)
699 OR ( (Recinfo.budget_version_id IS NULL)
700 AND (X_Budget_Version_Id IS NULL)))
701 AND ( (Recinfo.encumbrance_type_id = X_Encumbrance_Type_Id)
702 OR ( (Recinfo.encumbrance_type_id IS NULL)
703 AND (X_Encumbrance_Type_Id IS NULL)))
704 AND ( (Recinfo.level_code = X_Level_Code)
705 OR ( (Recinfo.level_code IS NULL)
706 AND (X_Level_Code IS NULL)))
707 AND ( (Recinfo.status_code = X_Status_Code)
708 OR ( (Recinfo.status_code IS NULL)
709 AND (X_Status_Code IS NULL)))
710 AND ( (Recinfo.description = X_Description)
711 OR ( (Recinfo.description IS NULL)
712 AND (X_Description IS NULL)))
713 AND ( (Recinfo.context = X_Context)
714 OR ( (Recinfo.context IS NULL)
715 AND (X_Context IS NULL)))
716 AND ( (Recinfo.attribute1 = X_Attribute1)
717 OR ( (Recinfo.attribute1 IS NULL)
718 AND (X_Attribute1 IS NULL)))
719 AND ( (Recinfo.attribute2 = X_Attribute2)
720 OR ( (Recinfo.attribute2 IS NULL)
721 AND (X_Attribute2 IS NULL)))
722 AND ( (Recinfo.attribute3 = X_Attribute3)
723 OR ( (Recinfo.attribute3 IS NULL)
727 AND (X_Attribute4 IS NULL)))
724 AND (X_Attribute3 IS NULL)))
725 AND ( (Recinfo.attribute4 = X_Attribute4)
726 OR ( (Recinfo.attribute4 IS NULL)
728 AND ( (Recinfo.attribute5 = X_Attribute5)
729 OR ( (Recinfo.attribute5 IS NULL)
730 AND (X_Attribute5 IS NULL)))
731 AND ( (Recinfo.attribute6 = X_Attribute6)
732 OR ( (Recinfo.attribute6 IS NULL)
733 AND (X_Attribute6 IS NULL)))
734 AND ( (Recinfo.attribute7 = X_Attribute7)
735 OR ( (Recinfo.attribute7 IS NULL)
736 AND (X_Attribute7 IS NULL)))
737 AND ( (Recinfo.attribute8 = X_Attribute8)
738 OR ( (Recinfo.attribute8 IS NULL)
739 AND (X_Attribute8 IS NULL)))
740 AND ( (Recinfo.attribute9 = X_Attribute9)
741 OR ( (Recinfo.attribute9 IS NULL)
742 AND (X_Attribute9 IS NULL)))
743 AND ( (Recinfo.attribute10 = X_Attribute10)
744 OR ( (Recinfo.attribute10 IS NULL)
745 AND (X_Attribute10 IS NULL)))
746 AND ( (Recinfo.attribute11 = X_Attribute11)
747 OR ( (Recinfo.attribute11 IS NULL)
748 AND (X_Attribute11 IS NULL)))
749 AND ( (Recinfo.attribute12 = X_Attribute12)
750 OR ( (Recinfo.attribute12 IS NULL)
751 AND (X_Attribute12 IS NULL)))
752 AND ( (Recinfo.attribute13 = X_Attribute13)
753 OR ( (Recinfo.attribute13 IS NULL)
754 AND (X_Attribute13 IS NULL)))
755 AND ( (Recinfo.attribute14 = X_Attribute14)
756 OR ( (Recinfo.attribute14 IS NULL)
757 AND (X_Attribute14 IS NULL)))
758 AND ( (Recinfo.attribute15 = X_Attribute15)
759 OR ( (Recinfo.attribute15 IS NULL)
760 AND (X_Attribute15 IS NULL)))
761 AND ( (Recinfo.segment1_type = X_Segment1_Type)
762 OR ( (Recinfo.segment1_type IS NULL)
763 AND (X_Segment1_Type IS NULL)))
764 AND ( (Recinfo.segment2_type = X_Segment2_Type)
765 OR ( (Recinfo.segment2_type IS NULL)
766 AND (X_Segment2_Type IS NULL)))
767 AND ( (Recinfo.segment3_type = X_Segment3_Type)
768 OR ( (Recinfo.segment3_type IS NULL)
769 AND (X_Segment3_Type IS NULL)))
770 AND ( (Recinfo.segment4_type = X_Segment4_Type)
771 OR ( (Recinfo.segment4_type IS NULL)
772 AND (X_Segment4_Type IS NULL)))
773 AND ( (Recinfo.segment5_type = X_Segment5_Type)
774 OR ( (Recinfo.segment5_type IS NULL)
775 AND (X_Segment5_Type IS NULL)))
776 AND ( (Recinfo.segment6_type = X_Segment6_Type)
777 OR ( (Recinfo.segment6_type IS NULL)
778 AND (X_Segment6_Type IS NULL)))
779 AND ( (Recinfo.segment7_type = X_Segment7_Type)
780 OR ( (Recinfo.segment7_type IS NULL)
781 AND (X_Segment7_Type IS NULL)))
782 AND ( (Recinfo.segment8_type = X_Segment8_Type)
783 OR ( (Recinfo.segment8_type IS NULL)
784 AND (X_Segment8_Type IS NULL)))
785 AND ( (Recinfo.segment9_type = X_Segment9_Type)
786 OR ( (Recinfo.segment9_type IS NULL)
787 AND (X_Segment9_Type IS NULL)))
788 AND ( (Recinfo.segment10_type = X_Segment10_Type)
789 OR ( (Recinfo.segment10_type IS NULL)
790 AND (X_Segment10_Type IS NULL)))
791 AND ( (Recinfo.segment11_type = X_Segment11_Type)
792 OR ( (Recinfo.segment11_type IS NULL)
793 AND (X_Segment11_Type IS NULL)))
794 AND ( (Recinfo.segment12_type = X_Segment12_Type)
795 OR ( (Recinfo.segment12_type IS NULL)
796 AND (X_Segment12_Type IS NULL)))
797 AND ( (Recinfo.segment13_type = X_Segment13_Type)
798 OR ( (Recinfo.segment13_type IS NULL)
799 AND (X_Segment13_Type IS NULL)))
800 AND ( (Recinfo.segment14_type = X_Segment14_Type)
801 OR ( (Recinfo.segment14_type IS NULL)
802 AND (X_Segment14_Type IS NULL)))
803 AND ( (Recinfo.segment15_type = X_Segment15_Type)
804 OR ( (Recinfo.segment15_type IS NULL)
805 AND (X_Segment15_Type IS NULL)))
806 AND ( (Recinfo.segment16_type = X_Segment16_Type)
807 OR ( (Recinfo.segment16_type IS NULL)
808 AND (X_Segment16_Type IS NULL)))
809 AND ( (Recinfo.segment17_type = X_Segment17_Type)
810 OR ( (Recinfo.segment17_type IS NULL)
811 AND (X_Segment17_Type IS NULL)))
812 AND ( (Recinfo.segment18_type = X_Segment18_Type)
813 OR ( (Recinfo.segment18_type IS NULL)
814 AND (X_Segment18_Type IS NULL)))
815 AND ( (Recinfo.segment19_type = X_Segment19_Type)
816 OR ( (Recinfo.segment19_type IS NULL)
817 AND (X_Segment19_Type IS NULL)))
818 AND ( (Recinfo.segment20_type = X_Segment20_Type)
819 OR ( (Recinfo.segment20_type IS NULL)
820 AND (X_Segment20_Type IS NULL)))
821 AND ( (Recinfo.segment21_type = X_Segment21_Type)
822 OR ( (Recinfo.segment21_type IS NULL)
823 AND (X_Segment21_Type IS NULL)))
824 AND ( (Recinfo.segment22_type = X_Segment22_Type)
825 OR ( (Recinfo.segment22_type IS NULL)
826 AND (X_Segment22_Type IS NULL)))
827 AND ( (Recinfo.segment23_type = X_Segment23_Type)
828 OR ( (Recinfo.segment23_type IS NULL)
829 AND (X_Segment23_Type IS NULL)))
830 AND ( (Recinfo.segment24_type = X_Segment24_Type)
831 OR ( (Recinfo.segment24_type IS NULL)
832 AND (X_Segment24_Type IS NULL)))
833 AND ( (Recinfo.segment25_type = X_Segment25_Type)
837 OR ( (Recinfo.segment26_type IS NULL)
834 OR ( (Recinfo.segment25_type IS NULL)
835 AND (X_Segment25_Type IS NULL)))
836 AND ( (Recinfo.segment26_type = X_Segment26_Type)
838 AND (X_Segment26_Type IS NULL)))
839 AND ( (Recinfo.segment27_type = X_Segment27_Type)
840 OR ( (Recinfo.segment27_type IS NULL)
841 AND (X_Segment27_Type IS NULL)))
842 AND ( (Recinfo.segment28_type = X_Segment28_Type)
843 OR ( (Recinfo.segment28_type IS NULL)
844 AND (X_Segment28_Type IS NULL)))
845 AND ( (Recinfo.segment29_type = X_Segment29_Type)
846 OR ( (Recinfo.segment29_type IS NULL)
847 AND (X_Segment29_Type IS NULL)))
848 AND ( (Recinfo.segment30_type = X_Segment30_Type)
849 OR ( (Recinfo.segment30_type IS NULL)
850 AND (X_Segment30_Type IS NULL)))
851 ) THEN
852 RETURN;
853 ELSE
854 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
855 APP_EXCEPTION.RAISE_EXCEPTION;
856 END IF;
857 END Lock_Row;
858
859
860 PROCEDURE Update_Row(X_Rowid VARCHAR2,
861 X_Variable_Id NUMBER,
862 X_Name VARCHAR2,
863 X_Object_Name VARCHAR2,
864 X_Column_Label VARCHAR2,
865 X_Balance_Type VARCHAR2,
866 X_Currency_Type VARCHAR2,
867 X_Currency_Code VARCHAR2,
868 X_Id_Flex_Code VARCHAR2,
869 X_Id_Flex_Num NUMBER,
870 X_Ledger_Id NUMBER,
871 X_Budget_Version_Id NUMBER,
872 X_Encumbrance_Type_Id NUMBER,
873 X_Level_Code VARCHAR2,
874 X_Status_Code VARCHAR2,
875 X_Description VARCHAR2,
876 X_Last_Update_Date DATE,
877 X_Last_Updated_By NUMBER,
878 X_Last_Update_Login NUMBER,
879 X_Context VARCHAR2,
880 X_Attribute1 VARCHAR2,
881 X_Attribute2 VARCHAR2,
882 X_Attribute3 VARCHAR2,
883 X_Attribute4 VARCHAR2,
884 X_Attribute5 VARCHAR2,
885 X_Attribute6 VARCHAR2,
886 X_Attribute7 VARCHAR2,
887 X_Attribute8 VARCHAR2,
888 X_Attribute9 VARCHAR2,
889 X_Attribute10 VARCHAR2,
890 X_Attribute11 VARCHAR2,
891 X_Attribute12 VARCHAR2,
892 X_Attribute13 VARCHAR2,
893 X_Attribute14 VARCHAR2,
894 X_Attribute15 VARCHAR2,
895 X_Segment1_Type IN OUT NOCOPY VARCHAR2,
896 X_Segment2_Type IN OUT NOCOPY VARCHAR2,
897 X_Segment3_Type IN OUT NOCOPY VARCHAR2,
898 X_Segment4_Type IN OUT NOCOPY VARCHAR2,
899 X_Segment5_Type IN OUT NOCOPY VARCHAR2,
900 X_Segment6_Type IN OUT NOCOPY VARCHAR2,
901 X_Segment7_Type IN OUT NOCOPY VARCHAR2,
902 X_Segment8_Type IN OUT NOCOPY VARCHAR2,
903 X_Segment9_Type IN OUT NOCOPY VARCHAR2,
904 X_Segment10_Type IN OUT NOCOPY VARCHAR2,
905 X_Segment11_Type IN OUT NOCOPY VARCHAR2,
906 X_Segment12_Type IN OUT NOCOPY VARCHAR2,
907 X_Segment13_Type IN OUT NOCOPY VARCHAR2,
908 X_Segment14_Type IN OUT NOCOPY VARCHAR2,
909 X_Segment15_Type IN OUT NOCOPY VARCHAR2,
910 X_Segment16_Type IN OUT NOCOPY VARCHAR2,
911 X_Segment17_Type IN OUT NOCOPY VARCHAR2,
912 X_Segment18_Type IN OUT NOCOPY VARCHAR2,
913 X_Segment19_Type IN OUT NOCOPY VARCHAR2,
914 X_Segment20_Type IN OUT NOCOPY VARCHAR2,
915 X_Segment21_Type IN OUT NOCOPY VARCHAR2,
916 X_Segment22_Type IN OUT NOCOPY VARCHAR2,
917 X_Segment23_Type IN OUT NOCOPY VARCHAR2,
918 X_Segment24_Type IN OUT NOCOPY VARCHAR2,
919 X_Segment25_Type IN OUT NOCOPY VARCHAR2,
920 X_Segment26_Type IN OUT NOCOPY VARCHAR2,
921 X_Segment27_Type IN OUT NOCOPY VARCHAR2,
922 X_Segment28_Type IN OUT NOCOPY VARCHAR2,
923 X_Segment29_Type IN OUT NOCOPY VARCHAR2,
924 X_Segment30_Type IN OUT NOCOPY VARCHAR2
925 ) IS
926 BEGIN
927
928 IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
932 APP_EXCEPTION.raise_exception;
929 -- can't modify a variable that is used in a frozen system
930 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
931 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
933 END IF;
934
935 -- primarily check for summary dimension
936 check_for_details(X_Variable_Id,X_Level_Code);
937
938 IF (X_Level_Code = 'S') THEN
939 generate_matching_struc( X_Variable_Id,
940 X_Id_Flex_Num,
941 X_Segment1_Type,
942 X_Segment2_Type,
943 X_Segment3_Type,
944 X_Segment4_Type,
945 X_Segment5_Type,
946 X_Segment6_Type,
947 X_Segment7_Type,
948 X_Segment8_Type,
949 X_Segment9_Type,
950 X_Segment10_Type,
951 X_Segment11_Type,
952 X_Segment12_Type,
953 X_Segment13_Type,
954 X_Segment14_Type,
955 X_Segment15_Type,
956 X_Segment16_Type,
957 X_Segment17_Type,
958 X_Segment18_Type,
959 X_Segment19_Type,
960 X_Segment20_Type,
961 X_Segment21_Type,
962 X_Segment22_Type,
963 X_Segment23_Type,
964 X_Segment24_Type,
965 X_Segment25_Type,
966 X_Segment26_Type,
967 X_Segment27_Type,
968 X_Segment28_Type,
969 X_Segment29_Type,
970 X_Segment30_Type);
971 END IF;
972
973 UPDATE rg_dss_variables
974 SET
975 variable_id = X_Variable_Id,
976 name = X_Name,
977 object_name = X_Object_Name,
978 column_label = X_Column_Label,
979 balance_type = X_Balance_Type,
980 currency_type = X_Currency_Type,
981 currency_code = X_Currency_Code,
982 id_flex_code = X_Id_Flex_Code,
983 id_flex_num = X_Id_Flex_Num,
984 ledger_id = X_Ledger_Id,
985 budget_version_id = X_Budget_Version_Id,
986 encumbrance_type_id = X_Encumbrance_Type_Id,
987 level_code = X_Level_Code,
988 status_code = X_Status_Code,
989 description = X_Description,
990 last_update_date = X_Last_Update_Date,
991 last_updated_by = X_Last_Updated_By,
992 last_update_login = X_Last_Update_Login,
993 context = X_Context,
994 attribute1 = X_Attribute1,
995 attribute2 = X_Attribute2,
996 attribute3 = X_Attribute3,
997 attribute4 = X_Attribute4,
998 attribute5 = X_Attribute5,
999 attribute6 = X_Attribute6,
1000 attribute7 = X_Attribute7,
1001 attribute8 = X_Attribute8,
1002 attribute9 = X_Attribute9,
1003 attribute10 = X_Attribute10,
1004 attribute11 = X_Attribute11,
1005 attribute12 = X_Attribute12,
1006 attribute13 = X_Attribute13,
1007 attribute14 = X_Attribute14,
1008 attribute15 = X_Attribute15,
1009 segment1_type = X_Segment1_Type,
1010 segment2_type = X_Segment2_Type,
1011 segment3_type = X_Segment3_Type,
1012 segment4_type = X_Segment4_Type,
1013 segment5_type = X_Segment5_Type,
1014 segment6_type = X_Segment6_Type,
1015 segment7_type = X_Segment7_Type,
1016 segment8_type = X_Segment8_Type,
1017 segment9_type = X_Segment9_Type,
1018 segment10_type = X_Segment10_Type,
1019 segment11_type = X_Segment11_Type,
1020 segment12_type = X_Segment12_Type,
1021 segment13_type = X_Segment13_Type,
1022 segment14_type = X_Segment14_Type,
1023 segment15_type = X_Segment15_Type,
1024 segment16_type = X_Segment16_Type,
1025 segment17_type = X_Segment17_Type,
1026 segment18_type = X_Segment18_Type,
1027 segment19_type = X_Segment19_Type,
1028 segment20_type = X_Segment20_Type,
1029 segment21_type = X_Segment21_Type,
1030 segment22_type = X_Segment22_Type,
1034 segment26_type = X_Segment26_Type,
1031 segment23_type = X_Segment23_Type,
1032 segment24_type = X_Segment24_Type,
1033 segment25_type = X_Segment25_Type,
1035 segment27_type = X_Segment27_Type,
1036 segment28_type = X_Segment28_Type,
1037 segment29_type = X_Segment29_Type,
1038 segment30_type = X_Segment30_Type
1039 WHERE rowid = X_rowid;
1040
1041 IF (SQL%NOTFOUND) THEN
1042 RAISE NO_DATA_FOUND;
1043 END IF;
1044
1045 END Update_Row;
1046
1047 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Variable_Id NUMBER) IS
1048 BEGIN
1049 IF (RG_DSS_VARIABLES_PKG.used_in_frozen_system(X_Variable_Id)) THEN
1050 -- can't modify a variable that is used in a frozen system
1051 FND_MESSAGE.set_name('RG', 'RG_DSS_FROZEN_SYSTEM');
1052 FND_MESSAGE.set_token('OBJECT', 'RG_DSS_VARIABLE', TRUE);
1053 APP_EXCEPTION.raise_exception;
1054 END IF;
1055
1056 DELETE FROM rg_dss_var_dimensions
1057 WHERE variable_id = X_Variable_Id;
1058
1059 DELETE FROM rg_dss_var_selections
1060 WHERE variable_id = X_Variable_Id;
1061
1062 DELETE FROM rg_dss_var_templates
1063 WHERE variable_id = X_Variable_Id;
1064
1065 DELETE FROM rg_dss_variables
1066 WHERE rowid = X_Rowid;
1067
1068 IF (SQL%NOTFOUND) THEN
1069 RAISE NO_DATA_FOUND;
1070 END IF;
1071
1072 END Delete_Row;
1073
1074
1075 END RG_DSS_VARIABLES_PKG;