[Home] [Help]
PACKAGE BODY: APPS.IEX_CASES_PKG
Source
1 package body IEX_CASES_PKG as
2 /* $Header: iextcasb.pls 120.0 2004/01/24 03:21:15 appldev noship $ */
3 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
4 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 procedure INSERT_ROW (
7 X_ROWID in out NOCOPY VARCHAR2,
8 X_CAS_ID in NUMBER,
9 X_CASE_NUMBER in VARCHAR2,
10 X_ACTIVE_FLAG in VARCHAR2,
11 X_PARTY_ID in NUMBER,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_CASE_ESTABLISHED_DATE in DATE,
14 X_CASE_CLOSING_DATE in DATE,
15 X_ORIG_CAS_ID in NUMBER,
16 X_CASE_STATE in VARCHAR2,
17 X_STATUS_CODE in VARCHAR2,
18 X_CLOSE_REASON in VARCHAR2,
19 X_ORG_ID in NUMBER,
20 X_OWNER_RESOURCE_ID in NUMBER,
21 X_ACCESS_RESOURCE_ID in NUMBER,
22 X_COMMENTS in VARCHAR2,
23 X_PREDICTED_RECOVERY_AMOUNT in NUMBER,
24 X_PREDICTED_CHANCE in NUMBER,
25 X_REQUEST_ID in NUMBER,
26 X_PROGRAM_APPLICATION_ID in NUMBER,
27 X_PROGRAM_ID in NUMBER,
28 X_PROGRAM_UPDATE_DATE in DATE,
29 X_ATTRIBUTE_CATEGORY in VARCHAR2,
30 X_ATTRIBUTE1 in VARCHAR2,
31 X_ATTRIBUTE2 in VARCHAR2,
32 X_ATTRIBUTE3 in VARCHAR2,
33 X_ATTRIBUTE4 in VARCHAR2,
34 X_ATTRIBUTE5 in VARCHAR2,
35 X_ATTRIBUTE6 in VARCHAR2,
36 X_ATTRIBUTE7 in VARCHAR2,
37 X_ATTRIBUTE8 in VARCHAR2,
38 X_ATTRIBUTE9 in VARCHAR2,
39 X_ATTRIBUTE10 in VARCHAR2,
40 X_ATTRIBUTE11 in VARCHAR2,
41 X_ATTRIBUTE12 in VARCHAR2,
42 X_ATTRIBUTE13 in VARCHAR2,
43 X_ATTRIBUTE14 in VARCHAR2,
44 X_ATTRIBUTE15 in VARCHAR2,
45 X_CREATION_DATE in DATE,
46 X_CREATED_BY in NUMBER,
47 X_LAST_UPDATE_DATE in DATE,
48 X_LAST_UPDATED_BY in NUMBER,
49 X_LAST_UPDATE_LOGIN in NUMBER
50 ) is
51 cursor C is select ROWID from IEX_CASES_ALL_B
52 where CAS_ID = X_CAS_ID ;
53
54
55 BEGIN
56 -- IF PG_DEBUG < 10 THEN
57 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
58 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PKG.INSERT_ROW ******** ');
59 END IF;
60 insert into IEX_CASES_ALL_B (
61 CAS_ID,
62 CASE_NUMBER,
63 ACTIVE_FLAG,
64 PARTY_ID,
65 ORIG_CAS_ID,
66 CASE_STATE,
67 STATUS_CODE,
68 OBJECT_VERSION_NUMBER,
69 CASE_ESTABLISHED_DATE,
70 CASE_CLOSING_DATE,
71 OWNER_RESOURCE_ID,
72 ACCESS_RESOURCE_ID,
73 PREDICTED_RECOVERY_AMOUNT,
74 PREDICTED_CHANCE,
75 REQUEST_ID,
76 PROGRAM_APPLICATION_ID,
77 PROGRAM_ID,
78 PROGRAM_UPDATE_DATE,
79 ATTRIBUTE_CATEGORY,
80 ATTRIBUTE1,
81 ATTRIBUTE2,
82 ATTRIBUTE3,
83 ATTRIBUTE4,
84 ATTRIBUTE5,
85 ATTRIBUTE6,
86 ATTRIBUTE7,
87 ATTRIBUTE8,
88 ATTRIBUTE9,
89 ATTRIBUTE10,
90 ATTRIBUTE11,
91 ATTRIBUTE12,
92 ATTRIBUTE13,
93 ATTRIBUTE14,
94 ATTRIBUTE15,
95 CREATED_BY,
96 CREATION_DATE,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_DATE,
99 LAST_UPDATE_LOGIN,
100 ORG_ID,
101 CLOSE_REASON
102 ) values
103 (
104 X_CAS_ID,
105 X_CASE_NUMBER,
106 X_ACTIVE_FLAG,
107 X_PARTY_ID,
108 decode( X_ORIG_CAS_ID, FND_API.G_MISS_NUM, NULL, X_ORIG_CAS_ID),
109 X_CASE_STATE,
110 X_STATUS_CODE,
111 x_OBJECT_VERSION_NUMBER,
112 x_CASE_ESTABLISHED_DATE,
113 decode( x_CASE_CLOSING_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_CASE_CLOSING_DATE),
114 decode( x_OWNER_RESOURCE_ID, FND_API.G_MISS_NUM, NULL, x_OWNER_RESOURCE_ID),
115 decode( x_ACCESS_RESOURCE_ID, FND_API.G_MISS_NUM, NULL, x_ACCESS_RESOURCE_ID),
116 decode( x_PREDICTED_RECOVERY_AMOUNT, FND_API.G_MISS_NUM, NULL, x_PREDICTED_RECOVERY_AMOUNT),
117 decode( x_PREDICTED_CHANCE, FND_API.G_MISS_NUM, NULL, x_PREDICTED_CHANCE),
118 decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL, x_REQUEST_ID),
119 decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, x_PROGRAM_APPLICATION_ID),
120 decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, x_PROGRAM_ID),
121 decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_PROGRAM_UPDATE_DATE),
122 decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE_CATEGORY),
123 decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE1),
124 decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE2),
125 decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE3),
126 decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE4),
127 decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE5),
128 decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE6),
129 decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE7),
130 decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE8),
131 decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE9),
132 decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE10),
133 decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE11),
134 decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE12),
135 decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE13),
136 decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE14),
137 decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, x_ATTRIBUTE15),
138 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL, x_CREATED_BY),
139 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_CREATION_DATE),
140 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATED_BY),
141 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_LAST_UPDATE_DATE),
142 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATE_LOGIN),
143 X_ORG_ID,
144 decode( x_CLOSE_REASON, FND_API.G_MISS_CHAR, NULL, x_CLOSE_REASON));
145 -- IF PG_DEBUG < 10 THEN
146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
147 IEX_DEBUG_PUB.LogMessage ('INSERT_ROW: ' || 'After iex_case_All_b Insert and before iex_cases_tl insert');
148 END IF;
149
150 insert into IEX_CASES_TL (
151 CAS_ID,
152 COMMENTS,
153 CREATED_BY,
154 CREATION_DATE,
155 LAST_UPDATED_BY,
156 LAST_UPDATE_DATE,
157 LAST_UPDATE_LOGIN,
158 LANGUAGE,
159 SOURCE_LANG,
160 ACTIVE_FLAG
161 ) select
162 X_CAS_ID,
163 decode( x_COMMENTS, FND_API.G_MISS_CHAR, NULL, x_COMMENTS),
164 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL, x_CREATED_BY),
165 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_CREATION_DATE),
166 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATED_BY),
167 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_LAST_UPDATE_DATE),
168 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATE_LOGIN),
169 L.LANGUAGE_CODE,
170 userenv('LANG'),
171 X_ACTIVE_FLAG
172 from FND_LANGUAGES L
173 where L.INSTALLED_FLAG in ('I', 'B')
174 and not exists
175 ( select NULL
176 from iex_cases_tl T
177 where T.cas_id = X_cas_id
178 and T.active_flag = X_active_flag
179 and T.LANGUAGE = L.LANGUAGE_CODE);
180
181 -- IF PG_DEBUG < 10 THEN
182 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
183 IEX_DEBUG_PUB.LogMessage ('INSERT_ROW: ' || 'After iex_cases_tl insert');
184 END IF;
185 open c;
186 fetch c into X_ROWID;
187 -- IF PG_DEBUG < 10 THEN
188 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
189 IEX_DEBUG_PUB.LogMessage ('INSERT_ROW: ' || 'Value of ROWID = '||X_ROWID);
190 END IF;
191 if (c%notfound) then
192 close c;
193 raise no_data_found;
194 end if;
195 close c;
196 --IF PG_DEBUG < 10 THEN
197 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
198 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PKG.INSERT_ROW ******** ');
199 END IF;
200 end INSERT_ROW;
201
202 procedure LOCK_ROW (
203 X_CAS_ID in NUMBER,
204 X_OBJECT_VERSION_NUMBER in NUMBER
205 ) is
206 cursor c is select OBJECT_VERSION_NUMBER
207 from IEX_CASES_ALL_B
208 where CAS_ID = X_CAS_ID
209 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
210 for update of CAS_ID nowait;
211 recinfo c%rowtype;
212
213
214 begin
215 -- IF PG_DEBUG < 10 THEN
216 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
217 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PKG.LOCK_ROW ******** ');
218 END IF;
219 open c;
220 fetch c into recinfo;
221 if (c%notfound) then
222 close c;
223 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
224 app_exception.raise_exception;
225 end if;
226
227 close c;
228
229 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
230 null;
231 else
232 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
233 app_exception.raise_exception;
234 end if;
235 --IF PG_DEBUG < 10 THEN
236 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
237 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PKG.LOCK_ROW ******** ');
238 END IF;
239 end LOCK_ROW;
240
241 /*procedure LOCK_ROW (
242 X_CAS_ID in NUMBER,
243 X_CASE_NUMBER in VARCHAR2,
244 X_ACTIVE_FLAG in VARCHAR2,
245 X_PARTY_ID in NUMBER,
246 X_OBJECT_VERSION_NUMBER in NUMBER,
247 X_CASE_ESTABLISHED_DATE in DATE,
248 X_CASE_CLOSING_DATE in DATE,
249 X_STATUS_CODE in VARCHAR2,
250 X_CLOSE_REASON in VARCHAR2,
251 X_ORG_ID in NUMBER,
252 X_OWNER_RESOURCE_ID in NUMBER,
253 X_ACCESS_RESOURCE_ID in NUMBER,
254 X_COMMENTS in VARCHAR2,
255 X_PREDICTED_RECOVERY_AMOUNT in NUMBER,
256 X_PREDICTED_CHANCE in NUMBER,
257 X_REQUEST_ID in NUMBER,
258 X_PROGRAM_APPLICATION_ID in NUMBER,
259 X_PROGRAM_ID in NUMBER,
260 X_PROGRAM_UPDATE_DATE in DATE,
261 X_ATTRIBUTE_CATEGORY in VARCHAR2,
262 X_ATTRIBUTE1 in VARCHAR2,
263 X_ATTRIBUTE2 in VARCHAR2,
264 X_ATTRIBUTE3 in VARCHAR2,
265 X_ATTRIBUTE4 in VARCHAR2,
266 X_ATTRIBUTE5 in VARCHAR2,
267 X_ATTRIBUTE6 in VARCHAR2,
268 X_ATTRIBUTE7 in VARCHAR2,
269 X_ATTRIBUTE8 in VARCHAR2,
270 X_ATTRIBUTE9 in VARCHAR2,
271 X_ATTRIBUTE10 in VARCHAR2,
272 X_ATTRIBUTE11 in VARCHAR2,
273 X_ATTRIBUTE12 in VARCHAR2,
274 X_ATTRIBUTE13 in VARCHAR2,
275 X_ATTRIBUTE14 in VARCHAR2,
276 X_ATTRIBUTE15 in VARCHAR2
277 ) is
278 cursor c is select
279 CAS_ID ,
280 CASE_NUMBER ,
281 ACTIVE_FLAG ,
282 PARTY_ID ,
283 OBJECT_VERSION_NUMBER,
284 CASE_ESTABLISHED_DATE,
285 CASE_CLOSING_DATE ,
286 STATUS_CODE ,
287 CLOSE_REASON ,
288 ORG_ID ,
289 OWNER_RESOURCE_ID ,
290 ACCESS_RESOURCE_ID ,
291 PREDICTED_RECOVERY_AMOUNT,
292 PREDICTED_CHANCE,
293 REQUEST_ID ,
294 PROGRAM_APPLICATION_ID,
295 PROGRAM_ID ,
296 PROGRAM_UPDATE_DATE ,
297 ATTRIBUTE_CATEGORY ,
298 ATTRIBUTE1 ,
299 ATTRIBUTE2 ,
300 ATTRIBUTE3 ,
301 ATTRIBUTE4 ,
302 ATTRIBUTE5 ,
303 ATTRIBUTE6 ,
304 ATTRIBUTE7 ,
305 ATTRIBUTE8 ,
306 ATTRIBUTE9 ,
307 ATTRIBUTE10 ,
308 ATTRIBUTE11 ,
309 ATTRIBUTE12 ,
310 ATTRIBUTE13 ,
311 ATTRIBUTE14 ,
312 ATTRIBUTE15
313 from IEX_CASES_ALL_B
314 where CAS_ID = X_CAS_ID
315 for update of CAS_ID nowait;
316 recinfo c%rowtype;
317
318 cursor c1 is select Comments,
319 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
320 from IEX_CASES_TL
321 where CAS_ID = X_CAS_ID
322 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
323 for update of CAS_ID nowait;
324 begin
325 open c;
326 fetch c into recinfo;
327 if (c%notfound) then
328 close c;
329 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
330 app_exception.raise_exception;
331 end if;
332 close c;
333 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
334 AND(recinfo.CASE_NUMBER = X_CASE_NUMBER)
335 AND(recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
336 AND(recinfo.STATUS_CODE = X_STATUS_CODE)
337 AND(recinfo.ORG_ID = X_ORG_ID)
338 AND(recinfo.PARTY_ID = X_PARTY_ID)
339
340 AND ((recinfo.CASE_ESTABLISHED_DATE = X_CASE_ESTABLISHED_DATE)
341 OR ((recinfo.CASE_ESTABLISHED_DATE is null) AND (X_CASE_ESTABLISHED_DATE is null)))
342 AND ((recinfo.CASE_CLOSING_DATE = X_CASE_CLOSING_DATE)
343 OR ((recinfo.CASE_CLOSING_DATE is null) AND (X_CASE_CLOSING_DATE is null)))
344 AND ((recinfo.CLOSE_REASON = X_CLOSE_REASON)
345 OR ((recinfo.CLOSE_REASON is null) AND (X_CLOSE_REASON is null)))
346 AND ((recinfo.OWNER_RESOURCE_ID = X_OWNER_RESOURCE_ID)
347 OR ((recinfo.OWNER_RESOURCE_ID is null) AND (X_OWNER_RESOURCE_ID is null)))
348 AND ((recinfo.ACCESS_RESOURCE_ID = X_ACCESS_RESOURCE_ID)
349 OR ((recinfo.ACCESS_RESOURCE_ID is null) AND (X_ACCESS_RESOURCE_ID is null)))
350 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
351 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
352 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
353 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
354 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID)
355 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
356 AND ((recinfo. PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
357 OR ((recinfo.PROGRAM_UPDATE_DATE is null) AND (X_PROGRAM_UPDATE_DATE is null)))
358 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
359 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
360 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
361 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
362 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
363 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
364 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
365 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
366 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
367 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
368 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
369 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
370 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
371 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
372 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
373 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
374 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
375 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
376 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
377 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
378 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
379 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
380 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
381 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
382 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
383 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
384 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
385 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
386 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
387 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
388 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
389 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
390 AND (recinfo.CAS_ID = X_CAS_ID)
391 ) then
392 null;
393 else
394 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
395 app_exception.raise_exception;
396 end if;
397 for tlinfo in c1 loop
398 if (tlinfo.BASELANG = 'Y') then
399 if ( ((tlinfo.COMMENTS = X_COMMENTS)
400 OR ((tlinfo.COMMENTS IS NULL) AND (X_COMMENTS IS NULL)))
401 ) THEN
402 NULL;
403 else
404 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
405 app_exception.raise_exception;
406 end if;
407 end if;
408 end loop;
409 return;
410 end LOCK_ROW;
411 */
412
413 procedure UPDATE_ROW (
414 X_CAS_ID in NUMBER,
415 X_CASE_NUMBER in VARCHAR2,
416 X_ACTIVE_FLAG in VARCHAR2,
417 X_PARTY_ID in NUMBER,
418 X_OBJECT_VERSION_NUMBER in NUMBER,
419 X_CASE_ESTABLISHED_DATE in DATE,
420 X_CASE_CLOSING_DATE in DATE,
421 X_ORIG_CAS_ID in NUMBER,
422 X_CASE_STATE in VARCHAR2,
423 X_STATUS_CODE in VARCHAR2,
424 X_CLOSE_REASON in VARCHAR2,
425 X_ORG_ID in NUMBER,
426 X_OWNER_RESOURCE_ID in NUMBER,
427 X_ACCESS_RESOURCE_ID in NUMBER,
428 X_COMMENTS in VARCHAR2,
429 X_PREDICTED_RECOVERY_AMOUNT in NUMBER,
430 X_PREDICTED_CHANCE in NUMBER,
431 X_REQUEST_ID in NUMBER,
432 X_PROGRAM_APPLICATION_ID in NUMBER,
433 X_PROGRAM_ID in NUMBER,
434 X_PROGRAM_UPDATE_DATE in DATE,
435 X_ATTRIBUTE_CATEGORY in VARCHAR2,
436 X_ATTRIBUTE1 in VARCHAR2,
437 X_ATTRIBUTE2 in VARCHAR2,
438 X_ATTRIBUTE3 in VARCHAR2,
439 X_ATTRIBUTE4 in VARCHAR2,
440 X_ATTRIBUTE5 in VARCHAR2,
441 X_ATTRIBUTE6 in VARCHAR2,
442 X_ATTRIBUTE7 in VARCHAR2,
443 X_ATTRIBUTE8 in VARCHAR2,
444 X_ATTRIBUTE9 in VARCHAR2,
445 X_ATTRIBUTE10 in VARCHAR2,
446 X_ATTRIBUTE11 in VARCHAR2,
447 X_ATTRIBUTE12 in VARCHAR2,
448 X_ATTRIBUTE13 in VARCHAR2,
449 X_ATTRIBUTE14 in VARCHAR2,
450 X_ATTRIBUTE15 in VARCHAR2,
451 X_LAST_UPDATE_DATE in DATE,
452 X_LAST_UPDATED_BY in NUMBER,
453 X_LAST_UPDATE_LOGIN in NUMBER) is
454 begin
455 -- IF PG_DEBUG < 10 THEN
456 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
457 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PKG.UPDATE_ROW ******** ');
458 END IF;
459 update IEX_CASES_ALL_B set
460 CASE_NUMBER = decode( x_CASE_NUMBER, FND_API.G_MISS_CHAR, CASE_NUMBER, x_CASE_NUMBER),
461 ACTIVE_FLAG = decode( x_ACTIVE_FLAG, FND_API.G_MISS_CHAR, ACTIVE_FLAG, x_ACTIVE_FLAG),
462 PARTY_ID = decode( X_PARTY_ID, FND_API.G_MISS_NUM, PARTY_ID, X_PARTY_ID),
463 ORIG_CAS_ID = decode( X_ORIG_CAS_ID, FND_API.G_MISS_NUM, ORIG_CAS_ID, X_ORIG_CAS_ID),
464 CASE_STATE = decode( x_CASE_STATE, FND_API.G_MISS_CHAR, CASE_STATE, x_CASE_STATE),
465 STATUS_CODE = decode( x_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, x_STATUS_CODE),
466 OBJECT_VERSION_NUMBER = decode( x_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, x_OBJECT_VERSION_NUMBER),
467 CASE_ESTABLISHED_DATE = decode( x_CASE_ESTABLISHED_DATE, FND_API.G_MISS_DATE, CASE_ESTABLISHED_DATE, x_CASE_ESTABLISHED_DATE),
468 CASE_CLOSING_DATE = decode( x_CASE_CLOSING_DATE, FND_API.G_MISS_DATE, CASE_CLOSING_DATE, x_CASE_CLOSING_DATE),
469 OWNER_RESOURCE_ID = decode( x_OWNER_RESOURCE_ID, FND_API.G_MISS_NUM, OWNER_RESOURCE_ID, x_OWNER_RESOURCE_ID),
470 ACCESS_RESOURCE_ID = decode( x_ACCESS_RESOURCE_ID, FND_API.G_MISS_NUM, ACCESS_RESOURCE_ID, x_ACCESS_RESOURCE_ID),
471 PREDICTED_RECOVERY_AMOUNT = decode( x_PREDICTED_RECOVERY_AMOUNT, FND_API.G_MISS_NUM, PREDICTED_RECOVERY_AMOUNT, x_PREDICTED_RECOVERY_AMOUNT),
472 PREDICTED_CHANCE = decode( x_PREDICTED_CHANCE, FND_API.G_MISS_NUM, PREDICTED_CHANCE, x_PREDICTED_CHANCE),
473 REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, x_REQUEST_ID),
474 PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, x_PROGRAM_APPLICATION_ID),
475 PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, x_PROGRAM_ID),
476 PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, x_PROGRAM_UPDATE_DATE),
477 ATTRIBUTE_CATEGORY = decode( x_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, x_ATTRIBUTE_CATEGORY),
478 ATTRIBUTE1 = decode( x_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, x_ATTRIBUTE1),
479 ATTRIBUTE2 = decode( x_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, x_ATTRIBUTE2),
480 ATTRIBUTE3 = decode( x_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, x_ATTRIBUTE3),
481 ATTRIBUTE4 = decode( x_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, x_ATTRIBUTE4),
482 ATTRIBUTE5 = decode( x_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, x_ATTRIBUTE5),
483 ATTRIBUTE6 = decode( x_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, x_ATTRIBUTE6),
484 ATTRIBUTE7 = decode( x_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, x_ATTRIBUTE7),
485 ATTRIBUTE8 = decode( x_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, x_ATTRIBUTE8),
486 ATTRIBUTE9 = decode( x_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, x_ATTRIBUTE9),
487 ATTRIBUTE10 = decode( x_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, x_ATTRIBUTE10),
488 ATTRIBUTE11 = decode( x_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, x_ATTRIBUTE11),
489 ATTRIBUTE12 = decode( x_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, x_ATTRIBUTE12),
490 ATTRIBUTE13 = decode( x_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, x_ATTRIBUTE13),
491 ATTRIBUTE14 = decode( x_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, x_ATTRIBUTE14),
492 ATTRIBUTE15 = decode( x_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, x_ATTRIBUTE15),
493 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, x_LAST_UPDATED_BY),
494 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, x_LAST_UPDATE_DATE),
495 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, x_LAST_UPDATE_LOGIN),
496 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, x_ORG_ID),
497 CLOSE_REASON = decode( x_CLOSE_REASON, FND_API.G_MISS_CHAR, CLOSE_REASON, x_CLOSE_REASON)
498 where CAS_ID = X_CAS_ID;
499
500 if (sql%notfound) then
501 raise no_data_found;
502 end if;
503
504 update IEX_CASES_TL set
505 COMMENTS = decode( x_COMMENTS, FND_API.G_MISS_CHAR, COMMENTS, x_COMMENTS),
506 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, x_LAST_UPDATED_BY),
507 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, x_LAST_UPDATE_DATE),
508 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, x_LAST_UPDATE_LOGIN),
509 SOURCE_LANG = userenv('LANG')
510 where CAS_ID = X_CAS_ID
511 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
512
513 if (sql%notfound) then
514 raise no_data_found;
515 end if;
516 -- IF PG_DEBUG < 10 THEN
517 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
518 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PKG.UPDATE_ROW ******** ');
519 END IF;
520 end UPDATE_ROW;
521
522 procedure DELETE_ROW (
523 X_CAS_ID in NUMBER
524 ) is
525 begin
526 -- IF PG_DEBUG < 10 THEN
527 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
528 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PKG.DELETE_ROW ******** ');
529 END IF;
530 delete from IEX_CASES_TL
531 where CAS_ID = X_CAS_ID;
532
533 if (sql%notfound) then
534 raise no_data_found;
535 end if;
536
537 delete from IEX_CASES_ALL_B
538 where CAS_ID = X_CAS_ID;
539
540 if (sql%notfound) then
541 raise no_data_found;
542 end if;
543 -- IF PG_DEBUG < 10 THEN
544 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
545 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PKG.DELETE_ROW ******** ');
546 END IF;
547 end DELETE_ROW;
548
549 procedure ADD_LANGUAGE
550 is
551 begin
552 delete from IEX_CASES_TL T
553 where not exists
554 (select NULL
555 from IEX_CASES_ALL_B B
556 where B.CAS_ID = T.CAS_ID
557 );
558
559 update IEX_CASES_TL T
560 set (COMMENTS) =
561 (select B.COMMENTS
562 from IEX_CASES_TL B
563 where B.CAS_ID = T.CAS_ID
564 and B.LANGUAGE = T.SOURCE_LANG)
565 where (
566 T.CAS_ID,T.LANGUAGE
567 ) in (select
568 SUBT.CAS_ID,
569 SUBT.LANGUAGE
570 from IEX_CASES_TL SUBB,
571 IEX_CASES_TL SUBT
572 where SUBB.CAS_ID = SUBT.CAS_ID
573 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
574 and SUBB.COMMENTS<> SUBT.COMMENTS
575 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
576 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
577 );
578
579 insert into IEX_CASES_TL (
580 CAS_ID,
581 COMMENTS,
582 LAST_UPDATE_DATE,
583 LAST_UPDATED_BY,
584 CREATION_DATE,
585 CREATED_BY,
586 LAST_UPDATE_LOGIN,
587 LANGUAGE,
588 SOURCE_LANG,
589 ACTIVE_FLAG
590 ) select
591 B.CAS_ID,
592 B.COMMENTS,
593 B.LAST_UPDATE_DATE,
594 B.LAST_UPDATED_BY,
595 B.CREATION_DATE,
596 B.CREATED_BY,
597 B.LAST_UPDATE_LOGIN,
598 L.LANGUAGE_CODE,
599 B.SOURCE_LANG,
600 B.ACTIVE_FLAG
601 from IEX_CASES_TL B, FND_LANGUAGES L
602 where L.INSTALLED_FLAG in ('I', 'B')
603 and B.LANGUAGE = userenv('LANG')
604 and not exists
605 (select NULL
606 from IEX_CASES_TL T
607 where T.CAS_ID = B.CAS_ID
608 and T.LANGUAGE = L.LANGUAGE_CODE);
609
610 end ADD_LANGUAGE;
611
612
613 procedure TRANSLATE_ROW (
614 X_CAS_ID in NUMBER,
615 X_COMMENTS in VARCHAR2,
616 X_OBJECT_VERSION_NUMBER in NUMBER,
617 X_OWNER in VARCHAR2
618 ) IS
619
620 begin
621 UPDATE IEX_CASES_tl SET
622 comments=X_COMMENTS,
623 last_update_date = sysdate,
624 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
625 last_update_login = 0,
626 source_lang = userenv('LANG')
627 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
628 CAS_id = X_CAS_ID;
629 end TRANSLATE_ROW;
630
631 end IEX_CASES_PKG;