<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng
    $Date: 2018-12-31 18:17:30
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" defaultOrderBy="cc.contract_id">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
	    	  select
	    	  cc.contract_id,
	    	  cc.contract_number,
       hbmt.bp_code bp_tenant_code,
       hbmt.bp_name bp_tenant_name,
       hbma.bp_code bp_agent_code,
       hbma.bp_name bp_agent_name,
       GUARANTOR_INFO,
       cc.division,
       (select hd.description
          from hls_division hd
         where hd.division = cc.division) division_n,
       cc.lease_channel,
       (select hlc.description
          from hls_lease_channel hlc
         where hlc.lease_channel = cc.lease_channel) lease_channel_n,
       cc.business_type,
       (select hbt.description
          from hls_business_type hbt
         where hbt.business_type = cc.business_type) business_type_n,
       ccli.pattern,
       ccli.machine_number,
       ccli.equipment_number,
       nvl(cc.loss_GUARANTOR_amount, 0) loss_GUARANTOR_amount, -- 损失担保申请金额
       nvl(cc.guarantor_return_amount,0) guarantor_return_amount, --原担保退回金额
       nvl((select sum(cwo.write_off_due_amount)
             from csh_write_off cwo
            where cwo.contract_id = cc.contract_id
              and cwo.write_off_classification in ('120', '130')
              and cwo.cf_item In (1, 102, 103, 104, 200, 9, 8,831,832)
              and cwo.reversed_flag = 'N'),
           0)-nvl(cc.before_guarantor_amount,0) write_off_due_amount,
       nvl(cc.old_sys_return_amount, 0) old_sys_return_amount, -- 老系统客户退还金额
       nvl((select sum(r.this_return_amount)
             from csh_transaction_return r
            where r.ref_contract_id = cc.contract_id
              and r.status = 'APPROVED'),
           0) return_amount,
       (nvl((select sum(cwo.write_off_due_amount)
             from csh_write_off cwo
            where cwo.contract_id = cc.contract_id
              and cwo.write_off_classification in ('120', '130')
              and cwo.cf_item In (1, 102, 103, 104, 200, 9, 8,831,832)
              and cwo.reversed_flag = 'N'),
           0)-nvl(cc.before_guarantor_amount,0)) - nvl((select sum(r.this_return_amount)
                      from csh_transaction_return r
                     where r.ref_contract_id = cc.contract_id
                       and r.status = 'APPROVED'),
                    0) - nvl(cc.old_sys_return_amount, 0) Guarantee_balance,
       greatest(ds_report_common_pkg.get_remain_write_off_principal(cc.contract_id),
                0) remain_write_off_principal,

       greatest(ds_report_common_pkg.get_remain_write_off_interest(cc.contract_id),
                0) remain_write_off_interest,

       greatest(ds_report_common_pkg.get_remain_write_off_residual(cc.contract_id),
                0) remain_write_off_residual,

       greatest(ds_report_common_pkg.get_remain_write_off_penalty(cc.contract_id),
                0) remain_write_off_penalty,

       greatest(ds_report_common_pkg.get_remain_write_off_832(cc.contract_id),
                0) remain_write_off_832,

       (nvl((select sum(cwo.write_off_due_amount)
             from csh_write_off cwo
            where cwo.contract_id = cc.contract_id
              and cwo.write_off_classification in ('120', '130')
              and cwo.cf_item In (1, 102, 103, 104, 200, 9, 8,831,832)
              and cwo.reversed_flag = 'N'),
           0)-nvl(cc.before_guarantor_amount,0)) - nvl((select sum(r.this_return_amount)
                      from csh_transaction_return r
                     where r.ref_contract_id = cc.contract_id
                       and r.status = 'APPROVED'),
                    0) - nvl(cc.old_sys_return_amount, 0)
       -
       greatest(ds_report_common_pkg.get_remain_write_off_principal(cc.contract_id),
                0) - greatest(ds_report_common_pkg.get_remain_write_off_interest(cc.contract_id),
                              0) - greatest(ds_report_common_pkg.get_remain_write_off_residual(cc.contract_id),
                                            0) -
       greatest(ds_report_common_pkg.get_remain_write_off_penalty(cc.contract_id),
                0) - greatest(ds_report_common_pkg.get_remain_write_off_832(cc.contract_id),
                              0) remain_Guarantee_balance,
                              cc.before_guarantor_amount
  from con_contract cc
  left outer join hls_bp_master hbmt
    on cc.bp_id_tenant = hbmt.bp_id
  left outer join hls_bp_master hbma
    on cc.bp_id_agent_level1 = hbma.bp_id
  left outer join con_contract_lease_item ccli
    on cc.contract_id = ccli.contract_id
   and ccli.equipment_type = 'MAIN'

#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#

	    	]]></bm:query-sql>
        </bm:operation>
    </bm:operations>

    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="cc.contract_id = ${@contract_id}"/>
        <bm:query-field name="contract_number" queryExpression="cc.contract_number like ${@contract_number}"/>
        <bm:query-field name="bp_tenant_name" queryExpression="hbmt.bp_name like ${@bp_tenant_name}"/>
        <bm:query-field name="bp_agent_name" queryExpression="hbma.bp_name like ${@bp_agent_name}"/>
        <bm:query-field name="guarantor_info" queryExpression="cc.guarantor_info like ${@guarantor_info}"/>
        <bm:query-field name="guarantee_balance_from" queryExpression="( nvl((select sum(cwo.write_off_due_amount)
             from csh_write_off cwo
            where cwo.contract_id = cc.contract_id
              and cwo.write_off_classification in ('120', '130')
              and cwo.cf_item In (1, 102, 103, 104, 200, 9, 8,831,832)
              and cwo.reversed_flag = 'N'),
           0) - nvl((select sum(r.this_return_amount)
                      from csh_transaction_return r
                     where r.ref_contract_id = cc.contract_id
                       and r.status = 'APPROVED'),
                    0) - nvl(cc.old_sys_return_amount, 0)) &gt;= ${@guarantee_balance_from}"/>
        <bm:query-field name="guarantee_balance_to" queryExpression="( nvl((select sum(cwo.write_off_due_amount)
             from csh_write_off cwo
            where cwo.contract_id = cc.contract_id
              and cwo.write_off_classification in ('120', '130')
              and cwo.cf_item In (1, 102, 103, 104, 200, 9, 8,831,832)
              and cwo.reversed_flag = 'N'),
           0) - nvl((select sum(r.this_return_amount)
                      from csh_transaction_return r
                     where r.ref_contract_id = cc.contract_id
                       and r.status = 'APPROVED'),
                    0) - nvl(cc.old_sys_return_amount, 0)) &lt;= ${@guarantee_balance_to}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression=" cc.data_class = 'NORMAL' and cc.lease_execution_flag = 'Y' and cc.loss_GUARANTOR_amount > 0"/>
    </bm:data-filters>
</bm:model>
