<?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
                    t1.spv_company_id,
                    t1.company_id,
                    t1.project_id,
                    t1.project_number,
                    t1.project_name,
                    t1.contract_id,
                    t1.contract_number,
                    t1.contract_name,
                    t1.inception_of_lease,
                    t1.contract_status,
                    t1.contract_status_desc,
                    t1.billing_method,
                    t1.billing_method_desc,
                    DECODE(t2.cf_item,'912',t1.inv_billing_object_id,t1.billing_object_id) billing_object_id,
                    DECODE(t2.cf_item,'912',t1.inv_billing_object_code,t1.billing_object_code) billing_object_code,
                    DECODE(t2.cf_item,'912',t1.inv_billing_object_name,t1.billing_object_name) billing_object_name,
                    DECODE(t2.cf_item,'912',t1.inv_object_taxpayer_type,t1.object_taxpayer_type) object_taxpayer_type,
                    DECODE(t2.cf_item,'912',t1.inv_object_taxpayer_type_desc,t1.object_taxpayer_type_desc) object_taxpayer_type_desc,
                    DECODE(t2.cf_item,'912',t1.inv_object_tax_registry_num,t1.object_tax_registry_num) object_tax_registry_num,
                    DECODE(t2.cf_item,'912',t1.inv_bill_object_bp_class,t1.bill_object_bp_class) bill_object_bp_class,
                    DECODE(t2.cf_item,'912',t1.inv_bill_object_bp_class_desc,t1.bill_object_bp_class_desc) bill_object_bp_class_desc,
                    t1.tax_type_vat,
                    t1.description,
                    DECODE(t2.cf_item,'912',t1.inv_invoice_title,t1.invoice_title) invoice_title,
                    DECODE(t2.cf_item,'912',t1.inv_bp_address_phone_num,t1.invoice_bp_address_phone_num) invoice_bp_address_phone_num,
                    DECODE(t2.cf_item,'912',t1.inv_invoice_bp_bank_account,t1.invoice_bp_bank_account) invoice_bp_bank_account,
                    DECODE(t2.cf_item,'912',t1.inv_tax_registry_num,t1.tax_registry_num) tax_registry_num,
                    t1.prj_search_term_1,
                    t1.prj_search_term_2,
                    t1.con_search_term_1,
                    t1.con_search_term_2,
                    t1.document_type,
                    t1.bp_name,
                    DECODE(t2.cf_item,'912',t1.inv_invoice_type_desc,t1.invoice_type_desc) invoice_type_desc,
                    t2.cashflow_id,
                    t2.cf_item,
                    t2.cf_item_desc,
                    t2.times,
                    t2.last_received_date,
                    t2.due_date,
                    t2.due_amount,
                    t2.principal,
                    t2.interest,
                    t2.received_amount,
                    t2.received_principal,
                    t2.received_interest,
                    t2.billing_amount,
                    t2.billing_principal,
                    t2.billing_interest,
                    t2.vat_due_amount,
                    t2.vat_principal,
                    t2.vat_interest,
                    t2.net_due_amount,
                    t2.net_principal,
                    t2.net_interest,
                    t2.currency,
                    t2.currency_desc,
                    t2.exchange_rate,
                    t2.exchange_rate_type,
                    t2.exchange_rate_type_desc,
                    t2.exchange_rate_quotation,
                    t2.exchange_rate_quotation_desc,
                    t2.overdue_status,
                    t2.overdue_max_days,
                    t1.lease_channel_desc,
                    t1.business_type_desc

                FROM
                    acr_invoice_contract_v t1,
                    ACR_INVOICE_CONTRACT_CF_V t2 #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_id,
                    t2.times,
                    t2.due_date,
                    t2.cf_item
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <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" required="true"/>
        <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"/>
        <bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" required="true"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME"/>
        <bm:field name="inception_of_lease" databaseType="DATE" datatype="java.util.Date" physicalName="INCEPTION_OF_LEASE"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" required="true"/>
        <bm:field name="contract_status_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS_DESC"/>
        <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"/>
        <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"/>
        <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"/>
        <bm:field name="object_tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="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"/>
        <bm:field name="invoice_title" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_TITLE"/>
        <bm:field name="invoice_bp_address_phone_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_BP_ADDRESS_PHONE_NUM"/>
        <bm:field name="invoice_bp_bank_account" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="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" required="true"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME"/>
        <bm:field name="invoice_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_TYPE_DESC"/>
        <bm:field name="cashflow_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CASHFLOW_ID" required="true"/>
        <bm:field name="cf_item" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CF_ITEM" required="true"/>
        <bm:field name="cf_item_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CF_ITEM_DESC"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TIMES" required="true"/>
        <bm:field name="last_received_date" databaseType="DATE" datatype="java.util.Date" physicalName="LAST_RECEIVED_DATE"/>
        <bm:field name="due_date" databaseType="DATE" datatype="java.util.Date" physicalName="DUE_DATE"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DUE_AMOUNT"/>
        <bm:field name="principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="PRINCIPAL"/>
        <bm:field name="interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="INTEREST"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RECEIVED_AMOUNT"/>
        <bm:field name="received_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RECEIVED_PRINCIPAL"/>
        <bm:field name="received_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RECEIVED_INTEREST"/>
        <bm:field name="billing_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="BILLING_AMOUNT"/>
        <bm:field name="billing_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="BILLING_PRINCIPAL"/>
        <bm:field name="billing_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="BILLING_INTEREST"/>
        <bm:field name="vat_due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="VAT_DUE_AMOUNT"/>
        <bm:field name="vat_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="VAT_PRINCIPAL"/>
        <bm:field name="vat_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="VAT_INTEREST"/>
        <bm:field name="net_due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="NET_DUE_AMOUNT"/>
        <bm:field name="net_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="NET_PRINCIPAL"/>
        <bm:field name="net_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="NET_INTEREST"/>
        <bm:field name="currency" databaseType="CHAR" datatype="java.lang.String" physicalName="CURRENCY"/>
        <bm:field name="currency_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_DESC"/>
        <bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Long" physicalName="EXCHANGE_RATE"/>
        <bm:field name="exchange_rate_type" databaseType="CHAR" datatype="java.lang.String" physicalName="EXCHANGE_RATE_TYPE"/>
        <bm:field name="exchange_rate_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="exchange_rate_quotation" databaseType="CHAR" datatype="java.lang.String" physicalName="EXCHANGE_RATE_QUOTATION"/>
        <bm:field name="exchange_rate_quotation_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_QUOTATION_DESC"/>
        <bm:field name="overdue_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OVERDUE_STATUS" required="true"/>
        <bm:field name="overdue_max_days" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OVERDUE_MAX_DAYS"/>
        <bm:field name="lease_channel_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHANNEL_DESC"/>
        <bm:field name="business_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BUSINESS_TYPE_DESC"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="cf_item" queryExpression="t2.cf_item = ${@cf_item}"/>
        <bm:query-field name="due_date_from" queryExpression="t2.due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="t2.due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="overdue_max_days_from" queryExpression="t2.overdue_max_days &gt;= ${@overdue_max_days_from}"/>
        <bm:query-field name="overdue_max_days_to" queryExpression="t2.overdue_max_days &lt;= ${@overdue_max_days_to}"/>
        <bm:query-field field="overdue_status" queryExpression="t2.overdue_status=${@overdue_status} and t2.write_off_flag != &apos;FULL&apos;"/>
        <bm:query-field field="bill_object_bp_class" queryOperator="="/>
        <bm:query-field name="invoice_type_desc" queryExpression="decode(t2.cf_item,&apos;912&apos;,t1.inv_invoice_type_desc,t1.invoice_type_desc) like ${@invoice_type_desc}"/>
        <bm:query-field name="invoice_title" queryExpression="decode(t2.cf_item,&apos;912&apos;,t1.inv_invoice_title,t1.invoice_title) like ${@invoice_title}"/>
        <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="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&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-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="nvl(t2.billing_amount,0)&lt;t2.received_amount"/>
        <bm:data-filter enforceOperations="query" expression="t1.contract_id = t2.contract_id"/>
        <bm:data-filter enforceOperations="query" expression="t2.cf_item in(&apos;1&apos;,&apos;8&apos;,&apos;9&apos;,&apos;10&apos;,&apos;12&apos;,&apos;13&apos;,&apos;51&apos;,&apos;200&apos;,&apos;912&apos;,&apos;913&apos;)"/>
        <!-- <bm:data-filter enforceOperations="query" expression="trunc(t2.due_date) &lt;= trunc(sysdate)"/> -->
        <bm:data-filter enforceOperations="query" expression="t2.write_off_flag != &apos;NOT&apos;"/>
        <!-- <bm:data-filter enforceOperations="query" expression="t2.cf_status = &apos;RELEASE&apos;"/> -->
        <bm:data-filter enforceOperations="query" expression="t2.billing_status != &apos;FULL&apos; and t2.cf_direction=&apos;INFLOW&apos;"/>
        <bm:data-filter enforceOperations="query" expression="t1.contract_status != &apos;CANCEL&apos;"/>
        <bm:data-filter enforceOperations="query" expression="(nvl(t2.received_amount,0) - nvl(t2.billing_amount,0)) &gt; 0.17"/>
    </bm:data-filters>
</bm:model>
