Dong Shin 10.28.2013

  • 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