<?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 * FROM( select t1.spv_company_id,
       NULL journal_header_id,
       NULL journal_line_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,
       t1.inv_object_taxpayer_type,
       t1.inv_object_taxpayer_type_desc,
       decode(t2.cf_item,'250',t1.inv_billing_object_id,t1.billing_object_id) billing_object_id,
       decode(t2.cf_item,'250',t1.inv_billing_object_code,t1.billing_object_code) billing_object_code,
       decode(t2.cf_item,'250',t1.inv_billing_object_name,t1.billing_object_name) billing_object_name,
      decode(t2.cf_item,'250',t1.inv_object_taxpayer_type,t1.object_taxpayer_type) object_taxpayer_type,
       decode(t2.cf_item,'250',t1.inv_object_taxpayer_type_desc,t1.object_taxpayer_type_desc) object_taxpayer_type_desc,
      -- t1.object_taxpayer_type_desc,
       decode(t2.cf_item,'250',t1.inv_object_tax_registry_num,t1.object_tax_registry_num) object_tax_registry_num,
       decode(t2.cf_item,'250',t1.inv_bill_object_bp_class,t1.bill_object_bp_class) bill_object_bp_class,
       decode(t2.cf_item,'250',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,'250',t1.inv_invoice_title,t1.invoice_title) invoice_title,
       decode(t2.cf_item,'250',t1.inv_bp_address_phone_num,t1.invoice_bp_address_phone_num) invoice_bp_address_phone_num,
       decode(t2.cf_item,'250',t1.inv_invoice_bp_bank_account,t1.invoice_bp_bank_account) invoice_bp_bank_account,
       decode(t2.cf_item,'250',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,'250',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,
      to_char((SELECT wm_concat(distinct c.bp_bank_account_name)
      FROM csh_transaction c
     WHERE c.transaction_id IN
           (SELECT coo.csh_transaction_id
              FROM csh_write_off coo
             WHERE coo.cashflow_id = t2.cashflow_id))) account_name,
       decode(t1.lease_channel,'01',null,t2.principal) principal,
       decode(t1.lease_channel,'01',null,t2.interest) interest,
       t2.received_amount,
       decode(t1.lease_channel,'01',null,t2.received_principal) received_principal,
       decode(t1.lease_channel,'01',null,t2.received_interest) 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.write_off_flag,
       t2.overdue_max_days,
       t1.bp_id_agent_level1,
       t1.bp_id_agent_level1_n,
       t1.termination_date,
       t1.lease_channel,
       t2.cf_direction,
       t2.billing_status,
       t2.cf_status
  from acr_invoice_contract_v t1, ACR_INVOICE_CONTRACT_CF_V t2
   where
   t1.contract_id=t2.contract_id
        and    ((decode(t2.cf_item,
                '250',
                t1.inv_object_taxpayer_type,
                t1.object_taxpayer_type) = 'SMALL_SCALE_TAXPAYER' and
       to_char(t2.due_date,'yyyy-mm') <= to_char(SYSDATE,'yyyy-mm')) or t2.write_off_flag = 'FULL')
   AND cf_item<> 9
   AND cf_status='RELEASE'
   and nvl(t2.billing_status,'NOT') != 'FULL'
   and t2.cf_direction='INFLOW'
  and ((decode(t2.cf_item,
                '250',
                t1.inv_object_taxpayer_type,
                t1.object_taxpayer_type) <> 'SMALL_SCALE_TAXPAYER' and
       received_amount>0) OR  (decode(t2.cf_item,
                '250',
                t1.inv_object_taxpayer_type,
                t1.object_taxpayer_type)= 'SMALL_SCALE_TAXPAYER'))
                ORDER BY t1.contract_id,t2.due_date )
  UNION ALL
    select t1.spv_company_id,
       t2.journal_header_id,
       t2.journal_line_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,
       t1.inv_object_taxpayer_type,
       t1.inv_object_taxpayer_type_desc,
       (t1.billing_object_id) billing_object_id,
       (t1.billing_object_code) billing_object_code,
       (t1.billing_object_name) billing_object_name,
      (t1.object_taxpayer_type) object_taxpayer_type,
       (t1.object_taxpayer_type_desc) object_taxpayer_type_desc,
      -- t1.object_taxpayer_type_desc,
       (t1.object_tax_registry_num) object_tax_registry_num,
       (t1.bill_object_bp_class) bill_object_bp_class,
       (t1.bill_object_bp_class_desc) bill_object_bp_class_desc,
       t1.tax_type_vat,
       t1.description,
       (t1.invoice_title) invoice_title,
       (t1.invoice_bp_address_phone_num) invoice_bp_address_phone_num,
       (t1.invoice_bp_bank_account) invoice_bp_bank_account,
       (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,
       (t1.invoice_type_desc) invoice_type_desc,
       NULL cashflow_id,
       9 cf_item,
       (SELECT t.description FROM hls_cashflow_item t WHERE t.cf_item=9) cf_item_desc,
       null times,
       t2.transaction_date last_received_date,
       NULL due_date,
       NULL due_amount,
      (SELECT c.bp_bank_account_name
             FROM csh_transaction c
             WHERE c.transaction_id =t2.csh_transaction_id) account_name,
       --NULL account_name,
       NULL principal,
       NULL interest,
       t2.amount_dr received_amount,
       NULL received_principal,
       NULL received_interest,
       0 billing_amount,
       NULL billing_principal,
       NULL billing_interest,
       NULL vat_due_amount,
       NULL vat_principal,
       NULL vat_interest,
       NULL net_due_amount,
       NULL net_principal,
       NULL net_interest,
       NULL currency,
       NULL currency_desc,
       NULL exchange_rate,
       NULL exchange_rate_type,
       NULL exchange_rate_type_desc,
       NULL exchange_rate_quotation,
       NULL exchange_rate_quotation_desc,
       NULL overdue_status,
       NULL write_off_flag,
       NULL overdue_max_days,
       t1.bp_id_agent_level1,
       t1.bp_id_agent_level1_n,
       t1.termination_date,
       t1.lease_channel,
       NULL cf_direction,
       NULL billing_status,
       NULL cf_status
  from acr_invoice_contract_v t1, acr_invoice_penalty_v t2
   where
     t2.contract_id=t1.contract_id
      AND (NOT EXISTS (SELECT 1
                            FROM acr_invoice_ln l
                                WHERE l.ref_n04 = t2.journal_line_id
                                GROUP BY journal_line_id
                                HAVING SUM(l.total_amount)>0 )
                                )

   )vv
 #WHERE_CLAUSE#

 ]]></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="journal_header_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="journal_line_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <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="account_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <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="inv_object_taxpayer_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="inv_object_taxpayer_type"/>
        <bm:field name="inv_object_taxpayer_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="inv_object_taxpayer_type_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"/>
        <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"/>
        <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"/>
        <bm:field name="overdue_max_days" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OVERDUE_MAX_DAYS"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="termination_date"/>
        <bm:field name="lease_channel"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="billing_object_name" queryExpression="vv.billing_object_name = ${@billing_object_name}"/>
        <bm:query-field name="cf_item" queryExpression="vv.cf_item = ${@cf_item}"/>
        <bm:query-field name="due_date_from" queryExpression="vv.due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="vv.due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="overdue_max_days_from" queryExpression="vv.overdue_max_days &gt;= ${@overdue_max_days_from}"/>
        <bm:query-field name="overdue_max_days_to" queryExpression="vv.overdue_max_days &lt;= ${@overdue_max_days_to}"/>
        <bm:query-field field="overdue_status" queryExpression="vv.overdue_status=${@overdue_status} and vv.write_off_flag != &apos;FULL&apos;"/>
        <bm:query-field field="bill_object_bp_class" queryOperator="="/>
        <bm:query-field name="invoice_type_desc" queryExpression="vv.invoice_type_desc like ${@invoice_type_desc}"/>
        <bm:query-field name="invoice_title" queryExpression="vv.invoice_title like ${@invoice_title}"/>
        <bm:query-field name="project_number_from" queryExpression="vv.project_number &gt;=${@project_number_from}"/>
        <bm:query-field name="project_number_to" queryExpression="vv.project_number &lt;=${@project_number_to}"/>
        <bm:query-field name="contract_number" queryExpression="vv.contract_number  like ${@contract_number}"/>
        <bm:query-field name="bp_code_tenant_from" queryExpression="vv.billing_object_code &gt;=${@bp_code_tenant_from}"/>
        <bm:query-field name="bp_code_tenant_to" queryExpression="vv.billing_object_code &lt;=${@bp_code_tenant_to}"/>
        <bm:query-field name="lease_start_date_from" queryExpression="vv.inception_of_lease &gt;= to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="last_received_date_from" queryExpression="vv.last_received_date &gt;= to_date(${@last_received_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="vv.inception_of_lease &lt;= to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="last_received_date_to" queryExpression="vv.last_received_date &lt;= to_date(${@last_received_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="document_type" queryExpression="vv.document_type=${@document_type}"/>
        <bm:query-field name="billing_method" queryExpression="vv.billing_method=${@billing_method}"/>
        <bm:query-field name="prj_search_term_1" queryExpression="vv.prj_search_term_1=${@prj_search_term_1}"/>
        <bm:query-field name="prj_search_term_2" queryExpression="vv.prj_search_term_2=${@prj_search_term_2}"/>
        <bm:query-field name="con_search_term_1" queryExpression="vv.con_search_term_1=${@con_search_term_1}"/>
        <bm:query-field name="con_search_term_2" queryExpression="vv.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="vv.project_name like &apos;%&apos;||${@project_name}||&apos;%&apos; "/>
        <bm:query-field name="contract_name" queryExpression="vv.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos; "/>
        <bm:query-field name="document_type" queryExpression="vv.document_type=${@document_type}"/>
        <bm:query-field name="contract_status" queryExpression="vv.contract_status = ${@contract_status}"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="vv.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="bp_id_agent_level1_n" queryExpression="vv.bp_id_agent_level1_n like ${@bp_id_agent_level1_n}"/>
        <bm:query-field name="termination_date_from" queryExpression="to_date(t1.termination_date,&apos;yyyy-mm-dd&apos;) &gt;= to_date(${@termination_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="termination_date_to" queryExpression="to_date(t1.termination_date,&apos;yyyy-mm-dd&apos;) &lt;= to_date(${@termination_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_channel" queryExpression="t1.lease_channel=${@lease_channel}"/>
        <bm:query-field name="project_number" queryExpression="t1.project_number like &apos;%&apos;|| ${@project_number}||&apos;%&apos;"/>
        <bm:query-field name="inv_object_taxpayer_type" queryExpression="vv.inv_object_taxpayer_type =${@inv_object_taxpayer_type}"/>
        <bm:query-field name="object_taxpayer_type" queryExpression="vv.object_taxpayer_type =${@object_taxpayer_type}"/>
    </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 = vv.contract_id"/>-->
<!--        <bm:data-filter name="contract_status" enforceOperations="query" expression="vv.contract_status in(&apos;NEW&apos;,&apos;INCEPT&apos;,&apos;ETING&apos;,&apos;REPURING&apos;,&apos;CLOSED&apos;)"/>-->

    </bm:data-filters>
</bm:model>
