<?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,null,0,ROUND(period_interest_income/average_outstanding_principal*360/(to_date(${@calc_date_to},'YYYY-MM-DD') -to_date(${@calc_date_from},'YYYY-MM-DD')+1),4)) int_rate
                FROM
                    (SELECT
                        v.code,
                        v.code_value,
                        v.code_name,
                        v.code_value_name,
                        ROUND(sdic_report_pkg.get_remain_prin_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date_from},'YYYY-MM-DD')), 2) beginning_balance,
                        ROUND(sdic_report_pkg.get_remain_prin_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) ending_balance,
                        ROUND(sdic_report_pkg.get_avg_outstanding_principal(p_binary_class => v.code_value, p_calc_date_from => to_date(${@calc_date_from},'YYYY-MM-DD'), p_calc_date_to => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) average_outstanding_principal,
                        ROUND(sdic_report_pkg.get_period_interest_income(p_binary_class => v.code_value, p_calc_date_from => to_date(${@calc_date_from},'YYYY-MM-DD'), p_calc_date_to => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) period_interest_income
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code                   IN ('WITHIN_GROUP', 'MARKETIZATION') AND
                        v.code_enabled_flag       = 'Y' AND
                        v.code_value_enabled_flag = 'Y' AND
                        ${@dimension}             ='BINARY_CLASSIFICATION'
                    UNION
                    SELECT
                        tt.code,
                        NULL code_value,
                        tt.code_name,
                        NULL code_value_name,
                        SUM(tt.beginning_balance) beginning_balance,
                        SUM(tt.ending_balance) ending_balance,
                        SUM(tt.average_outstanding_principal) average_outstanding_principal,
                        SUM(tt.period_interest_income) period_interest_income
                    FROM
                        (SELECT
                            v.code,
                            v.code_value,
                            v.code_name,
                            v.code_value_name,
                            ROUND(sdic_report_pkg.get_remain_prin_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date_from},'YYYY-MM-DD')), 2) beginning_balance,
                            ROUND(sdic_report_pkg.get_remain_prin_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) ending_balance,
                            ROUND(sdic_report_pkg.get_avg_outstanding_principal(p_binary_class => v.code_value, p_calc_date_from => to_date(${@calc_date_from},'YYYY-MM-DD'), p_calc_date_to => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) average_outstanding_principal,
                            ROUND(sdic_report_pkg.get_period_interest_income(p_binary_class => v.code_value, p_calc_date_from => to_date(${@calc_date_from},'YYYY-MM-DD'), p_calc_date_to => to_date(${@calc_date_to},'YYYY-MM-DD')), 2) period_interest_income
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                   IN ('WITHIN_GROUP', 'MARKETIZATION') AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y'
                        ) tt
                    WHERE
                        ${@dimension} ='CLASSIFICATION'
                    GROUP BY
                        tt.code,
                        tt.code_name
                    ) t1 #WHERE_CLAUSE#
            ]]></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>
