[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Commitments';
PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
, x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
Delete From PA_PROJECT_ACCUM_COMMITMENTS PAC
Where PAC.Project_Accum_id IN
(Select Project_Accum_id
from PA_PROJECT_ACCUM_HEADERS PAH
Where PAH.Project_Id = x_project_id
and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level record
)
AND rownum <= pa_proj_accum_main.x_commit_size;
UPDATE pa_project_accum_commitments SET
CMT_RAW_COST_ITD = 0
,CMT_RAW_COST_YTD = 0
,CMT_RAW_COST_PP = 0
,CMT_RAW_COST_PTD = 0
,CMT_BURDENED_COST_ITD = 0
,CMT_BURDENED_COST_YTD = 0
,CMT_BURDENED_COST_PP = 0
,CMT_BURDENED_COST_PTD = 0
,CMT_QUANTITY_ITD = 0
,CMT_QUANTITY_YTD = 0
,CMT_QUANTITY_PP = 0
,CMT_QUANTITY_PTD = 0
,CMT_UNIT_OF_MEASURE = NULL
,REQUEST_ID = pa_proj_accum_main.x_request_id
,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
,LAST_UPDATE_DATE = Trunc(sysdate)
,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
WHERE Project_Accum_id = l_Prj_Lvl_Accum_Id;
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
End Delete_Project_Commitments;
Procedure Delete_Project_Budgets (x_project_Id In Number,
x_budget_Type_Code In Varchar2,
x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_code In Out NOCOPY Number ) Is --File.Sql.39 bug 4440895
-- Use this Cursor to Retrieve the following:
--
-- 1) The specified r11.5.7 budget_type_code
--
-- 2) The FP model entities corresponding to r11.5.7 x_budget_type_code in ('AC', 'AR')
--
-- 3) A-L-L r11.5.7 Budget and FP Model budget records
--
-- Please Note:
-- Unlike the summarization pa_project_accum_budgets INSERT logic, this cursor logic does NOT need
-- to consider as many cases for the FP Model for the following reasons:
--
-- 1) This logic simply deletes records. It is not concerned with double-counting amounts.
--
-- 2) If to_char(fin_plan_type_id) returns a value that has
-- NOT been previously inserted as budget record, then the delete will simply not purge anything
-- for that cursor record. No harm.
--
--
Cursor Budget_ver_cur
IS
SELECT PAB.budget_type_code budget_type_code
FROM
(
SELECT pabv.budget_type_code budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NOT NULL -- r11.5.7 Budget Model
and pabv.Budget_type_code = nvl(x_budget_type_code, pabv.Budget_type_code)
UNION ALL
SELECT to_char(fin_plan_type_id) budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NULL -- Strictly FP model, NO AC/AR budget_type_codes
and x_budget_type_code IS NULL
UNION ALL
SELECT 'AC' budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NULL -- FP model, Approved Cost
and nvl(pabv.approved_cost_plan_type_flag, 'N') = 'Y'
and 'AC' = nvl(x_budget_type_code, 'AC')
UNION ALL
SELECT 'AR' budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NULL -- FP model, Approved Revenue
and nvl(pabv.approved_rev_plan_type_flag, 'N') = 'Y'
and 'AR' = nvl(x_budget_type_code, 'AR')
UNION ALL
SELECT 'FC' budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NULL -- FP model, PRIMARY FORECAST Cost
and nvl(pabv.primary_cost_forecast_flag, 'N') = 'Y'
and 'FC' = nvl(x_budget_type_code, 'FC')
UNION ALL
SELECT 'FR' budget_type_code
FROM PA_BUDGET_VERSIONS PABV
WHERE pabv.Project_id = x_project_id
AND pabv.Current_Flag = 'Y'
AND pabv.Resource_Accumulated_Flag = 'N'
and pabv.budget_type_code IS NULL -- FP model, PRIMARY FORECAST Revenue
and nvl(pabv.primary_rev_forecast_flag, 'N') = 'Y'
and 'FR' = nvl(x_budget_type_code, 'FR')
) PAB;
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Budgets';
x_err_stage := 'deleteing pa_project_accum_budgets';
PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
, x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
Delete From PA_PROJECT_ACCUM_BUDGETS
Where Budget_Type_Code = Budget_ver_rec.budget_type_code
and Project_Accum_id IN
(Select Project_Accum_id
from PA_PROJECT_ACCUM_HEADERS PAH
Where PAH.Project_Id = x_project_id
and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level records
);
UPDATE pa_project_accum_budgets SET
BASE_RAW_COST_ITD = 0
,BASE_RAW_COST_YTD = 0
,BASE_RAW_COST_PP = 0
,BASE_RAW_COST_PTD = 0
,BASE_BURDENED_COST_ITD = 0
,BASE_BURDENED_COST_YTD = 0
,BASE_BURDENED_COST_PP = 0
,BASE_BURDENED_COST_PTD = 0
,ORIG_RAW_COST_ITD = 0
,ORIG_RAW_COST_YTD = 0
,ORIG_RAW_COST_PP = 0
,ORIG_RAW_COST_PTD = 0
,ORIG_BURDENED_COST_ITD = 0
,ORIG_BURDENED_COST_YTD = 0
,ORIG_BURDENED_COST_PP = 0
,ORIG_BURDENED_COST_PTD = 0
,BASE_QUANTITY_ITD = 0
,BASE_QUANTITY_YTD = 0
,BASE_QUANTITY_PP = 0
,BASE_QUANTITY_PTD = 0
,ORIG_QUANTITY_ITD = 0
,ORIG_QUANTITY_YTD = 0
,ORIG_QUANTITY_PP = 0
,ORIG_QUANTITY_PTD = 0
,BASE_LABOR_HOURS_ITD = 0
,BASE_LABOR_HOURS_YTD = 0
,BASE_LABOR_HOURS_PP = 0
,BASE_LABOR_HOURS_PTD = 0
,ORIG_LABOR_HOURS_ITD = 0
,ORIG_LABOR_HOURS_YTD = 0
,ORIG_LABOR_HOURS_PP = 0
,ORIG_LABOR_HOURS_PTD = 0
,BASE_REVENUE_ITD = 0
,BASE_REVENUE_YTD = 0
,BASE_REVENUE_PP = 0
,BASE_REVENUE_PTD = 0
,ORIG_REVENUE_ITD = 0
,ORIG_REVENUE_YTD = 0
,ORIG_REVENUE_PP = 0
,ORIG_REVENUE_PTD = 0
,BASE_UNIT_OF_MEASURE = NULL
,ORIG_UNIT_OF_MEASURE = NULL
,BASE_RAW_COST_TOT = 0
,BASE_BURDENED_COST_TOT = 0
,ORIG_RAW_COST_TOT = 0
,ORIG_BURDENED_COST_TOT = 0
,BASE_REVENUE_TOT = 0
,ORIG_REVENUE_TOT = 0
,BASE_LABOR_HOURS_TOT = 0
,ORIG_LABOR_HOURS_TOT = 0
,BASE_QUANTITY_TOT = 0
,ORIG_QUANTITY_TOT = 0
,REQUEST_ID = pa_proj_accum_main.x_request_id
,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
,LAST_UPDATE_DATE = Trunc(sysdate)
,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
WHERE Budget_Type_Code = Budget_ver_rec.budget_type_code
AND Project_Accum_id = l_Prj_Lvl_Accum_Id;
Delete From PA_PROJECT_ACCUM_BUDGETS
Where Budget_Type_Code = Budget_ver_rec.budget_type_code
and Project_Accum_id IN
(Select Project_Accum_id
from PA_PROJECT_ACCUM_HEADERS PAH
Where PAH.Project_Id = x_project_id
);
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(SQL%ROWCOUNT));
End Delete_Project_Budgets;
Procedure Delete_Project_Actuals (x_project_Id In Number,
x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
V_Old_Stack Varchar2(630);
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Actuals';
PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
, x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
Delete From PA_PROJECT_ACCUM_ACTUALS PAA
Where PAA.Project_Accum_id IN
(Select Project_Accum_id
from PA_PROJECT_ACCUM_HEADERS PAH
Where PAH.Project_Id = x_project_id
and PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Skip project-level records
)
AND rownum <= pa_proj_accum_main.x_commit_size;
UPDATE pa_project_accum_actuals SET
RAW_COST_ITD = 0
,RAW_COST_YTD = 0
,RAW_COST_PP = 0
,RAW_COST_PTD = 0
,BILLABLE_RAW_COST_ITD = 0
,BILLABLE_RAW_COST_YTD = 0
,BILLABLE_RAW_COST_PP = 0
,BILLABLE_RAW_COST_PTD = 0
,BURDENED_COST_ITD = 0
,BURDENED_COST_YTD = 0
,BURDENED_COST_PP = 0
,BURDENED_COST_PTD = 0
,BILLABLE_BURDENED_COST_ITD = 0
,BILLABLE_BURDENED_COST_YTD = 0
,BILLABLE_BURDENED_COST_PP = 0
,BILLABLE_BURDENED_COST_PTD = 0
,QUANTITY_ITD = 0
,QUANTITY_YTD = 0
,QUANTITY_PP = 0
,QUANTITY_PTD = 0
,LABOR_HOURS_ITD = 0
,LABOR_HOURS_YTD = 0
,LABOR_HOURS_PP = 0
,LABOR_HOURS_PTD = 0
,BILLABLE_QUANTITY_ITD = 0
,BILLABLE_QUANTITY_YTD = 0
,BILLABLE_QUANTITY_PP = 0
,BILLABLE_QUANTITY_PTD = 0
,BILLABLE_LABOR_HOURS_ITD = 0
,BILLABLE_LABOR_HOURS_YTD = 0
,BILLABLE_LABOR_HOURS_PP = 0
,BILLABLE_LABOR_HOURS_PTD = 0
,REVENUE_ITD = 0
,REVENUE_YTD = 0
,REVENUE_PP = 0
,REVENUE_PTD = 0
,TXN_UNIT_OF_MEASURE = NULL
,REQUEST_ID = pa_proj_accum_main.x_request_id
,LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by
,LAST_UPDATE_DATE = Trunc(sysdate)
,LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
WHERE Project_Accum_id = l_Prj_Lvl_Accum_Id;
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
End Delete_Project_Actuals;
Procedure Delete_Res_List_Actuals (x_project_id In Number,
x_Resource_list_id In Number,
x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
V_Old_Stack Varchar2(630);
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Res_List_Actuals';
x_err_stage := 'deleteing PA_PROJECT_ACCUM_ACTUALS';
Delete From PA_PROJECT_ACCUM_ACTUALS PAA
Where Project_Accum_id IN
(Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
PAH.Project_Id = x_project_id and
PAH.resource_list_member_id <> 0 and
PAH.Resource_List_id = NVL(x_Resource_list_id,PAH.Resource_List_id))
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
End Delete_Res_List_Actuals;
Procedure Delete_Res_List_Commitments (x_project_id In Number,
x_Resource_list_id In Number,
x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
V_Old_Stack Varchar2(630);
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Res_List_Commitments';
Delete From PA_PROJECT_ACCUM_COMMITMENTS PAC
Where Project_Accum_id IN
(Select Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH Where
PAH.Project_Id = x_project_id and
PAH.resource_list_member_id <> 0 and
PAH.Resource_List_id = NVL(x_Resource_list_id,PAH.Resource_List_id))
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
End Delete_Res_List_Commitments;
Procedure Delete_Project_Accum_Headers (x_project_id In Number,
x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
V_Old_Stack Varchar2(630);
x_err_stack ||'->PA_DELETE_ACCUM_RECS.Delete_Project_Accum_Headers';
PA_DELETE_ACCUM_RECS.Get_Prj_Lvl_Accum_Id (p_project_id => x_project_Id
, x_Prj_Lvl_Accum_Id => l_Prj_Lvl_Accum_Id
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
Delete From PA_PROJECT_ACCUM_HEADERS PAH Where
PAH.Project_Id = x_project_id
AND PAH.project_accum_id <> l_Prj_Lvl_Accum_Id -- Don't delete project-level row details.
AND Not Exists
(Select 'Yes' from PA_PROJECT_ACCUM_ACTUALS PAA
Where PAH.PROJECT_ACCUM_ID = PAA.PROJECT_ACCUM_ID)
AND Not Exists
(Select 'Yes' from PA_PROJECT_ACCUM_COMMITMENTS PAC
Where PAH.PROJECT_ACCUM_ID = PAC.PROJECT_ACCUM_ID)
AND Not Exists
(Select 'Yes' from PA_PROJECT_ACCUM_BUDGETS PAB
Where PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID)
and rownum <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('Number of Records Deleted = '|| TO_CHAR(tot_recs_processed));
End Delete_Project_Accum_Headers;
IF (p_project_id <> nvl(PA_DELETE_ACCUM_RECS.G_Prj_Lvl_project_id, '-99')
)
THEN
BEGIN
-- FETCH New Project-Level Project_Accum_Id
SELECT project_accum_id
INTO l_Prj_Lvl_Accum_Id
FROM pa_project_accum_headers
WHERE project_id = p_project_id
AND task_id = 0
AND resource_list_member_id = 0;
PA_DELETE_ACCUM_RECS.G_Prj_Lvl_project_id := p_project_id;
PA_DELETE_ACCUM_RECS.G_Prj_Lvl_Accum_Id := l_Prj_Lvl_Accum_Id;
x_Prj_Lvl_Accum_Id := PA_DELETE_ACCUM_RECS.G_Prj_Lvl_Accum_Id;
( p_pkg_name => 'PA_DELETE_ACCUM_RECS'
, p_procedure_name => 'GET_PRJ_LVL_ACCUM_ID'
, p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
);