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