<?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" needAccessControl="false">
    <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="search_term_1"/>
        <bm:field name="contract_number"/>
        <bm:field name="contract_name"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="period_name"/>
        <bm:field name="lease_item_amount_c"/>
        <bm:field name="lease_item_amount"/>
        <bm:field name="insurance_fee_c"/>
        <bm:field name="down_payment_received_c"/>
        <bm:field name="lease_item_certi"/>
        <bm:field name="insurance_fee_received_c"/>
        <bm:field name="down_payment_invoice_c"/>
        <bm:field name="mgt_fee_invoice_c"/>
        <bm:field name="rental_invoice_c"/>
        <bm:field name="total_invoice_c"/>
        <bm:field name="amount_declare"/>
        <bm:field name="principal_invoice_c"/>
        <bm:field name="total_uninvoice_declare_c"/>
        <bm:field name="toal_declare"/>
        <bm:field name="vat_uninvoice_last_month"/>
        <bm:field name="vat_uninvoice_c"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
	SELECT tt.CONTRACT_ID,
       tt.period_name,
       tt.CALC_SESSION_ID,
       tt.CONTRACT_NUMBER,
       tt.CONTRACT_NAME,
       tt.SEARCH_TERM_1,
       tt.BP_ID_TENANT,
       tt.CONTRACT_STATUS,
       tt.CONTRACT_STATUS_N,
       tt.BP_ID_TENANT_N,
       tt.SERIAL_NUMBER,
       tt.SPECIFICATION,
       tt.LEASE_TERM,
       tt.PAY_TYPE,
       tt.PAY_TYPE_N,
       tt.DD_BANK_ACCOUNT_NUM,
       tt.INCEPTION_OF_LEASE,
       tt.LEASE_START_DATE,
       tt.LEASE_END_DATE,
       tt.LEASE_ITEM_AMOUNT,
       tt.LEASE_ITEM_AMOUNT_C,
       tt.INSURANCE_FEE_C,
       tt.DOWN_PAYMENT_RECEIVED_C,
       tt.LEASE_ITEM_CERTI,
       tt.INSURANCE_FEE_RECEIVED_C,
       tt.DOWN_PAYMENT_INVOICE_C,
       tt.MGT_FEE_INVOICE_C,
       tt.RENTAL_INVOICE_C,
       tt.TOTAL_INVOICE_C,
       (CASE
         WHEN tt.LEASE_ITEM_CERTI > 0 THEN
          tt.LEASE_ITEM_AMOUNT_C - tt.DOWN_PAYMENT_INVOICE_C
         ELSE
          0
       END) amount_declare,
       
       tt."PRINCIPAL_INVOICE_C",
       (CASE
         WHEN tt.LEASE_ITEM_CERTI > 0 THEN
          tt.LEASE_ITEM_AMOUNT_C - tt.DOWN_PAYMENT_INVOICE_C
         ELSE
          0
       END) - tt.principal_invoice_c total_uninvoice_declare_c,
       (CASE
         WHEN tt.LEASE_ITEM_CERTI > 0 THEN
          tt.LEASE_ITEM_AMOUNT_C - tt.DOWN_PAYMENT_INVOICE_C
         ELSE
          0
       END) - tt.principal_invoice_c + tt.TOTAL_INVOICE_C TOAL_DECLARE,
       tt."VAT_UNINVOICE_LAST_MONTH",
       tt.VAT_UNINVOICE_LAST_MONTH + (CASE
         WHEN tt.LEASE_ITEM_CERTI > 0 THEN
          tt.LEASE_ITEM_AMOUNT_C - tt.DOWN_PAYMENT_INVOICE_C
         ELSE
          0
       END) - tt.principal_invoice_c VAT_UNINVOICE_C
  FROM (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,
               t1.lease_item_amount,
               
               --设备款
               nvl((SELECT SUM(nvl(cf.due_amount, 0))
                     FROM con_contract_cashflow cf
                    WHERE cf.contract_id = t1.contract_id
                      AND cf.cf_item = 0
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND cf.cf_status = 'RELEASE'),
                   0) lease_item_amount_c,
               
               --当月收到保险费
               nvl((SELECT SUM(nvl(cf.received_amount, 0))
                     FROM con_contract_cashflow cf
                    WHERE cf.contract_id = t1.contract_id
                      AND cf.cf_item = 71
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND cf.cf_status = 'RELEASE'),
                   0) INSURANCE_FEE_C,
               
               --当月收到首付款
               nvl((SELECT SUM(nvl(cf.due_amount, 0))
                     FROM con_contract_cashflow cf
                    WHERE cf.contract_id = t1.contract_id
                      AND cf.cf_item = 2
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND cf.cf_status = 'RELEASE'),
                   0) down_payment_received_c,
               
               --本期设备认证
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acp_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 0),
                   0) lease_item_certi,
               
               --本期保险费扣减
               
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acp_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 71),
                   0) insurance_fee_received_c,
               
               --开票首付
               
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acr_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 2),
                   0) down_payment_invoice_c,
               
               --开票管理费
               
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acr_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 3),
                   0) mgt_fee_invoice_c,
               
               --开票租金
               
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acr_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 1),
                   0) rental_invoice_c,
               
               --开票小计
               
               nvl((SELECT SUM(nvl(al.total_amount, 0))
                     FROM acr_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item IN (1, 2, 3)),
                   0) total_invoice_c,
               
               --设备本金扣除首付后一次性申报
               NULL amount_declare,
               
               --当期开票租金内含本金
               nvl((SELECT SUM(nvl(cf.received_principal, 0))
                     FROM acr_invoice_ln al, con_contract_cashflow cf
                    WHERE al.cashflow_id = cf.cashflow_id
                      AND cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND al.cf_item = 1),
                   0) principal_invoice_c,
               
               --未开票申报小计 = 设备本金扣除首付后一次性申报 - 当期开票租金内含本金
               NULL total_uninvoice_declare_c,
               
               --申报收入合计 = 未开票申报小计 + 开票小计
               
               NULL toal_declare,
               
               --上月未票VAT累计
               nvl((SELECT SUM(nvl(cf.vat_principal, 0) +
                              nvl(cf.vat_interest, 0) -
                              nvl(cf.billing_amount, 0))
                     FROM con_contract_cashflow cf
                    WHERE cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -2) + 1) AND
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) + 0.01
                      AND cf.cf_status = 'RELEASE'),
                   0) vat_uninvoice_last_month,
               
               --本月未票VAT累计
               nvl((SELECT SUM(nvl(cf.vat_principal, 0) +
                              nvl(cf.vat_interest, 0) -
                              nvl(cf.billing_amount, 0))
                     FROM con_contract_cashflow cf
                    WHERE cf.contract_id = t1.contract_id
                      AND trunc(cf.due_date) BETWEEN
                          trunc(add_months(last_day(to_date(t2.period_name,'yyyy-mm')), -1) + 1) AND
                          trunc(last_day(to_date(t2.period_name,'yyyy-mm'))) + 0.01
                      AND cf.cf_status = 'RELEASE'),
                   0) vat_uninvoice_c,
                   t2.period_name 
        
          FROM con_contract t1,(select distinct w.contract_id,w.period_name from csh_write_off w
          where w.period_name = nvl(${@period_name},w.period_name)
          and  w.contract_id = nvl(${@contract_id},w.contract_id)) t2
 WHERE 1 = 1
 and t1.contract_id = t2.contract_id
    and t1.contract_status in ('INCEPT','PENDING')
   AND t1.data_class = 'NORMAL'
 ORDER BY t1.contract_number DESC,t2.period_name) tt
 

]]></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>
