tre_loan_contract_overview.lwm 9.69 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
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Guoxuezhao  
    $Date: 2013-6-3 下午5:44:23  
    $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
                    tlc.loan_contract_id,
                    tlc.loan_contract_number,
                    tlc.loan_contract_name,
                    tlc.bank_credit_contract_id,
                    tlc.bank_credit_contract_number,
                    DECODE(tlc.bank_credit_contract_id, NULL, '无授信',
                    (SELECT
                        v.code_value_name
                    FROM
                        tre_bank_credit_contract tbcc,
                        sys_code_values_v v
                    WHERE
                        tbcc.credit_contract_id = tlc.bank_credit_contract_id AND
                        v.code                  = 'TRE501_CREDIT_LIMIT_TYPE' AND
                        v.code_value            = tbcc.credit_limit_type
                    )) credit_limit_type_desc,
                    (SELECT
                        tbcc.credit_amount
                    FROM
                        tre_bank_credit_contract tbcc
                    WHERE
                        tbcc.credit_contract_id = tlc.bank_credit_contract_id
                    ) AS credit_amount,
                    tre_bank_credit_contract_pkg.get_available_amount(tlc.bank_credit_contract_id) AS available_amount,
                    tlc.loan_amount_type,
                    tlc.loan_amount,
                    tlc.loan_date_from,
                    tlc.loan_date_to,
                    tlc.withdraw_amount,
                    (SELECT
                        SUM(p.accrued_interest)
                    FROM
                        tre_interest_payable_plan p
                    WHERE
                        p.loan_contract_id=tlc.loan_contract_id AND
                        p.times          <> 0
                    )loan_accrued_interest,
                    tlc.repayment_amount,
                    tlc.repayment_principal,
                    tlc.repayment_interest,
                    tlc.remaining_amount,
                    tlc.business_type,
                    tlc.loan_contract_status,
                    tlc.loan_contract_status_display,
                    tlc.bank_branch_name,
                    tlc.bank_account_code,
                    tlc.withdraw_account_code,
                    tlc.bank_branch_code,
                    tlc.loan_con_wfl_status,
                    tlc.consulting_fee,
                    tlc.loan_charge,
                    tlc.other_fee,
                    tlc.consulting_fee_rate,
                    tlc.consulting_fee_rate*100
                    ||DECODE(tlc.consulting_fee_rate,NULL,'','%') consulting_fee_rate_desc,
                    tlc.deposit_amount,
                    (SELECT
                        c.code_value_name
                    FROM
                        sys_code_values_v c
                    WHERE
                        c.code_value = NVL(tlc.loan_con_wfl_status,'WFL_NEW') AND
                        c.code       ='TRE_LOAN_CON_WFL_STATUS'
                    ) loan_con_wfl_status_n
                FROM
                    tre_loan_contract_v tlc #WHERE_CLAUSE#
                ORDER BY
                    tlc.creation_date DESC,
                    tlc.loan_date_from DESC,
                    tlc.bank_id,
                    tlc.bank_branch_id
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="loan_con_wfl_status_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="loan_con_wfl_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CON_WFL_STATUS"/>
        <bm:field name="loan_contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LOAN_CONTRACT_ID" required="true"/>
        <bm:field name="loan_contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CONTRACT_NUMBER"/>
        <bm:field name="loan_contract_name"/>
        <bm:field name="bank_credit_contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BANK_CREDIT_CONTRACT_NUMBER"/>
        <bm:field name="credit_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="available_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="loan_amount_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_AMOUNT_TYPE"/>
        <bm:field name="bank_credit_contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANK_CREDIT_CONTRACT_ID"/>
        <bm:field name="business_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BUSINESS_TYPE"/>
        <bm:field name="loan_contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CONTRACT_STATUS"/>
        <bm:field name="loan_contract_status_display" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CONTRACT_STATUS_DISPLAY"/>
        <bm:field name="loan_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="LOAN_AMOUNT"/>
        <bm:field name="loan_date_from" databaseType="DATE" datatype="java.util.Date" physicalName="LOAN_DATE_FROM"/>
        <bm:field name="loan_date_to" databaseType="DATE" datatype="java.util.Date" physicalName="LOAN_DATE_TO"/>
        <bm:field name="withdraw_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WITHDRAW_AMOUNT"/>
        <bm:field name="loan_accrued_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="LOAN_ACCRUED_INTEREST"/>
        <bm:field name="repayment_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="REPAYMENT_AMOUNT"/>
        <bm:field name="consulting_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="loan_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="other_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="consulting_fee_rate_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="consulting_fee_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="repayment_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="REPAYMENT_PRINCIPAL"/>
        <bm:field name="repayment_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="REPAYMENT_INTEREST"/>
        <bm:field name="remaining_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="REMAINING_AMOUNT"/>
        <bm:field name="bank_branch_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BANK_BRANCH_NAME"/>
        <bm:field name="bank_account_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bank_account_code"/>
        <bm:field name="withdraw_account_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="withdraw_account_code"/>
        <bm:field name="bank_branch_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bank_branch_code"/>
        <bm:field name="deposit_amount"/>
        <bm:field name="credit_limit_type_desc"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="wfl_mdf_status_flag" queryExpression="${@wfl_mdf_status_flag}=&apos;Y&apos; and nvl(tlc.LOAN_CON_WFL_STATUS,&apos;WFL_NEW&apos;) not in(&apos;WFL_APPROVING&apos;,&apos;WFL_APPROVED&apos;)"/>
        <bm:query-field field="business_type" queryOperator="="/>
        <bm:query-field field="bank_credit_contract_id" queryOperator="="/>
        <bm:query-field field="loan_contract_status" queryOperator="="/>
        <bm:query-field field="loan_contract_number" queryOperator="like"/>
        <bm:query-field field="loan_contract_id" queryOperator="="/>
        <bm:query-field field="bank_account_code" queryOperator="="/>
        <bm:query-field field="withdraw_account_code" queryOperator="="/>
        <bm:query-field field="bank_branch_code" queryOperator="="/>
        <bm:query-field field="bank_branch_name" queryExpression="tlc.bank_branch_name like  &apos;%&apos; || ${@bank_branch_name} || &apos;%&apos; "/>
        <bm:query-field name="credit_contract_number" queryExpression="tlc.bank_credit_contract_number like ${@credit_contract_number}"/>
        <bm:query-field field="loan_date_from" queryExpression="${@loan_date_from} &lt;= tlc.loan_date_from "/>
        <bm:query-field field="loan_date_to" queryExpression="${@loan_date_to} &gt;=  tlc.loan_date_to "/>
        <bm:query-field name="interest_reset_flag" queryExpression="${@interest_reset_flag}=&apos;Y&apos; and tlc.loan_contract_status in (&apos;REVIEWED&apos;,&apos;CHANGED&apos;)"/>
        <bm:query-field name="review_status_flag" queryExpression="${@review_status_flag}=&apos;Y&apos; and tlc.loan_contract_status in (&apos;NEW&apos;,&apos;CHANGED&apos;)"/>
        <bm:query-field name="withdraw_review_flag" queryExpression="${@withdraw_review_flag}=&apos;Y&apos;  and (  select count(*) from tre_loan_contract_withdraw_v t where t.loan_contract_id=tlc.loan_contract_id and t.status =&apos;NEW&apos;) &gt;0"/>
        <bm:query-field name="repayment_review_flag" queryExpression="${@repayment_review_flag}=&apos;Y&apos; and (  select count(*) from TRE_LOAN_CONTRACT_REPAYMENT t where t.loan_contract_id=tlc.loan_contract_id and t.status =&apos;NEW&apos;) &gt;0"/>
        <bm:query-field name="repayment_reverse_flag" queryExpression="${@repayment_reverse_flag}=&apos;Y&apos;   and (  select count(*) from TRE_LOAN_CONTRACT_REPAYMENT t where t.loan_contract_id=tlc.loan_contract_id ) &gt;0"/>
    </bm:query-fields>
</bm:model>