CREATE OR REPLACE VIEW CON_CONTRACT_REPO_MODIFY_LV AS
SELECT t1.contract_id,
       t1.data_class,
       t1.calc_session_id,
       t1.project_id,
       t1.business_type,
       t1.division,
       t1.lease_organization,
       t1.lease_channel,
       t1.document_type,
       t1.repurchase_contract_number,
       t1.contract_number,
       t1.contract_name,
       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,
         t1.bp_id_agent_level1,
       (SELECT hbm.bp_name
          FROM hls_bp_master hbm
         WHERE hbm.enabled_flag = 'Y'
           AND hbm.bp_category = 'AGENT'
           AND hbm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n, --代理店
           (SELECT d.description
          FROM hls_division d
         WHERE d.division = t1.division) AS division_n,
          t1.employee_id,
       (SELECT e.name
          FROM exp_employees e
         WHERE e.employee_id = t1.employee_id) AS employee_id_n,
         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,
                  cr.created_by,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = cr.created_by) change_created_by_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.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,
         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.repo_change_flag
  FROM con_contract            t1,
       con_contract_change_req cr
 WHERE t1.contract_id = cr.change_req_id
 AND t1.data_class = 'CHANGE_REQ'
 AND cr.repo_change_flag = 'Y'
;