[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
into l_return_value
from pa_lookups
where lookup_type = p_lookup_type and
lookup_code = p_lookup_code;
select
record_version_number
into
l_record_version_number
from
pa_budget_versions
where
budget_version_id=p_unique_index;
SELECT migrated_frm_bdgt_typ_code
,approved_cost_plan_type_flag
,approved_rev_plan_type_flag
INTO l_migrated_frm_bdgt_typ_code
,l_approved_cost_plan_type_flag
,l_approved_rev_plan_type_flag
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id
AND nvl(use_for_workplan_flag,'N')='N'; -- Added for Changes for FP.M, Tracking Bug No - 3354518
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code=PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC
AND rownum = 1;
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code=PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR
AND rownum = 1;
SELECT budget_version_id
INTO l_budget_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code=l_migrated_frm_bdgt_typ_code
AND rownum = 1;
select
nvl(record_version_number, 0)
into
l_record_version_number
from
pa_budget_versions
where
budget_version_id=p_budget_version_id;
select 'Y'
into l_exists
from pa_budget_lines a,
pa_resource_assignments b
where a.resource_assignment_id = b.resource_assignment_id
and b.budget_version_id = p_budget_version_id
and rownum < 2;
select 'Y'
into l_exists
from pa_budget_lines a,
pa_resource_assignments b
where a.resource_assignment_id = b.resource_assignment_id
and b.budget_version_id = p_budget_version_id
and b.task_id = Nvl(p_task_id,b.task_id)
and b.resource_list_member_id = Nvl(p_resource_list_member_id,b.resource_list_member_id)
and rownum < 2;
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_resource_list_id)
INTO l_resource_list_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id;
SELECT budget_type_code,
budget_entry_method_code
INTO l_budget_type_code,
l_budget_entry_method_code
FROM pa_budget_versions
WHERE budget_version_id = p_fin_plan_version_id;
SELECT time_phased_type_code
INTO l_time_phased_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = l_budget_entry_method_code;
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_time_phased_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_time_phased_code,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_time_phased_code)
INTO l_time_phased_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id;
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_fin_plan_level_code)
INTO l_fin_plan_level_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id;
SELECT PLAN_IN_MULTI_CURR_FLAG
INTO l_multi_curr_flag
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id;
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP,
DECODE(p_element_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))
INTO l_fin_plan_level_code
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_amount_set_id,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_amount_set_id,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_amount_set_id)
INTO l_amount_set_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_fin_plan_version_id;
select period_name1
into l_start_date
from pa_proj_period_profiles
where period_profile_id = p_period_profile_id;
select profile_end_period_name
into l_end_date
from pa_proj_period_profiles
where period_profile_id = p_period_profile_id;
SELECT resource_list_id
INTO l_wp_bv_res_list_id
FROM pa_budget_versions
WHERE project_structure_version_id=p_proj_structure_version_id AND
NVL(WP_VERSION_FLAG,'N') = 'Y';
SELECT DECODE(BV.VERSION_TYPE,
'COST', OPT.COST_TIME_PHASED_CODE,
'REVENUE',OPT.REVENUE_TIME_PHASED_CODE,
'ALL',OPT.ALL_TIME_PHASED_CODE)
INTO x_time_phased_code
FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
WHERE BV.BUDGET_VERSION_ID = OPT.FIN_PLAN_VERSION_ID
AND BV.PROJECT_STRUCTURE_VERSION_ID = p_wp_structure_version_id
AND NVL(BV.WP_VERSION_FLAG,'N') = 'Y'
AND bv.project_id = opt.project_id -- added bug 6892631
AND bv.fin_plan_type_id = opt.fin_plan_type_id; -- added bug 6892631
SELECT budget_version_id
INTO x_app_bdgt_cost_cb_ver
FROM pa_budget_versions
WHERE project_id = p_project_id
AND nvl(APPROVED_COST_PLAN_TYPE_FLAG,'N') = 'Y'
AND budget_status_code = 'B'
AND current_flag = 'Y';
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_baselined_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 current_flag = 'Y'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT proj_fp_options_id
INTO l_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = l_baselined_version_id;
procedure Delete_Fp_Options(
p_project_id IN PA_FP_TXN_CURRENCIES.PROJECT_ID%TYPE
, x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
is
begin
-- delete from pa_proj_fp_options table
delete from pa_proj_fp_options where project_id=p_project_id;
delete from pa_fp_txn_currencies where project_id=p_project_id;
Commenting out code below for delete statment from pa_fp_elements
as this table is getting obsoleted */
-- delete from pa_fp_elements table
/* delete from pa_fp_elements where project_id=p_project_id; */
delete from pa_proj_period_profiles where project_id=p_project_id;
/* Bug 3683382 this delete is not required functionally as records can not
exist for a project level option in this table
-- delete from pa_resource_assignments table
delete from pa_resource_assignments where project_id = p_project_id;
/*start of bug 3342975 Refer to Update "16-JAN-04 sagarwal"
in the history above. This has been added as part of code merge */
-- delete from pa_fp_excluded_elements table
/* Changes for FPM, Tracking Bug No - 3354518
Commenting out code below for delete statment from pa_fp_excluded_elements
as this table is getting obsoleted */
/* delete from pa_fp_excluded_elements where project_id = p_project_id; */
delete from PA_FP_UPGRADE_AUDIT where project_id = p_project_id;
end Delete_Fp_Options;
PROCEDURE Update_Txn_Currencies
(p_project_id IN PA_FP_TXN_CURRENCIES.PROJECT_ID%TYPE
,p_proj_curr_code IN PA_FP_TXN_CURRENCIES.TXN_CURRENCY_CODE%TYPE)
is
cursor get_all_fp_options_cur is
select proj_fp_options_id
from pa_proj_fp_options
where project_id = p_project_id;
select fp_txn_currency_id,
txn_currency_code
from pa_fp_txn_currencies
where project_id = p_project_id
and project_currency_flag='Y'
and proj_fp_options_id = c_proj_fp_options_id;
select fp_txn_currency_id,
txn_currency_code
from pa_fp_txn_currencies
where project_id = p_project_id
and projfunc_currency_flag='Y'
and proj_fp_options_id = c_proj_fp_options_id;
select fp_txn_currency_id
from pa_fp_txn_currencies
where project_id = p_project_id
and txn_currency_code = p_proj_curr_code
and project_currency_flag='N'
and proj_fp_options_id = c_proj_fp_options_id;
/* Bug 5364011: The following code is introduced to update the plan_in_multi_curr_flag as 'Y'
in pa_proj_fp_options, if the newly entered project currency is different from the
existing project funtional currency. */
TYPE plan_in_multi_curr_tbl IS TABLE OF pa_proj_fp_options.proj_fp_options_id%TYPE
INDEX BY BINARY_INTEGER;
delete from pa_fp_txn_currencies
where fp_txn_currency_id = l_txn_currency_id;
update pa_fp_txn_currencies
set txn_currency_code = p_proj_curr_code
where fp_txn_currency_id = l_pc_currency_id;
update pa_fp_txn_currencies
set txn_currency_code = p_proj_curr_code,
projfunc_currency_flag = 'Y'
where fp_txn_currency_id = l_pc_currency_id;
update pa_fp_txn_currencies
set txn_currency_code = p_proj_curr_code
where fp_txn_currency_id = l_pc_currency_id;
update pa_fp_txn_currencies
set project_currency_flag='N'
where fp_txn_currency_id = l_pc_currency_id;
update pa_fp_txn_currencies
set project_currency_flag='Y'
where fp_txn_currency_id = l_txn_currency_id;
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate
,project_rev_exchange_rate
,projfunc_cost_exchange_Rate
,projfunc_rev_exchange_Rate
)
SELECT pa_fp_txn_currencies_s.NEXTVAL
, PROJ_FP_OPTIONS_ID
, PROJECT_ID
, FIN_PLAN_TYPE_ID
, FIN_PLAN_VERSION_ID
, p_proj_curr_code
, DEFAULT_REV_CURR_FLAG
, DEFAULT_COST_CURR_FLAG
, DEFAULT_ALL_CURR_FLAG
, 'Y'
, 'N'
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, PROJECT_COST_EXCHANGE_RATE
, PROJECT_REV_EXCHANGE_RATE
, PROJFUNC_COST_EXCHANGE_RATE
, PROJFUNC_REV_EXCHANGE_RATE
FROM PA_FP_TXN_CURRENCIES
where fp_txn_currency_id = l_pc_currency_id;
/* Bug 5364011: The following code is introduced to update the plan_in_multi_curr_flag as 'Y'
in pa_proj_fp_options, if the newly entered project currency is different from the existing
project functional currency. */
IF trim(p_proj_curr_code) <> trim(l_projfunc_curr_code) THEN
cnt := cnt+1;
/* Bug 5364011: The following code is introduced to update the plan_in_multi_curr_flag as 'Y'
in pa_proj_fp_options, if the newly entered project currency is different from the existing
project functional currency. */
IF l_plan_in_multi_curr_tbl.COUNT > 0 THEN
FORALL opt IN l_plan_in_multi_curr_tbl.FIRST..l_plan_in_multi_curr_tbl.LAST
UPDATE pa_proj_fp_options
SET plan_in_multi_curr_flag = 'Y',
record_version_number = record_version_number+1
WHERE proj_fp_options_id = l_plan_in_multi_curr_tbl(opt);
l_plan_in_multi_curr_tbl.DELETE;
end Update_Txn_Currencies;
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_working_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 current_working_flag = 'Y'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT proj_fp_options_id
INTO l_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = l_current_working_version_id;
SELECT fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND approved_cost_plan_type_flag = 'Y';
SELECT fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND approved_rev_plan_type_flag = 'Y';
select fin_plan_amount_set_id
into x_cost_amount_set_id
from pa_fin_plan_amount_sets
where
raw_cost_flag=p_raw_cost_flag and
burdened_cost_flag=p_burdened_cost_flag and
cost_qty_flag=p_cost_qty_flag and
revenue_flag = 'N' and
revenue_qty_flag = 'N' and
all_qty_flag = 'N' and
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
bill_rate_flag = 'N' and
cost_rate_flag = p_cost_rate_flag and
burden_rate_flag = p_burden_rate_flag and
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
amount_set_type_code=PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
burden_rate to the insert statment below */
IF l_status = 'NEW' THEN
INSERT INTO PA_FIN_PLAN_AMOUNT_SETS (
FIN_PLAN_AMOUNT_SET_ID,
AMOUNT_SET_TYPE_CODE,
RAW_COST_FLAG,
BURDENED_COST_FLAG,
COST_QTY_FLAG,
REVENUE_FLAG,
REVENUE_QTY_FLAG,
ALL_QTY_FLAG,
TP_COST_FLAG,
TP_REVENUE_FLAG,
UTIL_PERCENT_FLAG,
UTIL_HOURS_FLAG,
CAPACITY_FLAG,
PRE_DEFINED_FLAG,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
BILL_RATE_FLAG,
COST_RATE_FLAG,
BURDEN_RATE_FLAG,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
pa_fin_plan_amount_sets_s.NEXTVAL,
'COST',
p_raw_cost_flag,
p_burdened_cost_flag,
p_cost_qty_flag,
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
'N',
p_cost_rate_flag,
p_burden_rate_flag,
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
SYSDATE,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id) RETURNING FIN_PLAN_AMOUNT_SET_ID INTO x_cost_amount_set_id;
select fin_plan_amount_set_id
into x_revenue_amount_set_id
from pa_fin_plan_amount_sets
where
revenue_flag=p_revenue_flag and
revenue_qty_flag=p_revenue_qty_flag and
raw_cost_flag = 'N' and
burdened_cost_flag = 'N' and
cost_qty_flag = 'N' and
all_qty_flag = 'N' and
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
bill_rate_flag = p_bill_rate_flag and
cost_rate_flag = 'N' and
burden_rate_flag = 'N' and
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
amount_set_type_code = 'REVENUE';
burden_rate to the insert statment below */
INSERT INTO PA_FIN_PLAN_AMOUNT_SETS (
FIN_PLAN_AMOUNT_SET_ID,
AMOUNT_SET_TYPE_CODE,
RAW_COST_FLAG,
BURDENED_COST_FLAG,
COST_QTY_FLAG,
REVENUE_FLAG,
REVENUE_QTY_FLAG,
ALL_QTY_FLAG,
TP_COST_FLAG,
TP_REVENUE_FLAG,
UTIL_PERCENT_FLAG,
UTIL_HOURS_FLAG,
CAPACITY_FLAG,
PRE_DEFINED_FLAG,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
BILL_RATE_FLAG,
COST_RATE_FLAG,
BURDEN_RATE_FLAG,
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
pa_fin_plan_amount_sets_s.NEXTVAL,
'REVENUE',
'N',
'N',
'N',
p_revenue_flag,
p_revenue_qty_flag,
'N',
'N',
'N',
'N',
'N',
'N',
'N',
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
p_bill_rate_flag,
'N',
'N',
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
SYSDATE,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id)
RETURNING FIN_PLAN_AMOUNT_SET_ID INTO x_revenue_amount_set_id;
select fin_plan_amount_set_id
into x_all_amount_set_id
from pa_fin_plan_amount_sets
where
raw_cost_flag=p_raw_cost_flag and
burdened_cost_flag=p_burdened_cost_flag and
revenue_flag=p_revenue_flag and
all_qty_flag=p_all_qty_flag and
cost_qty_flag = 'N' and
revenue_qty_flag = 'N' and
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
bill_rate_flag = p_bill_rate_flag and
cost_rate_flag = p_cost_rate_flag and
burden_rate_flag = p_burden_rate_flag and
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
amount_set_type_code = 'ALL';
burden_rate to the insert statment below */
INSERT INTO PA_FIN_PLAN_AMOUNT_SETS (
FIN_PLAN_AMOUNT_SET_ID,
AMOUNT_SET_TYPE_CODE,
RAW_COST_FLAG,
BURDENED_COST_FLAG,
COST_QTY_FLAG,
REVENUE_FLAG,
REVENUE_QTY_FLAG,
ALL_QTY_FLAG ,
TP_COST_FLAG,
TP_REVENUE_FLAG,
UTIL_PERCENT_FLAG,
UTIL_HOURS_FLAG,
CAPACITY_FLAG,
PRE_DEFINED_FLAG,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
BILL_RATE_FLAG,
COST_RATE_FLAG,
BURDEN_RATE_FLAG,
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
pa_fin_plan_amount_sets_s.NEXTVAL,
'ALL',
p_raw_cost_flag,
p_burdened_cost_flag,
'N',
p_revenue_flag,
'N',
p_all_qty_flag,
'N',
'N',
'N',
'N',
'N',
'N',
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
p_bill_rate_flag,
p_cost_rate_flag,
p_burden_rate_flag,
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
SYSDATE,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id)
RETURNING FIN_PLAN_AMOUNT_SET_ID INTO x_all_amount_set_id;
select raw_cost_flag,
burdened_cost_flag,
revenue_flag,
cost_qty_flag,
revenue_qty_flag,
all_qty_flag,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
bill_rate_flag,
cost_rate_flag,
burden_rate_flag
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
into
X_RAW_COST_FLAG,
X_BURDENED_FLAG,
X_REVENUE_FLAG,
X_COST_QUANTITY_FLAG,
X_REV_QUANTITY_FLAG,
X_ALL_QUANTITY_FLAG,
/* Changes for FPM Start here ,Tracking Bug No - 3354518*/
X_BILL_RATE_FLAG,
X_COST_RATE_FLAG,
X_BURDEN_RATE_FLAG
/* Changes for FPM End here ,Tracking Bug No - 3354518*/
from
PA_FIN_PLAN_AMOUNT_SETS
where
fin_plan_amount_set_id = P_AMOUNT_SET_ID;
select pt.fin_plan_type_code
into l_plan_type_code
from pa_budget_versions bv,
pa_fin_plan_types_b pt
where bv.budget_version_id = p_budget_version_id and
bv.fin_plan_type_id = pt.fin_plan_type_id;
select full_name
into l_person_name
from per_people_x
where person_id = p_person_id;
SELECT start_date
,end_date
,bucketing_period_code
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code
AND bucketing_period_code in ('SD','PD');
SELECT sum(nvl(QUANTITY,0))
,sum(nvl(RAW_COST,0))
,sum(nvl(BURDENED_COST,0))
,sum(nvl(REVENUE,0))
,sum(nvl(PROJECT_RAW_COST,0))
,sum(nvl(PROJECT_BURDENED_COST,0))
,sum(nvl(PROJECT_REVENUE,0))
INTO x_quantity
,x_projfunc_raw_cost
,x_projfunc_burdened_cost
,x_projfunc_revenue
,x_project_raw_cost
,x_project_burdened_cost
,x_project_revenue
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND txn_currency_code = p_txn_currency_code ;
SELECT budget_version_id
INTO x_budget_version_id
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT budget_version_id
INTO x_budget_version_id
FROM pa_budget_versions
WHERE version_name = p_version_name
AND project_id = p_project_id ; -- Bug 2770562
SELECT txn_currency_code
INTO x_txn_currency_code
FROM pa_fp_txn_currencies
WHERE txn_currency_code = p_txn_currency_code;
SELECT currency_code
INTO x_txn_currency_code
FROM fnd_currencies_tl
WHERE name = replace(p_currency_code_name, currency_code || ' - ')
AND language = USERENV('LANG');
select task_id
into x_task_id
from PA_STRUCT_TASK_WBS_V -- Changes for FP.M, Tracking Bug No - 3354518
where task_id = p_task_id;
select task_id
into x_task_id
from PA_STRUCT_TASK_WBS_V -- Changes for FP.M, Tracking Bug No - 3354518
where project_id = p_project_id and
task_name = p_task_name;
select resource_list_member_id
into x_resource_id
from pa_resource_list_members
where resource_list_member_id = p_resource_id;
select rlm.resource_list_member_id
into x_resource_id
from pa_resources r,
pa_resource_list_members rlm
where r.name = p_resource_name and
r.resource_id = rlm.resource_id and
rlm.parent_member_id is null;
select resource_id
into x_resource_id
from pa_resources
where resource_id = p_resource_id;
select resource_id
into x_resource_id
from pa_resources
where name = p_resource_name;
select 1
into l_dummy
from sys.dual
where exists
/* Changes for FP.M, Tracking Bug No - 3354518
Adding conditon in the where clause below to
check for new column use_for_workplan flag.
This column indicates if a plan type is being
used for workplan or not.
So adding a join to pa_fin_plan_types_b and checking status of use_for_workplan_flag.
Without this check the function would return success status even if WP plantype exists */
(select 1 from pa_proj_fp_options pfo, pa_fin_plan_types_b pft -- Added pa_fin_plan_types_b for FP.M changes
where pfo.project_id = p_project_id
/*Changes for FP.M start here */
and pfo.fin_plan_option_level_code='PLAN_TYPE' /*bug 3224177 added fin_plan_option_level_code check*/
and nvl(pfo.fin_plan_type_id,-99) = pft.fin_plan_type_id
and nvl(pft.use_for_workplan_flag,'N') = 'N');
SELECT atb.amount_type_id
,atb.amount_type_code
FROM pa_amount_types_b atb
WHERE atb.amount_type_class = 'R';
select locked_by_person_id,
budget_status_code
from pa_budget_versions
where budget_version_id = p_budget_version_id;
/* Commenting out the select statment below for FP.M, Tracking Bug No - 3354518
The Select statement is modified and re-written below.
Please note that the similar change is also done for bug no - 3224177 in version 115.117.
The Select statment below has lot of redundant code which can be simplified
to check for the existence of PLAN_TYPE fp_options only haveing use_for_workplan
not set to 'Y'. Please note that this API will not be called for workplan Usage*/
/*
Select 'Y'
Into l_return
From dual
Where exists (Select 'x'
from pa_budget_lines bl,
pa_budget_versions bv
where bl.budget_version_id = bv.budget_version_id
and bv.project_id = p_project_id)
OR exists (Select 'x' -- included for bug 3224177
from pa_proj_fp_options pfo,
pa_projects_all pa where
pfo.project_id = pa.project_id and
pa.project_id = p_project_id and
pfo.fin_plan_option_level_code = 'PLAN_TYPE');
OR exists (Select 'x'
from pa_fp_txn_currencies fpcurr,
pa_proj_fp_options pfo, -- bug 3106741
pa_projects_all pa
where pa.project_currency_code = fpcurr.txn_currency_code
and pa.project_id = fpcurr.project_id
and fpcurr.project_currency_flag = 'Y'
and pfo.proj_fp_options_id = fpcurr.proj_fp_options_id -- bug 3106741
and pfo.project_id = pa.project_id -- bug 3106741
and pa.project_id = p_project_id
and pfo.fin_plan_option_level_code = 'PLAN_TYPE' ); end of bug 3224177 comment*/
/* Modified Select Clause */
Select 'Y'
Into l_return
From dual
Where exists (Select 'x'
from pa_budget_lines bl,
pa_budget_versions bv
where bl.budget_version_id = bv.budget_version_id
and bv.project_id = p_project_id)
OR exists (Select 'x' -- included for bug 3224177
from pa_proj_fp_options pfo, pa_fin_plan_types_b pft ,
pa_projects_all pa
where pfo.project_id = pa.project_id and
pa.project_id = p_project_id and
/* Commented out the below for bug 5364011*/
-- pfo.fin_plan_option_level_code = 'PLAN_TYPE' and
pfo.fin_plan_option_level_code = 'PLAN_VERSION' and -- Bug 5364011.
pfo.fin_plan_type_id = pft.fin_plan_type_id and
nvl(pft.use_for_workplan_flag,'N') = 'N');
Select 'Y'
from pa_proj_fp_options po /* Bug# 2665767 - Plan type option alone can be checked */
where po.approved_rev_plan_type_flag = 'Y'
and po.fin_plan_preference_code = 'COST_AND_REV_SAME'
and po.fin_plan_option_level_code = 'PLAN_TYPE'
and po.project_id = p_project_id;
SELECT nvl(uncategorized_flag,'N')
,decode (group_resource_type_id,0,'N','Y')
,group_resource_type_id
INTO x_res_list_is_uncategorized
,x_is_resource_list_grouped
,x_group_resource_type_id
FROM pa_resource_lists
WHERE resource_list_id = p_resource_list_id ;
SELECT nvl(uncategorized_flag,'N')
,decode (group_resource_type_id,0,'N','Y')
,group_resource_type_id
,use_for_wp_flag
,control_flag
,migration_code
INTO x_res_list_is_uncategorized
,x_is_resource_list_grouped
,x_group_resource_type_id
,x_use_for_wp_flag
,x_control_flag
,x_migration_code
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id ;
pa_debug.g_err_stage:='Executing the uncat res list info select...';
select pbg.resource_list_id,
prlm.track_as_labor_flag,
prlm.resource_list_member_id,
prlm.unit_of_measure
into x_resource_list_id,
x_track_as_labor_flag,
x_resource_list_member_id,
x_unit_of_measure
from pa_resource_lists_all_bg pbg,
pa_resource_list_members prlm
where pbg.business_group_id = l_business_group_id and -- bug 2760675 pa_utils.business_group_id and
pbg.uncategorized_flag = 'Y' and
prlm.resource_list_id = pbg.resource_list_id and
prlm.object_id = pbg.resource_list_id and
prlm.object_type = 'RESOURCE_LIST' and
prlm.resource_class_code = 'FINANCIAL_ELEMENTS' and
prlm.resource_class_flag = 'Y';
SELECT pbg.resource_list_id
,prlm.track_as_labor_flag
,prlm.resource_list_member_id
,pr.unit_of_measure
INTO x_resource_list_id
,x_track_as_labor_flag
,x_resource_list_member_id
,x_unit_of_measure
FROM pa_resource_lists_all_bg pbg
,pa_resource_list_members prlm
,pa_resources pr
WHERE prlm.resource_list_id = pbg.resource_list_id
AND pbg.resource_list_id = prlm.resource_list_id
AND prlm.resource_id = pr.resource_id
AND pbg.uncategorized_flag = 'Y'
AND pbg.business_group_id = pa_utils.business_group_id;
select bud.budget_type_code
into l_budget_type_code
from pa_budget_types bud
where bud.budget_type_code = l_ac_budget_type_code --Bug 3764635.
and exists
(
select budget_version_id
from pa_budget_versions
where project_id = p_project_id -- project id.
and budget_type_code = bud.budget_type_code
);
select bud.budget_type_code
into l_budget_type_code
from pa_budget_types bud
where bud.budget_type_code = l_ar_budget_type_code --Bug 3764635.
and exists
(
select budget_version_id
from pa_budget_versions
where project_id = p_project_id -- project id.
and budget_type_code = bud.budget_type_code
);
SELECT NVL(max(version_number),0)
INTO l_version_number
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED)
AND ci_id IS NULL;
SELECT version_number
INTO l_version_number
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED)
AND ci_id is null
AND version_number = l_version_number
FOR UPDATE;
SELECT NVL(max(version_number),0)
INTO l_version_number
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED)
AND ci_id = p_ci_id;
SELECT version_number
INTO l_version_number
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED)
AND ci_id = p_ci_id
AND version_number = l_version_number
FOR UPDATE;
SELECT NVL(max(version_number),0)
INTO l_version_number
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_BASELINED);
SELECT start_date
INTO l_start_date
FROM pa_periods
WHERE p_input_date between start_date and end_date;
SELECT g.start_date
INTO l_start_date
FROM PA_IMPLEMENTATIONS i,
GL_PERIOD_STATUSES g
WHERE g.set_of_books_id = i.set_of_books_id
AND g.application_id = pa_period_process_pkg.application_id
AND g.adjustment_period_flag = 'N'
AND p_input_date between g.start_date and g.end_date;
SELECT end_date
INTO l_end_date
FROM pa_periods
WHERE p_input_date between start_date and end_date;
SELECT g.end_date
INTO l_end_date
FROM PA_IMPLEMENTATIONS i,
GL_PERIOD_STATUSES g
WHERE g.set_of_books_id = i.set_of_books_id
AND g.application_id = pa_period_process_pkg.application_id
AND g.adjustment_period_flag = 'N'
AND p_input_date between g.start_date and g.end_date;
SELECT fin_plan_preference_code
INTO l_fin_plan_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 fin_plan_preference_code
INTO l_fin_plan_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;
DELETE FROM pa_fp_rollup_tmp;
INSERT INTO pa_fp_rollup_tmp(
resource_assignment_id,
start_date,
end_date,
txn_currency_code,
project_currency_code,
projfunc_currency_code,
txn_raw_cost,
txn_burdened_cost,
txn_revenue )
VALUES(
-1,
sysdate,
sysdate,
p_project_currency_Code,
p_project_currency_Code,
p_projfunc_currency_Code,
p_txn_raw_cost,
p_txn_burdened_cost,
p_txn_revenue );
SELECT PROJFUNC_RAW_COST,
PROJFUNC_BURDENED_COST,
PROJFUNC_REVENUE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_REVENUE
INTO
x_projfunc_raw_cost,
x_projfunc_burdened_cost,
x_projfunc_revenue,
x_project_raw_cost,
x_project_burdened_cost,
x_project_revenue
FROM Pa_Fp_Rollup_Tmp
WHERE RESOURCE_ASSIGNMENT_ID = -1;
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
--and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.approved_cost_plan_type_flag = 'Y'
and bV.current_flag = 'Y');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
where bv.project_id = x_project_id
--and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.approved_rev_plan_type_flag = 'Y'
and bV.current_flag = 'Y');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_tasks t
, pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and a.resource_assignment_type = 'USER_ENTERED'
and t.top_task_id = x_task_id
--and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.approved_cost_plan_type_flag = 'Y'
and bV.current_flag = 'Y');
select 1
into dummy
from dual
where exists
(select 1
from pa_budget_versions bv
, pa_tasks t
, pa_resource_assignments a
where a.budget_version_id = bv.budget_version_id
and a.task_id = t.task_id
and a.resource_assignment_type = 'USER_ENTERED'
and t.top_task_id = x_task_id
--and bv.fin_plan_type_id = x_fin_plan_type_id
and bv.approved_rev_plan_type_flag = 'Y'
and bV.current_flag = 'Y');
SELECT start_date
,end_date
INTO x_start_date
,x_end_date
FROM pa_periods
WHERE period_name = p_period_name;
SELECT start_date
,end_date
INTO x_start_date
,x_end_date
FROM gl_period_statuses g
,pa_implementations i
WHERE g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id
AND g.adjustment_period_flag = 'N'
AND g.period_name = p_period_name;
SELECT period_name
,start_date
,end_date
INTO x_shifted_period
,x_shifted_period_start_date
,x_shifted_period_end_date
FROM pa_periods a
WHERE p_number_of_periods = (SELECT COUNT(*) FROM pa_periods b
WHERE b.start_date < a.start_date
AND b.start_date >= l_start_date )
/* bug fix:5090115: added this to avoid FTS on pa_periods */
AND a.start_date >= l_start_date ;
SELECT /*+ index(a pa_periods_u2) */
period_name
,start_date
,end_date
INTO x_shifted_period
,x_shifted_period_start_date
,x_shifted_period_end_date
FROM pa_periods a
WHERE ABS(p_number_of_periods) = (SELECT COUNT(*) FROM pa_periods b
WHERE b.start_date > a.start_date
AND b.start_date <= l_start_date )
/* bug fix:5090115: added this to avoid FTS on pa_periods */
AND a.start_date <= l_start_date ;
SELECT period_name
,start_date
,end_date
INTO x_shifted_period
,x_shifted_period_start_date
,x_shifted_period_end_date
FROM gl_period_statuses g1
,pa_implementations i
WHERE g1.application_id = pa_period_process_pkg.application_id
AND g1.set_of_books_id = i.set_of_books_id
AND g1.adjustment_period_flag = 'N'
AND p_number_of_periods = (SELECT COUNT(*)
FROM gl_period_statuses g2
,pa_implementations i2
WHERE g2.adjustment_period_flag = 'N'
AND g2.application_id =pa_period_process_pkg.application_id
AND g2.set_of_books_id = i2.set_of_books_id
AND g2.start_date < g1.start_date
AND g2.start_date >= l_start_date);
SELECT period_name
,start_date
,end_date
INTO x_shifted_period
,x_shifted_period_start_date
,x_shifted_period_end_date
FROM gl_period_statuses g1
,pa_implementations i
WHERE g1.application_id = pa_period_process_pkg.application_id
AND g1.set_of_books_id = i.set_of_books_id
AND g1.adjustment_period_flag = 'N'
AND abs(p_number_of_periods) = (SELECT COUNT(*)
FROM gl_period_statuses g2
,pa_implementations i2
WHERE g2.adjustment_period_flag = 'N'
AND g2.application_id = pa_period_process_pkg.application_id
AND g2.set_of_books_id = i2.set_of_books_id
AND g2.start_date > g1.start_date
AND g2.start_date <= l_start_date);
SELECT budget_version_id, labor_quantity, est_quantity, equipment_quantity
FROM pa_budget_versions
WHERE project_id = p_project_id
-- Bug 5845142
-- AND version_type = nvl(c_version_type,version_type)
AND DECODE(c_version_type,
'COST',approved_cost_plan_type_flag,
'Y')='Y'
AND ci_id = c_ci_id;
SELECT budget_version_id, labor_quantity, est_quantity, equipment_quantity
FROM pa_budget_versions
WHERE project_id = p_project_id
AND current_flag = 'Y'
AND version_type = nvl(c_version_type,version_type)
AND (NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' );
SELECT budget_version_id, labor_quantity, est_quantity, equipment_quantity
FROM pa_budget_versions
WHERE project_id = p_project_id
AND current_working_flag = 'Y'
AND version_type = nvl(c_version_type,version_type)
AND (NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' );
SELECT count(1)
INTO l_ver_count
FROM pa_budget_versions
WHERE project_id = p_project_id
AND ci_id = p_ci_id;
SELECT count(1)
INTO l_ver_count
FROM pa_budget_versions
WHERE project_id = p_project_id
AND current_flag = 'Y'
AND (Approved_Cost_Plan_Type_Flag = 'Y' OR
Approved_Rev_Plan_Type_Flag = 'Y' );
SELECT count(1)
INTO l_ver_count
FROM pa_budget_versions
WHERE project_id = p_project_id
AND current_working_flag = 'Y'
AND (Approved_Cost_Plan_Type_Flag = 'Y' OR
Approved_Rev_Plan_Type_Flag = 'Y' );
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS (SELECT 'X'
FROM pa_conversion_types_v
WHERE ((p_project_cost_rate_type IS NULL
OR p_project_cost_rate_type=conversion_type) OR
p_project_currency_code IN( p_txn_currency_code
,p_projfunc_currency_code))
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_conversion_types_v
WHERE ((p_projfunc_cost_rate_type IS NULL
OR p_projfunc_cost_rate_type=conversion_type) OR
p_projfunc_currency_code = p_txn_currency_code )
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_conversion_types_v
WHERE ((p_project_rev_rate_type IS NULL
OR p_project_rev_rate_type=conversion_type) OR
p_project_currency_code IN( p_txn_currency_code
,p_projfunc_currency_code))
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_conversion_types_v
WHERE ((p_projfunc_rev_rate_type IS NULL
OR p_projfunc_rev_rate_type=conversion_type) OR
p_projfunc_currency_code = p_txn_currency_code )
AND rownum=1);
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS (SELECT 'X'
FROM pa_lookups
WHERE lookup_type='PA_FP_RATE_DATE_TYPE'
AND ((p_project_cost_rate_date_typ IS NULL
OR p_project_cost_rate_date_typ=lookup_code) OR
p_project_currency_code IN( p_txn_currency_code
,p_projfunc_currency_code))
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_lookups
WHERE lookup_type='PA_FP_RATE_DATE_TYPE'
AND ((p_projfunc_cost_rate_date_typ IS NULL
OR p_projfunc_cost_rate_date_typ=lookup_code) OR
p_projfunc_currency_code = p_txn_currency_code )
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_lookups
WHERE lookup_type='PA_FP_RATE_DATE_TYPE'
AND ((p_project_rev_rate_date_typ IS NULL
OR p_project_rev_rate_date_typ=lookup_code) OR
p_project_currency_code IN( p_txn_currency_code
,p_projfunc_currency_code))
AND rownum=1)
AND EXISTS (SELECT 'X'
FROM pa_lookups
WHERE lookup_type='PA_FP_RATE_DATE_TYPE'
AND ((p_projfunc_rev_rate_date_typ IS NULL
OR p_projfunc_rev_rate_date_typ=lookup_code) OR
p_projfunc_currency_code = p_txn_currency_code )
AND rownum=1) ;
/* Null Combination of conversion attributes is valid in the case of create update plan type
pages. Hence this check is made
*/
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='All the attributes are null';
/* on create update plan type it is allowed that when rate type is user there is no rate defined
*/
x_return_status := FND_API.G_RET_STS_ERROR;
As of now for WEBADI and Create Update Plan type context, this is not an
issue. But when this api is being used for the other contexts like AMG and
edit plan line details page,... this message should be changed so as to accept
tokens.
*/
PA_UTILS.ADD_MESSAGE
(p_app_short_name => 'PA',
p_msg_name => 'PA_FP_USER_EXCH_RATE_REQ',
p_token1 => 'COST_REV',
p_value1 => p_amount_type_code,
p_token2 => 'PROJECT_PROJFUNC',
p_value2 => p_currency_type_code );
CR_UP_PLAN_TYPE_PAGE (for create Update plan type page)
AMG_API (for AMG APIs)
*/
PROCEDURE VALIDATE_CURRENCY_ATTRIBUTES
( px_project_cost_rate_type IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_type%TYPE --File.Sql.39 bug 4440895
,px_project_cost_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_date_type%TYPE --File.Sql.39 bug 4440895
,px_project_cost_rate_date IN OUT NOCOPY pa_proj_fp_options.project_cost_rate_date%TYPE --File.Sql.39 bug 4440895
,px_project_cost_exchange_rate IN OUT NOCOPY pa_budget_lines.project_cost_exchange_rate%TYPE --File.Sql.39 bug 4440895
,px_projfunc_cost_rate_type IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_type%TYPE --File.Sql.39 bug 4440895
,px_projfunc_cost_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE --File.Sql.39 bug 4440895
,px_projfunc_cost_rate_date IN OUT NOCOPY pa_proj_fp_options.projfunc_cost_rate_date%TYPE --File.Sql.39 bug 4440895
,px_projfunc_cost_exchange_rate IN OUT NOCOPY pa_budget_lines.projfunc_cost_exchange_rate%TYPE --File.Sql.39 bug 4440895
,px_project_rev_rate_type IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_type%TYPE --File.Sql.39 bug 4440895
,px_project_rev_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_date_type%TYPE --File.Sql.39 bug 4440895
,px_project_rev_rate_date IN OUT NOCOPY pa_proj_fp_options.project_rev_rate_date%TYPE --File.Sql.39 bug 4440895
,px_project_rev_exchange_rate IN OUT NOCOPY pa_budget_lines.project_rev_exchange_rate%TYPE --File.Sql.39 bug 4440895
,px_projfunc_rev_rate_type IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_type%TYPE --File.Sql.39 bug 4440895
,px_projfunc_rev_rate_date_typ IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE --File.Sql.39 bug 4440895
,px_projfunc_rev_rate_date IN OUT NOCOPY pa_proj_fp_options.projfunc_rev_rate_date%TYPE --File.Sql.39 bug 4440895
,px_projfunc_rev_exchange_rate IN OUT NOCOPY pa_budget_lines.projfunc_rev_exchange_rate%TYPE --File.Sql.39 bug 4440895
,p_project_currency_code IN pa_projects_all.project_currency_code%TYPE
,p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE
,p_txn_currency_code IN pa_projects_all.projfunc_currency_code%TYPE
,p_context IN VARCHAR2
,p_attrs_to_be_validated IN VARCHAR2 -- valid values are COST, REVENUE , BOTH
,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
)IS
l_msg_count NUMBER := 0;
/*Do the Additional validations required in the case of Create / Update plan type page*/
IF l_debug_mode='Y' THEN
pa_debug.g_err_stage:= 'p_context is '||p_context;
select o.fin_plan_type_id,o.proj_fp_options_id,v.version_type
from pa_proj_fp_options o,pa_budget_versions v
where o.fin_plan_type_id = v.fin_plan_type_id
and o.project_id = v.project_id
and v.budget_version_id = c_version_id
and o.fin_plan_option_level_code = 'PLAN_TYPE';
SELECT p.multi_currency_billing_flag
,p.project_currency_code
,p.projfunc_currency_code
,NVL(p.project_rate_type,i.default_rate_type) project_cost_rate_type
,NVL(p.projfunc_cost_rate_type,i.default_rate_type) projfunc_cost_rate_type
,p.project_bil_rate_type
,p.projfunc_bil_rate_type
INTO x_multi_currency_billing_flag
,x_project_currency_code
,x_projfunc_currency_code
,x_project_cost_rate_type
,x_projfunc_cost_rate_type
,x_project_bil_rate_type
,x_projfunc_bil_rate_type
FROM pa_projects_all p
,pa_implementations_all i
WHERE p.project_id = p_project_id
--AND NVL(p.org_id,-99) = NVL(i.org_id,-99);
AND p.org_id = i.org_id; /* Bug 3174677: Added the NVL ,Refer to Update
in the select statment below
Please note that this API will not be called for Workplan Usage*/
cursor autobaseline_appr_rev_info_cur is
SELECT nvl(pr.baseline_funding_flag,'N') baseline_funding_flag, pfo.approved_rev_plan_type_flag,
pft.use_for_workplan_flag -- Added for FP.M ,Tracking Bug No - 3354518
FROM pa_projects_all pr, pa_proj_fp_options pfo,
pa_fin_plan_types_b pft -- Added for FP.M ,Tracking Bug No - 3354518
WHERE pr.project_id = pfo.project_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND pfo.fin_plan_type_id = p_fin_plan_type_id
AND pfo.project_id = p_project_id
AND pft.fin_plan_type_id = p_fin_plan_type_id; -- Added for FP.M ,Tracking Bug No - 3354518
select lookup_code
from pa_lookups
where lookup_type = p_lookup_type
and meaning = p_lookup_meaning;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 'x'
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id);
SELECT fin_plan_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 name
INTO l_fin_plan_type_name
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = p_fin_plan_type_id;
SELECT segment1
INTO l_segment1
FROM pa_projects_all
WHERE project_id = p_project_id ;
SELECT budget_version_id,
ci_id
FROM pa_budget_versions
WHERE project_id=p_project_id
AND fin_plan_type_id=p_fin_plan_type_id
AND version_type=p_version_type
AND version_number=p_version_number
AND budget_status_code='W';
SELECT pbv.budget_type_code
,pbv.fin_plan_type_id
,pbv.version_type
,pfo.approved_rev_plan_type_flag
,p.baseline_funding_flag
FROM pa_budget_versions pbv
,pa_proj_fp_options pfo
,pa_projects_all p
WHERE pbv.budget_version_id=p_budget_version_id
AND pfo.fin_plan_version_id(+)=pbv.budget_version_id
AND p.project_id=pbv.project_id;
SELECT DECODE(budget_amount_code, 'C', PA_FP_CONSTANTS_PKG.G_VERSION_TYPE_COST,
'R', PA_FP_CONSTANTS_PKG.G_VERSION_TYPE_REVENUE,
'ALL', PA_FP_CONSTANTS_PKG.G_VERSION_TYPE_ALL)
INTO x_version_type
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT budget_status_code,
locked_by_person_id,
request_id, -- Bug 3057564
plan_processing_code
INTO l_budget_status_code,
x_locked_by_person_id,
l_request_id, -- Bug 3057564
l_plan_processing_code
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT Nvl(fpt.edit_after_baseline_flag, 'N'),
bv.project_id,
bv.fin_plan_type_id,
bv.version_type
INTO l_edit_after_baseline_flag,
l_project_id,
l_fin_plan_type_id,
l_version_type
FROM pa_fin_plan_types_b fpt,
pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.fin_plan_type_id = fpt.fin_plan_type_id;
SELECT 'N'
INTO is_edit_allowed
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM pa_budget_versions a
WHERE a.project_id = l_project_id
AND a.fin_plan_type_id = l_fin_plan_type_id
AND a.version_type = l_version_type
AND a.budget_status_code = 'B');
task can be deleted from old budgets model, organization forecasting, and new
Budgeting and Forecasting perspective. For old budgets model and organization
forecasting, presence of a task in resource assignments table implies that amounts
exist for the task and so the task can not be deleted. For financial planning model,
since records exists in pa_resource_assignments even when no budget lines exists,
pa_fp_elements table has to be verified to check if plan amounts exist for a task.
If p_validation_mode is U,
p_task_id should not be present in BASELINED versions and should not be present in
other versions with amounts
If p_validation_mode is R,
p_task_id should not be present in any version.
Bug 2993894, in Restricted mode deletion of a task is not allowed if the task is
referenced for any of the options(project/plan type/ plan version) in pa_fp_elements table.
*/
PROCEDURE check_delete_task_ok
( /* p_task_id IN pa_tasks.task_id%TYPE Commenting out NOCOPY for to replace --File.Sql.39 bug 4440895
pa_tasks by PA_STRUCT_TASK_WBS_V as part of FP.M, Tracking Bug No - 3354518 */
p_task_id IN pa_struct_task_wbs_v.task_id%TYPE
,p_validation_mode 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;
l_validation_success VARCHAR2(1) := 'Y'; /* Y when delete is allowed, N when delete is not allowed */
CURSOR delete_task_R_mode_cur IS
SELECT 'N' validation_success /* If cursor returns a record, deletion is not allowed */
FROM DUAL
WHERE EXISTS (
/* Commenting out as part of FP.M, Tracking Bug No - 3354518
Since We will now check the existence of a budget version
(having wp_version_flag = 'N') using pa_budget_version and
pa_resource_assignments */
/* SELECT 1
FROM pa_fp_elements fe */
/* Bug 2993894 ,pa_budget_versions bv */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
/* WHERE fe.task_id IN
(SELECT pt.task_id
FROM PA_TASKS pt */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
/* pa_tasks pt Commenting out for to replace pa_tasks by PA_STRUCT_TASK_WBS_V
as part of FP.M, Tracking Bug No - 3354518 */
/* CONNECT BY PRIOR pt.task_id = pt.parent_task_id
START WITH pt.task_id = p_task_id)*/ -- Commenting out code for FP.M, Tracking Bug No - 3354518
/* Bug 2993894 AND bv.budget_version_id = fe.fin_plan_version_id */
/* UNION ALL */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
SELECT 1
FROM pa_resource_assignments r,
pa_budget_versions bv
WHERE r.task_id IN
(SELECT pt.task_id /*Changing refernece of pa_struct_task_wbs_v below to pa_tasks*/
FROM PA_TASKS pt /*Reverting changes for FPM, view pa_struct_task_wbs_v cannot be used in connect by clause*/
CONNECT BY PRIOR pt.task_id = pt.parent_task_id
START WITH pt.task_id = p_task_id)
AND bv.budget_version_id = r.budget_version_id
AND nvl(bv.wp_version_flag,'N') = 'N'); -- Added for FP.M, Tracking Bug No - 3354518
bv.fin_plan_type_id IN (SELECT fpt.fin_plan_type_id
FROM pa_fin_plan_types_b fpt
WHERE fpt.fin_plan_type_code = 'ORG_FORECAST')));
/* The above cursor delete_task_R_mode_cur shall be used for both restricted as well as unrestructed mode */
/*
CURSOR delete_task_U_mode_cur IS
SELECT 'N' validation_success -- If cursor returns a record, deletion is not allowed
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM -- pa_fp_elements fe Commenting out for to replace pa_fp_elements by PA_RESOURCE_ASSIGNMENTS as part of FP.M, Tracking Bug No - 3354518
pa_resource_assignments fe,
pa_budget_versions bv
WHERE fe.task_id IN
(SELECT pt.task_id
FROM PA_STRUCT_TASK_WBS_V pt
-- pa_tasks pt Commenting out for to replace pa_tasks by PA_STRUCT_TASK_WBS_V as part of FP.M, Tracking Bug No - 3354518
CONNECT BY PRIOR pt.task_id = pt.parent_task_id
START WITH pt.task_id = p_task_id)
-- AND bv.budget_version_id = fe.fin_plan_version_id
-- Part of Changes for FP.M, Tracking Bug No - 3354518 , replace fin_plan_version_id by budget_version_id
AND bv.budget_version_id = fe.budget_version_id
AND (fe.plan_amount_exists_flag = 'Y' OR bv.budget_status_code = 'B')
AND nvl(bv.wp_version_flag,'N') = 'N' -- Added for FP.M, Tracking Bug No - 3354518
UNION ALL
SELECT 1
FROM pa_resource_assignments r,
pa_budget_versions bv
WHERE r.task_id IN
(SELECT pt.task_id
FROM PA_STRUCT_TASK_WBS_V pt
-- pa_tasks pt Commenting out for to replace pa_tasks by PA_STRUCT_TASK_WBS_V as part of FP.M, Tracking Bug No - 3354518
CONNECT BY PRIOR pt.task_id = pt.parent_task_id
START WITH pt.task_id = p_task_id)
AND bv.budget_version_id = r.budget_version_id
AND nvl(bv.wp_version_flag,'N') = 'N' -- Added for FP.M, Tracking Bug No - 3354518
AND (bv.budget_type_code IS NOT NULL
OR
bv.fin_plan_type_id IN (SELECT fpt.fin_plan_type_id
FROM pa_fin_plan_types_b fpt
WHERE fpt.fin_plan_type_code = 'ORG_FORECAST')));
pa_debug.set_curr_function( p_function => 'check_delete_task_ok',
p_debug_mode => l_debug_mode );
/* The cursor delete_task_R_mode_cur shall be used for both restricted as well as unrestructed mode */
/*
IF p_validation_mode = 'U' THEN
OPEN delete_task_U_mode_cur;
FETCH delete_task_U_mode_cur into l_validation_success;
CLOSE delete_task_U_mode_cur;
OPEN delete_task_R_mode_cur;
FETCH delete_task_R_mode_cur into l_validation_success;
CLOSE delete_task_R_mode_cur;
OPEN delete_task_R_mode_cur;
FETCH delete_task_R_mode_cur into l_validation_success;
CLOSE delete_task_R_mode_cur;
pa_debug.g_err_stage:= 'Exiting check_delete_task_ok';
,p_procedure_name => 'check_delete_task_ok'
,p_error_text => x_msg_data);
END check_delete_task_ok;
select 1
from pa_resource_assignments
where task_id = tid;
pa_fin_plan_utils.check_delete_task_ok
(p_task_id => p_task_id,
p_validation_mode => p_validation_mode,
-- x_delete_task_ok_flag => l_delete_task_ok_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Justification: Error message will be added by check_delete_task_ok
PA_UTILS.ADD_MESSAGE
( p_app_short_name => 'PA',
p_msg_name => 'PA_FP_REPARENT_ERR_TASK',
p_token1 => 'TASK_NAME',
p_value1 => l_task_name);
/* UPDATE FROM VEJAYARA:
Validation done for old_parent_task_id is not required. This is because,
we dont expect to have any lines in RA table for parent_task_id when
amounts dont exists for the impacted_Task_id (checked by the call to
check_delete_task_ok). If there are other lowest tasks with amounts, the
old_parent_task_id will have a record in RA table and it is ok to have
that record. Presence of that record is not business violation for reparenting.
-- VALIDATION: Old parent task
open task_ra_csr(p_old_parent_task_id);
select task_name
into l_task_name
from pa_tasks
where task_id = p_task_id;
select task_name
into l_old_parent_task_name
from pa_tasks
where task_id = p_old_parent_task_id;
select top_task_id
into l_parent_top_task_id
from pa_tasks
where task_id = p_new_parent_task_id;
select task_name
into l_task_name
from pa_tasks
where task_id = p_task_id;
select task_name
into l_new_parent_task_name
from pa_tasks
where task_id = p_new_parent_task_id;
SELECT 'Y'
FROM DUAL
/* Changing reference of pa_fp_elements to pa_resource_assignments */
WHERE EXISTS (SELECT 'X' FROM pa_resource_assignments WHERE TASK_ID = P_TASK_ID);
/* WHERE EXISTS (SELECT 'X' FROM PA_FP_ELEMENTS WHERE TASK_ID = P_TASK_ID); */
SELECT sum(l.raw_cost)
, sum(l.burdened_cost)
FROM pa_budget_versions v
, pa_resource_assignments a
, pa_budget_lines l
WHERE v.project_id = p_project_id
AND v.budget_type_code = p_budget_type_code
AND v.current_flag = 'Y'
AND v.budget_version_id = a.budget_version_id
AND a.resource_assignment_id = l.resource_assignment_id;
SELECT raw_cost
, burdened_cost
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND current_flag = 'Y'
AND version_type IN ('COST','ALL');
SELECT sum(l.raw_cost)
, sum(l.burdened_cost)
FROM pa_budget_versions v
, pa_resource_assignments a
, pa_budget_lines l
WHERE v.project_id = p_project_id
AND v.budget_type_code = p_budget_type_code
AND v.current_flag = 'Y'
AND v.budget_version_id = a.budget_version_id
AND a.task_id = p_task_id
AND a.resource_assignment_id = l.resource_assignment_id;
SELECT sum(a.TOTAL_PLAN_RAW_COST)
, sum(a.TOTAL_PLAN_BURDENED_COST)
FROM pa_budget_versions v
, pa_resource_assignments a
WHERE v.project_id = p_project_id
AND v.fin_plan_type_id = p_fin_plan_type_id
AND v.current_flag = 'Y'
AND v.budget_version_id = a.budget_version_id
AND a.task_id = p_task_id
AND a.RESOURCE_ASSIGNMENT_TYPE = 'USER_ENTERED'
AND version_type IN ('COST','ALL');
select edit_after_baseline_flag
into x_editable_flag
from pa_fin_plan_types_b
where fin_plan_type_id = p_fin_plan_type_id;
select 'N'
into x_editable_flag
from dual where exists (
select 1
from pa_budget_versions
where project_id = p_project_id
and fin_plan_type_id = p_fin_plan_type_id
and version_type = p_version_type
and budget_status_code = 'B' );
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_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 gl.period_name
FROM pa_implementations i
, gl_period_statuses gl
WHERE gl.application_id = PA_Period_Process_PKG.Application_ID
AND gl.set_of_books_id = i.set_of_books_id
AND gl.adjustment_period_flag = 'N'
AND closing_status = 'F'
ORDER BY gl.start_date;
SELECT period_name
FROM pa_periods
WHERE status = 'F'
ORDER BY start_date;
SELECT actual_amts_thru_period
,record_version_number
INTO x_actual_amts_thru_period
,x_record_version_number
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT NVL(project_structure_version_id, PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(project_id )) */
SELECT NVL(project_structure_version_id, pa_planning_element_utils.get_fin_struct_id(project_id, budget_version_id) )
INTO l_structure_version_id
FROM pa_budget_versions
WHERE budget_Version_id=p_budget_version_id;
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM PA_RESOURCE_ASSIGNMENTS a, pa_proj_element_versions pelm
WHERE a.budget_version_id = p_budget_version_id
AND a.task_id = pelm.proj_element_id
AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
AND a.task_id = p_task_id
AND a.resource_class_flag = 'Y'
AND pelm.parent_structure_version_id= l_structure_version_id);
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM PA_RESOURCE_ASSIGNMENTS a
WHERE a.budget_version_id = p_budget_version_id
AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
AND a.task_id = p_task_id
AND a.resource_class_flag = 'Y');
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM PA_RESOURCE_ASSIGNMENTS a
WHERE a.budget_version_id = p_budget_version_id
AND a.task_id = p_task_id
--Commented for bug 3793136
--AND a.wbs_element_version_id = p_wbs_element_version_id
AND NOT(a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
and a.resource_class_flag = 'Y') );
To determince if a resource list can be updated for a workplan. If
progress exists for the strucuture or task, its not allowed
irrespective of versioning is enabled or disbled. Else if versioning
is disabled then if task assignments exist then its not allowed.
Rest of the cases it can be changed.
Bug 3619687 Changed the validations such that
1. Check if versioning is enabled
a. if published version exists change is not allowed
b. else allow change
2. If versioning disabled case,
a. if progress exists against project or any of the tasks
change is not allowed
b. else allow change
====================================================================*/
PROCEDURE IS_WP_RL_UPDATEABLE(
p_project_id IN pa_budget_versions.project_id%TYPE
,x_wp_rl_update_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_reason_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,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)AS --File.Sql.39 bug 4440895
--Start of variables used for debugging
l_return_status VARCHAR2(1);
SELECT ppe.proj_element_id
from pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
where ppe.project_id = p_project_id
and ppe.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'WORKPLAN';
SELECT ppev1.proj_element_id
FROM pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
WHERE ppev2.object_type = 'PA_STRUCTURES'
AND ppev2.project_id = p_project_id
AND ppev2.proj_element_id = c_structure_id
AND ppev1.parent_structure_version_id = ppev2.element_version_id
AND ppev1.object_type = 'PA_TASKS';
SELECT proj_element_id
from pa_proj_elements
where project_id = p_project_id
and object_type = 'PA_TASKS'
and proj_element_id IN (
select ppev1.proj_element_id
from pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2
where ppev2.object_type = 'PA_STRUCTURES'
and ppev2.project_id = p_project_id
and ppev2.proj_element_id = c_structure_id
and ppev1.parent_structure_version_id = ppev2.element_version_id);
p_function =>'PA_FIN_PLAN_UTILS.IS_WP_RL_UPDATEABLE'
,p_debug_mode => l_debug_mode );
pa_debug.write('IS_WP_RL_UPDATEABLE: ' || l_module_name,pa_debug.g_err_stage,5);
x_wp_rl_update_allowed_flag := 'N';
x_wp_rl_update_allowed_flag := 'Y';
x_wp_rl_update_allowed_flag := 'N';
x_wp_rl_update_allowed_flag := 'Y';
pa_debug.write('IS_WP_RL_UPDATEABLE: ' || l_module_name,pa_debug.g_err_stage,5);
,p_procedure_name => 'IS_WP_RL_UPDATEABLE');
pa_debug.write('IS_WP_RL_UPDATEABLE: ' || l_module_name,pa_debug.g_err_stage,5);
END IS_WP_RL_UPDATEABLE;
SELECT fin_plan_type_id
INTO x_plan_type_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND nvl(primary_cost_forecast_flag,'N') = 'Y' ;
SELECT fin_plan_type_id
INTO x_plan_type_id
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND nvl(primary_rev_forecast_flag,'N') = 'Y' ;
select 'Y'
into l_wp_resource_list_flag
from dual
where exists (select 'x'
from pa_proj_fp_options a, pa_fin_plan_types_b b
where a.project_id = p_project_id
and a.fin_plan_option_level_code <> 'PROJECT'
and a.fin_plan_type_id = b.fin_plan_type_id
and (a.cost_resource_list_id = p_resource_list_id or
a.revenue_resource_list_id = p_resource_list_id or
a.all_resource_list_id = p_resource_list_id)
and b.use_for_workplan_flag = 'Y');
select 'Y'
into l_fp_resource_list_flag
from dual
where exists (select 'x'
from pa_proj_fp_options a
where a.project_id = p_project_id
and not exists (select 'x'
from pa_fin_plan_types_b b
where a.fin_plan_type_id = b.fin_plan_type_id
and b.use_for_workplan_flag = 'Y')
and (a.cost_resource_list_id = p_resource_list_id or
a.revenue_resource_list_id = p_resource_list_id or
a.all_resource_list_id = p_resource_list_id));
SELECT fin_plan_preference_code
INTO l_plan_pref_code
FROM pa_proj_fp_options
WHERE fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id;
SELECT nvl(pfo.margin_derived_from_code,'B')
INTO l_margin_derived_from_code
FROM pa_proj_fp_options pfo
WHERE pfo.fin_plan_version_id=l_budget_version_id ;
SELECT labor_quantity,
Equipment_quantity,
Total_project_raw_cost,
Total_project_burdened_cost
INTO x_labor_hrs_cost,
x_equipment_hrs_cost,
x_proj_raw_cost,
x_proj_burdened_cost
FROM pa_budget_versions
WHERE budget_version_id = decode(nvl(p_cost_budget_version_id,-1),-1,p_all_budget_version_id,p_cost_budget_version_id);
SELECT labor_quantity,
Equipment_quantity,
Total_project_revenue
INTO x_labor_hrs_rev,
x_equipment_hrs_rev,
x_proj_revenue
FROM pa_budget_versions
WHERE budget_version_id = decode(nvl(p_rev_budget_version_id,-1),-1,p_all_budget_version_id,p_rev_budget_version_id);
select 'Y'
into l_exists
from dual
where exists
(select 1
from pa_budget_lines
where budget_version_id = p_budget_version_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));
FUNCTION check_delete_sch_ok(
p_bill_rate_sch_id IN pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE)
RETURN VARCHAR2 IS
--Start of variables used for debugging
l_debug_mode VARCHAR2(30);
l_delete_ok VARCHAR2(1);
SELECT 'N'
INTO l_delete_ok
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_PROJ_FP_OPTIONS
WHERE RES_CLASS_RAW_COST_SCH_ID = p_bill_rate_sch_id OR
RES_CLASS_BILL_RATE_SCH_ID = p_bill_rate_sch_id OR
COST_EMP_RATE_SCH_ID = p_bill_rate_sch_id OR
COST_JOB_RATE_SCH_ID = p_bill_rate_sch_id OR
COST_NON_LABOR_RES_RATE_SCH_ID = p_bill_rate_sch_id OR
COST_RES_CLASS_RATE_SCH_ID = p_bill_rate_sch_id OR
REV_EMP_RATE_SCH_ID = p_bill_rate_sch_id OR
REV_JOB_RATE_SCH_ID = p_bill_rate_sch_id OR
REV_NON_LABOR_RES_RATE_SCH_ID = p_bill_rate_sch_id OR
REV_RES_CLASS_RATE_SCH_ID = p_bill_rate_sch_id);
l_delete_ok := 'Y';
RETURN l_delete_ok;
END check_delete_sch_ok;
FUNCTION check_delete_burd_sch_ok(
p_ind_rate_sch_id IN pa_ind_rate_schedules_all_bg.ind_rate_sch_id%TYPE)
RETURN VARCHAR2
IS
--Start of variables used for debugging
l_debug_mode VARCHAR2(30);
l_delete_ok VARCHAR2(1);
SELECT 'N'
INTO l_delete_ok
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_PROJ_FP_OPTIONS
WHERE COST_BURDEN_RATE_SCH_ID = p_ind_rate_sch_id);
l_delete_ok := 'Y';
RETURN l_delete_ok;
END check_delete_burd_sch_ok;
SELECT project_currency_code
INTO l_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT 'N'
INTO is_valid_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM pa_budget_versions bv,
pa_budget_lines bl
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND bv.budget_version_id = bl.budget_version_id
AND bl.txn_currency_code <> l_currency_code);
SELECT 'N'
INTO is_valid_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND txn_currency_code <> l_currency_code);
This api is called to check if a txn currency can be deleted for an fp option.
For workplan case,
A txn currency can not be deleted if
1. the currency is project currency or
2. the currency is project functional currency or
3. amounts exist against the currency in any of the workplan versions
For Budgets and Forecasting case,
A txn currency can not be deleted if
1. the currency is project currency or
2. the currency is project functional currency or
3. option is a version and amounts exist against the currency
==============================================================================*/
FUNCTION Check_delete_txn_cur_ok(
p_project_id IN pa_projects_all.project_id%TYPE
,p_context IN VARCHAR2 -- FINPLAN or WORKPLAN
,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_txn_currency_code IN fnd_currencies.currency_code%TYPE
) RETURN VARCHAR2
IS
l_delete_ok_flag varchar2(1);
SELECT project_currency_code
,projfunc_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
p_function =>'PA_FIN_PLAN_UTILS.Check_delete_txn_cur_ok'
,p_debug_mode => P_PA_DEBUG_MODE );
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,5);
p_value1 => 'PA_FIN_PLAN_UTILS.Check_delete_txn_cur_ok');
l_delete_ok_flag := 'Y';
l_delete_ok_flag := 'N';
SELECT 'N' INTO l_delete_ok_flag
FROM DUAL
WHERE EXISTS
( select 1 from pa_budget_lines bl
where bl.budget_version_id = p_fin_plan_version_id
and bl.txn_currency_code = p_txn_currency_code
);
l_delete_ok_flag := 'N';
SELECT 'N' INTO l_delete_ok_flag
FROM DUAL
WHERE EXISTS
( select 1 from pa_budget_versions bv, pa_budget_lines bl
where bv.project_id = p_project_id
and bv.wp_version_flag = 'Y'
and bl.budget_version_id = bv.budget_version_id
and bl.txn_currency_code = p_txn_currency_code
);
pa_debug.g_err_stage:='Exiting Check_delete_txn_cur_ok';
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,3);
RETURN l_delete_ok_flag;
,p_procedure_name => 'Check_delete_txn_cur_ok');
pa_debug.write('Check_delete_txn_cur_ok: ' || l_module_name,pa_debug.g_err_stage,5);
END Check_delete_txn_cur_ok;
SELECT 'Y' INTO l_amounts_exist_flag
FROM dual WHERE EXISTS
(SELECT 1
FROM pa_budget_lines bl,
pa_budget_versions bv
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND bl.budget_version_id = bv.budget_version_id);
SELECT 'Y' INTO l_task_assignments_exist_flag
FROM dual WHERE EXISTS
(SELECT 1
FROM pa_budget_versions bv,
pa_resource_assignments ra
WHERE bv.project_id = p_project_id
AND bv.wp_version_flag = 'Y'
AND ra.budget_version_id = bv.budget_version_id
AND ra.ta_display_flag = 'Y');
SELECT 'Y' INTO l_amounts_exist_flag
FROM dual WHERE EXISTS
(SELECT 1
FROM pa_budget_lines bl,
pa_budget_versions bv
WHERE bv.project_id = p_project_id
AND bv.fin_plan_type_id = p_fin_plan_type_id
AND bl.budget_version_id = bv.budget_version_id);
SELECT plan_processing_code,
request_id
INTO l_plan_processing_code,
l_targ_request_id
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT COUNT(*)
INTO l_no_of_targ_ci_version
FROM pa_budget_versions
WHERE ci_id = p_target_ci_id;
SELECT plan_processing_code,
request_id
INTO l_targ_ci_ver_plan_prc_code,
l_targ_request_id
FROM pa_budget_versions
WHERE ci_id = p_target_ci_id;
SELECT plan_processing_code,
request_id
INTO l_targ_cost_ci_ver_plan_prc_cd,
l_targ_cost_ci_req_id
FROM pa_budget_versions
WHERE ci_id = p_target_ci_id
AND version_type = 'COST';
SELECT plan_processing_code,
request_id
INTO l_targ_rev_ci_ver_plan_prc_cd,
l_targ_rev_ci_req_id
FROM pa_budget_versions
WHERE ci_id = p_target_ci_id
AND version_type = 'REVENUE';
SELECT 1 INTO l_exists FROM dual WHERE EXISTS (SELECT fin_plan_type_id
FROM PA_PROJ_FP_OPTIONS
WHERE Project_id = p_project_id AND
(GEN_SRC_REV_PLAN_TYPE_ID = p_fin_plan_type_id
OR GEN_SRC_COST_PLAN_TYPE_ID= p_fin_plan_type_id
OR GEN_SRC_ALL_PLAN_TYPE_ID = p_fin_plan_type_id));
SELECT project_id
INTO l_project_id
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
* and uses it to read and return to avoid select every time for each row
* in the excel download view query
*/
FUNCTION get_cached_time_phased_code (bv_id IN pa_budget_versions.budget_version_id%TYPE)
RETURN VARCHAR2
IS
l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
select baseline_funding_flag
into l_baseline_funding_flag
from pa_projects_all
where project_id = p_pa_project_id;
select description
into l_description
from pa_budget_lines
where resource_assignment_id = p_resource_assignment_id
and txn_currency_code = p_txn_currency_code;
select meaning
into G_Chg_Reason
from pa_lookups
where lookup_type = 'BUDGET CHANGE REASON'
and lookup_code = 'MULTIPLE';
select change_reason_code
into l_chg_rsn_code
from pa_budget_lines
where resource_assignment_id = p_resource_assignment_id
and txn_currency_code = p_txn_currency_code;
select meaning
into l_chg_rsn
from pa_lookups
where lookup_type = 'BUDGET CHANGE REASON'
and lookup_code = l_chg_rsn_code;
select pr.copy_etc_from_plan_flag
into l_copy_etc_from_plan_flag
from pa_budget_versions bu, pa_proj_fp_options pr
where bu.budget_version_id = pr.fin_plan_version_id and
bu.budget_version_id = p_budget_version_id ;
select copy_etc_from_plan_flag
into l_copy_etc_from_plan_flag
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 = p_fin_plan_option_code;