[Home] [Help]
PACKAGE BODY: APPS.PA_WP_EXCEPTION_UTILS
Source
1 package body PA_WP_EXCEPTION_UTILS as
2 /*$Header: PAWPXCUB.pls 120.1.12010000.2 2008/09/12 19:31:01 snizam ship $*/
3
4 -- API name : get_wp_exception_value
5 -- Type : Utils Procedure
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Parameters
9 --
10 -- 25-JUN-01 HSIU -Created
11 --
12
13 procedure get_wp_exception_value
14 (
15 p_object_type IN VARCHAR2,
16 p_object_id IN NUMBER,
17 p_measure_id IN NUMBER,
18 p_period_type IN VARCHAR2,
19 x_measure_value OUT NOCOPY NUMBER, -- 4537865
20 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
21 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
22 x_msg_count OUT NOCOPY NUMBER, -- 4537865
23 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
24 )
25 IS
26 BEGIN
27 --need to replace measure ids with correct values
28 --once measure has been seeded
29 -- IF (p_measure_id = 11) THEN
30 IF (p_measure_id = 427) THEN
31 --get ITD workplan effort variance
32 PA_WP_EXCEPTION_UTILS.get_ITD_workplan_effort_var(
33 p_object_type => p_object_type,
34 p_object_id => p_object_id,
35 p_measure_id => p_measure_id,
36 p_period_type => p_period_type,
37 x_measure_value => x_measure_value,
38 x_period_name => x_period_name,
39 x_return_status => x_return_status,
40 x_msg_count => x_msg_count,
41 x_msg_data => x_msg_data
42 );
43 -- ELSIF (p_measure_id = 12) THEN
44 ELSIF (p_measure_id = 428) THEN
45 --get forecast workplan effor variance
46 PA_WP_EXCEPTION_UTILS.get_forecast_wp_eff_var(
47 p_object_type => p_object_type,
48 p_object_id => p_object_id,
49 p_measure_id => p_measure_id,
50 p_period_type => p_period_type,
51 x_measure_value => x_measure_value,
52 x_period_name => x_period_name,
53 x_return_status => x_return_status,
54 x_msg_count => x_msg_count,
55 x_msg_data => x_msg_data
56 );
57 -- ELSIF (p_measure_id = 13) THEN
58 ELSIF (p_measure_id = 429) THEN
59 --get current forecast to prior forecast workplan effor variance
60 PA_WP_EXCEPTION_UTILS.get_cur_fc_to_pri_effort_var(
61 p_object_type => p_object_type,
62 p_object_id => p_object_id,
63 p_measure_id => p_measure_id,
64 p_period_type => p_period_type,
65 x_measure_value => x_measure_value,
66 x_period_name => x_period_name,
67 x_return_status => x_return_status,
68 x_msg_count => x_msg_count,
69 x_msg_data => x_msg_data
70 );
71 -- ELSIF (p_measure_id = 14) THEN
72 ELSIF (p_measure_id = 430) THEN
73 --get schedule baseline finish variance
74 PA_WP_EXCEPTION_UTILS.get_sch_bsln_fin_var(
75 p_object_type => p_object_type,
76 p_object_id => p_object_id,
77 p_measure_id => p_measure_id,
78 p_period_type => p_period_type,
79 x_measure_value => x_measure_value,
80 x_period_name => x_period_name,
81 x_return_status => x_return_status,
82 x_msg_count => x_msg_count,
83 x_msg_data => x_msg_data
84 );
85 -- ELSIF (p_measure_id = 15) THEN
86 ELSIF (p_measure_id = 431) THEN
87 --get schedule baseline start variance
88 PA_WP_EXCEPTION_UTILS.get_sch_bsln_st_var(
89 p_object_type => p_object_type,
90 p_object_id => p_object_id,
91 p_measure_id => p_measure_id,
92 p_period_type => p_period_type,
93 x_measure_value => x_measure_value,
94 x_period_name => x_period_name,
95 x_return_status => x_return_status,
96 x_msg_count => x_msg_count,
97 x_msg_data => x_msg_data
98 );
99 -- ELSIF (p_measure_id = 16) THEN
100 ELSIF (p_measure_id = 432) THEN
101 --get schedule prior published version finish variance
102 PA_WP_EXCEPTION_UTILS.get_sch_pri_pub_ver_fin_var(
103 p_object_type => p_object_type,
104 p_object_id => p_object_id,
105 p_measure_id => p_measure_id,
106 p_period_type => p_period_type,
107 x_measure_value => x_measure_value,
108 x_period_name => x_period_name,
109 x_return_status => x_return_status,
110 x_msg_count => x_msg_count,
111 x_msg_data => x_msg_data
112 );
113 -- ELSIF (p_measure_id = 17) THEN
114 ELSIF (p_measure_id = 433) THEN
115 --get schedule prior published version start variance
116 PA_WP_EXCEPTION_UTILS.get_sch_pri_pub_ver_st_var(
117 p_object_type => p_object_type,
118 p_object_id => p_object_id,
119 p_measure_id => p_measure_id,
120 p_period_type => p_period_type,
121 x_measure_value => x_measure_value,
122 x_period_name => x_period_name,
123 x_return_status => x_return_status,
124 x_msg_count => x_msg_count,
125 x_msg_data => x_msg_data
126 );
127 -- ELSIF (p_measure_id = 18) THEN
128 ELSIF (p_measure_id = 434) THEN
129 --get schedule estimated finish variance
130 PA_WP_EXCEPTION_UTILS.get_sch_est_fin_var(
131 p_object_type => p_object_type,
132 p_object_id => p_object_id,
133 p_measure_id => p_measure_id,
134 p_period_type => p_period_type,
135 x_measure_value => x_measure_value,
136 x_period_name => x_period_name,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data
140 );
141 -- ELSIF (p_measure_id = 19) THEN
142 ELSIF (p_measure_id = 435) THEN
143 --get schedule estimated start variance
144 PA_WP_EXCEPTION_UTILS.get_sch_est_st_var(
145 p_object_type => p_object_type,
146 p_object_id => p_object_id,
147 p_measure_id => p_measure_id,
148 p_period_type => p_period_type,
149 x_measure_value => x_measure_value,
150 x_period_name => x_period_name,
151 x_return_status => x_return_status,
152 x_msg_count => x_msg_count,
153 x_msg_data => x_msg_data
154 );
155 END IF;
156 -- 4537865
157 EXCEPTION
158 WHEN OTHERS THEN
159 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
160 x_period_name := NULL ;
161 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
162 x_msg_count := 1;
163 x_msg_data := SQLERRM;
164
165 Fnd_Msg_Pub.add_exc_msg
166 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
167 , p_procedure_name => 'get_wp_exception_value'
168 , p_error_text => x_msg_data);
169
170 RAISE;
171
172 END get_wp_exception_value;
173
174 PROCEDURE get_ITD_workplan_effort_var
175 (
176 p_object_type IN VARCHAR2,
177 p_object_id IN NUMBER,
178 p_measure_id IN NUMBER,
179 p_period_type IN VARCHAR2,
180 x_measure_value OUT NOCOPY NUMBER, -- 4537865
181 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
182 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
183 x_msg_count OUT NOCOPY NUMBER, -- 4537865
184 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
185 )
186 IS
187 l_wp_versioN_id NUMBER;
188 CURSOR c1(c_wp_ver_id NUMBER) IS
189 select (NVL(PPRU.ppl_act_effort_to_date,0) + NVL(ppru.eqpmt_act_effort_to_date,0)) -
190 -- (NVL(pfxat.labor_hours, 0) + NVL(pfxat.equipment_hours, 0))
191 (NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0))
192 from pji_xbs_plans_v pxpv,
193 -- from pji_fm_xbs_accum_tmp1 pfxat,
194 pa_progress_rollup ppru,
195 pa_proj_element_versions ppev
196 where ppev.element_version_id = c_wp_ver_id
197 and ppev.project_id = ppru.project_id
198 and ppev.proj_element_id = ppru.object_id
199 and ppev.object_type = ppru.object_type
200 and ppru.structure_type = 'WORKPLAN'
201 and ppev.project_id = pxpv.project_id
202 and ppev.proj_element_id = pxpv.proj_element_id
203 and ppev.element_version_id = pxpv.structure_version_id
204 and pxpv.structure_type(+) = 'WORKPLAN'
205 -- and ppev.project_id = pfxat.project_id
206 -- and ppev.proj_element_id = pfxat.project_element_id
207 -- and ppev.element_version_id = pfxat.struct_version_id
208 -- and pfxat.calendar_type (+) = 'A'
209 order by ppru.as_of_date desc;
210
211 BEGIN
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 l_wp_versioN_id := pa_project_structure_utils.GET_LATEST_WP_VERSION(p_object_id);
215 IF l_wp_version_id IS NULL THEN
216 --no publish version; return 0;
217 x_measure_value := 0;
218 ELSE
219 OPEN c1(l_wp_version_id);
220 FETCH c1 into x_measure_value;
221 CLOSE c1;
222 END IF;
223 -- 4537865
224 EXCEPTION
225 WHEN OTHERS THEN
226 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
227 x_period_name := NULL ;
228 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
229 x_msg_count := 1;
230 x_msg_data := SQLERRM;
231
232 Fnd_Msg_Pub.add_exc_msg
233 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
234 , p_procedure_name => 'get_ITD_workplan_effort_var'
235 , p_error_text => x_msg_data);
236
237 RAISE;
238
239 END get_ITD_workplan_effort_var;
240
241 PROCEDURE get_forecast_wp_eff_var
242 (
243 p_object_type IN VARCHAR2,
244 p_object_id IN NUMBER,
245 p_measure_id IN NUMBER,
246 p_period_type IN VARCHAR2,
247 x_measure_value OUT NOCOPY NUMBER, -- 4537865
248 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
249 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
250 x_msg_count OUT NOCOPY NUMBER, -- 4537865
251 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
252 )
253 IS
254 l_wp_versioN_id NUMBER;
255 CURSOR c1(c_wp_ver_id NUMBER) IS
256 select (NVL(ppru.estimated_remaining_effort,0) +
257 NVL(ppru.eqpmt_etc_effort,0) +
258 NVL(ppru.PPL_ACT_EFFORT_TO_DATE, 0) +
259 NVL(ppru.EQPMT_ACT_EFFORT_TO_DATE, 0) ) -
260 -- (NVL(pfxat.labor_hours, 0) + NVL(pfxat.equipment_hours, 0))
261 (NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0))
262 from pji_xbs_plans_v pxpv,
263 -- from pji_fm_xbs_accum_tmp1 pfxat,
264 pa_progress_rollup ppru,
265 pa_proj_element_versions ppev
266 where ppev.element_version_id = c_wp_ver_id
267 and ppev.project_id = ppru.project_id
268 and ppev.proj_element_id = ppru.object_id
269 and ppev.object_type = ppru.object_type
270 and ppru.structure_version_id is null -- Added for bug 6337529
271 and ppru.structure_type = 'WORKPLAN'
272 and ppev.project_id = pxpv.project_id
273 and ppev.proj_element_id = pxpv.proj_element_id
274 and ppev.element_version_id = pxpv.structure_version_id
275 and pxpv.structure_type(+) = 'WORKPLAN'
276 -- and ppev.project_id = pfxat.project_id
277 -- and ppev.proj_element_id = pfxat.project_element_id
278 -- and ppev.element_version_id = pfxat.struct_version_id
279 -- and pfxat.calendar_type (+) = 'A'
280 order by ppru.as_of_date desc;
281
282 BEGIN
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 l_wp_versioN_id := pa_project_structure_utils.GET_LATEST_WP_VERSION(p_object_id);
286 IF l_wp_version_id IS NULL THEN
287 --no publish version; return 0;
288 x_measure_value := 0;
289 ELSE
290 OPEN c1(l_wp_version_id);
291 FETCH c1 into x_measure_value;
292 CLOSE c1;
293 END IF;
294 -- 4537865
295 EXCEPTION
296 WHEN OTHERS THEN
297 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
298 x_period_name := NULL ;
299 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
300 x_msg_count := 1;
301 x_msg_data := SQLERRM;
302
303 Fnd_Msg_Pub.add_exc_msg
304 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
305 , p_procedure_name => 'get_forecast_wp_eff_var'
306 , p_error_text => x_msg_data);
307
308 RAISE;
309
310 END get_forecast_wp_eff_var;
311
312 PROCEDURE get_cur_fc_to_pri_effort_var
313 (
314 p_object_type IN VARCHAR2,
315 p_object_id IN NUMBER,
316 p_measure_id IN NUMBER,
317 p_period_type IN VARCHAR2,
318 x_measure_value OUT NOCOPY NUMBER, -- 4537865
319 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
320 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
321 x_msg_count OUT NOCOPY NUMBER, -- 4537865
322 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
323 )
324 IS
325 l_wp_versioN_id NUMBER;
326 CURSOR c1(c_wp_ver_id NUMBER) IS
327 select (NVL(ppru.estimated_remaining_effort,0) + NVL(ppru.eqpmt_etc_effort,0))
328 from pa_progress_rollup ppru,
329 pa_proj_element_versions ppev
330 where ppev.element_version_id = c_wp_ver_id
331 and ppev.project_id = ppru.project_id
332 and ppev.proj_element_id = ppru.object_id
333 and ppev.object_type = ppru.object_type
334 and ppru.structure_type = 'WORKPLAN'
335 order by ppru.as_of_date desc;
336
337 l_value1 NUMBER;
338 l_value2 NUMBER;
339 BEGIN
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341
342 l_wp_versioN_id := pa_project_structure_utils.GET_LATEST_WP_VERSION(p_object_id);
343 IF l_wp_version_id IS NULL THEN
344 --no publish version; return 0;
345 x_measure_value := 0;
346 ELSE
347 OPEN c1(l_wp_version_id);
348 FETCH c1 into l_value1;
349 IF c1%NOTFOUND THEN
350 x_measure_value := 0;
351 CLOSE C1;
352 ELSE
353 FETCH c1 into l_value2;
354 IF c1%NOTFOUND THEN
355 x_measure_value := 0;
356 CLOSE c1;
357 ELSE
358 CLOSE c1;
359 END IF;
360 END IF;
361 END IF;
362 x_measure_value := l_value1 - l_value2;
363 -- 4537865
364 EXCEPTION
365 WHEN OTHERS THEN
366 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
367 x_period_name := NULL ;
368 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
369 x_msg_count := 1;
370 x_msg_data := SQLERRM;
371
372 Fnd_Msg_Pub.add_exc_msg
373 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
374 , p_procedure_name => 'get_cur_fc_to_pri_effort_var'
375 , p_error_text => x_msg_data);
376
377 RAISE;
378
379 END get_cur_fc_to_pri_effort_var;
380
381 PROCEDURE get_sch_bsln_fin_var
382 (
383 p_object_type IN VARCHAR2,
384 p_object_id IN NUMBER,
385 p_measure_id IN NUMBER,
386 p_period_type IN VARCHAR2,
387 x_measure_value OUT NOCOPY NUMBER, -- 4537865
388 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
389 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
390 x_msg_count OUT NOCOPY NUMBER, -- 4537865
391 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
392 )
393 IS
394 CURSOR c1 IS
395 select baseline_finish_date, scheduled_finish_date
396 from pa_projects_all
397 where project_id = p_object_id;
398 l_b_fin DATE;
399 l_s_fin DATE;
400 BEGIN
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 OPEN c1;
404 FETCH c1 into l_b_fin, l_s_fin;
405 CLOSE c1;
406
407 IF (l_b_fin IS NOT NULL) AND (l_s_fin IS NOT NULL) THEN
408 x_measure_value := l_s_fin - l_b_fin;
409 ELSE
410 x_measure_value := 0;
411 END IF;
412 -- 4537865
413 EXCEPTION
414 WHEN OTHERS THEN
415 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
416 x_period_name := NULL ;
417 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
418 x_msg_count := 1;
419 x_msg_data := SQLERRM;
420
421 Fnd_Msg_Pub.add_exc_msg
422 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
423 , p_procedure_name => 'get_sch_bsln_fin_var'
424 , p_error_text => x_msg_data);
425
426 RAISE;
427
428 END get_sch_bsln_fin_var;
429
430 PROCEDURE get_sch_bsln_st_var
431 (
432 p_object_type IN VARCHAR2,
433 p_object_id IN NUMBER,
434 p_measure_id IN NUMBER,
435 p_period_type IN VARCHAR2,
436 x_measure_value OUT NOCOPY NUMBER, -- 4537865
437 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
438 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
439 x_msg_count OUT NOCOPY NUMBER, -- 4537865
440 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
441 )
442 IS
443 CURSOR c1 IS
444 select baseline_start_date, scheduled_start_date
445 from pa_projects_all
446 where project_id = p_object_id;
447 l_b_st DATE;
448 l_s_st DATE;
449 BEGIN
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451
452 OPEN c1;
453 FETCH c1 into l_b_st, l_s_st;
454 CLOSE c1;
455
456 IF (l_b_st IS NOT NULL) AND (l_s_st IS NOT NULL) THEN
457 x_measure_value := l_s_st - l_b_st;
458 ELSE
459 x_measure_value := 0;
460 END IF;
461 -- 4537865
462 EXCEPTION
463 WHEN OTHERS THEN
464 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
465 x_period_name := NULL ;
466 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
467 x_msg_count := 1;
468 x_msg_data := SQLERRM;
469
470 Fnd_Msg_Pub.add_exc_msg
471 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
472 , p_procedure_name => 'get_sch_bsln_st_var'
473 , p_error_text => x_msg_data);
474
475 RAISE;
476 END get_sch_bsln_st_var;
477
478 PROCEDURE get_sch_pri_pub_ver_fin_var
479 (
480 p_object_type IN VARCHAR2,
481 p_object_id IN NUMBER,
482 p_measure_id IN NUMBER,
483 p_period_type IN VARCHAR2,
484 x_measure_value OUT NOCOPY NUMBER, -- 4537865
485 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
486 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
487 x_msg_count OUT NOCOPY NUMBER, -- 4537865
488 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
489 )
490 IS
491 CURSOR c1 IS
492 select sch.scheduled_finish_date
493 from pa_proj_elem_ver_schedule sch,
494 pa_proj_elem_ver_structure str,
495 pa_proj_structure_types ppst,
496 pa_structure_types pst
497 where pst.structure_type = 'WORKPLAN'
498 and pst.structure_type_id = ppst.structure_type_id
499 and ppst.proj_element_id = str.proj_element_id
500 and str.project_id = p_object_id
501 and str.project_id = sch.project_id
502 and str.proj_element_id = sch.proj_element_id
503 and str.element_version_id = sch.element_version_id
504 and str.status_code = 'STRUCTURE_PUBLISHED' --bug 3956895
505 order by str.published_date desc;
506
507 l_sch_date1 DATE;
508 l_sch_date2 DATE;
509 BEGIN
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 open C1;
513 FETCH c1 into l_sch_date1;
514 IF c1%NOTFOUND THEN
515 x_measure_value := 0;
516 CLOSE c1;
517 ELSE
518 fetch c1 into l_sch_date2;
519 IF c1%NOTFOUND THEN
520 x_measure_value := 0;
521 ELSE
522 x_measure_value := l_sch_date1 - l_sch_date2;
523 END IF;
524 close c1;
525 END IF;
526 -- 4537865
527 EXCEPTION
528 WHEN OTHERS THEN
529 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
530 x_period_name := NULL ;
531 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
532 x_msg_count := 1;
533 x_msg_data := SQLERRM;
534
535 Fnd_Msg_Pub.add_exc_msg
536 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
537 , p_procedure_name => 'get_sch_pri_pub_ver_fin_var'
538 , p_error_text => x_msg_data);
539
540 RAISE;
541 END get_sch_pri_pub_ver_fin_var;
542
543 PROCEDURE get_sch_pri_pub_ver_st_var
544 (
545 p_object_type IN VARCHAR2,
546 p_object_id IN NUMBER,
547 p_measure_id IN NUMBER,
548 p_period_type IN VARCHAR2,
549 x_measure_value OUT NOCOPY NUMBER, -- 4537865
550 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
551 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
552 x_msg_count OUT NOCOPY NUMBER, -- 4537865
553 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
554 )
555 IS
556 CURSOR c1 IS
557 select sch.scheduled_start_date
558 from pa_proj_elem_ver_schedule sch,
559 pa_proj_elem_ver_structure str,
560 pa_proj_structure_types ppst,
561 pa_structure_types pst
562 where pst.structure_type = 'WORKPLAN'
563 and pst.structure_type_id = ppst.structure_type_id
564 and ppst.proj_element_id = str.proj_element_id
565 and str.project_id = p_object_id
566 and str.project_id = sch.project_id
567 and str.proj_element_id = sch.proj_element_id
568 and str.element_version_id = sch.element_version_id
569 and str.status_code = 'STRUCTURE_PUBLISHED' --bug 3956895
570 order by str.published_date desc;
571
572 l_sch_date1 DATE;
573 l_sch_date2 DATE;
574 BEGIN
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 open C1;
578 FETCH c1 into l_sch_date1;
579 IF c1%NOTFOUND THEN
580 x_measure_value := 0;
581 CLOSE c1;
582 ELSE
583 fetch c1 into l_sch_date2;
584 IF c1%NOTFOUND THEN
585 x_measure_value := 0;
586 ELSE
587 x_measure_value := l_sch_date1 - l_sch_date2;
588 END IF;
589 close c1;
590 END IF;
591 -- 4537865
592 EXCEPTION
593 WHEN OTHERS THEN
594 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
595 x_period_name := NULL ;
596 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
597 x_msg_count := 1;
598 x_msg_data := SQLERRM;
599
600 Fnd_Msg_Pub.add_exc_msg
601 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
602 , p_procedure_name => 'get_sch_pri_pub_ver_st_var'
603 , p_error_text => x_msg_data);
604
605 RAISE;
606 END get_sch_pri_pub_ver_st_var;
607
608 PROCEDURE get_sch_est_fin_var
609 (
610 p_object_type IN VARCHAR2,
611 p_object_id IN NUMBER,
612 p_measure_id IN NUMBER,
613 p_period_type IN VARCHAR2,
614 x_measure_value OUT NOCOPY NUMBER, -- 4537865
615 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
616 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
617 x_msg_count OUT NOCOPY NUMBER, -- 4537865
618 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
619 )
620 IS
621 CURSOR c1 IS
622 select sch.scheduled_finish_date, nvl(sch.estimated_finish_date, sch.scheduled_finish_date)
623 from pa_proj_elem_ver_schedule sch,
624 pa_proj_elem_ver_structure str,
625 pa_proj_structure_types ppst,
626 pa_structure_types pst
627 where pst.structure_type = 'WORKPLAN'
628 and pst.structure_type_id = ppst.structure_type_id
629 and ppst.proj_element_id = str.proj_element_id
630 and str.project_id = p_object_id
631 and str.project_id = sch.project_id
632 and str.proj_element_id = sch.proj_element_id
633 and str.element_version_id = sch.element_version_id
634 and str.status_code = 'STRUCTURE_PUBLISHED' --bug 3956895
635 order by str.published_date desc;
636
637 l_sch_date DATE;
638 l_est_date DATE;
639 BEGIN
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 open C1;
643 FETCH c1 into l_sch_date, l_est_date;
644 IF c1%NOTFOUND THEN
645 x_measure_value := 0;
646 ELSE
647 x_measure_value := l_sch_date - l_est_date;
648 END IF;
649 close c1;
650 -- 4537865
651 EXCEPTION
652 WHEN OTHERS THEN
653 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
654 x_period_name := NULL ;
655 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
656 x_msg_count := 1;
657 x_msg_data := SQLERRM;
658
659 Fnd_Msg_Pub.add_exc_msg
660 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
661 , p_procedure_name => 'get_sch_est_fin_var'
662 , p_error_text => x_msg_data);
663
664 RAISE;
665 END get_sch_est_fin_var;
666
667 PROCEDURE get_sch_est_st_var
668 (
669 p_object_type IN VARCHAR2,
670 p_object_id IN NUMBER,
671 p_measure_id IN NUMBER,
672 p_period_type IN VARCHAR2,
673 x_measure_value OUT NOCOPY NUMBER, -- 4537865
674 x_period_name OUT NOCOPY VARCHAR2, -- 4537865
675 x_return_status OUT NOCOPY VARCHAR2, -- 4537865
676 x_msg_count OUT NOCOPY NUMBER, -- 4537865
677 x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
678 )
679 IS
680 CURSOR c1 IS
681 select sch.scheduled_start_date, nvl(sch.estimated_start_date, sch.scheduled_start_date)
682 from pa_proj_elem_ver_schedule sch,
683 pa_proj_elem_ver_structure str,
684 pa_proj_structure_types ppst,
685 pa_structure_types pst
686 where pst.structure_type = 'WORKPLAN'
687 and pst.structure_type_id = ppst.structure_type_id
688 and ppst.proj_element_id = str.proj_element_id
689 and str.project_id = p_object_id
690 and str.project_id = sch.project_id
691 and str.proj_element_id = sch.proj_element_id
692 and str.element_version_id = sch.element_version_id
693 and str.status_code = 'STRUCTURE_PUBLISHED' --bug 3956895
694 order by str.published_date desc;
695
696 l_sch_date DATE;
697 l_est_date DATE;
698 BEGIN
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 open C1;
702 FETCH c1 into l_sch_date, l_est_date;
703 IF c1%NOTFOUND THEN
704 x_measure_value := 0;
705 ELSE
706 x_measure_value := l_sch_date - l_est_date;
707 END IF;
708 close c1;
709 -- 4537865
710 EXCEPTION
711 WHEN OTHERS THEN
712 x_measure_value := 0 ; -- Setting this value to zero ,not NULL for issue mentioned in 3842408
713 x_period_name := NULL ;
714 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
715 x_msg_count := 1;
716 x_msg_data := SQLERRM;
717
718 Fnd_Msg_Pub.add_exc_msg
719 ( p_pkg_name => 'PA_WP_EXCEPTION_UTILS'
720 , p_procedure_name => 'get_sch_est_st_var'
721 , p_error_text => x_msg_data);
722
723 RAISE;
724 END get_sch_est_st_var;
725
726
727 end PA_WP_EXCEPTION_UTILS;