<?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>