[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_id INTO x_person_id FROM
PA_RESOURCE_TXN_ATTRIBUTES WHERE
RESOURCE_ID = p_res_id;
PROCEDURE UPDATE_BUDG_VERSION(p_budget_version_id IN NUMBER ) IS
BEGIN
UPDATE PA_BUDGET_VERSIONS SET PLAN_PROCESSING_CODE = 'E'
WHERE
BUDGET_VERSION_ID = p_budget_version_id;
SELECT BUDGET_VERSION_ID, PLAN_PROCESSING_CODE
FROM PA_BUDGET_VERSIONS
WHERE PROJECT_ID = p_project_id AND
BUDGET_TYPE_CODE = 'FORECASTING_BUDGET_TYPE';
l_program_update_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
l_program_update_date := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
PA_DEBUG.g_err_stage := '630: before calling PA_BUDGET_VERSIONS_PKG.INSERT_ROW';
PA_BUDGET_VERSIONS_PKG.Insert_Row(
X_ROWID => l_rowid,
X_BUDGET_VERSION_ID => x_budget_version_id,
X_PROJECT_ID => p_project_id,
X_BUDGET_TYPE_CODE => 'FORECASTING_BUDGET_TYPE',
X_VERSION_NUMBER => 1,
X_BUDGET_STATUS_CODE => 'W',
X_LAST_UPDATE_DATE => l_program_update_date,
X_LAST_UPDATED_BY => l_created_by,
X_CREATION_DATE => l_creation_date,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_LOGIN => l_request_id,
X_CURRENT_FLAG => 'X',
X_ORIGINAL_FLAG => 'X',
X_CURRENT_ORIGINAL_FLAG => 'X',
X_RESOURCE_ACCUMULATED_FLAG => 'X',
X_RESOURCE_LIST_ID => l_fcst_res_list,
X_VERSION_NAME => NULL,
X_BUDGET_ENTRY_METHOD_CODE => l_fcst_def_bem,
X_BASELINED_BY_PERSON_ID => NULL,
X_BASELINED_DATE => NULL,
X_CHANGE_REASON_CODE => NULL,
X_LABOR_QUANTITY => 0,
X_LABOR_UNIT_OF_MEASURE => 0,
X_RAW_COST => 0,
X_BURDENED_COST => 0,
X_REVENUE => 0,
X_DESCRIPTION => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_FIRST_BUDGET_PERIOD => NULL,
X_PM_PRODUCT_CODE => NULL,
X_PM_BUDGET_REFERENCE => NULL,
X_WF_STATUS_CODE => NULL,
X_PLAN_PROCESSING_CODE => p_plan_processing_code);
PA_DEBUG.g_err_stage := '660: after calling PA_BUDGET_VERSIONS_PKG.INSERT_ROW';
UPDATE PA_BUDGET_VERSIONS SET
PLAN_PROCESSING_CODE = p_plan_processing_code,
BUDGET_ENTRY_METHOD_CODE = l_fcst_def_bem
WHERE
BUDGET_VERSION_ID = x_budget_version_id;
SELECT P.PROJECT_TYPE,P.PROJECT_CURRENCY_CODE,P.CARRYING_OUT_ORGANIZATION_ID,
P.PROJECT_VALUE, P.JOB_BILL_RATE_SCHEDULE_ID, P.EMP_BILL_RATE_SCHEDULE_ID,
P.DISTRIBUTION_RULE,P.BILL_JOB_GROUP_ID,NVL(P.ORG_ID,-99),P.COMPLETION_DATE,
NVL(P.TEMPLATE_FLAG,'N'),
P.PROJFUNC_CURRENCY_CODE,
P.PROJFUNC_BIL_RATE_DATE_CODE,
P.PROJFUNC_BIL_RATE_TYPE,
P.PROJFUNC_BIL_RATE_DATE,
P.PROJFUNC_BIL_EXCHANGE_RATE,
P.COST_JOB_GROUP_ID,
P.PROJECT_RATE_DATE,
P.PROJECT_RATE_TYPE,
P.PROJECT_BIL_RATE_DATE_CODE,
P.PROJECT_BIL_RATE_TYPE,
P.PROJECT_BIL_RATE_DATE,
P.PROJECT_BIL_EXCHANGE_RATE,
P.PROJFUNC_COST_RATE_TYPE,
P.PROJFUNC_COST_RATE_DATE,
P.LABOR_TP_SCHEDULE_ID,
P.LABOR_TP_FIXED_DATE,
P.LABOR_SCHEDULE_DISCOUNT,
NVL(P.ASSIGN_PRECEDES_TASK,'N'),
NVL(P.LABOR_BILL_RATE_ORG_ID,-99),
P.LABOR_STD_BILL_RATE_SCHDL,
P.LABOR_SCHEDULE_FIXED_DATE,
P.LABOR_SCH_TYPE
FROM
PA_PROJECTS_ALL P WHERE PROJECT_ID = P_PROJECT_ID;
SELECT PA.ASSIGNMENT_ID, PA.START_DATE,PA.RESOURCE_ID,PA.PROJECT_ROLE_ID,
PA.FCST_JOB_ID,PA.FCST_JOB_GROUP_ID,PR.MEANING,
PA.ASSIGNMENT_TYPE ,
PA.EXPENDITURE_ORGANIZATION_ID,
PA.EXPENDITURE_TYPE,
PA.REVENUE_BILL_RATE,
PA.EXPENDITURE_ORG_ID,
PA.STATUS_CODE,
WB.BILLABLE_CAPITALIZABLE_FLAG --Added for the bug 2420564
FROM
PA_PROJECT_ASSIGNMENTS PA,
PA_WORK_TYPES_B WB,
PA_PROJECT_ROLE_TYPES PR
WHERE
PA.PROJECT_ID = p_project_id AND
PA.PROJECT_ROLE_ID = PR.PROJECT_ROLE_ID AND
WB.WORK_TYPE_ID = PA.WORK_TYPE_ID(+); --Added for the bug 2420564
SELECT FI.EXPENDITURE_ORG_ID,FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_PA_PERIOD_NAME,
P.START_DATE,P.END_DATE,SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID)
FROM
PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
PA_PERIODS_ALL P
WHERE
FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99) AND
P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME AND
FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID AND
FID.FORECAST_SUMMARIZED_CODE = 'N' AND
FID.NET_ZERO_FLAG = 'N' AND
FI.ERROR_FLAG = 'N' AND
FI.DELETE_FLAG = 'N' AND
ASSIGNMENT_ID = p_prj_assignment_id AND
FI.EXPENDITURE_ORG_ID <> -88 /* Added this condition for bug 3151420 */
GROUP BY
FI.EXPENDITURE_ORG_ID,FI.EXPENDITURE_ORGANIZATION_ID,
P.START_DATE,P.END_DATE,FI.RCVR_PA_PERIOD_NAME;
SELECT FI.EXPENDITURE_ORG_ID, FI.EXPENDITURE_ORGANIZATION_ID,
FI.RCVR_GL_PERIOD_NAME,
GLP.START_DATE, GLP.END_DATE,SUM(FI.ITEM_QUANTITY),
MIN(FI.FORECAST_ITEM_ID)
FROM
PA_FORECAST_ITEMS FI,
PA_FORECAST_ITEM_DETAILS FID,
GL_PERIODS GLP, /* Added the ending comma for Bug 3512491 */
PA_IMPLEMENTATIONS IMP, /* Added the table for Bug 3512491 */
GL_SETS_OF_BOOKS SOB /* Added the table for Bug 3512491 */
WHERE
FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID AND
FID.FORECAST_SUMMARIZED_CODE = 'N' AND
FID.NET_ZERO_FLAG = 'N' AND
FI.ERROR_FLAG = 'N' AND
FI.DELETE_FLAG = 'N' AND
SOB.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID AND /* Added the join for Bug 3512491 */
GLP.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME AND /* Modified the join for Bug 3512491 */
GLP.PERIOD_NAME = FI.RCVR_GL_PERIOD_NAME AND
ASSIGNMENT_ID = p_prj_assignment_id AND
FI.EXPENDITURE_ORG_ID <> -88 /* Added this condition for bug 3151420 */
GROUP BY
FI.EXPENDITURE_ORG_ID, FI.EXPENDITURE_ORGANIZATION_ID,
GLP.START_DATE, GLP.END_DATE, FI.RCVR_GL_PERIOD_NAME;
SELECT BL.PERIOD_NAME,BL.START_DATE,
BL.BURDENED_COST FROM PA_BUDGET_LINES BL,
PA_RESOURCE_ASSIGNMENTS RA WHERE
BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID AND
RA.BUDGET_VERSION_ID = c_budget_version_id AND
RA.PROJECT_ID = c_project_id AND
RA.RESOURCE_LIST_MEMBER_ID = 103
ORDER BY BL.START_DATE;
l_program_update_date DATE := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
l_program_update_date := PA_FORECAST_GLOBAL.G_who_columns.G_last_update_date;
DELETE FROM PA_BUDGET_LINES WHERE
RESOURCE_ASSIGNMENT_ID IN
(SELECT RESOURCE_ASSIGNMENT_ID FROM PA_RESOURCE_ASSIGNMENTS
WHERE
BUDGET_VERSION_ID = l_budget_version_id );
DELETE FROM PA_RESOURCE_ASSIGNMENTS WHERE
BUDGET_VERSION_ID = l_budget_version_id;
l_budget_lines_tot_tbl.DELETE;
SELECT PR.DEFAULT_JOB_ID,PJ.JOB_GROUP_ID INTO
l_prj_fcst_job_id,l_prj_fcst_job_group_id FROM
PA_PROJECT_ROLE_TYPES PR, PER_JOBS PJ
WHERE
PR.PROJECT_ROLE_ID = l_prj_project_role_id AND
PJ.JOB_ID = PR.DEFAULT_JOB_ID;
UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id);
SELECT RLM.RESOURCE_LIST_MEMBER_ID INTO
l_resource_list_member_id
FROM
PA_RESOURCE_LIST_MEMBERS RLM, PA_RESOURCES R, PA_RESOURCE_TXN_ATTRIBUTES RT
WHERE
RLM.RESOURCE_LIST_ID = l_fcst_res_list AND
RLM.RESOURCE_ID = R.RESOURCE_ID AND
RT.RESOURCE_ID = R.RESOURCE_ID AND
RT.PROJECT_ROLE_ID = l_prj_project_role_id;
l_budget_lines_tbl.delete;
l_rt_forecast_item_id_tab.delete;
l_rt_pd_name_tab.delete;
l_rt_start_date_tab.delete;
l_rt_end_date_tab.delete;
l_rt_qty_tab.delete;
l_rt_exp_org_id_tab.delete;
l_rt_exp_organization_id_tab.delete;
l_rt_exp_func_raw_cst_rt_tab.delete;
l_rt_exp_func_raw_cst_tab.delete;
l_rt_exp_func_bur_cst_rt_tab.delete;
l_rt_exp_func_burdned_cst_tab.delete;
l_rt_projfunc_bill_rt_tab.delete;
l_rt_projfunc_raw_revenue_tab.delete;
l_rt_projfunc_raw_cst_tab.delete;
l_rt_projfunc_raw_cst_rt_tab.delete;
l_rt_projfunc_burdned_cst_tab.delete;
l_rt_projfunc_bd_cst_rt_tab.delete;
l_rt_rev_rejct_reason_tab.delete;
l_rt_cst_rejct_reason_tab.delete;
l_rt_burdned_rejct_reason_tab.delete;
l_rt_others_rejct_reason_tab.delete;
SELECT EXPENDITURE_TYPE_CLASS INTO l_system_linkage FROM
Pa_Forecast_Items WHERE
Forecast_Item_Id = l_rt_forecast_item_id_tab(1);
UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
SELECT EXPENDITURE_TYPE_CLASS INTO l_system_linkage FROM
Pa_Forecast_Items WHERE
Forecast_Item_Id = l_rt_forecast_item_id_tab(1);
UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
l_rt_system_linkage_tab.delete;
l_rt_pfunc_rev_rt_dt_code_tab.delete;
l_rt_pfunc_rev_rt_date_tab.delete;
l_rt_pfunc_rev_rt_type_tab.delete;
l_rt_pfunc_rev_ex_rt_tab.delete;
l_rt_pfunc_cost_rt_date_tab.delete;
l_rt_pfunc_cost_rt_type_tab.delete;
l_rt_proj_rev_rt_dt_code_tab.delete;
l_rt_proj_rev_rt_date_tab.delete;
l_rt_proj_rev_rt_type_tab.delete;
l_rt_proj_rev_ex_rt_tab.delete;
l_rt_proj_cost_rt_date_tab.delete;
l_rt_proj_cost_rt_type_tab.delete;
UPDATE_BUDG_VERSION(p_budget_version_id => l_budget_version_id );
/* Added for Bulk Insert */
/* The following logic is from the PA_BUDGET_LINES_V_PKG to take care of the
bulk insert into PA_BUDGET_LINES for performance */
BEGIN
SELECT resource_assignment_id
INTO l_resource_assignment_id
FROM pa_resource_assignments a
WHERE a.budget_version_id = l_budget_version_id
AND a.project_id = p_project_id
AND nvl(a.task_id,0) = 0
AND a.resource_list_member_id = l_resource_list_member_id
AND a.project_assignment_id = l_prj_assignment_id;
SELECT pa_resource_assignments_s.nextval
INTO l_resource_assignment_id
FROM sys.dual;
INSERT INTO pa_resource_assignments(
resource_assignment_id,
budget_version_id,
project_id,
task_id,
resource_list_member_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unit_of_measure,
track_as_labor_flag,
project_assignment_id,
standard_bill_rate
) VALUES
( l_resource_assignment_id ,
l_budget_version_id,
p_project_id,
0,
l_resource_list_member_id,
SYSDATE,
l_created_by,
SYSDATE,
l_created_by,
l_request_id,
NULL,
NULL,
l_prj_assignment_id,
l_prj_revenue_bill_rate
);
l_bl_start_date_tab.delete;
l_bl_end_date_tab.delete;
l_bl_pd_name_tab.delete;
l_bl_qty_tab.delete;
l_bl_rcost_tab.delete;
l_bl_revenue_tab.delete;
l_bl_bcost_tab.delete;
l_bl_cost_rej_tab.delete;
l_bl_bcost_rej_tab.delete;
l_bl_rev_rej_tab.delete;
l_bl_oth_rej_tab.delete;
INSERT INTO PA_BUDGET_LINES(
BUDGET_LINE_ID, /* FPB2 */
BUDGET_VERSION_ID, /* FPB2 */
RESOURCE_ASSIGNMENT_ID,
START_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
END_DATE ,
PERIOD_NAME ,
QUANTITY ,
RAW_COST ,
BURDENED_COST ,
REVENUE ,
COST_REJECTION_CODE ,
REVENUE_REJECTION_CODE,
BURDEN_REJECTION_CODE ,
OTHER_REJECTION_CODE ,
RAW_COST_SOURCE ,
BURDENED_COST_SOURCE ,
QUANTITY_SOURCE ,
REVENUE_SOURCE ,
TXN_CURRENCY_CODE ) /* FPB2 - Bug 2753426 */
VALUES (
pa_budget_lines_s.nextval, /* FPB2 */
l_budget_version_id, /* FPB2 */
l_resource_assignment_id,
l_bl_start_date_tab(l_ins_temp),
l_program_update_date,
l_created_by,
l_creation_date,
l_created_by,
l_request_id,
l_bl_end_date_tab(l_ins_temp),
l_bl_pd_name_tab(l_ins_temp),
l_bl_qty_tab(l_ins_temp),
l_bl_rcost_tab(l_ins_temp),
l_bl_bcost_tab(l_ins_temp),
l_bl_revenue_tab(l_ins_temp),
l_bl_cost_rej_tab(l_ins_temp),
l_bl_rev_rej_tab(l_ins_temp),
l_bl_bcost_rej_tab(l_ins_temp),
l_bl_oth_rej_tab(l_ins_temp) ,
'M','M','M','M' ,
l_projfunc_currency_code);
/* Added for Bulk Insert */
/* Update only if some fis are processed for the current assignment id */
IF L_BUDGET_LINES_TBL.COUNT > 0 THEN
UPDATE PA_RESOURCE_ASSIGNMENTS SET
TOTAL_PLAN_REVENUE = NVL(TOTAL_PLAN_REVENUE,0) + l_tot_revenue,
TOTAL_PLAN_RAW_COST = NVL(TOTAL_PLAN_RAW_COST,0) + l_tot_cost,
TOTAL_PLAN_BURDENED_COST = NVL(TOTAL_PLAN_BURDENED_COST,0) + l_tot_bcost,
TOTAL_PLAN_QUANTITY = NVL(TOTAL_PLAN_QUANTITY,0) + l_tot_quantity,
PLAN_ERROR_CODE = l_role_error_code
WHERE
RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id;
if there is no exit stmt. the bulk insert will try to do insert the same records */
END LOOP;
SELECT RESOURCE_ASSIGNMENT_ID
INTO l_prj_res_assignment_id
FROM PA_RESOURCE_ASSIGNMENTS A
WHERE A.BUDGET_VERSION_ID = l_budget_version_id
AND A.PROJECT_ID = p_project_id
AND nvl(a.task_id,0) = 0 -- to make use of the index
AND A.PROJECT_ASSIGNMENT_ID = -1
AND A.RESOURCE_LIST_MEMBER_ID = 103;
SELECT pa_resource_assignments_s.nextval
INTO l_prj_res_assignment_id
FROM sys.dual;
insert into pa_resource_assignments(
resource_assignment_id,
budget_version_id,
project_id,
task_id,
resource_list_member_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unit_of_measure,
track_as_labor_flag,
project_assignment_id) VALUES
(l_prj_res_Assignment_Id ,
l_budget_version_id,
p_project_id,
0, -- Task Id
103, -- RLM Id for project level totals
SYSDATE,
l_created_by,
SYSDATE,
l_created_by,
l_request_id,
NULL, -- x_unit_of_measure
NULL,
-1 ); -- x_track_as_labor_flag
l_bl_start_date_tab.delete;
l_bl_end_date_tab.delete;
l_bl_pd_name_tab.delete;
l_bl_qty_tab.delete;
l_bl_rcost_tab.delete;
l_bl_revenue_tab.delete;
l_bl_bcost_tab.delete;
/* populating the tables for bulk insert */
FOR cnt_temp IN 1 .. l_budget_lines_tot_tbl.count LOOP
l_bl_revenue_tab(cnt_temp) := NULL;
PA_DEBUG.g_err_stage := '2125:aft populating tables for insert into BL RLMId 103 and bef bulk ins';
INSERT INTO PA_BUDGET_LINES(
BUDGET_LINE_ID, /* FPB2 */
BUDGET_VERSION_ID, /* FPB2 */
RESOURCE_ASSIGNMENT_ID,
START_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
END_DATE ,
PERIOD_NAME ,
QUANTITY ,
RAW_COST ,
BURDENED_COST ,
REVENUE ,
RAW_COST_SOURCE ,
BURDENED_COST_SOURCE ,
QUANTITY_SOURCE ,
REVENUE_SOURCE ,
TXN_CURRENCY_CODE ) /* FPB2 - Bug 2753426 */
VALUES (
pa_budget_lines_s.nextval, /* FPB2 */
l_budget_version_id, /* FPB2 */
l_prj_res_assignment_id,
l_bl_start_date_tab(l_ins_temp),
l_program_update_date,
l_created_by,
l_creation_date,
l_created_by,
l_request_id,
l_bl_end_date_tab(l_ins_temp),
l_bl_pd_name_tab(l_ins_temp),
l_bl_qty_tab(l_ins_temp),
l_bl_rcost_tab(l_ins_temp),
l_bl_bcost_tab(l_ins_temp),
l_bl_revenue_tab(l_ins_temp),
'M','M','M','M' ,
l_projfunc_currency_code); /* FPB2 - Bug 2753426 */
PA_DEBUG.g_err_stage := '2200: after bulk inserting into BLines for RLMId 103:'||l_rowcount;
UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_NO_PRJ_VALUE'
WHERE
RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
l_prj_revenue_tab.delete;
UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
UPDATE PA_BUDGET_LINES SET REVENUE = l_prj_revenue_tab(l_counter).amount
WHERE
RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id AND
PERIOD_NAME = l_prj_revenue_tab(l_counter).period_name;
select pa_budget_lines_s.nextval
into l_budget_line_id
from dual;
INSERT INTO PA_BUDGET_LINES(
BUDGET_LINE_ID, /* FPB2 */
BUDGET_VERSION_ID, /* FPB2 */
RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
PERIOD_NAME,
QUANTITY,
RAW_COST,
BURDENED_COST,
REVENUE,
TXN_CURRENCY_CODE) /* FPB2 - Bug 2753426 */
VALUES(
l_budget_line_id, /* FPB2 */
l_budget_version_id, /* FPB2 */
l_prj_res_assignment_id,
l_prj_revenue_tab(l_counter).start_date,
l_program_update_date,
l_created_by,
l_creation_date,
l_created_by,
l_request_id,
l_prj_revenue_tab(l_counter).end_date,
l_prj_revenue_tab(l_counter).period_name,
0,
0,
0,
l_prj_revenue_tab(l_counter).amount,
l_projfunc_currency_code); /* FPB2 - Bug 2753426 */
UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_PDS_NOT_DEFINED'
WHERE
RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
l_prj_cost_tab.delete;
l_prj_revenue_tab.delete;
UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
UPDATE PA_BUDGET_LINES SET REVENUE = l_prj_revenue_tab(l_cost_cnt).amount
WHERE
RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id AND
PERIOD_NAME =l_prj_revenue_tab(l_cost_cnt).period_name;
UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = l_cost_based_error_code
WHERE
RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
UPDATE PA_RESOURCE_ASSIGNMENTS SET PLAN_ERROR_CODE = 'PA_FCST_NO_PRJ_VALUE'
WHERE
RESOURCE_ASSIGNMENT_ID =l_prj_res_assignment_id;
/* Update Total Revenue in PA_RESOURCE_ASSIGNMENTS */
UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
( RA.TOTAL_PLAN_REVENUE,
RA.TOTAL_PLAN_QUANTITY,
RA.TOTAL_PLAN_RAW_COST,
RA.TOTAL_PLAN_BURDENED_COST )
= (SELECT SUM(BL.REVENUE),
SUM(BL.QUANTITY),
SUM(BL.RAW_COST),
SUM(BL.BURDENED_COST) FROM
PA_BUDGET_LINES BL WHERE
BL.RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id )
WHERE RA.RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id ;
UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
AVERAGE_COST_RATE =
DECODE(TOTAL_PLAN_BURDENED_COST,0,NULL,TOTAL_PLAN_BURDENED_COST ) / TOTAL_PLAN_QUANTITY,
AVERAGE_BILL_RATE =
DECODE(TOTAL_PLAN_REVENUE,0,NULL,TOTAL_PLAN_REVENUE ) / TOTAL_PLAN_QUANTITY
WHERE
BUDGET_VERSION_ID = l_budget_version_id AND
TOTAL_PLAN_QUANTITY > 0;
SELECT SUM( DECODE(STANDARD_BILL_RATE,0,NULL,STANDARD_BILL_RATE) * TOTAL_PLAN_QUANTITY )
INTO l_prj_level_revenue
FROM PA_RESOURCE_ASSIGNMENTS
WHERE
BUDGET_VERSION_ID = l_budget_version_id AND
RESOURCE_ASSIGNMENT_ID <> l_prj_res_assignment_id;
UPDATE PA_RESOURCE_ASSIGNMENTS SET
STANDARD_BILL_RATE = l_prj_level_revenue / l_tot_prj_quantity
WHERE
BUDGET_VERSION_ID = l_budget_version_id AND
RESOURCE_ASSIGNMENT_ID = l_prj_res_assignment_id;
UPDATE PA_RESOURCE_ASSIGNMENTS RA SET
AVERAGE_DISCOUNT_PERCENTAGE =
((STANDARD_BILL_RATE - AVERAGE_BILL_RATE)/STANDARD_BILL_RATE) * 100
WHERE
BUDGET_VERSION_ID = l_budget_version_id AND
STANDARD_BILL_RATE <> 0 AND
AVERAGE_BILL_RATE <> 0;
UPDATE PA_BUDGET_VERSIONS SET PLAN_PROCESSING_CODE = l_plan_processing_code,
PLAN_RUN_DATE = SYSDATE
WHERE BUDGET_VERSION_ID = l_budget_version_id;
/* The budget version record will be deleted if no forecast lines are generated,
this change is made to avoid the error from the page. */
DELETE FROM PA_BUDGET_VERSIONS WHERE BUDGET_VERSION_ID = l_budget_version_id;
l_prj_asg_id_tab.DELETE;
l_avg_bill_rate_tab.DELETE;
SELECT project_assignment_id,
ROUND(average_bill_rate,2) average_bill_rate
BULK COLLECT INTO
l_prj_asg_id_tab, l_avg_bill_rate_tab
FROM pa_resource_assignments WHERE
budget_version_id = l_budget_version_id AND
project_assignment_id > 0 AND
average_bill_rate IS NOT NULL;
PA_ASSIGNMENTS_PVT.Update_Revenue_Bill_Rate(
p_assignment_id_tbl => l_prj_asg_id_tab,
p_revenue_bill_rate_tbl => l_avg_bill_rate_tab,
x_return_status => x_return_status );
UPDATE_BUDG_VERSION( p_budget_version_id => l_budget_version_id );
SELECT plan_run_date INTO l_run_date
FROM pa_budget_versions
WHERE project_id = p_project_id AND
budget_type_code = 'FORECASTING_BUDGET_TYPE';