1 PACKAGE BODY gms_budget_matrix AS
2 -- $Header: GMSBUMXB.pls 120.1 2005/07/26 14:21:25 appldev 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 gms_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 gms_budget_matrix_driver;
140
141
142 PROCEDURE gms_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
151 , x_tot2 IN OUT NOCOPY NUMBER )
152
153 IS
154 begin
155 select sum(
156 decode(x_amt_type,'RC',nvl(raw_cost,0),'BC',nvl(burdened_cost,0),'RE',nvl(revenue,0),
157 'QU',decode(track_as_labor_flag,'Y',nvl(quantity,0),0))),
158 sum(
159 decode(x_amt_type,'RC',nvl(raw_cost,0),'BC',nvl(burdened_cost,0),'RE',nvl(revenue,0),
160 'QU',nvl(quantity,0),0))
161 into x_tot,
162 x_tot2
163 from gms_budget_lines_v
164 where budget_version_id = x_budget_version_id
165 and task_id = x_task_id
166 and resource_list_member_id = x_RLMI
167 and project_id = x_project_id
168 and start_date between x_totals_start_date
169 and x_totals_end_date ;
170 end;
171
172 PROCEDURE gms_calc_bottom_totals(
173 x_project_id IN NUMBER
174 , x_Budget_version_id IN NUMBER
175 , x_task_id IN NUMBER
176 , x_start_date IN DATE
177 , x_end_date IN DATE
178 , x_list_view_totals IN OUT NOCOPY VARCHAR2
179 , x_p1 IN OUT NOCOPY VARCHAR2
180 , x_p2 IN OUT NOCOPY VARCHAR2
181 , x_p3 IN OUT NOCOPY VARCHAR2
182 , x_p4 IN OUT NOCOPY VARCHAR2
183 , x_p1_tot IN OUT NOCOPY NUMBER
184 , x_p2_tot IN OUT NOCOPY NUMBER
185 , x_p3_tot IN OUT NOCOPY NUMBER
186 , x_p4_tot IN OUT NOCOPY NUMBER )
187
188
189 IS
190 begin
191 select sum(decode(period_name,x_p1,
192 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
193 'BC', nvl(burdened_cost,0),
194 'RE', nvl(revenue,0),
195 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
196 0), 0) ) ,
197 sum(decode(period_name,x_p2,
198 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
199 'BC', nvl(burdened_cost,0),
200 'RE', nvl(revenue,0),
201 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
202 0), 0) ) ,
203 sum(decode(period_name,x_p3,
204 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
205 'BC', nvl(burdened_cost,0),
206 'RE', nvl(revenue,0),
207 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
208 0), 0) ) ,
209 sum(decode(period_name,x_p4,
210 decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
211 'BC', nvl(burdened_cost,0),
212 'RE', nvl(revenue,0),
213 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
214 0), 0) )
215 into x_p1_tot,
216 x_p2_tot,
217 x_p3_tot,
218 x_p4_tot
219 from gms_budget_lines_v
220 where budget_version_id = x_budget_version_id
221 and task_id = x_task_id
222 and project_id = x_project_id
223 and start_date between x_start_date
224 and x_end_date ;
225 end;
226
227 PROCEDURE gms_calc_grand_totals(
228 x_project_id IN NUMBER
229 , x_Budget_version_id IN NUMBER
230 , x_task_id IN NUMBER
231 , x_start_date IN DATE
232 , x_end_date IN DATE
233 , x_list_view_totals IN OUT NOCOPY VARCHAR2
234 , x_grand_tot IN OUT NOCOPY NUMBER )
235
236
237 IS
238 begin
239 select sum(decode(x_list_view_totals, 'RC', nvl(raw_cost,0),
240 'BC', nvl(burdened_cost,0),
241 'RE', nvl(revenue,0),
242 'QU', decode(track_As_labor_flag,'Y',nvl(quantity,0),0 ),
243 0) )
244 into x_grand_tot
245 from gms_budget_lines_v
246 where budget_version_id = x_budget_version_id
247 and task_id = x_task_id
248 and project_id = x_project_id
249 and start_date between x_start_date
250 and x_end_date ;
251 end;
252
253 END gms_budget_matrix;