48: WHERE id = p_id;
49:
50: l_okl_pvnv_pk okl_pvnv_pk_csr%ROWTYPE;
51: l_pvnv_rec pvnv_rec_type;
52: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
53: BEGIN
54: x_no_data_found := TRUE;
55:
56: -- Get current database values
96: EXCEPTION
97: WHEN OTHERS THEN
98:
99: -- store SQL error message on message stack
100: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
101: p_msg_name => G_UNEXPECTED_ERROR,
102: p_token1 => G_SQLCODE_TOKEN,
103: p_token1_value => sqlcode,
104: p_token2 => G_SQLERRM_TOKEN,
103: p_token1_value => sqlcode,
104: p_token2 => G_SQLERRM_TOKEN,
105: p_token2_value => sqlerrm);
106: -- notify UNEXPECTED error for calling API.
107: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108:
109: IF (okl_pvnv_pk_csr%ISOPEN) THEN
110: CLOSE okl_pvnv_pk_csr;
111: END IF;
364: x_return_status OUT NOCOPY VARCHAR2,
365: x_msg_data OUT NOCOPY VARCHAR2
366: ) IS
367: l_pvnv_rec pvnv_rec_type;
368: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
369: l_valid BOOLEAN;
370: l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
371: BEGIN
372: l_pvnv_rec := p_pvnv_rec;
386: x_return_status => l_return_status,
387: x_valid => l_valid);
388:
389:
390: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
392: RAISE G_EXCEPTION_HALT_PROCESSING;
393: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394: (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
387: x_valid => l_valid);
388:
389:
390: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
392: RAISE G_EXCEPTION_HALT_PROCESSING;
393: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394: (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
395: l_valid <> TRUE) THEN
389:
390: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
392: RAISE G_EXCEPTION_HALT_PROCESSING;
393: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394: (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
395: l_valid <> TRUE) THEN
396: x_return_status := OKL_API.G_RET_STS_ERROR;
397: RAISE G_EXCEPTION_HALT_PROCESSING;
390: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
392: RAISE G_EXCEPTION_HALT_PROCESSING;
393: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394: (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
395: l_valid <> TRUE) THEN
396: x_return_status := OKL_API.G_RET_STS_ERROR;
397: RAISE G_EXCEPTION_HALT_PROCESSING;
398: END IF;
392: RAISE G_EXCEPTION_HALT_PROCESSING;
393: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394: (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
395: l_valid <> TRUE) THEN
396: x_return_status := OKL_API.G_RET_STS_ERROR;
397: RAISE G_EXCEPTION_HALT_PROCESSING;
398: END IF;
399: x_return_status := l_return_status;
400: EXCEPTION
404: NULL;
405:
406: WHEN OTHERS THEN
407: -- store SQL error message on message stack for caller
408: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
409: p_msg_name => G_UNEXPECTED_ERROR,
410: p_token1 => G_SQLCODE_TOKEN,
411: p_token1_value => sqlcode,
412: p_token2 => G_SQLERRM_TOKEN,
412: p_token2 => G_SQLERRM_TOKEN,
413: p_token2_value => sqlerrm );
414: x_msg_data := 'Unexpected Database Error';
415: -- notify caller of an UNEXPECTED error
416: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
417:
418: END check_updates;
419:
420:
432: l_api_version CONSTANT NUMBER := 1;
433: l_api_name CONSTANT VARCHAR2(30) := 'create_provisions';
434: l_no_data_found BOOLEAN := TRUE;
435: l_valid BOOLEAN := TRUE;
436: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
437: l_pvnv_rec pvnv_rec_type;
438: l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
439: BEGIN
440: l_return_status := OKL_API.G_RET_STS_SUCCESS;
436: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
437: l_pvnv_rec pvnv_rec_type;
438: l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
439: BEGIN
440: l_return_status := OKL_API.G_RET_STS_SUCCESS;
441: l_pvnv_rec := p_pvnv_rec;
442:
443: l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
444: p_pkg_name => G_PKG_NAME,
439: BEGIN
440: l_return_status := OKL_API.G_RET_STS_SUCCESS;
441: l_pvnv_rec := p_pvnv_rec;
442:
443: l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
444: p_pkg_name => G_PKG_NAME,
445: p_init_msg_list => p_init_msg_list,
446: l_api_version => l_api_version,
447: p_api_version => p_api_version,
446: l_api_version => l_api_version,
447: p_api_version => p_api_version,
448: p_api_type => '_PVT',
449: x_return_status => l_return_status);
450: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
453: RAISE OKL_API.G_EXCEPTION_ERROR;
454: END IF;
447: p_api_version => p_api_version,
448: p_api_type => '_PVT',
449: x_return_status => l_return_status);
450: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
453: RAISE OKL_API.G_EXCEPTION_ERROR;
454: END IF;
455:
448: p_api_type => '_PVT',
449: x_return_status => l_return_status);
450: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
453: RAISE OKL_API.G_EXCEPTION_ERROR;
454: END IF;
455:
456: /* validate name */
449: x_return_status => l_return_status);
450: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
453: RAISE OKL_API.G_EXCEPTION_ERROR;
454: END IF;
455:
456: /* validate name */
457: IF (l_pvnv_rec.name IS NULL OR l_pvnv_rec.name = G_MISS_CHAR) THEN
454: END IF;
455:
456: /* validate name */
457: IF (l_pvnv_rec.name IS NULL OR l_pvnv_rec.name = G_MISS_CHAR) THEN
458: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
459: p_msg_name => 'OKL_PVN_NAME_ERROR');
460: RAISE OKL_API.G_EXCEPTION_ERROR;
461: END IF;
462:
456: /* validate name */
457: IF (l_pvnv_rec.name IS NULL OR l_pvnv_rec.name = G_MISS_CHAR) THEN
458: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
459: p_msg_name => 'OKL_PVN_NAME_ERROR');
460: RAISE OKL_API.G_EXCEPTION_ERROR;
461: END IF;
462:
463: /* validate on application debit account */
464: IF (l_pvnv_rec.app_debit_ccid IS NULL OR l_pvnv_rec.app_debit_ccid = G_MISS_NUM) THEN
461: END IF;
462:
463: /* validate on application debit account */
464: IF (l_pvnv_rec.app_debit_ccid IS NULL OR l_pvnv_rec.app_debit_ccid = G_MISS_NUM) THEN
465: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
466: p_msg_name => 'OKL_PVN_CCID_ERROR');
467: RAISE OKL_API.G_EXCEPTION_ERROR;
468: END IF;
469:
463: /* validate on application debit account */
464: IF (l_pvnv_rec.app_debit_ccid IS NULL OR l_pvnv_rec.app_debit_ccid = G_MISS_NUM) THEN
465: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
466: p_msg_name => 'OKL_PVN_CCID_ERROR');
467: RAISE OKL_API.G_EXCEPTION_ERROR;
468: END IF;
469:
470: /* validate on application credit account */
471: IF (l_pvnv_rec.app_credit_ccid IS NULL OR l_pvnv_rec.app_credit_ccid = G_MISS_NUM) THEN
468: END IF;
469:
470: /* validate on application credit account */
471: IF (l_pvnv_rec.app_credit_ccid IS NULL OR l_pvnv_rec.app_credit_ccid = G_MISS_NUM) THEN
472: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
473: p_msg_name => 'OKL_PVN_CCID_ERROR');
474: RAISE OKL_API.G_EXCEPTION_ERROR;
475: END IF;
476:
470: /* validate on application credit account */
471: IF (l_pvnv_rec.app_credit_ccid IS NULL OR l_pvnv_rec.app_credit_ccid = G_MISS_NUM) THEN
472: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
473: p_msg_name => 'OKL_PVN_CCID_ERROR');
474: RAISE OKL_API.G_EXCEPTION_ERROR;
475: END IF;
476:
477: /* validate on reversal debit account */
478: IF (l_pvnv_rec.rev_debit_ccid IS NULL OR l_pvnv_rec.rev_debit_ccid = G_MISS_NUM) THEN
475: END IF;
476:
477: /* validate on reversal debit account */
478: IF (l_pvnv_rec.rev_debit_ccid IS NULL OR l_pvnv_rec.rev_debit_ccid = G_MISS_NUM) THEN
479: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
480: p_msg_name => 'OKL_PVN_CCID_ERROR');
481: RAISE OKL_API.G_EXCEPTION_ERROR;
482: END IF;
483:
477: /* validate on reversal debit account */
478: IF (l_pvnv_rec.rev_debit_ccid IS NULL OR l_pvnv_rec.rev_debit_ccid = G_MISS_NUM) THEN
479: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
480: p_msg_name => 'OKL_PVN_CCID_ERROR');
481: RAISE OKL_API.G_EXCEPTION_ERROR;
482: END IF;
483:
484: /* validate on reversal credit account */
485: IF (l_pvnv_rec.rev_credit_ccid IS NULL OR l_pvnv_rec.rev_credit_ccid = G_MISS_NUM) THEN
482: END IF;
483:
484: /* validate on reversal credit account */
485: IF (l_pvnv_rec.rev_credit_ccid IS NULL OR l_pvnv_rec.rev_credit_ccid = G_MISS_NUM) THEN
486: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
487: p_msg_name => 'OKL_PVN_CCID_ERROR');
488: RAISE OKL_API.G_EXCEPTION_ERROR;
489: END IF;
490:
484: /* validate on reversal credit account */
485: IF (l_pvnv_rec.rev_credit_ccid IS NULL OR l_pvnv_rec.rev_credit_ccid = G_MISS_NUM) THEN
486: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
487: p_msg_name => 'OKL_PVN_CCID_ERROR');
488: RAISE OKL_API.G_EXCEPTION_ERROR;
489: END IF;
490:
491: /* validate effective start date */
492: IF (l_pvnv_rec.from_date IS NULL OR l_pvnv_rec.from_date = G_MISS_DATE) THEN
489: END IF;
490:
491: /* validate effective start date */
492: IF (l_pvnv_rec.from_date IS NULL OR l_pvnv_rec.from_date = G_MISS_DATE) THEN
493: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
494: p_msg_name => 'OKL_PVN_DATE_ERROR');
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
497:
491: /* validate effective start date */
492: IF (l_pvnv_rec.from_date IS NULL OR l_pvnv_rec.from_date = G_MISS_DATE) THEN
493: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
494: p_msg_name => 'OKL_PVN_DATE_ERROR');
495: RAISE OKL_API.G_EXCEPTION_ERROR;
496: END IF;
497:
498: /* check for the records with start and end dates less than sysdate */
499: IF to_date(l_pvnv_rec.from_date, 'DD/MM/YYYY') < l_sysdate OR
497:
498: /* check for the records with start and end dates less than sysdate */
499: IF to_date(l_pvnv_rec.from_date, 'DD/MM/YYYY') < l_sysdate OR
500: to_date(l_pvnv_rec.to_date, 'DD/MM/YYYY') < l_sysdate THEN
501: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
502: p_msg_name => G_PAST_RECORDS);
503: RAISE OKL_API.G_EXCEPTION_ERROR;
504: END IF;
505:
499: IF to_date(l_pvnv_rec.from_date, 'DD/MM/YYYY') < l_sysdate OR
500: to_date(l_pvnv_rec.to_date, 'DD/MM/YYYY') < l_sysdate THEN
501: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
502: p_msg_name => G_PAST_RECORDS);
503: RAISE OKL_API.G_EXCEPTION_ERROR;
504: END IF;
505:
506:
507: /* public api to insert provision type */
512: x_msg_data => x_msg_data,
513: p_pvnv_rec => l_pvnv_rec,
514: x_pvnv_rec => x_pvnv_rec);
515:
516: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
517: RAISE OKL_API.G_EXCEPTION_ERROR;
518: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520: END IF;
513: p_pvnv_rec => l_pvnv_rec,
514: x_pvnv_rec => x_pvnv_rec);
515:
516: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
517: RAISE OKL_API.G_EXCEPTION_ERROR;
518: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520: END IF;
521:
514: x_pvnv_rec => x_pvnv_rec);
515:
516: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
517: RAISE OKL_API.G_EXCEPTION_ERROR;
518: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520: END IF;
521:
522: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
515:
516: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
517: RAISE OKL_API.G_EXCEPTION_ERROR;
518: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520: END IF;
521:
522: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
523: x_msg_data => x_msg_data);
518: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520: END IF;
521:
522: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
523: x_msg_data => x_msg_data);
524: x_return_status := l_return_status;
525: EXCEPTION
526: WHEN OKL_API.G_EXCEPTION_ERROR THEN
522: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
523: x_msg_data => x_msg_data);
524: x_return_status := l_return_status;
525: EXCEPTION
526: WHEN OKL_API.G_EXCEPTION_ERROR THEN
527: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
528: p_pkg_name => G_PKG_NAME,
529: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
530: x_msg_count => x_msg_count,
523: x_msg_data => x_msg_data);
524: x_return_status := l_return_status;
525: EXCEPTION
526: WHEN OKL_API.G_EXCEPTION_ERROR THEN
527: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
528: p_pkg_name => G_PKG_NAME,
529: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
530: x_msg_count => x_msg_count,
531: x_msg_data => x_msg_data,
525: EXCEPTION
526: WHEN OKL_API.G_EXCEPTION_ERROR THEN
527: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
528: p_pkg_name => G_PKG_NAME,
529: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
530: x_msg_count => x_msg_count,
531: x_msg_data => x_msg_data,
532: p_api_type => '_PVT');
533: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
529: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
530: x_msg_count => x_msg_count,
531: x_msg_data => x_msg_data,
532: p_api_type => '_PVT');
533: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
534: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
535: p_pkg_name => G_PKG_NAME,
536: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
537: x_msg_count => x_msg_count,
530: x_msg_count => x_msg_count,
531: x_msg_data => x_msg_data,
532: p_api_type => '_PVT');
533: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
534: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
535: p_pkg_name => G_PKG_NAME,
536: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
537: x_msg_count => x_msg_count,
538: x_msg_data => x_msg_data,
532: p_api_type => '_PVT');
533: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
534: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
535: p_pkg_name => G_PKG_NAME,
536: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
537: x_msg_count => x_msg_count,
538: x_msg_data => x_msg_data,
539: p_api_type => '_PVT');
540: WHEN OTHERS THEN
537: x_msg_count => x_msg_count,
538: x_msg_data => x_msg_data,
539: p_api_type => '_PVT');
540: WHEN OTHERS THEN
541: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
542: p_pkg_name => G_PKG_NAME,
543: p_exc_name => 'OTHERS',
544: x_msg_count => x_msg_count,
545: x_msg_data => x_msg_data,
567: l_db_pvnv_rec pvnv_rec_type; /* database copy */
568: l_upd_pvnv_rec pvnv_rec_type; /* input copy */
569: l_pvnv_rec pvnv_rec_type := p_pvnv_rec; /* latest with the retained changes */
570: l_tmp_pvnv_rec pvnv_rec_type; /* for any other purposes */
571: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
572: l_action VARCHAR2(1);
573: l_new_version VARCHAR2(100);
574: l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
575: BEGIN
572: l_action VARCHAR2(1);
573: l_new_version VARCHAR2(100);
574: l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
575: BEGIN
576: l_return_status := OKL_API.G_RET_STS_SUCCESS;
577: --l_upd_pvnv_rec := p_pvnv_rec;
578:
579: l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
580: p_pkg_name => G_PKG_NAME,
575: BEGIN
576: l_return_status := OKL_API.G_RET_STS_SUCCESS;
577: --l_upd_pvnv_rec := p_pvnv_rec;
578:
579: l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
580: p_pkg_name => G_PKG_NAME,
581: p_init_msg_list => p_init_msg_list,
582: l_api_version => l_api_version,
583: p_api_version => p_api_version,
582: l_api_version => l_api_version,
583: p_api_version => p_api_version,
584: p_api_type => '_PVT',
585: x_return_status => l_return_status);
586: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589: RAISE OKL_API.G_EXCEPTION_ERROR;
590: END IF;
583: p_api_version => p_api_version,
584: p_api_type => '_PVT',
585: x_return_status => l_return_status);
586: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589: RAISE OKL_API.G_EXCEPTION_ERROR;
590: END IF;
591:
584: p_api_type => '_PVT',
585: x_return_status => l_return_status);
586: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589: RAISE OKL_API.G_EXCEPTION_ERROR;
590: END IF;
591:
592: /* fetch old details from the database */
585: x_return_status => l_return_status);
586: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589: RAISE OKL_API.G_EXCEPTION_ERROR;
590: END IF;
591:
592: /* fetch old details from the database */
593: get_rec(p_pvnv_rec => p_pvnv_rec,
594: x_return_status => l_return_status,
595: x_no_data_found => l_no_data_found,
596: x_pvnv_rec => l_db_pvnv_rec);
597:
598: IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
599: l_no_data_found = TRUE THEN
600: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601: END IF;
602:
596: x_pvnv_rec => l_db_pvnv_rec);
597:
598: IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
599: l_no_data_found = TRUE THEN
600: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601: END IF;
602:
603: /* check for the records if start and end dates are in the past */
604: IF to_date(l_db_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate AND
602:
603: /* check for the records if start and end dates are in the past */
604: IF to_date(l_db_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate AND
605: to_date(l_db_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
606: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
607: p_msg_name => G_PAST_RECORDS);
608: RAISE OKL_API.G_EXCEPTION_ERROR;
609: END IF;
610:
604: IF to_date(l_db_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate AND
605: to_date(l_db_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
606: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
607: p_msg_name => G_PAST_RECORDS);
608: RAISE OKL_API.G_EXCEPTION_ERROR;
609: END IF;
610:
611: /* retain the details that has been changed only */
612: get_changes_only(p_pvnv_rec => p_pvnv_rec,
615:
616: /* check for start date lesser than sysdate */
617: IF to_date(l_upd_pvnv_rec.from_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
618: to_date(l_upd_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate THEN
619: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
620: p_msg_name => G_START_DATE);
621: RAISE OKL_API.G_EXCEPTION_ERROR;
622: END IF;
623:
617: IF to_date(l_upd_pvnv_rec.from_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
618: to_date(l_upd_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate THEN
619: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
620: p_msg_name => G_START_DATE);
621: RAISE OKL_API.G_EXCEPTION_ERROR;
622: END IF;
623:
624: /* check for end date lesser than sysdate */
625: IF to_date(l_upd_pvnv_rec.to_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
623:
624: /* check for end date lesser than sysdate */
625: IF to_date(l_upd_pvnv_rec.to_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
626: to_date(l_upd_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
627: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
628: p_msg_name => G_END_DATE);
629: RAISE OKL_API.G_EXCEPTION_ERROR;
630: END IF;
631:
625: IF to_date(l_upd_pvnv_rec.to_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
626: to_date(l_upd_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
627: OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
628: p_msg_name => G_END_DATE);
629: RAISE OKL_API.G_EXCEPTION_ERROR;
630: END IF;
631:
632: /* determine how the processing to be done */
633: l_action := determine_action(p_upd_pvnv_rec => l_upd_pvnv_rec,
644: x_msg_count => x_msg_count,
645: x_msg_data => x_msg_data,
646: p_pvnv_rec => l_upd_pvnv_rec,
647: x_pvnv_rec => x_pvnv_rec);
648: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
649: RAISE OKL_API.G_EXCEPTION_ERROR;
650: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
651: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652: END IF;
645: x_msg_data => x_msg_data,
646: p_pvnv_rec => l_upd_pvnv_rec,
647: x_pvnv_rec => x_pvnv_rec);
648: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
649: RAISE OKL_API.G_EXCEPTION_ERROR;
650: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
651: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652: END IF;
653:
646: p_pvnv_rec => l_upd_pvnv_rec,
647: x_pvnv_rec => x_pvnv_rec);
648: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
649: RAISE OKL_API.G_EXCEPTION_ERROR;
650: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
651: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652: END IF;
653:
654: /* Scenario 2: The Changed Field-Values include that needs Validation and Update */
647: x_pvnv_rec => x_pvnv_rec);
648: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
649: RAISE OKL_API.G_EXCEPTION_ERROR;
650: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
651: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652: END IF;
653:
654: /* Scenario 2: The Changed Field-Values include that needs Validation and Update */
655: ELSIF l_action = '2' THEN
658:
659: check_updates(p_pvnv_rec => l_pvnv_rec,
660: x_return_status => l_return_status,
661: x_msg_data => x_msg_data);
662: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
663: RAISE OKL_API.G_EXCEPTION_ERROR;
664: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
665: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
666: END IF;
659: check_updates(p_pvnv_rec => l_pvnv_rec,
660: x_return_status => l_return_status,
661: x_msg_data => x_msg_data);
662: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
663: RAISE OKL_API.G_EXCEPTION_ERROR;
664: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
665: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
666: END IF;
667:
660: x_return_status => l_return_status,
661: x_msg_data => x_msg_data);
662: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
663: RAISE OKL_API.G_EXCEPTION_ERROR;
664: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
665: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
666: END IF;
667:
668: /* public api to update provisions */
661: x_msg_data => x_msg_data);
662: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
663: RAISE OKL_API.G_EXCEPTION_ERROR;
664: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
665: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
666: END IF;
667:
668: /* public api to update provisions */
669: OKL_PROVISIONS_PUB.UPDATE_PROVISIONS(p_api_version => l_api_version,
672: x_msg_count => x_msg_count,
673: x_msg_data => x_msg_data,
674: p_pvnv_rec => l_upd_pvnv_rec,
675: x_pvnv_rec => x_pvnv_rec);
676: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
677: RAISE OKL_API.G_EXCEPTION_ERROR;
678: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
679: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
680: END IF;
673: x_msg_data => x_msg_data,
674: p_pvnv_rec => l_upd_pvnv_rec,
675: x_pvnv_rec => x_pvnv_rec);
676: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
677: RAISE OKL_API.G_EXCEPTION_ERROR;
678: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
679: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
680: END IF;
681:
674: p_pvnv_rec => l_upd_pvnv_rec,
675: x_pvnv_rec => x_pvnv_rec);
676: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
677: RAISE OKL_API.G_EXCEPTION_ERROR;
678: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
679: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
680: END IF;
681:
682: /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
675: x_pvnv_rec => x_pvnv_rec);
676: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
677: RAISE OKL_API.G_EXCEPTION_ERROR;
678: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
679: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
680: END IF;
681:
682: /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
683: ELSIF l_action = '3' THEN
697: check_updates(p_pvnv_rec => l_pvnv_rec,
698: x_return_status => l_return_status,
699: x_msg_data => x_msg_data);
700:
701: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
702: RAISE OKL_API.G_EXCEPTION_ERROR;
703: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
704: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705: END IF;
698: x_return_status => l_return_status,
699: x_msg_data => x_msg_data);
700:
701: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
702: RAISE OKL_API.G_EXCEPTION_ERROR;
703: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
704: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705: END IF;
706: END IF;
699: x_msg_data => x_msg_data);
700:
701: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
702: RAISE OKL_API.G_EXCEPTION_ERROR;
703: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
704: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705: END IF;
706: END IF;
707:
700:
701: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
702: RAISE OKL_API.G_EXCEPTION_ERROR;
703: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
704: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705: END IF;
706: END IF;
707:
708: /* public api to update provisions */
713: x_msg_data => x_msg_data,
714: p_pvnv_rec => l_pvnv_rec,
715: x_pvnv_rec => x_pvnv_rec);
716:
717: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
718: RAISE OKL_API.G_EXCEPTION_ERROR;
719: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
720: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
721: END IF;
714: p_pvnv_rec => l_pvnv_rec,
715: x_pvnv_rec => x_pvnv_rec);
716:
717: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
718: RAISE OKL_API.G_EXCEPTION_ERROR;
719: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
720: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
721: END IF;
722:
715: x_pvnv_rec => x_pvnv_rec);
716:
717: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
718: RAISE OKL_API.G_EXCEPTION_ERROR;
719: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
720: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
721: END IF;
722:
723: /* for new version */
716:
717: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
718: RAISE OKL_API.G_EXCEPTION_ERROR;
719: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
720: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
721: END IF;
722:
723: /* for new version */
724: /* create a temporary record with all relevant details from db and upd records */
741: p_view => 'OKL_PROVISIONS_V',
742: x_return_status => l_return_status,
743: x_new_version => l_new_version);
744:
745: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
746: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
747: ELSE
748: l_pvnv_rec.version := l_new_version;
749: END IF;
742: x_return_status => l_return_status,
743: x_new_version => l_new_version);
744:
745: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
746: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
747: ELSE
748: l_pvnv_rec.version := l_new_version;
749: END IF;
750:
755: IF l_pvnv_rec.from_date > l_db_pvnv_rec.to_date THEN
756: check_updates(p_pvnv_rec => l_pvnv_rec,
757: x_return_status => l_return_status,
758: x_msg_data => x_msg_data);
759: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
760: RAISE OKL_API.G_EXCEPTION_ERROR;
761: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
762: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763: END IF;
756: check_updates(p_pvnv_rec => l_pvnv_rec,
757: x_return_status => l_return_status,
758: x_msg_data => x_msg_data);
759: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
760: RAISE OKL_API.G_EXCEPTION_ERROR;
761: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
762: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763: END IF;
764: END IF;
757: x_return_status => l_return_status,
758: x_msg_data => x_msg_data);
759: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
760: RAISE OKL_API.G_EXCEPTION_ERROR;
761: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
762: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763: END IF;
764: END IF;
765:
758: x_msg_data => x_msg_data);
759: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
760: RAISE OKL_API.G_EXCEPTION_ERROR;
761: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
762: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763: END IF;
764: END IF;
765:
766: /* public api to insert provisions */
771: x_msg_data => x_msg_data,
772: p_pvnv_rec => l_pvnv_rec,
773: x_pvnv_rec => x_pvnv_rec);
774:
775: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
776: RAISE OKL_API.G_EXCEPTION_ERROR;
777: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
778: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779: END IF;
772: p_pvnv_rec => l_pvnv_rec,
773: x_pvnv_rec => x_pvnv_rec);
774:
775: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
776: RAISE OKL_API.G_EXCEPTION_ERROR;
777: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
778: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779: END IF;
780:
773: x_pvnv_rec => x_pvnv_rec);
774:
775: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
776: RAISE OKL_API.G_EXCEPTION_ERROR;
777: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
778: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779: END IF;
780:
781: /* copy output to input structure to get the id */
774:
775: IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
776: RAISE OKL_API.G_EXCEPTION_ERROR;
777: ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
778: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779: END IF;
780:
781: /* copy output to input structure to get the id */
782: l_pvnv_rec := x_pvnv_rec;
782: l_pvnv_rec := x_pvnv_rec;
783:
784: END IF;
785:
786: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
787: x_msg_data => x_msg_data);
788: x_return_status := l_return_status;
789: EXCEPTION
790: WHEN OKL_API.G_EXCEPTION_ERROR THEN
786: OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
787: x_msg_data => x_msg_data);
788: x_return_status := l_return_status;
789: EXCEPTION
790: WHEN OKL_API.G_EXCEPTION_ERROR THEN
791: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
792: p_pkg_name => G_PKG_NAME,
793: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
794: x_msg_count => x_msg_count,
787: x_msg_data => x_msg_data);
788: x_return_status := l_return_status;
789: EXCEPTION
790: WHEN OKL_API.G_EXCEPTION_ERROR THEN
791: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
792: p_pkg_name => G_PKG_NAME,
793: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
794: x_msg_count => x_msg_count,
795: x_msg_data => x_msg_data,
789: EXCEPTION
790: WHEN OKL_API.G_EXCEPTION_ERROR THEN
791: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
792: p_pkg_name => G_PKG_NAME,
793: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
794: x_msg_count => x_msg_count,
795: x_msg_data => x_msg_data,
796: p_api_type => '_PVT');
797: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
793: p_exc_name => 'OKL_API.G_RET_STS_ERROR',
794: x_msg_count => x_msg_count,
795: x_msg_data => x_msg_data,
796: p_api_type => '_PVT');
797: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
798: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
799: p_pkg_name => G_PKG_NAME,
800: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
801: x_msg_count => x_msg_count,
794: x_msg_count => x_msg_count,
795: x_msg_data => x_msg_data,
796: p_api_type => '_PVT');
797: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
798: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
799: p_pkg_name => G_PKG_NAME,
800: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
801: x_msg_count => x_msg_count,
802: x_msg_data => x_msg_data,
796: p_api_type => '_PVT');
797: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
798: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
799: p_pkg_name => G_PKG_NAME,
800: p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
801: x_msg_count => x_msg_count,
802: x_msg_data => x_msg_data,
803: p_api_type => '_PVT');
804: WHEN OTHERS THEN
801: x_msg_count => x_msg_count,
802: x_msg_data => x_msg_data,
803: p_api_type => '_PVT');
804: WHEN OTHERS THEN
805: x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
806: p_pkg_name => G_PKG_NAME,
807: p_exc_name => 'OTHERS',
808: x_msg_count => x_msg_count,
809: x_msg_data => x_msg_data,