[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ROW_INSERTABLE CONSTANT INTEGER := 1;
SELECT profile_option_value, level_value, level_value_application_id, level_value2
FROM fnd_profile_option_values
WHERE application_id = appl_id
AND profile_option_id = prof_id
AND level_id = lev_id;
g_prof_val_4_update profile_value;
g_prof_val_4_insert profile_value;
g_lev_val_4_update profile_level;
g_lev_val_4_insert profile_level;
g_lev_val_appl_4_update profile_level;
g_lev_val_appl_4_insert profile_level;
g_lev_val_4_update.delete;
g_lev_val_4_insert.delete;
g_lev_val_appl_4_update.delete;
g_lev_val_appl_4_insert.delete;
g_prof_val_4_update.delete;
g_prof_val_4_insert.delete;
select hierarchy_type into l_db_hierarchy_type
from fnd_profile_options
where profile_option_name = x_profile_option_name;
** The procedure separates insertable and updatable rows and
** collects them into global collections.
*/
procedure add_rows(x_profile_value_c in out nocopy profile_value ,
x_level_value_c in out nocopy profile_level,
x_level_val_appl_id_c in out nocopy profile_level,
x_profile_value varchar2,
x_level_value number,
x_level_value_appl_id number,
x_level_value2 number,
x_prof_ind number,
x_mode number)
is
begin
x_profile_value_c(x_prof_ind) := x_profile_value;
if( x_mode = INSERT_ONLY) then
x_level_value_c(x_prof_ind) := x_level_value2;
if( x_mode = UPDATE_ONLY) then
x_level_value_c(x_prof_ind) := x_level_value;
if( x_mode = INSERT_ONLY) then
x_level_value_c(x_prof_ind) := x_level_value;
if( x_mode = UPDATE_ONLY) then
x_level_value_c(x_prof_ind) := x_level_value2;
** COLLECT_INSERTABLE_ROWS - AOL INTERNAL ONLY
** The procedure collects insertable rows.
*/
procedure collect_insertable_rows(x_appl_id number,
x_prof_id number
)
is
cursor pov_to(appl_id number, prof_id number, lev_id number) is
select profile_option_value, level_value, level_value_application_id, level_value2
from fnd_profile_option_values
where application_id = appl_id
and profile_option_id = prof_id
and level_id = lev_id;
l_is_insertable boolean;
l_is_insertable := TRUE;
l_is_insertable := FALSE;
if (l_is_insertable) then
add_rows(g_prof_val_4_insert,
g_lev_val_4_insert,
g_lev_val_appl_4_insert,
from_rec.profile_option_value,
from_rec.level_value,
from_rec.level_value_application_id,
from_rec.level_value2,
l_prof_ind,
INSERT_ONLY);
end collect_insertable_rows;
** The procedure collects insertable and updatable rows.
*/
procedure collect_all_rows(x_appl_id number,
x_prof_id number
)
is
cursor pov_4_update(appl_id number, prof_id number, lev_id number) is
select profile_option_value, level_value, level_value_application_id, level_value2
from fnd_profile_option_values
where application_id = appl_id
and profile_option_id = prof_id
and level_id = lev_id
for update;
l_is_insertable boolean;
l_is_insertable := TRUE;
FOR to_rec IN pov_4_update(x_appl_id, x_prof_id, g_to_lev_id) LOOP
if ( is_row_updatable (
from_rec.level_value,
to_rec.level_value,
from_rec.level_value_application_id,
to_rec.level_value_application_id,
from_rec.level_value2,
to_rec.level_value2
)
) then
add_rows(g_prof_val_4_update,
g_lev_val_4_update,
g_lev_val_appl_4_update,
from_rec.profile_option_value,
to_rec.level_value,
to_rec.level_value_application_id,
to_rec.level_value2,
l_prof_ind,
UPDATE_ONLY
);
l_is_insertable := FALSE;
if (l_is_insertable) then
add_rows(g_prof_val_4_insert,
g_lev_val_4_insert,
g_lev_val_appl_4_insert,
from_rec.profile_option_value,
from_rec.level_value,
from_rec.level_value_application_id,
from_rec.level_value2,
l_prof_ind,
INSERT_ONLY);
** profile option value rows can be either updatable rows or insertable rows.
**
** when a profile has rows existing at the target hierarchy level, they are called
** updatable rows. For example, when a profile hierarchy switch is from
** SECURITY to SERVRESP, all rows in FND_PROFILE_OPTION_VALUES for this profile
** are considered updatable if there exist a valid LEVEL_VALUE2 value at level 10007.
**
** Insertable rows are all rows at source hierarchy level minus rows considered as
** updatable.
**
** 1. UPDATE_ONLY
** In this mode profile option value and who columns of updatable rows are updated
** from the similar rows at the source hierarchy level.
** 2. INSERT_ONLY
** In this mode profile option value and who columns of insertable rows are inserted
** at the target hierarchy level. Updatable rows are untouched.
** 3. INSERT_UPDATE
** This mode is combination of both (1) and (2).
*/
procedure carry_profile_values(
X_PROFILE_OPTION_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_PROFILE_OPTION_ID in NUMBER,
X_TO_HIERARCHY_TYPE in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_MODE in NUMBER default INSERT_UPDATE
)
is
begin
reset;
if(X_MODE = INSERT_UPDATE or X_MODE = UPDATE_ONLY) then
collect_all_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
if(X_MODE = INSERT_ONLY) then
collect_insertable_rows(X_APPLICATION_ID,X_PROFILE_OPTION_ID);
(X_MODE = INSERT_UPDATE or X_MODE= UPDATE_ONLY)
and
(g_prof_val_4_update.first is not null)
) then
FORALL rec in g_prof_val_4_update.first .. g_prof_val_4_update.last
update fnd_profile_option_values
set profile_option_value = g_prof_val_4_update(rec),
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by
where level_id = g_to_lev_id
and application_id = x_application_id
and profile_option_id = x_profile_option_id
and level_value =
decode( g_type, TYPE_SERVER_2_SERVRESP, -1,g_lev_val_4_update(rec))
and nvl(level_value_application_id,-11111) =
nvl(decode(g_type, TYPE_SERVER_2_SERVRESP,-1,
TYPE_SERVRESP_2_SERVER, null,
g_lev_val_appl_4_update(rec)
), -11111
)
and nvl(level_value2, -11111) =
nvl(decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_update(rec),
TYPE_SECURITY_2_SERVRESP, -1,
null
), -11111
);
(X_MODE = INSERT_UPDATE or X_MODE= INSERT_ONLY)
and
(g_prof_val_4_insert.first is not null)
)
then
FORALL rec in g_prof_val_4_insert.first .. g_prof_val_4_insert.last
insert into fnd_profile_option_values (
APPLICATION_ID,
PROFILE_OPTION_ID,
LEVEL_ID,
LEVEL_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROFILE_OPTION_VALUE,
LEVEL_VALUE_APPLICATION_ID,
LEVEL_VALUE2
) values (
X_APPLICATION_ID,
X_PROFILE_OPTION_ID,
g_to_lev_id,
decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
g_lev_val_4_insert(rec)
),
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
g_prof_val_4_insert(rec),
decode(g_type,TYPE_SERVER_2_SERVRESP, -1,
TYPE_SERVRESP_2_SERVER,null,
g_lev_val_appl_4_insert(rec)
),
decode(g_type,TYPE_SERVER_2_SERVRESP, g_lev_val_4_insert(rec),
TYPE_SECURITY_2_SERVRESP, -1,
null
)
);