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.BSC_KPI_ANALYSIS_MEASURES_PKG

Source


1 package body BSC_KPI_ANALYSIS_MEASURES_PKG as
2 /* $Header: BSCKAMSB.pls 115.9 2003/06/21 01:24:19 meastmon ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_INDICATOR in NUMBER,
6   X_ANALYSIS_OPTION0 in NUMBER,
7   X_ANALYSIS_OPTION1 in NUMBER,
8   X_ANALYSIS_OPTION2 in NUMBER,
9   X_SERIES_ID in NUMBER,
10   X_DATASET_ID in NUMBER,
11   X_AXIS in NUMBER,
12   X_SERIES_TYPE in NUMBER,
13   X_STACK_SERIES_ID in NUMBER,
14   X_BM_FLAG in NUMBER,
15   X_BUDGET_FLAG in NUMBER,
16   X_DEFAULT_VALUE in NUMBER,
17   X_SERIES_COLOR in NUMBER,
18   X_BM_COLOR in NUMBER,
19   X_NAME in VARCHAR2,
20   X_HELP in VARCHAR2
21 ) is
22   cursor C is select ROWID from BSC_KPI_ANALYSIS_MEASURES_B
23     where INDICATOR = X_INDICATOR
24     and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
25     and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
26     and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
27     and SERIES_ID = X_SERIES_ID
28     ;
29 begin
30   insert into BSC_KPI_ANALYSIS_MEASURES_B (
31     ANALYSIS_OPTION2,
32     SERIES_ID,
33     DATASET_ID,
34     AXIS,
35     SERIES_TYPE,
36     STACK_SERIES_ID,
37     BM_FLAG,
38     BUDGET_FLAG,
39     DEFAULT_VALUE,
40     SERIES_COLOR,
41     BM_COLOR,
42     INDICATOR,
43     ANALYSIS_OPTION0,
44     ANALYSIS_OPTION1
45   ) values (
46     X_ANALYSIS_OPTION2,
47     X_SERIES_ID,
48     X_DATASET_ID,
49     X_AXIS,
50     X_SERIES_TYPE,
51     X_STACK_SERIES_ID,
52     X_BM_FLAG,
53     X_BUDGET_FLAG,
54     X_DEFAULT_VALUE,
55     X_SERIES_COLOR,
56     X_BM_COLOR,
57     X_INDICATOR,
58     X_ANALYSIS_OPTION0,
59     X_ANALYSIS_OPTION1
60   );
61 
62   insert into BSC_KPI_ANALYSIS_MEASURES_TL (
63     INDICATOR,
64     ANALYSIS_OPTION0,
65     ANALYSIS_OPTION1,
66     ANALYSIS_OPTION2,
67     SERIES_ID,
68     NAME,
69     HELP,
70     LANGUAGE,
71     SOURCE_LANG
72   ) select
73     X_INDICATOR,
74     X_ANALYSIS_OPTION0,
75     X_ANALYSIS_OPTION1,
76     X_ANALYSIS_OPTION2,
77     X_SERIES_ID,
78     X_NAME,
79     X_HELP,
80     L.LANGUAGE_CODE,
81     userenv('LANG')
82   from FND_LANGUAGES L
83   where L.INSTALLED_FLAG in ('I', 'B')
84   and not exists
85     (select NULL
86     from BSC_KPI_ANALYSIS_MEASURES_TL T
87     where T.INDICATOR = X_INDICATOR
88     and T.ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
89     and T.ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
90     and T.ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
91     and T.SERIES_ID = X_SERIES_ID
92     and T.LANGUAGE = L.LANGUAGE_CODE);
93 
94   open c;
95   fetch c into X_ROWID;
96   if (c%notfound) then
97     close c;
98     raise no_data_found;
99   end if;
100   close c;
101 
102 end INSERT_ROW;
103 
104 procedure LOCK_ROW (
105   X_INDICATOR in NUMBER,
106   X_ANALYSIS_OPTION0 in NUMBER,
107   X_ANALYSIS_OPTION1 in NUMBER,
108   X_ANALYSIS_OPTION2 in NUMBER,
109   X_SERIES_ID in NUMBER,
110   X_DATASET_ID in NUMBER,
111   X_AXIS in NUMBER,
112   X_SERIES_TYPE in NUMBER,
113   X_STACK_SERIES_ID in NUMBER,
114   X_BM_FLAG in NUMBER,
115   X_BUDGET_FLAG in NUMBER,
116   X_DEFAULT_VALUE in NUMBER,
117   X_SERIES_COLOR in NUMBER,
118   X_BM_COLOR in NUMBER,
119   X_NAME in VARCHAR2,
120   X_HELP in VARCHAR2
121 ) is
122   cursor c is select
123       DATASET_ID,
124       AXIS,
125       SERIES_TYPE,
126       STACK_SERIES_ID,
127       BM_FLAG,
128       BUDGET_FLAG,
129       DEFAULT_VALUE,
130       SERIES_COLOR,
131       BM_COLOR
132     from BSC_KPI_ANALYSIS_MEASURES_B
133     where INDICATOR = X_INDICATOR
134     and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
135     and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
136     and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
137     and SERIES_ID = X_SERIES_ID
138     for update of INDICATOR nowait;
139   recinfo c%rowtype;
140 
141   cursor c1 is select
142       NAME,
143       HELP,
144       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
145     from BSC_KPI_ANALYSIS_MEASURES_TL
146     where INDICATOR = X_INDICATOR
147     and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
148     and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
149     and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
150     and SERIES_ID = X_SERIES_ID
151     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
152     for update of INDICATOR nowait;
153 begin
154   open c;
155   fetch c into recinfo;
156   if (c%notfound) then
157     close c;
158     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
159     app_exception.raise_exception;
160   end if;
161   close c;
162   if (    (recinfo.DATASET_ID = X_DATASET_ID)
163       AND (recinfo.AXIS = X_AXIS)
164       AND (recinfo.SERIES_TYPE = X_SERIES_TYPE)
165       AND ((recinfo.STACK_SERIES_ID = X_STACK_SERIES_ID)
166            OR ((recinfo.STACK_SERIES_ID is null) AND (X_STACK_SERIES_ID is null)))
167       AND (recinfo.BM_FLAG = X_BM_FLAG)
168       AND (recinfo.BUDGET_FLAG = X_BUDGET_FLAG)
169       AND (recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
170       AND (recinfo.SERIES_COLOR = X_SERIES_COLOR)
171       AND (recinfo.BM_COLOR = X_BM_COLOR)
172   ) then
173     null;
174   else
175     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176     app_exception.raise_exception;
177   end if;
178 
179   for tlinfo in c1 loop
180     if (tlinfo.BASELANG = 'Y') then
181       if (    (tlinfo.NAME = X_NAME)
182           AND (tlinfo.HELP = X_HELP)
183       ) then
184         null;
185       else
186         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
187         app_exception.raise_exception;
188       end if;
189     end if;
190   end loop;
191   return;
192 end LOCK_ROW;
193 
194 procedure UPDATE_ROW (
195   X_INDICATOR in NUMBER,
196   X_ANALYSIS_OPTION0 in NUMBER,
197   X_ANALYSIS_OPTION1 in NUMBER,
198   X_ANALYSIS_OPTION2 in NUMBER,
199   X_SERIES_ID in NUMBER,
200   X_DATASET_ID in NUMBER,
201   X_AXIS in NUMBER,
202   X_SERIES_TYPE in NUMBER,
203   X_STACK_SERIES_ID in NUMBER,
204   X_BM_FLAG in NUMBER,
205   X_BUDGET_FLAG in NUMBER,
206   X_DEFAULT_VALUE in NUMBER,
207   X_SERIES_COLOR in NUMBER,
208   X_BM_COLOR in NUMBER,
209   X_NAME in VARCHAR2,
210   X_HELP in VARCHAR2
211 ) is
212 l_old_dataset_id	number;
213 begin
214 
215   -- mdamle 4/23/2003 - PMD - Measure Definer - Update flag if dataset changed
216   select dataset_id into l_old_dataset_id
217   from bsc_kpi_analysis_measures_b
218   where INDICATOR = X_INDICATOR
219   and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
220   and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
221   and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
222   and SERIES_ID = X_SERIES_ID;
223 
224   if (l_old_dataset_id <> X_DATASET_ID) then
225 	BSC_DESIGNER_PVT.ActionFlag_Change(x_indicator, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
226   end if;
227 
228   update BSC_KPI_ANALYSIS_MEASURES_B set
229     DATASET_ID = X_DATASET_ID,
230     AXIS = X_AXIS,
231     SERIES_TYPE = X_SERIES_TYPE,
232     STACK_SERIES_ID = X_STACK_SERIES_ID,
233     BM_FLAG = X_BM_FLAG,
234     BUDGET_FLAG = X_BUDGET_FLAG,
235     DEFAULT_VALUE = X_DEFAULT_VALUE,
236     SERIES_COLOR = X_SERIES_COLOR,
237     BM_COLOR = X_BM_COLOR
238   where INDICATOR = X_INDICATOR
239   and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
240   and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
241   and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
242   and SERIES_ID = X_SERIES_ID;
243 
244   if (sql%notfound) then
245     raise no_data_found;
246   end if;
247 
248   update BSC_KPI_ANALYSIS_MEASURES_TL set
249     NAME = X_NAME,
250     HELP = X_HELP,
251     SOURCE_LANG = userenv('LANG')
252   where INDICATOR = X_INDICATOR
253   and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
254   and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
255   and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
256   and SERIES_ID = X_SERIES_ID
257   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
258 
259   if (sql%notfound) then
260     raise no_data_found;
261   end if;
262 end UPDATE_ROW;
263 
264 procedure DELETE_ROW (
265   X_INDICATOR in NUMBER,
266   X_ANALYSIS_OPTION0 in NUMBER,
267   X_ANALYSIS_OPTION1 in NUMBER,
268   X_ANALYSIS_OPTION2 in NUMBER,
269   X_SERIES_ID in NUMBER
270 ) is
271 begin
272   delete from BSC_KPI_ANALYSIS_MEASURES_TL
273   where INDICATOR = X_INDICATOR
274   and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
275   and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
276   and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
277   and SERIES_ID = X_SERIES_ID;
278 
279   if (sql%notfound) then
280     raise no_data_found;
281   end if;
282 
283   delete from BSC_KPI_ANALYSIS_MEASURES_B
284   where INDICATOR = X_INDICATOR
285   and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
286   and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
287   and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
288   and SERIES_ID = X_SERIES_ID;
289 
290   if (sql%notfound) then
291     raise no_data_found;
292   end if;
293 end DELETE_ROW;
294 
295 procedure ADD_LANGUAGE
296 is
297 begin
298   delete from BSC_KPI_ANALYSIS_MEASURES_TL T
299   where not exists
300     (select NULL
301     from BSC_KPI_ANALYSIS_MEASURES_B B
302     where B.INDICATOR = T.INDICATOR
303     and B.ANALYSIS_OPTION0 = T.ANALYSIS_OPTION0
304     and B.ANALYSIS_OPTION1 = T.ANALYSIS_OPTION1
305     and B.ANALYSIS_OPTION2 = T.ANALYSIS_OPTION2
306     and B.SERIES_ID = T.SERIES_ID
307     );
308 
309   update BSC_KPI_ANALYSIS_MEASURES_TL T set (
310       NAME,
311       HELP
312     ) = (select
313       B.NAME,
314       B.HELP
315     from BSC_KPI_ANALYSIS_MEASURES_TL B
316     where B.INDICATOR = T.INDICATOR
317     and B.ANALYSIS_OPTION0 = T.ANALYSIS_OPTION0
318     and B.ANALYSIS_OPTION1 = T.ANALYSIS_OPTION1
319     and B.ANALYSIS_OPTION2 = T.ANALYSIS_OPTION2
320     and B.SERIES_ID = T.SERIES_ID
321     and B.LANGUAGE = T.SOURCE_LANG)
322   where (
323       T.INDICATOR,
324       T.ANALYSIS_OPTION0,
325       T.ANALYSIS_OPTION1,
326       T.ANALYSIS_OPTION2,
327       T.SERIES_ID,
328       T.LANGUAGE
329   ) in (select
330       SUBT.INDICATOR,
331       SUBT.ANALYSIS_OPTION0,
332       SUBT.ANALYSIS_OPTION1,
333       SUBT.ANALYSIS_OPTION2,
334       SUBT.SERIES_ID,
335       SUBT.LANGUAGE
336     from BSC_KPI_ANALYSIS_MEASURES_TL SUBB, BSC_KPI_ANALYSIS_MEASURES_TL SUBT
337     where SUBB.INDICATOR = SUBT.INDICATOR
338     and SUBB.ANALYSIS_OPTION0 = SUBT.ANALYSIS_OPTION0
339     and SUBB.ANALYSIS_OPTION1 = SUBT.ANALYSIS_OPTION1
340     and SUBB.ANALYSIS_OPTION2 = SUBT.ANALYSIS_OPTION2
341     and SUBB.SERIES_ID = SUBT.SERIES_ID
342     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
343     and (SUBB.NAME <> SUBT.NAME
344       or SUBB.HELP <> SUBT.HELP
345   ));
346 
347   insert into BSC_KPI_ANALYSIS_MEASURES_TL (
348     INDICATOR,
349     ANALYSIS_OPTION0,
350     ANALYSIS_OPTION1,
351     ANALYSIS_OPTION2,
352     SERIES_ID,
353     NAME,
354     HELP,
355     LANGUAGE,
356     SOURCE_LANG
357   ) select
358     B.INDICATOR,
359     B.ANALYSIS_OPTION0,
360     B.ANALYSIS_OPTION1,
361     B.ANALYSIS_OPTION2,
362     B.SERIES_ID,
363     B.NAME,
364     B.HELP,
365     L.LANGUAGE_CODE,
366     B.SOURCE_LANG
367   from BSC_KPI_ANALYSIS_MEASURES_TL B, FND_LANGUAGES L
368   where L.INSTALLED_FLAG in ('I', 'B')
369   and B.LANGUAGE = userenv('LANG')
370   and not exists
371     (select NULL
372     from BSC_KPI_ANALYSIS_MEASURES_TL T
373     where T.INDICATOR = B.INDICATOR
374     and T.ANALYSIS_OPTION0 = B.ANALYSIS_OPTION0
375     and T.ANALYSIS_OPTION1 = B.ANALYSIS_OPTION1
376     and T.ANALYSIS_OPTION2 = B.ANALYSIS_OPTION2
377     and T.SERIES_ID = B.SERIES_ID
378     and T.LANGUAGE = L.LANGUAGE_CODE);
379 end ADD_LANGUAGE;
380 
381 end BSC_KPI_ANALYSIS_MEASURES_PKG;