text: 
  -- DROP PROCEDURE IF EXISTS `usp_Root_Cause_Analysis`;
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_Root_Cause_Analysis`(var_oe int)
  BEGIN
  
  DECLARE DONE,VAR_STAT,VAR_IDX,VAR_RCOR, mit_ex_r, mit_ex_c, mit_idx_ex INT;
  DECLARE LOG_STAT VARCHAR(3);
  DECLARE VAR_UNAME VARCHAR(30);
  DECLARE VAR_AGR_NAME, VAR_MYDB VARCHAR(100);
  DECLARE CUR_UNAM CURSOR FOR SELECT DISTINCT U.UNAME FROM UCONFLICTS_ORG U INNER JOIN UCOMPLETED C ON U.UNAME=C.UNAME WHERE C.EOCSTAT=0;
  DECLARE CUR_OR CURSOR FOR SELECT DISTINCT R.AGR_NAME FROM RCONFLICTS_ORG R INNER JOIN RCOMPLETED C ON R.AGR_NAME=C.AGR_NAME WHERE C.EOCSTAT=0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
  
  SELECT VARIABLE_VALUE INTO LOG_STAT FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'GENERAL_LOG';
  IF LOG_STAT = 'ON' THEN
      SET GLOBAL GENERAL_LOG = 'OFF';
  END IF;
  
  SELECT DATABASE() INTO VAR_MYDB;
  SELECT COUNT(*) INTO VAR_STAT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=VAR_MYDB AND TABLE_NAME='UCOMPLETED' AND COLUMN_NAME='EOCStat';
  IF VAR_STAT = 0 THEN
    ALTER TABLE ucompleted ADD COLUMN `EOCStat` INTEGER DEFAULT 0;
  END IF;
  
  DROP TABLE IF EXISTS UCONFLICTS_COL;
  CREATE TABLE UCONFLICTS_COL SELECT U.UNAME,U.CONFLICTID, C.VALUE TCODE1, D.VALUE TCODE2, E.VALUE TCODE3 FROM UCONFLICTS U, CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_C E, CONFLICTS_FIRST_CNT F WHERE C.CONFLICTID=U.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND D.CONFLICTID=E.CONFLICTID AND D.CONFLICTID=F.CONFLICTID AND C.VALUE<>D.VALUE AND D.VALUE<>E.VALUE AND C.VALUE<>E.VALUE AND F.COUNT=3 GROUP BY 1,2;
  INSERT INTO UCONFLICTS_COL (UNAME,CONFLICTID,TCODE1,TCODE2) SELECT DISTINCT U.UNAME,U.CONFLICTID,C.VALUE,D.VALUE FROM UCONFLICTS U,CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_FIRST_CNT F WHERE U.CONFLICTID=C.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND C.VALUE<>D.VALUE AND D.CONFLICTID=F.CONFLICTID AND F.COUNT=2 GROUP BY 1,2;
  CREATE INDEX IDX_UCOL ON UCONFLICTS_COL(UNAME,CONFLICTID,TCODE1,TCODE2,TCODE3);
  
  /* ==== Below RCONFLICTS_COL is added on 22March 2021 by @Hammad */
  
  DROP TABLE IF EXISTS RCONFLICTS_COL; 
  CREATE TABLE RCONFLICTS_COL SELECT U.AGR_NAME,U.CONFLICTID, C.VALUE TCODE1, D.VALUE TCODE2, E.VALUE TCODE3 FROM RCONFLICTS U, CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_C E, CONFLICTS_FIRST_CNT F WHERE C.CONFLICTID=U.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND D.CONFLICTID=E.CONFLICTID AND D.CONFLICTID=F.CONFLICTID AND C.VALUE<>D.VALUE AND D.VALUE<>E.VALUE AND C.VALUE<>E.VALUE AND F.COUNT=3 GROUP BY 1,2;
  INSERT INTO RCONFLICTS_COL (AGR_NAME,CONFLICTID,TCODE1,TCODE2) SELECT DISTINCT U.AGR_NAME,U.CONFLICTID,C.VALUE,D.VALUE FROM RCONFLICTS U,CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_FIRST_CNT F WHERE U.CONFLICTID=C.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND C.VALUE<>D.VALUE AND D.CONFLICTID=F.CONFLICTID AND F.COUNT=2 GROUP BY 1,2;
  CREATE INDEX IDX_UCOL ON RCONFLICTS_COL(AGR_NAME,CONFLICTID,TCODE1,TCODE2,TCODE3);
  
  /* === Line ADDED By Manish Kr === */
  INSERT INTO `procedure_message` (`id`, `procedure_name`, `message`, `create_datetime`) VALUES (NULL, 'Root Cause Analyis', 'Removing Temporary Tables..', current_timestamp());
  
  IF VAR_OE = 1 THEN
      DROP TABLE IF EXISTS ORG_CHECK_USER_SRC;
      DROP TABLE IF EXISTS ORG_CHECK_TCODE;
      DROP TABLE IF EXISTS ORG_CHECK_USER;
      DROP TABLE IF EXISTS ORG_CHECKED_USER;
      DROP TABLE IF EXISTS ORG_CHECK_ROLE_SRC;
      DROP TABLE IF EXISTS ORG_CHECK_ROLE;
      DROP TABLE IF EXISTS ORG_CHECKED_ROLE;
      DROP TABLE IF EXISTS U_CONF;
      DROP TABLE IF EXISTS U_RCO;
      DROP TABLE IF EXISTS R_CONF;
      DROP TABLE IF EXISTS R_RCO;
  
  
      INSERT INTO UCONFLICTS_ORG SELECT DISTINCT U.* FROM UCONFLICTS_ALL U INNER JOIN UCOMPLETED C ON U.UNAME=C.UNAME WHERE EOCStat = 0;
      SELECT COUNT(*) INTO VAR_IDX FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=VAR_MYDB AND TABLE_NAME='UCONFLICTS_ORG' AND INDEX_NAME='IDX_UO';
      IF VAR_IDX=0 THEN
        CREATE INDEX IDX_UO ON UCONFLICTS_ORG(UNAME,CONFLICTID);
      END IF;
      DROP TABLE IF EXISTS UCONFLICTS_ORG_TEMP;
      CREATE TABLE UCONFLICTS_ORG_TEMP SELECT DISTINCT * FROM UCONFLICTS_ORG;
      TRUNCATE UCONFLICTS_ORG;
      INSERT INTO UCONFLICTS_ORG SELECT * FROM UCONFLICTS_ORG_TEMP;
      DROP TABLE IF EXISTS UCONFLICTS_ORG_TEMP;
  
      /* === Line ADDED By Manish Kr === */
      INSERT INTO `procedure_message` (`id`, `procedure_name`, `message`, `create_datetime`) VALUES (NULL, 'Root Cause Analyis', 'Creating Temporary Tables..', current_timestamp());
  
      CREATE TABLE ORG_CHECK_TCODE(`CONFLICTID` VARCHAR(12),`TCODE` VARCHAR(50)) ENGINE=MYISAM DEFAULT CHARSET=UTF8;
      CREATE INDEX IDX_OCT ON ORG_CHECK_TCODE(CONFLICTID,TCODE);
      CREATE TABLE ORG_CHECK_USER_SRC (`UNAME` VARCHAR(20),`CONFLICTID` VARCHAR(13),`TCODE` VARCHAR(50),`FIELD` VARCHAR(25)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCUS ON ORG_CHECK_USER_SRC(UNAME,CONFLICTID,FIELD);
      CREATE TABLE ORG_CHECK_USER (`UNAME` VARCHAR(20),`CONFLICTID` VARCHAR(13),`FIELD` VARCHAR(25), TCODE_CNT INT(5)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCU ON ORG_CHECK_USER(UNAME,CONFLICTID,FIELD);
      CREATE TABLE ORG_CHECKED_USER (`UNAME` VARCHAR(20),`CONFLICTID` VARCHAR(13),`FLD_CNT` INT(5)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCDU ON ORG_CHECKED_USER(UNAME,CONFLICTID);
      CREATE TABLE U_RCO SELECT UNAME,TCODE,OBJCT,FIELD,`FROM`,`TO` FROM ROOT_CAUSE_ORG LIMIT 0;
      CREATE INDEX IDX_URCO ON U_RCO(UNAME,TCODE,OBJCT,FIELD,`FROM`,`TO`);
  
      OPEN CUR_UNAM;
      LOOP_UNAM:LOOP
          FETCH CUR_UNAM INTO VAR_UNAME;
          IF DONE = 1 THEN
              LEAVE LOOP_UNAM;
          END IF;
  
          INSERT INTO U_RCO SELECT DISTINCT UNAME,TCODE,OBJCT,FIELD,`FROM`,`TO` FROM ROOT_CAUSE_ORG WHERE UNAME=VAR_UNAME;
          CREATE TABLE U_CONF SELECT c.conflictid, c.value tcode1, d.value tcode2, e.value tcode3 FROM conflicts_c c, conflicts_c d, conflicts_c e, conflicts_first_cnt f, uconflicts_ALL u WHERE c.conflictid=d.conflictid AND d.conflictid=f.conflictid AND d.conflictid=e.conflictid AND c.value<>d.value AND d.value<>e.value AND c.value<>e.value AND f.conflictid=u.conflictid AND f.count=3 AND u.uname=VAR_UNAME GROUP BY 1;
          INSERT INTO U_CONF (CONFLICTID,TCODE1,TCODE2) SELECT C.CONFLICTID,C.VALUE,D.VALUE FROM CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_FIRST_CNT F, UCONFLICTS_ALL U WHERE C.CONFLICTID=D.CONFLICTID AND C.VALUE<>D.VALUE AND D.CONFLICTID=F.CONFLICTID AND F.CONFLICTID=U.CONFLICTID AND F.COUNT=2 AND U.UNAME=VAR_UNAME GROUP BY 1;
  
          INSERT INTO ORG_CHECK_TCODE SELECT DISTINCT C.CONFLICTID, VALUE TCODE
            FROM CONFLICTS_C C INNER JOIN (SELECT * FROM UCONFLICTS_ORG WHERE UNAME = VAR_UNAME) U
            ON C.CONFLICTID=U.CONFLICTID;
  
          INSERT INTO ORG_CHECK_USER_SRC SELECT DISTINCT VAR_UNAME, U.CONFLICTID, U.TCODE,R.FIELD
              FROM ORG_CHECK_TCODE U, (SELECT * FROM ROOT_CAUSE_ORG WHERE UNAME=VAR_UNAME) R
              WHERE U.TCODE=R.TCODE;
  
          INSERT INTO ORG_CHECK_USER SELECT DISTINCT UNAME, CONFLICTID, FIELD, COUNT(DISTINCT TCODE) TCODE_CNT
              FROM ORG_CHECK_USER_SRC O GROUP BY 1,2,3 HAVING COUNT(DISTINCT TCODE) > 1;
  
          INSERT INTO ORG_CHECKED_USER SELECT U.UNAME, U.CONFLICTID, COUNT(DISTINCT R.`FIELD`) FLD_CNT
          FROM ORG_CHECK_USER U, U_RCO R, U_RCO S, U_CONF C
          WHERE U.TCODE_CNT = 2 AND R.UNAME = U.UNAME AND S.UNAME = U.UNAME AND C.CONFLICTID = U.CONFLICTID AND R.TCODE = C.tcode1
          AND S.TCODE = C.tcode2 AND R.`FIELD` = u.`FIELD` AND S.`FIELD` = u.`FIELD` AND (R.FROM BETWEEN S.FROM AND S.TO OR R.TO BETWEEN S.FROM AND S.TO OR S.FROM BETWEEN R.FROM AND R.TO OR S.TO BETWEEN R.FROM AND R.TO)
          GROUP BY 1,2 ORDER BY 1;
  
          INSERT INTO ORG_CHECKED_USER SELECT U.UNAME, U.CONFLICTID, COUNT(DISTINCT R.`FIELD`) FLD_CNT
          FROM ORG_CHECK_USER U, U_RCO R, U_RCO S, U_RCO T, U_CONF C
              WHERE U.TCODE_CNT = 3 AND S.UNAME=T.UNAME AND U.UNAME = R.UNAME AND R.UNAME=S.UNAME AND U.CONFLICTID = C.CONFLICTID
              AND C.TCODE1 = R.TCODE AND C.TCODE2 = S.TCODE AND C.TCODE3 = T.TCODE AND R.`FIELD` = u.`FIELD` AND S.`FIELD` = u.`FIELD` AND T.`FIELD`= U.`FIELD` AND
              (R.FROM BETWEEN S.FROM AND S.TO OR R.TO BETWEEN S.FROM AND S.TO OR S.FROM BETWEEN R.FROM AND R.TO OR S.TO BETWEEN R.FROM AND R.TO)
              AND (S.FROM BETWEEN T.FROM AND T.TO OR S.TO BETWEEN T.FROM AND T.TO OR T.FROM BETWEEN S.FROM AND S.TO OR T.TO BETWEEN S.FROM AND S.TO)
              AND (R.FROM BETWEEN T.FROM AND T.TO OR R.TO BETWEEN T.FROM AND T.TO OR T.FROM BETWEEN R.FROM AND R.TO OR T.TO BETWEEN R.FROM AND R.TO)
              GROUP BY 1,2 ORDER BY 1;
  
          DELETE C FROM UCONFLICTS_ORG C INNER JOIN ORG_CHECK_USER U ON U.UNAME=C.UNAME AND U.CONFLICTID=C.CONFLICTID LEFT JOIN ORG_CHECKED_USER D ON U.CONFLICTID=D.CONFLICTID WHERE FLD_CNT IS NULL;
          DELETE U FROM UCONFLICTS_ORG U, (SELECT UNAME, CONFLICTID, COUNT(FIELD) FLD_CNT FROM ORG_CHECK_USER GROUP BY 1,2) O, ORG_CHECKED_USER D
              WHERE O.UNAME=D.UNAME AND D.UNAME=U.UNAME AND O.CONFLICTID=D.CONFLICTID AND D.CONFLICTID=U.CONFLICTID AND O.FLD_CNT<>D.FLD_CNT;
  
          TRUNCATE ORG_CHECK_USER;
          TRUNCATE ORG_CHECK_TCODE;
          TRUNCATE ORG_CHECK_USER_SRC;
          TRUNCATE ORG_CHECKED_USER;
          DROP TABLE U_CONF;
          TRUNCATE U_RCO;
         /* UPDATE UCOMPLETED SET EOCStat=1 WHERE UNAME=VAR_UNAME; */
  	    UPDATE UCOMPLETED SET EOCStat=1, DtStamp=current_timestamp() WHERE UNAME=VAR_UNAME;
          
      END LOOP;
  
      DELETE FROM UCONFLICTS;
      INSERT INTO UCONFLICTS SELECT DISTINCT * FROM UCONFLICTS_ORG;
  
      DROP TABLE IF EXISTS UCONFLICTS_TMP;
      CREATE TABLE UCONFLICTS_TMP SELECT DISTINCT * FROM UCONFLICTS;
      TRUNCATE UCONFLICTS;
      INSERT INTO UCONFLICTS SELECT * FROM UCONFLICTS_TMP;
      DROP TABLE IF EXISTS UCONFLICTS_TMP;
  
      DROP TABLE IF EXISTS ORG_CHECK_TCODE;
      DROP TABLE IF EXISTS ORG_CHECK_USER_SRC;
      DROP TABLE IF EXISTS ORG_CHECK_USER;
      DROP TABLE IF EXISTS ORG_CHECKED_USER;
      DROP TABLE IF EXISTS U_CONF;
      DROP TABLE IF EXISTS U_RCO;
  
      SET DONE = 0;
  
      SELECT COUNT(*) INTO VAR_STAT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=VAR_MYDB AND TABLE_NAME='RCOMPLETED' AND COLUMN_NAME='EOCStat';
      IF VAR_STAT = 0 THEN
          ALTER TABLE RCOMPLETED ADD COLUMN `EOCStat` INTEGER DEFAULT 0;
      END IF;
  
      
      INSERT INTO RCONFLICTS_ORG SELECT DISTINCT U.* FROM RCONFLICTS_ALL U INNER JOIN RCOMPLETED C ON U.AGR_NAME=C.AGR_NAME WHERE EOCStat = 0;
      DROP TABLE IF EXISTS RCONFLICTS_ORG_TEMP;
      CREATE TABLE RCONFLICTS_ORG_TEMP SELECT DISTINCT * FROM RCONFLICTS_ORG;
      TRUNCATE RCONFLICTS_ORG;
      INSERT INTO RCONFLICTS_ORG SELECT * FROM RCONFLICTS_ORG_TEMP;
      DROP TABLE IF EXISTS RCONFLICTS_ORG_TEMP;
      SELECT COUNT(*) INTO VAR_IDX FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=VAR_MYDB AND TABLE_NAME='RCONFLICTS_ORG' AND INDEX_NAME='IDX_RO';
      IF VAR_IDX=0 THEN
          CREATE INDEX IDX_RO ON RCONFLICTS_ORG(AGR_NAME,CONFLICTID);
      END IF;
  
      SELECT COUNT(DISTINCT R.AGR_NAME) INTO VAR_RCOR FROM RCONFLICTS_ORG R INNER JOIN RCOMPLETED C ON R.AGR_NAME=C.AGR_NAME WHERE C.EOCSTAT=0;
      IF VAR_RCOR>0 THEN
        DROP TABLE IF EXISTS ROOT_CAUSE_ORG_ROLE;
        CREATE TABLE ROOT_CAUSE_ORG_ROLE SELECT DISTINCT AGR_NAME, TCODE, OBJCT, FIELD, `FROM`, `TO` FROM ROOT_CAUSE_ORG;
        CREATE INDEX IDX_RCOR ON ROOT_CAUSE_ORG_ROLE(AGR_NAME,TCODE,OBJCT,FIELD,`FROM`,`TO`);
      END IF;
  
      CREATE TABLE ORG_CHECK_ROLE_SRC (`AGR_NAME` VARCHAR(50),`CONFLICTID` VARCHAR(13),`TCODE` VARCHAR(50),`FIELD` VARCHAR(25)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCRS ON ORG_CHECK_ROLE_SRC(AGR_NAME,CONFLICTID,FIELD);
      CREATE TABLE ORG_CHECK_ROLE (`AGR_NAME` VARCHAR(50),`CONFLICTID` VARCHAR(13),`FIELD` VARCHAR(25), TCODE_CNT INT(5)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCR ON ORG_CHECK_ROLE(AGR_NAME,CONFLICTID,FIELD);
      CREATE TABLE ORG_CHECKED_ROLE (`AGR_NAME` VARCHAR(50),`CONFLICTID` VARCHAR(13),`FLD_CNT` INT(5)) ENGINE=MYISAM DEFAULT CHARSET = UTF8;
      CREATE INDEX IDX_OCDR ON ORG_CHECKED_ROLE(AGR_NAME,CONFLICTID);
      CREATE TABLE R_CONF SELECT * FROM CONFLICTS_C LIMIT 0;
      CREATE INDEX IDX_RCONF ON R_CONF(CONFLICTID,`VALUE`);
      CREATE TABLE R_RCO SELECT * FROM ROOT_CAUSE_ORG_ROLE LIMIT 0;
      CREATE INDEX IDX_RRCO ON R_RCO(AGR_NAME,TCODE,OBJCT,FIELD,`FROM`,`TO`);
  
  
      OPEN CUR_OR;
      LOOP_ROLE:LOOP
          FETCH CUR_OR INTO VAR_AGR_NAME;
          IF DONE = 1 THEN
              LEAVE LOOP_ROLE;
          END IF;
          
          INSERT INTO R_RCO SELECT DISTINCT * FROM ROOT_CAUSE_ORG_ROLE WHERE AGR_NAME=VAR_AGR_NAME;
          INSERT INTO R_CONF SELECT DISTINCT C.* FROM CONFLICTS_C C INNER JOIN RCONFLICTS R ON C.CONFLICTID=R.CONFLICTID WHERE R.AGR_NAME=VAR_AGR_NAME;
          INSERT INTO ORG_CHECK_ROLE_SRC SELECT DISTINCT R.AGR_NAME, R.CONFLICTID, TCODE,RC.FIELD
              FROM RCONFLICTS_ORG R, ROOT_CAUSE_ORG_ROLE RC, CONFLICTS_C C
              WHERE R.AGR_NAME = VAR_AGR_NAME AND R.CONFLICTID = C.CONFLICTID AND R.AGR_NAME=RC.AGR_NAME AND C.VALUE=RC.TCODE;
  
          INSERT INTO ORG_CHECK_ROLE SELECT AGR_NAME, CONFLICTID, FIELD, COUNT(DISTINCT TCODE) TCODE_CNT FROM ORG_CHECK_ROLE_SRC O GROUP BY 1,2,3 HAVING COUNT(DISTINCT TCODE) > 1;
  
          INSERT INTO ORG_CHECKED_ROLE SELECT O.AGR_NAME, O.CONFLICTID, COUNT(DISTINCT R.`FIELD`) FLD_CNT FROM ORG_CHECK_ROLE O, R_RCO R, R_RCO S, R_CONF C, R_CONF D
              WHERE O.TCODE_CNT = 2 AND O.CONFLICTID = C.CONFLICTID AND C.CONFLICTID = D.CONFLICTID AND C.VALUE<> D.VALUE AND
              C.VALUE = R.TCODE AND D.VALUE = S.TCODE AND O.AGR_NAME = R.AGR_NAME AND R.AGR_NAME=S.AGR_NAME AND R.`FIELD` = O.`FIELD` AND S.`FIELD` = O.`FIELD` AND
              (R.FROM BETWEEN S.FROM AND S.TO OR R.TO BETWEEN S.FROM AND S.TO)
              GROUP BY 1,2 ORDER BY 1;
  
          INSERT INTO ORG_CHECKED_ROLE SELECT O.AGR_NAME, O.CONFLICTID, COUNT(DISTINCT R.`FIELD`) FLD_CNT FROM ORG_CHECK_ROLE O, R_RCO R, R_RCO S, R_RCO T, R_CONF C, R_CONF D, R_CONF E
              WHERE O.TCODE_CNT = 3 AND O.CONFLICTID = C.CONFLICTID AND C.CONFLICTID = D.CONFLICTID AND D.CONFLICTID = E.CONFLICTID AND C.VALUE<> D.VALUE AND D.VALUE<>E.VALUE AND C.VALUE<>E.VALUE
              AND C.VALUE = R.TCODE AND D.VALUE = S.TCODE AND E.VALUE = T.TCODE AND O.AGR_NAME = R.AGR_NAME AND R.AGR_NAME=S.AGR_NAME AND S.AGR_NAME=T.AGR_NAME AND R.`FIELD` = O.`FIELD` AND S.`FIELD` = O.`FIELD` AND T.`FIELD`= O.`FIELD` AND
              (R.FROM BETWEEN S.FROM AND S.TO OR R.TO BETWEEN S.FROM AND S.TO) AND (S.FROM BETWEEN T.FROM AND T.TO OR S.TO BETWEEN T.FROM AND T.TO) AND (R.FROM BETWEEN T.FROM AND T.TO OR R.TO BETWEEN T.FROM AND T.TO)
              GROUP BY 1,2 ORDER BY 1;
              
          DELETE C FROM RCONFLICTS_ORG C INNER JOIN ORG_CHECK_ROLE U ON U.AGR_NAME=C.AGR_NAME AND U.CONFLICTID=C.CONFLICTID LEFT JOIN ORG_CHECKED_ROLE D ON U.CONFLICTID=D.CONFLICTID WHERE FLD_CNT IS NULL;
          DELETE R FROM RCONFLICTS_ORG R, (SELECT AGR_NAME, CONFLICTID, COUNT(FIELD) FLD_CNT FROM ORG_CHECK_ROLE GROUP BY 1,2)O, ORG_CHECKED_ROLE D WHERE R.AGR_NAME=D.AGR_NAME AND D.AGR_NAME=O.AGR_NAME AND R.CONFLICTID=D.CONFLICTID
              AND D.CONFLICTID=O.CONFLICTID AND O.FLD_CNT<>D.FLD_CNT;
  
          DELETE FROM ORG_CHECK_ROLE_SRC;
          DELETE FROM ORG_CHECK_ROLE;
          DELETE FROM ORG_CHECKED_ROLE;
          DELETE FROM R_RCO;
          DELETE FROM R_CONF;
          UPDATE RCOMPLETED SET EOCStat=1 WHERE AGR_NAME=VAR_AGR_NAME;
      
      END LOOP;
      DROP TABLE IF EXISTS ORG_CHECK_ROLE_SRC;
      DROP TABLE IF EXISTS ORG_CHECK_ROLE;
      DROP TABLE IF EXISTS ORG_CHECKED_ROLE;
      DROP TABLE IF EXISTS R_CONF;
      DROP TABLE IF EXISTS R_RCO;
  
      DELETE FROM RCONFLICTS;
      INSERT INTO RCONFLICTS SELECT DISTINCT * FROM RCONFLICTS_ORG;
  
      DROP TABLE IF EXISTS UCONFLICTS_TMP;
      CREATE TABLE UCONFLICTS_TMP SELECT DISTINCT * FROM UCONFLICTS;
      TRUNCATE UCONFLICTS;
      INSERT INTO UCONFLICTS SELECT * FROM UCONFLICTS_TMP;
      DROP TABLE IF EXISTS UCONFLICTS_TMP;
  
  END IF;
  
  /*Creating tables required for reports*/
  
  SELECT COUNT(*) INTO var_stat FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'role_tcode' AND index_name = 'idx_rt' AND table_schema = var_mydb;
  IF var_stat=0 THEN
    CREATE INDEX idx_rt ON role_tcode(agr_name,tcode);
  END IF;
  
  INSERT INTO root_cause_tcdsrc SELECT DISTINCT uname, r.TCode, r.agr_name FROM root_cause r INNER JOIN role_tcode t ON r.agr_name=t.agr_name AND r.tcode=t.tcode;
  DELETE FROM root_cause_tcdsrc WHERE tcode='MIGO';
  INSERT INTO root_cause_tcdsrc SELECT DISTINCT r.uname,r.tcode,r.agr_name FROM root_cause r, conflicts_add_checks c WHERE r.tcode='MIGO' AND ((r.objct=c.objct AND r.field=c.field AND c.value BETWEEN r.from AND r.to) OR r.objct='S_TCODE');
  
  /* Below Line Changed by @Manish Kr */
  SELECT COUNT(*) INTO var_stat FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'root_cause_tcdsrc' AND index_name = 'idx_rct' AND table_schema = var_mydb;
  
  IF var_stat=0 THEN
    CREATE INDEX idx_rct ON root_cause_tcdsrc(uname,tcode,agr_name);
  END IF;
  
   /* === Line ADDED By Manish Kr === */
   INSERT INTO `procedure_message` (`id`, `procedure_name`, `message`, `create_datetime`) VALUES (NULL, 'Root Cause Analyis', 'Root Cause Analyzing..', current_timestamp());
  
  DROP TABLE IF EXISTS role_build_org;
  CREATE TABLE role_build_org SELECT DISTINCT r.agr_name,r.objct,r.field,r.from,r.to FROM role_build r INNER JOIN org_elements o ON r.field=o.element;
  CREATE INDEX idx_rbo ON role_build_org(agr_name,objct,FIELD,`from`,`to`);
  
  
  /*select count(*) into mit_ex from information_schema.tables where table_schema=var_mydb and table_name in ('mit_risk','mit_conflict');
  SELECT COUNT(*) INTO mit_ex_r FROM mit_risk;
  SELECT COUNT(*) INTO mit_ex_c FROM mit_conflict;
  IF mit_ex_r>0 OR mit_ex_c>0 THEN*/
      CREATE TABLE IF NOT EXISTS mit_uconflicts_r SELECT * FROM uconflicts LIMIT 0;
      CREATE TABLE IF NOT EXISTS mit_uconflicts_c SELECT * FROM uconflicts LIMIT 0;
      SELECT COUNT(DISTINCT index_name) INTO mit_idx_ex FROM information_schema.statistics WHERE table_schema=var_mydb AND table_name='mit_uconflicts_r' AND index_name='idx_mur';
      IF mit_idx_ex = 0 THEN
          CREATE INDEX idx_mur ON mit_uconflicts_r(uname,conflictid);
      END IF;
      SET mit_idx_ex=0;
      SELECT COUNT(DISTINCT index_name) INTO mit_idx_ex FROM information_schema.statistics WHERE table_schema=var_mydb AND table_name='mit_uconflicts_c' AND index_name='idx_muc';
      IF mit_idx_ex = 0 THEN
          CREATE INDEX idx_muc ON mit_uconflicts_c(uname,conflictid);
      END IF;    
      INSERT INTO mit_uconflicts_r SELECT DISTINCT u.* FROM uconflicts u INNER JOIN mit_risk m ON u.uname=m.uname AND LEFT(u.conflictid,6)=m.riskid;
      INSERT INTO mit_uconflicts_c SELECT DISTINCT u.* FROM uconflicts u INNER JOIN mit_conflict m ON u.uname=m.uname AND u.conflictid=m.conflictid;
      DELETE u FROM uconflicts u INNER JOIN mit_risk m ON u.uname=m.uname AND LEFT(u.conflictid,6)=m.riskid;
      DELETE u FROM uconflicts u INNER JOIN mit_conflict m ON u.uname=m.uname AND u.conflictid=m.conflictid;
      DELETE u FROM uconflicts_all u INNER JOIN mit_uconflicts_r m ON u.uname=m.uname AND u.conflictid=m.conflictid;
      DELETE u FROM uconflicts_all u INNER JOIN mit_uconflicts_c m ON u.uname=m.uname AND u.conflictid=m.conflictid;
      INSERT INTO uconflicts SELECT DISTINCT m.* FROM mit_uconflicts_r m WHERE CONCAT(m.uname,LEFT(m.conflictid,6)) NOT IN (SELECT CONCAT(mr.uname,mr.riskid) FROM mit_risk mr);
      INSERT INTO uconflicts SELECT DISTINCT m.* FROM mit_uconflicts_c m WHERE CONCAT(m.uname,m.conflictid) NOT IN (SELECT CONCAT(mc.uname,mc.conflictid) FROM mit_conflict mc);
      INSERT INTO uconflicts_all SELECT DISTINCT m.* FROM mit_uconflicts_r m WHERE CONCAT(m.uname,LEFT(m.conflictid,6)) NOT IN (SELECT CONCAT(mr.uname,mr.riskid) FROM mit_risk mr);
      INSERT INTO uconflicts_all SELECT DISTINCT m.* FROM mit_uconflicts_c m WHERE CONCAT(m.uname,m.conflictid) NOT IN (SELECT CONCAT(mc.uname,mc.conflictid) FROM mit_conflict mc);
      DELETE m FROM mit_uconflicts_r m WHERE CONCAT(m.uname,LEFT(m.conflictid,6)) NOT IN (SELECT CONCAT(mr.uname,mr.riskid) FROM mit_risk mr);
      DELETE m FROM mit_uconflicts_c m WHERE CONCAT(m.uname,m.conflictid) NOT IN (SELECT CONCAT(mc.uname,mc.conflictid) FROM mit_conflict mc);
          
      DROP TABLE IF EXISTS tmp_mit_uconflicts;
      CREATE TEMPORARY TABLE tmp_mit_uconflicts SELECT DISTINCT * FROM mit_uconflicts_r;
      TRUNCATE mit_uconflicts_r;
      INSERT INTO mit_uconflicts_r SELECT * FROM tmp_mit_uconflicts;
      DROP TABLE IF EXISTS tmp_mit_uconflicts;
  
      DROP TABLE IF EXISTS tmp_mit_uconflicts;
      CREATE TEMPORARY TABLE tmp_mit_uconflicts SELECT DISTINCT * FROM mit_uconflicts_c;
      TRUNCATE mit_uconflicts_c;
      INSERT INTO mit_uconflicts_c SELECT * FROM tmp_mit_uconflicts;
      DROP TABLE IF EXISTS tmp_mit_uconflicts;
  
      DROP TABLE IF EXISTS MIT_UCONFLICTS_COL;
      CREATE TABLE MIT_UCONFLICTS_COL SELECT U.UNAME,U.CONFLICTID, C.VALUE TCODE1, D.VALUE TCODE2, E.VALUE TCODE3 FROM (SELECT * FROM MIT_UCONFLICTS_C UNION SELECT * FROM MIT_UCONFLICTS_R) U, CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_C E, CONFLICTS_FIRST_CNT F WHERE C.CONFLICTID=U.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND D.CONFLICTID=E.CONFLICTID AND D.CONFLICTID=F.CONFLICTID AND C.VALUE<>D.VALUE AND D.VALUE<>E.VALUE AND C.VALUE<>E.VALUE AND F.COUNT=3 GROUP BY 1,2;
      INSERT INTO MIT_UCONFLICTS_COL (UNAME,CONFLICTID,TCODE1,TCODE2) SELECT DISTINCT U.UNAME,U.CONFLICTID,C.VALUE,D.VALUE FROM (SELECT * FROM MIT_UCONFLICTS_C UNION SELECT * FROM MIT_UCONFLICTS_R) U,CONFLICTS_C C, CONFLICTS_C D, CONFLICTS_FIRST_CNT F WHERE U.CONFLICTID=C.CONFLICTID AND C.CONFLICTID=D.CONFLICTID AND C.VALUE<>D.VALUE AND D.CONFLICTID=F.CONFLICTID AND F.COUNT=2 GROUP BY 1,2;
      CREATE INDEX IDX_UCOL ON MIT_UCONFLICTS_COL(UNAME,CONFLICTID,TCODE1,TCODE2,TCODE3);
  
      DROP TABLE IF EXISTS tmp_uconflicts;
      CREATE TEMPORARY TABLE tmp_uconflicts SELECT DISTINCT * FROM uconflicts;
      TRUNCATE uconflicts;
      INSERT INTO uconflicts SELECT * FROM tmp_uconflicts;
      DROP TABLE IF EXISTS tmp_uconflicts;
  
      DROP TABLE IF EXISTS tmp_uconflicts_all;
      CREATE TEMPORARY TABLE tmp_uconflicts_all SELECT DISTINCT * FROM uconflicts_all;
      TRUNCATE uconflicts_all;
      INSERT INTO uconflicts_all SELECT * FROM tmp_uconflicts_all;
      DROP TABLE IF EXISTS tmp_uconflicts_all;
  /*END IF;*/
  
  /* disbling reports @HM 14/02
  DROP TABLE IF EXISTS rep_tcode_level_sod_with_role;
  CREATE TABLE rep_tcode_level_sod_with_role
  SELECT DISTINCT USERID,USER_NAME,Department,Company,SUSER,CONFLICT_ID,MITIGATION, RISK_DESCRIPTION,RISK_RATING, TCODE1, ROLE1, TCODE2,ROLE2, TCODE3, ROLE3 FROM(SELECT U.UNAME USERID,UD.USER_NAME,ud.department,ud.company,UD.SUSER,U.ConflictID CONFLICT_ID,MITIGATION, K.RISKNAME RISK_DESCRIPTION, K.RATING RISK_RATING, U.TCODE1, RCCA.AGR_NAME ROLE1, U.TCODE2, RCCB.AGR_NAME ROLE2, U.TCODE3, RCCC.AGR_NAME ROLE3 FROM (SELECT *,'N' MITIGATION FROM UCONFLICTS_COL WHERE TCODE3 IS NOT NULL AND TCODE3 <>'' UNION SELECT *,'Y' MITIGATION FROM MIT_UCONFLICTS_COL WHERE TCODE3 IS NOT NULL AND TCODE3 <>'') U LEFT JOIN USER_DETAILS UD ON U.UNAME=UD.UNAME, SOD_RISK K, ROOT_CAUSE_TCDSRC RCCA, ROOT_CAUSE_TCDSRC RCCB, ROOT_CAUSE_TCDSRC RCCC WHERE U.UNAME =RCCA.UNAME AND U.UNAME=RCCB.UNAME AND U.UNAME=RCCC.UNAME AND U.TCODE1=RCCA.TCODE AND U.TCODE2=RCCB.TCODE AND U.TCODE3=RCCC.TCODE AND LEFT(U.CONFLICTID,6)=K.RISKID UNION SELECT U.UNAME USERID, UD.USER_NAME,ud.department,ud.company ,UD.SUSER,U.CONFLICTID CONFLICT_ID,MITIGATION, K.RISKNAME RISK_DESCRIPTION, K.RATING RISK_RATING, U.TCODE1, RCCA.AGR_NAME ROLE1, U.TCODE2, RCCB.AGR_NAME ROLE2, '' TCODE3,' ' ROLE3 FROM (SELECT *,'N' MITIGATION FROM UCONFLICTS_COL WHERE TCODE3 IS NULL OR TCODE3='' UNION SELECT *,'Y' MITIGATION FROM MIT_UCONFLICTS_COL WHERE TCODE3 IS NULL OR TCODE3='') U LEFT JOIN USER_DETAILS UD ON U.UNAME=UD.UNAME, SOD_RISK K, ROOT_CAUSE_TCDSRC RCCA, ROOT_CAUSE_TCDSRC RCCB WHERE U.UNAME =RCCA.UNAME AND U.UNAME=RCCB.UNAME AND U.TCODE1=RCCA.TCODE AND U.TCODE2=RCCB.TCODE AND LEFT(U.CONFLICTID,6)=K.RISKID)q ORDER BY USERID,CONFLICT_ID, role1, role2, role3;
  */
  /* === Line ADDED By Manish Kr === */
   INSERT INTO `procedure_message` (`id`, `procedure_name`, `message`, `create_datetime`) VALUES (NULL, 'Root Cause Analyis', 'Root Cause Analyzing Completed..', current_timestamp());
  
  
  IF LOG_STAT = 'ON' THEN
      SET GLOBAL GENERAL_LOG = 'ON';
  END IF;
  
  
  
  END
  