<?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 &gt;= to_date(${@req_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="req_date_to" queryExpression="t1.req_date &lt;= to_date(${@req_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </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>
