<?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
                        TO_CHAR(t2.transfer_time, 'yyyy-mm-dd') AS transfer_time,
                        t1.billing_way,
                        (select scv.code_value_name from sys_code_values_v scv where scv.code = 'DS_INVOICE_CATEGORY' and scv.code_value = t1.billing_way) billing_way_n,
                        t2.recipient,
                        TO_CHAR(t2.express_date, 'yyyy-mm-dd') AS express_date,
                        t2.express_num,
                        t2.express_to,
                        t2.invoice_hd_id,
                        t1.contract_number,
                        t1.search_term_1,
                        t2.document_number,
                        --t2.invoice_number,
                         (nvl(t2.invoice_number,
                                (Select he.yfphm
                                   From acr_ele_invoice_hd he
                                  Where he.invoice_hd_id = t2.invoice_hd_id))) invoice_number,
                        TO_CHAR(t2.invoice_date, 'yyyy-mm-dd') AS invoice_date,
                        t2.bp_name invoice_title,
                        (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,
                (Select dbms_lob.substr(wmsys.wm_concat(cc.pattern),
                1000)
                From con_contract_lease_item cc
                Where cc.contract_id = t1.contract_id
                And cc.equipment_type = 'MAIN') pattern,
                (select h.bp_name
                        from hls_bp_master h
                        where h.bp_id = t1.bp_id_agent_level1) bp_agent_name,
                (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,
               (nvl(t2.vat_invoice_code,
                    (Select he.yfpdm
                       From acr_ele_invoice_hd he
                      Where he.invoice_hd_id = t2.invoice_hd_id))) vat_invoice_code,
                    (select aeih.ele_invoice_hd_id
                  from acr_ele_invoice_hd aeih
                 where aeih.invoice_hd_id = t2.invoice_hd_id) ele_invoice_hd_id,
               nvl((select nvl(aeih.status, 'NEW')
                  from acr_ele_invoice_hd aeih
                 where aeih.invoice_hd_id = t2.invoice_hd_id),'NEW') ele_invoice_status,
               nvl((select decode(nvl(aeih.status, 'NEW'),
                              'NEW',
                              '未传入',
                              'IMPORT',
                              '已传入',
                              'DOWNLOAD',
                              '已下载')
                  from acr_ele_invoice_hd aeih
                 where aeih.invoice_hd_id = t2.invoice_hd_id),'未传入') ele_invoice_status_desc
                    FROM
                        con_contract t1,
                        acr_invoice_hd t2
                    WHERE
                        t1.contract_id     = t2.contract_id AND
                        --t2.express_status IN ('NEW', 'SENT_BACK') AND
                        t2.invoice_kind    in ('0','2') and
                        --t2.invoice_number is not null  and

                        t2.reversed_flag ='N'
                    ) t #WHERE_CLAUSE#
                ORDER BY
                    invoice_number 
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:parameters>
                <bm:parameter name="express_status" dataType="java.lang.String" forUpdate="true" required="true"/>
                <bm:parameter name="invoice_hd_id" dataType="java.lang.Long" required="true"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                update acr_invoice_hd
                set express_status = ${@express_status},
                	invoice_submitter = ${/session/@user_id},
                	invoice_submit_date = sysdate
                where invoice_hd_id = ${@invoice_hd_id}
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="bp_agent_name" queryExpression="t.bp_agent_name like ${@bp_agent_name}"/>
        <bm:query-field name="contract_number" queryExpression="t.contract_number = ${@contract_number}"/>
        <bm:query-field name="document_number" queryExpression="t.document_number = ${@document_number}"/>
        <bm:query-field name="invoice_date_from" queryExpression="t.invoice_date &gt;= ${@invoice_date_from}"/>
        <bm:query-field name="invoice_date_to" queryExpression="t.invoice_date &lt;= ${@invoice_date_to}"/>
        <bm:query-field name="invoice_number_from" queryExpression="t.invoice_number &gt;=  ${@invoice_number_from}"/>
        <bm:query-field name="invoice_number_to" queryExpression="t.invoice_number &lt;= ${@invoice_number_to}"/>

        <bm:query-field name="bp_name" queryExpression="t.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
        <bm:query-field name="times" queryExpression="t.times = ${@times}"/>
        <bm:query-field name="total_amount" queryExpression="t.total_amount = ${@total_amount}"/>
        <bm:query-field name="product_name" queryExpression="product_name like ${@product_name}"/>
        <bm:query-field name="division_n" queryExpression="division_n = ${@division_n}"/>
        <bm:query-field name="billing_way" queryExpression="t.billing_way = ${@billing_way}"/>
        <bm:query-field name="transfer_time" queryExpression="t.transfer_time = ${@transfer_time}"/>

        <bm:query-field name="express_date" queryExpression="t.express_date = ${@express_date}"/>

        <bm:query-field name="express_num" queryExpression="t.express_num = ${@express_num}"/>

        <bm:query-field name="pattern" queryExpression="t.pattern = ${@pattern}"/>

        <bm:query-field name="machine_number" queryExpression="t.machine_number = ${@machine_number}"/>

    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="query" expression="company_id = ${/session/@company_id}"/>
    </bm:data-filters>
</bm:model>
