Dong Shin 01.23.2014

  • bugs from site
    • Funded Amount is different between FA and RA – verified… will try new SWF’s tomorrow
    • need to be able to edit the COGNOS data to resolve duplicate errors – Lenny recommended using Voucher ID(?)
  • working on query for reports – query to retrieve the data….
    • SELECT ‘Obligation Goal’ AS oo_type, p.*, bc.*,
      (bc.funded_budget * obligation_month_1 / 100) as month_1,
      (bc.funded_budget * obligation_month_2 / 100) as month_2,
      (bc.funded_budget * obligation_month_3 / 100) as month_3,
      (bc.funded_budget * obligation_month_4 / 100) as month_4,
      (bc.funded_budget * obligation_month_5 / 100) as month_5,
      (bc.funded_budget * obligation_month_6 / 100) as month_6,
      (bc.funded_budget * obligation_month_7 / 100) as month_7,
      (bc.funded_budget * obligation_month_8 / 100) as month_8,
      (bc.funded_budget * obligation_month_9 / 100) as month_9,
      (bc.funded_budget * obligation_month_10 / 100) as month_10,
      (bc.funded_budget * obligation_month_11 / 100) as month_11,
      (bc.funded_budget * obligation_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      WHERE oo.year = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      UNION
      SELECT ‘Outlay Goal’ AS oo_type, p.*, bc.*,
      (bc.funded_budget * outlay_month_1 / 100) as month_1,
      (bc.funded_budget * outlay_month_2 / 100) as month_2,
      (bc.funded_budget * outlay_month_3 / 100) as month_3,
      (bc.funded_budget * outlay_month_4 / 100) as month_4,
      (bc.funded_budget * outlay_month_5 / 100) as month_5,
      (bc.funded_budget * outlay_month_6 / 100) as month_6,
      (bc.funded_budget * outlay_month_7 / 100) as month_7,
      (bc.funded_budget * outlay_month_8 / 100) as month_8,
      (bc.funded_budget * outlay_month_9 / 100) as month_9,
      (bc.funded_budget * outlay_month_10 / 100) as month_10,
      (bc.funded_budget * outlay_month_11 / 100) as month_11,
      (bc.funded_budget * outlay_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      WHERE oo.year = 1 AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      UNION
      SELECT ‘Committed’ AS oo_type, p.*, bc.*,
      SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
      SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
      SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
      SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
      SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
      SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
      SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
      SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
      SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
      SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
      SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
      SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN committed_amounts AS ca ON ca.budget_center_id = bc.uid
      GROUP BY budget_center_id
      UNION

      SELECT
      IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
      p.*, bc.*,
      month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      WHERE oo.year_count = 1
      AND bc.fiscal_year = IF(MONTH(CURDATE()) < 10, YEAR(CURDATE()), YEAR(CURDATE()) + 1)
      AND (oo.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced Outlay) $:’)