<?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
                    *
                FROM
                    (SELECT
                        b.contract_id,
                        b.contract_number,
                        b.contract_name,
                        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 AND
                        b.data_class          = 'NORMAL'
                    GROUP BY
                        b.contract_number,
                        b.contract_name,
                        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="contract_id"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" required="true"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" required="true"/>
        <bm:field name="bp_id_tenant_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_TENANT_N"/>
        <bm:field name="bp_id_agent_level1" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_AGENT_LEVEL1"/>
        <bm:field name="bp_id_agent_level1_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_AGENT_LEVEL1_N"/>
        <bm:field name="division"/>
        <bm:field name="division_n"/>
        <bm:field name="lease_organization_n"/>
        <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="overdue_amount"/>
        <bm:field name="should_confirmed_sum_income"/>
        <bm:field name="overdue_amount_out_ninety"/>
        <bm:field name="overdue_amount_in_ninety"/>
        <bm:field name="overdue_un_interest"/>
        <bm:field name="contract_name"/>
    </bm:fields>
</bm:model>
