The following lines contain the word 'select', 'insert', 'update' or 'delete':
select '1'
from dual
where exists
(select '1'
from user_objects
where object_name=p_level_view_name
and object_type='VIEW');
l_sql:='SELECT name FROM '||p_level_view_name||' WHERE code = :1';
select
DIM_LEVEL_INDEX,
DIM_LEVEL_VALUE
from bsc_user_list_access
where RESPONSIBILITY_ID=p_resp_id
and TAB_ID=p_tab_id
order by DIM_LEVEL_INDEX desc;
/** select
max(DIM_LEVEL_INDEX)
into
l_dim_ind
from
bsc_sys_com_dim_levels
where tab_id=p_tab_id;
select
b.LEVEL_VIEW_NAME
from
bsc_sys_com_dim_levels a,
bsc_sys_dim_levels_vl b
where
a.tab_id=p_tab_id
and a.DIM_LEVEL_INDEX=p_level_index
and a.DIM_LEVEL_ID=b.dim_level_id;
select
b.LEVEL_PK_COL
from
bsc_sys_com_dim_levels a,
bsc_sys_dim_levels_vl b
where a.tab_id=p_tab_id
and a.dim_level_index=p_level_index
and a.PARENT_DIM_LEVEL_ID=b.dim_level_id;
l_sql:='SELECT to_char('||l_parent_pk||') FROM '||l_level_view||' WHERE code= :1';
procedure Update_tab_access (
P_ROWID in ROWID := null,
P_RESP_ID in number,
P_TAB_ID in number,
P_START_DATE in date,
P_END_DATE in date,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'Update_tab_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
select
indicator
from
bsc_tab_indicators
where tab_id=p_tab_id;
update bsc_user_tab_access
set responsibility_id = P_RESP_ID,
tab_id = P_TAB_ID,
start_date = P_START_DATE,
end_date = P_END_DATE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
where rowid = P_ROWID;
UPDATE bsc_user_tab_access
SET
start_date = P_START_DATE,
end_date = P_END_DATE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE responsibility_id = P_RESP_ID
AND tab_id = P_TAB_ID ;
insert_kpi_access (
P_RESP_ID =>p_resp_id,
P_INDICATOR =>l_indicators_rec.indicator,
P_START_DATE =>p_start_date,
P_END_DATE =>p_end_date,
x_return_status =>x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
) ;
end Update_tab_access;
procedure Update_list_access (
P_ROWID in ROWID := null,
P_RESP_ID in number,
P_TAB_ID in number,
P_DIM_LEVEL_INDEX in number,
P_DIM_LEVEL_VALUE in VARCHAR2,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'Update_list_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
update bsc_user_list_access
set responsibility_id = P_RESP_ID,
tab_id = P_TAB_ID,
DIM_LEVEL_INDEX = P_DIM_LEVEL_INDEX,
DIM_LEVEL_VALUE = P_DIM_LEVEL_VALUE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
where rowid = P_ROWID;
UPDATE bsc_user_list_access
SET
DIM_LEVEL_VALUE = P_DIM_LEVEL_VALUE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE responsibility_id = P_RESP_ID
AND tab_id = P_TAB_ID
and dim_level_index = P_DIM_LEVEL_INDEX ;
end update_list_access;
procedure insert_list_access (
P_RESP_ID in number,
P_TAB_ID in number,
P_DIM_LEVEL_INDEX in number,
P_DIM_LEVEL_VALUE in VARCHAR2,
P_CREATION_DATE in date :=null,
p_CREATED_BY in number :=null,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_list_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
insert into bsc_user_list_access
(
RESPONSIBILITY_ID ,
TAB_ID ,
DIM_LEVEL_INDEX ,
DIM_LEVEL_VALUE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
P_RESP_ID ,
P_TAB_ID ,
P_DIM_LEVEL_INDEX ,
P_DIM_LEVEL_VALUE ,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login
);
end insert_list_access;
select 'Y'
from dual
where exists (select TAB_ID from bsc_sys_com_dim_levels where tab_id=p_tab_id);
select 'Y'
from dual
where exists
(select 'Y' from bsc_user_list_access
where RESPONSIBILITY_ID=p_resp_id
and TAB_ID=p_tab_id);
procedure insert_tab_access (
P_RESP_ID in number,
P_TAB_ID in number,
P_START_DATE in date,
P_END_DATE in date,
P_CREATED_BY in NUMBER := null,
P_CREATION_DATE in DATE := null,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'insert_tab_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
select 'Y'
from dual
where exists
(select 'Y'
from bsc_user_tab_access
where responsibility_id=p_resp_id
and tab_id=p_tab_id);
select
indicator
from
bsc_tab_indicators
where tab_id=p_tab_id;
select distinct
DIM_LEVEL_INDEX DIM_LEVEL_INDEX,
'0' DIM_LEVEL_VALUE ---default value 'ALL'
from
bsc_sys_com_dim_levels
where tab_id=p_tab_id;
Update_tab_access (
P_RESP_ID =>p_resp_id,
P_TAB_ID =>p_tab_id,
P_START_DATE =>p_start_date,
P_END_DATE =>p_end_date,
x_return_status =>x_return_status,
x_errorcode =>x_errorcode,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data
) ;
insert into bsc_user_tab_access (
responsibility_id,
tab_id,
start_date,
end_date,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
values(
P_resp_id,
P_tab_id,
P_start_date,
P_end_date,
L_CREATED_BY,
L_CREATION_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATE_DATE);
insert_kpi_access (
P_RESP_ID =>p_resp_id,
P_INDICATOR =>l_indicators_rec.indicator,
P_START_DATE =>p_start_date,
P_END_DATE =>p_end_date,
x_return_status =>x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
) ;
insert_list_access (
P_RESP_ID=>p_resp_id,
P_TAB_ID=>p_tab_id ,
P_DIM_LEVEL_INDEX=>l_comm_dim_value_rec.DIM_LEVEL_INDEX,
P_DIM_LEVEL_VALUE=>l_comm_dim_value_rec.DIM_LEVEL_value,
x_return_status => x_return_status ,
x_errorcode => x_errorcode ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
end insert_tab_access;
procedure insert_kpi_access (
P_RESP_ID in number,
P_INDICATOR in number,
P_START_DATE in date,
P_END_DATE in date,
P_CREATED_BY in NUMBER := null,
P_CREATION_DATE in DATE := null,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'insert_kpi_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
select 'Y'
from dual
where exists
(select 'Y'
from bsc_user_kpi_access
where responsibility_id=p_resp_id
and indicator=p_indicator);
Update_kpi_access (
P_RESP_ID =>p_resp_id,
P_INDICATOR =>p_indicator,
P_START_DATE =>p_start_date,
P_END_DATE =>p_end_date,
x_return_status =>x_return_status,
x_errorcode =>x_errorcode,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data
) ;
insert into bsc_user_kpi_access (
responsibility_id,
indicator,
start_date,
end_date,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
values(
P_resp_id,
P_indicator,
P_start_date,
P_end_date,
L_CREATED_BY,
L_CREATION_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATE_DATE);
end insert_kpi_access;
procedure Update_kpi_access (
P_ROWID in ROWID := null,
P_RESP_ID in number,
P_INDICATOR in number,
P_START_DATE in date,
P_END_DATE in date,
P_LAST_UPDATED_BY in NUMBER := null,
P_LAST_UPDATE_LOGIN in NUMBER := null,
P_LAST_UPDATE_DATE in DATE := null,
p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
p_commit IN VARCHAR2 := fnd_api.g_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_sysdate DATE := sysdate;
l_api_name CONSTANT VARCHAR2(30) := 'Update_kpi_access';
l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
update bsc_user_kpi_access
set responsibility_id = P_RESP_ID,
indicator = P_INDICATOR,
start_date = P_START_DATE,
end_date = P_END_DATE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
where rowid = P_ROWID;
UPDATE bsc_user_kpi_access
SET
start_date = P_START_DATE,
end_date = P_END_DATE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE responsibility_id = P_RESP_ID
AND indicator = P_INDICATOR ;
end Update_kpi_access;
delete from bsc_user_kpi_access
where responsibility_id=p_resp_id
and indicator=p_indicator;