[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_MACTION_DEFS_SEED_PKG
Source
1 PACKAGE BODY IEU_UWQ_MACTION_DEFS_SEED_PKG AS
2 /* $Header: IEUMACTB.pls 120.1 2005/07/07 02:18:38 appldev ship $ */
3 PROCEDURE Insert_Row (p_uwq_maction_defs_rec IN uwq_maction_defs_rec_type) IS
4
5 CURSOR c IS SELECT 'X' FROM ieu_uwq_maction_defs_b
6 WHERE maction_def_id = p_uwq_maction_defs_rec.maction_def_id;
7
8 l_dummy CHAR(1);
9
10 BEGIN
11
12 -- API body
13 INSERT INTO ieu_uwq_maction_defs_b (
14 maction_def_id,
15 created_by,
16 creation_date,
17 last_updated_by,
18 last_update_date,
19 last_update_login,
20 action_proc,
21 ACTION_PROC_TYPE_CODE,
22 maction_def_type_flag,
23 global_form_params,
24 multi_select_flag,
25 maction_def_key,
26 application_id
27 ) VALUES (
28 p_uwq_maction_defs_rec.maction_def_id,
29 p_uwq_maction_defs_rec.created_by,
30 p_uwq_maction_defs_rec.creation_date,
31 p_uwq_maction_defs_rec.last_updated_by,
32 p_uwq_maction_defs_rec.last_update_date,
33 p_uwq_maction_defs_rec.last_update_login,
34 p_uwq_maction_defs_rec.action_proc,
35 p_uwq_maction_defs_rec.ACTION_PROC_TYPE_CODE,
36 p_uwq_maction_defs_rec.maction_def_type_flag,
37 p_uwq_maction_defs_rec.global_form_params,
38 p_uwq_maction_defs_rec.multi_select_flag,
39 p_uwq_maction_defs_rec.maction_def_key,
40 p_uwq_maction_defs_rec.application_id
41 );
42
43 INSERT INTO ieu_uwq_maction_defs_tl (
44 maction_def_id,
45 language,
46 created_by,
47 creation_date,
48 last_updated_by,
49 last_update_date,
50 last_update_login,
51 action_user_label,
52 source_lang,
53 action_description
54 ) SELECT
55 p_uwq_maction_defs_rec.maction_def_id,
56 l.language_code,
57 p_uwq_maction_defs_rec.created_by,
58 p_uwq_maction_defs_rec.creation_date,
59 p_uwq_maction_defs_rec.last_updated_by,
60 p_uwq_maction_defs_rec.last_update_date,
61 p_uwq_maction_defs_rec.last_update_login,
62 p_uwq_maction_defs_rec.action_user_label,
63 USERENV('LANG'),
64 p_uwq_maction_defs_rec.action_description
65 FROM fnd_languages l
66 WHERE l.installed_flag IN ('I', 'B')
67 AND NOT EXISTS
68 (SELECT NULL
69 FROM ieu_uwq_maction_defs_tl t
70 WHERE t.maction_def_id = p_uwq_maction_defs_rec.maction_def_id
71 AND t.language = l.language_code);
72
73 OPEN c;
74 FETCH c INTO l_dummy;
75 IF (c%NOTFOUND) THEN
76 CLOSE c;
77 RAISE NO_DATA_FOUND;
78 END IF;
79 CLOSE c;
80 -- End of API body
81
82 END Insert_Row;
83
84 PROCEDURE Update_Row (p_uwq_maction_defs_rec IN uwq_maction_defs_rec_type) IS
85
86 BEGIN
87 -- API body
88 UPDATE ieu_uwq_maction_defs_b SET
89 last_updated_by = p_uwq_maction_defs_rec.last_updated_by,
90 last_update_date = p_uwq_maction_defs_rec.last_update_date,
91 last_update_login = p_uwq_maction_defs_rec.last_update_login,
92 action_proc = p_uwq_maction_defs_rec.action_proc,
93 ACTION_PROC_TYPE_CODE = p_uwq_maction_defs_rec.ACTION_PROC_TYPE_CODE,
94 MACTION_DEF_TYPE_FLAG = p_uwq_maction_defs_rec.maction_def_type_flag,
95 GLOBAL_FORM_PARAMS = p_uwq_maction_defs_rec.global_form_params,
96 MULTI_SELECT_FLAG = p_uwq_maction_defs_rec.multi_select_flag,
97 MACTION_DEF_KEY = p_uwq_maction_defs_rec.maction_def_key
98
99 WHERE maction_def_id = p_uwq_maction_defs_rec.maction_def_id;
100
101 IF (SQL%NOTFOUND) THEN
102 RAISE no_data_found;
103 END IF;
104
105 UPDATE ieu_uwq_maction_defs_tl SET
106 action_user_label = p_uwq_maction_defs_rec.action_user_label,
107 source_lang = USERENV('LANG'),
108 action_description = p_uwq_maction_defs_rec.action_description,
109 last_updated_by = p_uwq_maction_defs_rec.last_updated_by,
110 last_update_date = p_uwq_maction_defs_rec.last_update_date,
111 last_update_login = p_uwq_maction_defs_rec.last_update_login
112 WHERE maction_def_id = p_uwq_maction_defs_rec.maction_def_id
113 AND USERENV('LANG') IN (language, source_lang);
114
115 IF (SQL%NOTFOUND) THEN
116 RAISE no_data_found;
117 END IF;
118 -- End of API body
119
120 END Update_Row;
121
122 PROCEDURE Load_Row (
123
124 p_maction_def_id IN NUMBER,
125 p_action_proc IN VARCHAR2,
126 p_ACTION_PROC_TYPE_CODE IN VARCHAR2,
127 p_MACTION_DEF_TYPE_FLAG IN VARCHAR2,
128 p_GLOBAL_FORM_PARAMS IN VARCHAR2,
129 p_MULTI_SELECT_FLAG IN VARCHAR2,
130 p_MACTION_DEF_KEY IN VARCHAR2,
131 p_last_update_date IN VARCHAR2,
132 p_application_short_name IN VARCHAR2,
133 p_action_user_label IN VARCHAR2,
134 p_action_description IN VARCHAR2,
135 p_owner IN VARCHAR2) IS
136 BEGIN
137
138 DECLARE
139 user_id number := 0;
140 l_uwq_maction_defs_rec uwq_maction_defs_rec_type;
141 l_last_update_date date;
142 p_application_id number(15);
143
144 BEGIN
145
146 --IF (p_owner = 'SEED') then
147 -- user_id := -1;
148 --END IF;
149
150 user_id := fnd_load_util.owner_id(P_OWNER);
151
152 select a.application_id
153 into p_application_id
154 from fnd_application a
155 where a.application_short_name = p_application_short_name;
156
157 if (p_last_update_date is null) then
158 l_last_update_date := sysdate;
159 else
160 l_last_update_date := to_date(p_last_update_date, 'YYYY/MM/DD');
161 end if;
162
163 l_uwq_maction_defs_rec.maction_def_id := p_maction_def_id;
164 l_uwq_maction_defs_rec.action_proc := p_action_proc;
165 l_uwq_maction_defs_rec.ACTION_PROC_TYPE_CODE := p_ACTION_PROC_TYPE_CODE;
166 l_uwq_maction_defs_rec.maction_def_type_flag := p_MACTION_DEF_TYPE_FLAG ;
167 l_uwq_maction_defs_rec.global_form_params := p_GLOBAL_FORM_PARAMS;
168 l_uwq_maction_defs_rec.multi_select_flag := p_MULTI_SELECT_FLAG;
169 l_uwq_maction_defs_rec.maction_def_key := p_MACTION_DEF_KEY;
170 l_uwq_maction_defs_rec.application_id := p_application_id;
171 l_uwq_maction_defs_rec.action_user_label := p_action_user_label;
172 l_uwq_maction_defs_rec.action_description := p_action_description;
173 l_uwq_maction_defs_rec.last_update_date := l_last_update_date;
174 l_uwq_maction_defs_rec.last_updated_by := user_id;
175 l_uwq_maction_defs_rec.last_update_login := 0;
176
177 Update_Row (p_uwq_maction_defs_rec => l_uwq_maction_defs_rec);
178 EXCEPTION
179 when no_data_found then
180
181 l_uwq_maction_defs_rec.maction_def_id := p_maction_def_id;
182 l_uwq_maction_defs_rec.action_proc := p_action_proc;
183 l_uwq_maction_defs_rec.ACTION_PROC_TYPE_CODE := p_ACTION_PROC_TYPE_CODE;
184 l_uwq_maction_defs_rec.maction_def_type_flag := p_MACTION_DEF_TYPE_FLAG ;
185 l_uwq_maction_defs_rec.global_form_params := p_GLOBAL_FORM_PARAMS;
186 l_uwq_maction_defs_rec.multi_select_flag := p_MULTI_SELECT_FLAG;
187 l_uwq_maction_defs_rec.maction_def_key := p_MACTION_DEF_KEY;
188 l_uwq_maction_defs_rec.application_id := p_application_id;
189 l_uwq_maction_defs_rec.action_user_label := p_action_user_label;
190 l_uwq_maction_defs_rec.action_description := p_action_description;
191 l_uwq_maction_defs_rec.last_update_date := l_last_update_date;
192 l_uwq_maction_defs_rec.last_updated_by := user_id;
193 l_uwq_maction_defs_rec.last_update_login := 0;
194 l_uwq_maction_defs_rec.creation_date := sysdate;
195 l_uwq_maction_defs_rec.created_by := user_id;
196
197 Insert_Row (p_uwq_maction_defs_rec => l_uwq_maction_defs_rec);
198
199 END;
200 END load_row;
201
202 PROCEDURE translate_row (
203 p_maction_def_id IN NUMBER,
204 p_action_user_label IN VARCHAR2,
205 p_action_description IN VARCHAR2,
206 p_last_update_date IN VARCHAR2,
207 p_owner IN VARCHAR2) IS
208 user_id number := 0;
209 BEGIN
210
211 -- only UPDATE rows that have not been altered by user
212
213 user_id := fnd_load_util.owner_id(P_OWNER);
214
215 UPDATE ieu_uwq_maction_defs_tl SET
216 action_user_label = p_action_user_label,
217 source_lang = userenv('LANG'),
218 action_description = p_action_description,
219 last_update_date = decode(p_last_update_date, null, sysdate, to_date(p_last_update_date, 'YYYY/MM/DD')),
220 --last_updated_by = decode(p_owner, 'SEED', -1, 0),
221 last_updated_by = user_id,
222 last_update_login = 0
223 WHERE maction_def_id = p_maction_def_id
224 AND userenv('LANG') IN (language, source_lang);
225 end translate_row;
226
227 procedure ADD_LANGUAGE
228 is
229 begin
230 delete from IEU_UWQ_MACTION_DEFS_TL T
231 where not exists
232 (select NULL
233 from IEU_UWQ_MACTION_DEFS_B B
234 where B.MACTION_DEF_ID = T.MACTION_DEF_ID
235 );
236
237 update IEU_UWQ_MACTION_DEFS_TL T set (
238 ACTION_USER_LABEL,
239 ACTION_DESCRIPTION
240 ) = (select
241 B.ACTION_USER_LABEL,
242 B.ACTION_DESCRIPTION
243 from IEU_UWQ_MACTION_DEFS_TL B
244 where B.MACTION_DEF_ID = T.MACTION_DEF_ID
245 and B.LANGUAGE = T.SOURCE_LANG)
246 where (
247 T.MACTION_DEF_ID,
248 T.LANGUAGE
249 ) in (select
250 SUBT.MACTION_DEF_ID,
251 SUBT.LANGUAGE
252 from IEU_UWQ_MACTION_DEFS_TL SUBB, IEU_UWQ_MACTION_DEFS_TL SUBT
253 where SUBB.MACTION_DEF_ID = SUBT.MACTION_DEF_ID
254 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
255 and (SUBB.ACTION_USER_LABEL <> SUBT.ACTION_USER_LABEL
256 or SUBB.ACTION_DESCRIPTION <> SUBT.ACTION_DESCRIPTION
257 or (SUBB.ACTION_DESCRIPTION is null and SUBT.ACTION_DESCRIPTION is not null)
258 or (SUBB.ACTION_DESCRIPTION is not null and SUBT.ACTION_DESCRIPTION is null)
259 ));
260
261 insert into IEU_UWQ_MACTION_DEFS_TL (
262 ACTION_DESCRIPTION,
263 CREATION_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_DATE,
266 LAST_UPDATE_LOGIN,
267 ACTION_USER_LABEL,
268 MACTION_DEF_ID,
269 CREATED_BY,
270 object_version_number,
271 LANGUAGE,
272 SOURCE_LANG
273 ) select
274 B.ACTION_DESCRIPTION,
275 B.CREATION_DATE,
276 B.LAST_UPDATED_BY,
277 B.LAST_UPDATE_DATE,
278 B.LAST_UPDATE_LOGIN,
279 B.ACTION_USER_LABEL,
280 B.MACTION_DEF_ID,
281 B.CREATED_BY,
282 1,
283 L.LANGUAGE_CODE,
284 B.SOURCE_LANG
285 from IEU_UWQ_MACTION_DEFS_TL B, FND_LANGUAGES L
286 where L.INSTALLED_FLAG in ('I', 'B')
287 and B.LANGUAGE = userenv('LANG')
288 and not exists
289 (select NULL
290 from IEU_UWQ_MACTION_DEFS_TL T
291 where T.MACTION_DEF_ID = B.MACTION_DEF_ID
292 and T.LANGUAGE = L.LANGUAGE_CODE);
293 end ADD_LANGUAGE;
294
295 /* Modified */
296
297 procedure LOCK_ROW (
298 X_MACTION_DEF_ID in NUMBER,
299 X_ACTION_PROC in VARCHAR2,
300 X_ACTION_PROC_TYPE_CODE in VARCHAR2,
301 X_MACTION_DEF_TYPE_FLAG in VARCHAR2,
302 X_APPLICATION_ID in NUMBER,
303 X_GLOBAL_FORM_PARAMS in VARCHAR2,
304 X_ACTION_USER_LABEL in VARCHAR2,
305 X_ACTION_DESCRIPTION in VARCHAR2
306 ) is
307 cursor c is select
308 ACTION_PROC,
309 ACTION_PROC_TYPE_CODE,
310 MACTION_DEF_TYPE_FLAG,
311 APPLICATION_ID,
312 GLOBAL_FORM_PARAMS
313 from IEU_UWQ_MACTION_DEFS_B
314 where MACTION_DEF_ID = X_MACTION_DEF_ID
315 for update of MACTION_DEF_ID nowait;
316 recinfo c%rowtype;
317
318 cursor c1 is select
319 ACTION_USER_LABEL,
320 ACTION_DESCRIPTION,
321 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
322 from IEU_UWQ_MACTION_DEFS_TL
323 where MACTION_DEF_ID = X_MACTION_DEF_ID
324 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
325 for update of MACTION_DEF_ID nowait;
326 begin
327 open c;
328 fetch c into recinfo;
329 if (c%notfound) then
330 close c;
331 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
332 app_exception.raise_exception;
333 end if;
334 close c;
335 if ( (recinfo.ACTION_PROC = X_ACTION_PROC)
336 AND ( (recinfo.ACTION_PROC_TYPE_CODE = X_ACTION_PROC_TYPE_CODE) OR
337 ( (recinfo.ACTION_PROC_TYPE_CODE is NULL) AND (X_ACTION_PROC_TYPE_CODE is NULL) ) )
338 AND ( (recinfo.MACTION_DEF_TYPE_FLAG = X_MACTION_DEF_TYPE_FLAG) OR
339 ( (recinfo.MACTION_DEF_TYPE_FLAG IS NULL) AND (X_MACTION_DEF_TYPE_FLAG IS NULL) ) )
340 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
341 AND ((recinfo.GLOBAL_FORM_PARAMS = X_GLOBAL_FORM_PARAMS)
342 OR ((recinfo.GLOBAL_FORM_PARAMS is null) AND (X_GLOBAL_FORM_PARAMS is null)))
343 ) then
344 null;
345 else
346 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
347 app_exception.raise_exception;
348 end if;
349
350 for tlinfo in c1 loop
351 if (tlinfo.BASELANG = 'Y') then
352 if ( (tlinfo.ACTION_USER_LABEL = X_ACTION_USER_LABEL)
353 AND ((tlinfo.ACTION_DESCRIPTION = X_ACTION_DESCRIPTION)
354 OR ((tlinfo.ACTION_DESCRIPTION is null) AND (X_ACTION_DESCRIPTION is null)))
355 ) then
356 null;
357 else
358 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
359 app_exception.raise_exception;
360 end if;
361 end if;
362 end loop;
363 return;
364 end LOCK_ROW;
365
366 procedure DELETE_ROW (
367 X_MACTION_DEF_ID in NUMBER
368 ) is
369 begin
370 delete from IEU_UWQ_MACTION_DEFS_TL
371 where MACTION_DEF_ID = X_MACTION_DEF_ID;
372
373 if (sql%notfound) then
374 raise no_data_found;
375 end if;
376
377 delete from IEU_UWQ_MACTION_DEFS_B
378 where MACTION_DEF_ID = X_MACTION_DEF_ID;
379
380 if (sql%notfound) then
381 raise no_data_found;
382 end if;
383 end DELETE_ROW;
384
385 PROCEDURE Load_Seed_Row (
386 p_upload_mode IN VARCHAR2,
387 p_maction_def_id IN NUMBER,
388 p_action_proc IN VARCHAR2,
389 p_ACTION_PROC_TYPE_CODE IN VARCHAR2,
390 p_MACTION_DEF_TYPE_FLAG IN VARCHAR2,
391 p_GLOBAL_FORM_PARAMS IN VARCHAR2,
392 p_MULTI_SELECT_FLAG IN VARCHAR2,
393 p_MACTION_DEF_KEY IN VARCHAR2,
394 p_last_update_date IN VARCHAR2,
395 p_application_short_name IN VARCHAR2,
396 p_action_user_label IN VARCHAR2,
397 p_action_description IN VARCHAR2,
398 p_owner IN VARCHAR2
399 )is
400 begin
401
402 if (P_UPLOAD_MODE = 'NLS') then
403 TRANSLATE_ROW (
404 P_MACTION_DEF_ID,
405 P_ACTION_USER_LABEL,
406 P_ACTION_DESCRIPTION,
407 P_LAST_UPDATE_DATE,
408 P_OWNER);
409
410 else
411 LOAD_ROW (
412 P_MACTION_DEF_ID,
413 P_ACTION_PROC,
414 P_ACTION_PROC_TYPE_CODE,
415 p_MACTION_DEF_TYPE_FLAG,
416 P_GLOBAL_FORM_PARAMS,
417 P_MULTI_SELECT_FLAG,
418 p_MACTION_DEF_KEY,
419 P_LAST_UPDATE_DATE,
420 P_APPLICATION_SHORT_NAME,
421 P_ACTION_USER_LABEL,
422 P_ACTION_DESCRIPTION,
423 P_OWNER);
424 end if;
425
426 end Load_Seed_Row;
427
428 END IEU_UWQ_MACTION_DEFS_SEED_PKG;