<?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 hvci.insurance_id,
            	   hvci.insurance_seq,
			       (select pp.virtual_con_number
			          from hn_virtual_con_relate_hd hvcrh, prj_project pp
			         where hvcrh.hd_id = hvci.hd_id
			           and pp.project_id = hvcrh.project_id)||'-'||hvci.insurance_seq insurance_seq_n,
			       (select pp.virtual_con_number
			          from hn_virtual_con_relate_hd hvcrh, prj_project pp
			         where hvcrh.hd_id = hvci.hd_id
			           and pp.project_id = hvcrh.project_id) virtual_con_number, --大合同号
			       (select pp.project_name
			          from hn_virtual_con_relate_hd hvcrh, prj_project pp
			         where hvcrh.hd_id = hvci.hd_id
			           and pp.project_id = hvcrh.project_id) project_name, --合同名称
			       (SELECT nvl(sum(NVL(ccc.due_amount, 0)), 0)
			          FROM con_contract_cashflow    ccc,
			               con_contract             cc,
			               hn_virtual_con_relate_hd tt
			         WHERE cc.contract_id = ccc.contract_id
			           AND tt.project_id = cc.project_id
			           AND tt.hd_id = hvci.hd_id
			           AND ccc.times = 0
			           AND cc.data_class = 'NORMAL'
			           AND ccc.cf_item = 0) principal_amount, --本金总额
			       ((SELECT nvl(sum(NVL(ccc.due_amount, 0)), 0)
			           FROM con_contract_cashflow    ccc,
			                con_contract             cc,
			                hn_virtual_con_relate_hd tt
			          WHERE cc.contract_id = ccc.contract_id
			            AND tt.project_id = cc.project_id
			            AND tt.hd_id = hvci.hd_id
			            AND cc.data_class = 'NORMAL'
			            AND ccc.times = 0
			            AND ccc.cf_item = 0) -
			       (SELECT NVL(SUM(NVL(cwo.write_off_principal, 0)), 0)
			           FROM csh_write_off            cwo,
			                con_contract             cc,
			                hn_virtual_con_relate_hd tt
			          WHERE cwo.contract_id = cc.contract_id
			            AND tt.hd_id = hvci.hd_id
			            AND tt.project_id = cc.project_id
			            AND cwo.write_off_type = 'RECEIPT_CREDIT'
			            AND cc.data_class = 'NORMAL'
			            AND cwo.times <> '0'
			            AND cwo.cf_item = '1'
			            AND NVL(cwo.reversed_flag, 'N') = 'N'
			            AND cwo.write_off_date < to_date(${@query_date},'yyyy-mm-dd'))) surplus_amount, --剩余本金
			       hvci.insurance_number,  --保单编号
			       hvci.insurance_type,    --保险险种
			       hvci.insured_flag,      --我司是否为被保险人
			       hvci.insurance_value,   --投保金额(万元)
			       hvci.insurance_end_date,    --保险到期日
			       (case
			       when hvci.insurance_end_date is not null then
			       (nvl((select '已解除' from dual 
			              where trunc(hvci.insurance_end_date) < trunc(to_date(${@query_date},'yyyy-mm-dd'))),'保险中'))
			        else ''
			        END ) insurance_status,      --保险状态
			       (hn_report_pkg.get_prj_filr_name(p_check_id => hvci.insurance_id,
			                                        p_table_name => 'hn_virtual_con_insurance')) file_name,  --附件名
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => hvci.insurance_id,
			                                            p_table_name => 'hn_virtual_con_insurance')) attachment_id,
			       hvci.hd_id
			  from hn_virtual_con_insurance hvci,hn_virtual_con_relate_hd hvcrh
			 where hvcI.hd_id = hvcrh.hd_id and hvcrh.project_id = ${@project_id}
			  ) t1 #WHERE_CLAUSE# order by t1.virtual_con_number,t1.insurance_seq
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos;"/>
        <bm:query-field name="insurance_status_n" queryExpression="t1.insurance_status like &apos;%&apos;||${@insurance_status}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
