The following lines contain the word 'select', 'insert', 'update' or 'delete':
select dim.dimension_id
,dim.dimension_varchar_label
,xdim.member_b_table_name
,xdim.member_tl_table_name
,xdim.attribute_table_name
,xdim.member_col
,xdim.personal_hierarchy_table_name
from fem_dimensions_b dim
,fem_xdim_dimensions xdim
where xdim.dimension_id = dim.dimension_id
and xdim.hier_editor_managed_flag = 'Y'
and xdim.read_only_flag = 'N'
and xdim.composite_dimension_flag ='N'
and xdim.dimension_active_flag = 'Y'
and (
((c_dimension_id is not null) and (dim.dimension_id = c_dimension_id))
or ( c_dimension_id is null )
)
ORDER BY dim.dimension_id ;
SELECT hierarchy_obj_id
FROM fem_hierarchies
WHERE dimension_id = l_dimension_id
AND hierarchy_usage_code = 'PLANNING'
AND hierarchy_type_code <> 'DAG'
AND personal_flag = 'Y'
AND created_by = l_user_id ;
FEM_HIER_UTILS_PVT.Delete_Hierarchy (
p_api_version => 1.0
,p_hier_table_name => l_pers_hier_table_name
,p_hier_obj_id => l_hier_obj_rec.hierarchy_obj_id
,p_return_status => l_return_status
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
);
delete from fem_dim_attr_grps
where dimension_group_id in (
select dimension_group_id
from fem_dimension_grps_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id
);
delete from fem_dimension_grps_tl
where dimension_group_id in (
select dimension_group_id
from fem_dimension_grps_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id
);
delete from fem_dimension_grps_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id;
delete from fem_dim_attr_versions_tl avt
where exists (
select null
from fem_dim_attr_versions_b avb
,fem_dim_attributes_b ab
where avb.version_id = avt.version_id
--and avb.personal_flag = 'Y'
--and avb.created_by = l_user_id
and ab.attribute_id = avb.attribute_id
and ab.dimension_id = l_dimension_id
and ab.personal_flag = 'Y'
and ab.created_by = l_user_id
);
delete from fem_dim_attr_versions_b
--where personal_flag = 'Y'
--and created_by = l_user_id
where attribute_id in (
select attribute_id
from fem_dim_attributes_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id
);
delete from fem_dim_attributes_tl
where attribute_id in (
select attribute_id
from fem_dim_attributes_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id
);
delete from fem_dim_attributes_b
where dimension_id = l_dimension_id
and personal_flag = 'Y'
and created_by = l_user_id;
delete from fem_cal_periods_attr
where cal_period_id in (
select cal_period_id
from fem_cal_periods_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_cal_periods_tl
where cal_period_id in (
select cal_period_id
from fem_cal_periods_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_cal_periods_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_nat_accts_attr
where natural_account_id in (
select natural_account_id
from fem_nat_accts_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_nat_accts_tl
where natural_account_id in (
select natural_account_id
from fem_nat_accts_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_nat_accts_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_products_attr
where product_id in (
select product_id
from fem_products_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_products_tl
where product_id in (
select product_id
from fem_products_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_products_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_datasets_attr
where dataset_code in (
select dataset_code
from fem_datasets_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_datasets_tl
where dataset_code in (
select dataset_code
from fem_datasets_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_datasets_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_source_systems_tl
where source_system_code in (
select source_system_code
from fem_source_systems_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_source_systems_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_ledgers_attr
where ledger_id in (
select ledger_id
from fem_ledgers_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_ledgers_tl
where ledger_id in (
select ledger_id
from fem_ledgers_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_ledgers_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_cctr_orgs_attr
where company_cost_center_org_id in (
select company_cost_center_org_id
from fem_cctr_orgs_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_cctr_orgs_tl
where company_cost_center_org_id in (
select company_cost_center_org_id
from fem_cctr_orgs_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_cctr_orgs_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_fin_elems_attr
where financial_elem_id in (
select financial_elem_id
from fem_fin_elems_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_fin_elems_tl
where financial_elem_id in (
select financial_elem_id
from fem_fin_elems_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_fin_elems_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_channels_attr
where channel_id in (
select channel_id
from fem_channels_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_channels_tl
where channel_id in (
select channel_id
from fem_channels_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_channels_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_ln_items_attr
where line_item_id in (
select line_item_id
from fem_ln_items_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_ln_items_tl
where line_item_id in (
select line_item_id
from fem_ln_items_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_ln_items_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_projects_attr
where project_id in (
select project_id
from fem_projects_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_projects_tl
where project_id in (
select project_id
from fem_projects_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_projects_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_customers_attr
where customer_id in (
select customer_id
from fem_customers_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_customers_tl
where customer_id in (
select customer_id
from fem_customers_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_customers_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_entities_attr
where entity_id in (
select entity_id
from fem_entities_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_entities_tl
where entity_id in (
select entity_id
from fem_entities_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_entities_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_geography_attr
where geography_id in (
select geography_id
from fem_geography_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_geography_tl
where geography_id in (
select geography_id
from fem_geography_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_geography_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_tasks_attr
where task_id in (
select task_id
from fem_tasks_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_tasks_tl
where task_id in (
select task_id
from fem_tasks_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_tasks_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_budgets_attr
where budget_id in (
select budget_id
from fem_budgets_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_budgets_tl
where budget_id in (
select budget_id
from fem_budgets_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_budgets_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim1_attr
where user_dim1_id in (
select user_dim1_id
from fem_user_dim1_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim1_tl
where user_dim1_id in (
select user_dim1_id
from fem_user_dim1_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim1_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim2_attr
where user_dim2_id in (
select user_dim2_id
from fem_user_dim2_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim2_tl
where user_dim2_id in (
select user_dim2_id
from fem_user_dim2_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim2_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim3_attr
where user_dim3_id in (
select user_dim3_id
from fem_user_dim3_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim3_tl
where user_dim3_id in (
select user_dim3_id
from fem_user_dim3_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim3_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim4_attr
where user_dim4_id in (
select user_dim4_id
from fem_user_dim4_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim4_tl
where user_dim4_id in (
select user_dim4_id
from fem_user_dim4_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim4_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim5_attr
where user_dim5_id in (
select user_dim5_id
from fem_user_dim5_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim5_tl
where user_dim5_id in (
select user_dim5_id
from fem_user_dim5_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim5_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim6_attr
where user_dim6_id in (
select user_dim6_id
from fem_user_dim6_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim6_tl
where user_dim6_id in (
select user_dim6_id
from fem_user_dim6_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim6_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim7_attr
where user_dim7_id in (
select user_dim7_id
from fem_user_dim7_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim7_tl
where user_dim7_id in (
select user_dim7_id
from fem_user_dim7_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim7_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim8_attr
where user_dim8_id in (
select user_dim8_id
from fem_user_dim8_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim8_tl
where user_dim8_id in (
select user_dim8_id
from fem_user_dim8_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim8_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim9_attr
where user_dim9_id in (
select user_dim9_id
from fem_user_dim9_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim9_tl
where user_dim9_id in (
select user_dim9_id
from fem_user_dim9_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim9_b
where personal_flag = 'Y'
and created_by = p_user_id;
delete from fem_user_dim10_attr
where user_dim10_id in (
select user_dim10_id
from fem_user_dim10_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim10_tl
where user_dim10_id in (
select user_dim10_id
from fem_user_dim10_b
where personal_flag = 'Y'
and created_by = p_user_id
);
delete from fem_user_dim10_b
where personal_flag = 'Y'
and created_by = p_user_id;
'delete from '|| p_xdim_rec.attribute_table_name ||
' where ' || p_xdim_rec.member_col ||
' in ( select ' || p_xdim_rec.member_col ||
' from ' || p_xdim_rec.member_b_table_name ||
' where personal_flag = ''Y''' ||
' and created_by = ' || p_user_id ||
' )';
'delete from '|| p_xdim_rec.member_tl_table_name ||
' where ' || p_xdim_rec.member_col ||
' in ( select ' || p_xdim_rec.member_col ||
' from ' || p_xdim_rec.member_b_table_name ||
' where personal_flag = ''Y''' ||
' and created_by = ' || p_user_id ||
' )';
'delete from '|| p_xdim_rec.member_b_table_name ||
' where personal_flag = ''Y''' ||
' and created_by = ' || p_user_id;