Search Results delete_entity

Overview

BOM_DELETE_ENTITIES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is delivered as part of the Bills of Material (BOM) product family and, according to the ETRM definition, exposes deleted entities in BOM and INV. The view sits on top of the BOM_DELETE_ENTITIES table, which records the results of the Bill of Materials deletion process, and it enriches each row with decoded lookup meanings and the inventory organization code. This makes the view the canonical, presentation-ready source for reporting on which bills, routings, or inventory items have been deleted and the status of each deletion.

Its role in reporting and integration is significant. Rather than forcing developers and report authors to join the deletion table to the lookup tables and to MTL_PARAMETERS manually, the view performs those joins once and returns descriptive values such as DELETE_ENTITY and DELETE_STATUS alongside the raw lookup codes. The view is therefore commonly used in concurrent program output, Oracle Reports, BI Publisher data models, and custom SQL when auditing deletion activity or troubleshooting failed or pending deletions.

Underlying Base Objects

The documented ETRM metadata lists three referenced base objects: BOM_DELETE_ENTITIES (SYNONYM), MFG_LOOKUPS (VIEW), and MTL_PARAMETERS (SYNONYM). The view joins these with outer joins on the lookup tables so that a deletion row is returned even when no matching lookup meaning exists.

• BOM_DELETE_ENTITIES — the driving table. It supplies the primar y deletion entity record, including sequence identifiers, entity type, bill and routing sequence identifiers, inventory item context, and audit columns.
• MFG_LOOKUPS — joined twice, once for the 'BOM_DELETE_ENTITY_TYPE' lookup type to supply DELETE_ENTITY, and once for the 'BOM_DELETE_STATUS_TYPE' lookup type to supply DELETE_STATUS. Both joins are outer joins.
• MTL_PARAMETERS — joined on ORGANIZATION_ID to supply ORGANIZATION_CODE, the inventory organization short name.

Because two of the three sources are synonyms or views in APPS, the view presents a stable APPS-facing interface while the underlying physical objects remain in their base schemas.

Key Columns

• DELETE_ENTITY_SEQUENCE_ID and DELETE_GROUP_SEQUENCE_ID — identifiers that group and order individual deletion entities within a deletion run.
• DELETE_ENTITY_TYPE / DELETE_ENTITY — the lookup code and its decoded meaning for the type of object being deleted.
• BILL_SEQUENCE_ID, ROUTING_SEQUENCE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID — the BOM and inventory context of the deleted entity.
• ALTERNATE_DESIGNATOR, ITEM_DESCRIPTION, ITEM_CONCAT_SEGMENTS — descriptive item and alternate information associated with the deletion.
• DELETE_STATUS_TYPE / DELETE_STATUS — the status lookup code and decoded meaning for the deletion.
• DELETE_DATE, PRIOR_PROCESS_FLAG, PRIOR_COMMIT_FLAG — timing and processing state of the deletion.
• ROW_ID — the ROWID of the BOM_DELETE_ENTITIES row, useful for row-level identification.
• ORGANIZATION_CODE — decoded organization name from MTL_PARAMETERS.
• Standard audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The view is used to audit deletion activity, monitor outstanding or failed deletions, and feed reporting on bill and inventory cleanup.

SELECT delete_entity,
       delete_status,
       organization_code,
       item_concat_segments,
       delete_date
FROM   apps.bom_delete_entities_v
WHERE  delete_date >= SYSDATE - 30
ORDER  BY delete_date DESC;

To examine pending items awaiting processing:

SELECT delete_group_sequence_id,
       delete_entity,
       delete_status,
       prior_process_flag,
       prior_commit_flag
FROM   apps.bom_delete_entities_v
WHERE  delete_status_type NOT IN ('COMPLETE','ERROR')
ORDER  BY delete_group_sequence_id, delete_entity_sequence_id;

Because DELETE_ENTITY and DELETE_STATUS are already decoded through MFG_LOOKUPS, reporting queries need not join to the lookup tables. The outer joins also ensure that rows remain visible even if a lookup meaning is missing, which assists with diagnosing configuration gaps in the lookup set.