<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-6-21 上午9:44:25  
    $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 pp.project_id, --合同ID
       pp.virtual_con_number, --合同编号
       substr(pp.virtual_con_number, 0, 5) || '-B' ||
       substr(pp.virtual_con_number, 6) virtual_con_number_b, --账户监管协议编号
       pp.virtual_con_number || '-1' virtual_con_number_1, --租赁附表编号
       pp.virtual_con_number || '-2' virtual_con_number_2, --付款通知书编号
       pp.virtual_con_number || '-3' virtual_con_number_3, --租赁物接受书编号
       pp.virtual_con_number || '-4' virtual_con_number_4, --所有权转移确认书编号
       pp.virtual_con_number || '-5' virtual_con_number_5, --实际租金支付表编号
       pp.virtual_con_name, --合同名称
       pp.signing_date, --签约日期
       to_char(pp.signing_date, 'YYYY') signing_date_year, --签约日期（年）
       to_char(pp.signing_date, 'MM') signing_date_month, --签约日期（月）
       to_char(pp.signing_date, 'DD') signing_date_day, --签约日期（日）
       change_number_to_cn(to_char(pp.signing_date, 'YYYY')) signing_date_year_n, --签约日期（年）--中文
       change_number_to_cn(to_char(pp.signing_date, 'MM')) signing_date_month_n, --签约日期（月）--中文
       change_number_to_cn(to_char(pp.signing_date, 'DD')) signing_date_day_n, --签约日期（日）--中文
       pp.signing_place, --签约地点
       to_char( pp.finance_amount,'FM999,999,999,990.00') finance_amount, --租赁物价款、租赁成本（小写）
       change_number_to_rmb(pp.finance_amount) finance_amount_n, --租赁物价款、租赁成本（大写）
       pp.lease_term, --租赁期限
       change_number_to_cn(pp.lease_term) lease_term_n, --租赁期限（中文）
       to_char(pp.Inception_Of_Lease, 'YYYY') inception_of_lease_year, --概算起租日（年）
       to_char(pp.Inception_Of_Lease, 'MM') inception_of_lease_month, --概算起租日（月）
       to_char(pp.Inception_Of_Lease, 'DD') inception_of_lease_day, --概算起租日（日）
       change_number_to_cn(to_char(pp.Inception_Of_Lease, 'YYYY')) inception_of_lease_year_n, --概算起租日（年）--中文
       change_number_to_cn(to_char(pp.Inception_Of_Lease, 'MM')) inception_of_lease_month_n, --概算起租日（月）--中文
       change_number_to_cn(to_char(pp.Inception_Of_Lease, 'DD')) inception_of_lease_day_n, --概算起租日（日）--中文
       decode(pp.hd_user_col_v03, 'FIXED', '（1）', '（2）') adjust_rate_method, --租赁利率选择方式
       pp.hd_user_col_v03,
       to_char( pp.INT_RATE,'FM999,990.000000')* 100||'' int_rate, --租赁利率
       (CASE
         WHEN 12 * PP.LEASE_TERM <= 12 THEN
          '一年以内（含一年）'
         WHEN 12 * PP.LEASE_TERM > 12 and 12 * PP.LEASE_TERM <= 60 THEN
          '一至五年（含五年）'
         ELSE
          '五年以上'
       END) base_rate_type_n,
       TO_CHAR(PP.BASE_RATE , 'FM999,999,990.000000')* 100||'' AS base_rate, --基准利率
       pp.int_rate_fixing_way, --利率浮动方式
       to_char( pp.INT_RATE_FIXING_RANGE,'FM999,990.000000') * 100||'' int_rate_fixing_range, --利率浮动比例
       to_char(pp.total_rental,'FM999,999,999,990.00') total_rental, --概算租金总额（小写）
       change_number_to_rmb(pp.total_rental) total_rental_n, --概算租金总额（大写）
       pp.invoice_title, --承租人发票名称
       pp.invoice_bp_address_phone_num, --承租人发票地址
       pp.invoice_phone_number, --承租人发票电话
       pp.invoice_bank, --承租人发票开户行
       pp.invoice_bp_bank_account, --承租人发票账号
       decode(pp.taxpayer_type, 'GENERAL_TAXPAYER', '是', '否') taxpayer_type_is_or, --是否增值税一般纳税人
       pp.tax_registry_num, --税务登记号
       (select hbm.invoice_title
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_invoice_title, --次承租人发票名称
       (select hbm.invoice_bp_address_phone_num
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_invoice_bp_address, --次承租人发票地址
       (select hbm.invoice_phone_number
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_invoice_phone_num, --次承租人发票电话
       (select hbm.invoice_bank
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_invoice_bank, --次承租人发票开户行
       (select hbm.invoice_bp_bank_account
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_bank_account, --次承租人发票账号
       (select hbm.tax_registry_num
          from prj_project_bp ppb, hls_bp_master hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbm.bp_id) tenant_sec_tax_registry_num, --次承租人税务登记号 
       decode((select hbm.taxpayer_type
                from prj_project_bp ppb, hls_bp_master hbm
               where ppb.project_id = pp.project_id
                 and ppb.bp_category = 'TENANT'
                 and ppb.bp_type = 'TENANT_SEC'
                 and ppb.bp_id = hbm.bp_id),
              'GENERAL_TAXPAYER',
              '是',
              '否') sec_taxpayer_type_is_or, --是否增值税一般纳税人
       to_char(pp.HD_USER_COL_N03,'FM999,999,999,990.00') hd_user_col_n03, --留购价（小写）
       change_number_to_rmb(pp.HD_USER_COL_N03) hd_user_col_n03_n, --留购价（大写）
       to_char(pp.LEASE_CHARGE,'FM999,999,999,990.00') lease_charge, --手续费（小写）
       change_number_to_rmb(pp.LEASE_CHARGE) lease_charge_n, --手续费（大写）
       to_char(pp.deposit,'FM999,999,999,990.00') deposit, --保证金（小写）
       change_number_to_rmb(pp.deposit) deposit_n, --保证金（大写）
       to_char(pp.lease_item_in_amount,'FM999,999,999,990.00'), --租赁物投保金额（小写）
       change_number_to_rmb(pp.lease_item_in_amount) lease_item_in_amount_n, --租赁物投保金额（大写）
       (select fc.company_full_name
          from fnd_companies fc
         where fc.company_id = pp.company_id) hn_company_name, --华能公司名称（出租人）
       (select fc.contact_person
          from fnd_companies fc
         where fc.company_id = pp.company_id) hn_contact_person, --华能收件人
       (select fc.email
          from fnd_companies fc
         where fc.company_id = pp.company_id) hn_email, --华能电子邮件
       (select fc.fax
          from fnd_companies fc
         where fc.company_id = pp.company_id) hn_fax, --华能传真
       (select fc.phone
          from fnd_companies fc
         where fc.company_id = pp.company_id) hn_phone, --华能电话 
       pp.bp_id_tenant_n, --承租人名称
       (select ppb.bp_name
          from prj_project_bp ppb
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC') tenant_sec_name, --次承租人名称
       (select ppb.bp_name
          from prj_project_bp ppb
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER') vender_name, --供应商名称 
       (select hbm.contact_person
          from hls_bp_master_contact_info hbm
         where hbm.bp_id = pp.bp_id_tenant
           and hbm.position = 'LP') bp_legal_person, --承租人法定代表人
       (select hbm.contact_person
          from hls_bp_master_contact_info hbm
         where hbm.bp_id = pp.bp_id_tenant
           and hbm.position = 'RP') bp_contact_person, --承租人收件人
       (select hbm.contact_person
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'LP') tenant_sec_legal_person, --次承租人法定代表人
       (select hbm.contact_person
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'LP') vender_legal_person, --供应商法定代表人    
       (select ppb.bp_name
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') tenant_sec_contact_person, --次承租人收件人
       (select ppb.bp_name
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') vender_contact_person, --供应商收件人    
       (select hbma.address
          from hls_bp_master_address hbma
         where hbma.bp_id = pp.bp_id_tenant
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') bp_company_address, --承租人地址
       (select hbma.address
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') tenant_sec_company_address, --次承租人地址
       (select hbma.address
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') vender_company_address, --供应商地址    
       (select hbma.zipcode
          from hls_bp_master_address hbma
         where hbma.bp_id = pp.bp_id_tenant
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') bp_company_zipcode, --承租人邮注册地邮编
       (select hbma.zipcode
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') tenant_sec_company_zipcode, --次承租人注册地邮编
       (select hbma.zipcode
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbma.bp_id = ppb.bp_id
           and hbma.address_type = 'COMPANY_ADDRESS'
           and hbma.enabled_flag = 'Y') vender_company_zipcode, --供应商注册地邮编    
       (select hbma.phone
          from hls_bp_master_contact_info hbma
         where hbma.bp_id = pp.bp_id_tenant
         and hbma.position='LP'
           ) bp_company_phone, --承租人注册地电话
       (select hbma.phone
          from prj_project_bp ppb, hls_bp_master_contact_info hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbma.bp_id = ppb.bp_id
           and hbma.position='LP'
           ) tenant_sec_company_phone, --次承租人注册地电话
         (select hbma.phone
          from prj_project_bp ppb, hls_bp_master_contact_info hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbma.bp_id = ppb.bp_id
           and hbma.position='LP'
           ) vender_company_phone, --供应商注册地电话   
        (select hbma.fax
          from hls_bp_master_contact_info hbma
         where hbma.bp_id = pp.bp_id_tenant
         --  and hbma.address_type = 'COMPANY_ADDRESS'
         and hbma.position='LP'
           ) bp_company_fax, --承租人注册地传真          
       (select hbma.fax
          from prj_project_bp ppb, hls_bp_master_contact_info hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TEANAT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbma.bp_id = ppb.bp_id
         --  and hbma.address_type = 'COMPANY_ADDRESS'
         and hbma.position='LP'
          ) tenant_sec_company_fax, --次承租人注册地传真    
        (select hbma.fax
          from prj_project_bp ppb, hls_bp_master_contact_info hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbma.bp_id = ppb.bp_id
         --  and hbma.address_type = 'COMPANY_ADDRESS'
         and hbma.position='LP'
           ) vender_company_fax, --供应商注册地传真    
       (select hbma.address || '，' || hbma.zipcode
          from hls_bp_master_address hbma
         where hbma.bp_id = pp.bp_id_tenant
           and hbma.address_type = 'DOC_SENT_ADDRESS'
           and hbma.enabled_flag = 'Y') bp_address_zipcode, --地址及邮编
       (select hbma.address 
          from hls_bp_master_address hbma
         where hbma.bp_id = pp.bp_id_tenant
           and hbma.address_type = 'DOC_SENT_ADDRESS'
           and hbma.enabled_flag = 'Y') bp_address, --地址   
       (select hbma.address || '，' || hbma.zipcode
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and ppb.bp_id = hbma.bp_id
           and hbma.address_type = 'DOC_SENT_ADDRESS'
           and hbma.enabled_flag = 'Y') tenant_sec_address_zipcode, --次承租人地址及邮编
        (select hbma.address
          from prj_project_bp ppb, hls_bp_master_address hbma
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and ppb.bp_id = hbma.bp_id
           and hbma.address_type = 'DOC_SENT_ADDRESS'
           and hbma.enabled_flag = 'Y') vender_address, --供应商地址    
       (select hbm.email
          from hls_bp_master_contact_info hbm
         where hbm.bp_id = pp.bp_id_tenant
           and hbm.position = 'RP') bp_email, --承租人电子邮件
       (select hbm.email
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') tenant_sec_email, --次承租人电子邮件
       (select hbm.email
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') vender_email, --供应商联系人电子邮件    
       (select hbm.fax
          from hls_bp_master_contact_info hbm
         where hbm.bp_id = pp.bp_id_tenant
           and hbm.position = 'RP') bp_fax, --承租人联系人传真
       (select hbm.phone
          from hls_bp_master_contact_info hbm
         where hbm.bp_id = pp.bp_id_tenant
           and hbm.position = 'RP') bp_phone, --承租人联系人电话    
       (select hbm.fax
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT_SEC'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') tenant_sec_fax, --次承租人联系人传真
       (select hbm.fax
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') vender_fax, --供应商联系人传真
       (select hbm.phone
          from prj_project_bp ppb, hls_bp_master_contact_info hbm
         where ppb.project_id = pp.project_id
           and ppb.bp_category = 'VENDER'
           and ppb.bp_type = 'VENDER'
           and hbm.bp_id = ppb.bp_id
           and hbm.position = 'RP') vender_phone, --供应商电话    
        to_char((pp.FINANCE_AMOUNT - pp.deposit - pp.LEASE_CHARGE),'FM999,999,999,990.00') asset_finance_amount, --内扣后租赁物价款（小写）     
       change_number_to_rmb(pp.FINANCE_AMOUNT - pp.deposit -
                            pp.LEASE_CHARGE) asset_finance_amount_n, --内扣后租赁物价款（大写）
       (select hbmba.bank_account_name
          from prj_project_bp ppb, hls_bp_master_bank_account hbmba
         where ppb.bp_id = pp.bp_id_tenant
           and ppb.prj_bp_bank_account_id = hbmba.bank_account_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT'
           and ppb.project_id = pp.project_id) bp_bank_account_name, --承租人提款户名
       (select hbmba.bank_full_name
          from prj_project_bp ppb, hls_bp_master_bank_account hbmba
         where ppb.bp_id = pp.bp_id_tenant
           and ppb.prj_bp_bank_account_id = hbmba.bank_account_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT'
           and ppb.project_id = pp.project_id) bp_bank_full_name, --承租人开户行
       (select hbmba.bank_account_num
          from prj_project_bp ppb, hls_bp_master_bank_account hbmba
         where ppb.bp_id = pp.bp_id_tenant
           and ppb.prj_bp_bank_account_id = hbmba.bank_account_id
           and ppb.bp_category = 'TENANT'
           and ppb.bp_type = 'TENANT'
           and ppb.project_id = pp.project_id) bp_bank_account_num, --承租人提款账号
       (select hbm.bp_name
          from prj_project_mortgage ppm, hls_bp_master hbm
         where ppm.project_id = pp.ref_project_id
           and ppm.mortgage_asset_detail = 'STOCK'
           and ppm.bp_id_mortgagor = hbm.bp_id) stock_mortgagor_name, --公司股权质押人名称
       (select ppm.company_name_stock
          from prj_project_mortgage ppm, hls_bp_master hbm
         where ppm.project_id = pp.ref_project_id
           and ppm.mortgage_asset_detail = 'STOCK'
           and ppm.bp_id_mortgagor = hbm.bp_id) company_name_stock, -- 股权质押目标公司名称 
       (select hbm.bp_name
          from prj_project_mortgage ppm, hls_bp_master hbm
         where ppm.project_id = pp.ref_project_id
           and ppm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE'
           and ppm.bp_id_mortgagor = hbm.bp_id) accounts_mortgagor_name, --应收账款质押人名称
       (select ppm.accounts_contract_name
          from prj_project_mortgage ppm, hls_bp_master hbm
         where ppm.project_id = pp.ref_project_id
           and ppm.mortgage_asset_detail = 'ACCOUNTS_RECEIVABLE'
           and ppm.bp_id_mortgagor = hbm.bp_id) accounts_contract_name -- 应收账款抵押目标合同名称
  from prj_project_virtual_con_lv pp
  where pp.project_id=${/parameter/@project_id}
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
