[Home] [Help]
118: ELSE
119: BEGIN
120: SELECT prlm.alias, nvl(ra.task_id,0)
121: INTO l_resource_name, l_task_id
122: FROM pa_resource_list_members prlm, pa_Resource_assignments ra
123: WHERE ra.resource_assignment_id = p_res_asg_id
124: AND ra.resource_list_member_id = prlm.resource_list_member_id;
125: EXCEPTION
126: WHEN OTHERS THEN
437: NVL(V.BILL_TRANS_REV_AMOUNT,0)))
438: FROM PA_EVENTS_DELIVERABLE_V V,
439: PA_EVENT_TYPES ET,
440: PA_RES_LIST_MAP_TMP4 TMP,
441: PA_RESOURCE_ASSIGNMENTS P,
442: PA_PERIODS PAP
443: WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
444: AND V.EVENT_TYPE = ET.EVENT_TYPE
445: AND V.PROJECT_ID = P_PROJECT_ID
466: NVL(V.BILL_TRANS_REV_AMOUNT,0)))
467: FROM PA_EVENTS_DELIVERABLE_V V,
468: PA_EVENT_TYPES ET,
469: PA_RES_LIST_MAP_TMP4 TMP,
470: PA_RESOURCE_ASSIGNMENTS P,
471: GL_PERIOD_STATUSES GLP
472: WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
473: AND V.EVENT_TYPE = ET.EVENT_TYPE
474: AND V.PROJECT_ID = P_PROJECT_ID
498: NVL(V.BILL_TRANS_REV_AMOUNT,0)))
499: FROM PA_EVENTS_DELIVERABLE_V V,
500: PA_EVENT_TYPES ET,
501: PA_RES_LIST_MAP_TMP4 TMP,
502: PA_RESOURCE_ASSIGNMENTS P
503: WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
504: AND V.EVENT_TYPE = ET.EVENT_TYPE
505: AND V.PROJECT_ID = P_PROJECT_ID
506: AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
1518: RETURN;
1519: END IF;
1520:
1521: -- Bug 4297225: As of this bug fix, we no longer join pa_res_list_tmp4
1522: -- with pa_resource_assignments using the Target resource assignment id.
1523: -- However, we still need to call the UPD_TMP4_TXN_RA_ID_AND_ML API to
1524: -- handle the Retain Manually Added Plan Lines logic for the tmp4 table,
1525: -- which is still used when fetching Billing Event amounts downstream.
1526:
1552:
1553: -- Bug 4297225: Billing Events may map to Target resources that have not
1554: -- been created yet. Still, we should include the amounts for such Billing
1555: -- Events in the total amount returned by this API. To this end, the join
1556: -- between pa_resource_assignments with pa_res_list_tmp4 has been commented
1557: -- out in the query below that gets Billing Event amounts.
1558:
1559: SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
1560: V.EVENT_DATE,
1579: l_project_rev_amt_tab
1580: FROM PA_EVENTS_DELIVERABLE_V V,
1581: PA_EVENT_TYPES ET,
1582: PA_RES_LIST_MAP_TMP4 TMP
1583: --,PA_RESOURCE_ASSIGNMENTS RA
1584: WHERE V.PROJECT_ID = P_PROJECT_ID
1585: AND V.EVENT_DATE >= NVL(l_etc_start_date, V.EVENT_DATE)
1586: AND V.EVENT_TYPE = ET.EVENT_TYPE
1587: AND NVL(V.BILL_TRANS_REV_AMOUNT,0) <> 0
2040: P.RESOURCE_LIST_MEMBER_ID
2041: BULK COLLECT
2042: INTO l_res_assgn_id_tab,
2043: l_rlm_id_tab
2044: FROM PA_RESOURCE_ASSIGNMENTS P,
2045: PA_RES_LIST_MAP_TMP4 T
2046: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2047: AND NVL(P.TASK_ID,0) = 0
2048: AND P.PROJECT_ASSIGNMENT_ID = -1
2071: BULK COLLECT
2072: INTO l_res_assgn_id_tab,
2073: l_rlm_id_tab,
2074: l_txn_task_id_tab
2075: FROM PA_RESOURCE_ASSIGNMENTS P,
2076: PA_RES_LIST_MAP_TMP4 T
2077: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2078: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
2079: AND P.PROJECT_ASSIGNMENT_ID = -1
2103: INTO l_res_assgn_id_tab,
2104: l_rlm_id_tab,
2105: l_txn_top_task_id_tab,
2106: l_txn_sub_task_id_tab
2107: FROM PA_RESOURCE_ASSIGNMENTS P,
2108: PA_RES_LIST_MAP_TMP4 T,
2109: PA_TASKS TS
2110: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2111: AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
2139: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2140: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2141: NVL(T.TXN_TASK_ID,0) txn_task_id ,
2142: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
2143: FROM PA_RESOURCE_ASSIGNMENTS P,
2144: PA_RES_LIST_MAP_TMP4 T,
2145: PA_MAP_WP_TO_FIN_TASKS_V V
2146: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2147: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
2155: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2156: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2157: 0 txn_task_id,
2158: 0 mapped_fin_task_id
2159: FROM PA_RESOURCE_ASSIGNMENTS P,
2160: PA_RES_LIST_MAP_TMP4 T
2161: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2162: AND P.PROJECT_ASSIGNMENT_ID = -1
2163: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2231: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2232: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2233: NVL(T.TXN_TASK_ID,0) txn_task_id,
2234: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
2235: FROM PA_RESOURCE_ASSIGNMENTS P,
2236: PA_RES_LIST_MAP_TMP4 T,
2237: PA_MAP_WP_TO_FIN_TASKS_V V,
2238: PA_TASKS TS
2239: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2249: DISTINCT P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
2250: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
2251: 0 txn_task_id,
2252: 0 mapped_fin_task_id
2253: FROM PA_RESOURCE_ASSIGNMENTS P,
2254: PA_RES_LIST_MAP_TMP4 T
2255: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
2256: AND P.PROJECT_ASSIGNMENT_ID = -1
2257: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
2275: IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
2276: DELETE FROM pa_res_list_map_tmp4 tmp
2277: WHERE EXISTS
2278: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2279: FROM pa_resource_assignments ra
2280: WHERE ra.budget_version_id = p_budget_version_id
2281: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2282: AND ra.transaction_source_code IS NULL
2283: AND EXISTS
2291: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
2292: DELETE FROM pa_res_list_map_tmp4 tmp
2293: WHERE EXISTS
2294: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2295: FROM pa_resource_assignments ra
2296: WHERE ra.budget_version_id = p_budget_version_id
2297: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2298: AND ra.transaction_source_code IS NULL
2299: AND EXISTS
2305: ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
2306: DELETE FROM pa_res_list_map_tmp4 tmp
2307: WHERE EXISTS
2308: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
2309: FROM pa_resource_assignments ra
2310: WHERE ra.budget_version_id = p_budget_version_id
2311: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
2312: AND ra.transaction_source_code IS NULL
2313: AND EXISTS