create view vi_rent_plan as SELECT LRP.ID                                                                                                AS ID,
       LRP.CONTRACT_ID                                                                                       AS CONTRACT_ID,
       LRP.PAYMENT_NUMBER                                                                                    AS PAYMENT_NUMBER,
       LCI.CONTRACT_NUMBER                                                                                   AS CONTRACT_NUMBER,
       LUL.CUSTOMER_ID                                                                                       AS CUSTOMER_ID,
       LUL.CUSTOMER_NAME                                                                                     AS CUSTOMER_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,
       VCP.PLAN_PENALTY                                                                                      AS PLAN_PENALTY,
       `nvl`(LRI.HIRE_LIST, 0)                                                                               AS HIRE_LIST,
       `nvl`(LRI.RENT, 0)                                                                                    AS INCOME_RENT,
       `nvl`(LRI.CORPUS, 0)                                                                                  AS INCOME_CORPUS,
       `nvl`(LRI.INTEREST, 0)                                                                                AS INCOME_INTEREST,
       VCP.INCOME_PENALTY                                                                                    AS INCOME_PENALTY,
       (LRP.RENT - `nvl`(LRI.RENT, 0))                                                                       AS RENT_OVER,
       (LRP.CORPUS - `nvl`(LRI.CORPUS, 0))                                                                   AS CORPUS_OVER,
       (LRP.INTEREST - `nvl`(LRI.INTEREST, 0))                                                               AS INTEREST_OVER,
       VCP.PENALTY                                                                                           AS PENALTY_OVER,
       `nvl`(LRI.PENALTY_ADJUST, 0)                                                                          AS PENALTY_ADJUST,
       0                                                                                                     AS OVERDUE_DAYS,
       (CASE
            WHEN (((LRP.RENT - `nvl`(LRI.RENT, 0)) = 0) AND (VCP.PENALTY = 0)) THEN '已回笼'
            ELSE (CASE WHEN ((`nvl`(LRI.RENT, 0) + VCP.INCOME_PENALTY) > 0) THEN '部分回笼' ELSE '未回笼' END) END) AS PLANSTATUS
FROM ((((APZL.LC_RENT_PLAN LRP LEFT JOIN APZL.LB_CONTRACT_INFO LCI ON ((LCI.ID = LRP.CONTRACT_ID))) LEFT JOIN APZL.LB_UNION_LESSEE LUL ON (((LUL.CONTRACT_ID = LRP.CONTRACT_ID) AND (LUL.IS_MAIN = 'Y')))) LEFT JOIN APZL.VI_CONTRACT_PENALTY VCP ON (((VCP.PAYMENT_NUMBER = LRP.PAYMENT_NUMBER) AND (VCP.PLAN_LIST = LRP.PLAN_LIST))))
LEFT JOIN (
    SELECT APZL.LC_RENT_INCOME.PAYMENT_NUMBER                                                       AS PAYMENT_NUMBER,
           APZL.LC_RENT_INCOME.PLAN_LIST                                                            AS PLAN_LIST,
           count(1)                                                                                 AS HIRE_LIST,
           sum(APZL.LC_RENT_INCOME.RENT)                                                            AS RENT,
           sum(APZL.LC_RENT_INCOME.CORPUS)                                                          AS CORPUS,
           sum(APZL.LC_RENT_INCOME.PENALTY_ADJUST)                                                  AS PENALTY_ADJUST,
           (sum(APZL.LC_RENT_INCOME.INTEREST) + sum(`nvl`(APZL.LC_RENT_INCOME.INTEREST_ADJUST, 0))) AS INTEREST
    FROM APZL.LC_RENT_INCOME
    WHERE (APZL.LC_RENT_INCOME.ROLL_BACK = '0')
    GROUP BY APZL.LC_RENT_INCOME.PAYMENT_NUMBER, APZL.LC_RENT_INCOME.PLAN_LIST
) LRI ON (((LRI.PAYMENT_NUMBER = LRP.PAYMENT_NUMBER) AND (LRI.PLAN_LIST = LRP.PLAN_LIST))))
WHERE (LRP.PAYMENT_NUMBER IS NOT NULL);

-- comment on column vi_rent_plan.id not supported: 标识

-- comment on column vi_rent_plan.contract_id not supported: 合同编号

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

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

-- comment on column vi_rent_plan.customer_id not supported: 承租人编号

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

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

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

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

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

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

-- comment on column vi_rent_plan.all_remain_corpus not supported: 本期后本金余额