-- Create sql in-place upgrade patch for values
-- inside public_views.sql.
--
-- Bug #'s/Table OWBB_IMAGES, OWBB_TYPES updated
-- 8736199: CHINESE CHARACTERS CAN NOT BE DISPLAYED CORRECTLY IN BROWSER
-- 

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

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8736199'; -- 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


  
-- **************************************************************************
-- **************************************************************************
--                      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;  

-- ==========================================================================
-- Construct a new query. :nls(xxxxx) is replaced with ':nls(xxxxx)' in the qurey.
  FUNCTION reconstructQueryText(p_qry_text IN VARCHAR2) RETURN VARCHAR2
  IS
-- ==========================================================================
    l_qry_text VARCHAR2(4000, BYTE) := null;
    qry_text VARCHAR2(4000, BYTE) := null;
    nlsStr VARCHAR2(15) := ':nls(';
    rightBracket VARCHAR2(2) := ')';
    newNls VARCHAR2(15) := null;
    ind NUMBER := 0;
    idx NUMBER := 0;
    qry_len NUMBER := 0;
  BEGIN 	
    --put('Original Query: ' || p_qry_text);
    qry_text := p_qry_text;
    ind := instr(qry_text, nlsStr);
    WHILE ind != 0 LOOP
      l_qry_text :=  substr(qry_text, 1, ind - 1);
      idx := instr(qry_text, rightBracket, ind);
      newNls := '''' || substr(qry_text, ind, idx-ind+1) || '''';
      l_qry_text := l_qry_text || newNls;
      qry_len := length(qry_text);
      if idx  < qry_len then
        l_qry_text := l_qry_text || substr(qry_text, idx + 1);
      end if;
      qry_text := l_qry_text;
      ind := instr(qry_text, nlsStr, idx);
    END LOOP;
    --select instr(p_qry_text, nlsStr) into ind from dual;
    --put('New Query: ' || qry_text);
    
    return qry_text;   

  END;

  
-- ==========================================================================
-- get query text of a query with query id.
  FUNCTION getQueryText(p_id IN VARCHAR2) RETURN VARCHAR2
  IS
-- ==========================================================================
    l_qry_text VARCHAR2(4000,BYTE) := null;
    
  BEGIN 	
    select query_text into l_qry_text from owbb_design_queries where query_id = p_id;
    return l_qry_text;
  END;
  
  

-- ==========================================================================
-- Update the original query text.
  PROCEDURE updateQueryText(p_id IN VARCHAR2, p_qry_text IN VARCHAR2) IS
-- ==========================================================================
    l_count NUMBER := 0;   
  BEGIN
    select count(1) into l_count from owbb_design_queries where query_id = p_id;
    if (l_count = 1) then 
      update owbb_design_queries set query_text = p_qry_text where query_id = p_id;
      put('Updated query ' || p_id || '.');
    else
      put('Updated failed. Could not find query || p_id ' ||  '!' || EOL);
    end if;
    
  END;  
  
  
-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  
  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================
    original_query VARCHAR(4000, BYTE) := null;
    new_query VARCHAR(4000, BYTE) := null;
    l_count NUMBER := 0;
    max_id NUMBER := 0;
  -- =======================================================
  BEGIN -- start handling miv definition options defined in miv_siebel.xml
  -- =======================================================   
    select max(query_id) into max_id from owbb_design_queries;
    FOR x in ( select query_id from owbb_design_queries where (usage = 'PQ' or usage = 'TQ') and query_text like '%nls(%') LOOP
      original_query := getQueryText(x.query_id);
      new_query := reconstructQueryText(original_query);
      updateQueryText(x.query_id, new_query);      
      l_count := l_count + 1;
    END LOOP;
    
    put('Count: ' || l_count || EOL);

    commit;
  
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================  


  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/