<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
              Select cwo.cashflow_id,
                   t.transaction_num,
                   cwo.write_off_id,
                   (Select v.code_value_name
                      From sys_code_values_v v
                     Where v.code = 'RECEIPT_TYPE'
                       And v.code_value = t.receipt_type) transaction_type,
                   c.contract_id,
                   cwo.times,
                   ccc.cf_item,
                   (Select h.description
                      From hls_cashflow_item h
                     Where h.cf_item = ccc.cf_item) cf_item_n,
                   --代理店简称
                   (Select h.extra_nam
                      From hls_bp_master h
                     Where h.bp_id = c.bp_id_agent_level1) extra_nam,
                   --合同编号
                   c.contract_number,
                   --机号
                   (Select ccli.machine_number
                      From con_contract_lease_item ccli
                     Where ccli.contract_id = c.contract_id) machine_number,
                   --承租人编号，名称，身份证号
                   h.bp_code bp_id_tenant,
                   h.bp_name,
                   decode(h.bp_class, 'NP', h.id_card_no, h.social_code) id_card_no,
                   --合同总额
                   nvl(c.down_payment, 0) + nvl(c.total_rental, 0) total_amount,
                   to_char(ccc.due_date,'yyyy-mm-dd') due_date,
                   --收款日
                    to_char(t.transaction_date,'yyyy-mm-dd') transaction_date,

                   (ccc.due_amount - nvl((Select Sum(tn.write_off_due_amount)
                                           From csh_write_off tn
                                          Where tn.cashflow_id = cwo.cashflow_id
                                            And tn.creation_date <= cwo.creation_date),
                                         0) + cwo.write_off_due_amount) As due_amount,
                   to_char(cwo.write_off_date,'yyyy-mm-dd')write_off_date,
                   --实收金额
                   cwo.write_off_due_amount received_amount,

                   (ccc.due_amount - nvl((Select Sum(tn.write_off_due_amount)
                                           From csh_write_off tn
                                          Where tn.cashflow_id = cwo.cashflow_id
                                            And tn.creation_date <= cwo.creation_date),
                                         0)) As unreceived_amount,
                   nvl(cwo.trans_pently_flag, 'N') trans_pently_flag
              From con_contract_cashflow ccc,
                   con_contract          c,
                   hls_bp_master         h,
                   csh_write_off         cwo,
                   csh_transaction       t
             Where cwo.cashflow_id = ccc.cashflow_id
               And ccc.contract_id = c.contract_id
               And cwo.csh_transaction_id = t.transaction_id
               And nvl(cwo.reversed_flag, 'N') = 'N'
               And h.bp_id = c.bp_id_tenant
              -- And cwo.write_off_type In ('RECEIPT_CREDIT', 'DEPOSIT_CREDIT')
               And ccc.cf_item =9
               And ccc.cf_status = 'RELEASE'
               And cwo.write_off_due_amount > 0
             Order By c.contract_id,
                      ccc.times,
                      ccc.cf_item,
                      t.transaction_date,
                      cwo.write_off_id ) t
               #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="t.contract_id = ${@contract_id}"/>
    </bm:query-fields>
</bm:model>
