[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_sql_select IN VARCHAR2
, p_sql_from IN VARCHAR2
, p_piped_sql_select IN VARCHAR2
, p_piped_sql_from IN VARCHAR2
, x_status IN OUT NOCOPY cn_calc_sql_exps.status%TYPE
, x_exp_type_code IN OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_dummy PLS_INTEGER;
SELECT 1
FROM cn_calc_ext_tables
WHERE alias = l_alias
AND (org_id = p_org_id)
AND internal_table_id IN(
SELECT object_id
FROM cn_objects
WHERE (NAME = 'CN_COMMISSION_LINES' OR NAME = 'CN_COMMISSION_HEADERS')
AND object_type = 'TBL'
AND (org_id = p_org_id));
IF LENGTH(p_sql_select) + LENGTH(p_sql_from) > 4000 THEN
IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
fnd_message.set_name('CN', 'CN_EXP_TOO_LONG');
'select ' || p_sql_select || ' from ' || p_sql_from || ' where rownum < 1';
IF (p_piped_sql_select IS NULL OR p_piped_sql_from IS NULL) THEN
RETURN;
INSTR(p_piped_sql_select, 'CL.', 1, 1) = 1
OR INSTR(p_piped_sql_select, '|CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '(CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '+CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '-CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '*CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '/CL.', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'CH.', 1, 1) = 1
OR INSTR(p_piped_sql_select, '|CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '(CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '+CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '-CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '*CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, '/CH.', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'p_commission_line_id', 1, 1) > 0
) THEN
x_exp_type_code := 'Y';
INSTR(p_piped_sql_select, 'AVG(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'COUNT(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'MIN(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'MAX(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'STDDEV(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'SUM(', 1, 1) > 0
OR INSTR(p_piped_sql_select, 'VARIANCE(', 1, 1) > 0
) THEN
x_exp_type_code := x_exp_type_code || 'Y';
l_pos := INSTR(p_piped_sql_select, 'RateResult', 1, 1);
IF (l_pos = 1 AND INSTR(p_piped_sql_select, 'RateResult', 11, 1) = 0) THEN
x_exp_type_code := x_exp_type_code || 'Y';
IF (INSTR(p_piped_sql_select, 'p_commission_line_id', 1, 1) > 0) THEN
x_exp_type_code := x_exp_type_code || 'Y';
IF (INSTR(p_piped_sql_select, 'ForecastAmount', 1, 1) > 0) THEN
x_exp_type_code := x_exp_type_code || 'Y';
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
, x_calc_sql_exp_id IN OUT NOCOPY cn_calc_sql_exps.calc_sql_exp_id%TYPE
, x_exp_type_code OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
, x_status OUT NOCOPY cn_calc_sql_exps.status%TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_object_version_number OUT NOCOPY cn_calc_sql_exps.object_version_number%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_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;
, p_sql_select => p_sql_select
, p_sql_from => p_sql_from
, p_piped_sql_select => p_piped_sql_select
, p_piped_sql_from => p_piped_sql_from
, x_status => x_status
, x_exp_type_code => x_exp_type_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
cn_calc_sql_exps_pkg.insert_row(
x_org_id => p_org_id
, x_calc_sql_exp_id => x_calc_sql_exp_id
, x_name => p_name
, x_description => p_description
, x_status => x_status
, x_exp_type_code => x_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 => x_object_version_number
);
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
-- insert calc edges (calc edges has no table handler)
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
, x_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;
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
)
VALUES (
p_org_id
, cn_calc_edges_s.NEXTVAL
, x_calc_sql_exp_id
, l_calc_formula_id
, 'FE'
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, SYSDATE
, fnd_global.user_id
);
l_select_start := l_select_end + 1;
PROCEDURE update_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
, x_exp_type_code OUT NOCOPY cn_calc_sql_exps.exp_type_code%TYPE
, x_status OUT NOCOPY 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_Expression';
l_select_start PLS_INTEGER;
l_select_end PLS_INTEGER;
SELECT 1
FROM DUAL
WHERE (EXISTS(SELECT 1
FROM cn_calc_edges
WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
OR (
EXISTS(SELECT 1
FROM cn_calc_formulas
WHERE perf_measure_id = p_calc_sql_exp_id OR output_exp_id = p_calc_sql_exp_id)
)
OR (
EXISTS(
SELECT 1
FROM cn_formula_inputs
WHERE calc_sql_exp_id = p_calc_sql_exp_id
OR f_calc_sql_exp_id = p_calc_sql_exp_id)
)
OR (EXISTS(SELECT 1
FROM cn_rate_dim_tiers
WHERE min_exp_id = p_calc_sql_exp_id OR max_exp_id = p_calc_sql_exp_id));
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;
, p_sql_select => p_sql_select
, p_sql_from => p_sql_from
, p_piped_sql_select => p_piped_sql_select
, p_piped_sql_from => p_piped_sql_from
, x_status => x_status
, x_exp_type_code => x_exp_type_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
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 => x_status
, x_exp_type_code => x_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_expression;
PROCEDURE delete_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_calc_sql_exp_id IN cn_calc_sql_exps.calc_sql_exp_id%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_Expression';
SELECT 1
FROM DUAL
WHERE (EXISTS(SELECT 1
FROM cn_calc_edges
WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
OR (
EXISTS(SELECT 1
FROM cn_calc_formulas
WHERE perf_measure_id = p_calc_sql_exp_id OR output_exp_id = p_calc_sql_exp_id)
)
OR (
EXISTS(
SELECT 1
FROM cn_formula_inputs
WHERE calc_sql_exp_id = p_calc_sql_exp_id
OR f_calc_sql_exp_id = p_calc_sql_exp_id)
)
OR (EXISTS(SELECT 1
FROM cn_rate_dim_tiers
WHERE min_exp_id = p_calc_sql_exp_id OR max_exp_id = p_calc_sql_exp_id));
SAVEPOINT delete_expression;
cn_calc_sql_exps_pkg.delete_row(x_calc_sql_exp_id => p_calc_sql_exp_id);
DELETE FROM cn_calc_edges e
WHERE edge_type IN('EE', 'FE') AND NOT EXISTS(SELECT 1
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = e.parent_id);
ROLLBACK TO delete_expression;
ROLLBACK TO delete_expression;
ROLLBACK TO delete_expression;
END delete_expression;
SELECT name
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id IN (SELECT parent_id
FROM cn_calc_edges
CONNECT BY child_id = PRIOR parent_id
AND edge_type = 'EE'
START WITH child_id = p_calc_sql_exp_id
AND edge_type = 'EE')
UNION ALL
SELECT name
FROM cn_rate_dimensions
WHERE rate_dimension_id in (SELECT rate_dimension_id
FROM cn_rate_dim_tiers
WHERE min_exp_id = p_calc_sql_exp_id
OR max_exp_id = p_calc_sql_exp_id)
UNION ALL
SELECT name
FROM cn_calc_formulas
WHERE perf_measure_id = p_calc_sql_exp_id
OR output_exp_id = p_calc_sql_exp_id
OR f_output_exp_id = p_calc_sql_exp_id
OR (calc_formula_id IN (SELECT calc_formula_id FROM cn_formula_inputs
WHERE calc_sql_exp_id = p_calc_sql_exp_id
OR f_calc_sql_exp_id = p_calc_sql_exp_id));
select calc_sql_exp_id, name, description, status, exp_type_code
from cn_calc_sql_exps
where upper(name) like l_srch_name
order by 2;
CURSOR count_rows IS select count(1) from cn_calc_sql_exps
where upper(name) like l_srch_name;
x_sql_select OUT NOCOPY VARCHAR2,
x_sql_from OUT NOCOPY VARCHAR2,
x_piped_sql_select OUT NOCOPY VARCHAR2,
x_piped_sql_from OUT NOCOPY VARCHAR2,
x_piped_expression_disp OUT NOCOPY VARCHAR2,
x_ovn OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE) IS
CURSOR get_data IS
select name, description, status, exp_type_code,
dbms_lob.substr(expression_disp),
dbms_lob.substr(sql_select),
dbms_lob.substr(sql_from),
dbms_lob.substr(piped_sql_select),
dbms_lob.substr(piped_sql_from),
dbms_lob.substr(piped_expression_disp),
object_version_number
from cn_calc_sql_exps where calc_sql_exp_id = p_calc_sql_exp_id;
x_expression_disp, x_sql_select, x_sql_from,
x_piped_sql_select, x_piped_sql_from, x_piped_expression_disp, x_ovn;
select user_name, name, alias, object_id
from cn_objects
where calc_eligible_flag = 'Y'
and object_type in ('TBL', 'VIEW')
and user_name is not null
and object_id < 0
and name like 'CN%'
and alias is not null
order by user_name;
select user_name, name
from cn_objects
where table_id = p_table_id
and calc_formula_flag = 'Y'
and object_type = 'COL'
order by user_name;
select calc_sql_exp_id, name, dbms_lob.substr(sql_select) node_value
from cn_calc_sql_exps
where status = 'VALID'
and dbms_lob.getlength(sql_select) < 3999
order by name;
select name, 'cn_formula_' || abs(calc_formula_id) || '_' || abs(org_id) ||
'_pkg.get_result(p_commission_line_id)' node_value
from cn_calc_formulas
where formula_status = 'COMPLETE'
and cumulative_flag = 'N'
and trx_group_code = 'INDIVIDUAL'
and itd_flag = 'N'
and formula_type = 'C'
order by name;
select user_name, name, alias, object_id
from cn_objects
where calc_eligible_flag = 'Y'
and object_type in ('TBL', 'VIEW')
and user_name is not null
and object_id > 0
order by user_name;
select quota_id, name
from cn_quotas_v
order by name;
p_sql_select IN VARCHAR2
, x_plan_elt_tbl OUT NOCOPY num_tbl_type
, x_parsed_sql_select OUT NOCOPY VARCHAR2
) IS
s VARCHAR2(1); -- character before 'PE'
x_parsed_sql_select := p_sql_select;
i := INSTR(x_parsed_sql_select, 'PE.', i + 1);
s := SUBSTR(x_parsed_sql_select, i - 1, 1);
openpar := INSTR(x_parsed_sql_select, '(', i - LENGTH(x_parsed_sql_select));
clspar := INSTR(x_parsed_sql_select, ')', i);
pe := SUBSTR(x_parsed_sql_select, openpar + 1, i - openpar - 1);
x_parsed_sql_select :=
SUBSTR(x_parsed_sql_select, 1, openpar) || '0'
|| SUBSTR(x_parsed_sql_select, clspar);
SELECT calc_formula_id
FROM cn_quotas_v
WHERE quota_id = p_current_id;
SELECT ccse.calc_sql_exp_id
FROM cn_calc_sql_exps ccse, cn_calc_formulas ccf, cn_formula_inputs cfi
WHERE (
(ccse.calc_sql_exp_id = ccf.perf_measure_id)
OR (ccse.calc_sql_exp_id = ccf.output_exp_id)
OR (ccse.calc_sql_exp_id = cfi.calc_sql_exp_id)
OR (ccse.calc_sql_exp_id = cfi.f_calc_sql_exp_id)
)
AND cfi.calc_formula_id = ccf.calc_formula_id
AND ccf.calc_formula_id = p_current_id;
SELECT child_id
FROM cn_calc_edges
WHERE edge_type = 'FE' AND parent_id = p_current_id;
SELECT DBMS_LOB.SUBSTR(sql_select)
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = p_current_id;
SELECT quota_id
FROM cn_quotas_v
WHERE calc_formula_id = p_current_id;
SELECT calc_sql_exp_id
FROM cn_calc_sql_exps
WHERE DBMS_LOB.SUBSTR(sql_select) LIKE '%(' || p_current_id || 'PE.%';
SELECT calc_formula_id
FROM cn_formula_inputs
WHERE calc_sql_exp_id = p_current_id OR f_calc_sql_exp_id = p_current_id
UNION ALL
SELECT calc_formula_id
FROM cn_calc_formulas
WHERE output_exp_id = p_current_id
OR f_output_exp_id = p_current_id
OR perf_measure_id = p_current_id;
SELECT parent_id exp_id
FROM cn_calc_edges
WHERE edge_type = 'FE' AND child_id = p_current_id;
PROCEDURE parse_sql_select(
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_sql_select IN OUT NOCOPY VARCHAR2
, x_piped_sql_select OUT NOCOPY VARCHAR2
, x_expr_disp OUT NOCOPY VARCHAR2
, x_piped_expr_disp OUT NOCOPY VARCHAR2
, x_sql_from OUT NOCOPY VARCHAR2
, x_piped_sql_from OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_sql_select_left VARCHAR2(4000) := p_sql_select;
l_api_name CONSTANT VARCHAR2(30) := 'parse_sql_select';
SELECT NAME
FROM cn_calc_formulas
WHERE 'cn_formula_'
|| calc_formula_id
|| '_'
|| org_id
|| '_pkg.get_result(p_commission_line_id)' = l_segment;
SELECT NAME
FROM cn_quotas_v
WHERE quota_id || 'PE' = l_segment;
SELECT user_name
, object_id
, NAME
FROM cn_objects
WHERE calc_eligible_flag = 'Y'
AND object_type IN('TBL', 'VIEW')
AND user_name IS NOT NULL
AND alias = l_segment;
SELECT user_name
FROM cn_objects
WHERE table_id = l_table_id
AND calc_formula_flag = 'Y'
AND object_type = 'COL'
AND user_name IS NOT NULL
AND NAME = l_segment;
SELECT object_name
FROM user_objects
WHERE object_type = 'FUNCTION' AND status = 'VALID';
SELECT UPPER(p_sql_select)
INTO l_sql_select_left
FROM DUAL;
IF SUBSTR(l_sql_select_left, 1, 1) = '(' THEN
-- get close parenthesis
l_ix := INSTR(l_sql_select_left, '.');
l_seg := SUBSTR(l_sql_select_left, 2, l_ix - 2);
l_ix2 := INSTR(l_sql_select_left, ')');
l_seg2 := SUBSTR(l_sql_select_left, l_ix + 1, l_ix2 - l_ix - 1);
l_sql_select_left := SUBSTR(l_sql_select_left, l_ix2 + 1);
x_piped_sql_select := x_piped_sql_select || '(' || l_seg || '.' || l_seg2 || ')|';
IF SUBSTR(l_sql_select_left, 1, 1) = '''' AND success = FALSE THEN
-- get close quote
l_ix := INSTR(l_sql_select_left, '''', 2);
fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
x_piped_sql_select := x_piped_sql_select || SUBSTR(l_sql_select_left, 1, l_ix) || '|';
x_piped_expr_disp := x_piped_expr_disp || SUBSTR(l_sql_select_left, 1, l_ix) || '|';
l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 1);
WHILE SUBSTR(l_sql_select_left, 1, 1) BETWEEN '0' AND '9'
OR SUBSTR(l_sql_select_left, 1, 1) = '.' LOOP
x_piped_sql_select := x_piped_sql_select || SUBSTR(l_sql_select_left, 1, 1);
x_piped_expr_disp := x_piped_expr_disp || SUBSTR(l_sql_select_left, 1, 1);
l_sql_select_left := SUBSTR(l_sql_select_left, 2);
x_piped_sql_select := x_piped_sql_select || '|';
IF SUBSTR(l_sql_select_left, 1, LENGTH(sel_pieces(i))) = UPPER(sel_pieces(i)) THEN
l_sql_select_left := SUBSTR(l_sql_select_left, LENGTH(sel_pieces(i)) + 1);
x_piped_sql_select := x_piped_sql_select || sel_pieces(i) || '|';
IF success = FALSE AND SUBSTR(l_sql_select_left, 1, 10) = 'cn_formula' THEN
-- look for p_commission_line_id
l_ix := INSTR(l_sql_select_left, 'p_commission_line_id');
l_seg := SUBSTR(l_sql_select_left, 1, l_ix + 20);
l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 21);
x_piped_sql_select := x_piped_sql_select || l_seg || '|';
IF SUBSTR(l_sql_select_left, 1, LENGTH(f.object_name) + 1) = UPPER(f.object_name) || '(' THEN
-- found a function
x_piped_sql_select := x_piped_sql_select || f.object_name || '(|';
l_sql_select_left := SUBSTR(l_sql_select_left, LENGTH(f.object_name) + 2);
IF success = FALSE AND SUBSTR(l_sql_select_left, 1, 1) = ' ' THEN
l_sql_select_left := SUBSTR(l_sql_select_left, 2);
IF success = FALSE AND l_sql_select_left IS NOT NULL THEN
-- look for dot and table alias
l_ix := INSTR(l_sql_select_left, '.');
l_seg := SUBSTR(l_sql_select_left, 1, l_ix - 1); -- the alias
fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
x_piped_sql_select := x_piped_sql_select || l_seg;
l_sql_select_left := SUBSTR(l_sql_select_left, l_ix + 1);
l_ix := LENGTH(l_sql_select_left) + 1;
IF INSTR(l_sql_select_left, opers(c)) BETWEEN 1 AND l_ix THEN
l_ix := INSTR(l_sql_select_left, opers(c));
l_seg := SUBSTR(l_sql_select_left, 1, l_ix - 1);
fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
x_piped_sql_select := x_piped_sql_select || '.' || l_seg || '|';
l_sql_select_left := SUBSTR(l_sql_select_left, l_ix);
fnd_message.set_name('CN', 'CN_SQL_SELECT_PARSE_ERR');
p_sql_select := REPLACE(x_piped_sql_select, '|', '');
END parse_sql_select;
l_piped_sql_select VARCHAR2(4000);
SELECT *
FROM cn_exp_api_imp_v
WHERE imp_header_id = p_imp_header_id AND status_code = l_status_code;
SELECT NAME
, status_code
, server_flag
, imp_map_id
, source_column_num
, import_type_code
INTO l_imp_header.NAME
, l_imp_header.status_code
, l_imp_header.server_flag
, l_imp_header.imp_map_id
, l_imp_header.source_column_num
, l_imp_header.import_type_code
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
IF l_api_rec.expression_name IS NULL OR l_api_rec.sql_select IS NULL THEN
l_failed_row := l_failed_row + 1;
cn_import_pvt.update_imp_lines(
p_imp_line_id => l_api_rec.imp_line_id
, p_status_code => 'FAIL'
, p_error_code => l_error_code
);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => 'IMPORT_FAIL'
, p_failed_row => l_failed_row
);
parse_sql_select(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_sql_select => l_api_rec.sql_select
, x_piped_sql_select => l_piped_sql_select
, x_expr_disp => l_expr_disp
, x_piped_expr_disp => l_piped_expr_disp
, x_sql_from => l_sql_from
, x_piped_sql_from => l_piped_sql_from
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
, p_sql_select => l_api_rec.sql_select
, p_sql_from => l_sql_from
, p_piped_expression_disp => l_piped_expr_disp
, p_piped_sql_select => l_piped_sql_select
, p_piped_sql_from => l_piped_sql_from
, x_calc_sql_exp_id => l_exp_id
, x_exp_type_code => l_type_code
, x_status => l_status
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_object_version_number => l_object_version_number
);
UPDATE cn_calc_sql_exps
SET attribute_category = l_api_rec.attribute_category
, attribute1 = l_api_rec.attribute1
, attribute2 = l_api_rec.attribute2
, attribute3 = l_api_rec.attribute3
, attribute4 = l_api_rec.attribute4
, attribute5 = l_api_rec.attribute5
, attribute6 = l_api_rec.attribute6
, attribute7 = l_api_rec.attribute7
, attribute8 = l_api_rec.attribute8
, attribute9 = l_api_rec.attribute9
, attribute10 = l_api_rec.attribute10
, attribute11 = l_api_rec.attribute11
, attribute12 = l_api_rec.attribute12
, attribute13 = l_api_rec.attribute13
, attribute14 = l_api_rec.attribute14
, attribute15 = l_api_rec.attribute15
WHERE calc_sql_exp_id = l_exp_id;
cn_import_pvt.update_imp_lines(
p_imp_line_id => l_api_rec.imp_line_id
, p_status_code => 'FAIL'
, p_error_code => NULL
, p_error_msg => NVL(l_error_code, 'Unexpected Error')
);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => 'IMPORT_FAIL'
, p_failed_row => l_failed_row
);
cn_import_pvt.update_imp_lines(
p_imp_line_id => l_api_rec.imp_line_id
, p_status_code => 'COMPLETE'
, p_error_code => l_error_code
);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => NULL
, p_processed_row => l_processed_row
);
cn_import_pvt.update_imp_lines(
p_imp_line_id => l_api_rec.imp_line_id
, p_status_code => 'FAIL'
, p_error_code => NULL
, p_error_msg => l_message
);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => 'IMPORT_FAIL'
, p_processed_row => l_processed_row
, p_failed_row => l_failed_row
);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => 'COMPLETE'
, p_processed_row => l_processed_row
, p_failed_row => l_failed_row
);
, 'SQL_SELECT'
, 'ATTRIBUTE_CATEGORY'
, 'ATTRIBUTE1'
, 'ATTRIBUTE2'
, 'ATTRIBUTE3'
, 'ATTRIBUTE4'
, 'ATTRIBUTE5'
, 'ATTRIBUTE6'
, 'ATTRIBUTE7'
, 'ATTRIBUTE8'
, 'ATTRIBUTE9'
, 'ATTRIBUTE10'
, 'ATTRIBUTE11'
, 'ATTRIBUTE12'
, 'ATTRIBUTE13'
, 'ATTRIBUTE14'
, 'ATTRIBUTE15'
);
SELECT NAME expression_name
, description
, DBMS_LOB.SUBSTR(sql_select, g_max_field_length) sql_select
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM cn_calc_sql_exps
WHERE org_id = p_org_id
ORDER BY 1;
SELECT COUNT(1)
FROM cn_calc_sql_exps
WHERE org_id = p_org_id;
SELECT COUNT(1)
FROM cn_calc_sql_exps
WHERE DBMS_LOB.getlength(sql_select) > g_max_field_length AND org_id = p_org_id;
SELECT h.NAME
, h.import_type_code
, t.view_name
INTO l_name
, l_type
, l_view_name
FROM cn_imp_headers h, cn_import_types t
WHERE h.imp_header_id = p_imp_header_id AND t.import_type_code = h.import_type_code;
l_data(l_rowcount * 3 + l_rec_num) := EXP.sql_select;
cn_import_client_pvt.insert_data(
p_api_version => 1.0
, p_imp_header_id => p_imp_header_id
, p_import_type_code => l_type
, p_table_name => l_view_name
, p_col_names => l_col_names
, p_data => l_data
, p_row_count => l_rowcount
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
cn_import_pvt.update_imp_headers(p_imp_header_id => p_imp_header_id, p_status_code => 'FAIL'
, p_failed_row => l_rowcount);
cn_import_pvt.update_imp_headers(
p_imp_header_id => p_imp_header_id
, p_status_code => 'COMPLETE'
, p_processed_row => l_rowcount
, p_staged_row => l_rowcount - l_longcount
, p_failed_row => l_longcount
);
UPDATE cn_exp_api_imp_v
SET status_code = 'COMPLETE'
WHERE imp_header_id = p_imp_header_id;
UPDATE cn_exp_api_imp_v
SET status_code = 'FAIL'
, error_msg = my_message
WHERE imp_header_id = p_imp_header_id
AND expression_name IN(SELECT NAME
FROM cn_calc_sql_exps
WHERE DBMS_LOB.getlength(sql_select) > g_max_field_length);
l_sql_select VARCHAR2(32767);
l_piped_sql_select VARCHAR2(32767);
SELECT org_id
, NAME
, description
, expression_disp
, sql_select
, sql_from
, piped_expression_disp
, piped_sql_select
, piped_sql_from
INTO l_org_id
, x_new_expr_name
, l_description
, l_expression_disp
, l_sql_select
, l_sql_from
, l_piped_expression_disp
, l_piped_sql_select
, l_piped_sql_from
FROM cn_calc_sql_exps
WHERE calc_sql_exp_id = p_old_expr_id;
, p_sql_select => l_sql_select
, p_sql_from => l_sql_from
, p_piped_expression_disp => l_piped_expression_disp
, p_piped_sql_select => l_piped_sql_select
, p_piped_sql_from => l_piped_sql_from
, x_calc_sql_exp_id => x_new_expr_id
, x_exp_type_code => x_exp_type_code
, x_status => x_status
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_object_version_number => x_object_version_number
);