[Home] [Help]
PACKAGE BODY: APPS.IBC_LABELS_PKG
Source
1 PACKAGE BODY Ibc_Labels_Pkg AS
2 /* $Header: ibctlabb.pls 120.2 2006/05/24 12:21:34 sharma ship $*/
3
4 -- Purpose: Table Handler for Ibc_Labels table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- vicho 11/05/2002 Remove G_MISS defaulting on UPDATE_ROW
11 -- Sharma 07/04/2005 Modified LOAD_ROW and created
12 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
13
14
15 PROCEDURE INSERT_ROW (
16 x_ROWID OUT NOCOPY VARCHAR2,
17 p_LABEL_CODE IN VARCHAR2,
18 p_OBJECT_VERSION_NUMBER IN NUMBER,
19 p_LABEL_NAME IN VARCHAR2,
20 p_DESCRIPTION IN VARCHAR2,
21 p_CREATION_DATE IN DATE,
22 p_CREATED_BY IN NUMBER,
23 p_LAST_UPDATE_DATE IN DATE,
24 p_LAST_UPDATED_BY IN NUMBER,
25 p_LAST_UPDATE_LOGIN IN NUMBER
26 ) IS
27 CURSOR C IS SELECT ROWID FROM IBC_LABELS_B
28 WHERE LABEL_CODE = p_LABEL_CODE
29 ;
30 BEGIN
31 INSERT INTO IBC_LABELS_B (
32 LABEL_CODE,
33 OBJECT_VERSION_NUMBER,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) VALUES (
40 p_LABEL_CODE,
41 p_OBJECT_VERSION_NUMBER,
42 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
43 p_creation_date),
44 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
45 NULL, FND_GLOBAL.user_id, p_created_by),
46 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
47 p_last_update_date),
48 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
49 NULL, FND_GLOBAL.user_id, p_last_updated_by),
50 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
51 NULL, FND_GLOBAL.login_id, p_last_update_login)
52 );
53
54 INSERT INTO IBC_LABELS_TL (
55 LABEL_CODE,
56 LABEL_NAME,
57 DESCRIPTION,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 LANGUAGE,
64 SOURCE_LANG
65 ) SELECT
66 p_LABEL_CODE,
67 p_LABEL_NAME,
68 DECODE(p_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_DESCRIPTION),
69 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
70 p_creation_date) ,
71 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
72 NULL, FND_GLOBAL.user_id, p_created_by),
73 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
74 p_last_update_date),
75 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
76 NULL, FND_GLOBAL.user_id, p_last_updated_by),
77 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
78 NULL, FND_GLOBAL.login_id, p_last_update_login),
79 L.LANGUAGE_CODE,
80 USERENV('LANG')
81 FROM FND_LANGUAGES L
82 WHERE L.INSTALLED_FLAG IN ('I', 'B')
83 AND NOT EXISTS
84 (SELECT NULL
85 FROM IBC_LABELS_TL T
86 WHERE T.LABEL_CODE = p_LABEL_CODE
87 AND T.LANGUAGE = L.LANGUAGE_CODE);
88
89 OPEN c;
90 FETCH c INTO x_ROWID;
91 IF (c%NOTFOUND) THEN
92 CLOSE c;
93 RAISE NO_DATA_FOUND;
94 END IF;
95 CLOSE c;
96
97 END INSERT_ROW;
98
99 PROCEDURE LOCK_ROW (
100 p_LABEL_CODE IN VARCHAR2,
101 p_OBJECT_VERSION_NUMBER IN NUMBER,
102 p_LABEL_NAME IN VARCHAR2,
103 p_DESCRIPTION IN VARCHAR2
104 ) IS
105 CURSOR c IS SELECT
106 OBJECT_VERSION_NUMBER
107 FROM IBC_LABELS_B
108 WHERE LABEL_CODE = p_LABEL_CODE
109 FOR UPDATE OF LABEL_CODE NOWAIT;
110 recinfo c%ROWTYPE;
111
112 CURSOR c1 IS SELECT
113 LABEL_NAME,
114 DESCRIPTION,
115 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
116 FROM IBC_LABELS_TL
117 WHERE LABEL_CODE = p_LABEL_CODE
118 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
119 FOR UPDATE OF LABEL_CODE NOWAIT;
120 BEGIN
121 OPEN c;
122 FETCH c INTO recinfo;
123 IF (c%NOTFOUND) THEN
124 CLOSE c;
125 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
126 app_exception.raise_exception;
127 END IF;
128 CLOSE c;
129 IF ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
130 ) THEN
131 NULL;
132 ELSE
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 END IF;
136
137 FOR tlinfo IN c1 LOOP
138 IF (tlinfo.BASELANG = 'Y') THEN
139 IF ( (tlinfo.LABEL_NAME = p_LABEL_NAME)
140 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
141 OR ((tlinfo.DESCRIPTION IS NULL) AND (p_DESCRIPTION IS NULL)))
142 ) THEN
143 NULL;
144 ELSE
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 END IF;
148 END IF;
149 END LOOP;
150 RETURN;
151 END LOCK_ROW;
152
153 PROCEDURE UPDATE_ROW (
154 p_LABEL_CODE IN VARCHAR2,
155 p_DESCRIPTION IN VARCHAR2,
156 p_LABEL_NAME IN VARCHAR2,
157 p_LAST_UPDATED_BY IN NUMBER,
158 p_LAST_UPDATE_DATE IN DATE,
159 p_LAST_UPDATE_LOGIN IN NUMBER,
160 p_OBJECT_VERSION_NUMBER IN NUMBER
161 ) IS
162 BEGIN
163 UPDATE IBC_LABELS_B SET
164 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
165 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
166 NULL, SYSDATE, p_last_update_date),
167 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
168 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
169 p_last_updated_by),
170 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
171 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
172 p_last_update_login)
173 WHERE LABEL_CODE = p_LABEL_CODE
174 AND object_version_number = DECODE(p_object_version_number,
175 FND_API.G_MISS_NUM,object_version_number,
176 NULL,object_version_number,
177 p_object_version_number);
178
179 IF (SQL%NOTFOUND) THEN
180 RAISE NO_DATA_FOUND;
181 END IF;
182
183 UPDATE IBC_LABELS_TL SET
184 LABEL_NAME = DECODE(p_LABEL_NAME,FND_API.G_MISS_CHAR,NULL,NULL,LABEL_NAME,p_LABEL_NAME),
185 DESCRIPTION = DECODE(p_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,NULL,DESCRIPTION,p_DESCRIPTION),
186 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
187 NULL, SYSDATE, p_last_update_date),
188 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
189 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
190 p_last_updated_by),
191 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
192 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
193 p_last_update_login),
194 SOURCE_LANG = USERENV('LANG')
195 WHERE LABEL_CODE = p_LABEL_CODE
196 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
197
198 IF (SQL%NOTFOUND) THEN
199 RAISE NO_DATA_FOUND;
200 END IF;
201 END UPDATE_ROW;
202
203 PROCEDURE DELETE_ROW (
204 p_LABEL_CODE IN VARCHAR2
205 ) IS
206 BEGIN
207 DELETE FROM IBC_LABELS_TL
208 WHERE LABEL_CODE = p_LABEL_CODE;
209
210 IF (SQL%NOTFOUND) THEN
211 RAISE NO_DATA_FOUND;
212 END IF;
213
214 DELETE FROM IBC_LABELS_B
215 WHERE LABEL_CODE = p_LABEL_CODE;
216
217 IF (SQL%NOTFOUND) THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220 END DELETE_ROW;
221
222 PROCEDURE ADD_LANGUAGE
223 IS
224 BEGIN
225 DELETE FROM IBC_LABELS_TL T
226 WHERE NOT EXISTS
227 (SELECT NULL
228 FROM IBC_LABELS_B B
229 WHERE B.LABEL_CODE = T.LABEL_CODE
230 );
231
232 UPDATE IBC_LABELS_TL T SET (
233 LABEL_NAME,
234 DESCRIPTION
235 ) = (SELECT
236 B.LABEL_NAME,
237 B.DESCRIPTION
238 FROM IBC_LABELS_TL B
239 WHERE B.LABEL_CODE = T.LABEL_CODE
240 AND B.LANGUAGE = T.SOURCE_LANG)
241 WHERE (
242 T.LABEL_CODE,
243 T.LANGUAGE
244 ) IN (SELECT
245 SUBT.LABEL_CODE,
246 SUBT.LANGUAGE
247 FROM IBC_LABELS_TL SUBB, IBC_LABELS_TL SUBT
248 WHERE SUBB.LABEL_CODE = SUBT.LABEL_CODE
249 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
250 AND (SUBB.LABEL_NAME <> SUBT.LABEL_NAME
251 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
252 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
253 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
254 ));
255
256 INSERT INTO IBC_LABELS_TL (
257 LABEL_CODE,
258 LABEL_NAME,
259 DESCRIPTION,
260 CREATED_BY,
261 CREATION_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN,
265 LANGUAGE,
266 SOURCE_LANG
267 ) SELECT /*+ ORDERED */
268 B.LABEL_CODE,
269 B.LABEL_NAME,
270 B.DESCRIPTION,
271 B.CREATED_BY,
272 B.CREATION_DATE,
273 B.LAST_UPDATED_BY,
274 B.LAST_UPDATE_DATE,
275 B.LAST_UPDATE_LOGIN,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 FROM IBC_LABELS_TL B, FND_LANGUAGES L
279 WHERE L.INSTALLED_FLAG IN ('I', 'B')
280 AND B.LANGUAGE = USERENV('LANG')
281 AND NOT EXISTS
282 (SELECT NULL
283 FROM IBC_LABELS_TL T
284 WHERE T.LABEL_CODE = B.LABEL_CODE
285 AND T.LANGUAGE = L.LANGUAGE_CODE);
286 END ADD_LANGUAGE;
287
288
289 PROCEDURE LOAD_ROW (
290 p_upload_mode IN VARCHAR2,
291 p_label_CODE IN VARCHAR2,
292 p_label_NAME IN VARCHAR2,
293 p_DESCRIPTION IN VARCHAR2,
294 p_OWNER IN VARCHAR2,
295 p_last_update_date IN VARCHAR2) IS
296 BEGIN
297 DECLARE
298 l_user_id NUMBER := 0;
299 l_row_id VARCHAR2(64);
300 l_last_update_date DATE;
301
302 db_user_id NUMBER := 0;
303 db_last_update_date DATE;
304
305 BEGIN
306 --get last updated by user id
307 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
308
309 Ibc_Labels_Pkg.Update_row (
310 p_Label_CODE => p_Label_CODE,
311 p_Label_NAME => nvl(p_Label_NAME,FND_API.G_MISS_CHAR),
312 p_DESCRIPTION => nvl(p_DESCRIPTION,FND_API.G_MISS_CHAR),
313 p_LAST_UPDATED_BY => l_user_id,
314 p_LAST_UPDATE_DATE => sysdate,
315 p_LAST_UPDATE_LOGIN => 0,
316 p_OBJECT_VERSION_NUMBER => NULL);
317
318
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321
322 Ibc_Labels_Pkg.insert_row (
323 x_rowid => l_row_id,
324 p_Label_CODE => p_Label_CODE,
325 p_Label_NAME => p_Label_NAME,
326 p_CREATED_BY => l_user_id,
327 p_CREATION_DATE => SYSDATE,
328 p_DESCRIPTION => p_DESCRIPTION,
329 p_LAST_UPDATED_BY => l_user_id,
330 p_LAST_UPDATE_DATE => SYSDATE,
331 p_LAST_UPDATE_LOGIN => 0,
332 p_OBJECT_VERSION_NUMBER => 1);
333 END;
334 END LOAD_ROW;
335
336 PROCEDURE TRANSLATE_ROW (
337 p_upload_mode IN VARCHAR2,
338 p_LABEL_CODE IN VARCHAR2,
339 p_LABEL_NAME IN VARCHAR2,
340 p_DESCRIPTION IN VARCHAR2,
341 p_OWNER IN VARCHAR2,
342 p_last_update_date IN VARCHAR2) IS
343 BEGIN
344
345 DECLARE
346 l_user_id NUMBER := 0;
347 l_row_id VARCHAR2(64);
348 l_last_update_date DATE;
349
350 db_user_id NUMBER := 0;
351 db_last_update_date DATE;
352
353 BEGIN
354 --get last updated by user id
355 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
356
357
358 -- Only update rows which have not been altered by user
359 UPDATE IBC_LABELS_TL
360 SET description = p_DESCRIPTION,
361 LABEL_NAME = p_LABEL_NAME,
362 source_lang = USERENV('LANG'),
363 last_update_date = sysdate,
364 last_updated_by = l_user_id,
365 last_update_login = 0
366 WHERE LABEL_CODE = p_LABEL_CODE
367 AND USERENV('LANG') IN (LANGUAGE, source_lang);
368
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 NULL;
372 END;
373
374
375 END TRANSLATE_ROW;
376
377 PROCEDURE LOAD_SEED_ROW (
378 p_upload_mode VARCHAR2,
379 p_label_CODE IN VARCHAR2,
380 p_label_NAME IN VARCHAR2,
381 p_DESCRIPTION IN VARCHAR2,
382 p_OWNER IN VARCHAR2,
383 p_last_update_date VARCHAR2) IS
384
385 BEGIN
386 IF (p_UPLOAD_MODE = 'NLS') THEN
387 IBC_LABELS_PKG.TRANSLATE_ROW (
388 p_upload_mode => p_upload_mode,
389 p_LABEL_CODE => p_LABEL_CODE,
390 p_LABEL_NAME => p_LABEL_NAME,
391 p_DESCRIPTION => p_DESCRIPTION,
392 p_OWNER =>p_OWNER,
393 p_last_update_date => p_LAST_UPDATE_DATE);
394 ELSE
395 IBC_LABELS_PKG.LOAD_ROW (
396 p_upload_mode => p_upload_mode,
397 p_LABEL_CODE => p_LABEL_CODE,
398 p_LABEL_NAME => p_LABEL_NAME,
399 p_DESCRIPTION => p_DESCRIPTION,
400 p_OWNER =>p_OWNER,
401 p_last_update_date => p_LAST_UPDATE_DATE);
402 END IF;
403 END LOAD_SEED_ROW;
404
405
406 --
407
408 END Ibc_Labels_Pkg;