Dong Shin 03.10.2014

  • Lenny reported that Req 173756 doesn’t show Obligation Amount / Date in Claimed section… will take a look tomorrow
  • working on little Flex App to verify the Financial Data
  • reworked Financial Data query for report to generate all the data for all years
  • __view_financial_data contains….
    • SELECT ‘Obligation Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (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
      UNION
      SELECT ‘Outlay Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (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
      UNION
      SELECT ‘Committed’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year,
      (IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date)) – bc.fiscal_year + 1) AS year_count,
      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, IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date))
      UNION
      SELECT
      IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year_count,
      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.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced) Outlay $:’)