Dong Shin 04.03.2014

  • deployed new FA/RA/Reqonciler along with jar files. Still no FMP shown in FA, puzzled…..
  • fixed Logger to filter the items correctly
  • updating query to speed up RA… This would do it!
    • SELECT * FROM
      (SELECT ‘OVERDUE’ AS status, bc.*
      FROM budget_centers bc
      WHERE uid IN (
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) < 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)
      OR ISNULL(month_7) OR ISNULL(month_8) OR ISNULL(month_9) OR ISNULL(month_10) OR ISNULL(month_11) OR ISNULL(month_12))
      UNION
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) = 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)))
      UNION
      SELECT ‘CURRENT’ AS status, bc.*
      FROM budget_centers bc WHERE uid NOT IN (
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) < 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)
      OR ISNULL(month_7) OR ISNULL(month_8) OR ISNULL(month_9) OR ISNULL(month_10) OR ISNULL(month_11) OR ISNULL(month_12))
      UNION
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) = 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)))
      UNION
      SELECT ‘DUE’ AS status, bc.*
      FROM budget_centers bc WHERE uid NOT IN (
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) < 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)
      OR ISNULL(month_7) OR ISNULL(month_8) OR ISNULL(month_9) OR ISNULL(month_10) OR ISNULL(month_11) OR ISNULL(month_12))
      UNION
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) = 2014
      AND (ISNULL(month_1) OR ISNULL(month_2) OR ISNULL(month_3) OR ISNULL(month_4) OR ISNULL(month_5) OR ISNULL(month_6)))
      AND uid IN (
      SELECT
      oo.funding_id
      FROM obligations_outlays oo
      WHERE (oo.type LIKE ‘%PM%’ OR oo.type LIKE ‘Planned%’)
      AND (year + year_count + 1) = 2014
      AND (ISNULL(month_7)))) AS t
      LEFT JOIN committed_amounts AS ca ON t.uid = ca.budget_center_id
      LEFT JOIN projects AS p ON t.project_id = p.uid