1 PACKAGE PA_MAINT_PROJECT_ACCUMS AUTHID CURRENT_USER AS
2 /* $Header: PAACACTS.pls 120.1 2005/08/19 16:13:32 mwasowic noship $ */
3 -- This Package consists of the following procedures
4
5 -- Process_Txn_Accum - This reads the PA_TXN_ACCUM and processes the
6 -- transactions
7 -- create_accum_actuals - This creates the header and details records in
8 -- PA_PROJECT_ACCUM_HEADERS and PA_PROJECT_ACCUM_ACTUALS
9 -- Initialize Actuals - this initializes the TO_date numbers in the
10 -- Pa_Project_Accum_Actuals table whenever there is
11 -- a change in the Accumulation period
12 -- Initialize Budgets - this initializes the TO_date numbers in the
13 -- Pa_project_accum_budgets table whenever there is
14 -- a change in the Accumulation period
15 -- Initialize commitments - this initializes the TO_date numbers in the
16 -- Pa_project_accum_commitments table whenever
17 -- there is a change in the Accumulation period
18
19 -- Apr-09-99 Changes made to combine Process_txn_accum and Process_txn_accum_cmt
20 -- procedures and process_txn_accum procedure simplified
21
22 TYPE task_id_tabtype IS TABLE OF PA_TASKS.TASK_ID%TYPE INDEX BY BINARY_INTEGER;
23
24
25 New_raw_cost_itd NUMBER := 0;
26 New_raw_cost_ytd NUMBER := 0;
27 New_raw_cost_ptd NUMBER := 0;
28 New_raw_cost_pp NUMBER := 0;
29
30 New_quantity_itd NUMBER := 0;
31 New_quantity_ytd NUMBER := 0;
32 New_quantity_ptd NUMBER := 0;
33 New_quantity_pp NUMBER := 0;
34
35 New_bill_quantity_itd NUMBER := 0;
36 New_bill_quantity_ytd NUMBER := 0;
37 New_bill_quantity_ptd NUMBER := 0;
38 New_bill_quantity_pp NUMBER := 0;
39
40 New_cmt_raw_cost_itd NUMBER := 0;
41 New_cmt_raw_cost_ytd NUMBER := 0;
42 New_cmt_raw_cost_ptd NUMBER := 0;
43 New_cmt_raw_cost_pp NUMBER := 0;
44
45 New_burd_cost_itd NUMBER := 0;
46 New_burd_cost_ytd NUMBER := 0;
47 New_burd_cost_ptd NUMBER := 0;
48 New_burd_cost_pp NUMBER := 0;
49
50 New_cmt_burd_cost_itd NUMBER := 0;
51 New_cmt_burd_cost_ytd NUMBER := 0;
52 New_cmt_burd_cost_ptd NUMBER := 0;
53 New_cmt_burd_cost_pp NUMBER := 0;
54
55 New_labor_hours_itd NUMBER := 0;
56 New_labor_hours_ytd NUMBER := 0;
57 New_labor_hours_ptd NUMBER := 0;
58 New_labor_hours_pp NUMBER := 0;
59
60 New_revenue_itd NUMBER := 0;
61 New_revenue_ytd NUMBER := 0;
62 New_revenue_ptd NUMBER := 0;
63 New_revenue_pp NUMBER := 0;
64
65 New_bill_raw_cost_itd NUMBER := 0;
66 New_bill_raw_cost_ytd NUMBER := 0;
67 New_bill_raw_cost_ptd NUMBER := 0;
68 New_bill_raw_cost_pp NUMBER := 0;
69
70 New_bill_burd_cost_itd NUMBER := 0;
71 New_bill_burd_cost_ytd NUMBER := 0;
72 New_bill_burd_cost_ptd NUMBER := 0;
73 New_bill_burd_cost_pp NUMBER := 0;
74
75 New_bill_labor_hours_itd NUMBER := 0;
76 New_bill_labor_hours_ytd NUMBER := 0;
77 New_bill_labor_hours_ptd NUMBER := 0;
78 New_bill_labor_hours_pp NUMBER := 0;
79
80 New_cmt_quantity_itd NUMBER := 0;
81 New_cmt_quantity_ytd NUMBER := 0;
82 New_cmt_quantity_ptd NUMBER := 0;
83 New_cmt_quantity_pp NUMBER := 0;
84
85 Prt_raw_cost_itd NUMBER := 0;
86 Prt_raw_cost_ytd NUMBER := 0;
87 Prt_raw_cost_ptd NUMBER := 0;
88 Prt_raw_cost_pp NUMBER := 0;
89
90 Prt_quantity_itd NUMBER := 0;
91 Prt_quantity_ytd NUMBER := 0;
92 Prt_quantity_ptd NUMBER := 0;
93 Prt_quantity_pp NUMBER := 0;
94
95 Prt_bill_quantity_itd NUMBER := 0;
96 Prt_bill_quantity_ytd NUMBER := 0;
97 Prt_bill_quantity_ptd NUMBER := 0;
98 Prt_bill_quantity_pp NUMBER := 0;
99
100 Prt_cmt_raw_cost_itd NUMBER := 0;
101 Prt_cmt_raw_cost_ytd NUMBER := 0;
102 Prt_cmt_raw_cost_ptd NUMBER := 0;
103 Prt_cmt_raw_cost_pp NUMBER := 0;
104
105 Prt_burd_cost_itd NUMBER := 0;
106 Prt_burd_cost_ytd NUMBER := 0;
107 Prt_burd_cost_ptd NUMBER := 0;
108 Prt_burd_cost_pp NUMBER := 0;
109
110 Prt_cmt_burd_cost_itd NUMBER := 0;
111 Prt_cmt_burd_cost_ytd NUMBER := 0;
112 Prt_cmt_burd_cost_ptd NUMBER := 0;
113 Prt_cmt_burd_cost_pp NUMBER := 0;
114
115 Prt_labor_hours_itd NUMBER := 0;
116 Prt_labor_hours_ytd NUMBER := 0;
117 Prt_labor_hours_ptd NUMBER := 0;
118 Prt_labor_hours_pp NUMBER := 0;
119
120 Prt_revenue_itd NUMBER := 0;
121 Prt_revenue_ytd NUMBER := 0;
122 Prt_revenue_ptd NUMBER := 0;
123 Prt_revenue_pp NUMBER := 0;
124
125 Prt_bill_raw_cost_itd NUMBER := 0;
126 Prt_bill_raw_cost_ytd NUMBER := 0;
127 Prt_bill_raw_cost_ptd NUMBER := 0;
128 Prt_bill_raw_cost_pp NUMBER := 0;
129
130 Prt_bill_burd_cost_itd NUMBER := 0;
131 Prt_bill_burd_cost_ytd NUMBER := 0;
132 Prt_bill_burd_cost_ptd NUMBER := 0;
133 Prt_bill_burd_cost_pp NUMBER := 0;
134
135 Prt_bill_labor_hours_itd NUMBER := 0;
136 Prt_bill_labor_hours_ytd NUMBER := 0;
137 Prt_bill_labor_hours_ptd NUMBER := 0;
138 Prt_bill_labor_hours_pp NUMBER := 0;
139
140 Prt_cmt_quantity_itd NUMBER := 0;
141 Prt_cmt_quantity_ytd NUMBER := 0;
142 Prt_cmt_quantity_ptd NUMBER := 0;
143 Prt_cmt_quantity_pp NUMBER := 0;
144
145 Tsk_raw_cost_itd NUMBER := 0;
146 Tsk_raw_cost_ytd NUMBER := 0;
147 Tsk_raw_cost_ptd NUMBER := 0;
148 Tsk_raw_cost_pp NUMBER := 0;
149
150 Tsk_quantity_itd NUMBER := 0;
151 Tsk_quantity_ytd NUMBER := 0;
152 Tsk_quantity_ptd NUMBER := 0;
153 Tsk_quantity_pp NUMBER := 0;
154
155 Tsk_bill_quantity_itd NUMBER := 0;
156 Tsk_bill_quantity_ytd NUMBER := 0;
157 Tsk_bill_quantity_ptd NUMBER := 0;
158 Tsk_bill_quantity_pp NUMBER := 0;
159
160 Tsk_cmt_raw_cost_itd NUMBER := 0;
161 Tsk_cmt_raw_cost_ytd NUMBER := 0;
162 Tsk_cmt_raw_cost_ptd NUMBER := 0;
163 Tsk_cmt_raw_cost_pp NUMBER := 0;
164
165 Tsk_burd_cost_itd NUMBER := 0;
166 Tsk_burd_cost_ytd NUMBER := 0;
167 Tsk_burd_cost_ptd NUMBER := 0;
168 Tsk_burd_cost_pp NUMBER := 0;
169
170 Tsk_cmt_burd_cost_itd NUMBER := 0;
171 Tsk_cmt_burd_cost_ytd NUMBER := 0;
172 Tsk_cmt_burd_cost_ptd NUMBER := 0;
173 Tsk_cmt_burd_cost_pp NUMBER := 0;
174
175 Tsk_labor_hours_itd NUMBER := 0;
176 Tsk_labor_hours_ytd NUMBER := 0;
177 Tsk_labor_hours_ptd NUMBER := 0;
178 Tsk_labor_hours_pp NUMBER := 0;
179
180 Tsk_revenue_itd NUMBER := 0;
181 Tsk_revenue_ytd NUMBER := 0;
182 Tsk_revenue_ptd NUMBER := 0;
183 Tsk_revenue_pp NUMBER := 0;
184
185 Tsk_bill_raw_cost_itd NUMBER := 0;
186 Tsk_bill_raw_cost_ytd NUMBER := 0;
187 Tsk_bill_raw_cost_ptd NUMBER := 0;
188 Tsk_bill_raw_cost_pp NUMBER := 0;
189
190 Tsk_bill_burd_cost_itd NUMBER := 0;
191 Tsk_bill_burd_cost_ytd NUMBER := 0;
192 Tsk_bill_burd_cost_ptd NUMBER := 0;
193 Tsk_bill_burd_cost_pp NUMBER := 0;
194
195 Tsk_bill_labor_hours_itd NUMBER := 0;
196 Tsk_bill_labor_hours_ytd NUMBER := 0;
197 Tsk_bill_labor_hours_ptd NUMBER := 0;
198 Tsk_bill_labor_hours_pp NUMBER := 0;
199
200 Tsk_cmt_quantity_itd NUMBER := 0;
201 Tsk_cmt_quantity_ytd NUMBER := 0;
202 Tsk_cmt_quantity_ptd NUMBER := 0;
203 Tsk_cmt_quantity_pp NUMBER := 0;
204
205 Procedure Process_Txn_Accum (x_project_id in Number,
206 x_impl_opt In Varchar2,
207 x_Proj_accum_id in Number,
208 x_current_period in Varchar2,
209 x_prev_period in Varchar2,
210 x_current_year in Number,
211 x_prev_accum_period in Varchar2,
212 x_current_start_date In Date,
213 x_current_end_date In Date,
214 x_actual_cost_flag In Varchar2,
215 x_revenue_flag In Varchar2,
216 x_commitments_flag In Varchar2,
217 x_resource_list_id In number,
218 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
219 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
220 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
221
222 Procedure Check_Accum_Res_Tasks( x_project_id In Number,
223 x_task_id In Number,
224 x_proj_accum_id IN Number,
225 x_current_period IN VARCHAR2,
226 x_resource_list_id in Number,
227 x_resource_list_Member_id in Number,
228 x_resource_id in Number,
229 x_resource_list_assignment_id in Number,
230 x_create_actuals IN Varchar2,
231 x_create_commit IN Varchar2,
232 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
233 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
234 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
235 Procedure Check_Accum_wbs ( x_project_id In Number,
236 x_task_id In Number,
237 x_proj_accum_id IN Number,
238 x_current_period IN VARCHAR2,
239 x_resource_list_id in Number,
240 x_resource_list_Member_id in Number,
241 x_resource_id in Number,
242 x_resource_list_assignment_id in Number,
243 x_create_wbs_actuals IN Varchar2,
244 x_create_wbs_commit IN Varchar2,
245 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
246 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
247 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
248 Procedure create_accum_actuals
249 (x_project_id In Number,
250 x_task_id In Number,
251 x_current_period In Varchar2,
252 x_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
253 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
254 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
255 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
256
257 Procedure create_accum_actuals_res
258 (x_project_id In Number,
259 x_task_id In Number,
260 x_resource_list_id in Number,
261 x_resource_list_Member_id in Number,
262 x_resource_id in Number,
263 x_resource_list_assignment_id in Number,
264 x_current_period In Varchar2,
265 x_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
266 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
267 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
268 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
269
270 Procedure Get_all_higher_tasks
271 (x_project_id in Number,
272 x_task_id in Number,
273 x_resource_list_member_id in Number,
274 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
275 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
276 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
277 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
278 x_err_Code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
279
280 Procedure Insert_Headers_tasks
281 (x_project_id In Number,
282 x_task_id In Number,
283 x_current_period In Varchar2,
284 x_accum_id In Number,
285 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
286 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
287 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
288
289 Procedure Initialize_actuals
290 (x_project_id In Number,
291 x_accum_id In Number,
292 x_impl_opt In Varchar2,
293 x_current_period In Varchar2,
294 x_prev_period In Varchar2,
295 x_prev_Accum_period In Varchar2,
296 x_current_year In Number,
297 x_prev_year In Number,
298 x_prev_accum_year In Number,
299 x_current_start_date In Date,
300 x_current_end_date In Date,
301 x_prev_start_date In Date,
302 x_prev_end_date In Date,
303 x_prev_accum_start_date In Date,
304 x_prev_accum_end_date In Date,
305 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
306 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
307 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
308
309 Procedure Initialize_budgets
310 (x_project_id In Number,
311 x_accum_id In Number,
312 x_impl_opt In Varchar2,
313 x_budget_type In Varchar2,
314 x_current_period In Varchar2,
315 x_prev_period In Varchar2,
316 x_prev_accum_period In Varchar2,
317 x_current_year In Number,
318 x_prev_year In Number,
319 x_prev_accum_year In Number,
320 x_current_start_date In Date,
321 x_current_end_date In Date,
322 x_prev_start_date In Date,
323 x_prev_end_date In Date,
324 x_prev_accum_start_date In Date,
325 x_prev_accum_end_date In Date,
326 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
327 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
328 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
329
330 Procedure initialize_commitments
331 (x_project_id In Number,
332 x_accum_id In Number,
333 x_impl_opt In Varchar2,
334 x_current_period In Varchar2,
335 x_prev_period In Varchar2,
336 x_prev_accum_period In Varchar2,
337 x_current_year In Number,
338 x_prev_year In Number,
339 x_prev_accum_year In Number,
340 x_current_start_date In Date,
344 x_prev_accum_start_date In Date,
341 x_current_end_date In Date,
342 x_prev_start_date In Date,
343 x_prev_end_date In Date,
345 x_prev_accum_end_date In Date,
346 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
347 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
348 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
349
350 Procedure Initialize_task_level;
351
352 Procedure Initialize_parent_level;
353
354 Procedure Initialize_project_level;
355
356 Procedure Add_Parent_amounts;
357
358 Procedure Add_Project_amounts;
359
360 End ;