[Home] [Help]
PACKAGE BODY: APPS.QPR_USER_PLAN_INIT_PVT
Source
1 PACKAGE BODY QPR_USER_PLAN_INIT_PVT AS
2 /* $Header: QPRPUSRB.pls 120.0 2007/10/11 13:12:14 agbennet noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='QPR_USER_PLAN_INIT_PVT';
5
6 PROCEDURE Initialize
7 ( p_api_version IN NUMBER ,
8 p_init_msg_list IN VARCHAR2,
9 p_commit IN VARCHAR2,
10 p_validation_level IN NUMBER ,
11 p_user_id IN NUMBER ,
12 p_plan_id IN NUMBER ,
13 p_event_id IN NUMBER ,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER ,
16 x_msg_data OUT NOCOPY VARCHAR2
17 ) IS
18 l_api_name CONSTANT VARCHAR2(30) := 'Initialize';
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_valid_inputs VARCHAR2(10);
21 l_return_status VARCHAR2(10);
22 BEGIN
23 -- Standard Start of API savepoint
24 SAVEPOINT QPR_USER_PLAN_INITIALIZE;
25 -- Standard call to check for call compatibility.
26 IF NOT FND_API.Compatible_API_Call (l_api_version ,
27 p_api_version ,
28 l_api_name ,
29 G_PKG_NAME )
30 THEN
31 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
32 END IF;
33
34 -- Initialize message list if p_init_msg_list is set to TRUE.
35 IF FND_API.to_Boolean( p_init_msg_list ) THEN
36 FND_MSG_PUB.initialize;
37 END IF;
38 -- Initialize API return status to success
39
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 /* do cursory validation */
43 Validate_params (p_event_id => p_event_id,
44 p_user_id => p_user_id ,
45 p_plan_id => p_plan_id ,
46 x_return_status => l_valid_inputs);
47 if (l_valid_inputs <> FND_API.G_RET_STS_SUCCESS) then
48 x_return_status := FND_API.G_RET_STS_ERROR;
49 raise exc_severe_error;
50 end if;
51
52 if (p_event_id = G_INITIALIZE_REPORTS) then
53 Initialize_reports (p_user_id, p_plan_id, l_return_status);
54 qpr_dashboard_util.create_dashboard_default(p_user_id,p_plan_id,l_return_status);
55 elsif (p_event_id = G_MAINTAIN_DATAMART) THEN
56 /*if p_event_id = QPR_USER_PLAN_INIT.DATAMART_RELOAD*/
57 Reset_report_flags (p_user_id, p_plan_id, l_return_status);
58 end if;
59
60
61 -- Standard check of p_commit.
62 IF FND_API.To_Boolean( p_commit ) THEN
63 COMMIT WORK;
64 END IF;
65 -- Standard call to get message count and if count is 1, get message info.
66 FND_MSG_PUB.Count_And_Get
67 ( p_count => x_msg_count,
68 p_data => x_msg_data
69 );
70 EXCEPTION
71 WHEN FND_API.G_EXC_ERROR THEN
72 ROLLBACK TO QPR_USER_PLAN_INITIALIZE;
73 x_return_status := FND_API.G_RET_STS_ERROR ;
74 FND_MSG_PUB.Count_And_Get
75 ( p_count => x_msg_count,
76 p_data => x_msg_data
77 );
78 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
79 ROLLBACK TO QPR_USER_PLAN_INITIALIZE;
80 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
81 FND_MSG_PUB.Count_And_Get
82 ( p_count => x_msg_count,
83 p_data => x_msg_data
84 );
85 WHEN OTHERS THEN
86 ROLLBACK TO QPR_USER_PLAN_INITIALIZE;
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
88 IF FND_MSG_PUB.Check_Msg_Level
89 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
90 THEN
91 FND_MSG_PUB.Add_Exc_Msg
92 ( G_PKG_NAME,
93 l_api_name
94 );
95 END IF;
96 FND_MSG_PUB.Count_And_Get
97 ( p_count => x_msg_count,
98 p_data => x_msg_data
99 );
100 END Initialize;
101
102 Procedure Validate_params
103 (
104 p_event_id IN NUMBER,
105 p_user_id IN NUMBER,
106 p_plan_id IN NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2
108 ) IS
109 Cursor c_valid_user (c_p_user_id number) is
110 select user_id
111 from fnd_user
112 where user_id=c_p_user_id;
113
114 Cursor c_valid_plan (c_p_plan_id number) is
115 select price_plan_id
116 from qpr_price_plans_b
117 where price_plan_id = c_p_plan_id;
118
119 l_user_id NUMBER;
120 l_plan_id NUMBER;
121 l_check_user_id BOOLEAN;
122 l_check_plan_id BOOLEAN;
123 BEGIN
124 open c_valid_user (p_user_id);
125 fetch c_valid_user into l_user_id;
126 if (c_valid_user%NOTFOUND) then
127 l_user_id := NULL;
128 end if;
129 close c_valid_user;
130
131 open c_valid_plan (p_plan_id);
132 fetch c_valid_plan into l_plan_id;
133 if (c_valid_plan%NOTFOUND) then
134 l_plan_id := NULL;
135 end if;
136 close c_valid_plan;
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 if (p_event_id = G_INITIALIZE_REPORTS) then--{
140 /*user_id and plan_id should be not null and valid*/
141 l_check_user_id := TRUE;
142 l_check_plan_id := TRUE;--}
143 elsif (p_event_id = G_MAINTAIN_DATAMART) then--{
144 /* plan_id should be not null and valid*/
145 l_check_user_id := FALSE;
146 l_check_plan_id := TRUE;--}
147 else--{
148 x_return_status := FND_API.G_RET_STS_ERROR ;
149 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
150 FND_MESSAGE.Set_Token ('ERROR_TEXT','p_event_id Invalid');
151 FND_MSG_PUB.Add;
152 raise FND_API.G_EXC_ERROR;--}
153 end if;
154
155
156 if (l_check_user_id) then --{
157 if (p_user_id is null) then --{
158 x_return_status := FND_API.G_RET_STS_ERROR ;
159 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
160 FND_MESSAGE.Set_Token ('ERROR_TEXT','p_user_id is NULL');
161 FND_MSG_PUB.Add;
162 raise FND_API.G_EXC_ERROR;--}
163 elsif
164 (l_user_id is null) then --{
165 x_return_status := FND_API.G_RET_STS_ERROR ;
166 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
167 FND_MESSAGE.Set_Token ('ERROR_TEXT','p_user_id Invalid');
168 FND_MSG_PUB.Add;
169 raise FND_API.G_EXC_ERROR;--}
170 end if;--}
171 end if;
172
173 if (l_check_plan_id) then--{
174 if (p_plan_id is null) then--{
175 x_return_status := FND_API.G_RET_STS_ERROR ;
176 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
177 FND_MESSAGE.Set_Token ('ERROR_TEXT','p_plan_id is NULL');
178 FND_MSG_PUB.Add;
179 raise FND_API.G_EXC_ERROR;--}
180 elsif
181 (l_plan_id is null) then--{
182 x_return_status := FND_API.G_RET_STS_ERROR ;
183 FND_MESSAGE.Set_Name ('QPR','QPR_API_INVALID_INPUT');
184 FND_MESSAGE.Set_Token ('ERROR_TEXT','p_plan_id Invalid');
185 FND_MSG_PUB.Add;
186 raise FND_API.G_EXC_ERROR;--}
187 end if;--}
188 end if;
189 END Validate_params;
190
191 Procedure Initialize_reports
192 (
193 p_user_id IN NUMBER,
194 p_plan_id IN NUMBER,
195 x_return_status OUT NOCOPY VARCHAR2
196 ) is
197 BEGIN
198 /*
199 * fetch all data from QPR_REPORT_TYPE_HDRS_B
200 * for user_id=null and plan_id=null
201 */
202 /*
203 * Check for a report entity for that user_id and plan_id and
204 * report_header_id in QPR_REPORT_HDRS_B
205 *Case 1: If one/more record is found,
206 * then update that record's valid_flag='R'
207 *
208 *Case 2: If no record is found, then create a new report header
209 * record with seeded_report_flag='Y' and report_valid_flag='R'
210 * and insert into QPR_REPORT_HDRS_B/TL tables
211 */
212
213
214 insert into QPR_REPORT_HDRS_B
215 (
216 REPORT_HEADER_ID
217 ,REPORT_TYPE_HEADER_ID
218 ,USER_ID
219 ,PLAN_ID
220 ,SEEDED_REPORT_FLAG
221 ,REPORT_VALID_FLAG
222 ,ENABLED_OPTIONS
223 ,CREATION_DATE
224 ,CREATED_BY
225 ,LAST_UPDATE_DATE
226 ,LAST_UPDATED_BY
227 ,LAST_UPDATE_LOGIN
228 ,PROGRAM_ID
229 ,PROGRAM_LOGIN_ID
230 ,PROGRAM_APPLICATION_ID
231 ,REQUEST_ID
232 )
233 select
234 qpr_report_hdrs_s.nextval
235 ,report_type_header_id
236 ,p_user_id
237 ,p_plan_id
238 ,G_YES
239 ,G_REPORT_REFRESH_FLAG
240 ,null
241 ,sysdate
242 ,FND_GLOBAL.user_id
243 ,sysdate
244 ,FND_GLOBAL.user_id
245 ,null
246 ,null
247 ,null
248 ,null
249 ,null
250 from QPR_REPORT_TYPE_HDRS_B rth
251 where
252 rth.user_id is null
253 and rth.plan_id is null
254 and not exists
255 ( select 1
256 from QPR_REPORT_HDRS_B rh
257 where rh.user_id = p_user_id
258 and rh.plan_id = p_plan_id
259 and rh.report_type_header_id = rth.report_type_header_id );
260
261 insert into qpr_report_hdrs_tl
262 (
263 REPORT_HEADER_ID
264 ,REPORT_NAME
265 ,REPORT_TITLE
266 ,LANGUAGE
267 ,SOURCE_LANG
268 ,CREATION_DATE
269 ,CREATED_BY
270 ,LAST_UPDATE_DATE
271 ,LAST_UPDATED_BY
272 ,LAST_UPDATE_LOGIN
273 ,PROGRAM_ID
274 ,PROGRAM_APPLICATION_ID
275 ,PROGRAM_LOGIN_ID
276 ,REQUEST_ID
277 )
278 select
279 RHB.report_header_id
280 ,QL.meaning
281 ,RTH.report_type_name||'-'||QL.meaning
282 ,L.LANGUAGE_CODE
283 ,userenv('LANG')
284 ,sysdate
285 ,FND_GLOBAL.user_id
286 ,sysdate
287 ,FND_GLOBAL.user_id
288 ,null
289 ,null
290 ,null
291 ,null
292 ,null
293 from QPR_REPORT_HDRS_B RHB,
294 qpr_report_type_hdrs_tl RTH,
295 FND_LANGUAGES L,
296 QPR_LOOKUPS QL
297 where L.INSTALLED_FLAG in ('I', 'B')
298 and QL.Lookup_type = 'QPR_REPORT_TITLE_SUFFIX'
299 and QL.Lookup_code = 'DEFVW'
300 and RTH.language = l.language_code
301 and RHB.report_type_header_id = RTH.report_type_header_id
302 and not exists
303 (select null
304 from qpr_report_hdrs_tl RHT
305 where RHT.report_header_id = RHB.report_header_id
306 and RHT.LANGUAGE = L.LANGUAGE_CODE);
307
308
309 insert into qpr_report_lines
310 (
311 REPORT_LINE_ID
312 ,REPORT_HEADER_ID
313 ,REPORT_TYPE_LINE_ID
314 ,REPORT_LINE_NAME
315 ,FOLDER
316 ,REPORT_LINE_VALID_FLAG
317 ,DISPLAY_SEQUENCE
318 ,REPORT_SVG
319 ,ENABLED_OPTIONS
320 ,CREATION_DATE
321 ,CREATED_BY
322 ,LAST_UPDATE_DATE
323 ,LAST_UPDATED_BY
324 ,LAST_UPDATE_LOGIN
325 ,PROGRAM_ID
326 ,PROGRAM_LOGIN_ID
327 ,PROGRAM_APPLICATION_ID
328 ,REQUEST_ID
329 )
330 select
331 qpr_report_lines_s.nextval report_line_id
332 ,rhb.report_header_id
333 ,rtl.report_type_line_id
334 ,rtl.report_type_line_name
335 ,qpr_user_plan_init_pvt.g_folder_name
336 ,G_REPORT_REFRESH_FLAG
337 ,rta.report_display_sequence
338 ,null
339 ,null
340 ,sysdate
341 ,FND_GLOBAL.user_id
342 ,sysdate
343 ,FND_GLOBAL.user_id
344 ,null
345 ,null
346 ,null
347 ,null
348 ,null
349 from
350 QPR_REPORT_HDRS_B rhb,
351 QPR_REPORT_TYPE_ASGN rta,
352 qpr_report_type_lines rtl
353 where
354 rta.report_type_header_id = rhb.report_type_header_id
355 and rtl.report_type_line_id = rta.report_type_line_id
356 and rhb.report_header_id not in
357 (select report_header_id
358 from qpr_report_lines rl1
359 );
360
361 END Initialize_reports;
362
363 Procedure Reset_report_flags
364 (
365 p_user_id IN NUMBER,
366 p_plan_id IN NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2
368 ) IS
369 BEGIN
370 /*
371 * for the given plan_id, update all report entities
372 * with valid_flag='R'
373 */
374 if (p_user_id is not null) then --{
375 update QPR_REPORT_HDRS_B rh
376 set report_valid_flag = G_REPORT_REFRESH_FLAG
377 where rh.user_id = p_user_id
378 and rh.plan_id = p_plan_id;
379
380 update qpr_report_lines rl
381 set report_line_valid_flag = G_REPORT_REFRESH_FLAG
382 where rl.report_header_id in
383 (select report_header_id
384 from QPR_REPORT_TYPE_HDRS_B rth
385 where rth.user_id = p_user_id
386 and rth.plan_id = p_plan_id
387 );--}
388 else--{
389 update QPR_REPORT_HDRS_B rh
390 set report_valid_flag = G_REPORT_REFRESH_FLAG
391 where rh.plan_id = p_plan_id;
392
393 update qpr_report_lines rl
394 set report_line_valid_flag = G_REPORT_REFRESH_FLAG
395 where rl.report_header_id in
396 (select report_header_id
397 from QPR_REPORT_HDRS_B rh
398 where rh.plan_id = p_plan_id
399 );--}
400 end if;
401 END Reset_report_flags;
402
403 END QPR_USER_PLAN_INIT_PVT;
404