<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: trd
    $Date: 2022-12-6 下午2:36:27
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
       <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
              select * from (
              select t1.journal_header_id,
                     t1.journal_submit_batch_id,
                     t1.journal_num,
                     t1.error_message,
                     t1.total_amount_dr,
                     t1.total_amount_cr,
                     (select c.company_short_name
                        from fnd_companies_vl c
                       where c.company_id = t1.je_company_id) js_company_short_name,
                     t1.period_name,
                     to_char(t1.journal_date,'yyyy-mm-dd') journal_date,
                     t1.currency_code,
                     (SELECT gc.currency_name
                        FROM gld_currency_v gc
                       WHERE gc.currency_code = t1.currency_code) currency_desc,
                     t1.total_amount_fuc_dr,
                     (SELECT d.je_transaction_desc
                        FROM gld_je_transaction d
                       WHERE d.je_transaction_code = t1.je_transaction_code) je_transaction_desc,
                     t1.description,
                     (SELECT COUNT(1)
                        FROM hls_journal_detail_imp t
                       WHERE t.journal_header_id = t1.journal_header_id) je_detail_count,
                     t1.wfl_status,
                     (select v.code_value_name value_name
                        from sys_code_values_v v
                       where v.code = 'HLS536_WFL_STATUS'
                         and v.code_value = t1.wfl_status) wfl_status_n,
                     t1.reversed_flag,
                     t1.reverse_wfl_status,
                     (select v.code_value_name value_name
                        from sys_code_values_v v
                       where v.code = 'HLS536_WFL_STATUS_REVERSE'
                         and v.code_value = t1.reverse_wfl_status) reverse_wfl_status_n,
                     t1.journal_type,
                     (select t.post_gl_status
                        from hls_journal_header t
                       where t.journal_header_imp_id = t1.journal_header_id
                       ) post_gl_status
                FROM hls_journal_header_imp t1
                where
                 EXISTS (SELECT 1
                        FROM exp_emp_assign_e_v t
                       WHERE t.user_id =  ${/session/@user_id}
                         AND t.position_code IN ('014'))
                and t1.journal_type in('COST_IMPORT','GLOBAL_TEMPLATE')
              union
              select t1.journal_header_id,
                     t1.journal_submit_batch_id,
                     t1.journal_num,
                     t1.error_message,
                     t1.total_amount_dr,
                     t1.total_amount_cr,
                     (select c.company_short_name
                        from fnd_companies_vl c
                       where c.company_id = t1.je_company_id) js_company_short_name,
                     t1.period_name,
                     to_char(t1.journal_date,'yyyy-mm-dd') journal_date,
                     t1.currency_code,
                     (SELECT gc.currency_name
                        FROM gld_currency_v gc
                       WHERE gc.currency_code = t1.currency_code) currency_desc,
                     t1.total_amount_fuc_dr,
                     (SELECT d.je_transaction_desc
                        FROM gld_je_transaction d
                       WHERE d.je_transaction_code = t1.je_transaction_code) je_transaction_desc,
                     t1.description,
                     (SELECT COUNT(1)
                        FROM hls_journal_detail_imp t
                       WHERE t.journal_header_id = t1.journal_header_id) je_detail_count,
                     t1.wfl_status,
                     (select v.code_value_name value_name
                        from sys_code_values_v v
                       where v.code = 'HLS536_WFL_STATUS'
                         and v.code_value = t1.wfl_status) wfl_status_n,
                     t1.reversed_flag,
                     t1.reverse_wfl_status,
                     (select v.code_value_name value_name
                        from sys_code_values_v v
                       where v.code = 'HLS536_WFL_STATUS_REVERSE'
                         and v.code_value = t1.reverse_wfl_status) reverse_wfl_status_n,
                     t1.journal_type,
                     (select t.post_gl_status
                        from hls_journal_header t
                       where t.journal_header_imp_id = t1.journal_header_id
                       ) post_gl_status
                FROM hls_journal_header_imp t1
                where
                 EXISTS (SELECT 1
                        FROM exp_emp_assign_e_v t
                       WHERE t.user_id =  ${/session/@user_id}
                         AND t.position_code IN ('012'))
                and t1.journal_type in('FUND_POOL','FINANCIAL_TEMPLATE')
                union
                select t1.journal_header_id,
                       t1.journal_submit_batch_id,
                                     t1.journal_num,
                                     t1.error_message,
                                     t1.total_amount_dr,
                                     t1.total_amount_cr,
                                     (select c.company_short_name
                                        from fnd_companies_vl c
                                       where c.company_id = t1.je_company_id) js_company_short_name,
                                     t1.period_name,
                                     to_char(t1.journal_date,'yyyy-mm-dd') journal_date,
                                     t1.currency_code,
                                     (SELECT gc.currency_name
                                        FROM gld_currency_v gc
                                       WHERE gc.currency_code = t1.currency_code) currency_desc,
                                     t1.total_amount_fuc_dr,
                                     (SELECT d.je_transaction_desc
                                        FROM gld_je_transaction d
                                       WHERE d.je_transaction_code = t1.je_transaction_code) je_transaction_desc,
                                     t1.description,
                                     (SELECT COUNT(1)
                                        FROM hls_journal_detail_imp t
                                       WHERE t.journal_header_id = t1.journal_header_id) je_detail_count,
                                     t1.wfl_status,
                                     (select v.code_value_name value_name
                                        from sys_code_values_v v
                                       where v.code = 'HLS536_WFL_STATUS'
                                         and v.code_value = t1.wfl_status) wfl_status_n,
                                     t1.reversed_flag,
                                     t1.reverse_wfl_status,
                                     (select v.code_value_name value_name
                                        from sys_code_values_v v
                                       where v.code = 'HLS536_WFL_STATUS_REVERSE'
                                         and v.code_value = t1.reverse_wfl_status) reverse_wfl_status_n,
                                     t1.journal_type,
                                     (select t.post_gl_status
                                        from hls_journal_header t
                                       where t.journal_header_imp_id = t1.journal_header_id
                                       ) post_gl_status
                                FROM hls_journal_header_imp t1
                                where
                                EXISTS (SELECT 1
                                        FROM exp_emp_assign_e_v t
                                       WHERE t.user_id =  ${/session/@user_id}
                                         AND t.position_code IN ('026'))
                                and t1.journal_type in('GENERAL_SALARIES','BPO_SALARIES')
                union
                 select       t1.journal_header_id,
                              t1.journal_submit_batch_id,
                                     t1.journal_num,
                                     t1.error_message,
                                     t1.total_amount_dr,
                                     t1.total_amount_cr,
                                     (select c.company_short_name
                                        from fnd_companies_vl c
                                       where c.company_id = t1.je_company_id) js_company_short_name,
                                     t1.period_name,
                                     to_char(t1.journal_date,'yyyy-mm-dd') journal_date,
                                     t1.currency_code,
                                     (SELECT gc.currency_name
                                        FROM gld_currency_v gc
                                       WHERE gc.currency_code = t1.currency_code) currency_desc,
                                     t1.total_amount_fuc_dr,
                                     (SELECT d.je_transaction_desc
                                        FROM gld_je_transaction d
                                       WHERE d.je_transaction_code = t1.je_transaction_code) je_transaction_desc,
                                     t1.description,
                                     (SELECT COUNT(1)
                                        FROM hls_journal_detail_imp t
                                       WHERE t.journal_header_id = t1.journal_header_id) je_detail_count,
                                     t1.wfl_status,
                                     (select v.code_value_name value_name
                                        from sys_code_values_v v
                                       where v.code = 'HLS536_WFL_STATUS'
                                         and v.code_value = t1.wfl_status) wfl_status_n,
                                     t1.reversed_flag,
                                     t1.reverse_wfl_status,
                                     (select v.code_value_name value_name
                                        from sys_code_values_v v
                                       where v.code = 'HLS536_WFL_STATUS_REVERSE'
                                         and v.code_value = t1.reverse_wfl_status) reverse_wfl_status_n,
                                     t1.journal_type,
                                     (select t.post_gl_status
                                        from hls_journal_header t
                                       where t.journal_header_imp_id = t1.journal_header_id
                                      ) post_gl_status
                                FROM hls_journal_header_imp t1
                                where
                                 EXISTS (SELECT 1
                                        FROM exp_emp_assign_e_v t
                                       WHERE t.user_id =  ${/session/@user_id}
                                         AND t.position_code IN ('017') or ${/session/@user_id} =  10418
                                         )
                                and t1.journal_type in('GENERAL_SALARIES','BPO_SALARIES','COST_IMPORT','FUND_POOL','GLOBAL_TEMPLATE','FINANCIAL_TEMPLATE')

                ) t1
                 #WHERE_CLAUSE#
                 order by t1.journal_header_id desc
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
         <bm:query-field name="journal_num" queryExpression="t1.journal_num like ${@journal_num}"/>
         <bm:query-field name="journal_date_from" queryExpression="to_date(t1.journal_date,'yyyy-mm-dd') &gt;= to_date(${@journal_date_from},'yyyy-mm-dd')"/>
         <bm:query-field name="journal_date_to" queryExpression="to_date(t1.journal_date,'yyyy-mm-dd') &lt;= to_date(${@journal_date_to},'yyyy-mm-dd')"/>
         <bm:query-field name="total_amount_dr_from" datatype="java.lang.Double" queryExpression="nvl(t1.total_amount_dr,t1.total_amount_cr) &gt;= ${@total_amount_dr_from}"/>
         <bm:query-field name="total_amount_dr_to" datatype="java.lang.Double" queryExpression="nvl(t1.total_amount_dr,t1.total_amount_cr) &lt;= ${@total_amount_dr_to}"/>
         <bm:query-field name="description" queryExpression="t1.description like ${@description}"/>
         <bm:query-field name="wfl_status" queryExpression="t1.wfl_status=${@wfl_status}"/>
         <bm:query-field name="reverse_wfl_status" queryExpression="t1.reverse_wfl_status=${@reverse_wfl_status}"/>
      </bm:query-fields>
</bm:model>
