[Home] [Help]
PACKAGE BODY: APPS.FEM_USER_LOV168_PKG
Source
1 package body FEM_USER_LOV168_PKG as
2 /* $Header: fem_usrlv168_pkb.plb 120.0 2005/06/06 19:08:19 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_USER_LOV168_CODE in VARCHAR2,
6 X_ENABLED_FLAG in VARCHAR2,
7 X_PERSONAL_FLAG in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_READ_ONLY_FLAG in VARCHAR2,
10 X_USER_LOV168_NAME in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from FEM_USER_LOV168_B
19 where USER_LOV168_CODE = X_USER_LOV168_CODE
20 ;
21 begin
22 insert into FEM_USER_LOV168_B (
23 USER_LOV168_CODE,
24 ENABLED_FLAG,
25 PERSONAL_FLAG,
26 OBJECT_VERSION_NUMBER,
27 READ_ONLY_FLAG,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_USER_LOV168_CODE,
35 X_ENABLED_FLAG,
36 X_PERSONAL_FLAG,
37 X_OBJECT_VERSION_NUMBER,
38 X_READ_ONLY_FLAG,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY,
43 X_LAST_UPDATE_LOGIN
44 );
45
46 insert into FEM_USER_LOV168_TL (
47 USER_LOV168_CODE,
48 USER_LOV168_NAME,
49 DESCRIPTION,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_USER_LOV168_CODE,
59 X_USER_LOV168_NAME,
60 X_DESCRIPTION,
61 X_CREATION_DATE,
62 X_CREATED_BY,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATE_LOGIN,
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 FEM_USER_LOV168_TL T
73 where T.USER_LOV168_CODE = X_USER_LOV168_CODE
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_USER_LOV168_CODE in VARCHAR2,
88 X_ENABLED_FLAG in VARCHAR2,
89 X_PERSONAL_FLAG in VARCHAR2,
90 X_OBJECT_VERSION_NUMBER in NUMBER,
91 X_READ_ONLY_FLAG in VARCHAR2,
92 X_USER_LOV168_NAME in VARCHAR2,
93 X_DESCRIPTION in VARCHAR2
94 ) is
95 cursor c is select
96 ENABLED_FLAG,
97 PERSONAL_FLAG,
98 OBJECT_VERSION_NUMBER,
99 READ_ONLY_FLAG
100 from FEM_USER_LOV168_B
101 where USER_LOV168_CODE = X_USER_LOV168_CODE
102 for update of USER_LOV168_CODE nowait;
103 recinfo c%rowtype;
104
105 cursor c1 is select
106 USER_LOV168_NAME,
107 DESCRIPTION,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from FEM_USER_LOV168_TL
110 where USER_LOV168_CODE = X_USER_LOV168_CODE
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of USER_LOV168_CODE 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.ENABLED_FLAG = X_ENABLED_FLAG)
123 AND (recinfo.PERSONAL_FLAG = X_PERSONAL_FLAG)
124 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
125 AND (recinfo.READ_ONLY_FLAG = X_READ_ONLY_FLAG)
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.USER_LOV168_NAME = X_USER_LOV168_NAME)
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_USER_LOV168_CODE in VARCHAR2,
151 X_ENABLED_FLAG in VARCHAR2,
152 X_PERSONAL_FLAG in VARCHAR2,
153 X_OBJECT_VERSION_NUMBER in NUMBER,
154 X_READ_ONLY_FLAG in VARCHAR2,
155 X_USER_LOV168_NAME in VARCHAR2,
156 X_DESCRIPTION in VARCHAR2,
157 X_LAST_UPDATE_DATE in DATE,
158 X_LAST_UPDATED_BY in NUMBER,
159 X_LAST_UPDATE_LOGIN in NUMBER
160 ) is
161 begin
162 update FEM_USER_LOV168_B set
163 ENABLED_FLAG = X_ENABLED_FLAG,
164 PERSONAL_FLAG = X_PERSONAL_FLAG,
165 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
166 READ_ONLY_FLAG = X_READ_ONLY_FLAG,
167 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
168 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
170 where USER_LOV168_CODE = X_USER_LOV168_CODE;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 update FEM_USER_LOV168_TL set
177 USER_LOV168_NAME = X_USER_LOV168_NAME,
178 DESCRIPTION = X_DESCRIPTION,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
182 SOURCE_LANG = userenv('LANG')
183 where USER_LOV168_CODE = X_USER_LOV168_CODE
184 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end UPDATE_ROW;
190
191 procedure DELETE_ROW (
192 X_USER_LOV168_CODE in VARCHAR2
193 ) is
194 begin
195 delete from FEM_USER_LOV168_TL
196 where USER_LOV168_CODE = X_USER_LOV168_CODE;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201
202 delete from FEM_USER_LOV168_B
203 where USER_LOV168_CODE = X_USER_LOV168_CODE;
204
205 if (sql%notfound) then
206 raise no_data_found;
207 end if;
208 end DELETE_ROW;
209
210 procedure ADD_LANGUAGE
211 is
212 begin
213 delete from FEM_USER_LOV168_TL T
214 where not exists
215 (select NULL
216 from FEM_USER_LOV168_B B
217 where B.USER_LOV168_CODE = T.USER_LOV168_CODE
218 );
219
220 update FEM_USER_LOV168_TL T set (
221 USER_LOV168_NAME,
222 DESCRIPTION
223 ) = (select
224 B.USER_LOV168_NAME,
225 B.DESCRIPTION
226 from FEM_USER_LOV168_TL B
227 where B.USER_LOV168_CODE = T.USER_LOV168_CODE
228 and B.LANGUAGE = T.SOURCE_LANG)
229 where (
230 T.USER_LOV168_CODE,
231 T.LANGUAGE
232 ) in (select
233 SUBT.USER_LOV168_CODE,
234 SUBT.LANGUAGE
235 from FEM_USER_LOV168_TL SUBB, FEM_USER_LOV168_TL SUBT
236 where SUBB.USER_LOV168_CODE = SUBT.USER_LOV168_CODE
237 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
238 and (SUBB.USER_LOV168_NAME <> SUBT.USER_LOV168_NAME
239 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
240 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
241 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
242 ));
243
244 insert into FEM_USER_LOV168_TL (
245 USER_LOV168_CODE,
246 USER_LOV168_NAME,
247 DESCRIPTION,
248 CREATION_DATE,
249 CREATED_BY,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATE_LOGIN,
253 LANGUAGE,
254 SOURCE_LANG
255 ) select /*+ ORDERED */
256 B.USER_LOV168_CODE,
257 B.USER_LOV168_NAME,
258 B.DESCRIPTION,
259 B.CREATION_DATE,
260 B.CREATED_BY,
261 B.LAST_UPDATED_BY,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATE_LOGIN,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANG
266 from FEM_USER_LOV168_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from FEM_USER_LOV168_TL T
272 where T.USER_LOV168_CODE = B.USER_LOV168_CODE
273 and T.LANGUAGE = L.LANGUAGE_CODE);
274 end ADD_LANGUAGE;
275 PROCEDURE TRANSLATE_ROW(
276 x_USER_LOV168_CODE in varchar2,
277 x_owner in varchar2,
278 x_last_update_date in varchar2,
279 x_USER_LOV168_NAME in varchar2,
280 x_description in varchar2,
281 x_custom_mode in varchar2) is
282
283 owner_id number;
284 ludate date;
285 row_id varchar2(64);
286 f_luby number; -- entity owner in file
287 f_ludate date; -- entity update date in file
288 db_luby number; -- entity owner in db
289 db_ludate date; -- entity update date in db
290 begin
291
292
293 -- Translate owner to file_last_updated_by
294 f_luby := fnd_load_util.owner_id(x_owner);
295
296 -- Translate char last_update_date to date
297 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
298 begin
299 select LAST_UPDATED_BY, LAST_UPDATE_DATE
300 into db_luby, db_ludate
301 from FEM_USER_LOV168_TL
302 where USER_LOV168_CODE = x_USER_LOV168_CODE
303 and LANGUAGE = userenv('LANG');
304
305 -- Test for customization and version
306 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
307 db_ludate, x_custom_mode)) then
308 -- Update translations for this language
309 update FEM_USER_LOV168_TL set
310 USER_LOV168_NAME = decode(x_USER_LOV168_NAME,
311 fnd_load_util.null_value, null, -- Real null
312 null, x_USER_LOV168_NAME, -- No change
313 x_USER_LOV168_NAME),
314 DESCRIPTION = nvl(x_description, DESCRIPTION),
315 LAST_UPDATE_DATE = f_ludate,
316 LAST_UPDATED_BY = f_luby,
317 LAST_UPDATE_LOGIN = 0,
318 SOURCE_LANG = userenv('LANG')
319 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
320 and USER_LOV168_CODE = x_USER_LOV168_CODE;
321 end if;
322 exception
323 when no_data_found then
324 -- Do not insert missing translations, skip this row
325 null;
326 end;
327 end TRANSLATE_ROW;
328
329
330 end FEM_USER_LOV168_PKG;