[Home] [Help]
PACKAGE BODY: APPS.WMS_PAGE_TEMPLATES_PKG
Source
1 package body WMS_PAGE_TEMPLATES_PKG as
2 /* $Header: WMSPTTHB.pls 115.2 2003/10/31 05:14:58 sthamman noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_TEMPLATE_ID in NUMBER,
7 X_ATTRIBUTE_CATEGORY in VARCHAR2,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE2 in VARCHAR2,
10 X_ATTRIBUTE3 in VARCHAR2,
11 X_ATTRIBUTE4 in VARCHAR2,
12 X_ATTRIBUTE5 in VARCHAR2,
13 X_ATTRIBUTE6 in VARCHAR2,
14 X_ATTRIBUTE7 in VARCHAR2,
15 X_ATTRIBUTE8 in VARCHAR2,
16 X_ATTRIBUTE9 in VARCHAR2,
17 X_ATTRIBUTE10 in VARCHAR2,
18 X_ATTRIBUTE11 in VARCHAR2,
19 X_ATTRIBUTE12 in VARCHAR2,
20 X_ATTRIBUTE13 in VARCHAR2,
21 X_ATTRIBUTE14 in VARCHAR2,
22 X_ATTRIBUTE15 in VARCHAR2,
23 X_PAGE_ID in NUMBER,
24 X_TEMPLATE_NAME in VARCHAR2,
25 X_CREATING_ORGANIZATION_ID in NUMBER,
26 X_CREATING_ORGANIZATION_CODE in VARCHAR2,
27 X_COMMON_TO_ALL_ORGS in VARCHAR2,
28 X_ENABLED in VARCHAR2,
29 X_DEFAULT_FLAG in VARCHAR2,
30 X_USER_TEMPLATE_NAME in VARCHAR2,
31 X_TEMPLATE_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38
39 cursor C is select ROWID from WMS_PAGE_TEMPLATES_B
40 where TEMPLATE_ID = X_TEMPLATE_ID;
41
42 begin
43
44 insert into WMS_PAGE_TEMPLATES_B (
45 ATTRIBUTE_CATEGORY,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15,
61 PAGE_ID,
62 TEMPLATE_ID,
63 TEMPLATE_NAME,
64 CREATING_ORGANIZATION_ID,
65 CREATING_ORGANIZATION_CODE,
66 COMMON_TO_ALL_ORGS,
67 ENABLED,
68 DEFAULT_FLAG,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) values (
75 X_ATTRIBUTE_CATEGORY,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15,
91 X_PAGE_ID,
92 X_TEMPLATE_ID,
93 X_TEMPLATE_NAME,
94 X_CREATING_ORGANIZATION_ID,
95 X_CREATING_ORGANIZATION_CODE,
96 X_COMMON_TO_ALL_ORGS,
97 X_ENABLED,
98 X_DEFAULT_FLAG,
99 X_CREATION_DATE,
100 X_CREATED_BY,
101 X_LAST_UPDATE_DATE,
102 X_LAST_UPDATED_BY,
103 X_LAST_UPDATE_LOGIN
104 );
105
106 insert into WMS_PAGE_TEMPLATES_TL (
107 PAGE_ID,
108 TEMPLATE_ID,
109 USER_TEMPLATE_NAME,
110 TEMPLATE_DESCRIPTION,
111 CREATION_DATE,
112 CREATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN,
116 LANGUAGE,
117 SOURCE_LANG
118 ) select
119 X_PAGE_ID,
120 X_TEMPLATE_ID,
121 X_USER_TEMPLATE_NAME,
122 X_TEMPLATE_DESCRIPTION,
123 X_CREATION_DATE,
124 X_CREATED_BY,
125 X_LAST_UPDATE_DATE,
126 X_LAST_UPDATED_BY,
127 X_LAST_UPDATE_LOGIN,
128 L.LANGUAGE_CODE,
129 userenv('LANG')
130 from FND_LANGUAGES L
131 where L.INSTALLED_FLAG in ('I', 'B')
132 and not exists
133 (select NULL
134 from WMS_PAGE_TEMPLATES_TL T
135 where T.TEMPLATE_ID = X_TEMPLATE_ID
136 and T.LANGUAGE = L.LANGUAGE_CODE);
137
138 open c;
139 fetch c into X_ROWID;
140 if (c%notfound) then
141 close c;
142 raise no_data_found;
143 end if;
144 close c;
145
146 end INSERT_ROW;
147
148 procedure LOCK_ROW (
149 X_TEMPLATE_ID in NUMBER,
150 X_ATTRIBUTE_CATEGORY in VARCHAR2,
151 X_ATTRIBUTE1 in VARCHAR2,
152 X_ATTRIBUTE2 in VARCHAR2,
153 X_ATTRIBUTE3 in VARCHAR2,
154 X_ATTRIBUTE4 in VARCHAR2,
155 X_ATTRIBUTE5 in VARCHAR2,
156 X_ATTRIBUTE6 in VARCHAR2,
157 X_ATTRIBUTE7 in VARCHAR2,
158 X_ATTRIBUTE8 in VARCHAR2,
159 X_ATTRIBUTE9 in VARCHAR2,
160 X_ATTRIBUTE10 in VARCHAR2,
161 X_ATTRIBUTE11 in VARCHAR2,
162 X_ATTRIBUTE12 in VARCHAR2,
163 X_ATTRIBUTE13 in VARCHAR2,
164 X_ATTRIBUTE14 in VARCHAR2,
165 X_ATTRIBUTE15 in VARCHAR2,
166 X_PAGE_ID in NUMBER,
167 X_TEMPLATE_NAME in VARCHAR2,
168 X_CREATING_ORGANIZATION_ID in NUMBER,
169 X_CREATING_ORGANIZATION_CODE in VARCHAR2,
170 X_COMMON_TO_ALL_ORGS in VARCHAR2,
171 X_ENABLED in VARCHAR2,
172 X_DEFAULT_FLAG in VARCHAR2,
173 X_USER_TEMPLATE_NAME in VARCHAR2,
174 X_TEMPLATE_DESCRIPTION in VARCHAR2
175 ) is
176 cursor c is select
177 ATTRIBUTE_CATEGORY,
178 ATTRIBUTE1,
179 ATTRIBUTE2,
180 ATTRIBUTE3,
181 ATTRIBUTE4,
182 ATTRIBUTE5,
183 ATTRIBUTE6,
184 ATTRIBUTE7,
185 ATTRIBUTE8,
186 ATTRIBUTE9,
187 ATTRIBUTE10,
188 ATTRIBUTE11,
189 ATTRIBUTE12,
190 ATTRIBUTE13,
191 ATTRIBUTE14,
192 ATTRIBUTE15,
193 PAGE_ID,
194 TEMPLATE_NAME,
195 CREATING_ORGANIZATION_ID,
196 CREATING_ORGANIZATION_CODE,
197 COMMON_TO_ALL_ORGS,
198 ENABLED,
199 DEFAULT_FLAG
200 from WMS_PAGE_TEMPLATES_B
201 where TEMPLATE_ID = X_TEMPLATE_ID
202 for update of TEMPLATE_ID nowait;
203 recinfo c%rowtype;
204
205 cursor c1 is select
206 USER_TEMPLATE_NAME,
207 TEMPLATE_DESCRIPTION,
208 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
209 from WMS_PAGE_TEMPLATES_TL
210 where TEMPLATE_ID = X_TEMPLATE_ID
211 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
212 for update of TEMPLATE_ID nowait;
213 begin
214 open c;
215 fetch c into recinfo;
216 if (c%notfound) then
217 close c;
218 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
219 app_exception.raise_exception;
220 end if;
221 close c;
222 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
223 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
224 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
225 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
226 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
227 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
228 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
229 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
230 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
231 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
232 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
233 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
234 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
235 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
236 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
237 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
238 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
239 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
240 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
241 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
242 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
243 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
244 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
245 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
246 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
247 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
248 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
249 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
250 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
251 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
252 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
253 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
254 AND (recinfo.PAGE_ID = X_PAGE_ID)
255 AND (recinfo.TEMPLATE_NAME = X_TEMPLATE_NAME)
256 AND (recinfo.CREATING_ORGANIZATION_ID = X_CREATING_ORGANIZATION_ID)
257 AND ((recinfo.CREATING_ORGANIZATION_CODE = X_CREATING_ORGANIZATION_CODE)
258 OR ((recinfo.CREATING_ORGANIZATION_CODE is null) AND (X_CREATING_ORGANIZATION_CODE is null)))
259 AND ((recinfo.COMMON_TO_ALL_ORGS = X_COMMON_TO_ALL_ORGS)
260 OR ((recinfo.COMMON_TO_ALL_ORGS is null) AND (X_COMMON_TO_ALL_ORGS is null)))
261 AND ((recinfo.ENABLED = X_ENABLED)
262 OR ((recinfo.ENABLED is null) AND (X_ENABLED is null)))
263 AND ((recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
264 OR ((recinfo.DEFAULT_FLAG is null) AND (X_DEFAULT_FLAG is null)))
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271
272 for tlinfo in c1 loop
273 if (tlinfo.BASELANG = 'Y') then
274 if ( (tlinfo.USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME)
275 AND ((tlinfo.TEMPLATE_DESCRIPTION = X_TEMPLATE_DESCRIPTION)
276 OR ((tlinfo.TEMPLATE_DESCRIPTION is null) AND (X_TEMPLATE_DESCRIPTION is null)))
277 ) then
278 null;
279 else
280 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
281 app_exception.raise_exception;
282 end if;
283 end if;
284 end loop;
285 return;
286 end LOCK_ROW;
287
288 procedure UPDATE_ROW (
289 X_TEMPLATE_ID in NUMBER,
290 X_ATTRIBUTE_CATEGORY in VARCHAR2,
291 X_ATTRIBUTE1 in VARCHAR2,
292 X_ATTRIBUTE2 in VARCHAR2,
293 X_ATTRIBUTE3 in VARCHAR2,
294 X_ATTRIBUTE4 in VARCHAR2,
295 X_ATTRIBUTE5 in VARCHAR2,
296 X_ATTRIBUTE6 in VARCHAR2,
297 X_ATTRIBUTE7 in VARCHAR2,
298 X_ATTRIBUTE8 in VARCHAR2,
299 X_ATTRIBUTE9 in VARCHAR2,
300 X_ATTRIBUTE10 in VARCHAR2,
301 X_ATTRIBUTE11 in VARCHAR2,
302 X_ATTRIBUTE12 in VARCHAR2,
303 X_ATTRIBUTE13 in VARCHAR2,
304 X_ATTRIBUTE14 in VARCHAR2,
305 X_ATTRIBUTE15 in VARCHAR2,
306 X_PAGE_ID in NUMBER,
307 X_TEMPLATE_NAME in VARCHAR2,
308 X_CREATING_ORGANIZATION_ID in NUMBER,
309 X_CREATING_ORGANIZATION_CODE in VARCHAR2,
310 X_COMMON_TO_ALL_ORGS in VARCHAR2,
311 X_ENABLED in VARCHAR2,
312 X_DEFAULT_FLAG in VARCHAR2,
313 X_USER_TEMPLATE_NAME in VARCHAR2,
314 X_TEMPLATE_DESCRIPTION in VARCHAR2,
315 X_LAST_UPDATE_DATE in DATE,
316 X_LAST_UPDATED_BY in NUMBER,
317 X_LAST_UPDATE_LOGIN in NUMBER
318 ) is
319 begin
320 update WMS_PAGE_TEMPLATES_B set
321 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
322 ATTRIBUTE1 = X_ATTRIBUTE1,
323 ATTRIBUTE2 = X_ATTRIBUTE2,
324 ATTRIBUTE3 = X_ATTRIBUTE3,
325 ATTRIBUTE4 = X_ATTRIBUTE4,
326 ATTRIBUTE5 = X_ATTRIBUTE5,
327 ATTRIBUTE6 = X_ATTRIBUTE6,
328 ATTRIBUTE7 = X_ATTRIBUTE7,
329 ATTRIBUTE8 = X_ATTRIBUTE8,
330 ATTRIBUTE9 = X_ATTRIBUTE9,
331 ATTRIBUTE10 = X_ATTRIBUTE10,
332 ATTRIBUTE11 = X_ATTRIBUTE11,
333 ATTRIBUTE12 = X_ATTRIBUTE12,
334 ATTRIBUTE13 = X_ATTRIBUTE13,
335 ATTRIBUTE14 = X_ATTRIBUTE14,
336 ATTRIBUTE15 = X_ATTRIBUTE15,
337 PAGE_ID = X_PAGE_ID,
338 TEMPLATE_NAME = X_TEMPLATE_NAME,
339 CREATING_ORGANIZATION_ID = X_CREATING_ORGANIZATION_ID,
340 CREATING_ORGANIZATION_CODE = X_CREATING_ORGANIZATION_CODE,
341 COMMON_TO_ALL_ORGS = X_COMMON_TO_ALL_ORGS,
342 ENABLED = X_ENABLED,
343 DEFAULT_FLAG = X_DEFAULT_FLAG,
344 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
345 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
346 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
347 where TEMPLATE_ID = X_TEMPLATE_ID;
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352
353 update WMS_PAGE_TEMPLATES_TL set
354 USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME,
355 TEMPLATE_DESCRIPTION = X_TEMPLATE_DESCRIPTION,
356 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
357 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
358 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
359 SOURCE_LANG = userenv('LANG')
360 where TEMPLATE_ID = X_TEMPLATE_ID
361 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366 end UPDATE_ROW;
367
368 PROCEDURE TRANSLATE_ROW(
369 X_PAGE_ID in NUMBER,
370 X_TEMPLATE_NAME in VARCHAR2,
371 X_OWNER in VARCHAR2,
372 X_USER_TEMPLATE_NAME in VARCHAR2,
373 X_TEMPLATE_DESCRIPTION in VARCHAR2,
374 x_last_update_date in varchar2,
375 x_custom_mode in varchar2) is
376
377 l_template_id number;
378 owner_id number;
379 ludate date;
380 row_id varchar2(64);
381 f_luby number; -- entity owner in file
382 f_ludate date; -- entity update date in file
383 db_luby number; -- entity owner in db
384 db_ludate date; -- entity update date in db
385 begin
386 -- Translate owner to file_last_updated_by
387 f_luby := fnd_load_util.owner_id(X_OWNER);
388
389 -- Translate char last_update_date to date
390 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
391
392 begin
393
394 -- translate values to IDs
395 select TEMPLATE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
396 into l_template_id, db_luby, db_ludate
397 from WMS_PAGE_TEMPLATES_B
398 where PAGE_ID = X_PAGE_ID
399 and TEMPLATE_NAME = X_TEMPLATE_NAME;
400
401 -- Test for customization and version
402 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
403 db_ludate, x_custom_mode)) then
404 -- Update translations for this language
405 update WMS_PAGE_TEMPLATES_TL set
406 USER_TEMPLATE_NAME = X_USER_TEMPLATE_NAME,
407 TEMPLATE_DESCRIPTION = nvl(X_TEMPLATE_DESCRIPTION, TEMPLATE_DESCRIPTION),
408 LAST_UPDATE_DATE = f_ludate,
409 LAST_UPDATED_BY = f_luby,
410 LAST_UPDATE_LOGIN = 0,
411 SOURCE_LANG = userenv('LANG')
412 where TEMPLATE_ID = l_template_id
413 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
414 end if;
415 exception
416 when no_data_found then
417 -- Do not insert missing translations, skip this row
418 null;
419 end;
420 end TRANSLATE_ROW;
421
422 PROCEDURE LOAD_ROW(
423 X_PAGE_ID in NUMBER,
424 X_TEMPLATE_NAME in VARCHAR2,
425 X_OWNER in VARCHAR2,
426 X_CREATING_ORGANIZATION_ID in NUMBER,
427 X_CREATING_ORGANIZATION_CODE in VARCHAR2,
428 X_COMMON_TO_ALL_ORGS in VARCHAR2,
429 X_ENABLED in VARCHAR2,
430 X_DEFAULT_FLAG in VARCHAR2,
431 X_USER_TEMPLATE_NAME in VARCHAR2,
432 X_TEMPLATE_DESCRIPTION in VARCHAR2,
433 X_ATTRIBUTE_CATEGORY in VARCHAR2,
434 X_ATTRIBUTE1 in VARCHAR2,
435 X_ATTRIBUTE2 in VARCHAR2,
436 X_ATTRIBUTE3 in VARCHAR2,
437 X_ATTRIBUTE4 in VARCHAR2,
438 X_ATTRIBUTE5 in VARCHAR2,
439 X_ATTRIBUTE6 in VARCHAR2,
440 X_ATTRIBUTE7 in VARCHAR2,
441 X_ATTRIBUTE8 in VARCHAR2,
442 X_ATTRIBUTE9 in VARCHAR2,
443 X_ATTRIBUTE10 in VARCHAR2,
444 X_ATTRIBUTE11 in VARCHAR2,
445 X_ATTRIBUTE12 in VARCHAR2,
446 X_ATTRIBUTE13 in VARCHAR2,
447 X_ATTRIBUTE14 in VARCHAR2,
448 X_ATTRIBUTE15 in VARCHAR2,
449 x_last_update_date in varchar2,
450 x_custom_mode in varchar2) is
451
452 l_template_id number;
453 l_TEMPLATE_DESCRIPTION WMS_PAGE_TEMPLATES_TL.TEMPLATE_DESCRIPTION%TYPE;
454 row_id varchar2(64);
455 f_luby number; -- entity owner in file
456 f_ludate date; -- entity update date in file
457 db_luby number; -- entity owner in db
458 db_ludate date; -- entity update date in db
459
460 begin
461
462 -- Translate a true null value to fnd_api.g_miss_char
463 -- Note table handler apis should be coded to treat
464 -- fnd_api.g_miss_* as true nulls, and not as no-change.
465 if (X_TEMPLATE_DESCRIPTION = fnd_load_util.null_value) then
466 l_TEMPLATE_DESCRIPTION := fnd_api.g_miss_char;
467 else
468 l_TEMPLATE_DESCRIPTION := X_TEMPLATE_DESCRIPTION;
469 end if;
470
471 -- Translate owner to file_last_updated_by
472 f_luby := fnd_load_util.owner_id(X_OWNER);
473
474 -- Translate char last_update_date to date
475 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
476
477 begin
478 -- translate values to IDs
479 select TEMPLATE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
480 into l_template_id, db_luby, db_ludate
481 from WMS_PAGE_TEMPLATES_B
482 where PAGE_ID = X_PAGE_ID
483 and TEMPLATE_NAME = X_TEMPLATE_NAME;
484
485 -- Test for customization and version
486 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
487 db_ludate, X_CUSTOM_MODE)) then
488 -- Update existing row
489 WMS_PAGE_TEMPLATES_PKG.UPDATE_ROW(
490 X_PAGE_ID => X_PAGE_ID,
491 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
492 X_TEMPLATE_ID => l_TEMPLATE_ID,
493 X_CREATING_ORGANIZATION_ID => X_CREATING_ORGANIZATION_ID,
494 X_CREATING_ORGANIZATION_CODE => X_CREATING_ORGANIZATION_CODE,
495 X_COMMON_TO_ALL_ORGS => X_COMMON_TO_ALL_ORGS,
496 X_ENABLED => X_ENABLED,
497 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
498 X_USER_TEMPLATE_NAME => X_USER_TEMPLATE_NAME,
499 X_TEMPLATE_DESCRIPTION => l_TEMPLATE_DESCRIPTION,
500 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
501 X_ATTRIBUTE1 => X_ATTRIBUTE1,
502 X_ATTRIBUTE2 => X_ATTRIBUTE2,
503 X_ATTRIBUTE3 => X_ATTRIBUTE3,
504 X_ATTRIBUTE4 => X_ATTRIBUTE4,
505 X_ATTRIBUTE5 => X_ATTRIBUTE5,
506 X_ATTRIBUTE6 => X_ATTRIBUTE6,
507 X_ATTRIBUTE7 => X_ATTRIBUTE7,
508 X_ATTRIBUTE8 => X_ATTRIBUTE8,
509 X_ATTRIBUTE9 => X_ATTRIBUTE9,
510 X_ATTRIBUTE10 => X_ATTRIBUTE10,
511 X_ATTRIBUTE11 => X_ATTRIBUTE11,
512 X_ATTRIBUTE12 => X_ATTRIBUTE12,
513 X_ATTRIBUTE13 => X_ATTRIBUTE13,
514 X_ATTRIBUTE14 => X_ATTRIBUTE14,
515 X_ATTRIBUTE15 => X_ATTRIBUTE15,
516 X_LAST_UPDATE_DATE => f_ludate,
517 X_LAST_UPDATED_BY => f_luby,
518 X_LAST_UPDATE_LOGIN => 0);
519
520 end if;
521
522 exception
523 when no_data_found then
524 -- Record doesn't exist - insert in all cases
525
526 select WMS_PAGE_TEMPLATES_S.nextval into l_TEMPLATE_ID
527 from dual;
528
529 WMS_PAGE_TEMPLATES_PKG.INSERT_ROW(
530 X_ROWID => row_id,
531 X_PAGE_ID => X_PAGE_ID,
532 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
533 X_TEMPLATE_ID => l_TEMPLATE_ID,
534 X_CREATING_ORGANIZATION_ID => X_CREATING_ORGANIZATION_ID,
535 X_CREATING_ORGANIZATION_CODE => X_CREATING_ORGANIZATION_CODE,
536 X_COMMON_TO_ALL_ORGS => X_COMMON_TO_ALL_ORGS,
537 X_ENABLED => X_ENABLED,
538 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
539 X_USER_TEMPLATE_NAME => X_USER_TEMPLATE_NAME,
540 X_TEMPLATE_DESCRIPTION => l_TEMPLATE_DESCRIPTION,
541 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
542 X_ATTRIBUTE1 => X_ATTRIBUTE1,
543 X_ATTRIBUTE2 => X_ATTRIBUTE2,
544 X_ATTRIBUTE3 => X_ATTRIBUTE3,
545 X_ATTRIBUTE4 => X_ATTRIBUTE4,
546 X_ATTRIBUTE5 => X_ATTRIBUTE5,
547 X_ATTRIBUTE6 => X_ATTRIBUTE6,
548 X_ATTRIBUTE7 => X_ATTRIBUTE7,
549 X_ATTRIBUTE8 => X_ATTRIBUTE8,
550 X_ATTRIBUTE9 => X_ATTRIBUTE9,
551 X_ATTRIBUTE10 => X_ATTRIBUTE10,
552 X_ATTRIBUTE11 => X_ATTRIBUTE11,
553 X_ATTRIBUTE12 => X_ATTRIBUTE12,
554 X_ATTRIBUTE13 => X_ATTRIBUTE13,
555 X_ATTRIBUTE14 => X_ATTRIBUTE14,
556 X_ATTRIBUTE15 => X_ATTRIBUTE15,
557 X_CREATION_DATE => f_ludate,
558 X_CREATED_BY => f_luby,
559 X_LAST_UPDATE_DATE => f_ludate,
560 X_LAST_UPDATED_BY => f_luby,
561 X_LAST_UPDATE_LOGIN => 0);
562 end;
563 end LOAD_ROW;
564
565 procedure DELETE_ROW (
566 X_TEMPLATE_ID in NUMBER
567 ) is
568 begin
569
570 delete from WMS_PAGE_TEMPLATE_FIELDS
571 where TEMPLATE_ID = X_TEMPLATE_ID;
572
573 delete from WMS_PAGE_TEMPLATES_TL
574 where TEMPLATE_ID = X_TEMPLATE_ID;
575
576 if (sql%notfound) then
577 raise no_data_found;
578 end if;
579
580 delete from WMS_PAGE_TEMPLATES_B
581 where TEMPLATE_ID = X_TEMPLATE_ID;
582
583 if (sql%notfound) then
584 raise no_data_found;
585 end if;
586 end DELETE_ROW;
587
588 procedure ADD_LANGUAGE
589 is
590 begin
591 delete from WMS_PAGE_TEMPLATES_TL T
592 where not exists
593 (select NULL
594 from WMS_PAGE_TEMPLATES_B B
595 where B.TEMPLATE_ID = T.TEMPLATE_ID
596 );
597
598 update WMS_PAGE_TEMPLATES_TL T set (
599 USER_TEMPLATE_NAME,
600 TEMPLATE_DESCRIPTION
601 ) = (select
602 B.USER_TEMPLATE_NAME,
603 B.TEMPLATE_DESCRIPTION
604 from WMS_PAGE_TEMPLATES_TL B
605 where B.TEMPLATE_ID = T.TEMPLATE_ID
606 and B.LANGUAGE = T.SOURCE_LANG)
607 where (
608 T.TEMPLATE_ID,
609 T.LANGUAGE
610 ) in (select
611 SUBT.TEMPLATE_ID,
612 SUBT.LANGUAGE
613 from WMS_PAGE_TEMPLATES_TL SUBB, WMS_PAGE_TEMPLATES_TL SUBT
614 where SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
615 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
616 and (SUBB.USER_TEMPLATE_NAME <> SUBT.USER_TEMPLATE_NAME
617 or SUBB.TEMPLATE_DESCRIPTION <> SUBT.TEMPLATE_DESCRIPTION
618 or (SUBB.TEMPLATE_DESCRIPTION is null and SUBT.TEMPLATE_DESCRIPTION is not null)
619 or (SUBB.TEMPLATE_DESCRIPTION is not null and SUBT.TEMPLATE_DESCRIPTION is null)
620 ));
621
622 insert into WMS_PAGE_TEMPLATES_TL (
623 PAGE_ID,
624 TEMPLATE_ID,
625 USER_TEMPLATE_NAME,
626 TEMPLATE_DESCRIPTION,
627 CREATION_DATE,
628 CREATED_BY,
629 LAST_UPDATE_DATE,
630 LAST_UPDATED_BY,
631 LAST_UPDATE_LOGIN,
632 LANGUAGE,
633 SOURCE_LANG
634 ) select /*+ ORDERED */
635 B.PAGE_ID,
636 B.TEMPLATE_ID,
637 B.USER_TEMPLATE_NAME,
638 B.TEMPLATE_DESCRIPTION,
639 B.CREATION_DATE,
640 B.CREATED_BY,
641 B.LAST_UPDATE_DATE,
642 B.LAST_UPDATED_BY,
643 B.LAST_UPDATE_LOGIN,
644 L.LANGUAGE_CODE,
645 B.SOURCE_LANG
646 from WMS_PAGE_TEMPLATES_TL B, FND_LANGUAGES L
647 where L.INSTALLED_FLAG in ('I', 'B')
648 and B.LANGUAGE = userenv('LANG')
649 and not exists
650 (select NULL
651 from WMS_PAGE_TEMPLATES_TL T
652 where T.TEMPLATE_ID = B.TEMPLATE_ID
653 and T.LANGUAGE = L.LANGUAGE_CODE);
654 end ADD_LANGUAGE;
655
656 end WMS_PAGE_TEMPLATES_PKG;