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