<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="lease_item_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_tax_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_no_tax_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="invoice_type"/>
        <bm:field name="invoice_type_n"/>
        <bm:field name="invoice_code"/>
        <bm:field name="invoice_number"/>
        <bm:field name="invoice_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="net_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="tax_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="total_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="invoice_status"/>
        <bm:field name="invoice_status_n"/>
        <bm:field name="specification"/>
        <bm:field name="note"/>
        <bm:field name="sellername"/>
        <bm:field name="bp_name"/>
        <bm:field name="nvl_invoice_number_n"/>
        <bm:field name="bp_agent_name"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select t1.* from
        (select cc.contract_number,
        (select hb.bp_name
          from hls_bp_master hb
         where hb.bp_id = cc.bp_id_tenant) bp_name,
         (select hb.bp_name
          from hls_bp_master hb
         where hb.bp_id = cc.bp_id_agent_level1) bp_agent_name,
       cc.contract_status,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'CON500_CONTRACT_STATUS'
           and v.code_value = cc.contract_status) AS contract_status_n,
       cc.lease_execution_date,
       (select i.pattern
          from con_contract_lease_item i
         WHERE i.contract_id = cc.contract_id
           AND i.equipment_type = 'MAIN') pattern,
       (select i.machine_number
          from con_contract_lease_item i
         WHERE i.contract_id = cc.contract_id
           AND i.equipment_type = 'MAIN') machine_number,
       nvl(cc.lease_item_amount, 0) lease_item_amount,
       (case
         when cc.lease_execution_date - to_date('2018-05-01', 'yyyy-mm-dd') < 0 then
          round((nvl(cc.lease_item_amount, 0) / 1.17 * 0.17), 2)
         else
          round((nvl(cc.lease_item_amount, 0) / 1.16 * 0.16), 2)
       end) lease_tax_amount, --合同别税额
       (nvl(cc.lease_item_amount, 0) - (case
         when cc.lease_execution_date - to_date('2018-05-01', 'yyyy-mm-dd') < 0 then
          round((nvl(cc.lease_item_amount, 0) / 1.17 * 0.17), 2)
         else
          round((nvl(cc.lease_item_amount, 0) / 1.16 * 0.16), 2)
       end)) lease_no_tax_amount, --合同别不含税金额
       al.invoice_type,
       (Select v.code_value_name
          From sys_code_values_v v
         Where v.code = 'INVOICE_KIND_AP'
           And v.code_value = al.invoice_type) invoice_type_n,
       al.invoice_code,
       al.invoice_number,
       al.invoice_date,
       nvl(al.net_amount, 0) net_amount, --金额
       nvl(al.tax_amount, 0) tax_amount, --税额
       nvl(al.total_amount, 0) total_amount, --税价总计
       al.invoice_status, --发票状态
       (Select v.code_value_name
          From sys_code_values_v v
         Where v.code = 'ACP517_INVOICE_STATUS'
           And v.code_value = al.invoice_status) invoice_status_n,
       al.specification, --机型-发票
       al.note, --备注
       al.sellername, --销售方
       decode(al.invoice_number,null,'Y','N') nvl_invoice_number_n
  From acp_invoice_ln al, con_contract cc
 Where al.contract_id(+) = cc.contract_id
   and cc.data_class = 'NORMAL'
   and cc.contract_status not in ('CLOSED','CANCEL')
   and nvl(al.reversed_flag, 'N') = 'N'
   and cc.lease_execution_flag ='Y') t1

                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="invoice_status_n" queryOperator="="/>
        <bm:query-field field="nvl_invoice_number_n" queryOperator="="/>
        <bm:query-field field="invoice_code" queryOperator="like"/>
        <bm:query-field field="invoice_number" queryOperator="like"/>
        <bm:query-field name="invoice_date_date_from" queryExpression="t1.invoice_date &gt;= to_date(${@invoice_date_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="invoice_date_date_to" queryExpression="t1.invoice_date &lt;= to_date(${@invoice_date_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.lease_execution_date &gt;= to_date(${@lease_execution_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.lease_execution_date &lt;= to_date(${@lease_execution_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
