[Home] [Help]
PACKAGE BODY: APPS.OKL_REPORT_PVT
Source
1 PACKAGE BODY Okl_Report_Pvt AS
2 /* $Header: OKLCREPB.pls 120.15 2010/09/30 07:03:33 sosharma ship $ */
3
4 PROCEDURE ADD_LANGUAGE IS
5 BEGIN
6 Okl_Rep_Pvt.add_language;
7 END ;
8
9 PROCEDURE create_report(
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 p_repv_rec IN repv_rec_type,
16 x_repv_rec OUT NOCOPY repv_rec_type
17 ) IS
18
19 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
20 l_api_name VARCHAR2(30) := 'CREATE_REPORT';
21
22 BEGIN
23 -- Call start_activity to create savepoint, check compatibility
24 -- and initialize message list
25 l_return_status := OKL_API.START_ACTIVITY (
26 l_api_name,
27 p_init_msg_list,
28 '_PVT',
29 l_return_status);
30
31 -- Check if activity started successfully
32 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
33 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
34 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
35 RAISE OKL_API.G_EXCEPTION_ERROR;
36 END IF;
37
38 -- Tapi Call
39 Okl_Rep_Pvt.insert_row(
40 p_api_version,
41 p_init_msg_list,
42 x_return_status,
43 x_msg_count,
44 x_msg_data,
45 p_repv_rec,
46 x_repv_rec);
47
48 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
49 RAISE G_EXCEPTION_HALT_VALIDATION;
50 ELSE
51 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
52 l_return_status := x_return_status;
53 END IF;
54 END IF;
55
56 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
57
58 EXCEPTION
59 WHEN OKL_API.G_EXCEPTION_ERROR THEN
60 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
61 l_api_name,
62 G_PKG_NAME,
63 'OKL_API.G_RET_STS_ERROR',
64 x_msg_count,
65 x_msg_data,
66 '_PVT');
67
68 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
69 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
70 l_api_name,
71 G_PKG_NAME,
72 'OKL_API.G_RET_STS_UNEXP_ERROR',
73 x_msg_count,
74 x_msg_data,
75 '_PVT');
76
77 WHEN OTHERS THEN
78 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
79 l_api_name,
80 G_PKG_NAME,
81 'OTHERS',
82 x_msg_count,
83 x_msg_data,
84 '_PVT');
85
86 END create_report;
87
88 PROCEDURE update_report(
89 p_api_version IN NUMBER,
90 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_repv_rec IN repv_rec_type,
95 x_repv_rec OUT NOCOPY repv_rec_type
96 ) IS
97 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
98 l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
99
100 BEGIN
101 -- Call start_activity to create savepoint, check compatibility
102 -- and initialize message list
103 l_return_status := OKL_API.START_ACTIVITY (
104 l_api_name,
105 p_init_msg_list,
106 '_PVT',
107 l_return_status);
108
109 -- Check if activity started successfully
110 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
111 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
112 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
113 RAISE OKL_API.G_EXCEPTION_ERROR;
114 END IF;
115
116 -- Tapi Call
117 Okl_Rep_Pvt.update_row(
118 p_api_version,
119 p_init_msg_list,
120 x_return_status,
121 x_msg_count,
122 x_msg_data,
123 p_repv_rec,
124 x_repv_rec);
125
126 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
127 RAISE G_EXCEPTION_HALT_VALIDATION;
128 ELSE
129 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
130 l_return_status := x_return_status;
131 END IF;
132 END IF;
133
134 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
135
136 EXCEPTION
137 WHEN OKL_API.G_EXCEPTION_ERROR THEN
138 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
139 l_api_name,
140 G_PKG_NAME,
141 'OKL_API.G_RET_STS_ERROR',
142 x_msg_count,
143 x_msg_data,
144 '_PVT');
145
146 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
147 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
148 l_api_name,
149 G_PKG_NAME,
150 'OKL_API.G_RET_STS_UNEXP_ERROR',
151 x_msg_count,
152 x_msg_data,
153 '_PVT');
154
155 WHEN OTHERS THEN
156 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
157 l_api_name,
158 G_PKG_NAME,
159 'OTHERS',
160 x_msg_count,
161 x_msg_data,
162 '_PVT');
163
164 END update_report;
165
166 PROCEDURE delete_report(
167 p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2,
172 p_repv_rec IN repv_rec_type
173 ) IS
174 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
175 l_api_name VARCHAR2(30) := 'DELETE_REPORT';
176
177 BEGIN
178 -- Call start_activity to create savepoint, check compatibility
179 -- and initialize message list
180 l_return_status := OKL_API.START_ACTIVITY (
181 l_api_name,
182 p_init_msg_list,
183 '_PVT',
184 l_return_status);
185
186 -- Check if activity started successfully
187 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
188 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
189 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
190 RAISE OKL_API.G_EXCEPTION_ERROR;
191 END IF;
192
193 -- Tapi Call
194 Okl_Rep_Pvt.delete_row(
195 p_api_version,
196 p_init_msg_list,
197 x_return_status,
198 x_msg_count,
199 x_msg_data,
200 p_repv_rec);
201
202 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
203 RAISE G_EXCEPTION_HALT_VALIDATION;
204 ELSE
205 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
206 l_return_status := x_return_status;
207 END IF;
208 END IF;
209
210 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
211
212 EXCEPTION
213 WHEN OKL_API.G_EXCEPTION_ERROR THEN
214 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
215 l_api_name,
216 G_PKG_NAME,
217 'OKL_API.G_RET_STS_ERROR',
218 x_msg_count,
219 x_msg_data,
220 '_PVT');
221
222 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
223 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
224 l_api_name,
225 G_PKG_NAME,
226 'OKL_API.G_RET_STS_UNEXP_ERROR',
227 x_msg_count,
228 x_msg_data,
229 '_PVT');
230
231 WHEN OTHERS THEN
232 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
233 l_api_name,
234 G_PKG_NAME,
235 'OTHERS',
236 x_msg_count,
237 x_msg_data,
238 '_PVT');
239
240 END delete_report;
241
242 PROCEDURE submit_report(
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248 p_rep_id IN NUMBER
249 ) IS
250 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
251 l_api_name VARCHAR2(30) := 'SUBMIT_REPORT';
252 l_repv_rec repv_rec_type;
253 x_repv_rec repv_rec_type;
254
255 CURSOR c_get_details(p_rep_id NUMBER) IS
256 SELECT REPORT_ID
257 ,NAME
258 ,CHART_OF_ACCOUNTS_ID
259 ,BOOK_CLASSIFICATION_CODE
260 ,LEDGER_ID
261 ,REPORT_CATEGORY_CODE
262 ,REPORT_TYPE_CODE
263 ,ACTIVITY_CODE
264 ,STATUS_CODE
265 ,DESCRIPTION
266 ,EFFECTIVE_FROM_DATE
267 ,EFFECTIVE_TO_DATE
268 ,CREATED_BY
269 ,CREATION_DATE
270 ,LAST_UPDATED_BY
271 ,LAST_UPDATE_DATE
272 ,LAST_UPDATE_LOGIN
273 ,LANGUAGE
274 ,SOURCE_LANG
275 ,SFWT_FLAG
276 FROM OKL_REPORTS_V
277 WHERE REPORT_ID = p_rep_id;
278
279 BEGIN
280 -- Call start_activity to create savepoint, check compatibility
281 -- and initialize message list
282 l_return_status := OKL_API.START_ACTIVITY (
283 l_api_name,
284 p_init_msg_list,
285 '_PVT',
286 l_return_status);
287
288 -- Check if activity started successfully
289 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
290 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
292 RAISE OKL_API.G_EXCEPTION_ERROR;
293 END IF;
294
295 OPEN c_get_details(p_rep_id);
296 FETCH c_get_details
297 INTO l_repv_rec.report_id
298 ,l_repv_rec.name
299 ,l_repv_rec.chart_of_accounts_id
300 ,l_repv_rec.book_classification_code
301 ,l_repv_rec.ledger_id
302 ,l_repv_rec.report_category_code
303 ,l_repv_rec.report_type_code
304 ,l_repv_rec.activity_code
305 ,l_repv_rec.status_code
306 ,l_repv_rec.description
307 ,l_repv_rec.effective_from_date
308 ,l_repv_rec.effective_to_date
309 ,l_repv_rec.created_by
310 ,l_repv_rec.creation_date
311 ,l_repv_rec.last_updated_by
312 ,l_repv_rec.last_update_date
313 ,l_repv_rec.last_update_login
314 ,l_repv_rec.language
315 ,l_repv_rec.source_lang
316 ,l_repv_rec.sfwt_flag;
317 CLOSE c_get_details;
318
319 -- Tapi Call
320 Okl_Rep_Pvt.update_row(
321 p_api_version,
322 p_init_msg_list,
323 x_return_status,
324 x_msg_count,
325 x_msg_data,
326 l_repv_rec,
327 x_repv_rec);
328
329 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
330 RAISE G_EXCEPTION_HALT_VALIDATION;
331 ELSE
332 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
333 l_return_status := x_return_status;
334 END IF;
335 END IF;
336
337 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
338
339 EXCEPTION
340 WHEN OKL_API.G_EXCEPTION_ERROR THEN
341 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
342 l_api_name,
343 G_PKG_NAME,
344 'OKL_API.G_RET_STS_ERROR',
345 x_msg_count,
346 x_msg_data,
347 '_PVT');
348
349 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
350 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
351 l_api_name,
352 G_PKG_NAME,
353 'OKL_API.G_RET_STS_UNEXP_ERROR',
354 x_msg_count,
355 x_msg_data,
356 '_PVT');
357
358 WHEN OTHERS THEN
359 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
360 l_api_name,
361 G_PKG_NAME,
362 'OTHERS',
363 x_msg_count,
364 x_msg_data,
365 '_PVT');
366
367 END submit_report;
368
369 PROCEDURE activate_report(
370 p_api_version IN NUMBER,
371 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375 p_rep_id IN NUMBER
376 ) IS
377 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
378 l_api_name VARCHAR2(30) := 'ACTIVATE_REPORT';
379 l_repv_rec repv_rec_type;
380 x_repv_rec repv_rec_type;
381
382 CURSOR c_get_details(p_rep_id NUMBER) IS
383 SELECT REPORT_ID
384 ,NAME
385 ,CHART_OF_ACCOUNTS_ID
386 ,BOOK_CLASSIFICATION_CODE
387 ,LEDGER_ID
388 ,REPORT_CATEGORY_CODE
389 ,REPORT_TYPE_CODE
390 ,ACTIVITY_CODE
391 ,STATUS_CODE
392 ,DESCRIPTION
393 ,EFFECTIVE_FROM_DATE
394 ,EFFECTIVE_TO_DATE
395 ,CREATED_BY
396 ,CREATION_DATE
397 ,LAST_UPDATED_BY
398 ,LAST_UPDATE_DATE
399 ,LAST_UPDATE_LOGIN
400 ,LANGUAGE
401 ,SOURCE_LANG
402 ,SFWT_FLAG
403 FROM OKL_REPORTS_V
404 WHERE REPORT_ID = p_rep_id;
405
406 BEGIN
407 -- Call start_activity to create savepoint, check compatibility
408 -- and initialize message list
409 l_return_status := OKL_API.START_ACTIVITY (
410 l_api_name,
411 p_init_msg_list,
412 '_PVT',
413 l_return_status);
414
415 -- Check if activity started successfully
416 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
418 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421
422 OPEN c_get_details(p_rep_id);
423 FETCH c_get_details
424 INTO l_repv_rec.report_id
425 ,l_repv_rec.name
426 ,l_repv_rec.chart_of_accounts_id
427 ,l_repv_rec.book_classification_code
428 ,l_repv_rec.ledger_id
429 ,l_repv_rec.report_category_code
430 ,l_repv_rec.report_type_code
431 ,l_repv_rec.activity_code
432 ,l_repv_rec.status_code
433 ,l_repv_rec.description
434 ,l_repv_rec.effective_from_date
435 ,l_repv_rec.effective_to_date
436 ,l_repv_rec.created_by
437 ,l_repv_rec.creation_date
438 ,l_repv_rec.last_updated_by
439 ,l_repv_rec.last_update_date
440 ,l_repv_rec.last_update_login
441 ,l_repv_rec.language
442 ,l_repv_rec.source_lang
443 ,l_repv_rec.sfwt_flag;
444 CLOSE c_get_details;
445
446 l_repv_rec.status_code := 'ACTIVE';
447
448 -- Tapi Call
449 Okl_Rep_Pvt.update_row(
450 p_api_version,
451 p_init_msg_list,
452 x_return_status,
453 x_msg_count,
454 x_msg_data,
455 l_repv_rec,
456 x_repv_rec);
457
458 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
459 RAISE G_EXCEPTION_HALT_VALIDATION;
460 ELSE
461 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
462 l_return_status := x_return_status;
463 END IF;
464 END IF;
465
466 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
467
468 EXCEPTION
469 WHEN OKL_API.G_EXCEPTION_ERROR THEN
470 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
471 l_api_name,
472 G_PKG_NAME,
473 'OKL_API.G_RET_STS_ERROR',
474 x_msg_count,
475 x_msg_data,
476 '_PVT');
477
478 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
479 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
480 l_api_name,
481 G_PKG_NAME,
482 'OKL_API.G_RET_STS_UNEXP_ERROR',
483 x_msg_count,
484 x_msg_data,
485 '_PVT');
486
487 WHEN OTHERS THEN
488 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
489 l_api_name,
490 G_PKG_NAME,
491 'OTHERS',
492 x_msg_count,
493 x_msg_data,
494 '_PVT');
495
496 END activate_report;
497
498 PROCEDURE lock_report(
499 p_api_version IN NUMBER,
500 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_repv_rec IN repv_rec_type
505 ) IS
506 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
507 l_api_name VARCHAR2(30) := 'LOCK_REPORT';
508
509 BEGIN
510 -- Call start_activity to create savepoint, check compatibility
511 -- and initialize message list
512 l_return_status := OKL_API.START_ACTIVITY (
513 l_api_name,
514 p_init_msg_list,
515 '_PVT',
516 l_return_status);
517
518 -- Check if activity started successfully
519 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
520 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
521 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
522 RAISE OKL_API.G_EXCEPTION_ERROR;
523 END IF;
524
525 -- Tapi Call
526 Okl_Rep_Pvt.lock_row(
527 p_api_version,
528 p_init_msg_list,
529 x_return_status,
530 x_msg_count,
531 x_msg_data,
532 p_repv_rec);
533
534 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
535 RAISE G_EXCEPTION_HALT_VALIDATION;
536 ELSE
537 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
538 l_return_status := x_return_status;
539 END IF;
540 END IF;
541
542 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
543
544 EXCEPTION
545 WHEN OKL_API.G_EXCEPTION_ERROR THEN
546 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
547 l_api_name,
548 G_PKG_NAME,
549 'OKL_API.G_RET_STS_ERROR',
550 x_msg_count,
551 x_msg_data,
552 '_PVT');
553
554 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
555 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
556 l_api_name,
557 G_PKG_NAME,
558 'OKL_API.G_RET_STS_UNEXP_ERROR',
559 x_msg_count,
560 x_msg_data,
561 '_PVT');
562
563 WHEN OTHERS THEN
564 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
565 l_api_name,
566 G_PKG_NAME,
567 'OTHERS',
568 x_msg_count,
569 x_msg_data,
570 '_PVT');
571
572 END lock_report;
573
574 PROCEDURE create_report(
575 p_api_version IN NUMBER,
576 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580 p_repv_tbl IN repv_tbl_type,
581 x_repv_tbl OUT NOCOPY repv_tbl_type
582 ) IS
583
584 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
585 l_api_name VARCHAR2(30) := 'CREATE_REPORT';
586
587 BEGIN
588 -- Call start_activity to create savepoint, check compatibility
589 -- and initialize message list
590 l_return_status := OKL_API.START_ACTIVITY (
591 l_api_name,
592 p_init_msg_list,
593 '_PVT',
594 l_return_status);
595
596 -- Check if activity started successfully
597 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
598 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
599 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
600 RAISE OKL_API.G_EXCEPTION_ERROR;
601 END IF;
602
603 -- Tapi Call
604 Okl_Rep_Pvt.insert_row(
605 p_api_version,
606 p_init_msg_list,
607 x_return_status,
608 x_msg_count,
609 x_msg_data,
610 p_repv_tbl,
611 x_repv_tbl);
612
613 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
614 RAISE G_EXCEPTION_HALT_VALIDATION;
615 ELSE
616 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
617 l_return_status := x_return_status;
618 END IF;
619 END IF;
620
621 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
622
623 EXCEPTION
624 WHEN OKL_API.G_EXCEPTION_ERROR THEN
625 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
626 l_api_name,
627 G_PKG_NAME,
628 'OKL_API.G_RET_STS_ERROR',
629 x_msg_count,
630 x_msg_data,
631 '_PVT');
632
633 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
634 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
635 l_api_name,
636 G_PKG_NAME,
637 'OKL_API.G_RET_STS_UNEXP_ERROR',
638 x_msg_count,
639 x_msg_data,
640 '_PVT');
641
642 WHEN OTHERS THEN
643 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
644 l_api_name,
645 G_PKG_NAME,
646 'OTHERS',
647 x_msg_count,
648 x_msg_data,
649 '_PVT');
650
651 END create_report;
652
653 PROCEDURE update_report(
654 p_api_version IN NUMBER,
655 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 p_repv_tbl IN repv_tbl_type,
660 x_repv_tbl OUT NOCOPY repv_tbl_type
661 ) IS
662
663 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
664 l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
665
666 BEGIN
667 -- Call start_activity to create savepoint, check compatibility
668 -- and initialize message list
669 l_return_status := OKL_API.START_ACTIVITY (
670 l_api_name,
671 p_init_msg_list,
672 '_PVT',
673 l_return_status);
674
675 -- Check if activity started successfully
676 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
678 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
679 RAISE OKL_API.G_EXCEPTION_ERROR;
680 END IF;
681
682 -- Tapi Call
683 Okl_Rep_Pvt.update_row(
684 p_api_version,
685 p_init_msg_list,
686 x_return_status,
687 x_msg_count,
688 x_msg_data,
689 p_repv_tbl,
690 x_repv_tbl);
691
692 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
693 RAISE G_EXCEPTION_HALT_VALIDATION;
694 ELSE
695 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
696 l_return_status := x_return_status;
697 END IF;
698 END IF;
699
700 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
701
702 EXCEPTION
703 WHEN OKL_API.G_EXCEPTION_ERROR THEN
704 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
705 l_api_name,
706 G_PKG_NAME,
707 'OKL_API.G_RET_STS_ERROR',
708 x_msg_count,
709 x_msg_data,
710 '_PVT');
711
712 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
713 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
714 l_api_name,
715 G_PKG_NAME,
716 'OKL_API.G_RET_STS_UNEXP_ERROR',
717 x_msg_count,
718 x_msg_data,
719 '_PVT');
720
721 WHEN OTHERS THEN
722 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
723 l_api_name,
724 G_PKG_NAME,
725 'OTHERS',
726 x_msg_count,
727 x_msg_data,
728 '_PVT');
729
730 END update_report;
731
732 PROCEDURE delete_report(
733 p_api_version IN NUMBER,
734 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
735 x_return_status OUT NOCOPY VARCHAR2,
736 x_msg_count OUT NOCOPY NUMBER,
737 x_msg_data OUT NOCOPY VARCHAR2,
738 p_repv_tbl IN repv_tbl_type
739 ) IS
740
741 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
742 l_api_name VARCHAR2(30) := 'DELETE_REPORT';
743
744 BEGIN
745 -- Call start_activity to create savepoint, check compatibility
746 -- and initialize message list
747 l_return_status := OKL_API.START_ACTIVITY (
748 l_api_name,
749 p_init_msg_list,
750 '_PVT',
751 l_return_status);
752
753 -- Check if activity started successfully
754 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
755 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
756 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
757 RAISE OKL_API.G_EXCEPTION_ERROR;
758 END IF;
759
760 -- Tapi Call
761 Okl_Rep_Pvt.delete_row(
762 p_api_version,
763 p_init_msg_list,
764 x_return_status,
765 x_msg_count,
766 x_msg_data,
767 p_repv_tbl);
768
769 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
770 RAISE G_EXCEPTION_HALT_VALIDATION;
771 ELSE
772 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
773 l_return_status := x_return_status;
774 END IF;
775 END IF;
776
777 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
778
779 EXCEPTION
780 WHEN OKL_API.G_EXCEPTION_ERROR THEN
781 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
782 l_api_name,
783 G_PKG_NAME,
784 'OKL_API.G_RET_STS_ERROR',
785 x_msg_count,
786 x_msg_data,
787 '_PVT');
788
789 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
790 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
791 l_api_name,
792 G_PKG_NAME,
793 'OKL_API.G_RET_STS_UNEXP_ERROR',
794 x_msg_count,
795 x_msg_data,
796 '_PVT');
797
798 WHEN OTHERS THEN
799 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
800 l_api_name,
801 G_PKG_NAME,
802 'OTHERS',
803 x_msg_count,
804 x_msg_data,
805 '_PVT');
806
807 END delete_report;
808
809 PROCEDURE lock_report(
810 p_api_version IN NUMBER,
811 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2,
815 p_repv_tbl IN repv_tbl_type
816 ) IS
817
818 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
819 l_api_name VARCHAR2(30) := 'LOCK_REPORT';
820
821 BEGIN
822 -- Call start_activity to create savepoint, check compatibility
823 -- and initialize message list
824 l_return_status := OKL_API.START_ACTIVITY (
825 l_api_name,
826 p_init_msg_list,
827 '_PVT',
828 l_return_status);
829
830 -- Check if activity started successfully
831 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
833 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
834 RAISE OKL_API.G_EXCEPTION_ERROR;
835 END IF;
836
837 -- Tapi Call
838 Okl_Rep_Pvt.lock_row(
839 p_api_version,
840 p_init_msg_list,
841 x_return_status,
842 x_msg_count,
843 x_msg_data,
844 p_repv_tbl);
845
846 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
847 RAISE G_EXCEPTION_HALT_VALIDATION;
848 ELSE
849 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
850 l_return_status := x_return_status;
851 END IF;
852 END IF;
853
854 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
855
856 EXCEPTION
857 WHEN OKL_API.G_EXCEPTION_ERROR THEN
858 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
859 l_api_name,
860 G_PKG_NAME,
861 'OKL_API.G_RET_STS_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PVT');
865
866 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
867 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
868 l_api_name,
869 G_PKG_NAME,
870 'OKL_API.G_RET_STS_UNEXP_ERROR',
871 x_msg_count,
872 x_msg_data,
873 '_PVT');
874
875 WHEN OTHERS THEN
876 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
877 l_api_name,
878 G_PKG_NAME,
879 'OTHERS',
880 x_msg_count,
881 x_msg_data,
882 '_PVT');
883
884 END lock_report;
885
886 PROCEDURE create_report_parameters(
887 p_api_version IN NUMBER,
888 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892 p_rpp_rec IN rpp_rec_type,
893 x_rpp_rec OUT NOCOPY rpp_rec_type
894 ) IS
895
896 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
897 l_api_name VARCHAR2(30) := 'CREATE_REPORT_PARAMETERS';
898
899 BEGIN
900 -- Call start_activity to create savepoint, check compatibility
901 -- and initialize message list
902 l_return_status := OKL_API.START_ACTIVITY (
903 l_api_name,
904 p_init_msg_list,
905 '_PVT',
906 l_return_status);
907
908 -- Check if activity started successfully
909 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
910 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
911 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
912 RAISE OKL_API.G_EXCEPTION_ERROR;
913 END IF;
914
915 -- Tapi Call
916 Okl_Rpp_Pvt.insert_row(
917 p_api_version,
918 p_init_msg_list,
919 x_return_status,
920 x_msg_count,
921 x_msg_data,
922 p_rpp_rec,
923 x_rpp_rec);
924
925 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
926 RAISE G_EXCEPTION_HALT_VALIDATION;
927 ELSE
928 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
929 l_return_status := x_return_status;
930 END IF;
931 END IF;
932
933 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
934
935 EXCEPTION
936 WHEN OKL_API.G_EXCEPTION_ERROR THEN
937 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
938 l_api_name,
939 G_PKG_NAME,
940 'OKL_API.G_RET_STS_ERROR',
941 x_msg_count,
942 x_msg_data,
943 '_PVT');
944
945 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
946 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
947 l_api_name,
948 G_PKG_NAME,
949 'OKL_API.G_RET_STS_UNEXP_ERROR',
950 x_msg_count,
951 x_msg_data,
952 '_PVT');
953
954 WHEN OTHERS THEN
955 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
956 l_api_name,
957 G_PKG_NAME,
958 'OTHERS',
959 x_msg_count,
960 x_msg_data,
961 '_PVT');
962
963 END create_report_parameters;
964
965 PROCEDURE update_report_parameters(
966 p_api_version IN NUMBER,
967 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
968 x_return_status OUT NOCOPY VARCHAR2,
969 x_msg_count OUT NOCOPY NUMBER,
970 x_msg_data OUT NOCOPY VARCHAR2,
971 p_rpp_rec IN rpp_rec_type,
972 x_rpp_rec OUT NOCOPY rpp_rec_type
973 ) IS
974
975 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
976 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_PARAMETERS';
977
978 BEGIN
979 -- Call start_activity to create savepoint, check compatibility
980 -- and initialize message list
981 l_return_status := OKL_API.START_ACTIVITY (
982 l_api_name,
983 p_init_msg_list,
984 '_PVT',
985 l_return_status);
986
987 -- Check if activity started successfully
988 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
989 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
991 RAISE OKL_API.G_EXCEPTION_ERROR;
992 END IF;
993
994 -- Tapi Call
995 Okl_Rpp_Pvt.update_row(
996 p_api_version,
997 p_init_msg_list,
998 x_return_status,
999 x_msg_count,
1000 x_msg_data,
1001 p_rpp_rec,
1002 x_rpp_rec);
1003
1004 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1005 RAISE G_EXCEPTION_HALT_VALIDATION;
1006 ELSE
1007 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1008 l_return_status := x_return_status;
1009 END IF;
1010 END IF;
1011
1012 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1013
1014 EXCEPTION
1015 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1016 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1017 l_api_name,
1018 G_PKG_NAME,
1019 'OKL_API.G_RET_STS_ERROR',
1020 x_msg_count,
1021 x_msg_data,
1022 '_PVT');
1023
1024 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1025 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1026 l_api_name,
1027 G_PKG_NAME,
1028 'OKL_API.G_RET_STS_UNEXP_ERROR',
1029 x_msg_count,
1030 x_msg_data,
1031 '_PVT');
1032
1033 WHEN OTHERS THEN
1034 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1035 l_api_name,
1036 G_PKG_NAME,
1037 'OTHERS',
1038 x_msg_count,
1039 x_msg_data,
1040 '_PVT');
1041
1042 END update_report_parameters;
1043
1044 PROCEDURE delete_report_parameters(
1045 p_api_version IN NUMBER,
1046 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1047 x_return_status OUT NOCOPY VARCHAR2,
1048 x_msg_count OUT NOCOPY NUMBER,
1049 x_msg_data OUT NOCOPY VARCHAR2,
1050 p_rpp_rec IN rpp_rec_type
1051 ) IS
1052
1053 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1054 l_api_name VARCHAR2(30) := 'DELETE_REPORT_PARAMETERS';
1055
1056 BEGIN
1057 -- Call start_activity to create savepoint, check compatibility
1058 -- and initialize message list
1059 l_return_status := OKL_API.START_ACTIVITY (
1060 l_api_name,
1061 p_init_msg_list,
1062 '_PVT',
1063 l_return_status);
1064
1065 -- Check if activity started successfully
1066 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1067 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1068 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1069 RAISE OKL_API.G_EXCEPTION_ERROR;
1070 END IF;
1071
1072 -- Tapi Call
1073 Okl_Rpp_Pvt.delete_row(
1074 p_api_version,
1075 p_init_msg_list,
1076 x_return_status,
1077 x_msg_count,
1078 x_msg_data,
1079 p_rpp_rec);
1080
1081 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1082 RAISE G_EXCEPTION_HALT_VALIDATION;
1083 ELSE
1084 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1085 l_return_status := x_return_status;
1086 END IF;
1087 END IF;
1088
1089 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1090
1091 EXCEPTION
1092 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1093 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1094 l_api_name,
1095 G_PKG_NAME,
1096 'OKL_API.G_RET_STS_ERROR',
1097 x_msg_count,
1098 x_msg_data,
1099 '_PVT');
1100
1101 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1102 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1103 l_api_name,
1104 G_PKG_NAME,
1105 'OKL_API.G_RET_STS_UNEXP_ERROR',
1106 x_msg_count,
1107 x_msg_data,
1108 '_PVT');
1109
1110 WHEN OTHERS THEN
1111 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1112 l_api_name,
1113 G_PKG_NAME,
1114 'OTHERS',
1115 x_msg_count,
1116 x_msg_data,
1117 '_PVT');
1118
1119 END delete_report_parameters;
1120
1121 PROCEDURE lock_report_parameters(
1122 p_api_version IN NUMBER,
1123 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2,
1127 p_rpp_rec IN rpp_rec_type
1128 ) IS
1129
1130 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1131 l_api_name VARCHAR2(30) := 'LOCK_REPORT_PARAMETERS';
1132
1133 BEGIN
1134 -- Call start_activity to create savepoint, check compatibility
1135 -- and initialize message list
1136 l_return_status := OKL_API.START_ACTIVITY (
1137 l_api_name,
1138 p_init_msg_list,
1139 '_PVT',
1140 l_return_status);
1141
1142 -- Check if activity started successfully
1143 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1144 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1145 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1146 RAISE OKL_API.G_EXCEPTION_ERROR;
1147 END IF;
1148
1149 -- Tapi Call
1150 Okl_Rpp_Pvt.lock_row(
1151 p_api_version,
1152 p_init_msg_list,
1153 x_return_status,
1154 x_msg_count,
1155 x_msg_data,
1156 p_rpp_rec);
1157
1158 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1159 RAISE G_EXCEPTION_HALT_VALIDATION;
1160 ELSE
1161 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1162 l_return_status := x_return_status;
1163 END IF;
1164 END IF;
1165
1166 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1167
1168 EXCEPTION
1169 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1170 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1171 l_api_name,
1172 G_PKG_NAME,
1173 'OKL_API.G_RET_STS_ERROR',
1174 x_msg_count,
1175 x_msg_data,
1176 '_PVT');
1177
1178 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1179 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1180 l_api_name,
1181 G_PKG_NAME,
1182 'OKL_API.G_RET_STS_UNEXP_ERROR',
1183 x_msg_count,
1184 x_msg_data,
1185 '_PVT');
1186
1187 WHEN OTHERS THEN
1188 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1189 l_api_name,
1190 G_PKG_NAME,
1191 'OTHERS',
1192 x_msg_count,
1193 x_msg_data,
1194 '_PVT');
1195
1196 END lock_report_parameters;
1197
1198 PROCEDURE create_report_parameters(
1199 p_api_version IN NUMBER,
1200 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1201 x_return_status OUT NOCOPY VARCHAR2,
1202 x_msg_count OUT NOCOPY NUMBER,
1203 x_msg_data OUT NOCOPY VARCHAR2,
1204 p_rpp_tbl IN rpp_tbl_type,
1205 x_rpp_tbl OUT NOCOPY rpp_tbl_type
1206 ) IS
1207
1208 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1209 l_api_name VARCHAR2(30) := 'CREATE_REPORT_PARAMETERS';
1210
1211 BEGIN
1212 -- Call start_activity to create savepoint, check compatibility
1213 -- and initialize message list
1214 l_return_status := OKL_API.START_ACTIVITY (
1215 l_api_name,
1216 p_init_msg_list,
1217 '_PVT',
1218 l_return_status);
1219
1220 -- Check if activity started successfully
1221 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1223 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1224 RAISE OKL_API.G_EXCEPTION_ERROR;
1225 END IF;
1226
1227 -- Tapi Call
1228 Okl_Rpp_Pvt.insert_row(
1229 p_api_version,
1230 p_init_msg_list,
1231 x_return_status,
1232 x_msg_count,
1233 x_msg_data,
1234 p_rpp_tbl,
1235 x_rpp_tbl);
1236
1237 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1238 RAISE G_EXCEPTION_HALT_VALIDATION;
1239 ELSE
1240 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1241 l_return_status := x_return_status;
1242 END IF;
1243 END IF;
1244
1245 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1246
1247 EXCEPTION
1248 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1249 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1250 l_api_name,
1251 G_PKG_NAME,
1252 'OKL_API.G_RET_STS_ERROR',
1253 x_msg_count,
1254 x_msg_data,
1255 '_PVT');
1256
1257 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1258 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1259 l_api_name,
1260 G_PKG_NAME,
1261 'OKL_API.G_RET_STS_UNEXP_ERROR',
1262 x_msg_count,
1263 x_msg_data,
1264 '_PVT');
1265
1266 WHEN OTHERS THEN
1267 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1268 l_api_name,
1269 G_PKG_NAME,
1270 'OTHERS',
1271 x_msg_count,
1272 x_msg_data,
1273 '_PVT');
1274
1275 END create_report_parameters;
1276
1277 PROCEDURE update_report_parameters(
1278 p_api_version IN NUMBER,
1279 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1280 x_return_status OUT NOCOPY VARCHAR2,
1281 x_msg_count OUT NOCOPY NUMBER,
1282 x_msg_data OUT NOCOPY VARCHAR2,
1283 p_rpp_tbl IN rpp_tbl_type,
1284 x_rpp_tbl OUT NOCOPY rpp_tbl_type
1285 ) IS
1286
1287 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1288 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_PARAMETERS';
1289
1290 BEGIN
1291 -- Call start_activity to create savepoint, check compatibility
1292 -- and initialize message list
1293 l_return_status := OKL_API.START_ACTIVITY (
1294 l_api_name,
1295 p_init_msg_list,
1296 '_PVT',
1297 l_return_status);
1298
1299 -- Check if activity started successfully
1300 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1301 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1302 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1303 RAISE OKL_API.G_EXCEPTION_ERROR;
1304 END IF;
1305
1306 -- Tapi Call
1307 Okl_Rpp_Pvt.update_row(
1308 p_api_version,
1309 p_init_msg_list,
1310 x_return_status,
1311 x_msg_count,
1312 x_msg_data,
1313 p_rpp_tbl,
1314 x_rpp_tbl);
1315
1316 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1317 RAISE G_EXCEPTION_HALT_VALIDATION;
1318 ELSE
1319 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1320 l_return_status := x_return_status;
1321 END IF;
1322 END IF;
1323
1324 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1325
1326 EXCEPTION
1327 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1328 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1329 l_api_name,
1330 G_PKG_NAME,
1331 'OKL_API.G_RET_STS_ERROR',
1332 x_msg_count,
1333 x_msg_data,
1334 '_PVT');
1335
1336 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1337 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1338 l_api_name,
1339 G_PKG_NAME,
1340 'OKL_API.G_RET_STS_UNEXP_ERROR',
1341 x_msg_count,
1342 x_msg_data,
1343 '_PVT');
1344
1345 WHEN OTHERS THEN
1346 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1347 l_api_name,
1348 G_PKG_NAME,
1349 'OTHERS',
1350 x_msg_count,
1351 x_msg_data,
1352 '_PVT');
1353
1354 END update_report_parameters;
1355
1356 PROCEDURE delete_report_parameters(
1357 p_api_version IN NUMBER,
1358 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_count OUT NOCOPY NUMBER,
1361 x_msg_data OUT NOCOPY VARCHAR2,
1362 p_rpp_tbl IN rpp_tbl_type
1363 ) IS
1364
1365 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1366 l_api_name VARCHAR2(30) := 'DELETE_REPORT_PARAMETERS';
1367
1368 BEGIN
1369 -- Call start_activity to create savepoint, check compatibility
1370 -- and initialize message list
1371 l_return_status := OKL_API.START_ACTIVITY (
1372 l_api_name,
1373 p_init_msg_list,
1374 '_PVT',
1375 l_return_status);
1376
1377 -- Check if activity started successfully
1378 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1379 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1380 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1381 RAISE OKL_API.G_EXCEPTION_ERROR;
1382 END IF;
1383
1384 -- Tapi Call
1385 Okl_Rpp_Pvt.delete_row(
1386 p_api_version,
1387 p_init_msg_list,
1388 x_return_status,
1389 x_msg_count,
1390 x_msg_data,
1391 p_rpp_tbl);
1392
1393 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1394 RAISE G_EXCEPTION_HALT_VALIDATION;
1395 ELSE
1396 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1397 l_return_status := x_return_status;
1398 END IF;
1399 END IF;
1400
1401 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1402
1403 EXCEPTION
1404 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1405 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1406 l_api_name,
1407 G_PKG_NAME,
1408 'OKL_API.G_RET_STS_ERROR',
1409 x_msg_count,
1410 x_msg_data,
1411 '_PVT');
1412
1413 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1414 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1415 l_api_name,
1416 G_PKG_NAME,
1417 'OKL_API.G_RET_STS_UNEXP_ERROR',
1418 x_msg_count,
1419 x_msg_data,
1420 '_PVT');
1421
1422 WHEN OTHERS THEN
1423 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1424 l_api_name,
1425 G_PKG_NAME,
1426 'OTHERS',
1427 x_msg_count,
1428 x_msg_data,
1429 '_PVT');
1430
1431 END delete_report_parameters;
1432
1433 PROCEDURE lock_report_parameters(
1434 p_api_version IN NUMBER,
1435 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1436 x_return_status OUT NOCOPY VARCHAR2,
1437 x_msg_count OUT NOCOPY NUMBER,
1438 x_msg_data OUT NOCOPY VARCHAR2,
1439 p_rpp_tbl IN rpp_tbl_type
1440 ) IS
1441
1442 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1443 l_api_name VARCHAR2(30) := 'LOCK_REPORT_PARAMETERS';
1444
1445 BEGIN
1446 -- Call start_activity to create savepoint, check compatibility
1447 -- and initialize message list
1448 l_return_status := OKL_API.START_ACTIVITY (
1449 l_api_name,
1450 p_init_msg_list,
1451 '_PVT',
1452 l_return_status);
1453
1454 -- Check if activity started successfully
1455 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1456 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1457 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1458 RAISE OKL_API.G_EXCEPTION_ERROR;
1459 END IF;
1460
1461 -- Tapi Call
1462 Okl_Rpp_Pvt.lock_row(
1463 p_api_version,
1464 p_init_msg_list,
1465 x_return_status,
1466 x_msg_count,
1467 x_msg_data,
1468 p_rpp_tbl);
1469
1470 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1471 RAISE G_EXCEPTION_HALT_VALIDATION;
1472 ELSE
1473 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1474 l_return_status := x_return_status;
1475 END IF;
1476 END IF;
1477
1478 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1479
1480 EXCEPTION
1481 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1482 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1483 l_api_name,
1484 G_PKG_NAME,
1485 'OKL_API.G_RET_STS_ERROR',
1486 x_msg_count,
1487 x_msg_data,
1488 '_PVT');
1489
1490 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1491 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1492 l_api_name,
1493 G_PKG_NAME,
1494 'OKL_API.G_RET_STS_UNEXP_ERROR',
1495 x_msg_count,
1496 x_msg_data,
1497 '_PVT');
1498
1499 WHEN OTHERS THEN
1500 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1501 l_api_name,
1502 G_PKG_NAME,
1503 'OTHERS',
1504 x_msg_count,
1505 x_msg_data,
1506 '_PVT');
1507
1508 END lock_report_parameters;
1509
1510 PROCEDURE create_report_acc_parameters(
1511 p_api_version IN NUMBER,
1512 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_count OUT NOCOPY NUMBER,
1515 x_msg_data OUT NOCOPY VARCHAR2,
1516 p_rap_rec IN rap_rec_type,
1517 x_rap_rec OUT NOCOPY rap_rec_type
1518 ) IS
1519
1520 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1521 l_api_name VARCHAR2(30) := 'CREATE_REPORT_ACC_PARAMS';
1522
1523 BEGIN
1524 -- Call start_activity to create savepoint, check compatibility
1525 -- and initialize message list
1526 l_return_status := OKL_API.START_ACTIVITY (
1527 l_api_name,
1528 p_init_msg_list,
1529 '_PVT',
1530 l_return_status);
1531
1532 -- Check if activity started successfully
1533 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1535 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1536 RAISE OKL_API.G_EXCEPTION_ERROR;
1537 END IF;
1538
1539 -- Tapi Call
1540 Okl_Rap_Pvt.insert_row(
1541 p_api_version,
1542 p_init_msg_list,
1543 x_return_status,
1544 x_msg_count,
1545 x_msg_data,
1546 p_rap_rec,
1547 x_rap_rec);
1548
1549 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1550 RAISE G_EXCEPTION_HALT_VALIDATION;
1551 ELSE
1552 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1553 l_return_status := x_return_status;
1554 END IF;
1555 END IF;
1556
1557 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1558
1559 EXCEPTION
1560 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1561 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1562 l_api_name,
1563 G_PKG_NAME,
1564 'OKL_API.G_RET_STS_ERROR',
1565 x_msg_count,
1566 x_msg_data,
1567 '_PVT');
1568
1569 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1570 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1571 l_api_name,
1572 G_PKG_NAME,
1573 'OKL_API.G_RET_STS_UNEXP_ERROR',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT');
1577
1578 WHEN OTHERS THEN
1579 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1580 l_api_name,
1581 G_PKG_NAME,
1582 'OTHERS',
1583 x_msg_count,
1584 x_msg_data,
1585 '_PVT');
1586
1587 END create_report_acc_parameters;
1588
1589 PROCEDURE update_report_acc_parameters(
1590 p_api_version IN NUMBER,
1591 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1592 x_return_status OUT NOCOPY VARCHAR2,
1593 x_msg_count OUT NOCOPY NUMBER,
1594 x_msg_data OUT NOCOPY VARCHAR2,
1595 p_rap_rec IN rap_rec_type,
1596 x_rap_rec OUT NOCOPY rap_rec_type
1597 ) IS
1598
1599 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1600 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_ACC_PARAMS';
1601
1602 BEGIN
1603 -- Call start_activity to create savepoint, check compatibility
1604 -- and initialize message list
1605 l_return_status := OKL_API.START_ACTIVITY (
1606 l_api_name,
1607 p_init_msg_list,
1608 '_PVT',
1609 l_return_status);
1610
1611 -- Check if activity started successfully
1612 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1613 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1614 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1615 RAISE OKL_API.G_EXCEPTION_ERROR;
1616 END IF;
1617
1618 -- Tapi Call
1619 Okl_Rap_Pvt.update_row(
1620 p_api_version,
1621 p_init_msg_list,
1622 x_return_status,
1623 x_msg_count,
1624 x_msg_data,
1625 p_rap_rec,
1626 x_rap_rec);
1627
1628 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1629 RAISE G_EXCEPTION_HALT_VALIDATION;
1630 ELSE
1631 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1632 l_return_status := x_return_status;
1633 END IF;
1634 END IF;
1635
1636 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1637
1638 EXCEPTION
1639 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1640 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1641 l_api_name,
1642 G_PKG_NAME,
1643 'OKL_API.G_RET_STS_ERROR',
1644 x_msg_count,
1645 x_msg_data,
1646 '_PVT');
1647
1648 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1649 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1650 l_api_name,
1651 G_PKG_NAME,
1652 'OKL_API.G_RET_STS_UNEXP_ERROR',
1653 x_msg_count,
1654 x_msg_data,
1655 '_PVT');
1656
1657 WHEN OTHERS THEN
1658 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1659 l_api_name,
1660 G_PKG_NAME,
1661 'OTHERS',
1662 x_msg_count,
1663 x_msg_data,
1664 '_PVT');
1665
1666 END update_report_acc_parameters;
1667
1668 PROCEDURE delete_report_acc_parameters(
1669 p_api_version IN NUMBER,
1670 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1671 x_return_status OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER,
1673 x_msg_data OUT NOCOPY VARCHAR2,
1674 p_rap_rec IN rap_rec_type
1675 ) IS
1676
1677 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1678 l_api_name VARCHAR2(30) := 'DELETE_REPORT_ACC_PARAMS';
1679
1680 BEGIN
1681 -- Call start_activity to create savepoint, check compatibility
1682 -- and initialize message list
1683 l_return_status := OKL_API.START_ACTIVITY (
1684 l_api_name,
1685 p_init_msg_list,
1686 '_PVT',
1687 l_return_status);
1688
1689 -- Check if activity started successfully
1690 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1691 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1692 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1693 RAISE OKL_API.G_EXCEPTION_ERROR;
1694 END IF;
1695
1696 -- Tapi Call
1697 Okl_Rap_Pvt.delete_row(
1698 p_api_version,
1699 p_init_msg_list,
1700 x_return_status,
1701 x_msg_count,
1702 x_msg_data,
1703 p_rap_rec);
1704
1705 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1706 RAISE G_EXCEPTION_HALT_VALIDATION;
1707 ELSE
1708 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1709 l_return_status := x_return_status;
1710 END IF;
1711 END IF;
1712
1713 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1714
1715 EXCEPTION
1716 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1717 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1718 l_api_name,
1719 G_PKG_NAME,
1720 'OKL_API.G_RET_STS_ERROR',
1721 x_msg_count,
1722 x_msg_data,
1723 '_PVT');
1724
1725 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1726 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1727 l_api_name,
1728 G_PKG_NAME,
1729 'OKL_API.G_RET_STS_UNEXP_ERROR',
1730 x_msg_count,
1731 x_msg_data,
1732 '_PVT');
1733
1734 WHEN OTHERS THEN
1735 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1736 l_api_name,
1737 G_PKG_NAME,
1738 'OTHERS',
1739 x_msg_count,
1740 x_msg_data,
1741 '_PVT');
1742
1743 END delete_report_acc_parameters;
1744
1745 PROCEDURE lock_report_acc_parameters(
1746 p_api_version IN NUMBER,
1747 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1748 x_return_status OUT NOCOPY VARCHAR2,
1749 x_msg_count OUT NOCOPY NUMBER,
1750 x_msg_data OUT NOCOPY VARCHAR2,
1751 p_rap_rec IN rap_rec_type
1752 ) IS
1753
1754 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1755 l_api_name VARCHAR2(30) := 'LOCK_REPORT_ACC_PARAMS';
1756
1757 BEGIN
1758 -- Call start_activity to create savepoint, check compatibility
1759 -- and initialize message list
1760 l_return_status := OKL_API.START_ACTIVITY (
1761 l_api_name,
1762 p_init_msg_list,
1763 '_PVT',
1764 l_return_status);
1765
1766 -- Check if activity started successfully
1767 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1768 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1769 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1770 RAISE OKL_API.G_EXCEPTION_ERROR;
1771 END IF;
1772
1773 -- Tapi Call
1774 Okl_Rap_Pvt.lock_row(
1775 p_api_version,
1776 p_init_msg_list,
1777 x_return_status,
1778 x_msg_count,
1779 x_msg_data,
1780 p_rap_rec);
1781
1782 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1783 RAISE G_EXCEPTION_HALT_VALIDATION;
1784 ELSE
1785 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1786 l_return_status := x_return_status;
1787 END IF;
1788 END IF;
1789
1790 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1791
1792 EXCEPTION
1793 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1794 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1795 l_api_name,
1796 G_PKG_NAME,
1797 'OKL_API.G_RET_STS_ERROR',
1798 x_msg_count,
1799 x_msg_data,
1800 '_PVT');
1801
1802 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1803 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1804 l_api_name,
1805 G_PKG_NAME,
1806 'OKL_API.G_RET_STS_UNEXP_ERROR',
1807 x_msg_count,
1808 x_msg_data,
1809 '_PVT');
1810
1811 WHEN OTHERS THEN
1812 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1813 l_api_name,
1814 G_PKG_NAME,
1815 'OTHERS',
1816 x_msg_count,
1817 x_msg_data,
1818 '_PVT');
1819
1820 END lock_report_acc_parameters;
1821
1822 PROCEDURE create_report_acc_parameters(
1823 p_api_version IN NUMBER,
1824 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1825 x_return_status OUT NOCOPY VARCHAR2,
1826 x_msg_count OUT NOCOPY NUMBER,
1827 x_msg_data OUT NOCOPY VARCHAR2,
1828 p_rap_tbl IN rap_tbl_type,
1829 x_rap_tbl OUT NOCOPY rap_tbl_type
1830 ) IS
1831
1832 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1833 l_api_name VARCHAR2(30) := 'CREATE_REPORT_ACC_PARAMS';
1834
1835 BEGIN
1836 -- Call start_activity to create savepoint, check compatibility
1837 -- and initialize message list
1838 l_return_status := OKL_API.START_ACTIVITY (
1839 l_api_name,
1840 p_init_msg_list,
1841 '_PVT',
1842 l_return_status);
1843
1844 -- Check if activity started successfully
1845 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1846 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1847 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1848 RAISE OKL_API.G_EXCEPTION_ERROR;
1849 END IF;
1850
1851 -- Tapi Call
1852 Okl_Rap_Pvt.insert_row(
1853 p_api_version,
1854 p_init_msg_list,
1855 x_return_status,
1856 x_msg_count,
1857 x_msg_data,
1858 p_rap_tbl,
1859 x_rap_tbl);
1860
1861 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1862 RAISE G_EXCEPTION_HALT_VALIDATION;
1863 ELSE
1864 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1865 l_return_status := x_return_status;
1866 END IF;
1867 END IF;
1868
1869 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1870
1871 EXCEPTION
1872 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1873 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1874 l_api_name,
1875 G_PKG_NAME,
1876 'OKL_API.G_RET_STS_ERROR',
1877 x_msg_count,
1878 x_msg_data,
1879 '_PVT');
1880
1881 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1882 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1883 l_api_name,
1884 G_PKG_NAME,
1885 'OKL_API.G_RET_STS_UNEXP_ERROR',
1886 x_msg_count,
1887 x_msg_data,
1888 '_PVT');
1889
1890 WHEN OTHERS THEN
1891 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1892 l_api_name,
1893 G_PKG_NAME,
1894 'OTHERS',
1895 x_msg_count,
1896 x_msg_data,
1897 '_PVT');
1898
1899 END create_report_acc_parameters;
1900
1901 PROCEDURE update_report_acc_parameters(
1902 p_api_version IN NUMBER,
1903 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1904 x_return_status OUT NOCOPY VARCHAR2,
1905 x_msg_count OUT NOCOPY NUMBER,
1906 x_msg_data OUT NOCOPY VARCHAR2,
1907 p_rap_tbl IN rap_tbl_type,
1908 x_rap_tbl OUT NOCOPY rap_tbl_type
1909 ) IS
1910
1911 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1912 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_ACC_PARAMS';
1913
1914 BEGIN
1915 -- Call start_activity to create savepoint, check compatibility
1916 -- and initialize message list
1917 l_return_status := OKL_API.START_ACTIVITY (
1918 l_api_name,
1919 p_init_msg_list,
1920 '_PVT',
1921 l_return_status);
1922
1923 -- Check if activity started successfully
1924 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1925 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1926 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1927 RAISE OKL_API.G_EXCEPTION_ERROR;
1928 END IF;
1929
1930 -- Tapi Call
1931 Okl_Rap_Pvt.update_row(
1932 p_api_version,
1933 p_init_msg_list,
1934 x_return_status,
1935 x_msg_count,
1936 x_msg_data,
1937 p_rap_tbl,
1938 x_rap_tbl);
1939
1940 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1941 RAISE G_EXCEPTION_HALT_VALIDATION;
1942 ELSE
1943 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1944 l_return_status := x_return_status;
1945 END IF;
1946 END IF;
1947
1948 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1949
1950 EXCEPTION
1951 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1952 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1953 l_api_name,
1954 G_PKG_NAME,
1955 'OKL_API.G_RET_STS_ERROR',
1956 x_msg_count,
1957 x_msg_data,
1958 '_PVT');
1959
1960 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1961 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1962 l_api_name,
1963 G_PKG_NAME,
1964 'OKL_API.G_RET_STS_UNEXP_ERROR',
1965 x_msg_count,
1966 x_msg_data,
1967 '_PVT');
1968
1969 WHEN OTHERS THEN
1970 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1971 l_api_name,
1972 G_PKG_NAME,
1973 'OTHERS',
1974 x_msg_count,
1975 x_msg_data,
1976 '_PVT');
1977
1978 END update_report_acc_parameters;
1979
1980 PROCEDURE delete_report_acc_parameters(
1981 p_api_version IN NUMBER,
1982 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1983 x_return_status OUT NOCOPY VARCHAR2,
1984 x_msg_count OUT NOCOPY NUMBER,
1985 x_msg_data OUT NOCOPY VARCHAR2,
1986 p_rap_tbl IN rap_tbl_type
1987 ) IS
1988
1989 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1990 l_api_name VARCHAR2(30) := 'DELETE_REPORT_ACC_PARAMS';
1991
1992 BEGIN
1993 -- Call start_activity to create savepoint, check compatibility
1994 -- and initialize message list
1995 l_return_status := OKL_API.START_ACTIVITY (
1996 l_api_name,
1997 p_init_msg_list,
1998 '_PVT',
1999 l_return_status);
2000
2001 -- Check if activity started successfully
2002 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2003 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2004 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2005 RAISE OKL_API.G_EXCEPTION_ERROR;
2006 END IF;
2007
2008 -- Tapi Call
2009 Okl_Rap_Pvt.delete_row(
2010 p_api_version,
2011 p_init_msg_list,
2012 x_return_status,
2013 x_msg_count,
2014 x_msg_data,
2015 p_rap_tbl);
2016
2017 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2018 RAISE G_EXCEPTION_HALT_VALIDATION;
2019 ELSE
2020 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2021 l_return_status := x_return_status;
2022 END IF;
2023 END IF;
2024
2025 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2026
2027 EXCEPTION
2028 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2029 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2030 l_api_name,
2031 G_PKG_NAME,
2032 'OKL_API.G_RET_STS_ERROR',
2033 x_msg_count,
2034 x_msg_data,
2035 '_PVT');
2036
2037 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2038 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2039 l_api_name,
2040 G_PKG_NAME,
2041 'OKL_API.G_RET_STS_UNEXP_ERROR',
2042 x_msg_count,
2043 x_msg_data,
2044 '_PVT');
2045
2046 WHEN OTHERS THEN
2047 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2048 l_api_name,
2049 G_PKG_NAME,
2050 'OTHERS',
2051 x_msg_count,
2052 x_msg_data,
2053 '_PVT');
2054
2055 END delete_report_acc_parameters;
2056
2057 PROCEDURE lock_report_acc_parameters(
2058 p_api_version IN NUMBER,
2059 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2060 x_return_status OUT NOCOPY VARCHAR2,
2061 x_msg_count OUT NOCOPY NUMBER,
2062 x_msg_data OUT NOCOPY VARCHAR2,
2063 p_rap_tbl IN rap_tbl_type
2064 ) IS
2065
2066 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2067 l_api_name VARCHAR2(30) := 'LOCK_REPORT_ACC_PARAMS';
2068
2069 BEGIN
2070 -- Call start_activity to create savepoint, check compatibility
2071 -- and initialize message list
2072 l_return_status := OKL_API.START_ACTIVITY (
2073 l_api_name,
2074 p_init_msg_list,
2075 '_PVT',
2076 l_return_status);
2077
2078 -- Check if activity started successfully
2079 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2080 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2081 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2082 RAISE OKL_API.G_EXCEPTION_ERROR;
2083 END IF;
2084
2085 -- Tapi Call
2086 Okl_Rap_Pvt.lock_row(
2087 p_api_version,
2088 p_init_msg_list,
2089 x_return_status,
2090 x_msg_count,
2091 x_msg_data,
2092 p_rap_tbl);
2093
2094 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2095 RAISE G_EXCEPTION_HALT_VALIDATION;
2096 ELSE
2097 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2098 l_return_status := x_return_status;
2099 END IF;
2100 END IF;
2101
2102 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2103
2104 EXCEPTION
2105 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2106 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2107 l_api_name,
2108 G_PKG_NAME,
2109 'OKL_API.G_RET_STS_ERROR',
2110 x_msg_count,
2111 x_msg_data,
2112 '_PVT');
2113
2114 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2115 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2116 l_api_name,
2117 G_PKG_NAME,
2118 'OKL_API.G_RET_STS_UNEXP_ERROR',
2119 x_msg_count,
2120 x_msg_data,
2121 '_PVT');
2122
2123 WHEN OTHERS THEN
2124 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2125 l_api_name,
2126 G_PKG_NAME,
2127 'OTHERS',
2128 x_msg_count,
2129 x_msg_data,
2130 '_PVT');
2131
2132 END lock_report_acc_parameters;
2133
2134 PROCEDURE create_report_strm_parameters(
2135 p_api_version IN NUMBER,
2136 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_msg_count OUT NOCOPY NUMBER,
2139 x_msg_data OUT NOCOPY VARCHAR2,
2140 p_rps_rec IN rps_rec_type,
2141 x_rps_rec OUT NOCOPY rps_rec_type
2142 ) IS
2143
2144 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2145 l_api_name VARCHAR2(30) := 'CREATE_REPORT_STRM_PARAMS';
2146
2147 BEGIN
2148 -- Call start_activity to create savepoint, check compatibility
2149 -- and initialize message list
2150 l_return_status := OKL_API.START_ACTIVITY (
2151 l_api_name,
2152 p_init_msg_list,
2153 '_PVT',
2154 l_return_status);
2155
2156 -- Check if activity started successfully
2157 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2158 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2159 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2160 RAISE OKL_API.G_EXCEPTION_ERROR;
2161 END IF;
2162
2163 -- Tapi Call
2164 Okl_Rsp_Pvt.insert_row(
2165 p_api_version,
2166 p_init_msg_list,
2167 x_return_status,
2168 x_msg_count,
2169 x_msg_data,
2170 p_rps_rec,
2171 x_rps_rec);
2172
2173 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2174 RAISE G_EXCEPTION_HALT_VALIDATION;
2175 ELSE
2176 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2177 l_return_status := x_return_status;
2178 END IF;
2179 END IF;
2180
2181 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2182
2183 EXCEPTION
2184 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2185 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2186 l_api_name,
2187 G_PKG_NAME,
2188 'OKL_API.G_RET_STS_ERROR',
2189 x_msg_count,
2190 x_msg_data,
2191 '_PVT');
2192
2193 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2194 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2195 l_api_name,
2196 G_PKG_NAME,
2197 'OKL_API.G_RET_STS_UNEXP_ERROR',
2198 x_msg_count,
2199 x_msg_data,
2200 '_PVT');
2201
2202 WHEN OTHERS THEN
2203 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2204 l_api_name,
2205 G_PKG_NAME,
2206 'OTHERS',
2207 x_msg_count,
2208 x_msg_data,
2209 '_PVT');
2210
2211 END create_report_strm_parameters;
2212
2213 PROCEDURE update_report_strm_parameters(
2214 p_api_version IN NUMBER,
2215 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2216 x_return_status OUT NOCOPY VARCHAR2,
2217 x_msg_count OUT NOCOPY NUMBER,
2218 x_msg_data OUT NOCOPY VARCHAR2,
2219 p_rps_rec IN rps_rec_type,
2220 x_rps_rec OUT NOCOPY rps_rec_type
2221 ) IS
2222
2223 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2224 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_STRM_PARAMS';
2225
2226 BEGIN
2227 -- Call start_activity to create savepoint, check compatibility
2228 -- and initialize message list
2229 l_return_status := OKL_API.START_ACTIVITY (
2230 l_api_name,
2231 p_init_msg_list,
2232 '_PVT',
2233 l_return_status);
2234
2235 -- Check if activity started successfully
2236 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2237 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2238 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2239 RAISE OKL_API.G_EXCEPTION_ERROR;
2240 END IF;
2241
2242 -- Tapi Call
2243 Okl_Rsp_Pvt.update_row(
2244 p_api_version,
2245 p_init_msg_list,
2246 x_return_status,
2247 x_msg_count,
2248 x_msg_data,
2249 p_rps_rec,
2250 x_rps_rec);
2251
2252 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2253 RAISE G_EXCEPTION_HALT_VALIDATION;
2254 ELSE
2255 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2256 l_return_status := x_return_status;
2257 END IF;
2258 END IF;
2259
2260 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2261
2262 EXCEPTION
2263 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2264 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2265 l_api_name,
2266 G_PKG_NAME,
2267 'OKL_API.G_RET_STS_ERROR',
2268 x_msg_count,
2269 x_msg_data,
2270 '_PVT');
2271
2272 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2273 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2274 l_api_name,
2275 G_PKG_NAME,
2276 'OKL_API.G_RET_STS_UNEXP_ERROR',
2277 x_msg_count,
2278 x_msg_data,
2279 '_PVT');
2280
2281 WHEN OTHERS THEN
2282 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2283 l_api_name,
2284 G_PKG_NAME,
2285 'OTHERS',
2286 x_msg_count,
2287 x_msg_data,
2288 '_PVT');
2289
2290 END update_report_strm_parameters;
2291
2292 PROCEDURE delete_report_strm_parameters(
2293 p_api_version IN NUMBER,
2294 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2295 x_return_status OUT NOCOPY VARCHAR2,
2296 x_msg_count OUT NOCOPY NUMBER,
2297 x_msg_data OUT NOCOPY VARCHAR2,
2298 p_rps_rec IN rps_rec_type
2299 ) IS
2300
2301 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2302 l_api_name VARCHAR2(30) := 'DELETE_REPORT_STRM_PARAMS';
2303
2304 BEGIN
2305 -- Call start_activity to create savepoint, check compatibility
2306 -- and initialize message list
2307 l_return_status := OKL_API.START_ACTIVITY (
2308 l_api_name,
2309 p_init_msg_list,
2310 '_PVT',
2311 l_return_status);
2312
2313 -- Check if activity started successfully
2314 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2315 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2316 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2317 RAISE OKL_API.G_EXCEPTION_ERROR;
2318 END IF;
2319
2320 -- Tapi Call
2321 Okl_Rsp_Pvt.delete_row(
2322 p_api_version,
2323 p_init_msg_list,
2324 x_return_status,
2325 x_msg_count,
2326 x_msg_data,
2327 p_rps_rec);
2328
2329 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2330 RAISE G_EXCEPTION_HALT_VALIDATION;
2331 ELSE
2332 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2333 l_return_status := x_return_status;
2334 END IF;
2335 END IF;
2336
2337 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2338
2339 EXCEPTION
2340 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2341 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2342 l_api_name,
2343 G_PKG_NAME,
2344 'OKL_API.G_RET_STS_ERROR',
2345 x_msg_count,
2346 x_msg_data,
2347 '_PVT');
2348
2349 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2350 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2351 l_api_name,
2352 G_PKG_NAME,
2353 'OKL_API.G_RET_STS_UNEXP_ERROR',
2354 x_msg_count,
2355 x_msg_data,
2356 '_PVT');
2357
2358 WHEN OTHERS THEN
2359 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2360 l_api_name,
2361 G_PKG_NAME,
2362 'OTHERS',
2363 x_msg_count,
2364 x_msg_data,
2365 '_PVT');
2366
2367 END delete_report_strm_parameters;
2368
2369 PROCEDURE lock_report_strm_parameters(
2370 p_api_version IN NUMBER,
2371 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2372 x_return_status OUT NOCOPY VARCHAR2,
2373 x_msg_count OUT NOCOPY NUMBER,
2374 x_msg_data OUT NOCOPY VARCHAR2,
2375 p_rps_rec IN rps_rec_type
2376 ) IS
2377
2378 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2379 l_api_name VARCHAR2(30) := 'LOCK_REPORT_STRM_PARAMS';
2380
2381 BEGIN
2382 -- Call start_activity to create savepoint, check compatibility
2383 -- and initialize message list
2384 l_return_status := OKL_API.START_ACTIVITY (
2385 l_api_name,
2386 p_init_msg_list,
2387 '_PVT',
2388 l_return_status);
2389
2390 -- Check if activity started successfully
2391 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2394 RAISE OKL_API.G_EXCEPTION_ERROR;
2395 END IF;
2396
2397 -- Tapi Call
2398 Okl_Rsp_Pvt.lock_row(
2399 p_api_version,
2400 p_init_msg_list,
2401 x_return_status,
2402 x_msg_count,
2403 x_msg_data,
2404 p_rps_rec);
2405
2406 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2407 RAISE G_EXCEPTION_HALT_VALIDATION;
2408 ELSE
2409 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2410 l_return_status := x_return_status;
2411 END IF;
2412 END IF;
2413
2414 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2415
2416 EXCEPTION
2417 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2418 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2419 l_api_name,
2420 G_PKG_NAME,
2421 'OKL_API.G_RET_STS_ERROR',
2422 x_msg_count,
2423 x_msg_data,
2424 '_PVT');
2425
2426 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2427 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2428 l_api_name,
2429 G_PKG_NAME,
2430 'OKL_API.G_RET_STS_UNEXP_ERROR',
2431 x_msg_count,
2432 x_msg_data,
2433 '_PVT');
2434
2435 WHEN OTHERS THEN
2436 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2437 l_api_name,
2438 G_PKG_NAME,
2439 'OTHERS',
2440 x_msg_count,
2441 x_msg_data,
2442 '_PVT');
2443
2444 END lock_report_strm_parameters;
2445
2446 PROCEDURE create_report_strm_parameters(
2447 p_api_version IN NUMBER,
2448 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2449 x_return_status OUT NOCOPY VARCHAR2,
2450 x_msg_count OUT NOCOPY NUMBER,
2451 x_msg_data OUT NOCOPY VARCHAR2,
2452 p_rps_tbl IN rps_tbl_type,
2453 x_rps_tbl OUT NOCOPY rps_tbl_type
2454 ) IS
2455
2456 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2457 l_api_name VARCHAR2(30) := 'CREATE_REPORT_STRM_PARAMS';
2458
2459 BEGIN
2460 -- Call start_activity to create savepoint, check compatibility
2461 -- and initialize message list
2462 l_return_status := OKL_API.START_ACTIVITY (
2463 l_api_name,
2464 p_init_msg_list,
2465 '_PVT',
2466 l_return_status);
2467
2468 -- Check if activity started successfully
2469 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2470 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2471 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2472 RAISE OKL_API.G_EXCEPTION_ERROR;
2473 END IF;
2474
2475 -- Tapi Call
2476 Okl_Rsp_Pvt.insert_row(
2477 p_api_version,
2478 p_init_msg_list,
2479 x_return_status,
2480 x_msg_count,
2481 x_msg_data,
2482 p_rps_tbl,
2483 x_rps_tbl);
2484
2485 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2486 RAISE G_EXCEPTION_HALT_VALIDATION;
2487 ELSE
2488 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2489 l_return_status := x_return_status;
2490 END IF;
2491 END IF;
2492
2493 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2494
2495 EXCEPTION
2496 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2497 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2498 l_api_name,
2499 G_PKG_NAME,
2500 'OKL_API.G_RET_STS_ERROR',
2501 x_msg_count,
2502 x_msg_data,
2503 '_PVT');
2504
2505 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2506 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2507 l_api_name,
2508 G_PKG_NAME,
2509 'OKL_API.G_RET_STS_UNEXP_ERROR',
2510 x_msg_count,
2511 x_msg_data,
2512 '_PVT');
2513
2514 WHEN OTHERS THEN
2515 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2516 l_api_name,
2517 G_PKG_NAME,
2518 'OTHERS',
2519 x_msg_count,
2520 x_msg_data,
2521 '_PVT');
2522
2523 END create_report_strm_parameters;
2524
2525 PROCEDURE update_report_strm_parameters(
2526 p_api_version IN NUMBER,
2527 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2528 x_return_status OUT NOCOPY VARCHAR2,
2529 x_msg_count OUT NOCOPY NUMBER,
2530 x_msg_data OUT NOCOPY VARCHAR2,
2531 p_rps_tbl IN rps_tbl_type,
2532 x_rps_tbl OUT NOCOPY rps_tbl_type
2533 ) IS
2534
2535 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2536 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_STRM_PARAMS';
2537
2538 BEGIN
2539 -- Call start_activity to create savepoint, check compatibility
2540 -- and initialize message list
2541 l_return_status := OKL_API.START_ACTIVITY (
2542 l_api_name,
2543 p_init_msg_list,
2544 '_PVT',
2545 l_return_status);
2546
2547 -- Check if activity started successfully
2548 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2549 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2550 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2551 RAISE OKL_API.G_EXCEPTION_ERROR;
2552 END IF;
2553
2554 -- Tapi Call
2555 Okl_Rsp_Pvt.update_row(
2556 p_api_version,
2557 p_init_msg_list,
2558 x_return_status,
2559 x_msg_count,
2560 x_msg_data,
2561 p_rps_tbl,
2562 x_rps_tbl);
2563
2564 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2565 RAISE G_EXCEPTION_HALT_VALIDATION;
2566 ELSE
2567 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2568 l_return_status := x_return_status;
2569 END IF;
2570 END IF;
2571
2572 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2573
2574 EXCEPTION
2575 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2576 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2577 l_api_name,
2578 G_PKG_NAME,
2579 'OKL_API.G_RET_STS_ERROR',
2580 x_msg_count,
2581 x_msg_data,
2582 '_PVT');
2583
2584 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2585 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2586 l_api_name,
2587 G_PKG_NAME,
2588 'OKL_API.G_RET_STS_UNEXP_ERROR',
2589 x_msg_count,
2590 x_msg_data,
2591 '_PVT');
2592
2593 WHEN OTHERS THEN
2594 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2595 l_api_name,
2596 G_PKG_NAME,
2597 'OTHERS',
2598 x_msg_count,
2599 x_msg_data,
2600 '_PVT');
2601
2602 END update_report_strm_parameters;
2603
2604 PROCEDURE delete_report_strm_parameters(
2605 p_api_version IN NUMBER,
2606 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2607 x_return_status OUT NOCOPY VARCHAR2,
2608 x_msg_count OUT NOCOPY NUMBER,
2609 x_msg_data OUT NOCOPY VARCHAR2,
2610 p_rps_tbl IN rps_tbl_type
2611 ) IS
2612
2613 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2614 l_api_name VARCHAR2(30) := 'DELETE_REPORT_STRM_PARAMS';
2615
2616 BEGIN
2617 -- Call start_activity to create savepoint, check compatibility
2618 -- and initialize message list
2619 l_return_status := OKL_API.START_ACTIVITY (
2620 l_api_name,
2621 p_init_msg_list,
2622 '_PVT',
2623 l_return_status);
2624
2625 -- Check if activity started successfully
2626 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2627 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2628 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2629 RAISE OKL_API.G_EXCEPTION_ERROR;
2630 END IF;
2631
2632 -- Tapi Call
2633 Okl_Rsp_Pvt.delete_row(
2634 p_api_version,
2635 p_init_msg_list,
2636 x_return_status,
2637 x_msg_count,
2638 x_msg_data,
2639 p_rps_tbl);
2640
2641 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2642 RAISE G_EXCEPTION_HALT_VALIDATION;
2643 ELSE
2644 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2645 l_return_status := x_return_status;
2646 END IF;
2647 END IF;
2648
2649 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2650
2651 EXCEPTION
2652 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2653 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2654 l_api_name,
2655 G_PKG_NAME,
2656 'OKL_API.G_RET_STS_ERROR',
2657 x_msg_count,
2658 x_msg_data,
2659 '_PVT');
2660
2661 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2662 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2663 l_api_name,
2664 G_PKG_NAME,
2665 'OKL_API.G_RET_STS_UNEXP_ERROR',
2666 x_msg_count,
2667 x_msg_data,
2668 '_PVT');
2669
2670 WHEN OTHERS THEN
2671 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2672 l_api_name,
2673 G_PKG_NAME,
2674 'OTHERS',
2675 x_msg_count,
2676 x_msg_data,
2677 '_PVT');
2678
2679 END delete_report_strm_parameters;
2680
2681 PROCEDURE lock_report_strm_parameters(
2682 p_api_version IN NUMBER,
2683 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2684 x_return_status OUT NOCOPY VARCHAR2,
2685 x_msg_count OUT NOCOPY NUMBER,
2686 x_msg_data OUT NOCOPY VARCHAR2,
2687 p_rps_tbl IN rps_tbl_type
2688 ) IS
2689
2690 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2691 l_api_name VARCHAR2(30) := 'LOCK_REPORT_STRM_PARAMS';
2692
2693 BEGIN
2694 -- Call start_activity to create savepoint, check compatibility
2695 -- and initialize message list
2696 l_return_status := OKL_API.START_ACTIVITY (
2697 l_api_name,
2698 p_init_msg_list,
2699 '_PVT',
2700 l_return_status);
2701
2702 -- Check if activity started successfully
2703 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2704 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2705 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2706 RAISE OKL_API.G_EXCEPTION_ERROR;
2707 END IF;
2708
2709 -- Tapi Call
2710 Okl_Rsp_Pvt.lock_row(
2711 p_api_version,
2712 p_init_msg_list,
2713 x_return_status,
2714 x_msg_count,
2715 x_msg_data,
2716 p_rps_tbl);
2717
2718 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2719 RAISE G_EXCEPTION_HALT_VALIDATION;
2720 ELSE
2721 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2722 l_return_status := x_return_status;
2723 END IF;
2724 END IF;
2725
2726 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2727
2728 EXCEPTION
2729 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2730 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2731 l_api_name,
2732 G_PKG_NAME,
2733 'OKL_API.G_RET_STS_ERROR',
2734 x_msg_count,
2735 x_msg_data,
2736 '_PVT');
2737
2738 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2739 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2740 l_api_name,
2741 G_PKG_NAME,
2742 'OKL_API.G_RET_STS_UNEXP_ERROR',
2743 x_msg_count,
2744 x_msg_data,
2745 '_PVT');
2746
2747 WHEN OTHERS THEN
2748 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2749 l_api_name,
2750 G_PKG_NAME,
2751 'OTHERS',
2752 x_msg_count,
2753 x_msg_data,
2754 '_PVT');
2755
2756 END lock_report_strm_parameters;
2757
2758 PROCEDURE create_report_trx_parameters(
2759 p_api_version IN NUMBER,
2760 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2761 x_return_status OUT NOCOPY VARCHAR2,
2762 x_msg_count OUT NOCOPY NUMBER,
2763 x_msg_data OUT NOCOPY VARCHAR2,
2764 p_rtp_rec IN rtp_rec_type,
2765 x_rtp_rec OUT NOCOPY rtp_rec_type
2766 ) IS
2767
2768 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2769 l_api_name VARCHAR2(30) := 'CREATE_REPORT_TRX_PARAMS';
2770
2771 BEGIN
2772 -- Call start_activity to create savepoint, check compatibility
2773 -- and initialize message list
2774 l_return_status := OKL_API.START_ACTIVITY (
2775 l_api_name,
2776 p_init_msg_list,
2777 '_PVT',
2778 l_return_status);
2779
2780 -- Check if activity started successfully
2781 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2782 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2783 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2784 RAISE OKL_API.G_EXCEPTION_ERROR;
2785 END IF;
2786
2787 -- Tapi Call
2788 Okl_Rtp_Pvt.insert_row(
2789 p_api_version,
2790 p_init_msg_list,
2791 x_return_status,
2792 x_msg_count,
2793 x_msg_data,
2794 p_rtp_rec,
2795 x_rtp_rec);
2796
2797 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2798 RAISE G_EXCEPTION_HALT_VALIDATION;
2799 ELSE
2800 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2801 l_return_status := x_return_status;
2802 END IF;
2803 END IF;
2804
2805 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2806
2807 EXCEPTION
2808 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2809 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2810 l_api_name,
2811 G_PKG_NAME,
2812 'OKL_API.G_RET_STS_ERROR',
2813 x_msg_count,
2814 x_msg_data,
2815 '_PVT');
2816
2817 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2818 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2819 l_api_name,
2820 G_PKG_NAME,
2821 'OKL_API.G_RET_STS_UNEXP_ERROR',
2822 x_msg_count,
2823 x_msg_data,
2824 '_PVT');
2825
2826 WHEN OTHERS THEN
2827 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2828 l_api_name,
2829 G_PKG_NAME,
2830 'OTHERS',
2831 x_msg_count,
2832 x_msg_data,
2833 '_PVT');
2834
2835 END create_report_trx_parameters;
2836
2837 PROCEDURE update_report_trx_parameters(
2838 p_api_version IN NUMBER,
2839 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2840 x_return_status OUT NOCOPY VARCHAR2,
2841 x_msg_count OUT NOCOPY NUMBER,
2842 x_msg_data OUT NOCOPY VARCHAR2,
2843 p_rtp_rec IN rtp_rec_type,
2844 x_rtp_rec OUT NOCOPY rtp_rec_type
2845 ) IS
2846
2847 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2848 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_TRX_PARAMS';
2849
2850 BEGIN
2851 -- Call start_activity to create savepoint, check compatibility
2852 -- and initialize message list
2853 l_return_status := OKL_API.START_ACTIVITY (
2854 l_api_name,
2855 p_init_msg_list,
2856 '_PVT',
2857 l_return_status);
2858
2859 -- Check if activity started successfully
2860 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2861 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2862 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2863 RAISE OKL_API.G_EXCEPTION_ERROR;
2864 END IF;
2865
2866 -- Tapi Call
2867 Okl_Rtp_Pvt.update_row(
2868 p_api_version,
2869 p_init_msg_list,
2870 x_return_status,
2871 x_msg_count,
2872 x_msg_data,
2873 p_rtp_rec,
2874 x_rtp_rec);
2875
2876 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2877 RAISE G_EXCEPTION_HALT_VALIDATION;
2878 ELSE
2879 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2880 l_return_status := x_return_status;
2881 END IF;
2882 END IF;
2883
2884 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2885
2886 EXCEPTION
2887 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2888 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2889 l_api_name,
2890 G_PKG_NAME,
2891 'OKL_API.G_RET_STS_ERROR',
2892 x_msg_count,
2893 x_msg_data,
2894 '_PVT');
2895
2896 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2897 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2898 l_api_name,
2899 G_PKG_NAME,
2900 'OKL_API.G_RET_STS_UNEXP_ERROR',
2901 x_msg_count,
2902 x_msg_data,
2903 '_PVT');
2904
2905 WHEN OTHERS THEN
2906 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2907 l_api_name,
2908 G_PKG_NAME,
2909 'OTHERS',
2910 x_msg_count,
2911 x_msg_data,
2912 '_PVT');
2913
2914 END update_report_trx_parameters;
2915
2916 PROCEDURE delete_report_trx_parameters(
2917 p_api_version IN NUMBER,
2918 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2919 x_return_status OUT NOCOPY VARCHAR2,
2920 x_msg_count OUT NOCOPY NUMBER,
2921 x_msg_data OUT NOCOPY VARCHAR2,
2922 p_rtp_rec IN rtp_rec_type
2923 ) IS
2924
2925 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2926 l_api_name VARCHAR2(30) := 'DELETE_REPORT_TRX_PARAMS';
2927
2928 BEGIN
2929 -- Call start_activity to create savepoint, check compatibility
2930 -- and initialize message list
2931 l_return_status := OKL_API.START_ACTIVITY (
2932 l_api_name,
2933 p_init_msg_list,
2934 '_PVT',
2935 l_return_status);
2936
2937 -- Check if activity started successfully
2938 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2940 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2941 RAISE OKL_API.G_EXCEPTION_ERROR;
2942 END IF;
2943
2944 -- Tapi Call
2945 Okl_Rtp_Pvt.delete_row(
2946 p_api_version,
2947 p_init_msg_list,
2948 x_return_status,
2949 x_msg_count,
2950 x_msg_data,
2951 p_rtp_rec);
2952
2953 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
2954 RAISE G_EXCEPTION_HALT_VALIDATION;
2955 ELSE
2956 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
2957 l_return_status := x_return_status;
2958 END IF;
2959 END IF;
2960
2961 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
2962
2963 EXCEPTION
2964 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2965 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2966 l_api_name,
2967 G_PKG_NAME,
2968 'OKL_API.G_RET_STS_ERROR',
2969 x_msg_count,
2970 x_msg_data,
2971 '_PVT');
2972
2973 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2974 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2975 l_api_name,
2976 G_PKG_NAME,
2977 'OKL_API.G_RET_STS_UNEXP_ERROR',
2978 x_msg_count,
2979 x_msg_data,
2980 '_PVT');
2981
2982 WHEN OTHERS THEN
2983 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2984 l_api_name,
2985 G_PKG_NAME,
2986 'OTHERS',
2987 x_msg_count,
2988 x_msg_data,
2989 '_PVT');
2990
2991 END delete_report_trx_parameters;
2992
2993 PROCEDURE lock_report_trx_parameters(
2994 p_api_version IN NUMBER,
2995 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
2996 x_return_status OUT NOCOPY VARCHAR2,
2997 x_msg_count OUT NOCOPY NUMBER,
2998 x_msg_data OUT NOCOPY VARCHAR2,
2999 p_rtp_rec IN rtp_rec_type
3000 ) IS
3001
3002 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3003 l_api_name VARCHAR2(30) := 'LOCK_REPORT_TRX_PARAMS';
3004
3005 BEGIN
3006 -- Call start_activity to create savepoint, check compatibility
3007 -- and initialize message list
3008 l_return_status := OKL_API.START_ACTIVITY (
3009 l_api_name,
3010 p_init_msg_list,
3011 '_PVT',
3012 l_return_status);
3013
3014 -- Check if activity started successfully
3015 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3016 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3017 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3018 RAISE OKL_API.G_EXCEPTION_ERROR;
3019 END IF;
3020
3021 -- Tapi Call
3022 Okl_Rtp_Pvt.lock_row(
3023 p_api_version,
3024 p_init_msg_list,
3025 x_return_status,
3026 x_msg_count,
3027 x_msg_data,
3028 p_rtp_rec);
3029
3030 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3031 RAISE G_EXCEPTION_HALT_VALIDATION;
3032 ELSE
3033 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3034 l_return_status := x_return_status;
3035 END IF;
3036 END IF;
3037
3038 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3039
3040 EXCEPTION
3041 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3042 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3043 l_api_name,
3044 G_PKG_NAME,
3045 'OKL_API.G_RET_STS_ERROR',
3046 x_msg_count,
3047 x_msg_data,
3048 '_PVT');
3049
3050 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3051 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3052 l_api_name,
3053 G_PKG_NAME,
3054 'OKL_API.G_RET_STS_UNEXP_ERROR',
3055 x_msg_count,
3056 x_msg_data,
3057 '_PVT');
3058
3059 WHEN OTHERS THEN
3060 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3061 l_api_name,
3062 G_PKG_NAME,
3063 'OTHERS',
3064 x_msg_count,
3065 x_msg_data,
3066 '_PVT');
3067
3068 END lock_report_trx_parameters;
3069
3070 PROCEDURE create_report_trx_parameters(
3071 p_api_version IN NUMBER,
3072 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3073 x_return_status OUT NOCOPY VARCHAR2,
3074 x_msg_count OUT NOCOPY NUMBER,
3075 x_msg_data OUT NOCOPY VARCHAR2,
3076 p_rtp_tbl IN rtp_tbl_type,
3077 x_rtp_tbl OUT NOCOPY rtp_tbl_type
3078 ) IS
3079
3080 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3081 l_api_name VARCHAR2(30) := 'CREATE_REPORT_TRX_PARAMS';
3082
3083 BEGIN
3084 -- Call start_activity to create savepoint, check compatibility
3085 -- and initialize message list
3086 l_return_status := OKL_API.START_ACTIVITY (
3087 l_api_name,
3088 p_init_msg_list,
3089 '_PVT',
3090 l_return_status);
3091
3092 -- Check if activity started successfully
3093 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3095 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3096 RAISE OKL_API.G_EXCEPTION_ERROR;
3097 END IF;
3098
3099 -- Tapi Call
3100 Okl_Rtp_Pvt.insert_row(
3101 p_api_version,
3102 p_init_msg_list,
3103 x_return_status,
3104 x_msg_count,
3105 x_msg_data,
3106 p_rtp_tbl,
3107 x_rtp_tbl);
3108
3109 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3110 RAISE G_EXCEPTION_HALT_VALIDATION;
3111 ELSE
3112 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3113 l_return_status := x_return_status;
3114 END IF;
3115 END IF;
3116
3117 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3118
3119 EXCEPTION
3120 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3121 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3122 l_api_name,
3123 G_PKG_NAME,
3124 'OKL_API.G_RET_STS_ERROR',
3125 x_msg_count,
3126 x_msg_data,
3127 '_PVT');
3128
3129 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3130 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3131 l_api_name,
3132 G_PKG_NAME,
3133 'OKL_API.G_RET_STS_UNEXP_ERROR',
3134 x_msg_count,
3135 x_msg_data,
3136 '_PVT');
3137
3138 WHEN OTHERS THEN
3139 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3140 l_api_name,
3141 G_PKG_NAME,
3142 'OTHERS',
3143 x_msg_count,
3144 x_msg_data,
3145 '_PVT');
3146
3147 END create_report_trx_parameters;
3148
3149 PROCEDURE update_report_trx_parameters(
3150 p_api_version IN NUMBER,
3151 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3152 x_return_status OUT NOCOPY VARCHAR2,
3153 x_msg_count OUT NOCOPY NUMBER,
3154 x_msg_data OUT NOCOPY VARCHAR2,
3155 p_rtp_tbl IN rtp_tbl_type,
3156 x_rtp_tbl OUT NOCOPY rtp_tbl_type
3157 ) IS
3158
3159 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3160 l_api_name VARCHAR2(30) := 'UPDATE_REPORT_TRX_PARAMS';
3161
3162 BEGIN
3163 -- Call start_activity to create savepoint, check compatibility
3164 -- and initialize message list
3165 l_return_status := OKL_API.START_ACTIVITY (
3166 l_api_name,
3167 p_init_msg_list,
3168 '_PVT',
3169 l_return_status);
3170
3171 -- Check if activity started successfully
3172 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3173 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3174 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3175 RAISE OKL_API.G_EXCEPTION_ERROR;
3176 END IF;
3177
3178 -- Tapi Call
3179 Okl_Rtp_Pvt.update_row(
3180 p_api_version,
3181 p_init_msg_list,
3182 x_return_status,
3183 x_msg_count,
3184 x_msg_data,
3185 p_rtp_tbl,
3186 x_rtp_tbl);
3187
3188 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3189 RAISE G_EXCEPTION_HALT_VALIDATION;
3190 ELSE
3191 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3192 l_return_status := x_return_status;
3193 END IF;
3194 END IF;
3195
3196 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3197
3198 EXCEPTION
3199 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3200 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3201 l_api_name,
3202 G_PKG_NAME,
3203 'OKL_API.G_RET_STS_ERROR',
3204 x_msg_count,
3205 x_msg_data,
3206 '_PVT');
3207
3208 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3209 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3210 l_api_name,
3211 G_PKG_NAME,
3212 'OKL_API.G_RET_STS_UNEXP_ERROR',
3213 x_msg_count,
3214 x_msg_data,
3215 '_PVT');
3216
3217 WHEN OTHERS THEN
3218 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3219 l_api_name,
3220 G_PKG_NAME,
3221 'OTHERS',
3222 x_msg_count,
3223 x_msg_data,
3224 '_PVT');
3225
3226 END update_report_trx_parameters;
3227
3228 PROCEDURE delete_report_trx_parameters(
3229 p_api_version IN NUMBER,
3230 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3231 x_return_status OUT NOCOPY VARCHAR2,
3232 x_msg_count OUT NOCOPY NUMBER,
3233 x_msg_data OUT NOCOPY VARCHAR2,
3234 p_rtp_tbl IN rtp_tbl_type
3235 ) IS
3236
3237 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3238 l_api_name VARCHAR2(30) := 'DELETE_REPORT_TRX_PARAMS';
3239
3240 BEGIN
3241 -- Call start_activity to create savepoint, check compatibility
3242 -- and initialize message list
3243 l_return_status := OKL_API.START_ACTIVITY (
3244 l_api_name,
3245 p_init_msg_list,
3246 '_PVT',
3247 l_return_status);
3248
3249 -- Check if activity started successfully
3250 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3251 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3252 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3253 RAISE OKL_API.G_EXCEPTION_ERROR;
3254 END IF;
3255
3256 -- Tapi Call
3257 Okl_Rtp_Pvt.delete_row(
3258 p_api_version,
3259 p_init_msg_list,
3260 x_return_status,
3261 x_msg_count,
3262 x_msg_data,
3263 p_rtp_tbl);
3264
3265 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3266 RAISE G_EXCEPTION_HALT_VALIDATION;
3267 ELSE
3268 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3269 l_return_status := x_return_status;
3270 END IF;
3271 END IF;
3272
3273 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3274
3275 EXCEPTION
3276 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3277 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3278 l_api_name,
3279 G_PKG_NAME,
3280 'OKL_API.G_RET_STS_ERROR',
3281 x_msg_count,
3282 x_msg_data,
3283 '_PVT');
3284
3285 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3286 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3287 l_api_name,
3288 G_PKG_NAME,
3289 'OKL_API.G_RET_STS_UNEXP_ERROR',
3290 x_msg_count,
3291 x_msg_data,
3292 '_PVT');
3293
3294 WHEN OTHERS THEN
3295 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3296 l_api_name,
3297 G_PKG_NAME,
3298 'OTHERS',
3299 x_msg_count,
3300 x_msg_data,
3301 '_PVT');
3302
3303 END delete_report_trx_parameters;
3304
3305 PROCEDURE lock_report_trx_parameters(
3306 p_api_version IN NUMBER,
3307 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3308 x_return_status OUT NOCOPY VARCHAR2,
3309 x_msg_count OUT NOCOPY NUMBER,
3310 x_msg_data OUT NOCOPY VARCHAR2,
3311 p_rtp_tbl IN rtp_tbl_type
3312 ) IS
3313
3314 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3315 l_api_name VARCHAR2(30) := 'LOCK_REPORT_TRX_PARAMS';
3316
3317 BEGIN
3318 -- Call start_activity to create savepoint, check compatibility
3319 -- and initialize message list
3320 l_return_status := OKL_API.START_ACTIVITY (
3321 l_api_name,
3322 p_init_msg_list,
3323 '_PVT',
3324 l_return_status);
3325
3326 -- Check if activity started successfully
3327 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3328 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3329 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3330 RAISE OKL_API.G_EXCEPTION_ERROR;
3331 END IF;
3332
3333 -- Tapi Call
3334 Okl_Rtp_Pvt.lock_row(
3335 p_api_version,
3336 p_init_msg_list,
3337 x_return_status,
3338 x_msg_count,
3339 x_msg_data,
3340 p_rtp_tbl);
3341
3342 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3343 RAISE G_EXCEPTION_HALT_VALIDATION;
3344 ELSE
3345 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
3346 l_return_status := x_return_status;
3347 END IF;
3348 END IF;
3349
3350 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3351
3352 EXCEPTION
3353 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3354 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3355 l_api_name,
3356 G_PKG_NAME,
3357 'OKL_API.G_RET_STS_ERROR',
3358 x_msg_count,
3359 x_msg_data,
3360 '_PVT');
3361
3362 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3363 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3364 l_api_name,
3365 G_PKG_NAME,
3366 'OKL_API.G_RET_STS_UNEXP_ERROR',
3367 x_msg_count,
3368 x_msg_data,
3369 '_PVT');
3370
3371 WHEN OTHERS THEN
3372 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
3373 l_api_name,
3374 G_PKG_NAME,
3375 'OTHERS',
3376 x_msg_count,
3377 x_msg_data,
3378 '_PVT');
3379
3380 END lock_report_trx_parameters;
3381
3382 FUNCTION validate_accounts(
3383 p_rap_tbl rap_tbl_type
3384 ,p_coa_id OKL_REPORTS_B.CHART_OF_ACCOUNTS_ID%TYPE) RETURN BOOLEAN IS
3385
3386 l_return_status boolean := true;
3387 l_test_value fnd_flex_values_vl.flex_value%TYPE;
3388 i NUMBER := 0;
3389
3390 CURSOR c_is_valid_nat_acc(
3391 p_coa_id OKL_REPORTS_B.CHART_OF_ACCOUNTS_ID%TYPE
3392 ,p_segment_id OKL_REPORT_ACC_PARAMS.SEGMENT_RANGE_FROM%TYPE) IS
3393 SELECT flex.flex_value Account_Number
3394 FROM fnd_id_flex_segments s,
3395 fnd_segment_attribute_values sav,
3396 fnd_flex_values_vl flex
3397 WHERE s.application_id = 101 -- GL Application ID
3398 AND s.id_flex_code = 'GL#'
3399 AND s.enabled_flag = 'Y'
3400 AND s.application_column_name = sav.application_column_name
3401 AND sav.application_id = 101
3402 AND sav.id_flex_code = 'GL#'
3403 AND sav.id_flex_num = s.id_flex_num
3404 AND sav.attribute_value = 'Y'
3405 AND sav.segment_attribute_type = 'GL_ACCOUNT'
3406 AND s.flex_value_set_id = flex.flex_value_set_id
3407 AND s.id_flex_num = p_coa_id
3408 AND flex.flex_value_id = p_segment_id;
3409
3410 BEGIN
3411 IF (p_rap_tbl.COUNT <= 0) THEN
3412 OKL_API.set_message(p_app_name => G_APP_NAME,
3413 p_msg_name => 'OKL_RPP_ONE_NAT_ACC_MIN');
3414 l_return_status := false;
3415 END IF;
3416
3417 IF (p_rap_tbl.COUNT > 0) THEN
3418 FOR i IN p_rap_tbl.FIRST..p_rap_tbl.LAST LOOP
3419
3420 OPEN c_is_valid_nat_acc(p_coa_id, p_rap_tbl(i).segment_range_from);
3421 FETCH c_is_valid_nat_acc INTO l_test_value;
3422
3423 IF (c_is_valid_nat_acc%NOTFOUND) THEN
3424 OKL_API.set_message(p_app_name => G_APP_NAME,
3425 p_msg_name => 'OKL_RECON_COA_ACC_MISMATCH');
3426 l_return_status := false;
3427 CLOSE c_is_valid_nat_acc;
3428 return l_return_status;
3429 END IF;
3430 CLOSE c_is_valid_nat_acc;
3431
3432 OPEN c_is_valid_nat_acc(p_coa_id, p_rap_tbl(i).segment_range_to);
3433 FETCH c_is_valid_nat_acc INTO l_test_value;
3434
3435 IF (c_is_valid_nat_acc%NOTFOUND) THEN
3436 OKL_API.set_message(p_app_name => G_APP_NAME,
3437 p_msg_name => 'OKL_RECON_COA_ACC_MISMATCH');
3438 l_return_status := false;
3439 CLOSE c_is_valid_nat_acc;
3440 return l_return_status;
3441 END IF;
3442 CLOSE c_is_valid_nat_acc;
3443
3444 END LOOP;
3445 END IF;
3446 return l_return_status;
3447 END;
3448
3449 FUNCTION validate_transactions(
3450 p_rtp_tbl rtp_tbl_type) RETURN BOOLEAN IS
3451
3452 l_return_status boolean := true;
3453 l_test_value NUMBER := 0;
3454 i NUMBER := 0;
3455
3456 BEGIN
3457 IF (p_rtp_tbl.COUNT <= 0) THEN
3458 OKL_API.set_message(p_app_name => G_APP_NAME,
3459 p_msg_name => 'OKL_RPP_ONE_TRY_ID_MIN');
3460 l_return_status := false;
3461 END IF;
3462 return l_return_status;
3463 END;
3464
3465 FUNCTION validate_streams(
3466 p_rps_tbl rps_tbl_type) RETURN BOOLEAN IS
3467
3468 l_return_status boolean := true;
3469 l_test_value NUMBER := 0;
3470 i NUMBER := 0;
3471
3472 BEGIN
3473 IF (p_rps_tbl.COUNT <= 0) THEN
3474 OKL_API.set_message(p_app_name => G_APP_NAME,
3475 p_msg_name => 'OKL_RPP_ONE_STY_ID_MIN');
3476 l_return_status := false;
3477 END IF;
3478 return l_return_status;
3479 END;
3480
3481 PROCEDURE ident_rpp_tbl(
3482 p_rpp_tbl IN rpp_tbl_type,
3483 x_crt_rpp_tbl OUT NOCOPY rpp_tbl_type,
3484 x_upd_rpp_tbl OUT NOCOPY rpp_tbl_type,
3485 x_del_rpp_tbl OUT NOCOPY rpp_tbl_type) IS
3486
3487 CURSOR c_is_record_exists(p_param_id OKL_REPORT_PARAMETERS.PARAMETER_ID%TYPE) IS
3488 SELECT 1
3489 FROM OKL_REPORT_PARAMETERS
3490 WHERE PARAMETER_ID = p_param_id;
3491
3492 i NUMBER := 0;
3493 crt_idx NUMBER := 0;
3494 upd_idx NUMBER := 0;
3495 del_idx NUMBER := 0;
3496 l_test_value NUMBER := 0;
3497 l_vld_value boolean := true;
3498
3499 BEGIN
3500
3501 FOR i IN p_rpp_tbl.FIRST..p_rpp_tbl.LAST LOOP
3502 IF ((p_rpp_tbl(i).parameter_id IS NULL OR p_rpp_tbl(i).parameter_id = OKL_API.G_MISS_NUM)
3503 AND (p_rpp_tbl(i).param_num_value1 IS NOT NULL OR p_rpp_tbl(i).param_char_value1 IS NOT NULL))THEN
3504 x_crt_rpp_tbl(crt_idx) := p_rpp_tbl(i);
3505 crt_idx := crt_idx + 1;
3506 ELSE
3507 IF (p_rpp_tbl(i).parameter_type IN ('OPERATING_UNIT','LEGAL_ENTITY') AND
3508 p_rpp_tbl(i).parameter_id IS NOT NULL AND
3509 (p_rpp_tbl(i).param_num_value1 IS NULL OR p_rpp_tbl(i).param_num_value1 = OKL_API.G_MISS_NUM)) THEN
3510
3511 OPEN c_is_record_exists(p_rpp_tbl(i).parameter_id);
3512
3513 FETCH c_is_record_exists INTO l_test_value;
3514
3515 IF (c_is_record_exists%FOUND) THEN
3516 x_del_rpp_tbl(del_idx) := p_rpp_tbl(i);
3517 del_idx := del_idx + 1;
3518 END IF;
3519 CLOSE c_is_record_exists;
3520 ELSIF (p_rpp_tbl(i).parameter_id IS NOT NULL AND
3521 (p_rpp_tbl(i).param_num_value1 IS NOT NULL OR
3522 p_rpp_tbl(i).param_char_value1 IS NOT NULL)) THEN
3523 OPEN c_is_record_exists(p_rpp_tbl(i).parameter_id);
3524
3525 FETCH c_is_record_exists INTO l_test_value;
3526
3527 IF (c_is_record_exists%FOUND) THEN
3528 x_upd_rpp_tbl(upd_idx) := p_rpp_tbl(i);
3529 upd_idx := upd_idx + 1;
3530 ELSE
3531 x_crt_rpp_tbl(crt_idx) := p_rpp_tbl(i);
3532 crt_idx := crt_idx + 1;
3533 END IF;
3534 CLOSE c_is_record_exists;
3535 END IF;
3536 END IF;
3537 END LOOP;
3538
3539 END ident_rpp_tbl;
3540
3541 PROCEDURE ident_rap_tbl(
3542 p_rap_tbl IN rap_tbl_type,
3543 x_crt_rap_tbl OUT NOCOPY rap_tbl_type,
3544 x_upd_rap_tbl OUT NOCOPY rap_tbl_type) IS
3545
3546 CURSOR c_is_record_exists(p_acc_param_id OKL_REPORT_ACC_PARAMS.ACC_PARAMETER_ID%TYPE) IS
3547 SELECT 1
3548 FROM OKL_REPORT_ACC_PARAMS
3549 WHERE ACC_PARAMETER_ID = p_acc_param_id;
3550
3551 i NUMBER := 0;
3552 crt_idx NUMBER := 0;
3553 upd_idx NUMBER := 0;
3554 l_test_value NUMBER := 0;
3555 l_vld_value boolean := true;
3556
3557 BEGIN
3558
3559 FOR i IN p_rap_tbl.FIRST..p_rap_tbl.LAST LOOP
3560 IF (p_rap_tbl(i).acc_parameter_id IS NULL OR p_rap_tbl(i).acc_parameter_id = OKL_API.G_MISS_NUM) THEN
3561 x_crt_rap_tbl(crt_idx) := p_rap_tbl(i);
3562 crt_idx := crt_idx + 1;
3563 ELSE
3564 IF (p_rap_tbl(i).acc_parameter_id IS NOT NULL) THEN
3565
3566 OPEN c_is_record_exists(p_rap_tbl(i).acc_parameter_id);
3567
3568 FETCH c_is_record_exists INTO l_test_value;
3569
3570 IF (c_is_record_exists%FOUND) THEN
3571 x_upd_rap_tbl(upd_idx) := p_rap_tbl(i);
3572 upd_idx := upd_idx + 1;
3573 ELSE
3574 x_crt_rap_tbl(crt_idx) := p_rap_tbl(i);
3575 crt_idx := crt_idx + 1;
3576 END IF;
3577 CLOSE c_is_record_exists;
3578 END IF;
3579 END IF;
3580 END LOOP;
3581
3582 END ident_rap_tbl;
3583
3584 PROCEDURE ident_rps_tbl(
3585 p_rps_tbl IN rps_tbl_type,
3586 x_crt_rps_tbl OUT NOCOPY rps_tbl_type,
3587 x_upd_rps_tbl OUT NOCOPY rps_tbl_type) IS
3588
3589 CURSOR c_is_record_exists(p_strm_param_id OKL_REPORT_STREAM_PARAMS.STREAM_PARAMETER_ID%TYPE) IS
3590 SELECT 1
3591 FROM OKL_REPORT_STREAM_PARAMS
3592 WHERE STREAM_PARAMETER_ID = p_strm_param_id;
3593
3594 i NUMBER := 0;
3595 crt_idx NUMBER := 0;
3596 upd_idx NUMBER := 0;
3597 l_test_value NUMBER := 0;
3598 l_vld_value boolean := true;
3599
3600 BEGIN
3601
3602 FOR i IN p_rps_tbl.FIRST..p_rps_tbl.LAST LOOP
3603 IF (p_rps_tbl(i).stream_parameter_id IS NULL OR p_rps_tbl(i).stream_parameter_id = OKL_API.G_MISS_NUM) THEN
3604 x_crt_rps_tbl(crt_idx) := p_rps_tbl(i);
3605 crt_idx := crt_idx + 1;
3606 ELSE
3607 IF (p_rps_tbl(i).stream_parameter_id IS NOT NULL) THEN
3608
3609 OPEN c_is_record_exists(p_rps_tbl(i).stream_parameter_id);
3610
3611 FETCH c_is_record_exists INTO l_test_value;
3612
3613 IF (c_is_record_exists%FOUND) THEN
3614 x_upd_rps_tbl(upd_idx) := p_rps_tbl(i);
3615 upd_idx := upd_idx + 1;
3616 ELSE
3617 x_crt_rps_tbl(crt_idx) := p_rps_tbl(i);
3618 crt_idx := crt_idx + 1;
3619 END IF;
3620 CLOSE c_is_record_exists;
3621 END IF;
3622 END IF;
3623 END LOOP;
3624
3625 END ident_rps_tbl;
3626
3627 PROCEDURE ident_rtp_tbl(
3628 p_rtp_tbl IN rtp_tbl_type,
3629 x_crt_rtp_tbl OUT NOCOPY rtp_tbl_type,
3630 x_upd_rtp_tbl OUT NOCOPY rtp_tbl_type) IS
3631
3632 CURSOR c_is_record_exists(p_trx_param_id OKL_REPORT_TRX_PARAMS.TRX_PARAMETER_ID%TYPE) IS
3633 SELECT 1
3634 FROM OKL_REPORT_TRX_PARAMS
3635 WHERE TRX_PARAMETER_ID = p_trx_param_id;
3636
3637 i NUMBER := 0;
3638 crt_idx NUMBER := 0;
3639 upd_idx NUMBER := 0;
3640 l_test_value NUMBER := 0;
3641 l_vld_value boolean := true;
3642
3643 BEGIN
3644
3645 FOR i IN p_rtp_tbl.FIRST..p_rtp_tbl.LAST LOOP
3646 IF ((p_rtp_tbl(i).trx_parameter_id IS NULL OR p_rtp_tbl(i).trx_parameter_id = OKL_API.G_MISS_NUM)
3647 AND p_rtp_tbl(i).try_id IS NOT NULL )THEN
3648 x_crt_rtp_tbl(crt_idx) := p_rtp_tbl(i);
3649 crt_idx := crt_idx + 1;
3650 ELSE
3651 IF (p_rtp_tbl(i).trx_parameter_id IS NOT NULL
3652 AND p_rtp_tbl(i).try_id IS NOT NULL) THEN
3653
3654 OPEN c_is_record_exists(p_rtp_tbl(i).trx_parameter_id);
3655
3656 FETCH c_is_record_exists INTO l_test_value;
3657
3658 IF (c_is_record_exists%FOUND) THEN
3659 x_upd_rtp_tbl(upd_idx) := p_rtp_tbl(i);
3660 upd_idx := upd_idx + 1;
3661 ELSE
3662 x_crt_rtp_tbl(crt_idx) := p_rtp_tbl(i);
3663 crt_idx := crt_idx + 1;
3664 END IF;
3665 CLOSE c_is_record_exists;
3666 END IF;
3667 END IF;
3668 END LOOP;
3669
3670 END ident_rtp_tbl;
3671
3672 PROCEDURE merge_x_rpp_tbl(
3673 p_crt_rpp_tbl IN rpp_tbl_type,
3674 p_upd_rpp_tbl IN rpp_tbl_type,
3675 x_rpp_tbl OUT NOCOPY rpp_tbl_type
3676 ) IS
3677 crt_idx NUMBER := 0;
3678 upd_idx NUMBER := 0;
3679 i NUMBER := 0;
3680
3681 BEGIN
3682
3683 IF (p_crt_rpp_tbl.COUNT > 0) THEN
3684 FOR crt_idx IN p_crt_rpp_tbl.FIRST..p_crt_rpp_tbl.LAST LOOP
3685 x_rpp_tbl(i) := p_crt_rpp_tbl(crt_idx);
3686 i := i+1;
3687 END LOOP;
3688 END IF;
3689
3690 IF (p_upd_rpp_tbl.COUNT > 0) THEN
3691 FOR upd_idx IN p_upd_rpp_tbl.FIRST..p_upd_rpp_tbl.LAST LOOP
3692 x_rpp_tbl(i) := p_upd_rpp_tbl(upd_idx);
3693 i := i+1;
3694 END LOOP;
3695 END IF;
3696
3697 END merge_x_rpp_tbl;
3698
3699 PROCEDURE merge_x_rap_tbl(
3700 p_crt_rap_tbl IN rap_tbl_type,
3701 p_upd_rap_tbl IN rap_tbl_type,
3702 x_rap_tbl OUT NOCOPY rap_tbl_type
3703 ) IS
3704 crt_idx NUMBER := 0;
3705 upd_idx NUMBER := 0;
3706 i NUMBER := 0;
3707
3708 BEGIN
3709
3710 IF (p_crt_rap_tbl.COUNT > 0) THEN
3711 FOR crt_idx IN p_crt_rap_tbl.FIRST..p_crt_rap_tbl.LAST LOOP
3712 x_rap_tbl(i) := p_crt_rap_tbl(crt_idx);
3713 i := i+1;
3714 END LOOP;
3715 END IF;
3716
3717 IF (p_upd_rap_tbl.COUNT > 0) THEN
3718 FOR upd_idx IN p_upd_rap_tbl.FIRST..p_upd_rap_tbl.LAST LOOP
3719 x_rap_tbl(i) := p_upd_rap_tbl(upd_idx);
3720 i := i+1;
3721 END LOOP;
3722 END IF;
3723
3724 END merge_x_rap_tbl;
3725
3726 PROCEDURE merge_x_rps_tbl(
3727 p_crt_rps_tbl IN rps_tbl_type,
3728 p_upd_rps_tbl IN rps_tbl_type,
3729 x_rps_tbl OUT NOCOPY rps_tbl_type
3730 ) IS
3731 crt_idx NUMBER := 0;
3732 upd_idx NUMBER := 0;
3733 i NUMBER := 0;
3734
3735 BEGIN
3736
3737 IF (p_crt_rps_tbl.COUNT > 0) THEN
3738 FOR crt_idx IN p_crt_rps_tbl.FIRST..p_crt_rps_tbl.LAST LOOP
3739 x_rps_tbl(i) := p_crt_rps_tbl(crt_idx);
3740 i := i+1;
3741 END LOOP;
3742 END IF;
3743
3744 IF (p_upd_rps_tbl.COUNT > 0) THEN
3745 FOR upd_idx IN p_upd_rps_tbl.FIRST..p_upd_rps_tbl.LAST LOOP
3746 x_rps_tbl(i) := p_upd_rps_tbl(upd_idx);
3747 i := i+1;
3748 END LOOP;
3749 END IF;
3750
3751 END merge_x_rps_tbl;
3752
3753 PROCEDURE merge_x_rtp_tbl(
3754 p_crt_rtp_tbl IN rtp_tbl_type,
3755 p_upd_rtp_tbl IN rtp_tbl_type,
3756 x_rtp_tbl OUT NOCOPY rtp_tbl_type
3757 ) IS
3758 crt_idx NUMBER := 0;
3759 upd_idx NUMBER := 0;
3760 i NUMBER := 0;
3761
3762 BEGIN
3763
3764 IF (p_crt_rtp_tbl.COUNT > 0) THEN
3765 FOR crt_idx IN p_crt_rtp_tbl.FIRST..p_crt_rtp_tbl.LAST LOOP
3766 x_rtp_tbl(i) := p_crt_rtp_tbl(crt_idx);
3767 i := i+1;
3768 END LOOP;
3769 END IF;
3770
3771 IF (p_upd_rtp_tbl.COUNT > 0) THEN
3772 FOR upd_idx IN p_upd_rtp_tbl.FIRST..p_upd_rtp_tbl.LAST LOOP
3773 x_rtp_tbl(i) := p_upd_rtp_tbl(upd_idx);
3774 i := i+1;
3775 END LOOP;
3776 END IF;
3777
3778 END merge_x_rtp_tbl;
3779
3780 PROCEDURE create_report(
3781 p_api_version IN NUMBER,
3782 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3783 x_return_status OUT NOCOPY VARCHAR2,
3784 x_msg_count OUT NOCOPY NUMBER,
3785 x_msg_data OUT NOCOPY VARCHAR2,
3786 p_repv_rec IN repv_rec_type,
3787 x_repv_rec OUT NOCOPY repv_rec_type,
3788 p_rpp_tbl IN rpp_tbl_type,
3789 x_rpp_tbl OUT NOCOPY rpp_tbl_type,
3790 p_rap_tbl IN rap_tbl_type,
3791 x_rap_tbl OUT NOCOPY rap_tbl_type,
3792 p_rps_tbl IN rps_tbl_type,
3793 x_rps_tbl OUT NOCOPY rps_tbl_type,
3794 p_rtp_tbl IN rtp_tbl_type,
3795 x_rtp_tbl OUT NOCOPY rtp_tbl_type
3796 ) IS
3797
3798 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3799 l_api_name VARCHAR2(30) := 'CREATE_REPORT';
3800 i NUMBER := 0;
3801 l_book_class_code OKL_REPORTS_B.BOOK_CLASSIFICATION_CODE%TYPE;
3802 l_coa_id OKL_REPORTS_B.CHART_OF_ACCOUNTS_ID%TYPE;
3803 l_report_id OKL_REPORTS_B.REPORT_ID%TYPE;
3804 l_func_ret_value boolean := true;
3805 la_func_ret_value boolean := true;
3806 lt_func_ret_value boolean := true;
3807 ls_func_ret_value boolean := true;
3808 l_repv_rec repv_rec_type := p_repv_rec;
3809
3810 l_rps_tbl rps_tbl_type := p_rps_tbl;
3811 l_rpp_tbl rpp_tbl_type := p_rpp_tbl;
3812 l_rap_tbl rap_tbl_type := p_rap_tbl;
3813 l_rtp_tbl rtp_tbl_type := p_rtp_tbl;
3814
3815 l_crt_rpp_tbl rpp_tbl_type;
3816 x_crt_rpp_tbl rpp_tbl_type;
3817 x_upd_rpp_tbl rpp_tbl_type;
3818 x_del_rpp_tbl rpp_tbl_type;
3819
3820 l_crt_rtp_tbl rtp_tbl_type;
3821 l_upd_rtp_tbl rtp_tbl_type;
3822 x_crt_rtp_tbl rtp_tbl_type;
3823 x_upd_rtp_tbl rtp_tbl_type;
3824
3825 BEGIN
3826 -- Call start_activity to create savepoint, check compatibility
3827 -- and initialize message list
3828 l_return_status := OKL_API.START_ACTIVITY (
3829 l_api_name,
3830 p_init_msg_list,
3831 '_PVT',
3832 l_return_status);
3833
3834 -- Check if activity started successfully
3835 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3836 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3837 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3838 RAISE OKL_API.G_EXCEPTION_ERROR;
3839 END IF;
3840
3841 IF (l_repv_rec.effective_from_date IS NULL OR l_repv_rec.effective_from_date = OKL_API.G_MISS_DATE) THEN
3842 l_repv_rec.effective_from_date := SYSDATE;
3843 END IF;
3844
3845 create_report(
3846 p_api_version,
3847 p_init_msg_list,
3848 x_return_status,
3849 x_msg_count,
3850 x_msg_data,
3851 l_repv_rec,
3852 x_repv_rec);
3853
3854 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3855 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3856 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3857 RAISE OKL_API.G_EXCEPTION_ERROR;
3858 ELSIF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
3859 l_book_class_code := x_repv_rec.book_classification_code;
3860 l_coa_id := x_repv_rec.chart_of_accounts_id;
3861 l_report_id := x_repv_rec.report_id;
3862 END IF;
3863
3864 -- validation calls
3865 la_func_ret_value := validate_accounts(p_rap_tbl, l_coa_id);
3866 lt_func_ret_value := validate_transactions(p_rtp_tbl);
3867 ls_func_ret_value := validate_streams(p_rps_tbl);
3868
3869 IF (la_func_ret_value = true AND lt_func_ret_value = true AND ls_func_ret_value = true) THEN
3870
3871 IF (l_rpp_tbl.COUNT > 0) THEN
3872
3873 FOR i IN l_rpp_tbl.FIRST..l_rpp_tbl.LAST LOOP
3874 l_rpp_tbl(i).report_id := l_report_id;
3875 END LOOP;
3876
3877 ident_rpp_tbl(
3878 l_rpp_tbl,
3879 x_crt_rpp_tbl,
3880 x_upd_rpp_tbl,
3881 x_del_rpp_tbl
3882 );
3883
3884 l_crt_rpp_tbl := x_crt_rpp_tbl;
3885
3886 IF (l_crt_rpp_tbl.COUNT > 0) THEN
3887
3888 create_report_parameters(
3889 p_api_version,
3890 p_init_msg_list,
3891 x_return_status,
3892 x_msg_count,
3893 x_msg_data,
3894 l_crt_rpp_tbl,
3895 x_crt_rpp_tbl);
3896
3897 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3899 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3900 RAISE OKL_API.G_EXCEPTION_ERROR;
3901 END IF;
3902 END IF;
3903 END IF;
3904
3905 IF (l_rap_tbl.COUNT > 0) THEN
3906
3907 FOR i IN l_rap_tbl.FIRST..l_rap_tbl.LAST LOOP
3908 l_rap_tbl(i).report_id := l_report_id;
3909 END LOOP;
3910
3911 create_report_acc_parameters(
3912 p_api_version,
3913 p_init_msg_list,
3914 x_return_status,
3915 x_msg_count,
3916 x_msg_data,
3917 l_rap_tbl,
3918 x_rap_tbl);
3919
3920 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3921 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3922 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3923 RAISE OKL_API.G_EXCEPTION_ERROR;
3924 END IF;
3925 END IF;
3926
3927 IF (l_rps_tbl.COUNT > 0) THEN
3928
3929 FOR i IN l_rps_tbl.FIRST..l_rps_tbl.LAST LOOP
3930 l_rps_tbl(i).report_id := l_report_id;
3931 END LOOP;
3932
3933 create_report_strm_parameters(
3934 p_api_version,
3935 p_init_msg_list,
3936 x_return_status,
3937 x_msg_count,
3938 x_msg_data,
3939 l_rps_tbl,
3940 x_rps_tbl);
3941
3942 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3943 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3944 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3945 RAISE OKL_API.G_EXCEPTION_ERROR;
3946 END IF;
3947 END IF;
3948
3949 IF (l_rtp_tbl.COUNT > 0) THEN
3950
3951 FOR i IN l_rtp_tbl.FIRST..l_rtp_tbl.LAST LOOP
3952 l_rtp_tbl(i).report_id := l_report_id;
3953 END LOOP;
3954
3955 ident_rtp_tbl(
3956 l_rtp_tbl,
3957 x_crt_rtp_tbl,
3958 x_upd_rtp_tbl
3959 );
3960
3961 l_crt_rtp_tbl := x_crt_rtp_tbl;
3962
3963 IF (l_crt_rtp_tbl.COUNT <= 0) THEN
3964 OKL_API.set_message(p_app_name => G_APP_NAME,
3965 p_msg_name => 'OKL_RPP_ONE_TRY_ID_MIN');
3966
3967 x_return_status := OKL_API.G_RET_STS_ERROR;
3968 RAISE OKL_API.G_EXCEPTION_ERROR;
3969 ELSE
3970 create_report_trx_parameters(
3971 p_api_version,
3972 p_init_msg_list,
3973 x_return_status,
3974 x_msg_count,
3975 x_msg_data,
3976 l_crt_rtp_tbl,
3977 x_rtp_tbl);
3978
3979 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3980 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3981 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3982 RAISE OKL_API.G_EXCEPTION_ERROR;
3983 END IF;
3984 END IF;
3985
3986 END IF;
3987 ELSE
3988 x_return_status := OKL_API.G_RET_STS_ERROR;
3989 RAISE OKL_API.G_EXCEPTION_ERROR;
3990 END IF;
3991
3992 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
3993
3994 EXCEPTION
3995 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3996 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3997 l_api_name,
3998 G_PKG_NAME,
3999 'OKL_API.G_RET_STS_ERROR',
4000 x_msg_count,
4001 x_msg_data,
4002 '_PVT');
4003
4004 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4005 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4006 l_api_name,
4007 G_PKG_NAME,
4008 'OKL_API.G_RET_STS_UNEXP_ERROR',
4009 x_msg_count,
4010 x_msg_data,
4011 '_PVT');
4012
4013 WHEN OTHERS THEN
4014 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4015 l_api_name,
4016 G_PKG_NAME,
4017 'OTHERS',
4018 x_msg_count,
4019 x_msg_data,
4020 '_PVT');
4021
4022 END create_report;
4023
4024 PROCEDURE update_report(
4025 p_api_version IN NUMBER,
4026 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4027 x_return_status OUT NOCOPY VARCHAR2,
4028 x_msg_count OUT NOCOPY NUMBER,
4029 x_msg_data OUT NOCOPY VARCHAR2,
4030 p_repv_rec IN repv_rec_type,
4031 x_repv_rec OUT NOCOPY repv_rec_type,
4032 p_rpp_tbl IN rpp_tbl_type,
4033 x_rpp_tbl OUT NOCOPY rpp_tbl_type,
4034 p_rap_tbl IN rap_tbl_type,
4035 x_rap_tbl OUT NOCOPY rap_tbl_type,
4036 p_rps_tbl IN rps_tbl_type,
4037 x_rps_tbl OUT NOCOPY rps_tbl_type,
4038 p_rtp_tbl IN rtp_tbl_type,
4039 x_rtp_tbl OUT NOCOPY rtp_tbl_type
4040 ) IS
4041
4042 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4043 l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
4044 i NUMBER := 0;
4045 l_book_class_code OKL_REPORTS_B.BOOK_CLASSIFICATION_CODE%TYPE;
4046 l_coa_id OKL_REPORTS_B.CHART_OF_ACCOUNTS_ID%TYPE;
4047 l_func_ret_value boolean := true;
4048 la_func_ret_value boolean := true;
4049 lt_func_ret_value boolean := true;
4050 ls_func_ret_value boolean := true;
4051 l_rps_tbl rps_tbl_type := p_rps_tbl;
4052
4053 l_crt_rpp_tbl rpp_tbl_type;
4054 l_upd_rpp_tbl rpp_tbl_type;
4055 l_del_rpp_tbl rpp_tbl_type;
4056 x_crt_rpp_tbl rpp_tbl_type;
4057 x_upd_rpp_tbl rpp_tbl_type;
4058 x_del_rpp_tbl rpp_tbl_type;
4059
4060 l_crt_rps_tbl rps_tbl_type;
4061 l_upd_rps_tbl rps_tbl_type;
4062 x_crt_rps_tbl rps_tbl_type;
4063 x_upd_rps_tbl rps_tbl_type;
4064
4065 l_crt_rap_tbl rap_tbl_type;
4066 l_upd_rap_tbl rap_tbl_type;
4067 x_crt_rap_tbl rap_tbl_type;
4068 x_upd_rap_tbl rap_tbl_type;
4069
4070 l_crt_rtp_tbl rtp_tbl_type;
4071 l_upd_rtp_tbl rtp_tbl_type;
4072 x_crt_rtp_tbl rtp_tbl_type;
4073 x_upd_rtp_tbl rtp_tbl_type;
4074
4075 BEGIN
4076
4077 -- Call start_activity to create savepoint, check compatibility
4078 -- and initialize message list
4079 l_return_status := OKL_API.START_ACTIVITY (
4080 l_api_name,
4081 p_init_msg_list,
4082 '_PVT',
4083 l_return_status);
4084
4085 -- Check if activity started successfully
4086 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4087 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4088 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4089 RAISE OKL_API.G_EXCEPTION_ERROR;
4090 END IF;
4091
4092 update_report(
4093 p_api_version,
4094 p_init_msg_list,
4095 x_return_status,
4096 x_msg_count,
4097 x_msg_data,
4098 p_repv_rec,
4099 x_repv_rec);
4100
4101 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4102 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4103 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4104 RAISE OKL_API.G_EXCEPTION_ERROR;
4105 ELSIF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
4106 l_book_class_code := x_repv_rec.book_classification_code;
4107 l_coa_id := x_repv_rec.chart_of_accounts_id;
4108 END IF;
4109
4110 -- validation calls
4111 la_func_ret_value := validate_accounts(p_rap_tbl, l_coa_id);
4112 lt_func_ret_value := validate_transactions(p_rtp_tbl);
4113 ls_func_ret_value := validate_streams(p_rps_tbl);
4114
4115 IF (la_func_ret_value = true AND lt_func_ret_value = true AND ls_func_ret_value = true) THEN
4116
4117 IF (p_rpp_tbl.COUNT > 0) THEN
4118
4119 ident_rpp_tbl(
4120 p_rpp_tbl,
4121 x_crt_rpp_tbl,
4122 x_upd_rpp_tbl,
4123 x_del_rpp_tbl
4124 );
4125
4126 l_crt_rpp_tbl := x_crt_rpp_tbl;
4127 l_upd_rpp_tbl := x_upd_rpp_tbl;
4128 l_del_rpp_tbl := x_del_rpp_tbl;
4129
4130 IF (l_crt_rpp_tbl.COUNT > 0) THEN
4131
4132 FOR i IN l_crt_rpp_tbl.FIRST..l_crt_rpp_tbl.LAST LOOP
4133 l_crt_rpp_tbl(i).report_id := x_repv_rec.report_id;
4134 END LOOP;
4135
4136 create_report_parameters(
4137 p_api_version,
4138 p_init_msg_list,
4139 x_return_status,
4140 x_msg_count,
4141 x_msg_data,
4142 l_crt_rpp_tbl,
4143 x_crt_rpp_tbl);
4144
4145 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4147 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4148 RAISE OKL_API.G_EXCEPTION_ERROR;
4149 END IF;
4150 END IF;
4151
4152 IF (l_upd_rpp_tbl.COUNT > 0) THEN
4153
4154 update_report_parameters(
4155 p_api_version,
4156 p_init_msg_list,
4157 x_return_status,
4158 x_msg_count,
4159 x_msg_data,
4160 l_upd_rpp_tbl,
4161 x_upd_rpp_tbl);
4162
4163 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4165 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4166 RAISE OKL_API.G_EXCEPTION_ERROR;
4167 END IF;
4168 END IF;
4169
4170 IF (l_del_rpp_tbl.COUNT > 0) THEN
4171
4172 delete_report_parameters(
4173 p_api_version,
4174 p_init_msg_list,
4175 x_return_status,
4176 x_msg_count,
4177 x_msg_data,
4178 l_del_rpp_tbl);
4179
4180 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4181 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4182 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4183 RAISE OKL_API.G_EXCEPTION_ERROR;
4184 END IF;
4185 END IF;
4186
4187 merge_x_rpp_tbl(
4188 x_crt_rpp_tbl,
4189 x_upd_rpp_tbl,
4190 x_rpp_tbl
4191 );
4192
4193 END IF;
4194
4195 IF (p_rap_tbl.COUNT > 0) THEN
4196
4197 ident_rap_tbl(
4198 p_rap_tbl,
4199 x_crt_rap_tbl,
4200 x_upd_rap_tbl
4201 );
4202
4203 l_crt_rap_tbl := x_crt_rap_tbl;
4204 l_upd_rap_tbl := x_upd_rap_tbl;
4205
4206 IF (l_crt_rap_tbl.COUNT > 0) THEN
4207
4208 create_report_acc_parameters(
4209 p_api_version,
4210 p_init_msg_list,
4211 x_return_status,
4212 x_msg_count,
4213 x_msg_data,
4214 l_crt_rap_tbl,
4215 x_crt_rap_tbl);
4216
4217 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4218 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4219 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4220 RAISE OKL_API.G_EXCEPTION_ERROR;
4221 END IF;
4222 END IF;
4223
4224 IF (l_upd_rap_tbl.COUNT > 0) THEN
4225
4226 update_report_acc_parameters(
4227 p_api_version,
4228 p_init_msg_list,
4229 x_return_status,
4230 x_msg_count,
4231 x_msg_data,
4232 l_upd_rap_tbl,
4233 x_upd_rap_tbl);
4234
4235 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4236 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4237 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4238 RAISE OKL_API.G_EXCEPTION_ERROR;
4239 END IF;
4240 END IF;
4241
4242 merge_x_rap_tbl(
4243 x_crt_rap_tbl,
4244 x_upd_rap_tbl,
4245 x_rap_tbl
4246 );
4247
4248 END IF;
4249
4250 IF (p_rps_tbl.COUNT > 0) THEN
4251
4252 ident_rps_tbl(
4253 l_rps_tbl,
4254 x_crt_rps_tbl,
4255 x_upd_rps_tbl
4256 );
4257
4258 l_crt_rps_tbl := x_crt_rps_tbl;
4259 l_upd_rps_tbl := x_upd_rps_tbl;
4260
4261 IF (l_crt_rps_tbl.COUNT > 0) THEN
4262
4263 create_report_strm_parameters(
4264 p_api_version,
4265 p_init_msg_list,
4266 x_return_status,
4267 x_msg_count,
4268 x_msg_data,
4269 l_crt_rps_tbl,
4270 x_crt_rps_tbl);
4271
4272 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4273 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4274 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4275 RAISE OKL_API.G_EXCEPTION_ERROR;
4276 END IF;
4277 END IF;
4278
4279 IF (l_upd_rps_tbl.COUNT > 0) THEN
4280
4281 update_report_strm_parameters(
4282 p_api_version,
4283 p_init_msg_list,
4284 x_return_status,
4285 x_msg_count,
4286 x_msg_data,
4287 l_upd_rps_tbl,
4288 x_upd_rps_tbl);
4289
4290 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4291 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4292 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4293 RAISE OKL_API.G_EXCEPTION_ERROR;
4294 END IF;
4295 END IF;
4296
4297 merge_x_rps_tbl(
4298 x_crt_rps_tbl,
4299 x_upd_rps_tbl,
4300 x_rps_tbl
4301 );
4302
4303 END IF;
4304
4305 IF (p_rtp_tbl.COUNT > 0) THEN
4306
4307 ident_rtp_tbl(
4308 p_rtp_tbl,
4309 x_crt_rtp_tbl,
4310 x_upd_rtp_tbl
4311 );
4312
4313 l_crt_rtp_tbl := x_crt_rtp_tbl;
4314 l_upd_rtp_tbl := x_upd_rtp_tbl;
4315
4316 IF (l_crt_rtp_tbl.COUNT <= 0 AND l_upd_rtp_tbl.COUNT <= 0) THEN
4317
4318 OKL_API.set_message(p_app_name => G_APP_NAME,
4319 p_msg_name => 'OKL_RPP_ONE_TRY_ID_MIN');
4320
4321 x_return_status := OKL_API.G_RET_STS_ERROR;
4322 RAISE OKL_API.G_EXCEPTION_ERROR;
4323 END IF;
4324
4325 IF (l_crt_rtp_tbl.COUNT > 0) THEN
4326
4327 create_report_trx_parameters(
4328 p_api_version,
4329 p_init_msg_list,
4330 x_return_status,
4331 x_msg_count,
4332 x_msg_data,
4333 l_crt_rtp_tbl,
4334 x_crt_rtp_tbl);
4335
4336 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4338 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4339 RAISE OKL_API.G_EXCEPTION_ERROR;
4340 END IF;
4341 END IF;
4342
4343 IF (l_upd_rtp_tbl.COUNT > 0) THEN
4344
4345 update_report_trx_parameters(
4346 p_api_version,
4347 p_init_msg_list,
4348 x_return_status,
4349 x_msg_count,
4350 x_msg_data,
4351 l_upd_rtp_tbl,
4352 x_upd_rtp_tbl);
4353
4354 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4356 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4357 RAISE OKL_API.G_EXCEPTION_ERROR;
4358 END IF;
4359 END IF;
4360
4361 merge_x_rtp_tbl(
4362 x_crt_rtp_tbl,
4363 x_upd_rtp_tbl,
4364 x_rtp_tbl
4365 );
4366
4367 END IF;
4368 ELSE
4369 x_return_status := OKL_API.G_RET_STS_ERROR;
4370 RAISE OKL_API.G_EXCEPTION_ERROR;
4371 END IF;
4372
4373 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
4374
4375 EXCEPTION
4376 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4377 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4378 l_api_name,
4379 G_PKG_NAME,
4380 'OKL_API.G_RET_STS_ERROR',
4381 x_msg_count,
4382 x_msg_data,
4383 '_PVT');
4384
4385 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4386 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4387 l_api_name,
4388 G_PKG_NAME,
4389 'OKL_API.G_RET_STS_UNEXP_ERROR',
4390 x_msg_count,
4391 x_msg_data,
4392 '_PVT');
4393
4394 WHEN OTHERS THEN
4395 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4396 l_api_name,
4397 G_PKG_NAME,
4398 'OTHERS',
4399 x_msg_count,
4400 x_msg_data,
4401 '_PVT');
4402
4403 END update_report;
4404
4405 PROCEDURE insert_gt(p_contract_number IN okc_k_headers_all_b.contract_number%TYPE,
4406 p_party_name in hz_parties.party_name%TYPE,
4407 p_account_number IN hz_cust_accounts.account_number%TYPE ,
4408 p_cust_site_name IN okx_cust_sites_v.description%TYPE,
4409 p_inv_msg IN fnd_new_messages.message_text%TYPE,
4410 p_value IN VARCHAR2
4411
4412 ) IS
4413 -------------------------------------------------------------------------------
4414 -- PROCEDURE insert_gt
4415 -------------------------------------------------------------------------------
4416 -- Start of comments
4417 --
4418 -- Procedure Name : insert_gt
4419 -- Description : Inserts into the global temporary table.
4420 -- Business Rules :
4421 -- Parameters :
4422 -- Version : 1.0
4423 -- History : 31-Jan-2007 schodava created.
4424 -- End of comments
4425 -------------------------------------------------------------------------------
4426
4427 BEGIN
4428 INSERT INTO okl_g_reports_gt
4429 (value1_text,
4430 value2_text,
4431 value3_text,
4432 value4_text,
4433 value5_text,
4434 value6_text)
4435 VALUES
4436 (p_contract_number,
4437 p_party_name,
4438 p_account_number,
4439 p_cust_site_name,
4440 p_inv_msg,
4441 p_value);
4442 END;
4443
4444 FUNCTION pre_billing
4445 RETURN BOOLEAN
4446 -------------------------------------------------------------------------------
4447 -- FUNCTION pre_billing
4448 -------------------------------------------------------------------------------
4449 -- Start of comments
4450 --
4451 -- Procedure Name : pre_billing
4452 -- Description : Function for Pre Billing Report Generation using
4453 -- XML Publisher
4454 -- Business Rules :
4455 -- Parameters :
4456 -- Version : 1.0
4457 -- History : 28-Jan-2007 schodava created.
4458 -- Following are the validations carried out during pre-billing:
4459 -- a. Bank account to be used for billing is disabled
4460 -- b. Payment method to be used for billing is disabled
4461 -- c. Bill to site to be used for billing is incorrect
4462 -- d. GL code combination is invalid
4463
4464 -- HISTORY: 28-Jan-08 schodava Created function
4465 -- 31-Jan-08 schodava Removed sales rep validation,
4466 -- Inserting GL Concatenated segment value into GT table
4467 -- Created a new procedure insert_gt for inserting into gt table
4468 -- Introduced contract line level bank account validation
4469 -- Contract Line Level validation now overrides header level
4470 -- End of comments
4471 -------------------------------------------------------------------------------
4472 IS
4473 -- Streams cursor
4474 CURSOR c_streams IS
4475 SELECT stm.khr_id khr_id,
4476 TRUNC (ste.stream_element_date) bill_date,
4477 stm.kle_id kle_id,
4478 ste.id sel_id,
4479 stm.sty_id sty_id,
4480 khr.contract_number contract_number,
4481 khr.currency_code currency_code,
4482 khr.authoring_org_id authoring_org_id,
4483 sty.name sty_name,
4484 sty.taxable_default_yn taxable_default_yn,
4485 ste.amount amount,
4486 khr.sts_code sts_code,
4487 khl.pdt_id pdt_id
4488 FROM OKL_STRM_ELEMENTS ste,
4489 OKL_STREAMS stm,
4490 okl_strm_type_v sty,
4491 okc_k_headers_b khr,
4492 OKL_K_HEADERS khl,
4493 okc_k_lines_b kle,
4494 okc_statuses_b khs,
4495 okc_statuses_b kls
4496 WHERE TRUNC(ste.stream_element_date) >=
4497 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
4498 AND TRUNC(ste.stream_element_date) <=
4499 TRUNC((NVL (p_to_bill_date, SYSDATE) + okl_stream_billing_pvt.get_printing_lead_days(stm.khr_id)))
4500 AND ste.amount <> 0
4501 AND stm.id = ste.stm_id
4502 AND ste.date_billed IS NULL
4503 AND stm.active_yn = 'Y'
4504 AND stm.say_code = 'CURR'
4505 AND stm.sty_id = sty.id
4506 AND sty.billable_yn = 'Y'
4507 AND khr.scs_code IN ('LEASE', 'LOAN')
4508 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED')
4509 AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
4510 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
4511 AND khl.id = khr.id
4512 AND stm.khr_id = khl.id
4513 AND khl.deal_type IS NOT NULL
4514 AND khr.sts_code = khs.code
4515 AND khs.ste_code = 'ACTIVE'
4516 AND stm.kle_id = kle.id (+)
4517 AND kle.sts_code = kls.code
4518 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED')
4519 ORDER BY 1,2,3;
4520
4521 -- K level Bill to Site
4522 CURSOR cust_acct_site_csr (cp_khr_id IN NUMBER) IS
4523 SELECT cs.cust_acct_site_id
4524 FROM okc_k_headers_v khr
4525 , okx_cust_site_uses_v cs
4526 , hz_customer_profiles cp
4527 WHERE khr.id = cp_khr_id
4528 AND khr.bill_to_site_use_id = cs.id1
4529 AND khr.bill_to_site_use_id = cp.site_use_id(+);
4530
4531 -- Customer Account, Org Id cursor
4532 CURSOR cust_acct_csr(p_khr_id IN NUMBER) IS
4533 SELECT cust_acct_id,
4534 authoring_org_id
4535 FROM okc_k_headers_v
4536 WHERE id = p_khr_id;
4537
4538 -- Customer Name, Customer Account Number
4539 CURSOR c_cust_name(cp_cust_acct_id IN NUMBER) IS
4540 SELECT name,
4541 hca.account_number
4542 FROM okx_parties_v hp,
4543 hz_cust_accounts hca
4544 WHERE hp.id1 = hca.party_id
4545 AND hca.cust_account_id = cp_cust_acct_id;
4546
4547 -- Customer Site Name
4548 CURSOR c_cust_site_name(cp_cust_acct_site_id IN okx_cust_site_uses_v.cust_acct_site_id%TYPE) IS
4549 SELECT description
4550 FROM okx_cust_sites_v
4551 WHERE cust_acct_site_id = cp_cust_acct_site_id;
4552
4553 -- K level Payment method
4554 CURSOR cust_pmth_csr ( p_khr_id IN NUMBER ) IS
4555 SELECT object1_id1
4556 FROM OKC_RULES_B rul,
4557 Okc_rule_groups_B rgp
4558 WHERE rul.rgp_id = rgp.id AND
4559 rgp.rgd_code = 'LABILL' AND
4560 rgp.dnz_chr_id = rgp.chr_id AND
4561 rul.rule_information_category = 'LAPMTH' AND
4562 rgp.dnz_chr_id = p_khr_id;
4563
4564 -- Line Level Payment method
4565 CURSOR cust_line_pmth_csr ( p_khr_id IN NUMBER,
4566 p_kle_id IN NUMBER ) IS
4567 SELECT object1_id1
4568 FROM OKC_RULES_B rul,
4569 Okc_rule_groups_B rgp
4570 WHERE rul.rgp_id = rgp.id AND
4571 rgp.rgd_code = 'LABILL' AND
4572 rgp.cle_id = p_kle_id AND
4573 rul.rule_information_category = 'LAPMTH' AND
4574 rgp.dnz_chr_id = p_khr_id
4575 UNION
4576 SELECT rul.object1_id1
4577 FROM okc_k_lines_b cle
4578 , okc_k_items_v item
4579 , okc_k_lines_b linked_asset
4580 , OKC_RULES_B rul
4581 , Okc_rule_groups_B rgp
4582 WHERE cle.dnz_chr_id = p_khr_id AND
4583 cle.id = p_kle_id AND
4584 cle.chr_id IS NULL AND
4585 cle.id = item.cle_id AND
4586 item.object1_id1 = linked_asset.id AND
4587 linked_asset.id = rgp.cle_id AND
4588 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
4589 rgp.rgd_code = 'LABILL' AND
4590 rul.rgp_id = rgp.id AND
4591 rul.rule_information_category = 'LAPMTH';
4592
4593 -- System level Payment Method
4594 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
4595 SELECT c.receipt_method_id,
4596 c.name
4597 FROM okx_receipt_methods_v c
4598 WHERE c.id1 = p_cust_rct_mthd;
4599
4600 -- System Level Payment Method Code
4601 CURSOR rcpt_method_csr ( p_rct_method_id NUMBER) IS
4602 SELECT C.CREATION_METHOD_CODE
4603 FROM AR_RECEIPT_METHODS M,
4604 AR_RECEIPT_CLASSES C
4605 WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
4606 M.receipt_method_id = p_rct_method_id;
4607
4608 -- Line level Bill to Site
4609 CURSOR line_bill_to_csr(p_khr_id IN NUMBER,
4610 p_kle_id IN NUMBER) IS
4611 SELECT cs.cust_acct_site_id
4612 FROM okc_k_headers_b khr
4613 , okx_cust_site_uses_v cs
4614 , okc_k_lines_b cle
4615 , hz_customer_profiles cp
4616 WHERE khr.id = p_khr_id
4617 AND cle.dnz_chr_id = khr.id
4618 AND cle.chr_id IS NOT NULL
4619 AND cle.id = p_kle_id
4620 AND cle.BILL_TO_SITE_USE_ID = cs.id1
4621 AND khr.bill_to_site_use_id = cp.site_use_id(+)
4622 UNION
4623 SELECT cs.cust_acct_site_id
4624 FROM okc_k_headers_b khr
4625 , okc_k_lines_b cle
4626 , okc_k_items item
4627 , okc_k_lines_b linked_asset
4628 , okx_cust_site_uses_v cs
4629 , hz_customer_profiles cp
4630 WHERE khr.id = p_khr_id
4631 AND cle.dnz_chr_id = khr.id
4632 AND cle.id = p_kle_id
4633 AND cle.chr_id IS NULL
4634 AND cle.id = item.cle_id
4635 AND item.object1_id1 = linked_asset.id
4636 AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
4637 AND khr.bill_to_site_use_id = cp.site_use_id(+);
4638
4639 -- Validate customer payment method in AR
4640 CURSOR validate_cust_pmt_method (cp_customer_id IN ra_cust_receipt_methods.customer_id%TYPE,
4641 cp_receipt_method_id IN ra_cust_receipt_methods.receipt_method_id%TYPE,
4642 cp_trx_date IN DATE) IS
4643 SELECT 'X'
4644 FROM ra_cust_receipt_methods CPM
4645 WHERE cpm.customer_id = cp_customer_id
4646 AND cpm.receipt_method_id = cp_receipt_method_id
4647 AND cp_trx_date BETWEEN NVL(CPM.START_DATE, cp_trx_date)
4648 AND NVL(CPM.END_DATE, cp_trx_date);
4649
4650 -- Validate payment method in AR
4651 CURSOR validate_pmt_method (cp_receipt_method_id IN ra_cust_receipt_methods.receipt_method_id%TYPE,
4652 cp_trx_date IN DATE) IS
4653 SELECT 'X'
4654 FROM AR_RECEIPT_METHODS CPM
4655 WHERE cpm.receipt_method_id = cp_receipt_method_id
4656 AND cp_trx_date BETWEEN NVL(CPM.START_DATE, cp_trx_date)
4657 AND NVL(CPM.END_DATE, cp_trx_date);
4658
4659 -- Validate Bill to Site in AR
4660 -- bug 10139401 , Modified the where clause to set correct comparisions
4661 CURSOR validate_bill_to_site (cp_cust_accountsite_id IN NUMBER)
4662 IS
4663 SELECT 'X'
4664 FROM hz_cust_acct_sites acct_site,
4665 hz_cust_site_uses site_use
4666 WHERE acct_site.cust_acct_site_id = cp_cust_accountsite_id
4667 AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
4668 AND site_use.site_use_code = 'BILL_TO'
4669 AND acct_site.status = 'A'
4670 AND site_use.status = 'A';
4671
4672 -- Validate K Level Bank Account in IBY
4673 CURSOR validate_k_bank_account (cp_khr_id IN okc_k_headers_all_b.id%TYPE) IS
4674 SELECT DECODE (SIGN ( TRUNC (SYSDATE) - pym_instr.start_date),-1, 'I',
4675 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (pym_instr.end_date, TRUNC (SYSDATE))),1, 'I','A')) instr_status,
4676 bnk.bank_account_number,
4677 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.start_date, TRUNC (SYSDATE))), -1, 'I',
4678 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.end_date, TRUNC (SYSDATE))), 1, 'I','A') ) bnk_status
4679 FROM okc_rules_b rul,
4680 okc_rule_groups_b rgp,
4681 iby_pmt_instr_uses_all pym_instr,
4682 iby_ext_bank_accounts_v bnk
4683 WHERE rul.rgp_id = rgp.id
4684 AND rgp.rgd_code = 'LABILL'
4685 AND rgp.dnz_chr_id = rgp.chr_id
4686 AND rgp.dnz_chr_id = cp_khr_id
4687 AND rul.rule_information_category = 'LABACC'
4688 AND rul.object1_id1 = pym_instr.instrument_payment_use_id
4689 AND pym_instr.instrument_id = bnk.bank_account_id
4690 AND pym_instr.instrument_type = 'BANKACCOUNT';
4691
4692 -- Derive Line level Bank Account
4693 CURSOR cust_line_bank_csr ( cp_khr_id NUMBER,
4694 cp_kle_id NUMBER ) IS
4695 SELECT object1_id1
4696 FROM OKC_RULES_B rul,
4697 Okc_rule_groups_B rgp
4698 WHERE rul.rgp_id = rgp.id AND
4699 rgp.cle_id = cp_kle_id AND
4700 rgp.rgd_code = 'LABILL' AND
4701 rul.rule_information_category = 'LABACC' AND
4702 rgp.dnz_chr_id = cp_khr_id
4703 UNION
4704 SELECT rul.object1_id1
4705 FROM okc_k_lines_b cle
4706 , okc_k_items_v item
4707 , okc_k_lines_b linked_asset
4708 , OKC_RULES_B rul
4709 , Okc_rule_groups_B rgp
4710 WHERE cle.dnz_chr_id = cp_khr_id AND
4711 cle.id = cp_kle_id AND
4712 cle.chr_id IS NULL AND
4713 cle.id = item.cle_id AND
4714 item.object1_id1 = linked_asset.id AND
4715 linked_asset.id = rgp.cle_id AND
4716 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
4717 rgp.rgd_code = 'LABILL' AND
4718 rul.rgp_id = rgp.id AND
4719 rul.rule_information_category = 'LABACC';
4720
4721 -- Validate line level Bank Account in IBY
4722 CURSOR validate_bank_account (cp_instrument_payment_use_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE) IS
4723 SELECT DECODE (SIGN ( TRUNC (SYSDATE) - pym_instr.start_date),-1, 'I',
4724 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (pym_instr.end_date, TRUNC (SYSDATE))),1, 'I','A')) instr_status,
4725 bnk.bank_account_number,
4726 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.start_date, TRUNC (SYSDATE))), -1, 'I',
4727 DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.end_date, TRUNC (SYSDATE))), 1, 'I','A') ) bnk_status
4728 FROM iby_pmt_instr_uses_all pym_instr,
4729 iby_ext_bank_accounts_v bnk
4730 WHERE pym_instr.instrument_payment_use_id = cp_instrument_payment_use_id
4731 AND pym_instr.instrument_id = bnk.bank_account_id
4732 AND pym_instr.instrument_type = 'BANKACCOUNT';
4733
4734 -- Cursor to fetch ccids for the passed product and stream type
4735 CURSOR c_ccid(cp_sty_id IN okl_streams.sty_id%TYPE,
4736 cp_pdt_id IN okl_k_headers.pdt_id%TYPE) IS
4737 SELECT aetl.code_combination_id,
4738 glv.concatenated_segments
4739 FROM okl_ae_tmpt_lnes aetl,
4740 okl_ae_templates aet,
4741 okl_ae_tmpt_sets aes,
4742 okl_products pdt,
4743 okl_trx_types_b trx,
4744 gl_code_combinations_kfv glv
4745 WHERE aetl.avl_id = aet.id
4746 AND aet.aes_id = aes.id
4747 AND aes.id = pdt.aes_id
4748 AND pdt.id = cp_pdt_id
4749 AND aet.sty_id = cp_sty_id
4750 AND aet.try_id = trx.id
4751 AND trx.aep_code IN ('CREDIT_MEMO', 'BILLING','ADJUSTMENTS')
4752 AND aetl.code_combination_id = glv.code_combination_id;
4753
4754 -- Validate GL CCID
4755 CURSOR c_gl_ccid_valid(cp_ccid IN NUMBER,
4756 cp_bill_date IN DATE) IS
4757 SELECT 'x'
4758 FROM gl_code_combinations
4759 WHERE code_combination_id = cp_ccid
4760 AND enabled_flag = 'Y'
4761 AND cp_bill_date BETWEEN NVL(start_date_active,cp_bill_date)
4762 AND NVL(end_date_active, cp_bill_date);
4763
4764 -- cursor c_streams data structure
4765 TYPE bill_rec_type IS RECORD (
4766 khr_id okc_k_headers_b.id%TYPE,
4767 bill_date okl_strm_elements.stream_element_date%TYPE,
4768 kle_id okl_streams.kle_id%TYPE,
4769 sel_id okl_strm_elements.id%TYPE,
4770 sty_id okl_streams.sty_id%TYPE,
4771 contract_number okc_k_headers_b.contract_number%TYPE,
4772 currency_code okc_k_headers_b.currency_code%TYPE,
4773 authoring_org_id okc_k_headers_b.authoring_org_id%TYPE,
4774 sty_name okl_strm_type_v.NAME%TYPE,
4775 taxable_default_yn okl_strm_type_v.taxable_default_yn%TYPE,
4776 amount okl_strm_elements.amount%TYPE,
4777 sts_code okc_k_headers_b.sts_code%TYPE,
4778 pdt_id okl_k_headers.pdt_id%TYPE);
4779
4780 TYPE bill_tbl_type IS TABLE OF bill_rec_type
4781 INDEX BY BINARY_INTEGER;
4782
4783 bill_tbl bill_tbl_type;
4784
4785 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE := -1;
4786 l_bill_date okl_trx_ar_invoices_v.date_invoiced%TYPE;
4787 l_kle_id okl_txl_ar_inv_lns_v.kle_id%TYPE := -1;
4788 l_auth_org_id okc_k_headers_all_b.authoring_org_id%TYPE;
4789 l_pdt_id okl_products.id%TYPE;
4790 l_aes_id okl_products.aes_id%TYPE;
4791 l_chart_of_accounts_id NUMBER;
4792 l_instr_assignment_id NUMBER;
4793 l_ext_receipt_method_name okx_receipt_methods_v.name%TYPE;
4794 l_ext_line_receipt_method_name okx_receipt_methods_v.name%TYPE;
4795 l_rct_method_code ar_receipt_classes.creation_method_code%TYPE;
4796 l_ext_customer_id Okl_Ext_Sell_Invs_V.customer_id%TYPE;
4797 l_ext_receipt_method_id Okl_Ext_Sell_Invs_V.receipt_method_id%TYPE;
4798 l_party_name okx_parties_v.name%TYPE;
4799 l_ext_addr_id okx_cust_site_uses_v.cust_acct_site_id%TYPE;
4800 l_cust_site_name okx_cust_sites_v.description%TYPE;
4801 l_pmth_id1 okc_rules_b.object1_id1%TYPE;
4802 l_ext_line_addr_id okl_ext_sell_invs_v.customer_address_id%TYPE;
4803 l_pmth_line_id1 okc_rules_b.object1_id1%TYPE;
4804 l_ext_line_receipt_method_id Okl_Ext_Sell_Invs_V.receipt_method_id%TYPE;
4805 l_rct_line_method_code ar_receipt_classes.creation_method_code%TYPE;
4806 l_account_number hz_cust_accounts.account_number%TYPE;
4807 l_bank_line_id1 okc_rules_b.object1_id1%TYPE;
4808
4809 l_exists VARCHAR2(1);
4810 l_fetch_size NUMBER := 5000;
4811
4812 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_REPORT_PVT.PRE_BILLING';
4813 l_debug_enabled VARCHAR2(10);
4814 is_debug_procedure_on BOOLEAN;
4815 is_debug_statement_on BOOLEAN;
4816 x_errbuf VARCHAR2(1000);
4817 x_retcode NUMBER;
4818
4819 -- Messages
4820 l_inv_bank_account_msg fnd_new_messages.message_text%TYPE;
4821 l_inv_pmt_method_msg fnd_new_messages.message_text%TYPE;
4822 l_inv_bill_to_site_msg fnd_new_messages.message_text%TYPE;
4823 l_inv_ccid_msg fnd_new_messages.message_text%TYPE;
4824
4825 BEGIN
4826
4827 l_debug_enabled := okl_debug_pub.check_log_enabled;
4828 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
4829
4830 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
4831 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin procedure pre_billing_proc');
4832 END IF;
4833
4834 -- check for logging on STATEMENT level
4835 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
4836
4837 OPEN c_streams;
4838 LOOP
4839 -- Clear table contents
4840 bill_tbl.DELETE;
4841 FETCH c_streams BULK COLLECT INTO bill_tbl LIMIT l_fetch_size;
4842 IF bill_tbl.COUNT > 0 THEN
4843 FOR k IN bill_tbl.FIRST..bill_tbl.LAST LOOP
4844 IF l_khr_id <> bill_tbl(k).khr_id
4845 OR l_bill_date <> bill_tbl(k).bill_date THEN
4846
4847 -- 1,2,3 : Bank account, Payment method and Bill to Site validation
4848
4849 -- Customer Account Id
4850 OPEN cust_acct_csr ( bill_tbl(k).khr_id );
4851 FETCH cust_acct_csr INTO l_ext_customer_id, l_auth_org_id;
4852 CLOSE cust_acct_csr;
4853
4854 -- Customer Name
4855 OPEN c_cust_name(cp_cust_acct_id => l_ext_customer_id);
4856 FETCH c_cust_name INTO l_party_name, l_account_number;
4857 CLOSE c_cust_name;
4858
4859 -- Customer Account Site
4860 OPEN cust_acct_site_csr ( bill_tbl(k).khr_id );
4861 FETCH cust_acct_site_csr INTO l_ext_addr_id;
4862 CLOSE cust_acct_site_csr;
4863
4864 -- Customer Account Site Name
4865 OPEN c_cust_site_name(cp_cust_acct_site_id => l_ext_addr_id);
4866 FETCH c_cust_site_name INTO l_cust_site_name;
4867 CLOSE c_cust_site_name;
4868
4869 -- Check if Contract Line level details exist,
4870 -- If yes, then we need to validate only line level details
4871
4872 -- Fetch Line Level Bill to Site
4873 OPEN line_bill_to_csr(bill_tbl(k).khr_id,
4874 bill_tbl(k).kle_id );
4875 FETCH line_bill_to_csr INTO l_ext_line_addr_id;
4876 CLOSE line_bill_to_csr;
4877
4878 -- Note: If line level address exists,
4879 -- 1. derive the line level payment method
4880 -- 2. validate the line level bill to site
4881 -- If line level address and payment method is not null
4882 -- 1. validate line level payment method
4883 -- 2. validate line level bank account
4884 -- If line level address does not exist validate the header level details
4885
4886 -- If Line Level address exists
4887 IF l_ext_line_addr_id IS NOT NULL THEN
4888 -- clear line level address variables
4889 l_pmth_line_id1 := NULL;
4890 l_ext_line_receipt_method_id := NULL;
4891 l_ext_line_receipt_method_name:= NULL;
4892 l_rct_line_method_code := NULL;
4893 l_bank_line_id1 := NULL;
4894
4895 -- Customer Account Site Name
4896 OPEN c_cust_site_name(cp_cust_acct_site_id => l_ext_line_addr_id);
4897 FETCH c_cust_site_name INTO l_cust_site_name;
4898 CLOSE c_cust_site_name;
4899
4900 -- Validate Line level Bill to Site
4901 OPEN validate_bill_to_site(l_ext_line_addr_id);
4902 FETCH validate_bill_to_site INTO l_exists;
4903 IF validate_bill_to_site%NOTFOUND THEN
4904 IF l_khr_id <> bill_tbl(k).khr_id THEN
4905 fnd_message.set_name('OKL','OKL_INVALID_BILL_TO_SITE');
4906 l_inv_bill_to_site_msg := fnd_message.get;
4907 -- insert into gt table
4908 insert_gt(bill_tbl(k).contract_number,
4909 l_party_name,
4910 l_account_number,
4911 l_cust_site_name,
4912 l_inv_bill_to_site_msg,
4913 l_cust_site_name);
4914 END IF; -- l_khr_id <> bill_tbl(k).khr_id
4915 END IF; -- validate_bill_to_site%NOTFOUND
4916 CLOSE validate_bill_to_site;
4917
4918 -- Line level Payment Method
4919 OPEN cust_line_pmth_csr (bill_tbl(k).khr_id,
4920 bill_tbl(k).kle_id );
4921 FETCH cust_line_pmth_csr INTO l_pmth_line_id1;
4922 CLOSE cust_line_pmth_csr;
4923
4924 -- Fetch Line Level system payment method
4925 OPEN rcpt_mthd_csr( l_pmth_line_id1 );
4926 FETCH rcpt_mthd_csr INTO l_ext_line_receipt_method_id,
4927 l_ext_line_receipt_method_name;
4928 CLOSE rcpt_mthd_csr;
4929
4930 -- If line level payment method is not null,
4931 -- validate line level payment method and line level bank account
4932 IF l_ext_line_receipt_method_id IS NOT NULL THEN
4933
4934 -- Validate Line level payment method
4935 OPEN validate_cust_pmt_method(l_ext_customer_id,
4936 l_ext_line_receipt_method_id,
4937 bill_tbl(k).bill_date);
4938 FETCH validate_cust_pmt_method INTO l_exists;
4939
4940 OPEN validate_pmt_method(l_ext_line_receipt_method_id,
4941 bill_tbl(k).bill_date);
4942 FETCH validate_pmt_method INTO l_exists;
4943
4944 IF validate_pmt_method%NOTFOUND OR validate_cust_pmt_method%NOTFOUND THEN
4945 IF l_khr_id <> bill_tbl(k).khr_id THEN
4946 fnd_message.set_name('OKL','OKL_INVALID_PMT_METHOD');
4947 l_inv_pmt_method_msg := fnd_message.get;
4948 -- insert into gt table
4949 insert_gt(bill_tbl(k).contract_number,
4950 l_party_name,
4951 l_account_number,
4952 l_cust_site_name,
4953 l_inv_pmt_method_msg,
4954 l_ext_line_receipt_method_name);
4955 END IF; -- l_khr_id <> bill_tbl(k).khr_id
4956 END IF; -- validate_pmt_method%NOTFOUND
4957 CLOSE validate_pmt_method;
4958 CLOSE validate_cust_pmt_method;
4959
4960 -- Fetch Line Level System payment Method code
4961 OPEN rcpt_method_csr (l_ext_line_receipt_method_id);
4962 FETCH rcpt_method_csr INTO l_rct_line_method_code;
4963 CLOSE rcpt_method_csr;
4964
4965 -- derive and validate bank account
4966 IF (l_rct_line_method_code = 'AUTOMATIC') THEN
4967 -- fetch line level bank account
4968 OPEN cust_line_bank_csr(cp_khr_id => bill_tbl(k).khr_id,
4969 cp_kle_id => bill_tbl(k).kle_id);
4970 FETCH cust_line_bank_csr INTO l_bank_line_id1;
4971 CLOSE cust_line_bank_csr;
4972
4973 FOR l_validate_bank_account IN validate_bank_account(cp_instrument_payment_use_id => l_bank_line_id1) LOOP
4974 -- validate bank account id in iby
4975 IF l_validate_bank_account.instr_status = 'I' OR l_validate_bank_account.bnk_status = 'I' THEN
4976 fnd_message.set_name('OKL','OKL_INVALID_BANK_ACCT');
4977 l_inv_bank_account_msg := fnd_message.get;
4978 -- insert into gt table
4979 insert_gt(bill_tbl(k).contract_number,
4980 l_party_name,
4981 l_account_number,
4982 l_cust_site_name,
4983 l_inv_bank_account_msg,
4984 l_validate_bank_account.bank_account_number);
4985 END IF; -- bank_account.instr_status
4986 END LOOP; -- validate_bank_account
4987
4988 -- validate line level bank account in IBY
4989
4990 END IF;
4991
4992 END IF; -- l_ext_line_receipt_method_id IS NOT NULL
4993
4994 ELSE -- line level addr id is null, validate header details
4995
4996 -- Validate K Bill to Site
4997 OPEN validate_bill_to_site(l_ext_addr_id);
4998 FETCH validate_bill_to_site INTO l_exists;
4999 IF validate_bill_to_site%NOTFOUND THEN
5000 IF l_khr_id <> bill_tbl(k).khr_id THEN
5001 fnd_message.set_name('OKL','OKL_INVALID_BILL_TO_SITE');
5002 l_inv_bill_to_site_msg := fnd_message.get;
5003 -- insert into gt table
5004 insert_gt(bill_tbl(k).contract_number,
5005 l_party_name,
5006 l_account_number,
5007 l_cust_site_name,
5008 l_inv_bill_to_site_msg,
5009 l_cust_site_name);
5010 END IF; -- l_khr_id <> bill_tbl(k).khr_id
5011 END IF; -- validate_bill_to_site%NOTFOUND
5012 CLOSE validate_bill_to_site;
5013
5014 -- K level Payment method
5015 OPEN cust_pmth_csr ( bill_tbl(k).khr_id );
5016 FETCH cust_pmth_csr INTO l_pmth_id1;
5017 CLOSE cust_pmth_csr;
5018
5019 -- Fetch K system level payment method
5020 OPEN rcpt_mthd_csr( l_pmth_id1 );
5021 FETCH rcpt_mthd_csr INTO l_ext_receipt_method_id,
5022 l_ext_receipt_method_name;
5023 CLOSE rcpt_mthd_csr;
5024 -- Added if condition for bug 10139401 , do not validate payment method if no payment method is attached
5025
5026 IF l_ext_receipt_method_id IS NOT NULL THEN
5027
5028 -- Validate K level payment method
5029 OPEN validate_cust_pmt_method(l_ext_customer_id,
5030 l_ext_receipt_method_id,
5031 bill_tbl(k).bill_date);
5032 FETCH validate_cust_pmt_method INTO l_exists;
5033
5034 OPEN validate_pmt_method(l_ext_receipt_method_id,
5035 bill_tbl(k).bill_date);
5036 FETCH validate_pmt_method INTO l_exists;
5037
5038 IF validate_pmt_method%NOTFOUND OR validate_cust_pmt_method%NOTFOUND THEN
5039 IF l_khr_id <> bill_tbl(k).khr_id THEN
5040 fnd_message.set_name('OKL','OKL_INVALID_PMT_METHOD');
5041 l_inv_pmt_method_msg := fnd_message.get;
5042 -- insert into gt table
5043 insert_gt(bill_tbl(k).contract_number,
5044 l_party_name,
5045 l_account_number,
5046 l_cust_site_name,
5047 l_inv_pmt_method_msg,
5048 l_ext_receipt_method_name);
5049 END IF; -- l_khr_id <> bill_tbl(k).khr_id
5050 END IF; -- validate_pmt_method%NOTFOUND
5051
5052
5053 CLOSE validate_cust_pmt_method;
5054 CLOSE validate_pmt_method;
5055
5056 -- Fetch K system level Receipt method code
5057 OPEN rcpt_method_csr (l_ext_receipt_method_id);
5058 FETCH rcpt_method_csr INTO l_rct_method_code;
5059 CLOSE rcpt_method_csr;
5060
5061 -- Validate the K level Bank account
5062 IF (l_rct_method_code = 'AUTOMATIC') THEN
5063 -- Fetch the instrument assignment id of the K
5064 FOR l_validate_k_bank_account IN validate_k_bank_account(cp_khr_id => bill_tbl(k).khr_id) LOOP
5065 -- validate bank account id in iby
5066 IF l_validate_k_bank_account.instr_status = 'I' OR l_validate_k_bank_account.bnk_status = 'I' THEN
5067 IF l_khr_id <> bill_tbl(k).khr_id THEN
5068 fnd_message.set_name('OKL','OKL_INVALID_BANK_ACCT');
5069 l_inv_bank_account_msg := fnd_message.get;
5070 -- insert into gt table
5071 insert_gt(bill_tbl(k).contract_number,
5072 l_party_name,
5073 l_account_number,
5074 l_cust_site_name,
5075 l_inv_bank_account_msg,
5076 l_validate_k_bank_account.bank_account_number);
5077 END IF; -- l_khr_id <> bill_tbl(k).khr_id
5078 END IF; -- bank_account.instr_status
5079 END LOOP; -- validate_k_bank_account
5080
5081 END IF; -- rct_method_code = AUTOMATIC
5082
5083 END IF; -- l_ext_receipt_method_id IS NOT NULL
5084
5085 END IF; -- line level addr id is not null
5086
5087 -- 4. GL code combination is invalid
5088 FOR l_ccid IN c_ccid(cp_sty_id => bill_tbl(k).sty_id, cp_pdt_id => bill_tbl(k).pdt_id) LOOP
5089
5090 -- validate code combination id
5091 -- check if bill date or sysdate to be used for all cursors with PM
5092 OPEN c_gl_ccid_valid (cp_ccid => l_ccid.code_combination_id,
5093 cp_bill_date => bill_tbl(k).bill_date);
5094 FETCH c_gl_ccid_valid INTO l_exists;
5095 IF c_gl_ccid_valid%NOTFOUND THEN
5096 fnd_message.set_name('OKL','OKL_INVALID_CCID');
5097 fnd_message.set_token('STREAM_TYPE', bill_tbl(k).sty_name);
5098 l_inv_ccid_msg := fnd_message.get;
5099 -- insert into gt table
5100 insert_gt(bill_tbl(k).contract_number,
5101 l_party_name,
5102 l_account_number,
5103 l_cust_site_name,
5104 l_inv_ccid_msg,
5105 l_ccid.concatenated_segments);
5106 END IF;
5107 CLOSE c_gl_ccid_valid;
5108
5109 END LOOP; -- loop a/c ccids
5110
5111 -- put the context contract into local variables
5112 l_khr_id := bill_tbl(k).khr_id;
5113 l_bill_date := bill_tbl(k).bill_date;
5114 END IF; -- l_khr_id <> bill_tbl(k).khr_id
5115 END LOOP; -- loop a/c bill_tbl
5116 END IF; -- bill_tbl.count > 0
5117 EXIT WHEN c_streams%NOTFOUND;
5118 END LOOP; -- Loop a/c c_streams
5119 CLOSE c_streams;
5120
5121 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
5122 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end procedure pre_billing_proc');
5123 END IF;
5124 RETURN TRUE;
5125 EXCEPTION
5126 WHEN OTHERS THEN
5127 x_errbuf := SQLERRM;
5128 x_retcode := 2;
5129
5130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
5131
5132 IF (SQLCODE <> -20001) THEN
5133 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
5134 ELSE
5135 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
5136 END IF;
5137 RETURN TRUE;
5138 END pre_billing;
5139
5140 END Okl_Report_Pvt;