Search Results edr_files_b




Overview
Explore in AI Chat

Overview

The EDR_FILES_B table is the base table within the EDR (E-Records) product module of Oracle E-Business Suite, responsible for storing the files uploaded through the ERES (Electronic Records and Electronic Signatures) File Upload System. In EBS 12.1.1 and 12.2.2, this table serves as the central repository for file metadata that supports regulatory compliance and electronic record-keeping requirements, particularly those mandated by 21 CFR Part 11 for life sciences organizations. The table is owned by the EDR schema and is documented as VALID in the ETRM repository.

From a data modeling perspective, the heuristic Data Vault classification mined from the foreign key structure identifies EDR_FILES_B as a standalone table. This classification suggests that the table functions independently as a hub-like entity, with no documented inbound or outbound foreign key dependencies captured in the mined relationship data. Analysts designing integration or warehousing layers should treat file records as autonomous business entities, though additional relationships may exist at the application tier that are not reflected in the physical FK constraints.

Key Information Stored

The table comprises 31 documented columns. The most significant storage elements include:

  • FILE_ID — The surrogate primary key, enforced by the EDR_FILES_B_P unique index. It uniquely identifies each uploaded file record.
  • FILE_NAME and CATEGORY_ID — Together they form the business-key candidate EDR_FILES_B_U, ensuring that file names remain unique within a given category.
  • ORIGINAL_FILE_NAME — Captures the name of the file as supplied by the end user at upload time, before any system renaming.
  • CONTENT_TYPE and FILE_FORMAT — Describe the MIME type and physical format of the stored content.
  • VERSION_LABEL and VERSION_NUMBER — Support versioning of uploaded documents, enabling controlled document revision history.
  • STATUS — Tracks the lifecycle state of the file record (for example, active, obsolete, or pending approval).
  • FND_DOCUMENT_ID — References the Oracle Foundation document repository, linking the EDR record to the physical file stored via the FND attachment framework.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle descriptive flexfield columns, allowing customer-specific extension without schema modification.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — The mandatory WHO audit columns present on all Oracle EBS transactional tables.

The distinction between FILE_ID (surrogate key) and the FILE_NAME/CATEGORY_ID composite (business key) is important when writing joins or deduplication logic in downstream reporting.

Common Use Cases and Queries

Typical reporting and operational scenarios include auditing uploaded records for a specific category, tracing which files were uploaded during a given period, and joining file metadata to electronic signature records. A representative query retrieves files by category:

  • SELECT file_id, file_name, original_file_name, version_number, status FROM edr.edr_files_b WHERE category_id = :category_id ORDER BY creation_date DESC;
  • Auditing recent uploads: filter on CREATION_DATE between two timestamps to reconstruct upload activity for compliance reviews.
  • Version analysis: group by FILE_NAME and CATEGORY_ID to identify records with multiple VERSION_NUMBER values, supporting document control reporting.
  • Integration with FND: join FND_DOCUMENT_ID to the FND documents tables to retrieve the physical file and its attachment metadata.

Related Objects

Because EDR_FILES_B is classified as standalone, its documented dependencies are limited, but practical relationships exist through application logic and shared columns:

  • FND_DOCUMENTS / FND_ATTACHED_DOCUMENTS — Joined via FND_DOCUMENT_ID to resolve the actual stored file and attachment context.
  • EDR_ESIGNATURES and related signature tables — Reference uploaded files as the subject of electronic signature events.
  • EDR_CATEGORIES (or equivalent category lookup) — Joined via CATEGORY_ID to resolve the classification of each file.
  • FND_FLEX_VALUES / FND_DESCR_FLEX_COLUMN_USAGES — Support interpretation of the ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 flexfield values.
  • FND_USER — Joined via CREATED_BY and LAST_UPDATED_BY to resolve user identity for audit trails.

Consultants should validate additional relationships against the specific EBS patch level, as the EDR module has evolved between 12.1.1 and 12.2.2.