<?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:parameters>
                <bm:parameter name="return_id" dataType="java.lang.Long" input="false" output="true" outputPath="/parameter/@return_id"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                begin

                    hlcm_cux_deposit_pkg.insert_transaction_return(p_transaction_id => ${@transaction_id},
                                                                   p_user_id        => ${/session/@user_id},
                                                                   p_return_id      => ${@return_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,
               v1.bp_id,
               nvl((SELECT tc.trans_review_status
                  FROM csh_transaction_contemp tc
                 where tc.contemp_id =
                       (select max(contemp_id)
                          FROM csh_transaction_contemp tc
                         where tc.source_contract_id = v1.contract_id
                           and tc.batch_id is not null)),'NEW') trans_review_status,
               nvl((SELECT sc.code_value_name
                  FROM csh_transaction_contemp tc, sys_code_values_v sc
                 where tc.contemp_id =
                       (select max(contemp_id)
                          FROM csh_transaction_contemp tc
                         where tc.source_contract_id = v1.contract_id
                           and tc.batch_id is not null)
                   and sc.code = 'TRANS_REVIEW_STATUS'
                   AND sc.code_value =nvl(tc.trans_review_status,'NEW')),'新建') trans_review_status_n
          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,
                       tt.bp_id
                  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, tt.bp_id) 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 like &apos;%&apos;||${@bp_name_n}||&apos;%&apos;"/>
        <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-field name="trans_review_status" queryExpression="v.trans_review_status =${@trans_review_status}"/>
    </bm:query-fields>
</bm:model>