<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wujun  
    $Date: 2016年11月14日09:10:00
    $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
                        cc.contract_number, --合同编号
                        cc.contract_name,   --项目名称
                        cc.bp_id_tenant,
                        (SELECT yl.bp_name FROM hls_bp_master yl WHERE yl.bp_id = cc.bp_id_tenant
                        ) main_lessee, --主承租人
                        cc.project_id,
                        (sdic_report_pkg.get_prj_bp_name(p_project_id => cc.project_id, p_bp_category => 'GUARANTOR')) guarantor_name, --担保方
                        cc.hn_industry_classification,                                                                                 --行业分类
                        (SELECT
                            scvv.code_value_name
                        FROM
                            sys_code_values_v scvv
                        WHERE
                            scvv.code      IN ('WITHIN_GROUP', 'MARKETIZATION') AND
                            scvv.code_value =
                            (SELECT
                                pp.binary_classification
                            FROM
                                prj_project pp
                            WHERE
                                pp.project_id = cc.project_id
                            )
                        ) binary_classification_n, --行业细分
                        cc.business_type,
                        (SELECT
                            hbtv.description
                        FROM
                            hls_business_type_v hbtv
                        WHERE
                            hbtv.business_type = cc.business_type
                        ) lease_form,          --租赁形式
                        cc.lease_term,         --租赁年限
                        cc.inception_of_lease, --起租日
                        cc.contract_id,
                        (SELECT
                            NVL(ccc.due_amount, 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            ccc.times      = '0' AND
                            ccc.cf_item    = '0'
                        ) principal_amount, --投放本金
                        NVL((
                        (SELECT
                            NVL(ccc.due_amount, 0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            cc.contract_id = ccc.contract_id AND
                            ccc.times      = '0' AND
                            ccc.cf_item    = '0'
                        ) -
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_principal, 0)),0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.contract_id           = cc.contract_id AND
                            cwo.write_off_type        = 'RECEIPT_CREDIT' AND
                            cwo.times                <> '0' AND
                            cwo.cf_item               = '1' AND
                            NVL(cwo.reversed_flag,'N')='N'
                        )),0) surplus_amount, --剩余本金
                        NVL((
                        (SELECT
                            NVL(SUM(NVL(ccc.due_amount, 0)),0)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id   = cc.contract_id AND
                            ccc.cf_direction <> 'NONCASH' AND
                            ccc.cf_item       = '1'
                        ) -
                        (SELECT
                            NVL(SUM(NVL(cwo.write_off_due_amount, '0')),0)
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.contract_id           = cc.contract_id AND
                            cwo.times                <> '0' AND
                            cwo.cf_item               = '1' AND
                            NVL(cwo.reversed_flag,'N')='N'
                        )),0) residual_rent, --剩余租金
                        (SELECT pp.prj_scale FROM prj_project pp WHERE pp.project_id = cc.project_id
                        ) prj_scale, --  项目规模
                        (SELECT
                            pp.region_of_china
                        FROM
                            prj_project pp
                        WHERE
                            pp.project_id = cc.project_id
                        ) region_of_china, --   区域
                        (SELECT
                            pp.project_location
                        FROM
                            prj_project pp
                        WHERE
                            pp.project_id = cc.project_id
                        ) project_location, --  项目所在地
                        cc.employee_id,     --业务经理id
                        (SELECT
                            eeav.unit_name
                        FROM
                            exp_employee_assigns_v eeav
                        WHERE
                            eeav.employee_id  = cc.employee_id AND
                             eeav.primary_position_flag  = 'Y' and
                            eeav.enabled_flag = 'Y'
                        ) department, --业务部门
                        (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                        ) manager --项目经理
                    FROM
                        con_contract cc
                    WHERE
                        cc.data_class                    = 'NORMAL' AND
                        NVL(cc.hn_adjust_review_flag,'N')='Y'
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="hn_industry_classification_n" queryExpression="t1.hn_industry_classification like &apos;%&apos;||${@hn_industry_classification}||&apos;%&apos;"/>
        <!-- <bm:query-field name="principal_amount" queryExpression="t1.principal_amount like &apos;%&apos;||${@principal_amount}||&apos;%&apos;"/> -->
        <bm:query-field name="principal_amount_from" queryExpression="principal_amount &gt;= ${@principal_amount_from}"/>
        <bm:query-field name="principal_amount_to" queryExpression="principal_amount &lt;= ${@principal_amount_to}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="binary_classification_n" queryExpression="t1.binary_classification_n like &apos;%&apos;||${@binary_classification_n}||&apos;%&apos;"/>
        <!-- <bm:query-field name="surplus_amount" queryExpression="t1.surplus_amount like &apos;%&apos;||${@surplus_amount}||&apos;%&apos;"/> -->
        <bm:query-field name="surplus_amount_from" queryExpression="surplus_amount &gt;= ${@surplus_amount_from}"/>
        <bm:query-field name="surplus_amount_to" queryExpression="surplus_amount &lt;= ${@surplus_amount_to}"/>
        <bm:query-field name="main_lessee" queryExpression="t1.main_lessee like &apos;%&apos;||${@main_lessee}||&apos;%&apos;"/>
        <bm:query-field name="business_type_n" queryExpression="t1.business_type=${@business_type}"/>
        <bm:query-field name="guarantor_name" queryExpression="t1.guarantor_name like &apos;%&apos;||${@guarantor_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
