<?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,
		(select cc.business_type
                  from con_contract cc
                 where cc.contract_id = v1.contract_id) AS business_type,
            (select hb.description
                  from con_contract cc, hls_business_type_v hb
                 where cc.business_type = hb.business_type
                   and hb.enabled_flag = 'Y'
                   and cc.contract_id = v1.contract_id) AS business_type_desc,
       (select cc.search_term_1 from con_contract cc where cc.contract_id=v1.contract_id) 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=v1.contract_id) vender_project_number,
       --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 t.bp_code
                            from hls_bp_master t, con_contract ct
                           where t.bp_id = ct.bp_id_agent_level1
                             and ct.contract_id = v1.contract_id) agent_bp_code,
       (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<>'vender_deposit'
                   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,
						 '' business_type,
                         '' business_type_desc,
                         '' vender_contract_number,
                         '' vender_project_number,
                         nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                         nvl(tt.returned_amount, 0) unwrite_off_amount,
                       tt.deposit_trans_type  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,
					    (select h.bp_code
                            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') agent_bp_code,
                      (select v.code_value_name
                                   from sys_code_values_v v
                                  where v.code = 'DEPOSIT_TRANS_TYPE'
                                    and v.code_value = tt.deposit_trans_type) 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','ACCAUDITED_RECOI')
				    and not exists(select 1 from  csh_transaction ct where ct.transaction_id =tt.transaction_id and ct.deposit_trans_type in('vender_deposit'))
                     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-field name="vender_contract_number" queryExpression="v.vender_contract_number like &apos;%&apos;||${@vender_contract_number}||&apos;%&apos;"/>
        <bm:query-field name="vender_project_number" queryExpression="v.vender_project_number like &apos;%&apos;||${@vender_project_number}||&apos;%&apos;"/>
        <bm:query-field name="business_type" queryExpression="v.business_type =${@business_type}"/>
    </bm:query-fields>
</bm:model>