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