Category Archives: VISIBILITY

Dong Shin 12.16.2013

  • deployed new Pre-Processing queries, Lenny will review
  • changed Project Description to Project BLUF in FA
  • changed the way how FMP’s are handled
    • created mitigationPlanConfig.xml to describe the questions
    • FMP fields are dynamically created

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.