Rebooking a Contract through Backend

How to Rebook a Contract from Back-end

Once a contract is in BOOKED status, it cannot be modified, it has to be Rebooked. The rebook process involves copying the original contract into a RBK contract, making the required changes, and taking it through the following processes:

• QA Check
• Stream Generation
• Approval
• Booking

[restrictedarea]

Once the RBK contract is submitted for Booking, it gets into ABANDONED status and the original contract gets modified with the changes done.

The rebook process is done from the front-end by querying the contract and then selecting the Revise option from the drop-down list.

Sometime, it is required to rebook a contract or multiple contracts through backend. This blog gives details of how to do this using a PL/SQL script.

The script will create a procedure which will be registered as a concurrent program. The procedure takes Contract number and Extension term as input parameters. The program will extend the contract by the given extension term (in months).

The script has the following main parts:

1. Creating a Rebook Transaction and copying the contract into RBK contract
2. Update the contract header with the new term (and thus new end-date)
3. Extension of payments up to the new end date
4. Executing QA Process
5. Generating Streams
6. Submitting contract for Approval
7. Updating status to Approved
8. Activating the RBK contract

CREATE OR REPLACE PROCEDURE XXX_REBOOK_CONTRACT_PROC
(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_contract_number IN VARCHAR2, ---Contract Number
p_extension_term IN NUMBER ---Term in months
)
IS
---Cursor to get the contract details

CURSOR khr_csr IS
SELECT chr.id,
end_date,
term_duration
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE chr.id = khr.id
AND chr.contract_number = p_contract_number;

---Cursor to select the QA Check list ID
CURSOR qcl_csr IS
SELECT id
FROM okc_Qa_check_lists_v
WHERE name = 'OKL LA QA CHECK LIST';

---Cursor to get the existing Rule group for Payment

CURSOR rgp_csr(v_khr_id NUMBER) IS
SELECT rgp.id rgp_id, cle.id cle_id
FROM okc_rule_groups_b rgp, okc_k_lines_b cle
WHERE rgp.rgd_code = 'LALEVL'
AND rgp.rgp_type = 'KRG'
AND rgp.dnz_chr_id = v_khr_id
AND rgp.cle_id = cle.id
AND cle.lse_id = 33;

---Cursor to get the Payment Rule Header for Asset Line

CURSOR slh_csr(v_cle_id NUMBER) IS
SELECT rule.id rule_id
FROM okc_rules_b rule, okc_rule_groups_b rgp, okl_strm_type_b sty
WHERE rule.rgp_id = rgp.id
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = v_cle_id
AND rule.rule_information_category = 'LASLH'
AND rule.jtot_object1_code = 'OKL_STRMTYP'
AND rule.object1_id1 = sty.id
AND sty.code = 'RENT';
---Cursor to get the Payment Details for asset line

CURSOR sll_csr(v_cle_id NUMBER) IS
SELECT add_months(to_date(rule_information2,'YYYY/MM/DD HH24:MI:SS') ,rule_information3) sort_date,
object1_id1 frequency,
rule_information10 advance_arrears,
rule_information5 structure
FROM okc_rules_b rule, okc_rule_groups_B rgp
WHERE rule.rgp_id = rgp.id
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = v_cle_id
AND rule.rule_information_category = 'LASLL'
AND rule.jtot_object2_code = 'OKL_STRMHDR'
and rule_information6 is not null
ORDER BY to_date(rule_information2,'YYYY/MM/DD HH24:MI:SS') desc;

CURSOR cont_csr(v_khr_id NUMBER) IS
SELECT contract_number
FROM okc_k_headers_b
WHERE id = v_khr_id;

CURSOR sts_csr(v_khr_id NUMBER) IS
SELECT sis_code
FROM okl_stream_interfaces
WHERE khr_id = v_khr_id;

 

l_status OKL_STREAM_INTERFACES.sis_code%TYPE;

l_error_tbl okl_accounting_util.error_message_type;

l_rulv_tbl_in OKC_RULE_PVT.rulv_tbl_type;
l_rulv_tbl_out OKC_RULE_PVT.rulv_tbl_type;

i NUMBER := 0;

EXCP_OCCURRED EXCEPTION;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_from_chr_id NUMBER;
l_end_date DATE;
l_rebook_reason VARCHAR2(100) := 'OTHER';
l_rebook_desc VARCHAR2(100) := 'Auto-Rebook on Expiry';
l_trx_date DATE;
l_tcnv_rec_out OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
l_rebook_chr_id NUMBER;
l_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
l_qcl_id NUMBER;
l_term_duration NUMBER;
l_rebook_contract OKC_K_HEADERS_B.contract_number%TYPE;
l_new_end_date DATE;
l_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
l_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
x_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
x_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;

l_slh_id NUMBER;

l_sort_date DATE;
l_sort_date_char VARCHAR2(100);
l_payment_frequency VARCHAR2(10);
l_advance_arrears VARCHAR2(10);
l_structure VARCHAR2(10);

 
BEGIN

OPEN khr_csr;
FETCH khr_csr INTO l_from_chr_id, l_end_Date, l_term_duration;
CLOSE khr_csr;

IF (l_end_date < SYSDATE) THEN ----If contract end date is in past then Rebook Date=Contract end date
l_trx_date := l_end_date;
ELSE
l_trx_date := SYSDATE; ----If contract end date is in future then rebook date = system date
END IF;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Using Transaction Date of ' || l_trx_date);

--Copying the contract and creating a Rebook Transaction

OKL_CONTRACT_REBOOK_PVT.create_txn_contract(p_api_version => l_api_version,
                                            p_init_msg_list => l_init_msg_list,
                                            x_return_status => l_return_status,
                                            x_msg_count => l_msg_count,
                                            x_msg_data => l_msg_data,
                                            p_from_chr_id => l_from_chr_id,
                                            p_rebook_reason_code => l_rebook_reason,
                                            p_rebook_description => l_rebook_desc,
                                            p_trx_date => l_trx_date,
                                            x_tcnv_rec => l_tcnv_rec_out,
                                            x_rebook_chr_id => l_rebook_chr_id);

FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status after Creating transaction ' || l_return_status);

IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
   okl_accounting_util.get_error_message(l_error_tbl);
   for i IN 1..l_error_tbl.count
   loop
      FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
   end loop;
   RAISE excp_occurred;
END IF;

OPEN cont_csr(l_rebook_chr_id);
FETCH cont_csr INTO l_rebook_contract;
CLOSE cont_csr;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Rebook Contract is ' || l_rebook_contract);
l_chrv_rec.id := l_rebook_chr_id;
l_khrv_rec.id := l_rebook_chr_id;

l_khrv_rec.term_duration := l_term_duration + p_extension_term; ---This is the new Term

SELECT add_months(l_end_date,p_extension_term) INTO l_new_end_date FROM DUAL; ---New End Date

l_chrv_rec.end_date := l_new_end_date;

---Updating header with the new End date

okl_contract_pub.update_contract_header(p_api_version => 1.0,
                                        p_init_msg_list => OKC_API.G_FALSE,
                                        x_return_status => l_return_status,
                                        x_msg_count => l_msg_count,
                                        x_msg_data => l_msg_data,
                                        p_restricted_update => OKC_API.G_FALSE,
                                        p_chrv_rec => l_chrv_rec,
                                        p_khrv_rec => l_khrv_rec,
                                        x_chrv_rec => x_chrv_rec,
                                        x_khrv_rec => x_khrv_rec);
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
    okl_accounting_util.get_error_message(l_error_tbl);
    for i IN 1..l_error_tbl.count
    loop
       FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
    end loop;
    RAISE excp_occurred;
END IF;

i := 0;
FOR rgp_rec IN rgp_csr(l_rebook_chr_id)
LOOP

     i := i + 1;

     OPEN slh_csr(rgp_rec.cle_id);
     FETCH slh_csr INTO l_slh_id;
     CLOSE slh_csr;

     OPEN sll_csr(rgp_rec.cle_id);
     FETCH sll_csr INTO l_sort_date, l_payment_frequency, l_advance_arrears, l_structure;
     CLOSE sll_csr;

     l_rulv_tbl_in(i).rule_information7 := NULL;
     l_rulv_tbl_in(i).rule_information8 := NULL;
     l_rulv_tbl_in(i).rule_information3 := p_extension_term; ---Additional Payment Term
     l_rulv_tbl_in(i).rule_information6 := 0; -----New Payment for extended term. It can be changed to any value.
     SELECT TO_CHAR(l_sort_date,'YYYY/MM/DD HH24:MI:SS') INTO l_sort_date_char FROM DUAL;
     l_rulv_tbl_in(i).rule_information2 := l_sort_date_char;
     l_rulv_tbl_in(i).jtot_object1_code := 'OKL_TUOM';
     l_rulv_tbl_in(i).object1_id1 := l_payment_frequency;
     l_rulv_tbl_in(i).rule_information10 := l_advance_arrears;
     l_rulv_tbl_in(i).rule_information5 := l_structure;
     l_rulv_tbl_in(i).jtot_object2_code := 'OKL_STRMHDR';
     l_rulv_tbl_in(i).object2_id1 := l_slh_id;
     l_rulv_tbl_in(i).object2_id2 := '#';
     l_rulv_tbl_in(i).dnz_chr_id := l_rebook_chr_id;
     l_rulv_tbl_in(i).rgp_id := rgp_rec.rgp_id;
     l_rulv_tbl_in(i).sfwt_flag := 'N';
     l_rulv_tbl_in(i).std_template_yn := 'N';
     l_rulv_tbl_in(i).warn_yn := 'N';
     l_rulv_tbl_in(i).rule_information_category := 'LASLL';

END LOOP;

---Creating additional payments

okc_rule_pub.create_rule(p_api_version => l_api_version,
                         p_init_msg_list => l_init_msg_list,
                         x_msg_count => l_msg_count,
                         x_msg_data => l_msg_data,
                         x_return_status => l_return_status,
                         p_rulv_tbl => l_rulv_tbl_in,
                         x_rulv_tbl => l_rulv_tbl_out);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status after Update Rule is ' || l_return_status);

 
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
    okl_accounting_util.get_error_message(l_error_tbl);
    for i IN 1..l_error_tbl.count
    loop
        FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
    end loop;
    RAISE excp_occurred;
END IF;

 

OPEN qcl_csr;
FETCH qcl_csr INTO l_qcl_id;
CLOSE qcl_csr;

FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing QA Check List');

OKL_CONTRACT_BOOK_PUB.execute_qa_check_list(
                                 p_api_version => l_api_version,
                                 p_init_msg_list => l_init_msg_list,
                                 x_return_status => l_return_status,
                                 x_msg_count => l_msg_count,
                                 x_msg_data => l_msg_data,
                                 p_qcl_id => l_qcl_id,
                                 p_chr_id => l_rebook_chr_id,
                                 x_msg_tbl => l_msg_tbl);

FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status after QA Check is ' || l_return_status);

COMMIT;

IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
     okl_accounting_util.get_error_message(l_error_tbl);
     for i IN 1..l_error_tbl.count
     loop
         FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
     end loop;
     RAISE excp_occurred;
END IF;
FOR i IN 1..l_msg_tbl.COUNT
LOOP
   IF ( l_msg_tbl(i).error_status = 'E' ) THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,'QA Check has failed with Error');
      EXIT;
   END IF;
END LOOP;
---Displaying QA Check Errors/Warnings/Messages

FOR i IN 1..l_msg_tbl.COUNT
LOOP
    FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_tbl(i).severity || CHR(9) ||
    l_msg_tbl(i).name || CHR(9) ||
    l_msg_tbl(i).error_status || CHR(9) ||
    l_msg_tbl(i).data );
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling Generate Streams ');

OKL_CONTRACT_BOOK_PUB.generate_streams(
                          p_api_version => l_api_version,
                          p_init_msg_list => l_init_msg_list,
                          p_chr_id => l_rebook_chr_id,
                          p_generation_context => 'AUTH',
                          x_return_status => l_return_status,
                          x_msg_count => l_msg_count,
                          x_msg_data => l_msg_data);

FND_FILE.PUT_LINE(FND_FILE.LOG,'After Generating the Streams ' || l_return_status);
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
    okl_accounting_util.get_error_message(l_error_tbl);
    for i IN 1..l_error_tbl.count
    loop
         FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
    end loop;
    RAISE excp_occurred;
END IF;

COMMIT;

---Following LOOP waits for Stream Generation Process to complete.

LOOP

    OPEN sts_csr(l_rebook_chr_id);
    FETCH sts_csr INTO l_status;
    CLOSE sts_csr;

    IF (l_status IN ('PROCESS_COMPLETE','PROCESSING_FAILED','PROCESS_COMPLETE_ERRORS')) THEN
         EXIT;
    END IF;

END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitting for Approval');

OKL_CONTRACT_BOOK_PUB.submit_for_approval(
                       p_api_version => l_api_version,
                       p_init_msg_list => l_init_msg_list,
                       x_return_status => l_return_status,
                       x_msg_count => l_msg_count,
                       x_msg_data => l_msg_data,
                       p_chr_id => l_rebook_chr_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'After Submitting for Approval ' || l_return_status);
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
    okl_accounting_util.get_error_message(l_error_tbl);
    for i IN 1..l_error_tbl.count
    loop
         FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
    end loop;
    RAISE excp_occurred;
END IF;
----Updating contract status to APPROVED from PENDING_APPROVAL

okl_contract_status_pub.update_contract_status(
                               l_api_version,
                               l_init_msg_list,
                               l_return_status,
                               l_msg_count,
                               l_msg_data,
                               'APPROVED',
                               l_rebook_chr_id);

----Update the Lines as well with the APPROVED status

OKL_CONTRACT_STATUS_PUB.cascade_lease_status
                        (p_api_version => l_api_version,
                         p_init_msg_list => l_init_msg_list,
                         x_return_status => l_return_status,
                         x_msg_count => l_msg_count,
                         x_msg_data => l_msg_data,
                         p_chr_id => l_rebook_chr_id);
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
      okl_accounting_util.get_error_message(l_error_tbl);
      for i IN 1..l_error_tbl.count
      loop
          FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
      end loop;
      RAISE excp_occurred;
END IF;

 

FND_FILE.PUT_LINE(FND_FILE.LOG,'After updating Contract Status to Approved ' || l_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Activating the contract ');
OKL_CONTRACT_BOOK_PUB.activate_contract(
                    p_api_version => l_api_version,
                    p_init_msg_list => l_init_msg_list,
                    x_return_status => l_return_status,
                    x_msg_count => l_msg_count,
                    x_msg_data => l_msg_data,
                    p_chr_id => l_rebook_chr_id);

FND_FILE.PUT_LINE(FND_FILE.LOG,'After Activating the contract ' || l_return_status);
IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
    okl_accounting_util.get_error_message(l_error_tbl);
    for i IN 1..l_error_tbl.count
    loop
        FND_FILE.put_line(FND_FILE.LOG,l_error_tbl(i));
    end loop;
    RAISE excp_occurred;
END IF;

COMMIT;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract ' || p_contract_number || ' has been successfully extended for ' || p_extension_term || ' Months.');
EXCEPTION

WHEN excp_occurred THEN
rollback;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occurred ' || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract ' || p_contract_number || ' could not be rebooked. Please check logfile');

WHEN OTHERS THEN
rollback;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract ' || p_contract_number || ' could not be rebooked. Please check logfile');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Other Error Occurred ' || SQLERRM);
END XXX_REBOOK_CONTRACT_PROC;
/

 

[/restrictedarea]