<?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 * from(
            select hcdi.contract_id,
            	   (select cc.contract_number from con_contract cc where cc.contract_id = hcdi.contract_id) contract_number,
			       (select pp.virtual_con_number from con_contract cc,prj_project pp where cc.contract_id = hcdi.contract_id and pp.project_id = cc.project_id) virtual_con_number,
			       (select cc.contract_name from con_contract cc where cc.contract_id = hcdi.contract_id) contract_name,
			       (SELECT NVL(ccc.due_amount, 0)
			          FROM con_contract_cashflow ccc
			         WHERE hcdi.contract_id = ccc.contract_id
			           AND ccc.times = 0
			           AND ccc.cf_item = 0) principal_amount, --本金总额
			       (SELECT hbm.bp_name
			          FROM hls_bp_master hbm,con_contract cc
			         WHERE hbm.bp_id = cc.bp_id_tenant and cc.contract_id = hcdi.contract_id) lessee_name, --主承租人 
			       (select vc.name 
			          from exp_employees vc,con_contract cc,prj_project pp 
			         where vc.employee_id = pp.employee_id and pp.project_id = cc.project_id and cc.contract_id = hcdi.contract_id) manager, --项目经理
			       (select mc.unit_name 
			          from exp_employee_assigns_v mc,con_contract cc,prj_project pp
			         where mc.employee_id = pp.employee_id and mc.primary_position_flag = 'Y' and mc.enabled_flag='Y' and pp.project_id = cc.project_id and cc.contract_id = hcdi.contract_id) department, --业务部门 
			       hcdi.deliver_type, --寄送类型
			       hcdi.bp_id,
			       (select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = hcdi.bp_id) bp_id_n,--寄送对象
			       (select hbt.description from hls_bp_master hbm,hls_bp_type hbt
			         where hbm.bp_id = hcdi.bp_id and hbt.bp_type = hbm.bp_type) bp_type_n,--寄送对象角色
			       hcdi.address_type,             --地址类型
			       hcdi.province_id,
			       (select fp.description
			          from fnd_province fp
			         where fp.province_id = hcdi.province_id
			           and fp.enabled_flag = 'Y') province_id_n,
			       hcdi.city_id,
			       (select fc.description
			          from fnd_city fc
			         where fc.city_id = hcdi.city_id
			           and fc.enabled_flag = 'Y') city_id_n,
			       hcdi.district_id,
			       (select fd.description
			          from fnd_district fd
			         where fd.district_id = hcdi.district_id
			           and fd.enabled_flag = 'Y') district_id_n,
			       hcdi.address,           --详细地址
			       hcdi.zipcode,           --邮编
			       hcdi.contact_person,    --联系人
			       hcdi.cell_phone,        --联系电话
			       hcdi.email,             --邮箱
			       hcdi.deliver_id
			  from hn_con_deliver_info hcdi
			  ) t1 #WHERE_CLAUSE# order by t1.contract_id
			  ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="lessee_name" queryExpression="t1.lessee_name like &apos;%&apos;||${@lessee_name}||&apos;%&apos;"/>
        <bm:query-field name="manager" queryExpression="t1.manager like &apos;%&apos;||${@manager}||&apos;%&apos;"/>
        <bm:query-field name="deliver_type_n" queryExpression="t1.deliver_type like &apos;%&apos;||${@deliver_type}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_n" queryExpression="t1.bp_id_n like &apos;%&apos;||${@bp_id_n}||&apos;%&apos;"/>
        <bm:query-field name="bp_type_n" queryExpression="t1.bp_type_n like &apos;%&apos;||${@bp_type_n}||&apos;%&apos;"/>
        <bm:query-field name="address_type_n" queryExpression="t1.address_type like &apos;%&apos;||${@address_type}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
