<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: luochenglong
    $Date: 2023-02-10 早上 11:03:31
    $Revision: 1.0
    $Purpose: 电子档案池bm
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" >
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    			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 c.code_value_name
                          FROM sys_code_values_v c
                         WHERE c.code = 'POST_STATUS_DESC'
                               AND c.code_value = t1.post_status) post_status_desc,
                       (SELECT DISTINCT zwi.workflow_id
                                                      FROM zj_wfl_workflow_instance zwi,
                                                           zj_wfl_workflow          zww
                                                     WHERE zww.workflow_id = zwi.workflow_id
                                                           AND zwi.instance_id = t1.document_id) workflow_id,
                       (SELECT e.je_check_flag
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND e.workflow_id = (SELECT DISTINCT zwi.workflow_id
                                                      FROM zj_wfl_workflow_instance zwi,
                                                           zj_wfl_workflow          zww
                                                     WHERE zww.workflow_id = zwi.workflow_id
                                                           AND zwi.instance_id = t1.document_id)) je_check_flag,
                       (SELECT e.base_archive_id
                          FROM e_archives_define e
                         WHERE e.base_archive_code = t1.base_archive_code
                               AND e.workflow_id = (SELECT DISTINCT zwi.workflow_id
                                                      FROM zj_wfl_workflow_instance zwi,
                                                           zj_wfl_workflow          zww
                                                     WHERE zww.workflow_id = zwi.workflow_id
                                                           AND zwi.instance_id = t1.document_id)) base_archive_id,
                       nvl( (select 1
                    from gld_period_status s, gld_periods p
                   where s.monthly_closed_flag = 'Y'
                     and  s.company_id = ${/session/@company_id}
                     and s.period_set_code = p.period_set_code
                     and s.period_name = p.period_name
                     and p.adjustment_flag = 'N'
                     and trunc(to_date(t1.internal_period_num,'YYYY-MM')) between
                         p.start_date and p.end_date),0) monthly_closed_flag,
                       (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 = nvl(t1.suppl_trans_flag,'N')) suppl_trans_flag_desc,
                       t1.hly_req_number,
                       t1.suppl_trans_flag,
                       t1.post_message
                  FROM hl_e_archives_pool t1
				  #WHERE_CLAUSE#
    		]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
    		 	begin
   				   elec_wfl_pkg.elec_type_update(
   				   								p_sql_type            =>'INSERT',
                                                p_base_archive_id     => ${@base_archive_id},
                                                p_base_archive_code   => ${@base_archive_code},
                                                p_archive_type        => ${@archive_type},
                                                p_post_stru_data_flag => ${@post_stru_data_flag},
                                                p_stru_data_sql       => ${@stru_data_sql},
                                                p_is_workflow         => ${@is_workflow},
                                                p_workflow_id        => ${@workflow_id},
                                                p_post_atm_flag       => ${@post_atm_flag},
                                                p_atm_sql             => ${@atm_sql},
                                                p_je_check_flag       => ${@je_check_flag},
                                                p_je_source_sql       => ${@je_source_sql},
                                                p_user_id             => ${/session/@user_id});

   			   end;
    		 ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
    		 	begin
   				   elec_wfl_pkg.elec_type_update(
   				   								p_sql_type            => 'UPDATE',
                                                p_base_archive_id     => ${@base_archive_id},
                                                p_base_archive_code   => ${@base_archive_code},
                                                p_archive_type        => ${@archive_type},
                                                p_post_stru_data_flag => ${@post_stru_data_flag},
                                                p_stru_data_sql       => ${@stru_data_sql},
                                                p_is_workflow         => ${@is_workflow},
                                                p_workflow_id         => ${@workflow_id},
                                                p_post_atm_flag       => ${@post_atm_flag},
                                                p_atm_sql             => ${@atm_sql},
                                                p_je_check_flag       => ${@je_check_flag},
                                                p_je_source_sql       => ${@je_source_sql},
                                                p_user_id             => ${/session/@user_id});
   			   end;
    		 ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="delete">
            <bm:update-sql><![CDATA[
    		 	begin
   				   elec_wfl_pkg.elec_type_delete(
                                                p_base_archive_id     => ${@base_archive_id},
                                                p_user_id             => ${/session/@user_id});
   			   end;
    		 ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="internal_period_num_from" queryExpression="to_number(t1.internal_period_num) &gt;= to_number(${@internal_period_num_from})"/>
        <bm:query-field name="internal_period_num_to" queryExpression="to_number(t1.internal_period_num) &lt;= to_number(${@internal_period_num_to})"/>
        <bm:query-field name="archive_type" queryExpression="t1.archive_type =${@archive_type}"/>
        <bm:query-field name="primary_field"  queryExpression="t1.primary_field =${@primary_field}"/>
        <bm:query-field name="original_archive_no"  queryExpression="t1.original_archive_no =${@original_archive_no}"/>
        <bm:query-field name="post_status" queryExpression="t1.post_status =${@post_status}"/>
        <bm:query-field name="hly_req_number" queryExpression="t1.hly_req_number =${@hly_req_number}"/>
        <bm:query-field name="suppl_trans_flag"  queryExpression="t1.suppl_trans_flag =${@suppl_trans_flag}"/>
        <bm:query-field name="document_info"  queryExpression="t1.document_info like ${@document_info}"/>
    </bm:query-fields>
</bm:model>
