DECLARE uid INT DEFAULT 0;
DECLARE user TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_user TEXT;
DECLARE portfolio_admins TEXT;
DECLARE portfolio_mgrs TEXT;
DECLARE service_finance_pocs TEXT;
DECLARE program TEXT;
DECLARE MIPR TEXT;
DECLARE alert TEXT;
DECLARE status TEXT;
DECLARE timestamp TIMESTAMP;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT alerts.uid, alerts.service_project_mgrs,
alerts.portfolio_admins, alerts.portfolio_mgrs,
alerts.service_finance_pocs, alerts.program,
alerts.MIPR, alerts.alert, alerts.status, alerts.timestamp
FROM alerts
WHERE alerts.service_project_mgrs != ”;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`uid` INT NOT NULL,
`user` VARCHAR(255) NOT NULL,
`portfolio_admins` VARCHAR(255),
`portfolio_mgrs` VARCHAR(255),
`service_finance_pocs` VARCHAR(255),
`program` VARCHAR(255),
`MIPR` VARCHAR(255),
`alert` VARCHAR(255),
`status` VARCHAR(255),
`timestamp` TIMESTAMP
) ENGINE=Memory;

OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO uid, user, portfolio_admins, portfolio_mgrs, service_finance_pocs, program, MIPR, alert, status, timestamp;
IF done THEN
LEAVE read_loop;
END IF;

SET occurance = (SELECT LENGTH(user)
– LENGTH(REPLACE(user, bound, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_user = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(user, bound, i),
LENGTH(SUBSTRING_INDEX(user, bound, i – 1)) + 1), ‘,’, ”));

INSERT INTO table2 VALUES (uid, splitted_user, portfolio_admins, portfolio_mgrs, service_finance_pocs,
program, MIPR, alert, status, timestamp);
SET i = i + 1;

END WHILE;
END LOOP;

SELECT * FROM table2;
CLOSE cur1;
END; $$

• SELECT MID(requisition_id, LENGTH(requisition_id) – 5, 6) as req_id,
  appropriation_year,
  SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) as month,
  expensed_date,
  SUM(expensed_amount),
  CONCAT(‘UPDATE  project_portfolio.obligations_outlays as o,  project_portfolio.projects AS p,project_portfolio.budget_centers AS c SET ‘,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 1, ‘month_4’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 2, ‘month_5’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 3, ‘month_6’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 4, ‘month_7’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 5, ‘month_8’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 6, ‘month_9’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 7, ‘month_1-‘,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 8, ‘month_11’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 9, ‘month_12’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 10, ‘month_1’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 11, ‘month_2’,
  IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 12, ‘month_3’, ‘unknown’))))))))))))
  , ‘=’, SUM(expensed_amount),
  ‘ WHERE o.type = ‘Outlay $ (Reported in FACTS)’ AND o.year + o.year_count = ‘, appropriation_year + 1, ‘ AND o.year_count = 1 ‘,
  ‘ AND p.uid = o.project_id AND p.uid = c.project_id AND c.uid = o.funding_id ‘,
  ‘ AND c.funding_transaction_number = ”, MID(requisition_id, LENGTH(requisition_id) – 5, 6), ”’) as sqlstring
  FROM `visibility_dev2`.`facts_outlay_update_test`
  WHERE LENGTH(requisition_id) > 6 AND
  (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1)  > 0 AND SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1)  < 13)
  GROUP BY month, requisition_id