<?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" baseTable="CSH_TRANSACTION_CONTEMP">
    <bm:fields>
        <bm:field name="contemp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTEMP_ID"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID"/>
        <bm:field name="batch_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BATCH_ID"/>
        <bm:field name="source_contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_CONTRACT_ID"/>
		<bm:field name="source_transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_TRANSACTION_ID"/>
       <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" forInsert="false" forUpdate="false"/>
        <bm:field name="je_date" databaseType="DATE" datatype="java.util.Date" physicalName="JE_DATE"/>
        <bm:field name="deposit_trans_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DEPOSIT_TRANS_TYPE"/>
        <bm:field name="deposit_trans_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DEPOSIT_TRANS_TYPE_DESC" forInsert="false" forUpdate="false"/>
        <bm:field name="source_trans_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_TRANS_TYPE"/>
        <bm:field name="deposit_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DEPOSIT_AMOUNT"/>
        <bm:field name="deposit_info" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DEPOSIT_INFO"/>
        <bm:field name="bp_id_agent_level1" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_AGENT_LEVEL1" forInsert="false" forUpdate="false"/>
        <bm:field name="bp_id_agent_level1_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_ID_AGENT_LEVEL1_N" forInsert="false" forUpdate="false"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" forInsert="false" forUpdate="false"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME" forInsert="false" forUpdate="false"/>
        <bm:field name="file_num" databaseType="NUMBER" datatype="java.lang.Long" physicalName="FILE_NUM" forInsert="false" forUpdate="false"/>
		<bm:field name="transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TRANSACTION_ID"/>
        <bm:field name="transaction_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_NUM" forInsert="false" forUpdate="false"/>
        <bm:field name="con_search_term_1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="con_search_term_1" forInsert="false" forUpdate="false"/>
        <bm:field name="vender_project_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="vender_project_number" forInsert="false" forUpdate="false"/>
        <bm:field name="agent_bp_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="AGENT_BP_CODE" forInsert="false" forUpdate="false"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
        <o:sequence-pk/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="contemp_id"/>
    </bm:primary-key>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
       select * from (select ct.contemp_id,
       ct.bp_id,
       ct.batch_id,
       ct.source_trans_type,
       ct.source_contract_id,
	  ct.source_transaction_id,
      ct.contract_id,
       (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'
             ) con_search_term_1,
       (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,
	   ct.transaction_id,
       '' 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,
       ct.je_date,
       ct.deposit_amount,
       ct.deposit_info,
       cc.bp_id_agent_level1,
	   (select hbm.bp_code from hls_bp_master hbm where hbm.bp_id=bp_id_agent_level1) agent_bp_code,
       (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,
       (select count(1)
        from fnd_atm_attachment_multi fm
         where fm.table_name = 'CSH_TRANSACTION_CONTEMP'
           and fm.table_pk_value = ct.contemp_id) file_num
  from csh_transaction_contemp ct, con_contract cc
 where ct.contract_id = cc.contract_id
   and ct.trans_review_status = 'NEW'
   and cc.data_class = 'NORMAL'
   union all
   select ct.contemp_id,
          ct.bp_id,
          ct.batch_id,
          ct.source_trans_type,
          ct.source_contract_id,
          ct.source_transaction_id,
          ct.contract_id,
          '' contract_number,
		  '' con_search_term_1,
          '' vender_project_number,
         ct.transaction_id transaction_id,
         (select t.transaction_num
            from csh_transaction t
           where t.transaction_id = ct.transaction_id
              and rownum = 1) 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,
          ct.je_date,
          ct.deposit_amount,
          ct.deposit_info,
          (select h.bp_id
             from hls_bp_master hbm,hls_bp_master h
             where hbm.unit_id=h.unit_id
             and hbm.bp_id=cc.bp_id
             and h.bp_category='AGENT') bp_id_agent_level1,
		  (select h.bp_code
             from hls_bp_master hbm,hls_bp_master h
             where hbm.unit_id=h.unit_id
             and hbm.bp_id=cc.bp_id
             and h.bp_category='AGENT') agent_bp_code,
          (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') bp_id_agent_level1_n,
         cc.bp_id bp_id_tenant,
         (SELECT t.bp_name
              FROM hls_bp_master t
             WHERE t.bp_id = cc.bp_id) bp_name,
          (select count(1)
           from fnd_atm_attachment_multi fm
            where fm.table_name = 'CSH_TRANSACTION_CONTEMP'
              and fm.table_pk_value = ct.contemp_id) file_num
     from csh_transaction_contemp ct,csh_transaction cc
     where ct.transaction_id = cc.transaction_id
    and ct.trans_review_status = 'NEW') t1
       #WHERE_CLAUSE#
    order by t1.contemp_id
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="(t1.source_contract_id is null or(t1.source_contract_id is not null and t1.source_contract_id=${@source_contract_id}))"/>
        <bm:data-filter enforceOperations="query" expression="t1.bp_id=${@bp_id} "/>
    </bm:data-filters>
</bm:model>
