<?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 t1.*
                FROM (SELECT to_char(hx.bank_receipt_date, 'yyyy-MM-dd') bank_receipt_date,
                hx.slip_trx_id,
                hx.receipt_no,
                hx.payer_name,
                hx.payer_number,
                hx.payer_bank,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                (select bp_name
                from hls_bp_master
                where bp_name = hx.payer_name)
                else
                ' '
                end) bp_name,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                (Select u.unit_name
                From exp_org_unit_v u
                Where u.unit_id =
                (select v.unit_id
                from hls_bp_master v
                where v.enabled_flag = 'Y'
                and v.bp_id =
                (select bp_id
                from hls_bp_master
                where bp_name = hx.payer_name)))
                else
                ' '
                end) extra_nam,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                '否'
                else
                ' '
                end) paid_byother_flag_desc,
                hx.total_amount,
                hx.zy,
                hx.remark,
                hx.bank_num,
                'N' re_flag,
                NULL AS bank_num_r,
                NULL AS bank_receipt_date_r,
                NULL AS payer_number_r,
                NULL AS payer_name_r,
                NULL AS payer_bank_r,
                NULL AS total_amount_r,
                COALESCE((SELECT v.code_value_name
                FROM sys_code_values_v           v,
                csh_transaction_import_temp ct
                WHERE v.code = 'DS_COLLECTION_CLASSES'
                AND v.code_value = ct.collection_classes
                and ct.bp_bank_name = hx.payer_name),
                '非保证金类') AS collection_classes_desc,
                (SELECT ct.guarantor_name_duty
                FROM csh_transaction_import_temp ct
                WHERE ct.bp_bank_name = hx.payer_name) guarantor_name_duty
                FROM hlcm_ele_abc_bank_slip_trx hx
                where hx.create_trx_flag = 'Y'
                and not exists (select 1
                from hlcm_ele_trx_import_itfc a
                where hx.receipt_no = a.receipt_no)

                union all

                SELECT to_char(hx.bank_receipt_date, 'yyyy-MM-dd') bank_receipt_date,
                hx.slip_trx_id,
                hx.receipt_no,
                hx.payer_name,
                hx.payer_number,
                hx.payer_bank,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                (select bp_name
                from hls_bp_master
                where bp_name = hx.payer_name)
                else
                ' '
                end) bp_name,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                (Select u.unit_name
                From exp_org_unit_v u
                Where u.unit_id =
                (select v.unit_id
                from hls_bp_master v
                where v.enabled_flag = 'Y'
                and v.bp_id =
                (select bp_id
                from hls_bp_master
                where bp_name = hx.payer_name)))
                else
                ' '
                end) extra_nam,
                (case
                when (select count(bp_name)
                from hls_bp_master
                where bp_name = hx.payer_name) = 1 then
                '否'
                else
                ' '
                end) paid_byother_flag_desc,
                hx.total_amount,
                hx.zy,
                hx.remark,
                hx.bank_num,
                'Y' re_flag,
                a.bank_num bank_num_r,
                to_char(a.bank_receipt_date, 'yyyy-MM-dd') bank_receipt_date_r,
                a.payer_number payer_number_r,
                a.payer_name payer_name_r,
                a.bank_name payer_bank_r,
                a.total_amount total_amount_r,
                COALESCE((SELECT v.code_value_name
                FROM sys_code_values_v           v,
                csh_transaction_import_temp ct
                WHERE v.code = 'DS_COLLECTION_CLASSES'
                AND v.code_value = ct.collection_classes
                and ct.bp_bank_name = hx.payer_name),
                '非保证金类') AS collection_classes_desc,
                (SELECT ct.guarantor_name_duty
                FROM csh_transaction_import_temp ct
                WHERE ct.bp_bank_name = hx.payer_name) guarantor_name_duty
                FROM hlcm_ele_abc_bank_slip_trx hx, hlcm_ele_trx_import_itfc a
                where hx.create_trx_flag = 'Y'
                and a.receipt_no = hx.receipt_no
                order by receipt_no desc) t1
                #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>

    </bm:operations>

    <bm:query-fields>
        <bm:query-field name="exchange_hour_from" queryExpression="t1.bank_receipt_date &gt;= ${@exchange_hour_from} "/>
        <bm:query-field name="exchange_hour_to" queryExpression="t1.bank_receipt_date &lt;= ${@exchange_hour_to} "/>

        <bm:query-field name="money_from" queryExpression="t1.total_amount &gt;= ${@money_from} "/>
        <bm:query-field name="money_to" queryExpression="t1.total_amount &lt;= ${@money_to} "/>
    </bm:query-fields>




</bm:model>
