[Home] [Help]
PACKAGE BODY: APPS.IEU_WORK_PANEL_PUB
Source
1 PACKAGE BODY IEU_WORK_PANEL_PUB AS
2 /* $Header: IEUDELB.pls 120.0 2005/06/02 15:46:23 appldev noship $ */
3
4 -- Start of Comments
5 -- ===============================================================
6 -- API Name
7 -- DeleteActionPackage
8 -- Type
9 -- Private
10 -- Pre-Req
11 --
12 -- Parameters
13 --
14 -- IN
15 --
16 -- x_action_key IN VARCHAR2(32) Required
17 --
18 -- End of Comments
19 -- ===============================================================
20
21 PROCEDURE DeleteAction(x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 r_action_key IN ieu_uwq_maction_defs_b.maction_def_key%type)
25 As
26 v_cursor1 NUMBER;
27 v_cursor NUMBER;
28 v_numrows1 NUMBER;
29 sql_stmt varchar2(2000);
30 l_property_id ieu_wp_param_props_b.property_id%type;
31 l_delete_param_property_id IEU_WP_PARAM_PROPS_B.param_property_id%type;
32 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
33 l_enumID ieu_uwq_sel_enumerators.sel_enum_id%type;
34 l_applId ieu_wp_action_maps.application_id%type;
35 l_panel ieu_uwq_maction_defs_b.maction_def_type_flag%type;
36 TYPE c_cursor5 IS REF CURSOR;
37 c_ref c_cursor5;
38 -- find enough information for reordering later after delete
39 CURSOR c_cursor2 is
40 SELECT DISTINCT a.sel_enum_id, b.application_id, c.maction_def_type_flag
41 FROM ieu_uwq_sel_enumerators a, ieu_wp_action_maps b, ieu_uwq_maction_defs_b c
42 WHERE a.enum_type_uuid = b.action_map_code
43 AND c.maction_def_key = LTRIM(RTRIM(r_action_key))
44 AND b.action_param_set_id IN (SELECT action_param_set_id
45 FROM ieu_wp_act_param_sets_b
46 WHERE wp_action_def_id IN (SELECT maction_def_id
47 FROM ieu_uwq_maction_defs_b
48 WHERE maction_def_key = LTRIM(RTRIM(r_action_key))
49 )
50 );
51
52 -- this cursor is to find outall action_param_set_id
53 --which wp_action_def_id is related to x_action_key
54 cursor c_cursor is
55 select distinct a.action_param_set_id
56 from ieu_wp_act_param_sets_b a, ieu_uwq_maction_defs_b b
57 where b.MACTION_DEF_KEY = LTRIM(RTRIM(r_action_key))
58 and b.maction_def_id = a.wp_action_def_id;
59
60 cursor c_cursor1 is
61 select maction_def_id
62 from ieu_uwq_maction_defs_b
63 where MACTION_DEF_KEY = r_action_key;
64
65 begin
66 fnd_msg_pub.delete_msg();
67 x_return_status := fnd_api.g_ret_sts_success;
68 FND_MSG_PUB.initialize;
69 x_msg_data := '';
70 --DBMS_OUTPUT.Put_Line('before cursor loop');
71 -- find enumId, applicationId, and panel for
72 FOR cur_rec IN c_cursor
73 LOOP
74 --DBMS_OUTPUT.Put_Line('going to delete action_param_set_id is '||cur_rec.action_param_set_id);
75 --v_cursor := DBMS_SQL.OPEN_CURSOR;
76 sql_stmt :=' select param_property_id, property_id'||
77 ' from ieu_wp_param_props_b '||
78 ' where action_param_set_id = :id';
79 Open c_ref FOR sql_stmt USING cur_rec.action_param_set_id;
80 -- DBMS_SQL.parse(v_cursor, sql_stmt, DBMS_SQL.V7);
81 -- DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, l_delete_param_property_id);
82 -- DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, l_property_id);
83 -- v_numrows1 := DBMS_SQL.EXECUTE(v_cursor);
84 --v_numrows1 := DBMS_SQL.FETCH_ROWS(v_cursor);
85 LOOP
86 --if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
87 -- exit;
88 --end if;
89 --DBMS_SQL.COLUMN_VALUE(v_cursor, 1, l_delete_param_property_id);
90 --DBMS_SQL.COLUMN_VALUE(v_cursor, 2, l_property_id);
91 FETCH c_ref INTO l_delete_param_property_id, l_property_id;
92 EXIT WHEN c_ref%NOTFOUND;
93
94 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
95 from ieu_wp_properties_b
96 where property_id = l_property_id;
97
98 if (l_trans_flag = 'Y') then
99 delete from ieu_wp_param_props_tl where param_property_id = l_delete_param_property_id;
100 --DBMS_OUTPUT.Put_Line(' delete ieu_wp_param_props_tl for id '|| l_delete_param_property_id);
101 end if ;
102
103 delete from ieu_wp_param_props_b where param_property_id = l_delete_param_property_id;
104 --DBMS_OUTPUT.Put_Line(' delete ieu_wp_param_props_b for id '|| l_delete_param_property_id);
105
106 end LOOP;
107 --DBMS_SQL.CLOSE_CURSOR(v_cursor);
108 Close c_ref;
109
110 delete from ieu_wp_act_param_sets_b where action_param_set_id = cur_rec.action_param_set_id;
111 delete from ieu_wp_act_param_sets_tl where action_param_set_id = cur_rec.action_param_set_id;
112 delete from ieu_wp_action_maps where action_param_set_id = cur_rec.action_param_set_id;
113 --DBMS_OUTPUT.Put_Line(' delete 3 tables for id '|| cur_rec.action_param_set_id);
114 end LOOP;
115
116 FOR cur_rec IN c_cursor1
117 LOOP
118 delete from ieu_uwq_maction_defs_b where maction_def_id = cur_rec.maction_def_id;
119 delete from ieu_uwq_maction_defs_tl where maction_def_id = cur_rec.maction_def_id;
120 -- --DBMS_OUTPUT.Put_Line(' delete 2 tables for id '|| cur_rec.maction_def_id);
121 end loop;
122
123 FOR cur_rec IN c_cursor2
124 LOOP
125 IEU_WP_ACTION_PVT.ReOrdering(x_return_status ,x_msg_count , x_msg_data
126 , cur_rec.sel_enum_id, cur_rec.application_id, cur_rec.maction_def_type_flag);
127 end loop;
128 EXCEPTION
129 WHEN fnd_api.g_exc_error THEN
130 ROLLBACK;
131 x_return_status := fnd_api.g_ret_sts_error;
132
133 WHEN fnd_api.g_exc_unexpected_error THEN
134 ROLLBACK;
135 x_return_status := fnd_api.g_ret_sts_unexp_error;
136
137 WHEN OTHERS THEN
138 ROLLBACK;
139 x_return_status := fnd_api.g_ret_sts_unexp_error;
140
141 commit;
142 end DeleteAction;
143 -- Start of Comments
144 -- ===============================================================
145 -- API Name
146 -- DeleteCloneAction
147 -- Type
148 -- Public
149 -- Pre-Req
150 --
151 -- Parameters
152 --
153 -- IN
154 --
155 -- r_Lang IN ieu_wp_act_param_sets_tl.language%type Required,
156 -- r_Action_LABEL IN ieu_wp_act_param_sets_tl.ACTION_PARAM_SET_LABEL%type Required
157 -- End of Comments
158 -- ===============================================================
159
160 PROCEDURE DeleteCloneAction (x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2,
163 r_Lang IN ieu_wp_act_param_sets_tl.language%type,
164 r_Action_Label IN ieu_wp_act_param_sets_tl.ACTION_PARAM_SET_LABEL%type,
165 r_node_id IN ieu_uwq_sel_enumerators.sel_enum_id%type)
166 As
167 v_cursor1 NUMBER;
168 v_cursor NUMBER;
169 v_numrows1 NUMBER;
170 sql_stmt varchar2(2000);
171 l_property_id ieu_wp_param_props_b.property_id%type;
172 l_delete_param_property_id IEU_WP_PARAM_PROPS_B.param_property_id%type;
173 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
174 l_language VARCHAR2(4);
175
176 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type;
177 l_maction_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%type;
178 l_num_map_entries NUMBER;
179 l_num_set_entries NUMBER;
180 TYPE c_cursor5 IS REF CURSOR;
181 c_ref c_cursor5;
182 -- this cursor is to find outall action_param_set_id
183 --which wp_action_def_id is related to x_action_key
184 cursor c_cursor is
185 select action_param_set_id
186 from ieu_wp_act_param_sets_tl
187 where LANGUAGE = r_Lang
188 AND ACTION_PARAM_SET_LABEL = LTRIM(RTRIM(r_Action_Label))
189 AND action_param_set_id IN (SELECT action_param_set_id
190 FROM ieu_wp_action_maps
191 WHERE action_map_code IN (SELECT enum_type_uuid
192 FROM ieu_uwq_sel_enumerators
193 WHERE sel_enum_id = LTRIM(RTRIM(r_node_id))
194 )
195 );
196
197 -- find enough information for reordering later after delete
198 CURSOR c_cursor2 is
199 SELECT DISTINCT b.application_id, c.maction_def_type_flag
200 FROM ieu_uwq_sel_enumerators a, ieu_wp_action_maps b, ieu_uwq_maction_defs_b c
201 WHERE a.enum_type_uuid = b.action_map_code
202 AND a.sel_enum_id = r_node_id
203 AND c.maction_def_id IN (SELECT wp_action_def_id
204 FROM ieu_wp_act_param_sets_b
205 WHERE action_param_set_id IN (SELECT action_param_set_id
206 FROM ieu_wp_act_param_sets_tl
207 WHERE action_param_set_label = LTRIM(RTRIM(r_Action_Label))
208 AND LANGUAGE = r_Lang
209 )
210 )
211 AND b.action_param_set_id IN (SELECT action_param_set_id
212 FROM ieu_wp_act_param_sets_tl
213 WHERE action_param_set_label = LTRIM(RTRIM(r_Action_Label))
214 );
215
216 begin
217 fnd_msg_pub.delete_msg();
218 x_return_status := fnd_api.g_ret_sts_success;
219 FND_MSG_PUB.initialize;
220 x_msg_data := '';
221 --DBMS_OUTPUT.Put_Line('before cursor loop');
222
223
224
225 FOR cur_rec IN c_cursor
226 LOOP
227 -- v_cursor := DBMS_SQL.OPEN_CURSOR;
228 -- sql_stmt :=' select param_property_id, property_id'||
229 -- ' from ieu_wp_param_props_b '||
230 -- ' where action_param_set_id = :id';
231 --DBMS_SQL.parse(v_cursor, sql_stmt, DBMS_SQL.V7);
232 --DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, l_delete_param_property_id);
233 --DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, l_property_id);
234 --v_numrows1 := DBMS_SQL.EXECUTE(v_cursor);
235 --v_numrows1 := DBMS_SQL.FETCH_ROWS(v_cursor);
236 --DBMS_OUTPUT.Put_Line(' delete record from ieu_wp_param_props_b for action_param_set_id '||l_temp||', property id is 10003 is '|| v_numrows1 );
237 -- OPEN c_ref FOR sql_stmt USING cur_rec.action_param_set_id;
238 -- LOOP
239 -- FETCH c_ref INTO l_delete_param_property_id, l_property_id;
240 -- EXIT WHEN c_ref%NOTFOUND;
241
242 --if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
243 -- exit;
244 --end if;
245 --DBMS_SQL.COLUMN_VALUE(v_cursor, 1, l_delete_param_property_id);
246 --DBMS_SQL.COLUMN_VALUE(v_cursor, 2, l_property_id);
247
248 -- select VALUE_TRANSLATABLE_FLAG into l_trans_flag
249 -- from ieu_wp_properties_b
250 -- where property_id = l_property_id;
251 -- if (l_trans_flag = 'Y') then
252 -- delete from ieu_wp_param_props_tl where param_property_id = l_delete_param_property_id;
253 --DBMS_OUTPUT.Put_Line(' delete ieu_wp_param_props_tl for id '|| l_delete_param_property_id);
254 -- end if ;
255
256 -- delete from ieu_wp_param_props_b where param_property_id = l_delete_param_property_id;
257 --DBMS_OUTPUT.Put_Line(' delete ieu_wp_param_props_b for id '|| l_delete_param_property_id);
258
259 -- end LOOP;
260 --DBMS_SQL.CLOSE_CURSOR(v_cursor);
261 -- Close c_ref;
262
263 -- delete from ieu_wp_act_param_sets_b where action_param_set_id = cur_rec.action_param_set_id;
264 -- delete from ieu_wp_act_param_sets_tl where action_param_set_id = cur_rec.action_param_set_id;
265 -- delete from ieu_wp_action_maps where action_param_set_id = cur_rec.action_param_set_id;
266 --DBMS_OUTPUT.Put_Line(' delete 3 tables for id '|| cur_rec.action_param_set_id);
267
268 --1. determine if this action has 1:1 for action_maps to action_param_sets
269 --2. delete from maps
270 --3. if 1:1 in 1,
271 --a. delete from action_param_sets and param_props
272
273 --1.
274 SELECT count(unique(action_map_code))
275 INTO l_num_map_entries
276 FROM ieu_wp_action_maps
277 WHERE action_map_type_code = 'NODE' AND
278 action_param_set_id = cur_rec.action_param_set_id;
279
280 --2.
281 DELETE FROM ieu_wp_action_maps
282 WHERE action_param_set_id = cur_rec.action_param_set_id AND
283 action_map_type_code = 'NODE' AND
284 action_map_code IN
285 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
286 WHERE sel_enum_id = r_node_id);
287
288 --3.
289 IF (l_num_map_entries = 1) THEN
290 --a.
291 DELETE FROM ieu_wp_param_props_tl
292 WHERE param_property_id IN
293 (SELECT param_property_id FROM ieu_wp_param_props_b
294 WHERE
295 action_param_set_id = cur_rec.action_param_set_id);
296
297 DELETE FROM ieu_wp_param_props_b
298 WHERE action_param_set_id = cur_rec.action_param_set_id;
299
300 DELETE FROM ieu_wp_act_param_sets_tl
301 WHERE action_param_set_id = cur_rec.action_param_set_id;
302
303 DELETE FROM ieu_wp_act_param_sets_b
304 WHERE action_param_set_id = cur_rec.action_param_set_id;
305 END if;
306 end LOOP;
307 FOR cur_rec IN c_cursor2
308 LOOP
309 IEU_WP_ACTION_PVT.ReOrdering(x_return_status ,x_msg_count , x_msg_data
310 , r_node_id, cur_rec.application_id, cur_rec.maction_def_type_flag);
311 end loop;
312 EXCEPTION
313 WHEN fnd_api.g_exc_error THEN
314 ROLLBACK;
315 x_return_status := fnd_api.g_ret_sts_error;
316
317 WHEN fnd_api.g_exc_unexpected_error THEN
318 ROLLBACK;
319 x_return_status := fnd_api.g_ret_sts_unexp_error;
320
321 WHEN OTHERS THEN
322 ROLLBACK;
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324
325
326 commit;
327 end DeleteCloneAction;
328
329 -- ===============================================================
330 -- Start of Comments
331 -- ===============================================================
332 -- API Name
333 -- DeleteParam
334 -- Type
335 -- Public
336 -- Pre-Req
337 --
338 -- Parameters
339 --
340 -- IN
341 --
342 -- r_PARAM_NAME IN ieu_wp_param_defs_b.param_id%type Required
343 -- r_ACTION_KEY IN ieu_uwq_maction_defs-b.maction_def_key%type --- Requried
344 -- End of Comments
345 -- ===============================================================
346
347 PROCEDURE DeleteActionParam(x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2,
350 r_PARAM_NAME IN ieu_wp_param_defs_b.param_name%type,
351 r_ACTION_KEY IN ieu_uwq_maction_defs_b.maction_def_key%type)
352 As
353 v_cursor1 NUMBER;
354 v_cursor NUMBER;
355 v_numrows1 NUMBER;
356 sql_stmt1 varchar2(2000);
357 l_property_id ieu_wp_param_props_b.property_id%type;
358 l_delete_param_property_id IEU_WP_PARAM_PROPS_B.param_property_id%type;
359 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
360 l_count NUMBER := 0;
361 l_param_property_id NUMBER;
362 l_property_value VARCHAR2(4000);
363 TYPE c_cursor5 IS REF CURSOR;
364 c_ref c_cursor5;
365 --this cursor is used for parameter reording
366 CURSOR c_cursor3 is
367 SELECT action_param_set_id
368 FROM ieu_wp_act_param_sets_b
369 WHERE wp_action_def_id IN (SELECT maction_def_id
373
370 FROM ieu_uwq_maction_defs_b
371 WHERE maction_def_key = LTRIM(RTRIM(r_action_key))
372 );
374
375 -- this cursor is to find outall action_param_set_id
376 --which wp_action_def_id is related to x_action_key
377 cursor c_cursor is
378 select param_id
379 from ieu_wp_param_defs_b
380 where param_name = LTRIM(RTRIM(r_param_name))
381 AND param_id IN (SELECT param_id
382 FROM ieu_wp_action_params
383 WHERE WP_ACTION_DEF_ID IN (SELECT maction_def_id
384 FROM ieu_uwq_maction_defs_b
385 WHERE MACTION_DEF_KEY = LTRIM(RTRIM(r_ACTION_KEY))
386 )
387 );
388
389 cursor c_cursor1 is
390 select a.param_property_id, a.property_id
391 from ieu_wp_param_props_b a, ieu_wp_param_defs_b b
392 where a.param_id = b.param_id
393 and b.param_name = LTRIM(RTRIM(r_param_name))
394 AND a.param_id IN (SELECT param_id
395 FROM ieu_wp_action_params
396 WHERE WP_ACTION_DEF_ID IN (SELECT maction_def_id
397 FROM ieu_uwq_maction_defs_b
398 WHERE MACTION_DEF_KEY = LTRIM(RTRIM(r_ACTION_KEY))
399 )
400 );
401
402 cursor c_cursor2 is
403 select a.ACTION_PARAM_MAP_ID
404 from ieu_wp_action_params a, ieu_wp_param_defs_b b
405 where a.param_id = b.param_id
406 and b.param_name = LTRIM(RTRIM(r_param_name))
407 AND a.param_id IN (SELECT param_id
408 FROM ieu_wp_action_params
409 WHERE WP_ACTION_DEF_ID IN (SELECT maction_def_id
410 FROM ieu_uwq_maction_defs_b
411 WHERE MACTION_DEF_KEY = LTRIM(RTRIM(r_ACTION_KEY))
412 )
413 );
414 begin
415 fnd_msg_pub.delete_msg();
416 x_return_status := fnd_api.g_ret_sts_success;
417 FND_MSG_PUB.initialize;
418 x_msg_data := '';
419 FOR cur_rec IN c_cursor1
420 LOOP
421 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
422 from ieu_wp_properties_b
423 where property_id = cur_rec.property_id;
424
425 if (l_trans_flag = 'Y') then
426 delete from ieu_wp_param_props_tl where param_property_id = cur_rec.param_property_id;
427 end if ;
428
429 delete from ieu_wp_param_props_b where param_property_id = cur_rec.param_property_id;
430
431 end LOOP;
432 FOR cur_rec IN c_cursor2
433 LOOP
434 delete from ieu_wp_action_params where ACTION_PARAM_MAP_ID= cur_rec.ACTION_PARAM_MAP_ID;
435 end LOOP;
436
437 FOR cur_rec IN c_cursor
438 LOOP
439 delete from ieu_wp_param_defs_b where param_ID= cur_rec.param_ID;
440 delete from ieu_wp_param_defs_tl where param_ID= cur_rec.param_ID;
441 end LOOP;
442 FOR cur_rec IN c_cursor3
443 loop
444 l_count :=1;
445 v_cursor1 := DBMS_SQL.OPEN_CURSOR;
446 --find out the action_param_set_id which does not have param_id
447 sql_stmt1 := ' SELECT param_property_id, property_value '||
448 ' FROM ieu_wp_param_props_b '||
449 ' WHERE property_id = 10000 '||
450 ' AND action_param_set_id = :id ' ||
451 ' order by property_value';
452
453 Open c_ref FOR sql_stmt1 USING cur_rec.action_param_set_id;
454 --DBMS_SQL.parse(v_cursor1, sql_stmt1, DBMS_SQL.V7);
455 --DBMS_SQL.DEFINE_COLUMN(v_cursor1, 1, l_param_property_id);
456 --DBMS_SQL.DEFINE_COLUMN(v_cursor1, 2, l_property_value,4000);
457 --v_numrows1 := DBMS_SQL.EXECUTE(v_cursor1);
458
459 LOOP
460 FETCH c_ref INTO l_param_property_id, l_property_value;
461 EXIT WHEN c_ref%NOTFOUND;
462
463 --if DBMS_SQL.FETCH_ROWS(v_cursor1) = 0 then
464 -- exit;
465 --end if;
466 --DBMS_SQL.COLUMN_VALUE(v_cursor1, 1, l_param_property_id);
467 --DBMS_SQL.COLUMN_VALUE(v_cursor1, 2, l_property_value);
468 IF l_property_value <> l_count then
469 UPDATE ieu_wp_param_props_b
470 SET PROPERTY_VALUE = l_count,
471 last_update_date = sysdate,
472 last_update_login = 0
473 WHERE PARAM_PROPERTY_ID = l_param_property_id;
474
475
476 END if;
477 l_count := l_count +1;
478
479 end loop; --for v_cursor
480 --DBMS_SQL.CLOSE_CURSOR(v_cursor1);
481 CLOSE c_ref;
482 END loop;
483 EXCEPTION
484 WHEN fnd_api.g_exc_error THEN
485 ROLLBACK;
486 x_return_status := fnd_api.g_ret_sts_error;
487
488 WHEN fnd_api.g_exc_unexpected_error THEN
489 ROLLBACK;
490 x_return_status := fnd_api.g_ret_sts_unexp_error;
491
492 WHEN OTHERS THEN
493 ROLLBACK;
494 x_return_status := fnd_api.g_ret_sts_unexp_error;
495
496
497
498 commit;
499 end DeleteActionParam;
500
501 END IEU_WORK_PANEL_PUB;