<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: jack  
    $Date: 2014-6-6 上午10:01:07  
    $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.*,rownum from(
SELECT a.project_id,
               a.bp_id,
               b.bp_name,
               b.id_card_no,
               (SELECT c.relation_type_n FROM Hls_Bp_Master_Relation_Lv c,prj_project_bp d where c.bp_id=b.bp_id and d.project_id=a.project_id and  c.related_bp_id=d.bp_id and d.bp_category='TENANT' ) ralation_name,
               (SELECT c.description_text
                  FROM sys_code_values  a,
                       sys_codes        b,
                       fnd_descriptions c
                 WHERE b.code = 'HLS211_GENDER'
                       AND a.code_id = b.code_id
                       AND a.code_value_name_id = c.description_id
                       AND c.language = 'ZHS'
                       AND a.code_value = b.gender) gender,
               b.date_of_birth,
               (SELECT d.print_name || c.print_name || b.print_name || e.print_name || a.address address_union
                  FROM fnd_country  d,
                       fnd_city     b,
                       fnd_province c,
                       fnd_district e
                 WHERE d.country_id(+) = a.country_id
                       AND c.province_id(+) = a.province_id
                       AND b.city_id(+) = a.city_id
                       AND e.district_id(+) = a.district_id) address,
               b.annual_income,
               b.bp_name_sp,
               b.id_card_no_sp,
               a.credit_info,
               (SELECT c.phone
                  FROM hls_bp_master_address_v c
                 WHERE c.address_type = 'HOUSE_ADDRESS'
                       AND c.bp_id = a.bp_id) cell_phone,
               (SELECT c.country_name || c.province_name || c.city_name || c.district_name || c.street || c.address
                  FROM hls_bp_master_address_v c
                 WHERE c.address_type = 'ID_ADDRESS'
                       AND c.bp_id = a.bp_id) contact_address,
               (SELECT c.country_name || c.province_name || c.city_name || c.district_name || c.street || c.address
                  FROM hls_bp_master_address_v c
                 WHERE c.address_type = 'HOUSE_ADDRESS'
                       AND c.bp_id = a.bp_id) home_address
          FROM prj_project_act_ctrler_hd a,
               hls_bp_master             b
        
         WHERE a.bp_id = b.bp_id ) t1 #WHERE_CLAUSE#]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
