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