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.