Wednesday, September 7, 2011

Noetix: Adding a Flexfield to a View

This blog post covers a step-by-step guide to adding a Flexfield into a NoetixView. As is the case with all Noetix changes if you do now have NCM (Noetix Customisation Mantenance) these changes will not be supported by Noetix.

In order to add a flexfield you need to identify the table and columns used to store the data. Flexfield data will typically be stored in the ATTRIBUTE1..x columns of the table you specify, but this isn't always the case! You only need the "general" information, not down the the segment level.

Once you have the table/column combinations you need to create a script called from xu2 that contains the following insert statement;

INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, product_version, ref_lookup_column_name, created_by, creation_date, last_updated_by, last_update_date)
VALUES
  ('**1**' -- view_label
  ,**4** -- query_position
  ,'**2**' -- column_label
  ,'**2**' -- table_alias
  ,'**2**' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'ATTR' -- column_type
  ,'Descriptive flexfield' -- description
  ,'N' -- group_by_flag
  ,'%' -- product_version
  ,'**5**' -- ref_lookup_column_name
  ,'**3**' -- created_by
  ,to_date('07-SEP-2011') -- creation_date
  ,'**3**' -- last_updated_by
  ,to_date('07-SEP-2011')) -- last_update_date
;

**1** = The view label, from n_view_templates, i.e. GME_Batch_Headers
**2** = The table alias, from n_view_table_templates, that you identified as being used to store the flexfield
**3** = Your details!
**4** = The query position in use in the target view. Look in n_view_column_templates to get the active values. This will usually only be one value, if not you will need to replicate the insert statement for each query position.
**5** = The columns where the flex field is stored followed by a percentage (usually ATTRIBUTE%)

NOTE: Product version might also have to change if you have two environments you regenerate into (i.e. 11.5.1.0 and 12.1) when the table aliases between the versions are different.

If you follow the standard script template for xu2 then the @max_col_position variable will already be populated.
Post a Comment