Category Archives: Dong Shin

Dong Shin 12.18.2013

  • deployed new FA server jars… all is well.
  • few notes from Lenny this morning @ Phil’s blog
  • my Flex workspace is behaving bad, again.!
  • working on FA/RA
    • added query description to Reqonciler’s Pre-Processing window
    • fixed Req Funded Amount not displaying correctly…
    • changed FMP title to Monthly Status Report

Dong Shin 12.17.2013

  • deployed new FA/RA
  • working on COGNOS Data white list columns
    • columns should be compared to
      • Budget Center, Budget Center Name,Sub-Budget Center,Sub-Budget Center Name,Executing Budget Center,EBC Name,Appropriation Year,Requisition ID,PO Reference,ACR,Committed Date,Committed Amount,Obligation Date,Obligation Amount,Expensed Date,Expensed Amount
    • added congos.whitelist to FinancialAssistant.properties
    • modified the FA server to compare with the specified fields using hash map of column names and column headers
    • files to deploy – FinancialAssistant.jar, commons-lang-2.5.jar, FinancialAssistant.properties
  • fixed contrancts_cognos_history not updating..
  • working on Presentation Queries…

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

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

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!

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)

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

Dong Shin 11.26.2013

  • deployed new FA/RA
    • outlay not adding up from the previous year
    • add Total Funded to Project Mgmt Panel
  • working on FA
    • query to update second year of Outlays
    • UPDATE obligations_outlays o1,
      (SELECT month_12 FROM obligations_outlays oo2 WHERE type = ‘Outlay $ (Reported in FACTS)’ AND year_count = 1
      AND o1.funding_id = o2.funding_id AND o1.project_id = o2.project_id
      ) AS o2
      SET o1.month_1 = o2.month_12 + o1.month_1,
      o1.month_2 = o2.month_12 + o1.month_2,
      o1.month_3 = o2.month_12 + o1.month_3,
      o1.month_4 = o2.month_12 + o1.month_4,
      o1.month_5 = o2.month_12 + o1.month_5,
      o1.month_6 = o2.month_12 + o1.month_6,
      o1.month_7 = o2.month_12 + o1.month_7,
      o1.month_8 = o2.month_12 + o1.month_8,
      o1.month_9 = o2.month_12 + o1.month_9,
      o1.month_10 = o2.month_12 + o1.month_10,
      o1.month_11 = o2.month_12 + o1.month_11,
      o1.month_12 = o2.month_12 + o1.month_12
      WHERE type = ‘Outlay $ (Reported in FACTS)’
      AND (year + year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
      AND year_count = 2

Dong Shin 11.25.2013

  • deployed new RA
  • verified that Fiscal Year is the cause of the incorrect outlay/obligations import…..
  • fixed login id not showing in RA
  • reworked views and update obligation/outlay queries for the Fiscal Year!
  • added ReqView in QueryBuilder – double clicking shows the requisition details, removed the project view
  • removed Upload COGNOS data button – use Reqonciler!
  • modified the FA server code to keep upload history – FinancialAssistant.jar

Dong Shin 11.22.2013

  • continue working on FA/RA
    • added ReqDetailsWindow – show Requisition Details and financial data
    • successfully ingested test data from Phil’s code
    • added Portfolio Admins to view data in Requisition Assistant
    • working on COGNOS ingest queries

Dong Shin 11.21.2013

  • new Furniture!
  • working on Reqonciler
    • trouble-shooting incorrect ingest… changed budget_center to sub_budget_center in post-processing query
    • added update obligations and outlays queries to the post processing

Dong Shin 11.20.2013

  • deployed FA – Export to Viz works!
    • fixed table name not using the textInput
  • duplicate projects listed in Project Mgmt Panel – fixed
    • caused by incorrect parent id set from the server….. 0 instead of NULL
    • must use ResultSet.wasNull() to see if the last getXXX returned is NULL
  • created test XSLX spreadsheet to duplicate incorrect Outlay data on-site….