-- Start of DDL script for D2K_API_UTIL
-- Generated 10-Oct-00  12:02:13 pm
-- from oradev-REPOS:1

-- Package D2K_API_UTIL

CREATE OR REPLACE
PACKAGE d2k_api_util IS -- <Specification>
----------------------------------------------------------------------------------------------------------
-- Copywright 2000, chet  west
----------------------------------------------------------------------------------------------------------
--
-- Purpose: This package contains all of tthe code necessary to
--          perform various Designer 6i APPI functions.
--
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- -------------------------------------------
-- Chet        09/01/2000 Initial Creationn
   --
   -- EXCEPTIONS
   gx_cannot_init_appsys   EXCEPTION;
   --
   -- VARIABLES
   gv_show_messages        BOOLEAN := TRUE;
   gv_activity_status      VARCHAR2(1);
   gv_activity_warning     VARCHAR2(1);
   gv_app_sys              ci_application_systems.name%TYPE := NULL;
   --
   --
   PROCEDURE initialize_api (p_context_workarea VARCHAR2
                            ,p_application_system VARCHAR2);
   --
   PROCEDURE show_messages_on;
   --
   PROCEDURE show_messages_off;
   --
   FUNCTION create_domain
      (p_name                           IN VARCHAR2
      ,p_format                         IN VARCHAR2
      ,p_maximum_attribute_length       IN NUMBER
      ,p_attribute_precision            IN NUMBER
      ,p_average_attribute_length       IN NUMBER
      ,p_datatype                       IN VARCHAR2
      ,p_maximum_column_length          IN NUMBER
      ,p_column_precision               IN NUMBER
      ,p_average_column_length          IN NUMBER
      ,p_soft_lov                       IN VARCHAR2
      ,p_supertype_name                 IN VARCHAR2
      ,p_default_value                  IN VARCHAR2
      ,p_authority                      IN VARCHAR2
      ,p_derivation                     IN VARCHAR2
      ,p_null_value                     IN VARCHAR2
      ,p_unit_of_measure                IN VARCHAR2
      ,p_description                    IN VARCHAR2
      ) RETURN NUMBER;
   --
   PROCEDURE create_domain_values
      (p_domain_reference               IN NUMBER
      ,p_radio_sequence                 IN NUMBER
      ,p_low_value                      IN VARCHAR2
      ,p_high_value                     IN VARCHAR2
      ,p_abbreviation                   IN VARCHAR2
      ,p_meaning                        IN VARCHAR2
   );
   --
   FUNCTION create_entity (p_entity_name      IN VARCHAR2
                          ,p_entity_short_nm  IN VARCHAR2
                          ,p_entity_plural_nm IN VARCHAR2
                          ) RETURN NUMBER;
   --
   FUNCTION create_attribute (p_entity_name           IN VARCHAR2
                             ,p_attribute_name        IN VARCHAR2
                             ,p_attribute_sequence    IN NUMBER
                             ,p_attribute_optional    IN VARCHAR2
                             ,p_attribute_domain_nm   IN VARCHAR2
                             ,p_attribute_dataformat  IN VARCHAR2
                             ,p_attribute_maxlen      IN NUMBER
                             ,p_attribute_precision   IN NUMBER
                             ,p_attribute_comment     IN VARCHAR2
                             ) RETURN NUMBER;
   --
   FUNCTION create_table (p_table_name       IN VARCHAR2
                         ,p_alias            IN VARCHAR2
                         ,p_display_title    IN VARCHAR2
                         ,p_journal_location IN VARCHAR2 DEFAULT 'NONE'
                         ,p_comment          IN VARCHAR2
                         ) RETURN VARCHAR2;
   --
   FUNCTION create_declarative_view (p_view_name       IN VARCHAR2
                                    ,p_alias           IN VARCHAR2
                                    ,p_display_title   IN VARCHAR2
                                    ,p_comment         IN VARCHAR2
                                    ) RETURN VARCHAR2;
   --
   FUNCTION create_freeformat_view  (p_view_name       IN VARCHAR2
                                    ,p_alias           IN VARCHAR2
                                    ,p_display_title   IN VARCHAR2
                                    ,p_comment         IN VARCHAR2
                                    ,p_select_text     IN VARCHAR2
                                    ,p_where_clause    IN VARCHAR2
                                    ,p_optimizer_hint  IN VARCHAR2
                                    ) RETURN VARCHAR2;
   --
   FUNCTION add_view_base_table (p_view_name       IN VARCHAR2
                                ,p_table_name      IN VARCHAR2
                                )RETURN NUMBER;
   --
   FUNCTION create_column (p_owner_obj_name  IN VARCHAR2
                          ,p_owner_obj_type  IN VARCHAR2
                          ,p_col_name        IN VARCHAR2
                          ,p_col_sequence    IN NUMBER
                          ,p_col_null_ind    IN VARCHAR2 DEFAULT 'NULL'
                          ,p_col_domain_nm   IN VARCHAR2
                          ,p_col_datatype    IN VARCHAR2
                          ,p_col_maxlen      IN NUMBER
                          ,p_col_precision   IN NUMBER
                          ,p_col_comment     IN VARCHAR2
                          ,p_col_help_text   IN VARCHAR2 DEFAULT NULL
                          ,p_col_base_ref    IN NUMBER   DEFAULT NULL
                          ,p_col_autogen     IN VARCHAR2 DEFAULT NULL
                          ,p_col_dflt_value  IN VARCHAR2 DEFAULT NULL
                          ,p_col_disp_flag   IN VARCHAR2 DEFAULT 'Y'
                          ,p_col_disp_type   IN VARCHAR2 DEFAULT 'TEXT'
                          ,p_col_disp_height IN NUMBER   DEFAULT 1
                          ,p_col_disp_length IN NUMBER   DEFAULT 10
                          ,p_col_disp_seq    IN NUMBER   DEFAULT NULL
                          ,p_col_justify     IN VARCHAR2 DEFAULT 'LEFT'
                          ,p_col_prompt      IN VARCHAR2 DEFAULT NULL
                          ,p_col_srv_dflt    IN VARCHAR2 DEFAULT 'N'
                          ,p_col_srv_drv     IN VARCHAR2 DEFAULT 'N'
                          ,p_col_soft_lov    IN VARCHAR2 DEFAULT 'N'
                          ,p_col_uppercase   IN VARCHAR2 DEFAULT 'N'
                          ) RETURN NUMBER;
   --
END d2k_api_util;  -- </Specification>
/

-- End of DDL script for D2K_API_UTIL

-- Start of DDL script for D2K_API_UTIL
-- Generated 10-Oct-00  12:02:18 pm
-- from oradev-REPOS:1

-- Package body D2K_API_UTIL

CREATE OR REPLACE
PACKAGE BODY d2k_api_util IS -- <BODY>
   -- Private Component Declarations
   --
   -- Procedures and Functions
   --
   PROCEDURE instantiate_messages;
   PROCEDURE transaction_start;
   PROCEDURE transaction_end;
   --
   -- Code
   --
   -- Start an API Transaction (activity)
   PROCEDURE transaction_start IS
   BEGIN
      -- Open an activity
      -- This issues a savepoint, so incomplete changes
      -- can be rolled back to here.
      CDAPI.OPEN_ACTIVITY;
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('API Transaction Start.');
      END IF;
   END transaction_start;
   --
   -- End an API Transaction (activity)
   PROCEDURE transaction_end IS
   BEGIN
      CDAPI.VALIDATE_ACTIVITY(gv_activity_status, gv_activity_warning);
      -- Show violation messages here.
      FOR each_violation IN (SELECT * FROM ci_violations) LOOP
         IF gv_show_messages THEN
            DBMS_OUTPUT.PUT_LINE(CDAPI.INSTANTIATE_MESSAGE(each_violation.facility
                                                          ,each_violation.code
                                                          ,each_violation.p0
                                                          ,each_violation.p1
                                                          ,each_violation.p2
                                                          ,each_violation.p3
                                                          ,each_violation.p4
                                                          ,each_violation.p5
                                                          ,each_violation.p6
                                                          ,each_violation.p7
                                                          ));
         END IF;
      END LOOP;
      -- Attempt to close the activity.
      CDAPI.CLOSE_ACTIVITY(gv_activity_status);
      -- If activity did not close, then abort and roll back changes.
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('API Transaction End, status = ' || gv_activity_status || '.');
      END IF;
      IF gv_activity_status != 'Y' THEN
         CDAPI.ABORT_ACTIVITY;
         IF gv_show_messages THEN
            DBMS_OUTPUT.PUT_LINE('API Transaction ABORTED!');
         END IF;
      END IF;
   END transaction_end;
   --
   -- Display API message stack to DBMS_OUTPUT
   PROCEDURE instantiate_messages IS
      v_facility VARCHAR2 (3);
      v_code     NUMBER;
      v_arg1     VARCHAR2 (2000);
      v_arg2     VARCHAR2 (2000);
      v_arg3     VARCHAR2 (2000);
      v_arg4     VARCHAR2 (2000);
      v_arg5     VARCHAR2 (2000);
      v_arg6     VARCHAR2 (2000);
      v_arg7     VARCHAR2 (2000);
      v_arg8     VARCHAR2 (2000);
   BEGIN
      -- Report all violations regardless of the activity status
      IF gv_show_messages THEN
         FOR viol IN (SELECT * FROM ci_violations) LOOP
            dbms_output.put_line (cdapi.instantiate_message (
                                   viol.facility, viol.code,
                                   viol.p0, viol.p1, viol.p2,
                                   viol.p3, viol.p4, viol.p5,
                                   viol.p6, viol.p7));
         END LOOP;
      END IF;
      -- Pop messages off the stack and format them into a single text string
      WHILE cdapi.stacksize > 0 LOOP
         rmmes.pop (v_facility, v_code,
                    v_arg1, v_arg2, v_arg3,
                    v_arg4, v_arg5, v_arg6,
                    v_arg7, v_arg8);
         IF gv_show_messages THEN
            dbms_output.put_line (cdapi.instantiate_message (
                                  v_facility, v_code,
                                  v_arg1, v_arg2, v_arg3,
                                  v_arg4, v_arg5, v_arg6,
                                  v_arg7, v_arg8));
         END IF;
      END LOOP;
   END instantiate_messages;
   --
   -- Initialize the Repository API
   PROCEDURE initialize_api (p_context_workarea VARCHAR2
                            ,p_application_system VARCHAR2) IS
   BEGIN
      -- Initialize the Designer/2000 API
      IF NOT cdapi.initialized THEN
         -- Set the context workarea
         jr_context.set_workarea(p_context_workarea);
         --- Not already initialized
         CDAPI.INITIALIZE(UPPER(p_application_system));
      END IF;
      CDAPI.SET_CONTEXT_APPSYS (UPPER(p_application_system));
      gv_app_sys := UPPER(p_application_system);
   EXCEPTION
      WHEN OTHERS THEN
         RAISE gx_cannot_init_appsys;
   END initialize_api;
   --
   -- Turn on Message display
   PROCEDURE show_messages_on IS
   BEGIN
      gv_show_messages := TRUE;
   END show_messages_on;
   --
   -- Turn off Message display
   PROCEDURE show_messages_off IS
   BEGIN
      gv_show_messages := FALSE;
   END show_messages_off;
   --
   -- Create Domains
   FUNCTION create_domain
      (p_name                           IN VARCHAR2
      ,p_format                         IN VARCHAR2
      ,p_maximum_attribute_length       IN NUMBER
      ,p_attribute_precision            IN NUMBER
      ,p_average_attribute_length       IN NUMBER
      ,p_datatype                       IN VARCHAR2
      ,p_maximum_column_length          IN NUMBER
      ,p_column_precision               IN NUMBER
      ,p_average_column_length          IN NUMBER
      ,p_soft_lov                       IN VARCHAR2
      ,p_supertype_name                 IN VARCHAR2
      ,p_default_value                  IN VARCHAR2
      ,p_authority                      IN VARCHAR2
      ,p_derivation                     IN VARCHAR2
      ,p_null_value                     IN VARCHAR2
      ,p_unit_of_measure                IN VARCHAR2
      ,p_description                    IN VARCHAR2  )
   RETURN NUMBER IS
      --
      lws_supertype_reference   NUMBER;
      dom_rec                   ciodomain.data;
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Domain ' || p_name || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Set the values
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Setting Properties for Domain ' || p_name || '.');
      END IF;
      dom_rec.v.id                        := NULL;
      dom_rec.i.id                        := TRUE;
      dom_rec.v.name                      := p_name;
      dom_rec.i.name                      := TRUE;
      dom_rec.v.description               := p_description;
      dom_rec.i.description               := TRUE;
      dom_rec.v.datatype                  := p_datatype;
      dom_rec.i.datatype                  := TRUE;
      dom_rec.v.format                    := p_format;
      dom_rec.i.format                    := TRUE;
      dom_rec.v.maximum_attribute_length  := p_maximum_attribute_length;
      dom_rec.i.maximum_attribute_length  := TRUE;
      dom_rec.v.attribute_precision       := p_attribute_precision;
      dom_rec.i.attribute_precision       := TRUE;
      dom_rec.v.average_attribute_length  := p_average_attribute_length;
      dom_rec.i.average_attribute_length  := TRUE;
      dom_rec.v.maximum_column_length     := p_maximum_column_length;
      dom_rec.i.maximum_column_length     := TRUE;
      dom_rec.v.column_precision          := p_column_precision;
      dom_rec.i.column_precision          := TRUE;
      dom_rec.v.average_column_length     := p_average_column_length;
      dom_rec.i.average_column_length     := TRUE;
      dom_rec.v.soft_lov                  := p_soft_lov;
      dom_rec.i.soft_lov                  := TRUE;
      IF p_supertype_name IS NOT NULL THEN
         BEGIN
           SELECT dom.id INTO lws_supertype_reference
             FROM ci_domains             dom
            WHERE dom.name = p_supertype_name;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             -- error always shows up on "show" cause prior supertype was rolled back.
             IF gv_show_messages THEN
                DBMS_OUTPUT.PUT_LINE('   *Failed to set up supertype to ' || p_supertype_name || '.');
             END IF;
         END;
      END IF;
      dom_rec.v.supertype_reference       := lws_supertype_reference;
      dom_rec.i.supertype_reference       := TRUE;
      dom_rec.v.default_value             := p_default_value;
      dom_rec.i.default_value             := TRUE;
      dom_rec.v.authority                 := p_authority;
      dom_rec.i.authority                 := TRUE;
      dom_rec.v.derivation                := p_derivation;
      dom_rec.i.derivation                := TRUE;
      dom_rec.v.null_value                := p_null_value;
      dom_rec.i.null_value                := TRUE;
      dom_rec.v.unit_of_measure           := p_unit_of_measure;
      dom_rec.i.unit_of_measure           := TRUE;
      -- Create the domain record.
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Inserting the values into the repository.');
      END IF;
      CIODOMAIN.INS(NULL, dom_rec);
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Selecting the values just inserted into the repository.');
         CIODOMAIN.SEL(dom_rec.v.id, dom_rec);
         DBMS_OUTPUT.PUT_LINE('      Name:'||dom_rec.v.name||'.');
         DBMS_OUTPUT.PUT_LINE('      Format:'||dom_rec.v.format||'.');
         DBMS_OUTPUT.PUT_LINE('      Attr Max Length:'||TO_CHAR(dom_rec.v.maximum_attribute_length)||'.');
         DBMS_OUTPUT.PUT_LINE('      Attr Precision:'||TO_CHAR(dom_rec.v.attribute_precision)||'.');
         DBMS_OUTPUT.PUT_LINE('      Datatype:'||dom_rec.v.datatype||'.');
         DBMS_OUTPUT.PUT_LINE('      Col Max Length:'||TO_CHAR(dom_rec.v.maximum_column_length)||'.');
         DBMS_OUTPUT.PUT_LINE('      Col Precision:'||TO_CHAR(dom_rec.v.column_precision)||'.');
         DBMS_OUTPUT.PUT_LINE('      Soft LOV:'||dom_rec.v.soft_lov||'.');
         DBMS_OUTPUT.PUT_LINE('      Description:'||dom_rec.v.description||'.');
      END IF;
      -- Validate the domain insert.
      transaction_end;
      RETURN dom_rec.v.id;
   END create_domain;
   --
   -- Create Values for existing Domains
   PROCEDURE create_domain_values
      (p_domain_reference               IN NUMBER
      ,p_radio_sequence                 IN NUMBER
      ,p_low_value                      IN VARCHAR2
      ,p_high_value                     IN VARCHAR2
      ,p_abbreviation                   IN VARCHAR2
      ,p_meaning                        IN VARCHAR2
   ) IS
      --
      lws_supertype_reference   NUMBER;
      val_rec                   cioattribute_value.data;
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Domain Value' || p_meaning || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Set the values
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Setting Properties for Domain Value.');
         DBMS_OUTPUT.PUT_LINE('      Values ' || p_low_value || ' thru ' || NVL(p_high_value,p_low_value) || '.');
      END IF;
      --
      val_rec.v.id                        := NULL;
      val_rec.i.id                        := TRUE;
      val_rec.v.domain_reference          := p_domain_reference;
      val_rec.i.domain_reference          := TRUE;
      val_rec.v.radio_sequence            := p_radio_sequence;
      val_rec.i.radio_sequence            := TRUE;
      val_rec.v.low_value                 := p_low_value;
      val_rec.i.high_value                := TRUE;
      val_rec.v.abbreviation              := p_abbreviation;
      val_rec.i.abbreviation              := TRUE;
      val_rec.v.meaning                   := p_meaning;
      val_rec.i.meaning                   := TRUE;
      -- Create the domain value record.
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Inserting the values into the repository.');
      END IF;
      CIOATTRIBUTE_VALUE.INS(NULL, val_rec);
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Selecting the values just inserted into the repository.');
         CIOATTRIBUTE_VALUE.SEL(val_rec.v.id, val_rec);
         DBMS_OUTPUT.PUT_LINE('      Low Value:'||val_rec.v.low_value||'.');
         DBMS_OUTPUT.PUT_LINE('      High Value:'||NVL(val_rec.v.high_value,'N/A')||'.');
         DBMS_OUTPUT.PUT_LINE('      Meaning:'||val_rec.v.meaning||'.');
         DBMS_OUTPUT.PUT_LINE('      Abbreviation:'||val_rec.v.abbreviation||'.');
      END IF;
      -- Validate the domain insert.
      transaction_end;
   END create_domain_values;
   --
   --
   FUNCTION create_entity (p_entity_name      IN VARCHAR2
                          ,p_entity_short_nm  IN VARCHAR2
                          ,p_entity_plural_nm IN VARCHAR2
                          ) RETURN NUMBER IS
   --
      ent cioentity.data;
   --
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Entity' || p_entity_name || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the entity');
      END IF;
      --
      ent.v.name := p_entity_name;
      ent.i.name := TRUE;
      --
      ent.v.plural := p_entity_plural_nm;
      ent.i.plural := TRUE;
      --
      ent.v.short_name := p_entity_short_nm;
      ent.i.short_name := TRUE;
      --
      ent.v.instantiable_flag := 'N';
      ent.i.instantiable_flag := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Inserting a new entity');
      END IF;
      cioentity.ins (NULL, ent);
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Selecting the entity');
         cioentity.sel (ent.v.id, ent);    -- Select the property list
         dbms_output.put_line ('    NAME : '||ent.v.NAME);
         dbms_output.put_line ('    PLURAL : '||ent.v.PLURAL);
         dbms_output.put_line ('    SHORT_NAME : '||ent.v.SHORT_NAME);
      END IF;
      transaction_end;
      RETURN ent.v.id;
   END create_entity;
   --
   --
   FUNCTION create_attribute (p_entity_name           IN VARCHAR2
                             ,p_attribute_name        IN VARCHAR2
                             ,p_attribute_sequence    IN NUMBER
                             ,p_attribute_optional    IN VARCHAR2
                             ,p_attribute_domain_nm   IN VARCHAR2
                             ,p_attribute_dataformat  IN VARCHAR2
                             ,p_attribute_maxlen      IN NUMBER
                             ,p_attribute_precision   IN NUMBER
                             ,p_attribute_comment     IN VARCHAR2
                             ) RETURN NUMBER IS
   --
      CURSOR c_ent IS
      SELECT ent.id
      FROM ci_entities ent
          ,ci_application_systems app
          ,ci_app_sys_entities appent
      WHERE ent.name = p_entity_name
       AND  appent.entity_reference = ent.id
       AND  appent.application_system_reference = app.id
       AND  app.name = gv_app_sys;
      --
      CURSOR c_dom IS
      SELECT dom.id
      FROM ci_domains dom
          ,ci_application_systems app
          ,ci_app_sys_domains appdom
      WHERE dom.name = p_attribute_domain_nm
       AND  appdom.domain_reference = dom.id
       AND  appdom.application_system_reference = app.id
       AND  app.name = gv_app_sys;
   --
      attr      cioattribute.data;
      v_ent_id  ci_entities.id%TYPE := NULL;
      v_dom_id  ci_domains.id%TYPE := NULL;
   --
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Attirbute ' || p_attribute_name || '.');
      END IF;
      --
      -- Get the entity reference.
      OPEN c_ent;
      FETCH c_ent INTO v_ent_id;
      CLOSE c_ent;
      IF v_ent_id IS NULL THEN
         IF gv_show_messages THEN
            DBMS_OUTPUT.PUT_LINE('Entity '||p_entity_name||' not found in repository.');
         END IF;
         RETURN NULL;
      END IF;
      --
      -- Get the domain reference.
      IF p_attribute_domain_nm IS NOT NULL THEN
         OPEN c_dom;
         FETCH c_dom INTO v_dom_id;
         CLOSE c_dom;
         IF v_dom_id IS NULL THEN
            IF gv_show_messages THEN
               DBMS_OUTPUT.PUT_LINE('Domain '||p_attribute_domain_nm||' not found in repository.');
            END IF;
            RETURN NULL;
         END IF;
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the attribute');
      END IF;
      attr.v.entity_reference := v_ent_id;
      attr.i.entity_reference := TRUE;
      --
      attr.v.name := p_attribute_name;
      attr.i.name := TRUE;
      --
      attr.v.sequence_number := p_attribute_sequence;
      attr.i.sequence_number := TRUE;
      --
      attr.v.optional_flag := p_attribute_optional;
      attr.i.optional_flag := TRUE;
      --
      IF v_dom_id IS NOT NULL THEN
         attr.v.domain_reference := v_dom_id;
         attr.i.domain_reference := TRUE;
      ELSE
         --
         attr.v.format := p_attribute_dataformat;
         attr.i.format := TRUE;
         --
         attr.v.maximum_length := p_attribute_maxlen;
         attr.i.maximum_length := TRUE;
         --
         attr.v.precision := p_attribute_precision;
         attr.i.precision := TRUE;
      END IF;
      --
      IF p_attribute_comment IS NULL THEN
         attr.v.notes := 'Enter a value for '||p_attribute_name;
      ELSE
         attr.v.notes := p_attribute_comment;
      END IF;
      attr.i.notes := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('Inserting a new attribute');
      END IF;
      cioattribute.ins (NULL, attr);
      IF gv_show_messages THEN
         dbms_output.put_line ('Selecting the attribute');
         cioattribute.sel (attr.v.id, attr);    -- Select the property list
      END IF;
      IF gv_show_messages THEN
         dbms_output.put_line ('      NAME   : '||attr.v.name);
         dbms_output.put_line ('      FORMAT : '||attr.v.format);
         dbms_output.put_line ('      LENGTH : '||TO_CHAR(attr.v.maximum_length)||TO_CHAR(attr.v.precision));
      END IF;
      -- Validate the attribute insert.
      transaction_end;
      RETURN attr.v.id;
   END create_attribute;
   --
   --
   FUNCTION create_table (p_table_name       IN VARCHAR2
                         ,p_alias            IN VARCHAR2
                         ,p_display_title    IN VARCHAR2
                         ,p_journal_location IN VARCHAR2 DEFAULT 'NONE'
                         ,p_comment          IN VARCHAR2
                         ) RETURN VARCHAR2 IS
      tab   ciotable_definition.data;
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Table ' || p_table_name || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the table');
      END IF;
      --
      tab.v.name := p_table_name;
      tab.i.name := TRUE;
      --
      tab.v.alias := p_alias;
      tab.i.alias := TRUE;
      --
      tab.v.display_title := p_display_title;
      tab.i.display_title := TRUE;
      --
      tab.v.journal_location := UPPER(p_journal_location);
      tab.i.journal_location := TRUE;
      --
      tab.v.remark := p_comment;
      tab.i.remark := TRUE;
      --
      tab.v.index_only_flag := 'N';
      tab.i.index_only_flag := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Inserting a new view');
      END IF;
      ciotable_definition.ins (NULL, tab);
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Selecting the view');
         ciotable_definition.sel (tab.v.id, tab);    -- Select the property list
         dbms_output.put_line ('    NAME : '||tab.v.NAME);
         dbms_output.put_line ('    ALIAS : '||tab.v.ALIAS);
      END IF;
      transaction_end;
      RETURN tab.v.name;
   END create_table;
   --
   --
   FUNCTION create_declarative_view (p_view_name       IN VARCHAR2
                                    ,p_alias           IN VARCHAR2
                                    ,p_display_title   IN VARCHAR2
                                    ,p_comment         IN VARCHAR2
                                    ) RETURN VARCHAR2 IS
      vw   cioview_definition.data;
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating View ' || p_view_name || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the view');
      END IF;
      --
      vw.v.name := p_view_name;
      vw.i.name := TRUE;
      --
      vw.v.alias := p_alias;
      vw.i.alias := TRUE;
      --
      vw.v.display_title := p_display_title;
      vw.i.display_title := TRUE;
      --
      vw.v.remark := p_comment;
      vw.i.remark := TRUE;
      --
      vw.v.override_select_text_flag := 'N';
      vw.i.override_select_text_flag := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Inserting a new view');
      END IF;
      cioview_definition.ins (NULL, vw);
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Selecting the view');
         cioview_definition.sel (vw.v.id, vw);    -- Select the property list
         dbms_output.put_line ('    NAME : '||vw.v.NAME);
         dbms_output.put_line ('    ALIAS : '||vw.v.ALIAS);
      END IF;
      transaction_end;
      RETURN vw.v.name;
   END create_declarative_view;
   --
   --
   FUNCTION create_freeformat_view  (p_view_name       IN VARCHAR2
                                    ,p_alias           IN VARCHAR2
                                    ,p_display_title   IN VARCHAR2
                                    ,p_comment         IN VARCHAR2
                                    ,p_select_text     IN VARCHAR2
                                    ,p_where_clause    IN VARCHAR2
                                    ,p_optimizer_hint  IN VARCHAR2
                                    ) RETURN VARCHAR2 IS
      vw   cioview_definition.data;
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating View ' || p_view_name || '.');
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the view');
      END IF;
      --
      vw.v.name := p_view_name;
      vw.i.name := TRUE;
      --
      vw.v.alias := p_alias;
      vw.i.alias := TRUE;
      --
      vw.v.display_title := p_display_title;
      vw.i.display_title := TRUE;
      --
      vw.v.remark := p_comment;
      vw.i.remark := TRUE;
      --
      vw.v.override_select_text_flag := 'Y';
      vw.i.override_select_text_flag := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Inserting a new view');
      END IF;
      cioview_definition.ins (NULL, vw);
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('   Selecting the view');
         cioview_definition.sel (vw.v.id, vw);    -- Select the property list
         dbms_output.put_line ('    NAME : '||vw.v.NAME);
         dbms_output.put_line ('    ALIAS : '||vw.v.ALIAS);
      END IF;
      transaction_end;
      RETURN vw.v.name;
   END create_freeformat_view;
   --
   --
   FUNCTION add_view_base_table (p_view_name       IN VARCHAR2
                                ,p_table_name      IN VARCHAR2
                                )RETURN NUMBER IS
      vwtab   ciorelation_selection.data;
      --
      CURSOR ctab IS
         SELECT tab.id, tab.alias
         FROM ci_table_definitions tab
         WHERE tab.name = p_table_name;
      --
      CURSOR cvw IS
         SELECT vw.id
         FROM ci_view_definitions vw
         WHERE vw.name = p_view_name;
      --
      tab_alias      ci_table_definitions.alias%TYPE;
      tab_ref_id     ci_table_definitions.id%TYPE;
      vw_ref_id      ci_view_definitions.id%TYPE;
      --
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Adding base table defs to a view.');
      END IF;
      --
      -- First, start a transaction
      transaction_start;
      --
      --
      -- Second, get the Table and View ids
      OPEN ctab;
      FETCH ctab INTO tab_ref_id, tab_alias;
      CLOSE ctab;
      --
      OPEN cvw;
      FETCH cvw INTO vw_ref_id;
      CLOSE cvw;
      --
      -- Third, if we got both ids...do the relationship
      IF  tab_ref_id IS NOT NULL
      AND vw_ref_id IS NOT NULL THEN
         vwtab.v.table_reference := tab_ref_id;
         vwtab.i.table_reference := TRUE;
         --
         vwtab.v.view_reference := vw_ref_id;
         vwtab.i.view_reference := TRUE;
         --
         vwtab.v.alias := tab_alias;
         vwtab.i.alias := TRUE;
         --
         -- Do the Insert of values now.
         IF gv_show_messages THEN
            dbms_output.put_line ('   Inserting the new view / table reference');
         END IF;
         ciorelation_selection.ins(NULL, vwtab);
         IF gv_show_messages THEN
            dbms_output.put_line ('   View and Table Linked');
            dbms_output.put_line ('    VIEW : '||p_view_name);
            dbms_output.put_line ('    TABLE : '||p_table_name);
         END IF;
      END IF;
      transaction_end;
      RETURN vwtab.v.id;
   END add_view_base_table;
   --
   --
   FUNCTION create_column (p_owner_obj_name  IN VARCHAR2
                          ,p_owner_obj_type  IN VARCHAR2
                          ,p_col_name        IN VARCHAR2
                          ,p_col_sequence    IN NUMBER
                          ,p_col_null_ind    IN VARCHAR2 DEFAULT 'NULL'
                          ,p_col_domain_nm   IN VARCHAR2
                          ,p_col_datatype    IN VARCHAR2
                          ,p_col_maxlen      IN NUMBER
                          ,p_col_precision   IN NUMBER
                          ,p_col_comment     IN VARCHAR2
                          ,p_col_help_text   IN VARCHAR2 DEFAULT NULL
                          ,p_col_base_ref    IN NUMBER   DEFAULT NULL
                          ,p_col_autogen     IN VARCHAR2 DEFAULT NULL
                          ,p_col_dflt_value  IN VARCHAR2 DEFAULT NULL
                          ,p_col_disp_flag   IN VARCHAR2 DEFAULT 'Y'
                          ,p_col_disp_type   IN VARCHAR2 DEFAULT 'TEXT'
                          ,p_col_disp_height IN NUMBER   DEFAULT 1
                          ,p_col_disp_length IN NUMBER   DEFAULT 10
                          ,p_col_disp_seq    IN NUMBER   DEFAULT NULL
                          ,p_col_justify     IN VARCHAR2 DEFAULT 'LEFT'
                          ,p_col_prompt      IN VARCHAR2 DEFAULT NULL
                          ,p_col_srv_dflt    IN VARCHAR2 DEFAULT 'N'
                          ,p_col_srv_drv     IN VARCHAR2 DEFAULT 'N'
                          ,p_col_soft_lov    IN VARCHAR2 DEFAULT 'N'
                          ,p_col_uppercase   IN VARCHAR2 DEFAULT 'N'
                          ) RETURN NUMBER IS
      CURSOR c_tab IS
      SELECT tab.id
      FROM ci_table_definitions tab
          ,ci_application_systems app
          ,ci_app_sys_tables apptab
      WHERE tab.name = p_owner_obj_name
       AND  apptab.table_reference = tab.id
       AND  apptab.application_system_reference = app.id
       AND  app.name = gv_app_sys;
      --
      CURSOR c_vw IS
      SELECT vw.id
      FROM ci_view_definitions vw
          ,ci_application_systems app
          ,ci_app_sys_tables appvw
      WHERE vw.name = p_owner_obj_name
       AND  appvw.table_reference = vw.id
       AND  appvw.application_system_reference = app.id
       AND  app.name = gv_app_sys;
      --
      CURSOR c_dom IS
      SELECT dom.id, dom.datatype, dom.maximum_column_length, dom.column_precision
      FROM ci_domains dom
          ,ci_application_systems app
          ,ci_app_sys_domains appdom
      WHERE dom.name = p_col_domain_nm
       AND  appdom.domain_reference = dom.id
       AND  appdom.application_system_reference = app.id
       AND  app.name = gv_app_sys;
   --
      col      ciocolumn.data;
      v_owner_obj_id  ci_app_sys_tables.table_reference%TYPE := NULL;
      v_dom_id        ci_domains.id%TYPE := NULL;
      v_dom_type      ci_domains.datatype%TYPE;
      v_dom_len       ci_domains.maximum_column_length%TYPE;
      v_dom_dec       ci_domains.column_precision%TYPE;
   --
   BEGIN
      IF gv_show_messages THEN
         DBMS_OUTPUT.PUT_LINE('   Creating Column ' || p_col_name || '.');
      END IF;
      --
      -- Get the table/view reference.
      IF p_owner_obj_type = 'TABLE' THEN
         OPEN c_tab;
         FETCH c_tab INTO v_owner_obj_id;
         CLOSE c_tab;
         IF v_owner_obj_id IS NULL THEN
            IF gv_show_messages THEN
               DBMS_OUTPUT.PUT_LINE('Table '||p_owner_obj_name||' not found in repository.');
            END IF;
            RETURN NULL;
         END IF;
      ELSIF p_owner_obj_type = 'VIEW' THEN
         OPEN c_vw;
         FETCH c_vw INTO v_owner_obj_id;
         CLOSE c_vw;
         IF v_owner_obj_id IS NULL THEN
            IF gv_show_messages THEN
               DBMS_OUTPUT.PUT_LINE('View '||p_owner_obj_name||' not found in repository.');
            END IF;
         RETURN NULL;
         END IF;
      ELSE
         DBMS_OUTPUT.PUT_LINE('You didnot tell me where to put column '||p_col_name);
         RETURN NULL;
      END IF;
      --
      -- Get the domain reference.
      IF p_col_domain_nm IS NOT NULL THEN
         OPEN c_dom;
         FETCH c_dom INTO v_dom_id, v_dom_type, v_dom_len, v_dom_dec;
         CLOSE c_dom;
         IF v_dom_id IS NULL THEN
            IF gv_show_messages THEN
               DBMS_OUTPUT.PUT_LINE('Domain '||p_col_domain_nm||' not found in repository.');
            END IF;
            RETURN NULL;
         END IF;
      END IF;
      --
      -- Initialize a transaction
      transaction_start;
      --
      -- Seting the values to store
      IF gv_show_messages THEN
         dbms_output.put_line ('   Setting values for the column');
      END IF;
      col.v.table_reference := v_owner_obj_id;
      col.i.table_reference := TRUE;
      --
      col.v.name := p_col_name;
      col.i.name := TRUE;
      --
      col.v.sequence_number := p_col_sequence;
      col.i.sequence_number := TRUE;
      --
      col.v.null_indicator := p_col_null_ind;
      col.i.null_indicator := TRUE;
      --
      col.v.base_column_reference := p_col_base_ref;
      col.i.base_column_reference := TRUE;
      --
      IF v_dom_id IS NOT NULL THEN
         col.v.domain_reference := v_dom_id;
         col.i.domain_reference := TRUE;
         --
         col.v.datatype := v_dom_type;
         col.i.datatype := TRUE;
         --
         col.v.maximum_length := v_dom_len;
         col.i.maximum_length := TRUE;
         --
         col.v.decimal_places := v_dom_dec;
         col.i.decimal_places := TRUE;
      ELSE
         --
         col.v.datatype := p_col_datatype;
         col.i.datatype := TRUE;
         --
         col.v.maximum_length := p_col_maxlen;
         col.i.maximum_length := TRUE;
         --
         col.v.decimal_places := p_col_precision;
         col.i.decimal_places := TRUE;
      END IF;
      --
      IF p_col_comment IS NULL THEN
         col.v.remark := 'Enter a value for '||p_col_name;
      ELSE
         col.v.remark := p_col_comment;
      END IF;
      col.i.remark := TRUE;
      --
      col.v.auto_generated := p_col_autogen;
      col.i.auto_generated := TRUE;
      --
      col.v.complete_flag := 'Y';
      col.i.complete_flag := TRUE;
      --
      col.v.default_value := p_col_dflt_value;
      col.i.default_value := TRUE;
      --
      col.v.display_flag := p_col_disp_flag;
      col.i.display_flag := TRUE;
      --
      col.v.default_display_type := p_col_disp_type;
      col.i.default_display_type := TRUE;
      --
      col.v.display_height := p_col_disp_height;
      col.i.display_height := TRUE;
      --
      col.v.display_length := p_col_disp_length;
      col.i.display_length := TRUE;
      --
      col.v.display_sequence := p_col_disp_seq;
      col.i.display_sequence := TRUE;
      --
      col.v.justification := p_col_justify;
      col.i.justification := TRUE;
      --
      col.v.prompt := p_col_prompt;
      col.i.prompt := TRUE;
      --
      col.v.server_defaulted_flag := p_col_srv_dflt;
      col.i.server_defaulted_flag := TRUE;
      --
      col.v.server_derived_flag := p_col_srv_drv;
      col.i.server_derived_flag := TRUE;
      --
      col.v.signed := 'N';
      col.i.signed := TRUE;
      --
      col.v.soft_lov := p_col_soft_lov;
      col.i.soft_lov := TRUE;
      --
      col.v.store_ref_with_rowid_flag := 'N';
      col.i.store_ref_with_rowid_flag := TRUE;
      --
      col.v.suggestion_list_flag := 'N';
      col.i.suggestion_list_flag := TRUE;
      --
      col.v.uppercase := p_col_uppercase;
      col.i.uppercase := TRUE;
      --
      IF p_col_help_text IS NULL THEN
         col.v.help_text := 'Enter a value for '||p_col_name;
      ELSE
         col.v.help_text := p_col_help_text;
      END IF;
      col.i.help_text := TRUE;
      --
      IF gv_show_messages THEN
         dbms_output.put_line ('Inserting a new column');
      END IF;
      ciocolumn.ins (NULL, col);
      IF gv_show_messages THEN
         dbms_output.put_line ('Selecting the column');
         ciocolumn.sel (col.v.id, col);    -- Select the property list
      END IF;
      IF gv_show_messages THEN
         dbms_output.put_line ('      NAME    : '||col.v.name);
         dbms_output.put_line ('      DATATYPE: '||col.v.datatype);
         dbms_output.put_line ('      LENGTH  : '||TO_CHAR(col.v.maximum_length)||TO_CHAR(col.v.decimal_places));
      END IF;
      -- Validate the attribute insert.
      transaction_end;
      RETURN col.v.id;
   END create_column;
--
--
END d2k_api_util; -- </Body>
/

-- End of DDL script for D2K_API_UTIL