Descriptive Flexfields Matching a Pattern

I was looking at descriptive flexfields and it forced me to look at my Noetix Certification Course notes. Without looking at the view, fnd_descr_flex_view, and constraining it to the Oracle Application table which contains the descriptive flexfields (e.g. mtl_lot_numbers), some of the methods associated with exposing descriptive flexfields do not make sense (or at least are much easier to understand and implement).

For instance, if I perform the query:

SELECT
fdfv.*
FROM
fnd_descr_flex_view fdfv
WHERE 1=1
AND fdfv.application_table_name = 'MTL_LOT_NUMBERS'

I see all of the flexfields that are associated with the INV. MTL_LOT_NUMBERS table. Here they are:

Billet Length (IN) C_ATTRIBUTE1
Deviation Number C_ATTRIBUTE10
Customer Name C_ATTRIBUTE11
Conversion Indicator C_ATTRIBUTE18
Input PC Grandparent C_ATTRIBUTE19
Input Child Lot C_ATTRIBUTE2
Input GC Grandparent C_ATTRIBUTE20
Billet Heat # C_ATTRIBUTE3
Billet Cross Section C_ATTRIBUTE4
Disposition Code C_ATTRIBUTE5
PBL Supplier Parent Lot C_ATTRIBUTE6
Actual Billet Weight (LBS) C_ATTRIBUTE7
Input Parent Lot Number C_ATTRIBUTE8
Customer Lot Number C_ATTRIBUTE9

Consequently, if I look at the application column name in the result set, I see all of the descriptive flexfields application column names associated with this table which have been frozen and enabled during the most recent view regeneration.

In my most recent modification requirement, I wanted to expose all DFFs that match the pattern, C_ATTRIBUTE%, consequently I used the matching technique. Here is the DML for this:

INSERT INTO n_view_column_templates
(view_label
, query_position
, column_label
, table_alias
, column_expression
, column_position
, column_type
, description
, group_by_flag
, gen_search_by_col_flag
, profile_option
, product_version
,ref_application_label
,ref_table_name
,ref_lookup_column_name
,ref_description_column_name
,ref_lookup_type
,created_by
,creation_date
,last_updated_by
,last_update_date
)
VALUES
('INV_Lot_Status_History' --view_label
,1 --query_position number
,'LOTS' --column_label
,'LOTS' --table_alias
,'LOTS' --column_expression
,41 --column_position
,'ATTR' --column_type
,'Lot number descriptive flexfield (MTL_Lot_Numbers).' --description
,'N' --group_by_flag
,'N' --gen_search_by_col_flag
, NULL --profile_option
,'*' --product_version
,NULL -- ref_application_label
,NULL --ref_table_name
,'C_ATTRIBUTE%' --ref_lookup_column_name
,NULL --ref_description_column_name
,NULL -- ref_lookup_type
,'flinstonef' -- created_by
, SYSDATE --creation_date
,'flinstonef' --last_updated_by
, SYSDATE --last_update_date
);

Commit;

Instead of running a full blown regeneration to test my DML script, often times I just go to my development instance and start SQL Developer and run this script using my Noetix admin database account. If it throws an exception, I know that something is not correct (this sure beats wasting time running the Noetix View Administrator).
With this method of testing my DML script, if it does modify the appropriate table (e.g. n_view_column_templates), then when I run the regeneration, my modification will be blown away (this table will be dropped and re-created during the regeneration).

All in all, this was not too hard to add some descriptive flexfields.