<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="lease_end_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_name"/>
        <bm:field name="lease_charge" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="unearned_finance_income" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="net_unearned_finance_income" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="vat_unearned_finance_income" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="cumulative_unearned_finance" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="cumulative_net_finance" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="not_unearned_finance" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="not_net_unearned_finance" databaseType="NUMBER" datatype="java.lang.Long"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select *
  from (select c.contract_number,
               c.lease_channel,
               (Select ch.description
                  from hls_lease_channel ch
                 Where ch.lease_channel = c.lease_channel) lease_channel_n,
               c.business_type,
               (select bt.description
                  from hls_business_type bt
                 where bt.business_type = c.business_type) AS business_type_n,
               c.lease_execution_date,
               to_char(c.lease_end_date, 'yyyy-mm-dd') lease_end_date,
               (select hb.bp_name
                  from hls_bp_master hb
                 where hb.bp_id = c.bp_id_tenant) bp_name,
               ((SELECT decode(c.hd_user_col_v05, 'Y', 0, 1) *
                        nvl(SUM(nvl(cc.due_amount, 0)), 0)
                   FROM con_contract_cashflow cc
                  WHERE cc.contract_id = c.contract_id
                    AND cc.cf_item = 3
                    AND cc.cf_status = 'RELEASE') +
               (SELECT nvl(SUM(nvl(cc.due_amount, 0)), 0)
                   FROM con_contract_cashflow cc
                  WHERE cc.contract_id = c.contract_id
                    AND cc.cf_item = 301
                    AND cc.cf_status = 'RELEASE')) as lease_charge,
               (select nvl(round(((nvl(cu.unearned_finance_income, 0) *
                                 (SELECT decode(t.tax_type_id,
                                                  1,
                                                  1,
                                                  (SELECT nvl(c.tax_type_rate, 0)
                                                     FROM fnd_tax_type_codes c
                                                    WHERE c.tax_type_id =
                                                          t.tax_type_id))
                                      FROM con_contract t
                                     WHERE t.contract_id = cu.contract_id)) +
                                 nvl(cu.unearned_finance_income, 0)),
                                 2),
                           0)
                  from con_unearned_finance_income cu
                 where cu.contract_id = c.contract_id
                   and cu.internal_period_num = ${@internal_period_num}
                   and cu.cf_item = 3) AS unearned_finance_income, --本期手续费分摊额(含税)
               (select nvl(sum(nvl(cu.unearned_finance_income, 0)), 0)
                  from con_unearned_finance_income cu
                 where cu.contract_id = c.contract_id
                   and cu.internal_period_num = ${@internal_period_num}
                   and cu.cf_item = 3) as net_unearned_finance_income, --本期手续费分摊额(不含税)
               (select sum(nvl(round((nvl(cu.unearned_finance_income, 0) *
                                 (SELECT decode(t.tax_type_id,
                                                 1,
                                                 1,
                                                 (SELECT nvl(c.tax_type_rate, 0)
                                                    FROM fnd_tax_type_codes c
                                                   WHERE c.tax_type_id =
                                                         t.tax_type_id))
                                     FROM con_contract t
                                    WHERE t.contract_id = cu.contract_id)),
                                 2),
                           0))
                  from con_unearned_finance_income cu
                 where cu.contract_id = c.contract_id
                   and cu.internal_period_num = ${@internal_period_num}
                   and cu.cf_item = 3) AS vat_unearned_finance_income, --本期手续费分摊额(税金)
               (select nvl(sum(nvl(round(((cu.unearned_finance_income *
                                         (SELECT decode(t.tax_type_id,
                                                          1,
                                                          1,
                                                          (SELECT c.tax_type_rate
                                                             FROM fnd_tax_type_codes c
                                                            WHERE c.tax_type_id =
                                                                  t.tax_type_id))
                                              FROM con_contract t
                                             WHERE t.contract_id =
                                                   cu.contract_id)) +
                                         cu.unearned_finance_income),
                                         2),
                                   0)),
                           0)
                  from con_unearned_finance_income cu
                 where cu.contract_id = c.contract_id
                   and cu.internal_period_num <= ${@internal_period_num}
                   and cu.cf_item = 3) cumulative_unearned_finance, --累计分担额(含税)
               (select nvl(sum(nvl(cu.unearned_finance_income, 0)), 0)
                  from con_unearned_finance_income cu
                 where cu.contract_id = c.contract_id
                   and cu.internal_period_num <= ${@internal_period_num}
                   and cu.cf_item = 3) cumulative_net_finance, --累计分担手续费手续费(不含税)
               ((select nvl(sum(nvl(round(((cu.unearned_finance_income *
                                          (SELECT decode(t.tax_type_id,
                                                           1,
                                                           1,
                                                           (SELECT c.tax_type_rate
                                                              FROM fnd_tax_type_codes c
                                                             WHERE c.tax_type_id =
                                                                   t.tax_type_id))
                                               FROM con_contract t
                                              WHERE t.contract_id =
                                                    cu.contract_id)) +
                                          cu.unearned_finance_income),
                                          2),
                                    0)),
                            0)
                   from con_unearned_finance_income cu
                  where cu.contract_id = c.contract_id
                    and cu.cf_item = 3) -
               (select nvl(sum(nvl(round(((cu.unearned_finance_income *
                                          (SELECT decode(t.tax_type_id,
                                                           1,
                                                           1,
                                                           (SELECT c.tax_type_rate
                                                              FROM fnd_tax_type_codes c
                                                             WHERE c.tax_type_id =
                                                                   t.tax_type_id))
                                               FROM con_contract t
                                              WHERE t.contract_id =
                                                    cu.contract_id)) +
                                          cu.unearned_finance_income),
                                          2),
                                    0)),
                            0)
                   from con_unearned_finance_income cu
                  where cu.contract_id = c.contract_id
                    and cu.internal_period_num <= ${@internal_period_num}
                    and cu.cf_item = 3)) not_unearned_finance, --待摊余额(含税)
               ((select nvl(sum(nvl(cu.unearned_finance_income, 0)), 0)
                   from con_unearned_finance_income cu
                  where cu.contract_id = c.contract_id
                    and cu.cf_item = 3) -
               (select nvl(sum(nvl(cu.unearned_finance_income, 0)), 0)
                   from con_unearned_finance_income cu
                  where cu.contract_id = c.contract_id
                    and cu.internal_period_num <= ${@internal_period_num}
                    and cu.cf_item = 3)) not_net_unearned_finance --待摊余额(不含税)

         FROM con_unearned_finance_income i, con_contract c
             WHERE i.contract_id = c.contract_id
               AND i.internal_period_num = ${@internal_period_num}
               AND c.data_class = 'NORMAL'
               AND i.cf_item =3) t1


                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="lease_channel_n" queryOperator="="/>
        <bm:query-field field="business_type_n" queryOperator="="/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.lease_execution_date &gt;= to_date(${@lease_execution_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.lease_execution_date &lt;= to_date(${@lease_execution_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
