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.

Contract Query: Drill down From Contract to AR Invoice

SELECT okh.contract_number,
   okh.start_date contract_start_date,
   okh.end_date contract_end_date,
   oksl.line_number Sub_line_number,
   okbc.bill_action,
   okbt.trx_number invoice_number,
   okbs.date_billed_from,
   okbs.date_billed_to,
  (SELECT a.segment1
   FROM mtl_system_items_b a,
     csi_item_instances b,
okc_k_items c
   WHERE a.inventory_item_id = b.inventory_item_id
     and c.dnz_chr_id = okh.id
     and c.jtot_object1_code = 'OKX_CUSTPROD'
     and b.instance_id = c.object1_id1
     and a.organization_id = b.inv_master_organization_id
     and c.cle_id = oksl.id) product_item,
  (SELECT b.serial_number
   FROM mtl_system_items_b a,
     csi_item_instances b,
okc_k_items c
   WHERE a.inventory_item_id = b.inventory_item_id
     and c.dnz_chr_id = okh.id
     and c.jtot_object1_code = 'OKX_CUSTPROD'
     and b.instance_id = c.object1_id1
     and a.organization_id = b.inv_master_organization_id
     and c.cle_id = oksl.id) serial_number
FROM okc_k_headers_all_b okh,
  okc_k_lines_b okl,
  okc_k_lines_b oksl,
  oks_bill_sub_lines okbs,
  oks_bill_txn_lines okbt,
  oks_bill_cont_lines okbc
WHERE okh.id = okl.chr_id
  AND okl.id = oksl.cle_id
  AND oksl.id = okbs.cle_id
  AND okbs.bcl_id = okbt.bcl_id
  AND okbt.bcl_id = okbc.id
--AND okbt.trx_number = '91107083'
  AND okh.contract_number = 'SMFR00069'
ORDER BY okbt.trx_number;

Script to update Contract Header, Contract Lines and Cascading Attributes

DECLARE
  l_api_version        CONSTANT NUMBER := 1.0;
  l_init_msg_list       CONSTANT    VARCHAR2(1) := 'T'; 
  l_return_status      VARCHAR2(1);
  l_msg_count          NUMBER;
  l_msg_data           VARCHAR2(2000);
  l_chrv_tbl_in        okc_contract_pub.chrv_tbl_type;
  l_chrv_tbl_out       okc_contract_pub.chrv_tbl_type;
  l_clev_tbl_in        okc_contract_pub.clev_tbl_type;
  l_clev_tbl_out       okc_contract_pub.clev_tbl_type;
  l_cle_tbl_in         okc_contract_pub.clev_tbl_type;
  l_cle_tbl_out        okc_contract_pub.clev_tbl_type;
  i                    NUMBER  := 1;
  
  l_count number :=0;
  l_id okc_k_headers_all_b.id%TYPE;
  l_end_date okc_k_headers_all_b.end_date%TYPE;
  
  CURSOR header_cur IS
  SELECT id,
    contract_number,
    start_date,
    end_date
  FROM okc_k_headers_all_b
  WHERE contract_number = 'CJ-10123';
 -- where authoring_org_id = <ORG_ID>;
  
  CURSOR line_cur (l_header_id NUMBER)IS
  SELECT id,
    line_number
  FROM okc_k_lines_b
  WHERE chr_id = l_header_id;
  
  CURSOR subline_cur (l_header_id NUMBER)IS
  SELECT id
  FROM okc_k_lines_b
  WHERE dnz_chr_id = l_header_id;
  
BEGIN  
FOR header_rec IN header_cur LOOP
l_chrv_tbl_in(1).ID := header_rec.id;
l_chrv_tbl_in(1).start_date := header_rec.start_date;
l_chrv_tbl_in(1).end_date    := LAST_DAY(header_rec.start_date);

 okc_contract_pub.update_contract_header 
              (
                p_api_version       => l_api_version,
                p_init_msg_list     => FND_API.G_TRUE,
                x_return_status     => l_return_status,
                x_msg_count         => l_msg_count,
                x_msg_data          => l_msg_data,
                p_restricted_update => 'N',
                p_chrv_tbl          => l_chrv_tbl_in,
                x_chrv_tbl          => l_chrv_tbl_out
               );
  IF (l_return_status <>  'S') THEN
     dbms_output.put_line ('Error while updating the Contract Header for Contract '||header_rec.contract_number);
     dbms_output.put_line ('Error: '||l_msg_data);
  ELSE
    --init_contract_line (l_clev_tbl_in);
  --init_contract_oks_line (l_klnv_tbl_in);
    FOR line_rec IN line_cur(header_rec.id) LOOP
      l_clev_tbl_in(i).id                := line_rec.id;
      l_clev_tbl_in(i).start_date        := header_rec.start_date;
      l_clev_tbl_in(i).end_date            := LAST_DAY(header_rec.start_date);
      i := i+1;
    END LOOP;
    
    IF (i > 1) THEN
      okc_contract_pub.update_contract_line 
            (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_restricted_update    => 'F',
             p_clev_tbl             => l_clev_tbl_in,
             x_clev_tbl             => l_clev_tbl_out);
      IF l_return_status <> 'S' THEN
         dbms_output.put_line ('Error while updating the Contract Line for Contract '||header_rec.contract_number);
         dbms_output.put_line ('Error: '||l_msg_data);
      ELSE
         FOR line_rec IN line_cur(header_rec.id) LOOP
           oks_coverages_pvt.update_coverage_effectivity(
                  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_service_Line_Id => line_rec.id,
                  p_New_Start_Date  => header_rec.start_date,
                  p_New_End_Date    => LAST_DAY(header_rec.start_date));
                  
          IF l_return_status <> 'S' THEN
            dbms_output.put_line ('Error while updating the Contract Coverage for Contract '||header_rec.contract_number);
            dbms_output.put_line ('Error: '||l_msg_data);
          ELSE
            oks_pm_programs_pvt.adjust_pm_program_schedule
                 (p_api_version          => l_api_version,
                  p_init_msg_list        => l_init_msg_list,
                  p_contract_line_id     => line_rec.id,
                  p_new_start_date       => header_rec.start_date,
                  p_new_end_date         => LAST_DAY(header_rec.start_date),
                  x_return_status        => l_return_status,
                  x_msg_count            => l_msg_count,
                  x_msg_data             => l_msg_data
                  );
            IF l_return_status <> 'S' THEN
              dbms_output.put_line ('Error while updating the PM Schedule for Contract '||header_rec.contract_number);
              dbms_output.put_line ('Error: '||l_msg_data);
            ELSE
              i :=1;
              --init_contract_line (l_cle_tbl_in);
            --init_contract_oks_line (l_kln_tbl_in);
              FOR subline_rec IN subline_cur(header_rec.id) LOOP
                l_cle_tbl_in(i).id                    := subline_rec.id;
                l_cle_tbl_in(i).start_date            := header_rec.start_date;
                l_cle_tbl_in(i).end_date                := LAST_DAY(header_rec.start_date);
              --l_cle_tbl_in(i).object_version_number := l_object_version_number; 
                i := i+1;
              END LOOP;
                okc_contract_pub.update_contract_line 
                      (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_restricted_update    => 'F',
                       p_clev_tbl             => l_cle_tbl_in,
                       x_clev_tbl             => l_cle_tbl_out);
              IF l_return_status <> 'S' THEN
                dbms_output.put_line ('Error while updating the Sub Line for Contract '||header_rec.contract_number);
                dbms_output.put_line ('Error: '||l_msg_data);
              ELSE
                dbms_output.put_line ('Sript Updated the END Date successfully for Contract '||header_rec.contract_number);
               COMMIT;
              END IF;
            END IF;
          END IF;
         END LOOP;
      END IF;
    END IF;
  END IF;
END LOOP;
END;
/

Script to send emails

This summary is not available. Please click here to view the post.

Steps to get all the parameter of a Business Event

1. Create a temp table MY_LOG_TABLE using below script, this will store parameters information.

2. Compile the below package TEST_BUSINESSEVENT.

3. Create a subscription of that business event using function TEST_BUSINESSEVENT.UPDATE_ASGN.

4. Perform the steps to fire the business event.

5. Check the table MY_LOG_TABLE for the parameters detail.

Table Script: 

create table my_log_table (PARAMETERS VARCHAR2(100));

Package:

create or replace package TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2);
function update_asgn (p_subscription_guid in raw,
                      p_event in out WF_EVENT_T) return varchar2;
END;
/
create or replace package body TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2) IS pragma autonomous_transaction;
begin
  insert into my_log_table values(t);
  commit;

end;

function update_asgn (p_subscription_guid in raw,
                      p_event in out WF_EVENT_T) return varchar2 is
                     
    l_wf_parameter_list_t wf_parameter_list_t;
    i number := 1;
    c number;
    l_key varchar2(30);
    l_val varchar2(2000);
begin
  l_wf_parameter_list_t := p_event.getParameterList();
  c := l_wf_parameter_list_t.count();
 
  log_me('event = ' || p_event.getEventName());
  log_me('count : ' || c);
 
  while (i<=c)
  loop
   
    l_key := l_wf_parameter_list_t(i).getName();
    l_val := l_wf_parameter_list_t(i).getValue();
   
    log_me(l_key || '=' || l_val);
   
    i := i + 1;
  end loop;
 
  return 'SUCCESS';
  exception
    when others then
      log_me('errm' || sqlerrm);
end;
end;

Dependent LOV in WEB ADI (When LOV is dependent on two fields)

Assume we have three fields first Resource Name, second Destination Organization and third Destination Sub-inventory. Now we are going to create LOV on Destination Sub-inventory which is dependent on Resource Name and Destination Organization.

For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component

1.  SQL:

package holx.oracle.apps.icx;

import java.sql.Connection;
import java.sql.SQLException;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.utilities.sql.BneBaseSQL;

public class HolxDestSubSQL extends BneBaseSQL {
    public HolxDestSubSQL(BneWebAppsContext paramBneWebAppsContext, String s1, String s2,
                                    String paramString) throws SQLException,
                                                               BneException {
        Connection connection = paramBneWebAppsContext.getJDBCConnection();
        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("SELECT SECONDARY_INVENTORY_NAME, DESCRIPTION FROM MTL_SUBINVENTORIES_VAL_V WHERE SECONDARY_INVENTORY_NAME IN (SELECT DISTINCT SUBINVENTORY_CODE from CSP_RS_SUBINVENTORIES_V WHERE CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(EFFECTIVE_DATE_END,SYSDATE) AND UPPER(RESOURCE_NAME) = UPPER(:1) AND UPPER(ORGANIZATION_CODE) = UPPER(:2)) ");
       
        if ((paramString != null) && (!paramString.trim().equals(""))) {
            stringBuffer.append(" AND " + paramString);
        }
       
        setQuery(connection, stringBuffer.toString());
    }
}

2. Validator:

package holx.oracle.apps.icx;

import java.util.Hashtable;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneFatalException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.validators.BneUploadValidator;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSQLStatement;

import holx.oracle.apps.icx.HolxDestSubSQL;

public class HolxDestSubValidator extends BneUploadValidator {

    public String[] getDomainParameters() {
        return new String[] {"P_RESOURCE_NAME", "P_DESTINATION_ORGANIZATION"};
    }

    public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
                                        Hashtable paramHashtable,
                                        BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {

        HolxDestSubSQL HolxDestSubSQL = null;
        BneResultSet bneResultSet = null;
        BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

        if (paramBneCompositeSQLCriteria != null)
            bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
        try
        {
        String s1 = (String)paramHashtable.get("P_RESOURCE_NAME");
        String s2 = (String)paramHashtable.get("P_DESTINATION_ORGANIZATION");

        if(s1 == null || "".equals(s1) || s2 == null || "".equals(s2)) {
            throw new BneMissingParameterException("Error while fetching Source Name in HolxDestSubValidator");
        }
            HolxDestSubSQL holxdestsubsql =
                    new HolxDestSubSQL(paramBneWebAppsContext, s1,s2,
                                                 bneSQLStatement1.getStatement());
            BneSQLStatement bneSQLStatement2 =
                new BneSQLStatement(holxdestsubsql.getQuery(),
                                    new Object[] { s1,s2 });

            bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
            bneResultSet =
                    holxdestsubsql.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
        } catch (Exception exception) {
            throw new BneFatalException(exception.toString());
        }
        return bneResultSet;
    }
}

3. Component:

package holx.oracle.apps.icx;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Vector;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.exception.BneParameterException;
import oracle.apps.bne.exception.BneSQLException;
import oracle.apps.bne.framework.BneBajaContext;
import oracle.apps.bne.framework.BneBajaPage;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.component.BneAbstractListOfValues;
import oracle.apps.bne.parameter.BneParameter;
import oracle.apps.bne.repository.BneResourceString;
import oracle.apps.bne.utilities.BneUIXUtils;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSimpleSQLCriteria;
import oracle.apps.bne.webui.control.BneLOVControlBean;
import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestSubValidator;

public class HolxDestSubComponent extends BneAbstractListOfValues {

    private HolxDestSubValidator VALIDATOR = null;
    private String[] VALIDATOR_PARAMS = null;

    private String FILTERFIELD = null;
    private String FILTERVALUE = null;

    public String getLOVProcessorType() {
        return "TABLE";
    }

    public void init(BneBajaContext paramBneBajaContext, Page paramPage,
                     PageEvent paramPageEvent) {
        if (VALIDATOR == null) {
            VALIDATOR = new HolxDestSubValidator();
            VALIDATOR_PARAMS = VALIDATOR.getDomainParameters();
        }
    }

    public BneBajaPage handleListOfValues(BneBajaContext paramBneBajaContext,
                                          Page paramPage,
                                          PageEvent paramPageEvent,
                                          BneLOVControlBean paramBneLOVControlBean) throws BneException {
        BneWebAppsContext bneWebAppsContext =
            paramBneBajaContext.getBneWebAppsContext();
        BneCompositeSQLCriteria bneCompositeSQLCriteria = null;
        Hashtable hashtable = new Hashtable();

        handlePageParameters(paramPageEvent);

        for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
            String str2 =
                getParameterValue(bneWebAppsContext, VALIDATOR_PARAMS[i]);
            if (str2 == null)
                continue;
            hashtable.put(VALIDATOR_PARAMS[i], str2);
        }

        if ((FILTERVALUE != null) && (!FILTERVALUE.trim().equals(""))) {
            bneCompositeSQLCriteria = new BneCompositeSQLCriteria();
            if (FILTERFIELD != null && !FILTERFIELD.equals("")) {
                BneSimpleSQLCriteria bneSimpleSQLCriteria;
                if (FILTERFIELD.equals("SECONDARY_INVENTORY_NAME"))
                    bneSimpleSQLCriteria =
                            new BneSimpleSQLCriteria(0, "SECONDARY_INVENTORY_NAME", 0,
                                                     9, FILTERVALUE, 2);
                else
                    bneSimpleSQLCriteria =
                            new BneSimpleSQLCriteria(0, "DESCRIPTION",0,
                                                     9, FILTERVALUE, 2);
                                                   
                bneSimpleSQLCriteria.setSearchsCaseInsensitivity(true);
                bneCompositeSQLCriteria.addCriteria(bneSimpleSQLCriteria);
            }

        }

        setTableFilter(true);
        setTableData(getTableData(bneWebAppsContext, paramBneLOVControlBean,
                                  hashtable, bneCompositeSQLCriteria));
        return null;
    }

    public void getListOfValueParameters() throws BneParameterException {
        for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
            String str1 = VALIDATOR_PARAMS[i];
            String str2 ="Test" + str1 + ".";
            addComponentParameter(new BneParameter(str1, "", str2));
        }
    }

    private void handlePageParameters(PageEvent paramPageEvent) throws BneException {
        FILTERFIELD =
                BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterField");
        FILTERVALUE =
                BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterValue");
    }

    public DictionaryData[] getTableData(BneWebAppsContext paramBneWebAppsContext,
                                         BneLOVControlBean paramBneLOVControlBean,
                                         Hashtable paramHashtable,
                                         BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
        DictionaryData dictionaryData = null;
        Vector vector = new Vector();
        BneResultSet bneResultSet = null;
        ResultSetMetaData resultSetMetaData = null;
        try {
            String str = null;

            bneResultSet =
                    VALIDATOR.getDomainValues(paramBneWebAppsContext, paramHashtable,
                                              paramBneCompositeSQLCriteria);

            if (bneResultSet != null) {
                resultSetMetaData = bneResultSet.getResultSet().getMetaData();

                while (bneResultSet.next()) {
                    dictionaryData = new DictionaryData();

                    for (int i = 1; i <= resultSetMetaData.getColumnCount();
                         i++) {
                        str = bneResultSet.getString(i);

                        if (str == null) {
                            dictionaryData.put(resultSetMetaData.getColumnName(i),
                                               "");
                        } else {
                            dictionaryData.put(resultSetMetaData.getColumnName(i),
                                               str);
                        }
                    }

                    vector.addElement(dictionaryData);
                }
            }
        } catch (SQLException sqlException) {
            throw new BneSQLException(BneResourceString.getMlsString(-1L, -1L,
                                                                     "Error in HolxDestSubComponent: Can't get Source Sub-inventory."),
                                      sqlException);
        } catch (BneMissingParameterException bneMissingParameterException) {
            paramBneLOVControlBean.addError(bneMissingParameterException.getMessage());
        }

        DictionaryData[] arrayOfDictionaryData =
            new DictionaryData[vector.size()];

        for (int i = 0; i < vector.size(); i++) {
            arrayOfDictionaryData[i] = ((DictionaryData)vector.elementAt(i));
        }

        return arrayOfDictionaryData;
    }

    public String getComponentName() {
        return "DestSub";
    }

    public String getComponentVersion() {
        return "R12";
    }
}



Keep these three files on server and run the below script.

begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID       => 20003,
P_INTERFACE_CODE       => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME   => 'P_DESTINATION_SUBINVENTORY',
P_JAVA_CLASS           => 'holx.oracle.apps.icx.HolxDestSubComponent',
P_WINDOW_CAPTION       => 'Destination Subinventory',
P_WINDOW_WIDTH         => 500,
P_WINDOW_HEIGHT        => 500,
P_TABLE_BLOCK_SIZE     => 50,
P_TABLE_COLUMNS        => 'SECONDARY_INVENTORY_NAME,DESCRIPTION', 
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_SUBINVENTORY',
P_TABLE_COLUMN_ALIAS   => 'P_DESTINATION_SUBINVENTORY,SECONDARY_INVENTORY_NAME',
P_TABLE_HEADERS        => 'Sub Inventory,Description',
P_TABLE_SORT_ORDER     => 'Yes,Yes',
P_USER_ID              => 161909); 
commit;
end;
/