<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
            select
              cti.STORAGE_DATE,
              cti.AGENT_NAME,
              cti.CNTRT_NO,
              cti.BP_NAME,
              cti.MACHINE_NUMBER,
              cti.PATTERN,
              1                          MACHINE_COUNTS,
              cti.STORAGE_ADDRESS,
              cti.STORAGE_AMOUNT,
              cti.ACTUAL_PRICE,
              cti.SALES_OPERATEION_DATE,
              substr(cti.CNTRT_NO, 6, 1) ticket_type,
              cti.ACCOUNT_NOTES,
              cti.PHONE_CNO,
              nvl(t2.AMOUNT_FUC_DR, 0) tax
            from CON_TRAILER_INFO cti,
               (select l.AMOUNT_FUC_DR, h.source_id
                  FROM hls_journal_header  h,
                       hls_journal_detail  l,
                       gld_je_template_hds g,
                       GLD_ACCOUNTS_VL     gav
                 WHERE h.journal_header_id = l.journal_header_id
                   AND g.je_template_hd_id = l.je_template_hd_id
                   AND g.je_template_code = 'CON_TRAILER_DETAI_SG190401'
                   AND h.source_table = 'CON_TRAILER_INFO'
                   and h.reversed_flag = 'N'
                   AND h.je_transaction_code =
                       'CON_TRAILER_DETAIL_INFO'
                   and gav.ACCOUNT_ID = l.ACCOUNT_ID
                   and gav.ACCOUNT_CODE = '21030308') t2
            where cti.CON_TRAILER_ID = t2.SOURCE_ID(+)
              and ((cti.STORAGE_FLAG = 'Y'
               and nvl(cti.SEC_CAR_REVERSE_FLAG,'N') != 'Y'
               and nvl(cti.CREATE_SALES_JE_FLAG,'N') != 'Y')
              or (cti.STORAGE_FLAG = 'Y'
                  and nvl(cti.SEC_CAR_REVERSE_FLAG,'N') != 'Y'
                  and cti.CREATE_SALES_JE_FLAG = 'Y'
                  and cti.SEC_CAR_REVERSE_SALES_FLAG = 'Y'))
                 ) t1
                 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="agent_name"/>
        <bm:field name="cntrt_no"/>
        <bm:field name="bp_name"/>
        <bm:field name="machine_number"/>
        <bm:field name="pattern"/>
        <bm:field name="storage_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="machine_counts" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="storage_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="actual_price" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="sales_operateion_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="ticket_type"/>
        <bm:field name="account_notes"/>
        <bm:field name="storage_address"/>
        <bm:field name="tax"/>


    </bm:fields>

    <bm:query-fields>
        <bm:query-field field="agent_name" queryOperator="like"/>
        <bm:query-field field="cntrt_no" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="machine_number" queryOperator="like"/>
        <bm:query-field field="pattern" queryOperator="like"/>
    </bm:query-fields>

</bm:model>
