[Home] [Help]
PACKAGE BODY: APPS.PJI_PERF_RPTG_PUB
Source
1 package body PJI_PERF_RPTG_PUB as
2 /*$Header: PJIPRFPB.pls 120.0 2006/07/02 21:53:35 ajdas noship $*/
3
4 --Global constants to be used in error messages
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PJI_PERF_RPTG_PUB';
6 --PACKAGE GLOBAL to be used during updates -------------------------------------
7 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
8 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
9 g_debug_mode VARCHAR2(1):= NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10 --------------------------------------------------------------------------------
11 --Name: Create_resource_rollup
12 --Type: Procedure
13 --Description:This procedure can be used to create smart lines in
14 -- PJI_FP_XBS_ACCUM_F,PJI_ROLLUP_LEVEL_STATUS table based on the
15 -- RBS for list of Workplans/Financial Plans and Actual transaction.
16 --
17 --History:
18 -- 30-JUN-2006 DEGUPTA Created
19 --
20 --------------------------------------------------------------------------------
21
22 PROCEDURE Create_resource_rollup
23 ( p_api_version_number IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
24 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
25 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
26 ,x_msg_count OUT NOCOPY NUMBER
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ,x_return_status OUT NOCOPY VARCHAR2
29 ,p_project_id IN NUMBER
30 ,p_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
31 ,p_rbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
32 ,p_prg_rollup_flag IN VARCHAR2 :='N'
33 )
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Create_resource_rollup';
36 l_return_status VARCHAR2(1);
37 l_err_code NUMBER(15);
38 l_err_stage VARCHAR2(2000);
39 l_err_stack VARCHAR2(2000);
40 i NUMBER := 0; --counter
41 j NUMBER := 0; --counter
42 l_msg_count NUMBER ;
43 l_msg_data VARCHAR2(2000);
44 l_wbs_version_id NUMBER;
45 BEGIN
46
47 -- Standard begin of API savepoint
48
49 SAVEPOINT Create_resource_pub;
50
51
52 -- Standard call to check for call compatibility.
53
54
55 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
56 p_api_version_number ,
57 l_api_name ,
58 G_PKG_NAME )
59 THEN
60
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62
63 END IF;
64
65
66 -- Initialize the message table if requested.
67
68 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
69
70 FND_MSG_PUB.initialize;
71
72 END IF;
73
74 -- Set API return status to success
75
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 -- CHECK FOR MANDATORY FIELDS
79
80 IF P_PROJECT_ID IS NULL THEN
81
82 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
83 THEN
84 pa_interface_utils_pub.map_new_amg_msg
85 ( p_old_message_code => 'PA_PROJECT_ID_MISSING'
86 ,p_msg_attribute => 'CHANGE'
87 ,p_resize_flag => 'N'
88 ,p_msg_context => 'GENERAL'
89 ,p_attribute1 => ''
90 ,p_attribute2 => ''
91 ,p_attribute3 => ''
92 ,p_attribute4 => ''
93 ,p_attribute5 => '');
94 END IF;
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98
99 IF P_PLAN_VERSION_ID_TBL.COUNT = 0 THEN
100
101 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
102 THEN
103 pa_interface_utils_pub.map_new_amg_msg
104 ( p_old_message_code => 'PA_PLAN_VERSION_ID_MISSING'
105 ,p_msg_attribute => 'CHANGE'
106 ,p_resize_flag => 'N'
107 ,p_msg_context => 'GENERAL'
108 ,p_attribute1 => ''
109 ,p_attribute2 => ''
110 ,p_attribute3 => ''
111 ,p_attribute4 => ''
112 ,p_attribute5 => '');
113 END IF;
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 IF P_RBS_VERSION_ID_TBL.COUNT = 0 THEN
119
120 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
121 THEN
122 pa_interface_utils_pub.map_new_amg_msg
123 ( p_old_message_code => 'PA_RESOURCE_IS_MISSING'
124 ,p_msg_attribute => 'CHANGE'
125 ,p_resize_flag => 'N'
126 ,p_msg_context => 'GENERAL'
127 ,p_attribute1 => ''
128 ,p_attribute2 => ''
129 ,p_attribute3 => ''
130 ,p_attribute4 => ''
131 ,p_attribute5 => '');
132 END IF;
133 x_return_status := FND_API.G_RET_STS_ERROR;
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137 begin
138 SELECT distinct WBS_VERSION_ID
139 into l_wbs_version_id
140 FROM PJI_PJP_WBS_HEADER
141 WHERE PROJECT_ID = p_project_id
142 AND PLAN_VERSION_ID = p_plan_version_id_tbl(1);
143 exception
144 when no_data_found then
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 RAISE FND_API.G_EXC_ERROR;
147 when too_many_rows then
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 RAISE FND_API.G_EXC_ERROR;
150 end;
151 Pji_Fm_Xbs_Accum_Maint.maintain_smart_slice (
152 p_rbs_version_id_tbl =>p_rbs_version_id_tbl,
153 p_plan_version_id_tbl =>p_plan_version_id_tbl,
154 p_wbs_element_id =>NULL,
155 p_rbs_element_id => NULL,
156 p_prg_rollup_flag =>p_prg_rollup_flag,
157 p_curr_record_type_id => NULL,
158 p_calendar_type => NULL,
159 p_wbs_version_id =>l_wbs_version_id,
160 p_commit =>p_commit ,
161 x_msg_count =>x_msg_count,
162 x_msg_data =>x_msg_data,
163 x_return_status =>l_return_status);
164
165
166 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171
172 IF (FND_API.to_boolean( p_commit )) THEN
173 COMMIT ;
174 else
175 ROLLBACK TO Create_resource_pub;
176 END IF;
177
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 ROLLBACK TO create_resource_pub;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183
184 FND_MSG_PUB.Count_And_Get
185 ( p_count => x_msg_count ,
186 p_data => x_msg_data );
187
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 ROLLBACK TO create_resource_pub;
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191
192 FND_MSG_PUB.Count_And_Get
193 ( p_count => x_msg_count ,
194 p_data => x_msg_data );
195
196 WHEN OTHERS THEN
197 ROLLBACK TO create_resource_pub;
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199
200 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
201 THEN
202 FND_MSG_PUB.add_exc_msg
203 ( p_pkg_name => G_PKG_NAME
204 , p_procedure_name => l_api_name );
205
206 END IF;
207
208 FND_MSG_PUB.Count_And_Get
209 ( p_count => x_msg_count ,
210 p_data => x_msg_data );
211
212 END Create_resource_rollup;
213
214
215
216
217 PROCEDURE Delete_resource_rollup
218 ( p_api_version_number IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
219 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
220 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
221 ,x_msg_count OUT NOCOPY NUMBER
222 ,x_msg_data OUT NOCOPY VARCHAR2
223 ,x_return_status OUT NOCOPY VARCHAR2
224 ,p_project_id IN NUMBER
225 ,p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
226 ,p_rbs_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
227 ) IS
228 l_api_name CONSTANT VARCHAR2(30) := 'Delete_resource_rollup';
229 l_return_status VARCHAR2(1);
230 l_err_code NUMBER(15);
231 l_err_stage VARCHAR2(2000);
232 l_err_stack VARCHAR2(2000);
233 i NUMBER := 0; --counter
234 j NUMBER := 0; --counter
235 l_msg_count NUMBER ;
236 l_msg_data VARCHAR2(2000);
237
238 begin
239 -- Standard begin of API savepoint
240
241 SAVEPOINT Delete_resource_pub;
242
243
244 -- Standard call to check for call compatibility.
245
246
247 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
248 p_api_version_number ,
249 l_api_name ,
250 G_PKG_NAME )
251 THEN
252
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254
255 END IF;
256
257
258 -- Initialize the message table if requested.
259
260 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
261
262 FND_MSG_PUB.initialize;
263
264 END IF;
265
266 -- Set API return status to success
267
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270 -- CHECK FOR MANDATORY FIELDS
271
272 IF P_PROJECT_ID IS NULL THEN
273
274 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
275 THEN
276 pa_interface_utils_pub.map_new_amg_msg
277 ( p_old_message_code => 'PA_PROJECT_ID_IS_MISSING'
278 ,p_msg_attribute => 'CHANGE'
279 ,p_resize_flag => 'N'
280 ,p_msg_context => 'GENERAL'
281 ,p_attribute1 => ''
282 ,p_attribute2 => ''
283 ,p_attribute3 => ''
284 ,p_attribute4 => ''
285 ,p_attribute5 => '');
286 END IF;
287 x_return_status := FND_API.G_RET_STS_ERROR;
288 RAISE FND_API.G_EXC_ERROR;
289 END IF;
290
291 IF p_plan_version_id_tbl.COUNT=0 and p_rbs_version_id_tbl.COUNT=0 then
292 DELETE FROM pji_rollup_level_status
293 WHERE project_id=p_project_id;
294
295
296 IF (SQL%ROWCOUNT > 0) THEN
297
298 DELETE FROM pji_fp_xbs_accum_f
299 WHERE project_id=p_project_id
300 AND (rbs_aggr_level = 'R'
301 OR (rbs_aggr_level = 'L'
302 AND wbs_rollup_flag = 'Y')) ;
303
304 END IF;
305 ELSIF p_plan_version_id_tbl.COUNT=0 and p_rbs_version_id_tbl.COUNT<>0 then
306
307 FORALL j IN 1..p_rbs_version_id_tbl.COUNT
308
309 DELETE FROM pji_rollup_level_status
310 WHERE project_id=p_project_id
311 and rbs_version_id=p_rbs_version_id_tbl(j);
312
313
314 IF (SQL%ROWCOUNT > 0) THEN
315 FORALL j IN 1..p_rbs_version_id_tbl.COUNT
316
317 DELETE FROM pji_fp_xbs_accum_f
318 WHERE project_id=p_project_id
319 and rbs_version_id=p_rbs_version_id_tbl(j)
320 AND (rbs_aggr_level = 'R'
321 OR (rbs_aggr_level = 'L'
322 AND wbs_rollup_flag = 'Y')) ;
323
324 END IF;
325
326 ELSIF p_plan_version_id_tbl.COUNT<>0 and p_rbs_version_id_tbl.COUNT=0 then
327
328 FORALL i IN 1..p_plan_version_id_tbl.COUNT
329
330 DELETE FROM pji_rollup_level_status
331 WHERE project_id=p_project_id
332 and plan_version_id=p_plan_version_id_tbl(i);
333
334
335 IF (SQL%ROWCOUNT > 0) THEN
336 FORALL i IN 1..p_plan_version_id_tbl.COUNT
337
338 DELETE FROM pji_fp_xbs_accum_f
339 WHERE project_id=p_project_id
340 and plan_version_id=p_plan_version_id_tbl(i)
341 AND (rbs_aggr_level = 'R'
342 OR (rbs_aggr_level = 'L'
343 AND wbs_rollup_flag = 'Y')) ;
344
345 END IF;
346
347 ELSIF p_plan_version_id_tbl.COUNT<>0 and p_rbs_version_id_tbl.COUNT<>0 then
348 FOR j IN 1..p_rbs_version_id_tbl.COUNT LOOP
349 FORALL i IN 1..p_plan_version_id_tbl.COUNT
350 DELETE FROM pji_rollup_level_status
351 WHERE project_id=p_project_id
352 and plan_version_id=p_plan_version_id_tbl(i)
353 and rbs_version_id=p_rbs_version_id_tbl(j);
354
355
356 IF (SQL%ROWCOUNT > 0) THEN
357 FORALL i IN 1..p_plan_version_id_tbl.COUNT
358
359 DELETE FROM pji_fp_xbs_accum_f
360 WHERE project_id=p_project_id
361 and plan_version_id=p_plan_version_id_tbl(i)
362 and rbs_version_id=p_rbs_version_id_tbl(j)
363 AND (rbs_aggr_level = 'R'
364 OR (rbs_aggr_level = 'L'
365 AND wbs_rollup_flag = 'Y')) ;
366
367 END IF;
368 END LOOP;
369 ELSE
370 NULL;
371 END IF;
372
373
374 IF (FND_API.to_boolean( p_commit )) THEN
375 COMMIT ;
376 else
377 ROLLBACK to Delete_resource_pub;
378 END IF;
379
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 ROLLBACK TO delete_resource_pub;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384
385 FND_MSG_PUB.Count_And_Get
386 ( p_count => x_msg_count ,
387 p_data => x_msg_data );
388
389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
390 ROLLBACK TO delete_resource_pub;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392
393 FND_MSG_PUB.Count_And_Get
394 ( p_count => x_msg_count ,
395 p_data => x_msg_data );
396
397 WHEN OTHERS THEN
398 ROLLBACK TO delete_resource_pub;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400
401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
402 THEN
403 FND_MSG_PUB.add_exc_msg
404 ( p_pkg_name => G_PKG_NAME
405 , p_procedure_name => l_api_name );
406
407 END IF;
408
409 FND_MSG_PUB.Count_And_Get
410 ( p_count => x_msg_count ,
411 p_data => x_msg_data );
412
413 END Delete_resource_rollup;
414 end PJI_PERF_RPTG_PUB;