Search Results okl_subsidy_pools_b_u2

Overview

OKL.OKL_SUBSIDY_POOLS_B is a transaction-data table in the Oracle E-Business Suite (EBS) Lease and Finance Management (OKL) schema that stores the definition of subsidy pools. In the leasing model, a subsidy pool aggregates budget or rate-support funding that is applied against lease contracts and transactions so that a lessor or vendor can subsidise rates, fees, or residuals. The _B suffix identifies this as the base table of an entity that has a translated counterpart, OKL_SUBSIDY_POOLS_TL, so that subsidy pool names and descriptions can be stored in multiple languages. The table is held in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes reside in APPS_TS_TX_IDX.

In Data Vault modelling terms, this table's structure is hub-leaning: it is a stable, uniquely keyed registry of business entities (subsidy pools) with descriptive and editable attributes. A designer mapping the EBS model into a Data Vault warehouse would most naturally treat the unique business key columns as a hub, with the mutable attributes (limits, budgets, decision status, and dates) suitable for one or more satellites, and the self-referencing parent pool as a recursive link candidate rather than a true link to a second hub.

Key Information Stored

The surrogate primary key is ID, supported by unique index OKL_SUBSIDY_POOLS_B_U1. A second unique index, OKL_SUBSIDY_POOLS_B_U2 (the object the user searched for), enforces the business key of the table on SUBSIDY_POOL_NAME, guaranteeing that every defined pool has a system-wide unique name. The mandatory operational columns include:

• ID — surrogate unique identifier for the subsidy pool; primary key.
• SUBSIDY_POOL_NAME — unique business name of the pool (business-key candidate via OKL_SUBSIDY_POOLS_B_U2).
• SUBSIDY_POOL_ID — parent subsidy pool identifier, enabling hierarchical (parent/child) subsidy pool structures.
• POOL_TYPE_CODE — classifies the pool (for example reporting versus budget-style pools).
• EFFECTIVE_FROM_DATE / EFFECTIVE_TO_DATE — validity window that controls which pool applies to a given transaction date.
• CURRENCY_CODE / CURRENCY_CONVERSION_TYPE — currency of the pool and the conversion rate type used for cross-currency subsidy calculations.
• DECISION_STATUS_CODE / DECISION_DATE — approval or rejection state and the corresponding decision date.
• REPORTING_POOL_LIMIT — limit defined for reporting-type pools.
• TOTAL_BUDGETS — total budget allocated to a budget subsidy pool.
• TOTAL_SUBSIDY_AMOUNT — cumulative subsidy amount consumed against the pool.
• OBJECT_VERSION_NUMBER — optimistic locking column used during concurrent updates.
• ATTRIBUTE_CATEGORY / ATTRIBUTE1–15 — descriptive flexfield storage.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical reporting centres on approved, in-force pools and their remaining capacity. Because TOTAL_SUBSIDY_AMOUNT tracks consumption against TOTAL_BUDGETS or REPORTING_POOL_LIMIT, the difference yields remaining budget, and the effective-date columns define which pools are live.

To list active pools positioned to consume remaining budget:

• SELECT sp.ID, sp.SUBSIDY_POOL_NAME, sp.CURRENCY_CODE, sp.TOTAL_BUDGETS, sp.TOTAL_SUBSIDY_AMOUNT, NVL(sp.TOTAL_SUBSIDY_AMOUNT,0) - NVL(sp.TOTAL_BUDGETS,0) AS REMAINING FROM OKL.OKL_SUBSIDY_POOLS_B sp WHERE sp.DECISION_STATUS_CODE = 'APPROVED' AND SYSDATE BETWEEN sp.EFFECTIVE_FROM_DATE AND NVL(sp.EFFECTIVE_TO_DATE, SYSDATE+1);

Lookups by name should exploit the unique index OKL_SUBSIDY_POOLS_B_U2:

• SELECT ID, POOL_TYPE_CODE, TOTAL_SUBSIDY_AMOUNT FROM OKL.OKL_SUBSIDY_POOLS_B WHERE SUBSIDY_POOL_NAME = :p_name;

To reconstruct pool hierarchies, self-join on the parent column:

• SELECT c.SUBSIDY_POOL_NAME AS child_pool, p.SUBSIDY_POOL_NAME AS parent_pool FROM OKL.OKL_SUBSIDY_POOLS_B c, OKL.OKL_SUBSIDY_POOLS_B p WHERE c.SUBSIDY_POOL_ID = p.ID;

Translated names are obtained by joining to OKL_SUBSIDY_POOLS_TL on ID and the current language. Flexfield attributes may be surfaced using the standard OKL descriptive flexfield views.

Related Objects

The table participates in the following documented relationships:

• OKL_SUBSIDY_POOLS_TL — translation table joined on OKL_SUBSIDY_POOLS_TL.ID = OKL_SUBSIDY_POOLS_B.ID for language-specific pool names and descriptions.
• OKL_SUBSIDY_POOL_BUDGETS_B — child budget records joined on SUBSIDY_POOL_ID = OKL_SUBSIDY_POOLS_B.ID, holding budget lines per pool.
• OKL_TRX_SUBSIDY_POOLS — transaction-to-pool association joined on SUBSIDY_POOL_ID = OKL_SUBSIDY_POOLS_B.ID, recording which pools apply to which transactions.
• OKL_SUBSIDY_POOLS_B (self-reference) — parent/child hierarchy via SUBSIDY_POOL_ID = ID.

Because OKL_SUBSIDY_POOLS_B is the master registry, any code path that assigns or consumes subsidy must resolve a pool through this table before writing the association and budget records above.