create view vi_the_rent_plan as (
SELECT LCI.CONTRACT_NUMBER                                     AS CONTRACT_NUMBER,
       LRP.PAYMENT_NUMBER                                      AS PAYMENT_NUMBER,
       LUL.CUSTOMER_NAME                                       AS CUSTOMER_NAME,
       CP.MOBILE                                               AS MOBILE,
       LCI.PROJECT_MANAGE                                      AS PROJECT_MANAGE,
       UI.USERNAME                                             AS PROJECT_MANAGE_NAME,
       LRP.PLAN_LIST                                           AS PLAN_LIST,
       LRP.PLAN_DATE                                           AS PLAN_DATE,
       LRP.RENT                                                AS RENT,
       LRP.CORPUS                                              AS CORPUS,
       LRP.INTEREST                                            AS INTEREST,
       LRP.ALL_REMAIN_CORPUS                                   AS ALL_REMAIN_CORPUS,
       LRP.COLLECT_STATUS                                      AS COLLECT_STATUS,
       LRP.COLLECT_MSG                                         AS COLLECT_MSG,
       LRP.BATCH_NO                                            AS BATCH_NO,
       LRP.YEAR_RATE                                           AS YEAR_RATE,
       (CASE
            WHEN ((LRP.BATCH_STATUS = 'complete') OR (LRP.COLLECT_STATUS = '提前结清') OR (LRP.COLLECT_STATUS = '手工收款') OR
                  (LRP.COLLECT_STATUS = '提前结清') OR (LRP.COLLECT_STATUS = '网银收款') OR (LRP.COLLECT_STATUS = '经销商代偿')) THEN '完成'
            ELSE '未完成' END)                                    AS BATCH_STATUS,
       LRP.BATCH_SN                                            AS BATCH_SN,
       VLRP.FACT_RENT                                          AS FACT_RENT,
       VLRP.FACT_CORPUS                                        AS FACT_CORPUS,
       VLRP.FACT_INTEREST                                      AS FACT_INTEREST,
       VLRP.RENT_OVER                                          AS RENT_OVER,
       VLRP.CORPUS_OVER                                        AS CORPUS_OVER,
       VLRP.INTEREST_ADJUST                                    AS INTEREST_ADJUST,
       VLRP.INTEREST_OVER                                      AS INTEREST_OVER,
       VLRP.FACT_PENALTY                                       AS FACT_PENALTY,
       VLRP.PENALTY_ADJUST                                     AS PENALTY_ADJUST,
       VCP.PLAN_PENALTY                                        AS PENALTY,
       DI.DISTRIBUTOR_NAME                                     AS DISTRIBUTOR_NAME,
       DI.DISTRIBUTOR_NO                                       AS DISTRIBUTOR_NO,
       VS.OVERDUE_DAYS                                         AS OVER_DAYS,
       LRI.OWN_BANK                                            AS OWN_BANK,
       LRI.HIRE_DATE                                           AS HIRE_DATE,
       `getRemainInterests`(LRP.PAYMENT_NUMBER, LRP.PLAN_LIST) AS REMAININTEREST,
       ODR.NUM                                                 AS NUM,
       VSS.ACHIEVED_DATE                                       AS ACHIEVED_DATE
FROM ((((((((((((APZL.LC_RENT_PLAN LRP LEFT JOIN APZL.VI_CONTRACT_PENALTY VCP ON (((VCP.PAYMENT_NUMBER = LRP.PAYMENT_NUMBER) AND (VCP.PLAN_LIST = LRP.PLAN_LIST)))) LEFT JOIN APZL.VI_LC_RENT_PLAN VLRP ON ((LRP.ID = VLRP.ID))) LEFT JOIN APZL.LB_CONTRACT_INFO LCI ON ((LRP.CONTRACT_ID = LCI.ID))) LEFT JOIN (
    SELECT APZL.OVERDUE_DUNNING_RECORD.CONTRACT_ID AS CONTRACT_ID, count(0) AS NUM
    FROM APZL.OVERDUE_DUNNING_RECORD
    GROUP BY APZL.OVERDUE_DUNNING_RECORD.CONTRACT_ID
) ODR ON ((ODR.CONTRACT_ID = LCI.ID))) LEFT JOIN APZL.VI_OVERDUE_RENT_PLAN VS ON (((VS.CONTRACT_ID = LRP.CONTRACT_ID) AND (VS.PLAN_LIST = LRP.PLAN_LIST)))) LEFT JOIN (
    SELECT APZL.LC_RENT_INCOME.CONTRACT_ID    AS CONTRACT_ID,
           APZL.LC_RENT_INCOME.PLAN_LIST      AS PLAN_LIST,
           max(APZL.LC_RENT_INCOME.OWN_BANK)  AS OWN_BANK,
           max(APZL.LC_RENT_INCOME.HIRE_DATE) AS HIRE_DATE
    FROM APZL.LC_RENT_INCOME
    GROUP BY APZL.LC_RENT_INCOME.CONTRACT_ID, APZL.LC_RENT_INCOME.PLAN_LIST
) LRI ON (((LRI.CONTRACT_ID = LRP.CONTRACT_ID) AND (LRI.PLAN_LIST = LRP.PLAN_LIST)))) LEFT JOIN APZL.LB_UNION_LESSEE LUL ON (((LUL.CONTRACT_ID = LCI.ID) AND (LUL.IS_MAIN = 'Y')))) LEFT JOIN APZL.CUSTOMER_INFO CI ON ((CI.CUSTOMERID = LUL.CUSTOMER_ID))) LEFT JOIN APZL.CUSTOMER_PERSON CP ON ((LUL.CUSTOMER_ID = CP.CUSTOMERID))) LEFT JOIN APZL.USER_INFO UI ON ((UI.USERID = LCI.PROJECT_MANAGE))) LEFT JOIN APZL.DISTRIBUTOR_INFO DI ON ((DI.DISTRIBUTOR_NO = LCI.DISTRIBUTOR_ID)))
LEFT JOIN APZL.VERIFICATIONSPARE VSS ON ((VSS.SPARE_BATCH_NO = convert(LRP.SPARE_BATCH_NO USING UTF8))))
WHERE (LCI.BUSINESSTYPE = '1'));

-- comment on column vi_the_rent_plan.contract_number not supported: 业务合同号

-- comment on column vi_the_rent_plan.payment_number not supported: 投放编号

-- comment on column vi_the_rent_plan.customer_name not supported: 承租人名称

-- comment on column vi_the_rent_plan.mobile not supported: 手机号

-- comment on column vi_the_rent_plan.project_manage not supported: 项目经理

-- comment on column vi_the_rent_plan.project_manage_name not supported: 用户姓名

-- comment on column vi_the_rent_plan.plan_list not supported: 期次

-- comment on column vi_the_rent_plan.plan_date not supported: 计划日期

-- comment on column vi_the_rent_plan.rent not supported: 租金

-- comment on column vi_the_rent_plan.corpus not supported: 财务本金

-- comment on column vi_the_rent_plan.interest not supported: 财务租息

-- comment on column vi_the_rent_plan.ALL_REMAIN_CORPUS not supported: 本期后本金余额

-- comment on column vi_the_rent_plan.collect_status not supported: 收款状态

-- comment on column vi_the_rent_plan.collect_msg not supported: 收款描述

-- comment on column vi_the_rent_plan.batch_no not supported: 批量交易流水号,若拆分，以,分割

-- comment on column vi_the_rent_plan.year_rate not supported: 年利率

-- comment on column vi_the_rent_plan.distributor_name not supported: 经销商名称

-- comment on column vi_the_rent_plan.distributor_no not supported: 经销商编码

-- comment on column vi_the_rent_plan.own_bank not supported: 本方银行

-- comment on column vi_the_rent_plan.HIRE_DATE not supported: 核销日期