[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT formula_exps.exp_type_name exp_type_name,
dbms_lob.substr(piped_sql_from) sql_from,
dbms_lob.substr(piped_sql_select) sql_select,
cse.CALC_SQL_EXP_ID CALC_SQL_EXP_ID
FROM cn_calc_sql_exps_all cse,(SELECT 'PRF' exp_type_name,perf_measure_id exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id
UNION ALL
SELECT 'OUT' exp_type_name,output_exp_id exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id
UNION ALL
SELECT 'INP' exp_type_name,calc_sql_exp_id exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id) formula_exps
WHERE cse.CALC_SQL_EXP_ID = formula_exps.exp_id;
SELECT user_name,schema, name, alias, object_id
FROM cn_objects_all
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 org_id = g_org_id
ORDER BY user_name;
SELECT user_name, name ,object_id ,alias
FROM cn_objects_all
WHERE table_id = p_table_id
AND calc_formula_flag = 'Y'
AND object_type = 'COL'
AND org_id = g_org_id
ORDER BY user_name;
SELECT alias
FROM cn_objects_all
WHERE name = 'CN_COMMISSION_LINES'
AND object_type in ('TBL', 'VIEW')
AND org_id = g_org_id;
parse(expression_rec.sql_select,l_parsed_clmn_tbl);
IF INSTR(LOWER(expression_rec.sql_select),'decode(') >0 THEN
IF NOT g_non_plsql_func_used_tbl.EXISTS(expression_rec.calc_sql_exp_id) THEN
g_non_plsql_func_used_tbl(expression_rec.calc_sql_exp_id) := 'Y';
FUNCTION search_delimiter_select ( p_input_str varchar2, p_start number)
RETURN number IS
l_position_min NUMBER ;
IF sql_type = 'SELECT' THEN
l_next := search_delimiter_select(p_input_str, l_next_prev+1 );
SELECT user INTO X_userid FROM sys.dual;
l pls_integer; -- length of sql_select
SELECT dbms_lob.substr(piped_sql_select) sql_select
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id IN (SELECT perf_measure_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id
UNION ALL
SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id
UNION ALL
SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id);
SELECT dbms_lob.substr(piped_sql_select) sql_select
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id IN (SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id
UNION ALL
SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id);
SELECT rate_dim_sequence
FROM cn_formula_inputs_all
WHERE calc_formula_id = p_formula_id
AND calc_sql_exp_id = g_perf_measure_id;
SELECT calc_formula_id, trx_group_code, itd_flag, cumulative_flag, perf_measure_id, split_flag,
number_dim, formula_type
INTO g_formula_id, g_trx_group_code, g_itd_flag, g_cumulative_flag, g_perf_measure_id, g_split_flag,
g_number_dim, g_formula_type
FROM cn_calc_formulas_all
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id;
g_pe_reference_tbl.delete;
g_pe_id_tbl.delete;
IF (instr(expr.sql_select, 'CSPQ.TOTAL_ROLLOVER', 1,1) > 0) THEN
g_rollover_flag := 'Y';
l := length(expr.sql_select);
b := instr(expr.sql_select, 'PE.', b, 1);
IF not(substr(expr.sql_select, b-1, 1) between '0' and '9') THEN EXIT; END IF;
p1 := instr(expr.sql_select, '(', (b-l), 1);
p2 := instr(expr.sql_select, ')', b, 1);
g_pe_reference_tbl(i) := substr(expr.sql_select, p1+1, p2-p1-1);
g_pe_reference_tbl.DELETE(i);
IF (instr(expr.sql_select, 'CL.', 1, 1) = 1 OR
instr(expr.sql_select, '|CL.', 1, 1) > 0 OR
instr(expr.sql_select, '(CL.', 1, 1) > 0 OR
instr(expr.sql_select, '+CL.', 1, 1) > 0 OR
instr(expr.sql_select, '-CL.', 1, 1) > 0 OR
instr(expr.sql_select, '*CL.', 1, 1) > 0 OR
instr(expr.sql_select, '/CL.', 1, 1) > 0 OR
instr(expr.sql_select, 'CH.', 1, 1) = 1 OR
instr(expr.sql_select, '|CH.', 1, 1) > 0 OR
instr(expr.sql_select, '(CH.', 1, 1) > 0 OR
instr(expr.sql_select, '+CH.', 1, 1) > 0 OR
instr(expr.sql_select, '-CH.', 1, 1) > 0 OR
instr(expr.sql_select, '*CH.', 1, 1) > 0 OR
instr(expr.sql_select, '/CH.', 1, 1) > 0 OR
instr(expr.sql_select, 'p_commission_line_id', 1, 1) > 0)
THEN
g_no_trx_flag := FALSE;
SELECT COUNT(*)
INTO dummy
FROM cn_objects_all
WHERE name = x_name
AND object_type = x_object_type
AND org_id = g_org_id;
cn_objects_pkg.insert_row( x_rowid => x_rowid,
x_object_id => x_object_id,
x_org_id => g_org_id,
x_dependency_map_complete => 'N',
x_name => x_name,
x_description => null,
x_object_type => x_object_type,
x_repository_id => X_repository_id,
x_next_synchronization_date => null,
x_synchronization_frequency => null,
x_object_status => 'A',
x_object_value => NULL );
SELECT object_id INTO x_object_id
FROM cn_objects_all
WHERE name = x_name
AND object_type = x_object_type
AND org_id = g_org_id;
SELECT alias
FROM cn_objects_all
WHERE name = Upper(l_table_name)
AND object_type = 'TBL'
AND org_id = g_org_id;
FUNCTION check_sql_stmt_existence (p_sql_select VARCHAR2,
p_sql_stmt VARCHAR2 )
RETURN BOOLEAN IS
BEGIN
IF instr(p_sql_select, p_sql_stmt) > 0 THEN
RETURN TRUE;
PROCEDURE handle_comm_lines_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_line_alias VARCHAR2(30);
elsif (g_trx_group_code = 'GROUP' and instr(x_sql_select, 'p_commission_line_id', 1, 1) > 0) then
l_line_alias := get_table_alias_from_cn('cn_commission_lines');
x_sql_select := replace(x_sql_select, 'p_commission_line_id', l_line_alias || '.commission_line_id');
PROCEDURE handle_comm_headers_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_line_alias VARCHAR2(30);
PROCEDURE handle_cn_quotas_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_quota_alias VARCHAR2(30);
IF instr(x_sql_select, l_quota_alias||'.target') > 0 THEN
g_pq_target_flag := TRUE;
x_sql_select := replace( x_sql_select, l_quota_alias ||'.target', '1' );
IF instr(x_sql_select, l_quota_alias||'.payment_amount') > 0 THEN
g_pq_payment_flag := TRUE;
x_sql_select := replace( x_sql_select,
l_quota_alias ||'.payment_amount',
l_p_quota_alias ||'.itd_payment' );
END IF; -- there is cn_quotas.target/payment_amount selected
PROCEDURE handle_srp_p_quotas_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_sp_quota_alias VARCHAR2(30);
IF instr(x_sql_select, l_sp_quota_alias||'.period_payment') > 0 THEN
g_spq_payment_flag := TRUE;
x_sql_select := replace( x_sql_select, l_sp_quota_alias ||'.period_payment', l_sp_quota_alias ||'.itd_payment' );
ELSIF (instr(x_sql_select, l_sp_quota_alias || '.itd_payment') > 0) THEN
g_spq_payment_flag := TRUE;
IF instr(x_sql_select, l_sp_quota_alias||'.target_amount') > 0 THEN
g_spq_target_flag := TRUE;
x_sql_select := replace( x_sql_select, l_sp_quota_alias ||'.target_amount', l_sp_quota_alias ||'.itd_payment' );
x_sql_select := replace( x_sql_select, l_sp_quota_alias ||'.target_amount', '1' );
ELSIF (instr(x_sql_select, l_sp_quota_alias || '.itd_target') > 0) THEN
g_spq_target_flag := TRUE;
x_sql_select := REPLACE( x_sql_select, l_sp_quota_alias || '.itd_target', '1');
x_sql_select := REPLACE(x_sql_select, l_sp_quota_alias || '.total_rollover', 0);
PROCEDURE handle_srp_q_assigns_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_sq_assign_alias VARCHAR2(30);
IF (g_itd_flag = 'Y' AND instr(x_sql_select, l_sq_assign_alias || '.payment_amount') > 0) THEN
g_spq_payment_flag := TRUE;
PROCEDURE handle_srp_p_assigns_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_sp_assign_alias VARCHAR2(30);
PROCEDURE handle_cn_salesreps_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_srp_alias VARCHAR2(30);
PROCEDURE handle_cn_srp_periods_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_srp_alias VARCHAR2(30);
PROCEDURE handle_external_tables( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_sql_stmt VARCHAR2(1000);
SELECT lower(obj1.name) internal_column_name, lower(obj2.name) external_column_name
FROM cn_calc_ext_tbl_dtls_all detail,
cn_objects_all obj1,
cn_objects_all obj2
WHERE detail.calc_ext_table_id = l_calc_ext_table_id
AND obj1.object_id = detail.internal_column_id
AND obj1.org_id = detail.org_id
AND obj2.object_id = detail.external_column_id
AND obj2.org_id = detail.org_id;
SELECT map.calc_ext_table_id,
lower(obj.name) internal_table_name,
trim( both ' ' FROM lower(obj.alias) ) internal_table_alias,
lower(l_table_name) external_table_name,
lower(l_table_alias) external_table_alias
FROM cn_objects_all obj,
cn_calc_ext_tables_all map
WHERE lower(map.alias) = l_table_alias
AND map.org_id = g_org_id
AND obj.object_id = map.internal_table_id
AND obj.org_id = map.org_id;
PROCEDURE handle_bonus_ex_tbl_orderby( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2,
p_mode VARCHAR2 )
IS
l_table_name VARCHAR2(30);
l_sql_select VARCHAR2(4000);
SELECT col.table_id, lower(col.name) column_name
FROM cn_calc_ext_tables_all map,
cn_objects_all col
WHERE map.alias = l_table_alias
AND map.org_id = g_org_id
AND col.table_id = map.external_table_id
AND col.org_id = map.org_id
AND col.object_type = 'COL'
AND primary_key = 'Y'
AND position IS NOT NULL
ORDER BY position;
l_sql_select := 'select ' || l_table_alias || '.'|| l_col.column_name ;
l_sql_select := l_sql_select || ', ' || l_table_alias || '.'|| l_col.column_name ;
x_sql_select := l_sql_select;
x_sql_select := x_sql_select || l_sql_select;
PROCEDURE handle_output_sql_select( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 )
IS
l_position NUMBER;
l_position := instr(x_sql_select, 'rateresult');
l_operator_position := search_delimiter_select(x_sql_select, l_position);
x_sql_select := replace(x_sql_select, substr(x_sql_select, l_position, length('rateresult')), 'p_rate');
x_sql_select := replace(x_sql_select, substr(x_sql_select, l_position, length('rateresult')), 'p_rate');
x_sql_select := replace(x_sql_select, substr(x_sql_select, l_position, l_operator_position - l_position +1 ), ' ');
x_sql_select := replace(x_sql_select, substr(x_sql_select, l_position, length('rateresult')), 'p_rate');
x_sql_select := replace(x_sql_select, 'cspq.input_achieved_itd', '(g_input_achieved_itd(1) + g_input_achieved)');
END handle_output_sql_select;
PROCEDURE construct_sql_from_where( x_sql_select IN OUT NOCOPY VARCHAR2,
x_sql_from IN OUT NOCOPY VARCHAR2 ,
x_sql_where IN OUT NOCOPY VARCHAR2 ) IS
BEGIN
handle_external_tables(x_sql_select, x_sql_from, x_sql_where);
handle_comm_lines_where(x_sql_select, x_sql_from, x_sql_where);
handle_comm_headers_where(x_sql_select, x_sql_from, x_sql_where);
handle_cn_quotas_where(x_sql_select, x_sql_from, x_sql_where);
handle_cn_srp_periods_where(x_sql_select, x_sql_from, x_sql_where);
handle_srp_p_quotas_where(x_sql_select, x_sql_from, x_sql_where);
handle_srp_q_assigns_where(x_sql_select, x_sql_from, x_sql_where);
handle_srp_p_assigns_where(x_sql_select, x_sql_from, x_sql_where);
handle_cn_salesreps_where( x_sql_select, x_sql_from, x_sql_where);
cn_utils.appindcr(body_code, ' g_select_status_flag VARCHAR2(30);');
l_input_sql_select varchar2(8000);
l_input_sql_select_clob clob;
SELECT rate_dim_sequence, calc_sql_exp_id, nvl(split_flag, 'N') split_flag, nvl(cumulative_flag, 'N') cumulative_flag
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
ORDER BY rate_dim_sequence;
SELECT dim_unit_code
FROM cn_rate_dimensions_all
WHERE rate_dimension_id = (SELECT rate_dimension_id
FROM cn_rate_sch_dims_all
WHERE rate_dim_sequence = p_rate_dim_sequence
AND rate_schedule_id = (SELECT rate_schedule_id
FROM cn_rt_formula_asgns_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND ROWNUM = 1));
CURSOR l_sql_select_from_csr (l_calc_sql_exp_id NUMBER) IS
SELECT sql_select input_sql_select, sql_from input_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = l_calc_sql_exp_id;
OPEN l_sql_select_from_csr(l_mul_input.calc_sql_exp_id);
FETCH l_sql_select_from_csr INTO l_input_sql_select_clob, l_input_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
cn_utils.appindcr(body_code,'SELECT ');
l_input_sql_select := REPLACE(l_input_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
g_exp_tbl_dtls_tbl(i).variable_name);
l_input_sql_select := REPLACE(l_input_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).table_alias||'_'||g_exp_tbl_dtls_tbl(i).column_name);
l_input_sql_select := REPLACE(l_input_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
l_input_sql_select := REPLACE(l_input_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
split_long_sql( body_code, REPLACE(l_input_sql_select,'select',NULL)||';', 'SELECT');
split_long_sql( body_code, REPLACE(l_input_sql_select,'select',NULL), 'SELECT');
OPEN l_sql_select_from_csr(l_mul_input.calc_sql_exp_id);
FETCH l_sql_select_from_csr INTO l_input_sql_select_clob, l_input_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
OPEN l_sql_select_from_csr(l_mul_input.calc_sql_exp_id);
FETCH l_sql_select_from_csr INTO l_input_sql_select_clob, l_input_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
l_output_sql_select varchar2(8000);
l_output_sql_select_clob clob;
CURSOR l_sql_select_from_csr IS
SELECT calc_sql_exp_id,
sql_select output_sql_select,
sql_from output_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = (SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id);
OPEN l_sql_select_from_csr;
FETCH l_sql_select_from_csr INTO l_out_calc_sql_exp_id,l_output_sql_select_clob, l_output_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_output_sql_select_clob, l_output_sql_select);
l_output_sql_select := lower_str( 'select ' || l_output_sql_select );
g_rate_flag := check_sql_stmt_existence(l_output_sql_select, 'rateresult');
l_operator_position := search_delimiter_select(l_output_sql_select, 1);
l_operator := substr(l_output_sql_select, l_operator_position, 1);
construct_sql_from_where(l_output_sql_select, l_output_sql_from, l_output_sql_where);
handle_output_sql_select(l_output_sql_select, l_output_sql_from, l_output_sql_where);
split_long_sql( body_code, l_output_sql_select, 'SELECT');
cn_utils.appindcr(body_code,'SELECT ');
l_output_sql_select := REPLACE(l_output_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
g_exp_tbl_dtls_tbl(i).variable_name);
l_output_sql_select := REPLACE(l_output_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).table_alias||'_'||g_exp_tbl_dtls_tbl(i).column_name);
l_output_sql_select := REPLACE(l_output_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
l_output_sql_select := REPLACE(l_output_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
split_long_sql( body_code, REPLACE(l_output_sql_select,'select',NULL)||';', 'SELECT');
split_long_sql( body_code, REPLACE(l_output_sql_select,'select',NULL), 'SELECT');
OPEN l_sql_select_from_csr;
FETCH l_sql_select_from_csr INTO l_out_calc_sql_exp_id,l_output_sql_select_clob, l_output_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_output_sql_select_clob, l_output_sql_select);
l_output_sql_select := lower_str( 'select ' || l_output_sql_select );
g_rate_flag := check_sql_stmt_existence(l_output_sql_select, 'rateresult');
construct_sql_from_where(l_output_sql_select, l_output_sql_from, l_output_sql_where);
handle_output_sql_select(l_output_sql_select, l_output_sql_from, l_output_sql_where);
split_long_sql( body_code, l_output_sql_select, 'SELECT');
OPEN l_sql_select_from_csr;
FETCH l_sql_select_from_csr INTO l_out_calc_sql_exp_id,l_output_sql_select_clob, l_output_sql_from_clob;
CLOSE l_sql_select_from_csr;
convert_clob_to_string(l_output_sql_select_clob, l_output_sql_select);
l_output_sql_select := lower_str( 'select ' || l_output_sql_select );
construct_sql_from_where(l_output_sql_select, l_output_sql_from, l_output_sql_where);
handle_output_sql_select(l_output_sql_select, l_output_sql_from, l_output_sql_where);
split_long_sql( body_code, l_output_sql_select, 'SELECT');
l_perf_sql_select varchar2(8000);
l_perf_sql_select_clob clob;
CURSOR l_perf_select_from_csr IS
select sql_select, sql_from
from cn_calc_sql_exps_all
where calc_sql_exp_id = g_perf_measure_id;
CURSOR l_input_select_from_csr IS
SELECT calc_sql_exp_id,
sql_select input_sql_select,
sql_from input_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = (SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND rate_dim_sequence = 1);
OPEN l_perf_select_from_csr;
FETCH l_perf_select_from_csr
INTO l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_perf_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select ' || l_perf_sql_select);
OPEN l_input_select_from_csr;
FETCH l_input_select_from_csr
INTO l_input_sql_exp_id,l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_input_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select ' || l_perf_sql_select);
construct_sql_from_where(l_perf_sql_select,
l_perf_sql_from,
l_perf_sql_where );
split_long_sql( body_code, l_perf_sql_select, 'SELECT');
cn_utils.appindcr(body_code,'SELECT ');
l_perf_sql_select := REPLACE(l_perf_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
g_exp_tbl_dtls_tbl(i).variable_name);
l_perf_sql_select := REPLACE(l_perf_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).table_alias||'_'||g_exp_tbl_dtls_tbl(i).column_name);
l_perf_sql_select := REPLACE(l_perf_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
l_perf_sql_select := REPLACE(l_perf_sql_select,lower(g_exp_tbl_dtls_tbl(i).table_alias||'.'||
g_exp_tbl_dtls_tbl(i).column_name),
'g_commission_rec'||'.'||g_exp_tbl_dtls_tbl(i).column_name);
split_long_sql( body_code, REPLACE(l_perf_sql_select,'select',NULL)||';', 'SELECT');
split_long_sql( body_code, REPLACE(l_perf_sql_select,'select',NULL), 'SELECT');
OPEN l_perf_select_from_csr;
FETCH l_perf_select_from_csr
INTO l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_perf_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select sum( ' || l_perf_sql_select || ' ) ' );
OPEN l_input_select_from_csr;
FETCH l_input_select_from_csr
INTO l_input_sql_exp_id,l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_input_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select ' || l_perf_sql_select );
construct_sql_from_where(l_perf_sql_select,
l_perf_sql_from,
l_perf_sql_where );
split_long_sql( body_code, l_perf_sql_select, 'SELECT');
OPEN l_perf_select_from_csr;
FETCH l_perf_select_from_csr
INTO l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_perf_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select sum( ' || l_perf_sql_select || ' ) ' );
OPEN l_input_select_from_csr;
FETCH l_input_select_from_csr
INTO l_input_sql_exp_id,l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_input_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select ' || l_perf_sql_select );
construct_sql_from_where(l_perf_sql_select,
l_perf_sql_from,
l_perf_sql_where );
split_long_sql( body_code, l_perf_sql_select, 'SELECT');
cn_utils.appindcr(body_code, ' g_select_status_flag);');
cn_utils.appendcr(body_code, ' select ' || REPLACE(g_pe_reference_tbl(i), g_pe_id_tbl(i)||'PE.', ''));
l_sql_select VARCHAR2(8000);
SELECT dbms_lob.substr(sql_select) sql_select
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = (SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND rate_dim_sequence = (SELECT MIN(rate_dim_sequence)
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND cumulative_flag = 'Y'));
FETCH exps INTO l_sql_select;
IF (instr(l_sql_select, '/CSQA.TARGET', 1, 1) > 0 OR
instr(l_sql_select, '/(CSQA.TARGET+CSPQ.TOTAL_ROLLOVER)', 1, 1) > 0 OR
instr(l_sql_select, '/(CSPQ.TOTAL_ROLLOVER+CSQA.TARGET)', 1, 1) > 0) THEN
cn_utils.appendcr(body_code);
cn_utils.appendcr(body_code, ' SELECT csqa.target + nvl(cspq.total_rollover, 0)');
ELSIF (instr(l_sql_select, '/CSPQ.ITD_TARGET', 1, 1) > 0 OR
instr(l_sql_select, '/(CSPQ.ITD_TARGET+CSPQ.TOTAL_ROLLOVER)', 1, 1) > 0 OR
instr(l_sql_select, '/(CSPQ.TOTAL_ROLLOVER+CSPQ.ITD_TARGET)', 1, 1) > 0) THEN
cn_utils.appendcr(body_code);
cn_utils.appendcr(body_code, ' SELECT cspq.itd_target + nvl(cspq.total_rollover, 0)');
cn_utils.appendcr(body_code, ' SELECT cspq.itd_target - g_input_achieved_itd(' || g_cumulative_input_no || ') ');
cn_utils.appendcr(body_code, ' SELECT csqa.target - g_input_achieved_itd(' || g_cumulative_input_no || ') ');
cn_utils.appindcr(body_code, ' SELECT ');
cn_utils.appindcr(body_code, ' AND ( (g_select_status_flag = ''PCX'' and ');
cn_utils.appindcr(body_code, ' OR (g_select_status_flag = ''P'' and cl.status = ''POP'') )');
PROCEDURE update_variables(spec_code IN OUT NOCOPY cn_utils.code_type,
body_code IN OUT NOCOPY cn_utils.code_type )
IS
BEGIN
cn_utils.appendcr(body_code);
END update_variables;
l_output_sql_select varchar2(8000);
l_output_sql_select_clob clob;
l_input_sql_select varchar2(8000);
l_input_sql_select_clob clob;
SELECT calc_sql_exp_id, rate_dim_sequence, split_flag, cumulative_flag
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
ORDER BY rate_dim_sequence;
l_input_sql_select := lower_str( ' select ' ||g_uniq_tbl_names_tbl(l_table_object_id).column_name_list);
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
cn_utils.appindcr(body_code, 'SELECT * ');
update_variables(spec_code, body_code);
cn_utils.appindcr(body_code, ' cn_formula_common_pkg.update_trx(l_trx_rec_old, l_trx_rec_new) ; ');
SELECT sql_select input_sql_select, sql_from input_sql_from
INTO l_input_sql_select_clob, l_input_sql_from_clob
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = itd_input.calc_sql_exp_id
AND org_id = g_org_id;
convert_clob_to_string( l_input_sql_select_clob, l_input_sql_select );
l_input_sql_select := lower_str( 'select ' || l_input_sql_select);
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
cn_utils.appindcr(body_code, ' SELECT least(p.end_date,nvl(spa.end_date,p.end_date),nvl(q.end_date,p.end_date)) ');
SELECT sql_select output_sql_select
INTO l_output_sql_select_clob
FROM cn_calc_sql_exps_all
WHERE org_id = g_org_id
AND calc_sql_exp_id = (SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id);
convert_clob_to_string( l_output_sql_select_clob, l_output_sql_select );
l_output_sql_select := lower_str( 'select ' || l_output_sql_select);
g_rate_flag := check_sql_stmt_existence(l_output_sql_select, 'rateresult');
l_operator_position := search_delimiter_select(l_output_sql_select, 1);
l_operator := substr(l_output_sql_select, l_operator_position, 1);
SELECT sql_select input_sql_select, sql_from input_sql_from
INTO l_input_sql_select_clob, l_input_sql_from_clob
FROM cn_calc_sql_exps_all
WHERE org_id = g_org_id
AND calc_sql_exp_id = (SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id);
convert_clob_to_string( l_input_sql_select_clob, l_input_sql_select );
l_input_sql_select := lower_str( 'select ' || l_input_sql_select);
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
cn_utils.appindcr(body_code, ' cn_formula_common_pkg.update_trx(l_trx_rec_old, l_trx_rec_new) ; ');
cn_utils.appindcr(body_code, ' SELECT least(p.end_date,nvl(spa.end_date,p.end_date),nvl(q.end_date,p.end_date)) ');
cn_utils.appindcr(body_code, ' SELECT least(p.end_date,nvl(spa.end_date,p.end_date),nvl(q.end_date,p.end_date)) into l_processed_date ');
l_input_sql_select varchar2(8000);
l_input_sql_select_clob clob;
cn_utils.appindcr(body_code, ' SELECT ');
l_input_sql_select := lower_str( ' select ' ||g_uniq_tbl_names_tbl(l_table_object_id).column_name_list);
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
cn_utils.appindcr(body_code, 'SELECT * ');
PROCEDURE update_revclass_perf (spec_code IN OUT NOCOPY cn_utils.code_type,
body_code IN OUT NOCOPY cn_utils.code_type )
IS
procedure_name cn_obj_procedures_v.name%TYPE;
l_input_sql_select varchar2(8000);
l_input_sql_select_clob clob;
procedure_name := 'update_revclass_perf';
cn_utils.appindcr(body_code, ' SELECT revenue_class_id, srp_per_quota_rc_id');
select sql_select, sql_from
into l_input_sql_select_clob, l_input_sql_from_clob
from cn_calc_sql_exps_all
where calc_sql_exp_id = g_perf_measure_id
and org_id = g_org_id;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
SELECT sql_select input_sql_select, sql_from input_sql_from
INTO l_input_sql_select_clob, l_input_sql_from_clob
FROM cn_calc_sql_exps_all
WHERE org_id = g_org_id
AND calc_sql_exp_id = (SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND rate_dim_sequence = 1);
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where(l_input_sql_select,
l_input_sql_from ,
l_input_sql_where );
l_input_sql_select := REPLACE(l_input_sql_select, 'select', 'select sum(');
l_input_sql_select := l_input_sql_select || ' ) ';
split_long_sql( body_code, l_input_sql_select, 'SELECT');
cn_utils.appindcr(body_code, ' UPDATE cn_srp_per_quota_rc_all');
'cn.plsql.cn_formula_gen_pkg.update_revclass_perf.exception',
sqlerrm);
END update_revclass_perf;
l_input_sql_select VARCHAR2(8000);
l_input_sql_select_clob clob;
SELECT calc_sql_exp_id, rate_dim_sequence, nvl(split_flag, 'N') split_flag
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
ORDER BY rate_dim_sequence;
SELECT dim_unit_code
FROM cn_rate_dimensions_all
WHERE rate_dimension_id = (SELECT rate_dimension_id
FROM cn_rate_sch_dims_all
WHERE rate_dim_sequence = p_rate_dim_sequence
AND rate_schedule_id = (SELECT rate_schedule_id
FROM cn_rt_formula_asgns_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND ROWNUM = 1));
CURSOR l_input_sql_select_from_csr (l_calc_sql_exp_id NUMBER) IS
SELECT sql_select input_sql_select, sql_from input_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = l_calc_sql_exp_id
AND org_id = g_org_id;
l_output_sql_select varchar2(8000);
l_output_sql_select_clob clob;
CURSOR l_output_select_from_csr IS
SELECT sql_select output_sql_select, sql_from output_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = (SELECT output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id);
CURSOR l_perf_select_from_csr IS
select sql_select, sql_from
from cn_calc_sql_exps_all
where calc_sql_exp_id = g_perf_measure_id;
CURSOR l_inp_perf_select_from_csr IS
SELECT sql_select input_sql_select, sql_from input_sql_from
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = (SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND rate_dim_sequence = 1);
l_perf_sql_select varchar2(8000);
l_perf_sql_select_clob clob;
SELECT lower(name) column_name, data_type
FROM cn_objects_all
WHERE table_id = l_table_id
AND org_id = g_org_id
AND object_type = 'COL'
AND primary_key = 'Y'
AND position IS NOT NULL
ORDER BY position;
OPEN l_input_sql_select_from_csr(l_mul_input.calc_sql_exp_id);
FETCH l_input_sql_select_from_csr
INTO l_input_sql_select_clob, l_input_sql_from_clob;
CLOSE l_input_sql_select_from_csr;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
handle_bonus_ex_tbl_orderby(l_input_sql_select,
l_input_sql_from,
l_input_sql_where,
'INPUT1' );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
OPEN l_input_sql_select_from_csr(l_mul_input.calc_sql_exp_id);
FETCH l_input_sql_select_from_csr
INTO l_input_sql_select_clob, l_input_sql_from_clob;
CLOSE l_input_sql_select_from_csr;
convert_clob_to_string(l_input_sql_select_clob, l_input_sql_select);
l_input_sql_select := lower_str( 'select ' || l_input_sql_select );
construct_sql_from_where (l_input_sql_select,
l_input_sql_from,
l_input_sql_where );
handle_bonus_ex_tbl_orderby(l_input_sql_select,
l_input_sql_from,
l_input_sql_where,
'INPUT0' );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
handle_bonus_ex_tbl_orderby(l_input_sql_select,
l_input_sql_from,
l_input_sql_where,
'INPUT' );
split_long_sql( body_code, l_input_sql_select, 'SELECT');
OPEN l_output_select_from_csr;
FETCH l_output_select_from_csr
INTO l_output_sql_select_clob, l_output_sql_from_clob;
CLOSE l_output_select_from_csr;
convert_clob_to_string(l_output_sql_select_clob, l_output_sql_select);
l_output_sql_select := lower_str( 'select ' || l_output_sql_select );
g_rate_flag := check_sql_stmt_existence(l_output_sql_select, 'rateresult');
construct_sql_from_where(l_output_sql_select,
l_output_sql_from,
l_output_sql_where );
handle_output_sql_select(l_output_sql_select,
l_output_sql_from,
l_output_sql_where );
handle_bonus_ex_tbl_orderby(l_output_sql_select,
l_output_sql_from,
l_output_sql_where,
'OUTPUT' );
split_long_sql( body_code, l_output_sql_select, 'SELECT');
OPEN l_perf_select_from_csr;
FETCH l_perf_select_from_csr
INTO l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_perf_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select sum( ' || l_perf_sql_select || ' ) ' );
OPEN l_inp_perf_select_from_csr;
FETCH l_inp_perf_select_from_csr
INTO l_perf_sql_select_clob, l_perf_sql_from_clob;
CLOSE l_inp_perf_select_from_csr;
convert_clob_to_string( l_perf_sql_select_clob, l_perf_sql_select);
l_perf_sql_select := lower_str('select sum( ' || l_perf_sql_select || ' ) ' );
construct_sql_from_where(l_perf_sql_select,
l_perf_sql_from,
l_perf_sql_where );
handle_bonus_ex_tbl_orderby(l_perf_sql_select,
l_perf_sql_from,
l_perf_sql_where,
'PERF' );
split_long_sql( body_code, l_perf_sql_select, 'SELECT');
update_variables(spec_code, body_code);
l_sql_select VARCHAR2(8000);
SELECT f_output_exp_id
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id;
SELECT rate_dim_sequence, f_calc_sql_exp_id, cumulative_flag, split_flag
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id;
SELECT dim_unit_code
FROM cn_rate_dimensions_all
WHERE rate_dimension_id = (SELECT rate_dimension_id
FROM cn_rate_sch_dims_all
WHERE rate_dim_sequence = p_rate_dim_sequence
AND rate_schedule_id = (SELECT rate_schedule_id
FROM cn_rt_formula_asgns_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id
AND ROWNUM = 1));
SELECT dbms_lob.substr(sql_select), dbms_lob.substr(sql_from)
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = p_calc_sql_exp_id;
FETCH sql_statement INTO l_sql_select, l_sql_from;
l_sql_select := REPLACE(l_sql_select, 'ForecastAmount', 'p_forecast_amount');
l_sql_select := 'select ' || lower_str(l_sql_select);
construct_sql_from_where(l_sql_select, l_sql_from, l_sql_where);
split_long_sql(body_code, l_sql_select, 'SELECT');
cn_utils.appindcr(body_code, ' SELECT l_itd_target + total_rollover ');
FETCH sql_statement INTO l_sql_select, l_sql_from;
l_sql_select := REPLACE(l_sql_select, 'ForecastAmount', 'p_forecast_amount');
l_sql_select := lower_str( 'select ' || l_sql_select);
g_rate_flag := check_sql_stmt_existence(l_sql_select, 'rateresult');
l_operator_position := search_delimiter_select(l_sql_select, 1);
l_operator := substr(l_sql_select, l_operator_position, 1);
cn_utils.appindcr(body_code, ' SELECT end_date into l_processed_date ');
construct_sql_from_where(l_sql_select, l_sql_from, l_sql_where);
handle_output_sql_select(l_sql_select, l_sql_from, l_sql_where);
split_long_sql(body_code, l_sql_select, 'SELECT');
FUNCTION get_sql_select(p_piped_sql_select IN VARCHAR2) RETURN VARCHAR2
IS
l_piped_sql_select varchar2(8000);
l_piped_sql_select_copy varchar2(8000);
l_sql_select varchar2(8000);
l_piped_sql_select := p_piped_sql_select;
l_piped_sql_select_copy := l_piped_sql_select;
l_pipe_found := INSTR(l_piped_sql_select_copy,'|', l_begin);
l_segment_orig := substr(l_piped_sql_select_copy, l_begin, l_pipe_found-l_begin );
SELECT DECODE(l_segment_orig,
'CH.TRANSACTION_AMOUNT', 'p_est_achievement',
'CH.QUANTITY', 'p_est_achievement',
'CSQA.TARGET', l_target_replace,
'CSQA.PAYMENT_AMOUNT', 'payment_amount',
l_segment_orig
)
INTO l_segment
FROM dual;
l_piped_sql_select := REPLACE(l_piped_sql_select,l_segment_orig, l_segment);
l_sql_select := REPLACE(l_piped_sql_select,'|', '');
RETURN l_sql_select;
'cn.plsql.cn_formula_gen_pkg.get_sql_select.exception',
sqlerrm);
l_sql_select VARCHAR2(8000);
l_piped_sql_select VARCHAR2(8000);
SELECT output_exp_id, modeling_flag
FROM cn_calc_formulas_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id;
SELECT calc_sql_exp_id
FROM cn_formula_inputs_all
WHERE calc_formula_id = g_formula_id
AND org_id = g_org_id;
SELECT dbms_lob.substr(piped_sql_select), dbms_lob.substr(sql_select), dbms_lob.substr(sql_from)
FROM cn_calc_sql_exps_all
WHERE calc_sql_exp_id = p_calc_sql_exp_id;
FETCH sql_statement INTO l_piped_sql_select, l_sql_select, l_sql_from;
l_sql_select := get_sql_select(l_piped_sql_select);
l_sql_select := lower_str('select ' || l_sql_select);
split_long_sql(body_code, l_sql_select, 'SELECT');
FETCH sql_statement INTO l_piped_sql_select, l_sql_select, l_sql_from;
l_sql_select := get_sql_select(l_piped_sql_select);
l_sql_select := lower_str('select ' || l_sql_select);
g_rate_flag := check_sql_stmt_existence(l_sql_select, 'rateresult');
l_sql_select := REPLACE(l_sql_select, 'rateresult', 'l_rate');
split_long_sql(body_code, l_sql_select, 'SELECT');
SELECT repository_id
INTO l_repository_id
FROM cn_repositories_all
WHERE org_id = g_org_id;
update_revclass_perf(spec_code, body_code);
cn_message_pkg.debug( 'after update_revclass_perf ');
'After generating update_revclass_perf in create_formula');
SELECT co.object_id
INTO l_pkg_object_id
FROM cn_objects_all co
WHERE co.name = l_formula_name
AND co.object_type = 'PKS'
AND co.org_id = g_org_id;
SELECT cs.text bulk collect INTO sqlstring
FROM cn_source_all cs
WHERE cs.object_id = l_pkg_object_id
AND cs.org_id = g_org_id
ORDER BY cs.line_no ;
SELECT co.object_id
INTO l_pkg_object_id
FROM cn_objects_all co
WHERE co.name = l_formula_name
AND co.object_type = 'PKB'
AND co.org_id = g_org_id;
SELECT cs.text bulk collect INTO sqlstring
FROM cn_source_all cs
WHERE cs.object_id = l_pkg_object_id
AND cs.org_id = g_org_id
ORDER BY cs.line_no ;
SELECT COUNT(*)
INTO l_error_count
FROM user_errors
WHERE name = 'CN_FORMULA_'|| abs(p_formula_id) || '_' || abs(g_org_id) || '_PKG'
AND TYPE IN ('PACKAGE', 'PACKAGE BODY');
UPDATE cn_calc_formulas_all
SET formula_status = 'COMPLETE'
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id;
UPDATE cn_calc_formulas_all
SET formula_status = 'INCOMPLETE'
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id;
UPDATE cn_calc_formulas_all
SET formula_status = 'INCOMPLETE'
WHERE calc_formula_id = p_formula_id
AND org_id = g_org_id;
SELECT calc_formula_id, org_id
FROM cn_calc_formulas
WHERE org_id = nvl(p_org_id, org_id);
select object_name || ' ' ||
decode(object_type, 'PACKAGE BODY','compile body','PACKAGE','compile') stmt
from user_objects
where object_name like 'CN_FORMULA%PKG'
and substr(object_name, 12, 1)in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
and object_type like 'PACKAGE%'
and status = 'INVALID';
select object_name stmt
from user_objects
where object_name like 'CN_FORMULA%PKG'
and substr(object_name, 12, 1)in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
and object_type like 'PACKAGE%'
and status = 'INVALID';