create or replace view credit_tenant_details_v as
     select
           hv.bp_id,
           hv.bp_code,
           hv.bp_name,
           c.contract_number,
          (select bp_name from hls_bp_master hbm where hbm.bp_id=hv.bp_id)BP_ID_TENANT,
          --占用额度
          (select c.total_rental+c.down_payment from dual) used_amount,
          --释放额度
          (  select nvl(sum(co.csh_write_off_amount),0)
          from CSH_WRITE_OFF co
          where
            (  co.cf_item=1 and co.cf_type=1  and  co.reversed_flag='N' and co.contract_id=c.contract_id)
         or (  co.cf_item=2 and co.cf_type=2  and  co.reversed_flag='N' and co.contract_id=c.contract_id)
         or (  co.cf_item=200 and co.cf_type=1  and  co.reversed_flag='N' and co.contract_id=c.contract_id)
         or (  co.cf_item=250 and co.cf_type=1  and  co.reversed_flag='N' and co.contract_id=c.contract_id) )RELEASED_AMOUNT

       from hls_bp_master_v hv,con_contract c
       where  hv.bp_category='TENANT' and c.bp_id_tenant=bp_id