1 package body BSC_KPIS_PKG as
2 /* $Header: BSCKPIB.pls 115.6 2003/02/12 14:25:54 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_SHARE_FLAG in NUMBER,
7 X_PERIODICITY_ID in NUMBER,
8 X_BM_GROUP_ID in NUMBER,
9 X_APPLY_COLOR_FLAG in NUMBER,
10 X_PROTOTYPE_COLOR in VARCHAR2,
11 X_DISP_ORDER in NUMBER,
12 X_PROTOTYPE_FLAG in NUMBER,
13 X_INDICATOR_TYPE in NUMBER,
14 X_CONFIG_TYPE in NUMBER,
15 X_SOURCE_INDICATOR in NUMBER,
16 X_CSF_ID in NUMBER,
17 X_IND_GROUP_ID in NUMBER,
18 X_NAME in VARCHAR2,
19 X_HELP in VARCHAR2
20 ) is
21 cursor C is select ROWID from BSC_KPIS_B
22 where INDICATOR = X_INDICATOR
23 ;
24 begin
25 insert into BSC_KPIS_B (
26 SHARE_FLAG,
27 PERIODICITY_ID,
28 BM_GROUP_ID,
29 APPLY_COLOR_FLAG,
30 PROTOTYPE_COLOR,
31 DISP_ORDER,
32 PROTOTYPE_FLAG,
33 INDICATOR_TYPE,
34 CONFIG_TYPE,
35 SOURCE_INDICATOR,
36 CSF_ID,
37 IND_GROUP_ID,
38 INDICATOR
39 ) values (
40 X_SHARE_FLAG,
41 X_PERIODICITY_ID,
42 X_BM_GROUP_ID,
43 X_APPLY_COLOR_FLAG,
44 X_PROTOTYPE_COLOR,
45 X_DISP_ORDER,
46 X_PROTOTYPE_FLAG,
47 X_INDICATOR_TYPE,
48 X_CONFIG_TYPE,
49 X_SOURCE_INDICATOR,
50 X_CSF_ID,
51 X_IND_GROUP_ID,
52 X_INDICATOR
53 );
54
55 insert into BSC_KPIS_TL (
56 NAME,
57 HELP,
58 INDICATOR,
59 LANGUAGE,
60 SOURCE_LANG
61 ) select
62 X_NAME,
63 X_HELP,
64 X_INDICATOR,
65 L.LANGUAGE_CODE,
66 userenv('LANG')
67 from FND_LANGUAGES L
68 where L.INSTALLED_FLAG in ('I', 'B')
69 and not exists
70 (select NULL
71 from BSC_KPIS_TL T
72 where T.INDICATOR = X_INDICATOR
73 and T.LANGUAGE = L.LANGUAGE_CODE);
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure LOCK_ROW (
86 X_INDICATOR in NUMBER,
87 X_SHARE_FLAG in NUMBER,
88 X_PERIODICITY_ID in NUMBER,
89 X_BM_GROUP_ID in NUMBER,
90 X_APPLY_COLOR_FLAG in NUMBER,
91 X_PROTOTYPE_COLOR in VARCHAR2,
92 X_DISP_ORDER in NUMBER,
93 X_PROTOTYPE_FLAG in NUMBER,
94 X_INDICATOR_TYPE in NUMBER,
95 X_CONFIG_TYPE in NUMBER,
96 X_SOURCE_INDICATOR in NUMBER,
97 X_CSF_ID in NUMBER,
98 X_IND_GROUP_ID in NUMBER,
99 X_NAME in VARCHAR2,
100 X_HELP in VARCHAR2
101 ) is
102 cursor c is select
103 SHARE_FLAG,
104 PERIODICITY_ID,
105 BM_GROUP_ID,
106 APPLY_COLOR_FLAG,
107 PROTOTYPE_COLOR,
108 DISP_ORDER,
109 PROTOTYPE_FLAG,
110 INDICATOR_TYPE,
111 CONFIG_TYPE,
112 SOURCE_INDICATOR,
113 CSF_ID,
114 IND_GROUP_ID
115 from BSC_KPIS_B
116 where INDICATOR = X_INDICATOR
117 for update of INDICATOR nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 NAME,
122 HELP,
123 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
124 from BSC_KPIS_TL
125 where INDICATOR = X_INDICATOR
126 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
127 for update of INDICATOR nowait;
128 begin
129 open c;
130 fetch c into recinfo;
131 if (c%notfound) then
132 close c;
133 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
134 app_exception.raise_exception;
135 end if;
136 close c;
137 if ( (recinfo.SHARE_FLAG = X_SHARE_FLAG)
138 AND (recinfo.PERIODICITY_ID = X_PERIODICITY_ID)
139 AND (recinfo.BM_GROUP_ID = X_BM_GROUP_ID)
140 AND (recinfo.APPLY_COLOR_FLAG = X_APPLY_COLOR_FLAG)
141 AND ((recinfo.PROTOTYPE_COLOR = X_PROTOTYPE_COLOR)
142 OR ((recinfo.PROTOTYPE_COLOR is null) AND (X_PROTOTYPE_COLOR is null)))
143 AND (recinfo.DISP_ORDER = X_DISP_ORDER)
144 AND (recinfo.PROTOTYPE_FLAG = X_PROTOTYPE_FLAG)
145 AND (recinfo.INDICATOR_TYPE = X_INDICATOR_TYPE)
146 AND (recinfo.CONFIG_TYPE = X_CONFIG_TYPE)
147 AND ((recinfo.SOURCE_INDICATOR = X_SOURCE_INDICATOR)
148 OR ((recinfo.SOURCE_INDICATOR is null) AND (X_SOURCE_INDICATOR is null)))
149 AND (recinfo.CSF_ID = X_CSF_ID)
150 AND (recinfo.IND_GROUP_ID = X_IND_GROUP_ID)
151 ) then
152 null;
153 else
154 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
155 app_exception.raise_exception;
156 end if;
157
158 for tlinfo in c1 loop
159 if (tlinfo.BASELANG = 'Y') then
160 if ( (tlinfo.NAME = X_NAME)
161 AND (tlinfo.HELP = X_HELP)
162 ) then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168 end if;
169 end loop;
170 return;
171 end LOCK_ROW;
172
173 procedure UPDATE_ROW (
174 X_INDICATOR in NUMBER,
175 X_SHARE_FLAG in NUMBER,
176 X_PERIODICITY_ID in NUMBER,
177 X_BM_GROUP_ID in NUMBER,
178 X_APPLY_COLOR_FLAG in NUMBER,
179 X_PROTOTYPE_COLOR in VARCHAR2,
180 X_DISP_ORDER in NUMBER,
181 X_PROTOTYPE_FLAG in NUMBER,
182 X_INDICATOR_TYPE in NUMBER,
183 X_CONFIG_TYPE in NUMBER,
184 X_SOURCE_INDICATOR in NUMBER,
185 X_CSF_ID in NUMBER,
186 X_IND_GROUP_ID in NUMBER,
187 X_NAME in VARCHAR2,
188 X_HELP in VARCHAR2
189 ) is
190 begin
191 update BSC_KPIS_B set
192 SHARE_FLAG = X_SHARE_FLAG,
193 PERIODICITY_ID = X_PERIODICITY_ID,
194 BM_GROUP_ID = X_BM_GROUP_ID,
195 APPLY_COLOR_FLAG = X_APPLY_COLOR_FLAG,
196 PROTOTYPE_COLOR = X_PROTOTYPE_COLOR,
197 DISP_ORDER = X_DISP_ORDER,
198 PROTOTYPE_FLAG = X_PROTOTYPE_FLAG,
199 INDICATOR_TYPE = X_INDICATOR_TYPE,
200 CONFIG_TYPE = X_CONFIG_TYPE,
201 SOURCE_INDICATOR = X_SOURCE_INDICATOR,
202 CSF_ID = X_CSF_ID,
203 IND_GROUP_ID = X_IND_GROUP_ID
204 where INDICATOR = X_INDICATOR;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 update BSC_KPIS_TL set
211 NAME = X_NAME,
212 HELP = X_HELP,
213 SOURCE_LANG = userenv('LANG')
214 where INDICATOR = X_INDICATOR
215 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end UPDATE_ROW;
221
222 procedure DELETE_ROW (
223 X_INDICATOR in NUMBER
224 ) is
225 begin
226 delete from BSC_KPIS_TL
227 where INDICATOR = X_INDICATOR;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 delete from BSC_KPIS_B
234 where INDICATOR = X_INDICATOR;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239 end DELETE_ROW;
240
241 procedure ADD_LANGUAGE
242 is
243 begin
244 delete from BSC_KPIS_TL T
245 where not exists
246 (select NULL
247 from BSC_KPIS_B B
248 where B.INDICATOR = T.INDICATOR
249 );
250
251 update BSC_KPIS_TL T set (
252 NAME,
253 HELP
254 ) = (select
255 B.NAME,
256 B.HELP
257 from BSC_KPIS_TL B
258 where B.INDICATOR = T.INDICATOR
259 and B.LANGUAGE = T.SOURCE_LANG)
260 where (
261 T.INDICATOR,
262 T.LANGUAGE
263 ) in (select
264 SUBT.INDICATOR,
265 SUBT.LANGUAGE
266 from BSC_KPIS_TL SUBB, BSC_KPIS_TL SUBT
267 where SUBB.INDICATOR = SUBT.INDICATOR
268 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
269 and (SUBB.NAME <> SUBT.NAME
270 or SUBB.HELP <> SUBT.HELP
271 ));
272
273 insert into BSC_KPIS_TL (
274 NAME,
275 HELP,
276 INDICATOR,
277 LANGUAGE,
278 SOURCE_LANG
279 ) select
280 B.NAME,
281 B.HELP,
282 B.INDICATOR,
283 L.LANGUAGE_CODE,
284 B.SOURCE_LANG
285 from BSC_KPIS_TL B, FND_LANGUAGES L
286 where L.INSTALLED_FLAG in ('I', 'B')
287 and B.LANGUAGE = userenv('LANG')
288 and not exists
289 (select NULL
290 from BSC_KPIS_TL T
291 where T.INDICATOR = B.INDICATOR
292 and T.LANGUAGE = L.LANGUAGE_CODE);
293 end ADD_LANGUAGE;
294
295 end BSC_KPIS_PKG;
[Home] [Help]