<?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
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03       > 3
                    ) long_term_sum_principal,
                    to_char(ROUND((
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03       > 3
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )) * 100, 2),'FM90.00')
                    || '%' long_term_sum_prin_percent,
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03      <= 3
                    ) short_term_sum_principal,
                    to_char(ROUND((
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03      <= 3
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )) * 100, 2),'FM90.00')
                    || '%' short_term_sum_prin_percent,
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03       > 3
                    ) long_term_rec_principal,
                    to_char(ROUND((
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03       > 3
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )) * 100, 2),'FM90.00')
                    || '%' long_term_rec_prin_percent,
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03      <= 3
                    ) short_term_rec_principal,
                    to_char(ROUND((
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id} AND
                        bhrd.ref_n03      <= 3
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )) * 100, 2),'FM90.00')
                    || '%' short_term_rec_prin_percent,
                    ROUND((
                    (SELECT
                        SUM(bhrd.ref_n02 * bhrd.ref_n03)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n02)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )), 2) sum_principal_term_avg,
                    ROUND((
                    (SELECT
                        SUM(bhrd.ref_n05 * bhrd.ref_n03)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    ) /
                    (SELECT
                        SUM(bhrd.ref_n05)
                    FROM
                        bgfl_hls_report_data bhrd
                    WHERE
                        bhrd.function_code = 'HN2020' AND
                        bhrd.session_id    = ${/session/@session_id}
                    )), 2) rec_principal_term_avg
                FROM
                    dual
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="long_term_sum_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="short_term_sum_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="long_term_rec_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="short_term_rec_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="sum_principal_term_avg" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="rec_principal_term_avg" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="long_term_sum_prin_percent"/>
        <bm:field name="short_term_sum_prin_percent"/>
        <bm:field name="long_term_rec_prin_percent"/>
        <bm:field name="short_term_rec_prin_percent"/>
    </bm:fields>
</bm:model>
