<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: 26887cz
    $Date: 2021年8月2日15:40:53
    $Revision: 1.0
    $Purpose: 保证金划转复核
-->
<bm:model xmlns:o="leaf.database.local.oracle" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
      select *
        from (select ct.contemp_id,
       ct.batch_id,
       ct.bp_id,
       ct.source_contract_id,
       ct.contract_id,
	  ct.source_transaction_id,
      ct.transaction_id,
      ct.source_trans_type,
                     (select t.contract_number
                        from con_contract t
       where t.contract_id = ct.contract_id
       and t.data_class = 'NORMAL'
       and rownum = 1) contract_number,
				           (select t.search_term_1 from con_contract t
              where t.contract_id = ct.contract_id
              and t.data_class = 'NORMAL'
              and rownum = 1) vender_contract_number,
      (select p.project_number
       from prj_project_lease_item pi, prj_project p, con_contract_lease_item ci
      where pi.source_project_id = p.project_id
        and pi.project_lease_item_id = ci.project_lease_item_id
        and ci.contract_id=ct.contract_id) vender_project_number,
	  '' transaction_num,
      ct.deposit_trans_type,
       (SELECT sc.code_value_name
          FROM sys_code_values_v sc
         WHERE sc.code = 'DEPOSIT_TRANS_TYPE'
           AND sc.code_value = ct.deposit_trans_type) deposit_trans_type_desc,
       to_char(ct.je_date,'yyyy-MM-dd') je_date,
       ct.deposit_amount,
       ct.deposit_info,
       cc.bp_id_agent_level1,
       (SELECT t.bp_name
          FROM hls_bp_master t
         WHERE t.bp_id = cc.bp_id_agent_level1) bp_id_agent_level1_n,
       cc.bp_id_tenant,
       (SELECT bpv.bp_name
          FROM hls_bp_master bpv
         WHERE bpv.bp_id = cc.bp_id_tenant) bp_name
  from csh_transaction_contemp ct, con_contract cc
 where ct.contract_id = cc.contract_id
   and cc.data_class = 'NORMAL'
                 union all
              select ct.contemp_id,
                     ct.batch_id,
                     ct.bp_id,
                     ct.source_contract_id,
                     ct.contract_id,
                     ct.source_transaction_id,
                     ct.transaction_id,
                     ct.source_trans_type,
                     '' contract_number,
					 '' vender_contract_number,
                     '' vender_project_number,
                     cc.transaction_num,
                     ct.deposit_trans_type,
                     (SELECT sc.code_value_name
                        FROM sys_code_values_v sc
                       WHERE sc.code = 'DEPOSIT_TRANS_TYPE'
                         AND sc.code_value = ct.deposit_trans_type) deposit_trans_type_desc,
                     to_char(ct.je_date, 'yyyy-MM-dd') je_date,
                     ct.deposit_amount,
                     ct.deposit_info,
                     (select u.bp_id
                        from hls_bp_master u, hls_bp_master hbm
                       where u.unit_id = hbm.unit_id
                         and hbm.bp_id = cc.bp_id
                         and u.bp_category = 'AGENT'),
                     (select u.bp_name
                        from hls_bp_master u, hls_bp_master hbm
                       where u.unit_id = hbm.unit_id
                         and hbm.bp_id = cc.bp_id
                         and u.bp_category = 'AGENT'),
                     cc.bp_id bp_id_tenant,
                     (select hbm.bp_name
                        from hls_bp_master hbm
                       where hbm.bp_id = cc.bp_id) bp_name
                from csh_transaction_contemp ct, csh_transaction cc
               where ct.transaction_id = cc.transaction_id) t1
   #WHERE_CLAUSE#
    order by t1.je_date desc
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.batch_id=${@batch_id} "/>
    </bm:data-filters>
</bm:model>
