<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[select * from(
SELECT ccv.project_number,
ccv.owner_user_id user_id,
ccv.contract_id,
ccv.contract_number,
DECODE(cccl.times,
0,
(SELECT m.bp_name
FROM csh_payment_req_ln l, hls_bp_master m
WHERE l.ref_doc_category = 'CONTRACT'
AND l.ref_doc_id = ccv.contract_id
AND m.bp_id = l.bp_id
AND ROWNUM = 1
AND EXISTS
(SELECT *
FROM csh_write_off o, csh_transaction ct
WHERE o.cashflow_id = cccl.cashflow_id
AND cccl.cashflow_id = l.ref_doc_line_id
AND o.csh_transaction_id = ct.transaction_id
AND ct.write_off_flag = 'FULL'
AND ct.transaction_type = 'PAYMENT')),
NULL) bp_name,
ccv.dd_bank_account_name,
cccl.cf_direction_pic,
cccl.cf_item_n,
cccl.times,
nvl(to_char(cccl.due_date,'YYYY-MM-DD'),to_char(ccv.inception_of_lease,'YYYY-MM-DD')) due_date,
-- to_char(cccl.due_date,'YYYY-MM-DD') due_date,
cccl.due_amount,
cccl.principal,
cccl.interest,
to_char(cccl.last_received_date,'YYYY-MM-DD') last_received_date,
cccl.received_amount,
cccl.received_principal,
cccl.received_interest,
cccl.write_off_flag_n,
ccv.id_card_no,
(SELECT s.description
FROM sys_user s
WHERE s.user_id = ccv.owner_user_id
AND s.bp_category = 'AGENT') bp_name_n,
(select cc.overdue_max_days from con_contract_cashflow cc where cc.cashflow_id=cccl.cashflow_id) overdue_days,
(select v.code_value_name value_name
from sys_code_values_v v
where v.code = 'WITHHOLD_WAYS' and v.code_value=ccv.withhold_way) withhold_way_n,
ccv.dd_remark,
ccv.direct_debit_bank_id_n as direct_debit_bank_id,
ccv.dd_bank_account_num,
ccv.dd_bank_branch_name
FROM con_contract_v ccv, con_contract_cashflow_lv cccl
WHERE cccl.contract_id = ccv.contract_id
and ccv.data_class = 'NORMAL'
AND ccv.contract_status not in('SIGN','CLOSED','SIGN_REJECT','NEW')
) t1 #WHERE_CLAUSE#]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:fields>
<bm:field name="project_number"/>
<bm:field name="user_id"/>
<bm:field name="contract_id"/>
<bm:field name="contract_number"/>
<bm:field name="bp_name"/>
<bm:field name="bp_name_n"/>
<bm:field name="dd_bank_account_name"/>
<bm:field name="cf_direction_pic"/>
<bm:field name="cf_item_n"/>
<bm:field name="times"/>
<bm:field name="due_date"/>
<bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="principal" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="interest" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="last_received_date"/>
<bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="received_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="received_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
<bm:field name="write_off_flag_n"/>
<bm:field name="id_card_no"/>
<bm:field name="overdue_days"/>
<bm:field name="withhold_way_n"/>
<bm:field name="dd_remark"/>
<bm:field name="direct_debit_bank_id"/>
<bm:field name="dd_bank_account_num"/>
<bm:field name="dd_bank_branch_name"/>
</bm:fields>
<bm:query-fields>
<bm:query-field name="write_off_flag_n" queryExpression="write_off_flag_n=${@write_off_flag_n}"/>
<bm:query-field name="bp_name" queryExpression="bp_name=${@bp_name}"/>
<bm:query-field name="project_number" queryExpression="upper(project_number) like '%' || upper(${@project_number}) || '%'"/>
<bm:query-field name="contract_number" queryExpression="upper(contract_number) like '%' || upper(${@contract_number}) || '%'"/>
<bm:query-field name="cf_direction_pic" queryExpression="upper(cf_direction_pic) like '%' || upper(${@cf_direction_pic}) || '%'"/>
<bm:query-field name="cf_item_n" queryExpression="cf_item_n=${@cf_item_n}"/>
<bm:query-field name="times" queryExpression="times=${@times}"/>
<bm:query-field name="id_card_no" queryExpression="id_card_no like upper('%'||${@id_card_no}||'%')"/>
<bm:query-field name="last_received_date_from" queryexpression="trunc(to_date(last_received_date,'yyyy-mm-dd')) >= to_date(${@last_received_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="last_received_date_to" queryexpression="trunc(to_date(last_received_date,'yyyy-mm-dd')) <= to_date(${@last_received_date_to},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_from" queryexpression="trunc(to_date(due_date,'yyyy-mm-dd')) >= to_date(${@due_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_to" queryexpression="trunc(to_date(due_date,'yyyy-mm-dd')) <= to_date(${@due_date_to},'yyyy-mm-dd')"/>
</bm:query-fields>
<bm:data-filters>
<bm:data-filter name="owner_user_id" enforceOperations="query" expression=" exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'CONTRACT' and a1.trx_id = t1.contract_id and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) and a1.user_id = a2.owner_user_id and a1.trx_category = a2.trx_category and a2.authorized_user_id = ${/session/@user_id} and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate)))"/>
</bm:data-filters>
<!-- <bm:features>
<f:standard-who/>
<s:bm-script><![CDATA[
var cx = Packages.leaf.javascript.Context.getCurrentContext();
Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
]]></s:bm-script>
</bm:features> -->
</bm:model>