CREATE OR REPLACE VIEW CON_CONTRACT_CHANGE_REQ_LV AS 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, (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, 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, --提前结清 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,--提前结清利率 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, t.ccr_reason, t.termination_date --提前结清 end from con_contract_change_req t, con_contract cc where cc.contract_id = t.change_req_id AND cc.data_class = 'CHANGE_REQ' ;