1 PACKAGE BODY LNS_COND_ASSIGNMENTS_PKG AS
2 /* $Header: LNS_CASGM_TBLH_B.pls 120.2.12010000.7 2010/03/17 13:36:03 scherkas ship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_COND_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
7 ,P_LOAN_ID IN NUMBER
8 ,P_CONDITION_ID IN NUMBER
9 ,P_CONDITION_DESCRIPTION IN VARCHAR2
10 ,P_CONDITION_MET_FLAG IN VARCHAR2
11 ,P_MANDATORY_FLAG IN VARCHAR2
12 ,P_CREATED_BY IN NUMBER
13 ,P_CREATION_DATE IN DATE
14 ,P_LAST_UPDATED_BY IN NUMBER
15 ,P_LAST_UPDATE_DATE IN DATE
16 ,P_LAST_UPDATE_LOGIN IN NUMBER
17 ,P_OBJECT_VERSION_NUMBER IN NUMBER
18 ,P_FULFILLMENT_DATE IN DATE
19 ,P_FULFILLMENT_UPDATED_BY IN NUMBER
20 ,P_START_DATE_ACTIVE IN DATE
21 ,P_END_DATE_ACTIVE IN DATE
22 ,P_DISB_HEADER_ID IN NUMBER
23 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
24 ,P_OWNER_OBJECT_ID IN NUMBER
25 ,P_OWNER_TABLE IN VARCHAR2
26 ) IS
27
28 CURSOR l_insert is
29 SELECT ROWID FROM LNS_COND_ASSIGNMENTS
30 WHERE COND_ASSIGNMENT_ID = X_COND_ASSIGNMENT_ID ;
31
32 l_rowid varchar2(2000);
33
34 BEGIN
35 INSERT INTO LNS_COND_ASSIGNMENTS
36 (
37 COND_ASSIGNMENT_ID
38 ,LOAN_ID
39 ,CONDITION_ID
40 ,CONDITION_MET_FLAG
41 ,MANDATORY_FLAG
42 ,CREATED_BY
43 ,CREATION_DATE
44 ,LAST_UPDATED_BY
45 ,LAST_UPDATE_DATE
46 ,LAST_UPDATE_LOGIN
47 ,OBJECT_VERSION_NUMBER
48 ,FULFILLMENT_DATE
49 ,FULFILLMENT_UPDATED_BY
50 ,START_DATE_ACTIVE
51 ,END_DATE_ACTIVE
52 ,DISB_HEADER_ID
53 ,DELETE_DISABLED_FLAG
54 ,OWNER_OBJECT_ID
55 ,OWNER_TABLE
56 ) VALUES (
57 DECODE(X_COND_ASSIGNMENT_ID, FND_API.G_MISS_NUM, LNS_COND_ASSIGNMENTS_S.NEXTVAL, NULL, LNS_COND_ASSIGNMENTS_S.NEXTVAL, X_COND_ASSIGNMENT_ID)
58 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
59 ,DECODE(P_CONDITION_ID, FND_API.G_MISS_NUM, NULL, P_CONDITION_ID)
60 ,DECODE(P_CONDITION_MET_FLAG, FND_API.G_MISS_CHAR, NULL, P_CONDITION_MET_FLAG)
61 ,DECODE(P_MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, P_MANDATORY_FLAG)
62 ,LNS_UTILITY_PUB.CREATED_BY
63 ,LNS_UTILITY_PUB.CREATION_DATE
64 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
65 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
66 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
67 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
68 ,DECODE(P_FULFILLMENT_DATE, FND_API.G_MISS_DATE, NULL, P_FULFILLMENT_DATE)
69 ,DECODE(P_FULFILLMENT_UPDATED_BY, FND_API.G_MISS_NUM, NULL, P_FULFILLMENT_UPDATED_BY)
70 ,DECODE(P_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
71 ,DECODE(P_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
72 ,DECODE(P_DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
73 ,DECODE(P_DELETE_DISABLED_FLAG ,FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG)
74 ,DECODE(P_OWNER_OBJECT_ID, FND_API.G_MISS_NUM, NULL, P_OWNER_OBJECT_ID)
75 ,DECODE(P_OWNER_TABLE ,FND_API.G_MISS_CHAR, NULL, P_OWNER_TABLE)
76 ) RETURNING
77 COND_ASSIGNMENT_ID
78 INTO
79 X_COND_ASSIGNMENT_ID;
80
81 INSERT INTO LNS_COND_ASSIGNMENTS_TL (
82 COND_ASSIGNMENT_ID
83 ,CONDITION_DESCRIPTION
84 ,LANGUAGE
85 ,SOURCE_LANG
86 ,CREATED_BY
87 ,CREATION_DATE
88 ,LAST_UPDATE_DATE
89 ,LAST_UPDATED_BY
90 ,LAST_UPDATE_LOGIN
91 ) SELECT
92 X_COND_ASSIGNMENT_ID
93 ,DECODE(P_CONDITION_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_CONDITION_DESCRIPTION)
94 ,L.LANGUAGE_CODE
95 ,userenv('LANG')
96 ,fnd_global.user_id
97 ,sysdate
98 ,sysdate
99 ,fnd_global.user_id
100 ,fnd_global.user_id
101 FROM FND_LANGUAGES L
102 where L.INSTALLED_FLAG in ('I', 'B')
103 AND L.LANGUAGE_CODE = userenv('LANG')
104 AND not exists
105 (select NULL
106 from LNS_COND_ASSIGNMENTS_TL T
107 where T.cond_assignment_id = x_cond_assignment_id
108 and T.LANGUAGE = L.LANGUAGE_CODE);
109
110
111
112 INSERT INTO LNS_COND_ASSIGNMENTS_TL (
113 COND_ASSIGNMENT_ID
114 ,CONDITION_DESCRIPTION
115 ,LANGUAGE
116 ,SOURCE_LANG
117 ,CREATED_BY
118 ,CREATION_DATE
119 ,LAST_UPDATE_DATE
120 ,LAST_UPDATED_BY
121 ,LAST_UPDATE_LOGIN
122 ) SELECT
123 X_COND_ASSIGNMENT_ID
124 ,SourceTL.CONDITION_DESCRIPTION
125 ,SourceTL.LANGUAGE
126 ,SourceTL.SOURCE_LANG
127 ,fnd_global.user_id
128 ,sysdate
129 ,sysdate
130 ,fnd_global.user_id
131 ,fnd_global.user_id
132 FROM FND_LANGUAGES L, LNS_CONDITIONS_TL SourceTL
133 where L.INSTALLED_FLAG in ('I', 'B')
134 and L.LANGUAGE_CODE <> userenv('LANG')
135 AND SourceTL.language = L.LANGUAGE_CODE
136 AND SourceTL.CONDITION_ID = (
137 SELECT CONDITION_ID FROM LNS_COND_ASSIGNMENTS
138 WHERE COND_ASSIGNMENT_ID = X_COND_ASSIGNMENT_ID
139 )
140 AND not exists
141 (select NULL
142 from LNS_COND_ASSIGNMENTS_TL T
143 where T.cond_assignment_id = x_cond_assignment_id
144 and T.LANGUAGE = L.LANGUAGE_CODE);
145
146
147
148 OPEN l_insert;
149 FETCH l_insert INTO l_rowid;
150 IF (l_insert%notfound) THEN
151 CLOSE l_insert;
152 RAISE no_data_found;
153 END IF;
154 CLOSE l_insert;
155
156 END Insert_Row;
157
158 /* Update_Row procedure */
159 PROCEDURE Update_Row(
160 P_COND_ASSIGNMENT_ID IN NUMBER
161 ,P_LOAN_ID IN NUMBER
162 ,P_CONDITION_ID IN NUMBER
163 ,P_CONDITION_DESCRIPTION IN VARCHAR2
164 ,P_CONDITION_MET_FLAG IN VARCHAR2
165 ,P_MANDATORY_FLAG IN VARCHAR2
166 ,P_LAST_UPDATED_BY IN NUMBER
167 ,P_LAST_UPDATE_DATE IN DATE
168 ,P_LAST_UPDATE_LOGIN IN NUMBER
169 ,P_OBJECT_VERSION_NUMBER IN NUMBER
170 ,P_FULFILLMENT_DATE IN DATE
171 ,P_FULFILLMENT_UPDATED_BY IN NUMBER
172 ,P_START_DATE_ACTIVE IN DATE
173 ,P_END_DATE_ACTIVE IN DATE
174 ,P_DISB_HEADER_ID IN NUMBER
175 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
176 ,P_OWNER_OBJECT_ID IN NUMBER
177 ,P_OWNER_TABLE IN VARCHAR2
178 ) IS
179 -- It is not possible to pass negative Date values from Java.
180 -- So use LNS_NULL_DATE instead of FND_API.G_MISS_DATE
181 LNS_NULL_DATE CONSTANT DATE := TO_DATE('01/01/4711','DD/MM/YYYY');
182 --FND_API.G_MISS_NUM is large to fit into NUMBER(20) of FULFILLMENT_UPDATED_BY
183 --So using LNS_NULL_NUM
184 LNS_NULL_NUM CONSTANT NUMBER := -999999999;
185
186 BEGIN
187 UPDATE LNS_COND_ASSIGNMENTS SET
188 LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
189 ,CONDITION_ID = DECODE(P_CONDITION_ID, NULL, CONDITION_ID, FND_API.G_MISS_NUM, NULL, P_CONDITION_ID)
190 ,CONDITION_MET_FLAG = DECODE(P_CONDITION_MET_FLAG, NULL, CONDITION_MET_FLAG, FND_API.G_MISS_CHAR, NULL, P_CONDITION_MET_FLAG)
191 ,MANDATORY_FLAG = DECODE(P_MANDATORY_FLAG, NULL, MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, P_MANDATORY_FLAG)
192 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
193 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
194 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
195 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
196 --,FULFILLMENT_DATE = DECODE(P_FULFILLMENT_DATE, NULL, FULFILLMENT_DATE, FND_API.G_MISS_DATE, NULL, P_FULFILLMENT_DATE)
197 ,FULFILLMENT_DATE = DECODE(P_FULFILLMENT_DATE, NULL, FULFILLMENT_DATE, LNS_NULL_DATE, NULL, P_FULFILLMENT_DATE)
198 ,FULFILLMENT_UPDATED_BY = DECODE(P_FULFILLMENT_UPDATED_BY, NULL, FULFILLMENT_UPDATED_BY, LNS_NULL_NUM, NULL, P_FULFILLMENT_UPDATED_BY)
199 ,START_DATE_ACTIVE = DECODE(P_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
200 ,END_DATE_ACTIVE = DECODE(P_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
201 ,DISB_HEADER_ID = DECODE(P_DISB_HEADER_ID, NULL, DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
202 ,DELETE_DISABLED_FLAG = DECODE(P_DELETE_DISABLED_FLAG, NULL, DELETE_DISABLED_FLAG, FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG )
203 ,OWNER_OBJECT_ID = DECODE(P_OWNER_OBJECT_ID, NULL, OWNER_OBJECT_ID, FND_API.G_MISS_NUM, NULL, P_OWNER_OBJECT_ID)
204 ,OWNER_TABLE = DECODE(P_OWNER_TABLE, NULL, OWNER_TABLE, FND_API.G_MISS_CHAR, NULL, P_OWNER_TABLE )
205 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210
211 UPDATE LNS_COND_ASSIGNMENTS_TL SET
212 CONDITION_DESCRIPTION = DECODE(P_CONDITION_DESCRIPTION, NULL, CONDITION_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_CONDITION_DESCRIPTION)
213 ,LAST_UPDATE_DATE = sysdate
214 ,LAST_UPDATED_BY = fnd_global.user_id
215 ,LAST_UPDATE_LOGIN = fnd_global.user_id
216 ,SOURCE_LANG = userenv('LANG')
217 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
218 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
219
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 END Update_Row;
226
227 /* Delete_Row procedure */
228 PROCEDURE Delete_Row(P_COND_ASSIGNMENT_ID IN NUMBER) IS
229 BEGIN
230
231 DELETE FROM LNS_COND_ASSIGNMENTS_TL
232 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
233
234 if (sql%notfound) then
235 raise no_data_found;
236 end if;
237
238 DELETE FROM LNS_COND_ASSIGNMENTS
239 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
240
241 if (sql%notfound) then
242 raise no_data_found;
243 end if;
244 END Delete_Row;
245
246 /* Lock_Row procedure */
247 PROCEDURE Lock_Row(
248 P_COND_ASSIGNMENT_ID IN NUMBER
249 ,P_LOAN_ID IN NUMBER
250 ,P_CONDITION_ID IN NUMBER
251 ,P_CONDITION_DESCRIPTION IN VARCHAR2
252 ,P_CONDITION_MET_FLAG IN VARCHAR2
253 ,P_MANDATORY_FLAG IN VARCHAR2
254 ,P_CREATED_BY IN NUMBER
255 ,P_CREATION_DATE IN DATE
256 ,P_LAST_UPDATED_BY IN NUMBER
257 ,P_LAST_UPDATE_DATE IN DATE
258 ,P_LAST_UPDATE_LOGIN IN NUMBER
259 ,P_OBJECT_VERSION_NUMBER IN NUMBER
260 ,P_FULFILLMENT_DATE IN DATE
261 ,P_FULFILLMENT_UPDATED_BY IN NUMBER
262 ,P_START_DATE_ACTIVE IN DATE
263 ,P_END_DATE_ACTIVE IN DATE
264 ,P_DISB_HEADER_ID IN NUMBER
265 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
266 ,P_OWNER_OBJECT_ID IN NUMBER
267 ,P_OWNER_TABLE IN VARCHAR2
268 ) IS
269 CURSOR C IS SELECT * FROM LNS_COND_ASSIGNMENTS
270 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
271 FOR UPDATE of COND_ASSIGNMENT_ID NOWAIT;
272 Recinfo C%ROWTYPE;
273
274 CURSOR C_TL IS
275 SELECT
276 CONDITION_DESCRIPTION
277 ,decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
278 FROM LNS_COND_ASSIGNMENTS_TL
279 WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
280 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
281 FOR UPDATE of COND_ASSIGNMENT_ID NOWAIT;
282 -- tlinfo C_TL%ROWTYPE;
283
284
285
286 BEGIN
287 OPEN C;
288 FETCH C INTO Recinfo;
289 IF (C%NOTFOUND) THEN
290 CLOSE C;
291 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
292 APP_EXCEPTION.Raise_Exception;
293 END IF;
294 CLOSE C;
295
296 IF (
297 (Recinfo.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID)
298 AND ( (Recinfo.LOAN_ID = P_LOAN_ID)
299 OR ( (Recinfo.LOAN_ID IS NULL)
300 AND (P_LOAN_ID IS NULL)))
301 AND ( (Recinfo.CONDITION_ID = P_CONDITION_ID)
302 OR ( (Recinfo.CONDITION_ID IS NULL)
303 AND (P_CONDITION_ID IS NULL)))
304 AND ( (Recinfo.CONDITION_DESCRIPTION = P_CONDITION_DESCRIPTION)
305 OR ( (Recinfo.CONDITION_DESCRIPTION IS NULL)
306 AND (P_CONDITION_DESCRIPTION IS NULL)))
307 AND ( (Recinfo.CONDITION_MET_FLAG = P_CONDITION_MET_FLAG)
308 OR ( (Recinfo.CONDITION_MET_FLAG IS NULL)
309 AND (P_CONDITION_MET_FLAG IS NULL)))
310 AND ( (Recinfo.MANDATORY_FLAG = P_MANDATORY_FLAG)
311 OR ( (Recinfo.MANDATORY_FLAG IS NULL)
312 AND (P_MANDATORY_FLAG IS NULL)))
313 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
314 OR ( (Recinfo.CREATED_BY IS NULL)
315 AND (P_CREATED_BY IS NULL)))
316 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
317 OR ( (Recinfo.CREATION_DATE IS NULL)
318 AND (P_CREATION_DATE IS NULL)))
319 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
320 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
321 AND (P_LAST_UPDATED_BY IS NULL)))
322 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
323 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
324 AND (P_LAST_UPDATE_DATE IS NULL)))
325 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
326 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
327 AND (P_LAST_UPDATE_LOGIN IS NULL)))
328 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
329 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
330 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
331 AND ( (Recinfo.FULFILLMENT_DATE = P_FULFILLMENT_DATE)
332 OR ( (Recinfo.FULFILLMENT_DATE IS NULL)
333 AND (P_FULFILLMENT_DATE IS NULL)))
334 AND ( (Recinfo.FULFILLMENT_UPDATED_BY = P_FULFILLMENT_UPDATED_BY)
335 OR ( (Recinfo.FULFILLMENT_UPDATED_BY IS NULL)
336 AND (P_FULFILLMENT_UPDATED_BY IS NULL)))
337 AND ( (Recinfo.START_DATE_ACTIVE = P_START_DATE_ACTIVE)
338 OR ( (Recinfo.START_DATE_ACTIVE IS NULL)
339 AND (P_START_DATE_ACTIVE IS NULL)))
340 AND ( (Recinfo.END_DATE_ACTIVE = P_END_DATE_ACTIVE)
341 OR ( (Recinfo.END_DATE_ACTIVE IS NULL)
342 AND (P_END_DATE_ACTIVE IS NULL)))
343 AND ( (Recinfo.DISB_HEADER_ID = P_DISB_HEADER_ID)
344 OR ( (Recinfo.DISB_HEADER_ID IS NULL)
345 AND (P_DISB_HEADER_ID IS NULL)))
346 AND ( (Recinfo.DELETE_DISABLED_FLAG = P_DELETE_DISABLED_FLAG)
347 OR ( (Recinfo.DELETE_DISABLED_FLAG IS NULL)
348 AND (P_DELETE_DISABLED_FLAG IS NULL)))
349 AND ( (Recinfo.OWNER_OBJECT_ID = P_OWNER_OBJECT_ID)
350 OR ( (Recinfo.OWNER_OBJECT_ID IS NULL)
351 AND (P_OWNER_OBJECT_ID IS NULL)))
352 AND ( (Recinfo.OWNER_TABLE = P_OWNER_TABLE)
353 OR ( (Recinfo.OWNER_TABLE IS NULL)
354 AND (P_OWNER_TABLE IS NULL)))
355 ) THEN
356 return;
357 ELSE
358 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
359 APP_EXCEPTION.Raise_Exception;
360 END IF;
361
362 FOR tlinfo in C_TL LOOP
363 IF (tlinfo.BASELANG = 'Y') THEN
364 if ((tlinfo.CONDITION_DESCRIPTION = P_CONDITION_DESCRIPTION)
365 OR ((tlinfo.CONDITION_DESCRIPTION is null) AND (P_CONDITION_DESCRIPTION is null)))
366 THEN
367 null;
368 ELSE
369 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
370 APP_EXCEPTION.Raise_Exception;
371 END IF;
372 END IF;
373 END LOOP;
374
375 END Lock_Row;
376
377 procedure ADD_LANGUAGE
378 is
379 begin
380 delete from LNS_COND_ASSIGNMENTS_TL T
381 where not exists
382 (select NULL
383 from LNS_COND_ASSIGNMENTS B
384 where B.COND_ASSIGNMENT_ID = T.COND_ASSIGNMENT_ID
385 );
386
387 update LNS_COND_ASSIGNMENTS_TL T set (
388 CONDITION_DESCRIPTION
389 ) = (select
390 B.CONDITION_DESCRIPTION
391 from LNS_COND_ASSIGNMENTS_TL B
392 where B.COND_ASSIGNMENT_ID = T.COND_ASSIGNMENT_ID
393 and B.LANGUAGE = T.SOURCE_LANG)
394 where (
395 T.COND_ASSIGNMENT_ID,
396 T.LANGUAGE
397 ) in (select
398 SUBT.COND_ASSIGNMENT_ID,
399 SUBT.LANGUAGE
400 from LNS_COND_ASSIGNMENTS_TL SUBB, LNS_COND_ASSIGNMENTS_TL SUBT
401 where SUBB.COND_ASSIGNMENT_ID = SUBT.COND_ASSIGNMENT_ID
402 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
403 and (SUBB.CONDITION_DESCRIPTION <> SUBT.CONDITION_DESCRIPTION
404 or (SUBB.CONDITION_DESCRIPTION is null and SUBT.CONDITION_DESCRIPTION is not null)
405 or (SUBB.CONDITION_DESCRIPTION is not null and SUBT.CONDITION_DESCRIPTION is null)
406 ));
407
408 insert into LNS_COND_ASSIGNMENTS_TL (
409 COND_ASSIGNMENT_ID,
410 CONDITION_DESCRIPTION,
411 LAST_UPDATE_DATE,
412 LAST_UPDATED_BY,
413 CREATION_DATE,
414 CREATED_BY,
415 LAST_UPDATE_LOGIN,
416 LANGUAGE,
417 SOURCE_LANG
418 ) select
419 B.COND_ASSIGNMENT_ID,
420 CONDITION_DESCRIPTION,
421 B.LAST_UPDATE_DATE,
422 B.LAST_UPDATED_BY,
423 B.CREATION_DATE,
424 B.CREATED_BY,
425 B.LAST_UPDATE_LOGIN,
426 L.LANGUAGE_CODE,
427 B.SOURCE_LANG
428 from LNS_COND_ASSIGNMENTS_TL B, FND_LANGUAGES L
429 where L.INSTALLED_FLAG in ('I', 'B')
430 and B.LANGUAGE = userenv('LANG')
431 and not exists
432 (select NULL
433 from LNS_COND_ASSIGNMENTS_TL T
434 where T.COND_ASSIGNMENT_ID = B.COND_ASSIGNMENT_ID
435 and T.LANGUAGE = L.LANGUAGE_CODE);
436
437 end ADD_LANGUAGE;
438
439 END;
440