<?xml version="1.0" encoding="UTF-8"?> <!-- $Author: qwm $Date: 2013-8-30 上午11:30:49 $Revision: 1.0 $Purpose: --> <bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm"> <bm:operations> <bm:operation name="query"> <bm:query-sql><![CDATA[ SELECT * FROM (SELECT (SELECT b.bp_bank_account_num FROM csh_payment_req_debt_ln_lv b WHERE cl.payment_req_ln_id = b.payment_req_ln_id ) bp_num, (SELECT b.bp_bank_account_name FROM csh_payment_req_debt_ln_lv b WHERE cl.payment_req_ln_id = b.payment_req_ln_id ) bp_bank_account_name, h.payment_req_number, h.payment_req_id, h.req_date, h.business_type, h.document_type, h.document_category, h.approval_date, h.apply_pay_date, h.approval_status, h.closed_date, h.closed_flag, h.created_by, (SELECT SUM(NVL(rl.amount, 0) - NVL( (SELECT SUM(amount) FROM csh_payment_req_ln_ddct ld WHERE ld.payment_req_ln_id = rl.payment_req_ln_id ), 0) - NVL( (SELECT SUM(lp.write_off_amt) FROM csh_payment_req_ln_prepay lp WHERE lp.payment_req_ln_id = rl.payment_req_ln_id ), 0)) FROM csh_payment_req_ln rl WHERE rl.payment_req_ln_id = cl.payment_req_ln_id ) sum_act_amount, (SELECT e.ebank_name FROM csh_ebank e WHERE e.ebank_id = cc.direct_debit_bank_id ) bank_full_name, (SELECT e.name FROM sys_user u, exp_employees e WHERE h.owner_user_id = u.user_id AND u.employee_id = e.employee_id ) user_name, h.bp_id, (SELECT b.bp_bank_account_name FROM csh_payment_req_debt_ln_lv b WHERE cl.payment_req_ln_id = b.payment_req_ln_id ) bp_name, (SELECT b.bp_category FROM hls_bp_master b WHERE h.bp_id = b.bp_id ) bp_category, (SELECT c.description FROM hls_bp_master b,hls_bp_category c WHERE h.bp_id = b.bp_id and b.bp_category = c.bp_category ) bp_category_n, (SELECT b.bp_code FROM hls_bp_master b WHERE h.bp_id = b.bp_id ) bp_code, h.amount, cc.contract_number, (select pp.project_number from prj_project pp where pp.project_id=cc.project_id) project_number, (SELECT hm.bp_name FROM hls_bp_master hm where hm.bp_id=cc.bp_id_tenant ) bp_id_tenant_n, h.print_flag, decode(cl.payment_status,'FULL','Y','N') payment_flag, h.currency_code, cc.fin_manager, (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id=cc.fin_manager ) fin_manager_n, (SELECT vl.price FROM hls_lease_products vl,con_contract_lease_item l WHERE vl.lease_products_code = l.item_frame_number and l.contract_id = cc.contract_id) price, (select sv.code_value_name from hls_bpm_interface hi,sys_code_values_v sv where hi.payment_req_id=h.payment_req_id and sv.code='CSH508' and sv.code_value=hi.status) status_n FROM csh_payment_req_hd h, csh_payment_req_ln cl, con_contract cc WHERE h.payment_req_id=cl.payment_req_id and cl.ref_doc_id=cc.contract_id and cc.contract_status not in ('CLOSED') ) h #WHERE_CLAUSE# ORDER BY req_date DESC, payment_req_number DESC ]]></bm:query-sql> </bm:operation> </bm:operations> <bm:fields> <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="sum_act_amount" databaseType="NUMBER" datatype="java.lang.Double"/> <bm:field name="bp_num" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="bank_full_name" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="bp_bank_account_name" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="payment_flag" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="payment_req_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PAYMENT_REQ_ID" prompt="CSH_PAYMENT_REQ_HD.PAYMENT_REQ_ID"/> <bm:field name="payment_req_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PAYMENT_REQ_NUMBER" required="true"/> <bm:field name="req_date" databaseType="DATE" datatype="java.util.Date" physicalName="REQ_DATE"/> <bm:field name="approval_date" databaseType="DATE" datatype="java.util.Date" physicalName="APPROVAL_DATE"/> <bm:field name="apply_pay_date" databaseType="DATE" datatype="java.util.Date" physicalName="apply_pay_date"/> <bm:field name="approval_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="APPROVAL_STATUS"/> <bm:field name="closed_date" databaseType="DATE" datatype="java.util.Date" physicalName="CLOSED_DATE"/> <bm:field name="closed_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CLOSED_FLAG"/> <bm:field name="created_by" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CREATED_BY"/> <bm:field name="user_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="user_name"/> <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_name"/> <bm:field name="bp_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_category"/> <bm:field name="bp_category_n" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="amount"/> <bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="currency_code"/> <bm:field name="print_flag" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="bp_id_tenant_n" datatype="java.lang.String"/> <bm:field name="fin_manager_n" datatype="java.lang.String"/> <bm:field name="price" databaseType="java.lang.Double"/> <bm:field name="status_n" datatype="java.lang.String"/> </bm:fields> <bm:query-fields> <bm:query-field name="agent_flag" queryExpression="h.bp_id = decode((select su.bp_category from sys_user su where su.user_id =${/session/@user_id}),'AGENT',(select su.bp_id from sys_user su where su.user_id =${/session/@user_id}),'EMPLOYEE',h.bp_id)"/> <bm:query-field name="print_flag" queryExpression="nvl(h.print_flag,'N') = ${@print_flag} and h.approval_status = 'APPROVED' and nvl(h.closed_flag,'N') = 'N'"/> <bm:query-field name="payment_flag" queryExpression="h.payment_flag = ${@payment_flag}"/> <bm:query-field name="payment_req_number_from" datatype="java.lang.String" queryExpression="h.payment_req_number>=${@payment_req_number_from}"/> <bm:query-field name="payment_req_number_to" datatype="java.lang.String" queryExpression="h.payment_req_number<=${@payment_req_number_to}"/> <bm:query-field name="payment_req_number" queryExpression="upper(h.payment_req_number) like '%' || upper(${@payment_req_number}) || '%'"/> <bm:query-field name="contract_number" queryExpression="upper(h.contract_number) like '%' || upper(${@contract_number}) || '%'"/> <bm:query-field name="req_date_from" datatype="java.lang.String" queryexpression="h.req_date >= to_date(${@req_date_from},'yyyy-mm-dd')"/> <bm:query-field name="req_date_to" datatype="java.lang.String" queryexpression="h.req_date <= to_date(${@req_date_to},'yyyy-mm-dd')"/> <bm:query-field name="bp_code" datatype="java.lang.String" queryExpression="h.bp_code=${@bp_code}"/> <bm:query-field name="bp_code_from" datatype="java.lang.String" queryExpression="h.bp_code>=${@bp_code_from}"/> <bm:query-field name="bp_code_to" datatype="java.lang.String" queryExpression="h.bp_code<=${@bp_code_to}"/> <bm:query-field name="apply_pay_date_from" datatype="java.lang.String" queryexpression="trunc(h.apply_pay_date) >= to_date(${@apply_pay_date_from},'yyyy-mm-dd')"/> <bm:query-field name="apply_pay_date_to" datatype="java.lang.String" queryexpression="trunc(h.apply_pay_date) <= to_date(${@apply_pay_date_to},'yyyy-mm-dd')"/> <bm:query-field name="amount_from" datatype="java.lang.String" queryExpression="h.amount>=${@amount_from}"/> <bm:query-field name="amount_to" datatype="java.lang.String" queryExpression="h.amount<=${@amount_to}"/> <bm:query-field name="approval_date_from" datatype="java.lang.String" queryexpression="trunc(h.approval_date) >= to_date(${@approval_date_from},'yyyy-mm-dd')"/> <bm:query-field name="approval_date_to" datatype="java.lang.String" queryexpression="trunc(h.approval_date) <= to_date(${@approval_date_to},'yyyy-mm-dd')"/> <bm:query-field name="approval_status" datatype="java.lang.String" queryExpression="h.approval_status=${@approval_status}"/> <bm:query-field name="submitted_flag" datatype="java.lang.String" queryExpression="h.submitted_flag=${@submitted_flag}"/> <bm:query-field name="closed_flag" datatype="java.lang.String" queryExpression="h.closed_flag=${@closed_flag}"/> <bm:query-field name="currency_code" datatype="java.lang.String" queryExpression="h.currency_code=${@currency_code}"/> <bm:query-field name="closed_date_from" datatype="java.lang.String" queryexpression="trunc(h.closed_date) >= to_date(${@closed_date_from},'yyyy-mm-dd')"/> <bm:query-field name="closed_date_to" datatype="java.lang.String" queryexpression="trunc(h.closed_date) <= to_date(${@closed_date_to},'yyyy-mm-dd')"/> <bm:query-field name="bp_id_tenant_n" datatype="java.lang.String" queryexpression="h.bp_id_tenant_n like ${@bp_id_tenant_n}"/> <bm:query-field name="fin_manager_n" datatype="java.lang.String" queryexpression="h.fin_manager_n like ${@fin_manager_n}"/> <bm:query-field name="bp_category" dataType="java.lang.String" queryExpression="h.bp_category = ${@bp_category}"/> </bm:query-fields> </bm:model>