<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hc  
    $Date: 2015-10-9 下午05:04:37  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            
        select  rownum AS row_num,t1.*,
        nvl(lease_charge,0)+nvl(lease_mgt_fee,0) lease_charge_mgt_fee,
        nvl(received_lease_charge,0)+nvl(received_lease_mgt_fee,0) received_lease_charge_mgt_fee,
        nvl(lease_charge, 0) - nvl(received_lease_charge, 0) + nvl(lease_mgt_fee, 0) - nvl(received_lease_mgt_fee, 0) unreceived_total_amount,
  		nvl(total_rental,0)-nvl(total_received,0) unreceived_totle_rental
  from (select p.project_id,
               p.project_number,
               p.project_name,
               ct.contract_id,
               ct.contract_number,
               ct.contract_name,
               ct.finance_amount,
               ct.deposit,
               ct.residual_ratio,
                nvl((select ccc.due_amount from con_contract_cashflow ccc where ccc.contract_id=ct.contract_id
               and ccc.cf_item=8),0) residual_value,
               nvl((select ccc.net_due_amount from con_contract_cashflow ccc where ccc.contract_id=ct.contract_id
               and ccc.cf_item=8),0) hd_user_col_n03 ,
               ct.other_fee2,
               ct.price_list_n,
               (select c.description
		          from hls_fin_calc_type c ,HLS_PRICE_LIST l
		         where ct.price_list = l.price_list and c.calc_type =l.calc_type) AS calc_type_display,
               add_months(ct.lease_start_date,ct.lease_term*12 ) lease_due_end_date,
               sys_code_pkg.get_sys_code_value_name('HLS500_DEPOSIT_DEDUCTION',ct.deposit_deduction) deposit_deduction_n,
               (round(ct.int_rate,6)*100)||'%' int_rate_n,
               (round(ct.irr,6)*100)||'%' irr_n,
               ct.document_category,
               ct.document_category_desc,
               ct.bp_id_tenant,
               ct.lease_times,
               (select l.zipcode from hls_bp_master_address_lv l where l.address_type ='DOC_SENT_ADDRESS' and l.bp_id =ct.bp_id_tenant) zipcode,
               (select l.address from hls_bp_master_address_lv l where l.address_type ='DOC_SENT_ADDRESS' and l.bp_id =ct.bp_id_tenant) address,
               (select m.bp_code from hls_bp_master m where m.bp_id=ct.bp_id_tenant)bp_code,
               ct.lease_start_date,
               ct.bp_name,
               (select nvl(sum(nvl(c.due_amount,0)),0)
                  from con_contract_cashflow c
                 where c.contract_id = ct.contract_id
                   and c.cf_item = 3
                   and c.cf_status in ('BLOCK', 'RELEASE')
                   and c.cf_direction !='NONCASH'
                   ) lease_charge,
                (select sum(nvl(c.received_amount,0))
                  from con_contract_cashflow c
                 where c.contract_id = ct.contract_id
                   and c.cf_item = 3
                   and c.cf_status in ('BLOCK', 'RELEASE')
                   and c.cf_direction !='NONCASH'
                   ) received_lease_charge,
               (select sum(nvl(c.due_amount,0))
                  from con_contract_cashflow c
                 where c.contract_id = ct.contract_id
                   and c.cf_item = 4
                   and c.cf_status in ('BLOCK', 'RELEASE')
                   and c.cf_direction !='NONCASH'
                   ) lease_mgt_fee,
                (select sum(nvl(c.received_amount,0))
                  from con_contract_cashflow c
                 where c.contract_id = ct.contract_id
                   and c.cf_item = 4
                   and c.cf_status in ('BLOCK', 'RELEASE')
                   and c.cf_direction !='NONCASH'
                   ) received_lease_mgt_fee,
                nvl((select sum(nvl(cf.due_amount, 0))
                     from con_contract_cashflow cf
                    where cf.contract_id = ct.contract_id
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK', 'RELEASE')
                      and cf.cf_direction !='NONCASH'),
                   0)  total_rental,
               nvl((select sum(nvl(cf.received_amount, 0))
                     from con_contract_cashflow cf
                    where cf.contract_id = ct.contract_id
                      and cf.cf_item = 1
                      and cf.cf_status in ('BLOCK', 'RELEASE')
                      and cf.cf_direction !='NONCASH'),
                   0) total_received,
               nvl((select sum(nvl(cf2.overdue_amount, 0))
                     from con_contract_cashflow cf2
                    where cf2.overdue_status = 'Y'
                      and cf2.contract_id = ct.contract_id
                      and cf2.cf_item in (1,3,4)
                      and cf2.cf_status in ('BLOCK', 'RELEASE')
                      and cf2.cf_direction ='INFLOW'),
                   0) overdue_amount,
                   
                   (select sum(nvl(c.due_amount,0)-nvl(c.received_amount,0)) 
                   from con_contract_cashflow c 
                   where c.cf_item=9
                   and c.contract_id=ct.contract_id
                   and c.cf_status in ('BLOCK', 'RELEASE')
                   )unreceived_fine_amount,
                   
               ct.company_id,    
               ct.contract_status,
               ct.contract_status_desc,
               ct.lease_organization,
               ct.lease_organization_desc,
               ct.employee_id,
               ct.employee_name,
               ct.division,
               ct.division_desc,
               ct.lease_channel,
               ct.lease_channel_desc,
               ct.document_type,
               ct.document_type_desc,
               ct.con_search_term_1,
               ct.con_search_term_2,
               hbm.industry_big_n,
               hbm.bp_scale_n,
               (select t.province_id_n from hls_bp_master_address_lv t where t.address_type ='COMPANY_ADDRESS' and t.bp_id = ct.bp_id_tenant) province_id_n,
               (select l.email from hls_bp_master_contact_info_lv l where l.position ='CP' and l.bp_id =ct.bp_id_tenant) email,
               (select l.contact_person from hls_bp_master_contact_info_lv l where l.position ='CP' and l.bp_id =ct.bp_id_tenant) contact_person,
               (select l.phone from hls_bp_master_contact_info_lv l where l.position ='CP' and l.bp_id =ct.bp_id_tenant) phone,
               (select l.cell_phone from hls_bp_master_contact_info_lv l where l.position ='CP' and l.bp_id =ct.bp_id_tenant) cell_phone,
               (select l.contact_person from hls_bp_master_contact_info_lv l where l.position ='LP' and l.bp_id =ct.bp_id_tenant) legal_person,
               (select l.phone from hls_bp_master_contact_info_lv l where l.position ='LP' and l.bp_id =ct.bp_id_tenant) legal_phone
          from con_contract_v ct,prj_project p,hls_bp_master_lv hbm
          where ct.project_id=p.project_id(+) 
            AND ct.bp_id_tenant = hbm.bp_id
          order by project_number desc,contract_number desc) t1
			#WHERE_CLAUSE#
          
 
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="row_num" expression="rownum"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="project_number"/>
        <bm:field name="province_id_n"/>
        <bm:field name="bp_scale_n"/>
        <bm:field name="industry_big_n"/>
        <bm:field name="legal_person"/>
        <bm:field name="legal_phone"/>
        <bm:field name="cell_phone"/>
        <bm:field name="phone"/>
        <bm:field name="email"/>
        <bm:field name="project_name"/>
        <bm:field name="contact_person"/>
        <bm:field name="bp_code"/>
        <bm:field name="contract_number"/>
        <bm:field name="document_category"/>
        <bm:field name="document_type"/>
        <bm:field name="document_category_desc"/>
        <bm:field name="document_type_desc"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="lease_start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="lease_due_end_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="int_rate_n"/>
        <bm:field name="deposit_deduction_n"/>
        <bm:field name="irr_n"/>
        <bm:field name="address"/>
        <bm:field name="zipcode"/>
        <bm:field name="calc_type_display"/>
        <bm:field name="bp_name"/>
        <bm:field name="hd_user_col_n03" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="total_received" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="other_fee2" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="finance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="residual_ratio" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="residual_value" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="deposit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="overdue_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="total_rental" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="contract_status_desc"/>
        <bm:field name="lease_times" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="contract_name"/>
        <bm:field name="lease_charge_mgt_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_mgt_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="received_lease_charge_mgt_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="received_lease_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="received_lease_mgt_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="unreceived_total_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="unreceived_totle_rental" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_organization"/>
        <bm:field name="lease_organization_desc"/>
        <bm:field name="employee_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="employee_name"/>
        <bm:field name="division"/>
        <bm:field name="division_desc"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_desc"/>
        <bm:field name="price_list_n"/>
        <bm:field name="con_search_term_1"/>
        <bm:field name="con_search_term_2"/>
        <bm:field name="unreceived_fine_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="lease_start_date_from" queryExpression="t1.lease_start_date &gt;=to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="t1.lease_start_date &lt;=to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="finance_amount_from" queryExpression="t1.finance_amount &gt;= ${@finance_amount_from}"/>
        <bm:query-field name="finance_amount_to" queryExpression="t1.finance_amount &lt;= ${@finance_amount_to} "/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos; || ${@bp_name} || &apos;%&apos;"/>
        <bm:query-field name="project_name" queryExpression=" t1.contract_id in (select c.contract_id from con_contract c,prj_project p where c.project_id=p.project_id  and p.project_name like ${@project_name})"/>
        <bm:query-field field="employee_id" queryOperator="="/>
        <bm:query-field field="document_type" queryOperator="="/>
        <bm:query-field field="lease_organization" queryOperator="="/>
        <bm:query-field field="division" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="lease_channel" queryOperator="="/>
        <bm:query-field field="con_search_term_1" queryOperator="like"/>
        <bm:query-field field="con_search_term_2" queryOperator="like"/>
        <bm:query-field field="contract_name" queryOperator="like"/>
        <bm:query-field name="bp_id" queryExpression="t1.bp_id_tenant=${@bp_id}"/>
        <bm:query-field name="industry_big_n" queryExpression="t1.industry_big_n like ${@industry_big_n}"/>
        <bm:query-field name="province_id_n" queryExpression="t1.province_id_n like ${@province_id_n}"/>
        <bm:query-field name="bp_scale_n" queryExpression="t1.bp_scale_n like ${@bp_scale_n}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.company_id=${/session/@company_id}"/>
    </bm:data-filters>
    <bm:features>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    </bm:features>
</bm:model>
