Author Archives: gvr4wd

About gvr4wd

...hmmm

Dong Shin 01.02.2014

removed Committed Amount/Date from Unclaimed/Claimed Contracts section in Req Info
cleaned up Phil’s Test data for Reports queries
- had to add distinct values from Req Description, Req ID, Req Type, Sub Budget Center Number, Types

Dong Shin 12.20.2013

continue working on FA
- working on adding Committed Amounts – done
- changed all references to Funded Budget to sum of Committed Amounts
- move all funded budget from budget_centers table to committed_amounts table
  - INSERT INTO committed_amounts( budget_center_id, committed_amount, committed_date ) SELECT uid, funded_budget, now( ) FROM budget_centers;

Dong Shin 12.19.2013

deployed FinancialAssistant.jar, FA, RA,Req – all is well
discussed hot to handle multiple Commitments for a Requisition with Lenny
working on FA
- added Financial Data datagrid to Requisition Editor
- working on adding Committed Amount and Date to the Requisition Editor
- added committed_amounts table to the database – DBUpdateEnhancements.sql
- created CommittedAmount.as and CommittedAmountsTable.as for DB access
  - CREATE TABLE IF NOT EXISTS `committed_amounts` (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `budget_center_id` int(11) NOT NULL,
    `committed_amount` decimal(10,2) NOT NULL,
    `committed_date` date NOT NULL,
    PRIMARY KEY (`uid`),
    KEY `budget_center_id` (`budget_center_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Dong Shin 12.18.2013

deployed new FA server jars… all is well.
few notes from Lenny this morning @ Phil’s blog
my Flex workspace is behaving bad, again.!
working on FA/RA
- added query description to Reqonciler’s Pre-Processing window
- fixed Req Funded Amount not displaying correctly…
- changed FMP title to Monthly Status Report

Dong Shin 12.17.2013

deployed new FA/RA
working on COGNOS Data white list columns
- columns should be compared to
  - Budget Center, Budget Center Name,Sub-Budget Center,Sub-Budget Center Name,Executing Budget Center,EBC Name,Appropriation Year,Requisition ID,PO Reference,ACR,Committed Date,Committed Amount,Obligation Date,Obligation Amount,Expensed Date,Expensed Amount
- added congos.whitelist to FinancialAssistant.properties
- modified the FA server to compare with the specified fields using hash map of column names and column headers
- files to deploy – FinancialAssistant.jar, commons-lang-2.5.jar, FinancialAssistant.properties
fixed contrancts_cognos_history not updating..
working on Presentation Queries…

Dong Shin 12.16.2013

deployed new Pre-Processing queries, Lenny will review
changed Project Description to Project BLUF in FA
changed the way how FMP’s are handled
- created mitigationPlanConfig.xml to describe the questions
- FMP fields are dynamically created

Dong Shin 12.12.2013

working on queries for pre-processing (COGNOS conflicts)
- find multiple obligation dates
  SELECT * FROM contracts_cognos WHERE uid IN (SELECT a.uid FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.requisition_id = b.requisition_id AND a.sub_budget_center = b.sub_budget_center AND NOT ISNULL(a.obligation_date) AND NOT ISNULL(b.obligation_date) WHERE a.uid <> b.uid ORDER BY a.uid)
- Committed Amount greater than Req Allocation
  SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(committed_amount) > SUM(total_budget)))
- Obligated Amount greater than Req Allocation
  SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
  SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
- Outlay Amount greater than Req Allocation
  SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
  SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
- Obligated Amount greater than Committed Amount
  SELECT * FROM contracts_cognos WHERE obligated_amount > committed_amount
- Outlay Amount greater than Committed Amount
  SELECT * FROM contracts_cognos WHERE expensed_amount > committed_amount
- Outlay Amount greater than Obligated Amount
  SELECT * FROM contracts_cognos WHERE expensed_amount > obligated_amount

Dong Shin 12.11.2013

FMP’s and Invoice updates are not recorded in query_logs – fixed
- added REPLACE to supported types in recordQuery()
copy previous month’s FMP doesn’t work properly, especially from the previous year -fixed

Dong Shin 12.06.2013

deployed new RA and DB updates – forgot to check for query_logs!
went over the pre-processing query with Lenny, duplicate duplicates are showing in the result window!
- SELECT * FROM contracts_cognos
  WHERE uid IN (SELECT a.uid FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.obligated_amount = b.obligated_amount
  WHERE a.uid <> b.uid ORDER BY a.uid)
disabled auto-refresh of projects data in RA
updates for cognos data views!

Dong Shin 12.05.2013

deployed new Reqonciler and post-processing queries
found query log saving incorrect user id in RA – fixed
meeting with Finance Folks at S35P
need to get pre-processing working
- pre-processing query to find duplicate obligations amount
- SELECT * FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.obligated_amount = b.obligated_amount AND a.requisition_id = b.requisition_id WHERE a.uid <> b.uid ORDER BY a.uid
fixed update outlay query in Reqonciler to null out future months
- UPDATE obligations_outlays oo LEFT JOIN __view_monthly_obligations_outlays_by_req_id AS vv ON vv.bc_uid = oo.funding_id SET oo.month_1 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<10,NULL,vv.month_1_outlay),vv.month_1_outlay), oo.month_2 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<11,NULL,vv.month_2_outlay),vv.month_2_outlay), oo.month_3 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<12,NULL,vv.month_3_outlay),vv.month_3_outlay), oo.month_4 = IF(YEAR(CURDATE())=(year+year_count-2),IF(MONTH(CURDATE())<13,NULL,vv.month_4_outlay),vv.month_4_outlay), oo.month_5 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<2,NULL,vv.month_5_outlay),vv.month_5_outlay)), oo.month_6 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<3,NULL,vv.month_6_outlay),vv.month_6_outlay)), oo.month_7 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<4,NULL,vv.month_7_outlay),vv.month_7_outlay)), oo.month_8 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<5,NULL,vv.month_8_outlay),vv.month_8_outlay)), oo.month_9 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<6,NULL,vv.month_9_outlay),vv.month_9_outlay)), oo.month_10 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<7,NULL,vv.month_10_outlay),vv.month_10_outlay)), oo.month_11 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<8,NULL,vv.month_11_outlay),vv.month_11_outlay)), oo.month_12 = IF(YEAR(CURDATE())<(year+year_count-1),NULL,IF(YEAR(CURDATE())=(year+year_count-1),IF(MONTH(CURDATE())<9,NULL,vv.month_12_outlay),vv.month_12_outlay)) WHERE type = ‘Outlay $ (Reported in FACTS)’ AND (year + year_count) = (fiscal_year + 1)

Dong Shin 12.04.2013

set up sandbox FA apps for Phil’s test data
imported project and cognos data from Phil’s
added clean up Obligation/Outlays queries to Reqonciler
set clean up queries to be optional (0 in order)

Dong Shin 12.02.2013

tried the failed query back at the office, no success? How did I run this?
new query to update obligations/outlays….
UPDATE obligations_outlays o1
JOIN obligations_outlays o2
ON o1.funding_id = o2.funding_id AND o1.project_id = o2.project_id
SET o1.month_1 = o1.month_1 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_2 = o1.month_2 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_3 = o1.month_3 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_4 = o1.month_4 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_5 = o1.month_5 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_6 = o1.month_6 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_7 = o1.month_7 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_8 = o1.month_8 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_9 = o1.month_9 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_10 = o1.month_10 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_11 = o1.month_11 + IF(ISNULL(o2.month_12), 0, o2.month_12),
o1.month_12 = o1.month_12 + IF(ISNULL(o2.month_12), 0, o2.month_12),
WHERE o1.type = ‘Outlay $ (Reported in FACTS)’ AND (o1.year + o1.year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
AND o1.year_count = 2
AND o2.type = ‘Outlay $ (Reported in FACTS)’ AND o2.year_count = 1

Dong Shin 11.26.2013

deployed new FA/RA
- outlay not adding up from the previous year
- add Total Funded to Project Mgmt Panel
working on FA
- query to update second year of Outlays
- UPDATE obligations_outlays o1,
  (SELECT month_12 FROM obligations_outlays oo2 WHERE type = ‘Outlay $ (Reported in FACTS)’ AND year_count = 1
  AND o1.funding_id = o2.funding_id AND o1.project_id = o2.project_id
  ) AS o2
  SET o1.month_1 = o2.month_12 + o1.month_1,
  o1.month_2 = o2.month_12 + o1.month_2,
  o1.month_3 = o2.month_12 + o1.month_3,
  o1.month_4 = o2.month_12 + o1.month_4,
  o1.month_5 = o2.month_12 + o1.month_5,
  o1.month_6 = o2.month_12 + o1.month_6,
  o1.month_7 = o2.month_12 + o1.month_7,
  o1.month_8 = o2.month_12 + o1.month_8,
  o1.month_9 = o2.month_12 + o1.month_9,
  o1.month_10 = o2.month_12 + o1.month_10,
  o1.month_11 = o2.month_12 + o1.month_11,
  o1.month_12 = o2.month_12 + o1.month_12
  WHERE type = ‘Outlay $ (Reported in FACTS)’
  AND (year + year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
  AND year_count = 2

Dong Shin 11.25.2013

deployed new RA
verified that Fiscal Year is the cause of the incorrect outlay/obligations import…..
fixed login id not showing in RA
reworked views and update obligation/outlay queries for the Fiscal Year!
added ReqView in QueryBuilder – double clicking shows the requisition details, removed the project view
removed Upload COGNOS data button – use Reqonciler!
modified the FA server code to keep upload history – FinancialAssistant.jar

Dong Shin 11.22.2013

continue working on FA/RA
- added ReqDetailsWindow – show Requisition Details and financial data
- successfully ingested test data from Phil’s code
- added Portfolio Admins to view data in Requisition Assistant
- working on COGNOS ingest queries

viztales

Dimension reduction, State, Orientation, and Speed

Author Archives: gvr4wd

About gvr4wd

Dong Shin 01.02.2014

Dong Shin 12.20.2013

Dong Shin 12.19.2013

Dong Shin 12.18.2013

Dong Shin 12.17.2013

Dong Shin 12.16.2013

Dong Shin 12.12.2013

Dong Shin 12.11.2013

Dong Shin 12.06.2013

Dong Shin 12.05.2013

Dong Shin 12.04.2013

Dong Shin 12.02.2013

Dong Shin 11.26.2013

Dong Shin 11.25.2013

Dong Shin 11.22.2013