[Home] [Help]
103: where pfo.fin_plan_version_id = l_budget_version_id and
104: txncurr.proj_fp_options_id = pfo.proj_fp_options_id and
105: not (txn_currency_code in
106: (select distinct txn_currency_code
107: from pa_budget_lines bl
108: where bl.resource_assignment_id = p_resource_assignment_id));
109: unentered_rec unentered_csr%ROWTYPE;
110:
111: begin
1224: p_get_display_from IN VARCHAR2, -- 'COST', 'REVENUE', 'BOTH', 'ANY'
1225: p_filter_task_id IN pa_resource_assignments.task_id%TYPE,
1226: p_filter_resource_id IN pa_resource_list_members.resource_id%TYPE,
1227: p_filter_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
1228: p_filter_txncurrency IN pa_budget_lines.txn_currency_code%TYPE,
1229: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1230: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1231: x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1232:
1255: 0 as revenue,
1256: 0 as margin,
1257: 0 as margin_percent
1258: from pa_resource_assignments ra_cost,
1259: pa_budget_lines bl_cost,
1260: pa_resource_list_members rlm,
1261: pa_resources pr -- added for bug 3463685
1262: where ra_cost.budget_version_id = p_cost_version_id and
1263: ra_cost.resource_assignment_type = 'USER_ENTERED' and
1369: rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1370: ftc.default_cost_curr_flag = 'Y' and
1371: (p_filter_txncurrency = 'ALL' or
1372: ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1373: not exists(select bl.resource_assignment_id from pa_budget_lines bl
1374: where ra.resource_assignment_id = bl.resource_assignment_id) and
1375: p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1376:
1377: cursor revenue_csr is
1388: SUM(nvl(bl_revenue.txn_revenue,0)) as revenue
1389: -- 0 as margin,
1390: -- 0 as margin_percent
1391: from pa_resource_assignments ra_revenue,
1392: pa_budget_lines bl_revenue,
1393: pa_resource_list_members rlm,
1394: pa_resources pr -- Added for bug 3463685
1395: where ra_revenue.budget_version_id = p_revenue_version_id and
1396: ra_revenue.resource_assignment_type = 'USER_ENTERED' and
1489: ra.budget_version_id = ftc.fin_plan_version_id and
1490: ftc.default_rev_curr_flag = 'Y' and
1491: (p_filter_txncurrency = 'ALL' or
1492: ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1493: not exists(select bl.resource_assignment_id from pa_budget_lines bl
1494: where ra.resource_assignment_id = bl.resource_assignment_id) and
1495: p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1496:
1497: cursor all_csr is
1517: SUM(nvl(bl.txn_revenue,0)),
1518: (SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_burdened_cost,0)))/
1519: SUM(nvl(bl.txn_revenue,0)))) as margin_percent
1520: from pa_resource_assignments ra,
1521: pa_budget_lines bl,
1522: pa_resource_list_members rlm,
1523: pa_resources pr -- Added for bug 3463685
1524: where ra.budget_version_id = p_both_version_id and
1525: ra.resource_assignment_type = 'USER_ENTERED' and
1629: ra.budget_version_id = ftc.fin_plan_version_id and
1630: ftc.default_all_curr_flag = 'Y' and
1631: (p_filter_txncurrency = 'ALL' or
1632: ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1633: not exists(select bl.resource_assignment_id from pa_budget_lines bl
1634: where ra.resource_assignment_id = bl.resource_assignment_id) and
1635: p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1636:
1637: -------------------- CURSORS FOR SINGLE CURRENCY PLAN VERSIONS --------------------
1687: rlm.parent_member_id is not null and
1688: rlm.resource_id = p_filter_rlm_id and
1689: (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1690: rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1691: exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1692: bl.resource_assignment_id = ra.resource_assignment_id
1693: union
1694: select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1695:
1742: rlm.parent_member_id is not null and
1743: rlm.resource_id = p_filter_rlm_id and
1744: (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1745: rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1746: exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1747: bl.resource_assignment_id = ra.resource_assignment_id
1748: union
1749: select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1750:
1825: rlm.parent_member_id is not null and
1826: rlm.resource_id = p_filter_rlm_id and
1827: (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1828: rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1829: exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1830: bl.resource_assignment_id = ra.resource_assignment_id
1831: union
1832: select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1833:
3254: quantity,
3255: txn_raw_cost,
3256: txn_burdened_cost,
3257: txn_revenue
3258: FROM pa_budget_lines
3259: WHERE budget_version_id = p_budget_version_id
3260: ORDER BY resource_assignment_id;
3261:
3262: BEGIN
3422: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3423: END IF;
3424:
3425: FORALL i in l_budget_line_id_tbl.first..l_budget_line_id_tbl.last
3426: UPDATE pa_budget_lines
3427: SET txn_raw_cost = l_txn_raw_cost_tbl(i)
3428: ,txn_burdened_cost = l_txn_burdened_cost_tbl(i)
3429: ,txn_revenue = l_txn_revenue_tbl(i)
3430: ,quantity = l_quantity_tbl(i)