1 PACKAGE BODY IEU_WORK_PROVIDER_PUB AS
2 /* $Header: ieuwpdb.pls 115.4 2004/03/31 17:12:10 dolee 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 DeleteNode(x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 r_enumId IN ieu_uwq_sel_enumerators.sel_enum_id%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 cursor c_cursor is
37 select distinct a.action_param_set_id
38 from ieu_wp_action_maps a, ieu_uwq_sel_enumerators b
39 where b.sel_enum_id = r_enumId
40 and a.action_map_code = b.enum_type_uuid;
41 begin
42 fnd_msg_pub.delete_msg();
43 x_return_status := fnd_api.g_ret_sts_success;
44 FND_MSG_PUB.initialize;
45 x_msg_data := '';
46 --DBMS_OUTPUT.Put_Line('before cursor loop');
47 -- find enumId, applicationId, and panel for
48 -- given a sel_enum_id
49 -- a. delete the work node definition from the sel_enumerators table
50 -- b. delete the two profile options created for the node
51 -- c. delete any work panel data created for this node
52 -- d. delete any data source mapping set up for this node (used by quick filters)
53
54 -- start real work
55 -- d.
56 Execute IMMEDIATE ' delete from ieu_wp_node_section_maps where enum_type_uuid in '||
57 ' (select enum_type_uuid from ieu_uwq_sel_enumerators '||
58 ' where sel_enum_id = :1 ) '
59 USING r_enumId;
60
61 -- c.
62 /*
63 PROCEDURE Delete_Action_From_Node (
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2,
67 x_param_set_id IN NUMBER,
68 x_node_id IN NUMBER,
69 x_maction_id IN NUMBER,// if no maction_id information, provide -1
70 x_maction_def_flag IN VARCHAR2
71 );
72 END IEU_Work_ACTION_PVT;
73 */
74
75 FOR cur_rec IN c_cursor
76 LOOP
77 IEU_WORK_ACTION_PVT.Delete_Action_From_Node(x_return_status,
78 x_msg_count ,
79 x_msg_data ,
80 cur_rec.action_param_set_id,
81 r_enumId,
82 '-1',
83 'W');
84 END loop;
85
86 -- b.
87 EXECUTE IMMEDIATE ' delete from fnd_profile_options_tl where profile_option_name '||
88 ' in (select work_q_enable_profile_option from ieu_uwq_sel_enumerators '||
89 ' where sel_enum_id = :1 ) '
90 USING r_enumId;
91 EXECUTE IMMEDIATE ' delete from fnd_profile_options_tl where profile_option_name '||
92 ' in (select work_q_order_profile_option from ieu_uwq_sel_enumerators '||
93 ' where sel_enum_id = :1 ) '
94 USING r_enumId;
95 EXECUTE IMMEDIATE ' delete from fnd_profile_options where profile_option_name '||
96 ' in (select work_q_enable_profile_option from ieu_uwq_sel_enumerators '||
97 ' where sel_enum_id = :1 ) '
98 USING r_enumId;
99 EXECUTE IMMEDIATE ' delete from fnd_profile_options where profile_option_name '||
100 ' in (select work_q_order_profile_option from ieu_uwq_sel_enumerators '||
101 ' where sel_enum_id = :1 ) '
102 USING r_enumId;
103
104 -- a.
105 EXECUTE IMMEDIATE 'delete from fnd_lookup_values where (lookup_type, lookup_code) in '||
106 ' (select work_q_label_lu_type, work_q_label_lu_code ' ||
107 ' from ieu_uwq_sel_enumerators where sel_enum_id = :1 )'
108 USING r_enumId;
109 EXECUTE IMMEDIATE ' delete from ieu_uwq_sel_enumerators '||
110 ' where sel_enum_id = :1 '
111 USING r_enumId;
112
113 EXCEPTION
114 WHEN fnd_api.g_exc_error THEN
115 ROLLBACK;
116 x_return_status := fnd_api.g_ret_sts_error;
117
118 WHEN fnd_api.g_exc_unexpected_error THEN
119 ROLLBACK;
120 x_return_status := fnd_api.g_ret_sts_unexp_error;
121
122 WHEN OTHERS THEN
123 ROLLBACK;
124 x_return_status := fnd_api.g_ret_sts_unexp_error;
125
126 commit;
127 end DeleteNode;
128 END IEU_WORK_PROVIDER_PUB;
[Home] [Help]