<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-7-10 下午9:27: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,
       ppb.bp_id,
       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'
         ) pledger_id_card_no, --抵质押人身份证
       (select hbma.address
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') pledger_house_address, --抵质押人住所（家庭地址，联系地址）
       (select hbma.zipcode
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') pledger_zipcode, --抵质押人邮编（邮寄地址邮编）
       (select hbma.phone
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') pledger_phone, --担保人家庭地址电话
       (select hbma.fax
          from hls_bp_master_address hbma
         where hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'HOUSE_ADDRESS') pledger_fax, --担保人家庭地址传真
       (select hbm.bp_name_sp
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id) pledger_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'
         ) pledger_id_card_no_sp, --担保人配偶身份证
       (select hbm.spouse_phone
          from hls_bp_master hbm
         where hbm.bp_id = ppb.bp_id) pledger_spouse_phone, --担保人配偶电话
       ppm.company_name_stock, --目标公司名称
       ppm.stock_ratio, --持股比例
      to_char((ppm.value * 10000),'FM999,999,999,990.00') company_pledge_value, --认缴出资额
       (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 pp.virtual_con_number
          from prj_project pp
         where pp.project_id = ppb.project_id) virtual_con_number, --融资租赁合同编号
       (select to_char(pp.Inception_Of_Lease, 'YYYY')
          from prj_project_virtual_con_lv pp
         where pp.project_id = ppb.project_id) lease_start_date_year, --抵押期限开始日期（年）
       (select to_char(pp.Inception_Of_Lease, 'MM')
          from prj_project_virtual_con_lv pp
         where pp.project_id = ppb.project_id) lease_start_date_month, --抵押期限开始日期（月）
       (select to_char(pp.Inception_Of_Lease, 'DD')
          from prj_project_virtual_con_lv pp
         where pp.project_id = ppb.project_id) lease_start_date_day, --抵押期限开始日期（日）
       (select substr(max(ln.due_date),0,4)
          from prj_quotation pq, hls_fin_calculator_ln ln
         where pq.document_category = 'PROJECT'
           and pq.document_id = ppb.project_id
           and pq.main_contract_flag = 'Y'
           and pq.calc_session_id = ln.calc_session_id) lease_end_date_year, --抵押期限结束日期（年）
       (select substr(max(ln.due_date),6,2)
          from prj_quotation pq, hls_fin_calculator_ln ln
         where pq.document_category = 'PROJECT'
           and pq.document_id = ppb.project_id
           and pq.main_contract_flag = 'Y'
           and pq.calc_session_id = ln.calc_session_id) lease_end_date_month, --抵押期限结束日期（月）
       (select substr(max(ln.due_date),-2,2)
          from prj_quotation pq, hls_fin_calculator_ln ln
         where pq.document_category = 'PROJECT'
           and pq.document_id = ppb.project_id
           and pq.main_contract_flag = 'Y'
           and pq.calc_session_id = ln.calc_session_id) lease_end_date_day --抵押期限结束日期（日）
  from prj_project_bp ppb, prj_project_mortgage ppm
 where ppb.bp_category = 'PLEDGER'
   and ppb.bp_type = 'PLEDGER'
   and ppb.bp_class = 'NP'
   and ppb.project_id = ppm.project_id
   and ppb.bp_id = ppm.bp_id_mortgagor
   and ppm.mortgage_type = 'PLEDGE'
   and ppm.mortgage_ast_classfication = 'RIGHT_P'
   and ppm.mortgage_asset_detail = 'STOCK'
   and ppm.project_id=${/parameter/@project_id}
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
