The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select R.FLEX_VALIDATION_RULE_NAME ' ||
' from FND_FLEX_VALIDATION_RULES R ';
'MINUS select L.FLEX_VALIDATION_RULE_NAME ' ||
' from FND_FLEX_INCLUDE_RULE_LINES L ' ||
' where 1 = 1 ';
FUNCTION x_cv_rule_select(fstruct IN FND_FLEX_SERVER1.FlexStructId,
v_date IN DATE,
bad_rule OUT nocopy VARCHAR2) RETURN NUMBER;
FUNCTION x_xvc_update_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
v_date IN DATE,
p_cat_segs IN VARCHAR2,
is_violated IN BOOLEAN,
rule_name IN VARCHAR2) RETURN BOOLEAN;
select_comb_from_view IN VARCHAR2,
flex_num IN NUMBER,
flex_struct OUT nocopy FND_FLEX_SERVER1.FlexStructId,
struct_info OUT nocopy FND_FLEX_SERVER1.FlexStructInfo,
cctbl_info OUT nocopy FND_FLEX_SERVER1.CombTblInfo)
RETURN BOOLEAN
IS
BEGIN
-- Get all required info about the desired flexfield structure.
-- Note exceptions handle the case that the structure not found or not unique.
--
g_cache_key := (appl_sname || '.' || flex_code || '.' ||
flex_num || '.' || select_comb_from_view);
SELECT
f.application_id, f.table_application_id,
t.table_id, f.application_table_name,
Nvl(select_comb_from_view, f.application_table_name),
f.application_table_type, f.unique_id_column_name,
f.set_defining_column_name, f.dynamic_inserts_feasible_flag,
f.maximum_concatenation_len, f.concatenation_len_warning
INTO
g_cache_value.number_1, g_cache_value.number_2,
g_cache_value.number_3, g_cache_value.varchar2_1,
g_cache_value.varchar2_2,
g_cache_value.varchar2_3, g_cache_value.varchar2_4,
g_cache_value.varchar2_5, g_cache_value.varchar2_6,
g_cache_value.number_4, g_cache_value.varchar2_7
FROM fnd_id_flexs f, fnd_tables t, fnd_application a
WHERE f.id_flex_code = flex_code
AND f.application_id = a.application_id
AND a.application_short_name = appl_sname
AND t.application_id = f.table_application_id
AND t.table_name = f.application_table_name;
SELECT
enabled_flag, concatenated_segment_delimiter,
cross_segment_validation_flag, dynamic_inserts_allowed_flag
INTO
g_cache_value.varchar2_8,
g_cache_value.varchar2_9,
g_cache_value.varchar2_10,
g_cache_value.varchar2_11
FROM fnd_id_flex_structures
WHERE application_id = g_cache_value.number_1
AND id_flex_code = flex_code
AND id_flex_num = flex_num;
cctbl_info.select_comb_from := g_cache_value.varchar2_2;
struct_info.dynamic_inserts_feasible_flag := g_cache_value.varchar2_6;
struct_info.dynamic_inserts_allowed_flag := g_cache_value.varchar2_11;
SELECT
df.application_id, df.descriptive_flexfield_name,
df.description, df.table_application_id,
df.application_table_name, t.table_id,
df.context_required_flag, df.context_column_name,
df.context_user_override_flag, df.concatenated_segment_delimiter,
df.protected_flag, df.default_context_value,
df.default_context_field_name, df.context_override_value_set_id,
df.context_default_type, df.context_default_value,
df.context_runtime_property_funct
INTO
g_cache_value.number_1, g_cache_value.varchar2_1,
g_cache_value.varchar2_2, g_cache_value.number_2,
g_cache_value.varchar2_3, g_cache_value.number_3,
g_cache_value.varchar2_4, g_cache_value.varchar2_5,
g_cache_value.varchar2_6, g_cache_value.varchar2_7,
g_cache_value.varchar2_8, g_cache_value.varchar2_9,
g_cache_value.varchar2_10, g_cache_value.number_4,
g_cache_value.varchar2_11, g_cache_value.varchar2_12,
g_cache_value.varchar2_13
FROM fnd_tables t, fnd_descriptive_flexs_vl df, fnd_application a
WHERE a.application_short_name = flex_app_sname
AND df.application_id = a.application_id
AND df.descriptive_flexfield_name = desc_flex_name
AND t.application_id = df.table_application_id
AND t.table_name = df.application_table_name;
SELECT /*+ LEADING (G) USE_NL (G C S) */
g.application_column_name application_column_name,
c.column_type application_column_type,
Nvl(s.format_type, 'C') value_set_format_type,
Nvl(s.maximum_size, c.width) value_set_maximum_size
FROM fnd_flex_value_sets s, fnd_columns c, fnd_id_flex_segments g
WHERE g.application_id = p_application_id
AND g.id_flex_code = p_id_flex_code
AND g.id_flex_num = p_id_flex_num
AND g.enabled_flag = 'Y'
AND s.flex_value_set_id(+) = g.flex_value_set_id
AND c.application_id = p_table_application_id
AND c.table_id = p_table_id
AND c.column_name = g.application_column_name
ORDER BY g.segment_num;
SELECT
g.application_column_name application_column_name,
c.column_type application_column_type,
Nvl(s.format_type, 'C') value_set_format_type,
Nvl(s.maximum_size, c.width) value_set_maximum_size
FROM fnd_flex_value_sets s, fnd_columns c, fnd_descr_flex_column_usages g
WHERE g.application_id = p_application_id
AND g.descriptive_flexfield_name = p_descriptive_flexfield_name
AND g.descriptive_flex_context_code = p_descriptive_flex_context_co
AND g.enabled_flag = 'Y'
AND s.flex_value_set_id(+) = g.flex_value_set_id
AND c.application_id = p_table_application_id
AND c.table_id = p_table_id
AND c.column_name = g.application_column_name
ORDER BY g.column_seq_num;
SELECT segment_attribute_type fq_name, value_attribute_type sq_name,
application_column_name drv_colname, default_value dflt_val
FROM fnd_value_attribute_types
WHERE application_id = keystruct.application_id
AND id_flex_code = keystruct.id_flex_code;
/* select. For descriptive flexfields there are no qualifiers. In */
/* this case return just the segment info for the given context. */
/* For key flexfields it's an error if no enabled segments found. */
/* Returns FALSE and sets error message if error, or returns TRUE of OK */
/* ------------------------------------------------------------------------- */
FUNCTION get_qualsegs(fstruct IN FND_FLEX_SERVER1.FlexStructId,
nsegs OUT nocopy NUMBER,
segdisp OUT nocopy FND_FLEX_SERVER1.CharArray,
segrqd OUT nocopy FND_FLEX_SERVER1.CharArray,
fqtab OUT nocopy FND_FLEX_SERVER1.FlexQualTable)
RETURN BOOLEAN IS
n_segs NUMBER;
SELECT s.segment_num segnum,
s.display_flag displayed,
s.required_flag required,
sav.segment_attribute_type fqname
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
WHERE s.application_id = kff_struct.application_id
AND s.id_flex_code = kff_struct.id_flex_code
AND s.id_flex_num = kff_struct.id_flex_num
AND s.enabled_flag = 'Y'
AND sav.application_column_name(+) = s.application_column_name
AND sav.application_id(+) = kff_struct.application_id
AND sav.id_flex_code(+) = kff_struct.id_flex_code
AND sav.id_flex_num(+) = kff_struct.id_flex_num
AND sav.attribute_value(+) = 'Y'
ORDER BY s.segment_num;
SELECT column_seq_num segnum, display_flag displayed,
required_flag required
FROM fnd_descr_flex_column_usages
WHERE application_id = dff_struct.application_id
AND descriptive_flexfield_name = dff_struct.desc_flex_name
AND descriptive_flex_context_code = dff_struct.desc_flex_context
AND enabled_flag = 'Y'
ORDER BY column_seq_num;
if(x_xvc_update_cache(fstruct, vdt,
l_cat_segs,
isviolated, rulename) = FALSE) then
return(FND_FLEX_SERVER1.VV_ERROR);
select error_message_text, error_segment_column_name
into rulemsg, errcol
from fnd_flex_vdation_rules_vl
where application_id = fstruct.application_id
and id_flex_code = fstruct.id_flex_code
and id_flex_num = fstruct.id_flex_num
and flex_validation_rule_name = rulename;
nfound := x_cv_rule_select(fstruct, v_date, violated_rule);
/* Uses dynamic SQL to select violated cross-validation rule. */
/* Returns 1 and first violated rule name if any rule violated. */
/* Returns 0 if no violated rules, or < 0 and sets message if error. */
/* Returns 0 for descritive flexfields because they have no cv rules. */
/* ----------------------------------------------------------------------- */
FUNCTION x_cv_rule_select(fstruct IN FND_FLEX_SERVER1.FlexStructId,
v_date IN DATE,
bad_rule OUT nocopy VARCHAR2) RETURN NUMBER IS
num_returned NUMBER;
selected_rule VARCHAR2(15);
dbms_sql.define_column(cursornum, 1, selected_rule, 15);
dbms_sql.column_value(cursornum, 1, selected_rule);
FND_FLEX_SERVER1.add_debug('(DSQL returned ' || selected_rule || ')');
bad_rule := selected_rule;
END x_cv_rule_select;
/* Combination will be cleared after inserting in VALID(). */
/* Cached is limited in size. */
/* Returns in_cache = TRUE and is_violated = FALSE for descriptive */
/* flexfields since there are no cv rules for descriptive flexfields. */
/* ----------------------------------------------------------------------- */
FUNCTION x_xvc_check_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
v_date IN DATE,
p_cat_segs IN VARCHAR2,
in_cache OUT nocopy BOOLEAN,
is_violated OUT nocopy BOOLEAN,
rule_name OUT nocopy VARCHAR2)
RETURN BOOLEAN
IS
l_v_day DATE;
FUNCTION x_xvc_update_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
v_date IN DATE,
p_cat_segs IN VARCHAR2,
is_violated IN BOOLEAN,
rule_name IN VARCHAR2)
RETURN BOOLEAN
IS
l_v_day DATE;
fnd_flex_server1.add_debug('BEGIN SV2.x_xvc_update_cache()');
END x_xvc_update_cache;
/* Deletes the specified combination from the cross-validation cache. */
/* ----------------------------------------------------------------------- */
FUNCTION x_drop_cached_cv_result(fstruct IN FND_FLEX_SERVER1.FlexStructId,
n_segs IN NUMBER,
segs IN FND_FLEX_SERVER1.ValueArray)
RETURN BOOLEAN IS
l_count NUMBER;
p_update_flag IN VARCHAR2,
p_num_workers IN NUMBER,
p_debug_flag IN VARCHAR2)
IS
----------------------
-- Local definitions -
----------------------
l_request_id NUMBER;
SELECT
application_table_name,
set_defining_column_name,
unique_id_column_name
INTO
l_app_table_name,
l_set_def_col_name,
l_unique_id_col_name
FROM
fnd_id_flexs
WHERE
application_id = p_application_id and
id_flex_code = p_id_flex_code;
select count(*) total_ccid,
min(code_combination_id) min_ccid,
max(code_combination_id) max_ccid
from gl_code_combinations
where chart_of_accounts_id = 101;
('SELECT /* $Header: AFFFSV2B.pls 120.2.12010000.1 2008/07/25 14:14:26 appldev ship $ */ ' ||
' COUNT(*), ' ||
' MIN(' || l_unique_id_col_name || '), ' ||
' MAX(' || l_unique_id_col_name || ')' ||
' FROM ' || l_app_table_name ||
' WHERE ' || l_set_def_col_name || '= :b_id_flex_num' ||
' AND enabled_flag = ''Y'' ');
SELECT
MAX(code_combination_id)
FROM
(SELECT code_combination_id
FROM gl_code_combinations
WHERE chart_of_accounts_id = 101
AND enabled_flag = 'Y'
AND code_combination_id >= l_min_ccid_range
ORDER BY code_combination_id)
where rownum <= BATCH_SIZE
************************************************************/
l_max_ccid_range_sql :=
('SELECT /* $Header: AFFFSV2B.pls 120.2.12010000.1 2008/07/25 14:14:26 appldev ship $ */ ' ||
' MAX(' || l_unique_id_col_name || ')' ||
' FROM' ||
' (SELECT ' || l_unique_id_col_name ||
' FROM ' || l_app_table_name ||
' WHERE ' || l_set_def_col_name || '= :b_id_flex_num' ||
' AND enabled_flag = ''Y'' ' ||
' AND ' || l_unique_id_col_name || '>= :b_l_min_ccid_range' ||
' ORDER BY ' || l_unique_id_col_name || ')' ||
' WHERE rownum <= :b_batch_size');
argument4 => p_update_flag,
argument5 => l_min_ccid_range,
argument6 => l_max_ccid_range,
argument7 => p_debug_flag);