952: where exists
953: (select 1
954: from pa_budget_versions bv
955: , pa_budget_types bt
956: , pa_resource_assignments a
957: where a.task_id = x_task_id
958: and bv.budget_version_id = a.budget_version_id
959: and bv.budget_type_code is NOT NULL -- This must be specified for r11.5.7 Budgets Model
960: and bv.budget_type_code = bt.budget_type_code
987: from dual
988: where exists
989: (select 1
990: from pa_budget_versions bv
991: , pa_resource_assignments a
992: where a.task_id = x_task_id
993: and bv.budget_version_id = a.budget_version_id
994: and bv.fin_plan_type_id is NOT NULL -- Specified for FP Model
995: and nvl(bv.wp_version_flag,'N') = 'N' -- (Added for Patchset M,Tracking Bug No - 3354518)
1016: where exists
1017: (select 1
1018: from pa_budget_versions bv
1019: , pa_budget_types bt
1020: , pa_resource_assignments a
1021: where a.task_id = x_task_id
1022: and bv.budget_version_id = a.budget_version_id
1023: and bv.budget_type_code = x_budget_type_code
1024: and bv.budget_type_code = bt.budget_type_code
1044: from dual
1045: where exists
1046: (select 1
1047: from pa_budget_versions bv
1048: , pa_resource_assignments a
1049: where a.task_id = x_task_id
1050: and bv.budget_version_id = a.budget_version_id
1051: and bv.fin_plan_type_id = x_fin_plan_type_id
1052: and bv.version_type = nvl(x_version_type, bv.version_type)
1072: where exists
1073: (select 1
1074: from pa_budget_versions bv
1075: , pa_budget_types bt
1076: , pa_resource_assignments a
1077: where a.task_id = x_task_id
1078: and bv.budget_version_id = a.budget_version_id
1079: and bv.budget_type_code = x_budget_type_code
1080: and bv.budget_type_code = bt.budget_type_code
1100: from dual
1101: where exists
1102: (select 1
1103: from pa_budget_versions bv
1104: , pa_resource_assignments a
1105: where a.task_id = x_task_id
1106: and bv.budget_version_id = a.budget_version_id
1107: and bv.fin_plan_type_id = x_fin_plan_type_id
1108: and bv.version_type = nvl(x_version_type, bv.version_type)
1153: where exists
1154: (select 1
1155: from pa_budget_versions bv
1156: , pa_tasks t
1157: , pa_resource_assignments a
1158: where a.budget_version_id = bv.budget_version_id
1159: and a.task_id = t.task_id
1160: and t.top_task_id = x_task_id
1161: and bv.approved_cost_plan_type_flag = 'Y'
1171: where exists
1172: (select 1
1173: from pa_budget_versions bv
1174: , pa_tasks t
1175: , pa_resource_assignments a
1176: where a.budget_version_id = bv.budget_version_id
1177: and a.task_id = t.task_id
1178: and t.top_task_id = x_task_id
1179: and bv.approved_rev_plan_type_flag = 'Y'
1210: (select 1
1211: from pa_budget_versions bv
1212: ,pa_tasks t --Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1213: -- , pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1214: ,pa_resource_assignments a
1215: where a.budget_version_id = bv.budget_version_id
1216: and a.task_id = t.task_id
1217: and t.top_task_id = x_task_id
1218: and bv.fin_plan_type_id = x_fin_plan_type_id
1237: (select 1
1238: from pa_budget_versions bv
1239: ,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1240: --, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1241: ,pa_resource_assignments a
1242: where a.budget_version_id = bv.budget_version_id
1243: and a.task_id = t.task_id
1244: and t.top_task_id = x_task_id
1245: and bv.fin_plan_type_id = x_fin_plan_type_id
1276: (select 1
1277: from pa_budget_versions bv
1278: , pa_budget_types bt
1279: , pa_tasks t
1280: , pa_resource_assignments a
1281: where a.budget_version_id = bv.budget_version_id
1282: and a.task_id = t.task_id
1283: and t.top_task_id = x_task_id
1284: and bv.budget_type_code = x_budget_type_code
1313: (select 1
1314: from pa_budget_versions bv
1315: ,pa_tasks t -- Bug 4176059: Performance Fix: FP.M -B12: re-used pa_tasks
1316: --, pa_struct_task_wbs_v t -- Adding for FP.M, Tracking Bug No - 3354518.
1317: ,pa_resource_assignments a
1318: where a.budget_version_id = bv.budget_version_id
1319: and a.task_id = t.task_id
1320: and t.top_task_id = x_task_id
1321: and bv.fin_plan_type_id = x_fin_plan_type_id
1403: from sys.dual
1404: where exists
1405: (select 1
1406: from pa_resource_list_members m,
1407: pa_resource_assignments a
1408: where m.parent_member_id = x_resource_list_member_id
1409: and m.resource_list_member_id = a.resource_list_member_id
1410: and a.budget_version_id = x_budget_version_id
1411: and a.task_id = x_task_id);
1415: into dummy
1416: from sys.dual
1417: where exists
1418: (select 1
1419: from pa_resource_assignments a
1420: where a.budget_version_id = x_budget_version_id
1421: and a.task_id = x_task_id
1422: and a.resource_list_member_id = x_parent_member_id);
1423:
1547: burdened_cost,
1548: labor_qty,
1549: revenue_amount
1550: FROM pa_budget_lines l,
1551: pa_resource_assignments a,
1552: pa_tasks t,
1553: pa_budget_versions v
1554: WHERE v.project_id = x_project_id
1555: AND v.budget_type_code = x_budget_type
1652: for bl_rec in (select rowid
1653: from pa_budget_lines
1654: where resource_assignment_id in
1655: (select resource_assignment_id
1656: from pa_resource_assignments
1657: where budget_version_id = x_budget_version_id))
1658: loop
1659: pa_budget_lines_v_pkg.delete_row(x_rowid => bl_rec.rowid);
1660: -- Bug Fix: 4569365. Removed MRC code.
2065: -- 1) Added call to Get_Project_Currency_Info. For the first call
2066: -- for a project, this API stores the OUT-parameters in globals
2067: -- to optimize subsequent calls.
2068: -- 2) Added RESOURCE_ASSIGNMENT_TYPE column and defualt value to
2069: -- pa_resource_assignments insert.
2070: -- 3) Added new currency columns to insert SQL.
2071: -- 4) Added exception handing and exception paragraphs for
2072: -- Get_Project_Currency_Info.
2073: --
2181: begin
2182:
2183: select resource_assignment_id
2184: into x_resource_assignment_id
2185: from pa_resource_assignments
2186: where budget_version_id = x_budget_version_id
2187: and project_id = x_project_id
2188: and NVL(task_id, 0) = NVL(x_task_id, 0)
2189: and resource_list_member_id = x_resource_list_member_id;
2205: pa_debug.g_err_stage:= 'No data found';
2206: pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2207: END IF;
2208:
2209: select pa_resource_assignments_s.nextval
2210: into x_resource_assignment_id
2211: from sys.dual;
2212:
2213: IF P_PA_DEBUG_MODE = 'Y' THEN
2215: pa_debug.write('create_line: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2216: END IF;
2217:
2218: -- create a new resource assignment
2219: insert into pa_resource_assignments
2220: (resource_assignment_id,
2221: budget_version_id,
2222: project_id,
2223: task_id,
2575: pa_currency.round_currency_amt(sum(nvl(l.revenue, 0))),
2576: SYSDATE,
2577: x_created_by,
2578: x_last_update_login
2579: from pa_resource_assignments a,
2580: pa_budget_lines l
2581: where a.budget_version_id = x_budget_version_id /*Bug 4198840: Perf:Included this join*/
2582: and a.budget_version_id = v.budget_version_id
2583: and a.resource_assignment_id = l.resource_assignment_id
2774: into l_dummy
2775: from sys.dual
2776: where exists
2777: (select 1
2778: from pa_resource_assignments
2779: where budget_version_id = p_draft_version_id);
2780:
2781: EXCEPTION
2782: WHEN NO_DATA_FOUND THEN
2823: into l_entry_level_code
2824: from sys.dual
2825: where exists
2826: (select 1
2827: from pa_resource_assignments
2828: where budget_version_id = p_draft_version_id
2829: -- and task_id is not null);
2830: -- this has been changed since pa_resource_assignments
2831: -- stores 0 if a task_id does not exist rather than null
2826: (select 1
2827: from pa_resource_assignments
2828: where budget_version_id = p_draft_version_id
2829: -- and task_id is not null);
2830: -- this has been changed since pa_resource_assignments
2831: -- stores 0 if a task_id does not exist rather than null
2832: and task_id <> 0);
2833:
2834: EXCEPTION
3496: --History:
3497: -- xx-xxx-xx who? - Created
3498: --
3499: -- 13-AUG-02 jwhite - Modified for FP model:
3500: -- Added filter to pa_resource_assignments,
3501: -- RESOURCE_ASSIGNMENT_TYPE = USER_ENTERED
3502: --
3503: -- 10-Feb-05 dbora Bug 4176059: Performance Fix: FP.M-B12
3504: -- Split cursor get_totals in to four separate
3572: SUM(NVL(l.BURDENED_COST,0)),
3573: SUM(NVL(l.REVENUE,0))
3574: from pa_tasks t,
3575: pa_budget_lines l ,
3576: pa_resource_assignments a
3577: where v_rollup_flag = 'T' -- Top Task Level
3578: and a.budget_version_id = x_budget_version_id
3579: and a.task_id = t.task_id
3580: and t.top_task_id = x_task_id
3587: SUM(NVL(l.RAW_COST,0)),
3588: SUM(NVL(l.BURDENED_COST,0)),
3589: SUM(NVL(l.REVENUE,0))
3590: from pa_budget_lines l,
3591: pa_resource_assignments a
3592: where v_rollup_flag = 'M' -- Middle Task Level
3593: and a.budget_version_id = x_budget_version_id
3594: and a.task_id in (select task_id
3595: from pa_tasks
3604: SUM(NVL(l.RAW_COST,0)),
3605: SUM(NVL(l.BURDENED_COST,0)),
3606: SUM(NVL(l.REVENUE,0))
3607: from pa_budget_lines l,
3608: pa_resource_assignments a
3609: where v_rollup_flag = 'L' -- Lowest Task Level
3610: and a.budget_version_id = x_budget_version_id
3611: and a.task_id = x_task_id
3612: and a.resource_assignment_id = l.resource_assignment_id