|
SELECT GB_GROUP_TBL.GB_GROUP_ID , GB_GROUP_TBL.MANAGER_ID , SUM(VC_PLAN_MEM.VC_TARGET_VALUE) FROM PS_VC_PLAN_MEM VC_PLAN_MEM , 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_PLAN_MEM.VC_PLAN_ID = VC_PLAN_TBL.VC_PLAN_ID AND VC_PLAN_TBL.VC_PLAN_ID = PAYOUT_PRD_TBL.VC_PLAN_ID AND VC_PLAN_MEM.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_PLAN_MEM.EMPLID GROUP BY GB_GROUP_TBL.GB_GROUP_ID , GB_GROUP_TBL.MANAGER_ID
|