[Home] [Help]
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;
SELECT status INTO v_expression_rec.status
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.piped_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);
IF INSTR(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id,1) >0 THEN
v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.','[A'|| l_pe_num ||']PE.');
v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.','[A'|| l_pe_num ||']PE.');
v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,'[A'|| i ||']PE.', v_pe_tbl(i) ||'PE.');
v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select, '[A'|| i ||']PE.', v_pe_tbl(i) ||'PE.');
v_pe_tbl.DELETE;
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;
SELECT calc_sql_exp_id,object_version_number INTO l_calc_sql_exp_id, x_object_version_number
FROM cn_calc_sql_exps
WHERE name = v_name_node_value_new
AND org_id = p_org_id;
CN_PLANCOPY_UTIL_PVT.update_existing_expression(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_update_parent_also => fnd_api.g_false
, p_org_id => p_org_id
, p_calc_sql_exp_id => l_calc_sql_exp_id
, p_name => v_expression_rec.name
, p_description => v_expression_rec.description
, p_expression_disp => v_expression_rec.expression_disp
, -- CLOBs
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
, p_ovn => x_object_version_number
, p_exp_type_code => v_expression_rec.exp_type_code
, p_status => v_expression_rec.status
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
fnd_message.set_name ('CN' , 'Update of existing expression Failed with return status error');
fnd_message.set_name ('CN' , 'Update of existing expression failed with unexpected error');
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 formula_status INTO v_formula_rec.formula_status
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;
--If Plan Element exists then do not Insert otherwise insert a new Record.
IF l_reuse_count > 0 THEN
SELECT quota_status INTO v_plan_element_rec.status
FROM cn_quotas_v
WHERE name=v_name_node_value_new
AND org_id=p_org_id;
SELECT quota_id INTO g_miss_pe_exp_rec.new_pe_id
FROM cn_quotas_v
WHERE name = v_name_node_value_new;
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;
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_revenue_class_tbl.DELETE;
v_trx_factor_tbl.DELETE;
v_rev_uplift_tbl.DELETE;
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_id) into l_period_exist_count from cn_period_statuses where start_date <= p_start_date and end_date >= p_start_date AND org_id = p_org_id AND period_status='O';
select period_id into l_period_id from cn_period_statuses where start_date <= p_start_date and end_date >= p_start_date AND org_id = p_org_id;
SELECT COUNT(PERIOD_ID) INTO l_period_exist_count FROM cn_acc_period_statuses_v WHERE ORG_ID=p_org_id and period_id>l_period_id;
SELECT MIN(PERIOD_ID) INTO l_period_id FROM cn_acc_period_statuses_v WHERE ORG_ID=p_org_id AND period_id>l_period_id;
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;
SELECT status_code INTO v_comp_plan_rec.status_code
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;