- 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
Dong Shin 11.27.2012
Leave a reply
