[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_id
FROM pa_projects
WHERE segment1 BETWEEN c_from_project_number AND c_to_project_number
AND NVL(c_project_type,project_type) = project_type
AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
SELECT project_id
FROM pa_projects
WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
AND c_project_type = project_type
AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
SELECT project_id
FROM pa_projects
WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
AND NVL(c_project_type,project_type) = project_type
AND project_status_code <> 'CLOSED'; --Bug 5194368
SELECT project_id
FROM pa_projects
WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
AND NVL(c_project_type,project_type) = project_type
AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
SELECT allow_cost_budget_entry_flag
,allow_rev_budget_entry_flag
,name
,segment1
,org_project_flag -- bug 2788983
FROM pa_project_types ppt
,pa_projects pp
WHERE pp.project_id = c_project_id
AND ppt.project_type = pp.project_type;
SELECT pt.fin_plan_type_id fin_plan_type_id
,bt.budget_Type_code budget_Type_code
FROM pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
,pa_budget_types bt
WHERE DECODE(c_budget_types,'ALL','Y', bt.upgrade_budget_type_flag) = 'Y'
AND bt.budget_type_code = pt.migrated_frm_bdgt_typ_code
AND NVL(bt.plan_type,'BUDGET') = 'BUDGET'
AND not exists
(SELECT 1
FROM pa_proj_fp_options ppfo
WHERE ppfo.project_id = c_project_id
AND ppfo.fin_plan_type_id = pt.fin_plan_type_id
AND ppfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE)
AND exists
(SELECT 1
FROM pa_budget_versions pbv
WHERE pbv.project_id = c_project_id
AND pbv.budget_type_code = bt.budget_type_code);
SELECT budget_version_id
, bt.budget_type_code
, bv.resource_list_id /* bug 3673111, 07-JUN-4, jwhite: New Column */
, bv.budget_status_code -- Bug# 7187487
FROM pa_budget_versions bv,
pa_budget_types bt
WHERE bv.project_id = c_project_id
AND bt.budget_type_code = bv.budget_type_code
AND bv.budget_type_code IS NOT NULL
AND DECODE(c_budget_types,'ALL','Y',bt.upgrade_budget_type_flag) = 'Y'
AND NVL(bt.plan_type,'BUDGET') = 'BUDGET' /* Bug 2758786 */
AND EXISTS (
SELECT 1 FROM DUAL
WHERE c_budget_statuses = 'ALL'
UNION ALL
SELECT 1 FROM DUAL
WHERE (current_original_flag = 'Y' OR
original_flag = 'Y' OR
current_flag = 'Y' OR
budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING) )
AND c_budget_statuses = 'CWB')
AND (c_mode = 'PRE_UPGRADE' OR EXISTS (
SELECT 1
FROM pa_proj_fp_options pfo,
pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
WHERE pfo.project_id = c_project_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND pt.fin_plan_type_id = pfo.fin_plan_type_id
AND pt.migrated_frm_bdgt_typ_code = bv.budget_type_code));
SELECT pbv.budget_entry_method_code budget_entry_method_code
,resource_list_id
,entry_level_code
,time_phased_type_code
,cost_quantity_flag
,raw_cost_flag
,burdened_cost_flag
,rev_quantity_flag
,revenue_flag
FROM pa_budget_versions pbv,
pa_budget_entry_methods pbem
WHERE pbv.budget_version_id = c_budget_version_id
AND pbem.budget_entry_method_code = pbv.budget_entry_method_code;
SELECT cost_budget_entry_method_code
,cost_budget_resource_list_id
,rev_budget_entry_method_code
,rev_budget_resource_list_id
,allow_cost_budget_entry_flag
,allow_rev_budget_entry_flag
FROM pa_projects a,
pa_project_types b
WHERE a.project_id = c_project_id
AND b.project_type = a.project_type;
SELECT entry_level_code
,time_phased_type_code
,cost_quantity_flag
,raw_cost_flag
,burdened_cost_flag
,rev_quantity_flag
,revenue_flag
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = c_budget_entry_method_code;
SELECT fin_plan_type_id
INTO x_upgrade_elements_rec.curr_option_plan_type_id
FROM pa_fin_plan_types_b /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
WHERE migrated_frm_bdgt_typ_code = p_budget_type_code;
SELECT DECODE(budget_amount_code,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_C,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_R,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY)
INTO x_upgrade_elements_rec.curr_option_preference_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT fin_plan_type_id
INTO x_upgrade_elements_rec.curr_option_plan_type_id
FROM pa_fin_plan_types_b /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
WHERE migrated_frm_bdgt_typ_code = p_budget_type_code;
SELECT DECODE(budget_amount_code,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_C,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_R,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY)
INTO x_upgrade_elements_rec.curr_option_preference_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT proj_fp_options_id
INTO l_proj_fp_options_id
FROM pa_proj_fp_options
WHERE project_id = l_project_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
SELECT fin_plan_preference_code, plan_in_multi_curr_flag
INTO l_fp_preference_code, l_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id;
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => l_project_id
,p_budget_type_code => NULL
,p_proj_fp_options_id => l_proj_fp_options_id
,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
,p_basis_cost_version_id => l_upgrade_elements_rec.basis_cost_version_id
,p_basis_rev_version_id => l_upgrade_elements_rec.basis_rev_version_id
,p_basis_cost_bem => l_upgrade_elements_rec.basis_cost_bem
,p_basis_rev_bem => l_upgrade_elements_rec.basis_rev_bem
,p_upgraded_flag => 'Y'
,p_failure_reason_code => NULL);
selected for upgrade. IF plan type for a budget type already exists then this
api will skip such budget types. Users can submit the upgrade process either
for all budget types or only those which are selected on budget type from
=============================================================================*/
PROCEDURE Upgrade_Budget_Types(
p_budget_types IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(2000);
SELECT budget_type_code
,budget_type
,description
,enable_wf_flag
,start_date_active
,end_date_active
,predefined_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
FROM pa_budget_types bt
WHERE DECODE(c_budget_types, 'ALL' ,'Y', upgrade_budget_type_flag) = 'Y'
AND not exists
(SELECT 1
FROM pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
WHERE pt.migrated_frm_bdgt_typ_code = bt.budget_type_code);
PA_FIN_PLAN_TYPES_PKG.Insert_Row (
x_rowid => l_rowid
,x_fin_plan_type_id => NULL
,x_fin_plan_type_code => NULL
,x_pre_defined_flag => budget_types_for_upgrade_rec.predefined_flag
,x_generated_flag => 'N'
,x_edit_generated_amt_flag => 'N'
,x_used_in_billing_flag => 'N'
,x_enable_wf_flag => NVL(budget_types_for_upgrade_rec.enable_wf_flag,'N')
,x_start_date_active => budget_types_for_upgrade_rec.start_date_active
,x_end_date_active => budget_types_for_upgrade_rec.end_date_active
,x_record_version_number => 1
,x_name => budget_types_for_upgrade_rec.budget_type
,x_description => budget_types_for_upgrade_rec.description
,x_plan_class_code => l_plan_class_code
,x_approved_cost_plan_type_flag => l_approved_cost_plan_type_flag
,x_approved_rev_plan_type_flag => l_approved_rev_plan_type_flag
,x_projfunc_cost_rate_type => NULL
,x_projfunc_cost_rate_date_type => NULL
,x_projfunc_cost_rate_date => NULL
,x_projfunc_rev_rate_type => NULL
,x_projfunc_rev_rate_date_type => NULL
,x_projfunc_rev_rate_date => NULL
,x_project_cost_rate_type => NULL
,x_project_cost_rate_date_type => NULL
,x_project_cost_rate_date => NULL
,x_project_rev_rate_type => NULL
,x_project_rev_rate_date_type => NULL
,x_project_rev_rate_date => NULL
,x_attribute_category => budget_types_for_upgrade_rec.attribute_category
,x_attribute1 => budget_types_for_upgrade_rec.attribute1
,x_attribute2 => budget_types_for_upgrade_rec.attribute2
,x_attribute3 => budget_types_for_upgrade_rec.attribute3
,x_attribute4 => budget_types_for_upgrade_rec.attribute4
,x_attribute5 => budget_types_for_upgrade_rec.attribute5
,x_attribute6 => budget_types_for_upgrade_rec.attribute6
,x_attribute7 => budget_types_for_upgrade_rec.attribute7
,x_attribute8 => budget_types_for_upgrade_rec.attribute8
,x_attribute9 => budget_types_for_upgrade_rec.attribute9
,x_attribute10 => budget_types_for_upgrade_rec.attribute10
,x_attribute11 => budget_types_for_upgrade_rec.attribute11
,x_attribute12 => budget_types_for_upgrade_rec.attribute12
,x_attribute13 => budget_types_for_upgrade_rec.attribute13
,x_attribute14 => budget_types_for_upgrade_rec.attribute14
,x_attribute15 => budget_types_for_upgrade_rec.attribute15
,x_creation_date => sysdate
,x_created_by => fnd_global.user_id
,x_last_update_date => sysdate
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_migrated_frm_bdgt_typ_code => budget_types_for_upgrade_rec.budget_type_code
,X_ENABLE_PARTIAL_IMPL_FLAG => 'N'
,X_PRIMARY_COST_FORECAST_FLAG => 'N'
,X_PRIMARY_REV_FORECAST_FLAG => 'N'
,X_EDIT_AFTER_BASELINE_FLAG => 'Y'
,X_USE_FOR_WORKPLAN_FLAG => 'N');
UPDATE PA_PROJ_FP_OPTIONS
SET cost_fin_plan_level_code = l_upgrade_elements_rec.basis_cost_planning_level
,cost_time_phased_code = l_upgrade_elements_rec.basis_cost_time_phased_code
,cost_resource_list_id = l_upgrade_elements_rec.basis_cost_res_list_id
,cost_amount_set_id = l_upgrade_elements_rec.basis_cost_amount_set_id
,approved_cost_plan_type_flag = l_approved_cost_plan_type_flag
,approved_rev_plan_type_flag = l_approved_rev_plan_type_flag
--Bug 4174907
,primary_cost_forecast_flag = 'N'
,primary_rev_forecast_flag = 'N'
WHERE proj_fp_options_id = l_target_proj_fp_option_id;
UPDATE PA_PROJ_FP_OPTIONS
SET revenue_fin_plan_level_code = l_upgrade_elements_rec.basis_rev_planning_level
,revenue_time_phased_code = l_upgrade_elements_rec.basis_rev_time_phased_code
,revenue_resource_list_id = l_upgrade_elements_rec.basis_rev_res_list_id
,revenue_amount_set_id = l_upgrade_elements_rec.basis_rev_amount_Set_id
,approved_cost_plan_type_flag = l_approved_cost_plan_type_flag
,approved_rev_plan_type_flag = l_approved_rev_plan_type_flag
--Bug 4174907
,primary_cost_forecast_flag = 'N'
,primary_rev_forecast_flag = 'N'
WHERE proj_fp_options_id = l_target_proj_fp_option_id;
UPDATE PA_PROJ_FP_OPTIONS
SET projfunc_cost_rate_type = l_projfunc_cost_rate_type
,projfunc_cost_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
,projfunc_rev_rate_type = l_projfunc_bil_rate_type
,projfunc_rev_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
,project_cost_rate_type = l_project_cost_rate_type
,project_cost_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
,project_rev_rate_type = l_project_bil_rate_type
,project_rev_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
WHERE proj_fp_options_id = l_target_proj_fp_option_id;
SELECT fin_plan_preference_code, plan_in_multi_curr_flag
INTO l_fp_preference_code, l_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id;
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => p_project_id
,p_budget_type_code => attached_plan_types_rec.budget_type_code
,p_proj_fp_options_id => l_proj_fp_options_id
,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
,p_basis_cost_version_id => l_upgrade_elements_rec.basis_cost_version_id
,p_basis_rev_version_id => l_upgrade_elements_rec.basis_rev_version_id
,p_basis_cost_bem => l_upgrade_elements_rec.basis_cost_bem
,p_basis_rev_bem => l_upgrade_elements_rec.basis_rev_bem
,p_upgraded_flag => 'Y'
,p_failure_reason_code => NULL);
3.Update Budget Version in PA_BUDGET_VERSIONS
4.Create resource assignments
5.Roll up resource assignments
6.Create period denorm records for the budget.
-- 07-JUN-04 jwhite Bug 3673111
-- When I closely reviewed this package for
-- FP.M resource list and RBS modifications,
-- I found so many issues that I decided to do
-- following:
-- 1) Move most of the calls to this
-- private Upgrade_Budget_Versions api.
-- 2) Change the FP.M Uprade api calls to process one
-- budget_version_id at a time per the budget_version
-- cursor in this procedure.
-- 12-Dec-06 nkumbi Bug 5676682 :Same local variables cannot be passed as both
-- IN and OUT variables to an api. Fixed the issue
-- in upgrade_budget_versions api while calling
-- apply_calculate_fpm_rules.
=============================================================================*/
PROCEDURE Upgrade_Budget_Versions (
p_project_id IN pa_projects.project_id%TYPE
,p_budget_types IN VARCHAR2
,p_budget_statuses IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(2000);
SELECT fin_plan_preference_code, plan_in_multi_curr_flag
INTO l_fp_preference_code, l_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id;
UPDATE PA_BUDGET_VERSIONS
SET budget_type_code = NULL,
version_name = nvl(version_name,to_char(version_number)),-- Added for Bug 6722317
fin_plan_type_id = l_upgrade_elements_rec.curr_option_plan_type_id,
version_type = l_version_type,
approved_cost_plan_type_flag = l_approved_cost_plan_type_flag,
approved_rev_plan_type_flag = l_approved_rev_plan_type_flag,
record_version_number = NVl(record_version_number,0) + 1, -- null handling ,bug 2788983
first_budget_period = NULL,
request_id = FND_GLOBAL.conc_request_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
current_working_flag = DECODE(budget_status_code,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,'Y',NULL)
--Bug 4174907
,primary_cost_forecast_flag = 'N'
,primary_rev_forecast_flag = 'N'
WHERE budget_version_id = l_budget_ver_tbl(j);
UPDATE PA_RESOURCE_ASSIGNMENTS
SET resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
WHERE budget_version_id = l_budget_ver_tbl(j);
UPDATE PA_BUDGET_LINES
SET txn_raw_cost = raw_cost,
txn_burdened_cost = burdened_cost,
txn_revenue = revenue
WHERE budget_version_id = l_budget_ver_tbl(j);
SELECT migration_code
INTO l_migration_code
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_res_list_tbl(j);
DELETE
FROM PA_BUDGET_LINES BL
WHERE bl.budget_version_id = l_budget_ver_tbl(m)
AND NVL(bl.quantity,0) = 0
AND NVL(bl.txn_raw_cost,0) = 0
AND NVL(bl.txn_burdened_cost,0) = 0
AND NVL(bl.txn_revenue,0) = 0 ;
SELECT bl.budget_line_id
,bl.resource_assignment_id
,nvl(bl.quantity,0)
,nvl(bl.txn_raw_cost,0)
,nvl(bl.txn_burdened_cost,0)
,nvl(bl.txn_revenue,0)
,nvl(ra.rate_based_flag,'N') rate_based_flag
BULK COLLECT INTO
l_upg_bl_id_tbl
,l_upg_ra_id_tbl
,l_upg_quantity_tbl
,l_upg_txn_raw_cost_tbl
,l_upg_txn_burdened_cost_tbl
,l_upg_txn_revenue_tbl
,l_upg_rate_based_flag_tbl
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE bl.resource_assignment_id=ra.resource_assignment_id
AND bl.budget_version_id=l_budget_ver_tbl(m)
ORDER BY bl.resource_assignment_id ,bl.quantity NULLS FIRST;
SELECT fin_plan_preference_code
INTO l_pref_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id=l_budget_ver_tbl(m);
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.rate_based_flag = 'N'
,ra.unit_of_measure = 'DOLLARS'
WHERE ra.resource_assignment_id = l_upg_non_rb_ra_id_tbl(j);
UPDATE PA_BUDGET_LINES bl
SET bl.quantity = l_upg_quantity_tbl(j)
,bl.txn_raw_cost = l_upg_txn_raw_cost_tbl(j)
,bl.txn_cost_rate_override = l_upg_raw_cost_rate_tbl(j)
,bl.txn_standard_cost_rate = l_upg_raw_cost_rate_tbl(j)
,bl.txn_burdened_cost = l_upg_txn_burdened_cost_tbl(j)
,bl.burden_cost_rate_override = l_upg_burd_cost_rate_tbl(j)
,bl.burden_cost_rate = l_upg_burd_cost_rate_tbl(j)
,bl.txn_revenue = l_upg_txn_revenue_tbl(j)
,bl.txn_bill_rate_override = l_upg_bill_rate_tbl(j)
,bl.txn_standard_bill_rate = l_upg_bill_rate_tbl(j)
WHERE bl.budget_line_id = l_upg_bl_id_tbl(j);
* i. update the display_quantity new column in pa_budget_lines
* ii. insert planning transaction records in the new entity pa_resource_asgn_curr
* with the appropriate records.
*/
PA_BUDGET_LINES_UTILS.populate_display_qty
(p_budget_version_id => l_budget_ver_tbl(m),
p_context => 'FINANCIAL',
x_return_status => l_return_status);
/* calling the maintenance api to insert data into the new planning transaction level table */
PA_RES_ASG_CURRENCY_PUB.maintain_data
(p_fp_cols_rec => l_fp_cols_rec_var,
p_calling_module => 'UPGRADE',
p_rollup_flag => 'Y',
p_version_level_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
* to insert those resource assignment with default applicable currency
*/
PA_FIN_PLAN_PUB.create_default_plan_txn_rec
(p_budget_version_id => l_budget_ver_tbl(m),
p_calling_module => 'UPGRADE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PROCEDURE Insert_Audit_Record(
p_project_id IN PA_FP_UPGRADE_AUDIT.PROJECT_ID%TYPE
,p_budget_type_code IN PA_FP_UPGRADE_AUDIT.BUDGET_TYPE_CODE%TYPE
,p_proj_fp_options_id IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE
,p_fin_plan_option_level_code IN PA_FP_UPGRADE_AUDIT.FIN_PLAN_OPTION_LEVEL_CODE%TYPE
,p_basis_cost_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_COST_VERSION_ID%TYPE
,p_basis_rev_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_REV_VERSION_ID%TYPE
,p_basis_cost_bem IN PA_FP_UPGRADE_AUDIT.BASIS_COST_BEM%TYPE
,p_basis_rev_bem IN PA_FP_UPGRADE_AUDIT.BASIS_REV_BEM%TYPE
,p_upgraded_flag IN PA_FP_UPGRADE_AUDIT.UPGRADED_FLAG%TYPE
,p_failure_reason_code IN PA_FP_UPGRADE_AUDIT.FAILURE_REASON_CODE%TYPE
,p_proj_fp_options_id_rup IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE DEFAULT NULL) IS
BEGIN
INSERT INTO PA_FP_UPGRADE_AUDIT (
PROJECT_ID
,BUDGET_TYPE_CODE
,PROJ_FP_OPTIONS_ID
,FIN_PLAN_OPTION_LEVEL_CODE
,BASIS_COST_VERSION_ID
,BASIS_REV_VERSION_ID
,BASIS_COST_BEM
,BASIS_REV_BEM
,REQUEST_ID
,UPGRADED_FLAG
,FAILURE_REASON_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,proj_fp_options_id_rup )
VALUES(
p_project_id
,p_budget_type_code
,p_proj_fp_options_id
,p_fin_plan_option_level_code
,p_basis_cost_version_id
,p_basis_rev_version_id
,p_basis_cost_bem
,p_basis_rev_bem
,fnd_global.conc_request_id
,p_upgraded_flag
,p_failure_reason_code
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,p_proj_fp_options_id_rup);
END Insert_Audit_Record;
upgrade budgets report. The api would insert the exception records
into pa_fp_upgrade_audit table and pa_fp_upgrade_exceptions_tmp as
necessary.
==================================================================*/
PROCEDURE VALIDATE_BUDGETS (
p_from_project_number IN VARCHAR2
,p_to_project_number IN VARCHAR2
,p_budget_types IN VARCHAR2
,p_budget_statuses IN VARCHAR2
,p_project_type IN VARCHAR2
,p_project_statuses IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --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
AS
l_msg_count NUMBER := 0;
SELECT bt.budget_type_code budget_type_code
FROM pa_budget_types bt
WHERE DECODE(c_budget_types,'ALL','Y', bt.upgrade_budget_type_flag) = 'Y'
AND NVL(bt.plan_type,'BUDGET') = 'BUDGET'
AND NOT EXISTS
(SELECT 1
FROM pa_proj_fp_options ppfo
,pa_fin_plan_types_b pt
WHERE pt.migrated_frm_bdgt_typ_code = bt.budget_type_code
AND ppfo.project_id = c_project_id
AND ppfo.fin_plan_type_id = pt.fin_plan_type_id
AND ppfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE)
AND EXISTS
(SELECT 1
FROM pa_budget_versions pbv
WHERE pbv.project_id = c_project_id
AND pbv.budget_type_code = bt.budget_type_code);
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => p_project_id
,p_budget_type_code => NULL
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'N'
,p_failure_reason_code => 'NO_CONV_ATTR_FOR_PROJ');
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => p_project_id
,p_budget_type_code => p_budget_type_code
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'N'
,p_failure_reason_code => 'BUDGET_INTEGRATION_EXISTS');
SELECT budget_status_code
INTO l_budget_status_code
FROM pa_budget_versions
WHERE budget_version_id = l_draft_version_id;
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => p_project_id
,p_budget_type_code => p_budget_type_code
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'N'
,p_failure_reason_code => 'SUBMIT_STATUS_VERSION_EXISTS');
SELECT project_id,
budget_type_code,
resource_list_id
FROM pa_budget_versions
WHERE budget_Version_id = c_budget_version_id;
SELECT prj.project_id,
bv.budget_version_id,
bv.ci_id,
op.proj_fp_options_id,
ci.status_code
FROM pa_projects prj,
pa_budget_versions bv,
pa_fin_plan_types_b fp,
pa_control_items ci,
pa_proj_fp_options op
WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
AND DECODE(c_project_statuses,'ALL','ACTIVE',prj.project_status_code) = 'ACTIVE'
AND bv.project_id = prj.project_id
and bv.fin_plan_type_id = fp.fin_plan_type_id
and bv.budget_version_id = op.fin_plan_version_id
and op.project_id = bv.project_id
and nvl(c_fin_plan_type_id,fp.fin_plan_type_id) = fp.fin_plan_type_id
and nvl(fp.FIN_PLAN_TYPE_CODE,'x') <> 'ORG_FORECAST'
and bv.budget_status_code = 'W'
and bv.ci_id = ci.ci_id(+)
and NVL(pa_project_structure_utils.check_struc_ver_published(bv.project_id,bv.project_structure_version_id),'N') = 'N'
and NOT EXISTS (SELECT 1 FROM pa_fp_upgrade_audit aud
WHERE aud.project_id = op.project_id
AND aud.proj_fp_options_id_rup = op.PROJ_FP_OPTIONS_ID
AND aud.upgraded_flag = 'Y')
and EXISTS (SELECT 1 FROM pa_budget_lines bl
WHERE bl.budget_version_id = bv.budget_version_id
AND ( bl.cost_rejection_code IS NOT NULL
OR bl.revenue_rejection_code IS NOT NULL
OR bl.burden_rejection_code IS NOT NULL
OR bl.pfc_cur_conv_rejection_code IS NOT NULL
OR bl.pc_cur_conv_rejection_code IS NOT NULL
)
)
and bv.prc_generated_flag = 'M'; --IPM Optional Upgrade Process
/* Check if ci is in updateable status - following code got from ci team */
IF l_ci_id IS NOT NULL THEN
begin
select 'Y'
into l_process_flag
from pa_project_statuses ps ,
pa_project_status_controls psc
where ps.project_Status_code = l_ci_status_code
and ps.project_system_status_code = nvl(psc.project_system_status_code,psc.project_Status_code)
and psc.status_type = 'CONTROL_ITEM'
and psc.action_code = 'CONTROL_ITEM_ALLOW_UPDATE'
and psc.enabled_flag = 'N'
and rownum < 2;
l_rtx_ra_id_tbl.delete;
l_ra_id_tbl.delete ;
pa_debug.g_err_stage := 'Successfully Deleted the pl/sql tables.';
SELECT bv.budget_version_id INTO l_budg_ver_id
FROM pa_budget_versions bv
WHERE bv.budget_version_id = l_bv_id
FOR UPDATE OF bv.budget_version_id NOWAIT;
SELECT rtx.resource_assignment_id BULK COLLECT INTO l_rtx_ra_id_tbl
FROM pa_resource_asgn_curr rtx
WHERE rtx.budget_version_id = l_bv_id
FOR UPDATE OF rtx.resource_assignment_id NOWAIT;
SELECT ra.resource_assignment_id BULK COLLECT INTO l_ra_id_tbl
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = l_bv_id
FOR UPDATE OF ra.resource_assignment_id NOWAIT;
/* Bug 5098818 - Start - Replaced exclusive update stmt with a call to maintain_data api */
/* populating fp_cols_rec to call the new entity maintenace API */
PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls
(p_budget_version_id => l_bv_id,
x_fp_cols_rec => l_fp_cols_rec_var,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* calling the maintenance api to insert data into the new planning transaction level table */
PA_RES_ASG_CURRENCY_PUB.maintain_data
(p_fp_cols_rec => l_fp_cols_rec_var,
p_calling_module => 'UPGRADE',
p_rollup_flag => 'Y',
p_version_level_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Bug 5098818 - End - Replaced exclusive update stmt with a call to maintain_data api */
IF p_pa_debug_mode = 'Y' THEN
pa_debug.g_err_stage := 'Updated the resource assign curr amts from budget lines';
/* Bug 5098818 - Start - Replaced exclusive update stmt with a call to already existing rollup_budget api */
PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION(
p_budget_version_id => l_bv_id
,p_entire_version => 'Y'
,p_context => NULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/* Bug 5098818 - End - Replaced exclusive update stmt with a call to already existing rollup_budget api */
IF p_pa_debug_mode = 'Y' THEN
pa_debug.g_err_stage := 'Updated the resource assignment amts from resource assign curr';
INSERT INTO pa_budget_lines_m_upg_dtrange(
LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,BUDGET_VERSION_ID_RUP
,RESOURCE_ASSIGNMENT_ID_RUP)
VALUES ( sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,l_bv_id
,l_ra_id_tbl(i));
pa_debug.g_err_stage := 'Before calling PJI API PLAN_DELETE for budget ver '||l_bv_id;
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_budget_ver_tbl,
x_return_status => x_return_status,
x_msg_code => l_error_msg_code);
pa_debug.g_err_stage := 'The rtn sts of PJI API PLAN_DELETE is '||x_return_status;
pa_debug.g_err_stage := 'The msg code of PJI API PLAN_DELETE is '||l_error_msg_code;
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => l_project_id
,p_budget_type_code => NULL
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => NULL
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'Y'
,p_failure_reason_code => NULL
,p_proj_fp_options_id_rup => l_op_id);
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => l_project_id
,p_budget_type_code => NULL
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => NULL
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'N'
,p_failure_reason_code => 'Record Locked'
,p_proj_fp_options_id_rup => l_op_id);
pa_fp_upgrade_pkg.Insert_Audit_Record(
p_project_id => l_project_id
,p_budget_type_code => NULL
,p_proj_fp_options_id => NULL
,p_fin_plan_option_level_code => NULL
,p_basis_cost_version_id => NULL
,p_basis_rev_version_id => NULL
,p_basis_cost_bem => NULL
,p_basis_rev_bem => NULL
,p_upgraded_flag => 'N'
,p_failure_reason_code => sqlcode
,p_proj_fp_options_id_rup => l_op_id);
l_rtx_ra_id_tbl.delete;
l_ra_id_tbl.delete ;
l_budget_ver_tbl.DELETE;