<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-7-10 下午7:49:36  
    $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 ppb.project_id, --合同ID
       ppb.bp_id, --担保人ID
       (select pp.virtual_con_number
          from prj_project pp
         where pp.project_id = ppb.project_id) virtual_con_number, --融资租赁合同编号
       (select ppbm.bp_name
          from prj_project_bp ppbm
         where ppbm.project_id = ppb.project_id
           and ppbm.bp_category = 'TENANT'
           and ppbm.bp_type = 'TENANT') tenant_bp_name,--主承租人名称
       (select ppbm.bp_name
          from prj_project_bp ppbm
         where ppbm.project_id = ppb.project_id
           and ppbm.bp_category = 'TENANT'
           and ppbm.bp_type = 'TENANT_SEC') tenant_sec_bp_name,--次承租人名称
       (select substr(pp.virtual_con_number, 0, 5) || '-G' ||
               substr(pp.virtual_con_number, 6)
          from prj_project pp
         where pp.project_id = ppb.project_id) || '-' || (rank() over(partition by ppb.bp_type order by ppb.bp_id))    prj_guarantor_con_number, --担保合同编号
       ppb.bp_name, --担保人名称
       (select hbmci.contact_person
          from hls_bp_master_contact_info hbmci
         where hbmci.bp_id = ppb.bp_id
           and hbmci.position = 'LP') guarantor_legal_person, --担保人法定代表人
       (select hbma.address
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'DOC_SENT_ADDRESS') guarantor_doc_sent_address, --担保人住所（邮寄地址）
       (select hbma.zipcode
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'DOC_SENT_ADDRESS') guarantor_zipcode, --担保人邮编（邮寄地址邮编）
       (select hbmci.phone
          from hls_bp_master_contact_info hbmci
         where hbmci.bp_id = ppb.bp_id
           and hbmci.position = 'LP') guarantor_phone, --担保人法定代表人电话
       (select hbmci.fax
          from hls_bp_master_contact_info hbmci
         where hbmci.bp_id = ppb.bp_id
           and hbmci.position = 'LP') guarantor_fax --担保人法定代表人传真
  from prj_project_bp ppb
 where ppb.bp_category = 'GUARANTOR'
   --and ppb.bp_type = 'GUARANTOR'
   and ppb.bp_class = 'ORG'
   and ppb.project_id=${/parameter/@project_id}
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
