Results for “sy_purg_def”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

SY_PURG_DEF is an Oracle E-Business Suite table owned by the GMA schema, which corresponds to Process Manufacturing Systems (OPM). The table stores purge and archive definitions used by the OPM data management utilities. Each row represents a named purge definition that describes a class of historical or transactional data eligible for removal or archival, along with the metadata required for the purge program to identify, count, and process records in the associated source tables.

Within the EBS 12.1.1 and 12.2.2 data model, SY_PURG_DEF acts as a reference or definition table rather than a transactional table. It is consumed at runtime by purge and archive concurrent programs that read the definition, evaluate the configured criteria, and execute against the target operational tables. In 12.2.2 the table also carries the ZD_EDITION_NAME column and the associated editioning infrastructure typical of the Online Patching (ADOP) architecture.

Based on the documented foreign key topology, the heuristic Data Vault classification for this object is hub-leaning. Its primary key, PURGE_TYPE, functions as a business key that is referenced by multiple dependent tables, which is consistent with the behaviour of a hub entity. This classification is offered as a modeling suggestion rather than a defined attribute of the EBS data model itself.

Key Information Stored

The table contains 15 documented columns. The most significant are described below.

• PURGE_TYPE — The primary key column and the principal business identifier for a purge definition. Defined by the SY_PURG_DEF_PK unique index in combination with ZD_EDITION_NAME in 12.2.2. All dependent tables join to SY_PURG_DEF through this column.
• PURGE_CLASS — Classifies the purge definition, typically grouping definitions by functional area or processing behaviour.
• PURGE_DESC — The descriptive name or text that identifies the purge definition to users.
• TRANS_CNT — A counter or threshold value associated with the volume of transactions handled by the definition.
• TEXT_CODE — Foreign key to SY_TEXT_HDR, linking the definition to stored descriptive or instructional text.
• WORK_TABLESPACE — The tablespace used during purge processing for intermediate or workspace data.
• DEFAULT_TARGET_TABLESPACE — The default tablespace to which purged or archived data is directed.
• SQLSTATEMENT — The SQL fragment or statement used by the purge engine to select the candidate records for the definition.
• DELETE_MARK — A flag indicating the deletion or active status of the definition.
• ZD_EDITION_NAME — The editioning column supporting Online Patching in 12.2.2.

Standard audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN are also present. The surrogate primary key is SY_PURG_DEF_PK; the documented unique index SY_PURG_DEF_PK (PURGE_TYPE, ZD_EDITION_NAME) represents the closest business-key candidate.

Common Use Cases and Queries

Typical usage involves identifying available purge definitions, reviewing their configuration, and auditing which definitions are actively referenced by purge execution records. The following patterns are representative.

• Listing all defined purge types with their descriptions and classes:
  SELECT PURGE_TYPE, PURGE_CLASS, PURGE_DESC FROM GMA.SY_PURG_DEF WHERE DELETE_MARK = 'N';
• Reviewing the storage and SQL configuration for a specific definition:
  SELECT PURGE_TYPE, WORK_TABLESPACE, DEFAULT_TARGET_TABLESPACE, SQLSTATEMENT FROM GMA.SY_PURG_DEF WHERE PURGE_TYPE = :p_type;
• Joining to execution master records to audit purge runs:
  SELECT d.PURGE_TYPE, d.PURGE_DESC, m.* FROM GMA.SY_PURG_DEF d, GMA.SY_PURG_MST m WHERE d.PURGE_TYPE = m.PURGE_TYPE;
• Reporting on definitions with associated instructional text via SY_TEXT_HDR.

These queries support DBA and functional administration tasks such as capacity planning, impact analysis before running a purge, and troubleshooting failed or partial purges.

Related Objects

The following objects have documented relationships with SY_PURG_DEF.

• SY_PURG_MST — References SY_PURG_DEF.PURGE_TYPE; holds the purge master or execution records for each definition.
• SY_PURG_DEF_ACT — References SY_PURG_DEF.PURGE_TYPE; stores the action or target table details associated with a purge definition.
• SY_PURG_DEF_CRIT — References SY_PURG_DEF.PURGE_TYPE; stores the criteria used to select records for purging.
• SY_TEXT_HDR — Referenced by SY_PURG_DEF.TEXT_CODE; provides the text header record linked to the definition.

Together these dependent tables form the operational backbone of the OPM purge and archive subsystem, with SY_PURG_DEF serving as the central definition hub keyed by PURGE_TYPE.