1 package body IGW_QUESTIONS_PKG as
2 -- $Header: igwstqub.pls 115.8 2002/11/15 00:47:34 ashkumar ship $
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_QUESTION_NUMBER in VARCHAR2,
7 X_APPLIES_TO in VARCHAR2,
8 X_EXPLANATION_FOR_YES_FLAG in VARCHAR2,
9 X_EXPLANATION_FOR_NO_FLAG in VARCHAR2,
10 X_DATE_FOR_YES_FLAG in VARCHAR2,
11 X_DATE_FOR_NO_FLAG in VARCHAR2,
12 X_START_DATE_ACTIVE in DATE,
13 X_END_DATE_ACTIVE in DATE,
14 X_EXPLANATION in VARCHAR2,
15 X_POLICY in VARCHAR2,
16 X_REGULATION in VARCHAR2,
17 X_DESCRIPTION in VARCHAR2,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER
23 ) is
24 cursor C is select ROWID from IGW_QUESTIONS
25 where QUESTION_NUMBER = X_QUESTION_NUMBER
26 ;
27 begin
28 insert into IGW_QUESTIONS (
29 QUESTION_NUMBER,
30 APPLIES_TO,
31 EXPLANATION_FOR_YES_FLAG,
32 EXPLANATION_FOR_NO_FLAG,
33 DATE_FOR_YES_FLAG,
34 DATE_FOR_NO_FLAG,
35 START_DATE_ACTIVE,
36 END_DATE_ACTIVE,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN
42 ) values (
43 X_QUESTION_NUMBER,
44 X_APPLIES_TO,
45 X_EXPLANATION_FOR_YES_FLAG,
46 X_EXPLANATION_FOR_NO_FLAG,
47 X_DATE_FOR_YES_FLAG,
48 X_DATE_FOR_NO_FLAG,
49 X_START_DATE_ACTIVE,
50 X_END_DATE_ACTIVE,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into IGW_QUESTIONS_TL (
59 QUESTION_NUMBER,
60 DESCRIPTION,
61 EXPLANATION,
62 POLICY,
63 REGULATION,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_LOGIN,
69 LANGUAGE,
70 SOURCE_LANG
71 ) select
72 X_QUESTION_NUMBER,
73 X_DESCRIPTION,
74 X_EXPLANATION,
75 X_POLICY,
76 X_REGULATION,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_LOGIN,
82 L.LANGUAGE_CODE,
83 userenv('LANG')
84 from FND_LANGUAGES L
85 where L.INSTALLED_FLAG in ('I', 'B')
86 and not exists
87 (select NULL
88 from IGW_QUESTIONS_TL T
89 where T.QUESTION_NUMBER = X_QUESTION_NUMBER
90 and T.LANGUAGE = L.LANGUAGE_CODE);
91
92 open c;
93 fetch c into X_ROWID;
94 if (c%notfound) then
95 close c;
96 raise no_data_found;
97 end if;
98 close c;
99
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_QUESTION_NUMBER in VARCHAR2,
104 X_APPLIES_TO in VARCHAR2,
105 X_EXPLANATION_FOR_YES_FLAG in VARCHAR2,
106 X_EXPLANATION_FOR_NO_FLAG in VARCHAR2,
107 X_DATE_FOR_YES_FLAG in VARCHAR2,
108 X_DATE_FOR_NO_FLAG in VARCHAR2,
109 X_START_DATE_ACTIVE in DATE,
110 X_END_DATE_ACTIVE in DATE,
111 X_EXPLANATION in VARCHAR2,
112 X_POLICY in VARCHAR2,
113 X_REGULATION in VARCHAR2,
114 X_DESCRIPTION in VARCHAR2
115 ) is
116 cursor c is select
117 APPLIES_TO,
118 EXPLANATION_FOR_YES_FLAG,
119 EXPLANATION_FOR_NO_FLAG,
120 DATE_FOR_YES_FLAG,
121 DATE_FOR_NO_FLAG,
122 START_DATE_ACTIVE,
123 END_DATE_ACTIVE
124 from IGW_QUESTIONS
125 where QUESTION_NUMBER = X_QUESTION_NUMBER
126 for update of QUESTION_NUMBER nowait;
127 recinfo c%rowtype;
128
129 cursor c1 is select
130 DESCRIPTION,
131 EXPLANATION,
132 POLICY,
133 REGULATION,
134 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
135 from IGW_QUESTIONS_TL
136 where QUESTION_NUMBER = X_QUESTION_NUMBER
137 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
138 for update of QUESTION_NUMBER nowait;
139 begin
140 open c;
141 fetch c into recinfo;
142 if (c%notfound) then
143 close c;
144 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
145 app_exception.raise_exception;
146 end if;
147 close c;
148 if ( (recinfo.APPLIES_TO = X_APPLIES_TO)
149 AND (recinfo.EXPLANATION_FOR_YES_FLAG = X_EXPLANATION_FOR_YES_FLAG)
150 AND (recinfo.EXPLANATION_FOR_NO_FLAG = X_EXPLANATION_FOR_NO_FLAG)
151 AND (recinfo.DATE_FOR_YES_FLAG = X_DATE_FOR_YES_FLAG)
152 AND (recinfo.DATE_FOR_NO_FLAG = X_DATE_FOR_NO_FLAG)
153 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
154 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
155 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162
163 for tlinfo in c1 loop
164 if (tlinfo.BASELANG = 'Y') then
165 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
166 AND ((tlinfo.EXPLANATION = X_EXPLANATION)
167 OR ((tlinfo.EXPLANATION is null) AND (X_EXPLANATION is null)))
168 AND ((tlinfo.POLICY = X_POLICY)
169 OR ((tlinfo.POLICY is null) AND (X_POLICY is null)))
170 AND ((tlinfo.REGULATION = X_REGULATION)
171 OR ((tlinfo.REGULATION is null) AND (X_REGULATION is null)))
172 ) then
173 null;
174 else
175 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176 app_exception.raise_exception;
177 end if;
178 end if;
179 end loop;
180 return;
181 end LOCK_ROW;
182
183 procedure UPDATE_ROW (
184 X_QUESTION_NUMBER in VARCHAR2,
185 X_APPLIES_TO in VARCHAR2,
186 X_EXPLANATION_FOR_YES_FLAG in VARCHAR2,
187 X_EXPLANATION_FOR_NO_FLAG in VARCHAR2,
188 X_DATE_FOR_YES_FLAG in VARCHAR2,
189 X_DATE_FOR_NO_FLAG in VARCHAR2,
190 X_START_DATE_ACTIVE in DATE,
191 X_END_DATE_ACTIVE in DATE,
192 X_EXPLANATION in VARCHAR2,
193 X_POLICY in VARCHAR2,
194 X_REGULATION in VARCHAR2,
195 X_DESCRIPTION in VARCHAR2,
196 X_LAST_UPDATE_DATE in DATE,
197 X_LAST_UPDATED_BY in NUMBER,
198 X_LAST_UPDATE_LOGIN in NUMBER
199 ) is
200 begin
201 update IGW_QUESTIONS set
202 APPLIES_TO = X_APPLIES_TO,
203 EXPLANATION_FOR_YES_FLAG = X_EXPLANATION_FOR_YES_FLAG,
204 EXPLANATION_FOR_NO_FLAG = X_EXPLANATION_FOR_NO_FLAG,
205 DATE_FOR_YES_FLAG = X_DATE_FOR_YES_FLAG,
206 DATE_FOR_NO_FLAG = X_DATE_FOR_NO_FLAG,
207 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
208 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
209 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
210 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
211 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
212 where QUESTION_NUMBER = X_QUESTION_NUMBER;
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217
218 update IGW_QUESTIONS_TL set
219 DESCRIPTION = X_DESCRIPTION,
220 EXPLANATION = X_EXPLANATION,
221 POLICY = X_POLICY,
222 REGULATION = X_REGULATION,
223 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
224 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
226 SOURCE_LANG = userenv('LANG')
227 where QUESTION_NUMBER = X_QUESTION_NUMBER
228 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
229
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233 end UPDATE_ROW;
234
235 procedure DELETE_ROW (
236 X_QUESTION_NUMBER in VARCHAR2
237 ) is
238 begin
239 delete from IGW_QUESTIONS_TL
240 where QUESTION_NUMBER = X_QUESTION_NUMBER;
241
242 if (sql%notfound) then
243 raise no_data_found;
244 end if;
245
246 delete from IGW_QUESTIONS
247 where QUESTION_NUMBER = X_QUESTION_NUMBER;
248
249 if (sql%notfound) then
250 raise no_data_found;
251 end if;
252 end DELETE_ROW;
253
254 procedure ADD_LANGUAGE
255 is
256 begin
257 delete from IGW_QUESTIONS_TL T
258 where not exists
259 (select NULL
260 from IGW_QUESTIONS B
261 where B.QUESTION_NUMBER = T.QUESTION_NUMBER
262 );
263
264 update IGW_QUESTIONS_TL T set (
265 DESCRIPTION,
266 EXPLANATION,
267 POLICY,
268 REGULATION
269 ) = (select
270 B.DESCRIPTION,
271 B.EXPLANATION,
272 B.POLICY,
273 B.REGULATION
274 from IGW_QUESTIONS_TL B
275 where B.QUESTION_NUMBER = T.QUESTION_NUMBER
276 and B.LANGUAGE = T.SOURCE_LANG)
277 where (
278 T.QUESTION_NUMBER,
279 T.LANGUAGE
280 ) in (select
281 SUBT.QUESTION_NUMBER,
282 SUBT.LANGUAGE
283 from IGW_QUESTIONS_TL SUBB, IGW_QUESTIONS_TL SUBT
284 where SUBB.QUESTION_NUMBER = SUBT.QUESTION_NUMBER
285 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
286 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
287 OR (SUBB.EXPLANATION <> SUBT.EXPLANATION)
288 OR (SUBB.EXPLANATION is null and SUBT.EXPLANATION is not null)
289 OR (SUBB.EXPLANATION is not null and SUBT.EXPLANATION is null)
290 OR (SUBB.POLICY <> SUBT.POLICY)
291 OR (SUBB.POLICY is null and SUBT.POLICY is not null)
292 OR (SUBB.POLICY is not null and SUBT.POLICY is null)
293 OR (SUBB.REGULATION <> SUBT.REGULATION)
294 OR (SUBB.REGULATION is null and SUBT.REGULATION is not null)
295 OR (SUBB.REGULATION is not null and SUBT.REGULATION is null))
296 );
297
298 insert into IGW_QUESTIONS_TL (
299 QUESTION_NUMBER,
300 DESCRIPTION,
301 EXPLANATION,
302 POLICY,
303 REGULATION,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_LOGIN,
309 LANGUAGE,
310 SOURCE_LANG
311 ) select
312 B.QUESTION_NUMBER,
313 B.DESCRIPTION,
314 B.EXPLANATION,
315 B.POLICY,
316 B.REGULATION,
317 B.LAST_UPDATE_DATE,
318 B.LAST_UPDATED_BY,
319 B.CREATION_DATE,
320 B.CREATED_BY,
321 B.LAST_UPDATE_LOGIN,
322 L.LANGUAGE_CODE,
323 B.SOURCE_LANG
324 from IGW_QUESTIONS_TL B, FND_LANGUAGES L
325 where L.INSTALLED_FLAG in ('I', 'B')
326 and B.LANGUAGE = userenv('LANG')
327 and not exists
328 (select NULL
329 from IGW_QUESTIONS_TL T
330 where T.QUESTION_NUMBER = B.QUESTION_NUMBER
331 and T.LANGUAGE = L.LANGUAGE_CODE);
332 end ADD_LANGUAGE;
333
334 procedure TRANSLATE_ROW (
335 X_QUESTION_NUMBER in VARCHAR2,
336 X_DESCRIPTION in VARCHAR2,
337 X_EXPLANATION in VARCHAR2,
338 X_POLICY in VARCHAR2,
339 X_REGULATION in VARCHAR2,
340 X_OWNER in VARCHAR2) is
341 begin
342 update igw_questions_tl set
343 DESCRIPTION = nvl(X_DESCRIPTION, DESCRIPTION),
344 EXPLANATION = nvl(X_EXPLANATION, EXPLANATION),
345 POLICY = nvl(X_POLICY, POLICY),
346 REGULATION = nvl(X_REGULATION, REGULATION),
347 LAST_UPDATE_DATE = sysdate,
348 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
349 LAST_UPDATE_LOGIN = 0,
350 SOURCE_LANG = userenv('LANG')
351 where question_number = X_QUESTION_NUMBER and
352 userenv('LANG') in (language, source_lang);
353
354 end TRANSLATE_ROW;
355
356 end IGW_QUESTIONS_PKG;
357
[Home] [Help]