<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select t1.*,
                   decode(t1.ticket_type,
                   '1',
                   round(t1.ACTUAL_PRICE / 1.03, 2),
                   round(t1.ACTUAL_PRICE / 1.16, 2)) sale_amount
            from
            (
            select cti.STORAGE_DATE,
                   cti.AGENT_NAME,
                   cti.CNTRT_NO,
                   cti.BP_NAME,
                   cti.PATTERN,
                   cti.MACHINE_NUMBER,
                   nvl(cti.STORAGE_AMOUNT, 0) STORAGE_AMOUNT,
                   nvl(cti.ACTUAL_PRICE, 0) ACTUAL_PRICE,
                   cti.COLLECTION_TIME,
                   substr(cti.CNTRT_NO, 6, 1) ticket_type,
                   nvl(t1.AMOUNT_FUC_CR, 0) income,
                   nvl(t2.AMOUNT_FUC_CR, 0) tax,
                   cti.SALES_OPERATEION_DATE
          from CON_TRAILER_INFO cti,
               (SELECT l.AMOUNT_FUC_CR, 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_TRL_DL_INFO_SG190401'
                   AND h.source_table = 'CON_TRAILER_INFO'
                   and h.reversed_flag = 'N'
                   AND h.je_transaction_code =
                       'CON_TRAILER_DETAIL_INFO_SALES'
                   and gav.ACCOUNT_ID = l.ACCOUNT_ID
                   and gav.ACCOUNT_CODE = '51990141') t1,
               (select l.AMOUNT_FUC_CR, 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_TRL_DL_INFO_SG190401'
                   AND h.source_table = 'CON_TRAILER_INFO'
                   and h.reversed_flag = 'N'
                   AND h.je_transaction_code =
                       'CON_TRAILER_DETAIL_INFO_SALES'
                   and gav.ACCOUNT_ID = l.ACCOUNT_ID
                   and gav.ACCOUNT_CODE in ('21100032', '21100048')) t2
           where cti.CON_TRAILER_ID = t1.SOURCE_ID(+)
           and cti.CON_TRAILER_ID = t2.SOURCE_ID(+)
           and cti.CREATE_SALES_JE_FLAG = 'Y'
           and (cti.SEC_CAR_REVERSE_FLAG != 'Y' or
               cti.SEC_CAR_REVERSE_FLAG is null)
           and (cti.SEC_CAR_REVERSE_SALES_FLAG != 'Y' or
               cti.SEC_CAR_REVERSE_SALES_FLAG is null)
               ) t1
               #WHERE_CLAUSE#
               order by t1.sales_operateion_date desc

            ]]></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="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="collection_time" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="ticket_type"/>
        <bm:field name="income" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="tax" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="sale_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
    </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-field name="storage_date_from" queryExpression="t1.storage_date &gt;= to_date(${@storage_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="storage_date_to" queryExpression="t1.storage_date &lt;= to_date(${@storage_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="sales_operateion_date_from" queryExpression="t1.sales_operateion_date &gt;= to_date(${@sales_operateion_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="sales_operateion_date_to" queryExpression="t1.sales_operateion_date &lt;= to_date(${@sales_operateion_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>

</bm:model>
