<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: lgc5900  
    $Date: 2015年11月25日 下午5:41:00  
    $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
                    PB.BP_NAME,
                    PJ.VIRTUAL_CON_NUMBER AS PROJECT_NUMBER,
                    --法定代表人
                    (
                    SELECT
                        HO.CONTACT_PERSON
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.BP_ID    = PB.BP_ID AND
                        HO.POSITION = 'LP'
                    ) LEGAL_PERSON,
                    --取邮寄地址
                    (
                    SELECT
                        CASE
                            WHEN INSTR(HS.ADDRESS, '市') > 0
                            THEN HS.ADDRESS
                            WHEN HS.PROVINCE_ID_N = HS.CITY_ID_N
                            THEN HS.CITY_ID_N
                                || HS.DISTRICT_ID_N
                                || HS.ADDRESS
                            ELSE HS.PROVINCE_ID_N
                                || HS.CITY_ID_N
                                || HS.DISTRICT_ID_N
                                || HS.ADDRESS
                        END
                    FROM
                        HLS_BP_MASTER_ADDRESS_LV HS
                    WHERE
                        HS.BP_ID        = PB.BP_ID AND
                        HS.ADDRESS_TYPE = 'DOC_SENT_ADDRESS' AND
                        ROWNUM          = 1
                    ) ADDRESS,
                    --邮编
                    (
                    SELECT
                        HS.ZIPCODE
                    FROM
                        HLS_BP_MASTER_ADDRESS_LV HS
                    WHERE
                        HS.BP_ID        = PB.BP_ID AND
                        HS.ADDRESS_TYPE = 'DOC_SENT_ADDRESS' AND
                        ROWNUM          = 1
                    ) ZIPCODE,
                    --联系人
                    (
                    SELECT
                        HO.CONTACT_PERSON
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.BP_ID    = PB.BP_ID AND
                        ROWNUM      = 1 AND
                        HO.POSITION = 'CP'
                    ) CP_PERSON,
                    (SELECT
                        HO.PHONE
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.BP_ID    = PB.BP_ID AND
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1
                    ) CP_PHONE,
                    (SELECT
                        HO.FAX
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.BP_ID    = PB.BP_ID AND
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1
                    ) CP_FAX,
                    REPLACE(CHANGE_NUMBER_TO_RMB(PJ.FINANCE_AMOUNT), '圆整', '') FINANCE_AMOUNT_RMB,
                    TO_CHAR(PJ.FINANCE_AMOUNT, 'FM999,999,999,990.00') FINANCE_AMOUNT,
                    PJ.LEASE_TERM,
                    SUBSTR(HD.INCEPTION_OF_LEASE, 1, 4)
                    || '年'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 6, 2)
                    || '月'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 9, 2)
                    || '日' AS LEASE_START_DATE,
                    (SELECT
                        SUBSTR(MAX(LN.DUE_DATE), 1, 4)
                        || '年'
                        || SUBSTR(MAX(LN.DUE_DATE), 6, 2)
                        || '月'
                        || SUBSTR(MAX(LN.DUE_DATE), 9, 2)
                        || '日'
                    FROM
                        HLS_FIN_CALCULATOR_LN LN
                    WHERE
                        LN.CALC_SESSION_ID = HD.CALC_SESSION_ID
                    ) AS LEASE_END_DATE,
                    --商业伙伴修改之后取值
                    (
                    SELECT
                        HT.BANK_ACCOUNT_NAME
                    FROM
                        PRJ_PROJECT_BP PB,
                        HLS_BP_MASTER_BANK_ACCOUNT HT
                    WHERE
                        PB.BP_ID                  = PJ.BP_ID_TENANT AND
                        PB.PROJECT_ID             = PJ.PROJECT_ID AND
                        PB.PRJ_BP_BANK_ACCOUNT_ID = HT.BANK_ACCOUNT_ID
                    ) COM_BANK_ACCOUNT_NAME,
                    (SELECT
                        HT.BANK_ACCOUNT_NUM
                    FROM
                        PRJ_PROJECT_BP PB,
                        HLS_BP_MASTER_BANK_ACCOUNT HT
                    WHERE
                        PB.BP_ID                  = PJ.BP_ID_TENANT AND
                        PB.PROJECT_ID             = PJ.PROJECT_ID AND
                        PB.PRJ_BP_BANK_ACCOUNT_ID = HT.BANK_ACCOUNT_ID
                    ) COM_BANK_ACCOUNT_NUM,
                    (SELECT
                        HT.BANK_BRANCH_NAME
                    FROM
                        PRJ_PROJECT_BP PB,
                        HLS_BP_MASTER_BANK_ACCOUNT HT
                    WHERE
                        PB.BP_ID                  = PJ.BP_ID_TENANT AND
                        PB.PROJECT_ID             = PJ.PROJECT_ID AND
                        PB.PRJ_BP_BANK_ACCOUNT_ID = HT.BANK_ACCOUNT_ID
                    ) COM_BANK_BRANCH_NAME,
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.LEASE_ITEM_AMOUNT), '圆整', '') LEASE_ITEM_AMOUNT_CN,
                    TO_CHAR(HD.LEASE_ITEM_AMOUNT, 'FM999,999,999,990.00') LEASE_ITEM_AMOUNT,
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.LEASE_CHARGE), '圆整', '') LEASE_CHARGE_CN,
                    TO_CHAR(HD.LEASE_CHARGE, 'FM999,999,999,990.00') LEASE_CHARGE,
                    --不含税手续费
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.NET_LEASE_CHARGE), '圆整', '') NET_LEASE_CHARGE_RMB,
                    TO_CHAR(HD.NET_LEASE_CHARGE, 'FM999,999,999,990.00') NET_LEASE_CHARGE,
                    --手续费-增值税
                    TO_CHAR(HD.LEASE_CHARGE / 1.17 * 0.17, 'FM999,999,999,990.00') LEASE_CHARGE_VAT,
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.LEASE_CHARGE_RATIO), '圆整', '') LEASE_CHARGE_RATIO_CN,
                    TO_CHAR((NVL(HD.NET_LEASE_CHARGE, 0) / PJ.FINANCE_AMOUNT * 100), 'FM999,999,990.00')
                    || '%' LEASE_CHARGE_RATIO,
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.VAT_TOTAL_RENTAL), '圆整', '') VAT_TOTAL_RENTAL_CN,
                    TO_CHAR(HD.VAT_TOTAL_RENTAL, 'FM999,999,999,990.00') VAT_TOTAL_RENTAL,
                    PB.REGISTERED_PLACE,
                    PB.PHONE,
                    PB.TAX_REGISTRY_NUM,
                    PB.EMAIL,
                    PB.FAX_NUMBER,
                    PJ.LEASE_ITEM_SHORT_NAME,
                    HD.INT_RATE * 100 AS INT_RATE,
                    DECODE(HD.PAY_TYPE, '1', '先付', '0', '后付') AS PAY_TYPE,
                    TO_CHAR(HD.DEPOSIT, 'FM999,999,999,990.00') DEPOSIT,
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.RESIDUAL_VALUE), '圆整', '') RESIDUAL_VALUE_CN,
                    TO_CHAR(HD.RESIDUAL_VALUE, 'FM999,999,999,990.00') RESIDUAL_VALUE,
                    --租前金额
                    REPLACE(CHANGE_NUMBER_TO_RMB(HD.INTERIM_RENTAL), '圆整', '') INTERIM_RENTAL_CN,
                    TO_CHAR(HD.INTERIM_RENTAL, 'FM999,999,999,990.00') INTERIM_RENTAL,
                    SUBSTR(HD.INCEPTION_OF_LEASE, 1, 4)
                    || '年'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 6, 2)
                    || '月'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 9, 2)
                    || '日' AS INCEPTION_OF_LEASE,
                    --年期
                    /* (
                    SELECT
                    TO_CHAR(fe.months_from / 12, 'FM999,990.0')
                    || '-'
                    || TO_CHAR(fe.months_to / 12, 'FM999,990.0')
                    FROM
                    fnd_base_rate_set ft,
                    fnd_base_rate fe
                    WHERE
                    ft.base_rate_type = 'PBOC' AND
                    ft.currency       = 'CNY' AND
                    ft.enabled_flag   = 'Y' AND
                    ft.base_rate_set  = fe.base_rate_set AND
                    fe.months_from    < 12 * pj.lease_term AND
                    fe.months_to     >= 12 * pj.lease_term
                    ) description, */
                    (
                        CASE
                            WHEN 12 * PJ.LEASE_TERM <= 6
                            THEN '六个月以内（含6个月）'
                            WHEN 12 * PJ.LEASE_TERM > 6 AND
                                12 * PJ.LEASE_TERM <= 12
                            THEN '六个月至一年（含1年）'
                            WHEN 12 * PJ.LEASE_TERM > 12 AND
                                12 * PJ.LEASE_TERM <= 36
                            THEN '一至三年（含3年）'
                            WHEN 12 * PJ.LEASE_TERM > 36 AND
                                12 * PJ.LEASE_TERM <= 60
                            THEN '三至五年（含5年）'
                            ELSE '五年以上'
                        END) DESCRIPTION,
                    --调幅方式
                    (
                    SELECT
                        SL.CODE_VALUE_NAME
                    FROM
                        SYS_CODE_VALUES_V SL
                    WHERE
                        SL.CODE              = 'TRE502_INT_RATE_FIXING_WAY' AND
                        SL.CODE_ENABLED_FLAG = 'Y' AND
                        SL.CODE_VALUE        = HD.INT_RATE_FIXING_WAY
                    ) INT_RATE_FIXING_WAY,
                    --基准利率
                    TO_CHAR(HD.INT_RATE_FIXING_RANGE * 100, 'FM999,999,990.00') AS BASE_RATE,
                    PB.INVOICE_BP_ADDRESS_PHONE_NUM,
                    PB.INVOICE_PHONE_NUMBER,
                    --税号
                    PB.INVOICE_BANK,
                    PB.INVOICE_BP_BANK_ACCOUNT,
                    (SELECT
                        E.NAME
                    FROM
                        EXP_EMPLOYEES E
                    WHERE
                        E.EMPLOYEE_ID = PJ.EMPLOYEE_ID
                    ) EMPLOYEE_NAME,
                    --联系人类型
                    (
                    SELECT
                        HO.CONTACT_PERSON
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1 AND
                        HO.BP_ID    = PB.BP_ID
                    ) CONTACT_PERSON_CP,
                    (SELECT
                        HO.CONTACT_INFO
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1 AND
                        HO.BP_ID    = PB.BP_ID
                    ) CONTACT_INFO_CP,
                    (SELECT
                        HO.EMAIL
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1 AND
                        HO.BP_ID    = PB.BP_ID
                    ) EMAIL_CP,
                    --联系人类型的固定电话
                    (
                    SELECT
                        HO.PHONE
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1 AND
                        HO.BP_ID    = PB.BP_ID
                    ) CELL_PHONE_CP,
                    (SELECT
                        HO.FAX
                    FROM
                        HLS_BP_MASTER_CONTACT_INFO HO
                    WHERE
                        HO.POSITION = 'CP' AND
                        ROWNUM      = 1 AND
                        HO.BP_ID    = PB.BP_ID
                    ) FAX_CP,
                    --担保人
                    SDIC_VIRTUAL_CON_CONTENT_PKG.GET_VIRTUAL_CON_GUA_NAME(PJ.PROJECT_ID) GUARANTOR_NAME,
                    --预计放款日
                    (SUBSTR(HD.INCEPTION_OF_LEASE, 1, 4)
                    || '年'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 6, 2)
                    || '月'
                    || SUBSTR(HD.INCEPTION_OF_LEASE, 9, 2)
                    || '日') INCEPTION_OF_LEASE_ACTUALLY,
                    (TO_CHAR(HD.LEASE_ITEM_AMOUNT, 'FM999,999,999,990.00')) AS LEASE_ITEM_AMOUNT_FORMAT_MONEY,
                    (SELECT
                        (SELECT
                            COUNT(1) + 1
                        FROM
                            PRJ_QUOTATION Q1,
                            CON_CONTRACT CC
                        WHERE
                            Q1.WFL_STATUS            = 'APPROVED' AND
                            Q1.DOCUMENT_CATEGORY     = Q.DOCUMENT_CATEGORY AND
                            Q1.DOCUMENT_ID           = Q.DOCUMENT_ID AND
                            Q1.REF_DOCUMENT_ID       = CC.CONTRACT_ID AND
                            Q1.REF_DOCUMENT_CATEGORY = 'CONTRACT' AND
                            EXISTS
                            (SELECT
                                1
                            FROM
                                CON_CONTRACT CC1
                            WHERE
                                CC1.CONTRACT_ID         = Q.REF_DOCUMENT_ID AND
                                Q.REF_DOCUMENT_CATEGORY = 'CONTRACT' AND
                                CC1.CREATION_DATE       > CC.CREATION_DATE
                            )
                        )
                    FROM
                        PRJ_QUOTATION Q
                    WHERE
                        Q.DOCUMENT_CATEGORY = 'PROJECT' AND
                        Q.QUOTATION_ID      = PN.QUOTATION_ID
                    ) AS LOAN_ORDER
                FROM
                    PRJ_PROJECT PJ,
                    HLS_BP_MASTER PB,
                    FND_COMPANIES FS,
                    PRJ_QUOTATION PN,
                    HLS_FIN_CALCULATOR_HD HD
                WHERE
                    PJ.BP_ID_TENANT      = PB.BP_ID AND
                    FS.COMPANY_ID        = PJ.COMPANY_ID AND
                    PJ.PROJECT_ID        = PN.DOCUMENT_ID AND
                    PN.DOCUMENT_CATEGORY = 'PROJECT' AND
                    --PN.QUOTATION_TYPE    = 'MAJOR' AND
                    PN.CALC_SESSION_ID = HD.CALC_SESSION_ID AND
                    PJ.PROJECT_ID      = ${@project_id} 
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME"/>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NUMBER" required="true"/>
        <bm:field name="legal_person" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEGAL_PERSON"/>
        <bm:field name="address" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ADDRESS"/>
        <bm:field name="zipcode"/>
        <bm:field name="finance_amount"/>
        <bm:field name="lease_term" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_TERM"/>
        <bm:field name="lease_start_date"/>
        <bm:field name="com_bank_account_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COM_BANK_ACCOUNT_NAME"/>
        <bm:field name="com_bank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COM_BANK_ACCOUNT_NUM"/>
        <bm:field name="com_bank_branch_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COM_BANK_BRANCH_NAME"/>
        <bm:field name="lease_item_amount" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_ITEM_AMOUNT"/>
        <bm:field name="lease_charge" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHARGE"/>
        <bm:field name="lease_charge_ratio"/>
        <bm:field name="vat_total_rental" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="VAT_TOTAL_RENTAL"/>
        <bm:field name="registered_place" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REGISTERED_PLACE"/>
        <bm:field name="phone" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PHONE"/>
        <bm:field name="tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TAX_REGISTRY_NUM"/>
        <bm:field name="email" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EMAIL"/>
        <bm:field name="fax_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FAX_NUMBER"/>
        <bm:field name="lease_item_short_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_ITEM_SHORT_NAME"/>
        <bm:field name="int_rate" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INT_RATE"/>
        <bm:field name="pay_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PAY_TYPE"/>
        <bm:field name="deposit" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DEPOSIT"/>
        <bm:field name="residual_value" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="RESIDUAL_VALUE"/>
        <bm:field name="interim_rental"/>
        <bm:field name="inception_of_lease"/>
        <bm:field name="description"/>
        <bm:field name="int_rate_fixing_way"/>
        <bm:field name="base_rate"/>
        <bm:field name="invoice_bp_address_phone_num"/>
        <bm:field name="invoice_phone_number"/>
        <bm:field name="invoice_bank"/>
        <bm:field name="invoice_bp_bank_account"/>
        <bm:field name="cp_person"/>
        <bm:field name="cp_phone"/>
        <bm:field name="cp_fax"/>
        <bm:field name="finance_amount_rmb"/>
        <bm:field name="lease_item_amount_cn"/>
        <bm:field name="lease_charge_cn"/>
        <bm:field name="lease_charge_ratio_cn"/>
        <bm:field name="vat_total_rental_cn"/>
        <bm:field name="residual_value_cn"/>
        <bm:field name="interim_rental_cn"/>
        <bm:field name="employee_name"/>
        <bm:field name="contact_person_cp"/>
        <bm:field name="contact_info_cp"/>
        <bm:field name="email_cp"/>
        <bm:field name="cell_phone_cp"/>
        <bm:field name="fax_cp"/>
        <bm:field name="lease_end_date"/>
        <bm:field name="guarantor_name"/>
        <bm:field name="lease_charge_vat"/>
        <bm:field name="net_lease_charge_rmb"/>
        <bm:field name="net_lease_charge"/>
        <bm:field name="inception_of_lease_actually"/>
        <bm:field name="lease_item_amount_format_money"/>
        <bm:field name="loan_order"/>
    </bm:fields>
</bm:model>
