[Home] [Help]
408: 'Y', pji_tmp.pou_revenue,
409: 'N', pji_tmp.prj_revenue,
410: 'A', pji_tmp.pou_revenue))
411: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
412: pa_resource_assignments ra
413: WHERE c_version_type = 'ALL'
414: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
415: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
416: (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
462: 'Y', pji_tmp.pou_revenue,
463: 'N', pji_tmp.prj_revenue,
464: 'A', pji_tmp.pou_revenue))
465: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
466: pa_resource_assignments ra
467: WHERE c_version_type = 'COST'
468: AND (
469: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
470: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
514: 'Y', pji_tmp.pou_revenue,
515: 'N', pji_tmp.prj_revenue,
516: 'A', pji_tmp.pou_revenue))
517: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
518: pa_resource_assignments ra
519: WHERE c_version_type = 'REVENUE'
520: AND (
521: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
522: (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
773: RES_LIST_MEMBER_ID,
774: NULL,
775: CBS_ELEMENT_ID --bug#16791711
776: FROM PJI_FM_XBS_ACCUM_TMP1);
777: -- select count(*) into l_count from pa_resource_assignments where
778: -- budget_version_id = p_budget_version_id;
779: --dbms_output.put_line('before calling cre res asg api: res_assign has: '||l_count);
780: IF P_PA_DEBUG_MODE = 'Y' THEN
781: pa_fp_gen_amount_utils.fp_debug
863: FROM pji_fm_xbs_accum_tmp1;
864:
865: -- Bug 4170419: Start
866: -- FORALL k IN 1..l_res_asg_id_tmp_tab.count
867: -- UPDATE pa_resource_assignments ra
868: -- SET ra.unit_of_measure = 'DOLLARS',
869: -- ra.rate_based_flag = 'N'
870: -- WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k);
871:
872: If p_fp_cols_rec.x_time_phased_code IN ('P','G') then
873: l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.get_etc_start_date(p_fp_cols_rec.x_budget_version_id);
874:
875: FORALL k IN 1..l_res_asg_id_tmp_tab.count
876: UPDATE pa_resource_assignments ra
877: SET ra.unit_of_measure = 'DOLLARS',
878: ra.rate_based_flag = 'N'
879: WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
880: AND ( ra.transaction_source_code is not null
889: );
890:
891: Else
892: FORALL k IN 1..l_res_asg_id_tmp_tab.count
893: UPDATE pa_resource_assignments ra
894: SET ra.unit_of_measure = 'DOLLARS',
895: ra.rate_based_flag = 'N'
896: WHERE ra.resource_assignment_id = l_res_asg_id_tmp_tab(k)
897: AND ( ra.transaction_source_code is not null
990: --dbms_output.put_line('Closing budget_line_cursor_np');
991: END IF;
992:
993: SELECT rate_based_flag into l_rate_based_flag
994: FROM pa_resource_assignments
995: WHERE resource_assignment_id = l_res_asg_id_tab(i);
996: --dbms_output.put_line('l_rate_based_flag: '||l_rate_based_flag);
997: IF l_rate_based_flag = 'N' THEN
998: IF P_FP_COLS_REC.x_version_type = 'REVENUE' THEN
1075: END IF;
1076:
1077: END LOOP;
1078:
1079: /* the planning start date and end date in pa_resource assignments table
1080: * should be synched up with the budget lines after copying the actual
1081: * data for all the planning resources. */
1082: IF p_pa_debug_mode = 'Y' THEN
1083: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1253: FROM PA_FP_PLANNING_RES_TMP1 tmp1
1254: WHERE nvl(tmp1.task_id,0) = 0
1255: AND NOT EXISTS (
1256: SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N2)*/ 1
1257: FROM pa_resource_assignments ra
1258: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1259: AND NVL(ra.task_id,0) = 0
1260: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1261: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1282: pa_tasks task
1283: WHERE nvl(tmp1.task_id,0) > 0
1284: AND tmp1.task_id = task.task_id
1285: AND NOT EXISTS (
1286: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1287: FROM pa_resource_assignments ra
1288: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1289: AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1290: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1283: WHERE nvl(tmp1.task_id,0) > 0
1284: AND tmp1.task_id = task.task_id
1285: AND NOT EXISTS (
1286: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1287: FROM pa_resource_assignments ra
1288: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1289: AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1290: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1291: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1303: NVL(c_gen_etc_source_code, NULL)
1304: FROM PA_FP_PLANNING_RES_TMP1 tmp1
1305: WHERE nvl(tmp1.task_id,0) = 0
1306: AND NOT EXISTS (
1307: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1308: FROM pa_resource_assignments ra
1309: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1310: AND NVL(ra.task_id,0) = 0
1311: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1304: FROM PA_FP_PLANNING_RES_TMP1 tmp1
1305: WHERE nvl(tmp1.task_id,0) = 0
1306: AND NOT EXISTS (
1307: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1308: FROM pa_resource_assignments ra
1309: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1310: AND NVL(ra.task_id,0) = 0
1311: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1312: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1334: WHERE nvl(tmp1.task_id,0) > 0
1335: AND tmp1.task_id = task.task_id
1336: AND task.top_task_id = task_t.task_id
1337: AND NOT EXISTS (
1338: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1339: FROM pa_resource_assignments ra
1340: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1341: AND nvl(ra.task_id,0) = task_t.task_id
1342: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1335: AND tmp1.task_id = task.task_id
1336: AND task.top_task_id = task_t.task_id
1337: AND NOT EXISTS (
1338: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1339: FROM pa_resource_assignments ra
1340: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1341: AND nvl(ra.task_id,0) = task_t.task_id
1342: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1343: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1361: NVL(c_gen_etc_source_code, NULL)
1362: FROM PA_FP_PLANNING_RES_TMP1 tmp1
1363: WHERE nvl(tmp1.task_id,0) = 0
1364: AND NOT EXISTS (
1365: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1366: FROM pa_resource_assignments ra
1367: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1368: AND NVL(ra.task_id,0) = 0
1369: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1362: FROM PA_FP_PLANNING_RES_TMP1 tmp1
1363: WHERE nvl(tmp1.task_id,0) = 0
1364: AND NOT EXISTS (
1365: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1366: FROM pa_resource_assignments ra
1367: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1368: AND NVL(ra.task_id,0) = 0
1369: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1370: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
1397: PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
1398: min(tmp.planning_start_date) AS planning_start_date,
1399: max(tmp.planning_end_date) AS planning_end_date
1400: FROM PA_FP_CALC_AMT_TMP1 tmp,
1401: PA_RESOURCE_ASSIGNMENTS PRA
1402: WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
1403: GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) tmp1,
1404: pa_tasks task
1405: WHERE nvl(tmp1.task_id,0) > 0
1404: pa_tasks task
1405: WHERE nvl(tmp1.task_id,0) > 0
1406: AND tmp1.task_id = task.task_id
1407: AND NOT EXISTS (
1408: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1409: FROM pa_resource_assignments ra
1410: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1411: AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1412: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1405: WHERE nvl(tmp1.task_id,0) > 0
1406: AND tmp1.task_id = task.task_id
1407: AND NOT EXISTS (
1408: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1409: FROM pa_resource_assignments ra
1410: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1411: AND nvl(ra.task_id,0) = nvl(tmp1.task_id,0)
1412: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1413: AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID);
1434: PRA.CBS_ELEMENT_ID AS CBS_ELEMENT_ID,
1435: min(tmp.planning_start_date) AS planning_start_date,
1436: max(tmp.planning_end_date) AS planning_end_date
1437: FROM PA_FP_CALC_AMT_TMP1 tmp,
1438: PA_RESOURCE_ASSIGNMENTS PRA
1439: WHERE tmp.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
1440: GROUP BY mapped_fin_task_id,TARGET_RLM_ID,PRA.CBS_ELEMENT_ID) tmp1,
1441: pa_tasks task, pa_tasks task_t
1442: WHERE nvl(tmp1.task_id,0) > 0
1442: WHERE nvl(tmp1.task_id,0) > 0
1443: AND tmp1.task_id = task.task_id
1444: AND task.top_task_id = task_t.task_id
1445: AND NOT EXISTS (
1446: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1447: FROM pa_resource_assignments ra
1448: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1449: AND nvl(ra.task_id,0) = task_t.task_id
1450: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1443: AND tmp1.task_id = task.task_id
1444: AND task.top_task_id = task_t.task_id
1445: AND NOT EXISTS (
1446: SELECT /*+ INDEX(tmp1,PA_RESOURCE_ASSIGNMENTS_U2)*/ 1 -- Changed For Bug10331270
1447: FROM pa_resource_assignments ra
1448: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1449: AND nvl(ra.task_id,0) = task_t.task_id
1450: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1451: AND ra.CBS_ELEMENT_ID = tmp1.CBS_ELEMENT_ID)
1608: RETURN;
1609: END IF;
1610:
1611: FORALL i in l_task_id_tab.first .. l_task_id_tab.last
1612: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
1613: RESOURCE_ASSIGNMENT_ID,
1614: BUDGET_VERSION_ID,
1615: PROJECT_ID,
1616: RESOURCE_LIST_MEMBER_ID,
1627: RESOURCE_ASSIGNMENT_TYPE,
1628: RECORD_VERSION_NUMBER,
1629: TRANSACTION_SOURCE_CODE )
1630: VALUES (
1631: pa_resource_assignments_s.nextval,
1632: p_budget_version_id,
1633: p_project_id,
1634: l_rlm_id_tab(i),
1635: l_cbs_id_tab(i),--bug#16791711
1687: RETURN;
1688: END IF;
1689:
1690: FORALL i in l_task_id_tab.first .. l_task_id_tab.last
1691: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
1692: RESOURCE_ASSIGNMENT_ID,
1693: BUDGET_VERSION_ID,
1694: PROJECT_ID,
1695: RESOURCE_LIST_MEMBER_ID,
1706: RESOURCE_ASSIGNMENT_TYPE,
1707: RECORD_VERSION_NUMBER,
1708: TRANSACTION_SOURCE_CODE )
1709: VALUES (
1710: pa_resource_assignments_s.nextval,
1711: p_budget_version_id,
1712: p_project_id,
1713: l_rlm_id_tab(i),
1714: l_cbs_ele_ids(i),
1754: INTO l_spread_curve_id
1755: FROM pa_spread_curves_b
1756: WHERE spread_curve_code = 'FIXED_DATE';
1757:
1758: UPDATE PA_RESOURCE_ASSIGNMENTS
1759: SET SP_FIXED_DATE = PLANNING_START_DATE
1760: WHERE SP_FIXED_DATE IS NULL
1761: AND SPREAD_CURVE_ID = l_spread_curve_id
1762: AND BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1863:
1864: UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1865: SET resource_assignment_id =
1866: (SELECT resource_assignment_id
1867: FROM pa_resource_assignments ra
1868: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1869: AND ra.project_id = P_PROJECT_ID
1870: AND nvl(ra.task_id,0) = 0
1871: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1876:
1877: UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1878: SET resource_assignment_id =
1879: (SELECT resource_assignment_id
1880: FROM pa_resource_assignments ra
1881: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1882: AND ra.project_id = P_PROJECT_ID
1883: AND ra.task_id = tmp1.task_id
1884: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1889:
1890: UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1891: SET resource_assignment_id =
1892: (SELECT resource_assignment_id
1893: FROM pa_resource_assignments ra
1894: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1895: AND ra.project_id = P_PROJECT_ID
1896: AND nvl(ra.task_id,0) = 0
1897: AND ra.resource_list_member_id = tmp1.resource_list_member_id
1903:
1904: UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1905: SET resource_assignment_id =
1906: (SELECT resource_assignment_id
1907: FROM pa_resource_assignments ra,
1908: pa_tasks t
1909: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1910: AND ra.project_id = P_PROJECT_ID
1911: AND tmp1.task_id = t.task_id
1918:
1919: UPDATE PA_FP_PLANNING_RES_TMP1 tmp1
1920: SET resource_assignment_id =
1921: (SELECT resource_assignment_id
1922: FROM pa_resource_assignments ra
1923: WHERE ra.budget_version_id = P_BUDGET_VERSION_ID
1924: AND ra.project_id = P_PROJECT_ID
1925: AND nvl(ra.task_id,0) = 0
1926: AND ra.resource_list_member_id = tmp1.resource_list_member_id
2060:
2061: -- Cursor to get resource assignment ids for a budget version
2062: CURSOR get_resource_assgn_ids_csr IS
2063: SELECT RESOURCE_ASSIGNMENT_ID
2064: FROM PA_RESOURCE_ASSIGNMENTS
2065: WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
2066: get_resource_assgn_ids_rec get_resource_assgn_ids_csr%rowtype;
2067:
2068: -- Cursor to get budget line information for a resource assignment id
2066: get_resource_assgn_ids_rec get_resource_assgn_ids_csr%rowtype;
2067:
2068: -- Cursor to get budget line information for a resource assignment id
2069: cursor get_period_info_csr
2070: (p_resource_assignment_id pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%type)
2071: is
2072: select tmp.source_id
2073: ,tmp.txn_currency_code
2074: ,tmp.period_name
2627: /* updating the resource assignment id in pji_fm_xbs_accum_tmp1 */
2628: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
2629: SET source_id =
2630: (SELECT resource_assignment_id
2631: FROM pa_resource_assignments ra
2632: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
2633: AND ra.resource_list_member_id = tmp1.res_list_member_id
2634: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
2635: AND ra.budget_version_id = p_budget_version_id);
2841:
2842: END IF;
2843:
2844: SELECT rate_based_flag into l_rate_based_flag
2845: FROM pa_resource_assignments
2846: WHERE resource_assignment_id = l_resource_assgn_id_tab(i);
2847: IF l_rate_based_flag = 'N' THEN
2848: IF l_FP_COLS_REC.x_version_type = 'REVENUE' THEN
2849: l_quantity_tab := l_txn_revenue_tab;
2920:
2921: /*---------------------------------------------------------------
2922: Rollup amounts - Begin
2923: -----------------------------------------------------------------*/
2924: -- ROLLUP PC and PFC numbers to pa_resource_assignments
2925: pa_fp_calc_plan_pkg.rollup_pf_pfc_to_ra
2926: ( p_budget_version_id => p_budget_version_id
2927: ,p_calling_module => 'COLLECT_ACTUALS'
2928: ,x_return_status => x_return_status