<?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 hc.import_itfc_id,
                hc.bank_num,
                hc.beneficiary_number,
                to_char(hc.bank_receipt_date, 'yyyy-MM-dd') bank_receipt_date,
                hc.receipt_no,
                hc.payer_number,
                hc.payer_name,
                hc.bank_name,
                hc.beneficiary_name,
                decode(hc.paid_byother_flag_desc, 'Y', '是', 'N', '否') paid_byother_flag_desc,
                decode(hc.collection_classes_desc,
                'DEPOSIT',
                '保证金类',
                'NON_DEPOSIT',
                '非保证金类',
                'COMBINED',
                '混合类',
                'REFUND',
                '日立为优质客户付租金') collection_classes_desc,
                hc.total_amount,
                hc.zy,
                hc.remark,
                hc.extra_nam,
                decode(hc.guarantor_name_duty, 'Y', '是', 'N', '否') guarantor_name_duty,
                hc.create_trx_flag,
                hc.creation_date,
                hc.created_by,
                hc.last_update_date,
                hc.last_updated_by,
                hc.bp_id,
                hc.bank_account_id,
                hc.bp_category,
                '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 bank_name_r,
                NULL AS total_amount_r
                FROM hlcm_ele_trx_import_itfc hc
                where hc.create_trx_flag = 'Y'
                and not exists (select 1
                from hlcm_ele_abc_bank_slip_trx a
                where a.receipt_no = hc.receipt_no)
                union all
                SELECT hc.import_itfc_id,
                hc.bank_num,
                hc.beneficiary_number,
                to_char(hc.bank_receipt_date, 'yyyy-MM-dd') bank_receipt_date,
                hc.receipt_no,
                hc.payer_number,
                hc.payer_name,
                hc.bank_name,
                hc.beneficiary_name,
                decode(hc.paid_byother_flag_desc, 'Y', '是', 'N', '否') paid_byother_flag_desc,
                decode(hc.collection_classes_desc,
                'DEPOSIT',
                '保证金类',
                'NON_DEPOSIT',
                '非保证金类',
                'COMBINED',
                '混合类',
                'REFUND',
                '日立为优质客户付租金') collection_classes_desc,
                hc.total_amount,
                hc.zy,
                hc.remark,
                hc.extra_nam,
                decode(hc.guarantor_name_duty, 'Y', '是', 'N', '否') guarantor_name_duty,
                hc.create_trx_flag,
                hc.creation_date,
                hc.created_by,
                hc.last_update_date,
                hc.last_updated_by,
                hc.bp_id,
                hc.bank_account_id,
                hc.bp_category,
                '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.payer_bank bank_name_r,
                a.total_amount total_amount_r
                FROM hlcm_ele_trx_import_itfc hc, hlcm_ele_abc_bank_slip_trx a
                where hc.create_trx_flag = 'Y'
                and a.receipt_no = hc.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>
