<?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="execute">
            <bm:update-sql><![CDATA[
            	begin
           hlcm_cux_deposit_pkg.check_csh_transaction_status(
			              p_contract_id=>${@contract_id},
			              p_user_id=>${/session/@user_id}
            		);
            	end;
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            	begin
           hlcm_cux_deposit_pkg.check_transaction_status_new(
			              p_transaction_id=>${@transaction_id},
			              p_user_id=>${/session/@user_id}
            		);
            	end;
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from ( select v1.contract_id,
            (select cc.contract_number from con_contract cc where cc.contract_id=v1.contract_id)contract_number,
            (select cc.contract_name from con_contract cc where cc.contract_id=v1.contract_id)contract_name,
       --to_char(v1.unwrite_off_amount,'FM999,999,999,990.00')unwrite_off_amount,
       v1.unwrite_off_amount,
       v1.deposit_trans_type,
       (select t.bp_name
          from hls_bp_master t, con_contract ct
         where t.bp_id = ct.bp_id_tenant
           and ct.contract_id = v1.contract_id) bp_id_telnet_n,
       (select ct.bp_id_agent_level1 from
         con_contract ct
         where  contract_id = v1.contract_id) bp_id_agent_level1,
           (select t.bp_name
          from hls_bp_master t, con_contract ct
         where t.bp_id = ct.bp_id_agent_level1
           and ct.contract_id = v1.contract_id) bp_name,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = 'DEPOSIT_TRANS_TYPE'
           and v.code_value = v1.deposit_trans_type) deposit_trans_type_n,
          nvl(( hlcm_cux_deposit_pkg.get_transaction_status_new(p_contract_id=>v1.contract_id,p_user_id=>1) ),'正常')approve_status

  from (select tt.ref_contract_id contract_id,
               tt.deposit_trans_type,
               sum((nvl(tt.transaction_amount, 0) -
                   nvl(tt.write_off_amount, 0) - nvl(tt.returned_amount, 0))) unwrite_off_amount
          from csh_transaction tt
         where tt.transaction_type = 'DEPOSIT'
           and tt.ref_contract_id is not null
           and tt.transaction_amount > 0
           and tt.deposit_trans_type is not null
         group by tt.ref_contract_id, tt.deposit_trans_type) v1
          where v1.unwrite_off_amount>0)v
                #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <!--<bm:data-filters>-->
        <!--<bm:data-filter name="query" expression="(to_number(v.unwrite_off_amount)>0)"/>-->
    <!--</bm:data-filters>-->
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name_n" queryExpression="v.bp_name = ${@bp_name_n}"/>
        <bm:query-field name="bp_id_telnet_n" queryExpression="v.bp_id_telnet_n =${@bp_id_telnet_n}"/>
        <bm:query-field name="deposit_trans_type" queryExpression="v.deposit_trans_type =${@deposit_trans_type}"/>

    </bm:query-fields>
</bm:model>