<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: syj  
    $Date: 2015年7月29日11:50:44
    $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
                    *
                FROM
                    (SELECT
                        c.contract_id,
                        substr(c.contract_number,1,(length(c.contract_number)-2)) contract_number,
                        m.bp_name,
                        (SELECT
                            ccb.bp_name
                        FROM
                            con_contract_bp ccb
                        WHERE
                            ccb.contract_id = c.contract_id AND
                            ccb.bp_type     = 'TENANT_SEC'
                        ) bp_name_sec,
                        m.bp_name
                        ||
                        (SELECT
                            '、'
                            || ccb.bp_name
                        FROM
                            con_contract_bp ccb
                        WHERE
                            ccb.contract_id = c.contract_id AND
                            ccb.bp_type     = 'TENANT_SEC'
                        ) all_bp_name,
                        r.description lease_term,
                        TO_CHAR(NVL(c.int_rate * 100.0, 0))
                        || '%' int_rate,
                        TO_CHAR(NVL(c.base_rate * 100.0, 0))
                        || '%' base_rate,
                        change_number_to_cn(substr(extract(YEAR FROM (SELECT MIN(o.write_off_date)
                                             FROM csh_write_off o
                                            WHERE o.write_off_type =
                                                  'PAYMENT_DEBT'
                                              AND o.contract_id =
                                                  c.contract_id
                                              AND o.reversed_flag = 'N'
                                              AND o.cf_item = 0
                                              AND o.cf_type = 0)),
                                  -1,
                                  1)) lease_pay_year,
                        change_number_to_cn(extract(MONTH FROM
                        (SELECT
                            MIN(o.write_off_date)
                        FROM
                            csh_write_off o
                        WHERE
                            o.write_off_type = 'PAYMENT_DEBT' AND
                            o.contract_id    = c.contract_id AND
                            o.reversed_flag  = 'N' AND
                            o.cf_item        = 0 AND
                            o.cf_type        = 0
                        ))) lease_pay_month,
                        change_number_to_cn(extract(DAY FROM
                        (SELECT
                            MIN(o.write_off_date)
                        FROM
                            csh_write_off o
                        WHERE
                            o.write_off_type = 'PAYMENT_DEBT' AND
                            o.contract_id    = c.contract_id AND
                            o.reversed_flag  = 'N' AND
                            o.cf_item        = 0 AND
                            o.cf_type        = 0
                        ))) lease_pay_day,
                        change_number_to_cn(substr(extract(YEAR FROM s.valid_from), -1, 1)) valid_year,
                        change_number_to_cn(extract(MONTH FROM s.valid_from)) valid_month,
                        change_number_to_cn(extract(DAY FROM s.valid_from)) valid_day,
                        change_number_to_cn(substr(extract(YEAR FROM c.flt_next_adj_date),
                                  -1,
                                  1)) adj_year,
                        change_number_to_cn(extract(MONTH FROM c.flt_next_adj_date)) adj_month,
                        change_number_to_cn(extract(DAY FROM c.flt_next_adj_date)) adj_day,
                        change_number_to_cn(substr(extract(YEAR FROM c.inception_of_lease),
                                  -1,
                                  1)) inception_of_lease_year,
                        change_number_to_cn(extract(MONTH FROM c.inception_of_lease)) inception_of_lease_month,
                        change_number_to_cn(extract(DAY FROM c.inception_of_lease)) inception_of_lease_day,
                        TO_CHAR(c.finance_amount, 'FM999,999,999,999.00') finance_amount,
                        change_number_to_rmb(c.finance_amount) finance_amount_rmb,
                        c.contract_number payment_number
                    FROM
                        con_contract c,
                        hls_bp_master m,
                        hls_fin_calculator_hd h,
                        fnd_base_rate_set s,
                        fnd_base_rate r
                    WHERE
                        c.bp_id_tenant     = m.bp_id AND
                        c.calc_session_id  = h.calc_session_id AND
                        s.enabled_flag     = 'Y' AND
                        s.valid_to        IS NULL AND
                        s.base_rate_set    = r.base_rate_set AND
                        h.lease_term * 12 <= r.months_to AND
                        h.lease_term * 12  > r.months_from
                    )t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="t1.contract_id=${@contract_id}"/>
    </bm:query-fields>
</bm:model>
