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