<?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 >= ${@invoice_date_from}"/> <bm:query-field name="invoice_date_to" queryExpression="t.invoice_date <= ${@invoice_date_to}"/> <bm:query-field name="invoice_number_from" queryExpression="t.invoice_number >= ${@invoice_number_from}"/> <bm:query-field name="invoice_number_to" queryExpression="t.invoice_number <= ${@invoice_number_to}"/> <bm:query-field name="bp_name" queryExpression="t.bp_name like '%'||${@bp_name}||'%'"/> <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>