1 package body AMS_USER_STATUSES_PKG as
2 /* $Header: amslustb.pls 120.1 2006/05/08 01:35:17 mayjain noship $ */
3 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6
7 procedure INSERT_ROW (
8 X_ROWID in OUT NOCOPY VARCHAR2,
9 X_USER_STATUS_ID in NUMBER,
10 X_DEFAULT_FLAG in VARCHAR2,
11 X_SEEDED_FLAG in VARCHAR2,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_SYSTEM_STATUS_TYPE in VARCHAR2,
14 X_SYSTEM_STATUS_CODE in VARCHAR2,
15 X_ENABLED_FLAG in VARCHAR2,
16 X_START_DATE_ACTIVE in DATE,
17 X_END_DATE_ACTIVE in DATE,
18 X_NAME in VARCHAR2,
19 X_DESCRIPTION in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER,
25 X_APPLICATION_ID in NUMBER DEFAULT '530'
26 ) is
27 cursor C is select ROWID from AMS_USER_STATUSES_B
28 where USER_STATUS_ID = X_USER_STATUS_ID
29 ;
30 begin
31 insert into AMS_USER_STATUSES_B (
32 DEFAULT_FLAG,
33 SEEDED_FLAG,
34 USER_STATUS_ID,
35 OBJECT_VERSION_NUMBER,
36 SYSTEM_STATUS_TYPE,
37 SYSTEM_STATUS_CODE,
38 ENABLED_FLAG,
39 START_DATE_ACTIVE,
40 END_DATE_ACTIVE,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN,
46 APPLICATION_ID
47 ) values (
48 X_DEFAULT_FLAG,
49 X_SEEDED_FLAG,
50 X_USER_STATUS_ID,
51 X_OBJECT_VERSION_NUMBER,
52 X_SYSTEM_STATUS_TYPE,
53 X_SYSTEM_STATUS_CODE,
54 X_ENABLED_FLAG,
55 X_START_DATE_ACTIVE,
56 X_END_DATE_ACTIVE,
57 X_CREATION_DATE,
58 X_CREATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 X_APPLICATION_ID
63 );
64
65 insert into AMS_USER_STATUSES_TL (
66 USER_STATUS_ID,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_LOGIN,
72 NAME,
73 DESCRIPTION,
74 LANGUAGE,
75 SOURCE_LANG
76 ) select
77 X_USER_STATUS_ID,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_CREATION_DATE,
81 X_CREATED_BY,
82 X_LAST_UPDATE_LOGIN,
83 X_NAME,
84 X_DESCRIPTION,
85 L.LANGUAGE_CODE,
86 userenv('LANG')
87 from FND_LANGUAGES L
88 where L.INSTALLED_FLAG in ('I', 'B')
89 and not exists
90 (select NULL
91 from AMS_USER_STATUSES_TL T
92 where T.USER_STATUS_ID = X_USER_STATUS_ID
93 and T.LANGUAGE = L.LANGUAGE_CODE);
94
95 open c;
96 fetch c into X_ROWID;
97 if (c%notfound) then
98 close c;
99 raise no_data_found;
100 end if;
101 close c;
102
103 end INSERT_ROW;
104
105 procedure LOCK_ROW (
106 X_USER_STATUS_ID in NUMBER,
107 X_DEFAULT_FLAG in VARCHAR2,
108 X_SEEDED_FLAG in VARCHAR2,
109 X_OBJECT_VERSION_NUMBER in NUMBER,
110 X_SYSTEM_STATUS_TYPE in VARCHAR2,
111 X_SYSTEM_STATUS_CODE in VARCHAR2,
112 X_ENABLED_FLAG in VARCHAR2,
113 X_START_DATE_ACTIVE in DATE,
114 X_END_DATE_ACTIVE in DATE,
115 X_NAME in VARCHAR2,
116 X_DESCRIPTION in VARCHAR2
117 ) is
118 cursor c is select
119 DEFAULT_FLAG,
120 SEEDED_FLAG,
121 OBJECT_VERSION_NUMBER,
122 SYSTEM_STATUS_TYPE,
123 SYSTEM_STATUS_CODE,
124 ENABLED_FLAG,
125 START_DATE_ACTIVE,
126 END_DATE_ACTIVE
127 from AMS_USER_STATUSES_B
128 where USER_STATUS_ID = X_USER_STATUS_ID
129 for update of USER_STATUS_ID nowait;
130 recinfo c%rowtype;
131
132 cursor c1 is select
133 NAME,
134 DESCRIPTION,
135 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
136 from AMS_USER_STATUSES_TL
137 where USER_STATUS_ID = X_USER_STATUS_ID
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139 for update of USER_STATUS_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.DEFAULT_FLAG = X_DEFAULT_FLAG)
150 OR ((recinfo.DEFAULT_FLAG is null) AND (X_DEFAULT_FLAG is null)))
151 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
152 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
153 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
154 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
155 AND ((recinfo.SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE)
156 OR ((recinfo.SYSTEM_STATUS_TYPE is null) AND (X_SYSTEM_STATUS_TYPE is null)))
157 AND ((recinfo.SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE)
158 OR ((recinfo.SYSTEM_STATUS_CODE is null) AND (X_SYSTEM_STATUS_CODE is null)))
159 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
160 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
161 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
162 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
163 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
164 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
165 ) then
166 null;
167 else
168 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169 app_exception.raise_exception;
170 end if;
171
172 for tlinfo in c1 loop
173 if (tlinfo.BASELANG = 'Y') then
174 if ( ((tlinfo.NAME = X_NAME)
175 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
176 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
177 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
178 ) then
179 null;
180 else
181 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
182 app_exception.raise_exception;
183 end if;
184 end if;
185 end loop;
186 return;
187 end LOCK_ROW;
188
189 procedure UPDATE_ROW (
190 X_USER_STATUS_ID in NUMBER,
191 X_DEFAULT_FLAG in VARCHAR2,
192 X_SEEDED_FLAG in VARCHAR2,
193 X_OBJECT_VERSION_NUMBER in NUMBER,
194 X_SYSTEM_STATUS_TYPE in VARCHAR2,
195 X_SYSTEM_STATUS_CODE in VARCHAR2,
196 X_ENABLED_FLAG in VARCHAR2,
197 X_START_DATE_ACTIVE in DATE,
198 X_END_DATE_ACTIVE in DATE,
199 X_NAME in VARCHAR2,
200 X_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 X_APPLICATION_ID in NUMBER DEFAULT '530'
205 ) is
206
207 /*08-May-2006 mayjain fix for bug 5166318*/
208 cursor count_def_flag (P_SYSTEM_STATUS_TYPE VARCHAR2, P_SYSTEM_STATUS_CODE VARCHAR2)
209 IS
210 select count(1)
211 from AMS_USER_STATUSES_B
212 where SYSTEM_STATUS_TYPE = P_SYSTEM_STATUS_TYPE and
213 SYSTEM_STATUS_CODE = P_SYSTEM_STATUS_CODE and
214 ENABLED_FLAG = 'Y' and
215 DEFAULT_FLAG = 'Y' and
216 SEEDED_FLAG <> 'Y';
217
218 l_def_count NUMBER := 0;
219 l_default_flag VARCHAR2(1) :='Y';
220 /*08-May-2006 mayjain fix for bug 5166318*/
221 begin
222
223 /*08-May-2006 mayjain fix for bug 5166318*/
224 -- The default flag should be defaulted to the one in ldt file.
225 l_default_flag := X_DEFAULT_FLAG;
226 -- If this is a seeded and default user status
227 IF (X_DEFAULT_FLAG = 'Y') and (X_SEEDED_FLAG = 'Y')
228 THEN
229 -- Find out if there is a custom user status that is default
230 open count_def_flag (X_SYSTEM_STATUS_TYPE, X_SYSTEM_STATUS_CODE);
231 fetch count_def_flag into l_def_count;
232 close count_def_flag;
233
234 -- if there is a custom default user status, then the seeded user status should be marked as 'N'
235 IF l_def_count > 0
236 THEN l_default_flag := 'N';
237 END IF;
238 END IF;
239 /*08-May-2006 mayjain fix for bug 5166318*/
240
241 update AMS_USER_STATUSES_B set
242 DEFAULT_FLAG = l_default_flag, /*08-May-2006 mayjain fix for bug 5166318*/
243 SEEDED_FLAG = X_SEEDED_FLAG,
244 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
245 SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE,
246 SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE,
247 ENABLED_FLAG = X_ENABLED_FLAG,
248 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
249 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
250 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
251 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
253 APPLICATION_ID = X_APPLICATION_ID
254 where USER_STATUS_ID = X_USER_STATUS_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 update AMS_USER_STATUSES_TL set
261 NAME = X_NAME,
262 DESCRIPTION = X_DESCRIPTION,
263 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
264 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
265 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
266 SOURCE_LANG = userenv('LANG')
267 where USER_STATUS_ID = X_USER_STATUS_ID
268 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
269
270 if (sql%notfound) then
271 raise no_data_found;
272 end if;
273 end UPDATE_ROW;
274
275 procedure DELETE_ROW (
276 X_USER_STATUS_ID in NUMBER
277 ) is
278 begin
279 delete from AMS_USER_STATUSES_TL
280 where USER_STATUS_ID = X_USER_STATUS_ID;
281
282 if (sql%notfound) then
283 raise no_data_found;
284 end if;
285
286 delete from AMS_USER_STATUSES_B
287 where USER_STATUS_ID = X_USER_STATUS_ID;
288
289 if (sql%notfound) then
290 raise no_data_found;
291 end if;
292 end DELETE_ROW;
293
294 procedure ADD_LANGUAGE
295 is
296 begin
297 delete from AMS_USER_STATUSES_TL T
298 where not exists
299 (select NULL
300 from AMS_USER_STATUSES_B B
301 where B.USER_STATUS_ID = T.USER_STATUS_ID
302 );
303
304 update AMS_USER_STATUSES_TL T set (
305 NAME,
306 DESCRIPTION
307 ) = (select
308 B.NAME,
309 B.DESCRIPTION
310 from AMS_USER_STATUSES_TL B
311 where B.USER_STATUS_ID = T.USER_STATUS_ID
312 and B.LANGUAGE = T.SOURCE_LANG)
313 where (
314 T.USER_STATUS_ID,
315 T.LANGUAGE
316 ) in (select
317 SUBT.USER_STATUS_ID,
318 SUBT.LANGUAGE
319 from AMS_USER_STATUSES_TL SUBB, AMS_USER_STATUSES_TL SUBT
320 where SUBB.USER_STATUS_ID = SUBT.USER_STATUS_ID
321 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
322 and (SUBB.NAME <> SUBT.NAME
323 or (SUBB.NAME is null and SUBT.NAME is not null)
324 or (SUBB.NAME is not null and SUBT.NAME is null)
325 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
326 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
327 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
328 ));
329
330 insert into AMS_USER_STATUSES_TL (
331 USER_STATUS_ID,
332 LAST_UPDATE_DATE,
333 LAST_UPDATED_BY,
334 CREATION_DATE,
335 CREATED_BY,
336 LAST_UPDATE_LOGIN,
337 NAME,
338 DESCRIPTION,
339 LANGUAGE,
340 SOURCE_LANG
341 ) select
342 B.USER_STATUS_ID,
343 B.LAST_UPDATE_DATE,
344 B.LAST_UPDATED_BY,
345 B.CREATION_DATE,
346 B.CREATED_BY,
347 B.LAST_UPDATE_LOGIN,
348 B.NAME,
349 B.DESCRIPTION,
350 L.LANGUAGE_CODE,
351 B.SOURCE_LANG
352 from AMS_USER_STATUSES_TL B, FND_LANGUAGES L
353 where L.INSTALLED_FLAG in ('I', 'B')
354 and B.LANGUAGE = userenv('LANG')
355 and not exists
356 (select NULL
357 from AMS_USER_STATUSES_TL T
358 where T.USER_STATUS_ID = B.USER_STATUS_ID
359 and T.LANGUAGE = L.LANGUAGE_CODE);
360 end ADD_LANGUAGE;
361
362
363
364 procedure TRANSLATE_ROW(
365 X_USER_STATUS_ID in NUMBER,
366 X_NAME in VARCHAR2,
367 X_DESCRIPTION in VARCHAR2,
368 X_OWNER in VARCHAR2
369 ) IS
370
371 begin
372 update AMS_USER_STATUSES_TL set
373 name = nvl(x_name, name),
374 description = nvl(x_description, description),
375 source_lang = userenv('LANG'),
376 last_update_date = sysdate,
377 last_updated_by = decode(x_owner, 'SEED', 1, 0),
378 last_update_login = 0
379 where user_status_id = x_user_status_id
380 and userenv('LANG') in (language, source_lang);
381 end TRANSLATE_ROW;
382
383 /* This procedure is used to load the data from flat file to customer's database.
384 If there is no row existing for the data from flat file then create the data.
385 else
386 1) modify the whole data when data in db is not modified by customer which can be found
387 by comparing last updated by value to be
388 SEED/DATAMERGE(1), or
389 INITIAL SETUP/ORACLE (2), or
390 SYSTEM ADMINISTRATOR (0).or
391 2) modify the whole data when custom_mode is 'FORCE'
392 3) if the data in db is modified by customer, which can be found by
393 by comparing last updated by value to be not of 0,1,2, then
394 in that case modify only the user unexposed data with last updated by as 3 to
395 distinguish that data is updated by patch.
396 */
397 procedure LOAD_ROW (
398 X_USER_STATUS_ID in NUMBER,
399 X_DEFAULT_FLAG in VARCHAR2 DEFAULT 'N',
400 X_SEEDED_FLAG in VARCHAR2 DEFAULT 'Y',
401 X_OBJECT_VERSION_NUMBER in NUMBER,
402 X_SYSTEM_STATUS_TYPE in VARCHAR2,
403 X_SYSTEM_STATUS_CODE in VARCHAR2,
404 X_ENABLED_FLAG in VARCHAR2 DEFAULT 'Y',
405 X_START_DATE_ACTIVE in DATE,
406 X_END_DATE_ACTIVE in DATE,
407 X_NAME in VARCHAR2,
408 X_DESCRIPTION in VARCHAR2,
409 X_OWNER in VARCHAR2,
410 X_APPLICATION_ID in NUMBER DEFAULT '530',
411 X_CUSTOM_MODE in VARCHAR2
412 ) IS
413
414
415
416 l_user_id number := 1;
417 l_obj_verno number;
418 l_dummy_char varchar2(1);
419 l_row_id varchar2(100);
420 l_user_status_id number;
421 l_db_luby_id number;
422 /*
423 cursor c_obj_verno is
424 select object_version_number
425 from AMS_USER_STATUSES_B
426 where user_status_id = X_USER_STATUS_ID;
427 */
428 cursor c_chk_ust_exists is
429 select 'x'
430 from AMS_USER_STATUSES_B
431 where user_status_id = X_USER_STATUS_ID;
432
433 cursor c_get_ust_id is
434 select AMS_USER_STATUSES_B_S.nextval
435 from dual;
436
437 cursor c_db_data_details is
438 select last_updated_by, nvl(object_version_number,1)
439 from AMS_USER_STATUSES_B
440 where user_status_id = X_USER_STATUS_ID;
441 BEGIN
442
443 -- set the last_updated_by to be used while updating the data in customer data.
444 if X_OWNER = 'SEED' then
445 l_user_id := 1;
446 elsif X_OWNER = 'ORACLE' THEN
447 l_user_id := 2;
448 elsif X_OWNER = 'SYSADMIN' THEN
449 l_user_id := 0;
450 end if ;
451
452 open c_chk_ust_exists;
453 fetch c_chk_ust_exists into l_dummy_char;
454 if c_chk_ust_exists%notfound
455 then
456 -- data does not exist at customer site and hence create the data
457 close c_chk_ust_exists;
458 if X_USER_STATUS_ID is null
459 then
460 open c_get_ust_id;
461 fetch c_get_ust_id into l_user_status_id;
462 close c_get_ust_id;
463 else
464 l_user_status_id := X_USER_STATUS_ID;
465 end if;
466 l_obj_verno := 1;
467
468 AMS_USER_STATUSES_PKG.INSERT_ROW(
469 X_ROWID => l_row_id,
470 X_USER_STATUS_ID => l_user_status_id,
471 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
472 X_SEEDED_FLAG => X_SEEDED_FLAG,
473 X_OBJECT_VERSION_NUMBER => l_obj_verno,
474 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
475 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
476 X_ENABLED_FLAG => X_ENABLED_FLAG,
477 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
478 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
479 X_NAME => X_NAME,
480 X_DESCRIPTION => X_DESCRIPTION,
481 X_CREATION_DATE => SYSDATE,
482 X_CREATED_BY => l_user_id,
483 X_LAST_UPDATE_DATE => SYSDATE,
484 X_LAST_UPDATED_BY => l_user_id,
485 X_LAST_UPDATE_LOGIN => 0,
486 X_APPLICATION_ID => X_APPLICATION_ID);
487
488
489 else
490 -- update the data as per above rules
491 close c_chk_ust_exists;
492 open c_db_data_details;
493 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
494 close c_db_data_details;
495 -- assigning value for l_user_status_id
496 l_user_status_id := X_USER_STATUS_ID;
497
498 if (l_db_luby_id IN (1,2,0)
499 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
500
501 AMS_USER_STATUSES_PKG.UPDATE_ROW(
502 X_USER_STATUS_ID => l_user_status_id,
503 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
504 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
505 X_SEEDED_FLAG => X_SEEDED_FLAG,
506 X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
507 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
508 X_ENABLED_FLAG => X_ENABLED_FLAG,
509 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
510 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
511 X_NAME => X_NAME,
512 X_DESCRIPTION => X_DESCRIPTION,
513 X_LAST_UPDATE_DATE => SYSDATE,
514 X_LAST_UPDATED_BY => l_user_id,
515 X_LAST_UPDATE_LOGIN => 0,
516 X_APPLICATION_ID => X_APPLICATION_ID);
517
518 end if;
519
520 end if;
521 END LOAD_ROW;
522
523
524 end AMS_USER_STATUSES_PKG;
[Home] [Help]