[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select LIST_ID into l_list_id
from WF_WL_LISTS
where LIST_KEY=p_list_key;
select USER_ID into l_user_id
from FND_USER
where USER_NAME=p_user_name;
procedure INSERT_LIST_BY_KEY (X_LIST_KEY in VARCHAR2,
X_USER_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_STATUS in VARCHAR2,
X_FILTER_CONDITION in VARCHAR2,
X_ADDITIONAL_STATUS_FILTERS in VARCHAR2,
X_ICON_FILE_ID in NUMBER,
X_LIST_NAME 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_LISTS
where LIST_KEY = X_LIST_KEY;
l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
insert into WF_WL_LISTS (LIST_ID,
LIST_KEY,
USER_ID,
APPLICATION_ID,
STATUS,
FILTER_CONDITION,
ADDITIONAL_STATUS_FILTERS,
ICON_FILE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (WF_WL_LISTS_S.NEXTVAL,
X_LIST_KEY,
l_user_id,
X_APPLICATION_ID,
X_STATUS,
X_FILTER_CONDITION,
X_ADDITIONAL_STATUS_FILTERS,
X_ICON_FILE_ID,
l_ludate, --CREATION_DATE,
l_luby, --CREATED_BY,
l_ludate, --LAST_UPDATE_DATE,
l_luby, --LAST_UPDATED_BY,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
insert into WF_WL_LISTS_TL (LIST_ID,
LIST_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
select WF_WL_LISTS_S.CURRVAL,
X_LIST_NAME,
X_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG'),
l_ludate, --CREATION_DATE,
l_luby, --CREATED_BY,
l_ludate, --LAST_UPDATE_DATE,
l_luby, --LAST_UPDATED_BY,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WF_WL_LISTS_TL TL, WF_WL_LISTS B
where B.LIST_ID = TL.LIST_ID and
B.LIST_KEY = X_LIST_KEY and
TL.LANGUAGE = L.LANGUAGE_CODE);
raise_application_error (-20000, 'WF_WL_LISTS_PKG.INSERT_LIST_BY_KEY: unable to create list '||X_LIST_KEY);
end INSERT_LIST_BY_KEY;
procedure UPDATE_LIST_BY_KEY (X_LIST_KEY in VARCHAR2,
X_USER_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_STATUS in VARCHAR2,
X_FILTER_CONDITION in VARCHAR2,
X_ADDITIONAL_STATUS_FILTERS in VARCHAR2,
X_ICON_FILE_ID in NUMBER,
X_LIST_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_list_id number := FIND_LIST_ID(X_LIST_KEY);
l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
update WF_WL_LISTS B
set B.USER_ID = l_user_id,
B.APPLICATION_ID = X_APPLICATION_ID,
B.STATUS = X_STATUS,
B.FILTER_CONDITION = X_FILTER_CONDITION,
B.ADDITIONAL_STATUS_FILTERS = X_ADDITIONAL_STATUS_FILTERS,
B.ICON_FILE_ID = X_ICON_FILE_ID,
B.LAST_UPDATE_DATE = l_ludate,
B.LAST_UPDATED_BY = l_luby,
B.LAST_UPDATE_LOGIN = 0
where B.LIST_ID = l_list_id;
INSERT_LIST_BY_KEY (X_LIST_KEY => UPDATE_LIST_BY_KEY.X_LIST_KEY ,
X_USER_NAME => UPDATE_LIST_BY_KEY.X_USER_NAME ,
X_APPLICATION_ID => UPDATE_LIST_BY_KEY.X_APPLICATION_ID ,
X_STATUS => UPDATE_LIST_BY_KEY.X_STATUS ,
X_FILTER_CONDITION => UPDATE_LIST_BY_KEY.X_FILTER_CONDITION ,
X_ADDITIONAL_STATUS_FILTERS => UPDATE_LIST_BY_KEY.X_ADDITIONAL_STATUS_FILTERS,
X_ICON_FILE_ID => UPDATE_LIST_BY_KEY.X_ICON_FILE_ID ,
X_LIST_NAME => UPDATE_LIST_BY_KEY.X_LIST_NAME ,
X_DESCRIPTION => UPDATE_LIST_BY_KEY.X_DESCRIPTION ,
X_OWNER => UPDATE_LIST_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_LIST_BY_KEY.X_LAST_UPDATE_DATE );
TRANSLATE_LIST_BY_KEY (X_LIST_KEY => UPDATE_LIST_BY_KEY.X_LIST_KEY ,
X_LIST_NAME => UPDATE_LIST_BY_KEY.X_LIST_NAME ,
X_DESCRIPTION => UPDATE_LIST_BY_KEY.X_DESCRIPTION,
X_OWNER => UPDATE_LIST_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_LIST_BY_KEY.X_LAST_UPDATE_DATE);
end UPDATE_LIST_BY_KEY;
insert into WF_WL_LISTS_TL (LIST_ID,
LIST_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
select B.LIST_ID,
B.LIST_NAME,
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_LISTS_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_LISTS_TL TL
where B.LIST_ID = TL.LIST_ID and
TL.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_list_id number := FIND_LIST_ID(X_LIST_KEY);
l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
UPDATE WF_WL_LISTS_TL TL
SET TL.LIST_NAME = X_LIST_NAME,
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 LIST_ID = l_list_id and
userenv('LANG') in (LANGUAGE,SOURCE_LANG);
procedure INSERT_FAVORITE_BY_KEY (X_LIST_KEY in VARCHAR2,
X_USER_NAME in VARCHAR2,
X_SEQUENCE in NUMBER,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_list_id number := FIND_LIST_ID(X_LIST_KEY);
l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
raise_application_error (-20000, 'WF_WL_LISTS_PKG.INSERT_FAVORITE_BY_KEY: null value(s) when creating favorite list '||X_LIST_KEY||' for user '||X_USER_NAME);
insert into WF_WL_FAVORITE_LISTS (LIST_ID,
USER_ID,
SEQUENCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID)
values (l_list_id ,
l_user_id ,
X_SEQUENCE,
l_ludate, --CREATION_DATE,
l_luby, --CREATED_BY,
l_ludate, --LAST_UPDATE_DATE,
l_luby, --LAST_UPDATED_BY,
0, --LAST_UPDATE_LOGIN,
FND_GLOBAL.SECURITY_GROUP_ID);
raise_application_error (-20000, 'WF_WL_LISTS_PKG.INSERT_FAVORITE_BY_KEY: unable to create favorite list '||X_LIST_KEY||' for user '||X_USER_NAME||'. '||SQLCODE);
end INSERT_FAVORITE_BY_KEY;
procedure UPDATE_FAVORITE_BY_KEY (X_LIST_KEY in VARCHAR2,
X_USER_NAME in VARCHAR2,
X_SEQUENCE in NUMBER,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2) is
l_list_id number := FIND_LIST_ID(X_LIST_KEY);
l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
update WF_WL_FAVORITE_LISTS B
set B.SEQUENCE = X_SEQUENCE,
B.LAST_UPDATE_DATE = l_ludate,
B.LAST_UPDATED_BY = l_luby,
B.LAST_UPDATE_LOGIN = 0
where B.LIST_ID = l_list_id and
B.USER_ID = l_user_id;
INSERT_FAVORITE_BY_KEY (X_LIST_KEY => UPDATE_FAVORITE_BY_KEY.X_LIST_KEY ,
X_USER_NAME => UPDATE_FAVORITE_BY_KEY.X_USER_NAME,
X_SEQUENCE => UPDATE_FAVORITE_BY_KEY.X_SEQUENCE ,
X_OWNER => UPDATE_FAVORITE_BY_KEY.X_OWNER ,
X_LAST_UPDATE_DATE => UPDATE_FAVORITE_BY_KEY.X_LAST_UPDATE_DATE);
raise_application_error (-20000, 'WF_WL_LISTS_PKG.UPDATE_FAVORITE_BY_KEY: unable to update favorite list '||X_LIST_KEY||' for user '||X_USER_NAME||'. '||SQLCODE);
end UPDATE_FAVORITE_BY_KEY;