GRP_ALLOC3_VW

(SQL View)
 Index Back

Group Allocation view

This view is used to get managers' approved award values, if any.

SELECT GB_GROUP_TBL.GB_GROUP_ID , GB_GROUP_TBL.MANAGER_ID , VC_AWARD.VC_ACTUAL_AWD_AMT , VC_AWARD.VC_ACTUAL_AWD_UNIT FROM PS_VC_AWARD VC_AWARD , PS_VC_PLAN_TBL VC_PLAN_TBL , PS_VC_PLAN_EFFDT VC_PLAN_EFFDT ,PS_VC_PAYPRD_TBL PAYOUT_PRD_TBL , PSTREENODE TREENODE , PS_GB_GROUP_TBL GB_GROUP_TBL WHERE VC_AWARD.VC_PLAN_ID = VC_PLAN_TBL.VC_PLAN_ID AND VC_PLAN_TBL.VC_PLAN_ID = PAYOUT_PRD_TBL.VC_PLAN_ID AND VC_AWARD.VC_PAYOUT_PRD_ID = PAYOUT_PRD_TBL.VC_PAYOUT_PRD_ID AND VC_PLAN_TBL.VC_PLAN_ID = VC_PLAN_EFFDT.VC_PLAN_ID AND VC_PLAN_EFFDT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VC_PLAN_EFFDT VC_PLAN_EFFDT2 WHERE VC_PLAN_EFFDT.VC_PLAN_ID = VC_PLAN_EFFDT2.VC_PLAN_ID AND VC_PLAN_EFFDT.EFF_STATUS = 'A' AND VC_PLAN_EFFDT.EFFDT <= PAYOUT_PRD_TBL.PRD_END_DT) AND VC_PLAN_EFFDT.TREE_NAME = TREENODE.TREE_NAME AND TREENODE.EFFDT = ( SELECT MAX(EFFDT) FROM PSTREENODE TREENODE1 WHERE TREENODE1.SETID = TREENODE.SETID AND TREENODE1.SETCNTRLVALUE= TREENODE.SETCNTRLVALUE AND TREENODE1.TREE_NAME = TREENODE.TREE_NAME AND TREENODE1.EFFDT <= VC_PLAN_EFFDT.EFFDT AND TREENODE1.TREE_BRANCH = TREENODE.TREE_BRANCH AND TREENODE1.TREE_NODE_NUM = TREENODE.TREE_NODE_NUM AND TREENODE1.TREE_NODE = TREENODE.TREE_NODE) AND GB_GROUP_TBL.GB_GROUP_ID = TREENODE.TREE_NODE AND GB_GROUP_TBL.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GB_GROUP_TBL GB_GROUP_TBL WHERE GB_GROUP_TBL.GB_GROUP_ID = GB_GROUP_TBL.GB_GROUP_ID AND GB_GROUP_TBL.EFFDT <= TREENODE.EFFDT) AND GB_GROUP_TBL.MANAGER_ID = VC_AWARD.EMPLID AND VC_AWARD.VC_AWARD_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
2 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
3 VC_ACTUAL_AWD_AMT Number(19,6) DECIMAL(18,6) NOT NULL Actual Award as amount
4 VC_ACTUAL_AWD_UNIT Number(15,6) DECIMAL(14,6) NOT NULL Actual Award