<?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
           hlcm_cux_deposit_pkg.check_csh_transaction_status(
			              p_contract_id=>${@contract_id},
			              p_transaction_id=>${@transaction_id},
			              p_user_id=>${/session/@user_id}
            		);
            	end;
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:parameters>
                <bm:parameter name="return_id" dataType="java.lang.Long" input="false" output="true" outputPath="/parameter/@return_id"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                begin

                    hlcm_cux_deposit_pkg.insert_transaction_return(p_transaction_id => ${@transaction_id},
                                                                   p_user_id        => ${/session/@user_id},
                                                                   p_return_id      => ${@return_id});
                end;
                ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
          select *
            from (select v1.contract_id,
                         null transaction_id,
                         '' transaction_num,
                         (select cc.contract_number
                            from con_contract cc
                           where cc.contract_id = v1.contract_id) contract_number,
                         (select cc.contract_name
                            from con_contract cc
                           where cc.contract_id = v1.contract_id) contract_name,
                         --to_char(v1.unwrite_off_amount,'FM999,999,999,990.00')unwrite_off_amount,
                         v1.unwrite_off_amount,
                         v1.deposit_trans_type,
                         (select t.bp_name
                            from hls_bp_master t, con_contract ct
                           where t.bp_id = ct.bp_id_tenant
                             and ct.contract_id = v1.contract_id) bp_id_telnet_n,
                         (select ct.bp_id_agent_level1
                            from con_contract ct
                           where contract_id = v1.contract_id) bp_id_agent_level1,
                         (select t.bp_name
                            from hls_bp_master t, con_contract ct
                           where t.bp_id = ct.bp_id_agent_level1
                             and ct.contract_id = v1.contract_id) bp_name,
                         (select v.code_value_name
                            from sys_code_values_v v
                           where v.code = 'DEPOSIT_TRANS_TYPE'
                             and v.code_value = v1.deposit_trans_type) deposit_trans_type_n,
                         nvl((hlcm_cux_deposit_pkg.get_transaction_status_new(p_contract_id => v1.contract_id,
                                                                              p_user_id     => 1)),
                             '正常') approve_status,
                         v1.bp_id,
                         nvl((SELECT tc.trans_review_status
                               FROM csh_transaction_contemp tc
                              where tc.contemp_id =
                                    (select max(contemp_id)
                                       FROM csh_transaction_contemp tc
                                      where tc.source_contract_id = v1.contract_id
                                        and tc.batch_id is not null)),
                             'NEW') trans_review_status,
                         nvl((SELECT sc.code_value_name
                               FROM csh_transaction_contemp tc, sys_code_values_v sc
                              where tc.contemp_id =
                                    (select max(contemp_id)
                                       FROM csh_transaction_contemp tc
                                      where tc.source_contract_id = v1.contract_id
                                        and tc.batch_id is not null)
                                and sc.code = 'TRANS_REVIEW_STATUS'
                                AND sc.code_value = nvl(tc.trans_review_status, 'NEW')),
                             '新建') trans_review_status_n
                    from (select tt.ref_contract_id contract_id,
                                 tt.deposit_trans_type,
                                 sum((nvl(tt.transaction_amount, 0) -
                                     nvl(tt.write_off_amount, 0) -
                                     nvl(tt.returned_amount, 0))) unwrite_off_amount,
                                 tt.bp_id
                            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
                           group by tt.ref_contract_id, tt.deposit_trans_type, tt.bp_id) v1
                   where v1.unwrite_off_amount > 0
                  union all
                  select null contract_id,
                         tt.transaction_id,
                         tt.transaction_num,
                         '' contract_number,
                         '' contract_name,
                         nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                         nvl(tt.returned_amount, 0) unwrite_off_amount,
                         '' deposit_trans_type,
                         (select hbm.bp_name
                            from hls_bp_master hbm
                           where hbm.bp_id = tt.bp_id) bp_id_telnet_n,
                         (select h.bp_id
                            from hls_bp_master hbm, hls_bp_master h
                           where hbm.unit_id = h.unit_id
                             and hbm.bp_id = tt.bp_id
                             and h.bp_category = 'AGENT') bp_id_agent_level1,
                         (select h.bp_name
                            from hls_bp_master hbm, hls_bp_master h
                           where hbm.unit_id = h.unit_id
                             and hbm.bp_id = tt.bp_id
                             and h.bp_category = 'AGENT') bp_name,
                         '' deposit_trans_type_n,
                         nvl((hlcm_cux_deposit_pkg.get_transaction_status_new(p_contract_id    => Null,
                                                                              p_transaction_id => tt.transaction_id,
                                                                              p_user_id        => 1)),
                             '正常') approve_status,
                         tt.bp_id,
                         nvl((SELECT tc.trans_review_status
                               FROM csh_transaction_contemp tc
                              where tc.contemp_id =
                                    (select max(contemp_id)
                                       FROM csh_transaction_contemp tc
                                      where tc.source_transaction_id =
                                            tt.transaction_id
                                        and tc.batch_id is not null)),
                             'NEW') trans_review_status,
                         nvl((SELECT sc.code_value_name
                               FROM csh_transaction_contemp tc, sys_code_values_v sc
                              where tc.contemp_id =
                                    (select max(contemp_id)
                                       FROM csh_transaction_contemp tc
                                      where tc.source_transaction_id =
                                            tt.transaction_id
                                        and tc.batch_id is not null)
                                and sc.code = 'TRANS_REVIEW_STATUS'
                                AND sc.code_value = nvl(tc.trans_review_status, 'NEW')),
                             '新建') trans_review_status_n
                    from csh_transaction tt
                   where tt.transaction_type = 'DEPOSIT'
                     and tt.ref_contract_id is null
                     and tt.reversed_flag='N'
                     and tt.confirmed_flag not in
                         ('ACCAUDITING', 'NEW', 'CT_REJECTED')
                     and (nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                         nvl(tt.returned_amount, 0) > 0)) v
               #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <!--<bm:data-filters>-->
        <!--<bm:data-filter name="query" expression="(to_number(v.unwrite_off_amount)>0)"/>-->
    <!--</bm:data-filters>-->
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name_n" queryExpression="v.bp_name like &apos;%&apos;||${@bp_name_n}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_telnet_n" queryExpression="v.bp_id_telnet_n =${@bp_id_telnet_n}"/>
        <bm:query-field name="deposit_trans_type" queryExpression="v.deposit_trans_type =${@deposit_trans_type}"/>
        <bm:query-field name="trans_review_status" queryExpression="v.trans_review_status =${@trans_review_status}"/>
    </bm:query-fields>
</bm:model>