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
<?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="billing_object_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
</bm:fields>
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select t.cashflow_id,
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,
(select m.bp_name
from hls_bp_master_s_v m
where m.bp_id = con.bp_id_tenant) billing_object_name,
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,
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)) as product_name
from acr_invoice_create_tmp t,
con_contract_v con,
con_contract_cashflow_all_v cf
where t.session_id = ${/session/@session_id}
and t.contract_id = con.contract_id
and t.cashflow_id = cf.cashflow_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>