<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                           Select *
  From (Select t2.invoice_hd_id,
               t1.contract_number,
               t1.search_term_1,
               t1.contract_id,
               t2.document_number,
               t2.invoice_number,
               to_char(t2.invoice_date, 'yyyy-mm-dd') As invoice_date,
               --t2.bp_name,
               (Select bm.bp_name
                  From hls_bp_master bm
                 Where bm.bp_id = t1.bp_id_tenant) bp_name,
               t2.total_amount,
               (Select a.times
                  From con_contract_cashflow a
                 Where a.cashflow_id =
                       (Select b.cashflow_id
                          From acr_invoice_ln b
                         Where b.invoice_hd_id = t2.invoice_hd_id
                           And rownum = 1)) As times,
               t2.company_id,
               (Select dbms_lob.substr(wmsys.wm_concat(l.description), 1000)
                  From acr_invoice_ln l
                 Where l.invoice_hd_id = t2.invoice_hd_id
                   And l.description Is Not Null) description_l,
               (Select dbms_lob.substr(wmsys.wm_concat(l.product_name), 1000)
                  From acr_invoice_ln l
                 Where l.invoice_hd_id = t2.invoice_hd_id
                   And l.product_name Is Not Null) product_name,
               nvl(t2.express_status, 'NEW') express_status,
               (Select scv.code_value_name
                  From sys_code_values_v scv
                 Where scv.code = 'ACR520_EXPESS_STATUS'
                   And scv.code_value = nvl(t2.express_status, 'NEW')) express_status_n,
               (Select scv.code_value_name
                  From sys_code_values_v scv
                 Where scv.code = 'ACR510_INVOICE_KIND'
                   And scv.code_value = t2.invoice_kind) invoice_kind_n,
               (Select d.description
                  From hls_division d
                 Where d.division = t1.division) As division_n,
               (Select dbms_lob.substr(wmsys.wm_concat(cc.machine_number),
                                       1000)
                  From con_contract_lease_item cc
                 Where cc.contract_id = t1.contract_id
                   And cc.equipment_type = 'MAIN') machine_number,
                to_char(t2.transfer_time, 'yyyy-mm-dd') As transfer_time,
                t2.recipient,
                to_char(t2.express_date, 'yyyy-mm-dd') As express_date,
                t2.express_num,
                t2.express_to,
                (select to_char(wm_concat(distinct(ci.description)))
                  from acr_invoice_ln        ail,
                       con_contract_cashflow ccc,
                       hls_cashflow_item     ci
                 where ail.invoice_hd_id = t2.invoice_hd_id
                   and ccc.cashflow_id = ail.cashflow_id
                   and ci.cf_item = ccc.cf_item) cf_item_n
          From con_contract t1, acr_invoice_hd t2
         Where t1.contract_id = t2.contract_id
           And t2.invoice_kind In ('0', '2')
           And t2.reversed_flag = 'N') t
           where t.contract_id = ${@contract_id}
         Order By invoice_number
            ]]></bm:query-sql>
        </bm:operation>


    </bm:operations>

 <!--   <bm:query-fields>
        <bm:query-field field="contract_id" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="agent_name" queryOperator="like"/>
        <bm:query-field name="lease_end_date"
                        queryExpression="t1.lease_end_date =  ${@lease_end_date}"/>
       &lt;!&ndash; <bm:query-field name="et_date"
                        queryExpression="t1.et_date =  ${@et_date}"/>&ndash;&gt;
        <bm:query-field name="send_flag"
                        queryExpression="t1.send_flag =  ${@send_flag}"/>
        <bm:query-field name="trans_cert_print_flag"
                        queryExpression="t1.trans_cert_print_flag =  ${@trans_cert_print_flag}"/>

        <bm:query-field name="lease_execution_date_from"
                        queryExpression="t1.lease_execution_date &gt;=  ${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to"
                        queryExpression="t1.lease_execution_date &lt;=  ${@lease_execution_date_to}"/>
        <bm:query-field name="et_date_from"
                        queryExpression="t1.et_date &gt;=   ${@et_date_from}"/>
        <bm:query-field name="et_date_to"
                        queryExpression="t1.et_date &lt;= ${@et_date_to}"/>
    </bm:query-fields>-->
</bm:model>
