con_district_area.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hand  
    $Date: 2016-6-23 下午1:02:25  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_DISTRICT_AREA" needAccessControl="false">
    <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,
                    DECODE(
                    (SELECT
                        COUNT(*) FROM CON_DISTRICT_AREA ar WHERE ar.province_id = fp.province_id AND
                        ar.group_id                                             =${/parameter/@group_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,
                    DECODE(
                    (SELECT
                        COUNT(*) FROM CON_DISTRICT_AREA ar WHERE ar.province_id = fp.province_id AND
                        ar.city_id                                                      = fc.city_id AND
                        ar.group_id                                             =${/parameter/@group_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>