The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT extract(value(v),'/OIC_PLAN_COPY') "CP"
FROM cn_copy_requests_all cr,
TABLE(XMLSequence(extract(cr.file_content_xmltype,'/OIC_PLAN_COPY'))) v
WHERE cr.exp_imp_request_id = p_exp_imp_request_id;
SELECT COUNT(extract(value(v),'/CnCompPlansVO'))
FROM cn_copy_requests_all cr,
TABLE(XMLSequence(extract(cr.file_content_xmltype,'/OIC_PLAN_COPY/CnCompPlansVO'))) v
WHERE cr.exp_imp_request_id = p_exp_imp_request_id;
SELECT prefix_info, change_start_date, change_end_date, org_id INTO v_prefix, v_start_date, v_end_date, v_org_id
FROM cn_copy_requests_all
WHERE exp_imp_request_id = p_exp_imp_request_id;
UPDATE cn_copy_requests_all
SET status_code = 'COMPLETED',
completion_date = SYSDATE
WHERE exp_imp_request_id = p_exp_imp_request_id;
UPDATE cn_copy_requests_all
SET status_code = 'FAILED',
completion_date = SYSDATE
WHERE exp_imp_request_id = p_exp_imp_request_id;
UPDATE cn_copy_requests_all
SET status_code = 'FAILED',
completion_date = SYSDATE
WHERE exp_imp_request_id = p_exp_imp_request_id;
UPDATE cn_copy_requests_all
SET status_code = 'FAILED',
completion_date = SYSDATE
WHERE exp_imp_request_id = p_exp_imp_request_id;
SELECT code_combination_id
FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
WHERE account_type = 'E'
AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
AND r.set_of_books_id = glb.set_of_books_id
AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_exp_acc_desc
AND r.org_id = p_org_id;
SELECT code_combination_id
FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
WHERE account_type = 'L'
AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
AND r.set_of_books_id = glb.set_of_books_id
AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_liab_acc_desc
AND r.org_id = p_org_id;
SELECT COUNT(name) INTO l_reuse_count
FROM cn_calc_sql_exps
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
v_expression_rec.sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SqlSelect'),0)));
v_expression_rec.piped_sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedSqlSelect'),0)));
g_miss_calc_edges_tbl.DELETE;
SELECT COUNT(name) INTO l_formula_name_count
FROM cn_calc_formulas
WHERE name = g_miss_calc_edges_tbl(i).child_name
AND org_id = p_org_id;
SELECT calc_formula_id INTO g_miss_calc_edges_tbl(i).child_id
FROM cn_calc_formulas
WHERE name = g_miss_calc_edges_tbl(i).child_name
AND org_id = p_org_id;
v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,l_formula_pkg_source,l_formula_pkg_target);
v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.sql_select,l_formula_pkg_source,l_formula_pkg_target);
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = g_miss_calc_edges_tbl(i).child_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO g_miss_calc_edges_tbl(i).child_id
FROM cn_calc_sql_exps
WHERE name = g_miss_calc_edges_tbl(i).child_name
AND org_id = p_org_id;
l_pe_count := instr(v_expression_rec.piped_sql_select, 'PE.',1);
v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.',g_miss_pe_exp_tbl(i).new_pe_id||'PE.');
v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.',g_miss_pe_exp_tbl(i).new_pe_id||'PE.');
SELECT COUNT(name) INTO l_ee_exist_obj_check
FROM cn_objects
WHERE org_id = p_org_id
AND calc_eligible_flag = 'Y'
AND object_type = 'TBL'
AND name = l_ee_tab_name_new;
p_sql_select => v_expression_rec.sql_select,
p_sql_from => v_expression_rec.sql_from,
p_piped_expression_disp => v_expression_rec.piped_expression_disp,
p_piped_sql_select => v_expression_rec.piped_sql_select,
p_piped_sql_from => v_expression_rec.piped_sql_from,
x_calc_sql_exp_id => l_calc_sql_exp_id,
x_exp_type_code => v_expression_rec.exp_type_code,
x_status => v_expression_rec.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);
SELECT COUNT(name) INTO l_reuse_count
FROM cn_rate_dimensions
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
v_rate_dim_tiers_tbl.DELETE;
g_miss_rate_dim_exp_tbl.DELETE;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).min_exp_id
FROM cn_calc_sql_exps
WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).max_exp_id
FROM cn_calc_sql_exps
WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_reuse_count
FROM cn_rate_schedules
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
v_rate_sch_dims_tbl.DELETE;
SELECT COUNT(name) INTO l_rate_dim_name_count
FROM cn_rate_dimensions
WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
AND org_id = p_org_id;
SELECT rate_dimension_id INTO v_rate_sch_dims_tbl(i).rate_dimension_id
FROM cn_rate_dimensions
WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
AND org_id = p_org_id;
v_rate_tiers_tbl.DELETE;
cn_multi_rate_schedules_pvt.update_rate(
p_rate_schedule_id => l_rate_schedule_id,
p_rate_sequence => v_rate_tiers_tbl(i).p_rate_sequence,
p_commission_amount => v_rate_tiers_tbl(i).p_commission_amount,
p_object_version_number => x_object_version_number,
p_org_id => p_org_id);
SELECT COUNT(name) INTO l_reuse_count
FROM cn_calc_formulas
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = l_output_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_formula_rec.output_exp_id
FROM cn_calc_sql_exps
WHERE name = l_output_exp_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = l_f_output_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_formula_rec.f_output_exp_id
FROM cn_calc_sql_exps
WHERE name = l_f_output_exp_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = l_perf_measure_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_formula_rec.perf_measure_id
FROM cn_calc_sql_exps
WHERE name = l_perf_measure_name
AND org_id = p_org_id;
v_input_exp_tbl.DELETE;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = v_input_exp_tbl(i).calc_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).calc_sql_exp_id
FROM cn_calc_sql_exps
WHERE name = v_input_exp_tbl(i).calc_exp_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_exp_name_count
FROM cn_calc_sql_exps
WHERE name = v_input_exp_tbl(i).f_calc_exp_name
AND org_id = p_org_id;
SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).f_calc_sql_exp_id
FROM cn_calc_sql_exps_all
WHERE name = v_input_exp_tbl(i).f_calc_exp_name
AND org_id = p_org_id;
v_rt_assign_tbl.DELETE;
SELECT COUNT(name) INTO l_rate_schedule_name_count
FROM cn_rate_schedules
WHERE name = v_rt_assign_tbl(i).rate_schedule_name
AND org_id = p_org_id;
SELECT rate_schedule_id INTO v_rt_assign_tbl(i).rate_schedule_id
FROM cn_rate_schedules
WHERE name = v_rt_assign_tbl(i).rate_schedule_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_reuse_count
FROM cn_quotas_v
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_formula_name_count
FROM cn_calc_formulas
WHERE name = v_plan_element_rec.calc_formula_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_int_type_count
FROM cn_interval_types
WHERE name = v_plan_element_rec.interval_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_crd_type_count
FROM cn_credit_types
WHERE name = v_plan_element_rec.credit_type
AND org_id = p_org_id;
SELECT COUNT(lookup_code) INTO l_pmt_group_code
FROM cn_lookups
WHERE lookup_type = 'PAYMENT_GROUP_CODE'
AND lookup_code = v_plan_element_rec.payment_group_code;
v_revenue_class_tbl.DELETE;
SELECT COUNT(name) into l_rev_class_name_count
FROM cn_revenue_classes
WHERE name = l_rev_class_name
AND org_id = p_org_id;
v_rev_uplift_tbl.DELETE;
SELECT COUNT(name) INTO l_rev_class_name_count
FROM cn_revenue_classes
WHERE name = l_rev_class_name
AND org_id = p_org_id;
v_trx_factor_tbl.DELETE;
SELECT COUNT(name) INTO l_rev_class_name_count
FROM cn_revenue_classes
WHERE name = l_rev_class_name
AND org_id = p_org_id;
v_rt_quota_asgns_tbl.DELETE;
SELECT COUNT(name) INTO l_rate_schedule_name_count
FROM cn_rate_schedules
WHERE name = v_rt_quota_asgns_tbl(i).rate_schedule_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_formula_name_count
FROM cn_calc_formulas
WHERE name = v_rt_quota_asgns_tbl(i).calc_formula_name
AND org_id = p_org_id;
v_period_quotas_tbl.DELETE;
SELECT count(period_name) INTO l_period_exist_count
FROM cn_period_statuses
WHERE period_name = l_period_name
AND org_id = p_org_id;
SELECT end_date INTO l_period_end_date
FROM cn_period_statuses
WHERE period_name = l_period_name
AND org_id = p_org_id;
SELECT COUNT(name) INTO l_new_pe_name
FROM cn_quotas_v
WHERE name = v_plan_element_rec.name;
SELECT quota_id INTO g_miss_pe_exp_rec.new_pe_id
FROM cn_quotas_v
WHERE name = v_plan_element_rec.name;
SELECT COUNT(name) INTO l_reuse_count
FROM cn_comp_plans
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
v_quota_assign_tbl.DELETE;
SELECT count(name) INTO l_pe_name_count
FROM cn_quotas_v
WHERE name = v_quota_assign_tbl(i).name;
SELECT quota_id,start_date,end_date
INTO v_quota_assign_tbl(i).quota_id,v_quota_assign_tbl(i).start_date, v_quota_assign_tbl(i).end_date
FROM cn_quotas_v
WHERE name = v_quota_assign_tbl(i).name
AND org_id = p_org_id;