"Flexfield View Generator" Program code in PL/SQL

DECLARE
  TYPE global_column_rec_type IS RECORD(
           view_column_name               VARCHAR2(40),
           application_column_name        VARCHAR2(50),
          global_application_column_name VARCHAR2(50));
 TYPE t_global_column_tab IS TABLE OF  global_column_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE column_rec_type IS RECORD(
          view_column_name   VARCHAR2(40));
 TYPE t_column_tab IS TABLE OF column_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE attribute_rec_type IS RECORD(
          application_column_name        VARCHAR2(50),
          descriptive_flex_context_code  VARCHAR2(30));
 TYPE t_attribute_tab IS TABLE OF attribute_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE context_rec_type IS RECORD(
          descriptive_flex_context_code VARCHAR2(30));
 TYPE t_context_tab IS TABLE OF context_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE con_attr_rec_type IS RECORD(
          application_column_name        VARCHAR2(30));
 TYPE t_con_attr_tab IS TABLE OF con_attr_rec_type
 INDEX BY BINARY_INTEGER;

  p_application             VARCHAR2(3)  := 'ONT';
  p_flex_field_name    VARCHAR2(50) := 'OE_LINE_ATTRIBUTES';
  l_table_name             VARCHAR2(30);
  l_view_name             VARCHAR2(30);
  l_context_name         VARCHAR2(30);
  l_context_prompt      VARCHAR2(50);
  l_global_column_tab t_global_column_tab;
  l_column_tab             t_column_tab;
  l_attribute_tab            t_attribute_tab;
  l_context_tab              t_context_tab;
  l_con_attr_tab             t_con_attr_tab;
  l_view_header            CLOB;
  l_view_line                 CLOB;
  l_view                         CLOB;
  l_attribute                   CLOB;
  l_context                    CLOB;

  CURSOR cur_global_column
  IS
    SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name,
           DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
                                 'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 fcu.application_column_name) application_column_name,
fcu.application_column_name global_application_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_flex_value_sets fs,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name      = fc.descriptive_flexfield_name
    AND fcu.flex_value_set_id               = fs.flex_value_set_id(+)
    AND fcu.descriptive_flexfield_name      = p_flex_field_name
    AND fcu.application_id                  = fa.application_id
    AND fcu.descriptive_flex_context_code   = 'Global Data Elements'
    AND fa.application_short_name           = p_application
    AND fc.enabled_flag                     = 'Y'
    AND fcu.enabled_flag                    = 'Y'
ORDER BY fcu.application_column_name;

  CURSOR cur_column
  IS
    SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name      = fc.descriptive_flexfield_name
    AND fcu.descriptive_flexfield_name      = p_flex_field_name
    AND fcu.application_id                  = fa.application_id
    AND fa.application_short_name           = p_application
AND fcu.descriptive_flex_context_code   <> 'Global Data Elements'
    AND fc.enabled_flag                     = 'Y'
    AND fcu.enabled_flag                    = 'Y'
    ORDER BY UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_'));

  CURSOR cur_attribute (p_end_user_column_name IN VARCHAR2)
  IS
    SELECT DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
                                 'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 fcu.application_column_name) application_column_name,
      fcu.descriptive_flex_context_code
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_flex_value_sets fs,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code                                  = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name                                       = fc.descriptive_flexfield_name
    AND fcu.flex_value_set_id                                                = fs.flex_value_set_id(+)
    AND fcu.descriptive_flexfield_name                                       = p_flex_field_name
    AND fcu.application_id                                                   = fa.application_id
    AND fa.application_short_name                                            = p_application
    AND fcu.enabled_flag                                                     = 'Y'
    AND fc.enabled_flag                                                      = 'Y'
    AND UPPER(regexp_replace(fcu.end_user_column_name,'[^[:alnum:]""]','_')) = p_end_user_column_name
    ORDER BY fcu.application_column_name,
      fcu.descriptive_flex_context_code;
   
  CURSOR cur_context
  IS
    SELECT descriptive_flex_context_code
    FROM fnd_descr_flex_contexts fc,
      fnd_application fa
    WHERE descriptive_flexfield_name    = p_flex_field_name
    AND fc.application_id               = fa.application_id
    AND fa.application_short_name       = p_application
AND descriptive_flex_context_code   <> 'Global Data Elements'
    AND enabled_flag                    = 'Y'
    ORDER BY descriptive_flex_context_code;
 
  CURSOR cur_con_attr (p_dff_context_code IN VARCHAR2)
  IS
    SELECT application_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_application fa
    WHERE descriptive_flexfield_name  = p_flex_field_name
    AND fcu.application_id            = fa.application_id
    AND fa.application_short_name     = p_application
    AND enabled_flag                  = 'Y'
    AND descriptive_flex_context_code = p_dff_context_code
    ORDER BY to_number(SUBSTR(application_column_name,10));

  CURSOR cur_table_name
  IS
    SELECT fd.application_table_name,
      fd.concatenated_segs_view_name,
      fd.context_column_name,
      UPPER(regexp_replace(fdt.form_context_prompt,'[^[:alnum:]""]','_')) form_context_prompt
    FROM fnd_descriptive_flexs fd,
      FND_DESCRIPTIVE_FLEXS_TL fdt,
      fnd_application fa
    WHERE fd.descriptive_flexfield_name = fdt.descriptive_flexfield_name
    AND fd.application_id               = fdt.application_id
    AND fdt.language                    = userenv('LANG')
    AND fd.descriptive_flexfield_name   = p_flex_field_name
    AND fd.application_id               = fa.application_id
    AND fa.application_short_name       = p_application;
BEGIN
  OPEN cur_table_name;
  FETCH cur_table_name
  INTO l_table_name,
    l_view_name,
    l_context_name,
    l_context_prompt;
  CLOSE cur_table_name;

  OPEN cur_global_column;
  FETCH cur_global_column BULK COLLECT INTO l_global_column_tab;
  CLOSE cur_global_column;

  OPEN cur_column;
  FETCH cur_column BULK COLLECT INTO l_column_tab;
  CLOSE cur_column;

  OPEN cur_context;
  FETCH cur_context BULK COLLECT INTO l_context_tab;
  CLOSE cur_context;

  l_view_header         := 'CREATE OR REPLACE FORCE VIEW '||l_view_name||' (ROW_ID, '||l_context_prompt;
  l_view_line           := 'SELECT ROWID, '||l_context_name||', ';
  IF (l_global_column_tab.COUNT > 0 )THEN
    FOR m IN l_global_column_tab.FIRST .. l_global_column_tab.LAST
LOOP
  l_view_header := l_view_header||', '||l_global_column_tab(m).view_column_name;
  l_view_line   := l_view_line||l_global_column_tab(m).application_column_name||', ';
  l_context     := l_context || l_global_column_tab(m).global_application_column_name ||'|| ''.'' ||';
END LOOP;
  END IF;
  l_context := l_context||'(DECODE ('||l_context_name||', ';
  IF (l_column_tab.COUNT > 0) THEN
    FOR i IN l_column_tab.FIRST .. l_column_tab.LAST
    LOOP
      l_view_header := l_view_header||', '||l_column_tab(i).view_column_name;
      l_view_line   := l_view_line||'(DECODE ('||l_context_name||', ';
   
      OPEN cur_attribute(l_column_tab(i).view_column_name);
      FETCH cur_attribute BULK COLLECT INTO l_attribute_tab;
      CLOSE cur_attribute;
   
      IF (l_attribute_tab.COUNT > 0) THEN
        FOR j IN l_attribute_tab.FIRST .. l_attribute_tab.LAST
        LOOP
          l_view_line := l_view_line||''''|| l_attribute_tab(j).descriptive_flex_context_code ||''''||', '|| l_attribute_tab(j).application_column_name||','||CHR(10);
        END LOOP;
      END IF;
      l_view_line := l_view_line||'NULL )),'||CHR(10);
    END LOOP;
  END IF;
  IF (l_context_tab.COUNT > 0) THEN
    FOR k IN l_context_tab.FIRST .. l_context_tab.LAST
    LOOP
      OPEN cur_con_attr(l_context_tab(k).descriptive_flex_context_code);
      FETCH cur_con_attr BULK COLLECT INTO l_con_attr_tab;
      CLOSE cur_con_attr;
      IF (l_con_attr_tab.COUNT > 0) THEN
        FOR l IN l_con_attr_tab.FIRST .. l_con_attr_tab.LAST
        LOOP
          l_attribute := l_attribute || '|| ''.'' ||' ||l_con_attr_tab(l).application_column_name;
        END LOOP;
      END IF;
      l_context   := l_context||''''||l_context_tab(k).descriptive_flex_context_code||''''||', '||l_context_name||''||l_attribute||','||CHR(10);
      l_attribute := NULL;
    END LOOP;
  END IF;
  l_context := l_context||'NULL ))';
  l_view    := l_view_header||', CONCATENATED_SEGMENTS ) AS'||CHR(10)||l_view_line||l_context||CHR(10)|| 'FROM '||l_table_name;
  BEGIN
    EXECUTE IMMEDIATE l_view;
  EXCEPTION
  WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.log,'Error while compiling the view: '||SQLERRM);
    retcode := 2;
  END;
EXCEPTION
WHEN OTHERS THEN
  errbuf  := SQLERRM;
  retcode := 2;
END;

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;