[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_RESULTS_SEED_PVT
Source
1 package body JTF_IH_RESULTS_SEED_PVT as
2 /* $Header: JTFIHRSB.pls 115.1 2000/02/15 12:26:25 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_RESULT_ID in NUMBER,
6 X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
7 X_POSITIVE_RESPONSE_FLAG in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
10 X_VERSATILITY_CODE in NUMBER,
11 X_RESULT_REQUIRED in VARCHAR2,
12 X_RESULT_CODE in VARCHAR2,
13 X_MEDIA_TYPE in VARCHAR2,
14 X_SHORT_DESCRIPTION in VARCHAR2,
15 X_LONG_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from JTF_IH_RESULTS_B
23 where RESULT_ID = X_RESULT_ID
24 ;
25 begin
26 insert into JTF_IH_RESULTS_B (
27 GENERATE_PRIVATE_CALLBACK,
28 POSITIVE_RESPONSE_FLAG,
29 RESULT_ID,
30 OBJECT_VERSION_NUMBER,
31 GENERATE_PUBLIC_CALLBACK,
32 VERSATILITY_CODE,
33 RESULT_REQUIRED,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_GENERATE_PRIVATE_CALLBACK,
41 X_POSITIVE_RESPONSE_FLAG,
42 X_RESULT_ID,
43 X_OBJECT_VERSION_NUMBER,
44 X_GENERATE_PUBLIC_CALLBACK,
45 X_VERSATILITY_CODE,
46 X_RESULT_REQUIRED,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into JTF_IH_RESULTS_TL (
55 RESULT_CODE,
56 LONG_DESCRIPTION,
57 OBJECT_VERSION_NUMBER,
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 MEDIA_TYPE,
64 SHORT_DESCRIPTION,
65 RESULT_ID,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_RESULT_CODE,
70 X_LONG_DESCRIPTION,
71 X_OBJECT_VERSION_NUMBER,
72 X_CREATED_BY,
73 X_CREATION_DATE,
74 X_LAST_UPDATED_BY,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATE_LOGIN,
77 X_MEDIA_TYPE,
78 X_SHORT_DESCRIPTION,
79 X_RESULT_ID,
80 L.LANGUAGE_CODE,
81 userenv('LANG')
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from JTF_IH_RESULTS_TL T
87 where T.RESULT_ID = X_RESULT_ID
88 and T.LANGUAGE = L.LANGUAGE_CODE);
89
90 open c;
91 fetch c into X_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 X_RESULT_ID in NUMBER,
102 X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
103 X_POSITIVE_RESPONSE_FLAG in VARCHAR2,
104 X_OBJECT_VERSION_NUMBER in NUMBER,
105 X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
106 X_VERSATILITY_CODE in NUMBER,
107 X_RESULT_REQUIRED in VARCHAR2,
108 X_RESULT_CODE in VARCHAR2,
109 X_MEDIA_TYPE in VARCHAR2,
110 X_SHORT_DESCRIPTION in VARCHAR2,
111 X_LONG_DESCRIPTION in VARCHAR2
112 ) is
113 cursor c is select
114 GENERATE_PRIVATE_CALLBACK,
115 POSITIVE_RESPONSE_FLAG,
116 OBJECT_VERSION_NUMBER,
117 GENERATE_PUBLIC_CALLBACK,
118 VERSATILITY_CODE,
119 RESULT_REQUIRED
120 from JTF_IH_RESULTS_B
121 where RESULT_ID = X_RESULT_ID
122 for update of RESULT_ID nowait;
123 recinfo c%rowtype;
124
125 cursor c1 is select
126 RESULT_CODE,
127 MEDIA_TYPE,
128 SHORT_DESCRIPTION,
129 LONG_DESCRIPTION,
130 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
131 from JTF_IH_RESULTS_TL
132 where RESULT_ID = X_RESULT_ID
133 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
134 for update of RESULT_ID nowait;
135 begin
136 open c;
137 fetch c into recinfo;
138 if (c%notfound) then
139 close c;
140 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
141 app_exception.raise_exception;
142 end if;
143 close c;
144 if ( ((recinfo.GENERATE_PRIVATE_CALLBACK = X_GENERATE_PRIVATE_CALLBACK)
145 OR ((recinfo.GENERATE_PRIVATE_CALLBACK is null) AND (X_GENERATE_PRIVATE_CALLBACK is null)))
146 AND (recinfo.POSITIVE_RESPONSE_FLAG = X_POSITIVE_RESPONSE_FLAG)
147 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
148 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
149 AND ((recinfo.GENERATE_PUBLIC_CALLBACK = X_GENERATE_PUBLIC_CALLBACK)
150 OR ((recinfo.GENERATE_PUBLIC_CALLBACK is null) AND (X_GENERATE_PUBLIC_CALLBACK is null)))
151 AND ((recinfo.VERSATILITY_CODE = X_VERSATILITY_CODE)
152 OR ((recinfo.VERSATILITY_CODE is null) AND (X_VERSATILITY_CODE is null)))
153 AND ((recinfo.RESULT_REQUIRED = X_RESULT_REQUIRED)
154 OR ((recinfo.RESULT_REQUIRED is null) AND (X_RESULT_REQUIRED is null)))
155 ) then
156 null;
157 else
158 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159 app_exception.raise_exception;
160 end if;
161
162 for tlinfo in c1 loop
163 if (tlinfo.BASELANG = 'Y') then
164 if ( ((tlinfo.RESULT_CODE = X_RESULT_CODE)
165 OR ((tlinfo.RESULT_CODE is null) AND (X_RESULT_CODE is null)))
166 AND ((tlinfo.MEDIA_TYPE = X_MEDIA_TYPE)
167 OR ((tlinfo.MEDIA_TYPE is null) AND (X_MEDIA_TYPE is null)))
168 AND ((tlinfo.SHORT_DESCRIPTION = X_SHORT_DESCRIPTION)
169 OR ((tlinfo.SHORT_DESCRIPTION is null) AND (X_SHORT_DESCRIPTION is null)))
170 AND ((tlinfo.LONG_DESCRIPTION = X_LONG_DESCRIPTION)
171 OR ((tlinfo.LONG_DESCRIPTION is null) AND (X_LONG_DESCRIPTION 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_RESULT_ID in NUMBER,
185 X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
186 X_POSITIVE_RESPONSE_FLAG in VARCHAR2,
187 X_OBJECT_VERSION_NUMBER in NUMBER,
188 X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
189 X_VERSATILITY_CODE in NUMBER,
190 X_RESULT_REQUIRED in VARCHAR2,
191 X_RESULT_CODE in VARCHAR2,
192 X_MEDIA_TYPE in VARCHAR2,
193 X_SHORT_DESCRIPTION in VARCHAR2,
194 X_LONG_DESCRIPTION in VARCHAR2,
195 X_LAST_UPDATE_DATE in DATE,
196 X_LAST_UPDATED_BY in NUMBER,
197 X_LAST_UPDATE_LOGIN in NUMBER
198 ) is
199 begin
200 update JTF_IH_RESULTS_B set
201 GENERATE_PRIVATE_CALLBACK = X_GENERATE_PRIVATE_CALLBACK,
202 POSITIVE_RESPONSE_FLAG = X_POSITIVE_RESPONSE_FLAG,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
204 GENERATE_PUBLIC_CALLBACK = X_GENERATE_PUBLIC_CALLBACK,
205 VERSATILITY_CODE = X_VERSATILITY_CODE,
206 RESULT_REQUIRED = X_RESULT_REQUIRED,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
210 where RESULT_ID = X_RESULT_ID;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215
216 update JTF_IH_RESULTS_TL set
217 RESULT_CODE = X_RESULT_CODE,
218 MEDIA_TYPE = X_MEDIA_TYPE,
219 SHORT_DESCRIPTION = X_SHORT_DESCRIPTION,
220 LONG_DESCRIPTION = X_LONG_DESCRIPTION,
221 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
222 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
223 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
224 SOURCE_LANG = userenv('LANG')
225 where RESULT_ID = X_RESULT_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231 end UPDATE_ROW;
232
233 procedure DELETE_ROW (
234 X_RESULT_ID in NUMBER
235 ) is
236 begin
237 delete from JTF_IH_RESULTS_TL
238 where RESULT_ID = X_RESULT_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243
244 delete from JTF_IH_RESULTS_B
245 where RESULT_ID = X_RESULT_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250 end DELETE_ROW;
251
252 procedure ADD_LANGUAGE
253 is
254 begin
255 delete from JTF_IH_RESULTS_TL T
256 where not exists
257 (select NULL
258 from JTF_IH_RESULTS_B B
259 where B.RESULT_ID = T.RESULT_ID
260 );
261
262 update JTF_IH_RESULTS_TL T set (
263 RESULT_CODE,
264 MEDIA_TYPE,
265 SHORT_DESCRIPTION,
266 LONG_DESCRIPTION
267 ) = (select
268 B.RESULT_CODE,
269 B.MEDIA_TYPE,
270 B.SHORT_DESCRIPTION,
271 B.LONG_DESCRIPTION
272 from JTF_IH_RESULTS_TL B
273 where B.RESULT_ID = T.RESULT_ID
274 and B.LANGUAGE = T.SOURCE_LANG)
275 where (
276 T.RESULT_ID,
277 T.LANGUAGE
278 ) in (select
279 SUBT.RESULT_ID,
280 SUBT.LANGUAGE
281 from JTF_IH_RESULTS_TL SUBB, JTF_IH_RESULTS_TL SUBT
282 where SUBB.RESULT_ID = SUBT.RESULT_ID
283 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
284 and (SUBB.RESULT_CODE <> SUBT.RESULT_CODE
285 or (SUBB.RESULT_CODE is null and SUBT.RESULT_CODE is not null)
286 or (SUBB.RESULT_CODE is not null and SUBT.RESULT_CODE is null)
287 or SUBB.MEDIA_TYPE <> SUBT.MEDIA_TYPE
288 or (SUBB.MEDIA_TYPE is null and SUBT.MEDIA_TYPE is not null)
289 or (SUBB.MEDIA_TYPE is not null and SUBT.MEDIA_TYPE is null)
290 or SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
291 or (SUBB.SHORT_DESCRIPTION is null and SUBT.SHORT_DESCRIPTION is not null)
292 or (SUBB.SHORT_DESCRIPTION is not null and SUBT.SHORT_DESCRIPTION is null)
293 or SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION
294 or (SUBB.LONG_DESCRIPTION is null and SUBT.LONG_DESCRIPTION is not null)
295 or (SUBB.LONG_DESCRIPTION is not null and SUBT.LONG_DESCRIPTION is null)
296 ));
297
298 insert into JTF_IH_RESULTS_TL (
299 RESULT_CODE,
300 LONG_DESCRIPTION,
301 OBJECT_VERSION_NUMBER,
302 CREATED_BY,
303 CREATION_DATE,
304 LAST_UPDATED_BY,
305 LAST_UPDATE_DATE,
306 LAST_UPDATE_LOGIN,
307 MEDIA_TYPE,
308 SHORT_DESCRIPTION,
309 RESULT_ID,
310 LANGUAGE,
311 SOURCE_LANG
312 ) select
313 B.RESULT_CODE,
314 B.LONG_DESCRIPTION,
315 B.OBJECT_VERSION_NUMBER,
316 B.CREATED_BY,
317 B.CREATION_DATE,
318 B.LAST_UPDATED_BY,
319 B.LAST_UPDATE_DATE,
320 B.LAST_UPDATE_LOGIN,
321 B.MEDIA_TYPE,
322 B.SHORT_DESCRIPTION,
323 B.RESULT_ID,
324 L.LANGUAGE_CODE,
325 B.SOURCE_LANG
326 from JTF_IH_RESULTS_TL B, FND_LANGUAGES L
327 where L.INSTALLED_FLAG in ('I', 'B')
328 and B.LANGUAGE = userenv('LANG')
329 and not exists
330 (select NULL
331 from JTF_IH_RESULTS_TL T
332 where T.RESULT_ID = B.RESULT_ID
333 and T.LANGUAGE = L.LANGUAGE_CODE);
334 end ADD_LANGUAGE;
335
336 procedure LOAD_ROW (
337 X_RESULT_ID in NUMBER,
338 X_GENERATE_PUBLIC_CALLBACK in VARCHAR2,
339 X_GENERATE_PRIVATE_CALLBACK in VARCHAR2,
340 X_POSITIVE_RESPONSE_FLAG in VARCHAR2,
341 X_OBJECT_VERSION_NUMBER in NUMBER,
342 X_RESULT_REQUIRED in VARCHAR2,
343 X_VERSATILITY_CODE in NUMBER,
344 X_RESULT_CODE in VARCHAR2,
345 X_MEDIA_TYPE in VARCHAR2,
346 X_SHORT_DESCRIPTION in VARCHAR2,
347 X_LONG_DESCRIPTION in VARCHAR2,
348 X_OWNER IN VARCHAR2
349 ) IS
350 begin
351 declare
352 user_id NUMBER := 0;
353 row_id VARCHAR2(64);
354 l_api_version NUMBER := 1.0;
355 l_return_status VARCHAR2(1);
356 l_msg_count NUMBER;
357 l_msg_data VARCHAR2(100);
358 l_init_msg_list VARCHAR2(1) := 'F';
359 l_commit VARCHAR2(1) := 'F';
360 l_validation_level NUMBER := 100;
361 l_result_id NUMBER;
362 l_generate_public_callback VARCHAR2(240);
363 l_generate_private_callback VARCHAR2(240);
364 l_positive_response_flag VARCHAR2(1);
365 l_object_version_number NUMBER;
366 l_result_required VARCHAR2(240);
367 l_versatility_code NUMBER;
368 l_result_code VARCHAR2(80);
369 l_media_type VARCHAR2(240);
370 l_short_description VARCHAR2(240);
371 l_long_description VARCHAR2(1000);
372 l_last_update_date DATE;
373 l_last_updated_by NUMBER;
374 l_last_update_login NUMBER;
375 l_creation_date DATE;
376 l_created_by NUMBER;
377
378 begin
379 if (x_owner = 'SEED') then
380 user_id := -1;
381 end if;
382 l_result_id := X_RESULT_ID;
383 l_generate_public_callback := X_GENERATE_PUBLIC_CALLBACK;
384 l_generate_private_callback := X_GENERATE_PRIVATE_CALLBACK;
385 l_positive_response_flag := X_POSITIVE_RESPONSE_FLAG;
386 l_object_version_number := 1;
387 l_result_required := X_RESULT_REQUIRED;
388 l_versatility_code := X_VERSATILITY_CODE;
389 l_result_code := X_RESULT_CODE;
390 l_media_type := X_MEDIA_TYPE;
391 l_short_description := X_SHORT_DESCRIPTION;
392 l_long_description := X_LONG_DESCRIPTION;
393 l_last_update_date := sysdate;
394 l_last_updated_by := user_id;
395 l_last_update_login := 0;
396
397 UPDATE_ROW(
398 X_RESULT_ID => l_result_id,
399 X_GENERATE_PUBLIC_CALLBACK => l_generate_public_callback,
400 X_GENERATE_PRIVATE_CALLBACK => l_generate_private_callback,
401 X_POSITIVE_RESPONSE_FLAG => l_positive_response_flag,
402 X_OBJECT_VERSION_NUMBER => l_object_version_number,
403 X_RESULT_REQUIRED => l_result_required,
404 X_VERSATILITY_CODE => l_versatility_code,
405 X_RESULT_CODE => l_result_code,
406 X_MEDIA_TYPE => l_media_type,
407 X_SHORT_DESCRIPTION => l_short_description,
408 X_LONG_DESCRIPTION => l_long_description,
409 X_LAST_UPDATE_DATE => l_last_update_date,
410 X_LAST_UPDATED_BY => l_last_updated_by,
411 X_LAST_UPDATE_LOGIN => l_last_update_login);
412 EXCEPTION
413 when no_data_found then
414 l_creation_date := sysdate;
415 l_created_by := user_id;
416 INSERT_ROW(
417 row_id,
418 X_RESULT_ID => l_result_id,
419 X_GENERATE_PUBLIC_CALLBACK => l_generate_public_callback,
420 X_GENERATE_PRIVATE_CALLBACK => l_generate_private_callback,
421 X_POSITIVE_RESPONSE_FLAG => l_positive_response_flag,
422 X_OBJECT_VERSION_NUMBER => l_object_version_number,
423 X_RESULT_REQUIRED => l_result_required,
424 X_VERSATILITY_CODE => l_versatility_code,
425 X_RESULT_CODE => l_result_code,
426 X_MEDIA_TYPE => l_media_type,
427 X_SHORT_DESCRIPTION => l_short_description,
428 X_LONG_DESCRIPTION => l_long_description,
429 X_CREATION_DATE => l_creation_date,
430 X_CREATED_BY => l_created_by,
431 X_LAST_UPDATE_DATE => l_last_update_date,
432 X_LAST_UPDATED_BY => l_last_updated_by,
433 X_LAST_UPDATE_LOGIN => l_last_update_login);
434 end;
435 end LOAD_ROW;
436 procedure TRANSLATE_ROW (
437 X_RESULT_ID in NUMBER,
438 X_RESULT_CODE in VARCHAR2,
439 X_MEDIA_TYPE in VARCHAR2,
440 X_SHORT_DESCRIPTION in VARCHAR2,
441 X_LONG_DESCRIPTION in VARCHAR2,
442 X_OWNER IN VARCHAR2) is
443 begin
444 UPDATE jtf_ih_results_tl SET
445 result_id = X_RESULT_ID,
446 result_code = X_RESULT_CODE,
447 media_type = X_MEDIA_TYPE,
448 short_description = X_SHORT_DESCRIPTION,
449 long_description = X_LONG_DESCRIPTION,
450 last_update_date = sysdate,
451 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
452 last_update_login = 0,
453 source_lang = userenv('LANG')
454 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
455 result_id = X_RESULT_ID;
456 end TRANSLATE_ROW;
457
458
459 end JTF_IH_RESULTS_SEED_PVT;