<?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="guarantor_info"/>
        <bm:field name="jan_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="feb_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="mar_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="apr_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="may_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="jun_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="jul_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="aug_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="sep_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="oct_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="nov_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="dec_unearned" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select *
  from (select cc.contract_number,
       (SELECT c.lease_channel
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (SELECT c.lease_channel
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) lease_channel_n,
       (SELECT c.business_type
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type =
               (SELECT c.business_type
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) AS business_type_n,
       cc.lease_execution_date,
       cc.guarantor_info,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'01') jan_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'02') feb_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'03') mar_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'04') apr_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'05') may_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'06') jun_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'07') jul_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'08') aug_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'09') sep_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'10') oct_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'11') nov_unearned,
       (select nvl(sum(nvl(cu.unearned_finance_income,0)),0)
          from con_unearned_finance_income cu
         where cu.contract_id = cc.contract_id
           and cu.cf_type = 3
           and cu.internal_period_num = ${@query_year}||'12') dec_unearned
  from con_contract cc
  where  cc.data_class = 'NORMAL'
  and cc.contract_status not in('CANCEL','CLOSED')
  and cc.contract_id in (SELECT cuf.contract_id FROM con_unearned_finance_income cuf where cuf.cf_type=3)) t1


                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="guarantor_info" queryOperator="like"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="lease_channel_n" queryOperator="like"/>
        <bm:query-field field="business_type_n" queryOperator="like"/>
        <bm:query-field field="guarantor_info" queryOperator="like"/>
        <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>
