Results for “grant_shares”

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

Overview

BEN_CWB_STOCK_TOTAL_POS_DTL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) product family. Its documented description is "Total Stock Positions Detail," and it exposes grant-level stock position information for employees, including grant attributes, share quantities, valuation data, and vesting or exercise status. The view is a denormalized, presentation-oriented construct: it does not store data itself but projects a curated set of columns drawn from stock option detail records joined to current employee and assignment records.

In the context of EBS 12.1.1 and 12.2.2, the view serves reporting, extract, and integration purposes. Compensation and benefits administrators use it to review aggregate stock holdings and grant activity per person, while downstream interfaces and custom reports query it as a stable column interface rather than joining the underlying transactional and HR tables directly. Because it resolves current effective-dated person and assignment rows at query time, results always reflect the employee's present primary assignment and supervisor hierarchy. The view is documented as VALID, and its identifying columns such as EMPLOYEE_NUMBER, FULL_NAME, and BRIEF_NAME support name-based search and display.

Underlying Base Objects

ETRM metadata documents the view as being defined over three referenced base objects, each resolved through APPS synonyms:

• BEN_CWB_STOCK_OPTN_DTLS — the stock option detail table that supplies grant-level data (grant number, type, date, shares, prices, vesting and exercise quantities, symbol, expiration, reason code, class, and miscellaneous attributes).
• PER_ALL_PEOPLE_F — the effective-dated person table that supplies EMPLOYEE_NUMBER, FULL_NAME, BRIEF_NAME (concatenation of FIRST_NAME and LAST_NAME), and PERSON_ID.
• PER_ALL_ASSIGNMENTS_F — the effective-dated assignment table that supplies the primary assignment context and SUPERVISOR_ID.

The join logic links person to assignment on PERSON_ID with PRIMARY_FLAG = 'Y', restricts both HR tables to rows where SYSDATE falls between the effective start and end dates, and matches stock option details either by PERSON_ID or, as a fallback, by BUSINESS_GROUP_ID combined with EMPLOYEE_NUMBER. Output is ordered by FULL_NAME descending and then GRANT_DATE.

Key Columns

• EMPLOYEE_NUMBER, FULL_NAME, BRIEF_NAME, PERSON_ID — employee identity. The user's search term, brief_name, is derived as FIRST_NAME concatenated with LAST_NAME and is commonly used for compact display or name searches.
• GRANT_NUMBER, GRANT_NAME, GRANT_TYPE, GRANT_DATE — grant identification and classification.
• GRANT_SHARES, GRANT_PRICE, VALUE_AT_GRANT — original award size, strike or grant price, and grant-date valuation.
• CURRENT_SHARE_PRICE, CURRENT_SHARES_OUTSTANDING — present market price and remaining outstanding shares.
• VESTED_SHARES, UNVESTED_SHARES, EXERCISABLE_SHARES, EXERCISED_SHARES, CANCELLED_SHARES — position status breakdown across the vesting and exercise lifecycle.
• TRADING_SYMBOL, EXPIRATION_DATE, REASON_CODE, CLASS, MISC — instrument and grant metadata.
• SUPERVISOR_ID — supervisory hierarchy reference sourced from the primary assignment.

Common Use Cases and Queries

Typical scenarios include total stock position reporting by employee or supervisor, grant-level reconciliation of vested versus unvested shares, and extracts for compensation statements or regulatory reporting. The following sample retrieves brief name and grant detail for a single employee:

SELECT brief_name, grant_number, grant_date, grant_shares, vested_shares, unvested_shares
FROM apps.ben_cwb_stock_total_pos_dtl_v
WHERE employee_number = :p_employee_number
ORDER BY grant_date;

A second pattern aggregates outstanding and exercisable positions by supervisor:

SELECT supervisor_id, SUM(current_shares_outstanding) outstanding, SUM(exercisable_shares) exercisable
FROM apps.ben_cwb_stock_total_pos_dtl_v
GROUP BY supervisor_id;

Because effective-dated filtering uses SYSDATE, historical point-in-time reporting requires custom queries against the base tables rather than this view.