---
name: report-tester
description: Test a Blitz Report by running the original Oracle standard report and the Blitz version with the same parameters, then comparing output. Use after converting an RDF or BI Publisher report to Blitz.
---

# Report Tester Skill

Validates a converted Blitz Report by running both the original Oracle standard report and the new Blitz Report with identical parameters, then comparing the output. This catches SQL errors, missing columns, wrong joins, and data discrepancies.

## When to Use

- After converting an Oracle Report (.rdf) or BI Publisher report to Blitz
- When user asks to test or validate a converted report
- As the final quality gate before delivering a report conversion

## Arguments

$ARGUMENTS should contain the Blitz Report name (e.g., "FA Tax Reserve Ledger") and optionally the original concurrent program name.

## Phase 1: Find Test Data

The biggest challenge is finding parameter combinations that produce output. Many Vision demo reports return zero rows with default parameters.

### 1.1 Identify the Original Concurrent Program

```sql
select
fcp.concurrent_program_name,
fcp.user_concurrent_program_name,
fe.execution_file_name,
fa.application_short_name
from
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_application fa
where
fcp.executable_id=fe.executable_id and
fcp.executable_application_id=fe.application_id and
fcp.application_id=fa.application_id and
fcp.user_concurrent_program_name like '%'||:keyword||'%'
```

### 1.2 Find Previous Successful Runs

Check if the report has been run before with data-producing parameters:

```sql
-- Find previous runs that produced output
select
fcr.request_id,
fcr.actual_start_date,
fcr.phase_code,
fcr.status_code,
fcr.argument_text,
fcr.outfile_name
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where
fcr.concurrent_program_id=fcp.concurrent_program_id and
fcp.concurrent_program_name=:program_name and
fcr.phase_code='C' and
fcr.status_code='C'
order by
fcr.request_id desc
fetch first 10 rows only
```

If previous runs exist, reuse their parameter values.

### 1.3 Find Valid Parameter Values

When no previous runs exist, query the data to find values that produce rows:

```sql
-- Example: find books with assets (for FA reports)
select fb.book_type_code, count(*)
from fa_books fb
where fb.transaction_header_id_out is null
group by fb.book_type_code
order by count(*) desc;

-- Example: find periods with transactions (for GL reports)
select gjh.period_name, count(*)
from gl_je_headers gjh
where gjh.ledger_id=1
group by gjh.period_name
order by count(*) desc;

-- Example: find operating units with invoices (for AP reports)
select haouv.name, count(*)
from ap_invoices_all aia, hr_all_organization_units_vl haouv
where aia.org_id=haouv.organization_id
group by haouv.name
order by count(*) desc;
```

### 1.4 Map Concurrent Program Parameters to Blitz Parameters

```sql
-- Get concurrent program parameter definitions
select
fdfcuv.column_seq_num,
fdfcuv.end_user_column_name,
fdfcuv.form_left_prompt,
fdfcuv.default_value,
fdfcuv.required_flag,
ffvs.flex_value_set_name
from
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs
where
fdfcuv.application_id=:app_id and
fdfcuv.descriptive_flexfield_name='$SRS$.'||:program_name and
fdfcuv.descriptive_flex_context_code='Global Data Elements' and
fdfcuv.flex_value_set_id=ffvs.flex_value_set_id(+)
order by
fdfcuv.column_seq_num
```

Match each concurrent program parameter to the corresponding Blitz Report parameter by name or prompt.

## Phase 2: Run the Oracle Standard Report

### 2.1 Submit via fnd_request.submit_request

```sql
declare
l_request_id number;
begin
fnd_global.apps_initialize(<user_id>, :resp_id, :app_id); --your own EBS user: select fu.user_id from fnd_user fu where fu.user_name='<YOUR_USER>'
mo_global.init('S');

l_request_id:=fnd_request.submit_request(
:app_short_name,    -- e.g., 'OFA', 'SQLAP', 'AR'
:program_name,      -- e.g., 'FADTXA'
null,               -- description
null,               -- start_time
false,              -- sub_request
:param1, :param2, :param3, :param4, :param5,
:param6, :param7, :param8, :param9, :param10,
chr(0)              -- end of params marker
);
commit;
dbms_output.put_line('request_id: '||l_request_id);
end;
```

**Finding the right responsibility:**
```sql
-- Find a responsibility that can run this program
select distinct
fr.responsibility_key,
fr.responsibility_id,
fa.application_id
from
fnd_responsibility fr,
fnd_application fa,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs fcp
where
fr.request_group_id=frg.request_group_id and
fr.application_id=fa.application_id and
frg.request_group_id=frgu.request_group_id and
fcp.concurrent_program_name=:program_name and
(frgu.unit_application_id=fcp.application_id and
 frgu.request_unit_id=fcp.concurrent_program_id and
 frgu.request_unit_type='P'
 or
 frgu.unit_application_id=fcp.application_id and
 frgu.request_unit_type='A')
fetch first 5 rows only
```

### 2.2 Wait for Completion

```sql
-- Check request status
select
fcr.phase_code, fcr.status_code,
fcr.completion_text,
fcr.outfile_name, fcr.logfile_name
from
fnd_concurrent_requests fcr
where
fcr.request_id=:request_id
```

Wait until `phase_code='C'` (Completed). If `status_code<>'C'` (not Normal), check the log file for errors.

### 2.3 Retrieve Output

```bash
# Output files are typically in:
# $APPLCSF/out/
# or the path from fcr.outfile_name

# Copy output to /tmp for analysis
cp /path/to/o<request_id>.out /tmp/oracle_report_output.txt

# For PDF/Excel output, may need to check fnd_lobs
```

```sql
-- Check for output in fnd_lobs (some reports store output here)
select fl.file_id, fl.file_name, dbms_lob.getlength(fl.file_data) file_size
from fnd_lobs fl
where fl.file_name like '%'||:request_id||'%'
order by fl.file_id desc;
```

## Phase 3: Run the Blitz Report

### 3.1 Submit via xxen_api

```sql
declare
l_run_id number;
l_request_id number;
l_message varchar2(4000);
begin
fnd_global.apps_initialize(<user_id>, 50553, 101);
mo_global.init('S');

l_run_id:=xxen_api.create_run_id(:blitz_report_id);

-- Set parameters to match the Oracle report run
xxen_api.set_parameter_value_(
p_parameter_name=>'Book',
p_value=>:param_book,
p_report_name=>:blitz_report_name,
p_run_id=>l_run_id
);
-- Repeat for each parameter

l_message:=xxen_api.report_submit_concurrent(:blitz_report_id, l_run_id, x_request_id=>l_request_id);
commit;
dbms_output.put_line('request_id: '||l_request_id);
end;
```

### 3.2 Retrieve Blitz Output

```sql
-- Find the output XLSM in fnd_lobs
select fl.file_id, fl.file_name, dbms_lob.getlength(fl.file_data) file_size
from fnd_lobs fl, fnd_concurrent_requests fcr
where fcr.request_id=:blitz_request_id and
fl.file_id=fcr.output_file_id;
```

The Blitz output is an XLSM file. To compare, extract the data sheet.

## Phase 4: Compare Outputs

### 4.1 Row Count Comparison

The simplest check — do both reports return the same number of rows?

```sql
-- Quick Blitz row count (from the SQL directly)
select count(*) from (<blitz_report_sql_with_params>);

-- Compare with Oracle report output line count
-- (parse the .out file or count data rows in XLSM)
```

### 4.2 Data Comparison Strategy

Since Oracle Reports output is typically formatted text/PDF and Blitz output is XLSM, exact byte comparison isn't possible. Instead:

**Option A: SQL-level comparison** (preferred, most reliable)

Run the Blitz SQL directly and compare against the Oracle report's source query:

```sql
-- Run Blitz SQL
select count(*), sum(amount), min(date_col), max(date_col)
from (<blitz_report_sql>)
where <parameter_conditions>;

-- Run original RDF SQL (from the RAD)
select count(*), sum(amount), min(date_col), max(date_col)
from (<original_rdf_sql>)
where <parameter_conditions>;
```

Compare: row counts, key aggregates (sums, counts), min/max values.

Option A only compares what the Blitz SQL and the original's query both contain. Formula columns, placeholders, package loop code that fills cursor literals, group filters and format triggers are outside that query. A conversion that dropped them passes Option A with identical counts and sums. Check those values with the formula port of `migrate-custom-reports/references/value-fidelity.md`, or against the program's own output.

**Option B: Column-by-column comparison**

For each column in the Blitz Report, verify it matches the original:
1. Same column name/alias
2. Same data on every row: join the two row sets on the key and count differences per column (`sum(decode(o.col,n.col,0,1))`); a spot check of a few rows misses the branch or edge case that differs
3. Same aggregates (totals, subtotals)

**Option C: Full data comparison** (most thorough)

Export both datasets to a temp table and do a MINUS:

```sql
-- Create temp tables with both outputs
create global temporary table xx_oracle_output as <original_sql>;
create global temporary table xx_blitz_output as <blitz_sql>;

-- Find rows in Oracle but not in Blitz (missing rows)
select * from xx_oracle_output
minus
select * from xx_blitz_output;

-- Find rows in Blitz but not in Oracle (extra rows)
select * from xx_blitz_output
minus
select * from xx_oracle_output;
```

### 4.3 Common Discrepancies and Causes

| Discrepancy | Common Cause | Fix |
|-------------|-------------|-----|
| Extra rows in Blitz | Missing WHERE condition from BeforeReport lexical | Add condition to Blitz SQL |
| Missing rows in Blitz | Extra filter from Blitz parameter | Check parameter defaults |
| Different amounts | Formula function not converted correctly | Review CF_ formulas |
| Different date format | Format mask mismatch | Check to_char format |
| Missing columns | Group summary not included | Add analytic function |
| Different row order | ORDER BY mismatch | Verify sort columns |
| NULL vs blank | NVL in original, missing in Blitz | Add NVL |
| Currency formatting | SRW FORMAT_CURRENCY not replicated | Use to_char with format |

### 4.4 Flexfield Comparison

If the original report uses `FND FLEXSQL` / `FND FLEXIDVAL` user exits:
- Original: `SRW.USER_EXIT('FND FLEXSQL CODE="GL#" ...)` → flexfield segment values
- Blitz: `gcck.concatenated_segments` or `xxen_util.segments()` or individual segment columns

Verify the same segments appear in the same format.

## Phase 5: Report Results

### Test Report Format

```markdown
# Report Comparison Results
**Blitz Report:** <report name>
**Oracle Report:** <concurrent program name>
**Date:** <date>

## Parameters Used
| Parameter | Value |
|-----------|-------|
| Book | OPS CORP |
| Period | Jun-08 |

## Comparison Summary
| Check | Result | Details |
|-------|--------|---------|
| Oracle report runs successfully | Pass/Fail | request_id: NNN |
| Blitz report runs successfully | Pass/Fail | request_id: NNN |
| Row count matches | Pass/Fail | Oracle: N, Blitz: N |
| Key aggregates match | Pass/Fail | SUM(amount): Oracle=X, Blitz=X |
| All columns present | Pass/Fail | Missing: [list] |
| Column order matches | Pass/Fail | Differences: [list] |
| Data spot-check | Pass/Fail | Checked rows 1-5 and last 5 |

## Discrepancies Found
### [1] Missing condition in WHERE clause
- **Oracle SQL:** `AND fb.period_counter <= :P_END_PERIOD`
- **Blitz SQL:** condition missing
- **Impact:** Extra rows returned
- **Fix:** Add period filter

## Passed Checks
- Row count matches (both return 47 rows)
- SUM(cost) matches: 1,234,567.89
- ...
```

## Tips

1. **Start with simple parameters** — use a single book, one period, one org to minimize data volume
2. **Run Oracle report first** — if it returns 0 rows, the parameters are wrong (no point running Blitz)
3. **Check BeforeReport trigger** — it often builds dynamic WHERE clauses that the Blitz conversion must replicate
4. **Formula functions** — some CF_ formulas do complex calculations that may need PL/SQL functions in Blitz
5. **Vision data periods** — FA: Jun-08, GL/AP/AR: Dec-12, Manufacturing: Dec-2012
6. **Clean up temp tables** — drop any global temporary tables created for comparison
7. **Save test parameters** — document which parameters worked for future regression testing