<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Feng  
    $Date: 2013-9-23 下午8:16:18  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT *
                FROM (SELECT cc.contract_id,
                cc.contract_status,
                cc.contract_name,
                (SELECT pp.project_number
                FROM prj_project pp
                WHERE pp.project_id = cc.project_id) project_number,
                cc.bp_id_tenant,
                TO_CHAR(cc.inception_of_lease, 'yyyy-mm-dd') inception_of_lease,
                (SELECT hb.bp_name
                FROM hls_bp_master hb, con_contract_bp ccb
                WHERE hb.bp_category = 'AGENT'
                AND ccb.bp_category = 'AGENT'
                AND hb.bp_id = ccb.bp_id
                AND ccb.contract_id = cc.contract_id) agent_id_n,
                (SELECT bp_name
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_tenant
                AND enabled_flag = 'Y') bp_id_tenant_desc,
                cc.contract_number,
                cc.bp_id_agent_level1,
                (SELECT bp_name
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_agent_level1
                AND enabled_flag = 'Y') bp_id_agent_desc,
                cc.owner_user_id,
                (SELECT description
                FROM sys_user
                WHERE user_id = cc.owner_user_id) owner_user_id_desc,
                cc.lease_organization,
                cc.lease_times,
                (SELECT description
                FROM hls_lease_organization
                WHERE lease_organization = cc.lease_organization
                AND enabled_flag = 'Y') lease_organization_desc,
                (SELECT a.description value_name
                FROM HLS_BUSINESS_TYPE_V a
                WHERE A.enabled_flag = 'Y'
                AND A.business_type = cc.business_type) business_type_n,
                cc.data_class,
                (select hd.description
                from hls_division hd
                where hd.enabled_flag = 'Y'
                and hd.division = cc.division) division_n,
                cc.division,
                 con_contract_pkg.get_cus_column(p_contract_id => cc.contract_id,
                                               p_type        => 'times') account_due_times, --会计逾期总期数
               con_contract_pkg.get_cus_column(p_contract_id => cc.contract_id,
                                               p_type        => 'days') account_due_days, --会计逾期总天数
               con_contract_pkg.get_cus_column(p_contract_id => cc.contract_id,
                                               p_type        => 'amounts') account_due_amount, --会计逾期总金额
               (select count(1)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.overdue_status = 'Y'
                   and ccc.cf_item = 1
                   and ccc.write_off_flag <> 'FULL') business_due_times, --营业逾期总期数

               (select sum(ccc.overdue_max_days)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.overdue_status = 'Y'
                   and ccc.cf_item = 1
                   and ccc.write_off_flag <> 'FULL') business_due_days, --营业逾期总天数
               (select sum(ccc.due_amount - nvl(ccc.received_amount, 0))
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.overdue_status = 'Y'
                   and ccc.cf_item = 1
                   and ccc.write_off_flag <> 'FULL') business_due_amount, --营业逾期总金额

               (select sum(ccc.due_amount-nvl(ccc.received_amount,0))
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_item =9
                   and ccc.write_off_flag <> 'FULL') over_due_amount
                FROM con_contract cc
                where exists (select 1
                from con_contract_cashflow ccw
                where ccw.contract_id = cc.contract_id
                and ccw.overdue_status = 'Y'
                and ccw.write_off_flag <> 'FULL')
                AND ('Y' = 'Y' AND EXISTS
        (SELECT
          1
           FROM aut_trx_user_authorize a1, aut_owner_user_authorize a2
          WHERE a1.trx_category = 'CONTRACT'
            AND a1.trx_id = cc.contract_id
            AND trunc(SYSDATE) BETWEEN a1.start_date AND
                nvl(a1.end_date, trunc(SYSDATE))
            AND a1.user_id = a2.owner_user_id
            AND a1.trx_category = a2.trx_category
            AND a2.authorized_user_id =${/session/@user_id}
            AND trunc(SYSDATE) BETWEEN a2.start_date AND
                nvl(a2.end_date, trunc(SYSDATE))))) t1 #WHERE_CLAUSE#

                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number"
                        queryExpression="contract_number like upper(&apos;%&apos;||${@contract_number}||&apos;%&apos;)"/>
        <bm:query-field name="bp_id_tenant" queryExpression="t1.bp_id_tenant = ${@bp_id_tenant}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name = ${@contract_name}"/>
        <bm:query-field name="project_number" queryExpression="t1.project_number = ${@project_number}"/>
        <!--<bm:query-field name="bp_id_tenant_desc" queryExpression="t1.bp_id_tenant_desc like ${@bp_id_tenant_desc}"/>-->
        <bm:query-field name="bp_id_agent_desc" queryExpression="t1.bp_id_agent_desc like ${@bp_id_agent_desc}"/>
        <!-- <bm:query-field name="lease_start_date_from" queryExpression="t1.inception_of_lease &gt;=to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="t1.inception_of_lease &lt;=to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        -->
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="lease_start_date_from"
                        queryExpression="t1.inception_of_lease &gt;=${@lease_start_date_from}"/>
        <bm:query-field name="lease_start_date_to"
                        queryExpression="t1.inception_of_lease &lt;=${@lease_start_date_to}"/>
        <bm:query-field name="lease_times_from" queryExpression="t1.lease_times &gt;=${@lease_times_from}"/>
        <bm:query-field name="lease_times_to" queryExpression="t1.lease_times &lt;=${@lease_times_to}"/>
        <bm:query-field name="received_times_from" queryExpression="t1.received_times &gt;=${@received_times_from}"/>
        <bm:query-field name="received_times_to" queryExpression="t1.received_times &lt;=${@received_times_to}"/>
        <bm:query-field name="agent_id_n" queryExpression="t1.agent_id_n = ${@agent_id_n}"/>
        <bm:query-field name="promised_return_date_from"
                        queryExpression="t1.promised_return_date &gt;= to_date(${@promised_return_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="promised_return_date_to"
                        queryExpression="t1.promised_return_date &lt;= to_date(${@promised_return_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="account_due_times_from" queryExpression="t1.account_due_times &gt;= ${@account_due_times_from}"/>
        <bm:query-field name="account_due_times_to" queryExpression="t1.account_due_times &lt;= ${@account_due_times_to}"/>
        <bm:query-field name="account_due_days_from" queryExpression="t1.account_due_days &gt;= ${@account_due_days_from}"/>
        <bm:query-field name="account_due_days_to" queryExpression="t1.account_due_days &lt;= ${@account_due_days_to}"/>
        <bm:query-field name="account_due_amount_from" queryExpression="t1.account_due_amount &gt;= ${@account_due_amount_from}"/>
        <bm:query-field name="account_due_amount_to" queryExpression="t1.account_due_amount &lt;= ${@account_due_amount_to}"/>
        <bm:query-field name="business_due_times_from" queryExpression="t1.business_due_times &gt;= ${@business_due_times_from}"/>
        <bm:query-field name="business_due_times_to" queryExpression="t1.business_due_times &lt;= ${@business_due_times_to}"/>
        <bm:query-field name="business_due_days_from" queryExpression="t1.business_due_days &gt;= ${@business_due_days_from}"/>
        <bm:query-field name="business_due_days_to" queryExpression="t1.business_due_days &lt;= ${@business_due_days_to}"/>
        <bm:query-field name="business_due_amount_from" queryExpression="t1.business_due_amount &gt;= ${@business_due_amount_from}"/>
        <bm:query-field name="business_due_amount_to" queryExpression="t1.business_due_amount &lt;= ${@business_due_amount_to}"/>
        <bm:query-field name="owner_user_id" queryExpression="t1.owner_user_id = ${@owner_user_id}"/>
        <bm:query-field name="lease_organization" queryExpression="t1.lease_organization = ${@lease_organization}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="contract_status" enforceOperations="query"
                        expression="t1.contract_status = &apos;INCEPT&apos;"/>
        <!-- <bm:data-filter name="overdue_days" enforceOperations="query" expression="t1.overdue_days &gt; 0"/> -->
        <!-- <bm:data-filter enforceOperations="query" expression="exists (select 1 from con_contract_cashflow where contract_id = t1.contract_id and cf_type = 9 and due_amount != nvl(received_amount,0) and generated_source not in (&apos;CREATE&apos;))"/> -->
        <bm:data-filter enforceOperations="query" expression="t1.data_class = &apos;NORMAL&apos;"/>
        <!--<bm:data-filter enforceOperations="query" expression="exists (select 1 from con_contract where contract_id = t1.contract_id and created_by = ${/session/@user_id})"/>-->
    </bm:data-filters>
</bm:model>
