[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO PA_FP_CALCULATE_LOG
(SESSIONID
,SEQ_NUMBER
,LOG_MESSAGE)
VALUES
(userenv('sessionid')
,HR.PAY_US_GARN_FEE_RULES_S.nextval
,substr(P_MSG,1,240)
);
/* This is the main api called from calculate, budget generation process to update the
* reporting PJI data when budget lines are created,updated or deleted.
* The following params values must be passed
* p_activity_code 'UPDATE',/'DELETE'
* p_calling_module name of API, for calculate 'CALCULATE_API'
* p_start_date BudgetLine StartDate
* p_end_date BudgetLine Enddate
* If activity = 'UPDATE' then all the amounts and currency columns must be passed
* if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB and passed in params will be ignored
* NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
* AND CALL THIS API ONLY IF THERE ARE NO REJECTION CODES STAMPED ON THE BUDGET LINES
* NOTE: As of IPM, we ignore rejection codes stamped on budget lines for the purpose of updating PJI data.
*/
PROCEDURE update_reporting_lines
(p_calling_module IN Varchar2 Default 'CALCULATE_API'
,p_activity_code IN Varchar2 Default 'UPDATE'
,p_budget_version_id IN Number
,p_budget_line_id IN Number
,p_resource_assignment_id IN Number
,p_start_date IN Date
,p_end_date IN Date
,p_period_name IN Varchar2
,p_txn_currency_code IN Varchar2
,p_quantity IN Number
,p_txn_raw_cost IN Number
,p_txn_burdened_cost IN Number
,p_txn_revenue IN Number
,p_project_currency_code IN Varchar2
,p_project_raw_cost IN Number
,p_project_burdened_cost IN Number
,p_project_revenue IN Number
,p_projfunc_currency_code IN Varchar2
,p_projfunc_raw_cost IN Number
,p_projfunc_burdened_cost IN Number
,p_projfunc_revenue IN Number
,x_msg_data OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY Number
,x_return_status OUT NOCOPY Varchar2
) IS
l_msg_count Number := 0;
SELECT DECODE(nvl(pbv.wp_version_flag,'N'),'Y',pbv.project_structure_version_id,
PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
FROM pa_budget_versions pbv
WHERE pbv.budget_version_id = p_budget_version_id;
SELECT pbv.budget_version_id
,ppa.org_id
,ppfo.rbs_version_id
,pbv.fin_plan_type_id
/* Bug fix :3839761 ,nvl(pbv.project_structure_version_id,
--PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
*/
,pbv.wp_version_flag
,decode(pbv.version_type, 'COST',ppfo.cost_time_phased_code,
'REVENUE',ppfo.revenue_time_phased_code,
ppfo.all_time_phased_code) time_phase_code
,ra.project_id
,ra.task_id
,ra.rbs_element_id
,ra.cbs_element_id --bug#16200605
,ra.resource_class_code
,ra.rate_based_flag
FROM pa_projects_all ppa
,pa_budget_versions pbv
,pa_proj_fp_options ppfo
,pa_resource_assignments ra
WHERE ppa.project_id = pbv.project_id
AND pbv.budget_version_id = ppfo.fin_plan_version_id
AND pbv.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = p_resource_assignment_id
AND ra.budget_version_id = pbv.budget_version_id;
l_num_rows_inserted Number;
pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.update_reporting_lines');
print_msg('Inside Update reporting Lines api');
IF p_activity_code = 'DELETE' AND p_budget_line_id is NULL Then
l_stage := 20;
If p_activity_code in ('UPDATE','DELETE') Then
l_stage := 30;
SELECT bl.start_date
,bl.end_date
,bl.period_name
,bl.txn_currency_code
,decode(p_activity_code,'DELETE',bl.txn_raw_cost * -1,bl.txn_raw_cost)
,decode(p_activity_code,'DELETE',bl.txn_burdened_cost *-1 , bl.txn_burdened_cost)
,decode(p_activity_code,'DELETE',bl.txn_revenue * -1 ,bl.txn_revenue)
,bl.project_currency_code
,decode(p_activity_code,'DELETE',bl.project_raw_cost * -1 ,bl.project_raw_cost)
,decode(p_activity_code,'DELETE',bl.project_burdened_cost * -1 ,bl.project_burdened_cost)
,decode(p_activity_code,'DELETE',bl.project_revenue * -1 ,bl.project_revenue)
,bl.projfunc_currency_code
,decode(p_activity_code,'DELETE',bl.raw_cost * -1 ,bl.raw_cost)
,decode(p_activity_code,'DELETE',bl.burdened_cost * -1 ,bl.burdened_cost)
,decode(p_activity_code,'DELETE',bl.revenue * -1 ,bl.revenue)
,decode(p_activity_code,'DELETE',bl.quantity * -1 ,bl.quantity)
INTO
l_start_date
,l_end_date
,l_period_name
,l_txn_currency_code
,l_txn_raw_cost
,l_txn_burdened_cost
,l_txn_revenue
,l_project_currency_code
,l_project_raw_cost
,l_project_burdened_cost
,l_project_revenue
,l_projfunc_currency_code
,l_projfunc_raw_cost
,l_projfunc_burdened_cost
,l_projfunc_revenue
,l_quantity
FROM pa_budget_lines bl
WHERE bl.budget_line_id = p_budget_line_id;
print_msg('Calling PJI_FM_XBS_ACCUM_MAINT.plan_update api bdgtLineId['||l_budget_line_id||']');
print_msg('AmtPassing to planUpdateAPI l_txn_currency_code['||l_txn_currency_code||']TxnRaw['||l_txn_raw_cost||']');
l_num_rows_inserted := 0;
print_msg('Calling PJI_FM_XBS_ACCUM_MAINT.plan_update api bdgtLineId['||l_budget_line_id||']');
/* clean up the tmp table before inserting*/
l_num_rows_inserted := 0;
/* since this is not a tmp table, deleteing will delete all the
* pending transactions inserted from other sessions
* so commenting out the code
* Bug fix:3803569 --DELETE FROM PJI_FM_EXTR_PLAN_LINES;
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,CALENDAR_TYPE
,RBS_ELEMENT_ID
,CBS_ELEMENT_ID --bug#16200605
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG
,ROLLUP_TYPE
,START_DATE
,END_DATE
,PERIOD_NAME
,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
)
VALUES (
pji_rec.project_id
,pji_rec.org_id
,pji_rec.task_id
,l_project_structure_id --pji_rec.project_structure_version_id
,pji_rec.time_phase_code
,pji_rec.rbs_element_id
,pji_rec.cbs_element_id --bug#16200605
,pji_rec.rbs_version_id
,pji_rec.budget_version_id
,pji_rec.fin_plan_type_id
,pji_rec.wp_version_flag
,pji_rec.resource_class_code
,pji_rec.rate_based_flag
,'W'
,l_start_date
,l_end_date
,l_period_name
,l_txn_currency_code
,l_txn_raw_cost
,l_txn_burdened_cost
,l_txn_revenue
,l_project_currency_code
,l_project_raw_cost
,l_project_burdened_cost
,l_project_revenue
,l_projfunc_currency_code
,l_projfunc_raw_cost
,l_projfunc_burdened_cost
,l_projfunc_revenue
,l_quantity
);
l_num_rows_inserted := sql%rowcount;
If l_num_rows_inserted > 0 Then
l_stage := 100;
PJI_FM_XBS_ACCUM_MAINT.plan_update
(p_plan_version_id => p_budget_version_id
, x_msg_code => l_msg_data
,x_return_status => l_return_status
);
PJI_FM_XBS_ACCUM_MAINT.plan_update
( x_msg_code => l_msg_data
,x_return_status => l_return_status
);
Print_msg('End of PJI_FM_XBS_ACCUM_MAINT.plan_update retSts['||l_return_status||']msgdata['||l_msg_data||']');
print_msg('End of updateReportingLines api retSts['||x_return_status||']');
,p_procedure_name => 'update_reporting_lines:Error Occured in plan_update' );
print_msg(l_stage||'Error occured in update_reporting_lines:Error Occured in plan_update ['||x_msg_data||']','Y');
,p_procedure_name => 'update_reporting_lines' );
print_msg(l_stage||'Error occured in update_reporting_lines['|| substr(SQLERRM,1,240),'Y');
END update_reporting_lines;
/* This is an wrapper api, which in turn calls update_reporting_lines and passes
* each budget line to reporting api
*This is the main api called from calculate, budget generation process to update the
* reporting PJI data when budget lines are created,updated or deleted.
* The following params values must be passed
* p_activity_code 'UPDATE',/'DELETE'
* p_calling_module name of API, for ex: 'CALCULATE_API'
* If activity = 'UPDATE' then +ve budgetLine amounts will be selected from DB
* if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB
* NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
* AND a budget line must exists for the given p_budget_line_id
*/
PROCEDURE update_reporting_lines_frombl
(p_calling_module IN Varchar2 Default 'CALCULATE_API'
,p_activity_code IN Varchar2 Default 'UPDATE'
,p_budget_version_id IN Number
,p_resource_assignment_id IN Number
,p_budget_line_id IN Number
,x_msg_data OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY Number
,x_return_status OUT NOCOPY Varchar2
) IS
INVALID_PARAMS EXCEPTION;
pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.update_reporting_lines_frombl');
print_msg('Entered update_reporting_lines_frombl module['||p_calling_module||']Activity['||p_activity_code||']bdgtver['||p_budget_version_id||']');
p_activity_code NOT IN ('UPDATE','DELETE') Then
l_stage := 20;
print_msg('Invalid params passed to update_reporting_lines_frombl');
update_reporting_lines
(p_calling_module => 'BUDGET_LINE'
,p_activity_code => p_activity_code
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => p_budget_line_id
,p_resource_assignment_id => p_resource_assignment_id
,p_start_date => null
,p_end_date => null
,p_period_name => null
,p_txn_currency_code => null
,p_quantity => null
,p_txn_raw_cost => null
,p_txn_burdened_cost => null
,p_txn_revenue => null
,p_project_currency_code => null
,p_project_raw_cost => null
,p_project_burdened_cost => null
,p_project_revenue => null
,p_projfunc_currency_code => null
,p_projfunc_raw_cost => null
,p_projfunc_burdened_cost => null
,p_projfunc_revenue => null
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,x_return_status => x_return_status
) ;
print_msg('End of updateReportingLines_frombl api retSts['||x_return_status||']');
x_msg_data := 'Invalid params passed to update_reporting_lines_frombl';
,p_procedure_name => 'update_reporting_lines_from_bl' );
print_msg(l_stage||'Error occured in update_reporting_lines_frombl['|| substr(SQLERRM,1,240),'Y');
END update_reporting_lines_frombl;
/* This is the main api called from calculate, budget generation process to update the
* reporting PJI data when budget lines are created,updated or deleted.
* The following params values must be passed
* p_activity_code 'UPDATE',/'DELETE'
* p_calling_module name of API, for calculate 'CALCULATE_API'
* p_start_date BudgetLine StartDate
* p_end_date BudgetLine Enddate
* If activity = 'UPDATE' then all the amounts and currency columns must be passed
* if activity = 'DELETE' then -ve budgetLine amounts will be selected from DB and passed in params will be ignored
* NOTE: BEFORE CALLING THIS API, a record must exists in pa_resource_assignments for the p_resource_assignment_id
* AND CALL THIS API ONLY IF THERE ARE NO REJECTION CODES STAMPED ON THE BUDGET LINES
* NOTE: As of IPM, we ignore rejection codes stamped on budget lines for the purpose of updating PJI data.
* THIS API IS CREATED FOR BULK PROCESS OF DATA.
* NOTE: ALL PARAMS MUST BE PASSED , passing Null or incomplete params will error out
* the calling API must initialize all params and pass it
*/
PROCEDURE blk_update_reporting_lines
(p_calling_module IN Varchar2 Default 'CALCULATE_API'
,p_activity_code IN Varchar2 Default 'UPDATE'
,p_budget_version_id IN Number
,p_rep_budget_line_id_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_res_assignment_id_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_start_date_tab IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
,p_rep_end_date_tab IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
,p_rep_period_name_tab IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
,p_rep_txn_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
,p_rep_quantity_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_burdened_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_project_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
,p_rep_project_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_project_burden_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_project_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_projfunc_curr_code_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
,p_rep_projfunc_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_projfunc_burden_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_projfunc_revenue_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
/*
* The following _act_ parameters contain actual amounts.
*/
,p_rep_act_quantity_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_txn_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_prj_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_prj_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_prj_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_pf_act_raw_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_pf_act_burd_cost_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
,p_rep_pf_act_rev_tab IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
/* bug fix:5116157 */
,p_rep_line_mode_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
,p_rep_rate_base_flag_tab IN SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type()
,x_msg_data OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY Number
,x_return_status OUT NOCOPY Varchar2
) IS
l_msg_count Number := 0;
l_num_rows_inserted Number;
SELECT DECODE(nvl(pbv.wp_version_flag,'N'),'Y',pbv.project_structure_version_id,
PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
FROM pa_budget_versions pbv
WHERE pbv.budget_version_id = p_budget_version_id;
print_msg('Entered PA_FP_PJI_INTG_PKG.blk_update_reporting_lines api: Num of Trxns['||p_rep_res_assignment_id_tab.count||']','Y');
pa_debug.init_err_stack('PA_FP_PJI_INTG_PKG.blk_update_reporting_lines');
IF p_activity_code = 'DELETE' AND l_rep_budget_line_id_tab.COUNT = 0 Then
l_stage := 20;
If p_activity_code in ('UPDATE','DELETE') Then
l_stage := 30;
SELECT bl.start_date
,bl.end_date
,bl.period_name
,bl.txn_currency_code
,decode(p_activity_code,'DELETE',bl.txn_raw_cost * -1,bl.txn_raw_cost)
,decode(p_activity_code,'DELETE',bl.txn_burdened_cost *-1 , bl.txn_burdened_cost)
,decode(p_activity_code,'DELETE',bl.txn_revenue * -1 ,bl.txn_revenue)
,bl.project_currency_code
,decode(p_activity_code,'DELETE',bl.project_raw_cost * -1 ,bl.project_raw_cost)
,decode(p_activity_code,'DELETE',bl.project_burdened_cost * -1 ,bl.project_burdened_cost)
,decode(p_activity_code,'DELETE',bl.project_revenue * -1 ,bl.project_revenue)
,bl.projfunc_currency_code
,decode(p_activity_code,'DELETE',bl.raw_cost * -1 ,bl.raw_cost)
,decode(p_activity_code,'DELETE',bl.burdened_cost * -1 ,bl.burdened_cost)
,decode(p_activity_code,'DELETE',bl.revenue * -1 ,bl.revenue)
,decode(p_activity_code,'DELETE',bl.quantity * -1 ,bl.quantity)
,decode(p_activity_code,'DELETE',bl.txn_init_raw_cost * -1,bl.txn_init_raw_cost)
,decode(p_activity_code,'DELETE',bl.txn_init_burdened_cost *-1 , bl.txn_init_burdened_cost)
,decode(p_activity_code,'DELETE',bl.txn_init_revenue * -1 ,bl.txn_init_revenue)
,decode(p_activity_code,'DELETE',bl.project_init_raw_cost * -1 ,bl.project_init_raw_cost)
,decode(p_activity_code,'DELETE',bl.project_init_burdened_cost * -1 ,bl.project_init_burdened_cost)
,decode(p_activity_code,'DELETE',bl.project_init_revenue * -1 ,bl.project_init_revenue)
,decode(p_activity_code,'DELETE',bl.init_raw_cost * -1 ,bl.init_raw_cost)
,decode(p_activity_code,'DELETE',bl.init_burdened_cost * -1 ,bl.init_burdened_cost)
,decode(p_activity_code,'DELETE',bl.init_revenue * -1 ,bl.init_revenue)
,decode(p_activity_code,'DELETE',bl.init_quantity * -1 ,bl.init_quantity)
INTO
l_rep_start_date_tab(i)
,l_rep_end_date_tab(i)
,l_rep_period_name_tab(i)
,l_rep_txn_curr_code_tab(i)
,l_rep_txn_raw_cost_tab(i)
,l_rep_txn_burdened_cost_tab(i)
,l_rep_txn_revenue_tab(i)
,l_rep_project_curr_code_tab(i)
,l_rep_project_raw_cost_tab(i)
,l_rep_project_burden_cost_tab(i)
,l_rep_project_revenue_tab(i)
,l_rep_projfunc_curr_code_tab(i)
,l_rep_projfunc_raw_cost_tab(i)
,l_rep_projfunc_burden_cost_tab(i)
,l_rep_projfunc_revenue_tab(i)
,l_rep_quantity_tab(i)
,l_rep_txn_act_raw_cost_tab(i)
,l_rep_txn_act_burd_cost_tab(i)
,l_rep_txn_act_rev_tab(i)
,l_rep_prj_act_raw_cost_tab(i)
,l_rep_prj_act_burd_cost_tab(i)
,l_rep_prj_act_rev_tab(i)
,l_rep_pf_act_raw_cost_tab(i)
,l_rep_pf_act_burd_cost_tab(i)
,l_rep_pf_act_rev_tab(i)
,l_rep_act_quantity_tab(i)
FROM pa_budget_lines bl
WHERE bl.budget_line_id = l_rep_budget_line_id_tab(i);
/* Insert the records into pji tmp table*/
FOR i IN l_rep_res_assignment_id_tab.FIRST .. l_rep_res_assignment_id_tab.LAST LOOP --{
l_rep_org_id_tab.extend;
SELECT ra.project_id
,ra.task_id
,ra.rbs_element_id
,ra.cbs_element_id --bug#16200605
,ra.resource_class_code
,ra.rate_based_flag
,ppa.org_id
,ppfo.rbs_version_id
,pbv.fin_plan_type_id
/* Bug fix: 3839761 --nvl(pbv.project_structure_version_id,
--PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(pbv.project_id)) project_structure_version_id
*/
,pbv.wp_version_flag
,decode(pbv.version_type, 'COST',ppfo.cost_time_phased_code,
'REVENUE',ppfo.revenue_time_phased_code,
ppfo.all_time_phased_code) time_phase_code
INTO l_project_id
,l_task_id
,l_rbs_element_id
,l_cbs_element_id --bug#16200605
,l_res_class_code
,l_rate_base_flag
,l_org_id
,l_rbs_version_id
,l_fin_plan_type_id
/* bug fix: 3839761--,l_project_structure_id */
,l_wp_version_flag
,l_time_phase_code
FROM pa_projects_all ppa
,pa_budget_versions pbv
,pa_proj_fp_options ppfo
,pa_resource_assignments ra
WHERE ppa.project_id = pbv.project_id
AND pbv.budget_version_id = ppfo.fin_plan_version_id
AND pbv.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = l_rep_res_assignment_id_tab(i)
AND ra.budget_version_id = pbv.budget_version_id;
/*Bulk insert into reporting table */
IF l_rep_res_assignment_id_tab.COUNT > 0 Then --{
l_num_rows_inserted := 0;
print_msg(l_stage||': Inserting records into PJI_FM_EXTR_PLAN_LINES tmp table');
INSERT INTO PJI_FM_EXTR_PLAN_LINES
( PROJECT_ID
,PROJECT_ORG_ID
,PROJECT_ELEMENT_ID
,STRUCT_VER_ID
,CALENDAR_TYPE
,RBS_ELEMENT_ID
,CBS_ELEMENT_ID--bug#16200605
,RBS_VERSION_ID
,PLAN_VERSION_ID
,PLAN_TYPE_ID
,WP_VERSION_FLAG
,RESOURCE_CLASS_CODE
,RATE_BASED_FLAG
,ROLLUP_TYPE
,START_DATE
,END_DATE
,PERIOD_NAME
,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
,ACT_TXN_RAW_COST
,ACT_TXN_BURDENED_COST
,ACT_TXN_REVENUE
,ACT_PRJ_RAW_COST
,ACT_PRJ_BURDENED_COST
,ACT_PRJ_REVENUE
,ACT_PFC_RAW_COST
,ACT_PFC_BURDENED_COST
,ACT_PFC_REVENUE
,ACT_QUANTITY
)
SELECT
l_rep_project_id_tab(i)
,l_rep_org_id_tab(i)
,l_rep_task_id_tab(i)
,l_rep_proj_structure_id_tab(i)
,l_rep_time_phase_code_tab(i)
,l_rep_rbs_element_id_tab(i)
,l_rep_cbs_element_id_tab(i) --bug#16200605
,l_rep_rbs_version_id_tab(i)
,p_budget_version_id
,l_rep_finplan_type_id_tab(i)
,l_rep_wp_version_flag_tab(i)
,l_rep_resclass_code_tab(i)
,l_rep_rate_base_flag_tab(i)
,'W'
,l_rep_start_date_tab(i)
,l_rep_end_date_tab(i)
,l_rep_period_name_tab(i)
,l_rep_txn_curr_code_tab(i)
,l_rep_txn_raw_cost_tab(i)
,l_rep_txn_burdened_cost_tab(i)
,l_rep_txn_revenue_tab(i)
,l_rep_project_curr_code_tab(i)
,l_rep_project_raw_cost_tab(i)
,l_rep_project_burden_cost_tab(i)
,l_rep_project_revenue_tab(i)
,l_rep_projfunc_curr_code_tab(i)
,l_rep_projfunc_raw_cost_tab(i)
,l_rep_projfunc_burden_cost_tab(i)
,l_rep_projfunc_revenue_tab(i)
,l_rep_quantity_tab(i)
,l_rep_txn_act_raw_cost_tab(i)
,l_rep_txn_act_burd_cost_tab(i)
,l_rep_txn_act_rev_tab(i)
,l_rep_prj_act_raw_cost_tab(i)
,l_rep_prj_act_burd_cost_tab(i)
,l_rep_prj_act_rev_tab(i)
,l_rep_pf_act_raw_cost_tab(i)
,l_rep_pf_act_burd_cost_tab(i)
,l_rep_pf_act_rev_tab(i)
,l_rep_act_quantity_tab(i)
FROM DUAL
WHERE ( l_rep_quantity_tab(i) is NOT NULL
OR l_rep_txn_raw_cost_tab(i) is NOT NULL
OR l_rep_txn_burdened_cost_tab(i) is NOT NULL
OR l_rep_txn_revenue_tab(i) is NOT NULL
OR l_rep_act_quantity_tab(i) is NOT NULL
OR l_rep_txn_act_raw_cost_tab(i) is NOT NULL
OR l_rep_txn_act_burd_cost_tab(i) is NOT NULL
OR l_rep_txn_act_rev_tab(i) is NOT NULL ) ;
l_num_rows_inserted := sql%rowcount;
print_msg('Number of records inserted ['||l_num_rows_inserted||']');
/* end of Bulk insert */
/* Start for dubug message*/
/* Bug fix: 4159553 enclose the the following cursor in a debug flag */
IF NVL(g_debug_flag,'N') = 'Y' Then
for j in (select pj.RBS_ELEMENT_ID RBS_ELEMENT_ID
,pj.TXN_CURRENCY_CODE TXN_CURRENCY_CODE
,pj.RATE_BASED_FLAG RATE_BASED_FLAG
,sum(pj.QUANTITY) QUANTITY
,sum(pj.TXN_RAW_COST) TXN_RAW_COST
,sum(pj.TXN_BURDENED_COST) TXN_BURDENED_COST
,sum(pj.TXN_REVENUE) TXN_REVENUE
,sum(pj.ACT_QUANTITY) ACT_QUANTITY
,sum(pj.ACT_TXN_RAW_COST) ACT_TXN_RAW_COST
,sum(pj.ACT_TXN_BURDENED_COST) ACT_TXN_BURDENED_COST
,sum(pj.ACT_TXN_REVENUE) ACT_TXN_REVENUE
from PJI_FM_EXTR_PLAN_LINES pj
where pj.plan_version_id = p_budget_version_id
Group by pj.RBS_VERSION_ID
,pj.RBS_ELEMENT_ID
,pj.TXN_CURRENCY_CODE
,pj.RATE_BASED_FLAG ) LOOP
null;
IF l_num_rows_inserted > 0 Then
l_stage := 100;
print_msg('Start of PJI_plan_update:['||dbms_utility.get_time||']');
print_msg('l_stage: Calling PJI_FM_XBS_ACCUM_MAINT.plan_update for budget version');
PJI_FM_XBS_ACCUM_MAINT.plan_update
(p_plan_version_id => p_budget_version_id
, x_msg_code => l_msg_data
,x_return_status => l_return_status
);
print_msg('l_stage: Calling PJI_FM_XBS_ACCUM_MAINT.plan_update without version');
PJI_FM_XBS_ACCUM_MAINT.plan_update
( x_msg_code => l_msg_data
,x_return_status => l_return_status
);
print_msg('End of PJI_plan_update:['||dbms_utility.get_time||']');
Print_msg('End of PJI_FM_XBS_ACCUM_MAINT.plan_update retSts['||l_return_status||']msgdata['||l_msg_data||']');
print_msg('End of blk_update_reporting_lines api retSts['||x_return_status||']');
,p_procedure_name => 'blk_update_reporting_lines:Error Occured in plan_update' );
print_msg(l_stage||'Error occured in blk_update_reporting_lines:Error Occured in plan_update ['||x_msg_data||']','Y');
,p_procedure_name => 'update_reporting_lines' );
print_msg(l_stage||'Error occured in blk_update_reporting_lines['|| substr(SQLERRM,1,240),'Y');
END blk_update_reporting_lines;