<?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
                    fp.province_code area_id,
                    NULL parent_area_id,
                    'PROVINCE' area_type,
                    fp.description area_desc,
                    (SELECT
                        a.ebank_num
                    FROM
                        csh_to_ebank_area a
                    WHERE
                        a.province_id = fp.province_id AND
                        a.city_id    IS NULL AND
                        a.ebank_id    = ${@ebank_id}
                    ) ebank_num,
                    (SELECT
                        a.notes
                    FROM
                        csh_to_ebank_area a
                    WHERE
                        a.province_id = fp.province_id AND
                        a.city_id    IS NULL AND
                        a.ebank_id    = ${@ebank_id}
                    ) notes,
                    ${@ebank_spd_flag} ebank_spd_flag
                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,
                    (SELECT
                        b.ebank_num
                    FROM
                        csh_to_ebank_area b
                    WHERE
                        b.city_id  = fc.city_id AND
                        b.ebank_id = ${@ebank_id}
                    )ebank_num,
                    (SELECT
                        b.notes
                    FROM
                        csh_to_ebank_area b
                    WHERE
                        b.city_id  = fc.city_id AND
                        b.ebank_id = ${@ebank_id}
                    )notes,
                    ${@ebank_spd_flag} ebank_spd_flag
                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>
