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>