View: BEN_CWB_STOCK_TOTAL_POS_SUM_V

Product:	BEN - Advanced Benefits
Description:	Total Stock Positions Summary.
Implementation/DBA Data:	APPS.BEN_CWB_STOCK_TOTAL_POS_SUM_V

View Text

SELECT PPF.PERSON_ID
, PPF.EMPLOYEE_NUMBER
, PPF.BUSINESS_GROUP_ID
, PPF.FULL_NAME FULL_NAME
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME BRIEF_NAME
, CSO.TRADING_SYMBOL
, SUM(CSO.CURRENT_SHARES_OUTSTANDING) CURRENT_SHARES_OUTSTANDING
, SUM(CSO.CURRENT_SHARE_PRICE * CSO.CURRENT_SHARES_OUTSTANDING) CURRENT_SHARES_OUTST_EXER_VAL
, SUM(CSO.VESTED_SHARES ) VESTED_SHARES
, SUM(CSO.CURRENT_SHARE_PRICE * CSO.VESTED_SHARES) VESTED_SHARES_EXER_VAL
, SUM(CSO.UNVESTED_SHARES ) UNVESTED_SHARES
, SUM(CSO.CURRENT_SHARE_PRICE * CSO.UNVESTED_SHARES) UNVESTED_SHARES_EXER_VAL
, SUM(CSO.EXERCISABLE_SHARES) EXERCISABLE_SHARES
, SUM(CSO.CURRENT_SHARE_PRICE * CSO.EXERCISABLE_SHARES) EXERCISABLE_SHARES_EXER_VAL
, SUM(CSO.EXERCISED_SHARES ) EXERCISED_SHARES
, SUM(CSO.CANCELLED_SHARES ) CANCELLED_SHARES
, SUM(CSO.GRANT_SHARES) GRANTED_SHARES
, SUM(GRANT_PRICE)/COUNT(GRANT_PRICE) GRANT_PRICE
, ASG.SUPERVISOR_ID
FROM BEN_CWB_STOCK_OPTN_DTLS CSO
, PER_ALL_PEOPLE_F PPF
, PER_ALL_ASSIGNMENTS_F ASG
WHERE PPF.PERSON_ID = ASG.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND ASG.PRIMARY_FLAG = 'Y'
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ( PPF.PERSON_ID = CSO.PERSON_ID OR (PPF.BUSINESS_GROUP_ID = CSO.BUSINESS_GROUP_ID
AND PPF.EMPLOYEE_NUMBER = CSO.EMPLOYEE_NUMBER) ) GROUP BY PPF.PERSON_ID
, PPF.EMPLOYEE_NUMBER
, PPF.BUSINESS_GROUP_ID
, PPF.FULL_NAME
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, CSO.TRADING_SYMBOL
, ASG.SUPERVISOR_ID ORDER BY PPF.FULL_NAME

Columns