1 PACKAGE BODY Pji_Ev_Util AS
2 -- $Header: PJIPREVB.pls 120.1 2005/09/16 15:25:12 appldev noship $
3
4 PROCEDURE populate_percent_complete
5 (p_start_date IN VARCHAR2,
6 p_end_date IN VARCHAR2,
7 p_calendar_id IN NUMBER,
8 p_slice_name IN VARCHAR2,
9 p_project_id IN VARCHAR2,
10 p_proj_element_id IN VARCHAR2,
11 p_structure_version_id IN NUMBER,
12 p_include_sub_tasks_flag IN VARCHAR2 ,
13 p_calendar_type IN VARCHAR2,
14 p_prg_flag IN VARCHAR2,
15 x_msg_count IN OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_err_msg_data OUT NOCOPY VARCHAR2)
18 IS
19 l_completed_per NUMBER ;
20 l_object_type pa_proj_elements.object_type%TYPE;
21 l_calendar_type pji_fp_xbs_accum_f.calendar_type%TYPE := p_calendar_type;
22
23 CURSOR ent_periods IS
24 SELECT ent_period_id period_id , end_date
25 FROM pji_time_ent_period_v
26 WHERE end_date >= TO_DATE(p_start_date,'j')
27 AND start_date <= TO_DATE(p_end_date,'j')
28 AND 'PJI_TIME_ENT_PERIOD_V'=p_slice_name
29 UNION ALL
30 SELECT ent_qtr_id period_id , end_date
31 FROM pji_time_ent_qtr_v
32 WHERE end_date >= TO_DATE(p_start_date,'j')
33 AND start_date <= TO_DATE(p_end_date,'j')
34 AND 'PJI_TIME_ENT_QTR_V'=p_slice_name
35 UNION ALL
36 SELECT ent_year_id period_id , end_date
37 FROM pji_time_ent_year_v
38 WHERE end_date >= TO_DATE(p_start_date,'j')
39 AND start_date <= TO_DATE(p_end_date,'j')
40 AND 'PJI_TIME_ENT_YEAR_V'=p_slice_name
41 ORDER BY period_id;
42
43 CURSOR gl_pa_periods IS
44 SELECT cal_period_id period_id , end_date
45 FROM pji_time_cal_period_v
46 WHERE end_date >= TO_DATE(p_start_date,'j')
47 AND start_date <= TO_DATE(p_end_date,'j')
48 AND calendar_id=p_calendar_id
49 AND 'PJI_TIME_CAL_PERIOD_V'=p_slice_name
50 UNION ALL
51 SELECT cal_qtr_id period_id , end_date
52 FROM pji_time_cal_qtr_v
53 WHERE end_date >= TO_DATE(p_start_date,'j')
54 AND start_date <= TO_DATE(p_end_date,'j')
55 AND calendar_id=p_calendar_id
56 AND 'PJI_TIME_CAL_QTR_V'=p_slice_name
57 UNION ALL
58 SELECT cal_year_id period_id , end_date
59 FROM pji_time_cal_year_v
60 WHERE end_date >= TO_DATE(p_start_date,'j')
61 AND start_date <= TO_DATE(p_end_date,'j')
62 AND calendar_id=p_calendar_id
63 AND 'PJI_TIME_CAL_YEAR_V'=p_slice_name
64 ORDER BY period_id;
65
66 CURSOR object_type IS
67 SELECT object_type
68 FROM pa_proj_elements
69 WHERE proj_element_id = p_proj_element_id
70 AND project_id = p_project_id ;
71
72
73 BEGIN
74 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
75
76 OPEN object_type;
77 FETCH object_type INTO l_object_type;
78 CLOSE object_type;
79
80 DELETE PJI_PRD_PER_COMPLETE_TMP;
81
82 /* If calendar is enterprise, then open the corresponding cursor and fetch the percent complete */
83 IF (l_calendar_type = 'E')
84 THEN
85
86 FOR rec_ent_periods IN ent_periods LOOP
87 IF p_prg_flag = 'N' THEN
88 l_completed_per := Pa_Progress_Utils.get_pc_from_sub_tasks_assgn
89 (p_project_id
90 ,p_proj_element_id
91 ,p_structure_version_id
92 ,p_include_sub_tasks_flag
93 ,'FINANCIAL'
94 ,l_object_type
95 ,rec_ent_periods.end_date);
96 ELSE
97 l_completed_per := NULL;
98 END IF;
99
100 INSERT INTO PJI_PRD_PER_COMPLETE_TMP (period_id,completed_percentage) VALUES
101 (rec_ent_periods.period_id,l_completed_per);
102
103 END LOOP;
104
105
106 /* else fetch data for pa and gl calendars */
107 ELSE
108
109
110 FOR rec_gl_pa_periods IN gl_pa_periods LOOP
111 IF p_prg_flag = 'N' THEN
112 l_completed_per := Pa_Progress_Utils.get_pc_from_sub_tasks_assgn
113 (p_project_id
114 ,p_proj_element_id
115 ,p_structure_version_id
116 ,p_include_sub_tasks_flag
117 ,'FINANCIAL'
118 ,l_object_type
119 ,rec_gl_pa_periods.end_date);
120 ELSE
121 l_completed_per := NULL;
122 END IF;
123
124 INSERT INTO PJI_PRD_PER_COMPLETE_TMP (period_id,completed_percentage) VALUES
125 (rec_gl_pa_periods.period_id,l_completed_per );
126
127
128 END LOOP;
129
130
131 END IF;
132
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
137 x_msg_count := x_msg_count + 1;
138 RAISE;
139
140 END populate_percent_complete;
141
142
143 END Pji_Ev_Util;
[Home] [Help]