<?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 to_char(v.calendar_date, 'yyyy/mm/dd') calendar_date, v.named_day, v.purchase_amount, v.down_payment, v.lease_amount, nvl(v.purchase_amount, 0) + nvl(down_payment, 0) + nvl(lease_amount, 0) total_amount, outflow_amount From (Select 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(ccc.due_amount - nvl(ccc.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(ccc.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') || decode(to_char(to_date(to_char(Sysdate, 'yyyymm') || 20, 'yyyymmdd'), 'd'), 1, 18, 7, 19, 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(t.calendar_date, 'yyyymmdd') = decode(to_char(to_date((to_char(add_months(Sysdate, 1), 'yyyymm') || 20), 'yyyymmdd'), 'd'), 1, to_char(add_months(Sysdate, 1), 'yyyymm') || 18, 7, to_char(add_months(Sysdate, 1), 'yyyymm') || 19, to_char(add_months(Sysdate, 1), '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 Null 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 ]]></bm:query-sql> </bm:operation> <bm:operation name="update"> <bm:update-sql><![CDATA[ begin contract_item_query_pkg.send_excel_mail(p_file_name => ${@file_name}, p_file_path => ${@file_path} , p_file_size => ${@file_size}, p_user_id => -1 ); end; ]]></bm:update-sql> </bm:operation> </bm:operations> </bm:model>