<?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  t1.contract_id,
                t1.dd_bank_account_num,
                t1.dd_bank_account_name,
                t1.contract_number,
                t1.contract_name,
                t1.bp_id_tenant,
                t1.bp_id_agent_level1,
                (select bp_name from hls_bp_master where bp_id = t1.bp_id_tenant) bp_name,
                (SELECT sc.code_value_name
                FROM sys_code_values_v sc
                WHERE sc.code = 'CON500_CONTRACT_STATUS'
                AND sc.code_value = t1.contract_status) contract_status_desc,
                t1.payment_deduction,t1.division
                from con_contract t1
                where
                t1.contract_status in ('INCEPT','NEW','ETING','REPURING')
                and t1.data_class = 'NORMAL'
                and t1.company_id  in ((
                select company_id
                from fnd_companies t
                where t.company_id = ${/session/@company_id} or
                t.parent_company_id = ${/session/@company_id}
                union
                select t.parent_company_id company_id
                from fnd_companies t
                where t.parent_company_id is not null
                and (t.company_id = ${/session/@company_id} or t.parent_company_id = ${/session/@company_id})))
                ) t
                #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="contract_number"/>
        <bm:field name="contract_name"/>
        <bm:field name="bp_name"/>
        <bm:field name="contract_status_desc"/>
        <bm:field name="dd_bank_account_num"/>
        <bm:field name="dd_bank_account_name"/>
        <bm:field name="payment_deduction"/>
        <bm:field name="division"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_id" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field name="due_date_from"
                        queryExpression="due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to"
                        queryExpression="due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field field="dd_bank_account_num" queryOperator="="/>
        <bm:query-field field="dd_bank_account_name" queryOperator="="/>
        <!--<bm:query-field name="bp_id"
                        queryExpression="(t.bp_id_tenant = ${@bp_id} or t.bp_id_agent_level1 = ${@bp_id} or ((select bp_category from hls_bp_master where bp_id = ${@bp_id}) = 'VENDER'))"/>-->
        <bm:query-field name="bp_id" queryExpression="(Exists (Select 1
                  From hls_bp_master h, hls_bp_master_role hbr,con_contract_bp bp
                 Where h.bp_name in ( select hbm.bp_name from hls_bp_master hbm where hbm.bp_id= ${@bp_id})
                   and hbr.bp_id = h.bp_id
				   and h.bp_id= bp.bp_id
                   and hbr.bp_type in('TENANT','GUTA_NP')
                   and bp.contract_id=t.contract_id) Or not Exists
        (Select 1
                  From hls_bp_master h, hls_bp_master_role hbr
                 Where h.bp_id = ${@bp_id}
                   and hbr.bp_id = h.bp_id
                   and hbr.bp_type = 'TENANT'
                  ))"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like '%' || ${@bp_name} || '%'"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" Expression=" exists
                (select 1
                from con_contract_cashflow ccc
                where ccc.contract_id = t.contract_id
                and ccc.cf_status = 'RELEASE'
                and ccc.write_off_flag != 'FULL'
                and ccc.cf_direction = 'INFLOW'
                and ccc.cf_item not in (251)
                and ((nvl(${@deposit_flag}, 'N') = 'Y' and cf_item in (1, 8, 9, 200, 11, 250)) or
                (nvl(${@deposit_flag}, 'N') = 'N'))
                and  ccc.due_amount - nvl(ccc.received_amount, 0)>0
                )"/>
    </bm:data-filters>
</bm:model>
