acr_invoice_reverse.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: LR  
    $Date: 2013-7-18 上午09:32:02  
    $Revision: 1.0  
    $Purpose: 
-->



<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
               Select t.invoice_apply_id,
                      t.invoice_apply_num,
                      to_char(t.invoice_apply_date, 'yyyy-mm-dd') invoice_apply_date,
                      t.invoice_apply_user,
                      (Select su.description
                       From sys_user su
                       Where su.user_id = t.invoice_apply_user) invoice_apply_user_n,
                      t.wfl_instance_id,
                      t.wfl_apply_status,
                      (Select v.code_value_name
                       From sys_code_values_v v
                       Where v.code = 'WFL_STATUS'
                         And v.code_value = t.wfl_apply_status) wfl_apply_status_n,
                      nvl((Select nvl(Sum(ah.total_amount), 0)
                           From acr_invoice_hd_wfl ah
                           Where ah.invoice_apply_id = t.invoice_apply_id),
                          0) total_amount,
                      nvl((Select nvl(Sum(ah.tax_amount), 0)
                           From acr_invoice_hd_wfl ah
                           Where ah.invoice_apply_id = t.invoice_apply_id),
                          0) tax_amount,
                      nvl((Select nvl(Sum(nvl(ah.total_amount, 0) - nvl(ah.tax_amount, 0)),
                                      0)
                           From acr_invoice_hd_wfl ah
                           Where ah.invoice_apply_id = t.invoice_apply_id),
                          0) net_amount,
                      t.creation_date,
                      t.created_by,
                      t.last_update_date,
                      t.last_updated_by,
                      t.note,
                      t.ref_v01,
                      t.ref_v02,
                      t.ref_v03,
                      t.ref_v04,
                      t.ref_v05,
                      t.ref_n01,
                      t.ref_n02,
                      t.ref_n03,
                      t.ref_n04,
                      t.ref_n05,
                      t.ref_d01,
                      t.ref_d02,
                      t.ref_d03,
                      t.ref_d04,
                      t.ref_d05,
                      t.apply_mode,
                      t.reverse_method,
                      (Select v.code_value_name
                       From sys_code_values_v v
                       Where v.code = 'REVERSE_METHOD'
                         And v.code_value = t.reverse_method
                         and v.code_enabled_flag = 'Y'
                         and v.code_value_enabled_flag = 'Y') As reverse_method_n,
                      (Select  ahd.vat_interface_status
                       From  acr_invoice_hd ahd,acr_invoice_hd_wfl ahdw
                       Where  ahd.invoice_hd_id=ahdw.invoice_hd_id
                         and ahdw.invoice_apply_id=t.invoice_apply_id
                         and rownum=1) vat_interface_status,
                      (Select v.code_value_name
                       From sys_code_values_v v, acr_invoice_hd ahd,acr_invoice_hd_wfl ahdw
                       Where v.code = 'ACR515_VAT_INTERFACE_STATUS'
                         And v.code_value = ahd.vat_interface_status
                         and v.code_enabled_flag = 'Y'
                         and v.code_value_enabled_flag = 'Y'
                          and ahd.invoice_hd_id=ahdw.invoice_hd_id
                          and ahdw.invoice_apply_id=t.invoice_apply_id
                          and rownum=1) As vat_interface_status_n,
                      decode((select count(1)
                       from acr_invoice_hd ah, acr_invoice_hd_wfl ahw
                       where ah.invoice_hd_id = ahw.invoice_hd_id
                         and ahw.invoice_apply_id = t.invoice_apply_id
                         and nvl(ah.reversed_flag, 'N') = 'I'),0,'N','Y') reversing_flag
               From acr_invoice_apply t
                               #WHERE_CLAUSE#
               order by t.invoice_apply_id desc
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                BEGIN
                    acr_invoice_pkg.reverse_invoice(p_invoice_hd_id 		=> ${@invoice_hd_id},
								                    p_reverse_date 			=> to_date(${@reverse_date},'yyyy-mm-dd'), 
								                    p_vat_red_notice_num	=> ${@vat_red_notice_num},
								                    p_user_id 				=> ${/session/@user_id}, 
								                    p_role_id 				=> ${/session/@role_id});
                END;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="invoice_apply_num"
                        queryExpression="t.invoice_apply_num like &apos;%&apos;||${@invoice_apply_num}||&apos;%&apos;"/>
        <bm:query-field name="invoice_apply_date"
                        queryExpression="trunc(t.invoice_apply_date) = to_date(${@invoice_apply_date}, 'yyyy-mm-dd')"/>
        <bm:query-field name="invoice_apply_user_n" queryExpression="t.invoice_apply_user = ${@invoice_apply_user}"/>
        <bm:query-field name="wfl_apply_status_n" queryExpression="t.wfl_apply_status = ${@wfl_apply_status}"/>
        <bm:query-field name="vat_interface_status_n"
                        queryExpression="t.vat_interface_status = ${@vat_interface_status}"/>
        <bm:query-field name="invoice_apply_id" queryExpression="t.invoice_apply_id = ${@invoice_apply_id}"/>
        <bm:query-field name="apply_mode" queryExpression="nvl(t.apply_mode, &apos;APPLY&apos;) = ${@apply_mode}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t.wfl_apply_status = &apos;APPROVED&apos;"/>
        <bm:data-filter enforceOperations="query" expression="exists (select 1
                             from acr_invoice_hd ah, acr_invoice_hd_wfl ahw
                             where ah.invoice_hd_id = ahw.invoice_hd_id
                               and ahw.invoice_apply_id = t.invoice_apply_id
                               and nvl(ah.reversed_flag, 'N') = 'N')" />
    </bm:data-filters>
</bm:model>