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