<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: DJ
$Date: 2013-7-4 下午5:06:06
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
<bm:fields>
<bm:field name="record_id"/>
<bm:field name="cashflow_id"/>
<bm:field name="product_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PRODUCT_NAME"
prompt="ACR.PRODUCT_NAME"/>
<bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID"/>
<bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String"
physicalName="PROJECT_NUMBER" prompt="HLS.PROJECT_NUMBER"/>
<bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME"
prompt="HLS.PROJECT_NAME"/>
<bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID"/>
<bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"
physicalName="CONTRACT_NUMBER" prompt="HLS.CONTRACT_NUMBER"/>
<bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME"
prompt="HLS.CONTRACT_NAME"/>
<bm:field name="invoice_kind" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_KIND"/>
<bm:field name="invoice_kind_desc" databaseType="VARCHAR2" datatype="java.lang.String"
physicalName="INVOICE_KIND_DESC" prompt="ACR.INVOICE_TYPE"/>
<bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DUE_AMOUNT"
prompt="ACR.DUE_AMOUNT"/>
<bm:field name="cf_billing_amount" databaseType="NUMBER" datatype="java.lang.Double"
physicalName="CF_BILLING_AMOUNT" prompt="ACR.BILLING_AMOUNT"/>
<bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double"
physicalName="RECEIVED_AMOUNT" prompt="ACR.RECEIVED_AMOUNT"/>
<bm:field name="billing_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="BILLING_AMOUNT"
prompt="ACR.THIS_BILLING_AMOUNT"/>
<bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TIMES"
prompt="HLS.PERIOD_NUMBER"/>
<bm:field name="last_received_date" databaseType="DATE" datatype="java.util.Date"
physicalName="LAST_RECEIVED_DATE" prompt="ACR.RECEIVED_DATE"/>
<bm:field name="cf_item" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CF_ITEM"
prompt="HLS.RECEIVE_PROJECT"/>
<bm:field name="cf_item_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CF_ITEM_DESC"
prompt="HLS.RECEIVE_PROJECT"/>
<bm:field name="currency" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY"
prompt="HLS.CURRENCY"/>
<bm:field name="currency_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_DESC"
prompt="HLS.CURRENCY"/>
<bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Double" physicalName="EXCHANGE_RATE"
prompt="HLS.EXCHANGE_RATE"/>
<bm:field name="exchange_rate_type" databaseType="VARCHAR2" datatype="java.lang.String"
physicalName="EXCHANGE_RATE_TYPE" prompt="HLS.EXCHANGE_RATE_TYPE"/>
<bm:field name="exchange_rate_type_desc" databaseType="VARCHAR2" datatype="java.lang.String"
physicalName="EXCHANGE_RATE_TYPE_DESC" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
<bm:field name="invoice_bp_tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="billing_method" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="billing_way_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="billing_frequency_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="billing_object_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="invoice_title" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="object_taxpayer_type" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="invoice_bp_address_phone_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
<bm:field name="invoice_bp_bank_account" databaseType="VARCHAR2" datatype="java.lang.String"/>
</bm:fields>
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select t.cashflow_id,
t.bp_name,--承租人姓名
con.billing_method,--开票规则
con.billing_way_n,--开票方式
con.billing_frequency_n,--开票频率
aic.billing_object_name,--开票对象
t.invoice_title,--发票抬头
aic.object_taxpayer_type,--纳税人类型
t.invoice_bp_address_phone_num,--开票地址电话
t.invoice_bp_bank_account,--开户行及账号
t.record_id,
t.billing_object,
(select c.code_value_name
from sys_code_values_v c
where c.code = 'ACR510_BILL_DETAIL_METHOD'
and c.code_value = t.billing_object) as billing_object_desc,
t.due_amount,
t.billing_amount,
t.cf_billing_amount,
t.received_amount,
t.contract_id,
con.contract_number,
con.contract_name,
con.project_id,
con.project_number,
con.project_name,
t.invoice_kind,
(select v.code_value_name
from sys_code_values_v v
where v.code = 'ACR510_INVOICE_KIND'
and v.code_value = t.invoice_kind) as invoice_kind_desc,
t.currency,
(select c.currency_name
from gld_currency_vl c
where c.currency_code = t.currency) as currency_desc,
1 as exchange_rate,
'MANUAL' as exchange_rate_type,
(select v.rate_type_desc
from gld_exchange_rate_type_v v
where v.rate_type_code = 'MANUAL') as exchange_rate_type_desc,
cf.times,
cf.last_received_date,
t.cf_item,
(select i.description
from hls_cashflow_item i
where i.cf_item = t.cf_item) as cf_item_desc,
CASE
WHEN con.division = '10' THEN
(SELECT ci.oem_type
FROM con_contract_lease_item ci
WHERE ci.contract_id = con.contract_id
AND ci.equipment_type = 'MAIN')|| nvl((SELECT ci.billing_desc
FROM con_contract_cf_item ci
WHERE ci.contract_id = con.contract_id
AND ci.cf_item = t.cf_item),
(SELECT iv.billing_desc
FROM hls_cashflow_item_company_v iv
WHERE iv.cf_item = t.cf_item
AND iv.company_id = con.company_id))
ELSE
CASE
WHEN t.cf_item IN (2,
3,
8,
9,
304,
100,
101) THEN
(SELECT decode(con.division,
'00',
'挖掘机',
'01',
'装载机') || nvl((SELECT ci.billing_desc
FROM con_contract_cf_item ci
WHERE ci.contract_id = con.contract_id
AND ci.cf_item = t.cf_item),
(SELECT iv.billing_desc
FROM hls_cashflow_item_company_v iv
WHERE iv.cf_item = t.cf_item
AND iv.company_id = con.company_id))
FROM dual)
ELSE
nvl((SELECT ci.billing_desc
FROM con_contract_cf_item ci
WHERE ci.contract_id = con.contract_id
AND ci.cf_item = t.cf_item),
(SELECT iv.billing_desc
FROM hls_cashflow_item_company_v iv
WHERE iv.cf_item = t.cf_item
AND iv.company_id = con.company_id))
END
END AS product_name,
t.invoice_bp_tax_registry_num
from acr_invoice_create_tmp t,
con_contract_v con,
con_contract_cashflow_all_v cf,
acr_invoice_contract_v aic
where t.session_id = ${/session/@session_id}
and t.contract_id = con.contract_id
and t.cashflow_id = cf.cashflow_id
and con.contract_id = aic.contract_id
order by con.contract_id,cf.times,t.cf_item
]]>
</bm:query-sql>
</bm:operation>
<bm:operation name="update">
<bm:update-sql><![CDATA[
begin
ACR_INVOICE_PKG.invoiceTmp_update(p_record_id => ${@record_id},
p_invoice_kind => ${@invoice_kind},
p_billing_amount => ${@billing_amount},
p_product_name => ${@product_name},
p_user_id => ${/session/@user_id});
end;
]]>
</bm:update-sql>
</bm:operation>
<bm:operation name="execute">
<bm:update-sql><![CDATA[
begin
ACR_INVOICE_PKG.create_invoice(p_session_id => ${/session/@session_id},
p_company_id => ${@company_id},
p_group_billing_method => ${@group_billing_method},
p_invoice_date => to_date(${@invoice_date},'yyyy-mm-dd'),
p_accounting_date => to_date(${@accounting_date},'yyyy-mm-dd'),
p_user_id => ${/session/@user_id},
p_role_id => ${/session/@role_id});
end;
]]>
</bm:update-sql>
</bm:operation>
</bm:operations>
</bm:model>