con_contract.lwm

<?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
			        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: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 (Select h.bp_id From hls_bp_master h Where h.bp_name = (Select hbm.bp_name From hls_bp_master hbm Where hbm.bp_id = ${@bp_id})) t1 Where t1.bp_id = t.bp_id_tenant) Or Exists
                 (Select 1
                    From (Select h.bp_id
                            From hls_bp_master h
                           Where h.bp_name =
                                 (Select hbm.bp_name
                                    From hls_bp_master hbm
                                   Where hbm.bp_id = ${@bp_id})) t2
                   Where t2.bp_id = t.bp_id_agent_level1) Or Exists
                 (Select 1
                    From con_contract_lease_item ccli, hls_lease_item hli
                   Where ccli.lease_item_id = hli.lease_item_id
                     And hli.vender_id = ${@bp_id}
                     And t.contract_id = ccli.contract_id))"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like '%' || ${@bp_name} || '%'"/>
    </bm:query-fields>
</bm:model>