[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_combination(fstruct IN FND_FLEX_SERVER1.FlexStructId,
structnum IN NUMBER,
maintmode IN BOOLEAN,
v_date IN DATE,
seg_delim IN VARCHAR2,
ccid_inp IN NUMBER,
combtbl IN FND_FLEX_SERVER1.CombTblInfo,
combcols IN FND_FLEX_SERVER1.TabColArray,
combtypes IN FND_FLEX_SERVER1.CharArray,
user_id IN NUMBER,
nsegs IN NUMBER,
segids_in IN FND_FLEX_SERVER1.ValueIdArray,
segfmts IN FND_FLEX_SERVER1.SegFormats,
dvalues IN FND_FLEX_SERVER1.DerivedVals,
dquals IN FND_FLEX_SERVER1.Qualifiers,
nxcols IN NUMBER,
xcolnames IN FND_FLEX_SERVER1.StringArray,
xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
newcomb OUT nocopy BOOLEAN,
ccid_out OUT nocopy NUMBER) RETURN BOOLEAN;
dinsert IN VARCHAR2,
qsecurity IN VARCHAR2,
required IN VARCHAR2,
allow_nulls IN VARCHAR2,
display_segs IN VARCHAR2,
concat_segs IN VARCHAR2,
vals_or_ids IN VARCHAR2,
where_clause IN VARCHAR2,
extra_cols IN VARCHAR2,
get_valatts IN VARCHAR2,
get_derived IN VARCHAR2,
ccid IN NUMBER) RETURN VARCHAR2 IS
cat_vals VARCHAR2(2000);
validate_mode, dinsert, qsecurity, required,
allow_nulls, display_segs, concat_segs, vals_or_ids,
cat_vals, cat_ids, cat_desc, where_clause,
extra_cols, xtra_cols, get_valatts, valatts,
get_derived, derived, start_d, end_d, enab_flag,
sum_flag, dlim, ccid, ccid_o, v_stat,
s_stat, errseg, retmsg,NULL,NULL,NULL,NULL);
update_table IN VARCHAR2,
eff_activation IN VARCHAR2,
concat_segs IN VARCHAR2,
vals_or_ids IN VARCHAR2,
c_rowid IN VARCHAR2,
alternate_table IN VARCHAR2,
data_field IN VARCHAR2) RETURN VARCHAR2 IS
valid_d VARCHAR2(30);
update_table, eff_activation, concat_segs, vals_or_ids,
c_rowid, alternate_table, data_field, cat_vals,
cat_ids, cat_desc, dlim, v_stat, s_stat, errseg, retmsg);
insert_tok IN VARCHAR2,
update_tok IN VARCHAR2,
display_tok IN VARCHAR2) RETURN VARCHAR2 IS
n_segs NUMBER;
segment_maps(flex_app_sname, flex_code, flex_num, insert_tok, update_tok,
display_tok, insrt_map, updat_map, dspl_map, reqd_map,
n_segs, retmsg);
retstr := retstr || ' Insertable: ' || insrt_map;
SELECT hash_value INTO hash_num FROM fnd_flex_hash
WHERE hash_value = hash_number FOR UPDATE;
dinsert IN VARCHAR2,
qsecurity IN VARCHAR2,
required IN VARCHAR2,
allow_nulls IN VARCHAR2,
display_segs IN VARCHAR2,
concat_segs IN VARCHAR2,
vals_or_ids IN VARCHAR2,
concat_vals_out OUT nocopy VARCHAR2,
concat_ids_out OUT nocopy VARCHAR2,
concat_desc OUT nocopy VARCHAR2,
where_clause IN VARCHAR2,
get_extra_cols IN VARCHAR2,
extra_cols OUT nocopy VARCHAR2,
get_valatts IN VARCHAR2,
valatts OUT nocopy VARCHAR2,
get_derived IN VARCHAR2,
derived_vals OUT nocopy VARCHAR2,
start_date OUT nocopy VARCHAR2,
end_date OUT nocopy VARCHAR2,
enabled_flag OUT nocopy VARCHAR2,
summary_flag OUT nocopy VARCHAR2,
seg_delimiter OUT nocopy VARCHAR2,
ccid_in IN NUMBER,
ccid_out OUT nocopy NUMBER,
vstatus OUT nocopy NUMBER,
segcodes OUT nocopy VARCHAR2,
error_seg OUT nocopy NUMBER,
message OUT nocopy VARCHAR2,
select_comb_from_view IN VARCHAR2,
no_combmsg IN VARCHAR2,
where_clause_msg IN VARCHAR2,
server_debug_mode IN VARCHAR2)
IS
v_date DATE;
flex_code, select_comb_from_view,
flex_num, v_date, vrulestr, data_set, invoking_mode,
validate_mode, dinsert, qsecurity, required, allow_nulls,
display_segs, concat_segs, vals_or_ids, where_clause,
no_combmsg, where_clause_msg,
get_extra_cols, ccid_in, nvalidated, value_dvals, value_vals,
value_ids, value_descs, value_desclens, cc_cols, cc_coltypes,
segtypes, disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
n_xcol_vals, xcol_vals, delim, ccid_out, new_comb, vstatus,
segcodes, error_seg);
update_table IN VARCHAR2,
effective_activ IN VARCHAR2,
concat_segs IN VARCHAR2,
vals_or_ids IN VARCHAR2,
c_rowid IN VARCHAR2,
alternate_table IN VARCHAR2,
data_field IN VARCHAR2,
concat_vals_out OUT nocopy VARCHAR2,
concat_ids_out OUT nocopy VARCHAR2,
concat_desc OUT nocopy VARCHAR2,
seg_delimiter OUT nocopy VARCHAR2,
vstatus OUT nocopy NUMBER,
segcodes OUT nocopy VARCHAR2,
error_seg OUT nocopy NUMBER,
message OUT nocopy VARCHAR2) IS
v_date DATE;
(allow_nulls = 'Y'), (update_table = 'Y'),
omit_activation, concat_segs, (vals_or_ids = 'V'),
FALSE, dummy_coldef, rowid_in, alternate_table,
data_field, nvalidated, value_dvals, value_vals,
value_ids, value_descs, value_desclens, cc_cols,
cc_coltypes, segtypes, disp_segs, delim, vstatus,
segcodes, error_seg);
/* Interprets INSERTABLE, UPDATABLE and DISPLAYED tokens returning */
/* character strings consisting of Y's and N's which are segment by */
/* segment maps indicating which segments are insertable, updatable */
/* required and displayed. */
/* Returns n_segments > 0 on success or = 0 and message if failure. */
/* ------------------------------------------------------------------------ */
PROCEDURE segment_maps(flex_app_sname IN VARCHAR2,
flex_code IN VARCHAR2,
flex_num IN NUMBER,
insert_token IN VARCHAR2,
update_token IN VARCHAR2,
display_token IN VARCHAR2,
insert_map OUT nocopy VARCHAR2,
update_map OUT nocopy VARCHAR2,
display_map OUT nocopy VARCHAR2,
required_map OUT nocopy VARCHAR2,
n_segments OUT nocopy NUMBER,
message OUT nocopy VARCHAR2) IS
n_segs NUMBER;
s_insertable FND_FLEX_SERVER1.BooleanArray;
(NOT evaluate_token(update_token, n_segs, fq_table, s_updatable)) OR
(NOT evaluate_token(insert_token, n_segs, fq_table, s_insertable))) then
goto return_error;
if(s_insertable(i)) then
ins_map := ins_map || 'Y';
insert_map := ins_map;
update_map := upd_map;
FUNCTION insert_combination_at
(fstruct IN FND_FLEX_SERVER1.FlexStructId,
structnum IN NUMBER,
maintmode IN BOOLEAN,
v_date IN DATE,
seg_delim IN VARCHAR2,
ccid_inp IN NUMBER,
combtbl IN FND_FLEX_SERVER1.CombTblInfo,
combcols IN FND_FLEX_SERVER1.TabColArray,
combtypes IN FND_FLEX_SERVER1.CharArray,
user_id IN NUMBER,
nsegs IN NUMBER,
segids_in IN FND_FLEX_SERVER1.ValueIdArray,
segvals_in IN FND_FLEX_SERVER1.valuearray,
segfmts IN FND_FLEX_SERVER1.SegFormats,
dvalues IN FND_FLEX_SERVER1.DerivedVals,
dquals IN FND_FLEX_SERVER1.Qualifiers,
nxcols IN NUMBER,
xcolnames IN FND_FLEX_SERVER1.StringArray,
xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
newcomb OUT nocopy BOOLEAN,
ccid_out OUT nocopy NUMBER) RETURN BOOLEAN
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SAVEPOINT pre_insert_comb_at;
' segs passed to insert_combination().');
SELECT hash_value INTO hash_num FROM fnd_flex_hash
WHERE hash_value = hash_number FOR UPDATE;
sqls := 'select to_char(' || combtbl.application_table_name;
if(FND_FLEX_SERVER1.x_dsql_select_one(ccid_string) <> 1) THEN
GOTO return_false;
sqls := 'insert into ' || combtbl.application_table_name || ' (';
sqls := sqls || 'LAST_UPDATE_DATE, LAST_UPDATED_BY';
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
FND_MESSAGE.set_token('MSG','insert_combination() exception: '||SQLERRM);
ROLLBACK TO SAVEPOINT pre_insert_comb_at;
END insert_combination_AT;
select_comb_from_view IN VARCHAR2,
flex_num IN NUMBER,
val_date IN DATE,
vrulestr IN VARCHAR2,
data_set IN NUMBER,
invoking_mode IN VARCHAR2,
validate_mode IN VARCHAR2,
dinsert IN VARCHAR2,
qsecurity IN VARCHAR2,
required IN VARCHAR2,
allow_nulls IN VARCHAR2,
display_segstr IN VARCHAR2,
concat_segs IN VARCHAR2,
vals_or_ids IN VARCHAR2,
where_clause IN VARCHAR2,
no_combmsg IN VARCHAR2,
where_clause_msg IN VARCHAR2,
get_extra_cols IN VARCHAR2,
ccid_in IN NUMBER,
nvalidated OUT nocopy NUMBER,
displayed_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
stored_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
segment_ids OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
descriptions OUT nocopy FND_FLEX_SERVER1.ValueDescArray,
desc_lengths OUT nocopy FND_FLEX_SERVER1.NumberArray,
seg_colnames OUT nocopy FND_FLEX_SERVER1.TabColArray,
seg_coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
segment_types OUT nocopy FND_FLEX_SERVER1.SegFormats,
displayed_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs,
derived_eff OUT nocopy FND_FLEX_SERVER1.DerivedVals,
table_eff OUT nocopy FND_FLEX_SERVER1.DerivedVals,
derived_quals OUT nocopy FND_FLEX_SERVER1.Qualifiers,
table_quals OUT nocopy FND_FLEX_SERVER1.Qualifiers,
n_column_vals OUT nocopy NUMBER,
column_vals OUT nocopy FND_FLEX_SERVER1.StringArray,
seg_delimiter OUT nocopy VARCHAR2,
ccid_out OUT nocopy NUMBER,
new_combination OUT nocopy BOOLEAN,
v_status OUT nocopy NUMBER,
seg_codes OUT nocopy VARCHAR2,
err_segnum OUT nocopy NUMBER) IS
big_arg VARCHAR2(40);
for_insert BOOLEAN;
l_dinsert VARCHAR2(10);
l_dinsert := Nvl(dinsert, 'N');
select_comb_from_view, flex_num,
kff_id, kff_info, kff_cc) = FALSE) then
goto return_error;
Upper(kff_cc.select_comb_from)) THEN
l_dinsert := 'N';
('FULL', 'PARTIAL', 'PARTIAL_IF_POSSIBLE', 'FOR_INSERT'))) then
FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD VALMODE');
', Dinsert = ' || l_dinsert || '. ');
defer_insrt := ((l_dinsert is not null) and (l_dinsert = 'D'));
no_at := ((l_dinsert is not null) and (l_dinsert = 'O'));
dynam_insrt := (validate_mode = 'FOR_INSERT') or
((l_dinsert is not null) and (l_dinsert in ('Y', 'D', 'O')) and
((kff_cc.application_table_type is null) or
(kff_cc.application_table_type = 'G')) and
(kff_info.dynamic_inserts_feasible_flag = 'Y') and
(kff_info.dynamic_inserts_allowed_flag = 'Y'));
if((invoking_mode = 'L') and (validate_mode = 'FOR_INSERT')) then
comb_id := ccid_in;
(validate_mode in ('PARTIAL','PARTIAL_IF_POSSIBLE','FOR_INSERT')))) then
-- Convert concatenated segments to array and check there are not too many
--
if(FND_FLEX_SERVER2.breakup_catsegs(concat_segs,
kff_info.concatenated_segment_delimiter,
vv_flags.values_not_ids, disp_segs, nsegs, segs) = FALSE) then
goto return_error;
for_insert := (validate_mode = 'FOR_INSERT');
if(not for_insert) then
errcode:=FND_FLEX_SERVER1.parse_where_token(where_clause,nice_where_cl);
if(for_insert) then
-- In for insert mode,
-- if combination exists and ccid matches ccid_in we are done.
-- If it exists and ccid does not match its an error.
-- If combination does not exist continue on.
--
if((nfound = 1) and (ccid_in is not null) and (ccid_in <> 0) and
(ccid_in <> -1) and (comb_id = ccid_in)) then
errcode := FND_FLEX_SERVER1.VV_VALID;
FND_MESSAGE.set_name('FND', 'FLEX-NO DYNAMIC INSERTS');
((for_insert) OR (no_at))) THEN
--
-- We are in Maintenance Form.
-- We will not do the real insert, we will just lock the
-- hash number, and let Maintenenace Form do the insert.
-- So no need to call AutoTrans function.
--
IF (fnd_flex_server1.g_debug_level > 0) THEN
fnd_flex_server1.add_debug('FOR_INSERT:Maintenance Form');
SAVEPOINT pre_insert_comb;
if(insert_combination(kff_id, cc_struct, for_insert, val_date,
kff_info.concatenated_segment_delimiter,
ccid_in, kff_cc, cc_cols, cc_coltypes,
userid, nvals, value_ids, segtypes,
derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
tbl_quals.sq_values, tbl_derv, new_comb,
comb_id) = FALSE) then
ROLLBACK TO SAVEPOINT pre_insert_comb;
ROLLBACK TO SAVEPOINT pre_insert_comb;
and (NOT for_insert)) then
if(call_fdfgli(comb_id) = FALSE) then
ROLLBACK TO SAVEPOINT pre_insert_comb;
ROLLBACK TO SAVEPOINT pre_insert_comb;
if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
(ccid_in = -1) or (comb_id <> ccid_in))) then
FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
(NOT for_insert)) THEN
--
-- We are called from a foreign key form.
-- In this case use the AutoTrans.
--
IF (fnd_flex_server1.g_debug_level > 0) THEN
fnd_flex_server1.add_debug('Autonomous Transaction');
if(insert_combination_at
(kff_id, cc_struct, for_insert, val_date,
kff_info.concatenated_segment_delimiter,
ccid_in, kff_cc, cc_cols, cc_coltypes,
userid, nvals, value_ids, value_vals, segtypes,
derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
tbl_quals.sq_values, tbl_derv, new_comb,
comb_id) = FALSE) then
goto return_error;
if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
(ccid_in = -1) or (comb_id <> ccid_in))) then
FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
if(for_insert and (ccid_in is not null) and (ccid_in <> 0)) then
comb_id := ccid_in;
sqls := 'select to_char(' || combtbl.unique_id_column_name || '), ';
sqls := sqls || ', ' || FND_FLEX_SERVER1.select_clause(combcols(i),
combtypes(i), FND_FLEX_SERVER1.VC_ID,
segfmts.vs_format(i), segfmts.vs_maxsize(i));
sqls := sqls || ' from ' || combtbl.select_comb_from || ' where ';
nrecords := fnd_flex_server1.x_dsql_select(nsegs + nquals + nxcols + 5,
colvals);
/* Inserts combination of segment ids into code combinations table. */
/* */
/* If combination has been created since last checked, get it from */
/* the combinations table along with all the other table values for */
/* the qualifiers, extra columns, and enabled and effective dates. */
/* Determine names of qualifier columns from the derived qualifiers */
/* input to this function. Returns table qualifier values = derived */
/* qualifier values, table effecitivity information = derived */
/* effectivity information, and null for extra columns for new combs. */
/* returns newcomb = TRUE if just created this combination. */
/* If segment column is of type number, then does the default */
/* conversion on the character representation of the segment id. */
/* If segment column is of type date, does default to_date() */
/* conversion for non-translatable date, time and date-time value */
/* sets, but does correctly-formatted conversions for translatable */
/* dates, times and date times. This should emulate client behavior. */
/* */
/* If maintmode = TRUE, then user has called this in FOR_INSERT mode. */
/* In that case do not insert combination if it does not already */
/* exist, just return the ccid. If editing an existing combination */
/* the ccid_inp will be the ccid of the combination being edited. */
/* In that case, do not create a new ccid, but just return ccid_inp. */
/* If the ccid_inp is not null, 0 or -1 consider it to be ok to use. */
/* */
/* Calls user PLSQL validation function after locking the */
/* combination, getting a new CCID, and double-checking to make */
/* sure nobody else has created the combination since we last checked.*/
/* This is done at this time to maintain exact backward compatibility */
/* with the client c-code. It would make more sense to do the user */
/* validation before calling insert_combination(), but then more than */
/* one user might call the user validation function with the same */
/* combination, and somebody might be relying on this corner-case */
/* functionality. */
/* If the user PLSQL validation function returns FALSE, */
/* insert_combination() returns FALSE indicating a fatal error */
/* condition. In this case the error message is already loaded */
/* in FND_MESSAGE. */
/* A SAVEPOINT must be issued externally to this function and */
/* a rollback must occur if insert_combination returns an error */
/* of if it returns new_comb = FALSE to unlock the hash number. */
/* */
/* Returns TRUE on success or FALSE and sets message on error. */
/* ----------------------------------------------------------------------- */
FUNCTION insert_combination(fstruct IN FND_FLEX_SERVER1.FlexStructId,
structnum IN NUMBER,
maintmode IN BOOLEAN,
v_date IN DATE,
seg_delim IN VARCHAR2,
ccid_inp IN NUMBER,
combtbl IN FND_FLEX_SERVER1.CombTblInfo,
combcols IN FND_FLEX_SERVER1.TabColArray,
combtypes IN FND_FLEX_SERVER1.CharArray,
user_id IN NUMBER,
nsegs IN NUMBER,
segids_in IN FND_FLEX_SERVER1.ValueIdArray,
segfmts IN FND_FLEX_SERVER1.SegFormats,
dvalues IN FND_FLEX_SERVER1.DerivedVals,
dquals IN FND_FLEX_SERVER1.Qualifiers,
nxcols IN NUMBER,
xcolnames IN FND_FLEX_SERVER1.StringArray,
xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
newcomb OUT nocopy BOOLEAN,
ccid_out OUT nocopy NUMBER) RETURN BOOLEAN IS
deadlock EXCEPTION;
' segs passed to insert_combination().');
SELECT hash_value INTO hash_num FROM fnd_flex_hash
WHERE hash_value = hash_number FOR UPDATE;
sqls := 'select to_char(' || combtbl.application_table_name;
if(FND_FLEX_SERVER1.x_dsql_select_one(ccid_string) <> 1) then
return(FALSE);
sqls := 'insert into ' || combtbl.application_table_name || ' (';
sqls := sqls || 'LAST_UPDATE_DATE, LAST_UPDATED_BY';
FND_MESSAGE.set_token('MSG','insert_combination() exception: '||SQLERRM);
END insert_combination;
/* done in case user has updated the combinations table to differ */
/* from the derived values. Only checks the expiration and enabled */
/* flags if check_effective flag is TRUE. */
/* Error code indicating result of validation. VV_VALID means all ok */
/* ----------------------------------------------------------------------- */
FUNCTION check_table_comb(t_dval IN FND_FLEX_SERVER1.DerivedVals,
t_quals IN FND_FLEX_SERVER1.Qualifiers,
v_rules IN FND_FLEX_SERVER1.Vrules,
v_date IN DATE,
check_effective IN BOOLEAN) RETURN NUMBER IS
BEGIN
-- Print table segments, qualifiers, extra columns and effectivity info.
--
IF (fnd_flex_server1.g_debug_level > 0) THEN
FND_FLEX_SERVER1.add_debug('Check expiration/vrules on table quals.');
/* selecting all segments and their associated flexfield qualifiers */
/* in a single outer join and then interpreting the tokens for all */
/* segments at once. This requires the fewest possible database rows */
/* retrieved and only a single select statement. */
/* ----------------------------------------------------------------------- */
FUNCTION parse_displayed(fstruct IN FND_FLEX_SERVER1.FlexStructId,
token_str IN VARCHAR2,
dispsegs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
RETURN BOOLEAN IS
n_segs NUMBER;
/* Turns on or off calling of user PLSQL validation just before insert */
/* of new combination. */
/* ------------------------------------------------------------------------- */
PROCEDURE enable_user_validation(Y_or_N IN VARCHAR2) IS
BEGIN
if(Y_or_N = 'Y') then
userval_on := TRUE;
/* Turns on or off fdfgli calling after insert of new combination */
/* in accounting flexfield. */
/* ----------------------------------------------------------------------- */
PROCEDURE enable_fdfgli(Y_or_N IN VARCHAR2) IS
BEGIN
if(Y_or_N = 'Y') then
fdfgli_on := TRUE;
/* if GL_FLEX_INSERT_PKG is not there. */
/* ----------------------------------------------------------------------- */
FUNCTION call_fdfgli(ccid IN NUMBER) RETURN BOOLEAN IS
cursornum INTEGER;
sqlstr := 'BEGIN if(gl_flex_insert_pkg.fdfgli(:n)) then :r := ''Y'';';
PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
p_id_flex_code IN VARCHAR2,
p_id_flex_num IN NUMBER,
p_application_table_name IN VARCHAR2,
p_segment_delimiter IN VARCHAR2,
p_segment_count IN NUMBER,
p_validation_date IN DATE,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_insert_sql IN VARCHAR2,
p_insert_sql_binds IN VARCHAR2,
p_select_sql IN VARCHAR2,
p_select_sql_binds IN VARCHAR2,
x_ccid OUT nocopy NUMBER,
x_encoded_error OUT nocopy VARCHAR2)
IS
l_is_new VARCHAR2(100);
do_dynamic_insert_for_java
(p_application_id => p_application_id,
p_id_flex_code => p_id_flex_code,
p_id_flex_num => p_id_flex_num,
p_application_table_name => p_application_table_name,
p_segment_delimiter => p_segment_delimiter,
p_segment_count => p_segment_count,
p_validation_date => p_validation_date,
p_start_date_active => p_start_date_active,
p_end_date_active => p_end_date_active,
p_insert_sql => p_insert_sql,
p_insert_sql_binds => p_insert_sql_binds,
p_select_sql => p_select_sql,
p_select_sql_binds => p_select_sql_binds,
x_ccid => x_ccid,
x_is_new => l_is_new,
x_encoded_error => x_encoded_error);
END do_dynamic_insert_for_java;
PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
p_id_flex_code IN VARCHAR2,
p_id_flex_num IN NUMBER,
p_application_table_name IN VARCHAR2,
p_segment_delimiter IN VARCHAR2,
p_segment_count IN NUMBER,
p_validation_date IN DATE,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_insert_sql IN VARCHAR2,
p_insert_sql_binds IN VARCHAR2,
p_select_sql IN VARCHAR2,
p_select_sql_binds IN VARCHAR2,
x_ccid OUT nocopy NUMBER,
x_is_new OUT nocopy VARCHAR2,
x_encoded_error OUT nocopy VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_func_name := 'SSV.do_dynamic_insert_for_java()';
l_vc2 := Ltrim(Substr(p_insert_sql_binds,
Instr(p_insert_sql_binds,
l_newline, -1, p_segment_count + 1)),
l_newline);
SELECT hash_value
INTO l_num
FROM fnd_flex_hash
WHERE hash_value = l_hash_value
FOR UPDATE;
l_vc2 := p_select_sql_binds;
dbms_sql.parse(l_cursor, p_select_sql, dbms_sql.native);
' is unable to select from table ' ||
p_application_table_name || '. ' || l_newline ||
'SQLERRM: ' || Sqlerrm);
l_vc2 := p_insert_sql_binds;
EXECUTE IMMEDIATE ('SELECT ' || p_application_table_name ||
'_S.NEXTVAL FROM dual')
INTO l_ccid;
dbms_sql.parse(l_cursor, p_insert_sql, dbms_sql.native);
' is unable to insert new combination. ' ||
' Dynamic INSERT SQL returned ' || l_num ||
' rows, it was expected to return 1 row.');
' is unable to insert new combination. ' ||
' into table ' || p_application_table_name ||
'. ' || l_newline ||
'SQLERRM: ' || Sqlerrm);
END do_dynamic_insert_for_java;
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_security_status IN VARCHAR2,
p_error_message IN VARCHAR2)
IS
BEGIN
--
-- seperate p_parent_value and p_value to get rid of ambiguity.
--
g_cache_key := (p_parent_value || '.' ||
p_application_id || '.' ||
p_responsibility_id || '.' ||
p_value_set_id || '.' ||
p_value);
END update_vsc;
SELECT *
INTO l_vset
FROM fnd_flex_value_sets
WHERE 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : check_value_security' IS NOT NULL
AND flex_value_set_id = p_flex_value_set_id;
'SELECT r.error_message' ||
' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u, fnd_flex_value_rule_lines l' ||
' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
' AND u.application_id = :b_resp_application_id' ||
' AND u.responsibility_id = :b_responsibility_id' ||
' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
' AND l.flex_value_set_id = r.flex_value_set_id' ||
' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
' AND l.include_exclude_indicator = ''E''' ||
' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
' AND ROWNUM < 2';
'SELECT r.error_message' ||
' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u, fnd_flex_value_hier_all h, fnd_flex_value_rule_lines l' ||
' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
' AND u.application_id = :b_resp_application_id' ||
' AND u.responsibility_id = :b_responsibility_id' ||
' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
' AND h.flex_value_set_id = r.flex_value_set_id' ||
' AND l.flex_value_set_id = r.flex_value_set_id' ||
' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
' AND l.include_exclude_indicator = ''E''' ||
' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
' AND ROWNUM < 2 ';
'SELECT r.error_message' ||
' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
' AND u.application_id = :b_resp_application_id' ||
' AND u.responsibility_id = :b_responsibility_id' ||
' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
' AND NOT exists (SELECT NULL' ||
' FROM fnd_flex_value_rule_lines l' ||
' WHERE l.flex_value_rule_id = r.flex_value_rule_id' ||
' AND l.flex_value_set_id = r.flex_value_set_id' ||
' AND l.include_exclude_indicator = ''I''' ||
' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
' )' ||
' AND ROWNUM < 2 ';
'SELECT r.error_message' ||
' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
' AND u.application_id = :b_resp_application_id' ||
' AND u.responsibility_id = :b_responsibility_id' ||
' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
' AND NOT exists (SELECT NULL' ||
' FROM fnd_flex_value_hier_all h, fnd_flex_value_rule_lines l' ||
' WHERE h.flex_value_set_id = r.flex_value_set_id' ||
' AND l.flex_value_set_id = r.flex_value_set_id' ||
' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
' AND l.include_exclude_indicator = ''I''' ||
' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
' )' ||
' AND ROWNUM < 2';
update_vsc(p_application_id => p_resp_application_id,
p_responsibility_id => p_responsibility_id,
p_value_set_id => p_flex_value_set_id,
p_parent_value => p_parent_flex_value,
p_value => p_flex_value,
p_security_status => l_x_security_status,
p_error_message => l_x_error_message);
PROCEDURE delete_dff_compiled(p_application_id IN NUMBER,
p_descriptive_flexfield_name IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM fnd_compiled_descriptive_flexs
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name;
END delete_dff_compiled;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_dff_compiled' arcs_revision,
application_short_name
INTO g_arcs_revision,
l_application_short_name
FROM fnd_application
WHERE application_id = p_application_id;
/* delete_dff_compiled(p_application_id,
p_descriptive_flexfield_name); */
PROCEDURE delete_kff_structure_compiled(p_application_id IN NUMBER,
p_id_flex_code IN VARCHAR2,
p_id_flex_num IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM fnd_compiled_id_flex_structs
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num;
END delete_kff_structure_compiled;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_kff_structure_compiled' arcs_revision,
application_short_name
INTO g_arcs_revision,
l_application_short_name
FROM fnd_application
WHERE application_id = p_application_id;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_kff_structure_compiled' arcs_revision,
id_flex_structure_code
INTO g_arcs_revision,
l_id_flex_structure_code
FROM fnd_id_flex_structures
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num;
/* delete_kff_structure_compiled(p_application_id,
p_id_flex_code,
p_id_flex_num); */
PROCEDURE raise_vst_updated(p_flex_value_set_id IN NUMBER)
IS
l_parameters wf_parameter_list_t := wf_parameter_list_t();
SELECT flex_value_set_name
INTO l_flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id;
wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.vst.updated',
p_event_key => l_flex_value_set_name,
p_event_data => NULL,
p_parameters => l_parameters,
p_send_date => Sysdate);
END raise_vst_updated;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfvcct_record' arcs_revision,
fif.table_application_id,
fif.application_table_name,
ft.table_id,
fif.set_defining_column_name,
fif.unique_id_column_name
INTO g_arcs_revision,
px_kfvcct_record.table_application_id,
px_kfvcct_record.application_table_name,
px_kfvcct_record.table_id,
px_kfvcct_record.set_defining_column_name,
px_kfvcct_record.unique_id_column_name
FROM fnd_id_flexs fif, fnd_tables ft
WHERE fif.application_id = p_application_id
AND fif.id_flex_code = p_id_flex_code
AND ft.application_id = fif.table_application_id
AND ft.table_name = fif.application_table_name;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : create_kfvssc_record' arcs_revision,
fifsg.application_column_name,
ffvs.format_type vset_format_type,
ffvs.maximum_size vset_maximum_size,
ffvt.id_column_type vset_id_column_type,
ffvt.id_column_size vset_id_column_size,
fc.column_type column_type,
fc.width column_size
FROM fnd_id_flex_segments fifsg, fnd_flex_value_sets ffvs,
fnd_columns fc, fnd_flex_validation_tables ffvt
WHERE fifsg.application_id = p_application_id
AND fifsg.id_flex_code = p_id_flex_code
AND fifsg.id_flex_num = p_id_flex_num
AND fifsg.enabled_flag = 'Y'
AND fc.application_id = p_table_application_id
AND fc.table_id = p_table_id
AND fc.column_name = fifsg.application_column_name
AND fifsg.flex_value_set_id = ffvs.flex_value_set_id(+)
AND fifsg.flex_value_set_id = ffvt.flex_value_set_id(+)
ORDER BY fifsg.segment_num, fifsg.segment_name;
SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_sql' arcs_revision,
fifst.concatenated_segment_delimiter
INTO g_arcs_revision,
l_delimiter
FROM fnd_id_flex_structures fifst
WHERE fifst.application_id = p_application_id
AND fifst.id_flex_code = p_id_flex_code
AND fifst.id_flex_num = p_id_flex_num;
px_kfvssc_record.compact_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_concat_segs */ ' ||
l_compact_concat ||
' FROM ' || l_kfvcct_record.application_table_name);
px_kfvssc_record.padded_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_concat_segs */ ' ||
l_padded_concat ||
' FROM ' || l_kfvcct_record.application_table_name);
SELECT fnd_flex_value_sets.validation_type,fnd_flex_validation_tables.additional_where_clause,
fnd_flex_validation_tables.additional_quickpick_columns,
fnd_descriptive_flexs.default_context_field_name,fnd_flex_value_sets.flex_value_set_name
INTO l_validation_type,l_additional_where_clause,l_additional_quickpick_columns,
l_reference_field,l_value_set_name
FROM fnd_flex_value_sets,fnd_descriptive_flexs,fnd_flex_validation_tables
WHERE fnd_flex_value_sets.flex_value_set_id (+)= fnd_descriptive_flexs.context_override_value_set_id
AND fnd_flex_value_sets.flex_value_set_id = fnd_flex_validation_tables.flex_value_set_id(+)
AND fnd_descriptive_flexs.application_id = p_application_id
AND fnd_descriptive_flexs.descriptive_flexfield_name = p_descriptive_flexfield_name;
SELECT fnd_flex_value_sets.validation_type,fnd_flex_value_sets.format_type,
fnd_descr_flex_column_usages.default_type,
fnd_descr_flex_column_usages.default_value,fnd_flex_value_sets.flex_value_set_id,
fnd_flex_value_sets.flex_value_set_name,fnd_descr_flex_column_usages.end_user_column_name
INTO l_validation_type,l_format_type,l_default_type,l_default_value,l_value_set_id,
l_value_set_name,l_segment_name
FROM fnd_descr_flex_column_usages,fnd_flex_value_sets
WHERE fnd_descr_flex_column_usages.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id (+)
AND fnd_descr_flex_column_usages.application_column_name = p_application_column_name
AND fnd_descr_flex_column_usages.APPLICATION_ID = p_application_id
AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEXFIELD_NAME = p_descriptive_flexfield_name
AND fnd_descr_flex_column_usages.ENABLED_FLAG = 'Y'
AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code;
SELECT meaning
INTO l_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type='SEG_VAL_TYPES'
AND lookup_code = l_validation_type;
SELECT additional_where_clause,additional_quickpick_columns
INTO l_additional_where_clause,l_additional_quickpick_columns
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = l_value_set_id;
SELECT meaning
INTO l_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type='FIELD_TYPE'
AND lookup_code = l_format_type;
(SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND descriptive_flex_context_code = p_context_code
AND enabled_flag = 'Y')
LOOP
compute_segment_warnings(v_EnabledSegments.application_column_name);
SELECT application_short_name, application_name
INTO l_application_short_name, l_application_name
FROM fnd_application_vl
WHERE application_id = p_application_id;
SELECT title
INTO l_title
FROM fnd_descriptive_flexs_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name=p_descriptive_flexfield_name;
(SELECT descriptive_flex_context_code
FROM fnd_descr_flex_contexts
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_descriptive_flexfield_name
AND enabled_flag = 'Y')
LOOP
compute_context_warnings(v_EnabledContext.descriptive_flex_context_code);
select meaning into l_validation_type
from fnd_lookup_values_vl
where lookup_type='SEG_VAL_TYPES'
and lookup_code=l_vset_r.validation_type;
select meaning into l_format_type
from fnd_lookup_values_vl
where lookup_type='FIELD_TYPE'
and lookup_code=l_vset_dr.format_type;
select additional_quickpick_columns into l_additional_quickpick_columns from fnd_flex_validation_tables where flex_value_set_id=l_segtype.value_set_id;
select application_short_name, application_name
into l_application_short_name, l_application_name
from fnd_application_vl
where application_id=p_application_id;
select id_flex_structure_code into l_id_flex_structure_code
from fnd_id_flex_structures
where application_id=p_application_id
and id_flex_code=p_id_flex_code
and id_flex_num=p_id_flex_num;
vst_cache_storage.DELETE;
kfvssc_cache_storage.DELETE;
kfvcct_cache_storage.DELETE;