Dynamically Selecting DFF Columns in Reports

You should use the `&lexical` anchor, placing it directly within your `SELECT` clause (e.g., `select &flexfield_columns from mtl_system_items_vl msiv`) . The parameter associated with this anchor must contain SQL that calls the utility function `xxen_util.dff_columns` . This function automatically queries the `fnd_descr_flex_col_usage_vl` dictionary tables to generate the required comma-separated list of DFF columns and their respective column headers (prompts) , .

The `xxen_util.dff_columns` function requires the `p_table_name` (e.g., ‘mtl_system_items_b’) and the `p_table_alias` (e.g., ‘msiv’) , . By default, if you don’t specify a DFF context code, it generates columns from all contexts, starting with ‘Global Data Elements’ , . Optionally, you can restrict the output by providing the `p_descr_flex_context_code` to show only DFFs for a specific context .

You should place the `&lexical` anchor within the `SELECT` clause, usually followed or preceded by a comma, to correctly delimit the dynamically generated columns from your fixed column list . For instance, `select fu.user_name, fu.email_address, &columns frv.responsibility_name` allows the lexical anchor to inject columns like `fu.user_name, fu.email_address,` followed by your static column . The utility function ensures that the resulting SQL text is formatted correctly for insertion, often including quotation marks for column aliases (e.g., `msiv.attribute1 “Late Demands Penalty”`) .

The DFF utility automatically uses the DFF prompt text as the column header . While you cannot directly edit the output of the utility within the parameter definition, you can use the Column Translations feature in Blitz Report Setup to define multi-language headers . If you need a column-specific number format or translation, you should check ‘Columns’ during export to include this data for migration .

No, the standard SQL entry field through the Oracle Forms interface is limited to 32767 characters . If your extraction SQL, even with the embedded lexical logic, exceeds this limit, you must utilize the ‘Upload Large SQL’ functionality, which can be accessed from the Tools menu in the Blitz Report Setup window . You should ensure the SQL file is encoded correctly, particularly if non-ANSI characters are present.