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)