<?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 bp_code,
                        NULL bp_name,
                        sdic_report_pkg.get_prj_num_with_binary_class(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_class(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_class(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_class_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_class_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
                    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,
                        NULL code,
                        NULL code_value,
                        NULL code_name,
                        NULL code_value_name,
                        hbm.bp_code,
                        hbm.bp_name,
                        sdic_report_pkg.get_prj_num_with_bp_tenant(p_prj_industry_classification=>${@prj_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_tenant(p_prj_industry_classification=>${@prj_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_tenant(p_prj_industry_classification=>${@prj_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')), 2) prj_remain_due_amount,
                        TO_CHAR(ROUND(sdic_report_pkg.get_remain_prin_bp_tenant_p(p_prj_industry_classification=>${@prj_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_tenant_p(p_prj_industry_classification=>${@prj_industry_classification},p_bp_id => hbm.bp_id, p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) * 100, 2), 'FM990.00')
                        || '%' prj_remain_due_amount_percent
                    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'
                    ) 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>
