<?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 scvv.code_value,
			       scvv.code_value_name, --行业分类
			       (SELECT sum(bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}) principal_amount, --投资总额
			        TO_CHAR(ROUND(((SELECT sum(bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id})/
			       (SELECT sum(bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.session_id = ${/session/@session_id}))*100,2),'FM90.00')|| '%' principal_amount_percent,    --占比
			       (SELECT sum(bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}) surplus_principal_amount,         --剩余本金
			       TO_CHAR(ROUND((SELECT sum(bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}) /
			       (SELECT sum(bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.session_id = ${/session/@session_id})*100,2),'FM90.00')|| '%' surplus_principal_percent,  --剩余本金占比
			       TO_CHAR(ROUND(((SELECT sum(bhrd.ref_n08 * bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id} group by bhrd.ref_v18) /
			       (SELECT sum(bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}))*100,6),'FM90.00')|| '%' guarantee_irr_avg,                  --出租人加权平均irr(投放本金)
			       TO_CHAR(ROUND(((SELECT sum(bhrd.ref_n05 * bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id} group by bhrd.ref_v18) /
			       (SELECT sum(bhrd.ref_n04)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}))*100,6),'FM90.00')|| '%' leese_irr_avg,                      --承租人加权平均irr(投放本金)
			       TO_CHAR(ROUND(((SELECT sum(bhrd.ref_n08 * bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id} group by bhrd.ref_v18) /
			       (SELECT sum(bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}))*100,6),'FM90.00')|| '%' guarantee_irr,                      --出租人加权平均irr(剩余本金)    
			       TO_CHAR(ROUND(((SELECT sum(bhrd.ref_n05 * bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id} group by bhrd.ref_v18) /
			       (SELECT sum(bhrd.ref_n06)
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.ref_v18 = scvv.code_value
			           and bhrd.session_id = ${/session/@session_id}))*100,6),'FM90.00')|| '%' leese_irr                          --承租人加权平均irr(剩余本金)    
			  FROM sys_code_values_v scvv
			 WHERE scvv.code_value in
			       (select bhrd.ref_v18
			          FROM bgfl_hls_report_data bhrd
			         WHERE bhrd.function_code = 'HN5040'
			           and bhrd.ref_v03 = 'WITHIN_GROUP'
			           and bhrd.session_id = ${/session/@session_id}) and scvv.code IN ('WITHIN_GROUP', 'MARKETIZATION')
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
