[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM cn_calc_formulas
WHERE name = p_name
AND (p_calc_formula_id IS NULL OR p_calc_formula_id <> calc_formula_id)
--R12 MOAC Changes--Start
AND org_id = p_org_id;
CURSOR get_sql_select is
select dbms_lob.substr(sql_select)
from cn_calc_sql_exps
where calc_sql_exp_id = p_calc_sql_exp_id;
OPEN get_sql_select;
FETCH get_sql_select INTO l_sql;
CLOSE get_sql_select;
select s.number_dim, d.dim_unit_code
from cn_rt_formula_asgns a, cn_rate_schedules s,
cn_rate_sch_dims r, cn_rate_dimensions d
where a.rate_schedule_id = s.rate_schedule_id
and s.rate_schedule_id = r.rate_schedule_id
and r.rate_dimension_id = d.rate_dimension_id
and a.calc_formula_id = p_calc_formula_id;
select calc_sql_exp_id, f_calc_sql_exp_id
from cn_formula_inputs where calc_formula_id = p_calc_formula_id;
cn_calc_formulas_pkg.insert_row
(x_calc_formula_id => x_calc_formula_id,
x_name => p_name,
x_description => p_description,
x_formula_type => p_formula_type,
x_trx_group_code => p_trx_group_code,
x_number_dim => p_number_dim,
x_cumulative_flag => p_cumulative_flag,
x_itd_flag => p_itd_flag,
x_split_flag => p_split_flag,
x_threshold_all_tier_flag => p_threshold_all_tier_flag,
x_modeling_flag => p_modeling_flag,
x_perf_measure_id => p_perf_measure_id,
x_output_exp_id => p_output_exp_id,
x_f_output_exp_id => p_f_output_exp_id,
--R12 MOAC Changes--Start
x_org_id => p_org_id
--R12 MOAC Changes--End
);
select name into l_formula_name
from cn_calc_formulas
where calc_formula_id = x_calc_formula_id;
select name INTO l_output_exp_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_output_exp_id;
select name INTO l_f_output_exp_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_f_output_exp_id;
select name INTO l_perf_measure_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_perf_measure_id;
cn_formula_inputs_pkg.insert_row
(x_formula_input_id => l_temp_id,
x_calc_formula_id => x_calc_formula_id,
x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
x_cumulative_flag => p_input_tbl(i).cumulative_flag,
x_split_flag => p_input_tbl(i).split_flag,
--R12 MOAC Changes--Start
x_org_id => p_org_id
--R12 MOAC Changes--End
);
cn_rt_formula_asgns_pkg.insert_row
(x_rt_formula_asgn_id => l_temp_id,
x_calc_formula_id => x_calc_formula_id,
x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
x_start_date => p_rt_assign_tbl(i).start_date,
x_end_date => p_rt_assign_tbl(i).end_date,
--R12 MOAC Changes--Start
x_org_id => p_org_id
--R12 MOAC Changes--End
);
PROCEDURE Update_Formula
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_generate_packages IN VARCHAR2 := FND_API.G_TRUE ,
p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
p_name IN CN_CALC_FORMULAS.NAME%TYPE,
p_description IN CN_CALC_FORMULAS.DESCRIPTION%TYPE
:= null,
p_formula_type IN CN_CALC_FORMULAS.FORMULA_TYPE%TYPE,
p_formula_status IN CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
p_trx_group_code IN CN_CALC_FORMULAS.TRX_GROUP_CODE%TYPE,
p_number_dim IN CN_CALC_FORMULAS.NUMBER_DIM%TYPE,
p_cumulative_flag IN CN_CALC_FORMULAS.CUMULATIVE_FLAG%TYPE,
p_itd_flag IN CN_CALC_FORMULAS.ITD_FLAG%TYPE,
p_split_flag IN CN_CALC_FORMULAS.SPLIT_FLAG%TYPE,
p_threshold_all_tier_flag IN CN_CALC_FORMULAS.THRESHOLD_ALL_TIER_FLAG%TYPE,
p_modeling_flag IN CN_CALC_FORMULAS.MODELING_FLAG%TYPE,
p_perf_measure_id IN CN_CALC_FORMULAS.PERF_MEASURE_ID%TYPE,
p_output_exp_id IN CN_CALC_FORMULAS.OUTPUT_EXP_ID%TYPE,
p_f_output_exp_id IN CN_CALC_FORMULAS.F_OUTPUT_EXP_ID%TYPE := NULL,
p_input_tbl IN input_tbl_type := g_miss_input_tbl,
p_rt_assign_tbl IN rt_assign_tbl_type := g_miss_rt_assign_tbl,
--R12 MOAC Changes--Start
p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --new
p_object_version_number IN OUT NOCOPY CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --Changed
--R12 MOAC Changes--End
x_formula_status OUT NOCOPY CN_CALC_FORMULAS.FORMULA_STATUS%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Formula';
l_update_count NUMBER := 0;
SELECT cf.calc_formula_id, cf.NAME, cf.FORMULA_TYPE, cf.TRX_GROUP_CODE,
cf.SPLIT_FLAG, cf.CUMULATIVE_FLAG, cf.ITD_FLAG, cf.MODELING_FLAG, cl.MEANING,
cf.output_exp_id, cf.f_output_exp_id, cf.perf_measure_id, cs1.name oname,
cs2.name fname, cs3.name pname
FROM cn_calc_formulas cf, cn_lookups cl,
cn_calc_sql_exps cs1, cn_calc_sql_exps cs2, cn_calc_sql_exps cs3
WHERE cl.lookup_code = cf.FORMULA_TYPE
AND cl.lookup_type = 'FORMULA_TYPE'
AND cf.OUTPUT_EXP_ID = cs1.CALC_SQL_EXP_ID (+)
AND cf.F_OUTPUT_EXP_ID = cs2.CALC_SQL_EXP_ID (+)
AND cf.PERF_MEASURE_ID = cs3.CALC_SQL_EXP_ID (+)
AND calc_formula_id = l_calc_formula_id;
SELECT cn_srp_period_quotas_ext_s.NEXTVAL
FROM dual;
select spq.srp_period_quota_id
from cn_quotas_v qut, cn_srp_period_quotas spq
where qut.quota_id = spq.quota_id
and qut.calc_formula_id = p_calc_formula_id;
SELECT
formula_type,
trx_group_code,
number_dim,
cumulative_flag,
itd_flag,
split_flag,
threshold_all_tier_flag,
modeling_flag,
perf_measure_id,
output_exp_id,
f_output_exp_id,
formula_status
FROM cn_calc_formulas
WHERE calc_formula_id = p_calc_formula_id;
select distinct qa.comp_plan_id from cn_quota_assigns qa, cn_quotas_v q
where qa.quota_id = q.quota_id
and q.calc_formula_id = p_calc_formula_id;
SAVEPOINT Update_Formula;
cn_formula_inputs_pkg.insert_row
(x_formula_input_id => l_temp_id,
x_calc_formula_id => p_calc_formula_id,
x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
x_cumulative_flag => p_input_tbl(i).cumulative_flag,
x_split_flag => p_input_tbl(i).split_flag,
--R12 MOAC Changes--Start
x_org_id => p_org_id
--R12 MOAC Changes--End
);
l_update_count := l_update_count + 1;
cn_formula_inputs_pkg.delete_row(p_input_tbl(i).formula_input_id);
cn_formula_inputs_pkg.update_row
(x_formula_input_id => p_input_tbl(i).formula_input_id,
x_calc_formula_id => p_calc_formula_id,
x_calc_sql_exp_id => p_input_tbl(i).calc_sql_exp_id,
x_f_calc_sql_exp_id => p_input_tbl(i).f_calc_sql_exp_id,
x_rate_dim_sequence => p_input_tbl(i).rate_dim_sequence,
x_cumulative_flag => p_input_tbl(i).cumulative_flag,
x_split_flag => p_input_tbl(i).split_flag,
x_object_version_number => p_input_tbl(i).object_version_number);
l_update_count := l_update_count + 1;
delete from cn_srp_period_quotas_ext where srp_period_quota_id = form_rec.srp_period_quota_id;
FOR i in 2.. l_update_count LOOP
OPEN c_next_srp_qut_id;
insert into cn_srp_period_quotas_ext
(srp_period_quota_ext_id,
srp_period_quota_id,
input_sequence,
input_achieved_ptd,
input_achieved_itd
) values
(l_srp_prd_quota_ext_id,
form_rec.srp_period_quota_id,
i,
null,
null);
cn_calc_formulas_pkg.update_row
(x_calc_formula_id => p_calc_formula_id,
x_name => p_name,
x_description => p_description,
x_formula_status => x_formula_status,
x_formula_type => p_formula_type,
x_trx_group_code => p_trx_group_code,
x_number_dim => l_num_dim,
x_cumulative_flag => p_cumulative_flag,
x_itd_flag => p_itd_flag,
x_split_flag => p_split_flag,
x_threshold_all_tier_flag => p_threshold_all_tier_flag,
x_modeling_flag => p_modeling_flag,
x_perf_measure_id => p_perf_measure_id,
x_output_exp_id => p_output_exp_id,
x_f_output_exp_id => p_f_output_exp_id,
x_object_version_number => p_object_version_number);
SELECT meaning into l_meaning
FROM cn_calc_formulas cf, cn_lookups cl
WHERE cl.lookup_code = cf.FORMULA_TYPE
AND cl.lookup_type = 'FORMULA_TYPE'
AND calc_formula_id = p_calc_formula_id;
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_TYPE_UPDATE');
SELECT meaning into l_new_meaning
FROM cn_lookups
WHERE lookup_type = 'SPLIT_FLAG'
AND lookup_code = p_split_flag;
SELECT meaning into l_old_meaning
FROM cn_lookups
WHERE lookup_type = 'SPLIT_FLAG'
AND lookup_code = l_old_rec.split_flag;
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ACC1_UPDATE');
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ACC2_UPDATE');
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ITD1_UPDATE');
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_ITD2_UPDATE');
select name INTO l_output_exp_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_output_exp_id;
select name INTO l_f_output_exp_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_f_output_exp_id;
select name INTO l_perf_measure_name
from cn_calc_sql_exps
where calc_sql_exp_id = p_perf_measure_id;
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_UPDATE');
fnd_message.set_name('CN', 'CNR12_NOTE_FORMULA_PERF_DELETE');
update cn_formula_inputs set cumulative_flag = 'N' where calc_formula_id = p_calc_formula_id;
update cn_formula_inputs set split_flag = 'N' where calc_formula_id = p_calc_formula_id;
update cn_formula_inputs set split_flag = p_split_flag
where calc_formula_id = p_calc_formula_id and split_flag <> 'N' ;
SELECT count(*)
INTO l_count_formula_input
FROM cn_formula_inputs
WHERE calc_formula_id = p_calc_formula_id ;
-- update the split flag to 'Y'
IF (l_count_formula_input = 1) THEN
update cn_formula_inputs set split_flag = p_split_flag
where calc_formula_id = p_calc_formula_id ;
cn_rt_formula_asgns_pkg.insert_row
(x_rt_formula_asgn_id => l_temp_id,
x_calc_formula_id => p_calc_formula_id,
x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
x_start_date => p_rt_assign_tbl(i).start_date,
x_end_date => p_rt_assign_tbl(i).end_date,
--R12 MOAC Changes--Start
x_org_id => p_org_id
--R12 MOAC Changes--End
);
cn_rt_formula_asgns_pkg.delete_row
(p_rt_assign_tbl(i).rt_formula_asgn_id);
cn_rt_formula_asgns_pkg.update_row
(x_rt_formula_asgn_id => p_rt_assign_tbl(i).rt_formula_asgn_id,
x_calc_formula_id => p_calc_formula_id,
x_rate_schedule_id => p_rt_assign_tbl(i).rate_schedule_id,
x_start_date => p_rt_assign_tbl(i).start_date,
x_end_date => p_rt_assign_tbl(i).end_date,
x_object_version_number => p_rt_assign_tbl(i).object_version_number);
ROLLBACK TO Update_Formula;
ROLLBACK TO Update_Formula;
ROLLBACK TO Update_Formula;
END Update_Formula;
PROCEDURE Delete_Formula
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_calc_formula_id IN CN_CALC_FORMULAS.CALC_FORMULA_ID%TYPE,
p_org_id IN CN_CALC_FORMULAS.ORG_ID%TYPE, --SFP related change
--R12 MOAC Changes--Start
p_object_version_number IN CN_CALC_FORMULAS.OBJECT_VERSION_NUMBER%TYPE, --new
--R12 MOAC Changes--End
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Formula';
SELECT 1
FROM dual
WHERE exists (SELECT 1 FROM cn_calc_edges
WHERE child_id = p_calc_formula_id
AND edge_type = 'FE');
SAVEPOINT Delete_Formula;
select count(1) into l_dummy
from cn_quotas_v where calc_formula_id = p_calc_formula_id;
select count(1) into l_dummy
from cn_calc_formulas f, cn_role_quota_cates r
where f.calc_formula_id = p_calc_formula_id
and f.calc_formula_id = r.calc_formula_id
and f.modeling_flag = 'Y';
SELECT name INTO l_formula_name
FROM cn_calc_formulas
WHERE calc_formula_id = p_calc_formula_id
AND org_id = p_org_id;
cn_calc_formulas_pkg.delete_row(p_calc_formula_id, p_org_id);
fnd_message.set_name ('CN', 'CNR12_NOTE_FORMULA_DELETE');
p_source_object_code => 'CN_DELETED_OBJECTS',
p_notes => l_note_msg,
p_notes_detail => l_note_msg,
p_note_type => 'CN_SYSGEN', -- for system generated
x_jtf_note_id => l_note_id -- returned
);
DELETE FROM cn_formula_inputs WHERE calc_formula_id = p_calc_formula_id AND org_id = p_org_id;
DELETE FROM cn_rt_formula_asgns WHERE calc_formula_id = p_calc_formula_id AND org_id = p_org_id;
ROLLBACK TO Delete_Formula;
ROLLBACK TO Delete_Formula;
ROLLBACK TO Delete_Formula;
END Delete_Formula;
SELECT
formula_type,
trx_group_code,
number_dim,
itd_flag,
perf_measure_id,
output_exp_id,
f_output_exp_id
FROM cn_calc_formulas
WHERE calc_formula_id = p_calc_formula_id;
SELECT
split_flag
FROM cn_calc_formulas
WHERE calc_formula_id = p_calc_formula_id;
SELECT status, exp_type_code
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = l_perf_measure_id;
SELECT a.status, a.exp_type_code, b.status f_status,
b.exp_type_code f_exp_type_code
FROM cn_calc_sql_exps a,
cn_calc_sql_exps b,
cn_formula_inputs c
WHERE a.calc_sql_exp_id = c.calc_sql_exp_id
AND b.calc_sql_exp_id(+) = c.f_calc_sql_exp_id
AND c.calc_formula_id = p_calc_formula_id;
SELECT status, exp_type_code
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = l_output_exp_id;
SELECT status, exp_type_code
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = l_f_output_exp_id;
SELECT 1
FROM cn_rate_schedules
WHERE number_dim <> l_number_dim
AND rate_schedule_id IN (SELECT rate_schedule_id
FROM cn_rt_formula_asgns
WHERE calc_formula_id = p_calc_formula_id);
SELECT count(*)
INTO l_count_formula_input
FROM cn_formula_inputs
WHERE calc_formula_id = p_calc_formula_id
AND split_flag = 'Y';
select name into
l_name
from cn_calc_formulas
where calc_formula_id = p_calc_formula_id;