<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
           with tmp1 as (select *
              from (select
                      t.LOAN_CONTRACT_NUMBER,
                      t.BP_CODE,
                      t.BP_NAME,
                      t.LOAN_CONTRACT_ID,

                      t.loan_date_from,
                      t.MATURITY_MONTH,
                      SUM(t.principal) total_principal
                    from
                      (select
                         t1.LOAN_CONTRACT_NUMBER,
                         t1.LOAN_CONTRACT_ID,

                      t1.loan_date_from,
                         hbm.BP_CODE,
                         hbm.BP_NAME,
                         extract(MONTH from t3.MATURITY_DATE) MATURITY_MONTH,
                         t3.principal
                       from tre_loan_contract t1,
                         tre_loan_con_repayment_plan t3,
                         HLS_BP_MASTER hbm
                       #WHERE_CLAUSE#
                       ) t
                    group by t.MATURITY_MONTH, t.LOAN_CONTRACT_NUMBER, t.BP_CODE, t.BP_NAME,t.LOAN_CONTRACT_ID,

                      t.loan_date_from)
              pivot (
                sum(total_principal)
              for MATURITY_MONTH
              in (1 as month1, 2 as month2, 3 as month3, 4 as month4, 5 as month5, 6 as month6, 7 as month7, 8 as month8, 9 as month9, 10 as month10, 11 as month11, 12 as month12) ))
select
  LOAN_CONTRACT_NUMBER,
  BP_CODE,
  LOAN_CONTRACT_ID,
  loan_date_from,
  BP_NAME,
  nvl(MONTH1, 0)  MONTH1,
  nvl(MONTH2, 0)  MONTH2,
  nvl(MONTH3, 0)  MONTH3,
  nvl(MONTH4, 0)  MONTH4,
  nvl(MONTH5, 0)  MONTH5,
  nvl(MONTH6, 0)  MONTH6,
  nvl(MONTH7, 0)  MONTH7,
  nvl(MONTH8, 0)  MONTH8,
  nvl(MONTH9, 0)  MONTH9,
  nvl(MONTH10, 0) MONTH10,
  nvl(MONTH11, 0) MONTH11,
  nvl(MONTH12, 0) MONTH12
from tmp1

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>

    <bm:query-fields>

        <bm:query-field name="loan_contract_number" queryExpression="t1.loan_contract_number like ${@loan_contract_number}"/>
        <bm:query-field name="bp_name" queryExpression="hbm.bp_name like ${@bp_name}"/>
        <bm:query-field name="bp_code" queryExpression="hbm.bp_code like ${@bp_code}"/>
        <bm:query-field name="loan_date_from_f" queryExpression="t1.loan_date_from &gt;= to_date(${@loan_date_from_f},'yyyy-mm-dd')" />
        <bm:query-field name="loan_date_from_t" queryExpression="t1.loan_date_from &lt;= to_date(${@loan_date_from_t},'yyyy-mm-dd')" />
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter expression="t3.LOAN_CONTRACT_ID = t1.LOAN_CONTRACT_ID and t1.LOAN_CON_BP = hbm.BP_ID and exists(select 1 from  tre_loan_con_withdrawal_plan t2 where t1.LOAN_CONTRACT_ID = t2.LOAN_CONTRACT_ID and t2.WIDTHDRAWAL_WFL_STATUS = &apos;CONFIRM&apos;)"/>
        <bm:data-filter name="year" expression="to_char(t3.MATURITY_DATE,&apos;yyyy&apos;) = ${@year}"/>
    </bm:data-filters>
</bm:model>
