[Home] [Help]
195: ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
196: l_currency_flag := 'PC';
197: ELSIF l_rate_based_flag = 'N' THEN
198: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
199: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
200: DISTINCT act_tmp.txn_currency_code
201: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
202: PA_FP_CALC_AMT_TMP1 tot_tmp
203: WHERE act_tmp.project_element_id = tot_tmp.task_id
197: ELSIF l_rate_based_flag = 'N' THEN
198: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
199: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
200: DISTINCT act_tmp.txn_currency_code
201: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
202: PA_FP_CALC_AMT_TMP1 tot_tmp
203: WHERE act_tmp.project_element_id = tot_tmp.task_id
204: AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
205: AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
250: l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
251: END IF;
252:
253: IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
254: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
255: DECODE(l_currency_flag,
256: 'PC', NVL(SUM(DECODE(l_rate_based_flag,
257: 'Y', quantity,
258: 'N', NVL(prj_raw_cost,0))),0),
259: 'PFC', NVL(SUM(DECODE(l_rate_based_flag,
260: 'Y', quantity,
261: 'N', NVL(pou_raw_cost,0))),0))
262: INTO l_act_quantity_pc_pfc
263: FROM PA_FP_FCST_GEN_TMP1
264: WHERE project_element_id = p_task_id
265: AND res_list_member_id = p_resource_list_member_id
266: AND data_type_code = 'ETC_FP';
267:
466: l_tot_quantity_sum := l_tot_quantity_sum + NVL(l_tot_quantity_tab(i),0);
467: END LOOP;
468:
469: IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
470: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
471: txn_currency_code,
472: SUM(NVL(quantity,0)),
473: SUM(NVL(txn_raw_cost,0)),
474: SUM(NVL(txn_brdn_cost,0)),
478: l_act_quantity_tab,
479: l_act_raw_cost_tab,
480: l_act_brdn_cost_tab,
481: l_act_revenue_tab
482: FROM PA_FP_FCST_GEN_TMP1
483: WHERE project_element_id = p_task_id
484: AND res_list_member_id = p_resource_list_member_id
485: AND data_type_code = 'ETC_FP'
486: GROUP BY txn_currency_code;
564:
565: SELECT COUNT(*)
566: INTO l_currency_count_act_min_tot
567: FROM (
568: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
569: DISTINCT txn_currency_code
570: FROM PA_FP_FCST_GEN_TMP1
571: WHERE project_element_id = p_task_id
572: AND res_list_member_id = p_resource_list_member_id
566: INTO l_currency_count_act_min_tot
567: FROM (
568: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
569: DISTINCT txn_currency_code
570: FROM PA_FP_FCST_GEN_TMP1
571: WHERE project_element_id = p_task_id
572: AND res_list_member_id = p_resource_list_member_id
573: AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
574: 'WORKPLAN_RESOURCES', 'ETC_WP',
784: l_tot_quantity_pc_sum := l_tot_quantity_pc_sum + l_tot_quantity_pc_tab(i);
785: END LOOP;
786:
787: IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
788: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
789: NVL(SUM( DECODE(l_rate_based_flag,
790: 'Y', NVL(quantity,0),
791: 'N', NVL(prj_raw_cost,0))),0)
792: INTO l_act_quantity_pc_sum
789: NVL(SUM( DECODE(l_rate_based_flag,
790: 'Y', NVL(quantity,0),
791: 'N', NVL(prj_raw_cost,0))),0)
792: INTO l_act_quantity_pc_sum
793: FROM PA_FP_FCST_GEN_TMP1
794: WHERE project_element_id = p_task_id
795: AND res_list_member_id = p_resource_list_member_id
796: AND data_type_code = 'ETC_FP';
797:
1135: OR transaction_source_code = 'COMMITMENT')
1136: UNION
1137: SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1138: DISTINCT act_tmp.txn_currency_code
1139: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
1140: PA_FP_CALC_AMT_TMP2 tot_tmp
1141: WHERE act_tmp.source_id = tot_tmp.resource_assignment_id
1142: AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
1143: ) WHERE rownum <= 2;
2584: ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
2585: l_currency_flag := 'PC';
2586: ELSIF l_rate_based_flag = 'N' THEN
2587: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2588: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2589: DISTINCT act_tmp.txn_currency_code
2590: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2591: PA_FP_CALC_AMT_TMP1 tot_tmp
2592: WHERE act_tmp.project_element_id = tot_tmp.task_id
2586: ELSIF l_rate_based_flag = 'N' THEN
2587: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2588: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2589: DISTINCT act_tmp.txn_currency_code
2590: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2591: PA_FP_CALC_AMT_TMP1 tot_tmp
2592: WHERE act_tmp.project_element_id = tot_tmp.task_id
2593: AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
2594: AND tot_tmp.target_res_asg_id = l_tgt_res_asg_id
2666: END IF;
2667: END IF;
2668:
2669: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2670: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2671: DECODE(l_currency_flag,
2672: 'PC', NVL(SUM(DECODE(l_rate_based_flag,
2673: 'Y', quantity,
2674: 'N', NVL(prj_raw_cost,0))),0),
2679: 'PC', NVL(SUM(NVL(prj_raw_cost,0)),0),
2680: 'PFC', NVL(SUM(NVL(pou_raw_cost,0)),0))
2681: INTO l_act_quantity_pc_pfc,
2682: l_act_raw_cost_pc_pfc -- Added for Bug 5203622
2683: FROM PA_FP_FCST_GEN_TMP1
2684: WHERE project_element_id = l_curr_task_id
2685: AND res_list_member_id = l_resource_list_member_id
2686: AND data_type_code = 'ETC_FP';
2687:
2937: l_tot_revenue_sum := l_tot_revenue_sum + NVL(l_tot_revenue_tab(i),0);
2938: END LOOP;
2939:
2940: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2941: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2942: txn_currency_code,
2943: SUM(NVL(quantity,0)),
2944: SUM(NVL(txn_raw_cost,0)),
2945: SUM(NVL(txn_brdn_cost,0)),
2949: l_act_quantity_tab,
2950: l_act_raw_cost_tab,
2951: l_act_brdn_cost_tab,
2952: l_act_revenue_tab
2953: FROM PA_FP_FCST_GEN_TMP1
2954: WHERE project_element_id = l_curr_task_id
2955: AND res_list_member_id = l_resource_list_member_id
2956: AND data_type_code = 'ETC_FP'
2957: GROUP BY txn_currency_code;
3038:
3039: SELECT COUNT(*)
3040: INTO l_currency_count_act_min_tot
3041: FROM (
3042: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3043: DISTINCT txn_currency_code
3044: FROM PA_FP_FCST_GEN_TMP1
3045: WHERE project_element_id = l_curr_task_id
3046: AND res_list_member_id = l_resource_list_member_id
3040: INTO l_currency_count_act_min_tot
3041: FROM (
3042: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3043: DISTINCT txn_currency_code
3044: FROM PA_FP_FCST_GEN_TMP1
3045: WHERE project_element_id = l_curr_task_id
3046: AND res_list_member_id = l_resource_list_member_id
3047: AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
3048: 'WORKPLAN_RESOURCES', 'ETC_WP',
3328: l_tot_revenue_pc_sum := l_tot_revenue_pc_sum + l_tot_revenue_pc_tab(i);
3329: END LOOP;
3330:
3331: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
3332: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3333: NVL(SUM( DECODE(l_rate_based_flag,
3334: 'Y', NVL(quantity,0),
3335: 'N', NVL(prj_raw_cost,0))),0),
3336: NVL(SUM(NVL(prj_raw_cost,0)),0)
3335: 'N', NVL(prj_raw_cost,0))),0),
3336: NVL(SUM(NVL(prj_raw_cost,0)),0)
3337: INTO l_act_quantity_pc_sum,
3338: l_act_raw_cost_pc_sum -- Added for Bug 5203622
3339: FROM PA_FP_FCST_GEN_TMP1
3340: WHERE project_element_id = l_curr_task_id
3341: AND res_list_member_id = l_resource_list_member_id
3342: AND data_type_code = 'ETC_FP';
3343: