CREATE OR REPLACE VIEW CON_CONTRACT_CCR_LV AS
SELECT t1.contract_id,
       t1.calc_session_id,
       t1.contract_number,
       t1.contract_name,
       t1.business_type,
       (SELECT bt.description
          FROM hls_business_type bt
         WHERE bt.business_type = t1.business_type) AS business_type_n,
       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.spv_company_id,
       (SELECT c.company_short_name
          FROM fnd_companies_vl c
         WHERE c.company_id = t1.spv_company_id) AS spv_company_id_n,
       t1.lease_organization,
       (SELECT o.description
          FROM hls_lease_organization o
         WHERE o.lease_organization = t1.lease_organization) AS lease_organization_n,
       t1.lease_channel,
       (SELECT ch.description
          FROM hls_lease_channel ch
         WHERE ch.lease_channel = t1.lease_channel) AS lease_channel_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_code
          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,
       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 u.description
          FROM exp_org_unit_vl u
         WHERE u.unit_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.factoring_type,
       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.last_pay_date,
       t1.lease_end_date,
       t1.lease_times,
       t1.pay_times,
       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,
       t1.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,
       t1.deposit_deduction,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_DEPOSIT_DEDUCTION'
           AND v.code_value = t1.deposit_deduction) AS deposit_deduction_n,
       t1.residual_value,
       t1.residual_ratio,
       t1.balloon,
       t1.balloon_ratio,
       t1.interim_rent_period,
       t1.interim_times,
       t1.interim_rental,
       t1.insurance_fee,
       t1.insurance_rate,
       t1.commission_payable,
       t1.commission_receivable,
       t1.third_party_deposit,
       t1.promise_to_pay,
       t1.other_fee,
       t1.other_payment,
       t1.rounding_object,
       t1.rounding_method,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_CASHFLOW_ACCURATED'
           AND v.code_value = t1.rounding_method) AS rounding_method_n,
       t1.int_rate_fixing_way,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'TRE502_INT_RATE_FIXING_WAY'
           AND v.code_value = t1.int_rate_fixing_way) AS int_rate_fixing_way_n,
       t1.int_rate_fixing_range,
       t1.int_rate_display,
       t1.base_rate_type,
       (SELECT rt.description
          FROM fnd_base_rate_type rt
         WHERE rt.base_rate_type = t1.base_rate_type) AS base_rate_type_n,
       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.flt_rate_profile,
       (SELECT description
          FROM con_flt_rate_profile p
         WHERE p.flt_rate_profile = t1.flt_rate_profile) AS flt_rate_profile_n,
       t1.flt_rate_adj_method,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_FLT_RATE_ADJ_METHOD'
           AND v.code_value = t1.flt_rate_adj_method) AS flt_rate_adj_method_n,
       t1.flt_simulate_step,
       t1.flt_simulate_range,
       t1.flt_unit_adj_amt,
       t1.flt_execute_times_rule,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS_FLT_EXECUTE_TIMES_RULE'
           AND v.code_value = t1.flt_execute_times_rule) AS flt_execute_times_rule_n,
       t1.flt_int_rate_adj_date,
       t1.flt_delay_execute_period,
       t1.flt_annual_adj_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS_FLT_ANNUAL_ADJ_TIMES'
           AND v.code_value = t1.flt_annual_adj_times) AS flt_annual_adj_times_n,
       flt_next_adj_date,
       t1.int_rate_precision,
       t1.irr,
       t1.irr_after_tax,
       t1.int_rate_implicit_after_tax,
       t1.irr_reserved1,
       t1.irr_reserved2,
       t1.irr_reserved3,
       t1.pmt,
       t1.pmt_first,
       t1.npv_using_cof,
       t1.annual_mean_rate,
       t1.total_salestax,
       t1.biz_day_convention,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS050_BIZ_DAY_CONVENTION'
           AND v.code_value = t1.biz_day_convention) AS biz_day_convention_n,
       t1.calc_with_residual_value,
       t1.exchange_rate_type,
       (SELECT v.rate_type_desc
          FROM gld_exchange_rate_type_v v
         WHERE v.rate_type_code = t1.exchange_rate_type) AS exchange_rate_type_n,
       t1.exchange_rate_quotation,
       t1.exchange_rate,
       t1.penalty_profile,
       t1.grace_period,
       t1.penalty_rate,
       t1.penalty_calc_base,
       t1.penalty_total_base_ratio,
       t1.credit_write_off_order, --?
       t1.fin_income_recognize_method,
       t1.early_termination_profile,
       (SELECT p.description
          FROM con_contract_et_profile p
         WHERE p.et_profile = t1.early_termination_profile
           AND rownum = 1) AS early_termination_profile_n,
       t1.payment_method_id,
       t1.telex_transfer_bank_id,
       t1.tt_bank_branch_name,
       t1.tt_bank_account_num,
       t1.tt_bank_account_name,
       t1.tt_remark,
       t1.direct_debit_bank_id,
       t1.dd_bank_branch_name,
       t1.dd_bank_account_num,
       t1.dd_bank_account_name,
       t1.dd_agreement_no,
       t1.dd_agreement_status,
       t1.dd_remark,
       t1.purchase_order_no,
       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.user_status_1,
       t1.user_status_2,
       t1.user_status_3,
       t1.print_status,
       t1.print_times,
       t1.first_print_date,
       t1.first_print_by,
       t1.delivery_status,
       t1.delivery_date,
       t1.billing_method,
       (SELECT bm.description
          FROM con_billing_method bm
         WHERE bm.billing_method = t1.billing_method) AS billing_method_n,
       t1.billing_status,
       t1.signing_date,
       t1.original_recall_date,
       t1.lease_card_recall_date,
       t1.early_termination_date,
       t1.termination_date,
       t1.assignment_agreement_no,
       t1.assignment_date,
       t1.btb_payment_date,
       t1.btb_int_rate,
       t1.btb_int_rate_implicit,
       t1.btb_finance_amount,
       t1.btb_net_finance_amount,
       t1.btb_total_repayment,
       t1.btb_total_interest,
       t1.btb_total_fee_pv,
       t1.btb_total_fee,
       t1.btb_finance_ratio,
       t1.btb_interest_margin,
       t1.btb_vat_interest,
       t1.btb_vat_fee,
       t1.btb_interest_after_tax,
       t1.btb_fee_after_tax,
       t1.cdd_list_id,
       t1.hd_user_col_d01,
       t1.hd_user_col_d02,
       t1.hd_user_col_d03,
       t1.hd_user_col_d04,
       t1.hd_user_col_d05,
       t1.hd_user_col_v01,
       t1.hd_user_col_v02,
       t1.hd_user_col_v03,
       t1.hd_user_col_v04,
       t1.hd_user_col_v05,
       t1.hd_user_col_v06,
       t1.hd_user_col_v07,
       t1.hd_user_col_v08,
       t1.hd_user_col_v09,
       t1.hd_user_col_v10,
       t1.hd_user_col_n01,
       t1.hd_user_col_n02,
       t1.hd_user_col_n03,
       t1.hd_user_col_n04,
       t1.hd_user_col_n05,
       t1.hd_user_col_n06,
       t1.hd_user_col_n07,
       t1.hd_user_col_n08,
       t1.hd_user_col_n09,
       t1.hd_user_col_n10,
       t1.hd_user_col_n11,
       t1.hd_user_col_n12,
       t1.hd_user_col_n13,
       t1.serial_number,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n13)) AS hd_user_col_n13_n,
       t1.hd_user_col_n14,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n14)) AS hd_user_col_n14_n,
       t1.hd_user_col_n15,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n15)) AS hd_user_col_n15_n,
       overdue_status,
       overdue_max_days,
       five_class_code,
       (SELECT r.description
          FROM rsc_five_class_code r
         WHERE t1.five_class_code = r.five_class_code) AS five_class_code_n,
       main_business_income,
       main_business_cost,
       financing_cost,
       calc_prompt,
       calc_prompt_msg,
       lease_item_price_agent,
       legal_fee,
       version,
       version_date,
       version_display,
       version_external,
       version_reason,
       version_note,
       t1.created_by,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = cr.created_by) change_created_by_n,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       t1.search_term_1,
       t1.search_term_2,
       t1.data_class,
       t1.ccr_start_times,
       t1.ccr_outstanding_times,
       t1.ccr_outstanding_prin_tax_incld,
       t1.ccr_overdue_rental,
       t1.ccr_penalty,
       t1.ccr_fee,
       t1.ccr_finance_amount,
       t1.ccr_pmt,
       t1.ccr_outstanding_prin_ti_total,
       t1.ccr_financing_overdue_rental,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.ccr_financing_overdue_rental)) AS ccr_financing_overdue_rental_n,
       t1.ccr_period_int_rate_implicit,
       t1.ccr_period_lease_start_date,
       t1.ccr_added_principal,
       t1.ccr_nominal_finance_amount,
       t1.ccr_original_finance_amount,
       cr.change_quotation,
       cr.change_lease_item,
       cr.change_bp,
       cr.change_req_number,
       cr.req_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON_CHANGE_REQ_STATUS'
           AND v.code_value = cr.req_status) AS req_status_n,
       cr.req_date,
       cr.description AS ccr_description,
       cr.document_type AS ccr_document_type,
       (SELECT dt.description
          FROM hls_document_type dt
         WHERE dt.document_type = cr.document_type) AS ccr_document_type_n,
       cr.document_category AS ccr_document_category,
       (SELECT dc.description
          FROM hls_document_category dc
         WHERE dc.document_category = cr.document_category) AS ccr_document_category_n,
       cr.business_type AS ccr_business_type,
       (SELECT bt.description
          FROM hls_business_type bt
         WHERE bt.business_type = cr.business_type) AS ccr_business_type_n,
       cr.change_req_id,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = t1.owner_user_id) owner, -- 单据所有者
       t1.depart_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'DEPART_STATUS'
           AND v.code_value = t1.depart_status) depart_status_n, --发车状态
       t1.history_contract_id,
			 (SELECT ccd.comments
          FROM con_contract_case_record ccd
         WHERE ccd.document_table = 'CON_CONTRACT_CHANGE_REQ'
           AND ccd.document_id = cr.change_req_id
           and ccd.status in ('APPROVING','APPROVED','REJECT')) comments, --doodream返回意见
            cr.approved_date,--审批通过时间
					 cr.creation_date create_date--创建时间
  FROM con_contract            t1,
       con_contract_change_req cr
 WHERE t1.data_class = 'CHANGE_REQ'
   AND t1.contract_id = cr.change_req_id
--增加migration数据
UNION ALL
SELECT t1.contract_id,
       t1.calc_session_id,
       t1.contract_number,
       t1.contract_name,
       t1.business_type,
       (SELECT bt.description
          FROM hls_business_type bt
         WHERE bt.business_type = t1.business_type) AS business_type_n,
       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.spv_company_id,
       (SELECT c.company_short_name
          FROM fnd_companies_vl c
         WHERE c.company_id = t1.spv_company_id) AS spv_company_id_n,
       t1.lease_organization,
       (SELECT o.description
          FROM hls_lease_organization o
         WHERE o.lease_organization = t1.lease_organization) AS lease_organization_n,
       t1.lease_channel,
       (SELECT ch.description
          FROM hls_lease_channel ch
         WHERE ch.lease_channel = t1.lease_channel) AS lease_channel_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_code
          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,
       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 u.description
          FROM exp_org_unit_vl u
         WHERE u.unit_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.factoring_type,
       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.last_pay_date,
       t1.lease_end_date,
       t1.lease_times,
       t1.pay_times,
       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,
       t1.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,
       t1.deposit_deduction,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_DEPOSIT_DEDUCTION'
           AND v.code_value = t1.deposit_deduction) AS deposit_deduction_n,
       t1.residual_value,
       t1.residual_ratio,
       t1.balloon,
       t1.balloon_ratio,
       t1.interim_rent_period,
       t1.interim_times,
       t1.interim_rental,
       t1.insurance_fee,
       t1.insurance_rate,
       t1.commission_payable,
       t1.commission_receivable,
       t1.third_party_deposit,
       t1.promise_to_pay,
       t1.other_fee,
       t1.other_payment,
       t1.rounding_object,
       t1.rounding_method,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_CASHFLOW_ACCURATED'
           AND v.code_value = t1.rounding_method) AS rounding_method_n,
       t1.int_rate_fixing_way,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'TRE502_INT_RATE_FIXING_WAY'
           AND v.code_value = t1.int_rate_fixing_way) AS int_rate_fixing_way_n,
       t1.int_rate_fixing_range,
       t1.int_rate_display,
       t1.base_rate_type,
       (SELECT rt.description
          FROM fnd_base_rate_type rt
         WHERE rt.base_rate_type = t1.base_rate_type) AS base_rate_type_n,
       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.flt_rate_profile,
       (SELECT description
          FROM con_flt_rate_profile p
         WHERE p.flt_rate_profile = t1.flt_rate_profile) AS flt_rate_profile_n,
       t1.flt_rate_adj_method,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_FLT_RATE_ADJ_METHOD'
           AND v.code_value = t1.flt_rate_adj_method) AS flt_rate_adj_method_n,
       t1.flt_simulate_step,
       t1.flt_simulate_range,
       t1.flt_unit_adj_amt,
       t1.flt_execute_times_rule,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS_FLT_EXECUTE_TIMES_RULE'
           AND v.code_value = t1.flt_execute_times_rule) AS flt_execute_times_rule_n,
       t1.flt_int_rate_adj_date,
       t1.flt_delay_execute_period,
       t1.flt_annual_adj_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS_FLT_ANNUAL_ADJ_TIMES'
           AND v.code_value = t1.flt_annual_adj_times) AS flt_annual_adj_times_n,
       flt_next_adj_date,
       t1.int_rate_precision,
       t1.irr,
       t1.irr_after_tax,
       t1.int_rate_implicit_after_tax,
       t1.irr_reserved1,
       t1.irr_reserved2,
       t1.irr_reserved3,
       t1.pmt,
       t1.pmt_first,
       t1.npv_using_cof,
       t1.annual_mean_rate,
       t1.total_salestax,
       t1.biz_day_convention,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS050_BIZ_DAY_CONVENTION'
           AND v.code_value = t1.biz_day_convention) AS biz_day_convention_n,
       t1.calc_with_residual_value,
       t1.exchange_rate_type,
       (SELECT v.rate_type_desc
          FROM gld_exchange_rate_type_v v
         WHERE v.rate_type_code = t1.exchange_rate_type) AS exchange_rate_type_n,
       t1.exchange_rate_quotation,
       t1.exchange_rate,
       t1.penalty_profile,
       t1.grace_period,
       t1.penalty_rate,
       t1.penalty_calc_base,
       t1.penalty_total_base_ratio,
       t1.credit_write_off_order, --?
       t1.fin_income_recognize_method,
       t1.early_termination_profile,
       (SELECT p.description
          FROM con_contract_et_profile p
         WHERE p.et_profile = t1.early_termination_profile
           AND rownum = 1) AS early_termination_profile_n,
       t1.payment_method_id,
       t1.telex_transfer_bank_id,
       t1.tt_bank_branch_name,
       t1.tt_bank_account_num,
       t1.tt_bank_account_name,
       t1.tt_remark,
       t1.direct_debit_bank_id,
       t1.dd_bank_branch_name,
       t1.dd_bank_account_num,
       t1.dd_bank_account_name,
       t1.dd_agreement_no,
       t1.dd_agreement_status,
       t1.dd_remark,
       t1.purchase_order_no,
       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.user_status_1,
       t1.user_status_2,
       t1.user_status_3,
       t1.print_status,
       t1.print_times,
       t1.first_print_date,
       t1.first_print_by,
       t1.delivery_status,
       t1.delivery_date,
       t1.billing_method,
       (SELECT bm.description
          FROM con_billing_method bm
         WHERE bm.billing_method = t1.billing_method) AS billing_method_n,
       t1.billing_status,
       t1.signing_date,
       t1.original_recall_date,
       t1.lease_card_recall_date,
       t1.early_termination_date,
       t1.termination_date,
       t1.assignment_agreement_no,
       t1.assignment_date,
       t1.btb_payment_date,
       t1.btb_int_rate,
       t1.btb_int_rate_implicit,
       t1.btb_finance_amount,
       t1.btb_net_finance_amount,
       t1.btb_total_repayment,
       t1.btb_total_interest,
       t1.btb_total_fee_pv,
       t1.btb_total_fee,
       t1.btb_finance_ratio,
       t1.btb_interest_margin,
       t1.btb_vat_interest,
       t1.btb_vat_fee,
       t1.btb_interest_after_tax,
       t1.btb_fee_after_tax,
       t1.cdd_list_id,
       t1.hd_user_col_d01,
       t1.hd_user_col_d02,
       t1.hd_user_col_d03,
       t1.hd_user_col_d04,
       t1.hd_user_col_d05,
       t1.hd_user_col_v01,
       t1.hd_user_col_v02,
       t1.hd_user_col_v03,
       t1.hd_user_col_v04,
       t1.hd_user_col_v05,
       t1.hd_user_col_v06,
       t1.hd_user_col_v07,
       t1.hd_user_col_v08,
       t1.hd_user_col_v09,
       t1.hd_user_col_v10,
       t1.hd_user_col_n01,
       t1.hd_user_col_n02,
       t1.hd_user_col_n03,
       t1.hd_user_col_n04,
       t1.hd_user_col_n05,
       t1.hd_user_col_n06,
       t1.hd_user_col_n07,
       t1.hd_user_col_n08,
       t1.hd_user_col_n09,
       t1.hd_user_col_n10,
       t1.hd_user_col_n11,
       t1.hd_user_col_n12,
       t1.hd_user_col_n13,
       t1.serial_number,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n13)) AS hd_user_col_n13_n,
       t1.hd_user_col_n14,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n14)) AS hd_user_col_n14_n,
       t1.hd_user_col_n15,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.hd_user_col_n15)) AS hd_user_col_n15_n,
       overdue_status,
       overdue_max_days,
       five_class_code,
       (SELECT r.description
          FROM rsc_five_class_code r
         WHERE t1.five_class_code = r.five_class_code) AS five_class_code_n,
       main_business_income,
       main_business_cost,
       financing_cost,
       calc_prompt,
       calc_prompt_msg,
       lease_item_price_agent,
       legal_fee,
       version,
       version_date,
       version_display,
       version_external,
       version_reason,
       version_note,
       t1.created_by,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = cr.created_by) change_created_by_n,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       t1.search_term_1,
       t1.search_term_2,
       'CHANGE_REQ' data_class,
       t1.ccr_start_times,
       t1.ccr_outstanding_times,
       t1.ccr_outstanding_prin_tax_incld,
       t1.ccr_overdue_rental,
       t1.ccr_penalty,
       t1.ccr_fee,
       t1.ccr_finance_amount,
       t1.ccr_pmt,
       t1.ccr_outstanding_prin_ti_total,
       t1.ccr_financing_overdue_rental,
       (SELECT t.code_value_name
          FROM sys_code_values_v t
         WHERE t.code = 'HLS050_ZERO_OR_ONE'
           AND t.code_value = to_char(t1.ccr_financing_overdue_rental)) AS ccr_financing_overdue_rental_n,
       t1.ccr_period_int_rate_implicit,
       t1.ccr_period_lease_start_date,
       t1.ccr_added_principal,
       t1.ccr_nominal_finance_amount,
       t1.ccr_original_finance_amount,
       cr.change_quotation,
       cr.change_lease_item,
       cr.change_bp,
       cr.change_req_number,
       cr.req_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON_CHANGE_REQ_STATUS'
           AND v.code_value = cr.req_status) AS req_status_n,
       cr.req_date,
       cr.description AS ccr_description,
       cr.document_type AS ccr_document_type,
       (SELECT dt.description
          FROM hls_document_type dt
         WHERE dt.document_type = cr.document_type) AS ccr_document_type_n,
       cr.document_category AS ccr_document_category,
       (SELECT dc.description
          FROM hls_document_category dc
         WHERE dc.document_category = cr.document_category) AS ccr_document_category_n,
       cr.business_type AS ccr_business_type,
       (SELECT bt.description
          FROM hls_business_type bt
         WHERE bt.business_type = cr.business_type) AS ccr_business_type_n,
       cr.change_req_id,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = t1.owner_user_id) owner, -- 单据所有者
       t1.depart_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'DEPART_STATUS'
           AND v.code_value = t1.depart_status) depart_status_n, --发车状态
       t1.history_contract_id,
       (SELECT ccd.comments
          FROM con_contract_case_record ccd
         WHERE ccd.document_table = 'CON_CONTRACT_CHANGE_REQ'
           AND ccd.document_id = cr.change_req_id
           and ccd.status in ('APPROVING','APPROVED','REJECT')) comments, --doodream返回意见
            cr.approved_date,--审批通过时间
					 cr.creation_date create_date--创建时间
  FROM con_contract            t1,
       con_contract_change_req cr
 WHERE t1.contract_id = cr.change_req_id
   AND (cr.cashflow_change_flag='Y' OR cr.extension_change_flag = 'Y' OR
   cr.RESCHEDULE_FLAG='Y' OR cr.BANK_ACC_CHANGE_FLAG= 'Y')
;