<?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
                    pp.project_id,
                    pp.virtual_con_number,
                    pp.virtual_con_name,
                    pp.bp_id_tenant,
                    hbm.bp_name,
                    pp.finance_amount,
                    NVL((select SUM(NVL(CCC.RECEIVED_PRINCIPAL,0)) from con_contract cc, con_contract_cashflow ccc where 
                    cc.contract_id=ccc.contract_id
                    and cc.data_class='NORMAL'
                    AND CC.PROJECT_ID=PP.PROJECT_ID
                    AND CCC.CF_ITEM=1
                    AND CC.CONTRACT_STATUS<>'CANCEL'),0) RECEIVED_PRINCIPAL_TOTAL,
                    (SELECT
                        NVL(SUM(NVL(o.write_off_due_amount, 0)), 0)
                    FROM
                        csh_write_off o
                    WHERE
                        o.write_off_type = 'PAYMENT_DEBT' AND
                        o.reversed_flag  = 'N' AND
                        o.contract_id   IN
                        (SELECT
                            cc.contract_id
                        FROM
                            con_contract cc
                        WHERE
                            cc.data_class = 'NORMAL' AND
                            cc.project_id = pp.project_id
                        ) AND
                        o.cf_item = 0
                    ) payment_amount_sum,
                    (SELECT
                        NVL(SUM(o.write_off_due_amount), 0)
                    FROM
                        csh_write_off o
                    WHERE
                        o.reversed_flag = 'N' AND
                        o.contract_id  IN
                        (SELECT
                            cc.contract_id
                        FROM
                            con_contract cc
                        WHERE
                            cc.data_class = 'NORMAL' AND
                            cc.project_id = pp.project_id
                        ) AND
                        o.cf_item = 1
                    ) repaymrnt_amount_sum,
                    (SELECT
                        NVL(SUM(ccc.billing_amount), 0)
                    FROM
                        con_contract_cashflow ccc
                    WHERE
                        ccc.cf_item         = 0 AND
                        ccc.cf_direction    = 'OUTFLOW' AND
                        ccc.billing_status <> 'NOT' AND
                        ccc.contract_id    IN
                        (SELECT
                            cc.contract_id
                        FROM
                            con_contract cc
                        WHERE
                            cc.data_class = 'NORMAL' AND
                            cc.project_id = pp.project_id
                        )
                    ) billing_amount_sum
                FROM
                    prj_project pp,
                    hls_bp_master hbm
                WHERE
                    pp.data_class     = 'VIRTUAL_CON' AND
                    pp.project_status = 'APPROVED' AND
                    pp.bp_id_tenant   = hbm.bp_id
					and pp.division not in('01')
                    ) t1 #WHERE_CLAUSE#
					order by t1.virtual_con_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
        <bm:query-field name="finance_amount_from" queryExpression="t1.finance_amount &gt;=nvl(${@finance_amount_from},0)"/>
        <bm:query-field name="finance_amount_to" queryExpression="t1.finance_amount &lt;=nvl(${@finance_amount_to},99999999999999999)"/>
        <bm:query-field name="payment_amount_sum_from" queryExpression="t1.payment_amount_sum &gt;=nvl(${@payment_amount_sum_from},0)"/>
        <bm:query-field name="payment_amount_sum_to" queryExpression="t1.payment_amount_sum &lt;=nvl(${@payment_amount_sum_to},99999999999999999)"/>
        <bm:query-field name="repayment_amount_sum_from" queryExpression="t1.repayment_amount_sum &gt;=nvl(${@repayment_amount_sum_from},0)"/>
        <bm:query-field name="repayment_amount_sum_to" queryExpression="t1.repayment_amount_sum &lt;=nvl(${@repayment_amount_sum_to},99999999999999999)"/>
        <bm:query-field name="billing_amount_sum_from" queryExpression="t1.billing_amount_sum &gt;=nvl(${@billing_amount_sum_from},0)"/>
        <bm:query-field name="billing_amount_sum_to" queryExpression="t1.billing_amount_sum &lt;=nvl(${@billing_amount_sum_to},99999999999999999)"/>
    </bm:query-fields>
</bm:model>
