Add DFF Handling to an Upload Copy link

Adds one descriptive flexfield to an existing upload the standard Blitz Report way. Users see and enter display values in Excel; the download converts stored values to display values, and the upload procedure validates the whole DFF with the Oracle flexfield engine and converts it back to stored ids. A generator script writes every artefact from the flexfield's facts; the steps below say where each one goes. The upload-development rules, section E, are the checklist behind it.

Inputs

• The upload: its XML (exported from the Blitz Report setup form, Tools > Export) and the PL/SQL package of its upload procedure.
• The flexfield, by name or by the table that carries it, and a short column prefix for the entity (subinv, loc, line).
• A development instance to deploy and test on, as described in blitz-environment.md.

Step 0 - Check the Blitz Report build

Run the last query of references/flexfield_facts.sql. It must return P_COLUMN_PREFIX. Otherwise the build predates 18 September 2026, lacks xxen_upload.parameter_exists or p_column_prefix, and validates DFFs with contexts incorrectly. Stop and ask for an upgrade.

Step 1 - Collect the flexfield facts

Run queries 1 to 3 of references/flexfield_facts.sql:

Step 2 - Generate the artefacts

python ${CLAUDE_PLUGIN_ROOT}/skills/add-dff-to-upload/scripts/gen_dff.py \
  --app-id 401 --app-short-name INV --flexfield MTL_SECONDARY_INVENTORIES \
  --prefix subinv --count 15 --alias msi \
  --guid <upload GUID> --rule-name "XX Subinventory DFF Attributes" \
  --first-column <COLUMN_NUMBER of UPLOAD_ROW> --first-sequence <last upload parameter's DISPLAY_SEQUENCE + 1> \
  --template-keys ORGANIZATION_CODE,SUBINVENTORY \
  --package <upload package> --procedure <upload procedure> \
  --key-params p_organization_code,p_subinventory \
  --cursor-from "mtl_parameters mp,mtl_secondary_inventories msi" \
  --cursor-where "mp.organization_code=p_organization_code and mp.organization_id=msi.organization_id and msi.secondary_inventory_name=p_subinventory" \
  --out <folder>

• --first-column: the DFF columns go before UPLOAD_ROW, or before the column you name instead. --first-sequence: the procedure's new parameters follow its existing ones.
• --template-keys: the upload column names that identify the record, as in the upload XML.
• --alias must be the alias of the flexfield's own table (Query 1 application_table_name) in the main SQL, not of a view: its rowid resolves dependent value sets.
• --param-prefix / --context-param name the procedure parameters (default p_attribute, p_attribute_category); give each DFF its own when an upload has several.
• --dry-run writes a test build that reports the values the API would receive instead of calling it. Run the tests with it first.
• Aliases must stay within 30 bytes for databases before 12.2; the script warns.

Output: main_sql_columns.sql, success_sql_columns.sql, upload_columns.xml, upload_parameters.xml, translation_rule.xml, template.xml, <package>.pks, <package>.pkb.

Step 3 - Upload definition

1. Main SQL: insert main_sql_columns.sql after the entity's columns, before upload_row.
2. Upload columns: insert the upload_columns.xml rows and renumber the COLUMN_NUMBER of the columns after them, which must follow the select-list order.
   • Context column: LOV custom over fnd_descr_flex_contexts_vl, returning context names. Keep it when the DFF has only global segments: its list stays empty until a context is defined, as in the shipped uploads.
   • Attribute columns: LOV custom over xxen_util.dff_attribute_lov, with all siblings passed as :$column$, the value~description split, Validate From List Y, and a select comment listing the segment per context in the shipped format (Context) Attribute1: (Global) <prompt>, (<context>) <prompt>.
   • Keep the generated LOV text as it is (named arguments, lower case, literals at the start): the framework reads those literals from the first 1,000 characters to give Excel per-context date and number checks.
3. Upload parameters: insert the upload_parameters.xml rows in the procedure's parameter order and renumber DISPLAY_SEQUENCE after them.
4. Success SQL: add success_sql_columns.sql at the same position as in the main SQL. The tables sit in an inline view x that selects rowid row_id, because pre-12c databases allow an outer-joined table to reference only one other table:

   from
   <data view> xu,
   (
   select
   mp.organization_code,
   msi.rowid row_id,
   msi.*
   from
   mtl_parameters mp,
   mtl_secondary_inventories msi
   where
   mp.organization_id=msi.organization_id
   ) x
   where
   xu.organization_code=x.organization_code(+) and
   xu.subinventory=x.secondary_inventory_name(+) and
   xu.status_code_=xxen_upload.status_success

   The data view name is shown on the setup form's Upload Results tab.

5. Templates:
   • Keep the DFF columns out of the default template. An upload without one shows every column: add a default template with the existing columns.
   • Insert the template.xml row into the upload XML's <TEMPLATES>. It holds the key columns and all DFF columns, as the shipped uploads do, Upload Mode Update, and Site sharing.
6. Translation rule: import translation_rule.xml after the upload, as its own file. A rule whose upload does not exist yet is skipped, and rules import before reports in a combined file. It renames the attribute headers to the segment prompts. Developer access can also maintain it under Tools > Dynamic Column Translations.

Import the XML through the setup form (Tools > Import) or bin/import_xml. Editing an upload needs System access.

Step 4 - Upload procedure

Add the generated parameters (default null) to the procedure in spec and body. Then merge the generated declarations, helper functions and DFF block into it:

• Helper functions:
  • modified(p): parameter_exists plus value_modified. A hand-built csv/xlsx counts every parameter as modified, also those its template omits. Test the entity's other columns with it too: xxen_upload.new_value alone clears a column the csv template omits.
  • dff_modified: true on any DFF change.
  • segment_enabled: whether an enabled segment uses a column.
  • merged: builds the value of each cell:
  • a changed cell takes the uploaded value;
  • an untouched cell takes the stored value in display form;
  • a column without an enabled segment keeps its stored value unconverted;
  • after a context change, the old context's untouched segments are cleared, as in the Oracle form.
• Read the stored row with its rowid into l_stored, and initialise l_dff:=l_stored. The generated code treats a key without a stored row as a create; in an update-only upload, keep the procedure's own not-found error ahead of the DFF block.
• Merge and validate only on create or when dff_modified: merge, then validate_dff_attributes from 'V' to 'I'. On error, set message_ and raise.
• Detect a change by comparing the converted ids with l_stored, and report "No change." when nothing changed.
• Rename consistently: every parameter name literal (modified('p_attribute5'), the first argument of each merged call, the context parameter) must match the declared parameters, or edits are dropped silently.
• API call: replace xx_my_api.create_or_update with the entity's API and check its return status. A cleared cell is null in the record. How to pass it:

Never convert the stored record with validate_dff_attributes('I','V'): the conversion is all or nothing, so one invalid stored segment blocks every row.

Special cases

• Reference-field context (override N, default_context_field_name set): give the context column a DEFAULT_VALUE that derives the context name from the reference column, as the FND Lookup Upload does for FND_COMMON_LOOKUPS.
• Context set by the Oracle form itself, with neither override nor reference field (MTL_EAM_ASSET_ATTR_VALUES): in 'V' mode only global segments are validated, and context segments come back as typed. Convert each segment with xxen_util.dff_attribute_value_to_id and validate in 'I' mode. That function converts only segments with a list; convert dates and numbers with fnd_date/fnd_number yourself.
• Non-displayed segments (--hidden): 'V' mode returns them empty, so the generated code copies their stored values back.
• Value sets joining fnd_sessions (common in HR): call hr_integration_utils.add_or_update_session(<effective date>) at the start of the procedure.
• Date segments:
  • DFF dates travel as text in the Blitz Report XLSX Date Format profile, or else the ICX date mask, but validation reads them in the ICX mask. Keep the two equal.
  • Set Blitz Report XLSX Use Long Date Format = Yes when a date can fall outside 1950-2049; otherwise even untouched dates change century.
• Several DFFs in one upload: one prefix, parameter set and translation rule per DFF. Generate each with its own --prefix, --param-prefix and --context-param. Merge the helper functions under per-DFF names, or parametrise them with flexfield, record, rowid and context flag.

Step 5 - Deploy and test

1. Compile the package with --dry-run code, import the upload XML, then the rule. Check that the attribute headers show the prompts.
2. Download with the Descriptive Flexfield template, edit the rows, upload, and check the result workbook. Excel accepts only list values and valid dates, so write the error rows into the file outside Excel (cells as shared strings, Modified Columns listing the changed column positions). A csv maps by position: Action, Status, Message, then the template's columns in template order.

3. Replace the dry-run block with the API call, then repeat the tests and check the table.

Troubleshooting

Reference

• xxen_util.display_flexfield_context(app_id, flexfield, context_code): returns the context name.
• xxen_util.display_flexfield_value(app_id, flexfield, context_code, column_name, rowid, value): returns the display value.
• xxen_util.dff_attribute_lov(p_application_id, p_descriptive_flexfield_name, p_context_name, p_column_name, p_lov_delimter default '~', p_attribute1..30): pipelined, one value~description row per value. Call it with named notation.
• xxen_fnd_upload.validate_dff_attributes(p_appl_sn, p_flexfield_name, p_values_or_ids_in, p_values_or_ids_out, p_column_prefix, px_attribute_rec, x_error_message): on error the record is unchanged and the message is set.
• xxen_upload.value_modified(p) / xxen_upload.parameter_exists(p): parameter names in lower case, as declared.
• Shipped uploads to compare: INV Manufacturer Upload (one DFF), AR Customer Upload (five DFFs, one rule with a case on the flexfield name), FND Lookup Upload (reference-field context).