1 PACKAGE BODY OCM_GET_EXTRL_DECSN_PUB AS
2 /* $Header: ARCMPEXTB.pls 120.4 2005/12/27 18:28:31 bsarkar noship $ */
3 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
4
5 PROCEDURE debug (
6 p_message_name IN VARCHAR2 ) IS
7 BEGIN
8 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.OCM_CREDIT_REQUEST_UPDATE_PUB' );
9 END;
10
11 PROCEDURE Get_Score
12 ( p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
14 p_commit IN VARCHAR2,
15 p_validation_level IN VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_case_folder_id IN NUMBER,
20 p_score_model_id IN NUMBER Default NULL,
21 p_score IN NUMBER
22 ) IS
23 l_case_folder_type ar_cmgt_case_folders.type%type;
24 l_case_folder_status ar_cmgt_case_folders.status%type;
25 l_data_point_id ar_cmgt_score_dtls.DATA_POINT_ID%type;
26 l_party_id ar_cmgt_case_folders.party_id%type;
27 l_cust_account_id ar_cmgt_case_folders.cust_account_id%type;
28 l_site_use_id ar_cmgt_case_folders.site_use_id%type;
29 l_score_model_id ar_cmgt_case_folders.score_model_id%type;
30 l_data_point_code ar_cmgt_data_points_b.data_point_code%type;
31 BEGIN
32 /* The following validation will be placed into the procedure
33 1. If Score Model Id is passed then it must match with the case folder
34 score_model_id and conatin the External Score data points.
35 If the scoring model does not contain this data points then API should reject the score.
36 2. Case folder Id must be of typed 'CASE'
37 3. The case folder status must be in 'CREATED' or 'SAVED' status.
38 4. Once the score is updated in 'CASE' type the same score need to be updated
39 in 'DATA' type record also.
40 */
41 IF pg_debug = 'Y'
42 THEN
43 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Get_Score(+)');
44 debug ( 'Case Folder ID ' || p_case_folder_id);
45 debug ( 'Score Model ID ' || p_score_model_id);
46 debug ( 'Score ' || p_score);
47 END IF;
48
49 SAVEPOINT CREDIT_SCORE_PVT;
50
51 IF FND_API.to_Boolean( p_init_msg_list )
52 THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57
58 IF p_case_folder_id IS NOT NULL THEN
59 BEGIN
60 select type,
61 status,
62 score_model_id,
63 party_id,
64 cust_account_id,
65 site_use_id
66 into l_case_folder_type,
67 l_case_folder_status,
68 l_score_model_id,
69 l_party_id,
70 l_cust_account_id,
71 l_site_use_id
72 from ar_cmgt_case_folders
73 where case_folder_id = p_case_folder_id ;
74
75 IF l_case_folder_type = 'CASE' and
76 ((l_case_folder_status = 'CREATED') or (l_case_folder_status = 'SAVED')
77 or (l_case_folder_status = 'IN_PROCESS') ) THEN
78 IF p_score_model_id IS NOT NULL and (p_score_model_id = l_score_model_id) THEN
79 BEGIN
80 select sc.DATA_POINT_ID, dp.data_point_code
81 into l_data_point_id, l_data_point_code
82 from ar_cmgt_score_dtls sc, ar_cmgt_data_points_vl dp
83 where sc.SCORE_MODEL_ID = p_score_model_id
84 and sc.data_point_id = dp.data_point_id
85 and dp.data_point_code = 'OCM_EXTERNAL_SCORE';
86
87 update ar_cmgt_cf_dtls
88 set score = p_score,
89 data_point_value = p_score
90 where CASE_FOLDER_ID = p_case_folder_id
91 and DATA_POINT_ID = l_data_point_id;
92
93 update ar_cmgt_cf_dtls
94 set score = p_score,
95 data_point_value = p_score
96 where CASE_FOLDER_ID = ( select CASE_FOLDER_ID
97 from ar_cmgt_case_folders
98 where type = 'DATA'
99 and party_id = l_party_id
100 and cust_account_id = l_cust_account_id
101 and site_use_id = l_site_use_id)
102 and DATA_POINT_ID = l_data_point_id;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_SCORE_MODEL_ID');
106 FND_MSG_PUB.Add;
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 WHEN OTHERS THEN
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
111 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','GET_SCORE : '||SQLERRM);
112 END ;
113
114 ELSE
115 /*Message : Please provide a score model id which matches with the case folder's
116 score model id and conatin the External Score data points. */
117 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_SCORE_MODEL_ID');
118 FND_MSG_PUB.Add;
119 x_return_status := FND_API.G_RET_STS_ERROR;
120
121 END IF;
122 ELSE
123 /*Message : Please provide a case folder id of case folder type 'CASE' and
124 status as 'CREATED' or 'SAVED' */
125 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
126 FND_MSG_PUB.Add;
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 END IF;
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
132 FND_MSG_PUB.Add;
133 x_return_status := FND_API.G_RET_STS_ERROR;
134 WHEN OTHERS THEN
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
137 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','GET_SCORE : '||SQLERRM);
138
139 END ;
140 ELSE
141 /*Messae : Please provide a case folder id of case folder type 'CASE' and
142 status as 'CREATED' or 'SAVED' */
143 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
144 FND_MSG_PUB.Add;
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 END IF;
147
148 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
149 ROLLBACK TO CREDIT_SCORE_PVT;
150 END IF;
151 IF pg_debug = 'Y'
152 THEN
153 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Get_Score(-)');
154 END IF;
155 EXCEPTION
156 WHEN OTHERS THEN
157
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
159 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
160 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','GET_SCORE : '||SQLERRM);
161 FND_MSG_PUB.Add;
162
163 ROLLBACK TO CREDIT_SCORE_PVT;
164
165 END Get_Score;
166
167 PROCEDURE Include_Data_Points
168 ( p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
170 p_commit IN VARCHAR2,
171 p_validation_level IN VARCHAR2,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2,
175 p_case_folder_id IN NUMBER,
176 p_data_point_id IN data_point_id_varray
177 ) IS
178 l_case_folder_type ar_cmgt_case_folders.type%type;
179 l_case_folder_status ar_cmgt_case_folders.status%type;
180 l_data_point_id ar_cmgt_cf_dtls.DATA_POINT_ID%type;
181 BEGIN
182 /* The following validation will be placed into the procedure
183 1. Case folder Id must be of typed 'CASE'
184 2. The case folder status must be in 'CREATED' or 'SAVED' status.
185 3. Data Point Id must be a valid data point Id
186 */
187 IF pg_debug = 'Y'
188 THEN
189 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Include_Data_Points(+)');
190 debug ( 'Case Folder ID ' || p_case_folder_id);
191 -- debug ( 'Data Point IDs ' || p_data_point_id);
192 END IF;
193
194 SAVEPOINT CREDIT_INCLUDE_DP_PVT;
195
196 IF FND_API.to_Boolean( p_init_msg_list )
197 THEN
198 FND_MSG_PUB.initialize;
199 END IF;
200
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202 IF p_case_folder_id IS NOT NULL THEN
203 BEGIN
204 select type,
205 status
206 into l_case_folder_type,
207 l_case_folder_status
208 from ar_cmgt_case_folders
209 where case_folder_id = p_case_folder_id ;
210
211 IF l_case_folder_type = 'CASE' and
212 ((l_case_folder_status = 'CREATED') or (l_case_folder_status = 'SAVED')
213 or (l_case_folder_status = 'IN_PROCESS')) THEN
214 IF p_data_point_id IS NOT NULL THEN
215 FOR i IN 1..p_data_point_id.count
216 LOOP
217 BEGIN
218 select DATA_POINT_ID
219 into l_data_point_id
220 from ar_cmgt_cf_dtls
221 where case_folder_id = p_case_folder_id
222 and DATA_POINT_ID = p_data_point_id(i);
223
224 update ar_cmgt_cf_dtls
225 set included_in_checklist = 'Y'
226 where case_folder_id = p_case_folder_id
227 and data_point_id = l_data_point_id;
228
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN
231 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_DATAPOINT_ID');
232 FND_MSG_PUB.Add;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 WHEN OTHERS THEN
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
237 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Include_Data_Points : '||SQLERRM);
238 END ;
239 END LOOP;
240 ELSE
241 /*Message : Please provide a valid data point id */
242 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_DATAPOINT_ID');
243 FND_MSG_PUB.Add;
244 x_return_status := FND_API.G_RET_STS_ERROR;
245
246 END IF;
247 ELSE
248 /*Message : Please provide a case folder id of case folder type 'CASE' and
249 status as 'CREATED' or 'SAVED' */
250 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
251 FND_MSG_PUB.Add;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 END IF;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
258 FND_MSG_PUB.Add;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 WHEN OTHERS THEN
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
263 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Include_Data_Points : '||SQLERRM);
264
265 END ;
266 ELSE
267 /*Message : Please provide a case folder id of case folder type 'CASE' and
268 status as 'CREATED' or 'SAVED' */
269 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
270 FND_MSG_PUB.Add;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 END IF;
273
274 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
275 ROLLBACK TO CREDIT_INCLUDE_DP_PVT;
276 END IF;
277 IF pg_debug = 'Y'
278 THEN
279 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Include_Data_Points(-)');
280 END IF;
281 EXCEPTION
282 WHEN OTHERS THEN
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
284 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
285 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Include_Data_Points : '||SQLERRM);
286 FND_MSG_PUB.Add;
287
288 ROLLBACK TO CREDIT_INCLUDE_DP_PVT;
289 END ;
290
291 PROCEDURE Get_Recommendations
292 ( p_api_version IN NUMBER,
293 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
294 p_commit IN VARCHAR2,
295 p_validation_level IN VARCHAR2,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2,
299 p_case_folder_id IN NUMBER,
300 p_recommendations_type IN VARCHAR2,
301 p_recommendations_tbl IN credit_recommendation_tbl
302 ) IS
303 l_case_folder_type ar_cmgt_case_folders.type%type;
304 l_case_folder_status ar_cmgt_case_folders.status%type;
305 l_credit_request_id ar_cmgt_case_folders.credit_request_id%type;
306 l_credit_type ar_cmgt_credit_requests.credit_type%type;
307 l_lookup_type fnd_lookups.lookup_type%type;
308 l_application_id ar_cmgt_credit_requests.SOURCE_RESP_APPLN_ID%type;
309
310 BEGIN
311 /* The following validation will be placed into the API
312 1. Case folder Id must be of typed 'CASE'.
313 2. The case folder status must be in 'CREATED' or 'SAVED' status.
314 3. Recommendations type will be validated against the FND lookup type.
315 4. Individual Recommendations will be validated against lookup code of the Recommendations type.
316 */
317 IF pg_debug = 'Y'
318 THEN
319 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Get_Recommendations(+)');
320 debug ( 'Case Folder ID ' || p_case_folder_id);
321 debug ( 'Recommendation Type ' || p_recommendations_type);
322 END IF;
323
324 SAVEPOINT CREDIT_GET_RECO_PVT;
325
326 IF FND_API.to_Boolean( p_init_msg_list )
327 THEN
328 FND_MSG_PUB.initialize;
329 END IF;
330
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332 IF p_case_folder_id IS NOT NULL THEN
333 BEGIN
337 ar_cmgt_credit_requests.credit_type,
334 select ar_cmgt_case_folders.type,
335 ar_cmgt_case_folders.status,
336 ar_cmgt_case_folders.credit_request_id,
338 ar_cmgt_credit_requests.SOURCE_RESP_APPLN_ID
339 into l_case_folder_type,
340 l_case_folder_status,
341 l_credit_request_id,
342 l_credit_type,
343 l_application_id
344 from ar_cmgt_case_folders,
345 ar_cmgt_credit_requests
346 where ar_cmgt_case_folders.case_folder_id = p_case_folder_id ;
347
348 IF l_case_folder_type = 'CASE' and
349 ((l_case_folder_status = 'CREATED') or (l_case_folder_status = 'SAVED')
350 or (l_case_folder_status = 'IN_PROCESS') ) THEN
351 IF p_recommendations_type IS NOT NULL and p_recommendations_tbl IS NOT NULL THEN
352 /* validation of Recommendations type */
353 BEGIN
354 select LOOKUP_TYPE
355 into l_lookup_type
356 from fnd_lookups
357 where lookup_type = p_recommendations_type
358 and enabled_flag = 'Y'
359 and rownum =1;
360
361 FOR i in 1 .. p_recommendations_tbl.count LOOP
362 /*Individual Recommendations will be validated against lookup code of the Recommendations type */
363 select LOOKUP_TYPE
364 into l_lookup_type
365 from fnd_lookups
366 where lookup_type = p_recommendations_type
367 and enabled_flag = 'Y'
368 and lookup_code = p_recommendations_tbl(i).Credit_Recommendation;
369
370 INSERT INTO AR_CMGT_CF_RECOMMENDS
371 (RECOMMENDATION_ID,
372 LAST_UPDATED_BY,
373 LAST_UPDATE_DATE,
374 LAST_UPDATE_LOGIN,
375 CREATION_DATE,
376 CREATED_BY,
377 CASE_FOLDER_ID,
378 CREDIT_REQUEST_ID,
379 CREDIT_REVIEW_DATE,
380 CREDIT_RECOMMENDATION,
381 RECOMMENDATION_VALUE1,
382 RECOMMENDATION_VALUE2,
383 STATUS,
384 CREDIT_TYPE,
385 RECOMMENDATION_NAME,
386 APPLICATION_ID)
387 (SELECT AR_CMGT_CF_RECOMMENDS_S.NEXTVAL,
388 fnd_global.user_id,
389 SYSDATE,
390 fnd_global.login_id,
391 SYSDATE,
392 fnd_global.user_id,
393 p_case_folder_id,
394 l_credit_request_id,
395 SYSDATE,
396 p_recommendations_tbl(i).Credit_Recommendation,
397 p_recommendations_tbl(i).Recommendation_value1,
398 p_recommendations_tbl(i).Recommendation_value2,
399 'O',
400 l_credit_type,
401 p_recommendations_type,
402 l_application_id
403 FROM dual
404 );
405 END LOOP;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_RECOMMENDATIONS');
409 FND_MSG_PUB.Add;
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 WHEN OTHERS THEN
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
414 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Get_Recommendations : '||SQLERRM);
415 END;
416 ELSE
417 /*Message : Please provide a valid data point id */
418 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_RECOMMENDATIONS');
419 FND_MSG_PUB.Add;
420 x_return_status := FND_API.G_RET_STS_ERROR;
421 END IF;
422 ELSE
423 /*Message : Please provide a case folder id of case folder type 'CASE' and
424 status as 'CREATED' or 'SAVED' */
425 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
426 FND_MSG_PUB.Add;
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 END IF;
429
430 EXCEPTION
431 WHEN NO_DATA_FOUND THEN
432 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
433 FND_MSG_PUB.Add;
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 WHEN OTHERS THEN
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
438 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Get_Recommendations : '||SQLERRM);
439
440 END ;
441
442 ELSE
446 FND_MSG_PUB.Add;
443 /*Message : Please provide a case folder id of case folder type 'CASE' and
444 status as 'CREATED' or 'SAVED' */
445 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 END IF;
449 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
450 ROLLBACK TO CREDIT_GET_RECO_PVT;
451 END IF;
452 IF pg_debug = 'Y'
453 THEN
454 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Get_Recommendations(-)');
455 END IF;
456 EXCEPTION
457 WHEN OTHERS THEN
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
459 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
460 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Get_Recommendations : '||SQLERRM);
461 FND_MSG_PUB.Add;
465
462 ROLLBACK TO CREDIT_GET_RECO_PVT;
463
464 END ;
466 PROCEDURE Submit_Case_Folder
467 ( p_api_version IN NUMBER,
468 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
469 p_commit IN VARCHAR2,
470 p_validation_level IN VARCHAR2,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2,
474 p_case_folder_id IN NUMBER
475 ) IS
476 l_case_folder_type ar_cmgt_case_folders.type%type;
477 l_case_folder_status ar_cmgt_case_folders.status%type;
478 l_credit_request_id ar_cmgt_case_folders.credit_request_id%type;
479 BEGIN
480 /* The following validation will be placed into the API
481 1. Case folder Id must be of typed 'CASE'.
482 2. The case folder status must be in 'CREATED' or 'SAVED' status.
483 3. Will update the status of the case folder to Submitted and workflow
484 need to be kicked off and will continue for Approval route.
485 */
486 IF pg_debug = 'Y'
487 THEN
488 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Submit_Case_Folder(+)');
489 debug ( 'Case Folder ID ' || p_case_folder_id);
490 END IF;
491
492 SAVEPOINT CASE_FOLDER_SUBMIT_PVT;
493
494 IF FND_API.to_Boolean( p_init_msg_list )
495 THEN
496 FND_MSG_PUB.initialize;
497 END IF;
498
499 x_return_status := FND_API.G_RET_STS_SUCCESS;
500
501 IF p_case_folder_id IS NOT NULL THEN
502 BEGIN
503 select type,
504 status,
505 credit_request_id
506 into l_case_folder_type,
507 l_case_folder_status,
508 l_credit_request_id
509 from ar_cmgt_case_folders
510 where case_folder_id = p_case_folder_id ;
511
512 IF l_case_folder_type = 'CASE' and
513 ((l_case_folder_status = 'CREATED') or (l_case_folder_status = 'SAVED')
514 or (l_case_folder_status = 'IN_PROCESS') ) THEN
515 /* Update case folder status to 'SUBMIT' and kick of the worlflow */
516 update ar_cmgt_case_folders
517 set status = 'SUBMIT'
518 where case_folder_id = p_case_folder_id ;
519
520 AR_CMGT_WF_ENGINE.START_WORKFLOW
521 (l_credit_request_id ,'SUBMIT');
522 ELSE
523 /*Message : Please provide a case folder id of case folder type 'CASE' and
524 status as 'CREATED' or 'SAVED' */
525 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
526 FND_MSG_PUB.Add;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 END IF;
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
532 FND_MSG_PUB.Add;
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 WHEN OTHERS THEN
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
537 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Submit_Case_Folder : '||SQLERRM);
538 END ;
539
540 ELSE
541 /*Message : Please provide a case folder id of case folder type 'CASE' and
542 status as 'CREATED' or 'SAVED' */
543 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_CASE_FOLDER');
544 FND_MSG_PUB.Add;
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 END IF;
547 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
548 ROLLBACK TO CASE_FOLDER_SUBMIT_PVT;
549 END IF;
550 IF pg_debug = 'Y'
551 THEN
552 debug ( 'OCM_GET_EXTRL_DECSN_PUB.Submit_Case_Folder(-)');
553 END IF;
554 EXCEPTION
555 WHEN OTHERS THEN
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
557 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
558 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Submit_Case_Folder : '||SQLERRM);
559 FND_MSG_PUB.Add;
560 ROLLBACK TO CASE_FOLDER_SUBMIT_PVT;
561
562 END ;
563
564 END OCM_GET_EXTRL_DECSN_PUB;