<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: xuls  
    $Date: 2017-1-4 下午03:42:50  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select v.* from(
                select 
                rank() over(order by cr.return_id desc) rk,
		        ha.bp_id,
		        zi.submitted_by, --申请人
		        (select su.description from sys_user su where su.user_id = zi.submitted_by) submitted_by_n,
		        (select eou.DESCRIPTION from sys_user  su,exp_employees ee,exp_employee_assigns ea,exp_org_position_vl eop,exp_org_unit_vl eou
		          where su.user_id = zi.submitted_by and su.employee_id = ee.employee_id and ee.employee_id = ea.employee_id
		          and ea.primary_position_flag = 'Y' 
		          and eop.POSITION_ID = ea.position_id
		          and eop.unit_id = eou.unit_id) unit_name,
		        hm.bp_name, --付款内容
		        ha.bank_account_name, --收款单位名称
		        ha.bank_account_num, --账号
		        ha.bank_branch_name, --开户银行
		        cr.this_return_amount, --所有金额
		        fnd_convert_to_chinese_pkg.amount_convert_to_chinese(cr.this_return_amount) return_amount_big,
		        zi.creation_date, --计划付款日
		        cr.notes, --备注
		        (select zv.approver from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=10 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=10)) approver10,
		        (select zv.comment_text_out from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=10 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=10)) comment_text_out10,
		        (select zv.create_date_fmt from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=10 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=10)) create_date_fmt10,
		        (select zv.approver from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=20 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=20)) approver20,
		        (select zv.comment_text_out from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=20 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=20)) comment_text_out20,
		        (select zv.create_date_fmt from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=20 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=20)) create_date_fmt20,
		        (select zv.approver from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=30 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=30)) approver30,
		        (select zv.comment_text_out from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=30 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=30)) comment_text_out30,
		        (select zv.create_date_fmt from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=30 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=30)) create_date_fmt30,
		        (select zv.approver from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=40 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=40)) approver40,
		        (select zv.comment_text_out from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=40 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=40)) comment_text_out40,
		        (select zv.create_date_fmt from zj_wfl_approve_history_v zv where zv.instance_id=zi.instance_id and zv.node_sequence_num=40 and zv.action_type=1 and zv.create_date_fmt = (select max(z.create_date_fmt) from zj_wfl_approve_history_v z where z.instance_id=zv.instance_id and z.node_sequence_num=40)) create_date_fmt40
		        from hls_bp_master_bank_account ha,
		        hls_bp_master hm,
		        csh_transaction_return cr,
		        zj_wfl_workflow_instance zi 
		        where ha.bp_id=hm.bp_id
		        and hm.bp_id=cr.bp_id
		        and cr.wfl_instance_id=zi.instance_id
		        and zi.status=1
		        and zi.current_seq=50 
		        and ha.enabled_flag='Y'
		        and cr.return_id in (${:@return_id})) v 
		        ORDER BY
                v.rk
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
