[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select substr(to_char(abs(dbms_random.random)),1,RAND_NUM_LENGTH) into l_unique_identifier from dual;
select fnd_message.get_string('CN','CN_COPY_MSG') into l_msg_copy from dual;
SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_comp_plans_all where org_id = p_org_id and comp_plan_id = p_id;
SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_quotas_all where org_id = p_org_id and quota_id = p_id;
SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_calc_sql_exps_all where org_id = p_org_id and CALC_SQL_EXP_ID = p_id;
SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_rate_schedules_all where org_id = p_org_id and RATE_SCHEDULE_ID = p_id;
SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_rate_dimensions_all where org_id = p_org_id and RATE_DIMENSION_ID = p_id;
SELECT SUBSTRB(NAME,1,(30-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_calc_formulas_all where org_id = p_org_id and CALC_FORMULA_ID = p_id;
SELECT SUBSTRB(NAME,1,(80-l_length)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_scenarios_all where org_id = p_org_id and scenario_id = p_id;
SELECT SUBSTRB(NAME,1,(80-l_length_expscn)),LENGTHB(NAME) into l_obj_name,l_obj_length from cn_scenarios_all where org_id = p_org_id and scenario_id = p_id;
select file_content_blob into l_blob from cn_copy_requests_all
where EXP_IMP_REQUEST_ID = p_exp_imp_id;
update cn_copy_requests_all set file_content_clob = l_clob where EXP_IMP_REQUEST_ID = p_exp_imp_id;
select file_content_clob into l_clob from cn_copy_requests_all
where EXP_IMP_REQUEST_ID = p_exp_imp_id;
update cn_copy_requests_all set file_content_xmltype = l_xmltype where EXP_IMP_REQUEST_ID = p_exp_imp_id;
SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
SELECT SUBSTRB(l_temp_str,1,80) into l_return_str FROM DUAL;
SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
SELECT SUBSTRB(l_temp_str,1,30) into l_return_str FROM DUAL;
PROCEDURE update_existing_expression(
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_update_parent_also IN VARCHAR2 := fnd_api.g_false
, p_org_id IN cn_calc_sql_exps.org_id%TYPE
, p_calc_sql_exp_id IN cn_calc_sql_exps.calc_sql_exp_id%TYPE
, p_name IN cn_calc_sql_exps.NAME%TYPE
, p_description IN cn_calc_sql_exps.description%TYPE := NULL
, p_expression_disp IN VARCHAR2 := NULL
, -- CLOBs
p_sql_select IN VARCHAR2 := NULL
, p_sql_from IN VARCHAR2 := NULL
, p_piped_expression_disp IN VARCHAR2 := NULL
, p_piped_sql_select IN VARCHAR2 := NULL
, p_piped_sql_from IN VARCHAR2 := NULL
, p_ovn IN OUT NOCOPY cn_calc_sql_exps.object_version_number%TYPE
, p_exp_type_code IN cn_calc_sql_exps.exp_type_code%TYPE
, p_status IN cn_calc_sql_exps.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_Existing_Expression';
l_select_start PLS_INTEGER;
l_select_end PLS_INTEGER;
SELECT 1
FROM cn_calc_sql_exps
WHERE NAME = p_name AND org_id = p_org_id AND calc_sql_exp_id <> p_calc_sql_exp_id;
SAVEPOINT update_expression;
fnd_file.put_line(fnd_file.Log, ' update_existing_expression: x_exp_type_code ' || p_exp_type_code || ' x_status :' || p_status);
cn_calc_sql_exps_pkg.update_row(
x_org_id => p_org_id
, x_calc_sql_exp_id => p_calc_sql_exp_id
, x_name => p_name
, x_description => p_description
, x_status => p_status
, x_exp_type_code => p_exp_type_code
, x_expression_disp => p_expression_disp
, x_sql_select => p_sql_select
, x_sql_from => p_sql_from
, x_piped_sql_select => p_piped_sql_select
, x_piped_sql_from => p_piped_sql_from
, x_piped_expression_disp => p_piped_expression_disp
, x_object_version_number => p_ovn
);
l_select_start := 1;
l_select_end := INSTR(p_piped_sql_select, '|', l_select_start, 1);
INSTR(p_piped_sql_select, '(', l_select_start, 1) = l_select_start
AND (l_select_end - l_select_start) > 1
) THEN
l_exp_names := l_exp_names || l_token || '|';
INSERT INTO cn_calc_edges
(
org_id
, calc_edge_id
, parent_id
, child_id
, edge_type
, creation_date
, created_by
, last_update_login
, last_update_date
, last_updated_by
)
SELECT org_id
, cn_calc_edges_s.NEXTVAL
, p_calc_sql_exp_id
, calc_sql_exp_id
, 'EE'
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, fnd_global.user_id
FROM cn_calc_sql_exps
WHERE NAME = l_token
AND org_id= p_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_calc_edges
WHERE parent_id = p_calc_sql_exp_id
AND child_id = (SELECT calc_sql_exp_id
FROM cn_calc_sql_exps
WHERE NAME = l_token AND org_id = p_org_id AND edge_type = 'EE'));
ELSIF(INSTR(p_piped_sql_select, 'cn_formula', l_select_start, 1) = l_select_start) THEN
l_dummy := INSTR(p_piped_sql_select, '_', l_select_start, 2) + 1;
SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
);
INSERT INTO cn_calc_edges
(
org_id
, calc_edge_id
, parent_id
, child_id
, edge_type
, creation_date
, created_by
, last_update_login
, last_update_date
, last_updated_by
)
SELECT p_org_id
, cn_calc_edges_s.NEXTVAL
, p_calc_sql_exp_id
, l_calc_formula_id
, 'FE'
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, fnd_global.user_id
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM cn_calc_edges
WHERE parent_id = p_calc_sql_exp_id
AND child_id = l_calc_formula_id
AND edge_type = 'FE');
l_select_start := l_select_end + 1;
DELETE FROM cn_calc_edges
WHERE parent_id = p_calc_sql_exp_id
AND INSTR(l_formula_ids, '|' || child_id || '|', 1, 1) = 0
AND edge_type = 'FE';
DELETE FROM cn_calc_edges a
WHERE a.parent_id = p_calc_sql_exp_id
AND a.edge_type = 'EE'
AND NOT EXISTS(
SELECT 1
FROM cn_calc_sql_exps b
WHERE a.child_id = b.calc_sql_exp_id
AND INSTR(l_exp_names, '|' || b.NAME || '|', 1, 1) > 0);
IF (fnd_api.to_boolean(p_update_parent_also)) THEN
NULL;
ROLLBACK TO update_expression;
ROLLBACK TO update_expression;
ROLLBACK TO update_expression;
END update_existing_expression;