<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
<bm:fields>
<bm:field name="contract_id"/>
<bm:field name="history_contract_id"/>
<bm:field name="contract_number"/>
<bm:field name="change_req_number"/>
<bm:field name="req_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="deal_way"/>
<bm:field name="business_type_n"/>
<bm:field name="before_bp_name"/>
<bm:field name="after_bp_name"/>
<bm:field name="lease_channel"/>
<bm:field name="lease_channel_n"/>
<bm:field name="bp_agent_name"/>
<bm:field name="extra_nam"/>
<bm:field name="journal_num"/>
<bm:field name="journal_date" databaseType="DATE" datatype="java.util.Date"/>
<bm:field name="service_charge_journal_num"/>
<bm:field name="service_charge_journal_date" databaseType="DATE" datatype="java.util.Date"/>
</bm:fields>
<bm:operations>
<bm:operation name="query">
<bm:query-sql><![CDATA[
select *
from (SELECT cc.contract_id,
cc.history_contract_id,
cr.change_req_number,
cr.req_date,
'承租人变更' deal_way,
cc.contract_number,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id =
(select c.bp_id_tenant
from con_contract c
where c.contract_id = cc.history_contract_id)) before_bp_name,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_tenant) after_bp_name,
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id) lease_channel,
(Select ch.description
from hls_lease_channel ch
Where ch.lease_channel =
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) lease_channel_n,
(SELECT c.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id) business_type,
(select bt.description
from hls_business_type bt
where bt.business_type =
(SELECT cc.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) AS business_type_n,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
(SELECT ma.extra_nam
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
(select hj.journal_num
from hls_journal_header hj
where hj.source_table = 'CON_CONTRACT_CHANGE_REQ'
and hj.source_id = cr.change_req_id) journal_num,
(select hj.journal_date
from hls_journal_header hj
where hj.source_table = 'CON_CONTRACT_CHANGE_REQ'
and hj.source_id = cr.change_req_id) journal_date,
(select hj.journal_num
from hls_journal_header hj
where hj.source_table = 'CSH_WRITE_OFF'
and hj.source_id =
(select max(cwo.write_off_id)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'
and cwo.times =
(select max(times)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'))) service_charge_journal_num,
(select hj.journal_date
from hls_journal_header hj
where hj.source_table = 'CSH_WRITE_OFF'
and hj.source_id =
(select max(cwo.write_off_id)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'
and cwo.times =
(select max(times)
from csh_write_off cwo
where cwo.contract_id = cr.contract_id
and cwo.cf_item = 304
and cwo.reversed_flag = 'N'
and cwo.create_je_flag = 'Y'))) service_charge_journal_date
FROM con_contract cc,
con_contract_change_req cr,
con_contract_case_record ccd
WHERE ccd.document_id = cr.change_req_id
and cc.contract_id = cr.change_req_id
and cc.data_class = 'CHANGE_REQ'
and cr.document_type = 'LEASSE_CHAG'
and ccd.document_table = 'CON_CONTRACT_CHANGE_REQ'
and ccd.status in ('APPROVING', 'APPROVED')
UNION ALL
SELECT cc.contract_id,
cc.history_contract_id,
cr.change_req_number,
cr.req_date,
'承租人变更' deal_way,
cc.contract_number,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id =
(select c.bp_id_tenant
from con_contract c
where c.contract_id = cc.history_contract_id)) before_bp_name,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_tenant) after_bp_name,
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id) lease_channel,
(Select ch.description
from hls_lease_channel ch
Where ch.lease_channel =
(SELECT c.lease_channel
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) lease_channel_n,
(SELECT c.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id) business_type,
(select bt.description
from hls_business_type bt
where bt.business_type =
(SELECT cc.business_type
FROM con_contract c
WHERE c.contract_id = cc.contract_id)) AS business_type_n,
(SELECT ma.bp_name
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
(SELECT ma.extra_nam
FROM hls_bp_master ma
WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
null journal_num,
null journal_num,
null service_charge_journal_num,
null service_charge_journal_date
FROM con_contract cc, con_contract_change_req cr
WHERE cc.contract_id = cr.contract_id
and cr.history_flag = 'Y'
and cr.document_type = 'LEASSE_CHAG') t1
#WHERE_CLAUSE#
#ORDER_BY_CLAUSE#
]]></bm:query-sql>
</bm:operation>
</bm:operations>
<bm:query-fields>
<bm:query-field field="change_req_number" queryOperator="like"/>
<bm:query-field field="contract_number" queryOperator="like"/>
<bm:query-field field="extra_nam" queryOperator="like"/>
<bm:query-field field="bp_agent_name" queryOperator="like"/>
<bm:query-field name="req_date_from" queryExpression="t1.req_date >= to_date(${@req_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="req_date_to" queryExpression="t1.req_date <= to_date(${@req_date_to},'yyyy-mm-dd')"/>
</bm:query-fields>
<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:model>