1 package body FWK_TBX_LOOKUP_CODES_PKG as
2 /* $Header: fwktbxlookupcodesb.pls 120.2.12000000.4 2007/07/19 12:05:40 pbhamidi ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_LOOKUP_CODE in VARCHAR2,
7 X_START_DATE_ACTIVE in DATE,
8 X_END_DATE_ACTIVE in DATE,
9 X_MEANING in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from FWK_TBX_LOOKUP_CODES_B
18 where LOOKUP_TYPE = X_LOOKUP_TYPE
19 and LOOKUP_CODE = X_LOOKUP_CODE
20 ;
21 begin
22 insert into FWK_TBX_LOOKUP_CODES_B (
23 LOOKUP_TYPE,
24 LOOKUP_CODE,
25 START_DATE_ACTIVE,
26 END_DATE_ACTIVE,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_LOOKUP_TYPE,
34 X_LOOKUP_CODE,
35 X_START_DATE_ACTIVE,
36 X_END_DATE_ACTIVE,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 insert into FWK_TBX_LOOKUP_CODES_TL (
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_LOGIN,
50 LOOKUP_TYPE,
51 LOOKUP_CODE,
52 MEANING,
53 DESCRIPTION,
54 LANGUAGE,
55 SOURCE_LANG
56 ) select
57 X_LAST_UPDATE_DATE,
58 X_LAST_UPDATED_BY,
59 X_CREATION_DATE,
60 X_CREATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 X_LOOKUP_TYPE,
63 X_LOOKUP_CODE,
64 X_MEANING,
65 X_DESCRIPTION,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from FWK_TBX_LOOKUP_CODES_TL T
73 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
74 and T.LOOKUP_CODE = X_LOOKUP_CODE
75 and T.LANGUAGE = L.LANGUAGE_CODE);
76
77 open c;
78 fetch c into X_ROWID;
79 if (c%notfound) then
80 close c;
81 raise no_data_found;
82 end if;
83 close c;
84
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 X_LOOKUP_TYPE in VARCHAR2,
89 X_LOOKUP_CODE in VARCHAR2,
90 X_START_DATE_ACTIVE in DATE,
91 X_END_DATE_ACTIVE in DATE,
92 X_MEANING in VARCHAR2,
93 X_DESCRIPTION in VARCHAR2
94 ) is
95 cursor c is select
96 START_DATE_ACTIVE,
97 END_DATE_ACTIVE
98 from FWK_TBX_LOOKUP_CODES_B
99 where LOOKUP_TYPE = X_LOOKUP_TYPE
100 and LOOKUP_CODE = X_LOOKUP_CODE
101 for update of LOOKUP_TYPE nowait;
102 recinfo c%rowtype;
103
104 cursor c1 is select
105 MEANING,
106 DESCRIPTION,
107 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
108 from FWK_TBX_LOOKUP_CODES_TL
109 where LOOKUP_TYPE = X_LOOKUP_TYPE
110 and LOOKUP_CODE = X_LOOKUP_CODE
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of LOOKUP_TYPE nowait;
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
123 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
124 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
125 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132
133 for tlinfo in c1 loop
134 if (tlinfo.BASELANG = 'Y') then
135 if ( (tlinfo.MEANING = X_MEANING)
136 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
137 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
138 ) then
139 null;
140 else
141 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
142 app_exception.raise_exception;
143 end if;
144 end if;
145 end loop;
146 return;
147 end LOCK_ROW;
148
149 procedure UPDATE_ROW (
150 X_LOOKUP_TYPE in VARCHAR2,
151 X_LOOKUP_CODE in VARCHAR2,
152 X_START_DATE_ACTIVE in DATE,
153 X_END_DATE_ACTIVE in DATE,
154 X_MEANING in VARCHAR2,
155 X_DESCRIPTION in VARCHAR2,
156 X_LAST_UPDATE_DATE in DATE,
157 X_LAST_UPDATED_BY in NUMBER,
158 X_LAST_UPDATE_LOGIN in NUMBER
159 ) is
160 begin
161 update FWK_TBX_LOOKUP_CODES_B set
162 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
163 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
164 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
165 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
167 where LOOKUP_TYPE = X_LOOKUP_TYPE
168 and LOOKUP_CODE = X_LOOKUP_CODE;
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173
174 update FWK_TBX_LOOKUP_CODES_TL set
175 MEANING = X_MEANING,
176 DESCRIPTION = X_DESCRIPTION,
177 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
180 SOURCE_LANG = userenv('LANG')
181 where LOOKUP_TYPE = X_LOOKUP_TYPE
182 and LOOKUP_CODE = X_LOOKUP_CODE
183 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188 end UPDATE_ROW;
189
190 procedure DELETE_ROW (
191 X_LOOKUP_TYPE in VARCHAR2,
192 X_LOOKUP_CODE in VARCHAR2
193 ) is
194 begin
195 delete from FWK_TBX_LOOKUP_CODES_TL
196 where LOOKUP_TYPE = X_LOOKUP_TYPE
197 and LOOKUP_CODE = X_LOOKUP_CODE;
198
199 if (sql%notfound) then
200 raise no_data_found;
201 end if;
202
203 delete from FWK_TBX_LOOKUP_CODES_B
204 where LOOKUP_TYPE = X_LOOKUP_TYPE
205 and LOOKUP_CODE = X_LOOKUP_CODE;
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end DELETE_ROW;
211
212 procedure ADD_LANGUAGE
213 is
214 begin
215 delete from FWK_TBX_LOOKUP_CODES_TL T
216 where not exists
217 (select NULL
218 from FWK_TBX_LOOKUP_CODES_B B
219 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
220 and B.LOOKUP_CODE = T.LOOKUP_CODE
221 );
222
223 update FWK_TBX_LOOKUP_CODES_TL T set (
224 MEANING,
225 DESCRIPTION
226 ) = (select
227 B.MEANING,
228 B.DESCRIPTION
229 from FWK_TBX_LOOKUP_CODES_TL B
230 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
231 and B.LOOKUP_CODE = T.LOOKUP_CODE
232 and B.LANGUAGE = T.SOURCE_LANG)
233 where (
234 T.LOOKUP_TYPE,
235 T.LOOKUP_CODE,
236 T.LANGUAGE
237 ) in (select
238 SUBT.LOOKUP_TYPE,
239 SUBT.LOOKUP_CODE,
240 SUBT.LANGUAGE
241 from FWK_TBX_LOOKUP_CODES_TL SUBB, FWK_TBX_LOOKUP_CODES_TL SUBT
242 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
243 and SUBB.LOOKUP_CODE = SUBT.LOOKUP_CODE
244 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
245 and (SUBB.MEANING <> SUBT.MEANING
246 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
247 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
248 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
249 ));
250
251 insert into FWK_TBX_LOOKUP_CODES_TL (
252 LAST_UPDATE_DATE,
253 LAST_UPDATED_BY,
254 CREATION_DATE,
255 CREATED_BY,
256 LAST_UPDATE_LOGIN,
257 LOOKUP_TYPE,
258 LOOKUP_CODE,
259 MEANING,
260 DESCRIPTION,
261 LANGUAGE,
262 SOURCE_LANG
263 ) select /*+ ORDERED */
264 B.LAST_UPDATE_DATE,
265 B.LAST_UPDATED_BY,
266 B.CREATION_DATE,
267 B.CREATED_BY,
268 B.LAST_UPDATE_LOGIN,
269 B.LOOKUP_TYPE,
270 B.LOOKUP_CODE,
271 B.MEANING,
272 B.DESCRIPTION,
273 L.LANGUAGE_CODE,
274 B.SOURCE_LANG
275 from FWK_TBX_LOOKUP_CODES_TL B, FND_LANGUAGES L
276 where L.INSTALLED_FLAG in ('I', 'B')
277 and B.LANGUAGE = userenv('LANG')
278 and not exists
279 (select NULL
280 from FWK_TBX_LOOKUP_CODES_TL T
281 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
282 and T.LOOKUP_CODE = B.LOOKUP_CODE
283 and T.LANGUAGE = L.LANGUAGE_CODE);
284 end ADD_LANGUAGE;
285
286 procedure TRANSLATE_ROW (
287 X_LOOKUP_TYPE in VARCHAR2,
288 X_LOOKUP_CODE in VARCHAR2,
289 X_OWNER in VARCHAR2,
290 X_MEANING in VARCHAR2,
291 X_DESCRIPTION in VARCHAR2,
292 X_LAST_UPDATE_DATE in VARCHAR2,
293 X_CUSTOM_MODE in VARCHAR2
294 ) is
295 f_luby number; -- entity owner in file
296 f_ludate date; -- entity update date in file
297 db_luby number; -- entity owner in db
298 db_ludate date; -- entity update date in db
299 begin
300
301 f_luby := fnd_load_util.owner_id(x_owner);
302
303 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
304
305 select LAST_UPDATED_BY, LAST_UPDATE_DATE
306 into db_luby, db_ludate
307 from FWK_TBX_LOOKUP_CODES_TL
308 where LOOKUP_TYPE = X_LOOKUP_TYPE
309 and LOOKUP_CODE = X_LOOKUP_CODE
310 and LANGUAGE = userenv('LANG');
311
312 -- We want the values from file to be populated, if db has null value
313 db_ludate := nvl(db_ludate, to_date('1990/01/01', 'YYYY/MM/DD'));
314 -- Default last updated by to SEED, if db has null value
315 db_luby := nvl(db_luby, fnd_load_util.owner_id('SEED'));
316
317 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
318 db_ludate, X_CUSTOM_MODE)) then
319 update FWK_TBX_LOOKUP_CODES_TL set
320 MEANING = nvl(X_MEANING, meaning),
321 DESCRIPTION = nvl(X_DESCRIPTION, description),
322 LAST_UPDATE_DATE = f_ludate,
323 LAST_UPDATED_BY = f_luby,
324 LAST_UPDATE_LOGIN = 0,
325 SOURCE_LANG = userenv('LANG')
326 where LOOKUP_TYPE = X_LOOKUP_TYPE
327 and LOOKUP_CODE = X_LOOKUP_CODE
328 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
329
330 end if;
331
332 end TRANSLATE_ROW;
333
334
335 procedure LOAD_ROW (
336 X_ROWID in out nocopy VARCHAR2,
337 X_LOOKUP_TYPE in VARCHAR2,
338 X_LOOKUP_CODE in VARCHAR2,
339 X_START_DATE_ACTIVE in DATE,
340 X_END_DATE_ACTIVE in DATE,
341 X_MEANING in VARCHAR2,
342 X_DESCRIPTION in VARCHAR2,
343 X_CREATION_DATE in DATE,
344 X_CREATED_BY in NUMBER,
345 X_LAST_UPDATE_DATE in DATE,
346 X_LAST_UPDATED_BY in NUMBER,
347 X_LAST_UPDATE_LOGIN in NUMBER,
348 X_OWNER in VARCHAR2,
349 X_CUSTOM_MODE in VARCHAR2
350 ) is
351 f_luby number; -- entity owner in file
352 f_ludate date; -- entity update date in file
353 db_luby number; -- entity owner in db
354 db_ludate date; -- entity update date in db
355 begin
356 f_luby := fnd_load_util.owner_id(X_OWNER);
357 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
358
359 select LAST_UPDATED_BY, LAST_UPDATE_DATE
360 into db_luby, db_ludate
361 from FWK_TBX_LOOKUP_CODES_TL
362 where LOOKUP_TYPE = X_LOOKUP_TYPE
363 and LOOKUP_CODE = X_LOOKUP_CODE
364 and LANGUAGE = userenv('LANG');
365
366 -- We want the values from file to be populated, if db has null value
367 db_ludate := nvl(db_ludate, to_date('1990/01/01', 'YYYY/MM/DD'));
368 -- Default last updated by to SEED, if db has null value
369 db_luby := nvl(db_luby, fnd_load_util.owner_id('SEED'));
370
371 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
372 db_ludate, X_CUSTOM_MODE)) then
373
374 FWK_TBX_LOOKUP_CODES_PKG.UPDATE_ROW(
375 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
376 X_LOOKUP_CODE => X_LOOKUP_CODE,
377 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
378 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
379 X_MEANING => X_MEANING,
380 X_DESCRIPTION => X_DESCRIPTION,
381 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
382 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
383 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
384 end if;
385 exception
386 when no_data_found then
387 -- Record doesn't exist - insert in all cases
388 FWK_TBX_LOOKUP_CODES_PKG.INSERT_ROW(
389 X_ROWID => X_ROWID,
390 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
391 X_LOOKUP_CODE => X_LOOKUP_CODE,
392 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
393 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
394 X_MEANING => X_MEANING,
395 X_DESCRIPTION => X_DESCRIPTION,
396 X_CREATION_DATE => X_CREATION_DATE,
397 X_CREATED_BY => X_CREATED_BY,
398 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
399 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
400 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN );
401
402 end LOAD_ROW;
403
404 end FWK_TBX_LOOKUP_CODES_PKG;
[Home] [Help]