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