Script to add Billing Type to a coverage of existing contract

DECLARE
  l_clev_rec_in   okc_contract_pub.clev_rec_type;
  l_clev_rec_out  okc_contract_pub.clev_rec_type;
  l_klnv_rec_in   oks_contract_line_pub.klnv_rec_type;
  l_klnv_rec_out  oks_contract_line_pub.klnv_rec_type;
  l_return_status VARCHAR2 (10);
  l_msg_count     NUMBER                   := 0;
  l_msg_data      VARCHAR2 (25000);
  l_msg_dummy     VARCHAR2 (25000);
  l_output        VARCHAR2 (25000);
  c_api_version   CONSTANT NUMBER          := 1.0;
  c_init_msg_list CONSTANT VARCHAR2 (1000) := apps.okc_api.g_false;
  l_err_msg       VARCHAR2 (25000);
  l_cle_id        NUMBER;
  l_cimv_rec_in   okc_contract_item_pub.cimv_rec_type;
  l_cimv_rec_out  okc_contract_item_pub.cimv_rec_type;
  l_object_id     NUMBER;
  l_name          VARCHAR2 (30);
  l_count         NUMBER                   := 0;
  c_user_id       NUMBER                   := 1238;   -- Conversion
  CURSOR cur_contract_details
  IS
    SELECT okh.id contract_id,
      okh.contract_number,
      okh.sts_code,
      okl1.line_number,
      okl1.start_date,
      okl1.end_date,
      okl3.id bp_id,
      okl3.currency_code
    FROM okc_k_headers_all_b okh,
      okc_k_lines_b okl1,
      okc_k_lines_b okl2,
      okc_k_lines_b okl3,
      okc_k_items oki,
      okx_bus_processes_v okbp,
      oks_coverages_v okv
    WHERE okh.ID                      = okl1.dnz_chr_id
    AND okh.sts_code NOT             IN ('TERMINATED' , 'EXPIRED')
    AND okl1.sts_code NOT            IN ('TERMINATED' , 'EXPIRED')
    AND okl1.dnz_chr_id               = okl2.dnz_chr_id
    AND okl1.chr_id                  IS NOT NULL
    AND okl1.ID                       = okl2.cle_id
    AND okl2.lse_id                   = 2
    AND okl2.ID                       = okl3.cle_id
    AND okl3.lse_id                   = 3
    AND okl3.ID                       = oki.cle_id
    AND oki.object1_id1               = okbp.id1
    AND oki.jtot_object1_code         = 'OKX_BUSIPROC'
    AND okbp.NAME                     = 'CUSTOMER SUPPORT'
    AND okl1.id                       = okv.service_cle_id
    AND okv.template_coverage_chr_id IS NULL
    AND okv.name                      = 'R2-PLATINUM';
  CURSOR cur_trx_billing_type
  IS
    SELECT id1,
      billing_type
    FROM okx_txn_billing_types_v
    WHERE name = 'BDFS-CONTRACT';
BEGIN
  FOR rec_contract_details IN cur_contract_details
  LOOP
    FOR rec_trx_billing_type IN cur_trx_billing_type
    LOOP
      l_clev_rec_in.ID                    := NULL;
      l_clev_rec_in.dnz_chr_id            := rec_contract_details.contract_id;
      l_clev_rec_in.chr_id                := NULL;
      l_clev_rec_in.cle_id                := rec_contract_details.bp_id;
      l_clev_rec_in.lse_id                := 5;
      l_clev_rec_in.object_version_number := c_api_version;
      l_clev_rec_in.start_date            := rec_contract_details.start_date;
      l_clev_rec_in.end_date              := rec_contract_details.end_date;
      l_clev_rec_in.currency_code         := rec_contract_details.currency_code;
      l_clev_rec_in.created_by            := c_user_id;
      l_clev_rec_in.creation_date         := SYSDATE;
      l_clev_rec_in.last_updated_by       := c_user_id;
      l_clev_rec_in.last_update_date      := SYSDATE;
      l_clev_rec_in.sts_code              := rec_contract_details.sts_code;
      l_clev_rec_in.exception_yn          := 'N';
      l_clev_rec_in.display_sequence      := 1;
      BEGIN
        okc_contract_pub.create_contract_line (p_api_version       => c_api_version,
                                               p_init_msg_list     => c_init_msg_list,
                                               x_return_status     => l_return_status,
                                               x_msg_count         => l_msg_count,
                                               x_msg_data          => l_msg_data,
                                               p_restricted_update => 'Y',
                                               p_clev_rec          => l_clev_rec_in,
                                               x_clev_rec          => l_clev_rec_out
                                              );
        IF l_return_status <> 'S' THEN
          BEGIN
            FOR i IN 1 .. l_msg_count
            LOOP
              fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
              l_output := (TO_CHAR (i) || ': ' || l_msg_data);
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('Error1 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
          END;
          ROLLBACK;
        ELSE
          BEGIN
            l_klnv_rec_in.cle_id           := l_clev_rec_out.ID;
            l_klnv_rec_in.dnz_chr_id       := l_clev_rec_out.dnz_chr_id;
            l_klnv_rec_in.discount_percent := 100;
            l_klnv_rec_in.created_by       := c_user_id;
            l_klnv_rec_in.creation_date    := SYSDATE;
            l_klnv_rec_in.last_updated_by  := c_user_id;
            l_klnv_rec_in.last_update_date := SYSDATE;
            oks_contract_line_pub.create_line (p_api_version    => c_api_version,
                                               p_init_msg_list  => c_init_msg_list,
                                               x_return_status  => l_return_status,
                                               x_msg_count      => l_msg_count,
                                               x_msg_data       => l_msg_data,
                                               p_klnv_rec       => l_klnv_rec_in,
                                               x_klnv_rec       => l_klnv_rec_out,
                                               p_validate_yn    => 'Y'
                                              );
            IF l_return_status <> 'S' THEN
              BEGIN
                FOR i IN 1 .. l_msg_count
                LOOP
                  fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
                  l_output := (TO_CHAR (i) || ': ' || l_msg_data);
                END LOOP;
                DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
              END;
              ROLLBACK;
            ELSE
              BEGIN
                l_cimv_rec_in.cle_id            := l_clev_rec_out.ID;
                l_cimv_rec_in.dnz_chr_id        := l_clev_rec_out.dnz_chr_id;
                l_cimv_rec_in.object1_id1       := rec_trx_billing_type.id1;
                l_cimv_rec_in.object1_id2       := '#';
                l_cimv_rec_in.jtot_object1_code := 'OKX_BILLTYPE';
                l_cimv_rec_in.exception_yn      := 'N';
                l_cimv_rec_in.created_by        := c_user_id;
                l_cimv_rec_in.creation_date     := SYSDATE;
                l_cimv_rec_in.last_updated_by   := c_user_id;
                l_cimv_rec_in.last_update_date  := SYSDATE;
                okc_contract_item_pub.create_contract_item (p_api_version   => c_api_version,
                                                            p_init_msg_list => c_init_msg_list,
                                                            x_return_status => l_return_status,
                                                            x_msg_count     => l_msg_count,
                                                            x_msg_data      => l_msg_data,
                                                            p_cimv_rec      => l_cimv_rec_in,
                                                            x_cimv_rec      => l_cimv_rec_out
                                                           );
                IF l_return_status <> 'S' THEN
                  BEGIN
                    FOR i IN 1 .. l_msg_count
                    LOOP
                      fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
                      l_output := (TO_CHAR (i) || ': ' || l_msg_data);
                    END LOOP;
                    DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
                  END;
                  ROLLBACK;
                ELSE
                  COMMIT;
                  l_count:= l_count+1;
                END IF;
              END;
            END IF;
          END;
        END IF;
      END;
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total Contracts Updated successfull are :'||l_count);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE ('Error in the process:'||SQLERRM);
END;