<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: syj  
    $Date:2015年7月28日11:53:36
    $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
                    c.contract_id,
                    b.bp_id,
                    b.bp_name,
                    c.contract_number,
                    c.contract_number
                    || '-1' payment_number,
                    cc.times,
                    change_number_to_cn(extract(YEAR FROM c.lease_start_date)) lease_start_year,
                    change_number_to_cn(extract(MONTH FROM c.lease_start_date)) lease_start_month,
                    change_number_to_cn(extract(DAY FROM c.lease_start_date)) lease_start_day,
                    change_number_to_cn(extract(YEAR FROM cc.due_date)) due_year,
                    change_number_to_cn(extract(MONTH FROM cc.due_date)) due_month,
                    change_number_to_cn(extract(DAY FROM cc.due_date)) due_day,
                    TO_CHAR(cc.due_amount, 'FM999,999,999,999.00') due_amount,
                    TO_CHAR(cc.net_due_amount, 'FM999,999,999,999.00') net_due_amount,
                    '' penalty_amount, --违约金比例是合同上手工填写的 系统中没有维护 无法算出违约金
                    change_number_to_cn(extract(YEAR FROM c.lease_end_date)) lease_end_year,
                    change_number_to_cn(extract(MONTH FROM c.lease_end_date)) lease_end_month,
                    change_number_to_cn(extract(DAY FROM c.lease_end_date)) lease_end_day,
                    TO_CHAR(ROUND(NVL(f.tax_type_rate, 0) * 100.0, 2))
                    || '%' tax_type_rate,
                    fc.bank_branch_name,
                    fc.bank_account_name,
                    fc.bank_account_num,
                    change_number_to_cn(extract(YEAR FROM SYSDATE)) year,
                    change_number_to_cn(extract(MONTH FROM SYSDATE)) month ,
                    change_number_to_cn(extract(DAY FROM SYSDATE)) day
                FROM
                    con_contract c,
                    hls_bp_master b,
                    con_contract_cashflow cc,
                    hls_fin_calculator_hd h,
                    fnd_tax_type_codes f,
                    fnd_companies fc
                WHERE
                    c.contract_id     = cc.contract_id AND
                    cc.cashflow_id    = ${@cashflow_id} AND
                    c.contract_id     = cc.contract_Id AND
                    h.calc_session_id = c.calc_session_id AND
                    f.tax_type_id     = h.tax_type_id AND
                    fc.company_id     = c.company_id AND
                    b.bp_id           = ${@bp_id}
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
