<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: syj  
    $Date: 2015年7月27日12:04:24  
    $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
                    *
                FROM
                    (SELECT
                        c.contract_id,
                        cc.cashflow_id,
                        c.contract_number,
                        m.bp_name
                        ||
                        (SELECT
                            '、'
                            ||t.bp_name
                        FROM
                            con_contract_bp t
                        WHERE
                            t.bp_type     = 'TENANT_SEC' AND
                            t.contract_id = c.contract_id
                        ) bp_name,
                        cc.times,
                        TO_CHAR(cc.due_date,'YYYY-MM-DD')due_date,
                        TO_CHAR(cc.due_amount,'FM999,999,990.00')due_amount,
                        TO_CHAR(cc.vat_due_amount,'FM999,999,990.00')vat_due_amount,
                        TO_CHAR(cc.principal,'FM999,999,990.00')principal,
                        TO_CHAR(cc.interest,'FM999,999,990.00')interest,
                        TO_CHAR(cc.net_interest,'FM999,999,990.00')net_interest,
                        cc.cf_direction,
                        (SELECT
                            i.contact_person
                        FROM
                            hls_bp_master_contact_info i
                        WHERE
                            i.position = 'CP' AND
                            i.bp_id    = m.bp_id AND
                            rownum     = 1
                        ) contact_person,
                        (SELECT
                            i.cell_phone
                        FROM
                            hls_bp_master_contact_info i
                        WHERE
                            i.position = 'CP' AND
                            i.bp_id    = m.bp_id AND
                            rownum     = 1
                        ) cell_phone,
                        (SELECT
                            v.province_name
                            || v.city_name
                            || v.district_name
                            || v.address
                        FROM
                            hls_bp_master_address_v v
                        WHERE
                            v.bp_id        = c.bp_id_tenant AND
                            v.enabled_flag = 'Y' AND
                            v.address_type = 'DOC_SENT_ADDRESS'
                        ) address,
                        e.employee_id,
                        e.name employee_manager,
                        c.lease_organization,
                        (SELECT
                            o.description
                        FROM
                            hls_lease_organization o
                        WHERE
                            o.lease_organization = c.lease_organization
                        ) lease_organization_desc,
                        cc.cf_item,
                        cc.write_off_flag,
                        (SELECT
                            t1.code_value_name
                        FROM
                            sys_code_values_v t1
                        WHERE
                            t1.code             ='CON_PENALTY_WRITE_OFF_FLAG' AND
                            t1.code_enabled_flag='Y' AND
                            t1.code_value       =cc.write_off_flag
                        )write_off_flag_desc,
                        --增值税
                        TO_CHAR(cc.ln_user_col_n20,'FM999,999,990.00')ln_user_col_n20,
                        '【'
                        ||SUBSTR(TO_CHAR(c.signing_date,'YYYYMMDD'),1,4)
                        ||'】'
                        ||'年'
                        || '【'
                        ||SUBSTR(TO_CHAR(c.signing_date,'YYYYMMDD'),5,2)
                        ||'】'
                        ||'月'
                        || '【'
                        ||SUBSTR(TO_CHAR(c.signing_date,'YYYYMMDD'),7,2)
                        ||'】'
                        ||'日' signing_date,
                        TO_CHAR(outstanding_principal,'FM999,999,999,990.00')outstanding_principal,
                        TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"')sys_date
                    FROM
                        con_contract c,
                        con_contract_cashflow cc,
                        hls_bp_master m,
                        exp_employees e
                    WHERE
                        c.bp_id_tenant   = m.bp_id AND
                        cc.contract_id   = c.contract_id AND
                        c.data_class     = 'NORMAL' AND
                        c.contract_status='INCEPT' AND
                        c.employee_id    = e.employee_id(+)
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    contract_number,
                    times
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="cashflow_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="due_date"/>
        <bm:field name="due_amount"/>
        <bm:field name="contact_person" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="principal"/>
        <bm:field name="interest"/>
        <bm:field name="cell_phone"/>
        <bm:field name="address"/>
        <bm:field name="lease_organization_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="lease_organization" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="cf_item" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="ln_user_col_n20"/>
        <bm:field name="employee_manager"/>
        <bm:field name="cf_direction"/>
        <bm:field name="write_off_flag"/>
        <bm:field name="write_off_flag_desc"/>
        <bm:field name="signing_date"/>
        <bm:field name="outstanding_principal"/>
        <bm:field name="sys_date"/>
        <bm:field name="net_interest"/>
        <bm:field name="vat_due_amount"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="cashflow_id" queryExpression="t1.cashflow_id =${@cashflow_id}"/>
    </bm:query-fields>
</bm:model>
