<?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 >= to_date(${@due_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_to" queryExpression="t1.due_date <= to_date(${@due_date_to},'yyyy-mm-dd')"/>
<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 ('NONCASH','OUTFLOW')"/>
<bm:data-filter enforceOperations="query" expression="t1.due_amount >0"/>
</bm:data-filters>
</bm:model>