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' ;