<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Administrator  
    $Date: 2017-8-21 上午10:36:49  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
            SELECT t1.*,
               (SELECT b.bank_code
                  FROM csh_bank_account a, csh_bank b, csh_bank_branch bb
                 WHERE b.bank_id = bb.bank_id
                   AND bb.bank_branch_id = a.bank_branch_id
                   AND a.bank_account_id = t1.bank_account_id) bank_code,
               (SELECT b.bank_short_name
                  FROM csh_bank_account a, csh_bank b, csh_bank_branch bb
                 WHERE b.bank_id = bb.bank_id
                   AND bb.bank_branch_id = a.bank_branch_id
                   AND a.bank_account_id = t1.bank_account_id) bank_name,
               (SELECT bb.bank_branch_name
                  FROM csh_bank_account a, csh_bank b, csh_bank_branch bb
                 WHERE b.bank_id = bb.bank_id
                   AND bb.bank_branch_id = a.bank_branch_id
                   AND a.bank_account_id = t1.bank_account_id) bank_branch_name,

               (SELECT a.bank_account_num
                  FROM csh_bank_account a
                 WHERE a.bank_account_id = t1.bank_account_id) bank_account_num,
               (SELECT a.bank_account_name
                  FROM csh_bank_account a
                 WHERE a.bank_account_id = t1.bank_account_id) bank_account_name,
               (SELECT a.currency_code
                  FROM csh_bank_account a
                 WHERE a.bank_account_id = t1.bank_account_id) currency,

               (SELECT v.code_value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'CCB600_EBANK_STATUS'
                   AND v.code_value = t1.status) status_desc,

               (SELECT SUM(l.real_amount)
                  FROM hls_ebank_withhold_detail l
                 WHERE l.batch_id = t1.batch_id
                   AND l.status = 'SUCCESS') total_real_amount,

               (SELECT COUNT(1)
                  FROM hls_ebank_withhold_detail l
                 WHERE l.batch_id = t1.batch_id
                   AND l.status = 'SUCCESS') total_success_count,
               (select u.description
                          from sys_user u
                         where u.user_id = t1.created_by) created_by_n
                FROM hls_ebank_withhold_batch t1)t
                 #WHERE_CLAUSE#
                 order by t.batch_date desc
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
               begin
                    hls_ebank_batch_pkg.create_ebank_withhold_batch(
                               p_bank_account_id     =>${@bank_account_id},
                               p_user_id             =>${/session/@user_id},
                               p_batch_id            =>${@batch_id});
               end;
            ]]></bm:update-sql>
            <bm:parameters >
                <bm:parameter name ="batch_id" dataType="java.lang.Long" input ="false" output="true" outputPath ="/parameter/@batch_id" />
            </bm:parameters >
        </bm:operation>
        <bm:operation name="delete">
            <bm:update-sql>
                begin
                    delete from hls_ebank_withhold_batch b where b.batch_id = ${@batch_id};
                    delete from hls_ebank_withhold_detail hd where hd.batch_id = ${@batch_id};
                end;
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="batch_id"/>
        <bm:field name="batch_date"/>
        <bm:field name="bank_name"/>
        <bm:field name="bank_code"/>
        <bm:field name="bank_branch_name"/>
        <bm:field name="bank_account_id"/>
        <bm:field name="bank_account_num"/>
        <bm:field name="bank_account_name"/>
        <bm:field name="currency"/>
        <bm:field name="status"/>
        <bm:field name="status_desc"/>
        <bm:field name="message"/>
        <bm:field name="total_real_amount"/>
        <bm:field name="total_success_count"/>
        <bm:field name="created_by_n"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="batch_id" queryExpression="t.batch_id=${@batch_id}"/>
        <bm:query-field name="bank_code" queryExpression="t.bank_code=${@bank_code}"/>
        <bm:query-field name="status" queryExpression="t.status=${@status}"/>
        <bm:query-field name="batch_date" queryExpression="to_date(to_char(t.batch_date,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date(${@batch_date},'yyyy-mm-dd')"/>
    </bm:query-fields>
</bm:model>
