[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function is_update_allowed(X_CUSTOM_LEVEL_NEW in varchar2,
X_CUSTOM_LEVEL_OLD in varchar2) return varchar2
is
begin
-- Cannot overwrite data with a higher customization level
if X_CUSTOM_LEVEL_NEW = 'U' then
if X_CUSTOM_LEVEL_OLD in ('C','L') then
return ('N'); -- Error will be logged
return ('Y'); -- Return Y. Update is based on the caller
end is_update_allowed;
select VIEW_KEY into l_view_key
from WF_WL_VIEWS
where VIEW_ID=x_view_id;
select VIEW_ID into l_view_id
from WF_WL_VIEWS
where VIEW_KEY=X_KEY;
select CUSTOMIZATION_LEVEL into l_custom_level
from WF_WL_VIEWS
where VIEW_ID=l_view_id;
procedure INSERT_VIEW_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_PARENT_VIEW_ID in NUMBER,
X_VIEW_TYPE in VARCHAR2,
X_APPLICATION_MODULE in VARCHAR2,
X_AM_IMPL_CLASS in VARCHAR2,
X_AM_IMPL_METHOD in VARCHAR2,
X_VO_IMPL_CLASS in VARCHAR2,
X_VO_IMPL_METHOD in VARCHAR2,
X_VO_INSTANCE in VARCHAR2,
X_PLSQL_API in VARCHAR2,
X_SQL_TEXT in VARCHAR2,
X_CUSTOMIZATION_LEVEL in VARCHAR2,
X_SEQUENCE in NUMBER,
X_STATUS in VARCHAR2,
X_TITLE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2)
is
cursor c_newrow is select ROWID from WF_WL_VIEWS
where VIEW_KEY = X_VIEW_KEY;
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
insert into WF_WL_VIEWS (VIEW_ID,
VIEW_KEY,
PARENT_VIEW_ID,
VIEW_TYPE,
APPLICATION_MODULE,
AM_IMPL_CLASS,
AM_IMPL_METHOD,
VO_IMPL_CLASS,
VO_IMPL_METHOD,
VO_INSTANCE,
PLSQL_API,
SQL_TEXT,
CUSTOMIZATION_LEVEL,
SEQUENCE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (WF_WL_VIEWS_S.NEXTVAL,
X_VIEW_KEY,
X_PARENT_VIEW_ID,
X_VIEW_TYPE,
X_APPLICATION_MODULE,
X_AM_IMPL_CLASS,
X_AM_IMPL_METHOD,
X_VO_IMPL_CLASS,
X_VO_IMPL_METHOD,
X_VO_INSTANCE,
X_PLSQL_API,
X_SQL_TEXT,
X_CUSTOMIZATION_LEVEL,
X_SEQUENCE,
X_STATUS,
l_ludate, --X_CREATION_DATE,
l_luby, -- X_CREATED_BY,
l_ludate, -- X_LAST_UPDATE_DATE,
l_luby, -- X_LAST_UPDATED_BY,
0, -- X_LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
insert into WF_WL_VIEWS_TL (VIEW_ID, TITLE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
select WF_WL_VIEWS_S.CURRVAL,
X_TITLE, X_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG'),
l_ludate,
l_luby,
l_ludate,
l_luby,
0,
FND_GLOBAL.SECURITY_GROUP_ID
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WF_WL_VIEWS_TL TL, WF_WL_VIEWS B
where B.VIEW_ID = TL.VIEW_ID and
B.VIEW_KEY = X_VIEW_KEY and
TL.LANGUAGE = L.LANGUAGE_CODE);
raise_application_error (-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_BY_KEY: unable to create view '||X_VIEW_KEY);
end INSERT_VIEW_BY_KEY;
procedure UPDATE_VIEW_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_PARENT_VIEW in VARCHAR2,
X_VIEW_TYPE in VARCHAR2,
X_APPLICATION_MODULE in VARCHAR2,
X_AM_IMPL_CLASS in VARCHAR2,
X_AM_IMPL_METHOD in VARCHAR2,
X_VO_IMPL_CLASS in VARCHAR2,
X_VO_IMPL_METHOD in VARCHAR2,
X_VO_INSTANCE in VARCHAR2,
X_PLSQL_API in VARCHAR2,
X_SQL_TEXT in VARCHAR2,
X_CUSTOMIZATION_LEVEL in VARCHAR2,
X_SEQUENCE in NUMBER,
X_STATUS in VARCHAR2,
X_TITLE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_custom_level varchar2(1);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
update WF_WL_VIEWS B
set B.VIEW_TYPE = X_VIEW_TYPE,
B.PARENT_VIEW_ID = l_parent_view_id,
B.APPLICATION_MODULE = X_APPLICATION_MODULE,
B.AM_IMPL_CLASS = X_AM_IMPL_CLASS,
B.AM_IMPL_METHOD = X_AM_IMPL_METHOD,
B.VO_IMPL_CLASS = X_VO_IMPL_CLASS,
B.VO_IMPL_METHOD = X_VO_IMPL_METHOD,
B.VO_INSTANCE = X_VO_INSTANCE,
B.PLSQL_API = X_PLSQL_API,
B.SQL_TEXT = X_SQL_TEXT,
B.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
B.SEQUENCE = X_SEQUENCE,
B.STATUS = X_STATUS,
B.LAST_UPDATE_DATE = l_ludate, -- X_LAST_UPDATE_DATE,
B.LAST_UPDATED_BY = l_luby, -- X_LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN = 0
where B.VIEW_KEY = X_VIEW_KEY;
if is_update_allowed(X_CUSTOMIZATION_LEVEL, l_custom_level) = 'Y' then
update WF_WL_VIEWS B
set B.STATUS = X_STATUS,
B.LAST_UPDATE_DATE = l_ludate, -- X_LAST_UPDATE_DATE,
B.LAST_UPDATED_BY = l_luby, -- X_LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN = 0
where B.VIEW_KEY = X_VIEW_KEY;
raise_application_error(-20000, 'WF_WL_VIEWS_PKG.UPDATE_VIEW_BY_KEY unable to update view '||X_VIEW_KEY||' due to customization');
TRANSLATE_VIEW_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_BY_KEY.X_VIEW_KEY ,
X_TITLE => UPDATE_VIEW_BY_KEY.X_TITLE ,
X_DESCRIPTION => UPDATE_VIEW_BY_KEY.X_DESCRIPTION,
X_OWNER => UPDATE_VIEW_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_VIEW_BY_KEY.X_LAST_UPDATE_DATE);
INSERT_VIEW_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_BY_KEY.X_VIEW_KEY ,
X_PARENT_VIEW_ID => l_parent_view_id ,
X_VIEW_TYPE => UPDATE_VIEW_BY_KEY.X_VIEW_TYPE ,
X_APPLICATION_MODULE => UPDATE_VIEW_BY_KEY.X_APPLICATION_MODULE ,
X_AM_IMPL_CLASS => UPDATE_VIEW_BY_KEY.X_AM_IMPL_CLASS ,
X_AM_IMPL_METHOD => UPDATE_VIEW_BY_KEY.X_AM_IMPL_METHOD ,
X_VO_IMPL_CLASS => UPDATE_VIEW_BY_KEY.X_VO_IMPL_CLASS ,
X_VO_IMPL_METHOD => UPDATE_VIEW_BY_KEY.X_VO_IMPL_METHOD ,
X_VO_INSTANCE => UPDATE_VIEW_BY_KEY.X_VO_INSTANCE ,
X_PLSQL_API => UPDATE_VIEW_BY_KEY.X_PLSQL_API ,
X_SQL_TEXT => UPDATE_VIEW_BY_KEY.X_SQL_TEXT ,
X_CUSTOMIZATION_LEVEL => UPDATE_VIEW_BY_KEY.X_CUSTOMIZATION_LEVEL,
X_SEQUENCE => UPDATE_VIEW_BY_KEY.X_SEQUENCE ,
X_STATUS => UPDATE_VIEW_BY_KEY.X_STATUS ,
X_TITLE => UPDATE_VIEW_BY_KEY.X_TITLE ,
X_DESCRIPTION => UPDATE_VIEW_BY_KEY.X_DESCRIPTION ,
X_OWNER => UPDATE_VIEW_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_VIEW_BY_KEY.X_LAST_UPDATE_DATE );
end UPDATE_VIEW_BY_KEY;
insert into WF_WL_VIEW_ATTRIBUTES_TL (VIEW_ID,
ATTRIBUTE_NAME,
PROMPT,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select B.VIEW_ID,
B.ATTRIBUTE_NAME,
B.PROMPT,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN
from WF_WL_VIEW_ATTRIBUTES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from WF_WL_VIEW_ATTRIBUTES_TL TL
where TL.VIEW_ID = B.VIEW_ID and
TL.ATTRIBUTE_NAME = B.ATTRIBUTE_NAME and
TL.LANGUAGE = L.LANGUAGE_CODE);
insert into WF_WL_VIEWS_TL (VIEW_ID,
TITLE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
select B.VIEW_ID,
B.TITLE,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.SECURITY_GROUP_ID
from WF_WL_VIEWS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from WF_WL_VIEWS_TL T
where B.VIEW_ID = T.VIEW_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
UPDATE WF_WL_VIEWS_TL TL
SET TL.TITLE = X_TITLE,
TL.DESCRIPTION = X_DESCRIPTION,
TL.SOURCE_LANG = userenv('LANG'),
TL.LAST_UPDATE_DATE = l_ludate,
TL.LAST_UPDATED_BY = l_luby
WHERE TL.VIEW_ID = l_view_id and
userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
procedure INSERT_VIEW_ATTR_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_ATTRIBUTE_NAME in VARCHAR2,
X_ATTRIBUTE_TYPE in VARCHAR2,
X_RENDER in VARCHAR2,
X_SEQUENCE in NUMBER ,
X_CUSTOMIZATION_LEVEL in VARCHAR2,
X_PROMPT in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2)
is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
select ROWID from WF_WL_VIEW_ATTRIBUTES
where VIEW_ID = l_view_id and
ATTRIBUTE_NAME = X_ATTRIBUTE_NAME;
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
insert into WF_WL_VIEW_ATTRIBUTES (VIEW_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_TYPE ,
RENDER ,
SEQUENCE ,
CUSTOMIZATION_LEVEL,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (l_view_id ,
X_ATTRIBUTE_NAME ,
X_ATTRIBUTE_TYPE ,
X_RENDER ,
X_SEQUENCE ,
X_CUSTOMIZATION_LEVEL,
l_ludate,
l_luby,
l_ludate,
l_luby,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
insert into WF_WL_VIEW_ATTRIBUTES_TL (VIEW_ID,
ATTRIBUTE_NAME,
PROMPT,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select l_view_id,
X_ATTRIBUTE_NAME,
X_PROMPT,
X_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG'),
l_ludate,
l_luby,
l_ludate,
l_luby,
0
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WF_WL_VIEW_ATTRIBUTES_TL TL
where TL.VIEW_ID = l_view_id and
TL.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME and
TL.LANGUAGE = L.LANGUAGE_CODE);
raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_ATTR_BY_KEY unable to create attribute '||X_ATTRIBUTE_NAME||' for view '||X_VIEW_KEY);
end INSERT_VIEW_ATTR_BY_KEY;
procedure UPDATE_VIEW_ATTR_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_ATTRIBUTE_NAME in VARCHAR2,
X_ATTRIBUTE_TYPE in VARCHAR2,
X_RENDER in VARCHAR2,
X_SEQUENCE in NUMBER ,
X_CUSTOMIZATION_LEVEL in VARCHAR2,
X_PROMPT in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2)
is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
update WF_WL_VIEW_ATTRIBUTES B
set B.ATTRIBUTE_TYPE = X_ATTRIBUTE_TYPE ,
B.RENDER = X_RENDER ,
B.SEQUENCE = X_SEQUENCE ,
B.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
B.LAST_UPDATE_DATE = l_ludate,
B.LAST_UPDATED_BY = l_luby,
B.LAST_UPDATE_LOGIN = 0
where B.VIEW_ID = l_view_id and
B.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME;
INSERT_VIEW_ATTR_BY_KEY (X_VIEW_KEY ,
X_ATTRIBUTE_NAME ,
X_ATTRIBUTE_TYPE ,
X_RENDER ,
X_SEQUENCE ,
X_CUSTOMIZATION_LEVEL,
X_PROMPT ,
X_DESCRIPTION ,
X_OWNER ,
X_LAST_UPDATE_DATE);
--The record exists, thus update its translations
TRANSLATE_VIEW_ATTR_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_ATTR_BY_KEY.X_VIEW_KEY ,
X_ATTRIBUTE_NAME => UPDATE_VIEW_ATTR_BY_KEY.X_ATTRIBUTE_NAME ,
X_PROMPT => UPDATE_VIEW_ATTR_BY_KEY.X_PROMPT ,
X_DESCRIPTION => UPDATE_VIEW_ATTR_BY_KEY.X_DESCRIPTION ,
X_OWNER => UPDATE_VIEW_ATTR_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_VIEW_ATTR_BY_KEY.X_LAST_UPDATE_DATE);
end UPDATE_VIEW_ATTR_BY_KEY;
X_LAST_UPDATE_DATE in VARCHAR2)
is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
UPDATE WF_WL_VIEW_ATTRIBUTES_TL TL
SET TL.PROMPT = X_PROMPT,
TL.DESCRIPTION = X_DESCRIPTION,
TL.SOURCE_LANG = userenv('LANG'),
TL.LAST_UPDATE_DATE = l_ludate,
TL.LAST_UPDATED_BY = l_luby,
TL.LAST_UPDATE_LOGIN = 0
WHERE TL.VIEW_ID = l_view_id and
TL.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME and
userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
procedure INSERT_VIEW_PARAM_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_PARAMETER_NAME in VARCHAR2,
X_PARAMETER_SEQ in NUMBER ,
X_PARAMETER_TYPE in VARCHAR2,
X_PARAMETER_VALUE in VARCHAR2,
X_OWNER in VARCHAR2,
X_LAST_UPDATE_DATE in VARCHAR2) is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
select ROWID from WF_WL_VIEW_PARAMS
where VIEW_ID = l_view_id and
PARAMETER_NAME = X_PARAMETER_NAME;
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
insert into WF_WL_VIEW_PARAMS (VIEW_ID ,
PARAMETER_NAME,
PARAMETER_SEQ ,
PARAMETER_TYPE,
PARAMETER_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (l_view_id ,
X_PARAMETER_NAME ,
X_PARAMETER_SEQ ,
X_PARAMETER_TYPE ,
X_PARAMETER_VALUE,
l_ludate,
l_luby,
l_ludate,
l_luby,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_PARAM_BY_KEY unable to create parameter '||X_PARAMETER_NAME||' for view '||X_VIEW_KEY);
end INSERT_VIEW_PARAM_BY_KEY;
procedure UPDATE_VIEW_PARAM_BY_KEY (X_VIEW_KEY in VARCHAR2,
X_PARAMETER_NAME in VARCHAR2,
X_PARAMETER_SEQ in NUMBER ,
X_PARAMETER_TYPE in VARCHAR2,
X_PARAMETER_VALUE in VARCHAR2,
X_OWNER in VARCHAR2,
X_LAST_UPDATE_DATE in VARCHAR2) is
l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
update WF_WL_VIEW_PARAMS B
set B.PARAMETER_SEQ = X_PARAMETER_SEQ ,
B.PARAMETER_TYPE = X_PARAMETER_TYPE ,
B.PARAMETER_VALUE = X_PARAMETER_VALUE,
B.LAST_UPDATE_DATE = l_ludate,
B.LAST_UPDATED_BY = l_luby,
B.LAST_UPDATE_LOGIN = 0
where VIEW_ID = l_view_id and
PARAMETER_NAME= X_PARAMETER_NAME;
INSERT_VIEW_PARAM_BY_KEY (X_VIEW_KEY ,
X_PARAMETER_NAME ,
X_PARAMETER_SEQ ,
X_PARAMETER_TYPE ,
X_PARAMETER_VALUE,
X_OWNER,
X_LAST_UPDATE_DATE);
end UPDATE_VIEW_PARAM_BY_KEY;
procedure INSERT_REGION_VIEW_BY_KEY (X_REGION_KEY in VARCHAR2,
X_VIEW_KEY in VARCHAR2,
X_OWNER in VARCHAR2,
X_LAST_UPDATE_DATE in VARCHAR2) is
l_region_id number := WF_WL_REGIONS_PKG.FIND_REGION_ID(X_REGION_KEY);
l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
insert into WF_WL_REGION_VIEWS (REGION_ID,
VIEW_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (l_region_id,
l_view_id,
l_ludate,
l_luby,
l_ludate,
l_luby,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_REGION_VIEW_BY_KEY unable to create region view '||X_REGION_KEY||'/'||X_VIEW_KEY);
end INSERT_REGION_VIEW_BY_KEY;