CREATE OR REPLACE PACKAGE BODY ckOOTutl AS 
  -- Oracle Object Type utility 
  -- Creates Oracle Type/Attribute definitions based on existing 
  -- relational Table or View (TAB) definitions. 
  -- This utility is complementary to the ckovwutl utility 
  -- which creates Object Views based on the Object Types 
  -- created by this utility. 
 
  -- Change History: 
  -- 11-Apr-00 : JS fixed issue with ins calls returning IVID field of
  --             cdapi data records populated with result of last ins
  --             causing subsequent calls to cdapi to use that ivid
  --             Fixed by using locally scoped declarations for data record
  --             variables
  -- 22-Nov-99 : JS updated with model changes to TRANSFORMATION_MAPPING_SET 
  --             Also removed cdapi error stack code as it was preventing the 
  --             client side from picking up the errors and presenting them to 
  --             the user in the standard error stack dialog. 
  --             No other utilities have this 
  --  9-Jun-99 : Fixed bug 898608 
  --  2-Mar-98 : Fixed Bugs 631820, 632499 
 
  act_status varchar2(100); 
  act_warnings varchar2(100); 
  line varchar2(240); 
 
 -- BEGIN Local Defined Procedure Specifications 
 
  procedure o (text varchar2); 
 
  Procedure create_ref_4_fkey ( 
     i_TAB_IDs In IDrefs   -- Mandatory reference(ID) for Table or View 
    ,i_TMS_ID  In Number   -- Mandatory reference(ID) for context T.M.S. 
    ); 
  
 -- END Local Defined Procedure Specifications 
 
 
  -- Create an OOT and its Attributes (COLumns) for each given Table/View. 
  -- Note REF attributes are added, for foreign keys, once all the OOT's  
  -- have been created for all given Tables/Views. 
  -- Refer to package spec for definition of 'user defined' Types. 
  Procedure create_OOT_4_TAB ( 
     i_TAB_IDs       In     IDrefs   -- Mandatory reference(ID)list of Tables/Views 
    ,i_prfx          In     Varchar2 -- Optional user supplied prefix for the Type  
    ,i_store_ref     In     Boolean  -- Optional store REF with ROWID flag 
    ,i_scope_table   In     Boolean  -- Optional scope table flag  
    ,io_OOT_name     In Out OOTnames -- Optional user supplied list of names for the OOT's else derived               
    ,o_OOT_IDs       Out    IDrefs   -- Return reference(ID) list of OOT's created 
    ,i_sufx          In     Varchar2 Default '_T' 
                                     -- Optional user supplied suffix for the Type  
    ) Is 
 
    oot       ciooracle_object_type.data; 
    tms       ciotransformation_mapping_set.data; 
 
    i_TAB_reference ci_relation_definitions.id%type;  -- used to poke each 'i_TAB_ID' into a cursor 
    conflict_ID     ci_oracle_object_types.id%type;   -- return variable (voided!) for conflicting  
                                                      -- ID from 'is_pac_name_unique' 
 
    tab_name  ci_relation_definitions.name%type; 
    max_seq   ci_columns.sequence_number%type; 
       
    Cursor get_TAB_properties (TAB_id ci_relation_definitions.id%type) Is 
     SELECT max(t.name) 
     FROM   ci_relation_definitions t 
     WHERE  t.id = TAB_id          -- context table/view 
       AND   
            t.table_type in ('TABLE', 'VIEW') 
     ; 
 
    -- get column details for creating the  
    -- attributes of the OOT (note fkey columns are 
    -- retrieved and processed differently) 
    Cursor get_tabcol_properties Is 
     SELECT c.id                              COLUMN_ID 
     ,      decode(c.datatype, 'LONG', 'CLOB' 
                   , c.datatype)              DATATYPE 
     ,      c.decimal_places 
     ,      c.default_value 
     ,      c.domain_reference 
     ,      c.maximum_length 
     ,      c.name    
     ,      c.null_indicator 
     ,      c.object_type_inclusion 
     ,      c.oracle_type_reference 
     ,      c.remark 
     ,      c.scope_table_reference 
     ,      c.sequence_number 
     ,      c.store_ref_with_rowid_flag 
     FROM   ci_relation_definitions t 
     ,      ci_columns              c 
     WHERE  t.id = i_TAB_reference               -- context table/view 
       AND  t.id = c.table_reference 
       AND   
            t.table_type in ('TABLE', 'VIEW') 
       AND  -- Exclude fkey columns unless also part of the pkey 
            -- (pkey columns required to construct OID). 
            -- Fkeys are handled seperately 
            not exists  
              (select null 
               from ci_key_components fk 
               where fk.column_reference = c.id 
                 and  
                     fk.constraint_type = 'FOREIGN' 
                 and not exists 
                       (select null 
                        from ci_key_components pk 
                        where pk.column_reference = fk.column_reference 
                          and  
                              pk.constraint_type = 'PRIMARY' 
                        ) 
               ) 
     ; 
 
    -- TNS/9-JUN-99 : Fix bug 898608 
    Cursor get_TMS (context_appsys number) Is 
     SELECT s.ID 
     FROM   ci_transformation_mapping_sets s 
     ,      ci_folder_members              m 
     WHERE  s.transformation_type = 'REL2OREL' 
       AND  m.folder_reference = context_appsys 
       AND  m.member_object    = s.id 
       AND  m.ownership_flag = 'Y' 
     ; 
 
  BEGIN 
    o('At beginning of OOT_4_TAB'); 
    -- Check API initialized 
    rmmes.clear; 
    if not cdapi.initialized then 
      ciierr.fatal(1); 
    end if; 
 
    -- First find/insert a Transformation Mapping Set for the context 
    -- Application System, in which to create the Mapping Element     
    Open get_TMS (cdapi.app_sys_ref); 
    Fetch get_TMS into tms.v.ID; 
    o('Context Application System ID : '||cdapi.app_sys_ref 
      ||' TMS ID :' || tms.v.ID); 
 
    If get_TMS%NOTFOUND Then     -- need to create one 
      Close get_TMS; 
       
      tms.v.REMARK := 'Created by the Object Type Utility'; 
      tms.i.REMARK := true; 
 
      tms.v.transformation_type := 'REL2OREL'; 
      tms.i.transformation_type := true; 
 
      -- Perform the INSERT operation 
      o('Inserting a new Transformation Mapping Set'); 
      ciotransformation_mapping_set.ins(null,TMS); 
    Else Close get_TMS; 
    End If; 
 
    -- Process each of the Table/View's passed into the utility 
    For loop_idx In 1 .. i_TAB_IDs.Count Loop 
      i_TAB_reference := i_TAB_IDs(loop_idx); -- set the context TAB id for use in cursors 
      o('Processing Table/View ID: '||i_TAB_reference); 
 
      -- Set the NAME property of the Oracle Object Type (OOT) 
      --   1) NAME may be passed in as a parameter 
      --   2) but if not it will be derived from the given Table or View  
      --   3) the derived name may have a prefix and suffix 
      --   4) Let the API determine if the NAME is unique, and if its not 
      --      try once more by implanting the ID of the OOT to the name 
      tab_name := null; 
      o('io_OOT_name(loop_idx)'); 
      
      Begin 
        -- Ensure the binary indexed table io_OOT_name has been initialized 
        If io_OOT_name(loop_idx) Is NULL Or io_OOT_name(loop_idx) Is NOT NULL Then 
          NULL; 
        End If; 
        Exception  
          When NO_DATA_FOUND Then io_OOT_name(loop_idx) := NULL; 
          When Others Then RAISE; 
      End;  
      If io_OOT_name(loop_idx) Is null Then  
        Open get_tab_properties(i_TAB_reference); 
        Fetch get_tab_properties Into tab_name; 
        o('Fetched Table/View NAME: '||tab_name); 
        Close get_tab_properties; 
        -- This is the first try for an OOT name if not supplied by the user 
        io_OOT_name(loop_idx) :=  i_prfx 
                     ||substr(tab_name, 1, 30 - nvl(length(i_prfx),0) 
                                              - nvl(length(i_sufx),0)) 
                     ||i_sufx ; 
      End If; 
 
      o('New Oracle Object Type NAME: '||io_OOT_name(loop_idx)); 
      oot.v.NAME := io_OOT_name(loop_idx); 
      if oot.v.NAME is not null then 
        oot.i.NAME := true; 
      end if; 
 
      -- Perform the INSERT operation 
      o('Inserting a new Oracle Object Type'); 
      ciooracle_object_type.ins(null,OOT); 
 
      -- If the OOT NAME is not unique, try making it unique using its 
      -- ID (which is guaranteed to be unique), and UPDating the newly 
      -- inserted row. 
      -- TNS/9-JUN-99 : Fix bug 898608 
      If not cimappcon.is_pac_name_unique(cdapi.app_sys_ref 
                                         , oot.v.ID, oot.v.ORT_TYPE  
                                         , conflict_ID) Then 
        If tab_name is null Then  -- maybe NULL if user supplied the OOT name 
          Open get_tab_properties(i_TAB_reference); 
          Fetch get_tab_properties Into tab_name; 
          Close get_tab_properties; 
        End If; 
        io_OOT_name(loop_idx) :=  i_prfx 
                     ||substr(tab_name, 1, 30 - length(oot.v.ID) 
                                              - nvl(length(i_prfx),0) 
                                              - nvl(length(i_sufx),0)) 
                     ||to_char(oot.v.ID) 
                     ||i_sufx ; 
   
        oot.v.NAME := io_OOT_name(loop_idx); 
        if oot.v.NAME is not null then 
          oot.i.NAME := true; 
        end if; 
 
        -- Perform the UPDATE operation 
        o('Updating NAME of Oracle Object Type with ID : '|| oot.v.ID); 
        ciooracle_object_type.upd(oot.v.ID,OOT); 
      End If; 
        
      o_OOT_IDs(loop_idx) := oot.v.ID ; -- add the OOT ID to the output list of ID's 
  
      --  Print the (interesting) properties in the property list which hold values 
 
      if oot.v.ID is not null then 
        o('ID                         : '||oot.v.ID); 
      end if; 
      if oot.v.NAME is not null then 
        o('NAME                       : '||oot.v.NAME); 
      end if; 
      if oot.v.ORT_TYPE is not null then 
        o('ORT_TYPE                   : '||oot.v.ORT_TYPE); 
      end if; 
 
 
      declare
        map       ciomapping_element.data;     

      begin 
        -- Having established a Mapping Set, create the Mapping Element 
        -- between new OOT and existing TAB 
        map.v.CREATED_BY_TRANSFORMER_FLAG := 'Y'; 
        map.i.CREATED_BY_TRANSFORMER_FLAG := true; 
 
        map.v.SOURCE_CIELEMENT_REFERENCE := i_TAB_reference; 
        map.i.SOURCE_CIELEMENT_REFERENCE := true; 
 
        -- us bug 898618 
 
        select rme.short_name 
        into   map.v.SOURCE_ELEMENT_TYPE 
        from   ci_relation_definitions   tab, 
               rm_element_types          rme 
        where  tab.types   = rme.id 
        and    rme.product = 'CI' 
        and    tab.id      = i_TAB_reference; 
 
        map.i.SOURCE_ELEMENT_TYPE        := true; 
 
        map.v.TARGET_CIELEMENT_REFERENCE := oot.v.ID; 
        map.i.TARGET_CIELEMENT_REFERENCE := true; 
 
        -- us bug 898618 
        map.v.TARGET_ELEMENT_TYPE        := 'OOT'; 
        map.i.TARGET_ELEMENT_TYPE        := true; 
 
        map.v.TRANSFORMATION_SET_REFERENCE := tms.v.ID; 
        map.i.TRANSFORMATION_SET_REFERENCE := true; 
 
        -- Perform the INSERT operation 
        o('Inserting a new Mapping Element'); 
        ciomapping_element.ins(null,MAP); 
      end; 

      -- Create ATTributes of the new OOT, for each column of the  
      -- current Table/View which is not part of a foreign key 
      -- (NB: REF attributes are created for foreign keys once all 
      --  Table/View's have been processed and OOT's have been created) 
      For i In get_tabcol_properties Loop 

        declare
          col       ciocolumn.data; 
          map       ciomapping_element.data;     
 
        begin
          col.v.DATATYPE := i.DATATYPE; 
          if col.v.DATATYPE is not null then 
            col.i.DATATYPE := true; 
          end if; 
 
          col.v.DECIMAL_PLACES := i.DECIMAL_PLACES; 
          if col.v.DECIMAL_PLACES is not null then 
            col.i.DECIMAL_PLACES := true; 
          end if; 
 
          col.v.DEFAULT_VALUE := i.DEFAULT_VALUE; 
          if col.v.DEFAULT_VALUE is not null then 
            col.i.DEFAULT_VALUE := true; 
          end if; 
 
          col.v.DOMAIN_REFERENCE := i.DOMAIN_REFERENCE; 
          if col.v.DOMAIN_REFERENCE is not null then 
            col.i.DOMAIN_REFERENCE := true; 
          end if; 
 
          col.v.MAXIMUM_LENGTH := i.MAXIMUM_LENGTH; 
          if col.v.MAXIMUM_LENGTH is not null then 
            col.i.MAXIMUM_LENGTH := true; 
          end if; 
 
          col.v.NAME := i.NAME; 
          if col.v.NAME is not null then 
             col.i.NAME := true; 
          end if; 
 
          col.v.NULL_INDICATOR := i.NULL_INDICATOR; 
          if col.v.NULL_INDICATOR is not null then 
            col.i.NULL_INDICATOR := true; 
          end if; 
 
          col.v.OBJECT_TYPE_INCLUSION := i.OBJECT_TYPE_INCLUSION; 
          if col.v.OBJECT_TYPE_INCLUSION is not null then 
            col.i.OBJECT_TYPE_INCLUSION := true; 
          end if; 
 
          col.v.ORACLE_OBJECT_TYPE_REFERENCE := oot.v.ID;  
          col.i.ORACLE_OBJECT_TYPE_REFERENCE := true; 
 
          col.v.ORACLE_TYPE_REFERENCE := i.ORACLE_TYPE_REFERENCE; 
          if col.v.ORACLE_TYPE_REFERENCE is not null then 
            col.i.ORACLE_TYPE_REFERENCE := true; 
          end if; 
 
          col.v.REMARK := i.REMARK; 
          if col.v.REMARK is not null then 
            col.i.REMARK := true; 
          end if; 
 
          col.v.SCOPE_TABLE_REFERENCE := i.SCOPE_TABLE_REFERENCE; 
          if col.v.SCOPE_TABLE_REFERENCE is not null then 
            col.i.SCOPE_TABLE_REFERENCE := true; 
          end if; 
 
          col.v.SEQUENCE_NUMBER := i.SEQUENCE_NUMBER; 
          if col.v.SEQUENCE_NUMBER is not null then 
            col.i.SEQUENCE_NUMBER := true; 
          end if; 
 
          col.v.STORE_REF_WITH_ROWID_FLAG := i.STORE_REF_WITH_ROWID_FLAG; 
          if col.v.STORE_REF_WITH_ROWID_FLAG is not null then 
            col.i.STORE_REF_WITH_ROWID_FLAG := true; 
          end if; 
 
          -- Perform the INSERT operation 
          o('Inserting a new Oracle Object Type Attribute (COLumn)'); 
          ciocolumn.ins(null,COL); 

          -- create the Mapping Element between new Attribute(COL) and existing Column(COL) 
          map.v.CREATED_BY_TRANSFORMER_FLAG := 'Y'; 
          map.i.CREATED_BY_TRANSFORMER_FLAG := true; 
 
          map.v.SOURCE_CIELEMENT_REFERENCE := i.COLUMN_ID; 
          map.i.SOURCE_CIELEMENT_REFERENCE := true; 
 
          -- us bug 898618 
          map.v.SOURCE_ELEMENT_TYPE        := 'COL'; 
          map.i.SOURCE_ELEMENT_TYPE        := true; 
 
          map.v.TARGET_CIELEMENT_REFERENCE := col.v.ID; 
          map.i.TARGET_CIELEMENT_REFERENCE := true; 
 
          -- us bug 898618 
          map.v.TARGET_ELEMENT_TYPE        := 'COL'; 
          map.i.TARGET_ELEMENT_TYPE        := true; 
 
          map.v.TRANSFORMATION_SET_REFERENCE := tms.v.ID; 
          map.i.TRANSFORMATION_SET_REFERENCE := true; 
 
          -- Perform the INSERT operation 
          o('Inserting a new Mapping Element'); 
          ciomapping_element.ins(null,MAP); 
        end;

      End Loop; 
    End Loop; 
 
    -- Having created OOT's for each Table/View, now  
    -- create all the 'REF' Attributes based on the foreign keys 
    create_REF_4_fkey (i_TAB_IDs, tms.v.ID); 
  
  EXCEPTION 
    -- Catch all exceptions 
    When Others Then 
      raise; 
  
  END create_OOT_4_TAB ; 
 
 -- BEGIN Local Defined Procedure Bodies 
  Procedure create_ref_4_fkey ( 
     i_TAB_IDs In IDrefs         -- Mandatory reference(ID) for Table or View 
    ,i_TMS_ID  In Number   -- Mandatory reference(ID) for context T.M.S. 
    ) Is 
 
    col       ciocolumn.data; 
    map       ciomapping_element.data; 
 
    i_TAB_reference  ci_relation_definitions.id%Type; 
    max_seq   ci_columns.sequence_number%Type; 
       
    -- get all the foreign keys of the table/view  
    -- which are used to create REF attributes for 
    -- the OOT 
    -- NB: The joins for the context ci_relation_definition mapped to OOT 
    --     are not necessary, when selecting reference id's  
    --     This assumes that Tables/Views are only transformed into OOT's 
    --     Assumes that the join table/view is not an object table/view 
    -- Only create REF properties where the referenced object type exists 
    -- and has a mapping to a relational table or view. 
    Cursor get_tabfkey_properties Is 
     SELECT t.target_cielement_reference   
                        ORACLE_OBJECT_TYPE_REFERENCE  
     ,      f.id        FOREIGN_KEY_ID 
     ,      f.name      FOREIGN_KEY_NAME     -- name for the REF attribute 
     ,      y.id        ORACLE_TYPE_REFERENCE 
     FROM   ci_mapping_elements            t 
     ,      ci_foreign_key_constraints     f 
     ,      ci_relation_definitions        j 
     ,      ci_oracle_object_types         y 
     ,      ci_mapping_elements            m 
     WHERE  t.source_cielement_reference = i_TAB_reference   -- context table/view 
       AND  t.transformation_set_reference = i_TMS_ID 
       AND  f.table_reference = t.source_cielement_reference 
       AND  f.foreign_table_reference = j.id 
       AND  j.id = m.source_cielement_reference  
       AND  y.id = m.target_cielement_reference 
     ; 
    
    Cursor get_max_col_seq Is 
    SELECT  max(sequence_number)  MAX_SEQ 
    FROM    ci_columns  c 
    WHERE   c.table_reference = i_TAB_reference   -- context table/view 
    ; 
     
  BEGIN 
    o('At beginning of create_ref_4_fkey '); 
 
    For loop_idx In 1..i_TAB_IDs.Count Loop 
      i_TAB_reference := i_TAB_IDs(loop_idx); 
      o('Processing Table/View ID : ' || i_TAB_reference); 
 
      -- Initialise variable to set the SEQUENCE_NUMBER property 
      Open get_max_col_seq; 
      Fetch get_max_col_seq Into max_seq; 
      Close get_max_col_seq; 
 
      For i in get_tabfkey_properties Loop 
        col.v.NAME := i.FOREIGN_KEY_NAME; 
        if col.v.NAME is not null then 
           col.i.NAME := true; 
        end if; 
 
        col.v.OBJECT_TYPE_INCLUSION := 'REF'; 
        col.i.OBJECT_TYPE_INCLUSION := true; 
 
 
        col.v.ORACLE_OBJECT_TYPE_REFERENCE := i.ORACLE_OBJECT_TYPE_REFERENCE;  
        col.i.ORACLE_OBJECT_TYPE_REFERENCE := true; 
 
        col.v.ORACLE_TYPE_REFERENCE := i.ORACLE_TYPE_REFERENCE; 
        if col.v.ORACLE_TYPE_REFERENCE is not null then 
          col.i.ORACLE_TYPE_REFERENCE := true; 
        end if; 
 
        col.v.REMARK := 'Created by the Object Type Utility'; 
        col.i.REMARK := true; 
 
 
        col.v.SEQUENCE_NUMBER := max_seq + 10; 
        if col.v.SEQUENCE_NUMBER is not null then 
          col.i.SEQUENCE_NUMBER := true; 
        end if; 
        max_seq := max_seq + 10; 
 
        -- Perform the INSERT operation 
        o('Inserting a new Oracle Object Type Attribute (COLumn)'||col.v.NAME); 
        ciocolumn.ins(null,COL); 
 
        -- create the Mapping Element between new Attribute(COL) and existing Column(COL) 
        map.v.CREATED_BY_TRANSFORMER_FLAG := 'Y'; 
        map.i.CREATED_BY_TRANSFORMER_FLAG := true; 
 
        map.v.SOURCE_CIELEMENT_REFERENCE := i.FOREIGN_KEY_ID; 
        map.i.SOURCE_CIELEMENT_REFERENCE := true; 
 
        -- us bug 898618 
        map.v.SOURCE_ELEMENT_TYPE        := 'FCO'; 
        map.i.SOURCE_ELEMENT_TYPE        := true; 
 
        map.v.TARGET_CIELEMENT_REFERENCE := col.v.ID; 
        map.i.TARGET_CIELEMENT_REFERENCE := true; 
 
        -- us bug 898618 
        map.v.TARGET_ELEMENT_TYPE        := 'COL'; 
        map.i.TARGET_ELEMENT_TYPE        := true; 
 
        map.v.TRANSFORMATION_SET_REFERENCE := i_TMS_ID; 
        map.i.TRANSFORMATION_SET_REFERENCE := true; 
 
        -- Perform the INSERT operation 
        o('Inserting a new Mapping Element'); 
        ciomapping_element.ins(null,MAP); 
 
      End Loop; 
    End Loop; 
 
  EXCEPTION 
    -- Catch all exceptions 
    When Others Then 
      raise; 
 
  END create_ref_4_fkey ; 
 
  procedure o (text varchar2) is  
  begin  
    dbms_output.enable(1000000); 
    dbms_output.put_line(text);  
  end; 
 
 
 --  END Local Defined Procedure Bodies 
 
 
END ckootutl; 
/