<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng
    $Date: 2018-12-31 18:17:30
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" defaultOrderBy="v.contract_number desc">
    <bm:operations>

        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select v.*,  v.lease_charge+
       v.lease_mgt_fee-v.before_amout-v.net_amount-v.vat_amount remain_amount from
	    	（ select t1.contract_id,
       t1.contract_number,
       t1.business_type,
       (select t.description from hls_business_type t where t.business_type = t1.business_type) business_type_n,
       t1.bp_id_tenant,
       t2.bp_name bp_id_tenant_n,
       t1.bp_id_agent_level1,
       t3.bp_name bp_id_agent_level1_n,
       t1.HD_USER_COL_V05,
       (select v.code_value_name from sys_code_values_v
       v where v.code = 'YES_NO'
       and v.code_value = t1.HD_USER_COL_V05) HD_USER_COL_V05_n,
       nvl(t1.lease_charge,0) lease_charge,
       nvl(t1.lease_mgt_fee,0) lease_mgt_fee,
       (select sum(ccc.due_amount)
          from con_contract_cashflow ccc
         where ccc.contract_id = t1.contract_id
           and ccc.cf_item = 3
           and ccc.cf_status = 'RELEASE') cf_item_3,
       (select sum(ccc.due_amount)
          from con_contract_cashflow ccc
         where ccc.contract_id = t1.contract_id
           and ccc.cf_item = 301
           and ccc.cf_status = 'RELEASE') cf_item_301,

       --一般手续费取 查询期间之前的已摊销金额，分期手续费取查询期间之前的核销金额（取客户手续费的核销金额加上每个月代理商301的incom表的摊销金额）
       round( nvl(decode(t1.HD_USER_COL_V05,
              'Y',
              ((select sum(cwo.csh_write_off_amount)
                  from csh_write_off cwo
                 where cwo.contract_id = t1.contract_id
                   and cwo.cf_item = 3
                   and cwo.reversed_flag = 'N'
                   and cwo.write_off_date <
                       to_date(${@internal_period_num} || '01', 'yyyymmdd')) +
              (select sum(i.unearned_finance_income *
                           (1 + ds_sg_get_tax_rate(i.calc_end_date,
                                                   t1.business_type)))
                  from con_unearned_finance_income i
                 where i.contract_id = t1.contract_id
                   and i.cf_item = 3
                   and i.internal_period_num < ${@internal_period_num})),
              (select sum(i.unearned_finance_income)
                 from con_unearned_finance_income i
                where i.contract_id = t1.contract_id
                  and i.cf_item = 3
                  and i.internal_period_num < ${@internal_period_num})),0),2) before_amout,
       --    一般的只从月（本月）结表里取每月摊销额，本月核销了分期手续费金额（取客户手续费的核销金额加上每个月代理商的301的incom表的摊销金额）
       round( nvl(decode(t1.HD_USER_COL_V05,
              'Y',
              ((select sum(cwo.csh_write_off_amount /
                           (1 + ds_sg_get_tax_rate(cwo.write_off_date,
                                                   t1.business_type)))
                  from csh_write_off cwo
                 where cwo.contract_id = t1.contract_id
                   and cwo.cf_item = 3
                   and cwo.reversed_flag = 'N'
                   and cwo.write_off_date <
                       to_date(${@internal_period_num} || '01', 'yyyymmdd')) +
              (select sum(i.unearned_finance_income)
                  from con_unearned_finance_income i
                 where i.contract_id = t1.contract_id
                   and i.cf_item = 3
                   and i.internal_period_num = ${@internal_period_num})),
              (select sum(i.unearned_finance_income)
                 from con_unearned_finance_income i
                where i.contract_id = t1.contract_id
                  and i.cf_item = 3
                  and i.internal_period_num = ${@internal_period_num})),0),2) net_amount,
       --税额
       round( nvl(decode(t1.HD_USER_COL_V05,
              'Y',
              ((select sum(cwo.csh_write_off_amount /
                           (1 + ds_sg_get_tax_rate(cwo.write_off_date,
                                                   t1.business_type)) *
                           ds_sg_get_tax_rate(cwo.write_off_date,
                                              t1.business_type))
                  from csh_write_off cwo
                 where cwo.contract_id = t1.contract_id
                   and cwo.cf_item = 3
                   and cwo.reversed_flag = 'N'
                   and cwo.write_off_date <
                       to_date(${@internal_period_num} || '01', 'yyyymmdd')) +
              (select sum(i.unearned_finance_income *
                           ds_sg_get_tax_rate(i.calc_end_date,
                                              t1.business_type))
                  from con_unearned_finance_income i
                 where i.contract_id = t1.contract_id
                   and i.cf_item = 3
                   and i.internal_period_num = ${@internal_period_num})),
              (select sum(i.unearned_finance_income *
                          ds_sg_get_tax_rate(i.calc_end_date,
                                             t1.business_type))
                 from con_unearned_finance_income i
                where i.contract_id = t1.contract_id
                  and i.cf_item = 3
                  and i.internal_period_num = ${@internal_period_num})),0),2) vat_amount
  from con_contract t1, hls_bp_master t2, hls_bp_master t3


#WHERE_CLAUSE#
) v
#ORDER_BY_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="bp_id_tenant_n" queryExpression="t2.bp_name like ${@bp_id_tenant_n}"/>
    <bm:query-field name="bp_id_agent_level1_n" queryExpression="t3.bp_name like ${@bp_id_agent_level1_n}"/>
</bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression="t1.bp_id_tenant = t2.bp_id(+)
   and t1.bp_id_agent_level1 = t3.bp_id(+) and t1.data_class = 'NORMAL'
   and t1.contract_status &lt;&gt; 'CANCEL'
   and t1.lease_execution_flag = 'Y'"/>
    </bm:data-filters>
</bm:model>
