[Home] [Help]
165: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
166: always get total plan amounts in PC or TC or PFC (bug fix 4102848) from financial data model.*/
167: l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
168: l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
169: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
170: DECODE(l_currency_flag,
171: 'PC', l_pc_currency_code,
172: 'TC', txn_currency_code,
173: 'PFC',l_pfc_currency_code),
189: l_tot_quantity_tab,
190: l_tot_raw_cost_tab,
191: l_tot_brdn_cost_tab,
192: l_tot_revenue_tab
193: FROM PA_FP_CALC_AMT_TMP2
194: WHERE resource_assignment_id = p_src_res_asg_id
195: AND transaction_source_code = p_etc_source_code
196: GROUP BY DECODE(l_currency_flag, 'PC', l_pc_currency_code,
197: 'TC', txn_currency_code,
210: /*When not taking periodic rates, we need to calculate out the average rates
211: from the source resource assignments that are mapped to the current target
212: resource assignment.*/
213: FOR i IN 1..l_tot_currency_code_tab.count LOOP
214: SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
215: NVL(SUM(NVL(total_plan_quantity,0)),0),
216: NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_raw_cost,0)),
217: 'PC', SUM(NVL(total_pc_raw_cost,0)),
218: 'PFC', SUM(NVL(total_pfc_raw_cost,0))),0),
237: l_pc_rate_revenue,
238: l_pfc_rate_raw_cost,
239: l_pfc_rate_brdn_cost,
240: l_pfc_rate_revenue
241: FROM pa_fp_calc_amt_tmp2
242: WHERE resource_assignment_id = p_src_res_asg_id
243: AND DECODE(l_currency_flag, 'TC', txn_currency_code,
244: 'PC', l_tot_currency_code_tab(i),
245: 'PFC', l_tot_currency_code_tab(i)) = l_tot_currency_code_tab(i)
296: END IF;
297:
298: /* Insert total ETC amounts */
299: FORALL i IN 1..l_tot_currency_code_tab.count
300: INSERT INTO PA_FP_CALC_AMT_TMP2 (
301: RESOURCE_ASSIGNMENT_ID,
302: TARGET_RES_ASG_ID,
303: ETC_CURRENCY_CODE,
304: ETC_PLAN_QUANTITY,
592: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
593: always get total plan amounts in PC or TC or PFC (bug fix 4102848) from financial data model.*/
594: l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
595: l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
596: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
597: DECODE(l_currency_flag,
598: 'PC', l_pc_currency_code,
599: 'TC', txn_currency_code,
600: 'PFC',l_pfc_currency_code),
616: l_tot_quantity_tab,
617: l_tot_raw_cost_tab,
618: l_tot_brdn_cost_tab,
619: l_tot_revenue_tab
620: FROM PA_FP_CALC_AMT_TMP2
621: WHERE resource_assignment_id = p_src_res_asg_id_tab(main_loop)
622: AND transaction_source_code = p_etc_source_code_tab(main_loop)
623: GROUP BY DECODE(l_currency_flag, 'PC', l_pc_currency_code,
624: 'TC', txn_currency_code,
669: /*When not taking periodic rates, we need to calculate out the average rates
670: from the source resource assignments that are mapped to the current target
671: resource assignment.*/
672: FOR i IN 1..l_tot_currency_code_tab.count LOOP
673: SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
674: NVL(SUM(NVL(total_plan_quantity,0)),0),
675: NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_raw_cost,0)),
676: 'PC', SUM(NVL(total_pc_raw_cost,0)),
677: 'PFC', SUM(NVL(total_pfc_raw_cost,0))),0),
696: l_pc_rate_revenue,
697: l_pfc_rate_raw_cost,
698: l_pfc_rate_brdn_cost,
699: l_pfc_rate_revenue
700: FROM pa_fp_calc_amt_tmp2
701: WHERE resource_assignment_id = p_src_res_asg_id_tab(main_loop)
702: AND DECODE(l_currency_flag, 'TC', txn_currency_code,
703: 'PC', l_tot_currency_code_tab(i),
704: 'PFC', l_tot_currency_code_tab(i)) = l_tot_currency_code_tab(i)
793: END IF;
794:
795: /* Insert total ETC amounts */
796: FORALL i IN 1..l_blk_tot_currency_code_tab.count
797: INSERT INTO PA_FP_CALC_AMT_TMP2 (
798: RESOURCE_ASSIGNMENT_ID,
799: TARGET_RES_ASG_ID,
800: ETC_CURRENCY_CODE,
801: ETC_PLAN_QUANTITY,