1 package body AK_CUSTOMIZATIONS_PKG as
2 /* $Header: AKDCUSTB.pls 120.3 2006/01/25 15:56:42 tshort noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
7 X_CUSTOMIZATION_CODE in VARCHAR2,
8 X_REGION_APPLICATION_ID in NUMBER,
9 X_REGION_CODE in VARCHAR2,
10 X_NAME in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_VERTICALIZATION_ID in VARCHAR2,
13 X_LOCALIZATION_CODE in VARCHAR2,
14 X_ORG_ID in NUMBER,
15 X_SITE_ID in NUMBER,
16 X_RESPONSIBILITY_ID in NUMBER,
17 X_WEB_USER_ID in NUMBER,
18 X_DEFAULT_CUSTOMIZATION_FLAG in VARCHAR2,
19 X_CUSTOMIZATION_LEVEL_ID in NUMBER,
20 X_CREATED_BY in NUMBER,
21 X_CREATION_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATE_LOGIN in NUMBER,
25 X_START_DATE_ACTIVE in DATE,
26 X_END_DATE_ACTIVE in DATE
27 ) is
28 cursor C is select ROWID from AK_CUSTOMIZATIONS
29 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
30 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
31 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
32 and REGION_CODE = X_REGION_CODE;
33 begin
34 insert into AK_CUSTOMIZATIONS (
35 CUSTOMIZATION_APPLICATION_ID,
36 CUSTOMIZATION_CODE,
37 REGION_APPLICATION_ID,
38 REGION_CODE,
39 VERTICALIZATION_ID,
40 LOCALIZATION_CODE,
41 ORG_ID,
42 SITE_ID,
43 RESPONSIBILITY_ID,
44 WEB_USER_ID,
45 DEFAULT_CUSTOMIZATION_FLAG,
46 CUSTOMIZATION_LEVEL_ID,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 START_DATE_ACTIVE,
53 END_DATE_ACTIVE
54 ) values (
55 X_CUSTOMIZATION_APPLICATION_ID,
56 X_CUSTOMIZATION_CODE,
57 X_REGION_APPLICATION_ID,
58 X_REGION_CODE,
59 X_VERTICALIZATION_ID,
60 X_LOCALIZATION_CODE,
61 X_ORG_ID,
62 X_SITE_ID,
63 X_RESPONSIBILITY_ID,
64 X_WEB_USER_ID,
65 X_DEFAULT_CUSTOMIZATION_FLAG,
66 X_CUSTOMIZATION_LEVEL_ID,
67 X_CREATED_BY,
68 X_CREATION_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_DATE,
71 X_LAST_UPDATE_LOGIN,
72 X_START_DATE_ACTIVE,
73 X_END_DATE_ACTIVE
74 );
75
76 open C;
77 fetch C into X_ROWID;
78 if (C%notfound) then
79 close C;
80 raise no_data_found;
81 end if;
82 close C;
83
84 insert into AK_CUSTOMIZATIONS_TL (
85 CUSTOMIZATION_APPLICATION_ID,
86 CUSTOMIZATION_CODE,
87 REGION_APPLICATION_ID,
88 REGION_CODE,
89 NAME,
90 DESCRIPTION,
91 LANGUAGE,
92 SOURCE_LANG,
93 CREATED_BY,
94 CREATION_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATE_LOGIN
98 ) select
99 X_CUSTOMIZATION_APPLICATION_ID,
100 X_CUSTOMIZATION_CODE,
101 X_REGION_APPLICATION_ID,
102 X_REGION_CODE,
103 X_NAME,
104 X_DESCRIPTION,
105 L.LANGUAGE_CODE,
106 userenv('LANG'),
107 X_CREATED_BY,
108 X_CREATION_DATE,
109 X_LAST_UPDATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATE_LOGIN
112 from FND_LANGUAGES L
113 where L.INSTALLED_FLAG in ('I', 'B')
114 and not exists
115 (select NULL
116 from AK_CUSTOMIZATIONS_TL T
117 where T.CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
118 and T.CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
119 and T.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
120 and T.REGION_CODE = X_REGION_CODE
121 and T.LANGUAGE = L.LANGUAGE_CODE);
122 end INSERT_ROW;
123
124 procedure LOCK_ROW (
125 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
126 X_CUSTOMIZATION_CODE in VARCHAR2,
127 X_REGION_APPLICATION_ID in NUMBER,
128 X_REGION_CODE in VARCHAR2,
129 X_NAME in VARCHAR2,
130 X_DESCRIPTION in VARCHAR2,
131 X_VERTICALIZATION_ID in VARCHAR2,
132 X_LOCALIZATION_CODE in VARCHAR2,
133 X_ORG_ID in NUMBER,
134 X_SITE_ID in NUMBER,
135 X_RESPONSIBILITY_ID in NUMBER,
136 X_WEB_USER_ID in NUMBER,
137 X_DEFAULT_CUSTOMIZATION_FLAG in VARCHAR2,
138 X_CUSTOMIZATION_LEVEL_ID in NUMBER,
139 X_CREATED_BY in NUMBER,
140 X_CREATION_DATE in DATE,
141 X_LAST_UPDATED_BY in NUMBER,
142 X_LAST_UPDATE_DATE in DATE,
143 X_LAST_UPDATE_LOGIN in NUMBER,
144 X_START_DATE_ACTIVE in DATE,
145 X_END_DATE_ACTIVE in DATE
146 ) is
147 cursor C is select
148 CUSTOMIZATION_APPLICATION_ID,
149 CUSTOMIZATION_CODE,
150 REGION_APPLICATION_ID,
151 REGION_CODE,
152 VERTICALIZATION_ID,
153 LOCALIZATION_CODE,
154 ORG_ID,
155 SITE_ID,
156 RESPONSIBILITY_ID,
157 WEB_USER_ID,
158 DEFAULT_CUSTOMIZATION_FLAG,
159 CUSTOMIZATION_LEVEL_ID,
160 CREATED_BY,
161 CREATION_DATE,
162 LAST_UPDATED_BY,
163 LAST_UPDATE_DATE,
164 LAST_UPDATE_LOGIN,
165 START_DATE_ACTIVE,
166 END_DATE_ACTIVE
167 from AK_CUSTOMIZATIONS
168 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
169 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
170 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
171 and REGION_CODE = X_REGION_CODE
172 for update of CUSTOMIZATION_APPLICATION_ID nowait;
173 recinfo C%rowtype;
174
175 cursor C1 is select
176 NAME,
177 DESCRIPTION
178 from AK_CUSTOMIZATIONS_TL
179 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
180 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
181 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
182 and REGION_CODE = X_REGION_CODE
183 and LANGUAGE = userenv('LANG')
184 for update of CUSTOMIZATION_CODE nowait;
185 tlinfo C1%rowtype;
186 begin
187 open C;
188 fetch C into recinfo;
189 if (C%notfound) then
190 close C;
191 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
192 app_exception.raise_exception;
193 end if;
194 close C;
195
196 if (((recinfo.CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID)
197 OR ((recinfo.CUSTOMIZATION_APPLICATION_ID is null)
198 AND (X_CUSTOMIZATION_APPLICATION_ID is null)))
199 AND ((recinfo.CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE)
200 OR ((recinfo.CUSTOMIZATION_CODE is null)
201 AND (X_CUSTOMIZATION_CODE is null)))
202 AND ((recinfo.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID)
203 OR ((recinfo.REGION_APPLICATION_ID is null)
204 AND (X_REGION_APPLICATION_ID is null)))
205 AND ((recinfo.REGION_CODE = X_REGION_CODE)
206 OR ((recinfo.REGION_CODE is null)
207 AND (X_REGION_CODE is null)))
208 AND ((recinfo.VERTICALIZATION_ID = X_VERTICALIZATION_ID)
209 OR ((recinfo.VERTICALIZATION_ID is null)
210 AND (X_VERTICALIZATION_ID is null)))
211 AND ((recinfo.LOCALIZATION_CODE = X_LOCALIZATION_CODE)
212 OR ((recinfo.LOCALIZATION_CODE is null)
213 AND (X_LOCALIZATION_CODE is null)))
214 AND ((recinfo.ORG_ID = X_ORG_ID)
215 OR ((recinfo.ORG_ID is null)
216 AND (X_ORG_ID is null)))
217 AND ((recinfo.SITE_ID = X_SITE_ID)
218 OR ((recinfo.SITE_ID is null)
219 AND (X_SITE_ID is null)))
220 AND ((recinfo.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID)
221 OR ((recinfo.RESPONSIBILITY_ID is null)
222 AND (X_RESPONSIBILITY_ID is null)))
223 AND ((recinfo.WEB_USER_ID = X_WEB_USER_ID)
224 OR ((recinfo.WEB_USER_ID is null)
225 AND (X_WEB_USER_ID is null)))
226 AND ((recinfo.DEFAULT_CUSTOMIZATION_FLAG = X_DEFAULT_CUSTOMIZATION_FLAG)
227 OR ((recinfo.DEFAULT_CUSTOMIZATION_FLAG is null)
228 AND (X_DEFAULT_CUSTOMIZATION_FLAG is null)))
229 AND ((recinfo.CUSTOMIZATION_LEVEL_ID = X_CUSTOMIZATION_LEVEL_ID)
230 OR ((recinfo.CUSTOMIZATION_LEVEL_ID is null)
231 AND (X_CUSTOMIZATION_LEVEL_ID is null)))
232 ) then
233 null;
234 else
235 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
236 app_exception.raise_exception;
237 end if;
238
239 open C1;
240 fetch C1 into tlinfo;
241 if (C1%notfound) then
242 close C1;
243 return;
244 end if;
245 close C1;
246 if ((tlinfo.NAME = X_NAME)
247 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
248 OR((tlinfo.DESCRIPTION is null)
249 AND (X_DESCRIPTION is null)))
250 ) then
251 null;
252 else
253 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
254 app_exception.raise_exception;
255 end if;
256 return;
257 end LOCK_ROW;
258
259 procedure UPDATE_ROW (
260 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
261 X_CUSTOMIZATION_CODE in VARCHAR2,
262 X_REGION_APPLICATION_ID in NUMBER,
263 X_REGION_CODE in VARCHAR2,
264 X_NAME in VARCHAR2,
265 X_DESCRIPTION in VARCHAR2,
266 X_VERTICALIZATION_ID in VARCHAR2,
267 X_LOCALIZATION_CODE in VARCHAR2,
268 X_ORG_ID in NUMBER,
269 X_SITE_ID in NUMBER,
270 X_RESPONSIBILITY_ID in NUMBER,
271 X_WEB_USER_ID in NUMBER,
272 X_DEFAULT_CUSTOMIZATION_FLAG in VARCHAR2,
273 X_CUSTOMIZATION_LEVEL_ID in NUMBER,
274 X_LAST_UPDATED_BY in NUMBER,
275 X_LAST_UPDATE_DATE in DATE,
276 X_LAST_UPDATE_LOGIN in NUMBER,
277 X_START_DATE_ACTIVE in DATE,
278 X_END_DATE_ACTIVE in DATE
279 ) is
280 begin
281 update AK_CUSTOMIZATIONS set
282 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID,
283 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE,
284 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID,
285 REGION_CODE = X_REGION_CODE,
286 VERTICALIZATION_ID = X_VERTICALIZATION_ID,
287 LOCALIZATION_CODE = X_LOCALIZATION_CODE,
288 ORG_ID = X_ORG_ID,
289 SITE_ID = X_SITE_ID,
290 RESPONSIBILITY_ID = X_RESPONSIBILITY_ID,
291 WEB_USER_ID = X_WEB_USER_ID,
292 DEFAULT_CUSTOMIZATION_FLAG = X_DEFAULT_CUSTOMIZATION_FLAG,
293 CUSTOMIZATION_LEVEL_ID = X_CUSTOMIZATION_LEVEL_ID,
294 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
295 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
296 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
297 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
298 END_DATE_ACTIVE = X_END_DATE_ACTIVE
299 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
300 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
301 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
302 and REGION_CODE = X_REGION_CODE;
303
304 if (sql%notfound) then
305 raise no_data_found;
306 end if;
307
308 update AK_CUSTOMIZATIONS_TL set
309 NAME = X_NAME,
310 DESCRIPTION = X_DESCRIPTION,
311 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
312 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
313 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
314 SOURCE_LANG = userenv('LANG')
315 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
316 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
317 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
318 and REGION_CODE = X_REGION_CODE
319 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
320
321 if (sql%notfound) then
322 raise no_data_found;
323 end if;
324 end UPDATE_ROW;
325
326 procedure DELETE_ROW (
327 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
328 X_CUSTOMIZATION_CODE in VARCHAR2,
329 X_REGION_APPLICATION_ID in NUMBER,
330 X_REGION_CODE in VARCHAR2
331 ) is
332 begin
333 delete from AK_CUSTOMIZATIONS
334 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
335 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
336 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
337 and REGION_CODE = X_REGION_CODE;
338
339 if (sql%notfound) then
340 raise no_data_found;
341 end if;
342
343 delete from AK_CUSTOMIZATIONS_TL
344 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
345 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
346 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
347 and REGION_CODE = X_REGION_CODE;
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352 end DELETE_ROW;
353
354 procedure ADD_LANGUAGE
355 is
356 begin
357 /* Mar/19/03 requested by Ric Ginsberg */
358 /* The following delete and update statements are commented out */
359 /* as a quick workaround to fix the time-consuming table handler issue */
360 /* Eventually we'll need to turn them into a separate fix_language procedure */
361 /*
362
363 delete from AK_CUSTOMIZATIONS_TL T
364 where not exists
365 (select NULL
366 from AK_CUSTOMIZATIONS B
367 where B.CUSTOMIZATION_APPLICATION_ID = T.CUSTOMIZATION_APPLICATION_ID
368 and B.CUSTOMIZATION_CODE = T.CUSTOMIZATION_CODE
369 and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
370 and B.REGION_CODE = T.REGION_CODE
371 );
372
373 update AK_CUSTOMIZATIONS_TL T set (
374 NAME,
375 DESCRIPTION
376 ) = (select B.NAME,
377 B.DESCRIPTION
378 from AK_CUSTOMIZATIONS_TL B
379 where B.CUSTOMIZATION_APPLICATION_ID = T.CUSTOMIZATION_APPLICATION_ID
380 and B.CUSTOMIZATION_CODE = T.CUSTOMIZATION_CODE
381 and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
382 and B.REGION_CODE = T.REGION_CODE
383 and B.LANGUAGE = T.SOURCE_LANG)
384 where (
385 T.CUSTOMIZATION_APPLICATION_ID,
386 T.CUSTOMIZATION_CODE,
387 T.REGION_APPLICATION_ID,
388 T.REGION_CODE,
389 T.LANGUAGE
390 ) in (select SUBT.CUSTOMIZATION_APPLICATION_ID,
391 SUBT.CUSTOMIZATION_CODE,
392 SUBT.REGION_APPLICATION_ID,
393 SUBT.REGION_CODE,
394 SUBT.LANGUAGE
395 from AK_CUSTOMIZATIONS_TL SUBB,
396 AK_CUSTOMIZATIONS_TL SUBT
397 where SUBB.CUSTOMIZATION_APPLICATION_ID = SUBT.CUSTOMIZATION_APPLICATION_ID
398 and SUBB.CUSTOMIZATION_CODE = SUBT.CUSTOMIZATION_CODE
399 and SUBB.REGION_APPLICATION_ID = SUBT.REGION_APPLICATION_ID
400 and SUBB.REGION_CODE = SUBT.REGION_CODE
401 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
402 and (SUBB.NAME <> SUBT.NAME
403 or (SUBB.NAME is null and SUBT.NAME is not null)
404 or (SUBB.NAME is not null and SUBT.NAME is null))
408 );
405 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
406 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
407 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
409
410 */
411
412 insert /*+ append parallel(tt) */ into AK_CUSTOMIZATIONS_TL tt (
413 CUSTOMIZATION_APPLICATION_ID,
414 CUSTOMIZATION_CODE,
415 REGION_APPLICATION_ID,
416 REGION_CODE,
417 NAME,
418 DESCRIPTION,
419 LANGUAGE,
420 SOURCE_LANG,
421 CREATED_BY,
422 CREATION_DATE,
423 LAST_UPDATED_BY,
424 LAST_UPDATE_DATE,
425 LAST_UPDATE_LOGIN
426 ) select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
427 (select /*+ no_merge ordered parallel(b) */
428 B.CUSTOMIZATION_APPLICATION_ID,
429 B.CUSTOMIZATION_CODE,
430 B.REGION_APPLICATION_ID,
431 B.REGION_CODE,
432 B.NAME,
433 B.DESCRIPTION,
434 L.LANGUAGE_CODE,
435 B.SOURCE_LANG,
436 B.CREATED_BY,
437 B.CREATION_DATE,
438 B.LAST_UPDATED_BY,
439 B.LAST_UPDATE_DATE,
440 B.LAST_UPDATE_LOGIN
441 from AK_CUSTOMIZATIONS_TL B,
442 FND_LANGUAGES L
443 where L.INSTALLED_FLAG in ('I', 'B')
444 and B.LANGUAGE = userenv('LANG')
445 ) v, AK_CUSTOMIZATIONS_TL T
446 where T.CUSTOMIZATION_APPLICATION_ID(+) = v.CUSTOMIZATION_APPLICATION_ID
447 and T.CUSTOMIZATION_CODE(+) = v.CUSTOMIZATION_CODE
448 and T.REGION_APPLICATION_ID(+) = v.REGION_APPLICATION_ID
449 and T.REGION_CODE(+) = v.REGION_CODE
450 and T.LANGUAGE(+) = v.LANGUAGE_CODE
451 and T.CUSTOMIZATION_APPLICATION_ID is NULL
452 and T.CUSTOMIZATION_CODE is NULL
453 and T.REGION_APPLICATION_ID is NULL
454 and T.REGION_CODE is NULL;
455
456 end ADD_LANGUAGE;
457
458 end AK_CUSTOMIZATIONS_PKG;
[Home] [Help]