<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: whumeister  
    $Date: 2015-10-12 下午7:57:05  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    tt.*
                FROM
                    (SELECT
                        rownum row_num,
                        c.contract_id,
                        c.contract_number, ----合同编号
                        c.bp_id_tenant,
                        c.bp_id_tenant_n, --主承租人名称
                        (SELECT
                            SUM(cf.write_off_due_amount)
                        FROM
                            csh_write_off cf,
                            csh_transaction ct,
                            con_contract_cashflow cc
                        WHERE
                            cf.csh_transaction_id = ct.transaction_id AND
                            cf.contract_id        = c.contract_id AND
                            cc.cashflow_id        =cf.cashflow_id AND
                            cc.cf_direction       ='INFLOW' AND
                            ct.bp_id              = p.bp_id AND
                            cf.reversed_flag      = 'N' AND
                            ct.reversed_flag      = 'N'
                        ) sum_tenant_amount,                                             --厂商垫付金额
                        DECODE(c.overdue_status, 'Y', '已逾期', 'N', '未逾期') overdue_status, --逾期状态
                        p.bp_name,                                                       --推荐厂商
                        p.bp_id bp_id_v,
                        (SELECT
                            TO_CHAR(MAX(ct.transaction_date), 'yyyy-mm-dd')
                        FROM
                            csh_transaction ct,
                            csh_write_off cf
                        WHERE
                            cf.csh_transaction_id = ct.transaction_id AND
                            cf.contract_id        = c.contract_id AND
                            ct.bp_id              = p.bp_id
                        ) last_transaction_date, --厂商最后垫付日期
                        (SELECT
                            cl.due_amount
                        FROM
                            con_contract_cashflow cl,
                            csh_transaction ct,
                            csh_write_off cf
                        WHERE
                            ct.transaction_id   = cf.csh_transaction_id AND
                            cf.cashflow_id      = cl.cashflow_id AND
                            cl.contract_id      = c.contract_id AND
                            ct.bp_id            = p.bp_id AND
                            cf.contract_id      = c.contract_id AND
                            ct.transaction_date =
                            (SELECT
                                MAX(ct.transaction_date)
                            FROM
                                csh_transaction ct,
                                csh_write_off cf,
                                con_contract_cashflow cl
                            WHERE
                                cf.csh_transaction_id = ct.transaction_id AND
                                cf.cashflow_id        =cl.cashflow_id AND
                                cf.contract_id        = c.contract_id AND
                                ct.bp_id              = p.bp_id
                            ) AND
                            cf.cf_item = 1 AND
                            rownum     = 1
                        ) last_due_amount, --每期租金
                        (SELECT
                            COUNT(ct.bp_id)
                        FROM
                            csh_write_off cf,
                            con_contract_cashflow cc,
                            csh_transaction ct
                        WHERE
                            cf.csh_transaction_id = ct.transaction_id AND
                            cc.cashflow_id =cf.cashflow_id and
                            cc.cf_direction ='INFLOW' and
                            cf.contract_id        = c.contract_id AND
                            ct.bp_id              = p.bp_id AND
                            cf.reversed_flag      = 'N' AND
                            ct.reversed_flag      = 'N'
                        ) last_reserved_times --垫付次数
                    FROM
                        con_contract_lv c,
                        con_contract_bp_lv p
                    WHERE
                        c.contract_id    = p.contract_id AND
                        p.bp_category    = 'VENDER' AND
                        c.data_class     = 'NORMAL' AND
                        c.business_type <> 'LEASEBACK' AND
                        p.bp_id         IN
                        (SELECT
                            t.bp_id
                        FROM
                            csh_write_off cf,
                            csh_transaction t ,
                            con_contract_cashflow cc
                        WHERE
                            cf.csh_transaction_id = t.transaction_id AND
                            cc.cashflow_id        =cf.cashflow_id AND
                            cc.cf_direction       ='INFLOW' AND
                            cf.contract_id        = c.contract_id AND
                            t.bp_id               = p.bp_id AND
                            cf.reversed_flag      = 'N' AND
                            t.reversed_flag       = 'N'
                        )
                    ) tt #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="tt.contract_number like ${@contract_number}"/>
        <bm:query-field name="bp_id_tenant_n" queryExpression="tt.bp_id_tenant_n like ${@bp_id_tenant_n}"/>
        <bm:query-field name="bp_name" queryExpression="tt.bp_name like ${@bp_name}"/>
    </bm:query-fields>
</bm:model>
