Results for “days_in_period”

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

Overview

OKL_SIF_RET_LEVELS is a transactional table in the Oracle E-Business Suite Leasing and Finance Management (OKL) module. It holds the payment levels returned from the external pricing engine after a lease or loan contract has been priced. When Oracle Lease Management submits a stream of pricing requests to an external engine through the Stream Interface (SIF) layer, the engine returns structured results that describe payment streams, individual levels within each stream, and the corresponding amounts, periods, and dates. OKL_SIF_RET_LEVELS is the child table that persists the individual level detail of that returned pricing result, one row per payment level.

The table resides in the OKL schema and is validated in both Oracle EBS 12.1.1 and 12.2.2. Its documented physical schema contains 36 columns. From a Data Vault modeling perspective, the structure of this table is satellite-leaning: it is primarily descriptive detail captured around a parent pricing return header, with the foreign key to OKL_SIF_RETS anchoring each level row to its owning returned stream. This classification is a heuristic suggestion based on the FK topology rather than a mandated design.

Key Information Stored

The table stores the granular payment level information needed to reconstruct the pricing engine's response. The most significant columns are:

• ID — the surrogate primary key (constraint SRL_PK) that uniquely identifies each level row.
• SIR_ID — the foreign key to OKL_SIF_RETS, linking the level back to the returned stream header.
• INDEX_NUMBER — sequence reference for the stream item within the returned result.
• LEVEL_INDEX_NUMBER — ordering of the individual level within its stream; together with SIR_ID and INDEX_NUMBER this forms unique constraint SRL_SRL_UK.
• LEVEL_TYPE — categorizes the level (for example, a payment or non-payment level returned by the engine).
• NUMBER_OF_PERIODS — count of periods covered by the level.
• AMOUNT — monetary value of the level as returned by the pricing engine.
• PERIOD — the period frequency or code associated with the level.
• ADVANCE_OR_ARREARS — indicates whether the payment falls at the beginning or the end of the period.
• FIRST_PAYMENT_DATE — the date on which the first payment for the level falls due.
• DAYS_IN_PERIOD — number of days in the level's period, used in day-count and interest recalculation logic.
• RATE — the rate applied to the level.
• REAMORT_BALANCE / REAMORT_DATE — balance and date used when the level triggers reamortization.
• LOCK_LEVEL_STEP — controls step-locking behavior for the level.
• STREAM_INTERFACE_ATTRIBUTE1…15 — fifteen descriptive flexfield-style columns reserved for additional attributes returned by the pricing engine.
• OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard concurrency control and audit columns.

The documented business-key candidate is the unique index SRL_SRL_UK over SIR_ID, INDEX_NUMBER, and LEVEL_INDEX_NUMBER; the surrogate key remains ID.

Common Use Cases and Queries

Typical scenarios include reconciling the external pricing engine's response against the generated payment schedule, validating that returned levels total the expected financed amount, and reporting on rate, amount, and first payment date for a priced contract.

Joining levels to their parent stream header is the most common pattern:

• SELECT l.ID, l.LEVEL_INDEX_NUMBER, l.LEVEL_TYPE, l.AMOUNT, l.RATE, l.FIRST_PAYMENT_DATE FROM OKL_SIF_RET_LEVELS l, OKL_SIF_RETS r WHERE l.SIR_ID = r.ID AND r.SIR_ID = :p_sir;
• Aggregation queries sum AMOUNT grouped by LEVEL_TYPE where ADVANCE_OR_ARREARS is 'ADVANCE' to build advance-payment reporting.
• Exception queries compare NUMBER_OF_PERIODS multiplied by AMOUNT against the parent stream total to detect engine rounding discrepancies.
• Audit queries filter on CREATION_DATE to isolate levels returned during a specific pricing run.

Related Objects

The following objects are most significant in relation to OKL_SIF_RET_LEVELS:

• OKL_SIF_RETS — the parent table holding returned stream headers; joined on OKL_SIF_RET_LEVELS.SIR_ID = OKL_SIF_RETS.ID (documented foreign key).
• OKL_SIF_REQUESTS / OKL_SIF_REQUEST_LEVELS — the outbound request counterparts submitted to the pricing engine, used to trace a return back to its originating request.
• OKL_STREAMS / OKL_STREAM_LEVELS — the persistent contract payment stream tables populated from the returned SIF results.
• OKL_CONTRACTS — the lease or loan contract whose pricing produced the returned levels.
• Stream Interface (SIF) concurrent programs and APIs — the processes that invoke the external pricing engine and load OKL_SIF_RET_LEVELS as part of the return processing cycle.