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