[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pr.alias
FROM pa_resource_assignments ra, pa_resource_list_members pr
WHERE ra.resource_list_member_id = pr.resource_list_member_id
AND ra.wbs_element_version_id = p_element_version_id
AND ra.resource_class_code = 'PEOPLE'
AND ra.ta_display_flag = 'Y'
AND rownum <= 5
UNION ALL
SELECT pr.alias
FROM pa_resource_assignments ra, pa_resource_list_members pr
WHERE ra.resource_list_member_id = pr.resource_list_member_id
AND ra.wbs_element_version_id = p_element_version_id
AND ra.resource_class_code = 'EQUIPMENT'
AND ra.ta_display_flag = 'Y'
AND rownum <= 5
UNION ALL
SELECT pr.alias
FROM pa_resource_assignments ra, pa_resource_list_members pr
WHERE ra.resource_list_member_id = pr.resource_list_member_id
AND ra.wbs_element_version_id = p_element_version_id
AND ra.resource_class_code = 'MATERIAL_ITEMS'
AND ra.ta_display_flag = 'Y'
AND rownum <= 5
UNION ALL
SELECT pr.alias
FROM pa_resource_assignments ra, pa_resource_list_members pr
WHERE ra.resource_list_member_id = pr.resource_list_member_id
AND ra.wbs_element_version_id = p_element_version_id
AND ra.resource_class_code = 'FINANCIAL_ELEMENTS'
AND ra.ta_display_flag = 'Y'
AND rownum <= 5;
SELECT 'Y'
FROM pa_resource_assignments ra
WHERE ra.ta_display_flag = 'Y'
AND ra.wbs_element_version_id = p_element_version_id
AND rownum = 1;
SELECT 'Y'
FROM pa_resource_assignments ra, pa_map_wp_to_fin_tasks_v map
WHERE p_ei_date BETWEEN ra.planning_start_date AND ra.planning_end_Date
AND ra.person_id = p_person_id
AND ra.ta_display_flag = 'Y'
AND ra.wbs_element_version_id = map.element_version_id
AND map.mapped_fin_task_id = p_financial_task_id
AND map.project_id = p_project_id
AND ROWNUM = 1;
SELECT project_id
FROM pa_proj_elements
WHERE proj_element_id = p_financial_task_id;
p_context IN VARCHAR2 DEFAULT 'UPDATE',
p_element_version_id IN NUMBER,
p_old_task_sch_start IN DATE,
p_old_task_sch_finish IN DATE DEFAULT NULL,
p_new_task_sch_start IN DATE,
p_new_task_sch_finish IN DATE,
x_res_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
x_planning_start_tbl OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
x_planning_end_tbl OUT NOCOPY SYSTEM.PA_DATE_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_struct_ver_id number;
SELECT bv.budget_version_id, pev.parent_structure_version_id
from pa_proj_element_versions pev, pa_budget_versions bv
where pev.element_version_id = p_element_version_id
and pev.parent_structure_version_id = bv.project_structure_version_id;
pa_debug.g_err_stage:= 'insert into temp table:';
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
INSERT INTO pa_copy_asgmts_temp
(
SRC_ELEM_VER_ID,
TARG_ELEM_VER_ID,
OLD_TASK_SCH_START,
OLD_TASK_SCH_END,
NEW_TASK_SCH_START,
NEW_TASK_SCH_END
)
VALUES
(p_element_version_id, null,
p_old_task_sch_start,p_old_task_sch_finish,
p_new_task_sch_start,p_new_task_sch_finish);
IF p_context = 'UPDATE' OR p_context = 'COPY' THEN
SELECT count(SRC_ELEM_VER_ID)
INTO l_num_of_tasks
FROM pa_copy_asgmts_temp;
SELECT to_number(value)
INTO l_db_block_size
FROM v$parameter
WHERE name = 'db_block_size';
SELECT ra.resource_assignment_id, ra.total_plan_quantity,
tasks.src_elem_ver_id, tasks.new_task_sch_start, tasks.new_task_sch_end
BULK COLLECT INTO l_use_task_sch_asgmt_tbl, l_use_task_quantity_tbl,
l_use_task_task_ver_tbl, l_use_task_new_sch_start, l_use_task_new_sch_end --Bug 4153366
FROM pa_resource_assignments ra, pa_copy_asgmts_temp tasks
WHERE ra.use_task_schedule_flag = 'Y'
AND ra.ta_display_flag is not null
AND ra.budget_version_id = l_budget_ver_id -- Bug 4229020
AND ra.wbs_element_version_id = tasks.src_elem_ver_id;
SELECT ra.resource_assignment_id, ra.total_plan_quantity,
(ra.schedule_start_date - tasks.OLD_TASK_SCH_START) old_delay,
(ra.schedule_end_date - ra.schedule_start_date) old_duration,
(tasks.NEW_TASK_SCH_START + (ra.schedule_start_date - tasks.OLD_TASK_SCH_START)) new_start_date,
(tasks.NEW_TASK_SCH_START + (ra.schedule_start_date - tasks.OLD_TASK_SCH_START)
+ (ra.schedule_end_date - ra.schedule_start_date)) new_end_date,
Compare_Dates((tasks.NEW_TASK_SCH_START + (ra.schedule_start_date - tasks.OLD_TASK_SCH_START)
+ (ra.schedule_end_date - ra.schedule_start_date)), tasks.NEW_TASK_SCH_END),
-- compare 'new assignment start + old duration' to 'task new end'
tasks.NEW_TASK_SCH_START,
tasks.NEW_TASK_SCH_END,
tasks.src_elem_ver_id
BULK COLLECT INTO
l_adj_dates_asgmt_tbl, l_adj_task_quantity_tbl, l_delay_tbl, l_duration_tbl,
l_new_start_tbl, l_new_end_tbl, l_finish_flg_tbl, l_new_task_sch_start, l_new_task_sch_finish, l_adj_task_task_ver_tbl
FROM pa_resource_assignments ra, pa_copy_asgmts_temp tasks
WHERE nvl(ra.use_task_schedule_flag, 'N') <> 'Y'
AND ra.ta_display_flag is not null
AND ra.budget_version_id = l_budget_ver_id
AND ra.wbs_element_version_id = tasks.src_elem_ver_id;
IF p_context = 'UPDATE' THEN
IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
pa_debug.g_err_stage:= ' B4 update planning transaction call update in adjust dates: ' || x_return_status;
pa_fp_planning_transaction_pub.update_planning_transactions
(
p_context => 'TASK_ASSIGNMENT',
p_struct_elem_version_id => l_struct_ver_id,
p_budget_version_id => l_budget_ver_id,
p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
p_resource_assignment_id_tbl => l_resource_assignment_id_tbl,
p_schedule_start_date_tbl => l_schedule_start_date_tbl,
p_schedule_end_date_tbl => l_schedule_end_date_tbl,
X_Return_Status => x_return_status,
X_Msg_Data => l_msg_data,
X_Msg_Count => l_msg_count
);
pa_debug.g_err_stage:= 'x_return_status after update in adjust dates ' || x_return_status;
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
END IF; -- IF p_context = 'UPDATE' OR p_context = 'COPY' THEN
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
SELECT pevs.scheduled_start_date, pevs.scheduled_finish_date,
pe.name task_name, pe.element_number task_number,
pe.proj_element_id
FROM pa_proj_elem_ver_schedule pevs, pa_proj_element_versions pev,
pa_proj_elements pe
WHERE pev.element_version_id = pevs.element_version_id
AND pev.element_version_id = p_elem_version_id
AND pev.proj_element_id = pe.proj_element_id;
SELECT assignment_name, project_role_id
FROM pa_project_assignments
WHERE assignment_id = p_project_assignment_id;
UPDATE pa_resource_assignments
SET ta_display_flag = 'Y',
record_version_number = record_version_number + 1
WHERE project_id = l_task_rec_project_id_tbl(k)
AND wbs_element_version_id = l_task_rec_task_ver_id_tbl(k)
AND ta_display_flag = 'N'
RETURNING task_id, resource_list_member_id
BULK COLLECT INTO l_progress_task_id, l_progress_rlm_id;
PROCEDURE Validate_Update_Assignment
(
p_calling_context IN VARCHAR2 DEFAULT NULL, -- Added for Bug 6856934
p_task_assignment_tbl IN OUT NOCOPY l_resource_rec_tbl_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_task_sch_start DATE;
SELECT pevs.scheduled_start_date, pevs.scheduled_finish_date,
pe.name task_name, pe.element_number task_number
FROM pa_proj_elem_ver_schedule pevs, pa_proj_element_versions pev,
pa_proj_elements pe
WHERE pev.element_version_id = pevs.element_version_id
AND pev.element_version_id = p_elem_version_id
AND pev.proj_element_id = pe.proj_element_id;
SELECT latest_eff_published_flag
FROM pa_proj_elem_ver_structure pevs, pa_proj_element_versions pev
WHERE pev.element_version_id = p_elem_version_id
AND pev.parent_structure_version_id = pevs.element_version_id
AND pev.project_id = pevs.project_id;
SELECT *
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT budget_version_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT ra.ta_display_flag, ra.resource_list_member_id, ra.project_id, ra.task_id, bv.project_structure_version_id
FROM pa_resource_assignments ra, pa_budget_versions bv
WHERE ra.resource_assignment_id = p_resource_assignment_id
AND ra.budget_version_id = bv.budget_version_id;
SELECT actual_effort
FROM pa_assgn_cost_effort_v
WHERE resource_assignment_id = p_resource_assignment_id
AND progress_rollup_id IS NOT NULL
and rownum = 1;
SELECT project_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_task_assignment_tbl(1).resource_assignment_id;
L_FuncProc := 'Validate_Update_Assignment';
pa_debug.g_err_stage:='Beginning of TA:Validate_Update_Assignment';
UPDATE pa_resource_assignments
SET ta_display_flag = 'Y'
WHERE resource_assignment_id = p_task_assignment_tbl(i).resource_assignment_id;
SELECT parent_structure_version_id, project_id, proj_element_id
INTO l_struct_ver_id, l_project_id, l_proj_element_id
from pa_proj_element_versions
where element_version_id = l_wbs_element_version_id;
-- If the user re-select the same planning resource with via the planning resource LOV
-- (e.g. when the user selects the same planning resource but picks one with a team role)
-- Keep the attributes which have been defaulted.
IF (p_task_assignment_tbl(i).resource_list_member_id = R_Res_Assignment_Rec.resource_list_member_id
OR p_task_assignment_tbl(i).resource_list_member_id IS NULL) THEN
p_task_assignment_tbl(i).organization_id := R_Res_Assignment_Rec.organization_id;
PA_PROGRESS_UTILS.check_prog_for_update_asgmts
(
p_task_assignment_tbl => p_task_assignment_tbl,
x_return_status => x_return_status
);
pa_debug.g_err_stage:='End of TA:Validate_Update_Assignment';
END Validate_Update_Assignment;
PROCEDURE Validate_Delete_Assignment
(
p_context IN VARCHAR2,
p_calling_context IN VARCHAR2 DEFAULT NULL, -- Added for Bug 6856934
p_task_or_res IN VARCHAR2 DEFAULT 'ASSIGNMENT',
p_elem_ver_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_task_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE,
p_task_number_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE,
p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
x_delete_task_flag_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE,
x_delete_asgmt_flag_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE,
x_task_assmt_ids_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE, --Bug 4951422
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_FuncProc varchar2(250) ;
SELECT pev.project_id, pev.parent_structure_version_id, pev.proj_element_id, pe.name, pe.element_number
FROM pa_proj_element_versions pev, pa_proj_elements pe
WHERE pev.element_version_id = p_wbs_element_version_id AND pe.proj_element_id = pev.proj_element_id;
SELECT ra.wbs_element_version_id, ra.project_id, pev.proj_element_id, ra.resource_list_member_id, ra.ta_display_flag, ra.task_id, ra.unplanned_flag, ra.total_plan_quantity
FROM pa_resource_assignments ra, pa_proj_element_versions pev
WHERE resource_assignment_id = p_resource_assignment_id
and pev.element_version_id = ra.wbs_element_version_id;
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE wbs_element_version_id = p_element_version_id;
SELECT 'Y'
FROM pa_resource_assignments
WHERE wbs_element_version_id = p_element_version_id
AND total_plan_quantity > 0
AND rownum = 1;
SELECT 'Y'
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id
AND total_plan_quantity > 0;
SELECT project_id
FROM pa_proj_element_versions
WHERE element_version_id = p_elem_version_id;
SELECT project_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
L_FuncProc := 'Validate_Delete_Assignment';
pa_debug.g_err_stage:='Beginning of TA:Validate_Delete_Assignment';
x_delete_task_flag_tbl := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
x_delete_asgmt_flag_tbl := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
x_delete_task_flag_tbl.extend(p_elem_ver_id_tbl.COUNT);
l_progress_exists := PA_PROGRESS_UTILS.check_prog_exists_and_delete(
l_project_id,
l_proj_element_id,
'PA_TASKS',
l_proj_element_id,
'WORKPLAN');
x_delete_task_flag_tbl(i) := 'N';
x_delete_task_flag_tbl(i) := 'Y';
pa_deliverable_pub.delete_dlv_task_asscn_in_bulk
(
p_init_msg_list => FND_API.G_FALSE,
p_calling_context => 'PA_ASSIGNMENTS',
p_task_element_id => l_task_asgmts_tbl(j),
p_task_version_id => NULL,
p_project_id => l_project_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
x_delete_asgmt_flag_tbl.extend(p_resource_assignment_id_tbl.COUNT);
x_delete_asgmt_flag_tbl(i) := 'Y';
x_delete_asgmt_flag_tbl.extend(p_resource_assignment_id_tbl.COUNT);
l_progress_exists := PA_PROGRESS_UTILS.check_prog_exists_and_delete(
l_project_id,
l_proj_element_id,
'PA_ASSIGNMENTS',
l_rlm_id,
'WORKPLAN');
x_delete_asgmt_flag_tbl(i) := 'N';
x_delete_asgmt_flag_tbl(i) := 'Y';
pa_deliverable_pub.delete_dlv_task_asscn_in_bulk
(
p_init_msg_list => FND_API.G_FALSE,
p_calling_context => 'PA_ASSIGNMENTS',
p_task_element_id => p_resource_assignment_id_tbl(i),
p_task_version_id => l_wbs_element_version_id,
p_project_id => l_project_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
-- p_msg_name => 'PA_UPDATE_PUB_VER_ERR'
--);
pa_debug.g_err_stage:='End of TA:Validate_Delete_Assignment';
END Validate_Delete_Assignment;
select pev.parent_structure_version_id structure_version_id, bv.budget_version_id, fpo.track_workplan_costs_flag
from pa_proj_element_versions pev, pa_budget_versions bv, pa_proj_fp_options fpo
where bv.project_structure_version_id = pev.parent_structure_version_id
and bv.project_id = fpo.project_id
and bv.fin_plan_type_id = fpo.fin_plan_type_id
and bv.budget_version_id = fpo.fin_plan_version_id
and fpo.fin_plan_option_level_code = 'PLAN_VERSION'
and bv.wp_version_flag = 'Y'
and pev.element_version_id = p_target_element_version_id;
select projfunc_currency_code, project_currency_code
from pa_projects_all
where project_id = c_project_id;
select txn_currency_code
from pa_budget_lines
where resource_assignment_id = p_resource_assignment_id
and rownum = 1;
SELECT * FROM
(
SELECT
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
ra.resource_list_member_id res_mem_list_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
sum(bl.quantity),
sum(bl.txn_raw_cost),
-- Bug 3951947: Should sum up raw cost in projfunc currency
sum(bl.raw_cost),
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id tr_res_mem_list_id,
ra.task_id,
'RES_ASSIGNMENT',
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
FROM
pa_copy_asgmts_temp temp,
pa_proj_element_versions pev,
pa_resource_assignments ra,
pa_project_assignments asgn,
pa_budget_lines bl,
pa_resource_list_members rlm,
pa_res_formats_b rf
WHERE
pev.element_version_id = temp.src_elem_ver_id AND
pev.element_version_id = ra.wbs_element_version_id AND
bl.resource_assignment_id = ra.resource_assignment_id AND
ra.ta_display_flag IS NOT NULL AND
(ra.unplanned_flag = 'N' OR ra.unplanned_flag IS NULL) AND
decode(ra.project_assignment_id, -1, null, ra.project_assignment_id) = asgn.assignment_id(+) AND
ra.resource_class_code IN ( p_res_class1 , p_res_class2 , p_res_class3, p_res_class4 ) AND
rlm.resource_list_member_id = ra.resource_list_member_id AND
rf.res_format_id = rlm.res_format_id
GROUP BY
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
ra.resource_list_member_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id,
ra.task_id,
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
UNION ALL
SELECT
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
ra.resource_list_member_id res_mem_list_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
to_number(NULL),
to_number(NULL),
to_number(NULL),
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id tr_res_mem_list_id,
ra.task_id,
'RES_ASSIGNMENT',
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
FROM
pa_copy_asgmts_temp temp,
pa_proj_element_versions pev,
pa_resource_assignments ra,
pa_project_assignments asgn,
pa_resource_list_members rlm,
pa_res_formats_b rf
WHERE
pev.element_version_id = temp.src_elem_ver_id AND
pev.element_version_id = ra.wbs_element_version_id AND
ra.ta_display_flag IS NOT NULL AND
(ra.unplanned_flag = 'N' OR ra.unplanned_flag IS NULL) AND
decode(ra.project_assignment_id, -1, NULL, ra.project_assignment_id) = asgn.assignment_id(+) AND
ra.resource_class_code IN ( p_res_class1 , p_res_class2 , p_res_class3, p_res_class4 ) AND
rlm.resource_list_member_id = ra.resource_list_member_id AND
rf.res_format_id = rlm.res_format_id AND
NOT EXISTS
(SELECT 1 FROM pa_budget_lines bl WHERE bl.resource_assignment_id = ra.resource_assignment_id)
) ORDER BY 5;
SELECT * FROM
(
SELECT
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
source_rlm.resource_list_member_id src_res_mem_list_id,
target_rlm.resource_list_member_id tar_res_mem_list_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
sum(bl.quantity),
sum(bl.txn_raw_cost),
-- Bug 3951947: Should sum up raw cost in projfunc currency
sum(bl.raw_cost),
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id tr_res_mem_list_id,
ra.task_id,
'RES_ASSIGNMENT',
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
FROM
pa_copy_asgmts_temp temp,
pa_proj_element_versions pev,
pa_resource_assignments ra,
pa_project_assignments asgn,
pa_budget_lines bl,
pa_resource_list_members source_rlm,
pa_resource_list_members target_rlm,
pa_res_formats_b rf
WHERE
pev.element_version_id = temp.src_elem_ver_id AND
pev.element_version_id = ra.wbs_element_version_id AND
bl.resource_assignment_id = ra.resource_assignment_id AND
ra.ta_display_flag IS NOT NULL AND
(ra.unplanned_flag = 'N' OR ra.unplanned_flag IS NULL) AND
decode(ra.project_assignment_id, -1, null, ra.project_assignment_id) = asgn.assignment_id(+) AND
ra.resource_class_code IN ( p_res_class1 , p_res_class2 , p_res_class3, p_res_class4 ) AND
source_rlm.resource_list_member_id = ra.resource_list_member_id AND
target_rlm.resource_list_id = source_rlm.resource_list_id AND
target_rlm.alias = source_rlm.alias AND
target_rlm.object_type = source_rlm.object_type AND
target_rlm.object_id = p_target_project_id AND
rf.res_format_id = target_rlm.res_format_id
GROUP BY
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
source_rlm.resource_list_member_id,
target_rlm.resource_list_member_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id,
ra.task_id,
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
UNION ALL
SELECT
ra.resource_assignment_id,
ra.project_assignment_id,
ra.named_role,
rf.role_enabled_flag,
ra.wbs_element_version_id,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
ra.ta_display_flag,
source_rlm.resource_list_member_id src_res_mem_list_id,
target_rlm.resource_list_member_id tar_res_mem_list_id,
pev.parent_structure_version_id,
ra.res_type_code,
ra.resource_class_code,
ra.resource_class_flag,
to_number(NULL),
to_number(NULL),
to_number(NULL),
ra.rate_based_flag,
ra.budget_version_id,
asgn.resource_list_member_id tr_res_mem_list_id,
ra.task_id,
'RES_ASSIGNMENT',
ra.person_id,
ra.job_id,
ra.organization_id,
ra.supplier_id,
ra.expenditure_type,
ra.event_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.project_role_id,
ra.item_category_id,
ra.person_type_code,
ra.bom_resource_id,
ra.non_labor_resource,
ra.inventory_item_id
FROM
pa_copy_asgmts_temp temp,
pa_proj_element_versions pev,
pa_resource_assignments ra,
pa_project_assignments asgn,
pa_resource_list_members source_rlm,
pa_resource_list_members target_rlm,
pa_res_formats_b rf
WHERE
pev.element_version_id = temp.src_elem_ver_id AND
pev.element_version_id = ra.wbs_element_version_id AND
ra.ta_display_flag IS NOT NULL AND
(ra.unplanned_flag = 'N' OR ra.unplanned_flag IS NULL) AND
source_rlm.resource_list_member_id = ra.resource_list_member_id AND
target_rlm.resource_list_id = source_rlm.resource_list_id AND
target_rlm.alias = source_rlm.alias AND
target_rlm.object_type = source_rlm.object_type AND
target_rlm.object_id = p_target_project_id AND
decode(ra.project_assignment_id, -1, NULL, ra.project_assignment_id) = asgn.assignment_id(+) AND
ra.resource_class_code IN ( p_res_class1 , p_res_class2 , p_res_class3, p_res_class4 ) AND
rf.res_format_id = target_rlm.res_format_id AND
NOT EXISTS
(SELECT 1 FROM pa_budget_lines bl WHERE bl.resource_assignment_id = ra.resource_assignment_id)
) ORDER BY 5;
SELECT pev.element_version_id, pevs.scheduled_start_date, pevs.scheduled_finish_date
FROM pa_copy_asgmts_temp temp, pa_proj_element_versions pev, pa_proj_elem_ver_schedule pevs
WHERE pev.element_version_id = pevs.element_version_id
AND pev.element_version_id = temp.src_elem_ver_id;
SELECT pev.element_version_id, pevs.scheduled_start_date, pevs.scheduled_finish_date
FROM pa_copy_asgmts_temp temp, pa_proj_element_versions pev, pa_proj_elem_ver_schedule pevs
WHERE pev.element_version_id = pevs.element_version_id
AND pev.element_version_id = temp.targ_elem_ver_id;
SELECT decode(rlab.control_flag, 'Y', 'N', 'Y'), rl.uncategorized_flag
FROM pa_resource_lists_all_bg rlab, pa_resource_lists rl
WHERE rlab.resource_list_id = c_resource_list_id
AND rlab.resource_list_id = rl.resource_list_id;
SELECT bv.budget_version_id, pfo.rbs_version_id
FROM pa_proj_element_versions pev, pa_budget_versions bv, pa_proj_fp_options pfo
WHERE pev.element_version_id = c_evid
AND bv.project_structure_version_id = pev.parent_structure_version_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
select nvl(PLAN_IN_MULTI_CURR_FLAG, 'N'), nvl(track_workplan_costs_flag, 'N'), nvl(cost_time_phased_code, 'N')
from pa_proj_fp_options
where fin_plan_type_id = (select fin_plan_type_id
from pa_fin_plan_types_b
where use_for_workplan_flag = 'Y')
and project_id = c_project_id
and fin_plan_option_level_code = 'PLAN_TYPE';
SELECT to_number(value)
INTO l_db_block_size
FROM v$parameter
WHERE name = 'db_block_size';
DELETE pa_copy_asgmts_temp;
/*Bug 4377886 : Included explicitly the column names in the INSERT statement
to remove the GSCC Warning File.Sql.33 */
-- Changed due to bug 4153366
INSERT INTO pa_copy_asgmts_temp
(
SRC_ELEM_VER_ID,
TARG_ELEM_VER_ID,
OLD_TASK_SCH_START,
OLD_TASK_SCH_END,
NEW_TASK_SCH_START,
NEW_TASK_SCH_END
)
VALUES
(p_src_elem_ver_id_tbl(i), p_targ_elem_ver_id_tbl(i),null,null,null,null);
-- creation or update of resource class TA
IF l_accum_people_qty > 0 THEN
IF l_people_class_index <> -999 THEN
IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
PA_DEBUG.write(x_module => L_Module,
x_msg => 'l_people_class_index: ' || l_people_class_index,
x_log_level => 3);
-- Bug 3831695, 3834509: update elem version id in the loop
IF i < l_ta_resource_assgt_tbl.COUNT+1 THEN
l_previous_elem_version_id := l_ta_wbs_elem_ver_tbl(i);
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
DELETE pa_copy_asgmts_temp;
select par.alias, restype.name Res_type from pa_resource_list_members par,
pa_res_types_vl restype
where par.fc_res_type_code = restype.RES_TYPE_CODE
and par.resource_list_member_id = p_res_list_member_id;
select restype.name Res_type from
pa_res_types_vl restype
where p_fc_res_type_code = restype.RES_TYPE_CODE;
select cost_Resource_list_id
from pa_proj_fp_options
where fin_plan_type_id = (select fin_plan_type_id
from pa_fin_plan_types_b
where use_for_workplan_flag = 'Y')
and project_id = p_project_id
and fin_plan_option_level_code = 'PLAN_TYPE';
SELECT wbs_element_version_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT parent_structure_version_id, project_id
from pa_proj_element_versions
where element_version_id = p_element_version_id;
pa_debug.g_err_stage:='check process update';
M_Status := PA_PROJECT_STRUCTURE_UTILS.GET_UPDATE_WBS_FLAG(
P_PROJECT_ID => l_project_id,
P_STRUCTURE_VERSION_ID => l_structure_version_id);
select min(ra.SCHEDULE_START_DATE)
from pa_resource_assignments ra
where resource_list_member_id = p_resource_list_member_id
and project_id = p_project_id
and budget_version_id = p_budget_version_id
and ('Y' <> p_unstaffed_only or nvl(project_assignment_id, -1) = -1);
select max(ra.SCHEDULE_END_DATE)
from pa_resource_assignments ra
where resource_list_member_id = p_resource_list_member_id
and project_id = p_project_id
and budget_version_id = p_budget_version_id
and ('Y' <> p_unstaffed_only or nvl(project_assignment_id, -1) = -1);
select count(distinct ra.unit_of_measure) cnt
from pa_resource_assignments ra
where ta_display_flag = 'Y' and
ra.project_id = p_project_id and
ra.budget_version_id = p_budget_version_id and
ra.resource_class_code = p_class;
select distinct ro.meaning project_role
from pa_project_assignments pa,
pa_proj_roles_v ro
where
pa.project_role_id = ro.project_role_id (+)
and pa.resource_list_member_id = p_resource_list_member_id
and pa.project_id = p_project_id;
select distinct pap.assignment_name Team_Role, pap.assignment_effort
from pa_project_assignments pap, pa_project_statuses stat
where
pap.resource_list_member_id = p_resource_list_member_id
and pap.project_id = p_project_id
and
pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+) and
nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not in
('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');
SELECT bl.txn_currency_code
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND rownum = 1;
SELECT bl.txn_currency_code
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND rownum = 1;
SELECT
SUM(bl.quantity) as planned_quantity,
SUM(bl.txn_burdened_cost) as planned_bur_cost_txn_cur,
SUM(bl.txn_raw_cost) as planned_raw_cost_txn_cur,
SUM(bl.init_quantity) as actual_quantity,
SUM(bl.txn_init_burdened_cost) as act_bur_cost_txn_cur,
SUM(bl.txn_init_raw_cost) as act_raw_cost_txn_cur,
SUM(bl.project_init_burdened_cost) as act_bur_cost_pc_cur,
SUM(bl.project_init_raw_cost) as act_raw_cost_pc_cur,
SUM(bl.init_burdened_cost) as act_bur_cost_pfc_cur,
SUM(bl.init_raw_cost) as act_raw_cost_pfc_cur,
AVG(nvl(bl.txn_cost_rate_override, bl.txn_standard_cost_rate)) as avg_raw_cost_rate,
AVG(bl.burden_cost_rate) as avg_bur_cost_rate
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
GROUP BY bl.resource_assignment_id;
SELECT bl.txn_currency_code as txn_currency_code
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND rownum = 1;
SELECT proj.project_currency_code as txn_currency_code
FROM pa_projects_all proj
WHERE proj.project_id = p_project_id
AND rownum = 1;
select resource_assignment_id
from pa_resource_assignments
where ta_display_flag = 'N'
and wbs_element_version_id = p_element_version_id
and project_id = p_project_id
and rownum = 1;
l_module_name VARCHAR2(80) := 'PA_PM_UPDATE_TASK_ASSIGNMENT';
SELECT budget_version_id, project_structure_version_id
FROM pa_budget_versions
WHERE budget_version_id = l_budget_version_id
AND project_id = l_project_id
AND project_structure_version_id is not null;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev,
pa_proj_elem_ver_structure ppevs,
pa_proj_structure_types ppst
WHERE
ppev.element_version_id = l_str_ver_id
and ppev.project_id = l_project_id
and ppev.object_type='PA_STRUCTURES'
and ppev.element_version_id=ppevs.element_version_id
and ppev.project_id=ppevs.project_id
and ppevs.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = 1);
select budget_version_id
from pa_budget_versions
where project_structure_version_id = l_structure_version_id
and project_id = p_project_id;
Pa_Debug.g_err_stage:= 'PA_TASK_ASSIGNMENT_UTILS :CHECK_EDIT_OK: Checking Security: UPDATE_TASK_ASSIGNMENT';
p_function_name => 'PA_PM_UPDATE_TASK_ASSIGNMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed);
Pa_Debug.g_err_stage:= 'PA_TASK_ASSIGNMENT_UTILS :CHECK_EDIT_OK: Checking Security: UPDATE_TASK_ASSIGNMENT: Suceess';
Pa_Debug.g_err_stage:= 'PA_TASK_ASSIGNMENT_UTILS :CHECK_EDIT_OK: Checking Security: Update/Query Project';
OR pa_security.allow_update(x_project_id => p_project_id ) = 'N'
THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
Pa_Debug.g_err_stage:= 'PA_TASK_ASSIGNMENT_UTILS :CHECK_EDIT_OK: Checking Security: Update/Query Project: Success';
select 'Y'
from pa_progress_rollup
where project_id = p_project_id
and object_Type = 'PA_STRUCTURES'
and structure_version_id is null
and structure_type = 'WORKPLAN'
and current_flag = 'Y';
SELECT UNCATEGORIZED_FLAG
FROM pa_resource_lists_all_bg
WHERE RESOURCE_LIST_ID = c_resource_list_id;