1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: wangwei
$Date: 2015-11-16 下午4:30:02
$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 t1.* from (SELECT
sai.document_number,
cc.contract_number,
(select p.virtual_con_number from prj_project p where p.project_id=cc.project_id)
virtual_con_number,
bp.bp_name,
bp.tax_registry_num,
bp.invoice_bp_address_phone_num,
bp.invoice_phone_number,
bp.invoice_bank,
bp.invoice_bp_bank_account,
--sdic_invoice_pkg.get_cf_item_desc(sai.cashflow_id) cf_item_desc,
sai.cf_item_desc,
sai.specification,
sai.uom,
sai.quantity,
sai.price,
sai.net_due_amount,
sai.vat_rate,
sai.VAT_TYPE,
(select vv.code_value_name from sys_code_values_v vv where
vv.code='ACR_INVOICE_VAT_TYPE'
and vv.code_value=sai.VAT_TYPE
and vv.code_enabled_flag='Y'
and vv.code_value_enabled_flag='Y') VAT_TYPE_desc,
sai.vat_due_amount,
sai.due_amount,
sai.invoice_date,
sai.invoice_number,
sai.invoice_code,
sai.net_invoice_amount,
sai.vat_invoice_amount,
sai.vat_invoice_rate,
sai.invoice_amount,
sai.import_flag,
ccc.times,
ccc.due_date,
sai.invoice_id,
(select 'Y' from dual where round((select sum(nvl(li.invoice_amount,0))
from sdic_acr_invoice_line li
where li.invoice_id=sai.invoice_id),2)=sai.due_amount) done_flag,
(select sum(nvl(li.invoice_amount,0))
from sdic_acr_invoice_line li
where li.invoice_id=sai.invoice_id) done_invoice_amount,
sai.invoice_kind,
(select sv.code_value_name from sys_code_values_v sv where sv.code='ACR510_INVOICE_KIND'
and sv.code_value=sai.invoice_kind
and sv.code_enabled_flag='Y'
and sv.code_value_enabled_flag='Y') invoice_kind_des,
sdic_invoice_pkg.get_billing_times(p_invoice_id =>sai.invoice_id,
p_contract_id =>sai.contract_id,
p_cashflow_id =>sai.cashflow_id) billing_times,
sdic_invoice_pkg.get_not_billing_times(p_invoice_id =>sai.invoice_id,
p_contract_id =>sai.contract_id,
p_cashflow_id =>sai.cashflow_id) not_billing_times,
sdic_invoice_pkg.get_billing_amount(p_invoice_id =>sai.invoice_id,
p_contract_id =>sai.contract_id,
p_cashflow_id =>sai.cashflow_id) billing_amount,
sdic_invoice_pkg.get_not_billing_amount(p_invoice_id =>sai.invoice_id,
p_contract_id =>sai.contract_id,
p_cashflow_id =>sai.cashflow_id) not_billing_amount
FROM
sdic_acr_invoice sai,
con_contract cc,
hls_bp_master bp,
con_contract_cashflow ccc
WHERE
sai.contract_id=cc.contract_id AND
sai.bp_id =bp.bp_id AND
ccc.cashflow_id=sai.cashflow_id
and nvl(sai.closed_flag,'N')<>'Y') t1
#WHERE_CLAUSE#
order by t1.due_date,t1.contract_number desc
]]></bm:query-sql>
</bm:operation>
<bm:operation name="update">
<bm:update-sql><![CDATA[
begin
sdic_invoice_pkg.insert_pre_query(p_company_id =>${/session/@company_id},
p_user_id =>${/session/@user_id});
end;
]]></bm:update-sql>
</bm:operation>
<bm:operation name="execute">
<bm:update-sql><![CDATA[
declare
v_count_bp_flag number;
v_count_vat_flag number;
e_bp_check_error exception;
e_vat_check_error exception;
begin
select count(distinct ai.bp_id) into v_count_bp_flag
from sdic_acr_invoice ai where ai.invoice_id in (${:@invoice_id_str}) ;
if v_count_bp_flag>1 then
raise e_bp_check_error;
end if;
select count(distinct ai.invoice_kind) into v_count_vat_flag
from sdic_acr_invoice ai where ai.invoice_id in(${:@invoice_id_str}) ;
if v_count_vat_flag>1 then
raise e_vat_check_error;
end if;
exception
when e_bp_check_error then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只能打印同一购货单位项下的发票！',
p_created_by => ${/session/@user_id},
p_package_name => 'sdic_acr_invoice_pkg',
p_procedure_function_name => 'check_bp');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
when e_vat_check_error then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只能打印相同的发票类型的单据！',
p_created_by => ${/session/@user_id},
p_package_name => 'sdic_acr_invoice_pkg',
p_procedure_function_name => 'check_bp');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
]]></bm:update-sql>
</bm:operation>
</bm:operations>
<bm:query-fields>
<bm:query-field name="import_flag" queryExpression="nvl(t1.import_flag,'N')=${@import_flag}"/>
<bm:query-field name="contract_number" queryExpression="T1.contract_number LIKE ${@contract_number}"/>
<bm:query-field name="bp_name" queryExpression="T1.bp_name LIKE ${@bp_name}"/>
<bm:query-field name="document_number" queryExpression="T1.document_number LIKE ${@document_number}"/>
<bm:query-field name="invoice_number" queryExpression="T1.invoice_number LIKE ${@invoice_number}"/>
<bm:query-field name="invoice_bp_bank_account" queryExpression="T1.invoice_bp_bank_account LIKE ${@invoice_bp_bank_account}"/>
<bm:query-field name="invoice_date_from" queryExpression="t1.invoice_date>=trunc(to_date(${@invoice_date_from},'yyyy-mm-dd'))"/>
<bm:query-field name="invoice_date_to" queryExpression="trunc(t1.invoice_date)<=to_date(${@invoice_date_to},'yyyy-mm-dd')"/>
<bm:query-field name="vat_type" queryExpression="T1.vat_type = ${@vat_type}"/>
<bm:query-field name="invoice_id" queryExpression="T1.invoice_id = ${@invoice_id}"/>
<bm:query-field name="virtual_con_number" queryExpression="T1.virtual_con_number like ${@virtual_con_number}"/>
<bm:query-field name="done_flag" queryExpression="nvl(t1.done_flag,'N')=${@done_flag}"/>
<bm:query-field name="due_date_from" queryExpression="t1.due_date>=trunc(to_date(${@due_date_from},'yyyy-mm-dd'))"/>
<bm:query-field name="due_date_to" queryExpression="trunc(t1.due_date)<=to_date(${@due_date_to},'yyyy-mm-dd')"/>
</bm:query-fields>
</bm:model>