create view vi_bond_compensatory as (
select
  lrp.ID             AS id, 
  lrp.RENT           AS rent, 
  IFNULL(lri.irent,0) AS irent, 
  IFNULL(ddi.CAUTION_MONEY,0) AS caution_money, 
  lrp.PLAN_DATE      AS plan_date, 
  lrp.CONTRACT_ID    AS contract_id, 
  lci.distributor_id AS distributor_id, 
  lci.PROJECT_ID     AS project_id, 
  lrp.PLAN_LIST      AS plan_list, 
  lrp.RENT-IFNULL(lri.irent,0)-IFNULL(ddi.CAUTION_MONEY,0) as need_caution_money 
from lb_contract_info lci
     left join lc_rent_plan lrp
       on lci.ID = lrp.CONTRACT_ID
    left join (select
                 SUM(IFNULL(RENT,0)) AS irent,
                 PLAN_ID AS plan_id
               from lc_rent_income
               group by PLAN_ID) lri on lri.plan_id = lrp.ID
   left join (select
                RENT_PLAN_ID AS RENT_PLAN_ID,
                SUM(IFNULL(CAUTION_MONEY,0)) AS CAUTION_MONEY
              from d_depositreturn_info
              where MONEY_TYPE = 'bondRepay'
              group by RENT_PLAN_ID) ddi on ddi.RENT_PLAN_ID = lrp.ID
where lci.BUSINESSTYPE = '1'  and lci.CONTRACT_STATUS = '31' and (lrp.BATCH_STATUS<>'process' or  lrp.BATCH_STATUS IS NULL) );