<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: luochenglong
    $Date: 2023-02-10  下午3:03:31
    $Revision: 1.0
    $Purpose:电子档案池bm
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    			select * from (SELECT t1.pool_id,
    			       t1.primary_field,
                       t1.internal_period_num,
                       t1.base_archive_code,
                       t1.archive_type,
                       t1.document_id,
                       t1.document_number,
                       t1.document_info,
                       t1.post_status,
                       t1.e_archives_id,
                       (SELECT  hl.workflow_id  FROM hl_e_archives_post_list hl
                                                     WHERE  hl.e_archives_id = t1.e_archives_id) workflow_id,
                       (SELECT e.je_check_flag
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND nvl(e.workflow_id,'-1') = nvl((SELECT  hl.workflow_id
                                                      FROM hl_e_archives_post_list hl
                                                     WHERE hl.e_archives_id = t1.e_archives_id),'-1')
                                                     and nvl(e.func_type,'-1')=nvl((select hl.func_type from hl_e_archives_post_list hl where hl.e_archives_id=t1.e_archives_id),'-1')) je_check_flag,
                        (SELECT e.post_stru_data_flag
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND nvl(e.workflow_id,'-1') = nvl((SELECT  hl.workflow_id
                                                      FROM hl_e_archives_post_list hl
                                                     WHERE hl.e_archives_id = t1.e_archives_id),'-1')
                                                     and nvl(e.func_type,'-1')=nvl((select hl.func_type from hl_e_archives_post_list hl where hl.e_archives_id=t1.e_archives_id),'-1')) post_stru_data_flag,
                         (SELECT e.post_atm_flag
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND nvl(e.workflow_id,'-1') = nvl((SELECT  hl.workflow_id
                                                      FROM hl_e_archives_post_list hl
                                                     WHERE hl.e_archives_id = t1.e_archives_id),'-1')
                                                     and nvl(e.func_type,'-1')=nvl((select hl.func_type from hl_e_archives_post_list hl where hl.e_archives_id=t1.e_archives_id),'-1')) post_atm_flag,
                       (SELECT e.base_archive_id
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND nvl(e.workflow_id,'-1') = nvl((SELECT  hl.workflow_id
                                                      FROM hl_e_archives_post_list hl
                                                     WHERE hl.e_archives_id = t1.e_archives_id),'-1')
                                                     and nvl(e.func_type,'-1')=nvl((select hl.func_type from hl_e_archives_post_list hl where hl.e_archives_id=t1.e_archives_id),'-1')) base_archive_id,
                       (SELECT c.code_value_name
                          FROM sys_code_values_v c
                         WHERE c.code = 'DATA_CLASSIFICATION'
                               AND c.code_value = t1.archive_type) archive_type_desc,
                       (SELECT c.code_value_name
                          FROM sys_code_values_v c
                         WHERE c.code = 'YES_NO'
                               AND c.code_value = 'Y') suppl_trans_flag,
                       t1.hly_req_number ,
                       t1.post_message
                  FROM hl_e_archives_pool t1
                  where to_number(t1.internal_period_num) >=
                          nvl(to_number(${@period_num_from}),
                              to_number(t1.internal_period_num))
                         AND to_number(t1.internal_period_num) <=
                          nvl(to_number(${@period_num_to}),
                              to_number(t1.internal_period_num))
                          AND t1.archive_type =
                             nvl(${@archive_type}, t1.archive_type)
                     AND t1.post_status <>'POST_SUCCESS'
				    AND t1.pool_id IN
                               (SELECT tmp.document_id FROM hls_archive_pool_tmp tmp WHERE tmp.session_id = ${@session_id}))
				     #WHERE_CLAUSE#
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="e_archives_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="E_ARCHIVES_ID" />
        <bm:field name="workflow_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="WORKFLOW_ID" />
        <bm:field name="je_check_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="JE_CHECK_FLAG"/>
        <bm:field name="base_archive_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BASE_ARCHIVE_ID" />
        <bm:field name="pool_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="POOL_ID" />
        <bm:field name="primary_field" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PRIMARY_ID" />
        <bm:field name="internal_period_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INTERNAL_PERIOD_NUM"/>
        <bm:field name="base_archive_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BASE_ARCHIVE_CODE" />
        <bm:field name="archive_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ARCHIVE_TYPE"/>
        <bm:field name="document_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DOCUMENT_ID"/>
        <bm:field name="document_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DOCUMENT_NUMBER"/>
        <bm:field name="post_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DOCUMENT_NUMBER"/>
        <bm:field name="document_info" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="POST_STATUS"/>
        <bm:field name="post_message" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="POST_MESSAGE"/>
    </bm:fields>
</bm:model>
