|
SELECT CH.VENDOR_SETID , CH.VENDOR_ID , R.TO_CUR , ' ' , L.BUSINESS_UNIT , L.TXN_CURRENCY_CD , SUM(L.MERCHANDISE_AMT) , SUM(%Round(%DecMult(%DecDiv(L.MERCHANDISE_AMT, R.RATE_DIV), R.RATE_MULT), C.DECIMAL_POSITIONS)) , SUM(CH.AMT_CNTRCT_MAX - CH.AMT_RELSD_OITM_CUM) FROM PS_CNTRCT_HDR CH , PS_VOUCHER H , PS_VOUCHER_LINE L , PS_SET_CNTRL_REC S , PS_RT_RATE_TBL R , PS_CURRENCY_CD_TBL C WHERE CH.CNTRCT_PROC_OPT IN ('AP', 'RPOV') AND CH.VERSION_STATUS = 'C' AND CH.AMT_CNTRCT_MAX > 0 AND S.SETCNTRLVALUE = L.BUSINESS_UNIT AND S.RECNAME = 'CNTRCT_HDR' AND CH.SETID = S.SETID AND CH.CNTRCT_ID = L.CNTRCT_ID AND CH.VENDOR_SETID = H.VENDOR_SETID AND CH.VENDOR_ID = H.VENDOR_ID AND H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.VOUCHER_ID = L.VOUCHER_ID AND R.RT_RATE_INDEX = ( SELECT RT_RATE_INDEX FROM PS_RT_INDEX_TBL WHERE DEFAULT_INDEX = 'Y') AND R.TERM = 0 AND R.FROM_CUR = L.TXN_CURRENCY_CD AND R.RT_TYPE = H.RT_TYPE AND R.EFFDT = ( SELECT MAX(R_ED.EFFDT) FROM PS_RT_RATE_TBL R_ED WHERE R.RT_RATE_INDEX = R_ED.RT_RATE_INDEX AND R.TERM = R_ED.TERM AND R.FROM_CUR = R_ED.FROM_CUR AND R.TO_CUR = R_ED.TO_CUR AND R.RT_TYPE = R_ED.RT_TYPE AND R_ED.EFFDT <= %CurrentDateIn) AND C.CURRENCY_CD = R.TO_CUR AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_CURRENCY_CD_TBL C2 WHERE C2.CURRENCY_CD = C.CURRENCY_CD AND C2.EFFDT <= %CurrentDateIn) GROUP BY CH.VENDOR_SETID, CH.VENDOR_ID, R.TO_CUR, L.BUSINESS_UNIT, L.TXN_CURRENCY_CD
|