[Home] [Help]
PACKAGE BODY: APPS.JTF_SEEDED_QUAL_PKG
Source
1 PACKAGE BODY JTF_SEEDED_QUAL_PKG AS
2 /* $Header: jtfvsqlb.pls 120.2 2005/10/25 16:43:34 achanda ship $ */
3
4 -- eihsu 10/06/1999 adding procedures for MLS support
5 -- vnedunga 05/11/00 fixing the translate row as part of
6 -- MLS verification
7 -- vnedunga 05/16/00 Fixing UPdate_Row/Delete row use JTF_SEEDED_QUAL_ALL_B
8 -- instead of JTF_SEEDED_QUAL
9 -- jdochert 08/17/00 1331579 bug fix =>
10 -- Replaced: AND org_id = x_org_id
11 -- With: and NVL(ORG_ID, -99) = NVL(x_ORG_ID, -99)
12 --
13
14 PROCEDURE Insert_Row(
15 x_Rowid IN OUT NOCOPY VARCHAR2,
16 x_SEEDED_QUAL_ID IN OUT NOCOPY NUMBER,
17 x_LAST_UPDATE_DATE IN DATE,
18 x_LAST_UPDATED_BY IN NUMBER,
19 x_CREATION_DATE IN DATE,
20 x_CREATED_BY IN NUMBER,
21 x_LAST_UPDATE_LOGIN IN NUMBER,
22 x_NAME IN VARCHAR2,
23 x_DESCRIPTION IN VARCHAR2,
24 x_ORG_ID IN NUMBER
25 ) IS
26 CURSOR C IS SELECT rowid FROM JTF_SEEDED_QUAL_ALL_B
27 WHERE SEEDED_QUAL_ID = x_SEEDED_QUAL_ID and NVL(ORG_ID, -99) = NVL(x_ORG_ID, -99);
28 CURSOR C2 IS SELECT JTF_SEEDED_QUAL_s.nextval FROM sys.dual;
29 BEGIN
30 If (x_SEEDED_QUAL_ID IS NULL) then
31 OPEN C2;
32 FETCH C2 INTO x_SEEDED_QUAL_ID;
33 CLOSE C2;
34 End If;
35 INSERT INTO JTF_SEEDED_QUAL_ALL_B(
36 SEEDED_QUAL_ID,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 CREATION_DATE,
40 CREATED_BY,
41 LAST_UPDATE_LOGIN,
42 NAME,
43 DESCRIPTION,
44 ORG_ID
45 ) VALUES (
46 x_SEEDED_QUAL_ID,
47 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
48 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
49 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
50 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
51 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
52 decode( x_NAME, FND_API.G_MISS_CHAR, NULL,x_NAME),
53 decode( x_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,x_DESCRIPTION),
54 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL, x_ORG_ID) );
55
56 insert into JTF_SEEDED_QUAL_ALL_TL (
57 SEEDED_QUAL_ID,
58 NAME,
59 DESCRIPTION,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 CREATION_DATE,
64 CREATED_BY,
65 LANGUAGE,
66 SOURCE_LANG,
67 ORG_ID
68 ) select
69 X_SEEDED_QUAL_ID,
70 X_NAME,
71 X_DESCRIPTION,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_LOGIN,
75 X_CREATION_DATE,
76 X_CREATED_BY,
77 L.LANGUAGE_CODE,
78 userenv('LANG'),
79 X_ORG_ID
80 from FND_LANGUAGES L
81 where L.INSTALLED_FLAG in ('I', 'B')
82 and not exists
83 (select NULL
84 from JTF_SEEDED_QUAL_ALL_TL T
85 where T.SEEDED_QUAL_ID = X_SEEDED_QUAL_ID and
86 T.LANGUAGE = L.language_code and
87 NVL(T.ORG_ID, -99) = NVL(X_ORG_ID, -99) /* 1331579 BUG FIX */ );
88
89 OPEN C;
90 FETCH C INTO x_Rowid;
91 If (C%NOTFOUND) then
92 CLOSE C;
93 RAISE NO_DATA_FOUND;
94 End If;
95 End Insert_Row;
96
97
98
99 PROCEDURE Delete_Row( x_SEEDED_QUAL_ID IN NUMBER
100 ) IS
101 BEGIN
102 DELETE from JTF_SEEDED_QUAL_ALL_TL
103 where SEEDED_QUAL_ID = X_SEEDED_QUAL_ID;
104
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108
109 DELETE FROM JTF_SEEDED_QUAL_ALL_B
110 WHERE SEEDED_QUAL_ID = x_SEEDED_QUAL_ID;
111 If (SQL%NOTFOUND) then
112 RAISE NO_DATA_FOUND;
113 End If;
114 END Delete_Row;
115
116
117
118 PROCEDURE Update_Row(
119 x_Rowid IN VARCHAR2,
120 x_SEEDED_QUAL_ID IN NUMBER,
121 x_LAST_UPDATE_DATE IN DATE,
122 x_LAST_UPDATED_BY IN NUMBER,
123 x_CREATION_DATE IN DATE,
124 x_CREATED_BY IN NUMBER,
125 x_LAST_UPDATE_LOGIN IN NUMBER,
126 x_NAME IN VARCHAR2,
127 x_DESCRIPTION IN VARCHAR2,
128 x_ORG_ID IN NUMBER
129 ) IS
130 BEGIN
131 Update JTF_SEEDED_QUAL_ALL_B
132 SET SEEDED_QUAL_ID = decode( x_SEEDED_QUAL_ID, FND_API.G_MISS_NUM,SEEDED_QUAL_ID,x_SEEDED_QUAL_ID),
133 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
134 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
135 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
136 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
137 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
138 NAME = decode( x_NAME, FND_API.G_MISS_CHAR,NAME,x_NAME),
139 DESCRIPTION = decode( x_DESCRIPTION, FND_API.G_MISS_CHAR,DESCRIPTION,x_DESCRIPTION),
140 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, X_ORG_ID)
141 where SEEDED_QUAL_ID = X_SEEDED_QUAL_ID and
142 NVL(ORG_ID, -99) = NVL(X_ORG_ID, -99) /* 1331579 BUG FIX */ ;
143
144 If (SQL%NOTFOUND) then
145 RAISE NO_DATA_FOUND;
146 End If;
147
148 update JTF_SEEDED_QUAL_ALL_TL set
149 NAME = X_NAME,
150 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
151 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
152 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
153 SOURCE_LANG = userenv('LANG')
154 where SEEDED_QUAL_ID = X_SEEDED_QUAL_ID
155 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
156 NVL(ORG_ID, -99) = NVL(X_ORG_ID, -99) /* 1331579 BUG FIX */ ;
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 END Update_Row;
163
164
165 PROCEDURE Lock_Row(
166 x_Rowid IN VARCHAR2,
167 x_SEEDED_QUAL_ID IN NUMBER,
168 x_LAST_UPDATE_DATE IN DATE,
169 x_LAST_UPDATED_BY IN NUMBER,
170 x_CREATION_DATE IN DATE,
171 x_CREATED_BY IN NUMBER,
172 x_LAST_UPDATE_LOGIN IN NUMBER,
173 x_NAME IN VARCHAR2,
174 x_DESCRIPTION IN VARCHAR2,
175 x_ORG_ID IN NUMBER
176 ) IS
177 CURSOR C IS
178 SELECT *
179 FROM JTF_SEEDED_QUAL
180 WHERE rowid = x_Rowid
181 FOR UPDATE of SEEDED_QUAL_ID NOWAIT;
182 Recinfo C%ROWTYPE;
183
184 CURSOR c1 is select
185 NAME,
186 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
187 from JTF_SEEDED_QUAL_ALL_TL
188 where SEEDED_QUAL_ID = X_SEEDED_QUAL_ID
189 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
190 for update of SEEDED_QUAL_ID nowait;
191 BEGIN
192 OPEN C;
193 FETCH C INTO Recinfo;
194 If (C%NOTFOUND) then
195 CLOSE C;
196 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 End If;
199 CLOSE C;
200 if (
201 ( ( Recinfo.SEEDED_QUAL_ID = x_SEEDED_QUAL_ID)
202 OR ( ( Recinfo.SEEDED_QUAL_ID is NULL )
203 AND ( x_SEEDED_QUAL_ID is NULL )))
204 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
205 OR ( ( Recinfo.LAST_UPDATE_DATE is NULL )
206 AND ( x_LAST_UPDATE_DATE is NULL )))
207 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
208 OR ( ( Recinfo.LAST_UPDATED_BY is NULL )
209 AND ( x_LAST_UPDATED_BY is NULL )))
210 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
211 OR ( ( Recinfo.CREATION_DATE is NULL )
212 AND ( x_CREATION_DATE is NULL )))
213 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
214 OR ( ( Recinfo.CREATED_BY is NULL )
215 AND ( x_CREATED_BY is NULL )))
216 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
217 OR ( ( Recinfo.LAST_UPDATE_LOGIN is NULL )
218 AND ( x_LAST_UPDATE_LOGIN is NULL )))
219 AND ( ( Recinfo.NAME = x_NAME)
220 OR ( ( Recinfo.NAME is NULL )
221 AND ( x_NAME is NULL )))
222 AND ( ( Recinfo.DESCRIPTION = x_DESCRIPTION)
223 OR ( ( Recinfo.DESCRIPTION is NULL )
224 AND ( x_DESCRIPTION is NULL )))
225 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
226 OR ( ( Recinfo.ORG_ID is NULL )
227 AND ( x_ORG_ID is NULL )))
228 ) then
229 null;
230 else
231 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
232 APP_EXCEPTION.RAISE_EXCEPTION;
233 End If;
234
235 -- Lock the transalation Table
236 for tlinfo in c1 loop
237 if (tlinfo.BASELANG = 'Y') then
238 if ( (tlinfo.NAME = X_NAME)
239 ) then
240 null;
241 else
242 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
243 app_exception.raise_exception;
244 end if;
245 end if;
246 end loop;
247
248 END Lock_Row;
249
250 procedure ADD_LANGUAGE
251 is
252 begin
253 delete from JTF_SEEDED_QUAL_ALL_TL T
254 where not exists
255 (select NULL
256 from JTF_SEEDED_QUAL_ALL_B B
257 where B.SEEDED_QUAL_ID = T.SEEDED_QUAL_ID
258 and NVL(B.ORG_ID,-99) = NVL(T.ORG_ID,-99) /* 1331579 BUG FIX */
259 );
260
261 update JTF_SEEDED_QUAL_ALL_TL T set (
262 NAME,
263 DESCRIPTION
264 ) = (select
265 B.NAME,
266 B.DESCRIPTION
267 from JTF_SEEDED_QUAL_ALL_TL B
268 where B.SEEDED_QUAL_ID = T.SEEDED_QUAL_ID
269 and B.LANGUAGE = T.SOURCE_LANG
270 and NVL(B.ORG_ID, -99) = NVL(T.ORG_ID,-99))
271 where (
272 T.SEEDED_QUAL_ID,
273 T.LANGUAGE
274 ) in ( select
275 SUBT.SEEDED_QUAL_ID,
276 SUBT.LANGUAGE
277 from JTF_SEEDED_QUAL_ALL_TL SUBB, JTF_SEEDED_QUAL_ALL_TL SUBT
278 where SUBB.SEEDED_QUAL_ID = SUBT.SEEDED_QUAL_ID
279 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
280 AND NVL(SUBB.ORG_ID, -99) = NVL(SUBT.ORG_ID, -99) /* 1331579 BUG FIX */
281 and (SUBB.NAME <> SUBT.NAME)
282 );
283
284 insert into JTF_SEEDED_QUAL_ALL_TL (
285 SEEDED_QUAL_ID,
286 NAME,
287 DESCRIPTION,
288 LAST_UPDATE_DATE,
289 LAST_UPDATED_BY,
290 LAST_UPDATE_LOGIN,
291 CREATION_DATE,
292 CREATED_BY,
293 LANGUAGE,
294 SOURCE_LANG,
295 ORG_ID
296 ) select
297 B.SEEDED_QUAL_ID,
298 B.NAME,
299 B.DESCRIPTION,
300 B.LAST_UPDATE_DATE,
301 B.LAST_UPDATED_BY,
302 B.LAST_UPDATE_LOGIN,
303 B.CREATION_DATE,
304 B.CREATED_BY,
305 L.LANGUAGE_CODE,
306 B.SOURCE_LANG,
307 B.ORG_ID
308 from JTF_SEEDED_QUAL_ALL_TL B, FND_LANGUAGES L
309 where L.INSTALLED_FLAG in ('I', 'B')
310 and B.LANGUAGE = userenv('LANG')
311 and not exists
312 ( select NULL
313 from JTF_SEEDED_QUAL_ALL_TL T
314 where T.SEEDED_QUAL_ID = B.SEEDED_QUAL_ID
315 and T.LANGUAGE = L.LANGUAGE_CODE
316 AND NVL(T.ORG_ID, -99) = NVL(B.ORG_ID, -99) /* 1331579 BUG FIX */
317 );
318
319 end ADD_LANGUAGE;
320
321 -- --------------------------------------------------------------------
322 -- Procedure : LOAD_ROW
323 -- Description : Called by FNDLOAD to upload seed datas, this procedure
324 -- only handle seed datas.
325 -- --------------------------------------------------------------------
326 PROCEDURE LOAD_ROW
327 ( x_SEEDED_QUAL_ID IN NUMBER,
328 x_description IN VARCHAR2,
329 x_name IN VARCHAR2,
330 x_owner IN VARCHAR2) IS
331 user_id NUMBER;
332
336 (x_name IS NULL) THEN
333 BEGIN
334 -- Validate input data
335 IF (x_SEEDED_QUAL_ID IS NULL) OR
337 GOTO end_load_row;
338 END IF;
339
340 IF (x_owner IS NOT NULL) AND (x_owner = 'ORACLE12.0.0') THEN
341 user_id := 1;
342 ELSE
343 user_id := 0;
344 END IF;
345 -- Load The record to _B table
346 UPDATE JTF_SEEDED_QUAL_ALL_B SET
347 name = x_name,
348 description = x_description,
349 last_update_date = sysdate,
350 last_updated_by = user_id,
351 last_update_login = 0
352 WHERE SEEDED_QUAL_ID = x_SEEDED_QUAL_ID;
353
354 IF (SQL%NOTFOUND) THEN
355 -- Insert new record to _B table
356 INSERT INTO JTF_SEEDED_QUAL_ALL_B
357 (SEEDED_QUAL_ID,
358 name,
359 description,
360 creation_date,
361 created_by,
362 last_update_date,
363 last_updated_by,
364 last_update_login
365 ) VALUES
366 (x_SEEDED_QUAL_ID,
367 x_Name,
368 x_description,
369 sysdate,
370 user_id,
371 sysdate,
372 user_id,
373 0
374 );
375 END IF;
376 -- Load The record to _TL table
377 UPDATE JTF_SEEDED_QUAL_ALL_TL SET
378 name = x_name,
379 description = x_description,
380 last_update_date = sysdate,
381 last_updated_by = user_id,
382 last_update_login = 0,
383 source_lang = userenv('LANG')
384 WHERE SEEDED_QUAL_ID = x_SEEDED_QUAL_ID
385 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
386
387 IF (SQL%NOTFOUND) THEN
388 -- Insert new record to _TL table
389 INSERT INTO JTF_SEEDED_QUAL_ALL_TL
390 (SEEDED_QUAL_ID,
391 name,
392 description,
393 creation_date,
394 created_by,
395 last_update_date,
396 last_updated_by,
397 last_update_login,
398 language,
399 source_lang)
400 SELECT
401 x_SEEDED_QUAL_ID,
402 x_name,
403 x_description,
404 sysdate,
405 user_id,
406 sysdate,
407 user_id,
408 0,
409 l.language_code,
410 userenv('LANG')
411 FROM fnd_languages l
412 WHERE l.installed_flag IN ('I', 'B')
413 AND NOT EXISTS
414 ( SELECT NULL
415 FROM JTF_SEEDED_QUAL_ALL_TL t
416 WHERE t.SEEDED_QUAL_ID = x_SEEDED_QUAL_ID
417 AND t.language = l.language_code
418 );
419
420 END IF;
421 << end_load_row >>
422 NULL;
423 END LOAD_ROW ;
424
425 -- --------------------------------------------------------------------
426 -- Procedure : TRANSLATE_ROW
427 -- Description : Called by FNDLOAD to translate seed datas, this procedure
428 -- only handle seed datas. ORG_ID = -3113
429 -- --------------------------------------------------------------------
430 PROCEDURE TRANSLATE_ROW
431 ( x_SEEDED_QUAL_ID IN NUMBER,
432 x_name IN VARCHAR2,
433 x_Description IN VARCHAR2,
434 x_owner IN VARCHAR2) IS
435 user_id NUMBER;
436 BEGIN
437 -- Validate input data
438 IF (x_SEEDED_QUAL_ID IS NULL) OR (x_name IS NULL) THEN
439 GOTO end_translate_row;
440 END IF;
441
442 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
443 user_id := 1;
444 ELSE
445 user_id := 0;
446 END IF;
447 -- Update the translation
448 UPDATE JTF_SEEDED_QUAL_ALL_TL SET
449 name = x_name,
450 description = x_Description,
451 last_update_date = sysdate,
452 last_updated_by = user_id,
453 last_update_login = 0,
454 source_lang = userenv('LANG')
455 WHERE SEEDED_QUAL_ID = x_SEEDED_QUAL_ID
456 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
457
458 << end_translate_row >>
459 NULL;
460 END TRANSLATE_ROW ;
461
462
463 END JTF_SEEDED_QUAL_PKG;
464