[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rst.name,
rstg.stage_number,
mc.column_name
INTO idt_name,
stage_num,
col_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_columns mc
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.target_column_id = mc.column_id
AND r.rule_id = add_deriv_proc_failed_msg.rule_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name,
d.derivation_sequence
INTO idt_name,
stage_num,
col_name,
deriv_num
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_derivations d,
gcs_lex_map_columns mc
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.rule_id = d.rule_id
AND r.target_column_id = mc.column_id
AND d.derivation_id = add_deriv_failed_msg.derivation_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name,
d.derivation_sequence,
d.function_name
INTO idt_name,
stage_num,
col_name,
deriv_num,
func_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_derivations d,
gcs_lex_map_columns mc
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.rule_id = d.rule_id
AND r.target_column_id = mc.column_id
AND d.derivation_id = add_plsql_deriv_failed_msg.derivation_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name
INTO idt_name,
stage_num,
col_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_columns mc
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.target_column_id = mc.column_id
AND r.rule_id = add_rule_failed_msg.rule_id;
SELECT rst.name,
rstg.stage_number
INTO idt_name,
stage_num
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = add_stage_failed_msg.rule_stage_id;
SELECT rst.name,
rstg.stage_number
INTO idt_name,
stage_num
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = add_rows_changed_msg.rule_stage_id;
SELECT rst.name
INTO idt_name
FROM gcs_lex_map_rule_sets rst
WHERE rst.rule_set_id = add_idt_failed_msg.rule_set_id;
SELECT ms.structure_name
INTO struct_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_structs ms
WHERE rst.structure_id = ms.structure_id
AND rst.rule_set_id = add_structure_failed_msg.rule_set_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name,
ffvs.flex_value_set_name
INTO idt_name,
stage_num,
col_name,
vs_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_columns mc,
fnd_flex_value_sets ffvs
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.target_column_id = mc.column_id
AND r.value_set_id = ffvs.flex_value_set_id
AND r.rule_id = add_value_set_failed_msg.rule_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name,
lutms.structure_name
INTO idt_name,
stage_num,
col_name,
lut_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_columns mc,
gcs_lex_map_structs lutms
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.target_column_id = mc.column_id
AND r.lookup_table_id = lutms.structure_id
AND r.rule_id = add_rule_lut_failed_msg.rule_id;
SELECT rst.name,
rstg.stage_number,
mc.column_name,
d.derivation_sequence,
lutms.structure_name
INTO idt_name,
stage_num,
col_name,
deriv_num,
lut_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r,
gcs_lex_map_derivations d,
gcs_lex_map_columns mc,
gcs_lex_map_columns lutmc,
gcs_lex_map_structs lutms
WHERE rst.rule_set_id = rstg.rule_set_id
AND rstg.rule_stage_id = r.rule_stage_id
AND r.rule_id = d.rule_id
AND r.target_column_id = mc.column_id
AND d.lookup_result_column_id = lutmc.column_id
AND lutmc.structure_id = lutms.structure_id
AND d.derivation_id = add_deriv_lut_failed_msg.derivation_id;
SELECT mc.column_name
INTO error_code_col_name
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_columns mc
WHERE rst.structure_id = mc.structure_id
AND mc.error_code_column_flag = 'Y'
AND rst.rule_set_id = add_error_code_failed_msg.rule_set_id;
SELECT stg.rule_stage_id
FROM gcs_lex_map_rule_stages stg
WHERE stg.rule_set_id = initial_rule_set_check.rule_set_id;
SELECT name, enabled_flag
FROM gcs_lex_map_rule_sets rsts
WHERE rsts.rule_set_id = initial_rule_set_check.rule_set_id;
SELECT r.rule_id
FROM gcs_lex_map_rules r
WHERE r.rule_stage_id = p_stage_id;
SELECT mc.column_name, mc.write_flag
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_columns mc
WHERE rst.structure_id = mc.structure_id
AND mc.error_code_column_flag = 'Y'
AND rst.rule_set_id = initial_rule_set_check.rule_set_id;
SELECT d.derivation_id
FROM gcs_lex_map_derivations d,
gcs_lex_map_rules r,
gcs_lex_map_rule_stages stg
WHERE d.rule_id = r.rule_id
AND r.rule_stage_id = stg.rule_stage_id
AND stg.rule_set_id = initial_rule_set_check.rule_set_id
AND d.derivation_type_code = 'PLS'
AND NOT EXISTS
(SELECT 1
FROM gcs_lex_map_plsql_funcs f
WHERE UPPER(d.function_name) = f.function_name);
SELECT mc.column_name
FROM gcs_lex_map_columns mc,
gcs_lex_map_rule_sets rst
WHERE rst.structure_id = mc.structure_id
AND rst.rule_set_id = staging_table_check.rule_set_id;
check_text := 'SELECT ';
SELECT decode( usage,
'TRANSFORMATION', write_flag,
error_code_column_flag)
FROM gcs_lex_map_rule_sets rst,
gcs_lex_map_columns mc
WHERE mc.structure_id = rst.structure_id
AND rst.rule_set_id = get_filter_text.rule_set_id
AND UPPER(mc.column_name) = UPPER(col_name);
SELECT mc.column_name
FROM gcs_lex_map_columns mc,
gcs_lex_map_rules r
WHERE r.lookup_table_id = mc.structure_id
AND r.rule_id = validation_check.rule_id;
SELECT ffvs.validation_type
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = val_set_id;
SELECT ms.structure_name
FROM gcs_lex_map_rules r,
gcs_lex_map_structs ms
WHERE r.lookup_table_id = ms.structure_id
AND r.rule_id = validation_check.rule_id;
SELECT r.validation_type_code,
r.value_set_id
INTO valid_type_code,
value_set_id
FROM gcs_lex_map_rules r
WHERE r.rule_id = validation_check.rule_id;
SELECT COUNT(*)
INTO tv_vs_check_table
FROM fnd_flex_validation_tables ffvt,
gcs_lex_map_rules r
WHERE ffvt.flex_value_set_id = r.value_set_id
AND r.rule_id = validation_check.rule_id;
check_text := 'SELECT ';
(SELECT mc.column_name column_name,
decode(mc.column_type_code, 'N', 'NUMBER',
'D', 'DATE',
'V', 'VARCHAR2',
'') column_type_code
FROM gcs_lex_map_columns mc,
gcs_lex_map_rules r
WHERE mc.column_id = r.target_column_id
AND r.rule_id = create_param_list.rule_id)
UNION
(SELECT mc.column_name column_name,
decode(mc.column_type_code, 'N', 'NUMBER',
'D', 'DATE',
'V', 'VARCHAR2',
'') column_type_code
FROM gcs_lex_map_columns mc,
gcs_lex_map_drv_details dvd,
gcs_lex_map_derivations d
WHERE d.rule_id = create_param_list.rule_id
AND dvd.derivation_id = d.derivation_id
AND dvd.detail_column_id = mc.column_id)
UNION
(SELECT mc.column_name column_name,
decode(mc.column_type_code, 'N', 'NUMBER',
'D', 'DATE',
'V', 'VARCHAR2',
'') column_type_code
FROM gcs_lex_map_columns mc,
gcs_lex_map_conditions c,
gcs_lex_map_derivations d
WHERE d.rule_id = create_param_list.rule_id
AND c.derivation_id = d.derivation_id
AND c.source_column_id = mc.column_id)
ORDER BY column_name;
SELECT mc.column_name || ' ' || c.comparison_operator_code ||
decode(c.comparison_value,
'', '',
' ''' || REPLACE(c.comparison_value, '''', '''''') || '''')
simple_cond
FROM gcs_lex_map_columns mc,
gcs_lex_map_conditions c
WHERE c.derivation_id = create_condition.derivation_id
AND c.source_column_id = mc.column_id;
SELECT lutmc.column_name lut_col_name,
stgmc.column_name stg_col_name,
dvd.detail_constant detail_constant
FROM gcs_lex_map_drv_details dvd,
gcs_lex_map_columns lutmc,
gcs_lex_map_columns stgmc
WHERE dvd.derivation_id = create_lookup_derivation.derivation_id
AND dvd.lookup_column_id = lutmc.column_id
AND dvd.detail_column_id = stgmc.column_id (+);
SELECT ms.structure_name,
mc.column_name
INTO lookup_table_name,
result_col_name
FROM gcs_lex_map_derivations d,
gcs_lex_map_columns mc,
gcs_lex_map_structs ms
WHERE d.lookup_result_column_id = mc.column_id
AND mc.structure_id = ms.structure_id
AND d.derivation_id = create_lookup_derivation.derivation_id;
deriv_text := 'SELECT lut.' || result_col_name || ' INTO ' || g_ret_val;
SELECT ms.structure_name
INTO lookup_table_name
FROM gcs_lex_map_structs ms,
gcs_lex_map_derivations d
WHERE d.lookup_table_id = ms.structure_id
AND d.derivation_id = create_lookup_derivation.derivation_id;
deriv_text := 'SELECT DISTINCT 1 INTO dummy';
SELECT decode(string_action_type_code,
'S', 'SUBSTR('||mc.column_name || ',' ||
dvd.substring_start_index || ',' ||
dvd.substring_length || ')',
'C', mc.column_name,
'F', '''' ||
REPLACE(dvd.detail_constant, '''', '''''') ||
'''',
'') string_action
FROM gcs_lex_map_drv_details dvd,
gcs_lex_map_columns mc
WHERE dvd.derivation_id = create_string_derivation.derivation_id
AND dvd.detail_column_id = mc.column_id (+)
ORDER BY dvd.string_merge_order;
SELECT plsql_param_name || '=>' ||
decode(plsql_param_source_code,
'C', mc.column_name,
'S',''''||REPLACE(dvd.detail_constant, '''', '''''')||'''',
'N',dvd.detail_constant,
'') plsql_parameter
FROM gcs_lex_map_drv_details dvd,
gcs_lex_map_columns mc
WHERE dvd.derivation_id = create_plsql_derivation.derivation_id
AND dvd.detail_column_id = mc.column_id (+);
SELECT function_name
INTO deriv_text
FROM gcs_lex_map_derivations d
WHERE d.derivation_id = create_plsql_derivation.derivation_id;
SELECT object_name
FROM user_objects
WHERE object_name LIKE
'GCS\_LEX\_GET\_' || p_rule_set_id || '\__%' ESCAPE '\'
AND object_name NOT IN
(SELECT 'GCS_LEX_GET_' || p_rule_set_id || '_' || r.rule_id
FROM gcs_lex_map_rules r,
gcs_lex_map_rule_stages stg
WHERE stg.rule_set_id = p_rule_set_id
AND stg.rule_stage_id = r.rule_stage_id)
AND object_type = 'FUNCTION';
SELECT d.derivation_id, d.derivation_type_code
FROM gcs_lex_map_derivations d
WHERE d.rule_id = create_get_function.rule_id
ORDER BY d.derivation_sequence;
SELECT 1
FROM gcs_lex_map_conditions c
WHERE c.derivation_id = c_deriv_id;
SELECT mc.column_name, mc.column_type_code, mc.write_flag
INTO target_col_name, target_column_type, target_write_flag
FROM gcs_lex_map_rules r,
gcs_lex_map_columns mc
WHERE r.target_column_id = mc.column_id
AND r.rule_id = create_get_function.rule_id;
validation_text := 'SELECT rowid FROM ' || staging_table_name ||
' stg WHERE ';
SELECT ffvs.validation_type
INTO vs_validation_type
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = validate_column.value_set_id;
'(SELECT 1 FROM fnd_flex_values ffv ' ||
'WHERE ffv.flex_value_set_id=' || value_set_id ||
' AND ffv.flex_value = stg.' || target_col_name ||
' AND ffv.summary_flag = ''N'' ' ||
'AND ffv.enabled_flag = ''Y'')' || filter_clause;
SELECT ffvt.application_table_name,
ffvt.value_column_name,
ffvt.enabled_column_name,
ffvt.summary_allowed_flag,
ffvt.summary_column_name,
ffvt.additional_where_clause
INTO tv_table_name,
tv_column_name,
tv_enabled_column,
tv_summary_flag,
tv_summary_column,
tv_where_clause
FROM fnd_flex_validation_tables ffvt
WHERE ffvt.flex_value_set_id = validate_column.value_set_id;
'(SELECT 1 FROM (SELECT * FROM ' || tv_table_name ||
' ' || tv_where_clause || ') ffv WHERE ffv.' ||
tv_column_name || '=stg.' || target_col_name ||
' AND ' || tv_enabled_column || '=''Y''';
'NOT EXISTS (SELECT 1 FROM ' || lookup_table_name ||
' lut WHERE stg.' || target_col_name ||
'=lut.lookup_code)' || filter_clause;
SELECT r.rule_id rule_id,
tgtmc.column_name target_col_name,
r.validation_type_code validation_type_code,
lutms.structure_name lookup_table_name,
r.value_set_id value_set_id
FROM gcs_lex_map_rules r,
gcs_lex_map_columns tgtmc,
gcs_lex_map_structs lutms
WHERE r.rule_stage_id = validate_results.rule_stage_id
AND r.target_column_id = tgtmc.column_id
AND r.lookup_table_id = lutms.structure_id (+)
AND r.validation_type_code <> 'N';
SELECT r.rule_id rule_id,
mc.column_name column_name,
mc.write_flag write_flag
FROM gcs_lex_map_rules r,
gcs_lex_map_columns mc
WHERE r.rule_stage_id = apply_stage.rule_stage_id
AND r.target_column_id = mc.column_id;
SELECT 1
FROM user_objects
WHERE object_name = c_func_name;
stage_text := 'UPDATE ' || staging_table_name || ' stg';
SELECT mc.column_name
INTO error_col_name
FROM gcs_lex_map_columns mc,
gcs_lex_map_rule_sets rsts
WHERE mc.structure_id = rsts.structure_id
AND rsts.rule_set_id = init_error_column.rule_set_id
AND mc.error_code_column_flag = 'Y';
EXECUTE IMMEDIATE 'UPDATE ' || staging_table_name ||
' SET ' || error_col_name || '=''NEW''' ||
filter_where_clause;
SELECT mc.column_name
INTO error_col
FROM gcs_lex_map_columns mc,
gcs_lex_map_rule_sets rsts
WHERE mc.structure_id = rsts.structure_id
AND mc.error_code_column_flag = 'Y'
AND rsts.rule_set_id = fill_error_column.rule_set_id;
sql_text := 'UPDATE ' || staging_table_name || ' SET ' || error_col ||
'= decode(to_char(' || error_col || '), ''NEW'', '''', ' ||
error_col || '||'','') || :error_code WHERE rowid=:myrow';
error_table.delete(i);
SELECT r.validation_type_code, d.derivation_id, r.error_message
INTO validation_type, deriv_id, error_message
FROM gcs_lex_map_rules r,
gcs_lex_map_derivations d
WHERE r.rule_id = create_vrs_get_function.rule_id
AND d.rule_id = r.rule_id;
SELECT r.rule_id
FROM gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r
WHERE rstg.rule_set_id = p_rule_set_id
AND r.rule_stage_id = rstg.rule_stage_id;
SELECT rule_stage_id, stage_number
FROM gcs_lex_map_rule_stages rstg
WHERE rstg.rule_set_id = p_rule_set_id
ORDER BY stage_number;
gcs_lex_map_api_pkg.error_table.delete;
gcs_lex_map_api_pkg.error_table.delete;
gcs_lex_map_api_pkg.error_table.delete;
SELECT mc.column_name,
rstg.stage_number,
ffv.flex_value_set_name
INTO col_name,
stage_num,
vs_name
FROM gcs_lex_map_rules r,
gcs_lex_map_columns mc,
gcs_lex_map_rule_stages rstg,
fnd_flex_value_sets ffv
WHERE r.rule_stage_id = rstg.rule_stage_id
AND r.value_set_id = ffv.flex_value_set_id
AND r.target_column_id = mc.column_id
AND r.rule_id = error_table(i).rule_id;
SELECT mc.column_name,
rstg.stage_number,
lutms.structure_name
INTO col_name,
stage_num,
lut_name
FROM gcs_lex_map_rules r,
gcs_lex_map_columns mc,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_structs lutms
WHERE r.rule_stage_id = rstg.rule_stage_id
AND r.lookup_table_id = lutms.structure_id
AND r.target_column_id = mc.column_id
AND r.rule_id = error_table(i).rule_id;
EXECUTE IMMEDIATE 'SELECT ' || col_name || ' FROM ' ||
p_staging_table_name || ' WHERE rowid = :row_id'
INTO error_value
USING error_table(i).row_id;
gcs_lex_map_api_pkg.error_table.delete;
SELECT mc.column_name,
rstg.stage_number
INTO col_name,
stage_num
FROM gcs_lex_map_rules r,
gcs_lex_map_rule_stages rstg,
gcs_lex_map_columns mc
WHERE r.rule_stage_id = rstg.rule_stage_id
AND r.target_column_id = mc.column_id
AND r.rule_id = error_table(i).rule_id;
EXECUTE IMMEDIATE 'SELECT ' || param_list || ' FROM ' ||
p_staging_table_name || ' WHERE rowid = :row_id'
INTO error_value
USING error_table(i).row_id;
gcs_lex_map_api_pkg.error_table.delete;
gcs_lex_map_api_pkg.error_table.delete;
SELECT r.rule_id,
r.rule_name
FROM gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r
WHERE rstg.rule_set_id = p_rule_set_id
AND r.rule_stage_id = rstg.rule_stage_id;
SELECT r.rule_id, r.rule_name, r.validation_type_code
FROM gcs_lex_map_rule_stages rstg,
gcs_lex_map_rules r
WHERE rstg.rule_set_id = p_rule_set_id
AND r.rule_stage_id = rstg.rule_stage_id
ORDER BY rstg.stage_number;
SELECT 1
FROM user_objects
WHERE object_name = c_func_name;
SELECT mc.column_name
INTO error_col_name
FROM gcs_lex_map_rule_sets rs,
gcs_lex_map_columns mc
WHERE rs.rule_set_id = p_rule_set_id
AND mc.structure_id = rs.structure_id
AND mc.error_code_column_flag = 'Y';
DELETE FROM gcs_lex_vrs_plsql_gt;
'INSERT INTO gcs_lex_vrs_plsql_gt(associated_rowid, error_code) ' ||
'SELECT rowid, ' || func_name || '(' ||
create_param_list(val_rule.rule_id, 'C', 'Y') ||
') FROM ' || p_staging_table_name || ' WHERE ' || filter_text;
val_text := 'UPDATE ' || p_staging_table_name || ' stg SET ' ||
error_col_name || '=' || func_name ||
'(' ||create_param_list(val_rule.rule_id,'C','Y')|| ') ' ||
'WHERE ' || filter_text;
'UPDATE ' || p_staging_table_name || ' stg SET ' || error_col_name ||
'=(SELECT error_code FROM gcs_lex_vrs_plsql_gt plsgt ' ||
'WHERE plsgt.associated_rowid = stg.rowid) ' ||
'WHERE ' || filter_text;
'SELECT decode(COUNT(*), ' || num_rows_total || ', ''' ||
FND_API.G_RET_STS_SUCCESS || ''', ''' || FND_API.G_RET_STS_ERROR ||
''') FROM ' || p_staging_table_name || ' WHERE ' || filter_text
INTO x_return_status;
UPDATE gcs_lex_map_rule_sets
SET associated_object_id = fem_object_id_seq.nextval;
INSERT INTO fem_object_catalog_b(
object_id, folder_id, object_type_code, object_access_code,
object_origin_code, object_version_number, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT associated_object_id, 1300, 'CONSOLIDATION_DATA', 'R', 'USER', 1,
sysdate, created_by, sysdate, last_updated_by, last_update_login
FROM gcs_lex_map_rule_sets rst;
INSERT INTO fem_object_catalog_tl(
object_id, object_name, language, source_lang, description, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT associated_object_id, rst.name, userenv('LANG'), userenv('LANG'),
description, sysdate, created_by, sysdate, last_updated_by,
last_update_login
FROM gcs_lex_map_rule_sets rst;
INSERT INTO fem_object_definition_b(
object_definition_id, object_id, effective_start_date, effective_end_date,
object_origin_code, approval_status_code, old_approved_copy_flag,
object_version_number, creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT fem_object_definition_id_seq.nextval, associated_object_id,
to_date('01-01-1000', 'DD-MM-YYYY'),
to_date('31-12-9999', 'DD-MM-YYYY'), 'USER', 'NOT_APPLICABLE', 'N', 1,
sysdate, created_by, sysdate, last_updated_by, last_update_login
FROM gcs_lex_map_rule_sets rst;
INSERT INTO fem_object_definition_tl(
object_definition_id, object_id, language, source_lang,
old_approved_copy_flag, display_name, description, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
SELECT odb.object_definition_id, odb.object_id, userenv('LANG'),
userenv('LANG'), 'N', rst.name, rst.description, sysdate,
rst.created_by, sysdate, rst.last_updated_by, rst.last_update_login
FROM gcs_lex_map_rule_sets rst,
fem_object_definition_b odb
WHERE odb.object_id = rst.associated_object_id;