csh_accounts_receivable.lwm

<?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 bp_name,
                        cc.times,
                        cc.cf_item,
                        (select hci.description from hls_cashflow_item hci
                        where hci.cf_item=cc.cf_item) cf_item_n,
                        cc.due_date,
                        cc.calc_date,
                        (select max(ccc.due_date) from con_contract_cashflow ccc
       where ccc.contract_id = cc.contract_id
       and ccc.cf_item=1
       and ccc.cf_direction='INFLOW'
       and ccc.cf_status='RELEASE') lease_end_date,
       cc.outstanding_prin_tax_incld,
               cc.outstanding_rental_tax_incld,
                        cc.due_amount,
                        cc.net_due_amount,
                        cc.vat_due_amount,
                        cc.principal,
                        cc.net_principal,
                        cc.vat_principal,
                        cc.interest,
                        cc.vat_interest,
                        cc.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
                            i.email
                        FROM
                            hls_bp_master_contact_info i
                        WHERE
                            i.position = 'CP' AND
                            i.bp_id    = m.bp_id AND
                            rownum     = 1
                        ) email,
                        (SELECT
                            i.contact_person
                        FROM
                            hls_bp_master_contact_info i
                        WHERE
                            i.position = 'FM' AND
                            i.bp_id    = m.bp_id AND
                            rownum     = 1
                        ) contact_person_fm,
                        (SELECT
                            i.cell_phone
                        FROM
                            hls_bp_master_contact_info i
                        WHERE
                            i.position = 'FM' AND
                            i.bp_id    = m.bp_id AND
                            rownum     = 1
                        ) cell_phone_fm,
                        (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,
                        (SELECT
                            v.zipcode
                        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'
                        ) zipcode,
                        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.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,
                        --增值税
                        cc.ln_user_col_n20,
                        c.bank_account_id,
                        (SELECT
                            ct.bank_account_num
                        FROM
                            csh_bank_account ct
                        WHERE
                            ct.bank_account_id = c.bank_account_id
                        ) bank_account_id_n,
                        SUBSTR(c.contract_number, 1, INSTR(c.contract_number, '-') - 1) contract_number_substr,
                        c.hn_industry_classification,
                        (SELECT
                            vv.code_value_name
                        FROM
                            sys_code_values_v vv
                        WHERE
                            vv.code       = 'HN_INDUSTRY_CLASSIFICATION' AND
                            vv.code_value = c.hn_industry_classification
                        ) hn_industry_classification_n,
                        (SELECT
                            ccc.due_amount
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id = c.contract_id AND
                            ccc.cf_item     = 0 AND
                            ccc.times       = 0
                        ) sum_principal,
                        c.lease_form,
                        (SELECT
                            vv.code_value_name
                        FROM
                            sys_code_values_v vv
                        WHERE
                            vv.code       = 'LEASE_FORM' AND
                            vv.code_value = c.lease_form
                        ) lease_form_n,
                        sdic_report_pkg.get_cashflow_bank_account_num(p_cashflow_id => cc.cashflow_id) bank_account_num,
                        sdic_report_pkg.get_cashflow_bank_name(p_cashflow_id => cc.cashflow_id) bank_name,
                        c.annual_pay_times,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_ANNUAL_PAY_TIMES' AND
                            v.code_value = c.annual_pay_times
                        )
                        || '付' annual_pay_times_n,
                        sdic_report_pkg.get_cashflow_payment_method(p_cashflow_id => cc.cashflow_id) csh_payment_method_n,
                        NVL(
                        (SELECT
                            ccc.net_due_amount
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id = cc.contract_id AND
                            ccc.times       = cc.times AND
                            ccc.cf_item     = 3
                        ), 0) net_lease_charge,
                        NVL(
                        (SELECT
                            ccc.vat_due_amount
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id = cc.contract_id AND
                            ccc.times       = cc.times AND
                            ccc.cf_item     = 3
                        ), 0) vat_lease_charge,
                        NVL(
                        (SELECT
                            ccc.due_amount
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id = cc.contract_id AND
                            ccc.times       = cc.times AND
                            ccc.cf_item     = 9
                        ), 0) penalty_amount,
                        c.adjust_rate_type,
                        (SELECT
                            ar.code_value_name
                        FROM
                            sys_code_values_v ar
                        WHERE
                            ar.code       = 'ADJUST_RATE_TYPE' AND
                            ar.code_value = c.adjust_rate_type
                        ) adjust_rate_type_n,
                        c.flt_next_adj_date,
                        sdic_report_pkg.get_con_cashflow_adjust_flag(p_cashflow_id => cc.cashflow_id, p_type => 1) adjust_flag,
                        (SELECT
                            ar.code_value_name
                        FROM
                            sys_code_values_v ar
                        WHERE
                            ar.code       = 'YES_NO' AND
                            ar.code_value = sdic_report_pkg.get_con_cashflow_adjust_flag(p_cashflow_id => cc.cashflow_id, p_type => 1)
                        ) adjust_flag_n,
                        cc.full_write_off_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 IN ('INCEPT', 'SIGN', 'ET', 'TERMINATE') AND
                        nvl(c.hn_adjust_review_flag,'N') = 'Y' AND
                        c.contract_id     IN
                        (SELECT
                            t.contract_id
                        FROM
                            csh_write_off t
                        WHERE
                            t.write_off_type          = 'PAYMENT_DEBT' AND
                            NVL(t.reversed_flag, 'N') = 'N' AND
                            t.times                   = 0 AND
                            t.cf_type                 = 0 AND
                            t.cf_item                 = 0
                        ) AND
                        c.employee_id = e.employee_id(+)
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    due_date,
                    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" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="calc_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="contact_person" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <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" databaseType="NUMBER" datatype="java.lang.Long"/>
        <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="net_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="net_due_amount"/>
        <bm:field name="vat_due_amount"/>
        <bm:field name="bank_account_id_n"/>
        <bm:field name="bank_account_id"/>
        <bm:field name="contract_number_substr"/>
        <bm:field name="net_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vat_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vat_interest" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="email"/>
        <bm:field name="contact_person_fm"/>
        <bm:field name="cell_phone_fm"/>
        <bm:field name="zipcode"/>
        <bm:field name="hn_industry_classification"/>
        <bm:field name="hn_industry_classification_n"/>
        <bm:field name="sum_principal" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_form"/>
        <bm:field name="lease_form_n"/>
        <bm:field name="bank_account_num"/>
        <bm:field name="bank_name"/>
        <bm:field name="annual_pay_times"/>
        <bm:field name="annual_pay_times_n"/>
        <bm:field name="csh_payment_method_n"/>
        <bm:field name="net_lease_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="vat_lease_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="penalty_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="adjust_rate_type"/>
        <bm:field name="adjust_rate_type_n"/>
        <bm:field name="flt_next_adj_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="adjust_flag"/>
        <bm:field name="adjust_flag_n"/>
        <bm:field name="cf_item_n"/>
        <bm:field name="lease_end_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="outstanding_prin_tax_incld" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="outstanding_rental_tax_incld" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="full_write_off_date" databaseType="DATE" datatype="java.util.Date"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="due_date_from" queryExpression="t1.due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="t1.due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="cf_item" queryExpression="t1.cf_item =${@cf_item}"/>
        <bm:query-field name="employee_id" queryExpression="t1.employee_id =${@employee_id}"/>
        <bm:query-field name="lease_organization" queryExpression="t1.lease_organization =${@lease_organization}"/>
        <bm:query-field name="write_off_flag" queryExpression="t1.write_off_flag=${@write_off_flag}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.cf_direction not in (&apos;NONCASH&apos;,&apos;OUTFLOW&apos;)"/>
        <bm:data-filter enforceOperations="query" expression="t1.due_amount &gt;0"/>
    </bm:data-filters>
</bm:model>