<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-11-19 下午2:10: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 t1.* from (SELECT
                    rt.base_rate_type,
                    rs.currency,
                    r.months_from,
                    r.months_to,
                    r.base_rate
                FROM
                    fnd_base_rate_type rt,
                    fnd_base_rate_set rs ,
                    fnd_base_rate r
                WHERE
                    rt.base_rate_type=rs.base_rate_type AND
                    r.base_rate_set  =rs.base_rate_set AND
                    --rt.enabled_flag  ='Y' AND
                    --rs.enabled_flag  ='Y' and
                    rt.base_rate_type=${@base_rate_type} and
                    rs.currency=${@currency} and
                    months_between(to_date(nvl(${@loan_date_to},'3000-01-01'),'yyyy-mm-dd'),
                    to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd')) >r.months_from
                    and 
                    months_between(to_date(nvl(${@loan_date_to},'3000-01-01'),'yyyy-mm-dd'),
                    to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd')) <=r.months_to
                    and    to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd')>=trunc(rs.valid_from)
                    and    trunc(to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd'))<=nvl(rs.valid_to,to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd'))
                    and rt.base_rate_type in('FIXED','PBOC','LPR')
                    and rs.approve_status = 'APPROVED'
                 UNION
                SELECT
                    rt.base_rate_type,
                    rs.currency,
                    r.months_from,
                    r.months_to,
                    r.base_rate
                FROM
                    fnd_base_rate_type rt,
                    fnd_base_rate_set rs ,
                    fnd_base_rate r
                WHERE
                    rt.base_rate_type=rs.base_rate_type AND
                    r.base_rate_set  =rs.base_rate_set AND
                    --rt.enabled_flag  ='Y' AND
                    --rs.enabled_flag  ='Y' and
                    rt.base_rate_type=${@base_rate_type} and
                    rs.currency=${@currency} and 
                   decode(nvl(${@interest_period},1),12,0.5,4,1.5,2,4.5,1,9)
                    between r.months_from and r.months_to
                    and    to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd')>=trunc(rs.valid_from)
                    and    trunc(to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd'))<=nvl(rs.valid_to,to_date(nvl(${@loan_date_from},'3000-01-01'),'yyyy-mm-dd'))
                    and rt.base_rate_type not in('FIXED','PBOC','LPR')
                    and rs.approve_status = 'APPROVED'
                ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
