<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            	SELECT
                    '-1' area_id,
                    NULL parent_area_id,
                    'PROVINCE' area_type,
                    '中国' area_desc,
                    null ebank_province,
                    null  notes,
                    'N' checked
                FROM
                    dual
                UNION ALL
                SELECT
                    fp.province_code area_id,
                    '-1' parent_area_id,
                    'PROVINCE' area_type,
                    fp.description area_desc,
                    fp.ebank_province ebank_province,
                    fp.notes notes,
                    DECODE(
                    (SELECT
                        COUNT(*) FROM HLS_PRODUCT_TO_GRANT_AREA ar WHERE ar.province_id = fp.province_id AND
                        ar.product_grant_id                                             =${/parameter/@product_grant_id}
                    ),0,'N','Y') checked
                FROM
                    fnd_province fp
                WHERE
                    fp.enabled_flag = 'Y'
                UNION ALL
                SELECT
                    fc.city_code area_id,
                    fp.province_code parent_area_id,
                    'CITY' area_type,
                    fc.description area_desc,
                    fc.ebank_city ebank_city,
                    fc.notes notes,
                    DECODE(
                    (SELECT
                        COUNT(*) FROM HLS_PRODUCT_TO_GRANT_AREA ar WHERE ar.province_id = fp.province_id AND
                        ar.city_id                                                      = fc.city_id AND
                        ar.product_grant_id                                             =${/parameter/@product_grant_id}
                    ),0,'N','Y') checked
                FROM
                    fnd_city fc,
                    fnd_province fp
                WHERE
                    fc.province_id    = fp.province_id AND
                    fp.enabled_flag   = 'Y' AND
                    fc.enabled_flag   = 'Y' AND
                    fc.city_code NOT IN('110000','120000','500000','310000')
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
