<?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 '%'||${@invoice_apply_num}||'%'"/>
<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, 'APPLY') = ${@apply_mode}"/>
</bm:query-fields>
<bm:data-filters>
<bm:data-filter enforceOperations="query" expression="t.wfl_apply_status = 'APPROVED'"/>
<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>