1 PACKAGE PA_MAINT_PROJECT_BUDGETS AS
2 /* $Header: PAACBUDS.pls 120.1 2005/08/19 16:13:42 mwasowic noship $ */
3
4 -- This package consists of the following two procedures
5 -- Process_Budget_Txns - this procedure reads the PA_BUDGET_BY_PA_PERIOD_V
6 -- which is a Union of PA_BASE_BUDGET_BY_PA_PERIOD_V and
7 -- PA_ORIG_BUDGET_BY_PA_PERIOD_V and processes all
8 -- Unaccumulated Budgets
9
10 -- Process_Budget_Tot - this procedure reads the PA_BUDGET_BY_RESOURCE_V which
11 -- is a Union of PA_BASE_BUDGET_BY_RESOURCE_V and
12 -- PA_ORIG_BUDGET_BY_RESOURCE_V . These views contain the
13 -- total budget amounts (including the amounts for
14 -- the periods beyond the defined PA_PERIODS
15 --
16 -- Modified 05/10/99 Shanif
17 -- Added more procedures and variables to improve the performance
18 -- of budgets processing.
19
20 V_Base_Burdened_Cost_itd Number :=0;
21 V_Base_Burdened_Cost_ptd Number :=0;
22 V_Base_Burdened_Cost_pp Number :=0;
23 V_Base_Burdened_Cost_ytd Number :=0;
24
25 V_Base_Labor_Hours_itd Number :=0;
26 V_Base_Labor_Hours_ptd Number :=0;
27 V_Base_Labor_Hours_pp Number :=0;
28 V_Base_Labor_Hours_ytd Number :=0;
29
30 V_Base_Raw_Cost_itd Number :=0;
31 V_Base_Raw_Cost_ptd Number :=0;
32 V_Base_Raw_Cost_pp Number :=0;
33 V_Base_Raw_Cost_ytd Number :=0;
34
35 V_Base_Revenue_itd Number :=0;
36 V_Base_Revenue_ptd Number :=0;
37 V_Base_Revenue_pp Number :=0;
38 V_Base_Revenue_ytd Number :=0;
39
40 V_Base_Quantity_itd Number :=0;
41 V_Base_Quantity_ptd Number :=0;
42 V_Base_Quantity_pp Number :=0;
43 V_Base_Quantity_ytd Number :=0;
44
45 V_Orig_Burdened_Cost_itd Number :=0;
46 V_Orig_Burdened_Cost_ptd Number :=0;
47 V_Orig_Burdened_Cost_pp Number :=0;
48 V_Orig_Burdened_Cost_ytd Number :=0;
49
50 V_Orig_Labor_Hours_itd Number :=0;
51 V_Orig_Labor_Hours_ptd Number :=0;
52 V_Orig_Labor_Hours_pp Number :=0;
53 V_Orig_Labor_Hours_ytd Number :=0;
54
55 V_Orig_Quantity_itd Number :=0;
56 V_Orig_Quantity_ptd Number :=0;
57 V_Orig_Quantity_pp Number :=0;
58 V_Orig_Quantity_ytd Number :=0;
59
60 V_Orig_Raw_Cost_itd Number :=0;
61 V_Orig_Raw_Cost_ptd Number :=0;
62 V_Orig_Raw_Cost_pp Number :=0;
63 V_Orig_Raw_Cost_ytd Number :=0;
64
65 V_Orig_Revenue_itd Number :=0;
66 V_Orig_Revenue_ptd Number :=0;
67 V_Orig_Revenue_pp Number :=0;
68 V_Orig_Revenue_ytd Number :=0;
69
70 Prj_Base_Burdened_Cost_itd Number :=0;
71 Prj_Base_Burdened_Cost_ptd Number :=0;
72 Prj_Base_Burdened_Cost_pp Number :=0;
73 Prj_Base_Burdened_Cost_ytd Number :=0;
74
75 Prj_Base_Labor_Hours_itd Number :=0;
76 Prj_Base_Labor_Hours_ptd Number :=0;
77 Prj_Base_Labor_Hours_pp Number :=0;
78 Prj_Base_Labor_Hours_ytd Number :=0;
79
80 Prj_Base_Raw_Cost_itd Number :=0;
81 Prj_Base_Raw_Cost_ptd Number :=0;
82 Prj_Base_Raw_Cost_pp Number :=0;
83 Prj_Base_Raw_Cost_ytd Number :=0;
84
85 Prj_Base_Revenue_itd Number :=0;
86 Prj_Base_Revenue_ptd Number :=0;
87 Prj_Base_Revenue_pp Number :=0;
88 Prj_Base_Revenue_ytd Number :=0;
89
90 Prj_Base_Quantity_itd Number :=0;
91 Prj_Base_Quantity_ptd Number :=0;
92 Prj_Base_Quantity_pp Number :=0;
93 Prj_Base_Quantity_ytd Number :=0;
94
95 Prj_Orig_Burdened_Cost_itd Number :=0;
96 Prj_Orig_Burdened_Cost_ptd Number :=0;
97 Prj_Orig_Burdened_Cost_pp Number :=0;
98 Prj_Orig_Burdened_Cost_ytd Number :=0;
99
100 Prj_Orig_Labor_Hours_itd Number :=0;
101 Prj_Orig_Labor_Hours_ptd Number :=0;
102 Prj_Orig_Labor_Hours_pp Number :=0;
103 Prj_Orig_Labor_Hours_ytd Number :=0;
104
105 Prj_Orig_Quantity_itd Number :=0;
106 Prj_Orig_Quantity_ptd Number :=0;
107 Prj_Orig_Quantity_pp Number :=0;
108 Prj_Orig_Quantity_ytd Number :=0;
109
110 Prj_Orig_Raw_Cost_itd Number :=0;
111 Prj_Orig_Raw_Cost_ptd Number :=0;
112 Prj_Orig_Raw_Cost_pp Number :=0;
113 Prj_Orig_Raw_Cost_ytd Number :=0;
114
115 Prj_Orig_Revenue_itd Number :=0;
116 Prj_Orig_Revenue_ptd Number :=0;
117 Prj_Orig_Revenue_pp Number :=0;
118 Prj_Orig_Revenue_ytd Number :=0;
119
120 Tsk_Base_Burdened_Cost_itd Number :=0;
121 Tsk_Base_Burdened_Cost_ptd Number :=0;
122 Tsk_Base_Burdened_Cost_pp Number :=0;
123 Tsk_Base_Burdened_Cost_ytd Number :=0;
124
125 Tsk_Base_Labor_Hours_itd Number :=0;
126 Tsk_Base_Labor_Hours_ptd Number :=0;
127 Tsk_Base_Labor_Hours_pp Number :=0;
128 Tsk_Base_Labor_Hours_ytd Number :=0;
129
130 Tsk_Base_Raw_Cost_itd Number :=0;
131 Tsk_Base_Raw_Cost_ptd Number :=0;
132 Tsk_Base_Raw_Cost_pp Number :=0;
133 Tsk_Base_Raw_Cost_ytd Number :=0;
134
135 Tsk_Base_Revenue_itd Number :=0;
136 Tsk_Base_Revenue_ptd Number :=0;
137 Tsk_Base_Revenue_pp Number :=0;
138 Tsk_Base_Revenue_ytd Number :=0;
139
140 Tsk_Base_Quantity_itd Number :=0;
141 Tsk_Base_Quantity_ptd Number :=0;
142 Tsk_Base_Quantity_pp Number :=0;
143 Tsk_Base_Quantity_ytd Number :=0;
144
145 Tsk_Orig_Burdened_Cost_itd Number :=0;
146 Tsk_Orig_Burdened_Cost_ptd Number :=0;
147 Tsk_Orig_Burdened_Cost_pp Number :=0;
148 Tsk_Orig_Burdened_Cost_ytd Number :=0;
149
150 Tsk_Orig_Labor_Hours_itd Number :=0;
151 Tsk_Orig_Labor_Hours_ptd Number :=0;
152 Tsk_Orig_Labor_Hours_pp Number :=0;
153 Tsk_Orig_Labor_Hours_ytd Number :=0;
154
155 Tsk_Orig_Quantity_itd Number :=0;
156 Tsk_Orig_Quantity_ptd Number :=0;
157 Tsk_Orig_Quantity_pp Number :=0;
158 Tsk_Orig_Quantity_ytd Number :=0;
159
160 Tsk_Orig_Raw_Cost_itd Number :=0;
161 Tsk_Orig_Raw_Cost_ptd Number :=0;
162 Tsk_Orig_Raw_Cost_pp Number :=0;
163 Tsk_Orig_Raw_Cost_ytd Number :=0;
164
165 Tsk_Orig_Revenue_itd Number :=0;
166 Tsk_Orig_Revenue_ptd Number :=0;
167 Tsk_Orig_Revenue_pp Number :=0;
168 Tsk_Orig_Revenue_ytd Number :=0;
169
170 Tsk_ORIG_REVENUE Number := 0;
171 Tsk_BASE_REVENUE Number := 0;
172 Tsk_ORIG_QUANTITY Number := 0;
173 Tsk_BASE_QUANTITY Number := 0;
174 Tsk_ORIG_RAW_COST Number := 0;
175 Tsk_BASE_RAW_COST Number := 0;
176 Tsk_ORIG_BURDENED_COST Number := 0;
177 Tsk_BASE_BURDENED_COST Number := 0;
178 Tsk_ORIG_LABOR_HOURS Number := 0;
179 Tsk_BASE_LABOR_HOURS Number := 0;
180
181 TOT_ORIG_REVENUE Number := 0;
182 TOT_BASE_REVENUE Number := 0;
183 TOT_ORIG_QUANTITY Number := 0;
184 TOT_BASE_QUANTITY Number := 0;
185 TOT_ORIG_RAW_COST Number := 0;
186 TOT_BASE_RAW_COST Number := 0;
187 TOT_ORIG_BURDENED_COST Number := 0;
188 TOT_BASE_BURDENED_COST Number := 0;
189 TOT_ORIG_LABOR_HOURS Number := 0;
190 TOT_BASE_LABOR_HOURS Number := 0;
191
192 Prj_ORIG_REVENUE Number := 0;
193 Prj_BASE_REVENUE Number := 0;
194 Prj_ORIG_QUANTITY Number := 0;
195 Prj_BASE_QUANTITY Number := 0;
196 Prj_ORIG_RAW_COST Number := 0;
197 Prj_BASE_RAW_COST Number := 0;
198 Prj_ORIG_BURDENED_COST Number := 0;
199 Prj_BASE_BURDENED_COST Number := 0;
200 Prj_ORIG_LABOR_HOURS Number := 0;
201 Prj_BASE_LABOR_HOURS Number := 0;
202
203 Procedure Process_Budget_Txns (X_project_id in Number,
204 X_impl_opt In Varchar2,
205 x_Proj_accum_id in Number,
206 x_Budget_Type_code in Varchar2,
207 x_current_period in Varchar2,
208 x_prev_period in Varchar2,
209 x_current_year in Number,
210 x_prev_accum_period in Varchar2,
211 x_current_start_date In Date,
212 x_current_end_date In Date,
213 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
214 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
215 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
216
217 Procedure Process_Budget_Tot (X_project_id in Number,
218 x_Proj_accum_id in Number,
219 x_Budget_Type_code in Varchar2,
220 x_current_period in Varchar2,
221 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
222 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
223 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
224
225 TYPE task_id_tabtype IS TABLE OF PA_TASKS.TASK_ID%TYPE INDEX BY BINARY_INTEGER;
226
227 Procedure create_accum_budgets
228 (x_project_id In Number,
229 x_task_id In Number,
230 x_budget_type_code In Varchar2,
231 x_current_period In Varchar2,
232 x_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
233 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
234 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
235 x_err_code In Out NOCOPY Number ) ; --File.Sql.39 bug 4440895
236
237 Procedure create_accum_budgets_res
238 (x_project_id In Number,
239 x_task_id In Number,
240 x_resource_list_id in Number,
241 x_resource_list_Member_id in Number,
242 x_resource_id in Number,
243 x_resource_list_assignment_id in Number,
244 x_budget_type_code in Varchar2,
245 x_current_period In Varchar2,
246 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
247 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
248 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
249 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
250
251 Procedure Get_all_higher_tasks_bud (x_project_id in Number,
252 x_task_id in Number,
253 x_resource_list_member_id In Number,
254 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
255 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
256 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
257 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
258 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
259
260 Procedure Add_project_amounts;
261
262 Procedure Add_task_amounts;
263
264 Procedure Initialize_res_level;
265
266 Procedure Initialize_task_level;
267
268 Procedure Initialize_project_level;
269
270
271 --History:
272 -- xx-xxx-xxxx who? - Created
273 --
274 -- 26-SEP-2002 jwhite - Converted to support both r11.5.7 Budget and FP models.
275 -- Added x_fin_plan_type_id
276
277 Procedure Process_all_buds (x_project_id In Number,
278 x_current_period In varchar2,
279 x_task_id In Number,
280 x_resource_list_id In Number,
281 x_resource_list_Member_id In Number,
282 x_resource_id In Number,
283 x_resource_list_assignment_id In Number,
284 x_rollup_qty_flag In Varchar2,
285 x_budget_type_code In Varchar2,
286 x_fin_plan_type_id IN NUMBER,
287 X_Base_Unit_Of_Measure In Varchar2,
288 X_Orig_Unit_Of_Measure In Varchar2,
289 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
290 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
291 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
292 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
293 --History:
294 -- xx-xxx-xxxx who? - Created
295 --
296 -- 26-SEP-2002 jwhite - Converted to support both r11.5.7 Budget and FP models.
297 -- Added x_fin_plan_type_id
298 Procedure Process_bud_code (x_project_id In Number,
299 x_current_period In varchar2,
300 x_task_id In Number,
301 x_resource_list_id In Number,
302 x_resource_list_Member_id In Number,
303 x_resource_id In Number,
304 x_resource_list_assignment_id In Number,
305 x_rollup_qty_flag In Varchar2,
306 x_budget_type_code In Varchar2,
307 x_fin_plan_type_id IN NUMBER,
308 X_Base_Unit_Of_Measure In Varchar2,
309 X_Orig_Unit_Of_Measure In Varchar2,
310 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
311 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
312 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
313 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
314 --History:
315 -- xx-xxx-xxxx who? - Created
316 --
317 -- 26-SEP-2002 jwhite - Converted to support both r11.5.7 Budget and FP models.
318 -- Added x_fin_plan_type_id
319 Procedure Process_all_tasks (x_project_id In Number,
320 x_current_period In varchar2,
321 x_task_id In Number,
322 x_resource_list_id In Number,
323 x_resource_list_Member_id In Number,
324 x_resource_id In Number,
325 x_resource_list_assignment_id In Number,
326 x_rollup_qty_flag In Varchar2,
327 x_budget_type_code In Varchar2,
328 x_fin_plan_type_id IN NUMBER,
329 X_Base_Unit_Of_Measure In Varchar2,
330 X_Orig_Unit_Of_Measure In Varchar2,
331 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
332 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
333 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
334 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
335
336 End ;