<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
             SELECT *
  FROM (SELECT *
          FROM (SELECT t1.node_id,
                       t1.workflow_code,
                       t1.workflow_desc,
                       t1.node_sequence_num,
                       t1.node_desc,
                       t1.instance_id,
                       t1.action_taken,
                       t1.action_type,
                       t1.action_type_desc,
                       t1.record_type,
                       t1.comment_text,
                       t1.comment_text_out,
                       t1.record_id,
                       t1.seq_number,
                       t1.rcpt_record_id,
                       t1.disabled_flag,
                       t1.note,
                       t1.create_date_fmt,
                       t1.approver,
                       (cus_zj_wfl_pkg.check_agent_rcpt_record(p_rcpt_record_id => t1.rcpt_record_id)) agent_flag,
                       t1.record_approve_count,
                       t1.instance_approve_count,
                       t1.node_hide_approve_record,
                       t1.node_show_approve_ht,
                       t1.node_show_all_approve_ht,
                       t1.wfl_show_approve_ht,
                       t1.wfl_show_all_approve_ht,
                       (SELECT COUNT(1)
                          FROM fnd_atm_attachment_multi m
                         WHERE m.table_name = 'ZJ_WFL_INSTANCE_NODE_RECIPIENT'
                           AND m.table_pk_value = t1.rcpt_record_id) attach_count,
                       (SELECT u.bp_category
                          FROM sys_user u
                         WHERE u.user_id = ${@user_id}) bp_category,
                       t1.approver_id,
                       t1.node_type,
                       t1.submitted_by
                  FROM zj_wfl_approve_history_v t1
                 WHERE t1.instance_id = ${@instance_id}) v
         WHERE (v.bp_category != 'AGENT')
        UNION ALL
        SELECT *
          FROM (SELECT t1.node_id,
                       t1.workflow_code,
                       t1.workflow_desc,
                       t1.node_sequence_num,
                       t1.node_desc,
                       t1.instance_id,
                       t1.action_taken,
                       t1.action_type,
                       t1.action_type_desc,
                       t1.record_type,
                       t1.comment_text,
                       t1.comment_text_out,
                       t1.record_id,
                       t1.seq_number,
                       t1.rcpt_record_id,
                       t1.disabled_flag,
                       t1.note,
                       t1.create_date_fmt,
                       t1.approver,
                       (cus_zj_wfl_pkg.check_agent_rcpt_record(p_rcpt_record_id => t1.rcpt_record_id)) agent_flag,
                       t1.record_approve_count,
                       t1.instance_approve_count,
                       t1.node_hide_approve_record,
                       t1.node_show_approve_ht,
                       t1.node_show_all_approve_ht,
                       t1.wfl_show_approve_ht,
                       t1.wfl_show_all_approve_ht,
                       (SELECT COUNT(1)
                          FROM fnd_atm_attachment_multi m
                         WHERE m.table_name = 'ZJ_WFL_INSTANCE_NODE_RECIPIENT'
                           AND m.table_pk_value = t1.rcpt_record_id) attach_count,
                       (SELECT u.bp_category
                          FROM sys_user u
                         WHERE u.user_id = ${@user_id}) bp_category,
                       t1.approver_id,
                       t1.node_type,
                       t1.submitted_by
                  FROM zj_wfl_approve_history_v t1
                 WHERE t1.instance_id = ${@instance_id}) v
         WHERE (v.bp_category = 'AGENT' AND
               v.record_type NOT LIKE 'TRANSFER')
        UNION
        SELECT zwn.node_id,
               zz.workflow_code,
               zz.workflow_desc,
               zwn.sequence_num,
               zwn.sequence_num || '-' || zwn.node_desc AS node_desc,
               zw.instance_id,
               NULL AS action_taken,
               NULL AS action_type,
               NULL AS action_type_desc,
               NULL AS record_type,
               NULL AS comment_text,
               NULL AS comment_text_out,
               NULL AS record_id,
               NULL AS seq_number,
               NULL AS rcpt_record_id,
               NULL AS disabled_flag,
               NULL AS note,
               to_char((SELECT zi.creation_date
                         FROM zj_wfl_workflow_instance zi
                        WHERE zi.instance_id = zw.instance_id),
                       'yyyy-mm-dd hh24:mi:ss') AS create_date_fmt,
               NULL AS approver,
               'Y' AS agent_flag,
               1 AS record_approve_count,
               1 AS instance_approve_count,
               'N' AS node_hide_approve_record,
               NULL AS node_show_approve_ht,
               NULL AS node_show_all_approve_ht,
               NULL AS wfl_show_approve_ht,
               NULL AS wfl_show_all_approve_ht,
               NULL AS attach_count,
               NULL AS bp_category,
               NULL AS approver_id,
               zwn.node_type,
               zw.submitted_by
          FROM zj_wfl_workflow_instance zw,
               zj_wfl_workflow_node     zwn,
               zj_wfl_workflow          zz
         WHERE zw.workflow_id = zwn.workflow_id
           AND zz.workflow_id = zw.workflow_id
           AND zw.instance_id = ${@instance_id}
           AND zwn.node_id NOT IN
               (SELECT node_id
                  FROM zj_wfl_approve_history_v
                 WHERE instance_id = zw.instance_id)) t1
 where t1.node_type not in('BRANCH_NODE')
 and
 t1.node_sequence_num >
      
       nvl ((select distinct t.current_seq
          from zj_wfl_instance_node_rcpt_v t
         where t.instance_id = t1.instance_id),90000000000000000000)

 ORDER BY t1.node_sequence_num
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="node_id"/>
        <bm:field name="workflow_code"/>
        <bm:field name="workflow_desc"/>
        <bm:field name="node_sequence_num"/>
        <bm:field name="node_desc"/>
        <bm:field name="instance_id"/>
        <bm:field name="action_taken"/>
        <bm:field name="action_type"/>
        <bm:field name="action_type_desc"/>
        <bm:field name="record_type"/>
        <bm:field name="comment_text"/>
        <bm:field name="comment_text_out"/>
        <bm:field name="record_id"/>
        <bm:field name="seq_number"/>
        <bm:field name="rcpt_record_id"/>
        <bm:field name="disabled_flag"/>
        <bm:field name="note"/>
        <bm:field name="create_date_fmt"/>
        <bm:field name="approver"/>
        <bm:field name="agent_flag"/>
        <bm:field name="record_approve_count"/>
        <bm:field name="instance_approve_count"/>
        <bm:field name="node_hide_approve_record"/>
        <bm:field name="node_show_approve_ht"/>
        <bm:field name="node_show_all_approve_ht"/>
        <bm:field name="wfl_show_approve_ht"/>
        <bm:field name="wfl_show_all_approve_ht"/>
        <bm:field name="attach_count"/>
        <bm:field name="bp_category"/>
        <bm:field name="approver_id"/>
        <bm:field name="submitted_by"/>
    </bm:fields>
</bm:model>
