Monthly Archives: December 2013

Phil 12.16.13

8:00 – 4:00 SR

  • Backups
  • Deployed the preprocessing code that fixes all the bugs in our test data. Let’s see how it works on the real thing.
  • Going to spend the day making some reports from our fake data, then auto-updating the dates.
  • Working on updating Flexichart so that it will sort on column names. Done.
  • Spent some time with Dong remembering that CDATA exists and is useful.
  • Continued my slow-motion dialog with Andrea C.

Phil 12.13.13

8:00 – 12:00 SR

  • Backups
  • Meeting with Tim next week, probably Wednesday afternoon
  • Deployed new FA, RA and RQ
  • Installed a new truststore. We’ll see if it fixes the new login problems

Dong Shin 12.12.2013

  • working on queries for pre-processing (COGNOS conflicts)
    • find multiple obligation dates
      SELECT * FROM contracts_cognos WHERE uid IN (SELECT a.uid FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.requisition_id = b.requisition_id AND a.sub_budget_center = b.sub_budget_center AND NOT ISNULL(a.obligation_date) AND NOT ISNULL(b.obligation_date) WHERE a.uid <> b.uid ORDER BY a.uid)
    • Committed Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(committed_amount) > SUM(total_budget)))
    • Obligated Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
      SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
    • Outlay Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
      SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
    • Obligated Amount greater than Committed Amount
      SELECT * FROM contracts_cognos WHERE obligated_amount > committed_amount
    • Outlay Amount greater than Committed Amount
      SELECT * FROM contracts_cognos WHERE expensed_amount > committed_amount
    • Outlay Amount greater than Obligated Amount
      SELECT * FROM contracts_cognos WHERE expensed_amount > obligated_amount

Dong Shin 12.11.2013

  • FMP’s and Invoice updates are not recorded in query_logs – fixed
    • added REPLACE to supported types in recordQuery()
  • copy previous month’s FMP doesn’t work properly, especially from the previous year -fixed

Phil 12.11.13

8:00 – 4:00 SR

  • Backups
  • Deployed new Reqonciler and updated the queries
  • Created a new adminUtils.html page. This now has links to all the admin tools, and I took them off the index.html page.

Phil 12.9.13

8:30 – 2:30 SR

  • Two words without redeeming value: “Wintery mix”.
  • Backups
  • Verified that RA was recording user info in the query logs. It does not seem to be recording info about FMPs though.
  • Deploying new RA, but not the RQ updates, since Dong was stuck at home.

Dong Shin 12.06.2013

  • deployed new RA and DB updates – forgot to check for query_logs!
  • went over the pre-processing query with Lenny, duplicate duplicates are showing in the result window!
    • SELECT * FROM contracts_cognos
      WHERE uid IN (SELECT a.uid FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.obligated_amount = b.obligated_amount
      WHERE a.uid <> b.uid ORDER BY a.uid)
  • disabled auto-refresh of projects data in RA
  • updates for cognos data views!

Phil 12/6/13

8:00 – 4:00 SR

  • Backups
  • Deployed new RA and DB updates
  • Need to do status report!
  • Don’t refresh RA display unless an update is made.
  • Lenny will provide a whitelist of Cognos columns to use for row compare on ingest.
  • RQ is double-counting on pre-process
  • Obligation not showing up for FY14
  • Finished adding en errors to fake data as per customer spec
    • Multiple obligation dates
    • Disappearing commitment and/or obligation dates
    • Committed Amount greater than Req Allocation
    • Obligated Amount greater than Committed Amount
    • Obligated Amount greater than Req Allocation
    • Outlay Amount greater than Committed Amount
    • Outlay Amount greater than Obligated Amount
    • Outlay Amount greater than Req Allocation

Dong Shin 12.05.2013

  • deployed new Reqonciler and post-processing queries
  • found query log saving incorrect user id in RA – fixed
  • meeting with Finance Folks at S35P
  • need to get pre-processing working
    • pre-processing query to find duplicate obligations amount
    • SELECT * FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.obligated_amount = b.obligated_amount AND a.requisition_id = b.requisition_id WHERE a.uid <> b.uid ORDER BY a.uid
  • fixed update outlay query in Reqonciler to null out future months
    • UPDATE obligations_outlays oo LEFT JOIN __view_monthly_obligations_outlays_by_req_id AS vv ON vv.bc_uid = oo.funding_id SET oo.month_1 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<10,NULL,vv.month_1_outlay),vv.month_1_outlay), oo.month_2 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<11,NULL,vv.month_2_outlay),vv.month_2_outlay), oo.month_3 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<12,NULL,vv.month_3_outlay),vv.month_3_outlay), oo.month_4 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<13,NULL,vv.month_4_outlay),vv.month_4_outlay), oo.month_5 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<2,NULL,vv.month_5_outlay),vv.month_5_outlay)), oo.month_6 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<3,NULL,vv.month_6_outlay),vv.month_6_outlay)), oo.month_7 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<4,NULL,vv.month_7_outlay),vv.month_7_outlay)), oo.month_8 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<5,NULL,vv.month_8_outlay),vv.month_8_outlay)), oo.month_9 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<6,NULL,vv.month_9_outlay),vv.month_9_outlay)), oo.month_10 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<7,NULL,vv.month_10_outlay),vv.month_10_outlay)), oo.month_11 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<8,NULL,vv.month_11_outlay),vv.month_11_outlay)), oo.month_12 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<9,NULL,vv.month_12_outlay),vv.month_12_outlay)) WHERE type = ‘Outlay $ (Reported in FACTS)’ AND (year + year_count) = (fiscal_year + 1)

Phil 12.5.13

8:00 – 4:00 SR

  • Two years ago I broke my femur, so it’s as good as it’s going to get. Which is actually pretty good. Just some low-level aches.
  • Backups
  • Meeting with the full financial and project team
  • The user object in RA is not working correctly. Wrong user id’s are being written into the query log. FA is ok.
  • Meeting with Tim E. at 1:30?

Phil 12.4.13

8:00 – 4:00

  • Backups
  • Server paperwork
  • Fixing bugs in the generation program
    • Filling in data only until “today”
    • Allowing varying numbers of lines up to the requested amount, based on if the project is at its end year.
    • All bugs appear fixed. Now we need to break things. Burning a copy of the spreadsheet for Lenny to look at.
  • Next step is to create a Swing(?) app that will let the user determine what errors to inject.

Phil 12.3.13

8:00 – 4:00 SR

  • Backups
  • SQL changes
  • Changes made to queries in query builder are not updating correctly
  • Obligations may only be appearing in the month that they occurred. They need to carry forward, then overwrite as the next obligation comes in.
  • Still a problem with EAs. Req assistent shows correct outlays through September of the first year, but is blank in October of the second year, then roughly four times the amount that it should be. Queries are wrong, with no discernible pattern. MIPRs are ok.
  • Change “Committed Amount” to REQ Funded Amount”
  • Got spreadsheets ingesting
  • Found a date bug that may have had something to do with ints vs. longs. Adding more lines fixed the problem.

Dong Shin 12.02.2013

  • tried the failed query back at the office, no success? How did I run this?
  • new query to update obligations/outlays….
  • UPDATE obligations_outlays o1
    JOIN obligations_outlays o2
    ON o1.funding_id = o2.funding_id AND o1.project_id = o2.project_id
    SET o1.month_1 = o1.month_1 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_2 = o1.month_2 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_3 = o1.month_3 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_4 = o1.month_4 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_5 = o1.month_5 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_6 = o1.month_6 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_7 = o1.month_7 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_8 = o1.month_8 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_9 = o1.month_9 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_10 = o1.month_10 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_11 = o1.month_11 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    o1.month_12 = o1.month_12 + IF(ISNULL(o2.month_12), 0, o2.month_12),
    WHERE o1.type = ‘Outlay $ (Reported in FACTS)’ AND (o1.year + o1.year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
    AND o1.year_count = 2
    AND o2.type = ‘Outlay $ (Reported in FACTS)’ AND o2.year_count = 1