Category Archives: Dong Shin

Dong Shin 01.09.2013

  • Reported Outlay is now hidden from both PPM and PA
  • adjusted rowCount and height of the datagrids accordingly
  • reworked auto copy of PM Actuals to Reported Outlay in PPM and PA
  • disabled tooltips on list of projects in PA
  • working on adding “DUE” status to PA

Dong Shin 01.04.2013

  • PA Status bug not fixed
    • getting Current for incomplete projects
    • got ************* should not reach here !!!!! ******************** message, may be related?
  • Metting with Chris, Lenny, Tangie, Carla
    • Proposed changes ( added to Req’s)
      • Contract # should be a blank text field in Enter Invoice Diaglog
      • Delete “Reported Outlay” from all views (PPM and PA)
      • Fix DB so that the greater amount of PM Actuals or Reported Outlay is copied into the lower field. Also copy FMP.
      • Default to “Reporting Month”
      • For any row that is less than 100% use only those rows to calculate “incomplete” and “due”. When all rows are 100%, the item is current forever.
      • “Planned Outlay” becomes “Spend Plan Baseline”
      • For any row that equals 100%, auto-fill through the end of the contracts in PA (same rule as PPM)
      • No tool tip in PA list view
  • finished up Security Briefing and sent in the Acknowledge form

Dong Shin 12.29.2012

  • PPM bug fixes in Project Status
    • Date Acceptance Received not saving – fixed
    • Obligation and Outlay not showing when it’s $0 – removed for some reason… put it back -fixed
    • retrieve Funding Request data sorted on Description
    • added dataGridDefaultSort to TableAdbDataGrid.mxml

Dong Shin 12.12.2012

  • after hectic morning, got the alerts working on-site.
  • worked through alert queries to return results in xml so that data can be verified, fixed month and year hard-coded values
  • created VSSUpdates121212.sql for deployment
  • working on alerts export to Visibility

Dong Shin 12.08.2012

  • reworking all alerts
    • modified queries to just insert into alerts table, removed XML generation
    • created a stored procedure, a query, and a script to parse alerts by users for Visibility
    • modified python scripts accordingly
    • created VSSUpdates120912.sql to deploy everything in one script

Dong Shin 12.06.2012

  • forgot to include ‘DELIMITER $$’ for stored procedures this morning, will try again tomorrow
  • stored procedure to separate service project mgrs in the alerts table – need more tweaks to work with VSS
    • DELIMITER $$DROP PROCEDURE IF EXISTS explode_table $$
      CREATE PROCEDURE explode_table(bound VARCHAR(255))BEGIN

      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; $$

Dong Shin 12.05.2012

  • working on FACTS Outlay updates
    • create a script – UpdateFACTSOutlayData.py
    • query to construct update query
      • 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