Upload Validator Copy link

Reviews a Blitz Upload by comparing it against the Oracle EBS standard form and runs automated end-to-end tests. Acts as the Validator in the multi-agent upload development workflow.

When to Use

• After creating or significantly modifying an upload (XML + PL/SQL)
• When user asks to review, validate, or test an upload
• As the final quality gate before delivering an upload

Arguments

$ARGUMENTS should contain the upload name or XML file path (e.g., "FA Asset Upload" or "content/r12/uploads/FA_Asset_Upload.xml").

Inputs Required

1. Upload XML file — the Blitz upload XML definition
2. Upload PL/SQL package — the processing package (spec + body)
3. Oracle form name — the standard EBS form this upload replicates (e.g., FAXASSET for FA, APXINWKB for AP)

If the Oracle form name is not provided, infer it from the upload's module and purpose.

Phase 1: Oracle Form Analysis

Use the oracle-forms-analyzer skill to analyze the standard form. Request the full Form Analysis Document: field inventory, LOV map, defaulting map, validation map, KFF map, and API details.

Save the output for comparison in Phase 2.

Phase 2: Upload Review Checklist

Compare the upload XML + PL/SQL against the Form Analysis Document. Check each category and report findings.

Severity Levels

Issue ID Format

Each issue gets a structured ID: [CAT-n-NNN] where:

• CAT = category number (1-10)
• n = severity (B=blocker, M=major, m=minor, S=suggestion)
• NNN = sequential number within category

Example: [CAT-4-B-001] = Category 4 (LOVs), Blocker, first issue.

2.1 Missing Fields

For every field visible/editable in the Oracle form:

• Check if a corresponding column exists in the upload
• Exceptions: framework columns (Action, Status, Message, Modified Columns), display-only computed fields, WHO columns

Common misses:

• DFF attributes (ATTRIBUTE_CATEGORY + ATTRIBUTE1..15)
• KFF individual segments (form allows segment-by-segment entry)
• Alias fields for flexfields
• Comments/notes fields

2.2 Field Naming (CRITICAL — Use Form Prompts)

For each upload column, verify the alias matches the form Prompt attribute from the FAD (section 4.1), NOT the internal database column name:

• Upload column aliases become Excel headers — must match what Oracle form users see
• The FAD field inventory includes the Prompt for each field — use it as the upload column alias
• Related fields should use consistent prefixes (e.g., "Expense Account", "Expense Segment1")

Common issues:

• Internal DB column names instead of form Prompts (e.g., "RECEIPT_METHOD_ID" instead of "Receipt Method")
• Form Prompt differs from DB column (e.g., form says "Amount" but DB column is "RECEIPT_AMOUNT")
• Missing module prefix when name is ambiguous
• Inconsistent naming across related fields

How to check:

1. For each upload column alias, find the corresponding field in FAD section 4.1
2. Compare upload alias against the FAD's Prompt column
3. Flag any mismatch as MAJOR — users expect the same labels they see in the Oracle form

2.3 Field Order (CRITICAL — Must Match Form Visual Layout)

Compare upload column order against the form's visual field order from FAD section 4.2. This is the definitive order — do NOT guess or use generic grouping rules.

Validation steps:

1. Get the ordered field list from FAD section 4.2 (extracted from .fmb canvas coordinates: YPosition → XPosition)
2. Map each FAD field to its corresponding upload column
3. Verify the upload columns appear in the SAME order as the FAD visual field list
4. Framework columns (action, status, message_, modifiedcolumns) are always first — they are exempt from ordering

Severity:

• Fields significantly out of order (e.g., Receipt Method at position 17 when it's the first field in the form) = MAJOR
• Minor reordering within a logical group = MINOR

Common issues:

• Builder used generic grouping rules instead of actual form layout
• Fields from different form regions/tab pages mixed together
• Header fields appearing after detail fields

Fallback (only if FAD section 4.2 is unavailable):

• Key/identifier fields first
• Grouping: identifiers → classification → amounts → dates → methods → accounts → locations → DFFs
• But this is a last resort — always prefer the actual form visual order

2.4 List of Values (LOVs)

For each field with a LOV in the form:

• Verify upload column has matching LOV
• Compare LOV query: same values? same filters? (enabled, date-active, org-specific)
• Check dependent filtering matches form behavior
• Check VALIDATE_FROM_LIST_DSP matches form's LOV validation
• Check FILTER_BEFORE_DISPLAY_DSP for large LOVs

2.4a LOV Performance (CRITICAL)

Scan every LOV_QUERY_DSP for is null or patterns on parent column dependencies:

-- BAD (prevents index usage — full table scan every time):
(:$column$.customer_name is null or hp.party_name=:$column$.customer_name)
-- GOOD (index-friendly — requires parent field to be entered first):
hp.party_name=:$column$.customer_name

When to flag:

• If the FAD's Data Entry Workflow (section 4.2b) shows the form enforces entering the parent field before the child LOV is available, then the upload LOV MUST use a required join (no is null or). Severity: MAJOR.
• If the form allows the child LOV to be opened without the parent, is null or is acceptable.

How to check:

1. Find all is null or patterns in LOV_QUERY_DSP across the upload XML
2. For each, check the FAD's Data Entry Workflow — does the form require the parent field first?
3. If yes → flag as MAJOR: "LOV uses is null or pattern but form enforces parent-first entry. This prevents index usage and causes poor performance on large tables."
4. Also check: if FILTER_BEFORE_DISPLAY_DSP is set on a LOV that uses is null or, the LOV is effectively broken — the filter popup won't help because the underlying query is already slow.

2.4b LOV Filtering vs Form Behavior

Compare each LOV's dependent filtering against the form's actual data entry workflow:

• If form requires Field A before allowing Field B's LOV → upload LOV for B must have a required join on A (no is null or)
• If form allows Field B's LOV without Field A → upload LOV may use is null or for optional filtering

Common issues:

• Missing date-active or enabled_flag filter
• Missing org/book-dependent filtering
• Wrong table (base table instead of _vl for translated values)
• fnd_lookup_values with language filter instead of fnd_lookup_values_vl with security_group_id=0
• VALIDATE_FROM_LIST=Y on free-text fields or N on validated fields
• Missing VALUE_TO_ID_QUERY when PL/SQL needs the ID
• is null or pattern on joins where form enforces parent-first entry (performance killer)
• FILTER_BEFORE_DISPLAY_DSP used as a workaround for slow LOV instead of fixing the query

2.5 Defaulting Logic

For each field with a default in the form:

• Check upload column has matching DEFAULT_VALUE
• Compare defaulting logic: same source data? same conditions?
• Check cross-field defaults have correct UPLOAD_DEPENDENCIES

Common issues:

• Missing date defaults (period end date, sysdate)
• Missing cross-field defaults (Category → Depreciation Method, Life)
• Hardcoded delimiters in segment concatenation (must query fnd_id_flex_structures)
• Hardcoded segment count (must query fnd_id_flex_segments)
• Circular two-way dependencies (only one direction in DEFAULT_VALUE; reverse in PL/SQL)
• DEFAULT_VALUE returns wrong column (id vs value)

2.6 Validation Logic

Compare validate function against form validation triggers:

• All mandatory fields checked
• All cross-field validations replicated
• Range checks, format checks, date range checks

Common issues:

• Returns on first error instead of concatenating all (must use nested message() procedure)
• Missing conditional validation (validate X only when Y='VALUE')
• Validating fields already LOV-validated (redundant)
• Missing date-in-open-period validation

2.7 KFF Handling

For KFF fields, verify:

• Alias column present (if form supports aliases)
• Concatenated column present
• Individual segment columns present (one per enabled segment)
• PL/SQL resolution is dynamic (queries flex structure, not hardcoded)

Correct pattern (from GL Journal Upload):

1. DEFAULT_VALUE: alias → concatenated → segments (one direction only)
2. PL/SQL: segments → concatenated → CCID (dynamic via fnd_id_flex_structures + fnd_id_flex_segments)
3. No hardcoded delimiters or segment counts

2.8 Upload Processing Logic

• Correct Oracle API used (same as form)
• Handles create and update (auto-detect, not relying on action_ column)
• Error handling: catches API errors, extracts FND message stack
• status/message set correctly on success and failure
• No unused parameters (every PL/SQL param has UPLOAD_PARAMETERS_ROW)
• Parameters typed correctly: Number for IDs from LOV id, Char for display values

2.9 XML Structure Checks

• COLUMN_NAME entries match SQL select columns
• UPLOAD_COLUMN_NUMBER sequential
• DATA_TYPE correct: always varchar2 for LOV columns
• UPLOAD_DEPENDENCIES auto-generated (never manually edited)
• PARAMETER_DEPENDENCIES match :$flex$. references
• ANCHORS declared for all anchors used
• All filter parameters use 1=1 anchor
• Upload Mode uses standard Blitz Upload Mode LOV
• No ORDER BY in main SQL or success SQL
• VERSION_COMMENTS present when SQL was modified

2.10 Success SQL and Error SQL

• Error SQL: Prefer default (framework auto-generated)
• Success SQL:
  • Upload view (xxen_{prefix}_{id}_u xu) in FROM clause
  • Column count matches framework's first UNION ALL branch
  • Joins to actual Oracle tables to confirm record creation
  • xu.status_code_='S' filter present
  • No ORDER BY

Phase 3: E2E Testing

3.1 Prerequisites

-- Find report_id
select xrv.report_id from xxen_reports_v xrv where xrv.report_name='Upload Name';
-- Find upload view name
select view_name from all_views where view_name like 'XXEN_%_U' order by view_name;
-- Check PL/SQL compiles clean
select uo.status from user_objects uo where uo.object_name='PACKAGE_NAME';

3.2 Template Download

declare
l_run_id number;
l_request_id number;
l_message varchar2(4000);
begin
fnd_global.apps_initialize(<user_id>,50553,101); --your own EBS user, not OPERATIONS (no ICX_SESSIONS -> ORA-06502): select fu.user_id from fnd_user fu where fu.user_name='<YOUR_USER>'
mo_global.init('S');
l_run_id:=xxen_api.create_run_id(<report_id>);
xxen_api.set_parameter_value_(p_parameter_name=>'Upload Mode', p_value=>'Create', p_report_name=>'<Upload Name>', p_run_id=>l_run_id);
-- Set other required parameters
l_message:=xxen_api.report_submit_concurrent(<report_id>, l_run_id, x_request_id=>l_request_id);
commit;
dbms_output.put_line('request_id: '||l_request_id);
end;

3.3 Test Scenarios

For all uploads:

• Create mode: new records with all required fields
• Create with optional fields: DFFs, notes, references
• Validation errors: missing required fields, invalid LOV values, bad dates
• Multiple rows: at least 3 records

For uploads with update mode:

• Update existing: download, modify a field, re-upload
• Mixed create + update: some new, some modified

For KFF uploads:

• Existing combination from LOV
• Via alias entry
• Via individual segments (PL/SQL resolves to CCID)
• New combination (if allowed)

For interface-based uploads:

• Post-procedure error detection
• Concurrent program completion

3.4 XLSM Manipulation

1. Extract XLSM (ZIP) to working directory
2. Map column positions from xl/worksheets/sheet1.xml headers
3. Add test data using shared string format (t="s" with index)
4. Key rules:
   • Column A = Action: "Create" (or "Update")
   • Column B = Status: "Valid" (display meaning, NOT code 'V')
   • Column C = Message: "Validation complete."
   • Dates = Excel serial numbers (base: 1899-12-30)
   • LOV columns = display values (framework translates to IDs)
5. Repack XLSM

3.5 Upload Submission

declare
l_file_id number;
l_request_id number;
l_message varchar2(4000);
begin
fnd_global.apps_initialize(<user_id>,50553,101);
mo_global.init('S');
insert into fnd_lobs (file_id, file_name, file_content_type, file_data, upload_date, program_name, language, file_format)
values (fnd_lobs_s.nextval, '<filename>.xlsm', 'application/vnd.ms-excel.sheet.macroEnabled.12',
  bfilename('TMP','<filename>.xlsm'), sysdate, 'FNDATTCH', 'US', 'binary')
returning file_id into l_file_id;
commit;
l_message:=xxen_upload.submit_upload_process(
p_report_name=>'<Upload Name>',
p_template_name=>null,
p_file_id=>l_file_id,
x_request_id=>l_request_id
);
commit;
dbms_output.put_line('request_id: '||l_request_id||' message: '||l_message);
end;

3.6 Result Verification

1. Wait for concurrent request to complete
2. Fetch output XLSM from fnd_lobs
3. Check: row count matches, Status=Success for valid rows, error messages descriptive and concatenated
4. Verify records in Oracle tables
5. Compare with form: records look same as if entered through Oracle form

3.7 Test Data Guidelines

• NEVER use made-up values — check LOVs, value sets, valid orgs
• Run "Blitz Upload Data" report for example data
• Vision dates: AR Dec-12, FA Jun-08, Manufacturing Dec-2012
• XLSM dates: numeric serial numbers (03-DEC-2012 = 41246)

Phase 4: Report Findings

Report Format

# Blitz Upload Validation Report
**Upload:** <report name>
**Form:** <Oracle form name>
**Date:** <date>
## Summary
| Category | Issues | Blockers | Majors | Minors | Suggestions |
|----------|--------|----------|--------|--------|-------------|
| 1. Missing Fields | n | n | n | n | n |
| 2. Field Naming | n | n | n | n | n |
| ... | | | | | |
| **TOTAL** | n | n | n | n | n |
## BLOCKER Issues (must fix before import)
### [CAT-1-B-001] Missing required field: Depreciation Method
- **Location:** UPLOAD_COLUMNS (missing)
- **Expected:** Column for depreciation method (FAD field #12, REQUIRED)
- **Actual:** Not present in upload
- **Fix:** Add column with LOV from fa_methods
## MAJOR Issues
### [CAT-4-M-001] LOV missing org filter
- **Location:** UPLOAD_COLUMNS > SUPPLIER_NAME > LOV_QUERY_DSP
- **Expected:** Filter by operating unit
- **Actual:** Returns all suppliers across orgs
- **Fix:** Add `and aps.org_id=:$column$.org_id` to WHERE clause
## MINOR Issues
...
## SUGGESTIONS
...
## Passed Checks
- Category 2: Field naming — all aliases consistent
- Category 3: Field order — matches form tab order
- Category 7: KFF handling — dynamic resolution, no hardcoded delimiters
- ...
## E2E Test Results
| Scenario | Result | Details |
|----------|--------|---------|
| Create with all required fields | Pass | Record created in fa_additions_b |
| Missing required field | Pass | Error: "Book Type Code is required." |
| ... | | |
## Recommended Test Scenarios
1. Create a new record with all mandatory fields — verify success
2. Create with missing mandatory field — verify error message
3. Update existing record — verify only changed fields updated
4. KFF: select via alias, verify segments populate
5. KFF: enter individual segments, verify CCID resolution
6. LOV dependent filtering (e.g., category → asset type)
7. <Module-specific tests from form analysis>

Issue Details

For each issue include:

• Issue ID: [CAT-n-X-NNN] format
• Location: XML element path or PL/SQL function
• Expected: What it should be (from Oracle form / FAD)
• Actual: What is in the upload
• Fix: Specific code/XML change

Relationship to Other Commands

This skill is the comprehensive validator. Lightweight shortcuts:

• /validate-upload-xml — XML structure checks only (Phase 2.9)
• /test-upload — E2E testing only (Phase 3)

Multi-Agent Context