<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: DJ  
    $Date: 2013-9-29 下午3:59:51  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="ZJ_WFL_APPROVE_HISTORY_V">
    <bm:fields>
        <bm:field name="node_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="NODE_ID" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_ID"/>
        <bm:field name="workflow_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WORKFLOW_CODE" prompt="ZJ_WFL_APPROVE_HISTORY_V.WORKFLOW_CODE"/>
        <bm:field name="workflow_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WORKFLOW_DESC" prompt="ZJ_WFL_APPROVE_HISTORY_V.WORKFLOW_DESC"/>
        <bm:field name="node_sequence_num" databaseType="NUMBER" datatype="java.lang.Long" physicalName="NODE_SEQUENCE_NUM" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_SEQUENCE_NUM"/>
        <bm:field name="node_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NODE_DESC" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_DESC"/>
        <bm:field name="instance_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INSTANCE_ID" prompt="ZJ_WFL_APPROVE_HISTORY_V.INSTANCE_ID"/>
        <bm:field name="action_taken" databaseType="NUMBER" datatype="java.lang.Long" physicalName="ACTION_TAKEN" prompt="ZJ_WFL_APPROVE_HISTORY_V.ACTION_TAKEN"/>
        <bm:field name="action_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ACTION_TYPE" prompt="ZJ_WFL_APPROVE_HISTORY_V.ACTION_TYPE"/>
        <bm:field name="action_type_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ACTION_TYPE_DESC" prompt="ZJ_WFL_APPROVE_HISTORY_V.ACTION_TYPE_DESC"/>
        <bm:field name="record_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="RECORD_TYPE" prompt="ZJ_WFL_APPROVE_HISTORY_V.RECORD_TYPE"/>
        <bm:field name="comment_text" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COMMENT_TEXT" prompt="ZJ_WFL_APPROVE_HISTORY_V.COMMENT_TEXT"/>
        <bm:field name="comment_text_out" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COMMENT_TEXT_OUT" prompt="ZJ_WFL_APPROVE_HISTORY_V.COMMENT_TEXT_OUT"/>
        <bm:field name="record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RECORD_ID" prompt="ZJ_WFL_APPROVE_HISTORY_V.RECORD_ID"/>
        <bm:field name="seq_number" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SEQ_NUMBER" prompt="ZJ_WFL_APPROVE_HISTORY_V.SEQ_NUMBER"/>
        <bm:field name="rcpt_record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RCPT_RECORD_ID" prompt="ZJ_WFL_APPROVE_HISTORY_V.RCPT_RECORD_ID"/>
        <bm:field name="disabled_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DISABLED_FLAG" prompt="ZJ_WFL_APPROVE_HISTORY_V.DISABLED_FLAG"/>
        <bm:field name="note" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NOTE" prompt="ZJ_WFL_APPROVE_HISTORY_V.NOTE"/>
        <bm:field name="create_date_fmt" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_DATE_FMT" prompt="ZJ_WFL_APPROVE_HISTORY_V.CREATE_DATE_FMT"/>
        <bm:field name="approver" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="APPROVER" prompt="ZJ_WFL_APPROVE_HISTORY_V.APPROVER"/>
        <bm:field name="record_approve_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RECORD_APPROVE_COUNT" prompt="ZJ_WFL_APPROVE_HISTORY_V.RECORD_APPROVE_COUNT"/>
        <bm:field name="instance_approve_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INSTANCE_APPROVE_COUNT" prompt="ZJ_WFL_APPROVE_HISTORY_V.INSTANCE_APPROVE_COUNT"/>
        <bm:field name="node_hide_approve_record" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NODE_HIDE_APPROVE_RECORD" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_HIDE_APPROVE_RECORD"/>
        <bm:field name="node_show_approve_ht" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NODE_SHOW_APPROVE_HT" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_SHOW_APPROVE_HT"/>
        <bm:field name="node_show_all_approve_ht" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NODE_SHOW_ALL_APPROVE_HT" prompt="ZJ_WFL_APPROVE_HISTORY_V.NODE_SHOW_ALL_APPROVE_HT"/>
        <bm:field name="wfl_show_approve_ht" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WFL_SHOW_APPROVE_HT" prompt="ZJ_WFL_APPROVE_HISTORY_V.WFL_SHOW_APPROVE_HT"/>
        <bm:field name="wfl_show_all_approve_ht" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WFL_SHOW_ALL_APPROVE_HT" prompt="ZJ_WFL_APPROVE_HISTORY_V.WFL_SHOW_ALL_APPROVE_HT"/>
        <bm:field name="attach_count" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ATTACH_COUNT" prompt="ATTACH_COUNT"/>
        <bm:field name="agent_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="AGENT_FLAG" prompt="AGENT_FLAG"/>
    </bm:fields>
    <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=${/session/@user_id})bp_category
                    FROM
                        zj_wfl_approve_history_v t1
                    WHERE
                        t1.instance_id      =${@wfl_con_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=${/session/@user_id})bp_category
                    FROM
                        zj_wfl_approve_history_v t1
                    WHERE
                        t1.instance_id      =${@wfl_con_instance_id} ) v where (v.bp_category = 'AGENT' and v.record_type not like 'TRANSFER')
                    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=${/session/@user_id})bp_category
                    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=${/session/@user_id})bp_category
                    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
                        NULL AS node_id,
                        NULL AS workflow_code,
                        '月结报表工作流' AS workflow_desc,
                        00 AS node_sequence_num,
                        '00--提交节点' AS node_desc,
                        NULL AS instance_id,
                        NULL AS action_taken,
                        NULL AS action_type,
                        '提交--月结审批流程' AS action_type_desc,
                        NULL AS record_type,
                        '提交' 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(pp.creation_date, 'YYYY-MM-DD hh24:mi:ss') AS create_date_fmt,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = pp.created_by
                        ) approver,
                        null AS agent_flag,
                        NULL AS record_approve_count,
                        NULL AS instance_approve_count,
                        null 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
                    FROM
                        HLS_DOCUMENT_WFL_HISTORY pp
                    WHERE
                        pp.instance_id  = ${@instance_id} AND
                        pp.instance_id IS NOT NULL
                    ) t1
                ORDER BY
                    create_date_fmt DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
