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,
				t1.division,
                t1.business_type,
                (Select bt.description
                From hls_business_type bt
                Where bt.business_type = t1.business_type) As business_type_n,
                t1.search_term_1
			        from con_contract t1
			        where
			        t1.contract_status in ('INCEPT','NEW','ETING','REPURING')
			        and t1.data_class = 'NORMAL'
				and t1.document_type<>'PUR_ORDER'
			        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:field name="business_type_n"/>
        <bm:field name="search_term_1"/>
    </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','RENTER','GUTA_ORG')
                   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 in('TENANT','RENTER')
                  ))"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like '%' || ${@bp_name} || '%'"/>
        <bm:query-field field="search_term_1" queryOperator="like"/>
        <bm:query-field field="business_type_n" queryOperator="like"/>
    </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>