1 PACKAGE BODY IEX_CASE_UTL_PUB as
2 /* $Header: iexucasb.pls 120.3.12010000.3 2008/08/20 10:51:05 snuthala ship $ */
3 -- Start of Comments
4 -- Package name : IEX_CASE_UTL_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(100):= 'IEX_CASE_UTL_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexucasb.pls';
12
13
14 /**Name AddInvalidArgMsg
15 **Appends to a message the api name, parameter name and parameter Value
16 */
17
18 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
19 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
20
21 PROCEDURE AddInvalidArgMsg
22 ( p_api_name IN VARCHAR2,
23 p_param_value IN VARCHAR2,
24 p_param_name IN VARCHAR2 ) IS
25 BEGIN
26 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
27 fnd_message.set_name('IEX', 'IEX_API_ALL_INVALID_ARGUMENT');
28 fnd_message.set_token('API_NAME', p_api_name);
29 fnd_message.set_token('VALUE', p_param_value);
30 fnd_message.set_token('PARAMETER', p_param_name);
31 fnd_msg_pub.add;
32 END IF;
33
34
35 END AddInvalidArgMsg;
36
37 /**Name AddMissingArgMsg
38 **Appends to a message the api name, parameter name and parameter Value
39 */
40
41 PROCEDURE AddMissingArgMsg
42 ( p_api_name IN VARCHAR2,
43 p_param_name IN VARCHAR2 )IS
44 BEGIN
45 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
46 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
47 fnd_message.set_token('API_NAME', p_api_name);
48 fnd_message.set_token('MISSING_PARAM', p_param_name);
49 fnd_msg_pub.add;
50 END IF;
51 END AddMissingArgMsg;
52
53 /**Name AddNullArgMsg
54 **Appends to a message the api name, parameter name and parameter Value
55 */
56
57 PROCEDURE AddNullArgMsg
58 ( p_api_name IN VARCHAR2,
59 p_param_name IN VARCHAR2 )IS
60 BEGIN
61 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
62 fnd_message.set_name('IEX', 'IEX_API_ALL_NULL_PARAMETER');
63 fnd_message.set_token('API_NAME', p_api_name);
64 fnd_message.set_token('NULL_PARAM', p_param_name);
65 fnd_msg_pub.add;
66 END IF;
67
68
69 END AddNullArgMsg;
70
71 /**Name AddFailMsg
72 **Appends to a message the name of the object anf the operation (insert, update ,delete)
73 */
74 PROCEDURE AddfailMsg
75 ( p_object IN VARCHAR2,
76 p_operation IN VARCHAR2 ) IS
77
78 BEGIN
79 fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
80 fnd_message.set_token('OBJECT', p_object);
81 fnd_message.set_token('OPERATION', p_operation);
82 fnd_msg_pub.add;
83
84 END AddfailMsg;
85
86 /**Name Converts Case record type from public to PVT **/
87 Procedure Convert_case_Record (
88 p_cas_rec IN iex_case_utl_pub.cas_Rec_Type,
89 x_cas_rec OUT NOCOPY iex_cases_pvt.cas_Rec_Type) IS
90 Begin
91 -- IF PG_DEBUG < 10 THEN
92 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
93 iex_debug_pub.LogMessage('Convert_case_Record: ' || '*********Start of Convert Case record *********');
94 END IF;
95 x_cas_rec.CAS_ID := p_cas_rec.CAS_ID;
96 x_cas_rec.CASE_NUMBER := p_cas_rec.CASE_NUMBER;
97 x_cas_rec.ACTIVE_FLAG := p_cas_rec.ACTIVE_FLAG ;
98 x_cas_rec.PARTY_ID := p_cas_rec.party_id;
99 x_cas_rec.ORIG_CAS_ID := p_cas_rec.orig_cas_id;
100 x_cas_rec.CASE_STATE := p_cas_rec.CASE_STATE;
101 x_cas_rec.STATUS_CODE := p_cas_rec.STATUS_CODE;
102 x_cas_rec.OBJECT_VERSION_NUMBER := p_cas_rec.OBJECT_VERSION_NUMBER;
103 x_cas_rec.CASE_ESTABLISHED_DATE := p_cas_rec.CASE_ESTABLISHED_DATE;
104 x_cas_rec.CASE_CLOSING_DATE := p_cas_rec.CASE_CLOSING_DATE;
105 x_cas_rec.OWNER_RESOURCE_ID := p_cas_rec.OWNER_RESOURCE_ID;
106 x_cas_rec.ACCESS_RESOURCE_ID := p_cas_rec.ACCESS_RESOURCE_ID;
107 x_cas_rec.COMMENTS :=P_CAS_REC.COMMENTS;
108 x_cas_rec.REQUEST_ID := p_cas_rec.REQUEST_ID;
109 x_cas_rec.PROGRAM_APPLICATION_ID := p_cas_rec.PROGRAM_APPLICATION_ID;
110 x_cas_rec.PROGRAM_ID := p_cas_rec.PROGRAM_ID;
111 x_cas_rec.PROGRAM_UPDATE_DATE := p_cas_rec.PROGRAM_UPDATE_DATE;
112 x_cas_rec.ATTRIBUTE_CATEGORY := p_cas_rec.ATTRIBUTE_CATEGORY;
113 x_cas_rec.ATTRIBUTE1 := p_cas_rec.ATTRIBUTE1;
114 x_cas_rec.ATTRIBUTE2 := p_cas_rec.ATTRIBUTE2;
115 x_cas_rec.ATTRIBUTE3 := p_cas_rec.ATTRIBUTE3;
116 x_cas_rec.ATTRIBUTE4 := p_cas_rec.ATTRIBUTE4;
117 x_cas_rec.ATTRIBUTE5 := p_cas_rec.ATTRIBUTE5;
118 x_cas_rec.ATTRIBUTE6 := p_cas_rec.ATTRIBUTE6;
119 x_cas_rec.ATTRIBUTE7 := p_cas_rec.ATTRIBUTE7;
120 x_cas_rec.ATTRIBUTE8 := p_cas_rec.ATTRIBUTE8;
121 x_cas_rec.ATTRIBUTE9 := p_cas_rec.ATTRIBUTE9;
122 x_cas_rec.ATTRIBUTE10 := p_cas_rec.ATTRIBUTE10;
123 x_cas_rec.ATTRIBUTE11 := p_cas_rec.ATTRIBUTE11;
124 x_cas_rec.ATTRIBUTE12 := p_cas_rec.ATTRIBUTE12;
125 x_cas_rec.ATTRIBUTE13 := p_cas_rec.ATTRIBUTE13;
126 x_cas_rec.ATTRIBUTE14 := p_cas_rec.ATTRIBUTE14;
127 x_cas_rec.ATTRIBUTE15 := p_cas_rec.ATTRIBUTE15;
128 x_cas_rec.CREATED_BY := p_cas_rec.CREATED_BY ;
129 x_cas_rec.CREATION_DATE := p_cas_rec.CREATION_DATE;
130 x_cas_rec.LAST_UPDATED_BY := p_cas_rec.LAST_UPDATED_BY ;
131 x_cas_rec.LAST_UPDATE_DATE := p_cas_rec.LAST_UPDATE_DATE;
132 x_cas_rec.LAST_UPDATE_LOGIN := p_cas_rec.LAST_UPDATE_LOGIN;
133 x_cas_rec.PREDICTED_RECOVERY_AMOUNT := p_cas_rec.PREDICTED_RECOVERY_AMOUNT;
134 x_cas_rec.PREDICTED_CHANCE := p_cas_rec.PREDICTED_CHANCE;
135 -- IF PG_DEBUG < 10 THEN
136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
137 iex_debug_pub.LogMessage('Convert_case_Record: ' || '*********End of Convert Case record *********');
138 END IF;
139
140 end Convert_case_Record;
141
142
143 /**Name Converts Case record type from public to PVT **/
144 Procedure Convert_case_object_Record (
145 p_attribute_rec IN iex_case_utl_pub.cas_Rec_Type,
146 x_case_object_rec OUT NOCOPY iex_case_objects_pvt.case_object_Rec_Type)IS
147 Begin
148 -- IF PG_DEBUG < 10 THEN
149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
150 iex_debug_pub.LogMessage('Convert_case_object_Record: ' || '*********End of Convert case object Record record *********');
151 END IF;
152 x_case_object_rec.ACTIVE_FLAG := p_attribute_rec.ACTIVE_FLAG ;
153 x_case_object_rec.REQUEST_ID := p_attribute_rec.REQUEST_ID;
154 x_case_object_rec.PROGRAM_APPLICATION_ID := p_attribute_rec.PROGRAM_APPLICATION_ID;
155 x_case_object_rec.PROGRAM_ID := p_attribute_rec.PROGRAM_ID;
156 x_case_object_rec.PROGRAM_UPDATE_DATE := p_attribute_rec.PROGRAM_UPDATE_DATE;
157 x_case_object_rec.ATTRIBUTE_CATEGORY := p_attribute_rec.ATTRIBUTE_CATEGORY;
158 x_case_object_rec.ATTRIBUTE1 := p_attribute_rec.ATTRIBUTE1;
159 x_case_object_rec.ATTRIBUTE2 := p_attribute_rec.ATTRIBUTE2;
160 x_case_object_rec.ATTRIBUTE3 := p_attribute_rec.ATTRIBUTE3;
161 x_case_object_rec.ATTRIBUTE4 := p_attribute_rec.ATTRIBUTE4;
162 x_case_object_rec.ATTRIBUTE5 := p_attribute_rec.ATTRIBUTE5;
163 x_case_object_rec.ATTRIBUTE6 := p_attribute_rec.ATTRIBUTE6;
164 x_case_object_rec.ATTRIBUTE7 := p_attribute_rec.ATTRIBUTE7;
165 x_case_object_rec.ATTRIBUTE8 := p_attribute_rec.ATTRIBUTE8;
166 x_case_object_rec.ATTRIBUTE9 := p_attribute_rec.ATTRIBUTE9;
167 x_case_object_rec.ATTRIBUTE10 := p_attribute_rec.ATTRIBUTE10;
168 x_case_object_rec.ATTRIBUTE11 := p_attribute_rec.ATTRIBUTE11;
169 x_case_object_rec.ATTRIBUTE12 := p_attribute_rec.ATTRIBUTE12;
170 x_case_object_rec.ATTRIBUTE13 := p_attribute_rec.ATTRIBUTE13;
171 x_case_object_rec.ATTRIBUTE14 := p_attribute_rec.ATTRIBUTE14;
172 x_case_object_rec.ATTRIBUTE15 := p_attribute_rec.ATTRIBUTE15;
173 x_case_object_rec.CREATED_BY := p_attribute_rec.CREATED_BY ;
174 x_case_object_rec.CREATION_DATE := p_attribute_rec.CREATION_DATE;
175 x_case_object_rec.LAST_UPDATED_BY := p_attribute_rec.LAST_UPDATED_BY ;
176 x_case_object_rec.LAST_UPDATE_DATE := p_attribute_rec.LAST_UPDATE_DATE;
177 x_case_object_rec.LAST_UPDATE_LOGIN := p_attribute_rec.LAST_UPDATE_LOGIN;
178
179 -- IF PG_DEBUG < 10 THEN
180 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
181 iex_debug_pub.LogMessage('Convert_case_object_Record: ' || '*********Start of Convert case object Record record *********');
182 END IF;
183 end Convert_case_object_Record;
184
185 /**Name Converts Case def record type from public to PVT **/
186 Procedure Convert_case_def_Record (
187 p_attribute_rec IN iex_case_utl_pub.cas_Rec_Type,
188 x_case_def_rec OUT NOCOPY iex_case_definitions_pvt.CASE_DEFINITION_Rec_Type) IS
189 Begin
190 -- IF PG_DEBUG < 10 THEN
191 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
192 iex_debug_pub.LogMessage('Convert_case_def_Record: ' || '*********End of Convert case def Record record *********');
193 END IF;
194 x_case_def_rec.ACTIVE_FLAG := p_attribute_rec.ACTIVE_FLAG ;
195 x_case_def_rec.REQUEST_ID := p_attribute_rec.REQUEST_ID;
196 x_case_def_rec.PROGRAM_APPLICATION_ID := p_attribute_rec.PROGRAM_APPLICATION_ID;
197 x_case_def_rec.PROGRAM_ID := p_attribute_rec.PROGRAM_ID;
198 x_case_def_rec.PROGRAM_UPDATE_DATE := p_attribute_rec.PROGRAM_UPDATE_DATE;
199 x_case_def_rec.ATTRIBUTE_CATEGORY := p_attribute_rec.ATTRIBUTE_CATEGORY;
200 x_case_def_rec.ATTRIBUTE1 := p_attribute_rec.ATTRIBUTE1;
201 x_case_def_rec.ATTRIBUTE2 := p_attribute_rec.ATTRIBUTE2;
202 x_case_def_rec.ATTRIBUTE3 := p_attribute_rec.ATTRIBUTE3;
203 x_case_def_rec.ATTRIBUTE4 := p_attribute_rec.ATTRIBUTE4;
204 x_case_def_rec.ATTRIBUTE5 := p_attribute_rec.ATTRIBUTE5;
205 x_case_def_rec.ATTRIBUTE6 := p_attribute_rec.ATTRIBUTE6;
206 x_case_def_rec.ATTRIBUTE7 := p_attribute_rec.ATTRIBUTE7;
207 x_case_def_rec.ATTRIBUTE8 := p_attribute_rec.ATTRIBUTE8;
208 x_case_def_rec.ATTRIBUTE9 := p_attribute_rec.ATTRIBUTE9;
209 x_case_def_rec.ATTRIBUTE10 := p_attribute_rec.ATTRIBUTE10;
210 x_case_def_rec.ATTRIBUTE11 := p_attribute_rec.ATTRIBUTE11;
211 x_case_def_rec.ATTRIBUTE12 := p_attribute_rec.ATTRIBUTE12;
212 x_case_def_rec.ATTRIBUTE13 := p_attribute_rec.ATTRIBUTE13;
213 x_case_def_rec.ATTRIBUTE14 := p_attribute_rec.ATTRIBUTE14;
214 x_case_def_rec.ATTRIBUTE15 := p_attribute_rec.ATTRIBUTE15;
215 x_case_def_rec.CREATED_BY := p_attribute_rec.CREATED_BY ;
216 x_case_def_rec.CREATION_DATE := p_attribute_rec.CREATION_DATE;
217 x_case_def_rec.LAST_UPDATED_BY := p_attribute_rec.LAST_UPDATED_BY ;
218 x_case_def_rec.LAST_UPDATE_DATE := p_attribute_rec.LAST_UPDATE_DATE;
219 x_case_def_rec.LAST_UPDATE_LOGIN := p_attribute_rec.LAST_UPDATE_LOGIN;
220
221 -- IF PG_DEBUG < 10 THEN
222 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
223 iex_debug_pub.LogMessage('Convert_case_def_Record: ' || '*********Start of Convert case def Record record *********');
224 END IF;
225 end Convert_case_def_Record;
226
227
228 /* Name PopulateCaseDefTbl
229 ** Populates case definition for the given cas_id
230 */
231 Procedure PopulateCaseDefTbl
232 ( p_cas_id IN NUMBER,
233 X_case_definition_tbl OUT NOCOPY CASE_DEFINITION_TBL_TYPE
234 ) IS
235
236 Cursor get_case_def is
237 Select column_name,column_value,table_name
238 From iex_case_definitions
239 where cas_id =p_cas_id
240 and active_flag ='Y';
241 tbl_ctr NUMBER ;
242 BEGIN
243 -- IF PG_DEBUG < 10 THEN
244 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
245 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>PopulateCaseDefTbl ********* ');
246 END IF;
247 tbl_ctr := 1;
248 FOR get_case_def_rec IN get_case_def LOOP
249 x_case_definition_tbl(tbl_ctr).column_name :=get_case_def_rec.column_name ;
250 x_case_definition_tbl(tbl_ctr).column_value:=get_case_def_rec.column_value ;
251 x_case_definition_tbl(tbl_ctr).table_name :=get_case_def_rec.table_name ;
252 tbl_ctr := tbl_ctr +1;
253 END LOOP;
254 -- IF PG_DEBUG < 10 THEN
255 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
256 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>PopulateCaseDefTbl ********* ');
257 END IF;
258 END PopulateCaseDefTbl;
259
260 /** Populate Case Record **/
261 PROCEDURE PopCaseRec(p_cas_id IN NUMBER,
262 x_cas_rec OUT NOCOPY iex_cases_pvt.cas_rec_type) IS
263 Cursor Case_rec_cur (p_cas_id in number)is
264 select * from iex_cases_all_b
265 where cas_id =p_cas_id
266 and active_flag ='Y';
267
268 Cursor Case_comments_cur (p_cas_id in number)is
269 select comments from iex_cases_tl
270 where cas_id =p_cas_id and
271 userenv('LANG') in (LANGUAGE, SOURCE_LANG)
272 and active_flag ='Y';
273
274 BEGIN
275 -- IF PG_DEBUG < 10 THEN
276 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
277 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>PopCaseRec ********* ');
278 END IF;
279 FOR get_cas_rec in Case_rec_cur(p_cas_id) LOOP
280
281 x_cas_rec.ORIG_CAS_ID := p_cas_id ;
282 x_cas_rec.CASE_STATE := 'OPEN' ;
283 x_cas_rec.STATUS_CODE := 'CURRENT' ;
284 x_cas_rec.ACTIVE_FLAG :='Y';
285
286 x_cas_rec.PARTY_ID := get_cas_rec.PARTY_ID ;
287 x_cas_rec.CASE_NUMBER := get_cas_rec.CASE_NUMBER ;
288 x_cas_rec.CASE_ESTABLISHED_DATE := get_cas_rec.CASE_ESTABLISHED_DATE ;
289 x_cas_rec.OWNER_RESOURCE_ID := get_cas_rec.OWNER_RESOURCE_ID;
290 x_cas_rec.ACCESS_RESOURCE_ID := get_cas_rec.ACCESS_RESOURCE_ID;
291 x_cas_rec.REQUEST_ID := get_cas_rec.REQUEST_ID;
292 x_cas_rec.PROGRAM_APPLICATION_ID := get_cas_rec.PROGRAM_APPLICATION_ID;
293 x_cas_rec.PROGRAM_ID := get_cas_rec.PROGRAM_ID;
294 x_cas_rec.PROGRAM_UPDATE_DATE := get_cas_rec.PROGRAM_UPDATE_DATE;
295 x_cas_rec.ATTRIBUTE_CATEGORY := get_cas_rec.ATTRIBUTE_CATEGORY;
296 x_cas_rec.ATTRIBUTE1 := get_cas_rec.ATTRIBUTE1;
297 x_cas_rec.ATTRIBUTE2 := get_cas_rec.ATTRIBUTE2;
298 x_cas_rec.ATTRIBUTE3 := get_cas_rec.ATTRIBUTE3;
299 x_cas_rec.ATTRIBUTE4 := get_cas_rec.ATTRIBUTE4;
300 x_cas_rec.ATTRIBUTE5 := get_cas_rec.ATTRIBUTE5;
301 x_cas_rec.ATTRIBUTE6 := get_cas_rec.ATTRIBUTE6;
302 x_cas_rec.ATTRIBUTE7 := get_cas_rec.ATTRIBUTE7;
303 x_cas_rec.ATTRIBUTE8 := get_cas_rec.ATTRIBUTE8;
304 x_cas_rec.ATTRIBUTE9 := get_cas_rec.ATTRIBUTE9;
305 x_cas_rec.ATTRIBUTE10 := get_cas_rec.ATTRIBUTE10;
306 x_cas_rec.ATTRIBUTE11 := get_cas_rec.ATTRIBUTE11;
307 x_cas_rec.ATTRIBUTE12 := get_cas_rec.ATTRIBUTE12;
308 x_cas_rec.ATTRIBUTE13 := get_cas_rec.ATTRIBUTE13;
309 x_cas_rec.ATTRIBUTE14 := get_cas_rec.ATTRIBUTE14;
310 x_cas_rec.ATTRIBUTE15 := get_cas_rec.ATTRIBUTE15;
311 x_cas_rec.CLOSE_REASON := get_cas_rec.CLOSE_REASON;
312 x_cas_rec.org_id :=get_cas_rec.org_id;
313 x_cas_rec.PREDICTED_RECOVERY_AMOUNT := get_cas_rec.PREDICTED_RECOVERY_AMOUNT;
314 x_cas_rec.PREDICTED_CHANCE := get_cas_rec.PREDICTED_CHANCE;
315
316 END LOOP;
317 --get comments
318 -- IF PG_DEBUG < 10 THEN
319 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
320 IEX_DEBUG_PUB.LogMessage ('PopCaseRec: ' || 'Get comments for the case');
321 END IF;
322 For Case_comments_rec in Case_comments_cur (p_cas_id)
323 LOOP
324 x_cas_rec.COMMENTS :=Case_comments_rec.COMMENTS;
325 END LOOP;
326
327 -- IF PG_DEBUG < 10 THEN
328 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
329 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>PopCaseRec *********');
330 END IF;
331 END PopCaseRec;
332
333 /** Populate Case Definition Record **/
334 PROCEDURE PopulateCaseDefRec(p_table_name IN VARCHAR2,
335 p_column_name IN VARCHAR2,
336 p_column_value IN VARCHAR2,
337 p_cas_id IN NUMBER,
338 p_attribute_rec IN CAS_Rec_Type := G_MISS_CAS_REC,
339 x_case_def_rec OUT NOCOPY
340 iex_case_definitions_pvt.case_definition_rec_type) IS
341 BEGIN
342 -- IF PG_DEBUG < 10 THEN
343 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
344 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>PopulateCaseDefRec*********');
345 END IF;
346 --to populate attributes 1-15 and concurrent programs
347 convert_case_def_record(p_attribute_rec,
348 x_case_def_rec);
349 x_case_def_rec.table_name := p_table_name;
350 x_case_def_rec.column_name := p_column_name;
351 x_case_def_rec.column_value := p_column_value;
352 x_case_def_rec.cas_id := p_cas_id;
353 -- IF PG_DEBUG < 10 THEN
354 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
355 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>PopulateCaseDefRec*********');
356 END IF;
357 END PopulateCaseDefRec;
358
359 /** Populate Case Record **/
360 PROCEDURE PopulateCaseRec(p_case_number IN VARCHAR2 ,
361 p_comments IN VARCHAR2 ,
362 p_org_id IN NUMBER ,
363 p_case_established_date IN DATE ,
364 p_attribute_rec IN CAS_Rec_Type := G_MISS_CAS_REC,
365 x_cas_rec OUT NOCOPY iex_cases_pvt.cas_rec_type) IS
366 BEGIN
367 -- IF PG_DEBUG < 10 THEN
368 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
369 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>PopulateCaseRec ********* ');
370 END IF;
371 --to populate attributes 1-15 and concurrent programs
372 convert_case_record(p_attribute_rec,x_cas_rec);
373 x_cas_rec.CASE_NUMBER :=p_case_number;
374 x_cas_rec.org_id :=p_org_id;
375 x_cas_rec.COMMENTS :=p_comments;
376 x_cas_rec.case_established_date :=p_case_established_date;
377
378 -- IF PG_DEBUG < 10 THEN
379 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
380 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>PopulateCaseRec *********');
381 END IF;
382 END PopulateCaseRec;
383
384 /** Populate Case Objects Record **/
385 PROCEDURE PopulateCaseObjectRec(p_object_code IN VARCHAR2,
386 p_object_id IN NUMBER,
387 p_cas_id IN NUMBER,
388 p_attribute_rec IN CAS_Rec_Type := G_MISS_CAS_REC,
389 x_case_object_rec OUT NOCOPY
390 iex_case_objects_pvt.case_object_rec_type)IS
391
392 BEGIN
393 -- IF PG_DEBUG < 10 THEN
394 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
395 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>PopulateCaseObjectRec*********');
396 END IF;
397 --to populate attributes 1-15 and concurrent programs
398 convert_case_object_record(p_attribute_rec,x_case_object_rec);
399
400 x_case_object_rec.object_code :=p_object_code;
401 x_case_object_rec.object_id :=p_object_id;
402 x_case_object_rec.cas_id :=p_cas_id ;
403 -- IF PG_DEBUG < 10 THEN
404 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
405 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>PopulateCaseObjectRec *********');
406 END IF;
407
408 END PopulateCaseObjectRec;
409
410 /* Name CheckAdvanceDelinquencies
411 ** Used by the OKL wrapper to decide whether to call createCaseObjects or
412 ** reassign Case when Bill_to_address or any case attribute is changed
413 */
414 Function CheckAdvanceDelinquencies
415 (P_CaseID IN NUMBER,
416 x_del_id OUT NOCOPY NUMBER
417 )Return BOOLEAN IS
418
419
420 cursor c_writeoffs is
421 Select del.delinquency_id
422 from iex_delinquencies del,
423 iex_writeoffs wrioff
424 where del.case_id = p_caseid
425 and wrioff.delinquency_id = del.delinquency_id
426
427 --Start of BUG 4408860
428 -- For Bug 4408860
429 --jsanju 06/05/05
430 -- we shpudl npot be looking for status ='CLOSE'
431 --since from iex.h, we do not have case delinquencies and
432 -- and the old delinquencies with case id has been update to status ='CLOSE'
433 --and del.status <> 'CURRENT';
434 and del.status NOT IN ('CURRENT','CLOSE');
435 --End of bug 4408860
436
437
438 cursor c_bankruptcies is
439 Select del.delinquency_id
440 from iex_delinquencies del,
441 iex_bankruptcies bank
442 where del.case_id = p_caseid
443 and bank.delinquency_id = del.delinquency_id
444 --Start of BUG 4408860
445 -- For Bug 4408860
446 --jsanju 06/05/05
447 -- we shpudl npot be looking for status ='CLOSE'
448 --since from iex.h, we do not have case delinquencies and
449 -- and the old delinquencies with case id has been update to status ='CLOSE'
450 --and del.status <> 'CURRENT';
451 and del.status NOT IN ('CURRENT','CLOSE');
452 --End of bug 4408860
453
454 cursor c_repossessions is
455 Select del.delinquency_id
456 from iex_delinquencies del,
457 iex_repossessions repo
458 where del.case_id = p_caseid
459 and repo.delinquency_id = del.delinquency_id
460 --Start of BUG 4408860
461 -- For Bug 4408860
462 --jsanju 06/05/05
463 -- we shpudl npot be looking for status ='CLOSE'
464 --since from iex.h, we do not have case delinquencies and
465 -- and the old delinquencies with case id has been update to status ='CLOSE'
466 --and del.status <> 'CURRENT';
467 and del.status NOT IN ('CURRENT','CLOSE');
468 --End of bug 4408860
469
470 cursor c_litigations is
471 Select del.delinquency_id
472 from iex_delinquencies del,
473 iex_litigations litg
474 where del.case_id = p_caseid
475 and litg.delinquency_id = del.delinquency_id
476 --Start of BUG 4408860
477 -- For Bug 4408860
478 --jsanju 06/05/05
479 -- we shpudl npot be looking for status ='CLOSE'
480 --since from iex.h, we do not have case delinquencies and
481 -- and the old delinquencies with case id has been update to status ='CLOSE'
482 --and del.status <> 'CURRENT';
483 and del.status NOT IN ('CURRENT','CLOSE');
484 --End of bug 4408860
485
486
487 Begin
488
489 OPEN c_writeoffs;
490 FETCH c_writeoffs INTO x_del_id;
491 CLOSE c_writeoffs;
492
493 If x_del_id IS NOT NULL THEN
494 return TRUE;
495
496 END IF;
497
498 OPEN c_litigations;
499 FETCH c_litigations INTO x_del_id;
500 CLOSE c_litigations;
501
502 If x_del_id IS NOT NULL THEN
503 return TRUE;
504
505 END IF;
506
507 OPEN c_repossessions;
508 FETCH c_repossessions INTO x_del_id;
509 CLOSE c_repossessions;
510
511 If x_del_id IS NOT NULL THEN
512 return TRUE;
513
514 END IF;
515
516 OPEN c_bankruptcies;
517 FETCH c_bankruptcies INTO x_del_id;
518 CLOSE c_bankruptcies;
519
520 If x_del_id IS NOT NULL THEN
521 return TRUE;
522 END IF;
523
524 x_del_id := NULL;
525 return FALSE;
526
527
528 EXCEPTION WHEN OTHERS THEN
529 x_del_id := NULL;
530 Return FALSE;
531
532 End CheckAdvanceDelinquencies;
533
534 /** Send notification to the agent who case has
535 ** been reassigned
536 */
537 Procedure send_notification
538 ( p_OldCaseID IN NUMBER,
539 p_NewCaseID IN NUMBER,
540 p_ContractNumber IN VARCHAR2,
541 p_CaseAgent IN VARCHAR2,
542 x_return_status OUT NOCOPY VARCHAR2) IS
543
544 l_result VARCHAR2(100);
545 itemkey VARCHAR2(1000);
546 l_return_status VARCHAR2(100);
547
548
549 BEGIN
550
551 select iex_cases_all_b_s.nextval
552 into itemkey
553 from dual;
554
555 -- IF PG_DEBUG < 10 THEN
556 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
557 iex_debug_pub.logmessage ('send_notification: ' || 'item Key in send notification of iex_cas_utl_pub '
558 || itemkey );
559 END IF;
560
561 wf_engine.createprocess(itemtype => 'IEXCASNF',
562 itemkey => itemkey,
563 process => 'IEXCASNF');
564
565 wf_engine.setitemattrnumber(itemtype => 'IEXCASNF',
566 itemkey => itemkey,
567 aname => 'CASE_ID',
568 avalue => p_OldCaseID);
569
570 wf_engine.setitemattrnumber(itemtype => 'IEXCASNF',
571 itemkey => itemkey,
572 aname => 'NEW_CASE_ID',
573 avalue => p_NewCaseID);
574
575 wf_engine.setitemattrtext(itemtype => 'IEXCASNF',
576 itemkey => itemkey,
577 aname => 'CONTRACT_NUMBER',
578 avalue => p_ContractNumber);
579
580 wf_engine.setitemattrtext(itemtype => 'IEXCASNF',
581 itemkey => itemkey,
582 aname => 'CASE_AGENT',
583 avalue => p_CaseAgent);
584
585 wf_engine.startprocess(itemtype => 'IEXCASNF',
586 itemkey => itemkey);
587
588 wf_engine.ItemStatus(itemtype => 'IEXCASNF',
589 itemkey => itemkey,
590 status => l_return_status,
591 result => l_result);
592
593
594 if (l_return_status in ('COMPLETE', 'ACTIVE')) THEN
595 x_return_status := 'S';
596 else
597 x_return_status := 'F';
598 end if;
599 -- IF PG_DEBUG < 10 THEN
600 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
601 iex_debug_PUB.logmessage ('send_notification: ' || 'send notification status ' || x_return_status);
602 END IF;
603 EXCEPTION
604 WHEN OTHERS THEN
605 x_return_status := 'F';
606
607 END send_notification;
608
609 --******************Public routines****************************
610 /* Name CheckCaseDef
611 ** It Checks if all the elements of the case which defines a case are valid
612 ** Values
613 */
614 Function CheckCaseDef
615 (P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE
616
617 )Return BOOLEAN IS
618
619 x_sql VARCHAR2(32767);
620 x_count NUMBER;
621
622 -- clchang updated for sql bind var 05/07/2003
623 vstr1 VARCHAR2(100) := 'SELECT COUNT(*) FROM ' ;
624 vstr2 VARCHAR2(100) := ' WHERE ' ;
625 vstr3 VARCHAR2(100) := ' = ';
626
627 BEGIN
628 -- IF PG_DEBUG < 10 THEN
629 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
630 IEX_DEBUG_PUB.LogMessage ('********* Start of Function =>CheckCaseDef ******** ');
631 END IF;
632 -- IF PG_DEBUG < 10 THEN
633 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
634 iex_debug_pub.logmessage ('CheckCaseDef: ' || 'table count'|| P_case_definition_tbl.COUNT );
635 END IF;
636
637
638
639 FOR i IN 1..P_case_definition_tbl.COUNT LOOP
640 /* If Column value or column name is Null return False */
641
642
643 IF ((P_case_definition_tbl(i).column_name IS NOT NULL) and
644 (P_case_definition_tbl(i).column_value IS NOT NULL)) THEN
645
646 -- IF PG_DEBUG < 10 THEN
647 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
648 iex_debug_pub.logmessage ('CheckCaseDef: ' || 'inside loop' ||P_case_definition_tbl(i).column_name||
649 P_case_definition_tbl(i).column_value);
650 END IF;
651
652 If P_case_definition_tbl(i).table_name IS NOT NULL THEN
653
654 -- clchang updated for sql bind var 05/07/2003
655 --x_sql := 'SELECT COUNT(*) FROM ' || P_case_definition_tbl(i).table_name ;
656 --x_sql := x_sql || ' WHERE ' || P_case_definition_tbl(i).column_name|| ' = ';
657 x_sql := vstr1 || P_case_definition_tbl(i).table_name ;
658 x_sql := x_sql || vstr2 || P_case_definition_tbl(i).column_name|| vstr3;
659 -- end updated
660
661 IF P_case_definition_tbl(i).data_type = g_varchar2 THEN
662 x_sql := x_sql || '''' || P_case_definition_tbl(i).column_value || '''';
663 ELSE
664 x_sql := x_sql || P_case_definition_tbl(i).column_value;
665 END IF;
666 -- IF PG_DEBUG < 10 THEN
667 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
668 IEX_DEBUG_PUB.LogMessage ('CheckCaseDef: ' || 'SQL =>'|| x_sql);
669 END IF;
670 BEGIN
671 EXECUTE IMMEDIATE x_sql INTO x_count;
672 If x_count =0 THEN
673 -- IF PG_DEBUG < 10 THEN
674 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
675 IEX_DEBUG_PUB.LogMessage ('CheckCaseDef: ' || 'Invalid Case defintion');
676 END IF;
677 return FALSE;
678 End if;
679 EXCEPTION WHEN OTHERS THEN
680 -- IF PG_DEBUG < 10 THEN
681 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
682 IEX_DEBUG_PUB.LogMessage ('CheckCaseDef: ' || 'In Exception When Others =>'||SQLERRM);
683 END IF;
684 return FALSE;
685 END ;
686 END IF; --if table name is not null
687 ELSE -- else if column name or column value is null
688 -- IF PG_DEBUG < 10 THEN
689 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
690 IEX_DEBUG_PUB.LogMessage ('CheckCaseDef: ' || 'Column Name or column value is Null');
691 END IF;
692 return FALSE;
693 END IF; --if column name or column value is null
694 END LOOP;
695 Return TRUE;
696 -- IF PG_DEBUG < 10 THEN
697 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
698 IEX_DEBUG_PUB.LogMessage ('*************End of iex =>CheckCaseDef ******** ');
699 END IF;
700 END CheckCaseDef;
701
702 /* Name GetCaseID
703 ** Return matching case id for the given case definition
704 */
705 Procedure GetCaseID
706 (P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE
707 ,
708 x_cas_id OUT NOCOPY NUMBER ) IS
709
710
711 l_column_name VARCHAR2(1000) :=P_case_definition_tbl(1).column_name;
712 l_column_value VARCHAR2(1000) :=P_case_definition_tbl(1).column_value;
713 l_table_name VARCHAR2(1000) :=P_case_definition_tbl(1).table_name;
714 l_case_sql VARCHAR2(2000):= ' and exists (select null from iex_cases_all_b ICAS where ICAS.cas_id = a.cas_id'||
715 ' and ICAS.case_state='||'''OPEN''' ||
716 ' and ICAS.active_flag='||'''Y''' ||' ) ';
717
718 -- clchang updated for sql bind var 05/07/2003
719 vstr1 VARCHAR2 (2000):= ' select a.cas_id from ';
720 vstr2 VARCHAR2 (2000):= ' iex_case_definitions a ';
721 vstr3 VARCHAR2 (2000):= ' where a.column_name = ' ;
722 vstr4 VARCHAR2 (2000):= '''';
723 vstr5 VARCHAR2 (2000):= ' and a.column_value = ';
724 vstr6 VARCHAR2 (2000):= ' and a.active_flag= ''Y'' ';
725 l_first_sql VARCHAR2 (2000);
726 vstr7 VARCHAR2 (100) := ' and table_name=' ;
727 l_table_clause VARCHAR2 (100);
728 /*
729 l_first_sql VARCHAR2 (2000):=' select a.cas_id from '||
730 ' iex_case_definitions a '||
731 ' where a.column_name =' ||''''|| l_column_name || ''''||
732 ' and a.column_value =' ||''''|| l_column_value || ''''||
733 ' and a.active_flag='||'''Y''';
734 l_table_clause VARCHAR2(100) := ' and table_name=' ||''''|| l_table_name || '''' ;
735 */
736
737 -- for l_sql
738 v_sql_str1 VARCHAR2(100) := ' and exists ( select null from ';
739 v_sql_str2 VARCHAR2(100) := ' iex_case_definitions ';
740 v_sql_str3 VARCHAR2(100) := ' where column_name = ';
741 v_sql_str4 VARCHAR2(100) := ' and column_value = ' ;
742 v_sql_str5 VARCHAR2(100) := ' and ';
743 v_sql_str6 VARCHAR2(100) := '.cas_id= a.cas_id ' ;
744 v_sql_str7 VARCHAR2(100) := '.active_flag= ''Y'' ' ;
745
746 v_sql_str8 VARCHAR2(100) := ' and table_name= ';
747
748
749 --
750
751 l_no_table_clause VARCHAR2(100) := ' and table_name IS NULL ';
752 l_end VARCHAR2(10) := ' )';
753 l_table_alias VARCHAR2(100);
754 l_sql VARCHAR2(32767);
755
756 -- if multiple rows are returned pick the first one
757 --since the dynamic sql may return more than 1 row
758 l_multiple_rows VARCHAR2(32767) :='and rownum <2 ';
759
760 BEGIN
761 -- IF PG_DEBUG < 10 THEN
762 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
763 IEX_DEBUG_PUB.LogMessage ('*********Start of Procedure =>GetCaseID ********* ');
764 END IF;
765
766 -- clchang updated for sql bind var 05/07/2003
767 l_first_sql := vstr1 || vstr2 ||
768 vstr3 || vstr4 || l_column_name || vstr4 ||
769 vstr5 || vstr4 || l_column_value || vstr4 ||
770 vstr6;
771
772 l_table_clause := vstr7 || vstr4 || l_table_name || vstr4;
773
774 -- IF PG_DEBUG < 10 THEN
775 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776 IEX_DEBUG_PUB.LogMessage ('l_first_sql='|| l_first_sql);
777 IEX_DEBUG_PUB.LogMessage ('l_table_clause='|| l_table_clause);
778 END IF;
779 -- end
780
781 x_cas_id := NULL;
782 l_sql := l_first_sql ;
783
784 /* check if table name passed is null */
785 if l_table_name is NOT NULL THEN
786 l_sql :=l_sql ||l_table_clause;
787 else
788 l_sql :=l_sql ||l_no_table_clause;
789 end if;
790 -- IF PG_DEBUG < 10 THEN
791 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
792 iex_debug_pub.logMessage('GetCaseID: ' || 'First Sql Stmt =>'||l_sql);
793 END IF;
794
795 FOR i IN 2..P_case_definition_tbl.COUNT LOOP
796
797 l_table_alias :='ICDF'||i;
798 l_column_name :=P_case_definition_tbl(i).column_name;
799 l_column_value :=P_case_definition_tbl(i).column_value;
800 l_table_name :=P_case_definition_tbl(i).table_name;
801
802 -- clchang updated for sql bind var 05/07/2003
803 l_sql := l_sql || v_sql_str1 ||
804 v_sql_str2 || l_table_alias ||
805 v_sql_str3 || vstr4 || l_column_name || vstr4 ||
806 v_sql_str4 || vstr4 || l_column_value || vstr4 ||
807 v_sql_str5 || l_table_alias || v_sql_str6 ||
808 v_sql_str5 || l_table_alias || v_sql_str7 ;
809 /*
810 l_sql := l_sql || ' and exists ( select null from '||
811 ' iex_case_definitions ' || l_table_alias ||
812 ' where column_name =' ||''''|| l_column_name || ''''||
813 ' and column_value =' ||''''|| l_column_value || ''''||
814 ' and '||l_table_alias ||'.cas_id= ' ||'a.cas_id'||
815 ' and '||l_table_alias ||'.active_flag='||'''Y''' ;
816 */
817
818 -- IF PG_DEBUG < 10 THEN
819 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
820 IEX_DEBUG_PUB.LogMessage ('l_sql='|| l_sql);
821 END IF;
822 -- end updated
823
824 /* check if table name passed is null */
825 if l_table_name is NOT NULL THEN
826 -- clchang updated for sql bind var 05/07/2003
827 l_sql := l_sql || v_sql_str8 || vstr4 ||
828 l_table_name || vstr4 || l_end;
829 /*
830 l_sql :=l_sql || ' and table_name=' ||''''||
831 l_table_name || ''''||l_end;
832 */
833 -- IF PG_DEBUG < 10 THEN
834 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
835 IEX_DEBUG_PUB.LogMessage ('l_sql='|| l_sql);
836 END IF;
837 -- end updated
838 else
839 l_sql :=l_sql ||l_no_table_clause||l_end;
840 end if;
841 -- IF PG_DEBUG < 10 THEN
842 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
843 iex_debug_pub.logMessage('GetCaseID: ' || ' ');
844 END IF;
845 -- IF PG_DEBUG < 10 THEN
846 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
847 iex_debug_pub.logMessage('GetCaseID: ' || 'Sql Stmt =>'||l_sql);
848 END IF;
849 END LOOP;
850 l_sql := l_sql ||l_case_sql;
851
852 l_sql := l_sql ||l_multiple_rows ;
853 -- IF PG_DEBUG < 10 THEN
854 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
855 iex_debug_pub.logMessage('GetCaseID: ' || ' ');
856 END IF;
857 -- IF PG_DEBUG < 10 THEN
858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
859 iex_debug_pub.logMessage('GetCaseID: ' || 'Final Sql Stmt =>'||l_sql);
860 END IF;
861 BEGIN
862 EXECUTE IMMEDIATE l_sql INTO x_cas_id;
863 EXCEPTION
864 WHEN OTHERS THEN
865 -- IF PG_DEBUG < 10 THEN
866 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
867 IEX_DEBUG_PUB.LogMessage ('GetCaseID: ' || '*********IN WHEN OTHERS => '||SQLERRM ||' ********* ');
868 END IF;
869 x_cas_id := NULL;
870 END;
871 -- IF PG_DEBUG < 10 THEN
872 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
873 IEX_DEBUG_PUB.LogMessage ('*********End of Procedure =>GetCaseID ********* ');
874 END IF;
875 END GetCaseID;
876
877
878 /* Name CloseCase
879 ** api : Current version 2.0
880 ** Updates the status of a case. Also create a new case and updates the case objects to the new case
881 ** if specified ( the p_copy_objects parameter should be equal to 'Y')
882 ** Required Parameters are
883 ** a) p_Cas_id --> Cas Id
884 ** Optional Parameters
885 ** a) p_copy_objects --> default is 'N'. If 'Y' then create new case and copy all the case objects to
886 ** the new case. If Value is 'N',then do not copy the case objects.
887 ** p_close_date -->Closing date of the case.
888 */
889 PROCEDURE CloseCase(
890 P_Api_Version_Number IN NUMBER,
891 P_Init_Msg_List IN VARCHAR2 ,
892 P_Commit IN VARCHAR2 ,
893 P_validation_level IN NUMBER ,
894 P_cas_id IN NUMBER,
895 p_close_date IN DATE,
896 p_copy_objects IN VARCHAR2,
897 p_cas_Rec IN cas_Rec_Type,
898 X_Return_Status OUT NOCOPY VARCHAR2,
899 X_Msg_Count OUT NOCOPY NUMBER,
900 X_Msg_Data OUT NOCOPY VARCHAR2
901 ) IS
902
903
904 l_api_name CONSTANT VARCHAR2(30) := 'CloseCase';
905 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
906 l_api_version_number CONSTANT NUMBER := 2.0;
907 l_cas_new_id NUMBER;
908 l_cas_id NUMBER :=p_cas_id;
909 l_closing_date DATE :=p_close_Date;
910 l_case_comments VARCHAR2(240);
911 l_object_version_number NUMBER;
912 l_return_status VARCHAR2(1);
913 l_msg_count NUMBER;
914 l_msg_data VARCHAR2(32767);
915 l_case_object_id NUMBER;
916 l_case_definition_id NUMBER;
917 l_cas_rec iex_cases_pvt.cas_rec_type
918 := iex_cases_pvt.g_miss_cas_rec;
919 l_case_definition_rec iex_case_definitions_pvt.case_definition_rec_type
920 := iex_case_definitions_pvt.g_miss_case_definition_rec;
921 l_case_object_rec iex_case_objects_pvt.case_object_rec_type
922 := iex_case_objects_pvt.g_miss_case_object_rec;
923 l_case_definition_tbl CASE_DEFINITION_TBL_TYPE
924 DEFAULT G_MISS_CASE_DEF_TBL;
925
926 l_resource_tab iex_utilities.resource_tab_type; -- added by ehuh Aug 1 2003
927 l_resource_id NUMBER := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
928
929 Cursor get_case_objects_obj_ver_num (p_cas_id in number) is
930 select object_version_number,
931 case_object_id
932 from iex_case_objects
933 where cas_id =p_cas_id
934 and active_flag ='Y';
935
936 BEGIN
937 -- IF PG_DEBUG < 10 THEN
938 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
939 iex_debug_pub.logMessage('CloseCase: ' || '---------------------------------');
940 END IF;
941 -- IF PG_DEBUG < 10 THEN
942 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
943 IEX_DEBUG_PUB.LogMessage ('CloseCase: ' || '*********Start of Procedure => '||l_api_name||' *********');
944 END IF;
945 -- Standard Start of API savepoint
946 SAVEPOINT CLOSECASE_PUB;
947
948 -- Standard call to check for call compatibility.
949 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
950 p_api_version_number,
951 l_api_name,
952 G_PKG_NAME)
953 THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957
958 -- Initialize message list if p_init_msg_list is set to TRUE.
959 IF FND_API.to_Boolean( p_init_msg_list )
960 THEN
961 FND_MSG_PUB.initialize;
962 END IF;
963
964 -- IF PG_DEBUG < 10 THEN
965 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
966 iex_debug_pub.logMessage('CloseCase: ' || 'After Api compatability Check');
967 END IF;
968
969 -- Initialize API return status to SUCCESS
970 x_return_status := FND_API.G_RET_STS_SUCCESS;
971 --
972 -- API body
973 --
974 -- ******************************************************************
975 -- Validate Environment
976 -- ******************************************************************
977 IF FND_GLOBAL.User_Id IS NULL
978 THEN
979 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
980 THEN
981 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
982 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
983 FND_MSG_PUB.ADD;
984 END IF;
985 RAISE FND_API.G_EXC_ERROR;
986 END IF;
987 -- IF PG_DEBUG < 10 THEN
988 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
989 iex_debug_pub.logMessage('CloseCase: ' || 'After FND_GLOBAL_USER Check');
990 END IF;
991
992 -- Item level validation
993 IF (p_validation_level > fnd_api.g_valid_level_none) THEN
994
995 -- Check for valid cas_id
996 BEGIN
997 IF (l_cas_id IS NOT NULL) AND (l_cas_id <> FND_API.G_MISS_NUM) THEN
998 --May have to check for status_code too.
999 SELECT cas_id,object_version_number
1000 INTO l_cas_id,l_object_version_number
1001 FROM iex_cases_all_b
1002 WHERE cas_id = p_cas_id
1003 AND case_state = 'OPEN'
1004 and active_flag ='Y';
1005 ELSE
1006 AddMissingArgMsg(
1007 p_api_name => l_api_name_full,
1008 p_param_name => 'p_object_id' );
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF;
1011 EXCEPTION
1012 WHEN NO_DATA_FOUND THEN
1013 AddInvalidArgMsg(
1014 p_api_name => l_api_name_full,
1015 p_param_name => 'p_cas_id' ,
1016 p_param_value => p_cas_id);
1017 RAISE FND_API.G_EXC_ERROR;
1018 fnd_msg_pub.add;
1019 RAISE FND_API.G_EXC_ERROR;
1020 WHEN OTHERS THEN
1021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END;
1023
1024 END IF; --end of item level validation
1025 -- IF PG_DEBUG < 10 THEN
1026 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1027 iex_debug_pub.logMessage('CloseCase: ' || 'After Item validation ');
1028 END IF;
1029 -- Call update Case PVT to update the case_state to 'CLOSE';
1030 l_cas_rec.cas_id :=l_cas_id;
1031 l_cas_rec.object_version_number :=l_object_version_number;
1032 l_cas_rec.case_state :='CLOSE';
1033 l_cas_rec.case_closing_date :=p_close_date;
1034
1035 -- IF PG_DEBUG < 10 THEN
1036 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1037 iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling Update PVT');
1038 END IF;
1039 iex_cases_pvt.update_cas
1040 (P_Api_Version_Number =>l_api_version_number,
1041 P_Init_Msg_List =>FND_API.G_FALSE,
1042 P_Commit =>FND_API.G_FALSE,
1043 p_validation_level =>P_validation_level,
1044 P_cas_Rec =>l_cas_Rec,
1045 X_Return_Status =>l_return_status,
1046 X_Msg_Count =>l_msg_count,
1047 X_Msg_Data =>l_msg_data,
1048 xo_object_version_number =>l_object_version_number);
1049 -- IF PG_DEBUG < 10 THEN
1050 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1051 iex_debug_pub.logMessage('CloseCase: ' || 'After Calling update case PVT and Status =>'||l_return_status);
1052 END IF;
1053
1054 IF l_return_status = FND_API.G_RET_STS_ERROR then
1055 AddFailMsg( p_object => 'CASE',
1056 p_operation => 'UPDATE' );
1057 raise FND_API.G_EXC_ERROR;
1058 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1059 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1060 END IF;
1061 -- Update of case is successful and if Copy object ='Y'
1062 --Create a new case with the same case definition (copy case definition and
1063 -- create case definitions for the new case id)
1064 --Update case object with the new case ID.
1065 IF p_copy_objects =g_yes THEN
1066 -- IF PG_DEBUG < 10 THEN
1067 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1068 iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling Create Case PVT');
1069 END IF;
1070 PopCaseRec(p_cas_id =>l_cas_id,
1071 x_cas_rec =>l_cas_rec);
1072
1073 -- added by ehuh Aug 10 2003
1074
1075 begin
1076
1077 --Begin bug#5373412 schekuri 10-Jul-2006
1078 --Call new consolidated procedure get_assigned_collector
1079 /*iex_utilities.get_case_resources(p_api_version => 1.0,
1080 p_init_msg_list => FND_API.G_TRUE,
1081 p_commit => FND_API.G_FALSE,
1082 p_validation_level => p_validation_level,
1083 x_msg_count => l_msg_count,
1084 x_msg_data => l_msg_data,
1085 x_return_status => l_return_status,
1086 p_party_id => l_cas_rec.party_id,
1087 x_resource_tab => l_resource_tab);*/
1088
1089 iex_utilities.get_assigned_collector(p_api_version => 1.0,
1090 p_init_msg_list => FND_API.G_TRUE,
1091 p_commit => FND_API.G_FALSE,
1092 p_validation_level => p_validation_level,
1093 p_level => 'CASE',
1094 p_level_id => l_cas_rec.party_id,
1095 x_msg_count => l_msg_count,
1096 x_msg_data => l_msg_data,
1097 x_return_status => l_return_status,
1098 x_resource_tab => l_resource_tab);
1099 --End bug#5373412 schekuri 10-Jul-2006
1100
1101 if l_resource_tab.COUNT >0 THEN
1102 l_cas_rec.owner_resource_id := l_resource_tab(1).resource_id;
1103 else
1104 l_cas_rec.owner_resource_id := l_resource_id;
1105 end if;
1106
1107 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1108 iex_debug_pub.logMessage('Get_assign_resource(C) : ' || 'After Calling Get_assign_resource and Status =>'||l_return_status);
1109 iex_debug_pub.logMessage('Resource ID : ' || l_cas_rec.owner_resource_id );
1110 END IF;
1111
1112 exception
1113 when others then
1114 null;
1115 end;
1116 -- ended by ehuh Aug 10 2003
1117
1118 iex_cases_pvt.Create_CAS(
1119 P_Api_Version_Number =>l_api_version_number,
1120 P_Init_Msg_List =>FND_API.G_FALSE,
1121 P_Commit =>FND_API.G_FALSE,
1122 p_validation_level =>P_validation_level,
1123 P_cas_Rec =>l_cas_Rec,
1124 x_case_id =>l_cas_new_id,
1125 X_Return_Status =>l_return_status,
1126 X_Msg_Count =>l_msg_count,
1127 X_Msg_Data =>l_msg_data);
1128 -- IF PG_DEBUG < 10 THEN
1129 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1130 iex_debug_pub.logMessage('CloseCase: ' || 'After Calling Create Case PVT and Status =>'||l_return_status);
1131 END IF;
1132 IF l_return_status = FND_API.G_RET_STS_ERROR then
1133 AddFailMsg( p_object => 'CASE',
1134 p_operation => 'INSERT' );
1135 raise FND_API.G_EXC_ERROR;
1136 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1137 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139
1140 --Get case Case definition for the old case (l_cas_id)
1141 PopulateCaseDefTbl( p_cas_id =>l_cas_id,
1142 X_case_definition_tbl=>l_case_definition_tbl);
1143 --Call iex_case_definition_pvt to create case_def
1144 FOR i IN 1..l_case_definition_tbl.COUNT
1145 LOOP
1146 --Populate Case Definition record
1147 PopulateCaseDefRec
1148 (p_column_name =>l_case_definition_tbl(i).column_name,
1149 p_column_value =>l_case_definition_tbl(i).column_value,
1150 p_table_name =>l_case_definition_tbl(i).table_name,
1151 p_cas_id =>l_cas_new_id,
1152 p_attribute_rec =>p_cas_rec,
1153 x_case_def_rec =>l_case_definition_Rec);
1154 -- IF PG_DEBUG < 10 THEN
1155 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1156 iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling Create Case Definition PVT');
1157 END IF;
1158 iex_case_definitions_pvt.create_case_definitions(
1159 P_Api_Version_Number =>l_api_version_number,
1160 P_Init_Msg_List =>FND_API.G_FALSE,
1161 P_Commit =>FND_API.G_FALSE,
1162 p_validation_level =>P_validation_level,
1163 p_case_definition_rec =>l_case_definition_Rec,
1164 x_case_definition_id =>l_case_definition_id,
1165 X_Return_Status =>l_return_status,
1166 X_Msg_Count =>l_msg_count,
1167 X_Msg_Data =>l_msg_data);
1168 -- IF PG_DEBUG < 10 THEN
1169 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1170 iex_debug_pub.logMessage('CloseCase: ' || 'After Calling Create Case Definition PVT and Status =>'||l_return_status);
1171 END IF;
1172 IF l_return_status = FND_API.G_RET_STS_ERROR then
1173 AddFailMsg( p_object => 'CASE DEFINITION',
1174 p_operation => 'INSERT' );
1175 raise FND_API.G_EXC_ERROR;
1176 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1177 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1178 END IF;
1179 END LOOP;
1180 -- IF PG_DEBUG < 10 THEN
1181 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1182 iex_debug_pub.logMessage('CloseCase: ' || 'End of Case Definition' );
1183 END IF;
1184 --Populate Case object record for one CASE
1185 For cas_obj_rec in get_case_objects_obj_ver_num(l_cas_id) LOOP
1186 -- IF PG_DEBUG < 10 THEN
1187 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1188 IEX_DEBUG_PUB.LogMessage ('CloseCase: ' || ' the case object ID updated is =>'
1189 ||cas_obj_rec.case_object_id ||' and new cas ID is '||
1190 l_cas_new_id||' and old case id is => '||l_cas_id );
1191 END IF;
1192
1193 l_case_object_Rec.cas_id :=l_cas_new_id;
1194 l_case_object_Rec.case_object_id :=cas_obj_rec.case_object_id;
1195 l_case_object_Rec.object_version_number
1196 :=cas_obj_rec.object_version_number;
1197
1198 --Call update_case_object_pvt to update Case object_id
1199 -- IF PG_DEBUG < 10 THEN
1200 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1201 iex_debug_pub.logMessage('CloseCase: ' || 'Before Calling update Case Object PVT');
1202 END IF;
1203 iex_case_objects_pvt.update_case_objects(
1204 P_Api_Version_Number =>l_api_version_number,
1205 P_Init_Msg_List =>FND_API.G_FALSE,
1206 P_Commit =>FND_API.G_FALSE,
1207 p_validation_level =>P_validation_level,
1208 P_case_object_Rec =>l_case_object_Rec,
1209 X_Return_Status =>l_return_status,
1210 X_Msg_Count =>l_msg_count,
1211 X_Msg_Data =>l_msg_data,
1212 xo_object_version_number =>l_object_version_number);
1213 -- IF PG_DEBUG < 10 THEN
1214 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1215 iex_debug_pub.logMessage('CloseCase: ' || 'After Calling update Case Object PVT and Status =>'||l_return_status);
1216 END IF;
1217 -- Check return status from the above procedure call
1218 IF l_return_status = FND_API.G_RET_STS_ERROR then
1219 AddFailMsg( p_object => 'CASE OBJECTS',
1220 p_operation => 'INSERT' );
1221
1222 raise FND_API.G_EXC_ERROR;
1223 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1224 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1225 END IF;
1226 END LOOP;
1227 END IF; -- p_copy_objects =g_yes
1228
1229 --
1230 -- End of API body.
1231 --
1232
1233 -- Standard check for p_commit
1234 IF FND_API.to_Boolean( p_commit )
1235 THEN
1236 COMMIT WORK;
1237 END IF;
1238
1239 -- IF PG_DEBUG < 10 THEN
1240 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1241 IEX_DEBUG_PUB.LogMessage ('CloseCase: ' || '*********End of Procedure => '||l_api_name||' *********');
1242 END IF;
1243 -- Debug Message
1244 -- Standard call to get message count and if count is 1, get message info.
1245 FND_MSG_PUB.Count_And_Get
1246 ( p_count => x_msg_count,
1247 p_data => x_msg_data
1248 );
1249
1250 EXCEPTION
1251 WHEN FND_API.G_EXC_ERROR THEN
1252 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1253 P_API_NAME => L_API_NAME
1254 ,P_PKG_NAME => G_PKG_NAME
1255 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1256 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1257 ,X_MSG_COUNT => X_MSG_COUNT
1258 ,X_MSG_DATA => X_MSG_DATA
1259 ,X_RETURN_STATUS => X_RETURN_STATUS);
1260
1261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1262 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1263 P_API_NAME => L_API_NAME
1264 ,P_PKG_NAME => G_PKG_NAME
1265 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1266 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1267 ,X_MSG_COUNT => X_MSG_COUNT
1268 ,X_MSG_DATA => X_MSG_DATA
1269 ,X_RETURN_STATUS => X_RETURN_STATUS);
1270
1271 WHEN OTHERS THEN
1272 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1273 P_API_NAME => L_API_NAME
1274 ,P_PKG_NAME => G_PKG_NAME
1275 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1276 ,P_SQLCODE => SQLCODE
1277 ,P_SQLERRM => SQLERRM
1278 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1279 ,X_MSG_COUNT => X_MSG_COUNT
1280 ,X_MSG_DATA => X_MSG_DATA
1281 ,X_RETURN_STATUS => X_RETURN_STATUS);
1282 END CloseCase;
1283
1284
1285 /* Name CreateCaseObjects
1286 ** api : Current version 2.0
1287 ** Purpose To create case object. It first checks if a case exists, else creates
1288 ** a new case with the given case definition. Also checks if the case definition
1289 ** elements passed are valid or not.The logic is as follows
1290 ** 1. Checks validity of Case Definition
1291 ** 2.Gets Case Id for the given Valid Case Definition
1292 ** 3.Create Case if case does not exists with the valid case definition
1293 ** 4.Creates Case Object
1294 Optional Parameters
1295 ** p_cas_rec --> populate other attributes of a case, like attributes 1-15, concurrent program fields
1296 **
1297 */
1298 PROCEDURE CreateCaseObjects(
1299 P_Api_Version_Number IN NUMBER,
1300 P_Init_Msg_List IN VARCHAR2 ,
1301 P_Commit IN VARCHAR2 ,
1302 P_validation_level IN NUMBER ,
1303 P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE
1304 ,
1305 P_cas_id IN NUMBER ,
1306 P_case_number IN VARCHAR2 ,
1307 P_case_comments IN VARCHAR2 ,
1308 P_case_established_date IN DATE ,
1309 P_org_id IN NUMBER ,
1310 P_object_code IN VARCHAR2 ,
1311 P_party_id IN NUMBER,
1312 P_object_id IN NUMBER,
1313 p_cas_rec IN CAS_Rec_Type ,
1314 X_case_object_id OUT NOCOPY NUMBER,
1315 X_Return_Status OUT NOCOPY VARCHAR2,
1316 X_Msg_Count OUT NOCOPY NUMBER,
1317 X_Msg_Data OUT NOCOPY VARCHAR2
1318 ) IS
1319
1320 l_api_name CONSTANT VARCHAR2(30) := 'CreateCaseObjects';
1321 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
1322 l_api_version_number CONSTANT NUMBER := 2.0;
1323 l_org_id NUMBER :=p_org_id;
1324 l_cas_id NUMBER :=p_cas_id;
1325 l_object_code VARCHAR2(30) :=p_object_code;
1326 l_case_established_date DATE :=p_case_established_date;
1327 l_return_status VARCHAR2(1);
1328 l_msg_count NUMBER;
1329 l_msg_data VARCHAR2(32767);
1330 l_case_object_id NUMBER;
1331 l_case_definition_id NUMBER;
1332 l_cas_rec iex_cases_pvt.cas_rec_type
1333 := iex_cases_pvt.g_miss_cas_rec;
1334 l_case_definition_rec iex_case_definitions_pvt.case_definition_rec_type
1335 := iex_case_definitions_pvt.g_miss_case_definition_rec;
1336 l_case_object_rec iex_case_objects_pvt.case_object_rec_type
1337 := iex_case_objects_pvt.g_miss_case_object_rec;
1338
1339 l_resource_tab iex_utilities.resource_tab_type; -- added by ehuh Aug 1 2003
1340 l_resource_id NUMBER := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
1341
1342 --Begin Bug#6962575 barathsr 29-Jul-2008
1343 --While creating cases in bulk skip finding the resource and assign default resource
1344 l_orig_system_source OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%type;
1345
1346 cursor c_orig_system_source(p_contract_id number) is
1347 select ORIG_SYSTEM_SOURCE_CODE
1348 from OKC_K_HEADERS_B
1349 where id=p_contract_id;
1350 --End Bug#6962575 barathsr 29-Jul-2008
1351
1352
1353 BEGIN
1354 -- IF PG_DEBUG < 10 THEN
1355 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1356 IEX_DEBUG_PUB.LogMessage ('CreateCaseObjects: ' || '*********Start of Procedure => '||l_api_name||' *********');
1357 END IF;
1358 -- Standard Start of API savepoint
1359 SAVEPOINT CREATECASEOBJECTS_PUB;
1360
1361 -- Standard call to check for call compatibility.
1362 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1363 p_api_version_number,
1364 l_api_name,
1365 G_PKG_NAME)
1366 THEN
1367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1368 END IF;
1369
1370
1371 -- Initialize message list if p_init_msg_list is set to TRUE.
1372 IF FND_API.to_Boolean( p_init_msg_list )
1373 THEN
1374 FND_MSG_PUB.initialize;
1375 END IF;
1376
1377 -- IF PG_DEBUG < 10 THEN
1378 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1379 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Api compatability Check');
1380 END IF;
1381
1382 -- Initialize API return status to SUCCESS
1383 x_return_status := FND_API.G_RET_STS_SUCCESS;
1384 --
1385 -- API body
1386 --
1387 -- ******************************************************************
1388 -- Validate Environment
1389 -- ******************************************************************
1390 IF FND_GLOBAL.User_Id IS NULL
1391 THEN
1392 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1393 THEN
1394 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
1395 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1396 FND_MSG_PUB.ADD;
1397 END IF;
1398 RAISE FND_API.G_EXC_ERROR;
1399 END IF;
1400
1401 -- IF PG_DEBUG < 10 THEN
1402 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1403 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After FND_GLOBAL_USER Check');
1404 END IF;
1405
1406 -- Item level validation
1407 --IF (p_validation_level > fnd_api.g_valid_level_none) THEN
1408
1409 -- Get org_id if not present
1410 IF (p_org_id IS NULL) OR
1411 (p_org_id = FND_API.G_MISS_NUM) THEN
1412 --Bug#4679639 schekuri 20-OCT-2005
1413 --Used mo_global.get_current_org_id to get ORG_ID
1414 --l_org_id := fnd_profile.value('ORG_ID');
1415 l_org_id := mo_global.get_current_org_id;
1416 END IF;
1417 -- IF PG_DEBUG < 10 THEN
1418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1419 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'After ORG ID Check and Org_id is => '||l_org_id);
1420 END IF;
1421
1422 --Default Case_established_date to sysdate if null
1423 IF (p_Case_established_date IS NULL)OR
1424 (p_Case_established_date = FND_API.G_MISS_DATE) THEN
1425 l_Case_established_date := sysdate;
1426 END IF;
1427 -- IF PG_DEBUG < 10 THEN
1428 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1429 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'After Case ESTABLISHED Date Check and case_established is => '||l_case_ESTABLISHED_DATE);
1430 END IF;
1431
1432 -- Get object_code if not present
1433 IF (p_object_code IS NULL) OR
1434 (p_object_code = FND_API.G_MISS_CHAR) THEN
1435 l_object_code := 'CONTRACTS';
1436 END IF;
1437 -- IF PG_DEBUG < 10 THEN
1438 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1439 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'After Object Code Check and object is => '||l_object_code);
1440 END IF;
1441
1442 -- Check for required parameter object_id
1443 IF (p_object_id IS NULL) OR (p_object_id = FND_API.G_MISS_NUM) THEN
1444 -- IF PG_DEBUG < 10 THEN
1445 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1446 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'Required Parameter p_object_id is invalid');
1447 END IF;
1448 AddMissingArgMsg(
1449 p_api_name => l_api_name_full,
1450 p_param_name => 'p_object_id' );
1451 RAISE FND_API.G_EXC_ERROR;
1452 END IF;
1453
1454 -- Check for required parameter party_id
1455 IF (p_party_id IS NULL) OR (p_party_id = FND_API.G_MISS_NUM) THEN
1456 -- IF PG_DEBUG < 10 THEN
1457 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1458 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'Required Parameter p_party_id is invalid');
1459 END IF;
1460 AddMissingArgMsg(
1461 p_api_name => l_api_name_full,
1462 p_param_name => 'p_party_id' );
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465
1466 -- Check for valid cas_id or Case Definition has to be passed
1467 BEGIN
1468 IF (l_cas_id IS NOT NULL) AND (l_cas_id <> FND_API.G_MISS_NUM) THEN
1469 SELECT cas_id INTO l_cas_id
1470 FROM iex_cases_all_b
1471 WHERE cas_id = p_cas_id
1472 AND case_state = 'OPEN'
1473 and active_flag ='Y';
1474
1475 ELSIF ( P_case_definition_tbl.COUNT = 0 ) THEN
1476 -- IF PG_DEBUG < 10 THEN
1477 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1478 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'Required Parameter P_case_definition_tbl is EMPTY');
1479 END IF;
1480 AddMissingArgMsg(
1481 p_api_name => l_api_name_full,
1482 p_param_name => 'p_cas_id' );
1483 RAISE FND_API.G_EXC_ERROR;
1484 END IF;
1485 EXCEPTION
1486 WHEN NO_DATA_FOUND THEN
1487 -- IF PG_DEBUG < 10 THEN
1488 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1489 IEX_DEBUG_PUB.LogMessage('CreateCaseObjects: ' || 'Required Parameter p_cas_id is invalid');
1490 END IF;
1491 AddInvalidArgMsg(
1492 p_api_name => l_api_name_full,
1493 p_param_value => p_cas_id,
1494 p_param_name => 'p_cas_id' );
1495 RAISE FND_API.G_EXC_ERROR;
1496 WHEN OTHERS THEN
1497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1498 END;
1499
1500
1501 --END IF; --end of item level validation
1502 -- IF PG_DEBUG < 10 THEN
1503 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1504 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Item validation ');
1505 END IF;
1506
1507 --- Check Case Definition
1508 -- If case Id exists, call case_object_pvt with the given case_id
1509 -- To create the case objects
1510 -- otherwise, check case def, retrieve case number and then
1511 -- call case_object_pvt. If case does not exist, create case defintiton and
1512 -- and create case and then finally create case object.
1513
1514 IF (l_cas_id IS NOT NULL) AND (l_cas_id <> FND_API.G_MISS_NUM) THEN
1515 -- IF PG_DEBUG < 10 THEN
1516 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1517 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'case Id is passed '||l_cas_id);
1518 END IF;
1519 --Populate Case object record
1520 PopulateCaseObjectRec(p_object_code =>l_object_code,
1521 p_object_id =>p_object_id,
1522 p_cas_id =>l_cas_id,
1523 p_attribute_rec =>p_cas_rec,
1524 x_case_object_rec =>l_case_object_Rec);
1525 --Call create_case_object_pvt to create Case object_id
1526 -- IF PG_DEBUG < 10 THEN
1527 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1528 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1529 END IF;
1530 iex_case_objects_pvt.Create_case_objects(
1531 P_Api_Version_Number =>l_api_version_number,
1532 P_Init_Msg_List =>FND_API.G_FALSE,
1533 P_Commit =>FND_API.G_FALSE,
1534 p_validation_level =>P_validation_level,
1535 P_case_object_Rec =>l_case_object_Rec,
1536 x_case_object_id =>l_case_object_id,
1537 X_Return_Status =>l_return_status,
1538 X_Msg_Count =>l_msg_count,
1539 X_Msg_Data =>l_msg_data);
1540
1541 -- IF PG_DEBUG < 10 THEN
1542 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1543 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Status of Create Case object PVT => '||l_return_status);
1544 END IF;
1545 -- Check return status from the above procedure call
1546 IF l_return_status = FND_API.G_RET_STS_ERROR then
1547 FND_MESSAGE.SET_NAME('IEX', 'IEX_FAILED_CREATE_CO');
1548 FND_MSG_PUB.Add;
1549 raise FND_API.G_EXC_ERROR;
1550 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1551 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1552 else
1553 x_case_object_id :=l_case_object_id;
1554 -- IF PG_DEBUG < 10 THEN
1555 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1556 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
1557 END IF;
1558 END IF;
1559
1560 ELSE -- Check if case definition is valid and
1561 --get case id for the given case definition
1562 -- IF PG_DEBUG < 10 THEN
1563 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1564 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'case Id is not passed ');
1565 END IF;
1566 -- IF PG_DEBUG < 10 THEN
1567 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1568 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1569 END IF;
1570 If CheckCaseDef(P_case_definition_tbl) THEN
1571 -- IF PG_DEBUG < 10 THEN
1572 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1573 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'case Defintion is Valid ');
1574 END IF;
1575 --Get case Id for the given Case definition
1576 -- IF PG_DEBUG < 10 THEN
1577 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1578 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1579 END IF;
1580 GetCaseId(
1581 P_case_definition_tbl=>P_case_definition_tbl,
1582 x_cas_id =>l_cas_id);
1583 --Case definition is valid and if a matching case is found
1584 -- IF PG_DEBUG < 10 THEN
1585 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1586 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1587 END IF;
1588 if l_cas_id is NOT NULL THEN
1589 -- IF PG_DEBUG < 10 THEN
1590 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1591 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Matching case is found and CAS ID is => '||l_Cas_id);
1592 END IF;
1593 --Populate Case object record
1594 PopulateCaseObjectRec(p_object_code =>l_object_code,
1595 p_object_id =>p_object_id,
1596 p_cas_id =>l_cas_id,
1597 p_attribute_rec =>p_cas_rec,
1598 x_case_object_rec =>l_case_object_Rec);
1599 --Call create_case_object_pvt to create Case object_id
1600 -- IF PG_DEBUG < 10 THEN
1601 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1602 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Before Calling Create Case Object PVT');
1603 END IF;
1604 -- IF PG_DEBUG < 10 THEN
1605 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1606 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1607 END IF;
1608 iex_case_objects_pvt.Create_case_objects(
1609 P_Api_Version_Number =>l_api_version_number,
1610 P_Init_Msg_List =>FND_API.G_FALSE,
1611 P_Commit =>FND_API.G_FALSE,
1612 p_validation_level =>P_validation_level,
1613 P_case_object_Rec =>l_case_object_Rec,
1614 x_case_object_id =>l_case_object_id,
1615 X_Return_Status =>l_return_status,
1616 X_Msg_Count =>l_msg_count,
1617 X_Msg_Data =>l_msg_data);
1618 -- IF PG_DEBUG < 10 THEN
1619 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1620 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Calling Create Case Object PVT and Status =>'||l_return_status);
1621 END IF;
1622 -- Check return status from the above procedure call
1623 IF l_return_status = FND_API.G_RET_STS_ERROR then
1624 AddFailMsg( p_object => 'CASE OBJECTS',
1625 p_operation => 'INSERT' );
1626 raise FND_API.G_EXC_ERROR;
1627 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1628 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1629 else
1630 x_case_object_id :=l_case_object_id;
1631 -- IF PG_DEBUG < 10 THEN
1632 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1633 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
1634 END IF;
1635
1636 END IF;
1637 Else
1638 --If Case Definition is valid , but no matching Case,then create
1639 -- Case , Create case defintion and then create case object
1640 -- Populate Case Record
1641 -- IF PG_DEBUG < 10 THEN
1642 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1643 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Case Definition is Valid , no case found');
1644 END IF;
1645 PopulateCaseRec(p_case_number =>p_case_number,
1646 p_comments =>p_case_comments,
1647 p_org_id =>l_org_id,
1648 p_case_established_date =>l_case_established_date,
1649 p_attribute_rec =>p_cas_rec,
1650 x_cas_rec =>l_cas_rec);
1651
1652 l_cas_rec.party_id :=p_party_id;
1653
1654 -- added by ehuh Aug 10 2003
1655 begin
1656 --Begin bug#5373412 schekuri 10-Jul-2006
1657 --Call new consolidated procedure get_assigned_collector
1658 /*iex_utilities.get_case_resources(p_api_version => 1.0,
1659 p_init_msg_list => FND_API.G_TRUE,
1660 p_commit => FND_API.G_FALSE,
1661 p_validation_level => p_validation_level,
1662 x_msg_count => l_msg_count,
1663 x_msg_data => l_msg_data,
1664 x_return_status => l_return_status,
1665 p_party_id => l_cas_rec.party_id,
1666 x_resource_tab => l_resource_tab);*/
1667
1668 --Begin Bug#6962575 barathsr 29-Jul-2008
1669 --While creating cases in bulk skip finding the resource and assign default resource
1670 open c_orig_system_source(p_object_id);
1671 fetch c_orig_system_source into l_orig_system_source;
1672 close c_orig_system_source;
1673 if l_orig_system_source<>'OKL_IMPORT' then
1674
1675 iex_utilities.get_assigned_collector(p_api_version => 1.0,
1676 p_init_msg_list => FND_API.G_TRUE,
1677 p_commit => FND_API.G_FALSE,
1678 p_validation_level => p_validation_level,
1679 p_level => 'CASE',
1680 p_level_id => l_cas_rec.party_id,
1681 x_msg_count => l_msg_count,
1682 x_msg_data => l_msg_data,
1683 x_return_status => l_return_status,
1684 x_resource_tab => l_resource_tab);
1685 --End bug#5373412 schekuri 10-Jul-2006
1686 end if;
1687 --End Bug#6962575 barathsr 29-Jul-2008
1688 if l_resource_tab.COUNT >0 THEN
1689 l_cas_rec.owner_resource_id := l_resource_tab(1).resource_id;
1690 else
1691 l_cas_rec.owner_resource_id := l_resource_id;
1692 end if;
1693
1694 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1695 iex_debug_pub.logMessage('Get_assign_resource(O) : ' || 'After Calling Get_assign_resource and Status =>'||l_return_status);
1696 iex_debug_pub.logMessage('Resource ID : ' || l_cas_rec.owner_resource_id );
1697 END IF;
1698
1699 exception
1700 when others then
1701 null;
1702 end;
1703 -- ended by ehuh Aug 10 2003
1704
1705 --Call iex_cases_pvt to create a case
1706 -- IF PG_DEBUG < 10 THEN
1707 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1708 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Before Calling Create Case PVT');
1709 END IF;
1710
1711 -- IF PG_DEBUG < 10 THEN
1712 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1713 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1714 END IF;
1715
1716 iex_cases_pvt.Create_CAS(
1717 P_Api_Version_Number =>l_api_version_number,
1718 P_Init_Msg_List =>FND_API.G_FALSE,
1719 P_Commit =>FND_API.G_FALSE,
1720 p_validation_level =>P_validation_level,
1721 P_cas_Rec =>l_cas_Rec,
1722 x_case_id =>l_cas_id,
1723 X_Return_Status =>l_return_status,
1724 X_Msg_Count =>l_msg_count,
1725 X_Msg_Data =>l_msg_data);
1726 -- IF PG_DEBUG < 10 THEN
1727 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1728 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Calling Create Case PVT and Status =>'||l_return_status);
1729 END IF;
1730 IF l_return_status = FND_API.G_RET_STS_ERROR then
1731 AddFailMsg( p_object => 'CASE',
1732 p_operation => 'INSERT' );
1733 raise FND_API.G_EXC_ERROR;
1734 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1735 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1736 END IF;
1737 --Call iex_case_definition_pvt to create case_def
1738 FOR i IN 1..P_case_definition_tbl.COUNT LOOP
1739 --Populate Case Definition record
1740 PopulateCaseDefRec(p_column_name =>p_case_definition_tbl(i).column_name,
1741 p_column_value =>p_case_definition_tbl(i).column_value,
1742 p_table_name =>p_case_definition_tbl(i).table_name,
1743 p_cas_id =>l_cas_id,
1744 p_attribute_rec =>p_cas_rec,
1745 x_case_def_rec =>l_case_definition_Rec);
1746 -- IF PG_DEBUG < 10 THEN
1747 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1748 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Before Calling Create Case Definition PVT');
1749 END IF;
1750 -- IF PG_DEBUG < 10 THEN
1751 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1752 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1753 END IF;
1754 iex_case_definitions_pvt.create_case_definitions(
1755 P_Api_Version_Number =>l_api_version_number,
1756 P_Init_Msg_List =>FND_API.G_FALSE,
1757 P_Commit =>FND_API.G_FALSE,
1758 p_validation_level =>P_validation_level,
1759 p_case_definition_rec =>l_case_definition_Rec,
1760 x_case_definition_id =>l_case_definition_id,
1761 X_Return_Status =>l_return_status,
1762 X_Msg_Count =>l_msg_count,
1763 X_Msg_Data =>l_msg_data);
1764 -- IF PG_DEBUG < 10 THEN
1765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1766 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Calling Create Case Definition PVT and Status =>'||l_return_status);
1767 END IF;
1768 IF l_return_status = FND_API.G_RET_STS_ERROR then
1769 AddFailMsg( p_object => 'CASE DEFINITIONS',
1770 p_operation => 'INSERT' );
1771 raise FND_API.G_EXC_ERROR;
1772 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1773 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1774 END IF;
1775 END LOOP;
1776 --Populate Case object record
1777 PopulateCaseObjectRec(p_object_code =>l_object_code,
1778 p_object_id =>p_object_id,
1779 p_cas_id =>l_cas_id,
1780 p_attribute_rec =>p_cas_rec,
1781 x_case_object_rec =>l_case_object_Rec);
1782 --Call create_case_object_pvt to create Case object_id
1783 -- IF PG_DEBUG < 10 THEN
1784 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1785 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Before Calling Create Case Object PVT');
1786 END IF;
1787 -- IF PG_DEBUG < 10 THEN
1788 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1789 iex_debug_pub.logMessage('CreateCaseObjects: ' || '---------------------------------');
1790 END IF;
1791 iex_case_objects_pvt.Create_case_objects(
1792 P_Api_Version_Number =>l_api_version_number,
1793 P_Init_Msg_List =>FND_API.G_FALSE,
1794 P_Commit =>FND_API.G_FALSE,
1795 p_validation_level =>P_validation_level,
1796 P_case_object_Rec =>l_case_object_Rec,
1797 x_case_object_id =>l_case_object_id,
1798 X_Return_Status =>l_return_status,
1799 X_Msg_Count =>l_msg_count,
1800 X_Msg_Data =>l_msg_data);
1801 -- IF PG_DEBUG < 10 THEN
1802 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1803 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'After Calling Create Case Object PVT and Status =>'||l_return_status);
1804 END IF;
1805 -- Check return status from the above procedure call
1806 IF l_return_status = FND_API.G_RET_STS_ERROR then
1807 AddFailMsg( p_object => 'CASE OBJECTS',
1808 p_operation => 'INSERT' );
1809 raise FND_API.G_EXC_ERROR;
1810 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1811 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1812 else
1813 x_case_object_id :=l_case_object_id;
1814 -- IF PG_DEBUG < 10 THEN
1815 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1816 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
1817 END IF;
1818 END IF;
1819
1820 END IF; -- end of if G_match_Case_id is NOT NULL
1821 ELSE -- if case Definition is invalid then error out.
1822 --How to assign value of parameter,since it is table?
1823 -- IF PG_DEBUG < 10 THEN
1824 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1825 iex_debug_pub.logMessage('CreateCaseObjects: ' || 'Case Definition is invalid');
1826 END IF;
1827 AddInvalidArgMsg(
1828 p_api_name => l_api_name_full,
1829 p_param_value => 'P_case_definition_tbl',
1830 p_param_name => 'P_case_definition_tbl' );
1831 RAISE FND_API.G_EXC_ERROR;
1832
1833 END IF; --If CheckCaseDef(P_case_definition_tbl)
1834 END IF; -- (l_cas_id IS NOT NULL) OR (l_cas_id <> FND_API.G_MISS_NUM)
1835
1836 --
1837 -- End of API body.
1838 --
1839
1840 -- Standard check for p_commit
1841 IF FND_API.to_Boolean( p_commit )
1842 THEN
1843 COMMIT WORK;
1844 END IF;
1845
1846
1847 -- Standard call to get message count and if count is 1, get message info.
1848 FND_MSG_PUB.Count_And_Get
1849 ( p_count => x_msg_count,
1850 p_data => x_msg_data
1851 );
1852 -- IF PG_DEBUG < 10 THEN
1853 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1854 IEX_DEBUG_PUB.LogMessage ('CreateCaseObjects: ' || '*********End of Procedure => '||l_api_name||' *********');
1855 END IF;
1856 EXCEPTION
1857 WHEN FND_API.G_EXC_ERROR THEN
1858 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1859 P_API_NAME => L_API_NAME
1860 ,P_PKG_NAME => G_PKG_NAME
1861 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1862 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1863 ,X_MSG_COUNT => X_MSG_COUNT
1864 ,X_MSG_DATA => X_MSG_DATA
1865 ,X_RETURN_STATUS => X_RETURN_STATUS);
1866
1867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1868 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1869 P_API_NAME => L_API_NAME
1870 ,P_PKG_NAME => G_PKG_NAME
1871 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1872 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1873 ,X_MSG_COUNT => X_MSG_COUNT
1874 ,X_MSG_DATA => X_MSG_DATA
1875 ,X_RETURN_STATUS => X_RETURN_STATUS);
1876
1877 WHEN OTHERS THEN
1878 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1879 P_API_NAME => L_API_NAME
1880 ,P_PKG_NAME => G_PKG_NAME
1881 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1882 ,P_SQLCODE => SQLCODE
1883 ,P_SQLERRM => SQLERRM
1884 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
1885 ,X_MSG_COUNT => X_MSG_COUNT
1886 ,X_MSG_DATA => X_MSG_DATA
1887 ,X_RETURN_STATUS => X_RETURN_STATUS);
1888 END CreateCaseObjects;
1889
1890
1891 /* Name ReassignCaseObjects
1892 ** api : Current version 2.0
1893 ** Purpose To Reassign case object.
1894 ** Delete the contracts from iex_case_objects
1895 ** It first checks if a case exists, else creates
1896 ** a new case with the given case definition. Also checks if the case definition
1897 ** elements passed are valid or not.The logic is as follows
1898 ** 1. Checks validity of Case Definition
1899 ** 2.Gets Case Id for the given Valid Case Definition
1900 ** 3.Create Case if case does not exists with the valid case definition
1901 ** 4.Creates Case Object
1902 ** Rescore both the cases and creates/updates the delinquencies.
1903 Optional Parameters
1904 ** p_cas_rec --> populate other attributes of a case, like attributes 1-15, concurrent program fields
1905 **
1906 */
1907 PROCEDURE ReassignCaseObjects(
1908 P_Api_Version_Number IN NUMBER,
1909 P_Init_Msg_List IN VARCHAR2 ,
1910 P_Commit IN VARCHAR2 ,
1911 P_validation_level IN NUMBER ,
1912 P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE
1913 ,
1914 P_cas_id IN NUMBER ,
1915 P_case_number IN VARCHAR2 ,
1916 P_case_comments IN VARCHAR2 ,
1917 P_case_established_date IN DATE ,
1918 P_org_id IN NUMBER ,
1919 P_object_code IN VARCHAR2 ,
1920 P_party_id IN NUMBER,
1921 P_object_id IN NUMBER,
1922 p_cas_rec IN CAS_Rec_Type ,
1923 X_case_object_id OUT NOCOPY NUMBER,
1924 X_Return_Status OUT NOCOPY VARCHAR2,
1925 X_Msg_Count OUT NOCOPY NUMBER,
1926 X_Msg_Data OUT NOCOPY VARCHAR2
1927
1928 ) IS
1929
1930 l_api_name CONSTANT VARCHAR2(30) := 'ReassignCaseObjects';
1931 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
1932 l_api_version_number CONSTANT NUMBER := 2.0;
1933 l_org_id NUMBER :=p_org_id;
1934 l_cas_id NUMBER :=p_cas_id;
1935 l_object_code VARCHAR2(30) :=p_object_code;
1936 l_case_established_date DATE :=p_case_established_date;
1937 l_return_status VARCHAR2(1);
1938 l_msg_count NUMBER;
1939 l_msg_data VARCHAR2(32767);
1940 l_case_object_id NUMBER;
1941 l_case_definition_id NUMBER;
1942 l_cas_rec iex_cases_pvt.cas_rec_type
1943 := iex_cases_pvt.g_miss_cas_rec;
1944 l_case_definition_rec iex_case_definitions_pvt.case_definition_rec_type
1945 := iex_case_definitions_pvt.g_miss_case_definition_rec;
1946 l_case_object_rec iex_case_objects_pvt.case_object_rec_type
1947 := iex_case_objects_pvt.g_miss_case_object_rec;
1948
1949 l_old_case_id NUMBER;
1950 l_new_case_id NUMBER;
1951 x_del_id NUMBER;
1952
1953 l_case_agent VARCHAR2(120);
1954 l_contract_number VARCHAR2(120);
1955
1956 l_resource_tab iex_utilities.resource_tab_type; -- added by ehuh Aug 1 2003
1957 l_resource_id NUMBER := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
1958
1959
1960 cursor c_getname(p_old_case_id IN NUMBER) is
1961 Select user_name
1962 from jtf_rs_resource_extns a,
1963 iex_Cases_all_b b
1964 where b.cas_id =p_old_case_id
1965 and a.resource_id = nvl(b.access_resource_id,
1966 fnd_profile.value('IEX_DEFAULT_CASE_AGENT'));
1967
1968
1969 cursor c_contract (p_contract_id IN NUMBER) is
1970 Select contract_number
1971 from okc_k_headers_b
1972 where id =p_contract_id;
1973 l_del_id NUMBER;
1974
1975 BEGIN
1976
1977 -- IF PG_DEBUG < 10 THEN
1978 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1979 IEX_DEBUG_PUB.LogMessage ('ReassignCaseObjects: ' || '*********Start of Procedure => '||l_api_name||' *********');
1980 END IF;
1981 -- Standard Start of API savepoint
1982 SAVEPOINT REASSIGNCASEOBJECTS_PUB;
1983
1984 -- Standard call to check for call compatibility.
1985 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1986 p_api_version_number,
1987 l_api_name,
1988 G_PKG_NAME)
1989 THEN
1990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1991 END IF;
1992
1993
1994 -- Initialize message list if p_init_msg_list is set to TRUE.
1995 IF FND_API.to_Boolean( p_init_msg_list )
1996 THEN
1997 FND_MSG_PUB.initialize;
1998 END IF;
1999
2000 -- IF PG_DEBUG < 10 THEN
2001 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2002 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Api compatability Check');
2003 END IF;
2004
2005 -- Initialize API return status to SUCCESS
2006 x_return_status := FND_API.G_RET_STS_SUCCESS;
2007 l_return_status := FND_API.G_RET_STS_SUCCESS;
2008
2009 --
2010 -- API body
2011 --
2012 -- ******************************************************************
2013 -- Validate Environment
2014 -- ******************************************************************
2015 IF FND_GLOBAL.User_Id IS NULL
2016 THEN
2017 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2018 THEN
2019 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
2020 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2021 FND_MSG_PUB.ADD;
2022 END IF;
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 -- IF PG_DEBUG < 10 THEN
2027 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2028 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After FND_GLOBAL_USER Check');
2029 END IF;
2030
2031 -- Item level validation
2032 --IF (p_validation_level > fnd_api.g_valid_level_none) THEN
2033
2034 -- Get org_id if not present
2035 IF (p_org_id IS NULL) OR
2036 (p_org_id = FND_API.G_MISS_NUM) THEN
2037
2038 --Bug#4679639 schekuri 20-OCT-2005
2039 --Used mo_global.get_current_org_id to get ORG_ID
2040 --l_org_id := fnd_profile.value('ORG_ID');
2041 l_org_id := mo_global.get_current_org_id;
2042 END IF;
2043 -- IF PG_DEBUG < 10 THEN
2044 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2045 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'After ORG ID Check and Org_id is => '||l_org_id);
2046 END IF;
2047
2048 --Default Case_established_date to sysdate if null
2049 IF (p_Case_established_date IS NULL)OR
2050 (p_Case_established_date = FND_API.G_MISS_DATE) THEN
2051 l_Case_established_date := sysdate;
2052 END IF;
2053 -- IF PG_DEBUG < 10 THEN
2054 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2055 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'After Case ESTABLISHED Date Check and case_established is => '||l_case_ESTABLISHED_DATE);
2056 END IF;
2057
2058 -- Get object_code if not present
2059 IF (p_object_code IS NULL) OR
2060 (p_object_code = FND_API.G_MISS_CHAR) THEN
2061 l_object_code := 'CONTRACTS';
2062 END IF;
2063 -- IF PG_DEBUG < 10 THEN
2064 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2065 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'After Object Code Check and object is => '||l_object_code);
2066 END IF;
2067
2068 -- Check for required parameter object_id
2069 IF (p_object_id IS NULL) OR (p_object_id = FND_API.G_MISS_NUM) THEN
2070 -- IF PG_DEBUG < 10 THEN
2071 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2072 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'Required Parameter p_object_id is invalid');
2073 END IF;
2074 AddMissingArgMsg(
2075 p_api_name => l_api_name_full,
2076 p_param_name => 'p_object_id' );
2077 RAISE FND_API.G_EXC_ERROR;
2078 END IF;
2079
2080 -- Check for required parameter party_id
2081 IF (p_party_id IS NULL) OR (p_party_id = FND_API.G_MISS_NUM) THEN
2082 -- IF PG_DEBUG < 10 THEN
2083 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2084 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'Required Parameter p_party_id is invalid');
2085 END IF;
2086 AddMissingArgMsg(
2087 p_api_name => l_api_name_full,
2088 p_param_name => 'p_party_id' );
2089 RAISE FND_API.G_EXC_ERROR;
2090 END IF;
2091
2092 -- Check for valid cas_id or Case Definition has to be passed
2093 BEGIN
2094 IF (l_cas_id IS NOT NULL) AND (l_cas_id <> FND_API.G_MISS_NUM) THEN
2095 SELECT cas_id INTO l_cas_id
2096 FROM iex_cases_all_b
2097 WHERE cas_id = p_cas_id
2098 AND case_state = 'OPEN'
2099 and active_flag ='Y';
2100
2101 ELSIF ( P_case_definition_tbl.COUNT = 0 ) THEN
2102 -- IF PG_DEBUG < 10 THEN
2103 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2104 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'Required Parameter P_case_definition_tbl is EMPTY');
2105 END IF;
2106 AddMissingArgMsg(
2107 p_api_name => l_api_name_full,
2108 p_param_name => 'p_cas_id' );
2109 RAISE FND_API.G_EXC_ERROR;
2110 END IF;
2111 EXCEPTION
2112 WHEN NO_DATA_FOUND THEN
2113 -- IF PG_DEBUG < 10 THEN
2114 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2115 IEX_DEBUG_PUB.LogMessage('ReassignCaseObjects: ' || 'Required Parameter p_cas_id is invalid');
2116 END IF;
2117 AddInvalidArgMsg(
2118 p_api_name => l_api_name_full,
2119 p_param_value => p_cas_id,
2120 p_param_name => 'p_cas_id' );
2121 RAISE FND_API.G_EXC_ERROR;
2122 WHEN OTHERS THEN
2123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2124 END;
2125
2126 -- Store the old case Id , will be used for re-score
2127 BEGIN
2128 select a.cas_id ,b.contract_number
2129 into l_old_case_id,l_contract_number
2130 from iex_case_objects a, okc_k_headers_b b
2131 where a.object_id =p_object_id
2132 and b.id =a.object_id;
2133
2134 -- IF PG_DEBUG < 10 THEN
2135 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2136 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'old case Id ' || l_old_case_id);
2137 END IF;
2138 -- IF PG_DEBUG < 10 THEN
2139 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2140 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'Contract Number ' || l_contract_number);
2141 END IF;
2142
2143 EXCEPTION WHEN OTHERS THEN
2144 NULL;
2145 END;
2146
2147 -- Check if the case has any later stage delinquencies
2148 --if there is do not re assign, exit out.
2149 IF CheckAdvanceDelinquencies(l_old_case_id,x_del_id) THEN
2150 -- IF PG_DEBUG < 10 THEN
2151 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2152 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || l_old_case_id || ' has later stage of delinquencies');
2153 END IF;
2154 fnd_message.set_name('IEX', 'IEX_ADVANCE_DEL_EXISTS');
2155 fnd_message.set_token('DEL_ID', x_del_id);
2156 fnd_message.set_token('CASE_ID',l_old_case_id);
2157 fnd_message.set_token('CON_NUM',l_contract_number);
2158 fnd_msg_pub.add;
2159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2160
2161 END IF;
2162
2163 --Delete the contract/object from case objects table
2164 select case_object_id into l_case_object_id
2165 from iex_case_objects
2166 where object_id =p_object_id;
2167
2168 iex_case_objects_pvt.delete_case_objects(
2169 P_Api_Version_Number =>l_api_version_number,
2170 P_Init_Msg_List =>FND_API.G_TRUE,
2171 P_Commit =>FND_API.G_FALSE,
2172 p_validation_level =>P_validation_level,
2173 P_case_object_ID =>l_case_object_id,
2174 X_Return_Status =>l_return_status,
2175 X_Msg_Count =>l_msg_count,
2176 X_Msg_Data =>l_msg_data);
2177
2178 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
2179 AddFailMsg( p_object => 'CASE OBJECTS',
2180 p_operation => 'DELETE' );
2181 raise FND_API.G_EXC_ERROR;
2182 ELSE
2183 -- IF PG_DEBUG < 10 THEN
2184 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2185 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'deletion of ' || p_object_id
2186 ||' successfull');
2187 END IF;
2188 END IF;
2189
2190
2191
2192 --END IF; --end of item level validation
2193 -- IF PG_DEBUG < 10 THEN
2194 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2195 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Item validation ');
2196 END IF;
2197
2198 --- Check Case Definition
2199 -- If case Id exists, call case_object_pvt with the given case_id
2200 -- To create the case objects
2201 -- otherwise, check case def, retrieve case number and then
2202 -- call case_object_pvt. If case does not exist, create case defintiton and
2203 -- and create case and then finally create case object.
2204
2205 IF (l_cas_id IS NOT NULL) AND (l_cas_id <> FND_API.G_MISS_NUM) THEN
2206 -- IF PG_DEBUG < 10 THEN
2207 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2208 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'case Id is passed '||l_cas_id);
2209 END IF;
2210 --Populate Case object record
2211 PopulateCaseObjectRec(p_object_code =>l_object_code,
2212 p_object_id =>p_object_id,
2213 p_cas_id =>l_cas_id,
2214 p_attribute_rec =>p_cas_rec,
2215 x_case_object_rec =>l_case_object_Rec);
2216 --Call create_case_object_pvt to create Case object_id
2217 -- IF PG_DEBUG < 10 THEN
2218 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2219 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2220 END IF;
2221 iex_case_objects_pvt.Create_case_objects(
2222 P_Api_Version_Number =>l_api_version_number,
2223 P_Init_Msg_List =>FND_API.G_FALSE,
2224 P_Commit =>FND_API.G_FALSE,
2225 p_validation_level =>P_validation_level,
2226 P_case_object_Rec =>l_case_object_Rec,
2227 x_case_object_id =>l_case_object_id,
2228 X_Return_Status =>l_return_status,
2229 X_Msg_Count =>l_msg_count,
2230 X_Msg_Data =>l_msg_data);
2231
2232 -- IF PG_DEBUG < 10 THEN
2233 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2234 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Status of Create Case object PVT => '||l_return_status);
2235 END IF;
2236 -- Check return status from the above procedure call
2237 IF l_return_status = FND_API.G_RET_STS_ERROR then
2238 FND_MESSAGE.SET_NAME('IEX', 'IEX_FAILED_CREATE_CO');
2239 FND_MSG_PUB.Add;
2240 raise FND_API.G_EXC_ERROR;
2241 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2242 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2243 else
2244 x_case_object_id :=l_case_object_id;
2245 -- IF PG_DEBUG < 10 THEN
2246 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2247 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
2248 END IF;
2249 END IF;
2250
2251 ELSE -- Check if case definition is valid and
2252 --get case id for the given case definition
2253 -- IF PG_DEBUG < 10 THEN
2254 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2255 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'case Id is not passed ');
2256 END IF;
2257 -- IF PG_DEBUG < 10 THEN
2258 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2259 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2260 END IF;
2261 If CheckCaseDef(P_case_definition_tbl) THEN
2262 -- IF PG_DEBUG < 10 THEN
2263 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2264 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'case Defintion is Valid ');
2265 END IF;
2266 --Get case Id for the given Case definition
2267 -- IF PG_DEBUG < 10 THEN
2268 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2269 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2270 END IF;
2271 GetCaseId(
2272 P_case_definition_tbl=>P_case_definition_tbl,
2273 x_cas_id =>l_cas_id);
2274 --Case definition is valid and if a matching case is found
2275 -- IF PG_DEBUG < 10 THEN
2276 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2277 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2278 END IF;
2279 if l_cas_id is NOT NULL THEN
2280 -- IF PG_DEBUG < 10 THEN
2281 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2282 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Matching case is found and CAS ID is => '||l_Cas_id);
2283 END IF;
2284 --Populate Case object record
2285 PopulateCaseObjectRec(p_object_code =>l_object_code,
2286 p_object_id =>p_object_id,
2287 p_cas_id =>l_cas_id,
2288 p_attribute_rec =>p_cas_rec,
2289 x_case_object_rec =>l_case_object_Rec);
2290 --Call create_case_object_pvt to create Case object_id
2291 -- IF PG_DEBUG < 10 THEN
2292 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2293 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Before Calling Create Case Object PVT');
2294 END IF;
2295 -- IF PG_DEBUG < 10 THEN
2296 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2297 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2298 END IF;
2299 iex_case_objects_pvt.Create_case_objects(
2300 P_Api_Version_Number =>l_api_version_number,
2301 P_Init_Msg_List =>FND_API.G_FALSE,
2302 P_Commit =>FND_API.G_FALSE,
2303 p_validation_level =>P_validation_level,
2304 P_case_object_Rec =>l_case_object_Rec,
2305 x_case_object_id =>l_case_object_id,
2306 X_Return_Status =>l_return_status,
2307 X_Msg_Count =>l_msg_count,
2308 X_Msg_Data =>l_msg_data);
2309 -- IF PG_DEBUG < 10 THEN
2310 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2311 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Calling Create Case Object PVT and Status =>'||l_return_status);
2312 END IF;
2313 -- Check return status from the above procedure call
2314 IF l_return_status = FND_API.G_RET_STS_ERROR then
2315 AddFailMsg( p_object => 'CASE OBJECTS',
2316 p_operation => 'INSERT' );
2317 raise FND_API.G_EXC_ERROR;
2318 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2319 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2320 else
2321 x_case_object_id :=l_case_object_id;
2322 -- IF PG_DEBUG < 10 THEN
2323 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2324 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
2325 END IF;
2326
2327 END IF;
2328 Else
2329 --If Case Definition is valid , but no matching Case,then create
2330 -- Case , Create case defintion and then create case object
2331 -- Populate Case Record
2332 -- IF PG_DEBUG < 10 THEN
2333 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2334 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Case Definition is Valid , no case found');
2335 END IF;
2336 PopulateCaseRec(p_case_number =>p_case_number,
2337 p_comments =>p_case_comments,
2338 p_org_id =>l_org_id,
2339 p_case_established_date =>l_case_established_date,
2340 p_attribute_rec =>p_cas_rec,
2341 x_cas_rec =>l_cas_rec);
2342 l_cas_rec.party_id :=p_party_id;
2343 --Call iex_cases_pvt to create a case
2344
2345 -- IF PG_DEBUG < 10 THEN
2346 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2347 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Before Calling Create Case PVT');
2348 END IF;
2349 -- IF PG_DEBUG < 10 THEN
2350 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2351 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2352 END IF;
2353
2354 -- added by ehuh Aug 10 2003
2355
2356 begin
2357
2358 --Begin bug#5373412 schekuri 10-Jul-2006
2359 --Call new consolidated procedure get_assigned_collector
2360 /*iex_utilities.get_case_resources(p_api_version => 1.0,
2361 p_init_msg_list => FND_API.G_TRUE,
2362 p_commit => FND_API.G_FALSE,
2363 p_validation_level => p_validation_level,
2364 x_msg_count => l_msg_count,
2365 x_msg_data => l_msg_data,
2366 x_return_status => l_return_status,
2367 p_party_id => l_cas_rec.party_id,
2368 x_resource_tab => l_resource_tab);*/
2369
2370 iex_utilities.get_assigned_collector(p_api_version => 1.0,
2371 p_init_msg_list => FND_API.G_TRUE,
2372 p_commit => FND_API.G_FALSE,
2373 p_validation_level => p_validation_level,
2374 p_level => 'CASE',
2375 p_level_id => l_cas_rec.party_id,
2376 x_msg_count => l_msg_count,
2377 x_msg_data => l_msg_data,
2378 x_return_status => l_return_status,
2379 x_resource_tab => l_resource_tab);
2380
2381 --End bug#5373412 schekuri 10-Jul-2006
2382
2383 if l_resource_tab.COUNT >0 THEN
2384 l_cas_rec.owner_resource_id := l_resource_tab(1).resource_id;
2385 else
2386 l_cas_rec.owner_resource_id := l_resource_id;
2387 end if;
2388
2389 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2390 iex_debug_pub.logMessage('Get_assign_resource(R) : ' || 'After Calling Get_assign_resource and Status =>'||l_return_status);
2391 iex_debug_pub.logMessage('Resource ID : ' || l_cas_rec.owner_resource_id );
2392 END IF;
2393
2394 exception
2395 when others then
2396 null;
2397
2398 end;
2399
2400 -- ended by ehuh Aug 10 2003
2401
2402 iex_cases_pvt.Create_CAS(
2403 P_Api_Version_Number =>l_api_version_number,
2404 P_Init_Msg_List =>FND_API.G_FALSE,
2405 P_Commit =>FND_API.G_FALSE,
2406 p_validation_level =>P_validation_level,
2407 P_cas_Rec =>l_cas_Rec,
2408 x_case_id =>l_cas_id,
2409 X_Return_Status =>l_return_status,
2410 X_Msg_Count =>l_msg_count,
2411 X_Msg_Data =>l_msg_data);
2412 -- IF PG_DEBUG < 10 THEN
2413 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2414 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Calling Create Case PVT and Status =>'||l_return_status);
2415 END IF;
2416 IF l_return_status = FND_API.G_RET_STS_ERROR then
2417 AddFailMsg( p_object => 'CASE',
2418 p_operation => 'INSERT' );
2419 raise FND_API.G_EXC_ERROR;
2420 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2421 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2422 END IF;
2423 --Call iex_case_definition_pvt to create case_def
2424 FOR i IN 1..P_case_definition_tbl.COUNT LOOP
2425 --Populate Case Definition record
2426 PopulateCaseDefRec(p_column_name =>p_case_definition_tbl(i).column_name,
2427 p_column_value =>p_case_definition_tbl(i).column_value,
2428 p_table_name =>p_case_definition_tbl(i).table_name,
2429 p_cas_id =>l_cas_id,
2430 p_attribute_rec =>p_cas_rec,
2431 x_case_def_rec =>l_case_definition_Rec);
2432 -- IF PG_DEBUG < 10 THEN
2433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2434 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Before Calling Create Case Definition PVT');
2435 END IF;
2436 -- IF PG_DEBUG < 10 THEN
2437 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2438 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2439 END IF;
2440 iex_case_definitions_pvt.create_case_definitions(
2441 P_Api_Version_Number =>l_api_version_number,
2442 P_Init_Msg_List =>FND_API.G_FALSE,
2443 P_Commit =>FND_API.G_FALSE,
2444 p_validation_level =>P_validation_level,
2445 p_case_definition_rec =>l_case_definition_Rec,
2446 x_case_definition_id =>l_case_definition_id,
2447 X_Return_Status =>l_return_status,
2448 X_Msg_Count =>l_msg_count,
2449 X_Msg_Data =>l_msg_data);
2450 -- IF PG_DEBUG < 10 THEN
2451 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2452 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Calling Create Case Definition PVT and Status =>'||l_return_status);
2453 END IF;
2454 IF l_return_status = FND_API.G_RET_STS_ERROR then
2455 AddFailMsg( p_object => 'CASE DEFINITIONS',
2456 p_operation => 'INSERT' );
2457 raise FND_API.G_EXC_ERROR;
2458 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2459 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2460 END IF;
2461 END LOOP;
2462 --Populate Case object record
2463 PopulateCaseObjectRec(p_object_code =>l_object_code,
2464 p_object_id =>p_object_id,
2465 p_cas_id =>l_cas_id,
2466 p_attribute_rec =>p_cas_rec,
2467 x_case_object_rec =>l_case_object_Rec);
2468 --Call create_case_object_pvt to create Case object_id
2469 -- IF PG_DEBUG < 10 THEN
2470 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2471 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Before Calling Create Case Object PVT');
2472 END IF;
2473 -- IF PG_DEBUG < 10 THEN
2474 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2475 iex_debug_pub.logMessage('ReassignCaseObjects: ' || '---------------------------------');
2476 END IF;
2477 iex_case_objects_pvt.Create_case_objects(
2478 P_Api_Version_Number =>l_api_version_number,
2479 P_Init_Msg_List =>FND_API.G_FALSE,
2480 P_Commit =>FND_API.G_FALSE,
2481 p_validation_level =>P_validation_level,
2482 P_case_object_Rec =>l_case_object_Rec,
2483 x_case_object_id =>l_case_object_id,
2484 X_Return_Status =>l_return_status,
2485 X_Msg_Count =>l_msg_count,
2486 X_Msg_Data =>l_msg_data);
2487 -- IF PG_DEBUG < 10 THEN
2488 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2489 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'After Calling Create Case Object PVT and Status =>'||l_return_status);
2490 END IF;
2491 -- Check return status from the above procedure call
2492 IF l_return_status = FND_API.G_RET_STS_ERROR then
2493 AddFailMsg( p_object => 'CASE OBJECTS',
2494 p_operation => 'INSERT' );
2495 raise FND_API.G_EXC_ERROR;
2496 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2497 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2498 else
2499 x_case_object_id :=l_case_object_id;
2500 -- IF PG_DEBUG < 10 THEN
2501 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2502 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Value of x_case_object_id =>' ||x_case_object_id);
2503 END IF;
2504 END IF;
2505
2506 END IF; -- end of if G_match_Case_id is NOT NULL
2507 ELSE -- if case Definition is invalid then error out.
2508 --How to assign value of parameter,since it is table?
2509 -- IF PG_DEBUG < 10 THEN
2510 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2511 iex_debug_pub.logMessage('ReassignCaseObjects: ' || 'Case Definition is invalid');
2512 END IF;
2513 AddInvalidArgMsg(
2514 p_api_name => l_api_name_full,
2515 p_param_value => 'P_case_definition_tbl',
2516 p_param_name => 'P_case_definition_tbl' );
2517 RAISE FND_API.G_EXC_ERROR;
2518
2519 END IF; --If CheckCaseDef(P_case_definition_tbl)
2520 END IF; -- (l_cas_id IS NOT NULL) OR (l_cas_id <> FND_API.G_MISS_NUM)
2521
2522 -- IF PG_DEBUG < 10 THEN
2523 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2524 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'Case creation over, ready for notification');
2525 END IF;
2526
2527
2528 OPEN c_contract(p_object_id);
2529 FETCH c_contract INTO l_contract_number;
2530 CLOSE c_contract;
2531 -- IF PG_DEBUG < 10 THEN
2532 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2533 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'contract Number'||l_contract_number);
2534 END IF;
2535
2536
2537 OPEN c_getname(l_old_case_id);
2538 FETCH c_getname INTO l_case_agent;
2539 CLOSE c_getname;
2540 -- IF PG_DEBUG < 10 THEN
2541 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2542 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'case agent'||l_case_Agent);
2543 END IF;
2544
2545 --l_case_agent :='KWALKER';
2546
2547 if l_case_agent is not null THEN
2548 send_notification
2549 ( p_OldCaseID => l_old_case_id,
2550 p_NewCaseID => l_cas_id,
2551 p_ContractNumber =>l_contract_number,
2552 p_CaseAgent =>l_case_agent,
2553 x_return_status =>l_return_status);
2554 else
2555 -- IF PG_DEBUG < 10 THEN
2556 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2557 iex_debug_pub.logmessage ('ReassignCaseObjects: ' || 'Could not send notification');
2558 END IF;
2559 l_return_status :='F';
2560 end if;
2561
2562
2563
2564 --
2565 -- End of API body.
2566 --
2567
2568 -- Standard check for p_commit
2569 IF FND_API.to_Boolean( p_commit )
2570 THEN
2571 COMMIT WORK;
2572 END IF;
2573
2574 -- we still want to commit the changes , but send the message indicating notification did
2575 --not happen
2576
2577 if l_return_status <> 'S' THEN
2578
2579 FND_MSG_PUB.initialize;
2580 fnd_message.set_name('IEX', 'IEX_CASE_NOTIFICATION');
2581 fnd_message.set_token('OLD_CASE',l_old_case_id);
2582 fnd_message.set_token('NEW_CASE',l_cas_id);
2583 fnd_message.set_token('CON_NUM',l_contract_number);
2584 fnd_msg_pub.add;
2585 x_return_status := 'N';
2586 End if;
2587
2588
2589 -- Standard call to get message count and if count is 1, get message info.
2590 FND_MSG_PUB.Count_And_Get
2591 ( p_count => x_msg_count,
2592 p_data => x_msg_data
2593 );
2594 -- IF PG_DEBUG < 10 THEN
2595 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2596 IEX_DEBUG_PUB.LogMessage ('ReassignCaseObjects: ' || '*********End of Procedure => '||l_api_name||' *********');
2597 END IF;
2598 EXCEPTION
2599 WHEN FND_API.G_EXC_ERROR THEN
2600 ROLLBACK TO REASSIGNCASEOBJECTS_PUB;
2601 x_return_status := FND_API.G_RET_STS_ERROR ;
2602 x_msg_count := l_msg_count ;
2603 x_msg_data := l_msg_data ;
2604 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2605 p_data => x_msg_data);
2606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607 ROLLBACK TO REASSIGNCASEOBJECTS_PUB;
2608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2609 x_msg_count := l_msg_count ;
2610 x_msg_data := l_msg_data ;
2611 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2612 p_data => x_msg_data);
2613
2614 WHEN OTHERS THEN
2615 ROLLBACK TO REASSIGNCASEOBJECTS_PUB;
2616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2617 x_msg_count := l_msg_count ;
2618 x_msg_data := l_msg_data ;
2619 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,l_api_name);
2620 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2621 p_data => x_msg_data);
2622 END ReassignCaseObjects;
2623
2624
2625 /* Name UpdateCase
2626 ** api : Current version 2.0
2627 ** Purpose To update a case. Change the status from delinquent to current when the case
2628 ** comes out NOCOPY of delinquency
2629 **
2630 */
2631 PROCEDURE UpdateCase(
2632 P_Api_Version_Number IN NUMBER,
2633 P_Init_Msg_List IN VARCHAR2 ,
2634 P_Commit IN VARCHAR2 ,
2635 P_validation_level IN NUMBER ,
2636 p_cas_rec IN CAS_Rec_Type ,
2637 X_Return_Status OUT NOCOPY VARCHAR2,
2638 X_Msg_Count OUT NOCOPY NUMBER,
2639 X_Msg_Data OUT NOCOPY VARCHAR2
2640 ) IS
2641
2642 l_api_name CONSTANT VARCHAR2(30) := 'UpdateCase';
2643 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
2644 l_api_version_number CONSTANT NUMBER := 2.0;
2645 l_cas_id NUMBER ;
2646 l_return_status VARCHAR2(1);
2647 l_msg_count NUMBER;
2648 l_msg_data VARCHAR2(32767);
2649 l_case_object_id NUMBER;
2650 l_case_definition_id NUMBER;
2651 l_cas_rec iex_cases_pvt.cas_rec_type
2652 := iex_cases_pvt.g_miss_cas_rec;
2653 l_object_version_number NUMBER;
2654 BEGIN
2655 -- IF PG_DEBUG < 10 THEN
2656 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2657 IEX_DEBUG_PUB.LogMessage ('UpdateCase: ' || '*********Start of Procedure => '||l_api_name||' *********');
2658 END IF;
2659 -- Standard Start of API savepoint
2660 SAVEPOINT UPDATECASE_PUB;
2661
2662 -- Standard call to check for call compatibility.
2663 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2664 p_api_version_number,
2665 l_api_name,
2666 G_PKG_NAME)
2667 THEN
2668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2669 END IF;
2670
2671
2672 -- Initialize message list if p_init_msg_list is set to TRUE.
2673 IF FND_API.to_Boolean( p_init_msg_list )
2674 THEN
2675 FND_MSG_PUB.initialize;
2676 END IF;
2677
2678 -- IF PG_DEBUG < 10 THEN
2679 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2680 iex_debug_pub.logMessage('UpdateCase: ' || 'After Api compatability Check');
2681 END IF;
2682
2683 -- Initialize API return status to SUCCESS
2684 x_return_status := FND_API.G_RET_STS_SUCCESS;
2685 --
2686 -- API body
2687 --
2688 -- ******************************************************************
2689 -- Validate Environment
2690 -- ******************************************************************
2691 IF FND_GLOBAL.User_Id IS NULL
2692 THEN
2693 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2694 THEN
2695 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
2696 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2697 FND_MSG_PUB.ADD;
2698 END IF;
2699 RAISE FND_API.G_EXC_ERROR;
2700 END IF;
2701
2702 -- IF PG_DEBUG < 10 THEN
2703 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2704 iex_debug_pub.logMessage('UpdateCase: ' || 'After FND_GLOBAL_USER Check');
2705 END IF;
2706
2707 -- Item level validation
2708 IF (p_validation_level > fnd_api.g_valid_level_none) THEN
2709
2710 -- Check for valid cas_id
2711 BEGIN
2712 IF (p_cas_rec.cas_id IS NOT NULL) AND (p_cas_rec.cas_id <> FND_API.G_MISS_NUM) THEN
2713 --May have to check for status_code too.
2714 SELECT cas_id,object_version_number
2715 INTO l_cas_id,l_object_version_number
2716 FROM iex_cases_all_b
2717 WHERE cas_id = p_cas_rec.cas_id
2718 and active_flag ='Y';
2719
2720 ELSE
2721 AddMissingArgMsg(
2722 p_api_name => l_api_name_full,
2723 p_param_name => 'p_cas_rec.cas_id' );
2724 RAISE FND_API.G_EXC_ERROR;
2725 END IF;
2726 EXCEPTION
2727 WHEN NO_DATA_FOUND THEN
2728 AddInvalidArgMsg(
2729 p_api_name => l_api_name_full,
2730 p_param_name => 'p_cas_rec.cas_id' ,
2731 p_param_value => p_cas_rec.cas_id);
2732 RAISE FND_API.G_EXC_ERROR;
2733 fnd_msg_pub.add;
2734 RAISE FND_API.G_EXC_ERROR;
2735 WHEN OTHERS THEN
2736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2737 END;
2738
2739 END IF; --end of item level validation
2740 -- IF PG_DEBUG < 10 THEN
2741 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2742 iex_debug_pub.logMessage('UpdateCase: ' || 'After Item validation ');
2743 END IF;
2744
2745 -- Call update Case PVT to update
2746 PopulateCaseRec(p_attribute_rec =>p_cas_rec,
2747 p_comments => fnd_api.g_miss_char,
2748 p_org_id => fnd_api.g_miss_num,
2749 p_case_established_date => fnd_api.g_miss_date,
2750 x_cas_rec =>l_cas_rec,
2751 p_case_number => fnd_api.g_miss_char);
2752
2753 l_cas_rec.cas_id :=l_cas_id;
2754 l_cas_rec.object_version_number :=l_object_version_number;
2755
2756
2757 -- IF PG_DEBUG < 10 THEN
2758 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2759 iex_debug_pub.logMessage('UpdateCase: ' || 'Before Calling Update PVT');
2760 END IF;
2761 iex_cases_pvt.update_cas
2762 (P_Api_Version_Number =>l_api_version_number,
2763 P_Init_Msg_List =>FND_API.G_FALSE,
2764 P_Commit =>FND_API.G_FALSE,
2765 p_validation_level =>P_validation_level,
2766 P_cas_Rec =>l_cas_Rec,
2767 X_Return_Status =>l_return_status,
2768 X_Msg_Count =>l_msg_count,
2769 X_Msg_Data =>l_msg_data,
2770 xo_object_version_number =>l_object_version_number);
2771 -- IF PG_DEBUG < 10 THEN
2772 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2773 iex_debug_pub.logMessage('UpdateCase: ' || 'After Calling update case PVT and Status =>'||l_return_status);
2774 END IF;
2775
2776 IF l_return_status = FND_API.G_RET_STS_ERROR then
2777 AddFailMsg( p_object => 'CASE ',
2778 p_operation => 'UPDATE' );
2779 raise FND_API.G_EXC_ERROR;
2780 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2781 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2782 END IF;
2783
2784 --
2785 -- End of API body.
2786 --
2787
2788 -- Standard check for p_commit
2789 IF FND_API.to_Boolean( p_commit )
2790 THEN
2791 COMMIT WORK;
2792 END IF;
2793
2794
2795 -- Standard call to get message count and if count is 1, get message info.
2796 FND_MSG_PUB.Count_And_Get
2797 ( p_count => x_msg_count,
2798 p_data => x_msg_data
2799 );
2800 -- IF PG_DEBUG < 10 THEN
2801 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2802 IEX_DEBUG_PUB.LogMessage ('UpdateCase: ' || '*********End of Procedure => '||l_api_name||' *********');
2803 END IF;
2804 EXCEPTION
2805 WHEN FND_API.G_EXC_ERROR THEN
2806 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2807 P_API_NAME => L_API_NAME
2808 ,P_PKG_NAME => G_PKG_NAME
2809 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2810 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
2811 ,X_MSG_COUNT => X_MSG_COUNT
2812 ,X_MSG_DATA => X_MSG_DATA
2813 ,X_RETURN_STATUS => X_RETURN_STATUS);
2814
2815 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2816 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2817 P_API_NAME => L_API_NAME
2818 ,P_PKG_NAME => G_PKG_NAME
2819 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2820 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
2821 ,X_MSG_COUNT => X_MSG_COUNT
2822 ,X_MSG_DATA => X_MSG_DATA
2823 ,X_RETURN_STATUS => X_RETURN_STATUS);
2824
2825 WHEN OTHERS THEN
2826 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2827 P_API_NAME => L_API_NAME
2828 ,P_PKG_NAME => G_PKG_NAME
2829 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2830 ,P_SQLCODE => SQLCODE
2831 ,P_SQLERRM => SQLERRM
2832 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
2833 ,X_MSG_COUNT => X_MSG_COUNT
2834 ,X_MSG_DATA => X_MSG_DATA
2835 ,X_RETURN_STATUS => X_RETURN_STATUS);
2836 END UpdateCase;
2837
2838 /* Name CreateCaseContacts
2839 ** api : Current version 2.0
2840 ** Purpose: To create case Contacts. A table of contacts along with
2841 ** the table of roles is passed.
2842 ** For a case. There could only be 1 primary contact per case
2843 ** the address id and phone for a contact are optional, if it is not passed
2844 ** the procedure picks the default address and phone id based on the contact id.
2845 ** The contact id in the contact_tbl and roles_tbl are party id's with party type of
2846 ** 'party_Relationship'
2847 ** 11/20/01******
2848 ** since a contact could have multiple roles,the primary flag has been moved over
2849 ** to contact roles from contacts table.
2850 ** 01/07/02
2851 ** The contact roles has been descoped, so primary flag has been moved to
2852 ** contacts table and the contact roles table is obsolete.
2853
2854 */
2855 PROCEDURE CreateCasecontacts(
2856 P_Api_Version_Number IN NUMBER,
2857 P_Init_Msg_List IN VARCHAR2 ,
2858 P_Commit IN VARCHAR2 ,
2859 P_validation_level IN NUMBER ,
2860 P_case_contact_tbl IN CASE_CONTACT_TBL_TYPE ,
2861 P_cas_id IN NUMBER ,
2862 X_Return_Status OUT NOCOPY VARCHAR2,
2863 X_Msg_Count OUT NOCOPY NUMBER,
2864 X_Msg_Data OUT NOCOPY VARCHAR2)
2865 IS
2866 l_api_name CONSTANT VARCHAR2(30) := 'CREATECASECONTACTS';
2867 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
2868 l_api_version_number CONSTANT NUMBER := 2.0;
2869 l_cas_id NUMBER :=p_cas_id;
2870 l_return_status VARCHAR2(1);
2871 l_msg_count NUMBER;
2872 l_msg_data VARCHAR2(32767);
2873 l_case_object_id NUMBER;
2874 l_case_contact_id NUMBER;
2875 l_case_contact_role_id NUMBER;
2876 l_case_contact_rec iex_case_contacts_pvt.case_contact_rec_type
2877 := iex_case_contacts_pvt.g_miss_case_contact_rec;
2878 --l_case_contact_role_rec iex_case_contact_roles_pvt.case_contact_role_rec_type
2879 -- := iex_case_contact_roles_pvt.g_miss_case_contact_role_rec;
2880 l_assign BOOLEAN DEFAULT FALSE;
2881 BEGIN
2882 -- IF PG_DEBUG < 10 THEN
2883 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2884 IEX_DEBUG_PUB.LogMessage ('CreateCasecontacts: ' || '*********Start of Procedure => '||l_api_name||' *********');
2885 END IF;
2886 -- Standard Start of API savepoint
2887 SAVEPOINT CREATECASECONTACTS_PUB;
2888
2889 -- Standard call to check for call compatibility.
2890 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2891 p_api_version_number,
2892 l_api_name,
2893 G_PKG_NAME)
2894 THEN
2895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2896 END IF;
2897
2898
2899 -- Initialize message list if p_init_msg_list is set to TRUE.
2900 IF FND_API.to_Boolean( p_init_msg_list )
2901 THEN
2902 FND_MSG_PUB.initialize;
2903 END IF;
2904
2905 -- IF PG_DEBUG < 10 THEN
2906 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2907 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'After Api compatability Check');
2908 END IF;
2909
2910 -- Initialize API return status to SUCCESS
2911 x_return_status := FND_API.G_RET_STS_SUCCESS;
2912 --
2913 -- API body
2914 --
2915 -- ******************************************************************
2916 -- Validate Environment
2917 -- ******************************************************************
2918 IF FND_GLOBAL.User_Id IS NULL
2919 THEN
2920 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2921 THEN
2922 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
2923 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
2924 FND_MSG_PUB.ADD;
2925 END IF;
2926 RAISE FND_API.G_EXC_ERROR;
2927 END IF;
2928 -- IF PG_DEBUG < 10 THEN
2929 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2930 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'After FND_GLOBAL_USER Check');
2931 END IF;
2932 -- Check for required parameter cas_id
2933 IF (p_cas_id IS NULL) OR (p_cas_id = FND_API.G_MISS_NUM) THEN
2934 -- IF PG_DEBUG < 10 THEN
2935 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2936 IEX_DEBUG_PUB.LogMessage('CreateCasecontacts: ' || 'Required Parameter p_cas_id is invalid');
2937 END IF;
2938 AddMissingArgMsg(
2939 p_api_name => l_api_name_full,
2940 p_param_name => 'p_cas_id' );
2941 RAISE FND_API.G_EXC_ERROR;
2942 END IF;
2943 --Call iex_case_contacts_pvt to create contacts
2944 FOR i IN 1..P_case_contact_tbl.COUNT LOOP
2945 l_case_contact_Rec.cas_id :=P_cas_id;
2946 l_case_contact_Rec.contact_party_id :=P_case_contact_tbl(i).contact_party_id;
2947 l_case_contact_Rec.address_id :=P_case_contact_tbl(i).address_id;
2948 l_case_contact_Rec.phone_id :=P_case_contact_tbl(i).phone_id;
2949 l_case_contact_Rec.primary_flag :=P_case_contact_tbl(i).primary_flag;
2950 -- IF PG_DEBUG < 10 THEN
2951 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2952 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'Before Calling Create Case Contacts PVT');
2953 END IF;
2954 -- IF PG_DEBUG < 10 THEN
2955 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2956 iex_debug_pub.logMessage('CreateCasecontacts: ' || '---------------------------------');
2957 END IF;
2958 iex_case_contacts_pvt.create_case_contact(
2959 P_Api_Version_Number =>l_api_version_number,
2960 P_Init_Msg_List =>FND_API.G_FALSE,
2961 P_Commit =>FND_API.G_FALSE,
2962 p_validation_level =>P_validation_level,
2963 p_case_contact_rec =>l_case_contact_Rec,
2964 x_cas_contact_id =>l_case_contact_id,
2965 X_Return_Status =>l_return_status,
2966 X_Msg_Count =>l_msg_count,
2967 X_Msg_Data =>l_msg_data);
2968 -- IF PG_DEBUG < 10 THEN
2969 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2970 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'After Calling Create Case'||
2971 'contacts PVT and Status =>'||l_return_status);
2972 END IF;
2973 IF l_return_status = FND_API.G_RET_STS_ERROR then
2974 AddFailMsg( p_object => 'CASE CONTACTS',
2975 p_operation => 'INSERT' );
2976 raise FND_API.G_EXC_ERROR;
2977 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2978 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2979 /*
2980 ELSE -- if case contact insert = success
2981 --if it is a primary contact
2982 if P_case_contact_tbl(i).primary_flag = 'Y' THEN
2983 l_assign := TRUE;
2984 END IF;
2985
2986 FOR j IN 1..P_case_contact_roles_tbl.COUNT LOOP
2987 -- IF PG_DEBUG < 10 THEN
2988 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2989 iex_debug_pub.LogMessage ('CreateCasecontacts: ' || 'Roles for contact Party_id = '||
2990 P_case_contact_roles_tbl(j).contact_party_id||
2991 'P_case_contact_tbl(i).contact_party_id ='||
2992 P_case_contact_tbl(i).contact_party_id);
2993 END IF;
2994 If P_case_contact_roles_tbl(j).contact_party_id =
2995 P_case_contact_tbl(i).contact_party_id THEN
2996
2997 -- if the contact is primary , assign the first role as the primary
2998 If P_case_contact_tbl(i).primary_flag = 'Y' and l_assign THEN
2999 l_case_contact_role_Rec.primary_role_flag := 'Y';
3000 l_assign := FALSE;
3001 else
3002 l_case_contact_role_Rec.primary_role_flag := 'N';
3003 End if;
3004
3005 l_case_contact_role_rec.cas_contact_id :=l_case_contact_id;
3006 l_case_contact_role_rec.cas_contact_role_code :=P_case_contact_roles_tbl(j).CONTACT_ROLE;
3007
3008
3009 -- IF PG_DEBUG < 10 THEN
3010 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3011 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'Before Calling Create Case Contact roles PVT');
3012 END IF;
3013 -- IF PG_DEBUG < 10 THEN
3014 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3015 iex_debug_pub.logMessage('CreateCasecontacts: ' || '---------------------------------');
3016 END IF;
3017 iex_case_contact_roles_pvt.create_case_contact_roles(
3018 P_Api_Version_Number =>l_api_version_number,
3019 P_Init_Msg_List =>FND_API.G_FALSE,
3020 P_Commit =>FND_API.G_FALSE,
3021 p_validation_level =>P_validation_level,
3022 p_case_contact_role_rec =>l_case_contact_role_Rec,
3023 x_cas_contact_role_id =>l_case_contact_role_id,
3024 X_Return_Status =>l_return_status,
3025 X_Msg_Count =>l_msg_count,
3026 X_Msg_Data =>l_msg_data);
3027 -- IF PG_DEBUG < 10 THEN
3028 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3029 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'After Calling Create Case'||
3030 'contacts roles PVT and Status =>'||l_return_status);
3031 END IF;
3032 IF l_return_status = FND_API.G_RET_STS_ERROR then
3033 AddFailMsg( p_object => 'CASE CONTACT ROLES',
3034 p_operation => 'INSERT' );
3035 raise FND_API.G_EXC_ERROR;
3036 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
3037 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3038 END IF;
3039 END IF; --contact party id are similar
3040 END LOOP; --contact roles table loop
3041 -- IF PG_DEBUG < 10 THEN
3042 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3043 iex_debug_pub.logMessage('CreateCasecontacts: ' || 'After creation of Case'||
3044 'contacts roles for cas_contact_id '||l_case_contact_id);
3045 END IF;
3046 */
3047 END IF; --checking return status of case contact insert
3048 END LOOP;
3049 -- IF PG_DEBUG < 10 THEN
3050 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3051 IEX_DEBUG_PUB.LogMessage ('CreateCasecontacts: ' || '*********End of Procedure => '||l_api_name||' *********');
3052 END IF;
3053
3054 -- Debug Message
3055 -- Standard call to get message count and if count is 1, get message info.
3056 FND_MSG_PUB.Count_And_Get
3057 ( p_count => x_msg_count,
3058 p_data => x_msg_data
3059 );
3060
3061 EXCEPTION
3062 WHEN FND_API.G_EXC_ERROR THEN
3063 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3064 P_API_NAME => L_API_NAME
3065 ,P_PKG_NAME => G_PKG_NAME
3066 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3067 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
3068 ,X_MSG_COUNT => X_MSG_COUNT
3069 ,X_MSG_DATA => X_MSG_DATA
3070 ,X_RETURN_STATUS => X_RETURN_STATUS);
3071
3072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3073 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3074 P_API_NAME => L_API_NAME
3075 ,P_PKG_NAME => G_PKG_NAME
3076 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3077 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
3078 ,X_MSG_COUNT => X_MSG_COUNT
3079 ,X_MSG_DATA => X_MSG_DATA
3080 ,X_RETURN_STATUS => X_RETURN_STATUS);
3081
3082 WHEN OTHERS THEN
3083 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3084 P_API_NAME => L_API_NAME
3085 ,P_PKG_NAME => G_PKG_NAME
3086 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3087 ,P_SQLCODE => SQLCODE
3088 ,P_SQLERRM => SQLERRM
3089 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
3090 ,X_MSG_COUNT => X_MSG_COUNT
3091 ,X_MSG_DATA => X_MSG_DATA
3092 ,X_RETURN_STATUS => X_RETURN_STATUS);
3093
3094 END CreateCasecontacts;
3095
3096 /* Name CheckContract
3097 ** Used by the OKL wrapper to decide whether to call createCaseObjects or
3098 ** reassign Case when Bill_to_address or any case attribute is changed
3099 */
3100 Function CheckContract
3101 (P_ObjectID IN NUMBER
3102 )Return BOOLEAN IS
3103 x_ObjectID iex_case_Objects.object_id%TYPE;
3104 Begin
3105 Select object_id INTO x_ObjectID
3106 from iex_case_objects
3107 where object_id =P_ObjectID;
3108 return TRUE;
3109 EXCEPTION WHEN OTHERS THEN
3110 Return FALSE;
3111
3112 End CheckContract;
3113
3114
3115 END IEX_CASE_UTL_PUB;
3116
3117
[Home] [Help]