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