[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT external_level_id
FROM ece_external_levels_upg
WHERE map_id = xMap_ID;
SELECT eicu.interface_column_id interface_column_id
FROM ece_interface_cols_upg eicu,
ece_interface_tbls_upg eitu
WHERE eicu.interface_table_id = eitu.interface_table_id AND
eitu.map_id = xMap_ID;
SELECT interface_table_id
FROM ece_interface_tbls_upg
WHERE map_id = xMap_ID;
SELECT map_id,
map_code
FROM ece_mappings
WHERE transaction_type = xTransactionType AND
map_type = NVL(xMapType,map_type);
SELECT map_id,
map_code
FROM ece_mappings_upg
WHERE transaction_type = xTransactionType AND
map_type = NVL(xMapType,map_type);
SELECT rule_type,
action_code
FROM ece_process_rules
WHERE map_id = xMap_ID;
SELECT rule_type,
action_code
FROM ece_process_rules_upg
WHERE map_id = xMap_ID;
SELECT transtage_id
FROM ece_tran_stage_data_upg
WHERE map_id = xMap_ID;
SELECT map_id INTO iMap_ID
FROM ece_mappings_upg
WHERE map_code = xMapCode;
SELECT map_id INTO iMap_ID
FROM ece_mappings
WHERE map_code = xMapCode;
SELECT map_code INTO cMapCode
FROM ece_mappings_upg
WHERE map_id = xMap_ID;
SELECT map_code INTO cMapCode
FROM ece_mappings
WHERE map_id = xMap_ID;
SELECT ecmu.map_id INTO iMap_ID
FROM ece_mappings_upg ecmu,
ece_mappings ecm
WHERE ecmu.map_code = ecm.map_code AND
ecm.map_id = xMap_ID;
SELECT ecm.map_id INTO iMap_ID
FROM ece_mappings_upg ecmu,
ece_mappings ecm
WHERE ecm.map_code = ecmu.map_code AND
ecmu.map_id = xMap_ID;
SELECT NVL(upgraded_flag,'N') INTO cUpgradedFlag
FROM ece_interface_tables
WHERE transaction_type = xTransactionType AND
map_id = iMapId AND
ROWNUM = 1;
SELECT NVL(upgraded_flag,'N') INTO cUpgradedFlag
FROM ece_interface_tables
WHERE transaction_type = xTransactionType AND
ROWNUM = 1;
SELECT xref_category_id,
xref_key1_source_column,
xref_key2_source_column,
xref_key3_source_column,
xref_key4_source_column,
xref_key5_source_column
INTO iXref_Category_ID,
iKey1,
iKey2,
iKey3,
iKey4,
iKey5
FROM ece_interface_columns
WHERE interface_column_id = xInterface_Column_ID_Main;
UPDATE ece_interface_cols_upg
SET xref_category_id = iXref_Category_ID,
xref_key1_source_column = iKey1,
xref_key2_source_column = iKey2,
xref_key3_source_column = iKey3,
xref_key4_source_column = iKey4,
xref_key5_source_column = iKey5
WHERE interface_column_id = xInterface_Column_ID_Upg;
SELECT column_rule_id,
interface_column_id,
sequence,
rule_type,
action_code
FROM ece_column_rules
WHERE interface_column_id = xInterface_Column_ID
ORDER BY sequence;
UPDATE ece_column_rules
SET interface_column_id = xInterface_Column_ID_Upg
WHERE column_rule_id = v_column_rules.column_rule_id;
SELECT eitu.interface_table_id interface_table_id,
eitu.interface_table_name interface_table_name,
eitu.output_level output_level,
eicu.interface_column_id interface_column_id,
eicu.interface_column_name interface_column_name,
eicu.record_number record_number,
eicu.position position,
eicu.width width,
eicu.conversion_sequence conversion_sequence,
NVL(eicu.record_layout_code,' ') record_layout_code,
NVL(eicu.record_layout_qualifier,' ') record_layout_qualifier
FROM ece_interface_cols_upg eicu,
ece_interface_tbls_upg eitu
WHERE eicu.interface_table_id = eitu.interface_table_id AND
eitu.map_id = xMap_ID AND
eicu.interface_column_name IS NOT NULL
ORDER BY TO_NUMBER(eitu.output_level),
eicu.interface_column_name;
SELECT TO_NUMBER(eitu.output_level) output_level,
eicu.record_number record_number,
eicu.position position,
eitu.map_id map_id,
COUNT(*) count
FROM ece_interface_cols_upg eicu,
ece_interface_tbls_upg eitu
WHERE eitu.map_id = xMap_ID AND
eitu.interface_table_id = eicu.interface_table_id AND
eicu.record_number IS NOT NULL AND --These lines are used to filter out unmapped
eicu.position IS NOT NULL --records which are not true duplicates.
GROUP BY eicu.record_number,
eicu.position,
eitu.map_id,
TO_NUMBER(eitu.output_level)
HAVING COUNT(*) > 1
ORDER BY TO_NUMBER(eitu.output_level),
eicu.record_number,
eicu.position;
SELECT eitu.interface_table_id interface_table_id,
eitu.output_level output_level,
eitu.map_id map_id
FROM ece_interface_tbls_upg eitu
WHERE eitu.map_id = xMap_ID;
SELECT eic.interface_column_id,
eic.record_number,
eic.position,
eic.width,
eic.conversion_sequence,
NVL(eic.record_layout_code,' '),
NVL(eic.record_layout_qualifier,' ')
INTO v_layout_record
FROM ece_interface_columns eic,
ece_interface_tables eit
WHERE eic.interface_table_id = eit.interface_table_id AND
eit.map_id = iMap_ID_Main AND
eit.output_level = v_interface_columns_layout_upg.output_level AND
eic.interface_column_name = v_interface_columns_layout_upg.interface_column_name;
UPDATE ece_interface_cols_upg
SET record_number = v_layout_record.record_number,
position = v_layout_record.position
WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
UPDATE ece_interface_cols_upg
SET width = v_layout_record.width
WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
UPDATE ece_interface_cols_upg
SET conversion_sequence = v_layout_record.conversion_sequence
WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
UPDATE ece_interface_cols_upg
SET record_layout_code = v_layout_record.record_layout_code
WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
UPDATE ece_interface_cols_upg
SET record_layout_qualifier = v_layout_record.record_layout_qualifier
WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
select direction
into xDirection
from ece_interface_tables
where map_id = iMap_ID_Main
and rownum=1;
select count(*)
into xExt_Col_Count
from ece_interface_columns
where record_number like '_9__'
and map_id = iMap_ID_Main;
INSERT INTO ece_interface_cols_upg(
interface_column_id,
interface_table_id,
interface_column_name,
base_table_name,
base_column_name,
table_name,
column_name,
record_number,
position,
width,
data_type,
conversion_sequence,
record_layout_code,
record_layout_qualifier,
conversion_group_id,
xref_category_allowed,
xref_category_id,
xref_key1_source_column,
xref_key2_source_column,
xref_key3_source_column,
xref_key4_source_column,
xref_key5_source_column,
external_level,
map_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT ece_interface_column_id_s.NEXTVAL,
v_interface_tables_upg.interface_table_id,
eic.interface_column_name,
eic.base_table_name,
eic.base_column_name,
eic.table_name,
eic.column_name,
eic.record_number,
eic.position,
eic.width,
eic.data_type,
eic.conversion_sequence,
eic.record_layout_code,
eic.record_layout_qualifier,
eic.conversion_group_id,
eic.xref_category_allowed,
eic.xref_category_id,
eic.xref_key1_source_column,
eic.xref_key2_source_column,
eic.xref_key3_source_column,
eic.xref_key4_source_column,
eic.xref_key5_source_column,
eic.external_level,
v_interface_tables_upg.map_id,
SYSDATE,
1,
SYSDATE,
1,
1
FROM ece_interface_columns eic,
ece_interface_tables eit
WHERE eic.map_id=iMap_ID_Main
AND eic.record_number like '_9__'
AND eic.interface_table_id = eit.interface_table_id
AND eic.map_id = eit.map_id
AND eit.output_level = v_interface_tables_upg.output_level
AND eic.interface_column_name not in(select interface_column_name
from ece_interface_cols_upg
where record_number like '_9__'
and map_id=v_interface_tables_upg.map_id);
UPDATE ece_process_rules_upg
SET action_code = v_process_rules.action_code
WHERE map_id = xMap_ID AND
rule_type = v_process_rules.rule_type;
INSERT INTO ece_column_rules(
column_rule_id,
interface_column_id,
sequence,
rule_type,
action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT column_rule_id,
interface_column_id,
sequence,
rule_type,
action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
FROM ece_column_rules_upg
WHERE interface_column_id = v_interface_columns_upg.interface_column_id;
INSERT INTO ece_procedure_mappings(
procmap_id,
transtage_id,
parameter_name,
action_type,
variable_level,
variable_name)
SELECT procmap_id,
transtage_id,
parameter_name,
action_type,
variable_level,
variable_name
FROM ece_proc_mappings_upg
WHERE transtage_id = v_tran_stage_data_upg.transtage_id;
INSERT INTO ece_tran_stage_data(
transaction_type,
transaction_level,
stage,
seq_number,
action_type,
variable_level,
variable_name,
variable_value,
default_value,
previous_variable_level,
previous_variable_name,
sequence_name,
custom_procedure_name,
data_type,
function_name,
next_variable_name,
where_clause,
map_id,
transtage_id)
SELECT transaction_type,
transaction_level,
stage,
seq_number,
action_type,
variable_level,
variable_name,
variable_value,
default_value,
previous_variable_level,
previous_variable_name,
sequence_name,
custom_procedure_name,
data_type,
function_name,
next_variable_name,
where_clause,
map_id,
transtage_id
FROM ece_tran_stage_data_upg
WHERE map_id = xMap_ID;
INSERT INTO ece_external_levels(
external_level_id,
external_level,
description,
map_id,
transaction_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
start_element,
parent_level,
enabled_flag)
SELECT external_level_id,
external_level,
description,
map_id,
transaction_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
start_element,
parent_level,
enabled_flag
FROM ece_external_levels_upg
WHERE map_id = xMap_ID;
INSERT INTO ece_interface_columns(
interface_column_id,
interface_table_id,
interface_column_name,
base_table_name,
base_column_name,
table_name,
column_name,
record_number,
position,
width,
conversion_sequence,
data_type,
conversion_group_id,
xref_category_allowed,
xref_category_id,
xref_key1_source_column,
xref_key2_source_column,
xref_key3_source_column,
xref_key4_source_column,
xref_key5_source_column,
record_layout_code,
record_layout_qualifier,
data_loc_id,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
staging_column,
element_tag_name,
map_id,
external_level)
SELECT interface_column_id,
interface_table_id,
interface_column_name,
base_table_name,
base_column_name,
table_name,
column_name,
record_number,
position,
width,
conversion_sequence,
data_type,
conversion_group_id,
xref_category_allowed,
xref_category_id,
xref_key1_source_column,
xref_key2_source_column,
xref_key3_source_column,
xref_key4_source_column,
xref_key5_source_column,
record_layout_code,
record_layout_qualifier,
data_loc_id,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
staging_column,
element_tag_name,
map_id,
external_level
FROM ece_interface_cols_upg
WHERE interface_table_id = v_interface_tables_upg.interface_table_id;
INSERT INTO ece_interface_tables(
interface_table_id,
transaction_type,
output_level,
interface_table_name,
extension_table_name,
key_column_name,
start_number,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
flatfile_version,
direction,
primary_address_type,
parent_level,
installed_flag,
map_id,
enabled,
upgraded_flag)
SELECT interface_table_id,
transaction_type,
output_level,
interface_table_name,
extension_table_name,
key_column_name,
start_number,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
flatfile_version,
direction,
primary_address_type,
parent_level,
installed_flag,
map_id,
enabled,
upgraded_flag
FROM ece_interface_tbls_upg
WHERE map_id = xMap_ID;
INSERT INTO ece_level_matrices(
matrix_level_id,
external_level_id,
interface_table_id)
SELECT matrix_level_id,
external_level_id,
interface_table_id
FROM ece_level_matrices_upg
WHERE external_level_id = v_external_levels.external_level_id;
INSERT INTO ece_mappings(
map_id,
description,
map_type,
transaction_type,
root_element,
enabled,
map_code)
SELECT map_id,
description,
map_type,
transaction_type,
root_element,
enabled,
map_code
FROM ece_mappings_upg
WHERE map_id = xMap_ID;
INSERT INTO ece_process_rules(
process_rule_id,
transaction_type,
rule_type,
action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
map_id)
SELECT process_rule_id,
transaction_type,
rule_type,
action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
map_id
FROM ece_process_rules_upg
WHERE map_id = xMap_ID;
PROCEDURE ec_delete_column_rules(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_column_rules_upg
WHERE interface_column_id IN (SELECT eic.interface_column_id
FROM ece_interface_cols_upg eic,
ece_interface_tbls_upg eit
WHERE eic.interface_table_id = eit.interface_table_id AND
eit.map_id = xMap_ID);
DELETE FROM ece_column_rules
WHERE interface_column_id IN (SELECT eic.interface_column_id
FROM ece_interface_columns eic,
ece_interface_tables eit
WHERE eic.interface_table_id = eit.interface_table_id AND
eit.map_id = xMap_ID);
END ec_delete_column_rules;
PROCEDURE ec_delete_dynamic_action(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_proc_mappings_upg
WHERE transtage_id IN (SELECT transtage_id
FROM ece_tran_stage_data_upg
WHERE map_id = xMap_ID);
DELETE FROM ece_tran_stage_data_upg
WHERE map_id = xMap_ID;
DELETE FROM ece_procedure_mappings
WHERE transtage_id IN (SELECT transtage_id
FROM ece_tran_stage_data
WHERE map_id = xMap_ID);
DELETE FROM ece_tran_stage_data
WHERE map_id = xMap_ID;
END ec_delete_dynamic_action;
PROCEDURE ec_delete_external_levels(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_external_levels_upg
WHERE map_id = xMap_ID;
DELETE FROM ece_external_levels
WHERE map_id = xMap_ID;
END ec_delete_external_levels;
PROCEDURE ec_delete_interface_columns(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_interface_cols_upg
WHERE interface_table_id IN (SELECT interface_table_id
FROM ece_interface_tbls_upg
WHERE map_id = xMap_ID);
DELETE FROM ece_interface_columns
WHERE interface_table_id IN (SELECT interface_table_id
FROM ece_interface_tables
WHERE map_id = xMap_ID);
END ec_delete_interface_columns;
PROCEDURE ec_delete_interface_tables(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_interface_tbls_upg
WHERE map_id = xMap_ID;
DELETE FROM ece_interface_tables
WHERE map_id = xMap_ID;
END ec_delete_interface_tables;
PROCEDURE ec_delete_level_matrices(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_level_matrices_upg
WHERE external_level_id IN (SELECT external_level_id
FROM ece_external_levels_upg
WHERE map_id = xMap_ID);
DELETE FROM ece_level_matrices
WHERE external_level_id IN (SELECT external_level_id
FROM ece_external_levels
WHERE map_id = xMap_ID);
END ec_delete_level_matrices;
PROCEDURE ec_delete_mappings(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_mappings_upg
WHERE map_id = xMap_ID;
DELETE FROM ece_mappings
WHERE map_id = xMap_ID;
END ec_delete_mappings;
PROCEDURE ec_delete_process_rules(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
DELETE FROM ece_process_rules_upg
WHERE map_id = xMap_ID;
DELETE FROM ece_process_rules
WHERE map_id = xMap_ID;
END ec_delete_process_rules;
PROCEDURE ec_delete_map_data_by_mapcode(
xMapCode IN ece_mappings.map_code%TYPE,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
iMap_ID NUMBER;
ec_delete_dynamic_action(iMap_ID,xUpgradeFlag);
ec_delete_process_rules(iMap_ID,xUpgradeFlag);
ec_delete_column_rules(iMap_ID,xUpgradeFlag);
ec_delete_interface_columns(iMap_ID,xUpgradeFlag);
ec_delete_interface_tables(iMap_ID,xUpgradeFlag);
ec_delete_level_matrices(iMap_ID,xUpgradeFlag);
ec_delete_external_levels(iMap_ID,xUpgradeFlag);
ec_delete_mappings(iMap_ID,xUpgradeFlag);
END ec_delete_map_data_by_mapcode;
PROCEDURE ec_delete_map_data_by_mapid(
xMap_ID IN NUMBER,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
ec_delete_dynamic_action(xMap_ID,xUpgradeFlag);
ec_delete_process_rules(xMap_ID,xUpgradeFlag);
ec_delete_column_rules(xMap_ID,xUpgradeFlag);
ec_delete_interface_columns(xMap_ID,xUpgradeFlag);
ec_delete_interface_tables(xMap_ID,xUpgradeFlag);
ec_delete_level_matrices(xMap_ID,xUpgradeFlag);
ec_delete_external_levels(xMap_ID,xUpgradeFlag);
ec_delete_mappings(xMap_ID,xUpgradeFlag);
END ec_delete_map_data_by_mapid;
PROCEDURE ec_delete_map_data_by_trans(
xTransactionType IN ece_interface_tables.transaction_type%TYPE,
xMapType IN ece_mappings.map_type%TYPE,
xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
BEGIN
IF xUpgradeFlag = 'Y' THEN
FOR v_maps_upg IN c_maps_upg(xTransactionType,xMapType) LOOP
ec_delete_map_data_by_mapid(v_maps_upg.map_id,xUpgradeFlag);
ec_delete_map_data_by_mapid(v_maps.map_id,xUpgradeFlag);
END ec_delete_map_data_by_trans;
ec_delete_map_data_by_mapcode(xMapCode,'N');
ec_delete_map_data_by_mapcode(xMapCode,'Y');
SELECT COUNT(*) INTO n_map_count
FROM ece_mappings
WHERE map_code = v_maps_upg.map_code;
SELECT COUNT(*) INTO n_staged_doc_count
FROM ece_stage
WHERE map_id = iMap_ID_Main;
ec_delete_column_rules(v_maps_upg.map_id,'Y');
UPDATE ece_tp_details
SET map_id = xMap_ID
WHERE map_id = iMap_ID_Main;