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