Oracle Forms Analyzer Copy link

Extracts comprehensive field-level and form-level metadata from Oracle EBS entry forms — both Oracle Forms (.fmb/.fmx) and OAF web pages (HTML/self-service) — and their underlying database packages. The output is a structured Form Analysis Document used as input by the upload-builder and upload-validator.

When to Use

• Before building a new Blitz Upload — to understand what the form does
• When reviewing an existing upload — to compare against the form
• When user asks to analyze a specific Oracle form or OAF page

Arguments

$ARGUMENTS should contain the Oracle form name (e.g., "FAXASSET", "APXINWKB") or OAF page description (e.g., "iProcurement requisition", "iExpenses"), and optionally the EBS module context.

Form Type Detection

Determine whether the target is an Oracle Forms entry form or an OAF (self-service web) page:

If unsure, check both locations. Some modules have both (e.g., PO has Forms for buyers and OAF for requestors).

Common Form Names by Module

Oracle Forms (.fmb)

OAF Pages (Self-Service)

Phase 1: Locate and Convert Form Source Files

1.1 Find the Form File

The .fmb (Forms Module Binary) is a binary file — it cannot be read directly. It must first be converted to a readable format.

The source files are on the apps tier of the EBS instance ($AU_TOP/forms/US). Connect to it as described in blitz-environment.md, or ask the DBA to copy the files.

# Source the apps tier environment file first
# Forms source (.fmb) is in $AU_TOP/forms/US/
find $AU_TOP/forms/US -name "FORMNAME.fmb" 2>/dev/null
# Common locations by module:
# $AU_TOP/forms/US/FAXASSET.fmb (FA)
# $AU_TOP/forms/US/APXINWKB.fmb (AP)
# $AU_TOP/forms/US/ARXTWMAI.fmb (AR)

1.2 Convert .fmb to Readable XML

Preferred method: Use Oracle's frmf2xml.sh (Forms2XML) tool:

# Copy .fmb to /tmp (always work in /tmp to keep the server clean)
cp $AU_TOP/forms/US/FORMNAME.fmb /tmp/
cd /tmp
# Convert to XML (outputs FORMNAME.xml in current directory)
frmf2xml.sh FORMNAME.fmb

Known issue: frmf2xml.sh may fail with "Error initialising the Forms Jdapi: _jni_initialize: Failed to create new forms context" if the server has no X11 display. Workarounds:

1. Run with Xvfb if installed: Xvfb :99 & export DISPLAY=:99 && frmf2xml.sh FORMNAME.fmb
2. Run from a machine with Oracle Forms Builder installed (Windows/GUI)

Fallback method: If frmf2xml.sh fails, use strings to extract readable content from the binary:

# Extract all readable strings (PL/SQL triggers, SQL queries, field names)
strings /tmp/FORMNAME.fmb > /tmp/FORMNAME_strings.txt
# Search for specific content:
strings /tmp/FORMNAME.fmb | grep -i 'when-validate\|when-new-record\|pre-insert\|on-insert'
strings /tmp/FORMNAME.fmb | grep -i 'select.*from'
strings /tmp/FORMNAME.fmb | grep -i 'book_type_code\|asset_number\|category'

The strings output is unstructured but contains all trigger code, LOV SQL queries, field names, and PL/SQL logic embedded in the form. It's sufficient for analysis when the XML conversion is unavailable.

For PL/SQL libraries (.pll): Similar approach — .pll files are also binary. Use strings as a fallback or convert using the Forms Builder GUI.

Cleanup: Always remove temp files from /tmp when done.

1.3 Find Associated PL/SQL Libraries

Forms reference PL/SQL libraries (.pll) with shared logic:

ls $AU_TOP/resource/*.pll
ls $FA_TOP/resource/*.pll

Common: FAXOLSTD.pll, APPCORE.pll, CUSTOM.pll

1.4 Find the Underlying Database Package

The form calls a database package for DML. Find it by:

1. Check ON-INSERT, ON-UPDATE, ON-DELETE triggers for package.procedure calls
2. Common naming: form FAXASSET → FA_MASS_ADDITIONS_PKG, FA_ADDITION_PUB, etc.

-- Search for packages related to a module
select object_name, object_type from all_objects
where object_name like 'FA_%PKG' and object_type='PACKAGE'
order by object_name;
-- Search for API packages
select object_name from all_objects
where object_name like 'FA_%PUB' and object_type='PACKAGE'
order by object_name;

Phase 1-OAF: Locate and Read OAF Page Files

Use this phase instead of Phase 1 when the target is an OAF self-service page.

1-OAF.1 Find the Page Definition (PG.xml)

OAF page definitions are XML files in the JRAD metadata store (mds):

# Source the apps tier environment file first
# Page definitions are in $APPL_TOP/{module_short}/12.0.0/mds/
# Example: iProcurement checkout lines
find $APPL_TOP/icx/12.0.0/mds -name "*Checkout*PG.xml" 2>/dev/null
# Common module paths:
# $APPL_TOP/icx/12.0.0/mds/por/req/webui/     (iProcurement)
# $APPL_TOP/ap/12.0.0/mds/oie/webui/           (iExpenses)
# $APPL_TOP/pos/12.0.0/mds/supplier/webui/     (iSupplier)
# $APPL_TOP/per/12.0.0/mds/sshr/webui/         (Self-Service HR)
# If module is unknown, search broadly:
find $APPL_TOP/*/12.0.0/mds -name "*KEYWORD*PG.xml" 2>/dev/null

Page XML files are fully readable XML — no conversion needed (unlike .fmb files).

1-OAF.2 Find View Objects (VO.xml) and Application Modules (AM.xml)

These contain the SQL queries and data model:

# VOs and AMs are in $JAVA_TOP/oracle/apps/{module}/{submodule}/server/
# Example: iProcurement
ls $JAVA_TOP/oracle/apps/icx/por/req/server/*.xml
# Key files to look for:
# *VO.xml  — View Objects (contain SQL queries)
# *AM.xml  — Application Modules (VO hierarchy)
# *VOImpl.class — compiled Java (no source available)

1-OAF.3 Read and Analyze the Page XML

The PG.xml defines the complete UI layout. Key elements to extract:

<!-- Page-level: which AM backs this page -->
<oa:pageLayout amDefName="oracle.apps.icx.por.req.server.RequisitionAM">
<!-- Field definitions: type, prompt, VO binding -->
<oa:messageTextInput id="Description" prompt="Description"
    viewName="ReqLinesVO" viewAttr="ItemDescription"
    required="yes" maximumLength="240"/>
<oa:messageLovInput id="CategoryLov" prompt="Category"
    viewName="ReqLinesVO" viewAttr="CategoryDescription"
    externalListOfValues="/oracle/apps/icx/por/req/webui/CategoryLovRN"
    required="yes" selectiveSearchCriteria="true"/>
<oa:messageCheckBox id="TaxableFlag" prompt="Taxable"
    viewName="ReqLinesVO" viewAttr="TaxableFlag"
    checkedValue="Y" uncheckedValue="N"/>
<!-- Hidden fields (formValue = not displayed but passed to server) -->
<oa:formValue id="CategoryId" viewName="ReqLinesVO" viewAttr="CategoryId"/>
<!-- Regions define layout groupings (like form tabs) -->
<oa:stackLayout id="LineDetailsRN">
  <!-- child items here -->
</oa:stackLayout>

OAF element types → form equivalents:

1-OAF.4 Read View Object SQL

VO XML files contain the actual SQL query:

<!-- PoRequisitionLinesVO.xml -->
<ViewObject Name="PoRequisitionLinesVO">
  <SQLQuery><![CDATA[
    select requisition_line_id, item_description, category_id,
           unit_meas_lookup_code, quantity, unit_price, amount,
           suggested_vendor_name, org_id
    from po_requisition_lines_all
    where requisition_header_id = :1
  ]]></SQLQuery>
  <!-- Attribute definitions (column metadata) -->
  <ViewAttribute Name="RequisitionLineId" ColumnName="REQUISITION_LINE_ID"
      Type="oracle.jbo.domain.Number" IsPersistent="false"/>
  <ViewAttribute Name="ItemDescription" ColumnName="ITEM_DESCRIPTION"
      Type="oracle.jbo.domain.String" UpdateableFlag="Always"/>
</ViewObject>

What to extract from VO XML:

1. SQL query — the base table(s), joins, WHERE clause
2. Attribute definitions — column name, Java type, updateable flag
3. Bind variables — parameterized WHERE conditions
4. Entity references — which Entity Object handles DML

1-OAF.5 Understand LOVs in OAF

OAF LOVs are defined as separate Region XML files (*LovRN.xml) or inline:

# Find LOV region definitions
find $APPL_TOP/*/12.0.0/mds -name "*LovRN.xml" -path "*/req/*" 2>/dev/null
# LOV regions define:
# - lovMappings (which VO attrs pass to/from the LOV)
# - Search criteria
# - Result columns displayed

The LOV mapping tells you:

• lovInputSourceId — which page field's value is passed as search criteria
• returnItemId — which page field receives the selected value
• criteriaItemId — search criteria VO attribute

1-OAF.6 Validation Logic in OAF

OAF validation lives in three places (check all):

1. VO.xml declarative rules — <ViewAttribute ... Mandatory="true" />, value constraints
2. Entity Object validation — *EOImpl.class (compiled, read package body SQL instead)
3. Server-side PL/SQL packages — same as Oracle Forms, the API packages do the real validation. This is the most important source.

Since Java source (.java) is not available on the server (only compiled .class), focus on:

• Declarative metadata from the XML files (field types, mandatory, LOV bindings)
• The underlying PL/SQL API packages (same as Phase 3 for Oracle Forms)
• Database table constraints and triggers

Continue to Phase 2 to extract field details from the PG.xml, then Phase 3 to analyze the API packages.

Phase 2: Extract Form Metadata

OAF pages: The same metadata needs to be extracted, but from different sources. Use the PG.xml for field inventory (item elements = fields), VO.xml for data types and SQL, and LovRN.xml for LOVs. The output format (FAD sections 4.1-4.7) is identical regardless of form type.

2.1 Data Blocks

For each data block, document:

• Block Name, Base Table, Record Count (single/multi)
• Query/Insert/Update/Delete Allowed
• WHERE Clause, ORDER BY
• Master-detail relationships

2.2 Fields (Items)

For EVERY visible/editable item in each data block, extract:

CRITICAL — Visual Field Order (from .fmb XML): The upload column order MUST match the form's visual layout. Extract this from the converted form XML, NOT from guesswork.

Method 1: From frmf2xml output (preferred) The converted XML contains canvas coordinates for each item:

<Item Name="RECEIPT_METHOD_NAME" ... XPosition="10" YPosition="20" Canvas="MAIN_CANVAS" .../>
<Item Name="RECEIPT_NUMBER" ... XPosition="10" YPosition="50" Canvas="MAIN_CANVAS" .../>

Sort items by: Canvas → TabPage → YPosition → XPosition to get the visual order.

Method 2: From strings output (fallback) When XML conversion isn't available, the strings output preserves item ordering within blocks. Items appear in their internal sequence order, which generally follows the tab order. Cross-reference with Prompt text to map internal names to user-facing labels.

Method 3: From a form screenshot If a screenshot is available, read fields top-to-bottom, left-to-right within each region.

Steps:

1. Group by canvas/tab page — header canvas first, then each detail tab in order
2. Sort within each canvas by Y coordinate (top→bottom), then X coordinate (left→right)
3. Note visual groupboxes — form frame labels (e.g., "Receipt", "Customer", "Remittance Bank")
4. Number each field sequentially — this number becomes the upload column order

CRITICAL — Use Prompts, not internal names:

• The form's Prompt attribute (e.g., "Receipt Method", "GL Date") is what users see
• Upload column aliases MUST match these prompts, NOT the internal database column names
• Extract prompts from the XML: <Item Name="RECEIPT_METHOD_ID" Prompt="Receipt Method" .../>
• When a prompt differs from the DB column (e.g., Prompt="Location" for column CUSTOMER_SITE_USE_ID), always use the prompt
• If strings fallback is used, prompts appear near their item names in the output

CRITICAL — Form Data Entry Workflow: Document the order in which users MUST enter data, noting dependencies:

• Which fields must be filled before others become enabled or their LOV works?
• Example: "Customer must be entered before the Invoice LOV shows results"
• Example: "Book must be selected before Category LOV is filtered"
• Look for WHEN-VALIDATE-ITEM triggers that enable/disable other items
• Look for LOV WHERE clauses that reference other form items (:block.item)
• This directly affects LOV query design — dependent LOVs should use required joins (not is null or patterns that prevent index usage)

Mandatory classification — be precise:

• REQUIRED — form raises error if empty on save
• CONDITIONAL — mandatory only when another field has a specific value (document the condition)
• OPTIONAL — can be left blank
• SYSTEM — set by the system, user cannot enter (creation_date, created_by, etc.)

Default value — document EXACTLY:

• Hardcoded value: 'STANDARD'
• Profile option: fnd_profile.value('ORG_ID')
• Sequence: FA_MASS_ADDITIONS_S.NEXTVAL
• Derived from other field: DEPRECIATION_METHOD derived from CATEGORY via fa_category_books
• Trigger-based: describe the trigger code

2.3 Triggers — The Most Critical Part

Extract ALL triggers at item, block, and form level.

Item-Level Triggers

What to extract from WHEN-VALIDATE-ITEM:

1. Validation SQL: What query does it run?
2. Error message: What error on failure?
3. Cross-field updates: Does it populate OTHER items? (This IS the defaulting logic)
4. Conditional logic: Does validation depend on other field values?

Example extraction:

WHEN-VALIDATE-ITEM on CATEGORY:
  1. Query fa_categories_b where category_id=:ADDITIONS.CATEGORY_ID
  2. If found: set DEPRECIATE_FLAG, LIFE_IN_MONTHS, DEPRN_METHOD from fa_category_books
  3. If not found: raise error "Invalid Category"
  → Upload: DEFAULT_VALUE on Depreciate, Life, Method dependent on Category
  → Upload: Validation in validate_asset function

Block-Level Triggers

What to extract from WHEN-VALIDATE-RECORD:

1. All cross-field checks (field A requires field B, date range validations)
2. Conditional requirements (field X required only when field Y='VALUE')
3. Business rules (amount positive, quantity matches UOM)

What to extract from WHEN-NEW-RECORD-INSTANCE:

1. Default values and their source (parent block? profile? system date? period end?)
2. Conditional defaults based on mode/context

2.4 LOV Definitions

For each LOV, extract:

• LOV Name and Record Group name
• Full SQL Query — including all WHERE conditions, references to form items, ORDER BY
• Column Mapping — which LOV column maps to which form item
• Dependent Filters — does the query reference other form items (:block.item)?

Example:

-- LOV: CATEGORY_LOV (dependent on Book)
select fcb.category_id, fcbk.concatenated_segments, fcbk.description
from fa_categories_b fcb, fa_categories_b_kfv fcbk, fa_category_books fcbo
where fcb.category_id=fcbk.category_id
and fcbo.category_id=fcb.category_id
and fcbo.book_type_code=:BOOKS.BOOK_TYPE_CODE  -- DEPENDENT on Book
and fcb.enabled_flag='Y'
and trunc(sysdate) between nvl(fcb.start_date_active,trunc(sysdate))
                        and nvl(fcb.end_date_active,trunc(sysdate))
order by fcbk.concatenated_segments

2.5 Key Flexfields (KFF)

For each KFF field, extract:

Query flex structure:

select fifsg.segment_name, fifsg.segment_num, fifsg.application_column_name,
  fifsg.required_flag, ffvs.flex_value_set_name
from fnd_id_flex_segments fifsg, fnd_flex_value_sets ffvs
where fifsg.application_id=:app_id and fifsg.id_flex_code=:flex_code
and fifsg.id_flex_num=:flex_num and fifsg.enabled_flag='Y'
and ffvs.flex_value_set_id=fifsg.flex_value_set_id
order by fifsg.segment_num;
select fifst.concatenated_segment_delimiter
from fnd_id_flex_structures fifst
where fifst.application_id=:app_id and fifst.id_flex_code=:flex_code
and fifst.id_flex_num=:flex_num;
select count(*) from fnd_shorthand_flex_aliases fsfa
where fsfa.application_id=:app_id and fsfa.id_flex_code=:flex_code
and fsfa.id_flex_num=:flex_num and fsfa.enabled_flag='Y';

2.6 Descriptive Flexfields (DFF)

Check if form has DFF support (ATTRIBUTE_CATEGORY + ATTRIBUTE1..15):

select fdfv.title, fdfv.application_table_name
from fnd_descriptive_flexs_vl fdfv
where fdfv.application_id=:app_id
and fdfv.descriptive_flexfield_name=:dff_name;

Phase 3: Analyze Database Packages

3.1 Find Package Source

select text from all_source
where name=:package_name and type='PACKAGE BODY'
order by line;

3.2 Extract API Procedures

Document:

1. Procedure signature: All parameters with types and IN/OUT
2. Mandatory parameters: Which cannot be null?
3. Validation logic: What checks does it perform?
4. DML operations: What tables does it insert/update?
5. Error handling: Status + message? Exceptions? FND message stack?

3.3 Interface Tables (if applicable)

For interface-based: document ALL interface columns, processor concurrent program, error table(s).

3.4 Record Identification

How does the form uniquely identify an existing record for UPDATE:

Primary Key Column(s): e.g., ASSET_ID, BOOK_TYPE_CODE
Business Key (user-visible): e.g., ASSET_NUMBER + BOOK
Lookup SQL: select fa.asset_id from fa_additions fa where fa.asset_number=:value

3.5 Delete / Close / Cancel Logic

Can records be deleted? YES/NO
Delete restrictions: conditions that prevent deletion
Soft delete? Y flag or status code used
Alternatives to delete: Cancel, Close, Void — document each
API for cancel/close: package.procedure

Phase 4: Produce the Form Analysis Document

4.1 Summary

Form: FAXASSET (FA Asset Workbench)
Module: Fixed Assets (FA)
Base Tables: FA_ADDITIONS_B, FA_BOOKS, FA_DISTRIBUTION_HISTORY
API/Interface: FA_MASS_ADDITIONS (interface) → FAMAPT (processor)
DFF: FA_ADDITIONS (ATTRIBUTE_CATEGORY + ATTRIBUTE1..15)
KFFs: GL# (Expense Account), LOC# (Location), CAT# (Category)

4.2 Visual Field Order

CRITICAL — this section defines the upload column order.

Document fields in the exact visual layout order (top-to-bottom, left-to-right within each region/tab):

Region: [Header/Detail Tab Name]
 #  Field Label              Block.Item              Type    Req  LOV
 1  Receipt Method           RECEIPTS.RECEIPT_METHOD Char    Y    RECEIPT_METHOD_LOV
 2  Receipt Number           RECEIPTS.RECEIPT_NUMBER Char    N    —
 3  Receipt Amount           RECEIPTS.AMOUNT         Number  Y    —
 ...
Region: [Detail - Main Tab]
 7  Customer Name            DETAIL.CUSTOMER_NAME    Char    N    CUSTOMER_LOV
 ...

4.2b Data Entry Workflow

Document field entry dependencies — which fields must be populated before others work:

1. Receipt Method → required first (determines bank account defaults)
2. Customer Name/Number → must be entered before Invoice LOV works
3. Book → must be selected before Category LOV is filtered

This directly impacts upload LOV design: dependent LOVs should use required joins (not is null or patterns that disable index usage).

4.3 Field Inventory (Detailed)

4.4 Defaulting Map

4.5 Validation Map

4.6 LOV Map

4.7 KFF Map

4.8 API/Interface Details

Document the exact API call or interface table insert, including all parameters/columns.

4.9 Record Identification

Primary Key: ASSET_ID
Business Key: ASSET_NUMBER + BOOK_TYPE_CODE
Lookup SQL: select fa.asset_id from fa_additions fa where fa.asset_number=:asset_number

4.10 Delete / Cancel Logic

Document if records can be deleted, cancelled, closed, or voided — and which API to use.

4.11 Upload Design Recommendations

Based on the analysis, recommend:

• Which form fields to expose as upload columns (vs system-set)
• Fields that should be read-only in upload
• Suggested default values for upload parameters
• Identified risks / tricky validations the builder must handle
• Which actions to support: Create / Update / Delete

4.12 Open Questions

List any assumptions made where form source was unclear, so the developer can verify before building.

Quality Checklist Before Handoff

Before producing the FAD, verify:

• Every mandatory field has a default or is exposed as an upload column
• Every LOV has its full SQL query documented
• Every cross-field dependency is documented
• The API package and procedure signature is confirmed
• Primary key and business key are both identified
• Initialization requirements (apps_initialize, mo_global) are noted
• At least one existing similar Blitz upload was referenced for patterns

Tips for Accurate Analysis

1. Don't rely on form layout alone — real logic is in triggers and database packages
2. Check PL/SQL libraries (.pll) — significant logic may live there
3. Test on live instance — enter data in form and observe auto-population
4. Check profile options — defaults may come from fnd_profile.value() calls

Handoff

The Form Analysis Document is consumed by:

• upload-builder — uses field inventory, LOVs, defaults, and API details to build the upload
• upload-validator — uses the document to compare against the built upload and find gaps