[Home] [Help]
PACKAGE BODY: APPS.WF_WL_LISTS_PKG
Source
1 package body WF_WL_LISTS_PKG as
2 /* $Header: wfwllstb.pls 120.0.12020000.4 2013/05/15 21:26:41 alsosa noship $ */
3 function FIND_LIST_ID(p_list_key in VARCHAR2) return number is
4 l_list_id number;
5 begin
6 select LIST_ID into l_list_id
7 from WF_WL_LISTS
8 where LIST_KEY=p_list_key;
9 return l_list_id;
10 exception
11 when others then
12 return null;
13 end FIND_LIST_ID;
14
15 function FIND_USER_ID(p_user_name in VARCHAR2) return number is
16 l_user_id number;
17 begin
18 if p_user_name='PUBLIC' then
19 l_user_id := -1;
20 else
21 select USER_ID into l_user_id
22 from FND_USER
23 where USER_NAME=p_user_name;
24 end if;
25 return l_user_id;
26 exception
27 when others then
28 raise_application_error (-20000, 'WF_WL_LISTS_PKG.FIND_USER_ID: unable to find user '||p_user_name);
29 end FIND_USER_ID;
30
31 procedure INSERT_LIST_BY_KEY (X_LIST_KEY in VARCHAR2,
32 X_USER_NAME in VARCHAR2,
33 X_APPLICATION_ID in NUMBER,
34 X_STATUS in VARCHAR2,
35 X_FILTER_CONDITION in VARCHAR2,
36 X_ADDITIONAL_STATUS_FILTERS in VARCHAR2,
37 X_ICON_FILE_ID in NUMBER,
38 X_LIST_NAME in VARCHAR2,
39 X_DESCRIPTION in VARCHAR2,
40 X_OWNER IN VARCHAR2,
41 X_LAST_UPDATE_DATE IN VARCHAR2)
42 is
43 cursor c_newrow is select ROWID from WF_WL_LISTS
44 where LIST_KEY = X_LIST_KEY;
45 X_ROWID VARCHAR2(30);
46 l_user_id number := FIND_USER_ID(X_USER_NAME);
47 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
48 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
49 begin
50 insert into WF_WL_LISTS (LIST_ID,
51 LIST_KEY,
52 USER_ID,
53 APPLICATION_ID,
54 STATUS,
55 FILTER_CONDITION,
56 ADDITIONAL_STATUS_FILTERS,
57 ICON_FILE_ID,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 SECURITY_GROUP_ID)
64 values (WF_WL_LISTS_S.NEXTVAL,
65 X_LIST_KEY,
66 l_user_id,
67 X_APPLICATION_ID,
68 X_STATUS,
69 X_FILTER_CONDITION,
70 X_ADDITIONAL_STATUS_FILTERS,
71 X_ICON_FILE_ID,
72 l_ludate, --CREATION_DATE,
73 l_luby, --CREATED_BY,
74 l_ludate, --LAST_UPDATE_DATE,
75 l_luby, --LAST_UPDATED_BY,
76 0, --LAST_UPDATE_LOGIN,
77 FND_GLOBAL.SECURITY_GROUP_ID);
78 insert into WF_WL_LISTS_TL (LIST_ID,
79 LIST_NAME,
80 DESCRIPTION,
81 LANGUAGE,
82 SOURCE_LANG,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_DATE,
86 LAST_UPDATED_BY,
87 LAST_UPDATE_LOGIN,
88 SECURITY_GROUP_ID)
89 select WF_WL_LISTS_S.CURRVAL,
90 X_LIST_NAME,
91 X_DESCRIPTION,
92 L.LANGUAGE_CODE,
93 userenv('LANG'),
94 l_ludate, --CREATION_DATE,
95 l_luby, --CREATED_BY,
96 l_ludate, --LAST_UPDATE_DATE,
97 l_luby, --LAST_UPDATED_BY,
98 0, --LAST_UPDATE_LOGIN,
99 FND_GLOBAL.SECURITY_GROUP_ID
100 from FND_LANGUAGES L
101 where L.INSTALLED_FLAG in ('I', 'B')
102 and not exists
103 (select NULL
104 from WF_WL_LISTS_TL TL, WF_WL_LISTS B
105 where B.LIST_ID = TL.LIST_ID and
106 B.LIST_KEY = X_LIST_KEY and
107 TL.LANGUAGE = L.LANGUAGE_CODE);
108
109 open c_newrow;
110 fetch c_newrow into X_ROWID;
111 if (c_newrow%notfound) then
112 close c_newrow;
113 raise_application_error (-20000, 'WF_WL_LISTS_PKG.INSERT_LIST_BY_KEY: unable to create list '||X_LIST_KEY);
114 end if;
115 close c_newrow;
116 end INSERT_LIST_BY_KEY;
117
118
119 procedure UPDATE_LIST_BY_KEY (X_LIST_KEY in VARCHAR2,
120 X_USER_NAME in VARCHAR2,
121 X_APPLICATION_ID in NUMBER,
122 X_STATUS in VARCHAR2,
123 X_FILTER_CONDITION in VARCHAR2,
124 X_ADDITIONAL_STATUS_FILTERS in VARCHAR2,
125 X_ICON_FILE_ID in NUMBER,
126 X_LIST_NAME in VARCHAR2,
127 X_DESCRIPTION in VARCHAR2,
128 X_OWNER IN VARCHAR2,
129 X_LAST_UPDATE_DATE IN VARCHAR2) is
130 l_list_id number := FIND_LIST_ID(X_LIST_KEY);
131 l_user_id number := FIND_USER_ID(X_USER_NAME);
132 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
133 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
134 begin
135 update WF_WL_LISTS B
136 set B.USER_ID = l_user_id,
137 B.APPLICATION_ID = X_APPLICATION_ID,
138 B.STATUS = X_STATUS,
139 B.FILTER_CONDITION = X_FILTER_CONDITION,
140 B.ADDITIONAL_STATUS_FILTERS = X_ADDITIONAL_STATUS_FILTERS,
141 B.ICON_FILE_ID = X_ICON_FILE_ID,
142 B.LAST_UPDATE_DATE = l_ludate,
143 B.LAST_UPDATED_BY = l_luby,
144 B.LAST_UPDATE_LOGIN = 0
145 where B.LIST_ID = l_list_id;
146 -- dbms_output.put_line('UPDATE_LIST_BY_KEY->X_LIST_KEY: '||X_LIST_KEY);
147 -- dbms_output.put_line('UPDATE_LIST_BY_KEY->l_list_id: '||l_list_id);
148 if (sql%rowcount=0) then
149 -- dbms_output.put_line('UPDATE_LIST_BY_KEY->list not found. Creating it...');
150 INSERT_LIST_BY_KEY (X_LIST_KEY => UPDATE_LIST_BY_KEY.X_LIST_KEY ,
151 X_USER_NAME => UPDATE_LIST_BY_KEY.X_USER_NAME ,
152 X_APPLICATION_ID => UPDATE_LIST_BY_KEY.X_APPLICATION_ID ,
153 X_STATUS => UPDATE_LIST_BY_KEY.X_STATUS ,
154 X_FILTER_CONDITION => UPDATE_LIST_BY_KEY.X_FILTER_CONDITION ,
155 X_ADDITIONAL_STATUS_FILTERS => UPDATE_LIST_BY_KEY.X_ADDITIONAL_STATUS_FILTERS,
156 X_ICON_FILE_ID => UPDATE_LIST_BY_KEY.X_ICON_FILE_ID ,
157 X_LIST_NAME => UPDATE_LIST_BY_KEY.X_LIST_NAME ,
158 X_DESCRIPTION => UPDATE_LIST_BY_KEY.X_DESCRIPTION ,
159 X_OWNER => UPDATE_LIST_BY_KEY.X_OWNER ,
160 X_LAST_UPDATE_DATE => UPDATE_LIST_BY_KEY.X_LAST_UPDATE_DATE );
161 else
162 -- dbms_output.put_line('UPDATE_LIST_BY_KEY->list found. Updating translation...');
163 TRANSLATE_LIST_BY_KEY (X_LIST_KEY => UPDATE_LIST_BY_KEY.X_LIST_KEY ,
164 X_LIST_NAME => UPDATE_LIST_BY_KEY.X_LIST_NAME ,
165 X_DESCRIPTION => UPDATE_LIST_BY_KEY.X_DESCRIPTION,
166 X_OWNER => UPDATE_LIST_BY_KEY.X_OWNER ,
167 X_LAST_UPDATE_DATE => UPDATE_LIST_BY_KEY.X_LAST_UPDATE_DATE);
168 end if;
169 end UPDATE_LIST_BY_KEY;
170
171 procedure ADD_LANGUAGE is
172 begin
173 insert into WF_WL_LISTS_TL (LIST_ID,
174 LIST_NAME,
175 DESCRIPTION,
176 LANGUAGE,
177 SOURCE_LANG,
178 CREATION_DATE,
179 CREATED_BY,
180 LAST_UPDATE_DATE,
181 LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN,
183 SECURITY_GROUP_ID)
184 select B.LIST_ID,
185 B.LIST_NAME,
186 B.DESCRIPTION,
187 L.LANGUAGE_CODE,
188 B.SOURCE_LANG,
189 B.CREATION_DATE,
190 B.CREATED_BY,
191 B.LAST_UPDATE_DATE,
192 B.LAST_UPDATED_BY,
193 B.LAST_UPDATE_LOGIN,
194 B.SECURITY_GROUP_ID
195 from WF_WL_LISTS_TL B, FND_LANGUAGES L
196 where L.INSTALLED_FLAG in ('I', 'B')
197 and B.LANGUAGE=userenv('LANG')
198 and not exists
199 (select NULL
200 from WF_WL_LISTS_TL TL
201 where B.LIST_ID = TL.LIST_ID and
202 TL.LANGUAGE = L.LANGUAGE_CODE);
203 exception
204 when others then
205 raise_application_error (-20000, 'WF_WL_LISTS_PKG.ADD_LANGUAGE: unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
206 end ADD_LANGUAGE;
207
208 procedure TRANSLATE_LIST_BY_KEY (X_LIST_KEY in VARCHAR2,
209 X_LIST_NAME in VARCHAR2,
210 X_DESCRIPTION in VARCHAR2,
211 X_OWNER IN VARCHAR2,
212 X_LAST_UPDATE_DATE IN VARCHAR2) is
213 l_list_id number := FIND_LIST_ID(X_LIST_KEY);
214 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
215 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
216 BEGIN
217 UPDATE WF_WL_LISTS_TL TL
218 SET TL.LIST_NAME = X_LIST_NAME,
219 TL.DESCRIPTION = X_DESCRIPTION,
220 TL.SOURCE_LANG = userenv('LANG'),
221 TL.LAST_UPDATE_DATE = l_ludate,
222 TL.LAST_UPDATED_BY = l_luby,
223 TL.LAST_UPDATE_LOGIN = 0
224 WHERE LIST_ID = l_list_id and
225 userenv('LANG') in (LANGUAGE,SOURCE_LANG);
226 IF (sql%rowcount=0) THEN
227 raise_application_error (-20000, 'WF_WL_LISTS_PKG.TRANSLATE_LIST_BY_KEY: unable to translate list '||X_LIST_KEY||'. Ensure base language is uploaded first');
228 END IF;
229 end TRANSLATE_LIST_BY_KEY;
230
231 procedure INSERT_FAVORITE_BY_KEY (X_LIST_KEY in VARCHAR2,
232 X_USER_NAME in VARCHAR2,
233 X_SEQUENCE in NUMBER,
234 X_OWNER IN VARCHAR2,
235 X_LAST_UPDATE_DATE IN VARCHAR2) is
236 l_list_id number := FIND_LIST_ID(X_LIST_KEY);
237 l_user_id number := FIND_USER_ID(X_USER_NAME);
238 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
239 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
240 begin
241 if l_list_id is null or l_user_id is null then
242 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);
243 end if;
244 insert into WF_WL_FAVORITE_LISTS (LIST_ID,
245 USER_ID,
246 SEQUENCE,
247 CREATION_DATE,
248 CREATED_BY,
249 LAST_UPDATE_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_LOGIN,
252 SECURITY_GROUP_ID)
253 values (l_list_id ,
254 l_user_id ,
255 X_SEQUENCE,
256 l_ludate, --CREATION_DATE,
257 l_luby, --CREATED_BY,
258 l_ludate, --LAST_UPDATE_DATE,
259 l_luby, --LAST_UPDATED_BY,
260 0, --LAST_UPDATE_LOGIN,
261 FND_GLOBAL.SECURITY_GROUP_ID);
262 exception
263 when others then
264 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);
265 end INSERT_FAVORITE_BY_KEY;
266
267 procedure UPDATE_FAVORITE_BY_KEY (X_LIST_KEY in VARCHAR2,
268 X_USER_NAME in VARCHAR2,
269 X_SEQUENCE in NUMBER,
270 X_OWNER IN VARCHAR2,
271 X_LAST_UPDATE_DATE IN VARCHAR2) is
272 l_list_id number := FIND_LIST_ID(X_LIST_KEY);
273 l_user_id number := FIND_USER_ID(X_USER_NAME);
274 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
275 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
276 begin
277 update WF_WL_FAVORITE_LISTS B
281 B.LAST_UPDATE_LOGIN = 0
278 set B.SEQUENCE = X_SEQUENCE,
279 B.LAST_UPDATE_DATE = l_ludate,
280 B.LAST_UPDATED_BY = l_luby,
282 where B.LIST_ID = l_list_id and
283 B.USER_ID = l_user_id;
284 if (sql%rowcount=0) then
285 INSERT_FAVORITE_BY_KEY (X_LIST_KEY => UPDATE_FAVORITE_BY_KEY.X_LIST_KEY ,
286 X_USER_NAME => UPDATE_FAVORITE_BY_KEY.X_USER_NAME,
287 X_SEQUENCE => UPDATE_FAVORITE_BY_KEY.X_SEQUENCE ,
288 X_OWNER => UPDATE_FAVORITE_BY_KEY.X_OWNER ,
289 X_LAST_UPDATE_DATE => UPDATE_FAVORITE_BY_KEY.X_LAST_UPDATE_DATE);
290 end if;
291 exception
292 when others then
293 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);
294 end UPDATE_FAVORITE_BY_KEY;
295
296 end WF_WL_LISTS_PKG;