[Home] [Help]
PACKAGE BODY: APPS.QPR_DASHBOARD_UTIL
Source
1 PACKAGE BODY QPR_DASHBOARD_UTIL AS
2 /* $Header: QPRPDSBB.pls 120.0 2007/10/11 13:11:39 agbennet noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QPR_DASHBOARD_UTIL';
4
5
6
7 PROCEDURE Create_Dashboard_Default
8 ( p_user_id IN NUMBER
9 ,p_plan_id IN NUMBER
10
11 ,x_return_status OUT NOCOPY VARCHAR2
12 )
13 IS
14
15 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dashboard_Util';
16 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 l_dashboard_type VARCHAR2(1);
18 l_source_template_id NUMBER;
19 l_dashboard_id NUMBER;
20 n_dashboard_id NUMBER;
21 l_source_lang VARCHAR2(4);
22 l_dashboard_name VARCHAR2(50);
23 l_dsb_table DashboardDetailsTab ;
24 l_dummy_counter NUMBER;
25
26 TEMPLATE_NOT_FOUND EXCEPTION;
27
28 CURSOR c_dashboard is
29 SELECT dashboard_id,source_template_id
30 FROM QPR_DASHBOARD_MASTER_B
31 WHERE user_id is null
32 and plan_id is null
33 and default_flag = 'Y'
34 and dashboard_type = 'T';
35
36 BEGIN
37
38 SAVEPOINT Create_Dashboard_Default_P;
39 BEGIN
40 SELECT dashboard_type
41 INTO l_dashboard_type
42 FROM QPR_DASHBOARD_MASTER_B
43 WHERE user_id = p_user_id
44 and plan_id = p_plan_id
45 and default_flag = 'Y';
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 l_dummy_counter := 1;
49 END;
50
51 IF SQL%FOUND THEN
52 IF l_dashboard_type = 'I' THEN
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54 --ask shanmu whether error needs to be raised here
55 END IF;
56 ELSE
57 OPEN c_dashboard;
58 LOOP
59 FETCH c_dashboard INTO l_dashboard_id,l_source_template_id;
60
61 EXIT when c_dashboard%ROWCOUNT = 1;
62 IF c_dashboard%NOTFOUND is null THEN
63 raise TEMPLATE_NOT_FOUND;
64 END IF;
65 END LOOP;
66
67
68 SELECT QPR_DASHBOARD_MASTER_S.NEXTVAL into n_dashboard_id FROM DUAL;
69 Populate_Dashboard_Details(p_user_id,p_plan_id,l_dashboard_id,n_dashboard_id,l_dsb_table,l_return_status);
70
71
72 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
73 --generate proper error log ask shanmu
74
75 SELECT dashboard_name,source_lang
76 INTO l_dashboard_name,l_source_lang
77 FROM QPR_DASHBOARD_MASTER_TL
78 WHERE dashboard_id = l_dashboard_id
79 and language = (select USERENV('Lang') from dual);
80
81
82 UPDATE QPR_DASHBOARD_MASTER_B
83 SET dashboard_id = n_dashboard_id
84 ,user_id = p_user_id
85 ,plan_id = p_plan_id
86 ,dashboard_type = 'I'
87 WHERE dashboard_id = l_dashboard_id;
88
89 UPDATE QPR_DASHBOARD_MASTER_TL
90 SET dashboard_id = n_dashboard_id
91 WHERE dashboard_id = l_dashboard_id;
92
93 Generate_Default_Rows(l_dashboard_name,l_source_template_id,l_dashboard_id,l_source_lang,l_dsb_table,l_return_status);
94 END IF;
95 END IF;
96
97 EXCEPTION
98 WHEN TEMPLATE_NOT_FOUND THEN
99 --populate a msg saying no default template exists in table
100 ROLLBACK to Create_Dashboard_Default_P;
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 FND_MESSAGE.SET_NAME('QPR','QPR_API_INVALID_INPUT');
103 FND_MESSAGE.SET_TOKEN('ERROR_TEXT','Default Dashboard template Not found');
104 --FND_MESSAGE_PUB.add;
105 raise FND_API.G_EXC_ERROR;
106
107 WHEN OTHERS THEN
108 ROLLBACK to Create_Dashboard_Default_P;
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
111 FND_MSG_PUB.Add_Exc_Msg
112 ( G_PKG_NAME,
113 l_api_name
114 );
115 END IF;
116
117 raise FND_API.G_EXC_UNEXPECTED_ERROR;
118
119 END Create_Dashboard_Default;
120
121
122 -- Procedure POPULATE_DASHBOARD_DETAILS
123
124 PROCEDURE Populate_Dashboard_Details
125 ( p_user_id IN NUMBER
126 ,p_plan_id IN NUMBER
127 ,p_dashboard_id IN NUMBER
128 ,n_dashboard_id IN NUMBER
129 ,p_dsb_table OUT NOCOPY DashboardDetailsTab
130 ,x_return_status OUT NOCOPY VARCHAR2
131 )
132 IS
133
134 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dashboard_Util';
135 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
136 l_dashboard_detail_id NUMBER;
137 n_dashboard_detail_id NUMBER;
138 l_row_number NUMBER;
139 l_col_number NUMBER;
140 l_content_id NUMBER;
141 l_report_type_header_id NUMBER;
142 l_report_header_id NUMBER;
143 l_counter INTEGER := 0;
144
145
146 CURSOR c_details (p_dashboard_id IN NUMBER ) IS
147 SELECT
148 dashboard_detail_id,row_number,column_number,content_id,width
149 FROM QPR_DASHBOARD_DETAILS
150 WHERE dashboard_id = p_dashboard_id;
151
152 BEGIN
153
154 SAVEPOINT Populate_Dashboard_Details_P;
155 FOR details_rec in c_details(p_dashboard_id)
156 LOOP
157 l_dashboard_detail_id := details_rec.dashboard_detail_id;
158 l_content_id := details_rec.content_id;
159 SELECT report_header_id
160 INTO l_report_header_id
161 FROM QPR_REPORT_HDRS_B
162 WHERE report_type_header_id = l_content_id
163 and user_id = p_user_id
164 and plan_id = p_plan_id
165 and Seeded_Report_Flag = 'Y';
166 SELECT QPR_DASHBOARD_DETAILS_S.NEXTVAL INTO n_dashboard_detail_id FROM DUAL;
167
168 UPDATE QPR_DASHBOARD_DETAILS
169 SET DASHBOARD_ID = n_dashboard_id,
170 DASHBOARD_DETAIL_ID = n_dashboard_detail_id,
171 CONTENT_ID = l_report_header_id
172 WHERE DASHBOARD_DETAIL_ID = l_dashboard_detail_id;
173
174 p_dsb_table(l_counter).dashboard_detail_id := details_rec.dashboard_detail_id;
175 p_dsb_table(l_counter).row_number := details_rec.row_number;
176 p_dsb_table(l_counter).column_number := details_rec.column_number;
177 p_dsb_table(l_counter).content_id := details_rec.content_id;
178 p_dsb_table(l_counter).width := details_rec.width;
179 l_counter := l_counter + 1;
180
181 END LOOP;
182 x_return_status := FND_API.G_RET_STS_SUCCESS;
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 ROLLBACK TO Populate_Dashboard_Details_P;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
189 FND_MESSAGE.Set_Token ('ERROR_TEXT','No Rows for Content Id in Reports table');
190 FND_MSG_PUB.Add;
191 raise FND_API.G_EXC_ERROR;
192
193 WHEN TOO_MANY_ROWS THEN
194 ROLLBACK TO Populate_Dashboard_Details_P;
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
197 FND_MESSAGE.Set_Token ('ERROR_TEXT','Too many Rows for Content Id in Reports table');
198 FND_MSG_PUB.Add;
199 WHEN OTHERS THEN
200 ROLLBACK TO Populate_Dashboard_Details_P;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
203 THEN
204 FND_MSG_PUB.Add_Exc_Msg
205 ( G_PKG_NAME,
206 l_api_name
207 );
208 END IF;
209
210 raise FND_API.G_EXC_UNEXPECTED_ERROR;
211
212 END Populate_Dashboard_Details;
213
214
215 -- Procedure Generate_default_Rows
216
217 PROCEDURE Generate_Default_Rows
218 ( p_dashboard_name IN VARCHAR2
219 ,p_source_template_id IN NUMBER
220 ,p_dashboard_id IN NUMBER
221 ,p_source_lang IN VARCHAR2
222 ,p_dsb_table IN DashboardDetailsTab
223 ,x_return_status OUT NOCOPY VARCHAR2
224 )
225
226 IS
227
228 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dashboard_Util';
229 l_dashboard_id NUMBER;
230 l_dashboard_detail_id NUMBER;
231
232 BEGIN
233
234 SAVEPOINT Generate_default_Rows_V;
235 INSERT INTO QPR_DASHBOARD_MASTER_TL
236 ( DASHBOARD_ID
237 , DASHBOARD_NAME
238 , LANGUAGE
239 , SOURCE_LANG
240 , CREATION_DATE
241 , CREATED_BY
242 , LAST_UPDATE_DATE
243 , LAST_UPDATED_BY
244 , LAST_UPDATE_LOGIN)
245 select
246 p_dashboard_id
247 , p_dashboard_name
248 , l.language_code
249 , b.language_code
250 , sysdate
251 , fnd_global.user_id()
252 , sysdate
253 , fnd_global.user_id()
254 , 0
255 FROM fnd_languages l,fnd_languages b
256 where l.installed_flag in ('I','B')
257 and b.installed_flag = 'B'
258 and not exists
259 (select null
260 from qpr_dashboard_master_tl t
261 where t.dashboard_id = p_dashboard_id
262 and t.language = l.language_code);
263
264 INSERT INTO QPR_DASHBOARD_MASTER_B
265 ( SOURCE_TEMPLATE_ID
266 , DASHBOARD_ID
267 , DASHBOARD_TYPE
268 , DEFAULT_FLAG
269 , CREATION_DATE
270 , CREATED_BY
271 , LAST_UPDATE_DATE
272 , LAST_UPDATED_BY
273 , LAST_UPDATE_LOGIN
274 )
275 VALUES
276 ( p_source_template_id
277 , p_dashboard_id
278 , 'T'
279 , 'Y'
280 , sysdate
281 , fnd_global.user_id
282 , sysdate
283 , fnd_global.user_id
284 , fnd_global.login_id
285 );
286
287 FOR i in 0..(p_dsb_table.count - 1)
288 LOOP
289 SELECT QPR_DASHBOARD_DETAILS_S.NEXTVAL INTO l_dashboard_detail_id FROM
290 DUAL;
291 INSERT INTO QPR_DASHBOARD_DETAILS
292 ( DASHBOARD_DETAIL_ID
293 , DASHBOARD_ID
294 , ROW_NUMBER
295 , COLUMN_NUMBER
296 , CONTENT_ID
297 , WIDTH
298 , CREATION_DATE
299 , CREATED_BY
300 , LAST_UPDATE_DATE
301 , LAST_UPDATED_BY
302 , LAST_UPDATE_LOGIN
303 )
304 VALUES
305 ( p_dsb_table(i).dashboard_detail_id
306 , p_dashboard_id
307 , p_dsb_table(i).row_number
308 , p_dsb_table(i).column_number
309 , p_dsb_table(i).content_id
310 , p_dsb_table(i).width
311 , sysdate
312 , fnd_global.user_id
313 , sysdate
314 , fnd_global.user_id
315 , fnd_global.login_id
316 );
317
318 END LOOP;
319
320 END Generate_Default_Rows;
321
322
323 --========================================================================
324 -- PROCEDURE : DELETE_DASHBOARDS
325 --
326 -- PARAMETERS:
327 -- p_price_plan_id Price plan ID for which reports needs
328 -- to be deleted
329 -- x_return_status Return status
330 --
331 -- COMMENT : This procedure deletes all the dashboard masters and
332 -- corresponding records from dashboard details tables
333 -- for a given price plan id
334 --========================================================================
335
336 PROCEDURE DELETE_DASHBOARDS(
337 p_price_plan_id IN NUMBER,
338 x_return_status OUT NOCOPY VARCHAR2)
339 IS
340 l_detail_id NUMBER;
341 CURSOR Get_Dashboard_Details_C(c_price_plan_id NUMBER)
342 IS
343 SELECT DT.DASHBOARD_DETAIL_ID
344 FROM QPR_DASHBOARD_DETAILS DT
345 ,QPR_DASHBOARD_MASTER_B DMB
346 WHERE DMB.DASHBOARD_ID = DT.DASHBOARD_ID
347 AND DMB.PLAN_ID = c_price_plan_id;
348
349
350 BEGIN
351
352 OPEN Get_Dashboard_Details_C(p_price_plan_id);
353 LOOP
354 FETCH Get_Dashboard_Details_C INTO l_detail_id;
355 IF Get_Dashboard_Details_C%NOTFOUND
356 THEN
357 EXIT;
358 END IF;
359
360 DELETE FROM QPR_DASHBOARD_DETAILS
361 WHERE DASHBOARD_DETAIL_ID = l_detail_id;
362 END LOOP;
363 CLOSE Get_Dashboard_Details_C;
364
365 DELETE FROM QPR_DASHBOARD_MASTER_TL
366 WHERE DASHBOARD_ID IN
367 (SELECT DASHBOARD_ID
368 FROM QPR_DASHBOARD_MASTER_B
369 WHERE PLAN_ID = p_price_plan_id);
370
371 DELETE FROM QPR_DASHBOARD_MASTER_B
372 WHERE PLAN_ID = p_price_plan_id;
373
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375 EXCEPTION
376 WHEN OTHERS
377 THEN
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END DELETE_DASHBOARDS;
380
381
382 END QPR_DASHBOARD_UTIL;