[Home] [Help]
PACKAGE BODY: APPS.RG_REPORTS_PKG
Source
1 PACKAGE BODY RG_REPORTS_PKG AS
2 /* $Header: rgireptb.pls 120.7 2003/10/24 23:55:27 vtreiger ship $ */
3
4 --
5 -- PRIVATE
6 --
7 --
8 -- NAME
9 -- select_row
10 -- DESCRIPTION
11 -- find the record in rg_reports wiht report_id which stored in recinfo
12 -- PARAMETERS
13 -- 1. recinfo
14 --
15 --
16 PROCEDURE select_row (recinfo IN OUT NOCOPY rg_reports%ROWTYPE) IS
17 BEGIN
18 SELECT *
19 INTO recinfo
20 FROM rg_reports
21 WHERE report_id = recinfo.report_id;
22 END select_row;
23
24 --
25 -- PUBLIC FUNCTIONS
26 --
27 --
28
29 FUNCTION get_report_id RETURN NUMBER IS
30 new_sequence_number NUMBER;
31 BEGIN
32 SELECT rg_reports_s.nextval
33 INTO new_sequence_number
34 FROM dual;
35
36 RETURN(new_sequence_number);
37 END get_report_id;
38
39 --
40 -- NAME
41 -- report_is_used
42 --
43 -- DESCRIPTION
44 -- Check whether the report is used by a report request.
45 --
46 -- PARAMETERS
47 -- 1. Report ID
48 --
49 FUNCTION report_is_used(cur_report_id IN NUMBER)
50 RETURN BOOLEAN
51 IS
52 dummy NUMBER;
53 BEGIN
54 SELECT 1 INTO dummy FROM dual
55 WHERE NOT EXISTS
56 (SELECT 1
57 FROM rg_report_requests
58 WHERE report_id = cur_report_id
59 );
60 RETURN(FALSE);
61
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 RETURN(TRUE);
65 END report_is_used;
66
67 --
68 -- NAME
69 -- report_belongs_set
70 --
71 -- DESCRIPTION
72 -- Check whether the report is used by a report set.
73 --
74 -- PARAMETERS
75 -- 1. Report ID
76 --
77 FUNCTION report_belongs_set( cur_report_id IN NUMBER)
78 RETURN BOOLEAN
79 IS
80 dummy NUMBER;
81 BEGIN
82 SELECT 1 INTO dummy FROM dual
83 WHERE NOT EXISTS
84 (SELECT 1
85 FROM rg_report_requests
86 WHERE report_id = cur_report_id
87 AND report_set_id IS NOT NULL
88 );
89 RETURN(FALSE);
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 RETURN(TRUE);
94 END report_belongs_set;
95
96 --
97 -- NAME
98 -- check_dup_report_name
99 --
100 -- DESCRIPTION
101 -- Checking report name uniqueness
102 --
103 -- PARAMETERS
104 -- 1. Current application id
105 -- 2. Current Report ID
106 -- 3. New report name
107 --
108 FUNCTION check_dup_report_name( cur_application_id IN NUMBER,
109 cur_report_id IN NUMBER,
110 new_name IN VARCHAR2)
111 RETURN BOOLEAN
112 IS
113 dummy NUMBER;
114 BEGIN
115 SELECT 1 INTO dummy FROM dual
116 WHERE NOT EXISTS
117 (SELECT 1
118 FROM rg_reports
119 WHERE report_id <> cur_report_id
120 AND name = new_name
121 AND application_id = cur_application_id
122 );
123 RETURN(FALSE);
124
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 RETURN(TRUE);
128 END check_dup_report_name;
129
130 PROCEDURE get_adhoc_prefix(X_adhoc_prefix IN OUT NOCOPY VARCHAR2)
131 IS
132 BEGIN
133 if (X_adhoc_prefix is NULL) then
134 SELECT substr(meaning, 1, 16)
135 INTO X_adhoc_prefix
136 FROM rg_lookups
137 WHERE LOOKUP_TYPE='FSG_ADHOC_REPORT_NAME_PREFIX'
138 AND LOOKUP_CODE = 'ADHOC_PREFIX';
139 end if;
140 END get_adhoc_prefix;
141
142 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
143 X_Application_Id NUMBER,
144 X_Report_Id IN OUT NOCOPY NUMBER,
145 X_Last_Update_Date DATE,
146 X_Last_Updated_By NUMBER,
147 X_Last_Update_Login NUMBER,
148 X_Creation_Date DATE,
149 X_Created_By NUMBER,
150 X_Name IN OUT NOCOPY VARCHAR2,
151 X_Report_Title VARCHAR2,
152 X_Security_Flag VARCHAR2,
153 X_Column_Set_Id NUMBER,
154 X_Row_Set_Id NUMBER,
155 X_Rounding_Option VARCHAR2,
156 X_Output_Option VARCHAR2,
157 X_Report_Display_Set_Id NUMBER,
158 X_Content_Set_Id NUMBER,
159 X_Row_Order_Id NUMBER,
160 X_Parameter_Set_Id NUMBER,
161 X_Unit_Of_Measure_Id VARCHAR2,
162 X_Id_Flex_Code VARCHAR2,
163 X_Structure_Id NUMBER,
164 X_Segment_Override VARCHAR2,
165 X_Override_Alc_Ledger_Currency VARCHAR2,
166 X_Period_Set_Name VARCHAR2,
167 X_Minimum_Display_Level NUMBER,
168 X_Description VARCHAR2,
169 X_Context VARCHAR2,
170 X_Attribute1 VARCHAR2,
171 X_Attribute2 VARCHAR2,
172 X_Attribute3 VARCHAR2,
173 X_Attribute4 VARCHAR2,
174 X_Attribute5 VARCHAR2,
175 X_Attribute6 VARCHAR2,
176 X_Attribute7 VARCHAR2,
177 X_Attribute8 VARCHAR2,
178 X_Attribute9 VARCHAR2,
179 X_Attribute10 VARCHAR2,
180 X_Attribute11 VARCHAR2,
181 X_Attribute12 VARCHAR2,
182 X_Attribute13 VARCHAR2,
183 X_Attribute14 VARCHAR2,
184 X_Attribute15 VARCHAR2
185 ) IS
186 CURSOR C IS SELECT rowid
187 FROM rg_reports
188 WHERE report_id = X_Report_Id;
189 BEGIN
190
191 IF (X_Report_Id IS NULL) THEN
192 X_Report_Id := get_report_id;
193 END IF;
194
195 --
196 -- Find Ad Hoc report name
197 --
198 IF (X_Name is NULL) THEN
199 SELECT substr(meaning, 1, 16) || X_Report_Id
200 INTO X_Name
201 FROM rg_lookups
202 WHERE LOOKUP_TYPE='FSG_ADHOC_REPORT_NAME_PREFIX'
203 AND LOOKUP_CODE = 'ADHOC_PREFIX';
204 ELSE
205 IF (check_dup_report_name(X_Application_Id, X_Report_Id, X_Name)) THEN
206 FND_MESSAGE.set_name('RG', 'RG_FORMS_OBJECT_EXISTS');
207 FND_MESSAGE.set_token('OBJECT', 'RG_REPORT', Translate=>TRUE);
208 APP_EXCEPTION.raise_exception;
209 END IF;
210 END IF;
211
212 INSERT INTO rg_reports(
213 application_id,
214 report_id,
215 last_update_date,
216 last_updated_by,
217 last_update_login,
218 creation_date,
219 created_by,
220 name,
221 report_title,
222 security_flag,
223 column_set_id,
224 row_set_id,
225 rounding_option,
226 output_option,
227 report_display_set_id,
228 content_set_id,
229 row_order_id,
230 parameter_set_id,
231 unit_of_measure_id,
232 id_flex_code,
233 structure_id,
234 segment_override,
235 override_alc_ledger_currency,
236 period_set_name,
237 minimum_display_level,
238 description,
239 context,
240 attribute1,
241 attribute2,
242 attribute3,
243 attribute4,
244 attribute5,
245 attribute6,
246 attribute7,
247 attribute8,
248 attribute9,
249 attribute10,
250 attribute11,
251 attribute12,
252 attribute13,
253 attribute14,
254 attribute15
255 ) VALUES (
256 X_Application_Id,
257 X_Report_Id,
258 X_Last_Update_Date,
259 X_Last_Updated_By,
260 X_Last_Update_Login,
261 X_Creation_Date,
262 X_Created_By,
263 X_Name,
264 X_Report_Title,
265 X_Security_Flag,
266 X_Column_Set_Id,
267 X_Row_Set_Id,
268 X_Rounding_Option,
269 X_Output_Option,
270 X_Report_Display_Set_Id,
271 X_Content_Set_Id,
272 X_Row_Order_Id,
273 X_Parameter_Set_Id,
274 X_Unit_Of_Measure_Id,
275 X_Id_Flex_Code,
276 X_Structure_Id,
277 X_Segment_Override,
278 X_Override_Alc_Ledger_Currency,
279 X_Period_Set_Name,
280 X_Minimum_Display_Level,
281 X_Description,
282 X_Context,
283 X_Attribute1,
284 X_Attribute2,
285 X_Attribute3,
286 X_Attribute4,
287 X_Attribute5,
288 X_Attribute6,
289 X_Attribute7,
290 X_Attribute8,
291 X_Attribute9,
292 X_Attribute10,
293 X_Attribute11,
294 X_Attribute12,
295 X_Attribute13,
296 X_Attribute14,
297 X_Attribute15
298 );
299
300 OPEN C;
301 FETCH C INTO X_Rowid;
302
303 if (C%NOTFOUND) then
304 CLOSE C;
305 RAISE NO_DATA_FOUND;
306 end if;
307
308 CLOSE C;
309
310 END Insert_Row;
311
312 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
313 X_Application_Id NUMBER,
314 X_Report_Id NUMBER,
315 X_Name VARCHAR2,
316 X_Report_Title VARCHAR2,
317 X_Security_Flag VARCHAR2,
318 X_Column_Set_Id NUMBER,
319 X_Row_Set_Id NUMBER,
320 X_Rounding_Option VARCHAR2,
321 X_Output_Option VARCHAR2,
322 X_Report_Display_Set_Id NUMBER,
323 X_Content_Set_Id NUMBER,
324 X_Row_Order_Id NUMBER,
325 X_Parameter_Set_Id NUMBER,
326 X_Unit_Of_Measure_Id VARCHAR2,
327 X_Id_Flex_Code VARCHAR2,
328 X_Structure_Id NUMBER,
329 X_Segment_Override VARCHAR2,
330 X_Override_Alc_Ledger_Currency VARCHAR2,
331 X_Period_Set_Name VARCHAR2,
332 X_Minimum_Display_Level NUMBER,
333 X_Description VARCHAR2,
334 X_Context VARCHAR2,
335 X_Attribute1 VARCHAR2,
336 X_Attribute2 VARCHAR2,
337 X_Attribute3 VARCHAR2,
338 X_Attribute4 VARCHAR2,
339 X_Attribute5 VARCHAR2,
340 X_Attribute6 VARCHAR2,
341 X_Attribute7 VARCHAR2,
342 X_Attribute8 VARCHAR2,
343 X_Attribute9 VARCHAR2,
344 X_Attribute10 VARCHAR2,
345 X_Attribute11 VARCHAR2,
346 X_Attribute12 VARCHAR2,
347 X_Attribute13 VARCHAR2,
348 X_Attribute14 VARCHAR2,
349 X_Attribute15 VARCHAR2
350 ) IS
351 CURSOR C IS
352 SELECT *
353 FROM rg_reports
354 WHERE rowid = X_Rowid
355 FOR UPDATE of Report_Id NOWAIT;
356 Recinfo C%ROWTYPE;
357 BEGIN
358 OPEN C;
359 FETCH C INTO Recinfo;
360 if (C%NOTFOUND) then
361 CLOSE C;
362 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 end if;
365 CLOSE C;
366 if (
367 ( (Recinfo.application_id = X_Application_Id)
368 OR ( (Recinfo.application_id IS NULL)
369 AND (X_Application_Id IS NULL)))
370 AND ( (Recinfo.report_id = X_Report_Id)
371 OR ( (Recinfo.report_id IS NULL)
372 AND (X_Report_Id IS NULL)))
373 AND ( (Recinfo.name = X_Name)
374 OR ( (Recinfo.name IS NULL)
375 AND (X_Name IS NULL)))
376 AND ( (Recinfo.report_title = X_Report_Title)
377 OR ( (Recinfo.report_title IS NULL)
378 AND (X_Report_Title IS NULL)))
379 AND ( (Recinfo.security_flag = X_Security_Flag)
380 OR ( (Recinfo.security_flag IS NULL)
381 AND (X_Security_Flag IS NULL)))
382 AND ( (Recinfo.column_set_id = X_Column_Set_Id)
383 OR ( (Recinfo.column_set_id IS NULL)
384 AND (X_Column_Set_Id IS NULL)))
385 AND ( (Recinfo.row_set_id = X_Row_Set_Id)
386 OR ( (Recinfo.row_set_id IS NULL)
387 AND (X_Row_Set_Id IS NULL)))
388 AND ( (Recinfo.rounding_option = X_Rounding_Option)
389 OR ( (Recinfo.rounding_option IS NULL)
390 AND (X_Rounding_Option IS NULL)))
391 AND ( (Recinfo.output_option = X_Output_Option)
392 OR ( (Recinfo.output_option IS NULL)
393 AND (X_Output_Option IS NULL)))
394 AND ( (Recinfo.report_display_set_id = X_Report_Display_Set_Id)
395 OR ( (Recinfo.report_display_set_id IS NULL)
396 AND (X_Report_Display_Set_Id IS NULL)))
397 AND ( (Recinfo.content_set_id = X_Content_Set_Id)
398 OR ( (Recinfo.content_set_id IS NULL)
399 AND (X_Content_Set_Id IS NULL)))
400 AND ( (Recinfo.row_order_id = X_Row_Order_Id)
401 OR ( (Recinfo.row_order_id IS NULL)
402 AND (X_Row_Order_Id IS NULL)))
403 AND ( (Recinfo.parameter_set_id = X_Parameter_Set_Id)
404 OR ( (Recinfo.parameter_set_id IS NULL)
405 AND (X_Parameter_Set_Id IS NULL)))
406 AND ( (Recinfo.unit_of_measure_id = X_Unit_Of_Measure_Id)
407 OR ( (Recinfo.unit_of_measure_id IS NULL)
408 AND (X_Unit_Of_Measure_Id IS NULL)))
409 AND ( (Recinfo.id_flex_code = X_Id_Flex_Code)
410 OR ( (Recinfo.id_flex_code IS NULL)
414 AND (X_Structure_Id IS NULL)))
411 AND (X_Id_Flex_Code IS NULL)))
412 AND ( (Recinfo.structure_id = X_Structure_Id)
413 OR ( (Recinfo.structure_id IS NULL)
415 AND ( (Recinfo.segment_override = X_Segment_Override)
416 OR ( (Recinfo.segment_override IS NULL)
417 AND (X_Segment_Override IS NULL)))
418 AND ( (Recinfo.override_alc_ledger_currency = X_Override_Alc_Ledger_Currency)
419 OR ( (Recinfo.override_alc_ledger_currency IS NULL)
420 AND (X_Override_Alc_Ledger_Currency IS NULL)))
421 AND ( (Recinfo.period_set_name = X_Period_Set_Name)
422 OR ( (Recinfo.period_set_name IS NULL)
423 AND (X_Period_Set_Name IS NULL)))
424 AND ( (Recinfo.minimum_display_level = X_Minimum_Display_Level)
425 OR ( (Recinfo.minimum_display_level IS NULL)
426 AND (X_Minimum_Display_Level IS NULL)))
427 AND ( (Recinfo.description = X_Description)
428 OR ( (Recinfo.description IS NULL)
429 AND (X_Description IS NULL)))
430 AND ( (Recinfo.context = X_Context)
431 OR ( (Recinfo.context IS NULL)
432 AND (X_Context IS NULL)))
433 AND ( (Recinfo.attribute1 = X_Attribute1)
434 OR ( (Recinfo.attribute1 IS NULL)
435 AND (X_Attribute1 IS NULL)))
436 AND ( (Recinfo.attribute2 = X_Attribute2)
437 OR ( (Recinfo.attribute2 IS NULL)
438 AND (X_Attribute2 IS NULL)))
439 AND ( (Recinfo.attribute3 = X_Attribute3)
440 OR ( (Recinfo.attribute3 IS NULL)
441 AND (X_Attribute3 IS NULL)))
442 AND ( (Recinfo.attribute4 = X_Attribute4)
443 OR ( (Recinfo.attribute4 IS NULL)
444 AND (X_Attribute4 IS NULL)))
445 AND ( (Recinfo.attribute5 = X_Attribute5)
446 OR ( (Recinfo.attribute5 IS NULL)
447 AND (X_Attribute5 IS NULL)))
448 AND ( (Recinfo.attribute6 = X_Attribute6)
449 OR ( (Recinfo.attribute6 IS NULL)
450 AND (X_Attribute6 IS NULL)))
451 AND ( (Recinfo.attribute7 = X_Attribute7)
452 OR ( (Recinfo.attribute7 IS NULL)
453 AND (X_Attribute7 IS NULL)))
454 AND ( (Recinfo.attribute8 = X_Attribute8)
455 OR ( (Recinfo.attribute8 IS NULL)
456 AND (X_Attribute8 IS NULL)))
457 AND ( (Recinfo.attribute9 = X_Attribute9)
458 OR ( (Recinfo.attribute9 IS NULL)
459 AND (X_Attribute9 IS NULL)))
460 AND ( (Recinfo.attribute10 = X_Attribute10)
461 OR ( (Recinfo.attribute10 IS NULL)
462 AND (X_Attribute10 IS NULL)))
463 AND ( (Recinfo.attribute11 = X_Attribute11)
464 OR ( (Recinfo.attribute11 IS NULL)
465 AND (X_Attribute11 IS NULL)))
466 AND ( (Recinfo.attribute12 = X_Attribute12)
467 OR ( (Recinfo.attribute12 IS NULL)
468 AND (X_Attribute12 IS NULL)))
469 AND ( (Recinfo.attribute13 = X_Attribute13)
470 OR ( (Recinfo.attribute13 IS NULL)
471 AND (X_Attribute13 IS NULL)))
472 AND ( (Recinfo.attribute14 = X_Attribute14)
473 OR ( (Recinfo.attribute14 IS NULL)
474 AND (X_Attribute14 IS NULL)))
475 AND ( (Recinfo.attribute15 = X_Attribute15)
476 OR ( (Recinfo.attribute15 IS NULL)
477 AND (X_Attribute15 IS NULL)))
478 ) then
479 return;
480 else
481 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
482 APP_EXCEPTION.RAISE_EXCEPTION;
483 end if;
484 END Lock_Row;
485
486 PROCEDURE Update_Row(X_Rowid VARCHAR2,
487 X_Application_Id NUMBER,
488 X_Report_Id NUMBER,
489 X_Last_Update_Date DATE,
490 X_Last_Updated_By NUMBER,
491 X_Last_Update_Login NUMBER,
492 X_Name VARCHAR2,
493 X_Report_Title VARCHAR2,
494 X_Security_Flag VARCHAR2,
495 X_Column_Set_Id NUMBER,
496 X_Row_Set_Id NUMBER,
497 X_Rounding_Option VARCHAR2,
498 X_Output_Option VARCHAR2,
499 X_Report_Display_Set_Id NUMBER,
500 X_Content_Set_Id NUMBER,
501 X_Row_Order_Id NUMBER,
502 X_Parameter_Set_Id NUMBER,
503 X_Unit_Of_Measure_Id VARCHAR2,
504 X_Id_Flex_Code VARCHAR2,
505 X_Structure_Id NUMBER,
506 X_Segment_Override VARCHAR2,
507 X_Override_Alc_Ledger_Currency VARCHAR2,
508 X_Period_Set_Name VARCHAR2,
509 X_Minimum_Display_Level NUMBER,
510 X_Description VARCHAR2,
514 X_Attribute3 VARCHAR2,
511 X_Context VARCHAR2,
512 X_Attribute1 VARCHAR2,
513 X_Attribute2 VARCHAR2,
515 X_Attribute4 VARCHAR2,
516 X_Attribute5 VARCHAR2,
517 X_Attribute6 VARCHAR2,
518 X_Attribute7 VARCHAR2,
519 X_Attribute8 VARCHAR2,
520 X_Attribute9 VARCHAR2,
521 X_Attribute10 VARCHAR2,
522 X_Attribute11 VARCHAR2,
523 X_Attribute12 VARCHAR2,
524 X_Attribute13 VARCHAR2,
525 X_Attribute14 VARCHAR2,
526 X_Attribute15 VARCHAR2
527 ) IS
528 BEGIN
529 UPDATE rg_reports
530 SET
531 application_id = X_Application_Id,
532 report_id = X_Report_Id,
533 last_update_date = X_Last_Update_Date,
534 last_updated_by = X_Last_Updated_By,
535 last_update_login = X_Last_Update_Login,
536 name = X_Name,
537 report_title = X_Report_Title,
538 security_flag = X_Security_Flag,
539 column_set_id = X_Column_Set_Id,
540 row_set_id = X_Row_Set_Id,
541 rounding_option = X_Rounding_Option,
542 output_option = X_Output_Option,
543 report_display_set_id = X_Report_Display_Set_Id,
544 content_set_id = X_Content_Set_Id,
545 row_order_id = X_Row_Order_Id,
546 parameter_set_id = X_Parameter_Set_Id,
547 unit_of_measure_id = X_Unit_Of_Measure_Id,
548 id_flex_code = X_Id_Flex_Code,
549 structure_id = X_Structure_Id,
550 segment_override = X_Segment_Override,
551 override_alc_ledger_currency = X_Override_Alc_Ledger_Currency,
552 period_set_name = X_Period_Set_Name,
553 minimum_display_level = X_Minimum_Display_Level,
554 description = X_Description,
555 context = X_Context,
556 attribute1 = X_Attribute1,
557 attribute2 = X_Attribute2,
558 attribute3 = X_Attribute3,
559 attribute4 = X_Attribute4,
560 attribute5 = X_Attribute5,
561 attribute6 = X_Attribute6,
562 attribute7 = X_Attribute7,
563 attribute8 = X_Attribute8,
564 attribute9 = X_Attribute9,
565 attribute10 = X_Attribute10,
566 attribute11 = X_Attribute11,
567 attribute12 = X_Attribute12,
568 attribute13 = X_Attribute13,
569 attribute14 = X_Attribute14,
570 attribute15 = X_Attribute15
571 WHERE rowid = X_rowid;
572
573 if (SQL%NOTFOUND) then
574 RAISE NO_DATA_FOUND;
575 end if;
576
577 END Update_Row;
578
579 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
580 X_parameter_set_id NUMBER(15);
581 BEGIN
582
583 SELECT nvl(parameter_set_id,-1)
584 INTO X_parameter_set_id
585 FROM rg_reports
586 WHERE rowid = X_Rowid;
587
588 DELETE FROM rg_reports
589 WHERE rowid = X_Rowid;
590
591 if (SQL%NOTFOUND) then
592 RAISE NO_DATA_FOUND;
593 end if;
594
595 IF (X_parameter_set_id <> -1) THEN
596 DELETE FROM rg_report_parameters
597 WHERE parameter_set_id = X_parameter_set_id;
598 END IF;
599
600 END Delete_Row;
601
602
603 --
604 -- NAME
605 -- select_columns
606 -- DESCRIPTION
607 -- find report name corresponded to a report_id (currently no
608 -- form is using this procedure)
609 -- PARAMETERS
610 -- 1. Report ID
611 -- 2. Report name
612 --
613 PROCEDURE select_columns(report_id IN NUMBER,
614 name IN OUT NOCOPY VARCHAR2) IS
615 recinfo rg_reports%ROWTYPE;
616 BEGIN
617 recinfo.report_id := report_id;
618 select_row(recinfo);
619 name := recinfo.name;
620 END select_columns;
621
622
623 -- Name
624 -- contains_budget_overrides
625 -- Description
626 -- This function check whether a particular axis set
627 -- contains one or more budget overrides
631 --
628 -- Parameters
629 -- 1. row_set_id
630 -- 2. column_set_id
632 FUNCTION contains_budget_overrides(row_set_id IN NUMBER,
633 column_set_id IN NUMBER)
634 RETURN BOOLEAN
635 IS
636 dummy NUMBER;
637 BEGIN
638 SELECT 1 INTO dummy FROM dual
639 WHERE NOT EXISTS
640 (SELECT 1
641 FROM rg_report_axes R_A,
642 rg_report_standard_axes_b S_A
643 WHERE
644 (R_A.axis_set_id = row_set_id OR
645 R_A.axis_set_id = column_set_id)
646 AND R_A.parameter_num IS NOT NULL
647 AND R_A.standard_axis_id = S_A.standard_axis_id
648 AND S_A.simple_where_name = 'BUDGET'
649 );
650 RETURN(FALSE);
651
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 RETURN(TRUE);
655 END contains_budget_overrides;
656
657
658 -- Name
659 -- contains_encum_overrides
660 -- Description
661 -- This function check whether a particular axis set
662 -- contains one or more encumbrance runtime overrides
663 -- Parameters
664 -- 1. row_set_id
665 -- 2. column_set_id
666 --
667 FUNCTION contains_encum_overrides(row_set_id IN NUMBER,
668 column_set_id IN NUMBER)
669 RETURN BOOLEAN
670 IS
671 dummy NUMBER;
672 BEGIN
673 SELECT 1 INTO dummy FROM dual
674 WHERE NOT EXISTS
675 (SELECT 1
676 FROM rg_report_axes R_A,
677 rg_report_standard_axes_b S_A
678 WHERE
679 (R_A.axis_set_id = row_set_id OR
680 R_A.axis_set_id = column_set_id
681 )
682 AND R_A.parameter_num IS NOT NULL
683 AND R_A.standard_axis_id = S_A.standard_axis_id
684 AND S_A.simple_where_name = 'ENCUMBRANCE'
685 );
686 RETURN(FALSE);
687
688 EXCEPTION
689 WHEN NO_DATA_FOUND THEN
690 RETURN(TRUE);
691 END contains_encum_overrides;
692
693
694 -- Name
695 -- contains_overrides
696 -- Description
697 -- This function check whether a particular axis set
698 -- contains one or more runtime overrides
699 -- Parameters
700 -- 1. row set name
701 -- 2. column set name
702 --
703 FUNCTION contains_overrides(row_set_id IN NUMBER,
704 column_set_id IN NUMBER)
705 RETURN BOOLEAN
706 IS
707 dummy NUMBER;
708 BEGIN
709 SELECT 1 INTO dummy FROM dual
710 WHERE NOT EXISTS
711 (SELECT 1
712 FROM rg_report_axes R_A
713 WHERE
714 (R_A.axis_set_id = row_set_id OR
715 R_A.axis_set_id = column_set_id)
716 AND R_A.parameter_num IS NOT NULL
717 );
718 RETURN(FALSE);
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 RETURN(TRUE);
723 END contains_overrides;
724
725
726 PROCEDURE get_overrides(
727 row_set_id IN NUMBER,
728 column_set_id IN NUMBER,
729 budget_override IN OUT NOCOPY BOOLEAN,
730 encumbrance_override IN OUT NOCOPY BOOLEAN,
731 currency_override IN OUT NOCOPY BOOLEAN) IS
732 BEGIN
733 currency_override := contains_overrides(row_set_id, column_set_id);
734 IF NOT (currency_override) THEN
735 budget_override := FALSE;
736 encumbrance_override := FALSE;
737 ELSE
738 budget_override := contains_budget_overrides(row_set_id, column_set_id);
739 encumbrance_override := contains_encum_overrides(row_set_id, column_set_id);
740 END IF;
741
742 END get_overrides;
743
744
745 FUNCTION find_report_segment_override(x_report_id IN NUMBER) RETURN VARCHAR2
746 IS
747 CURSOR report IS
748 SELECT application_id, id_flex_code, structure_id,
749 segment_override, override_alc_ledger_currency
750 FROM rg_reports
751 WHERE report_id = x_report_id;
752
753 appl_id NUMBER;
754 flex_code VARCHAR2(4);
755 flex_num NUMBER;
756 seg_override VARCHAR2(800);
757 override_alc VARCHAR2(15);
758
759 coa_delimiter VARCHAR2(1);
760 first_delimiter_pos NUMBER;
761 override_ledger_id NUMBER;
762 override_ledger VARCHAR2(20);
763 translated_flag VARCHAR2(1);
764 conv_seg_override VARCHAR2(800);
765 BEGIN
766 OPEN report;
767 FETCH report INTO appl_id, flex_code, flex_num,
768 seg_override, override_alc;
769 CLOSE report;
770
771 IF (seg_override IS NULL) THEN
772 RETURN NULL;
773 END IF;
774
775 SELECT max(concatenated_segment_delimiter)
776 INTO coa_delimiter
777 FROM FND_ID_FLEX_STRUCTURES
778 WHERE APPLICATION_ID = appl_id
779 AND ID_FLEX_CODE = flex_code
780 AND ID_FLEX_NUM = flex_num;
781
782 IF (coa_delimiter IS NOT NULL) THEN
783 first_delimiter_pos := INSTR(seg_override, coa_delimiter);
787 IF (override_ledger_id IS NOT NULL) THEN
784 override_ledger_id := to_number(SUBSTR(seg_override,
785 1, first_delimiter_pos - 1));
786
788 GL_LEDGER_UTILS_PKG.Find_Ledger_Short_Name(
789 override_ledger_id,
790 override_alc,
791 override_ledger,
792 translated_flag);
793 conv_seg_override := override_ledger ||
794 SUBSTR(seg_override,
795 first_delimiter_pos);
796 ELSE
797 conv_seg_override := SUBSTR(seg_override,
798 first_delimiter_pos);
799 END IF;
800 END IF;
801 RETURN conv_seg_override;
802
803 END find_report_segment_override;
804
805 END RG_REPORTS_PKG;