[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
OPT.PROJECT_ID,
BV.BUDGET_VERSION_ID,
OPT.PROJ_FP_OPTIONS_ID,
OPT.FIN_PLAN_TYPE_ID,
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_AMOUNT_SET_ID,
'REVENUE',OPT.REVENUE_AMOUNT_SET_ID,
'ALL',OPT.ALL_AMOUNT_SET_ID),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_FIN_PLAN_LEVEL_CODE,
'REVENUE',OPT.REVENUE_FIN_PLAN_LEVEL_CODE,
'ALL',OPT.ALL_FIN_PLAN_LEVEL_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_TIME_PHASED_CODE,
'REVENUE',OPT.REVENUE_TIME_PHASED_CODE,
'ALL',OPT.ALL_TIME_PHASED_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RESOURCE_LIST_ID,
'REVENUE',OPT.REVENUE_RESOURCE_LIST_ID,
'ALL',OPT.ALL_RESOURCE_LIST_ID),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RES_PLANNING_LEVEL,
'REVENUE',OPT.REVENUE_RES_PLANNING_LEVEL,
'ALL',OPT.ALL_RES_PLANNING_LEVEL),
OPT.RBS_VERSION_ID,
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,
'COST', OPT.COST_EMP_RATE_SCH_ID,
'ALL',OPT.COST_EMP_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,
'REVENUE',OPT.REV_EMP_RATE_SCH_ID,
'ALL',OPT.REV_EMP_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_JOB_RATE_SCH_ID,
'ALL',OPT.COST_JOB_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_JOB_RATE_SCH_ID,
'ALL',OPT.REV_JOB_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_NON_LABOR_RES_RATE_SCH_ID,
'ALL', OPT.COST_NON_LABOR_RES_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_NON_LABOR_RES_RATE_SCH_ID,
'ALL', OPT.REV_NON_LABOR_RES_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RES_CLASS_RATE_SCH_ID,
'ALL',OPT.COST_RES_CLASS_RATE_SCH_ID,
null)),
decode(nvl(opt.use_planning_rates_flag,'N'),'N',null,
'Y',DECODE(BV.VERSION_TYPE,'REVENUE',OPT.REV_RES_CLASS_RATE_SCH_ID,
'ALL',OPT.REV_RES_CLASS_RATE_SCH_ID,
null)),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_BURDEN_RATE_SCH_ID,
'ALL', OPT.COST_BURDEN_RATE_SCH_ID,
null),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_CURRENT_PLANNING_PERIOD,
'REVENUE',OPT.REV_CURRENT_PLANNING_PERIOD,
'ALL',OPT.ALL_CURRENT_PLANNING_PERIOD),
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_PERIOD_MASK_ID,
'REVENUE',OPT.REV_PERIOD_MASK_ID,
'ALL',OPT.ALL_PERIOD_MASK_ID),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_PLAN_TYPE_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_TYPE_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_TYPE_ID),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_PLAN_VERSION_ID,
'REVENUE',OPT.GEN_SRC_REV_PLAN_VERSION_ID,
'ALL',OPT.GEN_SRC_ALL_PLAN_VERSION_ID),
DECODE(BV.VERSION_TYPE,'COST', OPT1.GEN_SRC_COST_PLAN_VER_CODE,
'REVENUE',OPT1.GEN_SRC_REV_PLAN_VER_CODE,
'ALL',OPT1.GEN_SRC_ALL_PLAN_VER_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_SRC_CODE,
'REVENUE',OPT.GEN_REV_SRC_CODE,
'ALL',OPT.GEN_ALL_SRC_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_ETC_SRC_CODE,
'REVENUE',OPT.GEN_REV_ETC_SRC_CODE,
'ALL',OPT.GEN_ALL_ETC_SRC_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_CHANGE_DOC_FLAG,
'REVENUE',OPT.GEN_REV_INCL_CHANGE_DOC_FLAG,
'ALL',OPT.GEN_ALL_INCL_CHANGE_DOC_FLAG),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_OPEN_COMM_FLAG,
'REVENUE','N',
'ALL',OPT.GEN_ALL_INCL_OPEN_COMM_FLAG),
DECODE(BV.VERSION_TYPE,'COST','N',
'REVENUE',OPT.GEN_REV_INCL_BILL_EVENT_FLAG,
'ALL',OPT.GEN_ALL_INCL_BILL_EVENT_FLAG),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_RET_MANUAL_LINE_FLAG,
'REVENUE',OPT.GEN_REV_RET_MANUAL_LINE_FLAG,
'ALL',OPT.GEN_ALL_RET_MANUAL_LINE_FLAG),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_ACTUAL_AMTS_THRU_CODE,
'REVENUE',OPT.GEN_REV_ACTUAL_AMTS_THRU_CODE,
'ALL',OPT.GEN_ALL_ACTUAL_AMTS_THRU_CODE),
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_COST_INCL_UNSPENT_AMT_FLAG,
'REVENUE','N',
'ALL',OPT.GEN_ALL_INCL_UNSPENT_AMT_FLAG),
OPT.PLAN_IN_MULTI_CURR_FLAG,
decode(OPT.REVENUE_DERIVATION_METHOD,
'COST','C',
'WORK','T',
'EVENT','E'), --Bug 5462471
NVL(P.ORG_ID,-99) ORG_ID,
P.PROJECT_CURRENCY_CODE,
P.PROJFUNC_CURRENCY_CODE,
I.SET_OF_BOOKS_ID,
FP.RAW_COST_FLAG,
FP.BURDENED_COST_FLAG,
FP.REVENUE_FLAG,
FP.COST_QTY_FLAG,
FP.REVENUE_QTY_FLAG,
FP.ALL_QTY_FLAG,
FP.BILL_RATE_FLAG,
FP.COST_RATE_FLAG,
FP.BURDEN_RATE_FLAG,
DECODE(BV.WP_VERSION_FLAG,'Y',BV.PROJECT_STRUCTURE_VERSION_ID,
PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(opt.project_id )),
FB.PLAN_CLASS_CODE,
BV.VERSION_TYPE,
P.PROJECT_VALUE,
OPT.TRACK_WORKPLAN_COSTS_FLAG,
DECODE(BV.VERSION_TYPE,'COST', OPT.GEN_SRC_COST_WP_VERSION_ID,
'REVENUE',OPT.GEN_SRC_REV_WP_VERSION_ID,
'ALL',OPT.GEN_SRC_ALL_WP_VERSION_ID),
DECODE(OPT1.FIN_PLAN_PREFERENCE_CODE,
'COST_ONLY',OPT1.GEN_SRC_COST_WP_VER_CODE,
'REVENUE_ONLY',OPT1.GEN_SRC_REV_WP_VER_CODE,
'COST_AND_REV_SAME',OPT1.GEN_SRC_ALL_WP_VER_CODE,
'COST_AND_REV_SEP',( DECODE(BV.VERSION_TYPE,
'COST', OPT1.GEN_SRC_COST_WP_VER_CODE,
'REVENUE',OPT1.GEN_SRC_REV_WP_VER_CODE)))
INTO X_FP_COLS_REC.X_PROJECT_ID,
X_FP_COLS_REC.X_BUDGET_VERSION_ID,
X_FP_COLS_REC.X_PROJ_FP_OPTIONS_ID,
X_FP_COLS_REC.X_FIN_PLAN_TYPE_ID,
X_FP_COLS_REC.X_AMOUNT_SET_ID,
X_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE,
X_FP_COLS_REC.X_TIME_PHASED_CODE,
X_FP_COLS_REC.X_RESOURCE_LIST_ID,
X_FP_COLS_REC.X_RES_PLANNING_LEVEL,
X_FP_COLS_REC.X_RBS_VERSION_ID,
X_FP_COLS_REC.X_COST_EMP_RATE_SCH_ID,
X_FP_COLS_REC.X_REV_EMP_RATE_SCH_ID,
X_FP_COLS_REC.X_COST_JOB_RATE_SCH_ID,
X_FP_COLS_REC.X_REV_JOB_RATE_SCH_ID,
X_FP_COLS_REC.X_CNON_LABOR_RES_RATE_SCH_ID,
X_FP_COLS_REC.X_RNON_LABOR_RES_RATE_SCH_ID,
X_FP_COLS_REC.X_COST_RES_CLASS_RATE_SCH_ID,
X_FP_COLS_REC.X_REV_RES_CLASS_RATE_SCH_ID,
X_FP_COLS_REC.X_BURDEN_RATE_SCH_ID,
X_FP_COLS_REC.X_CURRENT_PLANNING_PERIOD,
X_FP_COLS_REC.X_PERIOD_MASK_ID,
X_FP_COLS_REC.X_GEN_SRC_PLAN_TYPE_ID,
X_FP_COLS_REC.X_GEN_SRC_PLAN_VERSION_ID,
X_FP_COLS_REC.X_GEN_SRC_PLAN_VER_CODE,
X_FP_COLS_REC.X_GEN_SRC_CODE,
X_FP_COLS_REC.X_GEN_ETC_SRC_CODE,
X_FP_COLS_REC.X_GEN_INCL_CHANGE_DOC_FLAG,
X_FP_COLS_REC.X_GEN_INCL_OPEN_COMM_FLAG,
X_FP_COLS_REC.X_GEN_INCL_BILL_EVENT_FLAG,
X_FP_COLS_REC.X_GEN_RET_MANUAL_LINE_FLAG,
X_FP_COLS_REC.X_GEN_ACTUAL_AMTS_THRU_CODE,
X_FP_COLS_REC.X_GEN_INCL_UNSPENT_AMT_FLAG,
X_FP_COLS_REC.X_PLAN_IN_MULTI_CURR_FLAG,
X_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,--Bug 5462471
X_FP_COLS_REC.X_ORG_ID,
X_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
X_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
X_FP_COLS_REC.X_SET_OF_BOOKS_ID,
X_FP_COLS_REC.X_RAW_COST_FLAG,
X_FP_COLS_REC.X_BURDENED_FLAG,
X_FP_COLS_REC.X_REVENUE_FLAG,
X_FP_COLS_REC.X_COST_QUANTITY_FLAG,
X_FP_COLS_REC.X_REV_QUANTITY_FLAG,
X_FP_COLS_REC.X_ALL_QUANTITY_FLAG,
X_FP_COLS_REC.X_BILL_RATE_FLAG,
X_FP_COLS_REC.X_COST_RATE_FLAG,
X_FP_COLS_REC.X_BURDEN_RATE_FLAG,
X_FP_COLS_REC.X_PROJECT_STRUCTURE_VERSION_ID,
X_FP_COLS_REC.X_PLAN_CLASS_CODE,
X_FP_COLS_REC.X_VERSION_TYPE,
X_FP_COLS_REC.X_PROJECT_VALUE,
X_FP_COLS_REC.X_TRACK_WORKPLAN_COSTS_FLAG,
X_FP_COLS_REC.X_GEN_SRC_WP_VERSION_ID,
X_FP_COLS_REC.X_GEN_SRC_WP_VER_CODE
FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT, PA_PROJ_FP_OPTIONS OPT1,
PA_PROJECTS_ALL P, PA_IMPLEMENTATIONS_ALL I,
PA_FIN_PLAN_AMOUNT_SETS FP,
PA_FIN_PLAN_TYPES_B FB
WHERE BV.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND OPT.PROJECT_ID = BV.PROJECT_ID
AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
AND OPT.FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ID = BV.PROJECT_ID
AND I.ORG_ID = P.ORG_ID -- R12 MOAC 4447573: NVL(I.ORG_ID,-99) = NVL(P.ORG_ID,-99)
AND FP.FIN_PLAN_AMOUNT_SET_ID =
DECODE(BV.VERSION_TYPE,'COST', OPT.COST_AMOUNT_SET_ID,
'REVENUE',OPT.REVENUE_AMOUNT_SET_ID,
'ALL',OPT.ALL_AMOUNT_SET_ID)
AND FB.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
AND OPT1.PROJECT_ID = BV.PROJECT_ID
AND OPT1.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE'
AND OPT1.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID;
/* Plan_ver_code is selected at PLAN_TYPE instead of PLAN_VERSION */
--dbms_output.put_line('in utils after select');
SELECT 'Y'
INTO l_flag
FROM PA_COMMITMENT_TXNS
WHERE PROJECT_ID = P_PROJECT_ID
AND DENOM_CURRENCY_CODE <> P_PROJ_CURRENCY_CODE
AND NVL(generation_error_flag,'N') = 'N'
AND ROWNUM < 2;
SELECT fin_plan_preference_code
INTO l_fp_preference_code
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND fin_plan_option_level_code =
PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT budget_version_id
INTO l_current_original_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = NVL(p_version_type,l_version_type)
AND budget_status_code = 'B'
AND current_flag = 'Y';
SELECT budget_version_id
INTO l_current_original_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = NVL(p_version_type,l_version_type)
AND budget_status_code = 'B'
AND current_original_flag = 'Y';
SELECT proj_fp_options_id
INTO l_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = l_current_original_version_id;
SELECT project_id
,task_id
,unit_of_measure
,resource_class_code
,organization_id
,job_id
,person_id
,expenditure_type
,non_labor_resource
,bom_resource_id
,inventory_item_id
,item_category_id
,mfc_cost_type_id
,rate_based_flag
,rate_expenditure_org_id
,rate_expenditure_type
FROM pa_resource_assignments ra
WHERE ra.resource_assignment_id = c_resource_assignment_id;
SELECT non_labor_bill_rate_org_id
,non_labor_schedule_discount
,non_labor_schedule_fixed_date
,non_lab_std_bill_rt_sch_id
,emp_bill_rate_schedule_id
,job_bill_rate_schedule_id
,labor_bill_rate_org_id
,labor_sch_type
,non_labor_sch_type
,top_task_id
FROM pa_tasks t
WHERE t.task_id = c_task_id;
SELECT assign_precedes_task
,bill_job_group_id
,carrying_out_organization_id
,multi_currency_billing_flag
,org_id
,non_labor_bill_rate_org_id
,project_currency_code
,non_labor_schedule_discount
,non_labor_schedule_fixed_date
,non_lab_std_bill_rt_sch_id
,project_type
,projfunc_currency_code
,emp_bill_rate_schedule_id
,job_bill_rate_schedule_id
,labor_bill_rate_org_id
,labor_sch_type
,non_labor_sch_type
FROM pa_projects_all ppa
WHERE ppa.project_id = c_proj_id;
SELECT decode(pfo.use_planning_rates_flag,'N',
pfo.res_class_bill_rate_sch_id,
decode(bv.version_type,'REVENUE',
pfo.rev_res_class_rate_sch_id,
'ALL' ,pfo.rev_res_class_rate_sch_id,
NULL)) res_class_bill_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',
pfo.res_class_raw_cost_sch_id,
decode(bv.version_type,'COST',
pfo.cost_res_class_rate_sch_id, 'ALL',
pfo.cost_res_class_rate_sch_id,
NULL)) res_class_raw_cost_sch_id
,pfo.use_planning_rates_flag
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'REVENUE',
pfo.rev_job_rate_sch_id, 'ALL',
pfo.rev_job_rate_sch_id, NULL)) rev_job_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'COST',
pfo.cost_job_rate_sch_id, 'ALL',
pfo.cost_job_rate_sch_id, NULL)) cost_job_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'REVENUE',
pfo.rev_emp_rate_sch_id, 'ALL',
pfo.rev_emp_rate_sch_id, NULL)) rev_emp_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'COST',
pfo.cost_emp_rate_sch_id, 'ALL',
pfo.cost_emp_rate_sch_id, NULL)) cost_emp_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'REVENUE',
pfo.rev_non_labor_res_rate_sch_id, 'ALL',
pfo.rev_non_labor_res_rate_sch_id, NULL))
rev_non_labor_res_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'COST' ,
pfo.cost_non_labor_res_rate_sch_id,'ALL',
pfo.cost_non_labor_res_rate_sch_id, NULL))
cost_non_labor_res_rate_sch_id
,decode(pfo.use_planning_rates_flag,'N',null,
decode(bv.version_type,'COST',
pfo.cost_burden_rate_sch_id,'ALL',
pfo.cost_burden_rate_sch_id, NULL))
cost_burden_rate_sch_id
,bv.version_type
FROM pa_proj_fp_options pfo,
pa_budget_versions bv
WHERE pfo.fin_plan_version_id = bv.budget_version_id
AND bv.budget_version_id = p_budget_version_id;
SELECT res_format_id
INTO x_res_format_id
FROM pa_resource_list_members
WHERE resource_list_member_id = p_resource_list_member_id;
SELECT etc_start_date
INTO x_etc_start_date
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id
AND etc_start_date is not null;
SELECT (etc_start_date)-1
INTO x_actuals_thru_date
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id
AND etc_start_date is not null;
SELECT NVL(UNCATEGORIZED_FLAG,'N')
INTO x_rl_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
SELECT resource_list_member_id
INTO x_uc_res_list_rlm_id
FROM pa_resource_list_members
WHERE resource_class_code = P_RESOURCE_CLASS_CODE
AND object_type = 'RESOURCE_LIST'
AND resource_list_id = p_resource_list_id;
SELECT NVL(control_flag,'N')
INTO l_control_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = P_RESOURCE_LIST_ID;
select resource_list_member_id
into x_rlm_id
from pa_resource_list_members
where object_type = 'RESOURCE_LIST'
and object_id = p_resource_list_id
and resource_list_id = p_resource_list_id
and resource_class_code = P_RESOURCE_CLASS_CODE
and RESOURCE_CLASS_FLAG='Y';
select resource_list_member_id
into x_rlm_id
from pa_resource_list_members
where object_type = 'PROJECT'
and object_id = p_project_id
and resource_list_id = p_resource_list_id
and resource_class_code = P_RESOURCE_CLASS_CODE
and RESOURCE_CLASS_FLAG='Y';
* Note that when the generation option is 'Task Level Selection', we will
* raise an error only when at least one of the tasks has ETC generation
* source as 'WORKPLAN_RESOURCES' or 'WORK_QUANTITY'.
*2.For Forecast/Budget generation from Staffing Plan:
* 1)Revenue versions can't be generated.
* 2)Versions with Resource List of None can't be generated.
* --Bug 5325254
*3.Forecast generation from non-timephased Workplan is not supported.
* Note: Earlier, we restricted forecast generation from non-timephased
* financial plans as well. However, this restriction has been relaxed
* to support CDM's requirements.
*4.Forecast/Budget generation from:
* ADDED FOR ER 4391321:
* 1)Workplan and/or Financial Plan that has any rejection code in the
* budget lines should result in a warning or error from the UI and
* Concurrent Program, respectively.
* 2)Staffing Plan that has any Forecast Items with ERROR_FLAG = 'Y'
* should result in a warning or error from the UI and Concurrent
* Program, respectively.
*5.Forecast/Budget Generation,where Revenue Derivation Method of target
* is different from source, is not supported. ER: 5152892
*PARAMETERS:
*
*P_CALLING_CONTEXT
*-----------------
* 'CONCURRENT' : this api is being called from a Concurrent Program.
* 'SELF_SERVICE': this api is being called from the Self-Service pages.
*
*Added for ER 4391321:
*
*P_CHECK_SRC_ERRORS_FLAG
*-----------------------
* 'Y': when source is FP or WP, check source budget line rejection codes.
* when source is Staffing Plan, check ERROR_FLAG for forecast items.
* 'N': do not check source rejection codes or ERROR_FLAG values.
*By default, P_CHECK_SRC_ERRORS_FLAG is 'Y'.
*
*X_WARNING_MESSAGE
*----------------------
* NULL: P_CHECK_SRC_ERRORS_FLAG = 'N', OR
* P_CHECK_SRC_ERRORS_FLAG = 'Y' and source passed rejection code /
* ERROR_FLAG validation.
* Otherwise, contains the translated warning message text.
*X_WARNING_MESSAGE will be null whenever P_CALLING_CONTEXT = 'CONCURRENT'.
**/
PROCEDURE VALIDATE_SUPPORT_CASES
(P_FP_COLS_REC_TGT IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_CONTEXT IN VARCHAR2,
P_CHECK_SRC_ERRORS_FLAG IN VARCHAR2,
X_WARNING_MESSAGE OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_AMOUNT_UTILS.VALIDATE_SUPPORT_CASES';
/* The version defaulting API passes updated Target version details
* record back as an OUT parameter. */
PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER
( P_FP_COLS_REC_TGT => l_fp_cols_rec_tgt,
P_CALLING_CONTEXT => p_calling_context,
X_FP_COLS_REC_TGT => x_fp_cols_rec_tgt,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA );
SELECT COUNT(*) INTO l_count
FROM pa_tasks
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND (gen_etc_source_code = 'WORKPLAN_RESOURCES'
OR gen_etc_source_code = 'WORK_QUANTITY')
AND rownum < 2;
SELECT nvl(UNCATEGORIZED_FLAG,'N')
INTO l_uncategorized_flag
FROM pa_resource_lists
WHERE resource_list_id = l_fp_cols_rec_tgt.X_RESOURCE_LIST_ID;
SELECT COUNT(*) INTO l_count
FROM pa_tasks
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND (gen_etc_source_code = 'WORKPLAN_RESOURCES'
OR gen_etc_source_code = 'WORK_QUANTITY')
AND rownum < 2;
SELECT COUNT(*) INTO l_count
FROM pa_tasks
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND gen_etc_source_code = 'FINANCIAL_PLAN'
AND rownum < 2;
* to selectively call the defaulting API based on the case introduced
* for ER 4391254.
* At this point, we are doing defaulting for the remainder of the API.
* FUTURE VALIDATION CASES THAT NEED SPECIAL ORDERING WITH RESPECT TO
* THE DEFAULTING LOGIC SHOULD BE PLACED ABOVE THIS POINT. */
IF l_plan_class_code = 'BUDGET' THEN
-- Try to default the source version if both the WP and FP source
-- versions are Null and we have not done defaulting earlier.
IF l_default_bdgt_src_ver_flag = 'Y' AND
l_fp_cols_rec_tgt.X_GEN_SRC_WP_VERSION_ID IS NULL AND
l_fp_cols_rec_tgt.X_GEN_SRC_PLAN_VERSION_ID IS NULL THEN
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_called_mode => p_calling_context,
p_msg => 'Before calling
PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER',
p_module_name => l_module_name,
p_log_level => 5);
/* The version defaulting API passes updated Target version details
* record back as an OUT parameter. */
PA_FP_GEN_AMOUNT_UTILS.DEFAULT_BDGT_SRC_VER
( P_FP_COLS_REC_TGT => l_fp_cols_rec_tgt,
P_CALLING_CONTEXT => p_calling_context,
X_FP_COLS_REC_TGT => x_fp_cols_rec_tgt,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA );
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS
( SELECT null
FROM pa_tasks
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND gen_etc_source_code IN ('WORKPLAN_RESOURCES','WORK_QUANTITY') );
END; -- select l_count
SELECT 1 INTO l_bl_rejection_code_count
FROM DUAL
WHERE EXISTS
( SELECT null
FROM pa_budget_lines
WHERE budget_version_id = l_source_wp_ver_id
AND ( cost_rejection_code IS NOT NULL
OR revenue_rejection_code IS NOT NULL
OR burden_rejection_code IS NOT NULL
OR other_rejection_code IS NOT NULL
OR pc_cur_conv_rejection_code IS NOT NULL
OR pfc_cur_conv_rejection_code IS NOT NULL ) );
END; -- select l_bl_rejection_code_count
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS
( SELECT null
FROM pa_tasks
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND gen_etc_source_code = 'FINANCIAL_PLAN' );
END; -- select l_count
SELECT 1 INTO l_bl_rejection_code_count
FROM DUAL
WHERE EXISTS
( SELECT null
FROM pa_budget_lines
WHERE budget_version_id = l_source_fp_ver_id
AND ( cost_rejection_code IS NOT NULL
OR revenue_rejection_code IS NOT NULL
OR burden_rejection_code IS NOT NULL
OR other_rejection_code IS NOT NULL
OR pc_cur_conv_rejection_code IS NOT NULL
OR pfc_cur_conv_rejection_code IS NOT NULL ) );
END; -- select l_bl_rejection_code_count
* Task-Level Selection, both WP and FP sources may
* have budget lines with rejection codes. Check this. */
IF l_warning_message_code = lc_message_code_WP THEN
l_warning_message_code := lc_message_code_WPFP;
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS
( SELECT null
FROM PA_FORECAST_ITEMS
WHERE project_id = l_fp_cols_rec_tgt.X_PROJECT_ID
AND error_flag = 'Y' );
END; -- select l_count
l_update_details_flag VARCHAR2(1);
l_update_details_flag := 'Y';
corresponding to the work plan structure version id selected
as the source for the budget generation when the budget
generation source is Work plan. */
SELECT project_structure_version_id
INTO l_wp_id
FROM pa_budget_versions
WHERE budget_version_id = l_source_id;
* Now, we need to update back to pa_proj_fp_options*/
IF l_version_type = 'COST' THEN
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_COST_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_ALL_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_REV_WP_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
UPDATE PA_BUDGET_VERSIONS
SET project_structure_version_id = l_wp_id
WHERE budget_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
* Now, we need to update back to pa_proj_fp_options*/
IF l_version_type = 'COST' THEN
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_COST_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_ALL_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_SRC_REV_PLAN_VERSION_ID = l_source_id
WHERE fin_plan_version_id = P_FP_COLS_REC_TGT.X_BUDGET_VERSION_ID;
/* Get updated Target version details */
-- Currently, l_update_details_flag is always 'Y', but may change in the future.
IF l_update_details_flag = 'Y' THEN
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(p_called_mode => p_calling_context,
p_msg => 'Before calling
PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
p_module_name => l_module_name,
p_log_level => 5);
END IF; -- l_update_details_flag check