<?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 ttt.loan_con_bp_name,ttt.loan_contract_number,ttt.dates_month,ttt.loan_contract_id,ttt.loan_date_from,
                 sum(unearned_finance_income) total_unearned_finance_income
            from (SELECT tt.loan_con_bp_name,
                         tt.loan_contract_number,
                         tt.dates,
                         tt.dates_month,
                          tt.loan_contract_id,
                         tt.loan_date_from,
                         round(interest * guarantee_days /
                               (tt.next_maturity_date - last_maturity_date),
                               2) 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,
                                 hls_monthend_pkg.get_last_overdue_date(p_internal_period_num => tt2.dates,
                                                                        p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                        p_cf_item             => 900) AS last_maturity_date,
                                 hls_monthend_pkg.get_first_overdue_date(p_internal_period_num => tt2.dates,
                                                                         p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                         p_cf_item             => 900) AS next_maturity_date,
                                 hls_monthend_pkg.get_first_overdue_amount(p_internal_period_num => tt2.dates,
                                                                           p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                           p_cf_item             => 900) interest,
                                 (add_months(to_date(tt2.dates, 'yyyymm'), 1) - 1 -
                                 hls_monthend_pkg.get_last_overdue_date(p_internal_period_num => tt2.dates,
                                                                         p_widthdrawal_plan_id => t.widthdrawal_plan_id,
                                                                         p_cf_item             => 900) + 1) guarantee_days,
                                 lc.loan_contract_id,
                                 lc.loan_contract_number,
                                 lc.loan_con_bp,
                                 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 (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             => 900)) tt) ttt
                                                                       #WHERE_CLAUSE#
           group by loan_contract_id,loan_contract_number, loan_con_bp_name, dates_month,loan_date_from

          ) pivot(sum(total_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_id,
        LOAN_CONTRACT_NUMBER,
       loan_con_bp_name,
       loan_date_from,
       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="ttt.loan_date_from &gt;= to_date(${@loan_date_from_f},'yyyy-mm-dd')" />
        <bm:query-field name="loan_date_from_t" queryExpression="ttt.loan_date_from &lt;= to_date(${@loan_date_from_t},'yyyy-mm-dd')" />
        <bm:query-field name="loan_contract_number" queryExpression="ttt.loan_contract_number like ${@loan_contract_number}"/>
    </bm:query-fields>
</bm:model>