Noetix: Omitting A Column From a Template (Script)

As you might be aware Noetix 6.0.2 has introduced a new way to omit columns from view templates. Previous you needed to update the Product_Version column with a product version that would never be used by your installation (we typically picked '8'), from the new version there is a new column called User_Include_Flag which you can set to 'N' to exclude a column.

This makes a lot more sense but it has highlighted the issue that we have a lot of changes to make to our various script files in order to introduce the new functionality - hardly ideal. In order to get round this we have introduced a new script filed called "omit_view_column_template_xu2.sql";

define noetix_view = &1;
define column_label = &2;
define update_date = &3;

UPDATE N_View_Column_Templates N
   SET N.USER_INCLUDE_FLAG = 'N',
       n.last_update_date = TO_DATE('&update_date'),
       n.last_updated_by = 'A Pellew'
 WHERE n.view_label = '&noetix_view'
   AND N.column_label = '&column_label'
;
 
COMMIT;

This is pretty simple, it takes three parameters and omits the specified column from the specified view updating the Last_Update_Date to the date you specify. You'll notice my name is hard-coded in the Last_Updated_By column - feel free to replace with your own.

This way if there is any future changes in Noetix functionality we only need to update a single file to ensure they are taken into account!

As an example the following (from wnoetxu2.sql);

@omit_view_column_template_xu2.sql INV_Onhand_Quantities Organization 21-JUN-2012

Will omit the column "Organization" from the view INV_Onhand_Quantities (setting the date to 21-JUN-2012).

In the logfile you will see the following output when the script is called;

SQL> @omit_view_column_template_xu2.sql INV_Onhand_Quantities Organization 21-JUN-2012
SQL> define noetix_view = &1;
SQL> define column_label = &2;
SQL> define update_date = &3;
SQL>
SQL> UPDATE N_View_Column_Templates N
  2     SET N.USER_INCLUDE_FLAG = 'N',
  3         n.last_update_date = TO_DATE('&update_date'),
  4         n.last_updated_by = 'A Pellew'
  5   WHERE n.view_label = '&noetix_view'
  6     AND N.column_label = '&column_label'
  7  ;
old   3:        n.last_update_date = TO_DATE('&update_date'),
new   3:        n.last_update_date = TO_DATE('21-JUN-2012'),
old   5:  WHERE n.view_label = '&noetix_view'
new   5:  WHERE n.view_label = 'INV_Onhand_Quantities'
old   6:    AND N.column_label = '&column_label'
new   6:    AND N.column_label = 'Organization'

2 rows updated.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SQL>

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: Removing Obsolete Columns at R12

This blog post includes a script-generating piece of PL/SQL that will write a series of scripts for you that will remove all the obsolete columns from your Noetix Views following a R12 upgrade.

For example if you look at the Finance views for Payables then you will see the column "Set_Of_Books_Name". Oracle have replaced sets of books in R12 so this column is no longer relevant. Noetix, rather than removing the column, have changed it so that rather than displaying data it just displays the results of a TO_CHAR(NULL) function call - i.e. Nothing.

If you speak to Noetix they will tell you that this allows your code to work across versions *however* in our experience of an R12 upgrade all this allowed was code that needed re-pointing to the new structures to *appear* to work. In the specific case of significant change like this experience has shown me that it's better to have everything collapsing in a big heap than appearing to work when it doesn't!

The following SQL detects the new "obsolete" columns at R12;

select n.view_label, n.column_label, n.query_position
  from n_view_column_templates n
 where n.column_expression like '%(NULL)%'
   and n.include_flag = 'Y'
   and n.product_version like '12%'
   and not exists (select 1
          from n_view_column_templates t
         where t.column_expression not like '%(NULL)%'
           and t.include_flag = 'Y'
           and t.product_version like '12%'
           and t.view_label = n.view_label
           and t.column_label = n.column_label)
 order by n.view_label, n.column_label, n.query_position

When you run it it will give you some idea of the extent of your problem (which will obviously be more significant the more you use oracle - for us this query returned move than 5,000 rows).

For every record returned by this query the script will generate output. In most cases there is a single query for each of the columns so you will see something like;

@utlspon ap_checks_set_of_books_name_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Checks')
   AND UPPER(column_label) = UPPER('Set_Of_Books_Name')
   AND QUERY_POSITION = 1
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

In this case this is updating the view template AP_Checks, and moving the product_version of the "Set_Of_Books_Name" column back to version 8 - this will prevent it being picked up during a regenerate.

In the case of multiple queries the script will generate something similar to;

@utlspon ap_invoice_distributions_posted_amount_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 4
   AND PRODUCT_VERSION LIKE '12%'
;
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 5
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

This is removing the column "Posted_Amount" from the "AP_Invoice_Distributions" template where it appears in the 4th and 5th query positions.

The script is given below;

declare
  v_OldViewLabel   n_view_column_templates.view_label%TYPE := '@';
  v_OldColumnLabel n_view_column_templates.column_label%TYPE := '@';

  v_LastUpdateDate n_view_column_templates.last_update_date%TYPE := SYSDATE;
  v_LastUpdatedBy  n_view_column_templates.last_updated_by%TYPE := 'A Pellew';
begin
  for v_Data in (select n.view_label, n.column_label, n.query_position
                   from n_view_column_templates n
                  where n.column_expression like '%(NULL)%'
                    and n.include_flag = 'Y'
                    and n.product_version like '12%'
                    and not exists
                  (select 1
                           from n_view_column_templates t
                          where t.column_expression not like '%(NULL)%'
                            and t.include_flag = 'Y'
                            and t.product_version like '12%'
                            and t.view_label = n.view_label
                            and t.column_label = n.column_label)
                  order by n.view_label, n.column_label, n.query_position) loop
    if v_Data.view_label <> v_OldViewLabel or
       v_Data.column_label <> v_OldColumnLabel then
      if v_OldViewLabel <> '@' then
        dbms_output.put_line(' ');
        dbms_output.put_line('COMMIT; ');
        dbms_output.put_line(' ');
        dbms_output.put_line('@utlspoff ');
        dbms_output.put_line(LPAD('-', 40, '-'));
      end if;
      dbms_output.put_line('@utlspon ' || lower(v_Data.view_label) || '_' ||
                           lower(v_Data.column_label) || '_upd_xu2 ');
      v_OldViewLabel   := v_Data.view_label;
      v_OldColumnLabel := v_Data.column_label;
      dbms_output.put_line(' ');
    end if;
    dbms_output.put_line('UPDATE n_view_column_templates ');
    dbms_output.put_line('   SET product_version = ''8'', ');
    dbms_output.put_line('       last_update_date = TO_DATE(''' ||
                         TO_CHAR(v_LastUpdateDate, 'DD-MON-YYYY') ||
                         '''), ');
    dbms_output.put_line('       last_updated_by = ''' || v_LastUpdatedBy ||
                         ''' ');
    dbms_output.put_line(' WHERE UPPER(view_label) = UPPER(''' ||
                         v_Data.view_label || ''') ');
    dbms_output.put_line('   AND UPPER(column_label) = UPPER(''' ||
                         v_Data.column_label || ''') ');
    dbms_output.put_line('   AND QUERY_POSITION = ' ||
                         TO_CHAR(v_Data.Query_Position));
    dbms_output.put_line('   AND PRODUCT_VERSION LIKE ''12%'' ');
    dbms_output.put_line(';');
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line('COMMIT; ');
  dbms_output.put_line(' ');
  dbms_output.put_line('@utlspoff ');
  dbms_output.put_line(LPAD('-', 40, '-'));
end;

You should change "A Pellew" at the top to be your own name!

NOTE: Two files are generated in error (due to the base data from Noetix not following their own standards - or at least not following any standards they tell developers to follow!). The two files (in our installation, there might be others in yours) are;

ar_std_rcpt_dist_sla_gl_je_line_item_number_upd_xu2.sql (Receivables)
fa_adjustments_sla_gl_je_acct$_upd_xu2.sql (Fixed Assets)

When you run your regenerate after adding all the files errors quickly show up. Just stop using files which prevent your regenerate from working (did that need saying?!). If you consider that we added almost 500 files finding 2 with errors is a pretty good error rate - imagine trying to write them all by hand.

Noetix: Omitting Columns from the View Templates

The script works by updating the PRODUCT_VERSION column in the Noetix view with a version you aren't running (version 8) so that when the regenerate happens the column is not displayed. To use the script below you need to update the v_ColumnName variable with the column you wish to remove.

The script will then generate a "removal" script for each occurrence of the column in the system. Each of the ones you want to use then needs to be copy/pasted into their own file and called from XU2. A line of dashes marks where the place between scripts.

Sample output is included after the PL/SQL block;

declare
  v_ColumnName all_tab_columns.COLUMN_NAME%TYPE := UPPER('Lot_Status');
begin
  for v_Data in (SELECT DISTINCT nvct.column_label, nvct.view_label
                   FROM n_view_column_templates nvct
                  WHERE UPPER(nvct.column_label) = v_ColumnName
                  ORDER BY nvct.column_label, nvct.view_label) loop
    dbms_output.put_line('@utlspon ' ||
                         lower(v_Data.view_label) || '_' ||
                         lower(v_Data.column_label) || '_upd_xu2 ');
    dbms_output.put_line(' ');
    dbms_output.put_line('UPDATE n_view_column_templates ');
    dbms_output.put_line('   SET product_version = ''8'' ');
    dbms_output.put_line(' WHERE UPPER(view_label) = UPPER(''' ||
                         v_Data.view_label || ''') ');
    dbms_output.put_line('   AND UPPER(column_label) = UPPER(''' ||
                         v_Data.column_label || ''') ');
    dbms_output.put_line(';');
    dbms_output.put_line(' ');
    dbms_output.put_line('COMMIT; ');
    dbms_output.put_line(' ');
    dbms_output.put_line('@utlspoff ');
    dbms_output.put_line(LPAD('-', 40, '-'));
  end loop;
end;

On our system (where we use process manufacturing) this generates the following output;

@utlspon gmi_inv_alloc_unalloc_base_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('GMI_Inv_Alloc_Unalloc_Base')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon gmi_inv_transactions_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('GMI_Inv_Transactions')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon gmi_onhand_inv_by_lot_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('GMI_Onhand_Inv_By_Lot')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon gmi_unallocated_inventory_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('GMI_Unallocated_Inventory')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_alloc_unalloc_base_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Alloc_Unalloc_Base')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_item_onhand_by_lot_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Item_Onhand_By_Lot')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_lot_details_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Lot_Details')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_lot_transactions_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Lot_Transactions')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_serial_number_trans_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Serial_Number_Trans')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_transaction_details_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Transaction_Details')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------
@utlspon inv_unallocated_inventory_lot_status_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8'
 WHERE UPPER(view_label) = UPPER('INV_Unallocated_Inventory')
   AND UPPER(column_label) = UPPER('Lot_Status')
;
 
COMMIT;
 
@utlspoff
----------------------------------------