[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_planning_transaction(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_bvid IN NUMBER,
p_project_id in NUMBER,
p_task_id_tbl in SYSTEM.PA_NUM_TBL_TYPE,
p_effective_from_tbl in SYSTEM.PA_DATE_TBL_TYPE,
p_effective_to_tbl in SYSTEM.PA_DATE_TBL_TYPE,
p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE
) IS
l_task_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
select pev.element_version_id,parent_structure_version_id
from pa_proj_element_versions pev,pa_proj_elem_ver_structure pevs
where pev.project_id = c_project_id
and pev.project_id = pevs.project_id
and pev.proj_element_id = c_task_id
and pev.parent_structure_version_id = pevs.element_version_id
--and pevs.CURRENT_WORKING_FLAG = 'Y';
select task_number
from pa_tasks
where project_id = c_project_id
and task_id = c_task_id;
select pra.resource_assignment_id,
pra.unit_of_measure,
pra.project_assignment_id,
pra.organization_id,
pra.supplier_id,
pra.spread_curve_id,
pra.etc_method_code,
pra.mfc_cost_type_id,
pra.procure_resource_flag,
decode(pra.use_task_schedule_flag,'Y','Y','N') as use_task_schedule_flag,
pra.planning_start_date,
pra.planning_end_date,
pra.schedule_start_date,
pra.schedule_end_date,
pra.sp_fixed_date,
pra.named_role
from pa_resource_assignments pra, pa_resource_asgn_curr prc
where pra.budget_version_id = c_bv_Id
and pra.task_id = c_Task_Id
and pra.resource_list_member_id = c_rlmi_id
and pra.resource_assignment_id = prc.resource_assignment_id
and prc.txn_currency_code = c_currency_code;
select alias resource_alias,
resource_class_flag,
resource_class_code,
--resource_class_id,
res_type_code,
fc_res_type_code
from pa_resource_list_members
where resource_list_member_id = p_rlmi_id;
select distinct burden_cost_rate
from pa_ci_direct_cost_details
where task_id = c_task_id
and resource_list_member_id = c_rlmi_id
and currency_code = c_currency_code
and ci_id = (select ci_id from pa_budget_versions where budget_version_id = p_bvid)
and burden_cost_rate is not null;
select sum(burdened_cost)/sum(raw_cost)
from pa_ci_supplier_details
where task_id = c_task_id
and resource_list_member_id = c_rlmi_id
and currency_code = c_currency_code
and ci_id = (select ci_id from pa_budget_versions where budget_version_id = p_bvid)
and burdened_cost is not null and raw_cost is not null;
l_api_name CONSTANT varchar2(30) := 'CI.update_planning_trx';
pa_fp_planning_transaction_pub.update_planning_transactions(
p_context => 'BUDGET'
,p_calling_context => NULL
,p_struct_elem_version_id => l_struct_elem_version_id
,p_budget_version_id => p_bvid
,p_task_elem_version_id_tbl => l_task_elem_version_id_tbl
,p_task_name_tbl => l_task_name_tbl
,p_task_number_tbl => l_task_number_tbl
,p_start_date_tbl => l_in_start_date_tbl
,p_end_date_tbl => l_in_end_date_tbl
,p_planned_people_effort_tbl => l_planned_people_effort_tbl
,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
,p_resource_list_member_id_tbl => p_rlmi_id_tbl
,p_assignment_description_tbl => l_assignment_description_tbl
,p_project_assignment_id_tbl => l_project_assignment_id_tbl
,p_resource_alias_tbl => l_planning_resource_alias_tbl
,p_resource_class_flag_tbl => l_resource_class_flag_tbl
,p_resource_class_code_tbl => l_resource_class_code_tbl
,p_resource_class_id_tbl => l_resource_class_id_tbl
,p_res_type_code_tbl => l_res_type_code_tbl
,p_resource_code_tbl => l_resource_code_tbl
,p_resource_name => l_resource_name
,p_person_id_tbl => l_person_id_tbl
,p_job_id_tbl => l_job_id_tbl
,p_person_type_code => l_person_type_code_tbl
,p_bom_resource_id_tbl => l_bom_resource_id_tbl
,p_non_labor_resource_tbl => l_non_labor_resource_tbl
,p_inventory_item_id_tbl => l_inventory_item_id_tbl
,p_item_category_id_tbl => l_item_category_id_tbl
,p_project_role_id_tbl => l_project_role_id_tbl
,p_project_role_name_tbl => l_project_role_name_tbl
,p_organization_id_tbl => l_organization_id_tbl
,p_organization_name_tbl => l_organization_name_tbl
,p_fc_res_type_code_tbl => l_fc_res_type_code_tbl
,p_financial_category_code_tbl => l_financial_category_code_tbl
,p_expenditure_type_tbl => l_expenditure_type_tbl
,p_expenditure_category_tbl => l_expenditure_category_tbl
,p_event_type_tbl => l_event_type_tbl
,p_revenue_category_code_tbl => l_revenue_category_code_tbl
,p_incurred_by_res_flag_tbl => l_incurred_by_res_flag_tbl
,p_incur_by_res_class_code_tbl => l_incur_by_res_class_code_tbl
,p_incur_by_role_id_tbl => l_incur_by_role_id_tbl
,p_supplier_id_tbl => l_supplier_id_tbl
,p_unit_of_measure_tbl => l_unit_of_measure_tbl,
p_spread_curve_id_tbl => l_spread_curve_id_tbl,
p_etc_method_code_tbl => l_etc_method_code_tbl,
p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
p_procure_resource_flag_tbl => l_procure_resource_flag_tbl,
p_incur_by_resource_code_tbl => l_Incur_by_resource_code_tbl,
p_incur_by_resource_name_tbl => l_incur_by_resource_name_tbl,
p_use_task_schedule_flag_tbl => l_use_task_schedule_flag_tbl,
p_planning_start_date_tbl => l_planning_start_date_tbl,
p_planning_end_date_tbl => l_planning_end_date_tbl,
p_schedule_start_date_tbl => l_schedule_start_date_tbl,
p_schedule_end_date_tbl => l_schedule_end_date_tbl,
p_quantity_tbl => l_total_quantity_tbl,
p_currency_code_tbl => p_currency_code_tbl,
p_txn_currency_override_tbl => l_override_currency_code_tbl,
p_raw_cost_tbl => l_total_raw_cost_tbl,
p_burdened_cost_tbl => l_burdened_cost_tbl,
p_revenue_tbl => l_revenue_tbl,
p_cost_rate_tbl => l_raw_cost_rate_tbl,
p_cost_rate_override_tbl => l_cost_rate_override_tbl,
p_burdened_rate_tbl => l_burdened_rate_tbl,
p_burdened_rate_override_tbl => l_burdened_rate_override_tbl,
p_bill_rate_tbl => l_bill_rate_tbl,
p_bill_rate_override_tbl => l_bill_rate_override_tbl,
p_billable_percent_tbl => l_billable_percent_tbl,
p_sp_fixed_date_tbl => l_sp_fixed_date_tbl,
p_named_role_tbl => l_named_role_tbl,
p_financial_category_name_tbl => l_financial_category_name_tbl,
p_supplier_name_tbl => l_supplier_name_tbl,
p_attribute_category_tbl => l_attribute_category_tbl,
p_attribute1_tbl => l_attribute1_tbl,
p_attribute2_tbl => l_attribute1_tbl,
p_attribute3_tbl => l_attribute1_tbl,
p_attribute4_tbl => l_attribute1_tbl,
p_attribute5_tbl => l_attribute1_tbl,
p_attribute6_tbl => l_attribute1_tbl,
p_attribute7_tbl => l_attribute1_tbl,
p_attribute8_tbl => l_attribute1_tbl,
p_attribute9_tbl => l_attribute1_tbl,
p_attribute10_tbl => l_attribute1_tbl,
p_attribute11_tbl => l_attribute1_tbl,
p_attribute12_tbl => l_attribute1_tbl,
p_attribute13_tbl => l_attribute1_tbl,
p_attribute14_tbl => l_attribute1_tbl,
p_attribute15_tbl => l_attribute1_tbl,
p_attribute16_tbl => l_attribute1_tbl,
p_attribute17_tbl => l_attribute1_tbl,
p_attribute18_tbl => l_attribute1_tbl,
p_attribute19_tbl => l_attribute1_tbl,
p_attribute20_tbl => l_attribute1_tbl,
p_attribute21_tbl => l_attribute1_tbl,
p_attribute22_tbl => l_attribute1_tbl,
p_attribute23_tbl => l_attribute1_tbl,
p_attribute24_tbl => l_attribute1_tbl,
p_attribute25_tbl => l_attribute1_tbl,
p_attribute26_tbl => l_attribute1_tbl,
p_attribute27_tbl => l_attribute1_tbl,
p_attribute28_tbl => l_attribute1_tbl,
p_attribute29_tbl => l_attribute1_tbl,
p_attribute30_tbl => l_attribute1_tbl
,p_apply_progress_flag => 'N'
,p_scheduled_delay => l_scheduled_delay
,p_pji_rollup_required => 'Y'
,p_upd_cost_amts_too_for_ta_flg => 'N'
,p_distrib_amts => 'Y'
,p_direct_expenditure_type_tbl => l_direct_expenditure_type_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
end update_planning_transaction;
procedure delete_planning_transaction(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_bvid IN number,
p_project_id IN number,
p_task_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
p_currency_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE,
p_rlmi_tbl IN SYSTEM.PA_NUM_TBL_TYPE
) IS
cursor get_assignment_details(c_bvId number,
c_task_id number,
c_rlmi_id number,
c_currency_code varchar2) is
select pra.RESOURCE_ASSIGNMENT_ID, ppe.ELEMENT_NUMBER, ppe.NAME
from pa_resource_assignments pra, pa_proj_elements ppe, pa_tasks pt,
pa_resource_asgn_curr prc
WHERE pra.budget_version_id = c_bvId
and pra.task_id = c_task_id
and pra.RESOURCE_LIST_MEMBER_ID = c_rlmi_id
and pra.resource_assignment_id = prc.resource_assignment_id
and prc.txn_currency_code = c_currency_code
and pt.task_id = ppe.proj_element_id;
select pev.element_version_id
from pa_proj_element_versions pev, pa_proj_elem_ver_structure pevs
where pev.project_id = c_proj_id and pev.project_id=pevs.project_id
and pev.proj_element_id=c_task_id
and pev.parent_structure_version_id=pevs.element_version_id
--and pevs.CURRENT_WORKING_FLAG='Y';
l_api_name CONSTANT VARCHAR2(30) := 'CI.delete_planning_trx';
pa_fp_planning_transaction_pub.delete_planning_transactions(
p_context => 'BUDGET'
,p_calling_context => NULL
,p_task_or_res => 'ASSIGNMENT'
,p_element_version_id_tbl => l_task_elem_version_id_tbl
,p_task_number_tbl => l_task_number_tbl
,p_task_name_tbl => l_task_name_tbl
,p_resource_assignment_tbl => l_res_assgn_tbl
,p_validate_delete_flag => 'N'
,p_currency_code_tbl => p_currency_tbl
,p_calling_module => NULL
,p_task_id_tbl => p_task_tbl
,p_rbs_element_id_tbl => l_rbs_element_id_tbl
,p_rate_based_flag_tbl => l_rate_based_flag_tbl
,p_resource_class_code_tbl => l_resource_class_code_tbl
,p_rollup_required_flag => 'Y'
,p_pji_rollup_required => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
end delete_planning_transaction;
procedure insert_planning_transaction(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
P_BVID IN NUMBER,
P_PROJECT_ID IN NUMBER,
P_TASK_ID_TBL IN SYSTEM.pa_num_tbl_type,
P_RLMI_ID_TBL IN SYSTEM.pa_num_tbl_type,
P_CURRENCY_CODE_TBL IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE,
P_QUANTITY_TBL IN SYSTEM.pa_num_tbl_type,
P_RAW_COST_TBL IN SYSTEM.pa_num_tbl_type
) IS
--{
l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
select pev.element_version_id
from pa_proj_element_versions pev, pa_proj_elem_ver_structure pevs
where pev.project_id = c_proj_id
and pev.project_id=pevs.project_id
and pev.proj_element_id=c_task_id
and pev.parent_structure_version_id=pevs.element_version_id
-- and pevs.CURRENT_WORKING_FLAG='Y';
l_api_name CONSTANT varchar2(30) := 'CI.insert_planning_trx';
end insert_planning_transaction;
select min(effective_from),
max(effective_to),
sum(quantity),
sum(raw_cost),
count(*)
from pa_ci_direct_cost_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_member_id
and currency_code = p_currency_code;
select min(from_change_date) effective_from,
max(to_change_date) effective_to,
sum(NULL) quantity,
sum(raw_cost),
count(*)
from pa_ci_supplier_details
where ci_id = p_ci_id
and task_id = p_task_id
and resource_list_member_id = p_resource_list_member_id
and currency_code = p_currency_code;
select 'Y', pra.resource_assignment_id,
total_quantity,
total_txn_raw_cost
from pa_resource_assignments pra, pa_resource_asgn_curr prac
where pra.budget_version_id = c_bvid and pra.task_id = c_task_id
and pra.resource_list_member_id = c_rlmi_id
and prac.txn_currency_code = c_currency_code
and prac.resource_assignment_id = pra.resource_assignment_id;
select txn_currency_code
from pa_resource_assignments pra, pa_resource_asgn_curr prc
where pra.resource_assignment_id = prc.resource_assignment_id
and pra.budget_version_id = c_bvid
and pra.task_id = c_task_id
and pra.resource_list_member_id = c_rlmi_id
and prc.total_quantity is not null;
DeleteDCTblRecs PaCiDirCostTblType;
l_delete varchar2(1) := 'N';
l_update varchar2(1) := 'N';
if p_action_type = 'DELETE' then
for i in rollup_task_tbl.first..rollup_task_tbl.last loop -- delete loop
l_delete := 'N';
l_update := 'N';
l_delete := 'Y';
l_update := 'Y';
if l_delete = 'Y' then
del_task_tbl.extend(1);
if l_update = 'Y' then
k := k + 1;
end loop; -- delete loop
delete_planning_transaction(p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bvid => p_bvid,
p_project_id => p_project_id,
p_task_tbl => del_task_tbl,
p_currency_tbl => del_currency_tbl,
p_rlmi_tbl => del_rlmi_tbl);
update_planning_transaction(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bvid => p_bvid,
p_project_id => p_project_id,
p_task_id_tbl => upd_task_tbl,
p_effective_from_tbl => upd_effective_from,
p_effective_to_tbl => upd_effective_to,
p_rlmi_id_tbl => upd_rlmi_tbl,
p_quantity_tbl => upd_quantity_tbl,
p_raw_cost_tbl => upd_raw_cost_tbl,
p_currency_code_tbl => upd_currency_tbl
);
end if; -- if pa_action_type = 'DELETE'
if p_action_type in ('UPDATE', 'INSERT') then
if rollup_task_tbl.count > 0 then
for i in rollup_task_tbl.first..rollup_task_tbl.last loop
if rollup_ra_exists(i) = 'Y' then
upd_task_tbl.extend(1);
update_planning_transaction(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_bvid => p_bvid,
p_project_id => p_project_id,
p_task_id_tbl => upd_task_tbl,
p_effective_from_tbl => upd_effective_from,
p_effective_to_tbl => upd_effective_to,
p_rlmi_id_tbl => upd_rlmi_tbl,
p_quantity_tbl => upd_quantity_tbl,
p_raw_cost_tbl => upd_raw_cost_tbl,
p_currency_code_tbl => upd_currency_tbl
);
insert_planning_transaction(p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
P_BVID => p_bvid,
P_PROJECT_ID => p_project_id,
P_TASK_ID_TBL => ins_task_tbl,
P_RLMI_ID_TBL => ins_rlmi_tbl,
P_CURRENCY_CODE_TBL => ins_currency_tbl,
P_QUANTITY_TBL => ins_quantity_tbl,
P_RAW_COST_TBL => ins_raw_cost_tbl
);
select planning_resource_rate, burden_cost_rate
into i_resource_rate, i_burden_cost_rate
from pa_ci_direct_cost_details
where ci_id = p_ci_id
and task_id = p_task_id_tbl(j)
and resource_list_member_id = p_rlmi_id_tbl(j)
and currency_code = l_rate_based_res_curr
and rownum < 2;
insert into pa_ci_direct_cost_details(
dc_line_id
,ci_id
,project_id
,task_id
,expenditure_type
,resource_list_member_id
,unit_of_measure
,currency_code
,quantity
,planning_resource_rate
,raw_cost
,burdened_cost
,burden_cost_rate
,resource_assignment_id
,effective_from
,effective_to
,change_reason_code
,change_description
,created_by
,creation_date
,last_update_by
,last_update_date
,last_update_login)
select pa_ci_dir_cost_details_s.nextval
,pc.ci_id
,pc.project_id
,pc.task_id
,pc.expenditure_type
,pc.resource_list_member_id
,pc.unit_of_measure
,prc.txn_currency_code
,pc.quantity
,nvl(i_resource_rate, prc.txn_average_raw_cost_rate)
,nvl(i_resource_rate, prc.txn_average_raw_cost_rate) * pc.quantity
,nvl(i_burden_cost_rate, prc.txn_average_burden_cost_rate) * pc.quantity
,nvl(i_burden_cost_rate, prc.txn_average_burden_cost_rate)
,prc.resource_assignment_id
,pra.planning_start_date
,pra.planning_end_date
,pc.change_reason_code
,pc.change_description
,pc.created_by
,pc.creation_date
,pc.last_update_by
,pc.last_update_date
,pc.last_update_login
from pa_ci_direct_cost_details pc,
pa_resource_assignments pra,
pa_resource_asgn_curr prc
where pc.ci_id = p_ci_id
and pc.dc_line_id = p_line_id_tbl(j)
and pc.resource_assignment_id is null
and pra.task_id = pc.task_id
and pra.resource_list_member_id = pc.resource_list_member_id
and pra.resource_assignment_id = prc.resource_assignment_id
and prc.txn_currency_code = l_rate_based_res_curr
and pra.budget_version_id = p_bvid;
update pa_ci_direct_cost_details a
set quantity = NULL
where a.ci_id = p_ci_id
and dc_line_id = p_line_id_tbl(j);
update pa_ci_direct_cost_details a
set (quantity, raw_cost, burdened_cost) =
(select sum(quantity) + a.quantity,
(sum(quantity) + a.quantity) * a.planning_resource_rate,
(sum(quantity) + a.quantity) * a.burden_cost_rate
from pa_ci_direct_cost_details b
where b.ci_id = p_ci_id
and b.dc_line_id = p_line_id_tbl(j))
where a.ci_id = p_ci_id
and currency_code = l_rate_based_res_curr
and task_id = rollup_task_tbl(i)
and resource_list_member_id = rollup_rlmi_tbl(i)
and expenditure_type = (select expenditure_type
from pa_ci_direct_cost_details
where dc_line_id = p_line_id_tbl(j));
update pa_ci_direct_cost_details a
set quantity = NULL
where a.ci_id = p_ci_id
and dc_line_id = p_line_id_tbl(j);