<?xml version="1.0" encoding="UTF-8"?>
<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, -- 交易金额
			       (select nvl(sum(nvl(cwo.write_off_due_amount, 0)), 0)
			          from con_contract cc, prj_project pp, csh_write_off cwo
			         where p.project_id = pp.ref_project_id
			           and cc.data_class = 'NORMAL'
			           and cc.contract_id = cwo.contract_id
			           and cc.project_id = pp.project_id
			           and cwo.cf_item = 0
			           and cwo.times = 0) throw_in_amount, -- 已投放金额
			       nvl(bgfl_special_parameter_pkg.get_select_write_off_amount(p_project_id => p.project_id,
			       														      p_query_date_from  =>to_date(${@query_date_from},'YYYY-MM-DD'),
			       														      p_query_date_to  =>to_date(${@query_date_to},'YYYY-MM-DD')),0) query_date_amount,
			       (hd.lease_item_amount -
			       (select nvl(sum(nvl(cwo.write_off_due_amount, 0)), 0)
			          from con_contract cc, prj_project pp, csh_write_off cwo
			         where p.project_id = pp.ref_project_id
			           and cc.data_class = 'NORMAL'
			           and cc.contract_id = cwo.contract_id
			           and cc.project_id = pp.project_id
			           and cwo.cf_item = 0
			           and cwo.times = 0)) sur_throw_in_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,
			       
			       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_select_write_off_date(p_project_id => p.project_id,
			       														p_query_date_from  =>null,
			       														p_query_date_to  =>null) project_write_date
			  FROM prj_project           p,
			       prj_quotation         q,
			       hls_fin_calculator_hd hd,
			       PRJ_CHANCE            PC
			      
			 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_status <> 'CLOSED'
			   and (exists (
			   select 1 from dual where
			   bgfl_special_parameter_pkg.get_select_write_off_date(p_project_id => p.project_id,
			       												    p_query_date_from  =>to_date(${@query_date_from},'YYYY-MM-DD'),
			       													p_query_date_to  =>to_date(${@query_date_to},'YYYY-MM-DD')) is not null) 
			   or (${@query_date_from} is null and ${@query_date_to} is null))
			  
			   ) 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-field name="project_date_from" queryExpression="t1.project_date &gt;= to_date(${@project_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="project_date_to" queryExpression="t1.project_date &lt;=to_date(${@project_date_to},&apos;YYYY-MM-DD&apos;)"/>
    </bm:query-fields>
</bm:model>
