The following lines contain the word 'select', 'insert', 'update' or 'delete':
select RATE_SCHEDULE_ID into l_rate_schedule_id
from CN_RATE_SCHEDULES
where name = p_name;
select RATE_DIMENSION_ID into l_rate_dimension_id
from CN_RATE_DIMENSIONS
where name = p_name;
select rate_sch_dim_id into l_rate_sch_dim_id
from cn_rate_sch_dims
where rate_schedule_id = p_rate_schedule_id
and rate_dimension_id = p_rate_dimension_id;
select CALC_SQL_EXP_ID into l_calc_sql_exp_id
from CN_CALC_SQL_EXPS
where name = p_name;
select rate_dim_tier_id into l_rate_dim_tier_id
from cn_rate_dim_tiers
where rate_dimension_id = p_rate_dimension_id
and tier_sequence = p_tier_sequence;
select rdt.rate_dim_tier_id into l_rate_dim_tier_id
from cn_rate_sch_dims rsd, cn_rate_dim_tiers rdt
where rsd.rate_schedule_id = p_rate_schedule_id
and rsd.rate_dim_sequence = p_rate_dim_sequence
and rdt.rate_dimension_id = rsd.rate_dimension_id
and rdt.tier_sequence = p_tier_sequence;
PROCEDURE Update_Schedule
(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_original_name IN CN_RATE_SCHEDULES.NAME%TYPE,
p_new_name IN CN_RATE_SCHEDULES.NAME%TYPE :=
cn_api.g_miss_char,
p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE :=
cn_api.g_miss_char,
p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE,
p_dims_tbl IN dim_assign_tbl_type := g_miss_dim_assign_tbl,
-- Start - MOAC Change
p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE,
-- End - MOAC Change
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_Schedule';
SAVEPOINT Update_Schedule;
SELECT org_id INTO l_org_id
FROM cn_rate_schedules
WHERE rate_schedule_id = l_rate_schedule_id;
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_multi_rate_schedule_pub.update_schedule.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
select commission_unit_code into l_original_comm_unit_code
from cn_rate_schedules
where rate_schedule_id = l_rate_schedule_id;
select decode(p_new_name, cn_api.g_miss_char, p_original_name, p_new_name),
decode(p_commission_unit_code, cn_api.g_miss_char,
l_original_comm_unit_code, p_commission_unit_code)
into l_name, l_comm_unit_code from dual;
'UPDATE_SCHEDULE', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_SCHEDULE_PRE
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_commission_unit_code => p_commission_unit_code,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_SCHEDULE', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_SCHEDULE_PRE
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_commission_unit_code => p_commission_unit_code,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_MULTI_RATE_SCHEDULES_PVT.Update_Schedule
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_schedule_id => l_rate_schedule_id,
p_name => l_name,
p_commission_unit_code => l_comm_unit_code,
p_number_dim => null, -- not used
-- Start - R12 MOAC Changes
p_org_id => p_org_id,
-- End - R12 MOAC Changes
p_object_version_number => p_object_version_number,
p_dims_tbl => l_dims_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_SCHEDULE', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_SCHEDULE_POST
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_commission_unit_code => p_commission_unit_code,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_SCHEDULE', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_SCHEDULE_POST
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_commission_unit_code => p_commission_unit_code,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Schedule;
ROLLBACK TO Update_Schedule;
ROLLBACK TO Update_Schedule;
END Update_Schedule;
PROCEDURE Delete_Schedule
(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_name IN CN_RATE_SCHEDULES.NAME%TYPE,
-- Start - R12 MOAC Changes
p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
-- End - R12 MOAC Changes
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE := 0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Schedule';
SAVEPOINT Delete_Schedule;
'DELETE_SCHEDULE', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_SCHEDULE_PRE
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_SCHEDULE', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_SCHEDULE_PRE
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_MULTI_RATE_SCHEDULES_PVT.Delete_Schedule
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_schedule_id => l_rate_schedule_id,
-- Start - R12 MOAC Changes
p_object_version_number => p_object_version_number,
-- End - R12 MOAC Changes
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_SCHEDULE', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_SCHEDULE_POST
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_SCHEDULE', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_SCHEDULE_POST
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Delete_Schedule;
ROLLBACK TO Delete_Schedule;
ROLLBACK TO Delete_Schedule;
END Delete_Schedule;
PROCEDURE Update_Dimension_Assign
(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_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
p_orig_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
p_new_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE := cn_api.g_miss_char,
p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE :=
cn_api.g_miss_num,
p_object_version_number IN OUT NOCOPY CN_RATE_SCH_DIMS.OBJECT_VERSION_NUMBER%TYPE,
-- Start - MOAC Change
p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE,
-- End - MOAC Change
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_Dimension_Assign';
SAVEPOINT Update_Dimension_Assign;
SELECT org_id INTO l_org_id
FROM cn_rate_sch_dims
WHERE rate_sch_dim_id = l_rate_sch_dim_id;
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_multi_rate_schedule_pub.update_dimension_assign.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
select rate_dim_sequence into l_rate_dim_sequence
from cn_rate_sch_dims
where rate_sch_dim_id = l_rate_sch_dim_id;
'UPDATE_DIMENSION_ASSIGN', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_ASSIGN_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_orig_rate_dim_name => p_orig_rate_dim_name,
p_new_rate_dim_name => p_new_rate_dim_name,
p_rate_dim_sequence => p_rate_dim_sequence,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION_ASSIGN', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_ASSIGN_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_orig_rate_dim_name => p_orig_rate_dim_name,
p_new_rate_dim_name => p_new_rate_dim_name,
p_rate_dim_sequence => p_rate_dim_sequence,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_MULTI_RATE_SCHEDULES_PVT.update_dimension_assign
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_sch_dim_id => l_rate_sch_dim_id,
p_rate_schedule_id => l_rate_schedule_id,
p_rate_dimension_id => l_rate_dimension_id,
p_rate_dim_sequence => l_rate_dim_sequence,
-- Start - MOAC Change
p_org_id => p_org_id,
-- End - MOAC Change
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION_ASSIGN', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_ASSIGN_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_orig_rate_dim_name => p_orig_rate_dim_name,
p_new_rate_dim_name => p_new_rate_dim_name,
p_rate_dim_sequence => p_rate_dim_sequence,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION_ASSIGN', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_ASSIGN_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_orig_rate_dim_name => p_orig_rate_dim_name,
p_new_rate_dim_name => p_new_rate_dim_name,
p_rate_dim_sequence => p_rate_dim_sequence,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Dimension_Assign;
ROLLBACK TO Update_Dimension_Assign;
ROLLBACK TO Update_Dimension_Assign;
END Update_Dimension_Assign;
PROCEDURE Delete_Dimension_Assign
(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_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
-- Start - R12 MOAC Changes
p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, -- new
-- End - R12 MOAC Changes
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_Dimension_Assign';
SAVEPOINT Delete_Dimension_Assign;
'DELETE_DIMENSION_ASSIGN', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_ASSIGN_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_rate_dimension_name => p_rate_dimension_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION_ASSIGN', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_ASSIGN_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_rate_dimension_name => p_rate_dimension_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_MULTI_RATE_SCHEDULES_PVT.delete_dimension_assign
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_sch_dim_id => l_rate_sch_dim_id,
p_rate_schedule_id => l_rate_schedule_id,
-- Start - R12 MOAC Changes
p_object_version_number => p_object_version_number,
-- End - R12 MOAC Changes
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION_ASSIGN', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_ASSIGN_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_rate_dimension_name => p_rate_dimension_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION_ASSIGN', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_ASSIGN_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_rate_dimension_name => p_rate_dimension_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Delete_Dimension_Assign;
ROLLBACK TO Delete_Dimension_Assign;
ROLLBACK TO Delete_Dimension_Assign;
END Delete_Dimension_Assign;
PROCEDURE Update_Rate
(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_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
p_tier_coordinates_tbl IN tier_coordinates_tbl,
p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
-- Start - MOAC Change
p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE,
-- End - MOAC Change
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_Rate';
SAVEPOINT Update_Rate;
SELECT org_id INTO l_org_id
FROM cn_rate_schedules
WHERE rate_schedule_id = l_rate_schedule_id;
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_multi_rate_schedule_pub.update_rate.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'UPDATE_RATE', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_RATE_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_tier_coordinates_tbl => p_tier_coordinates_tbl,
p_commission_amount => p_commission_amount,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_RATE', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_RATE_PRE
(p_rate_schedule_name => p_rate_schedule_name,
p_tier_coordinates_tbl => p_tier_coordinates_tbl,
p_commission_amount => p_commission_amount,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_MULTI_RATE_SCHEDULES_PVT.update_rate
(p_rate_schedule_id => l_rate_schedule_id,
p_rate_sequence => l_rate_sequence,
p_commission_amount => p_commission_amount,
p_object_version_number => p_object_version_number,
-- Start - MOAC Change
p_org_id => p_org_id);
'UPDATE_RATE', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_RATE_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_tier_coordinates_tbl => p_tier_coordinates_tbl,
p_commission_amount => p_commission_amount,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_RATE', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_RATE_POST
(p_rate_schedule_name => p_rate_schedule_name,
p_tier_coordinates_tbl => p_tier_coordinates_tbl,
p_commission_amount => p_commission_amount,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Rate;
ROLLBACK TO Update_Rate;
ROLLBACK TO Update_Rate;
END Update_Rate;
PROCEDURE Update_Dimension
(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_original_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
p_new_name IN CN_RATE_DIMENSIONS.NAME%TYPE :=
cn_api.g_miss_char,
p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE :=
cn_api.g_miss_char,
p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE :=
cn_api.g_miss_char,
p_tiers_tbl IN rate_tier_tbl_type :=
g_miss_rate_tier_tbl,
p_object_version_number IN OUT NOCOPY CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
-- Start - MOAC Change
p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE,
-- End - MOAC Change
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_Dimension';
select rate_dim_tier_id from cn_rate_dim_tiers
where rate_dimension_id = p_rate_dimension_id
and tier_sequence = p_tier_sequence;
SAVEPOINT Update_Dimension;
SELECT org_id INTO l_org_id
FROM cn_rate_dimensions
WHERE rate_dimension_id = l_rate_dimension_id;
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_multi_rate_schedule_pub.update_dimenstion.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
select description into l_description from cn_rate_dimensions
where rate_dimension_id = l_rate_dimension_id;
select decode(p_new_name, cn_api.g_miss_char, p_original_name, p_new_name),
decode(p_description, cn_api.g_miss_char, l_description, p_description)
into l_name, l_description from dual;
'UPDATE_DIMENSION', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_PRE
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_description => p_description,
p_dim_unit_code => p_dim_unit_code,
p_tiers_tbl => p_tiers_tbl,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_PRE
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_description => p_description,
p_dim_unit_code => p_dim_unit_code,
p_tiers_tbl => p_tiers_tbl,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_RATE_DIMENSIONS_PVT.Update_Dimension
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_dimension_id => l_rate_dimension_id,
p_name => l_name,
p_description => l_description,
p_dim_unit_code => p_dim_unit_code,
p_number_tier => l_tiers_tbl.count,
p_tiers_tbl => l_tiers_tbl,
-- Start - MOAC Change
p_org_id => p_org_id,
-- End - MOAC Change
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_DIMENSION_POST
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_description => p_description,
p_dim_unit_code => p_dim_unit_code,
p_tiers_tbl => p_tiers_tbl,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_DIMENSION', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_DIMENSION_POST
(p_original_name => p_original_name,
p_new_name => p_new_name,
p_description => p_description,
p_dim_unit_code => p_dim_unit_code,
p_tiers_tbl => p_tiers_tbl,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Dimension;
ROLLBACK TO Update_Dimension;
ROLLBACK TO Update_Dimension;
END Update_Dimension;
PROCEDURE Delete_Dimension
(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_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
-- Start - R12 MOAC Changes
p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, -- new
-- End - R12 MOAC Changes
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_Dimension';
SAVEPOINT Delete_Dimension;
'DELETE_DIMENSION', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_PRE
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_PRE
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_RATE_DIMENSIONS_PVT.Delete_Dimension
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_dimension_id => l_rate_dimension_id,
-- Start - MOAC Change
p_object_version_number => p_object_version_number,
-- End - MOAC Change
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_DIMENSION_POST
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_DIMENSION', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_DIMENSION_POST
(p_name => p_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Delete_Dimension;
ROLLBACK TO Delete_Dimension;
ROLLBACK TO Delete_Dimension;
END Delete_Dimension;
select dim_unit_code into l_dim_unit_code from cn_rate_dimensions
where rate_dimension_id = l_rate_dimension_id;
PROCEDURE Update_Tier
(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_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
p_value1 IN VARCHAR2,
p_value2 IN VARCHAR2,
p_object_version_number IN OUT NOCOPY CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
-- Start - MOAC Change
p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE,
-- End - MOAC Change
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_Tier';
SAVEPOINT Update_tier;
SELECT org_id INTO l_org_id
FROM cn_rate_dim_tiers
WHERE rate_dim_tier_id = l_rate_dim_tier_id;
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_multi_rate_schedule_pub.update_tier.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
select dim_unit_code into l_dim_unit_code from cn_rate_dimensions
where rate_dimension_id = l_rate_dimension_id;
'UPDATE_TIER', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_TIER_PRE
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
p_value1 => p_value1,
p_value2 => p_value2,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_TIER', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_TIER_PRE
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
p_value1 => p_value1,
p_value2 => p_value2,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_RATE_DIMENSIONS_PVT.update_tier
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_dim_tier_id => l_rate_dim_tier_id,
p_rate_dimension_id => l_rate_dimension_id,
p_dim_unit_code => l_dim_unit_code,
p_minimum_amount => l_minimum_amount,
p_maximum_amount => l_maximum_amount,
p_min_exp_id => l_min_exp_id,
p_max_exp_id => l_max_exp_id,
p_string_value => l_string_value,
p_tier_sequence => p_tier_sequence,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_TIER', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.UPDATE_TIER_POST
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
p_value1 => p_value1,
p_value2 => p_value2,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'UPDATE_TIER', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.UPDATE_TIER_POST
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
p_value1 => p_value1,
p_value2 => p_value2,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_tier;
ROLLBACK TO Update_tier;
ROLLBACK TO Update_tier;
END Update_tier;
PROCEDURE Delete_Tier
(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_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
-- Start - R12 MOAC Changes
p_object_version_number IN CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- new
-- End - R12 MOAC Changes
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_Tier';
SAVEPOINT Delete_Tier;
'DELETE_TIER', 'B', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_TIER_PRE
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_TIER', 'B', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_TIER_PRE
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CN_RATE_DIMENSIONS_PVT.delete_tier
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_rate_dim_tier_id => l_rate_dim_tier_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_TIER', 'A', 'V') then
CN_MULTI_RATE_SCHEDULES_VUHK.DELETE_TIER_POST
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'DELETE_TIER', 'A', 'C') then
CN_MULTI_RATE_SCHEDULES_CUHK.DELETE_TIER_POST
(p_dimension_name => p_dimension_name,
p_tier_sequence => p_tier_sequence,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Delete_Tier;
ROLLBACK TO Delete_Tier;
ROLLBACK TO Delete_Tier;
END Delete_Tier;