[Home] [Help]
PACKAGE BODY: APPS.QPR_DELETE_AW
Source
1 PACKAGE BODY QPR_DELETE_AW AS
2 /* $Header: QPRUDAWB.pls 120.2 2007/11/29 08:23:43 bhuchand noship $ */
3
4
5
6 PROCEDURE DELETE_AW (
7 ERRBUF OUT NOCOPY VARCHAR2,
8 RETCODE OUT NOCOPY VARCHAR2,
9 P_PRICE_PLAN_ID IN NUMBER,
10 P_DELETE_QPR_TABLES IN VARCHAR2)
11
12 IS
13
14 l_unable_to_delete exception;
15 l_err_num number;
16 l_err_msg varchar2(2000);
17 l_aw_name1 varchar2(200);
18 l_stmtnum number;
19 l_rownum number;
20 l_price_plan_id number;
21 l_success varchar2(1) := FND_API.G_RET_STS_SUCCESS;
22 l_error varchar2(1) := FND_API.G_RET_STS_ERROR;
23 l_output1 varchar2(1);
24 l_output2 varchar2(1);
25 l_output3 varchar2(1);
26 l_table_name varchar2(30);
27 l_msg_count number;
28 l_msg_data varchar2(2000);
29
30 BEGIN
31
32 begin
33 SELECT price_plan_id, aw_code
34 INTO l_price_plan_id, l_aw_name1
35 FROM QPR_PRICE_PLANS_B
36 WHERE PRICE_PLAN_ID =P_PRICE_PLAN_ID
37 and rownum < 2;
38 exception
39 when no_data_found then
40 retcode := 2;
41 errbuf := sqlerrm;
42 fnd_file.put_line(fnd_file.log, 'Price Plan not found');
43 return;
44 end;
45
46 begin
47 select 1 into l_rownum
48 from qpr_pn_lines
49 where price_plan_id = p_price_plan_id
50 and rownum < 2;
51
52 retcode := 1;
53 errbuf := 'Deal lines exists for this price plan';
54 fnd_file.put_line(fnd_file.log, 'Deal lines exists for this price plan.' ||
55 'Delete deals before deleting priceplan');
56 return;
57 exception
58 when no_data_found then
59 null;
60 end;
61
62 begin
63 select 1 into l_rownum
64 from qpr_usr_price_plans
65 where aw_type_code = 'DATAMART'
66 and price_plan_id = p_price_plan_id
67 and default_assg_flag = 'Y'
68 and rownum < 2;
69
70 retcode := 1;
71 errbuf := 'Price plan present as default priceplan for user(s).';
72 fnd_file.put_line(fnd_file.log,
73 'Price plan present as default priceplan for user(s).' ||
74 'Assign a new priceplan as default before deleting priceplan.');
75 return;
76 exception
77 when no_data_found then
78 null;
79 end;
80
81 l_stmtnum := 10;
82
83 if (aw_exists(l_aw_name1)) then
84 fnd_file.put_line(fnd_file.log,'the analytic workspace '||l_aw_name1||
85 'exists in schema apps '||to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
86
87 dbms_aw.aw_detach('APPS',l_aw_name1);
88
89 l_stmtnum := 60;
90
91 dbms_aw.aw_delete('APPS',l_aw_name1);
92
93 fnd_file.put_line(fnd_file.log,'deleted analytic workspace '||l_aw_name1||
94 ' '||to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
95
96 l_stmtnum := 70;
97
98 update qpr_price_plans_b
99 set aw_created_flag = 'N'
100 where price_plan_id = p_price_plan_id;
101
102 commit;
103
104 fnd_file.put_line(fnd_file.log,'updated qpr_price_plans table '||
105 to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
106
107 if(p_delete_qpr_tables = 'N') then
108 l_stmtnum := 350;
109 l_table_name := 'CHANGED STATUS FOR REPORTS';
110
111 qpr_user_plan_init_pvt.Initialize
112 ( p_api_version =>1.0,
113 p_init_msg_list =>FND_API.G_TRUE,
114 p_commit =>FND_API.G_FALSE,
115 p_validation_level=>FND_API.G_VALID_LEVEL_NONE,
116 p_user_id =>null,
117 p_plan_id =>P_PRICE_PLAN_ID,
118 p_event_id =>qpr_user_plan_init_pvt.g_maintain_datamart,
119 x_return_status =>L_OUTPUT2,
120 x_msg_count =>l_msg_count,
121 x_msg_data =>l_msg_data
122 );
123
124 if(l_output2 = l_success) then
125 fnd_file.put_line(fnd_file.log,
126 'Changed status for reports for analytic workspace '||l_aw_name1);
127 else
128 retcode := 1;
129 errbuf := sqlerrm;
130 fnd_file.put_line(fnd_file.log, 'Unable to reset user report status');
131 return;
132 end if;
133 end if;
134 else
135 fnd_file.put_line(fnd_file.log,'aw does not exists!! '||
136 to_char(sysdate, 'hh24:mi:ss')||' '||L_STMTNUM);
137 end if;
138
139
140 if(p_delete_qpr_tables = 'Y') then
141 l_stmtnum := 100;
142 l_table_name := 'REPORTS';
143
144 qpr_report_entities_pvt.delete_reports(
145 p_price_plan_id => p_price_plan_id,
146 x_return_status => l_output2);
147 l_stmtnum := 110;
148
149 if(l_output2 = l_success) then
150 fnd_file.put_line(fnd_file.log,'Deleted reports for analytic workspace '||
151 l_aw_name1||' '||to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
152
153 l_stmtnum := 120;
154
155 l_table_name := 'DASHBOARDS';
156
157 qpr_dashboard_util.delete_dashboards(
158 p_price_plan_id => p_price_plan_id,
159 x_return_status => l_output3);
160 l_stmtnum := 130;
161
162 if(l_output3 = l_success) then
163 fnd_file.put_line(fnd_file.log,'deleted dashboards for analytic workspace'
164 ||l_aw_name1||' '||to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
165
166 l_stmtnum := 140;
167
168 l_table_name := 'ASSIGNMENTS';
169
170 delete from qpr_usr_price_plans where price_plan_id = p_price_plan_id;
171
172 fnd_file.put_line(fnd_file.log,'deleted assignment for analytic workspace'
173 ||l_aw_name1||' '||to_char(sysdate, 'hh24:mi:ss')||' '||l_stmtnum);
174
175 else
176 raise l_unable_to_delete;
177 end if;
178 else
179 raise l_unable_to_delete;
180 end if;
181
182 l_table_name := 'PRICEPLANS';
183
184 l_stmtnum := 170;
185 delete from qpr_price_plans_b where price_plan_id = p_price_plan_id;
186 delete from qpr_price_plans_tl where price_plan_id = p_price_plan_id;
187 l_stmtnum := 180;
188 delete from qpr_dimensions where price_plan_id = p_price_plan_id;
189 l_stmtnum := 190;
190 delete from qpr_hierarchies where price_plan_id = p_price_plan_id;
191 l_stmtnum := 200;
192 delete from qpr_hier_levels where price_plan_id = p_price_plan_id;
193 l_stmtnum := 210;
194 delete from qpr_dim_attributes where price_plan_id = p_price_plan_id;
195 l_stmtnum := 220;
196 delete from qpr_lvl_attributes where price_plan_id = p_price_plan_id;
197 l_stmtnum := 230;
198 delete from qpr_cubes where price_plan_id = p_price_plan_id;
199 l_stmtnum := 250;
200 delete from qpr_cube_dims where price_plan_id = p_price_plan_id;
201 l_stmtnum := 260;
202 delete from qpr_measures where price_plan_id = p_price_plan_id;
203 l_stmtnum := 270;
204 delete from qpr_meas_aggrs where price_plan_id = p_price_plan_id;
205 l_stmtnum := 280;
206 delete from qpr_set_levels where price_plan_id = p_price_plan_id;
207 l_stmtnum := 290;
208 delete from qpr_scopes where parent_entity_type = 'DATAMART'
209 and parent_id = p_price_plan_id;
210 l_stmtnum := 300;
211
212 fnd_file.put_line(fnd_file.log,'deleted from qp tables for price_plan_id:'
213 ||p_price_plan_id||' '||to_char(sysdate, 'hh24:mi:ss'));
214
215 elsif(p_delete_qpr_tables = 'N') then
216 fnd_file.put_line(fnd_file.log,
217 'Price plan tables not deleted as value of qp delete tables = "N" '||
218 ' '||to_char(sysdate, 'hh24:mi:ss'));
219 else
220 l_stmtnum := 320;
221 raise l_unable_to_delete;
222 end if;
223
224 exception
225 when l_unable_to_delete then
226 retcode := 2;
227 errbuf := sqlerrm;
228 fnd_file.put_line(fnd_file.log,
229 'Error deleting values for price_plan_id '||p_price_plan_id||
230 ' from the tables'||' '||l_table_name||' '||to_char(sysdate, 'hh24:mi:ss')||
231 ' '||'-');
232 when OTHERS then
233 retcode := 2;
234 errbuf := sqlerrm;
235 fnd_file.put_line(fnd_file.log,'Error deleting price plan');
236 fnd_file.put_line(fnd_file.log, dbms_utility.format_error_backtrace);
237
238 END DELETE_AW;
239
240
241
242 FUNCTION AW_EXISTS(
243 P_AW_NAME1 VARCHAR2 )
244
245 RETURN BOOLEAN
246 IS
247
248 L_AW_DOESNOT_EXISTS2 EXCEPTION;
249 L_RES BOOLEAN;
250 PRAGMA EXCEPTION_INIT (L_AW_DOESNOT_EXISTS2,-33262);
251
252 BEGIN
253
254 DBMS_AW.AW_ATTACH('APPS',P_AW_NAME1,true);
255 L_RES := TRUE;
256 RETURN L_RES;
257
258 EXCEPTION
259
260 WHEN L_AW_DOESNOT_EXISTS2 THEN
261
262 L_RES := FALSE;
263 RETURN L_RES;
264
265 END AW_EXISTS;
266
267 END QPR_DELETE_AW;