<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng  
    $Date: 2017-5-26 上午11:05:33  
    $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 v.province_name,
       ROUND(sum_receivable_balance_1/100000000,2) sum_receivable_balance_1,
                    ROUND(sum_receivable_balance_2/100000000,2) sum_receivable_balance_2,
                    ROUND(sum_receivable_balance_1 / sum_receivable_balance_3,4)*100 within_group,
                    ROUND(sum_receivable_balance_2 / sum_receivable_balance_4,4)*100 marketization
  from (select province_name,
               (select sum(receivable_balance)
                  from (SELECT 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')) receivable_balance,
                               nvl((SELECT v.province_name
                                     FROM hls_bp_master_address_v v
                                    WHERE v.address_type = 'COMPANY_ADDRESS'
                                      AND v.enabled_flag = 'Y'
                                      AND v.bp_id = hbm.bp_id
                                      AND rownum = 1),
                                   '其他') province_name,
                               hbm.hn_industry_classification
                          FROM hls_bp_master hbm) t1
              
                 where t1.province_name = pn.province_name
                   and t1.hn_industry_classification = 'MARKETIZATION') sum_receivable_balance_1,
               (select sum(receivable_balance)
                  from (SELECT 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')) receivable_balance,
                               nvl((SELECT v.province_name
                                     FROM hls_bp_master_address_v v
                                    WHERE v.address_type = 'COMPANY_ADDRESS'
                                      AND v.enabled_flag = 'Y'
                                      AND v.bp_id = hbm.bp_id
                                      AND rownum = 1),
                                   '其他') province_name,
                               hbm.hn_industry_classification
                          FROM hls_bp_master hbm) t1
        
                 where t1.province_name = pn.province_name
                   and t1.hn_industry_classification = 'WITHIN_GROUP') sum_receivable_balance_2,
                    (select sum(receivable_balance)
                  from (SELECT 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')) receivable_balance,
                               nvl((SELECT v.province_name
                                     FROM hls_bp_master_address_v v
                                    WHERE v.address_type = 'COMPANY_ADDRESS'
                                      AND v.enabled_flag = 'Y'
                                      AND v.bp_id = hbm.bp_id
                                      AND rownum = 1),
                                   '其他') province_name,
                               hbm.hn_industry_classification
                          FROM hls_bp_master hbm) t1
              
                 where  t1.hn_industry_classification = 'MARKETIZATION') sum_receivable_balance_3,
                   
                    (select sum(receivable_balance)
                  from (SELECT 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')) receivable_balance,
                               nvl((SELECT v.province_name
                                     FROM hls_bp_master_address_v v
                                    WHERE v.address_type = 'COMPANY_ADDRESS'
                                      AND v.enabled_flag = 'Y'
                                      AND v.bp_id = hbm.bp_id
                                      AND rownum = 1),
                                   '其他') province_name,
                               hbm.hn_industry_classification
                          FROM hls_bp_master hbm) t1
              
                 where  t1.hn_industry_classification = 'WITHIN_GROUP') sum_receivable_balance_4
          from (select nvl(province_name, '其他') province_name
                  from hls_bp_master_address_v a
                 WHERE a.enabled_flag = 'Y'
                   AND a.address_type = 'COMPANY_ADDRESS') pn
         GROUP BY pn.province_name) v

            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="due_date_to"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
