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