[Home] [Help]
PACKAGE BODY: APPS.AZ_SELECTION_SETS_PKG
Source
1 package body AZ_SELECTION_SETS_PKG as
2 /* $Header: aztssetb.pls 120.3.12000000.2 2007/03/02 10:57:29 sbandi ship $ */
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_STRUCTURE_CODE in VARCHAR2,
8 X_ACTIVE in VARCHAR2,
9 X_HIERARCHICAL_FLAG in VARCHAR2,
10 X_PARTIAL_SELECTION in VARCHAR2,
11 X_SOURCE_INSTANCE in VARCHAR2,
12 X_PREDEFINED_FLAG in VARCHAR2,
13 X_SELECTION_SET_NAME in VARCHAR2,
14 X_SELECTION_SET_DESC in VARCHAR2,
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 ) is
21 cursor C is select ROWID from AZ_SELECTION_SETS_B
22 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
23 and USER_ID = X_USER_ID
24 ;
25 begin
26 insert into AZ_SELECTION_SETS_B (
27 SELECTION_SET_CODE,
28 USER_ID,
29 STRUCTURE_CODE,
30 ACTIVE,
31 HIERARCHICAL_FLAG,
32 PARTIAL_SELECTION,
33 SOURCE_INSTANCE,
34 PREDEFINED_FLAG,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 X_SELECTION_SET_CODE,
42 X_USER_ID,
43 X_STRUCTURE_CODE,
44 X_ACTIVE,
45 X_HIERARCHICAL_FLAG,
46 X_PARTIAL_SELECTION,
47 X_SOURCE_INSTANCE,
48 X_PREDEFINED_FLAG,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_LOGIN
54 );
55
56 insert into AZ_SELECTION_SETS_TL (
57 LAST_UPDATE_LOGIN,
58 SELECTION_SET_CODE,
59 USER_ID,
60 SELECTION_SET_NAME,
61 SELECTION_SET_DESC,
62 CREATED_BY,
63 CREATION_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_DATE,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_LAST_UPDATE_LOGIN,
70 X_SELECTION_SET_CODE,
71 X_USER_ID,
72 X_SELECTION_SET_NAME,
73 X_SELECTION_SET_DESC,
74 X_CREATED_BY,
75 X_CREATION_DATE,
76 X_LAST_UPDATED_BY,
77 X_LAST_UPDATE_DATE,
78 L.LANGUAGE_CODE,
79 userenv('LANG')
80 from FND_LANGUAGES L
81 where L.INSTALLED_FLAG in ('I', 'B')
82 and not exists
83 (select NULL
84 from AZ_SELECTION_SETS_TL T
85 where T.SELECTION_SET_CODE = X_SELECTION_SET_CODE
86 and T.USER_ID = X_USER_ID
87 and T.LANGUAGE = L.LANGUAGE_CODE);
88
89 open c;
90 fetch c into X_ROWID;
91 if (c%notfound) then
92 close c;
93 raise no_data_found;
94 end if;
95 close c;
96
97 end INSERT_ROW;
98
99 procedure LOCK_ROW (
100 X_SELECTION_SET_CODE in VARCHAR2,
101 X_USER_ID in NUMBER,
102 X_STRUCTURE_CODE in VARCHAR2,
103 X_SOURCE_INSTANCE in VARCHAR2,
104 X_PREDEFINED_FLAG in VARCHAR2,
105 X_SELECTION_SET_NAME in VARCHAR2,
106 X_SELECTION_SET_DESC in VARCHAR2
107 ) is
108 cursor c is select
109 STRUCTURE_CODE,
110 SOURCE_INSTANCE,
111 PREDEFINED_FLAG
112 from AZ_SELECTION_SETS_B
113 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
114 and USER_ID = X_USER_ID
115 for update of SELECTION_SET_CODE nowait;
116 recinfo c%rowtype;
117
118 cursor c1 is select
119 SELECTION_SET_NAME,
120 SELECTION_SET_DESC,
121 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
122 from AZ_SELECTION_SETS_TL
123 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
124 and USER_ID = X_USER_ID
125 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
126 for update of SELECTION_SET_CODE nowait;
127 begin
128 open c;
129 fetch c into recinfo;
130 if (c%notfound) then
131 close c;
132 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
133 app_exception.raise_exception;
134 end if;
135 close c;
136 if ( (recinfo.STRUCTURE_CODE = X_STRUCTURE_CODE)
137 AND ((recinfo.SOURCE_INSTANCE = X_SOURCE_INSTANCE)
138 OR ((recinfo.SOURCE_INSTANCE is null) AND (X_SOURCE_INSTANCE is null)))
139 AND (recinfo.PREDEFINED_FLAG = X_PREDEFINED_FLAG)
140 ) then
141 null;
142 else
143 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
144 app_exception.raise_exception;
145 end if;
146
147 for tlinfo in c1 loop
148 if (tlinfo.BASELANG = 'Y') then
149 if ( (tlinfo.SELECTION_SET_NAME = X_SELECTION_SET_NAME)
150 AND ((tlinfo.SELECTION_SET_DESC = X_SELECTION_SET_DESC)
151 OR ((tlinfo.SELECTION_SET_DESC is null) AND (X_SELECTION_SET_DESC is null)))
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158 end if;
159 end loop;
160 return;
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
164 X_SELECTION_SET_CODE in VARCHAR2,
165 X_USER_ID in NUMBER,
166 X_STRUCTURE_CODE in VARCHAR2,
167 X_ACTIVE in VARCHAR2,
168 X_HIERARCHICAL_FLAG in VARCHAR2,
169 X_PARTIAL_SELECTION in VARCHAR2,
170 X_SOURCE_INSTANCE in VARCHAR2,
171 X_PREDEFINED_FLAG in VARCHAR2,
172 X_SELECTION_SET_NAME in VARCHAR2,
173 X_SELECTION_SET_DESC in VARCHAR2,
174 X_LAST_UPDATE_DATE in DATE,
175 X_LAST_UPDATED_BY in NUMBER,
176 X_LAST_UPDATE_LOGIN in NUMBER
177 ) is
178 begin
179 update AZ_SELECTION_SETS_B set
180 STRUCTURE_CODE = X_STRUCTURE_CODE,
181 ACTIVE = X_ACTIVE,
182 HIERARCHICAL_FLAG = X_HIERARCHICAL_FLAG,
183 PARTIAL_SELECTION = X_PARTIAL_SELECTION,
184 SOURCE_INSTANCE = X_SOURCE_INSTANCE,
185 PREDEFINED_FLAG = X_PREDEFINED_FLAG,
186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
189 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
190 and USER_ID = X_USER_ID;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195
196 update AZ_SELECTION_SETS_TL set
197 SELECTION_SET_NAME = X_SELECTION_SET_NAME,
198 SELECTION_SET_DESC = X_SELECTION_SET_DESC,
199 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
200 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
201 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
202 SOURCE_LANG = userenv('LANG')
203 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
204 and USER_ID = X_USER_ID
205 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end UPDATE_ROW;
211
212 procedure DELETE_ROW (
213 X_SELECTION_SET_CODE in VARCHAR2,
214 X_USER_ID in NUMBER
215 ) is
216 begin
217 delete from AZ_SELECTION_SETS_TL
218 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
219 and USER_ID = X_USER_ID;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 delete from AZ_SELECTION_SETS_B
226 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
227 and USER_ID = X_USER_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232 end DELETE_ROW;
233
234 procedure ADD_LANGUAGE
235 is
236 begin
237 delete from AZ_SELECTION_SETS_TL T
238 where not exists
239 (select NULL
240 from AZ_SELECTION_SETS_B B
241 where B.SELECTION_SET_CODE = T.SELECTION_SET_CODE
242 and B.USER_ID = T.USER_ID
243 );
244
245 update AZ_SELECTION_SETS_TL T set (
246 SELECTION_SET_NAME,
247 SELECTION_SET_DESC
248 ) = (select
249 B.SELECTION_SET_NAME,
250 B.SELECTION_SET_DESC
251 from AZ_SELECTION_SETS_TL B
252 where B.SELECTION_SET_CODE = T.SELECTION_SET_CODE
253 and B.USER_ID = T.USER_ID
254 and B.LANGUAGE = T.SOURCE_LANG)
255 where (
256 T.SELECTION_SET_CODE,
257 T.USER_ID,
258 T.LANGUAGE
259 ) in (select
260 SUBT.SELECTION_SET_CODE,
261 SUBT.USER_ID,
262 SUBT.LANGUAGE
263 from AZ_SELECTION_SETS_TL SUBB, AZ_SELECTION_SETS_TL SUBT
264 where SUBB.SELECTION_SET_CODE = SUBT.SELECTION_SET_CODE
265 and SUBB.USER_ID = SUBT.USER_ID
266 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
267 and (SUBB.SELECTION_SET_NAME <> SUBT.SELECTION_SET_NAME
268 or SUBB.SELECTION_SET_DESC <> SUBT.SELECTION_SET_DESC
269 or (SUBB.SELECTION_SET_DESC is null and SUBT.SELECTION_SET_DESC is not null)
270 or (SUBB.SELECTION_SET_DESC is not null and SUBT.SELECTION_SET_DESC is null)
271 ));
272
273 insert into AZ_SELECTION_SETS_TL (
274 LAST_UPDATE_LOGIN,
275 SELECTION_SET_CODE,
276 USER_ID,
277 SELECTION_SET_NAME,
278 SELECTION_SET_DESC,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LANGUAGE,
284 SOURCE_LANG
285 ) select
286 B.LAST_UPDATE_LOGIN,
287 B.SELECTION_SET_CODE,
288 B.USER_ID,
289 B.SELECTION_SET_NAME,
290 B.SELECTION_SET_DESC,
291 B.CREATED_BY,
292 B.CREATION_DATE,
293 B.LAST_UPDATED_BY,
294 B.LAST_UPDATE_DATE,
295 L.LANGUAGE_CODE,
296 B.SOURCE_LANG
297 from AZ_SELECTION_SETS_TL B, FND_LANGUAGES L
298 where L.INSTALLED_FLAG in ('I', 'B')
299 and B.LANGUAGE = userenv('LANG')
300 and not exists
301 (select NULL
302 from AZ_SELECTION_SETS_TL T
303 where T.SELECTION_SET_CODE = B.SELECTION_SET_CODE
304 and T.USER_ID = B.USER_ID
305 and T.LANGUAGE = L.LANGUAGE_CODE);
306 end ADD_LANGUAGE;
307
308 procedure TRANSLATE_ROW (
309 X_SELECTION_SET_CODE in VARCHAR2,
310 X_USER_ID in NUMBER ,
311 X_SELECTION_SET_NAME in VARCHAR2,
312 X_OWNER in VARCHAR2,
313 X_SELECTION_SET_DESC in VARCHAR2 ) is
314 begin
315 update AZ_SELECTION_SETS_TL set
316 SELECTION_SET_NAME = X_SELECTION_SET_NAME,
317 SELECTION_SET_DESC = X_SELECTION_SET_DESC,
318 last_update_date = sysdate,
319 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
320 last_update_login = 0,
321 source_lang = userenv('LANG')
322 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
323 and USER_ID = X_USER_ID
324 and userenv('LANG') in (language, source_lang);
325
326 end TRANSLATE_ROW;
327
328 procedure LOAD_ROW (
329 X_SELECTION_SET_CODE in VARCHAR2,
330 X_USER_ID in NUMBER,
331 X_OWNER in VARCHAR2,
332 X_STRUCTURE_CODE in VARCHAR2,
333 X_ACTIVE in VARCHAR2,
334 X_HIERARCHICAL_FLAG in VARCHAR2,
335 X_PARTIAL_SELECTION in VARCHAR2,
336 X_SOURCE_INSTANCE in VARCHAR2,
337 X_PREDEFINED_FLAG in VARCHAR2,
338 X_SELECTION_SET_NAME in VARCHAR2,
339 X_SELECTION_SET_DESC in VARCHAR2) IS
340 begin
341 declare
342 l_owner_id number := 0;
343 l_row_id varchar2(64);
344 luby number := null;
345 begin
346 if (X_OWNER = 'SEED') then
347 l_owner_id := 1;
348 end if;
349
350 select last_updated_by into luby
351 from AZ_SELECTION_SETS_B
352 where SELECTION_SET_CODE = X_SELECTION_SET_CODE
353 and USER_ID = X_USER_ID;
354
355 IF luby = 1 THEN
356 AZ_SELECTION_SETS_PKG.UPDATE_ROW(
357 X_SELECTION_SET_CODE => X_SELECTION_SET_CODE,
358 X_USER_ID => X_USER_ID,
359 X_STRUCTURE_CODE => X_STRUCTURE_CODE,
360 X_ACTIVE => X_ACTIVE,
361 X_HIERARCHICAL_FLAG => X_HIERARCHICAL_FLAG,
362 X_PARTIAL_SELECTION => X_PARTIAL_SELECTION,
363 X_SOURCE_INSTANCE => X_SOURCE_INSTANCE,
364 X_PREDEFINED_FLAG => X_PREDEFINED_FLAG,
365 X_SELECTION_SET_NAME => X_SELECTION_SET_NAME,
366 X_SELECTION_SET_DESC => X_SELECTION_SET_DESC,
367 X_LAST_UPDATE_DATE => sysdate,
368 X_LAST_UPDATED_BY => l_owner_id,
369 X_LAST_UPDATE_LOGIN => 0
370 );
371 END IF; -- if luby = 1
372
373 exception
374 when NO_DATA_FOUND then
375
376 AZ_SELECTION_SETS_PKG.INSERT_ROW(
377 X_ROWID => l_row_id,
378 X_SELECTION_SET_CODE => X_SELECTION_SET_CODE,
379 X_USER_ID => X_USER_ID,
380 X_STRUCTURE_CODE => X_STRUCTURE_CODE,
381 X_ACTIVE => X_ACTIVE,
382 X_HIERARCHICAL_FLAG => X_HIERARCHICAL_FLAG,
383 X_PARTIAL_SELECTION => X_PARTIAL_SELECTION,
384 X_SOURCE_INSTANCE => X_SOURCE_INSTANCE,
385 X_PREDEFINED_FLAG => X_PREDEFINED_FLAG,
386 X_SELECTION_SET_NAME => X_SELECTION_SET_NAME,
387 X_SELECTION_SET_DESC => X_SELECTION_SET_DESC,
388 X_CREATION_DATE => sysdate,
389 X_CREATED_BY => l_owner_id,
390 X_LAST_UPDATE_DATE => sysdate,
391 X_LAST_UPDATED_BY => l_owner_id,
392 X_LAST_UPDATE_LOGIN => 0
393 );
394
395 end;
396
397 end LOAD_ROW;
398
399 end AZ_SELECTION_SETS_PKG;