<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: lgc5900  
    $Date: 2015年12月24日 下午6:02:36  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.loan_contract_id,
                        T1.ORIGINAL_CON_NUM,
                        t1.LOAN_CON_TYPE_DETAIL,
                        (select 
                        v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'LOAN_CON_FIN_WAY'
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           AND V.code_value=T1.LOAN_CON_TYPE_DETAIL)  LOAN_CON_TYPE_DETAIL_N,
                        rank() over(order by tw.widthdrawal_date, t1.loan_contract_id) OBLIGATION_SEQ,
                        t1.loan_con_bp,
                        (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = t1.loan_con_bp
                        ) loan_con_bp_n,
                        t1.loan_contract_number,
                        --借款合同金额
                        t1.loan_amount,
                        t1.currency,
                        (SELECT
                            A.CURRENCY_NAME
                        FROM
                            GLD_CURRENCY_VL A
                        WHERE
                            A.CURRENCY_CODE = T1.CURRENCY
                        ) currency_n,
                        (SELECT
                            TO_date(hd.INCEPTION_OF_LEASE, 'yyyy-mm-dd')
                        FROM
                            prj_quotation q,
                            hls_fin_calculator_hd hd
                        WHERE
                            q.quotation_id    = tw.quotation_id AND
                            q.calc_session_id = hd.calc_session_id
                        ) withdrawal_date,
                        (SELECT
                            MAX(p.repayment_date)
                        FROM
                            tre_loan_con_repayment_plan p
                        WHERE
                            p.loan_contract_id = t1.loan_contract_id AND
                            p.quotation_id     = tw.quotation_id
                        ) last_repayment_date,
                        --提款金额
                        TW.widthdrawal_plan_id,
                        tw.amount finance_amount,
                        tw.quotation_id,
                        t1.document_category,
                        t1.document_type,
                        ( sdic_tre_loan_contract_pkg.get_withdraw_received_amount(tw.quotation_id,NVL(to_date(${@query_date},'YYYY/MM/DD' ),sysdate)) ) recived_amount,
                        (
                            CASE
                                WHEN sdic_tre_loan_contract_pkg.get_withdraw_noreceived_amount(tw.quotation_id,sysdate) > 0
                                THEN '使用'
                                ELSE '已结清'
                            END) status,
                        tw.widthdrawal_date,
                        ( sdic_tre_loan_contract_pkg.get_withdraw_noreceived_amount(tw.quotation_id,NVL(to_date(${@query_date},'YYYY/MM/DD' ),sysdate)) ) remaining_amount
                    FROM
                        tre_loan_con_withdrawal_plan tw,
                        tre_loan_contract t1
                    WHERE
                        EXISTS
                        (SELECT
                            1
                        FROM
                            prj_quotation q
                        WHERE
                            q.quotation_id                  = tw.quotation_id AND
                            NVL(q.main_contract_flag, 'N') != 'Y' AND
                            NVL(q.reverse_flag,'N')        <>'Y' AND
                            NVL(q.review_flag,'N')          ='Y'
                        ) AND
                        tw.loan_contract_id = t1.loan_contract_id AND
                        t1.data_class       = 'NORMAL'
                    )tt #WHERE_CLAUSE#
                ORDER BY
                    tt.widthdrawal_date,
                    tt.loan_contract_id
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="loan_contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LOAN_CONTRACT_ID"/>
        <bm:field name="obligation_seq" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OBLIGATION_SEQ"/>
        <bm:field name="loan_con_bp" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LOAN_CON_BP"/>
        <bm:field name="loan_con_bp_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CON_BP_N"/>
        <bm:field name="loan_contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LOAN_CONTRACT_NUMBER"/>
        <bm:field name="loan_amount" databaseType="NUMBER" datatype="java.lang.Long" physicalName="LOAN_AMOUNT"/>
        <bm:field name="currency" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY"/>
        <bm:field name="currency_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_N"/>
        <bm:field name="remaining_amount"/>
        <bm:field name="withdrawal_date" databaseType="DATE" datatype="java.util.Date" physicalName="WITHDRAWAL_DATE"/>
        <bm:field name="last_repayment_date" databaseType="DATE" datatype="java.util.Date" physicalName="LAST_REPAYMENT_DATE"/>
        <bm:field name="finance_amount" databaseType="NUMBER" datatype="java.lang.Long" physicalName="FINANCE_AMOUNT"/>
        <bm:field name="quotation_id"/>
        <bm:field name="recived_amount"/>
        <bm:field name="status"/>
        <bm:field name="document_category"/>
        <bm:field name="document_type"/>
        <bm:field name="widthdrawal_date"/>
        <bm:field name="widthdrawal_plan_id"/>
        <bm:field name="original_con_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="loan_con_type_detail_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="loan_con_type_detail" databaseType="VARCHAR2" datatype="java.lang.String"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="loan_con_bp" queryExpression="tt.loan_con_bp=${@loan_con_bp}"/>
        <bm:query-field name="loan_contract_number" queryExpression="tt.loan_contract_number=${@loan_contract_number}"/>
        <bm:query-field name="widthdrawal_date_from" queryExpression="to_char(tt.withdrawal_date,&apos;YYYY-MM-DD&apos;)&gt;=${@widthdrawal_date_from}"/>
        <bm:query-field name="widthdrawal_date_to" queryExpression="to_char(tt.withdrawal_date,&apos;YYYY-MM-DD&apos;)&lt;=${@widthdrawal_date_to}"/>
        <bm:query-field name="last_repayment_from" queryExpression="to_char(tt.last_repayment_date,&apos;YYYY-MM-DD&apos;)&gt;=${@last_repayment_from}"/>
        <bm:query-field name="last_repayment_to" queryExpression="to_char(tt.last_repayment_date,&apos;YYYY-MM-DD&apos;)&lt;=${@last_repayment_to}"/>
        <bm:query-field name="status" queryExpression="tt.status like &apos;%&apos;||${@status}||&apos;%&apos;"/>
        <bm:query-field name="currency_n" queryExpression="tt.currency_n=${@currency_n}"/>
        <bm:query-field name="loan_con_type_detail" queryExpression="tt.LOAN_CON_TYPE_DETAIL_N=${@loan_con_type_detail}"/>
    </bm:query-fields>
</bm:model>
