[Home] [Help]
PACKAGE BODY: APPS.ICX_QUESTIONS_PKG
Source
1 package body ICX_QUESTIONS_PKG as
2 /* $Header: ICXQUESB.pls 115.3 1999/11/12 15:35:18 pkm ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out VARCHAR2,
6 X_QUESTION_CODE in VARCHAR2,
7 X_APPLICATION_ID in NUMBER,
8 X_TYPE in VARCHAR2,
9 X_QUESTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from ICX_QUESTIONS
17 where QUESTION_CODE = X_QUESTION_CODE;
18
19 begin
20 insert into ICX_QUESTIONS (
21 QUESTION_CODE,
22 APPLICATION_ID,
23 TYPE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_QUESTION_CODE,
31 X_APPLICATION_ID,
32 X_TYPE,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN
38 );
39
40 insert into ICX_QUESTIONS_TL (
41 QUESTION_CODE,
42 QUESTION,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATE_LOGIN,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_QUESTION_CODE,
52 X_QUESTION,
53 X_CREATED_BY,
54 X_CREATION_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATE_LOGIN,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG IN ('I','B')
62 and not exists
63 (select NULL
64 from ICX_QUESTIONS_TL T
65 where T.QUESTION_CODE = X_QUESTION_CODE
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75 end INSERT_ROW;
76
77 procedure UPDATE_ROW (
78 X_QUESTION_CODE in VARCHAR2,
79 X_APPLICATION_ID in NUMBER,
80 X_TYPE in VARCHAR2,
81 X_QUESTION in VARCHAR2,
82 X_LAST_UPDATE_DATE in DATE,
83 X_LAST_UPDATED_BY in NUMBER,
84 X_LAST_UPDATE_LOGIN in NUMBER
85 ) is
86 begin
87 update ICX_QUESTIONS set
88 APPLICATION_ID = X_APPLICATION_ID,
89 TYPE = X_TYPE,
90 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
91 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
93 where QUESTION_CODE = X_QUESTION_CODE;
94
95 if (sql%notfound) then
96 raise no_data_found;
97 end if;
98
99 update ICX_QUESTIONS_TL set
100 QUESTION = X_QUESTION,
101 SOURCE_LANG = userenv('LANG'),
102 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
103 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
104 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
105 where QUESTION_CODE = X_QUESTION_CODE
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111 end UPDATE_ROW;
112
113 procedure TRANSLATE_ROW (
114 X_QUESTION_CODE in VARCHAR2,
115 X_OWNER in VARCHAR2,
116 X_QUESTION in VARCHAR2) is
117 begin
118
119 update ICX_QUESTIONS_tl set
120 question = X_QUESTION,
121 SOURCE_LANG = userenv('LANG'),
122 last_update_date = sysdate,
123 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
124 last_update_login = 0
125 where question_code = X_QUESTION_CODE
126 and userenv('LANG') in (language, source_lang);
127
128 end TRANSLATE_ROW;
129
130 procedure LOAD_ROW (
131 X_QUESTION_CODE in VARCHAR2,
132 X_OWNER in VARCHAR2,
133 X_APPLICATION_ID in NUMBER,
134 X_TYPE in VARCHAR2,
135 X_QUESTION in VARCHAR2) is
136 begin
137
138 declare
139 ques_code varchar2(30);
140 user_id number := 0;
141 row_id varchar2(64);
142
143 begin
144 if (X_OWNER = 'SEED') then
145 user_id := 1;
146 end if;
147
148 select QUESTION_CODE into ques_code
149 from ICX_QUESTIONS
150 where QUESTION_CODE = X_QUESTION_CODE;
151
152 icx_questions_pkg.UPDATE_ROW (
153 X_QUESTION_CODE => X_QUESTION_CODE,
154 X_APPLICATION_ID => X_APPLICATION_ID,
155 X_TYPE => X_TYPE,
156 X_QUESTION => X_QUESTION,
157 X_LAST_UPDATE_DATE => sysdate,
158 X_LAST_UPDATED_BY => user_id,
159 X_LAST_UPDATE_LOGIN => 0);
160
161 exception
162 when NO_DATA_FOUND then
163
164 icx_questions_pkg.INSERT_ROW (
165 X_ROWID => row_id,
166 X_QUESTION_CODE => X_QUESTION_CODE,
167 X_APPLICATION_ID => X_APPLICATION_ID,
168 X_TYPE => X_TYPE,
169 X_QUESTION => X_QUESTION,
170 X_CREATION_DATE => sysdate,
171 X_CREATED_BY => user_id,
172 X_LAST_UPDATE_DATE => sysdate,
173 X_LAST_UPDATED_BY => user_id,
174 X_LAST_UPDATE_LOGIN => 0);
175 end;
176 end LOAD_ROW;
177
178 --*****************************************************************************
179 procedure ADD_LANGUAGE
180 is
181 begin
182 delete from ICX_QUESTIONS_TL T
183 where not exists
184 (select NULL
185 from ICX_QUESTIONS B
186 where B.QUESTION_CODE = T.QUESTION_CODE
187 );
188
189 update ICX_QUESTIONS_TL T set (
190 QUESTION
191 ) = (select
192 B.QUESTION
193 from ICX_QUESTIONS_TL B
194 where B.QUESTION_CODE = T.QUESTION_CODE
195 and B.LANGUAGE = T.SOURCE_LANG)
196 where (
197 T.QUESTION_CODE,
198 T.LANGUAGE
199 ) in (select
200 SUBT.QUESTION_CODE,
201 SUBT.LANGUAGE
202 from ICX_QUESTIONS_TL SUBB, ICX_QUESTIONS_TL SUBT
203 where SUBB.QUESTION_CODE = SUBT.QUESTION_CODE
204 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
205 and (SUBB.QUESTION <> SUBT.QUESTION
206 or (SUBB.QUESTION is null and SUBT.QUESTION is not null)
207 ));
208
209 insert into ICX_QUESTIONS_TL (
210 QUESTION_CODE,
211 QUESTION,
212 CREATED_BY,
213 CREATION_DATE,
214 LAST_UPDATED_BY,
215 LAST_UPDATE_DATE,
216 LAST_UPDATE_LOGIN,
217 LANGUAGE,
218 SOURCE_LANG
219 ) select
220 B.QUESTION_CODE,
221 B.QUESTION,
222 B.CREATED_BY,
223 B.CREATION_DATE,
224 B.LAST_UPDATED_BY,
225 B.LAST_UPDATE_DATE,
226 B.LAST_UPDATE_LOGIN,
227 L.LANGUAGE_CODE,
228 B.SOURCE_LANG
229 from ICX_QUESTIONS_TL B, FND_LANGUAGES L
230 where L.INSTALLED_FLAG in ('I', 'B')
231 and B.LANGUAGE = userenv('LANG')
232 and not exists
233 (select NULL
234 from ICX_QUESTIONS_TL T
235 where T.QUESTION_CODE = B.QUESTION_CODE
236 and T.LANGUAGE = L.LANGUAGE_CODE);
237 end ADD_LANGUAGE;
238
239 end ICX_QUESTIONS_PKG;