rpt5012_result_query.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: WangYu  
    $Date: 2014-4-25 上午09:30:21  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select

                    (SELECT m.extra_nam
                     FROM hls_bp_master m
                     WHERE m.bp_id = ct.bp_id_agent_level1)                                                                       agent_extra_name,
                    --代理店简称
                    (SELECT m.bp_name
                     FROM hls_bp_master m
                     WHERE m.bp_id =
                           ct.bp_id_tenant)        AS                                                                             bp_tenant_name,
                    --承租人名称
                    ct.contract_number,
                    --合同编号
                    to_char(ct.lease_start_date,
                            'yyyy-mm-dd')                                                                                         lease_start_date,
                    --租赁期开始日
                    (SELECT ccli.modelcd
                     FROM con_contract_lease_item ccli
                     WHERE ccli.contract_id =
                           ct.contract_id)                                                                                        modelcd,
                    --机型
                    (SELECT nvl(i.machine_number,i.machine_number_70)
                     FROM con_contract_lease_item i
                     WHERE i.contract_id = ct.contract_id
                           AND i.equipment_type =
                               'MAIN')                                                                                            machine_number,
                    --机号
                    (NVL(ct.int_rate_display, 0) * 100 ||
                     '%')                                                                                                         int_rate_display,
                    --利率
                    ct.lease_times,
                    --租赁期间
                    (NVL(ct.down_payment_ratio, 0) * 100 ||
                     '%')                                                                                                         down_payment_ratio,
                    --首付比例
                    ct.lease_item_amount           as                                                                             lease_item_amount,
                    --设备款
                    NVL(ct.residual_value,
                        0)                                                                                                        residual_amount,
                    --留购金
                    NVL(ct.total_rental, 0) + NVL(ct.down_payment, 0) +
                    NVL(ct.residual_value,
                        0)                                                                                                        contract_amount,
                    --合同金额
                    NVL(ct.down_payment,
                        0)                                                                                                        down_payment,
                    --首付款
                    (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.due_amount)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_due_amount, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)<=0) then 0 else 
								(CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.due_amount)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_due_amount, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
								end) month_due_amount,
 --当月末应收款余额（合计）
 (CASE
   WHEN (CASE
          WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
           0
          ELSE
           ((SELECT SUM(ccw.due_amount)
               FROM con_contract_cashflow ccw
              WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
           nvl((SELECT SUM(nvl(cw.write_off_due_amount, 0))
                  FROM csh_transaction cn, csh_write_off cw
                 WHERE cw.csh_transaction_id = cn.transaction_id
                   AND cw.cf_item IN (1, 200, 250)
                   AND cw.contract_id = ct.contract_id
                   AND NVL(cw.reversed_flag, 'N') = 'N'
                   AND trunc(cn.transaction_date) <=
                       trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                                     nvl(tt.write_off_amount, 0) -
                                     nvl(tt.returned_amount, 0)))
                            FROM csh_transaction tt
                           WHERE tt.transaction_type = 'DEPOSIT'
                             AND tt.deposit_trans_type = 'rent_deposit'
                             AND tt.ref_contract_id = ct.contract_id
                             AND tt.transaction_date <=
                                 trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                          0))
        END) <= 0 THEN
    0
   ELSE
    (CASE
      WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
       0
      ELSE
       ((SELECT SUM(ccw.due_amount)
           FROM con_contract_cashflow ccw
          WHERE ccw.contract_id = ct.contract_id
            AND ccw.cf_item IN (1, 200, 250)
            AND ccw.cf_status = 'RELEASE') -
       nvl((SELECT SUM(nvl(cw.write_off_due_amount, 0))
              FROM csh_transaction cn, csh_write_off cw
             WHERE cw.csh_transaction_id = cn.transaction_id
               AND cw.cf_item IN (1, 200, 250)
               AND cw.contract_id = ct.contract_id
               AND NVL(cw.reversed_flag, 'N') = 'N'
               AND trunc(cn.transaction_date) <=
                   trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
            0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                                 nvl(tt.write_off_amount, 0) -
                                 nvl(tt.returned_amount, 0)))
                        FROM csh_transaction tt
                       WHERE tt.transaction_type = 'DEPOSIT'
                         AND tt.deposit_trans_type = 'rent_deposit'
                         AND tt.ref_contract_id = ct.contract_id
                         AND tt.transaction_date <=
                             trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                      0))
    END)
 END) month_due_amount_after,
 --当月末应收款余额（合计）保证金后
 (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.principal)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) <=0) then 0 else 
								(CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.principal)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
								end) month_due_principal,
 --当月末本金余额（合计）
 (CASE
   WHEN (CASE
          WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
           0
          ELSE
           ((SELECT SUM(ccw.principal)
               FROM con_contract_cashflow ccw
              WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
           nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                  FROM csh_transaction cn, csh_write_off cw
                 WHERE cw.csh_transaction_id = cn.transaction_id
                   AND cw.cf_item IN (1, 200, 250)
                   AND cw.contract_id = ct.contract_id
                   AND NVL(cw.reversed_flag, 'N') = 'N'
                   AND trunc(cn.transaction_date) <=
                       trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                                     nvl(tt.write_off_amount, 0) -
                                     nvl(tt.returned_amount, 0)))
                            FROM csh_transaction tt
                           WHERE tt.transaction_type = 'DEPOSIT'
                             AND tt.deposit_trans_type = 'rent_deposit'
                             AND tt.ref_contract_id = ct.contract_id
                             AND tt.transaction_date <=
                                 trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                          0))
        END) <= 0 THEN
    0
   ELSE
    (CASE
      WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
       0
      ELSE
       ((SELECT SUM(ccw.principal)
           FROM con_contract_cashflow ccw
          WHERE ccw.contract_id = ct.contract_id
            AND ccw.cf_item IN (1, 200, 250)
            AND ccw.cf_status = 'RELEASE') -
       nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
              FROM csh_transaction cn, csh_write_off cw
             WHERE cw.csh_transaction_id = cn.transaction_id
               AND cw.cf_item IN (1, 200, 250)
               AND cw.contract_id = ct.contract_id
               AND NVL(cw.reversed_flag, 'N') = 'N'
               AND trunc(cn.transaction_date) <=
                   trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
            0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                                 nvl(tt.write_off_amount, 0) -
                                 nvl(tt.returned_amount, 0)))
                        FROM csh_transaction tt
                       WHERE tt.transaction_type = 'DEPOSIT'
                         AND tt.deposit_trans_type = 'rent_deposit'
                         AND tt.ref_contract_id = ct.contract_id
                         AND tt.transaction_date <=
                             trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                      0))
    END)
 END) month_due_principal_after,
 --当月末本金余额（合计）保证金后
 (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.interest)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) <=0) then 0 else 
								(CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.interest)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (1, 200, 250)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
								end) month_interest,
 --当月末利息余额（合计）
 (CASE
   WHEN ((SELECT SUM((nvl(tt.transaction_amount, 0) -
                     nvl(tt.write_off_amount, 0) -
                     nvl(tt.returned_amount, 0)))
            FROM csh_transaction tt
           WHERE tt.transaction_type = 'DEPOSIT'
             AND tt.deposit_trans_type = 'rent_deposit'
             AND tt.ref_contract_id = ct.contract_id
             AND tt.transaction_date <=
                 trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) - ((CASE
          WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
           0
          ELSE
           ((SELECT SUM(ccw.principal)
               FROM con_contract_cashflow ccw
              WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 250)
                AND ccw.cf_status = 'RELEASE') -
           nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                  FROM csh_transaction cn, csh_write_off cw
                 WHERE cw.csh_transaction_id = cn.transaction_id
                   AND cw.cf_item IN (1, 200, 250)
                   AND cw.contract_id = ct.contract_id
                   AND NVL(cw.reversed_flag, 'N') = 'N'
                   AND trunc(cn.transaction_date) <=
                       trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
        END))) > 0 THEN
    (CASE
      WHEN ((CASE
             WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
              0
             ELSE
              ((SELECT SUM(ccw.interest)
                  FROM con_contract_cashflow ccw
                 WHERE ccw.contract_id = ct.contract_id
                   AND ccw.cf_item IN (1, 200, 250)
                   AND ccw.cf_status = 'RELEASE') -
              nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                     FROM csh_transaction cn, csh_write_off cw
                    WHERE cw.csh_transaction_id = cn.transaction_id
                      AND cw.cf_item IN (1, 200, 250)
                      AND cw.contract_id = ct.contract_id
                      AND NVL(cw.reversed_flag, 'N') = 'N'
                      AND trunc(cn.transaction_date) <=
                          trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                   0))
           END) - (SELECT SUM((nvl(tt.transaction_amount, 0) -
                               nvl(tt.write_off_amount, 0) -
                               nvl(tt.returned_amount, 0)))
                      FROM csh_transaction tt
                     WHERE tt.transaction_type = 'DEPOSIT'
                       AND tt.deposit_trans_type = 'rent_deposit'
                       AND tt.ref_contract_id = ct.contract_id
                       AND tt.transaction_date <=
                           trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) - (CASE
             WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
              0
             ELSE
              ((SELECT SUM(ccw.principal)
                  FROM con_contract_cashflow ccw
                 WHERE ccw.contract_id = ct.contract_id
                   AND ccw.cf_item IN (1, 200, 250)
                   AND ccw.cf_status = 'RELEASE') -
              nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                     FROM csh_transaction cn, csh_write_off cw
                    WHERE cw.csh_transaction_id = cn.transaction_id
                      AND cw.cf_item IN (1, 200, 250)
                      AND cw.contract_id = ct.contract_id
                      AND NVL(cw.reversed_flag, 'N') = 'N'
                      AND trunc(cn.transaction_date) <=
                          trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                   0))
           END)) <= 0 THEN
       0
      ELSE
       ((CASE
         WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
          0
         ELSE
          ((SELECT SUM(ccw.interest)
              FROM con_contract_cashflow ccw
             WHERE ccw.contract_id = ct.contract_id
               AND ccw.cf_item IN (1, 200, 250)
               AND ccw.cf_status = 'RELEASE') -
          nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                 FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                  AND cw.cf_item IN (1, 200, 250)
                  AND cw.contract_id = ct.contract_id
                  AND NVL(cw.reversed_flag, 'N') = 'N'
                  AND trunc(cn.transaction_date) <=
                      trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
               0))
       END) -
       (SELECT SUM((nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                    nvl(tt.returned_amount, 0)))
           FROM csh_transaction tt
          WHERE tt.transaction_type = 'DEPOSIT'
            AND tt.deposit_trans_type = 'rent_deposit'
            AND tt.ref_contract_id = ct.contract_id
            AND tt.transaction_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))) -
       (CASE
         WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
          0
         ELSE
          ((SELECT SUM(ccw.principal)
              FROM con_contract_cashflow ccw
             WHERE ccw.contract_id = ct.contract_id
               AND ccw.cf_item IN (1, 200, 250)
               AND ccw.cf_status = 'RELEASE') -
          nvl((SELECT SUM(nvl(cw.write_off_principal, 0))
                 FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                  AND cw.cf_item IN (1, 200, 250)
                  AND cw.contract_id = ct.contract_id
                  AND NVL(cw.reversed_flag, 'N') = 'N'
                  AND trunc(cn.transaction_date) <=
                      trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
               0))
       END))
    END)
   ELSE
    (CASE
      WHEN (CASE
             WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
              0
             ELSE
              ((SELECT SUM(ccw.interest)
                  FROM con_contract_cashflow ccw
                 WHERE ccw.contract_id = ct.contract_id
                   AND ccw.cf_item IN (1, 200, 250)
                   AND ccw.cf_status = 'RELEASE') -
              nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                     FROM csh_transaction cn, csh_write_off cw
                    WHERE cw.csh_transaction_id = cn.transaction_id
                      AND cw.cf_item IN (1, 200, 250)
                      AND cw.contract_id = ct.contract_id
                      AND NVL(cw.reversed_flag, 'N') = 'N'
                      AND trunc(cn.transaction_date) <=
                          trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                   0))
           END) <= 0 THEN
       0
      ELSE
       (CASE
         WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
          0
         ELSE
          ((SELECT SUM(ccw.interest)
              FROM con_contract_cashflow ccw
             WHERE ccw.contract_id = ct.contract_id
               AND ccw.cf_item IN (1, 200, 250)
               AND ccw.cf_status = 'RELEASE') -
          nvl((SELECT SUM(nvl(cw.write_off_interest, 0))
                 FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                  AND cw.cf_item IN (1, 200, 250)
                  AND cw.contract_id = ct.contract_id
                  AND NVL(cw.reversed_flag, 'N') = 'N'
                  AND trunc(cn.transaction_date) <=
                      trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
               0))
       END)
    END)
 END) month_interest_after,
 --当月末利息余额（合计）保证金后
  (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                ((SELECT SUM(ccw.due_amount)
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (8)
                and ccw.cf_status = 'RELEASE') -
                nvl((SELECT SUM(nvl(cw.write_off_due_amount, 0))
                FROM csh_transaction cn, csh_write_off cw
                WHERE cw.csh_transaction_id = cn.transaction_id
                AND cw.cf_item IN (8)
                AND cw.contract_id = ct.contract_id
                AND NVL(cw.reversed_flag, 'N') = 'N'
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END) month_residual,
 --当月末留购金余额
 (CASE
   WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
    0
   ELSE
    con_contract_pkg.get_business_times(p_contract_id             => ct.contract_id,
                                        p_calc_date               => to_date(${@cur_end},
                                                                             'yyyy-mm-dd'),
                                        p_contract_inception_date => ct.contract_inception_date)
 END) business_due_times_after,
 --逾期期数(保证金后)
 (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (Select count(*)
                    From con_contract_cashflow ccw
                   Where trunc(ccw.due_date) <= trunc(to_date(${@cur_end},'yyyy-mm-dd'))
                     And ccw.contract_id = ct.contract_id
                     And ccw.cf_item In (1, 200,250)
                     and ccw.cf_status = 'RELEASE'
                     And ccw.times > 0
					 --and ccw.due_amount > nvl(ccw.received_amount,0)
					 and ccw.due_amount>(SELECT nvl(SUM(nvl(cwo.write_off_due_amount, 0)),0)
                 FROM csh_write_off cwo, csh_transaction cn
                WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                  AND cwo.contract_id = ccw.contract_id
                  AND cwo.times = ccw.times
                  AND cwo.csh_transaction_id = cn.transaction_id
                  AND cwo.cf_item = ccw.cf_item
                  AND trunc(cn.transaction_date) <=
                      trunc(to_date(${@cur_end}, 'yyyy-mm-dd')) 
                      )
					 )
                END) business_due_times,
 --逾期期数(保证金前)
 (case when ((CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (nvl((SELECT SUM(ccw.due_amount) -
                SUM(nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo, csh_transaction cn
                WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                AND cwo.contract_id = ccw.contract_id
                AND cwo.times = ccw.times
                AND cwo.csh_transaction_id =
                cn.transaction_id
                AND cwo.cf_item = ccw.cf_item
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end},
                'yyyy-mm-dd'))),
                0))
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 8, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)<=0) then 0 else (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (nvl((SELECT SUM(ccw.due_amount) -
                SUM(nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo, csh_transaction cn
                WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                AND cwo.contract_id = ccw.contract_id
                AND cwo.times = ccw.times
                AND cwo.csh_transaction_id =
                cn.transaction_id
                AND cwo.cf_item = ccw.cf_item
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end},
                'yyyy-mm-dd'))),
                0))
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 8, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                end) business_due_amount,
 --当月末逾期金额（合计）
 
 (case when (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (nvl((SELECT SUM(ccw.due_amount) -
                SUM(nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo, csh_transaction cn
                WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                AND cwo.contract_id = ccw.contract_id
                AND cwo.times = ccw.times
                AND cwo.csh_transaction_id = cn.transaction_id
                AND cwo.cf_item = ccw.cf_item
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 8, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                nvl(tt.write_off_amount, 0) -
                nvl(tt.returned_amount, 0)))
                FROM csh_transaction tt
                WHERE tt.transaction_type = 'DEPOSIT'
                AND tt.deposit_trans_type = 'rent_deposit'
                AND tt.ref_contract_id = ct.contract_id
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)<=0 then
                0
                else
                (CASE
                WHEN ct.contract_status IN ('TERMINATE', 'REPUR', 'ET') THEN
                0
                ELSE
                (nvl((SELECT SUM(ccw.due_amount) -
                SUM(nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo, csh_transaction cn
                WHERE nvl(cwo.reversed_flag, 'N') = 'N'
                AND cwo.contract_id = ccw.contract_id
                AND cwo.times = ccw.times
                AND cwo.csh_transaction_id = cn.transaction_id
                AND cwo.cf_item = ccw.cf_item
                AND trunc(cn.transaction_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = ct.contract_id
                AND ccw.cf_item IN (1, 200, 8, 250)
                AND ccw.cf_status = 'RELEASE'
                AND trunc(ccw.due_date) <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0) - nvl((SELECT SUM((nvl(tt.transaction_amount, 0) -
                nvl(tt.write_off_amount, 0) -
                nvl(tt.returned_amount, 0)))
                FROM csh_transaction tt
                WHERE tt.transaction_type = 'DEPOSIT'
                AND tt.deposit_trans_type = 'rent_deposit'
                AND tt.ref_contract_id = ct.contract_id
                AND tt.transaction_date <=
                trunc(to_date(${@cur_end}, 'yyyy-mm-dd'))),
                0))
                END)
                end) business_due_amount_after,
 --当月末逾期金额（合计）保证金后
 
 ((SELECT SUM(nvl(tt.transaction_amount, 0))
                FROM csh_transaction tt
                WHERE tt.transaction_type = 'DEPOSIT'
                AND tt.deposit_trans_type = 'rent_deposit'
                AND tt.ref_contract_id =  ct.contract_id
                AND tt.transaction_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd')))
							-
(SELECT sum(nvl(cwo.WRITE_OFF_DUE_AMOUNT,0))               
             FROM csh_write_off cwo
								where   cwo.write_off_type in ('CSH_RETURN','DEPOSIT_TRANSFER','DEPOSIT_CREDIT')
                AND     cwo.csh_transaction_id in (select cts.transaction_id from csh_transaction cts where cts.ref_contract_id = ct.contract_id)
                AND     cwo.write_off_date <= trunc(to_date(${@cur_end}, 'yyyy-mm-dd')))) deposit_amount,
 --保证金金额
 
 (SELECT v.code_value_name
    FROM sys_code_values_v v
   WHERE v.code = 'CON500_CONTRACT_STATUS'
     AND v.code_value = ct.contract_status) AS contract_status_n,
 --合同金额
 (SELECT to_char(due_date, 'yyyy-mm-dd')
                FROM con_contract_cashflow
                WHERE contract_id = ct.contract_id
                AND cf_status = 'RELEASE'
                AND cf_item = 8) residual_date,
 -- 合同终了
                 /*(CASE
                WHEN ct.contract_status IN ('INCEPT', 'ETING', 'TERMINATE', 'ET') THEN
                (SELECT to_char(MAX(cc.last_received_date),'yyyy-mm-dd')
                FROM con_contract_cashflow cc
                WHERE cc.contract_id = ct.contract_id
                AND cf_status = 'RELEASE'
                AND cf_item = 8)
                WHEN ct.contract_status IN ('REPUR') THEN
                (SELECT to_char(MAX(cc.last_received_date),'yyyy-mm-dd')
                FROM con_contract_cashflow cc
                WHERE cc.contract_id = ct.contract_id
                AND cf_status = 'RELEASE'
                AND cf_item = 250)
                ELSE
                ''
                END) terminate_date,*/
                to_char(ct.et_date, 'yyyy-mm-dd') terminate_date,
 ct.is_buyout,
 (SELECT v.code_value_name
    FROM sys_code_values_v v
   WHERE v.code = 'YES_OR_NO'
     AND v.code_enabled_flag = 'Y'
     AND v.code_value_enabled_flag = 'Y'
     AND v.CODE_VALUE = ct.is_buyout) is_buyout_n,
  ct.large_balance,
 (SELECT v.code_value_name
    FROM sys_code_values_v v
   WHERE v.code = 'SECONDARY_LEASE'
     AND v.code_value =
         ct.large_balance) large_balance_n,
 ct.branch_code,
 (SELECT v.code_value_name
    FROM sys_code_values_v v
   WHERE v.code = 'BRANCH_CODE_SZMP'
     AND v.code_enabled_flag = 'Y'
     AND v.code_value_enabled_flag = 'Y'
     AND v.code_value = ct.branch_code) branch_code_n,
 (select decode(bp.bp_class, 'NP', '自然人', '法人')
    from con_contract_bp bp
   where bp.contract_id = ct.contract_id
     and bp.bp_category = 'TENANT') bp_class_n,
 (select b.description
    from hls_business_type b
   where b.business_type = ct.business_type) business_type_n,
 NVL((Select h1.code_value_name value_name
       From sys_code_values_v h1
      Where h1.code = 'SECONDARY_LEASE'
        And h1.code_enabled_flag = 'Y'
        And h1.code_value_enabled_flag = 'Y'
        AND H1.code_value = CT.SECONDARY_LEASE),
     '否') SECONDARY_LEASE_N,
 (select d.description from hls_division d where d.division = ct.division) division_n,
 nvl((select '是'
       from con_contract_change_req ccr
      where ccr.contract_id = ct.contract_id
        and ccr.req_status = 'APPROVED'
        and ccr.document_type = 'LEASE_CHAG'
        and rownum = 1),
     '否') tenant_change_flag_n,
     nvl((select '是'
             from dual
            where not exists (select 1
                     from con_contract_cashflow ccc
                    where ccc.cf_item = 1
                      and ccc.times <= 12
                      and ccc.contract_id = ct.contract_id
                      and ccc.write_off_flag != 'FULL'
                      and ccc.due_amount != 0
                      and ccc.cf_direction = 'INFLOW')
              and exists (select 1
                     from con_contract_cashflow ccc
                    where ccc.cf_item = 1
                      and ccc.times = 12
                      and ccc.contract_id = ct.contract_id
                      and (ccc.write_off_flag = 'FULL' or ccc.due_amount=0)
                      and ccc.cf_direction = 'INFLOW')),
           '否') rental_12_pay_flag,
 nvl((select '是'
             from con_contract_cashflow ccc
            where ccc.cf_item = 9
              and ccc.cf_direction = 'INFLOW'
              and ccc.contract_id = ct.contract_id
              and (ccc.due_amount > 0 or exists
                   (select 1
                      from CON_CONTRACT_CASE_RECORD    cr,
                           CON_CONTRACT_PENALTY_REDUCE pr,
                           CON_CONTRACT_PENALTY_INFO   pi
                     where cr.record_id = pr.record_id
                       and pr.penalty_reduce_id = pi.penalty_reduce_id
                       and pr.contract_id = ccc.contract_id
                       and pi.penalty_reduce_amount > 0
                       and cr.status = 'APPROVED'))
              and rownum = 1),
           '否') cf_9_exists_flag,
       nvl((select '是'
             from con_contract_cashflow ccc
            where ccc.cf_item = 9
              and ccc.cf_direction = 'INFLOW'
              and ccc.contract_id = ct.contract_id
              and (ccc.due_amount > 0 or exists
                   (select 1
                      from CON_CONTRACT_CASE_RECORD    cr,
                           CON_CONTRACT_PENALTY_REDUCE pr,
                           CON_CONTRACT_PENALTY_INFO   pi,
                           con_contract_cashflow       cf
                     where cr.record_id = pr.record_id
                       and pr.penalty_reduce_id = pi.penalty_reduce_id
                       and pr.contract_id = ccc.contract_id
                       and pi.penalty_reduce_amount > 0
                       and cr.status = 'APPROVED'
                       and pi.cashflow_id = cf.cashflow_id
                       and cf.times > 1))
              and ccc.times > 1
              and rownum = 1),
           '否') cf_9_notfirsttime_exists_flag
                    ,decode(acr_invoice_pkg.check_rl_flag(ct.contract_id), 'N', '否', 'Y', '是') rl_flag_n
  FROM con_contract ct
 WHERE ct.contract_status NOT IN ('CANCEL')
   AND ct.data_class = 'NORMAL'
 ORDER BY ct.bp_id_agent_level1
                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                    rpt5012_rental_balance_pkg.rental_balance_insert(
                            p_month   =>${@month},
                            p_user_id      =>${/session/@user_id},
                            p_batch_id       =>${@batch_id}
                    );
                end;
                ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                    rpt5012_rental_balance_pkg.get_month_date(
                            p_month   =>${@month},
                            p_cur_begin      =>${@cur_begin},
                            p_cur_end      =>${@cur_end},
                            p_before_begin      =>${@before_begin},
                            p_before_end       =>${@before_end}
                    );
                end;
                ]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="cur_begin" dataType="date" input="true" output="true"
                              outputPath="/parameter/@cur_begin"/>
                <bm:parameter name="cur_end" dataType="date" input="true" output="true"
                              outputPath="/parameter/@cur_end"/>
                <bm:parameter name="before_begin" dataType="date" input="true" output="true"
                              outputPath="/parameter/@before_begin"/>
                <bm:parameter name="before_end" dataType="date" input="true" output="true"
                              outputPath="/parameter/@before_end"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
	    <bm:fields>
        <bm:field name="agent_extra_name"/>
        <bm:field name="bp_tenant_name"/>
        <bm:field name="contract_number"/>
        <bm:field name="lease_start_date"/>
        <bm:field name="modelcd"/>
        <bm:field name="machine_number"/>
        <bm:field name="int_rate_display" datatype="java.lang.String"/>
        <bm:field name="lease_times" datatype="java.lang.Long"/>
        <bm:field name="down_payment_ratio"/>
        <bm:field name="lease_item_amount" datatype="java.lang.Double"/>
        <bm:field name="residual_amount" datatype="java.lang.Double"/>
        <bm:field name="contract_amount" datatype="java.lang.Double"/>
        <bm:field name="down_payment" datatype="java.lang.Double"/>
        <bm:field name="month_due_amount" datatype="java.lang.Double"/>
        <bm:field name="month_due_amount_after" datatype="java.lang.Double"/>
        <bm:field name="month_due_principal" datatype="java.lang.Double"/>
        <bm:field name="month_due_principal_after" datatype="java.lang.Double"/>
        <bm:field name="month_interest" datatype="java.lang.Double"/>
        <bm:field name="month_interest_after" datatype="java.lang.Double"/>
        <bm:field name="month_residual" datatype="java.lang.Double"/>
        <bm:field name="business_due_times" datatype="java.lang.Long"/>
		<bm:field name="business_due_times_after" datatype="java.lang.Long"/>
        <bm:field name="business_due_amount" datatype="java.lang.Double"/>
        <bm:field name="business_due_amount_after" datatype="java.lang.Double"/>
        <bm:field name="deposit_amount" datatype="java.lang.Double"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="residual_date"/>
        <bm:field name="terminate_date"/>
        <bm:field name="is_buyout_n"/>
		<bm:field name="large_balance_n"/>
		<bm:field name="branch_code_n"/>
        <bm:field name="bp_class_n"/>
        <bm:field name="business_type_n"/>
        <bm:field name="division_n"/>
        <bm:field name="tenant_change_flag_n"/>
        <bm:field name="rental_12_pay_flag"/>
        <bm:field name="cf_9_exists_flag"/>
        <bm:field name="cf_9_notfirsttime_exists_flag"/>
        <bm:field name="secondary_lease_n"/>
        <bm:field name="rl_flag_n"/>
    </bm:fields>
    <!--    <bm:query-fields>-->
    <!--        <bm:query-field field="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n = ${@bp_id_tenant_n}"/>-->
    <!--        <bm:query-field field="account_name" queryExpression="t1.account_name = ${@account_name}"/>-->
    <!--        <bm:query-field field="account_id_n" queryExpression="t1.account_id_n = ${@account_id_n}"/>-->
    <!--    </bm:query-fields>-->
</bm:model>