<?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>