<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT * FROM(
            select pp.project_number,
            	   pp.service_con_number,	--顾问服务合同号
			       pp.bp_id_tenant,
			       nvl((select 'Y' from dual where
             		(select count(1) from hls_fin_calculator_ln t,prj_quotation pq
                   where t.calc_session_id = pq.calc_session_id and pq.document_id = pp.project_id and pq.document_category = 'PROJECT'
                   and pq.quotation_type = 'MAJOR' and nvl(t.other_fee2,0) <> 0) > 1),'N') compare_chose,
			       (SELECT hbm.bp_name
			          FROM hls_bp_master hbm
			         WHERE hbm.bp_id = pp.bp_id_tenant) lessee_name, --主承租人
			       (select hbm.legal_person
			          from prj_project_bp ppb, hls_bp_master hbm
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbm.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT') legal_person,         --法定代表人(负责人)
			       (select hbma.address
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') 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_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'MAILING_ADDRESS'
			           and hbma.enabled_flag = 'Y') communication_address,            -- 通讯地址
			       (select hbma.address
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'DOC_SENT_ADDRESS'
			           and hbma.enabled_flag = 'Y') doc_sent_address,            --邮寄地址(联系地址)
			       (select hbma.zipcode
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'DOC_SENT_ADDRESS'
			           and hbma.enabled_flag = 'Y') doc_sent_zipcode,            --邮寄地址邮编(联系地址)
			       (select hbma.zipcode
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_zipcode,             --邮编
			       (select hbma.phone
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_phone,               --电话
			           (select hbma.fax
			          from prj_project_bp ppb, hls_bp_master_address  hbma
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbma.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_fax,                 --传真
			       (select hbmci.contact_person
			          from prj_project_bp ppb, hls_bp_master_contact_info hbmci
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbmci.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbmci.position = 'CP' and rownum = 1) contact_person,         --联系人
			       (select hbmci.phone
			          from prj_project_bp ppb, hls_bp_master_contact_info hbmci
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbmci.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbmci.position = 'CP' and rownum = 1) contact_person_phone,         --联系人电话
			       (select hbmci.phone
			          from prj_project_bp ppb, hls_bp_master_contact_info hbmci
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbmci.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbmci.position = 'LP' and rownum = 1) lp_person_phone,         --法定联系人电话
			       (select hbmci.fax
			          from prj_project_bp ppb, hls_bp_master_contact_info hbmci
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbmci.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbmci.position = 'CP' and rownum = 1) contact_person_fax,         --联系人传真
			       (select hbmci.fax
			          from prj_project_bp ppb, hls_bp_master_contact_info hbmci
			         where ppb.project_id = pp.project_id and ppb.bp_id = hbmci.bp_id and ppb.bp_type='TENANT' and ppb.bp_category = 'TENANT'
			           and hbmci.position = 'LP' and rownum = 1) lp_person_fax,         --法定联系人传真
			       (select max(hfcl.calc_date)
			          from hls_fin_calculator_ln hfcl,prj_quotation pq 
			         where pq.document_category = 'PROJECT'
			           and pq.document_id = pp.project_id
			           and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id ) calc_date,   --租赁报价截止日期
			       to_char(to_date((select max(hfcl.calc_date)
	                  from hls_fin_calculator_ln hfcl,prj_quotation pq 
	                 where pq.document_category = 'PROJECT'
	                   and pq.document_id = pp.project_id
	                   and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id),'yyyy-mm-dd'),'YYYY') calc_date_year,
	              to_char(to_date((select max(hfcl.calc_date)
	                  from hls_fin_calculator_ln hfcl,prj_quotation pq 
	                 where pq.document_category = 'PROJECT'
	                   and pq.document_id = pp.project_id
	                   and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id),'yyyy-mm-dd'),'mm') calc_date_month,
	             to_char(to_date((select max(hfcl.calc_date)
	                  from hls_fin_calculator_ln hfcl,prj_quotation pq 
	                 where pq.document_category = 'PROJECT'
	                   and pq.document_id = pp.project_id
	                   and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id),'yyyy-mm-dd'),'dd') calc_date_day,
			       to_char((select nvl(sum(nvl(hfcl.other_fee2,0)),0)
			          from hls_fin_calculator_ln hfcl,prj_quotation pq 
			         where pq.document_category = 'PROJECT'
			           and pq.document_id = pp.project_id
			           and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id ),'FM999,999,999,999,990.00') amount_other,                   --咨询费总额
			       change_number_to_rmb((select nvl(sum(nvl(hfcl.other_fee2,0)),0)
			          from hls_fin_calculator_ln hfcl,prj_quotation pq 
			         where pq.document_category = 'PROJECT'
			           and pq.document_id = pp.project_id
			           and pq.quotation_type = 'MAJOR' and pq.calc_session_id = hfcl.calc_session_id )) amount_other_rmb,
			       (select ee.name from exp_employees ee where  ee.employee_id = pp.employee_id) manager,--业务经理(联系人)
			       (select ee.mobil from exp_employees ee where ee.employee_id = pp.employee_id) mobil,--联系方式
			       pp.project_id
			  from prj_project pp where pp.project_id = ${@project_id}
			  ) t1 #WHERE_CLAUSE#
			  ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
