[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_DOCTYPE_PROC_NAME CONSTANT varchar2(50) := 'ECX_ACTIONS.DELETE_DOCTYPE';
Prepares the Select statement for the ec_views on the base Applications tables.
**/
procedure select_clause
(
i_level IN pls_integer,
i_Where_string OUT NOCOPY VARCHAR2
) IS
i_method_name varchar2(2000) := 'ecx_utils.select_clause';
cSelect_stmt VARCHAR2(32000) := 'SELECT ';
-- build SELECT statement
cSelect_stmt := cSelect_stmt || ' ' || cWord1 ||
nvl(ecx_utils.g_source(i).base_column_Name,'NULL') || cWord2 || ',';
cSelect_stmt := RTRIM(cSelect_stmt, ',');
i_Where_string := cSelect_stmt||' '||cFrom_stmt||' '||cWhere_Stmt;
'PROGRESS_LEVEL','ECX_UTILS.SELect_CLAUSE');
END select_clause;
1. Select statement on the ec_views.
3. Parses and loads Custom Procedures into memory table.
4. Loads mappings required by these procedures into memory tables.
**/
procedure load_objects
(
i_map_id in pls_integer
)
is
i_method_name varchar2(2000) := 'ecx_utils.load_objects';
select_clause
(
i,
ecx_utils.g_source_levels(i).sql_stmt
);
-- Parse the Select Statement for Each level
BEGIN
dbms_sql.parse (
ecx_utils.g_source_levels(i).cursor_handle,
ecx_utils.g_source_levels(i).sql_stmt,
dbms_sql.native
);
select epm.transtage_id transtage_id,
upper(etsd.custom_procedure_name) procedure_name,
upper(epm.parameter_name) parameter_name,
epm.action_type action_type,
epm.variable_level variable_level,
epm.variable_name variable_name,
epm.variable_pos variable_pos,
epm.data_type data_type,
nvl(epm.variable_direction,'S') variable_direction,
epm.variable_value variable_constant
from ecx_proc_mappings epm,
ecx_tran_stage_data etsd
where etsd.transtage_id = epm.transtage_id
and etsd.map_id = p_map_id
and epm.map_id = p_map_id
order by epm.transtage_id,procmap_id;
ecx_utils.g_procedure_mappings.DELETE;
select transtage_id,
custom_procedure_name
from ecx_tran_stage_data etsd
where etsd.map_id = i_map_id
and action_type = 1050;
ecx_utils.g_procedure_list.DELETE;
select attribute_id,
attribute_name,
parent_attribute_id,
decode(object_column_flag,'Y',attribute_name,null) base_column_name,
xref_category_id,
attribute_type,
default_value,
data_type,
has_attributes,
leaf_node,
occurrence,
cond_value,
cond_node,
cond_node_type
from ecx_object_attributes eoa
where eoa.map_id = p_map_id
and eoa.objectlevel_id = p_level_id
order by attribute_id;
select source_attribute_id
from ecx_attribute_mappings eam
where eam.target_attribute_id = p_attribute_id
and eam.map_id = p_map_id;
select
a.attribute_id,
a.attribute_name,
a.parent_attribute_id,
decode(a.object_column_flag,'Y',attribute_name,null) base_column_name,
a.xref_category_id,
a.attribute_type,
a.default_value,
a.data_type,
a.has_attributes,
a.leaf_node,
a.required_flag,
a.occurrence,
a.cond_value,
a.cond_node,
a.cond_node_type,
m.SOURCE_ATTRIBUTE_ID source_attribute_id
bulk collect into
-- obj_rec_table
v_attribute_id,
v_attribute_name,
v_parent_attribute_id,
v_base_column_name,
v_xref_category_id ,
v_attribute_type,
v_default_value,
v_data_type,
v_has_attributes,
v_leaf_node,
v_required_flag,
v_occurrence,
v_cond_value,
v_cond_node,
v_cond_node_type,
v_source_attribute_id
from ecx_object_attributes a,
ecx_attribute_mappings m
where a.map_id = i_map_id
and a.objectlevel_id = i_level_id
and m.map_id (+) = i_map_id
and m.TARGET_ATTRIBUTE_ID (+) = a.ATTRIBUTE_ID
order by a.attribute_id;
select objectlevel_id,
object_level,
object_level_name,
parent_level
from ecx_object_levels eol,
ecx_mappings em
where eol.map_id = p_map_id
and eol.map_id = em.map_id
and eol.object_id = 1
and eol.object_level = 0
order by objectlevel_id;
select objectlevel_id target_level_id,
object_level target_level,
object_level_name target_level_name,
parent_level
from ecx_object_levels eol,
ecx_mappings em
where eol.map_id = p_map_id
and eol.map_id = em.map_id
and eol.object_id = em.object_id_target
order by target_level;
select object_level source_level,
level_mapping_id ,
objectlevel_id source_level_id,
source_element_id,
target_element_id
from ecx_level_mappings elm,
ecx_object_levels eol
where elm.target_level_id = p_target_id
and elm.map_id = p_map_id
and elm.map_id = eol.map_id
and elm.source_level_id = eol.objectlevel_id;
select object_level source_level,
object_level_name source_level_name,
parent_level,
objectlevel_id
from ecx_object_levels
where objectlevel_id = p_source_level_id
and map_id = p_map_id;
ecx_utils.g_source_levels.DELETE;
ecx_utils.g_target_levels.DELETE;
ecx_utils.g_target_source_levels.DELETE;
ecx_utils.g_target.DELETE;
ecx_utils.g_source.DELETE;
select objectlevel_id target_level_id,
object_level target_level,
object_level_name target_level_name,
parent_level
bulk collect into
-- targ_rec_table
v_target_level_id,
v_target_level,
v_target_level_name,
v_parent_level
from ecx_object_levels eol,
ecx_mappings em
where eol.map_id = i_map_id
and eol.map_id = em.map_id
and eol.object_id = em.object_id_target
order by target_level;
select object_level source_level,
level_mapping_id ,
objectlevel_id source_level_id,
source_element_id,
target_element_id
bulk collect into
-- targ_src_rec_table
v_source_level,
v_level_mapping_id,
v_source_level_id,
v_source_element_id,
v_target_element_id
from ecx_level_mappings elm,
ecx_object_levels eol
where elm.target_level_id = v_target_level_id(j)
and elm.map_id = i_map_id
and elm.map_id = eol.map_id
and elm.source_level_id = eol.objectlevel_id;
/** Update the Target Node Index **/
ecx_utils.g_target_levels(cur_ext_level).dtd_node_index := v_target_element_id(k);
select em.ecx_major_version,
em.ecx_minor_version
into i_major_version,
i_minor_version
from ecx_mappings em
where em.map_id = i_map_id;
select text
into i_eng_version
from wf_resources
where name = 'ECX_VERSION'
and type = 'WFTKN'
and language = 'US';
select eobj.root_element,
eobj.fullpath,
eobj.runtime_location
from ecx_objects eobj
where eobj.object_id = p_object_id
and eobj.map_id = p_map_id;
select payload
from ecx_dtds
where root_element = p_root_element
and filename = p_filename
and ( version = p_location or p_location is null );
select map_code into i_map_code from ecx_mappings where map_id=i_map_id;
SELECT object_id_source,
object_id_target
INTO g_source_object_id,
g_target_object_id
FROM ecx_mappings em
WHERE em.map_id = i_map_id;
ecx_utils.g_xml_frag.DELETE;
SELECT object_type
INTO i_tar_obj_type
FROM ecx_objects
WHERE map_id = i_map_id
AND object_id = 2;
--ecx_utils.g_node_tbl.DELETE;
ecx_utils.g_node_tbl.DELETE;
This helps in improving the perfromance , as un-necessary selects are saved.
**/
get_tran_stage_data (i_map_id);
ecx_utils.g_node_tbl.DELETE;
ecx_utils.g_node_tbl.DELETE;
ecx_utils.g_node_tbl.DELETE;
select transtage_id,
object_level ,
a.objectlevel_id,
stage,
nvl(object_direction,'S') object_direction,
seq_number,
action_type,
variable_level,
variable_name,
nvl(variable_direction,'S') variable_direction,
variable_value,
default_value,
upper(sequence_name) sequence_name,
upper(custom_procedure_name) custom_procedure_name,
data_type,
upper(function_name) function_name,
where_clause,
variable_pos,
upper(cond_logical_operator) cond_logical_operator,
upper(cond_operator1) cond_operator1,
cond_var1_level,
cond_var1_name,
cond_var1_pos,
upper(cond_var1_direction) cond_var1_direction,
cond_var1_constant,
cond_val1_level,
cond_val1_name,
cond_val1_pos,
upper(cond_val1_direction) cond_val1_direction,
cond_val1_constant,
upper(cond_operator2) cond_operator2,
cond_var2_level,
cond_var2_name,
cond_var2_pos,
upper(cond_var2_direction) cond_var2_direction,
cond_var2_constant,
cond_val2_level,
cond_val2_name,
cond_val2_pos,
upper(cond_val2_direction) cond_val2_direction,
cond_val2_constant,
operand1_level,
operand1_name,
operand1_pos,
operand1_direction,
operand1_constant,
operand1_len,
operand1_start_pos,
operand2_level,
operand2_name,
operand2_pos,
operand2_direction,
operand2_constant,
operand3_level,
operand3_name,
operand3_pos,
operand3_direction,
operand3_constant,
operand4_level,
operand4_name,
operand4_pos,
operand4_direction,
operand4_constant,
operand5_level,
operand5_name,
operand5_pos,
operand5_direction,
operand5_constant,
operand6_level,
operand6_name,
operand6_pos,
operand6_direction,
operand6_constant
from ecx_tran_stage_data a,
ecx_object_levels b
where a.map_id = p_map_id
and a.map_id = b.map_id
and a.objectlevel_id = b.objectlevel_id
and action_type <> 10
order by stage,object_level,action_pos,seq_number;
ecx_utils.g_delete_doctype := false;
ecx_utils.g_stage_data.DELETE;
select
transtage_id,
object_level ,
a.objectlevel_id,
stage,
nvl(object_direction,'S') object_direction,
seq_number,
action_type,
variable_level,
variable_name,
nvl(variable_direction,'S') variable_direction,
variable_value,
default_value,
upper(sequence_name) sequence_name,
upper(custom_procedure_name) custom_procedure_name,
data_type,
upper(function_name) function_name,
where_clause,
variable_pos,
upper(cond_logical_operator) cond_logical_operator,
upper(cond_operator1) cond_operator1,
cond_var1_level,
cond_var1_name,
cond_var1_pos,
upper(cond_var1_direction) cond_var1_direction,
cond_var1_constant,
cond_val1_level,
cond_val1_name,
cond_val1_pos,
upper(cond_val1_direction) cond_val1_direction,
cond_val1_constant,
upper(cond_operator2) cond_operator2,
cond_var2_level,
cond_var2_name,
cond_var2_pos,
upper(cond_var2_direction) cond_var2_direction,
cond_var2_constant,
cond_val2_level,
cond_val2_name,
cond_val2_pos,
upper(cond_val2_direction) cond_val2_direction,
cond_val2_constant,
operand1_level,
operand1_name,
operand1_pos,
operand1_direction,
operand1_constant,
operand1_len,
operand1_start_pos,
operand2_level,
operand2_name,
operand2_pos,
operand2_direction,
operand2_constant,
operand3_level,
operand3_name,
operand3_pos,
operand3_direction,
operand3_constant,
operand4_level,
operand4_name,
operand4_pos,
operand4_direction,
operand4_constant,
operand5_level,
operand5_name,
operand5_pos,
operand5_direction,
operand5_constant,
operand6_level,
operand6_name,
operand6_pos,
operand6_direction,
operand6_constant
-- bulk collect into ecx_utils.g_stage_data
bulk collect into
-- i_stage_data
v_transtage_id,
v_object_level,
v_objectlevel_id,
v_stage,
v_object_direction,
v_seq_number,
v_action_type,
v_variable_level,
v_variable_name,
v_variable_direction,
v_variable_value,
v_default_value,
v_sequence_name,
v_custom_procedure_name,
v_data_type,
v_function_name,
v_where_clause,
v_variable_pos,
v_cond_logical_operator,
v_cond_operator1,
v_cond_var1_level,
v_cond_var1_name,
v_cond_var1_pos,
v_cond_var1_direction,
v_cond_var1_constant,
v_cond_val1_level,
v_cond_val1_name,
v_cond_val1_pos,
v_cond_val1_direction,
v_cond_val1_constant,
v_cond_operator2,
v_cond_var2_level,
v_cond_var2_name,
v_cond_var2_pos,
v_cond_var2_direction,
v_cond_var2_constant,
v_cond_val2_level,
v_cond_val2_name,
v_cond_val2_pos,
v_cond_val2_direction,
v_cond_val2_constant,
v_operand1_level,
v_operand1_name,
v_operand1_pos,
v_operand1_direction,
v_operand1_constant,
v_operand1_len,
v_operand1_start_pos,
v_operand2_level,
v_operand2_name,
v_operand2_pos,
v_operand2_direction,
v_operand2_constant,
v_operand3_level,
v_operand3_name,
v_operand3_pos,
v_operand3_direction,
v_operand3_constant,
v_operand4_level,
v_operand4_name,
v_operand4_pos,
v_operand4_direction,
v_operand4_constant,
v_operand5_level,
v_operand5_name,
v_operand5_pos,
v_operand5_direction,
v_operand5_constant,
v_operand6_level,
v_operand6_name,
v_operand6_pos,
v_operand6_direction,
v_operand6_constant
from ecx_tran_stage_data a,
ecx_object_levels b
where a.map_id = i_map_id
and a.map_id = b.map_id
and a.objectlevel_id = b.objectlevel_id
and action_type <> 10
order by stage,object_level,action_pos,seq_number;
IF (instr(temp_util_rec.custom_procedure_name, DELETE_DOCTYPE_PROC_NAME) > 0) THEN
g_delete_doctype := true;
ecx_debug.log(l_statement, 'g_delete_doctype = true',i_method_name);
SELECT object_level_name INTO l_root_node
FROM ecx_object_levels
WHERE map_id=v_map_id and object_level=0 and object_id=1;
SELECT attribute_name,attribute_id,parent_attribute_id
INTO l_attribute_name,l_attribute_id,l_parent_id
FROM ecx_object_attributes
WHERE map_id = v_map_id and objectlevel_id in(
select objectlevel_id from ecx_object_levels
WHERE map_id=v_map_id and object_id = 1) AND attribute_id=v_attribute_id;
SELECT attribute_name,attribute_id,parent_attribute_id
INTO l_attribute_name,l_attribute_id,l_parent_id
FROM ecx_object_attributes
WHERE map_id = v_map_id and objectlevel_id in(
select objectlevel_id from ecx_object_levels
WHERE map_id=v_map_id and object_id = 1) AND attribute_id=l_parent_id;