<?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 * FROM (
                SELECT
                    NULL bp_id,
                    v.code,
                    v.code_value,
                    v.code_name,
                    v.code_value_name, 
                    NULL project_name,
                    NULL bp_code,
                    NULL bp_name,
                    NULL group_manage_unit,
                    sdic_report_pkg.get_prj_num_with_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) project_num,
                    ROUND(sdic_report_pkg.get_remain_prin_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')), 2) prj_remain_principal,
                    ROUND(sdic_report_pkg.get_remain_due_binary_plan(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')), 2) prj_remain_due_amount,
                    TO_CHAR(ROUND(sdic_report_pkg.get_remain_prin_binary_plan_p(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) * 100, 2), 'FM990.00')
                    || '%' prj_remain_principal_percent,
                    TO_CHAR(ROUND(sdic_report_pkg.get_remain_due_binary_plan_p(p_binary_class => v.code_value, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) * 100, 2), 'FM990.00')
                    || '%' prj_remain_due_amount_percent,
                    NULL main_guarantor_name,
                    NULL guarantor_name
                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
                    hbm.bp_id,
                    ${@hn_industry_classification} code,
                    NULL code_value,
                    NULL code_name,
                    NULL code_value_name,
                    NULL project_name,
                    hbm.bp_code,
                    hbm.bp_name,
                    hbm.GROUP_MEMBERSHIP group_manage_unit,
                    sdic_report_pkg.get_prj_num_with_bp_tenant_p(p_prj_industry_classification=>${@hn_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) project_num,
                    ROUND(sdic_report_pkg.get_remain_prin_bp_plan(p_prj_industry_classification=>${@hn_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')), 2) remain_principal,
                    ROUND(sdic_report_pkg.get_remain_due_bp_plan(p_prj_industry_classification=>${@hn_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')), 2) remain_due_amount,
                    TO_CHAR(ROUND(sdic_report_pkg.get_remain_prin_bp_plan_p(p_prj_industry_classification=>${@hn_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) * 100, 2), 'FM990.00')
                    || '%' remain_principal_percent,
                    TO_CHAR(ROUND(sdic_report_pkg.get_remain_due_bp_plan_p(p_prj_industry_classification=>${@hn_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) * 100, 2), 'FM990.00')
                    || '%' remain_due_amount_percent,
                    NULL main_guarantor_name,
                    NULL guarantor_name
                FROM
                    hls_bp_master hbm
                WHERE
                    hbm.bp_id IN
                    (SELECT
                        pp.bp_id_tenant
                    FROM
                        prj_project pp
                    WHERE
                        pp.data_class  = 'NORMAL' AND
                        pp.project_id IN
                        (SELECT
                            ppt.ref_project_id
                        FROM
                            prj_project ppt,
                            con_contract cc
                        WHERE
                            ppt.project_id     = cc.project_id AND
                            ppt.ref_project_id = pp.project_id AND
                            cc.data_class      = 'NORMAL' AND
                            cc.contract_id    IN
                            (SELECT
                                cwo.contract_id
                            FROM
                                csh_write_off cwo
                            WHERE
                                cwo.write_off_type          = 'PAYMENT_DEBT' AND
                                NVL(cwo.reversed_flag, 'N') = 'N' AND
                                cwo.times                   = 0 AND
                                cwo.cf_type                 = 0 AND
                                cwo.cf_item                 = 0
                            )
                        )
                    )
                    and ${@dimension}='BP_TENANT'
                    UNION
                    select NULL bp_id,
					       pp.hn_industry_classification code,
					       NULL code_value,
					       NULL code_name,
					       NULL code_value_name,
					       pp.project_name,
					       NULL bp_code,
					       NULL bp_name,
					       NULL group_manage_unit,
					       1 project_num,
					      ROUND(((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 0 and ccc.cf_type = 0
				                     and ccc.cf_direction = 'OUTFLOW' and ccc.cf_status = 'RELEASE'  and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.principal,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD')))),2) prj_remain_principal,--本金余额
					           
					       ROUND(((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 1 and ccc.cf_type = 1
				                     and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'  and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD')))),2) prj_remain_due_amount, -- 租金余额
					           
					       to_char(round((((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 0 and ccc.cf_type = 0
				                     and ccc.cf_direction = 'OUTFLOW' and ccc.cf_status = 'RELEASE' and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.principal,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))))/
					       (select sum(am.amount) from (select ((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where ppro.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 0 and ccc.cf_type = 0
				                     and ccc.cf_direction = 'OUTFLOW' and ccc.cf_status = 'RELEASE'  and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.principal,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where ppro.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD')))) amount from prj_project ppro 
					           where ppro.data_class = 'NORMAL' and ppro.project_status = 'APPROVED' and exists
					   (select 1
			              from prj_project ppt
			             where ppt.data_class = 'VIRTUAL_CON'
			               and ppt.ref_project_id = ppro.project_id
			               and ppt.content_wfl_status = 'APPROVED'
			               and exists
			             (select 1
			                      from con_contract cc
			                     where cc.data_class = 'NORMAL'
			                       and cc.project_id = ppt.project_id
			                       and exists
			                     (select 1
			                              from csh_write_off cwo
			                             where cwo.write_off_type = 'PAYMENT_DEBT'
			                               and nvl(cwo.reversed_flag, 'N') = 'N'
			                               and cwo.contract_id = cc.contract_id
			                               and cwo.times = 0
			                               and cwo.cf_type = 0
			                               and cwo.cf_item = 0
			                               and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
			                       and not exists
			                     (select 1
			                              from con_contract_cashflow ccc
			                             where ccc.contract_id = cc.contract_id
			                               and ccc.times = cc.lease_times
			                               and ccc.cf_type = 1
			                               and ccc.cf_item = 1
			                               and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD'))))) am)) * 100,2),'FM90.90') || '%' prj_remain_principal_percent ,-- 本金余额占比
					       
					       to_char(round((((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 1 and ccc.cf_type = 1
				                     and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where pp.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))))/
					       (select sum(am.amount) from (select ((select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where ppro.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.contract_id = ccc.contract_id
				                     and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON' and ccc.cf_item = 1 and ccc.cf_type = 1
				                     and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE'  and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))) -
				                 (select nvl(sum(nvl(ccc.due_amount,0)),0) from con_contract cc,con_contract_cashflow ccc,prj_project tt 
				                   where ppro.project_id = tt.ref_project_id and tt.project_id = cc.project_id and cc.data_class = 'NORMAL' and tt.data_class = 'VIRTUAL_CON'
				                     and cc.contract_id = ccc.contract_id and ccc.cf_type = 1 and ccc.cf_item = 1 and ccc.cf_direction = 'INFLOW'
				                     and ccc.cf_status = 'RELEASE' and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD') and exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'PAYMENT_DEBT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = 0
				                                  and cwo.cf_type = 0
				                                  and cwo.cf_item = 0
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
				                          and not exists
				                        (select 1
				                                 from csh_write_off cwo
				                                where cwo.write_off_type = 'RECEIPT_CREDIT'
				                                  and nvl(cwo.reversed_flag, 'N') = 'N'
				                                  and cwo.contract_id = cc.contract_id
				                                  and cwo.times = cc.lease_times
				                                  and cwo.cf_type = 1
				                                  and cwo.cf_item = 1
				                                  and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD')))) amount from prj_project ppro 
					           where ppro.data_class = 'NORMAL' and ppro.project_status = 'APPROVED' and exists
					   (select 1
			              from prj_project ppt
			             where ppt.data_class = 'VIRTUAL_CON'
			               and ppt.ref_project_id = ppro.project_id
			               and ppt.content_wfl_status = 'APPROVED'
			               and exists
			             (select 1
			                      from con_contract cc
			                     where cc.data_class = 'NORMAL'
			                       and cc.project_id = ppt.project_id
			                       and exists
			                     (select 1
			                              from csh_write_off cwo
			                             where cwo.write_off_type = 'PAYMENT_DEBT'
			                               and nvl(cwo.reversed_flag, 'N') = 'N'
			                               and cwo.contract_id = cc.contract_id
			                               and cwo.times = 0
			                               and cwo.cf_type = 0
			                               and cwo.cf_item = 0
			                               and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
			                       and not exists
			                     (select 1
			                              from con_contract_cashflow ccc
			                             where ccc.contract_id = cc.contract_id
			                               and ccc.times = cc.lease_times
			                               and ccc.cf_type = 1
			                               and ccc.cf_item = 1
			                               and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD'))))) am)) * 100,2),'FM90.90') || '%' prj_remain_due_amount_percent, --租金余额占比
					       sdic_report_pkg.get_prj_bp_name(p_project_id  => pp.project_id,
		                                             	   p_bp_category => 'GUARANTOR',
		                                             	   p_bp_type     => 'GUARANTOR') main_guarantor_name, --主担保人
       					   sdic_report_pkg.get_prj_bp_name(p_project_id  => pp.project_id,
		                                             	   p_bp_category => 'GUARANTOR',
		                                             	   p_bp_type     => 'GUARANTOR_SEC') guarantor_name --次担保人 
					  from prj_project pp
					 where pp.data_class = 'NORMAL'
					   and pp.project_status = 'APPROVED'
					   and exists
					   (select 1
			              from prj_project ppt
			             where ppt.data_class = 'VIRTUAL_CON'
			               and ppt.ref_project_id = pp.project_id
			               and ppt.content_wfl_status = 'APPROVED'
			               and exists
			             (select 1
			                      from con_contract cc
			                     where cc.data_class = 'NORMAL'
			                       and cc.project_id = ppt.project_id
			                       and exists
			                     (select 1
			                              from csh_write_off cwo
			                             where cwo.write_off_type = 'PAYMENT_DEBT'
			                               and nvl(cwo.reversed_flag, 'N') = 'N'
			                               and cwo.contract_id = cc.contract_id
			                               and cwo.times = 0
			                               and cwo.cf_type = 0
			                               and cwo.cf_item = 0
			                               and cwo.write_off_date <= to_date(${@calc_date},'YYYY-MM-DD'))
			                       and not exists
			                     (select 1
			                              from con_contract_cashflow ccc
			                             where ccc.contract_id = cc.contract_id
			                               and ccc.times = cc.lease_times
			                               and ccc.cf_type = 1
			                               and ccc.cf_item = 1
			                               and ccc.due_date <= to_date(${@calc_date},'YYYY-MM-DD'))))
                       and ${@dimension}='PROJECT_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-field name="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
