Search Results oks_stream_levels_b_n2

Overview

OKS.OKS_STREAM_LEVELS_B is a transactional base table in the Oracle E-Business Suite Order Management and Contracts (OKS) schema. It stores the billing stream information defined for a contract header, contract line, or sub line. Each record defines a discrete billing stream level with its own chronological date range; the table enforces that streams belonging to the same contract, line, or sub line do not overlap and are tracked through the SEQUENCE_NO column. Records are created and maintained either through the Billing Schedule form (OKSAUBLG.fmb) or programmatically through the OKS_BILL_SCH API, and this information is subsequently used to derive the billing schedule.

The table exhibits a hybrid structure. A stream level may be defined at the contract header level, in which case CHR_ID is populated from OKC_K_HEADERS_B.ID and CLE_ID remains null, or at the line/sub line level, in which case CHR_ID is null and CLE_ID is populated from OKC_K_LINES_B.ID. The denormalized DNZ_CHR_ID column duplicates the contract header identifier for performance reasons, allowing header-level queries to avoid joins through the line hierarchy. Under the heuristic Data Vault classification mined from the foreign key structure, this object is tagged as standalone, suggesting it be modeled as an independent hub or reference table rather than a link or satellite.

Key Information Stored

The table contains 29 documented columns spanning identifiers, scheduling attributes, amounts, and standard EBS audit columns. The most significant include:

• ID — the surrogate primary key, defined by primary key constraint OKS_STREAM_LEVELS_B_PK and the unique index OKS_STREAM_LEVELS_B_U1. This is the business-key candidate for uniqueness and the join key for dependent objects.
• CHR_ID — contract header identifier when the stream level applies at the header level; references OKC_K_HEADERS_B.ID. Indexed non-uniquely via OKS_STREAM_LEVELS_B_N2.
• CLE_ID — contract line identifier when the stream level applies at the line or sub line level; references OKC_K_LINES_B.ID. Indexed via OKS_STREAM_LEVELS_B_N3.
• DNZ_CHR_ID — denormalized contract header ID used for performance, indexed via OKS_STREAM_LEVELE_B_N1.
• SEQUENCE_NO — user-entered chronological sequence number establishing the order of the billing streams associated with a contract, line, or sub line.
• UOM_CODE — unit of measure for the duration, referenced from OKC_TIME_CODE_UNITS_B.UOM_CODE.
• START_DATE and END_DATE — the effective date range for the stream level; ranges across a contract's streams must not overlap.
• LEVEL_PERIODS, UOM_PER_PERIOD, and ADVANCE_PERIODS — quantify the billing span, unit of measure per period, and number of advance periods.
• LEVEL_AMOUNT and AMOUNT — monetary values associated with the stream level.
• INVOICE_OFFSET_DAYS and INTERFACE_OFFSET_DAYS — offsets applied when generating invoices and interfacing to downstream systems.
• DUE_ARR_YN and LINES_DETAILED_YN — flags controlling due/arrears treatment and whether lines are detailed.
• Standard audit columns include CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, REQUEST_ID, and SECURITY_GROUP_ID.

The SECURITY_GROUP_ID column is the only documented foreign key reference, linking to FND_SECURITY_GROUPS.

Common Use Cases and Queries

Typical queries retrieve the billing streams for a contract or line, or reconstruct the billing schedule. The following pattern returns all streams for a given contract header, ordered chronologically:

• SELECT id, chr_id, cle_id, dnz_chr_id, sequence_no, start_date, end_date, level_amount, amount FROM oks.oks_stream_levels_b WHERE dnz_chr_id = :p_chr_id ORDER BY sequence_no;
• Line-level streams: SELECT id, cle_id, sequence_no, start_date, end_date FROM oks.oks_stream_levels_b WHERE cle_id = :p_cle_id ORDER BY sequence_no;
• Overlap validation: compare START_DATE and END_DATE ranges across streams for a contract to detect impossible overlaps before creating new records.
• Amount aggregation: SELECT dnz_chr_id, SUM(level_amount) FROM oks.oks_stream_levels_b GROUP BY dnz_chr_id;
• Reporting on upcoming invoices: filter on INVOICE_OFFSET_DAYS and START_DATE to project billing events.

Related Objects

The table participates in a limited set of documented relationships, supplemented by its usage context:

• OKC_K_HEADERS_B — joined on CHR_ID (or DNZ_CHR_ID) for contract header details.
• OKC_K_LINES_B — joined on CLE_ID for contract line and sub line details.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for security grouping.
• OKC_TIME_CODE_UNITS_B — provides UOM_CODE values for duration units.
• OKS_BILL_SCH — the API responsible for creating stream level records.
• OKSAUBLG.fmb — the Billing Schedule form used for manual maintenance of this data.