Search Results sy_purg_def_act




Overview
Explore in AI Chat

Overview

The SY_PURG_DEF_ACT table is a core configuration table within the Oracle Process Manufacturing (OPM) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. Residing in the GMA (Process Manufacturing Systems) schema, it defines the specific data management actions to be executed by the OPM Purge and Archive utility. This utility is critical for database performance and compliance, as it systematically removes or archives historical transactional data from the OPM product family. The table operates on a per-table basis, serving as the rule engine that instructs the purge process on whether to delete, archive, or take another defined action for each individual database table included in a purge set.

Key Information Stored

The table's structure is designed to map purge definitions to specific actions for target tables. Its primary key is a composite of PURGE_TYPE and TABLE_NAME, ensuring a unique action for each table within a given purge context. The PURGE_TYPE column links to the SY_PURG_DEF table, identifying the overarching purge definition or campaign. The TABLE_NAME column specifies the exact database table subject to the purge action. A critical column, though not explicitly named in the provided metadata but inherent to its purpose, would define the action itself (e.g., 'PURGE', 'ARCHIVE', 'SKIP'). Furthermore, the TEXT_CODE column establishes a foreign key relationship to SY_TEXT_HDR, which is a standard OPM table for storing multilingual descriptive text, likely used to provide a description or name for the configured action.

Common Use Cases and Queries

The primary use case is the administration and execution of data purge cycles in OPM environments. Database administrators and functional consultants query this table to audit, validate, and troubleshoot purge configurations before execution. A common query would list all tables and their designated actions for a specific purge type to ensure business rules are correctly implemented. For example: SELECT purge_type, table_name, text_code FROM gma.sy_purg_def_act WHERE purge_type = 'HISTORICAL_SALES' ORDER BY table_name;. Another typical scenario involves identifying all purge definitions that affect a particular transactional table, such as inventory transactions, to understand its lifecycle: SELECT a.purge_type, d.description FROM gma.sy_purg_def_act a JOIN gma.sy_purg_def d ON a.purge_type = d.purge_type WHERE a.table_name = 'MTL_MATERIAL_TRANSACTIONS';.

Related Objects

  • SY_PURG_DEF: This is the parent definition table. The foreign key from SY_PURG_DEF_ACT.PURGE_TYPE references SY_PURG_DEF, linking the specific table actions to the master purge campaign that controls parameters like date ranges and overall execution logic.
  • SY_TEXT_HDR: This table stores translatable text. The foreign key from SY_PURG_DEF_ACT.TEXT_CODE references SY_TEXT_HDR, likely to provide a descriptive name or note for the purge action defined for each table, supporting multilingual implementations.
  • SY_PURG_DEF_ACT_PK: This is the primary key constraint enforcing uniqueness on the combination of PURGE_TYPE and TABLE_NAME columns.