Noetix: Extracting All Changes To a NoetixView To A Single File

NOTE: Last update, adding in some more information to the report, new version 0.9.2. It's now a requirement that you use this script to Omit Columns (makes the SQL much more readable, and reduces the amount of code that needs to be updated when Noetix update their processes - as they did at 6.0.2).

The purpose of this script is to combine all the changes made by a specific group of users to a single NoetixView template (in the tables N_View_Column_Templates, N_View_Table_Templates, and N_View_Where_Templates) into a single file split into four sections according to the template below;

-- NOETIX VIEW UPDATE TEMPLATE V0.9.1
-- NOTE: All changes to a single Noetix view should be included in one file (base view changes
--   should be in a separate file)
--
@utlspon

-- Section 1: Removing existing columns, queries, etc use a single update where possible

-- Section 2: Table (and Where clause) additions. Group by the change, not the type (i.e. add the
--   first table, then add the where clauses for the first table, then the second table, then the
--   where clauses for the second table, etc)

-- Section 3: Column Additions (in alphabetical order of Column_Label)

-- Section 4: Updates

COMMIT;

@utlspoff
 
The script is available here (via Google Docs), because it's quite long I've not copied/ pasted it into this blog.

Just to give you some idea we have approximately 3,000 files, the aim of using this script is to reduce this to a more manageable number (in the ten's).

The newly announced NoetixViews Workbench will (hopefully) further reduce the need for these legacy files.

Noetix: Generating a (XU2) Column-Addition Script

In order to use the code below you need to replace the values for v_ViewLabel and v_ColumnLabel in the block with values that are relevant to the column you are trying to add. This script uses the standard DBMS_OUTPUT package to output the script - depending on the nature of the column you're copying this could be quite big.

If you want to include new tables in your column addition script then set the v_IncludeWhere clause to Y (otherwise only the new column script will be generated).

The script is below;

declare
  v_ViewLabel    n_view_column_templates.view_label%TYPE := 'GMF_Order_Details_Base'; -- Must be direct value from table, case sensitive!
  v_ColumnLabel  n_view_column_templates.column_label%TYPE := 'Location';
  v_IncludeWhere varchar2(1) := 'N'; -- Must be either Y or N
  v_ColumnCount  number := 1;

  v_HeaderSQL varchar2(4000);
  v_DataSQL   varchar2(4000);
  v_SQL       varchar2(4000);
  v_result    varchar2(4000);

  procedure AddToHeader(v_Text in varchar) as
  begin
    if length(v_HeaderSQL) > 100 then
      dbms_output.put_line(v_headerSQL);
      v_headerSQL := '  ';
    end if;
    v_headerSQL := v_headerSQL || v_Text;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_' ||
                       lower(v_ColumnLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('COLUMN max_col NEW_VALUE max_col_position');
  dbms_output.put_line('');
  dbms_output.put_line('SELECT MAX(column_position) max_col');
  dbms_output.put_line('FROM   n_view_column_templates');
  dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
  dbms_output.put_line('');

  if (v_IncludeWhere = 'Y') then
    dbms_output.put_line('COLUMN max_where NEW_VALUE max_where_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(where_clause_position) max_where');
    dbms_output.put_line('FROM   n_view_where_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
    dbms_output.put_line('COLUMN max_from NEW_VALUE max_from_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(from_clause_position) max_from');
    dbms_output.put_line('FROM   n_view_table_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
 
    for v_Data in (select n.*, n.rowid
                     from n_view_table_templates n
                    where n.view_label = v_ViewLabel
                      and n.table_alias in
                          (select n.table_alias
                             from n_view_column_templates n
                            where n.view_label = v_ViewLabel
                              and n.column_label = v_ColumnLabel)) loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_table_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG',
                                       'GEN_SEARCH_BY_COL_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_TABLE_TEMPLATES'
                          AND atc.column_name not in
                              ('INCLUDE_FLAG', 'GEN_SEARCH_BY_COL_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_TABLE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'FROM_CLAUSE_POSITION' then
          v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
 
    v_ColumnCount := 1;
    for v_Data in (select n.*, n.rowid
                     from n_view_where_templates n
                    where n.view_label = v_ViewLabel
                      and n.where_clause like
                          '%' ||
                          (select q.table_alias
                             from n_view_column_templates q
                            where q.view_label = v_ViewLabel
                              and q.column_label = v_ColumnLabel
                              and q.QUERY_POSITION  = n.QUERY_POSITION) || '%') loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_where_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_WHERE_TEMPLATES'
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_WHERE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'WHERE_CLAUSE_POSITION' then
          v_result := '(&max_where_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
  end if;
  v_ColumnCount := 1;
  for v_Data in (select n.*, n.rowid
                   from n_view_column_templates n
                  where n.view_label = v_ViewLabel
                    and lower(n.column_label) = lower(v_ColumnLabel)) loop
    v_HeaderSQL := '  ';
    v_DataSQL   := '';
    dbms_output.put_line('INSERT INTO n_view_column_templates(');
    for v_Column in (select atc.column_name,
                            atc.data_type,
                            atc.column_id,
                            (select max(atc2.column_id)
                               from all_tab_columns atc2
                              where atc2.owner = USER
                                AND atc2.column_name not in ('INCLUDE_FLAG')
                                and atc2.table_name = atc.table_name) as max_column_id
                       from all_tab_columns atc
                      where atc.owner = USER
                        and atc.table_name = 'N_VIEW_COLUMN_TEMPLATES'
                        AND atc.column_name not in ('INCLUDE_FLAG')
                      order by atc.column_id) loop
      v_SQL := 'SELECT T.' || V_Column.Column_name ||
               ' FROM N_VIEW_COLUMN_TEMPLATES T WHERE T.ROWID = ''' ||
               v_Data.rowid || '''';
      EXECUTE IMMEDIATE v_SQL
        into v_result;
      if instr(v_Result, '''') > 0 then
        v_Result := Replace(v_result, '''', '''''');
      end if;
      if v_Column.Column_Name = 'COLUMN_POSITION' then
        v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
      end if;
      if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
        v_result := SYSDATE;
      end if;
   
      if (v_result is not null) then
        if (v_Column.Column_Id = v_Column.Max_Column_Id) then
          AddToheader(lower(V_Column.Column_name) || ')');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''') -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || ''')) -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end;
        else
          AddToHeader(lower(V_Column.Column_name) || ', ');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''', -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || '''), -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end || chr(13);
        end if;
      end if;
    end loop;
    dbms_output.put_line(v_headerSQL);
    dbms_output.put_line('VALUES(');
    dbms_output.put_line(v_DataSQL);
    dbms_output.put_line(';');
 
    v_ColumnCount := v_ColumnCount + 1;
    dbms_output.put_line('');
  end loop;

  dbms_output.put_line('COMMIT;');
  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');

end;

Noetix: Extracting a View As A Script

UPDATE 16-DEC-2012: Added in the table n_view_col_property_templates. Where a view is using key flex fields the copying of the view was failing as the additional information (well, for Inventory anyway) was not being populated in this table.

The following script generates source code (i.e. uses DBMS_OUTPUT.PUT_LINE), you will need to copy/paste this into a separate file AND then change the name of the view before you run it (otherwise you will get a lot of duplicate errors).

Five fields are overwritten by this script; Last Updated By/Created By (replaced with the value in the variable at the top), Last Updated Date/Creation Date (set to today), and the product version set to 12+.

To add other tables you can just add calls to "ProcessTable" (rows 91-96) for the additional tables you want to include.

The script is available here (via Google Docs) or is copy/ pasted below;

declare
  v_ViewLabel     n_view_column_templates.view_label%TYPE := 'GMD_Recipes'; -- Must be direct value from table n_views, case sensitive!
  v_LastUpdatedBy n_view_templates.last_updated_by%TYPE := 'A Pellew'; -- The user who performed the last update (i.e. you)

  procedure ProcessTable(v_TableName in varchar2) as
    TYPE rowidRec IS RECORD(
      ri rowid);
    TYPE rowidSet IS TABLE OF rowidRec;
    v_Items rowidSet;

    v_HeaderSQL varchar2(4000);
    v_DataSQL   varchar2(4000);
    v_SQL       varchar2(4000);
    v_result    varchar2(4000);
    procedure AddToHeader(v_Text in varchar) as
    begin
      if length(v_HeaderSQL) > 100 then
        dbms_output.put_line(v_headerSQL);
        v_headerSQL := '  ';
      end if;
      v_headerSQL := v_headerSQL || v_Text;
    end;
  begin
    dbms_output.put_line('-- Performing updates to table ' ||
                         upper(v_tablename));
    EXECUTE IMMEDIATE 'SELECT n.rowid FROM ' || v_TableName ||
                      ' n WHERE n.view_label = ''' || v_ViewLabel || '''' BULK
                      COLLECT
      INTO v_Items;
    for v_Item in v_Items.First .. v_items.Last loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO ' || Lower(v_TableName) || ' (');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = Upper(v_TableName)
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name || ' FROM ' ||
                 Upper(v_TableName) || ' T WHERE T.ROWID = ''' || v_Items(v_Item).ri || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name in ('LAST_UPDATE_DATE', 'CREATION_DATE') then
          v_result := SYSDATE;
        end if;
        if v_Column.Column_Name IN ('LAST_UPDATED_BY', 'CREATED_BY') then
          v_result := v_LastUpdatedBy;
        end if;
        if v_Column.Column_Name IN ('T_COLUMN_PROPERTY_ID') then
          v_result := '(SELECT MAX(t_column_property_id)+1 FROM n_view_col_property_templates)';
        end if;
    
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' || lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
            AddToheader(lower(V_Column.Column_name) || ')');
          else
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      dbms_output.put_line('');
    end loop;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('SET SCAN OFF');
  dbms_output.put_line('');
  ProcessTable('n_view_templates');
  ProcessTable('n_role_view_templates');
  ProcessTable('n_view_query_templates');
  ProcessTable('n_view_table_templates');
  ProcessTable('n_view_where_templates');
  ProcessTable('n_view_column_templates');
  ProcessTable('n_view_col_property_templates');

  dbms_output.put_line('COMMIT;');  dbms_output.put_line('');  dbms_output.put_line('SET SCAN ON');  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');
end;

Noetix: Adding a new Z$ Column Reference

Sometimes you need to add an additional Z$ column to link between two view. This Google Knol tells you what you need to know to make a simple customisation.

NOTE: It's worth pointing out that there are a narrow range of customisations supported by Noetix and this isn't one of them. If you have purchased NCM (Noetix Customisation Maintenance) then it's likely that you can get them to support this enhancement but it's at the discretion of whoever handles your NCM files.


We will be making this change to the template for a view - what this means is if multiple views are generated from the template all will contain the additional column. This is by far the simplest way to add a new column (we shy away from customising individual views).

For the purposes of this example I'll be adding in a link between the PO_Receipts and the PO_PO_Distributions templates, I'm working against Oracle e-Business Suite version 12.1.3 and as things stand at the moment (Noetix views 6.0.1) there is no existing link.

Looking at the SQL of the built view I can see that the table alias POD maps to PO.PO_DISTRIBUTIONS_ALL which is the oracle base table that I want to publish the ROWID from in order to link to PO_PO_Distributions (if you open the target view, from the PO_PO_Distributions template, you can see the existing Z$ column Z$NPLPO_PO_Distributions is based on the PDSTR alias which in turn maps to PO.PO_DISTRIBUTIONS_ALL which is correct).

If you look into look into the N_VIEW_TABLE_TEMPLATES table (which is a Noetix object) you can see that the active query position at R12 for the view PO_Receipts is 2. This can be tricky to spot but you were provided with a script called get_data_tmpl.sql during the Noetix training which provides the answer.

Finally the SQL;

UPDATE N_VIEW_TABLE_TEMPLATES
SET
  BASE_TABLE_FLAG = 'Y',
  GEN_SEARCH_BY_COL_FLAG = 'Y',
  KEY_VIEW_LABEL = 'PO_PO_Distributions' -- the target template
WHERE 1=1
AND VIEW_LABEL = 'PO_Receipts' -- template name
AND QUERY_POSITION = 2 -- From get_data_tmpl.sql
AND TABLE_ALIAS = 'POD' -- table alias of PO.PO_DISTRIBUTIONS_ALL from PO_Receipts
;

You'll need to save this in a script can call it from wnoetxu2.sql in the normal way.