[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQM_WORK_SOURCES_PKG
Source
1 package body IEU_UWQM_WORK_SOURCES_PKG as
2 /* $Header: IEUWRWSB.pls 120.1 2005/06/15 22:16:43 appldev $ */
3
4 procedure insert_row(
5 x_rowid in out NOCOPY Varchar2,
6 p_ws_id in number,
7 p_ws_type in varchar2,
8 p_distribute_to in varchar2,
9 p_distribute_from in varchar2,
10 p_distribution_function in varchar2,
11 p_not_valid_flag in varchar2,
12 p_object_code in varchar2,
13 p_ws_name in varchar2,
14 p_ws_description in varchar2,
15 p_ws_code in varchar2,
16 p_ws_enable_profile_option varchar2,
17 p_application_id number,
18 p_active_flag varchar2 DEFAULT NULL
19
20 ) is
21 cursor C is select ROWID from IEU_UWQM_WORK_SOURCES_B
22 where WS_ID = p_ws_id;
23
24 -- l_ws_id number;
25
26 begin
27
28 -- select IEU_UWQM_WORK_SOURCES_B_S1.NEXTVAL into l_ws_id from sys.dual;
29
30 insert into IEU_UWQM_WORK_SOURCES_B(
31 ws_id,
32 ws_type,
33 distribute_to,
34 distribute_from,
35 distribution_function,
36 not_valid_flag,
37 object_code,
38 OBJECT_VERSION_NUMBER,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATE_LOGIN,
44 ws_code,
45 ws_enable_profile_option,
46 application_id,
47 active_flag
48 )
49 VALUES(
50 p_ws_id,
51 p_ws_type,
52 p_distribute_to,
53 p_distribute_from,
54 p_distribution_function,
55 p_not_valid_flag,
56 p_object_code,
57 1,
58 fnd_global.user_id,
59 sysdate,
60 fnd_global.user_id,
61 sysdate,
62 fnd_global.login_id,
63 p_ws_code,
64 p_ws_enable_profile_option,
65 p_application_id,
66 p_active_flag
67 );
68
69 insert into IEU_UWQM_WORK_SOURCES_TL (
70 ws_id,
71 OBJECT_VERSION_NUMBER,
72 CREATED_BY,
73 CREATION_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_DATE,
76 LAST_UPDATE_LOGIN,
77 ws_name,
78 ws_description,
79 LANGUAGE,
80 SOURCE_LANG
81 ) select
82 p_ws_id,
83 1,
84 fnd_global.user_id,
85 sysdate,
86 fnd_global.user_id,
87 sysdate,
88 fnd_global.login_id,
89 p_ws_name,
90 p_ws_description,
91 l.language_code,
92 userenv('LANG')
93 from fnd_languages l
94 where l.installed_flag in ('I', 'B')
95 and not exists
96 (select null from ieu_uwqm_work_sources_tl t
97 where t.ws_id = p_ws_id
98 and t.language = l.language_code);
99
100 open c;
101 fetch c into x_rowid;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 END INSERT_ROW;
109
110
111 procedure lock_row(
112 p_ws_id in number,
113 p_ws_type in varchar2,
114 p_distribute_to in varchar2,
115 p_distribute_from in varchar2,
116 p_distribution_function in varchar2,
117 p_not_valid_flag in varchar2,
118 p_object_code in varchar2,
119 p_ws_name in varchar2,
120 p_ws_description in varchar2,
121 p_ws_enable_profile_option varchar2,
122 p_application_id number,
123 p_object_version_number in number
124 ) is
125 cursor c is
126 select object_version_number, ws_type, distribute_to, distribute_from,
127 distribution_function, not_valid_flag, object_code, ws_enable_profile_option, application_id
128 from ieu_uwqm_work_sources_b
129 where ws_id = p_ws_id
130 for update of ws_id nowait;
131 recinfo c%rowtype;
132
133 cursor c1 is
134 select ws_name, ws_description,
135 decode(language, userenv('LANG'), 'Y', 'N') BASELANG
136 from ieu_uwqm_work_sources_tl
137 where ws_id = p_ws_id
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139 for update of ws_id nowait;
140
141 begin
142 open c;
143 fetch c into recinfo;
144
145 if (c%notfound) then
146 close c;
147 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
148 app_exception.raise_exception;
149 end if;
150
151 close c;
152
153 if ((recinfo.object_version_number = p_object_version_number)
154 AND (recinfo.ws_type = p_ws_type)
155 AND (recinfo.distribute_to = p_distribute_to)
156 AND (recinfo.distribute_from = p_distribute_from)
157 AND (recinfo.distribution_function = p_distribution_function)
158 AND (recinfo.not_valid_flag = p_not_valid_flag)
159 AND (recinfo.object_code = p_object_code)
160 AND (recinfo.ws_enable_profile_option = p_ws_enable_profile_option)
161 AND (recinfo.application_id = p_application_id))
162 then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168
169 for tlinfo in c1 loop
170 if (tlinfo.BASELANG = 'Y') then
171 if ((tlinfo.ws_name = p_ws_name)
172 and (tlinfo.ws_description = p_ws_description))
173 then
174 null;
175 else
176 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
177 app_exception.raise_exception;
178 end if;
179 end if;
180 end loop;
181 return;
182
183 END LOCK_ROW;
184
185 procedure update_row(
186 p_ws_id in number,
187 p_ws_type in varchar2,
188 p_distribute_to in varchar2,
189 p_distribute_from in varchar2,
190 p_distribution_function in varchar2,
191 p_not_valid_flag in varchar2,
192 p_object_code in varchar2,
193 p_ws_name in varchar2,
194 p_ws_description in varchar2,
195 p_ws_enable_profile_option varchar2,
196 p_application_id number,
197 p_active_flag varchar2 DEFAULT NULL
198 ) is
199
200 l_active_flag varchar2(1);
201 begin
202 begin
203 select active_flag
204 into l_active_flag
205 from ieu_uwqm_work_sources_b
206 where ws_id = p_ws_id;
207 exception
208 when others then
209 l_active_flag := null;
210 end;
211
212 if l_active_flag is null
213 then
214 l_active_flag := p_active_flag;
215 end if;
216
217 /****** Old code ******
218 if p_active_flag is null then
219 begin
220 select active_flag
221 into l_active_flag
222 from ieu_uwqm_work_sources_b
223 where ws_id = p_ws_id;
224 exception when others then null;
225 end;
226 elsif p_active_flag is not null then
227 l_active_flag := p_active_flag;
228 end if;
229 *********************/
230
231 update IEU_UWQM_WORK_SOURCES_B set
232 object_version_number = object_version_number+1,
233 ws_type = p_ws_type,
234 distribute_to = p_distribute_to,
235 distribute_from = p_distribute_from,
236 distribution_function = p_distribution_function,
237 not_valid_flag = p_not_valid_flag,
238 object_code = p_object_code,
239 last_update_date = sysdate,
240 last_updated_by = fnd_global.user_id,
241 last_update_login = fnd_global.login_id,
242 ws_enable_profile_option = p_ws_enable_profile_option,
243 application_id = p_application_id,
244 active_flag = l_active_flag
245 where ws_id = p_ws_id;
246
247
248 if (sql%notfound) then
249 raise no_data_found;
250 end if;
251
252 update IEU_UWQM_WORK_SOURCES_TL set
253 ws_name = p_ws_name,
254 ws_description = p_ws_description,
255 last_update_date = sysdate,
256 last_updated_by = fnd_global.user_id,
257 last_update_login = fnd_global.login_id,
258 object_version_number = object_version_number+1,
259 source_lang = userenv('LANG')
260 where ws_id = p_ws_id
261 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
262
263 if (sql%notfound) then
264 raise no_data_found;
265 end if;
266
267 END UPDATE_ROW;
268
269 procedure delete_row(
270 p_ws_id in number
271 ) is
272 begin
273 delete from IEU_UWQM_WORK_SOURCES_TL
274 where ws_id = p_ws_id;
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279
280 delete from ieu_uwqm_work_sources_b
281 where ws_id = p_ws_id;
282
283 if (sql%notfound) then
284 raise no_data_found;
285 end if;
286
287 END DELETE_ROW;
288
289
290 procedure add_language is
291
292 begin
293 delete from IEU_UWQM_WORK_SOURCES_TL t
294 where not exists
295 (select null
296 from ieu_uwqm_work_sources_b b
297 where b.ws_id = t.ws_id);
298
299 update ieu_uwqm_work_sources_tl t
300 set (ws_name, ws_description)
301 = (select b.ws_name,
302 b.ws_description
303 from ieu_uwqm_work_sources_tl b
304 where b.ws_id = t.ws_id
305 and b.language= t.source_lang)
306 where ( t.ws_id, t.language )
307 in (select subt.ws_id, subt.language
308 from ieu_uwqm_work_sources_tl subb, ieu_uwqm_work_sources_tl subt
309 where subb.ws_id = subt.ws_id
310 and subb.language = subt.source_lang
311 and (subb.ws_name <> subt.ws_name
312 or subb.ws_description <> subt.ws_description));
313
314 insert into ieu_uwqm_work_sources_tl(
315 ws_ID,
316 OBJECT_VERSION_NUMBER,
317 CREATED_BY,
318 CREATION_DATE,
319 LAST_UPDATED_BY,
320 LAST_UPDATE_DATE,
321 LAST_UPDATE_LOGIN,
322 ws_name,
323 ws_DESCription,
324 LANGUAGE,
325 SOURCE_LANG
326 ) select
327 b.ws_id,
328 b.object_version_number,
329 fnd_global.user_id,
330 sysdate,
331 fnd_global.user_id,
332 sysdate,
333 fnd_global.login_id,
334 b.ws_name,
335 b.ws_description,
336 l.language_code,
337 b.source_lang
338 from ieu_uwqm_work_sources_tl b, fnd_languages l
339 where l.installed_flag in ('I', 'B')
340 and b.language= userenv('LANG')
341 and not exists
342 (select null from ieu_uwqm_work_sources_tl t
343 where t.ws_id = b.ws_id
344 and t.language = l.language_code);
345
346 END ADD_LANGUAGE;
347
348
349 procedure load_row(
350 p_ws_id in number,
351 p_ws_type in varchar2,
352 p_distribute_to in varchar2,
353 p_distribute_from in varchar2,
354 p_distribution_function in varchar2,
355 p_not_valid_flag in varchar2,
356 p_object_code in varchar2,
357 p_ws_name in varchar2,
358 p_ws_description in varchar2,
359 p_owner in varchar2,
360 p_ws_code in varchar2,
361 p_ws_enable_profile_option varchar2,
362 p_application_id number,
363 p_active_flag varchar2 DEFAULT NULL
364 ) is
365
366 l_user_id number := 0;
367 l_rowid varchar2(50);
368
369 begin
370 if (p_owner = 'SEED') then
371 l_user_id := 1;
372 end if;
373 begin
374 update_row(
375 p_ws_id => p_ws_id,
376 p_ws_type => p_ws_type,
377 p_distribute_to => p_distribute_to,
378 p_distribute_from => p_distribute_from,
379 p_distribution_function => p_distribution_function,
380 p_not_valid_flag => p_not_valid_flag,
381 p_object_code => p_object_code,
382 p_ws_name => p_ws_name,
383 p_ws_description => p_ws_description,
384 p_ws_enable_profile_option => p_ws_enable_profile_option,
385 p_application_id => p_application_id,
386 p_active_flag => p_active_flag);
387
388 if (sql%notfound) then
389 raise no_data_found;
390 end if;
391
392 exception when no_data_found then
393 insert_row(
394 x_rowid => l_rowid,
395 p_ws_id => p_ws_id,
396 p_ws_type => p_ws_type,
397 p_distribute_to => p_distribute_to,
398 p_distribute_from => p_distribute_from,
399 p_distribution_function => p_distribution_function,
400 p_not_valid_flag => p_not_valid_flag,
401 p_object_code => p_object_code,
402 p_ws_name => p_ws_name,
403 p_ws_description => p_ws_description,
404 p_ws_code => p_ws_code,
405 p_ws_enable_profile_option => p_ws_enable_profile_option,
406 p_application_id => p_application_id,
407 p_active_flag => p_active_flag);
408
409 end;
410
411 END LOAD_ROW;
412
413 procedure translate_row(
414 p_ws_id in number,
415 p_ws_name in varchar2,
416 p_ws_description in varchar2,
417 p_owner in varchar2
418 ) is
419 begin
420 update IEU_UWQM_WORK_SOURCES_TL
421 set source_lang = userenv('LANG'),
422 ws_name = p_ws_name,
423 ws_description = p_ws_description,
424 last_update_date = sysdate,
425 --last_updated_by = decode(p_owner, 'SEED', 1, 0),
426 last_updated_by = fnd_load_util.owner_id(p_owner),
427 last_update_login = 0
428 where (ws_id = p_ws_id)
429 and (userenv('LANG') IN (LANGUAGE, SOURCE_LANG));
430
431 if (sql%notfound) then
432 raise no_data_found;
433 end if;
434
435 END TRANSLATE_ROW;
436
437 procedure load_seed_row(
438 p_upload_mode in varchar2,
439 p_ws_id in number,
440 p_ws_type in varchar2,
441 p_distribute_to in varchar2,
442 p_distribute_from in varchar2,
443 p_distribution_function in varchar2,
444 p_not_valid_flag in varchar2,
445 p_object_code in varchar2,
446 p_ws_name in varchar2,
447 p_ws_description in varchar2,
448 p_owner in varchar2,
449 p_ws_code in varchar2,
450 p_ws_enable_profile_option varchar2,
451 p_application_id number,
452 p_active_flag varchar2 DEFAULT NULL
453 ) is
454 begin
455
456 if (p_upload_mode = 'NLS') then
457 translate_row(
458 p_ws_id,
459 p_ws_name,
460 p_ws_description,
461 p_owner);
462 else
463 load_row(
464 p_ws_id,
465 p_ws_type,
466 p_distribute_to,
467 p_distribute_from,
468 p_distribution_function,
469 p_not_valid_flag,
470 p_object_code,
471 p_ws_name,
472 p_ws_description,
473 p_owner,
474 p_ws_code,
475 p_ws_enable_profile_option,
476 p_application_id,
477 p_active_flag);
478 end if;
479
480 end load_seed_row;
481
482 END IEU_UWQM_WORK_SOURCES_PKG;