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