Digression: Oracle Apps Descriptive Flexfield Query with Assigned Flexfield Value Sets

Much has happened since my last entry.
-completed a 6.0.1 to 6.4.1 Noetix View migration
-now have global views for everything in our Oracle Apps stack
-in UAT for a 6.4.1 to 6.5.1 Noetix View migration (migrating from 12.1.3 to 12.2.5 Oracle Apps concurrently)
-purchased Noetix Workbench

Many lessons have been learned.  Many tickets submitted to Magnitude. With the tickets to Magnitude, I had an opportunity to work with various levels of support (front line staff who were very helpful to senior engineers).

I will have to write about these details at some point.

Today, I have a reporting requirement pertaining to descriptive flexfields where the compiled usage is a date with a certain mask (no value set assigned though). 

Usually, I have created xu5 scripts to cast these dates as dates (though it is important to know the mask used).

Anyways, I did not have a DFF query with flex value sets associated, nor could I find one so here is my ANSI special query (I practice ANSI standard for practice and fun):

SELECT fcu.*,
  fcv.*,
 ffvs.*
FROM applsys.fnd_descr_flex_contexts fcv
JOIN applsys.fnd_application_tl A
ON fcv.application_id = A.application_id
AND A.language        = 'US'
JOIN applsys.fnd_descr_flex_column_usages fcu
ON fcv.application_id                 = fcu.application_id
AND fcv.descriptive_flexfield_name    = fcu.descriptive_flexfield_name
AND fcv.descriptive_flex_context_code = fcu.descriptive_flex_context_code
JOIN applsys.fnd_descr_flex_col_usage_tl fcut
ON fcu.application_id                 = fcut.application_id
AND fcu.descriptive_flexfield_name    = fcut.descriptive_flexfield_name
AND fcu.descriptive_flex_context_code = fcut.descriptive_flex_context_code
AND fcu.application_column_name       = fcut.application_column_name
AND fcut.language                     = 'US'
LEFT OUTER JOIN applsys.fnd_flex_value_sets ffvs
ON ffvs.flex_value_set_id = fcu.flex_value_set_id
WHERE A.application_name LIKE :application_name
  ||'%'
AND fcu.descriptive_flexfield_name LIKE '%'
  || :dff_name_eg_OE_LINE_ATTRIBUTES
  || '%'
AND fcut.form_left_prompt LIKE '%'
  || :dff_form_left_prompt
  ||'%'
ORDER BY fcut.descriptive_flexfield_name,
  fcut.application_column_name ;