<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hp  
    $Date: 2015-3-20 下午3:22:57  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="CUS_CON_FINANCE_INCOME_LV">
    <bm:fields>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" prompt="CUS_CON_FINANCE_INCOME_LV.BP_ID_TENANT"/>
        <bm:field name="bp_id_tenant_c" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_TENANT_C" prompt="CUS_CON_FINANCE_INCOME_LV.BP_ID_TENANT_C"/>
        <bm:field name="bp_id_tenant_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_TENANT_N" prompt="CUS_CON_FINANCE_INCOME_LV.BP_ID_TENANT_N"/>
        <bm:field name="penalty" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PENALTY" prompt="CUS_CON_FINANCE_INCOME_LV.PENALTY"/>
        <bm:field name="penalty_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PENALTY_B" prompt="CUS_CON_FINANCE_INCOME_LV.PENALTY_B"/>
        <bm:field name="penalty_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PENALTY_C" prompt="CUS_CON_FINANCE_INCOME_LV.PENALTY_C"/>
        <bm:field name="residual_value" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RESIDUAL_VALUE" prompt="CUS_CON_FINANCE_INCOME_LV.RESIDUAL_VALUE"/>
        <bm:field name="residual_value_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RESIDUAL_VALUE_B" prompt="CUS_CON_FINANCE_INCOME_LV.RESIDUAL_VALUE_B"/>
        <bm:field name="residual_value_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RESIDUAL_VALUE_C" prompt="CUS_CON_FINANCE_INCOME_LV.RESIDUAL_VALUE_C"/>
        <bm:field name="total_rental" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TOTAL_RENTAL" prompt="CUS_CON_FINANCE_INCOME_LV.TOTAL_RENTAL"/>
        <bm:field name="total_rental_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TOTAL_RENTAL_B" prompt="CUS_CON_FINANCE_INCOME_LV.TOTAL_RENTAL_B"/>
        <bm:field name="total_rental_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TOTAL_RENTAL_C" prompt="CUS_CON_FINANCE_INCOME_LV.TOTAL_RENTAL_C"/>
        <bm:field name="lease_charge" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_CHARGE" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_CHARGE"/>
        <bm:field name="lease_charge_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_CHARGE_B" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_CHARGE_B"/>
        <bm:field name="lease_charge_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_CHARGE_C" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_CHARGE_C"/>
        <bm:field name="lease_mgt_fee" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_MGT_FEE" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_MGT_FEE"/>
        <bm:field name="lease_mgt_fee_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_MGT_FEE_B" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_MGT_FEE_B"/>
        <bm:field name="lease_mgt_fee_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_MGT_FEE_C" prompt="CUS_CON_FINANCE_INCOME_LV.LEASE_MGT_FEE_C"/>
        <bm:field name="overdue_interest" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OVERDUE_INTEREST" prompt="CUS_CON_FINANCE_INCOME_LV.OVERDUE_INTEREST"/>
        <bm:field name="overdue_interest_b" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OVERDUE_INTEREST_B" prompt="CUS_CON_FINANCE_INCOME_LV.OVERDUE_INTEREST_B"/>
        <bm:field name="overdue_interest_c" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OVERDUE_INTEREST_C" prompt="CUS_CON_FINANCE_INCOME_LV.OVERDUE_INTEREST_C"/>
        <bm:field name="company_id"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[ 
            select * from (
     SELECT tt.bp_id_tenant,
tt.company_id,
       (SELECT h.bp_code
          FROM hls_bp_master h
         WHERE h.bp_id = tt.bp_id_tenant) bp_id_tenant_c,
       (SELECT h.bp_name
          FROM hls_bp_master h
         WHERE h.bp_id = tt.bp_id_tenant) bp_id_tenant_n,

       SUM(nvl(tt.penalty, 0)) penalty,
       SUM(nvl(tt.penalty_b, 0)) penalty_b,
       SUM(nvl(tt.penalty_b, 0)) - SUM(nvl(tt.penalty, 0)) penalty_c,
       
       SUM(nvl(tt.residual_value, 0)) residual_value,
       SUM(nvl(tt.residual_value_b, 0)) residual_value_b,
       SUM(nvl(tt.residual_value_b, 0)) - SUM(nvl(tt.residual_value, 0)) residual_value_c,
       
       
        SUM(nvl(tt.rental, 0)) total_rental,
       SUM(nvl(tt.rental_b, 0)) total_rental_b,
       SUM(nvl(tt.rental_b, 0)) - SUM(nvl(tt.rental, 0)) total_rental_c,

 

       SUM(nvl(tt.lease_charge, 0)) lease_charge,
       SUM(nvl(tt.lease_charge_b, 0)) lease_charge_b,
       SUM(nvl(tt.lease_charge_b, 0)) - SUM(nvl(tt.lease_charge, 0)) lease_charge_c,

       SUM(nvl(tt.lease_mgt_fee, 0)) lease_mgt_fee,
       SUM(nvl(tt.lease_mgt_fee_b, 0)) lease_mgt_fee_b,
       SUM(nvl(tt.lease_mgt_fee_b, 0)) - SUM(nvl(tt.lease_mgt_fee, 0)) lease_mgt_fee_c,

       SUM(nvl(tt.overdue_interest, 0)) overdue_interest,
       SUM(nvl(tt.overdue_interest_b, 0)) overdue_interest_b,
       SUM(nvl(tt.overdue_interest_b, 0)) -
       SUM(nvl(tt.overdue_interest, 0)) overdue_interest_c

  FROM (SELECT cc.bp_id_tenant,
               cc.company_id,
               
                (SELECT SUM(nvl(cfi.finance_income, 0))
                  FROM con_contract_cashflow cf, con_finance_income cfi
                 WHERE 1 = 1
                   AND cf.cashflow_id = cfi.cashflow_id
                   AND cf.contract_id = cfi.contract_id
                   AND cf.contract_id = cc.contract_id
                   AND cfi.accounting_date <
                       trunc(add_months(last_day(to_date(${@period_date},'yyyy-mm-dd')), -1) + 1)
                   AND cf.cf_item = 1
                   and cfi.reversed_flag = 'N'
                   AND cf.cf_status = 'RELEASE'
                   AND NOT (cf.times = 0 AND cf.cf_direction = 'NONCASH')) rental, --租金收入
               (SELECT SUM(nvl(cfi.finance_income, 0))
                  FROM con_contract_cashflow cf, con_finance_income cfi
                 WHERE 1 = 1
                   AND cf.cashflow_id = cfi.cashflow_id
                   AND cf.contract_id = cfi.contract_id
                   AND cf.contract_id = cc.contract_id
                   AND cfi.accounting_date< trunc(last_day(to_date(${@period_date},'yyyy-mm-dd'))) + 0.01
                   AND cf.cf_item = 1
                   and cfi.reversed_flag = 'N'
                   AND cf.cf_status = 'RELEASE'
                   AND NOT (cf.times = 0 AND cf.cf_direction = 'NONCASH')) rental_b,
               
               (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item = 9
               and w.write_off_date <
                       trunc(add_months(last_day(to_date(${@period_date},'yyyy-mm-dd')), -1) + 1))  penalty, --滞纳金
                       
              (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item = 9
               and w.write_off_date < trunc(last_day(to_date(${@period_date},'yyyy-mm-dd'))) + 0.01)  penalty_b, --滞纳金   
               
               
                (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item = 8
               and w.write_off_date <
                       trunc(add_months(last_day(to_date(${@period_date},'yyyy-mm-dd')), -1) + 1))  residual_value, --留购金
                       
              (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item = 8
               and w.write_off_date < trunc(last_day(to_date(${@period_date},'yyyy-mm-dd'))) + 0.01)  residual_value_b, --留购金   
               
               (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item IN (11, 131) --提前结清手续费 支付期平移手续费（流入）
               and w.write_off_date <
                       trunc(add_months(last_day(to_date(${@period_date},'yyyy-mm-dd')), -1) + 1))  LEASE_CHARGE, --手续费
                       
              (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item IN (11, 131)
               and w.write_off_date < trunc(last_day(to_date(${@period_date},'yyyy-mm-dd'))) + 0.01) LEASE_CHARGE_B, --手续费   
               
                (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item = 3
               and w.write_off_date <
                       trunc(add_months(last_day(to_date(${@period_date},'yyyy-mm-dd')), -1) + 1))  LEASE_MGT_FEE, --管理费
                       
        (select sum(nvl(w.write_off_due_amount,0)) from csh_write_off w 
               where w.contract_id =  cc.contract_id
               and w.cf_item =3
               and w.write_off_date < trunc(last_day(to_date(${@period_date},'yyyy-mm-dd'))) + 0.01) LEASE_MGT_FEE_B, --管理费   

                (select  SUM(nvl(fir.finance_income, 0)) from  con_finance_income_reverse fir where fir.contract_id = cc.contract_id
            --   and fir.company_id = cc.company_id
               and fir.je_internal_period_num <
                       to_char(sysdate,'YYYY-MM')) OVERDUE_INTEREST, --逾期利息红冲

 
                (select  SUM(nvl(fir.finance_income, 0)) from  con_finance_income_reverse fir where fir.contract_id = cc.contract_id
              -- and fir.company_id = cc.company_id
               and fir.je_internal_period_num <=
                       to_char(sysdate,'YYYY-MM')) OVERDUE_INTEREST_b --逾期利息红冲

          FROM con_contract cc
         WHERE 1 = 1
         and cc.data_class = 'NORMAL'
  and cc.contract_status in ('INCEPT','PENDING')) tt
 GROUP BY tt.bp_id_tenant,tt.company_id  ) t1
 #WHERE_CLAUSE# ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:query-fields>
        <bm:query-field field="company_id" queryOperator="="/>
        <bm:query-field field="bp_id_tenant" queryOperator="="/>
        <bm:query-field field="bp_id_tenant_c" queryOperator="="/>
        <bm:query-field field="bp_id_tenant_n" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
