CREATE OR REPLACE VIEW CON_CHANGE_CONTRACT_LV AS
SELECT t1.contract_id,
       t1.calc_session_id,
       (SELECT t.calc_successful FROM hls_fin_calculator_hd t WHERE t.calc_session_id = t1.calc_session_id)calc_successful,
       t1.contract_number,
       t1.contract_name,
       t1.document_type,
       (SELECT dt.description
          FROM hls_document_type dt
         WHERE dt.document_type = t1.document_type) AS document_type_n,
       t1.document_category,
       (SELECT dc.description
          FROM hls_document_category dc
         WHERE dc.document_category = t1.document_category) AS document_category_n,
       t1.project_id,
       (SELECT p.project_number
          FROM prj_project p
         WHERE t1.project_id = p.project_id) AS project_id_c,
       (SELECT p.project_name
          FROM prj_project p
         WHERE t1.project_id = p.project_id) AS project_id_n,
       t1.company_id,
       (SELECT c.company_short_name
          FROM fnd_companies_vl c
         WHERE c.company_id = t1.company_id) AS company_id_n,
       t1.division,
       (SELECT d.description
          FROM hls_division d
         WHERE d.division = t1.division) AS division_n,
       t1.bp_id_tenant,
       (SELECT m.bp_name
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_tenant) AS bp_id_tenant_n,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = t1.bp_id_tenant) bp_id_tenant_name,
       t1.bp_id_agent_level1,
       (SELECT m.bp_name
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level1) AS bp_id_agent_level1_n,
       t1.bp_id_agent_level2,
       (SELECT m.bp_code
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level2) AS bp_id_agent_level2_n,
       t1.bp_id_agent_level3,
       (SELECT m.bp_code
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level3) AS bp_id_agent_level3_n,
       t1.owner_user_id,
       (SELECT a.description
          FROM sys_user a
         WHERE a.user_id = t1.owner_user_id) owner_user_id_n,
       t1.employee_id,
       (SELECT e.name
          FROM exp_employees e
         WHERE e.employee_id = t1.employee_id) AS employee_id_n,
       t1.unit_id,
       (SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = t1.unit_id) AS unit_id_n,
       t1.employee_id_of_manager,
       (SELECT e.name
          FROM exp_employees e
         WHERE e.employee_id = t1.employee_id_of_manager) AS employee_id_of_manager_n,
       t1.description,
       t1.price_list,
       (SELECT l.description
          FROM hls_price_list l
         WHERE l.price_list = t1.price_list) AS price_list_n,
       t1.calc_method,
       t1.inception_of_lease,
       t1.lease_start_date,
       t1.first_pay_date,
       t1.lease_end_date,
       t1.lease_times,
       t1.pay_times,
       t1.int_rate_display,
       t1.annual_pay_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_ANNUAL_PAY_TIMES'
           AND v.code_value = t1.annual_pay_times) AS annual_pay_times_n,
       t1.lease_term,
       t1.pay_type,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_PAY_TYPE'
           AND v.code_value = t1.pay_type) AS pay_type_n,
       t1.currency,
       (SELECT g.currency_name
          FROM gld_currency_vl g
         WHERE g.currency_code = t1.currency) AS currency_n,
       t1.currency_precision,
       t1.machinery_amount,
       t1.parts_amount,
       t1.lease_item_amount,
       t1.lease_item_cost,
       t1.down_payment,
       t1.down_payment_ratio,
       t1.finance_amount,
       t1.net_finance_amount,
       t1.total_interest,
       t1.total_rental,
       t1.total_fee,
       t1.contract_amount,
       t1.tax_type_id,
       (SELECT t.description
          FROM fnd_tax_type_codes t
         WHERE t.tax_type_id = t1.tax_type_id) AS tax_type_id_n,
       t1.vat_flag,
       t1.vat_rate,
       t1.vat_input,
       t1.vat_total_interest,
       t1.vat_finance_amount,
       t1.vat_total_rental,
       t1.vat_total_fee,
       t1.net_total_interest,
       t1.net_total_rental,
       t1.net_total_fee,
       t1.net_lease_item_amount,
       nvl(t1.lease_charge, 0) lease_charge,
       t1.lease_charge_ratio,
       t1.lease_mgt_fee,
       t1.lease_mgt_fee_ratio,
       t1.lease_mgt_fee_rule,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_MGT_FEE_RULE'
           AND v.code_value = t1.lease_mgt_fee_rule) AS lease_mgt_fee_rule_n,
       t1.deposit,
       t1.deposit_ratio,
       nvl(t1.residual_value, 0) residual_value,
       t1.other_fee,
       t1.base_rate,
       t1.int_rate,
       t1.int_rate_implicit,
       t1.int_rate_type,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_INT_RATE_TYPE'
           AND v.code_value = t1.int_rate_type) AS int_rate_type_n,
       t1.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = t1.contract_status) AS contract_status_n,
       t1.created_by,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       t1.data_class,
       (SELECT sc.code_value_name
          FROM sys_code_values_v sc
         WHERE sc.code = 'HLS_DATA_CLASS'
           AND sc.code_value = t1.data_class) data_class_n,
       t1.ccr_penalty,
       nvl(t1.ccr_fee,
           decode((SELECT ccc1.document_type
                    FROM con_contract_change_req ccc1
                   WHERE ccc1.change_req_id = t1.contract_id),
                  'LEASSE_CHAG',
                  '1000',
                  'LEASEHOLD_CHAG',
                  '1000',
                  'CONEXTHAG',
                  '1000',
                  'DEBT_CHAG',
                  '2000',
                  'PART_CHAG',
                  '500',
                  'PAY_CHAG',
                  '0')) ccr_fee,
       --modify by liyuan begin
       (SELECT ccb.score_level
          FROM con_contract_bp ccb
         WHERE t1.contract_id = ccb.contract_id
           AND ccb.bp_category = 'TENANT') score_level,
       (SELECT ccc1.document_type
          FROM con_contract_change_req ccc1
         WHERE ccc1.change_req_id = t1.contract_id) ccr_document_type,
       (SELECT ht.description
          FROM hls_document_type ht
         WHERE ht.document_type =
               (SELECT ccc1.document_type
                  FROM con_contract_change_req ccc1
                 WHERE ccc1.change_req_id = t1.contract_id)) AS ccr_document_type_n, --变更类型
       /*decode((SELECT ccc1.document_type
         FROM con_contract_change_req ccc1
        WHERE ccc1.change_req_id = t1.contract_id),
       'LEASSE_CHAG',
       '1',
       'LEASEHOLD_CHAG',
       '2',
       'CONEXTHAG',
       '3',
       'DEBT_CHAG',
       '4',
       'PART_CHAG',
       '5',
       'PAY_CHAG',
       '6') times,*/
       (SELECT to_date(to_char(cccc.req_date,'yyyy-mm-dd'),'yyyy-mm-dd')
          FROM con_contract_change_req cccc
         WHERE cccc.change_req_id = t1.contract_id) req_date, --变更申请日期
       (SELECT cccc.ref_v01
          FROM con_contract_change_req cccc
         WHERE cccc.change_req_id = t1.contract_id) ref_v01, --变更人
       (SELECT cli.pattern
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') lease_item_id_n, --机型
       (SELECT cli.lease_item_id
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') lease_item_id,
       (SELECT cli.machine_number
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') machine_number, --机号 --end
       t1.ccr_lease_rental,
       t1.ccr_lease_interest,
       t1.ccr_lease_principal,
       t1.ccr_lease_penalty,
       t1.ccr_reason,
       t1.ccr_opinion,
       nvl(t1.hd_user_col_v04, 'DELAY') hd_user_col_v04, --冬雨季延期方式
       t1.ccr_start_times,
       t1.interim_times, --延期期数
       t1.ccr_outstanding_prin_tax_incld, --剩余本金
       t1.ccr_finance_amount,
       t1.interim_rental,
       t1.ccr_outstanding_times,
       t1.ccr_dept,
       t1.btb_payment_date, --变更基准日期
       t1.btb_finance_amount,
       t1.ccr_overdue_rental, --变更逾期租金
       t1.HD_USER_COL_V06, --罚息是否参与债权再调整
       (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code = 'YES_NO'
           and v.code_value = t1.HD_USER_COL_V06) HD_USER_COL_V06_n,
       t1.ccr_overdue_prin, --逾期本金
       t1.CCR_OVERDUE_INTEREST, --变更逾期利息
       t1.CCR_OUTSTANDING_PRIN_TI_TOTAL, --未到期本金
       t1. CCR_DUE_AMOUNT, --变更罚息总金额
       t1. CCR_DUE_RATIO, --变更收取比例
       t1.CCR_DUE_CHARGE, --变更收取罚息金额
       t1.BTB_INTEREST_MARGIN, --债权再调整变更不收本金期数
       t1.bp_character, --承租人性格
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_character
         and v.code='BP_CHARACTER') bp_character_n,
       t1.bp_engineer_source, --工程来源
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_engineer_source
         and v.code='BP_ENGINEER_SOURCE') bp_engineer_source_n,
       t1.bp_credit_conscious, --信用意识
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_credit_conscious
         and v.code='BP_CREDIT_CONSCIOUS') bp_credit_conscious_n,
       t1.bp_income_situation, --资产与收入情况
       t1.bp_engineer_discrip, --工程情况描述
       t1.bp_risk, --有何风险
       t1.bp_evaluate, --整体风险
       t1.fee_waiver, --是否手续费减免
       t1.fee_waiver_reason,--手续费减免说明
       --租金計劃變更 start
           (SELECT c.lease_times FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)TIMES_S,
           (SELECT c.irr FROM con_contract c ,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)IRR_S,
           (SELECT c.int_rate_display FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)RATE_S,
           t1.lease_times TIMES_C,
           t1.irr IRR_C,
           t1.int_rate_display RATE_C,
           --租金計劃變更 end
           --回款賬戶變更 start
           (SELECT v.code_value_name FROM con_contract c,con_contract_change_req cr,sys_code_values_v v WHERE c.contract_id = cr.contract_id
           AND v.code_value = c.pay_method
           AND cr.change_req_id = t1.contract_id)PAYMENT_METHOD_ID_S,	--原支付方式
            (SELECT c.BANK_BRANCH_NAME FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_FULL_NAME_S,	--原扣款银行
           (SELECT c.BANK_ACCOUNT_NAME FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_ACCOUNT_NAME_S,	--原扣款账户名
           (SELECT c.BANK_BRANCH_NUM FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_ACCOUNT_NUM_S,	--原扣款账号
           t1.pay_method,
           (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.pay_method
         and v.code='PAY_METHODS') PAY_METHOD_N,	--现支付方式
           t1.BANK_BRANCH_NAME ,	--现扣款银行
           t1.BANK_ACCOUNT_NAME ,	--现扣款账户名
           t1.BANK_BRANCH_NUM,	--现扣款账号
           t1.ccr_lease_date
           --回款賬戶變更 end
  FROM con_contract t1
;