[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- Add/Update Planning Transactions API.
Note: This api is called only for workplan.
Parameters:
IN 1) p_struct_elem_version_id
- project_structure_version_id
=======================================================================*/
FUNCTION Get_Wp_Budget_Version_Id (
p_struct_elem_version_id IN pa_budget_versions.project_structure_version_id%TYPE
)
RETURN NUMBER
IS
l_budget_version_id NUMBER;
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_budget_versions
WHERE project_structure_version_id = p_struct_elem_version_id
AND nvl(wp_version_flag,'N') = 'Y';
- Add/Update Planning Transactions API
=======================================================================*/
PROCEDURE Get_Res_Class_Rlm_Ids
(p_project_id IN pa_projects_all.project_id%TYPE,
p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE,
x_people_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
x_equip_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
x_fin_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
x_mat_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
--Start of variables used for debugging
l_msg_count NUMBER :=0;
SELECT resource_list_member_id, resource_class_code
FROM pa_resource_list_members,
(SELECT control_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id) rl_control_flag
WHERE resource_list_id = p_resource_list_id
AND ((rl_control_flag.control_flag = 'N' AND
object_type = 'PROJECT' AND
object_id = p_project_id)
OR
(rl_control_flag.control_flag = 'Y' AND
object_type = 'RESOURCE_LIST' AND
object_id = p_resource_list_id))
AND nvl(resource_class_flag,'N') = 'Y';
SELECT nvl(pt.transaction_start_date, nvl(pt.actual_start_date, nvl(pt.estimated_start_date, pt.start_date))),
decode(pt.transaction_start_date,
to_date(null),decode(pt.actual_start_date,
to_date(null),decode(pt.estimated_start_date,
to_date(null),decode(pt.start_date,
to_date(null),to_date(null),
pt.completion_date),
pt.estimated_finish_date),
pt.actual_finish_date),
pt.transaction_completion_date)
INTO x_planning_start_date_tbl(i)
,x_planning_end_date_tbl(i)
FROM pa_struct_task_wbs_v pt
WHERE pt.element_Version_id=p_element_version_id_tbl(i)
AND pt.parent_structure_version_id=p_project_structure_version_id;
* Since the below select is returning no data found, fixingit to read the
* project start and end date and commenting the below
SELECT nvl(pelm.actual_start_date, nvl(pelm.estimated_start_date, pelm.scheduled_start_date)),
decode(pelm.actual_start_date,
null,decode(pelm.estimated_start_date,
null,decode(pelm.scheduled_start_date,
null,null,
pelm.scheduled_finish_date),
pelm.estimated_finish_date),
pelm.actual_finish_date)
INTO l_parent_struct_st_dt
,l_parent_struct_end_dt
FROM pa_proj_elem_ver_schedule pelm
WHERE pelm.element_version_id=p_project_structure_version_id;
SELECT start_date,decode(start_date, null, to_Date(null), completion_date)
INTO l_parent_struct_st_dt ,l_parent_struct_end_dt
FROM pa_projects_all
where project_id = p_project_id;
PROCEDURE call_update_rep_lines_api
(
p_source IN VARCHAR2
,p_budget_version_id IN pa_budget_versions.budget_Version_id%TYPE
,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_start_date_tbl IN SYSTEM.pa_date_tbl_type
,p_end_date_tbl IN SYSTEM.pa_date_tbl_type
,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type
,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type
,p_project_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_project_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_project_revenue_tbl IN SYSTEM.pa_num_tbl_type
,p_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
,p_revenue_tbl IN SYSTEM.pa_num_tbl_type
,p_cost_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_revenue_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_burden_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_other_rejection_code IN SYSTEM.pa_varchar2_30_tbl_type
,p_pc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_pfc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
,p_rbs_element_id_tbl IN SYSTEM.pa_num_tbl_type
,p_task_id_tbl IN SYSTEM.pa_num_tbl_type
,p_res_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type
,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
,p_qty_sign IN NUMBER -- for bug 4543744
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
--Start of variables used for debugging
l_msg_count NUMBER :=0;
l_module_name VARCHAR2(100):='PAFPPTUB.call_update_rep_lines_api';
l_rows_inserted NUMBER:=0;
PA_DEBUG.Set_Curr_Function( p_function => 'pafpptub.call_update_rep_lines_api',
p_debug_mode => l_debug_mode );
SELECT project_id INTO l_project_id
FROM pa_budget_versions
WHERE budget_version_id=p_budget_version_id;
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,PERIOD_NAME
,CALENDAR_TYPE
,START_DATE
,END_DATE
,RBS_ELEMENT_ID
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,ROLLUP_TYPE
,TXN_CURRENCY_CODE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,PRJ_CURRENCY_CODE
,PRJ_RAW_COST
,PRJ_BURDENED_COST
,PRJ_REVENUE
,PFC_CURRENCY_CODE
,PFC_RAW_COST
,PFC_BURDENED_COST
,PFC_REVENUE
,QUANTITY
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG)
SELECT
p.project_id
,p.org_id
,pra.task_id
,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
,pbl.period_name
,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
,pbl.start_date
,pbl.end_date
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , pra.rbs_element_id, 'NEW', tmp.rbs_element_id)
,pfo.rbs_version_id
,pbv.budget_version_id
,pfo.fin_plan_type_id
,pbv.wp_version_flag
,'W'
,pbl.txn_currency_code
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_raw_cost, 'NEW', pbl.txn_raw_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_burdened_cost, 'NEW', pbl.txn_burdened_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.txn_revenue, 'NEW', pbl.txn_revenue)
,p.project_currency_code
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_raw_cost, 'NEW', pbl.project_raw_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_burdened_cost, 'NEW', pbl.project_burdened_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.project_revenue, 'NEW', pbl.project_revenue)
,p.projfunc_currency_code
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.raw_cost, 'NEW', pbl.raw_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.burdened_cost, 'NEW', pbl.burdened_cost)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.revenue, 'NEW', pbl.revenue)
,DECODE(rbs_dummy.rbs_elem_id, 'OLD' , 0-pbl.quantity, 'NEW', pbl.quantity)
,pra.resource_class_code
,pra.rate_based_flag
FROM pa_projects_all p
,pa_resource_assignments pra
,pa_budget_versions pbv
,pa_proj_fp_options pfo
,pa_rbs_plans_out_tmp tmp
,pa_budget_lines pbl
,(SELECT 'OLD' as rbs_elem_id
FROM DUAL
UNION ALL
SELECT 'NEW' as rbs_elem_id
FROM DUAL) rbs_dummy
WHERE p.project_id=pbv.project_id
AND pbv.budget_version_id=p_budget_Version_id
AND pra.resource_assignment_id=tmp.source_id
AND pbv.budget_version_id=pra.budget_version_id
AND pfo.fin_plan_version_id=pbv.budget_Version_id
AND pra.rbs_element_id <> tmp.rbs_element_id
AND pbl.resource_assignment_id=pra.resource_assignment_id
AND pbl.cost_rejection_code IS NULL
AND pbl.revenue_rejection_code IS NULL
AND pbl.burden_rejection_code IS NULL
AND pbl.other_rejection_code IS NULL
AND pbl.pc_cur_conv_rejection_code IS NULL
AND pbl.pfc_cur_conv_rejection_code IS NULL
AND pbl.start_date <= nvl(pbv.etc_start_date, pbl.start_date+1);
l_rows_inserted := SQL%ROWCOUNT;
pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
pa_debug.g_err_stage:='Selectiong from PA_FP_RA_MAP_TMP ';
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,PERIOD_NAME
,CALENDAR_TYPE
,START_DATE
,END_DATE
,RBS_ELEMENT_ID
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,ROLLUP_TYPE
,TXN_CURRENCY_CODE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,PRJ_CURRENCY_CODE
,PRJ_RAW_COST
,PRJ_BURDENED_COST
,PRJ_REVENUE
,PFC_CURRENCY_CODE
,PFC_RAW_COST
,PFC_BURDENED_COST
,PFC_REVENUE
,QUANTITY
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG)
SELECT
p.project_id
,p.org_id
,pra.task_id
,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
,pbl.period_name
,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
,pbl.start_date
,pbl.end_date
,pra.rbs_element_id
,pfo.rbs_version_id
,pbv.budget_version_id
,pfo.fin_plan_type_id
,pbv.wp_version_flag
,'W'
,pbl.txn_currency_code
,pbl.txn_raw_cost
,pbl.txn_burdened_cost
,pbl.txn_revenue
,p.project_currency_code
,pbl.project_raw_cost
,pbl.project_burdened_cost
,pbl.project_revenue
,p.projfunc_currency_code
,pbl.raw_cost
,pbl.burdened_cost
,pbl.revenue
,pbl.quantity
,pra.resource_class_code
,pra.rate_based_flag
FROM pa_projects_all p
,pa_resource_assignments pra
,pa_budget_versions pbv
,pa_proj_fp_options pfo
,pa_fp_ra_map_tmp tmp
,pa_budget_lines pbl
WHERE p.project_id=pbv.project_id
AND pbv.budget_version_id=p_budget_version_id
AND pra.resource_assignment_id=tmp.target_res_assignment_id
AND pbv.budget_version_id=pra.budget_version_id
AND pfo.fin_plan_version_id=pbv.budget_Version_id
AND pbl.resource_assignment_id=pra.resource_assignment_id
AND pbl.cost_rejection_code IS NULL
AND pbl.revenue_rejection_code IS NULL
AND pbl.burden_rejection_code IS NULL
AND pbl.other_rejection_code IS NULL
AND pbl.pc_cur_conv_rejection_code IS NULL
AND pbl.pfc_cur_conv_rejection_code IS NULL ;
l_rows_inserted := SQL%ROWCOUNT;
pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
pa_debug.g_err_stage:='Selectiong from PL-SQL ';
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,PERIOD_NAME
,CALENDAR_TYPE
,START_DATE
,END_DATE
,RBS_ELEMENT_ID
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,ROLLUP_TYPE
,TXN_CURRENCY_CODE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,PRJ_CURRENCY_CODE
,PRJ_RAW_COST
,PRJ_BURDENED_COST
,PRJ_REVENUE
,PFC_CURRENCY_CODE
,PFC_RAW_COST
,PFC_BURDENED_COST
,PFC_REVENUE
,QUANTITY
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG)
SELECT
p.project_id
,p.org_id
,p_task_id_tbl(i)
,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
,p_period_name_tbl(i)
,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
,p_start_date_tbl(i)
,p_end_date_tbl(i)
,p_rbs_element_id_tbl(i)
,pfo.rbs_version_id
,pbv.budget_version_id
,pfo.fin_plan_type_id
,pbv.wp_version_flag
,'W'
,p_txn_currency_code_tbl(i)
,p_txn_raw_cost_tbl(i)
,p_txn_burdened_cost_tbl(i)
,p_txn_revenue_tbl(i)
,p.project_currency_code
,p_project_raw_cost_tbl(i)
,p_project_burdened_cost_tbl(i)
,p_project_revenue_tbl(i)
,p.projfunc_currency_code
,p_raw_cost_tbl(i)
,p_burdened_cost_tbl(i)
,p_revenue_tbl(i)
,p_quantity_tbl(i)
,p_res_class_code_tbl(i)
,p_rate_based_flag_tbl(i)
FROM pa_projects_all p,
pa_proj_fp_options pfo,
pa_budget_versions pbv
WHERE p.project_id=pbv.project_id
AND pbv.budget_version_id=p_budget_version_id
AND pfo.fin_plan_version_id=p_budget_version_id
AND p_cost_rejection_code_tbl(i) IS NULL
AND p_revenue_rejection_code_tbl(i) IS NULL
AND p_burden_rejection_code_tbl(i) IS NULL
AND p_other_rejection_code(i) IS NULL
AND p_pc_cur_conv_rej_code_tbl(i) IS NULL
AND p_pfc_cur_conv_rej_code_tbl(i) IS NULL ;
l_rows_inserted := SQL%ROWCOUNT;
pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,PERIOD_NAME
,CALENDAR_TYPE
,START_DATE
,END_DATE
,RBS_ELEMENT_ID
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,ROLLUP_TYPE
,TXN_CURRENCY_CODE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,PRJ_CURRENCY_CODE
,PRJ_RAW_COST
,PRJ_BURDENED_COST
,PRJ_REVENUE
,PFC_CURRENCY_CODE
,PFC_RAW_COST
,PFC_BURDENED_COST
,PFC_REVENUE
,QUANTITY
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG)
SELECT
p.project_id
,p.org_id
,pra.task_id
,decode(pbv.wp_version_flag,'Y',pbv.project_structure_version_id,l_fin_structure_ver_id)
,pbl.period_name
,nvl(pfo.cost_time_phased_code,nvl(revenue_time_phased_code,all_time_phased_code))
,pbl.start_date
,pbl.end_date
,pra.rbs_element_id
,pfo.rbs_version_id
,pbv.budget_version_id
,pfo.fin_plan_type_id
,pbv.wp_version_flag
,'W'
,pbl.txn_currency_code
,pbl.txn_raw_cost * p_qty_sign
,pbl.txn_burdened_cost * p_qty_sign
,pbl.txn_revenue * p_qty_sign
,p.project_currency_code
,pbl.project_raw_cost * p_qty_sign
,pbl.project_burdened_cost * p_qty_sign
,pbl.project_revenue * p_qty_sign
,p.projfunc_currency_code
,pbl.raw_cost * p_qty_sign
,pbl.burdened_cost * p_qty_sign
,pbl.revenue * p_qty_sign
,pbl.quantity * p_qty_sign
,pra.resource_class_code
,pra.rate_based_flag
FROM pa_projects_all p,
pa_proj_fp_options pfo,
pa_budget_versions pbv,
pa_budget_lines pbl,
pa_resource_assignments pra
WHERE p.project_id=pbv.project_id
AND pbv.budget_version_id=p_budget_version_id
AND pfo.fin_plan_version_id=p_budget_version_id
AND pbl.resource_assignment_id= pra.resource_assignment_id
AND pbv.budget_version_id= pra.budget_version_id
AND pbl.cost_rejection_code IS NULL
AND pbl.revenue_rejection_code IS NULL
AND pbl.burden_rejection_code IS NULL
AND pbl.other_rejection_code IS NULL
AND pbl.pc_cur_conv_rejection_code IS NULL
AND pbl.pfc_cur_conv_rejection_code IS NULL ;
l_rows_inserted := SQL%ROWCOUNT;
pa_debug.g_err_stage:='No of rows inserted = '||l_rows_inserted;
IF nvl(l_rows_inserted,0) >0 THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='Calling the PJI API';
pa_debug.write('xxxxxxx','x_return_status before plan update '||x_return_status,5);
/*Bug 5073350. Commented out this IF condition as the plan_update api
has to be called for each plan version.*/
--IF p_source <> 'POPULATE_PJI_TABLE' THEN
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE
(p_plan_version_id => p_budget_version_id -- Added for bug 4218331
,x_msg_code =>l_msg_code
,x_return_status =>x_return_status);
pa_debug.write('xxxxxxx','x_return_status from plan update '||x_return_status,5);
pa_debug.g_err_stage:='Called API PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE returned error';
pa_debug.g_err_stage:='Leaving call_update_rep_lines_api';
,p_procedure_name => 'call_update_rep_lines_api');
END call_update_rep_lines_api;