<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: WANGWEI  
    $Date: 2016-8-5 上午11:07:56  
    $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
                        P.PROJECT_ID,
                        P.PROJECT_NUMBER,
                        P.PROJECT_NAME,
                        p.prj_scale,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_PRJ_WFL_STATUS' AND
                            v.code_value = p.prj_wfl_status
                        ) prj_wfl_status_n,
                        SDIC_INDIVIDUAL_PKG.string_combination(p_sql => 'select pbp.bp_name from prj_project_bp pbp where  pbp.Bp_Category=''TENANT'' AND PBP.PROJECT_ID=', p_compare_column_val => P.PROJECT_ID, p_division_symbol => '/') tenant_name,
                        (SELECT
                            bt.description
                        FROM
                            hls_business_type bt
                        WHERE
                            bt.business_type = p.business_type
                        ) business_type_n,
                        hd.lease_item_amount,
                        SDIC_INDIVIDUAL_PKG.string_combination(p_sql => 'select pbp.bp_name from prj_project_bp pbp where  pbp.Bp_Category=''GUARANTOR'' AND PBP.PROJECT_ID=', p_compare_column_val => P.PROJECT_ID, p_division_symbol => '/') GUARANTOR_name,
                        hd.deposit_ratio * 100
                        || '%-'
                        ||
                        (SELECT
                            b.code_value_name value_name
                        FROM
                            sys_codes a,
                            sys_code_values_vl b
                        WHERE
                            b.code_id    = a.code_id AND
                            a.code       = 'HLS500_DEPOSIT_DEDUCTION' AND
                            B.code_value = HD.DEPOSIT_DEDUCTION
                        ) DEPOSIT_DEDUCTION_DESC,
                        ROUND(hd.hd_user_col_n07 * 100, 6)
                        || '%-'
                        ||
                        (SELECT
                            b.code_value_name value_name
                        FROM
                            sys_codes a,
                            sys_code_values_vl b
                        WHERE
                            b.code_id    = a.code_id AND
                            a.code       = 'HLS500_CHARGE_PAY_TIMES' AND
                            B.code_value = HD.HD_USER_COL_V06
                        ) HD_USER_COL_V06_DESC,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_PAY_TYPE' AND
                            v.code_value = hd.pay_type
                        ) pay_type_n,
                        ROUND(hd.IRR_RESERVED2 * 100, 6)
                        || '%' xirr,
                        hd.lease_term
                        || '年' lease_term,
                        (SELECT
                            PCGD.GRT_INFO
                            || ' '
                            || PCGD.MORTGAGE_INFO
                            || ' '
                            || PCGD.PLEDGE_INFO
                            || ' '
                            || PCGD.OTHERS_INFO
                        FROM
                            prj_chance_grt_details PCGD
                        WHERE
                            PCGD.CHANCE_ID = PC.CHANCE_ID
                        ) grt_details,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'LAW_FIRM' AND
                            V.code_value = P.LAW_FIRM
                        ) LAW_FIRM_DESC,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'ACCOUNTING_FIRM' AND
                            V.code_value = P.ACCOUNTING_FIRM
                        ) ACCOUNTING_FIRM_DESC,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TEST_CERTIFICATION_AUTHORITY' AND
                            V.code_value = P.TEST_CERTIFICATION_AUTHORITY
                        ) TEST_CERTIFICATION_AUTHORITY,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TECH_CONSULTING_AUTHORITY' AND
                            V.code_value = P.TECH_CONSULTING_AUTHORITY
                        ) TECH_CONSULTING_AUTHORITY_DESC,
                        P.EMPLOYEE_ID,
                        (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = p.employee_id
                        ) employee_id_n,
                        P.EMPLOYEE_ID_OF_MANAGER,
                        (SELECT
                            ee.name
                        FROM
                            exp_employees ee
                        WHERE
                            ee.employee_id = p.employee_id_of_manager
                        ) employee_id_of_manager_n,
                        (SELECT description
          FROM (SELECT su.description,
                       zv.instance_id,
                       ROW_NUMBER() OVER(partition by zv.instance_id order by zv.rcpt_record_id) rn
                  FROM exp_employee_assigns_v  e,
                       zj_wfl_approve_record_v zv,
                       sys_user                su
                 WHERE e.position_code = '40'
                   AND e.unit_code = '70'
                   AND e.enabled_flag = 'Y'
                   AND zv.user_id = su.user_id
                   AND e.employee_id = su.employee_id) t1
         WHERE t1.instance_id = p.wfl_instance_id
           and rn = 1) review_manager,
                        (SELECT
                            swa.user_id
                        FROM
                            sdic_workflow_assigned swa
                        WHERE
                            swa.instance_id                                                  = p.wfl_instance_id AND
                            swa.sequence_num                                                 = '30' AND
                            sdic_workflow_assign_pkg.get_unit_code(p_user_id => swa.user_id) = '70'
                        ) review_manager_id,
                        P.hn_industry_classification,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                    = 'HN_INDUSTRY_CLASSIFICATION' AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y' AND
                            v.code_value              = p.hn_industry_classification
                        ) hn_industry_classification_n,
                        '按'
                        ||
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                    = 'HLS500_ANNUAL_PAY_TIMES' AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y' AND
                            V.code_value              = hd.annual_pay_times
                        )
                        || '支付' annual_pay_times_desc,
                        TRUNC(p.creation_date) chance_date,
                        (SELECT
                            ppm.meeting_date
                        FROM
                            prj_project_meeting ppm
                        WHERE
                            ppm.meeting_id = ppa.meeting_id
                        ) meeting_date,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_PRJ_APPROVAL_STATUS' AND
                            v.code_value = ppa.status
                        ) approval_status_n,
                        DECODE(p.project_status, 'APPROVED', NVL(
                        (SELECT
                            TRUNC(MAX(zwar.creation_date))
                        FROM
                            zj_wfl_approve_record zwar
                        WHERE
                            zwar.instance_id = p.wfl_instance_id
                        ), TRUNC(p.last_update_date)), '') project_date,
                        DECODE(p.project_status, 'APPROVED', '同意', 'REJECT', '不同意') prj_approve_opinion,
                        bgfl_special_parameter_pkg.get_project_sign_date(p_project_id => p.project_id) project_sign_date,
                        bgfl_special_parameter_pkg.get_prj_virtual_write_off_date(p_project_id => p.project_id) project_write_date
                    FROM
                        prj_project p,
                        prj_quotation q,
                        hls_fin_calculator_hd hd,
                        PRJ_CHANCE PC,
                        prj_project_approval ppa
                    WHERE
                        p.data_class        = 'NORMAL' AND
                        p.project_id        = q.document_id AND
                        q.document_category = 'PROJECT' AND
                        q.calc_session_id   = hd.calc_session_id AND
                        q.quotation_type    = 'MAJOR' AND
                        P.CHANCE_ID         = PC.CHANCE_ID AND
                        p.project_id        = ppa.project_id(+) AND
                        ppa.status(+)      <> 'NEW'
                    ) t1 #WHERE_CLAUSE#
                    order by t1.project_number desc
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="project_id" queryExpression="t1.project_id=${@project_id}"/>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.hn_industry_classification=${@hn_industry_classification}"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like ${@project_name}"/>
        <bm:query-field name="employee_id_n" queryExpression="t1.employee_id_n like ${@employee_id_n}"/>
        <bm:query-field name="employee_id_of_manager_n" queryExpression="t1.review_manager like ${@review_manager}"/>
        <bm:query-field name="tenant_name" queryExpression="t1.tenant_name like ${@tenant_name}"/>
    </bm:query-fields>
</bm:model>
