<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: ZhuXianfei
    $Date: 2018-12-17 10:22
    $Revision: 1.0 
    
-->

<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql>
                <![CDATA[
                   Select t1.*
                      From (Select cc.contract_id,
                                   ccc.cashflow_id,
                                   cc.contract_number,
                                   cc.contract_status,
                                   (Select scv.code_value_name
                                      From sys_code_values_v scv
                                     Where scv.code = 'CON500_CONTRACT_STATUS'
                                       And scv.code_value = cc.contract_status) contract_status_n,
                                   ccc.times,
                                   to_char(cc.lease_execution_date, 'yyyy-mm-dd') lease_execution_date,
                                   cc.business_type,
                                   (Select t.description
                                      From hls_business_type t
                                     Where t.business_type = cc.business_type) business_type_n,
                                   cc.bp_id_tenant,
                                   (Select bp.bp_name
                                      From hls_bp_master bp
                                     Where bp.bp_id = cc.bp_id_tenant) bp_name,
                                   cc.bp_id_agent_level1,
                                   (Select bp.bp_name
                                      From hls_bp_master bp
                                     Where bp.bp_id = cc.bp_id_agent_level1) agent_name,
                                   ccc.due_amount,
                                   to_char(ccc.due_date, 'yyyy-mm-dd') due_date,
                                   to_char((Select Max(cwo.write_off_date)
                                             From csh_write_off cwo
                                            Where cwo.cashflow_id = ccc.cashflow_id
                                              And cwo.reversed_flag = 'N'
                                              And cwo.write_off_date <=
                                                  trunc(to_date(${@base_date}, 'yyyy-mm-dd'))),
                                           'yyyy-mm-dd') write_off_date,
                                   (Select decode(cc.business_type,
                                                  '01',
                                                  nvl(Sum(cwo.csh_write_off_amount) * 0.15, 0),
                                                  nvl(Sum(cwo.csh_write_off_amount), 0))
                                      From csh_write_off cwo
                                     Where cwo.cashflow_id = ccc.cashflow_id
                                       And cwo.reversed_flag = 'N'
                                       And cwo.write_off_date <=
                                           trunc(to_date(${@base_date}, 'yyyy-mm-dd'))
                                       And cwo.write_off_date >= ccc.due_date) received_amount,

                                   (Case
                                     When trunc(ccc.due_date) <
                                          to_date('2018-05-01', 'yyyy-mm-dd') Then
                                      (Select decode(cc.business_type,
                                                     '01',
                                                     (nvl(Sum(cwo.csh_write_off_amount), 0) -
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.17 * 0.17,
                                                            2)) * 0.15,
                                                     nvl(Sum(cwo.csh_write_off_amount), 0) -
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.17 * 0.17,
                                                           2))
                                         From csh_write_off cwo
                                        Where cwo.cashflow_id = ccc.cashflow_id
                                          And cwo.reversed_flag = 'N'
                                          And cwo.write_off_date <=
                                              trunc(to_date(${@base_date}, 'yyyy-mm-dd'))
                                          And cwo.write_off_date >= ccc.due_date)
                                     Else
                                      (Select decode(cc.business_type,
                                                     '01',
                                                     (nvl(Sum(cwo.csh_write_off_amount), 0) -
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.16 * 0.16,
                                                            2)) * 0.15,
                                                     nvl(Sum(cwo.csh_write_off_amount), 0) -
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.16 * 0.16,
                                                           2))
                                         From csh_write_off cwo
                                        Where cwo.cashflow_id = ccc.cashflow_id
                                          And cwo.reversed_flag = 'N'
                                          And cwo.write_off_date <=
                                              trunc(to_date(${@base_date}, 'yyyy-mm-dd'))
                                          And cwo.write_off_date >= ccc.due_date)
                                   End) net_due_amount,
                                   (Case
                                     When trunc(ccc.due_date) <
                                          to_date('2018-05-01', 'yyyy-mm-dd') Then
                                      (Select decode(cc.business_type,
                                                     '01',
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.17 * 0.17 * 0.15,
                                                           2),
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.17 * 0.17,
                                                           2))
                                         From csh_write_off cwo
                                        Where cwo.cashflow_id = ccc.cashflow_id
                                          And cwo.reversed_flag = 'N'
                                          And cwo.write_off_date <=
                                              trunc(to_date(${@base_date}, 'yyyy-mm-dd'))
                                          And cwo.write_off_date >= ccc.due_date)
                                     Else
                                      (Select decode(cc.business_type,
                                                     '01',
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.16 * 0.16 * 0.15,
                                                           2),
                                                     round(nvl(Sum(cwo.csh_write_off_amount), 0) / 1.16 * 0.16,
                                                           2))
                                         From csh_write_off cwo
                                        Where cwo.cashflow_id = ccc.cashflow_id
                                          And cwo.reversed_flag = 'N'
                                          And cwo.write_off_date <=
                                              trunc(to_date(${@base_date}, 'yyyy-mm-dd'))
                                          And cwo.write_off_date >= ccc.due_date)
                                   End

                                   ) vat_due_amount,
                                   ds_common_get_data_pkg.get_over_days(p_cashflow_id => ccc.cashflow_id,
                                                                        p_due_date    => ccc.due_date,
                                                                        p_base_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd')) overdue_days,
                                   ds_common_get_data_pkg.get_overdue_amount(p_cashflow_id => ccc.cashflow_id,
                                                                             p_contract_id => cc.contract_id,
                                                                             p_due_date    => ccc.due_date,
                                                                             p_base_date   => to_date(${@base_date},
                                                                                                      'yyyy-mm-dd')) overdue_amount,
                                   ds_common_get_data_pkg.get_net_overdue_amount(p_cashflow_id => ccc.cashflow_id,
                                                                                 p_contract_id => cc.contract_id,
                                                                                 p_due_date    => ccc.due_date,
                                                                                 p_base_date   => to_date(${@base_date},
                                                                                                          'yyyy-mm-dd')) net_overdue_amount,
                                   ds_common_get_data_pkg.get_vat_overdue_amount(p_cashflow_id => ccc.cashflow_id,
                                                                                 p_contract_id => cc.contract_id,
                                                                                 p_due_date    => ccc.due_date,
                                                                                 p_base_date   => to_date(${@base_date},
                                                                                                          'yyyy-mm-dd')) vat_overdue_amount
                              From con_contract_cashflow ccc, con_contract cc
                             Where ccc.contract_id = cc.contract_id
                               And ccc.cf_direction = 'INFLOW'
                               And ccc.cf_status = 'RELEASE'
                               And cc.data_class = 'NORMAL'
                               And cc.hd_user_col_v05 = 'Y'
                               And ccc.due_amount > 0
                               And ccc.times > 0
                               And ccc.cf_item = 3
                               And cc.contract_status Not In ('NEW', 'SIGN')) t1



                #WHERE_CLAUSE#

                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="lease_execution_date"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="agent_name"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="due_date"/>
        <bm:field name="write_off_date"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="net_due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vat_due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="overdue_days" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="overdue_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="net_overdue_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vat_overdue_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field name="lease_execution_date_from"
                        queryExpression="t1.lease_execution_date &gt;=   ${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to"
                        queryExpression="t1.lease_execution_date &lt;= ${@lease_execution_date_to}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="due_date_from"
                        queryExpression="t1.due_date &gt;=   ${@due_date_from}"/>
        <bm:query-field name="due_date_to"
                        queryExpression="t1.due_date &lt;= ${@due_date_to}"/>

        <bm:query-field name="overdue_days_from"
                        queryExpression="t1.overdue_days &gt;=   ${@overdue_days_from}"/>
        <bm:query-field name="overdue_days_to"
                        queryExpression="t1.overdue_days &lt;= ${@overdue_days_to}"/>
    </bm:query-fields>
</bm:model>
