1 package body AMS_CUSTOM_SETUPS_PKG as
2 /* $Header: amslcusb.pls 120.1 2005/08/26 02:38:38 vmodur noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_CUSTOM_SETUP_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ACTIVITY_TYPE_CODE in VARCHAR2,
8 X_MEDIA_ID in NUMBER,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_OBJECT_TYPE in VARCHAR2,
11 X_SOURCE_CODE_SUFFIX in VARCHAR2,
12 X_SETUP_NAME in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_APPLICATION_ID in NUMBER,
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 X_ALLOW_ESSENTIAL_GROUPING in VARCHAR2,
21 X_USAGE in VARCHAR2,
22 X_MIGRATED_CUSTOM_SETUP_ID in NUMBER
23 ) is
24 cursor C is select ROWID from AMS_CUSTOM_SETUPS_B
25 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID
26 ;
27 begin
28 insert into AMS_CUSTOM_SETUPS_B (
29 CUSTOM_SETUP_ID,
30 OBJECT_VERSION_NUMBER,
31 ACTIVITY_TYPE_CODE,
32 MEDIA_ID,
33 ENABLED_FLAG,
34 OBJECT_TYPE,
35 SOURCE_CODE_SUFFIX,
36 APPLICATION_ID,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN,
42 ALLOW_ESSENTIAL_GROUPING,
43 USAGE,
44 MIGRATED_CUSTOM_SETUP_ID
45 ) values (
46 X_CUSTOM_SETUP_ID,
47 X_OBJECT_VERSION_NUMBER,
48 X_ACTIVITY_TYPE_CODE,
49 X_MEDIA_ID,
50 X_ENABLED_FLAG,
51 X_OBJECT_TYPE,
52 X_SOURCE_CODE_SUFFIX,
53 X_APPLICATION_ID,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN,
59 X_ALLOW_ESSENTIAL_GROUPING,
60 X_USAGE,
61 X_MIGRATED_CUSTOM_SETUP_ID
62 );
63
64 insert into AMS_CUSTOM_SETUPS_TL (
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 CREATION_DATE,
68 CREATED_BY,
69 LAST_UPDATE_LOGIN,
70 SETUP_NAME,
71 DESCRIPTION,
72 CUSTOM_SETUP_ID,
73 LANGUAGE,
74 SOURCE_LANG
75 ) select
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY,
78 X_CREATION_DATE,
79 X_CREATED_BY,
80 X_LAST_UPDATE_LOGIN,
81 X_SETUP_NAME,
82 X_DESCRIPTION,
83 X_CUSTOM_SETUP_ID,
84 L.LANGUAGE_CODE,
85 userenv('LANG')
86 from FND_LANGUAGES L
87 where L.INSTALLED_FLAG in ('I', 'B')
88 and not exists
89 (select NULL
90 from AMS_CUSTOM_SETUPS_TL T
91 where T.CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID
92 and T.LANGUAGE = L.LANGUAGE_CODE);
93
94 open c;
95 fetch c into X_ROWID;
96 if (c%notfound) then
97 close c;
98 raise no_data_found;
99 end if;
100 close c;
101
102 end INSERT_ROW;
103
104 procedure LOCK_ROW (
105 X_CUSTOM_SETUP_ID in NUMBER,
106 X_OBJECT_VERSION_NUMBER in NUMBER,
107 X_ACTIVITY_TYPE_CODE in VARCHAR2,
108 X_MEDIA_ID in NUMBER,
109 X_ENABLED_FLAG in VARCHAR2,
110 X_OBJECT_TYPE in VARCHAR2,
111 X_SETUP_NAME in VARCHAR2,
112 X_DESCRIPTION in VARCHAR2,
113 X_ALLOW_ESSENTIAL_GROUPING in VARCHAR2,
114 X_USAGE in VARCHAR2,
115 X_MIGRATED_CUSTOM_SETUP_ID IN NUMBER
116 ) is
117 cursor c is select
118 OBJECT_VERSION_NUMBER,
119 ACTIVITY_TYPE_CODE,
120 MEDIA_ID,
121 ENABLED_FLAG,
122 OBJECT_TYPE,
123 ALLOW_ESSENTIAL_GROUPING,
124 USAGE,
125 MIGRATED_CUSTOM_SETUP_ID
126 from AMS_CUSTOM_SETUPS_B
127 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID
128 for update of CUSTOM_SETUP_ID nowait;
129 recinfo c%rowtype;
130
131 cursor c1 is select
132 SETUP_NAME,
133 DESCRIPTION,
134 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
135 from AMS_CUSTOM_SETUPS_TL
136 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID
137 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
138 for update of CUSTOM_SETUP_ID nowait;
139 begin
140 open c;
141 fetch c into recinfo;
142 if (c%notfound) then
143 close c;
144 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
145 app_exception.raise_exception;
146 end if;
147 close c;
148 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
149 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
150 AND ((recinfo.ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE)
151 OR ((recinfo.ACTIVITY_TYPE_CODE is null) AND (X_ACTIVITY_TYPE_CODE is null)))
152 AND ((recinfo.MEDIA_ID = X_MEDIA_ID)
153 OR ((recinfo.MEDIA_ID is null) AND (X_MEDIA_ID is null)))
154 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
155 AND (recinfo.ALLOW_ESSENTIAL_GROUPING = X_ALLOW_ESSENTIAL_GROUPING)
156 AND (recinfo.USAGE = X_USAGE)
157 AND (recinfo.MIGRATED_CUSTOM_SETUP_ID = X_MIGRATED_CUSTOM_SETUP_ID)
158 AND (recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
159 ) then
160 null;
161 else
162 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
163 app_exception.raise_exception;
164 end if;
165
166 for tlinfo in c1 loop
167 if (tlinfo.BASELANG = 'Y') then
168 if ( (tlinfo.SETUP_NAME = X_SETUP_NAME)
169 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
170 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177 end if;
178 end loop;
179 return;
180 end LOCK_ROW;
181
182 procedure UPDATE_ROW (
183 X_CUSTOM_SETUP_ID in NUMBER,
184 X_OBJECT_VERSION_NUMBER in NUMBER,
185 X_ACTIVITY_TYPE_CODE in VARCHAR2,
186 X_MEDIA_ID in NUMBER,
187 X_ENABLED_FLAG in VARCHAR2,
188 X_OBJECT_TYPE in VARCHAR2,
189 X_SOURCE_CODE_SUFFIX in VARCHAR2,
190 X_SETUP_NAME in VARCHAR2,
191 X_DESCRIPTION in VARCHAR2,
192 X_APPLICATION_ID in NUMBER,
193 X_LAST_UPDATE_DATE in DATE,
194 X_LAST_UPDATED_BY in NUMBER,
195 X_LAST_UPDATE_LOGIN in NUMBER,
196 X_ALLOW_ESSENTIAL_GROUPING in VARCHAR2,
197 X_USAGE in VARCHAR2,
198 X_MIGRATED_CUSTOM_SETUP_ID in NUMBER
199 ) is
200 begin
201 update AMS_CUSTOM_SETUPS_B set
202 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
203 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
204 MEDIA_ID = X_MEDIA_ID,
205 ENABLED_FLAG = X_ENABLED_FLAG,
206 OBJECT_TYPE = X_OBJECT_TYPE,
207 APPLICATION_ID = X_APPLICATION_ID,
208 SOURCE_CODE_SUFFIX = X_SOURCE_CODE_SUFFIX,
209 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
210 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
211 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
212 ALLOW_ESSENTIAL_GROUPING = X_ALLOW_ESSENTIAL_GROUPING,
213 USAGE=X_USAGE,
214 MIGRATED_CUSTOM_SETUP_ID = X_MIGRATED_CUSTOM_SETUP_ID
215 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220
221 update AMS_CUSTOM_SETUPS_TL set
222 SETUP_NAME = X_SETUP_NAME,
223 DESCRIPTION = X_DESCRIPTION,
224 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
225 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
226 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
227 SOURCE_LANG = userenv('LANG')
228 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID
229 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234 end UPDATE_ROW;
235
236 procedure DELETE_ROW (
237 X_CUSTOM_SETUP_ID in NUMBER
238 ) is
239 begin
240 delete from AMS_CUSTOM_SETUPS_TL
241 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246
247 delete from AMS_CUSTOM_SETUPS_B
248 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253 end DELETE_ROW;
254
255 procedure ADD_LANGUAGE
256 is
257 begin
258 delete from AMS_CUSTOM_SETUPS_TL T
259 where not exists
260 (select NULL
261 from AMS_CUSTOM_SETUPS_B B
262 where B.CUSTOM_SETUP_ID = T.CUSTOM_SETUP_ID
263 );
264
265 update AMS_CUSTOM_SETUPS_TL T set (
266 SETUP_NAME,
267 DESCRIPTION
268 ) = (select
269 B.SETUP_NAME,
270 B.DESCRIPTION
271 from AMS_CUSTOM_SETUPS_TL B
272 where B.CUSTOM_SETUP_ID = T.CUSTOM_SETUP_ID
273 and B.LANGUAGE = T.SOURCE_LANG)
274 where (
275 T.CUSTOM_SETUP_ID,
276 T.LANGUAGE
277 ) in (select
278 SUBT.CUSTOM_SETUP_ID,
279 SUBT.LANGUAGE
280 from AMS_CUSTOM_SETUPS_TL SUBB, AMS_CUSTOM_SETUPS_TL SUBT
281 where SUBB.CUSTOM_SETUP_ID = SUBT.CUSTOM_SETUP_ID
282 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
283 and (SUBB.SETUP_NAME <> SUBT.SETUP_NAME
284 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
285 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
286 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
287 ));
288
289 insert into AMS_CUSTOM_SETUPS_TL (
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY,
292 CREATION_DATE,
293 CREATED_BY,
294 LAST_UPDATE_LOGIN,
295 SETUP_NAME,
296 DESCRIPTION,
297 CUSTOM_SETUP_ID,
298 LANGUAGE,
299 SOURCE_LANG
300 ) select
301 B.LAST_UPDATE_DATE,
302 B.LAST_UPDATED_BY,
303 B.CREATION_DATE,
304 B.CREATED_BY,
305 B.LAST_UPDATE_LOGIN,
306 B.SETUP_NAME,
307 B.DESCRIPTION,
308 B.CUSTOM_SETUP_ID,
309 L.LANGUAGE_CODE,
310 B.SOURCE_LANG
311 from AMS_CUSTOM_SETUPS_TL B, FND_LANGUAGES L
312 where L.INSTALLED_FLAG in ('I', 'B')
313 and B.LANGUAGE = userenv('LANG')
314 and not exists
315 (select NULL
316 from AMS_CUSTOM_SETUPS_TL T
317 where T.CUSTOM_SETUP_ID = B.CUSTOM_SETUP_ID
318 and T.LANGUAGE = L.LANGUAGE_CODE);
319 end ADD_LANGUAGE;
320
321
322 procedure TRANSLATE_ROW(
323 X_CUSTOM_SETUP_ID in NUMBER
324 , X_SETUP_NAME in VARCHAR2
325 , X_DESCRIPTION in VARCHAR2
326 , x_owner in VARCHAR2
327 ) is
328 begin
329 update ams_custom_setups_tl set
330 setup_name = nvl(x_SETUP_NAME, setup_name),
331 description = nvl(x_description, description),
332 source_lang = userenv('LANG'),
333 last_update_date = sysdate,
334 last_updated_by = decode(x_owner, 'SEED', 1, 0),
335 last_update_login = 0
336 where custom_setup_id = x_custom_setup_id
337 and userenv('LANG') in (language, source_lang);
338 end TRANSLATE_ROW;
339
340
341 /* This procedure is used to load the data from flat file to customer's database.
342 If there is no row existing for the data from flat file then create the data.
343 else
344 1) modify the whole data when data in db is not modified by customer which can be found
345 by comparing last updated by value to be
346 SEED/DATAMERGE(1), or
347 INITIAL SETUP/ORACLE (2), or
348 SYSTEM ADMINISTRATOR (0).or
349 2) modify the whole data when custom_mode is 'FORCE'
350 3) if the data in db is modified by customer, which can be found by
351 by comparing last updated by value to be not of 0,1,2, then
352 in that case modify only the user unexposed data with last updated by as 3 to
353 distinguish that data is updated by patch.
354 */
355 procedure LOAD_ROW(
356 X_CUSTOM_SETUP_ID in NUMBER,
357 X_ACTIVITY_TYPE_CODE in VARCHAR2,
358 X_MEDIA_ID in NUMBER,
359 X_ENABLED_FLAG in VARCHAR2,
360 X_OBJECT_TYPE in VARCHAR2,
361 X_SOURCE_CODE_SUFFIX in VARCHAR2,
362 X_SETUP_NAME in VARCHAR2,
363 X_DESCRIPTION in VARCHAR2,
364 X_ALLOW_ESSENTIAL_GROUPING in VARCHAR2,
365 X_USAGE in VARCHAR2 := NULL,
366 X_MIGRATED_CUSTOM_SETUP_ID in NUMBER,
367 X_APPLICATION_ID in NUMBER,
368 X_Owner in VARCHAR2,
369 x_custom_mode IN VARCHAR2,
370 X_LAST_UPDATE_DATE in DATE
371 ) is
372
373 l_user_id number := 1;
374 -- user id to be used in case of exceptions to update the customer modified unexposed data.
375 l_excp_user_id number := 3 ;
376
377 l_obj_verno number;
378 l_dummy_number number;
379 l_row_id varchar2(100);
380 l_CUSTOM_SETUP_ID number;
381 l_db_luby_id NUMBER;
382
383 cursor c_db_data_details is
384 select last_updated_by, nvl(object_version_number,1)
385 from ams_custom_setups_b
386 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
387
388 cursor c_chk_cus_exists is
389 select 1
390 from ams_custom_setups_b
391 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
392
393 cursor c_get_cusid is
394 select ams_custom_setups_b_S.nextval
395 from dual;
396
397 BEGIN
398
399 -- set the last_updated_by to be used while updating the data in customer data.
400 if X_OWNER = 'SEED' then
401 l_user_id := 1;
402 elsif X_OWNER = 'ORACLE' THEN
403 l_user_id := 2;
404 elsif X_OWNER = 'SYSADMIN' THEN
405 l_user_id := 0;
406 end if ;
407
408
409 open c_chk_cus_exists;
410 fetch c_chk_cus_exists into l_dummy_number;
411 if c_chk_cus_exists%notfound
412 then
413 -- data does not exist in customer, and hence create the data.
414 close c_chk_cus_exists;
415 if x_custom_setup_id is null
416 then
417 open c_get_cusid;
418 fetch c_get_cusid into l_CUSTOM_SETUP_ID;
419 close c_get_cusid;
420 else
421 l_CUSTOM_SETUP_ID := x_custom_setup_id;
422 end if;
423
424 l_obj_verno := 1;
425
426 AMS_CUSTOM_SETUPS_PKG.INSERT_ROW(
427 X_ROWID => l_row_id,
428 X_CUSTOM_SETUP_ID => l_CUSTOM_SETUP_ID ,
429 X_OBJECT_VERSION_NUMBER => l_obj_verno ,
430 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
431 X_MEDIA_ID => X_MEDIA_ID,
432 X_ENABLED_FLAG => X_ENABLED_FLAG,
433 X_OBJECT_TYPE => X_OBJECT_TYPE,
434 X_SOURCE_CODE_SUFFIX => X_SOURCE_CODE_SUFFIX,
435 X_SETUP_NAME => X_SETUP_NAME,
436 X_DESCRIPTION => X_DESCRIPTION,
437 X_APPLICATION_ID => X_APPLICATION_ID,
438 X_CREATION_DATE => X_LAST_UPDATE_DATE,
439 X_CREATED_BY => l_user_id,
440 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
441 X_LAST_UPDATED_BY => l_user_id,
442 X_LAST_UPDATE_LOGIN => 0,
443 X_ALLOW_ESSENTIAL_GROUPING => X_ALLOW_ESSENTIAL_GROUPING,
444 X_USAGE => X_USAGE,
445 X_MIGRATED_CUSTOM_SETUP_ID => X_MIGRATED_CUSTOM_SETUP_ID
446 );
447 else
448 -- Update the data as per above rules.
449 close c_chk_cus_exists;
450 open c_db_data_details;
451 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
452 close c_db_data_details;
453 if ( l_db_luby_id IN (1, 2, 0)
454 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
455 AMS_CUSTOM_SETUPS_PKG.UPDATE_ROW(
456 X_CUSTOM_SETUP_ID => X_CUSTOM_SETUP_ID,
457 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1 ,
458 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
459 X_MEDIA_ID => X_MEDIA_ID ,
460 X_ENABLED_FLAG => X_ENABLED_FLAG,
461 X_OBJECT_TYPE => X_OBJECT_TYPE,
462 X_SOURCE_CODE_SUFFIX => X_SOURCE_CODE_SUFFIX,
463 X_SETUP_NAME => X_SETUP_NAME,
464 X_DESCRIPTION => X_DESCRIPTION,
465 X_APPLICATION_ID => X_APPLICATION_ID,
466 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
467 X_LAST_UPDATED_BY => l_user_id,
468 X_LAST_UPDATE_LOGIN => 0,
469 X_ALLOW_ESSENTIAL_GROUPING => X_ALLOW_ESSENTIAL_GROUPING,
470 X_USAGE => X_USAGE,
471 X_MIGRATED_CUSTOM_SETUP_ID => X_MIGRATED_CUSTOM_SETUP_ID
472 );
473 --Commented OUT NOCOPY as this will not be ever needed as per nrengasw, and bgeorge.
474 /*
475 else
476 update AMS_CUSTOM_SETUPS_B set
477 OBJECT_VERSION_NUMBER = l_obj_verno + 1,
478 OBJECT_TYPE = X_OBJECT_TYPE,
479 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
480 SOURCE_CODE_SUFFIX = X_SOURCE_CODE_SUFFIX,
481 MEDIA_ID = X_MEDIA_ID , --???
482 LAST_UPDATE_DATE = sysdate,
483 LAST_UPDATED_BY = l_excp_user_id,
484 LAST_UPDATE_LOGIN = 0
485 where CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
486 if (sql%notfound) then
487 raise no_data_found;
488 end if;
489 */
490 end if;
491 end if;
492 END LOAD_ROW;
493
494 end AMS_CUSTOM_SETUPS_PKG;
[Home] [Help]