[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select published_ac_id
from zpb_cycle_relationships
where editable_ac_id = p_analysis_cycle_id;
select params.value
from zpb_ac_param_values params, fnd_lookup_values_vl fnd
where params.param_id = fnd.TAG and fnd.LOOKUP_CODE = 'BUSINESS_PROCESS_CURRENCY'
and fnd.LOOKUP_TYPE = 'ZPB_PARAMS' and params.analysis_cycle_id = l_pub_ac_id ;
select params.value
from zpb_ac_param_values params, fnd_lookup_values_vl fnd
where params.param_id = fnd.TAG and fnd.LOOKUP_CODE = 'BUSINESS_PROCESS_CURRENCY'
and fnd.LOOKUP_TYPE = 'ZPB_PARAMS' and params.analysis_cycle_id = l_edt_ac_id ;
select '1'
from zpb_ac_param_values pub, zpb_ac_param_values edt
where edt.analysis_cycle_id = l_edt_ac_id
and pub.analysis_cycle_id = l_pub_ac_id
and pub.param_id = l_param_id
and edt.param_id = l_param_id
and pub.value <> edt.value;
select '1'
from zpb_cycle_datasets pub
where pub.analysis_cycle_id = l_pub_ac_id
and pub.dataset_code not in (select edt.dataset_code
from zpb_cycle_datasets edt
where edt.analysis_cycle_id = l_edt_ac_id
and edt.order_id = pub.order_id
)
union
select '1'
from zpb_cycle_datasets edt
where edt.analysis_cycle_id = l_edt_ac_id
and edt.dataset_code not in (select pub.dataset_code
from zpb_cycle_datasets pub
where pub.analysis_cycle_id = l_pub_ac_id
and edt.order_id = pub.order_id
);
select dimension_name,dataset_dimension_flag,remove_dimension_flag
from zpb_cycle_model_dimensions
where analysis_cycle_id = p_analysis_cycle_id
minus
select dimension_name,dataset_dimension_flag,remove_dimension_flag
from zpb_cycle_model_dimensions
where analysis_cycle_id = l_pub_ac_id;
select dimension_name,dataset_dimension_flag,remove_dimension_flag
from zpb_cycle_model_dimensions
where analysis_cycle_id = l_pub_ac_id
minus
select dimension_name,dataset_dimension_flag,remove_dimension_flag
from zpb_cycle_model_dimensions
where analysis_cycle_id = p_analysis_cycle_id;
select query_object_path|| '/' || query_object_name
from zpb_cycle_model_dimensions
where dimension_name = l_line_dim
and analysis_cycle_id = l_ac_id;
select 1 from
zpb_solve_member_defs pub, zpb_solve_member_defs edt
where edt.analysis_cycle_id = p_analysis_cycle_id
and pub.analysis_cycle_id = l_pub_ac_id
and pub.member = edt.member
and edt.source_type <> pub.source_type;
select 1 from
zpb_data_initialization_defs pub, zpb_data_initialization_defs edt
where edt.analysis_cycle_id = p_analysis_cycle_id
and pub.analysis_cycle_id = l_pub_ac_id
and pub.member = edt.member
and ( edt.source_view <> pub.source_view
or nvl(edt.lag_time_periods,-1) <> nvl(pub.lag_time_periods,-1)
or nvl(edt.lag_time_level,'-1') <> nvl(pub.lag_time_level,'-1')
or nvl(edt.change_number,-1) <> nvl(pub.change_number,-1)
or nvl(edt.percentage_flag, '-1') <> nvl(pub.percentage_flag, '-1')
);
cursor input_selections_edt is
select '1'
from (select member, dimension,selection_name,hierarchy
from zpb_solve_input_selections
where analysis_cycle_id = p_analysis_cycle_id
minus
select member, dimension,selection_name,hierarchy
from zpb_solve_input_selections
where analysis_cycle_id = l_pub_ac_id);
cursor input_selections_pub is
select '1'
from (select member, dimension,selection_name,hierarchy
from zpb_solve_input_selections
where analysis_cycle_id = l_pub_ac_id
minus
select member, dimension,selection_name,hierarchy
from zpb_solve_input_selections
where analysis_cycle_id = p_analysis_cycle_id);
cursor input_selection_identifier(l_ac_id in number) is
select member,dimension,selection_path|| '/' || selection_name
from zpb_solve_input_selections
where analysis_cycle_id = l_ac_id
and selection_name <> 'DEFAULT';
cursor input_selection_ident_pub(l_ac_id in number,l_dim_name in varchar2,l_member in varchar2) is
select selection_path|| '/' || selection_name
from zpb_solve_input_selections
where analysis_cycle_id = l_ac_id
and dimension = l_dim_name
and member = l_member;
cursor output_selections_edt is
select '1'
from (select member, dimension,selection_name,hierarchy,match_input_flag
from zpb_solve_output_selections
where analysis_cycle_id = p_analysis_cycle_id
minus
select member, dimension,selection_name, hierarchy,match_input_flag
from zpb_solve_output_selections
where analysis_cycle_id = l_pub_ac_id);
cursor output_selections_pub is
select '1'
from (select member, dimension,selection_name,hierarchy,match_input_flag
from zpb_solve_output_selections
where analysis_cycle_id = l_pub_ac_id
minus
select member, dimension,selection_name, hierarchy,match_input_flag
from zpb_solve_output_selections
where analysis_cycle_id = p_analysis_cycle_id);
select '1'
from (select member,rule_name,method,basis,qualifier
from zpb_solve_allocation_defs
where analysis_cycle_id = p_analysis_cycle_id
minus
select member,rule_name,method,basis,qualifier
from zpb_solve_allocation_defs
where analysis_cycle_id = l_pub_ac_id);
select '1'
from (select member,rule_name,method,basis,qualifier,evaluation_option
from zpb_solve_allocation_defs
where analysis_cycle_id = l_pub_ac_id
minus
select member,rule_name,method,basis,qualifier,evaluation_option
from zpb_solve_allocation_defs
where analysis_cycle_id = p_analysis_cycle_id);
select '1'
from zpb_analysis_cycle_tasks edt,
zpb_analysis_cycle_tasks pub
where edt.analysis_cycle_id = l_edt_ac_id
and pub.analysis_cycle_id = l_pub_ac_id
and edt.sequence = pub.sequence
and edt.task_name <> pub.task_name;
select '1'
from zpb_analysis_cycle_tasks pub
where sequence not in (select sequence
from zpb_analysis_cycle_tasks edt
where edt.analysis_cycle_id = l_edt_ac_id)
and pub.analysis_cycle_id = l_pub_ac_id;
select status_sql
from zpb_status_sql
where query_path = l_query_path
and dimension_name = l_line_dim
order by row_num;
open output_selections_edt;
fetch output_selections_edt into dummy_var;
if (output_selections_edt%found) then
close output_selections_edt;
close output_selections_edt;
open output_selections_pub;
fetch output_selections_pub into dummy_var;
if (output_selections_pub%found) then
close output_selections_pub;
close output_selections_pub;
zpb_aw.execute('delete l_temp_vs aw ' ||l_dataAw);
select query_object_path|| '/' || query_object_name
from zpb_cycle_model_dimensions
where dimension_name = l_line_dim
and analysis_cycle_id = p_analysis_cycle_id;
l_selection_type varchar2(30);
select task_id
from zpb_analysis_cycle_tasks
where analysis_cycle_id = p_analysis_cycle_id
and wf_process_name = 'LOAD_DATA';
select name,value
from zpb_task_parameters
where task_id = l_task_id
and name in ('QUERY_OBJECT_PATH','QUERY_OBJECT_NAME', 'DATA_SELECTION_TYPE'
);
if (each.name = 'DATA_SELECTION_TYPE') then
l_name_exists := 'y';
l_selection_type := each.value;
if l_path_exists <> 'y' or l_name_exists <> 'y' or l_selection_type = 'ALL_LINE_ITEMS_SELECTION_TYPE' then
x_isvalid := 'Y';
l_selection_type varchar2(30);
select task_id,task_name
from zpb_analysis_cycle_tasks
where analysis_cycle_id = p_analysis_cycle_id
and wf_process_name = 'LOAD_DATA';
select name,value
from zpb_task_parameters
where task_id = l_task_id
and name in ('QUERY_OBJECT_PATH','QUERY_OBJECT_NAME','DATA_SELECTION_TYPE');
if (each.name = 'DATA_SELECTION_TYPE') then
l_name_exists := 'y';
l_selection_type := each.value;
if l_selection_type <> 'ALL_LINE_ITEMS_SELECTION_TYPE' then
l_query_name := l_query_path ||'/' || l_query_name;
end if; -- all_line_items_selection_type
select task_id, task_name
from zpb_analysis_cycle_tasks
where analysis_cycle_id = p_analysis_cycle_id
and wf_process_name = 'GENERATE_TEMPLATE';
select name,value
from zpb_task_parameters
where task_id = l_task_id
and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
PROCEDURE validate_input_selections(
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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
p_inputDims IN VARCHAR2,
x_isvalid OUT NOCOPY VARCHAR2,
x_invalid_dim_list OUT NOCOPY VARCHAR2,
x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_input_selections';
l_currentDim zpb_solve_input_selections.dimension%type ;
l_fetchedDim zpb_solve_input_selections.dimension%type;
l_inputSelection zpb_solve_input_selections.selection_name%type;
l_currentLine zpb_solve_input_selections.member%type;
select member
from zpb_solve_member_defs
where analysis_cycle_id = p_analysis_cycle_id
and source_type in (1000,1100,1130)
and member not in (select member
from zpb_solve_input_selections
where analysis_cycle_id = p_analysis_cycle_id);
cursor nullselections_c (p_dim in varchar2, p_time_dim in varchar2) is
select i.member,i.dimension, i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d,
zpb_line_dimensionality l
where d.member = i.member
and d.analysis_cycle_id = i.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and l.dimension = i.dimension
and l.member = i.member
and l.analysis_cycle_id = i.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000,1100,1130)
and i.dimension = p_time_dim
and i.dimension = p_dim
and i.selection_name is null
union all
select i.member, i.dimension,i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d,
zpb_line_dimensionality l
where d.member = i.member
and d.analysis_cycle_id = i.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and l.dimension = i.dimension
and l.member = i.member
and l.analysis_cycle_id = i.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000)
and i.dimension = p_dim
and i.dimension <> p_time_dim
and i.selection_name is null
union all
select i.member, i.dimension,i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d
where d.member = i.member
and d.analysis_cycle_id = i.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and d.source_type in (1100,1130)
and i.dimension = p_dim
and i.dimension <> p_time_dim
and i.selection_name is null;
cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
select distinct i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d,
zpb_line_dimensionality l
where i.member = d.member
and i.dimension = p_time_dim
and i.dimension = p_dim
and i.analysis_cycle_id = d.analysis_cycle_id
and l.dimension = i.dimension
and l.member = i.member
and l.analysis_cycle_id = i.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000,1100,1130)
and i.analysis_cycle_id = p_analysis_cycle_id
and i.selection_name is not null
union all
select distinct i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d,
zpb_line_dimensionality l
where i.member = d.member
and i.dimension <> p_time_dim
and i.dimension = p_dim
and i.analysis_cycle_id = d.analysis_cycle_id
and l.dimension = i.dimension
and l.member = i.member
and l.analysis_cycle_id = i.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000)
and i.analysis_cycle_id = p_analysis_cycle_id
and i.selection_name is not null
union all
select distinct i.selection_name
from zpb_solve_input_selections i, zpb_solve_member_defs d
where i.member = d.member
and i.dimension <> p_time_dim
and i.dimension = p_dim
and i.analysis_cycle_id = d.analysis_cycle_id
and d.source_type in (1100,1130)
and i.analysis_cycle_id = p_analysis_cycle_id
and i.selection_name is not null;
select distinct o.hierarchy
from zpb_solve_output_selections o
where o.analysis_cycle_id = p_analysis_cycle_id
and o.hierarchy <> 'NONE'
and o.dimension = p_dim
and o.member=p_input_line
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
l_hierarchy in varchar2) is
select i.member
from zpb_solve_input_selections i,
zpb_solve_output_selections o
where i.dimension = p_dim
and i.analysis_cycle_id = p_analysis_cycle_id
and i.member = o.member
and i.analysis_cycle_id = o.analysis_cycle_id
and i.selection_name = l_selection_name
and o.hierarchy = l_hierarchy
and i.dimension = o.dimension
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is
select member
from zpb_solve_input_selections
where selection_name = l_selection_name
and dimension = p_dim
and analysis_cycle_id = p_analysis_cycle_id;
SAVEPOINT validate_input_selections;
open nullselections_c(l_currentDim, l_timedim);
fetch nullselections_c into l_currentLine, l_fetchedDim, l_inputSelection;
while nullselections_c%found loop
-- dbms_output.put_line('found null sel ' || l_currentDim || l_currentLine);
fetch nullselections_c into l_currentLine,l_fetchedDim, l_inputSelection;
close nullselections_c;
open nonnullselections_c(l_currentDim, l_timedim);
fetch nonnullselections_c into l_inputSelection;
while nonnullselections_c%found loop
-- get a line member that corresponds to this input selection.
-- this line member will be used to limit the input selection valueset
open selection_member_c(l_currentDim,l_inputSelection);
fetch selection_member_c into l_currentLine;
close selection_member_c;
|| l_currentLine||l_currentDim||l_inputSelection );
open hiermember_c(l_currentDim,l_inputSelection, l_hierarchy);
fetch nonnullselections_c into l_inputSelection;
end loop; -- loop over input selections
close nonnullselections_c;
ROLLBACK TO validate_input_selections;
ROLLBACK TO validate_input_selections;
ROLLBACK TO validate_input_selections;
end validate_input_selections;
l_dim zpb_solve_output_selections.dimension%type;
l_currentLine zpb_solve_output_selections.member%type;
l_hierarchy zpb_solve_output_selections.hierarchy%type;
l_outputSelection zpb_solve_output_selections.selection_name%type;
l_currentDim zpb_solve_output_selections.dimension%type;
cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where o.member = d.member
and o.dimension = p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and l.dimension = o.dimension
and l.member = o.member
and l.analysis_cycle_id = o.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000,1100,1130)
and o.analysis_cycle_id = p_analysis_cycle_id
and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where o.member = d.member
and o.dimension <> p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and l.dimension = o.dimension
and l.member = o.member
and l.analysis_cycle_id = o.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000)
and o.analysis_cycle_id = p_analysis_cycle_id
and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d
where o.member = d.member
and o.dimension = p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and d.source_type in (1100,1130)
and o.analysis_cycle_id = p_analysis_cycle_id
and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is
select o.member
from zpb_solve_output_selections o, zpb_solve_member_defs d
where o.selection_name = l_selection_name
and o.member = d.member
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and d.source_type in (1000,1100,1130)
and o.analysis_cycle_id = p_analysis_cycle_id
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
l_hierarchy in varchar2) is
select o.member
from zpb_solve_output_selections o
where o.dimension = p_dim
and o.analysis_cycle_id = p_analysis_cycle_id
and o.selection_name = l_selection_name
and o.hierarchy = l_hierarchy
AND NVL(o.match_input_flag, 'N') <> 'Y';
open nonnullselections_c(l_currentDim, l_timedim);
fetch nonnullselections_c into l_outputSelection, l_hierarchy;
while nonnullselections_c%found loop
-- get a line member that corresponds to this input selection.
-- this line member will be used to limit the input selection valueset
open selection_member_c(l_currentDim,l_outputSelection);
fetch selection_member_c into l_currentLine;
close selection_member_c;
|| l_currentLine||l_currentDim||l_outputSelection );
open hiermember_c(l_currentDim,l_outputSelection, l_hierarchy);
fetch nonnullselections_c into l_outputSelection, l_hierarchy;
end loop; -- loop over output selections
close nonnullselections_c;
select query_object_path|| '/' || query_object_name
from zpb_cycle_model_dimensions
where dimension_name = l_line_dim
and analysis_cycle_id = p_analysis_cycle_id;
select task_id, task_name
from zpb_analysis_cycle_tasks
where analysis_cycle_id = p_analysis_cycle_id
and wf_process_name = 'GENERATE_TEMPLATE';
select name,value
from zpb_task_parameters
where task_id = l_task_id
and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
l_selection_type varchar2(30);
select task_id
from zpb_analysis_cycle_tasks
where analysis_cycle_id = p_analysis_cycle_id
and wf_process_name = 'GENERATE_TEMPLATE';
select name,value
from zpb_task_parameters
where task_id = l_task_id
and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
l_dim zpb_solve_output_selections.dimension%type;
l_line_mem zpb_solve_output_selections.member%type;
l_hierarchy zpb_solve_output_selections.hierarchy%type;
l_output_selection_name zpb_solve_output_selections.selection_name%type;
l_input_selection_name zpb_solve_input_selections.selection_name%type;
select o.member, o.dimension, o.hierarchy, o.selection_name,
m.source_type
from zpb_solve_output_selections o, zpb_solve_member_defs m
where m.analysis_cycle_id = p_analysis_cycle_id
and m.analysis_cycle_id = o.analysis_cycle_id
and m.member = o.member
and m.source_type <> 1200;
select selection_name
from zpb_solve_input_selections
where analysis_cycle_id = p_analysis_cycle_id
and member = p_line_mem
and dimension = p_dim;
fetch output_info into l_line_mem, l_dim, l_hierarchy, l_output_selection_name,
l_source_type;
fetch input_info into l_input_selection_name;
''','''||l_input_selection_name ||
''','''||l_output_selection_name ||
''','''||l_hierarchy ||
''','''||l_dim||''')');
fetch input_info into l_input_selection_name;
PROCEDURE validate_output_selections(
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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
p_outputDims IN VARCHAR2,
x_isvalid OUT NOCOPY VARCHAR2,
x_invalid_dim_list OUT NOCOPY VARCHAR2,
x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_output_selections';
l_currentDim zpb_solve_output_selections.dimension%type ;
l_fetchedDim zpb_solve_output_selections.dimension%type;
l_outputSelection zpb_solve_output_selections.selection_name%type;
l_currentLine zpb_solve_output_selections.member%type;
select member
from zpb_solve_member_defs
where analysis_cycle_id = p_analysis_cycle_id
and source_type in (1000,1100,1130)
and member not in (select member
from zpb_solve_output_selections
where analysis_cycle_id = p_analysis_cycle_id);
cursor nullselections_c (p_dim in varchar2, p_time_dim in varchar2) is
select o.member,o.dimension, o.selection_name
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where d.member = o.member
and d.analysis_cycle_id = o.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and l.dimension = o.dimension
and l.member = o.member
and l.analysis_cycle_id = o.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000,1100,1130)
and o.dimension = p_time_dim
and o.dimension = p_dim
and o.selection_name is null
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select o.member, o.dimension,o.selection_name
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where d.member = o.member
and d.analysis_cycle_id = o.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and l.dimension = o.dimension
and l.member = o.member
and l.analysis_cycle_id = o.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000)
and o.dimension = p_dim
and o.dimension <> p_time_dim
and o.selection_name is null
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select o.member, o.dimension,o.selection_name
from zpb_solve_output_selections o, zpb_solve_member_defs d
where d.member = o.member
and d.analysis_cycle_id = o.analysis_cycle_id
and d.analysis_cycle_id = p_analysis_cycle_id
and d.source_type in (1100,1130)
and o.dimension = p_dim
and o.dimension <> p_time_dim
and o.selection_name is null
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where o.member = d.member
and o.dimension = p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and l.dimension = o.dimension
and l.member = o.member
and l.analysis_cycle_id = o.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000,1100,1130)
and o.analysis_cycle_id = p_analysis_cycle_id
and o.selection_name is not null
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d,
zpb_line_dimensionality l
where o.member = d.member
and o.dimension <> p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and o.dimension = l.dimension
and o.member = l.member
and o.analysis_cycle_id = l.analysis_cycle_id
and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
and ( nvl(l.force_input_flag,'N') = 'Y'
OR nvl(l.sum_members_flag,'N') = 'N')
and d.source_type in (1000)
and o.analysis_cycle_id = p_analysis_cycle_id
and o.selection_name is not null
AND NVL(o.match_input_flag, 'N') <> 'Y'
union all
select distinct o.selection_name, o.hierarchy
from zpb_solve_output_selections o, zpb_solve_member_defs d
where o.member = d.member
and o.dimension <> p_time_dim
and o.dimension = p_dim
and o.analysis_cycle_id = d.analysis_cycle_id
and d.source_type in (1100,1130)
and o.analysis_cycle_id = p_analysis_cycle_id
and o.selection_name is not null
AND NVL(o.match_input_flag, 'N') <> 'Y';
cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is select a.member
from zpb_solve_output_selections a, zpb_line_dimensionality b,
zpb_solve_member_defs c
where a.member = b.member
AND a.dimension = b.dimension
AND a.member = c.member
AND a.analysis_cycle_id = c.analysis_cycle_id
AND a.analysis_cycle_id = b.analysis_cycle_id
AND b.analysis_cycle_id = p_analysis_cycle_id
AND c.source_type = 1000
AND b.exclude_from_solve_flag = 'N'
AND selection_name = l_selection_name
AND a.dimension= p_dim
AND NVL(a.match_input_flag, 'N') <> 'Y'
union
select a.member
from zpb_solve_output_selections a,
zpb_solve_member_defs b
where a.member = b.member
AND a.analysis_cycle_id = b.analysis_cycle_id
AND b.analysis_cycle_id = p_analysis_cycle_id
AND b.source_type <> 1000
AND selection_name = l_selection_name
AND a.dimension= p_dim
AND NVL(a.match_input_flag, 'N') <> 'Y';
cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
l_hierarchy in varchar2) is
select o.member
from zpb_solve_output_selections o
where o.dimension = p_dim
and o.analysis_cycle_id = p_analysis_cycle_id
and o.selection_name = l_selection_name
and o.hierarchy = l_hierarchy
AND NVL(o.match_input_flag, 'N') <> 'Y';
SAVEPOINT validate_output_selections;
open nullselections_c(l_currentDim, l_timedim);
fetch nullselections_c into l_currentLine, l_fetchedDim, l_outputSelection;
while nullselections_c%found loop
l_dimValid := 'N';
fetch nullselections_c into l_currentLine,l_fetchedDim, l_outputSelection;
close nullselections_c;
open nonnullselections_c(l_currentDim, l_timedim);
fetch nonnullselections_c into l_outputSelection,l_hierarchy;
while nonnullselections_c%found loop
-- get a line member that corresponds to this output selection.
-- this line member will be used to limit the output selection valueset
open selection_member_c(l_currentDim,l_outputSelection);
fetch selection_member_c into l_currentLine;
close selection_member_c;
open hiermember_c(l_currentDim,l_outputSelection, l_hierarchy);
fetch nonnullselections_c into l_outputSelection, l_hierarchy;
end loop; -- loop over output selections
close nonnullselections_c;
ROLLBACK TO validate_output_selections;
ROLLBACK TO validate_output_selections;
ROLLBACK TO validate_output_selections;
end validate_output_selections;
PROCEDURE val_solve_hrzselections(
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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
p_hrz_level IN VARCHAR2,
x_isvalid OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'val_solve_hrzselections';
l_hierarchy zpb_solve_output_selections.hierarchy%type;
TYPE selections_cur is REF CURSOR;
input_selections_cur selections_cur;
output_selections_cur selections_cur;
SAVEPOINT val_solve_hrzselections;
sql_stmt := ' select member from ' ||
' zpb_solve_input_selections a where ' ||
' :1 = a.analysis_cycle_id and ' ||
' :2 = a.dimension ' ||
' and not exists ( select b.hierarchy from ' ||
' zpb_solve_input_selections b where ' ||
' a.analysis_cycle_id = b.analysis_cycle_id ' ||
' and a.member = b.member ' ||
' and a.dimension = b.dimension ' ||
' and b.hierarchy in ' || l_hierlist||')';
open input_selections_cur for sql_stmt using p_analysis_cycle_id,
l_timedim;
fetch input_selections_cur into l_hiername;
if input_selections_cur%found then
-- dbms_output.put_line('i' || ' ' ||l_hierName);
close input_selections_cur;
sql_stmt := ' select member from ' ||
' zpb_solve_output_selections a where ' ||
' :1 = a.analysis_cycle_id and ' ||
' :2 = a.dimension ' ||
' and not exists ( select b.hierarchy from ' ||
' zpb_solve_output_selections b where ' ||
' a.analysis_cycle_id = b.analysis_cycle_id ' ||
' and a.member = b.member ' ||
' and a.dimension = b.dimension ' ||
' and b.hierarchy in ' || l_hierlist || ')';
open output_selections_cur for sql_stmt using p_analysis_cycle_id,
l_timedim;
fetch output_selections_cur into l_hiername;
if output_selections_cur%found then
-- dbms_output.put_line('i' || ' ' ||l_hierName);
close output_selections_cur;
ROLLBACK TO val_solve_hrzselections;
ROLLBACK TO val_solve_hrzselections;
ROLLBACK TO val_solve_hrzselections;
end val_solve_hrzselections;
PROCEDURE delete_view(p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type)
IS
l_pub_ac_id zpb_analysis_cycles.analysis_cycle_id%type;
l_api_name CONSTANT VARCHAR2(30) := 'delete_view';
select status_code
from zpb_analysis_cycles
where analysis_cycle_id = p_analysis_cycle_id;
SAVEPOINT zpb_acval_pvt_delete_view;
update zpb_analysis_cycles set status_code = 'MARKED_FOR_DELETION' where analysis_cycle_id = p_analysis_cycle_id ;
update ZPB_DC_OBJECTS set DELETE_INSTANCE_MEASURES_FLAG = 'Y' where ac_instance_id = p_analysis_cycle_id ;
delete FROM zpb_measure_scope WHERE instance_ac_id = p_analysis_cycle_id;
delete FROM zpb_measure_scope_exempt_users WHERE BUSINESS_PROCESS_ENTITY_ID = p_analysis_cycle_id;
ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, ' View Deleted with Analysis Cycleid' || p_analysis_cycle_id || '.');
ROLLBACK TO zpb_acval_pvt_delete_view;
ROLLBACK TO zpb_acval_pvt_delete_view;
ROLLBACK TO zpb_acval_pvt_delete_view;
end delete_view;
CURSOR c_val_res IS SELECT distinct message_type FROM
ZPB_BP_VALIDATION_RESULTS WHERE BUS_PROC_ID = l_bp_id;
CURSOR c_override_rt_warn IS SELECT value
FROM zpb_ac_param_values WHERE analysis_cycle_id = l_bp_id
AND param_id = (select to_number(tag) FROM fnd_lookup_values_vl
WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
AND LOOKUP_CODE = 'IGNORE_RT_BP_VAL_WARNINGS');