eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIS_RKPI

Source


1 PACKAGE BODY BIS_RKPI as
2 /*$Header: BISRKPIB.pls 120.2 2006/01/03 22:34:52 akoduri noship $*/
3 
4 
5 FUNCTION GET_PMF_DIM_L_SN(
6 	p_ak_region_code IN VARCHAR2,
7   p_filter_time_dl IN VARCHAR2 := 'T'
8 ) RETURN VARCHAR2
9 IS
10    TYPE t_cursor IS REF CURSOR;
11    h_cursor t_cursor;
12    h_name AK_REGION_ITEMS.region_code%TYPE;
13    h_group AK_REGION_ITEMS.region_code%TYPE;
14    h_sql VARCHAR2(32700);
15    h_sn_comb VARCHAR2(32700);
16 BEGIN
17 
18 -- avoid using OR
19    h_sql := 'SELECT DISTINCT sn, gr FROM '||
20    -- non nested items
21    		'((SELECT SUBSTR(A.attribute2,'||
22 		' INSTR(A.attribute2, ''+'') + 1,'||
23 		' LENGTH(A.attribute2)-INSTR(A.attribute2, ''+''))'||
24 		' AS sn,'||
25 		' SUBSTR(A.attribute2,'||
26 		' 1,'||
27 		' INSTR(A.attribute2, ''+'') - 1)'||
28 		' AS gr'||
29         ' FROM AK_REGION_ITEMS A'||
30     	' WHERE A.region_code = :1'||
31 	    ' AND A.item_style <> ''NESTED_REGION'''||
32         ' AND A.attribute1 IN (''DIM LEVEL SINGLE VALUE'', '||
33 		'''DIMENSION LEVEL'', ''HIDE DIMENSION LEVEL'', '||
34 		'''HIDE PARAMETER'', ''HIDE VIEW BY DIMENSION'', '||
35 		'''HIDE_VIEW_BY_DIM_SINGLE'', ''COMPARE TO DIMENSION LEVEL'', '||
36 		'''VIEWBY PARAMETER'')'||
37 		' AND A.attribute2 IS NOT NULL)'||
38 		' UNION '||
39 	-- nested items
40 		'(SELECT SUBSTR(A.attribute2,'||
41 		' INSTR(A.attribute2, ''+'') + 1,'||
42 		' LENGTH(A.attribute2)-INSTR(A.attribute2, ''+''))'||
43 		' AS sn,'||
44 		' SUBSTR(A.attribute2,'||
45 		' 1,'||
46 		' INSTR(A.attribute2, ''+'') - 1)'||
47 		' AS gr'||
48         ' FROM AK_REGION_ITEMS A'||
49     	' WHERE A.region_code IN '||
50 			'(SELECT B.nested_region_code '||
51 					'FROM AK_REGION_ITEMS B '||
52 					'WHERE B.region_code = :2 '||
53 					'AND B.ITEM_STYLE = ''NESTED_REGION'')'||
54         ' AND A.attribute1 IN (''DIM LEVEL SINGLE VALUE'', '||
55 		'''DIMENSION LEVEL'', ''HIDE DIMENSION LEVEL'', '||
56 		'''HIDE PARAMETER'', ''HIDE VIEW BY DIMENSION'', '||
57 		'''HIDE_VIEW_BY_DIM_SINGLE'', ''COMPARE TO DIMENSION LEVEL'', '||
58 		'''VIEWBY PARAMETER'')'||
59 		' AND A.attribute2 IS NOT NULL))'||
60         ' ORDER BY sn';
61 
62    OPEN h_cursor FOR h_sql USING p_ak_region_code, p_ak_region_code;
63    LOOP
64        FETCH h_cursor INTO h_name, h_group;
65        EXIT WHEN h_cursor%NOTFOUND;
66 
67        -- exclude time dimension
68        IF ((p_filter_time_dl IS NULL) OR (p_filter_time_dl <> 'T') OR
69            (p_filter_time_dl = 'T' AND h_group <> 'TIME' AND h_group <> 'EDW_TIME_M' AND h_group <> 'TIME_COMPARISON_TYPE')) THEN
70          IF (h_sn_comb IS NULL) THEN
71            h_sn_comb := ','||h_name||',';
72          ELSE
73            h_sn_comb := h_sn_comb||','||h_name||',';
74          END IF;
75     	 END IF;
76    END LOOP;
77    CLOSE h_cursor;
78 
79    IF h_sn_comb IS NULL THEN
80 	 RETURN ' ';
81    END IF;
82 
83    RETURN h_sn_comb;
84    EXCEPTION
85 	 WHEN OTHERS THEN
86 	   IF (h_cursor%ISOPEN) THEN
87 		CLOSE h_cursor;
88 	   END IF;
89        RETURN NULL;
90 END GET_PMF_DIM_L_SN;
91 
92 
93 
94 FUNCTION GET_PMF_DIM_L_COMB(
95 	p_dim_level_list IN VARCHAR2,
96 	p_lang IN VARCHAR2
97 ) RETURN VARCHAR2
98 IS
99    TYPE t_cursor IS REF CURSOR;
100    h_cursor t_cursor;
101    h_sql VARCHAR2(32700);
102 	-- current shortname position in the list
103    h_start_pos NUMBER;
104    h_end_pos NUMBER;
105    h_strlen NUMBER;
106    h_sn AK_REGION_ITEMS.attribute2%TYPE;
107    h_name BIS_LEVELS_VL.name%TYPE;
108    h_name_comb VARCHAR2(32767);
109 
110 BEGIN
111 
112    h_strlen := LENGTH(p_dim_level_list);
113 
114    IF (h_strlen <= 1) THEN
115 	RETURN ' ';
116    END IF;
117 
118    -- start position of the input list
119    h_start_pos := 2;
120 
121    h_sql := 'SELECT b2.name'||
122             ' FROM BIS_LEVELS_TL b2, BIS_LEVELS b1'||
123             ' WHERE b1.SHORT_NAME = :1'||
124 		' AND b1.LEVEL_ID = b2.LEVEL_ID'||
125 	      ' AND b2.language = :2';
126 
127    LOOP
128       EXIT WHEN h_start_pos >= h_strlen;
129 
130 	h_end_pos := INSTR(p_dim_level_list, ',' , h_start_pos, 1);
131 	h_sn := SUBSTR(p_dim_level_list, h_start_pos, h_end_pos-h_start_pos);
132 	OPEN h_cursor FOR h_sql USING h_sn, p_lang;
133       FETCH h_cursor INTO h_name;
134 	CLOSE h_cursor;
135 
136       IF (h_name_comb IS NULL) THEN
137            h_name_comb := h_name;
138       ELSE
139            h_name_comb := h_name_comb||', '||h_name;
140       END IF;
141 
142 	h_start_pos := h_end_pos + 2;
143 
144    END LOOP;
145 
146    RETURN h_name_comb;
147    EXCEPTION
148 	 WHEN OTHERS THEN
149 	   IF (h_cursor%ISOPEN) THEN
150 		CLOSE h_cursor;
151 	   END IF;
152        RETURN NULL;
153 END GET_PMF_DIM_L_COMB;
154 
155 
156 FUNCTION REMOVE_COMMON_PARAMS(
157 	p_dim_level_list IN VARCHAR2,
158 	p_common_params IN VARCHAR2
159 ) RETURN VARCHAR2
160 IS
161    TYPE t_cursor IS REF CURSOR;
162 
163 	-- current shortname position in the list
164    h_start_pos NUMBER;
165    h_end_pos NUMBER;
166    h_strlen NUMBER;
167 
168    h_com_start_pos NUMBER;
169    h_com_end_pos NUMBER;
170    h_com_strlen NUMBER;
171 
172    h_sn AK_REGION_ITEMS.attribute2%TYPE;
173    h_com_sn AK_REGION_ITEMS.attribute2%TYPE;
174 
175    h_sn_comb VARCHAR2(32767);
176 
177 BEGIN
178 -- loop until p_dim_level_list or p_common_params reaches the end
179 
180 	h_strlen := LENGTH(p_dim_level_list);
181 	IF (h_strlen <= 1) THEN
182 		RETURN ' ';
183 	END IF;
184 	-- start position of the input list
185 	h_start_pos := 2;
186 
187 	h_com_strlen := LENGTH(p_common_params);
188 	IF (h_com_strlen <= 1) THEN
189 		RETURN p_dim_level_list;
190 	END IF;
191 	-- start position of the input list
192 	h_com_start_pos := 2;
193 
194    LOOP
195       EXIT WHEN ((h_start_pos >= h_strlen) OR (h_com_start_pos >= h_com_strlen));
196 
197 	  h_end_pos := INSTR(p_dim_level_list, ',' , h_start_pos, 1);
198 	  h_sn := SUBSTR(p_dim_level_list, h_start_pos, h_end_pos-h_start_pos);
199 
200 	  h_com_end_pos := INSTR(p_common_params, ',' , h_com_start_pos, 1);
201 	  h_com_sn := SUBSTR(p_common_params, h_com_start_pos, h_com_end_pos-h_com_start_pos);
202 
203 
204         IF (h_sn < h_com_sn) THEN
205 	    h_start_pos := h_end_pos + 2;
206     	    IF (h_sn_comb IS NULL) THEN
207       	h_sn_comb := ','||h_sn||',';
208           ELSE
209           	h_sn_comb := h_sn_comb||','||h_sn||',';
210 	    END IF;
211         ELSIF (h_sn > h_com_sn) THEN
212 	    h_com_start_pos := h_com_end_pos + 2;
213         ELSE
214 	    -- they are equal
215 	    h_start_pos := h_end_pos + 2;
216 	    h_com_start_pos := h_com_end_pos + 2;
217        END IF;
218    END LOOP;
219 
220    -- if p_dim_level_list has not reach the end
221    -- copy the rest
222    IF (h_start_pos < h_strlen) THEN
223 	h_sn := SUBSTR(p_dim_level_list, h_start_pos, h_strlen-h_start_pos+1);
224       IF (h_sn_comb IS NULL) THEN
225 		h_sn_comb := ','||h_sn;
226       ELSE
227 		h_sn_comb := h_sn_comb||','||h_sn;
228       END IF;
229    END IF;
230 
231    IF (h_sn_comb IS NULL) THEN
232 	h_sn_comb := ' ';
233    END IF;
234 
235    RETURN h_sn_comb;
236 
237    EXCEPTION
238 	  WHEN OTHERS THEN
239           RETURN NULL;
240 END REMOVE_COMMON_PARAMS;
241 
242 END BIS_RKPI;