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