1 package body JTF_RS_GROUPS_AUD_PKG as
2 /* $Header: jtfrstkb.pls 120.0 2005/05/11 08:22:20 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_GROUP_AUDIT_ID in NUMBER,
6 X_GROUP_ID in NUMBER,
7 X_NEW_GROUP_NUMBER in VARCHAR2,
8 X_OLD_GROUP_NUMBER in VARCHAR2,
9 X_NEW_EMAIL_ADDRESS in VARCHAR2,
10 X_OLD_EMAIL_ADDRESS in VARCHAR2,
11 X_NEW_EXCLUSIVE_FLAG in VARCHAR2,
12 X_OLD_EXCLUSIVE_FLAG in VARCHAR2,
13 X_NEW_START_DATE_ACTIVE in DATE,
14 X_OLD_START_DATE_ACTIVE in DATE,
15 X_NEW_END_DATE_ACTIVE in DATE,
16 X_OLD_END_DATE_ACTIVE in DATE,
17 X_NEW_ACCOUNTING_CODE in VARCHAR2,
18 X_OLD_ACCOUNTING_CODE in VARCHAR2,
19 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
20 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
21 X_NEW_GROUP_NAME in VARCHAR2,
22 X_OLD_GROUP_NAME in VARCHAR2,
23 X_NEW_GROUP_DESC in VARCHAR2,
24 X_OLD_GROUP_DESC in VARCHAR2,
25 X_CREATION_DATE in DATE,
26 X_CREATED_BY in NUMBER,
27 X_LAST_UPDATE_DATE in DATE,
28 X_LAST_UPDATED_BY in NUMBER,
29 X_LAST_UPDATE_LOGIN in NUMBER
30 ) is
31 cursor C is select ROWID from JTF_RS_GROUPS_AUD_B
32 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID
33 ;
34 begin
35 insert into JTF_RS_GROUPS_AUD_B (
36 GROUP_AUDIT_ID,
37 GROUP_ID,
38 NEW_GROUP_NUMBER,
39 OLD_GROUP_NUMBER,
40 NEW_EMAIL_ADDRESS,
41 OLD_EMAIL_ADDRESS,
42 NEW_EXCLUSIVE_FLAG,
43 OLD_EXCLUSIVE_FLAG,
44 NEW_START_DATE_ACTIVE,
45 OLD_START_DATE_ACTIVE,
46 NEW_END_DATE_ACTIVE,
47 OLD_END_DATE_ACTIVE,
48 NEW_ACCOUNTING_CODE,
49 OLD_ACCOUNTING_CODE,
50 NEW_OBJECT_VERSION_NUMBER,
51 OLD_OBJECT_VERSION_NUMBER,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_LOGIN
57 ) values (
58 X_GROUP_AUDIT_ID,
59 X_GROUP_ID,
60 X_NEW_GROUP_NUMBER,
61 X_OLD_GROUP_NUMBER,
62 X_NEW_EMAIL_ADDRESS,
63 X_OLD_EMAIL_ADDRESS,
64 X_NEW_EXCLUSIVE_FLAG,
65 X_OLD_EXCLUSIVE_FLAG,
66 X_NEW_START_DATE_ACTIVE,
67 X_OLD_START_DATE_ACTIVE,
68 X_NEW_END_DATE_ACTIVE,
69 X_OLD_END_DATE_ACTIVE,
70 X_NEW_ACCOUNTING_CODE,
71 X_OLD_ACCOUNTING_CODE,
72 X_NEW_OBJECT_VERSION_NUMBER,
73 X_OLD_OBJECT_VERSION_NUMBER,
74 X_CREATION_DATE,
75 X_CREATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN
79 );
80
81 insert into JTF_RS_GROUPS_AUD_TL (
82 GROUP_AUDIT_ID,
83 NEW_GROUP_NAME,
84 OLD_GROUP_NAME,
85 NEW_GROUP_DESC,
86 OLD_GROUP_DESC,
87 CREATED_BY,
88 CREATION_DATE,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATE_LOGIN,
92 LANGUAGE,
93 SOURCE_LANG
94 ) select
95 X_GROUP_AUDIT_ID,
96 X_NEW_GROUP_NAME,
97 X_OLD_GROUP_NAME,
98 X_NEW_GROUP_DESC,
99 X_OLD_GROUP_DESC,
100 X_CREATED_BY,
101 X_CREATION_DATE,
102 X_LAST_UPDATED_BY,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATE_LOGIN,
105 L.LANGUAGE_CODE,
106 userenv('LANG')
107 from FND_LANGUAGES L
108 where L.INSTALLED_FLAG in ('I', 'B')
109 and not exists
110 (select NULL
111 from JTF_RS_GROUPS_AUD_TL T
112 where T.GROUP_AUDIT_ID = X_GROUP_AUDIT_ID
113 and T.LANGUAGE = L.LANGUAGE_CODE);
114
115 open c;
116 fetch c into X_ROWID;
117 if (c%notfound) then
118 close c;
119 raise no_data_found;
120 end if;
121 close c;
122
123 end INSERT_ROW;
124
125 procedure LOCK_ROW (
126 X_GROUP_AUDIT_ID in NUMBER,
127 X_GROUP_ID in NUMBER,
128 X_NEW_GROUP_NUMBER in VARCHAR2,
129 X_OLD_GROUP_NUMBER in VARCHAR2,
130 X_NEW_EMAIL_ADDRESS in VARCHAR2,
131 X_OLD_EMAIL_ADDRESS in VARCHAR2,
132 X_NEW_EXCLUSIVE_FLAG in VARCHAR2,
133 X_OLD_EXCLUSIVE_FLAG in VARCHAR2,
134 X_NEW_START_DATE_ACTIVE in DATE,
135 X_OLD_START_DATE_ACTIVE in DATE,
136 X_NEW_END_DATE_ACTIVE in DATE,
137 X_OLD_END_DATE_ACTIVE in DATE,
138 X_NEW_ACCOUNTING_CODE in VARCHAR2,
139 X_OLD_ACCOUNTING_CODE in VARCHAR2,
140 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
141 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
142 X_NEW_GROUP_NAME in VARCHAR2,
143 X_OLD_GROUP_NAME in VARCHAR2,
144 X_NEW_GROUP_DESC in VARCHAR2,
145 X_OLD_GROUP_DESC in VARCHAR2
146 ) is
147 cursor c is select
148 GROUP_ID,
149 NEW_GROUP_NUMBER,
150 OLD_GROUP_NUMBER,
151 NEW_EMAIL_ADDRESS,
152 OLD_EMAIL_ADDRESS,
153 NEW_EXCLUSIVE_FLAG,
154 OLD_EXCLUSIVE_FLAG,
155 NEW_START_DATE_ACTIVE,
156 OLD_START_DATE_ACTIVE,
157 NEW_END_DATE_ACTIVE,
158 OLD_END_DATE_ACTIVE,
159 NEW_ACCOUNTING_CODE,
160 OLD_ACCOUNTING_CODE,
161 NEW_OBJECT_VERSION_NUMBER,
162 OLD_OBJECT_VERSION_NUMBER
163 from JTF_RS_GROUPS_AUD_B
164 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID
165 for update of GROUP_AUDIT_ID nowait;
166 recinfo c%rowtype;
167
168 cursor c1 is select
169 NEW_GROUP_NAME,
170 OLD_GROUP_NAME,
171 NEW_GROUP_DESC,
172 OLD_GROUP_DESC,
173 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
174 from JTF_RS_GROUPS_AUD_TL
175 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
177 for update of GROUP_AUDIT_ID nowait;
178 begin
179 open c;
180 fetch c into recinfo;
181 if (c%notfound) then
182 close c;
183 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184 app_exception.raise_exception;
185 end if;
186 close c;
187 if ( (recinfo.GROUP_ID = X_GROUP_ID)
188 AND ((recinfo.NEW_GROUP_NUMBER = X_NEW_GROUP_NUMBER)
189 OR ((recinfo.NEW_GROUP_NUMBER is null) AND (X_NEW_GROUP_NUMBER is null)))
190 AND ((recinfo.OLD_GROUP_NUMBER = X_OLD_GROUP_NUMBER)
191 OR ((recinfo.OLD_GROUP_NUMBER is null) AND (X_OLD_GROUP_NUMBER is null)))
192 AND ((recinfo.NEW_EMAIL_ADDRESS = X_NEW_EMAIL_ADDRESS)
193 OR ((recinfo.NEW_EMAIL_ADDRESS is null) AND (X_NEW_EMAIL_ADDRESS is null)))
194 AND ((recinfo.OLD_EMAIL_ADDRESS = X_OLD_EMAIL_ADDRESS)
195 OR ((recinfo.OLD_EMAIL_ADDRESS is null) AND (X_OLD_EMAIL_ADDRESS is null)))
196 AND ((recinfo.NEW_EXCLUSIVE_FLAG = X_NEW_EXCLUSIVE_FLAG)
197 OR ((recinfo.NEW_EXCLUSIVE_FLAG is null) AND (X_NEW_EXCLUSIVE_FLAG is null)))
198 AND ((recinfo.OLD_EXCLUSIVE_FLAG = X_OLD_EXCLUSIVE_FLAG)
199 OR ((recinfo.OLD_EXCLUSIVE_FLAG is null) AND (X_OLD_EXCLUSIVE_FLAG is null)))
200 AND ((recinfo.NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE)
201 OR ((recinfo.NEW_START_DATE_ACTIVE is null) AND (X_NEW_START_DATE_ACTIVE is null)))
202 AND ((recinfo.OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE)
203 OR ((recinfo.OLD_START_DATE_ACTIVE is null) AND (X_OLD_START_DATE_ACTIVE is null)))
204 AND ((recinfo.NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE)
205 OR ((recinfo.NEW_END_DATE_ACTIVE is null) AND (X_NEW_END_DATE_ACTIVE is null)))
206 AND ((recinfo.OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE)
207 OR ((recinfo.OLD_END_DATE_ACTIVE is null) AND (X_OLD_END_DATE_ACTIVE is null)))
208 AND ((recinfo.NEW_ACCOUNTING_CODE = X_NEW_ACCOUNTING_CODE)
209 OR ((recinfo.NEW_ACCOUNTING_CODE is null) AND (X_NEW_ACCOUNTING_CODE is null)))
210 AND ((recinfo.OLD_ACCOUNTING_CODE = X_OLD_ACCOUNTING_CODE)
211 OR ((recinfo.OLD_ACCOUNTING_CODE is null) AND (X_OLD_ACCOUNTING_CODE is null)))
212 AND ((recinfo.NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER)
213 OR ((recinfo.NEW_OBJECT_VERSION_NUMBER is null) AND (X_NEW_OBJECT_VERSION_NUMBER is null)))
214 AND ((recinfo.OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER)
215 OR ((recinfo.OLD_OBJECT_VERSION_NUMBER is null) AND (X_OLD_OBJECT_VERSION_NUMBER is null)))
216 ) then
217 null;
218 else
219 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
220 app_exception.raise_exception;
221 end if;
222
223 for tlinfo in c1 loop
224 if (tlinfo.BASELANG = 'Y') then
225 if ( ((tlinfo.NEW_GROUP_NAME = X_NEW_GROUP_NAME)
226 OR ((tlinfo.NEW_GROUP_NAME is null) AND (X_NEW_GROUP_NAME is null)))
227 AND ((tlinfo.OLD_GROUP_NAME = X_OLD_GROUP_NAME)
228 OR ((tlinfo.OLD_GROUP_NAME is null) AND (X_OLD_GROUP_NAME is null)))
229 AND ((tlinfo.NEW_GROUP_DESC = X_NEW_GROUP_DESC)
230 OR ((tlinfo.NEW_GROUP_DESC is null) AND (X_NEW_GROUP_DESC is null)))
231 AND ((tlinfo.OLD_GROUP_DESC = X_OLD_GROUP_DESC)
232 OR ((tlinfo.OLD_GROUP_DESC is null) AND (X_OLD_GROUP_DESC is null)))
233 ) then
234 null;
235 else
236 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
237 app_exception.raise_exception;
238 end if;
239 end if;
240 end loop;
241 return;
242 end LOCK_ROW;
243
244 procedure UPDATE_ROW (
245 X_GROUP_AUDIT_ID in NUMBER,
246 X_GROUP_ID in NUMBER,
247 X_NEW_GROUP_NUMBER in VARCHAR2,
248 X_OLD_GROUP_NUMBER in VARCHAR2,
249 X_NEW_EMAIL_ADDRESS in VARCHAR2,
250 X_OLD_EMAIL_ADDRESS in VARCHAR2,
251 X_NEW_EXCLUSIVE_FLAG in VARCHAR2,
252 X_OLD_EXCLUSIVE_FLAG in VARCHAR2,
253 X_NEW_START_DATE_ACTIVE in DATE,
254 X_OLD_START_DATE_ACTIVE in DATE,
255 X_NEW_END_DATE_ACTIVE in DATE,
256 X_OLD_END_DATE_ACTIVE in DATE,
257 X_NEW_ACCOUNTING_CODE in VARCHAR2,
258 X_OLD_ACCOUNTING_CODE in VARCHAR2,
259 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
260 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
261 X_NEW_GROUP_NAME in VARCHAR2,
262 X_OLD_GROUP_NAME in VARCHAR2,
263 X_NEW_GROUP_DESC in VARCHAR2,
264 X_OLD_GROUP_DESC in VARCHAR2,
265 X_LAST_UPDATE_DATE in DATE,
266 X_LAST_UPDATED_BY in NUMBER,
267 X_LAST_UPDATE_LOGIN in NUMBER
268 ) is
269 begin
270 update JTF_RS_GROUPS_AUD_B set
271 GROUP_ID = X_GROUP_ID,
272 NEW_GROUP_NUMBER = X_NEW_GROUP_NUMBER,
273 OLD_GROUP_NUMBER = X_OLD_GROUP_NUMBER,
274 NEW_EMAIL_ADDRESS = X_NEW_EMAIL_ADDRESS,
275 OLD_EMAIL_ADDRESS = X_OLD_EMAIL_ADDRESS,
276 NEW_EXCLUSIVE_FLAG = X_NEW_EXCLUSIVE_FLAG,
277 OLD_EXCLUSIVE_FLAG = X_OLD_EXCLUSIVE_FLAG,
278 NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE,
279 OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE,
280 NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE,
281 OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE,
282 NEW_ACCOUNTING_CODE = X_NEW_ACCOUNTING_CODE,
283 OLD_ACCOUNTING_CODE = X_OLD_ACCOUNTING_CODE,
284 NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER,
285 OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER,
286 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
287 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
288 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
289 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID;
290
291 if (sql%notfound) then
292 raise no_data_found;
293 end if;
294
295 update JTF_RS_GROUPS_AUD_TL set
296 NEW_GROUP_NAME = X_NEW_GROUP_NAME,
297 OLD_GROUP_NAME = X_OLD_GROUP_NAME,
298 NEW_GROUP_DESC = X_NEW_GROUP_DESC,
299 OLD_GROUP_DESC = X_OLD_GROUP_DESC,
300 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
301 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
302 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
303 SOURCE_LANG = userenv('LANG')
304 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID
305 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
306
307 if (sql%notfound) then
308 raise no_data_found;
309 end if;
310 end UPDATE_ROW;
311
312 procedure DELETE_ROW (
313 X_GROUP_AUDIT_ID in NUMBER
314 ) is
315 begin
316 delete from JTF_RS_GROUPS_AUD_TL
317 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID;
318
319 if (sql%notfound) then
320 raise no_data_found;
321 end if;
322
323 delete from JTF_RS_GROUPS_AUD_B
324 where GROUP_AUDIT_ID = X_GROUP_AUDIT_ID;
325
326 if (sql%notfound) then
327 raise no_data_found;
328 end if;
329 end DELETE_ROW;
330
331 procedure ADD_LANGUAGE
332 is
333 begin
334 delete from JTF_RS_GROUPS_AUD_TL T
335 where not exists
336 (select NULL
337 from JTF_RS_GROUPS_AUD_B B
338 where B.GROUP_AUDIT_ID = T.GROUP_AUDIT_ID
339 );
340
341 update JTF_RS_GROUPS_AUD_TL T set (
342 NEW_GROUP_NAME,
343 OLD_GROUP_NAME,
344 NEW_GROUP_DESC,
345 OLD_GROUP_DESC
346 ) = (select
347 B.NEW_GROUP_NAME,
348 B.OLD_GROUP_NAME,
349 B.NEW_GROUP_DESC,
350 B.OLD_GROUP_DESC
351 from JTF_RS_GROUPS_AUD_TL B
352 where B.GROUP_AUDIT_ID = T.GROUP_AUDIT_ID
353 and B.LANGUAGE = T.SOURCE_LANG)
354 where (
355 T.GROUP_AUDIT_ID,
356 T.LANGUAGE
357 ) in (select
358 SUBT.GROUP_AUDIT_ID,
359 SUBT.LANGUAGE
360 from JTF_RS_GROUPS_AUD_TL SUBB, JTF_RS_GROUPS_AUD_TL SUBT
361 where SUBB.GROUP_AUDIT_ID = SUBT.GROUP_AUDIT_ID
362 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
363 and (SUBB.NEW_GROUP_NAME <> SUBT.NEW_GROUP_NAME
364 or (SUBB.NEW_GROUP_NAME is null and SUBT.NEW_GROUP_NAME is not null)
365 or (SUBB.NEW_GROUP_NAME is not null and SUBT.NEW_GROUP_NAME is null)
366 or SUBB.OLD_GROUP_NAME <> SUBT.OLD_GROUP_NAME
367 or (SUBB.OLD_GROUP_NAME is null and SUBT.OLD_GROUP_NAME is not null)
368 or (SUBB.OLD_GROUP_NAME is not null and SUBT.OLD_GROUP_NAME is null)
369 or SUBB.NEW_GROUP_DESC <> SUBT.NEW_GROUP_DESC
370 or (SUBB.NEW_GROUP_DESC is null and SUBT.NEW_GROUP_DESC is not null)
371 or (SUBB.NEW_GROUP_DESC is not null and SUBT.NEW_GROUP_DESC is null)
372 or SUBB.OLD_GROUP_DESC <> SUBT.OLD_GROUP_DESC
373 or (SUBB.OLD_GROUP_DESC is null and SUBT.OLD_GROUP_DESC is not null)
374 or (SUBB.OLD_GROUP_DESC is not null and SUBT.OLD_GROUP_DESC is null)
375 ));
376
377 insert into JTF_RS_GROUPS_AUD_TL (
378 GROUP_AUDIT_ID,
379 NEW_GROUP_NAME,
380 OLD_GROUP_NAME,
381 NEW_GROUP_DESC,
382 OLD_GROUP_DESC,
383 CREATED_BY,
384 CREATION_DATE,
385 LAST_UPDATED_BY,
386 LAST_UPDATE_DATE,
387 LAST_UPDATE_LOGIN,
388 LANGUAGE,
389 SOURCE_LANG
390 ) select
391 B.GROUP_AUDIT_ID,
392 B.NEW_GROUP_NAME,
393 B.OLD_GROUP_NAME,
394 B.NEW_GROUP_DESC,
395 B.OLD_GROUP_DESC,
396 B.CREATED_BY,
397 B.CREATION_DATE,
398 B.LAST_UPDATED_BY,
399 B.LAST_UPDATE_DATE,
400 B.LAST_UPDATE_LOGIN,
401 L.LANGUAGE_CODE,
402 B.SOURCE_LANG
403 from JTF_RS_GROUPS_AUD_TL B, FND_LANGUAGES L
404 where L.INSTALLED_FLAG in ('I', 'B')
405 and B.LANGUAGE = userenv('LANG')
406 and not exists
407 (select NULL
408 from JTF_RS_GROUPS_AUD_TL T
409 where T.GROUP_AUDIT_ID = B.GROUP_AUDIT_ID
410 and T.LANGUAGE = L.LANGUAGE_CODE);
411 end ADD_LANGUAGE;
412
413
414 Procedure TRANSLATE_ROW
415 (x_group_audit_id in number,
416 x_new_group_name in varchar2,
417 x_new_group_desc in varchar2,
418 x_old_group_name in varchar2,
419 x_old_group_desc in varchar2,
420 x_Last_update_date in date,
421 x_last_updated_by in number,
422 x_last_update_login in number)
423 is
424 begin
425
426 Update jtf_rs_groups_aud_tl set
427 new_group_name = nvl(x_new_group_name,new_group_name),
428 new_group_desc = nvl(x_new_group_desc,new_group_desc),
429 old_group_name = nvl(x_old_group_name,old_group_name),
430 old_group_desc = nvl(x_old_group_desc,old_group_desc),
431 last_update_date = nvl(x_last_update_date,sysdate),
432 last_updated_by = x_last_updated_by,
433 last_update_login = 0,
434 source_lang = userenv('LANG')
435 where group_audit_id = x_group_audit_id
436 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
437
438 if (sql%notfound) then
439 raise no_data_found;
440 end if;
441 end TRANSLATE_ROW;
442
443 end JTF_RS_GROUPS_AUD_PKG;
[Home] [Help]