[Home] [Help]
PACKAGE BODY: APPS.ASO_SUP_RESPONSE_PKG
Source
1 Package Body ASO_SUP_RESPONSE_PKG AS
2 /* $Header: asospreb.pls 120.4 2006/05/22 22:59:13 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_RESPONSE_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_RESPONSE_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_response_B
45 where RESPONSE_ID = PX_RESPONSE_ID ;
46
47 cursor CU_RESPONSE_ID IS
48 select aso_sup_response_B_S.NEXTVAL from sys.dual;
49
50 Begin
51
52 IF (PX_RESPONSE_ID IS NULL) OR (PX_RESPONSE_ID = FND_API.G_MISS_NUM) THEN
53 OPEN CU_RESPONSE_ID;
54 FETCH CU_RESPONSE_ID INTO PX_RESPONSE_ID;
55 CLOSE CU_RESPONSE_ID;
56
57 END IF;
58
59 insert into ASO_SUP_RESPONSE_B (
60 RESPONSE_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_RESPONSE_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_RESPONSE_TL (
119 RESPONSE_ID,
120 LANGUAGE,
121 SOURCE_LANG,
122 RESPONSE_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_RESPONSE_ID,
131 L.LANGUAGE_CODE,
132 userenv('LANG'),
133 P_RESPONSE_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_RESPONSE_TL T
145 where T.RESPONSE_ID = PX_RESPONSE_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_RESPONSE_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_RESPONSE_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 IS
191
192 Begin
193
194 update ASO_SUP_RESPONSE_B
195 set
196 last_updated_by = P_last_updated_by,
197 last_update_date = P_last_update_date,
198 last_update_login = P_last_update_login,
199 context = P_context,
200 ATTRIBUTE1 = P_ATTRIBUTE1,
201 ATTRIBUTE2 = P_ATTRIBUTE2,
202 ATTRIBUTE3 = P_ATTRIBUTE3,
203 ATTRIBUTE4 = P_ATTRIBUTE4,
204 ATTRIBUTE5 = P_ATTRIBUTE5,
205 ATTRIBUTE6 = P_ATTRIBUTE6,
206 ATTRIBUTE7 = P_ATTRIBUTE7,
207 ATTRIBUTE8 = P_ATTRIBUTE8,
208 ATTRIBUTE9 = P_ATTRIBUTE9,
209 ATTRIBUTE10 = P_ATTRIBUTE10,
210 ATTRIBUTE11 = P_ATTRIBUTE11,
211 ATTRIBUTE12 = P_ATTRIBUTE12,
212 ATTRIBUTE13 = P_ATTRIBUTE13,
213 ATTRIBUTE14 = P_ATTRIBUTE14,
214 ATTRIBUTE15 = P_ATTRIBUTE15,
215 ATTRIBUTE16 = P_ATTRIBUTE16,
216 ATTRIBUTE17 = P_ATTRIBUTE17,
217 ATTRIBUTE18 = P_ATTRIBUTE18,
218 ATTRIBUTE19 = P_ATTRIBUTE19,
219 ATTRIBUTE20 = P_ATTRIBUTE20
220 where RESPONSE_ID = P_RESPONSE_ID;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225
226 update ASO_SUP_RESPONSE_TL
227 set
228 RESPONSE_NAME = P_RESPONSE_NAME,
229 DESCRIPTION = P_DESCRIPTION,
230 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
231 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
233 SOURCE_LANG = userenv('LANG')
234 where RESPONSE_ID = P_RESPONSE_ID
235 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240
241
242 End UPDATE_ROW;
243
244
245 procedure DELETE_ROW (
246 P_RESPONSE_ID IN NUMBER
247
248 )
249
250 IS
251
252 Begin
253
254 delete from ASO_SUP_RESPONSE_TL
255 where RESPONSE_ID = P_RESPONSE_ID;
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260
261
262 delete from ASO_SUP_RESPONSE_B
263 where RESPONSE_ID = P_RESPONSE_ID;
264
265 if (sql%notfound) then
266 raise no_data_found;
267 end if;
268
269 End Delete_row;
270
271 PROCEDURE LOCK_ROW
272 (
273 P_RESPONSE_ID IN NUMBER,
274 P_created_by IN NUMBER ,
275 P_creation_date IN DATE ,
276 P_last_updated_by IN NUMBER,
277 P_last_update_date IN DATE,
278 P_last_update_login IN NUMBER,
279 P_RESPONSE_NAME IN VARCHAR2,
280 P_DESCRIPTION IN VARCHAR2,
281 P_CONTEXT IN VARCHAR2,
282 P_ATTRIBUTE1 IN VARCHAR2,
283 P_ATTRIBUTE2 IN VARCHAR2,
284 P_ATTRIBUTE3 IN VARCHAR2,
285 P_ATTRIBUTE4 IN VARCHAR2,
286 P_ATTRIBUTE5 IN VARCHAR2,
287 P_ATTRIBUTE6 IN VARCHAR2,
288 P_ATTRIBUTE7 IN VARCHAR2,
289 P_ATTRIBUTE8 IN VARCHAR2,
290 P_ATTRIBUTE9 IN VARCHAR2,
291 P_ATTRIBUTE10 IN VARCHAR2,
292 P_ATTRIBUTE11 IN VARCHAR2,
293 P_ATTRIBUTE12 IN VARCHAR2,
294 P_ATTRIBUTE13 IN VARCHAR2,
295 P_ATTRIBUTE14 IN VARCHAR2,
296 P_ATTRIBUTE15 IN VARCHAR2
297
298
299 )
300
301 IS
302
303 CURSOR i_csr is
304 SELECT
305 a.RESPONSE_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_RESPONSE_B a
329 where a.RESPONSE_ID = P_RESPONSE_ID
330 for update of a.RESPONSE_ID nowait;
331
332 recinfo i_csr%rowtype;
333
334 cursor c1 is
335 select
336 RESPONSE_NAME,
337 DESCRIPTION,
338 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
339 from ASO_SUP_RESPONSE_TL
340 where RESPONSE_ID = P_RESPONSE_ID
341 for update of RESPONSE_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_RESPONSE_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.RESPONSE_ID = P_RESPONSE_ID)
370 OR ((recinfo.RESPONSE_ID is null) AND (P_RESPONSE_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
421 if (tlinfo.BASELANG = 'Y') then
422 if ( ((tlinfo.RESPONSE_NAME = P_RESPONSE_NAME)
423 OR ((tlinfo.RESPONSE_NAME is null) AND (P_RESPONSE_NAME is null)))
424 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
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_RESPONSE_TL T
448 where not exists
449 ( select NULL
450 from ASO_SUP_RESPONSE_B B
451 where B.RESPONSE_ID = T.RESPONSE_ID
452 );
453
454 update ASO_SUP_RESPONSE_TL T set (
455 RESPONSE_NAME,
456 DESCRIPTION
457 ) = ( select
458 B.RESPONSE_NAME,
459 B.DESCRIPTION
460 from ASO_SUP_RESPONSE_TL B
461 where B.RESPONSE_ID = T.RESPONSE_ID
462 and B.LANGUAGE = T.SOURCE_LANG )
463 where (
464 T.RESPONSE_ID,
465 T.LANGUAGE
466 ) in ( select
467 SUBT.RESPONSE_ID,
468 SUBT.LANGUAGE
469 from ASO_SUP_RESPONSE_TL SUBB,
470 ASO_SUP_RESPONSE_TL SUBT
471 where SUBB.RESPONSE_ID = SUBT.RESPONSE_ID
472 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
473 and ( SUBB.RESPONSE_NAME <> SUBT.RESPONSE_NAME
474 or ( SUBB.RESPONSE_NAME is null and SUBT.RESPONSE_NAME is not null )
475 or ( SUBB.RESPONSE_NAME is not null and SUBT.RESPONSE_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_RESPONSE_TL (
483 RESPONSE_ID,
484 LANGUAGE,
485 SOURCE_LANG,
486 RESPONSE_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.RESPONSE_ID,
495 L.LANGUAGE_CODE,
496 B.SOURCE_LANG,
497 B.RESPONSE_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_RESPONSE_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_RESPONSE_TL T
511 where T.RESPONSE_ID = B.RESPONSE_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_RESPONSE_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_RESPONSE_NAME IN VARCHAR2,
527 P_DESCRIPTION IN VARCHAR2 := NULL,
528 P_CONTEXT IN VARCHAR2 := NULL,
529 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
530 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
531 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
532 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
533 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
534 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
535 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
536 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
537 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
538 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
539 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
540 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
541 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
542 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
543 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
544 P_ATTRIBUTE16 IN VARCHAR2 := NULL,
545 P_ATTRIBUTE17 IN VARCHAR2 := NULL,
546 P_ATTRIBUTE18 IN VARCHAR2 := NULL,
547 P_ATTRIBUTE19 IN VARCHAR2 := NULL,
548 P_ATTRIBUTE20 IN VARCHAR2 := NULL,
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_RESPONSE_id NUMBER := p_RESPONSE_id;
559
560 begin
561
562 if (X_OWNER = 'SEED') then
563 user_id := -1;
564 end if;
565
566 ASO_SUP_RESPONSE_PKG.UPDATE_ROW (
567 P_RESPONSE_ID => P_RESPONSE_ID,
568 P_LAST_UPDATE_DATE => sysdate,
569 P_LAST_UPDATED_BY => user_id,
570 P_LAST_UPDATE_LOGIN => 0,
571 P_RESPONSE_NAME => P_RESPONSE_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,
585 P_ATTRIBUTE12 => P_ATTRIBUTE12,
586 P_ATTRIBUTE13 => P_ATTRIBUTE13,
587 P_ATTRIBUTE14 => P_ATTRIBUTE14,
588 P_ATTRIBUTE15 => P_ATTRIBUTE15,
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_RESPONSE_PKG.INSERT_ROW (
601 PX_ROWID => row_id,
602 PX_RESPONSE_ID => L_RESPONSE_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_RESPONSE_NAME => P_RESPONSE_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_RESPONSE_ID IN NUMBER,
640 P_RESPONSE_NAME IN VARCHAR2,
641 P_DESCRIPTION in VARCHAR2,
642 X_OWNER in VARCHAR2)
643
644 IS
645
646 l_user_id number := 0;
647
648 begin
649
650 l_user_id := fnd_load_util.owner_id(X_OWNER);
651
652 update ASO_SUP_RESPONSE_TL
653 set RESPONSE_NAME = P_RESPONSE_NAME,
654 DESCRIPTION = P_DESCRIPTION,
655 source_lang = userenv('LANG'),
656 last_update_date = sysdate,
657 last_updated_by = l_user_id,
658 last_update_login = 0
659 where RESPONSE_ID = P_RESPONSE_ID
660 and userenv('LANG') in (language, source_lang);
661
662 end TRANSLATE_ROW;
663
664 PROCEDURE LOAD_SEED_ROW (
665 P_RESPONSE_ID IN NUMBER,
666 P_RESPONSE_NAME IN VARCHAR2,
667 P_DESCRIPTION IN VARCHAR2,
668 P_COMPONENT_ID IN NUMBER,
669 P_RESP_COMPONENT_ID IN NUMBER,
670 P_RESP_DISPLAY_SEQUENCE IN NUMBER,
671 p_context IN VARCHAR2,
672 P_OWNER IN VARCHAR2,
673 P_UPLOAD_MODE IN VARCHAR2,
674 P_ATTRIBUTE1 IN VARCHAR2,
675 P_ATTRIBUTE2 IN VARCHAR2,
676 P_ATTRIBUTE3 IN VARCHAR2,
677 P_ATTRIBUTE4 IN VARCHAR2,
678 P_ATTRIBUTE5 IN VARCHAR2,
679 P_ATTRIBUTE6 IN VARCHAR2,
680 P_ATTRIBUTE7 IN VARCHAR2,
681 P_ATTRIBUTE8 IN VARCHAR2,
682 P_ATTRIBUTE9 IN VARCHAR2,
683 P_ATTRIBUTE10 IN VARCHAR2,
684 P_ATTRIBUTE11 IN VARCHAR2,
685 P_ATTRIBUTE12 IN VARCHAR2,
686 P_ATTRIBUTE13 IN VARCHAR2,
687 P_ATTRIBUTE14 IN VARCHAR2,
688 P_ATTRIBUTE15 IN VARCHAR2,
689 P_ATTRIBUTE16 IN VARCHAR2,
690 P_ATTRIBUTE17 IN VARCHAR2,
691 P_ATTRIBUTE18 IN VARCHAR2,
692 P_ATTRIBUTE19 IN VARCHAR2,
693 P_ATTRIBUTE20 IN VARCHAR2
694 )
695 IS
696 l_user_id number := 0;
697 l_RESPONSE_ID NUMBER;
698 l_COMPONENT_RESPONSE_ID NUMBER;
699 row_id VARCHAR2(32767);
700 row_id1 VARCHAR2(32767);
701
702 cursor get_response_id is
703 SELECT RESPONSE_ID
704 FROM ASO_SUP_RESPONSE_TL
705 WHERE RESPONSE_ID = p_RESPONSE_ID;
706
707 cursor get_mappings is
708 select component_response_ID
709 from aso_sup_comp_resp_map
710 where component_id = P_RESP_COMPONENT_ID
711 and response_id = P_RESPONSE_ID;
712
713 begin
714 if (P_UPLOAD_MODE = 'NLS') then
715 ASO_SUP_RESPONSE_PKG.TRANSLATE_ROW (
716 P_RESPONSE_ID => P_RESPONSE_ID,
717 P_RESPONSE_NAME => P_RESPONSE_NAME,
718 P_DESCRIPTION => P_DESCRIPTION,
719 X_OWNER => P_OWNER);
720
721 else
722 if ( fnd_load_util.owner_id(P_OWNER) = 120 ) then
723
724 l_user_id := fnd_load_util.owner_id(P_OWNER);
725 open get_response_id;
726 loop
727 fetch get_response_id into l_RESPONSE_ID;
728 if get_response_id%FOUND THEN
729 -- THIS MEANS RESPONSE ALREADY EXISTS
730 --WHERE RESPONSE_NAME like P_RESPONSE_NAME;
731 ASO_SUP_RESPONSE_PKG.UPDATE_ROW (
732 P_RESPONSE_ID => P_RESPONSE_ID,
733 P_LAST_UPDATE_DATE => sysdate ,
734 P_LAST_UPDATED_BY => l_user_id ,
735 P_LAST_UPDATE_LOGIN => l_user_id ,
736 P_RESPONSE_NAME => P_RESPONSE_NAME,
737 P_DESCRIPTION => P_DESCRIPTION,
738 p_context => P_CONTEXT,
739 P_ATTRIBUTE1 => P_ATTRIBUTE1,
740 P_ATTRIBUTE2 => P_ATTRIBUTE2,
741 P_ATTRIBUTE3 => P_ATTRIBUTE3,
742 P_ATTRIBUTE4 => P_ATTRIBUTE4,
743 P_ATTRIBUTE5 => P_ATTRIBUTE5,
744 P_ATTRIBUTE6 => P_ATTRIBUTE6,
745 P_ATTRIBUTE7 => P_ATTRIBUTE7,
746 P_ATTRIBUTE8 => P_ATTRIBUTE8,
747 P_ATTRIBUTE9 => P_ATTRIBUTE9,
748 P_ATTRIBUTE10 => P_ATTRIBUTE10,
749 P_ATTRIBUTE11 => P_ATTRIBUTE11,
750 P_ATTRIBUTE12 => P_ATTRIBUTE12,
751 P_ATTRIBUTE13 => P_ATTRIBUTE13,
752 P_ATTRIBUTE14 => P_ATTRIBUTE14,
753 P_ATTRIBUTE15 => P_ATTRIBUTE15,
754 P_ATTRIBUTE16 => P_ATTRIBUTE16,
755 P_ATTRIBUTE17 => P_ATTRIBUTE17,
756 P_ATTRIBUTE18 => P_ATTRIBUTE18,
757 P_ATTRIBUTE19 => P_ATTRIBUTE19,
758 P_ATTRIBUTE20 => P_ATTRIBUTE20
759 );
760 exit;
761 elsif get_response_id%NOTFOUND THEN
762 -- this means this is a new response
763 L_RESPONSE_ID := P_RESPONSE_ID;
764 ASO_SUP_RESPONSE_PKG.INSERT_ROW (
765 PX_ROWID => row_id,
766 PX_RESPONSE_ID => L_RESPONSE_ID,
767 P_CREATION_DATE => sysdate ,
768 P_CREATED_BY => l_user_id ,
769 P_LAST_UPDATE_DATE => sysdate ,
770 P_LAST_UPDATED_BY => l_user_id ,
771 P_LAST_UPDATE_LOGIN => l_user_id ,
772 P_RESPONSE_NAME => P_RESPONSE_NAME,
773 P_DESCRIPTION => P_DESCRIPTION,
774 p_context => P_CONTEXT,
775 P_ATTRIBUTE1 => P_ATTRIBUTE1,
776 P_ATTRIBUTE2 => P_ATTRIBUTE2,
777 P_ATTRIBUTE3 => P_ATTRIBUTE3,
778 P_ATTRIBUTE4 => P_ATTRIBUTE4,
779 P_ATTRIBUTE5 => P_ATTRIBUTE5,
780 P_ATTRIBUTE6 => P_ATTRIBUTE6,
781 P_ATTRIBUTE7 => P_ATTRIBUTE7,
782 P_ATTRIBUTE8 => P_ATTRIBUTE8,
783 P_ATTRIBUTE9 => P_ATTRIBUTE9,
784 P_ATTRIBUTE10 => P_ATTRIBUTE10,
785 P_ATTRIBUTE11 => P_ATTRIBUTE11,
786 P_ATTRIBUTE12 => P_ATTRIBUTE12,
787 P_ATTRIBUTE13 => P_ATTRIBUTE13,
788 P_ATTRIBUTE14 => P_ATTRIBUTE14,
789 P_ATTRIBUTE15 => P_ATTRIBUTE15,
790 P_ATTRIBUTE16 => P_ATTRIBUTE16,
791 P_ATTRIBUTE17 => P_ATTRIBUTE17,
792 P_ATTRIBUTE18 => P_ATTRIBUTE18,
793 P_ATTRIBUTE19 => P_ATTRIBUTE19,
794 P_ATTRIBUTE20 => P_ATTRIBUTE20
795 );
796 exit;
797 END IF;
798 end loop;
799 close get_response_id;
800
801 open get_mappings;
802 loop
803 fetch get_mappings into l_component_response_ID;
804 if get_mappings%FOUND THEN
805 -- THIS MEANS RESPONSE has already been used for that component
806 ASO_SUP_COMP_RESP_MAP_PKG.UPDATE_ROW (
807 P_COMPONENT_RESPONSE_ID => l_COMPONENT_RESPONSE_ID,
808 P_LAST_UPDATE_DATE => sysdate ,
809 P_LAST_UPDATED_BY => l_user_id ,
810 P_LAST_UPDATE_LOGIN => l_user_id ,
811 P_COMPONENT_ID => P_RESP_COMPONENT_ID,
812 P_RESPONSE_ID => P_RESPONSE_ID ,
813 P_DISPLAY_SEQUENCE => P_RESP_DISPLAY_SEQUENCE,
814 p_context => P_CONTEXT,
815 P_ATTRIBUTE1 => P_ATTRIBUTE1,
816 P_ATTRIBUTE2 => P_ATTRIBUTE2,
817 P_ATTRIBUTE3 => P_ATTRIBUTE3,
818 P_ATTRIBUTE4 => P_ATTRIBUTE4,
819 P_ATTRIBUTE5 => P_ATTRIBUTE5,
820 P_ATTRIBUTE6 => P_ATTRIBUTE6,
821 P_ATTRIBUTE7 => P_ATTRIBUTE7,
822 P_ATTRIBUTE8 => P_ATTRIBUTE8,
823 P_ATTRIBUTE9 => P_ATTRIBUTE9,
824 P_ATTRIBUTE10 => P_ATTRIBUTE10,
825 P_ATTRIBUTE11 => P_ATTRIBUTE11,
826 P_ATTRIBUTE12 => P_ATTRIBUTE12,
827 P_ATTRIBUTE13 => P_ATTRIBUTE13,
828 P_ATTRIBUTE14 => P_ATTRIBUTE14,
829 P_ATTRIBUTE15 => P_ATTRIBUTE15,
830 P_ATTRIBUTE16 => P_ATTRIBUTE16,
831 P_ATTRIBUTE17 => P_ATTRIBUTE17,
832 P_ATTRIBUTE18 => P_ATTRIBUTE18,
833 P_ATTRIBUTE19 => P_ATTRIBUTE19,
834 P_ATTRIBUTE20 => P_ATTRIBUTE20
835 );
836 exit;
837 elsif get_mappings%NOTFOUND THEN
838 -- THIS MEANS RESPONSE has not been used for that component
839 ASO_SUP_COMP_RESP_MAP_PKG.INSERT_ROW (
840 PX_ROWID => row_id1,
841 PX_COMPONENT_RESPONSE_ID => l_COMPONENT_RESPONSE_ID,
842 P_CREATION_DATE => sysdate ,
843 P_CREATED_BY => l_user_id ,
844 P_LAST_UPDATE_DATE => sysdate ,
845 P_LAST_UPDATED_BY => l_user_id ,
846 P_LAST_UPDATE_LOGIN => l_user_id ,
847 P_COMPONENT_ID => P_RESP_COMPONENT_ID,
848 P_RESPONSE_ID => P_RESPONSE_ID ,
849 P_DISPLAY_SEQUENCE => P_RESP_DISPLAY_SEQUENCE,
850 p_context => P_CONTEXT,
851 P_ATTRIBUTE1 => P_ATTRIBUTE1,
852 P_ATTRIBUTE2 => P_ATTRIBUTE2,
853 P_ATTRIBUTE3 => P_ATTRIBUTE3,
854 P_ATTRIBUTE4 => P_ATTRIBUTE4,
855 P_ATTRIBUTE5 => P_ATTRIBUTE5,
856 P_ATTRIBUTE6 => P_ATTRIBUTE6,
857 P_ATTRIBUTE7 => P_ATTRIBUTE7,
858 P_ATTRIBUTE8 => P_ATTRIBUTE8,
859 P_ATTRIBUTE9 => P_ATTRIBUTE9,
860 P_ATTRIBUTE10 => P_ATTRIBUTE10,
861 P_ATTRIBUTE11 => P_ATTRIBUTE11,
862 P_ATTRIBUTE12 => P_ATTRIBUTE12,
863 P_ATTRIBUTE13 => P_ATTRIBUTE13,
864 P_ATTRIBUTE14 => P_ATTRIBUTE14,
865 P_ATTRIBUTE15 => P_ATTRIBUTE15,
866 P_ATTRIBUTE16 => P_ATTRIBUTE16,
867 P_ATTRIBUTE17 => P_ATTRIBUTE17,
868 P_ATTRIBUTE18 => P_ATTRIBUTE18,
869 P_ATTRIBUTE19 => P_ATTRIBUTE19,
870 P_ATTRIBUTE20 => P_ATTRIBUTE20
871 );
872 exit;
873 END IF;
874 end loop;
875 close get_mappings;
876
877 end if;
878 end if; -- end if for the NLS condition check
879 END LOAD_SEED_ROW;
880
881 END; -- Package Body ASO_SUP_RESPONSE_PKG