csh_transaction_query.lwm 21.3 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng
    $Date: 2018-09-05 16:06:45
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="CSH_TRANSACTION" defaultOrderBy="t1.creation_date desc,t1.transaction_num desc">
    <bm:fields>
        <bm:field name="transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TRANSACTION_ID" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_ID"/>
        <bm:field name="transaction_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_NUM" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_NUM"/>
        <bm:field name="transaction_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_CATEGORY" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_CATEGORY"/>
        <bm:field name="transaction_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_TYPE" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_TYPE"/>
        <bm:field name="transaction_type_desc" forInsert="false" forUpdate="false" prompt="HLS.TRANSACTION_TYPE" expression="(select v.code_value_name from sys_code_values_v v where v.code = 'CSH511_TRANSACTION_TYPE' and v.code_value =t1.transaction_type)"/>
        <bm:field name="receivables_type" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="RECEIVABLES_TYPE" lookupField="receivables_type_desc" physicalName="RECEIVABLES_TYPE" prompt="收款类型"/>
        <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date" physicalName="TRANSACTION_DATE" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_DATE"/>
        <bm:field name="penalty_calc_date" databaseType="DATE" datatype="java.util.Date" physicalName="PENALTY_CALC_DATE" prompt="CSH511.CSH_TRANSACTION.PENALTY_CALC_DATE"/>
        <bm:field name="bank_slip_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BANK_SLIP_NUM" prompt="CSH511.CSH_TRANSACTION.BANK_SLIP_NUM"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="CSH511.CSH_TRANSACTION.COMPANY_ID"/>
        <bm:field name="internal_period_num" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INTERNAL_PERIOD_NUM" prompt="CSH511.CSH_TRANSACTION.INTERNAL_PERIOD_NUM"/>
        <bm:field name="period_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PERIOD_NAME" prompt="HLS.ACCOUNT_PERIOD_NAME"/>
        <bm:field name="payment_method_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PAYMENT_METHOD_ID" prompt="HLS.PAYMENT_METHOD"/>
        <bm:field name="distribution_set_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DISTRIBUTION_SET_ID" prompt="CSH511.CSH_TRANSACTION.DISTRIBUTION_SET_ID"/>
        <bm:field name="cashflow_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="CASHFLOW_AMOUNT" prompt="CSH511.CSH_TRANSACTION.CASHFLOW_AMOUNT"/>
        <bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_CODE" prompt="HLS.CURRENCY"/>
        <bm:field name="transaction_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="TRANSACTION_AMOUNT" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_AMOUNT"/>
        <bm:field name="exchange_rate_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_TYPE" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="exchange_rate_quotation" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_QUOTATION" prompt="CSH511.CSH_TRANSACTION.EXCHANGE_RATE_QUOTATION"/>
        <bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Double" physicalName="EXCHANGE_RATE" prompt="HLS.EXCHANGE_RATE"/>
        <bm:field name="bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANK_ACCOUNT_ID" prompt="CSH511.CSH_TRANSACTION.BANK_ACCOUNT_ID"/>
        <bm:field name="bp_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_CATEGORY" prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID" prompt="CSH511.CSH_TRANSACTION.BP_ID"/>
        <bm:field name="bp_bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_BANK_ACCOUNT_ID" prompt="CSH511.CSH_TRANSACTION.BP_BANK_ACCOUNT_ID"/>
        <bm:field name="bp_bank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_BANK_ACCOUNT_NUM" prompt="CSH511.BANK_ACCOUNT_NUM"/>
        <bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION" prompt="HLS.NOTE"/>
        <bm:field name="handling_charge" databaseType="NUMBER" datatype="java.lang.Double" physicalName="HANDLING_CHARGE" prompt="CSH511.CSH_TRANSACTION.HANDLING_CHARGE"/>
        <bm:field name="posted_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="POSTED_FLAG" prompt="CSH511.CSH_TRANSACTION.POSTED_FLAG"/>
        <bm:field name="reversed_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REVERSED_FLAG" prompt="CSH511.CSH_TRANSACTION.REVERSED_FLAG"/>
        <bm:field name="reversed_date" databaseType="DATE" datatype="java.util.Date" physicalName="REVERSED_DATE" prompt="CSH511.CSH_TRANSACTION.REVERSED_DATE"/>
        <bm:field name="returned_flag" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="CSH510_RETURN_SELECT" lookupField="returned_flag_desc" physicalName="RETURNED_FLAG" prompt="CSH511.CSH_TRANSACTION.RETURNED_FLAG"/>
        <bm:field name="returned_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RETURNED_AMOUNT" prompt="CSH511.CSH_TRANSACTION.RETURNED_AMOUNT"/>
        <bm:field name="write_off_flag" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="write_off_flag_desc" expression="(select v.code_value_name from sys_code_values_v v where v.code = 'CON_WRITE_OFF_FLAG' and v.code_value =t1.write_off_flag)" forUpdate="false" forInsert="false"/>
        <bm:field name="write_off_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WRITE_OFF_AMOUNT" prompt="CSH511.CSH_TRANSACTION.WRITE_OFF_AMOUNT"/>
        <bm:field name="full_write_off_date" databaseType="DATE" datatype="java.util.Date" physicalName="FULL_WRITE_OFF_DATE" prompt="CSH511.CSH_TRANSACTION.FULL_WRITE_OFF_DATE"/>
        <bm:field name="twin_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TWIN_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.TWIN_CSH_TRX_ID"/>
        <bm:field name="return_from_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RETURN_FROM_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.RETURN_FROM_CSH_TRX_ID"/>
        <bm:field name="reversed_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REVERSED_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.REVERSED_CSH_TRX_ID"/>
        <bm:field name="source_csh_trx_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_CSH_TRX_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_TYPE"/>
        <bm:field name="source_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_ID"/>
        <bm:field name="source_doc_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_DOC_CATEGORY"/>
        <bm:field name="source_doc_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_DOC_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_TYPE"/>
        <bm:field name="source_doc_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_DOC_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_ID"/>
        <bm:field name="source_doc_line_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_DOC_LINE_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_LINE_ID"/>
        <bm:field name="create_je_mothed" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_MOTHED"/>
        <bm:field name="create_je_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_FLAG"/>
        <bm:field name="gld_interface_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="GLD_INTERFACE_FLAG" prompt="CSH511.CSH_TRANSACTION.GLD_INTERFACE_FLAG"/>
        <bm:field name="bp_code" expression="(select d.bp_code from hls_bp_master d where d.bp_id=t1.bp_id)" forInsert="false" forUpdate="false" prompt="HLS.BP_CODE"/>
        <bm:field name="bp_name" expression="(select d.bp_name from hls_bp_master d where d.bp_id=t1.bp_id)" forInsert="false" forUpdate="false" prompt="HLS.BP_NAME"/>
        <bm:field name="payment_method_display" expression="(select cp.description from csh_payment_method cp where cp.payment_method_id=t1.payment_method_id)" forInsert="false" forUpdate="false" prompt="HLS.PAYMENT_METHOD"/>
        <bm:field name="currency_name" expression="(select gc.currency_name from gld_currency_vl gc where gc.currency_code=t1.currency_code)" forInsert="false" forUpdate="false" prompt="HLS.CURRENCY"/>
        <bm:field name="bp_category_display" expression="(select b.description from hls_bp_category b where b.bp_category=t1.bp_category)" forInsert="false" forUpdate="false" prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
        <bm:field name="exchange_rate_type_display" expression="(select tv.type_name from gld_exchangerate_types_vl tv where tv.type_code=t1.exchange_rate_type)" forInsert="false" forUpdate="false" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="transaction_functional_amount" expression="gld_exchange_rate_pkg.get_currency_exchange(t1.transaction_amount,t1.exchange_rate,t1.exchange_rate_quotation)" forInsert="false" forUpdate="false" prompt="HLS.FUNCTIONAL_AMOUNT"/>
        <bm:field name="bank_account_code" expression="(select a.bank_account_code from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_CODE"/>
        <bm:field name="bank_account_name" expression="(select a.bank_account_name from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
        <bm:field name="bank_account_num" expression="(select a.bank_account_num from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NUM"/>
        <bm:field name="bp_bank_account_code" expression="(select a.bank_account_code  from hls_bp_master_bank_account a  where a.bank_account_id = t1.bp_bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BP_BANK_ACCOUNT_CODE"/>
        <bm:field name="bp_bank_account_name" prompt="CSH511.BANK_ACCOUNT_NAME"/>
        <bm:field name="functional_currency_code" expression="(select gb.functional_currency_code   from fnd_companies fc, gld_set_of_books gb  where fc.company_id = ${/session/@company_id}    and fc.set_of_books_id = gb.set_of_books_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="rate_method_code" expression="(select t.rate_method_code from gld_exchangerate_types t where t.type_code=t1.exchange_rate_type)" forInsert="false" forUpdate="false"/>
        <bm:field name="zero_amounts_allowed" expression="(select d.zero_amounts_allowed from csh_bank_account_v d where d.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="receivables_type_desc" expression="decode(t1.receivables_type,null,&apos;其他&apos;,t1.receivables_type)" forInsert="false" forUpdate="false" prompt="收款类型"/>
        <bm:field name="unwrite_off_amount" datatype="java.lang.Double" expression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0))" forInsert="false" forUpdate="false" prompt="HLS.UNWRITEOFF_AMOUNT"/>
        <bm:field name="precision" expression="(select g.precision from gld_currency g where g.currency_code =t1.currency_code)" forInsert="false" forUpdate="false"/>
        <bm:field name="reverse_amount" expression="(-1*t1.transaction_amount)" forInsert="false" forUpdate="false" prompt="CSH511.REVERSE_AMOUNT"/>
        <bm:field name="user_desc" expression="(select su.description from sys_user su where su.user_id=t1.created_by)" forInsert="false" forUpdate="false"/>
        <bm:field name="auto_filt_penalty" expression="&apos;N&apos;" forInsert="false" forUpdate="false" prompt="CSH513.AUTO_FILT_PENALTY"/>
        <bm:field name="auto_filt_prinicipal_interest" expression="&apos;Y&apos;" forInsert="false" forUpdate="false" prompt="CSH513.AUTO_FILT_PRINICIPAL_INTEREST"/>
        <bm:field name="returned_flag_desc" forInsert="false" forUpdate="false" prompt="CSH511.CSH_TRANSACTION.RETURNED_FLAG"/>
        <bm:field name="unallocate_amount" expression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0) -nvl((select sum(i.write_off_amount) from csh_write_off_interface i where i.trx_interface_id = t1.transaction_id and i.record_type is not null),0))"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="status" expression="nvl(t1.status,&apos;NEW&apos;)" forInsert="false" forUpdate="false"/>
        <bm:field name="ref_contract_id"/>
        <bm:field name="csh_bp_name"/>
        <bm:field name="contract_number" expression="(select cc.contract_number from con_contract cc where cc.contract_id = t1.ref_contract_id)"/>
        <!--add by chenlingfeng-->
        <bm:field name="bank_name" expression="(select a.bank_short_name from csh_bank_account_v a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="bank_branch_name" expression="(select a.bank_branch_name from csh_bank_account_v a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_BRANCH_NAME"/>

        <bm:field name="review_status" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="review_status_desc" expression="(select v.code_value_name from sys_code_values_v v where v.code = 'DS_CSH_REVIEW_STATUS' and v.code_value =t1.review_status)" forInsert="false" forUpdate="false"/>/>
        <!--end-->

        <!--add by lisitong-->
        <bm:field name="approving_return_amount" datatype="java.lang.Double" expression="(select nvl(sum(ctr.this_return_amount),0) from csh_transaction_return ctr where ctr.transaction_id = t1.transaction_id and ctr.status = 'APPROVING')" forInsert="false" forUpdate="false" prompt="审批中退款金额"/>
        <!--end-->
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="transaction_id"/>
    </bm:primary-key>
    <bm:query-fields>
        <bm:query-field name="transaction_num_from" queryExpression="t1.transaction_num &gt;= ${@transaction_num_from}"/>
        <bm:query-field name="transaction_num_to" queryExpression="t1.transaction_num &lt;= ${@transaction_num_to}"/>
        <bm:query-field name="bank_slip_num_from" queryExpression="t1.bank_slip_num &gt;= ${@bank_slip_num_from}"/>
        <bm:query-field name="bank_slip_num_to" queryExpression="t1.bank_slip_num &lt;= ${@bank_slip_num_to}"/>
        <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="currency_code_from" queryExpression="t1.currency_code &gt;= ${@currency_code_from}"/>
        <bm:query-field name="currency_code_to" queryExpression="t1.currency_code &lt;= ${@currency_code_to}"/>
        <bm:query-field name="bank_account_code_from" queryExpression="(select a.bank_account_code from csh_bank_account a where a.bank_account_id=t1.bank_account_id) &gt;= ${@bank_account_code_from}"/>
        <bm:query-field name="bank_account_code_to" queryExpression="(select a.bank_account_code from csh_bank_account a where a.bank_account_id=t1.bank_account_id) &lt;= ${@bank_account_code_to}"/>
        <bm:query-field name="transaction_date_from" queryExpression="transaction_date &gt;= to_date(${@transaction_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="transaction_date_to" queryExpression="transaction_date &lt;= to_date(${@transaction_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="user_id" queryExpression="t1.created_by=${@user_id}"/>
        <bm:query-field name="receipt" queryExpression="(t1.transaction_type=${@receipt} or t1.transaction_type=${@advance_receipt} or t1.transaction_type=${@deposit} or t1.transaction_type=${@risk})"/>
        <!--<bm:query-field name="full_write_off_flag" queryExpression="t1.write_off_flag!=${@full_write_off_flag}"/>-->
        <bm:query-field name="full_returned_flag" queryExpression="t1.returned_flag!=${@full_returned_flag}"/>
        <bm:query-field name="not_reversed_flag_r" queryExpression="t1.reversed_flag!=${@not_reversed_flag_r}"/>
        <bm:query-field name="bp_id_from" queryExpression="t1.bp_id=${@bp_id_from}"/>
        <!-- <bm:query-field name="bp_id_to" queryExpression="t1.bp_id&lt;=${@bp_id_to}"/> -->
        <bm:query-field field="write_off_flag" queryOperator="="/>
        <bm:query-field field="posted_flag" queryOperator="="/>
        <bm:query-field field="returned_flag" queryOperator="="/>
        <bm:query-field field="reversed_flag" queryOperator="="/>
        <bm:query-field field="period_name" queryOperator="="/>
        <bm:query-field field="payment_method_id" queryOperator="="/>
        <bm:query-field field="transaction_category" queryOperator="="/>
        <bm:query-field field="transaction_type" queryOperator="="/>
        <bm:query-field field="transaction_id" queryOperator="="/>
        <bm:query-field field="csh_bp_name" queryOperator="like"/>
        <bm:query-field name="description" queryExpression="t1.description like &apos;%&apos;||${@description}||&apos;%&apos;"/>
        <!--         <bm:query-field name="bp_id_from" queryExpression="t1.bp_id &gt;= ${@bp_id_from}"/>
        <bm:query-field name="bp_id_to" queryExpression="t1.bp_id &lt;= ${@bp_id_to}"/> -->
        <bm:query-field name="reverse_write_off_amount_flag" queryExpression="t1.write_off_amount &gt; 0 and ${@reverse_write_off_amount_flag}=&apos;Y&apos;"/>
        <bm:query-field field="bp_id" queryOperator="="/>
        <bm:query-field name="status" queryExpression="nvl(t1.status,&apos;NEW&apos;) = ${@status}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_id in (select d.bp_id from hls_bp_master d where d.bp_name like &apos;%&apos;||${@bp_name_from}||&apos;%&apos;)"/>
        <bm:query-field name="contract_number" queryExpression="(SELECT cc.contract_number FROM con_contract cc WHERE cc.contract_id = t1.ref_contract_id) like  &apos;%&apos;||upper(${@contract_number})||&apos;%&apos;"/>
        <!--add by chenlingfeng-->
        <bm:query-field name="full_write_off_flag" queryExpression="(t1.write_off_flag = ${@full_write_off_flag} or t1.write_off_flag = ${@partial_write_off_flag} or t1.write_off_flag = ${@not_write_off_flag})"/>
        <bm:query-field name="full_review_status" queryExpression="(nvl(t1.review_status,'NOT') = ${@full_review_status} or nvl(t1.review_status,'NOT') = ${@partial_review_status} or nvl(t1.review_status,'NOT') = ${@not_review_status})"/>
        <bm:query-field name="unwrite_off_amount_from" queryExpression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0))&gt;=${@unwrite_off_amount_from}"/>
        <bm:query-field name="unwrite_off_amount_to" queryExpression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0))&lt;=${@unwrite_off_amount_from}"/>
        <bm:query-field name="bank_slip_num" queryExpression="t1.bank_slip_num like ${@bank_slip_num}"/>
        <bm:query-field name="transaction_num" queryExpression="t1.transaction_num like ${@transaction_num}"/>
        <bm:query-field name="bp_bank_account_name" queryExpression="t1.bp_bank_account_name like ${@bp_bank_account_name}"/>
        <bm:query-field name="bp_bank_account_num" queryExpression="t1.bp_bank_account_num like ${@bp_bank_account_num}"/>
        <bm:query-field name="bank_name" queryExpression="(select a.bank_short_name from csh_bank_account_v a where a.bank_account_id=t1.bank_account_id) like ${@bank_name}"/>

        <bm:query-field name="bank_branch_name" queryExpression="(select a.bank_branch_name from csh_bank_account_v a where a.bank_account_id=t1.bank_account_id) like ${@bank_branch_name}"/>
        <!--end-->
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression="(t1.company_id=${/session/@company_id})"/>

        <bm:data-filter name="query"
                        expression="((EXISTS  (SELECT 1 FROM hls_bp_master_bank_account a, sys_user u WHERE a.bp_id = u.bp_id  AND u.user_id = ${/session/@user_id} AND a.bank_account_num = t1.bp_bank_account_num) AND (SELECT u.bp_category FROM sys_user u WHERE u.user_id =${/session/@user_id}) = 'AGENT') OR (SELECT u.bp_category FROM sys_user u WHERE u.user_id = ${/session/@user_id}) != 'AGENT')"/>


        <bm:data-filter name="type" expression="t1.transaction_type in ('ADVANCE_RECEIPT','RECEIPT') and t1.returned_flag != 'FULL' and t1.reversed_flag = 'N' and nvl(t1.deposit_flag,'N')='N' "/>
    </bm:data-filters>
</bm:model>