- deployed Reqonciler, few bugs
- fixed not going to next pre-processing when no ignore is selected
- added uniqueColumnIdName to setupModel.xml for to use uid of the table
- made debug menu visible on login
- working on correct EA Obligations calculation for Query Builder
- created two view – MySQL doesn’t allow creating a view with sub query!!!
- __view_ea_obligations_cognos – individual months by cognos req_id
- __view_ea_obligations_by_req_id – summary using __view_ea_obligations_cognos
- based on this query -SELECT bc_uid, sub_budget_center, requisition_id, funded_budget,
SUM(month_1_obligated) AS month_1,
SUM(GREATEST (month_1_obligated, month_2_obligated)) AS month_2,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated)) AS month_3,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated)) AS month_4,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated)) AS month_5,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated)) AS month_6,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated)) AS month_7,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated)) AS month_8,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated)) AS month_9,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated)) AS month_10,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated, month_11_obligated)) AS month_11,
SUM(GREATEST (month_1_obligated, month_2_obligated, month_3_obligated, month_4_obligated, month_5_obligated, month_6_obligated, month_7_obligated, month_8_obligated, month_9_obligated, month_10_obligated, month_11_obligated, month_12_obligated)) AS month_12
FROM (
SELECT bc.uid as bc_uid, bc.sub_budget_center_number AS sub_budget_center, bc.req_id AS requisition_id, bc.funded_budget AS funded_budget,
SUM(IF(month(cc.obligation_date)=10, cc.obligated_amount, 0)) as month_1_obligated,
SUM(IF(month(cc.obligation_date)=11, cc.obligated_amount, 0)) as month_2_obligated,
SUM(IF(month(cc.obligation_date)=12, cc.obligated_amount, 0)) as month_3_obligated,
SUM(IF(month(cc.obligation_date)=1, cc.obligated_amount, 0)) as month_4_obligated,
SUM(IF(month(cc.obligation_date)=2, cc.obligated_amount, 0)) as month_5_obligated,
SUM(IF(month(cc.obligation_date)=3, cc.obligated_amount, 0)) as month_6_obligated,
SUM(IF(month(cc.obligation_date)=4, cc.obligated_amount, 0)) as month_7_obligated,
SUM(IF(month(cc.obligation_date)=5, cc.obligated_amount, 0)) as month_8_obligated,
SUM(IF(month(cc.obligation_date)=6, cc.obligated_amount, 0)) as month_9_obligated,
SUM(IF(month(cc.obligation_date)=7, cc.obligated_amount, 0)) as month_10_obligated,
SUM(IF(month(cc.obligation_date)=8, cc.obligated_amount, 0)) as month_11_obligated,
SUM(IF(month(cc.obligation_date)=9, cc.obligated_amount, 0)) as month_12_obligated
FROM budget_centers bc
LEFT JOIN budget_center_contracts AS bcc ON bc.uid = bcc.budget_center_id
LEFT JOIN contracts_cognos AS cc ON cc.sub_budget_center = bcc.sub_budget_center
AND SUBSTRING(cc.requisition_id, 5) = bcc.requisition_id
WHERE bc.req_type = ‘EA’ AND to_seconds(cc.obligation_date) > 59958230400
GROUP BY cc.requisition_id) AS t
GROUP BY bc_uid
- created two view – MySQL doesn’t allow creating a view with sub query!!!