<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-7-10 下午8:30:43  
    $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
      to_char( (select sum(ppm.total_value) * 10000
          from prj_project_mortgage ppm
         where ppm.project_id = ppb.project_id
           and ppm.bp_id_mortgagor = ppb.bp_id),'FM999,999,999,990.00') total_value, --抵质押物价值（小写 元）
       (select change_number_to_rmb(sum(ppm.total_value) * 10000)
          from prj_project_mortgage ppm
         where ppm.project_id = ppb.project_id
           and ppm.bp_id_mortgagor = ppb.bp_id) total_value_n, --抵质押物价值（大写 元）
      to_char( (select pp.finance_amount
          from prj_project pp
         where pp.project_id = ppb.project_id),'FM999,999,999,990.00') finance_amount, --融资租赁成本        
        (select change_number_to_rmb(pp.finance_amount)
          from prj_project pp
         where pp.project_id = ppb.project_id) finance_amount_n , --融资租赁成本(大写 元) 
       (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, --抵押期限结束日期（日）
       (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, --次承租人名称
       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') pledger_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') pledger_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') pledger_zipcode, --抵质押人邮编（邮寄地址邮编）
       (select hbmci.phone
          from hls_bp_master_contact_info hbmci
         where hbmci.bp_id = ppb.bp_id
           and hbmci.position = 'LP') pledger_phone, --抵质押人法定代表人电话
       (select hbmci.fax
          from hls_bp_master_contact_info hbmci
         where hbmci.bp_id = ppb.bp_id
           and hbmci.position = 'LP') pledger_fax --抵质押人法定代表人传真
  from prj_project_bp ppb
 where ppb.bp_category = 'PLEDGER'
   and ppb.bp_type = 'PLEDGER'
   and ppb.bp_class = 'ORG'
   and ppb.project_id=${/parameter/@project_id}
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
