<?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.batch_id,
                     t1.batch_number,
                     t1.reverse_batch_number,
                     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
                FROM hls_journal_header_imp t1
                where  t1.created_by=${/session/@user_id}

              union
              select t1.journal_header_id,
                     t1.batch_id,
                     t1.batch_number,
                     t1.reverse_batch_number,
                     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
                FROM hls_journal_header_imp t1
                where ${/session/@user_id}=10418
                and(t1.wfl_status in ('APPROVING', 'REJECT', 'APPROVED')
                   )
               union
                select t1.journal_header_id,
                    t1.batch_id,
                    t1.batch_number,
                    t1.reverse_batch_number,
                    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
               FROM hls_journal_header_imp t1
               where ${/session/@user_id}=10417



) 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 &apos;%&apos;||${@journal_num}||&apos;%&apos;"/>
         <bm:query-field name="batch_number" queryExpression="t1.batch_number like &apos;%&apos;||${@batch_number}||&apos;%&apos;"/>
         <bm:query-field name="reverse_batch_number" queryExpression="t1.reverse_batch_number like &apos;%&apos;||${@reverse_batch_number}||&apos;%&apos;"/>
         <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 &apos;%&apos;||${@description}||&apos;%&apos;"/>
         <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>
