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