Search Results bil_do_l1_base_grp_temp

Overview

BIL_DO_L1_BASE_GRP_TEMP is a transient staging table owned by the BIL schema within the Oracle E-Business Suite Sales Intelligence (BIL) product family. Its documented description identifies it as a "Data Out Level 1 table used for hierarchy of group levels for temporary use in collection programs." In practice, the table acts as a working area where BIL collection concurrent programs materialize the Level 1 hierarchy of sales group parents and their child groups before the results are consumed by downstream Data Out (DO) processes or promoted into permanent reporting structures.

The table is defined with a single-column primary key, BIL_DO_L1_BASE_GRP_TEMP_PK, built on CHILD_SALES_GROUP_ID. Because the object holds intermediate collection output rather than durable master or transactional data, the heuristic Data Vault classification mined from its foreign key structure is standalone; no hub, link, or satellite role is asserted. This reflects the object's architecture: it is a process-scoped temporary structure keyed for fast lookup during a single program run, not a persistent entity participating in an enterprise integration model. It carries the standard 13-column EBS footprint, including the WHO columns and the concurrent program tracking columns that establish its lineage to a specific request.

Key Information Stored

The most significant columns reflect the table's narrow purpose of storing a two-level sales group relationship with collection context:

• CHILD_SALES_GROUP_ID — the primary key column and the business-relevant identifier of the child sales group in the hierarchy. It is the only column documented as part of the primary key.
• SALES_GROUP_ID — the parent (or base) sales group identifier, establishing the parent-child edge of the Level 1 hierarchy.
• HIER_LEVEL — the level indicator for the group within the hierarchy, distinguishing the base group from its child groups.
• SECURITY_GROUP_ID — the security group context, which is also the table's sole documented foreign key.
• REQUEST_ID — the concurrent request that populated the rows, providing run-level lineage.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the concurrent program that wrote the row and when it last executed, used to scope and purge stale data.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard audit (WHO) columns.

No secondary unique index is documented beyond the primary key, so CHILD_SALES_GROUP_ID functions as both the surrogate key and the only enforced uniqueness constraint within a collection run. The parent-side column SALES_GROUP_ID is a business-key candidate for the hierarchy edge but is not uniquely constrained on its own.

Common Use Cases and Queries

The table is primarily queried during and immediately after BIL hierarchy collection runs, and is also inspected for diagnosis and purge of stale rows. A typical pattern resolves the parent hierarchy for each child group:

• Listing the base group membership for a specific run: SELECT SALES_GROUP_ID, CHILD_SALES_GROUP_ID, HIER_LEVEL FROM BIL.BIL_DO_L1_BASE_GRP_TEMP WHERE REQUEST_ID = :request_id ORDER BY SALES_GROUP_ID, HIER_LEVEL;
• Finding the parent of a known child group: SELECT SALES_GROUP_ID FROM BIL.BIL_DO_L1_BASE_GRP_TEMP WHERE CHILD_SALES_GROUP_ID = :child_group_id AND SECURITY_GROUP_ID = :security_group_id;
• Purge or housekeeping by program: DELETE FROM BIL.BIL_DO_L1_BASE_GRP_TEMP WHERE PROGRAM_ID = :program_id AND REQUEST_ID < :keep_after;

Reporting use cases focus on validating hierarchy completeness (orphan child groups not appearing under any parent) and reconciling collected base groups against the permanent BIL structures after a run completes.

Related Objects

The FK metadata identifies the following directly related objects:

• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; provides the security group context for collected rows.
• BIL_DO_L1_BASE_GRP_TEMP_PK — the primary key constraint on CHILD_SALES_GROUP_ID, used by the optimizer for indexed access and DML locking.
• The BIL Data Out collection concurrent programs that populate and read this table, identifiable through PROGRAM_APPLICATION_ID and PROGRAM_ID.
• The permanent BIL sales group hierarchy structures that the collection programs ultimately load from this temporary area.