SELECT DISTINCT CA.CONTRACT_NUM , EX.EMPLID , ( SELECT FIRST_NAME %Concat ' ' %Concat LAST_NAME FROM PS_PERSONAL_DATA PERS WHERE PERS.EMPLID = EX.EMPLID), PROJ_RES.SHEET_ID , SUM(PROJ_RES.RESOURCE_AMOUNT) , PROJ_RES.CURRENCY_CD , PROJ_RES.ACCOUNTING_DT , EX.SHEET_NAME , ( SELECT DESCR FROM PS_EX_PURPOSE_TBL WHERE SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME = 'EX_PURPOSE_TBL' AND SETCNTRLVALUE = PROJ_RES.BUSINESS_UNIT_GL AND BUSINESS_PURPOSE = EX.BUSINESS_PURPOSE)), PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, ( SELECT DESCR FROM PS_PROJECT WHERE BUSINESS_UNIT = PROJ_RES.BUSINESS_UNIT AND PROJECT_ID = PROJ_RES.PROJECT_ID), %DecMult(%DecDiv(SUM(PROJ_RES.RESOURCE_AMOUNT), RT.RATE_DIV), RT.RATE_MULT), CA.CURRENCY_CD FROM PS_CA_CONTR_HDR CA, PS_CA_DETAIL CA_DTL, PS_CA_DETAIL_PROJ DTL_PROJ, PS_PROJ_RESOURCE PROJ_RES, PS_EX_SHEET_HDR EX, PS_RT_DFLT_VW RT WHERE CA.CONTRACT_NUM = CA_DTL.CONTRACT_NUM AND CA_DTL.CONTRACT_NUM = DTL_PROJ.CONTRACT_NUM AND CA_DTL.CONTRACT_LINE_NUM = DTL_PROJ.CONTRACT_LINE_NUM AND DTL_PROJ.BUSINESS_UNIT_PC = PROJ_RES.BUSINESS_UNIT AND DTL_PROJ.PROJECT_ID = PROJ_RES.PROJECT_ID AND DTL_PROJ.ACTIVITY_ID = PROJ_RES.ACTIVITY_ID AND PROJ_RES.SHEET_ID = EX.SHEET_ID AND PROJ_RES.SYSTEM_SOURCE = 'BEX' AND RT.RT_TYPE = CA.RT_TYPE AND PROJ_RES.CURRENCY_CD = RT.FROM_CUR AND CA.CURRENCY_CD = RT.TO_CUR AND RT.EFFDT = ( SELECT MAX(RT_ED.EFFDT) FROM PS_RT_DFLT_VW RT_ED WHERE RT.FROM_CUR = RT_ED.FROM_CUR AND RT.TO_CUR = RT_ED.TO_CUR AND RT.RT_TYPE = RT_ED.RT_TYPE AND RT_ED.EFFDT <= %CurrentDateIn) GROUP BY CA.CONTRACT_NUM, EX.EMPLID, PROJ_RES.SHEET_ID, PROJ_RES.CURRENCY_CD, PROJ_RES.ACCOUNTING_DT, EX.SHEET_NAME,PROJ_RES.BUSINESS_UNIT_GL, EX.BUSINESS_PURPOSE, PROJ_RES.BUSINESS_UNIT, PROJ_RES.PROJECT_ID, RT.RATE_DIV, RT.RATE_MULT, CA.CURRENCY_CD
|