<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                csh_deposit_transfer_pkg.check_sbo_num(
                        p_document_id =>${@document_id},
                        p_user_id =>${/session/@user_id}
                    );
                end;
             ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select *
        from (select v.source_contract_id,
               null source_transaction_id,
               v.batch_id,
               v.trans_review_status,
               to_char(v.je_date,'yyyy-MM-dd') je_date,
               v.source_trans_type，
               (SELECT sc.code_value_name
                  FROM sys_code_values_v sc
                 WHERE sc.code = 'DEPOSIT_TRANS_TYPE'
                   AND sc.code_value = v.source_trans_type) deposit_trans_type_n,
               (SELECT sc.code_value_name
                  FROM sys_code_values_v sc
                 WHERE sc.code = 'TRANS_REVIEW_STATUS'
                   AND sc.code_value = v.trans_review_status) trans_review_status_n,
               cc.contract_number,
               '' transaction_num,
               cc.contract_name,
               cc.bp_id_agent_level1,
               (SELECT t.bp_name
                  FROM hls_bp_master t
                 WHERE t.bp_id = cc.bp_id_agent_level1) bp_name,
               cc.bp_id_tenant,
               (SELECT bpv.bp_name
                  FROM hls_bp_master bpv
                 WHERE bpv.bp_id = cc.bp_id_tenant) bp_id_telnet_n,

               (select sum((nvl(tt.transaction_amount, 0) -
                           nvl(tt.write_off_amount, 0) -
                           nvl(tt.returned_amount, 0)))
                  from csh_transaction tt
                 where tt.transaction_type = 'DEPOSIT'
                   and tt.ref_contract_id is not null
                   and tt.transaction_amount > 0
                   and tt.deposit_trans_type is not null
                   and tt.deposit_trans_type = v.source_trans_type
                   and tt.ref_contract_id = v.source_contract_id
                   and tt.bp_id = v.bp_id
                 group by tt.ref_contract_id, tt.deposit_trans_type, tt.bp_id) unwrite_off_amount,

               nvl((hlcm_cux_deposit_pkg.get_transaction_status_new(p_contract_id => v.source_contract_id,
                                                                    p_user_id     => 1)),
                   '正常') approve_status

          from con_contract cc,
               (select distinct ct.batch_id,
                ct.source_contract_id,
                ct.bp_id,
                ct.trans_review_status,
                ct.source_trans_type,
                ct.je_date
                from csh_transaction_contemp ct
                where nvl(ct.trans_review_status, 'NEW') <> 'NEW'
                and ct.batch_id is not null
                ) v
         where cc.contract_id = v.source_contract_id
           and cc.data_class = 'NORMAL'
          union all
          select null source_contract_id,
                v.source_transaction_id,
                v.batch_id,
                v.trans_review_status,
                to_char(v.je_date,'yyyy-MM-dd') je_date,
                v.source_trans_type，
                (SELECT sc.code_value_name
                   FROM sys_code_values_v sc
                  WHERE sc.code = 'DEPOSIT_TRANS_TYPE'
                    AND sc.code_value = v.source_trans_type) deposit_trans_type_n,
                (SELECT sc.code_value_name
                   FROM sys_code_values_v sc
                  WHERE sc.code = 'TRANS_REVIEW_STATUS'
                    AND sc.code_value = v.trans_review_status) trans_review_status_n,
                '' contract_number,
                cc.transaction_num,
                '' contract_name,
                (select u.bp_id from hls_bp_master u,hls_bp_master hbm where u.unit_id= hbm.unit_id and u.bp_category='AGENT' and hbm.bp_id=cc.bp_id) bp_id_agent_level1,
               (select u.bp_name from hls_bp_master u,hls_bp_master hbm where u.unit_id= hbm.unit_id and u.bp_category='AGENT' and hbm.bp_id=cc.bp_id) bp_id_agent_level1,
               cc.bp_id bp_id_tenant,
                (SELECT bpv.bp_name
                   FROM hls_bp_master bpv
                  WHERE bpv.bp_id = cc.bp_id) bp_id_telnet_n,
               nvl(cc.transaction_amount, 0) -
                            nvl(cc.write_off_amount, 0) -
                            nvl(cc.returned_amount, 0) unwrite_off_amount,
                nvl((hlcm_cux_deposit_pkg.get_transaction_status_new(p_contract_id => null,
                                                                     p_transaction_id =>cc.transaction_id,
                                                                     p_user_id     => 1)),
                    '正常') approve_status

           from csh_transaction cc,
                (select distinct ct.batch_id,
                 ct.source_transaction_id,
                 ct.bp_id,
                 ct.trans_review_status,
                 ct.source_trans_type,
                 ct.je_date
                 from csh_transaction_contemp ct
                 where nvl(ct.trans_review_status, 'NEW') <> 'NEW'
                 and ct.batch_id is not null
                 ) v
          where cc.transaction_id = v.source_transaction_id
           ) t1

                #WHERE_CLAUSE#
            order by t1.trans_review_status,t1.je_date desc
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name_n" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name_n}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_telnet_n" queryExpression="t1.bp_id_telnet_n =${@bp_id_telnet_n}"/>
        <bm:query-field name="deposit_trans_type" queryExpression="t1.source_trans_type = ${@deposit_trans_type}"/>
        <bm:query-field name="trans_review_status" queryExpression="t1.trans_review_status =${@trans_review_status}"/>
        <bm:query-field name="no_status" queryExpression="t1.trans_review_status != ${@no_status}"/>
    </bm:query-fields>
</bm:model>