<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng  
    $Date: 2017-5-25 下午4:22:31  
    $Revision: 1.0  
    $Purpose: 前十大客户租赁业务情况表
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.*,
                    round(t1.amount / t1.sum_amount,4)*100 percentage,
                    '正常' five_classes_code
                FROM
                    (SELECT
                        t.*,
                        (SELECT
                            SUM(amount)
                        FROM
                            (SELECT
                                t.*
                            FROM
                                (SELECT
                                    ROUND(hn_report_pkg.get_due_amount_balance(p_bp_id => hbm.bp_id, p_due_date_to => to_date(${@due_date_to},'yyyy-mm-dd')) / 100000000, 2) amount
                                FROM
                                    hls_bp_master hbm
                                WHERE
                                    EXISTS
                                    (SELECT
                                        1
                                    FROM
                                        hls_bp_master_role r
                                    WHERE
                                        r.bp_id        = hbm.bp_id AND
                                        r.enabled_flag = 'Y' AND
                                        r.bp_category  = 'TENANT'
                                    )
                                ) t
                            ORDER BY
                                amount DESC nulls last
                            ) t1
                        WHERE
                            rownum < 11
                        ) sum_amount
                    FROM
                        (SELECT
                            ROUND(hn_report_pkg.get_due_amount_balance(p_bp_id => hbm.bp_id, p_due_date_to => to_date(${@due_date_to},'yyyy-mm-dd')) / 100000000, 2) amount,
                            (SELECT
                                v.code_value_name AS value_name
                            FROM
                                sys_code_values_v v
                            WHERE
                                v.code                         = 'WITHIN_GROUP' AND
                                v.code_enabled_flag            = 'Y' AND
                                v.code_value_enabled_flag      = 'Y' AND
                                hbm.hn_industry_classification = 'WITHIN_GROUP' AND
                                hbm.binary_classification      = v.code_value
                            UNION
                            SELECT
                                v.code_value_name AS value_name
                            FROM
                                sys_code_values_v v
                            WHERE
                                v.code                         = 'MARKETIZATION' AND
                                v.code_enabled_flag            = 'Y' AND
                                v.code_value_enabled_flag      = 'Y' AND
                                hbm.hn_industry_classification = 'MARKETIZATION' AND
                                hbm.binary_classification      = v.code_value
                            ) binary_classification_n,
                            (SELECT
                                v.code_value_name AS value_name
                            FROM
                                sys_code_values_v v
                            WHERE
                                v.code                    = 'HN_INDUSTRY_CLASSIFICATION' AND
                                v.code_enabled_flag       = 'Y' AND
                                v.code_value_enabled_flag = 'Y' AND
                                v.code_value              = hbm.hn_industry_classification
                            ) hn_industry_classification_n,
                            hbm.*
                        FROM
                            hls_bp_master hbm
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                hls_bp_master_role r
                            WHERE
                                r.bp_id        = hbm.bp_id AND
                                r.enabled_flag = 'Y' AND
                                r.bp_category  = 'TENANT'
                            )
                        ) t
                    ORDER BY
                        amount DESC nulls last
                    ) t1
                WHERE
                    rownum < 11
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="due_date_to"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
