[Home] [Help]
85: -- BUG FIX 2615852: need to recognize if all txn currencies entered for a ra
86: --
87:
88: function all_txn_currencies_entered
89: (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE)
90: return VARCHAR2
91: is
92: l_return_value VARCHAR2(1);
93: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
111: begin
112: l_return_value := 'N';
113: select budget_version_id
114: into l_budget_version_id
115: from pa_resource_assignments
116: where resource_assignment_id = p_resource_assignment_id;
117: open unentered_csr;
118: fetch unentered_csr into unentered_rec;
119: -- if the csr is empty, then there are no txn currencies for which a budget
1209:
1210: -- HISTORY
1211: -- 10/22/2002: dlai commented out using ra_cursors; it's just a performance optimization
1212: -- that may cause errors like bug 2637079
1213: -- 11/08/02: the cursors are modified such that if they query from pa_resource_assignments,
1214: -- we use the value of G_DISPLAY_CURRENCY_TYPE to determine whether to query from
1215: -- the project columns or projfunc columns
1216: -- 12/24/02: Bug 2710844-The view plan should show resource assignments for which budget lines exists
1217: PROCEDURE view_plans_txn_populate_tmp
1221: p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE,
1222: p_both_version_id IN pa_budget_versions.budget_version_id%TYPE,
1223: p_project_currency IN pa_projects_all.project_currency_code%TYPE,
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
1223: p_project_currency IN pa_projects_all.project_currency_code%TYPE,
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
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:
1233: -------------------- CURSORS FOR MULTICURRENCY PLAN VERSIONS --------------------
1234: -- 9/25/02: Inserted UNION with query from PA_RESOURCE_ASSIGNMENTS; we want to
1235: -- return all entered rows (budget lines) as well as resource assignments for
1236: -- which budget lines have not yet been created
1237: --10/08/02: modified cursor query: where rlm.resource_type_code <> 'UNCATEGORIZED'
1238:
1254: SUM(nvl(bl_cost.txn_raw_cost,0)) as raw_cost,
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
1330: DECODE(ra.total_plan_revenue,
1331: 0, 0,
1332: (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1333: ra.total_plan_revenue)) as margin_percent -- null
1334: from pa_resource_assignments ra,
1335: pa_resource_list_members rlm,
1336: pa_fp_txn_currencies ftc,
1337: pa_resources pr -- added for bug 3463685
1338: where ra.budget_version_id = p_cost_version_id and
1387: -- 0 as raw_cost,
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
1448: 'PROJECT', ra.total_project_revenue,
1449: ra.total_plan_revenue) as revenue -- null
1450: -- 0 as margin,
1451: -- 0 as margin_percent
1452: from pa_resource_assignments ra,
1453: pa_resource_list_members rlm,
1454: pa_fp_txn_currencies ftc,
1455: pa_resources pr -- added for bug 3463685
1456: where ra.budget_version_id = p_revenue_version_id and
1516: 'R', (SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_raw_cost,0)))/
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
1590: DECODE(ra.total_plan_revenue,
1591: 0, 0,
1592: (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1593: ra.total_plan_revenue)) as margin_percent -- null
1594: from pa_resource_assignments ra,
1595: pa_resource_list_members rlm,
1596: pa_fp_txn_currencies ftc,
1597: pa_resources pr -- Added for bug 3463685
1598: where ra.budget_version_id = p_both_version_id and
1653: ra.total_plan_raw_cost) as raw_cost,
1654: 0 as revenue,
1655: 0 as margin,
1656: 0 as margin_percent
1657: from pa_resource_assignments ra,
1658: pa_resource_list_members rlm,
1659: pa_resources pr -- added for bug 3463685
1660: where ra.budget_version_id = p_cost_version_id and
1661: ra.resource_assignment_type = 'USER_ENTERED' and
1708: 'PROJECT', ra.total_project_revenue,
1709: ra.total_plan_revenue) as revenue
1710: -- 0 as margin,
1711: -- 0 as margin_percent
1712: from pa_resource_assignments ra,
1713: pa_resource_list_members rlm,
1714: pa_resources pr -- Added for bug 3463685
1715: where ra.budget_version_id = p_revenue_version_id and
1716: ra.resource_assignment_type = 'USER_ENTERED' and
1791: 'R', (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1792: ra.total_plan_revenue,
1793: (ra.total_plan_revenue - ra.total_plan_burdened_cost)/
1794: ra.total_plan_revenue))) as margin_percent
1795: from pa_resource_assignments ra,
1796: pa_resource_list_members rlm,
1797: pa_resources pr -- Added for bug 3463685
1798: where ra.budget_version_id = p_both_version_id and
1799: ra.resource_assignment_type = 'USER_ENTERED' and
3240: l_ra_id_tbl SYSTEM.pa_num_tbl_type ;
3241: l_task_id pa_tasks.task_id%TYPE;
3242: l_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3243: l_resource_id pa_resource_list_members.resource_id%TYPE;
3244: l_prev_res_assignment_id pa_resource_assignments.resource_assignment_id%TYPE := -99;
3245:
3246: CURSOR cur_sel_bud_line IS
3247: SELECT budget_line_id,
3248: resource_assignment_id,
3354: l_rlm_id,
3355: l_project_id,
3356: l_resource_id,
3357: l_resource_list_id
3358: FROM pa_resource_assignments pra,
3359: pa_resource_list_members prlm
3360: WHERE pra.resource_assignment_id = l_ra_id_tbl(i)
3361: AND prlm.resource_list_member_id = pra.resource_list_member_id;
3362: