Results for “delete_mark”

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

Overview

SY_PURG_MST_CRIT is a table owned by the GMA schema within Oracle E-Business Suite, belonging to the Process Manufacturing Systems product family (GMA). It functions as a child table to SY_PURG_MST, storing the individual criteria rows that define the scope of an OPM Purge and Archive run. Each record associates a specific criterion tag and value with a parent Purge ID, allowing the purge engine to determine which production, inventory, or quality records qualify for archival or deletion.

From a dimensional modeling perspective, the heuristic Data Vault classification mined from the foreign key structure is link. This classification should be treated as a modeling suggestion rather than a documented fact. The table behaves as a link because it bridges the Purge master record (SY_PURG_MST) with another referenced entity (SY_TEXT_HDR via TEXT_CODE), while carrying descriptive attributes such as CRIT_VALUE and TRANS_CNT that describe the relationship rather than a standalone business entity.

Key Information Stored

The primary key SY_PURG_MST_CRIT_PK is a composite surrogate covering PURGE_ID and CRIT_TAG, meaning uniqueness is enforced only in combination. The most significant columns are:

• PURGE_ID — Foreign key to SY_PURG_MST; identifies the parent purge/archive definition this criterion belongs to.
• CRIT_TAG — The criterion identifier or tag name; part of the composite primary key.
• CRIT_VALUE — The value entered against the criterion tag, defining the filter condition for the purge run.
• TEXT_CODE — Foreign key to SY_TEXT_HDR, linking the criterion to extended descriptive text.
• TRANS_CNT — Count of transactions or records affected by the criterion, supporting audit and throughput reporting.
• DELETE_MARK — Standard soft-delete/active flag used throughout OPM tables.
• CREATION_DATE / CREATED_BY — Audit columns capturing who defined the criterion and when.
• LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — Audit columns capturing the most recent modification and session context.

The business-key candidate derived from the unique index is (PURGE_ID, CRIT_TAG), which doubles as the primary key in this case. No separate surrogate sequence column is documented.

Common Use Cases and Queries

Typical usage centers on reviewing and troubleshooting purge definitions before executing an archive job. Common scenarios include verifying which criteria are attached to a purge ID, extracting filter values for reconciliation, and reporting criteria changes over time.

• List all criteria for a given purge:

  SELECT crit_tag, crit_value, trans_cnt
  FROM   gma.sy_purg_mst_crit
  WHERE  purge_id = :purge_id
  AND    delete_mark = 0;

• Join to the master to summarize purge scope:

  SELECT m.purge_id, m.description, c.crit_tag, c.crit_value
  FROM   gma.sy_purg_mst m, gma.sy_purg_mst_crit c
  WHERE  m.purge_id = c.purge_id;

• Audit recent criterion changes:

  SELECT crit_tag, crit_value, last_updated_by, last_update_date
  FROM   gma.sy_purg_mst_crit
  WHERE  purge_id = :purge_id
  ORDER BY last_update_date DESC;

Reporting typically focuses on mapping criteria to record counts (TRANS_CNT) to estimate archive volume prior to execution.

Related Objects

• SY_PURG_MST — Parent table; joined on SY_PURG_MST_CRIT.PURGE_ID = SY_PURG_MST.PURGE_ID. Holds the purge definition header.
• SY_TEXT_HDR — Referenced via SY_PURG_MST_CRIT.TEXT_CODE = SY_TEXT_HDR.TEXT_CODE. Supplies extended descriptive text for criteria.
• GMA purge/archive concurrent programs — Consume this table to resolve filter criteria at runtime.
• SY_PURG_MST_CRIT_PK index — Enforces uniqueness on (PURGE_ID, CRIT_TAG).

Because the metadata documents only two outbound foreign keys, the dependency set is intentionally narrow: SY_PURG_MST and SY_TEXT_HDR are the principal related objects, with the purge engine acting as the primary consumer.