[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_MATRIX
Source
1 PACKAGE BODY pa_budget_matrix AS
2 -- $Header: PAXBUMXB.pls 120.2.12020000.2 2012/07/19 10:02:49 admarath ship $
3 --================================================================
4 --
5 --
6 ------------------------------------------------------------------------------------------------------------------
7 -- Functions and Procedures to Drive Budget Matrix Form Views
8 ------------------------------------------------------------------------------------------------------------------
9 --
10
11 FUNCTION Get_project_id RETURN NUMBER
12 IS
13 BEGIN
14
15 RETURN ( GlobVars.project_id );
16 END;
17
18 FUNCTION Get_budget_version_id RETURN NUMBER
19 IS
20 BEGIN
21
22 RETURN ( GlobVars.Budget_version_id );
23 END;
24
25 FUNCTION Get_Task_Id RETURN NUMBER
26 IS
27 BEGIN
28
29 RETURN ( GlobVars.Task_Id );
30 END;
31
32
33 FUNCTION Get_prd1 RETURN DATE
34 IS
35 BEGIN
36
37 RETURN ( GlobVars.Prd1 );
38 END;
39
40
41 FUNCTION Get_prd2 RETURN DATE
42 IS
43 BEGIN
44
45 RETURN ( GlobVars.Prd2 );
46 END;
47
48
49 FUNCTION Get_prd3 RETURN DATE
50 IS
51 BEGIN
52
53 RETURN ( GlobVars.Prd3 );
54 END;
55
56 FUNCTION Get_prd4 RETURN DATE
57 IS
58 BEGIN
59
60 RETURN ( GlobVars.Prd4 );
61 END;
62
63 FUNCTION Get_prd5 RETURN DATE
64 IS
65 BEGIN
66
67 RETURN ( GlobVars.Prd5 );
68 END;
69
70 FUNCTION Get_totals_start_date RETURN DATE
71 IS
72 BEGIN
73
74 RETURN ( GlobVars.totals_start_date );
75 END;
76
77 FUNCTION Get_totals_end_date RETURN DATE
78 IS
79 BEGIN
80
81 RETURN ( GlobVars.totals_end_date );
82 END;
83
84
85 FUNCTION Get_Raw_Cost_Flag RETURN VARCHAR2
86 IS
87 BEGIN
88 RETURN (GlobVars.Raw_Cost_Flag);
89 END;
90
91 FUNCTION Get_Burdened_Cost_Flag RETURN VARCHAR2
92 IS
93 BEGIN
94 RETURN (GlobVars.Burdened_Cost_Flag);
95 END;
96
97 FUNCTION Get_Quantity_Flag RETURN VARCHAR2
98 IS
99 BEGIN
100 RETURN (GlobVars.Quantity_Flag);
101 END;
102
103 FUNCTION Get_Revenue_Flag RETURN VARCHAR2
104 IS
105 BEGIN
106 RETURN (GlobVars.Revenue_Flag);
107 END;
108
109 PROCEDURE pa_budget_matrix_driver (
110 x_project_id IN NUMBER
111 , x_Budget_version_id IN NUMBER
112 , x_task_id IN NUMBER
113 , x_Prd1 IN DATE
114 , x_Prd2 IN DATE
115 , x_Prd3 IN DATE
116 , x_Prd4 IN DATE
117 , x_Prd5 IN DATE
118 , x_totals_start_date IN DATE
119 , x_totals_end_date IN DATE
120 , x_Raw_cost_flag IN VARCHAR2
121 , x_Burdened_cost_flag IN VARCHAR2
122 , x_Revenue_flag IN VARCHAR2
123 , x_Quantity_flag IN VARCHAR2) IS
124 BEGIN
125 GlobVars.project_id := x_project_id;
126 GlobVars.Budget_version_id := x_Budget_version_id;
127 GlobVars.Task_Id := x_task_id;
128 GlobVars.Prd1 := x_Prd1;
129 GlobVars.Prd2 := x_Prd2;
130 GlobVars.Prd3 := x_Prd3;
131 GlobVars.Prd4 := x_Prd4;
132 GlobVars.Prd5 := x_Prd5;
133 GlobVars.Totals_start_date := x_totals_start_date;
134 GlobVars.Totals_end_date := x_totals_end_date;
135 GlobVars.Raw_cost_flag := x_Raw_cost_flag;
136 GlobVars.Burdened_cost_flag := x_Burdened_cost_flag ;
137 GlobVars.Revenue_flag := x_Revenue_flag;
138 GlobVars.Quantity_flag := x_Quantity_flag;
139 END pa_budget_matrix_driver;
140
141
142 PROCEDURE pa_calc_side_totals(
143 x_project_id IN NUMBER
144 , x_Budget_version_id IN NUMBER
145 , x_task_id IN NUMBER
146 , x_RLMI IN NUMBER
147 , x_Totals_start_date IN DATE
148 , x_Totals_end_date IN DATE
149 , x_amt_type IN VARCHAR2
150 , x_tot IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
151 , x_tot2 IN OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
152
153 IS
154 nc_tot NUMBER;
155 nc_tot2 NUMBER;
156
157 begin
158 nc_tot := x_tot;
159 nc_tot2 := x_tot2;
160
161 select sum(
162 decode(x_amt_type,'RC',nvl(raw_cost,0),'BC',nvl(burdened_cost,0),'RE',nvl(revenue,0),
163 'QU',decode(track_as_labor_flag,'Y',nvl(quantity,0),0))),
164 sum(
165 decode(x_amt_type,'RC',nvl(raw_cost,0),'BC',nvl(burdened_cost,0),'RE',nvl(revenue,0),
166 'QU',nvl(quantity,0),0))
167 into x_tot,
168 x_tot2
169 from pa_budget_lines_v
170 where budget_version_id = x_budget_version_id
171 and task_id = x_task_id
172 and resource_list_member_id = x_RLMI
173 and project_id = x_project_id
174 and start_date between x_totals_start_date
175 and x_totals_end_date ;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 x_tot := nc_tot;
180 x_tot2 := nc_tot2;
181 RAISE;
182
183 end;
184
185 PROCEDURE pa_calc_bottom_totals(
186 x_project_id IN NUMBER
187 , x_Budget_version_id IN NUMBER
188 , x_task_id IN NUMBER
189 , x_start_date IN DATE
190 , x_end_date IN DATE
191 , x_list_view_totals IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
192 , x_p1 IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
193 , x_p2 IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
194 , x_p3 IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
195 , x_p4 IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
196 , x_p1_tot IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
197 , x_p2_tot IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
198 , x_p3_tot IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
199 , x_p4_tot IN OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
200
201
202 IS
203 nc_list_view_totals VARCHAR2(200);
204 nc_p1 VARCHAR2(200);
205 nc_p2 VARCHAR2(200);
206 nc_p3 VARCHAR2(200);
207 nc_p4 VARCHAR2(200);
208 nc_p1_tot NUMBER;
209 nc_p2_tot NUMBER;
210 nc_p3_tot NUMBER;
211 nc_p4_tot NUMBER;
212
213 begin
214 nc_list_view_totals := x_list_view_totals;
215 nc_p1 := x_p1;
216 nc_p2 := x_p2;
217 nc_p3 := x_p3;
218 nc_p4 := x_p4;
222 nc_p4_tot := x_p4_tot;
219 nc_p1_tot := x_p1_tot;
220 nc_p2_tot := x_p2_tot;
221 nc_p3_tot := x_p3_tot;
223
224 select sum(decode(period_name,x_p1,
225 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
226 'BC', nvl(burdened_cost,0),
227 'RE', nvl(revenue,0),
228 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
229 0), 0) ) ,
230 sum(decode(period_name,x_p2,
231 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
232 'BC', nvl(burdened_cost,0),
233 'RE', nvl(revenue,0),
234 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
235 0), 0) ) ,
236 sum(decode(period_name,x_p3,
237 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
238 'BC', nvl(burdened_cost,0),
239 'RE', nvl(revenue,0),
240 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
241 0), 0) ) ,
242 sum(decode(period_name,x_p4,
243 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
244 'BC', nvl(burdened_cost,0),
245 'RE', nvl(revenue,0),
246 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
247 0), 0) )
248 into x_p1_tot,
249 x_p2_tot,
250 x_p3_tot,
251 x_p4_tot
252 from pa_budget_lines_v
253 where budget_version_id = x_budget_version_id
254 and task_id = x_task_id
255 and project_id = x_project_id
256 and start_date between x_start_date
257 and x_end_date ;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 x_list_view_totals := nc_list_view_totals;
262 x_p1 := nc_p1;
263 x_p2 := nc_p2;
264 x_p3 := nc_p3;
265 x_p4 := nc_p4;
266 x_p1_tot := nc_p1_tot;
267 x_p2_tot := nc_p2_tot;
268 x_p3_tot := nc_p3_tot;
269 x_p4_tot := nc_p4_tot;
270 RAISE;
271
272 end;
273
274 PROCEDURE pa_calc_grand_totals(
275 x_project_id IN NUMBER
276 , x_Budget_version_id IN NUMBER
277 , x_task_id IN NUMBER
278 , x_start_date IN DATE
279 , x_end_date IN DATE
280 , x_list_view_totals IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
281 , x_grand_tot IN OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
282
283
284 IS
285 nc_list_view_totals VARCHAR2(200);
286 nc_grand_tot NUMBER;
287
288 begin
289 nc_list_view_totals := x_list_view_totals;
290 nc_grand_tot := x_grand_tot;
291
292 select sum(decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
293 'BC', nvl(burdened_cost,0),
294 'RE', nvl(revenue,0),
295 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
296 0) )
297 into x_grand_tot
298 from pa_budget_lines_v
299 where budget_version_id = x_budget_version_id
300 and task_id = x_task_id
301 and project_id = x_project_id
302 and start_date between x_start_date
303 and x_end_date ;
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 x_list_view_totals := nc_list_view_totals;
308 x_grand_tot := nc_grand_tot;
309 RAISE;
310
311 end;
312
313 END pa_budget_matrix;