[Home] [Help]
PACKAGE BODY: APPS.PA_GENERATE_FORECAST_PUB
Source
1 PACKAGE BODY PA_GENERATE_FORECAST_PUB AS
2 /* $Header: PARRFGPB.pls 120.3 2006/03/22 20:40:29 nkumbi noship $ */
3
4 FUNCTION Get_Person_Id(p_res_id NUMBER)
5 RETURN NUMBER IS
6 x_person_id NUMBER;
7 BEGIN
8 SELECT person_id INTO x_person_id FROM
9 PA_RESOURCE_TXN_ATTRIBUTES WHERE
10 RESOURCE_ID = p_res_id;
11 RETURN x_person_id;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 RETURN NULL;
15 WHEN OTHERS THEN
16 RETURN NULL;
17 END;
18
19 PROCEDURE UPDATE_BUDG_VERSION(p_budget_version_id IN NUMBER ) IS
20 BEGIN
21 UPDATE PA_BUDGET_VERSIONS SET PLAN_PROCESSING_CODE = 'E'
22 WHERE
23 BUDGET_VERSION_ID = p_budget_version_id;
24 COMMIT;
25 END;
26
27 --History:
28 -- 23-Mar-06 nkumbi Stubbed out the procedure as PAWFGPF workflow is obsolete in R12
29 PROCEDURE Submit_Project_Forecast(p_project_id IN NUMBER,
30 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_msg_data OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
33 BEGIN
34 NULL;
35 END Submit_Project_Forecast;
36
37 PROCEDURE Set_Error_Details(p_return_status IN VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
40 x_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
41 x_msg_index_out OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(2000);
44 l_data VARCHAR2(2000);
45 l_msg_index_out NUMBER;
46
47 BEGIN
48 PA_DEBUG.set_err_stack('Set_Error_Details');
49 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
50 IF fnd_msg_pub.count_msg = 1 THEN
51 PA_INTERFACE_UTILS_PUB.Get_Messages (
52 p_encoded => FND_API.G_TRUE,
53 p_msg_index => 1,
54 p_msg_count => 1 ,
55 p_msg_data => l_msg_data ,
56 p_data => l_data,
57 p_msg_index_out => l_msg_index_out );
58 x_msg_data := l_data;
59 x_msg_count := 1;
60 ELSE
61 x_msg_count := fnd_msg_pub.count_msg;
62 END IF;
63 END IF;
64 PA_DEBUG.reset_err_stack;
65 RETURN;
66 EXCEPTION
67 WHEN OTHERS THEN
68 RAISE;
69 END Set_Error_Details;
70
71
72 PROCEDURE Maintain_Budget_Version(p_project_id IN NUMBER,
73 p_plan_processing_code IN VARCHAR2,
74 x_budget_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
75 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
76 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
77 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
78 CURSOR BUDGET_VERSION IS
79 SELECT BUDGET_VERSION_ID, PLAN_PROCESSING_CODE
80 FROM PA_BUDGET_VERSIONS
81 WHERE PROJECT_ID = p_project_id AND
82 BUDGET_TYPE_CODE = 'FORECASTING_BUDGET_TYPE';
83 l_ret_status VARCHAR2(100);
84 l_created_by NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_created_by;
85 l_request_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_request_id;
86 l_program_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_program_id;
87 l_program_application_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_program_application_id;
88 l_creation_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_creation_date;
89 l_program_update_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
90
91 l_fcst_def_bem PA_BUDGET_VERSIONS.BUDGET_ENTRY_METHOD_CODE%TYPE;
92 l_fcst_res_list PA_RESOURCE_LISTS_ALL_BG.RESOURCE_LIST_ID%TYPE;
93 l_fcst_period_type VARCHAR2(30);
94
95 l_plan_processing_code PA_BUDGET_VERSIONS.PLAN_PROCESSING_CODE%TYPE;
96 l_rowid ROWID;
97 l_msg_data VARCHAR2(2000);
98 l_data VARCHAR2(2000);
99 l_msg_index_out NUMBER:=0;
100 l_msg_count NUMBER;
101
102
103 BEGIN
104 PA_DEBUG.set_err_stack('Maintain_Budget_Version');
105 l_ret_status := FND_API.G_RET_STS_SUCCESS;
106
107 PA_FORECAST_GLOBAL.Initialize_Global(
108 x_msg_count => x_msg_count,
109 x_msg_data => l_msg_data,
110 x_ret_status => l_ret_status );
111 -- l_msg_count := x_msg_count;
112 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
113
114 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
115 p_return_status => l_ret_status,
116 x_msg_count => l_msg_count,
117 x_msg_data => l_msg_data,
118 x_data => l_data,
119 x_msg_index_out => l_msg_index_out );
120
121 x_msg_count := l_msg_count;
122 x_msg_data := l_msg_data;
123 x_return_status := l_ret_status;
124 PA_DEBUG.reset_err_stack;
125 RETURN;
126 END IF;
127
128 l_fcst_def_bem := PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_def_bem;
129 l_fcst_res_list:= PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_res_list;
130 l_fcst_period_type:=PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_period_type;
131
132 l_created_by := PA_FORECAST_GLOBAL.G_who_columns.G_created_by;
133 l_request_id := PA_FORECAST_GLOBAL.G_who_columns.G_request_id;
134 l_program_id := PA_FORECAST_GLOBAL.G_who_columns.G_program_id;
135 l_program_application_id := PA_FORECAST_GLOBAL.G_who_columns.G_program_application_id;
136 l_creation_date := PA_FORECAST_GLOBAL.G_who_columns.G_creation_date;
137 l_program_update_date := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
138
139 OPEN BUDGET_VERSION;
140 FETCH BUDGET_VERSION INTO
141 x_budget_version_id,
142 l_plan_processing_code;
143 IF BUDGET_VERSION%NOTFOUND THEN
144 PA_DEBUG.g_err_stage := '630: before calling PA_BUDGET_VERSIONS_PKG.INSERT_ROW';
145 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
146 PA_BUDGET_VERSIONS_PKG.Insert_Row(
147 X_ROWID => l_rowid,
148 X_BUDGET_VERSION_ID => x_budget_version_id,
149 X_PROJECT_ID => p_project_id,
150 X_BUDGET_TYPE_CODE => 'FORECASTING_BUDGET_TYPE',
151 X_VERSION_NUMBER => 1,
152 X_BUDGET_STATUS_CODE => 'W',
153 X_LAST_UPDATE_DATE => l_program_update_date,
154 X_LAST_UPDATED_BY => l_created_by,
155 X_CREATION_DATE => l_creation_date,
156 X_CREATED_BY => l_created_by,
157 X_LAST_UPDATE_LOGIN => l_request_id,
158 X_CURRENT_FLAG => 'X',
159 X_ORIGINAL_FLAG => 'X',
160 X_CURRENT_ORIGINAL_FLAG => 'X',
161 X_RESOURCE_ACCUMULATED_FLAG => 'X',
162 X_RESOURCE_LIST_ID => l_fcst_res_list,
163 X_VERSION_NAME => NULL,
164 X_BUDGET_ENTRY_METHOD_CODE => l_fcst_def_bem,
165 X_BASELINED_BY_PERSON_ID => NULL,
166 X_BASELINED_DATE => NULL,
167 X_CHANGE_REASON_CODE => NULL,
168 X_LABOR_QUANTITY => 0,
169 X_LABOR_UNIT_OF_MEASURE => 0,
170 X_RAW_COST => 0,
171 X_BURDENED_COST => 0,
172 X_REVENUE => 0,
173 X_DESCRIPTION => NULL,
174 X_ATTRIBUTE_CATEGORY => NULL,
175 X_ATTRIBUTE1 => NULL,
176 X_ATTRIBUTE2 => NULL,
177 X_ATTRIBUTE3 => NULL,
178 X_ATTRIBUTE4 => NULL,
179 X_ATTRIBUTE5 => NULL,
180 X_ATTRIBUTE6 => NULL,
181 X_ATTRIBUTE7 => NULL,
182 X_ATTRIBUTE8 => NULL,
183 X_ATTRIBUTE9 => NULL,
184 X_ATTRIBUTE10 => NULL,
185 X_ATTRIBUTE11 => NULL,
186 X_ATTRIBUTE12 => NULL,
187 X_ATTRIBUTE13 => NULL,
188 X_ATTRIBUTE14 => NULL,
189 X_ATTRIBUTE15 => NULL,
190 X_FIRST_BUDGET_PERIOD => NULL,
191 X_PM_PRODUCT_CODE => NULL,
192 X_PM_BUDGET_REFERENCE => NULL,
193 X_WF_STATUS_CODE => NULL,
194 X_PLAN_PROCESSING_CODE => p_plan_processing_code);
195 PA_DEBUG.g_err_stage := '660: after calling PA_BUDGET_VERSIONS_PKG.INSERT_ROW';
196 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
197 ELSE
198 IF l_plan_processing_code = 'P' THEN
199 l_ret_status := FND_API.G_RET_STS_ERROR;
200 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
201 p_msg_name => 'PA_FCST_IN_PROCESS');
202
203 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
204 p_return_status => l_ret_status,
205 x_msg_count => l_msg_count,
206 x_msg_data => l_msg_data,
207 x_data => l_data,
208 x_msg_index_out => l_msg_index_out );
209
210 x_msg_count := l_msg_count;
211 x_msg_data := l_msg_data;
212 x_return_status := l_ret_status;
213 CLOSE BUDGET_VERSION;
214 PA_DEBUG.reset_err_stack;
215 RETURN;
216 END IF;
217 PA_DEBUG.g_err_stage := '680: before updating PA_BUDGET_VERSIONS';
218 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
219
220 UPDATE PA_BUDGET_VERSIONS SET
221 PLAN_PROCESSING_CODE = p_plan_processing_code,
222 BUDGET_ENTRY_METHOD_CODE = l_fcst_def_bem
223 WHERE
224 BUDGET_VERSION_ID = x_budget_version_id;
225
226 PA_DEBUG.g_err_stage := '690: after updating PA_BUDGET_VERSIONS';
227 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
228
229 END IF;
230 CLOSE BUDGET_VERSION;
231 x_return_status := l_ret_status;
232 PA_DEBUG.reset_err_stack;
233 RETURN;
234 EXCEPTION
235 WHEN OTHERS THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 RAISE;
238 END Maintain_Budget_Version;
239
240
241 PROCEDURE Generate_Forecast(p_project_id IN NUMBER
242 ,p_debug_mode IN VARCHAR2
243 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
244 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
245 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
246
247 l_budget_line_id PA_BUDGET_LINES.BUDGET_LINE_ID%type; /* FPB2 */
248 CURSOR PROJ_DETAILS IS
249 SELECT P.PROJECT_TYPE,P.PROJECT_CURRENCY_CODE,P.CARRYING_OUT_ORGANIZATION_ID,
250 P.PROJECT_VALUE, P.JOB_BILL_RATE_SCHEDULE_ID, P.EMP_BILL_RATE_SCHEDULE_ID,
251 P.DISTRIBUTION_RULE,P.BILL_JOB_GROUP_ID,NVL(P.ORG_ID,-99),P.COMPLETION_DATE,
252 NVL(P.TEMPLATE_FLAG,'N'),
253 P.PROJFUNC_CURRENCY_CODE,
254 P.PROJFUNC_BIL_RATE_DATE_CODE,
255 P.PROJFUNC_BIL_RATE_TYPE,
256 P.PROJFUNC_BIL_RATE_DATE,
257 P.PROJFUNC_BIL_EXCHANGE_RATE,
258 P.COST_JOB_GROUP_ID,
259 P.PROJECT_RATE_DATE,
260 P.PROJECT_RATE_TYPE,
261 P.PROJECT_BIL_RATE_DATE_CODE,
262 P.PROJECT_BIL_RATE_TYPE,
263 P.PROJECT_BIL_RATE_DATE,
264 P.PROJECT_BIL_EXCHANGE_RATE,
265 P.PROJFUNC_COST_RATE_TYPE,
266 P.PROJFUNC_COST_RATE_DATE,
267 P.LABOR_TP_SCHEDULE_ID,
268 P.LABOR_TP_FIXED_DATE,
269 P.LABOR_SCHEDULE_DISCOUNT,
270 NVL(P.ASSIGN_PRECEDES_TASK,'N'),
271 NVL(P.LABOR_BILL_RATE_ORG_ID,-99),
272 P.LABOR_STD_BILL_RATE_SCHDL,
273 P.LABOR_SCHEDULE_FIXED_DATE,
274 P.LABOR_SCH_TYPE
275 FROM
276 PA_PROJECTS_ALL P WHERE PROJECT_ID = P_PROJECT_ID;
277
278 CURSOR PROJ_ASSIGNMENTS IS
279 SELECT PA.ASSIGNMENT_ID, PA.START_DATE,PA.RESOURCE_ID,PA.PROJECT_ROLE_ID,
280 PA.FCST_JOB_ID,PA.FCST_JOB_GROUP_ID,PR.MEANING,
281 PA.ASSIGNMENT_TYPE ,
282 PA.EXPENDITURE_ORGANIZATION_ID,
283 PA.EXPENDITURE_TYPE,
284 PA.REVENUE_BILL_RATE,
285 PA.EXPENDITURE_ORG_ID,
286 PA.STATUS_CODE,
287 WB.BILLABLE_CAPITALIZABLE_FLAG --Added for the bug 2420564
288 FROM
289 PA_PROJECT_ASSIGNMENTS PA,
290 PA_WORK_TYPES_B WB,
291 PA_PROJECT_ROLE_TYPES PR
292 WHERE
293 PA.PROJECT_ID = p_project_id AND
294 PA.PROJECT_ROLE_ID = PR.PROJECT_ROLE_ID AND
295 WB.WORK_TYPE_ID = PA.WORK_TYPE_ID(+); --Added for the bug 2420564
296
297 CURSOR FCST_PA(p_prj_assignment_id NUMBER) IS
298 SELECT FI.EXPENDITURE_ORG_ID,FI.EXPENDITURE_ORGANIZATION_ID,
299 FI.RCVR_PA_PERIOD_NAME,
300 P.START_DATE,P.END_DATE,SUM(FI.ITEM_QUANTITY),
301 MIN(FI.FORECAST_ITEM_ID)
302 FROM
303 PA_FORECAST_ITEMS FI,
304 PA_FORECAST_ITEM_DETAILS FID,
305 PA_PERIODS_ALL P
306 WHERE
307 FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99) AND
308 P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME AND
309 FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID AND
310 FID.FORECAST_SUMMARIZED_CODE = 'N' AND
311 FID.NET_ZERO_FLAG = 'N' AND
312 FI.ERROR_FLAG = 'N' AND
313 FI.DELETE_FLAG = 'N' AND
314 ASSIGNMENT_ID = p_prj_assignment_id AND
315 FI.EXPENDITURE_ORG_ID <> -88 /* Added this condition for bug 3151420 */
316 GROUP BY
317 FI.EXPENDITURE_ORG_ID,FI.EXPENDITURE_ORGANIZATION_ID,
318 P.START_DATE,P.END_DATE,FI.RCVR_PA_PERIOD_NAME;
319
320 CURSOR FCST_GL(p_prj_assignment_id NUMBER) IS
321 SELECT FI.EXPENDITURE_ORG_ID, FI.EXPENDITURE_ORGANIZATION_ID,
322 FI.RCVR_GL_PERIOD_NAME,
323 GLP.START_DATE, GLP.END_DATE,SUM(FI.ITEM_QUANTITY),
324 MIN(FI.FORECAST_ITEM_ID)
325 FROM
326 PA_FORECAST_ITEMS FI,
327 PA_FORECAST_ITEM_DETAILS FID,
328 GL_PERIODS GLP, /* Added the ending comma for Bug 3512491 */
329 PA_IMPLEMENTATIONS IMP, /* Added the table for Bug 3512491 */
330 GL_SETS_OF_BOOKS SOB /* Added the table for Bug 3512491 */
331 WHERE
332 FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID AND
333 FID.FORECAST_SUMMARIZED_CODE = 'N' AND
334 FID.NET_ZERO_FLAG = 'N' AND
335 FI.ERROR_FLAG = 'N' AND
336 FI.DELETE_FLAG = 'N' AND
337 SOB.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID AND /* Added the join for Bug 3512491 */
338 GLP.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME AND /* Modified the join for Bug 3512491 */
339 GLP.PERIOD_NAME = FI.RCVR_GL_PERIOD_NAME AND
340 ASSIGNMENT_ID = p_prj_assignment_id AND
341 FI.EXPENDITURE_ORG_ID <> -88 /* Added this condition for bug 3151420 */
342 GROUP BY
343 FI.EXPENDITURE_ORG_ID, FI.EXPENDITURE_ORGANIZATION_ID,
344 GLP.START_DATE, GLP.END_DATE, FI.RCVR_GL_PERIOD_NAME;
345
346 CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
347 c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
348 c_resource_assignment_id
349 PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
350 SELECT BL.PERIOD_NAME,BL.START_DATE,
351 BL.BURDENED_COST FROM PA_BUDGET_LINES BL,
352 PA_RESOURCE_ASSIGNMENTS RA WHERE
353 BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID AND
354 RA.BUDGET_VERSION_ID = c_budget_version_id AND
355 RA.PROJECT_ID = c_project_id AND
356 RA.RESOURCE_LIST_MEMBER_ID = 103
357 ORDER BY BL.START_DATE;
358
359 l_carrying_out_organization_id PA_PROJECTS_ALL.CARRYING_OUT_ORGANIZATION_ID%TYPE;
360 l_project_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
361 l_projfunc_currency_code PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE;
362 l_project_value PA_PROJECTS_ALL.PROJECT_VALUE%TYPE;
363 l_job_bill_rate_schedule_id PA_PROJECTS_ALL.JOB_BILL_RATE_SCHEDULE_ID%TYPE;
364 l_emp_bill_rate_schedule_id PA_PROJECTS_ALL.EMP_BILL_RATE_SCHEDULE_ID%TYPE;
365 l_rev_gen_method VARCHAR2(3);
366 l_distribution_rule PA_PROJECTS_ALL.DISTRIBUTION_RULE%TYPE;
367 l_project_type PA_PROJECTS_ALL.PROJECT_TYPE%TYPE;
368 l_bill_job_group_id PA_PROJECTS_ALL.BILL_JOB_GROUP_ID%TYPE;
369 l_org_id PA_PROJECTS_ALL.ORG_ID%TYPE;
370 l_completion_date PA_PROJECTS_ALL.COMPLETION_DATE%TYPE;
371 l_template_flag PA_PROJECTS_ALL.TEMPLATE_FLAG%TYPE;
372 l_projfunc_bil_rate_date_code PA_PROJECTS_ALL.PROJECT_BIL_RATE_DATE_CODE%TYPE;
373 l_projfunc_bil_rate_type PA_PROJECTS_ALL.PROJECT_BIL_RATE_TYPE%TYPE;
374 l_projfunc_bil_rate_date PA_PROJECTS_ALL.PROJECT_BIL_RATE_DATE%TYPE;
375 l_projfunc_bil_exchange_rate PA_PROJECTS_ALL.PROJECT_BIL_EXCHANGE_RATE%TYPE;
376
377 l_system_linkage Pa_Forecast_Items.EXPENDITURE_TYPE_CLASS%TYPE;
378 /* Added for Org Forecasting */
379
380 l_cost_job_group_id Pa_Projects_All.Cost_Job_Group_Id%TYPE;
381 l_prj_rate_date Pa_Projects_All.PROJECT_RATE_DATE%TYPE;
382 l_prj_rate_type Pa_Projects_All.PROJECT_RATE_TYPE%TYPE;
383 l_prj_bil_rate_date_code Pa_Projects_All.PROJECT_BIL_RATE_DATE_CODE%TYPE;
384 l_prj_bil_rate_type Pa_Projects_All.PROJECT_BIL_RATE_TYPE%TYPE;
385 l_prj_bil_rate_date Pa_Projects_All.PROJECT_BIL_RATE_DATE%TYPE;
386 l_prj_bil_ex_rate Pa_Projects_All.PROJECT_BIL_EXCHANGE_RATE%TYPE;
387 l_prjfunc_cost_rate_type Pa_Projects_All.PROJFUNC_COST_RATE_TYPE%TYPE;
388 l_prjfunc_cost_rate_date Pa_Projects_All.PROJFUNC_COST_RATE_DATE%TYPE;
389 l_labor_tp_schedule_id Pa_Projects_All.LABOR_TP_SCHEDULE_ID%TYPE;
390 l_labor_tp_fixed_date Pa_Projects_All.LABOR_TP_FIXED_DATE%TYPE;
391
392 l_labor_sch_discount Pa_Projects_All.LABOR_SCHEDULE_DISCOUNT%TYPE;
393 l_asg_precedes_task Pa_Projects_All.ASSIGN_PRECEDES_TASK%TYPE;
394 l_labor_bill_rate_orgid Pa_Projects_All.LABOR_BILL_RATE_ORG_ID%TYPE;
395 l_labor_std_bill_rate_sch Pa_Projects_All.LABOR_STD_BILL_RATE_SCHDL%TYPE;
396 l_labor_sch_fixed_dt Pa_Projects_All.LABOR_SCHEDULE_FIXED_DATE%TYPE;
397 l_labor_sch_type Pa_Projects_All.LABOR_SCH_TYPE%TYPE;
398
399 l_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
400 l_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
401 l_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
402 l_rt_pfunc_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
403
404 l_rt_system_linkage_tab PA_PLSQL_DATATYPES.Char30TabTyp;
405
406
407 lx_rt_pfunc_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
408 lx_rt_pfunc_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
409 lx_rt_pfunc_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
410
411 l_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
412 l_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
413
414 lx_rt_pfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
415 lx_rt_pfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
416 lx_rt_pfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
417
418
419 l_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
420 l_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
421 l_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
422 l_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
423 l_rt_proj_rev_rt_dt_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
424 l_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
425
426 lx_rt_proj_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
427 lx_rt_proj_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
428 lx_rt_proj_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
429 lx_rt_proj_rev_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
430 lx_rt_proj_rev_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
431 lx_rt_proj_rev_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
432
433 lx_rt_proj_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
434 lx_rt_proj_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
435 lx_rt_proj_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
436 lx_rt_proj_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
437 lx_rt_proj_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
438 lx_rt_proj_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
439
440 lx_rt_expfunc_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
441 lx_rt_expfunc_cost_rt_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
442 lx_rt_expfunc_cost_rt_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
443 lx_rt_expfunc_cost_ex_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
444
445 lx_rt_cost_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
446 lx_rt_rev_txn_curr_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
447 lx_rt_txn_rev_bill_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
448 lx_rt_txn_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
449 lx_rt_txn_raw_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp ;
450 lx_rt_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
451 lx_rt_txn_bd_cost_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
452 lx_rt_txn_bd_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
453
454
455 /* Added for Org Forecasting */
456
457 l_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
458 l_version_number PA_BUDGET_VERSIONS.VERSION_NUMBER%TYPE;
459 l_plan_processing_code PA_BUDGET_VERSIONS.PLAN_PROCESSING_CODE%TYPE;
460
461 l_prj_assignment_id PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_ID%TYPE;
462 l_prj_start_date PA_PROJECT_ASSIGNMENTS.START_DATE%TYPE;
463 l_prj_resource_id PA_PROJECT_ASSIGNMENTS.RESOURCE_ID%TYPE;
464 l_prj_project_role_id PA_PROJECT_ASSIGNMENTS.PROJECT_ROLE_ID%TYPE;
465 l_prj_fcst_job_id PA_PROJECT_ASSIGNMENTS.FCST_JOB_ID%TYPE;
466 l_prj_fcst_job_group_id PA_PROJECT_ASSIGNMENTS.FCST_JOB_GROUP_ID%TYPE;
467 l_prj_meaning PA_PROJECT_ROLE_TYPES.MEANING%TYPE;
468 l_prj_assignment_type PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE;
469 l_prj_exp_org_id PA_PROJECT_ASSIGNMENTS.EXPENDITURE_ORG_ID%TYPE;
470 l_prj_exp_organization_id PA_PROJECT_ASSIGNMENTS.EXPENDITURE_ORGANIZATION_ID%TYPE;
471 l_prj_expenditure_org_id PA_PROJECT_ASSIGNMENTS.EXPENDITURE_ORG_ID%TYPE;
472 l_prj_exp_type PA_PROJECT_ASSIGNMENTS.EXPENDITURE_TYPE%TYPE;
473 l_prj_person_id PA_PROJECT_ASSIGNMENTS.RESOURCE_ID%TYPE;
474 l_prj_revenue_bill_rate PA_PROJECT_ASSIGNMENTS.REVENUE_BILL_RATE%TYPE;
475 l_prj_short_assignment_type PA_PROJECT_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE;
476 l_prj_status_code PA_PROJECT_ASSIGNMENTS.STATUS_CODE%TYPE;
477
478
479
480
481 l_fcst_def_bem PA_BUDGET_VERSIONS.BUDGET_ENTRY_METHOD_CODE%TYPE;
482 l_fcst_res_list PA_RESOURCE_LISTS_ALL_BG.RESOURCE_LIST_ID%TYPE;
483 l_fcst_period_type VARCHAR2(30);
484
485 l_fcst_exp_org_id PA_FORECAST_ITEMS.EXPENDITURE_ORGANIZATION_ID%TYPE;
486 l_fcst_period_name PA_PERIODS.PERIOD_NAME%TYPE;
487 l_fcst_start_date PA_PERIODS.START_DATE%TYPE;
488 l_fcst_end_date PA_PERIODS.END_DATE%TYPE;
489 l_fcst_item_quantity PA_FORECAST_ITEMS.ITEM_QUANTITY%TYPE;
490 l_role_error_code PA_RESOURCE_ASSIGNMENTS.PLAN_ERROR_CODE%TYPE;
491
492 l_err_code VARCHAR2(30);
493 l_err_stack VARCHAR2(2000);
494 l_err_stage VARCHAR2(2000);
495 l_err_id NUMBER;
496
497 l_exp_func_raw_cost_rate NUMBER;
498 l_exp_func_raw_cost NUMBER;
499 l_exp_func_burdened_cost_rate NUMBER;
500 l_exp_func_burdened_cost NUMBER;
501 l_projfunc_bill_rate NUMBER;
502 l_projfunc_raw_revenue NUMBER;
503 l_projfunc_raw_cost NUMBER;
504 l_projfunc_raw_cost_rate NUMBER;
505 l_projfunc_burdened_cost NUMBER;
506 l_projfunc_burdened_cost_rate NUMBER;
507 l_error_msg VARCHAR2(30);
508
509 l_std_raw_revenue NUMBER;
510 l_rev_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
511 l_billable_flag VARCHAR2(2);--Added for the bug 2420564
512
513 l_rev_reject_reason VARCHAR2(1000);
514 l_cost_reject_reason VARCHAR2(1000);
515 l_burdened_reject_reason VARCHAR2(1000);
516 l_other_reject_reason VARCHAR2(1000);
517
518 l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
519 l_resource_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
520 l_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
521 l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
522 l_parent_member_id PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%TYPE;
523 l_prj_res_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
524
525 l_fcst_opt_jobcostrate_sch_id PA_FORECASTING_OPTIONS_ALL.JOB_COST_RATE_SCHEDULE_ID%TYPE;
526
527 l_calling_mode VARCHAR2(50);
528 l_rowid ROWID;
529 l_counter NUMBER := 1 ;
530 l_cost_cnt NUMBER := 1 ;
531
532 l_created_by NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_created_by;
533 l_request_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_request_id;
534 l_program_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_program_id;
535 l_program_application_id NUMBER(15) := PA_FORECAST_GLOBAL.G_who_columns.G_program_application_id;
536 l_creation_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_creation_date;
537 l_program_update_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
538
539 l_period_name_flag varchar2(1);
540 l_period_name_tot_flag varchar2(1);
541 l_current_index PLS_INTEGER;
542 l_current_index_tot PLS_INTEGER:=1;
543 l_cnt PLS_INTEGER;
544 l_budget_lines_tbl PA_GENERATE_FORECAST_PUB.budget_lines_tbl_type;
545 l_budget_lines_tot_tbl PA_GENERATE_FORECAST_PUB.budget_lines_tbl_type;
546
547 /* Updating the ROLE LEVEL TOTAL in PA_RESOURCE_ASSIGNMENTS */
548 l_tot_quantity NUMBER;
549 l_tot_revenue NUMBER;
550 l_tot_bcost NUMBER;
551 l_tot_cost NUMBER;
552
553 /* For Storing PROJECT LEVEL TOTAL in PA_RESOURCE_ASSIGNMENTS */
554 l_tot_prj_quantity NUMBER:=0;
555 l_tot_prj_revenue NUMBER:=0;
556 l_tot_prj_bcost NUMBER:=0;
557 l_tot_prj_cost NUMBER:=0;
558
559
560 l_prj_revenue_tab PA_RATE_PVT_PKG.ProjAmt_TabTyp;
561 l_prj_cost_tab PA_RATE_PVT_PKG.ProjAmt_TabTyp;
562 l_project_id NUMBER;
563
564 l_ret_status VARCHAR2(100);
565 l_msg_count NUMBER;
566 l_msg_data VARCHAR2(2000);
567 l_data VARCHAR2(2000);
568 l_msg_index_out NUMBER:=0;
569 l_init_bill_rate_flag VARCHAR2(1);
570 l_role_error_code_flag VARCHAR2(1);
571 l_prj_level_revenue NUMBER:=0;
572 l_process_fis_flag VARCHAR2(1);
573 l_asgmt_status_flag VARCHAR2(1);
574 l_commit_size NUMBER:= PA_GENERATE_FORECAST_PUB.G_commit_cnt;
575 l_commit_cnt NUMBER:= 0;
576 l_event_error_msg VARCHAR2(100);
577
578 l_bl_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
579 l_bl_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
580 l_bl_pd_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
581 l_bl_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
582 l_bl_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
583 l_bl_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
584 l_bl_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
585 l_bl_cost_rej_tab PA_PLSQL_DATATYPES.Char30TabTyp;
586 l_bl_bcost_rej_tab PA_PLSQL_DATATYPES.Char30TabTyp;
587 l_bl_rev_rej_tab PA_PLSQL_DATATYPES.Char30TabTyp;
588 l_bl_oth_rej_tab PA_PLSQL_DATATYPES.Char30TabTyp;
589
590 l_rt_forecast_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
591 l_rt_pd_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
592 l_rt_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
593 l_rt_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
594
595 l_rt_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
596 l_rt_exp_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
597 l_rt_exp_organization_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
598
599 l_rt_exp_func_raw_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
600 l_rt_exp_func_raw_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
601 l_rt_exp_func_bur_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
602 l_rt_exp_func_burdned_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
603 l_rt_projfunc_bill_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
604 l_rt_projfunc_raw_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
605 l_rt_projfunc_raw_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
606 l_rt_projfunc_raw_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
607 l_rt_projfunc_burdned_cst_tab PA_PLSQL_DATATYPES.NumTabTyp;
608 l_rt_projfunc_bd_cst_rt_tab PA_PLSQL_DATATYPES.NumTabTyp;
609 l_rt_rev_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
610 l_rt_cst_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
611 l_rt_burdned_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
612 l_rt_others_rejct_reason_tab PA_PLSQL_DATATYPES.Char30TabTyp;
613 l_bulk_fetch_count NUMBER:= 0;
614 l_markup_percentage NUMBER;
615 l_cost_based_error_code VARCHAR2(100);
616
617 l_prj_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
618 l_avg_bill_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
619 /*Code Changes for Bug No.2984871 start */
620 l_rowcount number :=0;
621 /*Code Changes for Bug No.2984871 end */
622 BEGIN
623 PA_DEBUG.init_err_stack('PA_GENERATE_FORECAST_PUB.Generate_Forecast');
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625 l_ret_status := FND_API.G_RET_STS_SUCCESS;
626
627 l_counter := l_counter + 1;
628 PA_DEBUG.g_err_stage := '100: before calling global';
629 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
630 l_commit_size := l_commit_size + 1;
631
632 BEGIN
633 PA_FORECAST_GLOBAL.Initialize_Global(
634 x_msg_count => x_msg_count,
635 x_msg_data => x_msg_data,
636 x_ret_status => x_return_status);
637 EXCEPTION
638 WHEN OTHERS THEN
639 RAISE;
640 END;
641
642 l_ret_status := x_return_status;
643
644 PA_DEBUG.g_err_stage := '200: after calling global';
645 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
646 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
647 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
648 p_return_status => l_ret_status,
649 x_msg_count => l_msg_count,
650 x_msg_data => l_msg_data,
651 x_data => l_data,
652 x_msg_index_out => l_msg_index_out );
653
654 x_msg_count := l_msg_count;
655 x_msg_data := l_msg_data;
656 x_return_status := l_ret_status;
657 PA_DEBUG.Reset_Err_stack;
658 RETURN;
659 END IF;
660
661
662 l_fcst_def_bem := PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_def_bem;
663 l_fcst_res_list:= PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_res_list;
664 l_fcst_period_type:=PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_period_type;
665 l_fcst_opt_jobcostrate_sch_id:= PA_FORECAST_GLOBAL.G_implementation_details.G_fcst_cost_rate_sch_id;
666
667
668 PA_DEBUG.g_err_stage := '205: Project ID :'||p_project_id;
669 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
670 PA_DEBUG.g_err_stage := '210: Default budget entry method :'||l_fcst_def_bem;
671 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
672 PA_DEBUG.g_err_stage := '220: Default resource list :'||l_fcst_res_list;
673 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
674 PA_DEBUG.g_err_stage := '230: Forecasting Period Type :'||l_fcst_period_type;
675 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
676
677 l_created_by := PA_FORECAST_GLOBAL.G_who_columns.G_created_by;
678 l_request_id := PA_FORECAST_GLOBAL.G_who_columns.G_request_id;
679 l_program_id := PA_FORECAST_GLOBAL.G_who_columns.G_program_id;
680 l_program_application_id := PA_FORECAST_GLOBAL.G_who_columns.G_program_application_id;
681 l_creation_date := PA_FORECAST_GLOBAL.G_who_columns.G_creation_date;
682 l_program_update_date := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
683 PA_DEBUG.g_err_stage := '300: before fetching project cursor';
684 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
685 /* l_role_error_code_flag is used here for only checking whether to
686 continue with forecasting process or not */
687 l_role_error_code_flag := 'N';
688 OPEN PROJ_DETAILS;
689 FETCH PROJ_DETAILS INTO
690 l_project_type,
691 l_project_currency_code,
692 l_carrying_out_organization_id,
693 l_project_value,
694 l_job_bill_rate_schedule_id,
695 l_emp_bill_rate_schedule_id,
696 l_distribution_rule,
697 l_bill_job_group_id,
698 l_org_id,
699 l_completion_date,
700 l_template_flag,
701 l_projfunc_currency_code,
702 l_projfunc_bil_rate_date_code,
703 l_projfunc_bil_rate_type,
704 l_projfunc_bil_rate_date,
705 l_projfunc_bil_exchange_rate,
706 l_cost_job_group_id,
707 l_prj_rate_date,
708 l_prj_rate_type,
709 l_prj_bil_rate_date_code,
710 l_prj_bil_rate_type,
711 l_prj_bil_rate_date,
712 l_prj_bil_ex_rate,
713 l_prjfunc_cost_rate_type,
714 l_prjfunc_cost_rate_date,
715 l_labor_tp_schedule_id,
716 l_labor_tp_fixed_date,
717 l_labor_sch_discount,
718 l_asg_precedes_task,
719 l_labor_bill_rate_orgid,
720 l_labor_std_bill_rate_sch,
721 l_labor_sch_fixed_dt,
722 l_labor_sch_type;
723
724 IF PROJ_DETAILS%NOTFOUND THEN
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 l_ret_status := x_return_status;
727 l_role_error_code_flag := 'Y';
728 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
729 p_msg_name => 'PA_INVALID_PROJECT_ID');
730 ELSIF l_template_flag = 'Y' THEN
731 x_return_status := FND_API.G_RET_STS_ERROR;
732 l_ret_status := x_return_status;
733 l_role_error_code_flag := 'Y';
734 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
735 p_msg_name => 'PA_FCST_NOT_APPL_TMPL');
736 END IF;
737 IF l_role_error_code_flag = 'Y' THEN
738 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
739 p_return_status => l_ret_status,
740 x_msg_count => l_msg_count,
741 x_msg_data => l_msg_data,
742 x_data => l_data,
743 x_msg_index_out => l_msg_index_out );
744
745 x_msg_count := l_msg_count;
746 x_msg_data := l_msg_data;
747 x_return_status := l_ret_status;
748 PA_DEBUG.reset_err_stack;
749 CLOSE PROJ_DETAILS;
750 RETURN;
751 END IF;
752 CLOSE PROJ_DETAILS;
753 PA_DEBUG.g_err_stage := '400: after fetching project cursor';
754 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
755 PA_DEBUG.g_err_stage := '410: before calling for rev gen md';
756 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
757 BEGIN
758
759 PA_RATE_PVT_PKG.Get_Revenue_Generation_Method(
760 P_PROJECT_ID => p_project_id,
761 P_DISTRIBUTION_RULE => l_distribution_rule,
762 X_REV_GEN_METHOD => l_rev_gen_method,
763 X_ERROR_MSG => l_error_msg );
764 EXCEPTION
765 WHEN OTHERS THEN
766 RAISE;
767 END;
768 IF l_error_msg IS NOT NULL THEN
769 l_ret_status := FND_API.G_RET_STS_ERROR;
770
771 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
772 p_msg_name => l_error_msg );
773
774 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
775 p_return_status => l_ret_status,
776 x_msg_count => l_msg_count,
777 x_msg_data => l_msg_data,
778 x_data => l_data,
779 x_msg_index_out => l_msg_index_out );
780
781 x_msg_count := l_msg_count;
782 x_msg_data := l_msg_data;
783 x_return_status := l_ret_status;
784 PA_DEBUG.reset_err_stack;
785 RETURN;
786 END IF;
787
788
789 PA_DEBUG.g_err_stage := '500: before calling budget version cursor';
790 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
791
792 BEGIN
793 PA_GENERATE_FORECAST_PUB.Maintain_Budget_Version(
794 p_project_id => p_project_id,
795 p_plan_processing_code => 'P',
796 x_budget_version_id => l_budget_version_id,
797 x_msg_count => x_msg_count,
798 x_msg_data => x_msg_data,
799 x_return_status => x_return_status );
800 EXCEPTION
801 WHEN OTHERS THEN
802 RAISE;
803 END;
804
805 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
806 l_ret_status := x_return_status;
807 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
808 p_return_status => l_ret_status,
809 x_msg_count => l_msg_count,
810 x_msg_data => l_msg_data,
811 x_data => l_data,
812 x_msg_index_out => l_msg_index_out );
813
814 x_msg_count := l_msg_count;
815 x_msg_data := l_msg_data;
816 x_return_status := l_ret_status;
817
818 PA_DEBUG.g_err_stage := '550: The plan_processing_code may be P - PA_FCST_IN_PROCESS ';
819 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
820
821 PA_DEBUG.reset_err_stack;
822 RETURN;
823 END IF;
824
825 /* Deleting PA_BUDGET_LINES and PA_RESOURCE_ASSIGNMENTS */
826
827 DELETE FROM PA_BUDGET_LINES WHERE
828 RESOURCE_ASSIGNMENT_ID IN
829 (SELECT RESOURCE_ASSIGNMENT_ID FROM PA_RESOURCE_ASSIGNMENTS
830 WHERE
831 BUDGET_VERSION_ID = l_budget_version_id );
832
833 DELETE FROM PA_RESOURCE_ASSIGNMENTS WHERE
834 BUDGET_VERSION_ID = l_budget_version_id;
835 /* Commit the changes so that no other process pick up the same project for Forecasting */
836
837 COMMIT;
838
839 PA_DEBUG.g_err_stage := '690: Budget Version ID :'||l_budget_version_id;
840 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
841 PA_DEBUG.g_err_stage := '695: return status :'||x_return_status;
842 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
843
844 PA_DEBUG.g_err_stage := '700: before fetching PA_PROJ_ASSIGNMENT cursor';
845 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
846
847 /* Set plan processing code to G - G(enerated Successfully) */
848
849 l_plan_processing_code := 'G';
850 l_budget_lines_tot_tbl.DELETE;
851
852 OPEN PROJ_ASSIGNMENTS;
853 LOOP
854 FETCH PROJ_ASSIGNMENTS INTO
855 l_prj_assignment_id,
856 l_prj_start_date,
857 l_prj_resource_id,
858 l_prj_project_role_id,
859 l_prj_fcst_job_id,
860 l_prj_fcst_job_group_id,
861 l_prj_meaning,
862 l_prj_assignment_type,
863 l_prj_exp_organization_id,
864 l_prj_exp_type,
865 l_prj_revenue_bill_rate,
866 l_prj_expenditure_org_id,
867 l_prj_status_code,
868 l_billable_flag; --Added for the bug 2420564;
869 IF PROJ_ASSIGNMENTS%NOTFOUND THEN
870 EXIT;
871 END IF;
872 l_role_error_code := NULL;
873 l_role_error_code_flag := 'N';
874
875 PA_DEBUG.g_err_stage := '750: Assignment Id :'||l_prj_assignment_id;
876 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
877
878 /* The following check is added to avoid processing of assignment records if the status
879 is cancelled */
880
881 l_process_fis_flag := 'Y';
882 IF l_prj_status_code IS NOT NULL THEN
883 IF l_prj_assignment_type = 'OPEN_ASSIGNMENT' THEN
884 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Asgmt_In_Open_Status(
885 l_prj_status_code,
886 'OPEN_ASGMT');
887 IF l_asgmt_status_flag = 'N' THEN
888 l_process_fis_flag := 'N';
889 END IF;
890 ELSIF ( l_prj_assignment_type = 'STAFFED_ASSIGNMENT' OR
891 l_prj_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' ) THEN
892 l_asgmt_status_flag := PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(
893 l_prj_status_code,
894 'STAFFED_ASGMT');
895 IF l_asgmt_status_flag = 'Y' THEN
896 l_process_fis_flag := 'N';
897 END IF;
898 END IF;
899 END IF;
900
901 IF l_process_fis_flag = 'Y' AND l_prj_assignment_type = 'OPEN_ASSIGNMENT' AND
902 ( l_prj_fcst_job_id IS NULL OR l_prj_fcst_job_group_id IS NULL ) THEN
903 PA_DEBUG.g_err_stage := '800: before fetching deflt jobid and job group id from roles';
904 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
905 BEGIN
906 SELECT PR.DEFAULT_JOB_ID,PJ.JOB_GROUP_ID INTO
907 l_prj_fcst_job_id,l_prj_fcst_job_group_id FROM
908 PA_PROJECT_ROLE_TYPES PR, PER_JOBS PJ
909 WHERE
910 PR.PROJECT_ROLE_ID = l_prj_project_role_id AND
911 PJ.JOB_ID = PR.DEFAULT_JOB_ID;
912 EXCEPTION
913 WHEN NO_DATA_FOUND THEN
914 l_role_error_code := 'PA_FCST_NOJOB_FOR_ROLE';
915 l_role_error_code_flag := 'Y';
916 l_plan_processing_code := 'E';
917 WHEN OTHERS THEN
918 UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id);
919 RAISE;
920 END;
921 PA_DEBUG.g_err_stage := '850: after fetching PA_PROJ_ASSIGNMENT cursor';
922 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
923 END IF;
924
925 PA_DEBUG.g_err_stage := '900: before fetching the RLM ID';
926 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
927
928 l_parent_member_id := NULL;
929 l_track_as_labor_flag := NULL;
930 l_resource_list_member_id := NULL;
931 l_resource_id := NULL;
932 IF l_process_fis_flag = 'Y' THEN
933 BEGIN
934 SELECT RLM.RESOURCE_LIST_MEMBER_ID INTO
935 l_resource_list_member_id
936 FROM
937 PA_RESOURCE_LIST_MEMBERS RLM, PA_RESOURCES R, PA_RESOURCE_TXN_ATTRIBUTES RT
938 WHERE
939 RLM.RESOURCE_LIST_ID = l_fcst_res_list AND
940 RLM.RESOURCE_ID = R.RESOURCE_ID AND
941 RT.RESOURCE_ID = R.RESOURCE_ID AND
942 RT.PROJECT_ROLE_ID = l_prj_project_role_id;
943 PA_DEBUG.g_err_stage := '950: after fetching the RLM ID';
944 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
945 PA_DEBUG.g_err_stage := '960: RLM ID from TABLE :'||l_resource_list_member_id;
946 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 l_resource_list_member_id := NULL;
950 PA_DEBUG.g_err_stage := '1000: before calling PA_CREATE_RESOURCE.CREATE_RESOURCE_LIST_MEMBER';
951 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
952 PA_CREATE_RESOURCE.ADD_RESOUCE_LIST_MEMBER
953 (P_RESOURCE_LIST_ID => l_fcst_res_list,
954 P_RESOURCE_NAME => l_prj_meaning,
955 P_RESOURCE_TYPE_CODE => 'PROJECT_ROLE',
956 P_ALIAS => SUBSTR(l_prj_meaning,1,30),
957 P_SORT_ORDER => NULL,
958 P_DISPLAY_FLAG => NULL,
959 P_ENABLED_FLAG => NULL,
960 P_PERSON_ID => NULL,
961 P_JOB_ID => NULL,
962 P_PROJ_ORGANIZATION_ID => NULL,
963 P_VENDOR_ID => NULL,
964 P_EXPENDITURE_TYPE => NULL,
965 P_EVENT_TYPE => NULL,
966 P_EXPENDITURE_CATEGORY => NULL,
967 P_REVENUE_CATEGORY_CODE => NULL,
968 P_NON_LABOR_RESOURCE => NULL,
969 P_SYSTEM_LINKAGE => NULL,
970 P_PARENT_MEMBER_ID => l_parent_member_id,
971 P_RESOURCE_LIST_MEMBER_ID => l_resource_list_member_id,
972 P_TRACK_AS_LABOR_FLAG => l_track_as_labor_flag,
973 P_ERR_CODE => l_err_id,
974 P_ERR_STAGE => l_err_stage,
975 P_ERR_STACK => l_err_stack,
976 P_PROJECT_ROLE_ID => l_prj_project_role_id,
977 P_RESOURCE_ID => l_resource_id);
978 PA_DEBUG.g_err_stage := '1050: after calling PA_CREATE_RESOURCE.CREATE_RESOURCE_LIST_MEMBER';
979 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
980 l_commit_cnt := l_commit_cnt + 1;
981 END;
982 END IF;
983 PA_DEBUG.g_err_stage := '1060: RLM ID from API :'||l_resource_list_member_id;
984 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
985 l_current_index := 1;
986 l_tot_quantity := 0;
987 l_tot_revenue := 0;
988 l_tot_bcost := 0;
989 l_tot_cost := 0;
990 IF l_process_fis_flag = 'Y' THEN
991 IF l_prj_assignment_type = 'OPEN_ASSIGNMENT' THEN
992 l_calling_mode := 'ROLE';
993 l_prj_person_id := NULL;
994 l_prj_short_assignment_type := 'R';
995 IF l_prj_exp_organization_id IS NULL THEN
996 l_prj_exp_organization_id := l_carrying_out_organization_id;
997 END IF;
998 IF l_prj_expenditure_org_id IS NULL THEN
999 l_prj_expenditure_org_id := l_org_id;
1000 END IF;
1001 ELSIF ( l_prj_assignment_type = 'STAFFED_ASSIGNMENT' OR
1002 l_prj_assignment_type = 'STAFFED_ADMIN_ASSIGNMENT' ) THEN
1003 l_calling_mode := 'ASSIGNMENT';
1004 l_prj_short_assignment_type := 'A';
1005 l_prj_person_id := GET_PERSON_ID(l_prj_resource_id);
1006 IF l_prj_person_id IS NULL THEN
1007 l_role_error_code := 'PA_FCST_NO_PERSON_ID';
1008 l_role_error_code_flag := 'Y';
1009 l_plan_processing_code := 'E';
1010 END IF;
1011 END IF;
1012 END IF;
1013 PA_DEBUG.g_err_stage := '1100: before Fetching Forecasting cursor';
1014 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1015
1016 IF l_process_fis_flag = 'Y' THEN
1017 IF l_fcst_period_type = 'PA' THEN
1018 OPEN FCST_PA(l_prj_assignment_id);
1019 ELSE
1020 OPEN FCST_GL(l_prj_assignment_id);
1021 END IF;
1022 l_budget_lines_tbl.delete;
1023
1024 l_rt_forecast_item_id_tab.delete;
1025 l_rt_pd_name_tab.delete;
1026 l_rt_start_date_tab.delete;
1027 l_rt_end_date_tab.delete;
1028 l_rt_qty_tab.delete;
1029 l_rt_exp_org_id_tab.delete;
1030 l_rt_exp_organization_id_tab.delete;
1031 l_rt_exp_func_raw_cst_rt_tab.delete;
1032 l_rt_exp_func_raw_cst_tab.delete;
1033 l_rt_exp_func_bur_cst_rt_tab.delete;
1034 l_rt_exp_func_burdned_cst_tab.delete;
1035 l_rt_projfunc_bill_rt_tab.delete;
1036 l_rt_projfunc_raw_revenue_tab.delete;
1037 l_rt_projfunc_raw_cst_tab.delete;
1038 l_rt_projfunc_raw_cst_rt_tab.delete;
1039 l_rt_projfunc_burdned_cst_tab.delete;
1040 l_rt_projfunc_bd_cst_rt_tab.delete;
1041 l_rt_rev_rejct_reason_tab.delete;
1042 l_rt_cst_rejct_reason_tab.delete;
1043 l_rt_burdned_rejct_reason_tab.delete;
1044 l_rt_others_rejct_reason_tab.delete;
1045
1046 l_init_bill_rate_flag := 'N';
1047 /* the following LOOP is a dummy loop to take care of NO_DATA_FOUND error */
1048 LOOP
1049 IF l_fcst_period_type = 'PA' THEN
1050
1051 FETCH FCST_PA BULK COLLECT INTO
1052
1053 l_rt_exp_org_id_tab,
1054 l_rt_exp_organization_id_tab,
1055 l_rt_pd_name_tab,
1056 l_rt_start_date_tab,
1057 l_rt_end_date_tab,
1058 l_rt_qty_tab,
1059 l_rt_forecast_item_id_tab;
1060 ELSE
1061 FETCH FCST_GL BULK COLLECT INTO
1062 l_rt_exp_org_id_tab,
1063 l_rt_exp_organization_id_tab,
1064 l_rt_pd_name_tab,
1065 l_rt_start_date_tab,
1066 l_rt_end_date_tab,
1067 l_rt_qty_tab,
1068 l_rt_forecast_item_id_tab;
1069 END IF;
1070 l_bulk_fetch_count := l_rt_exp_org_id_tab.count;
1071 PA_DEBUG.g_err_stage := '1100A: aft Fetching Fcst cursor : '||l_rt_exp_org_id_tab.count;
1072 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1073 IF l_bulk_fetch_count = 0 THEN
1074 EXIT;
1075 END IF;
1076 /* Initial bill rate API will be called always, because of the
1077 billing client extn changes ( check for l_prj_revenue_bill_rate IS NULL
1078 is removed ) and Bill rate override in the Assignment level */
1079 l_prj_revenue_bill_rate := NULL;
1080 /* Added for Org Forecasting changes */
1081 BEGIN
1082 SELECT EXPENDITURE_TYPE_CLASS INTO l_system_linkage FROM
1083 Pa_Forecast_Items WHERE
1084 Forecast_Item_Id = l_rt_forecast_item_id_tab(1);
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 PA_DEBUG.g_err_stage := 'no data found in FI while getting exp type class';
1088 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1089 UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
1090 RAISE;
1091 END;
1092 /* Added for Org Forecasting changes */
1093 IF l_role_error_code IS NULL THEN
1094 BEGIN
1095 PA_DEBUG.g_err_stage := '1105: before calling init bill rate';
1096 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1097 PA_RATE_PVT_PKG.get_initial_bill_rate(
1098 p_assignment_type => l_prj_short_assignment_type,
1099 p_asgn_start_date => l_prj_start_date,
1100 p_project_id => p_project_id,
1101 p_quantity => 1,
1102 p_expenditure_org_id => l_rt_exp_org_id_tab(1),
1103 p_expenditure_type => l_prj_exp_type,
1104 p_expenditure_organization_id => l_rt_exp_organization_id_tab(1),
1105 p_person_id => l_prj_person_id,
1106 p_assignment_id => l_prj_assignment_id,
1107 p_forecast_item_id => l_rt_forecast_item_id_tab(1),
1108 p_forecast_job_id => l_prj_fcst_job_id,
1109 p_forecast_job_group_id => l_prj_fcst_job_group_id,
1110 p_project_org_id => l_org_id,
1111 p_expenditure_currency_code => NULL,
1112 p_project_type => l_project_type,
1113 p_task_id => NULL,
1114 p_bill_rate_multiplier => NULL,
1115 p_project_bill_job_group_id => l_bill_job_group_id,
1116 p_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id,
1117 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id,
1118 x_projfunc_bill_rate => l_prj_revenue_bill_rate,
1119 x_projfunc_raw_revenue => l_std_raw_revenue,
1120 x_rev_currency_code => l_rev_currency_code,
1121 x_markup_percentage => l_markup_percentage,
1122 x_return_status => x_return_status,
1123 x_msg_count => x_msg_count,
1124 x_msg_data => x_msg_data,
1125 p_forecasting_type => 'PROJECT_FORECASTING',
1126 p_assign_precedes_task => l_asg_precedes_task,
1127 p_system_linkage => l_system_linkage,
1128 p_labor_schdl_discnt => l_labor_sch_discount,
1129 p_labor_bill_rate_org_id => l_labor_bill_rate_orgid,
1130 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_sch,
1131 p_labor_schedule_fixed_date => l_labor_sch_fixed_dt,
1132 p_labor_sch_type => l_labor_sch_type,
1133 p_projfunc_currency_code => l_projfunc_currency_code,
1134 p_projfunc_rev_rt_dt_code => l_projfunc_bil_rate_date_code,
1135 p_projfunc_rev_rt_date => l_projfunc_bil_rate_date,
1136 p_projfunc_rev_rt_type => l_projfunc_bil_rate_type,
1137 p_projfunc_rev_exch_rt => l_projfunc_bil_exchange_rate,
1138 p_projfunc_cst_rt_date => l_prjfunc_cost_rate_date,
1139 p_projfunc_cst_rt_type => l_prjfunc_cost_rate_type,
1140 p_project_currency_code => l_project_currency_code,
1141 p_project_rev_rt_dt_code => l_prj_bil_rate_date_code,
1142 p_project_rev_rt_date => l_prj_bil_rate_date,
1143 p_project_rev_rt_type => l_prj_bil_rate_type,
1144 p_project_rev_exch_rt => l_prj_bil_ex_rate,
1145 p_project_cst_rt_date => l_prj_rate_date,
1146 p_project_cst_rt_type => l_prj_rate_type );
1147
1148 PA_DEBUG.g_err_stage := '1105: after calling init bill rate';
1149 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
1153 RAISE;
1154 END;
1155 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1156 l_plan_processing_code := 'E';
1157 l_role_error_code := SUBSTR(x_msg_data,1,30);
1158 END IF;
1159 END IF;
1160
1161 l_init_bill_rate_flag := 'Y';
1162
1163 l_error_msg := NULL;
1164 l_projfunc_raw_revenue := 0;
1165 l_projfunc_raw_cost := 0;
1166 l_projfunc_raw_cost_rate := 0;
1167 l_projfunc_burdened_cost := 0;
1168 /* Rate API should not be called if any role level error occurs for REQUIREMENT but
1169 not for STAFFED ASSIGNMENT */
1170 IF l_role_error_code_flag = 'N' THEN
1171 BEGIN
1172 /* Added for Org Forecasting changes */
1173 BEGIN
1174 SELECT EXPENDITURE_TYPE_CLASS INTO l_system_linkage FROM
1175 Pa_Forecast_Items WHERE
1176 Forecast_Item_Id = l_rt_forecast_item_id_tab(1);
1177 EXCEPTION
1178 WHEN NO_DATA_FOUND THEN
1179 PA_DEBUG.g_err_stage := 'no data found in FI while getting exp type class';
1180 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1181 UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
1182 RAISE;
1183 END;
1184 l_rt_system_linkage_tab.delete;
1185 l_rt_pfunc_rev_rt_dt_code_tab.delete;
1186 l_rt_pfunc_rev_rt_date_tab.delete;
1187 l_rt_pfunc_rev_rt_type_tab.delete;
1188 l_rt_pfunc_rev_ex_rt_tab.delete;
1189 l_rt_pfunc_cost_rt_date_tab.delete;
1190 l_rt_pfunc_cost_rt_type_tab.delete;
1191 l_rt_proj_rev_rt_dt_code_tab.delete;
1192 l_rt_proj_rev_rt_date_tab.delete;
1193 l_rt_proj_rev_rt_type_tab.delete;
1194 l_rt_proj_rev_ex_rt_tab.delete;
1195 l_rt_proj_cost_rt_date_tab.delete;
1196 l_rt_proj_cost_rt_type_tab.delete;
1197
1198 FOR l_tmp_idx IN 1 .. l_rt_start_date_tab.COUNT LOOP
1199 l_rt_system_linkage_tab(l_tmp_idx) := l_system_linkage;
1200
1201 l_rt_pfunc_rev_rt_dt_code_tab(l_tmp_idx) := l_projfunc_bil_rate_date_code;
1202 l_rt_pfunc_rev_rt_date_tab(l_tmp_idx) := l_projfunc_bil_rate_date;
1203 l_rt_pfunc_rev_rt_type_tab(l_tmp_idx) := l_projfunc_bil_rate_type;
1204 l_rt_pfunc_rev_ex_rt_tab(l_tmp_idx) := l_projfunc_bil_exchange_rate;
1205 l_rt_pfunc_cost_rt_date_tab(l_tmp_idx) := l_prjfunc_cost_rate_date;
1206 l_rt_pfunc_cost_rt_type_tab(l_tmp_idx) := l_prjfunc_cost_rate_type;
1207
1208 l_rt_proj_rev_rt_dt_code_tab(l_tmp_idx) := l_prj_bil_rate_date_code;
1209 l_rt_proj_rev_rt_date_tab(l_tmp_idx) := l_prj_bil_rate_date;
1210 l_rt_proj_rev_rt_type_tab(l_tmp_idx) := l_prj_bil_rate_type;
1211 l_rt_proj_rev_ex_rt_tab(l_tmp_idx) := l_prj_bil_ex_rate;
1212 l_rt_proj_cost_rt_date_tab(l_tmp_idx) := l_prj_rate_date;
1213 l_rt_proj_cost_rt_type_tab(l_tmp_idx) := l_prj_rate_type;
1214 l_rt_others_rejct_reason_tab(l_tmp_idx) := NULL;
1215 END LOOP;
1216 /* Added for Org Forecasting changes */
1217
1218 PA_DEBUG.g_err_stage := '1200: bef calling RATE API calc_rate_amount ';
1219 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1220 /* dbms_output.put_line('bef calling Rate API ');
1221 dbms_output.put_line('st dt :'||l_rt_start_date_tab.count);
1222 dbms_output.put_line(' p_projfunc_rev_rt_dt_code_tab ' || l_rt_pfunc_rev_rt_dt_code_tab.COUNT);
1223 dbms_output.put_line(' p_projfunc_rev_rt_date_tab ' || l_rt_pfunc_rev_rt_date_tab.COUNT);
1224 dbms_output.put_line(' p_projfunc_rev_rt_type_tab ' || l_rt_pfunc_rev_rt_type_tab.COUNT);
1225 dbms_output.put_line(' p_projfunc_rev_exch_rt_tab ' || l_rt_pfunc_rev_ex_rt_tab.COUNT);
1226 dbms_output.put_line(' p_projfunc_cst_rt_date_tab ' || l_rt_pfunc_cost_rt_date_tab.COUNT);
1227 dbms_output.put_line(' p_projfunc_cst_rt_type_tab ' || l_rt_pfunc_cost_rt_type_tab.COUNT);
1228 dbms_output.put_line(' x_projfunc_rev_rt_date_tab ' || lx_rt_pfunc_cost_rt_date_tab.COUNT);
1229 dbms_output.put_line(' x_projfunc_rev_rt_type_tab ' || lx_rt_pfunc_rev_rt_type_tab.COUNT);
1230 dbms_output.put_line(' x_projfunc_rev_exch_rt_tab ' || lx_rt_pfunc_rev_ex_rt_tab.COUNT);
1231 dbms_output.put_line(' x_projfunc_cst_rt_date_tab ' || lx_rt_pfunc_cost_rt_date_tab.COUNT);
1232 dbms_output.put_line(' x_projfunc_cst_rt_type_tab ' || lx_rt_pfunc_cost_rt_type_tab.COUNT);
1233 dbms_output.put_line(' x_projfunc_cst_exch_rt_tab ' || lx_rt_pfunc_cost_ex_rt_tab.COUNT);
1234 dbms_output.put_line(' p_project_rev_rt_dt_code_tab ' || l_rt_proj_rev_rt_dt_code_tab.COUNT);
1235 dbms_output.put_line(' p_project_rev_rt_date_tab ' || l_rt_proj_rev_rt_date_tab.COUNT);
1236 dbms_output.put_line(' p_project_rev_rt_type_tab ' || l_rt_proj_rev_rt_type_tab.COUNT);
1237 dbms_output.put_line(' p_project_rev_exch_rt_tab ' || l_rt_proj_rev_ex_rt_tab.COUNT);
1238 dbms_output.put_line(' p_project_cst_rt_date_tab ' || l_rt_proj_cost_rt_date_tab.COUNT);
1239 dbms_output.put_line(' p_project_cst_rt_type_tab ' || l_rt_proj_cost_rt_type_tab.COUNT); */
1240 PA_RATE_PVT_PKG.Calc_Rate_Amount(
1241 P_CALLING_MODE => l_calling_mode,
1242 P_RATE_CALC_DATE_TAB => l_rt_start_date_tab,
1243 P_ITEM_ID => l_prj_assignment_id,
1244 P_ASGN_START_DATE => l_prj_start_date,
1245 P_PROJECT_ID => p_project_id,
1246 P_FORECAST_ITEM_ID_TAB => l_rt_forecast_item_id_tab,
1247 P_QUANTITY_TAB => l_rt_qty_tab,
1248 P_FORECAST_JOB_ID => l_prj_fcst_job_id,
1249 P_FORECAST_JOB_GROUP_ID => l_prj_fcst_job_group_id,
1250 P_PERSON_ID => l_prj_person_id,
1251 P_EXPENDITURE_ORG_ID_TAB => l_rt_exp_org_id_tab,
1252 P_EXPENDITURE_TYPE => l_prj_exp_type,
1253 P_EXPENDITURE_ORGZ_ID_TAB => l_rt_exp_organization_id_tab,
1254 P_PROJECT_ORG_ID => l_org_id,
1255 P_LABOR_COST_MULTI_NAME => NULL,
1256 P_PROJ_COST_JOB_GROUP_ID => NULL,
1257 P_JOB_COST_RATE_SCHEDULE_ID => l_fcst_opt_jobcostrate_sch_id,
1258 P_PROJECT_TYPE => l_project_type,
1259 P_TASK_ID => NULL,
1260 P_PROJFUNC_CURRENCY_CODE => l_projfunc_currency_code,
1261 P_BILL_RATE_MULTIPLIER => NULL,
1262 P_PROJECT_BILL_JOB_GROUP_ID => l_bill_job_group_id,
1263 P_EMP_BILL_RATE_SCHEDULE_ID => l_emp_bill_rate_schedule_id,
1264 P_JOB_BILL_RATE_SCHEDULE_ID => l_job_bill_rate_schedule_id,
1265 P_DISTRIBUTION_RULE => l_distribution_rule,
1266 p_amount_calc_mode => 'ALL',
1267 P_system_linkage => l_rt_system_linkage_tab,
1268 p_assign_precedes_task => l_asg_precedes_task,
1269 p_labor_schdl_discnt => l_labor_sch_discount,
1270 p_labor_bill_rate_org_id => l_labor_bill_rate_orgid,
1271 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_sch,
1272 p_labor_schedule_fixed_date => l_labor_sch_fixed_dt,
1273 p_labor_sch_type => l_labor_sch_type,
1274 X_EXP_FUNC_RAW_CST_RT_TAB => l_rt_exp_func_raw_cst_rt_tab,
1275 X_EXP_FUNC_RAW_CST_TAB => l_rt_exp_func_raw_cst_tab,
1276 X_EXP_FUNC_BURDNED_CST_RT_TAB => l_rt_exp_func_bur_cst_rt_tab,
1277 X_EXP_FUNC_BURDNED_CST_TAB => l_rt_exp_func_burdned_cst_tab,
1278 X_PROJFUNC_BILL_RT_TAB => l_rt_projfunc_bill_rt_tab,
1279 X_PROJFUNC_RAW_REVENUE_TAB => l_rt_projfunc_raw_revenue_tab,
1280 X_PROJFUNC_RAW_CST_TAB => l_rt_projfunc_raw_cst_tab,
1281 X_PROJFUNC_RAW_CST_RT_TAB => l_rt_projfunc_raw_cst_rt_tab,
1282 X_PROJFUNC_BURDNED_CST_TAB => l_rt_projfunc_burdned_cst_tab,
1283 X_PROJFUNC_BURDNED_CST_RT_TAB => l_rt_projfunc_bd_cst_rt_tab,
1284 p_projfunc_rev_rt_dt_code_tab => l_rt_pfunc_rev_rt_dt_code_tab,
1285 p_projfunc_rev_rt_date_tab => l_rt_pfunc_rev_rt_date_tab,
1286 p_projfunc_rev_rt_type_tab => l_rt_pfunc_rev_rt_type_tab,
1287 p_projfunc_rev_exch_rt_tab => l_rt_pfunc_rev_ex_rt_tab,
1288 p_projfunc_cst_rt_date_tab => l_rt_pfunc_cost_rt_date_tab,
1289 p_projfunc_cst_rt_type_tab => l_rt_pfunc_cost_rt_type_tab,
1290 x_projfunc_rev_rt_date_tab => lx_rt_pfunc_rev_rt_date_tab,
1291 x_projfunc_rev_rt_type_tab => lx_rt_pfunc_rev_rt_type_tab,
1292 x_projfunc_rev_exch_rt_tab => lx_rt_pfunc_rev_ex_rt_tab,
1293 x_projfunc_cst_rt_date_tab => lx_rt_pfunc_cost_rt_date_tab,
1294 x_projfunc_cst_rt_type_tab => lx_rt_pfunc_cost_rt_type_tab,
1295 x_projfunc_cst_exch_rt_tab => lx_rt_pfunc_cost_ex_rt_tab,
1296 p_project_currency_code => l_project_currency_code,
1297 p_project_rev_rt_dt_code_tab => l_rt_proj_rev_rt_dt_code_tab,
1298 p_project_rev_rt_date_tab => l_rt_proj_rev_rt_date_tab,
1299 p_project_rev_rt_type_tab => l_rt_proj_rev_rt_type_tab,
1300 p_project_rev_exch_rt_tab => l_rt_proj_rev_ex_rt_tab,
1301 p_project_cst_rt_date_tab => l_rt_proj_cost_rt_date_tab,
1302 p_project_cst_rt_type_tab => l_rt_proj_cost_rt_type_tab,
1303 x_project_bill_rt_tab => lx_rt_proj_bill_rate_tab,
1304 x_project_raw_revenue_tab => lx_rt_proj_raw_revenue_tab,
1305 x_project_rev_rt_date_tab => lx_rt_proj_rev_rt_date_tab,
1306 x_project_rev_rt_type_tab => lx_rt_proj_rev_rt_type_tab,
1307 x_project_rev_exch_rt_tab => lx_rt_proj_rev_ex_rt_tab,
1308 x_project_raw_cst_tab => lx_rt_proj_raw_cost_tab,
1309 x_project_raw_cst_rt_tab => lx_rt_proj_raw_cost_rt_tab,
1310 x_project_burdned_cst_tab => lx_rt_proj_bd_cost_tab,
1311 x_project_burdned_cst_rt_tab => lx_rt_proj_bd_cost_rt_tab,
1312 x_project_cst_rt_date_tab => lx_rt_proj_cost_rt_date_tab,
1313 x_project_cst_rt_type_tab => lx_rt_proj_cost_rt_type_tab,
1314 x_project_cst_exch_rt_tab => lx_rt_proj_cost_ex_rt_tab,
1315 x_exp_func_curr_code_tab => lx_rt_expfunc_curr_code_tab,
1316 x_exp_func_cst_rt_date_tab => lx_rt_expfunc_cost_rt_date_tab,
1317 x_exp_func_cst_rt_type_tab => lx_rt_expfunc_cost_rt_type_tab,
1318 x_exp_func_cst_exch_rt_tab => lx_rt_expfunc_cost_ex_rt_tab,
1319 x_cst_txn_curr_code_tab => lx_rt_cost_txn_curr_code_tab,
1320 x_txn_raw_cst_rt_tab => lx_rt_txn_raw_cost_rt_tab,
1321 x_txn_raw_cst_tab => lx_rt_txn_raw_cost_tab,
1322 x_txn_burdned_cst_rt_tab => lx_rt_txn_bd_cost_rt_tab,
1323 x_txn_burdned_cst_tab => lx_rt_txn_bd_cost_tab,
1324 x_rev_txn_curr_code_tab => lx_rt_rev_txn_curr_code_tab,
1325 x_txn_rev_bill_rt_tab => lx_rt_txn_rev_bill_rt_tab,
1326 x_txn_rev_raw_revenue_tab => lx_rt_txn_raw_revenue_tab,
1327 X_ERROR_MSG => l_error_msg,
1328 X_REV_REJCT_REASON_TAB => l_rt_rev_rejct_reason_tab,
1329 X_CST_REJCT_REASON_TAB => l_rt_cst_rejct_reason_tab,
1330 X_BURDNED_REJCT_REASON_TAB => l_rt_burdned_rejct_reason_tab,
1331 X_OTHERS_REJCT_REASON_TAB => l_rt_others_rejct_reason_tab,
1332 X_RETURN_STATUS => x_return_status,
1333 X_MSG_COUNT => x_msg_count,
1334 X_MSG_DATA => x_msg_data );
1335 /*Added for the bug 2420564*/
1336 IF l_billable_flag = 'N' THEN
1337 FOR l_rt_tab_cnt IN 1 .. l_rt_pd_name_tab.count LOOP
1338 lx_rt_txn_raw_revenue_tab(l_rt_tab_cnt) := 0;
1339 l_rt_projfunc_raw_revenue_tab(l_rt_tab_cnt) := 0;
1340 lx_rt_proj_raw_revenue_tab(l_rt_tab_cnt) := 0;
1341 END LOOP;
1342 END IF;
1343 /*End of fix for bug 2420564*/
1344
1345 /* dbms_output.put_line('aft calling Rate API '); */
1346 PA_DEBUG.g_err_stage := '1200: aft calling RATE API calc_rate_amount ';
1347 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
1351 RAISE;
1352 END;
1353
1354 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1355 l_plan_processing_code := 'E';
1356 IF l_role_error_code IS NULL THEN
1357 l_role_error_code := 'PA_FCST_NO_DISP_ERR';
1358 END IF;
1359 END IF;
1360
1361 /* The err msg PA_FCST_NO_DISP_ERR is used to show the error icon in the
1362 pages and the actual error messages will be stored in budget lines table. */
1363
1364 END IF;
1365
1366 FOR l_rt_tab_cnt IN 1 .. l_rt_pd_name_tab.count LOOP
1367
1368 l_projfunc_raw_revenue := 0;
1369 l_projfunc_raw_cost := 0;
1370 l_projfunc_raw_cost_rate := 0;
1371 l_projfunc_burdened_cost := 0;
1372 l_fcst_item_quantity := 0;
1373
1374 l_fcst_period_name := l_rt_pd_name_tab(l_rt_tab_cnt);
1375 l_fcst_item_quantity := l_rt_qty_tab(l_rt_tab_cnt);
1376
1377 /* Bug 2084872 : Changed to check if data exists , b/c for Role level error
1378 Rate API will not be called and all the Rate API PL/SQL tables will be empty. */
1379
1380 IF l_rt_projfunc_raw_revenue_tab.exists(l_rt_tab_cnt) THEN
1381 l_projfunc_raw_revenue := NVL(l_rt_projfunc_raw_revenue_tab(l_rt_tab_cnt),0);
1382 ELSE
1383 l_projfunc_raw_revenue := 0;
1384 END IF;
1385 IF l_rt_projfunc_raw_cst_tab.exists(l_rt_tab_cnt) THEN
1386 l_projfunc_raw_cost := NVL(l_rt_projfunc_raw_cst_tab(l_rt_tab_cnt),0);
1387 ELSE
1388 l_projfunc_raw_cost := 0;
1389 END IF;
1390 IF l_rt_projfunc_raw_cst_rt_tab.exists(l_rt_tab_cnt) THEN
1391 l_projfunc_raw_cost_rate := NVL(l_rt_projfunc_raw_cst_rt_tab(l_rt_tab_cnt),0);
1392 ELSE
1393 l_projfunc_raw_cost_rate := 0;
1394 END IF;
1395 IF l_rt_projfunc_burdned_cst_tab.exists(l_rt_tab_cnt) THEN
1396 l_projfunc_burdened_cost := NVL(l_rt_projfunc_burdned_cst_tab(l_rt_tab_cnt),0);
1397 ELSE
1398 l_projfunc_burdened_cost := 0;
1399 END IF;
1400
1401 l_fcst_start_date := l_rt_start_date_tab(l_rt_tab_cnt);
1402 l_fcst_end_date := l_rt_end_date_tab(l_rt_tab_cnt);
1403 IF l_rt_cst_rejct_reason_tab.exists(l_rt_tab_cnt) THEN
1404 l_cost_reject_reason := l_rt_cst_rejct_reason_tab(l_rt_tab_cnt);
1405 ELSE
1406 l_cost_reject_reason := NULL;
1407 END IF;
1408 IF l_rt_rev_rejct_reason_tab.exists(l_rt_tab_cnt) THEN
1409 l_rev_reject_reason := l_rt_rev_rejct_reason_tab(l_rt_tab_cnt);
1410 ELSE
1411 l_rev_reject_reason := NULL;
1412 END IF;
1413 IF l_rt_burdned_rejct_reason_tab.exists(l_rt_tab_cnt) THEN
1414 l_burdened_reject_reason := l_rt_burdned_rejct_reason_tab(l_rt_tab_cnt);
1415 ELSE
1416 l_burdened_reject_reason := NULL;
1417 END IF;
1418 IF l_rt_others_rejct_reason_tab.exists(l_rt_tab_cnt) THEN
1419 l_other_reject_reason := l_rt_others_rejct_reason_tab(l_rt_tab_cnt);
1420 ELSE
1421 l_other_reject_reason := NULL;
1422 END IF;
1423
1424
1425 PA_DEBUG.g_err_stage := '1300: after calling RATE API';
1426 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1427
1428 PA_DEBUG.g_err_stage := '1350: before checking in the PL/SQL TABLE';
1429 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1430
1431 l_period_name_flag := 'N';
1432 FOR l_cnt IN 1 .. l_budget_lines_tbl.COUNT LOOP
1433 IF l_budget_lines_tbl(l_cnt).period_name = l_fcst_period_name THEN
1434 l_budget_lines_tbl(l_cnt).raw_cost := l_budget_lines_tbl(l_cnt).raw_cost + l_projfunc_raw_cost;
1435 l_budget_lines_tbl(l_cnt).burdened_cost := l_budget_lines_tbl(l_cnt).burdened_cost
1436 + l_projfunc_burdened_cost;
1437 l_budget_lines_tbl(l_cnt).revenue := l_budget_lines_tbl(l_cnt).revenue + l_projfunc_raw_revenue;
1438 l_budget_lines_tbl(l_cnt).quantity := l_budget_lines_tbl(l_cnt).quantity + l_fcst_item_quantity;
1439 l_period_name_flag := 'Y';
1440 EXIT;
1441 END IF;
1442 END LOOP;
1443 IF l_period_name_flag = 'N' THEN
1444 l_budget_lines_tbl(l_current_index).period_name := l_fcst_period_name;
1445 l_budget_lines_tbl(l_current_index).start_date := l_fcst_start_date;
1446 l_budget_lines_tbl(l_current_index).end_date:= l_fcst_end_date;
1447 l_budget_lines_tbl(l_current_index).raw_cost := l_projfunc_raw_cost;
1448 l_budget_lines_tbl(l_current_index).burdened_cost := l_projfunc_burdened_cost;
1449 l_budget_lines_tbl(l_current_index).quantity := l_fcst_item_quantity;
1450 l_budget_lines_tbl(l_current_index).revenue := l_projfunc_raw_revenue;
1451 l_budget_lines_tbl(l_current_index).cost_rejection_code := l_cost_reject_reason;
1452 l_budget_lines_tbl(l_current_index).revenue_rejection_code := l_rev_reject_reason;
1453 l_budget_lines_tbl(l_current_index).burden_rejection_code := l_burdened_reject_reason;
1454 l_budget_lines_tbl(l_current_index).other_rejection_code := l_other_reject_reason;
1455 l_current_index := l_current_index + 1;
1456 END IF;
1457
1458 PA_DEBUG.g_err_stage := '1400: after checking in the PL/SQL TABLE';
1459 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1460
1461 PA_DEBUG.g_err_stage := '1450: before checking in the PL/SQL TABLE for TOTALS';
1462 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1463
1464 l_period_name_tot_flag := 'N';
1465 FOR l_cnt IN 1 .. l_budget_lines_tot_tbl.COUNT LOOP
1466 IF l_budget_lines_tot_tbl(l_cnt).period_name = l_fcst_period_name THEN
1467 l_budget_lines_tot_tbl(l_cnt).raw_cost := l_budget_lines_tot_tbl(l_cnt).raw_cost + l_projfunc_raw_cost;
1468 l_budget_lines_tot_tbl(l_cnt).burdened_cost := l_budget_lines_tot_tbl(l_cnt).burdened_cost
1469 + l_projfunc_burdened_cost;
1470 l_budget_lines_tot_tbl(l_cnt).revenue := l_budget_lines_tot_tbl(l_cnt).revenue + l_projfunc_raw_revenue;
1471 l_budget_lines_tot_tbl(l_cnt).quantity := l_budget_lines_tot_tbl(l_cnt).quantity + l_fcst_item_quantity;
1472 l_period_name_tot_flag := 'Y';
1473 EXIT;
1474 END IF;
1475 END LOOP;
1476 IF l_period_name_tot_flag = 'N' THEN
1477 l_budget_lines_tot_tbl(l_current_index_tot).period_name := l_fcst_period_name;
1478 l_budget_lines_tot_tbl(l_current_index_tot).start_date := l_fcst_start_date;
1479 l_budget_lines_tot_tbl(l_current_index_tot).end_date:= l_fcst_end_date;
1480 l_budget_lines_tot_tbl(l_current_index_tot).raw_cost := l_projfunc_raw_cost;
1481 l_budget_lines_tot_tbl(l_current_index_tot).burdened_cost := l_projfunc_burdened_cost;
1482 l_budget_lines_tot_tbl(l_current_index_tot).quantity := l_fcst_item_quantity;
1483 l_budget_lines_tot_tbl(l_current_index_tot).revenue := l_projfunc_raw_revenue;
1484 l_current_index_tot := l_current_index_tot + 1;
1485 END IF;
1486 l_tot_quantity := l_tot_quantity + l_fcst_item_quantity;
1487 l_tot_cost := l_tot_cost + l_projfunc_raw_cost;
1488 l_tot_revenue := l_tot_revenue + l_projfunc_raw_revenue;
1489 l_tot_bcost := l_tot_bcost + l_projfunc_burdened_cost;
1490
1491 PA_DEBUG.g_err_stage := '1500: before checking in the PL/SQL TABLE for TOTALS';
1492 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1493
1494 END LOOP; -- after arriving at pdwise totals
1495
1496 /* Added for Bulk Insert */
1497 /* The following logic is from the PA_BUDGET_LINES_V_PKG to take care of the
1498 bulk insert into PA_BUDGET_LINES for performance */
1499
1500 BEGIN
1501 SELECT resource_assignment_id
1502 INTO l_resource_assignment_id
1503 FROM pa_resource_assignments a
1504 WHERE a.budget_version_id = l_budget_version_id
1505 AND a.project_id = p_project_id
1506 AND nvl(a.task_id,0) = 0
1507 AND a.resource_list_member_id = l_resource_list_member_id
1508 AND a.project_assignment_id = l_prj_assignment_id;
1509 EXCEPTION
1510 WHEN NO_DATA_FOUND THEN
1511 SELECT pa_resource_assignments_s.nextval
1512 INTO l_resource_assignment_id
1513 FROM sys.dual;
1514 INSERT INTO pa_resource_assignments(
1515 resource_assignment_id,
1516 budget_version_id,
1517 project_id,
1518 task_id,
1519 resource_list_member_id,
1520 last_update_date,
1521 last_updated_by,
1522 creation_date,
1523 created_by,
1524 last_update_login,
1525 unit_of_measure,
1526 track_as_labor_flag,
1527 project_assignment_id,
1528 standard_bill_rate
1529 ) VALUES
1530 ( l_resource_assignment_id ,
1531 l_budget_version_id,
1532 p_project_id,
1533 0,
1534 l_resource_list_member_id,
1535 SYSDATE,
1536 l_created_by,
1537 SYSDATE,
1538 l_created_by,
1539 l_request_id,
1540 NULL,
1541 NULL,
1542 l_prj_assignment_id,
1543 l_prj_revenue_bill_rate
1544 );
1545 END;
1546
1547 l_bl_start_date_tab.delete;
1548 l_bl_end_date_tab.delete;
1549 l_bl_pd_name_tab.delete;
1550 l_bl_qty_tab.delete;
1551 l_bl_rcost_tab.delete;
1552 l_bl_revenue_tab.delete;
1553 l_bl_bcost_tab.delete;
1554 l_bl_cost_rej_tab.delete;
1555 l_bl_bcost_rej_tab.delete;
1556 l_bl_rev_rej_tab.delete;
1557 l_bl_oth_rej_tab.delete;
1558
1559
1560 PA_DEBUG.g_err_stage := '1525: bef populating tabs for BL ins for RLM 101';
1561 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1562
1563 FOR l_counter IN 1 .. L_BUDGET_LINES_TBL.COUNT LOOP
1564
1565 PA_DEBUG.g_err_stage := 'st dt :'||to_char(l_budget_lines_tbl(l_counter).start_date,
1566 'dd-mon-yyyy');
1567 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1568 l_bl_start_date_tab(l_counter) := l_budget_lines_tbl(l_counter).start_date;
1569 l_bl_end_date_tab(l_counter) := l_budget_lines_tbl(l_counter).end_date;
1570 l_bl_pd_name_tab(l_counter) := l_budget_lines_tbl(l_counter).period_name;
1571 l_bl_qty_tab(l_counter) := l_budget_lines_tbl(l_counter).quantity;
1572 l_bl_rcost_tab(l_counter) := l_budget_lines_tbl(l_counter).raw_cost;
1573 l_bl_bcost_tab(l_counter) := l_budget_lines_tbl(l_counter).burdened_cost;
1574 l_bl_revenue_tab(l_counter) := l_budget_lines_tbl(l_counter).revenue;
1575 l_bl_cost_rej_tab(l_counter) := l_budget_lines_tbl(l_counter).cost_rejection_code;
1576 l_bl_bcost_rej_tab(l_counter) := l_budget_lines_tbl(l_counter).burden_rejection_code;
1577 l_bl_rev_rej_tab(l_counter) := l_budget_lines_tbl(l_counter).revenue_rejection_code;
1578 l_bl_oth_rej_tab(l_counter) := l_budget_lines_tbl(l_counter).other_rejection_code;
1579 END LOOP;
1580
1581 PA_DEBUG.g_err_stage := '1530: aft populating tabs for BL ins for RLM 101 : ' ||
1582 L_BUDGET_LINES_TBL.COUNT;
1583 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1584
1585 PA_DEBUG.g_err_stage := '1540: bef bulk ins into BL for RLM 101';
1586 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1587 PA_DEBUG.g_err_stage := 'res asg id :'|| l_resource_assignment_id;
1588 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1589
1590 FORALL l_ins_temp IN 1 .. L_BUDGET_LINES_TBL.COUNT
1591 INSERT INTO PA_BUDGET_LINES(
1592 BUDGET_LINE_ID, /* FPB2 */
1593 BUDGET_VERSION_ID, /* FPB2 */
1594 RESOURCE_ASSIGNMENT_ID,
1595 START_DATE ,
1596 LAST_UPDATE_DATE ,
1597 LAST_UPDATED_BY ,
1598 CREATION_DATE ,
1599 CREATED_BY ,
1600 LAST_UPDATE_LOGIN ,
1601 END_DATE ,
1602 PERIOD_NAME ,
1603 QUANTITY ,
1604 RAW_COST ,
1605 BURDENED_COST ,
1606 REVENUE ,
1607 COST_REJECTION_CODE ,
1608 REVENUE_REJECTION_CODE,
1609 BURDEN_REJECTION_CODE ,
1610 OTHER_REJECTION_CODE ,
1611 RAW_COST_SOURCE ,
1612 BURDENED_COST_SOURCE ,
1613 QUANTITY_SOURCE ,
1614 REVENUE_SOURCE ,
1615 TXN_CURRENCY_CODE ) /* FPB2 - Bug 2753426 */
1616 VALUES (
1617 pa_budget_lines_s.nextval, /* FPB2 */
1618 l_budget_version_id, /* FPB2 */
1619 l_resource_assignment_id,
1620 l_bl_start_date_tab(l_ins_temp),
1621 l_program_update_date,
1622 l_created_by,
1623 l_creation_date,
1624 l_created_by,
1625 l_request_id,
1626 l_bl_end_date_tab(l_ins_temp),
1627 l_bl_pd_name_tab(l_ins_temp),
1628 l_bl_qty_tab(l_ins_temp),
1629 l_bl_rcost_tab(l_ins_temp),
1630 l_bl_bcost_tab(l_ins_temp),
1631 l_bl_revenue_tab(l_ins_temp),
1632 l_bl_cost_rej_tab(l_ins_temp),
1633 l_bl_rev_rej_tab(l_ins_temp),
1634 l_bl_bcost_rej_tab(l_ins_temp),
1635 l_bl_oth_rej_tab(l_ins_temp) ,
1636 'M','M','M','M' ,
1637 l_projfunc_currency_code);
1638
1639 /*Code Changes for Bug No.2984871 start */
1640 l_rowcount:=sql%rowcount;
1641 /*Code Changes for Bug No.2984871 end */
1642
1643 PA_DEBUG.g_err_stage := '1550: aft bulk ins into BL for RLM 101 : '||
1644 l_rowcount;
1645 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1646 /* Bug 2984871: replaced sql%rowcount with l_rowcount */
1647 l_commit_cnt := l_commit_cnt + l_rowcount;
1648 IF l_commit_cnt >= l_commit_size THEN
1649 COMMIT;
1650 l_commit_cnt := 0;
1651 END IF;
1652
1653
1654 /* Added for Bulk Insert */
1655
1656
1657 /* Update only if some fis are processed for the current assignment id */
1658 IF L_BUDGET_LINES_TBL.COUNT > 0 THEN
1659 UPDATE PA_RESOURCE_ASSIGNMENTS SET
1660 TOTAL_PLAN_REVENUE = NVL(TOTAL_PLAN_REVENUE,0) + l_tot_revenue,
1661 TOTAL_PLAN_RAW_COST = NVL(TOTAL_PLAN_RAW_COST,0) + l_tot_cost,
1662 TOTAL_PLAN_BURDENED_COST = NVL(TOTAL_PLAN_BURDENED_COST,0) + l_tot_bcost,
1663 TOTAL_PLAN_QUANTITY = NVL(TOTAL_PLAN_QUANTITY,0) + l_tot_quantity,
1664 PLAN_ERROR_CODE = l_role_error_code
1665 WHERE
1666 RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id;
1667
1668 l_tot_prj_revenue := l_tot_prj_revenue + l_tot_revenue;
1669 l_tot_prj_cost := l_tot_prj_cost + l_tot_cost;
1670 l_tot_prj_bcost := l_tot_prj_bcost + l_tot_bcost;
1671 l_tot_prj_quantity:= l_tot_prj_quantity+ l_tot_quantity;
1672 END IF;
1673 EXIT;
1674 /* the above exit is to avoid getting unique constraint error in PA_BUDGET_LINES,
1675 if there is no exit stmt. the bulk insert will try to do insert the same records */
1676 END LOOP;
1677 /* the above dummy for loop is to avoid NO_DATA_FOUND error,
1678 if there are no fis to be proecessed */
1679
1680 /* the cursor should be closed regardless of fis are processed or not to avoid
1681 cursor already open error */
1682
1683 IF l_fcst_period_type = 'PA' THEN
1684 CLOSE FCST_PA;
1685 ELSE
1686 CLOSE FCST_GL;
1687 END IF;
1688 END IF; -- l_process_fis_flag check
1689
1690
1691 IF l_commit_cnt >= l_commit_size THEN
1692 COMMIT;
1693 l_commit_cnt := 0;
1694 END IF;
1695 END LOOP; -- for Assignments
1696 CLOSE PROJ_ASSIGNMENTS;
1697 PA_DEBUG.g_err_stage := '1800: after fetching all project assignments records';
1698 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1699
1700 l_prj_res_assignment_id := NULL;
1701 /* create res assignment record for RLM Is 103 for storing Periodwise TOTALS */
1702 PA_DEBUG.g_err_stage := '1900: before getting RA Id for RLM Id 103';
1703 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1704 IF l_budget_lines_tot_tbl.count > 0 THEN
1705 BEGIN
1706 SELECT RESOURCE_ASSIGNMENT_ID
1707 INTO l_prj_res_assignment_id
1708 FROM PA_RESOURCE_ASSIGNMENTS A
1709 WHERE A.BUDGET_VERSION_ID = l_budget_version_id
1710 AND A.PROJECT_ID = p_project_id
1711 AND nvl(a.task_id,0) = 0 -- to make use of the index
1712 AND A.PROJECT_ASSIGNMENT_ID = -1
1713 AND A.RESOURCE_LIST_MEMBER_ID = 103;
1714 EXCEPTION
1715 WHEN NO_DATA_FOUND THEN
1716 SELECT pa_resource_assignments_s.nextval
1717 INTO l_prj_res_assignment_id
1718 FROM sys.dual;
1719 insert into pa_resource_assignments(
1720 resource_assignment_id,
1721 budget_version_id,
1722 project_id,
1723 task_id,
1724 resource_list_member_id,
1725 last_update_date,
1726 last_updated_by,
1727 creation_date,
1728 created_by,
1729 last_update_login,
1730 unit_of_measure,
1731 track_as_labor_flag,
1732 project_assignment_id) VALUES
1733 (l_prj_res_Assignment_Id ,
1734 l_budget_version_id,
1735 p_project_id,
1736 0, -- Task Id
1737 103, -- RLM Id for project level totals
1738 SYSDATE,
1739 l_created_by,
1740 SYSDATE,
1741 l_created_by,
1742 l_request_id,
1743 NULL, -- x_unit_of_measure
1744 NULL,
1745 -1 ); -- x_track_as_labor_flag
1746 END;
1747
1748 PA_DEBUG.g_err_stage := '2000: after getting RA Id for RLM Id 103';
1749 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1750 /* Create budget lines to store periodwise totals from the table */
1751
1752 PA_DEBUG.g_err_stage := '2100: bef populate tabs for ins into BL RLMId 103: '||
1753 'BL tot tbl cnt:'||l_budget_lines_tot_tbl.count;
1754 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1755
1756 l_bl_start_date_tab.delete;
1757 l_bl_end_date_tab.delete;
1758 l_bl_pd_name_tab.delete;
1759 l_bl_qty_tab.delete;
1760 l_bl_rcost_tab.delete;
1761 l_bl_revenue_tab.delete;
1762 l_bl_bcost_tab.delete;
1763
1764 /* populating the tables for bulk insert */
1765
1766 FOR cnt_temp IN 1 .. l_budget_lines_tot_tbl.count LOOP
1767
1768 l_bl_revenue_tab(cnt_temp) := NULL;
1769
1770 l_bl_start_date_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).start_date;
1771 l_bl_end_date_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).end_date;
1772 l_bl_pd_name_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).period_name;
1773 l_bl_qty_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).quantity;
1774 l_bl_rcost_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).raw_cost;
1775 l_bl_bcost_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).burdened_cost;
1776 IF l_rev_gen_method = 'T' THEN
1777 l_bl_revenue_tab(cnt_temp) := l_budget_lines_tot_tbl(cnt_temp).revenue;
1778 END IF;
1779 END LOOP;
1780
1781 PA_DEBUG.g_err_stage := '2125:aft populating tables for insert into BL RLMId 103 and bef bulk ins';
1782 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1783
1784 FORALL l_ins_temp IN 1 .. l_budget_lines_tot_tbl.count
1785 INSERT INTO PA_BUDGET_LINES(
1786 BUDGET_LINE_ID, /* FPB2 */
1787 BUDGET_VERSION_ID, /* FPB2 */
1788 RESOURCE_ASSIGNMENT_ID,
1789 START_DATE ,
1790 LAST_UPDATE_DATE ,
1791 LAST_UPDATED_BY ,
1792 CREATION_DATE ,
1793 CREATED_BY ,
1794 LAST_UPDATE_LOGIN ,
1795 END_DATE ,
1796 PERIOD_NAME ,
1797 QUANTITY ,
1798 RAW_COST ,
1799 BURDENED_COST ,
1800 REVENUE ,
1801 RAW_COST_SOURCE ,
1802 BURDENED_COST_SOURCE ,
1803 QUANTITY_SOURCE ,
1804 REVENUE_SOURCE ,
1805 TXN_CURRENCY_CODE ) /* FPB2 - Bug 2753426 */
1806 VALUES (
1807 pa_budget_lines_s.nextval, /* FPB2 */
1808 l_budget_version_id, /* FPB2 */
1809 l_prj_res_assignment_id,
1810 l_bl_start_date_tab(l_ins_temp),
1811 l_program_update_date,
1812 l_created_by,
1813 l_creation_date,
1814 l_created_by,
1815 l_request_id,
1816 l_bl_end_date_tab(l_ins_temp),
1817 l_bl_pd_name_tab(l_ins_temp),
1818 l_bl_qty_tab(l_ins_temp),
1819 l_bl_rcost_tab(l_ins_temp),
1820 l_bl_bcost_tab(l_ins_temp),
1821 l_bl_revenue_tab(l_ins_temp),
1822 'M','M','M','M' ,
1823 l_projfunc_currency_code); /* FPB2 - Bug 2753426 */
1824 /*Code Changes for Bug No.2984871 start */
1825 l_rowcount:=sql%rowcount;
1826 /*Code Changes for Bug No.2984871 end */
1827 COMMIT;
1828
1829 /* Bug 2984871: replaced SQL%ROWCOUNT with l_rowcount in the below line */
1830 PA_DEBUG.g_err_stage := '2200: after bulk inserting into BLines for RLMId 103:'||l_rowcount;
1831 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1832
1833 PA_DEBUG.g_err_stage := '2300: before checking for REV GEN Md';
1834 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1835
1836 IF l_rev_gen_method = 'E' THEN
1837 IF l_project_value IS NULL THEN
1838 PA_DEBUG.g_err_stage := '2400: no prj value : bef updating err msg in event Based';
1839 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1840
1841 UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_NO_PRJ_VALUE'
1842 WHERE
1843 RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
1844
1845 l_plan_processing_code := 'E';
1846
1847 PA_DEBUG.g_err_stage := '2450: no prj value : aft updating err msg in event Based';
1848 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1849 ELSE
1850 l_prj_revenue_tab.delete;
1851 PA_DEBUG.g_err_stage := '2400: before calling PA_RATE_PVT_PKG.CALC_EVENT_BASED_REVENUE';
1852 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1853
1854 BEGIN
1855 PA_RATE_PVT_PKG.CALC_EVENT_BASED_REVENUE(
1856 p_project_id => p_project_id,
1857 p_rev_amt => l_project_value,
1858 p_completion_date => l_completion_date,
1859 p_project_currency_code => l_project_currency_code,
1860 p_projfunc_currency_code => l_projfunc_currency_code,
1861 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
1862 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
1863 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
1864 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
1865 x_projfunc_revenue_tab => l_prj_revenue_tab,
1866 x_error_code => l_event_error_msg);
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
1870 RAISE;
1871 END;
1872
1873 PA_DEBUG.g_err_stage := '2450: after calling PA_RATE_PVT_PKG.CALC_EVENT_BASED_REVENUE';
1874 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1875 IF l_event_error_msg IS NULL THEN
1876 PA_DEBUG.g_err_stage := '2500: before upserting in PA_BUDGET_LINES';
1877 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1878
1879 FOR l_counter IN 1 .. l_prj_revenue_tab.count LOOP
1880 UPDATE PA_BUDGET_LINES SET REVENUE = l_prj_revenue_tab(l_counter).amount
1881 WHERE
1882 RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id AND
1883 PERIOD_NAME = l_prj_revenue_tab(l_counter).period_name;
1884 IF SQL%ROWCOUNT = 0 THEN
1885
1886 /* FPB2 */
1887 select pa_budget_lines_s.nextval
1888 into l_budget_line_id
1889 from dual;
1890
1891 INSERT INTO PA_BUDGET_LINES(
1892 BUDGET_LINE_ID, /* FPB2 */
1893 BUDGET_VERSION_ID, /* FPB2 */
1894 RESOURCE_ASSIGNMENT_ID,
1895 START_DATE,
1896 LAST_UPDATE_DATE,
1897 LAST_UPDATED_BY,
1898 CREATION_DATE,
1899 CREATED_BY,
1900 LAST_UPDATE_LOGIN,
1901 END_DATE,
1902 PERIOD_NAME,
1903 QUANTITY,
1904 RAW_COST,
1905 BURDENED_COST,
1906 REVENUE,
1907 TXN_CURRENCY_CODE) /* FPB2 - Bug 2753426 */
1908 VALUES(
1909 l_budget_line_id, /* FPB2 */
1910 l_budget_version_id, /* FPB2 */
1911 l_prj_res_assignment_id,
1912 l_prj_revenue_tab(l_counter).start_date,
1913 l_program_update_date,
1914 l_created_by,
1915 l_creation_date,
1916 l_created_by,
1917 l_request_id,
1918 l_prj_revenue_tab(l_counter).end_date,
1919 l_prj_revenue_tab(l_counter).period_name,
1920 0,
1921 0,
1922 0,
1923 l_prj_revenue_tab(l_counter).amount,
1924 l_projfunc_currency_code); /* FPB2 - Bug 2753426 */
1925 END IF;
1926 l_commit_cnt := l_commit_cnt + 1;
1927 IF l_commit_cnt >= l_commit_size THEN
1928 COMMIT;
1929 l_commit_cnt := 0;
1930 END IF;
1931
1932 END LOOP;
1933 PA_DEBUG.g_err_stage := '2600: after upserting in PA_BUDGET_LINES for event based';
1934 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1935 ELSE
1936 PA_DEBUG.g_err_stage := '2500: no prj value : bef updating err msg in Event Based';
1937 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1938
1939 UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_PDS_NOT_DEFINED'
1940 WHERE
1941 RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
1942
1943 l_plan_processing_code := 'E';
1944
1945 PA_DEBUG.g_err_stage := '2600: no prj value : aft updating err msg in Event Based';
1946 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1947
1948 END IF; -- for if l_event_error_msg
1949 END IF; -- if the project value is null
1950 ELSIF l_rev_gen_method = 'C' THEN
1951 IF l_project_value IS NOT NULL THEN
1952 l_cost_cnt := 1;
1953 l_prj_cost_tab.delete;
1954 l_prj_revenue_tab.delete;
1955 PA_DEBUG.g_err_stage := '2700: Inside REV GEN MD C - before fetching BUDGET_LINES into Table';
1956 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1957 OPEN BUDGET_LINES(l_budget_version_id,p_project_id,l_prj_res_assignment_id);
1958 LOOP
1959 FETCH BUDGET_LINES INTO l_prj_cost_tab(l_cost_cnt).period_name,
1960 l_prj_cost_tab(l_cost_cnt).start_date,
1961 l_prj_cost_tab(l_cost_cnt).amount;
1962 IF BUDGET_LINES%NOTFOUND THEN
1963 EXIT;
1964 END IF;
1965 l_cost_cnt := l_cost_cnt + 1;
1966 END LOOP;
1967
1968 CLOSE BUDGET_LINES;
1969
1970 PA_DEBUG.g_err_stage := '2750: after fetching BUDGET_LINES into PL/SQL table';
1971 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1972
1973 PA_DEBUG.g_err_stage := '2800: before calling PA_RATE_PVT_PKG.CALC_COST_BASED_REVENUE';
1974 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1975 l_cost_based_error_code := NULL;
1976 BEGIN
1977 PA_RATE_PVT_PKG.CALC_COST_BASED_REVENUE(
1978 p_project_id => p_project_id,
1979 p_rev_amt => l_project_value,
1980 p_projfunc_cost_tab => l_prj_cost_tab,
1981 x_projfunc_revenue_tab => l_prj_revenue_tab,
1982 x_error_code => l_cost_based_error_code,
1983 p_project_currency_code => l_project_currency_code,
1984 p_projfunc_currency_code => l_projfunc_currency_code,
1985 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
1986 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
1987 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
1988 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate);
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991 UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
1992 RAISE;
1993 END;
1994
1995 PA_DEBUG.g_err_stage := '2850: after calling PA_RATE_PVT_PKG.CALC_COST_BASED_REVENUE';
1996 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1997 IF l_cost_based_error_code IS NULL THEN
1998 PA_DEBUG.g_err_stage := '2900: bef upd PA_BUDGET_LINES for COST_BASED_REVENUE';
1999 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2000
2001 FOR l_cost_cnt IN 1 .. l_prj_revenue_tab.count LOOP
2002 UPDATE PA_BUDGET_LINES SET REVENUE = l_prj_revenue_tab(l_cost_cnt).amount
2003 WHERE
2004 RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id AND
2005 PERIOD_NAME =l_prj_revenue_tab(l_cost_cnt).period_name;
2006
2007 l_commit_cnt := l_commit_cnt + 1;
2008 IF l_commit_cnt >= l_commit_size THEN
2009 COMMIT;
2010 l_commit_cnt := 0;
2011 END IF;
2012 END LOOP;
2013 PA_DEBUG.g_err_stage := '2950: after updating PA_BUDGET_LINES for COST_BASED_REVENUE';
2014 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2015 ELSE
2016 l_plan_processing_code := 'E';
2017 PA_DEBUG.g_err_stage := '2900: bef upd PA_RES_ASG for err code n COST_BASED_REVENUE';
2018 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2019 UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = l_cost_based_error_code
2020 WHERE
2021 RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
2022 END IF;
2023 ELSE
2024 PA_DEBUG.g_err_stage := '2900: no prj value : bef updating err msg in Cost Based';
2025 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2026
2027 UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_NO_PRJ_VALUE'
2028 WHERE
2029 RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
2030
2031 l_plan_processing_code := 'E';
2032
2033 PA_DEBUG.g_err_stage := '2950: no prj value : aft updating err msg in Cost Based';
2034 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2035
2036 END IF; -- project value not null
2037 END IF; -- Rev gen method
2038 COMMIT;
2039 PA_DEBUG.g_err_stage := '3000: before updating PA_RESOURCE_ASSIGNMENTS for REVENUE';
2040 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2041
2042 /* Update Total Revenue in PA_RESOURCE_ASSIGNMENTS */
2043
2044 UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
2045 ( RA.TOTAL_PLAN_REVENUE,
2046 RA.TOTAL_PLAN_QUANTITY,
2047 RA.TOTAL_PLAN_RAW_COST,
2048 RA.TOTAL_PLAN_BURDENED_COST )
2049 = (SELECT SUM(BL.REVENUE),
2050 SUM(BL.QUANTITY),
2051 SUM(BL.RAW_COST),
2052 SUM(BL.BURDENED_COST) FROM
2053 PA_BUDGET_LINES BL WHERE
2054 BL.RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id )
2055 WHERE RA.RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id ;
2056
2057 UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
2058 AVERAGE_COST_RATE =
2059 DECODE(TOTAL_PLAN_BURDENED_COST,0,NULL,TOTAL_PLAN_BURDENED_COST ) / TOTAL_PLAN_QUANTITY,
2060 AVERAGE_BILL_RATE =
2061 DECODE(TOTAL_PLAN_REVENUE,0,NULL,TOTAL_PLAN_REVENUE ) / TOTAL_PLAN_QUANTITY
2062 WHERE
2063 BUDGET_VERSION_ID = l_budget_version_id AND
2064 TOTAL_PLAN_QUANTITY > 0;
2065
2066 /* Calculate the STD bill rate for the Project Level */
2067
2068 SELECT SUM( DECODE(STANDARD_BILL_RATE,0,NULL,STANDARD_BILL_RATE) * TOTAL_PLAN_QUANTITY )
2069 INTO l_prj_level_revenue
2070 FROM PA_RESOURCE_ASSIGNMENTS
2071 WHERE
2072 BUDGET_VERSION_ID = l_budget_version_id AND
2073 RESOURCE_ASSIGNMENT_ID <> l_prj_res_assignment_id;
2074
2075 IF l_tot_prj_quantity > 0 AND l_prj_level_revenue IS NOT NULL THEN
2076 UPDATE PA_RESOURCE_ASSIGNMENTS SET
2077 STANDARD_BILL_RATE = l_prj_level_revenue / l_tot_prj_quantity
2078 WHERE
2079 BUDGET_VERSION_ID = l_budget_version_id AND
2080 RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id;
2081 END IF;
2082
2083 UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
2084 AVERAGE_DISCOUNT_PERCENTAGE =
2085 ((STANDARD_BILL_RATE - AVERAGE_BILL_RATE)/STANDARD_BILL_RATE) * 100
2086 WHERE
2087 BUDGET_VERSION_ID = l_budget_version_id AND
2088 STANDARD_BILL_RATE <> 0 AND
2089 AVERAGE_BILL_RATE <> 0;
2090
2091 PA_DEBUG.g_err_stage := '3100: after updating PA_RESOURCE_ASSIGNMENTS for REVENUE';
2092 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2093
2094 PA_DEBUG.g_err_stage := '3200: before updating PA_BUDGET_VERSIONS for PLAN_PROCESSING_CODE';
2095 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2096
2097 UPDATE PA_BUDGET_VERSIONS SET PLAN_PROCESSING_CODE = l_plan_processing_code,
2098 PLAN_RUN_DATE = SYSDATE
2099 WHERE BUDGET_VERSION_ID = l_budget_version_id;
2100
2101 PA_DEBUG.g_err_stage := '3300: after updating PA_BUDGET_VERSIONS for PLAN_PROCESSING_CODE';
2102 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2103 ELSE -- else for l_budget_lines_tot_tbl.count greater than zero
2104 /* The budget version record will be deleted if no forecast lines are generated,
2105 this change is made to avoid the error from the page. */
2106 DELETE FROM PA_BUDGET_VERSIONS WHERE BUDGET_VERSION_ID = l_budget_version_id;
2107 END IF; -- end if for l_budget_lines_tot_tbl.count greater than zero
2108
2109 /* API call added for updating Average Bill rate in Project Assignments table. */
2110 l_prj_asg_id_tab.DELETE;
2111 l_avg_bill_rate_tab.DELETE;
2112 BEGIN
2113 /* we should avoid records with Project Assignment Id having the value of
2114 -1. This res asg record is used to store the project level totals. */
2115 SELECT project_assignment_id,
2116 ROUND(average_bill_rate,2) average_bill_rate
2117 BULK COLLECT INTO
2118 l_prj_asg_id_tab, l_avg_bill_rate_tab
2119 FROM pa_resource_assignments WHERE
2120 budget_version_id = l_budget_version_id AND
2121 project_assignment_id > 0 AND
2122 average_bill_rate IS NOT NULL;
2123 EXCEPTION
2124 WHEN NO_DATA_FOUND THEN
2125 NULL;
2126 END;
2127 IF l_prj_asg_id_tab.COUNT > 0 THEN
2128 PA_ASSIGNMENTS_PVT.Update_Revenue_Bill_Rate(
2129 p_assignment_id_tbl => l_prj_asg_id_tab,
2130 p_revenue_bill_rate_tbl => l_avg_bill_rate_tab,
2131 x_return_status => x_return_status );
2132 END IF;
2133 COMMIT;
2134
2135 PA_GENERATE_FORECAST_PUB.Set_Error_Details(
2136 p_return_status => l_ret_status,
2137 x_msg_count => l_msg_count,
2138 x_msg_data => l_msg_data,
2139 x_data => l_data,
2140 x_msg_index_out => l_msg_index_out );
2141 x_msg_count := l_msg_count;
2142 x_msg_data := l_msg_data;
2143 x_return_status := l_ret_status;
2144 PA_DEBUG.g_err_stage := '3400: after Commiting';
2145 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2146 PA_DEBUG.reset_err_stack;
2147 RETURN;
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2151 UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
2152 RAISE;
2153 END Generate_Forecast;
2154
2155 FUNCTION get_forecast_gen_date(p_project_id IN pa_projects_all.project_id%TYPE)
2156 RETURN DATE IS
2157 l_run_date DATE:= NULL;
2158 BEGIN
2159 SELECT plan_run_date INTO l_run_date
2160 FROM pa_budget_versions
2161 WHERE project_id = p_project_id AND
2162 budget_type_code = 'FORECASTING_BUDGET_TYPE';
2163 RETURN l_run_date;
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 RETURN l_run_date;
2167 END get_forecast_gen_date;
2168
2169 END PA_GENERATE_FORECAST_PUB;