<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Administrator  
    $Date: 2016-8-4 下午3:38:54  
    $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
                -- ci.description
                -- FROM
                -- con_contract_cashflow ccc,
                -- hls_cashflow_item ci
                -- WHERE
                -- ccc.cashflow_id=sai.cashflow_id AND
                -- ci.cf_item     =ccc.cf_item
                -- ) cf_item_desc,
                -- sai.net_due_amount,
                -- sai.vat_rate*100
                -- ||'%' vat_rate,
                -- sai.vat_due_amount
                -- FROM
                -- sdic_acr_invoice sai
                -- where sai.invoice_id in(${:@invoice_id_str})
                -- order by sai.invoice_date
                SELECT
                    TO_CHAR((ccc.principal / (1 + sai.vat_rate)) * sai.vat_rate, 'FM999,999,999,999,990.00') vat_due_amount,
                    TO_CHAR(ccc.principal - ROUND((ccc.principal / (1 + sai.vat_rate)) * sai.vat_rate, 2), 'FM999,999,999,999,990.00') net_due_amount,
                    sai.vat_rate * 100
                    || '%' vat_rate,
                    DECODE(
                    (SELECT
                        cc.business_type FROM con_contract cc WHERE cc.contract_id = ccc.contract_id
                    ), 'LEASE', '租金', 'LEASEBACK', '本金') cf_item_desc,
                    sai.invoice_id
                FROM
                    con_contract_cashflow ccc,
                    sdic_acr_invoice sai
                WHERE
                    ccc.contract_id = sai.contract_id AND
                    ccc.times       =
                    (SELECT
                        cct.times
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        sai.cashflow_id = cct.cashflow_id
                    ) AND
                    ccc.cf_item            = 1 AND
                    NVL(ccc.principal, 0) <> 0 AND
                    (SELECT
                            cct.cf_item
                        FROM
                            con_contract_cashflow cct
                        WHERE
                            sai.cashflow_id = cct.cashflow_id
                    )
                                  <> 15
                   and sai.cf_item_desc in ('本金',
                            '本金、租息',
                            '本金、租息、手续费',
                            '租金、租息',
                            '租金、租息、手续费') AND
                    sai.invoice_id in (${:@invoice_id_str})
                UNION
                SELECT
                    TO_CHAR((ccc.interest / (1 + sai.vat_rate)) * sai.vat_rate, 'FM999,999,999,999,990.00') vat_due_amount,
                    TO_CHAR(ccc.interest - ROUND((ccc.interest / (1 + sai.vat_rate)) * sai.vat_rate, 2), 'FM999,999,999,999,990.00') net_due_amount,
                    sai.vat_rate * 100
                    || '%' vat_rate,
                    '租息' cf_item_desc,
                    sai.invoice_id
                FROM
                    con_contract_cashflow ccc,
                    sdic_acr_invoice sai
                WHERE
                    ccc.contract_id = sai.contract_id AND
                    ccc.times       =
                    (SELECT
                        cct.times
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        sai.cashflow_id = cct.cashflow_id
                    ) AND
                    ccc.cf_item = 1 AND
                    (SELECT
                            cct.cf_item
                        FROM
                            con_contract_cashflow cct
                        WHERE
                            sai.cashflow_id = cct.cashflow_id
                    )
                                  <> 15
                    and sai.cf_item_desc in ('本金、租息',
                            '本金、租息、手续费',
                            '租息',
                            '租息、手续费',
                            '租金、租息',
                            '租金、租息、手续费') AND
                    sai.invoice_id in (${:@invoice_id_str})
                UNION
                SELECT
                    TO_CHAR((ccc.due_amount / (1 + sai.vat_rate)) * sai.vat_rate, 'FM999,999,999,999,990.00') vat_due_amount,
                    TO_CHAR(ccc.due_amount - ROUND((ccc.due_amount / (1 + sai.vat_rate)) * sai.vat_rate, 2), 'FM999,999,999,999,990.00') net_due_amount,
                    sai.vat_rate * 100
                    || '%' vat_rate,
                    '手续费' cf_item_desc,
                    sai.invoice_id
                FROM
                    con_contract_cashflow ccc,
                    sdic_acr_invoice sai
                WHERE
                    ccc.contract_id = sai.contract_id AND
                    ccc.times       =
                    (SELECT
                        cct.times
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        sai.cashflow_id = cct.cashflow_id
                    ) AND
                    ccc.cf_item = 3 AND
                    (SELECT
                            cct.cf_item
                        FROM
                            con_contract_cashflow cct
                        WHERE
                            sai.cashflow_id = cct.cashflow_id
                    )
                                  <> 15
                    and sai.cf_item_desc in ('本金、租息、手续费',
                            '手续费',
                            '租息、手续费',
                            '租金、租息、手续费') AND
                    sai.invoice_id in (${:@invoice_id_str})
                UNION
                SELECT
                    TO_CHAR((ccc.due_amount / (1 + sai.vat_rate)) * sai.vat_rate, 'FM999,999,999,999,990.00') vat_due_amount,
                    TO_CHAR(ccc.due_amount - ROUND((ccc.due_amount / (1 + sai.vat_rate)) * sai.vat_rate, 2), 'FM999,999,999,999,990.00') net_due_amount,
                    sai.vat_rate * 100
                    || '%' vat_rate,
                    '顾问服务费' cf_item_desc,
                    sai.invoice_id
                FROM
                    con_contract_cashflow ccc,
                    sdic_acr_invoice sai
                WHERE
                    ccc.contract_id = sai.contract_id AND
                    ccc.times       =
                    (SELECT
                        cct.times
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        sai.cashflow_id = cct.cashflow_id
                    ) AND
                    ccc.cf_item = 15 AND
                    (SELECT
                            cct.cf_item
                        FROM
                            con_contract_cashflow cct
                        WHERE
                            sai.cashflow_id = cct.cashflow_id
                    )
                    NOT           IN (1, 3)
                    and sai.cf_item_desc in ('顾问服务费') AND
                    sai.invoice_id in (${:@invoice_id_str})
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
