<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-8-30 上午11:30: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
                        h.bp_bank_account_num AS bp_num,
                        h.bp_bank_account_name,
                        h.payment_req_number,
                        h.payment_req_id,
                        h.req_date,
                        h.business_type,
                        h.document_type,
                        h.document_category,
                        (SELECT
                            SUM(NVL(rl.amount, 0) - NVL(
                            (SELECT
                                SUM(amount)
                            FROM
                                csh_payment_req_ln_ddct ld
                            WHERE
                                ld.payment_req_ln_id = rl.payment_req_ln_id
                            ), 0) - NVL(
                            (SELECT
                                SUM(lp.write_off_amt)
                            FROM
                                csh_payment_req_ln_prepay lp
                            WHERE
                                lp.payment_req_ln_id = rl.payment_req_ln_id
                            ), 0))
                        FROM
                            csh_payment_req_ln rl
                        WHERE
                            rl.payment_req_id = h.payment_req_id
                        ) sum_act_amount,
                        (SELECT
                            hbmba.bank_full_name
                        FROM
                            hls_bp_master_bank_account hbmba
                        WHERE
                            hbmba.bank_account_id = h.bp_bank_account_id
                        ) bank_full_name,
                        (SELECT
                            e.name
                        FROM
                            sys_user u,
                            exp_employees e
                        WHERE
                            h.owner_user_id = u.user_id AND
                            u.employee_id   = e.employee_id
                        ) user_name,
                        h.bp_id,
                        (SELECT b.bp_name FROM hls_bp_master b WHERE h.bp_id = b.bp_id
                        ) bp_name,
                        (SELECT
                            b.bp_code
                        FROM
                            hls_bp_master b
                        WHERE
                            h.bp_id = b.bp_id
                        ) bp_code,
                        h.amount,
                        yonda_individual_pkg.string_combination(p_sql =>'select t1.contract_number from con_contract t1,csh_payment_req_ln t2 where t1.contract_id = t2.ref_doc_id and t2.payment_req_id=' ,p_compare_column_val => h.payment_req_id,p_division_symbol => ',') contract_number,
                        yonda_individual_pkg.string_combination(p_sql =>'select pp.project_number from con_contract t1,prj_project pp ,csh_payment_req_ln t2 where t1.project_id = pp.project_id  and  t1.contract_id = t2.ref_doc_id and t2.payment_req_id=' ,p_compare_column_val => h.payment_req_id,p_division_symbol => ',') project_number,
                        h.print_flag,
                        DECODE(
                        (SELECT
                            COUNT(1) FROM csh_payment_req_ln WHERE payment_req_id = h.payment_req_id AND
                            payment_status                                       <> 'FULL'
                        ),0,'Y','N')payment_flag,
                        h.currency_code,
                        t2.result_state,
                        TO_CHAR(t2.creation_date,'yyyy-mm-dd hh24:mi:ss') TIME,
                        DECODE(t2.result_state, '1', '成功', '2', '失败', '3','程序异常','未知错误') result_state_n,
                        t2.result_id
                    FROM
                        csh_payment_req_hd h,
                        gh_nc_inter_return t2
                    WHERE
                        h.payment_req_id  = t2.document_id AND
                        t2.document_table = 'CSH_PAYMENY'
                    ) h #WHERE_CLAUSE#
                ORDER BY
                    TIME DESC,
                    payment_req_number DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="sum_act_amount" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="bp_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="bank_full_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="bp_bank_account_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="payment_flag" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="payment_req_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PAYMENT_REQ_ID" prompt="CSH_PAYMENT_REQ_HD.PAYMENT_REQ_ID"/>
        <bm:field name="payment_req_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PAYMENT_REQ_NUMBER" required="true"/>
        <bm:field name="req_date" databaseType="DATE" datatype="java.util.Date" physicalName="REQ_DATE"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_name"/>
        <bm:field name="amount" databaseType="NUMBER" datatype="java.lang.Long" physicalName="amount"/>
        <bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="currency_code"/>
        <bm:field name="time"/>
        <bm:field name="result_state_n"/>
        <bm:field name="result_id"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="payment_req_number" queryOperator="like"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="bp_num" queryOperator="like"/>
        <bm:query-field field="bp_bank_account_name" queryOperator="like"/>
        <bm:query-field field="bank_full_name" queryOperator="like"/>
    </bm:query-fields>
</bm:model>