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
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
Select v.monthly,
Sum(NVL(purchase_amount, 0)) total_purchase_amount,
Sum(NVL(down_payment, 0)) total_down_payment,
Sum(NVL(lease_amount, 0)) total_lease_amount,
(Sum(NVL(purchase_amount, 0)) + Sum(NVL(down_payment, 0)) +
Sum(NVL(lease_amount, 0))) total_amount,
Sum(NVL(outflow_amount, 0)) total_outflow_amount
From (Select to_char(t.calendar_date, 'yyyy/mm') monthly,
t.calendar_date,
decode(to_char(t.calendar_date, 'd'),
1,
'日',
2,
'月',
3,
'火',
4,
'水',
5,
'木',
6,
'金',
7,
'土') named_day,
(Select Sum(ccc.due_amount - nvl(ccc.received_amount, 0))
From con_contract_cashflow ccc
Where ccc.cf_item = 8
And ccc.due_date = t.calendar_date
And ccc.cf_status = 'RELEASE'
And Exists
(Select 1
From con_contract ct
Where ct.contract_status In
('INCEPT', 'PENDING', 'ETING', 'REPURING')
And ct.contract_id = ccc.contract_id
And ct.data_class = 'NORMAL')) purchase_amount, --留购金
(Select Sum(due_amount - nvl(received_amount, 0))
From con_contract_cashflow ccc
Where ccc.cf_item = 2
And ccc.due_date = t.calendar_date
And ccc.cf_status = 'RELEASE'
And Exists
(Select 1
From con_contract ct
Where ct.contract_status In
('INCEPT', 'PENDING', 'ETING', 'REPURING')
And ct.contract_id = ccc.contract_id
And ct.data_class = 'NORMAL')) down_payment, --首付款
(Select Sum(due_amount)
From con_contract_cashflow ccc
Where ccc.cf_item In (1, 200, 250, 11)
And ccc.due_date = t.calendar_date
And ccc.cf_status = 'RELEASE'
And Exists
(Select 1
From con_contract ct
Where ct.contract_status In
('INCEPT', 'PENDING', 'ETING', 'REPURING')
And ct.contract_id = ccc.contract_id
And ct.data_class = 'NORMAL')) lease_amount, --租金
(Case
When to_char(Sysdate, 'dd') <= 20 And
to_char(t.calendar_date, 'yyyymmdd') =
to_char(Sysdate, 'yyyymm') || 20 Then
--当月的二十号统计数据
NVL((Select Sum(nvl(c.lease_item_amount, 0))
From con_contract c
Where c.contract_status <> 'CANCEL'
And c.data_class = 'NORMAL'
And to_char(add_months(c.lease_start_date, 1),
'yyyymm') = to_char(Sysdate, 'yyyymm')
And nvl(c.payment_deduction, 'NO_DEDUCTION') =
'NO_DEDUCTION'
And nvl(c.secondary_lease, 'NO') = 'NO'),
0) +
NVL((Select Sum(nvl(c.lease_item_amount, 0) -
nvl(c.down_payment, 0))
From con_contract c
Where c.contract_status <> 'CANCEL'
And c.data_class = 'NORMAL'
And to_char(add_months(c.lease_start_date, 1),
'yyyymm') = to_char(Sysdate, 'yyyymm')
And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
'NO_DEDUCTION'
And nvl(c.secondary_lease, 'NO') = 'NO'),
0)
When to_char(Sysdate, 'dd') > 20 And
to_char(add_months(t.calendar_date, -1), 'yyyymmdd') =
to_char(Sysdate, 'yyyymm') || 20 Then
NVL((Select Sum(nvl(c.lease_item_amount, 0))
From con_contract c
Where c.contract_status <> 'CANCEL'
And c.data_class = 'NORMAL'
And to_char(c.lease_start_date, 'yyyymm') =
to_char(Sysdate, 'yyyymm')
And nvl(c.payment_deduction, 'NO_DEDUCTION') =
'NO_DEDUCTION'
And nvl(c.secondary_lease, 'NO') = 'NO'),
0) + NVL((Select Sum(nvl(c.lease_item_amount, 0) -
nvl(c.down_payment, 0))
From con_contract c
Where c.contract_status <> 'CANCEL'
And c.data_class = 'NORMAL'
And to_char(c.lease_start_date, 'yyyymm') =
to_char(Sysdate, 'yyyymm')
And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
'NO_DEDUCTION'
And nvl(c.secondary_lease, 'NO') = 'NO'),
0)
Else
0
End) outflow_amount
From (Select s.calendar_date
From fnd_calendar_details s
Where trunc(s.calendar_date) >= trunc(Sysdate)
And trunc(s.calendar_date) <= Any
(Select trunc(due_date)
From con_contract_cashflow ccc
Where ccc.cf_status = 'RELEASE'
And Exists
(Select 1
From con_contract t
Where t.contract_status In
('INCEPT',
'PENDING',
'ETING',
'REPURING')
And t.contract_id = ccc.contract_id
And t.data_class = 'NORMAL'))
Order By s.calendar_date) t) v
Group By v.monthly
Order By monthly
]]></bm:query-sql>
</bm:operation>
</bm:operations>
</bm:model>