1 package body BSC_KPI_DIM_SETS_PKG as
2 /* $Header: BSCKDSTB.pls 120.1 2007/02/08 13:19:02 akoduri ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_DIM_SET_ID in NUMBER,
7 X_NAME in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from BSC_KPI_DIM_SETS_TL
15 where INDICATOR = X_INDICATOR
16 and DIM_SET_ID = X_DIM_SET_ID
17 and LANGUAGE = userenv('LANG')
18 ;
19 begin
20 insert into BSC_KPI_DIM_SETS_TL (
21 INDICATOR,
22 DIM_SET_ID,
23 NAME,
24 LANGUAGE,
25 SOURCE_LANG,
26 CREATION_DATE ,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY ,
30 LAST_UPDATE_LOGIN
31 ) select
32 X_INDICATOR,
33 X_DIM_SET_ID,
34 X_NAME,
35 L.LANGUAGE_CODE,
36 userenv('LANG'),
37 X_CREATION_DATE ,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY ,
41 X_LAST_UPDATE_LOGIN
42 from FND_LANGUAGES L
43 where L.INSTALLED_FLAG in ('I', 'B')
44 and not exists
45 (select NULL
46 from BSC_KPI_DIM_SETS_TL T
47 where T.INDICATOR = X_INDICATOR
48 and T.DIM_SET_ID = X_DIM_SET_ID
49 and T.LANGUAGE = L.LANGUAGE_CODE);
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_INDICATOR in NUMBER,
63 X_DIM_SET_ID in NUMBER,
64 X_NAME in VARCHAR2
65 ) is
66 cursor c1 is select
67 NAME,
68 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
69 from BSC_KPI_DIM_SETS_TL
70 where INDICATOR = X_INDICATOR
71 and DIM_SET_ID = X_DIM_SET_ID
72 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
73 for update of INDICATOR nowait;
74 begin
75 for tlinfo in c1 loop
76 if (tlinfo.BASELANG = 'Y') then
77 if ( (tlinfo.NAME = X_NAME)
78 ) then
79 null;
80 else
81 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
82 app_exception.raise_exception;
83 end if;
84 end if;
85 end loop;
86 return;
87 end LOCK_ROW;
88
89 procedure UPDATE_ROW (
90 X_INDICATOR in NUMBER,
91 X_DIM_SET_ID in NUMBER,
92 X_NAME in VARCHAR2,
93 X_CREATION_DATE in DATE,
94 X_CREATED_BY in NUMBER,
95 X_LAST_UPDATE_DATE in DATE,
96 X_LAST_UPDATED_BY in NUMBER,
97 X_LAST_UPDATE_LOGIN in NUMBER
98 ) is
99 begin
100 update BSC_KPI_DIM_SETS_TL set
101 NAME = X_NAME,
102 SOURCE_LANG = userenv('LANG'),
103 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
104 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
105 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN)
106 where INDICATOR = X_INDICATOR
107 and DIM_SET_ID = X_DIM_SET_ID
108 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
109
110 if (sql%notfound) then
111 raise no_data_found;
112 end if;
113 end UPDATE_ROW;
114
115 procedure DELETE_ROW (
116 X_INDICATOR in NUMBER,
117 X_DIM_SET_ID in NUMBER
118 ) is
119 begin
120 delete from BSC_KPI_DIM_SETS_TL
121 where INDICATOR = X_INDICATOR
122 and DIM_SET_ID = X_DIM_SET_ID;
123
124 if (sql%notfound) then
125 raise no_data_found;
126 end if;
127
128 end DELETE_ROW;
129
130 procedure ADD_LANGUAGE
131 is
132 begin
133 update BSC_KPI_DIM_SETS_TL T set (
134 NAME
135 ) = (select
136 B.NAME
137 from BSC_KPI_DIM_SETS_TL B
138 where B.INDICATOR = T.INDICATOR
139 and B.DIM_SET_ID = T.DIM_SET_ID
140 and B.LANGUAGE = T.SOURCE_LANG)
141 where (
142 T.INDICATOR,
143 T.DIM_SET_ID,
144 T.LANGUAGE
145 ) in (select
146 SUBT.INDICATOR,
147 SUBT.DIM_SET_ID,
148 SUBT.LANGUAGE
149 from BSC_KPI_DIM_SETS_TL SUBB, BSC_KPI_DIM_SETS_TL SUBT
150 where SUBB.INDICATOR = SUBT.INDICATOR
151 and SUBB.DIM_SET_ID = SUBT.DIM_SET_ID
152 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
153 and (SUBB.NAME <> SUBT.NAME
154 ));
155
156 insert into BSC_KPI_DIM_SETS_TL (
157 INDICATOR,
158 DIM_SET_ID,
159 NAME,
160 LANGUAGE,
161 SOURCE_LANG,
162 CREATION_DATE ,
163 CREATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATED_BY
166 ) select
167 B.INDICATOR,
168 B.DIM_SET_ID,
169 B.NAME,
170 L.LANGUAGE_CODE,
171 B.SOURCE_LANG,
172 B.CREATION_DATE ,
173 B.CREATED_BY,
174 B.LAST_UPDATE_DATE,
175 B.LAST_UPDATED_BY
176 from BSC_KPI_DIM_SETS_TL B, FND_LANGUAGES L
177 where L.INSTALLED_FLAG in ('I', 'B')
178 and B.LANGUAGE = userenv('LANG')
179 and not exists
180 (select NULL
181 from BSC_KPI_DIM_SETS_TL T
182 where T.INDICATOR = B.INDICATOR
183 and T.DIM_SET_ID = B.DIM_SET_ID
184 and T.LANGUAGE = L.LANGUAGE_CODE);
185 end ADD_LANGUAGE;
186
187 FUNCTION Get_Dim_Set_Name (
188 p_Indicator IN NUMBER
189 ,p_Dim_Set_Id IN NUMBER
190 ) RETURN VARCHAR2 IS
191 CURSOR c_Dim_Set_Name IS
192 SELECT Name
193 FROM bsc_kpi_dim_sets_vl
194 WHERE indicator = p_Indicator
195 AND dim_set_id = p_Dim_Set_Id;
196 l_Dim_Set_Name bsc_kpi_dim_sets_vl.Name%TYPE := NULL;
197 BEGIN
198
199 OPEN c_Dim_Set_Name;
200 FETCH c_Dim_Set_Name INTO l_Dim_Set_Name;
201 CLOSE c_Dim_Set_Name;
202
203 RETURN l_Dim_Set_Name;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 RETURN l_Dim_Set_Name;
208 END Get_Dim_Set_Name;
209
210 FUNCTION Get_Dim_Level_Names (
211 p_Indicator IN NUMBER
212 ,p_Dim_Set_Id IN NUMBER
213 ) RETURN VARCHAR2 IS
214
215 CURSOR c_dim_levels IS
216 SELECT Name
217 FROM bsc_kpi_dim_levels_vl
218 WHERE indicator = p_Indicator
219 AND dim_set_id = p_Dim_Set_Id
220 AND status = 2;
221 l_Dim_Level_Names VARCHAR2(2000) := '';
222 BEGIN
223 FOR cd in c_dim_levels LOOP
224 l_Dim_Level_Names := l_Dim_Level_Names || ' / ' || cd.Name;
225 END LOOP;
226 IF SUBSTR(l_Dim_Level_Names,1,3) = ' / ' THEN
227 l_Dim_Level_Names := SUBSTR(l_Dim_Level_Names,4);
228 END IF;
229 IF (l_Dim_Level_Names IS NULL) THEN
230 l_Dim_Level_Names := fnd_message.get_string('BSC','BSC_NO_DIM_OBJS_IN_DIMSET');
231 END IF;
232 RETURN l_Dim_Level_Names;
233 EXCEPTION
234 WHEN OTHERS THEN
235 RETURN l_Dim_Level_Names;
236 END Get_Dim_Level_Names;
237
238 end BSC_KPI_DIM_SETS_PKG;
[Home] [Help]