package ibase.dashboard.scm.ejb;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class DivMonthlyStatus
 */
@Stateless
public class DivMonthlyStatus extends ValidatorEJB implements DivMonthlyStatusRemote, DivMonthlyStatusLocal
{
	/**
	 * Default constructor.
	 */
	private JSONObject jsonMetaData = null;
	private JSONObject jsonData = null;

	public DivMonthlyStatus()
	{

	}
	//Method :1 for Grid
	
	@SuppressWarnings("unchecked")
	public JSONObject getMonthlyData(String dataSourceName,String month) throws RemoteException, ITMException
	{
		System.out.println("in getMonthlyData() ");
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";	
		/*old sql version1.1= " SELECT division, mth_cy_sales||' - '|| mth_ho_trgt AS cysl_trgt, mth_ho_ach, mth_ly_sales, mth_gr||' - '||bud_gr AS mth_bud_gr, no_of_so, bud_inc_pmpm, retu_ytd, retu_ytd_prct, act_pft_mth, terrcnt, terr_ach_cnt, ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, tot_cnt , rank() OVER(PARTITION BY NULL ORDER BY tot_cnt DESC,mth_cy_sales DESC ) AS rnk " +
		"FROM( SELECT division, mth_cy_sales, mth_ly_sales, mth_gr, mth_ho_trgt, mth_ho_ach, no_of_so, retu_ytd, retu_ytd_prct, act_pft_mth, bud_inc_pmpm, bud_gr, terrcnt, terr_ach_cnt, ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, ach_st+gr_st+IncPMPM_st+sret_st+Pft_st+soach_st-600 AS tot_cnt FROM( SELECT b.div_descr AS DIVISION," +
		" ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES, (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS MTH_GR, ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%'" +
		" ELSE ROUND(0*100,1)||'%' END) AS MTH_HO_ACH, c.psr AS NO_OF_SO, ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD, (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS RETU_YTD_PRCT , b.bud_inc_pmpm , b.bud_gr ,a.act_pft_mth ,a.terrcnt, a.terr_ach_cnt , ( CASE WHEN (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)" +
		" ELSE ROUND(0*100,1) END) -100 >= 0 THEN 101 ELSE 100 END) AS ach_st , ( CASE WHEN (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1) ELSE ROUND(0*100,1) END) -b.bud_gr >= 0 THEN 101 ELSE 100 END) AS gr_st , ( CASE WHEN (ROUND(a.sale_ytd_cy/100000/c.psr/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2)) -b.bud_inc_pmpm >= 0 THEN 101 ELSE 100 END) AS IncPMPM_st , " +
		"( CASE WHEN (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1) ELSE ROUND(0*100,1) END) -15 >= 0 THEN 101 ELSE 100 END) AS SRet_st , ( CASE WHEN a.act_pft_mth - (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN b.BUD_PFT_APR WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN b.BUD_PFT_MAY WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN b.BUD_PFT_JUN WHEN SUBSTR('"+month+"' ,5,2) ='07' " +
		"THEN b.BUD_PFT_JUL WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN b.BUD_PFT_AUG WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN b.BUD_PFT_SEP WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN b.BUD_PFT_OCT WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN b.BUD_PFT_NOV WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN b.BUD_PFT_DEC WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN b.BUD_PFT_JAN WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN" +
		" b.BUD_PFT_FEB WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN b.BUD_PFT_MAR END ) >= 0 THEN 101 ELSE 100 END) AS Pft_st , ( CASE WHEN ROUND(a.terr_ach_cnt/a.terrcnt*100,1) - 70 >= 0 THEN 101 ELSE 100 END) AS SOAch_st FROM (SELECT fin_year , item_ser, SUM(sale_mth_cy) AS sale_mth_cy, SUM(sale_mth_ly) AS sale_mth_ly, SUM(hotrgt_mth_cy) AS hotrgt_mth_cy, SUM(sale_YTD_cy) AS sale_YTD_cy, SUM(sale_YTD_ly) AS sale_YTD_ly, " +
		"SUM(hotrgt_YTD_cy) AS hotrgt_ytd_cy, SUM(retu_ytd_cy) AS retu_ytd_cy , SUM(act_pft_mth) AS act_pft_mth ,SUM(terrcnt) AS terrcnt, SUM(terr_ach_cnt) AS terr_ach_cnt FROM (SELECT dwh.fin_year , dwh.item_ser, dwh.terr_code, SUM(sale_mth_cy) AS sale_mth_cy, SUM(sale_mth_ly) AS sale_mth_ly, SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy, SUM(sale_YTD_cy) AS sale_YTD_cy, SUM(sale_YTD_ly) AS sale_YTD_ly," +
		" SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, SUM(retu_ytd_cy) AS retu_ytd_cy , SUM(act_pft_mth) AS act_pft_mth ,1 AS terrcnt, (CASE WHEN SUM(trgt_YTD_cy) =0 THEN 1 WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN 1 ELSE 0 END ) AS terr_ach_cnt FROM (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, terr_code, sales_item.item_sub_group AS sub_grp, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " +
		"WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' " +
		"THEN SUM(CY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07'" +
		" THEN SUM(lY_SALE_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03'" +
		" THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' " +
        "THEN SUM(FLD_TRGT_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' " +
		"THEN SUM(CY_SALE_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC)" +
		" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' " +
		"THEN SUM(lY_SALE_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, " +
		"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11'" +
		" THEN SUM(FLD_TRGT_YTD_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) " +
		"WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02'" +
		" THEN SUM(CY_RETU_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_ytd_cy ,(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN ROUND(SUM(CY_SALE_MTH_apr)/100000,2) - SUM(NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0)) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN ROUND(SUM(CY_SALE_MTH_may)/100000,2) - SUM(NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0)) WHEN SUBSTR('"+month+"' ,5,2) ='06' " +
		"THEN ROUND(SUM(CY_SALE_MTH_jun)/100000,2) - SUM(NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0)) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN ROUND(SUM(CY_SALE_MTH_jul)/100000,2) - SUM(NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0)) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN ROUND(SUM(CY_SALE_MTH_aug)/100000,2) - SUM(NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0)) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN ROUND(SUM(CY_SALE_MTH_sep)/100000,2) - SUM(NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0)) " +
		"WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN ROUND(SUM(CY_SALE_MTH_oct)/100000,2) - SUM(NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0)) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN ROUND(SUM(CY_SALE_MTH_nov)/100000,2) - SUM(NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0)) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN ROUND(SUM(CY_SALE_MTH_dec)/100000,2) - SUM(NVL(COGS_MTH_dec,0)+NVL(EXPENSE_MTH_dec,0)) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN ROUND(SUM(CY_SALE_MTH_jan)/100000,2) - SUM(NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0)) " +
		"WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN ROUND(SUM(CY_SALE_MTH_feb)/100000,2) - SUM(NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0)) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN ROUND(SUM(CY_SALE_MTH_mar)/100000,2) - SUM(NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0)) END ) AS act_pft_mth FROM SM_SALES_SUMM_WIDE, sales_item WHERE trim(fin_year)= (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' -100 ,1,4)||'-'||SUBSTR('"+month+"' ,3,2) ELSE SUBSTR('"+month+"' ,1,4)||'-' || SUBSTR('"+month+"' +100,3,2) END) " +
		" AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code AND sales_item.item_sub_group NOT IN ('CU','BJ') GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group ,terr_code ) dwh, SM_SALES_BRAND_MST brnd WHERE brnd.brand_code(+) = dwh.sub_grp AND brnd.div_code(+) = dwh.item_ser AND brnd.fin_year(+) = dwh.fin_year GROUP BY dwh.fin_year, dwh.item_ser ,terr_code ) GROUP BY fin_year, item_ser ) a, SM_SALES_DIV_MST b, ( SELECT finyr, table_no, SUM(psr)" +
		" AS psr FROM ( SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR FROM starclub.emp_sales WHERE LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code ) GROUP BY finyr, table_no ) c WHERE trim(a.item_ser)=trim(b.div_code) AND b.fin_year=a.fin_year AND trim(a.item_ser)=trim(c.table_no) AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2) ) ) ";*/
				
		/*sql version1.2 = "SELECT division,'CySale-'||ROUND(mth_cy_sales/100,1)||'Cr   Trgt-'|| ROUND(mth_ho_trgt/100,1)||'Cr' AS cysl_trgt, 'Ach-'||mth_ho_ach AS  mth_ho_ach,'CySale-'||ROUND(mth_cy_sales/100,1)||'Cr LySale-'||ROUND(mth_ly_sales/100,1)||'Cr' AS mth_ly_sales, 'GR-'||mth_gr||' Bud-'||bud_gr AS mth_bud_gr, 'TotSO-'||no_of_so AS no_of_so, 'IncPMPM-'||ytd_pmpm||'   Bud-'||bud_inc_pmpm  AS bud_inc_pmpm,"+ 
			      " 'SlRet-'||retu_ytd AS retu_ytd, 'Ret-'||retu_ytd_prct AS retu_ytd_prct, 'Act Pft-'||act_pft_mth AS act_pft_mth,'Tot Terr Cnt-'||terrcnt AS terrcnt, 'Ach Cnt-'||terr_ach_cnt AS terr_ach_cnt,ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, tot_cnt, rank() OVER(PARTITION BY NULL ORDER BY tot_cnt DESC,mth_cy_sales DESC ) AS rnk FROM( SELECT division, mth_cy_sales, mth_ly_sales, mth_gr, mth_ho_trgt, mth_ho_ach,no_of_so, ytd_pmpm,retu_ytd, retu_ytd_prct, act_pft_mth,bud_inc_pmpm, bud_gr, terrcnt, terr_ach_cnt,ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, ach_st+gr_st+IncPMPM_st+sret_st+Pft_st+soach_st-600 AS tot_cnt "+
				" FROM ( SELECT b.div_descr AS DIVISION, ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES, (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy -a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS MTH_GR,  ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS MTH_HO_ACH, c.psr AS NO_OF_SO, ROUND(a.sale_ytd_cy/100000/c.psr/(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2) - ROUND(b.pmpm_ly,2) AS YTD_PMPM, "+
				" ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD, ( CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS RETU_YTD_PRCT  , b.bud_inc_pmpm , b.bud_gr ,a.act_pft_mth,a.terrcnt, a.terr_ach_cnt , ( CASE WHEN (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1) ELSE ROUND(0*100,1) END) -100 >= 0 THEN 101 ELSE 100 END) AS ach_st,( CASE WHEN (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1) ELSE ROUND(0*100,1) END) -b.bud_gr >= 0 THEN 101 ELSE 100 END) AS gr_st , "+
			     "( CASE WHEN (ROUND(a.sale_ytd_cy/100000/c.psr/(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2)  - ROUND(b.pmpm_ly,2)) -b.bud_inc_pmpm >= 0 THEN 101 ELSE 100 END) AS IncPMPM_st , ( CASE WHEN (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1) ELSE ROUND(0*100,1) END) -15 >= 0 THEN 101 ELSE 100 END) AS SRet_st , ( CASE WHEN a.act_pft_mth -(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN b.BUD_PFT_APR WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN b.BUD_PFT_MAY  WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN b.BUD_PFT_JUN WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN b.BUD_PFT_JUL "+  
				" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN b.BUD_PFT_AUG  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN b.BUD_PFT_SEP  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN b.BUD_PFT_OCT  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN b.BUD_PFT_NOV  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN b.BUD_PFT_DEC  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN b.BUD_PFT_JAN  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN b.BUD_PFT_FEB  "+
				" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN b.BUD_PFT_MAR END ) "+
				" >= 0 THEN 101 ELSE 100 END) AS Pft_st "+
				", ( CASE WHEN ROUND(a.terr_ach_cnt/a.terrcnt*100,1) - 70 >= 0 THEN 101 ELSE 100 END) AS SOAch_st "+
				"  FROM "+
			  			       "  (SELECT fin_year , item_ser, "+
						              "   SUM(sale_mth_cy) AS sale_mth_cy, "+
						                      "   SUM(sale_mth_ly) AS sale_mth_ly, "+
						                      "   SUM(hotrgt_mth_cy) AS hotrgt_mth_cy, "+
						                       "  SUM(sale_YTD_cy) AS sale_YTD_cy, "+
						                       "  SUM(sale_YTD_ly) AS sale_YTD_ly, "+
						                       "  SUM(hotrgt_YTD_cy) AS hotrgt_ytd_cy,"+
						                       "  SUM(retu_ytd_cy) AS retu_ytd_cy"+
						                       "  , SUM(act_pft_mth) AS act_pft_mth"+
						                       "  ,SUM(terrcnt) AS terrcnt, SUM(terr_ach_cnt) AS terr_ach_cnt"+
						       "  FROM "+
						       "  (SELECT dwh.fin_year , dwh.item_ser, "+
						               "  dwh.terr_code, "+
						               "  SUM(sale_mth_cy) AS sale_mth_cy, "+
						                      "   SUM(sale_mth_ly) AS sale_mth_ly, "+
						                       "  SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy, "+
						                       "  SUM(sale_YTD_cy) AS sale_YTD_cy, "+
						                       "  SUM(sale_YTD_ly) AS sale_YTD_ly,"+
						                       "  SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, "+
						                       "  SUM(retu_ytd_cy) AS retu_ytd_cy"+
						                       "  , SUM(act_pft_mth) AS act_pft_mth"+
						                       "  ,1 AS terrcnt,"+
						              "  (CASE WHEN SUM(trgt_YTD_cy) =0 THEN 1"+
						                    "  WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy * "+
						                                         "  (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100"+
						                   "   THEN 1 ELSE 0 END ) AS terr_ach_cnt"+
						      "   FROM "+
						       "  (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,"+
						              "  terr_code,"+
						              "  sales_item.item_sub_group AS sub_grp,"+
						              "  (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) "+
						                        "  WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) "+
						                                " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy,"+
						              "  (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) "+
						                        "  WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly,"+
						              "  (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) "+
						                      "    WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy,"+
						              "  (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) "+
						                       "   WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy,"+
						                "    (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) "+
						                      "    WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) "+
						                              "      WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly,"+
						                  "  (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) "+
						                      "    WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy,"+
						            "   (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) "+
						                        "  WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_ytd_cy "+
						                      "   ,(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN ROUND(SUM(CY_SALE_MTH_apr)/100000,2)"+
						     "    - SUM(NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0)) "+
						                       "   WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN ROUND(SUM(CY_SALE_MTH_may)/100000,2)"+
						      "   - SUM(NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0)) "+
						      "       WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN ROUND(SUM(CY_SALE_MTH_jun)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0)) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN ROUND(SUM(CY_SALE_MTH_jul)/100000,2) -"+
						      "   SUM(NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0)) "+
						                               "   WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN ROUND(SUM(CY_SALE_MTH_aug)/100000,2) -"+
						      "   SUM(NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0)) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN ROUND(SUM(CY_SALE_MTH_sep)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0)) "+
						          "                        WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN ROUND(SUM(CY_SALE_MTH_oct)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0)) "+
						          "                        WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN ROUND(SUM(CY_SALE_MTH_nov)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0)) "+
						          "                        WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN ROUND(SUM(CY_SALE_MTH_dec)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_dec,0)+NVL(EXPENSE_MTH_dec,0)) "+
						                                "  WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN ROUND(SUM(CY_SALE_MTH_jan)/100000,2) -"+
						      "   SUM(NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0)) "+
						                              "    WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN ROUND(SUM(CY_SALE_MTH_feb)/100000,2) -"+
						      "   SUM(NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0)) "+
						                               " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN ROUND(SUM(CY_SALE_MTH_mar)/100000,2) -"+
						       "  SUM(NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0))"+
						                                "   END ) AS act_pft_mth  "+
						      "   FROM SM_SALES_SUMM_WIDE, sales_item"+
						      "   WHERE trim(fin_year)= (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' -100"+
						       "  ,1,4)||'-'||SUBSTR('"+month+"' ,3,2) "+
						                   "   ELSE SUBSTR('"+month+"' ,1,4)||'-' || SUBSTR('"+month+"' +100,3,2) END)"+
						       "  AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code"+
						       "  AND sales_item.item_sub_group NOT IN ('CU','BJ')"+
						      "   GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group"+
						              "    ,terr_code "+
						           "   ) dwh,"+
						           "   SM_SALES_BRAND_MST brnd"+
						         "  WHERE brnd.brand_code(+) = dwh.sub_grp"+
						          "   AND brnd.div_code(+) = dwh.item_ser"+
						         "    AND brnd.fin_year(+) = dwh.fin_year"+
						        "   GROUP BY dwh.fin_year, dwh.item_ser"+
						                 "   ,terr_code"+
						         "   )"+
						        "   GROUP BY fin_year, item_ser "+
						      "   ) a,"+
						       "  SM_SALES_DIV_MST b,"+
						      "   ("+
						      "   SELECT finyr, table_no, SUM(psr) AS psr"+
						      "   FROM ("+
						      "   SELECT SUBSTR(version_id,3,4) AS finyr ,table_no,"+
						      "   pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR"+
						      "   FROM starclub.emp_sales"+
						      "   WHERE "+
						       "  LEVEL_NO = 4"+
						      "   GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code"+
						      "   )"+
						      "   GROUP BY finyr, table_no"+
						      "   ) c"+
						      "   WHERE trim(a.item_ser)=trim(b.div_code)"+
						      "   AND b.fin_year=a.fin_year"+
						      "   AND trim(a.item_ser)=trim(c.table_no)"+
						      "   AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2)"+
						      "   )"+
						     "    )";*/
			
		/* 1.3 version
		 * sql = "SELECT division, 'CySale-'||ROUND(mth_cy_sales/100,1)||'Cr   Trgt-'|| ROUND(mth_ho_trgt/100,1)||'Cr' AS cysl_trgt, 'Ach-'||mth_ho_ach AS mth_ho_ach, " 
		     +"'LySale-'||ROUND(mth_ly_sales/100,1)||'Cr   GR-'||mth_gr AS mth_ly_sales,'YTDTrgt-'|| ROUND(ytd_ho_trgt/100,1)||'Cr   LySale-'||ROUND(ytd_ly_sales/100,1)||'Cr' AS mth_bud_gr,  "
	         +"'TotSO-'||no_of_so||'   No_of_Mths-'||no_mth AS no_of_so, 'YTDPMPM-'||ytd_pmpm AS bud_inc_pmpm, 'SlRet-'||retu_ytd AS retu_ytd, 'Ret-'||retu_ytd_prct AS retu_ytd_prct, "
		     +"'Act Pft-'||act_pft_mth AS act_pft_mth,'Tot SO Cnt-'||socnt AS terrcnt, 'Ach Cnt-'||so_ach_cnt AS terr_ach_cnt,  "
		     +" ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, tot_cnt, rank() OVER(PARTITION BY NULL ORDER BY tot_cnt DESC,mth_cy_sales DESC ) AS rnk FROM(	 "
	         +" SELECT division, mth_cy_sales, mth_ly_sales, mth_gr, mth_ho_trgt, mth_ho_ach,no_of_so, ytd_pmpm, retu_ytd, retu_ytd_prct, act_pft_mth, terrcnt, terr_ach_cnt, ytd_ly_sales,ytd_ho_trgt, 	    "
	         +"socnt, so_ach_cnt, no_mth, ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st,ach_st+gr_st+IncPMPM_st+sret_st+Pft_st+soach_st-600 AS tot_cnt FROM(	    "
	         +"SELECT b.div_descr AS DIVISION, ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES,ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES,   "
		    +"(CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS MTH_GR,   "
		    +"ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT,(CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS MTH_HO_ACH,  "
		    +"c.psr AS NO_OF_SO, ROUND(a.sale_ytd_cy/100000/c.psr/(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END),2) AS YTD_PMPM,  "
		    +"ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD,(CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' ELSE ROUND(0*100,1)||'%' END) AS RETU_YTD_PRCT   "
	        +",a.act_pft_mth,a.terrcnt, a.terr_ach_cnt, ROUND(a.sale_ytd_ly/100000,2) AS YTD_LY_SALES, ROUND(a.hotrgt_ytd_cy/100000,2) AS YTD_HO_TRGT, a.socnt, a.so_ach_cnt "
	        +",(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) AS no_mth "
	        +", ( CASE WHEN (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1) ELSE ROUND(0*100,1) END) -100 >= 0 THEN 101 ELSE 100 END) AS ach_st "
			+", ( CASE WHEN (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1) ELSE ROUND(0*100,1) END) - (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.hotrgt_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1) ELSE ROUND(0*100,1) END) >= 0 THEN 101 ELSE 100 END) AS gr_st "
			+", ( CASE WHEN (ROUND(a.hotrgt_ytd_cy/100000/c.psr/(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END),2) -  "
			   +"ROUND(a.sale_ytd_cy/100000/c.psr/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END),2) ) >= 0 THEN 101 ELSE 100 END) AS IncPMPM_st		 "
			+", ( CASE WHEN (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1) ELSE ROUND(0*100,1) END) -1.5 <= 0 THEN 101 ELSE 100 END) AS SRet_st "
			+", ( CASE WHEN a.act_pft_mth -(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN b.BUD_PFT_APR WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN b.BUD_PFT_MAY WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN b.BUD_PFT_JUN WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN b.BUD_PFT_JUL   "
					 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN b.BUD_PFT_AUG WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN b.BUD_PFT_SEP WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN b.BUD_PFT_OCT WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN b.BUD_PFT_NOV WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN b.BUD_PFT_DEC   "
					 +"WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN b.BUD_PFT_JAN WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN b.BUD_PFT_FEB WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN b.BUD_PFT_MAR END )>= 0 THEN 101 ELSE 100 END) AS Pft_st	 "
			+", ( CASE WHEN ROUND(a.so_ach_cnt/a.socnt*100,1) - 70 >= 0 THEN 101 ELSE 100 END) AS SOAch_st FROM  "
			+"(SELECT fin_year , item_ser, SUM(sale_mth_cy) AS sale_mth_cy,SUM(sale_mth_ly) AS sale_mth_ly,SUM(hotrgt_mth_cy) AS hotrgt_mth_cy,SUM(sale_YTD_cy) AS sale_YTD_cy,SUM(sale_YTD_ly) AS sale_YTD_ly, SUM(hotrgt_YTD_cy) AS hotrgt_ytd_cy "
			 +",SUM(retu_ytd_cy) AS retu_ytd_cy, SUM(act_pft_mth) AS act_pft_mth,SUM(terrcnt) AS terrcnt, SUM(terr_ach_cnt) AS terr_ach_cnt, SUM(socnt) AS socnt,SUM(so_ach_cnt) AS so_ach_cnt FROM	    "
			+"(SELECT dwh.fin_year , dwh.item_ser, dwh.terr_code,SUM(sale_mth_cy) AS sale_mth_cy,SUM(sale_mth_ly) AS sale_mth_ly,SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,SUM(sale_YTD_cy) AS sale_YTD_cy,  "
					+"SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy,	SUM(retu_ytd_cy) AS retu_ytd_cy, SUM(act_pft_mth) AS act_pft_mth,1 AS terrcnt,   "
					+"(CASE WHEN SUM(trgt_YTD_cy) =0 THEN 1 WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy *(100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN 1 ELSE 0 END ) AS terr_ach_cnt, emp.psr AS socnt, "
			 +"(CASE WHEN SUM(trgt_YTD_cy) =0 THEN emp.psr WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy *(100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN emp.psr ELSE 0 END ) AS so_ach_cnt FROM	    "
			+"(SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, terr_code, sales_item.item_sub_group AS sub_grp, "
			       +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, "
			       +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, "
			       +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, "
			       +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, "
				   +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB)	 WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, "
				   +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy, "
			      +"(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_ytd_cy  "
			  	  +",(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN ROUND(SUM(CY_SALE_MTH_apr)/100000,2) - SUM(NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0)) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN ROUND(SUM(CY_SALE_MTH_may)/100000,2) - SUM(NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0))  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN ROUND(SUM(CY_SALE_MTH_jun)/100000,2) - SUM(NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0)) WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN ROUND(SUM(CY_SALE_MTH_jul)/100000,2) - SUM(NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0))  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN ROUND(SUM(CY_SALE_MTH_aug)/100000,2) - SUM(NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0)) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN ROUND(SUM(CY_SALE_MTH_sep)/100000,2) - SUM(NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0))  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN ROUND(SUM(CY_SALE_MTH_oct)/100000,2) - SUM(NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0)) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN ROUND(SUM(CY_SALE_MTH_nov)/100000,2) - SUM(NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0))  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN ROUND(SUM(CY_SALE_MTH_dec)/100000,2) - SUM(NVL(COGS_MTH_dec,0)+NVL(EXPENSE_MTH_dec,0)) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN ROUND(SUM(CY_SALE_MTH_jan)/100000,2) - SUM(NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0))  "
						 +"WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN ROUND(SUM(CY_SALE_MTH_feb)/100000,2) - SUM(NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0)) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN ROUND(SUM(CY_SALE_MTH_mar)/100000,2) - SUM(NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0)) END ) AS act_pft_mth 			  "
			+" FROM SM_SALES_SUMM_WIDE, sales_item WHERE trim(fin_year)= (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' -100 ,1,4)||'-'||SUBSTR('"+month+"' ,3,2) ELSE SUBSTR('"+month+"' ,1,4)||'-' || SUBSTR('"+month+"' +100,3,2) END) AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code AND sales_item.item_sub_group NOT IN ('CU','BJ') "
			+" GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group ,terr_code ) dwh, SM_SALES_BRAND_MST brnd,  "
			+"(SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR FROM starclub.emp_sales WHERE LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code) emp "
			+" WHERE brnd.brand_code(+) = dwh.sub_grp AND brnd.div_code(+) = dwh.item_ser AND brnd.fin_year(+) = dwh.fin_year AND emp.finyr = SUBSTR(dwh.fin_year,3,2)||SUBSTR(dwh.fin_year,6,2) AND emp.pool_code = dwh.terr_code AND emp.table_no= dwh.item_ser "
			+" GROUP BY dwh.fin_year, dwh.item_ser,terr_code,emp.psr ) GROUP BY fin_year, item_ser) a, "
			+" SM_SALES_DIV_MST b, (SELECT finyr, table_no, SUM(psr) AS psr FROM (SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR FROM starclub.emp_sales WHERE LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code ) GROUP BY finyr, table_no ) c "
			+" WHERE trim(a.item_ser)=trim(b.div_code) AND b.fin_year=a.fin_year AND trim(a.item_ser)=trim(c.table_no) AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2) ) ) ";
				*/
		
		sql="SELECT division, "
				+" 'CySale: '||ROUND(ytd_cy_sales/100,1)||'Cr   Trgt: '|| ROUND(ytd_ho_trgt/100,1)||'Cr' AS cysl_trgt, " 
		    		   +" 'Ach: '||ytd_ho_ach AS mth_ho_ach,  "
			   					+" 'CySale >LySale: '||ROUND((ytd_cy_sales-ytd_ly_sales)/100,1)||'Cr   GR: '||ytd_gr AS mth_ly_sales, " 
					   +" 'Trgt >LySale: '|| ROUND((ytd_ho_trgt-ytd_ly_sales)/100,1)||'Cr' AS mth_bud_gr,  "
			   				+" 'Act Pft: '||act_pft_ytd||'L   Bud Pft: '||ytd_bud_pft||'L' AS act_pft_mth, "
					   +" 'Tot SO Cnt: '||no_of_so||'   No_of_Mths: '||no_mth AS no_of_so,  "
		    		   +" 'Trgt >CySale: '||ROUND((ytd_ho_trgt-ytd_cy_sales)/100,1)||'Cr' AS bud_inc_pmpm, " 
			   				+" 'Tot SO Cnt: '||socnt AS terrcnt,  "
					   +" 'Ach Cnt: '||so_ach_cnt AS terr_ach_cnt, " 
			   				+" 'SlRet: '||retu_ytd||'L' AS retu_ytd,  "
					   +" 'SlRet %: '||retu_ytd_prct AS retu_ytd_prct, "
			        		   +" ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st,tot_cnt "
					   +" , rank() OVER(PARTITION BY NULL ORDER BY tot_cnt DESC,ytd_cy_sales DESC ) AS rnk "
					   +" FROM(	 "
						+" SELECT division, ytd_cy_sales, ytd_ly_sales, ytd_gr, ytd_ho_trgt, ytd_ho_ach, "
						+" no_of_so, ytd_pmpm, retu_ytd, retu_ytd_prct, act_pft_ytd, terrcnt, terr_ach_cnt, " 
				    		   +" socnt, so_ach_cnt, ytd_bud_pft, no_mth, ach_st, gr_st, IncPMPM_st, sret_st, Pft_st, soach_st, "
						+" ach_st+gr_st+IncPMPM_st+sret_st+Pft_st+soach_st-600 AS tot_cnt "
						 +" FROM(	    "
						+" SELECT b.div_descr AS DIVISION, "
						+" ROUND(a.sale_ytd_cy/100000,2) AS ytd_CY_SALES, "  
				    		   +" ROUND(a.sale_ytd_ly/100000,2) AS ytd_LY_SALES, "  
							   +" (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1)||'%' " 
							   +" ELSE ROUND(0*100,1)||'%' END) AS ytd_GR, "  
					        		 +" ROUND(a.trgt_ytd_cy/100000,2) AS ytd_TRGT, " 
							   +" ROUND(a.hotrgt_ytd_cy/100000,2) AS ytd_HO_TRGT, " 
							   +" (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1)||'%' " 
							   +" ELSE ROUND(0*100,1)||'%' END) AS ytd_HO_ACH, " 
					        		 +" c.psr AS NO_OF_SO,  "
							   +" ROUND(a.sale_ytd_cy/100000/c.psr/ "
							   +" (CASE WHEN SUBSTR('"+month+"',5,2)<4 THEN SUBSTR('"+month+"',5,2)+9 ELSE SUBSTR('"+month+"',5,2)-3 END) " 
				                    +" ,2) AS YTD_PMPM,  "
				                    		+" ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD, "  
							   +" (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' " 
							   +" ELSE ROUND(0*100,1)||'%' END) AS RETU_YTD_PRCT "  
					        		 +" , ROUND(((a.sale_ytd_cy/100000) - d.cogs_exp_ytd)/100,1) AS act_pft_ytd "
						+" ,a.terrcnt, a.terr_ach_cnt "
						+" , a.socnt, a.so_ach_cnt "
						+" ,(CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN b.BUD_PFT_APR " 
						+" WHEN SUBSTR('"+month+"',5,2) ='05' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY "  
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN+b.BUD_PFT_FEB "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN+b.BUD_PFT_FEB+b.BUD_PFT_MAR " 
									 +" END ) AS ytd_bud_pft "
									 +" ,(CASE WHEN SUBSTR('"+month+"',5,2)<4 THEN SUBSTR('"+month+"',5,2)+9 ELSE SUBSTR('"+month+"',5,2)-3 END) AS no_mth "
						+" , ( CASE WHEN (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1) " 
						+" ELSE ROUND(0*100,1) END) -100 >= 0 THEN 101 ELSE 100 END) AS ach_st "
					        		 +" , ( CASE WHEN (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.hotrgt_ytd_cy)/a.sale_ytd_ly*100,1) "
						+" ELSE ROUND(0*100,1) END) "
					 +" >= 0 THEN 101 ELSE 100 END) AS gr_st "		
							 +" , ( CASE WHEN (ROUND((a.hotrgt_ytd_cy - a.sale_ytd_cy)/100000/c.psr/ "
						+" (CASE WHEN SUBSTR('"+month+"',5,2)<4 THEN SUBSTR('"+month+"',5,2)+9 ELSE SUBSTR('"+month+"',5,2)-3 END) ,2) "
				                   +" )	 >= 0 THEN 101 ELSE 100 END) AS IncPMPM_st "		
									  +" , ( CASE WHEN (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1) "
						+" ELSE ROUND(0*100,1) END) -1.5 <= 0 THEN 101 ELSE 100 END) AS SRet_st   "
					        		 +" , ( CASE WHEN ROUND(((a.sale_ytd_cy/100000) - d.cogs_exp_ytd)/100,1) - "
						+" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN b.BUD_PFT_APR  "
						+" WHEN SUBSTR('"+month+"',5,2) ='05' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY "  
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN+b.BUD_PFT_FEB "  
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN b.BUD_PFT_APR+b.BUD_PFT_MAY+b.BUD_PFT_JUN+b.BUD_PFT_JUL+b.BUD_PFT_AUG+b.BUD_PFT_SEP+b.BUD_PFT_OCT+b.BUD_PFT_NOV+b.BUD_PFT_DEC+b.BUD_PFT_JAN+b.BUD_PFT_FEB+b.BUD_PFT_MAR " 
									 +" END )	>= 0 THEN 101 ELSE 100 END) AS Pft_st "	
									 +" , ( CASE WHEN ROUND(a.so_ach_cnt/a.socnt*100,1) - 70 >= 0 THEN 101 ELSE 100 END) AS SOAch_st "
						+" FROM (SELECT fin_year , item_ser, " 
						+" SUM(sale_mth_cy) AS sale_mth_cy, " 
				        		+" SUM(sale_mth_ly) AS sale_mth_ly, SUM(trgt_mth_cy) AS trgt_mth_cy, "
								+" SUM(hotrgt_mth_cy) AS hotrgt_mth_cy, " 
								+" SUM(sale_YTD_cy) AS sale_YTD_cy,  "
								+" SUM(sale_YTD_ly) AS sale_YTD_ly, SUM(trgt_ytd_cy) AS trgt_ytd_cy, "
								+" SUM(hotrgt_YTD_cy) AS hotrgt_ytd_cy, "
								+" SUM(retu_ytd_cy) AS retu_ytd_cy "
								+" ,SUM(terrcnt) AS terrcnt, SUM(terr_ach_cnt) AS terr_ach_cnt "
								+" , SUM(socnt) AS socnt,	 "
						+" SUM(so_ach_cnt) AS so_ach_cnt "	
						+" FROM	    "
						+" (SELECT dwh.fin_year , dwh.item_ser, " 
						+" dwh.terr_code,  "
				        		+" SUM(sale_mth_cy) AS sale_mth_cy, " 
				        		+" SUM(sale_mth_ly) AS sale_mth_ly, SUM(trgt_mth_cy) AS trgt_mth_cy, "
								+" SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy, " 
								+" SUM(sale_YTD_cy) AS sale_YTD_cy,  "
								+" SUM(sale_YTD_ly) AS sale_YTD_ly, SUM(trgt_ytd_cy) AS trgt_ytd_cy, "
								+" SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, " 
								+" SUM(retu_ytd_cy) AS retu_ytd_cy "
								+" ,1 AS terrcnt, "
								+" (CASE WHEN SUM(trgt_YTD_cy) =0 THEN 1 "
				    		   +" WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy)*100,1)>= 100 "
				            		 +" THEN 1 ELSE 0 END ) AS terr_ach_cnt "
				            		 +" , emp.psr AS socnt, "
						+" (CASE WHEN SUM(trgt_YTD_cy) =0 THEN emp.psr "
						+" WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy)*100,1)>= 100 "
				            		 +" THEN emp.psr ELSE 0 END ) AS so_ach_cnt "	
				            		 +" FROM "	   
						+" (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, "
						+" terr_code, "
				    		   +" sales_item.item_sub_group AS sub_grp, "
				    		   +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " 
				    		   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(CY_SALE_MTH_NOV)  "
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(CY_SALE_MTH_DEC)  "
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(CY_SALE_MTH_JAN)  "
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(CY_SALE_MTH_FEB)  "
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(LY_SALE_MTH_APR) " 
				    		   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(LY_SALE_MTH_JUN)  "
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) " 
				    		   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(CY_SALE_YTD_APR) " 
				    		   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(lY_SALE_YTD_APR) " 
							   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR)  "
							   +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy, "
									 +" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(CY_RETU_YTD_APR) " 
				    		  +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) " 
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(CY_RETU_YTD_JUL) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(CY_RETU_YTD_NOV) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(CY_RETU_YTD_FEB) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_ytd_cy " 
									 +" FROM SM_SALES_SUMM_WIDE, sales_item "
						+" WHERE trim(fin_year)= (CASE WHEN SUBSTR('"+month+"',5,2)<4 THEN SUBSTR('"+month+"'-100 ,1,4)||'-'||SUBSTR('"+month+"',3,2) " 
						+" ELSE SUBSTR('"+month+"',1,4)||'-' || SUBSTR('"+month+"'+100,3,2) END) "
				+" AND '"+month+"' ='"+month+"' "
						+" AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code "
						+" AND sales_item.item_sub_group NOT IN ('CU','BJ') "
				+" GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group "
						+" ,terr_code " 
				        		 +" ) dwh, "
				    		 +" SM_SALES_BRAND_MST brnd ,	( "
				    		 +" SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR "
						+" FROM starclub.emp_sales	WHERE  LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code "
						+" ) emp "
						+" WHERE brnd.brand_code(+) = dwh.sub_grp   AND brnd.div_code(+) = dwh.item_ser "
						  +" AND brnd.fin_year(+) = dwh.fin_year	AND emp.finyr = SUBSTR(dwh.fin_year,3,2)||SUBSTR(dwh.fin_year,6,2) "
				    		+" AND emp.pool_code = dwh.terr_code 	AND emp.table_no= dwh.item_ser "
							+" GROUP BY dwh.fin_year, dwh.item_ser,terr_code  ,emp.psr ) "  
				  +" GROUP BY fin_year, item_ser) a, "
						  +" SM_SALES_DIV_MST b,(SELECT finyr, table_no, SUM(psr) AS psr "
						+" FROM (SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR "
						+" FROM starclub.emp_sales	WHERE  LEVEL_NO = 4 "
						+" GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code) GROUP BY finyr, table_no) c, "
						+" ( SELECT	fin_year, item_ser, "
						+" (CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0)) " 
				    		 +" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0))  "
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0)) "
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0)) "
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0)) "
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(NVL(COGS_MTH_dec,0)+NVL(EXPENSE_MTH_dec,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0)) "
									 +" END ) AS cogs_exp_mth " 			 
									  +" ,(CASE WHEN SUBSTR('"+month+"',5,2) ='04' THEN SUM(NVL(COGS_YTD_APR,0)+NVL(EXPENSE_YTD_APR,0)) " 
							+" WHEN SUBSTR('"+month+"',5,2) ='05' THEN SUM(NVL(COGS_YTD_may,0)+NVL(EXPENSE_YTD_may,0))  "
					        		 +" WHEN SUBSTR('"+month+"',5,2) ='06' THEN SUM(NVL(COGS_YTD_jun,0)+NVL(EXPENSE_YTD_jun,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='07' THEN SUM(NVL(COGS_YTD_jul,0)+NVL(EXPENSE_YTD_jul,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='08' THEN SUM(NVL(COGS_YTD_aug,0)+NVL(EXPENSE_YTD_aug,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='09' THEN SUM(NVL(COGS_YTD_sep,0)+NVL(EXPENSE_YTD_sep,0)) "
									 +" WHEN SUBSTR('"+month+"',5,2) ='10' THEN SUM(NVL(COGS_YTD_oct,0)+NVL(EXPENSE_YTD_oct,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='11' THEN SUM(NVL(COGS_YTD_nov,0)+NVL(EXPENSE_YTD_nov,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='12' THEN SUM(NVL(COGS_YTD_dec,0)+NVL(EXPENSE_YTD_dec,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='01' THEN SUM(NVL(COGS_YTD_jan,0)+NVL(EXPENSE_YTD_jan,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='02' THEN SUM(NVL(COGS_YTD_feb,0)+NVL(EXPENSE_YTD_feb,0)) " 
									 +" WHEN SUBSTR('"+month+"',5,2) ='03' THEN SUM(NVL(COGS_YTD_mar,0)+NVL(EXPENSE_YTD_mar,0)) "
									 +" END ) AS cogs_exp_ytd 	 "
									  +" FROM SM_SALES_SUMM_WIDE "
						  +" WHERE trim(fin_year)= (CASE WHEN SUBSTR('"+month+"',5,2)<4 THEN SUBSTR('"+month+"'-100 ,1,4)||'-'||SUBSTR('"+month+"',3,2) " 
						  +"         ELSE SUBSTR('"+month+"',1,4)||'-' || SUBSTR('"+month+"'+100,3,2) END)		 "
				  +" GROUP BY fin_year, item_ser ) d "
						  +" WHERE trim(a.item_ser)=trim(b.div_code) AND trim(a.item_ser)=trim(d.item_ser) "
						  +" AND b.fin_year=a.fin_year AND d.fin_year=a.fin_year "
				+"  AND trim(a.item_ser)=trim(c.table_no) AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2)))";
		
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;			
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("div_descr", checkNull(rs.getString("DIVISION")));
				rowData.put("cysl_trgt", checkNull(rs.getString("CYSL_TRGT")));
				rowData.put("mth_ho_ach", checkNull(rs.getString("MTH_HO_ACH")));
				rowData.put("mth_ly_sales", checkNull(rs.getString("MTH_LY_SALES")));
				rowData.put("mth_bud_gr", checkNull(rs.getString("MTH_BUD_GR")));
				rowData.put("no_of_so", checkNull(rs.getString("NO_OF_SO")));
				rowData.put("bud_inc_pmpm", checkNull(rs.getString("BUD_INC_PMPM")));
				rowData.put("retu_ytd", checkNull(rs.getString("RETU_YTD")));
				rowData.put("retu_ytd_prct", checkNull(rs.getString("RETU_YTD_PRCT")));
				rowData.put("act_pft_mth", checkNull(rs.getString("ACT_PFT_MTH")));
				rowData.put("terrcnt", checkNull(rs.getString("TERRCNT")));
				rowData.put("terr_ach_cnt", checkNull(rs.getString("TERR_ACH_CNT")));
				rowData.put("ach_st", checkNull(rs.getString("ACH_ST")));
				rowData.put("gr_st", checkNull(rs.getString("GR_ST")));
				rowData.put("incpmpm_st", checkNull(rs.getString("INCPMPM_ST")));
				rowData.put("sret_st", checkNull(rs.getString("SRET_ST")));
				rowData.put("pft_st", checkNull(rs.getString("PFT_ST")));
				rowData.put("soach_st", checkNull(rs.getString("SOACH_ST")));
				rowData.put("tot_cnt", checkNull(rs.getString("TOT_CNT")));
				rowData.put("rnk", checkNull(rs.getString("RNK")));
				rawDataJson.put(count, rowData);
				count++;
			}
			System.out.println("getMonthlyData rowwise json====" + rawDataJson.toString());

		} catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
						rs.close();
					rs = null;
					if (pstmt != null)
						pstmt.close();
					pstmt = null;
					conn.close();
					conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				System.out.println("Exception in :DivMonthlyStatus:getMonthlyData():" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataJson;
	}

	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		
}
