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