CREATE OR REPLACE VIEW CON_CONTRACT_LV AS
SELECT t1.contract_id,
          t1.calc_session_id,
          t1.contract_number,
          t1.contract_name,
          t1.business_type,
          t1.district,
          (SELECT ROUND (SUM (u.overdue_max_days) / COUNT (*), 2)
             FROM con_contract_cashflow u
            WHERE u.contract_id = t1.contract_id --AND u.write_off_flag = 'FULL'
                                                AND u.overdue_status = 'Y')
             dueday_avg,
          (SELECT SUM (ccc.overdue_amount)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.write_off_flag != 'FULL'
                  AND ccc.overdue_status = 'Y'
                  AND ccc.cf_item = 1
                  AND ccc.cf_direction != 'NONCASH')
             sum_due_amount,
          (SELECT (SUM (ccc.due_amount) - SUM (ccc.received_amount))
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.write_off_flag != 'FULL'
                  AND ccc.cf_item = 9
                  AND ccc.cf_direction != 'NONCASH')
             sum_fx_amount,
          t1.departing_date,
          t1.departed_date,
          t1.depart_status,
          --li.item_frame_number,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DEPART_STATUS'
                  AND v.code_value = t1.depart_status)
             AS depart_status_n,
          (SELECT h1.code_value_name
             FROM sys_code_values_v h1
            WHERE     h1.code = 'PRJ500N_DISTRICT_AREAS'
                  AND t1.district = h1.code_value)
             district_n,
          (SELECT bt.description
             FROM hls_business_type bt
            WHERE bt.business_type = t1.business_type)
             AS business_type_n,
          t1.document_type,
          (SELECT p.document_type
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS prj_document_type,
          (SELECT p.special_permit_flag
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS special_permit_flag,
          (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_code
             FROM fnd_companies_vl c
            WHERE c.company_id = t1.spv_company_id)
             AS spv_company_code,
          (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 p.lease_channel
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS prj_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_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.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_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,
          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,
          --li.deposit_deduction,
          --(SELECT v.code_value_name
          -- FROM sys_code_values_v v
          --  WHERE v.code = 'HLS500_DEPOSIT_DEDUCTION'
          --  AND v.code_value = li.deposit_deduction) AS deposit_deduction_n,
          NVL (t1.residual_value, 0) 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_fee3,
          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 < 2)
             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,
          NVL2 ( (SELECT t.bank_short_name value_name
                    FROM csh_bank t
                   WHERE t.bank_id = t1.direct_debit_bank_id),
                (SELECT t.bank_short_name value_name
                   FROM csh_bank t
                  WHERE t.bank_id = t1.direct_debit_bank_id),
                (SELECT t.ebank_name
                   FROM csh_ebank t
                  WHERE t.ebank_id = t1.direct_debit_bank_id))
             direct_debit_bank_id_n,
          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.hd_user_col_n14,
          t1.hd_user_col_n15,
          t1.serial_number,
          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,
          t1.creation_date,
          t1.last_updated_by,
          t1.last_update_date,
          t1.search_term_1,
          t1.search_term_2,
          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_start_times,
          t1.ccr_outstanding_times,
          t1.ccr_outstanding_prin_tax_incld,
          t1.ccr_overdue_rental,
          t1.ccr_penalty,
          NVL (t1.ccr_fee, 1000) ccr_fee,
          t1.ccr_finance_amount,
          t1.ccr_pmt,
          t1.ccr_outstanding_prin_ti_total,
          t1.ccr_financing_overdue_rental,
          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,
          t1.con_maintain_flag,
          t1.license_provider_id,
          -- li.license_number,
          (SELECT m.bp_name
             FROM hls_bp_master m
            WHERE m.bp_id = t1.license_provider_id)
             license_provider_id_n,
          (SELECT a.project_number
             FROM prj_project a
            WHERE a.project_id = t1.project_id)
             project_number,
          (SELECT a.document_type
             FROM prj_project a
            WHERE a.project_id = t1.project_id)
             project_document_type,
          (SELECT ccr.document_type
             FROM con_contract_change_req ccr
            WHERE ccr.change_req_id = t1.contract_id)
             change_document_type,
          (SELECT vv.code_value_name AS value_name
             FROM sys_code_values_v vv
            WHERE     vv.code = 'HLS_052_FINANCIAL_SCALE'
                  AND vv.code_value = t1.hd_user_col_v01)
             hd_user_col_v01_n,
          (SELECT bm.bp_class
             FROM hls_bp_master bm
            WHERE bm.bp_id = t1.bp_id_tenant)
             bp_class,
          (SELECT bm.bp_class_n
             FROM hls_bp_master_lv bm
            WHERE bm.bp_id = t1.bp_id_tenant)
             bp_class_n,
          t1.cancel_reason,
          t1.bp_id_vender,
          (SELECT bp_name
             FROM hls_bp_master
            WHERE bp_id = t1.bp_id_vender)
             bp_id_vender_n,
          NULL bp_name_sec_tenant,
          t1.invoice_price,
          t1.gps_number,
          t1.ebank_account_prop,                                     -- 代扣账户属性
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EBANK_BANK_PROP'
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y'
                  AND t1.ebank_account_prop = v.code_value)
             ebank_account_prop_n,
          t1.ccr_outstanding_rental,
          t1.ccr_overdue_prin,
          t1.ccr_overdue_interest,
          (SELECT TO_CHAR (MAX (ccc.due_date), 'dd')
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_item = '1'
                  AND ccc.cf_type = '1')
             AS pay_date,
          (SELECT MAX (ccc.due_date)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_item = '1'
                  AND ccc.cf_type = '1')
             AS last_pay_date,
          /*(SELECT sc.code_value_name
           FROM sys_code_values_v sc
          WHERE sc.code = 'PAY_METHOD'
            AND sc.code_value = (SELECT
                                   FROM hls_product_plan_definition hp,
                                        con_contract_lease_item     ccl
                                  WHERE hp.product_plan_id = ccl.product_plan_id
                                    AND ccl.contract_id = t1.contract_id)) pay_method, -- 方式*/

          t1.car_in_date,                                              --车辆入库日
          t1.car_type,                                                 -- 车辆类型
          (SELECT v.code_value_name value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'CAR_TYPE'
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y'
                  AND t1.car_type = v.code_value)
             car_type_n,
          t1.reg_status,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'REG_STATUS' AND t1.reg_status = v.code_value)
             reg_status_n,
          (SELECT DECODE (COUNT (*), 0, 'N', 'Y')
             FROM con_contract_cashflow f
            WHERE f.contract_id = t1.contract_id AND f.overdue_status = 'Y')
             overdue_status_et,
          (SELECT COUNT (*)
             FROM con_contract_cashflow h
            WHERE     h.contract_id = t1.contract_id
                  AND h.write_off_flag = 'FULL'
                  AND h.cf_item = 1)
             received_times,
          NULL version_number,
          (SELECT su.description
             FROM sys_user su
            WHERE su.user_id = t1.owner_user_id)
             owner,                                                   -- 单据所有者
          -- t1.cancel_reason, --合同取消原因
          t1.closed_date,                                            -- 合同取消日期
          t1.derate_amount_total,                                     -- 减免总金额
          t1.bp_id_agent_level1 bp_agent_id,
          (SELECT bm.bp_name
             FROM hls_bp_master bm
            WHERE bm.bp_id = t1.bp_id_agent_level1)
             bp_agent_id_n,
          t1.withhold_way,                                              --代扣方式
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'WITHHOLD_WAYS' AND t1.withhold_way = v.code_value)
             withhold_way_n,
          t1.ebank_id,
          (SELECT b.ebank_name
             FROM csh_ebank b
            WHERE b.ebank_id = t1.ebank_id)
             ebank_id_n,
          t1.ebank_type,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EBANK_TYPE'
                  AND v.code_value = t1.ebank_type
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y')
             ebank_type_n,
          /*(select v.code_value_name
           from sys_code_values_v v
          where v.code = 'ACCOUNT_PROP'
            and v.code_value = t1.ebank_account_prop
            and v.code_enabled_flag = 'Y'
            and v.code_value_enabled_flag = 'Y') ebank_account_prop_n,*/
          t1.ebank_feeno,
          t1.ebank_feeno ebank_feeno_n,
          t1.ebank_account_name,
          t1.ebank_account_num,
          t1.ebank_flag,
          t1.ebank_province,
          t1.emer_contacts,                                            --紧急联系人
          t1.emer_phone,                                              --紧急联系电话
          (SELECT fp.province_code
             FROM fnd_province fp
            WHERE fp.province_id = t1.ebank_province)
             ebank_province_d,
          (SELECT fp.description
             FROM fnd_province fp
            WHERE fp.province_id = t1.ebank_province)
             ebank_province_n,
          t1.ebank_city,
          (SELECT fc.description
             FROM fnd_city fc
            WHERE fc.city_id = t1.ebank_city)
             ebank_city_n,
          (SELECT fc.city_code
             FROM fnd_city fc
            WHERE fc.city_id = t1.ebank_city)
             ebank_city_d,
          (SELECT pp.approved_date
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             approved_date,                                        -- 项目创建合同时间
          (SELECT hp.product_name_write
             FROM con_contract_lease_item ccl, hls_product_plan_definition hp
            WHERE     ccl.contract_id = t1.contract_id
                  AND ccl.product_plan_id = hp.product_plan_id)
             product_name_write,                                        --产品方案
          (SELECT ccb.bp_name
             FROM con_contract_bp ccb
            WHERE     t1.contract_id = ccb.contract_id
                  AND ccb.bp_category = 'TENANT')
             bp_name,
          (SELECT a.bp_name_sp
             FROM con_contract_bp a
            WHERE a.contract_id = t1.contract_id AND a.bp_category = 'TENANT')
             bp_name_sp,
          --(select pi.contact_person from prj_project_bp_contact_info pi,prj_project_bp ppb where pi.prj_bp_id = ppb.prj_bp_id and ppb.project_id = t1.project_id)contact_person,
          --(select pi.contact_person_1 from prj_project_bp_contact_info pi,prj_project_bp ppb where pi.prj_bp_id = ppb.prj_bp_id and ppb.project_id = t1.project_id)contact_person_1,
          NULL due_times,
          NULL his_due_times,
          NULL sy_amount,
          --end
          t1.note,
          (SELECT ccb.record_id
             FROM con_contract_bp ccb
            WHERE     t1.contract_id = ccb.contract_id
                  AND ccb.bp_category = 'TENANT')
             content_bp_pk,
          NULL overdue_status_type,
          (SELECT DISTINCT tt.code_value_name
             FROM con_collection t, sys_code_values_v tt
            WHERE     t.contract_id = t1.contract_id
                  AND tt.code_value = t.overdue_status_type
                  AND tt.code = 'OVERDUE_STATUS_TYPE'
                  AND ROWNUM = 1)
             overdue_status_type_n,
          -- li.gps_amount,
          t1.col_law_reason,
          -- li.gps_flag,
          --(select c.code_value_name  from sys_code_values_v c
          -- where c.code = 'YES_NO' and c.code_value = li.gps_flag) gps_flag_n,
          (SELECT NVL (lm.id_card_no, lm.business_license_num)
             FROM hls_bp_master lm
            WHERE lm.bp_id = t1.bp_id_tenant)
             lessee_id,
          -- (select hp.product_name_write from hls_product_plan_definition hp where hp.product_plan_id = li.product_plan_id)PRODUCT_PLAN_ID_n, -- 应ZW要求修改产品名称
          -- li.product_plan_id,
          -- li.DISCOUNT_RATE,
          -- li.DISCOUNT,
          -- li.Gps_Install_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.gps_install_flag) gps_install_flag_n,
          -- li.Visit_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.VISIT_FLAG) VISIT_FLAG_n,
          --li.Purchase_Tax_Flag,
          -- (SELECT v.code_value_name AS value_name
          -- FROM sys_code_values_v v
          -- WHERE v.code = 'YES_NO'
          -- AND v.code_value = li.PURCHASE_TAX_FLAG) PURCHASE_TAX_FLAG_N,
          -- li.Purchase_Tax,
          --li.License_Fee_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.LICENSE_FEE_FLAG) LICENSE_FEE_FLAG_n,
          -- li.Plate_Price,
          --li.Insurance_Flag,
          --(SELECT v.code_value_name AS value_name
          -- FROM sys_code_values_v v
          -- WHERE v.code = 'YES_NO'
          -- AND v.code_value = li.INSURANCE_FLAG) INSURANCE_FLAG_N,
          -- li.Insurance_Amount,
          -- li.Insurance_Purchase,
          --(select scv.code_value_name
          -- from sys_code_values_v scv where scv.code = 'INSURANCE_PURCHASE' and scv.code_value = li.insurance_purchase) insurance_purchase_n,
          -- li.max_discount,
          t1.rebate,
          t1.wfl_instance_id,
          TO_CHAR ( (SELECT zi.creation_date
                       FROM zj_wfl_workflow_instance zi
                      WHERE zi.instance_id = t1.wfl_instance_id),
                   'yyyy-mm-dd hh24:mi:ss')
             submit_date_detail,
          (SELECT pp.fin_manager
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             fin_manager,
          (SELECT ee.name
             FROM exp_employees ee
            WHERE ee.employee_id = (SELECT pp.fin_manager
                                      FROM prj_project pp
                                     WHERE pp.project_id = t1.project_id))
             fin_manager_n,
          NVL (t1.archive_status, '10') archive_status,
          NVL (
             (SELECT v.code_value_name AS value_name
                FROM sys_code_values_v v
               WHERE     v.code = 'ARCHIVE_STATUS'
                     AND v.code_value = t1.archive_status),
             '未归档')
             archive_status_n,
          t1.transfer_flag,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'YES_NO' AND v.code_value = t1.transfer_flag)
             transfer_flag_n,
          t1.notarial_fee_payable,
          (SELECT p1.product_id
             FROM prj_quotation p1
            WHERE     p1.document_category = 'PROJECT'
                  AND p1.document_id = t1.project_id)
             product_id,
          (SELECT SUM (cf.principal) - SUM (NVL (cf.received_principal, 0))
             FROM con_contract_cashflow cf
            WHERE     cf.contract_id = t1.contract_id
                  AND cf.cf_item = 1
                  AND cf.cf_direction = 'INFLOW')
             outstanding_principal,
          NVL (t1.et_ins_derate_amount, 0) et_ins_derate_amount,
          NVL (t1.et_amount, 0) et_amount,
          NVL (t1.et_total_amt, 0) et_total_amt,
          t1.et_date,
          t1.bisiness_division,
          --add by chenlingfeng
          t1.product_name,
          t1.product_type,
          t1.approve_note,
          t1.direct_lease_charge,
          t1.cus_charge_allocate_flag,
          t1.insurance_pck_flag,
          t1.doc_received_date,
          t1.sign_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'SIGNING_MODE_DS'
                  AND v.code_value = t1.sign_method)
             sign_method_n,
          (SELECT hbm.large_area
             FROM hls_bp_master hbm
            WHERE hbm.bp_id = t1.bp_id_tenant)
             large_area,
          t1.submit_date,
          (SELECT pp.submit_date
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             prj_submit_date,
          t1.sales_method,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_SALES_METHOD'
                  AND t1.sales_method = v.code_value)
             sales_method_n,
          t1.sales_method_note,
          t1.nature_of_work,
          t1.engineering_area,
          t1.actual_user,
          t1.outsider_work_flag,
          t1.work_income,
          t1.work_term,
          t1.work_settlement_cycle,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'WORK_SETTLEMENT_CYCEL'
                  AND t1.work_settlement_method = v.code_value)
             work_settlement_cycle_n,
          t1.work_settlement_method,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'WORK_SETTLEMENT_METHOD'
                  AND t1.work_settlement_method = v.code_value)
             work_settlement_method_n,
          t1.signing_people,
          t1.signing_note,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EXECUTION_STATUS_DS'
                  AND t1.execution_status = v.code_value)
             execution_status_n,
          t1.execution_status,
          t1.insurance_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'INSURANCE_METHOD'
                  AND v.code_value = t1.insurance_method)
             insurance_method_n,
          NVL (t1.lease_execution_date, TRUNC (SYSDATE)) lease_execution_date,
          --end
          t1.product_code,
          (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 t1.description
             FROM hls_document_type t1
            WHERE t1.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,
          (SELECT cccc.req_date
             FROM con_contract_change_req cccc
            WHERE cccc.change_req_id = t1.contract_id)
             req_status,
          (SELECT cccc.ref_v01
             FROM con_contract_change_req cccc
            WHERE cccc.change_req_id = t1.contract_id)
             ref_v01,                                                    --end
          t1.con_solution,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'CON_SOLUTION' AND v.code_value = t1.con_solution)
             con_solution_n,
          t1.terms_of_payment,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'TERMS_OF_PAYMENT'
                  AND v.code_value = t1.terms_of_payment)
             terms_of_payment_n,                                   -------支付方式
          t1.arbitration,
          t1.warranty_standared,
          DECODE (t1.division,
                  '00', '工程机械(中国)有限公司',
                  '10', '工程机械(中国)有限公司',
                  '01', '工程机械(山东)有限公司',
                  '')
             warranty_standared_n,
          t1.warranty_year,
          t1.warranty_hour,
          t1.sub_price_list,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'PRICE_LIST_NAME'
                  AND v.code_value = t1.sub_price_list)
             sub_price_list_n,
          t1.project_plan,
          t1.unit_price,
          t1.quantities,
          t1.out_engineer_or_not,
          (SELECT v.code_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.out_engineer_or_not)
             out_engineer_or_not_n,
          t1.signing_city,
          t1.signing_district,
          (SELECT c.description
             FROM fnd_city c
            WHERE c.city_id = t1.signing_city)
             AS signing_city_n,
          (SELECT d.description
             FROM fnd_district d
            WHERE d.district_id = t1.signing_district)
             AS signing_district_n,
          --add by chenlingfeng
          t1.con_deposit_by_agent,
          NVL (t1.billing_way, 20) billing_way,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_INVOICE_CATEGORY'
                  AND v.code_value = NVL (t1.billing_way, 20))
             billing_way_n,
          NVL (t1.billing_frequency, 10) billing_frequency,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_INVOICE_FREQUENCY'
                  AND v.code_value = NVL (t1.billing_frequency, 10))
             billing_frequency_n,
          t1.first_delay_flag,
          --add by liuhaojie
          t1.information_completed,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'YES_NO'
                  AND v.code_value = t1.information_completed)
             information_completed_n,
          --end
          --add by liyuan
          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,                                      --手续费减免说明
          t1.UPLOAD_DATE,
          --end
          t1.lease_term_month,                                       --租赁期限(月)
          t1.content_type,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'CON1010_CONTENT_TYPE'
                  AND v.code_value = t1.content_type)
             content_type_n,
          (SELECT item_type
             FROM con_contract_lease_item
            WHERE contract_id = t1.contract_id AND equipment_type = 'MAIN')
             item_type,
          (SELECT v.code_value_name
             FROM con_contract_lease_item c, sys_code_values_v v
            WHERE     c.item_type = v.code_value
                  AND v.code = 'DS_ITEM_TYPE'
                  AND contract_id = t1.contract_id
                  AND equipment_type = 'MAIN')
             item_type_n,
          t1.area_distinguish,
          t1.direct_lease_charge_flag,
          t1.loss_sharing_mark,
          t1.loss_sharing_reason,
          (SELECT code_value_name
             FROM sys_code_values_v v
            WHERE     code = 'LOSS_SHARE_REASON'
                  AND v.code_value = t1.loss_sharing_reason)
             loss_sharing_reason_n,
          t1.loss_sharing_notes,
          (SELECT LISTAGG (ct.description, '、')
                     WITHIN GROUP (ORDER BY ct.cf_item)
             FROM hls_cashflow_item ct
            WHERE ct.cf_item IN (SELECT DISTINCT (cc.cf_item)
                                   FROM con_contract_cashflow cc
                                  WHERE     cc.contract_id = t1.contract_id
                                        AND cc.times = 0
                                        AND cc.write_off_flag <> 'FULL'
                                        AND cc.cf_direction = 'INFLOW'))
             cf_count,
          t1.sub_price_list_name,                                      --商品名称2
          NVL (t1.simple_contract, 'N') simple_contract,
          t1.sub_price_list_code,
          (SELECT m.extra_nam
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_agent_level1)
             agent_extra_nam,
          DECODE ( (SELECT bp.direct_sales_agent_flag
                      FROM hls_bp_master bp
                     WHERE bp.bp_id = t1.bp_id_agent_level1),
                  'Y', t1.lease_mgt_fee,
                  'N', 0,
                  0)
             direct_agent_fee,                                     ---直营代理商手续费
          DECODE ( (SELECT bp.direct_sales_agent_flag
                      FROM hls_bp_master bp
                     WHERE bp.bp_id = t1.bp_id_agent_level1),
                  'N', t1.lease_mgt_fee,
                  NULL, t1.lease_mgt_fee,
                  0)
             agent_fee,                                            --普通的代理商手续费
          t1.contract_sign_date,
          t1.CONTRACT_INCEPTION_DATE,                                 ---合同交付日
          (SELECT m.customer_id
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_tenant)
             tenant_customer_id,
          (SELECT m.customer_id
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_agent_level1)
             agent_customer_id,
          t1.special_policy,                                            --特殊政策
          t1.AS_ITEM_SOURCE,                                          --AS物件来源
          t1.DOWN_PAYMENT_STATUS,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DOWN_PAYMENT_STATUS'
                  AND v.code_value = t1.DOWN_PAYMENT_STATUS)
             DOWN_PAYMENT_STATUS_n,
          (SELECT NVL (SUM (NVL (ccc.received_amount, 0)), 0)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_direction = 'INFLOW'
                  AND ccc.cf_status = 'RELEASE'
                  and  ccc.cf_item not in (301,508))
             total_received_amount,
             t1.joint_lease_code,
             t1.joint_lease_cus_code,
             (select bp.bp_name from
             hls_bp_master bp where bp.bp_id= t1.joint_lease_cus_code)joint_lease_cus_code_n,
             t1.joint_lease_code joint_lease_code_n,
             t1.data_miss_flag,
             t1.data_miss_reason,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 2) write_off_cf_2,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 51) write_off_cf_51,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 508) write_off_cf_508,
              (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 3) write_off_cf_3,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 301) write_off_cf_301,
            /* (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 1 and cwo.times
             =(select min(ccc.times)
                          from con_contract_cashflow ccc
                         where ccc.contract_id = t1.contract_id
                           and ccc.cf_status = 'RELEASE'
                           and ccc.cf_direction = 'INFLOW'
                           and ccc.cf_item = 1)) write_off_first_cf_1*/
                (select sum(ccc.due_amount) from con_contract_cashflow
             ccc where ccc.contract_id = t1.contract_id
             AND ccc.cf_direction = 'INFLOW'
              AND ccc.cf_status = 'RELEASE'
             and  ccc.times=1 and ccc.cf_item in(1,102,103,104)
             ) write_off_first_cf_1,--首期租金
             --回购字段 start
             t1.REPURCHASE_CONTRACT_NUMBER,
             t1.REPURCHASE_TYPES,
             (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'REPURCHASE_TYPES'
                  AND v.code_value = t1.REPURCHASE_TYPES) AS REPURCHASE_TYPES_n,
                  t1.OBJECT_HANDINGO,
                  t1.RECOVERY_MATHOD_GOL,
                  t1.SUBJECT_MATTER_RESCISSIO,
                  t1.last_rent_due_date,
                  t1.repurchase_date,
                  t1.paymengt_deadlinedate,
                  t1.depoist_remaining_amount,
                  t1.repurchase_tatal_amount,
                  t1.repurchase_count_amount,
                  t1.repurchase_service_charges,
                  t1.fund_possession_cost,
                  t1.repurchase_interest_rate,
                  t1.depoist_offset_flag,
                  (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'YES_OR_NO'
                  AND v.code_value = t1.depoist_offset_flag) AS depoist_offset_flag_n,
                  t1.payment_frequency,
                  t1.payment_period
                  --回购字段 end
     FROM con_contract t1
     WHERE t1.data_class='NORMAL'
;