<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.*,
                    decode(average_outstanding_principal,0,0,ROUND((t1.loan_payment_interest/average_outstanding_principal)/(to_date(${@due_date_to},'yyyy-mm-dd')-to_date(${@due_date_from},'yyyy-mm-dd')+1)*360,6))int_rate
                FROM
                    (SELECT
                        v.code,
                        v.code_value,
                        v.code_name,
                        v.code_value_name,
                        (
                            CASE
                                WHEN v.code_value = 'LESS_THAN_ONE_YEAR'
                                THEN hn_report_pkg.get_tre_loan_remain_prin(p_due_date_to =>to_date(${@due_date_from},'yyyy-mm-dd'), p_year_period_from => NULL, p_year_period_to => 1)
                                ELSE hn_report_pkg.get_tre_loan_remain_prin(p_due_date_to =>to_date(${@due_date_from},'yyyy-mm-dd'), p_year_period_from => 1, p_year_period_to =>NULL)
                            END) beginning_balance,
                        (
                            CASE
                                WHEN v.code_value = 'LESS_THAN_ONE_YEAR'
                                THEN hn_report_pkg.get_tre_loan_remain_prin(p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from => NULL, p_year_period_to => 1)
                                ELSE hn_report_pkg.get_tre_loan_remain_prin(p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from => 1, p_year_period_to =>NULL)
                            END) ending_balance,
                        (
                            CASE
                                WHEN v.code_value = 'LESS_THAN_ONE_YEAR'
                                THEN hn_report_pkg.get_tre_loan_avg_remain_prin(p_due_date_from =>to_date(${@due_date_from},'yyyy-mm-dd'), p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from => NULL, p_year_period_to => 1)
                                ELSE hn_report_pkg.get_tre_loan_avg_remain_prin(p_due_date_from =>to_date(${@due_date_from},'yyyy-mm-dd'), p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from => 1, p_year_period_to => NULL)
                            END) average_outstanding_principal,
                        (
                            CASE
                                WHEN v.code_value = 'LESS_THAN_ONE_YEAR'
                                THEN hn_report_pkg.get_tre_loan_payment_interest(p_due_date_from =>to_date(${@due_date_from},'yyyy-mm-dd'), p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from =>NULL, p_year_period_to =>1)
                                ELSE hn_report_pkg.get_tre_loan_payment_interest(p_due_date_from =>to_date(${@due_date_from},'yyyy-mm-dd'), p_due_date_to =>to_date(${@due_date_to},'yyyy-mm-dd'), p_year_period_from =>1, p_year_period_to =>NULL)
                            END) loan_payment_interest
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code                    = 'HN_LOAN_TYPE' AND
                        v.code_enabled_flag       = 'Y' AND
                        v.code_value_enabled_flag = 'Y'
                    ) t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <!--<bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.project_num &gt;0"/>
    </bm:data-filters>
    <bm:query-fields>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.code=${@hn_industry_classification}"/>
        <bm:query-field name="binary_classification" queryExpression="t1.code_value=${@binary_classification}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
    </bm:query-fields>-->
</bm:model>
