[Home] [Help]
PACKAGE BODY: APPS.OKL_BOOK_CONTROLLER_PVT
Source
1 PACKAGE BODY OKL_BOOK_CONTROLLER_PVT AS
2 /* $Header: OKLRBCTB.pls 120.8 2007/06/13 22:57:27 asahoo noship $ */
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 ELSE
3065 l_qa_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3066 l_ut_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3067 l_st_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3068 l_bk_progress_status := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING;
3069 END IF;
3070
3071 i := 0;
3072 --populate record for QA Validation
3073 i := i+1;
3074 l_bct_tbl(i) := populate_ctrl_trx_rec(
3075 p_batch_num => l_batch_num,
3076 p_srl_num => 10,
3077 p_khr_id => p_khr_id,
3078 p_prog_name => 'OKLBCTQA',
3079 p_prog_short_name => 'OKLBCTQA',
3080 p_progress_status => l_qa_progress_status,
3081 p_active_flag => 'Y');
3082
3083 --populate record for Upfront Tax
3084 i := i+1;
3085 l_bct_tbl(i) := populate_ctrl_trx_rec(
3086 p_batch_num => l_batch_num,
3087 p_srl_num => 20,
3088 p_khr_id => p_khr_id,
3089 p_prog_name => 'OKLBCTUT',
3090 p_prog_short_name => 'OKLBCTUT',
3091 p_progress_status => l_ut_progress_status,
3092 p_active_flag => 'Y');
3093
3094 --populate record for Price Contract
3095 i := i+1;
3096 l_bct_tbl(i) := populate_ctrl_trx_rec(
3097 p_batch_num => l_batch_num,
3098 p_srl_num => 30,
3099 p_khr_id => p_khr_id,
3100 p_prog_name => 'OKLBCTST',
3101 p_prog_short_name => 'OKLBCTST',
3102 p_progress_status => l_st_progress_status,
3103 p_active_flag => 'Y');
3104
3105 --populate record for Activation
3106 i := i+1;
3107 l_bct_tbl(i) := populate_ctrl_trx_rec(
3108 p_batch_num => l_batch_num,
3109 p_srl_num => 40,
3110 p_khr_id => p_khr_id,
3111 p_prog_name => 'OKLBCTBK',
3112 p_prog_short_name => 'OKLBCTBK',
3113 p_progress_status => l_bk_progress_status,
3114 p_active_flag => 'Y');
3115
3116 IF l_bct_tbl.COUNT > 0 THEN
3117 --insert records into controller tranasction table for the batch
3118 okl_bct_pvt.insert_row(
3119 p_api_version => p_api_version,
3120 p_init_msg_list => p_init_msg_list,
3121 x_return_status => x_return_status,
3122 x_msg_count => x_msg_count,
3123 x_msg_data => x_msg_data,
3124 p_bct_tbl => l_bct_tbl,
3125 x_bct_tbl => lx_bct_tbl);
3126
3127 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3128 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3129 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3130 RAISE OKL_API.G_EXCEPTION_ERROR;
3131 END IF;
3132
3133 x_batch_number := l_batch_num;
3134
3135 END IF;
3136
3137 END IF;
3138
3139
3140 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3141 x_msg_data => x_msg_data);
3142
3143 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'End(-)');
3145 END IF;
3146
3147 EXCEPTION
3148 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3149 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3150 p_api_name => l_api_name,
3151 p_pkg_name => g_pkg_name,
3152 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3153 x_msg_count => x_msg_count,
3154 x_msg_data => x_msg_data,
3155 p_api_type => g_api_type);
3156
3157 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3158 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - ERROR');
3159 END IF;
3160
3161 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3162 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3163 p_api_name => l_api_name,
3164 p_pkg_name => g_pkg_name,
3165 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3166 x_msg_count => x_msg_count,
3167 x_msg_data => x_msg_data,
3168 p_api_type => g_api_type);
3169
3170 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3171 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - UNEXCP ERROR');
3172 END IF;
3173
3174 WHEN OTHERS THEN
3175 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3176 p_api_name => l_api_name,
3177 p_pkg_name => g_pkg_name,
3178 p_exc_name => 'OTHERS',
3179 x_msg_count => x_msg_count,
3180 x_msg_data => x_msg_data,
3181 p_api_type => g_api_type);
3182
3183 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3184 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.init_book_controller_trx.', 'EXP - OTHERS');
3185 END IF;
3186
3187 END init_book_controller_trx;
3188
3189 -----------------------------------------------------------------------------
3190 -- PROCEDURE update_book_controller_trx
3191 -----------------------------------------------------------------------------
3192 -- Start of comments
3193 --
3194 -- Procedure Name : update_book_controller_trx
3195 -- Description : Procedure to update status of records in OKL_BOOK_CONTROLLER_TRX table
3196 -- Called from OKL_CONTRACT_BOOK_PVT.execute_qa_check_list
3197 -- Business Rules :
3198 -- Parameters : p_khr_id p_prog_short_name p_progress_status
3199 -- Version : 1.0
3200 -- History : XX-XXX-XXXX asahoo Created
3201 -- End of comments
3202
3203 PROCEDURE update_book_controller_trx(
3204 p_api_version IN NUMBER,
3205 p_init_msg_list IN VARCHAR2,
3206 x_return_status OUT NOCOPY VARCHAR2,
3207 x_msg_count OUT NOCOPY NUMBER,
3208 x_msg_data OUT NOCOPY VARCHAR2,
3209 p_khr_id IN okc_k_headers_b.id%TYPE,
3210 p_prog_short_name IN okl_book_controller_trx.prog_short_name%TYPE,
3211 p_conc_req_id IN okl_book_controller_trx.conc_req_id%TYPE DEFAULT OKL_API.G_MISS_NUM,
3212 p_progress_status IN okl_book_controller_trx.progress_status%TYPE) IS
3213
3214 l_api_name CONSTANT VARCHAR2(30) := 'update_book_controller_trx';
3215 l_api_version CONSTANT NUMBER := 1.0;
3216 l_bct_rec bct_rec_type;
3217 x_bct_rec bct_rec_type;
3218
3219 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3220 SELECT *
3221 FROM okl_book_controller_trx
3222 WHERE khr_id = p_khr_id
3223 AND prog_short_name = nvl(p_prog_short_name, prog_short_name)
3224 AND NVL(active_flag,'N') = 'Y';
3225
3226 l_book_ctrl_trx c_book_ctrl_trx%ROWTYPE;
3227
3228 BEGIN
3229 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3230 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'Begin(+)');
3231 END IF;
3232 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3233
3234 x_return_status := OKL_API.START_ACTIVITY(
3235 p_api_name => l_api_name,
3236 p_pkg_name => g_pkg_name,
3237 p_init_msg_list => p_init_msg_list,
3238 l_api_version => l_api_version,
3239 p_api_version => p_api_version,
3240 p_api_type => g_api_type,
3241 x_return_status => x_return_status);
3242
3243 -- check if activity started successfully
3244 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3245 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3246 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3247 RAISE OKL_API.G_EXCEPTION_ERROR;
3248 END IF;
3249
3250 IF (p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM) THEN
3251 RAISE OKL_API.G_EXCEPTION_ERROR;
3252 ELSIF (p_progress_status IS NULL OR p_progress_status = OKL_API.G_MISS_CHAR) THEN
3253 RAISE OKL_API.G_EXCEPTION_ERROR;
3254 END IF;
3255
3256 OPEN c_book_ctrl_trx(p_khr_id,p_prog_short_name);
3257 LOOP
3258 FETCH c_book_ctrl_trx INTO l_book_ctrl_trx;
3259 EXIT WHEN c_book_ctrl_trx%NOTFOUND;
3260 l_bct_rec.user_id := l_book_ctrl_trx.user_id;
3261 l_bct_rec.org_id := l_book_ctrl_trx.org_id;
3262 l_bct_rec.batch_number := l_book_ctrl_trx.batch_number;
3263 l_bct_rec.processing_srl_number := l_book_ctrl_trx.processing_srl_number;
3264 l_bct_rec.khr_id := l_book_ctrl_trx.khr_id;
3265 l_bct_rec.program_name := l_book_ctrl_trx.program_name;
3266 l_bct_rec.prog_short_name := l_book_ctrl_trx.prog_short_name;
3267 IF (p_conc_req_id = OKL_API.G_MISS_NUM) THEN
3268 l_bct_rec.conc_req_id := l_book_ctrl_trx.conc_req_id;
3269 ELSE
3270 l_bct_rec.conc_req_id := p_conc_req_id;
3271 END IF;
3272 l_bct_rec.progress_status := p_progress_status;
3273 l_bct_rec.active_flag := l_book_ctrl_trx.active_flag;
3274
3275 --call TAPI to update okl_book_controller_trx
3276 okl_bct_pvt.update_row(
3277 p_api_version => p_api_version,
3278 p_init_msg_list => p_init_msg_list,
3279 x_return_status => x_return_status,
3280 x_msg_count => x_msg_count,
3281 x_msg_data => x_msg_data,
3282 p_bct_rec => l_bct_rec,
3283 x_bct_rec => x_bct_rec);
3284
3285 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3286 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3287 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3288 RAISE OKL_API.G_EXCEPTION_ERROR;
3289 END IF;
3290 END LOOP;
3291 CLOSE c_book_ctrl_trx;
3292
3293 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3294 x_msg_data => x_msg_data);
3295
3296 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3297 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'End(-)');
3298 END IF;
3299
3300 EXCEPTION
3301 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3302 IF (c_book_ctrl_trx%ISOPEN) THEN
3303 CLOSE c_book_ctrl_trx;
3304 END IF;
3305 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3306 p_api_name => l_api_name,
3307 p_pkg_name => g_pkg_name,
3308 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3309 x_msg_count => x_msg_count,
3310 x_msg_data => x_msg_data,
3311 p_api_type => g_api_type);
3312
3313 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - ERROR');
3315 END IF;
3316
3317 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3318 IF (c_book_ctrl_trx%ISOPEN) THEN
3319 CLOSE c_book_ctrl_trx;
3320 END IF;
3321 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3322 p_api_name => l_api_name,
3323 p_pkg_name => g_pkg_name,
3324 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3325 x_msg_count => x_msg_count,
3326 x_msg_data => x_msg_data,
3327 p_api_type => g_api_type);
3328
3329 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3330 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - UNEXCP ERROR');
3331 END IF;
3332
3333 WHEN OTHERS THEN
3334 IF (c_book_ctrl_trx%ISOPEN) THEN
3335 CLOSE c_book_ctrl_trx;
3336 END IF;
3337 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3338 p_api_name => l_api_name,
3339 p_pkg_name => g_pkg_name,
3340 p_exc_name => 'OTHERS',
3341 x_msg_count => x_msg_count,
3342 x_msg_data => x_msg_data,
3343 p_api_type => g_api_type);
3344
3345 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3346 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx.', 'EXP - OTHERS');
3347 END IF;
3348 END update_book_controller_trx;
3349
3350 -----------------------------------------------------------------------------
3351 -- PROCEDURE cancel_contract_activation
3352 -----------------------------------------------------------------------------
3353 -- Start of comments
3354 --
3355 -- Procedure Name : cancel_contract_activation
3356 -- Description : Procedure to update status of contract header, line and records in okl_book_controller_trx table
3357 -- Called from Authoring UI
3358 -- Business Rules :
3359 -- Parameters : p_khr_id
3360 -- Version : 1.0
3361 -- History : XX-XXX-XXXX asahoo Created
3362 -- End of comments
3363
3364 PROCEDURE cancel_contract_activation(
3365 p_api_version IN NUMBER,
3366 p_init_msg_list IN VARCHAR2,
3367 x_return_status OUT NOCOPY VARCHAR2,
3368 x_msg_count OUT NOCOPY NUMBER,
3369 x_msg_data OUT NOCOPY VARCHAR2,
3370 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3371 l_api_name CONSTANT VARCHAR2(30) := 'cancel_contract_activation';
3372 l_api_version CONSTANT NUMBER := 1.0;
3373 l_prog_short_name OKL_BOOK_CONTROLLER_TRX.prog_short_name%TYPE;
3374 BEGIN
3375 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3376 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'Begin(+)');
3377 END IF;
3378
3379 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3380
3381 x_return_status := OKL_API.START_ACTIVITY(
3382 p_api_name => l_api_name,
3383 p_pkg_name => g_pkg_name,
3384 p_init_msg_list => p_init_msg_list,
3385 l_api_version => l_api_version,
3386 p_api_version => p_api_version,
3387 p_api_type => g_api_type,
3388 x_return_status => x_return_status);
3389
3390 -- check if activity started successfully
3391 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3392 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3393 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3394 RAISE OKL_API.G_EXCEPTION_ERROR;
3395 END IF;
3396
3397 okl_contract_status_pub.update_contract_status(
3398 p_api_version => p_api_version,
3399 p_init_msg_list => p_init_msg_list,
3400 x_return_status => x_return_status,
3401 x_msg_count => x_msg_count,
3402 x_msg_data => x_msg_data,
3403 p_khr_status => 'INCOMPLETE',
3404 p_chr_id => p_khr_id);
3405 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3406 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3407 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3408 RAISE OKL_API.G_EXCEPTION_ERROR;
3409 END IF;
3410
3411 okl_contract_status_pub.cascade_lease_status
3412 (p_api_version => p_api_version,
3413 p_init_msg_list => p_init_msg_list,
3414 x_return_status => x_return_status,
3415 x_msg_count => x_msg_count,
3416 x_msg_data => x_msg_data,
3417 p_chr_id => p_khr_id);
3418
3419 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3420 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3421 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3422 RAISE OKL_API.G_EXCEPTION_ERROR;
3423 END IF;
3424
3425 FOR i in 1..4
3426 LOOP
3427 IF (i = 1) THEN
3428 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_VALIDATE_CONTRACT;
3429 ELSIF (i = 2) THEN
3430 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_CALC_UPFRONT_TAX;
3431 ELSIF (i = 3) THEN
3432 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT;
3433 ELSIF (i = 4) THEN
3434 l_prog_short_name := OKL_BOOK_CONTROLLER_PVT.G_SUBMIT_CONTRACT;
3435 END IF;
3436
3437 okl_book_controller_pvt.update_book_controller_trx(
3438 p_api_version => p_api_version,
3439 p_init_msg_list => p_init_msg_list,
3440 x_return_status => x_return_status,
3441 x_msg_count => x_msg_count,
3442 x_msg_data => x_msg_data,
3443 p_khr_id => p_khr_id,
3444 p_prog_short_name => l_prog_short_name,
3445 p_conc_req_id => NULL,
3446 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
3447
3448 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3449 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3450 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3451 RAISE OKL_API.G_EXCEPTION_ERROR;
3452 END IF;
3453
3454 END LOOP;
3455
3456
3457 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3458 x_msg_data => x_msg_data);
3459
3460 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3461 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'End(-)');
3462 END IF;
3463
3464 EXCEPTION
3465 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3466 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3467 p_api_name => l_api_name,
3468 p_pkg_name => g_pkg_name,
3469 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3470 x_msg_count => x_msg_count,
3471 x_msg_data => x_msg_data,
3472 p_api_type => g_api_type);
3473
3474 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3475 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - ERROR');
3476 END IF;
3477
3478 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3479 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3480 p_api_name => l_api_name,
3481 p_pkg_name => g_pkg_name,
3482 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3483 x_msg_count => x_msg_count,
3484 x_msg_data => x_msg_data,
3485 p_api_type => g_api_type);
3486
3487 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3488 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - UNEXCP ERROR');
3489 END IF;
3490
3491 WHEN OTHERS THEN
3492 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3493 p_api_name => l_api_name,
3494 p_pkg_name => g_pkg_name,
3495 p_exc_name => 'OTHERS',
3496 x_msg_count => x_msg_count,
3497 x_msg_data => x_msg_data,
3498 p_api_type => g_api_type);
3499
3500 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3501 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.cancel_contract_activation.', 'EXP - OTHERS');
3502 END IF;
3503 END cancel_contract_activation;
3504
3505 -----------------------------------------------------------------------------
3506 -- PROCEDURE validate_contract_nxtbtn
3507 -----------------------------------------------------------------------------
3508 -- Start of comments
3509 --
3510 -- Procedure Name : validate_contract_nxtbtn
3511 -- Description : Procedure to do validation when next button in validate
3512 -- contrtact page is clicked.
3513 -- Business Rules :
3514 -- Parameters : p_khr_id
3515 -- Version : 1.0
3516 -- History : XX-XXX-XXXX asahoo Created
3517 -- End of comments
3518
3519 PROCEDURE validate_contract_nxtbtn(
3520 p_api_version IN NUMBER,
3521 p_init_msg_list IN VARCHAR2,
3522 x_return_status OUT NOCOPY VARCHAR2,
3523 x_msg_count OUT NOCOPY NUMBER,
3524 x_msg_data OUT NOCOPY VARCHAR2,
3525 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3526 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract_nxtbtn';
3527 l_api_version CONSTANT NUMBER := 1.0;
3528
3529 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3530 SELECT progress_status
3531 FROM okl_book_controller_trx
3532 WHERE khr_id = p_khr_id
3533 AND prog_short_name = p_prog_short_name
3534 AND NVL(active_flag,'N') = 'Y';
3535
3536 l_progress_status okl_book_controller_trx.progress_status%TYPE;
3537 x_process_status VARCHAR2(30);
3538 BEGIN
3539 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'Begin(+)');
3541 END IF;
3542
3543 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3544
3545 x_return_status := OKL_API.START_ACTIVITY(
3546 p_api_name => l_api_name,
3547 p_pkg_name => g_pkg_name,
3548 p_init_msg_list => p_init_msg_list,
3549 l_api_version => l_api_version,
3550 p_api_version => p_api_version,
3551 p_api_type => g_api_type,
3552 x_return_status => x_return_status);
3553
3554 -- check if activity started successfully
3555 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3556 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3557 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3558 RAISE OKL_API.G_EXCEPTION_ERROR;
3559 END IF;
3560
3561 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_validate_contract);
3562 FETCH c_book_ctrl_trx INTO l_progress_status;
3563 CLOSE c_book_ctrl_trx;
3564
3565 IF (l_progress_status = okl_book_controller_pvt.g_prog_sts_complete) THEN
3566 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_calc_upfront_tax);
3567 FETCH c_book_ctrl_trx INTO l_progress_status;
3568 CLOSE c_book_ctrl_trx;
3569 IF (l_progress_status = okl_book_controller_pvt.g_prog_sts_pending) THEN
3570 OKL_CONTRACT_BOOK_PVT.calculate_upfront_tax(
3571 p_api_version => p_api_version,
3572 p_init_msg_list => p_init_msg_list,
3573 x_return_status => x_return_status,
3574 x_msg_count => x_msg_count,
3575 x_msg_data => x_msg_data,
3576 p_chr_id => p_khr_id,
3577 x_process_status => x_process_status);
3578 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3579 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3580 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3581 RAISE OKL_API.G_EXCEPTION_ERROR;
3582 END IF;
3583 END IF;
3584
3585 ELSE
3586 -- The Contract must pass validation before upfront tax can be calculated.
3587 OKL_API.set_message(p_app_name => G_APP_NAME,
3588 p_msg_name => 'OKL_LLA_NOT_VALIDATED');
3589 RAISE OKL_API.G_EXCEPTION_ERROR;
3590 END IF;
3591
3592 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3593 x_msg_data => x_msg_data);
3594
3595 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3596 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'End(-)');
3597 END IF;
3598
3599 EXCEPTION
3600 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3601 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3602 p_api_name => l_api_name,
3603 p_pkg_name => g_pkg_name,
3604 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3605 x_msg_count => x_msg_count,
3606 x_msg_data => x_msg_data,
3607 p_api_type => g_api_type);
3608
3609 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3610 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - ERROR');
3611 END IF;
3612
3613 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3614 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3615 p_api_name => l_api_name,
3616 p_pkg_name => g_pkg_name,
3617 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3618 x_msg_count => x_msg_count,
3619 x_msg_data => x_msg_data,
3620 p_api_type => g_api_type);
3621
3622 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - UNEXCP ERROR');
3624 END IF;
3625
3626 WHEN OTHERS THEN
3627 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3628 p_api_name => l_api_name,
3629 p_pkg_name => g_pkg_name,
3630 p_exc_name => 'OTHERS',
3631 x_msg_count => x_msg_count,
3632 x_msg_data => x_msg_data,
3633 p_api_type => g_api_type);
3634
3635 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3636 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - OTHERS');
3637 END IF;
3638 END validate_contract_nxtbtn;
3639
3640 -----------------------------------------------------------------------------
3641 -- PROCEDURE calc_upfronttax_nxtbtn
3642 -----------------------------------------------------------------------------
3643 -- Start of comments
3644 --
3645 -- Procedure Name : calc_upfronttax_nxtbtn
3646 -- Description : Procedure to do validation when next button in Upfront
3647 -- tax page is clicked.
3648 -- Business Rules :
3649 -- Parameters : p_khr_id
3650 -- Version : 1.0
3651 -- History : XX-XXX-XXXX asahoo Created
3652 -- End of comments
3653
3654 PROCEDURE calc_upfronttax_nxtbtn(
3655 p_api_version IN NUMBER,
3656 p_init_msg_list IN VARCHAR2,
3657 x_return_status OUT NOCOPY VARCHAR2,
3658 x_msg_count OUT NOCOPY NUMBER,
3659 x_msg_data OUT NOCOPY VARCHAR2,
3660 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3661 l_api_name CONSTANT VARCHAR2(30) := 'calc_upfronttax_nxtbtn';
3662 l_api_version CONSTANT NUMBER := 1.0;
3663
3664 CURSOR c_book_ctrl_trx(p_khr_id NUMBER,p_prog_short_name VARCHAR2) IS
3665 SELECT progress_status
3666 FROM okl_book_controller_trx
3667 WHERE khr_id = p_khr_id
3668 AND prog_short_name = p_prog_short_name
3669 AND NVL(active_flag,'N') = 'Y';
3670
3671 l_progress_status okl_book_controller_trx.progress_status%TYPE;
3672 x_process_status VARCHAR2(30);
3673 BEGIN
3674 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3675 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.calc_upfronttax_nxtbtn.', 'Begin(+)');
3676 END IF;
3677
3678 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3679
3680 x_return_status := OKL_API.START_ACTIVITY(
3681 p_api_name => l_api_name,
3682 p_pkg_name => g_pkg_name,
3683 p_init_msg_list => p_init_msg_list,
3684 l_api_version => l_api_version,
3685 p_api_version => p_api_version,
3686 p_api_type => g_api_type,
3687 x_return_status => x_return_status);
3688
3689 -- check if activity started successfully
3690 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3691 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3692 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3693 RAISE OKL_API.G_EXCEPTION_ERROR;
3694 END IF;
3695
3696 OPEN c_book_ctrl_trx(p_khr_id,okl_book_controller_pvt.g_calc_upfront_tax);
3697 FETCH c_book_ctrl_trx INTO l_progress_status;
3698 CLOSE c_book_ctrl_trx;
3699
3700 IF (l_progress_status <> okl_book_controller_pvt.g_prog_sts_complete) THEN
3701 OKL_LA_SALES_TAX_PVT.validate_upfront_tax_fee(
3702 p_api_version => p_api_version,
3703 p_init_msg_list => p_init_msg_list,
3704 x_return_status => x_return_status,
3705 x_msg_count => x_msg_count,
3706 x_msg_data => x_msg_data,
3707 p_chr_id => p_khr_id);
3708 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3710 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3711 RAISE OKL_API.G_EXCEPTION_ERROR;
3712 END IF;
3713 END IF;
3714
3715 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3716 x_msg_data => x_msg_data);
3717
3718 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_BOOK_CONTROLLER_PVT.calc_upfronttax_nxtbtn.', 'End(-)');
3720 END IF;
3721
3722 EXCEPTION
3723 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3724 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3725 p_api_name => l_api_name,
3726 p_pkg_name => g_pkg_name,
3727 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3728 x_msg_count => x_msg_count,
3729 x_msg_data => x_msg_data,
3730 p_api_type => g_api_type);
3731
3732 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - ERROR');
3734 END IF;
3735
3736 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3737 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3738 p_api_name => l_api_name,
3739 p_pkg_name => g_pkg_name,
3740 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3741 x_msg_count => x_msg_count,
3742 x_msg_data => x_msg_data,
3743 p_api_type => g_api_type);
3744
3745 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - UNEXCP ERROR');
3747 END IF;
3748
3749 WHEN OTHERS THEN
3750 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3751 p_api_name => l_api_name,
3752 p_pkg_name => g_pkg_name,
3753 p_exc_name => 'OTHERS',
3754 x_msg_count => x_msg_count,
3755 x_msg_data => x_msg_data,
3756 p_api_type => g_api_type);
3757
3758 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3759 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_BOOK_CONTROLLER_PVT.validate_contract_nxtbtn.', 'EXP - OTHERS');
3760 END IF;
3761 END calc_upfronttax_nxtbtn;
3762
3763 -----------------------------------------------------------------------------
3764 -- PROCEDURE calculate_upfront_tax
3765 -----------------------------------------------------------------------------
3766 -- Start of comments
3767 --
3768 -- procedure Name : calculate_upfront_tax
3769 -- Description : Procedure called from exec_controller_prg1
3770 -- Business Rules :
3771 -- Parameters : p_khr_id
3772 -- Version : 1.0
3773 -- History : XX-XXX-XXXX asahoo Created
3774 -- End of comments
3775
3776 PROCEDURE calculate_upfront_tax(
3777 p_errbuf OUT NOCOPY VARCHAR2,
3778 p_retcode OUT NOCOPY NUMBER,
3779 p_khr_id IN okc_k_headers_b.id%TYPE) IS
3780
3781 l_api_name CONSTANT VARCHAR2(30) := 'calculate_upfront_tax';
3782 l_api_version CONSTANT NUMBER := 1.0;
3783 p_api_version CONSTANT NUMBER := 1.0;
3784 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3785 l_init_msg_list VARCHAR2(1) := 'T';
3786 l_msg_count NUMBER;
3787 l_msg_data VARCHAR2(2000);
3788 l_msg_token_tbl msg_token_tbl;
3789 l_data VARCHAR2(2000);
3790 l_msg_index_out NUMBER;
3791 l_process_status VARCHAR2(1);
3792
3793
3794 BEGIN
3795 p_retcode := 0;
3796 --print OKL copyright in the output file
3797 l_msg_token_tbl(1).token_name := 'API_NAME';
3798 l_msg_token_tbl(1).token_value := 'OKLRBCTB.pls';
3799 l_msg_token_tbl(2).token_name := 'CONC_PROGRAM';
3800 l_msg_token_tbl(2).token_value := 'Calculate Upfront Tax';
3801 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3802 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_COPYRIGHT_HEADER',l_msg_token_tbl));
3803 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3804 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'));
3805 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,get_message('OKL_CONC_REQ_ID','REQUEST_ID',fnd_global.conc_request_id));
3806 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3807
3808 l_return_status := OKL_API.START_ACTIVITY(l_api_name
3809 ,G_PKG_NAME
3810 ,l_init_msg_list
3811 ,l_api_version
3812 ,p_api_version
3813 ,'_PVT'
3814 ,l_return_status);
3815
3816 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3817 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in Start Activity');
3818 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3819 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
3820 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in Start Activity');
3821 RAISE OKL_API.G_EXCEPTION_ERROR;
3822 END IF;
3823 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Activity Successful');
3824 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
3825
3826 --call Upfront Tax for Booking
3827 okl_contract_book_pvt.calculate_upfront_tax(
3828 p_api_version => l_api_version,
3829 p_init_msg_list => l_init_msg_list,
3830 x_return_status => l_return_status,
3831 x_msg_count => l_msg_count,
3832 x_msg_data => l_msg_data,
3833 p_chr_id => p_khr_id,
3834 x_process_status => l_process_status);
3835
3836 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error in call to okl_contract_book_pvt.calculate_upfront_tax');
3838 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3839 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3840 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in call to okl_contract_book_pvt.calculate_upfront_tax');
3841 RAISE OKL_API.G_EXCEPTION_ERROR;
3842 END IF;
3843
3844 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3845 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3846 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3847 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3848
3849 IF (l_process_status = OKL_API.G_RET_STS_ERROR OR l_process_status = OKL_API.G_RET_STS_WARNING) THEN
3850 -- print the error message in the output file
3851 IF (fnd_msg_pub.count_msg > 0) THEN
3852 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3853 LOOP
3854 fnd_msg_pub.get(
3855 p_msg_index => l_counter,
3856 p_encoded => 'F',
3857 p_data => l_data,
3858 p_msg_index_out => l_msg_index_out);
3859 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3860 END LOOP;
3861 END IF;
3862 END IF;
3863
3864 IF (l_process_status <> OKL_API.G_RET_STS_ERROR) THEN
3865 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Upfront Tax was calculated successfully....');
3866 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
3867 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_REQ_SUCCESS'));
3868 END IF;
3869
3870 IF (l_process_status = OKL_API.G_RET_STS_ERROR) THEN
3871 p_retcode := 2;
3872 END IF;
3873
3874 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'));
3875 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3876
3877 OKL_API.END_ACTIVITY(l_msg_count, l_msg_data);
3878 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End Activity Successful');
3879
3880 EXCEPTION
3881 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3882 p_retcode := 2;
3883
3884 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3885 p_api_name => l_api_name,
3886 p_pkg_name => g_pkg_name,
3887 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3888 x_msg_count => l_msg_count,
3889 x_msg_data => l_msg_data,
3890 p_api_type => g_api_type);
3891
3892 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3893 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3894 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3895
3896 -- print the error message in the output file
3897 IF (fnd_msg_pub.count_msg > 0) THEN
3898 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3899 LOOP
3900 fnd_msg_pub.get(
3901 p_msg_index => l_counter,
3902 p_encoded => 'F',
3903 p_data => l_data,
3904 p_msg_index_out => l_msg_index_out);
3905 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3906 END LOOP;
3907 END IF;
3908
3909 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3910 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'));
3911 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3912
3913 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3914 p_retcode := 2;
3915
3916 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3917 p_api_name => l_api_name,
3918 p_pkg_name => g_pkg_name,
3919 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3920 x_msg_count => l_msg_count,
3921 x_msg_data => l_msg_data,
3922 p_api_type => g_api_type);
3923
3924 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3925 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3926 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3927
3928 -- print the error message in the output file
3929 IF (fnd_msg_pub.count_msg > 0) THEN
3930 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3931 LOOP
3932 fnd_msg_pub.get(
3933 p_msg_index => l_counter,
3934 p_encoded => 'F',
3935 p_data => l_data,
3936 p_msg_index_out => l_msg_index_out);
3937 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3938 END LOOP;
3939 END IF;
3940
3941 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3942 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'));
3943 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3944
3945 WHEN OTHERS THEN
3946 p_retcode := 2;
3947 p_errbuf := SQLERRM;
3948
3949 l_return_status := OKL_API.HANDLE_EXCEPTIONS(
3950 p_api_name => l_api_name,
3951 p_pkg_name => g_pkg_name,
3952 p_exc_name => 'OTHERS',
3953 x_msg_count => l_msg_count,
3954 x_msg_data => l_msg_data,
3955 p_api_type => g_api_type);
3956
3957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3958 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get_string('OKL','OKL_LLA_BOOK_STATUS'));
3959 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------');
3960
3961 -- print the error message in the output file
3962 IF (fnd_msg_pub.count_msg > 0) THEN
3963 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
3964 LOOP
3965 fnd_msg_pub.get(
3966 p_msg_index => l_counter,
3967 p_encoded => 'F',
3968 p_data => l_data,
3969 p_msg_index_out => l_msg_index_out);
3970 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_data);
3971 END LOOP;
3972 END IF;
3973 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3974 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'));
3975 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'+--------------------------------------------------------+');
3976 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
3977
3978 END calculate_upfront_tax;
3979
3980 END OKL_BOOK_CONTROLLER_PVT;