Script to create Service Contract with Line and Sub Line

DECLARE
  l_k_header_rec oks_contracts_pub.header_rec_type;
  l_header_contacts_tbl oks_contracts_pub.contact_tbl;
  l_header_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_header_articles_tbl oks_contracts_pub.obj_articles_tbl;
  l_k_line_rec oks_contracts_pub.line_rec_type;
  l_line_contacts_tbl oks_contracts_pub.contact_tbl;
  l_line_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_k_support_rec oks_contracts_pub.line_rec_type;
  l_support_contacts_tbl oks_contracts_pub.contact_tbl;
  l_support_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_k_covd_rec oks_contracts_pub.covered_level_rec_type;
  l_price_attribs_in oks_contracts_pub.pricing_attributes_type;
  l_strm_level_tbl oks_bill_sch.streamlvl_tbl;
  l_merge_rule        VARCHAR2(50);
  l_usage_instantiate VARCHAR2(50);
  l_ib_creation       VARCHAR2(1);
  l_billing_sch_type  VARCHAR2(50);
  l_chrid             NUMBER;
  l_service_line_id   NUMBER;
  l_cp_line_id        NUMBER;
  l_return_status     VARCHAR2(1);
  l_msg_count         NUMBER;
  l_msg_data          VARCHAR2(2000);
  l_sts_code          VARCHAR2(10);
  l_scs_code          VARCHAR2(10) := 'WARRANTY';
  l_tax_status_flag   VARCHAR2(1)  := 'S';
  l_merge_type        VARCHAR2(3)  := 'NEW';
  l_renewal_type      VARCHAR2(3)  := 'DNR';
  l_line_number       VARCHAR2(1)  := '1';
  l_line_type         VARCHAR2(1)  := 'W';
  l_negotiated_amount NUMBER       := 0;
  l_net_amount        NUMBER       := 0;
  l_sub_line_number   VARCHAR2(5)  := '1.1';
  l_tmp_cle_id        NUMBER;
  l_user_id           NUMBER := 0; -- SYSADMIN
  l_resp_id           NUMBER;
  l_resp_appl_id      NUMBER;
Type header_rec_type
IS
  Record
  (
    start_date           DATE,
    end_date             DATE,
    authoring_org_id     NUMBER,
    short_description    VARCHAR2(1995),
    party_id             NUMBER,
    bill_to_id           NUMBER,
    ship_to_id           NUMBER,
    price_list_id        NUMBER,
    cust_po_number       VARCHAR2(240),
    agreement_id         NUMBER,
    currency             VARCHAR2(15),
    accounting_rule_type NUMBER,
    invoice_rule_type    NUMBER,
    order_hdr_id         NUMBER,
    order_number         NUMBER,
    payment_term_id      NUMBER,
    cvn_type             VARCHAR2(25),
    cvn_rate             NUMBER,
    cvn_date             DATE,
    Organization_id      NUMBER,
    contact_id           NUMBER,
    cust_account         NUMBER);
TYPE header_tbl_type
IS
  TABLE OF header_rec_type INDEX BY BINARY_INTEGER;
  l_header_tbl header_tbl_type;
Type line_rec_type
IS
  Record
  (
    order_line_id NUMBER,
    start_date    DATE,
    end_date      DATE,
    srv_id        NUMBER,
    quantity      NUMBER,
    uom_code      VARCHAR2(25));
TYPE line_tbl_type
IS
  TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
  l_line_tbl line_tbl_type;
Type subline_rec_type
IS
  Record
  (
    customer_product_id NUMBER,
    product_desc        VARCHAR2(440));
TYPE subline_tbl_type
IS
  TABLE OF subline_rec_type INDEX BY BINARY_INTEGER;
  l_subline_tbl subline_tbl_type;
  CURSOR header_cur
  IS
    SELECT
      /*+ use_nl index (oe_order_headers_all, RESM_ORDER_HEADERS_N2) */
      MIN(oola.actual_shipment_date + NVL(msib.service_starting_delay,0))start_date,
      MAX(DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
      ooha.org_id authoring_org_id,
      'CUSTOMER : '
      ||hp.party_name
      ||' Warranty/Extended Warranty Contract' short_description,
      hp.party_id party_id,
      ooha.invoice_to_org_id bill_to_id,
      ooha.ship_to_org_id ship_to_id,
      ooha.price_list_id,
      ooha.cust_po_number,
      ooha.agreement_id,
      ooha.transactional_curr_code currency,
      ooha.accounting_rule_id accounting_rule_type,
      ooha.invoicing_rule_id invoice_rule_type ,
      ooha.header_id order_hdr_id,
      ooha.order_number,
      ooha.payment_term_id,
      ooha.conversion_type_code cvn_type,
      ooha.conversion_rate cvn_rate,
      ooha.conversion_rate_date cvn_date,
      ooha.ship_from_org_id organization_id,
      ooha.salesrep_id contact_id,
      hca.cust_account_id cust_account
    FROM mtl_system_items_b msi,
      bom_structures_b bbm,
      bom_components_b bic,
      oe_order_lines_all oola,
      mtl_system_items_b msib,
      csi_item_instances cii,
      oe_order_headers_all ooha,
      oe_transaction_types_all ott,
      hz_cust_accounts hca,
      hz_parties hp
    WHERE msi.vendor_warranty_flag = 'Y'
    AND msi.inventory_item_id      = bic.component_item_id
    AND msi.organization_id        = bbm.organization_id
    AND bbm.bill_sequence_id       = bic.bill_sequence_id
    AND bic.disable_date          IS NULL
    AND bbm.assembly_item_id       = oola.inventory_item_id
    AND bbm.organization_id        = oola.ship_from_org_id
    AND oola.inventory_item_id     = msib.inventory_item_id
    AND oola.ship_from_org_id      = msib.organization_id
    AND oola.line_id               = cii.last_oe_order_line_id
    AND cii.instance_status_id    <> 1
    AND oola.header_id             = ooha.header_id
    AND ooha.order_type_id         = ott.transaction_type_id
    AND ooha.org_id                = ott.org_id
    AND ott.order_category_code   <> 'RETURN'
    AND ooha.order_source_id      <> 7
    AND ooha.sold_to_org_id        = hca.cust_account_id
    AND hca.party_id               = hp.party_id
    AND ooha.order_number          = '606104'
    AND NOT EXISTS
      (SELECT 1
      FROM okc_k_items
      WHERE jtot_object1_code    = 'OKX_CUSTPROD'
      AND to_number(object1_id1) = cii.instance_id
      )
  AND NOT EXISTS
    (SELECT 1 FROM csi_txn_errors ctr WHERE ctr.instance_id = cii.instance_id
    )
  GROUP BY ooha.org_id,
    hp.party_name,
    hp.party_id,
    ooha.invoice_to_org_id,
    ooha.ship_to_org_id,
    ooha.price_list_id,
    ooha.cust_po_number,
    ooha.agreement_id,
    ooha.transactional_curr_code,
    ooha.accounting_rule_id,
    ooha.invoicing_rule_id ,
    ooha.header_id,
    ooha.order_number,
    ooha.payment_term_id,
    ooha.conversion_type_code,
    ooha.conversion_rate,
    ooha.conversion_rate_date,
    ooha.ship_from_org_id,
    ooha.salesrep_id,
    hca.cust_account_id;
  CURSOR line_cur(p_header_id NUMBER)
  IS
    SELECT oola.line_id order_line_id,
      (oola.actual_shipment_date+ NVL(msib.service_starting_delay,0))start_date,
      (DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
      msi.inventory_item_id srv_id,
      bic.Component_Quantity quantity,
      msi.primary_uom_code uom_code
    FROM mtl_system_items_b msi,
      bom_structures_b bbm,
      bom_components_b bic,
      oe_order_lines_all oola,
      mtl_system_items_b msib
    WHERE msi.vendor_warranty_flag   = 'Y'
    AND msi.inventory_item_id        = bic.component_item_id
    AND msi.organization_id          = bbm.organization_id
    AND bbm.bill_sequence_id         = bic.bill_sequence_id
    AND bic.disable_date            IS NULL
    AND bbm.assembly_item_id         = oola.inventory_item_id
    AND bbm.organization_id          = oola.ship_from_org_id
    AND oola.inventory_item_id       = msib.inventory_item_id
    AND oola.ship_from_org_id        = msib.organization_id
    AND msib.comms_nl_trackable_flag = 'Y'
    AND oola.header_id               = p_header_id;
  CURSOR subline_cur(p_line_id NUMBER)
  IS
    SELECT cii.instance_id customer_product_id,
      msib.segment1
      ||';'
      ||cii.serial_number
      ||';'
      ||cii.instance_id product_desc
    FROM oe_order_lines_all oola,
      csi_item_instances cii,
      bom_structures_b bbm,
      bom_components_b bic,
      mtl_system_items_b msib
    WHERE oola.line_id            = cii.last_oe_order_line_id
    AND oola.inventory_item_id    = bbm.assembly_item_id
    AND oola.ship_from_org_id     = bbm.organization_id
    AND bbm.bill_sequence_id      = bic.bill_sequence_id
    AND bic.disable_date         IS NULL
    AND msib.vendor_warranty_flag = 'Y'
    AND msib.inventory_item_id    = bic.component_item_id
    AND msib.organization_id      = bbm.organization_id
    AND line_id                   = p_line_id;
  CURSOR coverage_cur (p_item_id NUMBER, p_org_id NUMBER)
  IS
    SELECT coverage_template_id
    FROM okx_system_items_v
    WHERE id1           = p_item_id
    AND organization_id = p_org_id;
  CURSOR resp_cur
  IS
    SELECT responsibility_id,
      application_id
    FROM fnd_responsibility_vl
    WHERE responsibility_name = 'Service Contracts Manager (Corp) Config';
BEGIN
  OPEN resp_cur;
  FETCH resp_cur INTO l_resp_id,l_resp_appl_id;
  CLOSE resp_cur;
  fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
  okc_context.set_okc_org_context;
  okc_api.init_msg_list(OKC_API.G_TRUE);
  OPEN header_cur;
  FETCH header_cur BULK COLLECT INTO l_header_tbl;
  CLOSE header_cur;
  FOR i IN l_header_tbl.FIRST .. l_header_tbl.LAST
  LOOP
    IF (l_header_tbl(i).start_date  > SYSDATE) THEN
      l_sts_code                   := 'SIGNED';
    ELSIF (l_header_tbl(i).end_date < SYSDATE) THEN
      l_sts_code                   := 'EXPIRED';
    ELSE
      l_sts_code := 'ACTIVE';
    END IF;
    l_K_header_rec.start_date                    := l_header_tbl(i).start_date;
    l_K_header_rec.end_date                      := l_header_tbl(i).end_date;
    l_K_header_rec.sts_code                      := l_sts_code;
    l_K_header_rec.scs_code                      := l_scs_code;
    l_K_header_rec.authoring_org_id              := l_header_tbl(i).authoring_org_id;
    l_K_header_rec.short_description             := l_header_tbl(i).short_description;
    l_K_header_rec.party_id                      := l_header_tbl(i).party_id;
    l_K_header_rec.bill_to_id                    := l_header_tbl(i).bill_to_id;
    l_K_header_rec.ship_to_id                    := l_header_tbl(i).ship_to_id;
    l_K_header_rec.price_list_id                 := l_header_tbl(i).price_list_id;
    l_K_header_rec.cust_po_number                := l_header_tbl(i).cust_po_number;
    l_K_header_rec.agreement_id                  := l_header_tbl(i).agreement_id;
    l_K_header_rec.currency                      := l_header_tbl(i).currency;
    l_K_header_rec.accounting_rule_type          := l_header_tbl(i).accounting_rule_type;
    l_K_header_rec.invoice_rule_type             := l_header_tbl(i).invoice_rule_type;
    l_K_header_rec.order_hdr_id                  := l_header_tbl(i).order_hdr_id;
    l_K_header_rec.payment_term_id               := l_header_tbl(i).payment_term_id;
    l_K_header_rec.cvn_type                      := l_header_tbl(i).cvn_type;
    l_K_header_rec.cvn_rate                      := l_header_tbl(i).cvn_rate;
    l_K_header_rec.cvn_date                      := l_header_tbl(i).cvn_date;
    l_K_header_rec.tax_status_flag               := l_tax_status_flag;
    l_K_header_rec.merge_type                    := l_merge_type;
    l_K_header_rec.renewal_type                  := l_renewal_type;
    l_K_header_rec.organization_id               := l_header_tbl(i).organization_id;
    l_header_contacts_tbl(1).party_role          := 'VENDOR';
    l_header_contacts_tbl(1).contact_role        := 'SALESPERSON';
    l_header_contacts_tbl(1).contact_object_code := 'OKX_SALEPERS';
    l_header_contacts_tbl(1).contact_id          := l_header_tbl(i).contact_id;
    oks_contracts_pub.create_contract_header(p_K_header_rec                   => l_K_header_rec,
                                          p_header_contacts_tbl            => l_header_contacts_tbl,
                                             p_header_sales_crd_tbl           => l_header_sales_crd_tbl,
                                             p_header_articles_tbl            => l_header_articles_tbl,
                                             x_chrid                          => l_chrid,
                                             x_return_status                  => l_return_status,
                                             x_msg_count                      => l_msg_count,
                                             x_msg_data                       => l_msg_data);
    IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
      dbms_output.put_line ('Error while creating the Contract Header for order#: '||l_header_tbl(i).order_number);
      IF NVL(l_msg_count,0) > 0 THEN
        FOR p IN 1..l_msg_count
        LOOP
          dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
        END LOOP;
      END IF;
      ROLLBACK;
    ELSE
      OPEN line_cur(l_header_tbl(i).order_hdr_id);
      FETCH line_cur BULK COLLECT INTO l_line_tbl;
      CLOSE line_cur;
      FOR j IN l_line_tbl.FIRST .. l_line_tbl.LAST
      LOOP
     IF (l_line_tbl(j).start_date  > SYSDATE) THEN
           l_sts_code                   := 'SIGNED';
        ELSIF (l_line_tbl(j).end_date < SYSDATE) THEN
           l_sts_code                   := 'EXPIRED';
        ELSE
           l_sts_code := 'ACTIVE';
        END IF;
        l_k_line_rec.k_hdr_id             := l_chrid;
        l_k_line_rec.k_line_number        := j;
        l_k_line_rec.line_sts_code        := l_sts_code;
        l_k_line_rec.cust_account         := l_header_tbl(i).cust_account;
        l_k_line_rec.org_id               := l_header_tbl(i).authoring_org_id;
        l_k_line_rec.organization_id      := l_header_tbl(i).organization_id;
        l_k_line_rec.bill_to_id           := l_header_tbl(i).bill_to_id;
        l_k_line_rec.ship_to_id           := l_header_tbl(i).ship_to_id;
        l_k_line_rec.order_line_id        := l_line_tbl(j).order_line_id;
        l_k_line_rec.accounting_rule_type := l_header_tbl(i).accounting_rule_type;
        l_k_line_rec.invoicing_rule_type  := l_header_tbl(i).invoice_rule_type;
        l_k_line_rec.line_type            := l_line_type;
        l_k_line_rec.currency             := l_header_tbl(i).currency;
        l_k_line_rec.list_price           := l_header_tbl(i).price_list_id;
        l_k_line_rec.negotiated_amount    := l_negotiated_amount;
        l_k_line_rec.line_renewal_type    := l_renewal_type;
        l_k_line_rec.srv_id               := l_line_tbl(j).srv_id;
        l_k_line_rec.srv_sdt              := l_line_tbl(j).start_date;
        l_k_line_rec.srv_edt              := l_line_tbl(j).end_date;
        l_k_line_rec.customer_id          := l_header_tbl(i).party_id;
        l_k_line_rec.start_date_active    := l_line_tbl(j).start_date;
        l_k_line_rec.end_date_active      := l_line_tbl(j).end_date;
        l_k_line_rec.quantity             := l_line_tbl(j).quantity;
        l_k_line_rec.uom_code             := l_line_tbl(j).uom_code;
        l_k_line_rec.net_amount           := l_net_amount;
        l_k_line_rec.currency_code        := l_header_tbl(i).currency;
        oks_contracts_pub.create_service_line(p_k_line_rec         => l_k_line_rec,
                                              p_Contact_tbl        => l_line_contacts_tbl,
                                              p_line_sales_crd_tbl => l_line_sales_crd_tbl,
                                              x_service_line_id    => l_service_line_id,
                                              x_return_status      => l_return_status,
                                              x_msg_count          => l_msg_count,
                                              x_msg_data           => l_msg_data);
        IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
    OPEN coverage_cur (l_line_tbl(j).srv_id, l_header_tbl(i).organization_id);
          FETCH coverage_cur INTO l_tmp_cle_id;
          CLOSE coverage_cur;
    
    UPDATE oks_k_lines_b
          SET Standard_Cov_YN = 'Y',
            coverage_id = l_tmp_cle_id
          WHERE cle_id = l_service_line_id;
    
          OPEN subline_cur(l_line_tbl(j).order_line_id);
          FETCH subline_cur BULK COLLECT INTO l_subline_tbl;
          CLOSE subline_cur;
          FOR k IN l_subline_tbl.FIRST .. l_subline_tbl.LAST
          LOOP
            l_K_covd_rec.k_id                := l_chrid;
            l_K_covd_rec.Attach_2_Line_id    := l_service_line_id;
            l_k_covd_rec.line_number         := j||'.'||k;
            l_k_covd_rec.product_sts_code    := l_sts_code;
            l_k_covd_rec.customer_product_id := l_subline_tbl(k).customer_product_id;
            l_k_covd_rec.product_desc        := l_subline_tbl(k).product_desc;
            l_k_covd_rec.product_start_date  := l_line_tbl(j).start_date;
            l_k_covd_rec.product_end_date    := l_line_tbl(j).end_date;
            l_k_covd_rec.quantity            := l_line_tbl(j).quantity;
            l_k_covd_rec.uom_code            := l_line_tbl(j).uom_code;
            l_k_covd_rec.list_price          := l_header_tbl(i).price_list_id;
            l_k_covd_rec.negotiated_amount   := l_negotiated_amount;
            l_k_covd_rec.currency_code       := l_header_tbl(i).currency;
            l_k_covd_rec.line_renewal_type   := l_renewal_type;
            oks_contracts_pub.create_covered_line(p_k_covd_rec   => l_k_covd_rec,
                                                  p_price_attribs => l_price_attribs_in,
                                                  x_cp_line_id    => l_cp_line_id,
                                                  x_return_status => l_return_status,
                                                  x_msg_count     => l_msg_count,
                                                  x_msg_data      => l_msg_data);
            IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
              dbms_output.put_line ('Error while creating the Contract Subline for order#: '||l_header_tbl(i).order_number);
              IF NVL(l_msg_count,0) > 0 THEN
                FOR p IN 1..l_msg_count
                LOOP
                  dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
                END LOOP;
              END IF;
     EXIT;
            END IF;
          END LOOP;
  END IF;
  IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
    dbms_output.put_line ('Error while creating the Contract Line for order#: '||l_header_tbl(i).order_number);
          IF NVL(l_msg_count,0) > 0 THEN
            FOR p IN 1..l_msg_count
            LOOP
              dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
            END LOOP;
          END IF;
          ROLLBACK;
    EXIT;
        END IF;
      END LOOP;
 dbms_output.put_line('Contract Created Successfully for Order#'||l_header_tbl(i).order_number||'. Contract ID: '||l_chrid);   
    END IF;
  END LOOP;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line ('Error: '||SQLERRM);
  ROLLBACK;
END;
/

"Compile error: User-defined type not defined - Dim oParser As New SAXXMLReader60" in Oracle Web ADI

Issue:

Getting the below error while downloading the Excel in Web ADI.

System Configuration:

Windows 8.1 or higher and  MS Excel 2010 or higher.

Root Cause:

This error occurs due to excel reference "Microsoft XML, v6.0" is not enabled. With the combination of Windows 8.1 or higher and MS Excel 2010 or higher, this reference doesn't enable automatically. So through the program, we need to enable it.

Solution:           

1. Click on below link and download the file "WebADI-Addins-MSXML6.zip" and extract it in your local system at the path like (eg.<C:\Users\<User Name>\AppData\Roaming\Microsoft\AddIns>).

    https://drive.google.com/open?id=19xMwOEVc1rIh1nU5lsN7d1UBg_X7G6lP

2. Open the MS Excel and navigate to "File > Options > Add-ins"
3. At the bottom under Manage, select Excel Add-ins and click the Go.
4. Click on the Browse.. button and find the location where "WebADI-Addins-MSXML6.xla" was saved and select it.
5. Select the checkbox to enable the Add-in and click on Ok.
6. Close Excel and test the issue.