<?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="transaction_id"/>
        <bm:field name="contract_id" />
        <bm:field name="contract_number"/>
        <bm:field name="bp_code"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_code"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="division_n"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="transaction_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="transaction_num"/>
        <bm:field name="zuonr"/>
        <bm:field name="re_kunnr"/>
        <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_bank_account_name"/>
        <bm:field name="description"/>
        <bm:field name="ref_v05"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="contract_status"/>
        <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="journal_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="unwrite_off_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="transaction_amount_y" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="transaction_num_y"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
       select * from (select t.*,tt.transaction_num transaction_num_y,tt.transaction_amount transaction_amount_y from (SELECT ct.transaction_id,
       cc.contract_id,
       cc.contract_number, ---预收款所挂合同
       (SELECT h.bp_code
          FROM hls_bp_master h
         where h.bp_id = cc.bp_id_tenant) bp_code, ---客户编号
       (SELECT h.bp_name
          FROM hls_bp_master h
         where h.bp_id = cc.bp_id_tenant) bp_name, ---客户名称
       (SELECT h.bp_code
          FROM hls_bp_master h
         where h.bp_id = cc.bp_id_agent_level1) bp_agent_code, ---代理商编号
       (SELECT h.bp_name
          FROM hls_bp_master h
         where h.bp_id = cc.bp_id_agent_level1) bp_agent_name, ---代理商名称
         cc.bp_id_agent_level1,
       (select hd.description
          from hls_division hd
         where hd.enabled_flag = 'Y'
           and hd.division = cc.division) AS division_n,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = cc.business_type) AS business_type_n,
       (select hc.description
          from hls_lease_channel hc
         where hc.enabled_flag = 'Y'
           and hc.lease_channel = cc.lease_channel) AS lease_channel_n,
       (SELECT m.pattern
          FROM con_contract_lease_item m
         where cc.contract_id = m.contract_id
           and m.equipment_type = 'MAIN') AS pattern,
       (SELECT m.machine_number
          FROM con_contract_lease_item m
         where cc.contract_id = m.contract_id
           and m.equipment_type = 'MAIN') As machine_number,
       ct.transaction_amount, ---预收款金额
       ct.transaction_num, ---预收款现金事务编号
       ct.ref_v05,
       ct.zuonr,
       (SELECT h.re_kunnr
          FROM hls_bp_master h
         where h.bp_id = cc.bp_id_tenant) re_kunnr, ---客户sap编码
       ct.transaction_date, ---汇款日期
       ct.bp_bank_account_name, ---汇款人
       ct.description, ---汇款摘要
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         where v.code = 'CON500_CONTRACT_STATUS'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code_value = cc.contract_status) contract_status_n,
           cc.contract_status,
       cc.et_date, ---结清日期
       (SELECT cwo.journal_date
                  FROM csh_write_off cwo
                 where ct.transaction_id = cwo.subsequent_csh_trx_id
                 and cwo.reversed_flag = 'N') journal_date, ---记账日期
                 (nvl(ct.transaction_amount,0)-nvl(ct.write_off_amount,0)-nvl(ct.returned_amount,0)) unwrite_off_amount,
       ct.source_csh_trx_id
  FROM csh_transaction ct, con_contract cc
 WHERE cc.contract_id = nvl(ct.ref_contract_id, ct.assigns_contract_id)
   and ct.transaction_category = 'BUSINESS'
   and ct.transaction_type = 'ADVANCE_RECEIPT'
   and ct.reversed_flag = 'N'
   and ct.write_off_flag <> 'FULL'
   and cc.data_class = 'NORMAL') t
   left join CSH_TRANSACTION tt on t.source_csh_trx_id = tt.transaction_id) 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_code" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="division_n" queryOperator="="/>
        <bm:query-field field="business_type_n" queryOperator="="/>
        <bm:query-field field="lease_channel_n" queryOperator="="/>
        <bm:query-field field="transaction_num" queryOperator="like"/>
        <bm:query-field field="transaction_num_y" queryOperator="like"/>
        <bm:query-field field="bp_bank_account_name" queryOperator="like"/>

        <bm:query-field field="contract_status" queryOperator="="/>
        <bm:query-field field="bp_id_agent_level1" queryOperator="="/>
        <bm:query-field name="transaction_amount_from" queryExpression="t1.transaction_amount &gt;= ${@transaction_amount_from}"/>
        <bm:query-field name="transaction_amount_to"  queryExpression="t1.transaction_amount &lt;= ${@transaction_amount_to}"/>
        <bm:query-field name="journal_date_from" queryExpression="t1.journal_date &gt;= to_date(${@journal_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="journal_date_to"  queryExpression="t1.journal_date &lt;= to_date(${@journal_date_to},'yyyy-mm-dd')"/>
    </bm:query-fields>
</bm:model>
