<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng7543
$Date: 2019/3/7 17:08
$Revision: 1.0 
$Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" >
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[

with tmp1 as
 (select *
    from (SELECT tt.loan_con_bp_name,tt.loan_contract_number,tt.dates_month,tt.loan_contract_id,tt.loan_date_from,
sum(tt.unearned_finance_income) sum_unearned_finance_income
  FROM (SELECT

         (SELECT m.bp_name
            FROM hls_bp_master m
           WHERE m.bp_id = lc.loan_con_bp) loan_con_bp_name,
         t.withdrawal_number,
         (SELECT SUM(i.unearned_finance_income)
            FROM con_unearned_finance_income i,
                 tre_loan_con_repayment_plan rp
           WHERE i.cashflow_id = rp.repayment_plan_id
             AND i.contract_id = rp.withdrawal_id
             AND rp.withdrawal_id = t.widthdrawal_plan_id
             AND i.cf_item = 901
             AND i.internal_period_num = tt2.dates) AS unearned_finance_income,
         lc.loan_contract_number,
         lc.loan_contract_id,
         lc.loan_date_from,
         tt2.dates,
         to_number(replace(tt2.dates, '2018', '')) dates_month
          FROM tre_loan_con_withdrawal_plan t,
               tre_loan_contract lc,
               tre_loan_con_repayment_plan crp,
               (select to_char(last_day(add_months(to_date('2018' || '12',
                                                           'yyyymm'),
                                                   -level)) + 1,
                               'yyyymm') dates
                  from dual
                connect by level <= 12) tt2
         WHERE t.widthdrawal_wfl_status = 'CONFIRM'
           AND t.loan_contract_id = lc.loan_contract_id
           AND lc.change_money > 0
           AND (EXISTS
                (SELECT 1
                   FROM tre_loan_con_repayment_plan  rp,
                        tre_loan_con_repayment_dtl   rd,
                        tre_loan_con_withdrawal_plan wp
                  WHERE rp.repayment_plan_id = rd.repayment_plan_id
                    AND rp.rental - nvl(rd.repayment_amount, 0) > 0
                    AND wp.widthdrawal_plan_id = rp.withdrawal_id
                    AND to_char(wp.widthdrawal_date, 'yyyymm') <= tt2.dates
                    AND rp.withdrawal_id = t.widthdrawal_plan_id) OR EXISTS
                (SELECT 1
                   FROM tre_loan_con_repayment_plan  rp,
                        tre_loan_con_repayment_dtl   rd,
                        tre_loan_con_withdrawal_plan wp
                  WHERE rp.repayment_plan_id = rd.repayment_plan_id
                    AND rp.rental - nvl(rd.repayment_amount, 0) = 0
                    AND wp.widthdrawal_plan_id = rp.withdrawal_id
                    AND to_char(wp.widthdrawal_date, 'yyyymm') = tt2.dates
                    AND rp.withdrawal_id = t.widthdrawal_plan_id) OR EXISTS
                (SELECT 1
                   FROM tre_loan_con_repayment_plan  rp,
                        tre_loan_con_withdrawal_plan wp
                  WHERE wp.widthdrawal_plan_id = rp.withdrawal_id
                    AND to_char(wp.widthdrawal_date, 'yyyymm') <= tt2.dates
                    AND rp.withdrawal_id = t.widthdrawal_plan_id))
           AND crp.withdrawal_id = t.widthdrawal_plan_id
           AND crp.repayment_plan_id =
               hls_monthend_pkg.get_first_overdue_id(p_internal_period_num => tt2.dates,
                                                     p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                     p_cf_item             => 901)

        ) tt
        #WHERE_CLAUSE#
        group by tt.loan_contract_id,tt.loan_con_bp_name,tt.loan_contract_number,tt.dates_month,tt.loan_date_from

          ) pivot(sum(sum_unearned_finance_income) for dates_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,
       loan_con_bp_name,
       loan_date_from,
       loan_contract_id,
       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
   order by loan_con_bp_name,LOAN_CONTRACT_NUMBER

]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="loan_date_from_f" queryExpression="tt.loan_date_from &gt;= to_date(${@loan_date_from_f},'yyyy-mm-dd')" />
        <bm:query-field name="loan_date_from_t" queryExpression="tt.loan_date_from &lt;= to_date(${@loan_date_from_t},'yyyy-mm-dd')" />
    <bm:query-field name="loan_contract_number" queryExpression="tt.loan_contract_number like ${@loan_contract_number}"/>
    </bm:query-fields>
</bm:model>