Search Results oki_renewal_aging_u1

Overview

OKI.OKI_RENEWAL_AGING is a transactional summary table in the Oracle E-Business Suite Service Contracts (OKI) schema. It holds aggregated renewal aging information by accounting period, operating unit, and customer, allowing contract administrators and financial analysts to review the value of renewing contracts grouped into aging buckets without scanning the underlying contract lines at query time. The table resides in the APPS_TS_TX_DATA tablespace and is registered in FND Design Data as OKI.OKI_RENEWAL_AGING, marking it as a recognized dictionary object within the ETRM (E-Business Suite Tables and Relationships Model) reference for releases 12.1.1 and 12.2.2.

From a dimensional modeling perspective the heuristic Data Vault classification is standalone. The table does not behave as a classic hub, link, or satellite; instead it functions as a periodic snapshot or derived fact holding pre-computed aging measures. Modelers may treat it as a snapshot satellite keyed by the natural combination of period, operating unit, customer, and category code, with ID serving as the surrogate key. The only documented foreign key targets FND_SECURITY_GROUPS through SECURITY_GROUP_ID, confirming the multi-org/security-group enforcement pattern typical of OKI tables.

Key Information Stored

The table contains 18 documented columns. The most significant are:

• ID — Surrogate primary key (OKI_RENEWAL_AGING_PK), the internal row identifier.
• PERIOD_NAME, PERIOD_SET_NAME, PERIOD_TYPE — General Ledger period context: the period name, the period set defining valid periods, and the period type (e.g., monthly, quarterly) for which aging was computed.
• AUTHORING_ORG_ID, AUTHORING_ORG_NAME — Operating unit in which the contract was authored, plus its denormalized name for reporting.
• CUSTOMER_PARTY_ID, CUSTOMER_NAME — HZ party identifier of the contract customer and its descriptive name.
• SCS_CODE — The contract category (sub-class) code, enabling aggregation by service line or contract classification.
• AGE_GROUP1 through AGE_GROUP4 — The four renewal aging buckets. Each holds the monetary value of contracts falling into the corresponding aging interval, providing the core fact measures.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent Manager audit columns identifying the program and request that populated the row.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing operating unit and responsibility-based data security.

Two unique indexes exist: OKI_RENEWAL_AGING_U1 on ID (surrogate key) and OKI_RENEWAL_AGING_U2 on PERIOD_NAME, PERIOD_SET_NAME, AUTHORING_ORG_ID, CUSTOMER_PARTY_ID, and SCS_CODE. The U2 index is the true business-key candidate, proving one row per period/operating-unit/customer/category combination.

Common Use Cases and Queries

Typical uses include renewal aging dashboards, collections and pipeline reporting, and reconciliation of contract value by operating unit. A representative query groups the aging buckets by operating unit and period:

• SELECT authoring_org_name, period_name, SUM(age_group1), SUM(age_group2), SUM(age_group3), SUM(age_group4) FROM oki.oki_renewal_aging WHERE period_name = :p_period GROUP BY authoring_org_name, period_name;
• Drill by customer: join CUSTOMER_PARTY_ID to HZ_PARTIES to obtain contact detail, or filter on SCS_CODE to isolate a contract category.
• Audit traceability: query REQUEST_ID and PROGRAM_UPDATE_DATE to confirm which concurrent program run produced a given snapshot.
• Security-scoped reporting: constrain rows using SECURITY_GROUP_ID through FND_SECURITY_GROUPS to match the responsibility's operating unit.

Because the table is a pre-aggregated snapshot, most production queries are read-only; the row set is normally refreshed by an OKI concurrent program rather than by direct DML.

Related Objects

• FND_SECURITY_GROUPS — referenced by the SECURITY_GROUP_ID foreign key; drives multi-org security filtering.
• OKI_RENEWAL_AGING_PK / OKI_RENEWAL_AGING_U1 / OKI_RENEWAL_AGING_U2 — primary and unique indexes enforcing identity and uniqueness on ID and on the period/org/customer/category combination.
• HZ_PARTIES / HZ_CUST_ACCOUNTS — standard join targets for CUSTOMER_PARTY_ID to resolve customer name and account context.
• GL_PERIODS / FND_PERIOD_SETS — resolve PERIOD_NAME, PERIOD_SET_NAME, and PERIOD_TYPE against General Ledger definitions.
• OKI_CONTRACTS (OKC_K_HEADERS_B and related OKC tables) — the source contract structures aged by this summary table.
• FND_CONCURRENT_REQUESTS / FND_CONCURRENT_PROGRAMS — join on REQUEST_ID and PROGRAM_ID to trace the populating concurrent run.