1 PACKAGE BODY PA_VRM_UTILS AS
2 --$Header: PAVRMUTB.pls 120.0.12020000.1 2013/03/22 06:55:05 rdegala noship $
3
4 FUNCTION GET_REVENUE_DIFF_AMOUNT (
5 p_project_id IN VARCHAR2,
6 p_current_version_number IN NUMBER
7 )
8 RETURN NUMBER
9 IS
10 l_diff_amount NUMBER;
11 l_prev_version_number NUMBER;
12
13 CURSOR get_prev_baselined_version(c_prev_version_number IN NUMBER) IS
14 select VERSION_NUMBER from PA_BUDGET_VERSIONS pbv
15 where PROJECT_ID = p_project_id
16 and pbv.APPROVED_REV_PLAN_TYPE_FLAG ='Y'
17 AND pbv.ci_id IS NULL
18 and pbv.VERSION_NUMBER = c_prev_version_number
19 and pbv.BUDGET_STATUS_CODE = 'B';
20
21 CURSOR get_revenue_amount IS
22 select REVENUE from PA_BUDGET_VERSIONS pbv
23 where PROJECT_ID = p_project_id
24 and pbv.APPROVED_REV_PLAN_TYPE_FLAG ='Y'
25 AND pbv.ci_id IS NULL
26 and pbv.VERSION_NUMBER = p_current_version_number
27 and pbv.BUDGET_STATUS_CODE = 'B';
28
29 CURSOR get_diff_revenue_amount(c_prev_version_number IN NUMBER) IS
30 select pbv.REVENUE - (select pbv1.REVENUE from PA_BUDGET_VERSIONS pbv1
31 where pbv1.PROJECT_ID = pbv.PROJECT_ID and pbv1.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
32 AND pbv.ci_id IS NULL
33 and pbv1.VERSION_NUMBER = c_prev_version_number and BUDGET_STATUS_CODE = 'B')
34 from PA_BUDGET_VERSIONS pbv where project_id = p_project_id and
35 pbv.APPROVED_REV_PLAN_TYPE_FLAG ='Y' and pbv.BUDGET_STATUS_CODE = 'B'
36 and pbv.VERSION_NUMBER = p_current_version_number;
37
38 BEGIN
39 IF p_current_version_number = 1 THEN
40 l_prev_version_number := -1;
41 ELSE
42 l_prev_version_number := p_current_version_number - 1;
43 FOR lcntr IN REVERSE 1..l_prev_version_number
44 LOOP
45 OPEN get_prev_baselined_version(l_prev_version_number);
46 FETCH get_prev_baselined_version INTO l_prev_version_number;
47 IF get_prev_baselined_version%notfound THEN
48 l_prev_version_number := -1;
49 ELSE
50 EXIT;
51 END IF;
52 CLOSE get_prev_baselined_version;
53
54 END LOOP;
55 END IF;
56 IF l_prev_version_number = -1 THEN
57 OPEN get_revenue_amount;
58 FETCH get_revenue_amount INTO l_diff_amount;
59 CLOSE get_revenue_amount;
60 RETURN l_diff_amount;
61 ELSE
62 OPEN get_diff_revenue_amount(l_prev_version_number);
63 FETCH get_diff_revenue_amount INTO l_diff_amount;
64 CLOSE get_diff_revenue_amount;
65 RETURN l_diff_amount;
66 END IF;
67 END;
68
69
70
71 FUNCTION GET_COST_DIFF_AMOUNT (
72 p_project_id IN VARCHAR2,
73 p_current_version_number IN NUMBER
74 )
75 RETURN NUMBER
76 IS
77 l_diff_amount NUMBER;
78 l_prev_version_number NUMBER;
79
80 CURSOR get_prev_baselined_version(c_prev_version_number IN NUMBER) IS
81 select VERSION_NUMBER from PA_BUDGET_VERSIONS pbv
82 where PROJECT_ID = p_project_id
83 and pbv.APPROVED_COST_PLAN_TYPE_FLAG ='Y'
84 AND pbv.ci_id IS NULL
85 and pbv.VERSION_NUMBER = c_prev_version_number
86 and pbv.BUDGET_STATUS_CODE = 'B';
87
88 CURSOR get_cost_amount IS
89 select BURDENED_COST from PA_BUDGET_VERSIONS pbv
90 where PROJECT_ID = p_project_id
91 and pbv.APPROVED_COST_PLAN_TYPE_FLAG ='Y'
92 AND pbv.ci_id IS NULL
93 and pbv.VERSION_NUMBER = p_current_version_number
94 and pbv.BUDGET_STATUS_CODE = 'B';
95
96 CURSOR get_diff_cost_amount(c_prev_version_number IN NUMBER) IS
97 select pbv.BURDENED_COST - (select pbv1.BURDENED_COST from PA_BUDGET_VERSIONS pbv1
98 where pbv1.PROJECT_ID = pbv.PROJECT_ID and pbv1.APPROVED_COST_PLAN_TYPE_FLAG = 'Y'
99 AND pbv.ci_id IS NULL
100 and pbv1.VERSION_NUMBER = c_prev_version_number and BUDGET_STATUS_CODE = 'B')
101 from PA_BUDGET_VERSIONS pbv where project_id = p_project_id and
102 pbv.APPROVED_COST_PLAN_TYPE_FLAG ='Y' and pbv.BUDGET_STATUS_CODE = 'B'
103 and pbv.VERSION_NUMBER = p_current_version_number;
104
105 BEGIN
106 IF p_current_version_number = 1 THEN
107 l_prev_version_number := -1;
108 ELSE
109 l_prev_version_number := p_current_version_number - 1;
110 FOR lcntr IN REVERSE 1..l_prev_version_number
111 LOOP
112 OPEN get_prev_baselined_version(l_prev_version_number);
113 FETCH get_prev_baselined_version INTO l_prev_version_number;
114 IF get_prev_baselined_version%notfound THEN
115 l_prev_version_number := -1;
116 ELSE
117 EXIT;
118 END IF;
119 CLOSE get_prev_baselined_version;
120
121 END LOOP;
122 END IF;
123 IF l_prev_version_number = -1 THEN
124 OPEN get_cost_amount;
125 FETCH get_cost_amount INTO l_diff_amount;
126 CLOSE get_cost_amount;
127 RETURN l_diff_amount;
128 ELSE
129 OPEN get_diff_cost_amount(l_prev_version_number);
130 FETCH get_diff_cost_amount INTO l_diff_amount;
131 CLOSE get_diff_cost_amount;
132 RETURN l_diff_amount;
133 END IF;
134 END;
135
136
137
138 FUNCTION IS_INITIAL_BASELINED_VER (
139 p_project_id IN VARCHAR2,
140 p_current_version_number IN NUMBER,
141 p_budget_type IN CHAR -- C for Cost, R for revenue, A for all
142 )
143 RETURN CHAR
144 IS
145 l_diff_amount NUMBER;
146 l_prev_version_number NUMBER;
147 l_is_initial_baselined_ver CHAR;
148
149 CURSOR get_prev_cost_version(c_prev_version_number IN NUMBER) IS
150 select VERSION_NUMBER from PA_BUDGET_VERSIONS pbv
151 where PROJECT_ID = p_project_id
152 and pbv.APPROVED_COST_PLAN_TYPE_FLAG ='Y'
153 AND pbv.ci_id IS NULL
154 and pbv.VERSION_NUMBER = c_prev_version_number
155 and pbv.BUDGET_STATUS_CODE = 'B';
156
157 CURSOR get_prev_rev_version(c_prev_version_number IN NUMBER) IS
158 select VERSION_NUMBER from PA_BUDGET_VERSIONS pbv
159 where PROJECT_ID = p_project_id
160 and pbv.APPROVED_REV_PLAN_TYPE_FLAG ='Y'
161 AND pbv.ci_id IS NULL
162 and pbv.VERSION_NUMBER = c_prev_version_number
163 and pbv.BUDGET_STATUS_CODE = 'B';
164
165 BEGIN
166 IF p_current_version_number = 1 THEN
167 l_prev_version_number := -1;
168 RETURN 'Y';
169 ELSE
170 l_prev_version_number := p_current_version_number - 1;
171 IF p_budget_type = 'C' THEN
172 FOR lcntr IN REVERSE 1..l_prev_version_number
173 LOOP
174 OPEN get_prev_cost_version(l_prev_version_number);
175 FETCH get_prev_cost_version INTO l_prev_version_number;
176 IF get_prev_cost_version%notfound THEN
177 l_prev_version_number := -1;
178 ELSE
179 EXIT;
180 END IF;
181 CLOSE get_prev_cost_version;
182 END LOOP;
183 ELSE IF p_budget_type = 'R' THEN
184 FOR lcntr IN REVERSE 1..l_prev_version_number
185 LOOP
186 OPEN get_prev_rev_version(l_prev_version_number);
187 FETCH get_prev_rev_version INTO l_prev_version_number;
188 IF get_prev_rev_version%notfound THEN
189 l_prev_version_number := -1;
190 ELSE
191 EXIT;
192 END IF;
193 CLOSE get_prev_rev_version;
194 END LOOP;
195 END IF;
196 END IF;
197 END IF;
198 IF l_prev_version_number = -1 THEN
199 RETURN 'Y';
200 ELSE
201 RETURN 'N';
202 END IF;
203 END;
204
205
206 FUNCTION GET_INITIAL_BASELINED_REV_VER (
207 p_project_id IN VARCHAR2
208 )
209 RETURN NUMBER
210 IS
211 l_initial_baselined_ver NUMBER;
212
213 CURSOR get_prev_rev_bl_version IS
214 select BUDGET_VERSION_ID from PA_BUDGET_VERSIONS pbv
215 where PROJECT_ID = p_project_id
216 and pbv.APPROVED_REV_PLAN_TYPE_FLAG ='Y'
217 AND pbv.ci_id IS NULL
218 and pbv.VERSION_NUMBER = (select min(VERSION_NUMBER) from PA_BUDGET_VERSIONS where PROJECT_ID = p_project_id
219 AND APPROVED_REV_PLAN_TYPE_FLAG ='Y' AND ci_id IS NULL AND BUDGET_STATUS_CODE = 'B')
220 and pbv.BUDGET_STATUS_CODE = 'B';
221
222 BEGIN
223 OPEN get_prev_rev_bl_version();
224 FETCH get_prev_rev_bl_version INTO l_initial_baselined_ver;
225 CLOSE get_prev_rev_bl_version;
226 return l_initial_baselined_ver;
227 END;
228
229 END PA_VRM_UTILS;
[Home] [Help]