<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hongquan.dai  
    $Date: 2018-02-07 下午18:56:15  
    $Revision: 1.0  
    $Purpose: 支付表收益分摊查询
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" defaultOrderBy="T1.contract_number" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
       select sum(nvl(sum_un_fin_income,0)) as sum_un_fin_income,
              sum(nvl(ensure_sum_un_fin_income,0)) as ensure_sum_un_fin_income,
              sum(nvl(unensure_sum_un_fin_income,0)) as unensure_sum_un_fin_income,
              sum(nvl(should_confirmed_sum_income,0)) as should_confirmed_sum_income
       from (
       select b.contract_id,
       b.contract_number,
       b.bp_id_tenant,
       b.bp_id_tenant_n,
       b.bp_id_agent_level1,
       b.bp_id_agent_level1_n,
       b.division,
       b.division_n,
       b.lease_organization_n,
       sum(nvl(a.unearned_finance_income,0)) AS sum_un_fin_income,--收益分摊之和
       sum(DECODE(a.finance_income_recognized,'Y',nvl(a.unearned_finance_income,0),0)) AS ensure_sum_un_fin_income,--已确认收益分摊之和
       sum(DECODE(a.finance_income_recognized,'N',nvl(a.unearned_finance_income,0),0)) AS unensure_sum_un_fin_income,--未确认收益分摊之和   
       (SELECT SUM(nvl(c1.due_amount,0)) 
        FROM  con_contract_cashflow c1 
        where c1.contract_id = b.contract_id
        AND c1.cf_item = 1
        AND c1.due_date < SYSDATE
        AND NVL(c1.due_amount,0) - NVL(c1.received_amount,0) > 0
        GROUP BY c1.contract_id) AS overdue_amount,--逾期租金总额
       (SELECT SUM(nvl(c1.interest,0) - NVL(c1.received_interest,0))
        FROM  con_contract_cashflow c1 
        where c1.contract_id = b.contract_id
        AND c1.cf_item = 1
        AND c1.due_date < SYSDATE
        AND NVL(c1.due_amount,0) - NVL(c1.received_amount,0) > 0
        GROUP BY c1.contract_id) AS overdue_un_interest,--逾期未收利息总额
       sum(DECODE(a.finance_income_recognized,'Y',nvl(a.unearned_finance_income,0),0)) - (SELECT SUM(nvl(c1.interest,0) - NVL(c1.received_interest,0))
                                                                                          FROM  con_contract_cashflow c1 
                                                                                          where c1.contract_id = b.contract_id
                                                                                          AND c1.cf_item = 1
                                                                                          AND c1.due_date < SYSDATE
                                                                                          AND NVL(c1.due_amount,0) - NVL(c1.received_amount,0) > 0
                                                                                          GROUP BY c1.contract_id) AS should_confirmed_sum_income,--应确认收入总额（实际已确认收入-逾期未收利息总额）
       
       (SELECT SUM(nvl(c1.due_amount,0)) 
        FROM  con_contract_cashflow c1 
        where c1.contract_id = b.contract_id
        AND c1.cf_item = 1
        AND c1.due_date < SYSDATE - 90
        AND NVL(c1.due_amount,0) - NVL(c1.received_amount,0) > 0
        GROUP BY c1.contract_id) AS overdue_amount_out_ninety,--逾期大于90天租金总额
       (SELECT SUM(nvl(c1.due_amount,0)) 
        FROM  con_contract_cashflow c1 
        where c1.contract_id = b.contract_id
        AND c1.cf_item = 1
        AND c1.due_date < SYSDATE 
        AND c1.due_date >= SYSDATE - 90
        AND NVL(c1.due_amount,0) - NVL(c1.received_amount,0) > 0
        GROUP BY c1.contract_id) AS overdue_amount_in_ninety--90天以内逾期租金总额
  from con_unearned_finance_income a,
       con_contract_lv             b,
       gld_period_all_v          c
 where a.contract_id = b.contract_id
   and c.COMPANY_ID = ${/session/@company_id}
   and c.INTERNAL_PERIOD_NUM = a.internal_period_num
   and b.bp_id_tenant = nvl(${@bp_id_tenant}, b.bp_id_tenant)
   and b.contract_id = nvl(${@contract_id},b.contract_id)
   and b.bp_id_agent_level1 = nvl(${@bp_id_agent_level1},b.bp_id_agent_level1) 
   and a.cf_item = 1
 GROUP BY b.contract_number,
          b.bp_id_tenant,
          b.bp_id_tenant_n,
          b.bp_id_agent_level1,
          b.bp_id_agent_level1_n,
          b.division,
          b.division_n,
          b.lease_organization_n,
          b.contract_id ) t1
 #WHERE_CLAUSE#
 #ORDER_BY_CLAUSE#
 ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="sum_un_fin_income"/>
        <bm:field name="ensure_sum_un_fin_income"/>
        <bm:field name="unensure_sum_un_fin_income"/>
        <bm:field name="should_confirmed_sum_income"/>
    </bm:fields>
</bm:model>
