<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Harry  
    $Date: 2017-2-15 下午7:59:08  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    (SELECT
                        NVL(SUM(principal),0)
                    FROM
                        (SELECT
                            *
                        FROM
                            (SELECT
                                t.due_amount,
                                t.received_amount,
                                t.principal - NVL(t.received_principal, 0) principal,
                                t.interest - NVL(t.received_interest, 0) interest
                            FROM
                                con_contract_cashflow t
                            WHERE
                                t.contract_id     = ${@contract_id} AND
                                t.write_off_flag <> 'FULL' AND
                                t.cf_direction    = 'INFLOW' AND
                                t.cf_item        != 202
                            ORDER BY
                                t.times,
                                t.cf_type
                            )
                        UNION ALL
                        SELECT
                            l.due_amount,
                            NULL received_amount,
                            l.principal,
                            l.interest
                        FROM
                            con_contract_et_ln l,
                            con_contract_et_hd h
                        WHERE
                            l.et_agreement_id = ${@et_agreement_id} AND
                            l.et_agreement_id = h.et_agreement_id AND
                            h.status         <> 'APPROVED'
                        )
                    ) sum_principal,
                    (SELECT
                        NVL(SUM(interest),0)
                    FROM
                        (SELECT
                            *
                        FROM
                            (SELECT
                                t.due_amount,
                                t.received_amount,
                                t.principal - NVL(t.received_principal, 0) principal,
                                t.interest - NVL(t.received_interest, 0) interest
                            FROM
                                con_contract_cashflow t
                            WHERE
                                t.contract_id     = ${@contract_id} AND
                                t.write_off_flag <> 'FULL' AND
                                t.cf_direction    = 'INFLOW'
                            ORDER BY
                                t.times,
                                t.cf_type
                            )
                        UNION ALL
                        SELECT
                            l.due_amount,
                            NULL received_amount,
                            l.principal,
                            l.interest
                        FROM
                            con_contract_et_ln l,
                            con_contract_et_hd h
                        WHERE
                            l.et_agreement_id = ${@et_agreement_id} AND
                            l.et_agreement_id = h.et_agreement_id AND
                            h.status         <> 'APPROVED'
                        )
                    ) sum_interest,
                    (SELECT
                        NVL(SUM(due_amount),0) - NVL(SUM(received_amount),0)
                    FROM
                        (SELECT
                            *
                        FROM
                            (SELECT
                                t.due_amount,
                                t.received_amount,
                                t.principal - NVL(t.received_principal, 0) principal,
                                t.interest - NVL(t.received_interest, 0) interest
                            FROM
                                con_contract_cashflow t
                            WHERE
                                t.contract_id     = ${@contract_id} AND
                                t.write_off_flag <> 'FULL' AND
                                t.cf_direction    = 'INFLOW' AND
                                t.cf_type         = 1
                            ORDER BY
                                t.times,
                                t.cf_type
                            )
                        UNION ALL
                        SELECT
                            l.due_amount,
                            NULL received_amount,
                            l.principal,
                            l.interest
                        FROM
                            con_contract_et_ln l,
                            con_contract_et_hd h
                        WHERE
                            l.et_agreement_id = ${@et_agreement_id} AND
                            l.et_agreement_id = h.et_agreement_id AND
                            h.status         <> 'APPROVED'
                        )
                    ) sum_due_amount
                FROM
                    dual
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="contract_id"/>
                <bm:parameter name="et_agreement_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
