|
SELECT OPR.OPRID , TP.PNLGRPNAME , GA.VC_PLAN_ID , GA.VC_PAYOUT_PRD_ID , GA.GB_GROUP_ID FROM PS_VC_GRP_AWARD GA , PS_VC_CHK_TPGRP_VW TP , PS_GB_GROUP_TBL GT , PSOPRDEFN OPR , PS_VC_PLAN_EFFDT PE , PS_VC_PAYPRD_TBL PP , PS_VC_TREE_NODE_VW TN WHERE GA.VC_PLAN_ID = TP.VC_PLAN_ID AND GA.VC_PAYOUT_PRD_ID = TP.VC_PAYOUT_PRD_ID AND GA.GB_GROUP_ID = TP.GB_GROUP_ID AND GT.GB_GROUP_ID = GA.GB_GROUP_ID AND GT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GB_GROUP_TBL GT3 WHERE GT3.GB_GROUP_ID = GT.GB_GROUP_ID AND GT3.EFFDT <= PP.PRD_END_DT ) AND OPR.OPRID = TP.GB_OPRID AND PP.VC_PLAN_ID = GA.VC_PLAN_ID AND PP.VC_PAYOUT_PRD_ID = GA.VC_PAYOUT_PRD_ID AND PE.VC_PLAN_ID = PP.VC_PLAN_ID AND PE.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VC_PLAN_EFFDT PE1 WHERE PE1.VC_PLAN_ID = PE.VC_PLAN_ID AND PE1.EFF_STATUS = 'A' AND PE1.EFFDT <= PP.PRD_END_DT ) AND TN.TREE_NAME = PE.TREE_NAME AND TN.TREE_EFFDT = ( SELECT MAX (TREE_EFFDT) FROM PS_VC_TREE_NODE_VW TN1 WHERE TN1.TREE_NAME = PE.TREE_NAME AND TN1.TREE_EFFDT <= PE.EFFDT ) AND TN.GB_CHILD_GROUP_ID = GA.GB_GROUP_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.EMPLID = GT.MANAGER_ID AND AWD.VC_AWARD_STATUS IN ('A','C','D','P','S','T') ) AND OPR.EMPLID = GT.MANAGER_ID ) OR (NOT EXISTS ( SELECT 'X' FROM PS_VC_PLAN_MEM PM1 WHERE PM1.VC_PLAN_ID = GA.VC_PLAN_ID AND PM1.VC_PAYOUT_PRD_ID = GA.VC_PAYOUT_PRD_ID AND PM1.GB_GROUP_ID = GT.GB_GROUP_ID AND PM1.EMPLID = GT.MANAGER_ID ) AND OPR.EMPLID = GT.MANAGER_ID ) OR ( PE.VC_APPROVAL_IND = 'Y' AND OPR.EMPLID = GT.APPROVER_ID ) OR ( PE.VC_APPROVAL_IND = 'N' AND EXISTS ( SELECT 'X' FROM PS_GB_GROUP_TBL GT1 WHERE GT1.GB_GROUP_ID = TN.GB_GROUP_ID AND GT1.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GB_GROUP_TBL GT2 WHERE GT2.GB_GROUP_ID = GT1.GB_GROUP_ID AND GT2.EFFDT <= PP.PRD_END_DT ) AND GT1.MANAGER_ID = OPR.EMPLID ) ) OR ( PE.VC_APPROVAL_IND = 'N' AND TN.GB_GROUP_ID = ' ' AND OPR.EMPLID = GT.APPROVER_ID ) )
|
OPRID