[Home] [Help]
920: -- This cursor should be used when p_version_level_flag is 'Y'
921: -- and p_copy_mode is 'COPY_ALL'.
922:
923: CURSOR ver_lvl_copy_all_csr
924: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
925: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
926: SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
927: src_rbc.TXN_CURRENCY_CODE,
928: src_rbc.TOTAL_QUANTITY,
921: -- and p_copy_mode is 'COPY_ALL'.
922:
923: CURSOR ver_lvl_copy_all_csr
924: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
925: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
926: SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
927: src_rbc.TXN_CURRENCY_CODE,
928: src_rbc.TOTAL_QUANTITY,
929: src_rbc.TOTAL_INIT_QUANTITY,
954: src_rbc.TOTAL_PROJFUNC_INIT_RAW_COST,
955: src_rbc.TOTAL_PROJFUNC_INIT_BD_COST,
956: src_rbc.TOTAL_PROJFUNC_INIT_REVENUE,
957: src_rbc.TOTAL_DISPLAY_QUANTITY
958: FROM pa_resource_assignments tgt_ra,
959: pa_resource_assignments src_ra,
960: pa_resource_asgn_curr src_rbc
961: WHERE src_rbc.budget_version_id = c_src_version_id
962: AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
955: src_rbc.TOTAL_PROJFUNC_INIT_BD_COST,
956: src_rbc.TOTAL_PROJFUNC_INIT_REVENUE,
957: src_rbc.TOTAL_DISPLAY_QUANTITY
958: FROM pa_resource_assignments tgt_ra,
959: pa_resource_assignments src_ra,
960: pa_resource_asgn_curr src_rbc
961: WHERE src_rbc.budget_version_id = c_src_version_id
962: AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
963: AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
969: -- This cursor should be used when p_version_level_flag is 'Y'
970: -- and p_copy_mode is 'COPY_OVERRIDES'.
971:
972: CURSOR ver_lvl_copy_overrides_csr
973: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
974: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
975: SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
976: src_rbc.TXN_CURRENCY_CODE,
977: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
970: -- and p_copy_mode is 'COPY_OVERRIDES'.
971:
972: CURSOR ver_lvl_copy_overrides_csr
973: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
974: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
975: SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
976: src_rbc.TXN_CURRENCY_CODE,
977: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
978: src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
976: src_rbc.TXN_CURRENCY_CODE,
977: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
978: src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
979: src_rbc.TXN_BILL_RATE_OVERRIDE
980: FROM pa_resource_assignments tgt_ra,
981: pa_resource_assignments src_ra,
982: pa_resource_asgn_curr src_rbc
983: WHERE src_rbc.budget_version_id = c_src_version_id
984: AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
977: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
978: src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
979: src_rbc.TXN_BILL_RATE_OVERRIDE
980: FROM pa_resource_assignments tgt_ra,
981: pa_resource_assignments src_ra,
982: pa_resource_asgn_curr src_rbc
983: WHERE src_rbc.budget_version_id = c_src_version_id
984: AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
985: AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
994: -- Note: Ordered hint has been added to avoid a Merge Join Cartesian
995: -- join order in the execution plan.
996:
997: CURSOR tbl_mode_copy_overrides_csr
998: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
999: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1000: SELECT /*+ ORDERED */
1001: tmp.RESOURCE_ASSIGNMENT_ID,
1002: src_rbc.TXN_CURRENCY_CODE,
995: -- join order in the execution plan.
996:
997: CURSOR tbl_mode_copy_overrides_csr
998: ( c_src_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
999: c_tgt_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1000: SELECT /*+ ORDERED */
1001: tmp.RESOURCE_ASSIGNMENT_ID,
1002: src_rbc.TXN_CURRENCY_CODE,
1003: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
1003: src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
1004: src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
1005: src_rbc.TXN_BILL_RATE_OVERRIDE
1006: FROM pa_resource_asgn_curr src_rbc,
1007: pa_resource_assignments src_ra,
1008: pa_resource_assignments tgt_ra,
1009: pa_resource_asgn_curr_tmp tmp
1010: WHERE tgt_ra.budget_version_id = c_tgt_version_id
1011: AND src_ra.budget_version_id = c_src_version_id
1004: src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
1005: src_rbc.TXN_BILL_RATE_OVERRIDE
1006: FROM pa_resource_asgn_curr src_rbc,
1007: pa_resource_assignments src_ra,
1008: pa_resource_assignments tgt_ra,
1009: pa_resource_asgn_curr_tmp tmp
1010: WHERE tgt_ra.budget_version_id = c_tgt_version_id
1011: AND src_ra.budget_version_id = c_src_version_id
1012: AND tgt_ra.resource_assignment_id = tmp.resource_assignment_id
1549: BULK COLLECT
1550: INTO l_ra_id_tab,
1551: l_txn_currency_code_tab
1552: FROM pa_budget_lines bl,
1553: pa_resource_assignments ra
1554: WHERE ra.budget_version_id = p_fp_cols_rec.x_budget_version_id
1555: AND bl.resource_assignment_id = ra.resource_assignment_id
1556: AND NOT EXISTS (SELECT null
1557: FROM pa_resource_asgn_curr rbc
1738: -- The second half gets data for resources that have records in
1739: -- PA_RESOURCE_ASGN_CURR but no budget lines.
1740:
1741: CURSOR version_level_rollup_csr
1742: ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1743: c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1744: SELECT bl.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1745: bl.txn_currency_code, --TXN_CURRENCY_CODE
1746: case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1739: -- PA_RESOURCE_ASGN_CURR but no budget lines.
1740:
1741: CURSOR version_level_rollup_csr
1742: ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1743: c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1744: SELECT bl.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1745: bl.txn_currency_code, --TXN_CURRENCY_CODE
1746: case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1747: decode(sum(nvl(bl.init_quantity,0)),
1839: decode(sum(nvl(bl.init_revenue,0)),
1840: 0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
1841: decode(sum(nvl(bl.display_quantity,0)),
1842: 0,null,sum(nvl(bl.display_quantity,0))) --TOTAL_DISPLAY_QUANTITY
1843: FROM pa_resource_assignments ra,
1844: pa_budget_lines bl,
1845: pa_resource_asgn_curr rbc
1846: WHERE bl.resource_assignment_id = rbc.resource_assignment_id (+)
1847: AND bl.txn_currency_code = rbc.txn_currency_code (+)
1902: -- NOTE: the cursor parameters c_project_id and c_budget_version_id
1903: -- are not necessary here, but included in case of future use.
1904:
1905: CURSOR table_level_rollup_csr
1906: ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1907: c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1908: SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1909: rbc.txn_currency_code, --TXN_CURRENCY_CODE
1910: case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1903: -- are not necessary here, but included in case of future use.
1904:
1905: CURSOR table_level_rollup_csr
1906: ( c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1907: c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE ) IS
1908: SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
1909: rbc.txn_currency_code, --TXN_CURRENCY_CODE
1910: case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
1911: decode(sum(nvl(bl.init_quantity,0)),