con_contract_change_req_lv.sql 3.78 KB
Newer Older
1
CREATE OR REPLACE VIEW CON_CONTRACT_CHANGE_REQ_LV AS
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
select t.change_req_id,
       t.batch_id,
       t.change_quotation,
       t.change_lease_item,
       t.change_bp,
       t.change_req_number,
       t.document_type,
       (select t1.description
          from hls_document_type t1
         where t1.document_type = t.document_type) as document_type_n,
       t.document_category,
       t.business_type,
       t.company_id,
       t.contract_id,
       t.req_status,
       (select t1.code_value_name
          from sys_code_values_v t1
         where t1.code = 'CON_CHANGE_REQ_STATUS'
           and t1.code_value = t.req_status) as req_status_n,
       t.req_date,
       t.description,
       t.wfl_instance_id,
       t.calc_session_id,
       t.owner_user_id,
       t.submit_date,
       t.approved_date,
       t.created_by,
       (SELECT su.user_name FROM sys_user su WHERE su.user_id=t.created_by)created_by_n,
       t.creation_date,
       t.last_updated_by,
       t.last_update_date,
       t.ccr_price_list,
       t.ccr_calc_session_id,
       t.simulation_flag,
       decode(t.simulation_flag,'Y','Y','') as simulation_flag_n,
       reschedule_flag,
       decode(t.reschedule_flag,'Y','Y','') as reschedule_flag_n,
       t.et_flag,
       decode(t.et_flag,'Y','Y','') as et_flag_n,
       t.tenant_change_flag,
       decode(t.tenant_change_flag,'Y','Y','') as tenant_change_flag_n,
       t.bank_acc_change_flag,
       decode(t.bank_acc_change_flag,'Y','Y','') as bank_acc_change_flag_n,
       t.lease_item_change_flag,
       decode(t.lease_item_change_flag,'Y','Y','') as lease_item_change_flag_n,
       t.mortgage_change_flag,
       decode(t.mortgage_change_flag,'Y','Y','') as mortgage_change_flag_n,
       t.guarantor_change_flag,
       decode(t.guarantor_change_flag,'Y','Y','') as guarantor_change_flag_n,
       t.contract_cancel_flag,
       decode(t.contract_cancel_flag,'Y','Y','') as contract_cancel_flag_n,
       t.cashflow_change_flag,
       decode(t.cashflow_change_flag,'Y','Y','') as cashflow_change_flag_n,
       cc.contract_number,
       cc.contract_name,
       cc.bp_id_tenant,
       (select  m.bp_name from hls_bp_master m where m.bp_id=cc.bp_id_tenant) as bp_id_tenant_n,
59
       (select  m.bp_name from hls_bp_master m,con_contract cct where m.bp_id=cct.bp_id_tenant AND cct.contract_id=t.contract_id) as s_bp_id_tenant_n,
60 61 62 63 64 65 66 67 68 69
       cc.inception_of_lease,
       nvl(t.et_ins_derate_amount,0) et_ins_derate_amount,
       (select sum(cf.principal) - sum(nvl(cf.received_principal, 0))
          from con_contract_cashflow cf
         where cf.contract_id = t.change_req_id
           and cf.cf_item = 1
           and cf.cf_direction = 'INFLOW') outstanding_principal,
           nvl(t.et_fee,500) et_fee,
           nvl(t.et_total_amount,0) et_total_amount,
           nvl(t.et_interest,0) et_interest,
70 71 72 73 74 75 76
           --提前结清 start
           nvl(t.FUND_POSSESSION_TIME,0)FUND_POSSESSION_TIME,--资金占用天数
           NVL(t.FUND_POSSESSION_COST,0)FUND_POSSESSION_COST,--资金占用费
           NVL(t.FUND_POSSESSION_RATE,0)FUND_POSSESSION_RATE,--资金占用费利率
           NVL(t.ET_DUE_AMOUNT,0)ET_DUE_AMOUNT,--提前结清租金
           NVL(t.SUM_UNRECEIVED_PRINCIPAL,0)SUM_UNRECEIVED_PRINCIPAL,--剩余本金
           NVL(t.ET_INTEREST_RATE,cc.int_rate_display)ET_INTEREST_RATE,--提前结清利率
77 78 79 80 81 82
           cc.bp_id_agent_level1,
          (SELECT m.bp_name
             FROM hls_bp_master m
            WHERE m.bp_id = cc.bp_id_agent_level1)
             AS bp_id_agent_level1_n,
             nvl(t.residual_value,cc.residual_value)residual_value,
83 84
             t.ccr_reason,
             t.termination_date
85
           --提前结清 end
86 87
  from con_contract_change_req t,
       con_contract cc
88 89
 where cc.contract_id = t.change_req_id
 AND cc.data_class = 'CHANGE_REQ'
90
;