[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;
2587: ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
2588: l_currency_flag := 'PC';
2589: ELSIF l_rate_based_flag = 'N' THEN
2590: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2591: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2592: DISTINCT act_tmp.txn_currency_code
2593: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2594: PA_FP_CALC_AMT_TMP1 tot_tmp
2595: WHERE act_tmp.project_element_id = tot_tmp.task_id
2589: ELSIF l_rate_based_flag = 'N' THEN
2590: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2591: SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2592: DISTINCT act_tmp.txn_currency_code
2593: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2594: PA_FP_CALC_AMT_TMP1 tot_tmp
2595: WHERE act_tmp.project_element_id = tot_tmp.task_id
2596: AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
2597: AND nvl(act_tmp.cbs_element_id, -1) = nvl(tot_tmp.cbs_element_id, -1) --bug#16791711
2670: END IF;
2671: END IF;
2672:
2673: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2674: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2675: DECODE(l_currency_flag,
2676: 'PC', NVL(SUM(DECODE(l_rate_based_flag,
2677: 'Y', quantity,
2678: 'N', NVL(prj_raw_cost,0))),0),
2683: 'PC', NVL(SUM(NVL(prj_raw_cost,0)),0),
2684: 'PFC', NVL(SUM(NVL(pou_raw_cost,0)),0))
2685: INTO l_act_quantity_pc_pfc,
2686: l_act_raw_cost_pc_pfc -- Added for Bug 5203622
2687: FROM PA_FP_FCST_GEN_TMP1
2688: WHERE project_element_id = l_curr_task_id
2689: AND res_list_member_id = l_resource_list_member_id
2690: AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
2691: AND data_type_code = 'ETC_FP';
2942: l_tot_revenue_sum := l_tot_revenue_sum + NVL(l_tot_revenue_tab(i),0);
2943: END LOOP;
2944:
2945: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2946: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2947: txn_currency_code,
2948: SUM(NVL(quantity,0)),
2949: SUM(NVL(txn_raw_cost,0)),
2950: SUM(NVL(txn_brdn_cost,0)),
2954: l_act_quantity_tab,
2955: l_act_raw_cost_tab,
2956: l_act_brdn_cost_tab,
2957: l_act_revenue_tab
2958: FROM PA_FP_FCST_GEN_TMP1
2959: WHERE project_element_id = l_curr_task_id
2960: AND res_list_member_id = l_resource_list_member_id
2961: AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
2962: AND data_type_code = 'ETC_FP'
3044:
3045: SELECT COUNT(*)
3046: INTO l_currency_count_act_min_tot
3047: FROM (
3048: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3049: DISTINCT txn_currency_code
3050: FROM PA_FP_FCST_GEN_TMP1
3051: WHERE project_element_id = l_curr_task_id
3052: AND res_list_member_id = l_resource_list_member_id
3046: INTO l_currency_count_act_min_tot
3047: FROM (
3048: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3049: DISTINCT txn_currency_code
3050: FROM PA_FP_FCST_GEN_TMP1
3051: WHERE project_element_id = l_curr_task_id
3052: AND res_list_member_id = l_resource_list_member_id
3053: AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
3054: AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
3341: l_tot_revenue_pc_sum := l_tot_revenue_pc_sum + l_tot_revenue_pc_tab(i);
3342: END LOOP;
3343:
3344: IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
3345: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3346: NVL(SUM( DECODE(l_rate_based_flag,
3347: 'Y', NVL(quantity,0),
3348: 'N', NVL(prj_raw_cost,0))),0),
3349: NVL(SUM(NVL(prj_raw_cost,0)),0)
3348: 'N', NVL(prj_raw_cost,0))),0),
3349: NVL(SUM(NVL(prj_raw_cost,0)),0)
3350: INTO l_act_quantity_pc_sum,
3351: l_act_raw_cost_pc_sum -- Added for Bug 5203622
3352: FROM PA_FP_FCST_GEN_TMP1
3353: WHERE project_element_id = l_curr_task_id
3354: AND res_list_member_id = l_resource_list_member_id
3355: AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
3356: AND data_type_code = 'ETC_FP';