1 package body BSC_KPI_ANALYSIS_OPTIONS_PKG as
2 /* $Header: BSCKAOPB.pls 115.6 2003/02/12 14:25:45 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_ANALYSIS_GROUP_ID in NUMBER,
7 X_OPTION_ID in NUMBER,
8 X_PARENT_OPTION_ID in NUMBER,
9 X_GRANDPARENT_OPTION_ID in NUMBER,
10 X_DIM_SET_ID in NUMBER,
11 X_USER_LEVEL0 in NUMBER,
12 X_USER_LEVEL1 in NUMBER,
13 X_USER_LEVEL1_DEFAULT in NUMBER,
14 X_USER_LEVEL2 in NUMBER,
15 X_USER_LEVEL2_DEFAULT in NUMBER,
16 X_NAME in VARCHAR2,
17 X_HELP in VARCHAR2
18 ) is
19 cursor C is select ROWID from BSC_KPI_ANALYSIS_OPTIONS_B
20 where INDICATOR = X_INDICATOR
21 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
22 and OPTION_ID = X_OPTION_ID
23 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
24 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID
25 ;
26 begin
27 insert into BSC_KPI_ANALYSIS_OPTIONS_B (
28 INDICATOR,
29 ANALYSIS_GROUP_ID,
30 OPTION_ID,
31 PARENT_OPTION_ID,
32 GRANDPARENT_OPTION_ID,
33 DIM_SET_ID,
34 USER_LEVEL0,
35 USER_LEVEL1,
36 USER_LEVEL1_DEFAULT,
37 USER_LEVEL2,
38 USER_LEVEL2_DEFAULT
39 ) values (
40 X_INDICATOR,
41 X_ANALYSIS_GROUP_ID,
42 X_OPTION_ID,
43 X_PARENT_OPTION_ID,
44 X_GRANDPARENT_OPTION_ID,
45 X_DIM_SET_ID,
46 X_USER_LEVEL0,
47 X_USER_LEVEL1,
48 X_USER_LEVEL1_DEFAULT,
49 X_USER_LEVEL2,
50 X_USER_LEVEL2_DEFAULT
51 );
52
53 insert into BSC_KPI_ANALYSIS_OPTIONS_TL (
54 INDICATOR,
55 ANALYSIS_GROUP_ID,
56 OPTION_ID,
57 PARENT_OPTION_ID,
58 GRANDPARENT_OPTION_ID,
59 NAME,
60 HELP,
61 LANGUAGE,
62 SOURCE_LANG
63 ) select
64 X_INDICATOR,
65 X_ANALYSIS_GROUP_ID,
66 X_OPTION_ID,
67 X_PARENT_OPTION_ID,
68 X_GRANDPARENT_OPTION_ID,
69 X_NAME,
70 X_HELP,
71 L.LANGUAGE_CODE,
72 userenv('LANG')
73 from FND_LANGUAGES L
74 where L.INSTALLED_FLAG in ('I', 'B')
75 and not exists
76 (select NULL
77 from BSC_KPI_ANALYSIS_OPTIONS_TL T
78 where T.INDICATOR = X_INDICATOR
79 and T.ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
80 and T.OPTION_ID = X_OPTION_ID
81 and T.PARENT_OPTION_ID = X_PARENT_OPTION_ID
82 and T.GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID
83 and T.LANGUAGE = L.LANGUAGE_CODE);
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end INSERT_ROW;
94
95 procedure LOCK_ROW (
96 X_INDICATOR in NUMBER,
97 X_ANALYSIS_GROUP_ID in NUMBER,
98 X_OPTION_ID in NUMBER,
99 X_PARENT_OPTION_ID in NUMBER,
100 X_GRANDPARENT_OPTION_ID in NUMBER,
101 X_DIM_SET_ID in NUMBER,
102 X_USER_LEVEL0 in NUMBER,
103 X_USER_LEVEL1 in NUMBER,
104 X_USER_LEVEL1_DEFAULT in NUMBER,
105 X_USER_LEVEL2 in NUMBER,
106 X_USER_LEVEL2_DEFAULT in NUMBER,
107 X_NAME in VARCHAR2,
108 X_HELP in VARCHAR2
109 ) is
110 cursor c is select
111 DIM_SET_ID,
112 USER_LEVEL0,
113 USER_LEVEL1,
114 USER_LEVEL1_DEFAULT,
115 USER_LEVEL2,
116 USER_LEVEL2_DEFAULT
117 from BSC_KPI_ANALYSIS_OPTIONS_B
118 where INDICATOR = X_INDICATOR
119 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
120 and OPTION_ID = X_OPTION_ID
121 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
122 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID
123 for update of INDICATOR nowait;
124 recinfo c%rowtype;
125
126 cursor c1 is select
127 NAME,
128 HELP,
129 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
130 from BSC_KPI_ANALYSIS_OPTIONS_TL
131 where INDICATOR = X_INDICATOR
132 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
133 and OPTION_ID = X_OPTION_ID
134 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
135 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID
136 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
137 for update of INDICATOR nowait;
138 begin
139 open c;
140 fetch c into recinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
144 app_exception.raise_exception;
145 end if;
146 close c;
147 if ( (recinfo.DIM_SET_ID = X_DIM_SET_ID)
148 AND ((recinfo.USER_LEVEL0 = X_USER_LEVEL0)
149 OR ((recinfo.USER_LEVEL0 is null) AND (X_USER_LEVEL0 is null)))
150 AND ((recinfo.USER_LEVEL1 = X_USER_LEVEL1)
151 OR ((recinfo.USER_LEVEL1 is null) AND (X_USER_LEVEL1 is null)))
152 AND ((recinfo.USER_LEVEL1_DEFAULT = X_USER_LEVEL1_DEFAULT)
153 OR ((recinfo.USER_LEVEL1_DEFAULT is null) AND (X_USER_LEVEL1_DEFAULT is null)))
154 AND ((recinfo.USER_LEVEL2 = X_USER_LEVEL2)
155 OR ((recinfo.USER_LEVEL2 is null) AND (X_USER_LEVEL2 is null)))
156 AND ((recinfo.USER_LEVEL2_DEFAULT = X_USER_LEVEL2_DEFAULT)
157 OR ((recinfo.USER_LEVEL2_DEFAULT is null) AND (X_USER_LEVEL2_DEFAULT is null)))
158 ) then
159 null;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164
165 for tlinfo in c1 loop
166 if (tlinfo.BASELANG = 'Y') then
167 if ( (tlinfo.NAME = X_NAME)
168 AND (tlinfo.HELP = X_HELP)
169 ) then
170 null;
171 else
172 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
173 app_exception.raise_exception;
174 end if;
175 end if;
176 end loop;
177 return;
178 end LOCK_ROW;
179
180 procedure UPDATE_ROW (
181 X_INDICATOR in NUMBER,
182 X_ANALYSIS_GROUP_ID in NUMBER,
183 X_OPTION_ID in NUMBER,
184 X_PARENT_OPTION_ID in NUMBER,
185 X_GRANDPARENT_OPTION_ID in NUMBER,
186 X_DIM_SET_ID in NUMBER,
187 X_USER_LEVEL0 in NUMBER,
188 X_USER_LEVEL1 in NUMBER,
189 X_USER_LEVEL1_DEFAULT in NUMBER,
190 X_USER_LEVEL2 in NUMBER,
191 X_USER_LEVEL2_DEFAULT in NUMBER,
192 X_NAME in VARCHAR2,
193 X_HELP in VARCHAR2
194 ) is
195 begin
196 update BSC_KPI_ANALYSIS_OPTIONS_B set
197 DIM_SET_ID = X_DIM_SET_ID,
198 USER_LEVEL0 = X_USER_LEVEL0,
199 USER_LEVEL1 = X_USER_LEVEL1,
200 USER_LEVEL1_DEFAULT = X_USER_LEVEL1_DEFAULT,
201 USER_LEVEL2 = X_USER_LEVEL2,
202 USER_LEVEL2_DEFAULT = X_USER_LEVEL2_DEFAULT
203 where INDICATOR = X_INDICATOR
204 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
205 and OPTION_ID = X_OPTION_ID
206 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
207 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212
213 update BSC_KPI_ANALYSIS_OPTIONS_TL set
214 NAME = X_NAME,
215 HELP = X_HELP,
216 SOURCE_LANG = userenv('LANG')
217 where INDICATOR = X_INDICATOR
218 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
219 and OPTION_ID = X_OPTION_ID
220 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
221 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227 end UPDATE_ROW;
228
229 procedure DELETE_ROW (
230 X_INDICATOR in NUMBER,
231 X_ANALYSIS_GROUP_ID in NUMBER,
232 X_OPTION_ID in NUMBER,
233 X_PARENT_OPTION_ID in NUMBER,
234 X_GRANDPARENT_OPTION_ID in NUMBER
235 ) is
236 begin
237 delete from BSC_KPI_ANALYSIS_OPTIONS_TL
238 where INDICATOR = X_INDICATOR
239 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
240 and OPTION_ID = X_OPTION_ID
241 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
242 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID;
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247
248 delete from BSC_KPI_ANALYSIS_OPTIONS_B
249 where INDICATOR = X_INDICATOR
250 and ANALYSIS_GROUP_ID = X_ANALYSIS_GROUP_ID
251 and OPTION_ID = X_OPTION_ID
252 and PARENT_OPTION_ID = X_PARENT_OPTION_ID
253 and GRANDPARENT_OPTION_ID = X_GRANDPARENT_OPTION_ID;
254
255 if (sql%notfound) then
256 raise no_data_found;
257 end if;
258 end DELETE_ROW;
259
260 procedure ADD_LANGUAGE
261 is
262 begin
263 delete from BSC_KPI_ANALYSIS_OPTIONS_TL T
264 where not exists
265 (select NULL
266 from BSC_KPI_ANALYSIS_OPTIONS_B B
267 where B.INDICATOR = T.INDICATOR
268 and B.ANALYSIS_GROUP_ID = T.ANALYSIS_GROUP_ID
269 and B.OPTION_ID = T.OPTION_ID
270 and B.PARENT_OPTION_ID = T.PARENT_OPTION_ID
271 and B.GRANDPARENT_OPTION_ID = T.GRANDPARENT_OPTION_ID
272 );
273
274 update BSC_KPI_ANALYSIS_OPTIONS_TL T set (
275 NAME,
276 HELP
277 ) = (select
278 B.NAME,
279 B.HELP
280 from BSC_KPI_ANALYSIS_OPTIONS_TL B
281 where B.INDICATOR = T.INDICATOR
282 and B.ANALYSIS_GROUP_ID = T.ANALYSIS_GROUP_ID
283 and B.OPTION_ID = T.OPTION_ID
284 and B.PARENT_OPTION_ID = T.PARENT_OPTION_ID
285 and B.GRANDPARENT_OPTION_ID = T.GRANDPARENT_OPTION_ID
286 and B.LANGUAGE = T.SOURCE_LANG)
287 where (
288 T.INDICATOR,
289 T.ANALYSIS_GROUP_ID,
290 T.OPTION_ID,
291 T.PARENT_OPTION_ID,
292 T.GRANDPARENT_OPTION_ID,
293 T.LANGUAGE
294 ) in (select
295 SUBT.INDICATOR,
296 SUBT.ANALYSIS_GROUP_ID,
297 SUBT.OPTION_ID,
298 SUBT.PARENT_OPTION_ID,
299 SUBT.GRANDPARENT_OPTION_ID,
300 SUBT.LANGUAGE
301 from BSC_KPI_ANALYSIS_OPTIONS_TL SUBB, BSC_KPI_ANALYSIS_OPTIONS_TL SUBT
302 where SUBB.INDICATOR = SUBT.INDICATOR
303 and SUBB.ANALYSIS_GROUP_ID = SUBT.ANALYSIS_GROUP_ID
304 and SUBB.OPTION_ID = SUBT.OPTION_ID
305 and SUBB.PARENT_OPTION_ID = SUBT.PARENT_OPTION_ID
306 and SUBB.GRANDPARENT_OPTION_ID = SUBT.GRANDPARENT_OPTION_ID
307 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
308 and (SUBB.NAME <> SUBT.NAME
309 or SUBB.HELP <> SUBT.HELP
310 ));
311
312 insert into BSC_KPI_ANALYSIS_OPTIONS_TL (
313 INDICATOR,
314 ANALYSIS_GROUP_ID,
315 OPTION_ID,
316 PARENT_OPTION_ID,
317 GRANDPARENT_OPTION_ID,
318 NAME,
319 HELP,
320 LANGUAGE,
321 SOURCE_LANG
322 ) select
323 B.INDICATOR,
324 B.ANALYSIS_GROUP_ID,
325 B.OPTION_ID,
326 B.PARENT_OPTION_ID,
327 B.GRANDPARENT_OPTION_ID,
328 B.NAME,
329 B.HELP,
330 L.LANGUAGE_CODE,
331 B.SOURCE_LANG
332 from BSC_KPI_ANALYSIS_OPTIONS_TL B, FND_LANGUAGES L
333 where L.INSTALLED_FLAG in ('I', 'B')
334 and B.LANGUAGE = userenv('LANG')
335 and not exists
336 (select NULL
337 from BSC_KPI_ANALYSIS_OPTIONS_TL T
338 where T.INDICATOR = B.INDICATOR
339 and T.ANALYSIS_GROUP_ID = B.ANALYSIS_GROUP_ID
340 and T.OPTION_ID = B.OPTION_ID
341 and T.PARENT_OPTION_ID = B.PARENT_OPTION_ID
342 and T.GRANDPARENT_OPTION_ID = B.GRANDPARENT_OPTION_ID
343 and T.LANGUAGE = L.LANGUAGE_CODE);
344 end ADD_LANGUAGE;
345
346 end BSC_KPI_ANALYSIS_OPTIONS_PKG;
[Home] [Help]