Dong Shin 10.24.2012

  • query to find offending (?) FMPs for underperforming obligations/outlays
    • SELECT *
      FROM `financial_mitigation_plans` fmp1, financial_mitigation_plans fmp2
      WHERE fmp1.obligation_outlay_uid = fmp2.obligation_outlay_uid
      AND fmp1.financial_mitigation_plan = fmp2.financial_mitigation_plan
      AND fmp1.explanation1 = fmp2.explanation1
      AND fmp1.explanation2 = fmp2.explanation2
      AND fmp1.explanation3 = fmp2.explanation3
      AND fmp1.explanation4 = fmp2.explanation4
      AND fmp1.explanation5 = fmp2.explanation5
      AND fmp1.month + 1 = fmp2.month
      AND fmp1.obligation_outlay_uid IN
      (SELECT o.uid
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE
      p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count
      AND o.year + o.year_count = 2014
      AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
      AND o.month_1 <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95)))