CREATE OR REPLACE VIEW VW_SH_CUST_MONEY_RANK AS SELECT M.ORG_ID,M.CUST_ID, TO_CHAR(M.SALES_DATE,'YYYYMM') Y_MONTH, M.PAY_CURR, SUM(D.MONEY) MONEY, ROUND(SUM(D.MONEY*NVL(GG_PO.GF_CURR_RATE(M.ORG_ID,M.PAY_CURR,'USD',TO_CHAR(M.SALES_DATE,'YYYYMM')),0)),2) U_MONEY, rank() over (PARTITION BY TO_CHAR(M.SALES_DATE,'YYYYMM') order by SUM(D.MONEY*NVL(GG_PO.GF_CURR_RATE(M.ORG_ID,M.PAY_CURR,'USD',TO_CHAR(M.SALES_DATE,'YYYYMM')),0)) desc NULLS LAST) rank FROM SH_SALES_M M,SH_SALES_D D,SO_ORD_M SO WHERE M.ORG_ID=D.ORG_ID AND M.SALES_ID=D.SALES_ID AND M.ORG_ID=SO.ORG_ID AND M.COLUMN1=SO.SE_ID AND SO.COLUMN1=1 AND M.STATUS=99 GROUP BY M.ORG_ID,M.CUST_ID,M.PAY_CURR,TO_CHAR(M.SALES_DATE,'YYYYMM') CREATE OR REPLACE VIEW VW_SH_CUST_QTY_RANK AS SELECT M.ORG_ID,M.CUST_ID, TO_CHAR(M.SALES_DATE,'YYYYMM') Y_MONTH, SUM(D.QTY) QTY, rank() over (PARTITION BY TO_CHAR(M.SALES_DATE,'YYYYMM') order by SUM(D.QTY) desc NULLS LAST) rank FROM SH_SALES_M M,SH_SALES_D D,SO_ORD_M SO WHERE M.ORG_ID=D.ORG_ID AND M.SALES_ID=D.SALES_ID AND M.ORG_ID=SO.ORG_ID AND M.COLUMN1=SO.SE_ID AND SO.COLUMN1=1 AND M.STATUS=99 GROUP BY M.ORG_ID,M.CUST_ID ,TO_CHAR(M.SALES_DATE,'YYYYMM')