[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_TEMPLATES_PKG
Source
1 package body JTF_UM_TEMPLATES_PKG as
2 /* $Header: JTFUMTLB.pls 120.5 2006/03/13 09:11:05 vimohan ship $ */
3 procedure INSERT_ROW (
4 X_TEMPLATE_ID out NOCOPY NUMBER,
5 X_TEMPLATE_KEY in VARCHAR2,
6 X_TEMPLATE_TYPE_CODE in VARCHAR2,
7 X_PAGE_NAME in VARCHAR2,
8 X_TEMPLATE_HANDLER in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_EFFECTIVE_START_DATE in DATE,
11 X_APPLICATION_ID in NUMBER,
12 X_EFFECTIVE_END_DATE in DATE,
13 X_TEMPLATE_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from JTF_UM_TEMPLATES_B
22 where TEMPLATE_ID = X_TEMPLATE_ID
23 ;
24 begin
25 insert into JTF_UM_TEMPLATES_B (
26 TEMPLATE_ID,
27 TEMPLATE_KEY,
28 TEMPLATE_TYPE_CODE,
29 PAGE_NAME,
30 TEMPLATE_HANDLER,
31 ENABLED_FLAG,
32 EFFECTIVE_START_DATE,
33 APPLICATION_ID,
34 EFFECTIVE_END_DATE,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 JTF_UM_TEMPLATES_B_S.NEXTVAL,
42 X_TEMPLATE_KEY,
43 X_TEMPLATE_TYPE_CODE,
44 X_PAGE_NAME,
45 X_TEMPLATE_HANDLER,
46 X_ENABLED_FLAG,
47 X_EFFECTIVE_START_DATE,
48 X_APPLICATION_ID,
49 X_EFFECTIVE_END_DATE,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_LOGIN
55 )RETURNING TEMPLATE_ID INTO X_TEMPLATE_ID;
56
57 insert into JTF_UM_TEMPLATES_TL (
58 TEMPLATE_ID,
59 TEMPLATE_NAME,
60 CREATED_BY,
61 CREATION_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATE_LOGIN,
65 DESCRIPTION,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_TEMPLATE_ID,
70 X_TEMPLATE_NAME,
71 X_CREATED_BY,
72 X_CREATION_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATE_LOGIN,
76 X_DESCRIPTION,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from JTF_UM_TEMPLATES_TL T
84 where T.TEMPLATE_ID = X_TEMPLATE_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 X_TEMPLATE_ID in NUMBER,
98 X_TEMPLATE_KEY in VARCHAR2,
99 X_TEMPLATE_TYPE_CODE in VARCHAR2,
100 X_PAGE_NAME in VARCHAR2,
101 X_TEMPLATE_HANDLER in VARCHAR2,
102 X_ENABLED_FLAG in VARCHAR2,
103 X_EFFECTIVE_START_DATE in DATE,
104 X_APPLICATION_ID in NUMBER,
105 X_EFFECTIVE_END_DATE in DATE,
106 X_TEMPLATE_NAME in VARCHAR2,
107 X_DESCRIPTION in VARCHAR2
108 ) is
109 cursor c is select
110 TEMPLATE_KEY,
111 TEMPLATE_TYPE_CODE,
112 PAGE_NAME,
113 TEMPLATE_HANDLER,
114 ENABLED_FLAG,
115 EFFECTIVE_START_DATE,
116 APPLICATION_ID,
117 EFFECTIVE_END_DATE
118 from JTF_UM_TEMPLATES_B
119 where TEMPLATE_ID = X_TEMPLATE_ID
120 for update of TEMPLATE_ID nowait;
121 recinfo c%rowtype;
122
123 cursor c1 is select
124 TEMPLATE_NAME,
125 DESCRIPTION,
126 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
127 from JTF_UM_TEMPLATES_TL
128 where TEMPLATE_ID = X_TEMPLATE_ID
129 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
130 for update of TEMPLATE_ID nowait;
131 begin
132 open c;
133 fetch c into recinfo;
134 if (c%notfound) then
135 close c;
136 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
137 app_exception.raise_exception;
138 end if;
139 close c;
140 if ( (recinfo.TEMPLATE_KEY = X_TEMPLATE_KEY)
141 AND (recinfo.TEMPLATE_TYPE_CODE = X_TEMPLATE_TYPE_CODE)
142 AND (recinfo.PAGE_NAME = X_PAGE_NAME)
143 AND (recinfo.TEMPLATE_HANDLER = X_TEMPLATE_HANDLER)
144 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
145 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
146 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
147 AND ((recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
148 OR ((recinfo.EFFECTIVE_END_DATE is null) AND (X_EFFECTIVE_END_DATE is null)))
149 ) then
150 null;
151 else
152 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
153 app_exception.raise_exception;
154 end if;
155
156 for tlinfo in c1 loop
157 if (tlinfo.BASELANG = 'Y') then
158 if ( (tlinfo.TEMPLATE_NAME = X_TEMPLATE_NAME)
159 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
160 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
161 ) then
162 null;
163 else
164 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
165 app_exception.raise_exception;
166 end if;
167 end if;
168 end loop;
169 return;
170 end LOCK_ROW;
171
172 procedure UPDATE_ROW (
173 X_TEMPLATE_ID in NUMBER,
174 X_TEMPLATE_KEY in VARCHAR2,
175 X_TEMPLATE_TYPE_CODE in VARCHAR2,
176 X_PAGE_NAME in VARCHAR2,
177 X_TEMPLATE_HANDLER in VARCHAR2,
178 X_ENABLED_FLAG in VARCHAR2,
179 X_APPLICATION_ID in NUMBER,
180 X_EFFECTIVE_END_DATE in DATE,
181 X_TEMPLATE_NAME in VARCHAR2,
182 X_DESCRIPTION in VARCHAR2,
183 X_LAST_UPDATE_DATE in DATE,
184 X_LAST_UPDATED_BY in NUMBER,
185 X_LAST_UPDATE_LOGIN in NUMBER
186 ) is
187 begin
188 update JTF_UM_TEMPLATES_B set
189 TEMPLATE_KEY = X_TEMPLATE_KEY,
190 TEMPLATE_TYPE_CODE = X_TEMPLATE_TYPE_CODE,
191 PAGE_NAME = X_PAGE_NAME,
192 TEMPLATE_HANDLER = X_TEMPLATE_HANDLER,
193 ENABLED_FLAG = X_ENABLED_FLAG,
194 APPLICATION_ID = X_APPLICATION_ID,
195 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
196 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
197 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
198 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
199 where TEMPLATE_ID = X_TEMPLATE_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204
205 update JTF_UM_TEMPLATES_TL set
206 TEMPLATE_NAME = X_TEMPLATE_NAME,
207 DESCRIPTION = X_DESCRIPTION,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
211 SOURCE_LANG = userenv('LANG')
212 where TEMPLATE_ID = X_TEMPLATE_ID
213 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end UPDATE_ROW;
219
220 procedure DELETE_ROW (
221 X_TEMPLATE_ID in NUMBER
222 ) is
223 begin
224 delete from JTF_UM_TEMPLATES_TL
225 where TEMPLATE_ID = X_TEMPLATE_ID;
226
227 if (sql%notfound) then
228 raise no_data_found;
229 end if;
230
231 delete from JTF_UM_TEMPLATES_B
232 where TEMPLATE_ID = X_TEMPLATE_ID;
233
234 if (sql%notfound) then
235 raise no_data_found;
236 end if;
237 end DELETE_ROW;
238
239 procedure ADD_LANGUAGE
240 is
241 begin
242 delete from JTF_UM_TEMPLATES_TL T
243 where not exists
244 (select NULL
245 from JTF_UM_TEMPLATES_B B
246 where B.TEMPLATE_ID = T.TEMPLATE_ID
247 );
248
249 update JTF_UM_TEMPLATES_TL T set (
250 TEMPLATE_NAME,
251 DESCRIPTION
252 ) = (select
253 B.TEMPLATE_NAME,
254 B.DESCRIPTION
255 from JTF_UM_TEMPLATES_TL B
256 where B.TEMPLATE_ID = T.TEMPLATE_ID
257 and B.LANGUAGE = T.SOURCE_LANG)
258 where (
259 T.TEMPLATE_ID,
260 T.LANGUAGE
261 ) in (select
262 SUBT.TEMPLATE_ID,
263 SUBT.LANGUAGE
264 from JTF_UM_TEMPLATES_TL SUBB, JTF_UM_TEMPLATES_TL SUBT
265 where SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
266 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
267 and (SUBB.TEMPLATE_NAME <> SUBT.TEMPLATE_NAME
268 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
269 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
270 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
271 ));
272
273 insert into JTF_UM_TEMPLATES_TL (
274 TEMPLATE_ID,
275 TEMPLATE_NAME,
276 CREATED_BY,
277 CREATION_DATE,
278 LAST_UPDATED_BY,
279 LAST_UPDATE_DATE,
280 LAST_UPDATE_LOGIN,
281 DESCRIPTION,
282 LANGUAGE,
283 SOURCE_LANG
284 ) select /*+ ORDERED */
285 B.TEMPLATE_ID,
286 B.TEMPLATE_NAME,
287 B.CREATED_BY,
288 B.CREATION_DATE,
289 B.LAST_UPDATED_BY,
290 B.LAST_UPDATE_DATE,
291 B.LAST_UPDATE_LOGIN,
292 B.DESCRIPTION,
293 L.LANGUAGE_CODE,
294 B.SOURCE_LANG
295 from JTF_UM_TEMPLATES_TL B, FND_LANGUAGES L
296 where L.INSTALLED_FLAG in ('I', 'B')
297 and B.LANGUAGE = userenv('LANG')
298 and not exists
299 (select NULL
300 from JTF_UM_TEMPLATES_TL T
301 where T.TEMPLATE_ID = B.TEMPLATE_ID
302 and T.LANGUAGE = L.LANGUAGE_CODE);
303 end ADD_LANGUAGE;
304
305
306
307 --For this procedure, if TEMPLATE_ID passed as input is NULL, then create a new record
308 -- otherwise, modify the existing record.
309
310 procedure LOAD_ROW (
311 X_TEMPLATE_ID IN NUMBER,
312 X_EFFECTIVE_START_DATE IN DATE,
313 X_EFFECTIVE_END_DATE IN DATE,
314 X_OWNER IN VARCHAR2,
315 X_APPLICATION_ID IN NUMBER,
316 X_ENABLED_FLAG IN VARCHAR2,
317 X_TEMPLATE_TYPE_CODE IN VARCHAR2,
318 X_PAGE_NAME IN VARCHAR2,
319 X_TEMPLATE_HANDLER IN VARCHAR2,
320 X_TEMPLATE_KEY IN VARCHAR2,
321 X_TEMPLATE_NAME IN VARCHAR2,
322 X_DESCRIPTION IN VARCHAR2,
323 x_last_update_date in varchar2 default NULL,
324 X_CUSTOM_MODE in varchar2 default NULL
325
326 ) is
327 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
328 l_template_id NUMBER := 0;
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 -- if (x_owner = '0') then
335 -- l_user_id := 1;
336 -- end if;
337
338 -- Translate owner to file_last_updated_by
339 f_luby := fnd_load_util.owner_id(x_owner);
340
341 -- Translate char last_update_date to date
342 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
343
344 -- If TEMPLATE_ID passed in NULL, insert the record
345 if ( X_TEMPLATE_ID is NULL ) THEN
346 INSERT_ROW(
347 X_TEMPLATE_ID => l_template_id,
348 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
349 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
350 X_APPLICATION_ID => X_APPLICATION_ID,
351 X_ENABLED_FLAG => X_ENABLED_FLAG,
352 X_TEMPLATE_TYPE_CODE => X_TEMPLATE_TYPE_CODE,
353 X_PAGE_NAME => X_PAGE_NAME,
354 X_TEMPLATE_HANDLER => X_TEMPLATE_HANDLER,
355 X_TEMPLATE_KEY => X_TEMPLATE_KEY,
356 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
357 X_DESCRIPTION => X_DESCRIPTION,
358 X_CREATION_DATE => f_ludate,
359 X_CREATED_BY => f_luby,
360 X_LAST_UPDATE_DATE => f_ludate,
361 X_LAST_UPDATED_BY => f_luby,
362 X_LAST_UPDATE_LOGIN => l_user_id
363 );
364 else
365 -- This select stmnt also checks if
366 -- there is a row for this app_id and this app_short_name
367 -- Exception is thrown otherwise.
368 select LAST_UPDATED_BY, LAST_UPDATE_DATE
369 into db_luby, db_ludate
370 FROM JTF_UM_TEMPLATES_B
371 where TEMPLATE_ID = X_TEMPLATE_ID;
372
373 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
374 db_ludate, X_CUSTOM_MODE)) then
375
376 UPDATE_ROW(
377 X_TEMPLATE_ID => X_TEMPLATE_ID,
378 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
379 X_APPLICATION_ID => X_APPLICATION_ID,
380 X_ENABLED_FLAG => X_ENABLED_FLAG,
381 X_TEMPLATE_TYPE_CODE => X_TEMPLATE_TYPE_CODE,
382 X_PAGE_NAME => X_PAGE_NAME,
383 X_TEMPLATE_HANDLER => X_TEMPLATE_HANDLER,
384 X_TEMPLATE_KEY => X_TEMPLATE_KEY,
385 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
386 X_DESCRIPTION => X_DESCRIPTION,
387 X_LAST_UPDATE_DATE => f_ludate,
388 X_LAST_UPDATED_BY => f_luby,
389 X_LAST_UPDATE_LOGIN => l_user_id
390 );
391
392 end if;
393
394 end if;
395
396 end LOAD_ROW;
397
398 procedure TRANSLATE_ROW (
399 X_TEMPLATE_ID in NUMBER, -- key field
400 X_TEMPLATE_NAME in VARCHAR2, -- translated name
401 X_DESCRIPTION in VARCHAR2, -- translated description
402 X_OWNER in VARCHAR2, -- owner field
403 x_last_update_date in varchar2 default NULL,
404 X_CUSTOM_MODE in varchar2 default NULL
405
406
407 ) is
408
409 f_luby number; -- entity owner in file
410 f_ludate date; -- entity update date in file
411 db_luby number; -- entity owner in db
412 db_ludate date; -- entity update date in db
413
414 begin
415
416 -- Translate owner to file_last_updated_by
417 f_luby := fnd_load_util.owner_id(x_owner);
418
419 -- Translate char last_update_date to date
420 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
421
422 -- This select stmnt also checks if
423 -- there is a row for this app_id and this app_short_name
424 -- Exception is thrown otherwise.
425 select LAST_UPDATED_BY, LAST_UPDATE_DATE
426 into db_luby, db_ludate
427 FROM JTF_UM_TEMPLATES_TL
428 where TEMPLATE_ID = X_TEMPLATE_ID
429 and LANGUAGE = userenv('LANG');
430
431 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
432 db_ludate, X_CUSTOM_MODE)) then
433
434
435 update JTF_UM_TEMPLATES_TL set
436 TEMPLATE_NAME = X_TEMPLATE_NAME,
437 DESCRIPTION = X_DESCRIPTION,
438 LAST_UPDATE_DATE = f_ludate,
439 LAST_UPDATED_BY = f_luby,
440 LAST_UPDATE_LOGIN = 0,
441 SOURCE_LANG = userenv('LANG')
442 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
443 and TEMPLATE_ID = X_TEMPLATE_ID;
444
445
446 end if;
447
448
449 end TRANSLATE_ROW;
450
451 end JTF_UM_TEMPLATES_PKG;