<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: DJ  
    $Date: 2013-7-4 下午5:06:06  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="ACR_INVOICE_CONTRACT_V" defaultOrderBy="t1.contract_number desc">
    <bm:fields>
        <bm:field name="spv_company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SPV_COMPANY_ID"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID"/>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NUMBER" prompt="HLS.PROJECT_NUMBER"/>
        <bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME" prompt="HLS.PROJECT_NAME"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" prompt="HLS.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME" prompt="HLS.CONTRACT_NAME"/>
        <bm:field name="inception_of_lease" databaseType="DATE" datatype="java.util.Date" physicalName="INCEPTION_OF_LEASE" prompt="ACR.INCEPTION_OF_LEASE"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" prompt="HLS.CONTRACT_STATUS"/>
        <bm:field name="contract_status_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS_DESC" prompt="HLS.CONTRACT_STATUS"/>
        <bm:field name="billing_method" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILLING_METHOD"/>
        <bm:field name="billing_method_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILLING_METHOD_DESC" prompt="HLS.BILLING_RULE"/>
        <bm:field name="billing_object_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BILLING_OBJECT_ID"/>
        <bm:field name="billing_object_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILLING_OBJECT_CODE"/>
        <bm:field name="billing_object_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILLING_OBJECT_NAME" prompt="ACR.BILLING_OBJECT_NAME"/>
        <bm:field name="object_taxpayer_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OBJECT_TAXPAYER_TYPE"/>
        <bm:field name="object_taxpayer_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OBJECT_TAXPAYER_TYPE_DESC" prompt="ACR.OBJECT_TAXPAYER_TYPE"/>
        <bm:field name="object_tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OBJECT_TAX_REGISTRY_NUM" prompt="ACR.OBJECT_TAX_REGISTRY_NUM"/>
        <bm:field name="bill_object_bp_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILL_OBJECT_BP_CLASS"/>
        <bm:field name="bill_object_bp_class_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BILL_OBJECT_BP_CLASS_DESC"/>
        <bm:field name="tax_type_vat" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TAX_TYPE_VAT"/>
        <bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION" prompt="HLS.COMMENT"/>
        <bm:field name="invoice_title" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_TITLE" prompt="ACR.INVOICE_TITLE"/>
        <bm:field name="invoice_bp_address_phone_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_BP_ADDRESS_PHONE_NUM" prompt="ACR.INVOICE_BP_ADDRESS_PHONE_NUM"/>
        <bm:field name="invoice_bp_bank_account" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_BP_BANK_ACCOUNT" prompt="ACR.INVOICE_BP_BANK_ACCOUNT"/>
        <bm:field name="tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TAX_REGISTRY_NUM"/>
        <bm:field name="prj_search_term_1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PRJ_SEARCH_TERM_1"/>
        <bm:field name="prj_search_term_2" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PRJ_SEARCH_TERM_2"/>
        <bm:field name="con_search_term_1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CON_SEARCH_TERM_1"/>
        <bm:field name="con_search_term_2" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CON_SEARCH_TERM_2"/>
        <bm:field name="document_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DOCUMENT_TYPE"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME"/>
        <bm:field name="invoice_type_desc"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:query-fields>
        <bm:query-field field="bill_object_bp_class" queryOperator="="/>
        <bm:query-field field="invoice_type_desc" queryOperator="like"/>
        <bm:query-field name="project_number_from" queryExpression="t1.project_number &gt;=${@project_number_from}"/>
        <bm:query-field name="project_number_to" queryExpression="t1.project_number &lt;=${@project_number_to}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number  like ${@contract_number}"/>
        <bm:query-field name="bp_code_tenant_from" queryExpression="t1.billing_object_code &gt;=${@bp_code_tenant_from}"/>
        <bm:query-field name="bp_code_tenant_to" queryExpression="t1.billing_object_code &lt;=${@bp_code_tenant_to}"/>
        <bm:query-field name="lease_start_date_from" queryExpression="t1.inception_of_lease &gt;= to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="t1.inception_of_lease &lt;= to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="document_type" queryExpression="t1.document_type=${@document_type}"/>
        <bm:query-field name="billing_method" queryExpression="t1.billing_method=${@billing_method}"/>
        <bm:query-field name="prj_search_term_1" queryExpression="t1.prj_search_term_1=${@prj_search_term_1}"/>
        <bm:query-field name="prj_search_term_2" queryExpression="t1.prj_search_term_2=${@prj_search_term_2}"/>
        <bm:query-field name="con_search_term_1" queryExpression="t1.con_search_term_1=${@con_search_term_1}"/>
        <bm:query-field name="con_search_term_2" queryExpression="t1.con_search_term_2=${@con_search_term_2}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos; "/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos; "/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos; "/>
        <bm:query-field name="document_type" queryExpression="t1.document_type=${@document_type}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="due_date_from" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null) and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
        <bm:query-field name="due_date_to" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null)  and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
        <bm:query-field name="overdue_status" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null)  and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
        <bm:query-field name="overdue_max_days_from" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null)  and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
        <bm:query-field name="overdue_max_days_to" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null) and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
        <bm:query-field name="cf_item" queryExpression="exists (select 1 from con_contract_cashflow t where t.contract_id = t1.contract_id and t.due_date between nvl(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;),t.due_date) and nvl(to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;),t.due_date) and t.billing_status!=&apos;FULL&apos; and t.cf_item=nvl(${@cf_item},t.cf_item) and (t.overdue_max_days between nvl(${@overdue_max_days_from},t.overdue_max_days) and nvl(${@overdue_max_days_to},t.overdue_max_days) or t.overdue_max_days is null)  and t.overdue_status=nvl(${@overdue_status},t.overdue_status))"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="nvl(t1.spv_company_id,t1.company_id)=${/session/@company_id}"/>
        <bm:data-filter enforceOperations="query" expression="t1.billing_status IN (&apos;NOT&apos;, &apos;PARTIAL&apos;) AND t1.contract_status not in  (&apos;NEW&apos;,&apos;PENDING&apos;)"/>
        <bm:data-filter enforceOperations="query" expression=" exists (SELECT 1 FROM con_contract_cashflow c WHERE c.cf_item in (&apos;1&apos;,&apos;8&apos;,&apos;9&apos;,&apos;912&apos;,&apos;913&apos;) and c.write_off_flag!=&apos;NOT&apos; and c.contract_id = t1.contract_id AND c.cf_status = &apos;RELEASE&apos; AND c.billing_status != &apos;FULL&apos; and c.cf_direction=&apos;INFLOW&apos; and c.due_date &lt;= trunc(sysdate)) "/>
        <!--                 <bm:data-filter enforceOperations="query" expression="exists(         SELECT 1                FROM con_contract_cashflow c               WHERE c.contract_id = t1.contract_id          AND c.cf_status = &apos;RELEASE&apos;          AND c.billing_status != &apos;FULL&apos;              AND (         (         c.cf_type = 109)          OR (c.cf_type = 9 AND c.write_off_flag != &apos;NOT&apos;)          OR (c.write_off_flag = &apos;FULL&apos; AND c.cf_direction = &apos;INFLOW&apos;)               )                 AND not exists (select 1           from con_contract_billing_method m          where m.equipment_billing = &apos;Y&apos;            and c.cf_type = 2 and m.contract_id = t1.contract_id)         AND c.cf_item = NVL(${@cf_item}, c.cf_item)                      AND (${@last_received_date_from} IS NULL          OR c.last_received_date &gt;= trunc(to_date(${@last_received_date_from}, &apos;yyyy-mm-dd&apos;)))         AND (         (${@last_received_date_to} IS NULL          OR c.last_received_date &lt; trunc(to_date(${@last_received_date_to}, &apos;yyyy-mm-dd&apos;)) + 1))                  )"/> -->
    </bm:data-filters>
</bm:model>
