1 PACKAGE BODY BSC_SYS_BENCHMARKS_PKG as
2 /* $Header: BSCBMTLB.pls 120.1 2005/08/17 12:35:12 hcamacho noship $ */
3 PROCEDURE INSERT_ROW (
4 X_BM_ID IN NUMBER
5 , X_COLOR IN NUMBER
6 , X_DATA_TYPE IN NUMBER
7 , X_SOURCE_TYPE IN NUMBER
8 , X_PERIODICITY_ID IN NUMBER
9 , X_NO_DISPLAY_FLAG IN NUMBER
10 , X_NAME IN VARCHAR2
11 ) IS
12 BEGIN
13
14 BSC_SYS_BENCHMARKS_PKG.INSERT_ROW
15 (
16 p_Bm_id => X_BM_ID
17 , p_Color => X_COLOR
18 , p_Data_type => X_DATA_TYPE
19 , p_Source_type => X_SOURCE_TYPE
20 , p_Periodicity_id => X_PERIODICITY_ID
21 , p_No_display_flag => X_NO_DISPLAY_FLAG
22 , p_Name => X_NAME
23 , p_Created_by => FND_GLOBAL.USER_ID
24 , p_Creation_date => SYSDATE
25 , p_Last_updated_by => FND_GLOBAL.USER_ID
26 , p_Last_update_date => SYSDATE
27 , p_Last_update_login => FND_GLOBAL.LOGIN_ID
28 );
29
30 END INSERT_ROW;
31
32
33
34 PROCEDURE INSERT_ROW (
35 p_Bm_id IN NUMBER
36 , p_Color IN NUMBER
37 , p_Data_type IN NUMBER
38 , p_Source_type IN NUMBER
39 , p_Periodicity_id IN NUMBER
40 , p_No_display_flag IN NUMBER
41 , p_Name IN VARCHAR2
42 , p_Created_by IN NUMBER
43 , p_Creation_date IN DATE
44 , p_Last_updated_by IN NUMBER
45 , p_Last_update_date IN DATE
46 , p_Last_update_login IN NUMBER
47 ) IS
48 BEGIN
49
50 INSERT INTO bsc_sys_benchmarks_b (
51 bm_id
52 , color
53 , data_type
54 , source_type
55 , periodicity_id
56 , no_display_flag
57 , created_by
58 , creation_date
59 , last_updated_by
60 , last_update_date
61 , last_update_login
62 ) VALUES (
63 p_Bm_id
64 , p_Color
65 , p_Data_type
66 , p_Source_type
67 , p_Periodicity_id
68 , p_No_display_flag
69 , p_Created_by
70 , p_Creation_date
71 , p_Last_updated_by
72 , p_Last_update_date
73 , NVL(p_Last_update_login,FND_GLOBAL.login_id)
74 );
75
76 INSERT INTO bsc_sys_benchmarks_tl (
77 bm_id
78 , name
79 , language
80 , source_lang
81 ) SELECT
82 p_Bm_id
83 , p_Name
84 , l.LANGUAGE_CODE
85 , USERENV('LANG')
86 FROM fnd_languages l
87 WHERE l.installed_flag IN ('I', 'B')
88 AND NOT EXISTS
89 ( SELECT NULL
90 FROM bsc_sys_benchmarks_tl t
91 WHERE t.bm_id = p_Bm_id
92 AND t.language = l.language_code
93 );
94
95 END INSERT_ROW;
96
97
98
99 PROCEDURE TRANSLATE_ROW
100 (
101 p_Bm_id IN NUMBER
102 , p_Name IN VARCHAR2
103 )IS
104 BEGIN
105 UPDATE bsc_sys_benchmarks_tl
106 SET name = NVL(p_Name,name)
107 , source_lang = USERENV('LANG')
108 WHERE USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
109 AND bm_id = p_Bm_id;
110
111 END TRANSLATE_ROW;
112
113 procedure LOCK_ROW (
114 X_BM_ID in NUMBER,
115 X_COLOR in NUMBER,
116 X_DATA_TYPE in NUMBER,
117 X_SOURCE_TYPE in NUMBER,
118 X_PERIODICITY_ID in NUMBER,
119 X_NO_DISPLAY_FLAG in NUMBER,
120 X_NAME in VARCHAR2
121 ) is
122 cursor c is select
123 COLOR,
124 DATA_TYPE,
125 SOURCE_TYPE,
126 PERIODICITY_ID,
127 NO_DISPLAY_FLAG
128 from BSC_SYS_BENCHMARKS_B
129 where BM_ID = X_BM_ID
130 for update of BM_ID nowait;
131 recinfo c%rowtype;
132
133 cursor c1 is select
134 NAME,
135 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
136 from BSC_SYS_BENCHMARKS_TL
137 where BM_ID = X_BM_ID
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139 for update of BM_ID nowait;
140 begin
141 open c;
142 fetch c into recinfo;
143 if (c%notfound) then
144 close c;
145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146 app_exception.raise_exception;
147 end if;
148 close c;
149 if ( (recinfo.COLOR = X_COLOR)
150 AND (recinfo.DATA_TYPE = X_DATA_TYPE)
151 AND (recinfo.SOURCE_TYPE = X_SOURCE_TYPE)
152 AND ((recinfo.PERIODICITY_ID = X_PERIODICITY_ID)
153 OR ((recinfo.PERIODICITY_ID is null) AND (X_PERIODICITY_ID is null)))
154 AND ((recinfo.NO_DISPLAY_FLAG = X_NO_DISPLAY_FLAG)
155 OR ((recinfo.NO_DISPLAY_FLAG is null) AND (X_NO_DISPLAY_FLAG is null)))
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162
163 for tlinfo in c1 loop
164 if (tlinfo.BASELANG = 'Y') then
165 if ( (tlinfo.NAME = X_NAME)
166 ) then
167 null;
168 else
169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
170 app_exception.raise_exception;
171 end if;
172 end if;
173 end loop;
174 return;
175 end LOCK_ROW;
176
177 PROCEDURE UPDATE_ROW (
178 p_Bm_id IN NUMBER
179 , p_Color IN NUMBER
180 , p_Data_type IN NUMBER
181 , p_Source_type IN NUMBER
182 , p_Periodicity_id IN NUMBER
183 , p_No_display_flag IN NUMBER
184 , p_Name IN VARCHAR2
185 , p_Last_updated_by IN NUMBER
186 , p_Last_update_date IN DATE
187 , p_Last_update_login IN NUMBER
188 , p_Custom_mode IN VARCHAR2
189 ) IS
190 l_prev_last_updated_by BSC_SYS_BENCHMARKS_B.last_updated_by%TYPE;
191 l_prev_last_update_date BSC_SYS_BENCHMARKS_B.last_update_date%TYPE;
192 BEGIN
193
194 SELECT NVL(last_updated_by,FND_GLOBAL.USER_ID)
195 , NVL(last_update_date,SYSDATE)
196 INTO l_prev_last_updated_by,l_prev_last_update_date
197 FROM bsc_sys_benchmarks_b
198 WHERE bm_id = p_Bm_id;
199
200 IF(SQL%NOTFOUND)THEN
201 RAISE NO_DATA_FOUND;
202 END IF;
203
204 IF (FND_LOAD_UTIL.UPLOAD_TEST(p_Last_updated_by
205 , p_Last_update_date
206 , l_prev_last_updated_by
207 , l_prev_last_update_date
208 , p_Custom_mode)) THEN
209
210
211 UPDATE bsc_sys_benchmarks_b
212 SET color = p_Color
213 , data_type = p_Data_type
214 , source_type = p_Source_type
215 , periodicity_id = p_Periodicity_id
216 , no_display_flag = p_No_display_flag
217 , last_updated_by = p_Last_updated_by
218 , last_update_date = p_Last_update_date
219 , last_update_login= NVL(p_Last_update_login,FND_GLOBAL.LOGIN_ID)
220 WHERE bm_id = p_Bm_id;
221
222 IF (SQL%NOTFOUND) THEN
223 RAISE NO_DATA_FOUND;
224 END IF;
225
226 UPDATE bsc_sys_benchmarks_tl
227 SET name = p_Name
228 , source_lang = USERENV('LANG')
229 WHERE bm_id = p_Bm_id
230 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
231
232 IF (SQL%NOTFOUND) THEN
233 RAISE NO_DATA_FOUND;
234 END IF;
235 END IF;
236 END UPDATE_ROW;
237
238
239 PROCEDURE DELETE_ROW (
240 X_BM_ID in NUMBER
241 ) is
242 begin
243 delete from BSC_SYS_BENCHMARKS_TL
244 where BM_ID = X_BM_ID;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249
250 delete from BSC_SYS_BENCHMARKS_B
251 where BM_ID = X_BM_ID;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256 end DELETE_ROW;
257
258 procedure ADD_LANGUAGE
259 is
260 begin
261 delete from BSC_SYS_BENCHMARKS_TL T
262 where not exists
263 (select NULL
264 from BSC_SYS_BENCHMARKS_B B
265 where B.BM_ID = T.BM_ID
266 );
267
268 update BSC_SYS_BENCHMARKS_TL T set (
269 NAME
270 ) = (select
271 B.NAME
272 from BSC_SYS_BENCHMARKS_TL B
273 where B.BM_ID = T.BM_ID
274 and B.LANGUAGE = T.SOURCE_LANG)
275 where (
276 T.BM_ID,
277 T.LANGUAGE
278 ) in (select
279 SUBT.BM_ID,
280 SUBT.LANGUAGE
281 from BSC_SYS_BENCHMARKS_TL SUBB, BSC_SYS_BENCHMARKS_TL SUBT
282 where SUBB.BM_ID = SUBT.BM_ID
283 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
284 and (SUBB.NAME <> SUBT.NAME
285 ));
286
287 insert into BSC_SYS_BENCHMARKS_TL (
288 BM_ID,
289 NAME,
290 LANGUAGE,
291 SOURCE_LANG
292 ) select
293 B.BM_ID,
294 B.NAME,
295 L.LANGUAGE_CODE,
296 B.SOURCE_LANG
297 from BSC_SYS_BENCHMARKS_TL B, FND_LANGUAGES L
298 where L.INSTALLED_FLAG in ('I', 'B')
299 and B.LANGUAGE = userenv('LANG')
300 and not exists
301 (select NULL
302 from BSC_SYS_BENCHMARKS_TL T
303 where T.BM_ID = B.BM_ID
304 and T.LANGUAGE = L.LANGUAGE_CODE);
305 end ADD_LANGUAGE;
306
307 END BSC_SYS_BENCHMARKS_PKG;
[Home] [Help]