CREDIT_AGENT_DETAILS_V.sql 1.01 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

create or replace view credit_agent_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='AGENT' and c.bp_id_agent_level1=bp_id