[Home] [Help]
845: UPDATE pji_fm_xbs_accum_tmp1
846: SET res_list_member_id = l_rlm_id;
847: END IF;
848:
849: INSERT INTO PA_FP_FCST_GEN_TMP1 (
850: PROJECT_ID,
851: STRUCT_VERSION_ID,
852: PROJECT_ELEMENT_ID,
853: CALENDAR_TYPE,
1040:
1041: -- Bug 4040832, 3970800: Modified project-level cursor to get
1042: -- actuals data from PJI table instead of target budget lines.
1043: CURSOR get_res_asg_cur_proj IS
1044: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1045: DISTINCT ra.resource_assignment_id,
1046: tmp.res_list_member_id,
1047: tmp.cbs_element_id, --bug#16791711
1048: ra.planning_start_date,
1047: tmp.cbs_element_id, --bug#16791711
1048: ra.planning_start_date,
1049: ra.planning_end_date,
1050: tmp.project_element_id
1051: FROM PA_FP_FCST_GEN_TMP1 tmp,
1052: pa_resource_assignments ra
1053: WHERE tmp.project_element_id = p_task_id AND
1054: ra.budget_version_id = P_BUDGET_VERSION_ID AND
1055: NVL(ra.task_id,0) = 0 AND
1136: AND NVL(quantity,0) <> NVL(init_quantity,0)
1137: AND rownum < 2 )));
1138:
1139: CURSOR get_res_asg_cur_proj_ml IS
1140: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1141: DISTINCT ra.resource_assignment_id,
1142: tmp.res_list_member_id,
1143: tmp.cbs_element_id, --bug#16791711
1144: ra.planning_start_date,
1143: tmp.cbs_element_id, --bug#16791711
1144: ra.planning_start_date,
1145: ra.planning_end_date,
1146: tmp.project_element_id
1147: FROM PA_FP_FCST_GEN_TMP1 tmp,
1148: pa_resource_assignments ra
1149: WHERE tmp.project_element_id = p_task_id AND
1150: ra.budget_version_id = P_BUDGET_VERSION_ID AND
1151: NVL(ra.task_id,0) = 0 AND
1160: AND bl.start_date > p_actuals_thru_date
1161: AND rownum < 2 )));
1162:
1163: CURSOR get_res_asg_cur_proj_ml_none IS
1164: SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
1165: DISTINCT ra.resource_assignment_id,
1166: tmp.res_list_member_id,
1167: tmp.cbs_element_id, --bug#16791711
1168: ra.planning_start_date,
1167: tmp.cbs_element_id, --bug#16791711
1168: ra.planning_start_date,
1169: ra.planning_end_date,
1170: tmp.project_element_id
1171: FROM PA_FP_FCST_GEN_TMP1 tmp,
1172: pa_resource_assignments ra
1173: WHERE tmp.project_element_id = p_task_id AND
1174: ra.budget_version_id = P_BUDGET_VERSION_ID AND
1175: NVL(ra.task_id,0) = 0 AND
1574: FROM PA_FP_CALC_AMT_TMP2
1575: WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
1576: AND ETC_CURRENCY_CODE IS NULL;
1577:
1578: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1579: COUNT(DISTINCT TXN_CURRENCY_CODE)
1580: INTO l_currency_count_act
1581: FROM PA_FP_FCST_GEN_TMP1
1582: WHERE project_element_id = P_TASK_ID
1577:
1578: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1579: COUNT(DISTINCT TXN_CURRENCY_CODE)
1580: INTO l_currency_count_act
1581: FROM PA_FP_FCST_GEN_TMP1
1582: WHERE project_element_id = P_TASK_ID
1583: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
1584: AND data_type_code = P_ETC_SOURCE_CODE;
1585: /* hr_utility.trace('currency count tot :'||l_currency_count_tot );
1600: FROM PA_FP_CALC_AMT_TMP2
1601: WHERE RESOURCE_ASSIGNMENT_ID = P_RESOURCE_ASSIGNMENT_ID
1602: AND ETC_CURRENCY_CODE IS NULL;
1603:
1604: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1605: DISTINCT TXN_CURRENCY_CODE
1606: INTO l_currency_code_act
1607: FROM PA_FP_FCST_GEN_TMP1
1608: WHERE project_element_id = P_TASK_ID
1603:
1604: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1605: DISTINCT TXN_CURRENCY_CODE
1606: INTO l_currency_code_act
1607: FROM PA_FP_FCST_GEN_TMP1
1608: WHERE project_element_id = P_TASK_ID
1609: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
1610: AND data_type_code = P_ETC_SOURCE_CODE;
1611:
1626: ELSIF l_currency_count_tot = 0
1627: AND l_currency_count_act = 1
1628: AND p_fp_cols_rec.x_plan_in_multi_curr_flag = 'Y' THEN
1629: l_currency_flag := 'TC';
1630: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1631: DISTINCT TXN_CURRENCY_CODE
1632: INTO l_etc_currency_code
1633: FROM PA_FP_FCST_GEN_TMP1
1634: WHERE project_element_id = P_TASK_ID
1629: l_currency_flag := 'TC';
1630: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1631: DISTINCT TXN_CURRENCY_CODE
1632: INTO l_etc_currency_code
1633: FROM PA_FP_FCST_GEN_TMP1
1634: WHERE project_element_id = P_TASK_ID
1635: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
1636: AND data_type_code = P_ETC_SOURCE_CODE;
1637: END IF;
1680: INTO
1681: l_act_qty,
1682: l_act_txn_raw_cost,
1683: l_act_txn_brdn_cost
1684: FROM /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1685: PA_FP_FCST_GEN_TMP1
1686: WHERE project_element_id = P_TASK_ID
1687: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
1688: AND data_type_code = P_ETC_SOURCE_CODE;
1681: l_act_qty,
1682: l_act_txn_raw_cost,
1683: l_act_txn_brdn_cost
1684: FROM /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
1685: PA_FP_FCST_GEN_TMP1
1686: WHERE project_element_id = P_TASK_ID
1687: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
1688: AND data_type_code = P_ETC_SOURCE_CODE;
1689: EXCEPTION
2324: END IF;
2325:
2326: /*Getting ACWP in both TXN and PROJ currencies*/
2327: IF P_ETC_SOURCE_CODE = 'FINANCIAL_PLAN' THEN
2328: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2329: COUNT(DISTINCT TXN_CURRENCY_CODE),SUM(NVL(quantity,0))
2330: INTO l_txn_currency_count_act,l_act_qty
2331: FROM PA_FP_FCST_GEN_TMP1
2332: WHERE project_element_id = P_TASK_ID
2327: IF P_ETC_SOURCE_CODE = 'FINANCIAL_PLAN' THEN
2328: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2329: COUNT(DISTINCT TXN_CURRENCY_CODE),SUM(NVL(quantity,0))
2330: INTO l_txn_currency_count_act,l_act_qty
2331: FROM PA_FP_FCST_GEN_TMP1
2332: WHERE project_element_id = P_TASK_ID
2333: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
2334: AND data_type_code = P_ETC_SOURCE_CODE;
2335:
2339: ELSIF l_txn_currency_count_act = 1
2340: AND p_fp_cols_rec.x_plan_in_multi_curr_flag = 'Y' THEN
2341: l_act_exist_flag := 'Y';
2342: l_act_txn_pc_flag := 'TC';
2343: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2344: DISTINCT TXN_CURRENCY_CODE
2345: INTO l_txn_currency_code_act
2346: FROM PA_FP_FCST_GEN_TMP1
2347: WHERE project_element_id = P_TASK_ID
2342: l_act_txn_pc_flag := 'TC';
2343: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2344: DISTINCT TXN_CURRENCY_CODE
2345: INTO l_txn_currency_code_act
2346: FROM PA_FP_FCST_GEN_TMP1
2347: WHERE project_element_id = P_TASK_ID
2348: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
2349: AND data_type_code = P_ETC_SOURCE_CODE;
2350:
2348: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
2349: AND data_type_code = P_ETC_SOURCE_CODE;
2350:
2351: BEGIN
2352: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2353: SUM(NVL(quantity,0)),
2354: SUM(NVL(txn_raw_cost,0)),
2355: SUM(NVL(txn_brdn_cost,0)),
2356: SUM(NVL(prj_raw_cost,0)),
2360: l_act_txn_raw_cost,
2361: l_act_txn_brdn_cost,
2362: l_act_pc_raw_cost,
2363: l_act_pc_brdn_cost
2364: FROM PA_FP_FCST_GEN_TMP1
2365: WHERE project_element_id = P_TASK_ID
2366: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
2367: AND data_type_code = P_ETC_SOURCE_CODE;
2368: EXCEPTION
2376: ELSE
2377: l_act_exist_flag := 'Y';
2378: l_act_txn_pc_flag := 'PC';
2379: BEGIN
2380: SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2381: SUM(NVL(quantity,0)),
2382: SUM(NVL(prj_raw_cost,0)),
2383: SUM(NVL(prj_brdn_cost,0))
2384: INTO
2384: INTO
2385: l_act_qty,
2386: l_act_pc_raw_cost,
2387: l_act_pc_brdn_cost
2388: FROM PA_FP_FCST_GEN_TMP1
2389: WHERE project_element_id = P_TASK_ID
2390: AND res_list_member_id = P_RESOURCE_LIST_MEMBER_ID
2391: AND data_type_code = P_ETC_SOURCE_CODE;
2392: EXCEPTION