[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_formula_rate_tables (
p_quota_id IN NUMBER,
p_calc_formula_id IN NUMBER,
p_rate_tables IN OUT NOCOPY rt_quota_asgn_tbl_type
)
IS
CURSOR rate_formula_date_curs
IS
SELECT rf.start_date,
rf.end_date,
rf.rate_schedule_id,
rs.NAME,
fml.NAME calc_formula_name
FROM cn_rt_formula_asgns rf,
cn_rate_schedules_all rs,
cn_calc_formulas fml
WHERE fml.calc_formula_id = p_calc_formula_id AND rf.rate_schedule_id = rs.rate_schedule_id AND fml.calc_formula_id = rf.calc_formula_id;
SELECT start_date,
end_date,
org_id
INTO l_quota_start_date,
l_quota_end_date,
l_org_id
FROM cn_quotas_all
WHERE quota_id = p_quota_id;
SELECT cn_rt_quota_asgns_s.NEXTVAL,
p_calc_formula_id,
p_quota_id,
l_start_date,
l_end_date,
rt_date.rate_schedule_id,
rt_date.NAME,
rt_date.calc_formula_name,
l_org_id
INTO l_rec.rt_quota_asgn_id,
l_rec.calc_formula_id,
l_rec.quota_id,
l_rec.start_date,
l_rec.end_date,
l_rec.rate_schedule_id,
l_rec.NAME,
l_rec.calc_formula_name,
l_rec.org_id
FROM DUAL;
END insert_formula_rate_tables;
SELECT NAME INTO l_temp_old
FROM CN_RATE_SCHEDULES
WHERE RATE_SCHEDULE_ID = p_rt_quota_asgn.rate_schedule_id
AND ORG_ID = p_rt_quota_asgn.org_id;
IF (p_operation = 'delete') THEN
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_RT_ASGN_DEL');
IF (p_operation = 'update') THEN
SELECT NAME INTO l_old_rt_name
FROM CN_RATE_SCHEDULES
WHERE RATE_SCHEDULE_ID = p_old_rt_quota_asgn.rate_schedule_id
AND ORG_ID = p_old_rt_quota_asgn.org_id;
p_last_update_login NUMBER;
p_last_update_date DATE;
p_last_updated_by NUMBER;
SELECT NULL,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
1
INTO p_rt_quota_asgn.rt_quota_asgn_id,
p_rt_quota_asgn.created_by,
p_rt_quota_asgn.creation_date,
p_rt_quota_asgn.last_update_login,
p_rt_quota_asgn.last_update_date,
p_rt_quota_asgn.last_updated_by,
p_rt_quota_asgn.object_version_number
FROM DUAL;
x_operation => 'INSERT',
x_rowid => g_rowid,
x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
x_calc_formula_id => p_rt_quota_asgn.calc_formula_id,
x_quota_id => p_rt_quota_asgn.quota_id,
x_start_date => p_rt_quota_asgn.start_date,
x_end_date => p_rt_quota_asgn.end_date,
x_rate_schedule_id => p_rt_quota_asgn.rate_schedule_id,
x_attribute_category => p_rt_quota_asgn.attribute_category,
x_attribute1 => p_rt_quota_asgn.attribute1,
x_attribute2 => p_rt_quota_asgn.attribute2,
x_attribute3 => p_rt_quota_asgn.attribute3,
x_attribute4 => p_rt_quota_asgn.attribute4,
x_attribute5 => p_rt_quota_asgn.attribute5,
x_attribute6 => p_rt_quota_asgn.attribute6,
x_attribute7 => p_rt_quota_asgn.attribute7,
x_attribute8 => p_rt_quota_asgn.attribute8,
x_attribute9 => p_rt_quota_asgn.attribute9,
x_attribute10 => p_rt_quota_asgn.attribute10,
x_attribute11 => p_rt_quota_asgn.attribute11,
x_attribute12 => p_rt_quota_asgn.attribute12,
x_attribute13 => p_rt_quota_asgn.attribute13,
x_attribute14 => p_rt_quota_asgn.attribute14,
x_attribute15 => p_rt_quota_asgn.attribute15,
x_last_update_date => p_rt_quota_asgn.last_update_date,
x_last_updated_by => p_rt_quota_asgn.last_updated_by,
x_creation_date => p_rt_quota_asgn.creation_date,
x_created_by => p_rt_quota_asgn.created_by,
x_last_update_login => p_rt_quota_asgn.last_updated_by,
x_program_type => g_program_type,
x_object_version_number => p_rt_quota_asgn.object_version_number
);
PROCEDURE update_rate_table_assignment (
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_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_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_rate_table_assignment';
SELECT *
FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = p_rt_quota_asgn.rt_quota_asgn_id;
p_last_update_login NUMBER;
p_last_update_date DATE;
p_last_updated_by NUMBER;
SAVEPOINT update_rt_quota_asgn;
SELECT DECODE (p_rt_quota_asgn.org_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.org_id, p_rt_quota_asgn.org_id),
DECODE (p_rt_quota_asgn.rt_quota_asgn_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.rt_quota_asgn_id, p_rt_quota_asgn.rt_quota_asgn_id),
DECODE (p_rt_quota_asgn.quota_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.quota_id, p_rt_quota_asgn.quota_id),
DECODE (p_rt_quota_asgn.start_date, fnd_api.g_miss_date, TRUNC (l_old_rt_quota_asgn.start_date), TRUNC (p_rt_quota_asgn.start_date)),
DECODE (p_rt_quota_asgn.end_date, fnd_api.g_miss_date, TRUNC (l_old_rt_quota_asgn.end_date), TRUNC (p_rt_quota_asgn.end_date)),
DECODE (p_rt_quota_asgn.rate_schedule_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.rate_schedule_id, p_rt_quota_asgn.rate_schedule_id),
DECODE (p_rt_quota_asgn.calc_formula_id, fnd_api.g_miss_num, l_old_rt_quota_asgn.calc_formula_id, p_rt_quota_asgn.calc_formula_id),
DECODE (p_rt_quota_asgn.attribute_category,
fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute_category,
p_rt_quota_asgn.attribute_category
),
DECODE (p_rt_quota_asgn.attribute1, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute1, p_rt_quota_asgn.attribute1),
DECODE (p_rt_quota_asgn.attribute2, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute2, p_rt_quota_asgn.attribute2),
DECODE (p_rt_quota_asgn.attribute3, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute3, p_rt_quota_asgn.attribute3),
DECODE (p_rt_quota_asgn.attribute4, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute4, p_rt_quota_asgn.attribute4),
DECODE (p_rt_quota_asgn.attribute5, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute5, p_rt_quota_asgn.attribute5),
DECODE (p_rt_quota_asgn.attribute6, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute6, p_rt_quota_asgn.attribute6),
DECODE (p_rt_quota_asgn.attribute7, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute7, p_rt_quota_asgn.attribute7),
DECODE (p_rt_quota_asgn.attribute8, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute8, p_rt_quota_asgn.attribute8),
DECODE (p_rt_quota_asgn.attribute9, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute9, p_rt_quota_asgn.attribute9),
DECODE (p_rt_quota_asgn.attribute10, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute10, p_rt_quota_asgn.attribute10),
DECODE (p_rt_quota_asgn.attribute11, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute11, p_rt_quota_asgn.attribute11),
DECODE (p_rt_quota_asgn.attribute12, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute12, p_rt_quota_asgn.attribute12),
DECODE (p_rt_quota_asgn.attribute13, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute13, p_rt_quota_asgn.attribute13),
DECODE (p_rt_quota_asgn.attribute14, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute14, p_rt_quota_asgn.attribute14),
DECODE (p_rt_quota_asgn.attribute15, fnd_api.g_miss_char, l_old_rt_quota_asgn.attribute15, p_rt_quota_asgn.attribute15),
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
p_rt_quota_asgn.object_version_number
INTO p_rt_quota_asgn.org_id,
p_rt_quota_asgn.rt_quota_asgn_id,
p_rt_quota_asgn.quota_id,
p_rt_quota_asgn.start_date,
p_rt_quota_asgn.end_date,
p_rt_quota_asgn.rate_schedule_id,
p_rt_quota_asgn.calc_formula_id,
p_rt_quota_asgn.attribute_category,
p_rt_quota_asgn.attribute1,
p_rt_quota_asgn.attribute2,
p_rt_quota_asgn.attribute3,
p_rt_quota_asgn.attribute4,
p_rt_quota_asgn.attribute5,
p_rt_quota_asgn.attribute6,
p_rt_quota_asgn.attribute7,
p_rt_quota_asgn.attribute8,
p_rt_quota_asgn.attribute9,
p_rt_quota_asgn.attribute10,
p_rt_quota_asgn.attribute11,
p_rt_quota_asgn.attribute12,
p_rt_quota_asgn.attribute13,
p_rt_quota_asgn.attribute14,
p_rt_quota_asgn.attribute15,
p_rt_quota_asgn.created_by,
p_rt_quota_asgn.creation_date,
p_rt_quota_asgn.last_update_login,
p_rt_quota_asgn.last_update_date,
p_rt_quota_asgn.last_updated_by,
p_rt_quota_asgn.object_version_number
FROM DUAL;
p_action => 'UPDATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_operation => 'UPDATE',
x_rowid => g_rowid,
x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
x_calc_formula_id => p_rt_quota_asgn.calc_formula_id,
x_quota_id => p_rt_quota_asgn.quota_id,
x_start_date => p_rt_quota_asgn.start_date,
x_end_date => p_rt_quota_asgn.end_date,
x_rate_schedule_id => p_rt_quota_asgn.rate_schedule_id,
x_attribute_category => p_rt_quota_asgn.attribute_category,
x_attribute1 => p_rt_quota_asgn.attribute1,
x_attribute2 => p_rt_quota_asgn.attribute2,
x_attribute3 => p_rt_quota_asgn.attribute3,
x_attribute4 => p_rt_quota_asgn.attribute4,
x_attribute5 => p_rt_quota_asgn.attribute5,
x_attribute6 => p_rt_quota_asgn.attribute6,
x_attribute7 => p_rt_quota_asgn.attribute7,
x_attribute8 => p_rt_quota_asgn.attribute8,
x_attribute9 => p_rt_quota_asgn.attribute9,
x_attribute10 => p_rt_quota_asgn.attribute10,
x_attribute11 => p_rt_quota_asgn.attribute11,
x_attribute12 => p_rt_quota_asgn.attribute12,
x_attribute13 => p_rt_quota_asgn.attribute13,
x_attribute14 => p_rt_quota_asgn.attribute14,
x_attribute15 => p_rt_quota_asgn.attribute15,
x_last_update_date => p_rt_quota_asgn.last_update_date,
x_last_updated_by => p_rt_quota_asgn.last_updated_by,
x_creation_date => p_rt_quota_asgn.creation_date,
x_created_by => p_rt_quota_asgn.created_by,
x_last_update_login => p_rt_quota_asgn.last_updated_by,
x_program_type => g_program_type,
x_object_version_number => p_rt_quota_asgn.object_version_number
);
'update',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO update_rt_quota_asgn;
ROLLBACK TO update_rt_quota_asgn;
ROLLBACK TO update_rt_quota_asgn;
END update_rate_table_assignment;
PROCEDURE delete_rate_table_assignment (
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_rt_quota_asgn IN OUT NOCOPY rt_quota_asgn_rec_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) := 'delete_rate_table_assignment';
p_last_update_login NUMBER;
p_last_update_date DATE;
p_last_updated_by NUMBER;
SELECT rate_schedule_id,
quota_id
FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = l_rt_quota_asgn_id;
SAVEPOINT delete_rt_quota_asgn;
/*select count(1)
into l_temp_count
from cn_srp_plan_assigns
where quota_rule_id = p_quota_rule.quota_rule_id
and rownum = 1;
SELECT p_rt_quota_asgn.org_id,
p_rt_quota_asgn.rt_quota_asgn_id,
p_rt_quota_asgn.quota_id,
p_rt_quota_asgn.start_date,
p_rt_quota_asgn.end_date,
p_rt_quota_asgn.rate_schedule_id,
p_rt_quota_asgn.calc_formula_id,
p_rt_quota_asgn.attribute_category,
p_rt_quota_asgn.attribute1,
p_rt_quota_asgn.attribute2,
p_rt_quota_asgn.attribute3,
p_rt_quota_asgn.attribute4,
p_rt_quota_asgn.attribute5,
p_rt_quota_asgn.attribute6,
p_rt_quota_asgn.attribute7,
p_rt_quota_asgn.attribute8,
p_rt_quota_asgn.attribute9,
p_rt_quota_asgn.attribute10,
p_rt_quota_asgn.attribute11,
p_rt_quota_asgn.attribute12,
p_rt_quota_asgn.attribute13,
p_rt_quota_asgn.attribute14,
p_rt_quota_asgn.attribute15,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
p_rt_quota_asgn.object_version_number + 1
INTO p_org_id,
p_rt_quota_asgn_id,
p_quota_id,
p_start_date,
p_end_date,
p_rate_schedule_id,
p_calc_formula_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_created_by,
p_creation_date,
p_last_update_login,
p_last_update_date,
p_last_updated_by,
p_object_version_number
FROM DUAL;
x_operation => 'DELETE',
x_rowid => g_rowid,
x_rt_quota_asgn_id => p_rt_quota_asgn.rt_quota_asgn_id,
x_calc_formula_id => p_calc_formula_id,
x_quota_id => p_quota_id,
x_start_date => p_start_date,
x_end_date => p_end_date,
x_rate_schedule_id => p_rate_schedule_id,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_last_update_date => p_last_update_date,
x_last_updated_by => p_last_updated_by,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
x_last_update_login => p_last_updated_by,
x_program_type => g_program_type,
x_object_version_number => p_rt_quota_asgn.object_version_number
);
'delete',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO delete_rt_quota_asgn;
ROLLBACK TO delete_rt_quota_asgn;
ROLLBACK TO delete_rt_quota_asgn;
END delete_rate_table_assignment;
SELECT DISTINCT child_id
FROM cn_calc_edges
WHERE edge_type = 'FE' AND parent_id IN (SELECT calc_sql_exp_id
FROM cn_formula_inputs
WHERE calc_formula_id = l_parent_id
UNION
SELECT output_exp_id
FROM cn_calc_formulas
WHERE calc_formula_id = l_parent_id);
SELECT rt_quota_asgn_id
FROM cn_rt_quota_asgns
WHERE quota_id = l_quota_id AND calc_formula_id = l_calc_formula_id;
l_stack.DELETE (l_stack.LAST);
SELECT NAME,
calc_formula_id
INTO l_calc_rec.NAME,
l_calc_rec.calc_formula_id
FROM cn_calc_formulas
WHERE calc_formula_id = l_parent_calc_formula_id;
insert_formula_rate_tables (p_quota_id, l_parent_calc_formula_id, l_rate_tables);
IF p_action = 'DELETE'
THEN
NULL;
SELECT NAME
INTO l_name
FROM cn_rate_schedules
WHERE rate_schedule_id = p_rt_quota_asgn.rate_schedule_id;
SELECT name, quota_type_code
INTO l_name , l_formula_type
FROM cn_quotas
WHERE quota_id = p_rt_quota_asgn.quota_id;
SELECT NAME
INTO l_name
FROM cn_calc_formulas fml
WHERE fml.calc_formula_id = p_rt_quota_asgn.calc_formula_id;
SELECT COUNT (*)
INTO l_counter
FROM cn_rt_quota_asgns rta
WHERE rta.quota_id = p_rt_quota_asgn.quota_id
AND rta.rt_quota_asgn_id <> NVL (p_rt_quota_asgn.rt_quota_asgn_id, 0)
AND NVL(rta.calc_formula_id,-1) = NVL(p_rt_quota_asgn.calc_formula_id,-1)
AND rta.start_date <= NVL (p_rt_quota_asgn.end_date, rta.start_date)
AND p_rt_quota_asgn.start_date <= NVL(rta.end_date,p_rt_quota_asgn.start_date) ;
SELECT NUMBER_DIM INTO l_formula_dim FROM CN_CALC_FORMULAS
WHERE calc_formula_id = p_rt_quota_asgn.calc_formula_id;
SELECT NUMBER_DIM INTO l_rate_dim FROM CN_RATE_SCHEDULES
WHERE rate_schedule_id = p_rt_quota_asgn.rate_schedule_id;