[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select M.ERROR_MESSAGE ' ||
' from FND_FLEX_VALUE_RULE_USAGES U, FND_FLEX_VALUE_RULES_VL M ' ||
' where U.FLEX_VALUE_RULE_ID = M.FLEX_VALUE_RULE_ID ';
'exists (select null ' ||
' from FND_FLEX_VALUE_RULE_LINES L ' ||
' where 1 = 1 ';
PROCEDURE update_vsc(p_application_id IN NUMBER,
p_responsibility_id IN NUMBER,
p_value_set_id IN NUMBER,
p_parent_value IN VARCHAR2,
p_value IN VARCHAR2,
p_error_code IN VARCHAR2)
IS
l_enc_err_msg VARCHAR2(2000) := NULL;
END update_vsc;
PROCEDURE update_fvc(p_value_set_id IN NUMBER,
p_parent_value IN VARCHAR2,
p_value IN VARCHAR2,
p_flexvalue IN flexvalue)
IS
BEGIN
--
-- seperate p_parent_value and p_value to get rid of ambiguity.
--
g_cache_key := (p_parent_value || '.' ||
p_value_set_id || '.' ||
p_value);
END update_fvc;
SELECT segment_name, flex_value_set_id, security_enabled_flag
FROM fnd_id_flex_segments
WHERE application_id = keystruct.application_id
AND id_flex_code = keystruct.id_flex_code
AND id_flex_num = keystruct.id_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num;
SELECT end_user_column_name, flex_value_set_id, security_enabled_flag
FROM fnd_descr_flex_column_usages
WHERE application_id = descstruct.application_id
AND descriptive_flexfield_name = descstruct.desc_flex_name
AND descriptive_flex_context_code = descstruct.desc_flex_context
AND enabled_flag = 'Y'
ORDER BY column_seq_num;
SELECT g.segment_name, g.application_column_name, c.column_type,
c.width, g.required_flag, g.security_enabled_flag,
g.concatenation_description_len,
g.default_type, g.default_value, g.flex_value_set_id,
g.runtime_property_function,
g.additional_where_clause
FROM fnd_id_flex_segments g, fnd_columns c
WHERE g.application_id = keystruct.application_id
AND g.id_flex_code = keystruct.id_flex_code
AND g.id_flex_num = keystruct.id_flex_num
AND g.enabled_flag = 'Y'
AND c.application_id = t_apid
AND c.table_id = t_id
AND c.column_name = g.application_column_name
ORDER BY g.segment_num;
IS SELECT g.end_user_column_name, g.application_column_name,
c.column_type, c.width, g.required_flag,
g.security_enabled_flag, g.concatenation_description_len,
g.default_type, g.default_value, g.flex_value_set_id,
g.runtime_property_function,
NULL
FROM fnd_descr_flex_column_usages g, fnd_columns c
WHERE g.application_id = descstruct.application_id
AND g.descriptive_flexfield_name = descstruct.desc_flex_name
AND g.descriptive_flex_context_code = descstruct.desc_flex_context
AND g.enabled_flag = 'Y'
AND c.application_id = t_apid
AND c.table_id = t_id
AND c.column_name = g.application_column_name
ORDER BY g.column_seq_num;
update_fvc(vsinf.vsid, parentval, thisseg, l_flexvalue);
SELECT enabled_flag, start_date_active, end_date_active,
summary_flag, compiled_value_attributes, description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vsid
AND flex_value = val;
SELECT enabled_flag, start_date_active, end_date_active,
summary_flag, compiled_value_attributes, description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vsid
AND flex_value = val
AND parent_flex_value_low = parnt;
SELECT enabled_flag, start_date_active, end_date_active,
summary_flag, compiled_value_attributes,
flex_value, flex_value_meaning, description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vsid
AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
((p_is_value = 'I') AND (flex_value = val)));
SELECT enabled_flag, start_date_active, end_date_active,
summary_flag, compiled_value_attributes,
flex_value, flex_value_meaning, description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vsid
AND parent_flex_value_low = parnt
AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
((p_is_value = 'I') AND (flex_value = val)));
SELECT application_table_name, value_column_name, value_column_type,
id_column_name, id_column_type, meaning_column_name,
meaning_column_type, enabled_column_name, start_date_column_name,
end_date_column_name, summary_column_name,
compiled_attribute_column_name, additional_quickpick_columns,
summary_allowed_flag, additional_where_clause
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_flex_value_set_id;
l_sql_pieces.DELETE;
l_sql := 'select ' ||
select_clause(l_tbl_rec.value_column_name, l_tbl_rec.value_column_type,
VC_VALUE, p_vs_info.vsformat, p_vs_info.maxsize);
select_clause(l_tbl_rec.id_column_name, l_tbl_rec.id_column_type,
VC_ID, p_vs_info.vsformat, p_vs_info.maxsize);
select_clause(l_tbl_rec.meaning_column_name, l_tbl_rec.meaning_column_type,
VC_DESCRIPTION, p_vs_info.vsformat, p_vs_info.maxsize);
l_nrecords := x_dsql_select(8, l_results);
nfound := x_dsql_select_one(stored_val);
update_vsc(user_apid, user_respid, vsinfo.vsid,
parentval, val, l_return_code);
/* contains a non-null value. Returns the updated qualifier values */
/* array and number of qualifier values found or < 0 if error. */
/* No qualifier values for descriptive flexfields. */
/* */
/* Note: Do not order by segment_attribute_type as this causes */
/* GL_ACCOUNT/GL_ACCOUNT_TYPE to appear before GL_GLOBAL/DETAIL... */
/* instead of after it as is the case in the client code. */
/* ----------------------------------------------------------------------- */
FUNCTION qualifier_values(ffstruct IN FlexStructId,
valset_id IN NUMBER,
cvas IN VARCHAR2,
nqualifs IN NUMBER,
fqnames IN QualNameArray,
sqnames IN QualNameArray,
sqvals IN OUT nocopy ValAttribArray) RETURN NUMBER IS
cva_index NUMBER;
SELECT id_flex_application_id fapid, id_flex_code fcode,
segment_attribute_type fqname, value_attribute_type sqname
FROM fnd_flex_validation_qualifiers
WHERE flex_value_set_id = vs_id
ORDER BY assignment_date, id_flex_application_id, id_flex_code,
value_attribute_type;
SELECT v.segment_attribute_type fq_name,
v.value_attribute_type sq_name,
v.application_column_name drv_colname,
v.default_value dflt_val
FROM fnd_value_attribute_types v, fnd_segment_attribute_values s
WHERE v.application_id = s.application_id
AND v.id_flex_code = s.id_flex_code
AND v.segment_attribute_type = s.segment_attribute_type
AND s.application_id = keystruct.application_id
AND s.id_flex_code = keystruct.id_flex_code
AND s.id_flex_num = keystruct.id_flex_num
AND s.application_column_name = colname
AND s.attribute_value = 'Y';
SELECT flex_value_set_id, parent_flex_value_set_id,
security_enabled_flag,
validation_type, format_type, maximum_size,
alphanumeric_allowed_flag, uppercase_only_flag,
numeric_mode_enabled_flag, number_precision, minimum_value,
maximum_value, flex_value_set_name
INTO l_vsi
FROM fnd_flex_value_sets
WHERE flex_value_set_id = value_set_id;
/* Creates an "and clause" of a SQL select statement for determining */
/* if the value passed in is in between the values in the columns */
/* whose names are in mincol and maxcol. Returns NULL if no */
/* statement required for this column */
/* */
/* By isolating all in range clauses to this routine we can ensure */
/* uniform range behavior. */
/* */
/* Null handling: If val is NULL, requires either mincol or maxcol */
/* to be null. If the value contained in mincol (maxcol) is NULL it */
/* means there is no lower(upper) limit on the range of val */
/* */
/* Clause is of the form: */
/* 'and (10 between nvl(to_number(MINCOL), 10) */
/* and nvl(to_number(MAXCOL), 10)) ' */
/* */
/* ----------------------------------------------------------------------- */
PROCEDURE x_inrange_clause(valstr IN VARCHAR2,
valtype IN VARCHAR2,
mincol IN VARCHAR2,
maxcol IN VARCHAR2) IS
clause VARCHAR2(500);
/* Function to convert a column name into a SQL clause for selecting */
/* a value, id, or description from that column into the correct */
/* character format for the given value set. */
/* */
/* This function does the default to_char() conversion for */
/* non-translatable date, time, date-time, or number value sets */
/* in order to maintain backward compatibility with old client code. */
/* For translatable date, time, and date-time value sets this code */
/* converts the data stored in the date column to date storage format */
/* */
/* Does not check for compatibility of column type and value set. */
/* ----------------------------------------------------------------------- */
FUNCTION select_clause(colname IN VARCHAR2,
coltype IN VARCHAR2,
v_component IN BINARY_INTEGER,
vs_fmt IN VARCHAR2,
vs_len IN NUMBER) RETURN VARCHAR2 IS
clause VARCHAR2(2000);
END select_clause;
/* value into a table column of the given type, or for inserting that */
/* value to a value stored in a table column of the given type. */
/* */
/* If the table column is a number assume the value input is a number */
/* and just use the text of the number. If the table column is */
/* CHAR or VARCHAR2, then substitute all single quotes in the value */
/* with double quotes and surround the value with single quotes. */
/* If table column is DATE, do to_date() conversion using the format */
/* appropriate for the value set. */
/* */
/* To maintin backward compatibility with existing client code must */
/* build in the BUG that if value set format type is old-fashioned */
/* date, time or date-time (D, T or t), then default to_date() */
/* conversions are done. This means non-validated Date-time values */
/* cannot be inserted into the combinations table. */
/* ----------------------------------------------------------------------- */
PROCEDURE x_compare_clause(coltype IN VARCHAR2,
colname IN VARCHAR2,
char_val IN VARCHAR2,
v_component IN BINARY_INTEGER,
vs_fmt IN VARCHAR2,
vs_len IN NUMBER)
IS
datefmt VARCHAR2(30);
/* Uses dynamic SQL to select up to one varchar2 valued column from */
/* a table using the select statement passed in. Returns 0, NULL */
/* if no rows found, or 1 and the column value if 1 row found, or */
/* 2 and the column value of the first row found if more than 1 row */
/* matches selection criteria, or < 0 if other errors. */
/* Invalid rowid exception mapped back to no data found. */
/* ----------------------------------------------------------------------- */
FUNCTION x_dsql_select_one(returned_column OUT nocopy VARCHAR2) RETURN NUMBER IS
cursornum INTEGER;
selected_col VARCHAR2(2000);
selected_col := NULL;
dbms_sql.define_column(cursornum, 1, selected_col, 2000);
dbms_sql.column_value(cursornum, 1, selected_col);
returned_column := selected_col;
add_debug('(DSQL returned ' || selected_col || ')');
FND_MESSAGE.set_token('MSG', 'x_dsql_select_one() could not fetch rows');
dbms_sql.column_value(cursornum, 1, selected_col);
returned_column := selected_col;
END x_dsql_select_one;
/* Uses dynamic SQL to select n_selected_cols of VARCHAR2 type from */
/* a table using the select statement passed in. Returns number */
/* of rows found, or sets error and returns < 0 if error. */
/* Invalid rowid exception mapped back to no data found. */
/* ----------------------------------------------------------------------- */
FUNCTION x_dsql_select(n_selected_cols IN NUMBER,
returned_columns OUT nocopy StringArray) RETURN NUMBER
IS
cursornum INTEGER;
selected_cols StringArray;
for i in 1..n_selected_cols loop
-- The following prevents NO-DATA-FOUND exception...
selected_cols(i) := NULL;
dbms_sql.define_column(cursornum, i, selected_cols(i), 2000);
for i in 1..n_selected_cols loop
dbms_sql.column_value(cursornum, i, selected_cols(i));
add_debug(' ''' || selected_cols(i) || '''');
FND_MESSAGE.set_token('MSG', 'x_dsql_select() could not fetch rows');
returned_columns := selected_cols;
for i in 1..n_selected_cols loop
dbms_sql.column_value(cursornum, i, selected_cols(i));
add_debug(' ''' || selected_cols(i) || '''');
returned_columns := selected_cols;
END x_dsql_select;
px_sql_pieces.DELETE;
str_cache_storage.DELETE;