VIEW: APPS.BEN_CWB_STOCK_TOTAL_POS_SUM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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