[Home] [Help]
PACKAGE BODY: APPS.PA_MAINT_PROJECT_COMMITMENTS
Source
1 PACKAGE body PA_MAINT_PROJECT_COMMITMENTS AS
2 /* $Header: PAACCMTB.pls 120.1 2005/08/19 16:13:47 mwasowic noship $ */
3 TYPE resource_list_id_tabtype IS
4 TABLE OF PA_RESOURCE_LIST_ASSIGNMENTS.RESOURCE_LIST_ID%TYPE
5 INDEX BY BINARY_INTEGER;
6
7 Procedure Process_Txn_Accum_Cmt (X_project_id in Number,
8 X_impl_opt In Varchar2,
9 x_Proj_accum_id in Number,
10 x_current_period in Varchar2,
11 x_prev_period in Varchar2,
12 x_current_year in Number,
13 x_prev_accum_period in Varchar2,
14 x_current_start_date In Date,
15 x_current_end_date In Date,
16 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
17 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
18 x_err_code In Out NOCOPY Number ) Is --File.Sql.39 bug 4440895
19
20 -- This cursor fetches all resource lists assigned to the given project
21 CURSOR Reslist_assgmt_Cur IS
22 SELECT Distinct
23 Resource_list_id
24 FROM
25 PA_RESOURCE_LIST_ASSIGNMENTS
26 Where Project_id = X_project_id;
27
28 x_resource_list_id resource_list_id_tabtype;
29 x_Res_accum_Res_list_id Number := 0;
30 x_Res_accum_txn_accum_id Number := 0;
31
32 -- This cursor fetches all relevant records from PA_RESOURCE_ACCUM_DETAILS
33 -- for the purpose of creating the resource level records in the
34 -- Accumulation tables
35
36 CURSOR Res_accum_Cur IS
37 SELECT
38 Para.RESOURCE_LIST_ASSIGNMENT_ID,
39 Para.RESOURCE_LIST_ID,
40 Para.RESOURCE_LIST_MEMBER_ID,
41 Para.RESOURCE_ID ,
42 Parl.TRACK_AS_LABOR_FLAG,
43 Par.ROLLUP_QUANTITY_FLAG ,
44 Par.UNIT_OF_MEASURE
45 FROM
46 PA_RESOURCE_ACCUM_DETAILS Para,
47 PA_RESOURCES Par,
48 PA_RESOURCE_LIST_MEMBERS Parl
49 WHERE Para.Txn_Accum_id = x_Res_accum_txn_accum_id and
50 Para.Resource_list_id = x_Res_accum_Res_list_id and
51 Para.Resource_list_id = Parl.Resource_list_id and
52 Para.Resource_list_member_id = Parl.Resource_list_member_id and
53 nvl(parl.migration_code,'-99') <> 'N' and
54 Para.Resource_id = Par.Resource_Id ;
55
56 -- This cursor fetches all relevant commitment records from PA_TXN_ACCUM
57 -- table
58
59 CURSOR PA_Txn_Accum_Cur IS
60
61 SELECT DISTINCT
62 PTA.TXN_ACCUM_ID,
63 PTA.TASK_ID,
64 PTA.PA_PERIOD,
65 PTA.GL_PERIOD,
66 NVL(PTA.TOT_CMT_RAW_COST,0) TOT_CMT_RAW_COST,
67 NVL(PTA.TOT_CMT_BURDENED_COST,0) TOT_CMT_BURDENED_COST,
68 NVL(PTA.TOT_CMT_QUANTITY,0) TOT_CMT_QUANTITY,
69 PTA.UNIT_OF_MEASURE,
70 PAP.PERIOD_YEAR
71 FROM
72 PA_TXN_ACCUM PTA,
73 PA_PERIODS_V PAP
74 WHERE PTA.Project_Id = x_project_id
75 AND PTA.CMT_Rollup_flag = 'Y'
76 AND PTA.PA_PERIOD = PAP.PERIOD_NAME
77 AND PAP.PA_END_DATE <= x_current_end_date;
78
79 x_res_list_rec Reslist_assgmt_Cur%ROWTYPE;
80 x_txn_accum_rec PA_Txn_Accum_Cur%ROWTYPE;
81 x_res_accum_rec Res_accum_Cur%ROWTYPE;
82 No_of_res_lists Number := 0;
83 x_recs_processed Number := 0;
84 tot_recs_processed Number := 0;
85 V_Old_Stack Varchar2(630);
86
87 Begin
88
89 V_Old_Stack := x_err_stack;
90 x_err_stack :=
91 x_err_stack ||'->PA_MAINT_PROJECT_COMMITMENTS.Process_Txn_Accum_Cmt';
92 pa_debug.debug(x_err_stack);
93
94 -- This checks for the project level record in the PA_PROJECT_ACCUM_COMMITMENTS
95 -- table . It is possible, that there might be a header record in
96 -- PA_PROJECT_ACCUM_HEADERS, but no corresponding record in any/all of the
97 -- ACTUALS,BUDGETS,COMMITMENT tables.
98
99 PA_ACCUM_UTILS.Check_Cmt_Details
100 (x_project_id,
101 0,
102 0,
103 x_recs_processed,
104 x_err_stack,
105 x_err_stage,
106 x_err_code);
107 tot_recs_processed := tot_recs_processed + x_recs_processed;
108
109 -- This stores all Resource lists assigned to the project in a PL/SQL table
110
111 FOR x_res_list_rec IN Reslist_assgmt_Cur LOOP
112 No_of_res_lists := No_of_res_lists + 1;
113 x_resource_list_id(No_of_res_lists) :=
114 x_res_list_rec.Resource_list_id;
115 END LOOP;
116
117 -- Read all commitment records from PA_TXN_ACCUM
118 -- Fetched period = current period
119 -- (Update only ITD,YTD and PTD figures)-Task level figures without resources
120 FOR x_txn_accum_rec in PA_Txn_Accum_Cur LOOP
121 IF (x_txn_accum_rec.PA_PERIOD = x_current_period ) or
122 (x_txn_accum_rec.GL_PERIOD = x_current_period ) Then
123 PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_pt_tasks_cmt
124 (x_project_id,
125 x_txn_accum_rec.task_id,
126 x_Proj_accum_id,
127 x_current_period,
128 x_txn_accum_rec.TOT_CMT_RAW_COST,
129 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
130 x_txn_accum_rec.TOT_CMT_QUANTITY,
131 x_txn_accum_rec.UNIT_OF_MEASURE,
132 x_recs_processed,
133 x_err_stack,
134 x_err_stage,
135 x_err_code);
136
137 ELSIF
138 -- Fetched period = Previous period
139 (x_txn_accum_rec.PA_PERIOD = x_prev_period )
140 or (x_txn_accum_rec.GL_PERIOD = x_prev_period ) Then
141 -- Fetched period = previous period and fetched year = current year
142 -- (Update only ITD,YTD and PP figures )- Task level figures without resources
143 IF x_txn_accum_rec.PERIOD_YEAR = x_current_year Then
144 PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_pp_tasks_cmt
145 (x_project_id,
146 x_txn_accum_rec.task_id,
147 x_Proj_accum_id,
148 x_current_period,
149 x_txn_accum_rec.TOT_CMT_RAW_COST,
150 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
151 x_txn_accum_rec.TOT_CMT_QUANTITY,
152 x_txn_accum_rec.UNIT_OF_MEASURE,
153 x_recs_processed,
154 x_err_stack,
155 x_err_stage,
156 x_err_code);
157 ELSE
158 -- Fetched period = previous period but fetched year != current year
159 -- (Update only ITD and PP figures )-Task level figures without resources
160 PA_PROCESS_ACCUM_COMMITMENTS.Process_it_pp_tasks_cmt
161 (x_project_id,
162 x_txn_accum_rec.task_id,
163 x_Proj_accum_id,
164 x_current_period,
165 x_txn_accum_rec.TOT_CMT_RAW_COST,
166 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
167 x_txn_accum_rec.TOT_CMT_QUANTITY,
168 x_txn_accum_rec.UNIT_OF_MEASURE,
169 x_recs_processed,
170 x_err_stack,
171 x_err_stage,
172 x_err_code);
173 END IF; -- (IF x_txn_accum_rec.PERIOD_YEAR = x_current_year )
174 ELSE
175 -- Fetched period != current or previous period but fetched year =
176 -- current year
177 -- (Update only ITD and YTD figures)- Task level figures without resources
178 IF x_txn_accum_rec.PERIOD_YEAR = x_current_year Then
179 PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_tasks_cmt
180 (x_project_id,
181 x_txn_accum_rec.task_id,
182 x_Proj_accum_id,
183 x_current_period,
184 x_txn_accum_rec.TOT_CMT_RAW_COST,
185 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
186 x_txn_accum_rec.TOT_CMT_QUANTITY,
187 x_txn_accum_rec.UNIT_OF_MEASURE,
188 x_recs_processed,
189 x_err_stack,
190 x_err_stage,
191 x_err_code);
192
193 ELSE
194 -- Fetched period != current or previous period and fetched year !=
195 -- current year (Update only ITD figures )-
196 -- Task level figures without resources
197 PA_PROCESS_ACCUM_COMMITMENTS.Process_it_tasks_cmt
198 (x_project_id,
199 x_txn_accum_rec.task_id,
200 x_Proj_accum_id,
201 x_current_period,
202 x_txn_accum_rec.TOT_CMT_RAW_COST,
203 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
204 x_txn_accum_rec.TOT_CMT_QUANTITY,
205 x_txn_accum_rec.UNIT_OF_MEASURE,
206 x_recs_processed,
207 x_err_stack,
208 x_err_stage,
209 x_err_code);
210 END IF;
211 END IF;
212 tot_recs_processed := tot_recs_processed + x_recs_processed;
213 -- At this stage , Task level figures have been updated (without resources )
214 -- Now Read all Resource lists and process them
215
216 IF No_of_res_lists > 0 then
217 For i in 1..No_of_res_lists LOOP
218 x_Res_accum_Res_list_id := x_resource_list_id(i);
219 x_Res_accum_txn_accum_id := x_txn_accum_rec.Txn_Accum_Id;
220 For Res_Accum_rec in Res_accum_Cur LOOP
221 -- Fetched period = current period
222 -- (Update only ITD,YTD and PTD figures)-Task level figures with resources
223 -- and Project-Resources
224 IF (x_txn_accum_rec.PA_PERIOD = x_current_period ) or
225 (x_txn_accum_rec.GL_PERIOD = x_current_period ) Then
226 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pt_cmt_res
227 (x_project_id,
228 x_txn_accum_rec.task_id,
229 Res_Accum_Rec.resource_list_id ,
230 Res_Accum_Rec.resource_list_Member_id ,
231 Res_Accum_Rec.resource_id ,
232 Res_Accum_Rec.resource_list_assignment_id ,
233 Res_Accum_Rec.track_as_labor_flag ,
234 Res_Accum_Rec.rollup_Quantity_flag ,
235 Res_Accum_Rec.unit_of_measure ,
236 x_current_period ,
237 x_txn_accum_rec.TOT_CMT_RAW_COST,
238 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
239 x_txn_accum_rec.TOT_CMT_QUANTITY,
240 X_Recs_processed ,
241 x_err_stack,
242 x_err_stage,
243 x_err_code);
244
245 ELSIF
246 -- Fetched period = Previous period
247 (x_txn_accum_rec.PA_PERIOD = x_prev_period )
248 or (x_txn_accum_rec.GL_PERIOD = x_prev_period ) Then
249 -- Fetched period = previous period and fetched year = current year
250 -- (Update only ITD,YTD and PP figures )- Task level figures with resources
251 -- and Project-Resources
252 IF x_txn_accum_rec.PERIOD_YEAR = x_current_year Then
253 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pp_cmt_res
254 (x_project_id,
255 x_txn_accum_rec.task_id,
256 Res_Accum_Rec.resource_list_id ,
257 Res_Accum_Rec.resource_list_Member_id ,
258 Res_Accum_Rec.resource_id ,
259 Res_Accum_Rec.resource_list_assignment_id ,
260 Res_Accum_Rec.track_as_labor_flag ,
261 Res_Accum_Rec.rollup_Quantity_flag ,
262 Res_Accum_Rec.unit_of_measure ,
263 x_current_period ,
264 x_txn_accum_rec.TOT_CMT_RAW_COST,
265 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
266 x_txn_accum_rec.TOT_CMT_QUANTITY,
267 X_Recs_processed ,
268 x_err_stack,
269 x_err_stage,
270 x_err_code);
271 ELSE
272 -- Fetched period = previous period but fetched year != current year
273 -- (Update only ITD and PP figures )-Task level figures with resources
274 -- and Project-Resources
275 PA_PROCESS_ACCUM_CMT_RES.Process_it_pp_cmt_res
276 (x_project_id,
277 x_txn_accum_rec.task_id,
278 Res_Accum_Rec.resource_list_id ,
279 Res_Accum_Rec.resource_list_Member_id ,
280 Res_Accum_Rec.resource_id ,
281 Res_Accum_Rec.resource_list_assignment_id ,
282 Res_Accum_Rec.track_as_labor_flag ,
283 Res_Accum_Rec.rollup_Quantity_flag ,
284 Res_Accum_Rec.unit_of_measure ,
285 x_current_period ,
286 x_txn_accum_rec.TOT_CMT_RAW_COST,
287 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
288 x_txn_accum_rec.TOT_CMT_QUANTITY,
289 X_Recs_processed ,
290 x_err_stack,
291 x_err_stage,
292 x_err_code);
293
294 END IF;
295 ELSE
296 -- Fetched period != current or previous period but fetched year =
297 -- current year
298 -- (Update only ITD and YTD figures)- Task level figures with resources
299 -- and Project-Resources
300 IF x_txn_accum_rec.PERIOD_YEAR = x_current_year Then
301 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_cmt_res
302 (x_project_id,
303 x_txn_accum_rec.task_id,
304 Res_Accum_Rec.resource_list_id ,
305 Res_Accum_Rec.resource_list_Member_id ,
306 Res_Accum_Rec.resource_id ,
307 Res_Accum_Rec.resource_list_assignment_id ,
308 Res_Accum_Rec.track_as_labor_flag ,
309 Res_Accum_Rec.rollup_Quantity_flag ,
310 Res_Accum_Rec.unit_of_measure ,
311 x_current_period ,
312 x_txn_accum_rec.TOT_CMT_RAW_COST,
313 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
314 x_txn_accum_rec.TOT_CMT_QUANTITY,
315 X_Recs_processed ,
316 x_err_stack,
317 x_err_stage,
318 x_err_code);
319
320 ELSE
321 -- Fetched period != current or previous period and fetched year !=
322 -- current year (Update only ITD figures )-
323 -- Task level figures with resources
324 -- and Project-Resources
325 PA_PROCESS_ACCUM_CMT_RES.Process_it_cmt_res
326 (x_project_id,
327 x_txn_accum_rec.task_id,
328 Res_Accum_Rec.resource_list_id ,
329 Res_Accum_Rec.resource_list_Member_id ,
330 Res_Accum_Rec.resource_id ,
331 Res_Accum_Rec.resource_list_assignment_id ,
332 Res_Accum_Rec.track_as_labor_flag ,
333 Res_Accum_Rec.rollup_Quantity_flag ,
334 Res_Accum_Rec.unit_of_measure ,
335 x_current_period ,
336 x_txn_accum_rec.TOT_CMT_RAW_COST,
337 x_txn_accum_rec.TOT_CMT_BURDENED_COST,
338 x_txn_accum_rec.TOT_CMT_QUANTITY,
339 X_Recs_processed ,
340 x_err_stack,
341 x_err_stage,
342 x_err_code);
343
344 END IF;
345 END IF;
346 tot_recs_processed := tot_recs_processed + x_recs_processed;
347 END LOOP; -- (end the For Res_Accum_rec in Res_accum_Cur LOOP)
348 END LOOP; -- (end the For i in 1..No_of_res_lists LOOP )
349 END IF; -- (end the IF No_of_res_lists > 0 IF)
350 -- After processing the record, Update the PA_TXN_ACCUM , modifying the
351 -- CMT_ROLLUP_FLAG as 'Y'
352
353 Update PA_TXN_ACCUM Set
354 last_updated_by = pa_proj_accum_main.x_last_updated_by,
355 last_update_date = SYSDATE,
356 request_id = pa_proj_accum_main.x_request_id,
357 program_application_id = pa_proj_accum_main.x_program_application_id,
358 program_id = pa_proj_accum_main.x_program_id,
359 program_update_date = SYSDATE,
360 CMT_ROLLUP_FLAG = 'N' Where
361 TXN_ACCUM_ID = x_txn_accum_rec.Txn_Accum_Id;
362 tot_recs_processed := tot_recs_processed + 1;
363 tot_recs_processed := tot_recs_processed + x_recs_processed;
364 END LOOP; -- (end the FOR x_txn_accum_rec in PA_Txn_Accum_Cur LOOP )
365
366 -- Restore the old x_err_stack;
367 x_err_stack := V_Old_Stack;
368
369 Exception
370 When Others then
371 x_err_code := SQLCODE;
372 RAISE;
373 End Process_Txn_Accum_Cmt;
374
375 End PA_MAINT_PROJECT_COMMITMENTS;