acr_invoice_delivery.lwm 8.44 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
<?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>