Category Archives: Dong Shin

Dong Shin 04.24.2014

  • added authorized users control in Reqonciler – setupModel.xml
  • continue working on Post-Processing
    • added saving new Obligations and Outlays as the first step
    • added queries to update obligations and outlays separately
      • need to update  __view_monthly_obligations_by_req_id,  __view_monthly_obligations_by_contract,  __view_monthly_outlays_by_req_id,  __view_monthly_outlays_by_contract
      • update obligations outlays for second year hanging….. ugh.!

Dong Shin 04.23.2014

  • got the dump from the _reqonciler_processing table
  • talked Lenny about the columns that could have missing data – ACR for Obligations and ACR, PO Reference for Outlays
  • added obligations/outlays blacklist and whitelist to FinancialAssistant.properties
    • added \FinancialAssistant\src\main\webapp\WEB-INF\ to ClassPath in Run Configurations in Eclipse to read the prop file!!
  • updating pre-processing queries – done
  • updating post-processing queries
  • added appropriation column and removed unnecessary columns from budget_center_contracts
    • ALTER TABLE `budget_center_contracts` ADD `appropriation` VARCHAR(20) NULL AFTER `appropriation_year`;
    • ALTER TABLE `budget_center_contracts`
      DROP `budget_center_name`,
      DROP `sub_budget_center_name`,
      DROP `ebc_name`,
      DROP `committed_date`,
      DROP `committed_amount`,
      DROP `po_start_date`,
      DROP `po_end_date`,
      DROP `contract_type`,
      DROP `po_type_code`,
      DROP `contract_no`,
      DROP `vendor_id`;

Dong Shin 04.22.2014

  • ContractsParser.java – testing completed
    • need to add Voucher ID and Appropriation to the spreadsheets
    • skipping rows based on NULL values? need to find what columns could be null
    • need to get all the pre and post processing queries from the site
  • Modified Reqonciler to use the new interface.

Dong Shin 04.18.2014

  • had discussion with Lenny on Reqonciler – needs to process Obligations and Outlays separate
    • Obligations
      • Budget Center, Sub Budget Center, Executing Budget Center, Appropriation Year, Appropriation, Requisition ID, PO Reference, ACR, Obligated Date, Obligated Amount (Bolds are the uniquely identifying fields)
      1. If all fields above match, ignore
      2. If only the obligation date changes, ignore
    • Outlays
      • Budget Center, Sub Budget Center, Executing Budget Center, Appropriation Year, Appropriation, Requisition ID, PO Reference, ACR, Expensed Date, Expensed Amount, Voucher ID (Bolds are the uniquely identifying fields)
      • If all fields match, ignore
    • Pre-Processing Steps
      • If line is ignored in previous steps, it should cascade down to follow on steps and not be shown
    • Appropriations – O&M, PROC, RDT&E
  • working on Reqonciler
    • created new tables – cognos_obligations and cognos_outlays
    • working on ContractsParser.java….

Dong Shin 04.08.2014

  • abnormal data in briefing data yesterday…. fixed missing ‘.’ caused it.
  • working on new QueryBuilder for past two days….
  • Lenny/Christ wants Req Info in QueryBuilder when a Req info is displayed.
  • found that duplicate Req’s showing up in RA – fixed!
    • incorrect Status returned – bug in Fiscal Year – fixed!

Dong Shin 04.04.2014

  • FMP’s still not loaded to FA, need to look at events closely…
    • caused by missing entries in config file!!!! throws exception in the debug flash player, not in normal version. changed the code to show it in logger…..
  • discussed few stuffs with Lenny. looked at Obligations not adding up, wrong data!

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

Dong Shin 04.01.2014

  • Oracle doubles the speed of MySQL query handling – found this while reading about MariaDB, upgrade soon?
  • need to run 6 queries to update Obligations/Outlays data when claim/un-claim happens…
    • update obligations (4), update outlays (5), update outlays for second year (6), update months in year 1 to 100% (101), update month 1 year 2 to 100% from month 12 year 1 (200), update months in year 2 to 100% (201)
  • worked off most of the Action Items from 3/25/2014
    • All fields with $ should accept negative values – CurrencyTextInput
    • get a list of budget values for FY13 and FY14 charts
    • create a query for Planned Obligated vs. FACTS
    • Outlay $ Reported row should not be editable
    • invoice dialog box should not show if the line item is not a MIPR or EAO
    • total budget in slides should be project total, not requistiion total
    • Req Data Analysis should show Req Allocated, Req Funded (Committed)

Dong Shin 03.31.2014

  • deployed database updates for the big data table…. lot quicker than existing query!
    • add FMP’s and invoices?
    • tried to ingest to Visibility, but too may long fields to try….
    • added more fields to format as Currency
  • changed the colors of the chart items in Req Data Analysis panel to match the briefing charts
  • working on queries to update data when claim/un-claim happens in Project Editor

Dong Shin 03.28.2014

  • reworking Req Data Analysis using the new view – __view_project_detailed_data
    • done… lot faster than before!
    • committed amounts not cumulative, working on it. fixed! using functions….
  • created few support functions in mysql
    • get_cumulative_committed_amount(budget_center_id, year_count, month_number)
    • get_fiscal_month (Date)
    • get_fiscal_year (Date)
  • using MySQL Workbench instead of phpMyAdmin – more robust and convenient!

Dong Shin 03.27.2014

  • working on generating big data table for FA. __view_project_detailed_data
    • created sub views to work-around nested SELECT limitation in view
      • __view_yearly_totals_by_project, __view_financial_analysts_by_project, __view_portfolio_mgrs_by_project, __view_total_committed_amount_by_budget_center, __view_service_pocs_by_budget_center
    • remember not t use nested SELECTs in views!!!!
    • adding monthly committed amounts take too long to process….. 3 sec compared to 1 sec…
    • *THIS* is the join that produces a year data per row that has all obligations, outlays, and goals!
      • SELECT SUM(IF(oo.type LIKE ‘%Planned%Oblig%’,1,0)) AS planned_obligated_count,
        SUM(IF(oo.type LIKE ‘%Reported%Oblig%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%Planned%Outlay%’,1,0)) AS planned_outlay_count,
        SUM(IF(oo.type LIKE ‘%Reported%Outlay%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%PM%Actuals%’,1,0)) AS pm_actuals_count,
        SUM(IF(oo.type LIKE ‘%Outlay%FACTS%’,1,0)) AS outlay_facts_count,
        p.*, bc.*
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN obligations_outlays_goals oog ON oog.appropriation = p.appropriation AND oog.year = oo.year_count
        LEFT JOIN committed_amounts ca ON ca.budget_center_id = bc.uid AND (bc.fiscal_year – YEAR(ca.committed_date) + 1) = oo.year_count
        LEFT JOIN __view_yearly_totals_by_project AS yt ON yt.project_id = p.uid
        LEFT JOIN __view_financial_analysts_by_project AS fa ON fa.project_id = p.uid
        LEFT JOIN __view_portfolio_mgrs_by_project pm ON pm.project_id = p.uid
        LEFT JOIN __view_service_pocs_by_budget_center AS spbc ON spbc.budget_center_id = bc.uid
        LEFT JOIN __view_total_committed_amount_by_budget_center ca_totals ON ca_totals.budget_center_id = bc.uid
        GROUP BY oo.funding_id, oo.year_count