[Home] [Help]
PACKAGE BODY: APPS.JTF_EVT_HANDLERS_PKG
Source
1 package body JTF_EVT_HANDLERS_PKG as
2 /* $Header: JTFEVTHB.pls 115.1 2002/02/14 05:44:04 appldev ship $ */
3 procedure INSERT_ROW (
4 X_JTF_EVT_HANDLERS_ID in NUMBER,
5 X_JTF_EVT_TYPES_ID in NUMBER,
6 X_JTF_EVT_HANDLERS_NAME in VARCHAR2,
7 X_JTF_EVT_HANDLERS_MTD_NAME in VARCHAR2,
8 X_JTF_EVT_HANDLERS_SYNC_FLAG in VARCHAR2,
9 X_SECURITY_GROUP_ID in NUMBER,
10 X_JTF_EVT_HANDLERS_DESC in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from JTF_EVT_HANDLERS_B
18 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID
19 ;
20 begin
21 insert into JTF_EVT_HANDLERS_B (
22 JTF_EVT_HANDLERS_ID,
23 JTF_EVT_TYPES_ID,
24 JTF_EVT_HANDLERS_NAME,
25 JTF_EVT_HANDLERS_MTD_NAME,
26 JTF_EVT_HANDLERS_SYNC_FLAG,
27 SECURITY_GROUP_ID,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_JTF_EVT_HANDLERS_ID,
35 X_JTF_EVT_TYPES_ID,
36 X_JTF_EVT_HANDLERS_NAME,
37 X_JTF_EVT_HANDLERS_MTD_NAME,
38 X_JTF_EVT_HANDLERS_SYNC_FLAG,
39 X_SECURITY_GROUP_ID,
40 X_CREATION_DATE,
41 X_CREATED_BY,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_LOGIN
45 );
46
47 insert into JTF_EVT_HANDLERS_TL (
48 SECURITY_GROUP_ID,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_LOGIN,
52 JTF_EVT_HANDLERS_ID,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 JTF_EVT_HANDLERS_DESC,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_SECURITY_GROUP_ID,
60 X_CREATION_DATE,
61 X_CREATED_BY,
62 X_LAST_UPDATE_LOGIN,
63 X_JTF_EVT_HANDLERS_ID,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATED_BY,
66 X_JTF_EVT_HANDLERS_DESC,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from JTF_EVT_HANDLERS_TL T
74 where T.JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID
75 and T.LANGUAGE = L.LANGUAGE_CODE);
76
77 open c;
78 --fetch c into X_ROWID;
79 if (c%notfound) then
80 close c;
81 raise no_data_found;
82 end if;
83 close c;
84
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 X_JTF_EVT_HANDLERS_ID in NUMBER,
89 X_JTF_EVT_TYPES_ID in NUMBER,
90 X_JTF_EVT_HANDLERS_NAME in VARCHAR2,
91 X_JTF_EVT_HANDLERS_MTD_NAME in VARCHAR2,
92 X_JTF_EVT_HANDLERS_SYNC_FLAG in VARCHAR2,
93 X_SECURITY_GROUP_ID in NUMBER,
94 X_JTF_EVT_HANDLERS_DESC in VARCHAR2
95 ) is
96 cursor c is select
97 JTF_EVT_TYPES_ID,
98 JTF_EVT_HANDLERS_NAME,
99 JTF_EVT_HANDLERS_MTD_NAME,
100 JTF_EVT_HANDLERS_SYNC_FLAG,
101 SECURITY_GROUP_ID
102 from JTF_EVT_HANDLERS_B
103 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID
104 for update of JTF_EVT_HANDLERS_ID nowait;
105 recinfo c%rowtype;
106
107 cursor c1 is select
108 JTF_EVT_HANDLERS_DESC,
109 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110 from JTF_EVT_HANDLERS_TL
111 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of JTF_EVT_HANDLERS_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( (recinfo.JTF_EVT_TYPES_ID = X_JTF_EVT_TYPES_ID)
124 AND (recinfo.JTF_EVT_HANDLERS_NAME = X_JTF_EVT_HANDLERS_NAME)
125 AND ((recinfo.JTF_EVT_HANDLERS_MTD_NAME = X_JTF_EVT_HANDLERS_MTD_NAME)
126 OR ((recinfo.JTF_EVT_HANDLERS_MTD_NAME is null) AND (X_JTF_EVT_HANDLERS_MTD_NAME is null)))
127 AND ((recinfo.JTF_EVT_HANDLERS_SYNC_FLAG = X_JTF_EVT_HANDLERS_SYNC_FLAG)
128 OR ((recinfo.JTF_EVT_HANDLERS_SYNC_FLAG is null) AND (X_JTF_EVT_HANDLERS_SYNC_FLAG is null)))
129 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
130 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137
138 for tlinfo in c1 loop
139 if (tlinfo.BASELANG = 'Y') then
140 if ( ((tlinfo.JTF_EVT_HANDLERS_DESC = X_JTF_EVT_HANDLERS_DESC)
141 OR ((tlinfo.JTF_EVT_HANDLERS_DESC is null) AND (X_JTF_EVT_HANDLERS_DESC is null)))
142 ) then
143 null;
144 else
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 end if;
148 end if;
149 end loop;
150 return;
151 end LOCK_ROW;
152
153 procedure UPDATE_ROW (
154 X_JTF_EVT_HANDLERS_ID in NUMBER,
155 X_JTF_EVT_TYPES_ID in NUMBER,
156 X_JTF_EVT_HANDLERS_NAME in VARCHAR2,
157 X_JTF_EVT_HANDLERS_MTD_NAME in VARCHAR2,
158 X_JTF_EVT_HANDLERS_SYNC_FLAG in VARCHAR2,
159 X_SECURITY_GROUP_ID in NUMBER,
160 X_JTF_EVT_HANDLERS_DESC in VARCHAR2,
161 X_LAST_UPDATE_DATE in DATE,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER
164 ) is
165 begin
166 update JTF_EVT_HANDLERS_B set
167 JTF_EVT_TYPES_ID = X_JTF_EVT_TYPES_ID,
168 JTF_EVT_HANDLERS_NAME = X_JTF_EVT_HANDLERS_NAME,
169 JTF_EVT_HANDLERS_MTD_NAME = X_JTF_EVT_HANDLERS_MTD_NAME,
170 JTF_EVT_HANDLERS_SYNC_FLAG = X_JTF_EVT_HANDLERS_SYNC_FLAG,
171 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
175 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID;
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180
181 update JTF_EVT_HANDLERS_TL set
182 JTF_EVT_HANDLERS_DESC = X_JTF_EVT_HANDLERS_DESC,
183 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
184 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
186 SOURCE_LANG = userenv('LANG')
187 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID
188 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end UPDATE_ROW;
194
195 procedure DELETE_ROW (
196 X_JTF_EVT_HANDLERS_ID in NUMBER
197 ) is
198 begin
199 delete from JTF_EVT_HANDLERS_TL
200 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID;
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205
206 delete from JTF_EVT_HANDLERS_B
207 where JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212 end DELETE_ROW;
213
214 procedure ADD_LANGUAGE
215 is
216 begin
217 delete from JTF_EVT_HANDLERS_TL T
218 where not exists
219 (select NULL
220 from JTF_EVT_HANDLERS_B B
221 where B.JTF_EVT_HANDLERS_ID = T.JTF_EVT_HANDLERS_ID
222 );
223
224 update JTF_EVT_HANDLERS_TL T set (
225 JTF_EVT_HANDLERS_DESC
226 ) = (select
227 B.JTF_EVT_HANDLERS_DESC
228 from JTF_EVT_HANDLERS_TL B
229 where B.JTF_EVT_HANDLERS_ID = T.JTF_EVT_HANDLERS_ID
230 and B.LANGUAGE = T.SOURCE_LANG)
231 where (
232 T.JTF_EVT_HANDLERS_ID,
233 T.LANGUAGE
234 ) in (select
235 SUBT.JTF_EVT_HANDLERS_ID,
236 SUBT.LANGUAGE
237 from JTF_EVT_HANDLERS_TL SUBB, JTF_EVT_HANDLERS_TL SUBT
238 where SUBB.JTF_EVT_HANDLERS_ID = SUBT.JTF_EVT_HANDLERS_ID
239 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
240 and (SUBB.JTF_EVT_HANDLERS_DESC <> SUBT.JTF_EVT_HANDLERS_DESC
241 or (SUBB.JTF_EVT_HANDLERS_DESC is null and SUBT.JTF_EVT_HANDLERS_DESC is not null)
242 or (SUBB.JTF_EVT_HANDLERS_DESC is not null and SUBT.JTF_EVT_HANDLERS_DESC is null)
243 ));
244
245 insert into JTF_EVT_HANDLERS_TL (
246 SECURITY_GROUP_ID,
247 CREATION_DATE,
248 CREATED_BY,
249 LAST_UPDATE_LOGIN,
250 JTF_EVT_HANDLERS_ID,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY,
253 JTF_EVT_HANDLERS_DESC,
254 LANGUAGE,
255 SOURCE_LANG
256 ) select
257 B.SECURITY_GROUP_ID,
258 B.CREATION_DATE,
259 B.CREATED_BY,
260 B.LAST_UPDATE_LOGIN,
261 B.JTF_EVT_HANDLERS_ID,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATED_BY,
264 B.JTF_EVT_HANDLERS_DESC,
265 L.LANGUAGE_CODE,
266 B.SOURCE_LANG
267 from JTF_EVT_HANDLERS_TL B, FND_LANGUAGES L
268 where L.INSTALLED_FLAG in ('I', 'B')
269 and B.LANGUAGE = userenv('LANG')
270 and not exists
271 (select NULL
272 from JTF_EVT_HANDLERS_TL T
273 where T.JTF_EVT_HANDLERS_ID = B.JTF_EVT_HANDLERS_ID
274 and T.LANGUAGE = L.LANGUAGE_CODE);
275 end ADD_LANGUAGE;
276
277 procedure LOAD_ROW (
278 X_JTF_EVT_HANDLERS_ID in NUMBER, -- key fields
279 X_SECURITY_GROUP_ID in NUMBER,
280 X_JTF_EVT_TYPES_ID in NUMBER, -- data fields
281 X_JTF_EVT_HANDLERS_NAME in VARCHAR2,
282 X_JTF_EVT_HANDLERS_MTD_NAME in VARCHAR2,
283 X_JTF_EVT_HANDLERS_SYNC_FLAG in VARCHAR2,
284 X_JTF_EVT_HANDLERS_DESC in VARCHAR2,
285 X_OWNER in VARCHAR2 -- owner fields
286 ) is
287
288 l_rowid VARCHAR2(64);
289 l_user_id NUMBER := 0;
290
291 begin
292 if(x_owner = 'SEED') then
293 l_user_id := 1;
294 end if;
295
296 -- Update row if present
297 JTF_EVT_HANDLERS_PKG.UPDATE_ROW (
298 X_JTF_EVT_HANDLERS_ID => X_JTF_EVT_HANDLERS_ID,
299 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
300 X_JTF_EVT_TYPES_ID => X_JTF_EVT_TYPES_ID,
301 X_JTF_EVT_HANDLERS_NAME => X_JTF_EVT_HANDLERS_NAME,
302 X_JTF_EVT_HANDLERS_MTD_NAME => X_JTF_EVT_HANDLERS_MTD_NAME,
303 X_JTF_EVT_HANDLERS_SYNC_FLAG => X_JTF_EVT_HANDLERS_SYNC_FLAG,
304 X_JTF_EVT_HANDLERS_DESC => X_JTF_EVT_HANDLERS_DESC,
305 X_LAST_UPDATE_DATE => sysdate,
306 X_LAST_UPDATED_BY => l_user_id,
307 X_LAST_UPDATE_LOGIN => 0 );
308 exception
309 when NO_DATA_FOUND then
310 -- Insert a row
311 JTF_EVT_HANDLERS_PKG.INSERT_ROW (
312 X_JTF_EVT_HANDLERS_ID => X_JTF_EVT_HANDLERS_ID,
313 X_JTF_EVT_TYPES_ID => X_JTF_EVT_TYPES_ID,
314 X_JTF_EVT_HANDLERS_NAME => X_JTF_EVT_HANDLERS_NAME,
315 X_JTF_EVT_HANDLERS_MTD_NAME => X_JTF_EVT_HANDLERS_MTD_NAME,
316 X_JTF_EVT_HANDLERS_SYNC_FLAG => X_JTF_EVT_HANDLERS_SYNC_FLAG,
317 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
318 X_JTF_EVT_HANDLERS_DESC => X_JTF_EVT_HANDLERS_DESC,
319 X_CREATION_DATE => sysdate,
320 X_CREATED_BY => l_user_id,
321 X_LAST_UPDATE_DATE => sysdate,
322 X_LAST_UPDATED_BY => l_user_id,
323 X_LAST_UPDATE_LOGIN => 0 );
324
325 end LOAD_ROW;
326
327
328
329 procedure TRANSLATE_ROW (
330 X_JTF_EVT_HANDLERS_ID in NUMBER, -- key field
331 X_JTF_EVT_HANDLERS_DESC in VARCHAR2, -- translated field
332 X_OWNER in VARCHAR2 -- owner fields
333 ) is
334
335 begin
336 update JTF_EVT_HANDLERS_TL set
337 JTF_EVT_HANDLERS_DESC = x_JTF_EVT_HANDLERS_DESC,
338 LAST_UPDATE_DATE = sysdate,
339 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
340 LAST_UPDATE_LOGIN = 0,
341 SOURCE_LANG = userenv('LANG')
342 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
343 and JTF_EVT_HANDLERS_ID = X_JTF_EVT_HANDLERS_ID;
344
345 end TRANSLATE_ROW;
346
347 end JTF_EVT_HANDLERS_PKG;