Dong Shin 03.05.2014

  • put old Reqonciler back to resolve the datagrid issue
  • updated 2nd year query – success after some fiddling…
    • clean obligation/outlays in the first step using SET month = 0, not SET month=NULL.
    • changed the query to update Obligations, not Outlays
  • query to update Obligations/Outlays when it reaches 100%?
    • UPDATE obligations_outlays oo
      SET month_12 = GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0))
      WHERE oo.year_count = 1
      AND GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0)) >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • query to find second year that needs to be set to 100%
    • SELECT IFNULL(oo1.month_1,0), IFNULL(oo2.month_12,0)
      FROM obligations_outlays oo1, obligations_outlays oo2, budget_centers bc
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND oo1.project_id = oo2.project_id AND oo1.funding_id = oo2.funding_id AND oo1.type = oo2.type
      AND (
      oo1.type = ‘Outlay $ (Reported in FACTS)’
      OR oo1.type = ‘Reported FACTS Obligated $:’
      )
      AND oo2.month_12 >= bc.total_budget
      AND oo1.funding_id = bc.uid