VC_GRPALC_SRCH

(SQL View)
 Index Back

Group Fund. Alloc. search view

This view is used as the search record for the component Group Funding Allocation.

SELECT OPR.OPRID , B.PNLGRPNAME , GA.VC_PLAN_ID , GA.VC_PAYOUT_PRD_ID , GA.GB_GROUP_ID , GA.CURRENCY_CD FROM PS_VC_GRP_ALLOC GA , PS_VC_PLAN_EFFDT PE , PS_GB_GROUP_TBL GT , PS_VC_PAYPRD_TBL PP , PS_VC_PLAN_TBL PT , PSOPRDEFN OPR , PS_VC_TOP_GROUP_VW TOPG , PS_VC_TREE_ALND_VW A , PS_GB_SECPNLG_TBL B , PS_GB_GROUP_TBL C , PS_VC_PAYPRD_TBL D , PS_VC_PLAN_EFFDT E WHERE A.GB_GROUP_ID = B.GB_GROUP_ID AND B.GB_GROUP_ID = C.GB_GROUP_ID AND D.VC_PLAN_ID = E.VC_PLAN_ID AND E.TREE_NAME = A.TREE_NAME AND E.EFFDT >= ( SELECT MAX(EFFDT) FROM PS_VC_PLAN_EFFDT VC_PLAN_EFFDT WHERE VC_PLAN_EFFDT.VC_PLAN_ID = E.VC_PLAN_ID AND VC_PLAN_EFFDT.EFFDT <= D.PRD_BGN_DT) AND E.EFF_STATUS = 'A' AND A.TREE_EFFDT <= D.PRD_END_DT AND A.TREE_EFFDT <= %CurrentDateIn AND A.TREE_EFFDT >= ( SELECT MAX(EFFDT) FROM PS_VC_TREE_DT_VW VC_TREE_DT_VW WHERE VC_TREE_DT_VW.TREE_NAME = E.TREE_NAME AND EFFDT <= D.PRD_BGN_DT) AND C.EFFDT=( SELECT MAX(EFFDT) FROM PS_GB_GROUP_TBL GB_GROUP_TBL WHERE GB_GROUP_TBL.GB_GROUP_ID=C.GB_GROUP_ID AND GB_GROUP_TBL.EFFDT<=%CurrentDateIn AND GB_GROUP_TBL.EFFDT<=D.PRD_END_DT) AND C.EFF_STATUS = 'A' AND B.EFFDT=( SELECT MAX(EFFDT) FROM PS_GB_SECPNLG_TBL GB_SECPNLG_TBL WHERE GB_SECPNLG_TBL.GB_GROUP_ID=B.GB_GROUP_ID AND GB_SECPNLG_TBL.EFFDT<=%CurrentDateIn AND GB_SECPNLG_TBL.EFFDT<=D.PRD_END_DT) AND GA.VC_PLAN_ID = PE.VC_PLAN_ID AND PE.VC_PLAN_ID = PT.VC_PLAN_ID AND PT.VC_PLAN_MEM_CRIT IN ('G','T') AND PT.VC_FUND_IND = 'Y' AND PE.VC_PLAN_ID = PP.VC_PLAN_ID AND GA.VC_PAYOUT_PRD_ID = PP.VC_PAYOUT_PRD_ID AND GA.GB_GROUP_ID = GT.GB_GROUP_ID AND PE.EFFDT = ( SELECT MAX(PE2.EFFDT) FROM PS_VC_PLAN_EFFDT PE2 WHERE PE.VC_PLAN_ID = PE2.VC_PLAN_ID AND PE2.EFFDT <= PP.PRD_END_DT ) AND PE.EFF_STATUS = 'A' AND GT.EFFDT = ( SELECT MAX(GT2.EFFDT) FROM PS_GB_GROUP_TBL GT2 WHERE GT.GB_GROUP_ID = GT2.GB_GROUP_ID AND GT2.EFFDT <= PP.PRD_END_DT ) AND GT.EFF_STATUS = 'A' AND OPR.OPRID = B.GB_OPRID AND TOPG.TREE_NAME = PE.TREE_NAME AND TOPG.TREE_EFFDT = ( SELECT MAX(TOPG2.TREE_EFFDT) FROM PS_VC_TOP_GROUP_VW TOPG2 WHERE TOPG.TREE_NAME = TOPG2.TREE_NAME AND TOPG2.TREE_EFFDT <= PP.PRD_END_DT ) AND ( ( ( ( TOPG.GB_GROUP_ID <> GA.GB_GROUP_ID AND OPR.EMPLID = GT.APPROVER_ID ) OR OPR.EMPLID = GT.MANAGER_ID ) AND EXISTS ( SELECT 'X' FROM PS_VC_AWARD AWD WHERE AWD.VC_PLAN_ID = GA.VC_PLAN_ID AND AWD.VC_PAYOUT_PRD_ID = GA.VC_PAYOUT_PRD_ID AND AWD.GB_GROUP_ID = GA.GB_GROUP_ID AND AWD.EMPLID = GT.MANAGER_ID AND AWD.VC_AWARD_STATUS IN ('A','C','P','S','T', 'D') ) ) OR ( TOPG.GB_GROUP_ID = GA.GB_GROUP_ID AND OPR.EMPLID = GT.APPROVER_ID ) OR (OPR.EMPLID = GT.MANAGER_ID AND NOT EXISTS ( SELECT 'X' FROM PS_VC_PLAN_MEM PM WHERE PM.VC_PLAN_ID = GA.VC_PLAN_ID AND PM.VC_PAYOUT_PRD_ID = GA.VC_PAYOUT_PRD_ID AND PM.GB_GROUP_ID = GA.GB_GROUP_ID AND PM.EMPLID = GT.MANAGER_ID) ) ) AND D.VC_PLAN_ID = GA.VC_PLAN_ID AND D.VC_PAYOUT_PRD_ID = GA.VC_PAYOUT_PRD_ID AND B.GB_GROUP_ID = GA.GB_GROUP_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 PNLGRPNAME Character(18) VARCHAR2(18) NOT NULL Component Name
3 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID
4 VC_PAYOUT_PRD_ID Character(10) VARCHAR2(10) NOT NULL Payout Period ID
5 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code