									select Indicator_Value INTO V_VALUE_2 from regional_indicators where
										CID = (select ic_id from microwatch_category where IC_NAME = 'Micro-Insurance') and
										IID = (select MWI_ID from microwatch_indicators where INDICATOR_NAME = 'Number of Policy Holders') and
										REGION_ID = (select R_ID from regions where TYPE = 'GT') and
										FILE_ID = P_FILE_ID;
										
									IF V_VALUE_1 = V_VALUE_2
										THEN
											-- CHECK NEXT INDICATOR
                                            select Indicator_Value INTO V_VALUE_1 from country_indicators where 
												CI_CID = (select ic_id from microwatch_category where IC_NAME = 'Micro-Insurance') and
												CI_IID = (select MWI_ID from microwatch_indicators where INDICATOR_NAME = 'Sum Insured') and
												CI_ITID = (select MWIT_ID from microwatch_indicator_types where IT_NAME = 'Total' and indicator_id = (select MWI_ID from microwatch_indicators where INDICATOR_NAME = 'Sum Insured')) and
												FILE_ID = P_FILE_ID;
												
												
											select Indicator_Value INTO V_VALUE_2 from regional_indicators where
												CID = (select ic_id from microwatch_category where IC_NAME = 'Micro-Insurance') and
												IID = (select MWI_ID from microwatch_indicators where INDICATOR_NAME = 'Sum Insured') and
												REGION_ID = (select R_ID from regions where TYPE = 'GT') and
												FILE_ID = P_FILE_ID;
												
											IF V_VALUE_1 = V_VALUE_2
												THEN
													  Set PCODE='00';
													  Set PDESC='success';
													  SET PMSG='Y';
											END IF;
									END IF;
							END IF;
					END IF;
			END IF;
            
	END IF;
	
  
  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_ACTIVE_BORROWERS_BY_SECTOR` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_ACTIVE_BORROWERS_BY_SECTOR`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT IFNULL(SUM(Q1.RESULT),0)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	   AND IT.IT_NAME IN  ('Agriculture','Livestock/Poultry','Trade','Services','Manufacturing/Production','Housing','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT ifnull(SUM(Q2.RESULT),0)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	   AND IT.IT_NAME IN  ('Agriculture','Livestock/Poultry','Trade','Services','Manufacturing/Production','Housing','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;


 SELECT  IT.IT_NAME,IFNULL(SUM(CI.Indicator_Value),0)AS RESULT,F.YEAR ,F.QUARTER
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       -- AND F.YEAR         IN ( PYEAR_FIRST,PYEAR_SEC)
		-- AND F.QUARTER IN  (PQUARTER_FIRST,PQUARTER_SEC)
        AND F.FILE_ID IN (select jk.FILE_ID from
    (
	   (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) union
		
		(SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC ) )as jk
    )
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       	   AND IT.IT_NAME IN  ('Agriculture','Livestock/Poultry','Trade','Services','Manufacturing/Production','Housing','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
ORDER BY F.YEAR desc,F.QUARTEr desc;
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_MFP_STAFF` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_MFP_STAFF`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
DECLARE V_TOTAL DOUBLE;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT 
    
     IFNULL(SUM(Q1.RESULT ),0) INTO 	Q1_TOTAL
FROM
    (SELECT  SUM(CI.Indicator_Value) as RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE 
    ORDER BY I.INDICATOR_NAME , F.QUARTER) Q1;

SELECT 
    
     IFNULL(SUM(Q2.RESULT ),0) INTO 	Q2_TOTAL
FROM
    (SELECT  SUM(CI.Indicator_Value) AS RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_SEC
            AND F.QUARTER = PQUARTER_SEC
            AND I.INDICATOR_NAME = PINDICATOR_NAME
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE
    ORDER BY I.INDICATOR_NAME , F.QUARTER) Q2;

SELECT   OT.ORGANIZATION_TYPE ,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT, F.YEAR , F.QUARTER
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
        -- AND F.YEAR         IN ( PYEAR_FIRST,PYEAR_SEC)
		-- AND F.QUARTER IN  (PQUARTER_FIRST,PQUARTER_SEC)
        AND F.FILE_ID IN (select jk.FILE_ID from
    (
	   (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) union
		
		(SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC ) )as jk
    )
            AND I.INDICATOR_NAME = PINDICATOR_NAME
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE 
    ORDER BY I.INDICATOR_NAME , F.YEAR desc,F.QUARTEr desc;


 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_MICROENTERPRISE` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_MICROENTERPRISE`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
IN      PIC_NAME  VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
SELECT SUM(CI.Indicator_Value) AS RESULT INTO Q1_TOTAL
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PIC_NAME
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER;

 SELECT SUM(CI.Indicator_Value) AS RESULT
		INTO Q2_TOTAL	
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
           AND CI.CI_IID        = I.MWI_ID
           AND CI.CI_CID   = C.IC_ID
           AND CI.CI_ITID          = IT.MWIT_ID
           AND F.YEAR         = PYEAR_SEC
           AND F.QUARTER = PQUARTER_SEC
           AND I.INDICATOR_NAME = PINDICATOR_NAME
           AND  IT.IT_NAME = PIC_NAME
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER;
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_OF_MFP_STAFF_RATIO` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_OF_MFP_STAFF_RATIO`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 
 
SELECT Q1.YEAR ,Q1.QUARTER,Q1.ORGANIZATION_TYPE,(Q1.RESULT/(CASE Q2.RESULT  WHEN  0 THEN 1 else Q2.RESULT END)) AS RESULT
FROM 
(
 SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR         =   PYEAR_FIRST
		AND F.QUARTER =  PQUARTER_FIRST
	   AND I.INDICATOR_NAME =  PINDICATOR_NAME
       AND IT.IT_NAME = 'Total' 
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q1,
 (SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR         =   PYEAR_FIRST
		AND F.QUARTER =  PQUARTER_FIRST
	   AND I.INDICATOR_NAME =  'Total Microfinance Staff'
       AND IT.IT_NAME = 'Total' 
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q2
WHERE Q1.ORGANIZATION_TYPE= Q2.ORGANIZATION_TYPE;

SELECT Q1.YEAR ,Q1.QUARTER,Q1.ORGANIZATION_TYPE,(Q1.RESULT/(CASE Q2.RESULT  WHEN  0 THEN 1 else Q2.RESULT END)) AS RESULT
FROM 
(
 SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR         =   PYEAR_SEC
		AND F.QUARTER =  PQUARTER_SEC
	   AND I.INDICATOR_NAME =  PINDICATOR_NAME
       AND IT.IT_NAME = 'Total' 
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q1,
 (SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR         =   PYEAR_SEC
		AND F.QUARTER =  PQUARTER_SEC
	   AND I.INDICATOR_NAME =  'Total Microfinance Staff'
       AND IT.IT_NAME = 'Total' 
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q2
WHERE Q1.ORGANIZATION_TYPE= Q2.ORGANIZATION_TYPE;


    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_OF_PROVISION` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_OF_PROVISION`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
IN PVALUE1 VARCHAR(50),
IN PVALUE2 VARCHAR(50),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
OUT Q1_VALUE1 DOUBLE,
OUT Q2_VALUE1 DOUBLE,
OUT Q1_VALUE2 DOUBLE,
OUT Q2_VALUE2 DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT SUM(Q1.RESULT)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME IN( PVALUE1,PVALUE2)
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT SUM(Q2.RESULT)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME IN( PVALUE1,PVALUE2)
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;


  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q1_VALUE1
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE1
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q2_VALUE1
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE1
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q1_VALUE2
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE2
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q2_VALUE2
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE2
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_OF_PROVISION_CHART_22` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_OF_PROVISION_CHART_22`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
OUT Q1_VALUE1 DOUBLE,
OUT Q2_VALUE1 DOUBLE,
OUT Q1_VALUE2 DOUBLE,
OUT Q2_VALUE2 DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT SUM(Q1.RESULT)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = 'Value of Savings'
       AND IT.IT_NAME IN( 'Branches4','Mobile Wallets5')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT SUM(Q2.RESULT)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = 'Value of Savings'
       AND IT.IT_NAME IN( 'Branches4','Mobile Wallets5')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;


    SELECT  IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
		INTO Q1_VALUE1
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
        AND OT.otid = 2
		AND F.FILE_ID IN (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) 
 
    and o.ended is null
		AND I.INDICATOR_NAME =  'Value of Savings'
        AND  IT.IT_NAME = 'Total'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR,F.QUARTER;
    
        SELECT  IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
		INTO Q2_VALUE1
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
        AND OT.otid = 2
		AND F.FILE_ID IN (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC) 
 
    and o.ended is null
		AND I.INDICATOR_NAME =  'Value of Savings'
        AND  IT.IT_NAME = 'Total'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR,F.QUARTER;

    SELECT  IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
		INTO Q1_VALUE2
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
        AND OT.otid = 3
		AND F.FILE_ID IN (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) 
 
    and o.ended is null
		AND I.INDICATOR_NAME =  'Value of Savings'
        AND  IT.IT_NAME = 'Total'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR,F.QUARTER;
    
        SELECT  IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
		INTO Q2_VALUE2
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
        AND OT.otid = 3
		AND F.FILE_ID IN (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC) 
 
    and o.ended is null
		AND I.INDICATOR_NAME =  'Value of Savings'
        AND  IT.IT_NAME = 'Total'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR,F.QUARTER;
  
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_OF_PROVISION_LM` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_OF_PROVISION_LM`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
IN PVALUE1 VARCHAR(50),
IN PVALUE2 VARCHAR(50),
IN PVALUE3 VARCHAR(50),

OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,

OUT Q1_VALUE1 DOUBLE,
OUT Q2_VALUE1 DOUBLE,

OUT Q1_VALUE2 DOUBLE,
OUT Q2_VALUE2 DOUBLE,

OUT Q1_VALUE3 DOUBLE,
OUT Q2_VALUE3 DOUBLE,


 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT SUM(Q1.RESULT)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME IN( PVALUE1,PVALUE2,PVALUE3)
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT SUM(Q2.RESULT)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME IN( PVALUE1,PVALUE2,PVALUE3)
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;


  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q1_VALUE1
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE1
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;



  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q1_VALUE3
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE3
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;


  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q2_VALUE3
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE3
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;



  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q2_VALUE1
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE1
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q1_VALUE2
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE2
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;

  SELECT IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
       INTO Q2_VALUE2
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       AND IT.IT_NAME = PVALUE2
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER;



 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_OF_PROVISION_PEERGROUP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_OF_PROVISION_PEERGROUP`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 
SELECT SUM(Q1.RESULT)
INTO Q1_TOTAL
FROM (
	 SELECT  SUM(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR         =   PYEAR_FIRST
		AND F.QUARTER =  PQUARTER_FIRST
		AND I.INDICATOR_NAME =  PINDICATOR_NAME
		AND IT.IT_NAME = 'TOTAL'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q1;

SELECT SUM(Q2.RESULT)
INTO Q2_TOTAL
FROM (	SELECT SUM(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		AND F.YEAR        =PYEAR_SEC
		AND F.QUARTER =PQUARTER_SEC
		AND I.INDICATOR_NAME =  PINDICATOR_NAME
		AND IT.IT_NAME = 'TOTAL'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE) Q2;


	 SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		-- AND F.YEAR         IN ( PYEAR_FIRST,PYEAR_SEC)
		-- AND F.QUARTER IN  (PQUARTER_FIRST,PQUARTER_SEC)
        AND F.FILE_ID IN (select jk.FILE_ID from
    (
	   (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) union
		
		(SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC ) )as jk
    )
		AND I.INDICATOR_NAME =  PINDICATOR_NAME
		AND IT.IT_NAME = 'TOTAL'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR desc,F.QUARTEr desc;


 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_POLICY_HOLDERS_BY_TYPE` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_POLICY_HOLDERS_BY_TYPE`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT SUM(Q1.RESULT)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	   AND IT.IT_NAME IN  ('Health','Agriculture/Livestock','Life','Property','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT SUM(Q2.RESULT)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	   AND IT.IT_NAME IN  ('Health','Agriculture/Livestock','Life','Property','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;


 SELECT  IT.IT_NAME,SUM(CI.Indicator_Value)AS RESULT,F.YEAR ,F.QUARTER
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       	   AND IT.IT_NAME IN ('Health','Agriculture/Livestock','Life','Property','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER;

 SELECT  IT.IT_NAME,SUM(CI.Indicator_Value)AS RESULT,F.YEAR ,F.QUARTER
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
       	   AND IT.IT_NAME IN  ('Health','Agriculture/Livestock','Life','Property','Other')
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER;

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_CHART_PORTFOLIO_RISK_30_DAYS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_CHART_PORTFOLIO_RISK_30_DAYS`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT Q1_TOTAL DOUBLE,
OUT Q2_TOTAL DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

 SELECT SUM(Q1.RESULT)
     INTO Q1_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q1;

 SELECT SUM(Q2.RESULT)
     INTO Q2_TOTAL
FROM
	(SELECT SUM(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND I.INDICATOR_NAME = PINDICATOR_NAME
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER) Q2;
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_DIST_DISTRIBUTION_MAP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_DIST_DISTRIBUTION_MAP`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PQUARTER_FIRST    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
   SET PCODE = '99';
  Set Pdesc =  'Other Vendor related software error.';
   SET PMSG  =  'N';
end;
    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SELECT  R.REGION_NAME AS Q1_REGION_NAME, SUM(RI.Indicator_Value) AS Q1_Indicator_Value
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID	
      AND R.TYPE = 'D'
      AND F.YEAR         = PYEAR_FIRST
      AND F.QUARTER = PQUARTER_FIRST
      AND I.INDICATOR_NAME =PINDICATOR_NAME
GROUP BY RI.REGION_ID
ORDER BY Q1_Indicator_Value DESC;
	
   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_FILTER_DATA` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_FILTER_DATA`(
IN 	 PINDICATOR VARCHAR(100),
IN	 PINDICATOR_TYPE VARCHAR(100),  
IN 	 PPROVIDER VARCHAR(100),
IN     PYEAR_Q1 VARCHAR(15),
IN     PYEAR_Q2 VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';



SELECT F.YEAR ,F.QUARTER, COALESCE(SUM(Indicator_Value),0)  AS RESULT
	FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Indicator_Types IT, organization O
  WHERE CI.FILE_ID   = F.FILE_ID
      AND CI.CI_IID		= I.MWI_ID
      -- AND CI.CI_CID		= C.IC_ID
      AND CI.CI_ITID     = IT.MWIT_ID
	  AND F.ORG_NAME = O.ORG_ABV
	  AND IT.IT_NAME = PINDICATOR_TYPE
	  AND I.INDICATOR_NAME = PINDICATOR
	  AND O.ORG_ABV = COALESCE(PPROVIDER, O.ORG_ABV )
      AND( F.YEAR         between  PYEAR_Q1
	  AND PYEAR_Q2)
		GROUP BY F.YEAR ,F.QUARTER;


   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_LARGEST_PROVIDERS_OF_MICROCREDIT` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_LARGEST_PROVIDERS_OF_MICROCREDIT`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
DECLARE V_TOTAL DOUBLE;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT 
        
            SUM(CI.Indicator_Value)
            INTO 
	V_TOTAL
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND OT.ORGANIZATION_TYPE = 'Other MFPs'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME;

 
	SELECT 
        O.ORGANIZATION_NAME,
        CI.Indicator_Value AS RESULT,
        V_TOTAL/CI.Indicator_Value AS MARKET_SHARE
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND OT.ORGANIZATION_TYPE = 'Other MFPs'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME
    ORDER BY RESULT
LIMIT 5;
 

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MC_HIGHESTGROWTH_DIST_BY_PROVINCE` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MC_HIGHESTGROWTH_DIST_BY_PROVINCE`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 
select distinct Q1.PARENT, Q1.REGION_NAME,Q1.RESULT AS A,(Q1.RESULT - Q2.RESULT) AS NET,IFNULL((((Q1.RESULT - Q2.RESULT)/Q2.RESULT)*100),0) AS PERCENTAGE ,
P.POTENTIAL_RATE,ifnull((((Q1.RESULT)/POTENTIAL_RATE)*100),0) AS PENETRATION_RATE
FROM 
(
SELECT  R.R_ID,R.PARENT, R.REGION_NAME,F.YEAR ,F.QUARTER, SUM(RI.Indicator_Value) AS RESULT
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
		AND F.YEAR         =   PYEAR_FIRST
		AND F.QUARTER =  PQUARTER_FIRST
	   AND I.INDICATOR_NAME =  PINDICATOR_NAME
GROUP BY R.R_ID,R.PARENT, R.REGION_NAME,F.YEAR ,F.QUARTER
order by  R.PARENT,RESULT desc
)Q1,
(
SELECT  R.R_ID,R.PARENT, R.REGION_NAME,F.YEAR ,F.QUARTER, SUM(RI.Indicator_Value) AS RESULT
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
		AND F.YEAR         =   PYEAR_SEC
		AND F.QUARTER =  PQUARTER_SEC
	   AND I.INDICATOR_NAME =  PINDICATOR_NAME
GROUP BY R.R_ID,R.PARENT, R.REGION_NAME,F.YEAR ,F.QUARTER
order by  R.PARENT,RESULT desc
) Q2,POTENTIAL P
WHERE Q1.REGION_NAME = Q2.REGION_NAME
AND Q1.R_ID = P.REGION_ID
AND Q2.R_ID = P.REGION_ID
order by  Q1.PARENT,Q1.RESULT desc
;

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MFPS_WITH_LARGEST_NET_INCREASE` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MFPS_WITH_LARGEST_NET_INCREASE`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
DECLARE V_TOTAL DOUBLE;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT    
     SUM(Q1.RESULT) INTO 	V_TOTAL
FROM
    (SELECT  SUM(CI.Indicator_Value)as RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME
    ORDER BY I.INDICATOR_NAME , F.QUARTER) Q1;

SELECT 
    Q1.ORGANIZATION_NAME,
    Q1.ORG_ABV,
    Q1.ORGANIZATION_TYPE,
    Q1.RESULT -coalesce(Q2.RESULT, 0) AS NET_VALUE,
    IFNULL((((Q1.RESULT - coalesce(Q2.RESULT, 0)) / Q2.RESULT) * 100),0) AS PRCENTAGE,
     (Q1.RESULT/V_TOTAL )  * 100 AS MARKET_SHARE
FROM
(
SELECT   OT.ORGANIZATION_TYPE ,O.ORGANIZATION_NAME,O.ORG_ABV,SUM(CI.Indicator_Value) as RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME, O.ORG_ABV
    ORDER BY I.INDICATOR_NAME , F.QUARTER) Q1
LEFT JOIN
(
SELECT   OT.ORGANIZATION_TYPE  ,O.ORGANIZATION_NAME,O.ORG_ABV,SUM(CI.Indicator_Value) as RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_SEC
            AND F.QUARTER = PQUARTER_SEC
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME, O.ORG_ABV
    ORDER BY I.INDICATOR_NAME , F.QUARTER)Q2
ON
    Q1.ORGANIZATION_NAME = Q2.ORGANIZATION_NAME
order by NET_VALUE desc;
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MFPS_WITH_LARGEST_NET_INCREASE_PRECNT` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MFPS_WITH_LARGEST_NET_INCREASE_PRECNT`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
DECLARE V_TOTAL DOUBLE;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT  
    SUM(Q1.RESULT ) AS NET_VALUE 
INTO 	V_TOTAL

FROM
    (SELECT 
        O.ORGANIZATION_NAME,
            IFNULL(SUM(CI.Indicator_Value), 0) AS RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
       --    AND OT.ORGANIZATION_TYPE = 'Other MFPs'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME
    ORDER BY I.INDICATOR_NAME , F.QUARTER) Q1;
 

 
SELECT 
    Q1.ORGANIZATION_NAME,
     Q1.ORG_ABV,
    Q1.RESULT - coalesce(Q2.RESULT, 0) AS NET_VALUE,
    IFNULL((((Q1.RESULT - coalesce(Q2.RESULT, 0)) / Q2.RESULT) * 100),0) AS PRCENTAGE,
      (Q1.RESULT/V_TOTAL )  * 100 AS MARKET_SHARE
FROM
    (SELECT 
        O.ORGANIZATION_NAME,O.ORG_ABV,
            IFNULL(SUM(CI.Indicator_Value), 0) AS RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
        --    AND OT.ORGANIZATION_TYPE = 'Other MFPs'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME,O.ORG_ABV) Q1
left join
    (SELECT 
        O.ORGANIZATION_NAME,O.ORG_ABV,
            IFNULL(SUM(CI.Indicator_Value), 0) AS RESULT
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_SEC
            AND F.QUARTER = PQUARTER_SEC
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
           -- AND OT.ORGANIZATION_TYPE = 'Other MFPs'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME,O.ORG_ABV) Q2
on
    Q1.ORGANIZATION_NAME = Q2.ORGANIZATION_NAME
ORDER BY 4 DESC;
-- LIMIT 5;
 

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MFPS_WITH_LARGEST_PROVIDERS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MFPS_WITH_LARGEST_PROVIDERS`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
DECLARE V_TOTAL DOUBLE;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT SUM(T.Indicator_Value)
INTO V_TOTAL
FROM (
SELECT 
        SUM(CI.Indicator_Value) AS Indicator_Value
    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME) T;

 
SELECT 
O.ORGANIZATION_NAME, O.ORG_ABV,       
SUM(CI.Indicator_Value) AS INDICATOR_VALUE,
((SUM(CI.Indicator_Value))/V_TOTAL) * 100 AS MARKET_SHARE

    FROM
        Country_Indicators CI, FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT, organization_type OT, organization O
    WHERE
        CI.FILE_ID = F.FILE_ID
            AND CI.CI_IID = I.MWI_ID
            AND CI.CI_CID = C.IC_ID
            AND CI.CI_ITID = IT.MWIT_ID
            AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
            AND O.OTID = OT.otid
            AND F.YEAR = PYEAR_FIRST
            AND F.QUARTER = PQUARTER_FIRST
            AND I.INDICATOR_NAME = PINDICATOR_NAME
            AND IT.IT_NAME = 'Total'
    GROUP BY I.INDICATOR_NAME , F.YEAR , F.QUARTER , OT.ORGANIZATION_TYPE , O.ORGANIZATION_NAME, O.ORG_ABV
    ORDER BY INDICATOR_VALUE  DESC;
-- LIMIT 5;
 

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MFP_LARGEST_GEOGRAPHIC_SPREAD` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MFP_LARGEST_GEOGRAPHIC_SPREAD`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 

SELECT 
   DISTINCT  
    O.ORGANIZATION_NAME,
    O.ORG_ABV,
   COUNT(*) AS RESULT
FROM
		organization O,
		REGIONS R,
		Regional_Indicators RI,
        FILE_DETAILS F
WHERE
				R.R_ID = RI.REGION_ID
        AND RI.FILE_ID = F.FILE_ID
        AND UPPER(F.ORG_NAME) = UPPER(O.ORG_ABV)
         AND RI.Indicator_Value > 0
        AND R.TYPE = 'D'
AND F.YEAR         = PYEAR_FIRST
AND F.QUARTER  = PQUARTER_FIRST
		AND RI.IID IN(13,14) 

GROUP BY  O.ORGANIZATION_NAME , O.ORG_ABV
ORDER BY 3 DESC
LIMIT 5;


 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MICROWATCH` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MICROWATCH`(
IN     PYEAR VARCHAR(15),
IN     PQUARTER VARCHAR(15),
OUT PNO_OF_DISTRICT_C INT,
OUT P_POTENTIAL_PAKISTAN INT,
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SET P_POTENTIAL_PAKISTAN = 0;

SELECT ifnull(POTENTIAL_RATE,0) INTO P_POTENTIAL_PAKISTAN FROM potential where REGION_ID = (select R_ID from regions where REGION_NAME = 'Pakistan') and year = pyear;


SELECT COUNT(Q1.REGION_ID)      INTO PNO_OF_DISTRICT_C
FROM
(
SELECT  DISTINCT REGION_ID

FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
    --   AND RI.CID			= C.IC_ID
	  AND RI.Indicator_Value > 0	
      AND R.TYPE  IN ('D')
     AND F.YEAR         = PYEAR
      AND F.QUARTER = PQUARTER
	--   AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME IN ('Number of offices Fixed', 'Number of offices Mobile','Number of active borrowers','Gross Loan Portfolio','Number of Active Savers','Value of Savings','Number of Policy Holders','Sum Insured') )Q1;

SELECT  C.IC_NAME,  I.INDICATOR_NAME, COALESCE(SUM(Indicator_Value),0)  AS RESULT
	FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
 WHERE CI.FILE_ID   = F.FILE_ID
      AND CI.CI_IID		= I.MWI_ID
      AND CI.CI_CID		= C.IC_ID
      AND CI.CI_ITID     = IT.MWIT_ID
	  AND IT.IT_NAME = 'Total'
      AND F.YEAR         = PYEAR
      AND F.QUARTER = PQUARTER
GROUP BY C.IC_NAME,  I.INDICATOR_NAME;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MIP_AC` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MIP_AC`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';
    truncate table `MI_TEMP`;
insert into `MI_TEMP` (region,ph)  (SELECT    r.REGION_NAME,
     SUM(ri.Indicator_Value) AS indicator_value
FROM
    REGIONS r,
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd
WHERE
   
         r.R_ID = ri.REGION_ID
        AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST
        AND mi.INDICATOR_NAME = 'Number of Active Savers'
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME
ORDER BY indicator_value DESC
LIMIT 5
);

SELECT 
    r.REGION_NAME,
    mt.ph AS Indicator_Value,
    mt.ph - SUM(ri.Indicator_Value)  AS net,
    (( mt.ph - SUM(ri.Indicator_Value)) / mt.ph) * 100 AS per
FROM
    REGIONS r,
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd,
    MI_TEMP mt
WHERE
    r.R_ID = ri.REGION_ID AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER_SEC
        AND fd.YEAR = PYEAR_SEC
        AND mi.INDICATOR_NAME = 'Number of Active Savers'
        AND r.REGION_NAME = mt.region
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME , mt.ph
ORDER BY Indicator_Value DESC
;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MIP_PH` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MIP_PH`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';
    truncate table `MI_TEMP`;
insert into `MI_TEMP` (region,ph)  (SELECT    r.REGION_NAME,
     SUM(ri.Indicator_Value) AS indicator_value
FROM
    REGIONS r,
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd
WHERE
   
         r.R_ID = ri.REGION_ID
        AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME
ORDER BY indicator_value DESC
LIMIT 5
);

SELECT 
    r.REGION_NAME,
    mt.ph AS Indicator_Value,
    mt.ph - SUM(ri.Indicator_Value)  AS net,
    (( mt.ph - SUM(ri.Indicator_Value)) / SUM(ri.Indicator_Value)) * 100 AS per
FROM
    
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd,
	REGIONS r
left join 
    MI_TEMP mt
on  r.REGION_NAME = mt.region
WHERE
    r.R_ID = ri.REGION_ID AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER_SEC
        AND fd.YEAR = PYEAR_SEC
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
        AND r.REGION_NAME = mt.region
        
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME , mt.ph
ORDER BY Indicator_Value DESC
;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MIP_PH_MS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MIP_PH_MS`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE total double;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';
   
   
SELECT 
    SUM(ct.Indicator_Value)
INTO total FROM
    Country_Indicators ct,
    MicroWatch_indicators mi,
    MicroWatch_Indicator_Types mit,
    FILE_DETAILS fd
WHERE
    ct.CI_IID = mi.MWI_ID
        AND ct.CI_ITID = mit.MWIT_ID
        AND ct.FILE_ID = fd.FILE_ID
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
        AND mit.IT_NAME = 'Total'
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST;
   
SELECT 
    O.ORG_ABV AS ORG_NAME,
    ct.Indicator_Value,
    (ct.Indicator_Value / total) * 100 AS ms
FROM
    Country_Indicators ct,
    MicroWatch_indicators mi,
    MicroWatch_Indicator_Types mit,
    FILE_DETAILS fd,
organization O
	
WHERE
    ct.CI_IID = mi.MWI_ID
        AND ct.CI_ITID = mit.MWIT_ID
        AND ct.FILE_ID = fd.FILE_ID
AND fd.ORG_NAME = O.ORG_ABV
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
        AND mit.IT_NAME = 'Total'
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST
ORDER BY ct.Indicator_Value DESC
LIMIT 5;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MIP_PH_NET` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MIP_PH_NET`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';
    truncate table `MI_TEMP`;
insert into `MI_TEMP` (region,ph)  (SELECT    r.REGION_NAME,
     SUM(ri.Indicator_Value) AS indicator_value
FROM
    REGIONS r,
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd
WHERE
   
         r.R_ID = ri.REGION_ID
        AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
       AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME
ORDER BY indicator_value DESC
);

SELECT 
    r.REGION_NAME,
    mt.ph AS Indicator_Value,
    mt.ph-SUM(ri.Indicator_Value)  AS net,
    ((mt.ph- SUM(ri.Indicator_Value)) / SUM(ri.Indicator_Value)) * 100 AS per
FROM
    REGIONS r,
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd,
    MI_TEMP mt
WHERE
    r.R_ID = ri.REGION_ID AND r.TYPE = 'D'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
         AND fd.QUARTER = PQUARTER_SEC
        AND fd.YEAR = PYEAR_SEC
        AND mi.INDICATOR_NAME = 'Number of Policy Holders'
        AND r.REGION_NAME = mt.region
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME , mt.ph
ORDER BY net DESC
limit 5
;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MIP_PH_SI` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MIP_PH_SI`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE total double;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';
   
   
SELECT 
    SUM(ct.Indicator_Value)
INTO total FROM
    Country_Indicators ct,
    MicroWatch_indicators mi,
    MicroWatch_Indicator_Types mit,
    FILE_DETAILS fd
WHERE
    ct.CI_IID = mi.MWI_ID
        AND ct.CI_ITID = mit.MWIT_ID
        AND ct.FILE_ID = fd.FILE_ID
        AND mi.INDICATOR_NAME = 'Sum Insured'
        AND mit.IT_NAME = 'Total'
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST;
   
SELECT 
     O.ORG_ABV AS ORG_NAME ,
    ct.Indicator_Value,
    (ct.Indicator_Value / total) * 100 AS ms
FROM
    Country_Indicators ct,
    MicroWatch_indicators mi,
    MicroWatch_Indicator_Types mit,
    FILE_DETAILS fd,
organization O
WHERE
    ct.CI_IID = mi.MWI_ID
AND fd.ORG_NAME = O.ORG_ABV
        AND ct.CI_ITID = mit.MWIT_ID
        AND ct.FILE_ID = fd.FILE_ID
        AND mi.INDICATOR_NAME = 'Sum Insured'
        AND mit.IT_NAME = 'Total'
        AND fd.QUARTER = PQUARTER_FIRST
        AND fd.YEAR = PYEAR_FIRST
ORDER BY ct.Indicator_Value DESC
LIMIT 5;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MI_PROVISION_BY_MFPS_BY_TYPE` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MI_PROVISION_BY_MFPS_BY_TYPE`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PCAT_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

	 SELECT  IT.IT_NAME,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
       AND F.YEAR = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND C.IC_NAME = PCAT_NAME
       AND  IT.IT_NAME IN('Health','Life','Other')
	GROUP BY  IT.IT_NAME; 

	 SELECT  IT.IT_NAME,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
       AND F.YEAR = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND C.IC_NAME = PCAT_NAME
       AND  IT.IT_NAME IN('Health','Life','Other')
	GROUP BY  IT.IT_NAME; 



    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MP_NO_OF_BRANCHES_UNITS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MP_NO_OF_BRANCHES_UNITS`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 OUT PQ1_TOTAL DOUBLE,
 OUT PQ2_TOTAL  DOUBLE,
 OUT PQ1_MFB DOUBLE,
 OUT PQ2_MFB DOUBLE,
 OUT PQ1_MFI  DOUBLE,
 OUT PQ2_MFI   DOUBLE,
 OUT PQ1_RSP DOUBLE,
 OUT PQ2_RSP DOUBLE,
 OUT PQ1_OTHER_MFPS DOUBLE,
 OUT PQ2_OTHER_MFPS  DOUBLE,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
    SET PCODE = '99';
   Set Pdesc =  'Other Vendor related software error.';
    SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 
 SELECT  IFNULL(SUM(CI.Indicator_Value),0)
   INTO   PQ1_TOTAL 
 FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
      AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
    AND C.IC_NAME = 'Operations'
       AND I.INDICATOR_NAME = 'Number of Offices'
       AND IT.IT_NAME = 'Total';
 
 SELECT  IFNULL(SUM(CI.Indicator_Value),0)
   INTO   PQ2_TOTAL 
 FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
      AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
    AND C.IC_NAME = 'Operations'
       AND I.INDICATOR_NAME = 'Number of Offices'
       AND IT.IT_NAME = 'Total';
 
SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ1_MFB			
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'MFB';

SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ2_MFB	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'MFB';
      
      SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ1_MFI	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'MFI';
      
      SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ2_MFI	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'MFI';
      
      SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ1_RSP	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'RSP';

      SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ2_RSP		
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'RSP';
 
SELECT  IFNULL(SUM(CI.Indicator_Value),0)
INTO PQ1_OTHER_MFPS		
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'Other MFPs';

SELECT  IFNULL(SUM(CI.Indicator_Value),0)
	INTO PQ2_OTHER_MFPS
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
      AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
	  AND O.OTID = OT.otid
    AND F.YEAR         = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME IN ('Fixed','Mobile')
      AND UPPER(OT.ORGANIZATION_TYPE) = 'Other MFPs';      

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_MP_NUMBER_OF_BRANCHES_UNITS
` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_MP_NUMBER_OF_BRANCHES_UNITS
`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC    VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PQ1_TOTAL DOUBLE,
OUT PQ2_TOTAL  DOUBLE,
OUT PQ1_MFB DOUBLE,
OUT PQ2_MFB DOUBLE,
OUT PQ1_MFI  DOUBLE,
OUT PQ2_MFI   DOUBLE,
OUT PQ1_RSP DOUBLE,
OUT PQ2_RSP DOUBLE,
OUT PQ1_OTHER DOUBLE,
OUT PQ2_OTHER  DOUBLE,
OUT PQ1_MFPS DOUBLE,
OUT PQ2_MFPS DOUBLE, 
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
   SET PCODE = '99';
  Set Pdesc =  'Other Vendor related software error.';
   SET PMSG  =  'N';
end;

    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SELECT  IFNULL(SUM(CI.Indicator_Value),0)
	 INTO   PQ1_TOTAL	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
     AND F.YEAR         = PYEAR_FIRST
      AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME = 'Total';

SELECT  IFNULL(SUM(CI.Indicator_Value),0)
	 INTO   PQ2_TOTAL	
FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
WHERE CI.FILE_ID        = F.FILE_ID
      AND CI.CI_IID				    = I.MWI_ID
      AND CI.CI_CID			= C.IC_ID
      AND CI.CI_ITID          = IT.MWIT_ID
     AND F.YEAR         = PYEAR_FIRST
      AND F.QUARTER = PQUARTER_FIRST
	  AND C.IC_NAME = 'Operations'
      AND I.INDICATOR_NAME = 'Number of Offices'
      AND IT.IT_NAME = 'Total';



   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_NUMBER_OF_UPLOADED_FILES` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_NUMBER_OF_UPLOADED_FILES`(
 IN     PYEAR VARCHAR(15),
 IN     PQUARTER VARCHAR(15),
 OUT 	PNUMBER_OF_FILES VARCHAR(15),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';


	select count(*) into PNUMBER_OF_FILES from file_details where YEAR = PYEAR and QUARTER = PQUARTER; -- like concat(PQUARTER,'%');

 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_NUMBER_OF_UPLOADED_FILES_PEER_GROUP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_NUMBER_OF_UPLOADED_FILES_PEER_GROUP`(
 IN     PYEAR VARCHAR(15),
 IN     PQUARTER VARCHAR(15),
 OUT 	PNUMBER_OF_FILES_MFI VARCHAR(15),
 OUT 	PNUMBER_OF_FILES_MFB VARCHAR(15),
 OUT 	PNUMBER_OF_FILES_RSP VARCHAR(15),
 OUT 	PNUMBER_OF_FILES_OTHER VARCHAR(15),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';


		select count(*) into PNUMBER_OF_FILES_MFB from file_details where ORG_NAME in 
        (select ORG_ABV from organization where otid in (select otid from organization_type where ORGANIZATION_TYPE = 'MFB')) and QUARTER = PQUARTER
		and year  = PYEAR;
        
        select count(*) into PNUMBER_OF_FILES_MFI from file_details where ORG_NAME in 
        (select ORG_ABV from organization where otid in (select otid from organization_type where ORGANIZATION_TYPE = 'MFI')) and QUARTER = PQUARTER
		and year  = PYEAR;
        
        select count(*) into PNUMBER_OF_FILES_RSP from file_details where ORG_NAME in 
        (select ORG_ABV from organization where otid in (select otid from organization_type where ORGANIZATION_TYPE = 'RSP')) and QUARTER = PQUARTER
		and year  = PYEAR;
        
        select count(*) into PNUMBER_OF_FILES_OTHER from file_details where ORG_NAME in 
        (select ORG_ABV from organization where otid in (select otid from organization_type where ORGANIZATION_TYPE = 'Other MFPs')) and QUARTER = PQUARTER
		and year  = PYEAR;
        
 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_OAP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_OAP`(
IN     PYEAR VARCHAR(15),
IN     PQUARTER VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SELECT 
    SUM(ri.Indicator_Value) AS indicator_value,
    r.REGION_NAME,
    mc.IC_NAME,
    mi.INDICATOR_NAME,
	COALESCE(P.POTENTIAL_RATE,0)  AS POTENTIAL_RATE
FROM
	Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd,
	REGIONS r
LEFT JOIN
	potential P
ON (P.REGION_ID = r.R_ID AND P.YEAR = PYEAR)
WHERE
    r.R_ID = ri.REGION_ID AND (r.TYPE = 'P'OR r.REGION_NAME = 'ICT')
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER
        AND fd.YEAR = PYEAR
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME

UNION
SELECT 
    SUM(ri.Indicator_Value) AS indicator_value,
    r.REGION_NAME,
    mc.IC_NAME,
    mi.INDICATOR_NAME,
	COALESCE(P.POTENTIAL_RATE,0)
FROM
	Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd,
	REGIONS r
LEFT JOIN
	potential P
ON (P.REGION_ID = r.R_ID AND P.YEAR = PYEAR)
WHERE
     r.R_ID = ri.REGION_ID AND r.TYPE  = 'GT'
        AND mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER
        AND fd.YEAR = PYEAR
GROUP BY r.REGION_NAME , mc.IC_NAME , mi.INDICATOR_NAME;
   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_ODL` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_ODL`(
IN     PYEAR VARCHAR(15),
IN     PQUARTER VARCHAR(15),
IN     PPROVINCE_NAME    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
end;

    SET PCODE = '99';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

    SELECT   
					SUM(T.INDICATOR_VALUE) AS INDICATOR_VALUE,
                    R.REGION_NAME,
					  	
					  T.IC_NAME,
					  T.INDICATOR_NAME,
					  COALESCE(P.POTENTIAL_RATE,0) AS POTENTIAL_RATE
		 FROM  REGIONS R

LEFT JOIN 
						  (
							 SELECT RI.INDICATOR_VALUE AS INDICATOR_VALUE,
									MC.IC_NAME,
									MI.INDICATOR_NAME,
									RI.REGION_ID
							   FROM MICROWATCH_CATEGORY MC,
									MICROWATCH_INDICATORS MI,
									FILE_DETAILS FD,
									REGIONAL_INDICATORS RI
							  WHERE MC.IC_ID = RI.CID
								AND RI.IID = MI.MWI_ID
								AND FD.FILE_ID = RI.FILE_ID
								AND FD.QUARTER =PQUARTER
								AND FD.YEAR =PYEAR
						  )  T
            ON R.R_ID = T.REGION_ID

LEFT JOIN POTENTIAL P
              ON (P.REGION_ID = R.R_ID AND P.YEAR = PYEAR)
     
     WHERE R.PARENT = PPROVINCE_NAME
           AND R.TYPE      = 'D'
 GROUP BY REGION_NAME , IC_NAME , INDICATOR_NAME;

SELECT
    SUM(T.INDICATOR_VALUE) AS INDICATOR_VALUE,
    R.REGION_NAME,
    T.ORG_NAME AS ORG_NAME
FROM REGIONS R
LEFT JOIN
    (
        SELECT
            RI.INDICATOR_VALUE AS INDICATOR_VALUE,
            RI.REGION_ID,
            O.ORG_ABV AS ORG_NAME
        FROM
            REGIONAL_INDICATORS RI,
            MICROWATCH_CATEGORY MC,
            MICROWATCH_INDICATORS MI,
            FILE_DETAILS FD,
            ORGANIZATION O
        WHERE MC.IC_ID = RI.CID
        AND RI.IID = MI.MWI_ID
        AND FD.FILE_ID = RI.FILE_ID
        AND FD.QUARTER = PQUARTER
        AND FD.YEAR = PYEAR
        AND FD.ORG_NAME = O.ORG_ABV
    ) T
ON R.R_ID = T.REGION_ID
WHERE R.PARENT = PPROVINCE_NAME
AND R.TYPE = 'D'
GROUP BY REGION_NAME, ORG_NAME
HAVING SUM(T.INDICATOR_VALUE)>0;


 SELECT    SUM(T.Indicator_Value) as indicator_value,
    r.REGION_NAME,
	T.IC_NAME,
    T.INDICATOR_NAME
FROM REGIONS r

LEFT JOIN 
(
SELECT 
    ri.Indicator_Value as indicator_value,
    RI.REGION_ID,
    mc.IC_NAME,
    mi.INDICATOR_NAME
FROM
   
    Regional_Indicators ri,
    MicroWatch_Category mc,
    MicroWatch_indicators mi,
    FILE_DETAILS fd
WHERE mc.IC_ID = ri.cid
        AND ri.IID = mi.MWI_ID
        AND fd.FILE_ID = ri.FILE_ID
        AND fd.QUARTER = PQUARTER
        AND fd.YEAR = PYEAR

) T
ON  r.R_ID = T.REGION_ID
WHERE
r.REGION_NAME = PPROVINCE_NAME
GROUP BY REGION_NAME , IC_NAME , INDICATOR_NAME ;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_PROVISION_BY_MFPS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_PROVISION_BY_MFPS`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PCAT_NAME VARCHAR(45),
OUT  TOTAL_1 INT,
OUT  TOTAL_2 INT,
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT COUNT(*) INTO TOTAL_1 FROM FILE_DETAILS WHERE   YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST;

SELECT COUNT(*) INTO TOTAL_2 FROM FILE_DETAILS WHERE   YEAR = PYEAR_SEC AND QUARTER =PQUARTER_SEC;

SELECT Q1.IT_NAME,COUNT(Q1.RESULT) AS RESULT,Q1.YEAR ,Q1.QUARTER
FROM (
		 SELECT IT.IT_NAME,F.FILE_NAME,F.YEAR ,F.QUARTER,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT 
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND C.IC_NAME = PCAT_NAME
		AND CI.Indicator_Value > 0 
	GROUP BY IT.IT_NAME,F.FILE_NAME,F.YEAR ,F.QUARTER)Q1
GROUP BY Q1.IT_NAME
UNION
SELECT Q1.IT_NAME,COUNT(Q1.RESULT) AS RESULT,Q1.YEAR ,Q1.QUARTER
FROM (
		 SELECT IT.IT_NAME,F.FILE_NAME,F.YEAR ,F.QUARTER,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT 
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND C.IC_NAME = PCAT_NAME
		AND CI.Indicator_Value > 0 
	GROUP BY IT.IT_NAME,F.FILE_NAME,F.YEAR ,F.QUARTER)Q1
GROUP BY Q1.IT_NAME;


/*	
SELECT COUNT(Q1.RESULT)
FROM (
		 SELECT F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORGANIZATION_NAME)
		AND O.OTID = OT.otid
			AND F.YEAR = '2017'       
			AND F.QUARTER = 'Q3 (1JUL-30SEP)'
       AND C.IC_NAME = 'Micro-Savings'
            AND IT.IT_NAME = 'Total'
       AND CI.Indicator_Value > 0
	GROUP BY F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE, O.ORGANIZATION_NAME
	ORDER BY F.QUARTER)Q1





SELECT C.IC_NAME,I.INDICATOR_NAME, IT.IT_NAME,F.YEAR ,F.QUARTER,COUNT(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND C.IC_NAME = PCAT_NAME
            AND IT.IT_NAME <> 'Total'
       AND CI.Indicator_Value > 0
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER; 


	SELECT C.IC_NAME,I.INDICATOR_NAME, IT.IT_NAME,F.YEAR ,F.QUARTER,COUNT(CI.Indicator_Value) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND F.YEAR = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND C.IC_NAME = PCAT_NAME
            AND IT.IT_NAME <> 'Total'
       AND CI.Indicator_Value > 0
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.QUARTER; 
*/
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_PROVISION_BY_MFPSBY_PEER_GROUP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_PROVISION_BY_MFPSBY_PEER_GROUP`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PCAT_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';


SELECT Q1.ORGANIZATION_TYPE,COUNT(Q1.RESULT) AS RESULT,Q1.YEAR ,Q1.QUARTER
FROM (
		 SELECT F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
       AND F.YEAR = PYEAR_FIRST
       AND F.QUARTER = PQUARTER_FIRST
       AND C.IC_NAME = PCAT_NAME
            AND IT.IT_NAME = 'Total'
       AND CI.Indicator_Value > 0
	GROUP BY F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE, O.ORGANIZATION_NAME)Q1
GROUP BY Q1.ORGANIZATION_TYPE,Q1.YEAR ,Q1.QUARTER
UNION 
SELECT Q2.ORGANIZATION_TYPE,COUNT(Q2.RESULT) AS RESULT,Q2.YEAR ,Q2.QUARTER
FROM (
		 SELECT F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,count(CI.Indicator_Value) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
       AND F.YEAR = PYEAR_SEC
       AND F.QUARTER = PQUARTER_SEC
       AND C.IC_NAME = PCAT_NAME
            AND IT.IT_NAME = 'Total'
       AND CI.Indicator_Value > 0
	GROUP BY F.FILE_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE, O.ORGANIZATION_NAME)Q2
GROUP BY Q2.ORGANIZATION_TYPE,Q2.YEAR ,Q2.QUARTER;


    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_REPORTING_ORGANIZATIONS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_REPORTING_ORGANIZATIONS`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN

 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 PDESC = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';

SELECT 
O.ORGANIZATION_NAME, OT.ORGANIZATION_TYPE,F.YEAR,F.QUARTER,
CASE WHEN F.FILE_NAME IS NOT NULL THEN '1' ELSE '0' END AS RESULT 
FROM organization_type OT,organization O 
LEFT JOIN
(SELECT
FILE_NAME,
ORG_NAME,
YEAR,
QUARTER
FROM FILE_DETAILS
WHERE YEAR IN (PYEAR_FIRST) AND QUARTER IN  (PQUARTER_FIRST)) F
ON O.ORG_ABV = F.ORG_NAME
where O.ENDED IS NULL
 AND OT.otid = O.OTID
ORDER BY O.ORGANIZATION_NAME;


    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_SUMMARY_OF_MC_PROVISION` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_SUMMARY_OF_MC_PROVISION`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 

	SELECT C.IC_NAME,I.INDICATOR_NAME, IT.IT_NAME,F.YEAR ,F.QUARTER,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	  FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT
	 WHERE CI.FILE_ID        = F.FILE_ID
       AND CI.CI_IID        = I.MWI_ID
       AND CI.CI_CID   = C.IC_ID
       AND CI.CI_ITID          = IT.MWIT_ID
       AND I.INDICATOR_NAME = PINDICATOR_NAME
      -- AND IT.IT_NAME IN ('Secured2','Total','Unsecured3','Group','Individual','Micro/Small Enterprise1','Fixed','Mobile')
      AND F.FILE_ID IN (select jk.FILE_ID from
    (
	   (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) union
		
		(SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC ) )as jk
    )
	GROUP BY  IT.IT_NAME,I.INDICATOR_NAME,C.IC_NAME ,F.YEAR ,F.QUARTER
    ORDER BY F.YEAR,F.QUARTEr asc;



 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_SUMMARY_OF_MC_PROVISION_PEERGROUP` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_SUMMARY_OF_MC_PROVISION_PEERGROUP`(
 IN     PYEAR_FIRST VARCHAR(15),
 IN     PYEAR_SEC    VARCHAR(15),
 IN     PQUARTER_FIRST VARCHAR(15),
 IN     PQUARTER_SEC    VARCHAR(15),
 IN     PINDICATOR_NAME VARCHAR(45),
 OUT PCODE       VARCHAR(10),
 OUT PDESC       VARCHAR(500),
 OUT PMSG        VARCHAR(10))
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
	 GET DIAGNOSTICS CONDITION 1
		 Pdesc = MESSAGE_TEXT;
       -- PCODE = RETURNED_SQLSTATE, Pdesc = MESSAGE_TEXT;
   SET PCODE = '99';
   SET PMSG  =  'N';
 end;
 
     SET PCODE = '-1';
     SET PDESC = 'Failure';
     SET PMSG  = 'N';
 


	 SELECT  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE,IFNULL(SUM(CI.Indicator_Value),0) AS RESULT
	   FROM Country_Indicators CI,  FILE_DETAILS F, MicroWatch_indicators I, MicroWatch_Category C, MicroWatch_Indicator_Types IT , organization_type OT, organization O
	  WHERE CI.FILE_ID        = F.FILE_ID
		AND CI.CI_IID				    = I.MWI_ID
		AND CI.CI_CID			= C.IC_ID
		AND CI.CI_ITID          = IT.MWIT_ID
		AND UPPER(F.ORG_NAME)	= UPPER(O.ORG_ABV)
		AND O.OTID = OT.otid
		 AND F.FILE_ID IN (select jk.FILE_ID from
    (
	   (SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_FIRST AND QUARTER = PQUARTER_FIRST) union
		
		(SELECT FILE_ID FROM FILE_DETAILS WHERE YEAR = PYEAR_SEC AND QUARTER = PQUARTER_SEC ) )as jk
    )
    and o.ended is null
		AND I.INDICATOR_NAME =  PINDICATOR_NAME
        AND  IT.IT_NAME = 'Total'
	GROUP BY  I.INDICATOR_NAME,F.YEAR ,F.QUARTER,OT.ORGANIZATION_TYPE
	ORDER BY F.YEAR,F.QUARTER;


 
    SET PCODE =  '00';
    SET PDESC  =  'Success';
    SET PMSG   =  'Y';
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_T5DIST_DISTRIBUTION_ACTIVE_BORROWERS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_T5DIST_DISTRIBUTION_ACTIVE_BORROWERS`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC    VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
   SET PCODE = '99';
  Set Pdesc =  'Other Vendor related software error.';
   SET PMSG  =  'N';
end;
    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SELECT Q1.REGION_NAME,Q1. Indicator_Value AS Q1_Indicator_Value,Q2.Indicator_Value AS Q2_Indicator_Value,(Q1. Indicator_Value -Q2.Indicator_Value) AS NET_VALUE,
((Q1. Indicator_Value -Q2.Indicator_Value)/Q2.Indicator_Value)*100 as PERCENTAGE
FROM
(SELECT  R.REGION_NAME AS REGION_NAME, IFNULL(SUM(RI.Indicator_Value),0) AS Indicator_Value
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
      AND F.YEAR         = PYEAR_FIRST
      AND F.QUARTER = PQUARTER_FIRST
      AND I.INDICATOR_NAME = 'Number of Active Borrowers'
GROUP BY RI.REGION_ID) Q1
LEFT JOIN
(SELECT  R.REGION_NAME AS REGION_NAME, IFNULL(SUM(RI.Indicator_Value),0) AS Indicator_Value
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
      AND F.YEAR         = PYEAR_SEC
      AND F.QUARTER = PQUARTER_SEC
      AND I.INDICATOR_NAME = 'Number of Active Borrowers' 
GROUP BY RI.REGION_ID)Q2
ON Q1.REGION_NAME = Q2.REGION_NAME
ORDER BY NET_VALUE DESC;

	
   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RPT_T5DIST_DISTRIBUTION_ACTIVE_SAVERS` */;
DELIMITER ;;
CREATE DEFINER=`microwb1_pmn`@`%` PROCEDURE `RPT_T5DIST_DISTRIBUTION_ACTIVE_SAVERS`(
IN     PYEAR_FIRST VARCHAR(15),
IN     PYEAR_SEC    VARCHAR(15),
IN     PQUARTER_FIRST VARCHAR(15),
IN     PQUARTER_SEC    VARCHAR(15),
OUT PCODE       VARCHAR(10),
OUT PDESC       VARCHAR(500),
OUT PMSG        VARCHAR(10))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
   SET PCODE = '99';
	set	 PDESC = MESSAGE_TEXT;
   SET PMSG  =  'N';
end;
    SET PCODE = '-1';
    SET PDESC = 'Failure';
    SET PMSG  = 'N';

SELECT Q1.REGION_NAME,Q1. Indicator_Value AS Q1_Indicator_Value,
Q2.Indicator_Value AS Q2_Indicator_Value,(Q1. Indicator_Value -Q2.Indicator_Value) AS NET_VALUE,
((Q1. Indicator_Value -Q2.Indicator_Value)/Q2.Indicator_Value)*100 as PERCENTAGE
FROM
(SELECT  R.REGION_NAME AS REGION_NAME, IFNULL(SUM(RI.Indicator_Value),0) AS Indicator_Value
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
      AND F.YEAR         = PYEAR_FIRST
      AND F.QUARTER = PQUARTER_FIRST
      AND I.INDICATOR_NAME = 'Number of Active Savers'
GROUP BY RI.REGION_ID) Q1
LEFT JOIN
(SELECT  R.REGION_NAME AS REGION_NAME, IFNULL(SUM(RI.Indicator_Value),0) AS Indicator_Value
FROM Regional_Indicators RI, REGIONS R, FILE_DETAILS F, MicroWatch_indicators I
WHERE RI.REGION_ID = R.R_ID
      AND RI.FILE_ID        = F.FILE_ID
      AND RI.IID				= I.MWI_ID
      AND R.TYPE ='D'
      AND F.YEAR         = PYEAR_SEC
      AND F.QUARTER = PQUARTER_SEC
      AND I.INDICATOR_NAME = 'Number of Active Savers'
GROUP BY RI.REGION_ID)Q2
ON Q1.REGION_NAME = Q2.REGION_NAME
ORDER BY NET_VALUE DESC;

   SET PCODE =  '00';
   SET PDESC  =  'Success';
   SET PMSG   =  'Y';

END ;;
DELIMITER ;


