[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = c_budget_type_code;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'BUDGET CHANGE REASON'
AND lookup_code = c_change_reason_code;
SELECT ppt.allow_cost_budget_entry_flag
,ppt.allow_rev_budget_entry_flag
FROM pa_project_types ppt
,pa_projects_all ppa
WHERE ppa.project_id = c_project_id
AND ppa.project_type = ppt.project_type;
SELECT *
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = c_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT fin_plan_preference_code
,nvl(plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
,approved_rev_plan_type_flag
,projfunc_cost_rate_type
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_cost_rate_type
,project_cost_rate_date_type
,project_cost_rate_date
,project_rev_rate_type
,project_rev_rate_date_type
,project_rev_rate_date
FROM pa_proj_fp_options
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND fin_plan_version_id IS NULL
AND fin_plan_option_level_code= PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT segment1
,baseline_funding_flag
FROM pa_projects_all
WHERE project_id=c_project_id;
SELECT decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_fin_plan_level_code) fin_plan_level_code
,decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_resource_list_id) resource_list_id
,decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_time_phased_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_time_phased_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_time_phased_code) time_phased_code
FROM pa_proj_fp_options
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND fin_plan_version_id IS NULL
AND fin_plan_option_level_code= PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT budget_version_id
,budget_status_code
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code IN ('W','S')
AND ci_id IS NULL; -- Bug # 3507156 --Added an extra clause ci_id IS NULL
SELECT budget_version_id
,budget_status_code
,record_version_number
,plan_processing_code
FROM pa_budget_versions
WHERE project_id = c_project_id
AND fin_plan_type_id = c_fin_plan_type_id
AND current_working_flag='Y'
AND version_type = c_version_type
AND budget_status_code IN ('W','S')
AND ci_id IS NULL; -- Bug # 3507156 --Added an extra clause ci_id IS NULL
SELECT 'X'
FROM pa_fin_plan_types_b fin ,pa_proj_fp_options pfo
WHERE pfo.project_id=c_project_id
AND pfo.fin_plan_option_level_code=PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE --AMG UT2
AND pfo.fin_plan_type_id = fin.fin_plan_type_id
AND fin.migrated_frm_bdgt_typ_code = c_budget_type_code;
SELECT 'x'
FROM pa_budget_versions bv
,pa_resource_assignments ra
,pa_budget_lines bl
WHERE bv.budget_version_id = c_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
AND bv.ci_id IS NULL -- Bug # 3507156 --Added an extra clause ci_id IS NULL
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
Select 'X'
from pa_lookups
where lookup_type='PM_PRODUCT_CODE'
and lookup_code = c_pm_product_code;
IF pa_security.allow_update (x_project_id => px_pa_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
SELECT migration_code
INTO l_res_list_migration_code
FROM pa_resource_lists_all_bg
WHERE resource_list_id = px_resource_list_id;
SELECT name,use_for_workplan_flag
INTO l_fin_plan_type_name,l_workplan_flag
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = px_fin_plan_type_id;
SELECT migration_code
INTO l_res_list_migration_code
FROM pa_resource_lists_all_bg
WHERE resource_list_id = px_resource_list_id;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE exists(SELECT 'X' FROM pa_period_masks_b WHERE trunc(sysdate) between EFFECTIVE_START_DATE
AND nvl( EFFECTIVE_END_DATE,sysdate)
AND TIME_PHASE_CODE = px_time_phased_code); -- Bug # 3507156
SELECT 1
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id
AND use_for_workplan_flag = 'Y';
SELECT entry_level_code
,categorization_code
,time_phased_type_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = c_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT nvl(plan_in_multi_curr_flag,'N') multi_curr_flag
,decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_fin_plan_level_code) fin_plan_level_code
,decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_resource_list_id) resource_list_id
,decode(c_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_time_phased_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_time_phased_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_time_phased_code) time_phased_code
FROM pa_proj_fp_options
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND fin_plan_version_id = c_fin_plan_version_id;
SELECT segment1
FROM pa_projects_all
WHERE project_id=c_project_id;
Select 'X'
from pa_lookups
where lookup_type='PM_PRODUCT_CODE'
and lookup_code = c_pm_product_code;
SELECT budget_version_id
, budget_entry_method_code
, resource_list_id
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'W'
AND ci_id IS NULL;
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND version_type=c_version_type
AND version_number=c_version_number
AND budget_status_code='W'
AND ci_id is null;
PROCEDURE insert_budget_line
( p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_budget_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_budget_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_period_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_raw_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_burdened_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_revenue IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_quantity IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_budget_line_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_time_phased_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_entry_level_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_amount_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_entry_method_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_categorization_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )--Bug 4224464
IS
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'insert_budget_line';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT pr.unit_of_measure
,prlm.track_as_labor_flag
FROM pa_resources pr
,pa_resource_lists prl
,pa_resource_list_members prlm
WHERE prl.resource_list_id = c_resource_list_id
AND pr.resource_id = prlm.resource_id
AND prl.resource_list_id = prlm.resource_list_id
AND prlm.resource_list_member_id = c_resource_list_member_id;
SAVEPOINT insert_budget_line_pvt;
NULL; --we don't insert budget lines with all zero's
ROLLBACK TO insert_budget_line_pvt;
ROLLBACK TO insert_budget_line_pvt;
ROLLBACK TO insert_budget_line_pvt;
END insert_budget_line;
PROCEDURE update_budget_line_sql
( p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_budget_entry_method_code IN VARCHAR2
,p_resource_assignment_id IN NUMBER
,p_start_date IN DATE
,p_time_phased_type_code IN VARCHAR2
,p_description IN VARCHAR2
,p_quantity IN NUMBER
,p_raw_cost IN NUMBER
,p_burdened_cost IN NUMBER
,p_revenue IN NUMBER
,p_change_reason_code IN VARCHAR2
,p_attribute_category IN VARCHAR2
,p_attribute1 IN VARCHAR2
,p_attribute2 IN VARCHAR2
,p_attribute3 IN VARCHAR2
,p_attribute4 IN VARCHAR2
,p_attribute5 IN VARCHAR2
,p_attribute6 IN VARCHAR2
,p_attribute7 IN VARCHAR2
,p_attribute8 IN VARCHAR2
,p_attribute9 IN VARCHAR2
,p_attribute10 IN VARCHAR2
,p_attribute11 IN VARCHAR2
,p_attribute12 IN VARCHAR2
,p_attribute13 IN VARCHAR2
,p_attribute14 IN VARCHAR2
,p_attribute15 IN VARCHAR2
)
IS
--needed to get the current data of a budget line
CURSOR l_budget_line_csr
(p_resource_assigment_id NUMBER
,p_budget_start_date DATE )
IS
SELECT pa_budget_lines.*, rowid
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assigment_id
AND start_date = p_budget_start_date;
SELECT burdened_cost_flag
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code
AND trunc(sysdate)
BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
l_api_name CONSTANT VARCHAR2(30) := 'update_budget_line_sql';
l_update_yes_flag VARCHAR2(1) := 'N';
SAVEPOINT update_budget_line_sql_pvt;
l_statement := ' UPDATE PA_BUDGET_LINES SET ';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
- Adding txn_currency_code in update for more clarity to indicate the update will
always update just one record. We get the budget_line_id of the updated record
and pass to mrc api */
BEGIN
SELECT projfunc_currency_code
INTO l_txn_currency_code
FROM pa_projects_all a, pa_budget_versions b, pa_resource_Assignments c
WHERE a.project_id = b.project_id
AND b.budget_version_id = c.budget_version_id
AND c.resource_assignment_id = p_resource_assignment_id
AND b.ci_id IS NULL; -- -- Added an extra clause b.ci_id IS NULL --Bug # 3507156
IF l_update_yes_flag = 'Y'
THEN
l_statement := l_statement ||
' LAST_UPDATE_DATE = :xLastUpdateDate'||',';
' LAST_UPDATED_BY = '||G_USER_ID||',';
' LAST_UPDATE_LOGIN = '||G_LOGIN_ID||',';
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':xLastUpdateDate', SYSDATE);
SELECT budget_line_id, budget_version_id --Bug 4224464
INTO l_budget_line_id, l_budget_version_id
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND start_date = p_start_date
AND txn_currency_code = l_txn_currency_code;
l_budget_line_id := null; /* No budget line was updated */
/* FPB2: Proceed with MRC only if a budget line was update */
--Bug 4224464: changed IF condition to determine whether update has happened or not
/*
IF nvl(l_rows,0) > 0 THEN
IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
PA_MRC_FINPLAN.CHECK_MRC_INSTALL
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_BUDGET_LINES_V_PKG.delete_row
( x_rowid => l_budget_line_rec.rowid );
IF nvl(l_rows,0) > 0 THEN--Calling MRC APIs only if a budget line was updated
IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
PA_MRC_FINPLAN.CHECK_MRC_INSTALL
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_action => PA_MRC_FINPLAN.G_ACTION_DELETE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
, p_procedure_name => 'DELETE_ROW'
, p_error_text => SQLCODE );
END IF;--l_update_yes_flag = 'Y'
ROLLBACK TO update_budget_line_sql_pvt;
ROLLBACK TO update_budget_line_sql_pvt;
ROLLBACK TO update_budget_line_sql_pvt;
END update_budget_line_sql;
,p_delete_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type()
,p_mfc_cost_type_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
,p_spread_curve_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
,p_sp_fixed_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE()
,p_etc_method_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE := SYSTEM.PA_VARCHAR2_80_TBL_TYPE()
,p_spread_curve_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
,p_amount_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
/* Bug 3986129: end*/
,px_budget_lines_in IN OUT NOCOPY PA_BUDGET_PUB.G_BUDGET_LINES_IN_TBL%TYPE --File.Sql.39 bug 4440895
/* Bug 3133930- a new output variable is introduced to return the error status */
,x_budget_lines_out OUT NOCOPY PA_BUDGET_PUB.G_BUDGET_LINES_OUT_TBL%TYPE --File.Sql.39 bug 4440895
/* Bug 3986129: FP.M Web ADI Dev changes: New parameters added */
,x_mfc_cost_type_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
,x_etc_method_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type --File.Sql.39 bug 4440895
,x_spread_curve_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --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
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
--Declare a pl/sql table for storing the txn currencies of the plan type
--l_valid_txn_currencies_tbl pa_fp_webadi_pkg.l_txn_currency_code_tbl_typ; Bug 2871603
SELECT txn_currency_code
FROM pa_fp_txn_currencies ptxn
,pa_projects_all p
WHERE p.project_id=c_project_id
AND ptxn.project_id = p.project_id
AND ptxn.txn_currency_code NOT IN (p.project_currency_code, p.projfunc_currency_code)
AND ptxn.proj_fp_options_id = c_proj_fp_options_id; --made changes to the sql for bug 4886319 (performance)
SELECT txn_currency_code
FROM pa_fp_txn_currencies ptxn
,pa_projects_all p
WHERE p.project_id = c_project_id
AND ptxn.project_id = p.project_id
AND ptxn.fin_plan_type_id = c_fin_plan_type_id
AND ptxn.txn_currency_code NOT IN (p.project_currency_code, p.projfunc_currency_code)
AND ptxn.fin_plan_version_id = c_fin_plan_version_id;
SELECT pr.unit_of_measure
,prlm.track_as_labor_flag
,prlm.migration_code
FROM pa_resources pr
,pa_resource_lists prl
,pa_resource_list_members prlm
WHERE prl.resource_list_id = c_resource_list_id
AND pr.resource_id = prlm.resource_id
AND prl.resource_list_id = prlm.resource_list_id
AND prlm.resource_list_member_id = c_resource_list_member_id;
SELECT prlm.unit_of_measure,
prlm.migration_code
FROM pa_resource_list_members prlm
WHERE prlm.resource_list_member_id = c_resource_list_member_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = c_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = c_pa_task_id;
SELECT approved_rev_plan_type_flag,
proj_fp_options_id
FROM pa_proj_fp_options
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND fin_plan_option_level_code=PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT pt.plan_class_code
,bv.etc_start_date
FROM pa_budget_versions bv,
pa_fin_plan_types_b pt
WHERE bv.budget_version_id = c_budget_version_id
AND pt.fin_plan_type_id = bv.fin_plan_type_id;
Select 'X'
from pa_lookups
where lookup_type='PM_PRODUCT_CODE'
and lookup_code = c_pm_product_code;
SELECT pbv.ci_id,
agr.agreement_currency_code
FROM pa_budget_versions pbv,
pa_agreements_all agr
WHERE pbv.budget_version_id = c_budget_version_id
AND pbv.agreement_id = agr.agreement_id;
IF ( p_calling_context in('RES_ASSGNMT_LEVEL_VALIDATION','UPDATE_PLANNING_ELEMENT_ATTR') AND p_budget_type_code IS NOT NULL )
THEN
PA_UTILS.ADD_MESSAGE
(p_app_short_name => 'PA',
p_msg_name => 'PA_FP_INV_PARAM_PASSED');
px_budget_lines_in.COUNT <> p_delete_flag_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_mfc_cost_type_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_spread_curve_name_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_sp_fixed_date_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_etc_method_name_tbl.COUNT THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'For Web ADI context the input tables are not equal';
IF p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' THEN
IF px_budget_lines_in.COUNT <> p_uom_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_planning_start_date_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_planning_end_date_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_mfc_cost_type_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_spread_curve_name_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_sp_fixed_date_tbl.COUNT OR
px_budget_lines_in.COUNT <> p_etc_method_name_tbl.COUNT THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'For UPDATE_PLANNING_ELEMENT_ATTR context the input tables are not equal';
Select spread_curve_id
into l_fixed_date_sp_id
from pa_spread_curves_b
where spread_curve_code = 'FIXED_DATE';
select segment1
into l_project_number
from pa_projects_all
where project_id=p_pa_project_id;
SELECT name
INTO l_fin_plan_type_name
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = p_fin_plan_type_id;
IF ( p_calling_context not in ('RES_ASSGNMT_LEVEL_VALIDATION','UPDATE_PLANNING_ELEMENT_ATTR') )
THEN
IF p_calling_context = 'WEBADI' THEN -- Bug 3986129: FP.M Web ADI Dev changes
-- checking if the planning start date/end date has been explcitely nulled ou
IF p_planning_start_date_tbl(i) = FND_API.G_MISS_DATE OR
p_planning_end_date_tbl(i) = FND_API.G_MISS_DATE THEN
l_webadi_err_code_tbl.extend(1);
END IF; --p_calling_context <> 'RES_ASSGNMT_LEVEL_VALIDATION','UPDATE_PLANNING_ELEMENT_ATTR'
IF p_calling_context <> 'UPDATE_PLANNING_ELEMENT_ATTR' then -- Bug 5509192
--every budget line need to be checked for it's amount values.
IF p_fin_plan_type_id IS NULL THEN
--Budget Model.Do not pass version type and amount flags
pa_budget_pvt.check_entry_method_flags
( p_budget_amount_code => l_budget_amount_code
,p_budget_entry_method_code => p_budget_entry_method_code
,p_quantity => px_budget_lines_in(i).quantity
,p_raw_cost => px_budget_lines_in(i).raw_cost
,p_burdened_cost => px_budget_lines_in(i).burdened_cost
,p_revenue => px_budget_lines_in(i).revenue
,p_return_status => x_return_status
-- Bug 3986129: FP.M Web ADI Dev changes
,x_webadi_error_code => l_new_error_code);
END IF; --p_clalling_context <> 'UPDATE_PLANNING_ELEMENT_ATTR' Bug 5509192
IF p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' then
l_valid_rlmid := 'Y';
SELECT parent_member_id
,resource_type_code
,alias
INTO l_parent_member_id
,l_resource_type_code
,pa_budget_pvt.g_resource_alias
FROM pa_resource_list_members
WHERE resource_list_member_id = px_budget_lines_in(i).resource_list_member_id;
IF p_calling_context= 'UPDATE_PLANNING_ELEMENT_ATTR' then
IF l_valid_rlmid ='Y' then
SELECT ALIAS
INTO px_budget_lines_in(i).resource_alias
FROM pa_resource_list_members
WHERE resource_list_member_id = px_budget_lines_in(i).resource_list_member_id;
(p_calling_context='UPDATE_PLANNING_ELEMENT_ATTR' and l_valid_rlmid ='Y') THEN --Bug 5509192
-- validating resource level attributes
IF p_uom_tbl(i) IS NOT NULL AND
p_uom_tbl(i) = FND_API.G_MISS_CHAR THEN
-- UOM has been nulled out
l_webadi_err_code_tbl.extend(1);
(p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' or
p_etc_method_name_tbl(i) <> FND_API.G_MISS_CHAR) THEN
BEGIN
SELECT lookup_code
INTO l_etc_method_code
FROM pa_lookups
WHERE lookup_type = 'PA_FP_ETC_METHOD'
AND meaning = p_etc_method_name_tbl(i);
IF p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' then
IF px_budget_lines_in(i).pa_task_id <> 0 then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'INVALID_ETC_METHOD_AMG',
p_token1 => 'PROJECT_OR_TASK_NUMBER',
p_value1 => l_amg_task_number,
p_token2 => 'RESOURCE',
p_value2 => px_budget_lines_in(i).resource_alias);
SELECT cost_type_id
INTO l_mfc_cost_type_id
FROM CST_COST_TYPES_V
WHERE multi_org_flag = 1
AND cost_type = p_mfc_cost_type_tbl(i);
p_calling_context <> 'UPDATE_PLANNING_ELEMENT_ATTR' THEN
l_webadi_err_code_tbl.extend(1);
(p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' or
p_spread_curve_name_tbl(i) <> FND_API.G_MISS_CHAR) THEN
BEGIN
SELECT spread_curve_id
INTO l_spread_curve_id
FROM pa_spread_curves_vl
WHERE name = p_spread_curve_name_tbl(i);
IF p_calling_context = 'UPDATE_PLANNING_ELEMENT_ATTR' then
IF px_budget_lines_in(i).pa_task_id <> 0 then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'INVALID_SPREAD_CURVE_AMG',
p_token1 => 'PROJECT_OR_TASK_NUMBER',
p_value1 => l_amg_task_number,
p_token2 => 'RESOURCE',
p_value2 => px_budget_lines_in(i).resource_alias);
if p_calling_context ='UPDATE_PLANNING_ELEMENT_ATTR' then
l_invalid_resassgn_flag :='N';
SELECT resource_assignment_id,planning_start_date,
planning_end_date,nvl(l_spread_curve_id,spread_curve_id),sp_fixed_date
INTO l_resource_assignment_id,l_planning_start_date,
l_planning_end_date,l_spread_curve_id,l_sp_fixed_date
FROM pa_resource_assignments
WHERE budget_version_id=p_version_info_rec.x_budget_version_id
AND task_id=px_budget_lines_in(i).pa_task_id
AND resource_list_member_id=px_budget_lines_in(i).resource_list_member_id
AND project_id=p_pa_project_id
AND PROJECT_ASSIGNMENT_ID =-1;
end if; -- end of newly introduced checks for UPDATE_PLANNING_ELEMENT_ATTR
END IF; -- Bug 3986129: FP.M Web ADI ,UPDATE_PLANNING_ELEMENT_ATTR
IF ( p_calling_context NOT IN( 'RES_ASSGNMT_LEVEL_VALIDATION','WEBADI','UPDATE_PLANNING_ELEMENT_ATTR') OR --Bug 5509192
(p_calling_context = 'WEBADI' AND ((NOT (p_delete_flag_tbl.exists(i))) OR
Nvl(p_delete_flag_tbl(i), 'N') <> 'Y')))
THEN
--Validate the change reason code. This validation is added for Fin plan model in FP L
IF (px_budget_lines_in(i).change_reason_code IS NOT NULL AND
((p_calling_context = 'WEBADI' AND px_budget_lines_in(i).change_reason_code <> FND_API.G_MISS_CHAR) OR
(p_calling_context <> 'WEBADI' AND px_budget_lines_in(i).change_reason_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))) THEN
OPEN l_budget_change_reason_csr( px_budget_lines_in(i).change_reason_code );
END IF; --p_calling_context <> 'RES_ASSGNMT_LEVEL_VALIDATION','UPDATE_PLANNING_ELEMENT_ATTR'
IF p_fin_plan_type_id IS NOT NULL and p_calling_context <> 'UPDATE_PLANNING_ELEMENT_ATTR' THEN -- Bug 5509192
--Bug#4457546:Added code to throw an error when amounts are entered for a period which
--does not fall within the planning start/end dates of resource assignment
--This API validate_budget_lines is being called from the following API's
--CREATE_DRAFT_BUDGET (PACKAGE PA_BUDGET_PUB)
--ADD_BUDGET_LINE (PACKAGE PA_BUDGET_PUB)
--UPDATE_BUDGET (PACKAGE PA_BUDGET_PUB)
--UPDATE_BUDGET_LINE (PACKAGE PA_BUDGET_PUB)
--CREATE_DRAFT_FINPLAN (PACKAGE PA_BUDGET_PUB)
--INSERT_BUDGET_LINE (PACKAGE PA_BUDGET_PVT)
-- Of all the above places the below if condition would only be satisfied by ADD_BUDGET_LINE, UPDATE_BUDGET,
--UPDATE_BUDGET_LINE for finplan model only which is what is required here. This validation should only be done
--only from these calling places and only for finplan model.
IF ( p_calling_context = 'BUDGET_LINE_LEVEL_VALIDATION' and p_version_info_rec.x_budget_version_id is not null)
THEN
--Prepare the index in this form for each budget line.
l_distinct_taskid_rlmid_index := 'T'||px_budget_lines_in(i).pa_task_id||'R'||px_budget_lines_in(i).resource_list_member_id;
SELECT pra.planning_start_date,
pra.planning_end_date,
prlm.alias
INTO l_plan_start_date,
l_plan_end_date,
l_resource_alias
FROM pa_resource_assignments pra,
pa_resource_list_members prlm
WHERE pra.budget_version_id = p_version_info_rec.x_budget_version_id
AND pra.resource_list_member_id = px_budget_lines_in(i).resource_list_member_id
AND pra.task_id = px_budget_lines_in(i).pa_task_id
AND prlm.resource_list_member_id = pra.resource_list_member_id;
IF (p_delete_flag_tbl.exists(i) AND
Nvl(p_delete_flag_tbl(i), 'N') = 'Y')THEN
l_webadi_cont_proc_flag := 'N';
SELECT ra.task_id,
ra.resource_list_member_id,
bl.txn_currency_code,
bl.start_date,
DECODE(bl.cost_rejection_code,NULL,
DECODE(bl.revenue_rejection_code,NULL,
DECODE(bl.burden_rejection_code,NULL,
DECODE(bl.other_rejection_code,NULL,
DECODE(bl.pfc_cur_conv_rejection_code,NULL,
DECODE(bl.pc_cur_conv_rejection_code,NULL,NULL,'E')
,'E'),'E'),'E'),'E'),'E') return_status
FROM pa_resource_assignments ra , pa_budget_lines bl
where ra.budget_version_id = p_fin_plan_version_id
and ra.resource_assignment_id = bl.resource_assignment_id;
SELECT nvl(cost_time_phased_code,NVL(revenue_time_phased_code,all_time_phased_code)), -- Added for BUG 6847497
prl.uncategorized_flag
INTO l_time_phased_code, l_uncategorized_flag
FROM pa_proj_fp_options , pa_resource_lists_all_bg prl
WHERE fin_plan_version_id=p_fin_plan_version_id
AND nvl(cost_resource_list_id,nvl(revenue_resource_list_id,all_resource_list_id))=
prl.resource_list_id;
SELECT 'Y'
FROM pa_bc_balances
WHERE budget_version_id = p_budget_version_id;
SELECT l.Latest_Encumbrance_Year
INTO x_latest_encumbrance_year
FROM GL_ledgers l
, pa_implementations_all i
, pa_projects_all p
WHERE l.LEDGER_ID = i.set_of_books_id
AND i.org_id = p.org_id
AND p.project_id = p_pa_project_id;