[Home] [Help]
PACKAGE BODY: APPS.IEU_WP_UI_COMP_CATG_PKG
Source
1 package body IEU_WP_UI_COMP_CATG_PKG as
2 /* $Header: IEUVCCAB.pls 120.1 2005/06/20 01:02:17 appldev ship $ */
3
4 procedure insert_row(
5 x_rowid in out nocopy Varchar2,
6 p_ui_comp_catg_id in number,
7 p_object_version_number in number,
8 p_created_by in number,
9 p_creation_date in date,
10 p_last_updated_by in number,
11 p_last_update_date in date,
12 p_last_update_login in number,
13 p_ui_comp_catg_code in varchar2,
14 p_ui_comp_catg_label in varchar2,
15 p_ui_comp_catg_desc in varchar2
16 ) is
17 cursor C is select ROWID from IEU_WP_UI_COMP_CATG_B
18 where UI_COMP_CATG_ID = P_UI_COMP_CATG_ID;
19
20 begin
21 insert into IEU_WP_UI_COMP_CATG_B (
22 UI_COMP_CATG_ID,
23 OBJECT_VERSION_NUMBER,
24 CREATED_BY,
25 CREATION_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATE_LOGIN,
29 UI_COMP_CATG_CODE
30 )
31 VALUES(
32 p_ui_comp_catg_id,
33 p_object_version_number,
34 p_created_by,
35 p_creation_date,
36 p_last_updated_by,
37 p_last_update_date,
38 p_last_update_login,
39 p_ui_comp_catg_code
40 );
41
42 insert into IEU_WP_UI_COMP_CATG_TL (
43 UI_COMP_CATG_ID,
44 OBJECT_VERSION_NUMBER,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 UI_COMP_CATG_LABEL,
51 UI_COMP_CATG_DESC,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 p_ui_comp_catg_id,
56 p_object_version_number,
57 p_created_by,
58 p_creation_date,
59 p_last_updated_by,
60 p_last_update_date,
61 p_last_update_login,
62 p_ui_comp_catg_label,
63 p_ui_comp_catg_desc,
64 l.language_code,
65 userenv('LANG')
66 from fnd_languages l
67 where l.installed_flag in ('I', 'B')
68 and not exists
69 (select null from ieu_wp_ui_comp_catg_tl t
70 where t.ui_comp_catg_id = p_ui_comp_catg_id
71 and t.language = l.language_code);
72
73 open c;
74 fetch c into x_rowid;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 END INSERT_ROW;
82
83
84 procedure lock_row(
85 p_ui_comp_catg_id in number,
86 p_object_version_number in number,
87 p_ui_comp_catg_code in varchar2,
88 p_ui_comp_catg_label in varchar2,
89 p_ui_comp_catg_desc in varchar2
90 ) is
91 cursor c is select
92 object_version_number,
93 ui_comp_catg_code
94 from ieu_wp_ui_comp_catg_b
95 where ui_comp_catg_id = p_ui_comp_catg_id
96 for update of ui_comp_catg_id nowait;
97 recinfo c%rowtype;
98
99 cursor c1 is select
100 ui_comp_catg_label,
101 ui_comp_catg_desc,
102 decode(language, userenv('LANG'), 'Y', 'N') BASELANG
103 from ieu_wp_ui_comp_catg_tl
104 where ui_comp_catg_id = p_ui_comp_catg_id
105 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
106 for update of ui_comp_catg_id nowait;
107
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ((recinfo.object_version_number = p_object_version_number)
118 AND (recinfo.ui_comp_catg_code = p_ui_comp_catg_code))
119 then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125
126 for tlinfo in c1 loop
127 if (tlinfo.BASELANG = 'Y') then
128 if ((tlinfo.ui_comp_catg_label = p_ui_comp_catg_label)
129 and (tlinfo.ui_comp_catg_desc = p_ui_comp_catg_desc))
130 then
131 null;
132 else
133 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136 end if;
137 end loop;
138 return;
139
140 END LOCK_ROW;
141
142 procedure update_row(
143 p_ui_comp_catg_id in number,
144 p_last_updated_by in number,
145 p_last_update_date in date,
146 p_last_update_login in number,
147 p_ui_comp_catg_code in varchar2,
148 p_ui_comp_catg_label in varchar2,
149 p_ui_comp_catg_desc in varchar2
150 ) is
151 begin
152 update IEU_WP_UI_COMP_CATG_B set
153 object_version_number = object_version_number+1,
154 ui_comp_catg_code = p_ui_comp_catg_code,
155 last_update_date = p_last_update_date,
156 last_updated_by = p_last_updated_by,
157 last_update_login = p_last_update_login
158 where ui_comp_catg_id = p_ui_comp_catg_id;
159
160 if (sql%notfound) then
161 raise no_data_found;
162 end if;
163
164 update IEU_WP_UI_COMP_CATG_TL set
165 ui_comp_catg_label = p_ui_comp_catg_label,
166 ui_comp_catg_desc = p_ui_comp_catg_desc,
167 last_update_date = p_last_update_date,
168 last_updated_by = p_last_updated_by,
169 last_update_login = p_last_update_login,
170 object_version_number = object_version_number+1,
171 source_lang = userenv('LANG')
172 where ui_comp_catg_id = p_ui_comp_catg_id
173 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 END UPDATE_ROW;
180
181
182 procedure delete_row(
183 p_ui_comp_catg_id in number
184 ) is
185 begin
186 delete from IEU_WP_UI_COMP_CATG_TL
187 where ui_comp_catg_id = p_ui_comp_catg_id;
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192
193 delete from ieu_wp_ui_comp_catg_b
194 where ui_comp_catg_id = p_ui_comp_catg_id;
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199
200 END DELETE_ROW;
201
202
203 procedure add_language is
204
205 begin
206 delete from IEU_WP_UI_COMP_CATG_TL t
207 where not exists
208 (select null
209 from ieu_wp_ui_comp_catg_b b
210 where b.ui_comp_catg_id = t.ui_comp_catg_id);
211
212 update ieu_wp_ui_comp_catg_tl t
213 set (ui_comp_catg_label, ui_comp_catg_desc)
214 = (select b.ui_comp_catg_label,
215 b.ui_comp_catg_desc
216 from ieu_wp_ui_comp_catg_tl b
217 where b.ui_comp_catg_id = t.ui_comp_catg_id
218 and b.language= t.source_lang)
219 where ( t.ui_comp_catg_id, t.language )
220 in (select subt.ui_comp_catg_id, subt.language
221 from ieu_wp_ui_comp_catg_tl subb, ieu_wp_ui_comp_catg_tl subt
222 where subb.ui_comp_catg_id = subt.ui_comp_catg_id
223 and subb.language = subt.source_lang
224 and (subb.ui_comp_catg_label <> subt.ui_comp_catg_label
225 or subb.ui_comp_catg_desc <> subt.ui_comp_catg_desc));
226
227 insert into ieu_wp_ui_comp_catg_tl(
228 UI_COMP_CATG_ID,
229 OBJECT_VERSION_NUMBER,
230 CREATED_BY,
231 CREATION_DATE,
232 LAST_UPDATED_BY,
233 LAST_UPDATE_DATE,
234 LAST_UPDATE_LOGIN,
235 UI_COMP_CATG_LABEL,
236 UI_COMP_CATG_DESC,
237 LANGUAGE,
238 SOURCE_LANG
239 ) select /*+ ORDERED */
240 b.ui_comp_catg_id,
241 b.object_version_number,
242 b.created_by,
243 b.creation_date,
244 b.last_updated_by,
245 b.last_update_date,
246 b.last_update_login,
247 b.ui_comp_catg_label,
248 b.ui_comp_catg_desc,
249 l.language_code,
250 b.source_lang
251 from ieu_wp_ui_comp_catg_tl b, fnd_languages l
252 where l.installed_flag in ('I', 'B')
253 and b.language= userenv('LANG')
254 and not exists
255 (select null from ieu_wp_ui_comp_catg_tl t
256 where t.ui_comp_catg_id = b.ui_comp_catg_id
257 and t.language = l.language_code);
258
259 END ADD_LANGUAGE;
260
261
262 procedure load_row(
263 p_ui_comp_catg_id in number,
264 p_ui_comp_catg_code in varchar2,
265 p_ui_comp_catg_label in varchar2,
266 p_ui_comp_catg_desc in varchar2,
267 p_owner in varchar2
268 ) is
269
270 l_user_id number := 0;
271 l_rowid varchar2(50);
272
273 begin
274 if (p_owner = 'SEED') then
275 l_user_id := 1;
276 end if;
277
278 begin
279 update_row(
280 p_ui_comp_catg_id => p_ui_comp_catg_id,
281 --p_last_updated_by => l_user_id,
282 p_last_updated_by => fnd_load_util.owner_id(p_owner),
283 p_last_update_date => sysdate,
284 p_last_update_login => 0,
285 p_ui_comp_catg_code => p_ui_comp_catg_code,
286 p_ui_comp_catg_label => p_ui_comp_catg_label,
287 p_ui_comp_catg_desc => p_ui_comp_catg_desc);
288
289 if (sql%notfound) then
290 raise no_data_found;
291 end if;
292
293 exception when no_data_found then
294 insert_row(
295 x_rowid => l_rowid,
296 p_ui_comp_catg_id => p_ui_comp_catg_id,
297 p_object_version_number => 1,
298 --p_created_by => l_user_id,
299 p_created_by => fnd_load_util.owner_id(p_owner),
300 p_creation_date => sysdate,
301 --p_last_updated_by => l_user_id,
302 p_last_updated_by => fnd_load_util.owner_id(p_owner),
303 p_last_update_date => sysdate,
304 p_last_update_login => 0,
305 p_ui_comp_catg_code => p_ui_comp_catg_code,
306 p_ui_comp_catg_label => p_ui_comp_catg_label,
307 p_ui_comp_catg_desc => p_ui_comp_catg_desc);
308 end;
309
310 END LOAD_ROW;
311
312 procedure translate_row(
313 p_ui_comp_catg_id in number,
314 p_ui_comp_catg_label in varchar2,
315 p_ui_comp_catg_desc in varchar2,
316 p_owner in varchar2
317 ) is
318 begin
319 update IEU_WP_UI_COMP_CATG_TL
320 set source_lang = userenv('LANG'),
321 ui_comp_catg_label = p_ui_comp_catg_label,
322 ui_comp_catg_desc = p_ui_comp_catg_desc,
323 last_update_date = sysdate,
324 --last_updated_by = decode(p_owner, 'SEED', 1, 0),
325 last_updated_by = fnd_load_util.owner_id(p_owner),
326 last_update_login = 0
327 where (ui_comp_catg_id = p_ui_comp_catg_id)
328 and (userenv('LANG') IN (LANGUAGE, SOURCE_LANG));
329
330 if (sql%notfound) then
331 raise no_data_found;
332 end if;
333
334 END TRANSLATE_ROW;
335
336 procedure load_seed_row(
337 p_upload_mode in varchar2,
338 p_ui_comp_catg_id in number,
339 p_ui_comp_catg_code in varchar2,
340 p_ui_comp_catg_label in varchar2,
341 p_ui_comp_catg_desc in varchar2,
342 p_owner in varchar2
343 )is
344 begin
345
346 if (p_upload_mode = 'NLS') then
347 translate_row(
348 p_ui_comp_catg_id,
349 p_ui_comp_catg_label,
350 p_ui_comp_catg_desc,
351 p_owner);
352 else
353 load_row(
354 p_ui_comp_catg_id,
355 p_ui_comp_catg_code,
356 p_ui_comp_catg_label,
357 p_ui_comp_catg_desc,
358 p_owner);
359 end if;
360
361 end load_seed_row;
362
363 END IEU_WP_UI_COMP_CATG_PKG;