Category Archives: Dong Shin

Dong Shin 03.04.2014

  • deployed database updates and new queries
    • Reqonciler has some datagrid problem – not updating hide_flags and weird mouse over highlighting, but no problems here back at the office…
    • second year processing missing, need to add this to post-processing – in reqonciler_processing.sql
      • UPDATE obligations_outlays o1,
        (SELECT month_12 FROM obligations_outlays oo2 WHERE type = ‘Outlay $ (Reported in FACTS)’ AND year_count = 1) 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
  • working on query for Chris/Lenny – show PM Actuals and Planned Outlay with differences in $ and % for current month
    • capability AS Capability, bcc.budget_center_name AS ‘Budget Center’, req_id AS ‘Req ID’, req_description AS ‘Req Title’,
      vrt.req_id_total AS ‘Req Funded Amount’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0)),0)))))))))))) AS ‘Outlay’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘PM Actuals’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘Diff $’,
      IF(MONTH(CURDATE())=10,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0))-SUM(IF(o.type=’Planned Outlay $:’,month_1,0))*100,1),’%’),IF(MONTH(CURDATE())=11,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0))/SUM(IF(o.type=’Planned Outlay $:’,month_2,0))*100,1),’%’),IF(MONTH(CURDATE())=12,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0))/SUM(IF(o.type=’Planned Outlay $:’,month_3,0))*100,1),’%’),IF(MONTH(CURDATE())=1,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0))/SUM(IF(o.type=’Planned Outlay $:’,month_4,0))*100,1),’%’),IF(MONTH(CURDATE())=2,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0))/SUM(IF(o.type=’Planned Outlay $:’,month_5,0))*100,1),’%’),IF(MONTH(CURDATE())=3,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0))/SUM(IF(o.type=’Planned Outlay $:’,month_6,0))*100,1),’%’),IF(MONTH(CURDATE())=4,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0))/SUM(IF(o.type=’Planned Outlay $:’,month_7,0))*100,1),’%’),IF(MONTH(CURDATE())=5,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0))/SUM(IF(o.type=’Planned Outlay $:’,month_8,0))*100,1),’%’),IF(MONTH(CURDATE())=6,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0))/SUM(IF(o.type=’Planned Outlay $:’,month_9,0))*100,1),’%’),IF(MONTH(CURDATE())=7,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0))/SUM(IF(o.type=’Planned Outlay $:’,month_10,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0))/SUM(IF(o.type=’Planned Outlay $:’,month_11,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0))/SUM(IF(o.type=’Planned Outlay $:’,month_12,0))*100,1),’%’),0)))))))))))) AS ‘Diff %’
      WHERE fiscal_year=2014
      GROUP BY capability, bc.sub_budget_center_number, req_id

Dong Shin 02.28.2014

  • deployed new FA and query updates, everything seemed well until…. found that the fiscal year in obligations/outlay gets messed up when the data spans over one year. will need to separate the obligations / outlays
  • created four new views…
    • __view_monthly_obligations_by_contract, __view_monthly_outlays_by_contract, __view_monthly_obligations_by_req_id, __view_monthly_outlays_by_req_id
  • updating post-processing queries – done
  • found some lingering (ghost) summary data in budget_center_contracts table in my database, need to check it on-site to make sure it doesn’t get confused with others.

Dong Shin 02.27.2014

  • deployed new FA/Reqonciler with new query for Planned Outlays… everything is well
  • Lenny reported that some Obligations are not imported
    • found fiscal year is set to NULL in views – __view_monthly_obligations_outlays_by_contract
    • view definition looks at the date from expensed date, need to look at obligation date as well
    • modified the view to examine both expensed and obligation dates for fiscal year – this would resolve the second year problem as well?
      • IF(ISNULL(if((month(MAX(`cc`.`expensed_date`)) > 9),(year(MAX(`cc`.`expensed_date`)) + 1),year(MAX(`cc`.`expensed_date`)))),
        if((month(MAX(`cc`.`obligation_date`)) > 9),(year(MAX(`cc`.`obligation_date`)) + 1),year(MAX(`cc`.`obligation_date`))),
        if((month(MAX(`cc`.`expensed_date`)) > 9),(year(MAX(`cc`.`expensed_date`)) + 1),year(MAX(`cc`.`expensed_date`)))) AS fiscal_year,
  • Funding Description in FA is disabled – fixed
  • new Query for Lenny/Chris – will try to implement it on-site tomorrow morning
    • Capability, Budget Center, Req ID, Req Title, Req Funded Amount, Current Month Outlay Plan, Current Month PM Actuals, Difference (in $) between Outlay Plan and PM Actuals, Difference (in %) between Outlay Plan and PM Actuals

Dong Shin 02.26.2014

  • snow! again! working at home
  • added where config to queryBuilderConfig.xml and modified to QueryBuilder code to read the config….
  • added currency fields in queryBuilderConfig.xml
  • set query name when saving existing query in QueryBuilder
  • changed Spark DataGrid to Advanced DataGrid in pre-processing to support multi-columns sorting
  • changed Col Pref window to ResizableWindow
  • added multiple selection support in Claimed/Unclaimed contracts

Dong Shin 02.25.2014

  • deployed to new query, query config… missing some details in where clause
  • some notes
    • add saved name when updating a query
    • 2nd year Obligation/Outlays not updating properly
    • bigger col prefs
    • multiple contracts selection in unclaimed/claimed
    • separate pre-processing for FY13/FY14
    • enable multi-column sort in Reqonciler
    • add Expensed Date in Duplicate Expensed Amount > 0 pre-processing query

Dong Shin 02.21.2014

  • working on the query for Lenny/Chris
    • capability AS Capability, bcc.budget_center_name AS ‘Budget Center’, req_id AS ‘Req ID’, req_description AS ‘Req Title’, vrt.req_id_total AS ‘Req Funded Amount’,
      SUM(IF(o.type=’Planned Outlay $:’,month_1,0)) AS ‘Oct 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_2,0)) AS ‘Nov 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_3,0)) AS ‘Dec 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_4,0)) AS ‘Jan 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_5,0)) AS ‘Feb 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_6,0)) AS ‘Mar 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_7,0)) AS ‘Apr 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_8,0)) AS ‘May 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_9,0)) AS ‘Jun 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_10,0)) AS ‘Jul 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_11,0)) AS ‘Aug 14′,
      SUM(IF(o.type=’Planned Outlay $:’,month_12,0)) AS ‘Sep 14’
      WHERE fiscal_year = 2014
      GROUP BY capability, req_id
    • getting the second year will be tricky….

Dong Shin 02.20.2014

  • deployed all swf’s with Mod/Revision text
  • missed queryBuilderConfig.xml
    • LEFT JOIN __view_committed_totals AS vct ON vct.budget_center_id = bc.uid
  • checked in all Flex projects
  • working on query for reports
  • Query Request from Lenny/Chris
    • query that shows Capability, Budget Center, Req ID, Req Title, Req Funded Amount,Outlay Plan by month
    • The column headings would be: Capability, Budget Center, Req ID, Req Title, Req Funded Amount, Oct 13, Nov 13, Dec 13, Jan 14, etc. through the first two years.

Dong Shin 02.19.2014

  • building vis2 with new settings… wouldn’t let me build with Flex SDK 3.
    • would not compile in Flex 4 workspace , weird. created a new workspace just for Flex 3 projects
    • built IngestManager – had to check out as a new project, AccountManager
    • packed up everything to VizTool.zip

Dong Shin 02.12.2014

  • deployed new queries to update COGNOS data mapping – working!
  • changed Reqonciler to retrieve the list of queries in ‘order’ order…
  • working on reconfiguring Commited Amounts/Date in Query Builder
    • created view for Committed Amount/Date – __view_committed_totals
      • CREATE ALGORITHM = UNDEFINED VIEW `__view_committed_totals` AS SELECT budget_center_id, MAX( committed_date ) AS committed_date, SUM( committed_amount ) AS committed_amount, GROUP_CONCAT( committed_amount )
        FROM `committed_amounts`
        GROUP BY budget_center_id
    • added __view_committed_totals join queryBuilderConfig.xml – vct
    • change queries – committed_date to vct.committed_date in queries!

Dong Shin 02.11.2014

  • found that a query is missing in Post Processing in Reqonciler for updating cognos data relations after new ingest….
    • UPDATE budget_center_contracts LEFT JOIN (SELECT bcc.uid as tuid, c.uid as cuid FROM budget_centers c, budget_center_contracts bcc WHERE c.sub_budget_center_number = bcc.sub_budget_center AND c.req_id = bcc.requisition_id AND ISNULL(bcc.budget_center_id)) t ON budget_center_contracts.uid = t.tuid SET budget_center_contracts.budget_center_id = cuid WHERE NOT ISNULL(t.tuid)
  • alter table syntax….
    • ALTER TABLE t COMMENT '';
  • need to look at these… mine are set to 16777216 – show variables
    • Check the value of max_heap_table_size variable
    • Check the value of tmp_table_size variable

Dong Shin 02.06.2014

  • fixed FA to display Req Editor window for non-admins
  • working on query for reports
    • sample query to get two consecutive months of data
    • SELECT uid,MONTH(CURDATE()) AS MONTH_NUM,
      CASE
      WHEN (MONTH(CURDATE())=10) THEN month_1
      WHEN (MONTH(CURDATE())=11) THEN month_2
      WHEN (MONTH(CURDATE())=12) THEN month_3
      WHEN (MONTH(CURDATE())=1) THEN month_4
      WHEN (MONTH(CURDATE())=2) THEN month_5
      WHEN (MONTH(CURDATE())=3) THEN month_6
      WHEN (MONTH(CURDATE())=4) THEN month_7
      WHEN (MONTH(CURDATE())=5) THEN month_8
      WHEN (MONTH(CURDATE())=6) THEN month_9
      WHEN (MONTH(CURDATE())=7) THEN month_10
      WHEN (MONTH(CURDATE())=8) THEN month_11
      WHEN (MONTH(CURDATE())=9) THEN month_12
      ELSE ‘no month’
      END
      AS month_1,

      CASE
      WHEN (MONTH(CURDATE())=10) THEN month_2
      WHEN (MONTH(CURDATE())=11) THEN month_3
      WHEN (MONTH(CURDATE())=12) THEN month_4
      WHEN (MONTH(CURDATE())=1) THEN month_5
      WHEN (MONTH(CURDATE())=2) THEN month_6
      WHEN (MONTH(CURDATE())=3) THEN month_7
      WHEN (MONTH(CURDATE())=4) THEN month_8
      WHEN (MONTH(CURDATE())=5) THEN month_9
      WHEN (MONTH(CURDATE())=6) THEN month_10
      WHEN (MONTH(CURDATE())=7) THEN month_11
      WHEN (MONTH(CURDATE())=8) THEN month_12
      ELSE ‘no month’
      END
      AS month_2
      FROM `obligations_outlays` WHERE 1

Dong Shin 02.03.2014

  • working on FMP viewer for FA – done, testing on-site tomorrow?
    • added FMPDataNavContent to AddBudgetCenterWindow, non-editable textArea!
    • query to get FMPs
      •  SELECT * FROM financial_mitigation_plans fmp LEFT JOIN obligations_outlays AS oo ON fmp.obligation_outlay_uid = oo.uid WHERE oo.project_id = 24 AND oo.funding_id = 1 AND year_count = 1 AND fmp.month = 2