<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Harry  
    $Date: 2017-2-21 上午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
                    COUNT(*) sum_count,
                    SUM(sum_act_amount) sum_amount
                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,
                        h.approval_date,
                        h.apply_pay_date,
                        h.approval_status,
                        h.closed_date,
                        h.closed_flag,
                        h.created_by,
                        (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_category FROM hls_bp_master b WHERE h.bp_id = b.bp_id
                        ) bp_category,
                        (SELECT c.description FROM hls_bp_master b,hls_bp_category c WHERE h.bp_id = b.bp_id and b.bp_category = c.bp_category
                        ) bp_category_n,
                        (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,
                        (SELECT
                            hm.bp_name
                        FROM
                            hls_bp_master hm
                        WHERE
                            hm.bp_id =
                            (SELECT
                                cc.bp_id_tenant
                            FROM
                                con_contract cc
                            WHERE
                                cc.contract_number IN (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 => ',')) AND
                                rownum              = 1
                            )
                        ) bp_id_tenant_n,
                        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,
                        (SELECT
                            pp.fin_manager
                        FROM
                            prj_project pp
                        WHERE
                            pp.wfl_instance_id=h.wfl_instance_id
                        ) fin_manager,
                        (SELECT
                            ee.name
                        FROM
                            exp_employees ee
                        WHERE
                            ee.employee_id =
                            (SELECT
                                pp.fin_manager
                            FROM
                                prj_project pp
                            WHERE
                                pp.wfl_instance_id=h.wfl_instance_id
                            )
                        ) fin_manager_n
                    FROM
                        csh_payment_req_hd h
                    ) h #WHERE_CLAUSE#
                ORDER BY
                    req_date DESC,
                    payment_req_number DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="agent_flag" queryExpression="h.bp_id = decode((select su.bp_category from sys_user su where su.user_id =${/session/@user_id}),&apos;AGENT&apos;,(select su.bp_id from sys_user su where su.user_id =${/session/@user_id}),&apos;EMPLOYEE&apos;,h.bp_id)"/>
        <bm:query-field name="print_flag" queryExpression="nvl(h.print_flag,&apos;N&apos;) = ${@print_flag} and h.approval_status = &apos;APPROVED&apos; and nvl(h.closed_flag,&apos;N&apos;) = &apos;N&apos;"/>
        <bm:query-field name="payment_flag" queryExpression="h.payment_flag = ${@payment_flag}"/>
        <bm:query-field name="payment_req_number_from" datatype="java.lang.String" queryExpression="h.payment_req_number&gt;=${@payment_req_number_from}"/>
        <bm:query-field name="payment_req_number_to" datatype="java.lang.String" queryExpression="h.payment_req_number&lt;=${@payment_req_number_to}"/>
        <bm:query-field name="payment_req_number" queryExpression="upper(h.payment_req_number) like &apos;%&apos; || upper(${@payment_req_number}) || &apos;%&apos;"/>
        <bm:query-field name="contract_number" queryExpression="upper(h.contract_number) like &apos;%&apos; || upper(${@contract_number}) || &apos;%&apos;"/>
        <bm:query-field name="req_date_from" datatype="java.lang.String" queryexpression="h.req_date &gt;= to_date(${@req_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="req_date_to" datatype="java.lang.String" queryexpression="h.req_date &lt;= to_date(${@req_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="bp_code" datatype="java.lang.String" queryExpression="h.bp_code=${@bp_code}"/>
        <bm:query-field name="bp_code_from" datatype="java.lang.String" queryExpression="h.bp_code&gt;=${@bp_code_from}"/>
        <bm:query-field name="bp_code_to" datatype="java.lang.String" queryExpression="h.bp_code&lt;=${@bp_code_to}"/>
        <bm:query-field name="apply_pay_date_from" datatype="java.lang.String" queryexpression="trunc(h.apply_pay_date) &gt;= to_date(${@apply_pay_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="apply_pay_date_to" datatype="java.lang.String" queryexpression="trunc(h.apply_pay_date) &lt;= to_date(${@apply_pay_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="amount_from" datatype="java.lang.String" queryExpression="h.amount&gt;=${@amount_from}"/>
        <bm:query-field name="amount_to" datatype="java.lang.String" queryExpression="h.amount&lt;=${@amount_to}"/>
        <bm:query-field name="approval_date_from" datatype="java.lang.String" queryexpression="trunc(h.approval_date) &gt;= to_date(${@approval_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="approval_date_to" datatype="java.lang.String" queryexpression="trunc(h.approval_date) &lt;= to_date(${@approval_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="approval_status" datatype="java.lang.String" queryExpression="h.approval_status=${@approval_status}"/>
        <bm:query-field name="submitted_flag" datatype="java.lang.String" queryExpression="h.submitted_flag=${@submitted_flag}"/>
        <bm:query-field name="closed_flag" datatype="java.lang.String" queryExpression="h.closed_flag=${@closed_flag}"/>
        <bm:query-field name="currency_code" datatype="java.lang.String" queryExpression="h.currency_code=${@currency_code}"/>
        <bm:query-field name="closed_date_from" datatype="java.lang.String" queryexpression="trunc(h.closed_date) &gt;= to_date(${@closed_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="closed_date_to" datatype="java.lang.String" queryexpression="trunc(h.closed_date) &lt;= to_date(${@closed_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="bp_id_tenant_n" datatype="java.lang.String" queryexpression="h.bp_id_tenant_n like ${@bp_id_tenant_n}"/>
        <bm:query-field name="fin_manager_n" datatype="java.lang.String" queryexpression="h.fin_manager_n like ${@fin_manager_n}"/>
        <bm:query-field name="bp_category" dataType="java.lang.String" queryExpression="h.bp_category = ${@bp_category}"/>
    </bm:query-fields>
</bm:model>
