con_contract.lwm 15.6 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 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
<?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 = &apos;HLS211_ID_TYPE&apos; 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 =&apos;CON591_EBANK_PROTOCOL_STATUS&apos;)" 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 =&apos;EBANK_DEDUCT_TYPE&apos;)" 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 &apos;%&apos;||${@ebank_id_n}||&apos;%&apos;"/>
        <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} = &apos;Y&apos;) or ${@flag} = &apos;N&apos;)"/> -->
        <bm:query-field field="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field field="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like &apos;%&apos;||${@bankacctid}||&apos;%&apos;"/>
        <bm:query-field name="status" queryExpression="(t1.ebank_protocol_status in (&apos;0&apos;,&apos;3&apos;,&apos;4&apos;,&apos;5&apos;) or t1.ebank_protocol_status is null)"/>
        <bm:query-field name="pki_status" queryExpression="(t1.ebank_protocol_status not in (&apos;2&apos;,&apos;4&apos;))"/>
        <bm:query-field name="deduction_flag" queryExpression="nvl(t1.deduction_flag, &apos;N&apos;)=${@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 &apos;%&apos;||${@bankacctname}||&apos;%&apos;"/>
        <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like &apos;%&apos;||${@bankacctid}||&apos;%&apos;"/>
        <bm:query-field name="expireddate_from" queryExpression="${@expireddate_from} &lt;= t1.expireddate"/>
        <bm:query-field name="expireddate_to" queryExpression="${@expireddate_to} &gt;=  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 (&apos;CHANGE&apos;,&apos;CANCEL&apos;,&apos;CLOSED&apos;)"/>-->
        <bm:data-filter enforceOperations="query" expression="t1.contract_status IN (&apos;SIGN&apos;,&apos;INCEPT&apos;,&apos;TERMINATE&apos;,&apos;ETING&apos;,&apos;ET&apos;,&apos;PENDING&apos;,&apos;ADING&apos;,&apos;AD&apos;,&apos;AGENT_TERMINATE&apos;,&apos;LOSSSHARE_TERMINATE&apos;,&apos;VIRTUAL_TERMINATE&apos;,&apos;REPUR&apos;,&apos;PAID&apos;)"/>
        <bm:data-filter enforceOperations="query" expression="t1.data_class = &apos;NORMAL&apos;"/>
        <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>