-- Create sql in-place upgrade patch for some Siebel miv option
-- inside miv_siebel.xml. The sql will be shown (as much as 
-- possible) in-line with associated segment of miv_siebel.xml.
-- The xml will be set off from the sql by commenting each
-- line with "--miv_siebel.xml->".
--
-- Bug #'s/Siebel miv property updated
-- 9048066: business domain filter was not supported when importing siebel data
-- 

SET SERVEROUTPUT ON
SET LINESIZE 500
-- ##########################################################################
DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util)
-- ##########################################################################

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 9048066'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  TRACEON  CONSTANT BOOLEAN      := TRUE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled


  -- Record definitions for the various views
  -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway)

-- ==========================================================================
  TYPE CMPWBMIVView IS RECORD (
-- ==========================================================================
 WORKSPACEID             CMPWBMIVView_r.WORKSPACEID%TYPE                   DEFAULT  1                        -- NUMBER(9,0)                1 
 ,DELETIONPARENT         CMPWBMIVView_r.DELETIONPARENT%TYPE                DEFAULT  null                     -- NUMBER(9,0)                2 
 ,AGGREGATEDSIGNATURE    CMPWBMIVView_r.AGGREGATEDSIGNATURE%TYPE           DEFAULT  null --sysdate           -- TIMESTAMP(6)               3 
 ,CLASSNAME              CMPWBMIVView_r.CLASSNAME%TYPE                     DEFAULT  'CMPWBMIVView'           -- VARCHAR2(255)              4 
 ,COMPLETED              CMPWBMIVView_r.COMPLETED%TYPE                     DEFAULT  0                        -- CHAR(1)                    5 
 ,CREATEDBY              CMPWBMIVView_r.CREATEDBY%TYPE                     DEFAULT  'OWBSYS'                 -- VARCHAR2(40)               6 
 ,CREATIONTIMESTAMP      CMPWBMIVView_r.CREATIONTIMESTAMP%TYPE             DEFAULT  null --sysdate           -- DATE                       7 
 ,CUSTOMERDELETABLE      CMPWBMIVView_r.CUSTOMERDELETABLE%TYPE             DEFAULT  0                        -- CHAR(1)                    8 
 ,CUSTOMERRENAMABLE      CMPWBMIVView_r.CUSTOMERRENAMABLE%TYPE             DEFAULT  0                        -- CHAR(1)                    9 
 ,DELETEINOVERRIDE       CMPWBMIVView_r.DELETEINOVERRIDE%TYPE              DEFAULT  0                        -- CHAR(1)                    10
 ,DESCRIPTION            CMPWBMIVView_r.DESCRIPTION%TYPE                   DEFAULT  null --DEFINITIONKEY     -- VARCHAR2(4000)             11
 ,EDITABLE               CMPWBMIVView_r.EDITABLE%TYPE                      DEFAULT  0                        -- CHAR(1)                    12
 ,ELEMENTID              CMPWBMIVView_r.ELEMENTID%TYPE                     DEFAULT  null --ELEMENTID         -- NUMBER(9,0)                13
 ,IMPORTED               CMPWBMIVView_r.IMPORTED%TYPE                      DEFAULT  0                        -- CHAR(1)                    14
 ,LOGICALNAME            CMPWBMIVView_r.LOGICALNAME%TYPE                   DEFAULT  null --NAME              -- VARCHAR2(1000)             15
 ,METADATASIGNATURE      CMPWBMIVView_r.METADATASIGNATURE%TYPE             DEFAULT  null --METADATASIGNATURE -- VARCHAR2(255)              16
 ,NAME                   CMPWBMIVView_r.NAME%TYPE                          DEFAULT  null --NAME              -- VARCHAR2(1000)             17
 ,NOTE                   CMPWBMIVView_r.NOTE%TYPE                          DEFAULT  null                     -- VARCHAR2(4000)             18
 ,NOTM                   CMPWBMIVView_r.NOTM%TYPE                          DEFAULT  0                        -- NUMBER(9,0)                19
 ,OVERRIDEATTRIBUTES     CMPWBMIVView_r.OVERRIDEATTRIBUTES%TYPE            DEFAULT  0                        -- CHAR(1)                    20
 ,OVERRIDECHILDREN       CMPWBMIVView_r.OVERRIDECHILDREN%TYPE              DEFAULT  0                        -- CHAR(1)                    21
 ,OVERRIDEROLES          CMPWBMIVView_r.OVERRIDEROLES%TYPE                 DEFAULT  0                        -- CHAR(1)                    22
 ,PERSISTENT             CMPWBMIVView_r.PERSISTENT%TYPE                    DEFAULT  1                        -- CHAR(1)                    23
 ,SEEDED                 CMPWBMIVView_r.SEEDED%TYPE                        DEFAULT  1                        -- CHAR(1)                    24
 ,STRONGTYPENAME         CMPWBMIVView_r.STRONGTYPENAME%TYPE                DEFAULT  null                     -- VARCHAR2(255)              25
 ,UOID                   CMPWBMIVView_r.UOID%TYPE                          DEFAULT  null --UOID              -- VARCHAR2(40)               26
 ,UPDATEDBY              CMPWBMIVView_r.UPDATEDBY%TYPE                     DEFAULT  'OWBSYS'                 -- VARCHAR2(40)               27
 ,UPDATETIMESTAMP        CMPWBMIVView_r.UPDATETIMESTAMP%TYPE               DEFAULT  null --sysdate           -- DATE                       28
 ,ICONOBJECT             CMPWBMIVView_r.ICONOBJECT%TYPE                    DEFAULT  null                     -- NUMBER(9,0)                29
 ,FIRSTCLASSOBJECT       CMPWBMIVView_r.FIRSTCLASSOBJECT%TYPE              DEFAULT  null                     -- NUMBER(9,0)                30
 ,ISDEFAULT              CMPWBMIVView_r.ISDEFAULT%TYPE                     DEFAULT  '1'                      -- CHAR(1)                    31
 ,VIEWTEXT               CMPWBMIVView_r.VIEWTEXT%TYPE                   DEFAULT  null                     -- CLOB                       32
 ,VIEWTYPE                  CMPWBMIVView_r.VIEWTYPE%TYPE              DEFAULT  'SQL'                    -- VARCHAR2(40)               33
 ,MIVDEFINITION           CMPWBMIVView_r.MIVDEFINITION%TYPE                 DEFAULT  null                     -- NUMBER(9,0)                34
);  
  
-- **************************************************************************
-- **************************************************************************
--                      Useful functions/procedures
-- **************************************************************************
-- **************************************************************************

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE put(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      put('DBMS_OUTPUT Enabled.');
    END IF;
    
    IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN
      -- 10.1 database can't handle messages with length > 255
      --DBMS_OUTPUT.put_line('Message length > 255');
      --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255));
      -- Break the string up into lines or 255-byte chunks
      DECLARE
        l_msg_line VARCHAR2(255);
        l_eol_index NUMBER;
        l_curr_index NUMBER;
        l_msg_len NUMBER := LENGTH(msg);
        l_loop_index NUMBER := 0;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':');
        l_eol_index := 1;
        l_curr_index := 1;
        l_loop_index := 0;
        WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP
          l_eol_index := INSTR(msg, EOL, l_curr_index);
          --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index));
          IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN
            l_msg_line := SUBSTR(msg, l_curr_index, 255);
            l_curr_index := l_curr_index + 255;
          ELSE 
            l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index);
            l_curr_index := l_eol_index + 1;
          END IF;
          DBMS_OUTPUT.PUT_LINE(l_msg_line);        
          l_loop_index := l_loop_index + 1;
        END LOOP;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
    ELSE 
      DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);
    END IF;  

  END;

-- ==========================================================================
-- put message to output controlled by TRACEON variable (uses put)
  PROCEDURE putTrace(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF TRACEON THEN
      put(msg);
    END IF;
  END;  

-- ==========================================================================
-- return next element id for new objects
  FUNCTION getNextElementId RETURN NUMBER
-- ==========================================================================
  IS
    l_id NUMBER;
  BEGIN
      SELECT cwmseq.nextval
        INTO l_id
        FROM DUAL;
      RETURN l_id;
  END;


-- ==========================================================================
--                         CMPWBMIVView
  FUNCTION newCMPWBMIVView(
    workspaceId                IN NUMBER   DEFAULT 0
    ,elementId                 IN NUMBER   DEFAULT 0
    ,uoid                      IN VARCHAR2 DEFAULT null
    ,logicalName               IN VARCHAR2 DEFAULT null
    ,name                      IN VARCHAR2 DEFAULT null
    ,metadataSignature         IN VARCHAR2 DEFAULT null
    ,firstClassObject          IN NUMBER   DEFAULT 0
    ,mivDefinition             IN NUMBER   DEFAULT 0
    ,viewText                  IN CLOB  
  ) RETURN CMPWBMIVView
-- ==========================================================================
  IS
    mivView CMPWBMIVView;
  BEGIN
    mivView.WORKSPACEID := workspaceId;
    mivView.AGGREGATEDSIGNATURE := SYSDATE;
    mivView.CREATIONTIMESTAMP := SYSDATE;
    mivView.ELEMENTID := elementId;
    mivView.UOID := uoid;
    mivView.LOGICALNAME := logicalName;
    mivView.METADATASIGNATURE := metadataSignature;
    mivView.NAME := name;
    mivView.UOID := uoid;
    mivView.UPDATETIMESTAMP := SYSDATE;
    mivView.FIRSTCLASSOBJECT := firstClassObject;
    mivView.MIVDEFINITION := mivDefinition;
    mivView.VIEWTEXT := viewText;
    RETURN mivView;
  END newCMPWBMIVView; 
  

-- ==========================================================================  
  PROCEDURE AddCMPWBMIVView(
    p_mivView IN CMPWBMIVView
  )
-- ==========================================================================
  IS
    l_count NUMBER := 0;
  BEGIN
     select count(1) into l_count from CMPWBMIVView_r where 
         NAME = p_mivView.NAME 
         and MIVDEFINITION = p_mivView.MIVDEFINITION
         and WORKSPACEID = p_mivView.WORKSPACEID
         and VIEWTYPE = p_mivView.VIEWTYPE;
     If l_count = 1 then
       put('Found CMPWBMIVView with name ''' || p_mivView.NAME ||'''. Will update it.' || EOL);
       update CMPWBMIVView_r set VIEWTEXT = p_mivView.VIEWTEXT where NAME = p_mivView.NAME
             and MIVDEFINITION = p_mivView.MIVDEFINITION
             and WORKSPACEID =   p_mivView.WORKSPACEID
             and VIEWTYPE = p_mivView.VIEWTYPE;
     else
       If l_count = 0 then
         put('Add CMPWBMIVView with name ''' || p_mivView.NAME ||'''.' || EOL);
         insert into CMPWBMIVView_r VALUES p_mivView;
       else
         put('Error! Found ' || l_count || ' CMPWBMIVViews with same name ''' || p_mivView.NAME || '''!' || EOL);
       end if;
     end if;
     
  END;
    
   


-- ==========================================================================
-- return the id (elementid) for the MIVDefinition defined by the input parameters
  FUNCTION getMivDefinitionId(
     p_owningFolder IN NUMBER,
     p_mivDefinitionName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_owningFolder NUMBER := p_owningFolder;
    l_mivDefinitionName VARCHAR2(255) := p_mivDefinitionName;
    l_mivDefinitionId NUMBER;
  BEGIN

      putTrace('getMivDefinitionId(' || l_owningFolder || ', ' || l_mivDefinitionName || ')');
      SELECT ELEMENTID
        INTO l_mivDefinitionId
        FROM CMPWBMIVDefinition_r
        WHERE NAME = l_mivDefinitionName and OWNINGFOLDER = l_owningFolder;
      putTrace('RETURN ' || l_mivDefinitionId || EOL);

    RETURN l_mivDefinitionId;
  END getMivDefinitionId; 
  
  
  
-- ==========================================================================
-- return the id (elementid) for the CMPWBMIVView defined by the input parameters
  FUNCTION getCMPWBMIVViewId(
     p_workspaceId IN NUMBER,
     p_mivDefinition IN NUMBER,
     p_mivViewName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_workspaceId NUMBER := p_workspaceId;
    l_mivDefinition NUMBER := p_mivDefinition;
    l_mivViewName VARCHAR2(255) := p_mivViewName;
    l_mivViewId NUMBER;
  BEGIN

      putTrace('getCMPWBMIVViewId(' || l_workspaceId || ', ' || l_mivDefinition || ', ' || l_mivViewName || ')');
      SELECT ELEMENTID
        INTO l_mivViewId
        FROM CMPWBMIVView_r
        WHERE WORKSPACEID = l_workspaceId and NAME = l_mivViewName and MIVDEFINITION = l_mivDefinition;
      putTrace('RETURN ' || l_mivViewId || EOL);

    RETURN l_mivViewId;
  END getCMPWBMIVViewId; 
   
   

-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  

  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================
   mivView CMPWBMIVView := null;      
   l_mivDefinitionId NUMBER;
   l_mivViewId NUMBER;
   l_mivBusinessAreaId NUMBER := getNextElementId;
   l_mivBusinessAreaObjectsId NUMBER := getNextElementId;
   l_mivBusinessAreaUOID VARCHAR2(40) := RAWTOHEX(SYS_GUID());
   l_mivBusinessAreaObjectsUOID VARCHAR2(40) := RAWTOHEX(SYS_GUID());
  -- =======================================================
  BEGIN -- start handling miv definition options defined in miv_siebel.xml
  -- =======================================================   
     FOR x in (select elementId from CMPWorkspace_r) LOOP
        put('Processing workspace [id: ' || x.elementId || ']' || EOL);
   
        l_mivDefinitionId := getMivDefinitionId(x.elementId, 'SIEBEL');
        mivView := newCMPWBMIVView(
               x.elementId                                                 --workspaceId           
               ,l_mivBusinessAreaId                                        --elementId
               ,l_mivBusinessAreaUOID                                      --UOID
               ,'miv_business_areas'                                        --logicalname    
               ,'miv_business_areas'                                        --name           
               ,'cea123f7bf058f4c8994c5a9c666bf30'                          --metadataSignature        
               ,l_mivDefinitionId                                           --firstClassObject 
               ,l_mivDefinitionId                                           --mivDefinition
               ,'<miv_business_areas type="SQLStatement" default="true">select * from (                  
             select ''1'' business_area_id,
             ''0'' parent_business_area_id,
             ''Siebel Business Component''  business_area_name,
             ''Siebel Business Component''  business_name,
             ''Siebel Business Component''  description
             from dual
             union 
             select DISTINCT  BC.row_id business_area_id,
                   ''1'' parent_business_area_id,
                   BC.name business_area_name,
                   BC.name business_name,
                   BC.comments description
             from S_BUSCOMP<Parameter name="dblink"/> BC,
             S_REPOSITORY<Parameter name="dblink"/> REPO_1
            where BC.repository_id = REPO_1.row_id and REPO_1.name = ''Siebel Repository''
           ) order by parent_business_area_id, business_area_name
           </miv_business_areas>'                                                         
          );
          AddCMPWBMIVView(mivView);
        
        mivView := newCMPWBMIVView(
               x.elementId                                                         --workspaceId               
               ,l_mivBusinessAreaObjectsId                                         --elementId
               ,l_mivBusinessAreaObjectsUOID                                       --UOID
               ,'miv_business_area_objects'                                        --logicalname    
               ,'miv_business_area_objects'                                        --name           
               ,'014b2180c98b7e5b15fcce49ed97025b'                                 --metadataSignature        
               ,l_mivDefinitionId                                                  --firstClassObject 
               ,l_mivDefinitionId                                                  --mivDefinition
               ,'<miv_business_area_objects type="SQLStatement" default="true">
                 select distinct  a.name object_name,
                       b.buscomp_id parent_business_area_id,
                 ''TABLE'' type,
                 a.desc_text description 
                 from S_TABLE<Parameter name="dblink"/> a, 
                 (     select table_name, row_id as buscomp_id, repository_id from S_BUSCOMP<Parameter name="dblink"/>
                 union
                 select distinct dest_tbl_name as table_name, buscomp_id, repository_id from S_JOIN<Parameter name="dblink"/> 
                 union
                 select distinct join_name as table_name, buscomp_id, repository_id from S_FIELD<Parameter name="dblink"/> t_field
                 where join_name is not null and
                 not exists (select dest_tbl_name from S_JOIN<Parameter name="dblink"/> t_join
                 where t_join.name = t_field.join_name and t_join.buscomp_id = t_field.buscomp_id )
                 ) b,
                 S_REPOSITORY<Parameter name="dblink"/> c
                 where a.name = b.table_name and a.repository_id = b.repository_id and a.repository_id = c.row_id and c.name = ''Siebel Repository''
                 </miv_business_area_objects>'             
          );
          AddCMPWBMIVView(mivView);
       
        l_mivViewId := getCMPWBMIVViewId(x.elementId,l_mivDefinitionId,'miv_capabilities');
        put('Update miv_capabilities of CMPWBMIVView: [ elementId: ' || l_mivViewId  || ']' || EOL);
        update CMPWBMIVVIEW_R set VIEWTEXT = '<miv_capabilities type="ResultSet">
                                               <table_supported>true</table_supported>
                                              <view_supported>false</view_supported>
                                               <sequence_supported>false</sequence_supported>
                                               <table_name_filter_supported>true</table_name_filter_supported>
                                               <view_name_filter_supported>false</view_name_filter_supported>
                                               <sequence_name_filter_supported>false</sequence_name_filter_supported>
                                               <business_area_supported>true</business_area_supported>   
                                               <business_area_table_supported>true</business_area_table_supported>   
                                               <business_area_view_supported>false</business_area_view_supported>   
                                               <business_area_sequence_supported>false</business_area_sequence_supported>   
                                               <application_owner_supported>true</application_owner_supported>
                                               <table_fklevel_supported>false</table_fklevel_supported>
                                               <reimport_supported>true</reimport_supported>
                                               <data_object_at_leaf_levels>true</data_object_at_leaf_levels>
                                               <multiple_tree_supported>false</multiple_tree_supported>
                                              </miv_capabilities>'
       where ELEMENTID = l_mivViewId and WORKSPACEID = x.elementId;
       
      put('End processing workspace [id: ' || x.elementId || ']' || EOL);
   
     END LOOP;
  
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================    
  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/