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