1 package body AHL_UNIT_DEFERRALS_PKG as
2 /* $Header: AHLLUDFB.pls 120.2 2005/12/20 06:14 sracha noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_UNIT_DEFERRAL_ID in out nocopy NUMBER,
6 X_ATA_SEQUENCE_ID in NUMBER,
7 X_ATTRIBUTE5 in VARCHAR2,
8 X_ATTRIBUTE6 in VARCHAR2,
9 X_ATTRIBUTE7 in VARCHAR2,
10 X_ATTRIBUTE8 in VARCHAR2,
11 X_ATTRIBUTE9 in VARCHAR2,
12 X_ATTRIBUTE10 in VARCHAR2,
13 X_ATTRIBUTE11 in VARCHAR2,
14 X_ATTRIBUTE12 in VARCHAR2,
15 X_ATTRIBUTE13 in VARCHAR2,
16 X_ATTRIBUTE14 in VARCHAR2,
17 X_ATTRIBUTE15 in VARCHAR2,
18 X_UNIT_EFFECTIVITY_ID in NUMBER,
19 X_UNIT_DEFERRAL_TYPE in VARCHAR2,
20 X_SET_DUE_DATE in DATE,
21 X_APPROVAL_STATUS_CODE in VARCHAR2,
22 X_SKIP_MR_FLAG in VARCHAR2,
23 X_AFFECT_DUE_CALC_FLAG in VARCHAR2,
24 X_DEFER_REASON_CODE in VARCHAR2,
25 X_DEFERRAL_EFFECTIVE_ON in DATE,
26 X_OBJECT_VERSION_NUMBER in NUMBER,
27 X_ATTRIBUTE_CATEGORY in VARCHAR2,
28 X_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE2 in VARCHAR2,
30 X_ATTRIBUTE3 in VARCHAR2,
31 X_ATTRIBUTE4 in VARCHAR2,
32 X_REMARKS in VARCHAR2,
33 X_APPROVER_NOTES in VARCHAR2,
34 X_USER_DEFERRAL_TYPE IN VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER
40 ) is
41 cursor C is select ROWID from AHL_UNIT_DEFERRALS_B
42 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID
43 ;
44 begin
45 insert into AHL_UNIT_DEFERRALS_B (
46 ATA_SEQUENCE_ID,
47 ATTRIBUTE5,
48 ATTRIBUTE6,
49 ATTRIBUTE7,
50 ATTRIBUTE8,
51 ATTRIBUTE9,
52 ATTRIBUTE10,
53 ATTRIBUTE11,
54 ATTRIBUTE12,
55 ATTRIBUTE13,
56 ATTRIBUTE14,
57 ATTRIBUTE15,
58 UNIT_DEFERRAL_ID,
59 UNIT_EFFECTIVITY_ID,
60 UNIT_DEFERRAL_TYPE,
61 SET_DUE_DATE,
62 APPROVAL_STATUS_CODE,
63 SKIP_MR_FLAG,
64 AFFECT_DUE_CALC_FLAG,
65 DEFER_REASON_CODE,
66 DEFERRAL_EFFECTIVE_ON,
67 OBJECT_VERSION_NUMBER,
68 USER_DEFERRAL_TYPE,
69 ATTRIBUTE_CATEGORY,
70 ATTRIBUTE1,
71 ATTRIBUTE2,
72 ATTRIBUTE3,
73 ATTRIBUTE4,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 LAST_UPDATE_LOGIN
79 ) values (
80 X_ATA_SEQUENCE_ID,
81 X_ATTRIBUTE5,
82 X_ATTRIBUTE6,
83 X_ATTRIBUTE7,
84 X_ATTRIBUTE8,
85 X_ATTRIBUTE9,
86 X_ATTRIBUTE10,
87 X_ATTRIBUTE11,
88 X_ATTRIBUTE12,
89 X_ATTRIBUTE13,
90 X_ATTRIBUTE14,
91 X_ATTRIBUTE15,
92 AHL_UNIT_DEFERRALS_B_S.NEXTVAL,
93 X_UNIT_EFFECTIVITY_ID,
94 X_UNIT_DEFERRAL_TYPE,
95 X_SET_DUE_DATE,
96 X_APPROVAL_STATUS_CODE,
97 X_SKIP_MR_FLAG,
98 X_AFFECT_DUE_CALC_FLAG,
99 X_DEFER_REASON_CODE,
100 X_DEFERRAL_EFFECTIVE_ON,
101 X_OBJECT_VERSION_NUMBER,
102 X_USER_DEFERRAL_TYPE,
103 X_ATTRIBUTE_CATEGORY,
104 X_ATTRIBUTE1,
105 X_ATTRIBUTE2,
106 X_ATTRIBUTE3,
107 X_ATTRIBUTE4,
108 X_CREATION_DATE,
109 X_CREATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN
113 ) RETURNING UNIT_DEFERRAL_ID INTO X_UNIT_DEFERRAL_ID;
114
115 insert into AHL_UNIT_DEFERRALS_TL (
116 UNIT_DEFERRAL_ID,
117 LAST_UPDATE_DATE,
118 LAST_UPDATED_BY,
119 CREATION_DATE,
120 CREATED_BY,
121 LAST_UPDATE_LOGIN,
122 REMARKS,
123 APPROVER_NOTES,
124 LANGUAGE,
125 SOURCE_LANG
126 ) select
127 X_UNIT_DEFERRAL_ID,
128 X_LAST_UPDATE_DATE,
129 X_LAST_UPDATED_BY,
130 X_CREATION_DATE,
131 X_CREATED_BY,
132 X_LAST_UPDATE_LOGIN,
133 X_REMARKS,
134 X_APPROVER_NOTES,
135 L.LANGUAGE_CODE,
136 userenv('LANG')
137 from FND_LANGUAGES L
138 where L.INSTALLED_FLAG in ('I', 'B')
139 and not exists
140 (select NULL
141 from AHL_UNIT_DEFERRALS_TL T
142 where T.UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID
143 and T.LANGUAGE = L.LANGUAGE_CODE);
144
145 open c;
146 fetch c into X_ROWID;
147 if (c%notfound) then
148 close c;
149 raise no_data_found;
150 end if;
151 close c;
152
153 end INSERT_ROW;
154
155 procedure LOCK_ROW (
156 X_UNIT_DEFERRAL_ID in NUMBER,
157 X_ATA_SEQUENCE_ID in NUMBER,
158 X_ATTRIBUTE5 in VARCHAR2,
159 X_ATTRIBUTE6 in VARCHAR2,
160 X_ATTRIBUTE7 in VARCHAR2,
161 X_ATTRIBUTE8 in VARCHAR2,
162 X_ATTRIBUTE9 in VARCHAR2,
163 X_ATTRIBUTE10 in VARCHAR2,
164 X_ATTRIBUTE11 in VARCHAR2,
165 X_ATTRIBUTE12 in VARCHAR2,
166 X_ATTRIBUTE13 in VARCHAR2,
167 X_ATTRIBUTE14 in VARCHAR2,
168 X_ATTRIBUTE15 in VARCHAR2,
169 X_UNIT_EFFECTIVITY_ID in NUMBER,
170 X_UNIT_DEFERRAL_TYPE in VARCHAR2,
171 X_SET_DUE_DATE in DATE,
172 X_APPROVAL_STATUS_CODE in VARCHAR2,
173 X_SKIP_MR_FLAG in VARCHAR2,
174 X_AFFECT_DUE_CALC_FLAG in VARCHAR2,
175 X_DEFER_REASON_CODE in VARCHAR2,
176 X_DEFERRAL_EFFECTIVE_ON in DATE,
177 X_OBJECT_VERSION_NUMBER in NUMBER,
178 X_ATTRIBUTE_CATEGORY in VARCHAR2,
179 X_ATTRIBUTE1 in VARCHAR2,
180 X_ATTRIBUTE2 in VARCHAR2,
181 X_ATTRIBUTE3 in VARCHAR2,
182 X_ATTRIBUTE4 in VARCHAR2,
183 X_REMARKS in VARCHAR2,
184 X_APPROVER_NOTES in VARCHAR2,
185 X_USER_DEFERRAL_TYPE IN VARCHAR2
186 ) is
187 cursor c is select
188 ATA_SEQUENCE_ID,
189 ATTRIBUTE5,
190 ATTRIBUTE6,
191 ATTRIBUTE7,
192 ATTRIBUTE8,
193 ATTRIBUTE9,
194 ATTRIBUTE10,
195 ATTRIBUTE11,
196 ATTRIBUTE12,
197 ATTRIBUTE13,
198 ATTRIBUTE14,
199 ATTRIBUTE15,
200 UNIT_EFFECTIVITY_ID,
201 UNIT_DEFERRAL_TYPE,
202 SET_DUE_DATE,
203 APPROVAL_STATUS_CODE,
204 SKIP_MR_FLAG,
205 AFFECT_DUE_CALC_FLAG,
206 DEFER_REASON_CODE,
207 DEFERRAL_EFFECTIVE_ON,
208 OBJECT_VERSION_NUMBER,
209 USER_DEFERRAL_TYPE,
210 ATTRIBUTE_CATEGORY,
211 ATTRIBUTE1,
212 ATTRIBUTE2,
213 ATTRIBUTE3,
214 ATTRIBUTE4
215 from AHL_UNIT_DEFERRALS_B
216 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID
217 for update of UNIT_DEFERRAL_ID nowait;
218 recinfo c%rowtype;
219
220 cursor c1 is select
221 REMARKS,
222 APPROVER_NOTES,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from AHL_UNIT_DEFERRALS_TL
225 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of UNIT_DEFERRAL_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( ((recinfo.ATA_SEQUENCE_ID = X_ATA_SEQUENCE_ID)
238 OR ((recinfo.ATA_SEQUENCE_ID is null) AND (X_ATA_SEQUENCE_ID is null)))
239 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
240 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
241 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
242 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
243 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
244 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
245 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
246 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
247 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
248 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
249 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
250 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
251 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
252 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
253 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
254 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
255 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
256 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
257 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
258 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
259 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
260 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
261 AND (recinfo.UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID)
262 AND (recinfo.UNIT_DEFERRAL_TYPE = X_UNIT_DEFERRAL_TYPE)
263 AND ((recinfo.SET_DUE_DATE = X_SET_DUE_DATE)
264 OR ((recinfo.SET_DUE_DATE is null) AND (X_SET_DUE_DATE is null)))
265 AND ((recinfo.APPROVAL_STATUS_CODE = X_APPROVAL_STATUS_CODE)
266 OR ((recinfo.APPROVAL_STATUS_CODE is null) AND (X_APPROVAL_STATUS_CODE is null)))
267 AND ((recinfo.USER_DEFERRAL_TYPE = X_USER_DEFERRAL_TYPE)
268 OR ((recinfo.USER_DEFERRAL_TYPE is null) AND (X_USER_DEFERRAL_TYPE is null)))
269 AND ((recinfo.SKIP_MR_FLAG = X_SKIP_MR_FLAG)
270 OR ((recinfo.SKIP_MR_FLAG is null) AND (X_SKIP_MR_FLAG is null)))
271 AND ((recinfo.AFFECT_DUE_CALC_FLAG = X_AFFECT_DUE_CALC_FLAG)
272 OR ((recinfo.AFFECT_DUE_CALC_FLAG is null) AND (X_AFFECT_DUE_CALC_FLAG is null)))
273 AND ((recinfo.DEFER_REASON_CODE = X_DEFER_REASON_CODE)
274 OR ((recinfo.DEFER_REASON_CODE is null) AND (X_DEFER_REASON_CODE is null)))
275 AND ((recinfo.DEFERRAL_EFFECTIVE_ON = X_DEFERRAL_EFFECTIVE_ON)
276 OR ((recinfo.DEFERRAL_EFFECTIVE_ON is null) AND (X_DEFERRAL_EFFECTIVE_ON is null)))
277 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
278 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
279 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
280 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
281 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
282 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
283 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
284 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
285 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
286 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
287 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
288 ) then
289 null;
290 else
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 end if;
294
295 for tlinfo in c1 loop
296 if (tlinfo.BASELANG = 'Y') then
297 if ( ((tlinfo.REMARKS = X_REMARKS)
298 OR ((tlinfo.REMARKS is null) AND (X_REMARKS is null)))
299 AND ((tlinfo.APPROVER_NOTES = X_APPROVER_NOTES)
300 OR ((tlinfo.APPROVER_NOTES is null) AND (X_APPROVER_NOTES is null)))
301 ) then
302 null;
303 else
304 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
305 app_exception.raise_exception;
306 end if;
307 end if;
308 end loop;
309 return;
310 end LOCK_ROW;
311
312 procedure UPDATE_ROW (
313 X_UNIT_DEFERRAL_ID in NUMBER,
314 X_ATA_SEQUENCE_ID in NUMBER,
315 X_ATTRIBUTE5 in VARCHAR2,
316 X_ATTRIBUTE6 in VARCHAR2,
317 X_ATTRIBUTE7 in VARCHAR2,
318 X_ATTRIBUTE8 in VARCHAR2,
319 X_ATTRIBUTE9 in VARCHAR2,
320 X_ATTRIBUTE10 in VARCHAR2,
321 X_ATTRIBUTE11 in VARCHAR2,
322 X_ATTRIBUTE12 in VARCHAR2,
323 X_ATTRIBUTE13 in VARCHAR2,
324 X_ATTRIBUTE14 in VARCHAR2,
325 X_ATTRIBUTE15 in VARCHAR2,
326 X_UNIT_EFFECTIVITY_ID in NUMBER,
327 X_UNIT_DEFERRAL_TYPE in VARCHAR2,
328 X_SET_DUE_DATE in DATE,
329 X_APPROVAL_STATUS_CODE in VARCHAR2,
330 X_SKIP_MR_FLAG in VARCHAR2,
331 X_AFFECT_DUE_CALC_FLAG in VARCHAR2,
332 X_DEFER_REASON_CODE in VARCHAR2,
333 X_DEFERRAL_EFFECTIVE_ON in DATE,
334 X_OBJECT_VERSION_NUMBER in NUMBER,
335 X_ATTRIBUTE_CATEGORY in VARCHAR2,
336 X_ATTRIBUTE1 in VARCHAR2,
337 X_ATTRIBUTE2 in VARCHAR2,
338 X_ATTRIBUTE3 in VARCHAR2,
339 X_ATTRIBUTE4 in VARCHAR2,
340 X_REMARKS in VARCHAR2,
341 X_APPROVER_NOTES in VARCHAR2,
342 X_USER_DEFERRAL_TYPE IN VARCHAR2,
343 X_LAST_UPDATE_DATE in DATE,
344 X_LAST_UPDATED_BY in NUMBER,
345 X_LAST_UPDATE_LOGIN in NUMBER
346 ) is
347 begin
348 update AHL_UNIT_DEFERRALS_B set
349 ATA_SEQUENCE_ID = X_ATA_SEQUENCE_ID,
350 ATTRIBUTE5 = X_ATTRIBUTE5,
351 ATTRIBUTE6 = X_ATTRIBUTE6,
352 ATTRIBUTE7 = X_ATTRIBUTE7,
353 ATTRIBUTE8 = X_ATTRIBUTE8,
354 ATTRIBUTE9 = X_ATTRIBUTE9,
355 ATTRIBUTE10 = X_ATTRIBUTE10,
356 ATTRIBUTE11 = X_ATTRIBUTE11,
357 ATTRIBUTE12 = X_ATTRIBUTE12,
358 ATTRIBUTE13 = X_ATTRIBUTE13,
359 ATTRIBUTE14 = X_ATTRIBUTE14,
360 ATTRIBUTE15 = X_ATTRIBUTE15,
361 UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID,
362 UNIT_DEFERRAL_TYPE = X_UNIT_DEFERRAL_TYPE,
363 SET_DUE_DATE = X_SET_DUE_DATE,
364 APPROVAL_STATUS_CODE = X_APPROVAL_STATUS_CODE,
365 SKIP_MR_FLAG = X_SKIP_MR_FLAG,
366 AFFECT_DUE_CALC_FLAG = X_AFFECT_DUE_CALC_FLAG,
367 DEFER_REASON_CODE = X_DEFER_REASON_CODE,
368 DEFERRAL_EFFECTIVE_ON = X_DEFERRAL_EFFECTIVE_ON,
369 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
370 USER_DEFERRAL_TYPE = X_USER_DEFERRAL_TYPE,
371 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
372 ATTRIBUTE1 = X_ATTRIBUTE1,
373 ATTRIBUTE2 = X_ATTRIBUTE2,
374 ATTRIBUTE3 = X_ATTRIBUTE3,
375 ATTRIBUTE4 = X_ATTRIBUTE4,
376 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
377 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
378 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
379 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID;
380
381 if (sql%notfound) then
382 raise no_data_found;
383 end if;
384
385 update AHL_UNIT_DEFERRALS_TL set
386 REMARKS = X_REMARKS,
387 APPROVER_NOTES = X_APPROVER_NOTES,
388 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
389 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
390 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
391 SOURCE_LANG = userenv('LANG')
392 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID
393 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
394
395 if (sql%notfound) then
396 raise no_data_found;
397 end if;
398 end UPDATE_ROW;
399
400 procedure DELETE_ROW (
401 X_UNIT_DEFERRAL_ID in NUMBER
402 ) is
403 begin
404 delete from AHL_UNIT_DEFERRALS_TL
405 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID;
406
407 if (sql%notfound) then
408 raise no_data_found;
409 end if;
410
411 delete from AHL_UNIT_DEFERRALS_B
412 where UNIT_DEFERRAL_ID = X_UNIT_DEFERRAL_ID;
413
414 if (sql%notfound) then
415 raise no_data_found;
416 end if;
417 end DELETE_ROW;
418
419 procedure ADD_LANGUAGE
420 is
421 begin
422 delete from AHL_UNIT_DEFERRALS_TL T
423 where not exists
424 (select NULL
425 from AHL_UNIT_DEFERRALS_B B
426 where B.UNIT_DEFERRAL_ID = T.UNIT_DEFERRAL_ID
427 );
428
429 update AHL_UNIT_DEFERRALS_TL T set (
430 REMARKS,
431 APPROVER_NOTES
432 ) = (select
433 B.REMARKS,
434 B.APPROVER_NOTES
435 from AHL_UNIT_DEFERRALS_TL B
436 where B.UNIT_DEFERRAL_ID = T.UNIT_DEFERRAL_ID
437 and B.LANGUAGE = T.SOURCE_LANG)
438 where (
439 T.UNIT_DEFERRAL_ID,
440 T.LANGUAGE
441 ) in (select
442 SUBT.UNIT_DEFERRAL_ID,
443 SUBT.LANGUAGE
444 from AHL_UNIT_DEFERRALS_TL SUBB, AHL_UNIT_DEFERRALS_TL SUBT
445 where SUBB.UNIT_DEFERRAL_ID = SUBT.UNIT_DEFERRAL_ID
446 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
447 and (SUBB.REMARKS <> SUBT.REMARKS
448 or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
449 or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
450 or SUBB.APPROVER_NOTES <> SUBT.APPROVER_NOTES
451 or (SUBB.APPROVER_NOTES is null and SUBT.APPROVER_NOTES is not null)
452 or (SUBB.APPROVER_NOTES is not null and SUBT.APPROVER_NOTES is null)
453 ));
454
455 insert into AHL_UNIT_DEFERRALS_TL (
456 UNIT_DEFERRAL_ID,
457 LAST_UPDATE_DATE,
458 LAST_UPDATED_BY,
459 CREATION_DATE,
460 CREATED_BY,
461 LAST_UPDATE_LOGIN,
462 REMARKS,
463 APPROVER_NOTES,
464 LANGUAGE,
465 SOURCE_LANG
466 ) select /*+ ORDERED */
467 B.UNIT_DEFERRAL_ID,
468 B.LAST_UPDATE_DATE,
469 B.LAST_UPDATED_BY,
470 B.CREATION_DATE,
471 B.CREATED_BY,
472 B.LAST_UPDATE_LOGIN,
473 B.REMARKS,
474 B.APPROVER_NOTES,
475 L.LANGUAGE_CODE,
476 B.SOURCE_LANG
477 from AHL_UNIT_DEFERRALS_TL B, FND_LANGUAGES L
478 where L.INSTALLED_FLAG in ('I', 'B')
479 and B.LANGUAGE = userenv('LANG')
480 and not exists
481 (select NULL
482 from AHL_UNIT_DEFERRALS_TL T
483 where T.UNIT_DEFERRAL_ID = B.UNIT_DEFERRAL_ID
484 and T.LANGUAGE = L.LANGUAGE_CODE);
485 end ADD_LANGUAGE;
486
487 end AHL_UNIT_DEFERRALS_PKG;
[Home] [Help]