<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen 
    $Date: 2016-4-12 下午4:05:30  
    $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 
CASE WHEN T1.FORTH_CLASS_NAME IS NULL
  THEN
    T1.THIRD_CLASS_NAME
ELSE T1.THIRD_CLASS_NAME||'-'||T1.FORTH_CLASS_NAME 
  END  ITEM_DESC , --计划项目
T1.* 
FROM
 (select 
       bp.item_name, --项目名称
       (SELECT F1.FIRST_CLASS_NAME FROM SDIC_FIN_PLAN_FIRST_CLASS F1 WHERE F1.FIRST_CLASS_ID=BP.FIRST_CLASS_ID) FIRST_CLASS_NAME,
        (SELECT F2.SECOND_CLASS_NAME FROM SDIC_FIN_PLAN_SECOND_CLASS F2 WHERE F2.SECOND_CLASS_ID=BP.SECOND_CLASS_ID) SECOND_CLASS_NAME,
         (SELECT F3.THIRD_CLASS_NAME FROM SDIC_FIN_PLAN_THIRD_CLASS F3 WHERE F3.THIRD_CLASS_ID=BP.THIRD_CLASS_ID) THIRD_CLASS_NAME,
        (SELECT F4.FORTH_CLASS_NAME FROM SDIC_FIN_PLAN_FORTH_CLASS F4 WHERE F4.FORTH_CLASS_ID=BP.FORTH_CLASS_ID) FORTH_CLASS_NAME,
       bp.due_amount, --计划金额
       bp.due_date, --计划日期
       bp.item_term,
       bp.int_rate_fixing_way,
       bp.int_rate||'%',  --利率
       bp.description,
       bp.owner_user_id,
       bd.plan_year,--年份
       bd.plan_month, --月份
       bd.plan_frequency
  from sdic_finance_balance_plan bp, SDIC_FIN_PLAN_BASE_DATA bd
 where bp.data_id = bd.data_id
 and bp.cf_direction='INFLOW'
 AND BD.PLAN_FREQUENCY='MONTHLY'
 AND EXISTS(SELECT 1 FROM SDIC_FIN_PLAN_THIRD_CLASS TC WHERE 
 TC.THIRD_CLASS_ID=BP.THIRD_CLASS_ID
 AND TC.THIRD_CLASS_CODE in ('A31','A36')) )
 T1
#WHERE_CLAUSE#
 ORDER BY T1.plan_year,T1.plan_month
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="plan_year" queryExpression="t1.plan_year=${@plan_year}"/>
        <bm:query-field name="plan_month" queryExpression="t1.plan_month=${@plan_month}"/>
    </bm:query-fields>
</bm:model>
