<?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="t1.contract_number desc">
    <bm:operations>

        <bm:operation name="query">
            <bm:query-sql><![CDATA[

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.vat_rate_of_interest,
       t1.guarantor_info,
       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,

       --核销日期在基准日范围内的利息，直租，回租，联组延期利息都算 然后减掉上月确认的金额 ，加上这月需要确认的金额

      round( nvl((select sum(cwo.write_off_due_amount /
                      (1 + ds_sg_get_tax_rate(cwo.write_off_date,
                                              t1.business_type)))
             from csh_write_off cwo
            where cwo.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item in (102, 103, 104)
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0) +
       nvl((select sum(cwo.write_off_interest /
                      (1 + ds_sg_get_tax_rate(cwo.write_off_date,
                                              t1.business_type)))
             from csh_write_off cwo
            where cwo.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item = 1
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0) - nvl((select sum(i.unearned_finance_income)
                      from con_unearned_finance_income i
                     where i.contract_id = t1.contract_id
                       and i.cf_item = 1
                       and i.internal_period_num = ${@internal_period_num} - 1),
                    0) + nvl((select sum(i2.unearned_finance_income)
                               from con_unearned_finance_income i2
                              where i2.contract_id = t1.contract_id
                                and i2.cf_item = 1
                                and i2.internal_period_num = ${@internal_period_num}),
                             0),2) net_interest,

       round( nvl((select sum(cwo.write_off_due_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.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item in (102, 103, 104)
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0) +
       nvl((select sum(cwo.write_off_interest /
                      (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.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item = 1
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0) - nvl((select sum(i.unearned_finance_income *
                               ds_sg_get_tax_rate(i.calc_start_date,
                                                  t1.business_type))
                      from con_unearned_finance_income i
                     where i.contract_id = t1.contract_id
                       and i.cf_item = 1
                       and i.internal_period_num = ${@internal_period_num} - 1),
                    0) + nvl((select sum(i2.unearned_finance_income *
                                        ds_sg_get_tax_rate(i2.calc_start_date,
                                                           t1.business_type))
                               from con_unearned_finance_income i2
                              where i2.contract_id = t1.contract_id
                                and i2.cf_item = 1
                                and i2.internal_period_num = ${@internal_period_num}),
                             0),2) vat_interest,
      round(  nvl((select sum(cwo.write_off_due_amount /
                      (1 + ds_sg_get_tax_rate(cwo.write_off_date,
                                              t1.business_type)))
             from csh_write_off cwo
            where cwo.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item = 9
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0),2) net_penalty,
     round(   nvl((select sum(cwo.write_off_due_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.reversed_flag = 'N'
              and cwo.contract_id = t1.contract_id
              and cwo.cf_item = 9
              and to_char(cwo.write_off_date, 'yyyymm') = to_char(${@internal_period_num})),
           0),2) vat_penalty,

           nvl((select sum(nvl(d.AMOUNT_CR, 0) - nvl(d.AMOUNT_DR, 0))
             from hls_journal_header   ht,
                  hls_journal_detail_v d,
                  csh_write_off cwo
            where ht.journal_header_id = d.journal_header_id
              and d.SOURCE_DOC_CATEGORY = 'CSH_WRITE_OFF'
              and d.SOURCE_DOC_ID = cwo.write_off_id
              and ht.reversed_flag = 'N'
              and d.account_code = '51990146'
              and ht.internal_period_num = ${@internal_period_num}
                      and cwo.contract_id = t1.contract_id),
           0)
   +
   nvl((select nvl(sum(nvl(d.AMOUNT_CR, 0) - nvl(d.AMOUNT_DR, 0)),0)
  from hls_journal_header   ht,
       hls_journal_detail_v d,
       CON_TRAILER_INFO  h
 where ht.journal_header_id = d.journal_header_id
   and  ht.source_table = 'CON_TRAILER_INFO'
   and ht.source_id = h.con_trailer_id
   and ht.reversed_flag = 'N'
    and d.account_code = '51990146'
and ht.internal_period_num = ${@internal_period_num}
    and h.contract_id = t1.contract_id),0) acc_51990146_amount,
     --
nvl((select sum(nvl(d.AMOUNT_CR, 0) - nvl(d.AMOUNT_DR, 0))
             from hls_journal_header   ht,
                  hls_journal_detail_v d,
                  csh_write_off cwo
            where ht.journal_header_id = d.journal_header_id
              and d.SOURCE_DOC_CATEGORY = 'CSH_WRITE_OFF'
              and d.SOURCE_DOC_ID = cwo.write_off_id
              and ht.reversed_flag = 'N'
              and ht.internal_period_num = ${@internal_period_num}
              and d.account_code = '51990147'
                      and cwo.contract_id = t1.contract_id),
           0)
   +
   nvl((select nvl(sum(nvl(d.AMOUNT_CR, 0) - nvl(d.AMOUNT_DR, 0)),0)
  from hls_journal_header   ht,
       hls_journal_detail_v d,
       CON_TRAILER_INFO  h
 where ht.journal_header_id = d.journal_header_id
   and  ht.source_table = 'CON_TRAILER_INFO'
   and ht.source_id = h.con_trailer_id
   and ht.reversed_flag = 'N'
   and ht.internal_period_num = ${@internal_period_num}
    and d.account_code = '51990147'

    and h.contract_id = t1.contract_id),0) acc_51990147_amount
  from con_contract t1, hls_bp_master t2, hls_bp_master t3


#WHERE_CLAUSE#
#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>
