<?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" xmlns:s="leaf.plugin.script" defaultOrderBy=" t1.CONTRACT_NUMBER, t1.times">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
	  select a.contract_number,
	  a.contract_id,
       a.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = a.contract_status) contract_status_desc,
       a.business_type,
       (select t.description
          from hls_business_type t
         where t.business_type = a.business_type) business_type_desc,
       a.lease_execution_date,
       a.bp_id_tenant,
       (select hbm.bp_name
          from hls_bp_master hbm
         where hbm.bp_id = a.bp_id_tenant) bp_tenant_name,
       a.bp_id_agent_level1,

       (select hbm.bp_name
          from hls_bp_master hbm
         where hbm.bp_id = a.bp_id_agent_level1) bp_agent_name,
       a.lease_channel,
       (select hlc.description
          from hls_lease_channel hlc
         where hlc.lease_channel = a.lease_channel) lease_channel_desc,
       t.times,
       t.due_date,
       t.due_date + 90 due_date_plus_90,
       TO_DATE(${@due_date}, 'yyyy-mm-dd') - 90 due_date_reduce_90,
       TO_DATE(${@due_date}, 'yyyy-mm-dd') - 90 - T.DUE_DATE More_than_90_days,
       t.cf_item,
       (select i.description from hls_cashflow_item i where i.cf_item = t.cf_item) cf_item_desc,
       t.due_amount,
       T.RECEIVED_AMOUNT,
       T.NET_DUE_AMOUNT,
       T.VAT_DUE_AMOUNT,
       b.sap_division
  from con_contract_cashflow t, con_contract a, CON_CONTRACT_PAYMENT_JEV b
 where t.cf_item = 3
   and a.contract_id = t.contract_id
   and a.data_class = 'NORMAL'
   and B.CONTRACT_ID = a.CONTRACT_ID
   and nvl(a.hd_user_col_v05, 'N') = 'Y'
   and t.due_amount > 0
   and T.WRITE_OFF_FLAG != 'FULL'
   and t.due_amount != T.RECEIVED_AMOUNT
   and T.DUE_DATE < TO_DATE(${@due_date}, 'yyyy-mm-dd')
   ) t1
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#

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

    <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    <bm:query-fields>
        <bm:query-field name="contract_number"  queryExpression="t1.contract_number like '%'||${@contract_number}||'%'"/>
        <bm:query-field name="bp_tenant_name"  queryExpression="t1.bp_tenant_name like '%'||${@bp_tenant_name}||'%'"/>
        <bm:query-field  name="bp_agent_name"  queryExpression="t1.bp_agent_name like '%'||${@bp_agent_name}||'%'"/>
        <bm:query-field name="more_than_90_days_from"  queryExpression="t1.More_than_90_days &gt;= ${@more_than_90_days_from}"/>
        <bm:query-field name="more_than_90_days_to"  queryExpression="t1.More_than_90_days &lt;= ${@more_than_90_days_to}"/>
    </bm:query-fields>
</bm:model>
