[Home] [Help]
PACKAGE BODY: APPS.PA_FP_EDIT_LINE_PKG
Source
1 PACKAGE BODY PA_FP_EDIT_LINE_PKG AS
2 /* $Header: PAFPEDLB.pls 120.2 2005/09/26 11:26:14 rnamburi noship $ */
3
4 l_module_name VARCHAR2(30) := 'pa.plsql.PA_FP_EDIT_LINE_PKG';
5
6 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
7 -- Bug Fix: 4569365. Removed MRC code.
8 -- g_mrc_exception EXCEPTION;
9
10 /* Bug 2672548 - Populate_local_varaiables would derive PD/SD start and end dates by
11 calling DERIVE_PD_SD_START_END_DATES if they are not available in budget lines.
12 */
13
14 PROCEDURE POPULATE_LOCAL_VARIABLES(
15 p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
16 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
17 ,p_calling_context IN VARCHAR2
18 ,x_preceding_prd_start_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
19 ,x_preceding_prd_end_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
20 ,x_succeeding_prd_start_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
21 ,x_succeeding_prd_end_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
22 ,x_period_profile_start_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
23 ,x_period_profile_end_date OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
24 ,x_period_profile_id OUT NOCOPY pa_budget_versions.period_profile_id%TYPE --File.Sql.39 bug 4440895
25 ,x_time_phased_code OUT NOCOPY pa_proj_fp_options.cost_time_phased_code%TYPE --File.Sql.39 bug 4440895
26 ,x_fin_plan_version_id OUT NOCOPY pa_proj_fp_options.fin_plan_version_id%TYPE --File.Sql.39 bug 4440895
27 ,x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.cost_fin_plan_level_code%TYPE --File.Sql.39 bug 4440895
28 ,x_project_start_date OUT NOCOPY pa_projects_all.start_date%TYPE --File.Sql.39 bug 4440895
29 ,x_project_end_date OUT NOCOPY pa_projects_all.start_date%TYPE --File.Sql.39 bug 4440895
30 ,x_task_start_date OUT NOCOPY pa_projects_all.start_date%TYPE --File.Sql.39 bug 4440895
31 ,x_task_end_date OUT NOCOPY pa_projects_all.start_date%TYPE --File.Sql.39 bug 4440895
32 ,x_plan_period_type OUT NOCOPY pa_proj_period_profiles.plan_period_type%TYPE --File.Sql.39 bug 4440895
33 ,x_period_set_name OUT NOCOPY pa_proj_period_profiles.period_set_name%TYPE --File.Sql.39 bug 4440895
34 ,x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE --File.Sql.39 bug 4440895
35 ,x_projfunc_currency_code OUT NOCOPY pa_projects_all.projfunc_currency_code%TYPE --File.Sql.39 bug 4440895
36 ,x_project_id OUT NOCOPY pa_projects_all.project_id%TYPE --File.Sql.39 bug 4440895
37 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
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 IS
41
42 l_period_profile_type pa_proj_period_profiles.period_profile_type%type;
43 l_gl_period_type pa_proj_period_profiles.gl_period_type%TYPE;
44 l_number_of_periods pa_proj_period_profiles.number_of_periods%TYPE;
45
46 l_task_id pa_tasks.task_id%TYPE;
47
48 l_dummy_pd_name pa_budget_lines.period_name%TYPE; /* webadi */
49
50 l_msg_count NUMBER := 0;
51 l_data VARCHAR2(2000);
52 l_msg_data VARCHAR2(2000);
53 l_msg_index_out NUMBER;
54 l_return_status VARCHAR2(2000);
55 l_debug_mode VARCHAR2(30);
56
57
58 BEGIN
59 -- Set the error stack.
60
61 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.Populate_Local_Variables');
62
63 -- Get the Debug mode into local variable and set it to 'Y'if its NULL
64
65 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
66 l_debug_mode := NVL(l_debug_mode, 'Y');
67
68 -- Initialize the return status to success
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
71
72 pa_debug.g_err_stage := 'In PA_FP_EDIT_LINE_PKG.Populate_Local_Variables ';
73 IF P_PA_DEBUG_MODE = 'Y' THEN
74 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
75 END IF;
76
77 /* Check for Budget Version ID not being NULL. */
78
79 pa_debug.g_err_stage := 'Getting the value of the version id profile id etc.';
80 IF P_PA_DEBUG_MODE = 'Y' THEN
81 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
82 END IF;
83
84
85 SELECT pra.budget_version_id
86 ,pbv.period_profile_id
87 ,pra.project_id
88 ,pra.task_id
89 INTO x_fin_plan_version_id
90 ,x_period_profile_id
91 ,x_project_id
92 ,l_task_id
93 FROM pa_resource_assignments pra,
94 pa_budget_versions pbv
95 WHERE pra.resource_assignment_id = p_resource_assignment_id
96 AND pra.budget_version_id = pbv.budget_version_id;
97
98 pa_debug.g_err_stage := 'getting x_time_phased_code ';
99 IF P_PA_DEBUG_MODE = 'Y' THEN
100 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
101 END IF;
102
103 x_time_phased_code := pa_fin_plan_utils.get_time_phased_code(
104 p_fin_plan_version_id => x_fin_plan_version_id);
105
106 pa_debug.g_err_stage := ':x_time_phased_code = ' || x_time_phased_code;
107 IF P_PA_DEBUG_MODE = 'Y' THEN
108 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
109 END IF;
110
111 x_fin_plan_level_code := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(
112 p_fin_plan_version_id => x_fin_plan_version_id);
113
114 pa_debug.g_err_stage := 'x_fin_plan_level_code = ' || x_fin_plan_level_code;
115 IF P_PA_DEBUG_MODE = 'Y' THEN
116 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
117 END IF;
118
119 IF x_period_profile_id IS NOT NULL THEN
120
121 pa_debug.g_err_stage := 'calling pa_prj_period_profile_utils.get_prj_period_profile_dtls.';
122 IF P_PA_DEBUG_MODE = 'Y' THEN
123 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
124 END IF;
125
126 PA_PRJ_PERIOD_PROFILE_UTILS.GET_PRJ_PERIOD_PROFILE_DTLS(
127 p_period_profile_id => x_period_profile_id
128 ,p_debug_mode => 'Y'
129 ,p_add_msg_in_stack => 'Y'
130 ,x_period_profile_type => l_period_profile_type
131 ,x_plan_period_type => x_plan_period_type
132 ,x_period_set_name => x_period_set_name
133 ,x_gl_period_type => l_gl_period_type
134 ,x_plan_start_date => x_period_profile_start_date
135 ,x_plan_end_date => x_period_profile_end_date
136 ,x_number_of_periods => l_number_of_periods
137 ,x_return_status => l_return_status
138 ,x_msg_data => l_msg_data );
139
140 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
141 RAISE FND_API.G_EXC_ERROR;
142 /* PA_PRJ_PERIOD_PROFILE_UTILS.GET_PRJ_PERIOD_PROFILE_DTLS doesn't
143 raise error and hence this error trapping is done */
144 END IF;
145
146 IF P_PA_DEBUG_MODE = 'Y' THEN
147 pa_debug.g_err_stage := 'calling pa_fin_plan_utils.get_peceding_suceeding_pd_info.';
148 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
149 END IF;
150
151 /* Calling DERIVE_PD_SD_START_END_DATES to get/dervie the PD/SD start and end dates */
152
153 PA_FP_EDIT_LINE_PKG.DERIVE_PD_SD_START_END_DATES
154 (p_calling_context => p_calling_context
155 ,p_pp_st_dt => x_period_profile_start_date
156 ,p_pp_end_dt => x_period_profile_end_date
157 ,p_plan_period_type => x_plan_period_type
158 ,p_resource_assignment_id => p_resource_assignment_id
159 ,p_transaction_currency_code => p_txn_currency_code
160 ,x_pd_st_dt => x_preceding_prd_start_date
161 ,x_pd_end_dt => x_preceding_prd_end_date
162 ,x_pd_period_name => l_dummy_pd_name /* webadi */
163 ,x_sd_st_dt => x_succeeding_prd_start_date
164 ,x_sd_end_dt => x_succeeding_prd_end_date
165 ,x_sd_period_name => l_dummy_pd_name /* webadi */
166 ,x_return_status => x_return_status
167 ,x_msg_count => x_msg_count
168 ,x_msg_data => x_msg_data);
169
170 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
171 IF P_PA_DEBUG_MODE = 'Y' THEN
172 pa_debug.g_err_stage := 'Call to derive_pd_sd_start_end_dates errored... ';
173 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
174 END IF;
175 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
176 END IF;
177 END IF;
178
179 /* This program assumes that in case time phased is by PA or GL
180 then period profile id will not be null.
181 */
182 IF nvl(x_project_id,0) <> 0 THEN
183
184 pa_debug.g_err_stage := 'getting project start and end dates.';
185 IF P_PA_DEBUG_MODE = 'Y' THEN
186 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
187 END IF;
188
189 SELECT start_date
190 ,completion_date
191 ,project_currency_code
192 ,projfunc_currency_code
193 INTO x_project_start_date
194 ,x_project_end_date
195 ,x_project_currency_code
196 ,x_projfunc_currency_code
197 FROM pa_projects_all p
198 WHERE p.project_id = x_project_id;
199
200 END IF;
201
202 IF nvl(l_task_id,0) <> 0 THEN
203
204 pa_debug.g_err_stage := 'getting task start and end dates.';
205 IF P_PA_DEBUG_MODE = 'Y' THEN
206 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.g_err_stage,3);
207 END IF;
208
209 SELECT start_date
210 ,completion_date
211 INTO x_task_start_date
212 ,x_task_end_date
213 FROM pa_tasks pt
214 WHERE pt.task_id = l_task_id;
215
216 END IF;
217
218 pa_debug.reset_err_stack; /* Bug 2699888 */
219
220 EXCEPTION
221 WHEN FND_API.G_EXC_ERROR or PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 l_msg_count := FND_MSG_PUB.count_msg;
224 IF l_msg_count = 1 THEN
225 PA_INTERFACE_UTILS_PUB.get_messages
226 (p_encoded => FND_API.G_TRUE,
227 p_msg_index => 1,
228 p_msg_count => l_msg_count,
229 p_msg_data => l_msg_data,
230 p_data => l_data,
231 p_msg_index_out => l_msg_index_out);
232
233 x_msg_data := l_data;
234 x_msg_count := l_msg_count;
235 ELSE
236 x_msg_count := l_msg_count;
237 END IF;
238
239 IF P_PA_DEBUG_MODE = 'Y' THEN
240 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,'Invalid arguments passed or some expected error',5);
241 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.G_Err_Stack,5);
242 END IF;
243 pa_debug.reset_err_stack;
244
245 RAISE;
246
247 WHEN OTHERS THEN
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 x_msg_count := 1;
250 x_msg_data := SQLERRM;
251 FND_MSG_PUB.add_exc_msg
252 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG.Populate_Local_Variables'
253 ,p_procedure_name => pa_debug.G_Err_Stack );
254 IF P_PA_DEBUG_MODE = 'Y' THEN
255 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,SQLERRM,5);
256 pa_debug.write('POPULATE_LOCAL_VARIABLES: ' || l_module_name,pa_debug.G_Err_Stack,5);
257 END IF;
258 pa_debug.reset_err_stack;
259
260 raise FND_API.G_EXC_UNEXPECTED_ERROR;
261 END POPULATE_LOCAL_VARIABLES;
262
263 /* This API first looks into pa_budget_lines to see if PD/SD records for the resource assignment id
264 and txn currency code can be found. If found it returns these values (done in
265 PA_FIN_PLAN_UTILS.GET_PECEDING_SUCEEDING_PD_INFO). If these values cannot be found in pa_budget_lines
266 then if context is not view then this API derives the PD/SD dates based upon the business rules.
267 */
268
269 PROCEDURE DERIVE_PD_SD_START_END_DATES
270 (p_calling_context IN VARCHAR2
271 ,p_pp_st_dt IN pa_budget_lines.start_date%TYPE
272 ,p_pp_end_dt IN pa_budget_lines.start_date%TYPE
273 ,p_plan_period_type IN pa_proj_period_profiles.plan_period_type%TYPE
274 ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
275 ,p_transaction_currency_code IN pa_budget_lines.txn_currency_code%TYPE
276 ,x_pd_st_dt OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
277 ,x_pd_end_dt OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
278 ,x_pd_period_name OUT NOCOPY pa_budget_lines.period_name%TYPE /* webadi */ --File.Sql.39 bug 4440895
279 ,x_sd_st_dt OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
280 ,x_sd_end_dt OUT NOCOPY pa_budget_lines.start_date%TYPE --File.Sql.39 bug 4440895
281 ,x_sd_period_name OUT NOCOPY pa_budget_lines.period_name%TYPE /* webadi */ --File.Sql.39 bug 4440895
282 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
283 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
284 ,x_msg_data OUT NOCOPY VARCHAR2) AS --File.Sql.39 bug 4440895
285
286 l_msg_count NUMBER := 0;
287 l_data VARCHAR2(2000);
288 l_msg_data VARCHAR2(2000);
289 l_msg_index_out NUMBER;
290 l_dummy_pd_name VARCHAR2(30);
291
292 l_project_id PA_PROJECTS_ALL.project_id%TYPE;
293 l_budget_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
294 l_time_phased_code PA_PROJ_FP_OPTIONS.all_time_phased_code%TYPE;
295 l_project_start_date DATE;
296 l_project_end_date DATE;
297 l_proj_start_prd_start_dt DATE;
298 l_proj_end_prd_end_dt DATE;
299
300 BEGIN
301
302 x_msg_count := 0;
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 IF p_pa_debug_mode = 'Y' THEN
306 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.DERIVE_PD_SD_START_END_DATES');
307 pa_debug.set_process('PLSQL','LOG',p_pa_debug_mode);
308 END IF;
309
310 -- Check for business rules violations
311
312 IF p_pa_debug_mode = 'Y' THEN
313 pa_debug.g_err_stage:= 'Validating input parameters';
314 pa_debug.write(l_module_name,pa_debug.g_err_stage,
315 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
316 END IF;
317
318 IF p_calling_context IS NULL or
319 p_pp_st_dt IS NULL or
320 p_pp_end_dt IS NULL or
321 p_plan_period_type IS NULL or
322 p_resource_assignment_id IS NULL THEN
323
324 IF p_pa_debug_mode = 'Y' THEN
325 pa_debug.g_err_stage:= 'p_calling_context = '|| p_calling_context;
326 pa_debug.write(l_module_name,pa_debug.g_err_stage,
327 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
328 pa_debug.g_err_stage:= 'p_pp_st_dt = '|| p_pp_st_dt;
329 pa_debug.write(l_module_name,pa_debug.g_err_stage,
330 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
331 pa_debug.g_err_stage:= 'p_pp_end_dt = '|| p_pp_end_dt;
332 pa_debug.write(l_module_name,pa_debug.g_err_stage,
333 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
334 pa_debug.g_err_stage:= 'p_plan_period_type = '|| p_plan_period_type;
335 pa_debug.write(l_module_name,pa_debug.g_err_stage,
336 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
337 pa_debug.g_err_stage:= 'p_resource_assignment_id = '|| p_resource_assignment_id;
338 pa_debug.write(l_module_name,pa_debug.g_err_stage,
339 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
340 pa_debug.g_err_stage:= 'p_transaction_currency_code = '|| p_transaction_currency_code;
341 pa_debug.write(l_module_name,pa_debug.g_err_stage,
342 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
343
344 PA_UTILS.ADD_MESSAGE
345 (p_app_short_name => 'PA',
346 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
347 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
348 END IF;
349 END IF;
350
351
352 /* Txn currency code can be Null when the context is G_CALLING_CONTEXT_OTHER_CURR and
353 so checking txn curr code only for other cases */
354
355 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR and
356 p_transaction_currency_code IS NULL THEN
357 IF p_pa_debug_mode = 'Y' THEN
358 pa_debug.g_err_stage:= 'p_calling_context = ' || p_calling_context;
359 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
360 pa_debug.g_err_stage:= 'p_transaction_currency_code is NULL';
361 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
362 END IF;
363 PA_UTILS.ADD_MESSAGE
364 (p_app_short_name => 'PA',
365 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
366 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
367 END IF;
368
369 BEGIN
370 SELECT project_id, budget_version_id
371 INTO l_project_id, l_budget_version_id
372 FROM PA_RESOURCE_ASSIGNMENTS
373 WHERE RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 IF p_pa_debug_mode = 'Y' THEN
378 pa_debug.g_err_stage:= 'Error while selecting for the input resource assignment id ' ||
379 p_resource_assignment_id;
380 pa_debug.write(l_module_name,pa_debug.g_err_stage || SQLERRM,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
381 END IF;
382 RAISE;
383 END;
384
385 /* In case of calling context being G_CALLING_CONTEXT_OTHER_CURR, get_peceding_suceeding_pd_info
386 need not be called since in this context we are sure there would no budget lines for the resource
387 assignment id and also the txn curr code would be Null */
388
389 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
390
391 IF P_PA_DEBUG_MODE = 'Y' THEN
392 pa_debug.g_err_stage := 'Calling get_peceding_suceeding_pd_info ... ';
393 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
394 END IF;
395
396 PA_FIN_PLAN_UTILS.GET_PECEDING_SUCEEDING_PD_INFO (
397 p_resource_assignment_id => p_resource_assignment_id
398 ,p_txn_currency_code => p_transaction_currency_code
399 ,x_preceding_prd_start_date => x_pd_st_dt
400 ,x_preceding_prd_end_date => x_pd_end_dt
401 ,x_succeeding_prd_start_date => x_sd_st_dt
402 ,x_succeeding_prd_end_date => x_sd_end_dt
403 ,x_return_status => x_return_status
404 ,x_msg_count => x_msg_count
405 ,x_msg_data => x_msg_data);
406
407 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408 IF P_PA_DEBUG_MODE = 'Y' THEN
409 pa_debug.g_err_stage := 'Call to get_peceding_suceeding_pd_info errored... ';
410 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
411 END IF;
412 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
413 END IF;
414
415 END IF; /* IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN */
416
417
418 /* In following block we will be deriving PD SD start and end date when these are not available in
419 budget lines table. This needs to be done only when mode is not view because in view mode only
420 those lines need to be displayed which are in budget lines table. */
421
422 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_VIEW THEN
423
424 /* In this block we will be deriving start/end date of the period in which project start/end
425 date falls. These dates are required later to derive PD/SD start dates */
426
427 IF (x_pd_st_dt IS NULL or x_sd_st_dt IS NULL) THEN
428
429 BEGIN
430 SELECT start_date
431 ,completion_date
432 INTO l_project_start_date
433 ,l_project_end_date
434 FROM pa_projects_all p
435 WHERE p.project_id = l_project_id;
436 EXCEPTION
437 WHEN OTHERS THEN
438 IF P_PA_DEBUG_MODE = 'Y' THEN
439 pa_debug.g_err_stage := 'Error while selecting for the project id ' || l_project_id;
440 pa_debug.write(l_module_name,pa_debug.g_err_stage || ' ' || SQLERRM,5);
441 END IF;
442 Raise;
443 END;
444
445 IF P_PA_DEBUG_MODE = 'Y' THEN
446 pa_debug.g_err_stage := 'Calling pa_fin_plan_utils apis...';
447 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
448 END IF;
449
450 l_time_phased_code := pa_fin_plan_utils.get_time_phased_code(
451 p_fin_plan_version_id => l_budget_version_id);
452
453 /* Bug : 2644537 Assigning the project start and end dates when proj start, end dates
454 do not fall in any period */
455
456 IF l_project_start_date IS NOT NULL THEN
457
458 l_proj_start_prd_start_dt := pa_fin_plan_utils.get_period_start_date(
459 p_input_date => l_project_start_date,
460 p_time_phased_code => l_time_phased_code);
461
462 IF l_proj_start_prd_start_dt IS NULL THEN
463 l_proj_start_prd_start_dt := l_project_start_date;
464 END IF;
465
466 END IF;
467
468 IF l_project_end_date IS NOT NULL THEN
469 l_proj_end_prd_end_dt := pa_fin_plan_utils.get_period_end_date (
470 p_input_date => l_project_end_date,
471 p_time_phased_code => l_time_phased_code);
472
473 IF l_proj_end_prd_end_dt IS NULL THEN
474 l_proj_end_prd_end_dt := l_project_end_date;
475 END IF;
476
477 END IF;
478
479 END IF;
480
481 /* Derive PD dates when these could not be found in pa_budget_lines. */
482
483 IF x_pd_st_dt IS NULL THEN /* i.e. PD record could not be found in budget lines */
484
485 IF P_PA_DEBUG_MODE = 'Y' THEN
486 pa_debug.g_err_stage := 'Preceding_prd_start_date IS NULL... ';
487 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
488 END IF;
489
490 /* We need to derive PD period only if either project start is null or if period profile
491 starts after the project start date. (Business rule for creating pd period.) */
492
493 IF l_proj_start_prd_start_dt IS NULL OR p_pp_st_dt > l_proj_start_prd_start_dt THEN
494
495 /* In the context of G_CALLING_CONTEXT_OTHER_CURR, p_txn_curr_code would be Null and
496 we are sure there would be no budget lines and hence the following update need not
497 be done */
498
499 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
500 UPDATE PA_BUDGET_LINES
501 SET BUCKETING_PERIOD_CODE = NULL
502 WHERE TXN_CURRENCY_CODE = p_transaction_currency_code
503 AND RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id
504 AND BUDGET_VERSION_ID = l_budget_version_id
505 AND BUCKETING_PERIOD_CODE = PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PE;
506 END IF;
507
508 IF P_PA_DEBUG_MODE = 'Y' THEN
509 pa_debug.g_err_stage := 'calling get_period_info ...';
510 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
511 END IF;
512
513 PA_PLAN_MATRIX.GET_PERIOD_INFO
514 (p_bucketing_period_code => PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD
515 ,p_st_dt_4_st_pd => p_pp_st_dt
516 ,p_st_dt_4_end_pd => p_pp_end_dt
517 ,p_plan_period_type => p_plan_period_type
518 ,p_project_id => l_project_id
519 ,p_budget_version_id => l_budget_version_id
520 ,p_resource_assignment_id => p_resource_assignment_id
521 ,p_transaction_currency_code => p_transaction_currency_code
522 ,x_start_date => x_pd_st_dt
523 ,x_end_date => x_pd_end_dt
524 ,x_period_name => x_pd_period_name /* webadi */
525 ,x_return_status => x_return_status
526 ,x_msg_count => x_msg_count
527 ,x_msg_data => x_msg_data);
528
529 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
530 IF P_PA_DEBUG_MODE = 'Y' THEN
531 pa_debug.g_err_stage := 'calling get_period_info - FAILED...';
532 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
533 END IF;
534 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
535 END IF;
536
537 END IF; /* l_proj_start_prd_start_dt IS NULL OR p_pp_st_dt > l_proj_start_prd_start_dt */
538
539 END IF; /* IF x_pd_st_dt IS NULL THEN */
540
541 /* Derive SD dates when these could not be found in pa_budget_lines. */
542
543 IF x_sd_st_dt IS NULL THEN
544
545 IF P_PA_DEBUG_MODE = 'Y' THEN
546 pa_debug.g_err_stage := 'Succeeding_prd_start_date IS NULL... ';
547 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
548 END IF;
549
550 /* We need to derive SD period only if either project end date is null or if period profile
551 ends before the project end date. (Business rule for creating sd period.) */
552
553 IF l_proj_end_prd_end_dt IS NULL OR p_pp_end_dt < l_proj_end_prd_end_dt THEN
554
555 /* In the context of G_CALLING_CONTEXT_OTHER_CURR, p_txn_curr_code would be Null and
556 we are sure there would be no budget lines and hence the following update need not
557 be done */
558
559 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
560 UPDATE PA_BUDGET_LINES
561 SET BUCKETING_PERIOD_CODE = NULL
562 WHERE TXN_CURRENCY_CODE = p_transaction_currency_code
563 AND RESOURCE_ASSIGNMENT_ID = p_resource_assignment_id
564 AND BUDGET_VERSION_ID = l_budget_version_id
565 AND BUCKETING_PERIOD_CODE = PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SE;
566 END IF;
567
568 IF P_PA_DEBUG_MODE = 'Y' THEN
569 pa_debug.g_err_stage := 'calling get_period_info ...';
570 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
571 END IF;
572
573 PA_PLAN_MATRIX.GET_PERIOD_INFO
574 (p_bucketing_period_code => PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD
575 ,p_st_dt_4_st_pd => p_pp_st_dt
576 ,p_st_dt_4_end_pd => p_pp_end_dt
577 ,p_plan_period_type => p_plan_period_type
578 ,p_project_id => l_project_id
579 ,p_budget_version_id => l_budget_version_id
580 ,p_resource_assignment_id => p_resource_assignment_id
581 ,p_transaction_currency_code => p_transaction_currency_code
582 ,x_start_date => x_sd_st_dt
583 ,x_end_date => x_sd_end_dt
584 ,x_period_name => x_sd_period_name /* webadi */
585 ,x_return_status => x_return_status
586 ,x_msg_count => x_msg_count
587 ,x_msg_data => x_msg_data);
588
589 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
590 IF P_PA_DEBUG_MODE = 'Y' THEN
591 pa_debug.g_err_stage := 'calling get_period_info - FAILED...';
592 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
593 END IF;
594 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
595 END IF;
596
597 END IF; /* l_proj_end_prd_end_dt IS NULL OR p_pp_end_dt < l_proj_end_prd_end_dt */
598
599 END IF; /* x_sd_st_dt IS NULL */
600
601 END IF; /* p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_VIEW */
602
603 IF p_pa_debug_mode = 'Y' THEN
604 pa_debug.g_err_stage:= 'Exiting DERIVE_PD_SD_START_END_DATES';
605 pa_debug.write(l_module_name,pa_debug.g_err_stage,
606 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
607 pa_debug.reset_err_stack;
608 END IF;
609
610 EXCEPTION
611
612 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
613
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 l_msg_count := FND_MSG_PUB.count_msg;
616
617 IF l_msg_count = 1 THEN
618 PA_INTERFACE_UTILS_PUB.get_messages
619 (p_encoded => FND_API.G_TRUE
620 ,p_msg_index => 1
621 ,p_msg_count => l_msg_count
622 ,p_msg_data => l_msg_data
623 ,p_data => l_data
624 ,p_msg_index_out => l_msg_index_out);
625 x_msg_data := l_data;
626 x_msg_count := l_msg_count;
627 ELSE
628 x_msg_count := l_msg_count;
629 END IF;
630
631 IF p_pa_debug_mode = 'Y' THEN
632 pa_debug.reset_err_stack;
633 END IF;
634 RAISE;
635
636 WHEN OTHERS THEN
637
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 x_msg_count := 1;
640 x_msg_data := SQLERRM;
641 FND_MSG_PUB.add_exc_msg
642 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG'
643 ,p_procedure_name => 'DERIVE_PD_SD_START_END_DATES'
644 ,p_error_text => x_msg_data);
645
646 IF p_pa_debug_mode = 'Y' THEN
647 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
648 pa_debug.write(l_module_name,pa_debug.g_err_stage,
649 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
650 pa_debug.reset_err_stack;
651 END IF;
652 RAISE;
653
654 END DERIVE_PD_SD_START_END_DATES;
655
656 PROCEDURE POPULATE_ROLLUP_TMP(
657 p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
658 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
659 ,p_calling_context IN VARCHAR2
660 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
661 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
662 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
663
664 IS
665 CURSOR MC_CUR(p_fin_plan_version_id IN NUMBER, p_txn_currency_code IN VARCHAR2) IS
666
667 SELECT
668 pfo.PROJECT_COST_RATE_TYPE
669 ,decode(pfo.PROJECT_COST_RATE_TYPE,'User',pftc.PROJECT_COST_EXCHANGE_RATE,null) PROJECT_COST_EXCHANGE_RATE
670 ,pfo.PROJECT_COST_RATE_DATE_TYPE
671 ,pfo.PROJECT_COST_RATE_DATE
672 ,pfo.PROJECT_REV_RATE_TYPE
673 ,decode(pfo.PROJECT_REV_RATE_TYPE,'User',pftc.PROJECT_REV_EXCHANGE_RATE,null) PROJECT_REV_EXCHANGE_RATE
674 ,pfo.PROJECT_REV_RATE_DATE_TYPE
675 ,pfo.PROJECT_REV_RATE_DATE
676 ,pfo.PROJFUNC_COST_RATE_TYPE
677 ,decode(pfo.PROJFUNC_COST_RATE_TYPE,'User',pftc.PROJFUNC_COST_EXCHANGE_RATE,null) PROJFUNC_COST_EXCHANGE_RATE
678 ,pfo.PROJFUNC_COST_RATE_DATE_TYPE
679 ,pfo.PROJFUNC_COST_RATE_DATE
680 ,pfo.PROJFUNC_REV_RATE_TYPE
681 ,decode(pfo.PROJFUNC_REV_RATE_TYPE,'User',pftc.PROJFUNC_REV_EXCHANGE_RATE,null) PROJFUNC_REV_EXCHANGE_RATE
682 ,pfo.PROJFUNC_REV_RATE_DATE_TYPE
683 ,pfo.PROJFUNC_REV_RATE_DATE
684 FROM pa_proj_fp_options pfo
685 ,pa_fp_txn_currencies pftc
686 WHERE pfo.proj_fp_options_id = pftc.proj_fp_options_id(+)
687 AND pfo.fin_plan_version_id = p_fin_plan_version_id
688 AND pftc.txn_currency_code(+) = p_txn_currency_code;
689
690 l_preceding_prd_start_date pa_budget_lines.start_date%TYPE;
691 l_preceding_prd_end_date pa_budget_lines.start_date%TYPE;
692 l_succeeding_prd_start_date pa_budget_lines.start_date%TYPE;
693 l_succeeding_prd_end_date pa_budget_lines.start_date%TYPE;
694 l_period_profile_start_date pa_budget_lines.start_date%TYPE;
695 l_period_profile_end_date pa_budget_lines.start_date%TYPE;
696 l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
697 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
698 l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
699 l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
700 l_project_start_date pa_projects_all.start_date%TYPE;
701 l_project_end_date pa_projects_all.start_date%TYPE;
702 l_task_start_date pa_projects_all.start_date%TYPE;
703 l_task_end_date pa_projects_all.start_date%TYPE;
704 l_plan_period_type pa_proj_period_profiles.plan_period_type%TYPE;
705 l_period_set_name pa_proj_period_profiles.period_set_name%TYPE;
706 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
707 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
708 l_project_id pa_projects_all.project_id%TYPE;
709 l_dummy_period_name pa_periods_all.period_name%TYPE;
710
711 l_msg_count NUMBER := 0;
712 l_msg_data VARCHAR2(2000);
713 l_return_status VARCHAR2(2000);
714
715 l_data VARCHAR2(2000);
716 l_msg_index_out NUMBER;
717 l_debug_mode VARCHAR2(10);
718 l_stage NUMBER := 100;
719
720 l_preceding_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
721 l_succeeding_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
722 l_preceding_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
723 l_succeeding_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
724 l_preceding_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
725 l_succeeding_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
726 l_preceding_quantity pa_proj_periods_denorm.preceding_periods_amount%TYPE;
727 l_succeeding_quantity pa_proj_periods_denorm.preceding_periods_amount%TYPE;
728
729 l_pd_pc_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
730 l_pd_pfc_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
731 l_sd_pc_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
732 l_sd_pfc_raw_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
733 l_pd_pc_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
734 l_pd_pfc_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
735 l_sd_pc_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
736 l_sd_pfc_burdened_cost pa_proj_periods_denorm.preceding_periods_amount%TYPE;
737 l_pd_pc_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
738 l_pd_pfc_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
739 l_sd_pc_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
740 l_sd_pfc_revenue pa_proj_periods_denorm.preceding_periods_amount%TYPE;
741
742 l_preceding_period_text fnd_new_messages.message_text%TYPE := fnd_message.GET_STRING('PA','PA_FP_PREC_PERIOD_AMOUNT');
743 l_succeeding_period_text fnd_new_messages.message_text%TYPE := fnd_message.GET_STRING('PA','PA_FP_SUCC_PERIOD_AMOUNT');
744
745 l_count NUMBER;
746 mc_cur_rec MC_CUR%ROWTYPE;
747
748 /* Added for enhancement # 2604957 */
749 l_min_start_date pa_budget_lines.start_date%TYPE;
750 l_max_start_date pa_budget_lines.start_date%TYPE;
751
752 /* Added for enhancement # 2593167 */
753 l_proj_start_prd_start_dt pa_budget_lines.start_date%TYPE;
754 l_proj_end_prd_end_dt pa_budget_lines.start_date%TYPE;
755
756 PROCEDURE insert_dummy_record_pvt (mc_rec IN mc_cur_rec%TYPE)
757 IS
758 BEGIN
759 INSERT INTO PA_FP_ROLLUP_TMP
760 (ROLLUP_ID
761 ,RESOURCE_ASSIGNMENT_ID
762 ,BUDGET_LINE_ID
763 ,OLD_START_DATE
764 ,START_DATE
765 ,END_DATE
766 ,PERIOD_NAME
767 ,txn_currency_code
768 ,old_quantity
769 ,old_txn_raw_cost
770 ,old_txn_burdened_cost
771 ,old_txn_revenue
772 ,quantity
773 ,txn_raw_cost
774 ,txn_burdened_cost
775 ,txn_revenue
776 ,bucketing_period_code
777 ,delete_flag
778 ,parent_assignment_id
779 ,project_currency_code
780 ,projfunc_currency_code
781 ,PROJECT_COST_RATE_TYPE
782 ,PROJECT_COST_EXCHANGE_RATE
783 ,PROJECT_COST_RATE_DATE_TYPE
784 ,PROJECT_COST_RATE_DATE
785 ,PROJECT_REV_RATE_TYPE
786 ,PROJECT_REV_EXCHANGE_RATE
787 ,PROJECT_REV_RATE_DATE_TYPE
788 ,PROJECT_REV_RATE_DATE
789 ,PROJFUNC_COST_RATE_TYPE
790 ,PROJFUNC_COST_EXCHANGE_RATE
791 ,PROJFUNC_COST_RATE_DATE_TYPE
792 ,PROJFUNC_COST_RATE_DATE
793 ,PROJFUNC_REV_RATE_TYPE
794 ,PROJFUNC_REV_EXCHANGE_RATE
795 ,PROJFUNC_REV_RATE_DATE_TYPE
796 ,PROJFUNC_REV_RATE_DATE
797 )
798 SELECT pa_fp_rollup_tmp_s.nextval
799 ,p_resource_assignment_id
800 ,NULL /* BUDGET_LINE_ID */
801 ,NULL
802 ,NULL
803 ,NULL
804 ,NULL /* period name */
805 ,p_txn_currency_code
806 ,NULL
807 ,NULL
808 ,NULL
809 ,NULL
810 ,NULL
811 ,NULL
812 ,NULL
813 ,NULL
814 ,NULL
815 ,'N'
816 ,parent_assignment_id
817 ,l_project_currency_code
818 ,l_projfunc_currency_code
819 ,mc_rec.PROJECT_COST_RATE_TYPE
820 ,mc_rec.PROJECT_COST_EXCHANGE_RATE
821 ,mc_rec.PROJECT_COST_RATE_DATE_TYPE
822 ,mc_rec.PROJECT_COST_RATE_DATE
823 ,mc_rec.PROJECT_REV_RATE_TYPE
824 ,mc_rec.PROJECT_REV_EXCHANGE_RATE
825 ,mc_rec.PROJECT_REV_RATE_DATE_TYPE
826 ,mc_rec.PROJECT_REV_RATE_DATE
827 ,mc_rec.PROJFUNC_COST_RATE_TYPE
828 ,mc_rec.PROJFUNC_COST_EXCHANGE_RATE
829 ,mc_rec.PROJFUNC_COST_RATE_DATE_TYPE
830 ,mc_rec.PROJFUNC_COST_RATE_DATE
831 ,mc_rec.PROJFUNC_REV_RATE_TYPE
832 ,mc_rec.PROJFUNC_REV_EXCHANGE_RATE
833 ,mc_rec.PROJFUNC_REV_RATE_DATE_TYPE
834 ,mc_rec.PROJFUNC_REV_RATE_DATE
835 FROM pa_resource_assignments pra
836 where resource_assignment_id = p_resource_assignment_id;
837
838 END insert_dummy_record_pvt;
839
840 BEGIN
841 -- Set the error stack.
842 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.POPULATE_ROLLUP_TMP');
843
844 -- Get the Debug mode into local variable and set it to 'Y' if its NULL
845 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
846 l_debug_mode := NVL(l_debug_mode, 'Y');
847
848 -- Initialize the return status to success
849 /* #2598389: Uncommented the following assignment. */
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851
852 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
853
854 -- Validating input parameters
855 IF p_calling_context IS NULL THEN
856
857 pa_debug.g_err_stage := 'calling context is null.';
858 IF P_PA_DEBUG_MODE = 'Y' THEN
859 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
860 END IF;
861
862 x_return_status := FND_API.G_RET_STS_ERROR;
863
864 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
865 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
866
867 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
868
869 ELSIF p_calling_context = PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
870
871 IF p_resource_assignment_id IS NULL THEN
872
873 pa_debug.g_err_stage := 'resource assignment id is null in edit in another currency.';
874 IF P_PA_DEBUG_MODE = 'Y' THEN
875 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
876 END IF;
877
878 x_return_status := FND_API.G_RET_STS_ERROR;
879
880 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
881 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
882
883 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
884 END IF;
885 ELSE
886
887 IF p_resource_assignment_id IS NULL OR
888 p_txn_currency_code IS NULL
889 THEN
890 pa_debug.g_err_stage := 'one of the input parameter is null.';
891 IF P_PA_DEBUG_MODE = 'Y' THEN
892 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
893 END IF;
894
895
896 pa_debug.g_err_stage := 'p_resource_assignment_id = ' || p_resource_assignment_id;
897 IF P_PA_DEBUG_MODE = 'Y' THEN
898 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
899 END IF;
900
901 pa_debug.g_err_stage := 'p_txn_currency_code = ' || p_txn_currency_code;
902 IF P_PA_DEBUG_MODE = 'Y' THEN
903 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
904 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
905 END IF;
906
907 x_return_status := FND_API.G_RET_STS_ERROR;
908
909 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
910 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
911
912 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
913 END IF;
914 END IF;
915
916 pa_debug.g_err_stage := 'p_calling_context = ' || p_calling_context;
917 IF P_PA_DEBUG_MODE = 'Y' THEN
918 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
919 END IF;
920
921 pa_debug.g_err_stage := 'p_resource_assignment_id = ' || p_resource_assignment_id;
922 IF P_PA_DEBUG_MODE = 'Y' THEN
923 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
924 END IF;
925
926 pa_debug.g_err_stage := 'p_txn_currency_code = ' || p_txn_currency_code;
927 IF P_PA_DEBUG_MODE = 'Y' THEN
928 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,5);
929 END IF;
930
931 pa_debug.g_err_stage := 'calling populate local variables';
932 IF P_PA_DEBUG_MODE = 'Y' THEN
933 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
934 END IF;
935
936 POPULATE_LOCAL_VARIABLES(
937 p_resource_assignment_id => p_resource_assignment_id
938 ,p_txn_currency_code => p_txn_currency_code
939 ,p_calling_context => p_calling_context
940 ,x_preceding_prd_start_date => l_preceding_prd_start_date
941 ,x_preceding_prd_end_date => l_preceding_prd_end_date
942 ,x_succeeding_prd_start_date => l_succeeding_prd_start_date
943 ,x_succeeding_prd_end_date => l_succeeding_prd_end_date
944 ,x_period_profile_start_date => l_period_profile_start_date
945 ,x_period_profile_end_date => l_period_profile_end_date
946 ,x_period_profile_id => l_period_profile_id
947 ,x_time_phased_code => l_time_phased_code
948 ,x_fin_plan_version_id => l_fin_plan_version_id
949 ,x_fin_plan_level_code => l_fin_plan_level_code
950 ,x_project_start_date => l_project_start_date
951 ,x_project_end_date => l_project_end_date
952 ,x_task_start_date => l_task_start_date
953 ,x_task_end_date => l_task_end_date
954 ,x_plan_period_type => l_plan_period_type
955 ,x_period_set_name => l_period_set_name
956 ,x_project_currency_code => l_project_currency_code
957 ,x_projfunc_currency_code => l_projfunc_currency_code
958 ,x_project_id => l_project_id
959 ,x_return_status => l_return_status
960 ,x_msg_count => l_msg_count
961 ,x_msg_data => l_msg_data
962 );
963
964 pa_debug.g_err_stage := ':l_period_profile_id = ' || l_period_profile_id;
965 IF P_PA_DEBUG_MODE = 'Y' THEN
966 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
967 END IF;
968
969 IF l_time_phased_code IN (PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G) THEN
970
971 IF l_period_profile_id IS NULL THEN
972 pa_debug.g_err_stage := 'period_profile_id is null when time phasing is PA or GL ';
973 IF P_PA_DEBUG_MODE = 'Y' THEN
974 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
975 END IF;
976 /* Bug # 2617990 */
977 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
978 p_msg_name => 'PA_FP_PERIODPROFILE_UNDEFINED');
979 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
980 END IF;
981
982 ELSE /* if time phasing is none then */
983
984 IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N THEN
985 IF l_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
986 IF l_project_start_date IS NULL or l_project_end_date IS NULL THEN
987 pa_debug.g_err_stage := 'for time phase none and entry level project, project start date and end date must be not null';
988 IF P_PA_DEBUG_MODE = 'Y' THEN
989 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
990 END IF;
991 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
992 END IF;
993 ELSE
994 /*bug 3182883 if calling context is 'VIEW' do not throw error*/
995 IF (l_task_start_date IS NULL or l_task_end_date IS NULL)
996 AND (p_calling_context <> 'VIEW') THEN
997 pa_debug.g_err_stage := 'for time phase none and entry level task, task start date and end date must be not null';
998 IF P_PA_DEBUG_MODE = 'Y' THEN
999 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1000 END IF;
1001 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1002 END IF;
1003 END IF;
1004 END IF;
1005 END IF;
1006
1007 IF l_period_profile_id IS NOT NULL THEN
1008
1009 /* Since in the context of G_CALLING_CONTEXT_OTHER_CURR, there would be no records
1010 in budget lines for the resource assignment and also since p_txn_curr_code would
1011 be null, we need not call get_preceding_succeeding_amt */
1012
1013 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
1014
1015 pa_debug.g_err_stage := 'calling get_preceding_succeeding_amt';
1016 IF P_PA_DEBUG_MODE = 'Y' THEN
1017 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1018 END IF;
1019
1020 GET_PRECEDING_SUCCEEDING_AMT(
1021 p_budget_version_id => l_fin_plan_version_id
1022 ,p_resource_assignment_id => p_resource_assignment_id
1023 ,p_txn_currency_code => p_txn_currency_code
1024 ,p_period_profile_id => l_period_profile_id
1025 ,x_preceding_raw_cost => l_preceding_raw_cost
1026 ,x_succeeding_raw_cost => l_succeeding_raw_cost
1027 ,x_preceding_burdened_cost => l_preceding_burdened_cost
1028 ,x_succeeding_burdened_cost => l_succeeding_burdened_cost
1029 ,x_preceding_revenue => l_preceding_revenue
1030 ,x_succeeding_revenue => l_succeeding_revenue
1031 ,x_preceding_quantity => l_preceding_quantity
1032 ,x_succeeding_quantity => l_succeeding_quantity
1033 ,x_return_status => l_return_status
1034 ,x_msg_count => l_msg_count
1035 ,x_msg_data => l_msg_data);
1036
1037 /* Call included for Bug2817407. This api returns the PC and PFC amounts
1038 for the input resource assignment id and txn currency code. */
1039
1040 GET_PD_SD_AMT_IN_PC_PFC(
1041 p_resource_assignment_id => p_resource_assignment_id
1042 ,p_txn_currency_code => p_txn_currency_code
1043 ,p_period_profile_id => l_period_profile_id
1044 ,x_pd_pc_raw_cost => l_pd_pc_raw_cost
1045 ,x_pd_pfc_raw_cost => l_pd_pfc_raw_cost
1046 ,x_sd_pc_raw_cost => l_sd_pc_raw_cost
1047 ,x_sd_pfc_raw_cost => l_sd_pfc_raw_cost
1048 ,x_pd_pc_burdened_cost => l_pd_pc_burdened_cost
1049 ,x_pd_pfc_burdened_cost => l_pd_pfc_burdened_cost
1050 ,x_sd_pc_burdened_cost => l_sd_pc_burdened_cost
1051 ,x_sd_pfc_burdened_cost => l_sd_pfc_burdened_cost
1052 ,x_pd_pc_revenue => l_pd_pc_revenue
1053 ,x_pd_pfc_revenue => l_pd_pfc_revenue
1054 ,x_sd_pc_revenue => l_sd_pc_revenue
1055 ,x_sd_pfc_revenue => l_sd_pfc_revenue
1056 ,x_return_status => l_return_status
1057 ,x_msg_count => l_msg_count
1058 ,x_msg_data => l_msg_data);
1059
1060 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1061 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1062 END IF;
1063 END IF;
1064
1065 /* Fix for enhancement bug # 2604957 starts */
1066
1067 /* In the context of view, we need to show only the records that are present in the
1068 budget lines and that from first avaiable period to the last available period in
1069 pa_budget_lines */
1070
1071 IF p_calling_context = PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_VIEW THEN
1072
1073 SELECT MIN(start_date), MAX(start_date)
1074 INTO l_min_start_date,l_max_start_date
1075 FROM pa_budget_lines
1076 WHERE budget_version_id = l_fin_plan_version_id
1077 AND resource_assignment_id = p_resource_assignment_id
1078 AND txn_currency_code = p_txn_currency_code
1079 AND start_date >= l_period_profile_start_date
1080 AND end_date <= l_period_profile_end_date;
1081
1082 ELSE
1083
1084 l_min_start_date := l_period_profile_start_date;
1085 l_max_start_date := l_period_profile_end_date;
1086
1087 END IF;
1088
1089 IF P_PA_DEBUG_MODE = 'Y' THEN
1090 pa_debug.g_err_stage := 'calling populate_eligible_periods ';
1091 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1092 END IF;
1093
1094 /* Calling populate_eligible_periods to populate the period date from
1095 l_min_start_date to l_max_start_date inclusive of the
1096 l_preceding_prd_start_date and l_succeeding_prd_start_date periods */
1097
1098 POPULATE_ELIGIBLE_PERIODS
1099 ( p_fin_plan_version_id => l_fin_plan_version_id
1100 ,p_period_profile_start_date => l_min_start_date
1101 ,p_period_profile_end_date => l_max_start_date
1102 ,p_preceding_prd_start_date => l_preceding_prd_start_date
1103 ,p_succeeding_prd_start_date => l_succeeding_prd_start_date
1104 ,x_return_status => l_return_status
1105 ,x_msg_count => l_msg_count
1106 ,x_msg_data => l_msg_data);
1107
1108 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1109 Raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1110 END IF;
1111
1112 /* Fix for enhancement bug # 2604957 ends*/
1113
1114 END IF;
1115
1116 DELETE FROM PA_FP_ROLLUP_TMP;
1117
1118 IF P_PA_DEBUG_MODE = 'Y' THEN
1119 pa_debug.g_err_stage:='deleted '||sql%rowcount || ' records from PA_FP_ROLLUP_TMP table' ;
1120 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1121 END IF;
1122
1123
1124 OPEN MC_CUR(l_fin_plan_version_id,p_txn_currency_code);
1125 FETCH MC_CUR INTO mc_cur_rec;
1126 CLOSE MC_CUR;
1127
1128 /* Enhancement bug # 2593167 : starts */
1129
1130 IF P_PA_DEBUG_MODE = 'Y' THEN
1131 pa_debug.g_err_stage:='time phasing is '|| l_time_phased_code ||
1132 ' before getting project start / end date period start / end dates';
1133 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1134 END IF;
1135
1136 IF l_period_profile_id IS NOT NULL THEN
1137
1138 /* Bug 2672548 - We are deriving the PD / SD info even when budget lines doesnt exists.
1139 So, bucketing period code can be derived by comparing the start date in pa_fp_cpy_periods_tmp
1140 with the derived preceding / succeeding information. */
1141
1142 INSERT INTO PA_FP_ROLLUP_TMP
1143 ( ROLLUP_ID
1144 ,RESOURCE_ASSIGNMENT_ID
1145 ,BUDGET_LINE_ID
1146 ,OLD_START_DATE
1147 ,START_DATE
1148 ,END_DATE
1149 ,PERIOD_NAME
1150 ,CHANGE_REASON_CODE
1151 ,DESCRIPTION
1152 ,BUCKETING_PERIOD_CODE
1153 ,TXN_CURRENCY_CODE
1154 ,PARENT_ASSIGNMENT_ID
1155 ,PROJECT_CURRENCY_CODE
1156 ,PROJFUNC_CURRENCY_CODE
1157 ,PROJECT_COST_RATE_TYPE
1158 ,PROJECT_COST_EXCHANGE_RATE
1159 ,PROJECT_COST_RATE_DATE_TYPE
1160 ,PROJECT_COST_RATE_DATE
1161 ,PROJECT_REV_RATE_TYPE
1162 ,PROJECT_REV_EXCHANGE_RATE
1163 ,PROJECT_REV_RATE_DATE_TYPE
1164 ,PROJECT_REV_RATE_DATE
1165 ,PROJFUNC_COST_RATE_TYPE
1166 ,PROJFUNC_COST_EXCHANGE_RATE
1167 ,PROJFUNC_COST_RATE_DATE_TYPE
1168 ,PROJFUNC_COST_RATE_DATE
1169 ,PROJFUNC_REV_RATE_TYPE
1170 ,PROJFUNC_REV_EXCHANGE_RATE
1171 ,PROJFUNC_REV_RATE_DATE_TYPE
1172 ,PROJFUNC_REV_RATE_DATE
1173 ,OLD_PROJ_RAW_COST
1174 ,OLD_PROJ_BURDENED_COST
1175 ,OLD_PROJ_REVENUE
1176 ,OLD_PROJFUNC_RAW_COST
1177 ,OLD_PROJFUNC_BURDENED_COST
1178 ,OLD_PROJFUNC_REVENUE
1179 ,OLD_QUANTITY
1180 ,PROJECT_RAW_COST
1181 ,PROJECT_BURDENED_COST
1182 ,PROJECT_REVENUE
1183 ,PROJFUNC_RAW_COST
1184 ,PROJFUNC_BURDENED_COST
1185 ,PROJFUNC_REVENUE
1186 ,OLD_TXN_RAW_COST
1187 ,OLD_TXN_BURDENED_COST
1188 ,OLD_TXN_REVENUE
1189 ,TXN_RAW_COST
1190 ,TXN_BURDENED_COST
1191 ,TXN_REVENUE
1192 ,QUANTITY
1193 ,DELETE_FLAG
1194 ,ATTRIBUTE_CATEGORY
1195 ,ATTRIBUTE1
1196 ,ATTRIBUTE2
1197 ,ATTRIBUTE3
1198 ,ATTRIBUTE4
1199 ,ATTRIBUTE5
1200 ,ATTRIBUTE6
1201 ,ATTRIBUTE7
1202 ,ATTRIBUTE8
1203 ,ATTRIBUTE9
1204 ,ATTRIBUTE10
1205 ,ATTRIBUTE11
1206 ,ATTRIBUTE12
1207 ,ATTRIBUTE13
1208 ,ATTRIBUTE14
1209 ,ATTRIBUTE15
1210 ,RAW_COST_SOURCE
1211 ,BURDENED_COST_SOURCE
1212 ,QUANTITY_SOURCE
1213 ,REVENUE_SOURCE
1214 ,PM_PRODUCT_CODE
1215 )
1216 (
1217 SELECT PA_FP_ROLLUP_TMP_S.NEXTVAL
1218 ,P_RESOURCE_ASSIGNMENT_ID
1219 ,pbl.BUDGET_LINE_ID
1220 ,pbl.START_DATE OLD_START_DATE /* when old_start_Date is null then the record should be inserted in pbl */
1221 ,tmp.START_DATE START_DATE
1222 ,tmp.END_DATE
1223 ,decode(tmp.start_date,
1224 l_preceding_prd_start_date,l_preceding_period_text,
1225 l_succeeding_prd_start_date,l_succeeding_period_text,
1226 tmp.PERIOD_NAME) PERIOD_NAME
1227 ,pbl.CHANGE_REASON_CODE
1228 ,pbl.DESCRIPTION
1229 ,decode(tmp.start_date,
1230 l_preceding_prd_start_date,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD,
1231 l_succeeding_prd_start_date,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD)
1232 BUCKETING_PERIOD_CODE
1233 ,P_TXN_CURRENCY_CODE
1234 ,pra.PARENT_ASSIGNMENT_ID
1235 ,nvl(pbl.PROJECT_CURRENCY_CODE,l_project_currency_code)
1236 ,nvl(pbl.PROJFUNC_CURRENCY_CODE,l_projfunc_currency_code) /* decode is used here since there are some outer joined records and we want to default only for such cases */
1237 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_TYPE,pbl.PROJECT_COST_RATE_TYPE)
1238 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_EXCHANGE_RATE,pbl.PROJECT_COST_EXCHANGE_RATE) /* due to changes in the mc page */
1239 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_DATE_TYPE,pbl.PROJECT_COST_RATE_DATE_TYPE)
1240 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_COST_RATE_DATE,pbl.PROJECT_COST_RATE_DATE)
1241 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_TYPE,pbl.PROJECT_REV_RATE_TYPE)
1242 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_EXCHANGE_RATE,pbl.PROJECT_REV_EXCHANGE_RATE) /* due to changes in the mc page */
1243 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_DATE_TYPE,pbl.PROJECT_REV_RATE_DATE_TYPE)
1244 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJECT_REV_RATE_DATE,pbl.PROJECT_REV_RATE_DATE)
1245 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_TYPE,pbl.PROJFUNC_COST_RATE_TYPE)
1246 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_EXCHANGE_RATE,pbl.PROJFUNC_COST_EXCHANGE_RATE)/* due to changes in the mc page */
1247 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE_TYPE,pbl.PROJFUNC_COST_RATE_DATE_TYPE)
1248 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE,pbl.PROJFUNC_COST_RATE_DATE)
1249 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_TYPE,pbl.PROJFUNC_REV_RATE_TYPE)
1250 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_EXCHANGE_RATE,pbl.PROJFUNC_REV_EXCHANGE_RATE) /* due to changes in the mc page */
1251 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE_TYPE,pbl.PROJFUNC_REV_RATE_DATE_TYPE)
1252 ,decode(pbl.START_DATE,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE,pbl.PROJFUNC_REV_RATE_DATE)
1253 ,decode(tmp.start_date,
1254 l_preceding_prd_start_date,l_pd_pc_raw_cost,
1255 l_succeeding_prd_start_date,l_sd_pc_raw_cost,
1256 pbl.PROJECT_RAW_COST) OLD_PROJ_RAW_COST
1257 ,decode(tmp.start_date,
1258 l_preceding_prd_start_date,l_pd_pc_burdened_cost,
1259 l_succeeding_prd_start_date,l_sd_pc_burdened_cost,
1260 pbl.PROJECT_BURDENED_COST) OLD_PROJ_BURDENED_COST
1261 ,decode(tmp.start_date,
1262 l_preceding_prd_start_date,l_pd_pc_revenue,
1263 l_succeeding_prd_start_date,l_sd_pc_revenue,
1264 pbl.PROJECT_REVENUE) OLD_PROJ_REVENUE
1265 ,decode(tmp.start_date,
1266 l_preceding_prd_start_date,l_pd_pfc_raw_cost,
1267 l_succeeding_prd_start_date,l_sd_pfc_raw_cost,
1268 pbl.RAW_COST) OLD_PROJFUNC_RAW_COST
1269 ,decode(tmp.start_date,
1270 l_preceding_prd_start_date,l_pd_pfc_burdened_cost,
1271 l_succeeding_prd_start_date,l_sd_pfc_burdened_cost,
1272 pbl.BURDENED_COST) OLD_PROJFUNC_BURDENED_COST
1273 ,decode(tmp.start_date,
1274 l_preceding_prd_start_date,l_pd_pfc_revenue,
1275 l_succeeding_prd_start_date,l_sd_pfc_revenue,
1276 pbl.REVENUE) OLD_PROJFUNC_REVENUE
1277 ,decode(tmp.start_date,
1278 l_preceding_prd_start_date,l_preceding_quantity,
1279 l_succeeding_prd_start_date,l_succeeding_quantity,
1280 pbl.QUANTITY) OLD_QUANTITY
1281 ,decode(tmp.start_date,
1282 l_preceding_prd_start_date,l_pd_pc_raw_cost,
1283 l_succeeding_prd_start_date,l_sd_pc_raw_cost,
1284 pbl.PROJECT_RAW_COST) PROJECT_RAW_COST
1285 ,decode(tmp.start_date,
1286 l_preceding_prd_start_date,l_pd_pc_burdened_cost,
1287 l_succeeding_prd_start_date,l_sd_pc_burdened_cost,
1288 pbl.PROJECT_BURDENED_COST) PROJECT_BURDENED_COST
1289 ,decode(tmp.start_date,
1290 l_preceding_prd_start_date,l_pd_pc_revenue,
1291 l_succeeding_prd_start_date,l_sd_pc_revenue,
1292 pbl.PROJECT_REVENUE) PROJECT_REVENUE
1293 ,decode(tmp.start_date,
1294 l_preceding_prd_start_date,l_pd_pfc_raw_cost,
1295 l_succeeding_prd_start_date,l_sd_pfc_raw_cost,
1296 pbl.RAW_COST) RAW_COST
1297 ,decode(tmp.start_date,
1298 l_preceding_prd_start_date,l_pd_pfc_burdened_cost,
1299 l_succeeding_prd_start_date,l_sd_pfc_burdened_cost,
1300 pbl.BURDENED_COST) BURDENED_COST
1301 ,decode(tmp.start_date,
1302 l_preceding_prd_start_date,l_pd_pfc_revenue,
1303 l_succeeding_prd_start_date,l_sd_pfc_revenue,
1304 pbl.REVENUE) REVENUE
1305 ,decode(tmp.start_date,
1306 l_preceding_prd_start_date,l_preceding_raw_cost,
1307 l_succeeding_prd_start_date,l_succeeding_raw_cost,
1308 pbl.TXN_RAW_COST) OLD_TXN_RAW_COST
1309 ,decode(tmp.start_date,
1310 l_preceding_prd_start_date,l_preceding_burdened_cost,
1311 l_succeeding_prd_start_date,l_succeeding_burdened_cost,
1312 pbl.TXN_BURDENED_COST) OLD_TXN_BURDENED_COST
1313 ,decode(tmp.start_date,
1314 l_preceding_prd_start_date,l_preceding_revenue,
1315 l_succeeding_prd_start_date,l_succeeding_revenue,
1316 pbl.TXN_REVENUE) OLD_TXN_REVENUE
1317 ,decode(tmp.start_date,
1318 l_preceding_prd_start_date,l_preceding_raw_cost,
1319 l_succeeding_prd_start_date,l_succeeding_raw_cost,
1320 pbl.TXN_RAW_COST) TXN_RAW_COST
1321 ,decode(tmp.start_date,
1322 l_preceding_prd_start_date,l_preceding_burdened_cost,
1323 l_succeeding_prd_start_date,l_succeeding_burdened_cost,
1324 pbl.TXN_BURDENED_COST) TXN_BURDENED_COST
1325 ,decode(tmp.start_date,
1326 l_preceding_prd_start_date,l_preceding_revenue,
1327 l_succeeding_prd_start_date,l_succeeding_revenue,
1328 pbl.TXN_REVENUE) TXN_REVENUE
1329 ,decode(tmp.start_date,
1330 l_preceding_prd_start_date,l_preceding_quantity,
1331 l_succeeding_prd_start_date,l_succeeding_quantity,
1332 pbl.QUANTITY) QUANTITY
1333 ,'N' DELETE_FLAG
1334 ,pbl.ATTRIBUTE_CATEGORY
1335 ,pbl.ATTRIBUTE1
1336 ,pbl.ATTRIBUTE2
1337 ,pbl.ATTRIBUTE3
1338 ,pbl.ATTRIBUTE4
1339 ,pbl.ATTRIBUTE5
1340 ,pbl.ATTRIBUTE6
1341 ,pbl.ATTRIBUTE7
1342 ,pbl.ATTRIBUTE8
1343 ,pbl.ATTRIBUTE9
1344 ,pbl.ATTRIBUTE10
1345 ,pbl.ATTRIBUTE11
1346 ,pbl.ATTRIBUTE12
1347 ,pbl.ATTRIBUTE13
1348 ,pbl.ATTRIBUTE14
1349 ,pbl.ATTRIBUTE15
1350 ,pbl.RAW_COST_SOURCE
1351 ,pbl.BURDENED_COST_SOURCE
1352 ,pbl.QUANTITY_SOURCE
1353 ,pbl.REVENUE_SOURCE
1354 ,pbl.PM_PRODUCT_CODE
1355 FROM pa_resource_assignments pra
1356 ,pa_budget_lines pbl
1357 ,pa_fp_cpy_periods_tmp tmp
1358 WHERE pra.resource_assignment_id = p_resource_assignment_id
1359 AND pbl.resource_assignment_id(+) = p_resource_assignment_id
1360 AND pbl.start_date(+) = tmp.start_date
1361 AND pbl.txn_currency_code(+) = p_txn_currency_code);
1362
1363 IF P_PA_DEBUG_MODE = 'Y' THEN
1364 pa_debug.g_err_stage := ':inserted ' || sql%rowcount || ' records ';
1365 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1366 END IF;
1367
1368 /* when period profile is null we can assume that the case is either date range or none */
1369
1370 ELSE /* IF time phased code in G_TIME_PHASED_CODE_P G_TIME_PHASED_CODE_G */
1371
1372 pa_debug.g_err_stage := ':period profile id is null and so time phasing should be none or date range';
1373 IF P_PA_DEBUG_MODE = 'Y' THEN
1374 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1375 END IF;
1376 /* bvarnasi added case for timephasing NONE */
1377
1378 DECLARE
1379 l_start_date pa_budget_lines.start_Date%type;
1380 l_end_date pa_budget_lines.end_Date%type;
1381 BEGIN
1382
1383 /* following is the logic of getting start date and end date
1384 1. in case time phasing is none and no record exists in budget lines then
1385 we need to insert either project start/end date or task start/end date
1386 based upon planning level.
1387 in case time phasing is date range. insert a row with these values as null
1388 User will enter the required start and end dates.
1389 */
1390 IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N THEN
1391 IF l_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1392 l_start_date := l_project_start_date;
1393 l_end_date := l_project_end_date;
1394 ELSE
1395 l_start_date := l_task_start_date;
1396 l_end_date := l_task_end_date;
1397 END IF;
1398 ELSE
1399 l_start_date := null;
1400 l_end_date := null;
1401 END IF;
1402
1403 pa_debug.g_err_stage := 'time phasing none and start / end dates are : '|| l_start_date ||' , '||l_end_date;
1404 IF P_PA_DEBUG_MODE = 'Y' THEN
1405 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1406 END IF;
1407
1408 INSERT INTO PA_FP_ROLLUP_TMP
1409 ( ROLLUP_ID
1410 ,RESOURCE_ASSIGNMENT_ID
1411 ,BUDGET_LINE_ID
1412 ,OLD_START_DATE
1413 ,START_DATE
1414 ,END_DATE
1415 ,PERIOD_NAME
1416 ,CHANGE_REASON_CODE
1417 ,DESCRIPTION
1418 ,BUCKETING_PERIOD_CODE
1419 ,TXN_CURRENCY_CODE
1420 ,PARENT_ASSIGNMENT_ID
1421 ,PROJECT_CURRENCY_CODE
1422 ,PROJFUNC_CURRENCY_CODE
1423 ,PROJECT_COST_RATE_TYPE
1424 ,PROJECT_COST_EXCHANGE_RATE
1425 ,PROJECT_COST_RATE_DATE_TYPE
1426 ,PROJECT_COST_RATE_DATE
1427 ,PROJECT_REV_RATE_TYPE
1428 ,PROJECT_REV_EXCHANGE_RATE
1429 ,PROJECT_REV_RATE_DATE_TYPE
1430 ,PROJECT_REV_RATE_DATE
1431 ,PROJFUNC_COST_RATE_TYPE
1432 ,PROJFUNC_COST_EXCHANGE_RATE
1433 ,PROJFUNC_COST_RATE_DATE_TYPE
1434 ,PROJFUNC_COST_RATE_DATE
1435 ,PROJFUNC_REV_RATE_TYPE
1436 ,PROJFUNC_REV_EXCHANGE_RATE
1437 ,PROJFUNC_REV_RATE_DATE_TYPE
1438 ,PROJFUNC_REV_RATE_DATE
1439 ,OLD_PROJ_RAW_COST
1440 ,OLD_PROJ_BURDENED_COST
1441 ,OLD_PROJ_REVENUE
1442 ,OLD_PROJFUNC_RAW_COST
1443 ,OLD_PROJFUNC_BURDENED_COST
1444 ,OLD_PROJFUNC_REVENUE
1445 ,OLD_QUANTITY
1446 ,PROJECT_RAW_COST
1447 ,PROJECT_BURDENED_COST
1448 ,PROJECT_REVENUE
1449 ,PROJFUNC_RAW_COST
1450 ,PROJFUNC_BURDENED_COST
1451 ,PROJFUNC_REVENUE
1452 ,OLD_TXN_RAW_COST
1453 ,OLD_TXN_BURDENED_COST
1454 ,OLD_TXN_REVENUE
1455 ,TXN_RAW_COST
1456 ,TXN_BURDENED_COST
1457 ,TXN_REVENUE
1458 ,QUANTITY
1459 ,DELETE_FLAG
1460 ,ATTRIBUTE_CATEGORY
1461 ,ATTRIBUTE1
1462 ,ATTRIBUTE2
1463 ,ATTRIBUTE3
1464 ,ATTRIBUTE4
1465 ,ATTRIBUTE5
1466 ,ATTRIBUTE6
1467 ,ATTRIBUTE7
1468 ,ATTRIBUTE8
1469 ,ATTRIBUTE9
1470 ,ATTRIBUTE10
1471 ,ATTRIBUTE11
1472 ,ATTRIBUTE12
1473 ,ATTRIBUTE13
1474 ,ATTRIBUTE14
1475 ,ATTRIBUTE15
1476 ,RAW_COST_SOURCE
1477 ,BURDENED_COST_SOURCE
1478 ,QUANTITY_SOURCE
1479 ,REVENUE_SOURCE
1480 ,PM_PRODUCT_CODE
1481 )
1482 (
1483 SELECT pa_fp_rollup_tmp_s.nextval
1484 ,p_resource_assignment_id /* Fix for bug # 2586514 */
1485 ,pbl.BUDGET_LINE_ID
1486 ,pbl.start_date OLD_START_DATE /* when old_start_Date is null then the record should be inserted in pbl */
1487 ,nvl(pbl.start_date,l_start_date)
1488 ,nvl(pbl.end_Date,l_end_date)
1489 ,pbl.PERIOD_NAME
1490 ,pbl.CHANGE_REASON_CODE
1491 ,pbl.DESCRIPTION
1492 ,pbl.BUCKETING_PERIOD_CODE
1493 ,P_TXN_CURRENCY_CODE /* Fix for bug # 2590361 */
1494 ,pra.PARENT_ASSIGNMENT_ID
1495 ,nvl(pbl.PROJECT_CURRENCY_CODE,l_project_currency_code)
1496 ,nvl(pbl.PROJFUNC_CURRENCY_CODE,l_projfunc_currency_code)
1497 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_TYPE,pbl.PROJECT_COST_RATE_TYPE) /* remove decode for start date now */
1498 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_EXCHANGE_RATE,pbl.PROJECT_COST_EXCHANGE_RATE) /* due to changes on mc page */
1499 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_DATE_TYPE,pbl.PROJECT_COST_RATE_DATE_TYPE)
1500 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_COST_RATE_DATE,pbl.PROJECT_COST_RATE_DATE)
1501 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_TYPE,pbl.PROJECT_REV_RATE_TYPE)
1502 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_EXCHANGE_RATE,pbl.PROJECT_REV_EXCHANGE_RATE) /* due to changes on mc page */
1503 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_DATE_TYPE,pbl.PROJECT_REV_RATE_DATE_TYPE)
1504 ,decode(pbl.start_date,null,mc_cur_rec.PROJECT_REV_RATE_DATE,pbl.PROJECT_REV_RATE_DATE)
1505 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_TYPE,pbl.PROJFUNC_COST_RATE_TYPE)
1506 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_EXCHANGE_RATE,pbl.PROJFUNC_COST_EXCHANGE_RATE)/* due to changes on mc page */
1507 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE_TYPE,pbl.PROJFUNC_COST_RATE_DATE_TYPE)
1508 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_COST_RATE_DATE,pbl.PROJFUNC_COST_RATE_DATE)
1509 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_TYPE,pbl.PROJFUNC_REV_RATE_TYPE)
1510 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_EXCHANGE_RATE,pbl.PROJFUNC_REV_EXCHANGE_RATE) /* due to changes on mc page */
1511 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE_TYPE,pbl.PROJFUNC_REV_RATE_DATE_TYPE)
1512 ,decode(pbl.start_date,null,mc_cur_rec.PROJFUNC_REV_RATE_DATE,pbl.PROJFUNC_REV_RATE_DATE)
1513 ,pbl.PROJECT_RAW_COST OLD_PROJ_RAW_COST
1514 ,pbl.PROJECT_BURDENED_COST OLD_PROJ_BURDENED_COST
1515 ,pbl.PROJECT_REVENUE OLD_PROJ_REVENUE
1516 ,pbl.RAW_COST OLD_PROJFUNC_RAW_COST
1517 ,pbl.BURDENED_COST OLD_PROJFUNC_BURDENED_COST
1518 ,pbl.REVENUE OLD_PROJFUNC_REVENUE
1519 ,pbl.QUANTITY OLD_QUANTITY
1520 ,pbl.PROJECT_RAW_COST
1521 ,pbl.PROJECT_BURDENED_COST
1522 ,pbl.PROJECT_REVENUE
1523 ,pbl.RAW_COST
1524 ,pbl.BURDENED_COST
1525 ,pbl.REVENUE
1526 ,pbl.TXN_RAW_COST OLD_TXN_RAW_COST
1527 ,pbl.TXN_BURDENED_COST OLD_TXN_BURDENED_COST
1528 ,pbl.TXN_REVENUE OLD_TXN_REVENUE
1529 ,pbl.TXN_RAW_COST
1530 ,pbl.TXN_BURDENED_COST
1531 ,pbl.TXN_REVENUE
1532 ,pbl.QUANTITY
1533 ,'N' DELETE_FLAG
1534 ,pbl.ATTRIBUTE_CATEGORY
1535 ,pbl.ATTRIBUTE1
1536 ,pbl.ATTRIBUTE2
1537 ,pbl.ATTRIBUTE3
1538 ,pbl.ATTRIBUTE4
1539 ,pbl.ATTRIBUTE5
1540 ,pbl.ATTRIBUTE6
1541 ,pbl.ATTRIBUTE7
1542 ,pbl.ATTRIBUTE8
1543 ,pbl.ATTRIBUTE9
1544 ,pbl.ATTRIBUTE10
1545 ,pbl.ATTRIBUTE11
1546 ,pbl.ATTRIBUTE12
1547 ,pbl.ATTRIBUTE13
1548 ,pbl.ATTRIBUTE14
1549 ,pbl.ATTRIBUTE15
1550 ,pbl.RAW_COST_SOURCE
1551 ,pbl.BURDENED_COST_SOURCE
1552 ,pbl.QUANTITY_SOURCE
1553 ,pbl.REVENUE_SOURCE
1554 ,pbl.PM_PRODUCT_CODE
1555 FROM pa_resource_assignments pra
1556 ,pa_budget_lines pbl
1557 ,pa_proj_fp_options pfo
1558 -- ,pa_fp_txn_currencies pftc -- Bug # 2615998
1559 WHERE pra.resource_assignment_id = p_resource_assignment_id
1560 AND pbl.txn_currency_code = p_txn_currency_code
1561 AND pbl.resource_assignment_id = pra.resource_assignment_id
1562 AND pfo.fin_plan_version_id = pra.budget_version_id
1563 -- AND pfo.proj_fp_options_id = pftc.proj_fp_options_id -- Bug # 2615998
1564 -- AND pftc.txn_currency_code = p_txn_currency_code -- Bug # 2615998
1565 );
1566
1567 l_count := SQL%ROWCOUNT;
1568 /* Bug found during Unit Testing. Do the insert only in EDIT / EDIT ANOTHER CURRENCY modes */
1569 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_VIEW THEN
1570 IF nvl(l_count,0) = 0 THEN
1571 IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_R THEN
1572 FOR i IN 1..5 LOOP
1573 insert_dummy_record_pvt(mc_cur_rec);
1574 END LOOP;
1575 ELSE
1576 insert_dummy_record_pvt(mc_cur_rec);
1577 /*
1578 Fix for bug # 2630282 : There will be only one record in case of NONE.
1579 txn_currency code is not included in the where clause as it will fail in
1580 case of Plan In Another Currency.
1581 */
1582
1583 UPDATE PA_FP_ROLLUP_TMP
1584 SET START_DATE = l_start_date,
1585 END_DATE = l_end_date
1586 WHERE resource_assignment_id = p_resource_assignment_id;
1587 END IF;
1588 END IF;
1589 END IF;
1590
1591 pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
1592 IF P_PA_DEBUG_MODE = 'Y' THEN
1593 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
1594 END IF;
1595 END;
1596
1597 END IF;
1598
1599 pa_debug.reset_err_stack; /* Bug 2699888 */
1600
1601 EXCEPTION
1602 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1603 x_return_status := FND_API.G_RET_STS_ERROR;
1604 l_msg_count := FND_MSG_PUB.count_msg;
1605 IF l_msg_count = 1 THEN
1606 PA_INTERFACE_UTILS_PUB.get_messages
1607 (p_encoded => FND_API.G_TRUE,
1608 p_msg_index => 1,
1609 p_msg_count => l_msg_count,
1610 p_msg_data => l_msg_data,
1611 p_data => l_data,
1612 p_msg_index_out => l_msg_index_out);
1613
1614 x_msg_data := l_data;
1615 x_msg_count := l_msg_count;
1616 ELSE
1617 x_msg_count := l_msg_count;
1618 END IF;
1619
1620 IF P_PA_DEBUG_MODE = 'Y' THEN
1621 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,'Invalid arguments passed',5);
1622 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.G_Err_Stack,5);
1623 END IF;
1624 pa_debug.reset_err_stack;
1625
1626 RETURN;
1627
1628 WHEN OTHERS THEN
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 x_msg_count := 1;
1631 x_msg_data := SQLERRM;
1632 FND_MSG_PUB.add_exc_msg
1633 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG.POPULATE_ROLLUP_TMP'
1634 ,p_procedure_name => pa_debug.G_Err_Stack );
1635 pa_debug.G_Err_Stack := SQLERRM;
1636 IF P_PA_DEBUG_MODE = 'Y' THEN
1637 pa_debug.write('POPULATE_ROLLUP_TMP: ' || l_module_name,pa_debug.G_Err_Stack,4);
1638 END IF;
1639 pa_debug.reset_err_stack;
1640
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1642
1643 END POPULATE_ROLLUP_TMP;
1644
1645 /* bug 2645574: Making this process more generic. Now this procedure can be used from places
1646 other than edit line page. One example is create_finplan_line.
1647 This procedure now can handle more than one resource assignments in PA_FP_ROLLUP_TMP.
1648 Logic will now be based upon budget_line_id rather than old_start_Date.
1649 If budget_line_id is null then it will be considered as new record (to be inserted)
1650 else records will be updated.
1651 */
1652
1653
1654 PROCEDURE PROCESS_MODIFIED_LINES
1655 (
1656 -- Bug Fix: 4569365. Removed MRC code.
1657 p_calling_context IN VARCHAR2 -- pa_mrc_finplan.g_calling_module%TYPE /* Bug# 2674353 */
1658 ,p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
1659 ,p_fin_plan_version_id IN pa_resource_assignments.budget_version_id%TYPE -- DEFAULT NULL
1660 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1661 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1662 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
1663
1664 /* Variables to be used for debugging purpose */
1665 l_msg_count NUMBER := 0;
1666 l_data VARCHAR2(2000);
1667 l_msg_data VARCHAR2(2000);
1668 l_msg_index_out NUMBER;
1669 l_return_status VARCHAR2(2000);
1670 l_debug_mode VARCHAR2(10);
1671 l_stage NUMBER := 100;
1672
1673 l_task_id NUMBER;
1674 l_resource_list_member_id NUMBER ;
1675 -- l_pm_product_code VARCHAR2(30); commented for bug 3858543 as this local variable is not used anywhere
1676
1677 l_budget_version_id NUMBER;
1678 -- Bug Fix: 4569365. Removed MRC code.
1679 -- l_calling_context pa_mrc_finplan.g_calling_module%TYPE;
1680 l_calling_context VARCHAR2(30);
1681
1682 -- l_budget_line_tmp pa_budget_lines.budget_line_id%TYPE;
1683
1684 l_res_assignment_tbl pa_fp_copy_from_pkg.l_res_assignment_tbl_typ ;
1685
1686 l_disabled_resource_entered VARCHAR2(10);
1687
1688 Cursor c_disabled_resource_exists IS
1689 select 1 from dual
1690 where exists (select prlm.resource_list_member_id
1691 from pa_resource_list_members prlm, pa_resource_assignments pra, pa_fp_rollup_tmp tmp
1692 where pra.resource_assignment_id = p_resource_assignment_id
1693 and pra.resource_list_member_id= prlm.resource_list_member_id
1694 and nvl(prlm.enabled_flag,'Y') = 'N'
1695 and tmp.resource_assignment_id = pra.resource_assignment_id
1696 and tmp.budget_line_id is null);
1697
1698
1699 BEGIN
1700
1701 -- Set the error stack.
1702 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES');
1703
1704 -- Get the Debug mode into local variable and set it to 'Y' if its NULL
1705 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1706 l_debug_mode := NVL(l_debug_mode, 'Y');
1707
1708 -- Initialize the return status to success
1709 x_return_status := FND_API.G_RET_STS_SUCCESS;
1710
1711 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1712
1713 pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES ';
1714 IF P_PA_DEBUG_MODE = 'Y' THEN
1715 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1716 END IF;
1717
1718 -- Validating for the Input Parameters
1719
1720 IF p_resource_assignment_id IS NULL AND p_fin_plan_version_id IS NULL THEN
1721
1722 pa_debug.g_err_stage := 'both p_resource_assignment_id and budget version id cannot be null.';
1723 IF P_PA_DEBUG_MODE = 'Y' THEN
1724 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,5);
1725 END IF;
1726
1727 x_return_status := FND_API.G_RET_STS_ERROR;
1728
1729 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1730 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1731
1732 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1733
1734 END IF;
1735
1736 l_calling_context := p_calling_context; /* Bug# 2674353 */
1737
1738 /*
1739 Delete all such lines from rollup_tmp table that are not existing in
1740 pa_budget_lines and also marked for delete by user.
1741 */
1742 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Deleting records from pa_fp_rollup_tmp that are not present in the budget lines table ';
1743 IF P_PA_DEBUG_MODE = 'Y' THEN
1744 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1745 END IF;
1746
1747 delete from pa_fp_rollup_tmp
1748 where budget_line_id is null /* FPB3: bug 2645574: Instead of old_start_date refer budget_line_id */
1749 and (delete_flag = 'Y' or
1750 (txn_raw_cost is null and
1751 txn_burdened_cost is null and
1752 quantity is null and
1753 txn_revenue is null)); /* Bug 2684537 */
1754
1755 pa_debug.g_err_stage := TO_CHAR(l_stage)||': Deleted '||sql%rowcount||' records ';
1756 IF P_PA_DEBUG_MODE = 'Y' THEN
1757 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1758 END IF;
1759
1760 /******************** Commented for bug#2821961.
1761
1762 Whatever has been done here is now being done in PaFpEditViewPlanLineCO.java
1763 because it was causing unique constraint violation error.
1764
1765 Bug 2817407: If PD/SD amounts are marked for deletion, we should be negating the
1766 deleted quantum of PD/SD records and updating the delete_flag back to N. This is done
1767 because deletion of PD/SD means reducing the PD/SD bucket amounts by that amount and
1768 we should not be deleting the records as such. This means PD/SD bucket amounts should
1769 be set to zero if delete flag = Y for them. PC/PFC buckets of PD/SD need not be touched
1770 since they will be maintained by the call to convert_mc api
1771
1772 UPDATE pa_fp_rollup_tmp
1773 SET delete_flag = 'N'
1774 ,txn_raw_cost = DECODE(old_txn_raw_cost, NULL,NULL,0)
1775 ,txn_burdened_cost = DECODE(old_txn_burdened_cost,NULL,NULL,0)
1776 ,txn_revenue = DECODE(old_txn_revenue,NULL,NULL,0)
1777 ,quantity = DECODE(old_quantity,NULL,NULL,0)
1778 WHERE delete_flag = 'Y'
1779 AND bucketing_period_code IN
1780 (PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD);
1781 *********************/
1782
1783 IF P_PA_DEBUG_MODE = 'Y' THEN
1784 pa_debug.g_err_stage := TO_CHAR(l_stage)||': updated '||sql%rowcount||' records ';
1785 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1786 END IF;
1787
1788 pa_debug.g_err_stage := TO_CHAR(l_stage)||': populating budget version id , task id and rlm id';
1789 IF P_PA_DEBUG_MODE = 'Y' THEN
1790 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1791 END IF;
1792
1793 IF p_resource_assignment_id IS NOT NULL THEN
1794 --Included this block for bug 3050933.
1795 BEGIN
1796 SELECT budget_version_id, task_id, resource_list_member_id
1797 INTO l_budget_version_id, l_task_id, l_resource_list_member_id
1798 FROM pa_resource_assignments
1799 WHERE resource_assignment_id = p_resource_assignment_id;
1800 EXCEPTION
1801 WHEN NO_DATA_FOUND THEN
1802 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Input res assmt id not found in pa_resource_assignments';
1803 IF P_PA_DEBUG_MODE = 'Y' THEN
1804 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,5);
1805 END IF;
1806 x_return_status := FND_API.G_RET_STS_ERROR;
1807 IF p_calling_context = PA_FP_CONSTANTS_PKG.G_EDIT_PLAN_LINE_PAGE THEN
1808 x_msg_data := 'PA_FP_EPL_TASK_UPDATED';
1809 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1810 ELSE
1811 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1812 p_msg_name => 'PA_FP_EPL_TASK_UPDATED');
1813 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1814 END IF;
1815
1816
1817 END;
1818
1819 ELSE
1820 l_budget_version_id := p_fin_plan_version_id;
1821 l_task_id := NULL;
1822 l_resource_list_member_id := NULL;
1823 END IF;
1824
1825 /* Bug# 2674353 - In case of autobaseline, the funding apis would have
1826 sent the converted amounts in pc and pfc and hence we should not
1827 be calling mc api. This might do a revaluation based on finplan setup
1828 which is wrong */
1829
1830 IF nvl(l_calling_context,-99) <> PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE THEN
1831
1832 pa_debug.g_err_stage := ': calling convert_txn_currency';
1833 IF P_PA_DEBUG_MODE = 'Y' THEN
1834 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1835 END IF;
1836
1837 PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency
1838 ( p_budget_version_id => l_budget_version_id
1839 ,p_entire_version => 'N'
1840 ,x_return_status =>x_return_status
1841 ,x_msg_count =>x_msg_count
1842 ,x_msg_data =>x_msg_data) ;
1843
1844 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN /* Bug# 2644641 */
1845 pa_debug.g_err_stage := 'MC Api returned error...';
1846 IF P_PA_DEBUG_MODE = 'Y' THEN
1847 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1848 pa_debug.g_err_stage := 'calling context : '|| p_calling_context; -- WEBADI UT
1849 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1850 END IF;
1851
1852 IF p_calling_context = PA_FP_CONSTANTS_PKG.G_WEBADI THEN --WEBADI UT
1853 --This exception needs to be raised only in webadi context.
1854 Raise PA_FP_CONSTANTS_PKG.MC_Conversion_Failed_Exc;
1855 END IF;
1856
1857 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1858 END IF;
1859
1860 END IF; /* l_calling_context <> PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE */
1861
1862 -- Delete the lines which have delete_flag = 'Y'
1863 -- in the temp table
1864
1865 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Deleting from pa_budget_lines table ';
1866 IF P_PA_DEBUG_MODE = 'Y' THEN
1867 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1868 END IF;
1869
1870 /* 2645574 : Instead of old_start_date based logic on budget line id now.
1871 A corresponding row from base table can be identified based upon
1872 budget line id now
1873 */
1874 /*
1875 DELETE FROM pa_budget_lines bl
1876 WHERE (bl.resource_assignment_id
1877 ,bl.txn_currency_code
1878 ,bl.start_date ) IN (SELECT tmp.resource_assignment_id
1879 ,tmp.txn_currency_code
1880 ,tmp.old_start_date
1881 FROM pa_fp_rollup_tmp tmp
1882 WHERE nvl(tmp.delete_flag,'N') = 'Y') ;
1883 */
1884
1885 DELETE /*+ INDEX( bl PA_BUDGET_LINES_U2 )*/ FROM pa_budget_lines bl --Bug 2782166
1886 WHERE (budget_line_id) IN (SELECT budget_line_id
1887 FROM pa_fp_rollup_tmp tmp
1888 WHERE nvl(tmp.delete_flag,'N') = 'Y');
1889
1890 pa_debug.g_err_stage := TO_CHAR(l_stage)||': Deleted '||sql%rowcount||' records';
1891 IF P_PA_DEBUG_MODE = 'Y' THEN
1892 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1893 END IF;
1894
1895 -- Update the budget line table with the values
1896 -- in the temp table for the records that exist
1897 -- in budget line table
1898
1899 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Updating the pa_budget_lines table ';
1900 IF P_PA_DEBUG_MODE = 'Y' THEN
1901 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
1902 END IF;
1903
1904 UPDATE /*+ INDEX( bl PA_BUDGET_LINES_U2 )*/ PA_BUDGET_LINES bl --Bug 2782166
1905 SET (
1906 START_DATE
1907 ,END_DATE
1908 ,QUANTITY
1909 ,RAW_COST
1910 ,BURDENED_COST
1911 ,REVENUE
1912 ,CHANGE_REASON_CODE
1913 ,DESCRIPTION
1914 ,ATTRIBUTE_CATEGORY
1915 ,ATTRIBUTE1
1916 ,ATTRIBUTE2
1917 ,ATTRIBUTE3
1918 ,ATTRIBUTE4
1919 ,ATTRIBUTE5
1920 ,ATTRIBUTE6
1921 ,ATTRIBUTE7
1922 ,ATTRIBUTE8
1923 ,ATTRIBUTE9
1924 ,ATTRIBUTE10
1925 ,ATTRIBUTE11
1926 ,ATTRIBUTE12
1927 ,ATTRIBUTE13
1928 ,ATTRIBUTE14
1929 ,ATTRIBUTE15
1930 ,PROJFUNC_CURRENCY_CODE
1931 ,PROJFUNC_COST_RATE_TYPE
1932 ,PROJFUNC_COST_EXCHANGE_RATE
1933 ,PROJFUNC_COST_RATE_DATE_TYPE
1934 ,PROJFUNC_COST_RATE_DATE
1935 ,PROJECT_CURRENCY_CODE
1936 ,PROJECT_COST_RATE_TYPE
1937 ,PROJECT_COST_EXCHANGE_RATE
1938 ,PROJECT_COST_RATE_DATE_TYPE
1939 ,PROJECT_COST_RATE_DATE
1940 ,PROJECT_RAW_COST
1941 ,PROJECT_BURDENED_COST
1942 ,PROJECT_REVENUE
1943 ,TXN_RAW_COST
1944 ,TXN_BURDENED_COST
1945 ,TXN_REVENUE
1946 ,TXN_CURRENCY_CODE
1947 ,BUCKETING_PERIOD_CODE
1948 ,PROJFUNC_REV_RATE_DATE
1949 ,PROJFUNC_REV_RATE_TYPE
1950 ,PROJFUNC_REV_EXCHANGE_RATE
1951 ,PROJFUNC_REV_RATE_DATE_TYPE
1952 ,PROJECT_REV_RATE_DATE
1953 ,PROJECT_REV_RATE_TYPE
1954 ,PROJECT_REV_EXCHANGE_RATE
1955 ,PROJECT_REV_RATE_DATE_TYPE
1956 ,RAW_COST_SOURCE
1957 ,BURDENED_COST_SOURCE
1958 ,QUANTITY_SOURCE
1959 ,REVENUE_SOURCE
1960 /* Code Addition for bug 3394907 starts */
1961 ,LAST_UPDATE_DATE
1962 ,LAST_UPDATED_BY
1963 ,LAST_UPDATE_LOGIN
1964 /* Code Addition for bug 3394907 ends */
1965
1966 ) =
1967 (
1968 SELECT
1969 START_DATE
1970 ,END_DATE
1971 ,decode(bucketing_period_code,NULL,QUANTITY,
1972 decode(bl.QUANTITY||tmp.QUANTITY||tmp.old_QUANTITY,null,null,
1973 nvl(bl.QUANTITY,0) + (nvl(tmp.QUANTITY,0) - nvl(tmp.old_QUANTITY,0))))
1974 ,decode(bucketing_period_code,NULL,tmp.PROJFUNC_RAW_COST,
1975 decode(bl.RAW_COST||tmp.PROJFUNC_RAW_COST||tmp.OLD_PROJFUNC_RAW_COST,null,null,
1976 nvl(bl.RAW_COST,0) + (nvl(tmp.PROJFUNC_RAW_COST,0) - nvl(tmp.OLD_PROJFUNC_RAW_COST,0)))) /* Bug 2774811 */
1977 ,decode(bucketing_period_code,NULL,tmp.PROJFUNC_BURDENED_COST,
1978 decode(bl.BURDENED_COST||tmp.PROJFUNC_BURDENED_COST||tmp.OLD_PROJFUNC_BURDENED_COST,null,null,
1979 nvl(bl.BURDENED_COST,0) + (nvl(tmp.PROJFUNC_BURDENED_COST,0) - nvl(tmp.OLD_PROJFUNC_BURDENED_COST,0)))) /* Bug 2774811 */
1980 ,decode(bucketing_period_code,NULL,tmp.PROJFUNC_REVENUE,
1981 decode(bl.REVENUE||tmp.PROJFUNC_REVENUE||tmp.OLD_PROJFUNC_REVENUE,null,null,
1982 nvl(bl.REVENUE,0) + (nvl(tmp.PROJFUNC_REVENUE,0) - nvl(tmp.OLD_PROJFUNC_REVENUE,0)))) /* Bug 2774811 */
1983 ,CHANGE_REASON_CODE
1984 ,DESCRIPTION
1985 ,ATTRIBUTE_CATEGORY
1986 ,ATTRIBUTE1
1987 ,ATTRIBUTE2
1988 ,ATTRIBUTE3
1989 ,ATTRIBUTE4
1990 ,ATTRIBUTE5
1991 ,ATTRIBUTE6
1992 ,ATTRIBUTE7
1993 ,ATTRIBUTE8
1994 ,ATTRIBUTE9
1995 ,ATTRIBUTE10
1996 ,ATTRIBUTE11
1997 ,ATTRIBUTE12
1998 ,ATTRIBUTE13
1999 ,ATTRIBUTE14
2000 ,ATTRIBUTE15
2001 ,PROJFUNC_CURRENCY_CODE
2002 ,PROJFUNC_COST_RATE_TYPE
2003 ,PROJFUNC_COST_EXCHANGE_RATE
2004 ,PROJFUNC_COST_RATE_DATE_TYPE
2005 ,PROJFUNC_COST_RATE_DATE
2006 ,PROJECT_CURRENCY_CODE
2007 ,PROJECT_COST_RATE_TYPE
2008 ,PROJECT_COST_EXCHANGE_RATE
2009 ,PROJECT_COST_RATE_DATE_TYPE
2010 ,PROJECT_COST_RATE_DATE
2011 ,decode(bucketing_period_code,NULL,tmp.PROJECT_RAW_COST,
2012 decode(bl.PROJECT_RAW_COST||tmp.PROJECT_RAW_COST||tmp.OLD_PROJ_RAW_COST,null,null,
2013 nvl(bl.PROJECT_RAW_COST,0) + (nvl(tmp.PROJECT_RAW_COST,0) - nvl(tmp.OLD_PROJ_RAW_COST,0)))) /* Bug 2774811 */
2014 ,decode(bucketing_period_code,NULL,tmp.PROJECT_BURDENED_COST,
2015 decode(bl.PROJECT_BURDENED_COST||tmp.PROJECT_BURDENED_COST||tmp.OLD_PROJ_BURDENED_COST,null,null,
2016 nvl(bl.PROJECT_BURDENED_COST,0) + (nvl(tmp.PROJECT_BURDENED_COST,0) - nvl(tmp.OLD_PROJ_BURDENED_COST,0)))) /* Bug 2774811 */
2017 ,decode(bucketing_period_code,NULL,tmp.PROJECT_REVENUE,
2018 decode(bl.PROJECT_REVENUE||tmp.PROJECT_REVENUE||tmp.OLD_PROJ_REVENUE,null,null,
2019 nvl(bl.PROJECT_REVENUE,0) + (nvl(tmp.PROJECT_REVENUE,0) - nvl(tmp.OLD_PROJ_REVENUE,0)))) /* Bug 2774811 */
2020 ,decode(bucketing_period_code,NULL,TXN_RAW_COST,
2021 decode(bl.txn_raw_cost||tmp.txn_raw_cost||tmp.old_txn_raw_cost,null,null,
2022 nvl(bl.txn_raw_cost,0) + (nvl(tmp.txn_raw_cost,0) - nvl(tmp.old_txn_raw_cost,0))))
2023 ,decode(bucketing_period_code,NULL,TXN_BURDENED_COST,
2024 decode(bl.txn_burdened_cost||tmp.txn_burdened_cost||tmp.old_txn_burdened_cost,null,null,
2025 nvl(bl.txn_burdened_cost,0) + (nvl(tmp.txn_burdened_cost,0) - nvl(tmp.old_txn_burdened_cost,0))))
2026 ,decode(bucketing_period_code,NULL,TXN_REVENUE,
2027 decode(bl.TXN_REVENUE||tmp.TXN_REVENUE||tmp.old_TXN_REVENUE,null,null,
2028 nvl(bl.TXN_REVENUE,0) + (nvl(tmp.TXN_REVENUE,0) - nvl(tmp.old_TXN_REVENUE,0))))
2029 ,TXN_CURRENCY_CODE
2030 ,BUCKETING_PERIOD_CODE
2031 ,PROJFUNC_REV_RATE_DATE
2032 ,PROJFUNC_REV_RATE_TYPE
2033 ,PROJFUNC_REV_EXCHANGE_RATE
2034 ,PROJFUNC_REV_RATE_DATE_TYPE
2035 ,PROJECT_REV_RATE_DATE
2036 ,PROJECT_REV_RATE_TYPE
2037 ,PROJECT_REV_EXCHANGE_RATE
2038 ,PROJECT_REV_RATE_DATE_TYPE
2039 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2040 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2041 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2042 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2043 /* Code Addition for bug 3394907 starts */
2044 ,sysdate
2045 ,FND_GLOBAL.USER_ID
2046 ,FND_GLOBAL.LOGIN_ID
2047 /* Code Addition for bug 3394907 ends */
2048
2049 FROM pa_fp_rollup_tmp tmp
2050 WHERE bl.budget_line_id = tmp.budget_line_id
2051 AND tmp.budget_line_id IS NOT NULL
2052 AND nvl(tmp.delete_flag,'N') <> 'Y')
2053 WHERE ( bl.budget_line_id ) IN (SELECT tmp.budget_line_id
2054 FROM pa_fp_rollup_tmp tmp
2055 where nvl(tmp.delete_flag,'N') <> 'Y'
2056 AND tmp.budget_line_id IS NOT NULL);
2057
2058 IF P_PA_DEBUG_MODE = 'Y' THEN
2059 pa_debug.g_err_stage := TO_CHAR(l_stage)||'updated '||sql%rowcount||' budget lines ';
2060 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2061 END IF;
2062
2063 /* Bug 2645574: Now a budget_line_id condition is sufficient.
2064 WHERE bl.resource_assignment_id = tmp.resource_assignment_id
2065 AND bl.start_date = tmp.old_start_date
2066 AND tmp.old_start_date IS NOT NULL
2067 AND bl.txn_currency_code = tmp.txn_currency_code
2068 WHERE ( bl.resource_assignment_id
2069 ,bl.start_date
2070 ,bl.txn_currency_code ) IN (SELECT tmp.resource_assignment_id
2071 ,tmp.old_start_date
2072 ,tmp.txn_currency_code
2073 FROM pa_fp_rollup_tmp tmp
2074 where nvl(tmp.delete_flag,'N') <> 'Y'
2075 AND tmp.old_start_date IS NOT NULL) ;
2076 */
2077 /* Introduced following cursor for bug 3289243 */
2078
2079 Open c_disabled_resource_exists;
2080 Fetch c_disabled_resource_exists INTO l_disabled_resource_entered;
2081 Close c_disabled_resource_exists;
2082
2083 IF l_disabled_resource_entered IS NOT NULL THEN
2084 -- throw error
2085 IF P_PA_DEBUG_MODE = 'Y' THEN
2086 pa_debug.g_err_stage := 'Throwing error since amounts are entered for a disabld resource';
2087 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2088 END IF;
2089 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2090 p_msg_name => 'PA_FP_DISABLED_RES_PLANNE');
2091
2092 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2093 END IF;
2094 /* End of code fix for bug 3289243 */
2095
2096 -- insert into budget lines table those
2097 -- records which are present in tmp table
2098 -- but does not exist in budget line table
2099
2100 INSERT INTO pa_budget_lines
2101 ( RESOURCE_ASSIGNMENT_ID
2102 ,BUDGET_LINE_ID
2103 ,BUDGET_VERSION_ID
2104 ,START_DATE
2105 ,LAST_UPDATE_DATE
2106 ,LAST_UPDATED_BY
2107 ,CREATION_DATE
2108 ,CREATED_BY
2109 ,LAST_UPDATE_LOGIN
2110 ,END_DATE
2111 ,PERIOD_NAME
2112 ,QUANTITY
2113 ,RAW_COST
2114 ,BURDENED_COST
2115 ,REVENUE
2116 ,CHANGE_REASON_CODE
2117 ,DESCRIPTION
2118 ,ATTRIBUTE_CATEGORY
2119 ,ATTRIBUTE1
2120 ,ATTRIBUTE2
2121 ,ATTRIBUTE3
2122 ,ATTRIBUTE4
2123 ,ATTRIBUTE5
2124 ,ATTRIBUTE6
2125 ,ATTRIBUTE7
2126 ,ATTRIBUTE8
2127 ,ATTRIBUTE9
2128 ,ATTRIBUTE10
2129 ,ATTRIBUTE11
2130 ,ATTRIBUTE12
2131 ,ATTRIBUTE13
2132 ,ATTRIBUTE14
2133 ,ATTRIBUTE15
2134 ,RAW_COST_SOURCE
2135 ,BURDENED_COST_SOURCE
2136 ,QUANTITY_SOURCE
2137 ,REVENUE_SOURCE
2138 ,PROJFUNC_CURRENCY_CODE
2139 ,PROJFUNC_COST_RATE_TYPE
2140 ,PROJFUNC_COST_EXCHANGE_RATE
2141 ,PROJFUNC_COST_RATE_DATE_TYPE
2142 ,PROJFUNC_COST_RATE_DATE
2143 ,PROJECT_CURRENCY_CODE
2144 ,PROJECT_COST_RATE_TYPE
2145 ,PROJECT_COST_EXCHANGE_RATE
2146 ,PROJECT_COST_RATE_DATE_TYPE
2147 ,PROJECT_COST_RATE_DATE
2148 ,PROJECT_RAW_COST
2149 ,PROJECT_BURDENED_COST
2150 ,PROJECT_REVENUE
2151 ,TXN_RAW_COST
2152 ,TXN_BURDENED_COST
2153 ,TXN_REVENUE
2154 ,TXN_CURRENCY_CODE
2155 ,BUCKETING_PERIOD_CODE
2156 ,PROJFUNC_REV_RATE_DATE_TYPE
2157 ,PROJFUNC_REV_RATE_DATE
2158 ,PROJFUNC_REV_RATE_TYPE
2159 ,PROJFUNC_REV_EXCHANGE_RATE
2160 ,PROJECT_REV_RATE_TYPE
2161 ,PROJECT_REV_EXCHANGE_RATE
2162 ,PROJECT_REV_RATE_DATE_TYPE
2163 ,PROJECT_REV_RATE_DATE
2164 ,PM_PRODUCT_CODE
2165 ,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3858543
2166 (SELECT
2167 RESOURCE_ASSIGNMENT_ID
2168 ,pa_budget_lines_s.nextval
2169 ,l_budget_version_id
2170 ,START_DATE
2171 ,SYSDATE
2172 ,FND_GLOBAL.USER_ID
2173 ,SYSDATE
2174 ,FND_GLOBAL.USER_ID
2175 ,FND_GLOBAL.LOGIN_ID
2176 ,END_DATE
2177 ,PERIOD_NAME
2178 ,QUANTITY
2179 ,PROJFUNC_RAW_COST
2180 ,PROJFUNC_BURDENED_COST
2181 ,PROJFUNC_REVENUE
2182 ,CHANGE_REASON_CODE
2183 ,DESCRIPTION
2184 ,ATTRIBUTE_CATEGORY
2185 ,ATTRIBUTE1
2186 ,ATTRIBUTE2
2187 ,ATTRIBUTE3
2188 ,ATTRIBUTE4
2189 ,ATTRIBUTE5
2190 ,ATTRIBUTE6
2191 ,ATTRIBUTE7
2192 ,ATTRIBUTE8
2193 ,ATTRIBUTE9
2194 ,ATTRIBUTE10
2195 ,ATTRIBUTE11
2196 ,ATTRIBUTE12
2197 ,ATTRIBUTE13
2198 ,ATTRIBUTE14
2199 ,ATTRIBUTE15
2200 ,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2201 ,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2202 ,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2203 ,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
2204 ,PROJFUNC_CURRENCY_CODE
2205 ,PROJFUNC_COST_RATE_TYPE
2206 ,PROJFUNC_COST_EXCHANGE_RATE
2207 ,PROJFUNC_COST_RATE_DATE_TYPE
2208 ,PROJFUNC_COST_RATE_DATE
2209 ,PROJECT_CURRENCY_CODE
2210 ,PROJECT_COST_RATE_TYPE
2211 ,PROJECT_COST_EXCHANGE_RATE
2212 ,PROJECT_COST_RATE_DATE_TYPE
2213 ,PROJECT_COST_RATE_DATE
2214 ,PROJECT_RAW_COST
2215 ,PROJECT_BURDENED_COST
2216 ,PROJECT_REVENUE
2217 ,TXN_RAW_COST
2218 ,TXN_BURDENED_COST
2219 ,TXN_REVENUE
2220 ,TXN_CURRENCY_CODE
2221 ,BUCKETING_PERIOD_CODE
2222 ,PROJFUNC_REV_RATE_DATE_TYPE
2223 ,PROJFUNC_REV_RATE_DATE
2224 ,PROJFUNC_REV_RATE_TYPE
2225 ,PROJFUNC_REV_EXCHANGE_RATE
2226 ,PROJECT_REV_RATE_TYPE
2227 ,PROJECT_REV_EXCHANGE_RATE
2228 ,PROJECT_REV_RATE_DATE_TYPE
2229 ,PROJECT_REV_RATE_DATE
2230 ,pm_product_code -- , l_pm_product_code changed to pm_product_code for bug 3858543
2231 ,pm_budget_line_reference -- Added for bug 3858543
2232 FROM pa_fp_rollup_tmp tmp
2233 /* bug 2645574 changed the condition to look into budget_line_id
2234 WHERE tmp.old_start_date IS NULL
2235 */
2236 WHERE tmp.budget_line_id IS NULL
2237 /* manokuma: added following as PD and SD should not be inserted in this procedure */
2238 /* Bug 2779688 - PD/SD can be inserted in this procedure and hence commenting the where clause
2239 AND nvl(tmp.bucketing_period_code,'XYZ') NOT IN
2240 (PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD) */
2241 AND (tmp.txn_raw_cost IS NOT NULL
2242 or tmp.txn_burdened_cost IS NOT NULL
2243 or tmp.quantity IS NOT NULL
2244 or tmp.txn_revenue IS NOT NULL));
2245
2246 /* Added the following code for WebADI functionality. Increasing the record version number
2247 on pa_budget_versions table everytime there is a change to the version. */
2248
2249 IF P_PA_DEBUG_MODE = 'Y' THEN
2250 pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted '|| sql%rowcount ||' budget lines ';
2251 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2252 END IF;
2253
2254 IF P_PA_DEBUG_MODE = 'Y' THEN
2255 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Increasing record version no. for Budget Version.';
2256 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2257 END IF;
2258
2259 IF nvl(p_calling_context,'-99') <> PA_FP_CONSTANTS_PKG.G_WEBADI THEN
2260 UPDATE pa_budget_versions
2261 SET record_version_number = nvl(record_version_number,0) + 1
2262 WHERE budget_version_id = l_budget_version_id;
2263 END IF;
2264
2265 -- Bug Fix: 4569365. Removed MRC code.
2266 /* Bug# 2641475 - MRC call moved here from before the insert/update of pa_budget_lines */
2267
2268 /* Call MRC API */
2269 /*
2270 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Calling MRC API ';
2271 IF P_PA_DEBUG_MODE = 'Y' THEN
2272 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2273 END IF;
2274
2275 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
2276 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
2277 (x_return_status => x_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data);
2280 END IF;
2281
2282 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
2283 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
2284
2285 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
2286 (p_fin_plan_version_id => l_budget_version_id,
2287 p_entire_version => 'N',
2288 x_return_status => x_return_status,
2289 x_msg_count => x_msg_count,
2290 x_msg_data => x_msg_data);
2291 END IF;
2292
2293 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2294 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Unexpected exception in MRC API '||sqlerrm;
2295 IF P_PA_DEBUG_MODE = 'Y' THEN
2296 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2297 END IF;
2298 RAISE g_mrc_exception;
2299 END IF;
2300 */
2301
2302 pa_debug.g_err_stage := TO_CHAR(l_stage)||'Calling PA_FP_ROLLUP_PKG ';
2303 IF P_PA_DEBUG_MODE = 'Y' THEN
2304 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.g_err_stage,3);
2305 END IF;
2306
2307 -- call the rollup API
2308
2309 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION(
2310 p_budget_version_id => l_budget_version_id
2311 ,p_entire_version => 'N'
2312 ,x_return_status => x_return_status
2313 ,x_msg_count => x_msg_count
2314 ,x_msg_data => x_msg_data ) ;
2315
2316 IF P_PA_DEBUG_MODE = 'Y' THEN
2317 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,'End of process_modified_lines',3);
2318 END IF;
2319
2320 pa_debug.reset_err_stack; /* 2641475 */
2321
2322 EXCEPTION
2323 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2324 x_return_status := FND_API.G_RET_STS_ERROR;
2325 l_msg_count := FND_MSG_PUB.count_msg;
2326 IF l_msg_count = 1 THEN
2327 PA_INTERFACE_UTILS_PUB.get_messages
2328 (p_encoded => FND_API.G_TRUE,
2329 p_msg_index => 1,
2330 p_msg_count => l_msg_count,
2331 p_msg_data => l_msg_data,
2332 p_data => l_data,
2333 p_msg_index_out => l_msg_index_out);
2334
2335 x_msg_data := l_data;
2336 x_msg_count := l_msg_count;
2337 ELSE
2338 x_msg_count := l_msg_count;
2339 END IF;
2340
2341 IF P_PA_DEBUG_MODE = 'Y' THEN
2342 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,'Invalid arguments passed',5);
2343 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.G_Err_Stack,5);
2344 END IF;
2345 pa_debug.reset_err_stack;
2346
2347 RETURN;
2348
2349 WHEN PA_FP_CONSTANTS_PKG.MC_Conversion_Failed_Exc THEN --WEBADI UT.
2350 -- No processing is required here. The processing will be done in WEBADI PKG.
2351 RAISE;
2352 WHEN DUP_VAL_ON_INDEX THEN --Added this handler for AMG.
2353 -- Call the api that adds the error messages for duplicate rows
2354
2355 pa_debug.G_Err_Stack := 'In Dup Value on index. Calling Find_dup_rows_in_rollup_tmp';
2356 IF P_PA_DEBUG_MODE = 'Y' THEN
2357 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.G_Err_Stack,3);
2358 END IF;
2359
2360 PA_FP_EDIT_LINE_PKG.Find_dup_rows_in_rollup_tmp
2361 ( x_return_status => x_return_status
2362 ,x_msg_count => x_msg_count
2363 ,x_msg_data => x_msg_data);
2364
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366 pa_debug.reset_err_stack;
2367 RETURN;
2368
2369 WHEN OTHERS THEN
2370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2371 x_msg_count := 1;
2372 x_msg_data := SQLERRM;
2373
2374 FND_MSG_PUB.add_exc_msg
2375 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG.PROCESS_MODIFIED_LINES'
2376 ,p_procedure_name => pa_debug.G_Err_Stack );
2377
2378 pa_debug.G_Err_Stack := SQLERRM;
2379 IF P_PA_DEBUG_MODE = 'Y' THEN
2380 pa_debug.write('PROCESS_MODIFIED_LINES: ' || l_module_name,pa_debug.G_Err_Stack,4);
2381 END IF;
2382 pa_debug.reset_err_stack;
2383
2384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2385
2386 END PROCESS_MODIFIED_LINES;
2387
2388 PROCEDURE GET_ELEMENT_AMOUNT_INFO
2389 ( p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
2390 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
2391 ,p_calling_context IN VARCHAR2
2392 ,x_quantity_flag OUT NOCOPY pa_fin_plan_amount_sets.cost_qty_flag%TYPE --File.Sql.39 bug 4440895
2393 ,x_raw_cost_flag OUT NOCOPY pa_fin_plan_amount_sets.raw_cost_flag%TYPE --File.Sql.39 bug 4440895
2394 ,x_burdened_cost_flag OUT NOCOPY pa_fin_plan_amount_sets.burdened_cost_flag%TYPE --File.Sql.39 bug 4440895
2395 ,x_revenue_flag OUT NOCOPY pa_fin_plan_amount_sets.revenue_flag%TYPE --File.Sql.39 bug 4440895
2396 /* Changes for FP.M, Tracking Bug No - 3354518. Adding three new OUT parameters x_bill_rate_flag,
2397 x_cost_rate_flag, x_burden_multiplier_flag below for new columns in pa_fin_plan_amount_sets */
2398 /* Changes for FP.M, Tracking Bug No - 3354518. Start here*/
2399 ,x_bill_rate_flag OUT NOCOPY pa_fin_plan_amount_sets.bill_rate_flag%TYPE --File.Sql.39 bug 4440895
2400 ,x_cost_rate_flag OUT NOCOPY pa_fin_plan_amount_sets.cost_rate_flag%TYPE --File.Sql.39 bug 4440895
2401 ,x_burden_multiplier_flag OUT NOCOPY pa_fin_plan_amount_sets.burden_rate_flag%TYPE --File.Sql.39 bug 4440895
2402 /* Changes for FP.M, Tracking Bug No - 3354518. End here*/
2403 ,x_period_profile_id OUT NOCOPY pa_proj_periods_denorm.PERIOD_PROFILE_ID%TYPE --File.Sql.39 bug 4440895
2404 ,x_plan_period_type OUT NOCOPY pa_proj_period_profiles.PLAN_PERIOD_TYPE%TYPE --File.Sql.39 bug 4440895
2405 ,x_quantity OUT NOCOPY pa_budget_lines.QUANTITY%TYPE --File.Sql.39 bug 4440895
2406 ,x_project_raw_cost OUT NOCOPY pa_budget_lines.RAW_COST%TYPE --File.Sql.39 bug 4440895
2407 ,x_project_burdened_cost OUT NOCOPY pa_budget_lines.BURDENED_COST%TYPE --File.Sql.39 bug 4440895
2408 ,x_project_revenue OUT NOCOPY pa_budget_lines.REVENUE%TYPE --File.Sql.39 bug 4440895
2409 ,x_projfunc_raw_cost OUT NOCOPY pa_budget_lines.RAW_COST%TYPE --File.Sql.39 bug 4440895
2410 ,x_projfunc_burdened_cost OUT NOCOPY pa_budget_lines.BURDENED_COST%TYPE --File.Sql.39 bug 4440895
2411 ,x_projfunc_revenue OUT NOCOPY pa_budget_lines.REVENUE%TYPE --File.Sql.39 bug 4440895
2412 ,x_projfunc_margin OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2413 ,x_projfunc_margin_percent OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2414 ,x_proj_margin OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2415 ,x_proj_margin_percent OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2416 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2417 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2418 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
2419 /*
2420 Bug#2668836
2421 New parameters are added for project functional currency and project currency
2422 to calculate margin and margin%. The parameters are :
2423 x_projfunc_margin,x_project_margin,
2424 x_projfunc_margin_percent,x_project_margin_percent
2425 */
2426
2427 /* Variables to be used for debugging purpose */
2428
2429 l_msg_count NUMBER := 0;
2430 l_data VARCHAR2(2000);
2431 l_msg_data VARCHAR2(2000);
2432 l_msg_index_out NUMBER;
2433 l_return_status VARCHAR2(2000);
2434 l_debug_mode VARCHAR2(10);
2435 l_stage NUMBER := 100;
2436
2437 /* Variables to be used for debugging purpose */
2438
2439 l_amount_set_id NUMBER ;
2440 /* Bug #2645300: Removing the defaulting of the quantity flags. */
2441 l_cost_qty_flag VARCHAR2(1) ;
2442 l_revenue_qty_flag VARCHAR2(1) ;
2443 l_all_qty_flag VARCHAR2(1) ;
2444 l_period_profile_type VARCHAR2(30) := null;
2445
2446 -- l_gl_period_type VARCHAR2(30) := null;
2447 -- l_plan_period_type VARCHAR2(30) := null;
2448 -- l_number_of_periods NUMBER ;
2449
2450
2451 l_preceding_prd_start_date pa_budget_lines.start_date%TYPE;
2452 l_preceding_prd_end_date pa_budget_lines.start_date%TYPE;
2453 l_succeeding_prd_start_date pa_budget_lines.start_date%TYPE;
2454 l_succeeding_prd_end_date pa_budget_lines.start_date%TYPE;
2455 l_period_profile_start_date pa_budget_lines.start_date%TYPE;
2456 l_period_profile_end_date pa_budget_lines.start_date%TYPE;
2457 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE ;
2458 l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE ;
2459 l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE ;
2460 l_project_start_date pa_projects_all.start_date%TYPE ;
2461 l_project_end_date pa_projects_all.start_date%TYPE;
2462 l_task_start_date pa_projects_all.start_date%TYPE;
2463 l_task_end_date pa_projects_all.start_date%TYPE;
2464 l_plan_period_type pa_proj_period_profiles.plan_period_type%TYPE;
2465 l_period_set_name pa_proj_period_profiles.period_set_name%TYPE;
2466 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2467 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2468 l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
2469 l_dummy_project_id pa_projects_all.project_id%TYPE;
2470
2471 BEGIN
2472 -- Set the error stack.
2473 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.GET_ELEMENT_AMOUNT_INFO');
2474
2475 -- Get the Debug mode into local variable and set it to 'Y' if its NULL
2476 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2477 l_debug_mode := NVL(l_debug_mode, 'Y');
2478
2479 -- Initialize the return status to success
2480 x_return_status := FND_API.G_RET_STS_SUCCESS;
2481
2482 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2483
2484 pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_EDIT_LINE_PKG.GET_ELEMENT_AMOUNT_INFO ';
2485 IF P_PA_DEBUG_MODE = 'Y' THEN
2486 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,3);
2487 END IF;
2488
2489 -- Validating for the Input Parameters
2490
2491 IF p_calling_context IS NULL THEN
2492
2493 pa_debug.g_err_stage := 'calling context is null.';
2494 IF P_PA_DEBUG_MODE = 'Y' THEN
2495 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2496 END IF;
2497
2498 x_return_status := FND_API.G_RET_STS_ERROR;
2499
2500 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2501 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2502
2503 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2504
2505 ELSIF p_calling_context = PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
2506
2507 IF p_resource_assignment_id IS NULL THEN
2508
2509 pa_debug.g_err_stage := 'resource assignment id is null in edit in another currency.';
2510 IF P_PA_DEBUG_MODE = 'Y' THEN
2511 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2512 END IF;
2513
2514 x_return_status := FND_API.G_RET_STS_ERROR;
2515
2516 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2517 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2518
2519 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2520 END IF;
2521 ELSE
2522
2523 IF p_resource_assignment_id IS NULL OR
2524 p_txn_currency_code IS NULL
2525 THEN
2526 pa_debug.g_err_stage := 'one of the input parameter is null.';
2527 IF P_PA_DEBUG_MODE = 'Y' THEN
2528 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2529 END IF;
2530
2531
2532 pa_debug.g_err_stage := 'p_resource_assignment_id = ' || p_resource_assignment_id;
2533 IF P_PA_DEBUG_MODE = 'Y' THEN
2534 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2535 END IF;
2536
2537 pa_debug.g_err_stage := 'p_txn_currency_code = ' || p_txn_currency_code;
2538 IF P_PA_DEBUG_MODE = 'Y' THEN
2539 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2540 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,5);
2541 END IF;
2542
2543 x_return_status := FND_API.G_RET_STS_ERROR;
2544
2545 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2546 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2547
2548 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2549 END IF;
2550 END IF;
2551
2552
2553 POPULATE_LOCAL_VARIABLES(
2554 p_resource_assignment_id => p_resource_assignment_id
2555 ,p_txn_currency_code => p_txn_currency_code
2556 ,p_calling_context => p_calling_context
2557 ,x_preceding_prd_start_date => l_preceding_prd_start_date
2558 ,x_preceding_prd_end_date => l_preceding_prd_end_date
2559 ,x_succeeding_prd_start_date => l_succeeding_prd_start_date
2560 ,x_succeeding_prd_end_date => l_succeeding_prd_end_date
2561 ,x_period_profile_start_date => l_period_profile_start_date
2562 ,x_period_profile_end_date => l_period_profile_end_date
2563 ,x_period_profile_id => x_period_profile_id
2564 ,x_time_phased_code => l_time_phased_code
2565 ,x_fin_plan_version_id => l_fin_plan_version_id
2566 ,x_fin_plan_level_code => l_fin_plan_level_code
2567 ,x_project_start_date => l_project_start_date
2568 ,x_project_end_date => l_project_end_date
2569 ,x_task_start_date => l_task_start_date
2570 ,x_task_end_date => l_task_end_date
2571 ,x_plan_period_type => x_plan_period_type
2572 ,x_period_set_name => l_period_set_name
2573 ,x_project_currency_code => l_project_currency_code
2574 ,x_projfunc_currency_code => l_projfunc_currency_code
2575 ,x_project_id => l_dummy_project_id
2576 ,x_return_status => l_return_status
2577 ,x_msg_count => l_msg_count
2578 ,x_msg_data => l_msg_data
2579 );
2580
2581 l_amount_set_id := pa_fin_plan_utils.Get_Amount_Set_Id(
2582 p_fin_plan_version_id => l_fin_plan_version_id);
2583
2584
2585 IF p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN
2586
2587 pa_debug.g_err_stage := TO_CHAR(l_stage)||':calling PA_FIN_PLAN_UTILS.Get_Element_Proj_PF_Amounts ';
2588 IF P_PA_DEBUG_MODE = 'Y' THEN
2589 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,3);
2590 END IF;
2591 BEGIN
2592
2593 SELECT margin_derived_from_code
2594 INTO l_margin_derived_from_code
2595 FROM pa_proj_fp_options
2596 WHERE fin_plan_version_id = l_fin_plan_version_id;
2597 EXCEPTION
2598 WHEN NO_DATA_FOUND THEN
2599 NULL;
2600 -- DO NOTHING
2601 END;
2602
2603 PA_FIN_PLAN_UTILS.Get_Element_Proj_PF_Amounts(
2604 p_resource_assignment_id => p_resource_assignment_id
2605 ,p_txn_currency_code => p_txn_currency_code
2606 ,x_quantity => x_quantity
2607 ,x_project_raw_cost => x_project_raw_cost
2608 ,x_project_burdened_cost => x_project_burdened_cost
2609 ,x_project_revenue => x_project_revenue
2610 ,x_projfunc_raw_cost => x_projfunc_raw_cost
2611 ,x_projfunc_burdened_cost => x_projfunc_burdened_cost
2612 ,x_projfunc_revenue => x_projfunc_revenue
2613 ,x_return_status => x_return_status
2614 ,x_msg_count => x_msg_count
2615 ,x_msg_data => x_msg_data
2616 ) ;
2617
2618
2619 -- Bug#2668836
2620 -- checking if margin derived from -code is for raw cost or burdened cost
2621 -- the calculations are made according to margin derived basis.
2622 /* Initializing the value of out parameters */
2623 x_projfunc_margin := 0;
2624 x_projfunc_margin_percent := 0;
2625 x_proj_margin := 0;
2626 x_proj_margin_percent := 0;
2627 /* Bug#2713480 */
2628 /* Changed the code from Raw cost/Burden cost to revenue for calculating */
2629 /* and for margin % multiply it by 100 */
2630 IF l_margin_derived_from_code IS NOT NULL THEN
2631 IF l_margin_derived_from_code = 'R' THEN
2632 IF x_projfunc_revenue <> 0 THEN
2633 x_projfunc_margin := x_projfunc_revenue - x_projfunc_raw_cost;
2634 x_projfunc_margin_percent := (x_projfunc_margin / x_projfunc_revenue)*100;
2635 END IF;
2636 IF x_project_revenue <> 0 THEN
2637 x_proj_margin := x_project_revenue - x_project_raw_cost;
2638 x_proj_margin_percent := (x_proj_margin / x_project_revenue)*100;
2639 END IF;
2640 ELSE
2641 IF x_projfunc_revenue <> 0 THEN
2642 x_projfunc_margin := x_projfunc_revenue - x_projfunc_burdened_cost;
2643 x_projfunc_margin_percent := (x_projfunc_margin / x_projfunc_revenue)*100;
2644 END IF;
2645 IF x_project_revenue <> 0 THEN
2646 x_proj_margin := x_project_revenue - x_project_burdened_cost;
2647 x_proj_margin_percent := (x_proj_margin / x_project_revenue)*100;
2648 END IF;
2649 END IF; -- end of IF l_margin_derived_from_code = 'R'
2650 END IF; -- end of IF l_margin_derived_from_code IS NOT NULL
2651 pa_debug.g_err_stage := TO_CHAR(x_projfunc_margin)||':'||TO_CHAR(x_projfunc_margin_percent)||':'||TO_CHAR(x_proj_margin)||':'||TO_CHAR(x_proj_margin_percent);
2652 IF P_PA_DEBUG_MODE = 'Y' THEN
2653 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,3);
2654 END IF;
2655 END IF ;
2656 /* p_calling_context <> PA_FP_CONSTANTS_PKG.G_CALLING_CONTEXT_OTHER_CURR THEN */
2657
2658 pa_debug.g_err_stage := TO_CHAR(l_stage)||':calling PA_FIN_PLAN_UTILS.GET_PLAN_AMOUNT_FLAGS ';
2659 IF P_PA_DEBUG_MODE = 'Y' THEN
2660 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.g_err_stage,3);
2661 END IF;
2662
2663
2664 /* Changes for FP.M, Tracking Bug No - 3354518. Adding three new arguements for x_bill_rate_flag,
2665 x_cost_rate_flag, x_burden_multiplier_flag below for new columns in pa_fin_plan_amount_sets
2666 The Signature of the API PA_FIN_PLAN_UTILS.GET_PLAN_AMOUNT_FLAGS has now changed now, so the
2667 API call below is being accrodingly modified. */
2668 PA_FIN_PLAN_UTILS.GET_PLAN_AMOUNT_FLAGS(
2669 P_AMOUNT_SET_ID => l_amount_set_id
2670 ,X_RAW_COST_FLAG => x_raw_cost_flag
2671 ,X_BURDENED_FLAG => x_burdened_cost_flag
2672 ,X_REVENUE_FLAG => x_revenue_flag
2673 ,X_COST_QUANTITY_FLAG => l_cost_qty_flag
2674 ,X_REV_QUANTITY_FLAG => l_revenue_qty_flag
2675 ,X_ALL_QUANTITY_FLAG => l_all_qty_flag
2676 /* Changes for FP.M, Tracking Bug No - 3354518. Start here*/
2677 ,X_BILL_RATE_FLAG => x_bill_rate_flag
2678 ,X_COST_RATE_FLAG => x_cost_rate_flag
2679 ,X_BURDEN_RATE_FLAG => x_burden_multiplier_flag
2680 /* Changes for FP.M, Tracking Bug No - 3354518. Start here*/
2681 ,x_message_count => x_msg_count
2682 ,x_return_status => x_return_status
2683 ,x_message_data => x_msg_data) ;
2684
2685 /* #2645300: Set the x_quantity out parameter based on the qty_flag local variables.
2686 If all the aty_flags are 'N', then the quantity flag should be 'N', if any of the
2687 qty_flags is 'Y', the quantity flag should be 'Y'. */
2688
2689 IF (nvl(l_cost_qty_flag,'N') = 'N' AND nvl(l_revenue_qty_flag,'N') = 'N' AND
2690 nvl(l_all_qty_flag,'N') = 'N') THEN
2691
2692 x_quantity_flag := 'N';
2693
2694 ELSIF (nvl(l_cost_qty_flag,'N') = 'Y' OR nvl(l_revenue_qty_flag,'N') = 'Y' OR
2695 nvl(l_all_qty_flag,'N') = 'Y') THEN
2696
2697 x_quantity_flag := 'Y';
2698
2699 END IF;
2700
2701 pa_debug.reset_err_stack; /* Bug 2699888 */
2702
2703 EXCEPTION
2704 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2705 x_return_status := FND_API.G_RET_STS_ERROR;
2706 l_msg_count := FND_MSG_PUB.count_msg;
2707 IF l_msg_count = 1 THEN
2708 PA_INTERFACE_UTILS_PUB.get_messages
2709 (p_encoded => FND_API.G_TRUE,
2710 p_msg_index => 1,
2711 p_msg_count => l_msg_count,
2712 p_msg_data => l_msg_data,
2713 p_data => l_data,
2714 p_msg_index_out => l_msg_index_out);
2715
2716 x_msg_data := l_data;
2717 x_msg_count := l_msg_count;
2718 ELSE
2719 x_msg_count := l_msg_count;
2720 END IF;
2721
2722 IF P_PA_DEBUG_MODE = 'Y' THEN
2723 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,'Invalid arguments passed',5);
2724 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.G_Err_Stack,5);
2725 END IF;
2726 pa_debug.reset_err_stack;
2727
2728 WHEN OTHERS THEN
2729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2730 x_msg_count := 1;
2731 x_msg_data := SQLERRM;
2732 FND_MSG_PUB.add_exc_msg
2733 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG.GET_ELEMENT_AMOUNT_INFO'
2734 ,p_procedure_name => pa_debug.G_Err_Stack );
2735 pa_debug.G_Err_Stack := SQLERRM;
2736 IF P_PA_DEBUG_MODE = 'Y' THEN
2737 pa_debug.write('GET_ELEMENT_AMOUNT_INFO: ' || l_module_name,pa_debug.G_Err_Stack,4);
2738 END IF;
2739 pa_debug.reset_err_stack;
2740
2741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742
2743 END GET_ELEMENT_AMOUNT_INFO;
2744
2745 PROCEDURE CALL_CLIENT_EXTENSIONS
2746 ( p_project_id IN pa_projects_all.PROJECT_ID%TYPE
2747 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
2748 ,p_task_id IN pa_tasks.TASK_ID%TYPE
2749 ,p_resource_list_member_id IN pa_resource_list_members.RESOURCE_LIST_MEMBER_ID%TYPE
2750 ,p_resource_list_id IN pa_resource_lists.RESOURCE_LIST_ID%TYPE
2751 ,p_resource_id IN pa_resources.RESOURCE_ID%TYPE
2752 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
2753 ,p_product_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
2754 ,p_start_date_tbl IN SYSTEM.pa_date_tbl_type
2755 ,p_end_date_tbl IN SYSTEM.pa_date_tbl_type
2756 ,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type
2757 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
2758 ,px_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
2759 ,px_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
2760 ,px_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
2761 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2762 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2763 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2764 )
2765
2766 IS
2767
2768 l_err_code NUMBER := 0;
2769 l_err_message VARCHAR2(100);
2770 l_debug_mode VARCHAR2(30);
2771 l_stage NUMBER := 100 ;
2772 l_err_stage VARCHAR2(120);
2773 l_msg_count NUMBER :=0 ;
2774 l_data VARCHAR2(160) ;
2775 l_msg_data VARCHAR2(160) ;
2776 l_msg_index_out NUMBER;
2777
2778 l_res_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2779 l_task_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2780 l_resource_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2781 l_txn_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type() ;
2782
2783 BEGIN
2784
2785 x_msg_count := 0;
2786 x_return_status := FND_API.G_RET_STS_SUCCESS;
2787 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'Y');
2788
2789 IF l_debug_mode = 'Y' THEN
2790 pa_debug.set_curr_function( p_function => 'CALL_CLIENT_EXTENSIONS'
2791 ,p_debug_mode => l_debug_mode );
2792 END IF;
2793
2794 IF l_debug_mode = 'Y' THEN
2795 pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS ';
2796 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
2797 END IF;
2798
2799 IF (p_project_id IS NULL) OR (p_budget_version_id IS NULL) THEN
2800 IF l_debug_mode = 'Y' THEN
2801 pa_debug.g_err_stage := TO_CHAR(l_stage)||':Invalid Input Parameters';
2802 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,1);
2803
2804 END IF;
2805 x_return_status := FND_API.G_RET_STS_ERROR;
2806 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2807 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2808 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2809 END IF;
2810
2811 IF l_debug_mode = 'Y' THEN
2812 pa_debug.g_err_stage := 'before extending p_start_date_tbl.last = ' || p_start_date_tbl.last;
2813 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
2814 END IF;
2815
2816 l_task_id_tbl.extend(p_start_date_tbl.last);
2817 l_res_list_member_id_tbl.extend(p_start_date_tbl.last);
2818 l_resource_id_tbl.extend(p_start_date_tbl.last);
2819 l_txn_currency_code_tbl.extend(p_start_date_tbl.last);
2820
2821 IF l_debug_mode = 'Y' THEN
2822 pa_debug.g_err_stage := 'before filling up the tables';
2823 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
2824 END IF;
2825
2826 IF nvl(p_start_date_tbl.last,0) >= 1 THEN
2827 FOR i in p_start_date_tbl.FIRST..p_start_date_tbl.LAST LOOP
2828 l_task_id_tbl(i) := p_task_id ;
2829 l_res_list_member_id_tbl(i) := p_resource_list_member_id ;
2830 l_resource_id_tbl(i) := p_resource_id ;
2831 l_txn_currency_code_tbl(i) := p_txn_currency_code ;
2832 END LOOP ;
2833 END IF ;
2834
2835 IF l_debug_mode = 'Y' THEN
2836 pa_debug.g_err_stage := 'PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS' ;
2837 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
2838 END IF;
2839
2840 PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS
2841 ( p_project_id => p_project_id
2842 ,p_budget_version_id => p_budget_version_id
2843 ,p_task_id_tbl => l_task_id_tbl
2844 ,p_res_list_member_id_tbl => l_res_list_member_id_tbl
2845 ,p_resource_list_id => p_resource_list_id
2846 ,p_resource_id_tbl => l_resource_id_tbl
2847 ,p_txn_currency_code_tbl => l_txn_currency_code_tbl
2848 ,p_product_code_tbl => p_product_code_tbl
2849 ,p_start_date_tbl => p_start_date_tbl
2850 ,p_end_date_tbl => p_end_date_tbl
2851 ,p_period_name_tbl => p_period_name_tbl
2852 ,p_quantity_tbl => p_quantity_tbl
2853 ,px_raw_cost_tbl => px_raw_cost_tbl
2854 ,px_burdened_cost_tbl => px_burdened_cost_tbl
2855 ,px_revenue_tbl => px_revenue_tbl
2856 ,x_return_status => x_return_status
2857 ,x_msg_count => x_msg_count
2858 ,x_msg_data => x_msg_data
2859 ) ;
2860
2861
2862 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2863 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2864 END IF ;
2865
2866 IF l_debug_mode = 'Y' THEN
2867 pa_debug.g_err_stage:= 'Exiting CALL_CLIENT_EXTENSION' ;
2868 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2869 pa_debug.reset_curr_function;
2870 END IF;
2871
2872 EXCEPTION
2873 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2874 x_return_status := FND_API.G_RET_STS_ERROR;
2875 l_msg_count := FND_MSG_PUB.count_msg;
2876 IF l_debug_mode = 'Y' THEN
2877 pa_debug.g_err_stage := 'inside invalid arg exception of call_client_extensions';
2878 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
2879 END IF;
2880 IF l_msg_count = 1 THEN
2881 PA_INTERFACE_UTILS_PUB.get_messages
2882 (p_encoded => FND_API.G_TRUE,
2883 p_msg_index => 1,
2884 p_msg_count => l_msg_count,
2885 p_msg_data => l_msg_data,
2886 p_data => l_data,
2887 p_msg_index_out => l_msg_index_out);
2888 x_msg_data := l_data;
2889 x_msg_count := l_msg_count;
2890 ELSE
2891 x_msg_count := l_msg_count;
2892 END IF;
2893 IF l_debug_mode = 'Y' THEN
2894 pa_debug.reset_curr_function;
2895 END IF ;
2896 RETURN;
2897 WHEN OTHERS THEN
2898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2899 x_msg_count := 1;
2900 x_msg_data := SQLERRM;
2901 FND_MSG_PUB.add_exc_msg
2902 ( p_pkg_name => 'PA_FP_LINE_EDIT_PKG'
2903 ,p_procedure_name => 'CALL_CLIENT_EXTENSION'
2904 ,p_error_text => sqlerrm);
2905
2906 IF l_debug_mode = 'Y' THEN
2907 pa_debug.g_err_stage := 'inside others exception of process_xface_lines';
2908 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
2909 pa_debug.G_Err_Stack := SQLERRM;
2910 pa_debug.reset_curr_function;
2911 END IF;
2912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2913 END CALL_CLIENT_EXTENSIONS;
2914
2915 PROCEDURE POPULATE_ELIGIBLE_PERIODS
2916 ( p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
2917 ,p_period_profile_start_date IN pa_budget_lines.start_date%TYPE
2918 ,p_period_profile_end_date IN pa_budget_lines.end_date%TYPE
2919 ,p_preceding_prd_start_date IN pa_budget_lines.start_Date%TYPE
2920 ,p_succeeding_prd_start_date IN pa_budget_lines.start_Date%TYPE
2921 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2922 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2923 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2924 IS
2925 l_stage NUMBER := 0;
2926 l_msg_count NUMBER :=0 ;
2927 l_data VARCHAR2(160) ;
2928 l_msg_data VARCHAR2(160) ;
2929 l_msg_index_out NUMBER;
2930 l_debug_mode VARCHAR2(30);
2931 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
2932
2933 BEGIN
2934 -- Set the error stack.
2935 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.POPULATE_ELIGIBLE_PERIODS');
2936
2937 -- Get the Debug mode into local variable and set it to 'Y' if its NULL
2938 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2939 l_debug_mode := NVL(l_debug_mode, 'Y');
2940
2941 -- Initialize the return status to success
2942 x_return_status := FND_API.G_RET_STS_SUCCESS;
2943
2944 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2945
2946 pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_EDIT_LINE_PKG.POPULATE_ELIGIBLE_PERIODS ';
2947 IF P_PA_DEBUG_MODE = 'Y' THEN
2948 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2949 END IF;
2950
2951 IF (p_fin_plan_version_id IS NULL) THEN
2952 pa_debug.g_err_stage := TO_CHAR(l_stage)||':Invalid Input Parameters';
2953 IF P_PA_DEBUG_MODE = 'Y' THEN
2954 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,1);
2955 END IF;
2956 x_return_status := FND_API.G_RET_STS_ERROR;
2957 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2958 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2959 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2960 END IF;
2961
2962 l_stage := 200;
2963 pa_debug.g_err_stage := TO_CHAR(l_stage)||':Starting the main processing. Inserting into temp table';
2964 IF P_PA_DEBUG_MODE = 'Y' THEN
2965 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2966 END IF;
2967
2968 l_time_phased_code := pa_fin_plan_utils.get_time_phased_code(
2969 p_fin_plan_version_id => p_fin_plan_version_id);
2970
2971 pa_debug.g_err_stage:='l_time_phased_code is '|| l_time_phased_code;
2972 IF P_PA_DEBUG_MODE = 'Y' THEN
2973 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2974 END IF;
2975
2976 pa_debug.g_err_stage:='l_start_period_start_date is '||to_char(p_period_profile_start_date);
2977 IF P_PA_DEBUG_MODE = 'Y' THEN
2978 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2979 END IF;
2980
2981 pa_debug.g_err_stage:='l_end_period_end_date is '||to_char(p_period_profile_end_date) ;
2982 IF P_PA_DEBUG_MODE = 'Y' THEN
2983 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2984 END IF;
2985
2986 pa_debug.g_err_stage:='p_preceding_prd_start_date is '||to_char(p_preceding_prd_start_date);
2987 IF P_PA_DEBUG_MODE = 'Y' THEN
2988 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2989 END IF;
2990
2991 pa_debug.g_err_stage:='p_succeeding_prd_start_date is '||to_char(p_succeeding_prd_start_date) ;
2992 IF P_PA_DEBUG_MODE = 'Y' THEN
2993 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2994 END IF;
2995
2996 delete from pa_fp_cpy_periods_tmp;
2997
2998 pa_debug.g_err_stage:='deleted '||sql%rowcount || ' records from tmp table' ;
2999 IF P_PA_DEBUG_MODE = 'Y' THEN
3000 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3001 END IF;
3002
3003
3004 IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P THEN
3005
3006 pa_debug.g_err_stage:='Populating pa_fp_cpy_periods_tmp in the case of pa_period time phasing';
3007 IF P_PA_DEBUG_MODE = 'Y' THEN
3008 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3009 END IF;
3010
3011 IF p_preceding_prd_start_date IS NOT NULL THEN
3012
3013 pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
3014 IF P_PA_DEBUG_MODE = 'Y' THEN
3015 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3016 END IF;
3017
3018 INSERT INTO pa_fp_cpy_periods_tmp
3019 ( start_date
3020 ,end_date
3021 ,pa_period_name
3022 ,gl_period_name
3023 ,period_name )
3024 SELECT start_date start_date
3025 ,end_date end_date
3026 ,period_name pa_period
3027 ,gl_period_name gl_period
3028 ,period_name period_name
3029 FROM PA_PERIODS
3030 WHERE start_date = p_preceding_prd_start_date;
3031
3032 END IF;
3033
3034 INSERT INTO pa_fp_cpy_periods_tmp
3035 ( start_date
3036 ,end_date
3037 ,pa_period_name
3038 ,gl_period_name
3039 ,period_name )
3040 SELECT start_date start_date
3041 ,end_date end_date
3042 ,period_name pa_period
3043 ,gl_period_name gl_period
3044 ,period_name period_name
3045 FROM PA_PERIODS
3046 WHERE start_date BETWEEN p_period_profile_start_date AND p_period_profile_end_date;
3047
3048 pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted ' || sql%rowcount || ' records';
3049 IF P_PA_DEBUG_MODE = 'Y' THEN
3050 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3051 END IF;
3052
3053 IF p_succeeding_prd_start_date IS NOT NULL THEN
3054
3055 pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for succeeding period' ;
3056 IF P_PA_DEBUG_MODE = 'Y' THEN
3057 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3058 END IF;
3059
3060 INSERT INTO pa_fp_cpy_periods_tmp
3061 ( start_date
3062 ,end_date
3063 ,pa_period_name
3064 ,gl_period_name
3065 ,period_name )
3066 SELECT start_date start_date
3067 ,end_date end_date
3068 ,period_name pa_period
3069 ,gl_period_name gl_period
3070 ,period_name period_name
3071 FROM PA_PERIODS
3072 WHERE start_date = p_succeeding_prd_start_date;
3073
3074 END IF;
3075
3076 ELSIF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G THEN
3077
3078 pa_debug.g_err_stage:='Populating pa_fp_cpy_periods_tmp in the case of gl_period time phasing';
3079 IF P_PA_DEBUG_MODE = 'Y' THEN
3080 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,3);
3081 END IF;
3082
3083
3084 IF p_preceding_prd_start_date IS NOT NULL THEN
3085
3086 pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
3087 IF P_PA_DEBUG_MODE = 'Y' THEN
3088 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3089 END IF;
3090
3091 INSERT INTO pa_fp_cpy_periods_tmp
3092 ( start_date
3093 ,end_date
3094 ,pa_period_name
3095 ,gl_period_name
3096 ,period_name )
3097 SELECT g.start_date start_date
3098 ,g.end_date end_date
3099 ,'null' pa_period
3100 ,g.period_name gl_period
3101 ,g.period_name period_name
3102 FROM PA_IMPLEMENTATIONS i
3103 ,GL_PERIOD_STATUSES g
3104 WHERE g.set_of_books_id = i.set_of_books_id
3105 AND g.application_id = pa_period_process_pkg.application_id
3106 AND g.adjustment_period_flag = 'N'
3107 AND g.start_date = p_preceding_prd_start_date;
3108
3109 END IF;
3110
3111 INSERT INTO pa_fp_cpy_periods_tmp(
3112 start_date
3113 ,end_date
3114 ,pa_period_name
3115 ,gl_period_name
3116 ,period_name )
3117 SELECT g.start_date start_date
3118 ,g.end_date end_period
3119 ,'null' pa_period /* this value is never used */
3120 ,g.period_name gl_period
3121 ,g.period_name period_name
3122 FROM PA_IMPLEMENTATIONS i
3123 ,GL_PERIOD_STATUSES g
3124 WHERE g.set_of_books_id = i.set_of_books_id
3125 AND g.application_id = pa_period_process_pkg.application_id
3126 AND g.adjustment_period_flag = 'N'
3127 AND g.start_date BETWEEN p_period_profile_start_date AND p_period_profile_end_date;
3128
3129 pa_debug.g_err_stage := TO_CHAR(l_stage)||'inserted ' || sql%rowcount || ' records';
3130 IF P_PA_DEBUG_MODE = 'Y' THEN
3131 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3132 END IF;
3133
3134 IF p_succeeding_prd_start_date IS NOT NULL THEN
3135
3136 pa_debug.g_err_stage:='inserting into pa_fp_cpy_periods_tmp for preceding period' ;
3137 IF P_PA_DEBUG_MODE = 'Y' THEN
3138 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3139 END IF;
3140
3141 INSERT INTO pa_fp_cpy_periods_tmp
3142 ( start_date
3143 ,end_date
3144 ,pa_period_name
3145 ,gl_period_name
3146 ,period_name )
3147 SELECT g.start_date start_date
3148 ,g.end_date end_date
3149 ,'null' pa_period
3150 ,g.period_name gl_period
3151 ,g.period_name period_name
3152 FROM PA_IMPLEMENTATIONS i
3153 ,GL_PERIOD_STATUSES g
3154 WHERE g.set_of_books_id = i.set_of_books_id
3155 AND g.application_id = pa_period_process_pkg.application_id
3156 AND g.adjustment_period_flag = 'N'
3157 AND g.start_date = p_succeeding_prd_start_date;
3158
3159 END IF;
3160
3161 END IF;
3162
3163 pa_debug.g_err_stage := TO_CHAR(l_stage)||'end of PA_FP_EDIT_LINE_PKG.POPULATE_ELIGIBLE_PERIODS';
3164 IF P_PA_DEBUG_MODE = 'Y' THEN
3165 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3166 END IF;
3167
3168 pa_debug.reset_err_stack; /* Bug 2699888 */
3169
3170 EXCEPTION
3171 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3172
3173 x_return_status := FND_API.G_RET_STS_ERROR;
3174 l_msg_count := FND_MSG_PUB.count_msg;
3175 IF l_msg_count = 1 THEN
3176 PA_INTERFACE_UTILS_PUB.get_messages
3177 (p_encoded => FND_API.G_TRUE,
3178 p_msg_index => 1,
3179 p_msg_count => l_msg_count,
3180 p_msg_data => l_msg_data,
3181 p_data => l_data,
3182 p_msg_index_out => l_msg_index_out);
3183 x_msg_data := l_data;
3184 x_msg_count := l_msg_count;
3185 ELSE
3186 x_msg_count := l_msg_count;
3187 END IF;
3188
3189 IF P_PA_DEBUG_MODE = 'Y' THEN
3190 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,'Invalid arguments passed or some expected error in client extns',5);
3191 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.G_Err_Stack,5);
3192 END IF;
3193 pa_debug.reset_err_stack;
3194
3195 RAISE;
3196 WHEN OTHERS THEN
3197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3198 x_msg_count := 1;
3199 x_msg_data := SQLERRM;
3200 FND_MSG_PUB.add_exc_msg
3201 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG.POPULATE_ELIGIBLE_PERIODS'
3202 ,p_procedure_name => pa_debug.G_Err_Stack );
3203 pa_debug.G_Err_Stack := SQLERRM;
3204 IF P_PA_DEBUG_MODE = 'Y' THEN
3205 pa_debug.write('POPULATE_ELIGIBLE_PERIODS: ' || l_module_name,pa_debug.G_Err_Stack,4);
3206 END IF;
3207 pa_debug.reset_err_stack;
3208
3209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3210
3211 END POPULATE_ELIGIBLE_PERIODS;
3212
3213 /*
3214 This api returns the PC and PFC amounts for the input resource assignment id and txn currency code.
3215 Since we want the PD/SD amounts for a particular txn currency we need get it from budget lines
3216 table. PPD table will contain the PC/PFC for ALL txn currencies for the raid and not the split up
3217 for each txn currency and hence cannot be used.
3218 */
3219
3220 PROCEDURE GET_PD_SD_AMT_IN_PC_PFC(
3221 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
3222 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
3223 ,p_period_profile_id IN pa_budget_versions.period_profile_id%TYPE
3224 ,x_pd_pc_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3225 ,x_pd_pfc_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3226 ,x_sd_pc_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3227 ,x_sd_pfc_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3228 ,x_pd_pc_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3229 ,x_pd_pfc_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3230 ,x_sd_pc_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3231 ,x_sd_pfc_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3232 ,x_pd_pc_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3233 ,x_pd_pfc_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3234 ,x_sd_pc_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3235 ,x_sd_pfc_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3236 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3237 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3238 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3239
3240 AS
3241
3242 l_msg_count NUMBER := 0;
3243 l_data VARCHAR2(2000);
3244 l_msg_data VARCHAR2(2000);
3245 l_msg_index_out NUMBER;
3246 l_debug_mode VARCHAR2(1);
3247
3248 l_debug_level2 CONSTANT NUMBER := 2;
3249 l_debug_level3 CONSTANT NUMBER := 3;
3250 l_debug_level4 CONSTANT NUMBER := 4;
3251 l_debug_level5 CONSTANT NUMBER := 5;
3252
3253 BEGIN
3254
3255 x_msg_count := 0;
3256 x_return_status := FND_API.G_RET_STS_SUCCESS;
3257 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3258
3259 pa_debug.set_curr_function( p_function => 'GET_PD_SD_AMT_IN_PC_PFC',
3260 p_debug_mode => l_debug_mode );
3261
3262 -- Check for business rules violations
3263
3264 IF l_debug_mode = 'Y' THEN
3265 pa_debug.g_err_stage:= 'Validating input parameters';
3266 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,
3267 l_debug_level3);
3268 END IF;
3269
3270 IF (p_resource_assignment_id IS NULL) OR (p_period_profile_id IS NULL) OR (p_txn_currency_code IS NULL)
3271 THEN
3272 IF l_debug_mode = 'Y' THEN
3273 pa_debug.g_err_stage:= 'p_resource_assignment_id = '|| p_resource_assignment_id;
3274 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
3275 pa_debug.g_err_stage:= 'p_period_profile_id = '|| p_period_profile_id;
3276 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
3277 pa_debug.g_err_stage:= 'p_txn_currency_code = '|| p_txn_currency_code;
3278 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
3279 END IF;
3280 PA_UTILS.ADD_MESSAGE
3281 (p_app_short_name => 'PA',
3282 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3283 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3284
3285 END IF;
3286
3287 IF P_PA_DEBUG_MODE = 'Y' THEN
3288 pa_debug.write('GET_GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,'getting pd/sd pc/pfc amounts',3);
3289 END IF;
3290
3291 BEGIN
3292 SELECT sum(nvl(raw_cost,0)),
3293 sum(nvl(burdened_cost,0)),
3294 sum(nvl(revenue,0)),
3295 sum(nvl(project_raw_cost,0)),
3296 sum(nvl(project_burdened_cost,0)),
3297 sum(nvl(project_revenue,0))
3298 INTO x_pd_pfc_raw_cost,
3299 x_pd_pfc_burdened_cost,
3300 x_pd_pfc_revenue,
3301 x_pd_pc_raw_cost,
3302 x_pd_pc_burdened_cost,
3303 x_pd_pc_revenue
3304 FROM pa_budget_lines
3305 WHERE resource_assignment_id = p_resource_assignment_id
3306 AND txn_currency_code = p_txn_currency_code
3307 AND bucketing_period_code in
3308 (PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_PE);
3309 EXCEPTION
3310 WHEN NO_DATA_FOUND THEN
3311 x_pd_pfc_raw_cost := null ;
3312 x_pd_pfc_burdened_cost := null ;
3313 x_pd_pfc_revenue := null ;
3314 x_pd_pc_raw_cost := null ;
3315 x_pd_pc_burdened_cost := null ;
3316 x_pd_pc_revenue := null ;
3317 END ;
3318
3319 BEGIN
3320 SELECT sum(nvl(raw_cost,0)),
3321 sum(nvl(burdened_cost,0)),
3322 sum(nvl(revenue,0)),
3323 sum(nvl(project_raw_cost,0)),
3324 sum(nvl(project_burdened_cost,0)),
3325 sum(nvl(project_revenue,0))
3326 INTO x_sd_pfc_raw_cost,
3327 x_sd_pfc_burdened_cost,
3328 x_sd_pfc_revenue,
3329 x_sd_pc_raw_cost,
3330 x_sd_pc_burdened_cost,
3331 x_sd_pc_revenue
3332 FROM pa_budget_lines
3333 WHERE resource_assignment_id = p_resource_assignment_id
3334 AND txn_currency_code = p_txn_currency_code
3335 AND bucketing_period_code in
3336 (PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SD,PA_FP_CONSTANTS_PKG.G_BUCKETING_PERIOD_CODE_SE);
3337 EXCEPTION
3338 WHEN NO_DATA_FOUND THEN
3339 x_sd_pfc_raw_cost := null ;
3340 x_sd_pfc_burdened_cost := null ;
3341 x_sd_pfc_revenue := null ;
3342 x_sd_pc_raw_cost := null ;
3343 x_sd_pc_burdened_cost := null ;
3344 x_sd_pc_revenue := null ;
3345 END ;
3346
3347 IF l_debug_mode = 'Y' THEN
3348 pa_debug.g_err_stage:= 'Exiting GET_PD_SD_AMT_IN_PC_PFC';
3349 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,
3350 l_debug_level3);
3351 END IF;
3352 pa_debug.reset_curr_function;
3353
3354 EXCEPTION
3355
3356 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3357
3358 IF nvl(x_return_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS THEN
3359 x_return_status := FND_API.G_RET_STS_ERROR;
3360 END IF;
3361
3362 l_msg_count := FND_MSG_PUB.count_msg;
3363
3364 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3365 PA_INTERFACE_UTILS_PUB.get_messages
3366 (p_encoded => FND_API.G_TRUE
3367 ,p_msg_index => 1
3368 ,p_msg_count => l_msg_count
3369 ,p_msg_data => l_msg_data
3370 ,p_data => l_data
3371 ,p_msg_index_out => l_msg_index_out);
3372 x_msg_data := l_data;
3373 x_msg_count := l_msg_count;
3374 ELSE
3375 x_msg_count := l_msg_count;
3376 END IF;
3377 pa_debug.reset_curr_function;
3378 RETURN;
3379
3380 WHEN others THEN
3381
3382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3383 x_msg_count := 1;
3384 x_msg_data := SQLERRM;
3385
3386 FND_MSG_PUB.add_exc_msg
3387 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG'
3388 ,p_procedure_name => 'GET_PD_SD_AMT_IN_PC_PFC'
3389 ,p_error_text => x_msg_data);
3390
3391 IF l_debug_mode = 'Y' THEN
3392 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3393 pa_debug.write('GET_PD_SD_AMT_IN_PC_PFC: ' || l_module_name,pa_debug.g_err_stage,
3394 l_debug_level5);
3395 END IF;
3396 pa_debug.reset_curr_function;
3397 RAISE;
3398 END GET_PD_SD_AMT_IN_PC_PFC;
3399
3400 PROCEDURE GET_PRECEDING_SUCCEEDING_AMT(
3401 p_budget_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
3402 ,p_resource_assignment_id IN pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE
3403 ,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
3404 ,p_period_profile_id IN pa_budget_versions.period_profile_id%TYPE
3405 ,x_preceding_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3406 ,x_succeeding_raw_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3407 ,x_preceding_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3408 ,x_succeeding_burdened_cost OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3409 ,x_preceding_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3410 ,x_succeeding_revenue OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3411 ,x_preceding_quantity OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3412 ,x_succeeding_quantity OUT NOCOPY pa_proj_periods_denorm.preceding_periods_amount%TYPE --File.Sql.39 bug 4440895
3413 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3414 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3415 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3416
3417
3418 IS
3419
3420 l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3421
3422 CURSOR period_denorm_cur IS
3423 SELECT amount_type_code
3424 ,amount_subtype_code
3425 ,preceding_periods_amount
3426 ,succeeding_periods_amount
3427 FROM pa_proj_periods_denorm
3428 WHERE budget_version_id = p_budget_version_id
3429 AND resource_assignment_id = p_resource_assignment_id
3430 AND object_type_code = PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT
3431 AND object_id = p_resource_assignment_id
3432 AND currency_code = p_txn_currency_code
3433 AND period_profile_id = p_period_profile_id
3434 AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION ;
3435
3436 l_period_denorm_cur_rec period_denorm_cur%ROWTYPE ;
3437
3438 BEGIN
3439 -- Set the error stack.
3440
3441 pa_debug.set_err_stack('PA_FP_EDIT_LINE_PKG.GET_PRECEDING_SUCCEEDING_AMT');
3442
3443 -- Initialize the return status to success
3444 x_return_status := FND_API.G_RET_STS_SUCCESS;
3445
3446 pa_debug.g_err_stage := 'In PA_FP_EDIT_LINE_PKG.GET_PRECEDING_SUCCEEDING_AMT ';
3447 IF P_PA_DEBUG_MODE = 'Y' THEN
3448 pa_debug.write('GET_PRECEDING_SUCCEEDING_AMT: ' || l_module_name,pa_debug.g_err_stage,3);
3449 END IF;
3450
3451 SELECT fin_plan_preference_code
3452 INTO l_fin_plan_preference_code
3453 FROM pa_proj_fp_options pfo
3454 ,pa_resource_assignments pra
3455 WHERE pra.resource_assignment_id = p_resource_assignment_id
3456 AND pra.budget_version_id = pfo.fin_plan_version_id;
3457
3458 IF P_PA_DEBUG_MODE = 'Y' THEN
3459 pa_debug.write('GET_PRECEDING_SUCCEEDING_AMT: ' || l_module_name,'getting preceding succeeding period amounts',3);
3460 END IF;
3461
3462 FOR l_period_denorm_cur_rec IN period_denorm_cur LOOP
3463 IF (l_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
3464 OR l_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ) THEN
3465 IF ( l_period_denorm_cur_rec.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
3466 AND l_period_denorm_cur_rec.amount_subtype_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_RAW_COST ) THEN
3467 x_preceding_raw_cost := l_period_denorm_cur_rec.preceding_periods_amount ;
3468 x_succeeding_raw_cost := l_period_denorm_cur_rec.succeeding_periods_amount ;
3469 ELSIF ( l_period_denorm_cur_rec.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
3470 AND l_period_denorm_cur_rec.amount_subtype_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_BURD_COST ) THEN
3471 x_preceding_burdened_cost := l_period_denorm_cur_rec.preceding_periods_amount ;
3472 x_succeeding_burdened_cost := l_period_denorm_cur_rec.succeeding_periods_amount ;
3473 END IF;
3474 END IF ;
3475
3476 IF (l_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY
3477 OR l_fin_plan_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ) then
3478 IF ( l_period_denorm_cur_rec.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_REVENUE
3479 AND l_period_denorm_cur_rec.amount_subtype_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_REVENUE ) THEN
3480 x_preceding_revenue := l_period_denorm_cur_rec.preceding_periods_amount ;
3481 x_succeeding_revenue := l_period_denorm_cur_rec.succeeding_periods_amount ;
3482 END IF;
3483 END IF ;
3484
3485 IF ( l_period_denorm_cur_rec.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY
3486 AND l_period_denorm_cur_rec.amount_subtype_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY ) THEN
3487 x_preceding_quantity := l_period_denorm_cur_rec.preceding_periods_amount ;
3488 x_succeeding_quantity := l_period_denorm_cur_rec.succeeding_periods_amount ;
3489 END IF;
3490 END LOOP;
3491
3492 pa_debug.reset_err_stack; /* Bug 2699888 */
3493
3494 EXCEPTION
3495 WHEN OTHERS THEN
3496 IF P_PA_DEBUG_MODE = 'Y' THEN
3497 pa_debug.write('GET_PRECEDING_SUCCEEDING_AMT: ' || l_module_name,'SQLERRM = ' || SQLERRM,5);
3498 END IF;
3499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3500 pa_debug.reset_err_stack; /* Bug 2699888 */
3501 RAISE;
3502 END GET_PRECEDING_SUCCEEDING_AMT;
3503
3504
3505 /* This function returns the value of the package variable for function security changes */
3506
3507 FUNCTION get_is_fn_security_available RETURN VARCHAR2 IS
3508 BEGIN
3509 return PA_FP_EDIT_LINE_PKG.G_IS_FN_SECURITY_AVAILABLE;
3510 END get_is_fn_security_available;
3511
3512 PROCEDURE CALL_CLIENT_EXTENSIONS
3513 ( p_project_id IN pa_projects_all.project_id%TYPE
3514 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
3515 ,p_task_id_tbl IN SYSTEM.pa_num_tbl_type
3516 ,p_res_list_member_id_tbl IN SYSTEM.pa_num_tbl_type
3517 ,p_resource_list_id IN pa_resource_lists.RESOURCE_LIST_ID%TYPE
3518 ,p_resource_id_tbl IN SYSTEM.pa_num_tbl_type
3519 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
3520 ,p_product_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
3521 ,p_start_date_tbl IN SYSTEM.pa_date_tbl_type
3522 ,p_end_date_tbl IN SYSTEM.pa_date_tbl_type
3523 ,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type
3524 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
3525 ,px_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3526 ,px_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3527 ,px_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3528 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3529 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3530 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3531 )
3532 IS
3533 l_err_code NUMBER := 0;
3534 l_err_message VARCHAR2(100);
3535 l_debug_mode VARCHAR2(30);
3536 l_stage NUMBER := 100 ;
3537 l_err_stage VARCHAR2(120);
3538 l_msg_count NUMBER :=0 ;
3539 l_data VARCHAR2(160) ;
3540 l_msg_data VARCHAR2(160) ;
3541 l_msg_index_out NUMBER;
3542
3543 l_expected_error BOOLEAN ;
3544 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3545
3546 BEGIN
3547 x_msg_count := 0;
3548 x_return_status := FND_API.G_RET_STS_SUCCESS;
3549 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'Y');
3550
3551 IF l_debug_mode = 'Y' THEN
3552 pa_debug.set_curr_function( p_function => 'CALL_CLIENT_EXTENSIONS'
3553 ,p_debug_mode => l_debug_mode );
3554 END IF;
3555
3556 IF l_debug_mode = 'Y' THEN
3557 pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS ';
3558 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3559 END IF;
3560
3561 IF (p_project_id IS NULL) OR (p_budget_version_id IS NULL) THEN
3562 IF l_debug_mode = 'Y' THEN
3563 pa_debug.g_err_stage := TO_CHAR(l_stage)||':Invalid Input Parameters';
3564 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,1);
3565
3566 END IF;
3567 x_return_status := FND_API.G_RET_STS_ERROR;
3568 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3569 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3570 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3571 END IF;
3572
3573
3574 IF l_debug_mode = 'Y' THEN
3575 pa_debug.g_err_stage := TO_CHAR(l_stage)||':Calling Client Extensions';
3576 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3577 END IF;
3578
3579 SAVEPOINT call_client_extns;
3580
3581 IF l_debug_mode = 'Y' THEN
3582 pa_debug.g_err_stage := 'getting preference code';
3583 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3584 END IF;
3585
3586 SELECT fin_plan_preference_code
3587 INTO l_fp_preference_code
3588 FROM pa_proj_fp_options
3589 WHERE fin_plan_version_id = p_budget_version_id;
3590
3591 IF l_debug_mode = 'Y' THEN
3592 pa_debug.g_err_stage := 'after getting preference code l_fp_preference_code = ' || l_fp_preference_code;
3593 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3594 END IF;
3595
3596 IF nvl(p_start_date_tbl.last,0) >= 1 THEN
3597
3598 IF l_debug_mode = 'Y' THEN
3599 pa_debug.g_err_stage := TO_CHAR(l_stage)||':inside IF block and before for loop';
3600 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3601 END IF;
3602
3603 IF nvl(px_raw_cost_tbl.last,0) = 0 THEN
3604 px_raw_cost_tbl := SYSTEM.pa_num_tbl_type();
3605 END IF;
3606
3607 IF nvl(px_burdened_cost_tbl.last,0) = 0 THEN
3608 px_burdened_cost_tbl := SYSTEM.pa_num_tbl_type();
3609 END IF;
3610
3611 IF nvl(px_revenue_tbl.last,0) = 0 THEN
3612 px_revenue_tbl := SYSTEM.pa_num_tbl_type();
3613 END IF;
3614
3615 FOR i IN nvl(px_raw_cost_tbl.last,1)..p_start_date_tbl.last LOOP
3616 px_raw_cost_tbl.extend(1);
3617 END LOOP;
3618
3619 FOR i IN nvl(px_burdened_cost_tbl.last,1)..p_start_date_tbl.last LOOP
3620 px_burdened_cost_tbl.extend(1);
3621 END LOOP;
3622
3623 FOR i IN nvl(px_revenue_tbl.last,1)..p_start_date_tbl.last LOOP
3624 px_revenue_tbl.extend(1);
3625 END LOOP;
3626
3627 FOR i IN 1..p_start_date_tbl.last LOOP
3628
3629 IF l_debug_mode = 'Y' THEN
3630 pa_debug.g_err_stage := TO_CHAR(l_stage)||':inside FOR loop and before calling cal_raw_cost';
3631 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3632 END IF;
3633
3634 IF l_fp_preference_code IN (PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) THEN
3635
3636 PA_CLIENT_EXTN_BUDGET.CALC_RAW_COST
3637 (x_budget_version_id => p_budget_version_id,
3638 x_project_id => p_project_id,
3639 x_task_id => p_task_id_tbl(i),
3640 x_resource_list_member_id => p_res_list_member_id_tbl(i),
3641 x_resource_list_id => p_resource_list_id,
3642 x_resource_id => p_resource_id_tbl(i),
3643 x_start_date => p_start_date_tbl(i),
3644 x_end_date => p_end_date_tbl(i),
3645 x_period_name => p_period_name_tbl(i),
3646 x_quantity => p_quantity_tbl(i),
3647 x_raw_cost => px_raw_cost_tbl(i), -- OUT
3648 x_pm_product_code => p_product_code_tbl(i),
3649 x_error_code => l_err_code,
3650 x_error_message => l_err_message,
3651 x_txn_currency_code => p_txn_currency_code_tbl(i)
3652 );
3653
3654 IF l_err_code > 0 THEN
3655
3656 x_return_status := FND_API.G_RET_STS_ERROR;
3657 IF l_debug_mode = 'Y' THEN
3658 pa_debug.G_Err_Stage := 'l_err_code = ' || l_err_code;
3659 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3660 pa_debug.G_Err_Stage := 'l_err_message = ' || l_err_message;
3661 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3662 END IF;
3663
3664 pa_utils.add_message
3665 ( p_app_short_name => 'PA',
3666 p_msg_name => 'PA_BU_CALC_RAW_EXTN_ERR',
3667 p_token1 => 'ERRNO',
3668 p_value1 => to_char(l_err_code),
3669 p_token2 => 'ERRMSG',
3670 p_value2 => l_err_message);
3671
3672 l_expected_error := TRUE;
3673
3674 ELSIF l_err_code < 0 THEN
3675
3676 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3677
3678 FND_MSG_PUB.add_exc_msg
3679 ( p_pkg_name => 'PA_CLIENT_EXTN_BUDGET'
3680 , p_procedure_name => 'CALC_RAW_COST'
3681 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
3682
3683 END IF;
3684
3685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3687 END IF;
3688
3689 PA_CLIENT_EXTN_BUDGET.CALC_BURDENED_COST
3690 (x_budget_version_id => p_budget_version_id,
3691 x_project_id => p_project_id,
3692 x_task_id => p_task_id_tbl(i),
3693 x_resource_list_member_id => p_res_list_member_id_tbl(i),
3694 x_resource_list_id => p_resource_list_id,
3695 x_resource_id => p_resource_id_tbl(i),
3696 x_start_date => p_start_date_tbl(i),
3697 x_end_date => p_end_date_tbl(i),
3698 x_period_name => p_period_name_tbl(i),
3699 x_quantity => p_quantity_tbl(i),
3700 x_raw_cost => px_raw_cost_tbl(i),
3701 x_burdened_cost => px_burdened_cost_tbl(i), -- OUT
3702 x_pm_product_code => p_product_code_tbl(i),
3703 x_error_code => l_err_code,
3704 x_error_message => l_err_message,
3705 x_txn_currency_code => p_txn_currency_code_tbl(i)
3706 );
3707
3708
3709 IF l_err_code > 0 THEN
3710
3711 x_return_status := FND_API.G_RET_STS_ERROR;
3712
3713 IF l_debug_mode = 'Y' THEN
3714 pa_debug.G_Err_Stage := 'l_err_code = ' || l_err_code;
3715 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3716 END IF;
3717
3718 IF l_debug_mode = 'Y' THEN
3719 pa_debug.G_Err_Stage := 'l_err_message = ' || l_err_message;
3720 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3721 END IF;
3722
3723 pa_utils.add_message
3724 ( p_app_short_name => 'PA',
3725 p_msg_name => 'PA_BU_CALC_BURDENED_EXTN_ERR',
3726 p_token1 => 'ERRNO',
3727 p_value1 => to_char(l_err_code),
3728 p_token2 => 'ERRMSG',
3729 p_value2 => l_err_message);
3730
3731 l_expected_error := TRUE;
3732
3733 ELSIF l_err_code < 0 THEN
3734
3735 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3736
3737 FND_MSG_PUB.add_exc_msg
3738 ( p_pkg_name => 'PA_CLIENT_EXTN_BUDGET'
3739 , p_procedure_name => 'CALC_BURDENED_COST'
3740 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
3741
3742 END IF;
3743
3744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3746
3747 END IF;
3748 END IF; -- preference code is cost only or cost and rev same
3749
3750 IF l_fp_preference_code IN (PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME) THEN
3751
3752 PA_CLIENT_EXTN_BUDGET.CALC_REVENUE
3753 (x_budget_version_id => p_budget_version_id,
3754 x_project_id => p_project_id,
3755 x_task_id => p_task_id_tbl(i),
3756 x_resource_list_member_id => p_res_list_member_id_tbl(i),
3757 x_resource_list_id => p_resource_list_id,
3758 x_resource_id => p_resource_id_tbl(i),
3759 x_start_date => p_start_date_tbl(i),
3760 x_end_date => p_end_date_tbl(i),
3761 x_period_name => p_period_name_tbl(i),
3762 x_quantity => p_quantity_tbl(i),
3763 x_revenue => px_revenue_tbl(i), -- OUT
3764 x_pm_product_code => p_product_code_tbl(i),
3765 x_error_code => l_err_code,
3766 x_error_message => l_err_message,
3767 x_txn_currency_code => p_txn_currency_code_tbl(i)
3768 );
3769
3770 IF l_err_code > 0 THEN
3771 x_return_status := FND_API.G_RET_STS_ERROR;
3772 IF l_debug_mode = 'Y' THEN
3773 pa_debug.G_Err_Stage := 'l_err_code = ' || l_err_code;
3774 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3775 pa_debug.G_Err_Stage := 'l_err_message = ' || l_err_message;
3776 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.G_Err_Stage,5);
3777 END IF;
3778 pa_utils.add_message
3779 ( p_app_short_name => 'PA',
3780 p_msg_name => 'PA_BU_CALC_REV_EXTN_ERR',
3781 p_token1 => 'ERRNO',
3782 p_value1 => to_char(l_err_code),
3783 p_token2 => 'ERRMSG',
3784 p_value2 => l_err_message);
3785
3786 l_expected_error := TRUE;
3787
3788 ELSIF l_err_code < 0 THEN
3789 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3790
3791 FND_MSG_PUB.add_exc_msg
3792 ( p_pkg_name => 'PA_CLIENT_EXTN_BUDGET'
3793 , p_procedure_name => 'CALC_REVENUE'
3794 , p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
3795
3796 END IF;
3797
3798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3800
3801 END IF;
3802 END IF;
3803
3804 END LOOP ;
3805
3806 IF l_debug_mode = 'Y' THEN
3807 pa_debug.g_err_stage := TO_CHAR(l_stage)||'after loop for start date';
3808 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3809 END IF;
3810
3811 END IF; -- if l_start_date_tbl
3812
3813
3814 IF l_expected_error THEN
3815
3816 IF l_debug_mode = 'Y' THEN
3817 pa_debug.g_err_stage := 'inside expected error occured';
3818 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,5);
3819 END IF;
3820 RAISE FND_API.G_EXC_ERROR;
3821 END IF;
3822
3823 IF l_debug_mode = 'Y' THEN
3824 pa_debug.g_err_stage := TO_CHAR(l_stage)||'leaving client extension ';
3825 pa_debug.write('CALL_CLIENT_EXTENSIONS: ' || l_module_name,pa_debug.g_err_stage,3);
3826 pa_debug.reset_curr_function;
3827 END IF;
3828
3829 EXCEPTION
3830 WHEN FND_API.G_EXC_ERROR OR PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3831
3832 ROLLBACK to call_client_extns;
3833 x_return_status := FND_API.G_RET_STS_ERROR;
3834 l_msg_count := FND_MSG_PUB.count_msg;
3835 IF l_debug_mode = 'Y' THEN
3836 pa_debug.g_err_stage := 'inside invalid arg exception of process_xface_lines';
3837 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
3838 END IF;
3839 IF l_msg_count = 1 THEN
3840 PA_INTERFACE_UTILS_PUB.get_messages
3841 (p_encoded => FND_API.G_TRUE,
3842 p_msg_index => 1,
3843 p_msg_count => l_msg_count,
3844 p_msg_data => l_msg_data,
3845 p_data => l_data,
3846 p_msg_index_out => l_msg_index_out);
3847 x_msg_data := l_data;
3848 x_msg_count := l_msg_count;
3849 ELSE
3850 x_msg_count := l_msg_count;
3851 END IF;
3852 IF l_debug_mode = 'Y' THEN
3853 pa_debug.reset_curr_function;
3854 END IF ;
3855 RETURN;
3856 WHEN OTHERS THEN
3857
3858 ROLLBACK to call_client_extns;
3859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3860 x_msg_count := 1;
3861 x_msg_data := SQLERRM;
3862 FND_MSG_PUB.add_exc_msg
3863 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG'
3864 ,p_procedure_name => 'CALL_CLIENT_EXTENSION'
3865 ,p_error_text => sqlerrm);
3866
3867 IF l_debug_mode = 'Y' THEN
3868 pa_debug.g_err_stage := 'inside others exception of call_client_extension';
3869 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
3870 pa_debug.G_Err_Stack := SQLERRM;
3871 pa_debug.reset_curr_function;
3872 END IF;
3873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3874 END CALL_CLIENT_EXTENSIONS ;
3875
3876 -- This procedure finds out the all the records in pa_fp_rollup_tmp with same
3877 -- budget start date, txn currency code and resource assignment. This
3878 -- api will be called from process_modified_lines whenever dup_val_on_index
3879 -- exception is raised.
3880
3881 PROCEDURE Find_dup_rows_in_rollup_tmp
3882 ( x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3883 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3884 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3885 IS
3886
3887 -- Cursor to detect the duplicate rows
3888 CURSOR l_duplicate_rows_csr
3889 IS
3890 SELECT resource_assignment_id,start_date,txn_currency_code
3891 FROM pa_fp_rollup_tmp
3892 GROUP BY resource_assignment_id,txn_currency_code,start_date
3893 HAVING COUNT(*)>1;
3894
3895 -- Cursor to get the resource assignment details
3896 CURSOR l_res_assignment_details_csr
3897 (c_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE)
3898 IS
3899 SELECT pra.project_id
3900 ,pra.task_id
3901 ,pra.resource_list_member_id
3902 ,pbv.budget_type_code
3903 ,pbv.fin_plan_type_id
3904 FROM pa_resource_assignments pra
3905 ,pa_budget_versions pbv
3906 WHERE pra.resource_assignment_id = c_resource_assignment_id
3907 AND pra.budget_version_id = pbv.budget_version_id ;
3908
3909 l_res_assignment_details_rec l_res_assignment_details_csr%ROWTYPE;
3910
3911 l_resource_alias pa_resource_list_members.alias%TYPE;
3912 l_segment1 pa_projects_all.segment1%TYPE;
3913 l_task_number pa_tasks.task_number%TYPE;
3914 l_debug_level3 CONSTANT NUMBER := 3;
3915 l_debug_level5 CONSTANT NUMBER := 5;
3916 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_EDIT_LINE_PKG Find_dup_rows_in_rollup_tmp ';
3917 l_debug_mode VARCHAR2(1);
3918 l_context_info pa_fin_plan_types_vl.name%TYPE;
3919 l_count NUMBER;
3920
3921 BEGIN
3922 x_msg_count := 0;
3923 x_return_status := FND_API.G_RET_STS_SUCCESS;
3924 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3925
3926 pa_debug.set_curr_function( p_function => 'Find_dup_rows_in_rollup_tmp',
3927 p_debug_mode => l_debug_mode );
3928 IF l_debug_mode = 'Y' THEN
3929 pa_debug.g_err_stage:= 'About to enter the for loop';
3930 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3931 END IF;
3932
3933 FOR l_duplicate_rows_rec IN l_duplicate_rows_csr LOOP
3934
3935 --Get the project number, task number and reasource alias
3936 --so that they can be passed as parameters
3937 OPEN l_res_assignment_details_csr (l_duplicate_rows_rec.resource_assignment_id);
3938 FETCH l_res_assignment_details_csr INTO l_res_assignment_details_rec;
3939 IF (l_res_assignment_details_csr%NOTFOUND) THEN
3940 IF l_debug_mode = 'Y' THEN
3941 pa_debug.g_err_stage:= 'The Cursor l_res_assignment_details_csr did not return rows';
3942 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
3943 END IF;
3944 CLOSE l_res_assignment_details_csr;
3945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3946 END IF;
3947 CLOSE l_res_assignment_details_csr;
3948 /* bug 3326976 Included substrb */
3949 l_task_number := substrb((pa_interface_utils_pub.get_task_number_amg
3950 ( p_task_number=> ''
3951 ,p_task_reference => null
3952 ,p_task_id => l_res_assignment_details_rec.task_id)),
3953 1,
3954 25);
3955
3956 SELECT alias
3957 INTO l_resource_alias
3958 FROM pa_resource_list_members
3959 WHERE resource_list_member_id = l_res_assignment_details_rec.resource_list_member_id;
3960
3961 IF l_debug_mode = 'Y' THEN
3962 pa_debug.g_err_stage:= 'Got the resource alias';
3963 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3964 END IF;
3965
3966
3967 IF l_segment1 IS NULL THEN
3968
3969 SELECT segment1
3970 INTO l_segment1
3971 FROM pa_projects_all
3972 WHERE project_id=l_res_assignment_details_rec.project_id;
3973
3974 END IF;
3975
3976 IF l_debug_mode = 'Y' THEN
3977 pa_debug.g_err_stage:= 'Got the Project Number';
3978 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3979 END IF;
3980
3981
3982 l_context_info := l_res_assignment_details_rec.budget_type_code;
3983
3984 IF l_res_assignment_details_rec.fin_plan_type_id IS NOT NULL THEN
3985
3986 SELECT name
3987 INTO l_context_info
3988 FROM pa_fin_plan_types_vl
3989 WHERE fin_plan_type_id = l_res_assignment_details_rec.fin_plan_type_id;
3990
3991 END IF;
3992
3993 IF l_debug_mode = 'Y' THEN
3994 pa_debug.g_err_stage:= 'About to add error message to stact';
3995 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3996 END IF;
3997
3998
3999 PA_UTILS.add_message
4000 (p_app_short_name => 'PA',
4001 p_msg_name => 'PA_BUD_LINE_ALREADY_EXISTS_AMG',
4002 p_token1 => 'PROJECT',
4003 p_value1 => l_segment1,
4004 p_token2 => 'TASK',
4005 p_value2 => l_task_number,
4006 p_token3 => 'BUDGET_TYPE',
4007 p_value3 => l_context_info ,
4008 p_token4 => 'SOURCE_NAME',
4009 p_value4 => l_resource_alias,
4010 p_token5 => 'START_DATE',
4011 p_value5 => to_char(l_duplicate_rows_rec.start_date));
4012
4013
4014 END LOOP;
4015 IF l_debug_mode = 'Y' THEN
4016 pa_debug.g_err_stage:= 'Exiting Find_dup_rows_in_rollup_tmp';
4017 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
4018 END IF;
4019 pa_debug.reset_curr_function;
4020
4021 EXCEPTION
4022 WHEN OTHERS THEN
4023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4024 x_msg_count := 1;
4025 x_msg_data := SQLERRM;
4026
4027 FND_MSG_PUB.add_exc_msg
4028 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG'
4029 ,p_procedure_name => 'Find_dup_rows_in_rollup_tmp'
4030 ,p_error_text => x_msg_data);
4031
4032 IF l_debug_mode = 'Y' THEN
4033 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4034 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
4035 END IF;
4036 pa_debug.reset_curr_function;
4037 RAISE;
4038
4039 END Find_dup_rows_in_rollup_tmp;
4040
4041 /*=================================================================================================
4042 This api will do the final processing of budget lines data for a budget version. The processing
4043 includes computing the MC amounts, creating MRC lines if required and rolling budget lines data
4044 in pa_resource_assignments and pa_proj_periods_denorm.
4045 This API does not do maintenance of plan amount exist flag on pa_fp_elements because as of now
4046 this process does not create budget lines. The maintenance of the flag should be done from the
4047 place where budget lines are created always.
4048 =================================================================================================*/
4049
4050 PROCEDURE PROCESS_BDGTLINES_FOR_VERSION
4051 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
4052 ,p_calling_context IN VARCHAR2
4053 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4054 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4055 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4056 AS
4057
4058 l_msg_count NUMBER := 0;
4059 l_data VARCHAR2(2000);
4060 l_msg_data VARCHAR2(2000);
4061 l_msg_index_out NUMBER;
4062 l_debug_mode VARCHAR2(1);
4063
4064 l_debug_level2 CONSTANT NUMBER := 2;
4065 l_debug_level3 CONSTANT NUMBER := 3;
4066 l_debug_level4 CONSTANT NUMBER := 4;
4067 l_debug_level5 CONSTANT NUMBER := 5;
4068
4069 BEGIN
4070
4071 x_msg_count := 0;
4072 x_return_status := FND_API.G_RET_STS_SUCCESS;
4073 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4074
4075 pa_debug.set_curr_function( p_function => 'PROCESS_BDGTLINES_FOR_VERSION',
4076 p_debug_mode => l_debug_mode );
4077
4078 -- Check if the requierd parameters are being passed.
4079
4080 IF l_debug_mode = 'Y' THEN
4081 pa_debug.g_err_stage:= 'Validating input parameters';
4082 pa_debug.write(l_module_name,pa_debug.g_err_stage,
4083 l_debug_level3);
4084 END IF;
4085
4086 IF (p_budget_version_id IS NULL)
4087 THEN
4088 IF l_debug_mode = 'Y' THEN
4089 pa_debug.g_err_stage:= 'Invalid parameter (p_budget_version_id)';
4090 pa_debug.write(l_module_name,pa_debug.g_err_stage,
4091 l_debug_level5);
4092 END IF;
4093 PA_UTILS.ADD_MESSAGE
4094 (p_app_short_name => 'PA',
4095 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4096 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4097 END IF;
4098
4099 /* Call PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY for the MC conversions. */
4100
4101 PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY
4102 ( p_budget_version_id => p_budget_version_id
4103 ,p_entire_version => 'Y'
4104 ,x_return_status => x_return_status
4105 ,x_msg_count => x_msg_count
4106 ,x_msg_data => x_msg_data);
4107
4108 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4109 IF P_PA_DEBUG_MODE = 'Y' THEN
4110 pa_debug.g_err_stage := 'Call to PA_FP_MULTI_CURRENCY_PKG.CONVERT_TXN_CURRENCY errored... ';
4111 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
4112 END IF;
4113 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4114 END IF;
4115
4116 /* Increasing the Record Version Number, similar to the one being done in the API
4117 Process_Modified_Lines. The parameter p_calling_context is not being used as of now.
4118 But in future if there are multiple calling contexts, then it is required that the
4119 calling context needs to be checked for before updating the Budget Version. */
4120
4121 IF P_PA_DEBUG_MODE = 'Y' THEN
4122 pa_debug.g_err_stage := 'Increasing record version no. for Budget Version.';
4123 pa_debug.write('PROCESS_BDGTLINES_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
4124 END IF;
4125
4126 UPDATE pa_budget_versions
4127 SET record_version_number = nvl(record_version_number,0) + 1
4128 ,last_update_date = SYSDATE
4129 ,last_updated_by = FND_GLOBAL.user_id
4130 ,last_update_login = FND_GLOBAL.login_id
4131 WHERE budget_version_id = p_budget_version_id;
4132
4133 -- Bug Fix: 4569365. Removed MRC code.
4134 /* Check if MRC is enabled and Call MRC API */
4135 /*
4136 IF P_PA_DEBUG_MODE = 'Y' THEN
4137 pa_debug.g_err_stage := 'Calling MRC API ';
4138 pa_debug.write('PROCESS_BDGTLINES_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,l_debug_level3);
4139 END IF;
4140
4141 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
4142 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
4143 (x_return_status => x_return_status,
4144 x_msg_count => x_msg_count,
4145 x_msg_data => x_msg_data);
4146 END IF;
4147
4148 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4149 IF P_PA_DEBUG_MODE = 'Y' THEN
4150 pa_debug.g_err_stage := 'Unexpected exception in checking MRC Install '||sqlerrm;
4151 pa_debug.write('PROCESS_BDGTLINES_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
4152 END IF;
4153 RAISE g_mrc_exception;
4154 END IF;
4155
4156 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
4157 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
4158
4159 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
4160 (p_fin_plan_version_id => p_budget_version_id,
4161 p_entire_version => 'Y',
4162 x_return_status => x_return_status,
4163 x_msg_count => x_msg_count,
4164 x_msg_data => x_msg_data);
4165 END IF;
4166
4167 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4168 IF P_PA_DEBUG_MODE = 'Y' THEN
4169 pa_debug.g_err_stage := 'Unexpected exception in MRC API '||sqlerrm;
4170 pa_debug.write('PROCESS_BDGTLINES_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
4171 END IF;
4172 RAISE g_mrc_exception;
4173 END IF;
4174 */
4175
4176 IF P_PA_DEBUG_MODE = 'Y' THEN
4177 pa_debug.g_err_stage := 'Calling PA_FP_ROLLUP_PKG ';
4178 pa_debug.write('PROCESS_BDGTLINES_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
4179 END IF;
4180
4181 /* Call the rollup API to rollup the amounts. */
4182 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION(
4183 p_budget_version_id => p_budget_version_id
4184 ,p_entire_version => 'Y'
4185 ,x_return_status => x_return_status
4186 ,x_msg_count => x_msg_count
4187 ,x_msg_data => x_msg_data ) ;
4188
4189
4190 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4191 IF P_PA_DEBUG_MODE = 'Y' THEN
4192 pa_debug.g_err_stage := 'Call to PA_FP_ROLLUP_PKG errored... ';
4193 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
4194 END IF;
4195 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4196 END IF;
4197
4198
4199 IF l_debug_mode = 'Y' THEN
4200 pa_debug.g_err_stage:= 'Exiting PROCESS_BDGTLINES_FOR_VERSION';
4201 pa_debug.write(l_module_name,pa_debug.g_err_stage,
4202 l_debug_level3);
4203 END IF;
4204 pa_debug.reset_curr_function;
4205 EXCEPTION
4206
4207 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4208
4209 x_return_status := FND_API.G_RET_STS_ERROR;
4210 l_msg_count := FND_MSG_PUB.count_msg;
4211
4212 IF l_msg_count = 1 and x_msg_data IS NULL THEN
4213 PA_INTERFACE_UTILS_PUB.get_messages
4214 (p_encoded => FND_API.G_TRUE
4215 ,p_msg_index => 1
4216 ,p_msg_count => l_msg_count
4217 ,p_msg_data => l_msg_data
4218 ,p_data => l_data
4219 ,p_msg_index_out => l_msg_index_out);
4220 x_msg_data := l_data;
4221 x_msg_count := l_msg_count;
4222 ELSE
4223 x_msg_count := l_msg_count;
4224 END IF;
4225 pa_debug.reset_curr_function;
4226 RETURN;
4227
4228 WHEN others THEN
4229
4230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4231 x_msg_count := 1;
4232 x_msg_data := SQLERRM;
4233
4234 FND_MSG_PUB.add_exc_msg
4235 ( p_pkg_name => 'PA_FP_EDIT_LINE_PKG'
4236 ,p_procedure_name => 'PROCESS_BDGTLINES_FOR_VERSION'
4237 ,p_error_text => x_msg_data);
4238
4239 IF l_debug_mode = 'Y' THEN
4240 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4241 pa_debug.write(l_module_name,pa_debug.g_err_stage,
4242 l_debug_level5);
4243 END IF;
4244 pa_debug.reset_curr_function;
4245 RAISE;
4246 END PROCESS_BDGTLINES_FOR_VERSION;
4247
4248 END PA_FP_EDIT_LINE_PKG;