<?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 hhrc.contract_id,
			       hhrc.hired_req_id,
			       hhrc.contract_number,  --合同号
			       hhrc.propose_contract_type,
			       hhrc.propose_sign_date,  --拟签署时间
			       to_char(hhrc.propose_sign_date,'YYYY') propose_sign_date_year,
       			   to_char(hhrc.propose_sign_date,'MM') propose_sign_date_month,
       			   to_char(hhrc.propose_sign_date,'DD') propose_sign_date_day,
			       (select hbm.bp_name from hn_intermediary_hired_req hihr,hls_bp_master hbm where
			        hihr.bp_id = hbm.bp_id AND hhrc.hired_req_id = hihr.hired_req_id) bp_name,  --中介机构名称
			       (select hbm.legal_person
			          from hn_intermediary_hired_req hihr, hls_bp_master hbm
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbm.bp_id) legal_person,         --法定代表人(负责人)
			       (select hbma.address
			          from hn_intermediary_hired_req hihr, hls_bp_master_address hbma
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbma.bp_id
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_address,            --注册地址(住所)
			       (select hbma.address
			          from hn_intermediary_hired_req hihr, hls_bp_master_address hbma
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbma.bp_id
			           and hbma.address_type = 'DOC_SENT_ADDRESS'
			           and hbma.enabled_flag = 'Y') doc_sent_address,            --邮寄地址(联系地址)
			       (select hbma.zipcode
			          from hn_intermediary_hired_req hihr, hls_bp_master_address hbma
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbma.bp_id
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_zipcode,             --邮编
			       (select hbma.phone
			          from hn_intermediary_hired_req hihr, hls_bp_master_address hbma
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbma.bp_id
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_phone,               --电话
			        (select hbma.fax
			          from hn_intermediary_hired_req hihr, hls_bp_master_address hbma
			         where hihr.hired_req_id = hhrc.hired_req_id
			           and hihr.bp_id = hbma.bp_id
			           and hbma.address_type = 'COMPANY_ADDRESS'
			           and hbma.enabled_flag = 'Y') company_fax,                 --传真
			        (select pc.project_name
			          from prj_chance pc,hn_intermediary_chance hic
			         where pc.chance_id = hic.chance_id AND hic.hired_req_id = hhrc.hired_req_id) project_name,  --项目名称
				   (select hbm.bp_name
                		 from prj_chance pc,hn_intermediary_chance hic,hls_bp_master hbm
              			 where pc.chance_id = hic.chance_id AND hic.hired_req_id = hhrc.hired_req_id and hbm.bp_id = pc.bp_id) main_lessee,  --主承租人
             	   (select pc.project_location
               			  from prj_chance pc,hn_intermediary_chance hic
              			  where pc.chance_id = hic.chance_id AND hic.hired_req_id = hhrc.hired_req_id) project_location,  --项目所在地
			        to_char(hhrc.due_amount,'FM999,999,999,990.00') due_amount,     --服务费用
			        change_number_to_rmb( hhrc.due_amount) rmb_due_amount,    --服务费用(汉子)
			        DECODE(hhrc.amount_type,'Y','1','N','2') amount_type_n,    --费用类型(取类型)
			        DECODE(hhrc.pay_method,'SINGLE_PAYMENT','1','PHASED_PAYMENT','2') pay_method_n,              --费用支付方式
			       hhrc.pay_effective_days,             --支付距生效天数
			       to_char(hhrc.pay_effective_amount,'FM999,999,999,990.00') pay_effective_amount,           --支付金额
			       change_number_to_rmb(hhrc.pay_effective_amount) pay_effective_amount_rmb,
			       hhrc.pay_report_days,                --支付距出具报告天数
			       to_char(hhrc.pay_report_amount,'FM999,999,999,990.00') pay_report_amount,              --支付金额
			       change_number_to_rmb(hhrc.pay_report_amount) pay_report_amount_rmb,
			       hhrc.bank_account_id,
            	   hhrc.add_item_flag,                          --是否增项
            	   to_char(hhrc.technical_reseach_amount,'FM999,999,999,990.00') technical_reseach_amount,               --每项增加费用
                   change_number_to_rmb(hhrc.technical_reseach_amount) technical_reseach_amount_rmb,
				   to_char(hhrc.special_assets_amount,'FM999,999,999,990.00') special_assets_amount,                      --每项特定资产费用
                   change_number_to_rmb(hhrc.special_assets_amount) special_assets_amount_rmb,
                   to_char(hhrc.add_company_amount,'FM999,999,999,990.00') add_company_amount,                            --每个被调查公司费用
                   change_number_to_rmb(hhrc.add_company_amount) add_company_amount_rmb,
            	   hhrc.first_draft_days,                                        --初稿定稿
            	   hhrc.final_draft_days,                                        --定稿日
            	   (hn_report_pkg.get_hired_req_con_asset_name(p_contract_id => hhrc.contract_id)) asset_name,     --特定资产     
			       (select hbmba.bank_branch_name from hls_bp_master_bank_account hbmba where hbmba.bank_account_id = hhrc.bank_account_id) bank_full_name,--支行名称
			       (select hbmba.bank_account_name from hls_bp_master_bank_account hbmba where hbmba.bank_account_id = hhrc.bank_account_id) bank_account_name,--账户名称(户名)
			       (select hbmba.bank_account_num from hls_bp_master_bank_account hbmba where hbmba.bank_account_id = hhrc.bank_account_id) bank_account_num   --账号
			from hn_hired_req_contract hhrc
			where hhrc.contract_id = ${@contract_id} and hhrc.propose_contract_type = 'FINANCIAL_ADJUSTMENT'
				]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="contract_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
