<?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) || '-' ||
       ((select count(1)
           from prj_project_bp ppbo
          where ppbo.project_id = ppb.project_id
            and ppbo.bp_category = 'GUARANTOR'
            --and ppbo.bp_type = 'GUARANTOR'
            and ppbo.bp_class = 'ORG') + (rank() over(partition by ppb.bp_type order by ppb.bp_id))) prj_guarantor_con_number, --担保合同编号
       ppb.bp_name, --担保人名称
       (select hbm.id_card_no
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id
         and hbm.id_type='ID_CARD'
         ) guarantor_id_card_no, --担保人身份证
       (select hbm.bp_name_sp
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id) guarantor_bp_name_sp, --担保人配偶名称
       (select hbm.id_card_no_sp
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id
         and hbm.id_type_sp ='ID_CARD'
         ) guarantor_id_card_no_sp, --担保人配偶身份证
       (select hbm.spouse_phone
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id) guarantor_spouse_phone, --担保人配偶电话
       (select hbma.address
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') guarantor_house_address, --担保人住所（家庭地址）
       (select hbma.zipcode
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') guarantor_zipcode, --担保人邮编（家庭地址邮编）
       (select hbma.phone
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') guarantor_phone, --担保人家庭地址电话
       (select hbma.fax
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') guarantor_fax --担保人家庭地址传真
  from prj_project_bp ppb
 where ppb.bp_category = 'GUARANTOR'
   --and ppb.bp_type = 'GUARANTOR'
   and ppb.bp_class = 'NP'
   and ppb.project_id=${/parameter/@project_id}
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
