<?xml version="1.0" encoding="UTF-8"?> <!-- $Author: qianming $Date: 2014-7-25 下午2:18:46 $Revision: 1.0 $Purpose: --> <bm:model xmlns:s="leaf.plugin.script" xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT" needAccessControl="false" defaultOrderBy="t1.contract_id DESC"> <bm:fields> <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CON_CONTRACT.CONTRACT_ID"/> <bm:field name="data_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DATA_CLASS" prompt="CON_CONTRACT.DATA_CLASS"/> <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" prompt="CON_CONTRACT.CONTRACT_NUMBER"/> <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME" prompt="CON_CONTRACT.CONTRACT_NAME"/> <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID" prompt="CON_CONTRACT.PROJECT_ID"/> <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="CON_CONTRACT.COMPANY_ID"/> <bm:field name="lease_channel" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHANNEL" prompt="CON_CONTRACT.LEASE_CHANNEL"/> <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" prompt="CON_CONTRACT.BP_ID_TENANT"/> <bm:field name="bp_id_tenant_n" expression="(select bp_name from hls_bp_master where bp_id = t1.bp_id_tenant)" forInsert="false" forUpdate="false"/> <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" prompt="CON_CONTRACT.CONTRACT_STATUS"/> <bm:field name="bankid" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANKID" prompt="CON_CONTRACT.EANKID"/> <bm:field name="ebank_id_code" expression="(select bank_code from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/> <bm:field name="ebank_id_n" expression="(select bank_full_name from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/> <bm:field name="ebank_id_c" expression="(select bank_short_name from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/> <bm:field name="ebank_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_TYPE" prompt="CON_CONTRACT.EBANK_TYPE"/> <bm:field name="ebank_account_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_ACCOUNT_NAME" prompt="CON_CONTRACT.EBANK_ACCOUNT_NAME"/> <bm:field name="ebank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ebank_account_num" prompt="CON_CONTRACT.EBANK_ACCOUNT_NUM"/> <bm:field name="idtype_n" expression="(SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'HLS211_ID_TYPE' AND scv.code_value = t1.idtype)" forInsert="false" forUpdate="false"/> <bm:field name="idtype"/> <bm:field name="idcode"/> <bm:field name="mobile"/> <!-- <bm:field name="ebank_protocol_status" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="CON591_EBANK_PROTOCOL_STATUS" lookupField="ebank_protocol_status_n" physicalName="EBANK_PROTOCOL_STATUS"/> --> <bm:field name="ebank_protocol_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_PROTOCOL_STATUS"/> <bm:field name="ebank_protocol_status_n" expression="(select sv.code_value_name from sys_codes s,sys_code_values_vl sv where s.code_id = sv.code_id and sv.code_value = t1.ebank_protocol_status and s.code ='CON591_EBANK_PROTOCOL_STATUS')" forInsert="false" forUpdate="false"/> <bm:field name="protocol_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROTOCOL_NUMBER"/> <bm:field name="ebank_account_prop" databaseType="VARCHAR2" datatype="java.lang.String"/> <bm:field name="deduction_flag"/> <bm:field name="expireddate"/> <bm:field name="bankacctname"/> <bm:field name="bankacctid"/> <bm:field name="acctype"/> <bm:field name="deduction_flag_n"/> <bm:field name="acctype_n" expression="(select sv.code_value_name from sys_codes s,sys_code_values_vl sv where s.code_id = sv.code_id and sv.code_value = t1.acctype and s.code ='EBANK_DEDUCT_TYPE')" forInsert="false" forUpdate="false"/> <bm:field name="bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="bank_account_id"/> <bm:field name="bank_account_code" expression="(select bank_account_code from csh_bank_account_v where bank_account_id = t1.bank_account_id)" forInsert="false" forUpdate="false"/> <bm:field name="deduct_bank"/> <bm:field name="deduct_bank_n"/> <bm:field name="bp_id_agent_level1"/> <bm:field name="app_bank_name"/> <bm:field name="app_bank_branch"/> <bm:field name="app_bank_account_name"/> <bm:field name="app_bank_account_num"/> <bm:field name="contract_status_desc"/> <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/> <bm:field name="bp_id_agent_level1_n" expression="(select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = t1.bp_id_agent_level1)" forInsert="false" forUpdate="false"/> </bm:fields> <bm:primary-key> <bm:pk-field name="contract_id"/> </bm:primary-key> <bm:features> <f:standard-who/> <s:bm-script><![CDATA[ var cx = Packages.leaf.javascript.Context.getCurrentContext(); Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js"); ]]></s:bm-script> </bm:features> <bm:operations> <bm:operation name="update"> <bm:update-sql><![CDATA[ begin -- Call the procedure csh_bank_account_pkg.update_contract_bank_info(p_contract_id => ${@contract_id}, p_bankid => ${@bankid}, p_deduction_flag => ${@deduction_flag}, p_acctype => ${@acctype}, p_bankacctname => ${@bankacctname}, p_bankacctid => ${@bankacctid}, p_expireddate => ${@expireddate}, p_mobile => ${@mobile}, p_idcode => ${@idcode}, p_bank_account_id => ${@bank_account_id}, p_deduct_bank => ${@deduct_bank}, p_app_bank_name => ${@app_bank_name}, p_app_bank_branch => ${@app_bank_branch}, p_app_bank_account_name => ${@app_bank_account_name}, p_app_bank_account_num => ${@app_bank_account_num}, p_user_id =>${/session/@user_id}); end; ]]></bm:update-sql> </bm:operation> <bm:operation name="query"> <bm:query-sql><![CDATA[ SELECT t1.contract_id, t1.data_class, t1.contract_number, t1.contract_name, t1.project_id, t1.company_id, t1.lease_channel, t1.bp_id_tenant, (SELECT bp_name FROM hls_bp_master WHERE bp_id = t1.bp_id_tenant ) AS bp_id_tenant_n, t1.contract_status, (SELECT v.code_value_name FROM sys_code_values_v v WHERE v.code = 'CON500_CONTRACT_STATUS' AND v.code_value = t1.contract_status ) contract_status_desc, t1.bankid, (SELECT bank_code FROM csh_tenant_bank_v WHERE bank_id = t1.bankid ) AS ebank_id_code, (SELECT bank_full_name FROM csh_tenant_bank_v WHERE bank_id = t1.bankid ) AS ebank_id_n, (SELECT bank_short_name FROM csh_tenant_bank_v WHERE bank_id = t1.bankid ) AS ebank_id_c, t1.ebank_type, t1.ebank_account_name, t1.ebank_account_num, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'HLS211_ID_TYPE' AND scv.code_value = NVL(t1.idtype,'ID_CARD') ) AS idtype_n, NVL(t1.idtype,'ID_CARD') AS idtype, NVL(t1.idcode, (SELECT l.id_card_no FROM hls_bp_master_lv l WHERE l.bp_id = t1.bp_id_tenant )) AS idcode, NVL(t1.mobile, (SELECT l.phone FROM hls_bp_master_lv l WHERE l.bp_id = t1.bp_id_tenant )) AS mobile, t1.ebank_protocol_status, (SELECT sv.code_value_name FROM sys_codes s, sys_code_values_vl sv WHERE s.code_id = sv.code_id AND sv.code_value = t1.ebank_protocol_status AND s.code ='CON591_EBANK_PROTOCOL_STATUS' ) AS ebank_protocol_status_n, t1.protocol_number, t1.ebank_account_prop, t1.deduction_flag, (SELECT V.CODE_VALUE_NAME FROM SYS_CODE_VALUES_V V WHERE V.CODE = 'YES_NO' AND V.CODE_VALUE = t1.deduction_flag) as deduction_flag_n, t1.expireddate, t1.bankacctname, t1.bankacctid, t1.acctype, (SELECT sv.code_value_name FROM sys_codes s, sys_code_values_vl sv WHERE s.code_id = sv.code_id AND sv.code_value = t1.acctype AND --s.code ='DEDUCTION_CARD_TYPE' s.code ='EBANK_DEDUCT_TYPE' ) AS acctype_n, t1.bank_account_id, (select bank_account_code from csh_bank_account_v where bank_account_id = t1.bank_account_id) bank_account_code, t1.deduct_bank, (SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = 'DEDUCT_BANK' AND scv.code_value = t1.deduct_bank ) AS deduct_bank_n, t1.bp_id_agent_level1, (select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n, t1.app_bank_name, app_bank_branch, app_bank_account_name, app_bank_account_num, t1.lease_execution_date FROM con_contract t1 #WHERE_CLAUSE# #ORDER_BY_CLAUSE# --ORDER BY --t1.contract_number DESC ]]></bm:query-sql> </bm:operation> </bm:operations> <bm:query-fields> <bm:query-field name="contract_id" queryExpression="t1.contract_id in ${:@contract_id}"/> <bm:query-field field="bp_id_tenant" queryOperator="="/> <bm:query-field field="ebank_protocol_status" queryOperator="="/> <bm:query-field field="bankid" queryOperator="="/> <bm:query-field field="ebank_id_n" queryExpression="(select bank_full_name from csh_bank_v where bank_id = t1.bankid) like '%'||${@ebank_id_n}||'%'"/> <bm:query-field field="protocol_number" queryOperator="="/> <!-- <bm:query-field name="flag" queryExpression="exists (select 1 from dual where (t1.ebank_account_name is not null and t1.ebank_account_num is not null and t1.ebank_type is not null and t1.ebank_id is not null and ${@flag} = 'Y') or ${@flag} = 'N')"/> --> <bm:query-field field="contract_number" queryExpression="t1.contract_number like '%'||${@contract_number}||'%'"/> <bm:query-field field="contract_name" queryExpression="t1.contract_name like '%'||${@contract_name}||'%'"/> <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like '%'||${@bankacctid}||'%'"/> <bm:query-field name="status" queryExpression="(t1.ebank_protocol_status in ('0','3','4','5') or t1.ebank_protocol_status is null)"/> <bm:query-field name="pki_status" queryExpression="(t1.ebank_protocol_status not in ('2','4'))"/> <bm:query-field name="deduction_flag" queryExpression="nvl(t1.deduction_flag, 'N')=${@deduction_flag}"/> <bm:query-field field="acctype" queryOperator="="/> <bm:query-field name="deduct_bank" queryExpression="nvl(t1.deduct_bank,'N')=${@deduct_bank}"/> <bm:query-field field="bankacctname" queryExpression="t1.bankacctname like '%'||${@bankacctname}||'%'"/> <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like '%'||${@bankacctid}||'%'"/> <bm:query-field name="expireddate_from" queryExpression="${@expireddate_from} <= t1.expireddate"/> <bm:query-field name="expireddate_to" queryExpression="${@expireddate_to} >= t1.expireddate"/> <bm:query-field name="bp_id_agent_level1" queryExpression=" t1.bp_id_agent_level1 = ${@bp_id_agent_level1}"/> </bm:query-fields> <bm:data-filters> <!--<bm:data-filter enforceOperations="query" expression="t1.contract_status NOT IN ('CHANGE','CANCEL','CLOSED')"/>--> <bm:data-filter enforceOperations="query" expression="t1.contract_status IN ('SIGN','INCEPT','TERMINATE','ETING','ET','PENDING','ADING','AD','AGENT_TERMINATE','LOSSSHARE_TERMINATE','VIRTUAL_TERMINATE','REPUR','PAID')"/> <bm:data-filter enforceOperations="query" expression="t1.data_class = 'NORMAL'"/> <bm:data-filter enforceOperations="query" expression="exists (select 1 from hls_bp_master where bp_id = t1.bp_id_tenant)"/> </bm:data-filters> </bm:model>