csh_payment_reverse.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-5-30 下午02:30:47  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:s="leaf.plugin.script">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
               SELECT *
  FROM (SELECT t.transaction_id,
               t.transaction_num,
               TO_CHAR(t.transaction_date, 'yyyy-mm-dd') transaction_date,
               t.transaction_amount,
               t.currency_code,
               (SELECT g.currency_name
                  FROM gld_currency_v g
                 WHERE g.currency_code = t.currency_code) currency_name,
               t.bp_bank_account_id,
               t.bp_bank_account_num,
               (SELECT b.bank_branch_name
                  FROM csh_bank_account_v b
                 WHERE b.bank_account_id = t.bank_account_id) bank_account_name,
               (SELECT b.bank_account_num
                  FROM csh_bank_account_v b
                 WHERE b.bank_account_id = t.bank_account_id) bank_account_num,
               t.bp_id,
               (SELECT hbbc.bank_account_name
                   FROM hls_bp_master_bank_account hbbc
                  WHERE  hbbc.bank_account_id=t.bp_bank_account_id) bp_name,
               t.bp_category,
               (select description
                  from hls_bp_category h
                  where h.bp_category=t.bp_category ) bp_category_n,
               h.payment_req_number
          FROM csh_transaction t, csh_payment_req_hd h
         WHERE t.transaction_type = 'PAYMENT'
           AND t.source_doc_id = h.payment_req_id
           AND t.posted_flag = 'Y'
           AND t.reversed_flag = 'N'
           AND t.write_off_flag = 'FULL'
           AND NOT EXISTS (SELECT 1
                  FROM con_contract cc
                 WHERE cc.contract_id = h.contract_id
                   AND cc.data_class = 'NORMAL'
                   AND cc.contract_status = 'CLOSED')
           AND NOT EXISTS (SELECT 1
                 FROM con_contract cc
                 WHERE cc.contract_id = h.contract_id
                  AND cc.division = '95')
           AND t.returned_flag = 'NOT'
           AND EXISTS
         (SELECT 1
                  FROM con_contract_cashflow cc
                 WHERE cc.cashflow_id =
                       (SELECT l.ref_doc_line_id
                          FROM csh_payment_req_ln l
                         WHERE l.payment_req_id = h.payment_req_id
                           AND rownum = 1)
                --AND
                --cc.cf_item = '52'
                )
         and not exists(select 1
                   from csh_payment_req_ln l,con_contract cc,con_contract_cashflow cf
                   where l.payment_req_id = h.payment_req_id
                   and l.ref_doc_category = 'CONTRACT'
                   and l.ref_doc_id = cc.contract_id
                   and l.ref_doc_line_id = cf.cashflow_id
                   and cf.cf_item in ('40','911'))) v  #WHERE_CLAUSE#
          ORDER BY transaction_date DESC, transaction_num

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:features>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    </bm:features>
    <bm:query-fields>
        <bm:query-field name="payment_req_number_from" datatype="java.lang.String" queryExpression="v.payment_req_number&gt;=${@payment_req_number_from}"/>
        <bm:query-field name="payment_req_number_to" datatype="java.lang.String" queryExpression="v.payment_req_number&lt;=${@payment_req_number_to}"/>
        <bm:query-field name="transaction_num_from" datatype="java.lang.String" queryExpression="v.transaction_num&gt;=${@transaction_num_from}"/>
        <bm:query-field name="transaction_num_to" datatype="java.lang.String" queryExpression="v.transaction_num&lt;=${@transaction_num_to}"/>
        <bm:query-field name="transaction_date_from" datatype="java.lang.String" queryexpression="v.transaction_date &gt;= ${@transaction_date_from}"/>
        <bm:query-field name="transaction_date_to" datatype="java.lang.String" queryexpression="v.transaction_date &lt;= ${@transaction_date_to}"/>
        <bm:query-field name="transaction_amount_from" datatype="java.lang.String" queryExpression="v.transaction_amount&gt;=${@transaction_amount_from}"/>
        <bm:query-field name="transaction_amount_to" datatype="java.lang.String" queryExpression="v.transaction_amount&lt;=${@transaction_amount_to}"/>
        <bm:query-field name="bank_account_code_from" datatype="java.lang.String" queryexpression="v.bank_account_code &gt;= ${@bank_account_code_from}"/>
        <bm:query-field name="bank_account_num"  queryexpression="v.bank_account_num like &apos;%&apos; || ${@bank_account_num} || &apos;%&apos;"/>
        <bm:query-field name="bank_account_code_to" datatype="java.lang.String" queryexpression="v.bank_account_code &lt;= ${@bank_account_code_to}"/>
        <bm:query-field name="currency_code" datatype="java.lang.String" queryexpression="v.currency_code = ${@currency_code}"/>
        <bm:query-field name="bp_id" datatype="java.lang.String" queryexpression="v.bp_id = ${@bp_id}"/>
        <bm:query-field name="bp_name" datatype="java.lang.String" queryexpression="v.bp_name = ${@bp_name}"/>
    </bm:query-fields>
</bm:model>