[Home] [Help]
PACKAGE BODY: APPS.OKL_SEC_AGREMNT_BOOK_PVT
Source
1 Package body OKL_SEC_AGREMNT_BOOK_PVT as
2 /* $Header: OKLRSZBB.pls 120.2 2007/12/21 14:10:44 kthiruva ship $ */
3
4 -------------------------------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 -------------------------------------------------------------------------------------------------
7 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
8 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
9 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
10 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
14 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UPPERCASE_REQ';
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
16 ------------------------------------------------------------------------------------
17 -- GLOBAL EXCEPTION
18 ------------------------------------------------------------------------------------
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
21 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
22 G_API_VERSION CONSTANT NUMBER := 1.0;
23 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
24
25 -- GLOBAL VARIABLES
26 -----------------------------------------------------------------------------------
27
28 Procedure execute_qa_check_list(
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34 p_qcl_id IN NUMBER,
35 p_chr_id IN NUMBER,
36 x_msg_tbl OUT NOCOPY OKL_QA_CHECK_PUB.msg_tbl_type) IS
37
38 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_QA_CHECK_LIST';
39 l_api_version CONSTANT NUMBER := 1;
40 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
41 l_PassStatus Varchar2(30) := 'INCOMPLETE'; --'PASSED';
42 l_FailStatus Varchar2(256) := 'INCOMPLETE';
43 severity VARCHAR2(1);
44 l_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
45 j NUMBER;
46
47
48 Cursor l_dltype_csr ( chrId NUMBER ) IS
49 select khr.deal_type
50 from okc_K_headers_v chr,
51 okl_K_headers khr
52 where chr.id = khr.id
53 and chr.id = chrId;
54
55 l_dltype_rec l_dltype_csr%ROWTYPE;
56
57 BEGIN
58
59 x_return_status := OKL_API.START_ACTIVITY(
60 p_api_name => l_api_name,
61 p_pkg_name => g_pkg_name,
62 p_init_msg_list => p_init_msg_list,
63 l_api_version => l_api_version,
64 p_api_version => p_api_version,
65 p_api_type => G_API_TYPE,
66 x_return_status => x_return_status);
67
68 -- check if activity started successfully
69 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
70 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
71 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
72 raise OKL_API.G_EXCEPTION_ERROR;
73 End If;
74
75 OPEN l_dltype_csr( p_chr_id);
76 FETCH l_dltype_csr INTO l_dltype_rec;
77 If ( l_dltype_csr%NOTFOUND) Then
78 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
79 End If;
80
81 OKL_QA_CHECK_PUB.execute_qa_check_list(
82 p_api_version,
83 p_init_msg_list,
84 x_return_status,
85 x_msg_count,
86 x_msg_data,
87 p_qcl_id,
88 p_chr_id,
89 x_msg_tbl);
90
91
92 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
93 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
94 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
95 RAISE OKL_API.G_EXCEPTION_ERROR;
96 END IF;
97
98
99 FOR i in 1..x_msg_tbl.COUNT
100 LOOP
101 If ( x_msg_tbl(i).name <> 'Check Email Address' ) Then
102 l_msg_tbl(l_msg_tbl.COUNT + 1) := x_msg_tbl(i);
103 End If;
104 END LOOP;
105 x_msg_tbl := l_msg_tbl;
106
107 severity := 'S';
108 FOR i in 1..x_msg_tbl.COUNT
109 LOOP
110 If ( x_msg_tbl(i).error_status = 'E' ) Then
111 severity := 'E';
112 Exit;
113 End If;
114 END LOOP;
115
116
117
118
119 IF((x_return_status = Okl_Api.G_RET_STS_SUCCESS) AND (severity='S')) THEN
120 OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts(
121 l_api_version,
122 p_init_msg_list,
123 x_return_status,
124 x_msg_count,
125 x_msg_data,
126 l_PassStatus,
127 p_chr_id );
128 ELSE
129 OKL_SEC_AGREEMENT_PVT.update_sec_agreement_sts(
130 l_api_version,
131 p_init_msg_list,
132 x_return_status,
133 x_msg_count,
134 x_msg_data,
135 l_FailStatus,
136 p_chr_id );
137 End If;
138
139 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
140 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
141 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
142 RAISE OKL_API.G_EXCEPTION_ERROR;
143 END IF;
144
145 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
146 ---
147 Exception
148 when OKL_API.G_EXCEPTION_ERROR then
149 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
150 p_api_name => l_api_name,
151 p_pkg_name => g_pkg_name,
152 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155 p_api_type => g_api_type);
156
157 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
158 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
159 p_api_name => l_api_name,
160 p_pkg_name => g_pkg_name,
161 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_api_type => g_api_type);
165
166 when OTHERS then
167 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
168 p_api_name => l_api_name,
169 p_pkg_name => g_pkg_name,
170 p_exc_name => 'OTHERS',
171 x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data,
173 p_api_type => g_api_type);
174
175
176 END execute_qa_check_list;
177
178 Procedure activate_contract(
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2,
184 p_chr_id IN VARCHAR2) IS
185
186 l_api_name CONSTANT VARCHAR2(30) := 'ACTIVATE_INV_AGMNT';
187 l_api_version CONSTANT NUMBER := 1.0;
188 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
189
190 l_isAllowed BOOLEAN;
191 l_PassStatus VARCHAR2(100):= 'BOOKED';
192 l_FailStatus VARCHAR2(100) := 'APPROVED';
193 l_event VARCHAR2(100) := OKL_CONTRACT_STATUS_PUB.G_K_ACTIVATE;
194 l_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
195 x_message VARCHAR2(256);
196
197
198 BEGIN
199
200 x_return_status := OKL_API.G_RET_STS_SUCCESS;
201
202 x_return_status := OKL_API.START_ACTIVITY(
203 p_api_name => l_api_name,
204 p_pkg_name => g_pkg_name,
205 p_init_msg_list => p_init_msg_list,
206 l_api_version => l_api_version,
207 p_api_version => p_api_version,
208 p_api_type => G_API_TYPE,
209 x_return_status => x_return_status);
210
211 -- check if activity started successfully
212 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
213 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
214 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
215 raise OKL_API.G_EXCEPTION_ERROR;
216 End If;
217
218 okl_sec_agreement_pvt.activate_sec_agreement(
219 p_api_version => l_api_version,
220 p_init_msg_list => p_init_msg_list,
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data,
224 p_khr_id => p_chr_id );
225
226 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
228 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
229 RAISE OKL_API.G_EXCEPTION_ERROR;
230 END IF;
231
232 OKL_API.set_message(
233 p_app_name => G_APP_NAME,
234 p_msg_name => 'OKL_LLA_AC_SUCCESS');
235
236 x_return_status := OKL_API.G_RET_STS_SUCCESS;
237
238
239 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
240
241 Exception
242 when OKL_API.G_EXCEPTION_ERROR then
243 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
244 p_api_name => l_api_name,
245 p_pkg_name => g_pkg_name,
246 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
247 x_msg_count => x_msg_count,
248 x_msg_data => x_msg_data,
249 p_api_type => g_api_type);
250
251 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
252 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
253 p_api_name => l_api_name,
254 p_pkg_name => g_pkg_name,
255 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
256 x_msg_count => x_msg_count,
257 x_msg_data => x_msg_data,
258 p_api_type => g_api_type);
259
260 when OTHERS then
261 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
262 p_api_name => l_api_name,
263 p_pkg_name => g_pkg_name,
264 p_exc_name => 'OTHERS',
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data,
267 p_api_type => g_api_type);
268
269
270 END activate_contract;
271
272 Procedure check_reconciled(
273 p_api_version IN NUMBER,
274 p_init_msg_list IN VARCHAR2,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 p_chr_id IN VARCHAR2) IS
279
280 l_api_name CONSTANT VARCHAR2(30) := 'RECONCILED';
281 l_api_version CONSTANT NUMBER := 1.0;
282 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
283
284 l_isAllowed BOOLEAN;
285 l_PassStatus VARCHAR2(100):= 'BOOKED';
286 l_FailStatus VARCHAR2(100) := 'APPROVED';
287 l_event VARCHAR2(100) := OKL_CONTRACT_STATUS_PUB.G_K_ACTIVATE;
288 l_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
289 x_message VARCHAR2(256);
290
291 pool_rec pool_csr%ROWTYPE;
292 x_reconciled VARCHAR2(1);
293
294 BEGIN
295 x_return_status := OKL_API.G_RET_STS_SUCCESS;
296
297 x_return_status := OKL_API.START_ACTIVITY(
298 p_api_name => l_api_name,
299 p_pkg_name => g_pkg_name,
300 p_init_msg_list => p_init_msg_list,
301 l_api_version => l_api_version,
302 p_api_version => p_api_version,
303 p_api_type => G_API_TYPE,
304 x_return_status => x_return_status);
305
306 -- check if activity started successfully
307 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
308 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
309 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
310 raise OKL_API.G_EXCEPTION_ERROR;
311 End If;
312
313 OPEN pool_csr ( p_chr_id );
314 FETCH pool_csr INTO pool_rec;
315 CLOSE pool_csr;
316
317 OKL_POOL_PVT.reconcile_contents(
318 p_api_version => l_api_version,
319 p_init_msg_list => p_init_msg_list,
320 x_return_status => x_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data,
323 p_pol_id => pool_rec.id,
324 x_reconciled => x_reconciled );
325
326 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
327 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
328 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
329 RAISE OKL_API.G_EXCEPTION_ERROR;
330 END IF;
331
332 IF ( x_reconciled = OKL_API.G_TRUE ) Then
333 x_return_status := OKL_API.G_RET_STS_ERROR;
334 OKL_API.set_message(
335 p_app_name => G_APP_NAME,
336 p_msg_name => 'OKL_LLA_RECONCILED');
337 raise OKL_API.G_EXCEPTION_ERROR;
338 End If;
339
340 x_return_status := OKL_API.G_RET_STS_SUCCESS;
341
342
343 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
344
345 Exception
346 when OKL_API.G_EXCEPTION_ERROR then
347 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
348 p_api_name => l_api_name,
349 p_pkg_name => g_pkg_name,
350 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 p_api_type => g_api_type);
354
355 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
356 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
357 p_api_name => l_api_name,
358 p_pkg_name => g_pkg_name,
359 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data,
362 p_api_type => g_api_type);
363
364 when OTHERS then
365 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
366 p_api_name => l_api_name,
367 p_pkg_name => g_pkg_name,
368 p_exc_name => 'OTHERS',
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data,
371 p_api_type => g_api_type);
372
373
374 END check_reconciled;
375
376 Procedure check_event(
377 p_api_version IN NUMBER,
378 p_init_msg_list IN VARCHAR2,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382 p_event IN VARCHAR2,
383 p_chr_id IN VARCHAR2) IS
384
385 l_api_name CONSTANT VARCHAR2(30) := 'EVENT';
386 l_api_version CONSTANT NUMBER := 1.0;
387 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
388
389 l_isAllowed BOOLEAN;
390 l_PassStatus VARCHAR2(100):= 'BOOKED';
391 l_FailStatus VARCHAR2(100) := 'APPROVED';
392 l_event VARCHAR2(100) := OKL_CONTRACT_STATUS_PUB.G_K_ACTIVATE;
393 l_cimv_tbl OKL_OKC_MIGRATION_PVT.cimv_tbl_type;
394 x_message VARCHAR2(256);
395
396
397 BEGIN
398
399 x_return_status := OKL_API.G_RET_STS_SUCCESS;
400
401 x_return_status := OKL_API.START_ACTIVITY(
402 p_api_name => l_api_name,
403 p_pkg_name => g_pkg_name,
404 p_init_msg_list => p_init_msg_list,
405 l_api_version => l_api_version,
406 p_api_version => p_api_version,
407 p_api_type => G_API_TYPE,
408 x_return_status => x_return_status);
409
410 -- check if activity started successfully
411 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
412 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
413 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
414 raise OKL_API.G_EXCEPTION_ERROR;
415 End If;
416
417 x_return_status := OKL_API.G_RET_STS_SUCCESS;
418
419
420 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
421
422 Exception
423 when OKL_API.G_EXCEPTION_ERROR then
424 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
425 p_api_name => l_api_name,
426 p_pkg_name => g_pkg_name,
427 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data,
430 p_api_type => g_api_type);
431
432 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
433 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
434 p_api_name => l_api_name,
435 p_pkg_name => g_pkg_name,
436 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
437 x_msg_count => x_msg_count,
438 x_msg_data => x_msg_data,
439 p_api_type => g_api_type);
440
441 when OTHERS then
442 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
443 p_api_name => l_api_name,
444 p_pkg_name => g_pkg_name,
445 p_exc_name => 'OTHERS',
446 x_msg_count => x_msg_count,
447 x_msg_data => x_msg_data,
448 p_api_type => g_api_type);
449
450
451 END check_event;
452
453 --Added by kthiruva on 18-Dec-2007
454 -- New method to validate an add request on an active investor agreement
455 --Bug 6691554 - Start of Changes
456 Procedure validate_add_request(
457 p_api_version IN NUMBER,
458 p_init_msg_list IN VARCHAR2,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 p_chr_id IN NUMBER)
463 IS
464
465 l_api_name CONSTANT VARCHAR2(30) := 'validate_add_request';
466 l_api_version CONSTANT NUMBER := 1.0;
467 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
468
469 l_event VARCHAR2(100) := OKL_CONTRACT_STATUS_PUB.G_K_ACTIVATE;
470 x_message VARCHAR2(256);
471
472
473 BEGIN
474
475 x_return_status := OKL_API.G_RET_STS_SUCCESS;
476
477 x_return_status := OKL_API.START_ACTIVITY(
478 p_api_name => l_api_name,
479 p_pkg_name => g_pkg_name,
480 p_init_msg_list => p_init_msg_list,
481 l_api_version => l_api_version,
482 p_api_version => p_api_version,
483 p_api_type => G_API_TYPE,
484 x_return_status => x_return_status);
485
486 -- check if activity started successfully
487 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
488 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
489 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
490 raise OKL_API.G_EXCEPTION_ERROR;
491 End If;
492
493 okl_sec_agreement_pvt.validate_add_request(
494 p_api_version => l_api_version,
495 p_init_msg_list => p_init_msg_list,
496 x_return_status => x_return_status,
497 x_msg_count => x_msg_count,
498 x_msg_data => x_msg_data,
499 p_chr_id => p_chr_id );
500
501 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
502 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
503 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
504 RAISE OKL_API.G_EXCEPTION_ERROR;
505 END IF;
506
507 x_return_status := OKL_API.G_RET_STS_SUCCESS;
508
509 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
510
511 Exception
512 when OKL_API.G_EXCEPTION_ERROR then
513 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
514 p_api_name => l_api_name,
515 p_pkg_name => g_pkg_name,
516 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
517 x_msg_count => x_msg_count,
518 x_msg_data => x_msg_data,
519 p_api_type => g_api_type);
520
521 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
522 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
523 p_api_name => l_api_name,
524 p_pkg_name => g_pkg_name,
525 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
526 x_msg_count => x_msg_count,
527 x_msg_data => x_msg_data,
528 p_api_type => g_api_type);
529
530 when OTHERS then
531 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
532 p_api_name => l_api_name,
533 p_pkg_name => g_pkg_name,
534 p_exc_name => 'OTHERS',
535 x_msg_count => x_msg_count,
536 x_msg_data => x_msg_data,
537 p_api_type => g_api_type);
538
539
540 END validate_add_request;
541 -- Bug 6691554 - End of Changes
542
543
544 End OKL_SEC_AGREMNT_BOOK_PVT;