[Home] [Help]
PACKAGE BODY: APPS.ASO_SUP_TEMPLATE_PKG
Source
1 Package Body ASO_SUP_TEMPLATE_PKG AS
2 /* $Header: asosptmb.pls 120.4 2006/05/22 22:59:53 skulkarn ship $*/
3
4 /* procedure to insert INSERT_ROW */
5
6 PROCEDURE INSERT_ROW
7 (
8 PX_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
9 PX_TEMPLATE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
10 P_created_by IN NUMBER ,
11 P_creation_date IN DATE ,
12 P_last_updated_by IN NUMBER,
13 P_last_update_date IN DATE,
14 P_last_update_login IN NUMBER,
15 P_TEMPLATE_NAME IN VARCHAR2,
16 P_DESCRIPTION IN VARCHAR2 := NULL,
17 P_TEMPLATE_LEVEL IN VARCHAR2,
18 P_TEMPLATE_CONTEXT IN VARCHAR2,
19 P_CONTEXT IN VARCHAR2 := NULL,
20 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
21 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
22 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
23 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
24 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
25 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
26 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
27 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
28 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
29 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
30 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
31 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
32 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
33 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
34 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
35 P_ATTRIBUTE16 IN VARCHAR2 := NULL,
36 P_ATTRIBUTE17 IN VARCHAR2 := NULL,
37 P_ATTRIBUTE18 IN VARCHAR2 := NULL,
38 P_ATTRIBUTE19 IN VARCHAR2 := NULL,
39 P_ATTRIBUTE20 IN VARCHAR2 := NULL
40 )
41
42 IS
43
44 cursor c is
45 select ROWID
46 from ASO_SUP_TEMPLATE_B
47 where TEMPLATE_ID = PX_TEMPLATE_ID ;
48
49 cursor CU_TEMPLATE_ID IS
50 select ASO_SUP_TEMPLATE_B_S.NEXTVAL from sys.dual;
51
52 Begin
53
54 IF (PX_TEMPLATE_ID IS NULL) OR (PX_TEMPLATE_ID = FND_API.G_MISS_NUM) THEN
55 OPEN CU_TEMPLATE_ID;
56 FETCH CU_TEMPLATE_ID INTO PX_TEMPLATE_ID;
57 CLOSE CU_TEMPLATE_ID;
58
59 END IF;
60
61 -- added new column as per bug 2940126
62
63 insert into ASO_SUP_TEMPLATE_B (
64 TEMPLATE_ID,
65 created_by ,
66 creation_date ,
67 last_updated_by ,
68 last_update_date ,
69 last_update_login ,
70 TEMPLATE_LEVEL,
71 TEMPLATE_CONTEXT,
72 CONTEXT,
73 ATTRIBUTE1 ,
74 ATTRIBUTE2 ,
75 ATTRIBUTE3 ,
76 ATTRIBUTE4 ,
77 ATTRIBUTE5 ,
78 ATTRIBUTE6 ,
79 ATTRIBUTE7 ,
80 ATTRIBUTE8 ,
81 ATTRIBUTE9 ,
82 ATTRIBUTE10 ,
83 ATTRIBUTE11 ,
84 ATTRIBUTE12 ,
85 ATTRIBUTE13 ,
86 ATTRIBUTE14 ,
87 ATTRIBUTE15,
88 ATTRIBUTE16,
89 ATTRIBUTE17,
90 ATTRIBUTE18,
91 ATTRIBUTE19,
92 ATTRIBUTE20 )
93 values
94 (
95 PX_TEMPLATE_ID,
96 P_created_by ,
97 P_creation_date ,
98 P_last_updated_by ,
99 P_last_update_date ,
100 P_last_update_login,
101 P_TEMPLATE_LEVEL,
102 P_TEMPLATE_CONTEXT,
103 P_CONTEXT,
104 P_ATTRIBUTE1 ,
105 P_ATTRIBUTE2 ,
106 P_ATTRIBUTE3 ,
107 P_ATTRIBUTE4 ,
108 P_ATTRIBUTE5 ,
109 P_ATTRIBUTE6 ,
110 P_ATTRIBUTE7 ,
111 P_ATTRIBUTE8 ,
112 P_ATTRIBUTE9 ,
113 P_ATTRIBUTE10 ,
114 P_ATTRIBUTE11 ,
115 P_ATTRIBUTE12 ,
116 P_ATTRIBUTE13 ,
117 P_ATTRIBUTE14 ,
118 P_ATTRIBUTE15,
119 P_ATTRIBUTE16,
120 P_ATTRIBUTE17,
121 P_ATTRIBUTE18,
122 P_ATTRIBUTE19,
123 P_ATTRIBUTE20
124 );
125
126 insert into ASO_SUP_TEMPLATE_TL (
127 TEMPLATE_ID,
128 LANGUAGE,
129 SOURCE_LANG,
130 TEMPLATE_NAME,
131 DESCRIPTION,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 CREATION_DATE,
135 CREATED_BY,
136 LAST_UPDATE_LOGIN
137 ) select
138 PX_TEMPLATE_ID,
139 L.LANGUAGE_CODE,
140 userenv('LANG'),
141 P_TEMPLATE_NAME,
142 P_DESCRIPTION,
143 P_LAST_UPDATE_DATE,
144 P_LAST_UPDATED_BY,
145 P_CREATION_DATE,
146 P_CREATED_BY,
147 P_LAST_UPDATE_LOGIN
148 from FND_LANGUAGES L
149 where L.INSTALLED_FLAG in ('I', 'B')
150 and not exists
151 ( select 'x'
152 from ASO_SUP_TEMPLATE_TL T
153 where T.TEMPLATE_ID = PX_TEMPLATE_ID
154 and T.LANGUAGE = L.LANGUAGE_CODE );
155
156 open c;
157 fetch c into PX_ROWID;
158 if (c%notfound) then
159 close c;
160 raise no_data_found;
161 end if;
162 close c;
163
164 end INSERT_ROW;
165
166
167 PROCEDURE UPDATE_ROW
168 (
169 P_TEMPLATE_ID IN NUMBER,
170 P_last_updated_by IN NUMBER,
171 P_last_update_date IN DATE,
172 P_last_update_login IN NUMBER,
173 P_TEMPLATE_NAME IN VARCHAR2,
174 P_DESCRIPTION IN VARCHAR2,
175 P_TEMPLATE_LEVEL IN VARCHAR2,
176 P_TEMPLATE_CONTEXT IN VARCHAR2,
177 P_CONTEXT IN VARCHAR2,
178 P_ATTRIBUTE1 IN VARCHAR2,
179 P_ATTRIBUTE2 IN VARCHAR2,
180 P_ATTRIBUTE3 IN VARCHAR2,
181 P_ATTRIBUTE4 IN VARCHAR2,
182 P_ATTRIBUTE5 IN VARCHAR2,
183 P_ATTRIBUTE6 IN VARCHAR2,
184 P_ATTRIBUTE7 IN VARCHAR2,
185 P_ATTRIBUTE8 IN VARCHAR2,
186 P_ATTRIBUTE9 IN VARCHAR2,
187 P_ATTRIBUTE10 IN VARCHAR2,
188 P_ATTRIBUTE11 IN VARCHAR2,
189 P_ATTRIBUTE12 IN VARCHAR2,
190 P_ATTRIBUTE13 IN VARCHAR2,
191 P_ATTRIBUTE14 IN VARCHAR2,
192 P_ATTRIBUTE15 IN VARCHAR2,
193 P_ATTRIBUTE16 IN VARCHAR2,
194 P_ATTRIBUTE17 IN VARCHAR2,
195 P_ATTRIBUTE18 IN VARCHAR2,
196 P_ATTRIBUTE19 IN VARCHAR2,
197 P_ATTRIBUTE20 IN VARCHAR2
198
199 )
200
201 IS
202
203 Begin
204 -- added new column as per bug 2940126
205 update ASO_SUP_TEMPLATE_B
206 set
207 last_updated_by = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
208 last_update_date = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
209 last_update_login = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
210 template_level = decode(P_TEMPLATE_LEVEL,FND_API.G_MISS_CHAR, template_level,P_TEMPLATE_LEVEL),
211 template_context = decode(P_TEMPLATE_CONTEXT,FND_API.G_MISS_CHAR,template_context,P_TEMPLATE_CONTEXT),
212 context = decode(P_context,FND_API.G_MISS_CHAR,context,P_context),
213 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
214 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
215 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
216 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
217 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
218 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
219 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
220 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
221 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
222 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
223 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
224 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
225 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
226 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
227 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15),
228 ATTRIBUTE16 = decode( p_ATTRIBUTE16, FND_API.G_MISS_CHAR, ATTRIBUTE16, p_ATTRIBUTE16),
229 ATTRIBUTE17 = decode( p_ATTRIBUTE17, FND_API.G_MISS_CHAR, ATTRIBUTE17, p_ATTRIBUTE17),
230 ATTRIBUTE18 = decode( p_ATTRIBUTE18, FND_API.G_MISS_CHAR, ATTRIBUTE18, p_ATTRIBUTE18),
231 ATTRIBUTE19 = decode( p_ATTRIBUTE19, FND_API.G_MISS_CHAR, ATTRIBUTE19, p_ATTRIBUTE19),
232 ATTRIBUTE20 = decode( p_ATTRIBUTE20, FND_API.G_MISS_CHAR, ATTRIBUTE20, p_ATTRIBUTE20)
233 where TEMPLATE_ID = P_TEMPLATE_ID;
234
235 if (sql%notfound) then
236 raise no_data_found;
237 end if;
238
239 update ASO_SUP_TEMPLATE_TL
240 set
241 TEMPLATE_NAME = decode(P_TEMPLATE_NAME,FND_API.G_MISS_CHAR,template_name,P_TEMPLATE_NAME),
242 DESCRIPTION = decode(P_DESCRIPTION,FND_API.G_MISS_CHAR,description,P_DESCRIPTION),
243 LAST_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
244 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
245 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
246 SOURCE_LANG = userenv('LANG')
247 where TEMPLATE_ID = P_TEMPLATE_ID
248 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253
254
255 End UPDATE_ROW;
256
257
258 procedure DELETE_ROW (
259 P_TEMPLATE_ID IN NUMBER
260
261 )
262
263 IS
264
265 Begin
266
267 delete from ASO_SUP_TEMPLATE_TL
268 where TEMPLATE_ID = P_TEMPLATE_ID;
269
270 if (sql%notfound) then
271 raise no_data_found;
272 end if;
273
274
275 delete from ASO_SUP_TEMPLATE_B
276 where TEMPLATE_ID = P_TEMPLATE_ID;
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281
282 End Delete_row;
283
284 PROCEDURE LOCK_ROW
285 (
286 P_TEMPLATE_ID IN NUMBER,
287 P_created_by IN NUMBER ,
288 P_creation_date IN DATE ,
289 P_last_updated_by IN NUMBER,
290 P_last_update_date IN DATE,
291 P_last_update_login IN NUMBER,
292 P_TEMPLATE_NAME IN VARCHAR2,
293 P_DESCRIPTION IN VARCHAR2,
294 P_TEMPLATE_LEVEL IN VARCHAR2,
295 P_TEMPLATE_CONTEXT IN VARCHAR2,
296 P_CONTEXT IN VARCHAR2,
297 P_ATTRIBUTE1 IN VARCHAR2,
298 P_ATTRIBUTE2 IN VARCHAR2,
299 P_ATTRIBUTE3 IN VARCHAR2,
300 P_ATTRIBUTE4 IN VARCHAR2,
301 P_ATTRIBUTE5 IN VARCHAR2,
302 P_ATTRIBUTE6 IN VARCHAR2,
303 P_ATTRIBUTE7 IN VARCHAR2,
304 P_ATTRIBUTE8 IN VARCHAR2,
305 P_ATTRIBUTE9 IN VARCHAR2,
306 P_ATTRIBUTE10 IN VARCHAR2,
307 P_ATTRIBUTE11 IN VARCHAR2,
308 P_ATTRIBUTE12 IN VARCHAR2,
309 P_ATTRIBUTE13 IN VARCHAR2,
310 P_ATTRIBUTE14 IN VARCHAR2,
311 P_ATTRIBUTE15 IN VARCHAR2
312
313 )
314
315 IS
316
317 CURSOR i_csr is
318 SELECT
319 a.TEMPLATE_ID ,
320 created_by ,
321 creation_date ,
322 last_updated_by ,
323 last_update_date ,
324 last_update_login ,
325 template_level,
326 template_context,
327 context,
328 ATTRIBUTE1 ,
329 ATTRIBUTE2 ,
330 ATTRIBUTE3 ,
331 ATTRIBUTE4 ,
332 ATTRIBUTE5 ,
333 ATTRIBUTE6 ,
334 ATTRIBUTE7 ,
335 ATTRIBUTE8 ,
336 ATTRIBUTE9 ,
337 ATTRIBUTE10 ,
338 ATTRIBUTE11 ,
339 ATTRIBUTE12 ,
340 ATTRIBUTE13 ,
341 ATTRIBUTE14 ,
342 ATTRIBUTE15
343
344 from ASO_SUP_TEMPLATE_B a
345 where a.TEMPLATE_ID = P_TEMPLATE_ID
346 for update of a.TEMPLATE_ID nowait;
347
348 recinfo i_csr%rowtype;
349
350 cursor c1 is
351 select
352 TEMPLATE_NAME,
353 DESCRIPTION,
354 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
355 from ASO_SUP_TEMPLATE_TL
356 where TEMPLATE_ID = P_TEMPLATE_ID
357 for update of TEMPLATE_ID nowait;
358
359 l_Item_ID NUMBER ;
360 l_Org_ID NUMBER ;
361
362 l_return_status VARCHAR2(1) ;
363
364 BEGIN
365
366
367 l_Item_ID := P_TEMPLATE_ID ;
368
369 open i_csr;
370
371 fetch i_csr into recinfo;
372
373 if (i_csr%notfound) then
374 close i_csr;
375 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
376 app_exception.raise_exception;
377 end if;
378
379 close i_csr;
380
381 -- Do not compare to the B table column;
382 -- only compare to TL column (c1 cursor below).
383
384 if (
385 ((recinfo.TEMPLATE_ID = P_TEMPLATE_ID)
386 OR ((recinfo.TEMPLATE_ID is null) AND (P_TEMPLATE_ID is null)))
387 AND ((recinfo.CREATED_BY = P_CREATED_BY)
388 OR ((recinfo.CREATED_BY is null) AND (P_CREATED_BY is null)))
389 AND ((recinfo.CREATION_DATE = P_CREATION_DATE)
390 OR ((recinfo.CREATION_DATE is null) AND (P_CREATION_DATE is null)))
391 AND ((recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
392 OR ((recinfo.LAST_UPDATED_BY is null) AND (P_LAST_UPDATED_BY is null)))
393 AND ((recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
394 OR ((recinfo.LAST_UPDATE_DATE is null) AND (P_LAST_UPDATE_DATE is null)))
395 AND ((recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
396 OR ((recinfo.LAST_UPDATE_LOGIN is null) AND (P_LAST_UPDATE_LOGIN is null)))
397
398
399 -- added new column as per bug 2940126
400 AND ((recinfo.TEMPLATE_LEVEL = P_TEMPLATE_LEVEL )
401 OR ((recinfo.TEMPLATE_LEVEL is null) AND (P_TEMPLATE_LEVEL is null)))
402
403 AND ((recinfo.TEMPLATE_CONTEXT = P_TEMPLATE_CONTEXT )
404 OR ((recinfo.TEMPLATE_CONTEXT is null) AND (P_TEMPLATE_CONTEXT is null)))
405
406
407 AND ((recinfo.CONTEXT = P_CONTEXT)
408 OR ((recinfo.CONTEXT is null) AND (P_CONTEXT is null)))
409 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
410 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
411 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
412 OR ((recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
413 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
414 OR ((recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
415 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
416 OR ((recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
417 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
418 OR ((recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
419 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
420 OR ((recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
421 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
422 OR ((recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
423 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
424 OR ((recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
425 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
426 OR ((recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
427 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
428 OR ((recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
429 AND ((recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
430 OR ((recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
431 AND ((recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
432 OR ((recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
433 AND ((recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
434 OR ((recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
438 OR ((recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
435 AND ((recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
436 OR ((recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
437 AND ((recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
439 ) then
440 null;
441 else
442 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
443 app_exception.raise_exception;
444 end if;
445
446 for tlinfo in c1 loop
447 if (tlinfo.BASELANG = 'Y') then
448 if ( ((tlinfo.TEMPLATE_NAME = P_TEMPLATE_NAME)
449 OR ((tlinfo.TEMPLATE_NAME is null) AND (P_TEMPLATE_NAME is null)))
450 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
451 OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
452
453 ) then
454 null;
455 else
456 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
457 app_exception.raise_exception;
458 end if;
459 end if;
460 end loop;
461
462 return;
463
464 End Lock_Row;
465
466
467 /* procedure for ADD_LANGUAGE */
468
469 procedure ADD_LANGUAGE
470 is
471 begin
472
473 delete from ASO_SUP_TEMPLATE_TL T
474 where not exists
475 ( select NULL
476 from ASO_SUP_TEMPLATE_B B
477 where B.TEMPLATE_ID = T.TEMPLATE_ID
478 );
479
480 update ASO_SUP_TEMPLATE_TL T set (
481 TEMPLATE_NAME,
482 DESCRIPTION
483 ) = ( select
484 B.TEMPLATE_NAME,
485 B.DESCRIPTION
486 from ASO_SUP_TEMPLATE_TL B
487 where B.TEMPLATE_ID = T.TEMPLATE_ID
488 and B.LANGUAGE = T.SOURCE_LANG )
489 where (
490 T.TEMPLATE_ID,
491 T.LANGUAGE
492 ) in ( select
493 SUBT.TEMPLATE_ID,
494 SUBT.LANGUAGE
495 from ASO_SUP_TEMPLATE_TL SUBB,
496 ASO_SUP_TEMPLATE_TL SUBT
497 where SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
498 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
499 and ( SUBB.TEMPLATE_NAME <> SUBT.TEMPLATE_NAME
500 or ( SUBB.TEMPLATE_NAME is null and SUBT.TEMPLATE_NAME is not null )
501 or ( SUBB.TEMPLATE_NAME is not null and SUBT.TEMPLATE_NAME is null ) )
502 and ( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
503 or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
504 or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
505
506 );
507
508 insert into ASO_SUP_TEMPLATE_TL (
509 TEMPLATE_ID,
510 LANGUAGE,
511 SOURCE_LANG,
512 TEMPLATE_NAME,
513 DESCRIPTION,
514 LAST_UPDATE_DATE,
515 LAST_UPDATED_BY,
516 CREATION_DATE,
517 CREATED_BY,
518 LAST_UPDATE_LOGIN
519 ) select
520 B.TEMPLATE_ID,
521 L.LANGUAGE_CODE,
522 B.SOURCE_LANG,
523 B.TEMPLATE_NAME,
524 B.DESCRIPTION,
525 B.LAST_UPDATE_DATE,
526 B.LAST_UPDATED_BY,
527 B.CREATION_DATE,
528 B.CREATED_BY,
529 B.LAST_UPDATE_LOGIN
530 from ASO_SUP_TEMPLATE_TL B,
531 FND_LANGUAGES L
532 where L.INSTALLED_FLAG in ('I', 'B')
533 and B.LANGUAGE = userenv('LANG')
534 and not exists
535 ( select NULL
536 from ASO_SUP_TEMPLATE_TL T
537 where T.TEMPLATE_ID = B.TEMPLATE_ID
538 and T.LANGUAGE = L.LANGUAGE_CODE );
539
540 end ADD_LANGUAGE;
541
542
543 /* Procedure for Load_Row */
544
545 procedure LOAD_ROW (
546 P_TEMPLATE_ID IN NUMBER,
547 P_created_by IN NUMBER ,
548 P_creation_date IN DATE ,
549 P_last_updated_by IN NUMBER,
550 P_last_update_date IN DATE,
551 P_last_update_login IN NUMBER,
552 P_TEMPLATE_NAME IN VARCHAR2,
553 P_DESCRIPTION IN VARCHAR2,
554 P_TEMPLATE_LEVEL IN VARCHAR2,
555 P_TEMPLATE_CONTEXT IN VARCHAR2,
556 P_CONTEXT IN VARCHAR2,
557 P_ATTRIBUTE1 IN VARCHAR2,
558 P_ATTRIBUTE2 IN VARCHAR2,
559 P_ATTRIBUTE3 IN VARCHAR2,
560 P_ATTRIBUTE4 IN VARCHAR2,
561 P_ATTRIBUTE5 IN VARCHAR2,
562 P_ATTRIBUTE6 IN VARCHAR2,
563 P_ATTRIBUTE7 IN VARCHAR2,
564 P_ATTRIBUTE8 IN VARCHAR2,
565 P_ATTRIBUTE9 IN VARCHAR2,
566 P_ATTRIBUTE10 IN VARCHAR2,
567 P_ATTRIBUTE11 IN VARCHAR2,
568 P_ATTRIBUTE12 IN VARCHAR2,
569 P_ATTRIBUTE13 IN VARCHAR2,
570 P_ATTRIBUTE14 IN VARCHAR2,
571 P_ATTRIBUTE15 IN VARCHAR2,
572 P_ATTRIBUTE16 IN VARCHAR2,
573 P_ATTRIBUTE17 IN VARCHAR2,
574 P_ATTRIBUTE18 IN VARCHAR2,
575 P_ATTRIBUTE19 IN VARCHAR2,
576 P_ATTRIBUTE20 IN VARCHAR2,
577 X_OWNER IN VARCHAR2)
578
579 IS
580
581 begin
582
583 declare
584 user_id number := 0;
585 row_id varchar2(64);
586 l_TEMPLATE_id NUMBER := p_TEMPLATE_id;
587
588 begin
589
590 if (X_OWNER = 'SEED') then
591 user_id := -1;
592 end if;
593
594 ASO_SUP_TEMPLATE_PKG.UPDATE_ROW (
595 P_TEMPLATE_ID => P_TEMPLATE_ID,
596 P_LAST_UPDATE_DATE => sysdate,
597 P_LAST_UPDATED_BY => user_id,
598 P_LAST_UPDATE_LOGIN => 0,
599 P_TEMPLATE_NAME => P_TEMPLATE_NAME,
600 P_DESCRIPTION => P_DESCRIPTION,
601 P_TEMPLATE_LEVEL => P_TEMPLATE_LEVEL,
602 P_TEMPLATE_CONTEXT => P_TEMPLATE_CONTEXT,
603 p_context => P_context,
604 P_ATTRIBUTE1 => P_ATTRIBUTE1,
605 P_ATTRIBUTE2 => P_ATTRIBUTE2,
606 P_ATTRIBUTE3 => P_ATTRIBUTE3,
607 P_ATTRIBUTE4 => P_ATTRIBUTE4,
608 P_ATTRIBUTE5 => P_ATTRIBUTE5,
609 P_ATTRIBUTE6 => P_ATTRIBUTE6,
610 P_ATTRIBUTE7 => P_ATTRIBUTE7,
611 P_ATTRIBUTE8 => P_ATTRIBUTE8,
612 P_ATTRIBUTE9 => P_ATTRIBUTE9,
613 P_ATTRIBUTE10 => P_ATTRIBUTE10,
614 P_ATTRIBUTE11 => P_ATTRIBUTE11,
615 P_ATTRIBUTE12 => P_ATTRIBUTE12,
616 P_ATTRIBUTE13 => P_ATTRIBUTE13,
617 P_ATTRIBUTE14 => P_ATTRIBUTE14,
618 P_ATTRIBUTE15 => P_ATTRIBUTE15,
619 P_ATTRIBUTE16 => P_ATTRIBUTE16,
620 P_ATTRIBUTE17 => P_ATTRIBUTE17,
621 P_ATTRIBUTE18 => P_ATTRIBUTE18,
622 P_ATTRIBUTE19 => P_ATTRIBUTE19,
623 P_ATTRIBUTE20 => P_ATTRIBUTE20
624 );
625
626 exception
627
628 when NO_DATA_FOUND then
629
630 ASO_SUP_TEMPLATE_PKG.INSERT_ROW (
631 PX_ROWID => row_id,
632 PX_TEMPLATE_ID => L_TEMPLATE_ID,
633 P_CREATION_DATE => sysdate,
634 P_CREATED_BY => user_id,
635 P_LAST_UPDATE_DATE => sysdate,
636 P_LAST_UPDATED_BY => user_id,
637 P_LAST_UPDATE_LOGIN => 0,
638 P_TEMPLATE_NAME => P_TEMPLATE_NAME,
639 P_DESCRIPTION => P_DESCRIPTION,
640 P_TEMPLATE_LEVEL => P_TEMPLATE_LEVEL,
641 P_TEMPLATE_CONTEXT => P_TEMPLATE_CONTEXT,
642 p_context => P_context,
643 P_ATTRIBUTE1 => P_ATTRIBUTE1,
644 P_ATTRIBUTE2 => P_ATTRIBUTE2,
645 P_ATTRIBUTE3 => P_ATTRIBUTE3,
646 P_ATTRIBUTE4 => P_ATTRIBUTE4,
647 P_ATTRIBUTE5 => P_ATTRIBUTE5,
648 P_ATTRIBUTE6 => P_ATTRIBUTE6,
649 P_ATTRIBUTE7 => P_ATTRIBUTE7,
650 P_ATTRIBUTE8 => P_ATTRIBUTE8,
651 P_ATTRIBUTE9 => P_ATTRIBUTE9,
652 P_ATTRIBUTE10 => P_ATTRIBUTE10,
653 P_ATTRIBUTE11 => P_ATTRIBUTE11,
654 P_ATTRIBUTE12 => P_ATTRIBUTE12,
655 P_ATTRIBUTE13 => P_ATTRIBUTE13,
656 P_ATTRIBUTE14 => P_ATTRIBUTE14,
657 P_ATTRIBUTE15 => P_ATTRIBUTE15,
658 P_ATTRIBUTE16 => P_ATTRIBUTE16,
659 P_ATTRIBUTE17 => P_ATTRIBUTE17,
660 P_ATTRIBUTE18 => P_ATTRIBUTE18,
661 P_ATTRIBUTE19 => P_ATTRIBUTE19,
662 P_ATTRIBUTE20 => P_ATTRIBUTE20
663 );
664 end;
665
666 end LOAD_ROW;
667
668 /* Translation procedure */
669
670 procedure TRANSLATE_ROW (
671 P_TEMPLATE_ID IN NUMBER,
672 P_TEMPLATE_NAME IN VARCHAR2,
673 P_DESCRIPTION in VARCHAR2,
674 X_OWNER in VARCHAR2)
675
676 IS
677
678 begin
679
680 -- only update rows that have not been altered by user
681
682 update ASO_SUP_TEMPLATE_TL
686 last_update_date = sysdate,
683 set TEMPLATE_NAME = P_TEMPLATE_NAME,
684 DESCRIPTION = P_DESCRIPTION,
685 source_lang = userenv('LANG'),
687 last_updated_by = fnd_load_util.owner_id(X_OWNER),
688 last_update_login = 0
689 where TEMPLATE_ID = P_TEMPLATE_ID
690 and userenv('LANG') in (language, source_lang);
691
692 end TRANSLATE_ROW;
693
694 Procedure LOAD_SEED_ROW (
695 PX_TEMPLATE_ID IN NUMBER ,
696 P_TEMPLATE_NAME IN VARCHAR2,
697 P_DESCRIPTION IN VARCHAR2,
698 P_TEMPLATE_LEVEL IN VARCHAR2,
699 P_TEMPLATE_CONTEXT IN VARCHAR2,
700 p_context IN VARCHAR2,
701 P_OWNER IN VARCHAR2,
702 P_UPLOAD_MODE IN VARCHAR2,
703 P_ATTRIBUTE1 IN VARCHAR2,
704 P_ATTRIBUTE2 IN VARCHAR2,
705 P_ATTRIBUTE3 IN VARCHAR2,
706 P_ATTRIBUTE4 IN VARCHAR2,
707 P_ATTRIBUTE5 IN VARCHAR2,
708 P_ATTRIBUTE6 IN VARCHAR2,
709 P_ATTRIBUTE7 IN VARCHAR2,
710 P_ATTRIBUTE8 IN VARCHAR2,
711 P_ATTRIBUTE9 IN VARCHAR2,
712 P_ATTRIBUTE10 IN VARCHAR2,
713 P_ATTRIBUTE11 IN VARCHAR2,
714 P_ATTRIBUTE12 IN VARCHAR2,
715 P_ATTRIBUTE13 IN VARCHAR2,
716 P_ATTRIBUTE14 IN VARCHAR2,
717 P_ATTRIBUTE15 IN VARCHAR2,
718 P_ATTRIBUTE16 IN VARCHAR2,
719 P_ATTRIBUTE17 IN VARCHAR2,
720 P_ATTRIBUTE18 IN VARCHAR2,
721 P_ATTRIBUTE19 IN VARCHAR2,
722 P_ATTRIBUTE20 IN VARCHAR2
723 )
724 is
725 l_template_id NUMBER;
726 l_template_scope_id NUMBER;
727 row_id VARCHAR2(32767);
728 row_id1 VARCHAR2(32767);
729
730 cursor get_templates is
731 SELECT template_id
732 FROM ASO_SUP_TEMPLATE_tl
733 WHERE TEMPLATE_ID = PX_TEMPLATE_ID;
734 BEGIN
735 if (P_UPLOAD_MODE = 'NLS') then
736
737 ASO_SUP_TEMPLATE_PKG.TRANSLATE_ROW (
738 P_TEMPLATE_ID => PX_TEMPLATE_ID ,
739 P_TEMPLATE_NAME => P_TEMPLATE_NAME,
740 P_DESCRIPTION => P_DESCRIPTION,
741 X_OWNER => P_OWNER);
742
743 else
744
745 if (fnd_load_util.owner_id(P_OWNER) = 120 ) then
746
747 open get_templates;
748 loop
749 fetch get_templates into l_template_id;
750 if get_templates%FOUND THEN
751 -- this means template already exists
752 ASO_SUP_TEMPLATE_PKG.UPDATE_ROW (
753 P_TEMPLATE_ID => PX_TEMPLATE_ID,
754 P_LAST_UPDATE_DATE => sysdate ,
755 P_LAST_UPDATED_BY => fnd_load_util.owner_id(P_OWNER) ,
756 P_LAST_UPDATE_LOGIN => fnd_load_util.owner_id(P_OWNER) ,
757 P_TEMPLATE_NAME => P_TEMPLATE_NAME,
758 P_DESCRIPTION => P_DESCRIPTION,
759 P_TEMPLATE_LEVEL => P_TEMPLATE_LEVEL,
760 P_TEMPLATE_CONTEXT => P_TEMPLATE_CONTEXT,
761 p_context => p_context,
762 P_ATTRIBUTE1 => P_ATTRIBUTE1,
763 P_ATTRIBUTE2 => P_ATTRIBUTE2,
764 P_ATTRIBUTE3 => P_ATTRIBUTE3,
765 P_ATTRIBUTE4 => P_ATTRIBUTE4,
766 P_ATTRIBUTE5 => P_ATTRIBUTE5,
767 P_ATTRIBUTE6 => P_ATTRIBUTE6,
768 P_ATTRIBUTE7 => P_ATTRIBUTE7,
769 P_ATTRIBUTE8 => P_ATTRIBUTE8,
770 P_ATTRIBUTE9 => P_ATTRIBUTE9,
771 P_ATTRIBUTE10 => P_ATTRIBUTE10,
772 P_ATTRIBUTE11 => P_ATTRIBUTE11,
773 P_ATTRIBUTE12 => P_ATTRIBUTE12,
774 P_ATTRIBUTE13 => P_ATTRIBUTE13,
775 P_ATTRIBUTE14 => P_ATTRIBUTE14,
776 P_ATTRIBUTE15 => P_ATTRIBUTE15,
777 P_ATTRIBUTE16 => P_ATTRIBUTE16,
778 P_ATTRIBUTE17 => P_ATTRIBUTE17,
779 P_ATTRIBUTE18 => P_ATTRIBUTE18,
780 P_ATTRIBUTE19 => P_ATTRIBUTE19,
781 P_ATTRIBUTE20 => P_ATTRIBUTE20
782 );
783 exit;
784 elsif get_templates%NOTFOUND THEN
785 -- this means this is a new template
786 l_TEMPLATE_ID := PX_TEMPLATE_ID;
787 ASO_SUP_TEMPLATE_PKG.INSERT_ROW (
788 PX_ROWID => row_id,
789 PX_TEMPLATE_ID => l_TEMPLATE_ID,
790 P_CREATION_DATE => sysdate ,
791 P_CREATED_BY => fnd_load_util.owner_id(P_OWNER) ,
792 P_LAST_UPDATE_DATE => sysdate ,
793 P_LAST_UPDATED_BY => fnd_load_util.owner_id(P_OWNER) ,
794 P_LAST_UPDATE_LOGIN => fnd_load_util.owner_id(P_OWNER) ,
795 P_TEMPLATE_NAME => P_TEMPLATE_NAME,
796 P_DESCRIPTION => P_DESCRIPTION,
797 P_TEMPLATE_LEVEL => P_TEMPLATE_LEVEL,
798 P_TEMPLATE_CONTEXT => P_TEMPLATE_CONTEXT,
799 p_context => p_context,
800 P_ATTRIBUTE1 => P_ATTRIBUTE1,
801 P_ATTRIBUTE2 => P_ATTRIBUTE2,
802 P_ATTRIBUTE3 => P_ATTRIBUTE3,
803 P_ATTRIBUTE4 => P_ATTRIBUTE4,
804 P_ATTRIBUTE5 => P_ATTRIBUTE5,
805 P_ATTRIBUTE6 => P_ATTRIBUTE6,
806 P_ATTRIBUTE7 => P_ATTRIBUTE7,
807 P_ATTRIBUTE8 => P_ATTRIBUTE8,
808 P_ATTRIBUTE9 => P_ATTRIBUTE9,
809 P_ATTRIBUTE10 => P_ATTRIBUTE10,
810 P_ATTRIBUTE11 => P_ATTRIBUTE11,
811 P_ATTRIBUTE12 => P_ATTRIBUTE12,
812 P_ATTRIBUTE13 => P_ATTRIBUTE13,
813 P_ATTRIBUTE14 => P_ATTRIBUTE14,
814 P_ATTRIBUTE15 => P_ATTRIBUTE15,
815 P_ATTRIBUTE16 => P_ATTRIBUTE16,
816 P_ATTRIBUTE17 => P_ATTRIBUTE17,
817 P_ATTRIBUTE18 => P_ATTRIBUTE18,
818 P_ATTRIBUTE19 => P_ATTRIBUTE19,
819 P_ATTRIBUTE20 => P_ATTRIBUTE20
820 );
821 exit;
822 end if;
823 end loop;
824 close get_templates;
825 end if;
826 end if; -- end if the NLS check
827 END LOAD_SEED_ROW;
828 END; -- Package Body ASO_SUP_TEMPLATE_PKG