[Home] [Help]
PACKAGE BODY: APPS.FRM_GLB_TEMPLATE_ALIASES_PKG
Source
1 package body FRM_GLB_TEMPLATE_ALIASES_PKG as
2 /* $Header: frmglbaliasb.pls 120.2 2005/09/29 00:14:01 ghooker noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ELEMENT_NAME in VARCHAR2,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_USER_NAME in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from FRM_GLB_TEMPLATE_ALIASES_B
15 where ELEMENT_NAME = X_ELEMENT_NAME
16 ;
17 begin
18 insert into FRM_GLB_TEMPLATE_ALIASES_B (
19 ELEMENT_NAME,
20 OBJECT_VERSION_NUMBER,
21 CREATION_DATE,
22 CREATED_BY,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 LAST_UPDATE_LOGIN
26 ) values (
27 X_ELEMENT_NAME,
28 X_OBJECT_VERSION_NUMBER,
29 X_CREATION_DATE,
30 X_CREATED_BY,
31 X_LAST_UPDATE_DATE,
32 X_LAST_UPDATED_BY,
33 X_LAST_UPDATE_LOGIN
34 );
35
36 insert into FRM_GLB_TEMPLATE_ALIASES_TL (
37 ELEMENT_NAME,
38 USER_NAME,
39 CREATION_DATE,
40 CREATED_BY,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN,
43 LAST_UPDATE_DATE,
44 LANGUAGE,
45 SOURCE_LANG
46 ) select
47 X_ELEMENT_NAME,
48 X_USER_NAME,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN,
53 X_LAST_UPDATE_DATE,
54 L.LANGUAGE_CODE,
55 userenv('LANG')
56 from FND_LANGUAGES L
57 where L.INSTALLED_FLAG in ('I', 'B')
58 and not exists
59 (select NULL
60 from FRM_GLB_TEMPLATE_ALIASES_TL T
61 where T.ELEMENT_NAME = X_ELEMENT_NAME
62 and T.LANGUAGE = L.LANGUAGE_CODE);
63
64 open c;
65 fetch c into X_ROWID;
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70 close c;
71
72 end INSERT_ROW;
73
74 procedure LOCK_ROW (
75 X_ELEMENT_NAME in VARCHAR2,
76 X_OBJECT_VERSION_NUMBER in NUMBER,
77 X_USER_NAME in VARCHAR2
78 ) is
79 cursor c is select
80 OBJECT_VERSION_NUMBER
81 from FRM_GLB_TEMPLATE_ALIASES_B
82 where ELEMENT_NAME = X_ELEMENT_NAME
83 for update of ELEMENT_NAME nowait;
84 recinfo c%rowtype;
85
86 cursor c1 is select
87 USER_NAME,
88 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
89 from FRM_GLB_TEMPLATE_ALIASES_TL
90 where ELEMENT_NAME = X_ELEMENT_NAME
91 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
92 for update of ELEMENT_NAME nowait;
93 begin
94 open c;
95 fetch c into recinfo;
96 if (c%notfound) then
97 close c;
98 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
99 app_exception.raise_exception;
100 end if;
101 close c;
102 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
103 ) then
104 null;
105 else
106 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
107 app_exception.raise_exception;
108 end if;
109
110 for tlinfo in c1 loop
111 if (tlinfo.BASELANG = 'Y') then
112 if ( (tlinfo.USER_NAME = X_USER_NAME)
113 ) then
114 null;
115 else
116 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
117 app_exception.raise_exception;
118 end if;
119 end if;
120 end loop;
121 return;
122 end LOCK_ROW;
123
124 procedure UPDATE_ROW (
125 X_ELEMENT_NAME in VARCHAR2,
126 X_OBJECT_VERSION_NUMBER in NUMBER,
127 X_USER_NAME in VARCHAR2,
128 X_LAST_UPDATE_DATE in DATE,
129 X_LAST_UPDATED_BY in NUMBER,
130 X_LAST_UPDATE_LOGIN in NUMBER
131 ) is
132 begin
133 update FRM_GLB_TEMPLATE_ALIASES_B set
134 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
135 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
138 where ELEMENT_NAME = X_ELEMENT_NAME;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143
144 update FRM_GLB_TEMPLATE_ALIASES_TL set
145 USER_NAME = X_USER_NAME,
146 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
147 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
148 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
149 SOURCE_LANG = userenv('LANG')
150 where ELEMENT_NAME = X_ELEMENT_NAME
151 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
152
153 if (sql%notfound) then
154 raise no_data_found;
155 end if;
156 end UPDATE_ROW;
157
158 procedure DELETE_ROW (
159 X_ELEMENT_NAME in VARCHAR2
160 ) is
161 begin
162 delete from FRM_GLB_TEMPLATE_ALIASES_TL
163 where ELEMENT_NAME = X_ELEMENT_NAME;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 delete from FRM_GLB_TEMPLATE_ALIASES_B
170 where ELEMENT_NAME = X_ELEMENT_NAME;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175 end DELETE_ROW;
176
177 procedure ADD_LANGUAGE
178 is
179 begin
180 delete from FRM_GLB_TEMPLATE_ALIASES_TL T
181 where not exists
182 (select NULL
183 from FRM_GLB_TEMPLATE_ALIASES_B B
184 where B.ELEMENT_NAME = T.ELEMENT_NAME
185 );
186
187 update FRM_GLB_TEMPLATE_ALIASES_TL T set (
188 USER_NAME
189 ) = (select
190 B.USER_NAME
191 from FRM_GLB_TEMPLATE_ALIASES_TL B
192 where B.ELEMENT_NAME = T.ELEMENT_NAME
193 and B.LANGUAGE = T.SOURCE_LANG)
194 where (
195 T.ELEMENT_NAME,
196 T.LANGUAGE
197 ) in (select
198 SUBT.ELEMENT_NAME,
199 SUBT.LANGUAGE
200 from FRM_GLB_TEMPLATE_ALIASES_TL SUBB, FRM_GLB_TEMPLATE_ALIASES_TL SUBT
201 where SUBB.ELEMENT_NAME = SUBT.ELEMENT_NAME
202 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
203 and (SUBB.USER_NAME <> SUBT.USER_NAME
204 ));
205
206 insert into FRM_GLB_TEMPLATE_ALIASES_TL (
207 ELEMENT_NAME,
208 USER_NAME,
209 CREATION_DATE,
210 CREATED_BY,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 LAST_UPDATE_DATE,
214 LANGUAGE,
215 SOURCE_LANG
216 ) select /*+ ORDERED */
217 B.ELEMENT_NAME,
218 B.USER_NAME,
219 B.CREATION_DATE,
220 B.CREATED_BY,
221 B.LAST_UPDATED_BY,
222 B.LAST_UPDATE_LOGIN,
223 B.LAST_UPDATE_DATE,
224 L.LANGUAGE_CODE,
225 B.SOURCE_LANG
226 from FRM_GLB_TEMPLATE_ALIASES_TL B, FND_LANGUAGES L
227 where L.INSTALLED_FLAG in ('I', 'B')
228 and B.LANGUAGE = userenv('LANG')
229 and not exists
230 (select NULL
231 from FRM_GLB_TEMPLATE_ALIASES_TL T
232 where T.ELEMENT_NAME = B.ELEMENT_NAME
233 and T.LANGUAGE = L.LANGUAGE_CODE);
234 end ADD_LANGUAGE;
235
236 --------------------------------------------------------------------------------
237 -- PROCEDURE: LOAD_ROW --
238 -- --
239 -- DESCRIPTION: Load a row into the FRM_GLB_TEMPLATE_ALIASES entity. --
240 -- This proc is called from the apps loader. --
241 -- --
242 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
243 -- --
244 -- --
245 -- MODIFICATION HISTORY --
246 -- Date Username Description --
247 -- 12-Jul-04 MLUETCHF CREATED --
248 --------------------------------------------------------------------------------
249
250 procedure LOAD_ROW(
251 x_element_name IN VARCHAR2,
252 x_object_version_number IN VARCHAR2,
253 x_user_name IN VARCHAR2,
254 x_owner IN VARCHAR2,
255 x_last_update_date IN VARCHAR2,
256 x_custom_mode IN VARCHAR2
257 )
258 is
259 l_row_id varchar2(64);
260 f_luby number; -- entity owner in file
261 f_ludate date; -- entity update date in file
262 db_luby number; -- entity owner in db
263 db_ludate date; -- entity update date in db
264 begin
265 -- Translate owner to file_last_updated_by
266 f_luby := fnd_load_util.owner_id(x_owner);
267
268 -- Translate char last_update_date to date
269 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
270 begin
271 select LAST_UPDATED_BY, LAST_UPDATE_DATE
272 into db_luby, db_ludate
273 from FRM_GLB_TEMPLATE_ALIASES_B
274 where ELEMENT_NAME = x_element_name;
275
276 -- Test for customization and version
277 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
278 db_ludate, x_custom_mode)) then
279 -- Update existing row
280 FRM_GLB_TEMPLATE_ALIASES_PKG.Update_Row(
281 X_ELEMENT_NAME => x_element_name,
282 X_OBJECT_VERSION_NUMBER => x_object_version_number,
283 X_USER_NAME => x_user_name,
284 X_LAST_UPDATE_DATE => f_ludate,
285 X_LAST_UPDATED_BY => f_luby,
286 X_LAST_UPDATE_LOGIN => 0
287 );
288 end if;
289 exception
290 when no_data_found then
291 -- Record doesn't exist - insert in all cases
292 FRM_GLB_TEMPLATE_ALIASES_PKG.Insert_Row(
293 X_ROWID => l_row_id,
294 X_ELEMENT_NAME => x_element_name,
295 X_OBJECT_VERSION_NUMBER => x_object_version_number,
296 X_USER_NAME => x_user_name,
297 X_CREATION_DATE => f_ludate,
298 X_CREATED_BY => f_luby,
299 X_LAST_UPDATE_DATE => f_ludate,
300 X_LAST_UPDATED_BY => f_luby,
301 X_LAST_UPDATE_LOGIN => 0
302 );
303 end;
304 end LOAD_ROW;
305
306
307 --------------------------------------------------------------------------------
308 -- PROCEDURE: TRANSLATE_ROW --
309 -- --
310 -- DESCRIPTION: Load a translation into the FRM_GLB_TEMPLATE_ALIASES entity. --
311 -- This proc is called from the apps loader. --
312 -- --
313 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
314 -- --
315 -- --
316 -- MODIFICATION HISTORY --
317 -- Date Username Description --
318 -- 12-Jul-04 MLUETCHF CREATED --
319 --------------------------------------------------------------------------------
320
321 procedure TRANSLATE_ROW(
322 x_element_name IN VARCHAR2,
323 x_user_name IN VARCHAR2,
324 x_owner IN VARCHAR2,
325 x_last_update_date IN VARCHAR2,
326 x_custom_mode IN VARCHAR2
327 )
328 is
329 f_luby number; -- entity owner in file
330 f_ludate date; -- entity update date in file
331 db_luby number; -- entity owner in db
332 db_ludate date; -- entity update date in db
333 begin
334 -- Translate owner to file_last_updated_by
335 f_luby := fnd_load_util.owner_id(x_owner);
336
337 -- Translate char last_update_date to date
338 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
339 begin
340 select LAST_UPDATED_BY, LAST_UPDATE_DATE
341 into db_luby, db_ludate
342 from FRM_GLB_TEMPLATE_ALIASES_TL
343 where ELEMENT_NAME = x_element_name
344 and LANGUAGE = userenv('LANG');
345
346 -- Test for customization and version
347 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
348 db_ludate, x_custom_mode)) then
349
350 update FRM_GLB_TEMPLATE_ALIASES_TL
351 set USER_NAME = x_user_name,
352 LAST_UPDATE_DATE = f_ludate,
353 LAST_UPDATED_BY = f_luby,
354 LAST_UPDATE_LOGIN = 0,
355 SOURCE_LANG = userenv('LANG')
356 where ELEMENT_NAME = x_element_name
357 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
358 ;
359 end if;
360 exception
361 when no_data_found then
362 -- Do not insert missing translations, skip this row
363 null;
364 end;
365 end TRANSLATE_ROW;
366
367 end FRM_GLB_TEMPLATE_ALIASES_PKG;