ST_PROC_RULE_VW(SQL View) |
Index Back |
|---|---|
Stock Action Process ViewThis view determines who to process, what the Stock Action Rules are and gathers information for each Grant that is Active, Pending and which hasn't been Canceled. |
| SELECT G.EMPLID , G.STOCK_ID , G.GRANT_NBR , G.EMPL_RCD , G.GRANT_DT , G.STOCK_PLAN , G.ST_OPTION_TYPE , G.VEST_DEFER_DT , G.STOCK_ACTION , G.STOCK_ACTION_DT , G.EXER_SUSPND_BGN_DT , G.EXER_SUSPND_END_DT , H.STOCK_ACTION , H.STOCK_ACTION_DT , R.VEST_IMMEDIATE , R.EXPIRE_GRACE_PD_MM , R.EXPIRE_GRACE_PD_DD , R.ISO_GRACE_PD_TYPE , R.EXER_SUSPEND_TYPE , R.EXER_SUSPEND_PD_MM , R.EXER_SUSPEND_PD_DD , R.VEST_SUSPEND , R.VEST_DEFR_GRACE_MM , R.VEST_DEFR_GRACE_DD , R.VEST_DEFER_SVC_MM , R.VEST_DEFER_SVC_DD , D.VEST_EFFDT , D.SEQ_NUM , D.VEST_SCHED_ID , G.ST_CANCEL_DT FROM PS_ST_ACTN_PRC_TBL H , PS_ST_ACTN_RULE R , PS_ST_GRANT G , PS_ST_GRANT_VEST D WHERE H.EMPLID = G.EMPLID AND H.EMPL_RCD = G.EMPL_RCD AND H.EMPLID = D.EMPLID AND H.STOCK_ID = G.STOCK_ID AND H.STOCK_ID = R.STOCK_ID AND H.STOCK_ID = D.STOCK_ID AND (H.GRANT_NBR = G.GRANT_NBR OR H.GRANT_NBR = ' ') AND G.GRANT_NBR = D.GRANT_NBR AND (H.ST_OPTION_TYPE = G.ST_OPTION_TYPE OR H.ST_OPTION_TYPE = ' ') AND G.GRANT_STATUS IN ('A','P') AND G.ST_CANCEL_DT IS NULL AND H.STOCK_ACTION = R.STOCK_ACTION AND R.STOCK_PLAN = G.STOCK_PLAN AND R.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ST_ACTN_RULE WHERE STOCK_ID = R.STOCK_ID AND STOCK_PLAN = R.STOCK_PLAN AND EFFDT <= H.STOCK_ACTION_DT) AND D.VEST_EFFDT = ( SELECT MAX(VEST_EFFDT) FROM PS_ST_GRANT_VEST WHERE EMPLID = D.EMPLID AND STOCK_ID = D.STOCK_ID AND GRANT_NBR = D.GRANT_NBR) AND D.SEQ_NUM = ( SELECT MAX(SEQ_NUM) FROM PS_ST_GRANT_VEST WHERE EMPLID = D.EMPLID AND STOCK_ID = D.STOCK_ID AND GRANT_NBR = D.GRANT_NBR AND VEST_EFFDT = D.VEST_EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | STOCK_ID |
Character(10) | VARCHAR2(10) NOT NULL | Stock ID |
| 3 | GRANT_NBR |
Character(10) | VARCHAR2(10) NOT NULL | System assigned grant number used to uniquely identify the grant record. |
| 4 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 5 | GRANT_DT | Date(10) | DATE NOT NULL | Grant Date |
| 6 | STOCK_PLAN | Character(10) | VARCHAR2(10) NOT NULL | Stock Plan |
| 7 | ST_OPTION_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
Option Type
ISAR=ISO-Stock Appreciation Right ISO=Incentive Stock Option NQ=Non-Qualified NSAR=NQ-Stock Appreciation Right RSA=Restricted Stock Award |
| 8 | VEST_DEFER_DT | Date(10) | DATE | The date the employee must return from leave to avoid vesting deferral. |
| 9 | PRIOR_STOCK_ACTION | Character(3) | VARCHAR2(3) NOT NULL | Stock Action |
| 10 | PRIOR_STCK_ACTN_DT | Date(10) | DATE | Date the stock action WAS effective. |
| 11 | EXER_SUSPND_BGN_DT | Date(10) | DATE | No exercises will be allowed on or after this date. |
| 12 | EXER_SUSPND_END_DT | Date(10) | DATE | Used in conjunction with Exer_Suspnd_Bgn_Dt. |
| 13 | STOCK_ACTION | Character(3) | VARCHAR2(3) NOT NULL | Stock Action |
| 14 | STOCK_ACTION_DT | Date(10) | DATE | Date the stock action is effective. |
| 15 | VEST_IMMEDIATE | Character(1) | VARCHAR2(1) NOT NULL |
Vest Immediate
Y/N Table Edit Default Value: N |
| 16 | EXPIRE_GRACE_PD_MM | Number(3,0) | SMALLINT NOT NULL |
Expire Grace Period
Default Value: 0 |
| 17 | EXPIRE_GRACE_PD_DD | Number(2,0) | SMALLINT NOT NULL |
Expire Grace Period: Days
Default Value: 0 |
| 18 | ISO_GRACE_PD_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
ISO Grace Period Type
D=Disability N=None T=Termination |
| 19 | EXER_SUSPEND_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Suspend Exercise Rule
D=Defined Period N=None R=Until Return from Leave |
| 20 | EXER_SUSPEND_PD_MM | Number(3,0) | SMALLINT NOT NULL | Exersise Suspend Months |
| 21 | EXER_SUSPEND_PD_DD | Number(2,0) | SMALLINT NOT NULL | Exercise Suspend: Days |
| 22 | VEST_SUSPEND | Character(1) | VARCHAR2(1) NOT NULL |
Suspend Vesting
Y/N Table Edit |
| 23 | VEST_DEFR_GRACE_MM | Number(3,0) | SMALLINT NOT NULL | Vest Defer Grace Months |
| 24 | VEST_DEFR_GRACE_DD | Number(2,0) | SMALLINT NOT NULL | Vest Defer Grace Days |
| 25 | VEST_DEFER_SVC_MM | Number(3,0) | SMALLINT NOT NULL | Vest Defer Service: Months |
| 26 | VEST_DEFER_SVC_DD | Number(2,0) | SMALLINT NOT NULL | Vest Defer Service: Days |
| 27 | VEST_EFFDT | Date(10) | DATE | Vesting effective date. |
| 28 | SEQ_NUM | Number(3,0) | SMALLINT NOT NULL | Sequence |
| 29 | VEST_SCHED_ID | Character(10) | VARCHAR2(10) NOT NULL | The ID that identifies this vesting schedule. |
| 30 | ST_CANCEL_DT | Date(10) | DATE | Cancellation Date |