[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_FCST_AMT_PVT
Source
1 PACKAGE body PA_FP_GEN_FCST_AMT_PVT as
2 /* $Header: PAFPFGVB.pls 120.6 2007/02/06 09:54:11 dthakker ship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 /*=======================================================================================
6 This procedure will return the total transaction amount for the given planning resource
7 =======================================================================================*/
8 PROCEDURE GET_TOTAL_PLAN_TXN_AMTS
9 (P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
10 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
11 P_BV_ID_ETC_WP IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
12 P_BV_ID_ETC_FP IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
13 P_FP_COLS_REC_ETC_WP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
14 P_FP_COLS_REC_ETC_FP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
15 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
16 P_TASK_ID IN PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE,
17 P_LATEST_PUBLISH_FP_WBS_ID IN NUMBER,
18 P_CALLING_CONTEXT IN VARCHAR2,
19 X_TXN_AMT_REC OUT NOCOPY PA_FP_GEN_FCST_AMT_PUB.TXN_AMT_REC_TYP,
20 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
21 X_MSG_COUNT OUT NOCOPY NUMBER,
22 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
23
24 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.get_total_plan_txn_amts';
25
26 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
27
28 /* Local variables for calling pa_fp_gen_amount_utils.get_values_for_planning_rate api */
29 l_res_format_id PA_RESOURCE_LIST_MEMBERS.RES_FORMAT_ID%TYPE;
30 l_resource_asn_rec PA_FP_GEN_AMOUNT_UTILS.RESOURCE_ASN_REC;
31 l_pa_tasks_rec PA_FP_GEN_AMOUNT_UTILS.PA_TASKS_REC;
32 l_pa_projects_all_rec PA_FP_GEN_AMOUNT_UTILS.PA_PROJECTS_ALL_REC;
33 l_proj_fp_options_rec PA_FP_GEN_AMOUNT_UTILS.PROJ_FP_OPTIONS_REC;
34 /* end */
35 /* Local variables for calling get_planning_rate api */
36 l_task_bill_rate_org_id pa_tasks.non_labor_bill_rate_org_id%TYPE;
37 l_task_sch_discount pa_tasks.non_labor_schedule_discount%TYPE;
38 l_task_sch_date pa_tasks.non_labor_schedule_fixed_date%TYPE;
39 l_task_nl_std_bill_rt_sch_id pa_tasks.non_lab_std_bill_rt_sch_id%TYPE;
40 l_task_emp_bill_rate_sch_id pa_tasks.emp_bill_rate_schedule_id%TYPE;
41 l_task_job_bill_rate_sch_id pa_tasks.job_bill_rate_schedule_id%TYPE;
42 l_task_lab_bill_rate_org_id pa_tasks.labor_bill_rate_org_id%TYPE;
43 l_task_lab_sch_type pa_tasks.labor_sch_type%TYPE;
44 l_task_non_labor_sch_type pa_tasks.non_labor_sch_type%TYPE;
45 l_top_task_id pa_tasks.top_task_id%TYPE;
46 --Bug 4108350: Fixed the type mismatch for l_lab_sch_type.
47 l_lab_sch_type pa_tasks.labor_sch_type%TYPE; --emp_bill_rate_schedule_id%TYPE;
48 l_rate_task_id pa_resource_assignments.task_id%TYPE;
49
50 l_txn_currency_code pa_fp_rollup_tmp.txn_currency_code%TYPE := NULL;
51 l_txn_plan_quantity pa_fp_rollup_tmp.quantity%TYPE := NULL;
52 l_budget_lines_start_date pa_fp_rollup_tmp.start_date%TYPE := NULL;
53 l_budget_line_id pa_fp_rollup_tmp.budget_line_id%TYPE := NULL;
54 l_burden_cost_rate_override pa_fp_rollup_tmp.burden_cost_rate_override%TYPE := NULL;
55 l_rw_cost_rate_override pa_fp_rollup_tmp.rw_cost_rate_override%TYPE := NULL;
56 l_bill_rate_override pa_fp_rollup_tmp.bill_rate_override%TYPE := NULL;
57 l_txn_raw_cost pa_fp_rollup_tmp.txn_raw_cost%TYPE := NULL;
58 l_txn_burdened_cost pa_fp_rollup_tmp.txn_burdened_cost%TYPE := NULL;
59 l_txn_revenue pa_fp_rollup_tmp.txn_revenue%TYPE := NULL;
60
61 l_emp_bill_rate_sch_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
62 l_job_bill_rate_sch_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
63 l_lab_bill_rate_org_id pa_projects_all.labor_bill_rate_org_id%TYPE;
64 l_non_labor_sch_type pa_projects_all.non_labor_sch_type%TYPE;
65
66 l_txn_currency_code_override pa_fp_res_assignments_tmp.txn_currency_code_override%TYPE;
67 l_assignment_id pa_project_assignments.assignment_id%TYPE := NULL;
68 l_cost_rate_multiplier CONSTANT pa_labor_cost_multipliers.multiplier%TYPE := 1;
69 l_bill_rate_multiplier CONSTANT NUMBER := 1;
70 l_cost_sch_type VARCHAR2(30) := 'COST';
71 l_mfc_cost_source CONSTANT NUMBER := 2;
72 l_calculate_mode VARCHAR2(60);
73 l_bill_rate NUMBER;
74 l_cost_rate NUMBER;
75 l_burden_cost_rate NUMBER;
76 l_burden_multiplier NUMBER;
77 l_raw_cost NUMBER;
78 l_burden_cost NUMBER;
79 l_raw_revenue NUMBER;
80 l_bill_markup_percentage NUMBER;
81 l_cost_txn_curr_code VARCHAR2(30);
82 l_rev_txn_curr_code VARCHAR2(30);
83 l_raw_cost_rejection_code VARCHAR2(30);
84 l_burden_cost_rejection_code VARCHAR2(30);
85 l_revenue_rejection_code VARCHAR2(30);
86 l_cost_ind_compiled_set_id NUMBER;
87 /* end */
88
89 /* Local variables pa_fp_multi_currency_pkg.conv_mc_bulk */
90 l_res_asn_id_tab pa_fp_multi_currency_pkg.number_type_tab;
91 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
92 l_end_date_tab pa_fp_multi_currency_pkg.date_type_tab;
93 l_res_list_member_id_tab pa_fp_multi_currency_pkg.number_type_tab; /* Bug 4070849 */
94 l_txn_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
95 l_txn_rw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
96 l_txn_burdend_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
97 l_txn_rev_tab pa_fp_multi_currency_pkg.number_type_tab;
98 l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
99 l_projfunc_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
100 l_projfunc_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
101 l_projfunc_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
102 l_projfunc_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
103 l_projfunc_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
104 l_projfunc_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
105 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
106 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
107 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
108 l_projfunc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
109 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
110 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
111 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
112 l_proj_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
113 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
114 l_proj_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
115 l_proj_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
116 l_proj_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
117 l_proj_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
118 l_proj_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
119 l_proj_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
120 l_user_validate_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
121 /* end */
122
123 l_count NUMBER;
124 l_msg_count NUMBER;
125 l_data VARCHAR2(2000);
126 l_msg_data VARCHAR2(2000);
127 l_msg_index_out NUMBER;
128
129 l_struct_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
130 /* Values for l_struct_sharing_code can be
131 SHARE_FULL
132 SHARE_PARTIAL
133 SPLIT_MAPPING
134 SPLIT_NO_MAPPING */
135
136 l_ins_bill_rate NUMBER;
137 l_ins_cost_rate NUMBER;
138 l_ins_burd_cost_rate NUMBER;
139 l_ins_burd_multiplier NUMBER;
140 /*
141 l_ins_raw_cost NUMBER;
142 l_ins_burden_cost NUMBER;
143 l_ins_raw_revenue NUMBER;
144 */
145 l_ins_bill_markup_perc NUMBER;
146 l_ins_cost_txn_curr_code VARCHAR2(30);
147 l_ins_rev_txn_curr_code VARCHAR2(30);
148 l_ins_raw_cost_rej_code VARCHAR2(30);
149 l_ins_burd_cost_rej_code VARCHAR2(30);
150 l_ins_rev_rej_code VARCHAR2(30);
151 l_ins_cost_ind_com_set_id NUMBER;
152
153 l_ins_pfc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
154 l_ins_pfc_burd_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
155 l_ins_pfc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
156 l_ins_pfc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
157 l_ins_pc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
158 l_ins_pc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
159 l_ins_pc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
160 l_ins_pc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
161
162 l_source_bv_id number;
163 l_txn_src_code PA_RESOURCE_ASSIGNMENTS.TRANSACTION_SOURCE_CODE%TYPE;
164 BEGIN
165 --Setting initial values
166 X_MSG_COUNT := 0;
167 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
168
169 IF p_pa_debug_mode = 'Y' THEN
170 pa_debug.set_curr_function( p_function => 'GET_TOTAL_PLAN_TXN_AMTS'
171 ,p_debug_mode => p_pa_debug_mode);
172 END IF;
173
174 l_struct_sharing_code := NVL(PA_PROJECT_STRUCTURE_UTILS.
175 get_Structure_sharing_code(P_PROJECT_ID=> P_PROJECT_ID),'SHARE_FULL');
176 --dbms_output.put_line('Value for Structure_sharing_code: '|| l_struct_sharing_code);
177 /* hr_utility.trace('etc wp bv id :'||P_BV_ID_ETC_WP);
178 hr_utility.trace('etc fp bv id :'||P_BV_ID_ETC_FP); */
179 IF p_calling_context = 'WORK_PLAN' THEN
180 l_source_bv_id := P_BV_ID_ETC_WP;
181 ELSE
182 l_source_bv_id := P_BV_ID_ETC_FP;
183 END IF;
184
185 -- Bug 5094401: If the source budget_version_id is null,
186 -- then return without doing any further processing since
187 -- no planned amounts are available foro this task.
188 IF l_source_bv_id IS NULL THEN
189 IF p_pa_debug_mode = 'Y' THEN
190 PA_DEBUG.Reset_Curr_Function;
191 END IF;
192 RETURN;
193 END IF;
194
195 IF p_calling_context = 'WORK_PLAN' THEN
196 l_txn_src_code := 'WORKPLAN_RESOURCES';
197 ELSE
198 l_txn_src_code := p_calling_context;
199 END IF;
200
201 /* hr_utility.trace_on(null,'mftest'); */
202 IF ( p_calling_context = 'WORK_PLAN'
203 AND
204 p_fp_cols_rec_etc_wp.x_track_workplan_costs_flag = 'Y'
205 AND
206 l_struct_sharing_code = 'SHARE_FULL')
207 OR
208 p_calling_context = 'FINANCIAL_PLAN' THEN
209 INSERT INTO PA_FP_CALC_AMT_TMP1
210 ( RESOURCE_ASSIGNMENT_ID
211 ,BUDGET_VERSION_ID
212 ,PROJECT_ID
213 ,TASK_ID
214 ,RESOURCE_LIST_MEMBER_ID
215 ,UNIT_OF_MEASURE
216 ,TRACK_AS_LABOR_FLAG
217 ,RESOURCE_ASSIGNMENT_TYPE
218 ,PLANNING_START_DATE
219 ,PLANNING_END_DATE
220 ,RES_TYPE_CODE
221 ,FC_RES_TYPE_CODE
222 ,RESOURCE_CLASS_CODE
223 ,ORGANIZATION_ID
224 ,JOB_ID
225 ,PERSON_ID
226 ,EXPENDITURE_TYPE
227 ,EXPENDITURE_CATEGORY
228 ,REVENUE_CATEGORY_CODE
229 ,EVENT_TYPE
230 ,SUPPLIER_ID
231 ,PROJECT_ROLE_ID
232 ,PERSON_TYPE_CODE
233 ,NON_LABOR_RESOURCE
234 ,BOM_RESOURCE_ID
235 ,INVENTORY_ITEM_ID
236 ,ITEM_CATEGORY_ID
237 ,BILLABLE_PERCENT
238 ,TRANSACTION_SOURCE_CODE
239 ,MFC_COST_TYPE_ID
240 ,PROCURE_RESOURCE_FLAG
241 ,INCURRED_BY_RES_FLAG
242 ,RATE_JOB_ID
243 ,RATE_EXPENDITURE_TYPE
244 ,TA_DISPLAY_FLAG
245 ,RATE_BASED_FLAG
246 ,USE_TASK_SCHEDULE_FLAG
247 ,RATE_EXP_FUNC_CURR_CODE
248 ,RATE_EXPENDITURE_ORG_ID
249 ,INCUR_BY_RES_CLASS_CODE
250 ,INCUR_BY_ROLE_ID
251 ,RESOURCE_CLASS_FLAG
252 ,NAMED_ROLE
253 ,ETC_METHOD_CODE
254 ,MAPPED_FIN_TASK_ID)
255 (SELECT ra.resource_assignment_id,
256 ra.budget_version_id,
257 ra.project_id,
258 ra.task_id,
259 ra.resource_list_member_id,
260 ra.unit_of_measure,
261 ra.track_as_labor_flag,
262 ra.resource_assignment_type,
263 ra.planning_start_date,
264 ra.planning_end_date,
265 ra.res_type_code,
266 ra.fc_res_type_code,
267 ra.resource_class_code,
268 ra.organization_id,
269 ra.job_id,
270 ra.person_id,
271 ra.expenditure_type,
272 ra.expenditure_category,
273 ra.revenue_category_code,
274 ra.event_type,
275 ra.supplier_id,
276 ra.project_role_id,
277 ra.person_type_code,
278 ra.non_labor_resource,
279 ra.bom_resource_id,
280 ra.inventory_item_id,
281 ra.item_category_id,
282 ra.billable_percent,
283 l_txn_src_code,
284 ra.mfc_cost_type_id,
285 ra.procure_resource_flag,
286 ra.incurred_by_res_flag,
287 ra.rate_job_id,
288 ra.rate_expenditure_type,
289 ra.ta_display_flag,
290 ra.rate_based_flag,
291 ra.use_task_schedule_flag,
292 ra.rate_exp_func_curr_code,
293 ra.rate_expenditure_org_id,
294 ra.incur_by_res_class_code,
295 ra.incur_by_role_id,
296 ra.resource_class_flag,
297 ra.named_role,
298 ra.etc_method_code,
299 ra.task_id
300 FROM pa_resource_assignments ra
301 WHERE ra.budget_version_id = l_source_bv_id
302 AND NVL(ra.task_id,0) = p_task_id AND
303 EXISTS (SELECT 1 from pa_budget_lines bl WHERE
304 ra.resource_assignment_id =
305 bl.resource_assignment_id AND
306 rownum < 2));
307 /* hr_utility.trace('no fo recs inserted in tmp1:'||sql%rowcount);
308 hr_utility.trace('p task id :'||p_task_id );
309 hr_utility.trace('budget ver id '||p_budget_version_id) ; */
310
311 --dbms_output.put_line('No. of rows inserted in tmp1 : '|| sql%rowcount);
312
313 INSERT INTO PA_FP_CALC_AMT_TMP2
314 (resource_assignment_id,
315 txn_currency_code,
316 total_plan_quantity,
317 total_txn_raw_cost,
318 total_txn_burdened_cost,
319 total_txn_revenue,
320 total_pc_raw_cost,
321 total_pc_burdened_cost,
322 total_pc_revenue,
323 total_pfc_raw_cost,
324 total_pfc_burdened_cost,
325 total_pfc_revenue,
326 transaction_source_code )
327 (SELECT ra.resource_assignment_id,
328 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
329 'Y',bl.txn_currency_code,
330 'N',p_fp_cols_rec.x_project_currency_code),
331 sum(bl.quantity),
332 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
333 'Y',bl.txn_raw_cost,
334 'N',bl.project_raw_cost)),
335 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
336 'Y',bl.txn_burdened_cost,
337 'N',bl.project_burdened_cost)),
338 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
339 'Y',bl.txn_revenue,
340 'N',bl.project_revenue)),
341 sum(bl.project_raw_cost),
342 sum(bl.project_burdened_cost),
343 sum(bl.project_revenue),
344 sum(bl.raw_cost),
345 sum(bl.burdened_cost),
346 sum(bl.revenue),
347 l_txn_src_code
348 FROM pa_budget_lines bl,
349 pa_resource_assignments ra
350 WHERE ra.resource_assignment_id = bl.resource_assignment_id
351 and ra.budget_version_id = l_source_bv_id
352 and NVL(ra.task_id,0) = p_task_id
353 AND bl.COST_REJECTION_CODE IS NULL
354 AND bl.REVENUE_REJECTION_CODE IS NULL
355 AND bl.BURDEN_REJECTION_CODE IS NULL
356 AND bl.OTHER_REJECTION_CODE IS NULL
357 AND bl.PC_CUR_CONV_REJECTION_CODE IS NULL
358 AND bl.PFC_CUR_CONV_REJECTION_CODE IS NULL
359 GROUP BY ra.resource_assignment_id,l_txn_src_code,
360 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
361 'Y',bl.txn_currency_code,
362 'N',p_fp_cols_rec.x_project_currency_code));
363
364 -- hr_utility.trace('no fo recs inserted in tmp2:'||sql%rowcount);
365
366 --dbms_output.put_line('No. of rows inserted in tmp2 : '|| sql%rowcount);
367
368 ELSIF (p_calling_context = 'WORK_PLAN'
369 AND p_fp_cols_rec_etc_wp.x_track_workplan_costs_flag = 'Y'
370 AND l_struct_sharing_code = 'SPLIT_NO_MAPPING') THEN
371 INSERT INTO PA_FP_CALC_AMT_TMP1
372 ( RESOURCE_ASSIGNMENT_ID
373 ,BUDGET_VERSION_ID
374 ,PROJECT_ID
375 ,TASK_ID
376 ,RESOURCE_LIST_MEMBER_ID
377 ,UNIT_OF_MEASURE
378 ,TRACK_AS_LABOR_FLAG
379 ,RESOURCE_ASSIGNMENT_TYPE
380 ,PLANNING_START_DATE
381 ,PLANNING_END_DATE
382 ,RES_TYPE_CODE
383 ,FC_RES_TYPE_CODE
384 ,RESOURCE_CLASS_CODE
385 ,ORGANIZATION_ID
386 ,JOB_ID
387 ,PERSON_ID
388 ,EXPENDITURE_TYPE
389 ,EXPENDITURE_CATEGORY
390 ,REVENUE_CATEGORY_CODE
391 ,EVENT_TYPE
392 ,SUPPLIER_ID
393 ,PROJECT_ROLE_ID
394 ,PERSON_TYPE_CODE
395 ,NON_LABOR_RESOURCE
396 ,BOM_RESOURCE_ID
397 ,INVENTORY_ITEM_ID
398 ,ITEM_CATEGORY_ID
399 ,BILLABLE_PERCENT
400 ,TRANSACTION_SOURCE_CODE
401 ,MFC_COST_TYPE_ID
402 ,PROCURE_RESOURCE_FLAG
403 ,INCURRED_BY_RES_FLAG
404 ,RATE_JOB_ID
405 ,RATE_EXPENDITURE_TYPE
406 ,TA_DISPLAY_FLAG
407 ,RATE_BASED_FLAG
408 ,USE_TASK_SCHEDULE_FLAG
409 ,RATE_EXP_FUNC_CURR_CODE
410 ,RATE_EXPENDITURE_ORG_ID
411 ,INCUR_BY_RES_CLASS_CODE
412 ,INCUR_BY_ROLE_ID
413 ,RESOURCE_CLASS_FLAG
414 ,NAMED_ROLE
415 ,ETC_METHOD_CODE
416 ,MAPPED_FIN_TASK_ID)
417 (SELECT ra.resource_assignment_id,
418 ra.budget_version_id,
419 ra.project_id,
420 ra.task_id,
421 ra.resource_list_member_id,
422 ra.unit_of_measure,
423 ra.track_as_labor_flag,
424 ra.resource_assignment_type,
425 ra.planning_start_date,
426 ra.planning_end_date,
427 ra.res_type_code,
428 ra.fc_res_type_code,
429 ra.resource_class_code,
430 ra.organization_id,
431 ra.job_id,
432 ra.person_id,
433 ra.expenditure_type,
434 ra.expenditure_category,
435 ra.revenue_category_code,
436 ra.event_type,
437 ra.supplier_id,
438 ra.project_role_id,
439 ra.person_type_code,
440 ra.non_labor_resource,
441 ra.bom_resource_id,
442 ra.inventory_item_id,
443 ra.item_category_id,
444 ra.billable_percent,
445 l_txn_src_code,
446 ra.mfc_cost_type_id,
447 ra.procure_resource_flag,
448 ra.incurred_by_res_flag,
449 ra.rate_job_id,
450 ra.rate_expenditure_type,
451 ra.ta_display_flag,
452 ra.rate_based_flag,
453 ra.use_task_schedule_flag,
454 ra.rate_exp_func_curr_code,
455 ra.rate_expenditure_org_id,
456 ra.incur_by_res_class_code,
457 ra.incur_by_role_id,
458 ra.resource_class_flag,
459 ra.named_role,
460 ra.etc_method_code,
461 0
462 FROM pa_resource_assignments ra
463 WHERE ra.budget_version_id = l_source_bv_id);
464
465 --dbms_output.put_line('No. of rows inserted in tmp1 : '|| sql%rowcount);
466
467 INSERT INTO PA_FP_CALC_AMT_TMP2
468 (resource_assignment_id,
469 txn_currency_code,
470 total_plan_quantity,
471 total_txn_raw_cost,
472 total_txn_burdened_cost,
473 total_txn_revenue,
474 total_pc_raw_cost,
475 total_pc_burdened_cost,
476 total_pc_revenue,
477 total_pfc_raw_cost,
478 total_pfc_burdened_cost,
479 total_pfc_revenue,
480 transaction_source_code )
481 (SELECT ra.resource_assignment_id,
482 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
483 'Y',bl.txn_currency_code,
484 'N',p_fp_cols_rec.x_project_currency_code),
485 sum(bl.quantity),
486 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
487 'Y',bl.txn_raw_cost,
488 'N',bl.project_raw_cost)),
489 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
490 'Y',bl.txn_burdened_cost,
491 'N',bl.project_burdened_cost)),
492 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
493 'Y',bl.txn_revenue,
494 'N',bl.project_revenue)),
495 sum(bl.project_raw_cost),
496 sum(bl.project_burdened_cost),
497 sum(bl.project_revenue),
498 sum(bl.raw_cost),
499 sum(bl.burdened_cost),
500 sum(bl.revenue),
501 l_txn_src_code
502 FROM pa_budget_lines bl,
503 pa_resource_assignments ra
504 WHERE ra.budget_version_id = l_source_bv_id
505 AND ra.resource_assignment_id = bl.resource_assignment_id
506 AND bl.COST_REJECTION_CODE IS NULL
507 AND bl.REVENUE_REJECTION_CODE IS NULL
508 AND bl.BURDEN_REJECTION_CODE IS NULL
509 AND bl.OTHER_REJECTION_CODE IS NULL
510 AND bl.PC_CUR_CONV_REJECTION_CODE IS NULL
511 AND bl.PFC_CUR_CONV_REJECTION_CODE IS NULL
512 GROUP BY ra.resource_assignment_id,l_txn_src_code,
513 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
514 'Y',bl.txn_currency_code,
515 'N',p_fp_cols_rec.x_project_currency_code));
516
517 ELSIF ( p_calling_context = 'WORK_PLAN'
518 AND
519 p_fp_cols_rec_etc_wp.x_track_workplan_costs_flag = 'Y'
520 AND
521 l_struct_sharing_code <> 'SHARE_FULL') THEN
522 INSERT INTO PA_FP_CALC_AMT_TMP1
523 ( RESOURCE_ASSIGNMENT_ID
524 ,BUDGET_VERSION_ID
525 ,PROJECT_ID
526 ,TASK_ID
527 ,RESOURCE_LIST_MEMBER_ID
528 ,UNIT_OF_MEASURE
529 ,TRACK_AS_LABOR_FLAG
530 ,RESOURCE_ASSIGNMENT_TYPE
531 ,PLANNING_START_DATE
532 ,PLANNING_END_DATE
533 ,RES_TYPE_CODE
534 ,FC_RES_TYPE_CODE
535 ,RESOURCE_CLASS_CODE
536 ,ORGANIZATION_ID
537 ,JOB_ID
538 ,PERSON_ID
539 ,EXPENDITURE_TYPE
540 ,EXPENDITURE_CATEGORY
541 ,REVENUE_CATEGORY_CODE
542 ,EVENT_TYPE
543 ,SUPPLIER_ID
544 ,PROJECT_ROLE_ID
545 ,PERSON_TYPE_CODE
546 ,NON_LABOR_RESOURCE
547 ,BOM_RESOURCE_ID
548 ,INVENTORY_ITEM_ID
549 ,ITEM_CATEGORY_ID
550 ,BILLABLE_PERCENT
551 ,TRANSACTION_SOURCE_CODE
552 ,MFC_COST_TYPE_ID
553 ,PROCURE_RESOURCE_FLAG
554 ,INCURRED_BY_RES_FLAG
555 ,RATE_JOB_ID
556 ,RATE_EXPENDITURE_TYPE
557 ,TA_DISPLAY_FLAG
558 ,RATE_BASED_FLAG
559 ,USE_TASK_SCHEDULE_FLAG
560 ,RATE_EXP_FUNC_CURR_CODE
561 ,RATE_EXPENDITURE_ORG_ID
562 ,INCUR_BY_RES_CLASS_CODE
563 ,INCUR_BY_ROLE_ID
564 ,RESOURCE_CLASS_FLAG
565 ,NAMED_ROLE
566 ,ETC_METHOD_CODE
567 ,MAPPED_FIN_TASK_ID)
568 (SELECT ra.resource_assignment_id,
569 ra.budget_version_id,
570 ra.project_id,
571 ra.task_id,
572 ra.resource_list_member_id,
573 ra.unit_of_measure,
574 ra.track_as_labor_flag,
575 ra.resource_assignment_type,
576 ra.planning_start_date,
577 ra.planning_end_date,
578 ra.res_type_code,
579 ra.fc_res_type_code,
580 ra.resource_class_code,
581 ra.organization_id,
582 ra.job_id,
583 ra.person_id,
584 ra.expenditure_type,
585 ra.expenditure_category,
586 ra.revenue_category_code,
587 ra.event_type,
588 ra.supplier_id,
589 ra.project_role_id,
590 ra.person_type_code,
591 ra.non_labor_resource,
592 ra.bom_resource_id,
593 ra.inventory_item_id,
594 ra.item_category_id,
595 ra.billable_percent,
596 l_txn_src_code,
597 ra.mfc_cost_type_id,
598 ra.procure_resource_flag,
599 ra.incurred_by_res_flag,
600 ra.rate_job_id,
601 ra.rate_expenditure_type,
602 ra.ta_display_flag,
603 ra.rate_based_flag,
604 ra.use_task_schedule_flag,
605 ra.rate_exp_func_curr_code,
606 ra.rate_expenditure_org_id,
607 ra.incur_by_res_class_code,
608 ra.incur_by_role_id,
609 ra.resource_class_flag,
610 ra.named_role,
611 ra.etc_method_code,
612 v.mapped_fin_task_id
613 FROM pa_resource_assignments ra,
614 pa_map_wp_to_fin_tasks_v v
615 WHERE
616 ra.budget_version_id = l_source_bv_id
617 AND v.mapped_fin_task_id = p_task_id
618 AND v.parent_structure_version_id =
619 p_fp_cols_rec_etc_wp.x_project_structure_version_id
620 AND v.proj_element_id = ra.task_id);
621
622 --dbms_output.put_line('No. of rows inserted in tmp1 : '|| sql%rowcount);
623
624 INSERT INTO PA_FP_CALC_AMT_TMP2
625 (resource_assignment_id,
626 txn_currency_code,
627 total_plan_quantity,
628 total_txn_raw_cost,
629 total_txn_burdened_cost,
630 total_txn_revenue,
631 total_pc_raw_cost,
632 total_pc_burdened_cost,
633 total_pc_revenue,
634 total_pfc_raw_cost,
635 total_pfc_burdened_cost,
636 total_pfc_revenue,
637 transaction_source_code )
638 (SELECT ra.resource_assignment_id,
639 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
640 'Y',bl.txn_currency_code,
641 'N',p_fp_cols_rec.x_project_currency_code),
642 sum(bl.quantity),
643 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
644 'Y',bl.txn_raw_cost,
645 'N',bl.project_raw_cost)),
646 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
647 'Y',bl.txn_burdened_cost,
648 'N',bl.project_burdened_cost)),
649 sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
650 'Y',bl.txn_revenue,
651 'N',bl.project_revenue)),
652 sum(bl.project_raw_cost),
653 sum(bl.project_burdened_cost),
654 sum(bl.project_revenue),
655 sum(bl.raw_cost),
656 sum(bl.burdened_cost),
657 sum(bl.revenue),
658 l_txn_src_code
659 FROM pa_budget_lines bl,
660 pa_resource_assignments ra,
661 pa_map_wp_to_fin_tasks_v v
662 WHERE ra.resource_assignment_id = bl.resource_assignment_id
663 and ra.budget_version_id = l_source_bv_id
664 and v.parent_structure_version_id = p_fp_cols_rec_etc_wp.x_project_structure_version_id
665 and v.mapped_fin_task_id = p_task_id
666 and NVL(ra.task_id,0) = v.proj_element_id
667 AND bl.COST_REJECTION_CODE IS NULL
668 AND bl.REVENUE_REJECTION_CODE IS NULL
669 AND bl.BURDEN_REJECTION_CODE IS NULL
670 AND bl.OTHER_REJECTION_CODE IS NULL
671 AND bl.PC_CUR_CONV_REJECTION_CODE IS NULL
672 AND bl.PFC_CUR_CONV_REJECTION_CODE IS NULL
673 GROUP BY ra.resource_assignment_id,l_txn_src_code,
674 decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
675 'Y',bl.txn_currency_code,
676 'N',p_fp_cols_rec.x_project_currency_code));
677
678 -- hr_utility.trace('no fo recs inserted in tmp2:'||sql%rowcount);;
679
680 --dbms_output.put_line('No. of rows inserted in tmp2 : '|| sql%rowcount);
681
682 ELSE
683 /*else part is used when p_calling_context = 'WORK_PLAN'and
684 p_fp_cols_rec_etc_wp.x_track_workplan_costs_flag = 'N' and
685 structure sharing code is not null */
686 --dbms_output.put_line('Value for track_workplan_costs_flag:' || p_fp_cols_rec_etc_wp.x_track_workplan_costs_flag);
687 INSERT INTO PA_FP_CALC_AMT_TMP1
688 ( RESOURCE_ASSIGNMENT_ID
689 ,BUDGET_VERSION_ID
690 ,PROJECT_ID
691 ,TASK_ID
692 ,RESOURCE_LIST_MEMBER_ID
693 ,UNIT_OF_MEASURE
694 ,TRACK_AS_LABOR_FLAG
695 ,RESOURCE_ASSIGNMENT_TYPE
696 ,PLANNING_START_DATE
697 ,PLANNING_END_DATE
698 ,RES_TYPE_CODE
699 ,FC_RES_TYPE_CODE
700 ,RESOURCE_CLASS_CODE
701 ,ORGANIZATION_ID
702 ,JOB_ID
703 ,PERSON_ID
704 ,EXPENDITURE_TYPE
705 ,EXPENDITURE_CATEGORY
706 ,REVENUE_CATEGORY_CODE
707 ,EVENT_TYPE
708 ,SUPPLIER_ID
709 ,PROJECT_ROLE_ID
710 ,PERSON_TYPE_CODE
711 ,NON_LABOR_RESOURCE
712 ,BOM_RESOURCE_ID
713 ,INVENTORY_ITEM_ID
714 ,ITEM_CATEGORY_ID
715 ,BILLABLE_PERCENT
716 ,TRANSACTION_SOURCE_CODE
717 ,MFC_COST_TYPE_ID
718 ,PROCURE_RESOURCE_FLAG
719 ,INCURRED_BY_RES_FLAG
720 ,RATE_JOB_ID
721 ,RATE_EXPENDITURE_TYPE
722 ,TA_DISPLAY_FLAG
723 ,RATE_BASED_FLAG
724 ,USE_TASK_SCHEDULE_FLAG
725 ,RATE_EXP_FUNC_CURR_CODE
726 ,RATE_EXPENDITURE_ORG_ID
727 ,INCUR_BY_RES_CLASS_CODE
728 ,INCUR_BY_ROLE_ID
729 ,RESOURCE_CLASS_FLAG
730 ,NAMED_ROLE
731 ,ETC_METHOD_CODE
732 ,MAPPED_FIN_TASK_ID)
733 (SELECT ra.resource_assignment_id,
734 ra.budget_version_id,
735 ra.project_id,
736 ra.task_id,
737 ra.resource_list_member_id,
738 ra.unit_of_measure,
739 ra.track_as_labor_flag,
740 ra.resource_assignment_type,
741 ra.planning_start_date,
742 ra.planning_end_date,
743 ra.res_type_code,
744 ra.fc_res_type_code,
745 ra.resource_class_code,
746 ra.organization_id,
747 ra.job_id,
748 ra.person_id,
749 ra.expenditure_type,
750 ra.expenditure_category,
751 ra.revenue_category_code,
752 ra.event_type,
753 ra.supplier_id,
754 ra.project_role_id,
755 ra.person_type_code,
756 ra.non_labor_resource,
757 ra.bom_resource_id,
758 ra.inventory_item_id,
759 ra.item_category_id,
760 ra.billable_percent,
761 l_txn_src_code,
762 ra.mfc_cost_type_id,
763 ra.procure_resource_flag,
764 ra.incurred_by_res_flag,
765 ra.rate_job_id,
766 ra.rate_expenditure_type,
767 ra.ta_display_flag,
768 ra.rate_based_flag,
769 ra.use_task_schedule_flag,
770 ra.rate_exp_func_curr_code,
771 ra.rate_expenditure_org_id,
772 ra.incur_by_res_class_code,
773 ra.incur_by_role_id,
774 ra.resource_class_flag,
775 ra.named_role,
776 ra.etc_method_code,
777 v.mapped_fin_task_id
778 FROM pa_resource_assignments ra,
779 pa_map_wp_to_fin_tasks_v v
780 WHERE
781 ra.budget_version_id = l_source_bv_id
782 AND v.mapped_fin_task_id = p_task_id
783 AND v.parent_structure_version_id =
784 p_fp_cols_rec_etc_wp.x_project_structure_version_id
785 AND v.proj_element_id = ra.task_id);
786
787 --dbms_output.put_line('No. of rows inserted in tmp1 : '|| sql%rowcount);
788
789
790 SELECT resource_assignment_id,
791 planning_start_date,
792 planning_end_date,
793 resource_list_member_id
794 BULK COLLECT
795 INTO l_res_asg_id_tab,
796 l_start_date_tab,
797 l_end_date_tab,
798 l_res_list_member_id_tab /* Bug 4070849 */
799 FROM pa_fp_calc_amt_tmp1
800 WHERE NVL(mapped_fin_task_id,0) = p_task_id;
801
802 --dbms_output.put_line('Count value for l_res_asg_id_tab : '||l_res_asg_id_tab.count );
803
804 IF l_res_asg_id_tab.count = 0 THEN
805 IF p_pa_debug_mode = 'Y' THEN
806 PA_DEBUG.Reset_Curr_Function;
807 END IF;
808 RETURN;
809 END IF;
810
811 SELECT o.projfunc_cost_rate_type
812 ,o.projfunc_cost_rate_date
813 ,o.projfunc_rev_rate_type
814 ,o.projfunc_rev_rate_date
815 ,o.project_cost_rate_type
816 ,o.project_cost_rate_date
817 ,o.project_rev_rate_type
818 ,o.project_rev_rate_date
819 BULK COLLECT
820 INTO l_projfunc_cost_rate_type_tab,
821 l_projfunc_cost_rate_date_tab,
822 l_projfunc_rev_rate_type_tab,
823 l_projfunc_rev_rate_date_tab,
824 l_proj_cost_rate_type_tab,
825 l_proj_cost_rate_date_tab,
826 l_proj_rev_rate_type_tab,
827 l_proj_rev_rate_date_tab
828 FROM pa_proj_fp_options o
829 WHERE o.fin_plan_version_id = p_budget_version_id;
830
831 SELECT project_currency_code
832 ,projfunc_currency_code
833 BULK COLLECT
834 INTO l_proj_currency_code_tab,
835 l_projfunc_currency_code_tab
836 FROM pa_projects_all
837 WHERE project_id = p_project_id;
838
839 FOR i IN 1..l_res_asg_id_tab.count LOOP
840 --Calling the Get_values_for_planning_Rate api
841 IF p_pa_debug_mode = 'Y' THEN
842 pa_fp_gen_amount_utils.fp_debug
843 (p_called_mode => p_calling_context,
844 p_msg => 'Before calling
845 pa_fp_gen_amount_utils.get_values_for_planning_rate',
846 p_module_name => l_module_name,
847 p_log_level => 5);
848 END IF;
849 PA_FP_GEN_AMOUNT_UTILS.GET_VALUES_FOR_PLANNING_RATE
850 (p_project_id => p_project_id,
851 p_budget_version_id => p_budget_version_id,
852 p_resource_assignment_id => l_res_asg_id_tab(i),
853 p_task_id => p_task_id,
854 p_resource_list_member_id => l_res_list_member_id_tab(i), /* Bug 4070849 */
855 p_txn_currency_code => l_txn_currency_code,
856 x_res_format_id => l_res_format_id,
857 x_resource_asn_rec => l_resource_asn_rec,
858 x_pa_tasks_rec => l_pa_tasks_rec,
859 x_pa_projects_all_rec => l_pa_projects_all_rec,
860 x_proj_fp_options_rec => l_proj_fp_options_rec,
861 x_return_status => x_return_status,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data);
864 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
865 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
866 END IF;
867 IF p_pa_debug_mode = 'Y' THEN
868 pa_fp_gen_amount_utils.fp_debug
869 (p_called_mode => p_calling_context,
870 p_msg => 'Status after calling
871 pa_fp_gen_amount_utils.
872 get_values_for_planning_rate'
873 ||x_return_status,
874 p_module_name => l_module_name,
875 p_log_level => 5);
876 END IF;
877 /* dbms_output.put_line('get_values_for_planning_rate api: '
878 ||X_RETURN_STATUS); */
879
880 /* Assigning project or task values to local variables */
881 IF p_task_id =0 THEN /* project level */
882 l_task_bill_rate_org_id := NULL;
883 l_task_sch_discount := NULL;
884 l_task_sch_date := NULL;
885 l_task_nl_std_bill_rt_sch_id := NULL;
886 l_task_emp_bill_rate_sch_id := NULL;
887 l_task_job_bill_rate_sch_id := NULL;
888 l_task_lab_bill_rate_org_id := NULL;
889 l_task_lab_sch_type := NULL;
890 l_task_non_labor_sch_type := NULL;
891 l_top_task_id := NULL;
892 l_rate_task_id := NULL;
893 /* If task level attributes are not found
894 then the following atributes can be
895 taken from the project level */
896 l_emp_bill_rate_sch_id := l_pa_projects_all_rec.x_emp_bill_rate_schedule_id;
897 l_job_bill_rate_sch_id := l_pa_projects_all_rec.x_job_bill_rate_schedule_id;
898 l_lab_bill_rate_org_id := l_pa_projects_all_rec.x_labor_bill_rate_org_id;
899 l_lab_sch_type := l_pa_projects_all_rec.x_labor_sch_type;
900 l_non_labor_sch_type := l_pa_projects_all_rec.x_non_labor_sch_type;
901 ELSE
902 l_task_bill_rate_org_id := l_pa_tasks_rec.x_task_bill_rate_org_id;
903 l_task_sch_discount := l_pa_tasks_rec.x_task_sch_discount;
904 l_task_sch_date := l_pa_tasks_rec.x_task_sch_date;
905 l_task_nl_std_bill_rt_sch_id := l_pa_tasks_rec.x_task_nl_std_bill_rt_sch_id;
906 l_task_emp_bill_rate_sch_id := l_pa_tasks_rec.x_task_emp_bill_rate_sch_id;
907 l_task_job_bill_rate_sch_id := l_pa_tasks_rec.x_task_job_bill_rate_sch_id;
908 l_task_lab_bill_rate_org_id := l_pa_tasks_rec.x_task_lab_bill_rate_org_id;
909 l_task_lab_sch_type := l_pa_tasks_rec.x_task_lab_sch_type;
910 l_task_non_labor_sch_type := l_pa_tasks_rec.x_task_non_labor_sch_type;
911 l_top_task_id := l_pa_tasks_rec.x_top_task_id;
912 l_rate_task_id := l_resource_asn_rec.x_rate_task_id;
913 /* Task level attributes are found
914 the following atributes can be
915 taken from the task level */
916 l_emp_bill_rate_sch_id := l_task_emp_bill_rate_sch_id;
917 l_job_bill_rate_sch_id := l_task_job_bill_rate_sch_id;
918 l_lab_bill_rate_org_id := l_task_lab_bill_rate_org_id;
919 l_lab_sch_type := l_task_lab_sch_type;
920 l_non_labor_sch_type := l_task_non_labor_sch_type;
921 END IF;
922 IF l_proj_fp_options_rec.
923 x_fp_budget_version_type = 'REVENUE' THEN
924 l_calculate_mode := 'REVENUE';
925 ELSIF l_proj_fp_options_rec.
926 x_fp_budget_version_type = 'COST' THEN
927 l_calculate_mode := 'COST';
928 ELSIF l_proj_fp_options_rec.
929 x_fp_budget_version_type = 'ALL' THEN
930 l_calculate_mode := 'COST_REVENUE';
931 END IF;
932
933 --Calling the Get_planning_Rates api
934 IF p_pa_debug_mode = 'Y' THEN
935 pa_fp_gen_amount_utils.fp_debug
936 (p_called_mode => p_calling_context,
937 p_msg => 'Before calling
938 pa_plan_revenue.Get_planning_Rates',
939 p_module_name => l_module_name,
940 p_log_level => 5);
941 END IF;
942 PA_PLAN_REVENUE.GET_PLANNING_RATES
943 (p_project_id => p_project_id
944 ,p_task_id => l_rate_task_id
945 ,p_top_task_id => l_top_task_id
946 ,p_person_id => l_resource_asn_rec.x_person_id
947 ,p_job_id => l_resource_asn_rec.x_job_id
948 ,p_bill_job_grp_id => l_pa_projects_all_rec.x_bill_job_group_id
949 ,p_resource_class => l_resource_asn_rec.x_resource_class_code
950 ,p_planning_resource_format => l_res_format_id
951 ,p_use_planning_rates_flag => l_proj_fp_options_rec.x_fp_use_planning_rt_flag
952 ,p_rate_based_flag => l_resource_asn_rec.x_rate_based_flag
953 ,p_uom => l_resource_asn_rec.x_unit_of_measure
954 ,p_system_linkage => NULL
955 ,p_project_organz_id => l_pa_projects_all_rec.x_carrying_out_organization_id
956 ,p_rev_res_class_rate_sch_id => l_proj_fp_options_rec.x_fp_res_cl_bill_rate_sch_id
957 ,p_cost_res_class_rate_sch_id => l_proj_fp_options_rec.x_fp_res_cl_raw_cost_sch_id
958 ,p_rev_task_nl_rate_sch_id => l_task_nl_std_bill_rt_sch_id
959 ,p_rev_proj_nl_rate_sch_id => l_pa_projects_all_rec.x_non_lab_std_bill_rt_sch_id
960 ,p_rev_job_rate_sch_id => l_job_bill_rate_sch_id
961 ,p_rev_emp_rate_sch_id => l_emp_bill_rate_sch_id
962 ,p_plan_rev_job_rate_sch_id => l_proj_fp_options_rec.x_fp_rev_job_rate_sch_id
963 ,p_plan_cost_job_rate_sch_id => l_proj_fp_options_rec.x_fp_cost_job_rate_sch_id
964 ,p_plan_rev_emp_rate_sch_id => l_proj_fp_options_rec.x_fp_rev_emp_rate_sch_id
965 ,p_plan_cost_emp_rate_sch_id => l_proj_fp_options_rec.x_fp_cost_emp_rate_sch_id
966 ,p_plan_rev_nlr_rate_sch_id => l_proj_fp_options_rec.x_fp_rev_non_lab_rs_rt_sch_id
967 ,p_plan_cost_nlr_rate_sch_id => l_proj_fp_options_rec.x_fp_cost_non_lab_rs_rt_sch_id
968 ,p_plan_burden_cost_sch_id => l_proj_fp_options_rec.x_fp_cost_burden_rate_sch_id
969 ,p_calculate_mode => l_calculate_mode
970 ,p_mcb_flag => l_pa_projects_all_rec.x_multi_currency_billing_flag
971 ,p_cost_rate_multiplier => l_cost_rate_multiplier
972 ,p_bill_rate_multiplier => l_bill_rate_multiplier
973 ,p_quantity => l_txn_plan_quantity
974 ,p_item_date => l_budget_lines_start_date
975 ,p_cost_sch_type => l_cost_sch_type
976 ,p_labor_sch_type => l_lab_sch_type
977 ,p_non_labor_sch_type => l_non_labor_sch_type
978 ,p_labor_schdl_discnt => NULL
979 ,p_labor_bill_rate_org_id => l_lab_bill_rate_org_id
980 ,p_labor_std_bill_rate_schdl => NULL
981 ,p_labor_schdl_fixed_date => NULL
982 ,p_assignment_id => l_assignment_id
983 ,p_project_org_id => l_pa_projects_all_rec.x_org_id
984 ,p_project_type => l_pa_projects_all_rec.x_project_type
985 ,p_expenditure_type => nvl(l_resource_asn_rec.x_expenditure_type,
986 l_resource_asn_rec.x_rate_expenditure_type)
987 ,p_non_labor_resource => l_resource_asn_rec.x_non_labor_resource
988 ,p_incurred_by_organz_id => l_resource_asn_rec.x_organization_id
989 ,p_override_to_organz_id => l_resource_asn_rec.x_organization_id
990 ,p_expenditure_org_id => nvl(l_resource_asn_rec.x_rate_expenditure_org_id,
991 l_pa_projects_all_rec.x_org_id)
992 ,p_assignment_precedes_task => l_pa_projects_all_rec.x_assign_precedes_task
993 ,p_planning_transaction_id => l_budget_line_id
994 ,p_task_bill_rate_org_id => l_task_bill_rate_org_id
995 ,p_project_bill_rate_org_id => l_pa_projects_all_rec.x_non_labor_bill_rate_org_id
996 ,p_nlr_organization_id => nvl(l_resource_asn_rec.x_organization_id,
997 l_pa_projects_all_rec.x_carrying_out_organization_id)
998 ,p_project_sch_date => l_pa_projects_all_rec.x_non_labor_sch_fixed_date
999 ,p_task_sch_date => l_task_sch_date
1000 ,p_project_sch_discount => l_pa_projects_all_rec.x_non_labor_schedule_discount
1001 ,p_task_sch_discount => l_task_sch_discount
1002 ,p_inventory_item_id => l_resource_asn_rec.x_inventory_item_id
1003 ,p_BOM_resource_Id => l_resource_asn_rec.x_bom_resource_id
1004 ,p_mfc_cost_type_id => l_resource_asn_rec.x_mfc_cost_type_id
1005 ,p_item_category_id => l_resource_asn_rec.x_item_category_id
1006 ,p_mfc_cost_source => l_mfc_cost_source
1007 ,p_cost_override_rate => l_rw_cost_rate_override
1008 ,p_revenue_override_rate => l_bill_rate_override
1009 ,p_override_burden_cost_rate => l_burden_cost_rate_override
1010 ,p_override_currency_code => l_txn_currency_code_override
1011 ,p_txn_currency_code => l_txn_currency_code
1012 ,p_raw_cost => l_txn_raw_cost
1013 ,p_burden_cost => l_txn_burdened_cost
1014 ,p_raw_revenue => l_txn_revenue
1015 ,x_bill_rate => l_bill_rate
1016 ,x_cost_rate => l_cost_rate
1017 ,x_burden_cost_rate => l_burden_cost_rate
1018 ,x_burden_multiplier => l_burden_multiplier
1019 ,x_raw_cost => l_raw_cost
1020 ,x_burden_cost => l_burden_cost
1021 ,x_raw_revenue => l_raw_revenue
1022 ,x_bill_markup_percentage => l_bill_markup_percentage
1023 ,x_cost_txn_curr_code => l_cost_txn_curr_code
1024 ,x_rev_txn_curr_code => l_rev_txn_curr_code
1025 ,x_raw_cost_rejection_code => l_raw_cost_rejection_code
1026 ,x_burden_cost_rejection_code => l_burden_cost_rejection_code
1027 ,x_revenue_rejection_code => l_revenue_rejection_code
1028 ,x_cost_ind_compiled_set_id => l_cost_ind_compiled_set_id
1029 ,x_return_status => x_return_status
1030 ,x_msg_data => x_msg_data
1031 ,x_msg_count => x_msg_count);
1032 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1033 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1034 END IF;
1035 IF p_pa_debug_mode = 'Y' THEN
1036 pa_fp_gen_amount_utils.fp_debug
1037 (p_called_mode => p_calling_context,
1038 p_msg => 'Status after calling
1039 pa_plan_revenue.Get_planning_Rates'
1040 ||x_return_status,
1041 p_module_name => l_module_name,
1042 p_log_level => 5);
1043 END IF;
1044 /*dbms_output.put_line('Status of Get_planning_Rates api: '
1045 ||X_RETURN_STATUS); */
1046
1047 l_ins_bill_rate := l_bill_rate;
1048 l_ins_cost_rate := l_cost_rate;
1049 l_ins_burd_cost_rate := l_burden_cost_rate;
1050 l_ins_burd_multiplier := l_burden_multiplier;
1051 l_ins_pfc_raw_cost_tab(l_ins_pfc_raw_cost_tab.count+1)
1052 := l_raw_cost;
1053 l_ins_pfc_burd_cost_tab(l_ins_pfc_burd_cost_tab.count+1)
1054 := l_burden_cost;
1055 l_ins_pfc_revenue_tab(l_ins_pfc_revenue_tab.count+1)
1056 := l_raw_revenue;
1057 l_ins_bill_markup_perc := l_bill_markup_percentage;
1058 l_ins_cost_txn_curr_code := l_cost_txn_curr_code;
1059 l_ins_rev_txn_curr_code := l_rev_txn_curr_code;
1060 l_ins_raw_cost_rej_code := l_raw_cost_rejection_code;
1061 l_ins_burd_cost_rej_code := l_burden_cost_rejection_code;
1062 l_ins_rev_rej_code := l_revenue_rejection_code;
1063 l_ins_cost_ind_com_set_id:= l_cost_ind_compiled_set_id;
1064
1065 IF p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' AND
1066 l_cost_txn_curr_code <>
1067 p_fp_cols_rec.X_PROJECT_CURRENCY_CODE THEN
1068 --Calling the conv_mc_bulk api
1069 IF p_pa_debug_mode = 'Y' THEN
1070 pa_fp_gen_amount_utils.fp_debug
1071 (p_called_mode => p_calling_context,
1072 p_msg => 'Before calling
1073 pa_fp_multi_currency_pkg.conv_mc_bulk',
1074 p_module_name => l_module_name,
1075 p_log_level => 5);
1076 END IF;
1077
1078 l_res_asn_id_tab.delete;
1079 l_start_date_tab.delete;
1080 l_end_date_tab.delete;
1081 l_txn_currency_code_tab.delete;
1082 l_txn_rw_cost_tab.delete;
1083 l_txn_burdend_cost_tab.delete;
1084 l_txn_rev_tab.delete;
1085 l_projfunc_currency_code_tab.delete;
1086 l_projfunc_cost_rate_type_tab.delete;
1087 l_projfunc_cost_rate_tab.delete;
1088 l_projfunc_cost_rate_date_tab.delete;
1089 l_projfunc_rev_rate_type_tab.delete;
1090 l_projfunc_rev_rate_tab.delete;
1091 l_projfunc_rev_rate_date_tab.delete;
1092 l_projfunc_raw_cost_tab.delete;
1093 l_projfunc_burdened_cost_tab.delete;
1094 l_projfunc_revenue_tab.delete;
1095 l_projfunc_rejection_tab.delete;
1096 l_proj_raw_cost_tab.delete;
1097 l_proj_burdened_cost_tab.delete;
1098 l_proj_revenue_tab.delete;
1099 l_proj_rejection_tab.delete;
1100 l_proj_currency_code_tab.delete;
1101 l_proj_cost_rate_type_tab.delete;
1102 l_proj_cost_rate_tab.delete;
1103 l_proj_cost_rate_date_tab.delete;
1104 l_proj_rev_rate_type_tab.delete;
1105 l_proj_rev_rate_tab.delete;
1106 l_proj_rev_rate_date_tab.delete;
1107 l_user_validate_flag_tab.delete;
1108
1109 PA_FP_MULTI_CURRENCY_PKG.CONV_MC_BULK(
1110 p_resource_assignment_id_tab => l_res_asn_id_tab
1111 ,p_start_date_tab => l_start_date_tab
1112 ,p_end_date_tab => l_end_date_tab
1113 ,p_txn_currency_code_tab => l_txn_currency_code_tab
1114 ,p_txn_raw_cost_tab => l_txn_rw_cost_tab
1115 ,p_txn_burdened_cost_tab => l_txn_burdend_cost_tab
1116 ,p_txn_revenue_tab => l_txn_rev_tab
1117 ,p_projfunc_currency_code_tab => l_projfunc_currency_code_tab
1118 ,p_projfunc_cost_rate_type_tab => l_projfunc_cost_rate_type_tab
1119 ,p_projfunc_cost_rate_tab => l_projfunc_cost_rate_tab
1120 ,p_projfunc_cost_rate_date_tab => l_projfunc_cost_rate_date_tab
1121 ,p_projfunc_rev_rate_type_tab => l_projfunc_rev_rate_type_tab
1122 ,p_projfunc_rev_rate_tab => l_projfunc_rev_rate_tab
1123 ,p_projfunc_rev_rate_date_tab => l_projfunc_rev_rate_date_tab
1124 ,x_projfunc_raw_cost_tab => l_projfunc_raw_cost_tab
1125 ,x_projfunc_burdened_cost_tab => l_projfunc_burdened_cost_tab
1126 ,x_projfunc_revenue_tab => l_projfunc_revenue_tab
1127 ,x_projfunc_rejection_tab => l_projfunc_rejection_tab
1128 ,p_proj_currency_code_tab => l_proj_currency_code_tab
1129 ,p_proj_cost_rate_type_tab => l_proj_cost_rate_type_tab
1130 ,p_proj_cost_rate_tab => l_proj_cost_rate_tab
1131 ,p_proj_cost_rate_date_tab => l_proj_cost_rate_date_tab
1132 ,p_proj_rev_rate_type_tab => l_proj_rev_rate_type_tab
1133 ,p_proj_rev_rate_tab => l_proj_rev_rate_tab
1134 ,p_proj_rev_rate_date_tab => l_proj_rev_rate_date_tab
1135 ,x_proj_raw_cost_tab => l_proj_raw_cost_tab
1136 ,x_proj_burdened_cost_tab => l_proj_burdened_cost_tab
1137 ,x_proj_revenue_tab => l_proj_revenue_tab
1138 ,x_proj_rejection_tab => l_proj_rejection_tab
1139 ,p_user_validate_flag_tab => l_user_validate_flag_tab
1140 ,p_calling_module => 'FORECAST_GENERATION'-- Added for Bug#5395732
1141 ,x_return_status => x_return_status
1142 ,x_msg_count => x_msg_count
1143 ,x_msg_data => x_msg_data);
1144 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1145 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1146 END IF;
1147 IF p_pa_debug_mode = 'Y' THEN
1148 pa_fp_gen_amount_utils.fp_debug
1149 (p_called_mode => p_calling_context,
1150 p_msg => 'Status after calling
1151 pa_fp_multi_currency_pkg.conv_mc_bulk: '
1152 ||x_return_status,
1153 p_module_name => l_module_name,
1154 p_log_level => 5);
1155 END IF;
1156 /* dbms_output.put_line('Status of conv_mc_bulk api: '
1157 ||X_RETURN_STATUS); */
1158 END IF;
1159 l_ins_pfc_raw_cost_tab := l_projfunc_raw_cost_tab;
1160 l_ins_pfc_burd_cost_tab:= l_projfunc_burdened_cost_tab;
1161 l_ins_pfc_revenue_tab := l_projfunc_revenue_tab;
1162 l_ins_pfc_rejection_tab:= l_projfunc_rejection_tab;
1163 l_ins_pc_raw_cost_tab := l_proj_raw_cost_tab;
1164 l_ins_pc_burdened_cost_tab:= l_proj_burdened_cost_tab;
1165 l_ins_pc_revenue_tab := l_proj_revenue_tab;
1166 l_ins_pc_rejection_tab := l_proj_rejection_tab;
1167 END LOOP;
1168
1169 /* bulk insert */
1170 FORALL m IN 1..l_res_asn_id_tab.count
1171 INSERT INTO pa_fp_calc_amt_tmp2
1172 (resource_assignment_id,
1173 total_pc_raw_cost,
1174 total_pc_burdened_cost,
1175 total_pc_revenue,
1176 total_pfc_raw_cost,
1177 total_pfc_burdened_cost,
1178 total_pfc_revenue,
1179 transaction_source_code )
1180 VALUES
1181 (l_res_asn_id_tab(m),
1182 l_ins_pc_raw_cost_tab(m),
1183 l_ins_pc_burdened_cost_tab(m),
1184 l_ins_pc_revenue_tab(m),
1185 l_ins_pfc_raw_cost_tab(m),
1186 l_ins_pfc_burd_cost_tab(m),
1187 l_ins_pfc_revenue_tab(m),
1188 l_txn_src_code);
1189 END IF;
1190 /* select not required as the amounts will be populated
1191 in the tmp table.
1192 SELECT total_plan_quantity,
1193 total_txn_raw_cost,
1194 total_txn_burdened_cost,
1195 total_txn_revenue
1196 INTO x_txn_amt_rec.quantity_sum,
1197 x_txn_amt_rec.txn_raw_cost_sum,
1198 x_txn_amt_rec.txn_burdened_cost_sum,
1199 x_txn_amt_rec.txn_revenue_sum
1200 FROM pa_fp_calc_amt_tmp2;
1201 */
1202 IF p_pa_debug_mode = 'Y' THEN
1203 PA_DEBUG.Reset_Curr_Function;
1204 END IF;
1205
1206 EXCEPTION
1207 WHEN NO_DATA_FOUND THEN
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 IF P_PA_DEBUG_MODE = 'Y' THEN
1210 pa_fp_gen_amount_utils.fp_debug
1211 (p_called_mode => p_calling_context,
1212 p_msg => 'Invalid Resource assignment Id',
1213 p_module_name => l_module_name,
1214 p_log_level => 5);
1215 PA_DEBUG.Reset_Curr_Function;
1216 END IF;
1217 RAISE;
1218
1219 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1220 l_msg_count := FND_MSG_PUB.count_msg;
1221 IF l_msg_count = 1 THEN
1222 PA_INTERFACE_UTILS_PUB.get_messages
1223 ( p_encoded => FND_API.G_TRUE,
1224 p_msg_index => 1,
1225 p_msg_count => l_msg_count,
1226 p_msg_data => l_msg_data,
1227 p_data => l_data,
1228 p_msg_index_out => l_msg_index_out);
1229 x_msg_data := l_data;
1230 x_msg_count := l_msg_count;
1231 ELSE
1232 x_msg_count := l_msg_count;
1233 END IF;
1234 ROLLBACK;
1235
1236 x_return_status := FND_API.G_RET_STS_ERROR;
1237 IF P_PA_DEBUG_MODE = 'Y' THEN
1238 pa_fp_gen_amount_utils.fp_debug
1239 (p_called_mode => p_calling_context,
1240 p_msg => 'Invalid Arguments Passed',
1241 p_module_name => l_module_name,
1242 p_log_level => 5);
1243 PA_DEBUG.Reset_Curr_Function;
1244 END IF;
1245 RAISE;
1246
1247 WHEN OTHERS THEN
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 x_msg_data := SUBSTR(SQLERRM,1,240);
1250 FND_MSG_PUB.add_exc_msg
1251 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PVT'
1252 ,p_procedure_name => 'GET_TOTAL_PLAN_TXN_AMTS');
1253 IF P_PA_DEBUG_MODE = 'Y' THEN
1254 pa_fp_gen_amount_utils.fp_debug
1255 (p_called_mode => p_calling_context,
1256 p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1257 p_module_name => l_module_name,
1258 p_log_level => 5);
1259 PA_DEBUG.Reset_Curr_Function;
1260 END IF;
1261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1262
1263 END GET_TOTAL_PLAN_TXN_AMTS;
1264
1265 PROCEDURE UPDATE_TOTAL_PLAN_AMTS
1266 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1267 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1268 X_MSG_COUNT OUT NOCOPY NUMBER,
1269 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1270
1271 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.update_total_plan_amts';
1272
1273 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
1274 l_last_update_login NUMBER := FND_GLOBAL.login_id;
1275 l_sysdate DATE := SYSDATE;
1276 BEGIN
1277 --Setting initial values
1278 X_MSG_COUNT := 0;
1279 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1280
1281 IF p_pa_debug_mode = 'Y' THEN
1282 pa_debug.set_curr_function( p_function => 'UPDATE_TOTAL_PLAN_AMTS'
1283 ,p_debug_mode => p_pa_debug_mode);
1284 END IF;
1285
1286 UPDATE pa_budget_lines
1287 SET raw_cost = nvl(raw_cost,0) + nvl(init_raw_cost,0),
1288 burdened_cost = nvl(burdened_cost,0) + nvl(init_burdened_cost,0),
1289 revenue = nvl(revenue,0) + nvl(init_revenue,0),
1290 project_raw_cost = nvl(project_raw_cost,0) + nvl(project_init_raw_cost,0),
1291 project_burdened_cost = nvl(project_burdened_cost,0) +
1292 nvl(project_init_burdened_cost,0),
1293 project_revenue = nvl(project_revenue,0) + nvl(project_init_revenue,0),
1294 txn_raw_cost = nvl(txn_raw_cost,0) + nvl(txn_init_raw_cost,0),
1295 txn_burdened_cost = nvl(txn_burdened_cost,0) +
1296 nvl(txn_init_burdened_cost,0),
1297 txn_revenue = nvl(txn_revenue,0) + nvl(txn_init_revenue,0),
1298 quantity = nvl(quantity,0) + nvl(init_quantity,0),
1299 LAST_UPDATE_DATE = l_sysdate,
1300 LAST_UPDATED_BY = l_last_updated_by,
1301 LAST_UPDATE_LOGIN = l_last_update_login
1302 WHERE budget_version_id = p_budget_version_id
1303 and (resource_assignment_id,txn_currency_code) in
1304 (select target_res_asg_id,etc_currency_code
1305 from PA_FP_CALC_AMT_TMP2
1306 where transaction_source_code = 'ETC');
1307 -- Above and clause added for bug 4247647 ...
1308
1309 IF p_pa_debug_mode = 'Y' THEN
1310 PA_DEBUG.Reset_Curr_Function;
1311 END IF;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316 x_msg_data := SUBSTR(SQLERRM,1,240);
1317 FND_MSG_PUB.add_exc_msg
1318 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PVT'
1319 ,p_procedure_name => 'UPDATE_TOTAL_PLAN_AMTS');
1320 IF P_PA_DEBUG_MODE = 'Y' THEN
1321 pa_fp_gen_amount_utils.fp_debug
1322 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1323 p_module_name => l_module_name,
1324 p_log_level => 5);
1325 PA_DEBUG.Reset_Curr_Function;
1326 END IF;
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END UPDATE_TOTAL_PLAN_AMTS;
1329
1330 PROCEDURE GET_ACTUAL_TXN_AMOUNT
1331 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1332 P_TASK_ID IN PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE,
1333 P_RES_LIST_MEMBER_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_LIST_MEMBER_ID%TYPE,
1334 P_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
1335 P_TXN_CURRENCY_CODE IN PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE,
1336 P_CURRENCY_FLAG IN VARCHAR2,
1337 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1338 P_ACTUAL_FROM_DATE IN PA_PERIODS_ALL.START_DATE%TYPE,
1339 P_ACTUAL_TO_DATE IN PA_PERIODS_ALL.START_DATE%TYPE,
1340 X_TXN_AMT_REC OUT NOCOPY PA_FP_GEN_FCST_AMT_PUB.TXN_AMT_REC_TYP,
1341 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1342 X_MSG_COUNT OUT NOCOPY NUMBER,
1343 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1344
1345 L_MODULE_NAME VARCHAR2(200) := 'PA.PLSQL.PA_FP_GEN_FCST_AMT_PUB.GET_ACTUAL_TXN_AMOUNT';
1346 L_RES_ASG_ID PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1347
1348 L_RATE_BASED_FLAG VARCHAR2(1);
1349 /*'TC' REPRESENTS TXN CURRENCY CODE
1350 'PC' REPRESENTS PROJECT CURRENCY CODE*/
1351 L_CURRENCY_FLAG VARCHAR2(2) := 'TC';
1352
1353 BEGIN
1354 --SETTING INITIAL VALUES
1355 X_MSG_COUNT := 0;
1356 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1357
1358 IF P_PA_DEBUG_MODE = 'Y' THEN
1359 PA_DEBUG.SET_CURR_FUNCTION( P_FUNCTION => 'GET_ACTUAL_TXN_AMOUNT'
1360 ,P_DEBUG_MODE => P_PA_DEBUG_MODE);
1361 END IF;
1362
1363 X_TXN_AMT_REC.QUANTITY_SUM := 0;
1364 X_TXN_AMT_REC.TXN_RAW_COST_SUM := 0;
1365 X_TXN_AMT_REC.TXN_BURDENED_COST_SUM := 0;
1366 X_TXN_AMT_REC.TXN_REVENUE_SUM := 0;
1367 X_TXN_AMT_REC.NO_OF_PERIODS := 0;
1368
1369 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1370 (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1371 P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1372 SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
1373 FROM pa_resource_assignments
1374 WHERE resource_assignment_id = P_RES_ASG_ID;
1375 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1376 SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
1377 FROM pa_resource_assignments
1378 WHERE budget_version_id = p_budget_version_id
1379 AND resource_list_member_id = p_res_list_member_id
1380 AND NVL(task_id, 0) = 0;
1381 END IF;
1382 l_currency_flag := P_CURRENCY_FLAG;
1383 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1384 /* Getting the sum of init qty,raw_cost,burdened_cost,rev
1385 between actual_from_date and actual_to_date for the given period(PA or GL) */
1386 BEGIN
1387 -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1388 -- Average of Actuals, we should get that Actual data from the Target
1389 -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1390
1391 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1392 (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1393 P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1394 SELECT count(*),
1395 SUM (DECODE(l_currency_flag,
1396 'TC', NVL(init_quantity,0),
1397 'PC', DECODE(l_rate_based_flag,
1398 'Y', NVL(init_quantity,0),
1399 decode(P_FP_COLS_REC.x_version_type,
1400 'REVENUE',nvl(project_init_revenue,0),
1401 NVL(project_init_raw_cost,0)))
1402 )),
1403 SUM (DECODE(l_currency_flag,
1404 'TC', NVL(txn_init_raw_cost,0),
1405 'PC', NVL(project_init_raw_cost,0))),
1406 SUM (DECODE(l_currency_flag,
1407 'TC', NVL(txn_init_burdened_cost,0),
1408 'PC', NVL(project_init_burdened_cost,0))),
1409 SUM (DECODE(l_currency_flag,
1410 'TC', NVL(txn_init_revenue,0),
1411 'PC', NVL(project_init_revenue,0)))
1412 INTO x_txn_amt_rec.no_of_periods,
1413 x_txn_amt_rec.quantity_sum,
1414 x_txn_amt_rec.txn_raw_cost_sum,
1415 x_txn_amt_rec.txn_burdened_cost_sum,
1416 x_txn_amt_rec.txn_revenue_sum
1417 FROM pa_budget_lines
1418 WHERE resource_assignment_id = P_RES_ASG_ID
1419 AND start_date >= p_actual_from_date
1420 AND start_date <= p_actual_to_date;
1421
1422 IF l_currency_flag = 'PC' THEN
1423 SELECT COUNT(DISTINCT period_name) INTO x_txn_amt_rec.no_of_periods
1424 FROM pa_budget_lines
1425 WHERE resource_assignment_id = P_RES_ASG_ID
1426 AND start_date >= p_actual_from_date
1427 AND start_date <= p_actual_to_date;
1428 END IF;
1429 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1430 IF p_fp_cols_rec.x_time_phased_code = 'P' THEN
1431 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1432 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1433 count(*),
1434 SUM (DECODE(l_rate_based_flag, 'Y',
1435 NVL(quantity,0),
1436 DECODE(l_currency_flag,
1437 'PC', NVL(prj_raw_cost,0),
1438 'TC', NVL(txn_raw_cost,0))
1439 )),
1440 SUM (DECODE(l_currency_flag,
1441 'TC', NVL(txn_raw_cost,0),
1442 'PC', NVL(prj_raw_cost,0))),
1443 SUM (DECODE(l_currency_flag,
1444 'TC', NVL(txn_brdn_cost,0),
1445 'PC', NVL(prj_brdn_cost,0))),
1446 SUM (DECODE(l_currency_flag,
1447 'TC', NVL(txn_revenue,0),
1448 'PC', NVL(prj_revenue,0)))
1449 INTO x_txn_amt_rec.no_of_periods,
1450 x_txn_amt_rec.quantity_sum,
1451 x_txn_amt_rec.txn_raw_cost_sum,
1452 x_txn_amt_rec.txn_burdened_cost_sum,
1453 x_txn_amt_rec.txn_revenue_sum
1454 FROM PA_FP_FCST_GEN_TMP1 tmp,
1455 pa_periods_all pd
1456 WHERE tmp.data_type_code = 'TARGET_FP'
1457 AND tmp.project_element_id = P_TASK_ID
1458 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1459 AND (NVL(tmp.quantity,0) <> 0
1460 OR NVL(tmp.txn_raw_cost,0) <> 0
1461 OR NVL(tmp.txn_brdn_cost,0) <> 0
1462 OR NVL(tmp.txn_revenue,0) <> 0)
1463 AND pd.period_name = tmp.period_name
1464 AND pd.org_id = p_fp_cols_rec.x_org_id
1465 AND pd.start_date >= p_actual_from_date
1466 AND pd.start_date <= p_actual_to_date;
1467 ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1468 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1469 count(*),
1470 SUM (DECODE(l_rate_based_flag, 'Y',
1471 NVL(quantity,0),
1472 DECODE(l_currency_flag,
1473 'PC', NVL(prj_raw_cost,0),
1474 'TC', NVL(txn_raw_cost,0))
1475 )),
1476 SUM (DECODE(l_currency_flag,
1477 'TC', NVL(txn_raw_cost,0),
1478 'PC', NVL(prj_raw_cost,0))),
1479 SUM (DECODE(l_currency_flag,
1480 'TC', NVL(txn_brdn_cost,0),
1481 'PC', NVL(prj_brdn_cost,0))),
1482 SUM (DECODE(l_currency_flag,
1483 'TC', NVL(txn_revenue,0),
1484 'PC', NVL(prj_revenue,0)))
1485 INTO x_txn_amt_rec.no_of_periods,
1486 x_txn_amt_rec.quantity_sum,
1487 x_txn_amt_rec.txn_raw_cost_sum,
1488 x_txn_amt_rec.txn_burdened_cost_sum,
1489 x_txn_amt_rec.txn_revenue_sum
1490 FROM PA_FP_FCST_GEN_TMP1 tmp,
1491 pa_periods_all pd
1492 WHERE tmp.data_type_code = 'TARGET_FP'
1493 AND tmp.project_element_id = P_TASK_ID
1494 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1495 AND (NVL(tmp.quantity,0) <> 0
1496 OR NVL(tmp.txn_raw_cost,0) <> 0
1497 OR NVL(tmp.txn_brdn_cost,0) <> 0)
1498 AND pd.period_name = tmp.period_name
1499 AND pd.org_id = p_fp_cols_rec.x_org_id
1500 AND pd.start_date >= p_actual_from_date
1501 AND pd.start_date <= p_actual_to_date;
1502 ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1503 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1504 count(*),
1505 SUM (DECODE(l_rate_based_flag, 'Y',
1506 NVL(quantity,0),
1507 DECODE(l_currency_flag,
1508 'PC', NVL(prj_revenue,0),
1509 'TC', NVL(txn_revenue,0))
1510 )),
1511 SUM (DECODE(l_currency_flag,
1512 'TC', NVL(txn_raw_cost,0),
1513 'PC', NVL(prj_raw_cost,0))),
1514 SUM (DECODE(l_currency_flag,
1515 'TC', NVL(txn_brdn_cost,0),
1516 'PC', NVL(prj_brdn_cost,0))),
1517 SUM (DECODE(l_currency_flag,
1518 'TC', NVL(txn_revenue,0),
1519 'PC', NVL(prj_revenue,0)))
1520 INTO x_txn_amt_rec.no_of_periods,
1521 x_txn_amt_rec.quantity_sum,
1522 x_txn_amt_rec.txn_raw_cost_sum,
1523 x_txn_amt_rec.txn_burdened_cost_sum,
1524 x_txn_amt_rec.txn_revenue_sum
1525 FROM PA_FP_FCST_GEN_TMP1 tmp,
1526 pa_periods_all pd
1527 WHERE tmp.data_type_code = 'TARGET_FP'
1528 AND tmp.project_element_id = P_TASK_ID
1529 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1530 AND (NVL(tmp.quantity,0) <> 0
1531 OR NVL(tmp.txn_revenue,0) <> 0)
1532 AND pd.period_name = tmp.period_name
1533 AND pd.org_id = p_fp_cols_rec.x_org_id
1534 AND pd.start_date >= p_actual_from_date
1535 AND pd.start_date <= p_actual_to_date;
1536 END IF;
1537 IF l_currency_flag = 'PC' THEN
1538 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1539 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1540 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1541 FROM PA_FP_FCST_GEN_TMP1 tmp,
1542 pa_periods_all pd
1543 WHERE tmp.data_type_code = 'TARGET_FP'
1544 AND tmp.project_element_id = P_TASK_ID
1545 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1546 AND (NVL(tmp.quantity,0) <> 0
1547 OR NVL(tmp.txn_raw_cost,0) <> 0
1548 OR NVL(tmp.txn_brdn_cost,0) <> 0
1549 OR NVL(tmp.txn_revenue,0) <> 0)
1550 AND pd.period_name = tmp.period_name
1551 AND pd.org_id = p_fp_cols_rec.x_org_id
1552 AND pd.start_date >= p_actual_from_date
1553 AND pd.start_date <= p_actual_to_date;
1554 ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1555 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1556 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1557 FROM PA_FP_FCST_GEN_TMP1 tmp,
1558 pa_periods_all pd
1559 WHERE tmp.data_type_code = 'TARGET_FP'
1560 AND tmp.project_element_id = P_TASK_ID
1561 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1562 AND (NVL(tmp.quantity,0) <> 0
1563 OR NVL(tmp.txn_raw_cost,0) <> 0
1564 OR NVL(tmp.txn_brdn_cost,0) <> 0)
1565 AND pd.period_name = tmp.period_name
1566 AND pd.org_id = p_fp_cols_rec.x_org_id
1567 AND pd.start_date >= p_actual_from_date
1568 AND pd.start_date <= p_actual_to_date;
1569 ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1570 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1571 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1572 FROM PA_FP_FCST_GEN_TMP1 tmp,
1573 pa_periods_all pd
1574 WHERE tmp.data_type_code = 'TARGET_FP'
1575 AND tmp.project_element_id = P_TASK_ID
1576 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1577 AND (NVL(tmp.quantity,0) <> 0
1578 OR NVL(tmp.txn_revenue,0) <> 0)
1579 AND pd.period_name = tmp.period_name
1580 AND pd.org_id = p_fp_cols_rec.x_org_id
1581 AND pd.start_date >= p_actual_from_date
1582 AND pd.start_date <= p_actual_to_date;
1583 END IF;
1584 END IF;
1585 ELSIF p_fp_cols_rec.x_time_phased_code = 'G' THEN
1586 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1587 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1588 count(*),
1589 SUM (DECODE(l_rate_based_flag, 'Y',
1590 NVL(quantity,0),
1591 DECODE(l_currency_flag,
1592 'PC', NVL(prj_raw_cost,0),
1593 'TC', NVL(txn_raw_cost,0))
1594 )),
1595 SUM (DECODE(l_currency_flag,
1596 'TC', NVL(txn_raw_cost,0),
1597 'PC', NVL(prj_raw_cost,0))),
1598 SUM (DECODE(l_currency_flag,
1599 'TC', NVL(txn_brdn_cost,0),
1600 'PC', NVL(prj_brdn_cost,0))),
1601 SUM (DECODE(l_currency_flag,
1602 'TC', NVL(txn_revenue,0),
1603 'PC', NVL(prj_revenue,0)))
1604 INTO x_txn_amt_rec.no_of_periods,
1605 x_txn_amt_rec.quantity_sum,
1606 x_txn_amt_rec.txn_raw_cost_sum,
1607 x_txn_amt_rec.txn_burdened_cost_sum,
1608 x_txn_amt_rec.txn_revenue_sum
1609 FROM PA_FP_FCST_GEN_TMP1 tmp,
1610 gl_period_statuses pd
1611 WHERE tmp.data_type_code = 'TARGET_FP'
1612 AND tmp.project_element_id = P_TASK_ID
1613 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1614 AND (NVL(tmp.quantity,0) <> 0
1615 OR NVL(tmp.txn_raw_cost,0) <> 0
1616 OR NVL(tmp.txn_brdn_cost,0) <> 0
1617 OR NVL(tmp.txn_revenue,0) <> 0)
1618 AND pd.period_name = tmp.period_name
1619 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1620 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1621 AND pd.adjustment_period_flag = 'N'
1622 AND pd.start_date >= p_actual_from_date
1623 AND pd.start_date <= p_actual_to_date;
1624 ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1625 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1626 count(*),
1627 SUM (DECODE(l_rate_based_flag, 'Y',
1628 NVL(quantity,0),
1629 DECODE(l_currency_flag,
1630 'PC', NVL(prj_raw_cost,0),
1631 'TC', NVL(txn_raw_cost,0))
1632 )),
1633 SUM (DECODE(l_currency_flag,
1634 'TC', NVL(txn_raw_cost,0),
1635 'PC', NVL(prj_raw_cost,0))),
1636 SUM (DECODE(l_currency_flag,
1637 'TC', NVL(txn_brdn_cost,0),
1638 'PC', NVL(prj_brdn_cost,0))),
1639 SUM (DECODE(l_currency_flag,
1640 'TC', NVL(txn_revenue,0),
1641 'PC', NVL(prj_revenue,0)))
1642 INTO x_txn_amt_rec.no_of_periods,
1643 x_txn_amt_rec.quantity_sum,
1644 x_txn_amt_rec.txn_raw_cost_sum,
1645 x_txn_amt_rec.txn_burdened_cost_sum,
1646 x_txn_amt_rec.txn_revenue_sum
1647 FROM PA_FP_FCST_GEN_TMP1 tmp,
1648 gl_period_statuses pd
1649 WHERE tmp.data_type_code = 'TARGET_FP'
1650 AND tmp.project_element_id = P_TASK_ID
1651 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1652 AND (NVL(tmp.quantity,0) <> 0
1653 OR NVL(tmp.txn_brdn_cost,0) <> 0
1654 OR NVL(tmp.txn_raw_cost,0) <> 0)
1655 AND pd.period_name = tmp.period_name
1656 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1657 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1658 AND pd.adjustment_period_flag = 'N'
1659 AND pd.start_date >= p_actual_from_date
1660 AND pd.start_date <= p_actual_to_date;
1661 ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1662 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1663 count(*),
1664 SUM (DECODE(l_rate_based_flag, 'Y',
1665 NVL(quantity,0),
1666 DECODE(l_currency_flag,
1667 'PC', NVL(prj_revenue,0),
1668 'TC', NVL(txn_revenue,0))
1669 )),
1670 SUM (DECODE(l_currency_flag,
1671 'TC', NVL(txn_raw_cost,0),
1672 'PC', NVL(prj_raw_cost,0))),
1673 SUM (DECODE(l_currency_flag,
1674 'TC', NVL(txn_brdn_cost,0),
1675 'PC', NVL(prj_brdn_cost,0))),
1676 SUM (DECODE(l_currency_flag,
1677 'TC', NVL(txn_revenue,0),
1678 'PC', NVL(prj_revenue,0)))
1679 INTO x_txn_amt_rec.no_of_periods,
1680 x_txn_amt_rec.quantity_sum,
1681 x_txn_amt_rec.txn_raw_cost_sum,
1682 x_txn_amt_rec.txn_burdened_cost_sum,
1683 x_txn_amt_rec.txn_revenue_sum
1684 FROM PA_FP_FCST_GEN_TMP1 tmp,
1685 gl_period_statuses pd
1686 WHERE tmp.data_type_code = 'TARGET_FP'
1687 AND tmp.project_element_id = P_TASK_ID
1688 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1689 AND (NVL(tmp.quantity,0) <> 0
1690 OR NVL(tmp.txn_revenue,0) <> 0)
1691 AND pd.period_name = tmp.period_name
1692 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1693 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1694 AND pd.adjustment_period_flag = 'N'
1695 AND pd.start_date >= p_actual_from_date
1696 AND pd.start_date <= p_actual_to_date;
1697 END IF;
1698 IF l_currency_flag = 'PC' THEN
1699 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1700 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1701 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1702 FROM PA_FP_FCST_GEN_TMP1 tmp,
1703 gl_period_statuses pd
1704 WHERE tmp.data_type_code = 'TARGET_FP'
1705 AND tmp.project_element_id = P_TASK_ID
1706 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1707 AND (NVL(tmp.quantity,0) <> 0
1708 OR NVL(tmp.txn_raw_cost,0) <> 0
1709 OR NVL(tmp.txn_brdn_cost,0) <> 0
1710 OR NVL(tmp.txn_revenue,0) <> 0)
1711 AND pd.period_name = tmp.period_name
1712 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1713 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1714 AND pd.adjustment_period_flag = 'N'
1715 AND pd.start_date >= p_actual_from_date
1716 AND pd.start_date <= p_actual_to_date;
1717 ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1718 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1719 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1720 FROM PA_FP_FCST_GEN_TMP1 tmp,
1721 gl_period_statuses pd
1722 WHERE tmp.data_type_code = 'TARGET_FP'
1723 AND tmp.project_element_id = P_TASK_ID
1724 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1725 AND (NVL(tmp.quantity,0) <> 0
1726 OR NVL(tmp.txn_raw_cost,0) <> 0
1727 OR NVL(tmp.txn_brdn_cost,0) <> 0)
1728 AND pd.period_name = tmp.period_name
1729 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1730 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1731 AND pd.adjustment_period_flag = 'N'
1732 AND pd.start_date >= p_actual_from_date
1733 AND pd.start_date <= p_actual_to_date;
1734 ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1735 SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1736 COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
1737 FROM PA_FP_FCST_GEN_TMP1 tmp,
1738 gl_period_statuses pd
1739 WHERE tmp.data_type_code = 'TARGET_FP'
1740 AND tmp.project_element_id = P_TASK_ID
1741 AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
1742 AND (NVL(tmp.quantity,0) <> 0
1743 OR NVL(tmp.txn_revenue,0) <> 0)
1744 AND pd.period_name = tmp.period_name
1745 AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
1746 AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1747 AND pd.adjustment_period_flag = 'N'
1748 AND pd.start_date >= p_actual_from_date
1749 AND pd.start_date <= p_actual_to_date;
1750 END IF;
1751 END IF;
1752 END IF;
1753 END IF;
1754
1755 /* bug : 4036127 Overriding the count when the currency flag is PC.
1756 Only the distinct Period name count should be taken. We can always
1757 use the following SELECT to get the period count. But, if the
1758 currency flag is TC, we can avoid this SELECT as we can get the
1759 count from the above sql. */
1760
1761 EXCEPTION
1762 WHEN NO_DATA_FOUND THEN
1763 x_txn_amt_rec.quantity_sum := 0;
1764 x_txn_amt_rec.txn_raw_cost_sum := 0;
1765 x_txn_amt_rec.txn_burdened_cost_sum := 0;
1766 x_txn_amt_rec.txn_revenue_sum := 0;
1767 x_txn_amt_rec.no_of_periods := 0;
1768
1769 IF p_pa_debug_mode = 'Y' THEN
1770 PA_DEBUG.Reset_Curr_Function;
1771 END IF;
1772 RETURN;
1773 END;
1774
1775 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1776 /* Getting the sum of init qty,raw_cost,burdened_cost,rev
1777 for the 'None' time phase */
1778 BEGIN
1779 -- Bug 4233720 : When the Target version is Revenue with ETC Source of
1780 -- Average of Actuals, we should get that Actual data from the Target
1781 -- budget lines instead of from the PA_FP_FCST_GEN_TMP1 table.
1782
1783 IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE <> 'P' OR
1784 (P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' AND
1785 P_FP_COLS_REC.X_GEN_ETC_SRC_CODE = 'AVERAGE_ACTUALS') THEN
1786 SELECT count(*),
1787 SUM (DECODE(l_rate_based_flag, 'Y',
1788 NVL(init_quantity,0),
1789 DECODE(l_currency_flag,
1790 'PC', NVL(project_init_raw_cost,0),
1791 'TC', NVL(txn_init_raw_cost,0))
1792 )),
1793 SUM (DECODE(l_currency_flag,
1794 'TC',NVL(txn_init_raw_cost,0),
1795 'PC',NVL(project_init_raw_cost,0))),
1796 SUM (DECODE(l_currency_flag,
1797 'TC', NVL(txn_init_burdened_cost,0),
1798 'PC', NVL(project_init_burdened_cost,0))),
1799 SUM (DECODE(l_currency_flag,
1800 'TC', NVL(txn_init_revenue,0),
1801 'PC', NVL(project_init_revenue,0)))
1802 INTO x_txn_amt_rec.no_of_periods,
1803 x_txn_amt_rec.quantity_sum,
1804 x_txn_amt_rec.txn_raw_cost_sum,
1805 x_txn_amt_rec.txn_burdened_cost_sum,
1806 x_txn_amt_rec.txn_revenue_sum
1807 FROM pa_budget_lines
1808 WHERE resource_assignment_id = P_RES_ASG_ID;
1809 ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
1810 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
1811 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1812 count(*),
1813 SUM (DECODE(l_rate_based_flag, 'Y',
1814 NVL(quantity,0),
1815 DECODE(l_currency_flag,
1816 'PC', NVL(prj_raw_cost,0),
1817 'TC', NVL(txn_raw_cost,0))
1818 )),
1819 SUM (DECODE(l_currency_flag,
1820 'TC',NVL(txn_raw_cost,0),
1821 'PC',NVL(prj_raw_cost,0))),
1822 SUM (DECODE(l_currency_flag,
1823 'TC', NVL(txn_brdn_cost,0),
1824 'PC', NVL(prj_brdn_cost,0))),
1825 SUM (DECODE(l_currency_flag,
1826 'TC', NVL(txn_revenue,0),
1827 'PC', NVL(prj_revenue,0)))
1828 INTO x_txn_amt_rec.no_of_periods,
1829 x_txn_amt_rec.quantity_sum,
1830 x_txn_amt_rec.txn_raw_cost_sum,
1831 x_txn_amt_rec.txn_burdened_cost_sum,
1832 x_txn_amt_rec.txn_revenue_sum
1833 FROM PA_FP_FCST_GEN_TMP1
1834 WHERE data_type_code = 'TARGET_FP'
1835 AND project_element_id = P_TASK_ID
1836 AND res_list_member_id = P_RES_LIST_MEMBER_ID
1837 AND (NVL(quantity,0) <> 0
1838 OR NVL(txn_raw_cost,0) <> 0
1839 OR NVL(txn_brdn_cost,0) <> 0
1840 OR NVL(txn_revenue,0) <> 0);
1841 ELSIF P_FP_COLS_REC.x_version_type = 'COST' THEN
1842 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1843 count(*),
1844 SUM (DECODE(l_rate_based_flag, 'Y',
1845 NVL(quantity,0),
1846 DECODE(l_currency_flag,
1847 'PC', NVL(prj_raw_cost,0),
1848 'TC', NVL(txn_raw_cost,0))
1849 )),
1850 SUM (DECODE(l_currency_flag,
1851 'TC',NVL(txn_raw_cost,0),
1852 'PC',NVL(prj_raw_cost,0))),
1853 SUM (DECODE(l_currency_flag,
1854 'TC', NVL(txn_brdn_cost,0),
1855 'PC', NVL(prj_brdn_cost,0))),
1856 SUM (DECODE(l_currency_flag,
1857 'TC', NVL(txn_revenue,0),
1858 'PC', NVL(prj_revenue,0)))
1859 INTO x_txn_amt_rec.no_of_periods,
1860 x_txn_amt_rec.quantity_sum,
1861 x_txn_amt_rec.txn_raw_cost_sum,
1862 x_txn_amt_rec.txn_burdened_cost_sum,
1863 x_txn_amt_rec.txn_revenue_sum
1864 FROM PA_FP_FCST_GEN_TMP1
1865 WHERE data_type_code = 'TARGET_FP'
1866 AND project_element_id = P_TASK_ID
1867 AND res_list_member_id = P_RES_LIST_MEMBER_ID
1868 AND (NVL(quantity,0) <> 0
1869 OR NVL(txn_raw_cost,0) <> 0
1870 OR NVL(txn_brdn_cost,0) <> 0);
1871 ELSIF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1872 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1873 count(*),
1874 SUM (DECODE(l_rate_based_flag, 'Y',
1875 NVL(quantity,0),
1876 DECODE(l_currency_flag,
1877 'PC', NVL(prj_raw_cost,0),
1878 'TC', NVL(txn_raw_cost,0))
1879 )),
1880 SUM (DECODE(l_currency_flag,
1881 'TC',NVL(txn_raw_cost,0),
1882 'PC',NVL(prj_raw_cost,0))),
1883 SUM (DECODE(l_currency_flag,
1884 'TC', NVL(txn_brdn_cost,0),
1885 'PC', NVL(prj_brdn_cost,0))),
1886 SUM (DECODE(l_currency_flag,
1887 'TC', NVL(txn_revenue,0),
1888 'PC', NVL(prj_revenue,0)))
1889 INTO x_txn_amt_rec.no_of_periods,
1890 x_txn_amt_rec.quantity_sum,
1891 x_txn_amt_rec.txn_raw_cost_sum,
1892 x_txn_amt_rec.txn_burdened_cost_sum,
1893 x_txn_amt_rec.txn_revenue_sum
1894 FROM PA_FP_FCST_GEN_TMP1
1895 WHERE data_type_code = 'TARGET_FP'
1896 AND project_element_id = P_TASK_ID
1897 AND res_list_member_id = P_RES_LIST_MEMBER_ID
1898 AND (NVL(quantity,0) <> 0
1899 OR NVL(txn_revenue,0) <> 0);
1900 END IF;
1901 END IF;
1902 EXCEPTION
1903 WHEN NO_DATA_FOUND THEN
1904 x_txn_amt_rec.quantity_sum := 0;
1905 x_txn_amt_rec.txn_raw_cost_sum := 0;
1906 x_txn_amt_rec.txn_burdened_cost_sum := 0;
1907 x_txn_amt_rec.txn_revenue_sum := 0;
1908 x_txn_amt_rec.no_of_periods := 0;
1909
1910 IF p_pa_debug_mode = 'Y' THEN
1911 PA_DEBUG.Reset_Curr_Function;
1912 END IF;
1913 RETURN;
1914
1915 END;
1916 END IF;
1917
1918 IF p_pa_debug_mode = 'Y' THEN
1919 PA_DEBUG.Reset_Curr_Function;
1920 END IF;
1921
1922 EXCEPTION
1923 WHEN NO_DATA_FOUND THEN
1924 x_return_status := FND_API.G_RET_STS_ERROR;
1925 IF P_PA_DEBUG_MODE = 'Y' THEN
1926 pa_fp_gen_amount_utils.fp_debug
1927 (p_msg => 'Invalid Resource assignment Id',
1928 p_module_name => l_module_name,
1929 p_log_level => 5);
1930 PA_DEBUG.Reset_Curr_Function;
1931 END IF;
1932 RAISE;
1933
1934 WHEN OTHERS THEN
1935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1936 x_msg_data := SUBSTR(SQLERRM,1,240);
1937 FND_MSG_PUB.add_exc_msg
1938 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PVT'
1939 ,p_procedure_name => 'GET_ACTUAL_TXN_AMOUNT');
1940 IF P_PA_DEBUG_MODE = 'Y' THEN
1941 pa_fp_gen_amount_utils.fp_debug
1942 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1943 p_module_name => l_module_name,
1944 p_log_level => 5);
1945 PA_DEBUG.Reset_Curr_Function;
1946 END IF;
1947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1948
1949 END GET_ACTUAL_TXN_AMOUNT;
1950
1951 PROCEDURE GEN_AVERAGE_OF_ACTUALS
1952 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1953 P_TASK_ID IN PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE,
1954 P_RES_LIST_MEMBER_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_LIST_MEMBER_ID%TYPE,
1955 P_TXN_CURRENCY_CODE IN PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE,
1956 P_CURRENCY_FLAG IN VARCHAR2,
1957 P_PLANNING_START_DATE IN PA_BUDGET_LINES.START_DATE%TYPE,
1958 P_PLANNING_END_DATE IN PA_BUDGET_LINES.END_DATE%TYPE,
1959 P_ACTUALS_THRU_DATE IN DATE,
1960 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1961 P_ACTUAL_FROM_PERIOD IN PA_PERIODS_ALL.PERIOD_NAME%TYPE,
1962 P_ACTUAL_TO_PERIOD IN PA_PERIODS_ALL.PERIOD_NAME%TYPE,
1963 P_ETC_FROM_PERIOD IN PA_PERIODS_ALL.PERIOD_NAME%TYPE,
1964 P_ETC_TO_PERIOD IN PA_PERIODS_ALL.PERIOD_NAME%TYPE,
1965 P_RESOURCE_ASSIGNMENT_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
1966 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1967 X_MSG_COUNT OUT NOCOPY NUMBER,
1968 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1969
1970 --Cursor used to select the start_date for PA periods
1971 CURSOR pa_start_date_csr(c_period PA_PERIODS_ALL.PERIOD_NAME%TYPE) IS
1972 SELECT start_date
1973 FROM pa_periods_all
1974 WHERE period_name = c_period
1975 AND org_id = p_fp_cols_rec.x_org_id;
1976
1977 --Cursor used to select the start_date for GL periods
1978 CURSOR gl_start_date_csr(c_period PA_PERIODS_ALL.PERIOD_NAME%TYPE) IS
1979 SELECT start_date
1980 FROM gl_period_statuses
1981 WHERE period_name = c_period
1982 AND application_id = PA_PERIOD_PROCESS_PKG.Application_id
1983 AND set_of_books_id = p_fp_cols_rec.x_set_of_books_id
1984 AND adjustment_period_flag = 'N';
1985
1986 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pvt.gen_average_of_actuals';
1987 l_txn_amt_rec PA_FP_GEN_FCST_AMT_PUB.TXN_AMT_REC_TYP;
1988 l_future_no_of_period NUMBER;
1989 l_res_asg_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1990 l_amt_dtls_tbl PA_FP_MAINTAIN_ACTUAL_PUB.l_amt_dtls_tbl_typ;
1991
1992 l_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1993 l_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1994 l_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1995
1996 l_actual_from_date PA_PERIODS_ALL.START_DATE%TYPE;
1997 l_actual_to_date PA_PERIODS_ALL.START_DATE%TYPE;
1998 l_etc_from_date PA_PERIODS_ALL.START_DATE%TYPE;
1999 l_etc_to_date PA_PERIODS_ALL.START_DATE%TYPE;
2000
2001 l_txn_raw_cost_sum PA_BUDGET_LINES.TXN_RAW_COST%TYPE;
2002 l_txn_burdened_cost_sum PA_BUDGET_LINES.TXN_BURDENED_COST%TYPE;
2003 l_txn_revenue_sum PA_BUDGET_LINES.TXN_REVENUE%TYPE;
2004 l_quantity_sum PA_BUDGET_LINES.QUANTITY%TYPE;
2005
2006 l_count NUMBER;
2007 l_msg_count NUMBER;
2008 l_data VARCHAR2(2000);
2009 l_msg_data VARCHAR2(2000);
2010 l_msg_index_out NUMBER;
2011 l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
2012
2013 /* Variables Added for ER 4376722 */
2014 l_billable_flag PA_TASKS.BILLABLE_FLAG%TYPE;
2015
2016 BEGIN
2017 --Setting initial values
2018 X_MSG_COUNT := 0;
2019 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2020
2021 IF p_pa_debug_mode = 'Y' THEN
2022 pa_debug.set_curr_function( p_function => 'GEN_AVERAGE_OF_ACTUALS'
2023 ,p_debug_mode => p_pa_debug_mode);
2024 END IF;
2025
2026 /*Bug 4036127 - l_rate_based_flag needed for rounding the amts. */
2027
2028 IF p_resource_assignment_id is null THEN
2029 SELECT ra.resource_assignment_id,
2030 ra.rate_based_flag,
2031 NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
2032 INTO l_res_asg_id,
2033 l_rate_based_flag,
2034 l_billable_flag /* Added for ER 4376722 */
2035 FROM pa_resource_assignments ra,
2036 pa_tasks ta /* Added for ER 4376722 */
2037 WHERE ra.budget_version_id = p_budget_version_id
2038 AND NVL(ra.task_id,0) = p_task_id
2039 AND ra.resource_list_member_id = p_res_list_member_id
2040 AND NVL(ra.task_id,0) = ta.task_id (+); /* Added for ER 4376722 */
2041 ELSE
2042 l_res_asg_id := p_resource_assignment_id;
2043
2044 SELECT ra.rate_based_flag,
2045 NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
2046 INTO l_rate_based_flag,
2047 l_billable_flag /* Added for ER 4376722 */
2048 FROM pa_resource_assignments ra,
2049 pa_tasks ta /* Added for ER 4376722 */
2050 WHERE ra.resource_assignment_id = l_res_asg_id
2051 AND NVL(ra.task_id,0) = ta.task_id (+); /* Added for ER 4376722 */
2052 END IF;
2053
2054 --hr_utility.trace('l_rate_based_flag : '||l_rate_based_flag);
2055
2056
2057 /* ER 4376722: When the Target is a Revenue-only version, we do not
2058 * generate quantity or amounts for non-billable, non-rate-based tasks.
2059 * For Average of Actuals, we can RETURN to avoid generating amounts. */
2060
2061 IF l_billable_flag = 'N' AND
2062 l_rate_based_flag = 'N' AND
2063 p_fp_cols_rec.x_version_type = 'REVENUE' THEN
2064 IF p_pa_debug_mode = 'Y' THEN
2065 PA_DEBUG.Reset_Curr_Function;
2066 END IF;
2067 RETURN;
2068 END IF; -- ER 4376722 billability logic for REVENUE versions
2069
2070
2071 /* Getting the start_date for given actual period based on time phase code */
2072 IF p_fp_cols_rec.x_time_phased_code = 'P' THEN
2073 /* Getting the actual_from_date for the given actual_from_period(PA Period) */
2074 OPEN pa_start_date_csr(p_actual_from_period);
2075 FETCH pa_start_date_csr
2076 INTO l_actual_from_date;
2077 CLOSE pa_start_date_csr;
2078 /* Getting the actual_to_date for the given actual_to_period(PA Period) */
2079 OPEN pa_start_date_csr(p_actual_to_period);
2080 FETCH pa_start_date_csr
2081 INTO l_actual_to_date;
2082 CLOSE pa_start_date_csr;
2083 /* Getting the etc_from_date for the given etc_from_period(PA Period) */
2084 OPEN pa_start_date_csr(p_etc_from_period);
2085 FETCH pa_start_date_csr
2086 INTO l_etc_from_date;
2087 CLOSE pa_start_date_csr;
2088 /* Getting the etc_to_date for the given etc_to_period(PA Period) */
2089 OPEN pa_start_date_csr(p_etc_to_period);
2090 FETCH pa_start_date_csr
2091 INTO l_etc_to_date;
2092 CLOSE pa_start_date_csr;
2093
2094 ELSIF p_fp_cols_rec.x_time_phased_code = 'G' THEN
2095 /* Getting the actual_from_date for the given actual_from_period(GL Period) */
2096 OPEN gl_start_date_csr(p_actual_from_period);
2097 FETCH gl_start_date_csr
2098 INTO l_actual_from_date;
2099 CLOSE gl_start_date_csr;
2100 /* Getting the actual_to_date for the given actual_to_period(GL Period) */
2101 OPEN gl_start_date_csr(p_actual_to_period);
2102 FETCH gl_start_date_csr
2103 INTO l_actual_to_date;
2104 CLOSE gl_start_date_csr;
2105 /* Getting the etc_from_date for the given etc_from_period(GL Period) */
2106 OPEN gl_start_date_csr(p_etc_from_period);
2107 FETCH gl_start_date_csr
2108 INTO l_etc_from_date;
2109 CLOSE gl_start_date_csr;
2110 /* Getting the etc_to_date for the given etc_to_period(GL Period) */
2111 OPEN gl_start_date_csr(p_etc_to_period);
2112 FETCH gl_start_date_csr
2113 INTO l_etc_to_date;
2114 CLOSE gl_start_date_csr;
2115 END IF;
2116
2117 /* Calling the get actual txn amt api to get
2118 the sum of init qty,cost and rev for the given period */
2119 IF p_pa_debug_mode = 'Y' THEN
2120 pa_fp_gen_amount_utils.fp_debug
2121 (p_msg => 'Before calling
2122 pa_fp_gen_fcst_amt_pvt.get_actual_txn_amount:'
2123 ||'P_RES_ASG_ID:'||l_res_asg_id
2124 ||';P_ACTUAL_FROM_DATE:'||l_actual_from_date
2125 ||';P_ACTUAL_TO_DATE:'||l_actual_to_date
2126 ||';P_CURRENCY_FLAG:'||P_CURRENCY_FLAG,
2127 p_module_name => l_module_name,
2128 p_log_level => 5);
2129 END IF;
2130 PA_FP_GEN_FCST_AMT_PVT.GET_ACTUAL_TXN_AMOUNT
2131 (P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2132 P_TASK_ID => P_TASK_ID,
2133 P_RES_LIST_MEMBER_ID => P_RES_LIST_MEMBER_ID,
2134 P_RES_ASG_ID => l_res_asg_id,
2135 P_TXN_CURRENCY_CODE => P_TXN_CURRENCY_CODE,
2136 P_CURRENCY_FLAG => P_CURRENCY_FLAG,
2137 P_FP_COLS_REC => P_FP_COLS_REC,
2138 P_ACTUAL_FROM_DATE => l_actual_from_date,
2139 P_ACTUAL_TO_DATE => l_actual_to_date,
2140 X_TXN_AMT_REC => l_txn_amt_rec,
2141 X_RETURN_STATUS => X_RETURN_STATUS,
2142 X_MSG_COUNT => X_MSG_COUNT,
2143 X_MSG_DATA => X_MSG_DATA);
2144 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2145 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2146 END IF;
2147 IF p_pa_debug_mode = 'Y' THEN
2148 pa_fp_gen_amount_utils.fp_debug
2149 (p_msg => 'Status after calling
2150 pa_fp_gen_fcst_amt_pvt.get_actual_txn_amount: '
2151 ||x_return_status,
2152 p_module_name => l_module_name,
2153 p_log_level => 5);
2154 END IF;
2155 --dbms_output.put_line('Status of get_actual_txn_amount api: '||X_RETURN_STATUS);
2156
2157 /* Getting the period_name, start_date, end_date
2158 between the etc_from_date and least(p_planning_end_date,l_etc_to_date)
2159 based on the time phase code*/
2160 IF p_fp_cols_rec.x_time_phased_code ='P' THEN
2161 SELECT period_name, start_date, end_date
2162 BULK COLLECT
2163 INTO l_period_name_tab, l_start_date_tab, l_end_date_tab
2164 FROM pa_periods_all
2165 WHERE org_id = p_fp_cols_rec.x_org_id
2166 AND start_date >= l_etc_from_date
2167 AND start_date <= least(p_planning_end_date,l_etc_to_date);
2168 ELSIF p_fp_cols_rec.x_time_phased_code ='G' THEN
2169 SELECT period_name, start_date, end_date
2170 BULK COLLECT
2171 INTO l_period_name_tab, l_start_date_tab, l_end_date_tab
2172 FROM gl_period_statuses
2173 WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
2174 AND set_of_books_id = p_fp_cols_rec.x_set_of_books_id
2175 AND adjustment_period_flag = 'N'
2176 AND start_date >= l_etc_from_date
2177 AND start_date <= least(p_planning_end_date,l_etc_to_date);
2178 END IF;
2179 IF l_period_name_tab.count = 0 THEN
2180 IF p_pa_debug_mode = 'Y' THEN
2181 PA_DEBUG.Reset_Curr_Function;
2182 END IF;
2183 RETURN;
2184 END IF;
2185
2186 IF l_txn_amt_rec.no_of_periods > 0 THEN
2187 l_txn_raw_cost_sum := (l_txn_amt_rec.txn_raw_cost_sum/l_txn_amt_rec.no_of_periods);
2188 l_txn_burdened_cost_sum := (l_txn_amt_rec.txn_burdened_cost_sum/l_txn_amt_rec.no_of_periods);
2189 l_txn_revenue_sum := (l_txn_amt_rec.txn_revenue_sum/l_txn_amt_rec.no_of_periods);
2190 l_quantity_sum := (l_txn_amt_rec.quantity_sum/l_txn_amt_rec.no_of_periods);
2191 END IF;
2192
2193 IF p_pa_debug_mode = 'Y' THEN
2194 pa_fp_gen_amount_utils.fp_debug
2195 (p_msg => '===in average_of_actuals,l_txn_amt_rec.no_of_periods:'
2196 ||l_txn_amt_rec.no_of_periods||';l_txn_amt_rec.txn_raw_cost_sum:'
2197 ||l_txn_amt_rec.txn_raw_cost_sum||';l_txn_amt_rec.txn_burdened_cost_sum:'
2198 ||l_txn_amt_rec.txn_burdened_cost_sum||';l_txn_amt_rec.txn_revenue_sum:'
2199 ||l_txn_amt_rec.txn_revenue_sum||';l_txn_amt_rec.quantity_sum:'
2200 ||l_txn_amt_rec.quantity_sum,
2201 p_module_name => l_module_name,
2202 p_log_level => 5);
2203 END IF;
2204
2205 IF l_txn_raw_cost_sum = 0 AND
2206 l_txn_burdened_cost_sum = 0 AND
2207 l_txn_revenue_sum = 0 AND
2208 l_quantity_sum = 0 THEN
2209 IF p_pa_debug_mode = 'Y' THEN
2210 PA_DEBUG.Reset_Curr_Function;
2211 END IF;
2212 RETURN;
2213 END IF;
2214 /*Bug 4036127 - Rounded all the amts */
2215
2216 IF l_rate_based_flag = 'Y' THEN
2217 l_quantity_sum := pa_fin_plan_utils2.round_quantity
2218 (p_quantity => l_quantity_sum);
2219 ELSE
2220 l_quantity_sum := pa_currency.round_trans_currency_amt1
2221 (x_amount => l_quantity_sum,
2222 x_curr_Code => p_txn_currency_code);
2223 END IF;
2224 l_txn_raw_cost_sum := pa_currency.round_trans_currency_amt1
2225 (x_amount => l_txn_raw_cost_sum,
2226 x_curr_Code => p_txn_currency_code);
2227 l_txn_burdened_cost_sum := pa_currency.round_trans_currency_amt1
2228 (x_amount => l_txn_burdened_cost_sum,
2229 x_curr_Code => p_txn_currency_code);
2230
2231
2232 /* ER 4376722: When the Target is a Cost and Revenue together
2233 * version, we do not generate revenue for non-billable tasks.
2234 * To do this, simply null out revenue amounts for non-billable
2235 * tasks. The result is that revenue amounts for non-billable
2236 * tasks will not be written to the budget lines. */
2237 IF l_billable_flag = 'N' AND
2238 p_fp_cols_rec.x_version_type = 'ALL' THEN
2239
2240 l_txn_revenue_sum := NULL;
2241
2242 /* ER 4376722: When the Target is a Revenue-only version, we generate
2243 * quantity but not revenue for non-billable, rate-based tasks. */
2244 ELSIF l_billable_flag = 'N' AND
2245 l_rate_based_flag = 'Y' AND
2246 p_fp_cols_rec.x_version_type = 'REVENUE' THEN
2247
2248 l_txn_revenue_sum := NULL;
2249
2250 ELSE
2251 l_txn_revenue_sum := pa_currency.round_trans_currency_amt1
2252 (x_amount => l_txn_revenue_sum,
2253 x_curr_Code => p_txn_currency_code);
2254 END IF; -- ER 4376722 billability logic for REVENUE versions
2255
2256
2257 FOR j IN 1..l_period_name_tab.count LOOP
2258 l_amt_dtls_tbl(j).period_name := l_period_name_tab(j);
2259 l_amt_dtls_tbl(j).start_date := l_start_date_tab(j);
2260 l_amt_dtls_tbl(j).end_date := l_end_date_tab(j);
2261 l_amt_dtls_tbl(j).txn_raw_cost := l_txn_raw_cost_sum;
2262 l_amt_dtls_tbl(j).txn_burdened_cost:= l_txn_burdened_cost_sum;
2263 l_amt_dtls_tbl(j).txn_revenue := l_txn_revenue_sum;
2264 l_amt_dtls_tbl(j).quantity := l_quantity_sum;
2265 END LOOP;
2266
2267
2268 --Calling the maintain actual amt ra api
2269 IF p_pa_debug_mode = 'Y' THEN
2270 pa_fp_gen_amount_utils.fp_debug
2271 (p_msg => 'Before calling
2272 pa_fp_maintain_actual_pub.maintain_actual_amt_ra',
2273 p_module_name => l_module_name,
2274 p_log_level => 5);
2275 END IF;
2276 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_RA
2277 (P_PROJECT_ID => p_fp_cols_rec.x_project_id,
2278 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2279 P_RESOURCE_ASSIGNMENT_ID => l_res_asg_id,
2280 P_TXN_CURRENCY_CODE => P_TXN_CURRENCY_CODE,
2281 P_AMT_DTLS_REC_TAB => l_amt_dtls_tbl,
2282 P_CALLING_CONTEXT => 'FP_GEN_FCST_COPY_ACTUAL',
2283 P_TXN_AMT_TYPE_CODE => 'PLANNING_TXN',
2284 X_RETURN_STATUS => X_RETURN_STATUS,
2285 X_MSG_COUNT => X_MSG_COUNT,
2286 X_MSG_DATA => X_MSG_DATA);
2287 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2288 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2289 END IF;
2290 IF p_pa_debug_mode = 'Y' THEN
2291 pa_fp_gen_amount_utils.fp_debug
2292 (p_msg => 'Status after calling
2293 pa_fp_maintain_actual_pub.maintain_actual_amt_ra: '
2294 ||x_return_status,
2295 p_module_name => l_module_name,
2296 p_log_level => 5);
2297 END IF;
2298 --dbms_output.put_line('Status of maintain_actual_amt_ra api: '||X_RETURN_STATUS);
2299
2300 IF P_PA_DEBUG_MODE = 'Y' THEN
2301 PA_DEBUG.Reset_Curr_Function;
2302 END IF;
2303 EXCEPTION
2304 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2305 l_msg_count := FND_MSG_PUB.count_msg;
2306 IF l_msg_count = 1 THEN
2307 PA_INTERFACE_UTILS_PUB.get_messages
2308 ( p_encoded => FND_API.G_TRUE,
2309 p_msg_index => 1,
2310 p_msg_count => l_msg_count,
2311 p_msg_data => l_msg_data,
2312 p_data => l_data,
2313 p_msg_index_out => l_msg_index_out);
2314 x_msg_data := l_data;
2315 x_msg_count := l_msg_count;
2316 ELSE
2317 x_msg_count := l_msg_count;
2318 END IF;
2319 ROLLBACK;
2320
2321 x_return_status := FND_API.G_RET_STS_ERROR;
2322 IF P_PA_DEBUG_MODE = 'Y' THEN
2323 pa_fp_gen_amount_utils.fp_debug
2324 (p_msg => 'Invalid Arguments Passed',
2325 p_module_name => l_module_name,
2326 p_log_level => 5);
2327 PA_DEBUG.Reset_Curr_Function;
2328 END IF;
2329 RAISE;
2330 WHEN OTHERS THEN
2331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2332 x_msg_data := SUBSTR(SQLERRM,1,240);
2333 FND_MSG_PUB.add_exc_msg
2334 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PVT'
2335 ,p_procedure_name => 'GEN_AVERAGE_OF_ACTUALS');
2336 IF P_PA_DEBUG_MODE = 'Y' THEN
2337 pa_fp_gen_amount_utils.fp_debug
2338 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2339 p_module_name => l_module_name,
2340 p_log_level => 5);
2341 PA_DEBUG.Reset_Curr_Function;
2342 END IF;
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344
2345 END GEN_AVERAGE_OF_ACTUALS;
2346
2347 /**The following API is to address bug 4145383.
2348 *Scenario: Other sources (Actual or commitment etc): Rate-based;
2349 * Generated amount from main source: Non rate-based
2350 *Strategy: Only take amount from other sources. Need to convert
2351 * the rate-based flag of the actual to be non rate-based.
2352 *Implementation: parse pa_fp_calc_amt_tmp1 table to check each target
2353 * resource assignment,for each rate based target res asg,
2354 * as long as there exists one source res asg with rate
2355 * based flag of 'N', target res asg will be updated to
2356 * non rate based. And for this target res asg, all existing
2357 * budget lines will be updated accordingly. **/
2358 PROCEDURE UPD_TGT_RATE_BASED_FLAG
2359 (P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2360 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2361 X_MSG_COUNT OUT NOCOPY NUMBER,
2362 X_MSG_DATA OUT NOCOPY VARCHAR2)
2363 IS
2364 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PVT.upd_tgt_rate_based_flag';
2365
2366 l_tgt_res_asg_tab pa_plsql_datatypes.IdTabTyp;
2367 l_budget_line_id_tab pa_plsql_datatypes.IdTabTyp;
2368 l_init_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
2369 l_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
2370 l_init_rev_tab pa_plsql_datatypes.NumTabTyp;
2371 l_rev_tab pa_plsql_datatypes.NumTabTyp;
2372
2373 l_msg_count NUMBER;
2374 l_msg_data VARCHAR2(2000);
2375 l_data VARCHAR2(2000);
2376 l_msg_index_out NUMBER:=0;
2377
2378 l_etc_start_date DATE;
2379 l_bv_id NUMBER;
2380 BEGIN
2381 IF p_pa_debug_mode = 'Y' THEN
2382 pa_debug.set_curr_function( p_function => 'UPD_TGT_RATE_BASED_FLAG',
2383 p_debug_mode => p_pa_debug_mode);
2384 END IF;
2385 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2386
2387 -- Bug 4170419 : Start
2388 --SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2389 -- DISTINCT target_res_asg_id
2390 --BULK COLLECT
2391 --INTO l_tgt_res_asg_tab
2392 --FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
2393 --WHERE tmp.target_res_asg_id = ra.resource_assignment_id
2394 -- AND ra.rate_based_flag = 'Y'
2395 -- AND tmp.rate_based_flag = 'N';
2396 l_bv_id := p_fp_cols_rec.x_budget_version_id;
2397
2398 IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
2399
2400 -- SQL Repository Bug 4884824; SQL ID 14902397
2401 -- Fixed Full Index Scan violation by replacing
2402 -- existing hint with leading hint.
2403 SELECT /*+ LEADING(tmp) */
2404 DISTINCT target_res_asg_id
2405 BULK COLLECT
2406 INTO l_tgt_res_asg_tab
2407 FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
2408 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
2409 AND ra.rate_based_flag = 'Y'
2410 AND tmp.rate_based_flag = 'N';
2411
2412 ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
2413 If p_fp_cols_rec.x_time_phased_code IN ('P','G') then
2414 l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.get_etc_start_date
2415 ( p_fp_cols_rec.x_budget_version_id );
2416
2417 -- SQL Repository Bug 4884824; SQL ID 14902422
2418 -- Fixed Full Index Scan violation by replacing
2419 -- existing hint with leading hint.
2420 SELECT /*+ LEADING(tmp) */
2421 DISTINCT target_res_asg_id
2422 BULK COLLECT
2423 INTO l_tgt_res_asg_tab
2424 FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
2425 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
2426 AND ra.rate_based_flag = 'Y'
2427 AND tmp.rate_based_flag = 'N'
2428 AND ( ra.transaction_source_code is not null
2429 OR
2430 (ra.transaction_source_code is null and NOT exists
2431 (select 1
2432 from pa_budget_lines pbl
2433 where pbl.resource_assignment_id = ra.resource_assignment_id
2434 and pbl.start_date >= l_etc_start_date
2435 )
2436 )
2437 );
2438 Else
2439 -- SQL Repository Bug 4884824; SQL ID 14902440
2440 -- Fixed Full Index Scan violation by replacing
2441 -- existing hint with leading hint.
2442 SELECT /*+ LEADING(tmp) */
2443 DISTINCT target_res_asg_id
2444 BULK COLLECT
2445 INTO l_tgt_res_asg_tab
2446 FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
2447 WHERE tmp.target_res_asg_id = ra.resource_assignment_id
2448 AND ra.rate_based_flag = 'Y'
2449 AND tmp.rate_based_flag = 'N'
2450 AND ( ra.transaction_source_code is not null
2451 OR
2452 (ra.transaction_source_code is null and NOT exists
2453 (select 1
2454 from pa_budget_lines pbl
2455 where pbl.resource_assignment_id = ra.resource_assignment_id
2456 and NVL(pbl.quantity,0) <> NVL(pbl.init_quantity,0)
2457 )
2458 )
2459 );
2460 End If;
2461 -- Bug 4170419 : End
2462 END IF; -- manual lines check
2463
2464 IF l_tgt_res_asg_tab.count = 0 THEN
2465 IF p_pa_debug_mode = 'Y' THEN
2466 PA_DEBUG.Reset_Curr_Function;
2467 END IF;
2468 RETURN;
2469 END IF;
2470
2471 FORALL i IN 1..l_tgt_res_asg_tab.count
2472 UPDATE pa_resource_assignments
2473 SET rate_based_flag = 'N',
2474 unit_of_measure = 'DOLLARS'
2475 WHERE resource_assignment_id = l_tgt_res_asg_tab(i);
2476
2477 DELETE FROM pa_res_list_map_tmp1;
2478 FORALL i IN 1..l_tgt_res_asg_tab.count
2479 INSERT INTO pa_res_list_map_tmp1(txn_resource_assignment_id)
2480 VALUES (l_tgt_res_asg_tab(i));
2481
2482 /* bl.bv id check added for perf bug 4183364 */
2483
2484 SELECT bl.budget_line_id,
2485 bl.txn_init_raw_cost,
2486 bl.txn_raw_cost,
2487 bl.txn_init_revenue,
2488 bl.txn_revenue
2489 BULK COLLECT
2490 INTO l_budget_line_id_tab,
2491 l_init_raw_cost_tab,
2492 l_raw_cost_tab,
2493 l_init_rev_tab,
2494 l_rev_tab
2495 FROM pa_budget_lines bl, pa_res_list_map_tmp1 tmp
2496 WHERE bl.budget_version_id = l_bv_id AND
2497 tmp.txn_resource_assignment_id = bl.resource_assignment_id;
2498
2499 IF l_budget_line_id_tab.count = 0 THEN
2500 IF p_pa_debug_mode = 'Y' THEN
2501 PA_DEBUG.Reset_Curr_Function;
2502 END IF;
2503 RETURN;
2504 END IF;
2505
2506 IF P_FP_COLS_REC.X_VERSION_TYPE = 'REVENUE' THEN
2507 FORALL i IN 1..l_budget_line_id_tab.count
2508 UPDATE pa_budget_lines
2509 SET init_quantity = l_init_rev_tab(i),
2510 quantity = l_rev_tab(i)
2511 WHERE budget_line_id = l_budget_line_id_tab(i);
2512 ELSE
2513 FORALL i IN 1..l_budget_line_id_tab.count
2514 UPDATE pa_budget_lines
2515 SET init_quantity = l_init_raw_cost_tab(i),
2516 quantity = l_raw_cost_tab(i)
2517 WHERE budget_line_id = l_budget_line_id_tab(i);
2518 END IF;
2519
2520 IF p_pa_debug_mode = 'Y' THEN
2521 PA_DEBUG.Reset_Curr_Function;
2522 END IF;
2523 EXCEPTION
2524 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2525 l_msg_count := FND_MSG_PUB.count_msg;
2526 IF l_msg_count = 1 THEN
2527 PA_INTERFACE_UTILS_PUB.get_messages
2528 (p_encoded => FND_API.G_TRUE
2529 ,p_msg_index => 1
2530 ,p_msg_count => l_msg_count
2531 ,p_msg_data => l_msg_data
2532 ,p_data => l_data
2533 ,p_msg_index_out => l_msg_index_out);
2534 x_msg_data := l_data;
2535 x_msg_count := l_msg_count;
2536 ELSE
2537 x_msg_count := l_msg_count;
2538 END IF;
2539 ROLLBACK;
2540
2541 x_return_status := FND_API.G_RET_STS_ERROR;
2542 IF P_PA_DEBUG_MODE = 'Y' THEN
2543 pa_debug.write_log(
2544 x_module => l_module_name,
2545 x_msg => 'Invalid Arguments Passed',
2546 x_log_level => 5);
2547 PA_DEBUG.Reset_Curr_Function;
2548 END IF;
2549 RAISE;
2550
2551 WHEN OTHERS THEN
2552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2553 x_msg_data := SUBSTR(SQLERRM,1,240);
2554 FND_MSG_PUB.add_exc_msg
2555 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PVT'
2556 ,p_procedure_name => 'UPD_TGT_RATE_BASED_FLAG');
2557 IF P_PA_DEBUG_MODE = 'Y' THEN
2558 pa_debug.write_log(
2559 x_module => l_module_name,
2560 x_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2561 x_log_level => 5);
2562 PA_DEBUG.Reset_Curr_Function;
2563 END IF;
2564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2565 END UPD_TGT_RATE_BASED_FLAG;
2566
2567 END PA_FP_GEN_FCST_AMT_PVT;