[Home] [Help]
PACKAGE BODY: APPS.OKL_BOOK_CONTROLLER_PVT
Source
1 PACKAGE BODY OKL_BOOK_CONTROLLER_PVT AS
2 /* $Header: OKLRBCTB.pls 120.8.12010000.4 2009/11/27 06:06:17 rpillay ship $ */
3 --------------------------------------------------------------------------------
4 --LOCAL DATASTRUCTURES
5 --------------------------------------------------------------------------------
6 TYPE msg_token_rec IS RECORD (
7 token_name VARCHAR2(50) := OKL_API.G_MISS_CHAR,
8 token_value VARCHAR2(300) := OKL_API.G_MISS_CHAR);
9
10 TYPE msg_token_tbl IS TABLE OF msg_token_rec
11 INDEX BY BINARY_INTEGER;
12
13 -------------------------------------------------------------------------------------------------
14 -- GLOBAL MESSAGE CONSTANTS
15 -------------------------------------------------------------------------------------------------
16 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
17 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
18 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
19
20
21 -----------------------------------------------------------------------------
22 -- FUNCTION get_message
23 -----------------------------------------------------------------------------
24 -- Start of comments
25 --
26 -- Function Name : get_message
27 -- Description : This procedure gets the translated mesg to
28 -- be put into output files.
29 -- Business Rules :
30 -- Parameters : p_msg_name,p_token_name,p_token_value
31 -- Version : 1.0
32 -- History : XX-XXX-XXXX vthiruva Created
33 -- End of comments
34
35 FUNCTION get_message(
36 p_msg_name IN VARCHAR2,
37 p_token_name IN VARCHAR2,
38 p_token_value IN VARCHAR2)
39 RETURN VARCHAR2 IS
40
41 l_message VARCHAR2(2000);
42 BEGIN
43 IF p_msg_name IS NOT NULL THEN
44 --create mesg to print in o/p files of requests
45 fnd_message.set_name(application => G_APP_NAME,
46 name => p_msg_name);
47
48 fnd_message.set_token(token => p_token_name,
49 value => p_token_value);
50
51 l_message := fnd_message.get();
52 END IF;
53 RETURN l_message;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 RETURN NULL;
58 END get_message;
59
60 -----------------------------------------------------------------------------
61 -- FUNCTION get_message
62 -----------------------------------------------------------------------------
63 -- Start of comments
64 --
65 -- Function Name : get_message
66 -- Description : This procedure gets the translated mesg to
67 -- be put into output files.
68 -- Business Rules :
69 -- Parameters : p_msg_name,p_token_name,p_token_value
70 -- Version : 1.0
71 -- History : XX-XXX-XXXX vthiruva Created
72 -- End of comments
73
74 FUNCTION get_message(
75 p_msg_name IN VARCHAR2,
76 p_msg_tokens_tbl IN msg_token_tbl)
77 RETURN VARCHAR2 IS
78
79 l_message VARCHAR2(2000);
80 BEGIN
81 IF p_msg_name IS NOT NULL THEN
82 --create mesg for to print in o/p filse of requests
83 fnd_message.set_name(application => G_APP_NAME,
84 name => p_msg_name);
85
86 FOR i IN p_msg_tokens_tbl.FIRST..p_msg_tokens_tbl.LAST
87 LOOP
88 fnd_message.set_token(token => p_msg_tokens_tbl(i).token_name,
89 value => p_msg_tokens_tbl(i).token_value);
90
91 END LOOP;
92 l_message := fnd_message.get();
93 END IF;
94 RETURN l_message;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 RETURN NULL;
99 END get_message;
100
101 -----------------------------------------------------------------------------
102 -- FUNCTION get_batch_id
103 -----------------------------------------------------------------------------
104 -- Start of comments
105 --
106 -- Function Name : get_batch_id
107 -- Description : This procedure generates the batch id for controller
108 -- trx table from sequence.
109 -- Business Rules :
110 -- Parameters :
111 -- Version : 1.0
112 -- History : XX-XXX-XXXX vthiruva Created
113 -- End of comments
114
115 FUNCTION get_batch_id RETURN NUMBER IS
116 l_batch_num OKL_BOOK_CONTROLLER_TRX.BATCH_NUMBER%TYPE;
117
118 CURSOR get_batch_num IS
119 SELECT OKL_BKG_CONTL_SEQ.NEXTVAL
120 FROM DUAL;
121
122 BEGIN
123 --get batch id for conc requests from seq
124 OPEN get_batch_num;
125 FETCH get_batch_num INTO l_batch_num;
126 CLOSE get_batch_num;
127
128 RETURN l_batch_num;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 RETURN null;
133 END get_batch_id;
134
135 -----------------------------------------------------------------------------
136 -- FUNCTION populate_ctrl_trx_rec
137 -----------------------------------------------------------------------------
138 -- Start of comments
139 --
140 -- Function Name : populate_ctrl_trx_rec
141 -- Description : This populates book_controller_trx record
142 -- Business Rules :
143 -- Parameters : p_batch_num,p_srl_num,p_khr_id,p_prog_name,
144 -- p_prog_short_name
145 -- Version : 1.0
146 -- History : XX-XXX-XXXX vthiruva Created
147 -- End of comments
148
149 FUNCTION populate_ctrl_trx_rec(
150 p_batch_num IN OKL_BOOK_CONTROLLER_TRX.BATCH_NUMBER%TYPE,
151 p_srl_num IN OKL_BOOK_CONTROLLER_TRX.PROCESSING_SRL_NUMBER%TYPE,
152 p_khr_id IN OKC_K_HEADERS_B.ID%TYPE,
153 p_prog_name IN OKL_BOOK_CONTROLLER_TRX.PROGRAM_NAME%TYPE,
154 p_prog_short_name IN OKL_BOOK_CONTROLLER_TRX.PROG_SHORT_NAME%TYPE,
155 p_active_flag IN VARCHAR2 DEFAULT NULL,
156 p_progress_status IN OKL_BOOK_CONTROLLER_TRX.PROGRESS_STATUS%TYPE)
157 RETURN bct_rec_type IS
158
159 l_bct_rec bct_rec_type;
160 BEGIN
161 --populate book_controller_trx record
162 l_bct_rec.user_id := fnd_global.user_id;
163 l_bct_rec.org_id := mo_global.get_current_org_id;
164 l_bct_rec.batch_number := p_batch_num;
165 l_bct_rec.processing_srl_number := p_srl_num;
166 l_bct_rec.khr_id := p_khr_id;
167 l_bct_rec.program_name := p_prog_name;
168 l_bct_rec.prog_short_name := p_prog_short_name;
169 l_bct_rec.progress_status := p_progress_status;
170 l_bct_rec.active_flag := p_active_flag;
171
172 RETURN l_bct_rec;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 null;
177 END populate_ctrl_trx_rec;
178
179
180 -----------------------------------------------------------------------------
181 -- PROCEDURE populate_book_ctrl_trx
182 -----------------------------------------------------------------------------
183 -- Start of comments
184 --
185 -- Procedure Name : populate_book_ctrl_trx
186 -- Description : Procedure to insert the processes to be executed for the
187 -- requested contract stage.
188 -- Business Rules :
189 -- Parameters : p_khr_id,p_cont_stage,p_draft_journal_entry,
190 -- p_curr_sts_code,x_batch_number
191 -- Version : 1.0
192 -- History : XX-XXX-XXXX vthiruva Created
193 -- End of comments
194
195 PROCEDURE populate_book_ctrl_trx(
196 p_api_version IN NUMBER,
197 p_init_msg_list IN VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 p_khr_id IN okc_k_headers_b.id%TYPE,
202 p_cont_stage IN VARCHAR2,
203 p_draft_journal_entry IN VARCHAR2,
204 p_curr_sts_code IN VARCHAR2,
205 x_batch_number OUT NOCOPY NUMBER) IS
206
207 l_api_name CONSTANT VARCHAR2(30) := 'populate_book_ctrl_trx';
208 l_api_version CONSTANT NUMBER := 1.0;
209 l_batch_num OKL_BOOK_CONTROLLER_TRX.BATCH_NUMBER%TYPE;
210 l_bct_tbl bct_tbl_type;
211 lx_bct_tbl bct_tbl_type;
212 i NUMBER;
213
214 CURSOR c_book_ctrl_trx(p_khr_id NUMBER) IS
215 SELECT *
216 FROM okl_book_controller_trx
217 WHERE khr_id = p_khr_id
218 AND NVL(active_flag,'N') = 'Y';
219
220 l_book_ctrl_trx c_book_ctrl_trx%ROWTYPE;
221 l_qa_progress_status okl_book_controller_trx.progress_status%TYPE;
222 l_ut_progress_status okl_book_controller_trx.progress_status%TYPE;
223 l_st_progress_status okl_book_controller_trx.progress_status%TYPE;
224 l_bk_progress_status okl_book_controller_trx.progress_status%TYPE;
225 lx_batch_number NUMBER;
226
227 BEGIN
228 x_batch_number := null;
229 x_return_status := OKL_API.G_RET_STS_SUCCESS;
230
231 x_return_status := OKL_API.START_ACTIVITY(
232 p_api_name => l_api_name,
233 p_pkg_name => g_pkg_name,
234 p_init_msg_list => p_init_msg_list,
235 l_api_version => l_api_version,
236 p_api_version => p_api_version,
237 p_api_type => g_api_type,
238 x_return_status => x_return_status);
239
240 -- check if activity started successfully
241 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
243 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
244 RAISE OKL_API.G_EXCEPTION_ERROR;
245 END IF;
246
247 -- Initialize records in okl_book_controller_trx table
248
249 OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx(
250 p_api_version => p_api_version,
251 p_init_msg_list => p_init_msg_list,
252 x_return_status => x_return_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data,
255 p_khr_id => p_khr_id,
256 x_batch_number => lx_batch_number);
257
258 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
259 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
260 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
261 RAISE OKL_API.G_EXCEPTION_ERROR;
262 END IF;
263
264 OPEN c_book_ctrl_trx(p_khr_id => p_khr_id);
265 LOOP
266 FETCH c_book_ctrl_trx INTO l_book_ctrl_trx;
267 IF (l_book_ctrl_trx.prog_short_name = 'OKLBCTQA') THEN
268 l_qa_progress_status := l_book_ctrl_trx.progress_status;
269 ELSIF (l_book_ctrl_trx.prog_short_name = 'OKLBCTUT') THEN
270 l_ut_progress_status := l_book_ctrl_trx.progress_status;
271 ELSIF (l_book_ctrl_trx.prog_short_name = 'OKLBCTST') THEN
272 l_st_progress_status := l_book_ctrl_trx.progress_status;
273 ELSIF (l_book_ctrl_trx.prog_short_name = 'OKLBCTBK') THEN
274 l_bk_progress_status := l_book_ctrl_trx.progress_status;
275 END IF;
276 EXIT WHEN c_book_ctrl_trx%NOTFOUND;
277 END LOOP;
278 CLOSE c_book_ctrl_trx;
279
280 --get new batch number from sequence
281 IF (l_qa_progress_status <> 'COMPLETE' OR l_ut_progress_status <> 'COMPLETE' OR l_st_progress_status <> 'COMPLETE'
282 OR l_bk_progress_status <> 'COMPLETE') THEN
283 l_batch_num := get_batch_id;
284 END IF;
285
286 IF(l_batch_num IS NULL OR l_batch_num = OKL_API.G_MISS_NUM) THEN
287 --raise error if batch number could not be generated from seq.
288 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: batch number could not be generated');
289 RAISE OKL_API.G_EXCEPTION_ERROR;
290 END IF;
291
292 i := 0;
293 IF (p_cont_stage IN ('OKLBCTQA','OKLBCTUT','OKLBCTST','OKLBCTBK') AND l_qa_progress_status <> 'COMPLETE') THEN
294 --populate record for QA Validation
295 i := i+1;
296 l_bct_tbl(i) := populate_ctrl_trx_rec(
297 p_batch_num => l_batch_num,
298 p_srl_num => 10,
299 p_khr_id => p_khr_id,
300 p_prog_name => 'OKLBCTQA',
301 p_prog_short_name => 'OKLBCTQA',
302 p_progress_status => 'PENDING');
303 END IF;
304
305 IF (p_cont_stage IN ('OKLBCTUT','OKLBCTST','OKLBCTBK') AND l_ut_progress_status <> 'COMPLETE') THEN
306 --populate record for Calculate Upfront Tax
307 i := i+1;
308 l_bct_tbl(i) := populate_ctrl_trx_rec(
309 p_batch_num => l_batch_num,
310 p_srl_num => 20,
311 p_khr_id => p_khr_id,
312 p_prog_name => 'OKLBCTUT',
313 p_prog_short_name => 'OKLBCTUT',
314 p_progress_status => 'PENDING');
315 END IF;
316
317 IF (p_cont_stage IN ('OKLBCTST','OKLBCTBK') AND l_st_progress_status <> 'COMPLETE') THEN
318 --populate record for Price Contract
319 i := i+1;
320 l_bct_tbl(i) := populate_ctrl_trx_rec(
321 p_batch_num => l_batch_num,
322 p_srl_num => 30,
323 p_khr_id => p_khr_id,
324 p_prog_name => 'OKLBCTST',
325 p_prog_short_name => 'OKLBCTST',
326 p_progress_status => 'PENDING');
327 END IF;
328
329 IF (p_cont_stage = 'OKLBCTBK' AND l_bk_progress_status <> 'COMPLETE') THEN
330 IF (p_curr_sts_code <> 'APPROVED') THEN
331 --populate record for Approval
332 i := i+1;
333 l_bct_tbl(i) := populate_ctrl_trx_rec(
334 p_batch_num => l_batch_num,
335 p_srl_num => 40,
336 p_khr_id => p_khr_id,
337 p_prog_name => 'OKLBCTAP',
338 p_prog_short_name => 'OKLBCTAP',
339 p_progress_status => 'PENDING');
340 END IF;
341 --populate record for Activation
342 i := i+1;
343 l_bct_tbl(i) := populate_ctrl_trx_rec(
344 p_batch_num => l_batch_num,
345 p_srl_num => 50,
346 p_khr_id => p_khr_id,
347 p_prog_name => 'OKLBCTBK',
348 p_prog_short_name => 'OKLBCTBK',
349 p_progress_status => 'PENDING');
350
351 END IF;
352
353 IF l_bct_tbl.COUNT > 0 THEN
354 --insert records into controller tranasction table for the batch
355 okl_bct_pvt.insert_row(
356 p_api_version => p_api_version,
357 p_init_msg_list => p_init_msg_list,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 p_bct_tbl => l_bct_tbl,
362 x_bct_tbl => lx_bct_tbl);
363
364 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
366 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
367 RAISE OKL_API.G_EXCEPTION_ERROR;
368 END IF;
369 COMMIT;
370 ELSE
371 FND_FILE.PUT_LINE(FND_FILE.LOG,'contract already in desired status');
372 END IF;
373
374 x_batch_number := l_batch_num;
375
376 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
377 x_msg_data => x_msg_data);
378
379 EXCEPTION
380 WHEN OKL_API.G_EXCEPTION_ERROR THEN
381 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
382 p_api_name => l_api_name,
383 p_pkg_name => g_pkg_name,
384 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data,
387 p_api_type => g_api_type);
388
389 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
390 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
391 p_api_name => l_api_name,
392 p_pkg_name => g_pkg_name,
393 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data,
396 p_api_type => g_api_type);
397
398 WHEN OTHERS THEN
399 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
400 p_api_name => l_api_name,
401 p_pkg_name => g_pkg_name,
402 p_exc_name => 'OTHERS',
403 x_msg_count => x_msg_count,
404 x_msg_data => x_msg_data,
405 p_api_type => g_api_type);
406
407 END populate_book_ctrl_trx;
408
409
410 -----------------------------------------------------------------------------
411 -- PROCEDURE update_book_ctrl_trx
412 -----------------------------------------------------------------------------
413 -- Start of comments
414 --
415 -- Procedure Name : update_book_ctrl_trx
416 -- Description : Procedure to update controller transaction table with
417 -- the concurrent request id and status of the individual
418 -- concurrent requests.
419 -- Business Rules :
420 -- Parameters : p_batch_num,p_srl_num,p_prog_status,p_request_id,
421 -- x_return_status
422 -- Version : 1.0
423 -- History : XX-XXX-XXXX vthiruva Created
424 -- End of comments
425
426 PROCEDURE update_book_ctrl_trx(
427 p_batch_num IN okl_book_controller_trx.batch_number%TYPE,
428 p_srl_num IN okl_book_controller_trx.processing_srl_number%TYPE,
429 p_prog_status IN okl_book_controller_trx.progress_status%TYPE,
430 p_request_id IN okl_book_controller_trx.conc_req_id%TYPE,
431 x_return_status OUT NOCOPY VARCHAR2) IS
432
433 l_api_name CONSTANT VARCHAR2(30) := 'update_book_ctrl_trx';
434 l_api_version CONSTANT NUMBER := 1.0;
435 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
436 l_init_msg_list VARCHAR2(1) := 'T';
437 l_msg_count NUMBER;
438 l_msg_data VARCHAR2(2000);
439 l_bct_rec bct_rec_type;
440 x_bct_rec bct_rec_type;
441
442 BEGIN
443 x_return_status := OKL_API.G_RET_STS_SUCCESS;
444 --start activity
445 x_return_status := OKL_API.START_ACTIVITY(
446 p_api_name => l_api_name,
447 p_pkg_name => g_pkg_name,
448 p_init_msg_list => l_init_msg_list,
449 l_api_version => l_api_version,
450 p_api_version => l_api_version,
451 p_api_type => g_api_type,
452 x_return_status => l_return_status);
453
454 --raise exception if error during start activity
455 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
456 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
457 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
458 RAISE OKL_API.G_EXCEPTION_ERROR;
459 END IF;
460
461 --check batch number is not null
462 IF (p_batch_num IS NULL OR p_batch_num = OKL_API.G_MISS_NUM) THEN
463 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error:batch number is null in call to update okl_book_controller_trx');
464 RAISE OKL_API.G_EXCEPTION_ERROR;
465 ELSE
466 l_bct_rec.batch_number := p_batch_num;
467 END IF;
468
469 --check processing serial number is not null
470 IF (p_srl_num IS NULL OR p_srl_num = OKL_API.G_MISS_NUM) THEN
471 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error:processing srl number is null in call to update okl_book_controller_trx');
472 RAISE OKL_API.G_EXCEPTION_ERROR;
473 ELSE
474 l_bct_rec.processing_srl_number := p_srl_num;
475 END IF;
476
477 --if request status is not null, update request status
478 IF (p_prog_status IS NOT NULL AND p_prog_status <> OKL_API.G_MISS_CHAR) THEN
479 l_bct_rec.progress_status := p_prog_status;
480 END IF;
481
482 --if request id is not null, update request id
483 IF (p_request_id IS NOT NULL AND p_request_id <> OKL_API.G_MISS_NUM) THEN
484 l_bct_rec.conc_req_id := p_request_id;
485 END IF;
486
487 --call TAPI to update okl_book_controller_trx
488 okl_bct_pvt.update_row(
489 p_api_version => l_api_version,
490 p_init_msg_list => l_init_msg_list,
491 x_return_status => l_return_status,
492 x_msg_count => l_msg_count,
493 x_msg_data => l_msg_data,
494 p_bct_rec => l_bct_rec,
495 x_bct_rec => x_bct_rec);
496
497 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
498 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
499 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
500 RAISE OKL_API.G_EXCEPTION_ERROR;
501 END IF;
502 COMMIT;
503
504 --end activity
505 OKL_API.END_ACTIVITY(x_msg_count => l_msg_count,
506 x_msg_data => l_msg_data);
507
508 EXCEPTION
509 WHEN OKL_API.G_EXCEPTION_ERROR THEN
510 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
511 p_api_name => l_api_name,
512 p_pkg_name => g_pkg_name,
513 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
514 x_msg_count => l_msg_count,
515 x_msg_data => l_msg_data,
516 p_api_type => g_api_type);
517
518 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
519 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
520 p_api_name => l_api_name,
521 p_pkg_name => g_pkg_name,
522 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
523 x_msg_count => l_msg_count,
524 x_msg_data => l_msg_data,
525 p_api_type => g_api_type);
526
527 WHEN OTHERS THEN
528 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
529 p_api_name => l_api_name,
530 p_pkg_name => g_pkg_name,
531 p_exc_name => 'OTHERS',
532 x_msg_count => l_msg_count,
533 x_msg_data => l_msg_data,
534 p_api_type => g_api_type);
535
536 END update_book_ctrl_trx;
537
538 -----------------------------------------------------------------------------
539 -- PROCEDURE submit_controller_prg1
540 -----------------------------------------------------------------------------
541 -- Start of comments
542 --
543 -- Procedure Name : submit_controller_prg1
544 -- Description : Procedure to submit request for controller program 1
545 -- Business Rules :
546 -- Parameters : p_khr_id,p_cont_stage,p_draft_journal_entry
547 -- Version : 1.0
548 -- History : XX-XXX-XXXX vthiruva Created
549 -- End of comments
550
551 PROCEDURE submit_controller_prg1(
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2,
557 p_khr_id IN okc_k_headers_b.id%TYPE,
558 p_cont_stage IN VARCHAR2,
559 p_draft_journal_entry IN VARCHAR2) IS
560
561 l_api_name CONSTANT VARCHAR2(30) := 'submit_controller_prg1';
562 l_api_version CONSTANT NUMBER := 1.0;
563 l_req_id NUMBER := 0;
564
565 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
566 l_init_msg_list VARCHAR2(1) := 'T';
567 l_batch_number okl_book_controller_trx.batch_number%TYPE;
568 l_msg_count NUMBER;
569 l_msg_data VARCHAR2(2000);
570 l_curr_sts_code okc_k_headers_b.sts_code%TYPE;
571
572 --cursor the fetch the current status of the contract
573 CURSOR get_curr_sts_code(p_khr_id okc_k_headers_b.id%TYPE) IS
574 SELECT sts_code
575 FROM okc_k_headers_b
576 WHERE id = p_khr_id;
577
578
579 BEGIN
580 x_return_status := OKL_API.G_RET_STS_SUCCESS;
581 x_return_status := OKL_API.START_ACTIVITY(
582 p_api_name => l_api_name,
583 p_pkg_name => g_pkg_name,
584 p_init_msg_list => p_init_msg_list,
585 l_api_version => l_api_version,
586 p_api_version => p_api_version,
587 p_api_type => g_api_type,
588 x_return_status => x_return_status);
589
590 --raise exception if error during start activity
591 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
592 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
593 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
594 RAISE OKL_API.G_EXCEPTION_ERROR;
595 END IF;
596
597 --fetch the current status of the contract
598 OPEN get_curr_sts_code(p_khr_id);
599 FETCH get_curr_sts_code INTO l_curr_sts_code;
600 IF get_curr_sts_code%NOTFOUND THEN
601 l_curr_sts_code := 'XXX'; --dummy value which does not match any status
602 END IF;
603 CLOSE get_curr_sts_code;
604
605 --populate controller transaction table with the steps to be executed
606 --to get the contract to the requested stage.
607 populate_book_ctrl_trx(
608 p_api_version => l_api_version,
609 p_init_msg_list => l_init_msg_list,
610 x_return_status => l_return_status,
611 x_msg_count => l_msg_count,
612 x_msg_data => l_msg_data,
613 p_khr_id => p_khr_id,
614 p_cont_stage => p_cont_stage,
615 p_draft_journal_entry => p_draft_journal_entry,
616 p_curr_sts_code => l_curr_sts_code,
617 x_batch_number => l_batch_number);
618
619 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
620 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to populate_book_ctrl_trx');
621 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
622 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
623 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to populate_book_ctrl_trx');
624 RAISE OKL_API.G_EXCEPTION_ERROR;
625 END IF;
626
627 --submit concurrent request for Controller Program 1
628 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
629 l_req_id := fnd_request.submit_request(
630 application => 'OKL',
631 program => 'OKLBCTC1',
632 description => 'Controller Program 1',
633 argument1 => p_khr_id,
634 argument2 => p_cont_stage,
635 argument3 => p_draft_journal_entry,
636 argument4 => 'UI');
637
638 --Raise Error if the request has not been submitted successfully.
639 IF l_req_id = 0 THEN
640 okl_api.set_message(p_app_name => 'OKL',
641 p_msg_name => 'OKL_CONC_REQ_ERROR',
642 p_token1 => 'PROG_NAME',
643 p_token1_value => 'Controller Program 1',
644 p_token2 => 'REQUEST_ID',
645 p_token2_value => l_req_id);
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648
649 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
650 x_msg_data => x_msg_data);
651
652 EXCEPTION
653 WHEN OKL_API.G_EXCEPTION_ERROR THEN
654 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
655 p_api_name => l_api_name,
656 p_pkg_name => g_pkg_name,
657 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
658 x_msg_count => x_msg_count,
659 x_msg_data => x_msg_data,
660 p_api_type => g_api_type);
661
662 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
663 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
664 p_api_name => l_api_name,
665 p_pkg_name => g_pkg_name,
666 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
667 x_msg_count => x_msg_count,
668 x_msg_data => x_msg_data,
669 p_api_type => g_api_type);
670
671 WHEN OTHERS THEN
672 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
673 p_api_name => l_api_name,
674 p_pkg_name => g_pkg_name,
675 p_exc_name => 'OTHERS',
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 p_api_type => g_api_type);
679 END submit_controller_prg1;
680
681 -----------------------------------------------------------------------------
682 -- PROCEDURE submit_controller_prg2
683 -----------------------------------------------------------------------------
684 -- Start of comments
685 --
686 -- Procedure Name : submit_controller_prg2
687 -- Description : Procedure to submit request for controller program 2.
688 -- Called from Approval workflow
689 -- Business Rules :
690 -- Parameters : p_khr_id
691 -- Version : 1.0
692 -- History : XX-XXX-XXXX vthiruva Created
693 -- End of comments
694
695 PROCEDURE submit_controller_prg2(
696 p_api_version IN NUMBER,
697 p_init_msg_list IN VARCHAR2,
698 x_return_status OUT NOCOPY VARCHAR2,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 p_khr_id IN okc_k_headers_b.id%TYPE) IS
702
703 --cursor to check if the contract passed is being
704 --booked through concurrent requests and has been
705 --requested for Activation.
706 CURSOR book_ctrl_trx_csr(
707 p_khr_id okl_book_controller_trx.khr_id%TYPE) IS
708 SELECT '1'
709 FROM okl_book_controller_trx
710 WHERE khr_id = p_khr_id
711 AND progress_status = 'PENDING'
712 AND prog_short_name = 'OKLBCTBK'
713 AND NVL(active_flag,'N') = 'N';
714
715 l_api_name CONSTANT VARCHAR2(30) := 'submit_controller_prg2';
716 l_api_version CONSTANT NUMBER := 1.0;
717 l_req_id NUMBER := 0;
718 l_dummy VARCHAR2(1);
719 l_conc_req_activate BOOLEAN;
720
721 BEGIN
722 x_return_status := OKL_API.G_RET_STS_SUCCESS;
723 x_return_status := OKL_API.START_ACTIVITY(
724 p_api_name => l_api_name,
725 p_pkg_name => g_pkg_name,
726 p_init_msg_list => p_init_msg_list,
727 l_api_version => l_api_version,
728 p_api_version => p_api_version,
729 p_api_type => g_api_type,
730 x_return_status => x_return_status);
731
732 -- check if activity started successfully
733 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
734 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
735 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
736 RAISE OKL_API.G_EXCEPTION_ERROR;
737 END IF;
738
739 l_conc_req_activate := FALSE;
740
741 --check if contract is being booked thru conc requests
742 OPEN book_ctrl_trx_csr(p_khr_id);
743 FETCH book_ctrl_trx_csr INTO l_dummy;
744 IF book_ctrl_trx_csr%FOUND THEN
745 l_conc_req_activate := TRUE;
746 END IF;
747 CLOSE book_ctrl_trx_csr;
748
749 --if booking being triggered from concurrent request..
750 --submit request for contract activation
751 IF(l_conc_req_activate = TRUE) THEN
752 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
753 l_req_id := fnd_request.submit_request(
754 application => 'OKL',
755 program => 'OKLBCTC2',
756 description => 'Controller Program 2',
757 argument1 => p_khr_id);
758
759 IF l_req_id = 0 THEN
760 -- Raise Error if the request has not been submitted successfully.
761 Okl_Api.set_message(p_app_name => 'OKL',
762 p_msg_name => 'OKL_CONC_REQ_ERROR',
763 p_token1 => 'PROG_NAME',
764 p_token1_value => 'Controller Program 2',
765 p_token2 => 'REQUEST_ID',
766 p_token2_value => l_req_id);
767 RAISE OKL_API.G_EXCEPTION_ERROR;
768 END IF;
769 END IF;
770
771 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data);
773
774 EXCEPTION
775 WHEN OKL_API.G_EXCEPTION_ERROR THEN
776
777 --close all open cursors
778 IF book_ctrl_trx_csr%ISOPEN THEN
779 CLOSE book_ctrl_trx_csr;
780 END IF;
781 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
782 p_api_name => l_api_name,
783 p_pkg_name => g_pkg_name,
784 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data,
787 p_api_type => g_api_type);
788
789 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
790
791 --close all open cursors
792 IF book_ctrl_trx_csr%ISOPEN THEN
793 CLOSE book_ctrl_trx_csr;
794 END IF;
795 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
796 p_api_name => l_api_name,
797 p_pkg_name => g_pkg_name,
798 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
799 x_msg_count => x_msg_count,
800 x_msg_data => x_msg_data,
801 p_api_type => g_api_type);
802
803 WHEN OTHERS THEN
804
805 --close all open cursors
806 IF book_ctrl_trx_csr%ISOPEN THEN
807 CLOSE book_ctrl_trx_csr;
808 END IF;
809 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
810 p_api_name => l_api_name,
811 p_pkg_name => g_pkg_name,
812 p_exc_name => 'OTHERS',
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data,
815 p_api_type => g_api_type);
816
817 END submit_controller_prg2;
818
819 -----------------------------------------------------------------------------
820 -- PROCEDURE submit_request
821 -----------------------------------------------------------------------------
822 -- Start of comments
823 --
824 -- procedure Name : submit_request
825 -- Description : Common procedure to submit all the concurrent requests
826 -- Business Rules :
827 -- Parameters : p_program_name,p_description,p_khr_id,p_batch_number
828 -- p_serial_num,x_req_status
829 -- Version : 1.0
830 -- History : XX-XXX-XXXX vthiruva Created
831 -- End of comments
832
833 PROCEDURE submit_request(
834 p_program_name IN VARCHAR2,
835 p_description IN VARCHAR2,
836 p_khr_id IN okc_k_headers_b.id%TYPE,
837 p_batch_number IN NUMBER,
838 p_serial_num IN NUMBER,
839 x_req_status OUT NOCOPY VARCHAR2) IS
840
841 l_request_id NUMBER := 0;
842 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
843 --parameters for wait logic
844 l_req_status BOOLEAN;
845 l_phase VARCHAR2(10);
846 l_status VARCHAR2(10);
847 l_dev_phase VARCHAR2(10);
848 l_dev_status VARCHAR2(10);
849 l_message VARCHAR2(1000);
850 l_data VARCHAR2(2000);
851 l_msg_index_out NUMBER;
852 l_msg_count NUMBER;
853 l_msg_data VARCHAR2(2000);
854 l_api_version NUMBER := 1.0;
855 l_init_msg_list VARCHAR2(1) := OKL_API.G_TRUE;
856 l_program_name OKL_BOOK_CONTROL_TRX_ALL.prog_short_name%TYPE;
857
858 BEGIN
859 x_req_status := 'S';
860
861 IF (p_program_name = 'OKLBCTAP') THEN
862 l_program_name := 'OKLBCTBK';
863 ELSE
864 l_program_name := p_program_name;
865 END IF;
866 --submit concurrent request using fnd_request
867 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
868 l_request_id := fnd_request.submit_request(
869 application => 'OKL',
870 program => p_program_name,
871 description => p_description,
872 argument1 => p_khr_id);
873
874 --if request is not submitted successfully,update the
875 --request status as ERROR in controller transaction table and
876 --set return status to E.
877 IF l_request_id = 0 THEN
878 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** Error:Could not submit request for '||p_description);
879
880 update_book_controller_trx(
881 p_api_version => l_api_version,
882 p_init_msg_list => l_init_msg_list,
883 x_return_status => l_return_status,
884 x_msg_count => l_msg_count,
885 x_msg_data => l_msg_data,
886 p_khr_id => p_khr_id,
887 p_prog_short_name => l_program_name,
888 p_conc_req_id => NULL,
889 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
890
891 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
892 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
893 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
894 RAISE OKL_API.G_EXCEPTION_ERROR;
895 END IF;
896
897 update_book_ctrl_trx(
898 p_batch_num => p_batch_number,
899 p_srl_num => p_serial_num,
900 p_prog_status => 'ERROR',
901 p_request_id => null,
902 x_return_status => l_return_status);
903
904 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
905 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
906 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
907 RAISE OKL_API.G_EXCEPTION_ERROR;
908 END IF;
909
910 x_req_status := 'E';
911 --if request is submitted successfully
912 ELSE
913 FND_FILE.PUT_LINE(FND_FILE.LOG,'**** Request ID: '||l_request_id);
914 FND_FILE.PUT_LINE(FND_FILE.LOG,'**** Monitoring request: '||l_request_id);
915
916 --update the request id and status as RUNNING in controller trx table
917
918 update_book_controller_trx(
919 p_api_version => l_api_version,
920 p_init_msg_list => l_init_msg_list,
921 x_return_status => l_return_status,
922 x_msg_count => l_msg_count,
923 x_msg_data => l_msg_data,
924 p_khr_id => p_khr_id,
925 p_prog_short_name => l_program_name,
926 p_conc_req_id => l_request_id,
927 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_RUNNING);
928
929 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
930 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
931 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
932 RAISE OKL_API.G_EXCEPTION_ERROR;
933 END IF;
934
935 update_book_ctrl_trx(
936 p_batch_num => p_batch_number,
937 p_srl_num => p_serial_num,
938 p_prog_status => 'RUNNING',
939 p_request_id => l_request_id,
940 x_return_status => l_return_status);
941
942 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
943 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
944 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
945 RAISE OKL_API.G_EXCEPTION_ERROR;
946 END IF;
947
948 --wait logic to wait for concurrent request to complete.
949 l_req_status := fnd_concurrent.wait_for_request(
950 l_request_id,
951 5,
952 0,
953 l_phase,
954 l_status,
955 l_dev_phase,
956 l_dev_status,
957 l_message);
958
959 --if the request errors out, update the controller trx table with request
960 --status as ERROR and update return status to E for submit request
961 IF l_dev_status = 'ERROR' THEN
962 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** Request '||l_request_id||' failed.');
963
964 update_book_controller_trx(
965 p_api_version => l_api_version,
966 p_init_msg_list => l_init_msg_list,
967 x_return_status => l_return_status,
968 x_msg_count => l_msg_count,
969 x_msg_data => l_msg_data,
970 p_khr_id => p_khr_id,
971 p_prog_short_name => l_program_name,
972 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
973
974 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
975 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
976 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
977 RAISE OKL_API.G_EXCEPTION_ERROR;
978 END IF;
979
980 update_book_ctrl_trx(
981 p_batch_num => p_batch_number,
982 p_srl_num => p_serial_num,
983 p_prog_status => 'ERROR',
984 p_request_id => null,
985 x_return_status => l_return_status);
986
987 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
988 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
989 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
990 RAISE OKL_API.G_EXCEPTION_ERROR;
991 END IF;
992
993 x_req_status := 'E';
994
995 --if request completes successfully, update the request status as
996 --COMPLETE in controller trx table.
997 ELSE
998 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** Request '||l_request_id||' Completed.');
999
1000 update_book_controller_trx(
1001 p_api_version => l_api_version,
1002 p_init_msg_list => l_init_msg_list,
1003 x_return_status => l_return_status,
1004 x_msg_count => l_msg_count,
1005 x_msg_data => l_msg_data,
1006 p_khr_id => p_khr_id,
1007 p_prog_short_name => l_program_name,
1008 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
1009
1010 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1012 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1013 RAISE OKL_API.G_EXCEPTION_ERROR;
1014 END IF;
1015
1016 update_book_ctrl_trx(
1017 p_batch_num => p_batch_number,
1018 p_srl_num => p_serial_num,
1019 p_prog_status => 'COMPLETE',
1020 p_request_id => null,
1021 x_return_status => l_return_status);
1022
1023 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1024 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1025 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1026 RAISE OKL_API.G_EXCEPTION_ERROR;
1027 END IF;
1028
1029 END IF;
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1034 -- print the error message in the output file
1035 IF (fnd_msg_pub.count_msg > 0) THEN
1036 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1037 LOOP
1038 fnd_msg_pub.get(
1039 p_msg_index => l_counter,
1040 p_encoded => 'F',
1041 p_data => l_data,
1042 p_msg_index_out => l_msg_index_out);
1043 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1044 END LOOP;
1045 END IF;
1046 x_req_status := 'E';
1047
1048 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1049 -- print the error message in the output file
1050 IF (fnd_msg_pub.count_msg > 0) THEN
1051 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1052 LOOP
1053 fnd_msg_pub.get(
1054 p_msg_index => l_counter,
1055 p_encoded => 'F',
1056 p_data => l_data,
1057 p_msg_index_out => l_msg_index_out);
1058 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1059 END LOOP;
1060 END IF;
1061 x_req_status := 'E';
1062
1063 WHEN OTHERS THEN
1064 -- print the error message in the output file
1065 IF (fnd_msg_pub.count_msg > 0) THEN
1066 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1067 LOOP
1068 fnd_msg_pub.get(
1069 p_msg_index => l_counter,
1070 p_encoded => 'F',
1071 p_data => l_data,
1072 p_msg_index_out => l_msg_index_out);
1073 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1074 END LOOP;
1075 END IF;
1076 x_req_status := 'E';
1077
1078 END submit_request;
1079
1080 -----------------------------------------------------------------------------
1081 -- PROCEDURE exec_controller_prg1
1082 -----------------------------------------------------------------------------
1083 -- Start of comments
1084 --
1085 -- procedure Name : exec_controller_prg1
1086 -- Description : Procedure called from concurrent request Controller
1087 -- Program 1 to execute contract booking.
1088 -- Business Rules :
1089 -- Parameters : p_khr_id,p_cont_stage,p_draft_journal_entry
1090 -- Version : 1.0
1091 -- History : XX-XXX-XXXX vthiruva Created
1092 -- End of comments
1093
1094 PROCEDURE exec_controller_prg1(
1095 p_errbuf OUT NOCOPY VARCHAR2,
1096 p_retcode OUT NOCOPY NUMBER,
1097 p_khr_id IN okc_k_headers_b.id%TYPE,
1098 p_cont_stage IN VARCHAR2,
1099 p_draft_journal_entry IN VARCHAR2 DEFAULT 'NO',
1100 p_called_from IN VARCHAR2 DEFAULT 'FORM') IS
1101
1102 l_api_name CONSTANT VARCHAR2(30) := 'exec_controller_prg1';
1103 l_api_version CONSTANT NUMBER := 1.0;
1104 --p_api_version CONSTANT NUMBER := 1.0;
1105 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1106 l_init_msg_list VARCHAR2(1) := 'T';
1107 l_msg_count NUMBER;
1108 l_msg_data VARCHAR2(2000);
1109 l_batch_number okl_book_controller_trx.batch_number%TYPE;
1110 l_curr_sts_code okc_k_headers_b.sts_code%TYPE;
1111 l_approval_path VARCHAR2(30) := 'NONE';
1112 l_req_status VARCHAR2(1);
1113 USER_EXCEPTION EXCEPTION;
1114 l_data VARCHAR2(2000);
1115 l_msg_index_out NUMBER;
1116
1117 --cursor to fetch the processes/steps to be executed
1118 --for the passed batch number.
1119 CURSOR book_ctrl_trx_csr(
1120 p_khr_id NUMBER) IS
1121 SELECT * FROM okl_book_controller_trx
1122 WHERE khr_id = p_khr_id
1123 AND progress_status = 'PENDING'
1124 AND nvl(active_flag,'N') = 'N'
1125 ORDER BY processing_srl_number;
1126
1127 --cursor the fetch the current status of the contract
1128 CURSOR get_curr_sts_code(p_khr_id okc_k_headers_b.id%TYPE) IS
1129 SELECT sts_code
1130 FROM okc_k_headers_b
1131 WHERE id = p_khr_id;
1132
1133 BEGIN
1134 p_retcode := 0;
1135 /*l_return_status := OKL_API.START_ACTIVITY(l_api_name
1136 ,G_PKG_NAME
1137 ,l_init_msg_list
1138 ,l_api_version
1139 ,p_api_version
1140 ,'_PVT'
1141 ,l_return_status);
1142
1143 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1144 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
1145 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1147 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
1148 RAISE OKL_API.G_EXCEPTION_ERROR;
1149 END IF;*/
1150
1151 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Started');
1152 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1153
1154 IF (p_called_from = 'FORM') THEN
1155
1156 --fetch the current status of the contract
1157 OPEN get_curr_sts_code(p_khr_id);
1158 FETCH get_curr_sts_code INTO l_curr_sts_code;
1159 IF get_curr_sts_code%NOTFOUND THEN
1160 l_curr_sts_code := 'XXX'; --dummy value which does not match any status
1161 END IF;
1162 CLOSE get_curr_sts_code;
1163
1164 --populate controller transaction table with the steps to be executed
1165 --to get the contract to the requested stage.
1166 populate_book_ctrl_trx(
1167 p_api_version => l_api_version,
1168 p_init_msg_list => l_init_msg_list,
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data,
1172 p_khr_id => p_khr_id,
1173 p_cont_stage => p_cont_stage,
1174 p_draft_journal_entry => p_draft_journal_entry,
1175 p_curr_sts_code => l_curr_sts_code,
1176 x_batch_number => l_batch_number);
1177
1178 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1179 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to populate_book_ctrl_trx');
1180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1181 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1182 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to populate_book_ctrl_trx');
1183 RAISE OKL_API.G_EXCEPTION_ERROR;
1184 END IF;
1185
1186 --check for batch number to be not null.
1187 --Raise exception if batch number is not generated
1188 IF(l_batch_number IS NULL OR l_batch_number = OKL_API.G_MISS_NUM) THEN
1189 FND_FILE.PUT_LINE(FND_FILE.LOG,'batch number could not be generated');
1190 RAISE OKL_API.G_EXCEPTION_ERROR;
1191 END IF;
1192 COMMIT;
1193
1194 END IF; --p_called_from = 'FORM'
1195
1196 --fetch the concurrent requests to be submitted for the current batch
1197 --and submit them with wait logic.
1198 FOR each_ctrl_trx IN book_ctrl_trx_csr(p_khr_id)
1199 LOOP
1200 l_req_status := 'S';
1201 l_batch_number := each_ctrl_trx.batch_number;
1202
1203 --concurrent request for QA validation..start
1204 IF (each_ctrl_trx.prog_short_name = 'OKLBCTQA') THEN
1205
1206 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Validation Program Submitted');
1207 --submit request for QA Validation
1208 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1209 submit_request(
1210 p_program_name => each_ctrl_trx.prog_short_name,
1211 p_description => 'QA Validation Request',
1212 p_khr_id => each_ctrl_trx.khr_id,
1213 p_batch_number => l_batch_number,
1214 p_serial_num => each_ctrl_trx.processing_srl_number,
1215 x_req_status => l_req_status);
1216
1217 IF (l_req_status = 'E') THEN
1218 RAISE USER_EXCEPTION;
1219 END IF;
1220 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Validation Program Completed with Status 0');
1221 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1222 END IF;
1223 --concurrent request for QA validation..end
1224
1225 --concurrent request for Calculate Upfront Tax..start
1226 IF (each_ctrl_trx.prog_short_name = 'OKLBCTUT') THEN
1227
1228 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Calculate Upfront Tax Program Submitted');
1229 --submit request for Upfront tax
1230 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1231 submit_request(
1232 p_program_name => each_ctrl_trx.prog_short_name,
1233 p_description => 'Calculate Upfront Tax Request',
1234 p_khr_id => each_ctrl_trx.khr_id,
1235 p_batch_number => l_batch_number,
1236 p_serial_num => each_ctrl_trx.processing_srl_number,
1237 x_req_status => l_req_status);
1238
1239 IF (l_req_status = 'E') THEN
1240 RAISE USER_EXCEPTION;
1241 END IF;
1242 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Calculate Upfront Tax Program Completed with Status 0');
1243 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1244 END IF;
1245 --concurrent request for Upfront Tax..end
1246
1247 --concurrent request for stream generation..start
1248 IF (each_ctrl_trx.prog_short_name = 'OKLBCTST') THEN
1249
1250 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Stream Generation Program Submitted');
1251 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1252 --submit request for Stream Generation
1253 submit_request(
1254 p_program_name => each_ctrl_trx.prog_short_name,
1255 p_description => 'Stream Generation Request',
1256 p_khr_id => each_ctrl_trx.khr_id,
1257 p_batch_number => l_batch_number,
1258 p_serial_num => each_ctrl_trx.processing_srl_number,
1259 x_req_status => l_req_status);
1260
1261 IF (l_req_status = 'E') THEN
1262 RAISE USER_EXCEPTION;
1263 END IF;
1264 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Stream Generation Program Completed with Status 0');
1265 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1266 END IF;
1267 --concurrent request for stream generation..end
1268
1269 /*--concurrent request for draft journal entries..start
1270 IF (each_ctrl_trx.prog_short_name = 'OKLBCTJE') THEN
1271
1272 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Journal Entries Program Submitted');
1273 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1274 --submit request for Draft journal Entry
1275 submit_request(
1276 p_program_name => each_ctrl_trx.prog_short_name,
1277 p_description => 'Journal Entries Request',
1278 p_khr_id => each_ctrl_trx.khr_id,
1279 p_batch_number => l_batch_number,
1280 p_serial_num => each_ctrl_trx.processing_srl_number,
1281 x_req_status => l_req_status);
1282
1283 IF (l_req_status = 'E') THEN
1284 RAISE USER_EXCEPTION;
1285 END IF;
1286 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Journal Entries Completed with Status 0');
1287 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1288 END IF;
1289 --concurrent request for draft journal entries..end */
1290
1291 --concurrent request for Approval..start
1292 IF (each_ctrl_trx.prog_short_name = 'OKLBCTAP') THEN
1293
1294 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Approval Program Submitted');
1295 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1296 --submit request for Contract Approval
1297 submit_request(
1298 p_program_name => each_ctrl_trx.prog_short_name,
1299 p_description => 'Approval Request',
1300 p_khr_id => each_ctrl_trx.khr_id,
1301 p_batch_number => l_batch_number,
1302 p_serial_num => each_ctrl_trx.processing_srl_number,
1303 x_req_status => l_req_status);
1304
1305 IF (l_req_status = 'E') THEN
1306 RAISE USER_EXCEPTION;
1307 END IF;
1308 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Approval Program Completed with Status 0');
1309 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1310 END IF;
1311 --concurrent request for approval..end
1312
1313 --concurrent request for Activate..start
1314 IF (each_ctrl_trx.prog_short_name = 'OKLBCTBK') THEN
1315
1316 --read profile for contract approval path
1317 l_approval_path := fnd_profile.value('OKL_LEASE_CONTRACT_APPROVAL_PROCESS');
1318
1319 --if no approval path is set or contract activation is only
1320 --process requested then submit request for Activation
1321 IF (l_curr_sts_code = 'APPROVED' OR
1322 NVL(l_approval_path,'NONE') = 'NONE') THEN
1323
1324 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Activation Program Submitted');
1325 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1326 --submit request for Contract Activation
1327 submit_request(
1328 p_program_name => each_ctrl_trx.prog_short_name,
1329 p_description => 'Contract Activation Request',
1330 p_khr_id => each_ctrl_trx.khr_id,
1331 p_batch_number => l_batch_number,
1332 p_serial_num => each_ctrl_trx.processing_srl_number,
1333 x_req_status => l_req_status);
1334
1335 IF (l_req_status = 'E') THEN
1336 RAISE USER_EXCEPTION;
1337 END IF;
1338 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Activation Program Completed with Status 0');
1339 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1340 END IF; --end condition for approval path
1341
1342 END IF;
1343 --concurrent request for Activate..end
1344
1345 END LOOP;--end submitting all requests
1346
1347 --OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
1348 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Completed with Status 0');
1349
1350 EXCEPTION
1351 WHEN USER_EXCEPTION THEN
1352 p_retcode := 0;
1353 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Completed With Errors');
1354
1355 --close all open cursors
1356 IF book_ctrl_trx_csr%ISOPEN THEN
1357 CLOSE book_ctrl_trx_csr;
1358 END IF;
1359 IF get_curr_sts_code%ISOPEN THEN
1360 CLOSE get_curr_sts_code;
1361 END IF;
1362
1363 --update all PENDING steps of the batch to ABANDONED
1364 FOR each_ctrl_trx IN book_ctrl_trx_csr(p_khr_id)
1365 LOOP
1366 update_book_ctrl_trx(
1367 p_batch_num => l_batch_number,
1368 p_srl_num => each_ctrl_trx.processing_srl_number,
1369 p_prog_status => 'ABANDONED',
1370 p_request_id => null,
1371 x_return_status => l_return_status);
1372 END LOOP;
1373
1374 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1375 p_retcode := 2;
1376
1377 --close all open cursors
1378 IF book_ctrl_trx_csr%ISOPEN THEN
1379 CLOSE book_ctrl_trx_csr;
1380 END IF;
1381 IF get_curr_sts_code%ISOPEN THEN
1382 CLOSE get_curr_sts_code;
1383 END IF;
1384
1385 /*l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1386 p_api_name => l_api_name,
1387 p_pkg_name => g_pkg_name,
1388 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1389 x_msg_count => l_msg_count,
1390 x_msg_data => l_msg_data,
1391 p_api_type => g_api_type);*/
1392
1393 -- print the error message in the output file
1394 IF (fnd_msg_pub.count_msg > 0) THEN
1395 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1396 LOOP
1397 fnd_msg_pub.get(
1398 p_msg_index => l_counter,
1399 p_encoded => 'F',
1400 p_data => l_data,
1401 p_msg_index_out => l_msg_index_out);
1402 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1403 END LOOP;
1404 END IF;
1405
1406 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1407 p_retcode := 2;
1408
1409 --close all open cursors
1410 IF book_ctrl_trx_csr%ISOPEN THEN
1411 CLOSE book_ctrl_trx_csr;
1412 END IF;
1413 IF get_curr_sts_code%ISOPEN THEN
1414 CLOSE get_curr_sts_code;
1415 END IF;
1416
1417 /*l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1418 p_api_name => l_api_name,
1419 p_pkg_name => g_pkg_name,
1420 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1421 x_msg_count => l_msg_count,
1422 x_msg_data => l_msg_data,
1423 p_api_type => g_api_type);*/
1424
1425 -- print the error message in the output file
1426 IF (fnd_msg_pub.count_msg > 0) THEN
1427 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1428 LOOP
1429 fnd_msg_pub.get(
1430 p_msg_index => l_counter,
1431 p_encoded => 'F',
1432 p_data => l_data,
1433 p_msg_index_out => l_msg_index_out);
1434 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1435 END LOOP;
1436 END IF;
1437
1438 WHEN OTHERS THEN
1439 p_retcode := 2;
1440 p_errbuf := SQLERRM;
1441
1442 --close all open cursors
1443 IF book_ctrl_trx_csr%ISOPEN THEN
1444 CLOSE book_ctrl_trx_csr;
1445 END IF;
1446 IF get_curr_sts_code%ISOPEN THEN
1447 CLOSE get_curr_sts_code;
1448 END IF;
1449
1450 /*l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1451 p_api_name => l_api_name,
1452 p_pkg_name => g_pkg_name,
1453 p_exc_name => 'OTHERS',
1454 x_msg_count => l_msg_count,
1455 x_msg_data => l_msg_data,
1456 p_api_type => g_api_type);*/
1457
1458 -- print the error message in the output file
1459 IF (fnd_msg_pub.count_msg > 0) THEN
1460 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1461 LOOP
1462 fnd_msg_pub.get(
1463 p_msg_index => l_counter,
1464 p_encoded => 'F',
1465 p_data => l_data,
1466 p_msg_index_out => l_msg_index_out);
1467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1468 END LOOP;
1469 END IF;
1470 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
1471
1472 END exec_controller_prg1;
1473
1474
1475 -----------------------------------------------------------------------------
1476 -- PROCEDURE exec_controller_prg2
1477 -----------------------------------------------------------------------------
1478 -- Start of comments
1479 --
1480 -- procedure Name : exec_controller_prg2
1481 -- Description : Procedure called from concurrent request Controller
1482 -- Program 2 to execute contract booking activation
1483 -- Business Rules :
1484 -- Parameters : p_khr_id
1485 -- Version : 1.0
1486 -- History : XX-XXX-XXXX vthiruva Created
1487 -- End of comments
1488
1489 PROCEDURE exec_controller_prg2(
1490 p_errbuf OUT NOCOPY VARCHAR2,
1491 p_retcode OUT NOCOPY NUMBER,
1492 p_khr_id IN okc_k_headers_b.id%TYPE) IS
1493
1494 l_api_name CONSTANT VARCHAR2(30) := 'exec_controller_prg2';
1495 /*l_api_version CONSTANT NUMBER := 1.0;
1496 p_api_version CONSTANT NUMBER := 1.0;
1497 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1498 l_init_msg_list VARCHAR2(1) := 'T';
1499 l_msg_count NUMBER;
1500 l_msg_data VARCHAR2(2000);*/
1501 l_req_status VARCHAR2(1);
1502 USER_EXCEPTION EXCEPTION;
1503 l_data VARCHAR2(2000);
1504 l_msg_index_out NUMBER;
1505
1506 --cursor to fetch the processes/steps to be executed
1507 --for the passed batch number.
1508 CURSOR book_ctrl_trx_csr(
1509 p_khr_id okl_book_controller_trx.khr_id%TYPE) IS
1510 SELECT * FROM okl_book_controller_trx
1511 WHERE khr_id = p_khr_id
1512 AND progress_status = 'PENDING'
1513 AND nvl(active_flag,'N') = 'N'
1514 ORDER BY processing_srl_number;
1515
1516
1517 BEGIN
1518 p_retcode := 0;
1519 /*l_return_status := OKL_API.START_ACTIVITY(l_api_name
1520 ,G_PKG_NAME
1521 ,l_init_msg_list
1522 ,l_api_version
1523 ,p_api_version
1524 ,'_PVT'
1525 ,l_return_status);
1526
1527 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1528 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
1529 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1530 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1531 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
1532 RAISE OKL_API.G_EXCEPTION_ERROR;
1533 END IF;*/
1534
1535 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Started');
1536 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1537
1538 --call the concurrent program for activation
1539 FOR each_ctrl_trx IN book_ctrl_trx_csr(p_khr_id)
1540 LOOP
1541 l_req_status := 'S';
1542 --concurrent request for Activation..start
1543 IF (each_ctrl_trx.prog_short_name = 'OKLBCTBK') THEN
1544
1545 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Activation Program Submitted');
1546 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1547 --submit request for Contract Activation
1548 submit_request(
1549 p_program_name => each_ctrl_trx.prog_short_name,
1550 p_description => 'Contract Activation Request',
1551 p_khr_id => each_ctrl_trx.khr_id,
1552 p_batch_number => each_ctrl_trx.batch_number,
1553 p_serial_num => each_ctrl_trx.processing_srl_number,
1554 x_req_status => l_req_status);
1555
1556 IF (l_req_status = 'E') THEN
1557 RAISE USER_EXCEPTION;
1558 END IF;
1559 FND_FILE.PUT_LINE(FND_FILE.LOG, '**** OKL: Contract Activation Program Completed with Status 0');
1560 END IF;
1561 --concurrent request for Activation..end
1562 END LOOP;
1563
1564 --OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
1565 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Completed with Status 0');
1566
1567 EXCEPTION
1568 WHEN USER_EXCEPTION THEN
1569 p_retcode := 0;
1570 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract Activation Failed');
1571 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKL: Contract Booking Controller Completed with Errors');
1572
1573 /*WHEN OKL_API.G_EXCEPTION_ERROR THEN
1574 p_retcode := 2;
1575
1576 IF book_ctrl_trx_csr%ISOPEN THEN
1577 CLOSE book_ctrl_trx_csr;
1578 END IF;
1579
1580 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1581 p_api_name => l_api_name,
1582 p_pkg_name => g_pkg_name,
1583 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1584 x_msg_count => l_msg_count,
1585 x_msg_data => l_msg_data,
1586 p_api_type => g_api_type);
1587
1588 -- print the error message in the log file
1589 Okl_Accounting_Util.get_error_message(g_error_msg);
1590 IF (g_error_msg.COUNT > 0) THEN
1591 FOR i IN g_error_msg.FIRST..g_error_msg.LAST
1592 LOOP
1593 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, g_error_msg(i));
1594 END LOOP;
1595 END IF;
1596
1597 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1598 p_retcode := 2;
1599
1600 IF book_ctrl_trx_csr%ISOPEN THEN
1601 CLOSE book_ctrl_trx_csr;
1602 END IF;
1603
1604 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1605 p_api_name => l_api_name,
1606 p_pkg_name => g_pkg_name,
1607 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1608 x_msg_count => l_msg_count,
1609 x_msg_data => l_msg_data,
1610 p_api_type => g_api_type);
1611
1612 -- print the error message in the log file
1613 Okl_Accounting_Util.get_error_message(g_error_msg);
1614 IF (g_error_msg.COUNT > 0) THEN
1615 FOR i IN g_error_msg.FIRST..g_error_msg.LAST
1616 LOOP
1617 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, g_error_msg(i));
1618 END LOOP;
1619 END IF; */
1620
1621 WHEN OTHERS THEN
1622 p_retcode := 2;
1623 p_errbuf := SQLERRM;
1624
1625 --close open cursors
1626 IF book_ctrl_trx_csr%ISOPEN THEN
1627 CLOSE book_ctrl_trx_csr;
1628 END IF;
1629
1630 -- print the error message in the output file
1631 IF (fnd_msg_pub.count_msg > 0) THEN
1632 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1633 LOOP
1634 fnd_msg_pub.get(
1635 p_msg_index => l_counter,
1636 p_encoded => 'F',
1637 p_data => l_data,
1638 p_msg_index_out => l_msg_index_out);
1639 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1640 END LOOP;
1641 END IF;
1642 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
1643
1644 END exec_controller_prg2;
1645
1646 -----------------------------------------------------------------------------
1647 -- PROCEDURE execute_qa_check_list
1648 -----------------------------------------------------------------------------
1649 -- Start of comments
1650 --
1651 -- procedure Name : execute_qa_check_list
1652 -- Description : Procedure called from QA Validation concurrent request
1653 -- to execute QA Checklist
1654 -- Business Rules :
1655 -- Parameters : p_khr_id
1656 -- Version : 1.0
1657 -- History : XX-XXX-XXXX vthiruva Created
1658 -- End of comments
1659
1660 PROCEDURE execute_qa_check_list(
1661 p_errbuf OUT NOCOPY VARCHAR2,
1662 p_retcode OUT NOCOPY NUMBER,
1663 p_khr_id IN okc_k_headers_b.id%TYPE) IS
1664
1665 l_api_name CONSTANT VARCHAR2(30) := 'execute_qa_check_list';
1666 l_api_version CONSTANT NUMBER := 1.0;
1667 p_api_version CONSTANT NUMBER := 1.0;
1668 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1669 l_init_msg_list VARCHAR2(1) := 'T';
1670 l_msg_count NUMBER;
1671 l_msg_data VARCHAR2(2000);
1672 l_qcl_id NUMBER;
1673 l_severity fnd_lookups.meaning%TYPE;
1674 l_error_sts VARCHAR2(1);
1675 l_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
1676 l_data_len NUMBER;
1677 l_msg_token_tbl msg_token_tbl;
1678 l_data VARCHAR2(2000);
1679 l_msg_index_out NUMBER;
1680 l_qa_sts VARCHAR2(1);
1681
1682 --cursor to check if the contract has an assigned QA checker.
1683 --else default it to 1
1684 CURSOR get_qcl_id(p_khr_id okc_k_headers_b.id%TYPE) IS
1685 SELECT NVL(qcl_id,1)
1686 FROM okc_k_headers_b
1687 WHERE id = p_khr_id;
1688
1689 --cursor to fetch severity meaning
1690 CURSOR get_severity(p_sts_code fnd_lookups.lookup_code%TYPE) IS
1691 SELECT meaning
1692 FROM fnd_lookups
1693 WHERE lookup_type = 'CP_SET_OUTCOME'
1694 AND lookup_code = p_sts_code;
1695
1696 BEGIN
1697
1698 p_retcode := 0;
1699 --print OKL copyright in the output file
1700 l_msg_token_tbl(1).token_name := 'API_NAME';
1701 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
1702 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
1703 l_msg_token_tbl(2).token_value := 'QA Validation';
1704 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1705 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
1706 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QACHECK_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
1708 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
1709 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1710
1711 l_return_status := OKL_API.START_ACTIVITY(l_api_name
1712 ,G_PKG_NAME
1713 ,l_init_msg_list
1714 ,l_api_version
1715 ,p_api_version
1716 ,'_PVT'
1717 ,l_return_status);
1718
1719 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1720 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
1721 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1722 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1723 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
1724 RAISE OKL_API.G_EXCEPTION_ERROR;
1725 END IF;
1726 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
1727 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1728
1729 --fetch qcl_id for the contract
1730 OPEN get_qcl_id(p_khr_id);
1731 FETCH get_qcl_id INTO l_qcl_id;
1732 IF get_qcl_id%NOTFOUND THEN
1733 --error : contract does not exist
1734 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : Contract Number is not valid.');
1735 l_return_status := OKL_API.G_RET_STS_ERROR;
1736 RAISE OKL_API.G_EXCEPTION_ERROR;
1737 END IF;
1738 CLOSE get_qcl_id;
1739
1740 --call QA validate program
1741 okl_contract_book_pub.execute_qa_check_list(
1742 p_api_version => l_api_version,
1743 p_init_msg_list => l_init_msg_list,
1744 x_return_status => l_return_status,
1745 x_msg_count => l_msg_count,
1746 x_msg_data => l_msg_data,
1747 p_qcl_id => l_qcl_id,
1748 p_chr_id => p_khr_id,
1749 x_msg_tbl => l_msg_tbl);
1750
1751 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1752 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pub.execute_qa_check_list');
1753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1754 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1755 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in call to okl_contract_book_pub.execute_qa_check_list');
1756 RAISE OKL_API.G_EXCEPTION_ERROR;
1757 END IF;
1758
1759 --print QA validation checklist into the output file
1760 IF (l_msg_tbl.COUNT > 0) THEN
1761 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QA_CHECKLIST'));
1762 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------------');
1763 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1764 --column headings for the QA Validation checklist
1765 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1766 RPAD(OKL_ACCOUNTING_UTIL.GET_MESSAGE_TOKEN('OKL_LA_CONTRACT_QA', 'OKL_PROCESS'),40,' ')||
1767 RPAD(OKL_ACCOUNTING_UTIL.GET_MESSAGE_TOKEN('OKL_LA_CONTRACT_QA', 'OKL_DESCRIPTION'),40,' ')||
1768 RPAD(OKL_ACCOUNTING_UTIL.GET_MESSAGE_TOKEN('OKL_LA_CONTRACT_QA', 'OKL_SEVERITY'),9,' ')||
1769 RPAD(OKL_ACCOUNTING_UTIL.GET_MESSAGE_TOKEN('OKL_LA_CONTRACT_QA', 'OKL_EXPLANATION'),51,' '));
1770
1771 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',140,'='));
1772
1773 --loop to print QA validation checklist
1774 FOR i IN l_msg_tbl.FIRST..l_msg_tbl.LAST
1775 LOOP
1776 l_severity := null;
1777 --handle unexpected system error
1778 IF (l_msg_tbl(i).error_status = 'U') THEN
1779 l_error_sts := 'E';
1780 ELSE
1781 l_error_sts := l_msg_tbl(i).error_status;
1782 END IF;
1783
1784 IF (l_qa_sts IS NULL AND l_error_sts = 'E') THEN
1785 l_qa_sts := 'E';
1786 END IF;
1787
1788 --fetch severity meaning to be displayed in checklist
1789 OPEN get_severity(l_error_sts);
1790 FETCH get_severity INTO l_severity;
1791 IF get_severity%NOTFOUND THEN
1792 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : Invalid error status code '||l_error_sts);
1793 l_return_status := OKL_API.G_RET_STS_ERROR;
1794 RAISE OKL_API.G_EXCEPTION_ERROR;
1795 END IF;
1796 CLOSE get_severity;
1797
1798 --printing checklist
1799 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1800 RPAD(l_msg_tbl(i).name,40,' ')||
1801 RPAD(l_msg_tbl(i).description,40,' ')||
1802 RPAD(l_severity,9,' ')||
1803 RPAD(l_msg_tbl(i).data,51,' '));
1804
1805 l_data_len := 52;
1806 LOOP
1807 EXIT WHEN (SUBSTR(l_msg_tbl(i).data,l_data_len) IS NULL);
1808 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1809 RPAD(' ',89,' ')||
1810 RPAD(SUBSTR(l_msg_tbl(i).data,l_data_len),51,' '));
1811
1812 l_data_len := l_data_len + 52;
1813 --condition to restrict explanation to 2000 characters
1814 --incase loop runs infinitely
1815 EXIT WHEN l_data_len > 2000;
1816 END LOOP;
1817
1818 END LOOP;
1819 END IF; --finished printing QA checklist to output file.
1820
1821 IF (l_qa_sts = 'E') THEN
1822 p_retcode := 2;
1823 END IF;
1824
1825 FND_FILE.PUT_LINE(FND_FILE.LOG, 'QA validation completed successfully....');
1826 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1827
1828 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
1829 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
1830
1831 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1832 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
1833 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
1834 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
1835 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1836 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QACHECK_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
1837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1838
1839 EXCEPTION
1840 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1841 p_retcode := 2;
1842
1843 --close open cursors
1844 IF get_qcl_id%ISOPEN THEN
1845 CLOSE get_qcl_id;
1846 END IF;
1847 IF get_severity%ISOPEN THEN
1848 CLOSE get_severity;
1849 END IF;
1850
1851 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1852 p_api_name => l_api_name,
1853 p_pkg_name => g_pkg_name,
1854 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1855 x_msg_count => l_msg_count,
1856 x_msg_data => l_msg_data,
1857 p_api_type => g_api_type);
1858
1859 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1860 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
1861 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
1862
1863 --print the error message in the output file
1864 IF (fnd_msg_pub.count_msg > 0) THEN
1865 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1866 LOOP
1867 fnd_msg_pub.get(
1868 p_msg_index => l_counter,
1869 p_encoded => 'F',
1870 p_data => l_data,
1871 p_msg_index_out => l_msg_index_out);
1872 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1873 END LOOP;
1874 END IF;
1875
1876 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1877 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QACHECK_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
1878 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1879
1880 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1881 p_retcode := 2;
1882
1883 --close open cursors
1884 IF get_qcl_id%ISOPEN THEN
1885 CLOSE get_qcl_id;
1886 END IF;
1887 IF get_severity%ISOPEN THEN
1888 CLOSE get_severity;
1889 END IF;
1890
1891 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1892 p_api_name => l_api_name,
1893 p_pkg_name => g_pkg_name,
1894 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1895 x_msg_count => l_msg_count,
1896 x_msg_data => l_msg_data,
1897 p_api_type => g_api_type);
1898
1899 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1900 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
1901 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
1902
1903 -- print the error message in the output file
1904 IF (fnd_msg_pub.count_msg > 0) THEN
1905 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1906 LOOP
1907 fnd_msg_pub.get(
1908 p_msg_index => l_counter,
1909 p_encoded => 'F',
1910 p_data => l_data,
1911 p_msg_index_out => l_msg_index_out);
1912 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1913 END LOOP;
1914 END IF;
1915
1916 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1917 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QACHECK_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
1918 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1919
1920 WHEN OTHERS THEN
1921 p_retcode := 2;
1922 p_errbuf := SQLERRM;
1923
1924 --close open cursors
1925 IF get_qcl_id%ISOPEN THEN
1926 CLOSE get_qcl_id;
1927 END IF;
1928 IF get_severity%ISOPEN THEN
1929 CLOSE get_severity;
1930 END IF;
1931
1932 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
1933 p_api_name => l_api_name,
1934 p_pkg_name => g_pkg_name,
1935 p_exc_name => 'OTHERS',
1936 x_msg_count => l_msg_count,
1937 x_msg_data => l_msg_data,
1938 p_api_type => g_api_type);
1939
1940 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1941 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
1942 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
1943
1944 -- print the error message in the output file
1945 IF (fnd_msg_pub.count_msg > 0) THEN
1946 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1947 LOOP
1948 fnd_msg_pub.get(
1949 p_msg_index => l_counter,
1950 p_encoded => 'F',
1951 p_data => l_data,
1952 p_msg_index_out => l_msg_index_out);
1953 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
1954 END LOOP;
1955 END IF;
1956
1957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1958 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_QACHECK_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
1959 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
1960 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
1961
1962 END execute_qa_check_list;
1963
1964
1965 -----------------------------------------------------------------------------
1966 -- PROCEDURE generate_streams
1967 -----------------------------------------------------------------------------
1968 -- Start of comments
1969 --
1970 -- procedure Name : generate_streams
1971 -- Description : Procedure called from Stream Generation concurrent request
1972 -- Business Rules :
1973 -- Parameters : p_khr_id
1974 -- Version : 1.0
1975 -- History : XX-XXX-XXXX vthiruva Created
1976 -- End of comments
1977
1978 PROCEDURE generate_streams(
1979 p_errbuf OUT NOCOPY VARCHAR2,
1980 p_retcode OUT NOCOPY NUMBER,
1981 p_khr_id IN okc_k_headers_b.id%TYPE) IS
1982
1983 l_api_name CONSTANT VARCHAR2(30) := 'generate_streams';
1984 l_api_version CONSTANT NUMBER := 1.0;
1985 p_api_version CONSTANT NUMBER := 1.0;
1986 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1987 l_init_msg_list VARCHAR2(1) := 'T';
1988 l_msg_count NUMBER;
1989 l_msg_data VARCHAR2(2000);
1990 l_pricing_engine VARCHAR2(30);
1991 l_trx_number NUMBER;
1992 x_trx_number NUMBER;
1993 l_trx_status VARCHAR2(30);
1994 l_sis_code VARCHAR2(30);
1995 l_sts_code VARCHAR2(30);
1996 l_msg_token_tbl msg_token_tbl;
1997 l_data VARCHAR2(2000);
1998 l_msg_index_out NUMBER;
1999
2000 --cursor to fetch the ESG transaction status
2001 CURSOR extr_strm_gen_status(p_trx_num NUMBER) IS
2002 SELECT sis_code
2003 FROM okl_stream_interfaces
2004 WHERE transaction_number = p_trx_num;
2005
2006 --cursor to fetch the current contract status
2007 CURSOR get_contract_status(p_khr_id okc_k_headers_b.id%TYPE) IS
2008 SELECT sts_code
2009 FROM okc_k_headers_b
2010 WHERE id = p_khr_id;
2011
2012 --cursor the fetch the ESG trx number and status for the contract
2013 CURSOR get_trx_number(p_khr_id okc_k_headers_b.id%TYPE) IS
2014 SELECT transaction_number, sis_code
2015 FROM okl_stream_interfaces
2016 WHERE khr_id = p_khr_id
2017 AND date_processed =
2018 (SELECT MAX(date_processed)
2019 FROM okl_stream_interfaces
2020 WHERE khr_id = p_khr_id
2021 AND sis_code NOT IN ('PROCESS_COMPLETE','PROCESS_COMPLETE_ERRORS',
2022 'PROCESSING_FAILED','TIME_OUT','PROCESS_ABORTED', 'SERVER_NA')
2023 );
2024
2025 BEGIN
2026 p_retcode := 0;
2027 --print OKL copyright in the output file
2028 l_msg_token_tbl(1).token_name := 'API_NAME';
2029 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
2030 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
2031 l_msg_token_tbl(2).token_value := 'Stream Generation';
2032 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2033 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
2034 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2035 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_STREAM_GENERATION_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2036 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
2037 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2038
2039 l_return_status := OKL_API.START_ACTIVITY(l_api_name
2040 ,G_PKG_NAME
2041 ,l_init_msg_list
2042 ,l_api_version
2043 ,p_api_version
2044 ,'_PVT'
2045 ,l_return_status);
2046
2047 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2048 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
2049 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2050 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2051 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
2052 RAISE OKL_API.G_EXCEPTION_ERROR;
2053 END IF;
2054 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
2055 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2056
2057 --get the pricing engine set for the contract(ISG or ESG)
2058 okl_streams_util.get_pricing_engine(
2059 p_khr_id => p_khr_id,
2060 x_pricing_engine => l_pricing_engine,
2061 x_return_status => l_return_status);
2062
2063 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2064 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in okl_stream_util.get_pricing_engine');
2065 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2067 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in okl_stream_util.get_pricing_engine');
2068 RAISE OKL_API.G_EXCEPTION_ERROR;
2069 END IF;
2070
2071 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_STREAM_GEN_METHOD','ST_GEN_METHOD',l_pricing_engine));
2072
2073 --pricing engine is set to INTERNAL or EXTERNAL
2074 IF (l_pricing_engine = 'INTERNAL' OR l_pricing_engine = 'EXTERNAL') THEN
2075 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Call to Stream Generation');
2076 --call to Internal Stream Generation
2077 okl_contract_book_pvt.generate_streams(
2078 p_api_version => l_api_version,
2079 p_init_msg_list => l_init_msg_list,
2080 p_chr_id => p_khr_id,
2081 p_generation_context => null,
2082 x_return_status => l_return_status,
2083 x_msg_count => l_msg_count,
2084 x_msg_data => l_msg_data,
2085 x_trx_number => l_trx_number,
2086 x_trx_status => l_trx_status);
2087
2088 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2089 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in okl_contract_book_pvt.generate_streams');
2090 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2091 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2092 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in okl_contract_book_pvt.generate_streams');
2093 RAISE OKL_API.G_EXCEPTION_ERROR;
2094 END IF;
2095
2096 ELSE
2097 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stream Generation failed. Pricing engine not set');
2098 RAISE OKL_API.G_EXCEPTION_ERROR;
2099 END IF;
2100
2101 IF (l_pricing_engine = 'EXTERNAL') THEN
2102 COMMIT;
2103 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_STREAM_TRX_ID','STREAM_TRX_ID',l_trx_number));
2104 --wait logic for ESG to complete..
2105 LOOP
2106 --fetch the transaction status for ESG
2107 OPEN extr_strm_gen_status(l_trx_number);
2108 FETCH extr_strm_gen_status INTO l_sis_code;
2109 IF extr_strm_gen_status%NOTFOUND THEN
2110 p_retcode := 2;
2111 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No record found in okl_stream_interfaces for trx_number'||l_trx_number);
2112 EXIT;
2113 END IF;
2114 CLOSE extr_strm_gen_status;
2115
2116 --fetch contract status
2117 OPEN get_contract_status(p_khr_id);
2118 FETCH get_contract_status INTO l_sts_code;
2119 IF get_contract_status%NOTFOUND THEN
2120 p_retcode := 2;
2121 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No record found for contract_id'||p_khr_id);
2122 EXIT;
2123 END IF;
2124 CLOSE get_contract_status;
2125
2126 --exit wait logic when ESG transaction completes successfully
2127 --and contract status is updated or when ESG transaction fails.
2128 EXIT WHEN((l_sis_code = 'PROCESS_COMPLETE' AND l_sts_code = 'COMPLETE') OR
2129 l_sis_code IN ('PROCESS_COMPLETE_ERRORS','PROCESSING_FAILED',
2130 'TIME_OUT','PROCESS_ABORTED', 'SERVER_NA'));
2131
2132 dbms_lock.sleep(10);
2133 END LOOP; --end of ESG wait logic
2134
2135 --return concurrent request status as Error if ESG failed.
2136 IF (l_sis_code IN ('PROCESS_COMPLETE_ERRORS','PROCESSING_FAILED',
2137 'TIME_OUT','PROCESS_ABORTED', 'SERVER_NA')) THEN
2138 p_retcode := 2;
2139 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stream Generation Not successful.'||l_sis_code);
2140 END IF;
2141 END IF;
2142
2143
2144 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Stream Generation completed....');
2145 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2146
2147 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
2148 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
2149
2150 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2151 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2152 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2153 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
2154 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2155 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_STREAM_GENERATION_SUCCESS')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2156 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2157
2158 EXCEPTION
2159 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2160 p_retcode := 2;
2161
2162 --close all open cursors
2163 IF extr_strm_gen_status%ISOPEN THEN
2164 CLOSE extr_strm_gen_status;
2165 END IF;
2166 IF get_contract_status%ISOPEN THEN
2167 CLOSE get_contract_status;
2168 END IF;
2169 IF get_trx_number%ISOPEN THEN
2170 CLOSE get_trx_number;
2171 END IF;
2172
2173 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2174 p_api_name => l_api_name,
2175 p_pkg_name => g_pkg_name,
2176 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2177 x_msg_count => l_msg_count,
2178 x_msg_data => l_msg_data,
2179 p_api_type => g_api_type);
2180
2181 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2182 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2183 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2184
2185 -- print the error message in the output file
2186 IF (fnd_msg_pub.count_msg > 0) THEN
2187 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2188 LOOP
2189 fnd_msg_pub.get(
2190 p_msg_index => l_counter,
2191 p_encoded => 'F',
2192 p_data => l_data,
2193 p_msg_index_out => l_msg_index_out);
2194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2195 END LOOP;
2196 END IF;
2197
2198 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2199 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_STREAM_GENERATION_SUCCESS')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2201
2202 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2203 p_retcode := 2;
2204
2205 --close all open cursors
2206 IF extr_strm_gen_status%ISOPEN THEN
2207 CLOSE extr_strm_gen_status;
2208 END IF;
2209 IF get_contract_status%ISOPEN THEN
2210 CLOSE get_contract_status;
2211 END IF;
2212 IF get_trx_number%ISOPEN THEN
2213 CLOSE get_trx_number;
2214 END IF;
2215
2216 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2217 p_api_name => l_api_name,
2218 p_pkg_name => g_pkg_name,
2219 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2220 x_msg_count => l_msg_count,
2221 x_msg_data => l_msg_data,
2222 p_api_type => g_api_type);
2223
2224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2226 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2227
2228 -- print the error message in the output file
2229 IF (fnd_msg_pub.count_msg > 0) THEN
2230 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2231 LOOP
2232 fnd_msg_pub.get(
2233 p_msg_index => l_counter,
2234 p_encoded => 'F',
2235 p_data => l_data,
2236 p_msg_index_out => l_msg_index_out);
2237 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2238 END LOOP;
2239 END IF;
2240
2241 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_STREAM_GENERATION_SUCCESS')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2244
2245 WHEN OTHERS THEN
2246 p_retcode := 2;
2247 p_errbuf := SQLERRM;
2248
2249 --close all open cursors
2250 IF extr_strm_gen_status%ISOPEN THEN
2251 CLOSE extr_strm_gen_status;
2252 END IF;
2253 IF get_contract_status%ISOPEN THEN
2254 CLOSE get_contract_status;
2255 END IF;
2256 IF get_trx_number%ISOPEN THEN
2257 CLOSE get_trx_number;
2258 END IF;
2259
2260 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2261 p_api_name => l_api_name,
2262 p_pkg_name => g_pkg_name,
2263 p_exc_name => 'OTHERS',
2264 x_msg_count => l_msg_count,
2265 x_msg_data => l_msg_data,
2266 p_api_type => g_api_type);
2267
2268 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2270 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2271
2272 -- print the error message in the output file
2273 IF (fnd_msg_pub.count_msg > 0) THEN
2274 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2275 LOOP
2276 fnd_msg_pub.get(
2277 p_msg_index => l_counter,
2278 p_encoded => 'F',
2279 p_data => l_data,
2280 p_msg_index_out => l_msg_index_out);
2281 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2282 END LOOP;
2283 END IF;
2284
2285 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_STREAM_GENERATION_SUCCESS')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2287 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2288 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
2289
2290 END generate_streams;
2291
2292
2293 -----------------------------------------------------------------------------
2294 -- PROCEDURE generate_journal_entries
2295 -----------------------------------------------------------------------------
2296 -- Start of comments
2297 --
2298 -- procedure Name : generate_journal_entries
2299 -- Description : Procedure called from Draft Journal Entry concurrent request
2300 -- Business Rules :
2301 -- Parameters : p_khr_id
2302 -- Version : 1.0
2303 -- History : XX-XXX-XXXX vthiruva Created
2304 -- End of comments
2305
2306 PROCEDURE generate_journal_entries(
2307 p_errbuf OUT NOCOPY VARCHAR2,
2308 p_retcode OUT NOCOPY NUMBER,
2309 p_khr_id IN okc_k_headers_b.id%TYPE) IS
2310
2311 l_api_name CONSTANT VARCHAR2(30) := 'generate_journal_entries';
2312 l_api_version CONSTANT NUMBER := 1.0;
2313 p_api_version CONSTANT NUMBER := 1.0;
2314 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2315 l_init_msg_list VARCHAR2(1) := 'T';
2316 l_msg_count NUMBER;
2317 l_msg_data VARCHAR2(2000);
2318 l_orig_syst_code okc_k_headers_b.orig_system_source_code%TYPE;
2319 l_orig_syst_id okc_k_headers_b.orig_system_id1%TYPE;
2320 l_msg_token_tbl msg_token_tbl;
2321 l_data VARCHAR2(2000);
2322 l_msg_index_out NUMBER;
2323
2324 --cursor to fetch original system source code and id
2325 CURSOR orig_syst_csr(p_khr_id okc_k_headers_b.id%TYPE) IS
2326 SELECT orig_system_source_code,
2327 orig_system_id1
2328 FROM okc_k_headers_v
2329 WHERE id = p_khr_id;
2330
2331 BEGIN
2332 p_retcode := 0;
2333 --print OKL copyright in the output file
2334 l_msg_token_tbl(1).token_name := 'API_NAME';
2335 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
2336 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
2337 l_msg_token_tbl(2).token_value := 'Draft Journal Entry';
2338 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2339 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
2340 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_JOURNAL_ENTRY_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2342 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
2343 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2344
2345 l_return_status := OKL_API.START_ACTIVITY(l_api_name
2346 ,G_PKG_NAME
2347 ,l_init_msg_list
2348 ,l_api_version
2349 ,p_api_version
2350 ,'_PVT'
2351 ,l_return_status);
2352
2353 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2354 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
2355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2356 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2357 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
2358 RAISE OKL_API.G_EXCEPTION_ERROR;
2359 END IF;
2360 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
2361 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2362
2363 --fetch orig_system_source_code and id
2364 OPEN orig_syst_csr(p_khr_id);
2365 FETCH orig_syst_csr INTO l_orig_syst_code,l_orig_syst_id;
2366 IF orig_syst_csr%NOTFOUND THEN
2367 --error : contract does not exist
2368 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : Contract Number is not valid.');
2369 l_return_status := OKL_API.G_RET_STS_ERROR;
2370 RAISE OKL_API.G_EXCEPTION_ERROR;
2371 END IF;
2372 CLOSE orig_syst_csr;
2373
2374 --if contract is in rebook process
2375 IF (l_orig_syst_code = 'OKL_REBOOK') THEN
2376 --call generate journal entries for rebook
2377 okl_contract_book_pub.generate_journal_entries(
2378 p_api_version => l_api_version,
2379 p_init_msg_list => l_init_msg_list,
2380 p_commit => 'Y',
2381 p_contract_id => l_orig_syst_id,
2382 p_transaction_type => 'Rebook',
2383 p_draft_yn => 'T',
2384 x_return_status => l_return_status,
2385 x_msg_count => l_msg_count,
2386 x_msg_data => l_msg_data);
2387
2388 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2389 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pub.generate_journal_entries');
2390 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2391 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2392 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pub.generate_journal_entries');
2393 RAISE OKL_API.G_EXCEPTION_ERROR;
2394 END IF;
2395
2396 END IF; --end entries for rebook
2397
2398 --call generate journal entries for Booking
2399 okl_contract_book_pub.generate_journal_entries(
2400 p_api_version => l_api_version,
2401 p_init_msg_list => l_init_msg_list,
2402 p_commit => 'Y',
2403 p_contract_id => p_khr_id,
2404 p_transaction_type => 'Booking',
2405 p_draft_yn => 'T',
2406 x_return_status => l_return_status,
2407 x_msg_count => l_msg_count,
2408 x_msg_data => l_msg_data);
2409
2410 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2411 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pub.generate_journal_entries');
2412 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2413 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2414 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pub.generate_journal_entries');
2415 RAISE OKL_API.G_EXCEPTION_ERROR;
2416 END IF;
2417
2418 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Journal Entries generated successfully....');
2419 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2420
2421 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
2422 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
2423
2424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2426 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2427 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
2428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2429 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_JOURNAL_ENTRY_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2431
2432 EXCEPTION
2433 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2434 p_retcode := 2;
2435
2436 --close open cursors
2437 IF orig_syst_csr%ISOPEN THEN
2438 CLOSE orig_syst_csr;
2439 END IF;
2440
2441 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2442 p_api_name => l_api_name,
2443 p_pkg_name => g_pkg_name,
2444 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2445 x_msg_count => l_msg_count,
2446 x_msg_data => l_msg_data,
2447 p_api_type => g_api_type);
2448
2449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2452
2453 -- print the error message in the output file
2454 IF (fnd_msg_pub.count_msg > 0) THEN
2455 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2456 LOOP
2457 fnd_msg_pub.get(
2458 p_msg_index => l_counter,
2459 p_encoded => 'F',
2460 p_data => l_data,
2461 p_msg_index_out => l_msg_index_out);
2462 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2463 END LOOP;
2464 END IF;
2465
2466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_JOURNAL_ENTRY_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2468 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2469
2470 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2471 p_retcode := 2;
2472
2473 --close open cursors
2474 IF orig_syst_csr%ISOPEN THEN
2475 CLOSE orig_syst_csr;
2476 END IF;
2477
2478 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2479 p_api_name => l_api_name,
2480 p_pkg_name => g_pkg_name,
2481 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2482 x_msg_count => l_msg_count,
2483 x_msg_data => l_msg_data,
2484 p_api_type => g_api_type);
2485
2486 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2487 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2488 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2489
2490 -- print the error message in the output file
2491 IF (fnd_msg_pub.count_msg > 0) THEN
2492 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2493 LOOP
2494 fnd_msg_pub.get(
2495 p_msg_index => l_counter,
2496 p_encoded => 'F',
2497 p_data => l_data,
2498 p_msg_index_out => l_msg_index_out);
2499 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2500 END LOOP;
2501 END IF;
2502
2503 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2504 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_JOURNAL_ENTRY_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2505 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2506
2507 WHEN OTHERS THEN
2508 p_retcode := 2;
2509 p_errbuf := SQLERRM;
2510
2511 --close open cursors
2512 IF orig_syst_csr%ISOPEN THEN
2513 CLOSE orig_syst_csr;
2514 END IF;
2515
2516 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2517 p_api_name => l_api_name,
2518 p_pkg_name => g_pkg_name,
2519 p_exc_name => 'OTHERS',
2520 x_msg_count => l_msg_count,
2521 x_msg_data => l_msg_data,
2522 p_api_type => g_api_type);
2523
2524 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2525 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2526 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2527
2528 -- print the error message in the output file
2529 IF (fnd_msg_pub.count_msg > 0) THEN
2530 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2531 LOOP
2532 fnd_msg_pub.get(
2533 p_msg_index => l_counter,
2534 p_encoded => 'F',
2535 p_data => l_data,
2536 p_msg_index_out => l_msg_index_out);
2537 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2538 END LOOP;
2539 END IF;
2540 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2541 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_JOURNAL_ENTRY_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2542 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2543 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
2544
2545 END generate_journal_entries;
2546
2547
2548 -----------------------------------------------------------------------------
2549 -- PROCEDURE submit_for_approval
2550 -----------------------------------------------------------------------------
2551 -- Start of comments
2552 --
2553 -- procedure Name : submit_for_approval
2554 -- Description : Procedure called from Approval concurrent request
2555 -- Business Rules :
2556 -- Parameters : p_khr_id
2557 -- Version : 1.0
2558 -- History : XX-XXX-XXXX vthiruva Created
2559 -- End of comments
2560
2561 PROCEDURE submit_for_approval(
2562 p_errbuf OUT NOCOPY VARCHAR2,
2563 p_retcode OUT NOCOPY NUMBER,
2564 p_khr_id IN okc_k_headers_b.id%TYPE) IS
2565
2566 l_api_name CONSTANT VARCHAR2(30) := 'submit_for_approval';
2567 l_api_version CONSTANT NUMBER := 1.0;
2568 p_api_version CONSTANT NUMBER := 1.0;
2569 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2570 l_init_msg_list VARCHAR2(1) := 'T';
2571 l_msg_count NUMBER;
2572 l_msg_data VARCHAR2(2000);
2573 l_msg_token_tbl msg_token_tbl;
2574 l_data VARCHAR2(2000);
2575 l_msg_index_out NUMBER;
2576
2577 BEGIN
2578 p_retcode := 0;
2579 --print OKL copyright in the output file
2580 l_msg_token_tbl(1).token_name := 'API_NAME';
2581 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
2582 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
2583 l_msg_token_tbl(2).token_value := 'Approval';
2584 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2585 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
2586 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2587 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_APPROVAL_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2588 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
2589 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2590
2591 l_return_status := OKL_API.START_ACTIVITY(l_api_name
2592 ,G_PKG_NAME
2593 ,l_init_msg_list
2594 ,l_api_version
2595 ,p_api_version
2596 ,'_PVT'
2597 ,l_return_status);
2598
2599 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2600 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
2601 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2602 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2603 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
2604 RAISE OKL_API.G_EXCEPTION_ERROR;
2605 END IF;
2606 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
2607 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2608
2609 --call program for contract approval
2610 okl_contract_book_pub.submit_for_approval(
2611 p_api_version => l_api_version,
2612 p_init_msg_list => l_init_msg_list,
2613 x_return_status => l_return_status,
2614 x_msg_count => l_msg_count,
2615 x_msg_data => l_msg_data,
2616 p_chr_id => p_khr_id);
2617
2618 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2619 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pub.submit_for_approval');
2620 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2621 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2622 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pub.submit_for_approval');
2623 RAISE OKL_API.G_EXCEPTION_ERROR;
2624 END IF;
2625
2626 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract Approved successfully....');
2627 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2628
2629 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
2630 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
2631
2632 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2633 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2634 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2635 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
2636 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2637 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_APPROVAL_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2638 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2639
2640 EXCEPTION
2641 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2642 p_retcode := 2;
2643 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2644 p_api_name => l_api_name,
2645 p_pkg_name => g_pkg_name,
2646 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2647 x_msg_count => l_msg_count,
2648 x_msg_data => l_msg_data,
2649 p_api_type => g_api_type);
2650
2651 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2654
2655 -- print the error message in the output file
2656 IF (fnd_msg_pub.count_msg > 0) THEN
2657 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2658 LOOP
2659 fnd_msg_pub.get(
2660 p_msg_index => l_counter,
2661 p_encoded => 'F',
2662 p_data => l_data,
2663 p_msg_index_out => l_msg_index_out);
2664 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2665 END LOOP;
2666 END IF;
2667
2668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2669 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_APPROVAL_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2671
2672 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2673 p_retcode := 2;
2674 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2675 p_api_name => l_api_name,
2676 p_pkg_name => g_pkg_name,
2677 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2678 x_msg_count => l_msg_count,
2679 x_msg_data => l_msg_data,
2680 p_api_type => g_api_type);
2681
2682 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2683 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2684 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2685
2686 -- print the error message in the output file
2687 IF (fnd_msg_pub.count_msg > 0) THEN
2688 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2689 LOOP
2690 fnd_msg_pub.get(
2691 p_msg_index => l_counter,
2692 p_encoded => 'F',
2693 p_data => l_data,
2694 p_msg_index_out => l_msg_index_out);
2695 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2696 END LOOP;
2697 END IF;
2698
2699 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2700 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_APPROVAL_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2701 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2702
2703 WHEN OTHERS THEN
2704 p_retcode := 2;
2705 p_errbuf := SQLERRM;
2706 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2707 p_api_name => l_api_name,
2708 p_pkg_name => g_pkg_name,
2709 p_exc_name => 'OTHERS',
2710 x_msg_count => l_msg_count,
2711 x_msg_data => l_msg_data,
2712 p_api_type => g_api_type);
2713
2714 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2715 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2716 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2717
2718 -- print the error message in the output file
2719 IF (fnd_msg_pub.count_msg > 0) THEN
2720 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2721 LOOP
2722 fnd_msg_pub.get(
2723 p_msg_index => l_counter,
2724 p_encoded => 'F',
2725 p_data => l_data,
2726 p_msg_index_out => l_msg_index_out);
2727 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2728 END LOOP;
2729 END IF;
2730
2731 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2732 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_APPROVAL_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2733 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2734 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
2735
2736 END submit_for_approval;
2737
2738 -----------------------------------------------------------------------------
2739 -- PROCEDURE activate_contract
2740 -----------------------------------------------------------------------------
2741 -- Start of comments
2742 --
2743 -- procedure Name : activate_contract
2744 -- Description : Procedure called from Activation concurrent request
2745 -- Business Rules :
2746 -- Parameters : p_khr_id
2747 -- Version : 1.0
2748 -- History : XX-XXX-XXXX vthiruva Created
2749 -- End of comments
2750
2751 PROCEDURE activate_contract(
2752 p_errbuf OUT NOCOPY VARCHAR2,
2753 p_retcode OUT NOCOPY NUMBER,
2754 p_khr_id IN okc_k_headers_b.id%TYPE) IS
2755
2756 l_api_name CONSTANT VARCHAR2(30) := 'activate_contract';
2757 l_api_version CONSTANT NUMBER := 1.0;
2758 p_api_version CONSTANT NUMBER := 1.0;
2759 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2760 l_init_msg_list VARCHAR2(1) := 'T';
2761 l_msg_count NUMBER;
2762 l_msg_data VARCHAR2(2000);
2763 l_rem_amt NUMBER;
2764 l_sts_code okc_k_headers_b.sts_code%TYPE;
2765 l_qte_num okl_trx_quotes_b.quote_number%TYPE;
2766 l_msg_token_tbl msg_token_tbl;
2767 l_data VARCHAR2(2000);
2768 l_msg_index_out NUMBER;
2769 l_process_status VARCHAR2(1);
2770
2771 BEGIN
2772 p_retcode := 0;
2773 --print OKL copyright in the output file
2774 l_msg_token_tbl(1).token_name := 'API_NAME';
2775 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
2776 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
2777 l_msg_token_tbl(2).token_value := 'Activation';
2778 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2779 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
2780 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2781 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2782 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
2783 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2784
2785 l_return_status := OKL_API.START_ACTIVITY(l_api_name
2786 ,G_PKG_NAME
2787 ,l_init_msg_list
2788 ,l_api_version
2789 ,p_api_version
2790 ,'_PVT'
2791 ,l_return_status);
2792
2793 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2794 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
2795 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2796 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2797 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
2798 RAISE OKL_API.G_EXCEPTION_ERROR;
2799 END IF;
2800 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
2801 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2802
2803 --call program for contract activation
2804 okl_contract_book_pvt.approve_activate_contract(
2805 p_api_version => l_api_version,
2806 p_init_msg_list => l_init_msg_list,
2807 x_return_status => l_return_status,
2808 x_msg_count => l_msg_count,
2809 x_msg_data => l_msg_data,
2810 p_chr_id => p_khr_id,
2811 x_process_status => l_process_status);
2812
2813 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2814 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pvt.approve_activate_contract');
2815 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2816 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2817 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pvt.approve_activate_contract');
2818 RAISE OKL_API.G_EXCEPTION_ERROR;
2819 END IF;
2820
2821 IF (l_process_status = OKL_API.G_RET_STS_ERROR OR l_process_status = OKL_API.G_RET_STS_WARNING) THEN
2822 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2823 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2824 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2825 -- print the error message in the output file
2826 IF (fnd_msg_pub.count_msg > 0) THEN
2827 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2828 LOOP
2829 fnd_msg_pub.get(
2830 p_msg_index => l_counter,
2831 p_encoded => 'F',
2832 p_data => l_data,
2833 p_msg_index_out => l_msg_index_out);
2834 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2835 END LOOP;
2836 END IF;
2837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2838 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2839 END IF;
2840
2841 IF (l_process_status <> OKL_API.G_RET_STS_ERROR) THEN
2842 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Contract Activated successfully....');
2843 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2844 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2845 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2846 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
2847 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2848 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2849 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2850 END IF;
2851
2852 IF (l_process_status = OKL_API.G_RET_STS_ERROR) THEN
2853 p_retcode := 2;
2854 END IF;
2855
2856 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
2857 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
2858
2859
2860 EXCEPTION
2861 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2862 p_retcode := 2;
2863 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2864 p_api_name => l_api_name,
2865 p_pkg_name => g_pkg_name,
2866 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2867 x_msg_count => l_msg_count,
2868 x_msg_data => l_msg_data,
2869 p_api_type => g_api_type);
2870
2871 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2872 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2873 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2874
2875 -- print the error message in the output file
2876 IF (fnd_msg_pub.count_msg > 0) THEN
2877 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2878 LOOP
2879 fnd_msg_pub.get(
2880 p_msg_index => l_counter,
2881 p_encoded => 'F',
2882 p_data => l_data,
2883 p_msg_index_out => l_msg_index_out);
2884 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2885 END LOOP;
2886 END IF;
2887
2888 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2889 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2890 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2891
2892 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2893 p_retcode := 2;
2894 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2895 p_api_name => l_api_name,
2896 p_pkg_name => g_pkg_name,
2897 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2898 x_msg_count => l_msg_count,
2899 x_msg_data => l_msg_data,
2900 p_api_type => g_api_type);
2901
2902 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2903 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2904 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2905
2906 -- print the error message in the output file
2907 IF (fnd_msg_pub.count_msg > 0) THEN
2908 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2909 LOOP
2910 fnd_msg_pub.get(
2911 p_msg_index => l_counter,
2912 p_encoded => 'F',
2913 p_data => l_data,
2914 p_msg_index_out => l_msg_index_out);
2915 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2916 END LOOP;
2917 END IF;
2918
2919 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2920 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2921 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2922
2923 WHEN OTHERS THEN
2924 p_retcode := 2;
2925 p_errbuf := SQLERRM;
2926 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
2927 p_api_name => l_api_name,
2928 p_pkg_name => g_pkg_name,
2929 p_exc_name => 'OTHERS',
2930 x_msg_count => l_msg_count,
2931 x_msg_data => l_msg_data,
2932 p_api_type => g_api_type);
2933
2934 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2935 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
2936 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
2937
2938 -- print the error message in the output file
2939 IF (fnd_msg_pub.count_msg > 0) THEN
2940 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
2941 LOOP
2942 fnd_msg_pub.get(
2943 p_msg_index => l_counter,
2944 p_encoded => 'F',
2945 p_data => l_data,
2946 p_msg_index_out => l_msg_index_out);
2947 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
2948 END LOOP;
2949 END IF;
2950
2951 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
2952 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CONTRACT_ACTIVATION_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
2953 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
2954 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
2955
2956 END activate_contract;
2957
2958 -----------------------------------------------------------------------------
2959 -- PROCEDURE init_book_controller_trx
2960 -----------------------------------------------------------------------------
2961 -- Start of comments
2962 --
2963 -- Procedure Name : init_book_controller_trx
2964 -- Description : Procedure to insert 4 records into OKL_BOOK_CONTROLLER_TRX
2965 -- Called from OKL_CONTRACT_BOOK_PVT.execute_qa_check_list
2966 -- Business Rules :
2967 -- Parameters : p_khr_id
2968 -- Version : 1.0
2969 -- History : XX-XXX-XXXX asahoo Created
2970 -- End of comments
2971
2972 PROCEDURE init_book_controller_trx(
2973 p_api_version IN NUMBER,
2974 p_init_msg_list IN VARCHAR2,
2975 x_return_status OUT NOCOPY VARCHAR2,
2976 x_msg_count OUT NOCOPY NUMBER,
2977 x_msg_data OUT NOCOPY VARCHAR2,
2978 p_khr_id IN okc_k_headers_b.id%TYPE,
2979 x_batch_number OUT NOCOPY NUMBER) IS
2980
2981 l_api_name CONSTANT VARCHAR2(30) := 'init_book_controller_trx';
2982 l_api_version CONSTANT NUMBER := 1.0;
2983 l_batch_num OKL_BOOK_CONTROLLER_TRX.BATCH_NUMBER%TYPE;
2984 l_bct_tbl bct_tbl_type;
2985 lx_bct_tbl bct_tbl_type;
2986 i NUMBER;
2987
2988 --Cursor to check existence of contract trx records
2989 CURSOR c_book_ctrl_trx(p_khr_id IN NUMBER) IS
2990 SELECT 'Y'
2991 FROM OKL_BOOK_CONTROLLER_TRX
2992 WHERE khr_id = p_khr_id
2993 AND NVL(active_flag,'N') = 'Y';
2994
2995 --cursor to fetch the current status of the contract
2996 CURSOR get_curr_sts_code(p_khr_id okc_k_headers_b.id%TYPE) IS
2997 SELECT sts_code
2998 FROM okc_k_headers_b
2999 WHERE id = p_khr_id;
3000
3001 l_exists VARCHAR2(1) DEFAULT 'N';
3002 l_curr_sts_code okc_k_headers_b.sts_code%TYPE;
3003 l_qa_progress_status okl_book_controller_trx.progress_status%TYPE;
3004 l_ut_progress_status okl_book_controller_trx.progress_status%TYPE;
3005 l_st_progress_status okl_book_controller_trx.progress_status%TYPE;
3006 l_bk_progress_status okl_book_controller_trx.progress_status%TYPE;
3007
3008 BEGIN
3009 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3010 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'Begin(+)');
3011 END IF;
3012
3013 x_batch_number := null;
3014 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3015
3016 x_return_status := OKL_API.START_ACTIVITY(
3017 p_api_name => l_api_name,
3018 p_pkg_name => g_pkg_name,
3019 p_init_msg_list => p_init_msg_list,
3020 l_api_version => l_api_version,
3021 p_api_version => p_api_version,
3022 p_api_type => g_api_type,
3023 x_return_status => x_return_status);
3024
3025 -- check if activity started successfully
3026 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3027 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3028 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3029 RAISE OKL_API.G_EXCEPTION_ERROR;
3030 END IF;
3031
3032 OPEN c_book_ctrl_trx(p_khr_id => p_khr_id);
3033 FETCH c_book_ctrl_trx INTO l_exists;
3034 CLOSE c_book_ctrl_trx;
3035
3036 IF (l_exists = 'N') THEN
3037 --get new batch number from sequence
3038 l_batch_num := get_batch_id;
3039 IF(l_batch_num IS NULL OR l_batch_num = OKL_API.G_MISS_NUM) THEN
3040 --raise error if batch number could not be generated from seq.
3041 RAISE OKL_API.G_EXCEPTION_ERROR;
3042 END IF;
3043
3044 --fetch the current status of the contract
3045 OPEN get_curr_sts_code(p_khr_id);
3046 FETCH get_curr_sts_code INTO l_curr_sts_code;
3047 CLOSE get_curr_sts_code;
3048
3049 IF (l_curr_sts_code = 'PASSED') THEN
3050 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3051 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3052 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3053 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3054 ELSIF (l_curr_sts_code IN ('COMPLETE','APPROVED')) THEN
3055 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3056 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3057 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3058 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3059 ELSIF (l_curr_sts_code = 'PENDING_APPROVAL') THEN
3060 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3061 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3062 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3063 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_RUNNING;
3064 --Bug# 9119881
3065 ELSIF (l_curr_sts_code IN ('NEW','INCOMPLETE')) THEN
3066 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3067 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3068 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3069 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3070 ELSE
3071 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3072 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3073 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3074 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
3075 END IF;
3076 --Bug# 9119881
3077
3078 i := 0;
3079 --populate record for QA Validation
3080 i := i+1;
3081 l_bct_tbl(i) := populate_ctrl_trx_rec(
3082 p_batch_num => l_batch_num,
3083 p_srl_num => 10,
3084 p_khr_id => p_khr_id,
3085 p_prog_name => 'OKLBCTQA',
3086 p_prog_short_name => 'OKLBCTQA',
3087 p_progress_status => l_qa_progress_status,
3088 p_active_flag => 'Y');
3089
3090 --populate record for Upfront Tax
3091 i := i+1;
3092 l_bct_tbl(i) := populate_ctrl_trx_rec(
3093 p_batch_num => l_batch_num,
3094 p_srl_num => 20,
3095 p_khr_id => p_khr_id,
3096 p_prog_name => 'OKLBCTUT',
3097 p_prog_short_name => 'OKLBCTUT',
3098 p_progress_status => l_ut_progress_status,
3099 p_active_flag => 'Y');
3100
3101 --populate record for Price Contract
3102 i := i+1;
3103 l_bct_tbl(i) := populate_ctrl_trx_rec(
3104 p_batch_num => l_batch_num,
3105 p_srl_num => 30,
3106 p_khr_id => p_khr_id,
3107 p_prog_name => 'OKLBCTST',
3108 p_prog_short_name => 'OKLBCTST',
3109 p_progress_status => l_st_progress_status,
3110 p_active_flag => 'Y');
3111
3112 --populate record for Activation
3113 i := i+1;
3114 l_bct_tbl(i) := populate_ctrl_trx_rec(
3115 p_batch_num => l_batch_num,
3116 p_srl_num => 40,
3117 p_khr_id => p_khr_id,
3118 p_prog_name => 'OKLBCTBK',
3119 p_prog_short_name => 'OKLBCTBK',
3120 p_progress_status => l_bk_progress_status,
3121 p_active_flag => 'Y');
3122
3123 IF l_bct_tbl.COUNT > 0 THEN
3124 --insert records into controller tranasction table for the batch
3125 okl_bct_pvt.insert_row(
3126 p_api_version => p_api_version,
3127 p_init_msg_list => p_init_msg_list,
3128 x_return_status => x_return_status,
3129 x_msg_count => x_msg_count,
3130 x_msg_data => x_msg_data,
3131 p_bct_tbl => l_bct_tbl,
3132 x_bct_tbl => lx_bct_tbl);
3133
3134 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3135 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3136 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3137 RAISE OKL_API.G_EXCEPTION_ERROR;
3138 END IF;
3139
3140 x_batch_number := l_batch_num;
3141
3142 END IF;
3143
3144 END IF;
3145
3146
3147 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3148 x_msg_data => x_msg_data);
3149
3150 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'End(-)');
3152 END IF;
3153
3154 EXCEPTION
3155 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3156 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3157 p_api_name => l_api_name,
3158 p_pkg_name => g_pkg_name,
3159 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3160 x_msg_count => x_msg_count,
3161 x_msg_data => x_msg_data,
3162 p_api_type => g_api_type);
3163
3164 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3165 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - ERROR');
3166 END IF;
3167
3168 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3169 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3170 p_api_name => l_api_name,
3171 p_pkg_name => g_pkg_name,
3172 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3173 x_msg_count => x_msg_count,
3174 x_msg_data => x_msg_data,
3175 p_api_type => g_api_type);
3176
3177 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3178 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - UNEXCP ERROR');
3179 END IF;
3180
3181 WHEN OTHERS THEN
3182 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3183 p_api_name => l_api_name,
3184 p_pkg_name => g_pkg_name,
3185 p_exc_name => 'OTHERS',
3186 x_msg_count => x_msg_count,
3187 x_msg_data => x_msg_data,
3188 p_api_type => g_api_type);
3189
3190 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3191 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - OTHERS');
3192 END IF;
3193
3194 END init_book_controller_trx;
3195
3196 -----------------------------------------------------------------------------
3197 -- PROCEDURE update_book_controller_trx
3198 -----------------------------------------------------------------------------
3199 -- Start of comments
3200 --
3201 -- Procedure Name : update_book_controller_trx
3202 -- Description : Procedure to update status of records in OKL_BOOK_CONTROLLER_TRX table
3203 -- Called from OKL_CONTRACT_BOOK_PVT.execute_qa_check_list
3204 -- Business Rules :
3205 -- Parameters : p_khr_id p_prog_short_name p_progress_status
3206 -- Version : 1.0
3207 -- History : XX-XXX-XXXX asahoo Created
3208 -- End of comments
3209
3210 PROCEDURE update_book_controller_trx(
3211 p_api_version IN NUMBER,
3212 p_init_msg_list IN VARCHAR2,
3213 x_return_status OUT NOCOPY VARCHAR2,
3214 x_msg_count OUT NOCOPY NUMBER,
3215 x_msg_data OUT NOCOPY VARCHAR2,
3216 p_khr_id IN okc_k_headers_b.id%TYPE,
3217 p_prog_short_name IN okl_book_controller_trx.prog_short_name%TYPE,
3218 p_conc_req_id IN okl_book_controller_trx.conc_req_id%TYPE DEFAULT OKL_API.G_MISS_NUM,
3219 p_progress_status IN okl_book_controller_trx.progress_status%TYPE) IS
3220
3221 l_api_name CONSTANT VARCHAR2(30) := 'update_book_controller_trx';
3222 l_api_version CONSTANT NUMBER := 1.0;
3223 l_bct_rec bct_rec_type;
3224 x_bct_rec bct_rec_type;
3225
3226 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3227 SELECT *
3228 FROM okl_book_controller_trx
3229 WHERE khr_id = p_khr_id
3230 AND prog_short_name = nvl(p_prog_short_name, prog_short_name)
3231 AND NVL(active_flag,'N') = 'Y';
3232
3233 l_book_ctrl_trx c_book_ctrl_trx%ROWTYPE;
3234
3235 BEGIN
3236 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3237 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'Begin(+)');
3238 END IF;
3239 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3240
3241 x_return_status := OKL_API.START_ACTIVITY(
3242 p_api_name => l_api_name,
3243 p_pkg_name => g_pkg_name,
3244 p_init_msg_list => p_init_msg_list,
3245 l_api_version => l_api_version,
3246 p_api_version => p_api_version,
3247 p_api_type => g_api_type,
3248 x_return_status => x_return_status);
3249
3250 -- check if activity started successfully
3251 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3252 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3253 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3254 RAISE OKL_API.G_EXCEPTION_ERROR;
3255 END IF;
3256
3257 IF (p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM) THEN
3258 RAISE OKL_API.G_EXCEPTION_ERROR;
3259 ELSIF (p_progress_status IS NULL OR p_progress_status = OKL_API.G_MISS_CHAR) THEN
3260 RAISE OKL_API.G_EXCEPTION_ERROR;
3261 END IF;
3262
3263 OPEN c_book_ctrl_trx(p_khr_id,p_prog_short_name);
3264 LOOP
3265 FETCH c_book_ctrl_trx INTO l_book_ctrl_trx;
3266 EXIT WHEN c_book_ctrl_trx%NOTFOUND;
3267 l_bct_rec.user_id := l_book_ctrl_trx.user_id;
3268 l_bct_rec.org_id := l_book_ctrl_trx.org_id;
3269 l_bct_rec.batch_number := l_book_ctrl_trx.batch_number;
3270 l_bct_rec.processing_srl_number := l_book_ctrl_trx.processing_srl_number;
3271 l_bct_rec.khr_id := l_book_ctrl_trx.khr_id;
3272 l_bct_rec.program_name := l_book_ctrl_trx.program_name;
3273 l_bct_rec.prog_short_name := l_book_ctrl_trx.prog_short_name;
3274 IF (p_conc_req_id = OKL_API.G_MISS_NUM) THEN
3275 l_bct_rec.conc_req_id := l_book_ctrl_trx.conc_req_id;
3276 ELSE
3277 l_bct_rec.conc_req_id := p_conc_req_id;
3278 END IF;
3279 l_bct_rec.progress_status := p_progress_status;
3280 l_bct_rec.active_flag := l_book_ctrl_trx.active_flag;
3281
3282 --call TAPI to update okl_book_controller_trx
3283 okl_bct_pvt.update_row(
3284 p_api_version => p_api_version,
3285 p_init_msg_list => p_init_msg_list,
3286 x_return_status => x_return_status,
3287 x_msg_count => x_msg_count,
3288 x_msg_data => x_msg_data,
3289 p_bct_rec => l_bct_rec,
3290 x_bct_rec => x_bct_rec);
3291
3292 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3294 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3295 RAISE OKL_API.G_EXCEPTION_ERROR;
3296 END IF;
3297 END LOOP;
3298 CLOSE c_book_ctrl_trx;
3299
3300 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3301 x_msg_data => x_msg_data);
3302
3303 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3304 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'End(-)');
3305 END IF;
3306
3307 EXCEPTION
3308 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3309 IF (c_book_ctrl_trx%ISOPEN) THEN
3310 CLOSE c_book_ctrl_trx;
3311 END IF;
3312 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3313 p_api_name => l_api_name,
3314 p_pkg_name => g_pkg_name,
3315 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3316 x_msg_count => x_msg_count,
3317 x_msg_data => x_msg_data,
3318 p_api_type => g_api_type);
3319
3320 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3321 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - ERROR');
3322 END IF;
3323
3324 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3325 IF (c_book_ctrl_trx%ISOPEN) THEN
3326 CLOSE c_book_ctrl_trx;
3327 END IF;
3328 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3329 p_api_name => l_api_name,
3330 p_pkg_name => g_pkg_name,
3331 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3332 x_msg_count => x_msg_count,
3333 x_msg_data => x_msg_data,
3334 p_api_type => g_api_type);
3335
3336 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3337 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - UNEXCP ERROR');
3338 END IF;
3339
3340 WHEN OTHERS THEN
3341 IF (c_book_ctrl_trx%ISOPEN) THEN
3342 CLOSE c_book_ctrl_trx;
3343 END IF;
3344 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3345 p_api_name => l_api_name,
3346 p_pkg_name => g_pkg_name,
3347 p_exc_name => 'OTHERS',
3348 x_msg_count => x_msg_count,
3349 x_msg_data => x_msg_data,
3350 p_api_type => g_api_type);
3351
3352 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3353 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - OTHERS');
3354 END IF;
3355 END update_book_controller_trx;
3356
3357 -----------------------------------------------------------------------------
3358 -- PROCEDURE cancel_contract_activation
3359 -----------------------------------------------------------------------------
3360 -- Start of comments
3361 --
3362 -- Procedure Name : cancel_contract_activation
3363 -- Description : Procedure to update status of contract header, line and records in okl_book_controller_trx table
3364 -- Called from Authoring UI
3365 -- Business Rules :
3366 -- Parameters : p_khr_id
3367 -- Version : 1.0
3368 -- History : XX-XXX-XXXX asahoo Created
3369 -- End of comments
3370
3371 PROCEDURE cancel_contract_activation(
3372 p_api_version IN NUMBER,
3373 p_init_msg_list IN VARCHAR2,
3374 x_return_status OUT NOCOPY VARCHAR2,
3375 x_msg_count OUT NOCOPY NUMBER,
3376 x_msg_data OUT NOCOPY VARCHAR2,
3377 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3378 l_api_name CONSTANT VARCHAR2(30) := 'cancel_contract_activation';
3379 l_api_version CONSTANT NUMBER := 1.0;
3380 l_prog_short_name OKL_BOOK_CONTROLLER_TRX.prog_short_name%TYPE;
3381 BEGIN
3382 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3383 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'Begin(+)');
3384 END IF;
3385
3386 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3387
3388 x_return_status := OKL_API.START_ACTIVITY(
3389 p_api_name => l_api_name,
3390 p_pkg_name => g_pkg_name,
3391 p_init_msg_list => p_init_msg_list,
3392 l_api_version => l_api_version,
3393 p_api_version => p_api_version,
3394 p_api_type => g_api_type,
3395 x_return_status => x_return_status);
3396
3397 -- check if activity started successfully
3398 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3399 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3400 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3401 RAISE OKL_API.G_EXCEPTION_ERROR;
3402 END IF;
3403
3404 okl_contract_status_pub.update_contract_status(
3405 p_api_version => p_api_version,
3406 p_init_msg_list => p_init_msg_list,
3407 x_return_status => x_return_status,
3408 x_msg_count => x_msg_count,
3409 x_msg_data => x_msg_data,
3410 p_khr_status => 'INCOMPLETE',
3411 p_chr_id => p_khr_id);
3412 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3414 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3415 RAISE OKL_API.G_EXCEPTION_ERROR;
3416 END IF;
3417
3418 okl_contract_status_pub.cascade_lease_status
3419 (p_api_version => p_api_version,
3420 p_init_msg_list => p_init_msg_list,
3421 x_return_status => x_return_status,
3422 x_msg_count => x_msg_count,
3423 x_msg_data => x_msg_data,
3424 p_chr_id => p_khr_id);
3425
3426 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3427 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3428 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3429 RAISE OKL_API.G_EXCEPTION_ERROR;
3430 END IF;
3431
3432 FOR i in 1..4
3433 LOOP
3434 IF (i = 1) THEN
3435 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_VALIDATE_CONTRACT;
3436 ELSIF (i = 2) THEN
3437 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_CALC_UPFRONT_TAX;
3438 ELSIF (i = 3) THEN
3439 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT;
3440 ELSIF (i = 4) THEN
3441 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_SUBMIT_CONTRACT;
3442 END IF;
3443
3444 okl_book_controller_pvt.update_book_controller_trx(
3445 p_api_version => p_api_version,
3446 p_init_msg_list => p_init_msg_list,
3447 x_return_status => x_return_status,
3448 x_msg_count => x_msg_count,
3449 x_msg_data => x_msg_data,
3450 p_khr_id => p_khr_id,
3451 p_prog_short_name => l_prog_short_name,
3452 p_conc_req_id => NULL,
3453 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
3454
3455 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3456 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3457 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3458 RAISE OKL_API.G_EXCEPTION_ERROR;
3459 END IF;
3460
3461 END LOOP;
3462
3463
3464 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3465 x_msg_data => x_msg_data);
3466
3467 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3468 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'End(-)');
3469 END IF;
3470
3471 EXCEPTION
3472 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3473 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3474 p_api_name => l_api_name,
3475 p_pkg_name => g_pkg_name,
3476 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3477 x_msg_count => x_msg_count,
3478 x_msg_data => x_msg_data,
3479 p_api_type => g_api_type);
3480
3481 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3482 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - ERROR');
3483 END IF;
3484
3485 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3486 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3487 p_api_name => l_api_name,
3488 p_pkg_name => g_pkg_name,
3489 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3490 x_msg_count => x_msg_count,
3491 x_msg_data => x_msg_data,
3492 p_api_type => g_api_type);
3493
3494 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3495 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - UNEXCP ERROR');
3496 END IF;
3497
3498 WHEN OTHERS THEN
3499 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3500 p_api_name => l_api_name,
3501 p_pkg_name => g_pkg_name,
3502 p_exc_name => 'OTHERS',
3503 x_msg_count => x_msg_count,
3504 x_msg_data => x_msg_data,
3505 p_api_type => g_api_type);
3506
3507 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3508 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - OTHERS');
3509 END IF;
3510 END cancel_contract_activation;
3511
3512 -----------------------------------------------------------------------------
3513 -- PROCEDURE validate_contract_nxtbtn
3514 -----------------------------------------------------------------------------
3515 -- Start of comments
3516 --
3517 -- Procedure Name : validate_contract_nxtbtn
3518 -- Description : Procedure to do validation when next button in validate
3519 -- contrtact page is clicked.
3520 -- Business Rules :
3521 -- Parameters : p_khr_id
3522 -- Version : 1.0
3523 -- History : XX-XXX-XXXX asahoo Created
3524 -- End of comments
3525
3526 PROCEDURE validate_contract_nxtbtn(
3527 p_api_version IN NUMBER,
3528 p_init_msg_list IN VARCHAR2,
3529 x_return_status OUT NOCOPY VARCHAR2,
3530 x_msg_count OUT NOCOPY NUMBER,
3531 x_msg_data OUT NOCOPY VARCHAR2,
3532 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3533 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract_nxtbtn';
3534 l_api_version CONSTANT NUMBER := 1.0;
3535
3536 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3537 SELECT progress_status
3538 FROM okl_book_controller_trx
3539 WHERE khr_id = p_khr_id
3540 AND prog_short_name = p_prog_short_name
3541 AND NVL(active_flag,'N') = 'Y';
3542
3543 l_progress_status okl_book_controller_trx.progress_status%TYPE;
3544 x_process_status VARCHAR2(30);
3545 BEGIN
3546 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3547 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'Begin(+)');
3548 END IF;
3549
3550 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3551
3552 x_return_status := OKL_API.START_ACTIVITY(
3553 p_api_name => l_api_name,
3554 p_pkg_name => g_pkg_name,
3555 p_init_msg_list => p_init_msg_list,
3556 l_api_version => l_api_version,
3557 p_api_version => p_api_version,
3558 p_api_type => g_api_type,
3559 x_return_status => x_return_status);
3560
3561 -- check if activity started successfully
3562 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3563 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3564 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3565 RAISE OKL_API.G_EXCEPTION_ERROR;
3566 END IF;
3567
3568 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_validate_contract);
3569 FETCH c_book_ctrl_trx INTO l_progress_status;
3570 CLOSE c_book_ctrl_trx;
3571
3572 IF (l_progress_status = okl_book_controller_pvt.g_prog_sts_complete) THEN
3573 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_calc_upfront_tax);
3574 FETCH c_book_ctrl_trx INTO l_progress_status;
3575 CLOSE c_book_ctrl_trx;
3576 IF (l_progress_status = okl_book_controller_pvt.g_prog_sts_pending) THEN
3577 OKL_CONTRACT_BOOK_PVT.calculate_upfront_tax(
3578 p_api_version => p_api_version,
3579 p_init_msg_list => p_init_msg_list,
3580 x_return_status => x_return_status,
3581 x_msg_count => x_msg_count,
3582 x_msg_data => x_msg_data,
3583 p_chr_id => p_khr_id,
3584 x_process_status => x_process_status);
3585 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3586 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3587 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3588 RAISE OKL_API.G_EXCEPTION_ERROR;
3589 END IF;
3590 END IF;
3591
3592 ELSE
3593 -- The Contract must pass validation before upfront tax can be calculated.
3594 OKL_API.set_message(p_app_name => G_APP_NAME,
3595 p_msg_name => 'OKL_LLA_NOT_VALIDATED');
3596 RAISE OKL_API.G_EXCEPTION_ERROR;
3597 END IF;
3598
3599 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3600 x_msg_data => x_msg_data);
3601
3602 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3603 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'End(-)');
3604 END IF;
3605
3606 EXCEPTION
3607 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3608 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3609 p_api_name => l_api_name,
3610 p_pkg_name => g_pkg_name,
3611 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3612 x_msg_count => x_msg_count,
3613 x_msg_data => x_msg_data,
3614 p_api_type => g_api_type);
3615
3616 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - ERROR');
3618 END IF;
3619
3620 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3621 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3622 p_api_name => l_api_name,
3623 p_pkg_name => g_pkg_name,
3624 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3625 x_msg_count => x_msg_count,
3626 x_msg_data => x_msg_data,
3627 p_api_type => g_api_type);
3628
3629 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3630 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - UNEXCP ERROR');
3631 END IF;
3632
3633 WHEN OTHERS THEN
3634 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3635 p_api_name => l_api_name,
3636 p_pkg_name => g_pkg_name,
3637 p_exc_name => 'OTHERS',
3638 x_msg_count => x_msg_count,
3639 x_msg_data => x_msg_data,
3640 p_api_type => g_api_type);
3641
3642 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3643 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - OTHERS');
3644 END IF;
3645 END validate_contract_nxtbtn;
3646
3647 -----------------------------------------------------------------------------
3648 -- PROCEDURE calc_upfronttax_nxtbtn
3649 -----------------------------------------------------------------------------
3650 -- Start of comments
3651 --
3652 -- Procedure Name : calc_upfronttax_nxtbtn
3653 -- Description : Procedure to do validation when next button in Upfront
3654 -- tax page is clicked.
3655 -- Business Rules :
3656 -- Parameters : p_khr_id
3657 -- Version : 1.0
3658 -- History : XX-XXX-XXXX asahoo Created
3659 -- End of comments
3660
3661 PROCEDURE calc_upfronttax_nxtbtn(
3662 p_api_version IN NUMBER,
3663 p_init_msg_list IN VARCHAR2,
3664 x_return_status OUT NOCOPY VARCHAR2,
3665 x_msg_count OUT NOCOPY NUMBER,
3666 x_msg_data OUT NOCOPY VARCHAR2,
3667 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3668 l_api_name CONSTANT VARCHAR2(30) := 'calc_upfronttax_nxtbtn';
3669 l_api_version CONSTANT NUMBER := 1.0;
3670
3671 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3672 SELECT progress_status
3673 FROM okl_book_controller_trx
3674 WHERE khr_id = p_khr_id
3675 AND prog_short_name = p_prog_short_name
3676 AND NVL(active_flag,'N') = 'Y';
3677
3678 l_progress_status okl_book_controller_trx.progress_status%TYPE;
3679 x_process_status VARCHAR2(30);
3680 BEGIN
3681 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3682 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.calc_upfronttax_nxtbtn.', 'Begin(+)');
3683 END IF;
3684
3685 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3686
3687 x_return_status := OKL_API.START_ACTIVITY(
3688 p_api_name => l_api_name,
3689 p_pkg_name => g_pkg_name,
3690 p_init_msg_list => p_init_msg_list,
3691 l_api_version => l_api_version,
3692 p_api_version => p_api_version,
3693 p_api_type => g_api_type,
3694 x_return_status => x_return_status);
3695
3696 -- check if activity started successfully
3697 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3698 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3699 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3700 RAISE OKL_API.G_EXCEPTION_ERROR;
3701 END IF;
3702
3703 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_calc_upfront_tax);
3704 FETCH c_book_ctrl_trx INTO l_progress_status;
3705 CLOSE c_book_ctrl_trx;
3706
3707 IF (l_progress_status <> okl_book_controller_pvt.g_prog_sts_complete) THEN
3708 OKL_LA_SALES_TAX_PVT.validate_upfront_tax_fee(
3709 p_api_version => p_api_version,
3710 p_init_msg_list => p_init_msg_list,
3711 x_return_status => x_return_status,
3712 x_msg_count => x_msg_count,
3713 x_msg_data => x_msg_data,
3714 p_chr_id => p_khr_id);
3715 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3716 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3717 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3718 RAISE OKL_API.G_EXCEPTION_ERROR;
3719 END IF;
3720 END IF;
3721
3722 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3723 x_msg_data => x_msg_data);
3724
3725 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3726 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.calc_upfronttax_nxtbtn.', 'End(-)');
3727 END IF;
3728
3729 EXCEPTION
3730 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3731 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3732 p_api_name => l_api_name,
3733 p_pkg_name => g_pkg_name,
3734 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3735 x_msg_count => x_msg_count,
3736 x_msg_data => x_msg_data,
3737 p_api_type => g_api_type);
3738
3739 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3740 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - ERROR');
3741 END IF;
3742
3743 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3744 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3745 p_api_name => l_api_name,
3746 p_pkg_name => g_pkg_name,
3747 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3748 x_msg_count => x_msg_count,
3749 x_msg_data => x_msg_data,
3750 p_api_type => g_api_type);
3751
3752 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3753 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - UNEXCP ERROR');
3754 END IF;
3755
3756 WHEN OTHERS THEN
3757 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3758 p_api_name => l_api_name,
3759 p_pkg_name => g_pkg_name,
3760 p_exc_name => 'OTHERS',
3761 x_msg_count => x_msg_count,
3762 x_msg_data => x_msg_data,
3763 p_api_type => g_api_type);
3764
3765 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3766 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - OTHERS');
3767 END IF;
3768 END calc_upfronttax_nxtbtn;
3769
3770 -----------------------------------------------------------------------------
3771 -- PROCEDURE calculate_upfront_tax
3772 -----------------------------------------------------------------------------
3773 -- Start of comments
3774 --
3775 -- procedure Name : calculate_upfront_tax
3776 -- Description : Procedure called from exec_controller_prg1
3777 -- Business Rules :
3778 -- Parameters : p_khr_id
3779 -- Version : 1.0
3780 -- History : XX-XXX-XXXX asahoo Created
3781 -- End of comments
3782
3783 PROCEDURE calculate_upfront_tax(
3784 p_errbuf OUT NOCOPY VARCHAR2,
3785 p_retcode OUT NOCOPY NUMBER,
3786 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3787
3788 l_api_name CONSTANT VARCHAR2(30) := 'calculate_upfront_tax';
3789 l_api_version CONSTANT NUMBER := 1.0;
3790 p_api_version CONSTANT NUMBER := 1.0;
3791 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3792 l_init_msg_list VARCHAR2(1) := 'T';
3793 l_msg_count NUMBER;
3794 l_msg_data VARCHAR2(2000);
3795 l_msg_token_tbl msg_token_tbl;
3796 l_data VARCHAR2(2000);
3797 l_msg_index_out NUMBER;
3798 l_process_status VARCHAR2(1);
3799
3800
3801 BEGIN
3802 p_retcode := 0;
3803 --print OKL copyright in the output file
3804 l_msg_token_tbl(1).token_name := 'API_NAME';
3805 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
3806 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
3807 l_msg_token_tbl(2).token_value := 'Calculate Upfront Tax';
3808 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3809 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
3810 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3811 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CALC_UPFRNTTX_START')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
3812 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
3813 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3814
3815 l_return_status := OKL_API.START_ACTIVITY(l_api_name
3816 ,G_PKG_NAME
3817 ,l_init_msg_list
3818 ,l_api_version
3819 ,p_api_version
3820 ,'_PVT'
3821 ,l_return_status);
3822
3823 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3824 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
3825 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3826 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3827 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
3828 RAISE OKL_API.G_EXCEPTION_ERROR;
3829 END IF;
3830 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
3831 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
3832
3833 --call Upfront Tax for Booking
3834 okl_contract_book_pvt.calculate_upfront_tax(
3835 p_api_version => l_api_version,
3836 p_init_msg_list => l_init_msg_list,
3837 x_return_status => l_return_status,
3838 x_msg_count => l_msg_count,
3839 x_msg_data => l_msg_data,
3840 p_chr_id => p_khr_id,
3841 x_process_status => l_process_status);
3842
3843 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3844 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pvt.calculate_upfront_tax');
3845 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3846 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3847 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pvt.calculate_upfront_tax');
3848 RAISE OKL_API.G_EXCEPTION_ERROR;
3849 END IF;
3850
3851 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3852 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3853 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3854 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3855
3856 IF (l_process_status = OKL_API.G_RET_STS_ERROR OR l_process_status = OKL_API.G_RET_STS_WARNING) THEN
3857 -- print the error message in the output file
3858 IF (fnd_msg_pub.count_msg > 0) THEN
3859 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3860 LOOP
3861 fnd_msg_pub.get(
3862 p_msg_index => l_counter,
3863 p_encoded => 'F',
3864 p_data => l_data,
3865 p_msg_index_out => l_msg_index_out);
3866 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3867 END LOOP;
3868 END IF;
3869 END IF;
3870
3871 IF (l_process_status <> OKL_API.G_RET_STS_ERROR) THEN
3872 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Upfront Tax was calculated successfully....');
3873 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
3874 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
3875 END IF;
3876
3877 IF (l_process_status = OKL_API.G_RET_STS_ERROR) THEN
3878 p_retcode := 2;
3879 END IF;
3880
3881 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CALC_UPFRNTTX_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
3882 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3883
3884 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
3885 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
3886
3887 EXCEPTION
3888 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3889 p_retcode := 2;
3890
3891 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3892 p_api_name => l_api_name,
3893 p_pkg_name => g_pkg_name,
3894 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3895 x_msg_count => l_msg_count,
3896 x_msg_data => l_msg_data,
3897 p_api_type => g_api_type);
3898
3899 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3900 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3901 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3902
3903 -- print the error message in the output file
3904 IF (fnd_msg_pub.count_msg > 0) THEN
3905 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3906 LOOP
3907 fnd_msg_pub.get(
3908 p_msg_index => l_counter,
3909 p_encoded => 'F',
3910 p_data => l_data,
3911 p_msg_index_out => l_msg_index_out);
3912 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3913 END LOOP;
3914 END IF;
3915
3916 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3917 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CALC_UPFRNTTX_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
3918 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3919
3920 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3921 p_retcode := 2;
3922
3923 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3924 p_api_name => l_api_name,
3925 p_pkg_name => g_pkg_name,
3926 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3927 x_msg_count => l_msg_count,
3928 x_msg_data => l_msg_data,
3929 p_api_type => g_api_type);
3930
3931 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3932 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3933 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3934
3935 -- print the error message in the output file
3936 IF (fnd_msg_pub.count_msg > 0) THEN
3937 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3938 LOOP
3939 fnd_msg_pub.get(
3940 p_msg_index => l_counter,
3941 p_encoded => 'F',
3942 p_data => l_data,
3943 p_msg_index_out => l_msg_index_out);
3944 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3945 END LOOP;
3946 END IF;
3947
3948 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3949 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CALC_UPFRNTTX_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
3950 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3951
3952 WHEN OTHERS THEN
3953 p_retcode := 2;
3954 p_errbuf := SQLERRM;
3955
3956 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3957 p_api_name => l_api_name,
3958 p_pkg_name => g_pkg_name,
3959 p_exc_name => 'OTHERS',
3960 x_msg_count => l_msg_count,
3961 x_msg_data => l_msg_data,
3962 p_api_type => g_api_type);
3963
3964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3965 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3966 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3967
3968 -- print the error message in the output file
3969 IF (fnd_msg_pub.count_msg > 0) THEN
3970 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3971 LOOP
3972 fnd_msg_pub.get(
3973 p_msg_index => l_counter,
3974 p_encoded => 'F',
3975 p_data => l_data,
3976 p_msg_index_out => l_msg_index_out);
3977 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3978 END LOOP;
3979 END IF;
3980 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3981 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_CALC_UPFRNTTX_END')||' '||TO_CHAR(sysdate,'DD-MON-YYYY HH:MM:SS'));
3982 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3983 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
3984
3985 END calculate_upfront_tax;
3986
3987 --Bug# 8798934
3988 -----------------------------------------------------------------------------
3989 -- FUNCTION is_prb_upgrade_required
3990 -----------------------------------------------------------------------------
3991 -- Start of comments
3992 --
3993 -- procedure Name : is_prb_upgrade_required
3994 -- Description : Function called from Contract Activation Train -
3995 -- Price and Submit UI
3996 -- Business Rules :
3997 -- Parameters : p_khr_id
3998 -- Version : 1.0
3999 -- History : XX-XXX-XXXX rpillay Created
4000 -- End of comments
4001 FUNCTION is_prb_upgrade_required(
4002 p_khr_id IN NUMBER) RETURN VARCHAR2
4003 IS
4004
4005 l_prb_upgrade_required VARCHAR2(1) := 'N';
4006
4007 CURSOR l_chr_csr(p_khr_id IN NUMBER) IS
4008 SELECT chrb.orig_system_id1 orig_chr_id
4009 FROM okc_k_headers_b chrb,
4010 okl_trx_contracts trx
4011 WHERE chrb.id = p_khr_id
4012 AND chrb.orig_system_source_code = 'OKL_REBOOK'
4013 AND trx.khr_id_new = p_khr_id
4014 AND trx.tcn_type = 'TRBK'
4015 AND trx.tsu_code = 'ENTERED'
4016 AND trx.representation_type = 'PRIMARY';
4017
4018 CURSOR l_pricing_engine_csr(p_khr_id IN NUMBER) IS
4019 SELECT gts.pricing_engine
4020 FROM okl_k_headers khr,
4021 okl_products pdt,
4022 okl_ae_tmpt_sets aes,
4023 okl_st_gen_tmpt_sets gts
4024 WHERE khr.pdt_id = pdt.id
4025 AND pdt.aes_id = aes.id
4026 AND aes.gts_id = gts.id
4027 AND khr.id = p_khr_id;
4028
4029 CURSOR l_acct_sys_opt_csr IS
4030 SELECT amort_inc_adj_rev_dt_yn
4031 FROM okl_sys_acct_opts;
4032
4033 CURSOR l_chr_upg_csr(p_khr_id IN NUMBER) IS
4034 SELECT 'Y' chr_upgraded_yn
4035 FROM okl_stream_trx_data
4036 WHERE orig_khr_id = p_khr_id
4037 AND last_trx_state = 'Y';
4038
4039 l_chr_rec l_chr_csr%ROWTYPE;
4040 l_pricing_engine_rec l_pricing_engine_csr%ROWTYPE;
4041 l_acct_sys_opt_rec l_acct_sys_opt_csr%ROWTYPE;
4042 l_chr_upgraded_yn VARCHAR2(1);
4043
4044 BEGIN
4045
4046 OPEN l_chr_csr(p_khr_id => p_khr_id);
4047 FETCH l_chr_csr INTO l_chr_rec;
4048 CLOSE l_chr_csr;
4049
4050 IF (l_chr_rec.orig_chr_id IS NOT NULL) THEN
4051
4052 OPEN l_acct_sys_opt_csr;
4053 FETCH l_acct_sys_opt_csr INTO l_acct_sys_opt_rec;
4054 CLOSE l_acct_sys_opt_csr;
4055
4056 IF (NVL(l_acct_sys_opt_rec.amort_inc_adj_rev_dt_yn,'N') = 'Y') THEN
4057
4058 OPEN l_pricing_engine_csr(l_chr_rec.orig_chr_id);
4059 FETCH l_pricing_engine_csr INTO l_pricing_engine_rec;
4060 CLOSE l_pricing_engine_csr;
4061
4062 IF (l_pricing_engine_rec.pricing_engine = 'EXTERNAL') THEN
4063
4064 l_chr_upgraded_yn := 'N';
4065 OPEN l_chr_upg_csr(p_khr_id => l_chr_rec.orig_chr_id);
4066 FETCH l_chr_upg_csr INTO l_chr_upgraded_yn;
4067 CLOSE l_chr_upg_csr;
4068
4069 IF (NVL(l_chr_upgraded_yn,'N') = 'N') THEN
4070
4071 l_prb_upgrade_required := 'Y';
4072
4073 END IF;
4074 END IF;
4075 END IF;
4076 END IF;
4077
4078 RETURN l_prb_upgrade_required;
4079
4080 EXCEPTION
4081 WHEN OTHERS THEN
4082 RETURN NULL;
4083 END is_prb_upgrade_required;
4084
4085 -----------------------------------------------------------------------------
4086 -- PROCEDURE submit_prb_upgrade
4087 -----------------------------------------------------------------------------
4088 -- Start of comments
4089 --
4090 -- procedure Name : submit_prb_upgrade
4091 -- Description : Procedure called from Upgrade button on
4092 -- Contract Activation Train - Price and Submit UI
4093 -- Business Rules :
4094 -- Parameters : p_khr_id
4095 -- Version : 1.0
4096 -- History : XX-XXX-XXXX rpillay Created
4097 -- End of comments
4098 PROCEDURE submit_prb_upgrade(
4099 p_api_version IN NUMBER,
4100 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
4101 x_return_status OUT NOCOPY VARCHAR2,
4102 x_msg_count OUT NOCOPY NUMBER,
4103 x_msg_data OUT NOCOPY VARCHAR2,
4104 p_khr_id IN NUMBER,
4105 x_request_numbers OUT NOCOPY VARCHAR2) IS
4106
4107 l_api_name CONSTANT VARCHAR2(30) := 'submit_prb_upgrade';
4108 l_api_version CONSTANT NUMBER := 1.0;
4109
4110 CURSOR l_chk_upg_req_csr(p_khr_id IN NUMBER) IS
4111 SELECT 'Y' upg_req_exists_yn
4112 FROM okl_stream_interfaces
4113 WHERE khr_id = p_khr_id
4114 AND orp_code = 'UPGRADE'
4115 AND sis_code IN ('PROCESSING_REQUEST','PROCESS_COMPLETE_ERRORS','PROCESS_COMPLETE','RET_DATA_RECEIVED');
4116
4117 CURSOR l_chr_csr(p_khr_id IN NUMBER) IS
4118 SELECT chrb_orig.id orig_chr_id,
4119 chrb_orig.contract_number
4120 FROM okc_k_headers_b chrb_rbk,
4121 okc_k_headers_b chrb_orig,
4122 okl_trx_contracts trx
4123 WHERE chrb_rbk.id = p_khr_id
4124 AND chrb_rbk.orig_system_source_code = 'OKL_REBOOK'
4125 AND chrb_orig.id = chrb_rbk.orig_system_id1
4126 AND trx.khr_id = chrb_orig.id
4127 AND trx.khr_id_new = p_khr_id
4128 AND trx.tcn_type = 'TRBK'
4129 AND trx.tsu_code = 'ENTERED'
4130 AND trx.representation_type = 'PRIMARY';
4131
4132 l_chr_rec l_chr_csr%ROWTYPE;
4133 l_upg_req_exists_yn VARCHAR2(1);
4134 l_request_id NUMBER;
4135 l_trans_status VARCHAR2(100);
4136 l_rep_request_id NUMBER;
4137 l_rep_trans_status VARCHAR2(100);
4138
4139 l_request_numbers_token VARCHAR2(1000);
4140
4141 BEGIN
4142
4143 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4144 x_request_numbers := NULL;
4145
4146 x_return_status := OKL_API.START_ACTIVITY(
4147 p_api_name => l_api_name,
4148 p_pkg_name => g_pkg_name,
4149 p_init_msg_list => p_init_msg_list,
4150 l_api_version => l_api_version,
4151 p_api_version => p_api_version,
4152 p_api_type => g_api_type,
4153 x_return_status => x_return_status);
4154
4155 -- check if activity started successfully
4156 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4157 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4158 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4159 RAISE OKL_API.G_EXCEPTION_ERROR;
4160 END IF;
4161
4162 OPEN l_chr_csr(p_khr_id => p_khr_id);
4163 FETCH l_chr_csr INTO l_chr_rec;
4164 CLOSE l_chr_csr;
4165
4166 IF (l_chr_rec.orig_chr_id IS NOT NULL) THEN
4167
4168 l_upg_req_exists_yn := 'N';
4169 OPEN l_chk_upg_req_csr(p_khr_id => l_chr_rec.orig_chr_id);
4170 FETCH l_chk_upg_req_csr INTO l_upg_req_exists_yn;
4171 CLOSE l_chk_upg_req_csr;
4172
4173 IF (l_upg_req_exists_yn = 'Y') THEN
4174
4175 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
4176 p_msg_name => 'OKL_LLA_RBK_UPG_IN_PROG',
4177 p_token1 => 'CONTRACT_NUMBER',
4178 p_token1_value => l_chr_rec.contract_number);
4179
4180 RAISE OKL_API.G_EXCEPTION_ERROR;
4181
4182 ELSE
4183 -- establish the external_id values for the contracts, if they don't have one.
4184 OKL_LLA_UTIL_PVT.update_external_id(p_chr_id => l_chr_rec.orig_chr_id,
4185 x_return_status => x_return_status);
4186
4187 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS
4188 THEN
4189 RAISE OKL_API.G_EXCEPTION_ERROR;
4190 END IF;
4191
4192 OKL_LA_STREAM_PVT.upgrade_esg_khr_for_prb(
4193 p_chr_id => l_chr_rec.orig_chr_id
4194 ,x_return_status => x_return_status
4195 ,x_msg_count => x_msg_count
4196 ,x_msg_data => x_msg_data
4197 ,x_request_id => l_request_id
4198 ,x_trans_status => l_trans_status
4199 ,x_rep_request_id => l_rep_request_id
4200 ,x_rep_trans_status => l_rep_trans_status );
4201
4202 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4203 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4204 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4205 RAISE OKL_API.G_EXCEPTION_ERROR;
4206 END IF;
4207
4208 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
4209
4210 l_request_numbers_token := TO_CHAR(l_request_id);
4211 IF ( NVL(l_rep_request_id,OKL_API.G_MISS_NUM) <> OKL_API.G_MISS_NUM ) THEN
4212 l_request_numbers_token := l_request_numbers_token || ', '|| TO_CHAR(l_rep_request_id);
4213 END IF;
4214
4215 x_request_numbers := l_request_numbers_token;
4216
4217 END IF;
4218 END IF;
4219 END IF;
4220
4221 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4222 x_msg_data => x_msg_data);
4223
4224 EXCEPTION
4225 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4226 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4227 p_api_name => l_api_name,
4228 p_pkg_name => g_pkg_name,
4229 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4230 x_msg_count => x_msg_count,
4231 x_msg_data => x_msg_data,
4232 p_api_type => g_api_type);
4233
4234 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4235 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4236 p_api_name => l_api_name,
4237 p_pkg_name => g_pkg_name,
4238 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4239 x_msg_count => x_msg_count,
4240 x_msg_data => x_msg_data,
4241 p_api_type => g_api_type);
4242
4243 WHEN OTHERS THEN
4244 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4245 p_api_name => l_api_name,
4246 p_pkg_name => g_pkg_name,
4247 p_exc_name => 'OTHERS',
4248 x_msg_count => x_msg_count,
4249 x_msg_data => x_msg_data,
4250 p_api_type => g_api_type);
4251
4252 END submit_prb_upgrade;
4253
4254 --Bug# 8798934
4255
4256 END OKL_BOOK_CONTROLLER_PVT;