<?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 nvl(ccli.machine_number,ccli.machine_number_70)
                              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,
                             ccc.due_date,
                             --收款日
                             t.transaction_date,
                             -- ccc.due_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) + cwo.write_off_due_amount) As due_amount,
                             --t.transaction_num,
                             cwo.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,
                             c.BUSINESS_TYPE,
                             (select hbt.DESCRIPTION
                              from hls_business_type hbt
                              where hbt.BUSINESS_TYPE = c.BUSINESS_TYPE)             BUSINESS_TYPE_N,
                              c.large_balance,
                              (select v.code_value_name
                                from sys_code_values_v v
                               where v.code = 'SECONDARY_LEASE'
                                 and v.code_value = c.large_balance) large_balance_n,
                                 c.branch_code,
                               (Select v.code_value_name
                              From sys_code_values_v v
                             Where v.code = 'BRANCH_CODE_SZMP'
                               And v.code_enabled_flag = 'Y'
                               And v.code_value_enabled_flag = 'Y'
                               And v.code_value = c.branch_code) branch_code_n
                         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_status = 'RELEASE'
			                   --modify by 18938 I-7158
			                   And ccc.cf_status in ('RELEASE','BLOCK')
                               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#
              order by extra_nam, bp_id_tenant, contract_id,times,cf_item, due_amount desc, transaction_date,write_off_date
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t.contract_number like &apos;%&apos; || ${@contract_number} || &apos;%&apos;"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like &apos;%&apos; || ${@bp_name} || &apos;%&apos;"/>
        <bm:query-field name="transaction_date_from" queryExpression="trunc(t.transaction_date) &gt;=  trunc(to_date(${@transaction_date_from},'yyyy-mm-dd'))"/>
        <bm:query-field name="transaction_date_to" queryExpression="trunc(t.transaction_date) &lt;=  trunc(to_date(${@transaction_date_to},'yyyy-mm-dd'))"/>
        <bm:query-field name="journal_date_from" queryExpression="trunc(t.write_off_date) &gt;=  trunc(to_date(${@journal_date_from},'yyyy-mm-dd'))"/>
        <bm:query-field name="journal_date_to" queryExpression="trunc(t.write_off_date) &lt;=  trunc(to_date(${@journal_date_to},'yyyy-mm-dd'))"/>
        <bm:query-field name="transaction_num" queryExpression="t.transaction_num like &apos;%&apos; || ${@transaction_num} || &apos;%&apos;"/>
        <bm:query-field name="cf_item_n" queryExpression="t.cf_item_n like &apos;%&apos; || ${@cf_item_n} || &apos;%&apos;"/>
        <bm:query-field name="extra_nam" queryExpression="t.extra_nam like &apos;%&apos; || ${@extra_nam} || &apos;%&apos;"/>
        <bm:query-field name="business_type" queryExpression="t.business_type = ${@business_type}"/>
        <bm:query-field name="large_balance" queryExpression="t.large_balance = ${@large_balance}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="exists (select 1 from aut_trx_user_authorize a1, aut_owner_user_authorize a2 WHERE a1.trx_category = 'CONTRACT' and a1.trx_id = t.contract_id AND trunc(sysdate) between a1.start_date AND nvl(a1.end_date, trunc(sysdate)) and a1.user_id = a2.owner_user_id AND a1.trx_category = a2.trx_category AND a2.authorized_user_id = ${/session/@user_id} AND trunc(sysdate) between a2.start_date and nvl(a2.end_date, trunc(sysdate)))"/>
        <bm:data-filter enforceOperations="query" expression="( (decode(${/session/@user_id}, 10600, 1, 2) = 1
                                                   and exists (select 1 from con_contract c, hls_bp_master hb where c.contract_id = t.contract_id
                                                   and hb.bp_id = c.bp_id_agent_level1
                                                   and hb.bp_code in  ('D00000028','D00000035','D00000016','D00000021','D00000039','D00000025','D00000034','D00000017','D00000018','D00000024','D00000032','D00000009','D00000012','D00000014','D00000015','D00000023')) )
                                                   or (decode(${/session/@user_id}, 10600, 1, 2) = 2) )"/>
    </bm:data-filters>
</bm:model>
