[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMINATE_PUB
Source
1 PACKAGE BODY OKC_TERMINATE_PUB as
2 /* $Header: OKCPTERB.pls 120.0 2005/05/25 22:39:09 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5
6 FUNCTION is_k_term_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
7 BEGIN
8 return OKC_TERMINATE_PVT.is_k_term_allowed(p_chr_id,p_sts_code);
9 END;
10
11 FUNCTION is_kl_term_allowed(p_cle_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
12 BEGIN
13 return OKC_TERMINATE_PVT.is_kl_term_allowed(p_cle_id,p_sts_code);
14 END;
15
16
17 PROCEDURE terminate_chr(p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2 ,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_terminate_in_parameters_tbl IN terminate_in_parameters_tbl,
23 p_do_commit IN VARCHAR2
24 ) is
25
26 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
27 l_msg_count number := 0;
28
29 BEGIN
30
31 OKC_API.init_msg_list(p_init_msg_list);
32
33 x_return_status := OKC_API.G_RET_STS_SUCCESS;
34
35 FOR i IN p_terminate_in_parameters_tbl.first..p_terminate_in_parameters_tbl.last LOOP
36
37 IF p_terminate_in_parameters_tbl.exists(i) THEN
38
39 OKC_TERMINATE_PUB.terminate_chr(p_api_version => 1,
40 p_init_msg_list => OKC_API.G_FALSE,
41 x_return_status => l_return_status,
42 x_msg_count => x_msg_count,
43 x_msg_data => x_msg_data,
44 p_terminate_in_parameters_rec => p_terminate_in_parameters_tbl(i),
45 p_do_commit => p_do_commit);
46
47
48 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
49 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
50 x_return_status := l_return_status;
51 raise G_EXCEPTION_HALT_VALIDATION;
52 ELSE
53 x_return_status := l_return_status;
54 END IF;
55 END IF;
56 END IF; -- if exists
57
58 -- Bug 3658108
59 OKC_CVM_PVT.clear_g_transaction_id;
60
61 END LOOP;
62
63 IF P_DO_COMMIT = OKC_API.G_TRUE THEN
64 COMMIT;
65 END IF;
66
67 EXCEPTION
68 WHEN G_EXCEPTION_HALT_VALIDATION THEN
69 NULL;
70 WHEN OTHERS THEN
71 OKC_API.set_message(p_app_name => g_app_name,
72 p_msg_name => g_unexpected_error,
73 p_token1 => g_sqlcode_token,
74 p_token1_value => sqlcode,
75 p_token2 => g_sqlerrm_token,
76 p_token2_value => sqlerrm);
77 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
78 end;
79
80 PROCEDURE terminate_chr( p_api_version IN NUMBER,
81 p_init_msg_list IN VARCHAR2 ,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2,
85 p_terminate_in_parameters_rec IN terminate_in_parameters_rec,
86 p_do_commit IN VARCHAR2
87 ) is
88
89 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
90 l_terminate_in_parameters_rec terminate_in_parameters_rec := p_terminate_in_parameters_rec;
91 l_api_name constant varchar2(30) := 'terminate_chr';
92 l_chr_id number;
93 BEGIN
94
95 x_return_status := OKC_API.G_RET_STS_SUCCESS;
96
97 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
98 p_init_msg_list,
99 '_PUB',
100 x_return_status);
101
102 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
103 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
104 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
105 raise OKC_API.G_EXCEPTION_ERROR;
106 END IF;
107
108 -- Call user hook FOR BEFORE
109 g_terminate_in_parameters_rec := l_terminate_in_parameters_rec;
110
111 OKC_UTIL.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'B');
112
113 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
114 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
115 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
116 raise OKC_API.G_EXCEPTION_ERROR;
117 END IF;
118
119 l_terminate_in_parameters_rec := g_terminate_in_parameters_rec;
120 l_terminate_in_parameters_rec.p_contract_id := p_terminate_in_parameters_rec.p_contract_id;
121
122
123 OKC_TERMINATE_PVT.terminate_chr(p_api_version => 1,
124 p_init_msg_list => OKC_API.G_FALSE,
125 x_return_status => l_return_status,
126 x_msg_count => x_msg_count,
127 x_msg_data => x_msg_data,
128 p_terminate_in_parameters_rec => l_terminate_in_parameters_rec );
129
130 IF l_return_status = 'W' then
131 x_return_status := 'W';
132 END IF;
133
134 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
135 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
136 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
137 RAISE OKC_API.G_EXCEPTION_ERROR;
138 END IF;
139
140
141 OKC_UTIL.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'A');
142
143 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
144 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
145 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
146 raise OKC_API.G_EXCEPTION_ERROR;
147 END IF;
148
149 IF P_DO_COMMIT = OKC_API.G_TRUE THEN
150 COMMIT;
151 END IF;
152 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
153
154 EXCEPTION
155 WHEN OKC_API.G_EXCEPTION_ERROR THEN
156 x_return_status := OKC_API.HANDLE_EXCEPTIONS
157 (l_api_name,
158 G_PKG_NAME,
159 'OKC_API.G_RET_STS_ERROR',
160 x_msg_count,
161 x_msg_data,
162 '_PUB');
163 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
164
165 x_return_status := OKC_API.HANDLE_EXCEPTIONS
166 (l_api_name,
167 G_PKG_NAME,
168 'OKC_API.G_RET_STS_UNEXP_ERROR',
169 x_msg_count,
170 x_msg_data,
171 '_PUB');
172 WHEN OTHERS THEN
173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
174 (l_api_name,
175 G_PKG_NAME,
176 'OTHERS',
177 x_msg_count,
178 x_msg_data,
179 '_PUB');
180 end;
181
182
183 PROCEDURE validate_chr( p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2 ,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 p_terminate_in_parameters_tbl IN terminate_in_parameters_tbl ) is
189
190 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
191
192 BEGIN
193
194 okc_api.init_msg_list(p_init_msg_list);
195
196 x_return_status := OKC_API.G_RET_STS_SUCCESS;
197
198 FOR i IN p_terminate_in_parameters_tbl.first..p_terminate_in_parameters_tbl.last
199 LOOP
200
201 IF p_terminate_in_parameters_tbl.exists(i) THEN
202
203 OKC_TERMINATE_PVT.validate_chr(p_api_version => p_api_version,
204 p_init_msg_list => OKC_API.G_FALSE,
205 x_return_status => l_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208 p_terminate_in_parameters_rec => p_terminate_in_parameters_tbl(i) );
209
210
211 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
212 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
213 x_return_status := l_return_status;
214 raise G_EXCEPTION_HALT_VALIDATION;
215 ELSE
216 x_return_status := l_return_status;
217 END IF;
218 END IF;
219
220 END IF; -- if exists
221 END LOOP;
222
223 EXCEPTION
224 WHEN G_EXCEPTION_HALT_VALIDATION THEN
225 null;
226 WHEN OTHERS THEN
227 OKC_API.set_message(p_app_name => g_app_name,
228 p_msg_name => g_unexpected_error,
229 p_token1 => g_sqlcode_token,
230 p_token1_value => sqlcode,
231 p_token2 => g_sqlerrm_token,
232 p_token2_value => sqlerrm);
233 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
234 end;
235
236
237 PROCEDURE VALIDATE_CHR( p_api_version IN NUMBER,
238 p_init_msg_list IN VARCHAR2 ,
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2,
242 p_terminate_in_parameters_rec IN terminate_in_parameters_rec ) is
243 BEGIN
244
245 okc_terminate_pvt.validate_chr(p_api_version => p_api_version,
246 p_init_msg_list => OKC_API.G_FALSE,
247 x_return_status => x_return_status,
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data,
250 p_terminate_in_parameters_rec => p_terminate_in_parameters_rec);
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 OKC_API.set_message(p_app_name => g_app_name,
255 p_msg_name => g_unexpected_error,
256 p_token1 => g_sqlcode_token,
257 p_token1_value => sqlcode,
258 p_token2 => g_sqlerrm_token,
259 p_token2_value => sqlerrm);
260
261 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
262 END;
263
264 PROCEDURE terminate_cle( p_api_version IN NUMBER,
265 p_init_msg_list IN VARCHAR2 ,
266 x_return_status OUT NOCOPY VARCHAR2,
267 x_msg_count OUT NOCOPY NUMBER,
268 x_msg_data OUT NOCOPY VARCHAR2,
269 p_terminate_in_parameters_tbl IN terminate_in_cle_tbl,
270 p_do_commit IN VARCHAR2
271 ) IS
272
273 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
274
275 cursor cur_lock_header(p_id okc_k_headers_v.id%type) is
276 select id
277 from okc_k_headers_b
278 where id = p_id
279 FOR update of id nowait;
280
281 l_id number;
282
283 cursor cur_lock_rules (p_id okc_k_headers_v.id%type) is
284 select id
285 from okc_rules_b
286 where dnz_chr_id = p_id
287 FOR update of attribute1 nowait;
288
289 E_Resource_Busy EXCEPTION;
290 PRAGMA EXCEPTION_init(E_Resource_Busy, -00054);
291 l_api_name constant varchar2(30) := 'terminate_cle_pub';
292 BEGIN
293
294 OKC_API.init_msg_list(p_init_msg_list);
295
296 x_return_status := OKC_API.G_RET_STS_SUCCESS;
297
298 l_return_status := OKC_API.START_ACTIVITY( l_api_name,
299 p_init_msg_list,
300 '_PUB',
301 x_return_status );
302
303 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
304 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
305 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
306 RAISE OKC_API.G_EXCEPTION_ERROR;
307 END IF;
308
309 l_id := p_terminate_in_parameters_tbl.first;
310
311 OPEN cur_lock_header(p_terminate_in_parameters_tbl(l_id).p_dnz_chr_id);
312 FETCH cur_lock_header INto l_id;
313 CLOSE cur_lock_header;
314
315 l_id := p_terminate_in_parameters_tbl.first;
316
317 OPEN cur_lock_rules(p_terminate_in_parameters_tbl(l_id).p_dnz_chr_id);
318 FETCH cur_lock_rules INto l_id;
319 CLOSE cur_lock_rules;
320
321 FOR i IN p_terminate_in_parameters_tbl.first..p_terminate_in_parameters_tbl.last LOOP
322
323 if p_terminate_in_parameters_tbl.exists(i) then
324
325 OKC_TERMINATE_PUB.terminate_cle(p_api_version => 1,
326 p_init_msg_list => OKC_API.G_FALSE,
327 x_return_status => l_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data,
330 p_terminate_in_parameters_rec => p_terminate_in_parameters_tbl(i));
331
332 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
333 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
334 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
335 RAISE OKC_API.G_EXCEPTION_ERROR;
336 END IF;
337
338 END IF; -- if exists
339
340 END LOOP;
341
342 IF P_DO_COMMIT = OKC_API.G_TRUE THEN
343 COMMIT;
344 END IF;
345 EXCEPTION
346 WHEN E_Resource_Busy THEN
347
348 x_return_status := okc_api.g_ret_sts_error;
349
350 OKC_API.set_message(G_FND_APP,
351 G_FORM_UNABLE_TO_RESERVE_REC);
352 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
353
354 WHEN OKC_API.G_EXCEPTION_ERROR THEN
355 x_return_status := OKC_API.HANDLE_EXCEPTIONS
356 (l_api_name,
357 G_PKG_NAME,
358 'OKC_API.G_RET_STS_ERROR',
359 x_msg_count,
360 x_msg_data,
361 '_PUB');
362 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
363 x_return_status := OKC_API.HANDLE_EXCEPTIONS
364 (l_api_name,
365 G_PKG_NAME,
366 'OKC_API.G_RET_STS_UNEXP_ERROR',
367 x_msg_count,
368 x_msg_data,
369 '_PUB');
370 WHEN OTHERS THEN
371 x_return_status := OKC_API.HANDLE_EXCEPTIONS
372 (l_api_name,
373 G_PKG_NAME,
374 'OTHERS',
375 x_msg_count,
376 x_msg_data,
377 '_PUB');
378 END;
379
380 PROCEDURE terminate_cle( p_api_version IN NUMBER,
381 p_init_msg_list IN VARCHAR2 ,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_count OUT NOCOPY NUMBER,
384 x_msg_data OUT NOCOPY VARCHAR2,
385 p_terminate_in_parameters_rec IN terminate_in_cle_rec,
386 p_do_commit IN varchar2
387 ) is
388
389 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
390 l_terminate_in_parameters_rec terminate_in_cle_rec := p_terminate_in_parameters_rec;
391 l_api_name constant varchar2(30) := 'terminate_cle';
392 l_chr_id number;
393
394 BEGIN
395
396 x_return_status := OKC_API.G_RET_STS_SUCCESS;
397
398 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
399 p_init_msg_list,
400 '_PUB',
401 x_return_status);
402
403 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
404 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
405 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
406 raise OKC_API.G_EXCEPTION_ERROR;
407 END IF;
408
409 -- Call user hook FOR BEFORE
410 g_terminate_in_cle_rec := l_terminate_in_parameters_rec;
411
412 OKC_UTIL.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'B');
413
414 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
415 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
416 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
417 raise OKC_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 l_terminate_in_parameters_rec := g_terminate_in_cle_rec;
421 l_terminate_in_parameters_rec.p_cle_id := p_terminate_in_parameters_rec.p_cle_id;
422
423 OKC_TERMINATE_PVT.terminate_cle(p_api_version => 1,
424 p_init_msg_list => OKC_API.G_FALSE,
425 x_return_status => l_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data,
428 p_terminate_in_parameters_rec => l_terminate_in_parameters_rec );
429
430
431 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
432 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
433 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
434 RAISE OKC_API.G_EXCEPTION_ERROR;
435 END IF;
436
437
438 OKC_UTIL.call_user_hook(l_return_status, g_pkg_name, l_api_name, 'A');
439
440 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
441 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
442 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
443 raise OKC_API.G_EXCEPTION_ERROR;
444 END IF;
445
446 IF P_DO_COMMIT = OKC_API.G_TRUE THEN
447 COMMIT;
448 END IF;
449
450 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
451
452 EXCEPTION
453 WHEN OKC_API.G_EXCEPTION_ERROR THEN
454 x_return_status := OKC_API.HANDLE_EXCEPTIONS
455 (l_api_name,
456 G_PKG_NAME,
457 'OKC_API.G_RET_STS_ERROR',
458 x_msg_count,
459 x_msg_data,
460 '_PUB');
461 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
462 x_return_status := OKC_API.HANDLE_EXCEPTIONS
463 (l_api_name,
464 G_PKG_NAME,
465 'OKC_API.G_RET_STS_UNEXP_ERROR',
466 x_msg_count,
467 x_msg_data,
468 '_PUB');
469 WHEN OTHERS THEN
470 x_return_status := OKC_API.HANDLE_EXCEPTIONS
471 (l_api_name,
472 G_PKG_NAME,
473 'OTHERS',
474 x_msg_count,
475 x_msg_data,
476 '_PUB');
477 end;
478
479 PROCEDURE validate_cle( p_api_version IN NUMBER,
480 p_init_msg_list IN VARCHAR2 ,
481 x_return_status OUT NOCOPY VARCHAR2,
482 x_msg_count OUT NOCOPY NUMBER,
483 x_msg_data OUT NOCOPY VARCHAR2,
484 p_terminate_in_parameters_tbl IN terminate_in_cle_tbl) is
485
486 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
487
488 BEGIN
489
490 okc_api.init_msg_list(p_init_msg_list);
491
492 x_return_status := OKC_API.G_RET_STS_SUCCESS;
493
494 FOR i IN p_terminate_in_parameters_tbl.first..p_terminate_in_parameters_tbl.last LOOP
495
496 IF p_terminate_in_parameters_tbl.exists(i) THEN
497
498 OKC_TERMINATE_PUB.validate_cle(p_api_version => p_api_version,
499 p_init_msg_list => OKC_API.G_FALSE,
500 x_return_status => l_return_status,
501 x_msg_count => x_msg_count,
502 x_msg_data => x_msg_data,
503 p_terminate_in_parameters_rec => p_terminate_in_parameters_tbl(i) );
504
505 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
506 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
507 x_return_status := l_return_status;
508 raise G_EXCEPTION_HALT_VALIDATION;
509 ELSE
510 x_return_status := l_return_status;
511 END IF;
512 END IF;
513 END IF; --if exists
514 END LOOP;
515 EXCEPTION
516 WHEN G_EXCEPTION_HALT_VALIDATION THEN
517 NULL;
518 WHEN OTHERS THEN
519 OKC_API.set_message(p_app_name => g_app_name,
520 p_msg_name => g_unexpected_error,
521 p_token1 => g_sqlcode_token,
522 p_token1_value => sqlcode,
523 p_token2 => g_sqlerrm_token,
524 p_token2_value => sqlerrm);
525 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
526 end;
527
528
529
530
531
532 PROCEDURE validate_cle( p_api_version IN NUMBER,
533 p_init_msg_list IN VARCHAR2 ,
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2,
537 p_terminate_in_parameters_rec IN terminate_in_cle_rec ) is
538 BEGIN
539
540 OKC_TERMINATE_PVT.validate_cle(p_api_version => p_api_version,
541 p_init_msg_list => OKC_API.G_FALSE,
542 x_return_status => x_return_status,
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data,
545 p_terminate_in_parameters_rec => p_terminate_in_parameters_rec );
546
547 EXCEPTION
548 WHEN OTHERS THEN
549 OKC_API.set_message(p_app_name => g_app_name,
550 p_msg_name => g_unexpected_error,
551 p_token1 => g_sqlcode_token,
552 p_token1_value => sqlcode,
553 p_token2 => g_sqlerrm_token,
554 p_token2_value => sqlerrm);
555
556 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
557 END;
558
559 END OKC_TERMINATE_PUB;