- COGNOS Data to update PPM data
- Match COGNOS Requisition ID to MIPR (Funding Transaction Number)
- COGNOS Expensed Amount summed by month
- Overwrite existing PPM data
- Update continuously (daily?)
- If there is no value for Expensed Amount COGNOS column, then skip
- If there is a negative amount – i.e. ($10,123), subtract in the sum
- Ingest as “FACTS_Outlay_update”
- created dummy FACTS_Outlay_update spreadsheet and ingested it – FACTS_Outlay_update.xls
- working on queries to update PPM data – update query can be constructed in python script using the query below
- SELECT requisition_id,
appropriation_year,
SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) as month,
expensed_date,
SUM(expensed_amount),
CONCAT(‘UPDATE project_portfolio.obligations_outlays o SET ‘,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 1, ‘month_4’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 2, ‘month_5’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 3, ‘month_6’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 4, ‘month_7’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 5, ‘month_8’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 6, ‘month_9’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 7, ‘month_1-‘,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 8, ‘month_11’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 9, ‘month_12’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 10, ‘month_1’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 11, ‘month_2’,
IF (SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) = 12, ‘month_3’, ‘unknown’))))))))))))
, “=”, SUM(expensed_amount), ” WHERE o.year + o.year_count = ” + appropriation_year + ” AND o.year_count = 1 AND o.type = ‘Outlay $ (Reported in FACTS'” ) as sqlstring
FROM `facts_outlay_update`
WHERE LENGTH(requisition_id) > 6 AND
(SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) > 0 AND SUBSTR(expensed_date, 1, LOCATE(‘/’, expensed_date)-1) < 13)
GROUP BY month, requisition_id
- SELECT requisition_id,
- When adding Budget Center to a Project, Budget Center columns selection should default to previous entry except for
- Funding Transaction Name
- Funding Transaction Number
- Funding Transaction Type
- Org
- Location
Category Archives: VISIBILITY
Phil 11.27.12
8:00 – 2:00 ESSO
- Discussion with Lenny and Dong about how to read in cognos data and use it to populate the FACTS fields in PPM and PA.
- Backups
- Working on the JDeveloper JSF tutorials – managed to crash JDeveloper.
- Talked to Dong about next dev environments. We discussed Adobe and Netbeans in addition to what we’re using now. My thought is that if we can make a table and chart that allow us to visualize data sitting in a DB here and on site, we have a viable option. If it’s open source and supported by a player unlikely to go out of business any time soon, then so much the better.
2:00 – 4:00 FP
- Qualitative Methods.
Phil 11.26.12
8:30 – 4:30 ESSO
- Backups
- More interaction with Don abo0ut what kind of visualizations he wants. Printed out a description of the putative table that we need to produce. Dong and I will try to code it tomorrow in site.
- Put together FlexiCharts of ACC, since that appears to have vanished.
- Got started back on JDeveloper and ADF.
Dong Shin 11.26.2012
- got Visibility Login working using Oracle ADF!
Phil 11.21.12
8:00 – 4:00 ESSO
- Meetings with everyone, Dan, Lenny, Chris.
- Backups
- 2:00pm meeting with Kristen
- Meeting went well. I basically walked through how to use the Data Visualization and how PPM and PA generated data and where it was stored. We then talked about generating more graphics for her and how scripting could produce the tables she (implicitly) needs for the charts she wants.
Dong Shin 11.21.2012
- going through Oracle ADF tutorials
- JSF components showcase – trend?
- RichFaces
- PrimeFaces – charts generated as images!
- ICEfaces – charts generated as images!
- OpenFaces – Data Table
- ADF Faces component guide
- Custom Declarative Components in ADF Faces Rich Client
Phil 11.20.12
7:30 – 2:30 ESSO
- Deployed new PPM and IM
- Discussions with Don about progress
- Said hello to Chris
- Discussions with Lenny, Carla and Tangie about progress
- Discovered that the IM was broken on fgmdev, and I had forgotton how the system was set up. After a bit of help from Dong, fixed and updated the Vis2 and IM SWFs.
2:30 – 3:30 FP
- Interview with Allison Y
Dong Shin 11.19.2012
- JDeveloper tutorials
- http://docs.oracle.com/cd/E18941_01/tutorials/toc.htm
- MySQL set up is painful! requires mysql jar file in multiple locations when using WebLogic server for testing
- using Oracle Database Expression Edition 11g Release 2 – got it working!
- JDeveloper/Maven Integration
- Creating and Using an ADF Declarative Component
- How to deploy a 11g ADF application to Tomcat 6.x – should work with 7
Phil 11.19.12
7:30 – 10:00, 12:00 – 3:30 ESSO
- Updated the truststore file on the server
- Backups
- Some discussions with Don about progress
- Cleaning up the login screen on Vis2
- Cleaning up the version info on Ingest Manager
- Due to some different configuration data between Dong’s environment and mine, spent a few hours fishing for red herrings. Done now thoug, with a new build for tomorrow.
10:00 – 12:00 – FP
- Contextual user interface design and metaphors
Dong Shin 11.16.2012
- interview
- talked to Len about duplicate projects warning bug. The current criteria is the same number of years with same total FY budget, disabled for now. may want to add it later
- looking into Oracle JDeveloper/ADF
Phil 11.16.12
7:30 – 9:30, 11:30 – 3:30 ESSO
- Backups
- Working on new password
- Looking onto Oracle ADF
- Downloaded the Eclipse version, but it doesn’t seem as nice as JDeveloper that Dong’s using. Going to pull that one down next
- Very nice! Need to see how to integrate with subversion
9:30- 11:30 FP
- AV scheduling meeting
Phil 11.15.12
8:00 – 2:00 ESSO
- Backups and meetings, oh my.
- Working on getting Don and Lenny to focus on “report wishlists”. Meeting in two weeks to discuss?
- Found a “duplicate project” bug(?) for Dong. He says that there are a set of criteria that Trish layed out that might be getting triggered. Need to look into that.
- Need to clear out old password when changing password.
2:00 – 4:00 FP
- Qualitative methods. Need to look into developing data dictionary-based mechanism for that which is able to point into AV stream.
Phil 11.14.12
8:00 – 4:00 ESSO
- Deployed new versions of PPM, PA (new view only permissions), and Vis2 (bug fixes). Checked the fact that the bug fixes in fact do work by having Dan, my favorite bug-finding user try out this system.
- Cleaned out old backups to free up disk space. All teh backups are still on the network drive, so this was redundant. Still keeping the most recent backups on the server in case everything goes down and I lose connectivity with the network drive, since that’s happened before.
- Back to GWT. Rather than the book, I’m going to try something quicker. We’ll see if Google keeps up on its own technology well.
- Made a GWT_HOME environment variable so that I can run webAppCreator.
- That seems to be working well. Even ran everything from the command line.
- Importing it into the IDE didn’t result in a project that was recognized as a GWT project. Had to re-create it in the IDE.
- Stockwatcher (again)
- Create a GWT Project – up and running. Playing around with the components.
Dong Shin 11.14.2012
- Alert_UpdateAlertsNotResolvedIn7Days.py – should run every day to update alert status
- rearranged panels VSS4 so the panel select works properly
- modified backup.sh to delete files older than 3 days
Dong Shin 11.13.2012
- continue working on Alerts
- alerts that are not closed and older than 7 days query
- SELECT *, DATEDIFF(NOW(), timestamp) AS days
FROM ppm_alerts.alerts
WHERE status <> ‘CLOSED’
AND DATEDIFF(NOW(), timestamp) > 7
- SELECT *, DATEDIFF(NOW(), timestamp) AS days
- Alert_8_notResolvedIn7Days.py – not saving into alerts table, report only
- saved table_queries – visibility_scripting.table_queries.sql
- alerts that are not closed and older than 7 days query

You must be logged in to post a comment.