[Home] [Help]
PACKAGE BODY: APPS.AZ_SELECTION_SET_ENTITIES_PKG
Source
1 PACKAGE BODY AZ_SELECTION_SET_ENTITIES_PKG as
2 /* $Header: aztssetenb.pls 120.4 2006/09/18 12:39:36 sbandi noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SELECTION_SET_CODE in VARCHAR2,
6 X_USER_ID in NUMBER,
7 X_ENTITY_OCCURANCE_CODE in VARCHAR2,
8 X_REF_ENTITY_OCCURANCE_CODE in VARCHAR2,
9 X_UPDATABLE_FLAG in VARCHAR2,
10 X_ALLOW_SET_TARGETVAL_FLAG in VARCHAR2,
11 X_ALLOW_FILTER_FLAG in VARCHAR2,
12 X_CHANGE_UPDATABLE_FLAG in VARCHAR2,
13 X_FILTER_SET_FLAG in VARCHAR2,
14 X_SEQ_NUM in NUMBER,
15 X_ENTITY_CODE in VARCHAR2,
16 X_FILTERING_PARAMETERS in CLOB,
17 X_INCLUDE_TYPE in VARCHAR2,
18 X_ENTITY_OCCURANCE_NAME in VARCHAR2,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25 cursor C is select ROWID from AZ_SELECTION_SET_ENTITIES_B
26 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
27 and USER_ID = X_USER_ID
28 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
29 ;
30 begin
31 insert into AZ_SELECTION_SET_ENTITIES_B (
32 REF_ENTITY_OCCURANCE_CODE,
33 UPDATABLE_FLAG,
34 ALLOW_SET_TARGETVAL_FLAG,
35 ALLOW_FILTER_FLAG,
36 CHANGE_UPDATABLE_FLAG,
37 FILTER_SET_FLAG,
38 SEQ_NUM,
39 ENTITY_CODE,
40 FILTERING_PARAMETERS,
41 INCLUDE_TYPE,
42 ENTITY_OCCURANCE_CODE,
43 USER_ID,
44 SELECTION_SET_CODE,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_LOGIN
50 ) values (
51 X_REF_ENTITY_OCCURANCE_CODE,
52 X_UPDATABLE_FLAG,
53 X_ALLOW_SET_TARGETVAL_FLAG,
54 X_ALLOW_FILTER_FLAG,
55 X_CHANGE_UPDATABLE_FLAG,
56 X_FILTER_SET_FLAG,
57 X_SEQ_NUM,
58 X_ENTITY_CODE,
59 X_FILTERING_PARAMETERS,
60 X_INCLUDE_TYPE,
61 X_ENTITY_OCCURANCE_CODE,
62 X_USER_ID,
63 X_SELECTION_SET_CODE,
64 X_CREATION_DATE,
65 X_CREATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
68 X_LAST_UPDATE_LOGIN
69 );
70
71 insert into AZ_SELECTION_SET_ENTITIES_TL (
72 LAST_UPDATE_LOGIN,
73 ENTITY_OCCURANCE_CODE,
74 USER_ID,
75 LAST_UPDATE_DATE,
76 SELECTION_SET_CODE,
77 LAST_UPDATED_BY,
78 ENTITY_OCCURANCE_NAME,
79 CREATED_BY,
80 CREATION_DATE,
81 LANGUAGE,
82 SOURCE_LANG
83 ) select
84 X_LAST_UPDATE_LOGIN,
85 X_ENTITY_OCCURANCE_CODE,
86 X_USER_ID,
87 X_LAST_UPDATE_DATE,
88 X_SELECTION_SET_CODE,
89 X_LAST_UPDATED_BY,
90 X_ENTITY_OCCURANCE_NAME,
91 X_CREATED_BY,
92 X_CREATION_DATE,
93 L.LANGUAGE_CODE,
94 userenv('LANG')
95 from FND_LANGUAGES L
96 where L.INSTALLED_FLAG in ('I', 'B')
97 and not exists
98 (select NULL
99 from AZ_SELECTION_SET_ENTITIES_TL T
100 where T.SELECTION_SET_CODE = X_SELECTION_SET_CODE
101 and T.USER_ID = X_USER_ID
102 and T.ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
103 and T.LANGUAGE = L.LANGUAGE_CODE);
104
105 open c;
106 fetch c into X_ROWID;
107 if (c%notfound) then
108 close c;
109 raise no_data_found;
110 end if;
111 close c;
112
113 end INSERT_ROW;
114
115 procedure LOCK_ROW (
116 X_SELECTION_SET_CODE in VARCHAR2,
117 X_USER_ID in NUMBER,
118 X_ENTITY_OCCURANCE_CODE in VARCHAR2,
119 X_REF_ENTITY_OCCURANCE_CODE in VARCHAR2,
120 X_UPDATABLE_FLAG in VARCHAR2,
121 X_ALLOW_SET_TARGETVAL_FLAG in VARCHAR2,
122 X_ALLOW_FILTER_FLAG in VARCHAR2,
123 X_CHANGE_UPDATABLE_FLAG in VARCHAR2,
124 X_ENTITY_CODE in VARCHAR2,
125 X_FILTERING_PARAMETERS in CLOB,
126 X_INCLUDE_TYPE in VARCHAR2,
127 X_ENTITY_OCCURANCE_NAME in VARCHAR2
128 ) is
129 cursor c is select
130 REF_ENTITY_OCCURANCE_CODE,
131 UPDATABLE_FLAG,
132 ALLOW_SET_TARGETVAL_FLAG,
133 ALLOW_FILTER_FLAG,
134 CHANGE_UPDATABLE_FLAG,
135 ENTITY_CODE,
136 FILTERING_PARAMETERS,
137 INCLUDE_TYPE
138 from AZ_SELECTION_SET_ENTITIES_B
139 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
140 and USER_ID = X_USER_ID
141 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
142 for update of SELECTION_SET_CODE nowait;
143 recinfo c%rowtype;
144
145 cursor c1 is select
146 ENTITY_OCCURANCE_NAME,
147 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
148 from AZ_SELECTION_SET_ENTITIES_TL
149 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
150 and USER_ID = X_USER_ID
151 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
152 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
153 for update of SELECTION_SET_CODE nowait;
154
155 begin
156 open c;
157 fetch c into recinfo;
158 if (c%notfound) then
159 close c;
160 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
161 app_exception.raise_exception;
162 end if;
163 close c;
164 if ( ((recinfo.REF_ENTITY_OCCURANCE_CODE = X_REF_ENTITY_OCCURANCE_CODE)
165 OR ((recinfo.REF_ENTITY_OCCURANCE_CODE is null) AND (X_REF_ENTITY_OCCURANCE_CODE is null)))
166 AND ((recinfo.UPDATABLE_FLAG = X_UPDATABLE_FLAG)
167 OR ((recinfo.UPDATABLE_FLAG is null) AND (X_UPDATABLE_FLAG is null)))
168 AND ((recinfo.ALLOW_SET_TARGETVAL_FLAG = X_ALLOW_SET_TARGETVAL_FLAG)
169 OR ((recinfo.ALLOW_SET_TARGETVAL_FLAG is null) AND (X_ALLOW_SET_TARGETVAL_FLAG is null)))
170 AND ((recinfo.ALLOW_FILTER_FLAG = X_ALLOW_FILTER_FLAG)
171 OR ((recinfo.ALLOW_FILTER_FLAG is null) AND (X_ALLOW_FILTER_FLAG is null)))
172 AND ((recinfo.CHANGE_UPDATABLE_FLAG = X_CHANGE_UPDATABLE_FLAG)
173 OR ((recinfo.CHANGE_UPDATABLE_FLAG is null) AND (X_CHANGE_UPDATABLE_FLAG is null)))
174 AND (recinfo.ENTITY_CODE = X_ENTITY_CODE)
175 AND ((recinfo.FILTERING_PARAMETERS = X_FILTERING_PARAMETERS)
176 OR ((recinfo.FILTERING_PARAMETERS is null) AND (X_FILTERING_PARAMETERS is null)))
177 AND ((recinfo.INCLUDE_TYPE = X_INCLUDE_TYPE)
178 OR ((recinfo.INCLUDE_TYPE is null) AND (X_INCLUDE_TYPE is null)))
179 ) then
180 null;
181 else
182 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
183 app_exception.raise_exception;
184 end if;
185
186 for tlinfo in c1 loop
187 if (tlinfo.BASELANG = 'Y') then
188 if ( ((tlinfo.ENTITY_OCCURANCE_NAME = X_ENTITY_OCCURANCE_NAME)
189 OR ((tlinfo.ENTITY_OCCURANCE_NAME is null) AND (X_ENTITY_OCCURANCE_NAME is null)))
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196 end if;
197 end loop;
198 return;
199 end LOCK_ROW;
200
201 procedure UPDATE_ROW (
202 X_SELECTION_SET_CODE in VARCHAR2,
203 X_USER_ID in NUMBER,
204 X_ENTITY_OCCURANCE_CODE in VARCHAR2,
205 X_REF_ENTITY_OCCURANCE_CODE in VARCHAR2,
206 X_UPDATABLE_FLAG in VARCHAR2,
207 X_ALLOW_SET_TARGETVAL_FLAG in VARCHAR2,
208 X_ALLOW_FILTER_FLAG in VARCHAR2,
209 X_CHANGE_UPDATABLE_FLAG in VARCHAR2,
210 X_FILTER_SET_FLAG in VARCHAR2,
211 X_SEQ_NUM in NUMBER,
212 X_ENTITY_CODE in VARCHAR2,
213 X_FILTERING_PARAMETERS in CLOB,
214 X_INCLUDE_TYPE in VARCHAR2,
215 X_ENTITY_OCCURANCE_NAME in VARCHAR2,
216 X_LAST_UPDATE_DATE in DATE,
217 X_LAST_UPDATED_BY in NUMBER,
218 X_LAST_UPDATE_LOGIN in NUMBER
219 ) is
220 begin
221 update AZ_SELECTION_SET_ENTITIES_B set
222 REF_ENTITY_OCCURANCE_CODE = X_REF_ENTITY_OCCURANCE_CODE,
223 UPDATABLE_FLAG = X_UPDATABLE_FLAG,
224 ALLOW_SET_TARGETVAL_FLAG = X_ALLOW_SET_TARGETVAL_FLAG,
225 ALLOW_FILTER_FLAG = X_ALLOW_FILTER_FLAG,
226 CHANGE_UPDATABLE_FLAG = X_CHANGE_UPDATABLE_FLAG,
227 FILTER_SET_FLAG = X_FILTER_SET_FLAG,
228 SEQ_NUM = X_SEQ_NUM,
229 ENTITY_CODE = X_ENTITY_CODE,
230 FILTERING_PARAMETERS = X_FILTERING_PARAMETERS,
231 INCLUDE_TYPE = X_INCLUDE_TYPE,
232 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
233 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
234 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
235 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
236 and USER_ID = X_USER_ID
237 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE;
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242
243 update AZ_SELECTION_SET_ENTITIES_TL set
244 ENTITY_OCCURANCE_NAME = X_ENTITY_OCCURANCE_NAME,
245 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
246 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
247 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
248 SOURCE_LANG = userenv('LANG')
249 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
250 and USER_ID = X_USER_ID
251 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
252 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257 end UPDATE_ROW;
258
259 procedure DELETE_ROW (
260 X_SELECTION_SET_CODE in VARCHAR2,
261 X_USER_ID in NUMBER,
262 X_ENTITY_OCCURANCE_CODE in VARCHAR2
263 ) is
264 begin
265 delete from AZ_SELECTION_SET_ENTITIES_TL
266 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
267 and USER_ID = X_USER_ID
268 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE;
269
270 if (sql%notfound) then
271 raise no_data_found;
272 end if;
273
274 delete from AZ_SELECTION_SET_ENTITIES_B
275 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
276 and USER_ID = X_USER_ID
277 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282 end DELETE_ROW;
283
284 procedure ADD_LANGUAGE
285 is
286 begin
287 delete from AZ_SELECTION_SET_ENTITIES_TL T
288 where not exists
289 (select NULL
290 from AZ_SELECTION_SET_ENTITIES_B B
291 where B.SELECTION_SET_CODE = T.SELECTION_SET_CODE
292 and B.USER_ID = T.USER_ID
293 and B.ENTITY_OCCURANCE_CODE = T.ENTITY_OCCURANCE_CODE
294 );
295
296 update AZ_SELECTION_SET_ENTITIES_TL T set (
297 ENTITY_OCCURANCE_NAME
298 ) = (select
299 B.ENTITY_OCCURANCE_NAME
300 from AZ_SELECTION_SET_ENTITIES_TL B
301 where B.SELECTION_SET_CODE = T.SELECTION_SET_CODE
302 and B.USER_ID = T.USER_ID
303 and B.ENTITY_OCCURANCE_CODE = T.ENTITY_OCCURANCE_CODE
304 and B.LANGUAGE = T.SOURCE_LANG)
305 where (
306 T.SELECTION_SET_CODE,
307 T.USER_ID,
308 T.ENTITY_OCCURANCE_CODE,
309 T.LANGUAGE
310 ) in (select
311 SUBT.SELECTION_SET_CODE,
312 SUBT.USER_ID,
313 SUBT.ENTITY_OCCURANCE_CODE,
314 SUBT.LANGUAGE
315 from AZ_SELECTION_SET_ENTITIES_TL SUBB, AZ_SELECTION_SET_ENTITIES_TL SUBT
316 where SUBB.SELECTION_SET_CODE = SUBT.SELECTION_SET_CODE
317 and SUBB.USER_ID = SUBT.USER_ID
318 and SUBB.ENTITY_OCCURANCE_CODE = SUBT.ENTITY_OCCURANCE_CODE
319 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
320 and (SUBB.ENTITY_OCCURANCE_NAME <> SUBT.ENTITY_OCCURANCE_NAME
321 or (SUBB.ENTITY_OCCURANCE_NAME is null and SUBT.ENTITY_OCCURANCE_NAME is not null)
322 or (SUBB.ENTITY_OCCURANCE_NAME is not null and SUBT.ENTITY_OCCURANCE_NAME is null)
323 ));
324
325 insert into AZ_SELECTION_SET_ENTITIES_TL (
326 LAST_UPDATE_LOGIN,
327 ENTITY_OCCURANCE_CODE,
328 USER_ID,
329 LAST_UPDATE_DATE,
330 SELECTION_SET_CODE,
331 LAST_UPDATED_BY,
332 ENTITY_OCCURANCE_NAME,
333 CREATED_BY,
334 CREATION_DATE,
335 LANGUAGE,
336 SOURCE_LANG
337 ) select
338 B.LAST_UPDATE_LOGIN,
339 B.ENTITY_OCCURANCE_CODE,
340 B.USER_ID,
341 B.LAST_UPDATE_DATE,
342 B.SELECTION_SET_CODE,
343 B.LAST_UPDATED_BY,
344 B.ENTITY_OCCURANCE_NAME,
345 B.CREATED_BY,
346 B.CREATION_DATE,
347 L.LANGUAGE_CODE,
348 B.SOURCE_LANG
349 from AZ_SELECTION_SET_ENTITIES_TL B, FND_LANGUAGES L
350 where L.INSTALLED_FLAG in ('I', 'B')
351 and B.LANGUAGE = userenv('LANG')
352 and not exists
353 (select NULL
354 from AZ_SELECTION_SET_ENTITIES_TL T
355 where T.SELECTION_SET_CODE = B.SELECTION_SET_CODE
356 and T.USER_ID = B.USER_ID
357 and T.ENTITY_OCCURANCE_CODE = B.ENTITY_OCCURANCE_CODE
358 and T.LANGUAGE = L.LANGUAGE_CODE);
359
360 end ADD_LANGUAGE;
361
362 procedure TRANSLATE_ROW (
363 X_SELECTION_SET_CODE in VARCHAR2,
364 X_USER_ID in NUMBER ,
365 X_ENTITY_OCCURANCE_CODE in VARCHAR2,
366 X_ENTITY_OCCURANCE_NAME in VARCHAR2,
367 X_OWNER in VARCHAR2 ) is
368 begin
369 update AZ_SELECTION_SET_ENTITIES_TL set
370 ENTITY_OCCURANCE_NAME = X_ENTITY_OCCURANCE_NAME,
371 last_update_date = sysdate,
372 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
373 last_update_login = 0,
374 source_lang = userenv('LANG')
375 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
376 and USER_ID = X_USER_ID
377 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE
378 and userenv('LANG') in (language, source_lang);
379
380 end TRANSLATE_ROW;
381
382 procedure LOAD_ROW (
383 X_SELECTION_SET_CODE in VARCHAR2,
384 X_USER_ID in NUMBER,
385 X_ENTITY_OCCURANCE_CODE in VARCHAR2,
386 X_OWNER in VARCHAR2,
387 X_ENTITY_CODE in VARCHAR2,
388 X_FILTERING_PARAMETERS in CLOB,
389 X_INCLUDE_TYPE in VARCHAR2,
393 X_ALLOW_FILTER_FLAG in VARCHAR2,
390 X_REF_ENTITY_OCCURANCE_CODE in VARCHAR2,
391 X_UPDATABLE_FLAG in VARCHAR2,
392 X_ALLOW_SET_TARGETVAL_FLAG in VARCHAR2,
394 X_CHANGE_UPDATABLE_FLAG in VARCHAR2,
395 X_FILTER_SET_FLAG in VARCHAR2,
396 X_SEQ_NUM in NUMBER,
397 X_ENTITY_OCCURANCE_NAME in VARCHAR2) IS
398 begin
399 declare
400 l_owner_id number := 0;
401 l_row_id varchar2(64);
402 luby number := null;
403 begin
404 if (X_OWNER = 'SEED') then
405 l_owner_id := 1;
406 end if;
407
408 select created_by into luby
409 from AZ_SELECTION_SET_ENTITIES_B
410 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
411 and USER_ID = X_USER_ID
412 and ENTITY_OCCURANCE_CODE = X_ENTITY_OCCURANCE_CODE;
413
414 IF luby = 1 THEN
415 AZ_SELECTION_SET_ENTITIES_PKG.UPDATE_ROW(
416 X_SELECTION_SET_CODE => X_SELECTION_SET_CODE,
417 X_USER_ID => X_USER_ID,
418 X_ENTITY_OCCURANCE_CODE => X_ENTITY_OCCURANCE_CODE,
419 X_REF_ENTITY_OCCURANCE_CODE => X_REF_ENTITY_OCCURANCE_CODE,
420 X_UPDATABLE_FLAG => X_UPDATABLE_FLAG,
421 X_ALLOW_SET_TARGETVAL_FLAG => X_ALLOW_SET_TARGETVAL_FLAG,
422 X_ALLOW_FILTER_FLAG => X_ALLOW_FILTER_FLAG,
423 X_CHANGE_UPDATABLE_FLAG => X_CHANGE_UPDATABLE_FLAG,
424 X_FILTER_SET_FLAG => X_FILTER_SET_FLAG,
425 X_SEQ_NUM => X_SEQ_NUM,
426 X_ENTITY_CODE => X_ENTITY_CODE,
427 X_FILTERING_PARAMETERS => X_FILTERING_PARAMETERS,
428 X_INCLUDE_TYPE => X_INCLUDE_TYPE,
429 X_ENTITY_OCCURANCE_NAME => X_ENTITY_OCCURANCE_NAME,
430 X_LAST_UPDATE_DATE => sysdate,
431 X_LAST_UPDATED_BY => l_owner_id,
432 X_LAST_UPDATE_LOGIN => 0
433 );
434 END IF; -- if luby = 1
435
436 exception
437 when NO_DATA_FOUND then
438
439 AZ_SELECTION_SET_ENTITIES_PKG.INSERT_ROW(
440 X_ROWID => l_row_id,
441 X_SELECTION_SET_CODE => X_SELECTION_SET_CODE,
442 X_USER_ID => X_USER_ID,
443 X_ENTITY_OCCURANCE_CODE => X_ENTITY_OCCURANCE_CODE,
444 X_REF_ENTITY_OCCURANCE_CODE => X_REF_ENTITY_OCCURANCE_CODE,
445 X_UPDATABLE_FLAG => X_UPDATABLE_FLAG,
446 X_ALLOW_SET_TARGETVAL_FLAG => X_ALLOW_SET_TARGETVAL_FLAG,
447 X_ALLOW_FILTER_FLAG => X_ALLOW_FILTER_FLAG,
448 X_CHANGE_UPDATABLE_FLAG => X_CHANGE_UPDATABLE_FLAG,
449 X_FILTER_SET_FLAG => X_FILTER_SET_FLAG,
450 X_SEQ_NUM => X_SEQ_NUM,
451 X_ENTITY_CODE => X_ENTITY_CODE,
452 X_FILTERING_PARAMETERS => X_FILTERING_PARAMETERS,
453 X_INCLUDE_TYPE => X_INCLUDE_TYPE,
454 X_ENTITY_OCCURANCE_NAME => X_ENTITY_OCCURANCE_NAME,
455 X_CREATION_DATE => sysdate,
456 X_CREATED_BY => l_owner_id,
457 X_LAST_UPDATE_DATE => sysdate,
458 X_LAST_UPDATED_BY => l_owner_id,
459 X_LAST_UPDATE_LOGIN => 0
460 );
461 end;
462
463 end LOAD_ROW;
464
465 end AZ_SELECTION_SET_ENTITIES_PKG;