<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features">
    <bm:fields>
        <bm:field name="instance_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INSTANCE_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.INSTANCE_ID"/>
        <bm:field name="approve_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="APPROVE_COUNT" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.APPROVE_COUNT"/>
        <bm:field name="node_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="NODE_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.NODE_ID"/>
        <bm:field name="submitted_by" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SUBMITTED_BY" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.NODE_ID"/>
        <bm:field name="submitted_by_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SUBMITTED_BY_NAME"/>
        <bm:field name="seq_number" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SEQ_NUMBER" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.SEQ_NUMBER"/>
        <bm:field name="user_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="USER_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.USER_ID"/>
        <bm:field name="date_limit" databaseType="DATE" datatype="java.util.Date" physicalName="DATE_LIMIT" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.DATE_LIMIT"/>
        <bm:field name="record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RECORD_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.RECORD_ID"/>
        <bm:field name="commision_by" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMMISION_BY" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.COMMISION_BY"/>
        <bm:field name="commision_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COMMISION_DESC" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.COMMISION_DESC"/>
        <bm:field name="last_notify_date" databaseType="DATE" datatype="java.util.Date" physicalName="LAST_NOTIFY_DATE" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.LAST_NOTIFY_DATE"/>
        <bm:field name="hierarchy_record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="HIERARCHY_RECORD_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.HIERARCHY_RECORD_ID"/>
        <bm:field name="workflow_id" databaseType="NUMBER" datatype="java.lang.Long" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_code" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="document_info" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_info" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="node_desc" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_desc" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="creation_date_format" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="urgent_type"/>
        <bm:field name="position_code"/>
        <bm:field name="node_sequence_num"/>
        <bm:field name="record_type"/>
        <bm:field name="residence_date_format"/>
        <bm:field name="list_type"/>
        <bm:field name="url_link"/>
        <bm:field name="creation_date"/>
        <bm:field name="winid"/>
        <bm:field name="ranks"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    			select t1.* 
    			 from (select s.instance_id,
    		   rank() over(order by  s.creation_date desc) ranks,
               s.approve_count,
               s.submitted_by,
               s.submitted_by_name,
               s.node_id,
               s.node_desc,
               s.hierarchy_record_id,
               s.seq_number,
               s.user_id,
               s.date_limit,
               s.record_id,
               s.commision_by,
               s.commision_desc,
               s.last_notify_date,
               s.workflow_id,
               s.workflow_code,
               s.workflow_desc,
               s.workflow_desc || '-' || s.node_desc as workflow_info,
               s.document_info,
               to_char(s.creation_date, 'yyyy-mm-dd hh24:mi:ss') as creation_date_format,
               CAST(sysdate AS TIMESTAMP) - CAST(s.creation_date AS TIMESTAMP)  || '' as residence_date_format,
               s.urgent_type,
               (select e.position_code from exp_emp_assign_e_v e where e.user_id=s.user_id and e.primary_position_flag='Y' and e.company_id=${/session/@company_id} and rownum=1)position_code,
               (select sequence_num
          from zj_wfl_workflow_node n
         where n.node_id = s.node_id) node_sequence_num,
                       s.record_type,
                'SYS_WFL' LIST_TYPE,
                null url_link,
                s.creation_date,
                null winid
          from zj_wfl_instance_node_rcpt_v s
          where s.user_id=${/session/@user_id}
          union  ALL
          SELECT
          S1.WFL_TO_DO_LIST_ID instance_id,
          1 ranks,
          NULL approve_count,
          S1.SUBMIT_BY,
          (SELECT SU.DESCRIPTION FROM SYS_USER SU WHERE SU.USER_ID=S1.SUBMIT_BY) 
          submitted_by_name,
          NULL node_id,
          NULL node_desc,
          NULL hierarchy_record_id,
          NULL node_desc,
          NULL USER_ID,
          NULL date_limit,
           NULL record_id,
               NULL commision_by,
               NULL commision_desc,
               NULL last_notify_date,
               NULL workflow_id,
               NULL workflow_code,
               NULL workflow_desc,
              s1.list_desc as workflow_info,
               s1.document_info,
               to_char(s1.create_date, 'yyyy-mm-dd hh24:mi:ss') as creation_date_format,
               CAST(sysdate AS TIMESTAMP) - CAST(s1.creation_date AS TIMESTAMP)  || '' as residence_date_format,
               'dai' urgent_type,
               null position_code,
               null node_sequence_num,
               null     record_type,
               'NO_SYS_WFL' LIST_TYPE,
               s1.url url_link,
               s1.creation_date,
               s1.winid
           FROM SDIC_WFL_TO_DO_LIST S1
           where (exists(select 1 from dual where ${/session/@user_id} =s1.user_id)
           or exists(select 1 from dual where ${/session/@role_id} =s1.role_id)
           )
           and s1.DONE_FLAG='N')  t1
				    #WHERE_CLAUSE#
				 order by t1.urgent_type, t1.creation_date desc
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="any_info" queryExpression="(t1.workflow_desc like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.node_desc like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.document_info like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or to_char(t1.creation_date, &apos;yyyy-mm-dd hh24:mi:ss&apos;) like &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.submitted_by_name like &apos;%&apos;|| ${@any_info} || &apos;%&apos; )"/>
        <bm:query-field name="workflow_info" queryExpression="(t1.workflow_desc like  &apos;%&apos;|| ${@workflow_info} || &apos;%&apos; or t1.node_desc like  &apos;%&apos;|| ${@workflow_info} || &apos;%&apos;)"/>
        <bm:query-field name="document_info" queryExpression="t1.document_info like  &apos;%&apos;|| ${@document_info} || &apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
