<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT *
            FROM (SELECT c.contract_id,
                         ccc.cashflow_id,
                         c.contract_number,
                         ccc.CF_DIRECTION,
                         ccc.cf_status,
                         c.bank_branch_num bank_account_num,
                         c.bp_id_tenant bp_id,
                         decode(ccc.cf_item,
                                9,
                                (SELECT due_date
                                 FROM con_contract_cashflow
                                 WHERE cf_item = 1
                                   AND times = ccc.times
                                   AND contract_id = ccc.contract_id),
                                due_date)                               due_date_order,
                         to_char(ccc.due_date, 'yyyy-mm-dd')            due_date,
                         c.contract_name,
                         c.bp_id_tenant,
                         c.data_class,
                         ccc.write_off_flag,
                         c.contract_status,
                         BP_ID_AGENT_LEVEL1                             bp_id_agent,
                         (SELECT bp_name
                          FROM hls_bp_master h
                          WHERE c.bp_id_tenant = h.bp_id)               bp_tenant_name,
                         ccc.times,
                         ccc.cf_item,
                         (SELECT description
                          FROM hls_cashflow_item
                          WHERE cf_item = ccc.cf_item)                  cf_item_desc,
                         c.pay_method,
                         (select decode(cf_item, 9,
                                        CON_OVERDUE_PENALTY_PKG.CALC_PENALTY_FOR_WRITE_OFF(p_cashflow_id=> cashflow_id,
                                                                                           p_calc_date=>to_date(${@due_date_to}, 'yyyy-mm-dd'),
                                                                                           p_user_id=>${/session/@user_id},
                                                                                           p_penalty_flag=>'Y'),
                                        due_amount) from con_contract_cashflow where cashflow_id = ccc.cashflow_id) - nvl(ccc.received_amount, 0) due_amount,
                         (select decode(cf_item, 9,
                                        CON_OVERDUE_PENALTY_PKG.CALC_PENALTY_FOR_WRITE_OFF(p_cashflow_id=> cashflow_id,
                                                                                           p_calc_date=>to_date(${@due_date_to}, 'yyyy-mm-dd'),
                                                                                           p_user_id=>${/session/@user_id},
                                                                                           p_penalty_flag=>'Y'),
                                        due_amount) from con_contract_cashflow where cashflow_id = ccc.cashflow_id) - nvl(ccc.received_amount, 0) manual_amount
                         --(ccc.due_amount - nvl(ccc.received_amount, 0)) due_amount,
                         --(ccc.due_amount - nvl(ccc.received_amount, 0)) manual_amount
                  FROM con_contract c,
                       con_contract_cashflow ccc #WHERE_CLAUSE#) t
            where not exists(select 1
                             from hls_ebank_batch_group g,
                                  hls_ebank_batch_ln l
                             where g.status in ('NEW', 'DEAL')
                               and g.group_id = l.group_id
                               and l.cashflow_id = t.cashflow_id) and t.due_amount > 0
            ORDER BY t.due_date_ORDER, t.contract_id, t.times, decode(t.cf_item,1,2,8,3,9,1)
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="pay_method" queryExpression="(c.pay_method = ${@pay_method})"/>
        <bm:query-field name="due_date_from"
                        queryExpression="(decode(ccc.cf_item,
                                9,
                                (SELECT due_date
                                 FROM con_contract_cashflow
                                 WHERE cf_item = 1
                                   AND times = ccc.times
                                   AND contract_id = ccc.contract_id),
                                due_date) &gt;= to_date(${@due_date_from},'yyyy-mm-dd'))"/>
        <bm:query-field name="due_date_to"
                        queryExpression="(decode(ccc.cf_item,
                                9,
                                (SELECT due_date
                                 FROM con_contract_cashflow
                                 WHERE cf_item = 1
                                   AND times = ccc.times
                                   AND contract_id = ccc.contract_id),
                                due_date) &lt;= to_date(${@due_date_to},'yyyy-mm-dd'))"/>
        <bm:query-field name="contract_id" queryExpression="(ccc.contract_id = ${@contract_id})"/>
        <bm:query-field name="cf_item" queryExpression="(ccc.cf_item = ${@cf_item})"/>
        <bm:query-field name="bp_id_tenant" queryExpression="(bp_id_tenant = ${@bp_id_tenant})"/>
        <bm:query-field name="bp_id_agent" queryExpression="(bp_id_agent_level1 = ${@bp_id_agent})"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression="(c.contract_id = ccc.contract_id AND c.data_class = 'NORMAL' AND ccc.write_off_flag != 'FULL' AND c.contract_status IN ('INCEPT') AND ccc.cf_status = 'RELEASE' AND ccc.cf_direction = 'INFLOW' AND c.pay_method IS NOT NULL  and (nvl(c.is_include_pealty, 'Y') = 'Y' and ccc.cf_item in (1, 8, 9) or nvl(c.is_include_pealty, 'Y') = 'N' and ccc.cf_item in (1, 8)))"/>
    </bm:data-filters>
</bm:model>