<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hp  
    $Date: 2015-3-27 上午10:25:08  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" defaultOrderBy="t1.contract_number desc">
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CONTRACT_ID"/>
        <bm:field name="calc_session_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CALC_SESSION_ID" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CALC_SESSION_ID"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CONTRACT_NAME"/>
        <bm:field name="search_term_1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SEARCH_TERM_1" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.SEARCH_TERM_1"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.BP_ID_TENANT"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CONTRACT_STATUS"/>
        <bm:field name="contract_status_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS_N" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.CONTRACT_STATUS_N"/>
        <bm:field name="bp_id_tenant_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_TENANT_N" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.BP_ID_TENANT_N"/>
        <bm:field name="serial_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SERIAL_NUMBER" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.SERIAL_NUMBER"/>
        <bm:field name="specification" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SPECIFICATION" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.SPECIFICATION"/>
        <bm:field name="lease_term" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_TERM" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEASE_TERM"/>
        <bm:field name="pay_type" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PAY_TYPE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.PAY_TYPE"/>
        <bm:field name="pay_type_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PAY_TYPE_N" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.PAY_TYPE_N"/>
        <bm:field name="dd_bank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DD_BANK_ACCOUNT_NUM" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DD_BANK_ACCOUNT_NUM"/>
        <bm:field name="inception_of_lease" databaseType="DATE" datatype="java.util.Date" physicalName="INCEPTION_OF_LEASE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.INCEPTION_OF_LEASE"/>
        <bm:field name="lease_start_date" databaseType="DATE" datatype="java.util.Date" physicalName="LEASE_START_DATE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEASE_START_DATE"/>
        <bm:field name="lease_end_date" databaseType="DATE" datatype="java.util.Date" physicalName="LEASE_END_DATE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEASE_END_DATE"/>
        <bm:field name="due_amount_all" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DUE_AMOUNT_ALL" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DUE_AMOUNT_ALL"/>
        <bm:field name="due_amount_current" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DUE_AMOUNT_CURRENT" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DUE_AMOUNT_CURRENT"/>
        <bm:field name="due_amount_old" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DUE_AMOUNT_OLD" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DUE_AMOUNT_OLD"/>
        <bm:field name="due_amount_now" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DUE_AMOUNT_NOW" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DUE_AMOUNT_NOW"/>
        <bm:field name="penalty_1" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PENALTY_1" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.PENALTY_1"/>
        <bm:field name="penalty_sum" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PENALTY_SUM" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.PENALTY_SUM"/>
        <bm:field name="lease_mgt_fee" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_MGT_FEE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEASE_MGT_FEE"/>
        <bm:field name="deposit" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DEPOSIT" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.DEPOSIT"/>
        <bm:field name="insurance_fee" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INSURANCE_FEE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.INSURANCE_FEE"/>
        <bm:field name="notarial_fee" databaseType="NUMBER" datatype="java.lang.Long" physicalName="NOTARIAL_FEE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.NOTARIAL_FEE"/>
        <bm:field name="legal_fee" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEGAL_FEE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEGAL_FEE"/>
        <bm:field name="lease_charge" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LEASE_CHARGE" prompt="CUS_CON_CONTACT_AR_DETAIL_LV.LEASE_CHARGE"/>
        <bm:field name="ar_description"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
	SELECT t1.contract_id,
       t1.calc_session_id,
       t1.contract_number,
       t1.contract_name,
       t1.search_term_1,
       t1.bp_id_tenant,
       t1.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = t1.contract_status) AS contract_status_n,
       (SELECT m.bp_name
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_tenant) AS bp_id_tenant_n,
       (SELECT dbms_lob.substr(wmsys.wm_concat(ci.serial_number))
          FROM con_contract_lease_item ci
         WHERE ci.contract_id = t1.contract_id
           AND ci.serial_number IS NOT NULL) serial_number,
       (SELECT dbms_lob.substr(wmsys.wm_concat(ci.specification))
          FROM con_contract_lease_item ci
         WHERE ci.contract_id = t1.contract_id
           AND ci.specification IS NOT NULL) specification,
       t1.lease_term,
       t1.pay_type,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_PAY_TYPE'
           AND v.code_value = t1.pay_type) AS pay_type_n,
       t1.dd_bank_account_num,
       t1.inception_of_lease,
       t1.lease_start_date,
       t1.lease_end_date,
       nvl((SELECT dbms_lob.substr(wmsys.wm_concat('第' || cf.times || '期:' ||
                                               cf.due_amount))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 1
           AND cf.cf_status = 'RELEASE'),0) due_amount_all,
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 1
           AND trunc(cf.due_date) BETWEEN 
               trunc(add_months(last_day(to_date(${@end_date},'yyyy-mm-dd')), -1) + 1) AND
               trunc(to_date(${@end_date},'yyyy-mm-dd')) + 0.01
           AND cf.cf_status = 'RELEASE'),0) due_amount_current,
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 1
           AND trunc(cf.due_date) <
               trunc(add_months(last_day(to_date(${@end_date},'yyyy-mm-dd')), -1) + 1)
           AND cf.cf_status = 'RELEASE'),0) due_amount_old,
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 1
           AND trunc(cf.due_date) < trunc(to_date(${@end_date},'yyyy-mm-dd')) + 0.01
           AND cf.cf_status = 'RELEASE'),0) due_amount_now,
       nvl((SELECT cf.due_amount
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.times = 1
           AND cf.cf_item = 9
           AND cf.cf_status = 'RELEASE'),0) penalty_1, --第一期罚息
       --总罚息
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 9
           AND cf.cf_status = 'RELEASE'),0) penalty_sum,

       --管理费
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 3
           AND cf.cf_direction = 'INFLOW'
           AND cf.cf_status = 'RELEASE'),0) lease_mgt_fee,

       --保证金
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 51
           AND cf.cf_direction = 'INFLOW'
           AND cf.cf_status = 'RELEASE'),0) DEPOSIT,

       --保险费
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 71
           AND cf.cf_direction = 'INFLOW'
           AND cf.cf_status = 'RELEASE'),0) insurance_fee,

       --公证费
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 72
           AND cf.cf_status = 'RELEASE'),0) NOTARIAL_FEE,

       --法律费用
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_type = 200
           AND cf.cf_direction = 'INFLOW'
           AND cf.cf_status = 'RELEASE'),0) LEGAL_FEE,

       --手续费 wei
       nvl((SELECT SUM(nvl(cf.due_amount, 0) - nvl(cf.received_amount, 0))
          FROM con_contract_cashflow cf
         WHERE cf.contract_id = t1.contract_id
           AND cf.cf_item = 3
           AND cf.cf_direction = 'INFLOW'
           AND cf.cf_status = 'RELEASE'),0) lease_charge,
           
    cus_con_lease_item_pkg.get_description(p_contract_id =>t1.contract_id,
                           p_period_name =>substr(${@end_date}, 0, 7))  ar_description          

  FROM con_contract t1
 #WHERE_CLAUSE#

]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="search_term_1" queryExpression="t1.search_term_1 like ${@search_term_1}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like ${@contract_name}"/>
        <bm:query-field name="division" queryExpression="t1.division like ${@division}"/>
        <bm:query-field name="bp_id_tenant_c" queryExpression="t1.bp_id_tenant_c like ${@bp_id_tenant_c}"/>
        <bm:query-field name="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n like ${@bp_id_tenant_n}"/>
        <bm:query-field name="transition_date" queryExpression="t1.transition_date = ${@transition_date}"/>
        <bm:query-field name="serial_number" queryExpression="t1.serial_number like ${@serial_number}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression="t1.contract_status in (&apos;INCEPT&apos;,&apos;PENDING&apos;) and  t1.data_class = &apos;NORMAL&apos;"/>
    </bm:data-filters>
</bm:model>
