[Home] [Help]
PACKAGE BODY: APPS.IEU_WORKPANEL_PVT
Source
1 PACKAGE BODY IEU_WorkPanel_PVT AS
2 /* $Header: IEUVWPB.pls 120.2 2007/12/17 11:40:55 svidiyal ship $ */
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- IEU_WorkPanel_PVT
9 -- Purpose
10 -- To provide easy to use apis for UQW Work Panel
11 -- History
12 -- 08-May-2002 gpagadal Created.
13 -- NOTE
14 --
15 -- End of Comments
16 -- ==================================================================
17
18
19 --===================================================================
20 -- NAME
21 -- Validate_Action
22 --
23 -- PURPOSE
24 -- Private api to Validate fields.
25 --
26 -- NOTES
27 -- 1. UWQ Work Panel Admin will use this procedure to validate action
28 --
29 --
30 -- HISTORY
31 -- 08-May-2002 GPAGADAL Created
32
33 --===================================================================
34
35 PROCEDURE Validate_Action ( x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
39 is_create IN VARCHAR2,
40 p_maction_def_type_flag IN VARCHAR2,
41 p_param_set_id IN NUMBER) AS
42
43 l_language VARCHAR2(4);
44
45 l_act_usr_lbl_count NUMBER(10);
46
47 l_act_def_key_count NUMBER(10);
48
49 l_temp_act_label IEU_UWQ_MACTION_DEFS_TL.ACTION_USER_LABEL%type;
50
51 l_temp_pkg_name IEU_UWQ_MACTION_DEFS_B.ACTION_PROC%TYPE;
52
53
54 l_temp_aproc IEU_UWQ_MACTION_DEFS_B.ACTION_PROC%TYPE;
55
56 l_temp_count NUMBER(10);
57 l_msg_count NUMBER(10);
58 l_msg_data VARCHAR2(2000);
59
60 l_set_id_count NUMBER(10);
61
62 temp_act_user_label IEU_UWQ_MACTION_DEFS_TL.action_user_label%type;
63
64 temp_act_key IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_KEY%type;
65
66
67 BEGIN
68
69 fnd_msg_pub.delete_msg();
70 x_return_status := fnd_api.g_ret_sts_success;
71 FND_MSG_PUB.initialize;
72
73 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
74 l_act_usr_lbl_count := 0;
75 l_act_def_key_count := 0;
76
77
78
79 temp_act_user_label := LTRIM(RTRIM(rec_obj.action_user_label));
80
81 -- select count(b.maction_def_id) into l_act_usr_lbl_count
82 -- from IEU_UWQ_MACTION_DEFS_B b, IEU_UWQ_MACTION_DEFS_TL tl
83 -- where b.maction_def_id = tl.maction_def_id
84 -- and tl.language = l_language
85 -- and lower(tl.action_user_label) = lower(temp_act_user_label)
86 -- and b.maction_def_type_flag = p_maction_def_type_flag;
87
88
89 if (p_maction_def_type_flag <> 'F' and p_maction_def_type_flag <> 'N' and p_maction_def_type_flag <> 'M' ) then
90
91 select count(b.maction_def_id) into l_act_usr_lbl_count
92 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
93 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
94 where e. sel_enum_id = rec_obj.enum_id
95 -- and e.application_id = m.application_id
96 and e.enum_type_uuid = m.action_map_code
97 and b.maction_def_id = tl.maction_def_id
98 and tl.language = l_language
99 and s.wp_action_def_id = b.maction_def_id
100 and s.action_param_set_id = m.action_param_set_id
101 and m.action_map_type_code = 'NODE'
102 and b.maction_def_type_flag = p_maction_def_type_flag
103 and s.action_param_set_id = stl.action_param_set_id
104 and stl.language = l_language
105 and m.responsibility_id = -1
106 and lower(tl.action_user_label) = lower(temp_act_user_label);
107
108
109 --select count(sb.action_param_set_id) into l_set_id_count
110 --from IEU_WP_ACT_PARAM_SETS_B sb, IEU_WP_ACT_PARAM_SETS_TL stl
111 --where sb.action_param_set_id = stl.action_param_set_id
112 --and stl.language = l_language
113 --and lower(stl.action_param_set_label) = lower(temp_act_user_label);
114
115
116
117 select count(b.maction_def_id) into l_set_id_count
118 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
119 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
120 where e. sel_enum_id = rec_obj.enum_id
121 --and e.application_id = m.application_id
122 and e.enum_type_uuid = m.action_map_code
123 and b.maction_def_id = tl.maction_def_id
124 and tl.language = l_language
125 and s.wp_action_def_id = b.maction_def_id
126 and s.action_param_set_id = m.action_param_set_id
127 and m.action_map_type_code = 'NODE'
128 and b.maction_def_type_flag = p_maction_def_type_flag
129 and s.action_param_set_id = stl.action_param_set_id
130 and stl.language = l_language
131 and m.responsibility_id = -1
132 and lower(stl.action_param_set_label) = lower(temp_act_user_label);
133
134
135 elsif (p_maction_def_type_flag = 'F') then
136
137 select count(b.maction_def_id) into l_act_usr_lbl_count
138 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
139 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e,
140 ieu_wp_act_param_sets_tl stl, ieu_uwq_node_ds ds
141 where e. sel_enum_id = rec_obj.enum_id
142 --and e.application_id = m.application_id
143 and e.enum_type_uuid = ds.ENUM_TYPE_UUID
144 and b.maction_def_id = tl.maction_def_id
145 and tl.language = l_language
146 and s.wp_action_def_id = b.maction_def_id
147 and s.action_param_set_id = m.action_param_set_id
148 and m.action_map_type_code = 'NODE_DS'
149 and b.maction_def_type_flag = 'F'
150 and s.action_param_set_id = stl.action_param_set_id
151 and stl.language = l_language
152 and m.responsibility_id = -1
153 and lower(tl.action_user_label) = lower(temp_act_user_label)
154 and to_char(ds.NODE_DS_ID) = m.ACTION_MAP_CODE;
155
156
157
158 select count(b.maction_def_id) into l_set_id_count
159 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
160 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e,
161 ieu_wp_act_param_sets_tl stl, ieu_uwq_node_ds ds
162 where e. sel_enum_id = rec_obj.enum_id
163 --and e.application_id = m.application_id
164 and e.enum_type_uuid = ds.ENUM_TYPE_UUID
165 and b.maction_def_id = tl.maction_def_id
166 and tl.language = l_language
167 and s.wp_action_def_id = b.maction_def_id
168 and s.action_param_set_id = m.action_param_set_id
169 and m.action_map_type_code = 'NODE_DS'
170 and b.maction_def_type_flag = 'F'
171 and s.action_param_set_id = stl.action_param_set_id
172 and stl.language = l_language
173 and m.responsibility_id = -1
174 and lower(stl.action_param_set_label) = lower(temp_act_user_label)
175 and to_char(ds.NODE_DS_ID) = m.ACTION_MAP_CODE;
176
177
178
179 end if;
180
181 if (is_create = 'N' and p_maction_def_type_flag <> 'M' and p_maction_def_type_flag <> 'N') then
182 select action_param_set_label into l_temp_act_label
183 from ieu_wp_act_param_sets_tl stl, ieu_wp_act_param_sets_b s
184 where s.action_param_set_id = stl.action_param_set_id
185 and s.wp_action_def_id = rec_obj.maction_def_id
186 and stl.language = l_language
187 and stl.action_param_set_id = p_param_set_id;
188
189 if (l_temp_act_label <> rec_obj.action_user_label) then
190 if (l_set_id_count <> 0) then
191 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
192 FND_MSG_PUB.Add;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 end if;
195 end if;
196 else
197 if (l_act_usr_lbl_count <> 0 or l_set_id_count <> 0) then
198 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
199 FND_MSG_PUB.Add;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 end if;
202 end if;
203
204
205 -- this check for media and non-media actions is always done
206 if (p_maction_def_type_flag = 'M' or p_maction_def_type_flag = 'N') then
207 -- count how many maction_defs of the same type have the same name, besides the current one
208 select count(mb.maction_def_id) into l_set_id_count
209 from ieu_uwq_maction_defs_tl mtl,
210 ieu_uwq_maction_defs_b mb
211 where mb.maction_def_id = mtl.maction_def_id and
212 mb.maction_def_id <> nvl(rec_obj.maction_def_id, -1) and -- ignore current action's record
213 nvl(mb.maction_def_type_flag, 'M') = p_maction_def_type_flag and
214 mtl.action_user_label = rec_obj.action_user_label and --
215 mtl.language = l_language;
216
217 if (l_set_id_count >= 1) then
218 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
219 FND_MSG_PUB.Add;
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 end if;
222 end if;
223
224 temp_act_key := LTRIM(RTRIM(rec_obj.maction_def_key));
225
226 if (is_create = 'Y') then
227
228 select count(*) into l_act_def_key_count from IEU_UWQ_MACTION_DEFS_B
229 where lower(MACTION_DEF_KEY) = lower(temp_act_key)
230 and maction_def_type_flag = p_maction_def_type_flag;
231
232
233 if (l_act_def_key_count <> 0) then
234 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_NAME_UNIQUE');
235 FND_MSG_PUB.Add;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 end if;
238
239 end if;
240
241 -- commented for seed115
242 -- l_temp_aproc := rec_obj.action_proc;
243 -- l_temp_pkg_name := substr(l_temp_aproc,1, (instr(l_temp_aproc,'.',1,1)-1));
244
245
246 -- select count(*) into l_temp_count
247 -- from all_objects
248 -- where owner = 'APPS' and object_type in('PACKAGE', 'IEU_PROV_PKG_INVALID')
249 -- and status ='VALID' and object_name = l_temp_pkg_name;
250
251
252
253 -- if (l_temp_count <= 0) then
254 -- FND_MESSAGE.set_name('IEU', 'IEU_PROV_PKG_INVALID');
255 -- FND_MSG_PUB.Add;
256 -- x_return_status := FND_API.G_RET_STS_ERROR;
257 -- end if;
258
259
260
261 x_msg_count := fnd_msg_pub.COUNT_MSG();
262
263 FOR i in 1..x_msg_count LOOP
264 l_msg_data := '';
265 l_msg_count := 0;
266 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
267 x_msg_data := x_msg_data || ',' || l_msg_data;
268
269 END LOOP;
270
271 EXCEPTION
272
273 WHEN FND_API.G_EXC_ERROR THEN
274
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 x_msg_count := fnd_msg_pub.COUNT_MSG();
277 -- DBMS_OUTPUT.PUT_LINE(' Error : '||sqlerrm);
278
279 FOR i in 1..x_msg_count LOOP
280 l_msg_data := '';
281 l_msg_count := 0;
282 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
283 x_msg_data := x_msg_data || ',' || l_msg_data;
284 END LOOP;
285
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 x_msg_count := fnd_msg_pub.COUNT_MSG();
290
291 FOR i in 1..x_msg_count LOOP
292 l_msg_data := '';
293 l_msg_count := 0;
294 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
295 x_msg_data := x_msg_data || ',' || l_msg_data;
296 END LOOP;
297 WHEN OTHERS THEN
298 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 --DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
301
302
306 l_msg_data := '';
303 x_msg_count := fnd_msg_pub.COUNT_MSG();
304
305 FOR i in 1..x_msg_count LOOP
307 l_msg_count := 0;
308 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
309 x_msg_data := x_msg_data || ',' || l_msg_data;
310 END LOOP;
311 -- DBMS_OUTPUT.PUT_LINE('x_return_status : '||x_return_status);
312
313
314 END Validate_Action;
315
316 --===================================================================
317 -- NAME
318 -- Validate_Action_Label
319 --
320 -- PURPOSE
321 -- Private api to Validate label fields.
322 --
323 -- NOTES
324 -- 1. UWQ Work Panel Admin will use this procedure to validate action
325 --
326 --
327 -- HISTORY
328 -- 08-May-2002 GPAGADAL Created
329
330 --===================================================================
331
332
333 PROCEDURE Validate_Action_Label( x_return_status OUT NOCOPY VARCHAR2,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 p_label IN VARCHAR2,
337 p_maction_def_type_flag IN VARCHAR2,
338 p_enum_id IN NUMBER)
339 AS
340
341 l_language VARCHAR2(4);
342
343 l_act_usr_lbl_count NUMBER(10);
344
345 l_temp_count NUMBER;
346 l_msg_count NUMBER(10);
347 l_msg_data VARCHAR2(2000);
348 temp_act_user_label IEU_UWQ_MACTION_DEFS_TL.action_user_label%type;
349 l_set_id_count NUMBER(10);
350
351
352 BEGIN
353
354
355 fnd_msg_pub.delete_msg();
356 x_return_status := fnd_api.g_ret_sts_success;
357 FND_MSG_PUB.initialize;
358
359 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
360 l_set_id_count :=0;
361
362
363 temp_act_user_label := LTRIM(RTRIM(p_label));
364
365
366 -- select count(sb.action_param_set_id) into l_set_id_count
367 -- from IEU_WP_ACT_PARAM_SETS_B sb, IEU_WP_ACT_PARAM_SETS_TL stl
368 -- where sb.action_param_set_id = stl.action_param_set_id
369 -- and stl.language = l_language
370 --and lower(stl.action_param_set_label) = lower(temp_act_user_label);
371
372
373
374 if(p_maction_def_type_flag <> 'F') then
375
376 select count(b.maction_def_id) into l_act_usr_lbl_count
377 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
378 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
379 where e. sel_enum_id = p_enum_id
380 -- and e.application_id = m.application_id
381 and e.enum_type_uuid = m.action_map_code
382 and b.maction_def_id = tl.maction_def_id
383 and tl.language = l_language
384 and s.wp_action_def_id = b.maction_def_id
385 and s.action_param_set_id = m.action_param_set_id
386 and m.action_map_type_code = 'NODE'
387 and b.maction_def_type_flag = p_maction_def_type_flag
388 and s.action_param_set_id = stl.action_param_set_id
389 and stl.language = l_language
390 and m.responsibility_id = -1
391 and lower(tl.action_user_label) = lower(temp_act_user_label);
392
393 --DBMS_OUTPUT.PUT_LINE(' maction lable count : '||l_act_usr_lbl_count);
394
395 select count(b.maction_def_id) into l_set_id_count
396 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
397 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl
398 where e. sel_enum_id = p_enum_id
399 --and e.application_id = m.application_id
400 and e.enum_type_uuid = m.action_map_code
401 and b.maction_def_id = tl.maction_def_id
402 and tl.language = l_language
403 and s.wp_action_def_id = b.maction_def_id
404 and s.action_param_set_id = m.action_param_set_id
405 and m.action_map_type_code = 'NODE'
406 and b.maction_def_type_flag = p_maction_def_type_flag
407 and s.action_param_set_id = stl.action_param_set_id
408 and stl.language = l_language
409 and m.responsibility_id = -1
410 and lower(stl.action_param_set_label) = lower(temp_act_user_label);
411
412 elsif (p_maction_def_type_flag = 'F') then
413
414 select count(b.maction_def_id) into l_act_usr_lbl_count
415 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
416 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl,
417 IEU_UWQ_NODE_DS ds
418 where e. sel_enum_id = p_enum_id
419 --and e.application_id = m.application_id
420 and e.enum_type_uuid = ds.ENUM_TYPE_UUID
421 and b.maction_def_id = tl.maction_def_id
422 and tl.language = l_language
423 and s.wp_action_def_id = b.maction_def_id
424 and s.action_param_set_id = m.action_param_set_id
425 and m.action_map_type_code = 'NODE_DS'
426 and b.maction_def_type_flag = 'F'
427 and s.action_param_set_id = stl.action_param_set_id
428 and stl.language = l_language
429 and m.responsibility_id = -1
430 and m.action_map_code = to_char(ds.NODE_DS_ID)
431 and lower(tl.action_user_label) = lower(temp_act_user_label);
432
433
434
435
436 select count(b.maction_def_id) into l_set_id_count
437 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl, ieu_wp_action_maps m,
438 ieu_wp_act_param_sets_b s, ieu_uwq_sel_enumerators e, ieu_wp_act_param_sets_tl stl, IEU_UWQ_NODE_DS ds
439 where e. sel_enum_id = p_enum_id
440 --and e.application_id = m.application_id
441 and e.enum_type_uuid = ds.ENUM_TYPE_UUID
442 and b.maction_def_id = tl.maction_def_id
446 and m.action_map_type_code = 'NODE_DS'
443 and tl.language = l_language
444 and s.wp_action_def_id = b.maction_def_id
445 and s.action_param_set_id = m.action_param_set_id
447 and b.maction_def_type_flag = 'F'
448 and s.action_param_set_id = stl.action_param_set_id
449 and stl.language = l_language
450 and m.responsibility_id = -1
451 and m.action_map_code = to_char(ds.NODE_DS_ID)
452 and lower(stl.action_param_set_label) = lower(temp_act_user_label);
453
454
455 end if;
456
457 --DBMS_OUTPUT.PUT_LINE(' set lable count : '|| l_set_id_count);
458
459 if (l_act_usr_lbl_count <> 0 or l_set_id_count <> 0) then
460 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
461 FND_MSG_PUB.Add;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 end if;
464
465 -- x_return_status := fnd_api.g_ret_sts_success;
466
467 x_msg_count := fnd_msg_pub.COUNT_MSG();
468
469 FOR i in 1..x_msg_count LOOP
470 l_msg_data := '';
471 l_msg_count := 0;
472 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
473 x_msg_data := x_msg_data || ',' || l_msg_data;
474 END LOOP;
475
476 EXCEPTION
477
478 WHEN FND_API.G_EXC_ERROR THEN
479
480 x_return_status := FND_API.G_RET_STS_ERROR;
481 x_msg_count := fnd_msg_pub.COUNT_MSG();
482 -- DBMS_OUTPUT.PUT_LINE(' Error : '||sqlerrm);
483
484 FOR i in 1..x_msg_count LOOP
485 l_msg_data := '';
486 l_msg_count := 0;
487 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
488 x_msg_data := x_msg_data || ',' || l_msg_data;
489 END LOOP;
490
491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
492 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
493
494
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 x_msg_count := fnd_msg_pub.COUNT_MSG();
497
498 FOR i in 1..x_msg_count LOOP
499 l_msg_data := '';
500 l_msg_count := 0;
501 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
502 x_msg_data := x_msg_data || ',' || l_msg_data;
503 END LOOP;
504 WHEN OTHERS THEN
505 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
508
509
510 x_msg_count := fnd_msg_pub.COUNT_MSG();
511
512 FOR i in 1..x_msg_count LOOP
513 l_msg_data := '';
514 l_msg_count := 0;
515 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
516 x_msg_data := x_msg_data || ',' || l_msg_data;
517 END LOOP;
518 -- DBMS_OUTPUT.PUT_LINE('x_return_status : '||x_return_status);
519
520 END Validate_Action_Label;
521
522 --===================================================================
523 -- NAME
524 -- Create_MAction
525 -- PURPOSE
526 -- Private api to create an action
527 --
528 -- NOTES
529 -- 1. UWQ Work Panel Admin will use this
530 -- procedure to create a work panel action
531 --
532 --
533 -- HISTORY
534 -- 08-May-2002 GPAGADAL Created
535 --===================================================================
536
537 PROCEDURE Create_MAction (x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
541 p_maction_def_type_flag IN VARCHAR2)
542 AS
543
544 l_language VARCHAR2(4);
545
546 l_source_lang VARCHAR2(4);
547
548 l_return_status VARCHAR2(4);
549
550 l_msg_count NUMBER(2);
551
552 BEGIN
553
554
555 fnd_msg_pub.delete_msg();
556 x_return_status := fnd_api.g_ret_sts_success;
557 FND_MSG_PUB.initialize;
558 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
559 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
560 x_msg_data := '';
561
562
563 IEU_WorkPanel_PVT.Create_MAction2 (x_return_status,
564 x_msg_count,
565 x_msg_data,
566 rec_obj,
567 p_maction_def_type_flag,
568 null);
569
570
571 COMMIT;
572
573 EXCEPTION
574 WHEN fnd_api.g_exc_error THEN
575 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
576 ROLLBACK;
577 x_return_status := fnd_api.g_ret_sts_error;
578
579 WHEN fnd_api.g_exc_unexpected_error THEN
580 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
581 ROLLBACK;
582 x_return_status := fnd_api.g_ret_sts_unexp_error;
583
584 WHEN OTHERS THEN
585 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
586
587 ROLLBACK;
588 x_return_status := fnd_api.g_ret_sts_unexp_error;
589
590 END Create_MAction;
591
592 --===================================================================
593 -- NAME
594 -- Create_MAction2
595 -- PURPOSE
596 -- Private api to create an action
597 --
598 -- NOTES
602 --
599 -- 1. UWQ Work Panel Admin will use this
600 -- procedure to create a work panel action
601 --
603 -- HISTORY
604 -- 14-NOV-2002 GPAGADAL Created
605 --===================================================================
606
607
608
609 PROCEDURE Create_MAction2 (x_return_status OUT NOCOPY VARCHAR2,
610 x_msg_count OUT NOCOPY NUMBER,
611 x_msg_data OUT NOCOPY VARCHAR2,
612 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
613 p_maction_def_type_flag IN VARCHAR2,
614 p_datasource IN VARCHAR2)
615 AS
616
617 l_language VARCHAR2(4);
618
619 l_source_lang VARCHAR2(4);
620
621 l_return_status VARCHAR2(4);
622
623 l_msg_count NUMBER(2);
624
625 l_msg_data VARCHAR2(2000);
626
627 l_maction_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%TYPE;
628
629 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%TYPE;
630
631 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
632
633
634 l_temp_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
635
636 l_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
637
638 l_wp_node_section_map_id IEU_WP_NODE_SECTION_MAPS.WP_NODE_SECTION_MAP_ID%type;
639
640 act_map_obj SYSTEM.IEU_wp_action_maps_OBJ;
641
642 act_map_obj1 SYSTEM.IEU_wp_action_maps_OBJ;
643
644 l_count NUMBER(5);
645
646 l_section_id IEU_WP_NODE_SECTION_MAPS.SECTION_ID%type;
647
648 l_section_map_sequence IEU_WP_NODE_SECTION_MAPS.SECTION_MAP_SEQUENCE%type;
649
650 l_panel_sec_cat_code IEU_WP_ACTION_MAPS.PANEL_SEC_CAT_CODE%type;
651
652 l_action_map_type_code IEU_WP_ACTION_MAPS.ACTION_MAP_TYPE_CODE%type;
653
654
655
656
657
658 BEGIN
659 fnd_msg_pub.delete_msg();
660 x_return_status := fnd_api.g_ret_sts_success;
661 FND_MSG_PUB.initialize;
662 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
663 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
664 x_msg_data := '';
665 l_count := 0;
666 l_section_id := 0;
667 l_section_map_sequence := 0;
668
669
670
671 IEU_WorkPanel_PVT.Validate_Action ( l_return_status,
672 l_msg_count,
673 l_msg_data,
674 rec_obj, 'Y', p_maction_def_type_flag, null);
675
676 --dbms_output.put_line('out out from validate_action :' || l_return_status);
677
678 if (l_return_status = 'S') then
679 --dbms_output.put_line('out out from validate_action is S');
680 select IEU_UWQ_MACTION_DEFS_B_S1.NEXTVAL into l_maction_def_id from sys.dual;
681
682 insert INTO IEU_UWQ_MACTION_DEFS_B
683 (MACTION_DEF_ID,
684 CREATED_BY,
685 CREATION_DATE,
686 LAST_UPDATED_BY,
687 LAST_UPDATE_DATE,
688 LAST_UPDATE_LOGIN,
689 ACTION_PROC,
690 APPLICATION_ID,
691 MACTION_DEF_TYPE_FLAG,
692 MACTION_DEF_KEY,
693 OBJECT_VERSION_NUMBER,
694 MULTI_SELECT_FLAG
695 )
696 values(
697 l_maction_def_id,
698 FND_GLOBAL.USER_ID,
699 SYSDATE,
700 FND_GLOBAL.USER_ID,
701 SYSDATE,
702 FND_GLOBAL.LOGIN_ID,
703 LTRIM(RTRIM(rec_obj.action_proc)),
704 rec_obj.application_id,
705 p_maction_def_type_flag,
706 LTRIM(RTRIM(rec_obj.maction_def_key)),
707 0,
708 rec_obj.multi_select_flag
709 );
710
711 --DBMS_OUTPUT.PUT_LINE('inserted in maction defs b: ');
712
713 insert INTO IEU_UWQ_MACTION_DEFS_TL
714 (MACTION_DEF_ID,
715 LANGUAGE,
716 CREATED_BY,
717 CREATION_DATE,
718 LAST_UPDATED_BY,
719 LAST_UPDATE_DATE,
720 LAST_UPDATE_LOGIN,
721 ACTION_USER_LABEL,
722 SOURCE_LANG,
723 ACTION_DESCRIPTION,
724 OBJECT_VERSION_NUMBER
725 ) values (
726 l_maction_def_id,
727 l_language,
728 FND_GLOBAL.USER_ID,
729 SYSDATE,
730 FND_GLOBAL.USER_ID,
731 SYSDATE,
732 FND_GLOBAL.LOGIN_ID,
733 LTRIM(RTRIM(rec_obj.action_user_label)),
734 l_source_lang,
735 LTRIM(RTRIM(rec_obj.action_description)),
736 0
737 );
738
739 --DBMS_OUTPUT.PUT_LINE('inserted in maction defs tl ');
740
741 select IEU_WP_ACT_PARAM_SETS_B_S1.NEXTVAL into l_action_param_set_id from sys.dual;
742
743 --DBMS_OUTPUT.PUT_LINE('got next val '|| l_action_param_set_id);
744
745
746 insert INTO IEU_WP_ACT_PARAM_SETS_B
747 (ACTION_PARAM_SET_ID,
748 CREATED_BY,
749 CREATION_DATE,
750 LAST_UPDATED_BY,
751 LAST_UPDATE_DATE,
752 LAST_UPDATE_LOGIN,
753 WP_ACTION_DEF_ID,
754 OBJECT_VERSION_NUMBER
755 ) values (
756 l_action_param_set_id,
757 FND_GLOBAL.USER_ID,
758 SYSDATE,
759 FND_GLOBAL.USER_ID,
760 SYSDATE,
761 FND_GLOBAL.LOGIN_ID,
762 l_maction_def_id,
763 0
764 );
765 --DBMS_OUTPUT.PUT_LINE('inserted in param sets b: ');
766
770 CREATION_DATE,
767 insert INTO IEU_WP_ACT_PARAM_SETS_TL
768 (ACTION_PARAM_SET_ID,
769 CREATED_BY,
771 LAST_UPDATED_BY,
772 LAST_UPDATE_DATE,
773 LAST_UPDATE_LOGIN,
774 ACTION_PARAM_SET_LABEL,
775 LANGUAGE,
776 SOURCE_LANG,
777 ACTION_PARAM_SET_DESC,
778 OBJECT_VERSION_NUMBER
779 ) values (
780 l_action_param_set_id,
781 FND_GLOBAL.USER_ID,
782 SYSDATE,
783 FND_GLOBAL.USER_ID,
784 SYSDATE,
785 FND_GLOBAL.LOGIN_ID,
786 LTRIM(RTRIM(rec_obj.action_user_label)),
787 l_language,
788 l_source_lang,
789 LTRIM(RTRIM(rec_obj.action_description)),
790 0
791 );
792
793 -- DBMS_OUTPUT.PUT_LINE('inserted in param sets tl: ');
794
795
796 select enum_type_uuid into l_enum_uuid
797 from ieu_uwq_sel_enumerators
798 where sel_enum_id = rec_obj.enum_id;
799
800 if (p_maction_def_type_flag <> 'F') then
801
802 select max(m.action_map_sequence) into l_temp_map_sequence
803 from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
804 ieu_wp_act_param_sets_b sb
805 --where m.application_id = rec_obj.application_id
806 where m.action_map_type_code = 'NODE'
807 and m.action_map_code = l_enum_uuid
808 --and m.application_id = db.application_id
809 and db.maction_def_type_flag = p_maction_def_type_flag
810 and db.maction_def_id = sb.wp_action_def_id
811 and sb.action_param_set_id = m.action_param_set_id
812 and m.responsibility_id = -1;
813
814 if (l_temp_map_sequence IS NULL) then
815 l_map_sequence := 1;
816 else
817 l_map_sequence := l_temp_map_sequence +1;
818 end if;
819
820 end if;
821
822 --DBMS_OUTPUT.PUT_LINE('calling IEU_wp_action_maps_OBJ');
823 /*
824 TYPE IEU_WP_ACTION_MAPS_OBJ AS OBJECT
825 (
826 wp_action_map_id NUMBER,
827 ACTION_PARAM_SET_ID NUMBER,
828 APPLICATION_ID NUMBER,
829 RESPONSIBILITY_ID NUMBER,
830 ACTION_MAP_TYPE_CODE VARCHAR2(50),
831 ACTION_MAP_CODE VARCHAR2(50),
832 ACTION_MAP_SEQUENCE NUMBER,
833 PANEL_SEC_CAT_CODE VARCHAR2(32),
834 NOT_VALID_FLAG VARCHAR2(5),
835 DEV_DATA_FLAG VARCHAR2(1)
836 )
837
838 */
839
840 if (p_maction_def_type_flag ='W') then
841 l_section_id := 10002;
842 l_section_map_sequence := 2;
843 l_panel_sec_cat_code := null;
844 l_action_map_type_code := 'NODE';
845 elsif (p_maction_def_type_flag ='I') then
846 l_section_id := 10001;
847 l_section_map_sequence := 1;
848 l_panel_sec_cat_code := 'NOTES';
849 l_action_map_type_code := 'NODE';
850 elsif (p_maction_def_type_flag ='G') then
851 l_panel_sec_cat_code := null;
852 l_action_map_type_code := 'NODE';
853 elsif (p_maction_def_type_flag = 'F') then
854 l_action_map_type_code := 'NODE_DS';
855 l_map_sequence := 1;
856 l_panel_sec_cat_code := null;
857 l_enum_uuid := p_datasource;
858 end if;
859
860
861 if (p_maction_def_type_flag <> 'F') then
862
863 act_map_obj := SYSTEM.IEU_wp_action_maps_OBJ(null, l_action_param_set_id,
864 rec_obj.application_id, null, l_action_map_type_code,
865 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code, null, 'Y');
866
867 IEU_WP_ACTION_PVT.CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj);
868
869
870 end if;
871
872
873
874 act_map_obj1 := SYSTEM.IEU_wp_action_maps_OBJ(null, l_action_param_set_id,
875 rec_obj.application_id, -1, l_action_map_type_code,
876 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code, null, 'Y');
877
878 IEU_WP_ACTION_PVT.CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj1);
879
880
881 --DBMS_OUTPUT.PUT_LINE('inserted in maction maps: ');
882
883 if (p_maction_def_type_flag <> 'F') then
884
885
886 update IEU_UWQ_SEL_ENUMERATORS set
887 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
888 LAST_UPDATE_DATE = SYSDATE,
889 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
890 WORK_PANEL_REGISTERED_FLAG = 'Y'
891 where SEL_ENUM_ID = rec_obj.enum_id;
892
893
894 if (p_maction_def_type_flag <> 'G') then
895
896 select count(*) into l_count
897 from IEU_WP_NODE_SECTION_MAPS
898 where ENUM_TYPE_UUID = l_enum_uuid
899 and APPLICATION_ID = rec_obj.application_id
900 AND SECTION_ID = l_section_id;
901
902 if (l_count > 0) then
903
904 update IEU_WP_NODE_SECTION_MAPS set
905 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
909 SECTION_MAP_SEQUENCE = l_section_map_sequence
906 LAST_UPDATE_DATE = SYSDATE,
907 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
908 RESPONSIBILITY_ID = null,
910 where ENUM_TYPE_UUID = l_enum_uuid
911 and APPLICATION_ID = rec_obj.application_id
912 and SECTION_ID = l_section_id;
913
914
915 else
916
917 select IEU_WP_NODE_SECTION_MAPS_S1.nextval into l_wp_node_section_map_id from sys.dual;
918
919 insert INTO IEU_WP_NODE_SECTION_MAPS
920 (WP_NODE_SECTION_MAP_ID,
921 OBJECT_VERSION_NUMBER,
922 CREATED_BY,
923 CREATION_DATE,
924 LAST_UPDATED_BY,
925 LAST_UPDATE_DATE,
926 LAST_UPDATE_LOGIN,
927 APPLICATION_ID,
928 RESPONSIBILITY_ID,
929 ENUM_TYPE_UUID,
930 SECTION_ID,
931 SECTION_MAP_SEQUENCE
932 ) values
933 (l_wp_node_section_map_id,
934 0,
935 FND_GLOBAL.USER_ID,
936 SYSDATE,
937 FND_GLOBAL.USER_ID,
938 SYSDATE,
939 FND_GLOBAL.LOGIN_ID,
940 rec_obj.application_id,
941 null,
942 l_enum_uuid,
943 l_section_id,
944 l_section_map_sequence);
945
946 end if;
947 end if;
948 end if;
949
950
951 x_return_status := fnd_api.g_ret_sts_success;
952 else
953 x_return_status := l_return_status;
954 x_msg_count := l_msg_count;
955 x_msg_data := l_msg_data;
956 end if;
957
958
959 COMMIT;
960
961 EXCEPTION
962 WHEN fnd_api.g_exc_error THEN
963 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
964 ROLLBACK;
965 x_return_status := fnd_api.g_ret_sts_error;
966
967 WHEN fnd_api.g_exc_unexpected_error THEN
968 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
969 ROLLBACK;
970 x_return_status := fnd_api.g_ret_sts_unexp_error;
971
972 WHEN OTHERS THEN
973 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
974
975 ROLLBACK;
976 x_return_status := fnd_api.g_ret_sts_unexp_error;
977
978 END Create_MAction2;
979
980 --===================================================================
981 -- NAME
982 -- Update_MAction
983 --
984 -- PURPOSE
985 -- Private api to update media type
986 --
987 -- NOTES
988 -- 1. UWQ Work Panel Admin will use this procedure to update an action
989 --
990 --
991 -- HISTORY
992 -- 08-MAY-2002 GPAGADAL Created
993
994 --===================================================================
995 PROCEDURE Update_MAction (x_return_status OUT NOCOPY VARCHAR2,
996 x_msg_count OUT NOCOPY NUMBER,
997 x_msg_data OUT NOCOPY VARCHAR2,
998 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
999 p_param_set_id IN NUMBER,
1000 p_maction_def_type_flag IN VARCHAR2) AS
1001
1002
1003 l_language VARCHAR2(4);
1004
1005 l_source_lang VARCHAR2(4);
1006
1007 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type;
1008
1009 l_return_status VARCHAR2(4);
1010
1011 l_msg_count NUMBER(2);
1012
1013 l_msg_data VARCHAR2(2000);
1014
1015
1016 BEGIN
1017
1018
1019 fnd_msg_pub.delete_msg();
1020 x_return_status := fnd_api.g_ret_sts_success;
1021 FND_MSG_PUB.initialize;
1022 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1023 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1024 x_msg_data := '';
1025
1026 IEU_WorkPanel_PVT.Validate_Action ( l_return_status,
1027 l_msg_count,
1028 l_msg_data,
1029 rec_obj, 'N', p_maction_def_type_flag, p_param_set_id);
1030
1031
1032 if (l_return_status = 'S') then
1033 if (p_maction_def_type_flag = 'N' or p_maction_def_type_flag = 'M') then
1034 update IEU_UWQ_MACTION_DEFS_B set
1035 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1036 LAST_UPDATE_DATE = SYSDATE,
1037 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1038 ACTION_PROC = LTRIM(RTRIM(rec_obj.action_proc)),
1039 MULTI_SELECT_FLAG = rec_obj.multi_select_flag
1040 where MACTION_DEF_ID = p_param_set_id
1041 and nvl(MACTION_DEF_TYPE_FLAG,'M') = p_maction_def_type_flag;
1042
1043 update IEU_UWQ_MACTION_DEFS_tl set
1044 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1045 LAST_UPDATE_DATE = SYSDATE,
1049 where MACTION_DEF_ID = p_param_set_id
1046 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1047 action_description = LTRIM(RTRIM(rec_obj.action_description)),
1048 action_user_label = LTRIM(RTRIM(rec_obj.action_user_label))
1050 and l_language IN (language, source_lang);
1051
1052
1053 else
1054 update IEU_UWQ_MACTION_DEFS_B set
1055 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1056 LAST_UPDATE_DATE = SYSDATE,
1057 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1058 ACTION_PROC = LTRIM(RTRIM(rec_obj.action_proc)),
1059 MULTI_SELECT_FLAG = rec_obj.multi_select_flag
1060 where MACTION_DEF_ID = rec_obj.maction_def_id
1061 and MACTION_DEF_TYPE_FLAG = p_maction_def_type_flag;
1062
1063
1064 update IEU_WP_ACT_PARAM_SETS_TL set
1065 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1066 LAST_UPDATE_DATE = SYSDATE,
1067 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1068 ACTION_PARAM_SET_LABEL = LTRIM(RTRIM(rec_obj.action_user_label)),
1069 ACTION_PARAM_SET_DESC = LTRIM(RTRIM(rec_obj.action_description))
1070 where ACTION_PARAM_SET_ID = p_param_set_id
1071 and l_language IN (language, source_lang);
1072 end if;
1073
1074 else
1075 x_return_status := l_return_status;
1076 x_msg_count := l_msg_count;
1077 x_msg_data := l_msg_data;
1078 end if;
1079
1080 COMMIT;
1081
1082 END Update_MAction;
1083
1084
1085 --===================================================================
1086 -- NAME
1087 -- Delete_MAction
1088 --
1089 -- PURPOSE
1090 -- Private api to delete work panel action
1091 --
1092 -- NOTES
1093 -- 1. UWQ Work Panel Admin will use this procedure to delete an action
1094 --
1095 --
1096 -- HISTORY
1097 -- 08-May-2002 GPAGADAL Created
1098
1099
1100 --===================================================================
1101
1102
1103 PROCEDURE Delete_MAction (
1104 x_action_def_id IN NUMBER
1105 ) is
1106
1107 l_language VARCHAR2(4);
1108
1109 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type;
1110
1111
1112
1113 BEGIN
1114 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1115
1116
1117 select ACTION_PARAM_SET_ID into l_action_param_set_id
1118 from IEU_WP_ACT_PARAM_SETS_B
1119 where WP_ACTION_DEF_ID = x_action_def_id;
1120
1121
1122
1123 delete from IEU_UWQ_MACTION_DEFS_B
1124 where MACTION_DEF_ID = x_action_def_id;
1125
1126 if (sql%notfound) then
1127 null;
1128 end if;
1129
1130 delete from IEU_UWQ_MACTION_DEFS_TL
1131 where MACTION_DEF_ID = x_action_def_id and language= l_language;
1132
1133 if (sql%notfound) then
1134 null;
1135 end if;
1136
1137
1138 delete from IEU_WP_ACT_PARAM_SETS_B
1139 where WP_ACTION_DEF_ID = x_action_def_id;
1140
1141 if (sql%notfound) then
1142 null;
1143 end if;
1144
1145
1146 delete from IEU_WP_ACT_PARAM_SETS_TL
1147 where ACTION_PARAM_SET_ID = l_action_param_set_id and language= l_language;
1148
1149 if (sql%notfound) then
1150 null;
1151 end if;
1152
1153 COMMIT;
1154 END Delete_MAction;
1155
1156 --===================================================================
1157 -- NAME
1158 -- Delete_Action_From_Node
1159 --
1160 -- PURPOSE
1161 -- Private api to delete work panel action
1162 --
1163 -- NOTES
1164 -- 1. UWQ Work Panel Admin will use this procedure to delete an action
1165 -- from a work panel node.
1166 --
1167 --
1168 -- HISTORY
1169 -- 12-June-2002 Msista Created
1170
1171
1172 --===================================================================
1173
1174
1175 PROCEDURE Delete_Action_From_Node (
1176 x_return_status OUT NOCOPY VARCHAR2,
1177 x_msg_count OUT NOCOPY NUMBER,
1178 x_msg_data OUT NOCOPY VARCHAR2,
1179 x_param_set_id IN NUMBER,
1180 x_node_id IN NUMBER
1181 ) is
1182
1183 l_language VARCHAR2(4);
1184
1185 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type;
1186 l_maction_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%type;
1187 l_num_map_entries NUMBER;
1188 l_num_set_entries NUMBER;
1189
1190 l_count_map NUMBER;
1191 l_def_type_flag IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_TYPE_FLAG%type;
1192 l_mact_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%type;
1193 l_section_id number;
1194
1195 BEGIN
1196 x_return_status := fnd_api.g_ret_sts_success;
1197 x_msg_count := 0;
1198 x_msg_data := '';
1199
1200 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1201 -- I. get the maction_def_type_flag
1202 --1. determine if this action has 1:1 for action_maps to action_param_sets
1203 --2. delete from maps
1204 --3. if 1:1 in 1,
1205 --a. query if 1:1 between action_param_sets and maction_defs
1206 --b. delete from action_param_sets and param_props
1207 --c. if 1:1 in 1, delete from maction_Defs and action_params and param_defs
1208
1209 --I.
1210 SELECT db.maction_def_type_flag
1211 into l_def_type_flag
1212 FROM ieu_uwq_maction_defs_b db,
1213 ieu_wp_act_param_sets_b sb
1214 WHERE db.maction_def_id = sb.wp_action_def_id
1215 AND sb.action_param_set_id = x_param_set_id;
1216
1217 --1.
1218 if ( l_def_type_flag <> 'F') then
1219
1220 SELECT count(unique(action_map_code))
1221 INTO l_num_map_entries
1225
1222 FROM ieu_wp_action_maps
1223 WHERE action_map_type_code = 'NODE' AND
1224 action_param_set_id = x_param_set_id;
1226 elsif ( l_def_type_flag ='F') then
1227
1228 SELECT count(unique(action_map_code))
1229 INTO l_num_map_entries
1230 FROM ieu_wp_action_maps
1231 WHERE action_map_type_code = 'NODE_DS' AND
1232 action_param_set_id = x_param_set_id;
1233
1234
1235 end if;
1236
1237 --2.
1238 if ( l_def_type_flag <> 'F') then
1239 DELETE FROM ieu_wp_action_maps
1240 WHERE action_param_set_id = x_param_set_id AND
1241 action_map_type_code = 'NODE' AND
1242 action_map_code IN
1243 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
1244 WHERE sel_enum_id = x_node_id);
1245
1246 -- dolee modified on 8/27/04, if no action/information,
1247 -- delete related records in IEU_WP_NODE_SECTION_MAPS
1248 if (l_def_type_flag = 'W' or l_def_type_flag = 'I') then
1249 if (l_def_type_flag = 'W') then
1250 l_section_id := 10002;
1251 else l_section_id := 10001;
1252 end if;
1253
1254 l_count_map := 0;
1255 select count(distinct action_param_set_id) into l_count_map
1256 from ieu_wp_action_maps
1257 where action_map_type_code = 'NODE'
1258 AND action_map_code IN
1259 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
1260 WHERE sel_enum_id = x_node_id)
1261 and action_param_set_id in
1262 (SELECT action_param_set_id
1263 FROM ieu_wp_act_param_sets_b
1264 WHERE wp_action_def_id in
1265 (select maction_def_id
1266 from ieu_uwq_maction_defs_b
1267 where maction_def_type_flag = l_def_type_flag
1268 )
1269 );
1270 if (l_count_map = 0) then
1271 delete from IEU_WP_NODE_SECTION_MAPS
1272 where ENUM_TYPE_UUID IN
1273 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
1274 WHERE sel_enum_id = x_node_id)
1275 and SECTION_ID = l_section_id;
1276 end if;
1277 end if;
1278 elsif ( l_def_type_flag ='F') then
1279
1280 DELETE FROM ieu_wp_action_maps
1281 WHERE action_param_set_id = x_param_set_id AND
1282 action_map_type_code = 'NODE_DS' AND
1283 action_map_code IN
1284 (SELECT to_char(ds.NODE_DS_ID) FROM ieu_uwq_sel_enumerators e, ieu_uwq_node_ds ds
1285 WHERE e.sel_enum_id = x_node_id
1286 and e.ENUM_TYPE_UUID = ds.ENUM_TYPE_UUID);
1287
1288 end if;
1289
1290
1291
1292
1293
1294
1295 --3.
1296 IF (l_num_map_entries = 1 and l_def_type_flag ='F') THEN
1297
1298 --a.
1299 SELECT wp_action_def_id, COUNT(*)
1300 INTO l_maction_def_id, l_num_set_entries
1301 FROM ieu_wp_act_param_sets_b
1302 WHERE wp_action_def_id IN
1303 (SELECT wp_action_def_id FROM ieu_wp_act_param_sets_b
1304 WHERE action_param_set_id = x_param_set_id)
1305 GROUP BY wp_action_def_id;
1306
1307 --b.
1308 DELETE FROM ieu_wp_param_props_tl
1309 WHERE param_property_id IN
1310 (SELECT param_property_id FROM ieu_wp_param_props_b
1311 WHERE
1312 action_param_set_id = x_param_set_id);
1313
1314 DELETE FROM ieu_wp_param_props_b
1315 WHERE action_param_set_id = x_param_set_id;
1316
1317 DELETE FROM ieu_wp_act_param_sets_tl
1318 WHERE action_param_set_id = x_param_set_id;
1319
1320 DELETE FROM ieu_wp_act_param_sets_b
1321 WHERE action_param_set_id = x_param_set_id;
1322
1323 --c.
1324 IF (l_num_set_entries = 1) THEN
1325
1326 DELETE FROM ieu_wp_param_defs_tl
1327 WHERE param_id IN
1328 (SELECT param_id FROM ieu_wp_action_params
1329 WHERE wp_action_def_id = l_maction_def_id);
1330
1331 DELETE FROM ieu_wp_param_defs_b
1332 WHERE param_id IN
1333 (SELECT param_id FROM ieu_wp_action_params
1334 WHERE wp_action_def_id = l_maction_def_id);
1335
1336 DELETE FROM ieu_wp_action_params
1337 WHERE wp_action_def_id = l_maction_def_id;
1338
1339 DELETE FROM ieu_uwq_maction_defs_tl
1340 WHERE maction_def_id = l_maction_def_id;
1341
1342 DELETE FROM ieu_uwq_maction_defs_b
1343 WHERE maction_def_id = l_maction_def_id;
1344 END IF;
1345 END IF;
1346
1347 COMMIT;
1348
1349 if (l_def_type_flag <> 'F') then
1350 select count(m.WP_ACTION_MAP_ID) into l_count_map
1351 from IEU_WP_ACTION_MAPS m
1352 where m.ACTION_MAP_CODE = (select ENUM_TYPE_UUID from
1353 ieu_uwq_sel_enumerators where SEL_ENUM_ID = x_node_id)
1354 and m.ACTION_MAP_TYPE_CODE = 'NODE';
1355
1356 if (l_count_map = 0) then
1357
1358 update IEU_UWQ_SEL_ENUMERATORS set
1359 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1360 LAST_UPDATE_DATE = SYSDATE,
1361 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1362 WORK_PANEL_REGISTERED_FLAG = null
1363 where SEL_ENUM_ID = x_node_id;
1364
1365 end if;
1366
1367 end if;
1368
1369 COMMIT;
1370 EXCEPTION
1371 WHEN fnd_api.g_exc_unexpected_error THEN
1375 WHEN OTHERS THEN
1372 ROLLBACK;
1373 x_return_status := fnd_api.g_ret_sts_unexp_error;
1374
1376 ROLLBACK;
1377 x_return_status := fnd_api.g_ret_sts_unexp_error;
1378 END Delete_Action_From_Node;
1379
1380
1381 --===================================================================
1382 -- NAME
1383 -- Validate_Parameter
1384 -- PURPOSE
1385 -- Private api to validate parameter
1386 --
1387 -- NOTES
1388 -- 1. UWQ Work Panel Admin will use this procedure to validate
1389 -- a work panel action parameter name and label
1390 --
1391 --
1392 -- HISTORY
1393 -- 20-June-2002 GPAGADAL Created
1394 --===================================================================
1395
1396
1397
1398 PROCEDURE Validate_Parameter( x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2,
1401 rec_obj IN SYSTEM.IEU_WP_ACT_PARAM_OBJ,
1402 is_create IN VARCHAR2) AS
1403
1404
1405
1406 l_language VARCHAR2(4);
1407
1408 l_param_usr_lbl_count NUMBER(10);
1409
1410 l_param_name_count NUMBER(10);
1411
1412 l_temp_param_label ieu_wp_param_defs_TL.PARAM_USER_LABEL%type;
1413 l_temp_param_name ieu_wp_param_defs_b.PARAM_NAME%type;
1414
1415
1416 l_temp_count NUMBER(10);
1417 l_msg_count NUMBER(10);
1418 l_msg_data VARCHAR2(2000);
1419
1420
1421 temp_param_user_label ieu_wp_param_defs_TL.PARAM_USER_LABEL%type;
1422
1423 temp_param_name ieu_wp_param_defs_b.PARAM_NAME%type;
1424
1425
1426 BEGIN
1427
1428 fnd_msg_pub.delete_msg();
1429 x_return_status := fnd_api.g_ret_sts_success;
1430 FND_MSG_PUB.initialize;
1431
1432 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1433 l_param_usr_lbl_count := 0;
1434 l_param_name_count := 0;
1435
1436
1437 temp_param_user_label := LTRIM(RTRIM(rec_obj.param_user_label));
1438 temp_param_name := LTRIM(RTRIM(rec_obj.param_name));
1439
1440
1441 select count(*) into l_param_name_count
1442 from ieu_wp_action_params p,
1443 ieu_wp_param_defs_b b
1444 where p.PARAM_ID = b.PARAM_ID
1445 and p.WP_ACTION_DEF_ID = rec_obj.wp_action_def_id
1446 and lower(b.PARAM_NAME) = lower(temp_param_name);
1447
1448
1449 select count(*) into l_param_usr_lbl_count
1450 from ieu_wp_action_params p,
1451 ieu_wp_param_defs_b b,
1452 ieu_wp_param_defs_tl tl
1453 where p.PARAM_ID = b.PARAM_ID
1454 and p.WP_ACTION_DEF_ID = rec_obj.wp_action_def_id
1455 and b.PARAM_ID = tl.PARAM_ID
1456 and tl.LANGUAGE = l_language
1457 and lower(tl.PARAM_USER_LABEL) = lower(temp_param_user_label);
1458
1459 if (is_create = 'Y') then
1460
1461 if (l_param_name_count <> 0) then
1462 FND_MESSAGE.set_name('IEU', 'Parameter name must be unique');
1463 FND_MSG_PUB.Add;
1464 x_return_status := FND_API.G_RET_STS_ERROR;
1465 end if;
1466
1467 if (l_param_usr_lbl_count <> 0) then
1468 FND_MESSAGE.set_name('IEU', 'Parameter label must be unique');
1469 FND_MSG_PUB.Add;
1470 x_return_status := FND_API.G_RET_STS_ERROR;
1471 end if;
1472
1473 end if;
1474
1475
1476 x_msg_count := fnd_msg_pub.COUNT_MSG();
1477
1478 FOR i in 1..x_msg_count LOOP
1479 l_msg_data := '';
1480 l_msg_count := 0;
1481 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1482 x_msg_data := x_msg_data || ',' || l_msg_data;
1483 END LOOP;
1484
1485 EXCEPTION
1486
1487 WHEN FND_API.G_EXC_ERROR THEN
1488
1489 x_return_status := FND_API.G_RET_STS_ERROR;
1490 x_msg_count := fnd_msg_pub.COUNT_MSG();
1491 -- DBMS_OUTPUT.PUT_LINE(' Error : '||sqlerrm);
1492
1493 FOR i in 1..x_msg_count LOOP
1494 l_msg_data := '';
1495 l_msg_count := 0;
1496 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1497 x_msg_data := x_msg_data || ',' || l_msg_data;
1498 END LOOP;
1499
1500 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1501 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1503 x_msg_count := fnd_msg_pub.COUNT_MSG();
1504
1505 FOR i in 1..x_msg_count LOOP
1506 l_msg_data := '';
1507 l_msg_count := 0;
1508 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1509 x_msg_data := x_msg_data || ',' || l_msg_data;
1510 END LOOP;
1511 WHEN OTHERS THEN
1512 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1514 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1515
1516
1517 x_msg_count := fnd_msg_pub.COUNT_MSG();
1518
1519 FOR i in 1..x_msg_count LOOP
1520 l_msg_data := '';
1521 l_msg_count := 0;
1522 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1523 x_msg_data := x_msg_data || ',' || l_msg_data;
1524 END LOOP;
1525 -- DBMS_OUTPUT.PUT_LINE('x_return_status : '||x_return_status);
1526
1527
1528
1529
1530 END Validate_Parameter;
1531
1532
1533
1534
1535 --===================================================================
1539 -- Private api to create parameter
1536 -- NAME
1537 -- Create_Param_Defs
1538 -- PURPOSE
1540 --
1541 -- NOTES
1542 -- 1. UWQ Work Panel Admin will use this procedure to create
1543 -- a work panel action parameter
1544 --
1545 --
1546 -- HISTORY
1547 -- 10-May-2002 GPAGADAL Created
1548 --===================================================================
1549
1550
1551
1552 PROCEDURE Create_Param_Defs ( x_return_status OUT NOCOPY VARCHAR2,
1553 x_msg_count OUT NOCOPY NUMBER,
1554 x_msg_data OUT NOCOPY VARCHAR2,
1555 rec_obj IN SYSTEM.IEU_WP_ACT_PARAM_OBJ,
1556 p_param_id OUT NOCOPY NUMBER) AS
1557
1558 l_language VARCHAR2(4);
1559
1560 l_source_lang VARCHAR2(4);
1561
1562 l_return_status VARCHAR2(4);
1563
1564 l_msg_count NUMBER(2);
1565
1566 l_msg_data VARCHAR2(2000);
1567
1568 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
1569
1570 l_action_param_map_id IEU_WP_ACTION_PARAMS.ACTION_PARAM_MAP_ID%TYPE;
1571
1572 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%TYPE;
1573
1574
1575 BEGIN
1576
1577
1578 fnd_msg_pub.delete_msg();
1579 x_return_status := fnd_api.g_ret_sts_success;
1580 FND_MSG_PUB.initialize;
1581 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1582 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1583 x_msg_data := '';
1584
1585
1586
1587 IEU_WorkPanel_PVT.Validate_Parameter ( l_return_status,
1588 l_msg_count,
1589 l_msg_data,
1590 rec_obj, 'Y');
1591
1592 if (l_return_status = 'S') then
1593 select IEU_WP_PARAM_DEFS_B_S1.NEXTVAL into l_param_id from sys.dual;
1594
1595
1596 insert INTO IEU_WP_PARAM_DEFS_B
1597 (PARAM_ID,
1598 CREATED_BY,
1599 CREATION_DATE,
1600 LAST_UPDATED_BY,
1601 LAST_UPDATE_DATE,
1602 LAST_UPDATE_LOGIN,
1603 PARAM_NAME,
1604 DATA_TYPE,
1605 OBJECT_VERSION_NUMBER,
1606 APPLICATION_ID
1607 ) values
1608 (l_param_id,
1609 FND_GLOBAL.USER_ID,
1610 SYSDATE,
1611 FND_GLOBAL.USER_ID,
1612 SYSDATE,
1613 FND_GLOBAL.LOGIN_ID,
1614 LTRIM(RTRIM(rec_obj.param_name)),
1615 rec_obj.data_type,
1616 0,
1617 rec_obj.application_id
1618 );
1619
1620 --dbms_outPUT.PUT_LINE('inserted into param defs b ');
1621 -- APPLICATION_ID
1622 --rec_obj.application_id
1623
1624 insert INTO IEU_WP_PARAM_DEFS_TL
1625 (PARAM_ID,
1626 CREATED_BY,
1627 CREATION_DATE,
1628 LAST_UPDATED_BY,
1629 LAST_UPDATE_DATE,
1630 LAST_UPDATE_LOGIN,
1631 PARAM_USER_LABEL,
1632 PARAM_DESCRIPTION,
1633 LANGUAGE,
1634 SOURCE_LANG,
1635 OBJECT_VERSION_NUMBER
1636 ) VALUES (
1637 l_param_id,
1638 FND_GLOBAL.USER_ID,
1639 SYSDATE,
1640 FND_GLOBAL.USER_ID,
1641 SYSDATE,
1642 FND_GLOBAL.LOGIN_ID,
1643 LTRIM(RTRIM(rec_obj.param_user_label)),
1644 LTRIM(RTRIM(rec_obj.param_description)),
1645 l_language,
1646 l_source_lang,
1647 0
1648 );
1649
1650 --DBMS_OUTPUT.PUT_LINE('inserted into param defs tl ');
1651
1652 select IEU_WP_ACTION_PARAMS_S1.NEXTVAL into l_action_param_map_id from sys.dual;
1653
1654 insert INTO IEU_WP_ACTION_PARAMS
1655 (PARAM_ID,
1656 CREATED_BY,
1657 CREATION_DATE,
1658 LAST_UPDATED_BY,
1659 LAST_UPDATE_DATE,
1660 LAST_UPDATE_LOGIN,
1661 WP_ACTION_DEF_ID,
1662 ACTION_PARAM_MAP_ID,
1663 NOT_VALID_FLAG,
1664 OBJECT_VERSION_NUMBER
1665 ) VALUES (
1666 l_param_id,
1667 FND_GLOBAL.USER_ID,
1668 SYSDATE,
1669 FND_GLOBAL.USER_ID,
1670 SYSDATE,
1671 FND_GLOBAL.LOGIN_ID,
1672 rec_obj.wp_action_def_id,
1673 l_action_param_map_id,
1674 null,
1675 0
1676 );
1677
1678 --dbms_outPUT.PUT_LINE('inserted into params ');
1679 p_param_id := l_param_id;
1680
1681 else
1682 x_return_status := l_return_status;
1683 x_msg_count := l_msg_count;
1684 x_msg_data := l_msg_data;
1685 end if;
1686
1687
1688 COMMIT;
1689
1690 EXCEPTION
1691 WHEN fnd_api.g_exc_error THEN
1692 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1693 ROLLBACK;
1694 x_return_status := fnd_api.g_ret_sts_error;
1695
1696
1697 WHEN fnd_api.g_exc_unexpected_error THEN
1698 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1699 ROLLBACK;
1700 x_return_status := fnd_api.g_ret_sts_unexp_error;
1701
1702 WHEN OTHERS THEN
1703 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1704 ROLLBACK;
1705 x_return_status := fnd_api.g_ret_sts_unexp_error;
1706
1707
1708 END Create_Param_Defs;
1709
1710
1711 --===================================================================
1712 -- NAME
1713 -- Update_Param_Defs
1714 -- PURPOSE
1715 -- Private api to update parameter details
1716 --
1720 --
1717 -- NOTES
1718 -- 1. UWQ Work Panel Admin will use this procedure to update
1719 -- work panel action parameter details
1721 --
1722 -- HISTORY
1723 -- 10-May-2002 GPAGADAL Created
1724 --===================================================================
1725
1726
1727 PROCEDURE Update_Param_Defs ( x_return_status OUT NOCOPY VARCHAR2,
1728 x_msg_count OUT NOCOPY NUMBER,
1729 x_msg_data OUT NOCOPY VARCHAR2,
1730 rec_obj IN SYSTEM.IEU_WP_ACT_PARAM_OBJ
1731 ) AS
1732
1733
1734 l_language VARCHAR2(4);
1735
1736 l_source_lang VARCHAR2(4);
1737
1738
1739 l_msg_count NUMBER(2);
1740
1741 l_msg_data VARCHAR2(2000);
1742
1743 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
1744
1745 l_action_param_map_id IEU_WP_ACTION_PARAMS.ACTION_PARAM_MAP_ID%TYPE;
1746
1747 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%TYPE;
1748
1749 BEGIN
1750
1751
1752 fnd_msg_pub.delete_msg();
1753 x_return_status := fnd_api.g_ret_sts_success;
1754 FND_MSG_PUB.initialize;
1755 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1756 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1757 x_msg_data := '';
1758
1759
1760
1761
1762 update IEU_WP_PARAM_DEFS_B set
1763 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1764 LAST_UPDATE_DATE = SYSDATE,
1765 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1766 DATA_TYPE = rec_obj.data_type
1767 where PARAM_ID = rec_obj.param_id;
1768
1769
1770 if (SQL%NOTFOUND) then
1771 null;
1772 end if;
1773
1774
1775 update IEU_WP_PARAM_DEFS_TL set
1776 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1777 LAST_UPDATE_DATE = SYSDATE,
1778 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1779 PARAM_USER_LABEL = LTRIM(RTRIM(rec_obj.param_user_label)),
1780 PARAM_DESCRIPTION = LTRIM(RTRIM(rec_obj.param_description))
1781 where PARAM_ID = rec_obj.param_id
1782 and l_language IN (language, source_lang);
1783
1784 if (SQL%NOTFOUND) then
1785 null;
1786 end if;
1787
1788
1789 COMMIT;
1790 x_return_status := fnd_api.g_ret_sts_success;
1791
1792
1793
1794 END Update_Param_Defs;
1795
1796
1797
1798 --===================================================================
1799 -- NAME
1800 -- Create_Param_Props
1801 -- PURPOSE
1802 -- Private api to create parameter properties
1803 --
1804 -- NOTES
1805 -- 1. UWQ Work Panel Admin will use this procedure to create
1806 -- work panel action parameter properties
1807 --
1808 --
1809 -- HISTORY
1810 -- 10-May-2002 GPAGADAL Created
1811 --===================================================================
1812
1813
1814 PROCEDURE Create_Param_Props ( x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_count OUT NOCOPY NUMBER,
1816 x_msg_data OUT NOCOPY VARCHAR2,
1817 p_param_id IN NUMBER,
1818 p_property_id IN NUMBER,
1819 p_property_value IN VARCHAR2,
1820 p_action_param_set_id IN NUMBER)AS
1821
1822
1823 l_language VARCHAR2(4);
1824
1825 l_source_lang VARCHAR2(4);
1826
1827
1828 l_msg_count NUMBER(2);
1829
1830 l_msg_data VARCHAR2(2000);
1831
1832 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
1833
1834 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
1835
1836 l_return_status VARCHAR2(4);
1837
1838
1839 BEGIN
1840
1841
1842
1843 fnd_msg_pub.delete_msg();
1844 x_return_status := fnd_api.g_ret_sts_success;
1845 FND_MSG_PUB.initialize;
1846 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1847 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1848 x_msg_data := '';
1849
1850
1851 if ( p_property_id <> -1) then
1852
1853 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
1854
1855 insert INTO IEU_WP_PARAM_PROPS_B
1856 (PARAM_PROPERTY_ID,
1857 CREATED_BY,
1858 CREATION_DATE,
1859 LAST_UPDATED_BY,
1860 LAST_UPDATE_DATE,
1861 LAST_UPDATE_LOGIN,
1862 ACTION_PARAM_SET_ID,
1863 PARAM_ID,
1864 PROPERTY_ID,
1865 PROPERTY_VALUE,
1866 VALUE_OVERRIDE_FLAG,
1867 OBJECT_VERSION_NUMBER
1868 ) VALUES (
1869 l_param_property_id,
1870 FND_GLOBAL.USER_ID,
1871 SYSDATE,
1872 FND_GLOBAL.USER_ID,
1873 SYSDATE,
1874 FND_GLOBAL.LOGIN_ID,
1875 p_action_param_set_id,
1876 p_param_id,
1877 p_property_id,
1878 p_property_value,
1879 'F',
1880 0
1881 );
1882 --dbms_outPUT.PUT_LINE('inserted into param props b ');
1883
1884 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
1885 from ieu_wp_properties_b
1886 where property_id = p_property_id;
1887
1888 if (l_trans_flag = 'Y') then
1889
1890 insert INTO IEU_WP_PARAM_PROPS_TL
1891 (PARAM_PROPERTY_ID,
1892 CREATED_BY,
1893 CREATION_DATE,
1894 LAST_UPDATED_BY,
1898 LANGUAGE,
1895 LAST_UPDATE_DATE,
1896 LAST_UPDATE_LOGIN,
1897 PROPERTY_VALUE,
1899 SOURCE_LANG,
1900 OBJECT_VERSION_NUMBER
1901 ) VALUES (
1902 l_param_property_id,
1903 FND_GLOBAL.USER_ID,
1904 SYSDATE,
1905 FND_GLOBAL.USER_ID,
1906 SYSDATE,
1907 FND_GLOBAL.LOGIN_ID,
1908 p_property_value,
1909 l_language,
1910 l_source_lang,
1911 0
1912 );
1913
1914 --dbms_outPUT.PUT_LINE('inserted into param props tl ');
1915 end if;
1916
1917 end if;
1918
1919 COMMIT;
1920
1921
1922
1923 EXCEPTION
1924 WHEN fnd_api.g_exc_error THEN
1925 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1926
1927 ROLLBACK;
1928 x_return_status := fnd_api.g_ret_sts_error;
1929
1930 WHEN fnd_api.g_exc_unexpected_error THEN
1931 --dbms_outPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1932
1933 ROLLBACK;
1934 x_return_status := fnd_api.g_ret_sts_unexp_error;
1935
1936 WHEN OTHERS THEN
1937 --dbms_outPUT.PUT_LINE('other Error : '||sqlerrm);
1938
1939 ROLLBACK;
1940 x_return_status := fnd_api.g_ret_sts_unexp_error;
1941
1942 END Create_Param_Props;
1943
1944
1945 --===================================================================
1946 -- NAME
1947 -- Update_Param_Props
1948 -- PURPOSE
1949 -- Private api to update parameter properties
1950 --
1951 -- NOTES
1952 -- 1. UWQ Work Panel Admin will use this procedure to update
1953 -- work panel action parameter properties
1954 --
1955 --
1956 -- HISTORY
1957 -- 10-May-2002 GPAGADAL Created
1958 --===================================================================
1959
1960
1961 PROCEDURE Update_Param_Props ( x_return_status OUT NOCOPY VARCHAR2,
1962 x_msg_count OUT NOCOPY NUMBER,
1963 x_msg_data OUT NOCOPY VARCHAR2,
1964 p_param_id IN NUMBER,
1965 p_property_id IN NUMBER,
1966 p_property_value IN VARCHAR2,
1967 p_action_param_set_id IN NUMBER)AS
1968
1969 l_language VARCHAR2(4);
1970
1971 l_source_lang VARCHAR2(4);
1972
1973
1974 l_msg_count NUMBER(2);
1975
1976 l_msg_data VARCHAR2(2000);
1977
1978 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
1979
1980
1981 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
1982
1983 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
1984
1985
1986 BEGIN
1987
1988 fnd_msg_pub.delete_msg();
1989 x_return_status := fnd_api.g_ret_sts_success;
1990 FND_MSG_PUB.initialize;
1991 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1992 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1993 x_msg_data := '';
1994
1995
1996
1997 if (p_property_id = 10003 or p_property_id =10010 or p_property_id = 10021
1998 or p_property_id =10011 or p_property_id = 10022) then
1999
2000
2001 EXECUTE immediate
2002 ' update IEU_WP_PARAM_PROPS_B set '||
2003 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
2004 ' LAST_UPDATE_DATE = SYSDATE, '||
2005 ' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
2006 ' PROPERTY_VALUE = :1, '||
2007 ' PROPERTY_ID = :2 '||
2008 ' where PARAM_ID= :3 '||
2009 ' and ACTION_PARAM_SET_ID = :4 ' ||
2010 ' and PROPERTY_ID in ( 10010, 10021, 10011, 10022, 10003)'
2011 USING p_property_value, p_property_id,p_param_id,p_action_param_set_id;
2012 if (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) then
2013
2014 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
2015
2016
2017 EXECUTE immediate
2018 ' INSERT INTO IEU_WP_PARAM_PROPS_B '||
2019 ' (PARAM_PROPERTY_ID, '||
2020 ' CREATED_BY, '||
2021 ' CREATION_DATE,'||
2022 ' LAST_UPDATED_BY, '||
2023 ' LAST_UPDATE_DATE, '||
2024 ' LAST_UPDATE_LOGIN, '||
2025 ' ACTION_PARAM_SET_ID, '||
2026 ' PARAM_ID, '||
2027 ' PROPERTY_ID, '||
2028 ' PROPERTY_VALUE, '||
2029 ' VALUE_OVERRIDE_FLAG, '||
2030 ' OBJECT_VERSION_NUMBER '||
2031 ' ) VALUES ( ' ||
2032 ' :1, '||
2033 ' :2, '||
2034 ' :3, '||
2035 ' :4, '||
2036 ' :5, '||
2037 ' :6, '||
2038 ' :7, '||
2039 ' :8, '||
2040 ' :9, '||
2041 ' :10, '||
2042 ' :11, '||
2043 ' :12 '||
2044 ' ) '
2045 USING l_param_property_id,FND_GLOBAL.USER_ID,SYSDATE, FND_GLOBAL.USER_ID,
2046 SYSDATE,FND_GLOBAL.LOGIN_ID, p_action_param_set_id,p_param_id,p_property_id,
2047 p_property_value, 'F', '0';
2048
2049 end if;
2050
2051
2052
2053
2054 else
2055
2056
2057
2058 EXECUTE immediate
2059 ' update IEU_WP_PARAM_PROPS_B set '||
2060 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
2061 ' LAST_UPDATE_DATE = SYSDATE, '||
2065 ' and ACTION_PARAM_SET_ID = :3 '||
2062 ' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
2063 ' PROPERTY_VALUE = :1 '||
2064 ' where PARAM_ID= :2 '||
2066 ' and PROPERTY_ID = :4 '
2067 USING p_property_value,p_param_id, p_action_param_set_id, p_property_id ;
2068
2069 if (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) then
2070
2071 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
2072 EXECUTE immediate
2073 ' INSERT INTO IEU_WP_PARAM_PROPS_B'||
2074 ' (PARAM_PROPERTY_ID, '||
2075 ' CREATED_BY, '||
2076 ' CREATION_DATE, '||
2077 ' LAST_UPDATED_BY, '||
2078 ' LAST_UPDATE_DATE, '||
2079 ' LAST_UPDATE_LOGIN, '||
2080 ' ACTION_PARAM_SET_ID, '||
2081 ' PARAM_ID, '||
2082 ' PROPERTY_ID, '||
2083 ' PROPERTY_VALUE, '||
2084 ' VALUE_OVERRIDE_FLAG, '||
2085 ' OBJECT_VERSION_NUMBER '||
2086 ' ) VALUES ( '||
2087 ' :1, '||
2088 ' :2, '||
2089 ' :3, '||
2090 ' :4, '||
2091 ' :5, '||
2092 ' :6, '||
2093 ' :7, '||
2094 ' :8, '||
2095 ' :9, '||
2096 ' :10, '||
2097 ' :11, '||
2098 ' :12) '
2099 USING l_param_property_id,FND_GLOBAL.USER_ID,SYSDATE,
2100 FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.LOGIN_ID,
2101 p_action_param_set_id, p_param_id, p_property_id,
2102 p_property_value, 'F','0' ;
2103 EXECUTE immediate
2104 ' select VALUE_TRANSLATABLE_FLAG '||
2105 ' from ieu_wp_properties_b '||
2106 ' where property_id = :1 '
2107 into l_trans_flag USING p_property_id;
2108
2109 if (l_trans_flag = 'Y') then
2110
2111 EXECUTE immediate
2112 ' insert INTO IEU_WP_PARAM_PROPS_TL '||
2113 ' (PARAM_PROPERTY_ID, '||
2114 ' CREATED_BY, '||
2115 ' CREATION_DATE, '||
2116 ' LAST_UPDATED_BY, '||
2117 ' LAST_UPDATE_DATE, '||
2118 ' LAST_UPDATE_LOGIN, '||
2119 ' PROPERTY_VALUE, '||
2120 ' LANGUAGE, '||
2121 ' SOURCE_LANG, '||
2122 ' OBJECT_VERSION_NUMBER '||
2123 ' ) VALUES ( '||
2124 ' :1, '||
2125 ' :2, '||
2126 ' :3,'||
2127 ' :4, '||
2128 ' :5, '||
2129 ' :6, '||
2130 ' :7, '||
2131 ' :8, '||
2132 ' :9, '||
2133 ' :10 '||
2134 ') '
2135 USING l_param_property_id, FND_GLOBAL.USER_ID, SYSDATE,
2136 FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.LOGIN_ID,
2137 p_property_value, l_language, l_source_lang , '0' ;
2138
2139 end if;
2140
2141
2142 end if;
2143
2144
2145
2146 begin
2147
2148 execute immediate
2149 ' select PARAM_PROPERTY_ID '||
2150 ' from IEU_WP_PARAM_PROPS_B '||
2151 ' where PARAM_ID = :1 '||
2152 ' and ACTION_PARAM_SET_ID = :2 ' ||
2153 ' and PROPERTY_ID = :3 '
2154 into l_param_property_id USING p_param_id,p_action_param_set_id, p_property_id ;
2155
2156
2157 EXECUTE immediate
2158 ' update IEU_WP_PARAM_PROPS_TL set '||
2159 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
2160 ' LAST_UPDATE_DATE = SYSDATE, '||
2161 ' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
2162 ' PROPERTY_VALUE = :1 ' ||
2163 ' where PARAM_PROPERTY_ID = :2 '
2164 USING p_property_value, l_param_property_id;
2165
2166 if (sql%notfound) then
2167 null;
2168 end if;
2169
2170
2171 EXCEPTION
2172
2173 WHEN NO_DATA_FOUND THEN
2174 null;
2175 end;
2176
2177
2178
2179 end if;
2180
2181 x_return_status := fnd_api.g_ret_sts_success;
2182
2183 COMMIT;
2184
2185
2186 END Update_Param_Props;
2187
2188
2189 PROCEDURE Update_Column_Props ( x_return_status OUT NOCOPY VARCHAR2,
2190 x_msg_count OUT NOCOPY NUMBER,
2191 x_msg_data OUT NOCOPY VARCHAR2,
2192 p_param_id IN NUMBER,
2193 p_property_id IN NUMBER,
2194 p_property_value IN VARCHAR2,
2195 p_action_param_set_id IN NUMBER)AS
2196
2197 l_language VARCHAR2(4);
2198
2199 l_source_lang VARCHAR2(4);
2203
2200
2201
2202 l_msg_count NUMBER(2);
2204 l_msg_data VARCHAR2(2000);
2205
2206 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
2207
2208
2209 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
2210
2211 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
2212
2213
2214 BEGIN
2215
2216 fnd_msg_pub.delete_msg();
2217 x_return_status := fnd_api.g_ret_sts_success;
2218 FND_MSG_PUB.initialize;
2219 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2220 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2221 x_msg_data := '';
2222
2223
2224 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
2225 if (p_property_id = 10003 or p_property_id = 10022) then
2226 --if (p_property_id = 10003 or p_property_id = 10022 or p_property_id = 10011) then
2227 /*********************************************************************************/
2228 update IEU_WP_PARAM_PROPS_B set
2229 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2230 LAST_UPDATE_DATE = SYSDATE,
2231 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2232 PROPERTY_VALUE = p_property_value,
2233 PROPERTY_ID = p_property_id
2234 where PARAM_ID= p_param_id
2235 and ACTION_PARAM_SET_ID = p_action_param_set_id
2236 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
2237 and PROPERTY_ID in (10022, 10003);
2238 --and PROPERTY_ID in (10022, 10003, 10011);
2239 /*********************************************************************************/
2240 if (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) then
2241
2242 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
2243
2244
2245 INSERT INTO IEU_WP_PARAM_PROPS_B
2246 (PARAM_PROPERTY_ID,
2247 CREATED_BY,
2248 CREATION_DATE,
2249 LAST_UPDATED_BY,
2250 LAST_UPDATE_DATE,
2251 LAST_UPDATE_LOGIN,
2252 ACTION_PARAM_SET_ID,
2253 PARAM_ID,
2254 PROPERTY_ID,
2255 PROPERTY_VALUE,
2256 VALUE_OVERRIDE_FLAG,
2257 OBJECT_VERSION_NUMBER
2258 ) VALUES (
2259 l_param_property_id,
2260 FND_GLOBAL.USER_ID,
2261 SYSDATE,
2262 FND_GLOBAL.USER_ID,
2263 SYSDATE,
2264 FND_GLOBAL.LOGIN_ID,
2265 p_action_param_set_id,
2266 p_param_id,
2267 p_property_id,
2268 p_property_value,
2269 'F',
2270 0
2271 );
2272
2273 end if;
2274
2275
2276
2277
2278 else
2279
2280
2281
2282 update IEU_WP_PARAM_PROPS_B set
2283 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2284 LAST_UPDATE_DATE = SYSDATE,
2285 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2286 PROPERTY_VALUE = p_property_value
2287 where PARAM_ID= p_param_id
2288 and ACTION_PARAM_SET_ID = p_action_param_set_id
2289 and PROPERTY_ID = p_property_id;
2290
2291 if (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) then
2292
2293 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
2294 INSERT INTO IEU_WP_PARAM_PROPS_B
2295 (PARAM_PROPERTY_ID,
2296 CREATED_BY,
2297 CREATION_DATE,
2298 LAST_UPDATED_BY,
2299 LAST_UPDATE_DATE,
2300 LAST_UPDATE_LOGIN,
2301 ACTION_PARAM_SET_ID,
2302 PARAM_ID,
2303 PROPERTY_ID,
2304 PROPERTY_VALUE,
2305 VALUE_OVERRIDE_FLAG,
2306 OBJECT_VERSION_NUMBER
2307 ) VALUES (
2308 l_param_property_id,
2309 FND_GLOBAL.USER_ID,
2310 SYSDATE,
2311 FND_GLOBAL.USER_ID,
2312 SYSDATE,
2313 FND_GLOBAL.LOGIN_ID,
2314 p_action_param_set_id,
2315 p_param_id,
2316 p_property_id,
2317 p_property_value,
2318 'F',
2319 0
2320 );
2321
2322
2323 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
2324 from ieu_wp_properties_b
2325 where property_id = p_property_id;
2326
2327 if (l_trans_flag = 'Y') then
2328
2329 insert INTO IEU_WP_PARAM_PROPS_TL
2330 (PARAM_PROPERTY_ID,
2331 CREATED_BY,
2332 CREATION_DATE,
2333 LAST_UPDATED_BY,
2334 LAST_UPDATE_DATE,
2335 LAST_UPDATE_LOGIN,
2336 PROPERTY_VALUE,
2337 LANGUAGE,
2338 SOURCE_LANG,
2339 OBJECT_VERSION_NUMBER
2340 ) VALUES (
2341 l_param_property_id,
2342 FND_GLOBAL.USER_ID,
2343 SYSDATE,
2344 FND_GLOBAL.USER_ID,
2345 SYSDATE,
2346 FND_GLOBAL.LOGIN_ID,
2347 p_property_value,
2351 );
2348 l_language,
2349 l_source_lang,
2350 0
2352
2353 end if;
2354
2355
2356 end if;
2357
2358
2359
2360 begin
2361
2362 select PARAM_PROPERTY_ID into l_param_property_id
2363 from IEU_WP_PARAM_PROPS_B
2364 where PARAM_ID = p_param_id
2365 and ACTION_PARAM_SET_ID = p_action_param_set_id
2366 and PROPERTY_ID = p_property_id;
2367
2368
2369 update IEU_WP_PARAM_PROPS_TL set
2370 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2371 LAST_UPDATE_DATE = SYSDATE,
2372 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2373 PROPERTY_VALUE = p_property_value
2374 where PARAM_PROPERTY_ID = l_param_property_id;
2375
2376 if (sql%notfound) then
2377 null;
2378 end if;
2379
2380
2381 EXCEPTION
2382
2383 WHEN NO_DATA_FOUND THEN
2384 null;
2385 end;
2386
2387
2388
2389 end if;
2390
2391 x_return_status := fnd_api.g_ret_sts_success;
2392
2393 COMMIT;
2394
2395
2396 END Update_Column_Props;
2397
2398
2399
2400
2401
2402 --===================================================================
2403 -- NAME
2404 -- Delete_Parameter
2405 --
2406 -- PURPOSE
2407 -- Private api to delete work panel action parameter
2408 --
2409 -- NOTES
2410 -- 1. UWQ Work Panel Admin will use this procedure to delete an action parameter
2411 --
2412 --
2413 -- HISTORY
2414 -- 08-May-2002 GPAGADAL Created
2415
2416
2417 --===================================================================
2418 PROCEDURE Delete_Parameter (x_param_id IN NUMBER, x_param_set_id IN NUMBER) AS
2419
2420 l_language VARCHAR2(4);
2421
2422 l_param_property_id IEU_WP_PARAM_PROPS_TL.PARAM_PROPERTY_ID%type;
2423 BEGIN
2424 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2425
2426
2427
2428 EXECUTE immediate
2429 ' delete from ieu_wp_action_params where param_id = :1 '
2430 USING x_param_id;
2431
2432 if (sql%notfound) then
2433 null;
2434 end if;
2435
2436
2437 EXECUTE immediate
2438 ' delete from IEU_WP_PARAM_DEFS_TL where PARAM_ID = :1 '
2439 USING x_param_id;
2440
2441 if (sql%notfound) then
2442 null;
2443 end if;
2444
2445
2446
2447 EXECUTE immediate
2448 ' delete from IEU_WP_PARAM_DEFS_B where PARAM_ID = :1 '
2449 USING x_param_id;
2450
2451 if (sql%notfound) then
2452 null;
2453 end if;
2454
2455
2456
2457 EXECUTE immediate
2458 ' delete from IEU_WP_PARAM_PROPS_B ' ||
2459 ' where PARAM_ID = :1 '||
2460 ' and ACTION_PARAM_SET_ID = :2 '
2461 USING x_param_id, x_param_set_id;
2462
2463 if (sql%notfound) then
2464 null;
2465 end if;
2466
2467 begin
2468
2469 EXECUTE immediate
2470 ' select PARAM_PROPERTY_ID '||
2471 ' from IEU_WP_PARAM_PROPS_B '||
2472 ' where PARAM_ID = :1 ' ||
2473 ' and ACTION_PARAM_SET_ID= :2 '
2474 INTO l_param_property_id USING x_param_id, x_param_set_id;
2475
2476 delete from IEU_WP_PARAM_PROPS_TL
2477 where PARAM_PROPERTY_ID = l_param_property_id;
2478
2479 if (sql%notfound) then
2480 null;
2481 end if;
2482
2483
2484 EXCEPTION
2485
2486 WHEN NO_DATA_FOUND THEN
2487 null;
2488 end;
2489
2490
2491
2492 --delete param props if param has been deleted by some actions
2493 delete from ieu_wp_param_props_b where param_property_id in
2494 (select param_property_id
2495 from ieu_wp_param_props_b
2496 where param_id not in (select param_id from ieu_wp_param_defs_b));
2497
2498 if (sql%notfound) then
2499 null;
2500 end if;
2501
2502 delete from ieu_wp_param_props_tl where param_property_id in
2503 (select param_property_id
2504 from ieu_wp_param_props_b
2505 where param_id not in (select param_id from ieu_wp_param_defs_b));
2506
2507 if (sql%notfound) then
2508 null;
2509 end if;
2510
2511 COMMIT;
2512
2513
2514
2515 END Delete_Parameter;
2516
2517
2518 -- ===============================================================
2519 -- Start of Comments
2520 -- ===============================================================
2521 -- API Name
2522 -- Create_From_Action
2523 -- Type
2524 -- Private
2525 -- Pre-Req
2526 --
2527 -- Parameters
2528 --
2529 -- IN
2530 --
2531 -- r_wp_action_key VARCHAR2
2532 --
2533 -- OUT
2534 -- x_return_status OUT VARCHAR2
2535 -- x_msg_count OUT NUMBER
2536 -- x_msg_data OUT VARCHAR2
2537 --
2538 -- End of Comments
2539 -- ===============================================================
2540
2541 PROCEDURE Create_From_Action( x_return_status OUT NOCOPY VARCHAR2,
2542 x_msg_count OUT NOCOPY NUMBER,
2546 -- r_label IN VARCHAR2,
2543 x_msg_data OUT NOCOPY VARCHAR2,
2544 -- r_wp_action_key IN VARCHAR2,
2545 -- r_language IN VARCHAR2,
2547 -- r_desc IN VARCHAR2,
2548 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
2549 p_param_set_id IN IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type,
2550 p_maction_def_type_flag IN VARCHAR2)
2551 AS
2552
2553 l_wp_maction_def_id NUMBER(15);
2554 l_param_set_id NUMBER(15);
2555
2556 l_language VARCHAR2(4);
2557
2558 l_source_lang VARCHAR2(4);
2559
2560
2561 l_msg_count NUMBER(2);
2562
2563 l_msg_data VARCHAR2(2000);
2564
2565 l_return_status VARCHAR2(4);
2566
2567
2568 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
2569
2570
2571
2572 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
2573
2574 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
2575
2576
2577 BEGIN
2578
2579 fnd_msg_pub.delete_msg();
2580 x_return_status := fnd_api.g_ret_sts_success;
2581 FND_MSG_PUB.initialize;
2582 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2583 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2584 x_msg_data := '';
2585
2586
2587 IEU_WorkPanel_PVT.Validate_Action_Label(l_return_status,
2588 l_msg_count,
2589 l_msg_data,
2590 rec_obj.action_user_label,
2591 p_maction_def_type_flag,
2592 rec_obj.enum_id);
2593
2594 if (l_return_status = 'S') then
2595
2596 IEU_WP_ACTION_PVT.CreateFromAction2(x_return_status, x_msg_count, x_msg_data,
2597 rec_obj.maction_def_key, l_language,
2598 rec_obj.action_user_label, rec_obj.action_description,
2599 p_param_set_id,rec_obj.enum_id, 'Y');
2600 else
2601 x_return_status := l_return_status;
2602 x_msg_count := l_msg_count;
2603 x_msg_data := l_msg_data;
2604 end if; -- end (l_return_status = 'S')
2605
2606 commit;
2607 end Create_From_Action;
2608
2609
2610 PROCEDURE Create_From_Filter( x_return_status OUT NOCOPY VARCHAR2,
2611 x_msg_count OUT NOCOPY NUMBER,
2612 x_msg_data OUT NOCOPY VARCHAR2,
2613 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
2614 p_param_set_id IN IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type,
2615 p_maction_def_type_flag IN VARCHAR2)
2616 AS
2617
2618 l_wp_maction_def_id NUMBER(15);
2619 l_param_set_id NUMBER(15);
2620
2621 l_language VARCHAR2(4);
2622
2623 l_source_lang VARCHAR2(4);
2624
2625
2626 l_msg_count NUMBER(2);
2627
2628 l_msg_data VARCHAR2(2000);
2629
2630 l_return_status VARCHAR2(4);
2631
2632
2633 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
2634
2635
2636
2637 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
2638
2639 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
2640
2641
2642 BEGIN
2643
2644 fnd_msg_pub.delete_msg();
2645 x_return_status := fnd_api.g_ret_sts_success;
2646 FND_MSG_PUB.initialize;
2647 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2648 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2649 x_msg_data := '';
2650
2651
2652 IEU_WorkPanel_PVT.Validate_Action_Label(l_return_status,
2653 l_msg_count,
2654 l_msg_data,
2655 rec_obj.action_user_label,
2656 p_maction_def_type_flag,
2657 rec_obj.enum_id);
2658
2659 if (l_return_status = 'S') then
2660
2661 /*
2662 x_return_status OUT VARCHAR2,
2663 x_msg_count OUT NUMBER,
2664 x_msg_data OUT VARCHAR2,
2665 r_wp_action_key IN VARCHAR2,
2666 r_language IN VARCHAR2,
2667 r_label IN VARCHAR2,
2668 r_desc IN VARCHAR2,
2669 r_param_set_id IN NUMBER,
2670 r_enumId IN VARCHAR2,
2671 r_dev_data_flag IN VARCHAR2)
2672
2673 */
2674
2675 IEU_WP_ACTION_PVT.CreateFromQFilter(x_return_status, x_msg_count, x_msg_data,
2676 rec_obj.maction_def_key, l_language,
2677 rec_obj.action_user_label, rec_obj.action_description,
2678 p_param_set_id,rec_obj.enum_id, 'Y');
2679 else
2680 x_return_status := l_return_status;
2681 x_msg_count := l_msg_count;
2682 x_msg_data := l_msg_data;
2683 end if; -- end (l_return_status = 'S')
2684
2685 commit;
2686 end Create_From_Filter;
2687
2688
2689
2690
2691
2692
2693
2694 PROCEDURE Map_Action( x_return_status OUT NOCOPY VARCHAR2,
2695 x_msg_count OUT NOCOPY NUMBER,
2696 x_msg_data OUT NOCOPY VARCHAR2,
2697 p_enum_id IN NUMBER,
2698 p_application IN NUMBER,
2702
2699 p_param_set_id IN IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type,
2700 p_maction_def_type_flag IN VARCHAR2
2701 ) as
2703 l_language VARCHAR2(4);
2704
2705 l_source_lang VARCHAR2(4);
2706
2707 l_return_status VARCHAR2(4);
2708
2709 l_msg_count NUMBER(2);
2710
2711 l_msg_data VARCHAR2(2000);
2712
2713 act_map_obj SYSTEM.IEU_wp_action_maps_OBJ;
2714
2715 act_map_obj1 SYSTEM.IEU_wp_action_maps_OBJ;
2716
2717 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
2718
2719 l_temp_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
2720
2721 l_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
2722
2723 l_panel_sec_cat_code IEU_WP_ACTION_MAPS.PANEL_SEC_CAT_CODE%type;
2724 l_section_id IEU_WP_NODE_SECTION_MAPS.SECTION_ID%type;
2725 l_section_map_sequence IEU_WP_NODE_SECTION_MAPS.SECTION_MAP_SEQUENCE%type;
2726 --l_panel_sec_cat_code IEU_WP_ACTION_MAPS.PANEL_SEC_CAT_CODE%type;
2727 l_action_map_type_code IEU_WP_ACTION_MAPS.ACTION_MAP_TYPE_CODE%type;
2728 l_count NUMBER(2);
2729 l_wp_node_section_map_id IEU_WP_NODE_SECTION_MAPS.WP_NODE_SECTION_MAP_ID%type;
2730
2731
2732
2733 BEGIN
2734
2735 fnd_msg_pub.delete_msg();
2736 x_return_status := fnd_api.g_ret_sts_success;
2737 FND_MSG_PUB.initialize;
2738 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2739 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2740 x_msg_data := '';
2741
2742
2743
2744 select enum_type_uuid into l_enum_uuid
2745 from ieu_uwq_sel_enumerators
2746 where sel_enum_id = p_enum_id;
2747
2748 select max(m.action_map_sequence) into l_temp_map_sequence
2749 from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
2750 ieu_wp_act_param_sets_b sb
2751 --where m.application_id = p_application
2752 where m.action_map_type_code = 'NODE'
2753 and m.action_map_code = l_enum_uuid
2754 -- and m.application_id = db.application_id
2755 and db.maction_def_type_flag = p_maction_def_type_flag
2756 and db.maction_def_id = sb.wp_action_def_id
2757 and sb.action_param_set_id = m.action_param_set_id
2758 and m.responsibility_id = -1;
2759 -- and m.action_param_set_id = p_param_set_id;
2760
2761
2762
2763 if (l_temp_map_sequence IS NULL) then
2764 l_map_sequence := 1;
2765 else
2766 l_map_sequence := l_temp_map_sequence +1;
2767 end if;
2768 /*
2769 if (p_maction_def_type_flag ='W') then
2770 l_panel_sec_cat_code := null;
2771 elsif (p_maction_def_type_flag ='I') then
2772 l_panel_sec_cat_code := 'NOTES';
2773 elsif (p_maction_def_type_flag ='G') then
2774 l_panel_sec_cat_code := null;
2775 end if;
2776 */
2777 if (p_maction_def_type_flag ='W') then
2778 l_section_id := 10002;
2779 l_section_map_sequence := 2;
2780 l_panel_sec_cat_code := null;
2781 l_action_map_type_code := 'NODE';
2782 elsif (p_maction_def_type_flag ='I') then
2783 l_section_id := 10001;
2784 l_section_map_sequence := 1;
2785 l_panel_sec_cat_code := 'NOTES';
2786 l_action_map_type_code := 'NODE';
2787 elsif (p_maction_def_type_flag ='G') then
2788 l_panel_sec_cat_code := null;
2789 l_action_map_type_code := 'NODE';
2790 elsif (p_maction_def_type_flag = 'F') then
2791 l_action_map_type_code := 'NODE_DS';
2792 l_map_sequence := 1;
2793 l_panel_sec_cat_code := null;
2794 -- l_enum_uuid := p_datasource;
2795 end if;
2796
2797
2798 act_map_obj := SYSTEM.IEU_wp_action_maps_OBJ(null, p_param_set_id,
2799 p_application, null, 'NODE',
2800 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code,
2801 'N', 'Y');
2802
2803 IEU_WP_ACTION_PVT.CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj);
2804
2805
2806 act_map_obj1 := SYSTEM.IEU_wp_action_maps_OBJ(null, p_param_set_id,
2807 p_application, -1, 'NODE',
2808 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code,
2809 'N', 'Y');
2810
2811 IEU_WP_ACTION_PVT.CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj1);
2812
2813 /* dolee add on 8/26/04 mapped action should set registered flag to 'y' */
2814 if (p_maction_def_type_flag <> 'F') then
2815
2816 update IEU_UWQ_SEL_ENUMERATORS set
2817 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2818 LAST_UPDATE_DATE = SYSDATE,
2819 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2820 WORK_PANEL_REGISTERED_FLAG = 'Y'
2821 where SEL_ENUM_ID = p_enum_id;
2822
2823
2824 if (p_maction_def_type_flag <> 'G') then
2825 select count(*) into l_count
2826 from IEU_WP_NODE_SECTION_MAPS
2827 where ENUM_TYPE_UUID = l_enum_uuid
2828 and APPLICATION_ID = p_application
2829 AND SECTION_ID = l_section_id;
2830
2831 if (l_count > 0) then
2832 update IEU_WP_NODE_SECTION_MAPS set
2833 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2834 LAST_UPDATE_DATE = SYSDATE,
2835 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2836 RESPONSIBILITY_ID = null,
2837 SECTION_MAP_SEQUENCE = l_section_map_sequence
2838 where ENUM_TYPE_UUID = l_enum_uuid
2839 and APPLICATION_ID = p_application
2840 and SECTION_ID = l_section_id;
2841 else
2845 OBJECT_VERSION_NUMBER,
2842 select IEU_WP_NODE_SECTION_MAPS_S1.nextval into l_wp_node_section_map_id from sys.dual;
2843 insert INTO IEU_WP_NODE_SECTION_MAPS
2844 (WP_NODE_SECTION_MAP_ID,
2846 CREATED_BY,
2847 CREATION_DATE,
2848 LAST_UPDATED_BY,
2849 LAST_UPDATE_DATE,
2850 LAST_UPDATE_LOGIN,
2851 APPLICATION_ID,
2852 RESPONSIBILITY_ID,
2853 ENUM_TYPE_UUID,
2854 SECTION_ID,
2855 SECTION_MAP_SEQUENCE
2856 ) values
2857 (l_wp_node_section_map_id,
2858 0,
2859 FND_GLOBAL.USER_ID,
2860 SYSDATE,
2861 FND_GLOBAL.USER_ID,
2862 SYSDATE,
2863 FND_GLOBAL.LOGIN_ID,
2864 p_application,
2865 null,
2866 l_enum_uuid,
2867 l_section_id,
2868 l_section_map_sequence);
2869
2870 end if;
2871 end if;
2872 end if;
2873
2874 x_return_status := fnd_api.g_ret_sts_success;
2875 COMMIT;
2876
2877
2878 EXCEPTION
2879 WHEN fnd_api.g_exc_error THEN
2880 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
2881
2882 ROLLBACK;
2883 x_return_status := fnd_api.g_ret_sts_error;
2884
2885 WHEN fnd_api.g_exc_unexpected_error THEN
2886 --dbms_outPUT.PUT_LINE('unexpected Error : '||sqlerrm);
2887
2888 ROLLBACK;
2889 x_return_status := fnd_api.g_ret_sts_unexp_error;
2890
2891 WHEN OTHERS THEN
2892 --dbms_outPUT.PUT_LINE('other Error : '||sqlerrm);
2893
2894 ROLLBACK;
2895 x_return_status := fnd_api.g_ret_sts_unexp_error;
2896
2897
2898 END Map_Action;
2899
2900 PROCEDURE Update_Data_Type ( x_return_status OUT NOCOPY VARCHAR2,
2901 x_msg_count OUT NOCOPY NUMBER,
2902 x_msg_data OUT NOCOPY VARCHAR2,
2903 p_wp_action_def_id IN NUMBER,
2904 p_param_id IN NUMBER)
2905 AS
2906
2907
2908 l_language VARCHAR2(4);
2909
2910 l_source_lang VARCHAR2(4);
2911
2912
2913 l_msg_count NUMBER(2);
2914
2915 l_msg_data VARCHAR2(2000);
2916
2917 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
2918
2919
2920 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
2921
2922 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
2923
2924 cursor c_cur is
2925 select distinct ppb.action_param_set_id, ap.param_id
2926 from
2927 ieu_wp_action_params ap,
2928 ieu_wp_param_props_b ppb
2929 where
2930 ap.wp_action_def_id=p_wp_action_def_id
2931 and ap.param_id = ppb.param_id
2932 and ap.param_id=p_param_id;
2933
2934 BEGIN
2935
2936
2937 fnd_msg_pub.delete_msg();
2938 x_return_status := fnd_api.g_ret_sts_success;
2939 FND_MSG_PUB.initialize;
2940 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2941 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2942 x_msg_data := '';
2943
2944
2945
2946 delete from ieu_wp_param_props_b
2947 where action_param_set_id in ( select distinct ppb.ACTION_PARAM_SET_ID
2948 from
2949 ieu_wp_action_params ap,
2950 ieu_wp_param_props_b ppb
2951 where
2952 ap.WP_ACTION_DEF_ID=p_wp_action_def_id
2953 and ap.PARAM_ID = ppb.PARAM_ID)
2954 and param_id =p_param_id
2955 and property_id in (10002, 10013, 10014, 10015, 10016, 10017, 10018, 10019, 10020, 10010, 10003, 10021, 10011,10022);
2956
2957 if (sql%notfound) then
2958 null;
2959 end if;
2960
2961 x_return_status := fnd_api.g_ret_sts_success;
2962 COMMIT;
2963 EXCEPTION
2964 WHEN fnd_api.g_exc_unexpected_error THEN
2965 ROLLBACK;
2966 x_return_status := fnd_api.g_ret_sts_unexp_error;
2967
2968 WHEN OTHERS THEN
2969 ROLLBACK;
2970 x_return_status := fnd_api.g_ret_sts_unexp_error;
2971
2972
2973 END Update_Data_Type;
2974
2975 PROCEDURE Update_Multi_Select_Flag ( x_return_status OUT NOCOPY VARCHAR2,
2976 x_msg_count OUT NOCOPY NUMBER,
2977 x_msg_data OUT NOCOPY VARCHAR2,
2978 p_wp_action_def_id IN NUMBER)
2979 as
2980
2981
2982 l_language VARCHAR2(4);
2983
2984 l_source_lang VARCHAR2(4);
2985
2986
2987 l_msg_count NUMBER(2);
2988
2989 l_msg_data VARCHAR2(2000);
2990
2991 l_param_id IEU_WP_PARAM_DEFS_B.PARAM_ID%TYPE;
2992
2993
2994 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
2995
2996 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
2997
2998
2999 BEGIN
3000 fnd_msg_pub.delete_msg();
3001 x_return_status := fnd_api.g_ret_sts_success;
3002 FND_MSG_PUB.initialize;
3003 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
3004 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
3005 x_msg_data := '';
3006
3007
3008 delete from ieu_wp_param_props_b
3009 where property_id in(10010, 10003, 10021, 10011)
3010 and action_param_set_id in (select distinct ppb.ACTION_PARAM_SET_ID
3011 from
3012 ieu_wp_action_params ap,
3013 ieu_wp_param_props_b ppb
3014 where
3015 ap.WP_ACTION_DEF_ID= p_wp_action_def_id
3016 and ap.PARAM_ID = ppb.PARAM_ID);
3017
3018 if (sql%notfound) then
3019 null;
3020 end if;
3021
3022 x_return_status := fnd_api.g_ret_sts_success;
3023 COMMIT;
3024 EXCEPTION
3025 WHEN fnd_api.g_exc_unexpected_error THEN
3026 ROLLBACK;
3027 x_return_status := fnd_api.g_ret_sts_unexp_error;
3028
3029 WHEN OTHERS THEN
3030 ROLLBACK;
3031 x_return_status := fnd_api.g_ret_sts_unexp_error;
3032
3033
3034
3035 END Update_Multi_Select_Flag;
3036
3037
3038
3039 PROCEDURE Param_ReOrdering(x_return_status OUT NOCOPY VARCHAR2,
3040 x_msg_count OUT NOCOPY NUMBER,
3041 x_msg_data OUT NOCOPY VARCHAR2,
3042 p_wp_action_def_id IN NUMBER,
3043 p_action_param_set_id IN NUMBER)
3044 As
3045 cursor c_cur is
3046 select ppb.PARAM_PROPERTY_ID, pdb.PARAM_ID, ppb.PROPERTY_VALUE
3047 from ieu_wp_action_params p,
3048 ieu_wp_param_defs_b pdb,
3049 ieu_wp_param_props_b ppb
3050 where p.WP_ACTION_DEF_ID = p_wp_action_def_id
3051 and p.PARAM_ID = pdb.PARAM_ID
3052 and pdb.PARAM_ID = ppb.PARAM_ID
3053 and ppb.ACTION_PARAM_SET_ID = p_action_param_set_id
3054 and ppb.PROPERTY_ID = 10000
3055 order by to_number(ppb.PROPERTY_VALUE);
3056
3057 l_count NUMBER:=1;
3058
3059
3060
3061 BEGIN
3062 fnd_msg_pub.delete_msg();
3063 x_return_status := fnd_api.g_ret_sts_success;
3064 FND_MSG_PUB.initialize;
3065
3066 x_msg_data := '';
3067 for c_rec in c_cur LOOP
3068 if l_count <> c_rec.PROPERTY_VALUE then
3069
3070 update ieu_wp_param_props_b
3071 set property_value = l_count
3072 where param_id = c_rec.param_id
3073 and param_property_id = c_rec.param_property_id
3074 and property_value = c_rec.property_value
3075 and property_id = 10000
3076 and action_param_set_id = p_action_param_set_id;
3077
3078 end if;
3079 l_count :=l_count+1;
3080 end loop;
3081 commit;
3082
3083 END Param_ReOrdering;
3084
3085
3086
3087
3088
3089 END IEU_WorkPanel_PVT;