|
SELECT A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , A.SUB_PROP_NBR , A.BUDGET_ID , A.BUDGET_PERIOD_GM , A.BUDGET_LN , A.BUDGET_CATEGORY , A.DESCR , SUM(A.SPONSOR_AMT) , B.FA_BASE_ID , C.DESCR , D.FA_RATE_PCT , E.DESCR50 FROM PS_GM_BUD_MDTL_VW9 A , PS_GM_BUD_FA_HDR B , PS_GM_FA_BASE_DTL C , PS_GM_BUD_FA_RATE D , PS_GM_FA_RATE_TYPE E WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.PROPOSAL_ID = B.PROPOSAL_ID AND A.VERSION_ID=B.VERSION_ID AND A.SUB_PROP_NBR=B.SUB_PROP_NBR AND A.BUDGET_ID=B.BUDGET_ID AND B.FA_BASE_ID = C.FA_BASE_ID AND B.BUSINESS_UNIT=D.BUSINESS_UNIT AND B.PROPOSAL_ID = D.PROPOSAL_ID AND B.VERSION_ID=D.VERSION_ID AND B.SUB_PROP_NBR=D.SUB_PROP_NBR AND B.BUDGET_ID=D.BUDGET_ID AND B.FA_RATE_TYPE=D.FA_RATE_TYPE AND C.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.SETCNTRLVALUE = A.BUSINESS_UNIT AND G.RECNAME ='GM_FA_BASE_DTL') AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_GM_FA_BASE_DTL C1 WHERE C.SETID = C1.SETID AND C.FA_BASE_ID = C1.FA_BASE_ID AND C1.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND D.RATE_EFFDT = ( SELECT MAX(D1.RATE_EFFDT) FROM PS_GM_BUD_FA_RATE D1 WHERE D.BUSINESS_UNIT = D1.BUSINESS_UNIT AND D.PROPOSAL_ID = D1.PROPOSAL_ID AND D.VERSION_ID = D1.VERSION_ID AND D.SUB_PROP_NBR = D1.SUB_PROP_NBR AND D.BUDGET_ID = D1.BUDGET_ID AND D.FA_RATE_TYPE = D1.FA_RATE_TYPE) AND C.SETID = E.SETID AND E.FA_RATE_TYPE = B.FA_RATE_TYPE AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_GM_FA_RATE_TYPE E1 WHERE E1.SETID = E.SETID AND E1.FA_RATE_TYPE = E.FA_RATE_TYPE AND E1.EFFDT <= %CurrentDateIn) GROUP BY A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , A.SUB_PROP_NBR , A.BUDGET_ID , A.BUDGET_PERIOD_GM , A.BUDGET_LN , A.BUDGET_CATEGORY , A.DESCR , B.FA_BASE_ID , C.DESCR , D.FA_RATE_PCT, E.DESCR50
|